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 1319: 'create index MSC_ST_REGIONS_N1_'||v_instance_code

1315: ad_ddl.do_ddl( applsys_schema => v_applsys_schema,
1316: application_short_name => 'MSC',
1317: statement_type => AD_DDL.CREATE_INDEX,
1318: statement =>
1319: 'create index MSC_ST_REGIONS_N1_'||v_instance_code
1320: ||' on MSC_ST_REGIONS '
1321: ||'(sr_instance_code, country, state, city, postal_code_from, postal_code_to, zone ) '
1322: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
1323: object_name =>'MSC_ST_REGIONS_N1_'||v_instance_code);

Line 1320: ||' on MSC_ST_REGIONS '

1316: application_short_name => 'MSC',
1317: statement_type => AD_DDL.CREATE_INDEX,
1318: statement =>
1319: 'create index MSC_ST_REGIONS_N1_'||v_instance_code
1320: ||' on MSC_ST_REGIONS '
1321: ||'(sr_instance_code, country, state, city, postal_code_from, postal_code_to, zone ) '
1322: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
1323: object_name =>'MSC_ST_REGIONS_N1_'||v_instance_code);
1324:

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

1319: 'create index MSC_ST_REGIONS_N1_'||v_instance_code
1320: ||' on MSC_ST_REGIONS '
1321: ||'(sr_instance_code, country, state, city, postal_code_from, postal_code_to, zone ) '
1322: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
1323: object_name =>'MSC_ST_REGIONS_N1_'||v_instance_code);
1324:
1325: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Created Index MSC_ST_REGIONS_N1_'||v_instance_code);
1326:
1327: EXCEPTION

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

1321: ||'(sr_instance_code, country, state, city, postal_code_from, postal_code_to, zone ) '
1322: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
1323: object_name =>'MSC_ST_REGIONS_N1_'||v_instance_code);
1324:
1325: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Created Index MSC_ST_REGIONS_N1_'||v_instance_code);
1326:
1327: EXCEPTION
1328: WHEN OTHERS THEN
1329: 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 1329: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_WARNING,'Error creating Index MSC_ST_REGIONS_N1_'||v_instance_code||'. Error:'||substr(SQLERRM,1,240));

1325: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Created Index MSC_ST_REGIONS_N1_'||v_instance_code);
1326:
1327: EXCEPTION
1328: WHEN OTHERS THEN
1329: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_WARNING,'Error creating Index MSC_ST_REGIONS_N1_'||v_instance_code||'. Error:'||substr(SQLERRM,1,240));
1330: END;
1331:
1332: BEGIN
1333:

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

1384: 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));
1385: END;
1386:
1387: msc_analyse_tables_pk.analyse_table( 'MSC_ST_ITEM_SOURCING', v_instance_id, -1);
1388: msc_analyse_tables_pk.analyse_table( 'MSC_ST_REGIONS', v_instance_id, -1);
1389: msc_analyse_tables_pk.analyse_table( 'MSC_ST_ZONE_REGIONS', v_instance_id, -1);
1390: msc_analyse_tables_pk.analyse_table( 'MSC_ST_REGION_LOCATIONS', v_instance_id, -1);
1391: msc_analyse_tables_pk.analyse_table( 'MSC_ST_REGION_SITES', v_instance_id, -1);
1392:

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

2491: ad_ddl.do_ddl( applsys_schema => v_applsys_schema,
2492: application_short_name => 'MSC',
2493: statement_type => AD_DDL.DROP_INDEX,
2494: statement =>
2495: 'drop index MSC_ST_REGIONS_N1_'||v_instance_code,
2496: object_name => 'MSC_ST_REGIONS_N1_'||v_instance_code);
2497:
2498: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Dropped Index MSC_ST_REGIONS_N1_'||v_instance_code);
2499:

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

2492: application_short_name => 'MSC',
2493: statement_type => AD_DDL.DROP_INDEX,
2494: statement =>
2495: 'drop index MSC_ST_REGIONS_N1_'||v_instance_code,
2496: object_name => 'MSC_ST_REGIONS_N1_'||v_instance_code);
2497:
2498: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Dropped Index MSC_ST_REGIONS_N1_'||v_instance_code);
2499:
2500: EXCEPTION

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

2494: statement =>
2495: 'drop index MSC_ST_REGIONS_N1_'||v_instance_code,
2496: object_name => 'MSC_ST_REGIONS_N1_'||v_instance_code);
2497:
2498: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Dropped Index MSC_ST_REGIONS_N1_'||v_instance_code);
2499:
2500: EXCEPTION
2501: WHEN OTHERS THEN
2502: 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 2502: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_WARNING,'Error dropping Index MSC_ST_REGIONS_N1_'||v_instance_code||'. Error:'||substr(SQLERRM,1,240));

2498: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Dropped Index MSC_ST_REGIONS_N1_'||v_instance_code);
2499:
2500: EXCEPTION
2501: WHEN OTHERS THEN
2502: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_WARNING,'Error dropping Index MSC_ST_REGIONS_N1_'||v_instance_code||'. Error:'||substr(SQLERRM,1,240));
2503: END;
2504:
2505: BEGIN
2506: ad_ddl.do_ddl( applsys_schema => v_applsys_schema,

Line 16975: lv_batch_id msc_st_regions.batch_id%TYPE;

16971: lv_error_text VARCHAR2(250);
16972: lv_where_str VARCHAR2(5000);
16973: lv_sql_stmt VARCHAR2(5000);
16974: lv_column_names VARCHAR2(5000); --stores concatenated column names
16975: lv_batch_id msc_st_regions.batch_id%TYPE;
16976: lv_message_text msc_errors.error_text%TYPE;
16977: lv_cursor_stmt VARCHAR2(5000);
16978:
16979: ex_logging_err EXCEPTION;

Line 16983: FROM msc_st_regions

16979: ex_logging_err EXCEPTION;
16980:
16981: CURSOR c2(p_batch_id NUMBER) IS
16982: SELECT rowid
16983: FROM msc_st_regions
16984: WHERE process_flag IN (G_IN_PROCESS,G_ERROR_FLG)
16985: AND sr_instance_code = v_instance_code
16986: AND batch_id = p_batch_id;
16987:

Line 16991: FROM msc_st_regions

16987:
16988:
16989: CURSOR c3(p_batch_id NUMBER) IS
16990: SELECT rowid
16991: FROM msc_st_regions
16992: WHERE NVL(region_id,NULL_VALUE) = NULL_VALUE
16993: AND process_flag = G_IN_PROCESS
16994: AND NVL(batch_id,NULL_VALUE) =p_batch_id
16995: AND sr_instance_code = v_instance_code;

Line 17052: 'UPDATE msc_st_regions msr1 '

17048:
17049: --Duplicate records check for the records whose source is XML
17050: v_sql_stmt := 01;
17051: lv_sql_stmt :=
17052: 'UPDATE msc_st_regions msr1 '
17053: ||' SET process_flag = '||G_ERROR_FLG||','
17054: ||' error_text = '||''''||lv_message_text||''''
17055: ||' WHERE message_id < (SELECT MAX(message_id)'
17056: ||' FROM msc_st_regions msr2'

Line 17056: ||' FROM msc_st_regions msr2'

17052: 'UPDATE msc_st_regions msr1 '
17053: ||' SET process_flag = '||G_ERROR_FLG||','
17054: ||' error_text = '||''''||lv_message_text||''''
17055: ||' WHERE message_id < (SELECT MAX(message_id)'
17056: ||' FROM msc_st_regions msr2'
17057: ||' WHERE msr2.sr_instance_code = msr1.sr_instance_code'
17058: ||' AND msr2.region_type = msr1.region_type'
17059: ||' AND NVL(msr2.company_name, '||''''||NULL_CHAR||''''||') = '
17060: ||' NVL(msr1.company_name, '||''''||NULL_CHAR||''''||')'

Line 17095: 'UPDATE msc_st_regions msr1 '

17091:
17092:
17093: /* v_sql_stmt := 1.1;
17094: lv_sql_stmt :=
17095: 'UPDATE msc_st_regions msr1 '
17096: ||' SET process_flag = '||G_ERROR_FLG||','
17097: ||' error_text = '||''''||lv_message_text||''''
17098: ||' WHERE message_id < (SELECT MAX(message_id)'
17099: ||' FROM msc_st_regions msr2'

Line 17099: ||' FROM msc_st_regions msr2'

17095: 'UPDATE msc_st_regions msr1 '
17096: ||' SET process_flag = '||G_ERROR_FLG||','
17097: ||' error_text = '||''''||lv_message_text||''''
17098: ||' WHERE message_id < (SELECT MAX(message_id)'
17099: ||' FROM msc_st_regions msr2'
17100: ||' WHERE msr2.sr_instance_code = msr1.sr_instance_code'
17101: ||' AND msr2.region_type = msr1.region_type'
17102: ||' AND NVL(msr2.company_name, '||''''||NULL_CHAR||''''||') = '
17103: ||' NVL(msr1.company_name, '||''''||NULL_CHAR||''''||')'

Line 17137: 'UPDATE msc_st_regions msr1'

17133: --Different SQL is used because in XML we can identify the latest records
17134: --whereas in batch load we cannot.
17135: v_sql_stmt := 02;
17136: lv_sql_stmt :=
17137: 'UPDATE msc_st_regions msr1'
17138: ||' SET process_flag = '||G_ERROR_FLG||','
17139: ||' error_text = '||''''||lv_message_text||''''
17140: ||' WHERE EXISTS( SELECT 1 '
17141: ||' FROM msc_st_regions msr2'

Line 17141: ||' FROM msc_st_regions msr2'

17137: 'UPDATE msc_st_regions msr1'
17138: ||' SET process_flag = '||G_ERROR_FLG||','
17139: ||' error_text = '||''''||lv_message_text||''''
17140: ||' WHERE EXISTS( SELECT 1 '
17141: ||' FROM msc_st_regions msr2'
17142: ||' WHERE msr2.sr_instance_code = msr1.sr_instance_code'
17143: ||' AND msr2.region_type = msr1.region_type'
17144: ||' AND NVL(msr2.company_name, '||''''||NULL_CHAR||''''||') = '
17145: ||' NVL(msr1.company_name, '||''''||NULL_CHAR||''''||')'

Line 17208: ' UPDATE msc_st_regions '

17204: CLOSE c1;
17205:
17206: v_sql_stmt := 04;
17207: lv_sql_stmt :=
17208: ' UPDATE msc_st_regions '
17209: ||' SET batch_id = :lv_batch_id'
17210: ||' WHERE process_flag IN ('||G_IN_PROCESS||','||G_ERROR_FLG||')'
17211: ||' AND sr_instance_code = :v_instance_code'
17212: ||' AND NVL(batch_id,'||NULL_VALUE||') = '||NULL_VALUE

Line 17229: UPDATE msc_st_regions

17225: CLOSE c2;
17226:
17227: v_sql_stmt := 05;
17228: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
17229: UPDATE msc_st_regions
17230: SET st_transaction_id = msc_st_regions_s.NEXTVAL,
17231: refresh_number = v_refresh_id,
17232: last_update_date = v_current_date,
17233: last_updated_by = v_current_user,

Line 17230: SET st_transaction_id = msc_st_regions_s.NEXTVAL,

17226:
17227: v_sql_stmt := 05;
17228: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
17229: UPDATE msc_st_regions
17230: SET st_transaction_id = msc_st_regions_s.NEXTVAL,
17231: refresh_number = v_refresh_id,
17232: last_update_date = v_current_date,
17233: last_updated_by = v_current_user,
17234: creation_date = v_current_date,

Line 17244: p_token_value1 => 'MSC_ST_REGIONS');

17240: p_error_code => 'MSC_PP_NO_DELETION',
17241: p_message_text => lv_message_text,
17242: p_error_text => lv_error_text,
17243: p_token1 => 'TABLE_NAME',
17244: p_token_value1 => 'MSC_ST_REGIONS');
17245:
17246:
17247: -- Deletion is not allowed for this table
17248: v_sql_stmt := 06;

Line 17250: 'UPDATE msc_st_regions '

17246:
17247: -- Deletion is not allowed for this table
17248: v_sql_stmt := 06;
17249: lv_sql_stmt :=
17250: 'UPDATE msc_st_regions '
17251: ||' SET process_flag = '||G_ERROR_FLG||','
17252: ||' error_text = '||''''||lv_message_text||''''
17253: ||' WHERE deleted_flag = '||SYS_YES
17254: ||' AND process_flag = '||G_IN_PROCESS

Line 17289: (p_table_name => 'MSC_ST_REGIONS',

17285: lv_where_str := ' AND NVL(deleted_flag,'||NULL_VALUE||') '
17286: ||' NOT IN(1,2)';
17287:
17288: lv_return := MSC_ST_UTIL.LOG_ERROR
17289: (p_table_name => 'MSC_ST_REGIONS',
17290: p_instance_code => v_instance_code,
17291: p_row => lv_column_names,
17292: p_severity => G_SEV_WARNING,
17293: p_message_text => lv_message_text,

Line 17319: 'UPDATE msc_st_regions'

17315: RAISE ex_logging_err;
17316: END IF;
17317:
17318: lv_sql_stmt :=
17319: 'UPDATE msc_st_regions'
17320: ||' SET process_flag ='||G_ERROR_FLG||','
17321: ||' error_text = '||''''||lv_message_text||''''
17322: ||' WHERE region_type NOT IN (0,1,2,3,10) '
17323: ||' AND sr_instance_code = :v_instance_code'

Line 17334: ' UPDATE msc_st_regions '

17330: EXECUTE IMMEDIATE lv_sql_stmt USING v_instance_code,lv_batch_id;
17331:
17332:
17333: lv_sql_stmt :=
17334: ' UPDATE msc_st_regions '
17335: ||' SET parent_region_id = -1'
17336: ||' WHERE process_flag = '||G_IN_PROCESS
17337: ||' AND sr_instance_code = :v_instance_code'
17338: ||' AND region_type in (0,10) '

Line 17361: 'UPDATE msc_st_regions msr1'

17357: RAISE ex_logging_err;
17358: END IF;
17359:
17360: lv_sql_stmt :=
17361: 'UPDATE msc_st_regions msr1'
17362: ||' SET process_flag = '||G_ERROR_FLG||','
17363: ||' error_text = '||''''||lv_message_text||''''
17364: ||' WHERE EXISTS( SELECT 1 '
17365: ||' FROM msc_st_regions msr2'

Line 17365: ||' FROM msc_st_regions msr2'

17361: 'UPDATE msc_st_regions msr1'
17362: ||' SET process_flag = '||G_ERROR_FLG||','
17363: ||' error_text = '||''''||lv_message_text||''''
17364: ||' WHERE EXISTS( SELECT 1 '
17365: ||' FROM msc_st_regions msr2'
17366: ||' WHERE msr2.sr_instance_code = msr1.sr_instance_code'
17367: ||' AND msr2.region_type = msr1.region_type'
17368: ||' AND NVL(msr2.country, '||''''||NULL_CHAR||''''||') = '
17369: ||' NVL(msr1.country, '||''''||NULL_CHAR||''''||')'

Line 17408: 'UPDATE msc_st_regions msr'

17404:
17405: --Deriving region_id
17406:
17407: lv_sql_stmt :=
17408: 'UPDATE msc_st_regions msr'
17409: ||' SET region_id = nvl((SELECT local_id'
17410: ||' FROM msc_local_id_setup mls'
17411: ||' WHERE mls.char1 = msr.sr_instance_code'
17412: ||' AND mls.number1 = msr.region_type'

Line 17451: p_token_value1 => 'MSC_ST_REGIONS');

17447: p_error_code => 'MSC_PP_NO_UPDATION',
17448: p_message_text => lv_message_text,
17449: p_error_text => lv_error_text,
17450: p_token1 => 'TABLE_NAME',
17451: p_token_value1 => 'MSC_ST_REGIONS');
17452:
17453:
17454: -- Error out the record if same region already exists in ODS
17455: -- As we do not allow regions incremental update

Line 17459: 'UPDATE msc_st_regions msr'

17455: -- As we do not allow regions incremental update
17456: -- It has to be purged before loaded again
17457:
17458: lv_sql_stmt :=
17459: 'UPDATE msc_st_regions msr'
17460: ||' SET msr.process_flag = '||G_ERROR_FLG||','
17461: ||' msr.error_text = '||''''||lv_message_text||''''
17462: ||' WHERE msr.batch_id = :lv_batch_id'
17463: ||' AND msr.sr_instance_code = :v_instance_code'

Line 17484: pEntityName => 'MSC_ST_REGIONS',

17480: (ERRBUF => lv_error_text,
17481: RETCODE => lv_return,
17482: pBatchID => lv_batch_id,
17483: pInstanceCode => v_instance_code,
17484: pEntityName => 'MSC_ST_REGIONS',
17485: pInstanceID => v_instance_id);
17486:
17487: IF NVL(lv_return,0) <> 0 THEN
17488: RAISE ex_logging_err;

Line 17498: UPDATE msc_st_regions

17494:
17495: IF c3%ROWCOUNT > 0 THEN
17496: v_sql_stmt := 12;
17497: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
17498: UPDATE msc_st_regions
17499: SET region_id = msc_st_region_id_s.NEXTVAL
17500: WHERE rowid = lb_rowid(j);
17501:
17502: v_sql_stmt := 13;

Line 17546: FROM msc_st_regions

17542: v_current_date,
17543: v_current_user,
17544: v_current_date,
17545: v_current_user
17546: FROM msc_st_regions
17547: WHERE rowid = lb_rowid(j);
17548:
17549: END IF;
17550: CLOSE c3 ;

Line 17553: ' UPDATE msc_st_regions msr1 '

17549: END IF;
17550: CLOSE c3 ;
17551:
17552: lv_sql_stmt :=
17553: ' UPDATE msc_st_regions msr1 '
17554: ||' SET parent_region_id = (SELECT msr2.region_id'
17555: ||' FROM msc_st_regions msr2'
17556: ||' WHERE msr2.sr_instance_code = msr1.sr_instance_code '
17557: ||' AND msr2.region_type = msr1.region_type - 1 '

Line 17555: ||' FROM msc_st_regions msr2'

17551:
17552: lv_sql_stmt :=
17553: ' UPDATE msc_st_regions msr1 '
17554: ||' SET parent_region_id = (SELECT msr2.region_id'
17555: ||' FROM msc_st_regions msr2'
17556: ||' WHERE msr2.sr_instance_code = msr1.sr_instance_code '
17557: ||' AND msr2.region_type = msr1.region_type - 1 '
17558: ||' AND NVL(msr2.country, NVL(msr1.country, '||''''||NULL_CHAR||''''||')) = '
17559: ||' NVL(msr1.country, '||''''||NULL_CHAR||''''||') '

Line 17585: (p_table_name => 'MSC_ST_REGIONS',

17581:
17582: EXECUTE IMMEDIATE lv_sql_stmt USING v_instance_code, lv_batch_id;
17583:
17584: lv_return := MSC_ST_UTIL.SET_PROCESS_FLAG
17585: (p_table_name => 'MSC_ST_REGIONS',
17586: p_instance_id => v_instance_id,
17587: p_instance_code => v_instance_code,
17588: p_process_flag => G_VALID,
17589: p_error_text => lv_error_text,

Line 17597: (p_table_name => 'MSC_ST_REGIONS',

17593: RAISE ex_logging_err;
17594: END IF;
17595:
17596: lv_return := MSC_ST_UTIL.LOG_ERROR
17597: (p_table_name => 'MSC_ST_REGIONS',
17598: p_instance_code => v_instance_code,
17599: p_row => lv_column_names,
17600: p_severity => G_SEV_ERROR,
17601: p_message_text => NULL,

Line 17675: ||' FROM MSC_ST_REGIONS msr'

17671: ||' msr.CREATED_BY,'
17672: ||' msr.CREATION_DATE,'
17673: ||' msr.LAST_UPDATED_BY,'
17674: ||' msr.LAST_UPDATE_DATE'
17675: ||' FROM MSC_ST_REGIONS msr'
17676: ||' WHERE msr.batch_id = :lv_batch_id'
17677: ||' AND msr.sr_instance_code = :v_instance_code'
17678: ||' AND msr.process_flag = '||G_VALID ;
17679:

Line 33255: SET st_transaction_id = msc_st_regions_s.NEXTVAL,

33251: CLOSE c2;
33252: v_sql_stmt := 03;
33253: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
33254: UPDATE msc_st_resource_setups
33255: SET st_transaction_id = msc_st_regions_s.NEXTVAL,
33256: refresh_id = v_refresh_id,
33257: last_update_date = v_current_date,
33258: last_updated_by = v_current_user,
33259: creation_date = v_current_date,

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

55802: lv_count:= SET_IN_PROCESS (p_table_name => 'MSC_ST_ITEM_SOURCING');
55803: IF lv_count > 0 Then
55804: prec.sourcing_rule_flag:= SYS_YES;
55805: End IF;
55806: lv_count:= SET_IN_PROCESS (p_table_name => 'MSC_ST_REGIONS');
55807: IF lv_count > 0 Then
55808: prec.sourcing_rule_flag:= SYS_YES;
55809: End IF;
55810: lv_count:= SET_IN_PROCESS (p_table_name => 'MSC_ST_ZONE_REGIONS');