[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_delete_flag IN VARCHAR2,
p_collect_ISO IN NUMBER DEFAULT SYS_NO, /* Bug# 4615390 ISO, Bug# 4865396 */
p_order_type_flag IN NUMBER DEFAULT C_ALL, /* Bug# 4747555*/
p_order_type_ids IN VARCHAR2 DEFAULT NULL) IS
v_instance_id varchar2(40);
- to identify whether we need to insert the
- data into the staging tables or the
- fact tables.
- 3. Check for the Data Duplication, we should
- use the shipped_date for this
fact data.
- 4. Insert the Data accordingly into the
- Staging or the Fact table based on the
- MSD_SR_SHIPMENT_DATA_V.
- 5. Commit
****************************************************/
retcode :=0;
/* Physically delete existing data before inserting new rows*/
v_sql_stmt := 'DELETE FROM msd_st_shipment_data '||
' WHERE instance = ''' || p_instance_id || '''' ;
if p_delete_flag = 'Y' then
EXECUTE IMMEDIATE v_sql_stmt
USING nvl(p_from_date, C_FROM_DATE),
nvl(p_to_date, C_TO_DATE);
/* DWK Added sr_original_item_pk to insert stmt */
/* Bug# 4615390 ISO , added ORDER_SOURCE_ID to the insert stmt */
v_sql_stmt := 'INSERT INTO ' || p_dest_table ||
' (instance, inv_org, item, customer, sales_channel, '||
'sales_rep, ship_to_loc, user_defined1, user_defined2, '||
'sr_inv_org_pk, sr_item_pk, sr_customer_pk, sr_sales_channel_pk, '||
'sr_sales_rep_pk, sr_ship_to_loc_pk, sr_user_defined1_pk, sr_user_defined2_pk, '||
'booked_date, requested_date, promised_date, shipped_date, ' ||
'scheduled_ship_date, scheduled_arrival_date, amount, qty_shipped, creation_date, created_by, ' ||
'last_update_date, last_updated_by, last_update_login, '||
'sr_original_item_pk, sr_parent_item_pk, sr_demand_class_pk, ORDER_SOURCE_ID ) '||
'SELECT '''||p_instance_id ||
''', inv_org, item, customer, sales_channel, sales_rep, '||
'ship_to_loc, user_defined1, user_defined2, '||
'nvl(sr_inv_org_pk, msd_sr_util.get_null_pk), ' ||
'nvl(sr_item_pk, msd_sr_util.get_null_pk), ' ||
'nvl(sr_customer_pk, msd_sr_util.get_null_pk), ' ||
'nvl(sr_sales_channel_pk, msd_sr_util.get_null_pk), '||
'nvl(sr_sales_rep_pk, msd_sr_util.get_null_pk), ' ||
'nvl(sr_ship_to_loc_pk, msd_sr_util.get_null_pk), ' ||
'nvl(sr_user_defined1_pk, msd_sr_util.get_null_pk), ' ||
'nvl(sr_user_defined2_pk, msd_sr_util.get_null_pk), '||
'booked_date,'||
'requested_date, promised_date, shipped_date, scheduled_ship_date, scheduled_arrival_date, amount, '||
'qty_shipped, sysdate, '|| FND_GLOBAL.USER_ID ||
', sysdate, '|| FND_GLOBAL.USER_ID ||', '|| FND_GLOBAL.USER_ID ||
', to_char(sr_original_item_pk), to_char(sr_parent_item_pk), to_char(sr_demand_class_pk) ' ||
', ORDER_SOURCE_ID ' ||
'FROM ' || p_source_table ||' where 1 = 1';
/* Mark delete for overlapping rows and Update its last_refresh_num */
v_sql_stmt := ' UPDATE msd_shipment_data ' ||
' SET last_refresh_num = ' || p_new_refresh_num ||
', Action_code = ' || '''D''' ||
' WHERE Action_code = ''I'' and instance = '||p_instance_id || v_date_range;
/* DWK Added sr_original_item_pk to insert stmt */
/* Bug# 4615390 ISO , added ORDER_SOURCE_ID to the insert stmt */
v_sql_stmt := 'INSERT INTO ' || p_dest_table ||
' (instance, inv_org, item, customer, sales_channel, '||
'sales_rep, ship_to_loc, user_defined1, user_defined2, '||
'sr_inv_org_pk, sr_item_pk, sr_customer_pk, sr_sales_channel_pk, '||
'sr_sales_rep_pk, sr_ship_to_loc_pk, sr_user_defined1_pk, sr_user_defined2_pk, '||
'booked_date, requested_date, promised_date, shipped_date, ' ||
'scheduled_ship_date, scheduled_arrival_date, amount, ' ||
'qty_shipped, creation_date, created_by, ' ||
'last_update_date, last_updated_by, last_update_login, '||
'last_refresh_num, created_by_refresh_num, action_code, '||
'sr_original_item_pk, sr_parent_item_pk, sr_demand_class_pk, ORDER_SOURCE_ID ) '||
'SELECT '''||p_instance_id ||
''', inv_org, item, customer, sales_channel, sales_rep, '||
'ship_to_loc, user_defined1, user_defined2, '||
'nvl(sr_inv_org_pk, msd_sr_util.get_null_pk), ' ||
'nvl(sr_item_pk, msd_sr_util.get_null_pk), ' ||
'nvl(sr_customer_pk, msd_sr_util.get_null_pk), ' ||
'nvl(sr_sales_channel_pk, msd_sr_util.get_null_pk), '||
'nvl(sr_sales_rep_pk, msd_sr_util.get_null_pk), ' ||
'nvl(sr_ship_to_loc_pk, msd_sr_util.get_null_pk), ' ||
'nvl(sr_user_defined1_pk, msd_sr_util.get_null_pk), ' ||
'nvl(sr_user_defined2_pk, msd_sr_util.get_null_pk), '||
'booked_date,'||
'requested_date, promised_date, shipped_date, ' ||
'scheduled_ship_date, scheduled_arrival_date, ' ||
'amount, qty_shipped, sysdate, '|| FND_GLOBAL.USER_ID ||
', sysdate, '|| FND_GLOBAL.USER_ID ||', '|| FND_GLOBAL.USER_ID ||
', '|| p_new_refresh_num || ', ' || p_new_refresh_num || ', '|| '''I''' ||
', to_char(sr_original_item_pk), to_char(sr_parent_item_pk), to_char(sr_demand_class_pk) ' ||
', ORDER_SOURCE_ID ' ||
'FROM ' || p_source_table ||' where 1 = 1';
/* DWK. if the source is from staging table, then we shouldn't insert instance = '0'
rows into fact table*/
if (p_source_table = MSD_COMMON_UTILITIES.SHIPMENT_STAGING_TABLE) then
v_sql_stmt := v_sql_stmt || ' and nvl(instance,''888'') <> '||'''0''';
select 1 into v_ref_num
from msd_cs_data_headers
where cs_definition_id in (select cs_definition_id
from msd_cs_definitions
where name = 'MSD_SHIPMENT_HISTORY');
update msd_cs_data_headers
set last_refresh_num = p_new_refresh_num
where cs_definition_id in (select cs_definition_id
from msd_cs_definitions
where name in ('MSD_SHIPMENT_HISTORY','MSD_SHIPMENT_ORIG_HISTORY'));
p_delete_flag IN VARCHAR2,
p_collect_ISO IN NUMBER DEFAULT SYS_NO, /* Bug# 4615390 ISO, Bug# 4865396 */
p_order_type_flag IN NUMBER DEFAULT C_ALL, /* Bug# 4747555*/
p_order_type_ids IN VARCHAR2 DEFAULT NULL) IS
v_instance_id varchar2(40);
- to identify whether we need to insert the
- data into the staging tables or the
- fact tables.
- 3. Check for the Data Duplication, we should
- use the shipped_date for this fact data.
- 4. Insert the Data accordingly into the
- Staging or the Fact table based on the
- MSD_SR_BOOKING_DATA_V.
- 5. Commit
****************************************************/
retcode :=0;
/* Physically delete existing data before inserting new rows*/
v_sql_stmt := 'DELETE FROM msd_st_booking_data '||
' WHERE instance = ''' || p_instance_id || '''' ;
if p_delete_flag = 'Y' then
EXECUTE IMMEDIATE v_sql_stmt
USING nvl(p_from_date, C_FROM_DATE ),
nvl(p_to_date, C_TO_DATE );
/* DWK Added sr_original_item_pk to insert stmt */
/* Bug# 4615390 ISO , added ORDER_SOURCE_ID to the insert stmt */
v_sql_stmt := 'INSERT INTO ' || p_dest_table ||
' (instance, inv_org, item, customer, sales_channel, '||
'sales_rep, ship_to_loc, user_defined1, user_defined2, '||
'sr_inv_org_pk, sr_item_pk, sr_customer_pk, sr_sales_channel_pk, '||
'sr_sales_rep_pk, sr_ship_to_loc_pk, sr_user_defined1_pk, sr_user_defined2_pk, '||
'booked_date, requested_date, promised_date, '||
'scheduled_date, scheduled_arrival_date, ' ||
'amount, qty_ordered, creation_date, created_by, ' ||
'last_update_date, last_updated_by, last_update_login, '||
'sr_original_item_pk, sr_parent_item_pk, sr_demand_class_pk, ORDER_SOURCE_ID) '||
'SELECT '''||p_instance_id ||
''', inv_org, item, customer, sales_channel, sales_rep, '||
'ship_to_loc, user_defined1, user_defined2, '||
'nvl(sr_inv_org_pk, msd_sr_util.get_null_pk), ' ||
'nvl(sr_item_pk, msd_sr_util.get_null_pk), ' ||
'nvl(sr_customer_pk, msd_sr_util.get_null_pk), ' ||
'nvl(sr_sales_channel_pk, msd_sr_util.get_null_pk), '||
'nvl(sr_sales_rep_pk, msd_sr_util.get_null_pk), ' ||
'nvl(sr_ship_to_loc_pk, msd_sr_util.get_null_pk), ' ||
'nvl(sr_user_defined1_pk, msd_sr_util.get_null_pk), ' ||
'nvl(sr_user_defined2_pk, msd_sr_util.get_null_pk), '||
'booked_date,'||
'requested_date, promised_date, scheduled_ship_date, scheduled_arrival_date , '||
'amount, qty_ordered, sysdate, '|| FND_GLOBAL.USER_ID ||
', sysdate, '|| FND_GLOBAL.USER_ID || ', '|| FND_GLOBAL.USER_ID ||
', to_char(sr_original_item_pk), ' ||
' to_char(sr_parent_item_pk), to_char(sr_demand_class_pk), ORDER_SOURCE_ID ' ||
'from ' || p_source_table ||' where 1 = 1';
/* Mark delete for overlapping rows and Update its last_refresh_num */
v_sql_stmt := ' UPDATE msd_booking_data ' ||
' SET last_refresh_num = ' || p_new_refresh_num ||
', Action_code = ' || '''D''' ||
' WHERE Action_code = ''I'' and instance = '||p_instance_id || v_date_range;
/* Bug# 4615390 ISO , added ORDER_SOURCE_ID to the insert stmt */
v_sql_stmt := 'INSERT INTO ' || p_dest_table ||
' (instance, inv_org, item, customer, sales_channel, '||
'sales_rep, ship_to_loc, user_defined1, user_defined2, '||
'sr_inv_org_pk, sr_item_pk, sr_customer_pk, sr_sales_channel_pk, '||
'sr_sales_rep_pk, sr_ship_to_loc_pk, sr_user_defined1_pk, sr_user_defined2_pk, '||
'booked_date, requested_date, promised_date, '||
'scheduled_date, scheduled_arrival_date, ' ||
'amount, qty_ordered, creation_date, created_by, ' ||
'last_update_date, last_updated_by, last_update_login, '||
'last_refresh_num, created_by_refresh_num, action_code, '||
'sr_original_item_pk, sr_parent_item_pk, sr_demand_class_pk, ORDER_SOURCE_ID ) '||
'SELECT '''||p_instance_id ||
''', inv_org, item, customer, sales_channel, sales_rep, '||
'ship_to_loc, user_defined1, user_defined2, '||
'nvl(sr_inv_org_pk, msd_sr_util.get_null_pk), ' ||
'nvl(sr_item_pk, msd_sr_util.get_null_pk), ' ||
'nvl(sr_customer_pk, msd_sr_util.get_null_pk), ' ||
'nvl(sr_sales_channel_pk, msd_sr_util.get_null_pk), '||
'nvl(sr_sales_rep_pk, msd_sr_util.get_null_pk), ' ||
'nvl(sr_ship_to_loc_pk, msd_sr_util.get_null_pk), ' ||
'nvl(sr_user_defined1_pk, msd_sr_util.get_null_pk), ' ||
'nvl(sr_user_defined2_pk, msd_sr_util.get_null_pk), '||
'booked_date,'||
'requested_date, promised_date, scheduled_date, scheduled_arrival_date, '||
'amount, qty_ordered, sysdate, '|| FND_GLOBAL.USER_ID ||
', sysdate, '|| FND_GLOBAL.USER_ID || ', '|| FND_GLOBAL.USER_ID ||
', '|| p_new_refresh_num || ', ' || p_new_refresh_num || ', '|| '''I''' ||
', to_char(sr_original_item_pk), to_char(sr_parent_item_pk), to_char(sr_demand_class_pk) ' ||
', ORDER_SOURCE_ID ' ||
'from ' || p_source_table ||' where 1 = 1';
/* DWK. if the source is from staging table, then we shouldn't insert instance = '0'
rows into fact table*/
if (p_source_table = MSD_COMMON_UTILITIES.BOOKING_STAGING_TABLE) then
v_sql_stmt := v_sql_stmt || ' and nvl(instance,''888'') <> '||'''0''';
select 1 into v_ref_num
from msd_cs_data_headers
where cs_definition_id in (select cs_definition_id
from msd_cs_definitions
where name = 'MSD_BOOKING_HISTORY');
update msd_cs_data_headers
set last_refresh_num = p_new_refresh_num
where cs_definition_id in (select cs_definition_id
from msd_cs_definitions
where name in ('MSD_BOOKING_HISTORY','MSD_BOOKING_ORIG_HISTORY'));
/* Cursor for delete */
/* Changed for bug # 3752937. Only those records should be deleted which are in staging */
CURSOR c_delete IS
SELECT from_uom_class, to_uom_class,
from_uom_code, to_uom_code,
base_uom_flag, sr_item_pk
FROM msd_uom_conversions
WHERE instance = p_instance_id
INTERSECT
SELECT from_uom_class, to_uom_class,
from_uom_code, to_uom_code,
base_uom_flag, sr_item_pk
FROM msd_st_uom_conversions
WHERE instance = p_instance_id;
/* Cursor for insert */
CURSOR c_insert IS
SELECT from_uom_class, to_uom_class,
from_uom_code, to_uom_code,
base_uom_flag, conversion_rate, sr_item_pk, item
FROM msd_st_uom_conversions
WHERE instance = p_instance_id and
nvl(instance,-999) <> 0
MINUS
SELECT from_uom_class, to_uom_class,
from_uom_code, to_uom_code,
base_uom_flag, conversion_rate, sr_item_pk, item
FROM msd_uom_conversions
WHERE instance = p_instance_id and
nvl(instance,-999) <> 0;
- to identify whether we need to insert the
- data into the staging tables or the
- fact tables.
- 3. Do a complete refresh for this instance,
- hence delete all the underlying values.
- 4. Insert the Data accordingly into the
- Staging or the Fact table based on the
- MSD_SR_UOM_CONVERSION_V
- 5. Commit
****************************************************/
v_instance_id := p_instance_id;
DELETE FROM msd_st_uom_conversions
WHERE instance = p_instance_id;
v_sql_stmt := 'INSERT INTO ' || p_dest_table ||
' ( INSTANCE, SR_ITEM_PK, ITEM, FROM_UOM_CLASS, TO_UOM_CLASS, ' ||
' FROM_UOM_CODE, TO_UOM_CODE, BASE_UOM_FLAG, ' ||
' CONVERSION_RATE, CREATION_DATE, CREATED_BY, ' ||
' LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN )'||
' SELECT ' || NVL(v_instance_id, 'INSTANCE') || ', ' ||
' decode(SR_ITEM_PK, ''0'', null, SR_ITEM_PK), ITEM, FROM_UOM_CLASS, TO_UOM_CLASS, ' ||
' FROM_UOM_CODE, TO_UOM_CODE, BASE_UOM_FLAG, ' ||
' CONVERSION_RATE, sysdate, '|| FND_GLOBAL.USER_ID ||
', sysdate, '|| FND_GLOBAL.USER_ID ||', '|| FND_GLOBAL.USER_ID ||' ' ||
' from ' || p_source_table || ' where 1 = 1';
OPEN c_delete;
FETCH c_delete BULK COLLECT INTO a_from_uom_class, a_to_uom_class, a_from_uom_code,
a_to_uom_code, a_base_uom_flag, a_sr_item_pk;
CLOSE c_delete;
DELETE FROM msd_uom_conversions
WHERE instance = p_instance_id and
nvl(sr_item_pk,'NULL') = nvl(a_sr_item_pk(i), 'NULL') and
from_uom_class = a_from_uom_class(i) and
to_uom_class = a_to_uom_class(i) and
from_uom_code = a_from_uom_code(i) and
to_uom_code = a_to_uom_code(i) and
base_uom_flag = a_base_uom_flag(i);
OPEN c_insert;
FETCH c_insert BULK COLLECT INTO a_from_uom_class, a_to_uom_class, a_from_uom_code,
a_to_uom_code, a_base_uom_flag, a_conversion_rate,
a_sr_item_pk, a_item;
CLOSE c_insert;
INSERT INTO msd_uom_conversions(
INSTANCE, SR_ITEM_PK, ITEM, FROM_UOM_CLASS, TO_UOM_CLASS,
FROM_UOM_CODE, TO_UOM_CODE, BASE_UOM_FLAG,
CONVERSION_RATE, CREATION_DATE, CREATED_BY,
LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
last_refresh_num, created_by_refresh_num, action_code)
VALUES ( p_instance_id, a_sr_item_pk(i), a_item(i),
a_from_uom_class(i), a_to_uom_class(i),
a_from_uom_code(i), a_to_uom_code(i), a_base_uom_flag(i),
a_conversion_rate(i), sysdate, FND_GLOBAL.USER_ID,
sysdate, FND_GLOBAL.USER_ID, FND_GLOBAL.USER_ID,
p_new_refresh_num, p_new_refresh_num, 'I');
- to identify whether we need to insert the
- data into the staging tables or the
- fact tables.
- 3. Do a complete refresh for this instance,
- hence delete all the underlying values.
- 4. Insert the Data accordingly into the
- Staging or the Fact table based on the
- MSD_SR_CURRENCY_CONVERSION_V
- 5. Commit
****************************************************/
retcode :=0;
v_sql_stmt := 'DELETE FROM ' || p_dest_table ||
' where 1 = 1';
/* DWK. If dest_table is staging table, then we shouldn't delete
instance = '0' row */
IF (p_dest_table = MSD_COMMON_UTILITIES.CURRENCY_STAGING_TABLE) THEN
v_sql_stmt := v_sql_stmt || ' and nvl(instance, ''888'') <> '||'''0''';
v_sql_stmt := 'INSERT INTO ' || p_dest_table ||
' ( FROM_CURRENCY, TO_CURRENCY, ' ||
' CONVERSION_DATE, CONVERSION_RATE, ' ||
' CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, ' ||
' LAST_UPDATED_BY, LAST_UPDATE_LOGIN )'||
' SELECT ' ||
' FROM_CURRENCY, TO_CURRENCY, ' ||
' CONVERSION_DATE, CONVERSION_RATE, ' ||
' sysdate, '|| FND_GLOBAL.USER_ID || ', sysdate, ' ||
FND_GLOBAL.USER_ID ||', '|| FND_GLOBAL.USER_ID ||' ' ||
'from ' || p_source_table || ' where 1 = 1';
/* DWK. if the source is from staging table, then we shouldn't insert instance = '0'
rows into fact table*/
if p_source_table = MSD_COMMON_UTILITIES.CURRENCY_STAGING_TABLE then
v_sql_stmt := v_sql_stmt || ' and nvl(instance, ''888'') <> '||'''0''';
--insert into msd_test values(v_sql_stmt) ;
- to identify whether we need to insert the
- data into the staging tables or the
- fact tables.
- 3. Check for the Data Duplication, we should
- use the ship_date for this fact.
- 4. Insert the Data accordingly into the
- Staging or the Fact table based on the
- MSD_SR_OPPORTUNITIES_DATA_V
- 5. Commit
****************************************************/
v_sql_stmt := 'DELETE FROM ' || p_dest_table ||
' where instance = ''' || p_instance_id || '''' ;
v_sql_stmt := 'INSERT INTO ' || p_dest_table ||
' (instance, lead_number, interest_type, primary_interest_code, ' ||
' secondary_interest_code, item, inv_org, quantity, amount, '||
' customer, ship_to_loc, sales_channel, sales_rep, '||
' user_defined1, user_defined2, ship_date, win_probability, '||
'sr_inv_org_pk, sr_item_pk, sr_customer_pk, sr_sales_channel_pk, '||
'sr_sales_rep_pk, sr_ship_to_loc_pk, sr_user_defined1_pk, sr_user_defined2_pk, '||
' status, creation_date, created_by, last_update_date, '||
' last_updated_by, last_update_login ) '||
'select '''||p_instance_id ||
''', lead_number, interest_type, primary_interest_code, ' ||
' secondary_interest_code, item, inv_org, quantity, amount, '||
' customer, ship_to_loc, sales_channel, sales_rep, '||
' user_defined1, user_defined2, nvl(ship_date, sysdate), win_probability, '||
'nvl(sr_inv_org_pk, msd_sr_util.get_null_pk), ' ||
'nvl(sr_item_pk, msd_sr_util.get_null_pk), ' ||
'nvl(sr_customer_pk, msd_sr_util.get_null_pk), ' ||
'nvl(sr_sales_channel_pk, msd_sr_util.get_null_pk), '||
'nvl(sr_sales_rep_pk, msd_sr_util.get_null_pk), ' ||
'nvl(sr_ship_to_loc_pk, msd_sr_util.get_null_pk), ' ||
'nvl(sr_user_defined1_pk, msd_sr_util.get_null_pk), ' ||
'nvl(sr_user_defined2_pk, msd_sr_util.get_null_pk), '||
' status, sysdate, '|| FND_GLOBAL.USER_ID || ', sysdate, ' ||
FND_GLOBAL.USER_ID ||', '|| FND_GLOBAL.USER_ID ||' ' ||
'from ' || p_source_table || ' where 1 = 1';
/* DWK. if the source is from staging table, then we shouldn't insert instance = '0'
rows into fact table*/
if p_source_table = MSD_COMMON_UTILITIES.OPPORTUNITY_STAGING_TABLE then
v_sql_stmt := v_sql_stmt || ' and nvl(instance,''888'') <> '||'''0''';
- to identify whether we need to insert the
- data into the staging tables or the
- fact tables.
- 3. Check for the Data Duplication, we should
- use the from_date, to_date and period_name
- for this fact.
- 4. Insert the Data accordingly into the
- Staging or the Fact table based on the
- MSD_SR_SALES_FCST_V
- 5. Commit
****************************************************/
retcode :=0;
v_sql_stmt := 'DELETE FROM ' || p_dest_table ||
' where instance = ''' || p_instance_id ||'''' ;
v_sql_stmt := 'INSERT INTO ' || p_dest_table ||
' (instance, forecast_designator, inv_org, interest_type, ' ||
' PRIMARY_INTEREST_CODE, SECONDARY_INTEREST_CODE, item, ' ||
' CUSTOMER, SALES_CHANNEL, SALES_REP, SALES_GROUP, SHIP_TO_LOC, ' ||
' USER_DEFINED1, USER_DEFINED2, PERIOD_NAME, PERIOD_START_DATE, ' ||
' PERIOD_END_DATE, FORECAST_AMOUNT, UPSIDE_AMOUNT, QUOTA_AMOUNT, ' ||
'sr_inv_org_pk, sr_item_pk, sr_customer_pk, sr_sales_channel_pk, '||
'sr_sales_rep_pk, sr_ship_to_loc_pk, sr_user_defined1_pk, sr_user_defined2_pk, '||
' creation_date, created_by, last_update_date, '||
' last_updated_by, last_update_login ) '||
'select '''||p_instance_id ||
''', NULL, inv_org, interest_type, ' ||
' PRIMARY_INTEREST_CODE, SECONDARY_INTEREST_CODE, item, ' ||
' CUSTOMER, SALES_CHANNEL, SALES_REP, SALES_GROUP, SHIP_TO_LOC, ' ||
' USER_DEFINED1, USER_DEFINED2, PERIOD_NAME, nvl(PERIOD_START_DATE, sysdate), ' ||
' nvl(PERIOD_END_DATE, sysdate), FORECAST_AMOUNT, UPSIDE_AMOUNT, QUOTA_AMOUNT, ' ||
'nvl(sr_inv_org_pk, msd_sr_util.get_null_pk), ' ||
'nvl(sr_item_pk, msd_sr_util.get_null_pk), ' ||
'nvl(sr_customer_pk, msd_sr_util.get_null_pk), ' ||
'nvl(sr_sales_channel_pk, msd_sr_util.get_null_pk), '||
'nvl(sr_sales_rep_pk, msd_sr_util.get_null_pk), ' ||
'nvl(sr_ship_to_loc_pk, msd_sr_util.get_null_pk), ' ||
'nvl(sr_user_defined1_pk, msd_sr_util.get_null_pk), ' ||
'nvl(sr_user_defined2_pk, msd_sr_util.get_null_pk), '||
' sysdate, '|| FND_GLOBAL.USER_ID || ', sysdate, ' ||
FND_GLOBAL.USER_ID ||', '|| FND_GLOBAL.USER_ID ||' ' ||
' from ' || p_source_table || ' where 1 = 1';
/* DWK. if the source is from staging table, then we shouldn't insert instance = '0'
rows into fact table*/
IF p_source_table = MSD_COMMON_UTILITIES.SALES_FCST_STAGING_TABLE then
v_sql_stmt := v_sql_stmt || ' and nvl(instance,''888'') <> '||'''0''';
p_delete_flag IN VARCHAR2) IS
v_instance_id varchar2(40);
SELECT cs_definition_id
FROM msd_cs_definitions
WHERE name = p_cs_name;
- to identify whether we need to insert the
- data into the staging tables or the
- fact tables.
- 3. Check for the Data Duplication, we should
- use the forecast_designator for this fact.
- 4. Insert the Data accordingly into the
- Staging or the Fact table based on the
- MSD_SR_MFG_FCST_V
- 5. Commit
****************************************************/
retcode :=0;
v_sql_stmt := ' DELETE FROM ' || p_dest_table ||
' where instance = ''' || p_instance_id || '''' ||
' and forecast_designator = nvl(:p_fcst_desg, forecast_designator) ' ;
if p_delete_flag = 'Y' then
EXECUTE IMMEDIATE v_sql_stmt USING p_fcst_desg ;
v_sql_stmt := 'INSERT INTO ' || p_dest_table ||
' (instance, forecast_designator, prd_level_id, ITEM, INV_ORG, CUSTOMER, ' ||
' SALES_CHANNEL, SHIP_TO_LOC, USER_DEFINED1, USER_DEFINED2, ' ||
' BUCKET_TYPE, FORECAST_DATE, RATE_END_DATE, ' ||
' ORIGINAL_QUANTITY, CURRENT_QUANTITY, ' ||
'sr_inv_org_pk, sr_item_pk, sr_customer_pk, sr_sales_channel_pk, '||
'sr_ship_to_loc_pk, sr_user_defined1_pk, sr_user_defined2_pk, '||
' creation_date, created_by, last_update_date, '||
' last_updated_by, last_update_login, sr_demand_class_pk ) '||
'SELECT '''||p_instance_id ||
''', forecast_designator, prd_level_id, ITEM, INV_ORG, CUSTOMER, ' ||
' SALES_CHANNEL, SHIP_TO_LOC, USER_DEFINED1, USER_DEFINED2, ' ||
v_bucket_sql ||
' FORECAST_DATE, RATE_END_DATE, ' ||
' ORIGINAL_QUANTITY, CURRENT_QUANTITY, ' ||
'nvl(sr_inv_org_pk, msd_sr_util.get_null_pk), ' ||
'nvl(sr_item_pk, msd_sr_util.get_null_pk), ' ||
'nvl(sr_customer_pk, msd_sr_util.get_null_pk), ' ||
'nvl(sr_sales_channel_pk, msd_sr_util.get_null_pk), '||
'nvl(sr_ship_to_loc_pk, msd_sr_util.get_null_pk), ' ||
'nvl(sr_user_defined1_pk, msd_sr_util.get_null_pk), ' ||
'nvl(sr_user_defined2_pk, msd_sr_util.get_null_pk), '||
' sysdate, '|| FND_GLOBAL.USER_ID || ', sysdate, ' ||
FND_GLOBAL.USER_ID ||', '|| FND_GLOBAL.USER_ID ||', ' ||
'to_char(sr_demand_class_pk) ' ||
' FROM ' || p_source_table ||
' WHERE forecast_designator = nvl(:p_fcst_desg, forecast_designator) ';
v_sql_stmt := ' UPDATE msd_mfg_forecast ' ||
' SET last_refresh_num = ' || p_new_refresh_num ||
', Action_code = ' || '''D''' ||
' WHERE Action_code = ''I'' and instance = ' ||
p_instance_id || ' and forecast_designator = ' ||
' nvl(:p_fcst_desg, forecast_designator) ' ;
/* Delete Cs Data Headers */
delete from msd_cs_data_headers
where cs_definition_id = x_cs_id
and instance = p_instance_id
and cs_name = p_fcst_desg;
v_sql_stmt := 'INSERT INTO ' || p_dest_table ||
' ( instance, forecast_designator, prd_level_id, ITEM, INV_ORG, CUSTOMER, ' ||
' SALES_CHANNEL, SHIP_TO_LOC, USER_DEFINED1, USER_DEFINED2, ' ||
' BUCKET_TYPE, FORECAST_DATE, RATE_END_DATE, ' ||
' ORIGINAL_QUANTITY, CURRENT_QUANTITY, ' ||
' sr_inv_org_pk, sr_item_pk, sr_customer_pk, sr_sales_channel_pk, '||
' sr_ship_to_loc_pk, sr_user_defined1_pk, sr_user_defined2_pk, '||
' last_refresh_num, created_by_refresh_num, action_code, '||
' creation_date, created_by, last_update_date, '||
' last_updated_by, last_update_login, sr_demand_class_pk ) '||
'SELECT '''||p_instance_id ||
''', forecast_designator, prd_level_id, ITEM, INV_ORG, CUSTOMER, ' ||
' SALES_CHANNEL, SHIP_TO_LOC, USER_DEFINED1, USER_DEFINED2, ' ||
' bucket_type, FORECAST_DATE, RATE_END_DATE, ' ||
' ORIGINAL_QUANTITY, CURRENT_QUANTITY, ' ||
' nvl(sr_inv_org_pk, msd_sr_util.get_null_pk), ' ||
' nvl(sr_item_pk, msd_sr_util.get_null_pk), ' ||
' nvl(sr_customer_pk, msd_sr_util.get_null_pk), ' ||
' nvl(sr_sales_channel_pk, msd_sr_util.get_null_pk), '||
' nvl(sr_ship_to_loc_pk, msd_sr_util.get_null_pk), ' ||
' nvl(sr_user_defined1_pk, msd_sr_util.get_null_pk), ' ||
' nvl(sr_user_defined2_pk, msd_sr_util.get_null_pk), '||
p_new_refresh_num || ', ' || p_new_refresh_num || ', '|| '''I''' ||
', sysdate, '|| FND_GLOBAL.USER_ID || ', sysdate, ' ||
FND_GLOBAL.USER_ID ||', '|| FND_GLOBAL.USER_ID ||', ' ||
' to_char(sr_demand_class_pk) ' ||
' FROM ' || p_source_table ||
' WHERE forecast_designator = nvl(:p_fcst_desg, forecast_designator) ';
/* DWK. if the source is from staging table, then we shouldn't insert instance = '0'
rows into fact table*/
IF p_source_table = MSD_COMMON_UTILITIES.MFG_FCST_STAGING_TABLE then
v_sql_stmt := v_sql_stmt || ' and nvl(instance,''888'') <> '||'''0''';
INSERT INTO msd_cs_data_headers (
CS_DATA_HEADER_ID,
INSTANCE,
CS_DEFINITION_ID,
CS_NAME,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
LAST_REFRESH_NUM
)
VALUES ( msd_cs_data_headers_s.nextval,
p_instance_id,
x_cs_id,
p_fcst_desg,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
p_new_refresh_num
);
- to identify whether we need to insert the
- data into the staging tables or the
- fact tables.
- 3. Check for the Data Duplication, we should
- use the shipped_date for this fact data.
- 4. Insert the Data accordingly into the
- Staging or the Fact table based on the
- MSD_SR_PRICE_LIST_V.
- 5. Commit
****************************************************/
retcode :=0;
v_sql_stmt := 'DELETE FROM ' || p_dest_table ||
' where instance = ''' || p_instance_id || '''' ||
' and price_list_name like nvl(:p_price_list, price_list_name) ';
'INSERT INTO ' || p_dest_table ||
' ( INSTANCE, ' ||
' ORGANIZATION_LVL_ID, SR_ORGANIZATION_LVL_PK, ' ||
' PRODUCT_LVL_ID, SR_PRODUCT_LVL_PK, ' ||
' SALESCHANNEL_LVL_ID, SR_SALESCHANNEL_LVL_PK, ' ||
' SALES_REP_LVL_ID, SR_SALES_REP_LVL_PK, ' ||
' GEOGRAPHY_LVL_ID, SR_GEOGRAPHY_LVL_PK, ' ||
' USER_DEFINED1_LVL_ID,SR_USER_DEFINED1_LVL_PK,' ||
' USER_DEFINED2_LVL_ID,SR_USER_DEFINED2_LVL_PK,' ||
' DEMAND_CLASS_LVL_ID, SR_DEMAND_CLASS_LVL_PK,' ||
' PRICE_LIST_NAME, START_DATE, END_DATE, ' ||
' PRICE, PRIORITY, ' ||
' PRIMARY_UOM_FLAG, PRICE_LIST_UOM, ' ||
' CREATION_DATE, CREATED_BY, ' ||
' LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN) ' ||
'SELECT ''' || p_instance_id ||
''', nvl(ORGANIZATION_LVL_ID, 29) , nvl(SR_ORGANIZATION_LVL_PK,msd_sr_util.get_all_org_pk), ' ||
' nvl(PRODUCT_LVL_ID, 28) , nvl(SR_PRODUCT_LVL_PK,msd_sr_util.get_all_prd_pk), ' ||
' nvl(SALESCHANNEL_LVL_ID, 33) , nvl(SR_SALESCHANNEL_LVL_PK,msd_sr_util.get_all_scs_pk), ' ||
' nvl(SALES_REP_LVL_ID, 32) , nvl(SR_SALES_REP_LVL_PK,msd_sr_util.get_all_rep_pk), ' ||
' nvl(GEOGRAPHY_LVL_ID, 30) , nvl(SR_GEOGRAPHY_LVL_PK,msd_sr_util.get_all_geo_pk), ' ||
' USER_DEFINED1_LVL_ID,nvl(SR_USER_DEFINED1_LVL_PK,msd_sr_util.get_null_pk),' ||
' USER_DEFINED2_LVL_ID,nvl(SR_USER_DEFINED2_LVL_PK,msd_sr_util.get_null_pk),' ||
' nvl(DEMAND_CLASS_LVL_ID, 40), nvl(SR_DEMAND_CLASS_LVL_PK,msd_sr_util.get_null_pk),' ||
' PRICE_LIST_NAME, START_DATE, END_DATE, ' ||
' PRICE, PRIORITY, ' ||
' PRIMARY_UOM_FLAG, PRICE_LIST_UOM, ' ||
' SYSDATE,'|| FND_GLOBAL.USER_ID || ', ' ||
' SYSDATE,'|| FND_GLOBAL.USER_ID || ', '|| FND_GLOBAL.USER_ID ||' '||
'FROM ' || p_source_table || ' ' ||
'WHERE PRICE_LIST_NAME like NVL(:p_price_list, PRICE_LIST_NAME) ';
UPDATE msd_price_list
SET Action_code = 'D', last_refresh_num = p_new_refresh_num
WHERE Action_code = 'I' and instance = p_instance_id and
price_list_name like p_price_list;
UPDATE msd_price_list
SET Action_code = 'D', last_refresh_num = p_new_refresh_num
WHERE Action_code = 'I' and instance = p_instance_id;
'INSERT INTO ' || p_dest_table ||
' ( INSTANCE, ' ||
' ORGANIZATION_LVL_ID, SR_ORGANIZATION_LVL_PK, ' ||
' PRODUCT_LVL_ID, SR_PRODUCT_LVL_PK, ' ||
' SALESCHANNEL_LVL_ID, SR_SALESCHANNEL_LVL_PK, ' ||
' SALES_REP_LVL_ID, SR_SALES_REP_LVL_PK, ' ||
' GEOGRAPHY_LVL_ID, SR_GEOGRAPHY_LVL_PK, ' ||
' USER_DEFINED1_LVL_ID,SR_USER_DEFINED1_LVL_PK,' ||
' USER_DEFINED2_LVL_ID,SR_USER_DEFINED2_LVL_PK,' ||
' DEMAND_CLASS_LVL_ID, SR_DEMAND_CLASS_LVL_PK,' ||
' PRICE_LIST_NAME, START_DATE, END_DATE, ' ||
' PRICE, PRIORITY, ' ||
' last_refresh_num, created_by_refresh_num, action_code, '||
' CREATION_DATE, CREATED_BY, ' ||
' LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN) ' ||
'SELECT ''' || p_instance_id ||
''', nvl(ORGANIZATION_LVL_ID, 29) , nvl(SR_ORGANIZATION_LVL_PK,msd_sr_util.get_all_org_pk), ' ||
' nvl(PRODUCT_LVL_ID, 28) , nvl(SR_PRODUCT_LVL_PK,msd_sr_util.get_all_prd_pk), ' ||
' nvl(SALESCHANNEL_LVL_ID, 33) , nvl(SR_SALESCHANNEL_LVL_PK,msd_sr_util.get_all_scs_pk), ' ||
' nvl(SALES_REP_LVL_ID, 32) , nvl(SR_SALES_REP_LVL_PK,msd_sr_util.get_all_rep_pk), ' ||
' nvl(GEOGRAPHY_LVL_ID, 30) , nvl(SR_GEOGRAPHY_LVL_PK,msd_sr_util.get_all_geo_pk), ' ||
' USER_DEFINED1_LVL_ID,nvl(SR_USER_DEFINED1_LVL_PK,msd_sr_util.get_null_pk),' ||
' USER_DEFINED2_LVL_ID,nvl(SR_USER_DEFINED2_LVL_PK,msd_sr_util.get_null_pk),' ||
' nvl(DEMAND_CLASS_LVL_ID, 40) , nvl(SR_DEMAND_CLASS_LVL_PK,msd_sr_util.get_all_geo_pk), ' ||
' PRICE_LIST_NAME, START_DATE, END_DATE, ' ||
' PRICE, PRIORITY, ' ||
p_new_refresh_num || ', ' || p_new_refresh_num || ', '|| '''I''' || ', ' ||
' SYSDATE,'|| FND_GLOBAL.USER_ID || ', ' ||
' SYSDATE,'|| FND_GLOBAL.USER_ID || ', '|| FND_GLOBAL.USER_ID ||' '||
'FROM ' || p_source_table || ' ' ||
'WHERE PRICE_LIST_NAME like NVL(:p_price_list, PRICE_LIST_NAME) ';
/* DWK. if the source is from staging table, then we shouldn't insert instance = '0'
rows into fact table*/
IF p_source_table = MSD_COMMON_UTILITIES.PRICING_STAGING_TABLE then
v_sql_stmt := v_sql_stmt || ' and nvl(instance,''888'') <> '||'''0''';
dates according to the calendar. We split raw entity by inserting all the sub
entities into msd_mfg_forecast talbe and delete the original raw entity from
that table. When we insert sub entities into msd_mfg_forecast table, we join
this table with msd_st_time table and only select days which is in between
forecast_date and rate_end_date of individual raw entity. This INSERT
statement contains 3 SELECT statement according to raw entities bucket_type.
We connect the results with UNION. Only one SELECT statement will be
executed due to different bucket_type condition.
Once we insert all sub entity, we delte all raw entities, and delete
all calendar dates we populated at the beginning.
1) mfg_post_process will UPDATE the following rows
-----------------------------------------------------------
BUCKET_TYPE ACTION
-----------------------------------------------------------
DAY NONE
WEEK YES only if rate_end_date is NULL
MONTH YES only if rate_end_date is NULL
2) mfg_post_process will EXPLODE the following rows
-----------------------------------------------------------
BUCKET_TYPE ACTION
-----------------------------------------------------------
DAY YES only if rate_end_date IS NOT NULL and
rate_end_date <> forecast_date.
WEEK YES only if rate_end_date IS NOT NULL.
MONTH YES only if rate_end_date IS NOT NULL.
3) mfg_post_process will DELETE the following rows
-----------------------------------------------------------
BUCKET_TYPE ACTION
-----------------------------------------------------------
DAY YES rate_end_date IS NOT NULL and
rate_end_date <> forecast_date
WEEK YES rate_end_date IS NOT NULL
MONTH YES rate_end_date IS NOT NULL
*/
PROCEDURE mfg_post_process( errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2,
p_instance IN VARCHAR2,
p_designator IN VARCHAR2,
p_new_refresh_num IN NUMBER) IS
p_min_date DATE;
select month_end_date from msd_st_time
where instance = '-999' and day <= p_temp_end_date
order by day desc;
/* Select min and max date for the forecast_date
and rate_end_date. This will reduce the number of
dates populate_calendar_dates will populate */
SELECT min(forecast_date), max( nvl(rate_end_date, forecast_date)) INTO p_min_date, p_max_date
FROM msd_mfg_forecast
WHERE instance = p_instance AND
created_by_refresh_num = p_new_refresh_num AND
forecast_designator = nvl(p_designator,forecast_designator);
/************************** Update forecast_date *********************************/
/* First, Update forecast_date to bucket_end_date only if, it has NULL
for rate_end_date. Since we are not exploding any rows with NULL
value in rate_end_date, we have to update forecast_date separately */
/* First check if there is any row to be updated or not */
select count(*) into l_count
from msd_mfg_forecast
where rate_end_date is null and
bucket_type = '1' and
instance = p_instance and
forecast_designator = nvl(p_designator, forecast_designator) and
created_by_refresh_num = p_new_refresh_num and
rownum < 2;
p_str := 'UPDATE msd_mfg_forecast f ' ||
' SET forecast_date = ' ||
' nvl((SELECT t.week_end_date FROM msd_st_time t ' ||
' WHERE f.forecast_date = t.day and t.instance = '||
''''|| -999 ||'''' ||'), f.forecast_date)
WHERE f.rate_end_date is NULL and
f.bucket_type = ' || '''' || 1 ||''''|| ' and
f.created_by_refresh_num = ' || p_new_refresh_num || ' and
f.instance = ' || ''''||p_instance ||'''' ||
' and f.forecast_designator = nvl(:p_designator, f.forecast_designator) ';
/* First check if there is any row to be updated or not */
select count(*) into l_count
from msd_mfg_forecast
where rate_end_date is null and
bucket_type = '2' and
instance = p_instance and
forecast_designator = nvl(p_designator, forecast_designator) and
rownum < 2;
p_str := ' UPDATE msd_mfg_forecast f ' ||
' SET forecast_date = ' ||
' nvl((SELECT t.month_end_date FROM msd_st_time t
WHERE f.forecast_date = t.day and t.instance = '||
'''' || -999 ||'''' ||'), f.forecast_date)
WHERE f.rate_end_date is NULL and
f.bucket_type = ' || '''' || 2 ||''''|| ' and
f.created_by_refresh_num = ' || p_new_refresh_num || ' and
f.instance = ' || ''''||p_instance ||'''' ||
' and f.forecast_designator = nvl(:p_designator,f.forecast_designator) ';
/******************************* Insert ****************************************/
/* Find If there is any row needs to be exploded with Daily bucket.
If so, delete those rows from fact table and cache them into arrary */
DELETE FROM msd_mfg_forecast
WHERE instance = p_instance AND
forecast_designator = nvl(p_designator,forecast_designator) AND
bucket_type = C_DAILY_BUCKET AND
created_by_refresh_num = p_new_refresh_num AND
forecast_date <> nvl(rate_end_date, forecast_date)
RETURNING
FORECAST_DESIGNATOR, PRD_LEVEL_ID, ITEM, INV_ORG, CUSTOMER,
SALES_CHANNEL, SHIP_TO_LOC, USER_DEFINED1, USER_DEFINED2,
BUCKET_TYPE, FORECAST_DATE, RATE_END_DATE,
ORIGINAL_QUANTITY, CURRENT_QUANTITY,
sr_inv_org_pk, sr_item_pk, sr_customer_pk, sr_sales_channel_pk,
sr_ship_to_loc_pk, sr_user_defined1_pk, sr_user_defined2_pk,
sr_demand_class_pk
BULK COLLECT INTO
a_FORECAST_DESIGNATOR, a_prd_level_id, a_ITEM, a_INV_ORG, a_CUSTOMER,
a_SALES_CHANNEL, a_SHIP_TO_LOC, a_USER_DEFINED1, a_USER_DEFINED2,
a_BUCKET_TYPE, a_FORECAST_DATE, a_RATE_END_DATE,
a_ORIGINAL_QUANTITY, a_CURRENT_QUANTITY,
a_sr_inv_org_pk, a_sr_item_pk, a_sr_customer_pk, a_sr_sales_channel_pk,
a_sr_ship_to_loc_pk, a_sr_user_defined1_pk, a_sr_user_defined2_pk,
a_sr_demand_class_pk;
/* Bulk INSERT cached rows with explosion, For Daily Bucket */
IF (a_sr_item_pk.exists(1)) THEN
FORALL i IN a_sr_item_pk.FIRST..a_sr_item_pk.LAST
INSERT INTO msd_mfg_forecast(instance, forecast_designator, bucket_type,
forecast_date, rate_end_date, original_quantity,
current_quantity, creation_date, created_by,
last_update_date, last_updated_by, last_update_login,
sr_inv_org_pk, prd_level_id, sr_item_pk, sr_customer_pk,
sr_sales_channel_pk, sr_ship_to_loc_pk,
sr_user_defined1_pk, sr_user_defined2_pk,
sr_demand_class_pk,
created_by_refresh_num, last_refresh_num, action_code)
SELECT p_instance, a_forecast_designator(i), a_bucket_type(i),
t.day, NULL, a_original_quantity(i),
a_current_quantity(i), SYSDATE, FND_GLOBAL.USER_ID,
SYSDATE, FND_GLOBAL.USER_ID, fnd_global.login_id,
a_sr_inv_org_pk(i), a_prd_level_id(i), a_sr_item_pk(i), a_sr_customer_pk(i),
a_sr_sales_channel_pk(i), a_sr_ship_to_loc_pk(i),
a_sr_user_defined1_pk(i), a_sr_user_defined2_pk(i),
a_sr_demand_class_pk(i),
p_new_refresh_num, p_new_refresh_num, 'I'
FROM
msd_st_time t
WHERE
t.day between a_forecast_date(i) and a_rate_end_date(i) and
t.instance = '-999';
If so, delete those rows from fact table and cache them into arrary */
/* Also, rate_end_date can equal to forecast_date
since we already update it */
DELETE FROM msd_mfg_forecast
WHERE instance = p_instance AND
forecast_designator = nvl(p_designator,forecast_designator) AND
bucket_type = C_WEEKLY_BUCKET AND
created_by_refresh_num = p_new_refresh_num AND
rate_end_date IS NOT NULL
RETURNING
FORECAST_DESIGNATOR, ITEM, INV_ORG, CUSTOMER,
SALES_CHANNEL, SHIP_TO_LOC, USER_DEFINED1, USER_DEFINED2,
BUCKET_TYPE, FORECAST_DATE, RATE_END_DATE,
ORIGINAL_QUANTITY, CURRENT_QUANTITY,
sr_inv_org_pk, prd_level_id, sr_item_pk, sr_customer_pk, sr_sales_channel_pk,
sr_ship_to_loc_pk, sr_user_defined1_pk, sr_user_defined2_pk,
sr_demand_class_pk
BULK COLLECT INTO
a_FORECAST_DESIGNATOR, a_ITEM, a_INV_ORG, a_CUSTOMER,
a_SALES_CHANNEL, a_SHIP_TO_LOC, a_USER_DEFINED1, a_USER_DEFINED2,
a_BUCKET_TYPE, a_FORECAST_DATE, a_RATE_END_DATE,
a_ORIGINAL_QUANTITY, a_CURRENT_QUANTITY,
a_sr_inv_org_pk, a_prd_level_id, a_sr_item_pk, a_sr_customer_pk, a_sr_sales_channel_pk,
a_sr_ship_to_loc_pk, a_sr_user_defined1_pk, a_sr_user_defined2_pk,
a_sr_demand_class_pk;
/* Bulk INSERT cached rows with explosion, For Weekly Bucket */
IF (a_sr_item_pk.exists(1)) THEN
FORALL i IN a_sr_item_pk.FIRST..a_sr_item_pk.LAST
INSERT INTO msd_mfg_forecast(instance, forecast_designator, bucket_type,
forecast_date, rate_end_date, original_quantity,
current_quantity, creation_date, created_by,
last_update_date, last_updated_by, last_update_login,
sr_inv_org_pk, prd_level_id, sr_item_pk, sr_customer_pk,
sr_sales_channel_pk, sr_ship_to_loc_pk,
sr_user_defined1_pk, sr_user_defined2_pk,
sr_demand_class_pk,
created_by_refresh_num, last_refresh_num, action_code)
SELECT p_instance, a_forecast_designator(i), a_bucket_type(i),
t.week_end_date, NULL, a_original_quantity(i),
a_current_quantity(i), SYSDATE, FND_GLOBAL.USER_ID,
SYSDATE, FND_GLOBAL.USER_ID, fnd_global.login_id,
a_sr_inv_org_pk(i), a_prd_level_id(i), a_sr_item_pk(i), a_sr_customer_pk(i),
a_sr_sales_channel_pk(i), a_sr_ship_to_loc_pk(i),
a_sr_user_defined1_pk(i), a_sr_user_defined2_pk(i),
a_sr_demand_class_pk(i),
p_new_refresh_num, p_new_refresh_num, 'I'
FROM
(select distinct week_start_date, week_end_date
from msd_st_time
where instance = '-999') t
WHERE
t.week_start_date between a_forecast_date(i) and a_rate_end_date(i) or t.week_end_date between a_forecast_date(i) and a_rate_end_date(i);
If so, delete those rows from fact table and cache them into arrary */
/* Also, rate_end_date can equal to forecast_date
since we already update it */
DELETE FROM msd_mfg_forecast
WHERE instance = p_instance AND
forecast_designator = nvl(p_designator,forecast_designator) AND
bucket_type = C_MONTHLY_BUCKET AND
created_by_refresh_num = p_new_refresh_num AND
rate_end_date IS NOT NULL
RETURNING
FORECAST_DESIGNATOR, ITEM, INV_ORG, CUSTOMER,
SALES_CHANNEL, SHIP_TO_LOC, USER_DEFINED1, USER_DEFINED2,
BUCKET_TYPE, FORECAST_DATE, RATE_END_DATE,
ORIGINAL_QUANTITY, CURRENT_QUANTITY,
sr_inv_org_pk, prd_level_id, sr_item_pk, sr_customer_pk, sr_sales_channel_pk,
sr_ship_to_loc_pk, sr_user_defined1_pk, sr_user_defined2_pk,
sr_demand_class_pk
BULK COLLECT INTO
a_FORECAST_DESIGNATOR, a_ITEM, a_INV_ORG, a_CUSTOMER,
a_SALES_CHANNEL, a_SHIP_TO_LOC, a_USER_DEFINED1, a_USER_DEFINED2,
a_BUCKET_TYPE, a_FORECAST_DATE, a_RATE_END_DATE,
a_ORIGINAL_QUANTITY, a_CURRENT_QUANTITY,
a_sr_inv_org_pk, a_prd_level_id, a_sr_item_pk, a_sr_customer_pk, a_sr_sales_channel_pk,
a_sr_ship_to_loc_pk, a_sr_user_defined1_pk, a_sr_user_defined2_pk,
a_sr_demand_class_pk;
/* Bulk INSERT cached rows with explosion, For Monthly Bucket */
IF (a_sr_item_pk.exists(1)) THEN
FORALL i IN a_sr_item_pk.FIRST..a_sr_item_pk.LAST
INSERT INTO msd_mfg_forecast(instance, forecast_designator, bucket_type,
forecast_date, rate_end_date, original_quantity,
current_quantity, creation_date, created_by,
last_update_date, last_updated_by, last_update_login,
sr_inv_org_pk, prd_level_id, sr_item_pk, sr_customer_pk,
sr_sales_channel_pk, sr_ship_to_loc_pk,
sr_user_defined1_pk, sr_user_defined2_pk,
sr_demand_class_pk,
created_by_refresh_num, last_refresh_num, action_code)
SELECT p_instance, a_forecast_designator(i), a_bucket_type(i),
t.month_end_date, NULL, a_original_quantity(i),
a_current_quantity(i), SYSDATE, FND_GLOBAL.USER_ID,
SYSDATE, FND_GLOBAL.USER_ID, fnd_global.login_id,
a_sr_inv_org_pk(i), a_prd_level_id(i), a_sr_item_pk(i), a_sr_customer_pk(i),
a_sr_sales_channel_pk(i), a_sr_ship_to_loc_pk(i),
a_sr_user_defined1_pk(i), a_sr_user_defined2_pk(i),
a_sr_demand_class_pk(i),
p_new_refresh_num, p_new_refresh_num, 'I'
FROM
(select distinct month_start_date, month_end_date
from msd_st_time
where instance = '-999') t
WHERE
t.month_start_date between a_forecast_date(i) and a_rate_end_date(i);
update msd_mfg_forecast
set bucket_type = C_DAILY_BUCKET
WHERE instance = p_instance AND
forecast_designator = nvl(p_designator,forecast_designator) AND
bucket_type = C_WEEKLY_BUCKET AND
created_by_refresh_num = p_new_refresh_num;
only insert working date into the table, since holiday and weekends will have
NULL for seq_num.
*/
PROCEDURE populate_calendar_dates(p_cal_code VARCHAR2,
p_min_date DATE,
p_max_date DATE,
p_dblink VARCHAR2 ) IS
p_cal_type NUMBER;
p_str := 'INSERT INTO msd_st_time(instance, calendar_code,
calendar_type,seq_num, month_start_date,
month_end_date, week_start_date,
week_end_date, day,last_update_date,
last_updated_by, creation_date, created_by,
last_update_login)
SELECT ' || '''-999''' || ', calendar_code, 1,
seq_num, month_start_date,month_end_date, week_start_date, week_end_date, day,
SYSDATE, FND_GLOBAL.USER_ID, SYSDATE, FND_GLOBAL.USER_ID, FND_GLOBAL.LOGIN_ID
FROM msd_sr_mfg_time_v' || p_dblink ||
' WHERE seq_num <> -1 and calendar_code = :p_cal_code '||
' AND day BETWEEN :p_min_date AND :p_max_date ';
/* Select min and max date for the forecast_date and rate_end_date. */
l_str := 'SELECT min(forecast_date), max( nvl(rate_end_date, forecast_date))'||
' FROM '||p_table_name|| ' WHERE instance = '||''''||p_instance||'''';
l_str := 'SELECT parameter_value FROM msd_setup_parameters'||
l_dblink || ' WHERE parameter_name = '|| '''MSD_MASTER_ORG''';
l_str := 'SELECT calendar_code FROM mtl_parameters' ||
l_dblink || ' WHERE organization_id = :l_org_id ';
/* DWK Delete existing calendar before populating the current one */
DELETE msd_st_time WHERE instance = '-999';
/* Populate calendar dates. We will delete all these data
after we finish exploiting entities */
populate_calendar_dates(l_cal_code, l_min_date, l_max_date, l_dblink);
SELECT instance_type FROM msc_apps_instances
WHERE to_char(instance_id) = p_instance;
SELECT count(*) INTO l_count FROM msd_mfg_forecast
WHERE instance = p_instance AND
forecast_designator = nvl(p_designator,forecast_designator) AND
(bucket_type <> C_DAILY_BUCKET OR
forecast_date <> nvl(rate_end_date, forecast_date)) and
-- VM created_by_refresh .....
rownum < 2;
SELECT nvl(min(scn_build_refresh_num), 0) INTO l_least_refresh_num
FROM msd_dp_parameters;
l_sql_stmt := ' DELETE FROM ' || p_table_name ||
' WHERE ACTION_CODE = ' || '''D''' ||
' and LAST_REFRESH_NUM <= ' || l_least_refresh_num;
select cs_definition_id
from msd_cs_definitions
where name = p_name;
insert into msd_cs_data_headers
( cs_data_header_id,
instance,
cs_definition_id,
cs_name,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
last_refresh_num
)
select msd_cs_data_headers_s.nextval,
p_instance,
x_cs_id,
'SINGLE_STREAM',
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
p_ref_num from dual;