Query to get PO tax amount against po header id (zx_lines join with po) in oracle fusion

 SELECT 

       sum(((nvl(zl.taxable_amt,0) *nvl(zl.tax_rate,0))/100) )tax_amount

  FROM zx_lines              zl,

       po_line_locations_all pll,

       po_lines_all          pl,

       po_headers_all        ph

 WHERE     zl.trx_id = pll.po_header_id

       AND zl.trx_line_id = pll.line_location_id

       AND zl.entity_code = 'PURCHASE_ORDER'

       AND pl.po_header_id = pll.po_header_id

       AND pl.po_line_id = pll.po_line_id

       AND pl.po_header_id = ph.po_header_id

       AND ph.po_header_id = 300000003243945 --po_headers_all.po_header_id

       order by pl.po_line_id

Query to get PO total amount in oracle fusion

 /* Formatted on 11/24/2021 11:03:32 AM (QP5 v5.294) */

SELECT SUM (NVL ( (spl.unit_price * spl.quantity), 0)) po_total_amount

   FROM po_headers_all sph, po_lines_all spl

  WHERE     sph.po_header_id = spl.po_header_id

        AND sph.type_lookup_code = 'STANDARD'

        AND sph.po_header_id = po_headers_all.po_header_id

Query to Get Requisition creator name and Po creator name in oracle fusion

 1-To get requisition creator name 

 SELECT spn.display_name  req_creator_name

          FROM per_person_names_f_v spn

         WHERE     POR_REQUISITION_HEADERS_ALL.preparer_id = spn.PERSON_ID(+)

               AND TRUNC (SYSDATE) BETWEEN spn.EFFECTIVE_START_DATE(+)

                                       AND spn.EFFECTIVE_END_DATE(+)

2- To get PO creator name 

SELECT spn.display_name po_creator_name

  FROM per_person_names_f_v spn

 WHERE     po_headers_all.AGENT_ID = spn.PERSON_ID(+)

       AND TRUNC (SYSDATE) BETWEEN spn.EFFECTIVE_START_DATE(+)

                               AND spn.EFFECTIVE_END_DATE(+)

          

PO, CMR and XLA joins in oracle fusion

 SELECT a.po_line_id,

       a.PO_HEADER_ID,

       a.PO_NUMBER,

       a.VENDOR_ID,

       a.VENDOR_SITE_ID,

       a.PO_LINE_LOCATION_ID

  FROM cmr_purchase_order_dtls a, cmr_rcv_events b, poz_suppliers_v c

 WHERE b.cmr_po_distribution_id = a.cmr_po_distribution_id

   AND a.vendor_id = c.vendor_id

   AND a.active_flag = 'Y'

   AND b.accounting_event_id = xla_transaction_entities.source_id_int_1

RCV ,CMR and XLA joins in oracle fusion

 SELECT RSH.COMMENTS

  FROM cmr_purchase_order_dtls cpod,

       cmr_rcv_events          cre,

       CMR_TRANSACTIONS        CT,

       RCV_TRANSACTIONS        RT,

       RCV_SHIPMENT_HEADERS    RSH,

       poz_suppliers_v         ps

 WHERE     cre.cmr_po_distribution_id = cpod.cmr_po_distribution_id

       AND cpod.vendor_id = ps.vendor_id

       AND cpod.active_flag = 'Y'

       AND CRE.TRANSACTION_ID = CT.TRANSACTION_ID

       AND CT.RCV_TRANSACTION_ID = RT.TRANSACTION_ID

       AND RT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID

       AND RSH.RECEIPT_NUM = CT.RECEIPT_NUMBER

       AND cre.accounting_event_id = xla_transaction_entities.source_id_int_1