P2P Report

➜ Back to Library
Description
SELECT 
    operating_unit ,
    requisition_number,
    requisition_type,
    requisition_date ,
    requisition_status ,
    requisition_user_name,
    requisition_line_number,
    requisition_item_name,
    requisition_item_description,
    note_to_agent ,
    cost_center,
    project_number,
    task_number,
    requisition_quantity,
    requisition_line_amount,
    po_date,
    po_number  ,
    po_created_by,
     po_approval_status ,
     po_line_status,
     po_quantity ,
     po_amount ,
     currency_code,
     destination_type,
     inspection_required,
     vendor_name ,
     vendor_site,
     receipt_creation_date,
     receipt_number,
     receipt_quantity,
     invoice_date ,
      invoice_number ,
      invoice_amount,
     invoice_due_date 
FROM
    (      
            SELECT
            haouv.NAME operating_unit,
            prha.segment1       requisition_number,
            xxen_util.meaning(prha.type_lookup_code,'REQUISITION TYPE',201) requisition_type,
            nvl(xxen_util.meaning(prha.authorization_status,'AUTHORIZATION STATUS',201) ,prha.authorization_status) requisition_status,
            ppx.full_name  requisition_user_name,
            xxen_util.meaning(prha.closed_code,'DOCUMENT STATE',201) requisition_closed_status,
            gcck.segment2       cost_center,
            TRUNC(prha.creation_date)   requisition_date,
            prla.line_num        requisition_line_number,
            (SELECT DISTINCT msiv.segment1 FROM mtl_system_items_vl msiv
                WHERE msiv.inventory_item_id = prla.item_id
                ) requisition_item_name,
            prla.item_description requisition_item_description,
            prla.note_to_agent ,
            (select distinct ppa.segment1 from pa_projects_all ppa where ppa.project_id = prda.project_id) project_number,
            (select distinct pt.task_number from pa_tasks pt where pt.task_id = prda.task_id) task_number,
            prla.quantity        requisition_quantity,
            ( nvl(prla.quantity, 0) * nvl(prla.unit_price, 0) ) requisition_line_amount,
           xxen_util.client_time(pha.creation_date) po_date,
            pha.segment1 po_number,
             xxen_util.user_name(pha.created_by) po_created_by,
    	     xxen_util.meaning(pha.authorization_status , 'DOCUMENT STATE', 201)   po_approval_status,
              coalesce(
                           xxen_util.meaning(plla.closed_code,'DOCUMENT STATE',201),
                           xxen_util.meaning(pla.closed_code,'DOCUMENT STATE',201),
                            xxen_util.meaning(pha.closed_code,'DOCUMENT STATE',201) 
                             ) po_line_status,
            plla.quantity        po_quantity,
            pla.line_num         po_line_number,
            ( nvl(plla.quantity, 0) * nvl(pla.unit_price, 0) ) po_amount,
             pha.currency_code currency_code,
             xxen_util.meaning(decode(plla.inspection_required_flag,'Y','Y'),'YES_NO',0)  inspection_required,
             xxen_util.meaning(pda.destination_type_code,'DESTINATION TYPE',201) destination_type,
			pv.vendor_name vendor_name,
                            pvs.vendor_site_code  vendor_site,
	      xxen_util.client_time( rsh.creation_date) receipt_creation_date,
            rsh.receipt_num receipt_number,
            rsl.quantity_received  receipt_quantity,
            xxen_util.client_time(aia.invoice_date)   invoice_date,
            aia.invoice_num    invoice_number,
             aia.invoice_amount,
            xxen_util.client_time(apsa.due_date)      invoice_due_date
        FROM
            po_requisition_headers_all   prha,
            po_requisition_lines_all     prla,
            po_headers_all               pha,
            po_lines_all                 pla,
            po_line_locations_all        plla,
            po_distributions_all         pda,
            po_req_distributions_all     prda,
             hr_all_organization_units_vl haouv,
             rcv_shipment_headers rsh,
             rcv_shipment_lines rsl,
            rcv_transactions             rct,
            ap_invoice_distributions_all      aid,
            ap_invoices_all                   aia,
            ap_payment_schedules_all          apsa,
            per_people_x              ppx,
            gl_code_combinations_kfv          gcck,
	    po_vendors                   pv,
             po_vendor_sites        pvs
        WHERE
 
                 pha.po_header_id = pla.po_header_id (+)
            AND pla.po_line_id = plla.po_line_id (+)
            AND plla.line_location_id = pda.line_location_id (+)
            AND pda.req_distribution_id = prda.distribution_id (+)
            AND prla.requisition_line_id = prda.requisition_line_id (+)
            AND prha.requisition_header_id = prla.requisition_header_id (+)
            AND prha.org_id=haouv.organization_id
            AND pla.po_line_id = rct.po_line_id (+)
            AND plla.line_location_id = rct.po_line_location_id (+)
            AND pda.po_distribution_id = rct.po_distribution_id (+)
            AND rsh.shipment_header_id = rct.shipment_header_id
            AND rsl.shipment_line_id = rct.shipment_line_id
            AND rct.transaction_type (+)  IN ('RECEIVE') 
            AND pda.po_distribution_id = aid.po_distribution_id (+)
            AND aid.invoice_id = aia.invoice_id (+)
            AND aia.invoice_id = apsa.invoice_id (+)
            AND prha.preparer_id = ppx.person_id
            AND prda.code_combination_id = gcck.code_combination_id
         	AND pha.vendor_id = pv.vendor_id
          AND pha.vendor_site_id = pvs.vendor_site_id  
        UNION ALL
        SELECT 
           haouv.NAME operating_unit,
            prha.segment1        requisition_number,
            xxen_util.meaning(prha.type_lookup_code,'REQUISITION TYPE',201) requisition_type,
	      prha.authorization_status        requisition_status,
            ppx.full_name   requisition_user_name,
            xxen_util.meaning(prha.closed_code,'DOCUMENT STATE',201) requisition_closed_status,
            gcck.segment2  cost_center,
            prha.creation_date   requisition_date,
            prla.line_num        requisition_line_number,
            (SELECT msib.segment1 FROM mtl_system_items_b msib
                WHERE msib.inventory_item_id = prla.item_id
                AND ROWNUM <= 1) requisition_item_name,
            prla.item_description requisition_item_description,
              prla.note_to_agent ,
               (select distinct ppa.segment1 from pa_projects_all ppa where ppa.project_id = prda.project_id) project_number,
            (select distinct  pt.task_number from pa_tasks pt where pt.task_id = prda.task_id) task_number,
            prla.quantity        requisition_quantity,
            ( nvl(prla.quantity, 0) * nvl(prla.unit_price, 0) ) requisition_line_amount,
            NULL po_date,
            NULL po_number,
             NULL po_created_by,
	    NULL po_approval_status,
              NULL po_line_status,
            NULL po_quantity,
            NULL po_line_number,
            NULL po_amount,
             prla.currency_code  currency_code,
              NULL destination_type,
              NULL  inspection_required,
	     (select  pv.vendor_name from po_vendors pv where pv.vendor_id=prla.vendor_id) vendor_name,
              (select  pvs.vendor_site_code from po_vendor_sites pvs where pvs.vendor_site_id=prla.vendor_site_id)  vendor_site,
            NULL receipt_creation_date,
            NULL receipt_number,
            NULL receipt_quantity,
            NULL invoice_date,
            NULL invoice_number,
            NULL invoice_amount,
            NULL invoice_due_date
        FROM
            po_requisition_headers_all   prha,
            po_requisition_lines_all     prla,
            po_req_distributions_all     prda,
            per_people_x              ppx,
            hr_all_organization_units_vl haouv,
            gl_code_combinations_kfv          gcck
        WHERE
              prha.requisition_header_id = prla.requisition_header_id (+)
            AND prla.requisition_line_id = prda.requisition_line_id (+)
            AND prha.preparer_id = ppx.person_id
            AND prha.org_id=haouv.organization_id
            AND prda.code_combination_id = gcck.code_combination_id
            AND NOT EXISTS ( SELECT 1 FROM po_distributions_all pda
                            WHERE prda.distribution_id = pda.req_distribution_id)
    )
WHERE 1 = 1
          AND requisition_date >= DATE '2005-01-01'
           AND requisition_date <  DATE '2007-01-02'
Download
Related Blitz Reports
ECC Procurement, Purchase Orders PO Receiving Transactions Pipeline PO Endeca Template GL Account Analysis (Distributions) PO Requisition Upload Procure to Pay Detailed Report Test GL Account Distribution Analysis ECC Procurement, Requisitions ECC Project Manufacturing, Procurement Activities, SQL2
Blitz Report™