VIEW: APPS.MSD_SPF_INSTALL_BASE_UC_V

Source

View Text - Preformatted

SELECT ibuc.sr_instance_id sr_instance_id, DECODE (msi.base_item_id, NULL, msi.sr_inventory_item_id, DECODE(NVL(msi.ato_forecast_control, 3), 3, NVL(msi2.sr_inventory_item_id, msi.sr_inventory_item_id), msi.sr_inventory_item_id)) sr_item_pk, DECODE (msi.base_item_id, NULL, msi.item_name, DECODE(NVL(msi.ato_forecast_control, 3), 3, NVL(msi2.item_name, msi.item_name), msi.item_name)) dm_item_code, mtp_org.sr_tp_id sr_inv_org_pk, mtp_org.organization_code dm_org_code, ibuc.sample_date sdate, ibuc.quantity quantity FROM msc_org_aggr_ibuc ibuc, msc_trading_partners mtp_org, msd_dem_app_instance_orgs mio, (select to_number(parameter_value) spf_master_org_id from msd_dem_setup_parameters where parameter_name = 'MSD_SPF_MASTER_ORG') mdsp, msc_system_items msi, msc_system_items msi2 WHERE upper(msd_dem_common_utilities.dm_time_level) = 'DAY' AND mtp_org.sr_instance_id = ibuc.sr_instance_id AND mtp_org.sr_tp_id = ibuc.supplying_org_id AND mtp_org.partner_type = 3 AND mio.organization_id = ibuc.supplying_org_id AND msi.plan_id = -1 AND msi.sr_instance_id = ibuc.sr_instance_id AND msi.organization_id = mdsp.spf_master_org_id AND msi.inventory_item_id = ibuc.inventory_item_id AND ( msi.mrp_planning_code<> 6 OR (msi.mrp_planning_code = 6 AND msi.pick_components_flag = 'Y')) AND msi2.plan_id (+) = msi.plan_id AND msi2.sr_instance_id (+) = msi.sr_instance_id AND msi2.organization_id (+) = msi.organization_id AND msi2.inventory_item_id (+) = msi.base_item_id UNION ALL SELECT sr_instance_id, sr_item_pk, dm_item_code, sr_inv_org_pk, dm_org_code, sdate, quantity FROM (SELECT ibuc.sr_instance_id sr_instance_id, DECODE (msi.base_item_id, NULL, msi.sr_inventory_item_id, DECODE(NVL(msi.ato_forecast_control, 3), 3, NVL(msi2.sr_inventory_item_id, msi.sr_inventory_item_id), msi.sr_inventory_item_id)) sr_item_pk, DECODE (msi.base_item_id, NULL, msi.item_name, DECODE(NVL(msi.ato_forecast_control, 3), 3, NVL(msi2.item_name, msi.item_name), msi.item_name)) dm_item_code, mtp_org.sr_tp_id sr_inv_org_pk, mtp_org.organization_code dm_org_code, ibuc.sample_date sdate, FIRST_VALUE(ibuc.sample_date) OVER (PARTITION BY ibuc.sr_instance_id, DECODE (msi.base_item_id, NULL, msi.item_name, DECODE(NVL(msi.ato_forecast_control, 3), 3, NVL(msi2.item_name, msi.item_name), msi.item_name)), mtp_org.organization_code, mdd.end_date ORDER BY ibuc.sample_date DESC) bucket_last_date, ibuc.quantity quantity FROM msc_org_aggr_ibuc ibuc, msc_trading_partners mtp_org, msd_dem_app_instance_orgs mio, (select to_number(parameter_value) spf_master_org_id from msd_dem_setup_parameters where parameter_name = 'MSD_SPF_MASTER_ORG') mdsp, msc_system_items msi, msc_system_items msi2, msd_dem_dates mdd WHERE upper(msd_dem_common_utilities.dm_time_level) <> 'DAY' AND mtp_org.sr_instance_id = ibuc.sr_instance_id AND mtp_org.sr_tp_id = ibuc.supplying_org_id AND mtp_org.partner_type = 3 AND mio.organization_id = ibuc.supplying_org_id AND msi.plan_id = -1 AND msi.sr_instance_id = ibuc.sr_instance_id AND msi.organization_id = mdsp.spf_master_org_id AND msi.inventory_item_id = ibuc.inventory_item_id AND ( msi.mrp_planning_code <> 6 OR (msi.mrp_planning_code = 6 AND msi.pick_components_flag = 'Y')) AND msi2.plan_id (+) = msi.plan_id AND msi2.sr_instance_id (+) = msi.sr_instance_id AND msi2.organization_id (+) = msi.organization_id AND msi2.inventory_item_id (+) = msi.base_item_id AND ibuc.sample_date BETWEEN mdd.start_date AND mdd.end_date ) WHERE bucket_last_date = sdate

View Text - HTML Formatted

SELECT IBUC.SR_INSTANCE_ID SR_INSTANCE_ID
, DECODE (MSI.BASE_ITEM_ID
, NULL
, MSI.SR_INVENTORY_ITEM_ID
, DECODE(NVL(MSI.ATO_FORECAST_CONTROL
, 3)
, 3
, NVL(MSI2.SR_INVENTORY_ITEM_ID
, MSI.SR_INVENTORY_ITEM_ID)
, MSI.SR_INVENTORY_ITEM_ID)) SR_ITEM_PK
, DECODE (MSI.BASE_ITEM_ID
, NULL
, MSI.ITEM_NAME
, DECODE(NVL(MSI.ATO_FORECAST_CONTROL
, 3)
, 3
, NVL(MSI2.ITEM_NAME
, MSI.ITEM_NAME)
, MSI.ITEM_NAME)) DM_ITEM_CODE
, MTP_ORG.SR_TP_ID SR_INV_ORG_PK
, MTP_ORG.ORGANIZATION_CODE DM_ORG_CODE
, IBUC.SAMPLE_DATE SDATE
, IBUC.QUANTITY QUANTITY
FROM MSC_ORG_AGGR_IBUC IBUC
, MSC_TRADING_PARTNERS MTP_ORG
, MSD_DEM_APP_INSTANCE_ORGS MIO
, (SELECT TO_NUMBER(PARAMETER_VALUE) SPF_MASTER_ORG_ID
FROM MSD_DEM_SETUP_PARAMETERS
WHERE PARAMETER_NAME = 'MSD_SPF_MASTER_ORG') MDSP
, MSC_SYSTEM_ITEMS MSI
, MSC_SYSTEM_ITEMS MSI2
WHERE UPPER(MSD_DEM_COMMON_UTILITIES.DM_TIME_LEVEL) = 'DAY'
AND MTP_ORG.SR_INSTANCE_ID = IBUC.SR_INSTANCE_ID
AND MTP_ORG.SR_TP_ID = IBUC.SUPPLYING_ORG_ID
AND MTP_ORG.PARTNER_TYPE = 3
AND MIO.ORGANIZATION_ID = IBUC.SUPPLYING_ORG_ID
AND MSI.PLAN_ID = -1
AND MSI.SR_INSTANCE_ID = IBUC.SR_INSTANCE_ID
AND MSI.ORGANIZATION_ID = MDSP.SPF_MASTER_ORG_ID
AND MSI.INVENTORY_ITEM_ID = IBUC.INVENTORY_ITEM_ID
AND ( MSI.MRP_PLANNING_CODE<> 6 OR (MSI.MRP_PLANNING_CODE = 6
AND MSI.PICK_COMPONENTS_FLAG = 'Y'))
AND MSI2.PLAN_ID (+) = MSI.PLAN_ID
AND MSI2.SR_INSTANCE_ID (+) = MSI.SR_INSTANCE_ID
AND MSI2.ORGANIZATION_ID (+) = MSI.ORGANIZATION_ID
AND MSI2.INVENTORY_ITEM_ID (+) = MSI.BASE_ITEM_ID UNION ALL SELECT SR_INSTANCE_ID
, SR_ITEM_PK
, DM_ITEM_CODE
, SR_INV_ORG_PK
, DM_ORG_CODE
, SDATE
, QUANTITY
FROM (SELECT IBUC.SR_INSTANCE_ID SR_INSTANCE_ID
, DECODE (MSI.BASE_ITEM_ID
, NULL
, MSI.SR_INVENTORY_ITEM_ID
, DECODE(NVL(MSI.ATO_FORECAST_CONTROL
, 3)
, 3
, NVL(MSI2.SR_INVENTORY_ITEM_ID
, MSI.SR_INVENTORY_ITEM_ID)
, MSI.SR_INVENTORY_ITEM_ID)) SR_ITEM_PK
, DECODE (MSI.BASE_ITEM_ID
, NULL
, MSI.ITEM_NAME
, DECODE(NVL(MSI.ATO_FORECAST_CONTROL
, 3)
, 3
, NVL(MSI2.ITEM_NAME
, MSI.ITEM_NAME)
, MSI.ITEM_NAME)) DM_ITEM_CODE
, MTP_ORG.SR_TP_ID SR_INV_ORG_PK
, MTP_ORG.ORGANIZATION_CODE DM_ORG_CODE
, IBUC.SAMPLE_DATE SDATE
, FIRST_VALUE(IBUC.SAMPLE_DATE) OVER (PARTITION BY IBUC.SR_INSTANCE_ID
, DECODE (MSI.BASE_ITEM_ID
, NULL
, MSI.ITEM_NAME
, DECODE(NVL(MSI.ATO_FORECAST_CONTROL
, 3)
, 3
, NVL(MSI2.ITEM_NAME
, MSI.ITEM_NAME)
, MSI.ITEM_NAME))
, MTP_ORG.ORGANIZATION_CODE
, MDD.END_DATE ORDER BY IBUC.SAMPLE_DATE DESC) BUCKET_LAST_DATE
, IBUC.QUANTITY QUANTITY
FROM MSC_ORG_AGGR_IBUC IBUC
, MSC_TRADING_PARTNERS MTP_ORG
, MSD_DEM_APP_INSTANCE_ORGS MIO
, (SELECT TO_NUMBER(PARAMETER_VALUE) SPF_MASTER_ORG_ID
FROM MSD_DEM_SETUP_PARAMETERS
WHERE PARAMETER_NAME = 'MSD_SPF_MASTER_ORG') MDSP
, MSC_SYSTEM_ITEMS MSI
, MSC_SYSTEM_ITEMS MSI2
, MSD_DEM_DATES MDD
WHERE UPPER(MSD_DEM_COMMON_UTILITIES.DM_TIME_LEVEL) <> 'DAY'
AND MTP_ORG.SR_INSTANCE_ID = IBUC.SR_INSTANCE_ID
AND MTP_ORG.SR_TP_ID = IBUC.SUPPLYING_ORG_ID
AND MTP_ORG.PARTNER_TYPE = 3
AND MIO.ORGANIZATION_ID = IBUC.SUPPLYING_ORG_ID
AND MSI.PLAN_ID = -1
AND MSI.SR_INSTANCE_ID = IBUC.SR_INSTANCE_ID
AND MSI.ORGANIZATION_ID = MDSP.SPF_MASTER_ORG_ID
AND MSI.INVENTORY_ITEM_ID = IBUC.INVENTORY_ITEM_ID
AND ( MSI.MRP_PLANNING_CODE <> 6 OR (MSI.MRP_PLANNING_CODE = 6
AND MSI.PICK_COMPONENTS_FLAG = 'Y'))
AND MSI2.PLAN_ID (+) = MSI.PLAN_ID
AND MSI2.SR_INSTANCE_ID (+) = MSI.SR_INSTANCE_ID
AND MSI2.ORGANIZATION_ID (+) = MSI.ORGANIZATION_ID
AND MSI2.INVENTORY_ITEM_ID (+) = MSI.BASE_ITEM_ID
AND IBUC.SAMPLE_DATE BETWEEN MDD.START_DATE
AND MDD.END_DATE )
WHERE BUCKET_LAST_DATE = SDATE