VIEW: APPS.MSC_FLP_DEMAND_SUPPLY_V

Source

View Text - Preformatted

SELECT MFP2.ROWID , MFP2.plan_id , MFP2.ORGANIZATION_ID , MFP2.PEGGING_ID , NVL(MFP2.PREV_PEGGING_ID, -1) , MFP2.DEMAND_ID , MFP2.TRANSACTION_ID , MFP2.END_ITEM_USAGE , MFP2.INVENTORY_ITEM_ID , MIF2.ITEM_name||'/'|| msc_get_name.org_code(mfp2.organization_id,mfp2.sr_instance_id) , ROUND(MR2.NEW_ORDER_QUANTITY,6) , ROUND(MFP2.ALLOCATED_QUANTITY,6) , MR2.NEW_SCHEDULE_DATE , ROUND(MGR2.USING_REQUIREMENT_QUANTITY,6) , msc_get_name.demand_date(Mfp2.pegging_id,mfp2.plan_id) , msc_get_name.lookup_by_plan('MRP_ORDER_TYPE',decode(MR2.ORDER_TYPE,92,70,MR2.ORDER_TYPE), mp.plan_type,mr2.source_organization_id) , Msc_GET_name.PROJECT(MFP2.PROJECT_ID,mfp2.organization_id, mfp2.plan_id,mfp2.sr_instance_id) , Msc_GET_name.TASK(MFP2.TASK_ID, mfp2.project_id,mfp2.organization_id, mfp2.plan_id,mfp2.sr_instance_id) , decode(MR2.ORDER_TYPE,92,70,MR2.ORDER_TYPE) , msc_get_name.supply_order_number( mr2.order_type ,mr2.order_number ,mr2.plan_id ,mr2.sr_instance_id ,mr2.transaction_id ,mr2.disposition_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), msc_get_name.demand_date(Mfp3.pegging_id,mfp3.plan_id)) , MIF3.ITEM_name||'/'|| msc_get_name.org_code(mfp3.organization_id, mfp3.sr_instance_id) , SUBSTRB(msc_get_name.item_desc(mfp3.inventory_item_id, mfp3.organization_id, mfp3.plan_id,mfp3.sr_instance_id),1,80) , decode(mgr3.demand_id, -1,msc_get_name.lookup_meaning('MRP_FLP_SUPPLY_DEMAND_TYPE',mgr3.demand_id), -2,msc_get_name.lookup_meaning('MRP_FLP_SUPPLY_DEMAND_TYPE',mgr3.demand_id), -3,msc_get_name.lookup_meaning('MRP_FLP_SUPPLY_DEMAND_TYPE',mgr3.demand_id), msc_get_name.lookup_meaning('MSC_DEMAND_ORIGINATION',decode(mgr3.origination_type,92,50,mgr3.origination_type))) , DECODE(MGR3.ORIGINATION_TYPE,6, MGR3.order_number, 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) , mgr3.DEMAND_CLASS , MR2.UNIT_NUMBER , MGR3.UNIT_NUMBER , msc_get_name.item_desc(mfp2.inventory_item_id, mfp2.organization_id, mfp2.plan_id,mfp2.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 ,mr2.need_by_date ,nvl(msc_get_name.org_code(mr2.source_organization_id, mr2.source_sr_instance_id), msc_get_name.supplier(mr2.source_supplier_id)) ,msc_get_name.customer(mgr3.customer_id) ,mgr3.demand_priority ,mgr3.dmd_satisfied_date ,mgr3.attribute1 ,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 ,decode(mgr3.origination_type,92,50,mgr3.origination_type) ,mgr3.demand_id ,substrb(nvl(MIF2.Description , MIF2.ITEM_name),1,80)||'/'|| msc_get_name.org_code(mfp2.organization_id,mfp2.sr_instance_id), MR2.PRODUCT_CLASSIFICATION, MR2.MAINTENANCE_REQT, msc_get_name.lookup_meaning('SYS_YES_NO', decode(MR2.ORDER_TYPE , 70 , nvl(mr2.PRODUCES_TO_STOCK, 2) , mr2.produces_to_stock) ), Msc_get_name.visit_name(MR2.visit_id), msc_get_name.lookup_meaning('MSC_ACTIVITY_TYPE', MR2.ACTIVITY_TYPE) , msc_get_name.item_name(mr2.activity_item_id, null, null, null), MR2.CLASS_CODE, msc_get_name.lookup_meaning('MSC_PART_CONDITION',mr2.item_type_value) FROM Msc_system_items MIF3, Msc_system_items MIF2, Msc_demands MGR3, Msc_demands MGR2, Msc_supplies MR2, msc_plans mp, Msc_FULL_PEGGING MFP3, Msc_FULL_PEGGING MFP2 WHERE MGR3.DEMAND_ID(+) = MFP3.DEMAND_ID and mgr3.plan_id(+)= mfp3.plan_id AND MGR2.demand_id(+)=MFP2.DEMAND_ID AND MGR2.plan_id(+)=MFP2.plan_id AND MIF3.INVENTORY_ITEM_ID = MFP3.INVENTORY_ITEM_ID and MIF3.SR_INSTANCE_ID = MFP3.SR_INSTANCE_ID and MIF3.ORGANIZATION_ID = MFP3.ORGANIZATION_ID and MIF3.PLAN_ID = MFP3.PLAN_ID AND MFP3.PEGGING_ID = MFP2.END_PEGGING_ID and mfp3.plan_id = mfp2.plan_id AND decode(mfp2.supply_type, 4, mr2.disposition_id, MR2.TRANSACTION_ID) = MFP2.TRANSACTION_ID and mr2.plan_id = mfp2.plan_id AND MIF2.INVENTORY_ITEM_ID = MFP2.INVENTORY_ITEM_ID and MIF2.SR_INSTANCE_ID = MFP2.SR_INSTANCE_ID and MIF2.ORGANIZATION_ID = MFP2.ORGANIZATION_ID and MIF2.PLAN_ID = MFP2.PLAN_ID and mp.plan_id = mfp2.plan_id

View Text - HTML Formatted

SELECT MFP2.ROWID
, MFP2.PLAN_ID
, MFP2.ORGANIZATION_ID
, MFP2.PEGGING_ID
, NVL(MFP2.PREV_PEGGING_ID
, -1)
, MFP2.DEMAND_ID
, MFP2.TRANSACTION_ID
, MFP2.END_ITEM_USAGE
, MFP2.INVENTORY_ITEM_ID
, MIF2.ITEM_NAME||'/'|| MSC_GET_NAME.ORG_CODE(MFP2.ORGANIZATION_ID
, MFP2.SR_INSTANCE_ID)
, ROUND(MR2.NEW_ORDER_QUANTITY
, 6)
, ROUND(MFP2.ALLOCATED_QUANTITY
, 6)
, MR2.NEW_SCHEDULE_DATE
, ROUND(MGR2.USING_REQUIREMENT_QUANTITY
, 6)
, MSC_GET_NAME.DEMAND_DATE(MFP2.PEGGING_ID
, MFP2.PLAN_ID)
, MSC_GET_NAME.LOOKUP_BY_PLAN('MRP_ORDER_TYPE'
, DECODE(MR2.ORDER_TYPE
, 92
, 70
, MR2.ORDER_TYPE)
, MP.PLAN_TYPE
, MR2.SOURCE_ORGANIZATION_ID)
, MSC_GET_NAME.PROJECT(MFP2.PROJECT_ID
, MFP2.ORGANIZATION_ID
, MFP2.PLAN_ID
, MFP2.SR_INSTANCE_ID)
, MSC_GET_NAME.TASK(MFP2.TASK_ID
, MFP2.PROJECT_ID
, MFP2.ORGANIZATION_ID
, MFP2.PLAN_ID
, MFP2.SR_INSTANCE_ID)
, DECODE(MR2.ORDER_TYPE
, 92
, 70
, MR2.ORDER_TYPE)
, MSC_GET_NAME.SUPPLY_ORDER_NUMBER( MR2.ORDER_TYPE
, MR2.ORDER_NUMBER
, MR2.PLAN_ID
, MR2.SR_INSTANCE_ID
, MR2.TRANSACTION_ID
, MR2.DISPOSITION_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)
, MSC_GET_NAME.DEMAND_DATE(MFP3.PEGGING_ID
, MFP3.PLAN_ID))
, MIF3.ITEM_NAME||'/'|| MSC_GET_NAME.ORG_CODE(MFP3.ORGANIZATION_ID
, MFP3.SR_INSTANCE_ID)
, SUBSTRB(MSC_GET_NAME.ITEM_DESC(MFP3.INVENTORY_ITEM_ID
, MFP3.ORGANIZATION_ID
, MFP3.PLAN_ID
, MFP3.SR_INSTANCE_ID)
, 1
, 80)
, DECODE(MGR3.DEMAND_ID
, -1
, MSC_GET_NAME.LOOKUP_MEANING('MRP_FLP_SUPPLY_DEMAND_TYPE'
, MGR3.DEMAND_ID)
, -2
, MSC_GET_NAME.LOOKUP_MEANING('MRP_FLP_SUPPLY_DEMAND_TYPE'
, MGR3.DEMAND_ID)
, -3
, MSC_GET_NAME.LOOKUP_MEANING('MRP_FLP_SUPPLY_DEMAND_TYPE'
, MGR3.DEMAND_ID)
, MSC_GET_NAME.LOOKUP_MEANING('MSC_DEMAND_ORIGINATION'
, DECODE(MGR3.ORIGINATION_TYPE
, 92
, 50
, MGR3.ORIGINATION_TYPE)))
, DECODE(MGR3.ORIGINATION_TYPE
, 6
, MGR3.ORDER_NUMBER
, 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)
, MGR3.DEMAND_CLASS
, MR2.UNIT_NUMBER
, MGR3.UNIT_NUMBER
, MSC_GET_NAME.ITEM_DESC(MFP2.INVENTORY_ITEM_ID
, MFP2.ORGANIZATION_ID
, MFP2.PLAN_ID
, MFP2.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
, MR2.NEED_BY_DATE
, NVL(MSC_GET_NAME.ORG_CODE(MR2.SOURCE_ORGANIZATION_ID
, MR2.SOURCE_SR_INSTANCE_ID)
, MSC_GET_NAME.SUPPLIER(MR2.SOURCE_SUPPLIER_ID))
, MSC_GET_NAME.CUSTOMER(MGR3.CUSTOMER_ID)
, MGR3.DEMAND_PRIORITY
, MGR3.DMD_SATISFIED_DATE
, MGR3.ATTRIBUTE1
, 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
, DECODE(MGR3.ORIGINATION_TYPE
, 92
, 50
, MGR3.ORIGINATION_TYPE)
, MGR3.DEMAND_ID
, SUBSTRB(NVL(MIF2.DESCRIPTION
, MIF2.ITEM_NAME)
, 1
, 80)||'/'|| MSC_GET_NAME.ORG_CODE(MFP2.ORGANIZATION_ID
, MFP2.SR_INSTANCE_ID)
, MR2.PRODUCT_CLASSIFICATION
, MR2.MAINTENANCE_REQT
, MSC_GET_NAME.LOOKUP_MEANING('SYS_YES_NO'
, DECODE(MR2.ORDER_TYPE
, 70
, NVL(MR2.PRODUCES_TO_STOCK
, 2)
, MR2.PRODUCES_TO_STOCK) )
, MSC_GET_NAME.VISIT_NAME(MR2.VISIT_ID)
, MSC_GET_NAME.LOOKUP_MEANING('MSC_ACTIVITY_TYPE'
, MR2.ACTIVITY_TYPE)
, MSC_GET_NAME.ITEM_NAME(MR2.ACTIVITY_ITEM_ID
, NULL
, NULL
, NULL)
, MR2.CLASS_CODE
, MSC_GET_NAME.LOOKUP_MEANING('MSC_PART_CONDITION'
, MR2.ITEM_TYPE_VALUE)
FROM MSC_SYSTEM_ITEMS MIF3
, MSC_SYSTEM_ITEMS MIF2
, MSC_DEMANDS MGR3
, MSC_DEMANDS MGR2
, MSC_SUPPLIES MR2
, MSC_PLANS MP
, MSC_FULL_PEGGING MFP3
, MSC_FULL_PEGGING MFP2
WHERE MGR3.DEMAND_ID(+) = MFP3.DEMAND_ID
AND MGR3.PLAN_ID(+)= MFP3.PLAN_ID
AND MGR2.DEMAND_ID(+)=MFP2.DEMAND_ID
AND MGR2.PLAN_ID(+)=MFP2.PLAN_ID
AND MIF3.INVENTORY_ITEM_ID = MFP3.INVENTORY_ITEM_ID
AND MIF3.SR_INSTANCE_ID = MFP3.SR_INSTANCE_ID
AND MIF3.ORGANIZATION_ID = MFP3.ORGANIZATION_ID
AND MIF3.PLAN_ID = MFP3.PLAN_ID
AND MFP3.PEGGING_ID = MFP2.END_PEGGING_ID
AND MFP3.PLAN_ID = MFP2.PLAN_ID
AND DECODE(MFP2.SUPPLY_TYPE
, 4
, MR2.DISPOSITION_ID
, MR2.TRANSACTION_ID) = MFP2.TRANSACTION_ID
AND MR2.PLAN_ID = MFP2.PLAN_ID
AND MIF2.INVENTORY_ITEM_ID = MFP2.INVENTORY_ITEM_ID
AND MIF2.SR_INSTANCE_ID = MFP2.SR_INSTANCE_ID
AND MIF2.ORGANIZATION_ID = MFP2.ORGANIZATION_ID
AND MIF2.PLAN_ID = MFP2.PLAN_ID
AND MP.PLAN_ID = MFP2.PLAN_ID