VIEW: APPS.CSI_WIP_ASSEMBLIES_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 last_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 FROM csi_t_transaction_lines ctt ,csi_t_txn_line_details ctl WHERE ctt.transaction_line_id = ctl.transaction_line_id AND ctt.source_transaction_table = 'WIP_ENTITIES' AND ctl.source_transaction_flag = 'Y' UNION SELECT instance_id instance_id ,quantity quantity ,unit_of_measure unit_of_measure ,inventory_item_id inventory_item_id ,last_vld_organization_id inv_organization_id ,serial_number serial_number ,lot_number lot_number ,instance_status_id instance_status_id ,location_type_code location_type_code ,location_id location_id ,last_wip_job_id last_wip_job_id ,created_by created_by ,creation_date creation_date ,last_updated_by last_updated_by ,last_update_date last_update_date ,last_update_login last_update_login ,mfg_serial_number_flag mfg_serial_number_flag ,external_reference external_reference ,'Y' source_transaction_flag ,to_char(null) processing_status ,to_number(null) transaction_line_id ,to_number(null) txn_line_detail_id FROM csi_item_instances cii WHERE NOT EXISTS (SELECT 'x' FROM csi_t_txn_line_details ctt, csi_t_transaction_lines ctl WHERE ctt.transaction_line_id = ctl.transaction_line_id AND ctl.source_transaction_table = 'WIP_ENTITIES' AND ctl.source_transaction_id = cii.last_wip_job_id AND ctt.source_transaction_flag = 'Y' AND ctt.instance_id = cii.instance_id)

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 LAST_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
FROM CSI_T_TRANSACTION_LINES CTT
, CSI_T_TXN_LINE_DETAILS CTL
WHERE CTT.TRANSACTION_LINE_ID = CTL.TRANSACTION_LINE_ID
AND CTT.SOURCE_TRANSACTION_TABLE = 'WIP_ENTITIES'
AND CTL.SOURCE_TRANSACTION_FLAG = 'Y' UNION SELECT INSTANCE_ID INSTANCE_ID
, QUANTITY QUANTITY
, UNIT_OF_MEASURE UNIT_OF_MEASURE
, INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, LAST_VLD_ORGANIZATION_ID INV_ORGANIZATION_ID
, SERIAL_NUMBER SERIAL_NUMBER
, LOT_NUMBER LOT_NUMBER
, INSTANCE_STATUS_ID INSTANCE_STATUS_ID
, LOCATION_TYPE_CODE LOCATION_TYPE_CODE
, LOCATION_ID LOCATION_ID
, LAST_WIP_JOB_ID LAST_WIP_JOB_ID
, CREATED_BY CREATED_BY
, CREATION_DATE CREATION_DATE
, LAST_UPDATED_BY LAST_UPDATED_BY
, LAST_UPDATE_DATE LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, MFG_SERIAL_NUMBER_FLAG MFG_SERIAL_NUMBER_FLAG
, EXTERNAL_REFERENCE EXTERNAL_REFERENCE
, 'Y' SOURCE_TRANSACTION_FLAG
, TO_CHAR(NULL) PROCESSING_STATUS
, TO_NUMBER(NULL) TRANSACTION_LINE_ID
, TO_NUMBER(NULL) TXN_LINE_DETAIL_ID
FROM CSI_ITEM_INSTANCES CII
WHERE NOT EXISTS (SELECT 'X'
FROM CSI_T_TXN_LINE_DETAILS CTT
, CSI_T_TRANSACTION_LINES CTL
WHERE CTT.TRANSACTION_LINE_ID = CTL.TRANSACTION_LINE_ID
AND CTL.SOURCE_TRANSACTION_TABLE = 'WIP_ENTITIES'
AND CTL.SOURCE_TRANSACTION_ID = CII.LAST_WIP_JOB_ID
AND CTT.SOURCE_TRANSACTION_FLAG = 'Y'
AND CTT.INSTANCE_ID = CII.INSTANCE_ID)