VIEW: APPS.RCV_RECEIPTS_PRINT_RMA

Source

View Text - Preformatted

SELECT rsl.item_id , '' , RSH.SHIP_TO_ORG_ID , rsl.item_revision , mic.category_id , rsl.item_description , '' , '' , '' , rrh.routing_name , PLC1.DISPLAYED_FIELD , to_char(OEH.ORDER_NUMBER) , to_number(null) , oel.line_number , null buyer_preparer_name , plc.displayed_field , substrb(hzp.party_name,1,50) , MUOM.UNIT_OF_MEASURE , rsl.customer_item_num , oel.promise_date , nvl(oel.ordered_quantity, 0) - nvl(oel.cancelled_quantity, 0) , DECODE(OEOT.ORDER_CATEGORY_CODE, 'RETURN', OEH.SHIPPING_INSTRUCTIONS,NULL) , rsh.receipt_num , rct.unit_of_measure , rct.transaction_date , hrl.location_code , hre.first_name , hre.last_name , rsh.FREIGHT_CARRIER_CODE , rct.quantity , rct.primary_quantity , nvl(oel.ordered_quantity,0) - nvl(oel.shipped_quantity,0) - nvl(oel.cancelled_quantity,0) , rsh.shipment_num , rsh.bill_of_lading , rct.vendor_lot_num , NVL(RSL.PACKING_SLIP,RSH.PACKING_SLIP) , rsh.num_of_containers , RSL.COMMENTS || DECODE(RSL.COMMENTS,NULL,TO_CHAR(NULL) , DECODE(RSH.COMMENTS,NULL ,TO_CHAR(NULL) ,' , ')) || RSH.COMMENTS , 'RMA' , rct.transaction_id , rct.group_id , rct.shipment_header_id , rct.shipment_line_id , rsl.shipment_line_id , to_number(null) , to_number(null) , to_number(null) , to_number(null) , to_number(null) , 'N' , hrl1.location_code , OEL.SHIPPING_INSTRUCTIONS ,RCT.SUBINVENTORY ,RCT.LOCATOR_ID ,WLPN1.LICENSE_PLATE_NUMBER LICENSE_PLATE_NUMBER ,WLPN2.LICENSE_PLATE_NUMBER TRANSFER_LICENSE_PLATE_NUMBER ,OEH.ORG_ID FROM rcv_transactions rct , rcv_shipment_headers rsh , rcv_shipment_lines rsl , rcv_routing_headers rrh , po_lookup_codes plc , po_lookup_codes plc1 , hz_cust_accounts hzca , hz_parties hzp , 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 , per_all_people_f hre , hr_locations_all_tl hrl1 , mtl_item_categories mic , mtl_default_sets_view mdsv , MTL_UNITS_OF_MEASURE_TL MUOM , WMS_LICENSE_PLATE_NUMBERS WLPN1 , WMS_LICENSE_PLATE_NUMBERS WLPN2 WHERE rct.source_document_code = 'RMA' And mic.inventory_item_id = rsl.item_id And mic.organization_id = rct.organization_id And mdsv.functional_area_id = 2 And mdsv.category_set_id = mic.category_set_id And rsh.shipment_header_id = rsl.shipment_header_id And rsl.oe_order_header_id = rct.oe_order_header_id And rsl.oe_order_line_id = rct.oe_order_line_id And RSL.ROUTING_HEADER_ID = RRH.ROUTING_HEADER_ID (+) AND PLC.LOOKUP_CODE = RSH.RECEIPT_SOURCE_CODE AND PLC.LOOKUP_TYPE = 'SHIPMENT SOURCE TYPE' AND PLC1.LOOKUP_CODE = RCT.SOURCE_DOCUMENT_CODE AND PLC1.LOOKUP_TYPE = 'SHIPMENT SOURCE DOCUMENT TYPE' AND hzca.cust_account_id(+) =rsh.customer_id AND hzca.party_id = hzp.party_id(+) AND oel.line_id = rsl.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 RCT.LOCATION_ID = HRL.LOCATION_ID (+) AND HRL.LANGUAGE(+) = USERENV('LANG') AND RCT.EMPLOYEE_ID = HRE.PERSON_ID (+) AND (( TRUNC(SYSDATE) BETWEEN HRE.EFFECTIVE_START_DATE AND HRE.EFFECTIVE_END_DATE AND ( HRE.EMPLOYEE_NUMBER IS NOT NULL OR ( HRE.EMPLOYEE_NUMBER IS NULL AND HRE.NPW_NUMBER IS NOT NULL ))) OR HRE.PERSON_ID IS NULL ) AND RCT.DELIVER_TO_LOCATION_ID = HRL1.LOCATION_ID (+) AND HRL1.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.item_id , '' , RSH.SHIP_TO_ORG_ID , rct.item_revision , mic.category_id , rct.item_description , '' , '' , '' , rrh.routing_name , PLC1.DISPLAYED_FIELD , to_char(OEH.ORDER_NUMBER) , to_number(null) , oel.line_number , null buyer_preparer_name , plc.displayed_field , substrb(hzp.party_name,1,50) , MUOM.UNIT_OF_MEASURE , rct.customer_item_num , oel.promise_date , nvl(oel.ordered_quantity, 0) - nvl(oel.cancelled_quantity, 0) , DECODE(OEOT.ORDER_CATEGORY_CODE, 'RETURN', OEH.SHIPPING_INSTRUCTIONS,NULL) , rsh.receipt_num , rct.unit_of_measure , rct.transaction_date , hrl.location_code , hre.first_name , hre.last_name , rsh.FREIGHT_CARRIER_CODE , rct.quantity , rct.primary_quantity , nvl(oel.ordered_quantity,0) - nvl(oel.shipped_quantity,0) - nvl(oel.cancelled_quantity,0) , rsh.shipment_num , rsh.bill_of_lading , rct.vendor_lot_num , RSH.PACKING_SLIP , rsh.num_of_containers , DECODE(RSH.COMMENTS,NULL ,TO_CHAR(NULL) ,' , ') || RSH.COMMENTS , 'RMA' , rct.interface_transaction_id , rct.group_id , rct.shipment_header_id , rct.shipment_line_id , to_number(null) , to_number(null) , to_number(null) , to_number(null) , to_number(null) , to_number(null) , 'Y' , hrl1.location_code , OEL.SHIPPING_INSTRUCTIONS ,RCT.SUBINVENTORY ,RCT.LOCATOR_ID ,WLPN1.LICENSE_PLATE_NUMBER LICENSE_PLATE_NUMBER ,WLPN2.LICENSE_PLATE_NUMBER TRANSFER_LICENSE_PLATE_NUMBER ,OEH.ORG_ID FROM rcv_transactions_interface rct , rcv_shipment_headers rsh , rcv_routing_headers rrh , po_lookup_codes plc , po_lookup_codes plc1 , hz_cust_accounts hzca , hz_parties hzp , 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 , per_all_people_f hre , hr_locations_all_tl hrl1 , mtl_item_categories mic , mtl_default_sets_view mdsv , MTL_UNITS_OF_MEASURE_TL MUOM , WMS_LICENSE_PLATE_NUMBERS WLPN1 , WMS_LICENSE_PLATE_NUMBERS WLPN2 WHERE rct.source_document_code = 'RMA' And mic.inventory_item_id = rct.item_id And mic.organization_id = rct.to_organization_id And mdsv.functional_area_id = 2 And mdsv.category_set_id = mic.category_set_id And rsh.shipment_header_id = rct.shipment_header_id And RCT.ROUTING_HEADER_ID = RRH.ROUTING_HEADER_ID (+) AND PLC.LOOKUP_CODE = RSH.RECEIPT_SOURCE_CODE AND PLC.LOOKUP_TYPE = 'SHIPMENT SOURCE TYPE' AND PLC1.LOOKUP_CODE = RCT.SOURCE_DOCUMENT_CODE AND PLC1.LOOKUP_TYPE = 'SHIPMENT SOURCE DOCUMENT TYPE' AND hzca.cust_account_id(+) =rsh.customer_id AND hzca.party_id = hzp.party_id(+) 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 RCT.LOCATION_ID = HRL.LOCATION_ID (+) AND HRL.LANGUAGE(+) = USERENV('LANG') AND RCT.EMPLOYEE_ID = HRE.PERSON_ID (+) AND (( TRUNC(SYSDATE) BETWEEN HRE.EFFECTIVE_START_DATE AND HRE.EFFECTIVE_END_DATE AND ( HRE.EMPLOYEE_NUMBER IS NOT NULL OR ( HRE.EMPLOYEE_NUMBER IS NULL AND HRE.NPW_NUMBER IS NOT NULL ))) OR HRE.PERSON_ID IS NULL ) AND RCT.DELIVER_TO_LOCATION_ID = HRL1.LOCATION_ID (+) AND HRL1.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 RSL.ITEM_ID
, ''
, RSH.SHIP_TO_ORG_ID
, RSL.ITEM_REVISION
, MIC.CATEGORY_ID
, RSL.ITEM_DESCRIPTION
, ''
, ''
, ''
, RRH.ROUTING_NAME
, PLC1.DISPLAYED_FIELD
, TO_CHAR(OEH.ORDER_NUMBER)
, TO_NUMBER(NULL)
, OEL.LINE_NUMBER
, NULL BUYER_PREPARER_NAME
, PLC.DISPLAYED_FIELD
, SUBSTRB(HZP.PARTY_NAME
, 1
, 50)
, MUOM.UNIT_OF_MEASURE
, RSL.CUSTOMER_ITEM_NUM
, OEL.PROMISE_DATE
, NVL(OEL.ORDERED_QUANTITY
, 0) - NVL(OEL.CANCELLED_QUANTITY
, 0)
, DECODE(OEOT.ORDER_CATEGORY_CODE
, 'RETURN'
, OEH.SHIPPING_INSTRUCTIONS
, NULL)
, RSH.RECEIPT_NUM
, RCT.UNIT_OF_MEASURE
, RCT.TRANSACTION_DATE
, HRL.LOCATION_CODE
, HRE.FIRST_NAME
, HRE.LAST_NAME
, RSH.FREIGHT_CARRIER_CODE
, RCT.QUANTITY
, RCT.PRIMARY_QUANTITY
, NVL(OEL.ORDERED_QUANTITY
, 0) - NVL(OEL.SHIPPED_QUANTITY
, 0) - NVL(OEL.CANCELLED_QUANTITY
, 0)
, RSH.SHIPMENT_NUM
, RSH.BILL_OF_LADING
, RCT.VENDOR_LOT_NUM
, NVL(RSL.PACKING_SLIP
, RSH.PACKING_SLIP)
, RSH.NUM_OF_CONTAINERS
, RSL.COMMENTS || DECODE(RSL.COMMENTS
, NULL
, TO_CHAR(NULL)
, DECODE(RSH.COMMENTS
, NULL
, TO_CHAR(NULL)
, '
, ')) || RSH.COMMENTS
, 'RMA'
, RCT.TRANSACTION_ID
, RCT.GROUP_ID
, RCT.SHIPMENT_HEADER_ID
, RCT.SHIPMENT_LINE_ID
, RSL.SHIPMENT_LINE_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, 'N'
, HRL1.LOCATION_CODE
, OEL.SHIPPING_INSTRUCTIONS
, RCT.SUBINVENTORY
, RCT.LOCATOR_ID
, WLPN1.LICENSE_PLATE_NUMBER LICENSE_PLATE_NUMBER
, WLPN2.LICENSE_PLATE_NUMBER TRANSFER_LICENSE_PLATE_NUMBER
, OEH.ORG_ID
FROM RCV_TRANSACTIONS RCT
, RCV_SHIPMENT_HEADERS RSH
, RCV_SHIPMENT_LINES RSL
, RCV_ROUTING_HEADERS RRH
, PO_LOOKUP_CODES PLC
, PO_LOOKUP_CODES PLC1
, HZ_CUST_ACCOUNTS HZCA
, HZ_PARTIES HZP
, 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
, PER_ALL_PEOPLE_F HRE
, HR_LOCATIONS_ALL_TL HRL1
, MTL_ITEM_CATEGORIES MIC
, MTL_DEFAULT_SETS_VIEW MDSV
, MTL_UNITS_OF_MEASURE_TL MUOM
, WMS_LICENSE_PLATE_NUMBERS WLPN1
, WMS_LICENSE_PLATE_NUMBERS WLPN2
WHERE RCT.SOURCE_DOCUMENT_CODE = 'RMA'
AND MIC.INVENTORY_ITEM_ID = RSL.ITEM_ID
AND MIC.ORGANIZATION_ID = RCT.ORGANIZATION_ID
AND MDSV.FUNCTIONAL_AREA_ID = 2
AND MDSV.CATEGORY_SET_ID = MIC.CATEGORY_SET_ID
AND RSH.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID
AND RSL.OE_ORDER_HEADER_ID = RCT.OE_ORDER_HEADER_ID
AND RSL.OE_ORDER_LINE_ID = RCT.OE_ORDER_LINE_ID
AND RSL.ROUTING_HEADER_ID = RRH.ROUTING_HEADER_ID (+)
AND PLC.LOOKUP_CODE = RSH.RECEIPT_SOURCE_CODE
AND PLC.LOOKUP_TYPE = 'SHIPMENT SOURCE TYPE'
AND PLC1.LOOKUP_CODE = RCT.SOURCE_DOCUMENT_CODE
AND PLC1.LOOKUP_TYPE = 'SHIPMENT SOURCE DOCUMENT TYPE'
AND HZCA.CUST_ACCOUNT_ID(+) =RSH.CUSTOMER_ID
AND HZCA.PARTY_ID = HZP.PARTY_ID(+)
AND OEL.LINE_ID = RSL.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 RCT.LOCATION_ID = HRL.LOCATION_ID (+)
AND HRL.LANGUAGE(+) = USERENV('LANG')
AND RCT.EMPLOYEE_ID = HRE.PERSON_ID (+)
AND (( TRUNC(SYSDATE) BETWEEN HRE.EFFECTIVE_START_DATE
AND HRE.EFFECTIVE_END_DATE
AND ( HRE.EMPLOYEE_NUMBER IS NOT NULL OR ( HRE.EMPLOYEE_NUMBER IS NULL
AND HRE.NPW_NUMBER IS NOT NULL ))) OR HRE.PERSON_ID IS NULL )
AND RCT.DELIVER_TO_LOCATION_ID = HRL1.LOCATION_ID (+)
AND HRL1.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.ITEM_ID
, ''
, RSH.SHIP_TO_ORG_ID
, RCT.ITEM_REVISION
, MIC.CATEGORY_ID
, RCT.ITEM_DESCRIPTION
, ''
, ''
, ''
, RRH.ROUTING_NAME
, PLC1.DISPLAYED_FIELD
, TO_CHAR(OEH.ORDER_NUMBER)
, TO_NUMBER(NULL)
, OEL.LINE_NUMBER
, NULL BUYER_PREPARER_NAME
, PLC.DISPLAYED_FIELD
, SUBSTRB(HZP.PARTY_NAME
, 1
, 50)
, MUOM.UNIT_OF_MEASURE
, RCT.CUSTOMER_ITEM_NUM
, OEL.PROMISE_DATE
, NVL(OEL.ORDERED_QUANTITY
, 0) - NVL(OEL.CANCELLED_QUANTITY
, 0)
, DECODE(OEOT.ORDER_CATEGORY_CODE
, 'RETURN'
, OEH.SHIPPING_INSTRUCTIONS
, NULL)
, RSH.RECEIPT_NUM
, RCT.UNIT_OF_MEASURE
, RCT.TRANSACTION_DATE
, HRL.LOCATION_CODE
, HRE.FIRST_NAME
, HRE.LAST_NAME
, RSH.FREIGHT_CARRIER_CODE
, RCT.QUANTITY
, RCT.PRIMARY_QUANTITY
, NVL(OEL.ORDERED_QUANTITY
, 0) - NVL(OEL.SHIPPED_QUANTITY
, 0) - NVL(OEL.CANCELLED_QUANTITY
, 0)
, RSH.SHIPMENT_NUM
, RSH.BILL_OF_LADING
, RCT.VENDOR_LOT_NUM
, RSH.PACKING_SLIP
, RSH.NUM_OF_CONTAINERS
, DECODE(RSH.COMMENTS
, NULL
, TO_CHAR(NULL)
, '
, ') || RSH.COMMENTS
, 'RMA'
, RCT.INTERFACE_TRANSACTION_ID
, RCT.GROUP_ID
, RCT.SHIPMENT_HEADER_ID
, RCT.SHIPMENT_LINE_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, 'Y'
, HRL1.LOCATION_CODE
, OEL.SHIPPING_INSTRUCTIONS
, RCT.SUBINVENTORY
, RCT.LOCATOR_ID
, WLPN1.LICENSE_PLATE_NUMBER LICENSE_PLATE_NUMBER
, WLPN2.LICENSE_PLATE_NUMBER TRANSFER_LICENSE_PLATE_NUMBER
, OEH.ORG_ID
FROM RCV_TRANSACTIONS_INTERFACE RCT
, RCV_SHIPMENT_HEADERS RSH
, RCV_ROUTING_HEADERS RRH
, PO_LOOKUP_CODES PLC
, PO_LOOKUP_CODES PLC1
, HZ_CUST_ACCOUNTS HZCA
, HZ_PARTIES HZP
, 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
, PER_ALL_PEOPLE_F HRE
, HR_LOCATIONS_ALL_TL HRL1
, MTL_ITEM_CATEGORIES MIC
, MTL_DEFAULT_SETS_VIEW MDSV
, MTL_UNITS_OF_MEASURE_TL MUOM
, WMS_LICENSE_PLATE_NUMBERS WLPN1
, WMS_LICENSE_PLATE_NUMBERS WLPN2
WHERE RCT.SOURCE_DOCUMENT_CODE = 'RMA'
AND MIC.INVENTORY_ITEM_ID = RCT.ITEM_ID
AND MIC.ORGANIZATION_ID = RCT.TO_ORGANIZATION_ID
AND MDSV.FUNCTIONAL_AREA_ID = 2
AND MDSV.CATEGORY_SET_ID = MIC.CATEGORY_SET_ID
AND RSH.SHIPMENT_HEADER_ID = RCT.SHIPMENT_HEADER_ID
AND RCT.ROUTING_HEADER_ID = RRH.ROUTING_HEADER_ID (+)
AND PLC.LOOKUP_CODE = RSH.RECEIPT_SOURCE_CODE
AND PLC.LOOKUP_TYPE = 'SHIPMENT SOURCE TYPE'
AND PLC1.LOOKUP_CODE = RCT.SOURCE_DOCUMENT_CODE
AND PLC1.LOOKUP_TYPE = 'SHIPMENT SOURCE DOCUMENT TYPE'
AND HZCA.CUST_ACCOUNT_ID(+) =RSH.CUSTOMER_ID
AND HZCA.PARTY_ID = HZP.PARTY_ID(+)
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 RCT.LOCATION_ID = HRL.LOCATION_ID (+)
AND HRL.LANGUAGE(+) = USERENV('LANG')
AND RCT.EMPLOYEE_ID = HRE.PERSON_ID (+)
AND (( TRUNC(SYSDATE) BETWEEN HRE.EFFECTIVE_START_DATE
AND HRE.EFFECTIVE_END_DATE
AND ( HRE.EMPLOYEE_NUMBER IS NOT NULL OR ( HRE.EMPLOYEE_NUMBER IS NULL
AND HRE.NPW_NUMBER IS NOT NULL ))) OR HRE.PERSON_ID IS NULL )
AND RCT.DELIVER_TO_LOCATION_ID = HRL1.LOCATION_ID (+)
AND HRL1.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(+)