PO Receiving Transactions Register

SELECT RCT.TRANSACTION_ID TX_ID
,HRU.NAME ORG
,PLC1.DISPLAYED_FIELD SRC_TYPE
,POV.VENDOR_NAME SRC
,DECODE(POL.purchase_basis,
'TEMP LABOR',POL.contractor_first_name || ' ' || POL.contractor_last_name,
null) C_FLEX_ITEM
,null C_FLEX_CAT
,RSL.ITEM_REVISION REV
,RSL.ITEM_DESCRIPTION DES
,RSH.RECEIPT_NUM RECEIPT_NUM
,POH.SEGMENT1 || DECODE (POR.RELEASE_NUM,NULL,NULL,'-' || POR.RELEASE_NUM) || DECODE (POL.LINE_NUM,NULL,NULL,'-' || POL.LINE_NUM) || DECODE (PLL.SHIPMENT_NUM,NULL,NULL,'-'|| PLL.SHIPMENT_NUM) DOC_NUM
,TO_CHAR(RCT.TRANSACTION_DATE,'DD/MON/YYYY HH24:MI:SS') TX_DATE
,PLC2.DISPLAYED_FIELD TX_TYPE
,DECODE(POL.matching_basis,
'AMOUNT',RCT.currency_code,
RCT.UNIT_OF_MEASURE) UOM
,DECODE(POL.matching_basis,
'AMOUNT',NULL,
PLL.UNIT_MEAS_LOOKUP_CODE) PO_UOM
,round(DECODE(POL.matching_basis,
'AMOUNT',RCT.amount,
RCT.QUANTITY),
:P_qty_precision) QTY
,RCT.PO_UNIT_PRICE PRICE
,PDT.TYPE_NAME DOC_TYPE
,P2.FULL_NAME BUYER_PREPARER
,P1.FULL_NAME RECEIVER
,RSH.PACKING_SLIP PCKING_SLIP
,RCT.CREATION_DATE CREATION_DATE
,RCT.INSPECTION_QUALITY_CODE Q_CODE
,DECODE(RCT.RECEIPT_EXCEPTION_FLAG,'Y',:yes,'N',:no,null) EXCEPTION
,PLC3.DISPLAYED_FIELD PAR_TX_TYPE
,PLC.DISPLAYED_FIELD DESTINATION_TYPE
,P3.FULL_NAME DELIVER_TO_PERSON
,LOT.LOCATION_CODE DELIVER_TO_LOCATION
,RCT.SUBINVENTORY DESTINATION_SUBINVENTORY
,null C_FLEX_LOCATOR
,MTR.REASON_NAME TX_REASON
,RSL.ITEM_ID LS_ITEM_ID
,RCT.TRANSACTION_ID LS_TRANSACTION_ID
,RCT.SHIPMENT_LINE_ID LS_SHIPMENT_LINE_ID
,RCT.ORGANIZATION_ID LS_ORGANIZATION_ID
,WLPN1.LICENSE_PLATE_NUMBER LICENSE_PLATE_NUMBER,
nvl2(RSL.ITEM_ID,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_item_disp','INV','MSTK',101,MSI.ORGANIZATION_ID,MSI.INVENTORY_ITEM_ID,'ALL','Y','VALUE'),null) C_flex_item_disp,
nvl2(RSL.CATEGORY_ID,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_cat_disp','INV','MCAT',MCA.STRUCTURE_ID,NULL,MCA.CATEGORY_ID,'ALL','Y','VALUE'),null) C_flex_cat_disp,
nvl2(RCT.locator_id,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_locator_disp','INV','MTLL',101,MSL.ORGANIZATION_ID,MSL.INVENTORY_LOCATION_ID,'ALL','Y','VALUE'),null) C_FLEX_LOCATOR_DISP,
PO_RCVTXRTR_new_XMLP_PKG.rcv_uom_convertformula(DECODE (POL.matching_basis,'AMOUNT',NULL,PLL.UNIT_MEAS_LOOKUP_CODE),DECODE (POL.matching_basis,'AMOUNT',RCT.currency_code,RCT.UNIT_OF_MEASURE),RSL.ITEM_ID,RCT.PO_UNIT_PRICE) RCV_UOM_CONVERT
FROM
PO_LOOKUP_CODES PLC,
PO_LOOKUP_CODES PLC1,
PO_LOOKUP_CODES PLC2,
PO_LOOKUP_CODES PLC3,
PO_RELEASES_ALL POR,
PO_DOCUMENT_TYPES PDT,
PO_DISTRIBUTIONS_ALL POD,
PER_PEOPLE_F P2,
PER_PEOPLE_F P3,
HR_LOCATIONS_ALL_TL LOT,
MTL_TRANSACTION_REASONS MTR,
PO_VENDORS POV,
HR_ORGANIZATION_UNITS HRU,
MTL_SYSTEM_ITEMS MSI,
MTL_CATEGORIES MCA,
MTL_ITEM_LOCATIONS MSL,
PO_HEADERS POH,
PO_LINES_ALL POL,
PO_LINE_LOCATIONS_ALL PLL,
RCV_TRANSACTIONS PAR,
RCV_SHIPMENT_LINES RSL,
RCV_SHIPMENT_HEADERS RSH,
PER_PEOPLE_F P1,
RCV_TRANSACTIONS RCT,
WMS_LICENSE_PLATE_NUMBERS WLPN1
WHERE MSI.INVENTORY_ITEM_ID (+)=RSL.ITEM_ID
AND nvl(MSI.ORGANIZATION_ID,:organization_id)=:organization_id
AND MCA.CATEGORY_ID=RSL.CATEGORY_ID
AND MSL.INVENTORY_LOCATION_ID (+)=RCT.LOCATOR_ID
AND nvl(MSL.ORGANIZATION_ID,RCT.ORGANIZATION_ID)=RCT.ORGANIZATION_ID
AND RCT.SOURCE_DOCUMENT_CODE='PO'
AND RSH.RECEIPT_SOURCE_CODE || ''='VENDOR'
AND RCT.SHIPMENT_LINE_ID=RSL.SHIPMENT_LINE_ID
AND RCT.SHIPMENT_HEADER_ID=RSH.SHIPMENT_HEADER_ID
AND RCT.PO_HEADER_ID=POH.PO_HEADER_ID
AND RCT.PO_LINE_ID=POL.PO_LINE_ID
AND RCT.PO_LINE_LOCATION_ID=PLL.LINE_LOCATION_ID
AND RCT.PO_DISTRIBUTION_ID=POD.PO_DISTRIBUTION_ID (+)
AND RCT.PO_RELEASE_ID=POR.PO_RELEASE_ID (+)
AND ((POH.TYPE_LOOKUP_CODE='STANDARD'
AND
PDT.DOCUMENT_TYPE_CODE='PO'
AND
PDT.DOCUMENT_SUBTYPE='STANDARD')
OR
(PDT.DOCUMENT_TYPE_CODE='RELEASE'
AND
PDT.DOCUMENT_SUBTYPE=NVL(POR.RELEASE_TYPE,'~')))
AND (RCT.EMPLOYEE_ID=P1.PERSON_ID (+)
AND trunc(RCT.CREATION_DATE) BETWEEN
NVL(P1.EFFECTIVE_START_DATE,trunc(RCT.CREATION_DATE)) AND
NVL(P1.EFFECTIVE_END_DATE,trunc(RCT.CREATION_DATE)))
AND (POH.AGENT_ID=P2.PERSON_ID (+)
AND trunc(POH.APPROVED_DATE) BETWEEN
NVL(P2.EFFECTIVE_START_DATE,trunc(POH.APPROVED_DATE))
AND NVL(P2.EFFECTIVE_END_DATE,trunc(POH.APPROVED_DATE)))
AND PLC1.LOOKUP_CODE=RSH.RECEIPT_SOURCE_CODE || ''
AND PLC1.LOOKUP_TYPE='SHIPMENT SOURCE TYPE'
AND RSH.VENDOR_ID=POV.VENDOR_ID
AND PAR.TRANSACTION_ID (+)=RCT.PARENT_TRANSACTION_ID
AND LOT.LOCATION_ID(+)=RCT.DELIVER_TO_LOCATION_ID
AND LOT.LANGUAGE(+)=USERENV('LANG')
AND (P3.PERSON_ID(+)=RCT.DELIVER_TO_PERSON_ID
and trunc(RCT.CREATION_DATE) BETWEEN
NVL(P3.EFFECTIVE_START_DATE,trunc(RCT.CREATION_DATE)) AND
NVL(P3.EFFECTIVE_END_DATE,trunc(RCT.CREATION_DATE)))
AND PLC.LOOKUP_CODE=RCT.DESTINATION_TYPE_CODE
AND PLC.LOOKUP_TYPE='RCV DESTINATION TYPE'
AND PLC2.LOOKUP_TYPE='RCV TRANSACTION TYPE'
AND PLC2.LOOKUP_CODE=RCT.TRANSACTION_TYPE
AND PLC3.LOOKUP_TYPE (+)='RCV TRANSACTION TYPE'
AND PLC3.LOOKUP_CODE (+)=PAR.TRANSACTION_TYPE
AND HRU.ORGANIZATION_ID=RCT.ORGANIZATION_ID
AND &P_WHERE_ORG_ID
AND MTR.REASON_ID(+)=RCT.REASON_ID
AND &P_WHERE_RECEIPT_NUM_FROM
AND &P_WHERE_RECEIPT_NUM_TO
AND &P_WHERE_SHIP_NUM_FROM
AND &P_WHERE_SHIP_NUM_TO
AND &P_WHERE_ITEM
AND &P_WHERE_CAT
AND &P_WHERE_PO_NUM_FROM
AND &P_WHERE_PO_NUM_TO
AND &P_WHERE_TRX_DATE_FROM
AND &P_WHERE_TRX_DATE_TO
AND &P_WHERE_TRX_TYPE
AND nvl(P1.FULL_NAME,'~')=nvl(:P_receiver,nvl(P1.FULL_NAME,'~'))
AND &P_WHERE_BUYER
AND &P_WHERE_VENDOR_FROM
AND &P_WHERE_VENDOR_TO
AND ((:P_req_num_from is null
and :P_req_num_to is null
and :P_rma_num_from is null
and :P_rma_num_to is null
and :P_customer_from is null
and :P_customer_to is null)
OR(:P_vendor_from is not null
or :P_vendor_to is not null
or :P_po_num_from is not null
or :P_po_num_to is not null))
AND (:P_exception is null
or
(:P_exception is not null
and rct.receipt_exception_flag=:P_exception))
AND RCT.TRANSFER_LPN_ID=WLPN1.LPN_ID(+)
UNION
SELECT RCT.TRANSACTION_ID TX_ID
,HRU.NAME ORG
,PLC1.DISPLAYED_FIELD SRC_TYPE
,HRU1.NAME || PRL.SOURCE_SUBINVENTORY SRC
,null C_FLEX_ITEM
,null C_FLEX_CAT
,RSL.ITEM_REVISION REV
,RSL.ITEM_DESCRIPTION DES
,RSH.RECEIPT_NUM RECEIPT_NUM
,PRH.SEGMENT1 DOC_NUM
,TO_CHAR(RCT.TRANSACTION_DATE,'DD/MON/YYYY HH24:MI:SS') TX_DATE
,PLC2.DISPLAYED_FIELD TX_TYPE
,RCT.UNIT_OF_MEASURE UOM
,NULL PO_UOM
,round(RCT.QUANTITY,:P_qty_precision) QTY
,PRL.UNIT_PRICE PRICE
,PDT.TYPE_NAME DOC_TYPE
,P2.FULL_NAME BUYER_PREPARER
,P1.FULL_NAME RECEIVER
,RSH.PACKING_SLIP PCKING_SLIP
,RCT.CREATION_DATE CREATION_DATE
,RCT.INSPECTION_QUALITY_CODE Q_CODE
,DECODE(RCT.RECEIPT_EXCEPTION_FLAG,'Y',:yes,'N',:no,null) EXCEPTION
,PLC3.DISPLAYED_FIELD PAR_TX_TYPE
,PLC.DISPLAYED_FIELD DESTINATION_TYPE
,P3.FULL_NAME DELIVER_TO_PERSON
,LOT.LOCATION_CODE DELIVER_TO_LOCATION
,RCT.SUBINVENTORY DESTINATION_SUBINVENTORY
,null C_FLEX_LOCATOR
,MTR.REASON_NAME TX_REASON
,RSL.ITEM_ID LS_ITEM_ID
,RCT.TRANSACTION_ID LS_TRANSACTION_ID
,RCT.SHIPMENT_LINE_ID LS_SHIPMENT_LINE_ID
,RCT.ORGANIZATION_ID LS_ORGANIZATION_ID
,WLPN1.LICENSE_PLATE_NUMBER LICENSE_PLATE_NUMBER,
nvl2(RSL.ITEM_ID,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_item_disp','INV','MSTK',101,MSI.ORGANIZATION_ID,MSI.INVENTORY_ITEM_ID,'ALL','Y','VALUE'),null) C_flex_item_disp,
nvl2(RSL.CATEGORY_ID,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_cat_disp','INV','MCAT',MCA.STRUCTURE_ID,NULL,MCA.CATEGORY_ID,'ALL','Y','VALUE'),null) C_flex_cat_disp,
nvl2(RCT.locator_id,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_locator_disp','INV','MTLL',101,MSL.ORGANIZATION_ID,MSL.INVENTORY_LOCATION_ID,'ALL','Y','VALUE'),null) C_FLEX_LOCATOR_DISP,
PO_RCVTXRTR_new_XMLP_PKG.rcv_uom_convertformula(NULL,RCT.UNIT_OF_MEASURE,RSL.ITEM_ID,PRL.UNIT_PRICE) RCV_UOM_CONVERT
FROM
PO_LOOKUP_CODES PLC,
PO_LOOKUP_CODES PLC1,
PO_LOOKUP_CODES PLC2,
PO_LOOKUP_CODES PLC3,
PO_DOCUMENT_TYPES PDT,
PER_PEOPLE_F P2,
PER_PEOPLE_F P3,
MTL_TRANSACTION_REASONS MTR,
HR_ORGANIZATION_UNITS HRU,
HR_LOCATIONS_ALL_TL LOT,
HR_ORGANIZATION_UNITS HRU1,
MTL_SYSTEM_ITEMS MSI,
MTL_CATEGORIES MCA,
MTL_ITEM_LOCATIONS MSL,
PO_REQUISITION_HEADERS PRH,
PO_REQUISITION_LINES_ALL PRL,
RCV_TRANSACTIONS PAR,
RCV_SHIPMENT_LINES RSL,
RCV_SHIPMENT_HEADERS RSH,
PER_PEOPLE_F P1,
RCV_TRANSACTIONS RCT,
WMS_LICENSE_PLATE_NUMBERS WLPN1
WHERE MSI.INVENTORY_ITEM_ID (+)=RSL.ITEM_ID
AND nvl(MSI.ORGANIZATION_ID,:organization_id)=:organization_id
AND MCA.CATEGORY_ID=RSL.CATEGORY_ID
AND MSL.INVENTORY_LOCATION_ID (+)=RCT.LOCATOR_ID
AND nvl(MSL.ORGANIZATION_ID,RCT.ORGANIZATION_ID)=RCT.ORGANIZATION_ID
AND RCT.SHIPMENT_LINE_ID=RSL.SHIPMENT_LINE_ID
AND RCT.SHIPMENT_HEADER_ID=RSH.SHIPMENT_HEADER_ID
AND RCT.REQUISITION_LINE_ID=PRL.REQUISITION_LINE_ID
AND PRL.REQUISITION_HEADER_ID=PRH.REQUISITION_HEADER_ID
AND RSH.ORGANIZATION_ID=HRU1.ORGANIZATION_ID
AND PDT.DOCUMENT_SUBTYPE=PRH.TYPE_LOOKUP_CODE
AND PDT.DOCUMENT_TYPE_CODE='REQUISITION'
AND RCT.SOURCE_DOCUMENT_CODE='REQ'
AND RSH.RECEIPT_SOURCE_CODE || ''='INTERNAL ORDER'
AND (RCT.EMPLOYEE_ID=P1.PERSON_ID (+)
AND trunc(RCT.CREATION_DATE) BETWEEN
NVL(P1.EFFECTIVE_START_DATE,trunc(RCT.CREATION_DATE)) AND
NVL(P1.EFFECTIVE_END_DATE,trunc(RCT.CREATION_DATE)))
AND (PRH.PREPARER_ID=P2.PERSON_ID
AND trunc(PRH.LAST_UPDATE_DATE) BETWEEN
NVL(P2.EFFECTIVE_START_DATE,trunc(PRH.LAST_UPDATE_DATE)) AND
NVL(P2.EFFECTIVE_END_DATE,trunc(PRH.LAST_UPDATE_DATE)))
AND PLC1.LOOKUP_CODE=RSH.RECEIPT_SOURCE_CODE || ''
AND PLC1.LOOKUP_TYPE='SHIPMENT SOURCE TYPE'
AND PAR.TRANSACTION_ID (+)=RCT.PARENT_TRANSACTION_ID
AND PLC.LOOKUP_CODE=PRL.DESTINATION_TYPE_CODE
AND PLC.LOOKUP_TYPE='RCV DESTINATION TYPE'
AND (P3.PERSON_ID=PRL.TO_PERSON_ID
AND trunc(PRL.LAST_UPDATE_DATE) BETWEEN
NVL(P3.EFFECTIVE_START_DATE,trunc(PRL.LAST_UPDATE_DATE)) AND
NVL(P3.EFFECTIVE_END_DATE,trunc(PRL.LAST_UPDATE_DATE)))
AND LOT.LOCATION_ID(+)=PRL.DELIVER_TO_LOCATION_ID
AND LOT.LANGUAGE(+)=USERENV('LANG')
AND PLC2.LOOKUP_TYPE='RCV TRANSACTION TYPE'
AND PLC2.LOOKUP_CODE=RCT.TRANSACTION_TYPE
AND PLC3.LOOKUP_TYPE (+)='RCV TRANSACTION TYPE'
AND PLC3.LOOKUP_CODE (+)=PAR.TRANSACTION_TYPE
AND HRU.ORGANIZATION_ID=RCT.ORGANIZATION_ID
AND &P_WHERE_ORG_ID
AND MTR.REASON_ID(+)=RCT.REASON_ID
AND &P_WHERE_RECEIPT_NUM_FROM
AND &P_WHERE_RECEIPT_NUM_TO
AND &P_WHERE_SHIP_NUM_FROM
AND &P_WHERE_SHIP_NUM_TO
AND &P_WHERE_ITEM
AND &P_WHERE_CAT
AND &P_WHERE_REQ_NUM_FROM
AND &P_WHERE_REQ_NUM_TO
AND &P_WHERE_TRX_DATE_FROM
AND &P_WHERE_TRX_DATE_TO
AND &P_WHERE_TRX_TYPE
AND nvl(P1.FULL_NAME,'~')=nvl(:P_receiver,nvl(P1.FULL_NAME,'~'))
AND &P_WHERE_BUYER
AND ((:P_po_num_from is null
and :P_po_num_to is null
and :P_vendor_from is null
and :P_vendor_to is null
and :P_rma_num_from is null
and :P_rma_num_to is null
and :P_customer_from is null
and :P_customer_to is null)
OR(:P_req_num_from is not null
or :P_req_num_to is not null))
AND (:P_exception is null
or
(:P_exception is not null
and rct.receipt_exception_flag=:P_exception))
AND RCT.TRANSFER_LPN_ID=WLPN1.LPN_ID(+)
UNION
SELECT RCT.TRANSACTION_ID TX_ID
,HRU.NAME ORG
,PLC1.DISPLAYED_FIELD SRC_TYPE
,HRU1.NAME SRC
,null C_FLEX_ITEM
,null C_FLEX_CAT
,RSL.ITEM_REVISION REV
,RSL.ITEM_DESCRIPTION DES
,RSH.RECEIPT_NUM RECEIPT_NUM
,RSH.SHIPMENT_NUM DOC_NUM
,TO_CHAR(RCT.TRANSACTION_DATE,'DD/MON/YYYY HH24:MI:SS') TX_DATE
,PLC2.DISPLAYED_FIELD TX_TYPE
,RCT.UNIT_OF_MEASURE UOM
,NULL PO_