VIEW: APPS.MRP_ATP_SD_TEMP_V

Source

View Text - Preformatted

SELECT madt.order_line_id, madt.Session_id, madt.scenario_id, madt.pegging_id, madt.parent_pegging_id, madt.atp_Level, madt.Request_Item_Id, madt.Request_Item_name, madt.Inventory_Item_Id, madt.Inventory_Item_name, madt.Organization_Id, NVL(madt.organization_code, madt1.organization_code), madt.Department_Id, madt1.DEPARTMENT_CODE, madt.Resource_Id, madt1.RESOURCE_CODE, madt.Supplier_Id, madt.supplier_name, madt.Supplier_Site_Id, madt.supplier_site_name, madt.From_Organization_Id, madt.from_organization_code, madt.From_Location_Id, madt.from_location_code, madt.To_Organization_Id, madt.to_organization_code, madt.To_Location_Id, madt.to_location_code, madt.Ship_Method, decode(madt.batchable_flag,1,'Capacity hours',madt.Uom_code), madt.Identifier1, madt.Identifier2, madt.Identifier3, madt.Identifier4, madt.Supply_Demand_Type, madt.Supply_demand_date, nvl(madt.original_demand_quantity,madt.Supply_Demand_Quantity), nvl(madt.original_supply_demand_type,madt.Supply_Demand_source_type), NVL(ml.meaning, ml2.meaning), madt.allocated_quantity, madt.source_type, madt.record_type, madt.total_supply_quantity, madt.total_demand_quantity, madt.period_start_date, madt.period_end_date, madt.period_quantity, madt.cumulative_quantity, madt.disposition_type, madt.disposition_name, decode(madt.department_id, NULL, DECODE(madt.original_item_id,null, NVL(madt1.inventory_item_name,madt.inventory_item_name), madt.inventory_item_name), decode(madt.resource_id, NULL, madt1.department_code, -1, madt1.department_code, madt1.resource_code)) , ml1.meaning, madt.end_pegging_id, msc_sch_wb.get_label(madt.char1), madt.plan_id, madt.demand_class, madt.class, madt.customer_name, madt.customer_site_name, decode(madt.supply_demand_type,'2','MSC_SUPPLIES','1','MSC_DEMANDS'), madt.customer_id, madt.customer_site_id, msc_sch_wb.get_label(madt.demand_class), decode(madt.class, '-1', null, msc_sch_wb.get_label(madt.class)), madt.pf_display_flag, nvl(madt.original_demand_date,madt.supply_demand_date), madt.orig_request_date, decode(madt.orig_demand_class, '-1', null, msc_sch_wb.get_label( madt.orig_demand_class)), madt.orig_customer_name, madt.orig_customer_site_name, nvl(madt.original_item_id, madt.inventory_item_id) from mrp_atp_details_temp madt, mrp_atp_details_temp madt1, mfg_lookups ml, mfg_lookups ml1, mfg_lookups ml2, mfg_lookups ml3, mfg_lookups ml4 WHERE ml2.lookup_type(+) = 'MRP_ATP_FORM_TYPE' and ml2.lookup_code(+) = decode(madt.department_id, NULL, 25, decode(madt.supply_demand_type, 1, -1, 20)) and ml3.lookup_type(+) = 'MRP_ATP_FORM_TYPE' and ml3.lookup_code(+) = decode(madt.demand_class, '-1', 118) and ml4.lookup_type(+) = 'MRP_ATP_FORM_TYPE' and ml4.lookup_code(+) = decode(madt.char1, '-1', 118) and ml1.lookup_type = 'MRP_ATP_FORM_TYPE' and ml1.lookup_code = decode(madt.department_id, NULL, 10, 15) and ml.lookup_type(+) = decode(madt.supply_demand_type,1, decode(madt.department_id, NULL, decode(madt.supplier_id, NULL, decode(nvl(madt.original_supply_demand_type,madt.supply_demand_source_type), 1, 'MRP_PLANNED_ORDER_DEMAND', 3, 'MRP_PLANNED_ORDER_DEMAND', 25, 'MRP_PLANNED_ORDER_DEMAND', 'MRP_DEMAND_ORIGINATION'), 'MRP_ORDER_TYPE'), 'MRP_ORDER_TYPE'), 'MRP_ORDER_TYPE') and ml.lookup_code(+) = nvl(madt.original_supply_demand_type,madt.supply_demand_source_type) and madt1.pegging_id(+) = madt.pegging_id and madt1.end_pegging_id(+) = madt.end_pegging_id and madt1.record_type(+) = 3 and madt.record_type = 2 order by madt.supply_demand_date, madt.supply_demand_source_type_name, madt. supply_demand_quantity

View Text - HTML Formatted

SELECT MADT.ORDER_LINE_ID
, MADT.SESSION_ID
, MADT.SCENARIO_ID
, MADT.PEGGING_ID
, MADT.PARENT_PEGGING_ID
, MADT.ATP_LEVEL
, MADT.REQUEST_ITEM_ID
, MADT.REQUEST_ITEM_NAME
, MADT.INVENTORY_ITEM_ID
, MADT.INVENTORY_ITEM_NAME
, MADT.ORGANIZATION_ID
, NVL(MADT.ORGANIZATION_CODE
, MADT1.ORGANIZATION_CODE)
, MADT.DEPARTMENT_ID
, MADT1.DEPARTMENT_CODE
, MADT.RESOURCE_ID
, MADT1.RESOURCE_CODE
, MADT.SUPPLIER_ID
, MADT.SUPPLIER_NAME
, MADT.SUPPLIER_SITE_ID
, MADT.SUPPLIER_SITE_NAME
, MADT.FROM_ORGANIZATION_ID
, MADT.FROM_ORGANIZATION_CODE
, MADT.FROM_LOCATION_ID
, MADT.FROM_LOCATION_CODE
, MADT.TO_ORGANIZATION_ID
, MADT.TO_ORGANIZATION_CODE
, MADT.TO_LOCATION_ID
, MADT.TO_LOCATION_CODE
, MADT.SHIP_METHOD
, DECODE(MADT.BATCHABLE_FLAG
, 1
, 'CAPACITY HOURS'
, MADT.UOM_CODE)
, MADT.IDENTIFIER1
, MADT.IDENTIFIER2
, MADT.IDENTIFIER3
, MADT.IDENTIFIER4
, MADT.SUPPLY_DEMAND_TYPE
, MADT.SUPPLY_DEMAND_DATE
, NVL(MADT.ORIGINAL_DEMAND_QUANTITY
, MADT.SUPPLY_DEMAND_QUANTITY)
, NVL(MADT.ORIGINAL_SUPPLY_DEMAND_TYPE
, MADT.SUPPLY_DEMAND_SOURCE_TYPE)
, NVL(ML.MEANING
, ML2.MEANING)
, MADT.ALLOCATED_QUANTITY
, MADT.SOURCE_TYPE
, MADT.RECORD_TYPE
, MADT.TOTAL_SUPPLY_QUANTITY
, MADT.TOTAL_DEMAND_QUANTITY
, MADT.PERIOD_START_DATE
, MADT.PERIOD_END_DATE
, MADT.PERIOD_QUANTITY
, MADT.CUMULATIVE_QUANTITY
, MADT.DISPOSITION_TYPE
, MADT.DISPOSITION_NAME
, DECODE(MADT.DEPARTMENT_ID
, NULL
, DECODE(MADT.ORIGINAL_ITEM_ID
, NULL
, NVL(MADT1.INVENTORY_ITEM_NAME
, MADT.INVENTORY_ITEM_NAME)
, MADT.INVENTORY_ITEM_NAME)
, DECODE(MADT.RESOURCE_ID
, NULL
, MADT1.DEPARTMENT_CODE
, -1
, MADT1.DEPARTMENT_CODE
, MADT1.RESOURCE_CODE))
, ML1.MEANING
, MADT.END_PEGGING_ID
, MSC_SCH_WB.GET_LABEL(MADT.CHAR1)
, MADT.PLAN_ID
, MADT.DEMAND_CLASS
, MADT.CLASS
, MADT.CUSTOMER_NAME
, MADT.CUSTOMER_SITE_NAME
, DECODE(MADT.SUPPLY_DEMAND_TYPE
, '2'
, 'MSC_SUPPLIES'
, '1'
, 'MSC_DEMANDS')
, MADT.CUSTOMER_ID
, MADT.CUSTOMER_SITE_ID
, MSC_SCH_WB.GET_LABEL(MADT.DEMAND_CLASS)
, DECODE(MADT.CLASS
, '-1'
, NULL
, MSC_SCH_WB.GET_LABEL(MADT.CLASS))
, MADT.PF_DISPLAY_FLAG
, NVL(MADT.ORIGINAL_DEMAND_DATE
, MADT.SUPPLY_DEMAND_DATE)
, MADT.ORIG_REQUEST_DATE
, DECODE(MADT.ORIG_DEMAND_CLASS
, '-1'
, NULL
, MSC_SCH_WB.GET_LABEL( MADT.ORIG_DEMAND_CLASS))
, MADT.ORIG_CUSTOMER_NAME
, MADT.ORIG_CUSTOMER_SITE_NAME
, NVL(MADT.ORIGINAL_ITEM_ID
, MADT.INVENTORY_ITEM_ID)
FROM MRP_ATP_DETAILS_TEMP MADT
, MRP_ATP_DETAILS_TEMP MADT1
, MFG_LOOKUPS ML
, MFG_LOOKUPS ML1
, MFG_LOOKUPS ML2
, MFG_LOOKUPS ML3
, MFG_LOOKUPS ML4
WHERE ML2.LOOKUP_TYPE(+) = 'MRP_ATP_FORM_TYPE'
AND ML2.LOOKUP_CODE(+) = DECODE(MADT.DEPARTMENT_ID
, NULL
, 25
, DECODE(MADT.SUPPLY_DEMAND_TYPE
, 1
, -1
, 20))
AND ML3.LOOKUP_TYPE(+) = 'MRP_ATP_FORM_TYPE'
AND ML3.LOOKUP_CODE(+) = DECODE(MADT.DEMAND_CLASS
, '-1'
, 118)
AND ML4.LOOKUP_TYPE(+) = 'MRP_ATP_FORM_TYPE'
AND ML4.LOOKUP_CODE(+) = DECODE(MADT.CHAR1
, '-1'
, 118)
AND ML1.LOOKUP_TYPE = 'MRP_ATP_FORM_TYPE'
AND ML1.LOOKUP_CODE = DECODE(MADT.DEPARTMENT_ID
, NULL
, 10
, 15)
AND ML.LOOKUP_TYPE(+) = DECODE(MADT.SUPPLY_DEMAND_TYPE
, 1
, DECODE(MADT.DEPARTMENT_ID
, NULL
, DECODE(MADT.SUPPLIER_ID
, NULL
, DECODE(NVL(MADT.ORIGINAL_SUPPLY_DEMAND_TYPE
, MADT.SUPPLY_DEMAND_SOURCE_TYPE)
, 1
, 'MRP_PLANNED_ORDER_DEMAND'
, 3
, 'MRP_PLANNED_ORDER_DEMAND'
, 25
, 'MRP_PLANNED_ORDER_DEMAND'
, 'MRP_DEMAND_ORIGINATION')
, 'MRP_ORDER_TYPE')
, 'MRP_ORDER_TYPE')
, 'MRP_ORDER_TYPE')
AND ML.LOOKUP_CODE(+) = NVL(MADT.ORIGINAL_SUPPLY_DEMAND_TYPE
, MADT.SUPPLY_DEMAND_SOURCE_TYPE)
AND MADT1.PEGGING_ID(+) = MADT.PEGGING_ID
AND MADT1.END_PEGGING_ID(+) = MADT.END_PEGGING_ID
AND MADT1.RECORD_TYPE(+) = 3
AND MADT.RECORD_TYPE = 2 ORDER BY MADT.SUPPLY_DEMAND_DATE
, MADT.SUPPLY_DEMAND_SOURCE_TYPE_NAME
, MADT. SUPPLY_DEMAND_QUANTITY