Tech Trainings

Oracle Fusion ERP – AR Cash Report Sample Query

May 24, 2022 by admin

SELECT DISTINCT
       pha.segment1                                   ponum,
       pha.revision_num                               revnum,
       pla.line_num                                   polinenum,
       pha.document_status                            postatus,
       esiv.item_number                               itemnum,
       pla.item_description                           itemdesc,
       rsh.comments                                   comments,
       rsh.receipt_num                                rcptnum
       psv.vendor_name                                supplier,
       TO_CHAR(rct.transaction_date,'Mon DD, YYYY','NLS_DATE_LANGUAGE=American')
                                                      trxdate,
       (CASE WHEN flv.meaning IN ('Receive', 'Deliver')
             THEN 'RECEIPT'
             WHEN flv.meaning = 'Return to supplier'
             THEN 'RETURN'
        END)                                          issuetype,
       DECODE(TRIM(TO_CHAR(CASE WHEN pla.unit_price IS NULL
                                THEN 0
                                ELSE pla.unit_price
                                END,
                           '9,999,999,999.99')),
                   '.00', '0.00',
              TRIM(TO_CHAR(CASE WHEN pla.unit_price IS NULL
                                THEN 0
                                ELSE pla.unit_price
                                END,
                           '9,999,999,999.99')))      currunit,
       (SELECT prh.requisition_number
          FROM fusion.por_requisition_headers_all prh,
               fusion.por_requisition_lines_all prl
         WHERE 1 = 1
           AND prh.requisition_header_id = prl.requisition_header_id
           AND prl.po_header_id = pha.po_header_id
           AND prl.po_line_id = pla.po_line_id)       prnumber,
       (SELECT itl.segment1
          FROM fusion.inv_item_locations itl
         WHERE 1 = 1
           AND itl.inventory_location_id = rsl.locator_id
           AND itl.subinventory_code = pda.destination_subinventory)
                                                      tobin,
       (SELECT rls.lot_num
          FROM fusion.rcv_lots_supply rls
         WHERE 1 = 1
           AND rls.shipment_line_id = rsl.shipment_line_id
           AND rls.transaction_id = rct.transaction_id)            lotnum,

       NVL(rct.subinventory, pda.destination_subinventory)                        subinv,
       rsh.bill_of_lading                             billoflad,
       plt.line_type                                  linetype
  FROM fusion.po_headers_all pha,
       fusion.po_lines_all pla,
       fusion.po_line_locations_all plla,
       fusion.po_distributions_all pda,
       fusion.egp_system_items_all_v esiv,
       fusion.rcv_shipment_headers rsh,
       fusion.rcv_shipment_lines rsl,
       fusion.rcv_transactions rct,
       fusion.poz_suppliers_v psv,
       fusion.fnd_lookup_values flv,
       fusion.po_line_types_vl plt
 WHERE 1 = 1
   AND pha.po_header_id = pla.po_header_id
   AND pha.po_header_id = plla.po_header_id
   AND pla.po_line_id = plla.po_line_id
   AND pha.po_header_id = pda.po_header_id
   AND pla.po_line_id = pda.po_line_id
   AND plla.line_location_id = pda.line_location_id
   AND pla.item_id = esiv.inventory_item_id(+)
   AND plla.ship_to_organization_id = esiv.organization_id(+)
   AND rsh.shipment_header_id = rsl.shipment_header_id
   AND rct.shipment_header_id = rsh.shipment_header_id
   AND rct.shipment_line_id = rsl.shipment_line_id
   AND rct.po_header_id = pha.po_header_id
   AND rct.po_line_id = pla.po_line_id
   AND rct.po_line_location_id = plla.line_location_id
   AND rct.po_distribution_id = pda.po_distribution_id
   AND psv.vendor_id = pha.vendor_id
   AND rct.transaction_type = flv.lookup_code
   AND flv.lookup_type = 'RCV_TRANSACTION_TYPE'
   AND flv.enabled_flag = 'Y'
   AND TRUNC(SYSDATE) BETWEEN TRUNC(flv.start_date_active) AND TRUNC(NVL(flv.end_date_active, SYSDATE))
   AND flv.language = USERENV('LANG')
   AND flv.lookup_code IN ('RECEIVE', 'RETURN TO VENDOR', 'DELIVER')
   AND plt.line_type_id = pla.line_type_id
   AND TRUNC(rsh.creation_date) >= TO_DATE(SUBSTR(:P_DATE_START, 1, 10), 'RRRR-MM-DD')
   AND TRUNC(rsh.creation_date) <= TO_DATE(SUBSTR(:P_DATE_END, 1, 10), 'RRRR-MM-DD')
   AND plt.line_type = NVL(:P_PO_LINE_TYPE, plt.line_type)
 ORDER BY 1, 3, 2

Comments |0|