How To Fetch (Query) The Payment Details In Fusion

 Payment: The final leg of P2P Cycle in Oracle. Once you run the payment process Request in Fusion, depending on the criteria you have chosen Invoices will be selected for payment. Once selected, you’ll also get the option to remove the invoices you don’t want to pay. Once you confirm the final list of the invoices to be paid, and take the payment process request to the conclusion, Invoices will be paid (at least in the system. You might still need to send the checks to the vendor or EFT file to the bank)

At high level, this is how the relationship among Payment Process Request, Payment, Checks and Invoices sample looks like

Oracle Fusion Payment structure

To give the further details, every payment process request can have multiple invoices paid against. So relationship between Payment Process Request and Check is 1:N.

Again, 1 Check can be used to pay multiple invoices. At the same time one invoice can be paid by multiple checks. So relationship between review and the Invoices is M:N. Table that holds the relationship is AP_INVOICE_PAYMENTS_ALL.

EBS to FUSION PA (Project Accounting) Table Changes

 

EBS to FUSION PA (Project Accounting) Table Changes

EBS Tables -----------------------------------------	FUSION Tables -------------------------------------------
PA_PROJECTS_ALL -----------------------------------------	PJF_PROJECTS_ALL_B PJF_PROJECTS_ALL_TL -------------------------------------------
PA_PROJECT_TYPES_ALL -----------------------------------------	PJF_PROJECT_TYPES_B PJF_PROJECT_TYPES_TL -------------------------------------------
PA_PROJECT_STATUSES -----------------------------------------	PJF_PROJECT_STATUSES_B PJF_PROJECT_STATUSES_TL -------------------------------------------
PA_PROJECT_STATUS_CONTROLS -----------------------------------------	PJF_PROJ_STATUS_CONTROLS -------------------------------------------
PA_PROJECT_ACCUM_ACTUALS -----------------------------------------	PJO_PROJECT_PLAN_ACTUALS -------------------------------------------
PA_TASKS -----------------------------------------	PJF_PROJ_ELEMENTS_B PJF_PROJ_ELEMENTS_TL PJF_TASKS_V -------------------------------------------
PA_BUDGET_TYPES -----------------------------------------	PJO_PLAN_TYPES_B -------------------------------------------
PA_EXPENDITURE_TYPES -----------------------------------------	PJF_EXP_TYPES_B PJF_EXP_TYPES_B_ST PJF_EXP_TYPES_TL PJF_EXP_CATEGORIES_B  -------------------------------------------
PA_EXPENDITURE_ITEMS_ALL -----------------------------------------	PJC_EXP_ITEMS_ALL -------------------------------------------
PA_COST_DISTRIBUTION_LINES_ALL  -----------------------------------------	PJC_COST_DIST_LINES_ALL -------------------------------------------
PA_TRANSACTION_SOURCES -----------------------------------------	PJF_TXN_SOURCES_B PJF_TXN_SOURCES_TL -------------------------------------------
PA_IMPLEMENTATIONS_ALL -----------------------------------------	PJF_BU_IMPL_ALL -------------------------------------------
PA_EXPENDITURE_COMMENTS -----------------------------------------	PJC_EXP_COMMENTS -------------------------------------------
PA_CUST_REV_DIST_LINES_ALL -----------------------------------------	PJB_REV_DISTRIBUTIONS -------------------------------------------
PA_TASKS -----------------------------------------	PJF_TASKS_V -------------------------------------------
PA_EXPENDITURE_ITEMS_ALL, PA_EXPENDITURES_ALL -----------------------------------------	PJC_EXP_ITEMS_ALL -------------------------------------------
PA_EXPENDITURE_TYPES -----------------------------------------	PJF_EXP_TYPES_B PJF_EXP_TYPES_B_ST PJF_EXP_TYPES_TL -------------------------------------------
PA_EVENTS -----------------------------------------	PJB_BILLING_EVENTS -------------------------------------------
PA_PROJECT_PARTIES -----------------------------------------	PJF_PROJECT_PARTIES -------------------------------------------
PA_PROJECT_ROLE_TYPES_B -----------------------------------------	PJF_PROJ_ROLE_TYPES_B -------------------------------------------

AP Supplier Invoices query - FUSION

AP Supplier Invoices query - FUSION

SELECT gp.period_name,
       (SELECT    'Q'
               || quarter_num
               || '-WK'
               || LPAD (TO_CHAR (DECODE (period_num,
                                         53, 14,
                                         DECODE (MOD (period_num, 13),
                                                 0, 13,
                                                 MOD (period_num, 13)
                                                )
                                        )
                                ),
                        2,
                        '0'
                       ) planning_week
          FROM gl_periods gp
         WHERE 1 = 1
           AND dist.accounting_date BETWEEN start_date AND end_date
           AND adjustment_period_flag = 'N'
           AND gp.period_set_name = gl.period_set_name) planning_week,
       (SELECT fv.description
          FROM fnd_flex_values_vl fv,
               fnd_flex_value_sets fvs
         WHERE fv.flex_value_set_id = fvs.flex_value_set_id
           AND fvs.flex_value_set_name = 'Department'
           AND fv.flex_value = gcc.segment3) expenditure_organization,
       inv.project_id project_id,
       NVL ((SELECT p.segment1
               FROM pjf_projects_all_b p
              WHERE p.project_id = inv.project_id), 'N/A') project_number,
       NVL ((SELECT p.NAME
               FROM pjf_projects_all_tl p
              WHERE project_id(+) = inv.project_id), 'N/A') project_name,
       NULL project_type_class_code, hp.party_name employee_vendor,
       DECODE (TO_CHAR (dist.accounting_date, 'DAY'),
               5, dist.accounting_date,
               NEXT_DAY (dist.accounting_date, 6)
              ) week_ending,
       dist.accounting_date expenditure_date,
       inv.invoice_currency_code entered_currency_code,
       dist.amount entered_amount,
       DECODE (inv.invoice_currency_code,
               gl.currency_code, NVL (dist.amount, 0),
               NVL (dist.amount, 0) * NVL (dist.exchange_rate, 0)
              ) accounted_amount,
       dist.exchange_rate accounted_exchange_rate,
       dist.exchange_rate_type accounted_exchange_type,
       (  DECODE (inv.invoice_currency_code,
                  gl.currency_code, NVL (dist.amount, 0),
                  NVL (dist.amount, 0) * NVL (dist.exchange_rate, 1)
                 )
        * NVL ((SELECT gdr.conversion_rate
                  FROM gl_daily_rates gdr
                 WHERE gdr.conversion_type = 'Corporate'
                   AND gdr.to_currency = 'USD'
                   AND gdr.from_currency = gl.currency_code
                   AND gdr.conversion_date = dist.accounting_date),
               1
              )
       ) usd_amount,
       NVL ((SELECT gdr.conversion_rate
               FROM gl_daily_rates gdr
              WHERE gdr.conversion_type = 'Corporate'
                AND gdr.to_currency = 'USD'
                AND gdr.from_currency = gl.currency_code
                AND gdr.conversion_date = dist.accounting_date),
            1
           ) usd_conversion_rate,
       dist.accounting_date conversion_date, gl.NAME set_of_books_name,
       gl.currency_code book_currency_code, gcc.segment1 company,
       gcc.segment2 ACCOUNT, gcc.segment3 department, gcc.segment4 region,
       gcc.segment5 FUNCTION, gcc.segment6 intercompany,
       dist.description comments, inv.invoice_num ap_invoice_number,
       gcc.segment1,
       (SELECT class_code
          FROM pjf_project_classes prc,
               pjf_class_codes_tl pct
         WHERE prc.class_code_id = pct.class_code_id
           AND prc.project_id = inv.project_id) project_region_code
  FROM ap_invoice_distributions_all dist,
       ap_invoice_lines_all line,
       ap_invoices_all inv,
       gl_code_combinations gcc,
       poz_suppliers ps,
       hz_parties hp,
       gl_ledgers gl,
       gl_periods gp
 WHERE dist.invoice_id = line.invoice_id
   AND dist.invoice_line_number = line.line_number
   AND line.invoice_id = inv.invoice_id
   AND dist.dist_code_combination_id = gcc.code_combination_id
   AND inv.vendor_id = ps.vendor_id
   AND hp.party_id = ps.party_id
   AND inv.set_of_books_id = gl.ledger_id
   AND gp.period_set_name = gl.period_set_name
   AND dist.accounting_date BETWEEN gp.start_date AND gp.end_date
   AND gp.adjustment_period_flag = 'N'

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

DISPLAY QR CODE IN CLOUD ERP BI PUBLISHER REPORT

 

It is very easy to display QR code in a Fusion BI Publisher report. To achieve that you need to add the below tag in the RTF template of your BIP report

<?qrcode: <DATA>; <SIZE>[; <CHARSET>]?>

for example: <?qrcode: ITEM_NUMBER; 100?>

adding qrcode tag in the RTF template

Sample Query: select item_number,description from egp_system_items_vl

BIP Data model

Report output with QR Code

For more information please refer
https://docs.oracle.com/en/cloud/paas/analytics-cloud/acpmr/2d-barcode-functions.html#GUID-E0D697B1-0B13-46A3-8578-1FE933F1A86D
and
Fusion Application – BI Publisher – How to implement QR Code (Doc ID 2382839.1)

Supplier Bank Assignment Query SQL Oracle Fusion

 Select * from (SELECT PaymentInstrumentUsesEO.INSTRUMENT_PAYMENT_USE_ID,

  PaymentInstrumentUsesEO.EXT_PMT_PARTY_ID,

  PaymentInstrumentUsesEO.INSTRUMENT_TYPE,

  PaymentInstrumentUsesEO.INSTRUMENT_ID,

  PaymentInstrumentUsesEO.PAYMENT_FUNCTION,

  PaymentInstrumentUsesEO.ORDER_OF_PREFERENCE,

  PaymentInstrumentUsesEO.DEBIT_AUTH_FLAG,

  PaymentInstrumentUsesEO.DEBIT_AUTH_METHOD,

  PaymentInstrumentUsesEO.DEBIT_AUTH_REFERENCE,

  PaymentInstrumentUsesEO.DEBIT_AUTH_BEGIN,

  PaymentInstrumentUsesEO.DEBIT_AUTH_END,

  PaymentInstrumentUsesEO.START_DATE,

  PaymentInstrumentUsesEO.END_DATE,

  ibyextbankaccts.bank_account_id,

  ibyextbankaccts.bank_account_name,

  ibyextbankaccts.bank_account_number,

  ibyextbankaccts.currency_code,

  ibyextbankaccts.bank_name,

  ibyextbankaccts.bank_number,

  ibyextbankaccts.bank_branch_name,

  ibyextbankaccts.bank_account_type,

  ibyextbankaccts.branch_number,

  ibyextbankaccts.iban_number,

  ibyextbankaccts.eft_swift_code,

  'N' detailView,

  PaymentInstrumentUsesEO.PAYMENT_FLOW,

  supplier.vendor_name party_name,

  supplier.vendor_name_alt alt_party_name,

  supplier.segment1 party_number,

  NULL site_name,

  NULL alt_site_name,

  address.party_site_name,

  NULL organization_name,

  DECODE(Payee.supplier_site_id, NULL, DECODE(Payee.org_id, NULL, DECODE(Payee.party_site_id, NULL,

  (SELECT MEANING

  FROM fnd_lookup_values_vl

  WHERE LOOKUP_TYPE = 'IBY_EXT_BNK_ACCT_ASSGN_SRCH'

  AND LOOKUP_CODE   ='SUPPLIER'

  ),

  (SELECT MEANING

  FROM fnd_lookup_values_vl

  WHERE LOOKUP_TYPE = 'IBY_EXT_BNK_ACCT_ASSGN_SRCH'

  AND LOOKUP_CODE   ='ADDRESS'

  )),

  (SELECT MEANING

  FROM fnd_lookup_values_vl

  WHERE LOOKUP_TYPE = 'IBY_EXT_BNK_ACCT_ASSGN_SRCH'

  AND LOOKUP_CODE   ='ADDRESS_OPERATING'

  )),

  (SELECT MEANING

  FROM fnd_lookup_values_vl

  WHERE LOOKUP_TYPE = 'IBY_EXT_BNK_ACCT_ASSGN_SRCH'

  AND LOOKUP_CODE   ='SITE'

  )) assignment_level,

  ibyextbankaccts.ext_bank_account_id ext_bank_account_id,

  ibyextbankaccts.bank_party_id bank_party_id,

  ibyextbankaccts.branch_party_id branch_party_id,

  DECODE(ibyextbankaccts.FOREIGN_PAYMENT_USE_FLAG, 'Y',

  (SELECT meaning

  FROM fnd_lookup_values_vl

  WHERE LOOKUP_TYPE = 'IBY_YES_NO'

  AND lookup_code   = 'Y'

  ),

  (SELECT meaning

  FROM fnd_lookup_values_vl

  WHERE LOOKUP_TYPE = 'IBY_YES_NO'

  AND lookup_code   = 'N'

  )) FOREIGN_PAYMENT_USE_FLAG,

  DECODE(ibyextbankaccts.PRIMARY_ACCT_OWNER_PARTY_ID, Payee.payee_party_id,

  (SELECT meaning

  FROM fnd_lookup_values_vl

  WHERE LOOKUP_TYPE = 'IBY_YES_NO'

  AND lookup_code   = 'Y'

  ),

  (SELECT meaning

  FROM fnd_lookup_values_vl

  WHERE LOOKUP_TYPE = 'IBY_YES_NO'

  AND lookup_code   = 'N'

  )) primary_flag,

  Payee.payee_party_id party_id,

  (SELECT MEANING

  FROM fnd_lookup_values_vl

  WHERE LOOKUP_TYPE = 'IBY_EXT_BNK_ACCT_ASSGN_SRCH'

  AND LOOKUP_CODE   ='SUPPLIER'

  ) party_type

FROM IBY_PMT_INSTR_USES_ALL PaymentInstrumentUsesEO,

  IBY_EXT_BANK_ACCOUNTS_V ibyextbankaccts,

  IBY_EXTERNAL_PAYEES_ALL Payee,

  poz_suppliers_v supplier,

  hz_party_sites address

WHERE PaymentInstrumentUsesEO.instrument_id  = ibyextbankaccts.bank_account_id

AND PaymentInstrumentUsesEO.instrument_type  = 'BANKACCOUNT'

AND PaymentInstrumentUsesEO.payment_function = 'PAYABLES_DISB'

AND PaymentInstrumentUsesEO.EXT_PMT_PARTY_ID = Payee.ext_payee_id

AND Payee.payee_party_id                     = supplier.party_id

AND Payee.party_site_id                      = address.party_site_id(+)

AND Payee.org_id                            IS NULL

AND Payee.org_type                          IS NULL

UNION

SELECT PaymentInstrumentUsesEO.INSTRUMENT_PAYMENT_USE_ID,

  PaymentInstrumentUsesEO.EXT_PMT_PARTY_ID,

  PaymentInstrumentUsesEO.INSTRUMENT_TYPE,

  PaymentInstrumentUsesEO.INSTRUMENT_ID,

  PaymentInstrumentUsesEO.PAYMENT_FUNCTION,

  PaymentInstrumentUsesEO.ORDER_OF_PREFERENCE,

  PaymentInstrumentUsesEO.DEBIT_AUTH_FLAG,

  PaymentInstrumentUsesEO.DEBIT_AUTH_METHOD,

  PaymentInstrumentUsesEO.DEBIT_AUTH_REFERENCE,

  PaymentInstrumentUsesEO.DEBIT_AUTH_BEGIN,

  PaymentInstrumentUsesEO.DEBIT_AUTH_END,

  PaymentInstrumentUsesEO.START_DATE,

  PaymentInstrumentUsesEO.END_DATE,

  ibyextbankaccts.bank_account_id,

  ibyextbankaccts.bank_account_name,

  ibyextbankaccts.bank_account_number,

  ibyextbankaccts.currency_code,

  ibyextbankaccts.bank_name,

  ibyextbankaccts.bank_number,

  ibyextbankaccts.bank_branch_name,

  ibyextbankaccts.bank_account_type,

  ibyextbankaccts.branch_number,

  ibyextbankaccts.iban_number,

  ibyextbankaccts.eft_swift_code,

  'N' detailView,

  PaymentInstrumentUsesEO.PAYMENT_FLOW,

  supplier.vendor_name party_name,

  supplier.vendor_name_alt alt_party_name,

  supplier.segment1 party_number,

  DECODE(supplier.vendor_type_lookup_code, 'EMPLOYEE', NVL(

  (SELECT NVL(ALC.displayed_field, site.VENDOR_SITE_CODE)

  FROM AP_LOOKUP_CODES ALC

  WHERE ALC.lookup_type(+) = 'VENDOR_SITE_CODE'

  AND ALC.lookup_code(+)   = site.VENDOR_SITE_CODE

  ), site.VENDOR_SITE_CODE), site.VENDOR_SITE_CODE) site_name,

  site.vendor_site_code_alt alt_site_name,

  address.party_site_name,

  ou.NAME organization_name,

  DECODE(Payee.supplier_site_id, NULL, DECODE(Payee.org_id, NULL, DECODE(Payee.party_site_id, NULL,

  (SELECT MEANING

  FROM fnd_lookup_values_vl

  WHERE LOOKUP_TYPE = 'IBY_EXT_BNK_ACCT_ASSGN_SRCH'

  AND LOOKUP_CODE   ='SUPPLIER'

  ),

  (SELECT MEANING

  FROM fnd_lookup_values_vl

  WHERE LOOKUP_TYPE = 'IBY_EXT_BNK_ACCT_ASSGN_SRCH'

  AND LOOKUP_CODE   ='ADDRESS'

  )),

  (SELECT MEANING

  FROM fnd_lookup_values_vl

  WHERE LOOKUP_TYPE = 'IBY_EXT_BNK_ACCT_ASSGN_SRCH'

  AND LOOKUP_CODE   ='ADDRESS_OPERATING'

  )),

  (SELECT MEANING

  FROM fnd_lookup_values_vl

  WHERE LOOKUP_TYPE = 'IBY_EXT_BNK_ACCT_ASSGN_SRCH'

  AND LOOKUP_CODE   ='SITE'

  )) assignment_level,

  ibyextbankaccts.ext_bank_account_id ext_bank_account_id,

  ibyextbankaccts.bank_party_id bank_party_id,

  ibyextbankaccts.branch_party_id branch_party_id,

  DECODE(ibyextbankaccts.FOREIGN_PAYMENT_USE_FLAG, 'Y',

  (SELECT meaning

  FROM fnd_lookup_values_vl

  WHERE LOOKUP_TYPE = 'IBY_YES_NO'

  AND lookup_code   = 'Y'

  ),

  (SELECT meaning

  FROM fnd_lookup_values_vl

  WHERE LOOKUP_TYPE = 'IBY_YES_NO'

  AND lookup_code   = 'N'

  )) FOREIGN_PAYMENT_USE_FLAG,

  DECODE(ibyextbankaccts.PRIMARY_ACCT_OWNER_PARTY_ID, Payee.payee_party_id,

  (SELECT meaning

  FROM fnd_lookup_values_vl

  WHERE LOOKUP_TYPE = 'IBY_YES_NO'

  AND lookup_code   = 'Y'

  ),

  (SELECT meaning

  FROM fnd_lookup_values_vl

  WHERE LOOKUP_TYPE = 'IBY_YES_NO'

  AND lookup_code   = 'N'

  )) primary_flag,

  Payee.payee_party_id party_id,

  (SELECT MEANING

  FROM fnd_lookup_values_vl

  WHERE LOOKUP_TYPE = 'IBY_EXT_BNK_ACCT_ASSGN_SRCH'

  AND LOOKUP_CODE   ='SUPPLIER'

  ) party_type

FROM IBY_PMT_INSTR_USES_ALL PaymentInstrumentUsesEO,

  IBY_EXT_BANK_ACCOUNTS_V ibyextbankaccts,

  IBY_EXTERNAL_PAYEES_ALL Payee,

  poz_suppliers_v supplier,

  POZ_SUPPLIER_SITES_V site,

  hz_party_sites address,

  hr_operating_units ou

WHERE PaymentInstrumentUsesEO.instrument_id  = ibyextbankaccts.bank_account_id

AND PaymentInstrumentUsesEO.instrument_type  = 'BANKACCOUNT'

AND PaymentInstrumentUsesEO.payment_function = 'PAYABLES_DISB'

AND PaymentInstrumentUsesEO.EXT_PMT_PARTY_ID = Payee.ext_payee_id

AND Payee.payee_party_id                     = supplier.party_id

AND Payee.supplier_site_id                   = site.vendor_site_id(+)

AND Payee.party_site_id                      = address.party_site_id(+)

AND Payee.org_id                             = ou.organization_id (+)

AND Payee.org_id                            IS NOT NULL

AND Payee.org_type                          IS NOT NULL

/*AND (Payee.org_id, Payee.org_type)          IN

(SELECT uo.organization_id,

uo.organization_type

FROM ce_security_profiles_v uo

)*/

UNION

SELECT PaymentInstrumentUsesEO.INSTRUMENT_PAYMENT_USE_ID,

  PaymentInstrumentUsesEO.EXT_PMT_PARTY_ID,

  PaymentInstrumentUsesEO.INSTRUMENT_TYPE,

  PaymentInstrumentUsesEO.INSTRUMENT_ID,

  PaymentInstrumentUsesEO.PAYMENT_FUNCTION,

  PaymentInstrumentUsesEO.ORDER_OF_PREFERENCE,

  PaymentInstrumentUsesEO.DEBIT_AUTH_FLAG,

  PaymentInstrumentUsesEO.DEBIT_AUTH_METHOD,

  PaymentInstrumentUsesEO.DEBIT_AUTH_REFERENCE,

  PaymentInstrumentUsesEO.DEBIT_AUTH_BEGIN,

  PaymentInstrumentUsesEO.DEBIT_AUTH_END,

  PaymentInstrumentUsesEO.START_DATE,

  PaymentInstrumentUsesEO.END_DATE,

  ibyextbankaccts.bank_account_id,

  ibyextbankaccts.bank_account_name,

  ibyextbankaccts.bank_account_number,

  ibyextbankaccts.currency_code,

  ibyextbankaccts.bank_name,

  ibyextbankaccts.bank_number,

  ibyextbankaccts.bank_branch_name,

  ibyextbankaccts.bank_account_type,

  ibyextbankaccts.branch_number,

  ibyextbankaccts.iban_number,

  ibyextbankaccts.eft_swift_code,

  'N' detailView,

  PaymentInstrumentUsesEO.PAYMENT_FLOW,

  hp.party_name party_name,

  NULL alt_party_name,

  Cust.ACCOUNT_NUMBER party_number,

  NULL site_name,

  NULL alt_site_name,

  NULL party_site_name,

  NULL organization_name,

  DECODE(Payer.ACCT_SITE_USE_ID,NULL,(

  (SELECT MEANING

  FROM fnd_lookup_values_vl

  WHERE LOOKUP_TYPE = 'IBY_EXT_BNK_ACCT_ASSGN_SRCH'

  AND LOOKUP_CODE   ='CUSTOMER'

  )),(

  (SELECT MEANING

  FROM fnd_lookup_values_vl

  WHERE LOOKUP_TYPE = 'IBY_EXT_BNK_ACCT_ASSGN_SRCH'

  AND LOOKUP_CODE   ='SITE'

  ))) assignment_level,

  ibyextbankaccts.ext_bank_account_id ext_bank_account_id,

  ibyextbankaccts.bank_party_id bank_party_id,

  ibyextbankaccts.branch_party_id branch_party_id,

  DECODE(ibyextbankaccts.FOREIGN_PAYMENT_USE_FLAG, 'Y',

  (SELECT meaning

  FROM fnd_lookup_values_vl

  WHERE LOOKUP_TYPE = 'IBY_YES_NO'

  AND lookup_code   = 'Y'

  ),

  (SELECT meaning

  FROM fnd_lookup_values_vl

  WHERE LOOKUP_TYPE = 'IBY_YES_NO'

  AND lookup_code   = 'N'

  )) FOREIGN_PAYMENT_USE_FLAG,

  DECODE(ibyextbankaccts.PRIMARY_ACCT_OWNER_PARTY_ID, Payer.party_id,

  (SELECT meaning

  FROM fnd_lookup_values_vl

  WHERE LOOKUP_TYPE = 'IBY_YES_NO'

  AND lookup_code   = 'Y'

  ),

  (SELECT meaning

  FROM fnd_lookup_values_vl

  WHERE LOOKUP_TYPE = 'IBY_YES_NO'

  AND lookup_code   = 'N'

  )) primary_flag,

  Payer.party_id party_id,

  (SELECT MEANING

  FROM fnd_lookup_values_vl

  WHERE LOOKUP_TYPE = 'IBY_EXT_BNK_ACCT_ASSGN_SRCH'

  AND LOOKUP_CODE   ='CUSTOMER'

  ) party_type

FROM IBY_PMT_INSTR_USES_ALL PaymentInstrumentUsesEO,

  IBY_EXT_BANK_ACCOUNTS_V ibyextbankaccts,

  IBY_EXTERNAL_PAYERS_ALL Payer,

  HZ_CUST_ACCOUNTS Cust,

  hz_parties hp

WHERE PaymentInstrumentUsesEO.instrument_id  = ibyextbankaccts.bank_account_id

AND PaymentInstrumentUsesEO.instrument_type  = 'BANKACCOUNT'

AND PaymentInstrumentUsesEO.payment_function = 'CUSTOMER_PAYMENT'

AND PaymentInstrumentUsesEO.EXT_PMT_PARTY_ID = Payer.ext_payer_id

AND Payer.party_id                           = Cust.party_id

AND Payer.CUST_ACCOUNT_ID                    = Cust.CUST_ACCOUNT_ID

AND Cust.party_id                            = hp.party_id

AND Payer.org_id                            IS NULL

AND Payer.org_type                          IS NULL

UNION

SELECT PaymentInstrumentUsesEO.INSTRUMENT_PAYMENT_USE_ID,

  PaymentInstrumentUsesEO.EXT_PMT_PARTY_ID,

  PaymentInstrumentUsesEO.INSTRUMENT_TYPE,

  PaymentInstrumentUsesEO.INSTRUMENT_ID,

  PaymentInstrumentUsesEO.PAYMENT_FUNCTION,

  PaymentInstrumentUsesEO.ORDER_OF_PREFERENCE,

  PaymentInstrumentUsesEO.DEBIT_AUTH_FLAG,

  PaymentInstrumentUsesEO.DEBIT_AUTH_METHOD,

  PaymentInstrumentUsesEO.DEBIT_AUTH_REFERENCE,

  PaymentInstrumentUsesEO.DEBIT_AUTH_BEGIN,

  PaymentInstrumentUsesEO.DEBIT_AUTH_END,

  PaymentInstrumentUsesEO.START_DATE,

  PaymentInstrumentUsesEO.END_DATE,

  ibyextbankaccts.bank_account_id,

  ibyextbankaccts.bank_account_name,

  ibyextbankaccts.bank_account_number,

  ibyextbankaccts.currency_code,

  ibyextbankaccts.bank_name,

  ibyextbankaccts.bank_number,

  ibyextbankaccts.bank_branch_name,

  ibyextbankaccts.bank_account_type,

  ibyextbankaccts.branch_number,

  ibyextbankaccts.iban_number,

  ibyextbankaccts.eft_swift_code,

  'N' detailView,

  PaymentInstrumentUsesEO.PAYMENT_FLOW,

  hp.PARTY_NAME party_name,

  NULL alt_party_name,

  Cust.ACCOUNT_NUMBER party_number,

  address.party_site_name site_name,

  NULL alt_site_name,

  address.party_site_name,

  ou.NAME organization_name,

  DECODE(Payer.ACCT_SITE_USE_ID,NULL,(

  (SELECT MEANING

  FROM fnd_lookup_values_vl

  WHERE LOOKUP_TYPE = 'IBY_EXT_BNK_ACCT_ASSGN_SRCH'

  AND LOOKUP_CODE   ='CUSTOMER'

  )),(

  (SELECT MEANING

  FROM fnd_lookup_values_vl

  WHERE LOOKUP_TYPE = 'IBY_EXT_BNK_ACCT_ASSGN_SRCH'

  AND LOOKUP_CODE   ='SITE'

  ))) assignment_level,

  ibyextbankaccts.ext_bank_account_id ext_bank_account_id,

  ibyextbankaccts.bank_party_id bank_party_id,

  ibyextbankaccts.branch_party_id branch_party_id,

  DECODE(ibyextbankaccts.FOREIGN_PAYMENT_USE_FLAG, 'Y',

  (SELECT meaning

  FROM fnd_lookup_values_vl

  WHERE LOOKUP_TYPE = 'IBY_YES_NO'

  AND lookup_code   = 'Y'

  ),

  (SELECT meaning

  FROM fnd_lookup_values_vl

  WHERE LOOKUP_TYPE = 'IBY_YES_NO'

  AND lookup_code   = 'N'

  )) FOREIGN_PAYMENT_USE_FLAG,

  DECODE(ibyextbankaccts.PRIMARY_ACCT_OWNER_PARTY_ID, Payer.party_id,

  (SELECT meaning

  FROM fnd_lookup_values_vl

  WHERE LOOKUP_TYPE = 'IBY_YES_NO'

  AND lookup_code   = 'Y'

  ),

  (SELECT meaning

  FROM fnd_lookup_values_vl

  WHERE LOOKUP_TYPE = 'IBY_YES_NO'

  AND lookup_code   = 'N'

  )) primary_flag,

  Payer.party_id party_id,

  (SELECT MEANING

  FROM fnd_lookup_values_vl

  WHERE LOOKUP_TYPE = 'IBY_EXT_BNK_ACCT_ASSGN_SRCH'

  AND LOOKUP_CODE   ='CUSTOMER'

  ) party_type

FROM IBY_PMT_INSTR_USES_ALL PaymentInstrumentUsesEO,

  IBY_EXT_BANK_ACCOUNTS_V ibyextbankaccts,

  IBY_EXTERNAL_PAYERS_ALL Payer,

  HZ_CUST_ACCOUNTS Cust,

  HZ_CUST_ACCT_SITES_ALL site,

  HZ_CUST_SITE_USES_ALL site_use,

  hz_party_sites address,

  hr_operating_units ou,

  hz_parties hp

WHERE PaymentInstrumentUsesEO.instrument_id  = ibyextbankaccts.bank_account_id

AND PaymentInstrumentUsesEO.instrument_type  = 'BANKACCOUNT'

AND PaymentInstrumentUsesEO.payment_function = 'CUSTOMER_PAYMENT'

AND PaymentInstrumentUsesEO.EXT_PMT_PARTY_ID = Payer.ext_payer_id

AND Payer.party_id                           = Cust.party_id

AND Payer.CUST_ACCOUNT_ID                    = Cust.CUST_ACCOUNT_ID

AND Cust.party_id                            = hp.party_id

AND Payer.ACCT_SITE_USE_ID                   = site_use.site_use_id(+)

AND site_use.cust_acct_site_id               = site.cust_acct_site_id(+)

AND site.party_site_id                       = address.party_site_id(+)

AND Payer.org_id                             = ou.organization_id (+)

AND Payer.org_id                            IS NOT NULL

AND Payer.org_type                          IS NOT NULL

  /*AND (Payer.org_id, Payer.org_type)          IN

  (SELECT uo.organization_id,

  uo.organization_type

  FROM ce_security_profiles_v uo

  )*/

) where

PARTY_NAME = :bind_partyName

or bank_account_name = :bind_bankAccountName

or bank_account_number = :bind_bankAccountNumber

or party_number = :bind_partyNumber

Query to get list of Data Accesses of users – Oracle Fusion

    Query to get list of Data Accesses of users – Oracle Fusion

The below query extracts the list of data accesses given for any user in Oracle Cloud Application

————————————————–***************——————————————–

SELECT gl.name "Security Context Value", pr.role_name"Job Role Name", pu.username "User Name", role.ACTIVE_FLAG "Is Data Access Active"

,'DATA ACCESS SET' "Security Context"

FROM fusion.FUN_USER_ROLE_DATA_ASGNMNTS role

,fusion.gl_access_sets gl

,fusion.per_users pu

,fusion.per_roles_dn_vl pr

WHERE gl.ACCESS_SET_ID = role.ACCESS_SET_ID

AND pu.USER_GUID = role.USER_GUID

AND pr.ROLE_COMMON_NAME = role.ROLE_NAME

UNION

SELECT bu.bu_name "Security Context Value", pr.role_name"Job Role Name", pu.username "User Name", role.ACTIVE_FLAG "Is Data Access Active"

,'BUSINESS UNIT' "Security Context"

FROM fusion.FUN_ALL_BUSINESS_UNITS_V bu

,fusion.FUN_USER_ROLE_DATA_ASGNMNTS role

,fusion.per_users pu

,fusion.per_roles_dn_vl pr

WHERE role.org_id = bu.bu_id

AND pu.USER_GUID = role.USER_GUID

AND pr.ROLE_COMMON_NAME = role.ROLE_NAME

UNION

SELECT led.NAME "Security Context Value", pr.role_name "Job Role Name", pu.username "User Name", role.ACTIVE_FLAG "Is Data Access Active"

,'LEDGERS' "Security Context"

FROM fusion.GL_LEDGERS led,

fusion.FUN_USER_ROLE_DATA_ASGNMNTS role,

fusion.per_users pu,

fusion.per_roles_dn_vl pr

WHERE role.LEDGER_ID = led.LEDGER_ID

AND pu.USER_GUID = role.USER_GUID

AND pr.ROLE_COMMON_NAME = role.ROLE_NAME

UNION

SELECT book.book_type_name "Security Context Value", pr.role_name "Job Role Name", pu.username "User Name", role.ACTIVE_FLAG "Is Data Access Active"

,'ASSET BOOK' "Security Context"

FROM fusion.FUN_USER_ROLE_DATA_ASGNMNTS role,

fusion.FA_BOOK_CONTROLS book,

fusion.per_users pu,

fusion.per_roles_dn_vl pr

WHERE book.BOOK_CONTROL_ID = role.book_id

AND pu.USER_GUID = role.USER_GUID

AND pr.ROLE_COMMON_NAME = role.ROLE_NAME

UNION

SELECT interco.INTERCO_ORG_NAME "Security Context Value", pr.role_name "Job Role Name", pu.username "User Name", role.ACTIVE_FLAG "Is Data Access Active"

,'INTERCOMPANY ORGANIZATION' "Security Context"

FROM fusion.FUN_USER_ROLE_DATA_ASGNMNTS role,

fusion.FUN_INTERCO_ORGANIZATIONS interco,

fusion.per_users pu,

fusion.per_roles_dn_vl pr

WHERE interco.INTERCO_ORG_ID= role.INTERCO_ORG_ID

AND pu.USER_GUID = role.USER_GUID

AND pr.ROLE_COMMON_NAME = role.ROLE_NAME

UNION

SELECT cost.COST_ORG_NAME "Security Context Value", pr.role_name "Job Role Name", pu.username "User Name", role.ACTIVE_FLAG "Is Data Access Active"

,'COST ORGANIZATION' "Security Context"

FROM fusion.FUN_USER_ROLE_DATA_ASGNMNTS role,

fusion.CST_COST_ORGS_V cost,

fusion.per_users pu,

fusion.per_roles_dn_vl pr

WHERE cost.COST_ORG_ID= role.CST_ORGANIZATION_ID

AND pu.USER_GUID = role.USER_GUID

AND pr.ROLE_COMMON_NAME = role.ROLE_NAME

UNION

SELECT mfg.DEF_SUPPLY_SUBINV "Security Context Value", pr.role_name "Job Role Name", pu.username "User Name", role.ACTIVE_FLAG "Is Data Access Active"

,'MANUFACTURING PLANT' "Security Context"

FROM fusion.FUN_USER_ROLE_DATA_ASGNMNTS role,

fusion.RCS_MFG_PARAMETERS mfg,

fusion.per_users pu,

fusion.per_roles_dn_vl pr

WHERE mfg.ORGANIZATION_ID= role.MFG_ORGANIZATION_ID

AND pu.USER_GUID = role.USER_GUID

AND pr.ROLE_COMMON_NAME = role.ROLE_NAME

UNION

SELECT budget.NAME "Security Context Value", pr.role_name "Job Role Name", pu.username "User Name", role.ACTIVE_FLAG "Is Data Access Active"

,'CONTROL BUDGET' "Security Context"

FROM fusion.FUN_USER_ROLE_DATA_ASGNMNTS role,

fusion.XCC_CONTROL_BUDGETS budget,

fusion.per_users pu,

fusion.per_roles_dn_vl pr

WHERE budget.CONTROL_BUDGET_ID = role.CONTROL_BUDGET_ID

AND pu.USER_GUID = role.USER_GUID

AND pr.ROLE_COMMON_NAME = role.ROLE_NAME

UNION

SELECT st.SET_NAME "Security Context Value", pr.role_name "Job Role Name", pu.username "User Name", role.ACTIVE_FLAG "Is Data Access Active"

,'REFERENCE DATA SET' "Security Context"

FROM fusion.FUN_USER_ROLE_DATA_ASGNMNTS role,

fusion.FND_SETID_SETS_VL st,

fusion.per_users pu,

fusion.per_roles_dn_vl pr

WHERE st.SET_ID = role.SET_ID

AND pu.USER_GUID = role.USER_GUID

AND pr.ROLE_COMMON_NAME = role.ROLE_NAME

UNION

SELECT inv.ORGANIZATION_CODE "Security Context Value", pr.role_name "Job Role Name", pu.username "User Name", role.ACTIVE_FLAG "Is Data Access Active"

,'INVENTORY ORGANIZATION' "Security Context"

FROM fusion.FUN_USER_ROLE_DATA_ASGNMNTS role,

fusion.INV_ORG_PARAMETERS inv,

fusion.per_users pu,

fusion.per_roles_dn_vl pr

WHERE inv.ORGANIZATION_ID = role.INV_ORGANIZATION_ID

AND pu.USER_GUID = role.USER_GUID

AND pr.ROLE_COMMON_NAME = role.ROLE_NAME

UNION

SELECT hr.CLASSIFICATION_CODE "Security Context Value", pr.role_name "Job Role Name", pu.username "User Name", role.ACTIVE_FLAG "Is Data Access Active"

,'PROJECT ORGANIZATION CLASSIFICATION' "Security Context"

FROM fusion.FUN_USER_ROLE_DATA_ASGNMNTS role,

fusion.HR_ORG_UNIT_CLASSIFICATIONS_F hr,

fusion.per_users pu,

fusion.per_roles_dn_vl pr

WHERE hr.ORG_UNIT_CLASSIFICATION_ID = role.ORG_ID

AND pu.USER_GUID = role.USER_GUID

AND pr.ROLE_COMMON_NAME = role.ROLE_NAME

Fusion User Roles SQL Query & Tables

 

4 Important Tables of Oracle fusion user roles query

1.per_users

2.per_user_roles

3.per_roles_dn

4.per_roles_dn_tl

Detail SQL Query to Extract Oracle Fusion User & Roles

Query 1:-

select a1.USERNAME,

a1.ACTIVE_FLAG,

a1.CREDENTIALS_EMAIL_SENT,

a2.START_DATE,USER_ROLE_ID,

ROLE_ID,

ROLE_GUID,

ABSTRACT_ROLE,

JOB_ROLE,

DATA_ROLE,

ROLE_COMMON_NAME

from  per_users a1, per_user_roles a2,per_roles_dn a3

where a1.user_id=a2.USER_ID

and a2.ROLE_ID=a3.ROLE_ID

and a2.ROLE_GUID=a3.ROLE_GUID

Query 2:-

 

SELECT prdt.role_id, prdt.role_name,

              prdt.description RoleDescription,

              prdt.source_lang

FROM per_roles_dn_tl prdt

Query 3:-

SELECT pu.user_id,
         pu.username,
         ppnf.full_name,
         prdt.role_id,
         prdt.role_name,
         prd.role_common_name,
         prdt.description,
         TO_CHAR (pur.start_date, 'DD-MON-YYYY') role_start_date,
         TO_CHAR (pur.end_date, 'DD-MON-YYYY') role_end_date,
         prd.abstract_role,
         prd.job_role,
         prd.data_role,
         prd.duty_role,
         prd.active_flag
    FROM per_user_roles    pur,
         per_users         pu,
         per_roles_dn_tl   prdt,
         per_roles_dn      prd,
         per_person_names_f ppnf
   WHERE     1 = 1
         AND pu.user_id = pur.user_id
         AND prdt.role_id = pur.role_id
         AND prdt.language = USERENV ('lang')
         AND prdt.role_id = prd.role_id
         AND NVL (pu.suspended, 'N') = 'N'
   AND pu.username =:p_username
         AND ppnf.person_id = pu.person_id
         AND ppnf.name_type = 'GLOBAL'
         AND pu.active_flag = 'Y'
         AND NVL (pu.start_date, SYSDATE) <= SYSDATE
         AND NVL (pu.end_date, SYSDATE) >= SYSDATE
ORDER BY pu.username, prdt.role_name;