VIEW: APPS.MSD_LB_FCST_LIAB_CS_V

Source

View Text - Preformatted

SELECT mdp.liab_plan_name, 7, org.level_pk, 1, prd.level_pk, 12, geo.level_pk, 34, dcs.level_pk, 9, sup.key_date, (sup.primary_quantity * mlah.percentage_purchase_price)/100, 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 msd_level_values_lb prd, msd_level_values_lb geo, msd_level_values_lb dcs, msd_level_values_lb org, msc_sup_dem_entries sup, msc_company_relationships mcr_sup, msc_trading_partner_maps map_sup, msc_trading_partner_maps map_org, msc_asl_auth_details mlah, msc_trading_partners mtp, (select plan_start_date, liab_plan_name from msd_demand_plans where liab_plan_id = -1) mdp WHERE sup.plan_id = -1 /* Product Mapping */ AND prd.plan_id = -1 AND prd.level_id = 1 AND prd.sr_level_pk = sup.inventory_item_id /* Supplier Mapping */ AND sup.supplier_id = mcr_sup.object_id AND mcr_sup.subject_id = 1 AND mcr_sup.relationship_type = 2 AND mcr_sup.relationship_id = map_sup.company_key AND map_sup.map_type = 1 AND geo.plan_id = -1 AND geo.level_id = 12 AND geo.sr_level_pk = map_sup.tp_key /* Org Mapping */ AND sup.customer_site_id = map_org.company_key AND map_org.map_type = 2 AND mtp.partner_id = map_org.tp_key AND org.plan_id = -1 AND org.level_id = 7 AND org.sr_level_pk = mtp.sr_instance_id||':'||mtp.sr_tp_id AND mtp.partner_type = 3 /* Agreement Details Mapping */ AND mlah.plan_id = sup.plan_id AND mlah.inventory_item_id = sup.inventory_item_id AND mlah.organization_id = mtp.sr_tp_id AND mlah.sr_instance_id = mtp.sr_instance_id AND mlah.supplier_id = map_sup.tp_key /* Order Forecast - Planned Orders * OR * Supply Commit - Supply Commit */ AND (((mlah.ASL_LIABILITY_AGREEMENT_BASIS = 1) and (sup.publisher_order_type = 24)) OR ((mlah.ASL_LIABILITY_AGREEMENT_BASIS = 2) and (sup.publisher_order_type = 3))) AND nvl(mlah.INCLUDE_LIABILITY_AGREEMENT,2) = 1 /* Authorization Code Manpping */ AND dcs.plan_id = -1 AND dcs.level_id = 34 AND dcs.sr_level_pk = mlah.authorization_code AND sup.key_date >= (mdp.plan_start_date + mlah.start_days) and sup.key_date < (mdp.plan_start_date + mlah.end_days) UNION ALL /* PDS Data - Planned Orders */ SELECT mpln.compile_designator, 7, org.level_pk, 1, prd.level_pk, 12, geo.level_pk, 34, dcs.level_pk, 9, ms.new_schedule_date, (mad.percentage_purchase_price * ms.new_order_quantity)/100, ms.creation_date, ms.created_by, ms.last_update_date, ms.last_updated_by, ms.last_update_login, 1, 1, 'I', to_number(null), to_number(null) FROM msc_supplies ms, msd_demand_plans mdp, msc_plans mpln, msd_level_values_lb prd, msd_level_values_lb geo, msd_level_values_lb dcs, msd_level_values_lb org, msc_trading_partners mtp, msc_asl_auth_details mad WHERE mpln.plan_id <> -1 AND prd.plan_id = ms.plan_id AND prd.level_id = 1 AND prd.sr_level_pk = ms.inventory_item_id AND org.plan_id = ms.plan_id AND org.level_id = 7 AND mtp.partner_type = 3 AND mtp.sr_tp_id = ms.organization_id AND mtp.sr_instance_id = ms.sr_instance_id AND org.sr_level_pk = mtp.sr_instance_id||':'||mtp.sr_tp_id AND geo.plan_id = ms.plan_id AND geo.level_id = 12 AND geo.sr_level_pk = ms.supplier_id AND dcs.plan_id = ms.plan_id AND dcs.level_id = 34 AND ms.order_type = 5 AND mad.ASL_LIABILITY_AGREEMENT_BASIS = 1 AND nvl(mad.INCLUDE_LIABILITY_AGREEMENT,2) = 1 AND mad.inventory_item_id = ms.inventory_item_id and mad.organization_id = ms.organization_id and mad.supplier_id = ms.supplier_id and mad.sr_instance_id = ms.sr_instance_id and dcs.sr_level_pk = mad.authorization_code and mdp.liab_plan_id = mpln.plan_id and ms.new_schedule_date >= ( mdp.plan_start_date + mad.start_days) and ms.new_schedule_date < (mdp.plan_start_date + mad.end_days) and ms.plan_id = mpln.plan_id UNION ALL /* PDS Data - Supplier Capacity */ SELECT mpln.compile_designator, 7, org.level_pk, 1, prd.level_pk, 12, geo.level_pk, 34, dcs.level_pk, 9, mt.day, (mad.percentage_purchase_price * ms.capacity)/100, ms.creation_date, ms.created_by, ms.last_update_date, ms.last_updated_by, ms.last_update_login, 1, 1, 'I', to_number(null), to_number(null) FROM msc_supplier_capacities ms, msd_time mt, msc_plans mpln, msd_level_values_lb prd, msd_level_values_lb geo, msd_level_values_lb dcs, msd_level_values_lb org, msc_trading_partners mtp, msc_asl_auth_details mad WHERE mpln.plan_id <> -1 AND prd.plan_id = ms.plan_id AND prd.level_id = 1 AND prd.sr_level_pk = ms.inventory_item_id AND org.plan_id = ms.plan_id AND org.level_id = 7 AND ms.sr_instance_id = mtp.sr_instance_id AND mtp.partner_type = 3 AND ms.organization_id = mtp.sr_tp_id AND org.sr_level_pk = mtp.sr_instance_id||':'||mtp.sr_tp_id AND geo.plan_id = ms.plan_id AND geo.level_id = 12 AND geo.sr_level_pk = ms.supplier_id AND dcs.plan_id = ms.plan_id AND dcs.level_id = 34 AND ms.sr_instance_id = mad.sr_instance_id AND ms.organization_id = mad.organization_id AND ms.inventory_item_id = mad.inventory_item_id AND ms.supplier_id = mad.supplier_id AND mad.ASL_LIABILITY_AGREEMENT_BASIS = 2 AND nvl(mad.INCLUDE_LIABILITY_AGREEMENT,2) = 1 and mad.authorization_code = dcs.sr_level_pk and mt.calendar_type = 1 and mt.calendar_code = 'GREGORIAN' and mt.day between ms.from_date and ms.to_date and mt.day >= mpln.plan_start_date + mad.start_days and mt.day < mpln.plan_start_date + mad.end_days and ms.plan_id = mpln.plan_id

View Text - HTML Formatted

SELECT MDP.LIAB_PLAN_NAME
, 7
, ORG.LEVEL_PK
, 1
, PRD.LEVEL_PK
, 12
, GEO.LEVEL_PK
, 34
, DCS.LEVEL_PK
, 9
, SUP.KEY_DATE
, (SUP.PRIMARY_QUANTITY * MLAH.PERCENTAGE_PURCHASE_PRICE)/100
, 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 MSD_LEVEL_VALUES_LB PRD
, MSD_LEVEL_VALUES_LB GEO
, MSD_LEVEL_VALUES_LB DCS
, MSD_LEVEL_VALUES_LB ORG
, MSC_SUP_DEM_ENTRIES SUP
, MSC_COMPANY_RELATIONSHIPS MCR_SUP
, MSC_TRADING_PARTNER_MAPS MAP_SUP
, MSC_TRADING_PARTNER_MAPS MAP_ORG
, MSC_ASL_AUTH_DETAILS MLAH
, MSC_TRADING_PARTNERS MTP
, (SELECT PLAN_START_DATE
, LIAB_PLAN_NAME
FROM MSD_DEMAND_PLANS
WHERE LIAB_PLAN_ID = -1) MDP
WHERE SUP.PLAN_ID = -1 /* PRODUCT MAPPING */
AND PRD.PLAN_ID = -1
AND PRD.LEVEL_ID = 1
AND PRD.SR_LEVEL_PK = SUP.INVENTORY_ITEM_ID /* SUPPLIER MAPPING */
AND SUP.SUPPLIER_ID = MCR_SUP.OBJECT_ID
AND MCR_SUP.SUBJECT_ID = 1
AND MCR_SUP.RELATIONSHIP_TYPE = 2
AND MCR_SUP.RELATIONSHIP_ID = MAP_SUP.COMPANY_KEY
AND MAP_SUP.MAP_TYPE = 1
AND GEO.PLAN_ID = -1
AND GEO.LEVEL_ID = 12
AND GEO.SR_LEVEL_PK = MAP_SUP.TP_KEY /* ORG MAPPING */
AND SUP.CUSTOMER_SITE_ID = MAP_ORG.COMPANY_KEY
AND MAP_ORG.MAP_TYPE = 2
AND MTP.PARTNER_ID = MAP_ORG.TP_KEY
AND ORG.PLAN_ID = -1
AND ORG.LEVEL_ID = 7
AND ORG.SR_LEVEL_PK = MTP.SR_INSTANCE_ID||':'||MTP.SR_TP_ID
AND MTP.PARTNER_TYPE = 3 /* AGREEMENT DETAILS MAPPING */
AND MLAH.PLAN_ID = SUP.PLAN_ID
AND MLAH.INVENTORY_ITEM_ID = SUP.INVENTORY_ITEM_ID
AND MLAH.ORGANIZATION_ID = MTP.SR_TP_ID
AND MLAH.SR_INSTANCE_ID = MTP.SR_INSTANCE_ID
AND MLAH.SUPPLIER_ID = MAP_SUP.TP_KEY /* ORDER FORECAST - PLANNED ORDERS * OR * SUPPLY COMMIT - SUPPLY COMMIT */
AND (((MLAH.ASL_LIABILITY_AGREEMENT_BASIS = 1)
AND (SUP.PUBLISHER_ORDER_TYPE = 24)) OR ((MLAH.ASL_LIABILITY_AGREEMENT_BASIS = 2)
AND (SUP.PUBLISHER_ORDER_TYPE = 3)))
AND NVL(MLAH.INCLUDE_LIABILITY_AGREEMENT
, 2) = 1 /* AUTHORIZATION CODE MANPPING */
AND DCS.PLAN_ID = -1
AND DCS.LEVEL_ID = 34
AND DCS.SR_LEVEL_PK = MLAH.AUTHORIZATION_CODE
AND SUP.KEY_DATE >= (MDP.PLAN_START_DATE + MLAH.START_DAYS)
AND SUP.KEY_DATE < (MDP.PLAN_START_DATE + MLAH.END_DAYS) UNION ALL /* PDS DATA - PLANNED ORDERS */ SELECT MPLN.COMPILE_DESIGNATOR
, 7
, ORG.LEVEL_PK
, 1
, PRD.LEVEL_PK
, 12
, GEO.LEVEL_PK
, 34
, DCS.LEVEL_PK
, 9
, MS.NEW_SCHEDULE_DATE
, (MAD.PERCENTAGE_PURCHASE_PRICE * MS.NEW_ORDER_QUANTITY)/100
, MS.CREATION_DATE
, MS.CREATED_BY
, MS.LAST_UPDATE_DATE
, MS.LAST_UPDATED_BY
, MS.LAST_UPDATE_LOGIN
, 1
, 1
, 'I'
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
FROM MSC_SUPPLIES MS
, MSD_DEMAND_PLANS MDP
, MSC_PLANS MPLN
, MSD_LEVEL_VALUES_LB PRD
, MSD_LEVEL_VALUES_LB GEO
, MSD_LEVEL_VALUES_LB DCS
, MSD_LEVEL_VALUES_LB ORG
, MSC_TRADING_PARTNERS MTP
, MSC_ASL_AUTH_DETAILS MAD
WHERE MPLN.PLAN_ID <> -1
AND PRD.PLAN_ID = MS.PLAN_ID
AND PRD.LEVEL_ID = 1
AND PRD.SR_LEVEL_PK = MS.INVENTORY_ITEM_ID
AND ORG.PLAN_ID = MS.PLAN_ID
AND ORG.LEVEL_ID = 7
AND MTP.PARTNER_TYPE = 3
AND MTP.SR_TP_ID = MS.ORGANIZATION_ID
AND MTP.SR_INSTANCE_ID = MS.SR_INSTANCE_ID
AND ORG.SR_LEVEL_PK = MTP.SR_INSTANCE_ID||':'||MTP.SR_TP_ID
AND GEO.PLAN_ID = MS.PLAN_ID
AND GEO.LEVEL_ID = 12
AND GEO.SR_LEVEL_PK = MS.SUPPLIER_ID
AND DCS.PLAN_ID = MS.PLAN_ID
AND DCS.LEVEL_ID = 34
AND MS.ORDER_TYPE = 5
AND MAD.ASL_LIABILITY_AGREEMENT_BASIS = 1
AND NVL(MAD.INCLUDE_LIABILITY_AGREEMENT
, 2) = 1
AND MAD.INVENTORY_ITEM_ID = MS.INVENTORY_ITEM_ID
AND MAD.ORGANIZATION_ID = MS.ORGANIZATION_ID
AND MAD.SUPPLIER_ID = MS.SUPPLIER_ID
AND MAD.SR_INSTANCE_ID = MS.SR_INSTANCE_ID
AND DCS.SR_LEVEL_PK = MAD.AUTHORIZATION_CODE
AND MDP.LIAB_PLAN_ID = MPLN.PLAN_ID
AND MS.NEW_SCHEDULE_DATE >= ( MDP.PLAN_START_DATE + MAD.START_DAYS)
AND MS.NEW_SCHEDULE_DATE < (MDP.PLAN_START_DATE + MAD.END_DAYS)
AND MS.PLAN_ID = MPLN.PLAN_ID UNION ALL /* PDS DATA - SUPPLIER CAPACITY */ SELECT MPLN.COMPILE_DESIGNATOR
, 7
, ORG.LEVEL_PK
, 1
, PRD.LEVEL_PK
, 12
, GEO.LEVEL_PK
, 34
, DCS.LEVEL_PK
, 9
, MT.DAY
, (MAD.PERCENTAGE_PURCHASE_PRICE * MS.CAPACITY)/100
, MS.CREATION_DATE
, MS.CREATED_BY
, MS.LAST_UPDATE_DATE
, MS.LAST_UPDATED_BY
, MS.LAST_UPDATE_LOGIN
, 1
, 1
, 'I'
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
FROM MSC_SUPPLIER_CAPACITIES MS
, MSD_TIME MT
, MSC_PLANS MPLN
, MSD_LEVEL_VALUES_LB PRD
, MSD_LEVEL_VALUES_LB GEO
, MSD_LEVEL_VALUES_LB DCS
, MSD_LEVEL_VALUES_LB ORG
, MSC_TRADING_PARTNERS MTP
, MSC_ASL_AUTH_DETAILS MAD
WHERE MPLN.PLAN_ID <> -1
AND PRD.PLAN_ID = MS.PLAN_ID
AND PRD.LEVEL_ID = 1
AND PRD.SR_LEVEL_PK = MS.INVENTORY_ITEM_ID
AND ORG.PLAN_ID = MS.PLAN_ID
AND ORG.LEVEL_ID = 7
AND MS.SR_INSTANCE_ID = MTP.SR_INSTANCE_ID
AND MTP.PARTNER_TYPE = 3
AND MS.ORGANIZATION_ID = MTP.SR_TP_ID
AND ORG.SR_LEVEL_PK = MTP.SR_INSTANCE_ID||':'||MTP.SR_TP_ID
AND GEO.PLAN_ID = MS.PLAN_ID
AND GEO.LEVEL_ID = 12
AND GEO.SR_LEVEL_PK = MS.SUPPLIER_ID
AND DCS.PLAN_ID = MS.PLAN_ID
AND DCS.LEVEL_ID = 34
AND MS.SR_INSTANCE_ID = MAD.SR_INSTANCE_ID
AND MS.ORGANIZATION_ID = MAD.ORGANIZATION_ID
AND MS.INVENTORY_ITEM_ID = MAD.INVENTORY_ITEM_ID
AND MS.SUPPLIER_ID = MAD.SUPPLIER_ID
AND MAD.ASL_LIABILITY_AGREEMENT_BASIS = 2
AND NVL(MAD.INCLUDE_LIABILITY_AGREEMENT
, 2) = 1
AND MAD.AUTHORIZATION_CODE = DCS.SR_LEVEL_PK
AND MT.CALENDAR_TYPE = 1
AND MT.CALENDAR_CODE = 'GREGORIAN'
AND MT.DAY BETWEEN MS.FROM_DATE
AND MS.TO_DATE
AND MT.DAY >= MPLN.PLAN_START_DATE + MAD.START_DAYS
AND MT.DAY < MPLN.PLAN_START_DATE + MAD.END_DAYS
AND MS.PLAN_ID = MPLN.PLAN_ID