[Home] [Help]
SELECT RSL.SHIPMENT_HEADER_ID ,
RSL.SHIPMENT_LINE_ID ,
RSL.ITEM_ID ,
MSIBK.CONCATENATED_SEGMENTS ITEM ,
MP.ORGANIZATION_CODE ,
MP.ORGANIZATION_ID ,
RSL.ITEM_DESCRIPTION ,
DECODE((SELECT UPPER(MCR.CROSS_REFERENCE)
FROM MTL_CROSS_REFERENCES_VL MCR
WHERE MCR.INVENTORY_ITEM_ID = MSIBK.INVENTORY_ITEM_ID+0
AND MCR.ORGANIZATION_ID = MSIBK.ORGANIZATION_ID +0
AND TO_CHAR(MCR.LAST_UPDATE_DATE,'DD/MM/YYYY-HH24.MM.SS')=
(SELECT (MAX(TO_CHAR(MCR1.LAST_UPDATE_DATE,'DD/MM/YYYY-HH24.MM.SS')))
FROM MTL_CROSS_REFERENCES_VL MCR1
WHERE MCR1.INVENTORY_ITEM_ID = MSIBK.INVENTORY_ITEM_ID+0
AND MCR1.ORGANIZATION_ID = MSIBK.ORGANIZATION_ID +0
AND MCR1.CROSS_REFERENCE_TYPE ='OPSM INTEGRATED'
)),'NO',0,1) OPSM_INTEGRATED_FLAG,
(SELECT MCR.ATTRIBUTE1
FROM MTL_CROSS_REFERENCES_VL MCR
WHERE MCR.INVENTORY_ITEM_ID = MSIBK.INVENTORY_ITEM_ID+0
AND MCR.ORGANIZATION_ID =(SELECT MASTER_ORGANIZATION_ID
FROM MTL_PARAMETERS
WHERE ORGANIZATION_ID=MSIBK.ORGANIZATION_ID+0)
AND MCR.CROSS_REFERENCE_TYPE ='OPSM INTEGRATED'
AND MCR.CROSS_REFERENCE = 'YES')SERIAL_TYPE,
RSL.QUANTITY_SHIPPED ,
RSL.QUANTITY_RECEIVED ,
RSL.UNIT_OF_MEASURE ,
RSL.SHIPMENT_LINE_STATUS_CODE ,
RSL.BAR_CODE_LABEL ,
RSL.QC_GRADE ,
RSL.ASN_LINE_FLAG ,
RSL.DESTINATION_TYPE_CODE ,
MSIBK.LOT_CONTROL_CODE ,
MSIBK.SERIAL_NUMBER_CONTROL_CODE ,
RSL.OE_ORDER_HEADER_ID ,
OOHA.ORDER_NUMBER ,
OTTT.NAME ORDER_TYPE ,
OOLA.LINE_NUMBER ,
OOLA.SOLD_TO_ORG_ID SOLDBYORGID ,
OOHA.SOLD_TO_CONTACT_ID SOLDBYCONTACTID ,
OOlA.SHIP_TO_ORG_ID SHIPFROMORGID ,
OOLA.SHIP_TO_CONTACT_ID SHIPFROMCONTACTID ,
OOlA.INVOICE_TO_ORG_ID BILLEDBYORGID ,
OOLA.INVOICE_TO_CONTACT_ID BILLEDBYCONTACTID ,
RT.PARENT_TRANSACTION_ID ,
RT.TRANSACTION_ID ,
RT.TRANSACTION_TYPE ,
RT.TRANSACTION_DATE ,
RT.QUANTITY TRANSACTION_QTY ,
RT.UOM_CODE TRANSACTION_UOM ,
RT.PRIMARY_QUANTITY ,
MSIBK.PRIMARY_UOM_CODE ,
RT.SECONDARY_QUANTITY ,
RT.SECONDARY_UOM_CODE ,
'RMA RECEIPT' ,
RT.DESTINATION_TYPE_CODE TRANS_DESTINATION_TYPE_CODE,
RT.GROUP_ID
FROM RCV_SHIPMENT_LINES RSL ,
RCV_TRANSACTIONS RT ,
MTL_SYSTEM_ITEMS_B_KFV MSIBK ,
MTL_PARAMETERS MP ,
OE_ORDER_HEADERS_ALL OOHA ,
OE_ORDER_LINES_ALL OOLA ,
OE_TRANSACTION_TYPES_TL OTTT ,
FND_LANGUAGES FL
WHERE MSIBK.INVENTORY_ITEM_ID=RSL.ITEM_ID
AND MSIBK.ORGANIZATION_ID =RSL.TO_ORGANIZATION_ID
AND MSIBK.ORGANIZATION_ID =MP.ORGANIZATION_ID
AND RSL.SHIPMENT_HEADER_ID =RT.SHIPMENT_HEADER_ID
AND RSL.SHIPMENT_LINE_ID =RT.SHIPMENT_LINE_ID
AND RT.TRANSACTION_TYPE ='DELIVER'
AND RSL.OE_ORDER_HEADER_ID =OOLA.HEADER_ID
AND RSL.OE_ORDER_LINE_ID =OOLA.LINE_ID
AND OOHA.HEADER_ID =OOLA.HEADER_ID
AND OOHA.ORDER_TYPE_ID =OTTT.TRANSACTION_TYPE_ID
AND FL.INSTALLED_FLAG ='B'
AND OTTT.LANGUAGE = LANGUAGE_CODE
SELECT RSL.SHIPMENT_HEADER_ID
,
RSL.SHIPMENT_LINE_ID
,
RSL.ITEM_ID
,
MSIBK.CONCATENATED_SEGMENTS ITEM
,
MP.ORGANIZATION_CODE
,
MP.ORGANIZATION_ID
,
RSL.ITEM_DESCRIPTION
,
DECODE((SELECT UPPER(MCR.CROSS_REFERENCE)
FROM MTL_CROSS_REFERENCES_VL MCR
WHERE MCR.INVENTORY_ITEM_ID = MSIBK.INVENTORY_ITEM_ID+0
AND MCR.ORGANIZATION_ID = MSIBK.ORGANIZATION_ID +0
AND TO_CHAR(MCR.LAST_UPDATE_DATE
, 'DD/MM/YYYY-HH24.MM.SS')=
(SELECT (MAX(TO_CHAR(MCR1.LAST_UPDATE_DATE
, 'DD/MM/YYYY-HH24.MM.SS')))
FROM MTL_CROSS_REFERENCES_VL MCR1
WHERE MCR1.INVENTORY_ITEM_ID = MSIBK.INVENTORY_ITEM_ID+0
AND MCR1.ORGANIZATION_ID = MSIBK.ORGANIZATION_ID +0
AND MCR1.CROSS_REFERENCE_TYPE ='OPSM INTEGRATED'
))
, 'NO'
, 0
, 1) OPSM_INTEGRATED_FLAG
,
(SELECT MCR.ATTRIBUTE1
FROM MTL_CROSS_REFERENCES_VL MCR
WHERE MCR.INVENTORY_ITEM_ID = MSIBK.INVENTORY_ITEM_ID+0
AND MCR.ORGANIZATION_ID =(SELECT MASTER_ORGANIZATION_ID
FROM MTL_PARAMETERS
WHERE ORGANIZATION_ID=MSIBK.ORGANIZATION_ID+0)
AND MCR.CROSS_REFERENCE_TYPE ='OPSM INTEGRATED'
AND MCR.CROSS_REFERENCE = 'YES')SERIAL_TYPE
,
RSL.QUANTITY_SHIPPED
,
RSL.QUANTITY_RECEIVED
,
RSL.UNIT_OF_MEASURE
,
RSL.SHIPMENT_LINE_STATUS_CODE
,
RSL.BAR_CODE_LABEL
,
RSL.QC_GRADE
,
RSL.ASN_LINE_FLAG
,
RSL.DESTINATION_TYPE_CODE
,
MSIBK.LOT_CONTROL_CODE
,
MSIBK.SERIAL_NUMBER_CONTROL_CODE
,
RSL.OE_ORDER_HEADER_ID
,
OOHA.ORDER_NUMBER
,
OTTT.NAME ORDER_TYPE
,
OOLA.LINE_NUMBER
,
OOLA.SOLD_TO_ORG_ID SOLDBYORGID
,
OOHA.SOLD_TO_CONTACT_ID SOLDBYCONTACTID
,
OOLA.SHIP_TO_ORG_ID SHIPFROMORGID
,
OOLA.SHIP_TO_CONTACT_ID SHIPFROMCONTACTID
,
OOLA.INVOICE_TO_ORG_ID BILLEDBYORGID
,
OOLA.INVOICE_TO_CONTACT_ID BILLEDBYCONTACTID
,
RT.PARENT_TRANSACTION_ID
,
RT.TRANSACTION_ID
,
RT.TRANSACTION_TYPE
,
RT.TRANSACTION_DATE
,
RT.QUANTITY TRANSACTION_QTY
,
RT.UOM_CODE TRANSACTION_UOM
,
RT.PRIMARY_QUANTITY
,
MSIBK.PRIMARY_UOM_CODE
,
RT.SECONDARY_QUANTITY
,
RT.SECONDARY_UOM_CODE
,
'RMA RECEIPT'
,
RT.DESTINATION_TYPE_CODE TRANS_DESTINATION_TYPE_CODE
,
RT.GROUP_ID
FROM RCV_SHIPMENT_LINES RSL
,
RCV_TRANSACTIONS RT
,
MTL_SYSTEM_ITEMS_B_KFV MSIBK
,
MTL_PARAMETERS MP
,
OE_ORDER_HEADERS_ALL OOHA
,
OE_ORDER_LINES_ALL OOLA
,
OE_TRANSACTION_TYPES_TL OTTT
,
FND_LANGUAGES FL
WHERE MSIBK.INVENTORY_ITEM_ID=RSL.ITEM_ID
AND MSIBK.ORGANIZATION_ID =RSL.TO_ORGANIZATION_ID
AND MSIBK.ORGANIZATION_ID =MP.ORGANIZATION_ID
AND RSL.SHIPMENT_HEADER_ID =RT.SHIPMENT_HEADER_ID
AND RSL.SHIPMENT_LINE_ID =RT.SHIPMENT_LINE_ID
AND RT.TRANSACTION_TYPE ='DELIVER'
AND RSL.OE_ORDER_HEADER_ID =OOLA.HEADER_ID
AND RSL.OE_ORDER_LINE_ID =OOLA.LINE_ID
AND OOHA.HEADER_ID =OOLA.HEADER_ID
AND OOHA.ORDER_TYPE_ID =OTTT.TRANSACTION_TYPE_ID
AND FL.INSTALLED_FLAG ='B'
AND OTTT.LANGUAGE = LANGUAGE_CODE
|
|
|
|