PO Receiving Transactions Register
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Receiving Transactions Register
Application: Purchasing
Source: Receiving Transactions Register (XML)
Short Name: RCVTXRTR_XML
DB package: PO_RCVTXRTR_new_XMLP_PKG
Description: Receiving Transactions Register
Application: Purchasing
Source: Receiving Transactions Register (XML)
Short Name: RCVTXRTR_XML
DB package: PO_RCVTXRTR_new_XMLP_PKG
Run
PO Receiving Transactions Register and other Oracle EBS reports with Blitz Report™ on our demo environment
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_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, 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,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 |