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
Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS
Contact us to schedule a demo or if you need help with the installation
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 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, 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,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, 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 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 | |
---|---|---|---|
Operating Unit |
|
LOV | |
Title |
|
Char | |
Organization |
|
LOV Oracle | |
Transaction Type |
|
LOV Oracle | |
Transaction Dates From |
|
DateTime | |
To |
|
DateTime | |
Receipt Numbers From |
|
LOV Oracle | |
To 2 |
|
LOV Oracle | |
Receiver |
|
LOV Oracle | |
Items From |
|
Char | |
To 3 |
|
Char | |
Categories From |
|
Char | |
To 4 |
|
Char | |
Suppliers From |
|
LOV Oracle | |
To 5 |
|
LOV Oracle | |
Purchase Order Numbers From |
|
LOV Oracle | |
To 6 |
|
LOV Oracle | |
Buyer Name |
|
LOV Oracle | |
Requisition Numbers From |
|
LOV Oracle | |
To 7 |
|
LOV Oracle | |
Shipment Numbers From |
|
LOV Oracle | |
To 8 |
|
LOV Oracle | |
Exception |
|
LOV Oracle | |
Detail |
|
LOV Oracle | |
Include Lot/Serial |
|
LOV Oracle | |
Customers From |
|
LOV Oracle | |
To 9 |
|
LOV Oracle | |
RMA Numbers From |
|
LOV Oracle | |
To 10 |
|
LOV Oracle |