VIEW: APPS.RCV_DISTRIBUTIONS_PRINT_RMA

Source

View Text - Preformatted

SELECT RCT.ORGANIZATION_ID, PLC.DISPLAYED_FIELD , NVL(PPF2.FULL_NAME, '') || ',' || HRL.LOCATION_CODE || ',' || OOD.NAME || ',' || NVL(RSL.TO_SUBINVENTORY, RCT.SUBINVENTORY) , TO_CHAR(OEH.ORDER_NUMBER) , MUOM.UNIT_OF_MEASURE , NVL(OEL.ORDERED_QUANTITY,0) - NVL(OEL.FULFILLED_QUANTITY,0) - NVL(OEL.CANCELLED_QUANTITY,0) , DECODE(OEOT.ORDER_CATEGORY_CODE, 'RETURN', OEH.SHIPPING_INSTRUCTIONS,NULL) , 'RMA' , RCT.TRANSACTION_ID , RCT.QUANTITY , RCT.TRANSACTION_DATE , PPF.FULL_NAME , RSL.SHIPMENT_LINE_ID , 'N' , PPF2.FULL_NAME , HRL.LOCATION_CODE , OOD.NAME , NVL(RSL.TO_SUBINVENTORY, RCT.SUBINVENTORY) , RCT.LOCATOR_ID , TO_NUMBER(NULL) , TO_NUMBER(NULL) , TO_NUMBER(NULL) , TO_NUMBER(NULL) , TO_NUMBER(NULL) , TO_NUMBER(NULL), OEL.SHIPPING_INSTRUCTIONS, WLPN1.LICENSE_PLATE_NUMBER LICENSE_PLATE_NUMBER, WLPN2.LICENSE_PLATE_NUMBER TRANSFER_LICENSE_PLATE_NUMBER FROM PO_LOOKUP_CODES PLC , RCV_SHIPMENT_LINES RSL , RCV_SHIPMENT_HEADERS RSH , RCV_TRANSACTIONS RCT , PER_ALL_PEOPLE_F PPF2 , PER_ALL_PEOPLE_F PPF , HR_ALL_ORGANIZATION_UNITS_TL OOD , OE_ORDER_LINES_ALL OEL , OE_ORDER_HEADERS_ALL OEH , OE_TRANSACTION_TYPES_ALL OEOT, OE_TRANSACTION_TYPES_TL OETL, HR_LOCATIONS_ALL_TL HRL, MTL_UNITS_OF_MEASURE_TL MUOM, WMS_LICENSE_PLATE_NUMBERS WLPN1, WMS_LICENSE_PLATE_NUMBERS WLPN2 WHERE PLC.LOOKUP_TYPE = 'DESTINATION TYPE' AND PLC.LOOKUP_CODE = NVL(RCT.DESTINATION_TYPE_CODE, RSL.DESTINATION_TYPE_CODE) AND RCT.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID(+) AND RCT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID AND RSH.RECEIPT_SOURCE_CODE IN ('CUSTOMER') AND OOD.ORGANIZATION_ID(+) = RCT.ORGANIZATION_ID AND OOD.LANGUAGE(+) = USERENV('LANG') AND PPF2.PERSON_ID(+) = RSL.DELIVER_TO_PERSON_ID AND ((PPF2.BUSINESS_GROUP_ID IN (SELECT NVL(FSP.BUSINESS_GROUP_ID,0) FROM FINANCIALS_SYSTEM_PARAMS_ALL FSP) AND TRUNC(SYSDATE) BETWEEN PPF2.EFFECTIVE_START_DATE AND PPF2.EFFECTIVE_END_DATE) OR (PPF2.PERSON_ID IS NULL)) AND PPF.PERSON_ID(+) = RCT.EMPLOYEE_ID AND ((PPF.BUSINESS_GROUP_ID IN (SELECT NVL(FSP.BUSINESS_GROUP_ID,0) FROM FINANCIALS_SYSTEM_PARAMS_ALL FSP) AND TRUNC(SYSDATE) BETWEEN PPF.EFFECTIVE_START_DATE AND PPF.EFFECTIVE_END_DATE) OR (PPF.PERSON_ID IS NULL)) AND RCT.LOCATION_ID = HRL.LOCATION_ID (+) AND HRL.LANGUAGE(+) = USERENV('LANG') AND OEL.LINE_ID = RSL.OE_ORDER_LINE_ID AND RSL.OE_ORDER_HEADER_ID = RCT.OE_ORDER_HEADER_ID AND RSL.OE_ORDER_LINE_ID = RCT.OE_ORDER_LINE_ID AND OEH.HEADER_ID = OEL.HEADER_ID AND OEH.ORDER_TYPE_ID = OEOT.TRANSACTION_TYPE_ID(+) AND OEOT.TRANSACTION_TYPE_CODE(+) = 'ORDER' AND OEOT.TRANSACTION_TYPE_ID = OETL.TRANSACTION_TYPE_ID(+) AND OETL.LANGUAGE(+) = USERENV('LANG') AND MUOM.UOM_CODE = OEL.ORDER_QUANTITY_UOM AND RCT.LPN_ID = WLPN1.LPN_ID(+) AND RCT.TRANSFER_LPN_ID = WLPN2.LPN_ID (+) UNION SELECT RCT.TO_ORGANIZATION_ID, PLC.DISPLAYED_FIELD , HRL.LOCATION_CODE || ',' || OOD.NAME || ',' || RCT.SUBINVENTORY , TO_CHAR(OEH.ORDER_NUMBER) , MUOM.UNIT_OF_MEASURE , NVL(OEL.ORDERED_QUANTITY,0) - NVL(OEL.FULFILLED_QUANTITY,0) - NVL(OEL.CANCELLED_QUANTITY,0) , DECODE(OEOT.ORDER_CATEGORY_CODE, 'RETURN', OEH.SHIPPING_INSTRUCTIONS,NULL) , 'RMA' , RCT.INTERFACE_TRANSACTION_ID , RCT.QUANTITY , RCT.TRANSACTION_DATE , PPF.FULL_NAME , RCT.SHIPMENT_LINE_ID , 'Y' , NULL , HRL.LOCATION_CODE , OOD.NAME , RCT.SUBINVENTORY , RCT.LOCATOR_ID , TO_NUMBER(NULL) , TO_NUMBER(NULL) , TO_NUMBER(NULL) , TO_NUMBER(NULL) , TO_NUMBER(NULL) , TO_NUMBER(NULL), OEL.SHIPPING_INSTRUCTIONS , WLPN1.LICENSE_PLATE_NUMBER LICENSE_PLATE_NUMBER, WLPN2.LICENSE_PLATE_NUMBER TRANSFER_LICENSE_PLATE_NUMBER FROM PO_LOOKUP_CODES PLC , RCV_TRANSACTIONS_INTERFACE RCT , PER_ALL_PEOPLE_F PPF2 , PER_ALL_PEOPLE_F PPF , HR_ALL_ORGANIZATION_UNITS_TL OOD , OE_ORDER_LINES_ALL OEL , OE_ORDER_HEADERS_ALL OEH , OE_TRANSACTION_TYPES_ALL OEOT, OE_TRANSACTION_TYPES_TL OETL, HR_LOCATIONS_ALL_TL HRL, MTL_UNITS_OF_MEASURE_TL MUOM, WMS_LICENSE_PLATE_NUMBERS WLPN1, WMS_LICENSE_PLATE_NUMBERS WLPN2 WHERE PLC.LOOKUP_TYPE = 'DESTINATION TYPE' AND PLC.LOOKUP_CODE = RCT.DESTINATION_TYPE_CODE AND RCT.RECEIPT_SOURCE_CODE IN ('CUSTOMER') AND OOD.ORGANIZATION_ID(+) = RCT.TO_ORGANIZATION_ID AND OOD.LANGUAGE(+) = USERENV('LANG') AND PPF.PERSON_ID(+) = RCT.EMPLOYEE_ID AND ((PPF.BUSINESS_GROUP_ID IN (SELECT NVL(FSP.BUSINESS_GROUP_ID,0) FROM FINANCIALS_SYSTEM_PARAMS_ALL FSP) AND TRUNC(SYSDATE) BETWEEN PPF.EFFECTIVE_START_DATE AND PPF.EFFECTIVE_END_DATE) OR (PPF.PERSON_ID IS NULL)) AND RCT.LOCATION_ID = HRL.LOCATION_ID (+) AND HRL.LANGUAGE(+) = USERENV('LANG') AND OEL.LINE_ID = RCT.OE_ORDER_LINE_ID AND OEH.HEADER_ID = OEL.HEADER_ID AND OEH.ORDER_TYPE_ID = OEOT.TRANSACTION_TYPE_ID(+) AND OEOT.TRANSACTION_TYPE_CODE(+) = 'ORDER' AND OEOT.TRANSACTION_TYPE_ID = OETL.TRANSACTION_TYPE_ID(+) AND OETL.LANGUAGE(+) = USERENV('LANG') AND MUOM.UOM_CODE = OEL.ORDER_QUANTITY_UOM AND RCT.LPN_ID = WLPN1.LPN_ID(+) AND RCT.TRANSFER_LPN_ID = WLPN2.LPN_ID(+)

View Text - HTML Formatted

SELECT RCT.ORGANIZATION_ID
, PLC.DISPLAYED_FIELD
, NVL(PPF2.FULL_NAME
, '') || '
, ' || HRL.LOCATION_CODE || '
, ' || OOD.NAME || '
, ' || NVL(RSL.TO_SUBINVENTORY
, RCT.SUBINVENTORY)
, TO_CHAR(OEH.ORDER_NUMBER)
, MUOM.UNIT_OF_MEASURE
, NVL(OEL.ORDERED_QUANTITY
, 0) - NVL(OEL.FULFILLED_QUANTITY
, 0) - NVL(OEL.CANCELLED_QUANTITY
, 0)
, DECODE(OEOT.ORDER_CATEGORY_CODE
, 'RETURN'
, OEH.SHIPPING_INSTRUCTIONS
, NULL)
, 'RMA'
, RCT.TRANSACTION_ID
, RCT.QUANTITY
, RCT.TRANSACTION_DATE
, PPF.FULL_NAME
, RSL.SHIPMENT_LINE_ID
, 'N'
, PPF2.FULL_NAME
, HRL.LOCATION_CODE
, OOD.NAME
, NVL(RSL.TO_SUBINVENTORY
, RCT.SUBINVENTORY)
, RCT.LOCATOR_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, OEL.SHIPPING_INSTRUCTIONS
, WLPN1.LICENSE_PLATE_NUMBER LICENSE_PLATE_NUMBER
, WLPN2.LICENSE_PLATE_NUMBER TRANSFER_LICENSE_PLATE_NUMBER
FROM PO_LOOKUP_CODES PLC
, RCV_SHIPMENT_LINES RSL
, RCV_SHIPMENT_HEADERS RSH
, RCV_TRANSACTIONS RCT
, PER_ALL_PEOPLE_F PPF2
, PER_ALL_PEOPLE_F PPF
, HR_ALL_ORGANIZATION_UNITS_TL OOD
, OE_ORDER_LINES_ALL OEL
, OE_ORDER_HEADERS_ALL OEH
, OE_TRANSACTION_TYPES_ALL OEOT
, OE_TRANSACTION_TYPES_TL OETL
, HR_LOCATIONS_ALL_TL HRL
, MTL_UNITS_OF_MEASURE_TL MUOM
, WMS_LICENSE_PLATE_NUMBERS WLPN1
, WMS_LICENSE_PLATE_NUMBERS WLPN2
WHERE PLC.LOOKUP_TYPE = 'DESTINATION TYPE'
AND PLC.LOOKUP_CODE = NVL(RCT.DESTINATION_TYPE_CODE
, RSL.DESTINATION_TYPE_CODE)
AND RCT.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID(+)
AND RCT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
AND RSH.RECEIPT_SOURCE_CODE IN ('CUSTOMER')
AND OOD.ORGANIZATION_ID(+) = RCT.ORGANIZATION_ID
AND OOD.LANGUAGE(+) = USERENV('LANG')
AND PPF2.PERSON_ID(+) = RSL.DELIVER_TO_PERSON_ID
AND ((PPF2.BUSINESS_GROUP_ID IN (SELECT NVL(FSP.BUSINESS_GROUP_ID
, 0)
FROM FINANCIALS_SYSTEM_PARAMS_ALL FSP)
AND TRUNC(SYSDATE) BETWEEN PPF2.EFFECTIVE_START_DATE
AND PPF2.EFFECTIVE_END_DATE) OR (PPF2.PERSON_ID IS NULL))
AND PPF.PERSON_ID(+) = RCT.EMPLOYEE_ID
AND ((PPF.BUSINESS_GROUP_ID IN (SELECT NVL(FSP.BUSINESS_GROUP_ID
, 0)
FROM FINANCIALS_SYSTEM_PARAMS_ALL FSP)
AND TRUNC(SYSDATE) BETWEEN PPF.EFFECTIVE_START_DATE
AND PPF.EFFECTIVE_END_DATE) OR (PPF.PERSON_ID IS NULL))
AND RCT.LOCATION_ID = HRL.LOCATION_ID (+)
AND HRL.LANGUAGE(+) = USERENV('LANG')
AND OEL.LINE_ID = RSL.OE_ORDER_LINE_ID
AND RSL.OE_ORDER_HEADER_ID = RCT.OE_ORDER_HEADER_ID
AND RSL.OE_ORDER_LINE_ID = RCT.OE_ORDER_LINE_ID
AND OEH.HEADER_ID = OEL.HEADER_ID
AND OEH.ORDER_TYPE_ID = OEOT.TRANSACTION_TYPE_ID(+)
AND OEOT.TRANSACTION_TYPE_CODE(+) = 'ORDER'
AND OEOT.TRANSACTION_TYPE_ID = OETL.TRANSACTION_TYPE_ID(+)
AND OETL.LANGUAGE(+) = USERENV('LANG')
AND MUOM.UOM_CODE = OEL.ORDER_QUANTITY_UOM
AND RCT.LPN_ID = WLPN1.LPN_ID(+)
AND RCT.TRANSFER_LPN_ID = WLPN2.LPN_ID (+) UNION SELECT RCT.TO_ORGANIZATION_ID
, PLC.DISPLAYED_FIELD
, HRL.LOCATION_CODE || '
, ' || OOD.NAME || '
, ' || RCT.SUBINVENTORY
, TO_CHAR(OEH.ORDER_NUMBER)
, MUOM.UNIT_OF_MEASURE
, NVL(OEL.ORDERED_QUANTITY
, 0) - NVL(OEL.FULFILLED_QUANTITY
, 0) - NVL(OEL.CANCELLED_QUANTITY
, 0)
, DECODE(OEOT.ORDER_CATEGORY_CODE
, 'RETURN'
, OEH.SHIPPING_INSTRUCTIONS
, NULL)
, 'RMA'
, RCT.INTERFACE_TRANSACTION_ID
, RCT.QUANTITY
, RCT.TRANSACTION_DATE
, PPF.FULL_NAME
, RCT.SHIPMENT_LINE_ID
, 'Y'
, NULL
, HRL.LOCATION_CODE
, OOD.NAME
, RCT.SUBINVENTORY
, RCT.LOCATOR_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, OEL.SHIPPING_INSTRUCTIONS
, WLPN1.LICENSE_PLATE_NUMBER LICENSE_PLATE_NUMBER
, WLPN2.LICENSE_PLATE_NUMBER TRANSFER_LICENSE_PLATE_NUMBER
FROM PO_LOOKUP_CODES PLC
, RCV_TRANSACTIONS_INTERFACE RCT
, PER_ALL_PEOPLE_F PPF2
, PER_ALL_PEOPLE_F PPF
, HR_ALL_ORGANIZATION_UNITS_TL OOD
, OE_ORDER_LINES_ALL OEL
, OE_ORDER_HEADERS_ALL OEH
, OE_TRANSACTION_TYPES_ALL OEOT
, OE_TRANSACTION_TYPES_TL OETL
, HR_LOCATIONS_ALL_TL HRL
, MTL_UNITS_OF_MEASURE_TL MUOM
, WMS_LICENSE_PLATE_NUMBERS WLPN1
, WMS_LICENSE_PLATE_NUMBERS WLPN2
WHERE PLC.LOOKUP_TYPE = 'DESTINATION TYPE'
AND PLC.LOOKUP_CODE = RCT.DESTINATION_TYPE_CODE
AND RCT.RECEIPT_SOURCE_CODE IN ('CUSTOMER')
AND OOD.ORGANIZATION_ID(+) = RCT.TO_ORGANIZATION_ID
AND OOD.LANGUAGE(+) = USERENV('LANG')
AND PPF.PERSON_ID(+) = RCT.EMPLOYEE_ID
AND ((PPF.BUSINESS_GROUP_ID IN (SELECT NVL(FSP.BUSINESS_GROUP_ID
, 0)
FROM FINANCIALS_SYSTEM_PARAMS_ALL FSP)
AND TRUNC(SYSDATE) BETWEEN PPF.EFFECTIVE_START_DATE
AND PPF.EFFECTIVE_END_DATE) OR (PPF.PERSON_ID IS NULL))
AND RCT.LOCATION_ID = HRL.LOCATION_ID (+)
AND HRL.LANGUAGE(+) = USERENV('LANG')
AND OEL.LINE_ID = RCT.OE_ORDER_LINE_ID
AND OEH.HEADER_ID = OEL.HEADER_ID
AND OEH.ORDER_TYPE_ID = OEOT.TRANSACTION_TYPE_ID(+)
AND OEOT.TRANSACTION_TYPE_CODE(+) = 'ORDER'
AND OEOT.TRANSACTION_TYPE_ID = OETL.TRANSACTION_TYPE_ID(+)
AND OETL.LANGUAGE(+) = USERENV('LANG')
AND MUOM.UOM_CODE = OEL.ORDER_QUANTITY_UOM
AND RCT.LPN_ID = WLPN1.LPN_ID(+)
AND RCT.TRANSFER_LPN_ID = WLPN2.LPN_ID(+)