***INVENTORY QUERIES***
--Number of Receipt Lines_Transaction Throughput
--SELECT COUNT(*) FROM
(SELECT RSH.RECEIPT_NUM "RECEIPT NUMBER"
,TO_CHAR(RCT.CREATION_DATE, 'DD-Mon-YYYY','NLS_DATE_LANGUAGE=ENGLISH') "RECEIPT CREATION DATE"
,RCT.QUANTITY "RECEIVED QUANTITY"
,PO_HDR.SEGMENT1 "PURCHASE ORDER NUMBER"
,TO_CHAR(PO_HDR.CREATION_DATE, 'DD-Mon-YYYY','NLS_DATE_LANGUAGE=ENGLISH') "PO CREATION DATE"
,PS.VENDOR_NAME "SUPPLIER NAME"
,PVS.VENDOR_SITE_CODE "SUPPLIER SITE"
,PO_LINE.line_num "PO LINE NUMBER"
,ESIB.ITEM_NUMBER "ITEM_NUMBER"
,PLTB.LINE_TYPE_CODE "PO LINE TYPE"
,PO_LINE.ATTRIBUTE2 "USD ORDER NUMBER - USD LINE NUMBER"
,PO_LINE.ATTRIBUTE1 "USD LINE TYPE"
,(LINE_LOC.QUANTITY-LINE_LOC.QUANTITY_CANCELLED) "ORDERED QUANTITY"
,ITLN.LOT_NUMBER "LOT NUMBER"
FROM PO_HEADERS_ALL PO_HDR
,PO_LINES_ALL PO_LINE
,PO_LINE_LOCATIONS_ALL LINE_LOC
,POZ_SUPPLIERS_V PS
,POZ_SUPPLIER_SITES_V PVS
,PO_LINE_TYPES_B PLTB
,RCV_TRANSACTIONS RCT
,RCV_SHIPMENT_HEADERS RSH
,RCV_SHIPMENT_LINES RSL
,INV_MATERIAL_TXNS IMT
,INV_TRANSACTION_LOT_NUMBERS ITLN
,EGP_SYSTEM_ITEMS_B ESIB
WHERE PO_HDR.PO_HEADER_ID = PO_LINE.PO_HEADER_ID
AND PO_LINE.PO_LINE_ID = LINE_LOC.PO_LINE_ID
AND PO_LINE.LINE_TYPE_ID = PLTB.LINE_TYPE_ID
AND PO_HDR.VENDOR_ID = PS.VENDOR_ID
AND PO_HDR.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID
AND PO_LINE.PO_LINE_ID = RCT.PO_LINE_ID
AND PO_LINE.ITEM_ID = ESIB.INVENTORY_ITEM_ID
AND LINE_LOC.SHIP_TO_ORGANIZATION_ID = ESIB.ORGANIZATION_ID
AND RCT.transaction_type = 'DELIVER'
AND RCT.shipment_header_id = RSH.shipment_header_id
AND RSH.shipment_header_id = RSL.shipment_header_id
AND RCT.TRANSACTION_ID = IMT.RCV_TRANSACTION_ID
AND IMT.TRANSACTION_ID = ITLN.TRANSACTION_ID
AND PO_LINE.PO_LINE_ID = RSL.PO_LINE_ID
AND TO_CHAR(RCT.CREATION_DATE, 'YYYY-MM-DD') BETWEEN TO_CHAR(:P_FROM_DATE,'YYYY-MM-DD') AND TO_CHAR(:P_TO_DATE,'YYYY-MM-DD')
ORDER BY RSH.RECEIPT_NUM, PO_HDR.SEGMENT1, PO_LINE.LINE_NUM
)
--Number of SO Lines Awaiting Billing
--SELECT COUNT(*) FROM
( SELECT DHA.HEADER_ID HEADER_ID,
DHA.ORDER_NUMBER ORDER_NUMBER,
DLA.DISPLAY_LINE_NUMBER LINE_NUMBER,
DFLA.CUSTOMER_PO_NUMBER CUSTOMER_PO_NUMBER,
HOU.NAME SELLING_PROFIT_CENTER_BU_NAME,
RCTTA.NAME RECEIVABLES_TRANSACTION,
DFLA.STATUS_CODE LINE_STATUS
FROM DOO_HEADERS_ALL DHA,
DOO_LINES_ALL DLA,
DOO_FULFILL_LINES_ALL DFLA,
HR_OPERATING_UNITS HOU,
RA_CUST_TRX_TYPES_ALL RCTTA
WHERE DHA.HEADER_ID = DLA.HEADER_ID
AND DLA.LINE_ID = DFLA.LINE_ID
AND DFLA.SELLING_PROFIT_CENTER_BU_ID = HOU.ORGANIZATION_ID
AND DFLA.BILLING_TRX_TYPE_ID = RCTTA.CUST_TRX_TYPE_SEQ_ID
AND DFLA.STATUS_CODE = 'AWAIT_BILLING'
AND DHA.OBJECT_VERSION_NUMBER = ( SELECT MAX(OBJECT_VERSION_NUMBER)
FROM DOO_HEADERS_ALL DHA_LATEST
WHERE DHA_LATEST.ORDER_NUMBER = DHA.ORDER_NUMBER
AND DHA_LATEST.STATUS_CODE = DHA.STATUS_CODE
)
)
--Number of DS Lines with Supplier Invoice on Hold
--SELECT COUNT(*) FROM
(SELECT distinct
AIA.INVOICE_NUM "INVOICE NUMBER"
,TO_CHAR(AIA.CREATION_DATE, 'DD-Mon-YYYY','NLS_DATE_LANGUAGE=ENGLISH') "CREATION DATE"
,AILA.LINE_NUMBER "LINE NUMBER"
,AILA.UNIT_PRICE "LINE UNIT PRICE"
,AILA.QUANTITY_INVOICED "QUANTITY"
,AHA.HOLD_LOOKUP_CODE "HOLD NAME"
,PO_HDR.SEGMENT1 "MATCHED PO NUMBER"
,PO_LINE.LINE_NUM "MATCHED PO LINE NUMBER"
FROM AP_INVOICES_ALL AIA
,AP_INVOICE_LINES_ALL AILA
,PO_HEADERS_ALL PO_HDR
,PO_LINES_ALL PO_LINE
,PO_LINE_LOCATIONS_ALL LINE_LOC
,PO_LINE_TYPES_B PLTB
,AP_HOLDS_ALL AHA
WHERE AIA.INVOICE_ID = AILA.INVOICE_ID
AND AILA.PO_LINE_LOCATION_ID = LINE_LOC.LINE_LOCATION_ID
AND LINE_LOC.PO_LINE_ID = PO_LINE.PO_LINE_ID
AND PO_LINE.PO_HEADER_ID = PO_HDR.PO_HEADER_ID
AND AIA.INVOICE_ID = AHA.INVOICE_ID
AND PO_LINE.LINE_TYPE_ID = PLTB.LINE_TYPE_ID
AND PLTB.LINE_TYPE_CODE = 'DS'
AND PO_LINE.LINE_STATUS != 'CLOSED'
AND AILA.DISCARDED_FLAG = 'N'
AND AIA.APPROVAL_STATUS = 'NEEDS REAPPROVAL'
AND AIA.INVOICE_TYPE_LOOKUP_CODE = 'STANDARD'
AND PO_HDR.DOCUMENT_STATUS in ('OPEN','CLOSED FOR INVOICING')
AND AHA.HOLD_LOOKUP_CODE != 'QTY REC'
ORDER BY TO_CHAR(AIA.CREATION_DATE, 'DD-Mon-YYYY','NLS_DATE_LANGUAGE=ENGLISH'), AIA.INVOICE_NUM, AILA.LINE_NUMBER, PO_HDR.SEGMENT1, PO_LINE.LINE_NUM
)