PO Receiving Transactions Register

Description
Categories: BI Publisher
Application: Purchasing
Source: Receiving Transactions Register (XML)
Short Name: RCVTXRTR_XML
DB package: PO_RCVTXRTR_new_XMLP_PKG
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,-- Bug21567184 
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,-- Bug21567184 
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,-- Bug21567184 
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,-- Bug21567184 
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_UOM
,round(RCT.QUANTITY,:P_qty_precision) QTY
,RSL.SHIPMENT_UNIT_PRICE PRICE
,PLC4.DISPLAYED_FIELD DOC_TYPE
,'' BUYER_PREPARER
,P.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
,P1.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,-- Bug21567184 
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,-- Bug21567184 
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,RSL.SHIPMENT_UNIT_PRICE) RCV_UOM_CONVERT
FROM PO_LOOKUP_CODES PLC
,PO_LOOKUP_CODES PLC1
,PO_LOOKUP_CODES PLC2
,PO_LOOKUP_CODES PLC3
,PO_LOOKUP_CODES PLC4
,PER_PEOPLE_F P1
,HR_ORGANIZATION_UNITS HRU1
,HR_LOCATIONS_ALL_TL LOT
,MTL_TRANSACTION_REASONS MTR
,HR_ORGANIZATION_UNITS HRU 
,MTL_SYSTEM_ITEMS MSI
,MTL_CATEGORIES MCA 
,MTL_ITEM_LOCATIONS MSL
,RCV_TRANSACTIONS PAR
,RCV_SHIPMENT_LINES RSL
,RCV_SHIPMENT_HEADERS RSH
,PER_PEOPLE_F P
,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 RSH.RECEIPT_SOURCE_CODE || ''='INVENTORY'
AND PLC4.LOOKUP_CODE=RCT.SOURCE_DOCUMENT_CODE
AND PLC4.LOOKUP_TYPE='SHIPMENT SOURCE DOCUMENT TYPE'
AND PLC1.LOOKUP_CODE=RSH.RECEIPT_SOURCE_CODE || ''
AND PLC1.LOOKUP_TYPE='SHIPMENT SOURCE TYPE'
AND (RCT.EMPLOYEE_ID=P.person_id (+)
AND trunc(RCT.CREATION_DATE) BETWEEN
NVL(P.EFFECTIVE_START_DATE,trunc(RCT.CREATION_DATE)) AND
NVL(P.EFFECTIVE_END_DATE,trunc(RCT.CREATION_DATE)))
AND RSH.ORGANIZATION_ID=HRU1.ORGANIZATION_ID
AND PAR.TRANSACTION_ID (+)=RCT.PARENT_TRANSACTION_ID
AND LOT.LOCATION_ID(+)=RCT.DELIVER_TO_LOCATION_ID
AND LOT.LANGUAGE(+)=USERENV('LANG')
AND (P1.person_id(+)=RCT.DELIVER_TO_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 PLC.LOOKUP_CODE=RSL.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_TRX_DATE_FROM
AND &P_WHERE_TRX_DATE_TO 
AND &P_WHERE_TRX_TYPE
AND nvl(P.FULL_NAME,'~')=nvl(:P_receiver,nvl(P.FULL_NAME,'~'))
AND :P_buyer is NULL
AND &P_WHERE_SHIP_NUM_FROM
AND &P_WHERE_SHIP_NUM_TO
AND ((:P_po_num_from is null
and :P_po_num_to is null
and :P_req_num_from is null
and :P_req_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_ship_num_from is not null 
or :P_ship_num_to is not null))
and nvl(p.business_group_id,0)=(select nvl(max(fsp.business_group_id),0)
from financials_system_parameters fsp) 
and trunc(sysdate) between nvl(p.effective_start_date,trunc(sysdate)) and nvl(p.effective_end_date,trunc(sysdate))
and ((nvl(p1.business_group_id,0)=(select nvl(max(fsp.business_group_id),0)
from financials_system_parameters fsp) )
or NVL(rct.deliver_to_person_id,0)=0 ) 
and trunc(sysdate) between nvl(p1.effective_start_date,trunc(sysdate)) and nvl(p1.effective_end_date,trunc(sysdate))
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,
DECODE(rct.source_document_code,
'PO',pov.vendor_name,
oev.NAME
) src,
null c_flex_item,
null c_flex_cat,
rsl.item_revision rev,
rsl.item_description des,
rsh.receipt_num receipt_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_uom,
ROUND(rct.quantity,:p_qty_precision) qty,
TO_NUMBER('') price,
'' doc_type,
'' buyer_preparer,
p.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,
p2.full_name deliver_to_person,
lot.location_code deliver_to_location,
'' 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,-- Bug21567184 
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,-- Bug21567184 
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,TO_NUMBER('')) RCV_UOM_CONVERT
FROM po_lookup_codes plc,
po_lookup_codes plc1,
po_lookup_codes plc2,
po_lookup_codes plc3,
per_people_f p2,
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,
rcv_transactions par,
rcv_shipment_lines rsl,
rcv_shipment_headers rsh,
per_people_f p,
rcv_transactions rct,
wms_license_plate_numbers wlpn1,
oe_sold_to_orgs_v oev
WHERE msi.inventory_item_id(+)=rsl.item_id
AND msi.organization_id(+)=:organization_id
AND mca.category_id=rsl.category_id
AND msl.inventory_location_id(+)=rct.locator_id
AND msl.organization_id(+)=rct.organization_id
AND rct.source_document_code IN('PO','RMA')
AND rsh.receipt_source_code IN('VENDOR','CUSTOMER')
AND rct.shipment_line_id=rsl.shipment_line_id
AND rct.shipment_header_id=rsh.shipment_header_id
AND (rct.employee_id=p.person_id(+)
AND TRUNC(rct.creation_date) BETWEEN NVL(p.effective_start_date,TRUNC(rct.creation_date)) AND NVL(p.effective_end_date,TRUNC(rct.creation_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 (p2.person_id(+)=rct.deliver_to_person_id
AND TRUNC(rct.creation_date) BETWEEN NVL(p2.effective_start_date,TRUNC(rct.creation_date)) AND NVL(p2.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_trx_date_from
AND &p_where_trx_date_to
AND &p_where_trx_type
AND NVL(p.full_name,'~')=NVL(:p_receiver,NVL(p.full_name,'~'))
AND :p_buyer IS NULL
AND &p_where_vendor_from
AND &p_where_vendor_to
AND rct.transaction_type='UNORDERED'
AND ((:p_req_num_from IS NULL
AND :p_req_num_to IS NULL
AND :p_po_num_from IS NULL
AND :p_po_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)
)
AND NVL(p.business_group_id,0)=(SELECT NVL(MAX(fsp.business_group_id),0)
FROM financials_system_parameters fsp)
AND TRUNC(SYSDATE) BETWEEN NVL(p.effective_start_date,TRUNC(SYSDATE)) AND NVL(p.effective_end_date,TRUNC(SYSDATE))
AND ((NVL(p2.business_group_id,0)=(SELECT NVL(MAX(fsp.business_group_id),0)
FROM financials_system_parameters fsp))
OR NVL(rct.deliver_to_person_id,0)=0) 
AND TRUNC(SYSDATE) BETWEEN NVL(p2.effective_start_date,TRUNC(SYSDATE)) AND NVL(p2.effective_end_date,TRUNC(SYSDATE))
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(+)
AND oev.customer_id(+)=rsh.customer_id
UNION
SELECT rct.transaction_id TX_ID
,HRU.name ORG
,plc1.displayed_field SRC_TYPE
,oev.name src
,null C_FLEX_ITEM
,null C_FLEX_CAT
,rsl.item_revision REV
,rsl.item_description DES
,rsh.receipt_num receipt_num
,to_char(oeh.order_number) || '-' || to_char(oel.line_number) 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
,RCT.QUANTITY QTY 
,rsl.shipment_unit_price PRICE
,'RMA' DOC_TYPE
,to_char(null) 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,-- Bug21567184 
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,-- Bug21567184 
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,rsl.shipment_unit_price) RCV_UOM_CONVERT
FROM RCV_transactions rct
,RCV_transactions par
,hr_organization_units hru
,rcv_shipment_headers rsh
,po_lookup_codes plc1
,po_lookup_codes plc2
,oe_sold_to_orgs_v oev
,rcv_shipment_lines rsl
,MTL_SYSTEM_ITEMS MSI
,MTL_CATEGORIES MCA
,per_people_f p1
,per_people_f p3
,po_lookup_codes plc3
,po_lookup_codes plc
,HR_LOCATIONS_ALL_TL LOT
,MTL_TRANSACTION_REASONS MTR
,MTL_ITEM_LOCATIONS MSL
,OE_ORDER_HEADERS OEH
,OE_ORDER_LINES OEL
,<