VIEW: APPS.MSD_AUTHORIZATION_DS_V

Source

View Text - Preformatted

SELECT MDS.DEMAND_PLAN_ID, PLN.plan_id, PLN.COMPILE_DESIGNATOR, org.level_pk , 7, PRD.LEVEL_PK , 1, GEO.level_pk , 50, 9 TIME_level_id, MSD_COMMON_UTILITIES.EFFEC_AUTH(decode(mad.start_days, 0, mdp.plan_start_date, mtw.start_date) , mtw.end_date,mad.supplier_id, mad.sr_instance_id,mad.organization_id,mad.inventory_item_id, mad.supplier_site_id, mdp.demand_plan_id), /* Gives weigted averaged authorization percentage */ mt.day, NULL, NULL, NULL, NULL, NULL, 1, 1, 'I', to_number(null), to_number(null) FROM MSC_PLANS PLN, msd_demand_plans mdp, msd_dp_session mds, msc_item_suppliers mis, MSC_SYSTEM_ITEMS MSI, msc_trading_partners mtp, msd_time mt, MSC_ASL_AUTH_DETAILS MAD, MSD_LEVEL_VALUES_DS PRD, MSD_LEVEL_VALUES_DS ORG, MSD_LEVEL_VALUES_DS GEO, (select distinct nvl(decode(to_char(mt.week_start_date,'MM'), to_char(mt.week_end_date,'MM'), mt.week_start_date, last_day(mt.week_start_date)+1), mt.month_start_date) start_date, /* Week start date or 1st day of next month if manufacturing calendar attached Otherwise Gregorian month start date */ nvl(mt.week_end_date,month_end_date) end_date from msd_time mt, (select mdc.calendar_code calendar_code from msd_dp_calendars mdc, msd_dp_session mds where mds.demand_plan_id=mdc.demand_plan_id and mdc.calendar_type=2 Union all select mdc.calendar_code calendar_code from msd_dp_calendars mdc, msd_dp_session mds where mds.demand_plan_id=mdc.demand_plan_id and mdc.calendar_type=1 and not exists( select 1 from msd_dp_calendars mdc, msd_dp_session mds where mds.demand_plan_id=mdc.demand_plan_id and mdc.calendar_type=2) ) mcal where mt.calendar_code=mcal.calendar_code union select distinct nvl(mt.week_start_date, month_start_date) start_date, nvl(decode(to_char(mt.week_start_date,'MM'), to_char(mt.week_end_date,'MM'),mt.week_end_date, last_day(mt.week_start_date)), month_end_date) end_date /* Week end date or last day of current month if manufacturing calendar attached Otherwise Gregorian month end date */ from msd_time mt, (select mdc.calendar_code calendar_code from msd_dp_calendars mdc, msd_dp_session mds where mds.demand_plan_id=mdc.demand_plan_id and mdc.calendar_type=2 Union all select mdc.calendar_code calendar_code from msd_dp_calendars mdc, msd_dp_session mds where mds.demand_plan_id=mdc.demand_plan_id and mdc.calendar_type=1 and not exists( select 1 from msd_dp_calendars mdc, msd_dp_session mds where mds.demand_plan_id=mdc.demand_plan_id and mdc.calendar_type=2) ) mcal where mt.calendar_code=mcal.calendar_code ) mtw WHERE PLN.PLAN_ID <> -1 AND MIS.PLAN_ID = PLN.PLAN_ID AND MSI.PLAN_ID=MIS.PLAN_ID AND MSI.SR_INSTANCE_ID = MIS.SR_INSTANCE_ID AND MSI.ORGANIZATION_ID = MIS.ORGANIZATION_ID AND MSI.INVENTORY_ITEM_ID = MIS.INVENTORY_ITEM_ID AND mad.INCLUDE_LIABILITY_AGREEMENT = 1 AND MAD.SUPPLIER_ID = MIS.SUPPLIER_ID AND MAD.SR_INSTANCE_ID = MIS.SR_INSTANCE_ID AND MAD.ORGANIZATION_ID = MIS.ORGANIZATION_ID AND MAD.INVENTORY_ITEM_ID = MIS.INVENTORY_ITEM_ID AND MAD.SUPPLIER_SITE_ID = MIS.SUPPLIER_SITE_ID AND MTP.PARTNER_ID=MIS.SUPPLIER_ID AND MTP.PARTNER_TYPE=1 AND PRD.LEVEL_ID = 1 AND PRD.SR_LEVEL_PK = to_char(MSI.SR_INVENTORY_ITEM_ID) AND PRD.INSTANCE=TO_CHAR(MSI.SR_INSTANCE_ID) AND PRD.DEMAND_PLAN_ID = MDS.DEMAND_PLAN_ID AND ORG.LEVEL_ID = 7 AND ORG.SR_LEVEL_PK = TO_CHAR(MIS.ORGANIZATION_ID) AND ORG.INSTANCE=TO_CHAR(MIS.SR_INSTANCE_ID) AND ORG.DEMAND_PLAN_ID =MDS.DEMAND_PLAN_ID AND GEO.LEVEL_ID = 50 AND GEO.SR_LEVEL_PK = TO_CHAR('1.'||MTP.SR_TP_ID) AND GEO.INSTANCE=TO_CHAR(MIS.SR_INSTANCE_ID) AND GEO.DEMAND_PLAN_ID =MDS.DEMAND_PLAN_ID AND MDP.DEMAND_PLAN_ID = MDS.DEMAND_PLAN_ID AND MT.CALENDAR_CODE='GREGORIAN'AND MT.CALENDAR_TYPE=1 AND MT.DAY BETWEEN trunc(MDP.PLAN_START_DATE+MAD.START_DAYS) AND TRUNC(MDP.PLAN_START_DATE+decode( MAD.END_DAYS ,( select max(mad1.end_days) from MSC_ASL_AUTH_DETAILS MAD1 where mad1.INCLUDE_LIABILITY_AGREEMENT = mad.INCLUDE_LIABILITY_AGREEMENT AND mad1.INCLUDE_LIABILITY_AGREEMENT =1 and MAD1.SUPPLIER_ID = MAD.SUPPLIER_ID AND MAD1.SR_INSTANCE_ID = MAD.SR_INSTANCE_ID AND MAD1.ORGANIZATION_ID = MAD.ORGANIZATION_ID AND MAD1.INVENTORY_ITEM_ID = MAD.INVENTORY_ITEM_ID AND MAD1.SUPPLIER_SITE_ID = MAD.SUPPLIER_SITE_ID), mad.end_days+6, mad.end_days)) AND MT.day = mtw.end_date UNION SELECT /* LEGACY DATA */ MDS.DEMAND_PLAN_ID, TO_NUMBER(NULL) PLAN_ID, MCD.CS_NAME CS_NAME, MLV_ORG.LEVEL_PK ORG_LEVEL_VALUE_PK, 7 ORG_LEVEL_ID, MLV_PRD.LEVEL_PK PRD_LEVEL_VALUE_PK, 1 PRD_LEVEL_ID, MLV_GEO.LEVEL_PK GEO_LEVEL_VALUE_PK, 50 GEO_LEVEL_ID, 9 TIME_LEVEL_ID, TO_NUMBER(MCD.ATTRIBUTE_41) QUANTITY, TO_DATE(MCD.ATTRIBUTE_43,'YYYY/MM/DD'), 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_DS MLV_ORG, MSD_LEVEL_VALUES_DS MLV_PRD, MSD_LEVEL_VALUES_DS MLV_GEO, MSD_DP_SESSION MDS WHERE MCD.CS_DEFINITION_ID = (SELECT CS_DEFINITION_ID FROM MSD_CS_DEFINITIONS WHERE NAME = 'MSD_AUTHORIZATION') AND MCD.ATTRIBUTE_10 = 7 AND MLV_ORG.LEVEL_ID = 7 AND MCD.ATTRIBUTE_11 = MLV_ORG.SR_LEVEL_PK AND MCD.ATTRIBUTE_1 = MLV_ORG.INSTANCE AND MLV_ORG.DEMAND_PLAN_ID =MDS.DEMAND_PLAN_ID AND MCD.ATTRIBUTE_2 = 1 AND MLV_PRD.LEVEL_ID = 1 AND MCD.ATTRIBUTE_3 = MLV_PRD.SR_LEVEL_PK AND MCD.ATTRIBUTE_1 = MLV_PRD.INSTANCE AND MLV_PRD.DEMAND_PLAN_ID =MDS.DEMAND_PLAN_ID AND MCD.ATTRIBUTE_6 = 50 AND MLV_GEO.LEVEL_ID = 50 AND MCD.ATTRIBUTE_7 = MLV_GEO.SR_LEVEL_PK AND MCD.ATTRIBUTE_1 = MLV_GEO.INSTANCE AND MLV_GEO.DEMAND_PLAN_ID =MDS.DEMAND_PLAN_ID

View Text - HTML Formatted

SELECT MDS.DEMAND_PLAN_ID
, PLN.PLAN_ID
, PLN.COMPILE_DESIGNATOR
, ORG.LEVEL_PK
, 7
, PRD.LEVEL_PK
, 1
, GEO.LEVEL_PK
, 50
, 9 TIME_LEVEL_ID
, MSD_COMMON_UTILITIES.EFFEC_AUTH(DECODE(MAD.START_DAYS
, 0
, MDP.PLAN_START_DATE
, MTW.START_DATE)
, MTW.END_DATE
, MAD.SUPPLIER_ID
, MAD.SR_INSTANCE_ID
, MAD.ORGANIZATION_ID
, MAD.INVENTORY_ITEM_ID
, MAD.SUPPLIER_SITE_ID
, MDP.DEMAND_PLAN_ID)
, /* GIVES WEIGTED AVERAGED AUTHORIZATION PERCENTAGE */ MT.DAY
, NULL
, NULL
, NULL
, NULL
, NULL
, 1
, 1
, 'I'
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
FROM MSC_PLANS PLN
, MSD_DEMAND_PLANS MDP
, MSD_DP_SESSION MDS
, MSC_ITEM_SUPPLIERS MIS
, MSC_SYSTEM_ITEMS MSI
, MSC_TRADING_PARTNERS MTP
, MSD_TIME MT
, MSC_ASL_AUTH_DETAILS MAD
, MSD_LEVEL_VALUES_DS PRD
, MSD_LEVEL_VALUES_DS ORG
, MSD_LEVEL_VALUES_DS GEO
, (SELECT DISTINCT NVL(DECODE(TO_CHAR(MT.WEEK_START_DATE
, 'MM')
, TO_CHAR(MT.WEEK_END_DATE
, 'MM')
, MT.WEEK_START_DATE
, LAST_DAY(MT.WEEK_START_DATE)+1)
, MT.MONTH_START_DATE) START_DATE
, /* WEEK START DATE OR 1ST DAY OF NEXT MONTH IF MANUFACTURING CALENDAR ATTACHED OTHERWISE GREGORIAN MONTH START DATE */ NVL(MT.WEEK_END_DATE
, MONTH_END_DATE) END_DATE
FROM MSD_TIME MT
, (SELECT MDC.CALENDAR_CODE CALENDAR_CODE
FROM MSD_DP_CALENDARS MDC
, MSD_DP_SESSION MDS
WHERE MDS.DEMAND_PLAN_ID=MDC.DEMAND_PLAN_ID
AND MDC.CALENDAR_TYPE=2 UNION ALL SELECT MDC.CALENDAR_CODE CALENDAR_CODE
FROM MSD_DP_CALENDARS MDC
, MSD_DP_SESSION MDS
WHERE MDS.DEMAND_PLAN_ID=MDC.DEMAND_PLAN_ID
AND MDC.CALENDAR_TYPE=1
AND NOT EXISTS( SELECT 1
FROM MSD_DP_CALENDARS MDC
, MSD_DP_SESSION MDS
WHERE MDS.DEMAND_PLAN_ID=MDC.DEMAND_PLAN_ID
AND MDC.CALENDAR_TYPE=2) ) MCAL
WHERE MT.CALENDAR_CODE=MCAL.CALENDAR_CODE UNION SELECT DISTINCT NVL(MT.WEEK_START_DATE
, MONTH_START_DATE) START_DATE
, NVL(DECODE(TO_CHAR(MT.WEEK_START_DATE
, 'MM')
, TO_CHAR(MT.WEEK_END_DATE
, 'MM')
, MT.WEEK_END_DATE
, LAST_DAY(MT.WEEK_START_DATE))
, MONTH_END_DATE) END_DATE /* WEEK END DATE OR LAST DAY OF CURRENT MONTH IF MANUFACTURING CALENDAR ATTACHED OTHERWISE GREGORIAN MONTH END DATE */
FROM MSD_TIME MT
, (SELECT MDC.CALENDAR_CODE CALENDAR_CODE
FROM MSD_DP_CALENDARS MDC
, MSD_DP_SESSION MDS
WHERE MDS.DEMAND_PLAN_ID=MDC.DEMAND_PLAN_ID
AND MDC.CALENDAR_TYPE=2 UNION ALL SELECT MDC.CALENDAR_CODE CALENDAR_CODE
FROM MSD_DP_CALENDARS MDC
, MSD_DP_SESSION MDS
WHERE MDS.DEMAND_PLAN_ID=MDC.DEMAND_PLAN_ID
AND MDC.CALENDAR_TYPE=1
AND NOT EXISTS( SELECT 1
FROM MSD_DP_CALENDARS MDC
, MSD_DP_SESSION MDS
WHERE MDS.DEMAND_PLAN_ID=MDC.DEMAND_PLAN_ID
AND MDC.CALENDAR_TYPE=2) ) MCAL
WHERE MT.CALENDAR_CODE=MCAL.CALENDAR_CODE ) MTW
WHERE PLN.PLAN_ID <> -1
AND MIS.PLAN_ID = PLN.PLAN_ID
AND MSI.PLAN_ID=MIS.PLAN_ID
AND MSI.SR_INSTANCE_ID = MIS.SR_INSTANCE_ID
AND MSI.ORGANIZATION_ID = MIS.ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID = MIS.INVENTORY_ITEM_ID
AND MAD.INCLUDE_LIABILITY_AGREEMENT = 1
AND MAD.SUPPLIER_ID = MIS.SUPPLIER_ID
AND MAD.SR_INSTANCE_ID = MIS.SR_INSTANCE_ID
AND MAD.ORGANIZATION_ID = MIS.ORGANIZATION_ID
AND MAD.INVENTORY_ITEM_ID = MIS.INVENTORY_ITEM_ID
AND MAD.SUPPLIER_SITE_ID = MIS.SUPPLIER_SITE_ID
AND MTP.PARTNER_ID=MIS.SUPPLIER_ID
AND MTP.PARTNER_TYPE=1
AND PRD.LEVEL_ID = 1
AND PRD.SR_LEVEL_PK = TO_CHAR(MSI.SR_INVENTORY_ITEM_ID)
AND PRD.INSTANCE=TO_CHAR(MSI.SR_INSTANCE_ID)
AND PRD.DEMAND_PLAN_ID = MDS.DEMAND_PLAN_ID
AND ORG.LEVEL_ID = 7
AND ORG.SR_LEVEL_PK = TO_CHAR(MIS.ORGANIZATION_ID)
AND ORG.INSTANCE=TO_CHAR(MIS.SR_INSTANCE_ID)
AND ORG.DEMAND_PLAN_ID =MDS.DEMAND_PLAN_ID
AND GEO.LEVEL_ID = 50
AND GEO.SR_LEVEL_PK = TO_CHAR('1.'||MTP.SR_TP_ID)
AND GEO.INSTANCE=TO_CHAR(MIS.SR_INSTANCE_ID)
AND GEO.DEMAND_PLAN_ID =MDS.DEMAND_PLAN_ID
AND MDP.DEMAND_PLAN_ID = MDS.DEMAND_PLAN_ID
AND MT.CALENDAR_CODE='GREGORIAN'AND MT.CALENDAR_TYPE=1
AND MT.DAY BETWEEN TRUNC(MDP.PLAN_START_DATE+MAD.START_DAYS)
AND TRUNC(MDP.PLAN_START_DATE+DECODE( MAD.END_DAYS
, ( SELECT MAX(MAD1.END_DAYS)
FROM MSC_ASL_AUTH_DETAILS MAD1
WHERE MAD1.INCLUDE_LIABILITY_AGREEMENT = MAD.INCLUDE_LIABILITY_AGREEMENT
AND MAD1.INCLUDE_LIABILITY_AGREEMENT =1
AND MAD1.SUPPLIER_ID = MAD.SUPPLIER_ID
AND MAD1.SR_INSTANCE_ID = MAD.SR_INSTANCE_ID
AND MAD1.ORGANIZATION_ID = MAD.ORGANIZATION_ID
AND MAD1.INVENTORY_ITEM_ID = MAD.INVENTORY_ITEM_ID
AND MAD1.SUPPLIER_SITE_ID = MAD.SUPPLIER_SITE_ID)
, MAD.END_DAYS+6
, MAD.END_DAYS))
AND MT.DAY = MTW.END_DATE UNION SELECT /* LEGACY DATA */ MDS.DEMAND_PLAN_ID
, TO_NUMBER(NULL) PLAN_ID
, MCD.CS_NAME CS_NAME
, MLV_ORG.LEVEL_PK ORG_LEVEL_VALUE_PK
, 7 ORG_LEVEL_ID
, MLV_PRD.LEVEL_PK PRD_LEVEL_VALUE_PK
, 1 PRD_LEVEL_ID
, MLV_GEO.LEVEL_PK GEO_LEVEL_VALUE_PK
, 50 GEO_LEVEL_ID
, 9 TIME_LEVEL_ID
, TO_NUMBER(MCD.ATTRIBUTE_41) QUANTITY
, TO_DATE(MCD.ATTRIBUTE_43
, 'YYYY/MM/DD')
, 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_DS MLV_ORG
, MSD_LEVEL_VALUES_DS MLV_PRD
, MSD_LEVEL_VALUES_DS MLV_GEO
, MSD_DP_SESSION MDS
WHERE MCD.CS_DEFINITION_ID = (SELECT CS_DEFINITION_ID
FROM MSD_CS_DEFINITIONS
WHERE NAME = 'MSD_AUTHORIZATION')
AND MCD.ATTRIBUTE_10 = 7
AND MLV_ORG.LEVEL_ID = 7
AND MCD.ATTRIBUTE_11 = MLV_ORG.SR_LEVEL_PK
AND MCD.ATTRIBUTE_1 = MLV_ORG.INSTANCE
AND MLV_ORG.DEMAND_PLAN_ID =MDS.DEMAND_PLAN_ID
AND MCD.ATTRIBUTE_2 = 1
AND MLV_PRD.LEVEL_ID = 1
AND MCD.ATTRIBUTE_3 = MLV_PRD.SR_LEVEL_PK
AND MCD.ATTRIBUTE_1 = MLV_PRD.INSTANCE
AND MLV_PRD.DEMAND_PLAN_ID =MDS.DEMAND_PLAN_ID
AND MCD.ATTRIBUTE_6 = 50
AND MLV_GEO.LEVEL_ID = 50
AND MCD.ATTRIBUTE_7 = MLV_GEO.SR_LEVEL_PK
AND MCD.ATTRIBUTE_1 = MLV_GEO.INSTANCE
AND MLV_GEO.DEMAND_PLAN_ID =MDS.DEMAND_PLAN_ID