VIEW: APPS.MSC_ALLOCATION_DETAILS_V

Source

View Text - Preformatted

SELECT mad.rowid, mad.plan_id , mad.item_id, msi.product_family_id, mic.category_set_id , mic.sr_category_id , mad.alloc_bkt_id, mic.category_name , msi.item_name, msc_get_name.item_name(msi.product_family_id,null,null,null), msi.buyer_name , msi.planner_code , mab.bkt_start_date, mab.bkt_end_date, mad.organization_id, mad.sr_instance_id, msc_get_name.org_code(mad.organization_id, mad.sr_instance_id), mad.to_organization_id, mad.to_sr_instance_id, msc_get_name.org_code(mad.to_organization_id, mad.to_sr_instance_id), decode(mad.priority, -23453, to_number(null), mad.priority), mad.to_org_seq_id, mad.demand_qty, mad.allocated_qty, mad.total_supply_qty, mad.pri_supply_qty, decode(mad.pri_supply_qty, 0,to_number(null), round(100*mad.allocated_qty/mad.pri_supply_qty,2)), mad.higher_pri_allocated_qty, mad.higher_pri_demand_qty, mad.FIXED_LOT_MULTIPLIER, mad.supplier_id, mad.supplier_site_id, msc_get_name.supplier(mad.supplier_id), msc_get_name.supplier_site(mad.supplier_site_id), mad.firm_flag, mad.FIRM_CARRY_FWD_BKTS, mad.PRI_DEMAND_QTY, mad.LAST_UPDATE_DATE , mad.LAST_UPDATED_BY , mad.CREATION_DATE , mad.CREATED_BY , mad.LAST_UPDATE_LOGIN, mad.customer_id, msc_get_name.customer(mad.customer_id), mad.customer_site_id, msc_get_name.customer_site(mad.customer_site_id), mad.demand_class, mad.alloc_rule_id, mad.assembly_item_id, msc_get_name.item_name(mad.assembly_item_id,null,null,null) FROM msc_allocation_details mad, msc_allocation_buckets mab, msc_system_items msi, msc_item_categories mic, msc_plans mp WHERE msi.inventory_item_id = mad.ITEM_ID AND msi.organization_id = decode(mad.organization_id,-1,mad.to_organization_id, mad.organization_id) AND msi.sr_instance_id = decode(mad.sr_instance_id, -1, mad.to_sr_instance_id, mad.sr_instance_id) AND msi.plan_id = mad.plan_id AND mp.plan_id = mad.plan_id AND mab.sr_instance_id = mp.sr_instance_id AND mab.organization_id = mp.organization_id AND mab.plan_id = mp.plan_id AND mab.bucket_index = mad.alloc_bkt_id AND mic.organization_id = msi.organization_id AND mic.sr_instance_id = msi.sr_instance_id AND mic.inventory_item_id = msi.inventory_item_id

View Text - HTML Formatted

SELECT MAD.ROWID
, MAD.PLAN_ID
, MAD.ITEM_ID
, MSI.PRODUCT_FAMILY_ID
, MIC.CATEGORY_SET_ID
, MIC.SR_CATEGORY_ID
, MAD.ALLOC_BKT_ID
, MIC.CATEGORY_NAME
, MSI.ITEM_NAME
, MSC_GET_NAME.ITEM_NAME(MSI.PRODUCT_FAMILY_ID
, NULL
, NULL
, NULL)
, MSI.BUYER_NAME
, MSI.PLANNER_CODE
, MAB.BKT_START_DATE
, MAB.BKT_END_DATE
, MAD.ORGANIZATION_ID
, MAD.SR_INSTANCE_ID
, MSC_GET_NAME.ORG_CODE(MAD.ORGANIZATION_ID
, MAD.SR_INSTANCE_ID)
, MAD.TO_ORGANIZATION_ID
, MAD.TO_SR_INSTANCE_ID
, MSC_GET_NAME.ORG_CODE(MAD.TO_ORGANIZATION_ID
, MAD.TO_SR_INSTANCE_ID)
, DECODE(MAD.PRIORITY
, -23453
, TO_NUMBER(NULL)
, MAD.PRIORITY)
, MAD.TO_ORG_SEQ_ID
, MAD.DEMAND_QTY
, MAD.ALLOCATED_QTY
, MAD.TOTAL_SUPPLY_QTY
, MAD.PRI_SUPPLY_QTY
, DECODE(MAD.PRI_SUPPLY_QTY
, 0
, TO_NUMBER(NULL)
, ROUND(100*MAD.ALLOCATED_QTY/MAD.PRI_SUPPLY_QTY
, 2))
, MAD.HIGHER_PRI_ALLOCATED_QTY
, MAD.HIGHER_PRI_DEMAND_QTY
, MAD.FIXED_LOT_MULTIPLIER
, MAD.SUPPLIER_ID
, MAD.SUPPLIER_SITE_ID
, MSC_GET_NAME.SUPPLIER(MAD.SUPPLIER_ID)
, MSC_GET_NAME.SUPPLIER_SITE(MAD.SUPPLIER_SITE_ID)
, MAD.FIRM_FLAG
, MAD.FIRM_CARRY_FWD_BKTS
, MAD.PRI_DEMAND_QTY
, MAD.LAST_UPDATE_DATE
, MAD.LAST_UPDATED_BY
, MAD.CREATION_DATE
, MAD.CREATED_BY
, MAD.LAST_UPDATE_LOGIN
, MAD.CUSTOMER_ID
, MSC_GET_NAME.CUSTOMER(MAD.CUSTOMER_ID)
, MAD.CUSTOMER_SITE_ID
, MSC_GET_NAME.CUSTOMER_SITE(MAD.CUSTOMER_SITE_ID)
, MAD.DEMAND_CLASS
, MAD.ALLOC_RULE_ID
, MAD.ASSEMBLY_ITEM_ID
, MSC_GET_NAME.ITEM_NAME(MAD.ASSEMBLY_ITEM_ID
, NULL
, NULL
, NULL)
FROM MSC_ALLOCATION_DETAILS MAD
, MSC_ALLOCATION_BUCKETS MAB
, MSC_SYSTEM_ITEMS MSI
, MSC_ITEM_CATEGORIES MIC
, MSC_PLANS MP
WHERE MSI.INVENTORY_ITEM_ID = MAD.ITEM_ID
AND MSI.ORGANIZATION_ID = DECODE(MAD.ORGANIZATION_ID
, -1
, MAD.TO_ORGANIZATION_ID
, MAD.ORGANIZATION_ID)
AND MSI.SR_INSTANCE_ID = DECODE(MAD.SR_INSTANCE_ID
, -1
, MAD.TO_SR_INSTANCE_ID
, MAD.SR_INSTANCE_ID)
AND MSI.PLAN_ID = MAD.PLAN_ID
AND MP.PLAN_ID = MAD.PLAN_ID
AND MAB.SR_INSTANCE_ID = MP.SR_INSTANCE_ID
AND MAB.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND MAB.PLAN_ID = MP.PLAN_ID
AND MAB.BUCKET_INDEX = MAD.ALLOC_BKT_ID
AND MIC.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MIC.SR_INSTANCE_ID = MSI.SR_INSTANCE_ID
AND MIC.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID