[Home] [Help]
| Product: | MRP - Master Scheduling/MRP |
|---|---|
| Description: | |
| Implementation/DBA Data: | Not implemented in this database |
SELECT ITEMS.INVENTORY_ITEM_ID
, ITEMS.ORGANIZATION_ID
, B.BILL_SEQUENCE_ID
, B.ALTERNATE_BOM_DESIGNATOR
, R.ALTERNATE_ROUTING_DESIGNATOR
, R.ROUTING_SEQUENCE_ID
, TO_NUMBER(NULL) LINE_ID
, TO_NUMBER(NULL) PRIMARY_LINE_FLAG
, TO_NUMBER(NULL) PRODUCTION_LINE_RATE
, SYSDATE-3650 EFFECTIVITY_DATE
, 0 RN5
, NVL(R.RN
, 0) RN4
, NVL(B.RN
, 0) RN3
, ITEMS.RN RN2
, MP.RN RN1
FROM MRP_AP_BOMS_SN B
, MRP_AP_OPR_RTNS_SN R
, MRP_AP_MRP_PARAS_SN MP
, MRP_AP_SYS_ITEMS_SN ITEMS
WHERE ITEMS.ORGANIZATION_ID= MP.ORGANIZATION_ID
AND ITEMS.INVENTORY_ITEM_ID = B.ASSEMBLY_ITEM_ID(+)
AND ITEMS.ORGANIZATION_ID = B.ORGANIZATION_ID(+)
AND ITEMS.INVENTORY_ITEM_ID = R.ASSEMBLY_ITEM_ID (+)
AND ITEMS.ORGANIZATION_ID = R.ORGANIZATION_ID (+)
AND ( (R.ROUTING_SEQUENCE_ID IS NULL
AND B.BILL_SEQUENCE_ID IS NOT NULL) OR (R.ROUTING_SEQUENCE_ID IS NOT NULL
AND B.BILL_SEQUENCE_ID IS NULL)) UNION ALL SELECT X.INVENTORY_ITEM_ID
, X.ORGANIZATION_ID
, X.BILL_SEQUENCE_ID
, X.ALTERNATE_BOM_DESIGNATOR
, R2.ALTERNATE_ROUTING_DESIGNATOR
, R2.ROUTING_SEQUENCE_ID
, TO_NUMBER(NULL) LINE_ID
, TO_NUMBER(NULL) PRIMARY_LINE_FLAG
, TO_NUMBER(NULL) PRODUCTION_LINE_RATE
, SYSDATE-3650 EFFECTIVITY_DATE
, R2.RN RN5
, X.RN4 RN4
, X.RN3 RN3
, X.RN2 RN2
, X.RN1 RN1
FROM MRP_AP_OPR_RTNS_SN R2
, ( SELECT ITEMS.INVENTORY_ITEM_ID
, ITEMS.ORGANIZATION_ID
, B.BILL_SEQUENCE_ID
, B.ALTERNATE_BOM_DESIGNATOR
, R1.ROUTING_SEQUENCE_ID
, R1.ALTERNATE_ROUTING_DESIGNATOR
, NVL(R1.RN
, 0) RN4
, B.RN RN3
, ITEMS.RN RN2
, MP.RN RN1
FROM MRP_AP_BOMS_SN B
, MRP_AP_OPR_RTNS_SN R1
, MRP_AP_MRP_PARAS_SN MP
, MRP_AP_SYS_ITEMS_SN ITEMS
WHERE ITEMS.ORGANIZATION_ID= MP.ORGANIZATION_ID
AND ITEMS.INVENTORY_ITEM_ID = B.ASSEMBLY_ITEM_ID
AND ITEMS.ORGANIZATION_ID = B.ORGANIZATION_ID
AND B.ASSEMBLY_ITEM_ID = R1.ASSEMBLY_ITEM_ID(+)
AND B.ORGANIZATION_ID = R1.ORGANIZATION_ID(+)
AND B.ALTERNATE_BOM_DESIGNATOR= R1.ALTERNATE_ROUTING_DESIGNATOR(+)) X
WHERE X.INVENTORY_ITEM_ID = R2.ASSEMBLY_ITEM_ID
AND X.ORGANIZATION_ID= R2.ORGANIZATION_ID
AND ( X.ALTERNATE_BOM_DESIGNATOR IS NULL OR NVL(X.ALTERNATE_ROUTING_DESIGNATOR
, -1)= NVL(R2.ALTERNATE_ROUTING_DESIGNATOR
, -1)) UNION ALL SELECT ITEMS.INVENTORY_ITEM_ID
, ITEMS.ORGANIZATION_ID
, TO_NUMBER(NULL) BILL_SEQUENCE_ID
, TO_CHAR(NULL) ALTERNATE_BOM_DESIGNATOR
, TO_CHAR(NULL) ALTERNATE_ROUTING_DESIGNATOR
, TO_NUMBER(NULL) ROUTING_SEQUENCE_ID
, MARI.LINE_ID
, MARI.PRIMARY_LINE_FLAG
, MARI.PRODUCTION_LINE_RATE
, SYSDATE-3650 EFFECTIVITY_DATE
, 0 RN5
, 0 RN4
, MARI.RN RN3
, ITEMS.RN RN2
, MP.RN RN1
FROM MRP_AP_REPT_ITEMS_SN MARI
, MRP_AP_MRP_PARAS_SN MP
, MRP_AP_SYS_ITEMS_SN ITEMS
WHERE ITEMS.ORGANIZATION_ID= MP.ORGANIZATION_ID
AND ITEMS.INVENTORY_ITEM_ID = MARI.PRIMARY_ITEM_ID
AND ITEMS.ORGANIZATION_ID = MARI.ORGANIZATION_ID
AND ITEMS.REPETITIVE_TYPE= 1