BPM Approval (Workflow) Table list in Oracle Fusion

BPM(Workflow) Table list in Oracle Fusion

1. POR_AMX_RULES
2. POR_AMX_PARTICIPANTS
3. POR_AMX_DIMENSIONS
4. POR_AMX_TASKS
5. POR_AMX_STAGES
6. POR_AMX_ACTIONS
7. POR_AMX_CONDITIONS

SQL Query

The sql query below extracts information about the BPM approval rules for Purchase requisitions data

SELECT par.rule_id,
       par.rule_number,
       par.rule_name,
       par.DISPLAY_RULE_NAME,
       par.description,
       par.CONDITIONS_STRING,
       par.PRIORITY,
       par.active_flag,
       pat.LOOKUP_CODE pat_LOOKUP_CODE,
       pap.PARTICIPANT_KEY,
       paa.ACTION_CODE,
       paa.APPROVAL_GROUP_NAME,
       pac.condition_number,
       pac.attribute_key cond_key,
       pac.dimension_key,
       pac.operator,
       pac.condition_string
FROM   POR_AMX_RULES PAR,
       POR_AMX_PARTICIPANTS pap,
       POR_AMX_DIMENSIONS   PAD,
       POR_AMX_TASKS PAT,
       (select RULE_NAME max_rul_name, max(OBJECT_VERSION_NUMBER) max_obj 
        from POR_AMX_RULES 
        group by RULE_NAME) rul_max,
       POR_AMX_STAGES   PAS,
       POR_AMX_ACTIONS PAA,
       POR_AMX_CONDITIONS PAC
WHERE  par.active_flag = 'Y'
   and par.PARTICIPANT_ID = pap.PARTICIPANT_ID
   and par.task_id = pap.task_id
   and pas.STAGE_ID = pap.STAGE_ID
   and pas.task_id = pap.task_id
   and pas.stage_id = pap.stage_id
   and PAT.task_id = pad.task_id
   and rul_max.max_obj = par.OBJECT_VERSION_NUMBER
   and rul_max.max_rul_name = par.rule_name
   and paa.rule_id = par.rule_id
   and paa.task_id = par.task_id
   and pac.rule_id = paa.rule_id
   and pac.task_id = paa.task_id
   and pat.task_key = 'ReqApproval'
   and pad.TABLE_NAME  = 'POR_REQUISITION_HEADERS_ALL';

 

Oracle ERP Cloud SQL Query to find Bank Accounts Associated with a Supplier

/******************************************************************* *PURPOSE: ERP Cloud SQL Query to find Bank Accounts Associated with a Supplier * *AUTHOR: Shailender Thallam * *******************************************************************/ SELECT ps.segment1 AS vendor_num , hzp.party_name AS vendor_name , pssm.attribute20 AS legacy_vendor_number , pssm.vendor_site_code , bank.party_name bank_name , branch.party_name branch_name , ieb.bank_account_name , ieb.bank_account_num , ieb.currency_code , ieb.Bank_Account_Name_alt AS TRANSIT_NUMBER , ieb.Secondary_Account_Reference AS ROUTING_NUMBER FROM poz_suppliers ps , poz_supplier_sites_all_m pssm , iby_external_payees_all payee , iby_pmt_instr_uses_all uses , iby_ext_bank_accounts ieb , hz_parties bank , hz_parties branch , HZ_PARTIES HZP WHERE ps.vendor_id = pssm.vendor_id AND ps.party_id = payee.payee_party_id AND payee.supplier_site_id = pssm.vendor_site_id AND uses.instrument_type = 'BANKACCOUNT' AND payee.ext_payee_id = uses.ext_pmt_party_id AND uses.payment_function = 'PAYABLES_DISB' AND uses.instrument_id = ieb.ext_bank_account_id AND ieb.bank_id = bank.party_id(+) AND ieb.branch_id = branch.party_id(+) AND hzp.party_id = ps.party_id AND SYSDATE BETWEEN NVL(uses.start_date,SYSDATE) AND NVL(uses.end_date,SYSDATE) AND SYSDATE BETWEEN NVL(ieb.start_date,SYSDATE) AND NVL(ieb.end_date,SYSDATE) AND NVL(ps.end_date_active,SYSDATE+1) > TRUNC (SYSDATE) AND NVL(pssm.inactive_date,SYSDATE+1) > TRUNC (SYSDATE)

SQL Query to get the Supplier Contact Address Detail Information

Here are the tables that we can refer supplier Information. Suppliers - POZ_SUPPLIERS Contacts - HZ_PARTIES Addresses - HZ_PARTY_SITES Contact Addresses - POZ_SUPPLIER_CONTACTS (Join to HZ_PARTIES on per_party_id - POZ_SUPPLIER_CONTACTS.PER_PARTY_ID = HZ_PARTIES.PARTY_ID ) (Join to HZ_PARTY_SITES on party_site_id - POZ_SUPPLIER_CONTACTS.PARTY_SITE_ID = HZ_PARTY_SITES.PARTY_SITE_ID)

Supplier Site Query ( SQL) – General in Fusion

Supplier site is one of the most important set ups in Oracle Fusion Finance. if you have implemented P2P, vendor site and reports fetching the data are inevitable. in this blog, let me share the general query for supplier site. Of course there will be posts as the follow up to this post to find other important fields:

Supplier Site Query Output:

This query will return you following columns:
1. Vendor Site Code
2. Procurement Business Unit
3. Alternate Vendor Site Code
4. Customer Number
5. Purchasing Site Flag
6. Pay Site Flag
7. Procurement Site Flag
8. Primary Pay Site Flag
9. Tax Reporting Site Flag
10. Document Category
11. Site Address
12. Site Status

Supplier Site Query:

SELECT ste.vendor_site_code,
hou.name procurement_bu,
ste.vendor_site_code_alt alternate_site_name,
ste.customer_num customer_number,
ste.purchasing_site_flag,
ste.pay_site_flag,
ste.pcard_site_flag procurement_site_flag ,
ste.primary_pay_site_flag,
ste.tax_reporting_site_flag income_tax_reporting_site,
b2b_site_code b2b_supplier_site_code ,
dsc.name document_category,
hl.address1 ||’, ‘|| hl.address2||’, ‘||hl.city||’, ‘||hl.state||’, ‘|| hl.postal_code|| ‘, ‘||hl.country site_address,
CASE
WHEN NVL(ste.INACTIVE_DATE, SYSDATE +1) > SYSDATE
THEN
‘Active’
ELSE
‘Inactive’
END status
FROM poz_supplier_sites_all_m ste,
hz_party_sites hps,
hz_locations hl,
hr_operating_units hou,
fnd_doc_sequence_categories dsc
WHERE 1=1
AND hps.party_site_id = ste.party_site_id
AND hl.location_id = ste.location_id
AND hou.organization_id = ste.PRC_BU_ID
AND dsc.code(+) = ste.global_attribute1
AND ste.vendor_site_code= :vendor_site_code

Here :vendor_site_code is the input parameter. But in case you want to get all the sites of the supplier, you can add POZ_SUPPLIERS table in the above query and connect POZ_SUPPLIER_SITES_ALL_M table using vendor_id.

Supplier Site Table in Oracle Fusion

 

Types of Supplier Details in Oracle Fusion

In Oracle Fusion, Supplier details are divided into four parts.

1. Supplier Header

2.Supplier Addresses

3.Supplier Sites

4.Supplier Site Assignment

4 Important Supplier Tables in Oracle Fusion

1.POZ_SUPPLIER_SITES_ALL_M

2.POZ_SUPPLIERS_V

3.POZ_SUPPLIER_CONTACTS_V

4.POZ_SITE_ASSIGNMENTS_ALL_M

SQL Query to Extract Supplier detail Using Supplier Site Table in Oracle Fusion

SELECT PS.VENDOR_NAME,

PS.SEGMENT1 SUPPLIER_NUMBER,

NVL2(PS.TAX_REPORTING_NAME, 'Secure', NULL) TAX_REPORTING_NAME,

PS.EMPLOYEE_ID,

pvs.vendor_site_spk_id, pvs.vendor_site_id, pvs.effective_end_date,

pvs.vendor_site_code, pvs.purchasing_site_flag,pvs.rfq_only_site_flag,

pvs.pay_site_flag,pvs.pay_on_receipt_summary_code, pvs.ece_tp_location_code,

pvs.pcard_site_flag, pvs.match_option, pvs.country_of_origin_code,

pvs.create_debit_memo_flag, pvs.supplier_notif_method,

pvs.email_address, pvs.primary_pay_site_flag, pvs.shipping_control,

pvs.hold_by,

pvs.hold_date, pvs.hold_flag, pvs.purchasing_hold_reason,

pvs.vendor_site_code_alt,pvs.attention_ar_flag, pvs.area_code,

pvs.phone, pvs.customer_num, pvs.ship_via_lookup_code,

pvs.freight_terms_lookup_code,

pvs.fax_area_code,pvs.payment_priority,

pvs.invoice_amount_limit, 

hzl.address1 address_line1,

hzl.address2 address_line2,

hzl.address3 address_line3,

hzl.address4 address_line4,

hzl.address_style address_style,

hzl.address_lines_phonetic address_lines_alt, 

hzl.city city,

hzl.state state, 

hzl.county county,

hzl.country country,

 hzl.postal_code zip, hzl.province province,

hps.duns_number_c duns_number

FROM poz_supplier_sites_all_m pvs, hz_locations hzl, hz_party_sites hps ,POZ_SUPPLIERS_V PS

WHERE hzl.location_id = pvs.location_id

AND hps.party_site_id = pvs.party_site_id

AND pvs.vendor_id=PS.VENDOR_ID

Oracle Fusion Cloud: Supplier Import Process using File Based Data Import (FBDI)

 Supplier Data Migration or Upload to Oracle Fusion environment

File-Based Data Import for Oracle Procurement Cloud

Supplier import in oracle fusion

 

In this post , We will discuss about Supplier import in oracle fusion. Oracle has provided the FBDI tool to import suppliers from External Source to the Oracle fusion. Supplier Import in Oracle fusion we first need to Download the Supplier Import FDBI templates given by the Oracle fusion to import suppliers. Oracle has given 5 Different FBDI templates to Import supplier in Fusion. Here in this post , I will share the Complete steps for Supplier import in oracle fusion. You can refer this post for Supplier Import.

 

Steps for Supplier import in oracle fusion

 

 

Step1- First we need to download Supplier Data Template from Oracle Repository.

Go to this path for Oracle Repository. ( select Based on your Cloud version)

https://docs.oracle.com/en/cloud/saas/procurement/20c/oefbp/supplier-model.html#supplier-model

 

FBDI Template oracle

 

Step-2:- Go to Procurement Tab as below and Click on Release 11 under File-Based Data Import.

 

Step-3:- Then this below screen page will open.

 

Step 4:- Under File-Based Data Imports click on Import Supplier Data Template.

Step-5:- Click on XLSM template and save the data Template in our Desktop.

Step-6:- This is the Below Data Template for Import Supplier.

First Tab is the Instruction tab and Second Tab is the Data Template in which we will put data for suppliers.

 

Oracle FBDI r13

Step-7

I am going to create TEST SUPPLIER through FBDI.

Step-8:- After Putting data in Data Template and then go back to first tab and Click on Generate CSV File button as below.

This will create PozSuppliersInt.zip file in your local system.

 

Step-9:- Login to Oracle Fusion and go to 'File Import and Export ' option as below.

-10:- Click on Create Upload

 

 Browse local drive to select PozSuppliersInt.zip  file

 Select Account – prc/supplier/import.

Click Save and Continue.

 

Step-11 :- Once the File will be Imported.

Click Navigator -> Tools -> Scheduled Process

Step-12:- Select ESS Job ‘Load Interface File for Import’

 

 

Step13:- Go to the Procurement Module in Fusion and Select supplier.

Step:-14 : Under Supplier ==> click on Import Supplier

Step 16:-Select the Import Process ‘Import Supplier’ and your zip file uploaded in the previous step.

Step17:- Import Suppliers Parameters

Step18:- 

Supplier Has been created as Below

 

Please share your comments and feedback about this Post.