Query To Fetch AP Invoice Details From SO Number(Doc ID 2949013.1)

SELECT dh.source_order_number

      ,df.source_line_number as so_line_number

  ,df.fulfill_line_number 

  ,ddr.doc_user_key as po_number

  ,ai.invoice_num

  ,ail.quantity_invoiced

  ,ail.amount

  ,ail.line_number as ap_invoice_line_number

  FROM fusion.ap_invoices_all ai

      ,fusion.ap_invoice_lines_all ail

  ,fusion.doo_document_references ddr

  ,fusion.doo_headers_all dh

  ,fusion.doo_fulfill_lines_all df

 WHERE ai.invoice_id = ail.invoice_id

   AND dh.source_order_number = <sales_order_number>

   AND ddr.doc_id = TO_CHAR(ai.po_header_id)

   AND ddr.fulfill_line_id = df.fulfill_line_id

   AND df.header_id = dh.header_id

Query to get Item Category Details in Oracle Fusion

SELECT cba.bank_account_name

      ,cba.bank_account_id

      ,cba.bank_account_name_alt

      ,cba.bank_account_num

      ,cba.multi_currency_allowed_flag

      ,cba.zero_amount_allowed

      ,cba.account_classification

      ,cbb.bank_name

      ,cba.bank_id

      ,cbb.bank_number

      ,cbb.bank_branch_type

      ,cbb.bank_branch_name

      ,cba.bank_branch_id

      ,cbb.bank_branch_number

      ,cbb.eft_swift_code

      ,cbb.description bank_description

      ,cba.currency_code

      ,cbb.address_line1

  ,cbb.address_line2

      ,cbb.city

      ,cbb.county

      ,cbb.state

      ,cbb.zip_code

      ,cbb.country

      ,hou.name

      ,gcf.concatenated_segments

      ,cba.ap_use_allowed_flag

      ,cba.ar_use_allowed_flag

      ,cba.xtr_use_allowed_flag

      ,cba.pay_use_allowed_flag

  FROM ce_bank_accounts cba

      ,ce_bank_acct_uses_all bau

      ,cefv_bank_branches cbb

      ,hr_operating_units hou

      ,gl_code_combinations_kfv gcf

 WHERE cba.bank_account_id = bau.bank_account_id

   AND cba.bank_branch_id = cbb.bank_branch_id

   AND hou.organization_id = bau.org_id

   AND cba.asset_code_combination_id = gcf.code_combination_id

   AND (

cba.end_date IS NULL OR cba.end_date > TRUNC(SYSDATE)

       )

   AND hou.name = 'XX Operating Unit'

ORDER BY cba.bank_account_num

Query for AR Sales Representative Name in Oracle Fusion

SELECT hp.party_name sales_representative

  FROM ra_customer_trx_all rcta

      ,jtf_rs_salesreps jrs

  ,hz_parties hp

 WHERE rcta.primary_resource_salesrep_id=jrs.resource_salesrep_id

   AND jrs.resource_id=hp.party_id

Join between Sales Order(SO) and Receivables(AR) in Oracle Fusion

SELECT *

  FROM ra_customer_trx_all rcta

      ,ra_customer_trx_lines_all ra

  ,doo_fulfill_lines_all l

 WHERE ra.interface_line_attribute5 = TO_CHAR(l.fulfill_line_id)

   AND rcta.customer_trx_id = ra.customer_trx_id

   AND rcta.trx_number = '123456789';

You can use INTERFACE_LINE_ATTRIBUTE5 column in RA_CUSTOMER_TRX_LINES_ALL which stores Fulfill Line ID of Sales Order.