VIEW: APPS.CSI_WIP_COMPONENTS_V

Source

View Text - Preformatted

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)

View Text - HTML Formatted

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)