PO Receiving Transactions Register

Description
Categories: BI Publisher, Procurement
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
,WMS_LICENSE_PLATE_NUMBERS WLPN1
where RCT.SOURCE_DOCUMENT_CODE='RMA'
AND RSH.RECEIPT_SOURCE_CODE || ''='CUSTOMER'
AND PAR.TRANSACTION_ID (+)=RCT.PARENT_TRANSACTION_ID
AND hru.organization_id=rct.organization_id
AND &P_WHERE_ORG_ID
AND PLC1.LOOKUP_CODE=RSH.RECEIPT_SOURCE_CODE || ''
AND PLC1.LOOKUP_TYPE='SHIPMENT SOURCE TYPE'
AND PLC.LOOKUP_CODE=RCT.DESTINATION_TYPE_CODE
AND PLC.LOOKUP_TYPE='RCV DESTINATION TYPE'
AND oev.customer_id (+)=rsh.customer_id
AND RCT.SHIPMENT_LINE_ID=RSL.SHIPMENT_LINE_ID
AND RCT.SHIPMENT_HEADER_ID=RSH.SHIPMENT_HEADER_ID
AND RSH.shipment_header_id=rsl.shipment_header_id
AND MCA.CATEGORY_ID (+)=RSL.CATEGORY_ID AND MSI.INVENTORY_ITEM_ID (+)=RSL.ITEM_ID
AND MSL.INVENTORY_LOCATION_ID (+)=RCT.LOCATOR_ID
AND nvl(MSL.ORGANIZATION_ID,RCT.ORGANIZATION_ID)=RCT.ORGANIZATION_ID
AND PLC2.LOOKUP_TYPE='RCV TRANSACTION TYPE'
AND PLC2.LOOKUP_CODE=RCT.TRANSACTION_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 PLC3.LOOKUP_TYPE (+)='RCV TRANSACTION TYPE'
AND PLC3.LOOKUP_CODE (+)=PAR.TRANSACTION_TYPE
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 LOT.LOCATION_ID(+)=RCT.DELIVER_TO_LOCATION_ID
AND LOT.LANGUAGE(+)=USERENV('LANG')
AND MTR.REASON_ID(+)=RCT.REASON_ID
AND OEH.header_id=rct.oe_order_header_id
AND oel.line_id=rct.oe_order_line_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_RMA_NUM_FROM
AND &P_WHERE_RMA_NUM_TO
AND &P_WHERE_TRX_DATE_FROM
AND &P_WHERE_TRX_DATE_TO 
AND &P_WHERE_TRX_TYPE
AND &P_WHERE_CUSTOMER_FROM
AND &P_WHERE_CUSTOMER_TO
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_vendor_from is null
and :P_vendor_to is null)
OR(:P_customer_from is not null
or :P_customer_to is not null
or :P_rma_num_from is not null 
or :P_rma_num_to is not null))
AND nvl(P1.FULL_NAME,'~')=nvl(:P_receiver,nvl(P1.FULL_NAME,'~'))
AND :P_buyer is NULL
AND RCT.TRANSFER_LPN_ID=WLPN1.LPN_ID(+)
order by 3,4,5,36,37,9,10,11,35,14,2,19,1,33,31,7,8,38,12,34,13,15,16,32,17,18,20,21,22,23,24,25,26,27,28,30
Parameter Name SQL text Validation
P_STRUCT_NUM
 
Number
To
 
LOV Oracle
RMA Numbers From
 
LOV Oracle
To
 
LOV Oracle
Customers From
 
LOV Oracle
Include Lot/Serial
 
LOV Oracle
Detail
 
LOV Oracle
Exception
 
LOV Oracle
To
 
LOV Oracle
Shipment Numbers From
 
LOV Oracle
To
 
LOV Oracle
Requisition Numbers From
 
LOV Oracle
Buyer Name
 
LOV Oracle
To
 
LOV Oracle
Purchase Order Numbers From
 
LOV Oracle
To
 
LOV Oracle
Suppliers From
 
LOV Oracle
To
 
Categories From
 
To
 
Items From
 
Receiver
 
LOV Oracle
To
 
LOV Oracle
Receipt Numbers From
 
LOV Oracle
To
 
DateTime
Transaction Dates From
 
DateTime
Transaction Type
 
LOV Oracle
Organization
 
LOV Oracle
Title