View: CSI_WIP_COMPONENTS_V

Product:	CSI - Install Base
Description:
Implementation/DBA Data:	APPS.CSI_WIP_COMPONENTS_V

View Text

SELECT CTL.INSTANCE_ID INSTANCE_ID
, CTL.QUANTITY QUANTITY
, CTL.UNIT_OF_MEASURE UNIT_OF_MEASURE
, CTL.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, CTL.INV_ORGANIZATION_ID INV_ORGANIZATION_ID
, CTL.SERIAL_NUMBER SERIAL_NUMBER
, CTL.LOT_NUMBER LOT_NUMBER
, CTL.INSTANCE_STATUS_ID INSTANCE_STATUS_ID
, CTL.LOCATION_TYPE_CODE LOCATION_TYPE_CODE
, CTL.LOCATION_ID LOCATION_ID
, CTT.SOURCE_TRANSACTION_ID WIP_JOB_ID
, CTL.CREATED_BY CREATED_BY
, CTL.CREATION_DATE CREATION_DATE
, CTL.LAST_UPDATED_BY LAST_UPDATED_BY
, CTL.LAST_UPDATE_DATE LAST_UPDATE_DATE
, CTL.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, CTL.MFG_SERIAL_NUMBER_FLAG MFG_SERIAL_NUMBER_FLAG
, CTL.EXTERNAL_REFERENCE EXTERNAL_REFERENCE
, CTL.SOURCE_TRANSACTION_FLAG SOURCE_TRANSACTION_FLAG
, CTL.PROCESSING_STATUS PROCESSING_STATUS
, CTL.TRANSACTION_LINE_ID TRANSACTION_LINE_ID
, CTL.TXN_LINE_DETAIL_ID TXN_LINE_DETAIL_ID
, CTI.OBJECT_ID PARENT_TXN_LINE_DETAIL_ID
, CTI.TXN_RELATIONSHIP_ID TXN_RELATIONSHIP_ID
, 'N' QUANTITY_CHECK_FLAG
FROM CSI_T_TRANSACTION_LINES CTT
, CSI_T_TXN_LINE_DETAILS CTL
, CSI_T_II_RELATIONSHIPS CTI
WHERE CTT.TRANSACTION_LINE_ID = CTL.TRANSACTION_LINE_ID
AND CTT.SOURCE_TRANSACTION_TABLE = 'WIP_ENTITIES'
AND CTL.SOURCE_TRANSACTION_FLAG = 'N'
AND CTI.TRANSACTION_LINE_ID = CTL.TRANSACTION_LINE_ID
AND CTI.SUBJECT_ID = CTL.TXN_LINE_DETAIL_ID UNION SELECT CII.INSTANCE_ID INSTANCE_ID
, CII.QUANTITY QUANTITY
, CII.UNIT_OF_MEASURE UNIT_OF_MEASURE
, CII.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, CII.LAST_VLD_ORGANIZATION_ID INV_ORGANIZATION_ID
, CII.SERIAL_NUMBER SERIAL_NUMBER
, CII.LOT_NUMBER LOT_NUMBER
, CII.INSTANCE_STATUS_ID INSTANCE_STATUS_ID
, CII.LOCATION_TYPE_CODE LOCATION_TYPE_CODE
, CII.LOCATION_ID LOCATION_ID
, CII.WIP_JOB_ID WIP_JOB_ID
, CII.CREATED_BY CREATED_BY
, CII.CREATION_DATE CREATION_DATE
, CII.LAST_UPDATED_BY LAST_UPDATED_BY
, CII.LAST_UPDATE_DATE LAST_UPDATE_DATE
, CII.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, CII.MFG_SERIAL_NUMBER_FLAG MFG_SERIAL_NUMBER_FLAG
, CII.EXTERNAL_REFERENCE EXTERNAL_REFERENCE
, 'N' SOURCE_TRANSACTION_FLAG
, TO_CHAR(NULL) PROCESSING_STATUS
, TO_NUMBER(NULL) TRANSACTION_LINE_ID
, TO_NUMBER(NULL) TXN_LINE_DETAIL_ID
, -999 PARENT_TXN_LINE_DETAIL_ID
, TO_NUMBER(NULL) TXN_RELATIONSHIP_ID
, 'N' QUANTITY_CHECK_FLAG
FROM CSI_ITEM_INSTANCES CII
, MTL_SYSTEM_ITEMS MSI
WHERE CII.LOCATION_TYPE_CODE = 'WIP'
AND MSI.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID
AND MSI.SERIAL_NUMBER_CONTROL_CODE IN (2
, 5)
AND NOT EXISTS (SELECT 'X'
FROM CSI_T_TXN_LINE_DETAILS CTT
, CSI_T_TRANSACTION_LINES CTL
WHERE CTL.SOURCE_TRANSACTION_TABLE = 'WIP_ENTITIES'
AND CTT.TRANSACTION_LINE_ID = CTL.TRANSACTION_LINE_ID AND CTL.SOURCE_TRANSACTION_ID = CII.WIP_JOB_ID
AND CTT.SOURCE_TRANSACTION_FLAG = 'N'
AND CTT.INSTANCE_ID = CII.INSTANCE_ID) UNION SELECT CII.INSTANCE_ID INSTANCE_ID
, CII.QUANTITY QUANTITY
, CII.UNIT_OF_MEASURE UNIT_OF_MEASURE
, CII.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, CII.LAST_VLD_ORGANIZATION_ID INV_ORGANIZATION_ID
, CII.SERIAL_NUMBER SERIAL_NUMBER
, CII.LOT_NUMBER LOT_NUMBER
, CII.INSTANCE_STATUS_ID INSTANCE_STATUS_ID
, CII.LOCATION_TYPE_CODE LOCATION_TYPE_CODE
, CII.LOCATION_ID LOCATION_ID
, CII.WIP_JOB_ID WIP_JOB_ID
, CII.CREATED_BY CREATED_BY
, CII.CREATION_DATE CREATION_DATE
, CII.LAST_UPDATED_BY LAST_UPDATED_BY
, CII.LAST_UPDATE_DATE LAST_UPDATE_DATE
, CII.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, CII.MFG_SERIAL_NUMBER_FLAG MFG_SERIAL_NUMBER_FLAG
, CII.EXTERNAL_REFERENCE EXTERNAL_REFERENCE
, 'N' SOURCE_TRANSACTION_FLAG
, TO_CHAR(NULL) PROCESSING_STATUS
, TO_NUMBER(NULL) TRANSACTION_LINE_ID
, TO_NUMBER(NULL) TXN_LINE_DETAIL_ID
, -999 PARENT_TXN_LINE_DETAIL_ID
, TO_NUMBER(NULL) TXN_RELATIONSHIP_ID
, 'Y' QUANTITY_CHECK_FLAG
FROM CSI_ITEM_INSTANCES CII
, MTL_SYSTEM_ITEMS MSI
WHERE CII.LOCATION_TYPE_CODE = 'WIP'
AND MSI.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID
AND MSI.SERIAL_NUMBER_CONTROL_CODE IN (1
, 6)

Columns