VIEW: APPS.MSC_FLP_SUPPLY_DEMAND_ATP_V

Source

View Text - Preformatted

SELECT MFP2.ROWID , MFP2.plan_id , MFP2.ORGANIZATION_ID , MFP2.PEGGING_ID , MFP2.PREV_PEGGING_ID , MFP2.DEMAND_ID , MFP2.TRANSACTION_ID , MFP2.END_ITEM_USAGE , nvl( ROUND(MGR2.USING_REQUIREMENT_QUANTITY,6), ROUND(MFP2.DEMAND_QUANTITY,6)) ,nvl( MGR2.USING_ASSEMBLY_DEMAND_DATE, mfp2.demand_date) , ROUND(MR2.NEW_ORDER_QUANTITY,6) , MFP2.INVENTORY_ITEM_ID , MIF2.ITEM_name||'/'|| msc_get_name.org_code(mfp2.organization_id,mfp2.sr_instance_id) , SUBSTR(msc_get_name.item_desc(mfp2.inventory_item_id, mfp2.organization_id, mfp2.plan_id,mfp2.sr_instance_id),1,20) , ROUND(MFP2.ALLOCATED_QUANTITY,6) , decode(mfp2.demand_id, -1,msc_get_name.lookup_meaning('MRP_FLP_SUPPLY_DEMAND_TYPE',mfp2.demand_id), -2,msc_get_name.lookup_meaning('MRP_FLP_SUPPLY_DEMAND_TYPE',mfp2.demand_id), -3,msc_get_name.lookup_meaning('MRP_FLP_SUPPLY_DEMAND_TYPE',mfp2.demand_id), msc_get_name.lookup_meaning(DECODE(mgr2.origination_type, 1, 'MRP_PLANNED_ORDER_DEMAND', 3, 'MRP_PLANNED_ORDER_DEMAND', 25, 'MRP_PLANNED_ORDER_DEMAND', 'MRP_DEMAND_ORIGINATION'),mgr2.origination_type)) , Msc_GET_name.PROJECT(MGR2.PROJECT_ID,mgr2.organization_id, mgr2.plan_id, mgr2.sr_instance_id) , Msc_GET_name.TASK(MGR2.TASK_ID, mgr2.project_id,mgr2.organization_id, mgr2.plan_id, mgr2.sr_instance_id) , DECODE(MFP2.END_ITEM_USAGE, NULL, 0, ROUND(MFP2.ALLOCATED_QUANTITY/MFP2.END_ITEM_USAGE,6)) , DECODE(MFP3.DEMAND_ID, -1,ROUND(MFP3.ALLOCATED_QUANTITY,6), ROUND(MGR3.USING_REQUIREMENT_QUANTITY,6)) , DECODE(MFP3.DEMAND_ID, -1, msc_get_name.supply_date(mfp3.pegging_id, mfp3.plan_id), mgr3.using_assembly_demand_date) , MIF3.ITEM_name||'/'|| msc_get_name.org_code(mfp3.organization_id, mfp3.sr_instance_id) , SUBSTR(msc_get_name.item_desc(mfp3.inventory_item_id, mfp3.organization_id, mfp3.plan_id,mfp3.sr_instance_id),1,20) , decode(mfp3.demand_id, -1,msc_get_name.lookup_meaning('MRP_FLP_SUPPLY_DEMAND_TYPE',mfp3.demand_id), -2,msc_get_name.lookup_meaning('MRP_FLP_SUPPLY_DEMAND_TYPE',mfp3.demand_id), -3,msc_get_name.lookup_meaning('MRP_FLP_SUPPLY_DEMAND_TYPE',mfp3.demand_id), msc_get_name.lookup_meaning('MRP_DEMAND_ORIGINATION',mgr3.origination_type)) , DECODE(MGR3.ORIGINATION_TYPE,6, MGR3.order_number, 8, mgr3.order_number, 30, mgr3.order_number, 24,msc_get_name.designator(mgr3.schedule_designator_id), msc_get_name.supply_order(mgr3.origination_type, mgr3.demand_id, mgr3.organization_id, mgr3.plan_id, mgr3.sr_instance_id)) , Msc_GET_name.PROJECT(MFP3.PROJECT_ID,mfp3.organization_id, mfp3.plan_id, mfp3.sr_instance_id) , Msc_GET_name.TASK(MFP3.TASK_ID, mfp3.project_id,mfp3.organization_id, mfp3.plan_id, mfp3.sr_instance_id) , ROUND(MR1.NEW_ORDER_QUANTITY,6) , MR1.NEW_SCHEDULE_DATE , MIF1.ITEM_name||'/'|| msc_get_name.org_code(mfp1.organization_id,mfp1.sr_instance_id) , SUBSTR(msc_get_name.item_desc(mfp1.inventory_item_id, mfp1.organization_id, mfp1.plan_id,mfp1.sr_instance_id),1,20) , MFP1.INVENTORY_ITEM_ID , MFP1.DEMAND_ID , ROUND(MFP1.ALLOCATED_QUANTITY,6) , MR1.NEW_PROCESSING_DAYS , msc_get_name.lookup_meaning('MRP_ORDER_TYPE',mr1.order_type) , MR1.ORDER_TYPE , DECODE(MR1.ORDER_TYPE, 3, mr1.WIP_ENTITY_NAME, 7, mr1.WIP_ENTITY_NAME, 14, mr1.WIP_ENTITY_NAME, 15, mr1.WIP_ENTITY_NAME, 27, mr1.WIP_ENTITY_NAME, 28, mr1.WIP_ENTITY_NAME, 1, mr1.order_NUMBER, 2, mr1.order_NUMBER, 8, mr1.order_NUMBER, 11, mr1.order_NUMBER, 12, mr1.order_NUMBER, '') , mgr3.DEMAND_CLASS , MGR2.UNIT_NUMBER , MGR3.UNIT_NUMBER , MR1.UNIT_NUMBER , Msc_GET_name.PROJECT(MR1.project_id,mr1.organization_id, mr1.plan_id, mr1.sr_instance_id) , Msc_GET_name.TASK(MR1.task_id,MR1.project_id,mr1.organization_id, mr1.plan_id, mr1.sr_instance_id) , Msc_GET_name.PROJECT(mgr2.lending_project_id,mgr2.organization_id, mgr2.plan_id, mgr2.sr_instance_id) , Msc_GET_name.TASK(mgr2.lending_task_id, mgr2.lending_project_id,mgr2.organization_id, mgr2.plan_id,mgr2.sr_instance_id) , Msc_GET_name.PROJECT(mgr3.lending_project_id,mgr3.organization_id, mgr3.plan_id, mgr3.sr_instance_id) , Msc_GET_name.TASK(mgr3.lending_task_id, mgr3.lending_project_id,mgr3.organization_id, mgr3.plan_id,mgr3.sr_instance_id) ,mfp2.sr_instance_id ,mgr3.dmd_satisfied_date ,mgr3.attribute1 ,msc_get_name.customer(mgr2.customer_id) ,mgr2.demand_priority ,msc_get_name.customer(mgr3.customer_id) ,mgr3.demand_priority ,msc_get_name.customer(mgr1.customer_id) ,mgr1.demand_priority ,mgr2.dmd_satisfied_date ,mgr1.dmd_satisfied_date ,mgr2.origination_type ,mgr3.attribute2 ,mgr3.attribute3 ,mgr3.attribute4 ,mgr3.attribute5 ,mgr3.attribute6 ,mgr3.attribute7 ,mgr3.attribute8 ,mgr3.attribute9 ,mgr3.attribute10 ,mgr3.attribute11 ,mgr3.attribute12 ,mgr3.attribute13 ,mgr3.attribute14 ,mgr3.attribute15 FROM Msc_items MIF3, Msc_items MIF2, Msc_items MIF1, Msc_supplies MR2, Msc_supplies MR1, msc_demands mgr1, Msc_demands MGR3, Msc_demands MGR2, Msc_FULL_PEGGING MFP1, Msc_FULL_PEGGING MFP3, Msc_FULL_PEGGING MFP2 WHERE MR1.TRANSACTION_ID(+) = MFP1.TRANSACTION_ID and mr1.plan_id(+) = mfp1.plan_id AND MIF1.INVENTORY_ITEM_ID(+) = MFP1.INVENTORY_ITEM_ID AND MFP1.PEGGING_ID(+) = MFP2.PREV_PEGGING_ID and mfp1.demand_id = mgr1.demand_id(+) and mfp1.plan_id = mgr1.plan_id(+) and mfp1.plan_id(+) = mfp2.plan_id AND MGR3.DEMAND_ID(+) = MFP3.DEMAND_ID and mgr3.plan_id(+) = mfp3.plan_id AND MIF3.INVENTORY_ITEM_ID = MFP3.INVENTORY_ITEM_ID AND MFP3.PEGGING_ID = MFP2.END_PEGGING_ID and mfp3.plan_id = mfp2.plan_id AND MIF2.INVENTORY_ITEM_ID = MFP2.INVENTORY_ITEM_ID AND MGR2.DEMAND_ID(+) = MFP2.DEMAND_ID and mgr2.plan_id(+) = mfp2.plan_id AND MR2.TRANSACTION_ID = MFP2.TRANSACTION_ID and mr2.plan_id = mfp2.plan_id

View Text - HTML Formatted

SELECT MFP2.ROWID
, MFP2.PLAN_ID
, MFP2.ORGANIZATION_ID
, MFP2.PEGGING_ID
, MFP2.PREV_PEGGING_ID
, MFP2.DEMAND_ID
, MFP2.TRANSACTION_ID
, MFP2.END_ITEM_USAGE
, NVL( ROUND(MGR2.USING_REQUIREMENT_QUANTITY
, 6)
, ROUND(MFP2.DEMAND_QUANTITY
, 6))
, NVL( MGR2.USING_ASSEMBLY_DEMAND_DATE
, MFP2.DEMAND_DATE)
, ROUND(MR2.NEW_ORDER_QUANTITY
, 6)
, MFP2.INVENTORY_ITEM_ID
, MIF2.ITEM_NAME||'/'|| MSC_GET_NAME.ORG_CODE(MFP2.ORGANIZATION_ID
, MFP2.SR_INSTANCE_ID)
, SUBSTR(MSC_GET_NAME.ITEM_DESC(MFP2.INVENTORY_ITEM_ID
, MFP2.ORGANIZATION_ID
, MFP2.PLAN_ID
, MFP2.SR_INSTANCE_ID)
, 1
, 20)
, ROUND(MFP2.ALLOCATED_QUANTITY
, 6)
, DECODE(MFP2.DEMAND_ID
, -1
, MSC_GET_NAME.LOOKUP_MEANING('MRP_FLP_SUPPLY_DEMAND_TYPE'
, MFP2.DEMAND_ID)
, -2
, MSC_GET_NAME.LOOKUP_MEANING('MRP_FLP_SUPPLY_DEMAND_TYPE'
, MFP2.DEMAND_ID)
, -3
, MSC_GET_NAME.LOOKUP_MEANING('MRP_FLP_SUPPLY_DEMAND_TYPE'
, MFP2.DEMAND_ID)
, MSC_GET_NAME.LOOKUP_MEANING(DECODE(MGR2.ORIGINATION_TYPE
, 1
, 'MRP_PLANNED_ORDER_DEMAND'
, 3
, 'MRP_PLANNED_ORDER_DEMAND'
, 25
, 'MRP_PLANNED_ORDER_DEMAND'
, 'MRP_DEMAND_ORIGINATION')
, MGR2.ORIGINATION_TYPE))
, MSC_GET_NAME.PROJECT(MGR2.PROJECT_ID
, MGR2.ORGANIZATION_ID
, MGR2.PLAN_ID
, MGR2.SR_INSTANCE_ID)
, MSC_GET_NAME.TASK(MGR2.TASK_ID
, MGR2.PROJECT_ID
, MGR2.ORGANIZATION_ID
, MGR2.PLAN_ID
, MGR2.SR_INSTANCE_ID)
, DECODE(MFP2.END_ITEM_USAGE
, NULL
, 0
, ROUND(MFP2.ALLOCATED_QUANTITY/MFP2.END_ITEM_USAGE
, 6))
, DECODE(MFP3.DEMAND_ID
, -1
, ROUND(MFP3.ALLOCATED_QUANTITY
, 6)
, ROUND(MGR3.USING_REQUIREMENT_QUANTITY
, 6))
, DECODE(MFP3.DEMAND_ID
, -1
, MSC_GET_NAME.SUPPLY_DATE(MFP3.PEGGING_ID
, MFP3.PLAN_ID)
, MGR3.USING_ASSEMBLY_DEMAND_DATE)
, MIF3.ITEM_NAME||'/'|| MSC_GET_NAME.ORG_CODE(MFP3.ORGANIZATION_ID
, MFP3.SR_INSTANCE_ID)
, SUBSTR(MSC_GET_NAME.ITEM_DESC(MFP3.INVENTORY_ITEM_ID
, MFP3.ORGANIZATION_ID
, MFP3.PLAN_ID
, MFP3.SR_INSTANCE_ID)
, 1
, 20)
, DECODE(MFP3.DEMAND_ID
, -1
, MSC_GET_NAME.LOOKUP_MEANING('MRP_FLP_SUPPLY_DEMAND_TYPE'
, MFP3.DEMAND_ID)
, -2
, MSC_GET_NAME.LOOKUP_MEANING('MRP_FLP_SUPPLY_DEMAND_TYPE'
, MFP3.DEMAND_ID)
, -3
, MSC_GET_NAME.LOOKUP_MEANING('MRP_FLP_SUPPLY_DEMAND_TYPE'
, MFP3.DEMAND_ID)
, MSC_GET_NAME.LOOKUP_MEANING('MRP_DEMAND_ORIGINATION'
, MGR3.ORIGINATION_TYPE))
, DECODE(MGR3.ORIGINATION_TYPE
, 6
, MGR3.ORDER_NUMBER
, 8
, MGR3.ORDER_NUMBER
, 30
, MGR3.ORDER_NUMBER
, 24
, MSC_GET_NAME.DESIGNATOR(MGR3.SCHEDULE_DESIGNATOR_ID)
, MSC_GET_NAME.SUPPLY_ORDER(MGR3.ORIGINATION_TYPE
, MGR3.DEMAND_ID
, MGR3.ORGANIZATION_ID
, MGR3.PLAN_ID
, MGR3.SR_INSTANCE_ID))
, MSC_GET_NAME.PROJECT(MFP3.PROJECT_ID
, MFP3.ORGANIZATION_ID
, MFP3.PLAN_ID
, MFP3.SR_INSTANCE_ID)
, MSC_GET_NAME.TASK(MFP3.TASK_ID
, MFP3.PROJECT_ID
, MFP3.ORGANIZATION_ID
, MFP3.PLAN_ID
, MFP3.SR_INSTANCE_ID)
, ROUND(MR1.NEW_ORDER_QUANTITY
, 6)
, MR1.NEW_SCHEDULE_DATE
, MIF1.ITEM_NAME||'/'|| MSC_GET_NAME.ORG_CODE(MFP1.ORGANIZATION_ID
, MFP1.SR_INSTANCE_ID)
, SUBSTR(MSC_GET_NAME.ITEM_DESC(MFP1.INVENTORY_ITEM_ID
, MFP1.ORGANIZATION_ID
, MFP1.PLAN_ID
, MFP1.SR_INSTANCE_ID)
, 1
, 20)
, MFP1.INVENTORY_ITEM_ID
, MFP1.DEMAND_ID
, ROUND(MFP1.ALLOCATED_QUANTITY
, 6)
, MR1.NEW_PROCESSING_DAYS
, MSC_GET_NAME.LOOKUP_MEANING('MRP_ORDER_TYPE'
, MR1.ORDER_TYPE)
, MR1.ORDER_TYPE
, DECODE(MR1.ORDER_TYPE
, 3
, MR1.WIP_ENTITY_NAME
, 7
, MR1.WIP_ENTITY_NAME
, 14
, MR1.WIP_ENTITY_NAME
, 15
, MR1.WIP_ENTITY_NAME
, 27
, MR1.WIP_ENTITY_NAME
, 28
, MR1.WIP_ENTITY_NAME
, 1
, MR1.ORDER_NUMBER
, 2
, MR1.ORDER_NUMBER
, 8
, MR1.ORDER_NUMBER
, 11
, MR1.ORDER_NUMBER
, 12
, MR1.ORDER_NUMBER
, '')
, MGR3.DEMAND_CLASS
, MGR2.UNIT_NUMBER
, MGR3.UNIT_NUMBER
, MR1.UNIT_NUMBER
, MSC_GET_NAME.PROJECT(MR1.PROJECT_ID
, MR1.ORGANIZATION_ID
, MR1.PLAN_ID
, MR1.SR_INSTANCE_ID)
, MSC_GET_NAME.TASK(MR1.TASK_ID
, MR1.PROJECT_ID
, MR1.ORGANIZATION_ID
, MR1.PLAN_ID
, MR1.SR_INSTANCE_ID)
, MSC_GET_NAME.PROJECT(MGR2.LENDING_PROJECT_ID
, MGR2.ORGANIZATION_ID
, MGR2.PLAN_ID
, MGR2.SR_INSTANCE_ID)
, MSC_GET_NAME.TASK(MGR2.LENDING_TASK_ID
, MGR2.LENDING_PROJECT_ID
, MGR2.ORGANIZATION_ID
, MGR2.PLAN_ID
, MGR2.SR_INSTANCE_ID)
, MSC_GET_NAME.PROJECT(MGR3.LENDING_PROJECT_ID
, MGR3.ORGANIZATION_ID
, MGR3.PLAN_ID
, MGR3.SR_INSTANCE_ID)
, MSC_GET_NAME.TASK(MGR3.LENDING_TASK_ID
, MGR3.LENDING_PROJECT_ID
, MGR3.ORGANIZATION_ID
, MGR3.PLAN_ID
, MGR3.SR_INSTANCE_ID)
, MFP2.SR_INSTANCE_ID
, MGR3.DMD_SATISFIED_DATE
, MGR3.ATTRIBUTE1
, MSC_GET_NAME.CUSTOMER(MGR2.CUSTOMER_ID)
, MGR2.DEMAND_PRIORITY
, MSC_GET_NAME.CUSTOMER(MGR3.CUSTOMER_ID)
, MGR3.DEMAND_PRIORITY
, MSC_GET_NAME.CUSTOMER(MGR1.CUSTOMER_ID)
, MGR1.DEMAND_PRIORITY
, MGR2.DMD_SATISFIED_DATE
, MGR1.DMD_SATISFIED_DATE
, MGR2.ORIGINATION_TYPE
, MGR3.ATTRIBUTE2
, MGR3.ATTRIBUTE3
, MGR3.ATTRIBUTE4
, MGR3.ATTRIBUTE5
, MGR3.ATTRIBUTE6
, MGR3.ATTRIBUTE7
, MGR3.ATTRIBUTE8
, MGR3.ATTRIBUTE9
, MGR3.ATTRIBUTE10
, MGR3.ATTRIBUTE11
, MGR3.ATTRIBUTE12
, MGR3.ATTRIBUTE13
, MGR3.ATTRIBUTE14
, MGR3.ATTRIBUTE15
FROM MSC_ITEMS MIF3
, MSC_ITEMS MIF2
, MSC_ITEMS MIF1
, MSC_SUPPLIES MR2
, MSC_SUPPLIES MR1
, MSC_DEMANDS MGR1
, MSC_DEMANDS MGR3
, MSC_DEMANDS MGR2
, MSC_FULL_PEGGING MFP1
, MSC_FULL_PEGGING MFP3
, MSC_FULL_PEGGING MFP2
WHERE MR1.TRANSACTION_ID(+) = MFP1.TRANSACTION_ID
AND MR1.PLAN_ID(+) = MFP1.PLAN_ID
AND MIF1.INVENTORY_ITEM_ID(+) = MFP1.INVENTORY_ITEM_ID
AND MFP1.PEGGING_ID(+) = MFP2.PREV_PEGGING_ID
AND MFP1.DEMAND_ID = MGR1.DEMAND_ID(+)
AND MFP1.PLAN_ID = MGR1.PLAN_ID(+)
AND MFP1.PLAN_ID(+) = MFP2.PLAN_ID
AND MGR3.DEMAND_ID(+) = MFP3.DEMAND_ID
AND MGR3.PLAN_ID(+) = MFP3.PLAN_ID
AND MIF3.INVENTORY_ITEM_ID = MFP3.INVENTORY_ITEM_ID
AND MFP3.PEGGING_ID = MFP2.END_PEGGING_ID
AND MFP3.PLAN_ID = MFP2.PLAN_ID
AND MIF2.INVENTORY_ITEM_ID = MFP2.INVENTORY_ITEM_ID
AND MGR2.DEMAND_ID(+) = MFP2.DEMAND_ID
AND MGR2.PLAN_ID(+) = MFP2.PLAN_ID
AND MR2.TRANSACTION_ID = MFP2.TRANSACTION_ID
AND MR2.PLAN_ID = MFP2.PLAN_ID