VIEW: APPS.MSC_WIP_JOB_SCHEDULE_V

Source

View Text - Preformatted

SELECT mjs.source_line_id, mjs.ORGANIZATION_ID, mtp.PARTNER_NAME, substr(mtp.ORGANIZATION_CODE, instr(mtp.ORGANIZATION_CODE,':')+1) ORGANIZATION_CODE, mjs.STATUS_TYPE, lookup1.MEANING STATUS, mjs.LAST_UNIT_COMPLETION_DATE, mjs.LINE_ID, decode(mjs.LINE_ID, NULL, NULL, nvl(MSC_ST_UTIL.RET_CODE ('LINE_ID',mjs.LINE_ID), mjs.LINE_ID)) LINE_CODE, mjs.PRIMARY_ITEM_ID, decode(mjs.PRIMARY_ITEM_ID, NULL, NULL, nvl(MSC_ST_UTIL.RET_CODE ('SR_INVENTORY_ITEM_ID', mjs.PRIMARY_ITEM_ID), mjs.PRIMARY_ITEM_ID)) PRIMARY_ITEM_NAME, mjs.CLASS_CODE, nvl(mjs.JOB_NAME,msc_st_util.ret_code('WIP_ENTITY_ID',mjs.wip_entity_id)) JOB_NAME, mjs.ALTERNATE_ROUTING_DESIGNATOR, mjs.ALTERNATE_BOM_DESIGNATOR, mjs.START_QUANTITY, mjs.BUILD_SEQUENCE, mjs.PROJECT_ID, decode(mjs.PROJECT_ID, NULL, NULL, nvl(MSC_ST_UTIL.RET_CODE ('PROJECT_ID', mjs.PROJECT_ID),mjs.PROJECT_ID)) PROJECT_NUMBER, mjs.TASK_ID, decode(mjs.TASK_ID, NULL, NULL, nvl(MSC_ST_UTIL.RET_CODE ('TASK_ID', mjs.TASK_ID), mjs.TASK_ID)) TASK_NUMBER, mjs.NET_QUANTITY, mjs.UOM_CODE, mjs.SR_INSTANCE_ID, mai.INSTANCE_CODE, mjs.BILL_RTG_EXPLOSION_FLAG, mjs.FIRST_UNIT_START_DATE, mjs.BOM_REFERENCE_ID, decode(mjs.BOM_REFERENCE_ID, NULL, NULL, nvl(MSC_ST_UTIL.RET_CODE ('BILL_SEQUENCE_ID', mjs.BOM_REFERENCE_ID), mjs.BOM_REFERENCE_ID)) BOM_NAME, mjs.ROUTING_REFERENCE_ID, decode(mjs.ROUTING_REFERENCE_ID, NULL, NULL, nvl(MSC_ST_UTIL.RET_CODE ('ROUTING_SEQUENCE_ID', mjs.ROUTING_REFERENCE_ID), mjs.ROUTING_REFERENCE_ID)) ROUTING_NAME, decode(mjs.CFM_ROUTING_FLAG,3,'Yes','No') COPRODUCTS_SUPPLY FROM MSC_WIP_JOB_SCHEDULE_INTERFACE mjs, MSC_TRADING_PARTNERS mtp, MFG_LOOKUPS lookup1, MSC_APPS_INSTANCES mai WHERE mjs.organization_id = mtp.sr_tp_id and mtp.partner_type = 3 and mtp.sr_instance_id = mjs.sr_instance_id and mjs.sr_instance_id = mai.instance_id and lookup1.lookup_type = 'WIP_JOB_STATUS' and mjs.STATUS_TYPE = lookup1.lookup_code

View Text - HTML Formatted

SELECT MJS.SOURCE_LINE_ID
, MJS.ORGANIZATION_ID
, MTP.PARTNER_NAME
, SUBSTR(MTP.ORGANIZATION_CODE
, INSTR(MTP.ORGANIZATION_CODE
, ':')+1) ORGANIZATION_CODE
, MJS.STATUS_TYPE
, LOOKUP1.MEANING STATUS
, MJS.LAST_UNIT_COMPLETION_DATE
, MJS.LINE_ID
, DECODE(MJS.LINE_ID
, NULL
, NULL
, NVL(MSC_ST_UTIL.RET_CODE ('LINE_ID'
, MJS.LINE_ID)
, MJS.LINE_ID)) LINE_CODE
, MJS.PRIMARY_ITEM_ID
, DECODE(MJS.PRIMARY_ITEM_ID
, NULL
, NULL
, NVL(MSC_ST_UTIL.RET_CODE ('SR_INVENTORY_ITEM_ID'
, MJS.PRIMARY_ITEM_ID)
, MJS.PRIMARY_ITEM_ID)) PRIMARY_ITEM_NAME
, MJS.CLASS_CODE
, NVL(MJS.JOB_NAME
, MSC_ST_UTIL.RET_CODE('WIP_ENTITY_ID'
, MJS.WIP_ENTITY_ID)) JOB_NAME
, MJS.ALTERNATE_ROUTING_DESIGNATOR
, MJS.ALTERNATE_BOM_DESIGNATOR
, MJS.START_QUANTITY
, MJS.BUILD_SEQUENCE
, MJS.PROJECT_ID
, DECODE(MJS.PROJECT_ID
, NULL
, NULL
, NVL(MSC_ST_UTIL.RET_CODE ('PROJECT_ID'
, MJS.PROJECT_ID)
, MJS.PROJECT_ID)) PROJECT_NUMBER
, MJS.TASK_ID
, DECODE(MJS.TASK_ID
, NULL
, NULL
, NVL(MSC_ST_UTIL.RET_CODE ('TASK_ID'
, MJS.TASK_ID)
, MJS.TASK_ID)) TASK_NUMBER
, MJS.NET_QUANTITY
, MJS.UOM_CODE
, MJS.SR_INSTANCE_ID
, MAI.INSTANCE_CODE
, MJS.BILL_RTG_EXPLOSION_FLAG
, MJS.FIRST_UNIT_START_DATE
, MJS.BOM_REFERENCE_ID
, DECODE(MJS.BOM_REFERENCE_ID
, NULL
, NULL
, NVL(MSC_ST_UTIL.RET_CODE ('BILL_SEQUENCE_ID'
, MJS.BOM_REFERENCE_ID)
, MJS.BOM_REFERENCE_ID)) BOM_NAME
, MJS.ROUTING_REFERENCE_ID
, DECODE(MJS.ROUTING_REFERENCE_ID
, NULL
, NULL
, NVL(MSC_ST_UTIL.RET_CODE ('ROUTING_SEQUENCE_ID'
, MJS.ROUTING_REFERENCE_ID)
, MJS.ROUTING_REFERENCE_ID)) ROUTING_NAME
, DECODE(MJS.CFM_ROUTING_FLAG
, 3
, 'YES'
, 'NO') COPRODUCTS_SUPPLY
FROM MSC_WIP_JOB_SCHEDULE_INTERFACE MJS
, MSC_TRADING_PARTNERS MTP
, MFG_LOOKUPS LOOKUP1
, MSC_APPS_INSTANCES MAI
WHERE MJS.ORGANIZATION_ID = MTP.SR_TP_ID
AND MTP.PARTNER_TYPE = 3
AND MTP.SR_INSTANCE_ID = MJS.SR_INSTANCE_ID
AND MJS.SR_INSTANCE_ID = MAI.INSTANCE_ID
AND LOOKUP1.LOOKUP_TYPE = 'WIP_JOB_STATUS'
AND MJS.STATUS_TYPE = LOOKUP1.LOOKUP_CODE