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_REGION_LOCATIONS

Line 1357: ||' on MSC_ST_REGION_LOCATIONS '

1353: application_short_name => 'MSC',
1354: statement_type => AD_DDL.CREATE_INDEX,
1355: statement =>
1356: 'create index MSC_ST_REGION_LOC_N1_'||v_instance_code
1357: ||' on MSC_ST_REGION_LOCATIONS '
1358: ||'(sr_instance_code, country, state, city, postal_code_from, postal_code_to, location_code ) '
1359: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
1360: object_name =>'MSC_ST_REGION_LOC_N1_'||v_instance_code);
1361:

Line 1390: msc_analyse_tables_pk.analyse_table( 'MSC_ST_REGION_LOCATIONS', v_instance_id, -1);

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

Line 17014: FROM msc_st_region_locations

17010: AND sr_instance_code = v_instance_code;
17011:
17012: CURSOR c6(p_batch_id NUMBER) IS
17013: SELECT rowid
17014: FROM msc_st_region_locations
17015: WHERE process_flag IN (G_IN_PROCESS,G_ERROR_FLG)
17016: AND sr_instance_code = v_instance_code
17017: AND batch_id = p_batch_id;
17018:

Line 17021: FROM msc_st_region_locations

17017: AND batch_id = p_batch_id;
17018:
17019: CURSOR c7(p_batch_id NUMBER) IS
17020: SELECT max(rowid)
17021: FROM msc_st_region_locations
17022: WHERE process_flag = G_IN_PROCESS
17023: AND deleted_flag = SYS_NO
17024: AND NVL(location_id,NULL_VALUE) = NULL_VALUE
17025: AND sr_instance_code = v_instance_code

Line 18276: 'UPDATE msc_st_region_locations msr1 '

18272:
18273: --Duplicate records check for the records whose source is XML
18274: v_sql_stmt := 01;
18275: lv_sql_stmt :=
18276: 'UPDATE msc_st_region_locations msr1 '
18277: ||' SET process_flag = '||G_ERROR_FLG||','
18278: ||' error_text = '||''''||lv_message_text||''''
18279: ||' WHERE message_id < (SELECT MAX(message_id)'
18280: ||' FROM msc_st_region_locations msr2'

Line 18280: ||' FROM msc_st_region_locations msr2'

18276: 'UPDATE msc_st_region_locations msr1 '
18277: ||' SET process_flag = '||G_ERROR_FLG||','
18278: ||' error_text = '||''''||lv_message_text||''''
18279: ||' WHERE message_id < (SELECT MAX(message_id)'
18280: ||' FROM msc_st_region_locations msr2'
18281: ||' WHERE msr2.sr_instance_code = msr1.sr_instance_code'
18282: ||' AND NVL(msr2.region_type, '||NULL_VALUE||') = NVL(msr1.region_type, '||NULL_VALUE||')'
18283: ||' AND NVL(msr2.company_name, '||''''||NULL_CHAR||''''||') = '
18284: ||' NVL(msr1.company_name, '||''''||NULL_CHAR||''''||')'

Line 18331: 'UPDATE msc_st_region_locations msr1'

18327: --Different SQL is used because in XML we can identify the latest records
18328: --whereas in batch load we cannot.
18329: v_sql_stmt := 02;
18330: lv_sql_stmt :=
18331: 'UPDATE msc_st_region_locations msr1'
18332: ||' SET process_flag = '||G_ERROR_FLG||','
18333: ||' error_text = '||''''||lv_message_text||''''
18334: ||' WHERE EXISTS( SELECT 1 '
18335: ||' FROM msc_st_region_locations msr2'

Line 18335: ||' FROM msc_st_region_locations msr2'

18331: 'UPDATE msc_st_region_locations msr1'
18332: ||' SET process_flag = '||G_ERROR_FLG||','
18333: ||' error_text = '||''''||lv_message_text||''''
18334: ||' WHERE EXISTS( SELECT 1 '
18335: ||' FROM msc_st_region_locations msr2'
18336: ||' WHERE msr2.sr_instance_code = msr1.sr_instance_code'
18337: ||' AND NVL(msr2.region_type, '||NULL_VALUE||') = NVL(msr1.region_type,'||NULL_VALUE||')'
18338: ||' AND NVL(msr2.company_name, '||''''||NULL_CHAR||''''||') = '
18339: ||' NVL(msr1.company_name, '||''''||NULL_CHAR||''''||')'

Line 18400: ' UPDATE msc_st_region_locations '

18396: FETCH c1 INTO lv_batch_id;
18397: CLOSE c1;
18398:
18399: lv_sql_stmt :=
18400: ' UPDATE msc_st_region_locations '
18401: ||' SET batch_id = :lv_batch_id'
18402: ||' WHERE process_flag IN ('||G_IN_PROCESS||','||G_ERROR_FLG||')'
18403: ||' AND sr_instance_code = :v_instance_code'
18404: ||' AND NVL(batch_id,'||NULL_VALUE||') = '||NULL_VALUE

Line 18420: UPDATE msc_st_region_locations

18416: CLOSE c6;
18417:
18418:
18419: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
18420: UPDATE msc_st_region_locations
18421: SET st_transaction_id = msc_st_region_locations_s.NEXTVAL,
18422: refresh_number = v_refresh_id,
18423: last_update_date = v_current_date,
18424: last_updated_by = v_current_user,

Line 18421: SET st_transaction_id = msc_st_region_locations_s.NEXTVAL,

18417:
18418:
18419: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
18420: UPDATE msc_st_region_locations
18421: SET st_transaction_id = msc_st_region_locations_s.NEXTVAL,
18422: refresh_number = v_refresh_id,
18423: last_update_date = v_current_date,
18424: last_updated_by = v_current_user,
18425: creation_date = v_current_date,

Line 18435: p_token_value1 => 'MSC_ST_REGION_LOCATIONS');

18431: p_error_code => 'MSC_PP_NO_DELETION',
18432: p_message_text => lv_message_text,
18433: p_error_text => lv_error_text,
18434: p_token1 => 'TABLE_NAME',
18435: p_token_value1 => 'MSC_ST_REGION_LOCATIONS');
18436:
18437:
18438: -- Deletion is not allowed for this table
18439: v_sql_stmt := 06;

Line 18441: 'UPDATE msc_st_region_locations '

18437:
18438: -- Deletion is not allowed for this table
18439: v_sql_stmt := 06;
18440: lv_sql_stmt :=
18441: 'UPDATE msc_st_region_locations '
18442: ||' SET process_flag = '||G_ERROR_FLG||','
18443: ||' error_text = '||''''||lv_message_text||''''
18444: ||' WHERE deleted_flag = '||SYS_YES
18445: ||' AND process_flag = '||G_IN_PROCESS

Line 18479: (p_table_name => 'MSC_ST_REGION_LOCATIONS',

18475: lv_where_str := ' AND NVL(deleted_flag,'||NULL_VALUE||') '
18476: ||' NOT IN(1,2)';
18477:
18478: lv_return := MSC_ST_UTIL.LOG_ERROR
18479: (p_table_name => 'MSC_ST_REGION_LOCATIONS',
18480: p_instance_code => v_instance_code,
18481: p_row => lv_column_names,
18482: p_severity => G_SEV_WARNING,
18483: p_message_text => lv_message_text,

Line 18510: 'UPDATE msc_st_region_locations '

18506:
18507: -- Error out records where location_code is NULL
18508:
18509: lv_sql_stmt :=
18510: 'UPDATE msc_st_region_locations '
18511: ||' SET process_flag = '||G_ERROR_FLG||','
18512: ||' error_text = '||''''||lv_message_text||''''
18513: ||' WHERE sr_instance_code = :v_instance_code'
18514: ||' AND batch_id = :lv_batch_id'

Line 18530: 'UPDATE msc_st_region_locations msr'

18526:
18527: -- Derive region_id
18528:
18529: lv_sql_stmt :=
18530: 'UPDATE msc_st_region_locations msr'
18531: ||' SET region_id = (SELECT local_id'
18532: ||' FROM msc_local_id_setup mls'
18533: ||' WHERE mls.char1 = msr.sr_instance_code'
18534: ||' AND NVL(mls.number1, '||NULL_VALUE||') = NVL(msr.region_type,'||NULL_VALUE||')'

Line 18567: 'UPDATE msc_st_region_locations msr'

18563: v_instance_code;
18564:
18565: --Deriving location_id
18566: lv_sql_stmt :=
18567: 'UPDATE msc_st_region_locations msr'
18568: ||' SET location_id = nvl((SELECT local_id'
18569: ||' FROM msc_local_id_setup mls'
18570: ||' WHERE mls.char1 = msr.sr_instance_code'
18571: ||' AND mls.char3 = msr.location_code'

Line 18591: p_token_value1 => 'MSC_ST_REGION_LOCATIONS');

18587: p_error_code => 'MSC_PP_NO_UPDATION',
18588: p_message_text => lv_message_text,
18589: p_error_text => lv_error_text,
18590: p_token1 => 'TABLE_NAME',
18591: p_token_value1 => 'MSC_ST_REGION_LOCATIONS');
18592:
18593:
18594: -- Error out the record if same region locations already exists in ODS
18595: -- As we do not allow region locations incremental update

Line 18599: 'UPDATE msc_st_region_locations msrl'

18595: -- As we do not allow region locations incremental update
18596: -- It has to be purged before laoded again
18597:
18598: lv_sql_stmt :=
18599: 'UPDATE msc_st_region_locations msrl'
18600: ||' SET msrl.process_flag = '||G_ERROR_FLG||','
18601: ||' msrl.error_text = '||''''||lv_message_text||''''
18602: ||' WHERE msrl.batch_id = :lv_batch_id'
18603: ||' AND msrl.sr_instance_code = :v_instance_code'

Line 18623: pEntityName => 'MSC_ST_REGION_LOCATIONS',

18619: (ERRBUF => lv_error_text,
18620: RETCODE => lv_return,
18621: pBatchID => lv_batch_id,
18622: pInstanceCode => v_instance_code,
18623: pEntityName => 'MSC_ST_REGION_LOCATIONS',
18624: pInstanceID => v_instance_id);
18625:
18626: IF NVL(lv_return,0) <> 0 THEN
18627: RAISE ex_logging_err;

Line 18635: UPDATE msc_st_region_locations

18631: FETCH c7 BULK COLLECT INTO lb_rowid ;
18632:
18633: IF c7%ROWCOUNT > 0 THEN
18634: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
18635: UPDATE msc_st_region_locations
18636: SET location_id = msc_st_location_id_s.NEXTVAL
18637: WHERE rowid = lb_rowid(j);
18638:
18639: -- Insert record in LID

Line 18665: FROM msc_st_region_locations

18661: v_current_date,
18662: v_current_user,
18663: v_current_date,
18664: v_current_user
18665: FROM msc_st_region_locations
18666: WHERE rowid = lb_rowid(j);
18667: END IF;
18668: CLOSE c7;
18669:

Line 18673: 'UPDATE msc_st_region_locations msr'

18669:
18670: --Deriving location_id
18671:
18672: lv_sql_stmt :=
18673: 'UPDATE msc_st_region_locations msr'
18674: ||' SET location_id = nvl((SELECT local_id'
18675: ||' FROM msc_local_id_setup mls'
18676: ||' WHERE mls.char1 = msr.sr_instance_code'
18677: ||' AND mls.char3 = msr.location_code'

Line 18692: (p_table_name => 'MSC_ST_REGION_LOCATIONS',

18688: v_instance_code;
18689:
18690: -- Set the process flag as Valid and populate instance_id
18691: lv_return := MSC_ST_UTIL.SET_PROCESS_FLAG
18692: (p_table_name => 'MSC_ST_REGION_LOCATIONS',
18693: p_instance_id => v_instance_id,
18694: p_instance_code => v_instance_code,
18695: p_process_flag => G_VALID,
18696: p_error_text => lv_error_text,

Line 18707: (p_table_name => 'MSC_ST_REGION_LOCATIONS',

18703:
18704: -- At the end calling the LOG_ERROR for logging all
18705: -- errored out records.
18706: lv_return := MSC_ST_UTIL.LOG_ERROR
18707: (p_table_name => 'MSC_ST_REGION_LOCATIONS',
18708: p_instance_code => v_instance_code,
18709: p_row => lv_column_names,
18710: p_severity => G_SEV_ERROR,
18711: p_message_text => NULL,

Line 18747: ||' FROM MSC_ST_REGION_LOCATIONS msrl'

18743: ||' CREATION_DATE,'
18744: ||' LAST_UPDATED_BY,'
18745: ||' LAST_UPDATE_DATE,'
18746: ||' LAST_UPDATE_LOGIN'
18747: ||' FROM MSC_ST_REGION_LOCATIONS msrl'
18748: ||' WHERE msrl.batch_id = :lv_batch_id'
18749: ||' AND msrl.sr_instance_code = :v_instance_code'
18750: ||' AND msrl.process_flag = '||G_VALID ;
18751:

Line 55818: lv_count:= SET_IN_PROCESS (p_table_name => 'MSC_ST_REGION_LOCATIONS');

55814: lv_count:= SET_IN_PROCESS (p_table_name => 'MSC_ST_REGION_SITES');
55815: IF lv_count > 0 Then
55816: prec.sourcing_rule_flag:= SYS_YES;
55817: End IF;
55818: lv_count:= SET_IN_PROCESS (p_table_name => 'MSC_ST_REGION_LOCATIONS');
55819: IF lv_count > 0 Then
55820: prec.sourcing_rule_flag:= SYS_YES;
55821: End IF;
55822: END IF;