[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
EXECUTE IMMEDIATE 'SELECT plan_id FROM ' ||
msd_dem_common_utilities.get_lookup_value ('MSD_DEM_TABLES', 'SUPPLY_PLAN') ||
' WHERE supply_plan_id = ' || p_member_id
INTO x_plan_id;
EXECUTE IMMEDIATE 'SELECT plan_type FROM ' ||
msd_dem_common_utilities.get_lookup_value ('MSD_DEM_TABLES', 'SUPPLY_PLAN') ||
' WHERE supply_plan_id = ' || p_member_id
INTO x_plan_type;
select curr_start_date, cutoff_date,
daily_mwo_aggr_lvl,weekly_mwo_aggr_lvl,period_mwo_aggr_lvl
from msc_plans
where plan_id = p_plan_id;
select min(bkt_start_date) into l_aggregation_start_date
from msc_plan_buckets
where plan_id = p_plan_id and bucket_type = 2 ;
select min(bkt_start_date) into l_aggregation_start_date
from msc_plan_buckets
where plan_id = p_plan_id and bucket_type = 3 ;
x_sql := 'select nvl(ms.transaction_id , mfp.transaction_id) ' ||
' from (select * from ( ' ||
' select sr_instance_id, plan_id, transaction_id, demand_id ' ||
' from msc_full_pegging ' ||
' where sr_instance_id = :1 and plan_id = :2 and supply_type in (70,92)' ||
' start with sr_instance_id = :3 and plan_id = :4 ' || case when p_pegging_id is not null then 'and pegging_id = ' || to_char(p_pegging_id) else ' ' end ||
' and ' || case when nvl(p_dem_sup_flag, 1)= 1 then 'transaction_id' else 'demand_id' end || ' = :5 ' ||
' connect by nocycle prior prev_pegging_id = pegging_id and prior plan_id = plan_id and prior sr_instance_id = sr_instance_id ' ||
' order by level desc) ' ||
' where rownum <= 1) mfp, ' ||
' msc_demands md, msc_supplies ms ' ||
' where md.sr_instance_id(+) = mfp.sr_instance_id and md.plan_id(+) = mfp.plan_id ' ||
' and md.demand_id(+) = mfp.demand_id ' ||
' and ms.sr_instance_id(+) = md.sr_instance_id and ms.plan_id(+) = md.plan_id ' ||
' and ms.transaction_id(+) = md.disposition_id and ms.order_type(+) in (70,92) ' ;
EXECUTE IMMEDIATE 'DELETE FROM ' || x_table_name;
EXECUTE IMMEDIATE 'DELETE FROM ' || x_table_name;
SELECT
series_name,
series_type,
identifier,
custom_view_name,
ps_view_name,
stg_series_col_name
FROM
msd_dem_series
WHERE
series_id = p_series_id;
SELECT input_schedule_id from msc_plan_schedules
where plan_id = p_plan_id;
x_sql := ' Select to_char(bucket_date,''YYYY-MM-DD HH24:MI:SS'') from msd_Dem_day_dates mdd where mdd.day_date=TRUNC(TO_DATE(''' || p_start_date || ''',''YYYY-MM-DD HH24:MI:SS'') )';
SELECT
series_id
FROM msd_dem_series
WHERE series_id IN (112, 113);
select curr_start_date
from msc_plans
where plan_id = p_plan_id;
/***** 3. UPDATE START DATES AND END DATES OF THE PURGE PLAN DATA *****/
OPEN c_plan_start_date;
l_sql := 'select datet from '|| g_schema ||'.inputs where datet > '''||l_plan_start_date||''' and rownum = 1 order by datet asc';
l_sql := 'select max(datet) from '||g_schema||'.inputs ';
SELECT
series_id
FROM
msd_dem_series
WHERE
series_id IN (101, 102, 103, 104, 105, 106, 107, 108, 109, 110);
/* Delete non-saleable items from the staging table BIIO_OTHER_PLAN_DATA */
-- syenamar
-- In case 'Include dependent demand' is true, delete non-saleable items only if they are non-cto
-- if false, no change in existing behaviour (any non-saleable item will be removed)
x_sql := ' DELETE FROM ' || g_schema || '.BIIO_OTHER_PLAN_DATA bopd '
|| ' WHERE ';
|| ' (SELECT 1 from '
|| g_schema || '.t_ep_item tei, '
|| g_schema || '.t_ep_cto_matrix tcm, '
|| g_schema || '.items itm '
|| ' where tei.item = bopd.level2 '
|| ' and tei.t_ep_item_ep_id = itm.t_ep_item_ep_id '
|| ' and itm.item_id = tcm.item_id '
|| ' and rownum < 2) '
|| ' AND ';
x_sql := x_sql || ' NOT EXISTS ( SELECT 1 '
|| ' FROM ' || g_schema || '.t_ep_item tei '
|| ' WHERE tei.item = bopd.level2 '
|| ' AND EXISTS ( SELECT 1 FROM ' || g_schema || '.mdp_matrix mm '
|| ' WHERE mm.t_ep_item_ep_id = tei.t_ep_item_ep_id '
|| ' AND rownum < 2 ) ) '
|| ' AND avail_sup_std_cap IS NULL '
|| ' AND required_sup_cap IS NULL ';
/* Updated non-supplier series to NULL for non-saleable:buy:critical items from the staging table BIIO_OTHER_PLAN_DATA */
x_sql := ' UPDATE ' || g_schema || '.BIIO_OTHER_PLAN_DATA bopd '
|| ' SET bopd.constrained_fcst = NULL, '
|| ' bopd.prod_plan = NULL, '
|| ' bopd.safety_stk = NULL, '
|| ' bopd.beginning_on_hand = NULL, '
|| ' bopd.dependent_demand = NULL, '
|| ' bopd.planned_shipments = NULL '
|| ' WHERE NOT EXISTS ( SELECT 1 '
|| ' FROM ' || g_schema || '.t_ep_item tei, '
|| g_schema || '.mdp_matrix mm '
|| ' WHERE tei.item = bopd.level2 '
|| ' AND mm.t_ep_item_ep_id = tei.t_ep_item_ep_id '
|| ' AND mm.is_fictive = 0 '
|| ' AND rownum < 2 ) '
|| ' AND ( avail_sup_std_cap IS NOT NULL '
|| ' OR required_sup_cap IS NOT NULL ) ';
EXECUTE IMMEDIATE 'DELETE FROM msd_dem_dates';
EXECUTE IMMEDIATE 'DELETE FROM msd_dem_day_dates';
x_sql := 'SELECT time_bucket, first_day_of_week, aggregation_method ' ||
' FROM ' || x_dm_table ||
' WHERE dm_or_template = 2 ' ||
' AND is_active = 1 ';
log_debug ('Lowest Time Bucket - Day : Time data not inserted into source msd_dem_dates');
log_debug ('Inserting time data into msd_dem_dates');
x_sql := 'INSERT INTO msd_dem_dates' ||
' (datet, num_of_days, start_date, end_date, last_update_date, last_updated_by, creation_date, created_by, last_update_login) ' ||
' SELECT datet, num_of_days, ' || x_start_date || x_end_date || ', ' ||
' sysdate, :1, sysdate, :2, :3 ' ||
' FROM ' || x_source_time_table;
x_sql := 'INSERT INTO msd_dem_day_dates' ||
' (day_date, bucket_date, bucket_start_date, bucket_end_date, last_update_date, last_updated_by, creation_date, created_by, last_update_login) ' ||
' with dem_dates as '||
' (SELECT datet, num_of_days, ' || x_start_date || x_end_date ||
' FROM ' || x_source_time_table || ') ' ||
' select start_date + i day_date, datet, start_date, end_date, sysdate, :1, sysdate, :2, :3 ' ||
' from dem_dates, xmltable(''for $i in 0 to xs:int(D)-1 return $i'' passing xmlelement(d, num_of_days)
columns i integer path ''.'')' ;
l_stmt := 'select input_name from msc_plan_sched_v where plan_id = ' ||p_plan_id || ' group by input_name ';
l_sql := 'select scenario_status_id from '|| g_schema||'.supply_plan where plan_id = '||p_plan_id;
l_sql := 'select scenario_status_code from '|| g_schema||'.scenario_status where scenario_status_id = '||l_scenario_status_id;
l_sql := 'insert into ' || g_schema||'.biio_supply_plans(supply_plan_code,
supply_plan_desc,
scenario_status_code,
plan_id,
method_status,
demand_schedules,
plan_type,
start_date,
end_date,
last_imported)
VALUES ('''
||x_plan_code ||''','
||''''||x_plan_desc ||''','
||''||l_scenario_status_code ||','
||''||p_plan_id ||','
||''''|| NULL ||''','
||''''||x_dem_sched ||''','
||''''||x_plan_type ||''','
||''''||p_plan_start_date ||''','
||''''||p_end_date ||''','
||''''||sysdate ||''')' ;
l_sql := 'insert into ' || g_schema||'.biio_supply_plans_pop(level_member,
from_date,
until_date,
filter_level,
level_order,
filter_member)
values ('''
||x_plan_code ||''','
||''''||p_plan_start_date ||''','
||''''||sysdate ||''','
||''''|| msd_dem_common_utilities.get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID',
'LEVEL_ITEM',
1,
'table_label') ||''','
||''||'2' ||','
||''''||'0' ||''')' ;
l_sql := 'insert into ' || g_schema||'.biio_supply_plans_pop(level_member,
from_date,
until_date,
filter_level,
level_order,
filter_member)
values ('''
||x_plan_code ||''','
||''''||p_plan_start_date ||''','
||''''||sysdate ||''','
||''''|| msd_dem_common_utilities.get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID',
'LEVEL_DEMAND_CLASS',
1,
'table_label') ||''','
||''||'2' ||','
||''''||'0' ||''')' ;
l_sql := 'insert into ' || g_schema||'.biio_supply_plans_pop(level_member,
from_date,
until_date,
filter_level,
level_order,
filter_member)
values ('''
||x_plan_code ||''','
||''''||p_plan_start_date ||''','
||''''||sysdate ||''','
||''''|| msd_dem_common_utilities.get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID',
'LEVEL_ORGANIZATION',
1,
'table_label') ||''','
||''||'1' ||','
||''''||'0' ||''')' ;
l_sql := 'insert into ' || g_schema||'.biio_supply_plans_pop(level_member,
from_date,
until_date,
filter_level,
level_order,
filter_member)
values ('''
||x_plan_code ||''','
||''''||p_plan_start_date ||''','
||''''||sysdate ||''','
||''''|| msd_dem_common_utilities.get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID',
'LEVEL_SITE',
1,
'table_label') ||''','
||''||'1' ||','
||''''||'0' ||''')' ;
l_sql := 'insert into ' || g_schema||'.biio_supply_plans_pop(level_member,
from_date,
until_date,
filter_level,
level_order,
filter_member)
values ('''
||x_plan_code ||''','
||''''||p_plan_start_date ||''','
||''''||sysdate ||''','
||''''|| msd_dem_common_utilities.get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID',
'LEVEL_SALES_CHANNEL',
1,
'table_label') ||''','
||''||'1' ||','
||''''||'0' ||''')' ;
select sr_instance_id, compile_designator, plan_type, curr_start_date, cutoff_date
from msc_plans
where plan_id = p_plan_id;
select compile_designator, curr_start_date, cutoff_date, sop_enabled, plan_type
from msc_plans
where plan_id=p_plan_id;
l_stmt := 'select plan_id, compile_designator, curr_start_date, cutoff_date, sop_enabled, plan_type
from msc_plans
where sop_enabled = 1 ';
|| ' ( SELECT supply_plan_code FROM '
|| g_schema || '.supply_plan )';
msd_dem_query_utilities.get_query(l_retcode, l_stmt, 'MSD_DEM_DELETE_STMT', l_instance_id);
msd_dem_query_utilities.get_query(l_retcode, l_stmt, 'MSD_DEM_DELETE_STMT', l_instance_id);
l_stmt := 'select input_name from msc_plan_sched_v where plan_id = ' ||p_plan_id || ' group by input_name ';
l_stmt := ' SELECT spd.from_date FROM ' || g_schema || '.supply_plan sp, '
|| g_schema || '.supply_plan_dates spd '
|| ' WHERE sp.plan_id = ' || p_plan_id
|| ' AND spd.supply_plan_id = sp.supply_plan_id ';
select sr_instance_id, compile_designator, plan_type, curr_start_date, cutoff_date
from msc_plans
where plan_id = p_plan_id;
l_stmt := ' SELECT spd.from_date FROM ' || g_schema || '.supply_plan sp, '
|| g_schema || '.supply_plan_dates spd '
|| ' WHERE sp.plan_id = ' || p_plan_id
|| ' AND spd.supply_plan_id = sp.supply_plan_id ';
select sr_instance_id
from msc_plans
where plan_id = p_plan_id;
x_sql := 'select 1 from dual where exists ( select 1 from dba_jobs where (upper(what) like ''MSD_DEM_SOP.LOAD_SERIES_DATA_BATCH%'' or upper(what) like ''MSD_DEM_SOP.LOAD_PLAN_DATA_WO_PDS%'' or ';
SELECT series_id FROM msd_dem_series
WHERE
series_id IN (105, 107, 115, 116, 117, 118);
SELECT organization_id FROM msc_plan_organizations
WHERE plan_id = p_plan_id;
SELECT series_id FROM msd_dem_series
WHERE
series_id IN (115, 116);
SELECT organization_id FROM msc_plan_organizations
WHERE plan_id = p_plan_id;
SELECT series_id FROM msd_dem_series
WHERE
series_id IN (105, 107, 115, 116, 117, 118);
SELECT series_id FROM msd_dem_series
WHERE
series_id IN (119, 120);
SELECT organization_id FROM msc_plan_organizations
WHERE plan_id = p_plan_id;
select sr_instance_id, compile_designator, plan_type, curr_start_date, cutoff_date
from msc_plans
where plan_id = p_plan_id;
select instance_id
from msc_apps_instances;
l_stmt := 'select input_name from msc_plan_sched_v where plan_id = ' ||p_plan_id || ' group by input_name ';
l_stmt := ' SELECT spd.from_date FROM ' || g_schema || '.supply_plan sp, '
|| g_schema || '.supply_plan_dates spd '
|| ' WHERE sp.plan_id = ' || p_plan_id
|| ' AND spd.supply_plan_id = sp.supply_plan_id ';
l_stmt := 'DELETE FROM MSD_ST_SOP_WO_DEMANDS ' ||
' WHERE NVL(CONSTRAINED_FCST, 0) = 0 AND NVL(DEPENDENT_DEMAND, 0) = 0 ' ||
' AND NVL(PLANNED_SHIPMENTS, 0) = 0 AND NVL(WORK_ORDER_DEMAND, 0) = 0 ';
l_stmt := 'DELETE FROM MSD_ST_SOP_WO_REQR_RES_CAP ' ||
' WHERE NVL(REQUIRED_RES_PLAN, 0) = 0 ';
l_stmt := 'DELETE FROM MSD_ST_SOP_WO_SUPPLIES ' ||
' WHERE NVL(PROD_PLAN, 0) = 0 AND NVL(INTRANSIT_DEFECTIVES, 0) = 0 ' ||
' AND NVL(REQUIRED_SUPPLIER_CAP, 0) = 0 AND NVL(BEGINNING_ONHAND, 0) = 0 ' ||
' AND NVL(BEGINNING_ONHAND_DEFECTIVES, 0) = 0 ';
l_stmt := 'DELETE FROM ' || g_schema || '.BIIO_OTHER_PLAN_DATA ' ||
' WHERE NVL(AVAIL_RES_STD_CAP, 0) = 0 AND NVL(AVAIL_SUP_STD_CAP, 0) = 0 ' ||
' AND NVL(SAFETY_STK, 0) = 0 ';
/* update AG, CC attributes in MSD_ST_SOP_WO_DEMANDS, MSD_ST_SOP_WO_REQR_RES_CAP, MSD_ST_SOP_WO_SUPPLIES*/
-- Bug#14151347: added date filter with bind variable for aggregation_start_date
l_stmt := 'MERGE INTO C_TABLE_NAME a
USING
(select distinct
msup.sr_instance_id,
msup.plan_id,
wsup.organization_id,
wsup.wo_supply_id,
decode(nvl(maintenance_object_source,2),
1, (select item_name from msc_system_items
where sr_instance_id = msup.sr_instance_id and inventory_item_id = msup.asset_item_id
and plan_id = msup.plan_id and organization_id = msup.organization_id),
msup.product_classification
) asset_group,
decode(nvl(maintenance_object_source,2),
1, msup.class_code,
msup.maintenance_reqt
) class_code
from (select distinct sr_instance_id, plan_id, organization_id, wo_supply_id, sdate from C_TABLE_NAME) wsup,
msc_supplies msup
where msup.sr_instance_id = wsup.sr_instance_id
and msup.plan_id= wsup.plan_id
and msup.transaction_id = wsup.wo_supply_id
and wsup.sdate < :1
) b
ON (
a.sr_instance_id = b.sr_instance_id
and a.plan_id = b.plan_id
and a.organization_id = b.organization_id
and a.wo_supply_id = b.wo_supply_id
)
WHEN MATCHED THEN
UPDATE SET
a.asset_group = b.asset_group,
a.class_code = b.class_code ';
l_stmt := ' INSERT /*+ APPEND */ INTO ' || g_schema || '.BIIO_SUPPLY_PLAN_DATA_WO NOLOGGING '
|| '(SDATE, LEVEL1, LEVEL2, LEVEL3, LEVEL4, LEVEL5, LEVEL6, LEVEL7, LEVEL8, '
|| ' PROD_PLAN, REQUIRED_SUP_CAP, REQUIRED_RES_PLAN, BEGINNING_ON_HAND, '
|| ' CONSTRAINED_FCST, DEPENDENT_DEMAND, PLANNED_SHIPMENTS, INTRANSIT_DEFECTIVES, '
|| ' BEGINNING_ONHAND_DEFECTIVES, WORK_ORDER_DEMAND) '
|| ' SELECT '
|| ' SDATE, :1, nvl(ASSET_GROUP, du.default_code), nvl(CLASS_CODE, du.default_code), ITEM_CODE, ORGANIZATION_CODE, '
|| ' du.default_code, SITE_CODE, du.default_code, '
|| ' sum(PROD_PLAN), sum(REQUIRED_SUPPLIER_CAP), sum(REQUIRED_RES_PLAN), sum(BEGINNING_ONHAND), '
|| ' sum(CONSTRAINED_FCST), sum(DEPENDENT_DEMAND), sum(PLANNED_SHIPMENTS), sum(INTRANSIT_DEFECTIVES), '
|| ' sum(BEGINNING_ONHAND_DEFECTIVES), sum(WORK_ORDER_DEMAND) '
|| ' from ( '
|| ' select SDATE, ASSET_GROUP, CLASS_CODE, ITEM_CODE, ORGANIZATION_CODE, dfu.default_code site_code, '
|| ' null PROD_PLAN, null REQUIRED_SUPPLIER_CAP, null REQUIRED_RES_PLAN, null BEGINNING_ONHAND, '
|| ' CONSTRAINED_FCST, DEPENDENT_DEMAND, PLANNED_SHIPMENTS, null INTRANSIT_DEFECTIVES, '
|| ' null BEGINNING_ONHAND_DEFECTIVES, WORK_ORDER_DEMAND'
|| ' from MSD_ST_SOP_WO_DEMANDS, '
|| ' (select msd_dem_sr_util.get_null_code default_code from dual) dfu '
|| ' UNION ALL '
|| ' select SDATE, ASSET_GROUP, CLASS_CODE, RESOURCE_CODE ITEM_CODE, ORGANIZATION_CODE, dfu.default_code site_code, '
|| ' null PROD_PLAN, null REQUIRED_SUPPLIER_CAP, REQUIRED_RES_PLAN, null BEGINNING_ONHAND, '
|| ' null CONSTRAINED_FCST, null DEPENDENT_DEMAND, null PLANNED_SHIPMENTS, null INTRANSIT_DEFECTIVES, '
|| ' null BEGINNING_ONHAND_DEFECTIVES, null WORK_ORDER_DEMAND'
|| ' from MSD_ST_SOP_WO_REQR_RES_CAP, '
|| ' (select msd_dem_sr_util.get_null_code default_code from dual) dfu '
|| ' UNION ALL '
|| ' select SDATE, ASSET_GROUP, CLASS_CODE, ITEM_CODE, ORGANIZATION_CODE, SITE_CODE, '
|| ' PROD_PLAN, REQUIRED_SUPPLIER_CAP, null REQUIRED_RES_PLAN, BEGINNING_ONHAND, '
|| ' null CONSTRAINED_FCST, null DEPENDENT_DEMAND, null PLANNED_SHIPMENTS, INTRANSIT_DEFECTIVES, '
|| ' BEGINNING_ONHAND_DEFECTIVES, null WORK_ORDER_DEMAND'
|| ' from MSD_ST_SOP_WO_SUPPLIES '
|| ' ) data, '
|| ' (select msd_dem_sr_util.get_null_code default_code from dual) du '
|| ' group by '
|| ' SDATE, :2, ASSET_GROUP, CLASS_CODE, ITEM_CODE, ORGANIZATION_CODE, '
|| ' du.default_code, SITE_CODE, du.default_code';
/* Inserting an entry into Integ_Status table
that loading of plan is running. */
l_sql := 'Insert into ' || g_schema || '.integ_status(username, process, stage, status, info, status_date) values (''' || g_schema || ''',' || -- bug 14134116
'''LOAD_PLAN_DATA'',
''LOAD_PLAN_DATA'',
''RUNNING'', '
||''''|| ' ' ||''','
||''''||sysdate ||''')' ;
l_sql := 'select plan_id from ' || g_schema || '.supply_plan
where supply_plan_id = ' ||p_member_id;
p_delete_item_pop IN NUMBER DEFAULT 1,
p_delete_dc_site_sc_pop IN NUMBER DEFAULT 1,
p_use_jobs IN NUMBER default 0,
p_split_batch_by_org IN NUMBER default 0,
p_job_check_interval IN NUMBER default 60,
p_jobs_timeout IN NUMBER default 86400)
IS
/*** LOCAL VARIABLES - BEGIN ***/
x_errbuf VARCHAR2(600) := NULL;
if (p_delete_dc_site_sc_pop = 1) then
EXECUTE IMMEDIATE 'DELETE FROM ' || g_schema || '.BIIO_SUPPLY_PLANS_POP WHERE LEVEL_ORDER in (3,4,5)';
if (v_demantra_version < 7.3 and p_delete_item_pop = 1) then
-- bug#8266960
EXECUTE IMMEDIATE 'DELETE FROM ' || g_schema || '.BIIO_SUPPLY_PLANS_POP WHERE LEVEL_ORDER = 2';
if (p_delete_dc_site_sc_pop = 1) then
EXECUTE IMMEDIATE 'DELETE FROM ' || g_schema || '.BIIO_SUPPLY_PLANS_POP WHERE LEVEL_ORDER in (3,4,5)';
l_sql := 'Insert into ' || g_schema || '.integ_status(username, process, stage, status, info, status_date) values (''' || g_schema || ''',' ||
'''LOAD_PLAN_DATA'',
''LOAD_PLAN_DATA'',
''SUCCEEDED'', '
||''''|| ' ' ||''','
||''''||sysdate ||''')' ;
/* Delete all data (if any) from the staging table */
x_small_sql := 'TRUNCATE TABLE ' || g_schema || '.BIIO_ITEM_COST';
/* Delete all data (if any) from the ERR staging table */
x_small_sql := 'TRUNCATE TABLE ' || g_schema || '.BIIO_ITEM_COST_ERR';
v_sql := 'select nvl((select ''Running'' from ' || g_schema || '.wf_process_log ' ||
'where schema_id = :1 and step_id not in ' || v_not_in_steps ||
' and status not in(0,-1,-2) and rownum = 1), ''Not Running'') from dual';
SELECT owner
FROM dba_objects
WHERE owner = owner
AND object_type = 'TABLE'
AND object_name = 'MDP_MATRIX'
ORDER BY created desc;
l_sql := 'select table_name, from_date, until_date from '|| g_schema || '.transfer_query where id = ' || l_profile_id;
l_sql := 'select datet from '|| g_schema ||'.inputs where datet >= '''||x_from_date||''' and rownum = 1 order by datet asc';
l_sql := 'select datet from '|| g_schema ||'.inputs where datet <= '''||x_to_date||''' and rownum = 1 order by datet desc';
l_sql := 'insert into '|| g_schema ||'.'||l_table_name||'(sdate, level1)'||
'select '''||x_from_date||''', teo.organization from '||g_schema||'.t_ep_organization teo '||
'where teo.organization in
(SELECT mtp.organization_code
FROM msc_instance_orgs mio,
msc_trading_partners mtp
WHERE mio.sr_instance_id = '||p_sr_instance_id||
' AND nvl(mio.org_group, ''-888'') = decode('''||p_collection_group||''', ''-999'', nvl(mio.org_group, ''-888''), '''||p_collection_group||''')'||
' AND nvl(mio.dp_enabled_flag, mio.enabled_flag) = 1 '||
' AND mtp.sr_instance_id = mio.sr_instance_id ' ||
' AND mtp.sr_tp_id = mio.organization_id '||
' AND mtp.partner_type = 3) ';
SELECT owner
FROM dba_objects
WHERE owner = owner
AND object_type = 'TABLE'
AND object_name = 'MDP_MATRIX'
ORDER BY created desc;
msd_dem_common_utilities.log_message ('An Error occured in API call MSD_DEM_COLLECT_RETURN_HISTORY.COLLECT_RETURN_HISTORY_DATA,while inserting Total Backlog Data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
msd_dem_common_utilities.log_message ('Exception while inserting Total Backlog Data in the table BIIIO_SCI_BACKLOG - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
msd_dem_common_utilities.log_message ('Warning Text for Total Backlog Insertion is - ' || l_errbuff1);
l_sql_stmnt := ' update '||x_dest_table||' bsb '
||' set level3 = ( select mtp.partner_id '
||' from msc_trading_partners mtp '
||' where mtp.partner_name = bsb.level3 '
||' and mtp.partner_type = 2 )'
||' WHERE LEVEL3 <> ''' || msd_dem_sr_util.get_null_code || ''' ';
l_sql_stmnt := ' UPDATE ' || x_dest_table
|| ' SET level3 = ''' || msd_dem_sr_util.get_null_code || ''' '
|| ' WHERE level3 IS NULL ';
/* update dmtra_template.BIIO_SCI_BACKLOG bsb
set level3 = ( select mtp.partner_name
from msc_trading_partners mtp,
msc_tp_id_lid mtil
where mtil.sr_tp_id = bsb.level3_sr_pk
and mtil.sr_instance_id = (select instance_id
from msc_apps_instances mai
where mai.instance_code = substr(bsb.level2,1,instr(bsb.level2,':')-1)
)
and mtil.partner_type = 2
and mtil.tp_id = mtp.partner_id
); */
msd_dem_common_utilities.log_message ('An Error occured in API call MSD_DEM_COLLECT_RETURN_HISTORY.COLLECT_RETURN_HISTORY_DATA,while inserting On Hand Inventory Data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
msd_dem_common_utilities.log_message ('Exception while inserting ON-Hand Inventory Data in the table BIIIO_SCI - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
msd_dem_common_utilities.log_message ('Warning Text for On-Hand Inventory Insertion is - ' || l_errbuff1);
/*Delete all data (if any) from the staging table */
x_small_sql := 'TRUNCATE TABLE ' || var_schema || '.BIIO_SOP_ITEM_COST';
/*Delete all data (if any) from the ERR staging table */
x_small_sql := 'TRUNCATE TABLE ' || var_schema || '.BIIO_SOP_ITEM_COST_ERR';
sql_statement:= ' INSERT INTO ' || var_schema || '. BIIO_SOP_ITEM_COST (SDATE, LEVEL1, COST_PRODUCE,
COST_PURCHASE, COST_REPAIR, COST_RESOURCE)
Select (Select Bucket_date from msd_dem_day_dates where day_date=trunc(sysdate)),tei.item item_name,null,null,null,
avg(mds.resource_cost) ResourceCost
from msc_Department_Resources mds,' || var_schema || '.T_EP_Organization teo,msc_trading_partners mtp,
' || var_schema || '.T_EP_Item tei
where mds.plan_id=-1
and mds.sr_instance_id= mtp.sr_instance_id
and mds.sr_instance_id='''||p_sr_instance_id||'''
and mds.resource_code=tei.item
and mtp.partner_type=3
and mds.organization_id=mtp.sr_tp_id
and mtp.organization_code = teo.organization
and mds.resource_cost is not null
group by tei.item
union all
select
(Select Bucket_date from msd_dem_day_dates where day_date=trunc(sysdate)),tei.item item_name,
avg(msi.standard_cost) StandardCost,
avg(mis.item_price) ItemPrice,
avg(mia.repair_cost) RepairCost,null
from
' || var_schema || '.t_ep_organization teo,
msc_trading_partners mtp,
' || var_schema || '.t_ep_item tei,
msc_system_items msi,
msc_item_suppliers mis,
msc_item_attributes mia
where
mtp.organization_code = teo.organization
and mtp.partner_type = 3
and msi.plan_id = -1
and msi.sr_instance_id = mtp.sr_instance_id
and msi.organization_id = mtp.sr_tp_id
and msi.Sr_instance_id='''||p_sr_instance_id||'''
and msi.item_name = tei.item
and mis.plan_id (+) = msi.plan_id
and mis.sr_instance_id (+) = msi.sr_instance_id
and mis.organization_id(+) = msi.organization_id
and mis.inventory_item_id(+) = msi.inventory_item_id
and mia.simulation_set_id(+) = '''||var_sim_set_id||'''
and mia.plan_id(+) = msi.plan_id
and mia.sr_instance_id(+) = msi.sr_instance_id
and mia.organization_id(+) = msi.organization_id
and mia.inventory_item_id(+) = msi.inventory_item_id
and (msi.standard_cost IS NOT NULL OR mis.item_price IS NOT NULL OR mia.repair_cost IS NOT NULL )
group by tei.item';
msd_dem_common_utilities.log_debug('The number of records inserted is - ' ||SQL%ROWCOUNT);
msd_dem_common_utilities.log_debug('The insert statement was successful');
/* Update Purchase Cost from Source */
sql_statement := 'UPDATE ' || var_schema || '.BIIO_SOP_ITEM_COST target '
|| ' SET COST_PURCHASE = nvl((SELECT source.list_price_per_unit FROM mtl_system_items_kfv' || x_dblink || ' source '
|| ' WHERE source.concatenated_segments = target.level1 '
|| ' AND source.organization_id = ' || to_char(x_master_org) || '), target.cost_purchase) '
|| ' WHERE cost_purchase IS NULL ';
msd_dem_common_utilities.log_debug('The number of records updated is - ' ||SQL%ROWCOUNT);
msd_dem_common_utilities.log_debug('The update statement was successful');
FOR rec IN (SELECT job from user_jobs WHERE upper(what) like v_job_name || '%' and upper(broken) = 'N')
LOOP
dbms_job.remove (rec.job);