VIEW: APPS.MSD_AVAIL_SUPPLIER_CAPACITY_V

Source

View Text - Preformatted

SELECT sup.plan_id plan_id, plans.compile_designator cs_name, d_org.level_pk org_level_value_pk, d_org.level_id org_level_id, d_itm.level_pk prd_level_value_pk, d_itm.level_id prd_level_id, d_geo.level_pk geo_level_value_pk, d_geo.level_id geo_level_id, msd_common_utilities.get_dcs_key(sup.sr_instance_id,'-6',null,40) dcs_level_value_pk, 40 dcs_level_id, 9 time_level_id, sup.capacity quantity, cal.calendar_date schdule_date, sup.creation_date, sup.created_by, sup.last_update_date, sup.last_updated_by, sup.last_update_login, 1, 1, 'I', to_number(null), to_number(null) FROM MSC_SUPPLIER_CAPACITIES sup, msc_plans plans, msc_system_items msi, msc_calendar_dates cal, msd_level_values d_itm, msd_level_values d_org, msd_level_values d_geo, msc_tp_site_id_lid ste, msc_trading_partners mtp WHERE sup.plan_id = plans.plan_id and mtp.sr_instance_id = sup.sr_instance_id and mtp.sr_tp_id = sup.organization_id and mtp.partner_type = 3 and ste.tp_site_id = sup.supplier_site_id and ste.sr_instance_id = sup.sr_instance_id and ste.operating_unit = mtp.operating_unit and ste.sr_company_id = -1 and ste.partner_type = 1 and sup.sr_instance_id = cal.sr_instance_id and cal.calendar_code = MSD_COMMON_UTILITIES.get_supplier_calendar(sup.plan_id, sup.sr_instance_id, sup.organization_id, sup.inventory_item_id, sup.supplier_id, sup.supplier_site_id, sup.using_organization_id) and cal.exception_set_id = -1 and cal.calendar_date between sup.from_date and nvl(sup.to_date, plans.CURR_CUTOFF_DATE) and cal.seq_num is not null and sup.organization_id = msi.organization_id and sup.inventory_item_id = msi.inventory_item_id and sup.plan_id = msi.plan_id and sup.sr_instance_id = msi.sr_instance_id and msi.mrp_planning_code <> 6 and msi.planning_make_buy_code = 2 and msi.critical_component_flag = 1 and d_itm.sr_level_pk = to_char(msi.sr_inventory_item_id) and d_itm.instance = to_char(msi.sr_instance_id) and d_itm.level_id = 1 and d_org.sr_level_pk = to_char(sup.organization_id) and d_org.instance = to_char(sup.sr_instance_id) and d_org.level_id = 7 and d_geo.sr_level_pk = to_char('1.'||ste.sr_tp_site_id) and d_geo.instance = to_char(sup.sr_instance_id) and d_geo.level_id = 11 and sup.plan_id <> -1 UNION ALL SELECT to_number(null) plan_id, cs_name cs_name, d_org.level_pk org_level_value_pk, 7 org_level_id, d_prd.level_pk prd_level_value_pk, 1 prd_level_id, d_geo.level_pk geo_level_value_pk, 11 geo_level_id, msd_common_utilities.get_dcs_key(mcd.attribute_1,'-6', null,40) dcs_level_value_pk, 40 dcs_level_id, 9 time_level_id, to_number(mcd.attribute_41) quantity, to_date(mcd.attribute_43, 'yyyy/mm/dd') schedule_date, mcd.creation_date creation_date, mcd.created_by created_by, mcd.last_update_date last_update_date, mcd.last_updated_by last_updated_by, mcd.last_update_login last_update_login, mcd.last_refresh_num last_refresh_num, mcd.created_by_refresh_num created_by_refresh_num, mcd.action_code action_code, to_number(null) prd_parent_level_id, to_number(null) prd_parent_level_value_pk FROM msd_cs_data mcd, msd_level_values d_org, msd_level_values d_prd, msd_level_values d_geo WHERE mcd.cs_definition_id = (select cs_definition_id from msd_cs_definitions where name = 'MSD_AVAIL_SUPPLIER_CAPACITY') and mcd.attribute_11 = d_org.sr_level_pk and mcd.attribute_10 = 7 and d_org.level_id = 7 and mcd.attribute_1 = d_org.instance and mcd.attribute_3 = d_prd.sr_level_pk and mcd.attribute_2 = 1 and d_prd.level_id = 1 and mcd.attribute_1 = d_prd.instance and mcd.attribute_7 = d_geo.sr_level_pk and mcd.attribute_6 = 11 and d_geo.level_id = 11 and mcd.attribute_1 = d_geo.instance

View Text - HTML Formatted

SELECT SUP.PLAN_ID PLAN_ID
, PLANS.COMPILE_DESIGNATOR CS_NAME
, D_ORG.LEVEL_PK ORG_LEVEL_VALUE_PK
, D_ORG.LEVEL_ID ORG_LEVEL_ID
, D_ITM.LEVEL_PK PRD_LEVEL_VALUE_PK
, D_ITM.LEVEL_ID PRD_LEVEL_ID
, D_GEO.LEVEL_PK GEO_LEVEL_VALUE_PK
, D_GEO.LEVEL_ID GEO_LEVEL_ID
, MSD_COMMON_UTILITIES.GET_DCS_KEY(SUP.SR_INSTANCE_ID
, '-6'
, NULL
, 40) DCS_LEVEL_VALUE_PK
, 40 DCS_LEVEL_ID
, 9 TIME_LEVEL_ID
, SUP.CAPACITY QUANTITY
, CAL.CALENDAR_DATE SCHDULE_DATE
, SUP.CREATION_DATE
, SUP.CREATED_BY
, SUP.LAST_UPDATE_DATE
, SUP.LAST_UPDATED_BY
, SUP.LAST_UPDATE_LOGIN
, 1
, 1
, 'I'
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
FROM MSC_SUPPLIER_CAPACITIES SUP
, MSC_PLANS PLANS
, MSC_SYSTEM_ITEMS MSI
, MSC_CALENDAR_DATES CAL
, MSD_LEVEL_VALUES D_ITM
, MSD_LEVEL_VALUES D_ORG
, MSD_LEVEL_VALUES D_GEO
, MSC_TP_SITE_ID_LID STE
, MSC_TRADING_PARTNERS MTP
WHERE SUP.PLAN_ID = PLANS.PLAN_ID
AND MTP.SR_INSTANCE_ID = SUP.SR_INSTANCE_ID
AND MTP.SR_TP_ID = SUP.ORGANIZATION_ID
AND MTP.PARTNER_TYPE = 3
AND STE.TP_SITE_ID = SUP.SUPPLIER_SITE_ID
AND STE.SR_INSTANCE_ID = SUP.SR_INSTANCE_ID
AND STE.OPERATING_UNIT = MTP.OPERATING_UNIT
AND STE.SR_COMPANY_ID = -1
AND STE.PARTNER_TYPE = 1
AND SUP.SR_INSTANCE_ID = CAL.SR_INSTANCE_ID
AND CAL.CALENDAR_CODE = MSD_COMMON_UTILITIES.GET_SUPPLIER_CALENDAR(SUP.PLAN_ID
, SUP.SR_INSTANCE_ID
, SUP.ORGANIZATION_ID
, SUP.INVENTORY_ITEM_ID
, SUP.SUPPLIER_ID
, SUP.SUPPLIER_SITE_ID
, SUP.USING_ORGANIZATION_ID)
AND CAL.EXCEPTION_SET_ID = -1
AND CAL.CALENDAR_DATE BETWEEN SUP.FROM_DATE
AND NVL(SUP.TO_DATE
, PLANS.CURR_CUTOFF_DATE)
AND CAL.SEQ_NUM IS NOT NULL
AND SUP.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND SUP.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND SUP.PLAN_ID = MSI.PLAN_ID
AND SUP.SR_INSTANCE_ID = MSI.SR_INSTANCE_ID
AND MSI.MRP_PLANNING_CODE <> 6
AND MSI.PLANNING_MAKE_BUY_CODE = 2
AND MSI.CRITICAL_COMPONENT_FLAG = 1
AND D_ITM.SR_LEVEL_PK = TO_CHAR(MSI.SR_INVENTORY_ITEM_ID)
AND D_ITM.INSTANCE = TO_CHAR(MSI.SR_INSTANCE_ID)
AND D_ITM.LEVEL_ID = 1
AND D_ORG.SR_LEVEL_PK = TO_CHAR(SUP.ORGANIZATION_ID)
AND D_ORG.INSTANCE = TO_CHAR(SUP.SR_INSTANCE_ID)
AND D_ORG.LEVEL_ID = 7
AND D_GEO.SR_LEVEL_PK = TO_CHAR('1.'||STE.SR_TP_SITE_ID)
AND D_GEO.INSTANCE = TO_CHAR(SUP.SR_INSTANCE_ID)
AND D_GEO.LEVEL_ID = 11
AND SUP.PLAN_ID <> -1 UNION ALL SELECT TO_NUMBER(NULL) PLAN_ID
, CS_NAME CS_NAME
, D_ORG.LEVEL_PK ORG_LEVEL_VALUE_PK
, 7 ORG_LEVEL_ID
, D_PRD.LEVEL_PK PRD_LEVEL_VALUE_PK
, 1 PRD_LEVEL_ID
, D_GEO.LEVEL_PK GEO_LEVEL_VALUE_PK
, 11 GEO_LEVEL_ID
, MSD_COMMON_UTILITIES.GET_DCS_KEY(MCD.ATTRIBUTE_1
, '-6'
, NULL
, 40) DCS_LEVEL_VALUE_PK
, 40 DCS_LEVEL_ID
, 9 TIME_LEVEL_ID
, TO_NUMBER(MCD.ATTRIBUTE_41) QUANTITY
, TO_DATE(MCD.ATTRIBUTE_43
, 'YYYY/MM/DD') SCHEDULE_DATE
, MCD.CREATION_DATE CREATION_DATE
, MCD.CREATED_BY CREATED_BY
, MCD.LAST_UPDATE_DATE LAST_UPDATE_DATE
, MCD.LAST_UPDATED_BY LAST_UPDATED_BY
, MCD.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, MCD.LAST_REFRESH_NUM LAST_REFRESH_NUM
, MCD.CREATED_BY_REFRESH_NUM CREATED_BY_REFRESH_NUM
, MCD.ACTION_CODE ACTION_CODE
, TO_NUMBER(NULL) PRD_PARENT_LEVEL_ID
, TO_NUMBER(NULL) PRD_PARENT_LEVEL_VALUE_PK
FROM MSD_CS_DATA MCD
, MSD_LEVEL_VALUES D_ORG
, MSD_LEVEL_VALUES D_PRD
, MSD_LEVEL_VALUES D_GEO
WHERE MCD.CS_DEFINITION_ID = (SELECT CS_DEFINITION_ID
FROM MSD_CS_DEFINITIONS
WHERE NAME = 'MSD_AVAIL_SUPPLIER_CAPACITY')
AND MCD.ATTRIBUTE_11 = D_ORG.SR_LEVEL_PK
AND MCD.ATTRIBUTE_10 = 7
AND D_ORG.LEVEL_ID = 7
AND MCD.ATTRIBUTE_1 = D_ORG.INSTANCE
AND MCD.ATTRIBUTE_3 = D_PRD.SR_LEVEL_PK
AND MCD.ATTRIBUTE_2 = 1
AND D_PRD.LEVEL_ID = 1
AND MCD.ATTRIBUTE_1 = D_PRD.INSTANCE
AND MCD.ATTRIBUTE_7 = D_GEO.SR_LEVEL_PK
AND MCD.ATTRIBUTE_6 = 11
AND D_GEO.LEVEL_ID = 11
AND MCD.ATTRIBUTE_1 = D_GEO.INSTANCE