Purchasing

--Number of POs Created

SELECT ph.segment1                                             OrderNumber,

       ph.creation_date                                        OrderCreationDate

       ,

       ph.document_status

       OrderStatus,

       SupParty.party_name                                     SupplierName,

       supsite.vendor_site_code                                SupplierSite,

       Substr(pl.attribute2, 1, Instr(pl.attribute2, '-') - 1) USDOrderNumber,

       Decode(pl.attribute1, 'IO', 'Y',

                             'STK', 'Y',

                             'DS', 'N',

                             'N')

       InternalSupplierFlag,

       ph.attribute1                                           ShippingMethod,

       ph.attribute2                                           FOB,

       ph.freight_terms_lookup_code                            FreightTerms,

       ph.attribute3                                           EdiFlag,

       (SELECT Count(1)

        FROM   po_lines_all pol

        WHERE  pol.po_header_id = ph.po_header_id

        GROUP  BY po_header_id)                                LineCount

FROM   po_headers_all ph,

       po_lines_all pl,

       poz_suppliers_v SUP,

       poz_supplier_sites_v SUPSITE,

       hz_parties SupParty

WHERE  ph.creation_date LIKE Cast (:p_date AS DATE)

                             || '%'

       AND ph.po_header_id = pl.po_header_id

       AND PH.vendor_id = SUP.vendor_id

       AND SUP.party_id = SupParty.party_id

       AND PH.vendor_site_id = SUPSITE.vendor_site_id

       AND pl.line_num = 1 

  /

  --No of External DS PO Lines Created 

  SELECT ph.segment1                OrderNumber,

       ph.creation_date           OrderCreationDate,

       ph.document_status         OrderStatus,

       SupParty.party_name        SupplierName,

       supsite.vendor_site_code   SupplierSite,

       pl.attribute2              USDOrderNumberLineNumber,

       Decode(pl.attribute1, 'IO', 'Y',

                             'STK', 'Y',

                             'DS', 'N',

                             'N') InternalSupplierFlag,

       pl.line_num                POLineNumber,

       plt.line_type_code         POLineType,

       Pl.quantity,

       pl.unit_price,

       pl.line_status,

       pol.requested_ship_date,

       Item.item_number

FROM   po_headers_all ph,

       po_lines_all pl,

       po_line_locations_all pol,

       po_line_types_b plt,

       poz_suppliers_v SUP,

       poz_supplier_sites_v SUPSITE,

       hz_parties SupParty,

       egp_system_items_vl item

WHERE  ph.creation_date LIKE Cast (:p_date AS DATE)

                             || '%'

       AND ph.po_header_id = pl.po_header_id

       AND Pol.po_header_id = ph.po_header_id

       AND pol.po_line_id = pl.po_line_id

       AND pl.attribute1 = 'DS'

       AND plt.line_type_code = 'DS'

       AND plt.line_type_id = pl.line_type_id

       AND PH.vendor_id = SUP.vendor_id

       AND SUP.party_id = SupParty.party_id

       AND PH.vendor_site_id = SUPSITE.vendor_site_id

       AND Item.inventory_item_id = Pl.item_id 

  /

  -- Number of Internal DS PO Lines Created

  SELECT ph.segment1                OrderNumber,

       ph.creation_date           OrderCreationDate,

       ph.document_status         OrderStatus,

       SupParty.party_name        SupplierName,

       supsite.vendor_site_code   SupplierSite,

       pl.attribute2              USDOrderNumberLineNumber,

       Decode(pl.attribute1, 'IO', 'Y',

                             'STK', 'Y',

                             'DS', 'N',

                             'N') InternalSupplierFlag,

       pl.line_num                POLineNumber,

       plt.line_type_code         POLineType,

       Pl.quantity,

       pl.unit_price,

       pl.line_status,

       pol.requested_ship_date,

       Item.item_number

FROM   po_headers_all ph,

       po_lines_all pl,

       po_line_locations_all pol,

       po_line_types_b plt,

       poz_suppliers_v SUP,

       poz_supplier_sites_v SUPSITE,

       hz_parties SupParty,

       egp_system_items_vl item

WHERE  ph.creation_date LIKE Cast (:p_date AS DATE)

                             || '%'

       AND ph.po_header_id = pl.po_header_id

       AND Pol.po_header_id = ph.po_header_id

       AND pol.po_line_id = pl.po_line_id

       AND pl.attribute1 IN ('IO','STK')

       AND plt.line_type_code = 'DS'

       AND plt.line_type_id = pl.line_type_id

       AND PH.vendor_id = SUP.vendor_id

       AND SUP.party_id = SupParty.party_id

       AND PH.vendor_site_id = SUPSITE.vendor_site_id

       AND Item.inventory_item_id = Pl.item_id 

  /

  --Number of Requisition Lines without PO 

  SELECT prh.requisition_header_id,

       prh.requisition_number,

       prl.requisition_line_id,

       prl.line_number,

       prl.line_status,

       prl.attribute2 UsdOrderNumberLineNumber,

       Item.item_number

FROM   por_requisition_lines_all prl,

       por_requisition_headers_all prh,

       egp_system_items_vl Item,

  inv_org_parameters iop

WHERE  prl.po_line_id IS NULL

       AND item.inventory_item_id = prl.item_id 

AND  Item.organization_id = iop.ORGANIZATION_ID  

AND iop.organization_code = 'IMO' 

AND prl.requisition_header_id=prh.requisition_header_id

/

--Number of PO Lines Not approved 

SELECT ph.segment1                                           ordernumber,

       ph.creation_date                                      ordercreationdate,

       ph.document_status                                    orderstatus,

       supparty.party_name                                   suppliername ,

       supsite.vendor_site_code                              suppliersite,

       pl.attribute2                                         usdordernumber,

       Decode(pl.attribute1,'IO','Y','STK','Y','DS','N','N') internalsupplierflag,

       pl.line_num                                           polinenumber,

       plt.line_type_code                                    polinetype,

       pl.quantity ,

       pl.unit_price,

       pl.line_status,

       pol.requested_ship_date,

       item.item_number

FROM   po_headers_all ph,

       po_lines_all pl,

       po_line_locations_all pol,

       po_line_types_b plt,

       poz_suppliers_v SUP,

       poz_supplier_sites_v SUPSITE,

       hz_parties SupParty,

       egp_system_items_vl item

WHERE  ph.document_status = 'INCOMPLETE'

AND    ph.po_header_id=pl.po_header_id

AND    pol.po_header_id=ph.po_header_id

AND    pol.po_line_id=pl.po_line_id

AND    ph.vendor_id = sup.vendor_id

AND    sup.party_id=supparty.party_id

AND    ph.vendor_site_id = supsite.vendor_site_id

AND    item.inventory_item_id=pl.item_id

/

--Requisition Approval Time Interval

SELECT

       requisition_number,

       document_status,

       creation_date,

       submission_date,

       approved_date,

       Extract (hour FROM ( submission_date - creation_date ))

       || ' hours '

       || Extract (minute FROM ( submission_date - creation_date ))

       || ' minutes '

       || Extract (second FROM ( submission_date - creation_date ))

       || ' seconds ' AS Submisstion_time_interval,

       Extract (hour FROM ( approved_date - submission_date ))

       || ' hours '

       || Extract (minute FROM ( approved_date - submission_date ))

       || ' minutes '

       || Extract (second FROM ( approved_date - submission_date ))

       || ' seconds ' AS Approval_time_interval

FROM   por_requisition_headers_all 

where document_status='APPROVED'