VIEW: APPS.MSC_FLP_DEMAND_SUPPLY_ALL_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.inventory_item_id , substrb(mif2.item_name,1,80)||'/'|| 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 , 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), 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) , mfp2.sr_instance_id , substrb(nvl(mif2.description,mif2.item_name),1,80)||'/'|| msc_get_name.org_code(mfp2.organization_id,mfp2.sr_instance_id) from msc_system_items mif2, msc_supplies mr2, msc_full_pegging mfp2, msc_plans mp WHERE 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 and mr2.order_type <> 13 and nvl(mfp2.supply_type, 0) <> 4 union all select mfp2.rowid, mfp2.plan_id , mfp2.organization_id , mfp2.pegging_id , nvl(mfp2.prev_pegging_id, -1) , mfp2.demand_id , mr2.transaction_id , mfp2.inventory_item_id , substrb(mif2.item_name,1,80)||'/'|| 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 , msc_get_name.lookup_meaning('MRP_ORDER_TYPE',mr2.order_type) , 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) , mfp2.sr_instance_id , substrb(nvl(mif2.description,mif2.item_name),1,80)||'/'|| msc_get_name.org_code(mfp2.organization_id,mfp2.sr_instance_id) from msc_system_items mif2, msc_supplies mr2, msc_full_pegging mfp2 where mr2.disposition_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 mr2.order_type = 13 and mfp2.supply_type = 4

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.INVENTORY_ITEM_ID
, SUBSTRB(MIF2.ITEM_NAME
, 1
, 80)||'/'|| 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
, 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)
, 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)
, MFP2.SR_INSTANCE_ID
, SUBSTRB(NVL(MIF2.DESCRIPTION
, MIF2.ITEM_NAME)
, 1
, 80)||'/'|| MSC_GET_NAME.ORG_CODE(MFP2.ORGANIZATION_ID
, MFP2.SR_INSTANCE_ID)
FROM MSC_SYSTEM_ITEMS MIF2
, MSC_SUPPLIES MR2
, MSC_FULL_PEGGING MFP2
, MSC_PLANS MP
WHERE 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
AND MR2.ORDER_TYPE <> 13
AND NVL(MFP2.SUPPLY_TYPE
, 0) <> 4 UNION ALL SELECT MFP2.ROWID
, MFP2.PLAN_ID
, MFP2.ORGANIZATION_ID
, MFP2.PEGGING_ID
, NVL(MFP2.PREV_PEGGING_ID
, -1)
, MFP2.DEMAND_ID
, MR2.TRANSACTION_ID
, MFP2.INVENTORY_ITEM_ID
, SUBSTRB(MIF2.ITEM_NAME
, 1
, 80)||'/'|| 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
, MSC_GET_NAME.LOOKUP_MEANING('MRP_ORDER_TYPE'
, MR2.ORDER_TYPE)
, 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)
, MFP2.SR_INSTANCE_ID
, SUBSTRB(NVL(MIF2.DESCRIPTION
, MIF2.ITEM_NAME)
, 1
, 80)||'/'|| MSC_GET_NAME.ORG_CODE(MFP2.ORGANIZATION_ID
, MFP2.SR_INSTANCE_ID)
FROM MSC_SYSTEM_ITEMS MIF2
, MSC_SUPPLIES MR2
, MSC_FULL_PEGGING MFP2
WHERE MR2.DISPOSITION_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 MR2.ORDER_TYPE = 13
AND MFP2.SUPPLY_TYPE = 4