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_ZONE_REGIONS

Line 1330: 'create index MSC_ST_ZONE_REGIONS_N1_'||v_instance_code

1326: ad_ddl.do_ddl( applsys_schema => v_applsys_schema,
1327: application_short_name => 'MSC',
1328: statement_type => AD_DDL.CREATE_INDEX,
1329: statement =>
1330: 'create index MSC_ST_ZONE_REGIONS_N1_'||v_instance_code
1331: ||' on MSC_ST_ZONE_REGIONS '
1332: ||'(sr_instance_code, country, state, city, postal_code_from, postal_code_to, zone ) '
1333: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
1334: object_name =>'MSC_ST_ZONE_REGIONS_N1_'||v_instance_code);

Line 1331: ||' on MSC_ST_ZONE_REGIONS '

1327: application_short_name => 'MSC',
1328: statement_type => AD_DDL.CREATE_INDEX,
1329: statement =>
1330: 'create index MSC_ST_ZONE_REGIONS_N1_'||v_instance_code
1331: ||' on MSC_ST_ZONE_REGIONS '
1332: ||'(sr_instance_code, country, state, city, postal_code_from, postal_code_to, zone ) '
1333: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
1334: object_name =>'MSC_ST_ZONE_REGIONS_N1_'||v_instance_code);
1335:

Line 1334: object_name =>'MSC_ST_ZONE_REGIONS_N1_'||v_instance_code);

1330: 'create index MSC_ST_ZONE_REGIONS_N1_'||v_instance_code
1331: ||' on MSC_ST_ZONE_REGIONS '
1332: ||'(sr_instance_code, country, state, city, postal_code_from, postal_code_to, zone ) '
1333: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
1334: object_name =>'MSC_ST_ZONE_REGIONS_N1_'||v_instance_code);
1335:
1336: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Created Index MSC_ST_ZONE_REGIONS_N1_'||v_instance_code);
1337:
1338: EXCEPTION

Line 1336: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Created Index MSC_ST_ZONE_REGIONS_N1_'||v_instance_code);

1332: ||'(sr_instance_code, country, state, city, postal_code_from, postal_code_to, zone ) '
1333: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
1334: object_name =>'MSC_ST_ZONE_REGIONS_N1_'||v_instance_code);
1335:
1336: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Created Index MSC_ST_ZONE_REGIONS_N1_'||v_instance_code);
1337:
1338: EXCEPTION
1339: WHEN OTHERS THEN
1340: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_WARNING,'Error creating Index MSC_ST_ZONE_REGIONS_N1_'||v_instance_code||'. Error:'||substr(SQLERRM,1,240));

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

1336: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Created Index MSC_ST_ZONE_REGIONS_N1_'||v_instance_code);
1337:
1338: EXCEPTION
1339: WHEN OTHERS THEN
1340: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_WARNING,'Error creating Index MSC_ST_ZONE_REGIONS_N1_'||v_instance_code||'. Error:'||substr(SQLERRM,1,240));
1341: END;
1342:
1343: BEGIN
1344: ad_ddl.do_ddl( applsys_schema => v_applsys_schema,

Line 1381: msc_analyse_tables_pk.analyse_table( 'MSC_ST_ZONE_REGIONS', v_instance_id, -1);

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:
1385: END IF;

Line 2503: object_name => 'MSC_ST_ZONE_REGIONS_N1_'||v_instance_code);

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:
2507: EXCEPTION

Line 2505: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Dropped Index MSC_ST_ZONE_REGIONS_N1_'||v_instance_code);

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:
2507: EXCEPTION
2508: WHEN OTHERS THEN
2509: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_WARNING,'Error dropping Index MSC_ST_ZONE_REGIONS_N1_'||v_instance_code||'. Error:'||substr(SQLERRM,1,240));

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

2505: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Dropped Index MSC_ST_ZONE_REGIONS_N1_'||v_instance_code);
2506:
2507: EXCEPTION
2508: WHEN OTHERS THEN
2509: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_WARNING,'Error dropping Index MSC_ST_ZONE_REGIONS_N1_'||v_instance_code||'. Error:'||substr(SQLERRM,1,240));
2510: END;
2511:
2512: BEGIN
2513: ad_ddl.do_ddl( applsys_schema => v_applsys_schema,

Line 13512: FROM msc_st_zone_regions

13508: AND sr_instance_code = v_instance_code;
13509:
13510: CURSOR c4(p_batch_id NUMBER) IS
13511: SELECT rowid
13512: FROM msc_st_zone_regions
13513: WHERE process_flag IN (G_IN_PROCESS,G_ERROR_FLG)
13514: AND sr_instance_code = v_instance_code
13515: AND batch_id = p_batch_id;
13516:

Line 13519: FROM msc_st_zone_regions

13515: AND batch_id = p_batch_id;
13516:
13517: CURSOR c5(p_batch_id NUMBER) IS
13518: SELECT rowid
13519: FROM msc_st_zone_regions
13520: WHERE NVL(zone_region_id,NULL_VALUE) = NULL_VALUE
13521: AND process_flag = G_IN_PROCESS
13522: AND NVL(batch_id,NULL_VALUE) =p_batch_id
13523: AND sr_instance_code = v_instance_code;

Line 14221: 'UPDATE msc_st_zone_regions mszr1 '

14217:
14218: --Duplicate records check for the records whose source is XML
14219: v_sql_stmt := 01;
14220: lv_sql_stmt :=
14221: 'UPDATE msc_st_zone_regions mszr1 '
14222: ||' SET process_flag = '||G_ERROR_FLG||','
14223: ||' error_text = '||''''||lv_message_text||''''
14224: ||' WHERE message_id < (SELECT MAX(message_id)'
14225: ||' FROM msc_st_zone_regions mszr2'

Line 14225: ||' FROM msc_st_zone_regions mszr2'

14221: 'UPDATE msc_st_zone_regions mszr1 '
14222: ||' SET process_flag = '||G_ERROR_FLG||','
14223: ||' error_text = '||''''||lv_message_text||''''
14224: ||' WHERE message_id < (SELECT MAX(message_id)'
14225: ||' FROM msc_st_zone_regions mszr2'
14226: ||' WHERE mszr2.sr_instance_code = mszr1.sr_instance_code'
14227: ||' AND mszr2.zone = mszr1.zone'
14228: ||' AND NVL(mszr2.country, NVL(mszr1.country, '||''''||NULL_CHAR||''''||')) = '
14229: ||' NVL(mszr1.country, NVL(mszr2.country, '||''''||NULL_CHAR||''''||'))'

Line 14271: 'UPDATE msc_st_zone_regions mszr1'

14267: --Different SQL is used because in XML we can identify the latest records
14268: --whereas in batch load we cannot.
14269: v_sql_stmt := 02;
14270: lv_sql_stmt :=
14271: 'UPDATE msc_st_zone_regions mszr1'
14272: ||' SET process_flag = '||G_ERROR_FLG||','
14273: ||' error_text = '||''''||lv_message_text||''''
14274: ||' WHERE EXISTS( SELECT 1 '
14275: ||' FROM msc_st_zone_regions mszr2'

Line 14275: ||' FROM msc_st_zone_regions mszr2'

14271: 'UPDATE msc_st_zone_regions mszr1'
14272: ||' SET process_flag = '||G_ERROR_FLG||','
14273: ||' error_text = '||''''||lv_message_text||''''
14274: ||' WHERE EXISTS( SELECT 1 '
14275: ||' FROM msc_st_zone_regions mszr2'
14276: ||' WHERE mszr2.sr_instance_code = mszr1.sr_instance_code'
14277: ||' AND mszr2.zone = mszr1.zone'
14278: ||' AND NVL(mszr2.country, NVL(mszr1.country, '||''''||NULL_CHAR||''''||')) = '
14279: ||' NVL(mszr1.country, NVL(mszr2.country, '||''''||NULL_CHAR||''''||'))'

Line 14327: 'UPDATE msc_st_zone_regions '

14323: INTO lv_batch_id
14324: FROM DUAL;
14325:
14326: lv_sql_stmt :=
14327: 'UPDATE msc_st_zone_regions '
14328: ||' SET batch_id = :lv_batch_id'
14329: ||' WHERE process_flag IN ('||G_IN_PROCESS||','||G_ERROR_FLG||')'
14330: ||' AND sr_instance_code = :v_instance_code'
14331: ||' AND NVL(batch_id,'||NULL_VALUE||') = '||NULL_VALUE

Line 14350: UPDATE msc_st_zone_regions

14346: CLOSE c4;
14347:
14348: v_sql_stmt := 05;
14349: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
14350: UPDATE msc_st_zone_regions
14351: SET st_transaction_id = msc_st_zone_regions_s.NEXTVAL,
14352: refresh_number = v_refresh_id,
14353: last_update_date = v_current_date,
14354: last_updated_by = v_current_user,

Line 14351: SET st_transaction_id = msc_st_zone_regions_s.NEXTVAL,

14347:
14348: v_sql_stmt := 05;
14349: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
14350: UPDATE msc_st_zone_regions
14351: SET st_transaction_id = msc_st_zone_regions_s.NEXTVAL,
14352: refresh_number = v_refresh_id,
14353: last_update_date = v_current_date,
14354: last_updated_by = v_current_user,
14355: creation_date = v_current_date,

Line 14365: p_token_value1 => 'MSC_ST_ZONE_REGIONS');

14361: p_error_code => 'MSC_PP_NO_DELETION',
14362: p_message_text => lv_message_text,
14363: p_error_text => lv_error_text,
14364: p_token1 => 'TABLE_NAME',
14365: p_token_value1 => 'MSC_ST_ZONE_REGIONS');
14366:
14367:
14368: -- Deletion is not allowed for this table
14369: v_sql_stmt := 06;

Line 14371: 'UPDATE msc_st_zone_regions '

14367:
14368: -- Deletion is not allowed for this table
14369: v_sql_stmt := 06;
14370: lv_sql_stmt :=
14371: 'UPDATE msc_st_zone_regions '
14372: ||' SET process_flag = '||G_ERROR_FLG||','
14373: ||' error_text = '||''''||lv_message_text||''''
14374: ||' WHERE deleted_flag = '||SYS_YES
14375: ||' AND process_flag = '||G_IN_PROCESS

Line 14409: (p_table_name => 'MSC_ST_ZONE_REGIONS',

14405: lv_where_str := ' AND NVL(deleted_flag,'||NULL_VALUE||') '
14406: ||' NOT IN(1,2)';
14407:
14408: lv_return := MSC_ST_UTIL.LOG_ERROR
14409: (p_table_name => 'MSC_ST_ZONE_REGIONS',
14410: p_instance_code => v_instance_code,
14411: p_row => lv_column_names,
14412: p_severity => G_SEV_WARNING,
14413: p_message_text => lv_message_text,

Line 14440: 'UPDATE msc_st_zone_regions '

14436:
14437: -- Error out records where zone/country/country_code is NULL
14438:
14439: lv_sql_stmt :=
14440: 'UPDATE msc_st_zone_regions '
14441: ||' SET process_flag = '||G_ERROR_FLG||','
14442: ||' error_text = '||''''||lv_message_text||''''
14443: ||' WHERE sr_instance_code = :v_instance_code'
14444: ||' AND batch_id = :lv_batch_id'

Line 14463: 'UPDATE msc_st_zone_regions mszr'

14459:
14460:
14461:
14462: lv_sql_stmt :=
14463: 'UPDATE msc_st_zone_regions mszr'
14464: ||' SET region_id = (SELECT local_id'
14465: ||' FROM msc_local_id_setup mls'
14466: ||' WHERE mls.char1 = mszr.sr_instance_code'
14467: ||' AND NVL(mls.char2, '||''''||NULL_CHAR||''''||') = '

Line 14512: 'UPDATE msc_st_zone_regions '

14508:
14509: -- Error out records where region_id is null
14510:
14511: lv_sql_stmt :=
14512: 'UPDATE msc_st_zone_regions '
14513: ||' SET process_flag = '||G_ERROR_FLG||','
14514: ||' error_text = '||''''||lv_message_text||''''
14515: ||' WHERE sr_instance_code = :v_instance_code'
14516: ||' AND batch_id = :lv_batch_id'

Line 14528: 'UPDATE msc_st_zone_regions mszr'

14524: USING v_instance_code,
14525: lv_batch_id;
14526:
14527: lv_sql_stmt :=
14528: 'UPDATE msc_st_zone_regions mszr'
14529: ||' SET parent_region_id = (SELECT local_id'
14530: ||' FROM msc_local_id_setup mls'
14531: ||' WHERE mls.char1 = mszr.sr_instance_code'
14532: ||' AND NVL(mls.char10, '||''''||NULL_CHAR||''''||') = '

Line 14563: 'UPDATE msc_st_zone_regions '

14559:
14560: -- Error out records where parent_region_id is null
14561:
14562: lv_sql_stmt :=
14563: 'UPDATE msc_st_zone_regions '
14564: ||' SET process_flag = '||G_ERROR_FLG||','
14565: ||' error_text = '||''''||lv_message_text||''''
14566: ||' WHERE sr_instance_code = :v_instance_code'
14567: ||' AND batch_id = :lv_batch_id'

Line 14581: 'UPDATE msc_st_zone_regions '

14577:
14578: -- set the default value of party_id to -1
14579:
14580: lv_sql_stmt :=
14581: 'UPDATE msc_st_zone_regions '
14582: ||' SET party_id = -1 '
14583: ||' WHERE sr_instance_code = :v_instance_code'
14584: ||' AND batch_id = :lv_batch_id'
14585: ||' AND process_flag = '||G_IN_PROCESS

Line 14599: 'UPDATE msc_st_zone_regions mszr'

14595:
14596: -- Deriving zone_region_id
14597:
14598: lv_sql_stmt :=
14599: 'UPDATE msc_st_zone_regions mszr'
14600: ||' SET zone_region_id = (SELECT local_id'
14601: ||' FROM msc_local_id_setup mls'
14602: ||' WHERE mls.char1 = mszr.sr_instance_code'
14603: ||' AND NVL(mls.char2, '||''''||NULL_CHAR||''''||') = '

Line 14640: pEntityName => 'MSC_ST_ZONE_REGIONS',

14636: (ERRBUF => lv_error_text,
14637: RETCODE => lv_return,
14638: pBatchID => lv_batch_id,
14639: pInstanceCode => v_instance_code,
14640: pEntityName => 'MSC_ST_ZONE_REGIONS',
14641: pInstanceID => v_instance_id);
14642:
14643: IF NVL(lv_return,0) <> 0 THEN
14644: RAISE ex_logging_err;

Line 14654: UPDATE msc_st_zone_regions

14650:
14651: IF c5%ROWCOUNT > 0 THEN
14652: v_sql_stmt := 12;
14653: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
14654: UPDATE msc_st_zone_regions
14655: SET zone_region_id = msc_st_zone_region_id_s.NEXTVAL
14656: WHERE rowid = lb_rowid(j);
14657:
14658: v_sql_stmt := 13;

Line 14700: FROM msc_st_zone_regions

14696: v_current_date,
14697: v_current_user,
14698: v_current_date,
14699: v_current_user
14700: FROM msc_st_zone_regions
14701: WHERE rowid = lb_rowid(j);
14702:
14703: END IF;
14704: CLOSE c5 ;

Line 14707: (p_table_name => 'MSC_ST_ZONE_REGIONS',

14703: END IF;
14704: CLOSE c5 ;
14705:
14706: lv_return := MSC_ST_UTIL.SET_PROCESS_FLAG
14707: (p_table_name => 'MSC_ST_ZONE_REGIONS',
14708: p_instance_id => v_instance_id,
14709: p_instance_code => v_instance_code,
14710: p_process_flag => G_VALID,
14711: p_error_text => lv_error_text,

Line 14719: (p_table_name => 'MSC_ST_ZONE_REGIONS',

14715: RAISE ex_logging_err;
14716: END IF;
14717:
14718: lv_return := MSC_ST_UTIL.LOG_ERROR
14719: (p_table_name => 'MSC_ST_ZONE_REGIONS',
14720: p_instance_code => v_instance_code,
14721: p_row => lv_column_names,
14722: p_severity => G_SEV_ERROR,
14723: p_message_text => NULL,

Line 14753: ||' FROM MSC_ST_ZONE_REGIONS msr'

14749: ||' msr.CREATED_BY,'
14750: ||' msr.CREATION_DATE,'
14751: ||' msr.LAST_UPDATED_BY,'
14752: ||' msr.LAST_UPDATE_DATE'
14753: ||' FROM MSC_ST_ZONE_REGIONS msr'
14754: ||' WHERE msr.batch_id = :lv_batch_id'
14755: ||' AND msr.sr_instance_code = :v_instance_code'
14756: ||' AND msr.process_flag = '||G_VALID
14757: ||' AND NOT EXISTS'

Line 50896: lv_count:= SET_IN_PROCESS (p_table_name => 'MSC_ST_ZONE_REGIONS');

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');
50897: IF lv_count > 0 Then
50898: prec.sourcing_rule_flag:= SYS_YES;
50899: End IF;
50900: lv_count:= SET_IN_PROCESS (p_table_name => 'MSC_ST_REGION_SITES');