eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.MSC_PEGGING_SUPPLY_V3

Source

View Text - Preformatted


SELECT 'MSC_SUPPLIES' as SOURCE_TABLE, sup.ROWID as ROW_ID, sup.TRANSACTION_ID as TRANSACTION_ID, sup.LAST_UPDATE_DATE as LAST_UPDATE_DATE, sup.LAST_UPDATED_BY as LAST_UPDATED_BY, sup.CREATION_DATE as CREATION_DATE, sup.CREATED_BY as CREATED_BY, sup.LAST_UPDATE_LOGIN as LAST_UPDATE_LOGIN, msi.INVENTORY_ITEM_ID as INVENTORY_ITEM_ID, sup.ORGANIZATION_ID as ORGANIZATION_ID, msi.organization_code as ORGANIZATION_CODE, to_number(null) as PLAN_ORGANIZATION_ID, to_number(null) as PLAN_INSTANCE_ID, sup.sr_instance_id as SR_INSTANCE_ID, sup.plan_id as PLAN_ID, mp.compile_designator as COMPILE_DESIGNATOR, null as action, msc_get_name.action_id('MSC_SUPPLIES', msi.bom_item_type, msi.base_item_id, msi.wip_supply_type, sup.order_type, sup.reschedule_flag, sup.disposition_status_type, sup.new_schedule_date, sup.old_schedule_date, sup.implemented_quantity, sup.quantity_in_process, sup.new_order_quantity, msi.release_time_fence_code, sup.reschedule_days, sup.firm_quantity, sup.plan_id, msi.critical_component_flag, msi.mrp_planning_code, msi.lots_exist, sup.item_type_value) as ACTION_id, sup.NEW_SCHEDULE_DATE as NEW_DUE_DATE, sup.OLD_SCHEDULE_DATE as OLD_DUE_DATE, sup.NEW_WIP_START_DATE as NEW_START_DATE, sup.DISPOSITION_ID as DISPOSITION_ID, msc_get_name.supply_order_number ( sup.order_type ,sup.order_number ,sup.plan_id ,sup.sr_instance_id ,sup.transaction_id ,sup.disposition_id ) as ORDER_NUMBER, sup.DISPOSITION_STATUS_TYPE as DISPOSITION_STATUS_TYPE, sup.ORDER_TYPE as ORDER_TYPE, decode(mp.plan_type, 8, decode(sup.order_type, 1, msc_get_name.lookup_meaning('SRP_CHANGED_ORDER_TYPE',sup.order_type), 2, decode(sup.source_organization_id, null, msc_get_name.lookup_meaning('SRP_CHANGED_ORDER_TYPE',sup.order_type), msc_get_name.lookup_meaning('MRP_ORDER_TYPE',53) ), 51, msc_get_name.lookup_meaning('SRP_CHANGED_ORDER_TYPE',sup.order_type), l1.meaning),l1.meaning) as ORDER_TYPE_TEXT, NVL(sup.daily_rate,sup.NEW_ORDER_QUANTITY) as QUANTITY_RATE, sup.OLD_ORDER_QUANTITY as OLD_ORDER_QUANTITY, nvl(sup.NEW_ORDER_PLACEMENT_DATE , sup.firm_date) as NEW_ORDER_DATE, sup.FIRM_PLANNED_TYPE as FIRM_PLANNED_TYPE, sup.RESCHEDULE_FLAG as RESCHEDULED_FLAG, sup.NEW_PROCESSING_DAYS as NEW_PROCESSING_DAYS, sup.IMPLEMENTED_QUANTITY as IMPLEMENTED_QUANTITY, sup.LAST_UNIT_COMPLETION_DATE as LAST_UNIT_COMPLETION_DATE, sup.FIRST_UNIT_START_DATE as FIRST_UNIT_START_DATE, sup.LAST_UNIT_START_DATE as LAST_UNIT_START_DATE, sup.NEW_DOCK_DATE as NEW_DOCK_DATE, sup.QUANTITY_IN_PROCESS as QUANTITY_IN_PROCESS, sup.FIRM_QUANTITY as FIRM_QUANTITY, sup.FIRM_DATE as FIRM_DATE, sup.LINE_ID as LINE_ID, /*DECODE(sup.line_id, NULL, NULL, msc_get_name.department_code(1,sup.line_id, sup.organization_id, sup.plan_id, sup.sr_instance_id)) as LINE_CODE,*/ null as LINE_CODE, sup.IMPLEMENT_DEMAND_CLASS as IMPLEMENT_DEMAND_CLASS, msi.item_name as ITEM_SEGMENTS, sup.IMPLEMENT_DATE as IMPLEMENT_DATE, NVL(sup.implement_daily_rate,sup.IMPLEMENT_QUANTITY) as IMPLEMENT_QUANTITY_RATE, sup.IMPLEMENT_FIRM as IMPLEMENT_FIRM, TO_NUMBER(NULL) as IMPLEMENT_PROCESSING_DAYS, sup.IMPLEMENT_WIP_CLASS_CODE as IMPLEMENT_WIP_CLASS_CODE, sup.IMPLEMENT_JOB_NAME as IMPLEMENT_JOB_NAME, TO_NUMBER(NULL) as IMPLEMENT_LINE_ID, sup.IMPLEMENT_STATUS_CODE as IMPLEMENT_STATUS_CODE, sup.IMPLEMENT_LOCATION_ID as IMPLEMENT_LOCATION_ID, RELEASE_STATUS as RELEASE_STATUS, sup.IMPLEMENT_AS as IMPLEMENT_AS, DECODE(sup.implement_as, NULL, NULL, msc_get_name.lookup_meaning('MRP_WORKBENCH_IMPLEMENT_AS',sup.implement_as)) as IMPLEMENT_AS_TEXT, msi.PLANNER_CODE as PLANNER_CODE, to_number(null) as USING_ASSEMBLY_ITEM_ID, msi.sr_category_id as CATEGORY_ID, msi.MRP_PLANNING_CODE as MRP_PLANNING_CODE, msi.REPETITIVE_TYPE as REPETITIVE_TYPE, msi.BUILD_IN_WIP_FLAG as BUILD_IN_WIP_FLAG, msi.PURCHASING_ENABLED_FLAG as PURCHASING_ENABLED_FLAG, msi.PLANNING_MAKE_BUY_CODE as PLANNING_MAKE_BUY_CODE, round(GREATEST(0, sup.NEW_ORDER_PLACEMENT_DATE - (TRUNC(SYSDATE) )) , 2) as DAYS_FROM_TODAY, msi.BOM_ITEM_TYPE as BOM_ITEM_TYPE, msi.BASE_ITEM_ID as BASE_ITEM_ID, msi.WIP_SUPPLY_TYPE as WIP_SUPPLY_TYPE, sup.SOURCE_ORGANIZATION_ID as SOURCE_ORGANIZATION_ID, sup.SOURCE_sr_instance_id as SOURCE_SR_INSTANCE_ID, msc_get_name.org_code(sup.source_organization_id,sup.source_sr_instance_id) as SOURCE_ORGANIZATION_CODE, sup.wip_status_code as STATUS_CODE, NULL as USING_ASSEMBLY_SEGMENTS, msi.FULL_PEGGING as FULL_PEGGING, sup.source_supplier_id as SOURCE_VENDOR_ID, msc_get_name.source_supplier(sup.sr_instance_id, sup.plan_id,sup.supplier_id,sup.source_supplier_id,sup.SOURCE_ORGANIZATION_ID,sup.order_type) as SOURCE_VENDOR_NAME, sup.source_supplier_site_id as SOURCE_VENDOR_SITE_ID, msc_get_name.source_supplier_site(sup.sr_instance_id, sup.plan_id,sup.supplier_site_id,sup.source_supplier_site_id,sup.SOURCE_ORGANIZATION_ID,sup.order_type) as SOURCE_VENDOR_SITE_CODE, sup.supplier_id as VENDOR_ID, NULL as SUPPLIER_NAME, sup.supplier_site_id as VENDOR_SITE_ID, NULL as SUPPLIER_SITE_CODE, sup.IMPLEMENT_SOURCE_ORG_ID as IMPLEMENT_SOURCE_ORG_ID, sup.IMPLEMENT_sr_instance_id as IMPLEMENT_SR_INSTANCE_ID, sup.implement_supplier_id as IMPLEMENT_VENDOR_ID, sup.implement_supplier_site_id as IMPLEMENT_VENDOR_SITE_ID, DECODE(msi.IN_SOURCE_PLAN, 1, 1, 2) as IN_SOURCE_PLAN, sup.schedule_compress_days as SCHEDULE_COMPRESSION_DAYS, sup.project_id as PROJECT_ID, sup.task_id as TASK_ID, DECODE(sup.project_id, NULL, NULL, msc_get_name.project(sup.project_id, sup.organization_id, sup.plan_id, sup.sr_instance_id)) as PROJECT_NUMBER, DECODE(sup.task_id, NULL, NULL, msc_get_name.task(sup.task_id, sup.project_id,sup.organization_id, sup.plan_id, sup.sr_instance_id)) as TASK_NUMBER, sup.implement_project_id as IMPLEMENT_PROJECT_ID, sup.implement_task_id as IMPLEMENT_TASK_ID, sup.planning_group as PLANNING_GROUP, sup.status as STATUS, sup.applied as APPLIED, msi.release_time_fence_code as RELEASE_TIME_FENCE_CODE, sup.implement_schedule_group_id as IMPLEMENT_SCHEDULE_GROUP_ID, sup.implement_build_sequence as IMPLEMENT_BUILD_SEQUENCE, DECODE(sup.ORDER_TYPE, 3, sup.BUILD_SEQUENCE, 7, sup.BUILD_SEQUENCE, 14, sup.BUILD_SEQUENCE, 15, sup.BUILD_SEQUENCE, 27, sup.BUILD_SEQUENCE, to_number( NULL)) as BUILD_SEQUENCE, DECODE(sup.ORDER_TYPE, 3, sup.SCHEDULE_GROUP_ID, 7, sup.SCHEDULE_GROUP_ID, 14, sup.SCHEDULE_GROUP_ID, 15, sup.SCHEDULE_GROUP_ID, 27, sup.SCHEDULE_GROUP_ID, to_number(NULL)) as SCHEDULE_GROUP_ID, decode(sup.plan_id,-1,sup.alternate_bom_designator, nvl(sup.alternate_bom_designator,msc_get_name.alternate_bom(sup.plan_id, sup.sr_instance_id, nvl(pe.bill_sequence_id,sup.bill_sequence_id)))) as ALTERNATE_BOM_DESIGNATOR, decode(sup.plan_id,-1, sup.alternate_routing_designator,nvl(sup.alternate_routing_designator, msc_get_name.alternate_rtg(sup.plan_id, sup.sr_instance_id,nvl(pe.routing_sequence_id,sup.routing_sequence_id)))) as ALTERNATE_ROUTING_DESIGNATOR, sup.process_seq_id as PROCESS_SEQ_ID, sup.implement_alternate_bom as IMPLEMENT_ALTERNATE_BOM, sup.implement_alternate_routing as IMPLEMENT_ALTERNATE_ROUTING, sup.schedule_group_name as SCHEDULE_GROUP_NAME, sup.implement_employee_id as IMPLEMENT_EMPLOYEE_ID, msc_get_name.cfm_routing_flag(pe.plan_id, pe.sr_instance_id,pe.routing_sequence_id) as CFM_ROUTING_FLAG, msi.buyer_name as BUYER_NAME, sup.release_errors as RELEASE_ERRORS, sup.number1 as REL_ALL_QTY, sup.unit_number as UNIT_NUMBER, sup.implement_unit_number as IMPLEMENT_UNIT_NUMBER, msi.description as DESCRIPTION, nvl(sup.planning_group, '0') as PLANNING_GROUP_HIDDEN, /*nvl(msi.buyer_name, '0') as BUYER_NAME_HIDDEN,*/ null as BUYER_NAME_HIDDEN, nvl(msi.planner_code,'0') as PLANNER_CODE_HIDDEN, nvl(sup.project_id, 0) as PROJECT_ID_HIDDEN, nvl(sup.task_id,0) as TASK_ID_HIDDEN, null as LENDING_PROJECT_NUMBER, null as LENDING_TASK_NUMBER, mp.category_set_id as CATEGORY_SET_ID, msi.category_name as CATEGORY_NAME, msi.product_family_id as PRODUCT_FAMILY_ID, msc_get_name.item_name(msi.product_family_id,null,null,null) as PRODUCT_FAMILY_NAME, /*msc_get_name.item_name(msi.base_item_id,null,null,null) as BASE_ITEM_NAME,*/ null as BASE_ITEM_NAME, msi.abc_class_name as ABC_CLASS, msi.uom_code as UOM_CODE, /*decode(mp.plan_type, 5, decode(sup.order_type,2, msc_drp_util.get_iso_name(sup.plan_id, sup.sr_instance_id, sup.transaction_id),null),null) as INTERNAL_SO_NUMBER,*/ null as INTERNAL_SO_NUMBER, decode(mp.plan_type, 5, to_number(null), sup.schedule_priority) as DEMAND_PRIORITY, sup.promised_date as PROMISE_DATE, to_date(null) as REQUEST_DATE, to_number(null) as CUSTOMER_ID, null as CUSTOMER_NAME, to_number(null) as CUSTOMER_SITE_ID, null as CUSTOMER_SITE_NAME, to_number(null) as SHIP_TO_SITE_ID, null as SHIP_TO_SITE_NAME, sup.lot_number as LOT_NUMBER, sup.subinventory_code as SUBINVENTORY_CODE, sup.parent_id as PARENT_ID, sup.wip_status_code as WIP_STATUS_CODE, msc_get_name.lookup_meaning('WIP_JOB_STATUS', sup.wip_status_code) as WIP_STATUS_TEXT, to_number(null) as DMD_LATENESS_COST, to_date(null) as DMD_SATISFIED_DATE, sup.need_by_date as NEED_BY_DATE, DECODE(sup.schedule_designator_id, NULL, NULL, msc_get_name.designator(sup.schedule_designator_id)) as DESIGNATOR_NAME, msi.list_price as LIST_PRICE, msi.standard_cost as STANDARD_COST, to_number(null) as SELLING_PRICE, to_number(null) as SERVICE_LEVEL, decode(sup.demand_class ,'-1', null, sup.demand_class) as DEMAND_CLASS, to_number(null) as PROBABILITY, sup.new_ship_date as SHIP_DATE, nvl(sup.daily_rate,sup.new_order_quantity) as QUANTITY, to_date(null) as ASSEMBLY_DEMAND_COMP_DATE, null as BUCKET_TYPE, to_number(null) as SOURCE_DMD_PRIORITY, sup.purch_line_num as PO_LINE_ID, sup.reschedule_days as RESCHEDULE_DAYS, decode(sup.plan_id, -1, sup.new_dock_date, sup.old_need_by_date) as OLD_NEED_BY_DATE, to_number(null) as CUMULATIVE_PROBABILITY, to_number(null) as ORIGINAL_ITEM_ID, null as ORIGINAL_ITEM_NAME, to_number(null) as ORIGINAL_ITEM_QTY, sup.ship_method as SHIP_METHOD, sup.explosion_date as EXPLOSION_DATE, to_number(NULL) as UNMET_QUANTITY, sup.original_quantity as ORIGINAL_QUANTITY, sup.original_need_by_date as ORIGINAL_NEED_BY_DATE, sup.earliest_start_date as EPST, sup.earliest_completion_date as EPCT, sup.min_start_date as MIN_START_TIME, msi.low_level_code as LOW_LEVEL_CODE, round(nvl(sup.earliest_completion_date-sup.need_by_date,0),2) as ORDERS_DAYS_LATE, sup.ulpsd as ULPSD, sup.ulpcd as ULPCD, sup.uepsd as UEPSD, sup.uepcd as UEPCD, sup.eacd as EACD, sup.supply_is_shared as SUPPLY_IS_SHARED, sup.record_source as RECORD_SOURCE, to_number(null) as FORWARD_DAYS, to_number(null) as BACKWARD_DAYS, sup.wip_start_quantity as WIP_START_QUANTITY, sup.expiration_date as EXPIRATION_DATE, msc_get_name.lookup_meaning('MRP_PLANNING_CODE',msi.mrp_planning_code) as MRP_PLANNING_CODE_TEXT, to_number(null) as ORIGINAL_ORG_ID, to_number(null) as ORIGINAL_INST_ID, /*decode(sup.order_type, 3, decode( msi.rounding_control_type, 1, ceil(decode(sup.implement_quantity, null, sup.new_order_quantity, sup.implement_quantity)/MSC_Rel_Plan_PUB.GET_REV_CUM_YIELD( sup.sr_instance_id, sup.plan_id, sup.process_seq_id, sup.transaction_id, sup.organization_id)), decode(sup.implement_quantity, null, sup.new_order_quantity, sup.implement_quantity)/MSC_Rel_Plan_PUB.GET_REV_CUM_YIELD( sup.sr_instance_id, sup.plan_id, sup.process_seq_id, sup.transaction_id, sup.organization_id)), 5, decode(msi.PLANNING_MAKE_BUY_CODE,1,decode(msi.rounding_control_type, 1, ceil(decode(sup.implement_quantity, null, sup.new_order_quantity, sup.implement_quantity)/ MSC_Rel_Plan_PUB.GET_REV_CUM_YIELD(sup.sr_instance_id, sup.plan_id, sup.process_seq_id, sup.transaction_id, sup.organization_id)), decode(sup.implement_quantity, null, sup.new_order_quantity, sup.implement_quantity)/ MSC_Rel_Plan_PUB.GET_REV_CUM_YIELD(sup.sr_instance_id, sup.plan_id, sup.process_seq_id, sup.transaction_id, sup.organization_id)), to_number(null)), to_number(null)) as START_QUANTITY,*/ to_number(null) as START_QUANTITY, msi.critical_component_flag as CRITICAL_COMPONENT_FLAG, to_number(null) as ORDER_DATE_TYPE_CODE, to_char(null) as ORDER_DATE_TYPE_TEXT, to_date(null) as SCHEDULE_ARRIVAL_DATE, to_date(null) as LATEST_ACCEPTABLE_DATE, to_date(null) as PLANNED_ARRIVAL_DATE, sup.shipment_id as SHIPMENT_ID, sup.ship_calendar as SHIP_CALENDAR, sup.receiving_calendar as RECEIVING_CALENDAR, sup.intransit_calendar as INTRANSIT_CALENDAR, sup.intransit_lead_time as INTRANSIT_LEAD_TIME, sup.implement_dock_date as IMPLEMENT_DOCK_DATE, sup.implement_ship_date as IMPLEMENT_SHIP_DATE, to_date(null) as IMPLEMENT_ARRIVAL_DATE, to_date(null) as REQUEST_SHIP_DATE, to_date(null) as PROMISE_SHIP_DATE, sup.days_late as LATE_DAYS, null as RECOMMENDED, to_number(null) as SUBSTITUTE_COMPONENT_RANK, to_number(null) as COMPONENT_YIELD, 'MSC_SUPPLIES' as SOURCE_TABLE2, nvl(decode(sup.order_type,5,MSC_UTIL.GET_VMI_FLAG ( SUP.PLAN_ID, SUP.SR_INSTANCE_ID,SUP.ORGANIZATION_ID, SUP.INVENTORY_ITEM_ID, SUP.SUPPLIER_ID ,SUP.SUPPLIER_SITE_ID), SUP.VMI_FLAG),2) as VMI_FLAG, nvl(msi.unit_weight,0)*nvl(sup.firm_quantity,sup.new_order_quantity) as WEIGHT, nvl(msi.unit_volume,0)*nvl(sup.firm_quantity,sup.new_order_quantity) as VOLUME, to_date(null) as SCHEDULE_SHIP_DATE, null as DEST_ORG_CODE, to_number(null) as QTY_BY_DUE_DATE, null as SHIP_SET_NAME, null as ARRIVAL_SET_NAME, null as ORIG_ORG_CODE, to_number(null) as ORIG_LEAD_TIME, null as ORIG_SHIP_METHOD, to_number(null) as DEST_ORG_ID, to_number(null) as DEST_INST_ID, to_number(null) as IMPLEMENT_DEST_ORG_ID, to_number(null) as IMPLEMENT_DEST_INST_ID, null as IMPLEMENT_DEST_ORG_CODE, to_date(null) as HIDDEN_DOCK_DATE, to_number(null) as FINAL_USE_PRIORITY, to_date(null) as FINAL_USE_DATE, msc_get_name.get_zone_name(sup.zone_id, sup.sr_instance_id) as ZONE, to_number(null) as ALLOCATION_QTY, to_number(null) as FIRM_ALLOCATION_FLAG, to_date(null) as ALLOCATION_DATE, null as SALES_ORDER_LINE_SPLIT, /*decode(sup.order_type , 70, msc_get_name.eam_parent_work_order (sup.plan_id, sup.sr_instance_id, sup.transaction_id), to_number(null) ) as PARENT_WORK_ORDER,*/ to_number(null) as PARENT_WORK_ORDER, /*msc_get_name.item_name (sup.asset_item_id, sup.organization_id, sup.plan_id, sup.sr_instance_id) as ASSET_GROUP,*/ null as ASSET_GROUP, sup.asset_serial_number as ASSET_NUMBER, sup.requested_start_date as REQUESTED_START_DATE, sup.requested_completion_date as REQUESTED_COMPLETION_DATE, to_number(null) as FILL_KILL_FLAG, msi.lots_exist as LOTS_EXIST, sup.implement_ship_method as IMPLEMENT_SHIP_METHOD, sup.actual_start_date as ACTUAL_START_DATE, to_date(null) as FIRM_DUE_DATE, to_date(null) as IMPLEMENT_DUE_DATE, to_date(null) as HIDDEN_DUE_DATE, /*msc_get_name.is_within_rel_time_fence(mp.plan_start_date, NVL(sup.NEW_ORDER_PLACEMENT_DATE , sup.firm_date), msi.release_time_fence_code, msi.cumulative_total_lead_time, msi.cum_manufacturing_lead_time, msi.full_lead_time, msi.release_time_fence_days) as WITHIN_REL_TIME_FENCE,*/ to_number(null) as WITHIN_REL_TIME_FENCE, (nvl(sup.daily_rate,sup.new_order_quantity) * msi.standard_cost) AMOUNT, to_number(null) as SO_LINE_SPLIT, to_number(null) as FORECAST_SET_ID, to_number(null) as SCHEDULE_DESIGNATOR_ID, sup.delivery_price as DELIVERY_PRICE, to_number(null) sales_order_line_id , to_number(null) ship_set_id , to_number(null) arrival_set_id , sup.item_type_id as ITEM_TYPE_ID, sup.item_type_value as ITEM_TYPE_VALUE, /* decode(mp.plan_type, 8, msc_get_name.lookup_meaning('MSC_PART_CONDITION',sup.item_type_value), null) as PART_CONDITION,*/ null as PART_CONDITION, sup.firm_start_date as FIRM_START_DATE, sup.bill_sequence_id as BILL_SEQUENCE_ID, sup.routing_sequence_id as ROUTING_SEQUENCE_ID, to_number(null) as PREV_SUBST_ITEM_ID, to_number(null) as PREV_SUBST_ORG_ID, null as PREV_SUBST_ITEM, null as PREV_SUBST_ORG, sup.OTM_ARRIVAL_DATE as OTM_ARRIVAL_DATE, to_number(null) as MIN_REM_SHELF_LIFE_DAYS, Msc_GET_name.get_order_Comments(sup.plan_id, 'SUPPLY', sup.TRANSACTION_ID) COMMENTS, mfp2.demand_id as pegged_demand_id, mfp2.allocated_quantity, mfp2.pegging_id, mfp2.prev_pegging_id, mfp2.end_pegging_id, mfp1.demand_id as down_level_demand_id, mfp1.transaction_id as down_level_transaction_id, mfp1.organization_id as down_level_organization_id, mfp1.inventory_item_id as down_level_inventory_item_id, mfp1.sr_instance_id as down_level_sr_instance_id, mfp1.pegging_id as down_level_pegging_id, mfp1.prev_pegging_id as down_level_prev_pegging_id, mfp1.end_pegging_id as down_level_end_pegging_id, sup.load_type, msi.plan_id base_plan_id, msi.rounding_control_type, sup.OLD_DOCK_DATE, mp.CURR_CUTOFF_DATE as plan_cutoff_date, mp.CURR_START_DATE as plan_start_date, to_number(null) as reserved_quantity , sup.CTB_FLAG , sup.CTB_COMP_AVAIL_PERCENT , sup.RTB_ORDER_QTY_PERCENT , sup.CTB_EXPECTED_DATE , sup.POTENTIAL_RTB_PERCENT , sup.CTB_PRIORITY , to_date(null) as revised_dmd_date , to_number(null) as revised_dmd_priority , nvl(msc_get_name.lookup_meaning1('SHIP_METHOD',sup.SHIP_METHOD,msc_get_name.get_application_id('AU'),0) , sup.SHIP_METHOD) as ship_method_text , sup.ORIG_FIRM_DATE as ORIG_FIRM_DATE , sup.ORIG_FIRM_QUANTITY as ORIG_FIRM_QUANTITY from MSC_SUPPLIES SUP, mfg_lookups l1, msc_system_items msi , msc_plans mp, (select mp1.plan_id plan_id,pe1.sr_instance_id,pe1.routing_sequence_id,pe1.bill_sequence_id ,pe1.process_Sequence_id from msc_process_effectivity pe1,msc_plans mp1 where pe1.plan_id = nvl(mp1.base_plan_id,mp1.plan_id) ) pe, msc_full_pegging mfp1, msc_full_pegging mfp2 WHERE pe.plan_id(+) = sup.plan_id and pe.sr_instance_id(+) = sup.sr_instance_id and pe.process_sequence_id(+) = sup.process_seq_id and msi.plan_id=nvl(mp.base_plan_id,mp.plan_id) and sup.sr_instance_id = msi.sr_instance_id and sup.ORGANIZATION_ID = msi.ORGANIZATION_ID and sup.INVENTORY_ITEM_ID = msi.INVENTORY_ITEM_ID and l1.lookup_type = 'MRP_ORDER_TYPE' and l1.lookup_code = sup.order_type and mp.plan_id = sup.plan_id and mfp2.plan_id = mp.plan_id and mfp2.sr_instance_id = msi.sr_instance_id and mfp2.organization_id=msi.organization_id and mfp2.inventory_item_id = msi.inventory_item_id and mfp2.transaction_id = sup.transaction_id and mfp2.plan_id = mfp1.plan_id and mfp2.sr_instance_id = mfp1.sr_instance_id and mfp2.prev_pegging_id = mfp1.pegging_id AND (msi.new_plan_id = -1 OR msi.new_plan_id IS NULL ) AND (msi.SIMULATION_SET_ID IS NULL)

View Text - HTML Formatted

SELECT 'MSC_SUPPLIES' AS SOURCE_TABLE
, SUP.ROWID AS ROW_ID
, SUP.TRANSACTION_ID AS TRANSACTION_ID
, SUP.LAST_UPDATE_DATE AS LAST_UPDATE_DATE
, SUP.LAST_UPDATED_BY AS LAST_UPDATED_BY
, SUP.CREATION_DATE AS CREATION_DATE
, SUP.CREATED_BY AS CREATED_BY
, SUP.LAST_UPDATE_LOGIN AS LAST_UPDATE_LOGIN
, MSI.INVENTORY_ITEM_ID AS INVENTORY_ITEM_ID
, SUP.ORGANIZATION_ID AS ORGANIZATION_ID
, MSI.ORGANIZATION_CODE AS ORGANIZATION_CODE
, TO_NUMBER(NULL) AS PLAN_ORGANIZATION_ID
, TO_NUMBER(NULL) AS PLAN_INSTANCE_ID
, SUP.SR_INSTANCE_ID AS SR_INSTANCE_ID
, SUP.PLAN_ID AS PLAN_ID
, MP.COMPILE_DESIGNATOR AS COMPILE_DESIGNATOR
, NULL AS ACTION
, MSC_GET_NAME.ACTION_ID('MSC_SUPPLIES'
, MSI.BOM_ITEM_TYPE
, MSI.BASE_ITEM_ID
, MSI.WIP_SUPPLY_TYPE
, SUP.ORDER_TYPE
, SUP.RESCHEDULE_FLAG
, SUP.DISPOSITION_STATUS_TYPE
, SUP.NEW_SCHEDULE_DATE
, SUP.OLD_SCHEDULE_DATE
, SUP.IMPLEMENTED_QUANTITY
, SUP.QUANTITY_IN_PROCESS
, SUP.NEW_ORDER_QUANTITY
, MSI.RELEASE_TIME_FENCE_CODE
, SUP.RESCHEDULE_DAYS
, SUP.FIRM_QUANTITY
, SUP.PLAN_ID
, MSI.CRITICAL_COMPONENT_FLAG
, MSI.MRP_PLANNING_CODE
, MSI.LOTS_EXIST
, SUP.ITEM_TYPE_VALUE) AS ACTION_ID
, SUP.NEW_SCHEDULE_DATE AS NEW_DUE_DATE
, SUP.OLD_SCHEDULE_DATE AS OLD_DUE_DATE
, SUP.NEW_WIP_START_DATE AS NEW_START_DATE
, SUP.DISPOSITION_ID AS DISPOSITION_ID
, MSC_GET_NAME.SUPPLY_ORDER_NUMBER ( SUP.ORDER_TYPE
, SUP.ORDER_NUMBER
, SUP.PLAN_ID
, SUP.SR_INSTANCE_ID
, SUP.TRANSACTION_ID
, SUP.DISPOSITION_ID ) AS ORDER_NUMBER
, SUP.DISPOSITION_STATUS_TYPE AS DISPOSITION_STATUS_TYPE
, SUP.ORDER_TYPE AS ORDER_TYPE
, DECODE(MP.PLAN_TYPE
, 8
, DECODE(SUP.ORDER_TYPE
, 1
, MSC_GET_NAME.LOOKUP_MEANING('SRP_CHANGED_ORDER_TYPE'
, SUP.ORDER_TYPE)
, 2
, DECODE(SUP.SOURCE_ORGANIZATION_ID
, NULL
, MSC_GET_NAME.LOOKUP_MEANING('SRP_CHANGED_ORDER_TYPE'
, SUP.ORDER_TYPE)
, MSC_GET_NAME.LOOKUP_MEANING('MRP_ORDER_TYPE'
, 53) )
, 51
, MSC_GET_NAME.LOOKUP_MEANING('SRP_CHANGED_ORDER_TYPE'
, SUP.ORDER_TYPE)
, L1.MEANING)
, L1.MEANING) AS ORDER_TYPE_TEXT
, NVL(SUP.DAILY_RATE
, SUP.NEW_ORDER_QUANTITY) AS QUANTITY_RATE
, SUP.OLD_ORDER_QUANTITY AS OLD_ORDER_QUANTITY
, NVL(SUP.NEW_ORDER_PLACEMENT_DATE
, SUP.FIRM_DATE) AS NEW_ORDER_DATE
, SUP.FIRM_PLANNED_TYPE AS FIRM_PLANNED_TYPE
, SUP.RESCHEDULE_FLAG AS RESCHEDULED_FLAG
, SUP.NEW_PROCESSING_DAYS AS NEW_PROCESSING_DAYS
, SUP.IMPLEMENTED_QUANTITY AS IMPLEMENTED_QUANTITY
, SUP.LAST_UNIT_COMPLETION_DATE AS LAST_UNIT_COMPLETION_DATE
, SUP.FIRST_UNIT_START_DATE AS FIRST_UNIT_START_DATE
, SUP.LAST_UNIT_START_DATE AS LAST_UNIT_START_DATE
, SUP.NEW_DOCK_DATE AS NEW_DOCK_DATE
, SUP.QUANTITY_IN_PROCESS AS QUANTITY_IN_PROCESS
, SUP.FIRM_QUANTITY AS FIRM_QUANTITY
, SUP.FIRM_DATE AS FIRM_DATE
, SUP.LINE_ID AS LINE_ID
, /*DECODE(SUP.LINE_ID
, NULL
, NULL
, MSC_GET_NAME.DEPARTMENT_CODE(1
, SUP.LINE_ID
, SUP.ORGANIZATION_ID
, SUP.PLAN_ID
, SUP.SR_INSTANCE_ID)) AS LINE_CODE
, */ NULL AS LINE_CODE
, SUP.IMPLEMENT_DEMAND_CLASS AS IMPLEMENT_DEMAND_CLASS
, MSI.ITEM_NAME AS ITEM_SEGMENTS
, SUP.IMPLEMENT_DATE AS IMPLEMENT_DATE
, NVL(SUP.IMPLEMENT_DAILY_RATE
, SUP.IMPLEMENT_QUANTITY) AS IMPLEMENT_QUANTITY_RATE
, SUP.IMPLEMENT_FIRM AS IMPLEMENT_FIRM
, TO_NUMBER(NULL) AS IMPLEMENT_PROCESSING_DAYS
, SUP.IMPLEMENT_WIP_CLASS_CODE AS IMPLEMENT_WIP_CLASS_CODE
, SUP.IMPLEMENT_JOB_NAME AS IMPLEMENT_JOB_NAME
, TO_NUMBER(NULL) AS IMPLEMENT_LINE_ID
, SUP.IMPLEMENT_STATUS_CODE AS IMPLEMENT_STATUS_CODE
, SUP.IMPLEMENT_LOCATION_ID AS IMPLEMENT_LOCATION_ID
, RELEASE_STATUS AS RELEASE_STATUS
, SUP.IMPLEMENT_AS AS IMPLEMENT_AS
, DECODE(SUP.IMPLEMENT_AS
, NULL
, NULL
, MSC_GET_NAME.LOOKUP_MEANING('MRP_WORKBENCH_IMPLEMENT_AS'
, SUP.IMPLEMENT_AS)) AS IMPLEMENT_AS_TEXT
, MSI.PLANNER_CODE AS PLANNER_CODE
, TO_NUMBER(NULL) AS USING_ASSEMBLY_ITEM_ID
, MSI.SR_CATEGORY_ID AS CATEGORY_ID
, MSI.MRP_PLANNING_CODE AS MRP_PLANNING_CODE
, MSI.REPETITIVE_TYPE AS REPETITIVE_TYPE
, MSI.BUILD_IN_WIP_FLAG AS BUILD_IN_WIP_FLAG
, MSI.PURCHASING_ENABLED_FLAG AS PURCHASING_ENABLED_FLAG
, MSI.PLANNING_MAKE_BUY_CODE AS PLANNING_MAKE_BUY_CODE
, ROUND(GREATEST(0
, SUP.NEW_ORDER_PLACEMENT_DATE - (TRUNC(SYSDATE) ))
, 2) AS DAYS_FROM_TODAY
, MSI.BOM_ITEM_TYPE AS BOM_ITEM_TYPE
, MSI.BASE_ITEM_ID AS BASE_ITEM_ID
, MSI.WIP_SUPPLY_TYPE AS WIP_SUPPLY_TYPE
, SUP.SOURCE_ORGANIZATION_ID AS SOURCE_ORGANIZATION_ID
, SUP.SOURCE_SR_INSTANCE_ID AS SOURCE_SR_INSTANCE_ID
, MSC_GET_NAME.ORG_CODE(SUP.SOURCE_ORGANIZATION_ID
, SUP.SOURCE_SR_INSTANCE_ID) AS SOURCE_ORGANIZATION_CODE
, SUP.WIP_STATUS_CODE AS STATUS_CODE
, NULL AS USING_ASSEMBLY_SEGMENTS
, MSI.FULL_PEGGING AS FULL_PEGGING
, SUP.SOURCE_SUPPLIER_ID AS SOURCE_VENDOR_ID
, MSC_GET_NAME.SOURCE_SUPPLIER(SUP.SR_INSTANCE_ID
, SUP.PLAN_ID
, SUP.SUPPLIER_ID
, SUP.SOURCE_SUPPLIER_ID
, SUP.SOURCE_ORGANIZATION_ID
, SUP.ORDER_TYPE) AS SOURCE_VENDOR_NAME
, SUP.SOURCE_SUPPLIER_SITE_ID AS SOURCE_VENDOR_SITE_ID
, MSC_GET_NAME.SOURCE_SUPPLIER_SITE(SUP.SR_INSTANCE_ID
, SUP.PLAN_ID
, SUP.SUPPLIER_SITE_ID
, SUP.SOURCE_SUPPLIER_SITE_ID
, SUP.SOURCE_ORGANIZATION_ID
, SUP.ORDER_TYPE) AS SOURCE_VENDOR_SITE_CODE
, SUP.SUPPLIER_ID AS VENDOR_ID
, NULL AS SUPPLIER_NAME
, SUP.SUPPLIER_SITE_ID AS VENDOR_SITE_ID
, NULL AS SUPPLIER_SITE_CODE
, SUP.IMPLEMENT_SOURCE_ORG_ID AS IMPLEMENT_SOURCE_ORG_ID
, SUP.IMPLEMENT_SR_INSTANCE_ID AS IMPLEMENT_SR_INSTANCE_ID
, SUP.IMPLEMENT_SUPPLIER_ID AS IMPLEMENT_VENDOR_ID
, SUP.IMPLEMENT_SUPPLIER_SITE_ID AS IMPLEMENT_VENDOR_SITE_ID
, DECODE(MSI.IN_SOURCE_PLAN
, 1
, 1
, 2) AS IN_SOURCE_PLAN
, SUP.SCHEDULE_COMPRESS_DAYS AS SCHEDULE_COMPRESSION_DAYS
, SUP.PROJECT_ID AS PROJECT_ID
, SUP.TASK_ID AS TASK_ID
, DECODE(SUP.PROJECT_ID
, NULL
, NULL
, MSC_GET_NAME.PROJECT(SUP.PROJECT_ID
, SUP.ORGANIZATION_ID
, SUP.PLAN_ID
, SUP.SR_INSTANCE_ID)) AS PROJECT_NUMBER
, DECODE(SUP.TASK_ID
, NULL
, NULL
, MSC_GET_NAME.TASK(SUP.TASK_ID
, SUP.PROJECT_ID
, SUP.ORGANIZATION_ID
, SUP.PLAN_ID
, SUP.SR_INSTANCE_ID)) AS TASK_NUMBER
, SUP.IMPLEMENT_PROJECT_ID AS IMPLEMENT_PROJECT_ID
, SUP.IMPLEMENT_TASK_ID AS IMPLEMENT_TASK_ID
, SUP.PLANNING_GROUP AS PLANNING_GROUP
, SUP.STATUS AS STATUS
, SUP.APPLIED AS APPLIED
, MSI.RELEASE_TIME_FENCE_CODE AS RELEASE_TIME_FENCE_CODE
, SUP.IMPLEMENT_SCHEDULE_GROUP_ID AS IMPLEMENT_SCHEDULE_GROUP_ID
, SUP.IMPLEMENT_BUILD_SEQUENCE AS IMPLEMENT_BUILD_SEQUENCE
, DECODE(SUP.ORDER_TYPE
, 3
, SUP.BUILD_SEQUENCE
, 7
, SUP.BUILD_SEQUENCE
, 14
, SUP.BUILD_SEQUENCE
, 15
, SUP.BUILD_SEQUENCE
, 27
, SUP.BUILD_SEQUENCE
, TO_NUMBER( NULL)) AS BUILD_SEQUENCE
, DECODE(SUP.ORDER_TYPE
, 3
, SUP.SCHEDULE_GROUP_ID
, 7
, SUP.SCHEDULE_GROUP_ID
, 14
, SUP.SCHEDULE_GROUP_ID
, 15
, SUP.SCHEDULE_GROUP_ID
, 27
, SUP.SCHEDULE_GROUP_ID
, TO_NUMBER(NULL)) AS SCHEDULE_GROUP_ID
, DECODE(SUP.PLAN_ID
, -1
, SUP.ALTERNATE_BOM_DESIGNATOR
, NVL(SUP.ALTERNATE_BOM_DESIGNATOR
, MSC_GET_NAME.ALTERNATE_BOM(SUP.PLAN_ID
, SUP.SR_INSTANCE_ID
, NVL(PE.BILL_SEQUENCE_ID
, SUP.BILL_SEQUENCE_ID)))) AS ALTERNATE_BOM_DESIGNATOR
, DECODE(SUP.PLAN_ID
, -1
, SUP.ALTERNATE_ROUTING_DESIGNATOR
, NVL(SUP.ALTERNATE_ROUTING_DESIGNATOR
, MSC_GET_NAME.ALTERNATE_RTG(SUP.PLAN_ID
, SUP.SR_INSTANCE_ID
, NVL(PE.ROUTING_SEQUENCE_ID
, SUP.ROUTING_SEQUENCE_ID)))) AS ALTERNATE_ROUTING_DESIGNATOR
, SUP.PROCESS_SEQ_ID AS PROCESS_SEQ_ID
, SUP.IMPLEMENT_ALTERNATE_BOM AS IMPLEMENT_ALTERNATE_BOM
, SUP.IMPLEMENT_ALTERNATE_ROUTING AS IMPLEMENT_ALTERNATE_ROUTING
, SUP.SCHEDULE_GROUP_NAME AS SCHEDULE_GROUP_NAME
, SUP.IMPLEMENT_EMPLOYEE_ID AS IMPLEMENT_EMPLOYEE_ID
, MSC_GET_NAME.CFM_ROUTING_FLAG(PE.PLAN_ID
, PE.SR_INSTANCE_ID
, PE.ROUTING_SEQUENCE_ID) AS CFM_ROUTING_FLAG
, MSI.BUYER_NAME AS BUYER_NAME
, SUP.RELEASE_ERRORS AS RELEASE_ERRORS
, SUP.NUMBER1 AS REL_ALL_QTY
, SUP.UNIT_NUMBER AS UNIT_NUMBER
, SUP.IMPLEMENT_UNIT_NUMBER AS IMPLEMENT_UNIT_NUMBER
, MSI.DESCRIPTION AS DESCRIPTION
, NVL(SUP.PLANNING_GROUP
, '0') AS PLANNING_GROUP_HIDDEN
, /*NVL(MSI.BUYER_NAME
, '0') AS BUYER_NAME_HIDDEN
, */ NULL AS BUYER_NAME_HIDDEN
, NVL(MSI.PLANNER_CODE
, '0') AS PLANNER_CODE_HIDDEN
, NVL(SUP.PROJECT_ID
, 0) AS PROJECT_ID_HIDDEN
, NVL(SUP.TASK_ID
, 0) AS TASK_ID_HIDDEN
, NULL AS LENDING_PROJECT_NUMBER
, NULL AS LENDING_TASK_NUMBER
, MP.CATEGORY_SET_ID AS CATEGORY_SET_ID
, MSI.CATEGORY_NAME AS CATEGORY_NAME
, MSI.PRODUCT_FAMILY_ID AS PRODUCT_FAMILY_ID
, MSC_GET_NAME.ITEM_NAME(MSI.PRODUCT_FAMILY_ID
, NULL
, NULL
, NULL) AS PRODUCT_FAMILY_NAME
, /*MSC_GET_NAME.ITEM_NAME(MSI.BASE_ITEM_ID
, NULL
, NULL
, NULL) AS BASE_ITEM_NAME
, */ NULL AS BASE_ITEM_NAME
, MSI.ABC_CLASS_NAME AS ABC_CLASS
, MSI.UOM_CODE AS UOM_CODE
, /*DECODE(MP.PLAN_TYPE
, 5
, DECODE(SUP.ORDER_TYPE
, 2
, MSC_DRP_UTIL.GET_ISO_NAME(SUP.PLAN_ID
, SUP.SR_INSTANCE_ID
, SUP.TRANSACTION_ID)
, NULL)
, NULL) AS INTERNAL_SO_NUMBER
, */ NULL AS INTERNAL_SO_NUMBER
, DECODE(MP.PLAN_TYPE
, 5
, TO_NUMBER(NULL)
, SUP.SCHEDULE_PRIORITY) AS DEMAND_PRIORITY
, SUP.PROMISED_DATE AS PROMISE_DATE
, TO_DATE(NULL) AS REQUEST_DATE
, TO_NUMBER(NULL) AS CUSTOMER_ID
, NULL AS CUSTOMER_NAME
, TO_NUMBER(NULL) AS CUSTOMER_SITE_ID
, NULL AS CUSTOMER_SITE_NAME
, TO_NUMBER(NULL) AS SHIP_TO_SITE_ID
, NULL AS SHIP_TO_SITE_NAME
, SUP.LOT_NUMBER AS LOT_NUMBER
, SUP.SUBINVENTORY_CODE AS SUBINVENTORY_CODE
, SUP.PARENT_ID AS PARENT_ID
, SUP.WIP_STATUS_CODE AS WIP_STATUS_CODE
, MSC_GET_NAME.LOOKUP_MEANING('WIP_JOB_STATUS'
, SUP.WIP_STATUS_CODE) AS WIP_STATUS_TEXT
, TO_NUMBER(NULL) AS DMD_LATENESS_COST
, TO_DATE(NULL) AS DMD_SATISFIED_DATE
, SUP.NEED_BY_DATE AS NEED_BY_DATE
, DECODE(SUP.SCHEDULE_DESIGNATOR_ID
, NULL
, NULL
, MSC_GET_NAME.DESIGNATOR(SUP.SCHEDULE_DESIGNATOR_ID)) AS DESIGNATOR_NAME
, MSI.LIST_PRICE AS LIST_PRICE
, MSI.STANDARD_COST AS STANDARD_COST
, TO_NUMBER(NULL) AS SELLING_PRICE
, TO_NUMBER(NULL) AS SERVICE_LEVEL
, DECODE(SUP.DEMAND_CLASS
, '-1'
, NULL
, SUP.DEMAND_CLASS) AS DEMAND_CLASS
, TO_NUMBER(NULL) AS PROBABILITY
, SUP.NEW_SHIP_DATE AS SHIP_DATE
, NVL(SUP.DAILY_RATE
, SUP.NEW_ORDER_QUANTITY) AS QUANTITY
, TO_DATE(NULL) AS ASSEMBLY_DEMAND_COMP_DATE
, NULL AS BUCKET_TYPE
, TO_NUMBER(NULL) AS SOURCE_DMD_PRIORITY
, SUP.PURCH_LINE_NUM AS PO_LINE_ID
, SUP.RESCHEDULE_DAYS AS RESCHEDULE_DAYS
, DECODE(SUP.PLAN_ID
, -1
, SUP.NEW_DOCK_DATE
, SUP.OLD_NEED_BY_DATE) AS OLD_NEED_BY_DATE
, TO_NUMBER(NULL) AS CUMULATIVE_PROBABILITY
, TO_NUMBER(NULL) AS ORIGINAL_ITEM_ID
, NULL AS ORIGINAL_ITEM_NAME
, TO_NUMBER(NULL) AS ORIGINAL_ITEM_QTY
, SUP.SHIP_METHOD AS SHIP_METHOD
, SUP.EXPLOSION_DATE AS EXPLOSION_DATE
, TO_NUMBER(NULL) AS UNMET_QUANTITY
, SUP.ORIGINAL_QUANTITY AS ORIGINAL_QUANTITY
, SUP.ORIGINAL_NEED_BY_DATE AS ORIGINAL_NEED_BY_DATE
, SUP.EARLIEST_START_DATE AS EPST
, SUP.EARLIEST_COMPLETION_DATE AS EPCT
, SUP.MIN_START_DATE AS MIN_START_TIME
, MSI.LOW_LEVEL_CODE AS LOW_LEVEL_CODE
, ROUND(NVL(SUP.EARLIEST_COMPLETION_DATE-SUP.NEED_BY_DATE
, 0)
, 2) AS ORDERS_DAYS_LATE
, SUP.ULPSD AS ULPSD
, SUP.ULPCD AS ULPCD
, SUP.UEPSD AS UEPSD
, SUP.UEPCD AS UEPCD
, SUP.EACD AS EACD
, SUP.SUPPLY_IS_SHARED AS SUPPLY_IS_SHARED
, SUP.RECORD_SOURCE AS RECORD_SOURCE
, TO_NUMBER(NULL) AS FORWARD_DAYS
, TO_NUMBER(NULL) AS BACKWARD_DAYS
, SUP.WIP_START_QUANTITY AS WIP_START_QUANTITY
, SUP.EXPIRATION_DATE AS EXPIRATION_DATE
, MSC_GET_NAME.LOOKUP_MEANING('MRP_PLANNING_CODE'
, MSI.MRP_PLANNING_CODE) AS MRP_PLANNING_CODE_TEXT
, TO_NUMBER(NULL) AS ORIGINAL_ORG_ID
, TO_NUMBER(NULL) AS ORIGINAL_INST_ID
, /*DECODE(SUP.ORDER_TYPE
, 3
, DECODE( MSI.ROUNDING_CONTROL_TYPE
, 1
, CEIL(DECODE(SUP.IMPLEMENT_QUANTITY
, NULL
, SUP.NEW_ORDER_QUANTITY
, SUP.IMPLEMENT_QUANTITY)/MSC_REL_PLAN_PUB.GET_REV_CUM_YIELD( SUP.SR_INSTANCE_ID
, SUP.PLAN_ID
, SUP.PROCESS_SEQ_ID
, SUP.TRANSACTION_ID
, SUP.ORGANIZATION_ID))
, DECODE(SUP.IMPLEMENT_QUANTITY
, NULL
, SUP.NEW_ORDER_QUANTITY
, SUP.IMPLEMENT_QUANTITY)/MSC_REL_PLAN_PUB.GET_REV_CUM_YIELD( SUP.SR_INSTANCE_ID
, SUP.PLAN_ID
, SUP.PROCESS_SEQ_ID
, SUP.TRANSACTION_ID
, SUP.ORGANIZATION_ID))
, 5
, DECODE(MSI.PLANNING_MAKE_BUY_CODE
, 1
, DECODE(MSI.ROUNDING_CONTROL_TYPE
, 1
, CEIL(DECODE(SUP.IMPLEMENT_QUANTITY
, NULL
, SUP.NEW_ORDER_QUANTITY
, SUP.IMPLEMENT_QUANTITY)/ MSC_REL_PLAN_PUB.GET_REV_CUM_YIELD(SUP.SR_INSTANCE_ID
, SUP.PLAN_ID
, SUP.PROCESS_SEQ_ID
, SUP.TRANSACTION_ID
, SUP.ORGANIZATION_ID))
, DECODE(SUP.IMPLEMENT_QUANTITY
, NULL
, SUP.NEW_ORDER_QUANTITY
, SUP.IMPLEMENT_QUANTITY)/ MSC_REL_PLAN_PUB.GET_REV_CUM_YIELD(SUP.SR_INSTANCE_ID
, SUP.PLAN_ID
, SUP.PROCESS_SEQ_ID
, SUP.TRANSACTION_ID
, SUP.ORGANIZATION_ID))
, TO_NUMBER(NULL))
, TO_NUMBER(NULL)) AS START_QUANTITY
, */ TO_NUMBER(NULL) AS START_QUANTITY
, MSI.CRITICAL_COMPONENT_FLAG AS CRITICAL_COMPONENT_FLAG
, TO_NUMBER(NULL) AS ORDER_DATE_TYPE_CODE
, TO_CHAR(NULL) AS ORDER_DATE_TYPE_TEXT
, TO_DATE(NULL) AS SCHEDULE_ARRIVAL_DATE
, TO_DATE(NULL) AS LATEST_ACCEPTABLE_DATE
, TO_DATE(NULL) AS PLANNED_ARRIVAL_DATE
, SUP.SHIPMENT_ID AS SHIPMENT_ID
, SUP.SHIP_CALENDAR AS SHIP_CALENDAR
, SUP.RECEIVING_CALENDAR AS RECEIVING_CALENDAR
, SUP.INTRANSIT_CALENDAR AS INTRANSIT_CALENDAR
, SUP.INTRANSIT_LEAD_TIME AS INTRANSIT_LEAD_TIME
, SUP.IMPLEMENT_DOCK_DATE AS IMPLEMENT_DOCK_DATE
, SUP.IMPLEMENT_SHIP_DATE AS IMPLEMENT_SHIP_DATE
, TO_DATE(NULL) AS IMPLEMENT_ARRIVAL_DATE
, TO_DATE(NULL) AS REQUEST_SHIP_DATE
, TO_DATE(NULL) AS PROMISE_SHIP_DATE
, SUP.DAYS_LATE AS LATE_DAYS
, NULL AS RECOMMENDED
, TO_NUMBER(NULL) AS SUBSTITUTE_COMPONENT_RANK
, TO_NUMBER(NULL) AS COMPONENT_YIELD
, 'MSC_SUPPLIES' AS SOURCE_TABLE2
, NVL(DECODE(SUP.ORDER_TYPE
, 5
, MSC_UTIL.GET_VMI_FLAG ( SUP.PLAN_ID
, SUP.SR_INSTANCE_ID
, SUP.ORGANIZATION_ID
, SUP.INVENTORY_ITEM_ID
, SUP.SUPPLIER_ID
, SUP.SUPPLIER_SITE_ID)
, SUP.VMI_FLAG)
, 2) AS VMI_FLAG
, NVL(MSI.UNIT_WEIGHT
, 0)*NVL(SUP.FIRM_QUANTITY
, SUP.NEW_ORDER_QUANTITY) AS WEIGHT
, NVL(MSI.UNIT_VOLUME
, 0)*NVL(SUP.FIRM_QUANTITY
, SUP.NEW_ORDER_QUANTITY) AS VOLUME
, TO_DATE(NULL) AS SCHEDULE_SHIP_DATE
, NULL AS DEST_ORG_CODE
, TO_NUMBER(NULL) AS QTY_BY_DUE_DATE
, NULL AS SHIP_SET_NAME
, NULL AS ARRIVAL_SET_NAME
, NULL AS ORIG_ORG_CODE
, TO_NUMBER(NULL) AS ORIG_LEAD_TIME
, NULL AS ORIG_SHIP_METHOD
, TO_NUMBER(NULL) AS DEST_ORG_ID
, TO_NUMBER(NULL) AS DEST_INST_ID
, TO_NUMBER(NULL) AS IMPLEMENT_DEST_ORG_ID
, TO_NUMBER(NULL) AS IMPLEMENT_DEST_INST_ID
, NULL AS IMPLEMENT_DEST_ORG_CODE
, TO_DATE(NULL) AS HIDDEN_DOCK_DATE
, TO_NUMBER(NULL) AS FINAL_USE_PRIORITY
, TO_DATE(NULL) AS FINAL_USE_DATE
, MSC_GET_NAME.GET_ZONE_NAME(SUP.ZONE_ID
, SUP.SR_INSTANCE_ID) AS ZONE
, TO_NUMBER(NULL) AS ALLOCATION_QTY
, TO_NUMBER(NULL) AS FIRM_ALLOCATION_FLAG
, TO_DATE(NULL) AS ALLOCATION_DATE
, NULL AS SALES_ORDER_LINE_SPLIT
, /*DECODE(SUP.ORDER_TYPE
, 70
, MSC_GET_NAME.EAM_PARENT_WORK_ORDER (SUP.PLAN_ID
, SUP.SR_INSTANCE_ID
, SUP.TRANSACTION_ID)
, TO_NUMBER(NULL) ) AS PARENT_WORK_ORDER
, */ TO_NUMBER(NULL) AS PARENT_WORK_ORDER
, /*MSC_GET_NAME.ITEM_NAME (SUP.ASSET_ITEM_ID
, SUP.ORGANIZATION_ID
, SUP.PLAN_ID
, SUP.SR_INSTANCE_ID) AS ASSET_GROUP
, */ NULL AS ASSET_GROUP
, SUP.ASSET_SERIAL_NUMBER AS ASSET_NUMBER
, SUP.REQUESTED_START_DATE AS REQUESTED_START_DATE
, SUP.REQUESTED_COMPLETION_DATE AS REQUESTED_COMPLETION_DATE
, TO_NUMBER(NULL) AS FILL_KILL_FLAG
, MSI.LOTS_EXIST AS LOTS_EXIST
, SUP.IMPLEMENT_SHIP_METHOD AS IMPLEMENT_SHIP_METHOD
, SUP.ACTUAL_START_DATE AS ACTUAL_START_DATE
, TO_DATE(NULL) AS FIRM_DUE_DATE
, TO_DATE(NULL) AS IMPLEMENT_DUE_DATE
, TO_DATE(NULL) AS HIDDEN_DUE_DATE
, /*MSC_GET_NAME.IS_WITHIN_REL_TIME_FENCE(MP.PLAN_START_DATE
, NVL(SUP.NEW_ORDER_PLACEMENT_DATE
, SUP.FIRM_DATE)
, MSI.RELEASE_TIME_FENCE_CODE
, MSI.CUMULATIVE_TOTAL_LEAD_TIME
, MSI.CUM_MANUFACTURING_LEAD_TIME
, MSI.FULL_LEAD_TIME
, MSI.RELEASE_TIME_FENCE_DAYS) AS WITHIN_REL_TIME_FENCE
, */ TO_NUMBER(NULL) AS WITHIN_REL_TIME_FENCE
, (NVL(SUP.DAILY_RATE
, SUP.NEW_ORDER_QUANTITY) * MSI.STANDARD_COST) AMOUNT
, TO_NUMBER(NULL) AS SO_LINE_SPLIT
, TO_NUMBER(NULL) AS FORECAST_SET_ID
, TO_NUMBER(NULL) AS SCHEDULE_DESIGNATOR_ID
, SUP.DELIVERY_PRICE AS DELIVERY_PRICE
, TO_NUMBER(NULL) SALES_ORDER_LINE_ID
, TO_NUMBER(NULL) SHIP_SET_ID
, TO_NUMBER(NULL) ARRIVAL_SET_ID
, SUP.ITEM_TYPE_ID AS ITEM_TYPE_ID
, SUP.ITEM_TYPE_VALUE AS ITEM_TYPE_VALUE
, /* DECODE(MP.PLAN_TYPE
, 8
, MSC_GET_NAME.LOOKUP_MEANING('MSC_PART_CONDITION'
, SUP.ITEM_TYPE_VALUE)
, NULL) AS PART_CONDITION
, */ NULL AS PART_CONDITION
, SUP.FIRM_START_DATE AS FIRM_START_DATE
, SUP.BILL_SEQUENCE_ID AS BILL_SEQUENCE_ID
, SUP.ROUTING_SEQUENCE_ID AS ROUTING_SEQUENCE_ID
, TO_NUMBER(NULL) AS PREV_SUBST_ITEM_ID
, TO_NUMBER(NULL) AS PREV_SUBST_ORG_ID
, NULL AS PREV_SUBST_ITEM
, NULL AS PREV_SUBST_ORG
, SUP.OTM_ARRIVAL_DATE AS OTM_ARRIVAL_DATE
, TO_NUMBER(NULL) AS MIN_REM_SHELF_LIFE_DAYS
, MSC_GET_NAME.GET_ORDER_COMMENTS(SUP.PLAN_ID
, 'SUPPLY'
, SUP.TRANSACTION_ID) COMMENTS
, MFP2.DEMAND_ID AS PEGGED_DEMAND_ID
, MFP2.ALLOCATED_QUANTITY
, MFP2.PEGGING_ID
, MFP2.PREV_PEGGING_ID
, MFP2.END_PEGGING_ID
, MFP1.DEMAND_ID AS DOWN_LEVEL_DEMAND_ID
, MFP1.TRANSACTION_ID AS DOWN_LEVEL_TRANSACTION_ID
, MFP1.ORGANIZATION_ID AS DOWN_LEVEL_ORGANIZATION_ID
, MFP1.INVENTORY_ITEM_ID AS DOWN_LEVEL_INVENTORY_ITEM_ID
, MFP1.SR_INSTANCE_ID AS DOWN_LEVEL_SR_INSTANCE_ID
, MFP1.PEGGING_ID AS DOWN_LEVEL_PEGGING_ID
, MFP1.PREV_PEGGING_ID AS DOWN_LEVEL_PREV_PEGGING_ID
, MFP1.END_PEGGING_ID AS DOWN_LEVEL_END_PEGGING_ID
, SUP.LOAD_TYPE
, MSI.PLAN_ID BASE_PLAN_ID
, MSI.ROUNDING_CONTROL_TYPE
, SUP.OLD_DOCK_DATE
, MP.CURR_CUTOFF_DATE AS PLAN_CUTOFF_DATE
, MP.CURR_START_DATE AS PLAN_START_DATE
, TO_NUMBER(NULL) AS RESERVED_QUANTITY
, SUP.CTB_FLAG
, SUP.CTB_COMP_AVAIL_PERCENT
, SUP.RTB_ORDER_QTY_PERCENT
, SUP.CTB_EXPECTED_DATE
, SUP.POTENTIAL_RTB_PERCENT
, SUP.CTB_PRIORITY
, TO_DATE(NULL) AS REVISED_DMD_DATE
, TO_NUMBER(NULL) AS REVISED_DMD_PRIORITY
, NVL(MSC_GET_NAME.LOOKUP_MEANING1('SHIP_METHOD'
, SUP.SHIP_METHOD
, MSC_GET_NAME.GET_APPLICATION_ID('AU')
, 0)
, SUP.SHIP_METHOD) AS SHIP_METHOD_TEXT
, SUP.ORIG_FIRM_DATE AS ORIG_FIRM_DATE
, SUP.ORIG_FIRM_QUANTITY AS ORIG_FIRM_QUANTITY
FROM MSC_SUPPLIES SUP
, MFG_LOOKUPS L1
, MSC_SYSTEM_ITEMS MSI
, MSC_PLANS MP
, (SELECT MP1.PLAN_ID PLAN_ID
, PE1.SR_INSTANCE_ID
, PE1.ROUTING_SEQUENCE_ID
, PE1.BILL_SEQUENCE_ID
, PE1.PROCESS_SEQUENCE_ID
FROM MSC_PROCESS_EFFECTIVITY PE1
, MSC_PLANS MP1
WHERE PE1.PLAN_ID = NVL(MP1.BASE_PLAN_ID
, MP1.PLAN_ID) ) PE
, MSC_FULL_PEGGING MFP1
, MSC_FULL_PEGGING MFP2
WHERE PE.PLAN_ID(+) = SUP.PLAN_ID
AND PE.SR_INSTANCE_ID(+) = SUP.SR_INSTANCE_ID
AND PE.PROCESS_SEQUENCE_ID(+) = SUP.PROCESS_SEQ_ID
AND MSI.PLAN_ID=NVL(MP.BASE_PLAN_ID
, MP.PLAN_ID)
AND SUP.SR_INSTANCE_ID = MSI.SR_INSTANCE_ID
AND SUP.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND SUP.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND L1.LOOKUP_TYPE = 'MRP_ORDER_TYPE'
AND L1.LOOKUP_CODE = SUP.ORDER_TYPE
AND MP.PLAN_ID = SUP.PLAN_ID
AND MFP2.PLAN_ID = MP.PLAN_ID
AND MFP2.SR_INSTANCE_ID = MSI.SR_INSTANCE_ID
AND MFP2.ORGANIZATION_ID=MSI.ORGANIZATION_ID
AND MFP2.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MFP2.TRANSACTION_ID = SUP.TRANSACTION_ID
AND MFP2.PLAN_ID = MFP1.PLAN_ID
AND MFP2.SR_INSTANCE_ID = MFP1.SR_INSTANCE_ID
AND MFP2.PREV_PEGGING_ID = MFP1.PEGGING_ID
AND (MSI.NEW_PLAN_ID = -1 OR MSI.NEW_PLAN_ID IS NULL )
AND (MSI.SIMULATION_SET_ID IS NULL)