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 items.item_number

      ,itemcats.category_id

  ,itemcats.sequence_number

  ,itemcats.alt_item_cat_code

  ,ect.description

  ,ect.category_name

  FROM egp_item_cat_assignments itemcats

      ,egp_category_sets_b catsets

  ,egp_system_items_b items

  ,inv_org_parameters org

  ,egp_categories_tl ect

 WHERE catsets.category_set_id = itemcats.category_set_id

   AND items.inventory_item_id = itemcats.inventory_item_id

   AND org.organization_id = items.organization_id

   AND itemcats.category_id=ect.category_id

   AND ( ( catsets.control_level = 1

AND itemcats.organization_id = org.master_organization_id 

     )

  OR ( catsets.control_level = 2

AND itemcats.organization_id = org.organization_id

  ) 

)

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.