eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.MSC_CL_PRE_PROCESS dependencies on MSC_ST_REGIONS

Line 1311: 'create index MSC_ST_REGIONS_N1_'||v_instance_code

1307: ad_ddl.do_ddl( applsys_schema => v_applsys_schema,
1308: application_short_name => 'MSC',
1309: statement_type => AD_DDL.CREATE_INDEX,
1310: statement =>
1311: 'create index MSC_ST_REGIONS_N1_'||v_instance_code
1312: ||' on MSC_ST_REGIONS '
1313: ||'(sr_instance_code, country, state, city, postal_code_from, postal_code_to, zone ) '
1314: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
1315: object_name =>'MSC_ST_REGIONS_N1_'||v_instance_code);

Line 1312: ||' on MSC_ST_REGIONS '

1308: application_short_name => 'MSC',
1309: statement_type => AD_DDL.CREATE_INDEX,
1310: statement =>
1311: 'create index MSC_ST_REGIONS_N1_'||v_instance_code
1312: ||' on MSC_ST_REGIONS '
1313: ||'(sr_instance_code, country, state, city, postal_code_from, postal_code_to, zone ) '
1314: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
1315: object_name =>'MSC_ST_REGIONS_N1_'||v_instance_code);
1316:

Line 1315: object_name =>'MSC_ST_REGIONS_N1_'||v_instance_code);

1311: 'create index MSC_ST_REGIONS_N1_'||v_instance_code
1312: ||' on MSC_ST_REGIONS '
1313: ||'(sr_instance_code, country, state, city, postal_code_from, postal_code_to, zone ) '
1314: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
1315: object_name =>'MSC_ST_REGIONS_N1_'||v_instance_code);
1316:
1317: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Created Index MSC_ST_REGIONS_N1_'||v_instance_code);
1318:
1319: EXCEPTION

Line 1317: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Created Index MSC_ST_REGIONS_N1_'||v_instance_code);

1313: ||'(sr_instance_code, country, state, city, postal_code_from, postal_code_to, zone ) '
1314: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
1315: object_name =>'MSC_ST_REGIONS_N1_'||v_instance_code);
1316:
1317: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Created Index MSC_ST_REGIONS_N1_'||v_instance_code);
1318:
1319: EXCEPTION
1320: WHEN OTHERS THEN
1321: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_WARNING,'Error creating Index MSC_ST_REGIONS_N1_'||v_instance_code||'. Error:'||substr(SQLERRM,1,240));

Line 1321: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_WARNING,'Error creating Index MSC_ST_REGIONS_N1_'||v_instance_code||'. Error:'||substr(SQLERRM,1,240));

1317: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Created Index MSC_ST_REGIONS_N1_'||v_instance_code);
1318:
1319: EXCEPTION
1320: WHEN OTHERS THEN
1321: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_WARNING,'Error creating Index MSC_ST_REGIONS_N1_'||v_instance_code||'. Error:'||substr(SQLERRM,1,240));
1322: END;
1323:
1324: BEGIN
1325:

Line 1380: msc_analyse_tables_pk.analyse_table( 'MSC_ST_REGIONS', v_instance_id, -1);

1376: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_WARNING,'Error creating Index MSC_ST_REGION_SITE_N1_'||v_instance_code||'. Error:'||substr(SQLERRM,1,240));
1377: END;
1378:
1379: msc_analyse_tables_pk.analyse_table( 'MSC_ST_ITEM_SOURCING', v_instance_id, -1);
1380: msc_analyse_tables_pk.analyse_table( 'MSC_ST_REGIONS', v_instance_id, -1);
1381: msc_analyse_tables_pk.analyse_table( 'MSC_ST_ZONE_REGIONS', v_instance_id, -1);
1382: msc_analyse_tables_pk.analyse_table( 'MSC_ST_REGION_LOCATIONS', v_instance_id, -1);
1383: msc_analyse_tables_pk.analyse_table( 'MSC_ST_REGION_SITES', v_instance_id, -1);
1384:

Line 2487: 'drop index MSC_ST_REGIONS_N1_'||v_instance_code,

2483: ad_ddl.do_ddl( applsys_schema => v_applsys_schema,
2484: application_short_name => 'MSC',
2485: statement_type => AD_DDL.DROP_INDEX,
2486: statement =>
2487: 'drop index MSC_ST_REGIONS_N1_'||v_instance_code,
2488: object_name => 'MSC_ST_REGIONS_N1_'||v_instance_code);
2489:
2490: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Dropped Index MSC_ST_REGIONS_N1_'||v_instance_code);
2491:

Line 2488: object_name => 'MSC_ST_REGIONS_N1_'||v_instance_code);

2484: application_short_name => 'MSC',
2485: statement_type => AD_DDL.DROP_INDEX,
2486: statement =>
2487: 'drop index MSC_ST_REGIONS_N1_'||v_instance_code,
2488: object_name => 'MSC_ST_REGIONS_N1_'||v_instance_code);
2489:
2490: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Dropped Index MSC_ST_REGIONS_N1_'||v_instance_code);
2491:
2492: EXCEPTION

Line 2490: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Dropped Index MSC_ST_REGIONS_N1_'||v_instance_code);

2486: statement =>
2487: 'drop index MSC_ST_REGIONS_N1_'||v_instance_code,
2488: object_name => 'MSC_ST_REGIONS_N1_'||v_instance_code);
2489:
2490: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Dropped Index MSC_ST_REGIONS_N1_'||v_instance_code);
2491:
2492: EXCEPTION
2493: WHEN OTHERS THEN
2494: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_WARNING,'Error dropping Index MSC_ST_REGIONS_N1_'||v_instance_code||'. Error:'||substr(SQLERRM,1,240));

Line 2494: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_WARNING,'Error dropping Index MSC_ST_REGIONS_N1_'||v_instance_code||'. Error:'||substr(SQLERRM,1,240));

2490: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Dropped Index MSC_ST_REGIONS_N1_'||v_instance_code);
2491:
2492: EXCEPTION
2493: WHEN OTHERS THEN
2494: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_WARNING,'Error dropping Index MSC_ST_REGIONS_N1_'||v_instance_code||'. Error:'||substr(SQLERRM,1,240));
2495: END;
2496:
2497: BEGIN
2498: ad_ddl.do_ddl( applsys_schema => v_applsys_schema,

Line 2502: 'drop index MSC_ST_REGIONS_N1_'||v_instance_code,

2498: ad_ddl.do_ddl( applsys_schema => v_applsys_schema,
2499: application_short_name => 'MSC',
2500: statement_type => AD_DDL.DROP_INDEX,
2501: statement =>
2502: 'drop index MSC_ST_REGIONS_N1_'||v_instance_code,
2503: object_name => 'MSC_ST_ZONE_REGIONS_N1_'||v_instance_code);
2504:
2505: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Dropped Index MSC_ST_ZONE_REGIONS_N1_'||v_instance_code);
2506:

Line 13488: lv_batch_id msc_st_regions.batch_id%TYPE;

13484: lv_error_text VARCHAR2(250);
13485: lv_where_str VARCHAR2(5000);
13486: lv_sql_stmt VARCHAR2(5000);
13487: lv_column_names VARCHAR2(5000); --stores concatenated column names
13488: lv_batch_id msc_st_regions.batch_id%TYPE;
13489: lv_message_text msc_errors.error_text%TYPE;
13490: lv_cursor_stmt VARCHAR2(5000);
13491:
13492: ex_logging_err EXCEPTION;

Line 13496: FROM msc_st_regions

13492: ex_logging_err EXCEPTION;
13493:
13494: CURSOR c2(p_batch_id NUMBER) IS
13495: SELECT rowid
13496: FROM msc_st_regions
13497: WHERE process_flag IN (G_IN_PROCESS,G_ERROR_FLG)
13498: AND sr_instance_code = v_instance_code
13499: AND batch_id = p_batch_id;
13500:

Line 13504: FROM msc_st_regions

13500:
13501:
13502: CURSOR c3(p_batch_id NUMBER) IS
13503: SELECT rowid
13504: FROM msc_st_regions
13505: WHERE NVL(region_id,NULL_VALUE) = NULL_VALUE
13506: AND process_flag = G_IN_PROCESS
13507: AND NVL(batch_id,NULL_VALUE) =p_batch_id
13508: AND sr_instance_code = v_instance_code;

Line 13565: 'UPDATE msc_st_regions msr1 '

13561:
13562: --Duplicate records check for the records whose source is XML
13563: v_sql_stmt := 01;
13564: lv_sql_stmt :=
13565: 'UPDATE msc_st_regions msr1 '
13566: ||' SET process_flag = '||G_ERROR_FLG||','
13567: ||' error_text = '||''''||lv_message_text||''''
13568: ||' WHERE message_id < (SELECT MAX(message_id)'
13569: ||' FROM msc_st_regions msr2'

Line 13569: ||' FROM msc_st_regions msr2'

13565: 'UPDATE msc_st_regions msr1 '
13566: ||' SET process_flag = '||G_ERROR_FLG||','
13567: ||' error_text = '||''''||lv_message_text||''''
13568: ||' WHERE message_id < (SELECT MAX(message_id)'
13569: ||' FROM msc_st_regions msr2'
13570: ||' WHERE msr2.sr_instance_code = msr1.sr_instance_code'
13571: ||' AND msr2.region_type = msr1.region_type'
13572: ||' AND NVL(msr2.company_name, '||''''||NULL_CHAR||''''||') = '
13573: ||' NVL(msr1.company_name, '||''''||NULL_CHAR||''''||')'

Line 13608: 'UPDATE msc_st_regions msr1 '

13604:
13605:
13606: /* v_sql_stmt := 1.1;
13607: lv_sql_stmt :=
13608: 'UPDATE msc_st_regions msr1 '
13609: ||' SET process_flag = '||G_ERROR_FLG||','
13610: ||' error_text = '||''''||lv_message_text||''''
13611: ||' WHERE message_id < (SELECT MAX(message_id)'
13612: ||' FROM msc_st_regions msr2'

Line 13612: ||' FROM msc_st_regions msr2'

13608: 'UPDATE msc_st_regions msr1 '
13609: ||' SET process_flag = '||G_ERROR_FLG||','
13610: ||' error_text = '||''''||lv_message_text||''''
13611: ||' WHERE message_id < (SELECT MAX(message_id)'
13612: ||' FROM msc_st_regions msr2'
13613: ||' WHERE msr2.sr_instance_code = msr1.sr_instance_code'
13614: ||' AND msr2.region_type = msr1.region_type'
13615: ||' AND NVL(msr2.company_name, '||''''||NULL_CHAR||''''||') = '
13616: ||' NVL(msr1.company_name, '||''''||NULL_CHAR||''''||')'

Line 13650: 'UPDATE msc_st_regions msr1'

13646: --Different SQL is used because in XML we can identify the latest records
13647: --whereas in batch load we cannot.
13648: v_sql_stmt := 02;
13649: lv_sql_stmt :=
13650: 'UPDATE msc_st_regions msr1'
13651: ||' SET process_flag = '||G_ERROR_FLG||','
13652: ||' error_text = '||''''||lv_message_text||''''
13653: ||' WHERE EXISTS( SELECT 1 '
13654: ||' FROM msc_st_regions msr2'

Line 13654: ||' FROM msc_st_regions msr2'

13650: 'UPDATE msc_st_regions msr1'
13651: ||' SET process_flag = '||G_ERROR_FLG||','
13652: ||' error_text = '||''''||lv_message_text||''''
13653: ||' WHERE EXISTS( SELECT 1 '
13654: ||' FROM msc_st_regions msr2'
13655: ||' WHERE msr2.sr_instance_code = msr1.sr_instance_code'
13656: ||' AND msr2.region_type = msr1.region_type'
13657: ||' AND NVL(msr2.company_name, '||''''||NULL_CHAR||''''||') = '
13658: ||' NVL(msr1.company_name, '||''''||NULL_CHAR||''''||')'

Line 13721: ' UPDATE msc_st_regions '

13717: CLOSE c1;
13718:
13719: v_sql_stmt := 04;
13720: lv_sql_stmt :=
13721: ' UPDATE msc_st_regions '
13722: ||' SET batch_id = :lv_batch_id'
13723: ||' WHERE process_flag IN ('||G_IN_PROCESS||','||G_ERROR_FLG||')'
13724: ||' AND sr_instance_code = :v_instance_code'
13725: ||' AND NVL(batch_id,'||NULL_VALUE||') = '||NULL_VALUE

Line 13742: UPDATE msc_st_regions

13738: CLOSE c2;
13739:
13740: v_sql_stmt := 05;
13741: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
13742: UPDATE msc_st_regions
13743: SET st_transaction_id = msc_st_regions_s.NEXTVAL,
13744: refresh_number = v_refresh_id,
13745: last_update_date = v_current_date,
13746: last_updated_by = v_current_user,

Line 13743: SET st_transaction_id = msc_st_regions_s.NEXTVAL,

13739:
13740: v_sql_stmt := 05;
13741: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
13742: UPDATE msc_st_regions
13743: SET st_transaction_id = msc_st_regions_s.NEXTVAL,
13744: refresh_number = v_refresh_id,
13745: last_update_date = v_current_date,
13746: last_updated_by = v_current_user,
13747: creation_date = v_current_date,

Line 13757: p_token_value1 => 'MSC_ST_REGIONS');

13753: p_error_code => 'MSC_PP_NO_DELETION',
13754: p_message_text => lv_message_text,
13755: p_error_text => lv_error_text,
13756: p_token1 => 'TABLE_NAME',
13757: p_token_value1 => 'MSC_ST_REGIONS');
13758:
13759:
13760: -- Deletion is not allowed for this table
13761: v_sql_stmt := 06;

Line 13763: 'UPDATE msc_st_regions '

13759:
13760: -- Deletion is not allowed for this table
13761: v_sql_stmt := 06;
13762: lv_sql_stmt :=
13763: 'UPDATE msc_st_regions '
13764: ||' SET process_flag = '||G_ERROR_FLG||','
13765: ||' error_text = '||''''||lv_message_text||''''
13766: ||' WHERE deleted_flag = '||SYS_YES
13767: ||' AND process_flag = '||G_IN_PROCESS

Line 13802: (p_table_name => 'MSC_ST_REGIONS',

13798: lv_where_str := ' AND NVL(deleted_flag,'||NULL_VALUE||') '
13799: ||' NOT IN(1,2)';
13800:
13801: lv_return := MSC_ST_UTIL.LOG_ERROR
13802: (p_table_name => 'MSC_ST_REGIONS',
13803: p_instance_code => v_instance_code,
13804: p_row => lv_column_names,
13805: p_severity => G_SEV_WARNING,
13806: p_message_text => lv_message_text,

Line 13832: 'UPDATE msc_st_regions'

13828: RAISE ex_logging_err;
13829: END IF;
13830:
13831: lv_sql_stmt :=
13832: 'UPDATE msc_st_regions'
13833: ||' SET process_flag ='||G_ERROR_FLG||','
13834: ||' error_text = '||''''||lv_message_text||''''
13835: ||' WHERE region_type NOT IN (0,1,2,3,10) '
13836: ||' AND sr_instance_code = :v_instance_code'

Line 13847: ' UPDATE msc_st_regions '

13843: EXECUTE IMMEDIATE lv_sql_stmt USING v_instance_code,lv_batch_id;
13844:
13845:
13846: lv_sql_stmt :=
13847: ' UPDATE msc_st_regions '
13848: ||' SET parent_region_id = -1'
13849: ||' WHERE process_flag = '||G_IN_PROCESS
13850: ||' AND sr_instance_code = :v_instance_code'
13851: ||' AND region_type in (0,10) '

Line 13874: 'UPDATE msc_st_regions msr1'

13870: RAISE ex_logging_err;
13871: END IF;
13872:
13873: lv_sql_stmt :=
13874: 'UPDATE msc_st_regions msr1'
13875: ||' SET process_flag = '||G_ERROR_FLG||','
13876: ||' error_text = '||''''||lv_message_text||''''
13877: ||' WHERE EXISTS( SELECT 1 '
13878: ||' FROM msc_st_regions msr2'

Line 13878: ||' FROM msc_st_regions msr2'

13874: 'UPDATE msc_st_regions msr1'
13875: ||' SET process_flag = '||G_ERROR_FLG||','
13876: ||' error_text = '||''''||lv_message_text||''''
13877: ||' WHERE EXISTS( SELECT 1 '
13878: ||' FROM msc_st_regions msr2'
13879: ||' WHERE msr2.sr_instance_code = msr1.sr_instance_code'
13880: ||' AND msr2.region_type = msr1.region_type'
13881: ||' AND NVL(msr2.country, '||''''||NULL_CHAR||''''||') = '
13882: ||' NVL(msr1.country, '||''''||NULL_CHAR||''''||')'

Line 13921: 'UPDATE msc_st_regions msr'

13917:
13918: --Deriving region_id
13919:
13920: lv_sql_stmt :=
13921: 'UPDATE msc_st_regions msr'
13922: ||' SET region_id = (SELECT local_id'
13923: ||' FROM msc_local_id_setup mls'
13924: ||' WHERE mls.char1 = msr.sr_instance_code'
13925: ||' AND mls.number1 = msr.region_type'

Line 13964: p_token_value1 => 'MSC_ST_REGIONS');

13960: p_error_code => 'MSC_PP_NO_UPDATION',
13961: p_message_text => lv_message_text,
13962: p_error_text => lv_error_text,
13963: p_token1 => 'TABLE_NAME',
13964: p_token_value1 => 'MSC_ST_REGIONS');
13965:
13966:
13967: -- Error out the record if same region already exists in ODS
13968: -- As we do not allow regions incremental update

Line 13972: 'UPDATE msc_st_regions msr'

13968: -- As we do not allow regions incremental update
13969: -- It has to be purged before loaded again
13970:
13971: lv_sql_stmt :=
13972: 'UPDATE msc_st_regions msr'
13973: ||' SET msr.process_flag = '||G_ERROR_FLG||','
13974: ||' msr.error_text = '||''''||lv_message_text||''''
13975: ||' WHERE msr.batch_id = :lv_batch_id'
13976: ||' AND msr.sr_instance_code = :v_instance_code'

Line 13997: pEntityName => 'MSC_ST_REGIONS',

13993: (ERRBUF => lv_error_text,
13994: RETCODE => lv_return,
13995: pBatchID => lv_batch_id,
13996: pInstanceCode => v_instance_code,
13997: pEntityName => 'MSC_ST_REGIONS',
13998: pInstanceID => v_instance_id);
13999:
14000: IF NVL(lv_return,0) <> 0 THEN
14001: RAISE ex_logging_err;

Line 14011: UPDATE msc_st_regions

14007:
14008: IF c3%ROWCOUNT > 0 THEN
14009: v_sql_stmt := 12;
14010: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
14011: UPDATE msc_st_regions
14012: SET region_id = msc_st_region_id_s.NEXTVAL
14013: WHERE rowid = lb_rowid(j);
14014:
14015: v_sql_stmt := 13;

Line 14059: FROM msc_st_regions

14055: v_current_date,
14056: v_current_user,
14057: v_current_date,
14058: v_current_user
14059: FROM msc_st_regions
14060: WHERE rowid = lb_rowid(j);
14061:
14062: END IF;
14063: CLOSE c3 ;

Line 14066: ' UPDATE msc_st_regions msr1 '

14062: END IF;
14063: CLOSE c3 ;
14064:
14065: lv_sql_stmt :=
14066: ' UPDATE msc_st_regions msr1 '
14067: ||' SET parent_region_id = (SELECT msr2.region_id'
14068: ||' FROM msc_st_regions msr2'
14069: ||' WHERE msr2.sr_instance_code = msr1.sr_instance_code '
14070: ||' AND msr2.region_type = msr1.region_type - 1 '

Line 14068: ||' FROM msc_st_regions msr2'

14064:
14065: lv_sql_stmt :=
14066: ' UPDATE msc_st_regions msr1 '
14067: ||' SET parent_region_id = (SELECT msr2.region_id'
14068: ||' FROM msc_st_regions msr2'
14069: ||' WHERE msr2.sr_instance_code = msr1.sr_instance_code '
14070: ||' AND msr2.region_type = msr1.region_type - 1 '
14071: ||' AND NVL(msr2.country, NVL(msr1.country, '||''''||NULL_CHAR||''''||')) = '
14072: ||' NVL(msr1.country, '||''''||NULL_CHAR||''''||') '

Line 14098: (p_table_name => 'MSC_ST_REGIONS',

14094:
14095: EXECUTE IMMEDIATE lv_sql_stmt USING v_instance_code, lv_batch_id;
14096:
14097: lv_return := MSC_ST_UTIL.SET_PROCESS_FLAG
14098: (p_table_name => 'MSC_ST_REGIONS',
14099: p_instance_id => v_instance_id,
14100: p_instance_code => v_instance_code,
14101: p_process_flag => G_VALID,
14102: p_error_text => lv_error_text,

Line 14110: (p_table_name => 'MSC_ST_REGIONS',

14106: RAISE ex_logging_err;
14107: END IF;
14108:
14109: lv_return := MSC_ST_UTIL.LOG_ERROR
14110: (p_table_name => 'MSC_ST_REGIONS',
14111: p_instance_code => v_instance_code,
14112: p_row => lv_column_names,
14113: p_severity => G_SEV_ERROR,
14114: p_message_text => NULL,

Line 14188: ||' FROM MSC_ST_REGIONS msr'

14184: ||' msr.CREATED_BY,'
14185: ||' msr.CREATION_DATE,'
14186: ||' msr.LAST_UPDATED_BY,'
14187: ||' msr.LAST_UPDATE_DATE'
14188: ||' FROM MSC_ST_REGIONS msr'
14189: ||' WHERE msr.batch_id = :lv_batch_id'
14190: ||' AND msr.sr_instance_code = :v_instance_code'
14191: ||' AND msr.process_flag = '||G_VALID ;
14192:

Line 50892: lv_count:= SET_IN_PROCESS (p_table_name => 'MSC_ST_REGIONS');

50888: lv_count:= SET_IN_PROCESS (p_table_name => 'MSC_ST_ITEM_SOURCING');
50889: IF lv_count > 0 Then
50890: prec.sourcing_rule_flag:= SYS_YES;
50891: End IF;
50892: lv_count:= SET_IN_PROCESS (p_table_name => 'MSC_ST_REGIONS');
50893: IF lv_count > 0 Then
50894: prec.sourcing_rule_flag:= SYS_YES;
50895: End IF;
50896: lv_count:= SET_IN_PROCESS (p_table_name => 'MSC_ST_ZONE_REGIONS');