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_ITEM_SUBSTITUTES

Line 1538: 'create index MSC_ST_ITEM_SUBSTITUTES_N1_'||v_instance_code

1534: ad_ddl.do_ddl( applsys_schema => v_applsys_schema,
1535: application_short_name => 'MSC',
1536: statement_type => AD_DDL.CREATE_INDEX,
1537: statement =>
1538: 'create index MSC_ST_ITEM_SUBSTITUTES_N1_'||v_instance_code
1539: ||' on MSC_ST_ITEM_SUBSTITUTES '
1540: ||' (sr_instance_code,item_name,substitute_item_name,customer_name,'
1541: ||' customer_site_code,substitution_set,company_name) '
1542: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',

Line 1539: ||' on MSC_ST_ITEM_SUBSTITUTES '

1535: application_short_name => 'MSC',
1536: statement_type => AD_DDL.CREATE_INDEX,
1537: statement =>
1538: 'create index MSC_ST_ITEM_SUBSTITUTES_N1_'||v_instance_code
1539: ||' on MSC_ST_ITEM_SUBSTITUTES '
1540: ||' (sr_instance_code,item_name,substitute_item_name,customer_name,'
1541: ||' customer_site_code,substitution_set,company_name) '
1542: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
1543: object_name =>'MSC_ST_SAFETY_STOCKS_N1_'||v_instance_code);

Line 1545: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Created Index MSC_ST_ITEM_SUBSTITUTES_N1_'||v_instance_code);

1541: ||' customer_site_code,substitution_set,company_name) '
1542: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
1543: object_name =>'MSC_ST_SAFETY_STOCKS_N1_'||v_instance_code);
1544:
1545: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Created Index MSC_ST_ITEM_SUBSTITUTES_N1_'||v_instance_code);
1546:
1547: EXCEPTION
1548: WHEN OTHERS THEN
1549: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_WARNING,'Error creating Index MSC_ST_ITEM_SUBSTITUTES_N1_'||v_instance_code||'. Error:'||substr(SQLERRM,1,240));

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

1545: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Created Index MSC_ST_ITEM_SUBSTITUTES_N1_'||v_instance_code);
1546:
1547: EXCEPTION
1548: WHEN OTHERS THEN
1549: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_WARNING,'Error creating Index MSC_ST_ITEM_SUBSTITUTES_N1_'||v_instance_code||'. Error:'||substr(SQLERRM,1,240));
1550: END;
1551:
1552: msc_analyse_tables_pk.analyse_table( 'MSC_ST_SAFETY_STOCKS', v_instance_id, -1);
1553: msc_analyse_tables_pk.analyse_table( 'MSC_ST_ITEM_SUBSTITUTES', v_instance_id, -1);

Line 1553: msc_analyse_tables_pk.analyse_table( 'MSC_ST_ITEM_SUBSTITUTES', v_instance_id, -1);

1549: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_WARNING,'Error creating Index MSC_ST_ITEM_SUBSTITUTES_N1_'||v_instance_code||'. Error:'||substr(SQLERRM,1,240));
1550: END;
1551:
1552: msc_analyse_tables_pk.analyse_table( 'MSC_ST_SAFETY_STOCKS', v_instance_id, -1);
1553: msc_analyse_tables_pk.analyse_table( 'MSC_ST_ITEM_SUBSTITUTES', v_instance_id, -1);
1554:
1555: END IF;
1556:
1557: IF v_planners_enabled = SYS_YES THEN

Line 2647: 'drop index MSC_ST_ITEM_SUBSTITUTES_N1_'||v_instance_code,

2643: ad_ddl.do_ddl( applsys_schema => v_applsys_schema,
2644: application_short_name => 'MSC',
2645: statement_type => AD_DDL.DROP_INDEX,
2646: statement =>
2647: 'drop index MSC_ST_ITEM_SUBSTITUTES_N1_'||v_instance_code,
2648: object_name => 'MSC_ST_ITEM_SUBSTITUTES_N1_'||v_instance_code);
2649:
2650: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Dropped Index MSC_ST_ITEM_SUBSTITUTES_N1_'||v_instance_code);
2651:

Line 2648: object_name => 'MSC_ST_ITEM_SUBSTITUTES_N1_'||v_instance_code);

2644: application_short_name => 'MSC',
2645: statement_type => AD_DDL.DROP_INDEX,
2646: statement =>
2647: 'drop index MSC_ST_ITEM_SUBSTITUTES_N1_'||v_instance_code,
2648: object_name => 'MSC_ST_ITEM_SUBSTITUTES_N1_'||v_instance_code);
2649:
2650: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Dropped Index MSC_ST_ITEM_SUBSTITUTES_N1_'||v_instance_code);
2651:
2652: EXCEPTION

Line 2650: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Dropped Index MSC_ST_ITEM_SUBSTITUTES_N1_'||v_instance_code);

2646: statement =>
2647: 'drop index MSC_ST_ITEM_SUBSTITUTES_N1_'||v_instance_code,
2648: object_name => 'MSC_ST_ITEM_SUBSTITUTES_N1_'||v_instance_code);
2649:
2650: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Dropped Index MSC_ST_ITEM_SUBSTITUTES_N1_'||v_instance_code);
2651:
2652: EXCEPTION
2653: WHEN OTHERS THEN
2654: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_WARNING,'Error dropping Index MSC_ST_ITEM_SUBSTITUTES_N1_'||v_instance_code||'. Error:'||substr(SQLERRM,1,240));

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

2650: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Dropped Index MSC_ST_ITEM_SUBSTITUTES_N1_'||v_instance_code);
2651:
2652: EXCEPTION
2653: WHEN OTHERS THEN
2654: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_WARNING,'Error dropping Index MSC_ST_ITEM_SUBSTITUTES_N1_'||v_instance_code||'. Error:'||substr(SQLERRM,1,240));
2655: END;
2656:
2657: END IF;
2658:

Line 24266: | DESCRIPTION : This procedure validates the data in MSC_ST_ITEM_SUBSTITUTES|

24262: END LOAD_ITEMS;
24263:
24264:
24265: /*===========================================================================+
24266: | DESCRIPTION : This procedure validates the data in MSC_ST_ITEM_SUBSTITUTES|
24267: | table and derives the id's from the local id's table. |
24268: +===========================================================================*/
24269: PROCEDURE LOAD_ITEM_SUBSTITUTES IS
24270:

Line 24278: lv_batch_id msc_st_item_substitutes.batch_id%TYPE;

24274: lv_error_text VARCHAR2(250);
24275: lv_where_str VARCHAR2(5000);
24276: lv_sql_stmt VARCHAR2(5000);
24277: lv_column_names VARCHAR2(5000); --stores concatenated column names
24278: lv_batch_id msc_st_item_substitutes.batch_id%TYPE;
24279: lv_message_text msc_errors.error_text%TYPE;
24280:
24281: ex_logging_err EXCEPTION;
24282:

Line 24285: FROM msc_st_item_substitutes

24281: ex_logging_err EXCEPTION;
24282:
24283: CURSOR c1(p_batch_id NUMBER) IS
24284: SELECT rowid
24285: FROM msc_st_item_substitutes
24286: WHERE process_flag IN (G_IN_PROCESS,G_ERROR_FLG)
24287: AND batch_id = p_batch_id
24288: AND sr_instance_code = v_instance_code;
24289:

Line 24305: ' UPDATE msc_st_item_substitutes msu1'

24301:
24302: --Duplicate records check for the records whose source is XML
24303: v_sql_stmt := 01;
24304: lv_sql_stmt :=
24305: ' UPDATE msc_st_item_substitutes msu1'
24306: ||' SET process_flag = '||G_ERROR_FLG||','
24307: ||' error_text = '||''''||lv_message_text||''''
24308: ||' WHERE message_id < (SELECT MAX(message_id)'
24309: ||' FROM msc_st_item_substitutes msu2'

Line 24309: ||' FROM msc_st_item_substitutes msu2'

24305: ' UPDATE msc_st_item_substitutes msu1'
24306: ||' SET process_flag = '||G_ERROR_FLG||','
24307: ||' error_text = '||''''||lv_message_text||''''
24308: ||' WHERE message_id < (SELECT MAX(message_id)'
24309: ||' FROM msc_st_item_substitutes msu2'
24310: ||' WHERE msu2.sr_instance_code = msu1.sr_instance_code'
24311: ||' AND NVL(msu2.RELATIONSHIP_TYPE, '||''''||NULL_CHAR||''''||') = '
24312: ||' NVL(msu1.RELATIONSHIP_TYPE, '||''''||NULL_CHAR||''''||')'
24313: ||' AND msu2.ITEM_NAME = msu1.ITEM_NAME'

Line 24353: ' UPDATE msc_st_item_substitutes msu1'

24349: --whereas in batch load we cannot.
24350:
24351: v_sql_stmt := 02;
24352: lv_sql_stmt :=
24353: ' UPDATE msc_st_item_substitutes msu1'
24354: ||' SET process_flag = '||G_ERROR_FLG||','
24355: ||' error_text = '||''''||lv_message_text||''''
24356: ||' WHERE EXISTS( SELECT 1 '
24357: ||' FROM msc_st_item_substitutes msu2'

Line 24357: ||' FROM msc_st_item_substitutes msu2'

24353: ' UPDATE msc_st_item_substitutes msu1'
24354: ||' SET process_flag = '||G_ERROR_FLG||','
24355: ||' error_text = '||''''||lv_message_text||''''
24356: ||' WHERE EXISTS( SELECT 1 '
24357: ||' FROM msc_st_item_substitutes msu2'
24358: ||' WHERE msu2.sr_instance_code = msu1.sr_instance_code'
24359: ||' AND NVL(msu2.RELATIONSHIP_TYPE, '||''''||NULL_CHAR||''''||') = '
24360: ||' NVL(msu1.RELATIONSHIP_TYPE, '||''''||NULL_CHAR||''''||')'
24361: ||' AND msu2.ITEM_NAME = msu1.ITEM_NAME'

Line 24410: ' UPDATE msc_st_item_substitutes '

24406: FROM dual;
24407:
24408: v_sql_stmt := 04;
24409: lv_sql_stmt :=
24410: ' UPDATE msc_st_item_substitutes '
24411: ||' SET batch_id = :lv_batch_id'
24412: ||' WHERE process_flag IN ('||G_IN_PROCESS||','||G_ERROR_FLG||')'
24413: ||' AND sr_instance_code = :v_instance_code'
24414: ||' AND NVL(batch_id,'||NULL_VALUE||') = '||NULL_VALUE

Line 24432: UPDATE msc_st_item_substitutes

24428: CLOSE c1;
24429:
24430: v_sql_stmt := 05;
24431: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
24432: UPDATE msc_st_item_substitutes
24433: SET st_transaction_id = msc_st_item_substitute_s.NEXTVAL,
24434: last_update_date = v_current_date,
24435: last_updated_by = v_current_user,
24436: creation_date = v_current_date,

Line 24446: p_token_value1 => 'MSC_ST_ITEM_SUBSTITUTES');

24442: p_error_code => 'MSC_PP_NO_DELETION',
24443: p_message_text => lv_message_text,
24444: p_error_text => lv_error_text,
24445: p_token1 => 'TABLE_NAME',
24446: p_token_value1 => 'MSC_ST_ITEM_SUBSTITUTES');
24447:
24448: IF lv_return <> 0 THEN
24449: RAISE ex_logging_err;
24450: END IF;

Line 24455: ' UPDATE msc_st_item_substitutes '

24451:
24452: --Deletion is not allowed for this entity.
24453: v_sql_stmt := 06;
24454: lv_sql_stmt :=
24455: ' UPDATE msc_st_item_substitutes '
24456: ||' SET process_flag = '||G_ERROR_FLG||','
24457: ||' error_text = '||''''||lv_message_text||''''
24458: ||' WHERE deleted_flag = '||SYS_YES
24459: ||' AND process_flag = '||G_IN_PROCESS

Line 24476: (p_table_name => 'msc_st_item_substitutes',

24472: -- RELATIONSHIP_TYPE default to 2
24473:
24474: lv_where_str := ' AND RELATIONSHIP_TYPE IS NULL';
24475: lv_return := MSC_ST_UTIL.LOG_ERROR
24476: (p_table_name => 'msc_st_item_substitutes',
24477: p_instance_code => v_instance_code,
24478: p_row => lv_column_names,
24479: p_severity => G_SEV_WARNING,
24480: p_message_text => lv_message_text,

Line 24511: ' UPDATE msc_st_item_substitutes '

24507:
24508: v_sql_stmt := 99;
24509: lv_sql_stmt :=
24510:
24511: ' UPDATE msc_st_item_substitutes '
24512: ||' SET process_flag = '||G_ERROR_FLG||','
24513: ||' error_text = '||''''||lv_message_text||''''
24514: ||' WHERE nvl(RECIPROCAL_FLAG,'||NULL_VALUE||') not in(1,2)'
24515: ||' AND process_flag = '||G_IN_PROCESS

Line 24545: ' UPDATE msc_st_item_substitutes '

24541:
24542: v_sql_stmt := 98;
24543: lv_sql_stmt :=
24544:
24545: ' UPDATE msc_st_item_substitutes '
24546: ||' SET process_flag = '||G_ERROR_FLG||','
24547: ||' error_text = '||''''||lv_message_text||''''
24548: ||' WHERE nvl(PARTIAL_FULFILLMENT_FLAG,'||NULL_VALUE||') not in(1,2)'
24549: ||' AND relationship_type = 2 '

Line 24575: (p_table_name => 'MSC_ST_ITEM_SUBSTITUTES',

24571: END IF;
24572:
24573: -- Derive organization_id
24574: lv_return :=msc_st_util.derive_partner_org_id
24575: (p_table_name => 'MSC_ST_ITEM_SUBSTITUTES',
24576: p_org_partner_name => 'ORGANIZATION_CODE',
24577: p_org_partner_id => 'ORGANIZATION_ID',
24578: p_instance_code => v_instance_code,
24579: p_partner_type => G_ORGANIZATION,

Line 24604: (p_table_name => 'MSC_ST_ITEM_SUBSTITUTES',

24600: lv_where_str := ' AND RELATIONSHIP_TYPE = 2';
24601:
24602: -- Derive sr_tp_id(Customer)
24603: lv_return :=msc_st_util.derive_partner_org_id
24604: (p_table_name => 'MSC_ST_ITEM_SUBSTITUTES',
24605: p_org_partner_name => 'CUSTOMER_NAME',
24606: p_org_partner_id => 'CUSTOMER_ID',
24607: p_instance_code => v_instance_code,
24608: p_partner_type => G_CUSTOMER,

Line 24633: (p_table_name => 'MSC_ST_ITEM_SUBSTITUTES',

24629:
24630: lv_where_str := ' AND RELATIONSHIP_TYPE = 2';
24631: -- Derive sr_tp_site_id(Customer)
24632: lv_return := MSC_ST_UTIL.DERIVE_PARTNER_SITE_ID
24633: (p_table_name => 'MSC_ST_ITEM_SUBSTITUTES',
24634: p_partner_name => 'CUSTOMER_NAME',
24635: p_partner_site_code => 'CUSTOMER_SITE_CODE',
24636: p_partner_site_id => 'CUSTOMER_SITE_ID',
24637: p_instance_code => v_instance_code,

Line 24662: (p_table_name =>'MSC_ST_ITEM_SUBSTITUTES',

24658: p_token_value1 => 'ITEM_NAME');
24659:
24660:
24661: lv_return := MSC_ST_UTIL.DERIVE_ITEM_ID
24662: (p_table_name =>'MSC_ST_ITEM_SUBSTITUTES',
24663: p_item_col_name =>'ITEM_NAME',
24664: p_item_col_id =>'LOWER_ITEM_ID',
24665: p_instance_code => v_instance_code,
24666: p_instance_id => v_instance_id,

Line 24692: (p_table_name =>'msc_st_item_substitutes',

24688: p_token_value1 => 'SUBSTITUTE_ITEM_NAME');
24689:
24690:
24691: lv_return := MSC_ST_UTIL.DERIVE_ITEM_ID
24692: (p_table_name =>'msc_st_item_substitutes',
24693: p_item_col_name =>'SUBSTITUTE_ITEM_NAME',
24694: p_item_col_id =>'HIGHER_ITEM_ID',
24695: p_instance_code => v_instance_code,
24696: p_instance_id => v_instance_id,

Line 24714: (p_table_name => 'msc_st_item_substitutes',

24710:
24711: lv_where_str :=
24712: ' AND NVL(SUBSTITUTION_SET,'||''''||NULL_CHAR||''''||') = '||''''||NULL_CHAR||'''';
24713: lv_return := MSC_ST_UTIL.LOG_ERROR
24714: (p_table_name => 'msc_st_item_substitutes',
24715: p_instance_code => v_instance_code,
24716: p_row => lv_column_names,
24717: p_severity => G_SEV_WARNING,
24718: p_message_text => lv_message_text,

Line 24737: p_token_value1 => 'MSC_ST_ITEM_SUBSTITUTES');

24733: p_error_code => 'MSC_PP_NO_UPDATION',
24734: p_message_text => lv_message_text,
24735: p_error_text => lv_error_text,
24736: p_token1 => 'TABLE_NAME',
24737: p_token_value1 => 'MSC_ST_ITEM_SUBSTITUTES');
24738:
24739: IF lv_return <> 0 THEN
24740: RAISE ex_logging_err;
24741: END IF;

Line 24748: ' UPDATE msc_st_item_substitutes mis1'

24744: --Post-Processor in sync with the collection program).
24745:
24746: v_sql_stmt := 09;
24747: lv_sql_stmt :=
24748: ' UPDATE msc_st_item_substitutes mis1'
24749: ||' SET process_flag = '||G_ERROR_FLG||','
24750: ||' error_text = '||''''||lv_message_text||''''
24751: ||' WHERE process_flag = '||G_IN_PROCESS
24752: ||' AND batch_id = :lv_batch_id'

Line 24791: ' UPDATE msc_st_item_substitutes '

24787: -- check if relationship_type not in 2,5,8,18
24788:
24789: v_sql_stmt := 12;
24790: lv_sql_stmt :=
24791: ' UPDATE msc_st_item_substitutes '
24792: ||' SET process_flag = '||G_ERROR_FLG||','
24793: ||' error_text = '||''''||lv_message_text||''''
24794: ||' WHERE RELATIONSHIP_TYPE not in(2,5,8,18)'
24795: ||' AND process_flag = '||G_IN_PROCESS

Line 24814: pEntityName => 'MSC_ST_ITEM_SUBSTITUTES',

24810: (ERRBUF => lv_error_text,
24811: RETCODE => lv_return,
24812: pBatchID => lv_batch_id,
24813: pInstanceCode => v_instance_code,
24814: pEntityName => 'MSC_ST_ITEM_SUBSTITUTES',
24815: pInstanceID => v_instance_id);
24816:
24817: IF NVL(lv_return,0) <> 0 THEN
24818: RAISE ex_logging_err;

Line 24824: (p_table_name => 'MSC_ST_ITEM_SUBSTITUTES',

24820:
24821: -- Set the process flag as Valid and derive sr_instance_id
24822:
24823: lv_return := MSC_ST_UTIL.SET_PROCESS_FLAG
24824: (p_table_name => 'MSC_ST_ITEM_SUBSTITUTES',
24825: p_instance_id => v_instance_id,
24826: p_instance_code => v_instance_code,
24827: p_process_flag => G_VALID,
24828: p_error_text => lv_error_text,

Line 24839: (p_table_name => 'MSC_ST_ITEM_SUBSTITUTES',

24835:
24836: -- Inserting all the errored out records into MSC_ERRORS
24837:
24838: lv_return := MSC_ST_UTIL.LOG_ERROR
24839: (p_table_name => 'MSC_ST_ITEM_SUBSTITUTES',
24840: p_instance_code => v_instance_code,
24841: p_row => lv_column_names,
24842: p_severity => G_SEV_ERROR,
24843: p_error_text => lv_error_text,

Line 24899: ||' FROM msc_st_item_substitutes'

24895: ||' LAST_UPDATED_BY,'
24896: ||' LAST_UPDATE_LOGIN,'
24897: ||' CREATION_DATE,'
24898: ||' CREATED_BY '
24899: ||' FROM msc_st_item_substitutes'
24900: ||' WHERE sr_instance_code = :sr_instance_code'
24901: ||' AND batch_id = :lv_batch_id'
24902: ||' AND process_flag = '||G_VALID;
24903:

Line 36519: p_token_value2 => 'MSC_ST_ITEM_SUBSTITUTES/MSC_ITEM_SUBSTITUTES',

36515: p_token1 => 'COLUMN_NAMES',
36516: p_token_value1 => 'ORIGINAL_ITEM,ITEM_NAME,'
36517: ||'RECIPROCAL_FLAG,SR_INSTANCE_CODE',
36518: p_token2 => 'MASTER_TABLE',
36519: p_token_value2 => 'MSC_ST_ITEM_SUBSTITUTES/MSC_ITEM_SUBSTITUTES',
36520: p_token3 => 'CHILD_TABLE' ,
36521: p_token_value3 => 'MSC_ST_SALES_ORDERS' );
36522:
36523: IF lv_return <> 0 THEN

Line 36550: FROM msc_st_item_substitutes mss

36546: AND mis.sr_instance_id = v_instance_id
36547: AND mis.plan_id = -1
36548: UNION
36549: SELECT 1
36550: FROM msc_st_item_substitutes mss
36551: WHERE ((mss.reciprocal_flag = 1
36552: AND ((mss.lower_item_id = original_item_id
36553: AND mss.higher_item_id = inventory_item_id)
36554: OR (mss.higher_item_id = original_item_id

Line 56033: lv_count:= SET_IN_PROCESS (p_table_name => 'MSC_ST_ITEM_SUBSTITUTES');

56029:
56030: -- Product Item Substitute
56031:
56032: IF v_item_substitute_enabled = SYS_YES THEN
56033: lv_count:= SET_IN_PROCESS (p_table_name => 'MSC_ST_ITEM_SUBSTITUTES');
56034: IF lv_count > 0 Then
56035: prec.item_subst_flag:= SYS_YES;
56036: End IF;
56037: END IF;