eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.RCV_TRANSACTIONS_V

Source

View Text - Preformatted


SELECT /*+ opt_param('_optimizer_cost_based_transformation','off') OPT_PARAM('_or_expand_nvl_predicate','FALSE') OPT_PARAM('_replace_virtual_columns','FALSE') NO_EXPAND */ 'N' LINE_CHKBOX , RSUP.FROM_ORGANIZATION_ID , RSUP.TO_ORGANIZATION_ID , RT.SOURCE_DOCUMENT_CODE , RSH.RECEIPT_SOURCE_CODE , RSUP.RCV_TRANSACTION_ID , RT.TRANSACTION_DATE , RT.TRANSACTION_TYPE , PLC.DISPLAYED_FIELD TRANSACTION_TYPE_DSP , RT.QUANTITY , RT.UNIT_OF_MEASURE , RT.PRIMARY_UNIT_OF_MEASURE PRIMARY_UOM , RT.PRIMARY_QUANTITY , MUM1.UOM_CLASS PRIMARY_UOM_CLASS , MUM.UOM_CLASS UOM_CLASS , RSUP.PO_HEADER_ID , DECODE(RT.SOURCE_DOCUMENT_CODE, 'PO', POH.SEGMENT1, 'RMA', OEH.ORDER_NUMBER, RSH.SHIPMENT_NUM) ORDER_NUMBER , RT.PO_REVISION_NUM , DECODE(RT.SOURCE_DOCUMENT_CODE, 'RMA', OEOT.ORDER_CATEGORY_CODE, POH.TYPE_LOOKUP_CODE) TYPE_LOOKUP_CODE , DECODE(RT.SOURCE_DOCUMENT_CODE, 'RMA', OETL.NAME, PLC3.DISPLAYED_FIELD) ORDER_TYPE , RSUP.PO_RELEASE_ID , DECODE(RT.SOURCE_DOCUMENT_CODE, 'RMA', NULL, PR.RELEASE_NUM) PO_RELEASE_NUMBER , RSH.VENDOR_ID , RT.VENDOR_SITE_ID , DECODE(RT.SOURCE_DOCUMENT_CODE, 'PO', POV.VENDOR_NAME, 'RMA', SUBSTR(HZP.PARTY_NAME,1,255), OOD.NAME) SOURCE , RSUP.PO_LINE_ID , DECODE(RT.SOURCE_DOCUMENT_CODE, 'PO', POL.LINE_NUM, 'RMA', OEL.LINE_NUMBER, RSL.LINE_NUM) LINE_NUMBER , DECODE(RT.SOURCE_DOCUMENT_CODE, 'RMA', OEL.ORDERED_QUANTITY, 'PO', PLL.QUANTITY, RSL.QUANTITY_SHIPPED) ORDERED_QTY , DECODE(RT.SOURCE_DOCUMENT_CODE, 'RMA', OEL.ORDER_QUANTITY_UOM, 'PO', MUM2.UNIT_OF_MEASURE, RSL.UNIT_OF_MEASURE) ORDERED_UOM , NVL(PLL.PRICE_OVERRIDE, POL.UNIT_PRICE) po_unit_price , RSL.CATEGORY_ID , RSUP.SUPPLY_SOURCE_ID , RSUP.ITEM_ID , NVL(MSI.ALLOWED_UNITS_LOOKUP_CODE,2) ITEM_ALLOWED_UNITS_LOOKUP_CODE , NVL(MSI.LOCATION_CONTROL_CODE,1) ITEM_LOCATOR_CONTROL , DECODE(MSI.RESTRICT_LOCATORS_CODE,1,'Y','N') RESTRICT_LOCATORS_CODE , DECODE(MSI.RESTRICT_SUBINVENTORIES_CODE,1,'Y','N') RESTRICT_SUBINVENTORIES_CODE , NVL(MSI.SHELF_LIFE_CODE,1) SHELF_LIFE_CODE , NVL(MSI.SHELF_LIFE_DAYS,0) SHELF_LIFE_DAYS , MSI.SERIAL_NUMBER_CONTROL_CODE SERIAL_NUMBER_CONTROL_CODE , MSI.LOT_CONTROL_CODE LOT_CONTROL_CODE , DECODE(MSI.REVISION_QTY_CONTROL_CODE,1,'N',2,'Y','N') ITEM_REVISION_CONTROL_FLAG , DECODE(MSI1.REVISION_QTY_CONTROL_CODE,1,'N',2,'Y','N') REVISION_QTY_CONTROL_CODE , RSL.ITEM_DESCRIPTION , RSUP.ITEM_REVISION , RSUP.PO_LINE_LOCATION_ID , DECODE(RT.SOURCE_DOCUMENT_CODE, 'RMA', OEL.SHIPMENT_NUMBER, PLL.SHIPMENT_NUM) PO_SHIPMENT_NUMBER , RT.PO_DISTRIBUTION_ID , NVL(RSL.EMPLOYEE_ID,RT.EMPLOYEE_ID) , DECODE(RT.SOURCE_DOCUMENT_CODE, 'RMA', DECODE(OEOT1.ORDER_CATEGORY_CODE, 'RETURN', OEH.SHIPPING_INSTRUCTIONS,NULL), POH.NOTE_TO_RECEIVER) NOTE_TO_RECEIVER , RT.COMMENTS , RSL.COMMENTS INV_REQ_NOTE_TO_RECEIVER , DECODE(RT.SOURCE_DOCUMENT_CODE, 'RMA', OEL.SCHEDULE_ARRIVAL_DATE, 'PO', NVL(PLL.PROMISED_DATE, PLL.NEED_BY_DATE), RSH.EXPECTED_RECEIPT_DATE) DUE_DATE , RSUP.REQ_HEADER_ID , RSUP.REQ_LINE_ID , RSUP.SHIPMENT_HEADER_ID , RSUP.SHIPMENT_LINE_ID , RSH.RECEIPT_NUM , RSH.SHIPMENT_NUM RCV_SHIPMENT_NUM , RSH.PACKING_SLIP , RSH.BILL_OF_LADING , RSH.FREIGHT_CARRIER_CODE FREIGHT_CARRIER , RSH.WAYBILL_AIRBILL_NUM , RSL.LINE_NUM RCV_LINE_NUM , RSL.GOVERNMENT_CONTEXT , RSL.USSGL_TRANSACTION_CODE , RT.INSPECTION_STATUS_CODE , PLC1.DISPLAYED_FIELD INSPECTION_STATUS_DSP , RT.INSPECTION_QUALITY_CODE , RT.VENDOR_LOT_NUM , DECODE(RT.SOURCE_DOCUMENT_CODE, 'RMA', NULL, POL.VENDOR_PRODUCT_NUM) VENDOR_ITEM_NUMBER , RT.RECEIPT_EXCEPTION_FLAG , RT.SUBSTITUTE_UNORDERED_CODE , RT.ROUTING_HEADER_ID ROUTING_ID , RRH.ROUTING_NAME , RT.ROUTING_STEP_ID , RT.REASON_ID , MTR.REASON_NAME REASON_CODE , RT.CURRENCY_CODE , RT.CURRENCY_CONVERSION_RATE , RT.CURRENCY_CONVERSION_DATE , RT.CURRENCY_CONVERSION_TYPE , RSL.REQ_DISTRIBUTION_ID , RSUP.DESTINATION_TYPE_CODE DESTINATION_TYPE_CODE_HOLD , PLC2.DISPLAYED_FIELD DESTINATION_TYPE_DSP_HOLD , RSL.DESTINATION_CONTEXT , RT.LOCATION_ID , RSL.DELIVER_TO_PERSON_ID , RSL.DELIVER_TO_LOCATION_ID , RSL.TO_SUBINVENTORY SUBINVENTORY , DECODE(RT.SOURCE_DOCUMENT_CODE, 'RMA', MSI.UN_NUMBER_ID, NVL(POL.UN_NUMBER_ID,MSI.UN_NUMBER_ID)) UN_NUMBER_ID , DECODE(RT.SOURCE_DOCUMENT_CODE, 'RMA', MSI.HAZARD_CLASS_ID, NVL(POL.HAZARD_CLASS_ID,MSI.HAZARD_CLASS_ID)) HAZARD_CLASS_ID , RSUP.CREATION_DATE , RSUP.PROGRAM_APPLICATION_ID , RSUP.PROGRAM_ID , RSUP.PROGRAM_UPDATE_DATE , RSUP.REQUEST_ID , RSUP.LAST_UPDATE_DATE , RSUP.LAST_UPDATED_BY , RSUP.LAST_UPDATE_LOGIN , RSUP.CREATED_BY , HRL.LOCATION_CODE CURRENT_LOCATION , RT.ATTRIBUTE_CATEGORY , RT.ATTRIBUTE1 , RT.ATTRIBUTE2 , RT.ATTRIBUTE3 , RT.ATTRIBUTE4 , RT.ATTRIBUTE5 , RT.ATTRIBUTE6 , RT.ATTRIBUTE7 , RT.ATTRIBUTE8 , RT.ATTRIBUTE9 , RT.ATTRIBUTE10 , RT.ATTRIBUTE11 , RT.ATTRIBUTE12 , RT.ATTRIBUTE13 , RT.ATTRIBUTE14 , RT.ATTRIBUTE15 , RT.QA_COLLECTION_ID , DCT.USER_CONVERSION_TYPE , RT.MATCH_OPTION , RSUP.OE_ORDER_HEADER_ID , OEH.ORDER_NUMBER OE_ORDER_NUM , RSUP.OE_ORDER_LINE_ID , OEL.LINE_NUMBER OE_ORDER_LINE_NUM , RSH.CUSTOMER_ID , RSH.CUSTOMER_SITE_ID , decode(oel.item_identifier_type, 'CUST', MCI.CUSTOMER_ITEM_NUMBER, '') CUSTOMER_ITEM_NUM ,RSL.BAR_CODE_LABEL ,RSL.CONTAINER_NUM ,RSL.TRUCK_NUM ,RT.SECONDARY_QUANTITY ,RT.SECONDARY_UNIT_OF_MEASURE , DECODE(RT.SOURCE_DOCUMENT_CODE, 'RMA', OEL.ORDERED_QUANTITY2, 'PO', PLL.SECONDARY_QUANTITY, RSL.SECONDARY_QUANTITY_SHIPPED) SECONDARY_ORDERED_QTY , DECODE(RT.SOURCE_DOCUMENT_CODE, 'RMA',MUM3.UNIT_OF_MEASURE, 'PO', MUM4.UNIT_OF_MEASURE, RSL.SECONDARY_UNIT_OF_MEASURE) SECONDARY_ORDERED_UOM , RT.QC_GRADE QC_GRADE , DECODE(RT.SOURCE_DOCUMENT_CODE, 'RMA', OEL.SHIPPING_INSTRUCTIONS, PLL.NOTE_TO_RECEIVER) PLL_NOTE_TO_RECEIVER , RSUP.LPN_ID ,DECODE(MSI.TRACKING_QUANTITY_IND,'PS',MSI.SECONDARY_DEFAULT_IND,NULL) ,DECODE(RT.SOURCE_DOCUMENT_CODE,'PO',POH.ORG_ID,'RMA',OEH.ORG_ID,NULL) , NVL(RSL.LCM_SHIPMENT_LINE_ID, RT.LCM_SHIPMENT_LINE_ID) , NVL(RSL.UNIT_LANDED_COST, RT.UNIT_LANDED_COST) , NVL(PLL.LCM_FLAG,'N') FROM RCV_SUPPLY RSUP , RCV_SHIPMENT_LINES RSL, RCV_TRANSACTIONS RT , RCV_SHIPMENT_HEADERS RSH, PO_LOOKUP_CODES PLC , PO_LOOKUP_CODES PLC1 , PO_LOOKUP_CODES PLC2 , PO_LOOKUP_CODES PLC3 , MTL_UNITS_OF_MEASURE MUM, MTL_UNITS_OF_MEASURE MUM1, MTL_UNITS_OF_MEASURE MUM2, MTL_UNITS_OF_MEASURE MUM3, MTL_UNITS_OF_MEASURE MUM4, MTL_SYSTEM_ITEMS MSI, MTL_SYSTEM_ITEMS MSI1, MTL_TRANSACTION_REASONS MTR, HZ_CUST_ACCOUNTS HZCA, HZ_PARTIES HZP, OE_ORDER_LINES_ALL OEL, OE_ORDER_HEADERS_ALL OEH, OE_TRANSACTION_TYPES_TL OETL, OE_TRANSACTION_TYPES_TL OETL1, OE_TRANSACTION_TYPES_ALL OEOT, OE_TRANSACTION_TYPES_ALL OEOT1, MTL_CUSTOMER_ITEMS MCI, /* PO_LINES_TRX_V POL,*/ (SELECT PO_LINE_ID , UNIT_MEAS_LOOKUP_CODE , UNIT_PRICE , UN_NUMBER_ID , HAZARD_CLASS_ID , VENDOR_PRODUCT_NUM , COST_CONSTRAINT, ORDER_TYPE_LOOKUP_CODE, LINE_NUM FROM PO_LINES WHERE NVL(CLM_INFO_FLAG,'N') = 'N' AND ( NVL(clm_option_indicator,'B') <> 'O' OR CLM_EXERCISED_FLAG = 'Y' ) ) POL , /* OR NVL(CLM_EXERCISED_FLAG,'N') = 'Y' ) ) POL ,*/ PO_HEADERS_TRX_V POH, /* PO_LINE_LOCATIONS_TRX_V PLL,*/ PO_LINE_LOCATIONS_ALL PLL, PO_RELEASES_ALL PR , PO_VENDORS POV, RCV_ROUTING_HEADERS RRH, HR_LOCATIONS_ALL_TL HRL, GL_DAILY_CONVERSION_TYPES DCT, HR_ALL_ORGANIZATION_UNITS_TL OOD, FINANCIALS_SYSTEM_PARAMS_ALL FSP WHERE EXISTS (SELECT /*+ NO_UNNEST */ 'S' FROM PO_LINES PLTV WHERE NVL(PLTV.CLM_INFO_FLAG,'N') = 'N' AND ( NVL(PLTV.clm_option_indicator,'B') <> 'O' OR PLTV.CLM_EXERCISED_FLAG = 'Y' ) AND pll.po_line_id = pltv.po_line_id UNION ALL SELECT /*+ NO_UNNEST */ 'X' FROM DUAL WHERE pll.po_line_id IS NULL ) AND RSUP.SUPPLY_TYPE_CODE = 'RECEIVING' AND RSL.SHIPMENT_LINE_ID = RSUP.SHIPMENT_LINE_ID AND RT.TRANSACTION_ID = RSUP.RCV_TRANSACTION_ID AND RT.TRANSACTION_TYPE != 'UNORDERED' AND RRH.ROUTING_HEADER_ID(+) = RT.ROUTING_HEADER_ID AND RSH.SHIPMENT_HEADER_ID = RSUP.SHIPMENT_HEADER_ID AND RT.TRANSACTION_TYPE = PLC.LOOKUP_CODE AND PLC.LOOKUP_TYPE = 'RCV TRANSACTION TYPE' AND PLC1.LOOKUP_TYPE(+) = 'INSPECTION STATUS' AND PLC1.LOOKUP_CODE(+) = RT.INSPECTION_STATUS_CODE AND PLC2.LOOKUP_TYPE(+) = 'RCV DESTINATION TYPE' AND PLC2.LOOKUP_CODE(+) = RSUP.DESTINATION_TYPE_CODE AND OEL.LINE_ID(+) = RSUP.OE_ORDER_LINE_ID AND OEH.HEADER_ID(+) = RSUP.OE_ORDER_HEADER_ID AND OEH.ORDER_TYPE_ID = OEOT1.TRANSACTION_TYPE_ID(+) AND OEL.LINE_TYPE_ID = OEOT.TRANSACTION_TYPE_ID(+) AND OEOT.TRANSACTION_TYPE_ID = OETL.TRANSACTION_TYPE_ID(+) AND OEOT1.TRANSACTION_TYPE_ID = OETL1.TRANSACTION_TYPE_ID(+) AND OETL.LANGUAGE(+) = USERENV('LANG') AND OETL1.LANGUAGE(+) = USERENV('LANG') AND OEOT.TRANSACTION_TYPE_CODE(+) = 'LINE' AND OEOT1.TRANSACTION_TYPE_CODE(+) = 'ORDER' AND HZCA.CUST_ACCOUNT_ID(+) = RSH.CUSTOMER_ID AND HZCA.PARTY_ID = HZP.PARTY_ID(+) AND OEL.ORDERED_ITEM_ID = MCI.CUSTOMER_ITEM_ID(+) AND POL.PO_LINE_ID(+) = RSUP.PO_LINE_ID AND POH.PO_HEADER_ID(+) = RSUP.PO_HEADER_ID AND POV.VENDOR_ID(+) = RSH.VENDOR_ID AND PLL.LINE_LOCATION_ID(+) = RSUP.PO_LINE_LOCATION_ID AND NVL(PLL.ORG_ID,-99) = NVL(POH.ORG_ID,-99) AND MUM.UNIT_OF_MEASURE = RSUP.UNIT_OF_MEASURE AND MUM2.UNIT_OF_MEASURE(+) = POL.UNIT_MEAS_LOOKUP_CODE AND (MUM4.UNIT_OF_MEASURE (+) = PLL.SECONDARY_UNIT_OF_MEASURE) AND (MUM3.UOM_CODE (+) =OEL.ORDERED_QUANTITY_UOM2) AND MSI.ORGANIZATION_ID (+) = RSUP.TO_ORGANIZATION_ID AND MSI.INVENTORY_ITEM_ID (+) = RSUP.ITEM_ID AND MUM1.UNIT_OF_MEASURE (+) = MSI.PRIMARY_UNIT_OF_MEASURE AND MSI1.ORGANIZATION_ID (+) = RSUP.FROM_ORGANIZATION_ID AND MSI1.INVENTORY_ITEM_ID(+) = RSUP.ITEM_ID AND PR.PO_RELEASE_ID (+) = RSUP.PO_RELEASE_ID AND MTR.REASON_ID (+) = RT.REASON_ID AND HRL.LOCATION_ID (+) = RT.LOCATION_ID AND HRL.LANGUAGE(+) = USERENV('LANG') AND OOD.ORGANIZATION_ID(+) = RSL.FROM_ORGANIZATION_ID AND OOD.LANGUAGE(+) = USERENV('LANG') AND DECODE(RT.SOURCE_DOCUMENT_CODE, 'PO', 'PO TYPE', 'SHIPMENT SOURCE TYPE') = PLC3.LOOKUP_TYPE AND DECODE(RT.SOURCE_DOCUMENT_CODE, 'PO', POH.TYPE_LOOKUP_CODE, RSH.RECEIPT_SOURCE_CODE) = PLC3.LOOKUP_CODE AND DCT.CONVERSION_TYPE (+) = RT.CURRENCY_CONVERSION_TYPE AND NVL(PLL.MATCHING_BASIS(+),'QUANTITY') != 'AMOUNT' AND PLL.PAYMENT_TYPE IS NULL AND POH.ORG_ID = FSP.ORG_ID (+) AND ( RT.SOURCE_DOCUMENT_CODE!='PO' OR ( /*NVL(FSP.purch_encumbrance_flag,'N') = 'Y' AND NVL(PLL.encumbered_flag,'N') = 'Y' */ FSP.purch_encumbrance_flag = 'Y' AND PLL.encumbered_flag = 'Y' ) OR NVL(FSP.purch_encumbrance_flag,'N') = 'N' OR ( FSP.purch_encumbrance_flag = 'Y' AND NVL(PLL.encumbered_flag,'N') = 'N' AND RT.SOURCE_DOCUMENT_CODE = 'PO' AND EXISTS (SELECT /*+ no_unnest */ 1 FROM po_distributions_all pod, po_lines_trx_v pltv WHERE pod.po_line_id = pltv.po_line_id AND pod.line_location_id = pll.line_location_id AND destination_type_code = 'SHOP FLOOR' OR (pltv.cost_constraint IN ('NSP', 'NC')) OR (pltv.order_type_lookup_code = 'QUANTITY' AND (pltv.unit_price = 0 OR pltv.unit_price IS NULL)) ) ) )

View Text - HTML Formatted

SELECT /*+ OPT_PARAM('_OPTIMIZER_COST_BASED_TRANSFORMATION'
, 'OFF') OPT_PARAM('_OR_EXPAND_NVL_PREDICATE'
, 'FALSE') OPT_PARAM('_REPLACE_VIRTUAL_COLUMNS'
, 'FALSE') NO_EXPAND */ 'N' LINE_CHKBOX
, RSUP.FROM_ORGANIZATION_ID
, RSUP.TO_ORGANIZATION_ID
, RT.SOURCE_DOCUMENT_CODE
, RSH.RECEIPT_SOURCE_CODE
, RSUP.RCV_TRANSACTION_ID
, RT.TRANSACTION_DATE
, RT.TRANSACTION_TYPE
, PLC.DISPLAYED_FIELD TRANSACTION_TYPE_DSP
, RT.QUANTITY
, RT.UNIT_OF_MEASURE
, RT.PRIMARY_UNIT_OF_MEASURE PRIMARY_UOM
, RT.PRIMARY_QUANTITY
, MUM1.UOM_CLASS PRIMARY_UOM_CLASS
, MUM.UOM_CLASS UOM_CLASS
, RSUP.PO_HEADER_ID
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'PO'
, POH.SEGMENT1
, 'RMA'
, OEH.ORDER_NUMBER
, RSH.SHIPMENT_NUM) ORDER_NUMBER
, RT.PO_REVISION_NUM
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, OEOT.ORDER_CATEGORY_CODE
, POH.TYPE_LOOKUP_CODE) TYPE_LOOKUP_CODE
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, OETL.NAME
, PLC3.DISPLAYED_FIELD) ORDER_TYPE
, RSUP.PO_RELEASE_ID
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, NULL
, PR.RELEASE_NUM) PO_RELEASE_NUMBER
, RSH.VENDOR_ID
, RT.VENDOR_SITE_ID
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'PO'
, POV.VENDOR_NAME
, 'RMA'
, SUBSTR(HZP.PARTY_NAME
, 1
, 255)
, OOD.NAME) SOURCE
, RSUP.PO_LINE_ID
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'PO'
, POL.LINE_NUM
, 'RMA'
, OEL.LINE_NUMBER
, RSL.LINE_NUM) LINE_NUMBER
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, OEL.ORDERED_QUANTITY
, 'PO'
, PLL.QUANTITY
, RSL.QUANTITY_SHIPPED) ORDERED_QTY
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, OEL.ORDER_QUANTITY_UOM
, 'PO'
, MUM2.UNIT_OF_MEASURE
, RSL.UNIT_OF_MEASURE) ORDERED_UOM
, NVL(PLL.PRICE_OVERRIDE
, POL.UNIT_PRICE) PO_UNIT_PRICE
, RSL.CATEGORY_ID
, RSUP.SUPPLY_SOURCE_ID
, RSUP.ITEM_ID
, NVL(MSI.ALLOWED_UNITS_LOOKUP_CODE
, 2) ITEM_ALLOWED_UNITS_LOOKUP_CODE
, NVL(MSI.LOCATION_CONTROL_CODE
, 1) ITEM_LOCATOR_CONTROL
, DECODE(MSI.RESTRICT_LOCATORS_CODE
, 1
, 'Y'
, 'N') RESTRICT_LOCATORS_CODE
, DECODE(MSI.RESTRICT_SUBINVENTORIES_CODE
, 1
, 'Y'
, 'N') RESTRICT_SUBINVENTORIES_CODE
, NVL(MSI.SHELF_LIFE_CODE
, 1) SHELF_LIFE_CODE
, NVL(MSI.SHELF_LIFE_DAYS
, 0) SHELF_LIFE_DAYS
, MSI.SERIAL_NUMBER_CONTROL_CODE SERIAL_NUMBER_CONTROL_CODE
, MSI.LOT_CONTROL_CODE LOT_CONTROL_CODE
, DECODE(MSI.REVISION_QTY_CONTROL_CODE
, 1
, 'N'
, 2
, 'Y'
, 'N') ITEM_REVISION_CONTROL_FLAG
, DECODE(MSI1.REVISION_QTY_CONTROL_CODE
, 1
, 'N'
, 2
, 'Y'
, 'N') REVISION_QTY_CONTROL_CODE
, RSL.ITEM_DESCRIPTION
, RSUP.ITEM_REVISION
, RSUP.PO_LINE_LOCATION_ID
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, OEL.SHIPMENT_NUMBER
, PLL.SHIPMENT_NUM) PO_SHIPMENT_NUMBER
, RT.PO_DISTRIBUTION_ID
, NVL(RSL.EMPLOYEE_ID
, RT.EMPLOYEE_ID)
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, DECODE(OEOT1.ORDER_CATEGORY_CODE
, 'RETURN'
, OEH.SHIPPING_INSTRUCTIONS
, NULL)
, POH.NOTE_TO_RECEIVER) NOTE_TO_RECEIVER
, RT.COMMENTS
, RSL.COMMENTS INV_REQ_NOTE_TO_RECEIVER
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, OEL.SCHEDULE_ARRIVAL_DATE
, 'PO'
, NVL(PLL.PROMISED_DATE
, PLL.NEED_BY_DATE)
, RSH.EXPECTED_RECEIPT_DATE) DUE_DATE
, RSUP.REQ_HEADER_ID
, RSUP.REQ_LINE_ID
, RSUP.SHIPMENT_HEADER_ID
, RSUP.SHIPMENT_LINE_ID
, RSH.RECEIPT_NUM
, RSH.SHIPMENT_NUM RCV_SHIPMENT_NUM
, RSH.PACKING_SLIP
, RSH.BILL_OF_LADING
, RSH.FREIGHT_CARRIER_CODE FREIGHT_CARRIER
, RSH.WAYBILL_AIRBILL_NUM
, RSL.LINE_NUM RCV_LINE_NUM
, RSL.GOVERNMENT_CONTEXT
, RSL.USSGL_TRANSACTION_CODE
, RT.INSPECTION_STATUS_CODE
, PLC1.DISPLAYED_FIELD INSPECTION_STATUS_DSP
, RT.INSPECTION_QUALITY_CODE
, RT.VENDOR_LOT_NUM
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, NULL
, POL.VENDOR_PRODUCT_NUM) VENDOR_ITEM_NUMBER
, RT.RECEIPT_EXCEPTION_FLAG
, RT.SUBSTITUTE_UNORDERED_CODE
, RT.ROUTING_HEADER_ID ROUTING_ID
, RRH.ROUTING_NAME
, RT.ROUTING_STEP_ID
, RT.REASON_ID
, MTR.REASON_NAME REASON_CODE
, RT.CURRENCY_CODE
, RT.CURRENCY_CONVERSION_RATE
, RT.CURRENCY_CONVERSION_DATE
, RT.CURRENCY_CONVERSION_TYPE
, RSL.REQ_DISTRIBUTION_ID
, RSUP.DESTINATION_TYPE_CODE DESTINATION_TYPE_CODE_HOLD
, PLC2.DISPLAYED_FIELD DESTINATION_TYPE_DSP_HOLD
, RSL.DESTINATION_CONTEXT
, RT.LOCATION_ID
, RSL.DELIVER_TO_PERSON_ID
, RSL.DELIVER_TO_LOCATION_ID
, RSL.TO_SUBINVENTORY SUBINVENTORY
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, MSI.UN_NUMBER_ID
, NVL(POL.UN_NUMBER_ID
, MSI.UN_NUMBER_ID)) UN_NUMBER_ID
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, MSI.HAZARD_CLASS_ID
, NVL(POL.HAZARD_CLASS_ID
, MSI.HAZARD_CLASS_ID)) HAZARD_CLASS_ID
, RSUP.CREATION_DATE
, RSUP.PROGRAM_APPLICATION_ID
, RSUP.PROGRAM_ID
, RSUP.PROGRAM_UPDATE_DATE
, RSUP.REQUEST_ID
, RSUP.LAST_UPDATE_DATE
, RSUP.LAST_UPDATED_BY
, RSUP.LAST_UPDATE_LOGIN
, RSUP.CREATED_BY
, HRL.LOCATION_CODE CURRENT_LOCATION
, RT.ATTRIBUTE_CATEGORY
, RT.ATTRIBUTE1
, RT.ATTRIBUTE2
, RT.ATTRIBUTE3
, RT.ATTRIBUTE4
, RT.ATTRIBUTE5
, RT.ATTRIBUTE6
, RT.ATTRIBUTE7
, RT.ATTRIBUTE8
, RT.ATTRIBUTE9
, RT.ATTRIBUTE10
, RT.ATTRIBUTE11
, RT.ATTRIBUTE12
, RT.ATTRIBUTE13
, RT.ATTRIBUTE14
, RT.ATTRIBUTE15
, RT.QA_COLLECTION_ID
, DCT.USER_CONVERSION_TYPE
, RT.MATCH_OPTION
, RSUP.OE_ORDER_HEADER_ID
, OEH.ORDER_NUMBER OE_ORDER_NUM
, RSUP.OE_ORDER_LINE_ID
, OEL.LINE_NUMBER OE_ORDER_LINE_NUM
, RSH.CUSTOMER_ID
, RSH.CUSTOMER_SITE_ID
, DECODE(OEL.ITEM_IDENTIFIER_TYPE
, 'CUST'
, MCI.CUSTOMER_ITEM_NUMBER
, '') CUSTOMER_ITEM_NUM
, RSL.BAR_CODE_LABEL
, RSL.CONTAINER_NUM
, RSL.TRUCK_NUM
, RT.SECONDARY_QUANTITY
, RT.SECONDARY_UNIT_OF_MEASURE
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, OEL.ORDERED_QUANTITY2
, 'PO'
, PLL.SECONDARY_QUANTITY
, RSL.SECONDARY_QUANTITY_SHIPPED) SECONDARY_ORDERED_QTY
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, MUM3.UNIT_OF_MEASURE
, 'PO'
, MUM4.UNIT_OF_MEASURE
, RSL.SECONDARY_UNIT_OF_MEASURE) SECONDARY_ORDERED_UOM
, RT.QC_GRADE QC_GRADE
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, OEL.SHIPPING_INSTRUCTIONS
, PLL.NOTE_TO_RECEIVER) PLL_NOTE_TO_RECEIVER
, RSUP.LPN_ID
, DECODE(MSI.TRACKING_QUANTITY_IND
, 'PS'
, MSI.SECONDARY_DEFAULT_IND
, NULL)
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'PO'
, POH.ORG_ID
, 'RMA'
, OEH.ORG_ID
, NULL)
, NVL(RSL.LCM_SHIPMENT_LINE_ID
, RT.LCM_SHIPMENT_LINE_ID)
, NVL(RSL.UNIT_LANDED_COST
, RT.UNIT_LANDED_COST)
, NVL(PLL.LCM_FLAG
, 'N')
FROM RCV_SUPPLY RSUP
, RCV_SHIPMENT_LINES RSL
, RCV_TRANSACTIONS RT
, RCV_SHIPMENT_HEADERS RSH
, PO_LOOKUP_CODES PLC
, PO_LOOKUP_CODES PLC1
, PO_LOOKUP_CODES PLC2
, PO_LOOKUP_CODES PLC3
, MTL_UNITS_OF_MEASURE MUM
, MTL_UNITS_OF_MEASURE MUM1
, MTL_UNITS_OF_MEASURE MUM2
, MTL_UNITS_OF_MEASURE MUM3
, MTL_UNITS_OF_MEASURE MUM4
, MTL_SYSTEM_ITEMS MSI
, MTL_SYSTEM_ITEMS MSI1
, MTL_TRANSACTION_REASONS MTR
, HZ_CUST_ACCOUNTS HZCA
, HZ_PARTIES HZP
, OE_ORDER_LINES_ALL OEL
, OE_ORDER_HEADERS_ALL OEH
, OE_TRANSACTION_TYPES_TL OETL
, OE_TRANSACTION_TYPES_TL OETL1
, OE_TRANSACTION_TYPES_ALL OEOT
, OE_TRANSACTION_TYPES_ALL OEOT1
, MTL_CUSTOMER_ITEMS MCI
, /* PO_LINES_TRX_V POL
, */ (SELECT PO_LINE_ID
, UNIT_MEAS_LOOKUP_CODE
, UNIT_PRICE
, UN_NUMBER_ID
, HAZARD_CLASS_ID
, VENDOR_PRODUCT_NUM
, COST_CONSTRAINT
, ORDER_TYPE_LOOKUP_CODE
, LINE_NUM
FROM PO_LINES
WHERE NVL(CLM_INFO_FLAG
, 'N') = 'N'
AND ( NVL(CLM_OPTION_INDICATOR
, 'B') <> 'O' OR CLM_EXERCISED_FLAG = 'Y' ) ) POL
, /* OR NVL(CLM_EXERCISED_FLAG
, 'N') = 'Y' ) ) POL
, */ PO_HEADERS_TRX_V POH
, /* PO_LINE_LOCATIONS_TRX_V PLL
, */ PO_LINE_LOCATIONS_ALL PLL
, PO_RELEASES_ALL PR
, PO_VENDORS POV
, RCV_ROUTING_HEADERS RRH
, HR_LOCATIONS_ALL_TL HRL
, GL_DAILY_CONVERSION_TYPES DCT
, HR_ALL_ORGANIZATION_UNITS_TL OOD
, FINANCIALS_SYSTEM_PARAMS_ALL FSP
WHERE EXISTS (SELECT /*+ NO_UNNEST */ 'S'
FROM PO_LINES PLTV
WHERE NVL(PLTV.CLM_INFO_FLAG
, 'N') = 'N'
AND ( NVL(PLTV.CLM_OPTION_INDICATOR
, 'B') <> 'O' OR PLTV.CLM_EXERCISED_FLAG = 'Y' )
AND PLL.PO_LINE_ID = PLTV.PO_LINE_ID UNION ALL SELECT /*+ NO_UNNEST */ 'X'
FROM DUAL
WHERE PLL.PO_LINE_ID IS NULL )
AND RSUP.SUPPLY_TYPE_CODE = 'RECEIVING'
AND RSL.SHIPMENT_LINE_ID = RSUP.SHIPMENT_LINE_ID
AND RT.TRANSACTION_ID = RSUP.RCV_TRANSACTION_ID
AND RT.TRANSACTION_TYPE != 'UNORDERED'
AND RRH.ROUTING_HEADER_ID(+) = RT.ROUTING_HEADER_ID
AND RSH.SHIPMENT_HEADER_ID = RSUP.SHIPMENT_HEADER_ID
AND RT.TRANSACTION_TYPE = PLC.LOOKUP_CODE
AND PLC.LOOKUP_TYPE = 'RCV TRANSACTION TYPE'
AND PLC1.LOOKUP_TYPE(+) = 'INSPECTION STATUS'
AND PLC1.LOOKUP_CODE(+) = RT.INSPECTION_STATUS_CODE
AND PLC2.LOOKUP_TYPE(+) = 'RCV DESTINATION TYPE'
AND PLC2.LOOKUP_CODE(+) = RSUP.DESTINATION_TYPE_CODE
AND OEL.LINE_ID(+) = RSUP.OE_ORDER_LINE_ID
AND OEH.HEADER_ID(+) = RSUP.OE_ORDER_HEADER_ID
AND OEH.ORDER_TYPE_ID = OEOT1.TRANSACTION_TYPE_ID(+)
AND OEL.LINE_TYPE_ID = OEOT.TRANSACTION_TYPE_ID(+)
AND OEOT.TRANSACTION_TYPE_ID = OETL.TRANSACTION_TYPE_ID(+)
AND OEOT1.TRANSACTION_TYPE_ID = OETL1.TRANSACTION_TYPE_ID(+)
AND OETL.LANGUAGE(+) = USERENV('LANG')
AND OETL1.LANGUAGE(+) = USERENV('LANG')
AND OEOT.TRANSACTION_TYPE_CODE(+) = 'LINE'
AND OEOT1.TRANSACTION_TYPE_CODE(+) = 'ORDER'
AND HZCA.CUST_ACCOUNT_ID(+) = RSH.CUSTOMER_ID
AND HZCA.PARTY_ID = HZP.PARTY_ID(+)
AND OEL.ORDERED_ITEM_ID = MCI.CUSTOMER_ITEM_ID(+)
AND POL.PO_LINE_ID(+) = RSUP.PO_LINE_ID
AND POH.PO_HEADER_ID(+) = RSUP.PO_HEADER_ID
AND POV.VENDOR_ID(+) = RSH.VENDOR_ID
AND PLL.LINE_LOCATION_ID(+) = RSUP.PO_LINE_LOCATION_ID
AND NVL(PLL.ORG_ID
, -99) = NVL(POH.ORG_ID
, -99)
AND MUM.UNIT_OF_MEASURE = RSUP.UNIT_OF_MEASURE
AND MUM2.UNIT_OF_MEASURE(+) = POL.UNIT_MEAS_LOOKUP_CODE
AND (MUM4.UNIT_OF_MEASURE (+) = PLL.SECONDARY_UNIT_OF_MEASURE)
AND (MUM3.UOM_CODE (+) =OEL.ORDERED_QUANTITY_UOM2)
AND MSI.ORGANIZATION_ID (+) = RSUP.TO_ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID (+) = RSUP.ITEM_ID
AND MUM1.UNIT_OF_MEASURE (+) = MSI.PRIMARY_UNIT_OF_MEASURE
AND MSI1.ORGANIZATION_ID (+) = RSUP.FROM_ORGANIZATION_ID
AND MSI1.INVENTORY_ITEM_ID(+) = RSUP.ITEM_ID
AND PR.PO_RELEASE_ID (+) = RSUP.PO_RELEASE_ID
AND MTR.REASON_ID (+) = RT.REASON_ID
AND HRL.LOCATION_ID (+) = RT.LOCATION_ID
AND HRL.LANGUAGE(+) = USERENV('LANG')
AND OOD.ORGANIZATION_ID(+) = RSL.FROM_ORGANIZATION_ID
AND OOD.LANGUAGE(+) = USERENV('LANG')
AND DECODE(RT.SOURCE_DOCUMENT_CODE
, 'PO'
, 'PO TYPE'
, 'SHIPMENT SOURCE TYPE') = PLC3.LOOKUP_TYPE
AND DECODE(RT.SOURCE_DOCUMENT_CODE
, 'PO'
, POH.TYPE_LOOKUP_CODE
, RSH.RECEIPT_SOURCE_CODE) = PLC3.LOOKUP_CODE
AND DCT.CONVERSION_TYPE (+) = RT.CURRENCY_CONVERSION_TYPE
AND NVL(PLL.MATCHING_BASIS(+)
, 'QUANTITY') != 'AMOUNT'
AND PLL.PAYMENT_TYPE IS NULL
AND POH.ORG_ID = FSP.ORG_ID (+)
AND ( RT.SOURCE_DOCUMENT_CODE!='PO' OR ( /*NVL(FSP.PURCH_ENCUMBRANCE_FLAG
, 'N') = 'Y'
AND NVL(PLL.ENCUMBERED_FLAG
, 'N') = 'Y' */ FSP.PURCH_ENCUMBRANCE_FLAG = 'Y'
AND PLL.ENCUMBERED_FLAG = 'Y' ) OR NVL(FSP.PURCH_ENCUMBRANCE_FLAG
, 'N') = 'N' OR ( FSP.PURCH_ENCUMBRANCE_FLAG = 'Y'
AND NVL(PLL.ENCUMBERED_FLAG
, 'N') = 'N'
AND RT.SOURCE_DOCUMENT_CODE = 'PO'
AND EXISTS (SELECT /*+ NO_UNNEST */ 1
FROM PO_DISTRIBUTIONS_ALL POD
, PO_LINES_TRX_V PLTV
WHERE POD.PO_LINE_ID = PLTV.PO_LINE_ID
AND POD.LINE_LOCATION_ID = PLL.LINE_LOCATION_ID
AND DESTINATION_TYPE_CODE = 'SHOP FLOOR' OR (PLTV.COST_CONSTRAINT IN ('NSP'
, 'NC')) OR (PLTV.ORDER_TYPE_LOOKUP_CODE = 'QUANTITY'
AND (PLTV.UNIT_PRICE = 0 OR PLTV.UNIT_PRICE IS NULL)) ) ) )