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_CATEGORY_SETS

Line 19208: | DESCRIPTION : This procedure validates the data in MSC_ST_CATEGORY_SETS |

19204: END LOAD_REGIONS;
19205:
19206:
19207: /*==========================================================================+
19208: | DESCRIPTION : This procedure validates the data in MSC_ST_CATEGORY_SETS |
19209: | table. |
19210: +==========================================================================*/
19211: PROCEDURE LOAD_CATEGORY_SET IS
19212: TYPE RowidTab IS TABLE OF ROWID INDEX BY BINARY_INTEGER;

Line 19219: lv_batch_id msc_st_category_sets.batch_id%TYPE;

19215: lv_error_text VARCHAR2(250);
19216: lv_where_str VARCHAR2(5000);
19217: lv_sql_stmt VARCHAR2(5000);
19218: lv_column_names VARCHAR2(5000); --stores concatenated column names
19219: lv_batch_id msc_st_category_sets.batch_id%TYPE;
19220: lv_message_text msc_errors.error_text%TYPE;
19221: ex_logging_err EXCEPTION;
19222:
19223: CURSOR c1(p_batch_id NUMBER) IS

Line 19225: FROM msc_st_category_sets

19221: ex_logging_err EXCEPTION;
19222:
19223: CURSOR c1(p_batch_id NUMBER) IS
19224: SELECT rowid
19225: FROM msc_st_category_sets
19226: WHERE process_flag IN (G_IN_PROCESS,G_ERROR_FLG)
19227: AND batch_id = p_batch_id
19228: AND sr_instance_code = v_instance_code;
19229:

Line 19232: FROM msc_st_category_sets

19228: AND sr_instance_code = v_instance_code;
19229:
19230: CURSOR c2(p_batch_id NUMBER) IS
19231: SELECT rowid
19232: FROM msc_st_category_sets
19233: WHERE NVL(sr_category_set_id,NULL_VALUE) = NULL_VALUE
19234: AND process_flag = G_IN_PROCESS
19235: AND batch_id = p_batch_id
19236: AND sr_instance_code = v_instance_code;

Line 19254: 'UPDATE msc_st_category_sets mcs1 '

19250:
19251: --Duplicate records check for the records whose source is XML
19252: v_sql_stmt := 01;
19253: lv_sql_stmt :=
19254: 'UPDATE msc_st_category_sets mcs1 '
19255: ||' SET process_flag = '||G_ERROR_FLG||','
19256: ||' error_text = '||''''||lv_message_text||''''
19257: ||' WHERE message_id < (SELECT MAX(message_id)'
19258: ||' FROM msc_st_category_sets mcs2'

Line 19258: ||' FROM msc_st_category_sets mcs2'

19254: 'UPDATE msc_st_category_sets mcs1 '
19255: ||' SET process_flag = '||G_ERROR_FLG||','
19256: ||' error_text = '||''''||lv_message_text||''''
19257: ||' WHERE message_id < (SELECT MAX(message_id)'
19258: ||' FROM msc_st_category_sets mcs2'
19259: ||' WHERE mcs2.sr_instance_code = mcs1.sr_instance_code'
19260: ||' AND NVL(mcs2.company_name, '||''''||NULL_CHAR||''''||') = '
19261: ||' NVL(mcs1.company_name, '||''''||NULL_CHAR||''''||')'
19262: ||' AND mcs2.category_set_name = mcs1.category_set_name'

Line 19291: 'UPDATE msc_st_category_sets mcs1'

19287: --Different SQL is used because in XML we can identify the latest records
19288: --whereas in batch load we cannot.
19289: v_sql_stmt := 02;
19290: lv_sql_stmt :=
19291: 'UPDATE msc_st_category_sets mcs1'
19292: ||' SET process_flag = '||G_ERROR_FLG||','
19293: ||' error_text = '||''''||lv_message_text||''''
19294: ||' WHERE EXISTS( SELECT 1 '
19295: ||' FROM msc_st_category_sets mcs2'

Line 19295: ||' FROM msc_st_category_sets mcs2'

19291: 'UPDATE msc_st_category_sets mcs1'
19292: ||' SET process_flag = '||G_ERROR_FLG||','
19293: ||' error_text = '||''''||lv_message_text||''''
19294: ||' WHERE EXISTS( SELECT 1 '
19295: ||' FROM msc_st_category_sets mcs2'
19296: ||' WHERE mcs2.sr_instance_code = mcs1.sr_instance_code'
19297: ||' AND NVL(mcs2.company_name, '||''''||NULL_CHAR||''''||') = '
19298: ||' NVL(mcs1.company_name, '||''''||NULL_CHAR||''''||')'
19299: ||' AND mcs2.category_set_name = mcs1.category_set_name'

Line 19331: ' UPDATE msc_st_category_sets '

19327: FROM dual;
19328:
19329: v_sql_stmt := 04;
19330: lv_sql_stmt :=
19331: ' UPDATE msc_st_category_sets '
19332: ||' SET batch_id = :lv_batch_id'
19333: ||' WHERE process_flag IN ('||G_IN_PROCESS||','||G_ERROR_FLG||')'
19334: ||' AND sr_instance_code = :v_instance_code'
19335: ||' AND NVL(batch_id,'||NULL_VALUE||') = '||NULL_VALUE

Line 19353: UPDATE msc_st_category_sets

19349: CLOSE c1;
19350:
19351: v_sql_stmt := 05;
19352: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
19353: UPDATE msc_st_category_sets
19354: SET st_transaction_id = msc_st_category_sets_s.NEXTVAL,
19355: refresh_id = v_refresh_id,
19356: last_update_date = v_current_date,
19357: last_updated_by = v_current_user,

Line 19354: SET st_transaction_id = msc_st_category_sets_s.NEXTVAL,

19350:
19351: v_sql_stmt := 05;
19352: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
19353: UPDATE msc_st_category_sets
19354: SET st_transaction_id = msc_st_category_sets_s.NEXTVAL,
19355: refresh_id = v_refresh_id,
19356: last_update_date = v_current_date,
19357: last_updated_by = v_current_user,
19358: creation_date = v_current_date,

Line 19368: p_token_value1 => 'MSC_ST_CATEGORY_SETS');

19364: p_error_code => 'MSC_PP_NO_DELETION',
19365: p_message_text => lv_message_text,
19366: p_error_text => lv_error_text,
19367: p_token1 => 'TABLE_NAME',
19368: p_token_value1 => 'MSC_ST_CATEGORY_SETS');
19369:
19370: IF lv_return <> 0 THEN
19371: RAISE ex_logging_err;
19372: END IF;

Line 19377: ' UPDATE msc_st_category_sets '

19373:
19374: --Deletion is not allowed for this entity.
19375: v_sql_stmt := 06;
19376: lv_sql_stmt :=
19377: ' UPDATE msc_st_category_sets '
19378: ||' SET process_flag = '||G_ERROR_FLG||','
19379: ||' error_text = '||''''||lv_message_text||''''
19380: ||' WHERE deleted_flag = '||SYS_YES
19381: ||' AND process_flag = '||G_IN_PROCESS

Line 19413: (p_table_name => 'MSC_ST_CATEGORY_SETS',

19409:
19410: --Logging a warning for those records where the deleted_flag value
19411: --is other than Yes/No.
19412: lv_return := MSC_ST_UTIL.LOG_ERROR
19413: (p_table_name => 'MSC_ST_CATEGORY_SETS',
19414: p_instance_code => v_instance_code,
19415: p_row => lv_column_names,
19416: p_severity => G_SEV_WARNING,
19417: p_message_text => lv_message_text,

Line 19443: ' UPDATE msc_st_category_sets '

19439: END IF;
19440:
19441: v_sql_stmt := 07;
19442: lv_sql_stmt :=
19443: ' UPDATE msc_st_category_sets '
19444: ||' SET process_flag = '||G_ERROR_FLG||','
19445: ||' error_text = '||''''||lv_message_text||''''
19446: ||' WHERE NVL(category_set_name,'||''''||NULL_CHAR||''''||')'
19447: ||' = '||''''||NULL_CHAR||''''

Line 19473: ' UPDATE msc_st_category_sets '

19469: END IF;
19470:
19471: v_sql_stmt := 08;
19472: lv_sql_stmt :=
19473: ' UPDATE msc_st_category_sets '
19474: ||' SET process_flag = '||G_ERROR_FLG||','
19475: ||' error_text = '||''''||lv_message_text||''''
19476: ||' WHERE (NVL(control_level,'||NULL_VALUE||') NOT IN(1,2)'
19477: ||' OR NVL(default_flag,' ||NULL_VALUE||') NOT IN(1,2))'

Line 19503: 'UPDATE msc_st_category_sets mcs1'

19499: --There can be only one record in the staging/ODS with the
19500: --default_flag = 'YES'
19501: v_sql_stmt := 09;
19502: lv_sql_stmt :=
19503: 'UPDATE msc_st_category_sets mcs1'
19504: ||' SET process_flag = '||G_ERROR_FLG||','
19505: ||' error_text = '||''''||lv_message_text||''''
19506: ||' WHERE EXISTS( SELECT 1 '
19507: ||' FROM msc_st_category_sets mcs2'

Line 19507: ||' FROM msc_st_category_sets mcs2'

19503: 'UPDATE msc_st_category_sets mcs1'
19504: ||' SET process_flag = '||G_ERROR_FLG||','
19505: ||' error_text = '||''''||lv_message_text||''''
19506: ||' WHERE EXISTS( SELECT 1 '
19507: ||' FROM msc_st_category_sets mcs2'
19508: ||' WHERE mcs2.sr_instance_code = mcs1.sr_instance_code'
19509: ||' AND mcs2.process_flag IN(2,5)'
19510: ||' AND mcs2.default_flag = '||SYS_YES
19511: ||' GROUP BY sr_instance_code'

Line 19536: ' UPDATE msc_st_category_sets'

19532:
19533: --Derive sr_category_set_id.
19534: v_sql_stmt := 10;
19535: lv_sql_stmt :=
19536: ' UPDATE msc_st_category_sets'
19537: ||' SET sr_category_set_id = (SELECT local_id'
19538: ||' FROM msc_local_id_misc'
19539: ||' WHERE char1 = sr_instance_code'
19540: ||' AND NVL(char2, '||''''||NULL_CHAR||''''||')='

Line 19560: pEntityName => 'MSC_ST_CATEGORY_SETS',

19556: (ERRBUF => lv_error_text,
19557: RETCODE => lv_return,
19558: pBatchID => lv_batch_id,
19559: pInstanceCode => v_instance_code,
19560: pEntityName => 'MSC_ST_CATEGORY_SETS',
19561: pInstanceID => v_instance_id);
19562:
19563: IF NVL(lv_return,0) <> 0 THEN
19564: RAISE ex_logging_err;

Line 19573: UPDATE msc_st_category_sets

19569:
19570: IF c2%ROWCOUNT > 0 THEN
19571: v_sql_stmt := 11;
19572: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
19573: UPDATE msc_st_category_sets
19574: SET sr_category_set_id = msc_st_sr_category_set_id_s.NEXTVAL
19575: WHERE rowid = lb_rowid(j);
19576:
19577: v_sql_stmt := 12;

Line 19607: FROM msc_st_category_sets

19603: v_current_date,
19604: v_current_user,
19605: v_current_date,
19606: v_current_user
19607: FROM msc_st_category_sets
19608: WHERE rowid = lb_rowid(j);
19609:
19610: END IF;
19611: CLOSE c2 ;

Line 19614: (p_table_name => 'MSC_ST_CATEGORY_SETS',

19610: END IF;
19611: CLOSE c2 ;
19612:
19613: lv_return := MSC_ST_UTIL.SET_PROCESS_FLAG
19614: (p_table_name => 'MSC_ST_CATEGORY_SETS',
19615: p_instance_id => v_instance_id,
19616: p_instance_code => v_instance_code,
19617: p_process_flag => G_VALID,
19618: p_error_text => lv_error_text,

Line 19627: (p_table_name => 'MSC_ST_CATEGORY_SETS',

19623: RAISE ex_logging_err;
19624: END IF;
19625:
19626: lv_return := MSC_ST_UTIL.LOG_ERROR
19627: (p_table_name => 'MSC_ST_CATEGORY_SETS',
19628: p_instance_code => v_instance_code,
19629: p_row => lv_column_names,
19630: p_severity => G_SEV_ERROR,
19631: p_message_text => NULL,

Line 27642: lv_default_category_set msc_st_category_sets.category_set_name%TYPE := NULL_CHAR ;

27638: lv_default_sourcing_rule msc_st_item_sourcing.sourcing_rule_name%TYPE
27639: := v_instance_code||':'||'SRULE' ;
27640: lv_default_assignment_set msc_st_item_sourcing.sourcing_rule_name%TYPE
27641: := 'ASET' ;
27642: lv_default_category_set msc_st_category_sets.category_set_name%TYPE := NULL_CHAR ;
27643: lv_batch_id msc_st_item_sourcing.batch_id%TYPE;
27644: lv_message_text msc_errors.error_text%TYPE;
27645:
27646: ex_logging_err EXCEPTION;

Line 27872: ||' FROM msc_st_category_sets '

27868:
27869: lv_sql_stmt := 13;
27870: lv_sql_stmt :=
27871: 'SELECT category_set_name '
27872: ||' FROM msc_st_category_sets '
27873: ||' WHERE sr_instance_id = :v_instance_id'
27874: ||' AND default_flag = 1'
27875: ||' AND deleted_flag = '||SYS_NO
27876: ||' AND process_flag ='|| G_VALID ;

Line 56003: lv_count:= SET_IN_PROCESS (p_table_name => 'MSC_ST_CATEGORY_SETS');

55999: End IF;
56000: END IF;
56001:
56002: IF v_ctg_enabled = SYS_YES THEN
56003: lv_count:= SET_IN_PROCESS (p_table_name => 'MSC_ST_CATEGORY_SETS');
56004: IF lv_count > 0 Then
56005: prec.item_flag:= SYS_YES;
56006: End IF;
56007: END IF;