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 1530: 'create index MSC_ST_ITEM_SUBSTITUTES_N1_'||v_instance_code

1526: ad_ddl.do_ddl( applsys_schema => v_applsys_schema,
1527: application_short_name => 'MSC',
1528: statement_type => AD_DDL.CREATE_INDEX,
1529: statement =>
1530: 'create index MSC_ST_ITEM_SUBSTITUTES_N1_'||v_instance_code
1531: ||' on MSC_ST_ITEM_SUBSTITUTES '
1532: ||' (sr_instance_code,item_name,substitute_item_name,customer_name,'
1533: ||' customer_site_code,substitution_set,company_name) '
1534: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',

Line 1531: ||' on MSC_ST_ITEM_SUBSTITUTES '

1527: application_short_name => 'MSC',
1528: statement_type => AD_DDL.CREATE_INDEX,
1529: statement =>
1530: 'create index MSC_ST_ITEM_SUBSTITUTES_N1_'||v_instance_code
1531: ||' on MSC_ST_ITEM_SUBSTITUTES '
1532: ||' (sr_instance_code,item_name,substitute_item_name,customer_name,'
1533: ||' customer_site_code,substitution_set,company_name) '
1534: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
1535: object_name =>'MSC_ST_SAFETY_STOCKS_N1_'||v_instance_code);

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

1533: ||' customer_site_code,substitution_set,company_name) '
1534: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
1535: object_name =>'MSC_ST_SAFETY_STOCKS_N1_'||v_instance_code);
1536:
1537: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Created Index MSC_ST_ITEM_SUBSTITUTES_N1_'||v_instance_code);
1538:
1539: EXCEPTION
1540: WHEN OTHERS THEN
1541: 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 1541: 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));

1537: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Created Index MSC_ST_ITEM_SUBSTITUTES_N1_'||v_instance_code);
1538:
1539: EXCEPTION
1540: WHEN OTHERS THEN
1541: 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));
1542: END;
1543:
1544: msc_analyse_tables_pk.analyse_table( 'MSC_ST_SAFETY_STOCKS', v_instance_id, -1);
1545: msc_analyse_tables_pk.analyse_table( 'MSC_ST_ITEM_SUBSTITUTES', v_instance_id, -1);

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

1541: 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));
1542: END;
1543:
1544: msc_analyse_tables_pk.analyse_table( 'MSC_ST_SAFETY_STOCKS', v_instance_id, -1);
1545: msc_analyse_tables_pk.analyse_table( 'MSC_ST_ITEM_SUBSTITUTES', v_instance_id, -1);
1546:
1547: END IF;
1548:
1549: IF v_planners_enabled = SYS_YES THEN

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

2635: ad_ddl.do_ddl( applsys_schema => v_applsys_schema,
2636: application_short_name => 'MSC',
2637: statement_type => AD_DDL.DROP_INDEX,
2638: statement =>
2639: 'drop index MSC_ST_ITEM_SUBSTITUTES_N1_'||v_instance_code,
2640: object_name => 'MSC_ST_ITEM_SUBSTITUTES_N1_'||v_instance_code);
2641:
2642: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Dropped Index MSC_ST_ITEM_SUBSTITUTES_N1_'||v_instance_code);
2643:

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

2636: application_short_name => 'MSC',
2637: statement_type => AD_DDL.DROP_INDEX,
2638: statement =>
2639: 'drop index MSC_ST_ITEM_SUBSTITUTES_N1_'||v_instance_code,
2640: object_name => 'MSC_ST_ITEM_SUBSTITUTES_N1_'||v_instance_code);
2641:
2642: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Dropped Index MSC_ST_ITEM_SUBSTITUTES_N1_'||v_instance_code);
2643:
2644: EXCEPTION

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

2638: statement =>
2639: 'drop index MSC_ST_ITEM_SUBSTITUTES_N1_'||v_instance_code,
2640: object_name => 'MSC_ST_ITEM_SUBSTITUTES_N1_'||v_instance_code);
2641:
2642: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Dropped Index MSC_ST_ITEM_SUBSTITUTES_N1_'||v_instance_code);
2643:
2644: EXCEPTION
2645: WHEN OTHERS THEN
2646: 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 2646: 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));

2642: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Dropped Index MSC_ST_ITEM_SUBSTITUTES_N1_'||v_instance_code);
2643:
2644: EXCEPTION
2645: WHEN OTHERS THEN
2646: 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));
2647: END;
2648:
2649: END IF;
2650:

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

20773: END LOAD_ITEMS;
20774:
20775:
20776: /*===========================================================================+
20777: | DESCRIPTION : This procedure validates the data in MSC_ST_ITEM_SUBSTITUTES|
20778: | table and derives the id's from the local id's table. |
20779: +===========================================================================*/
20780: PROCEDURE LOAD_ITEM_SUBSTITUTES IS
20781:

Line 20789: lv_batch_id msc_st_item_substitutes.batch_id%TYPE;

20785: lv_error_text VARCHAR2(250);
20786: lv_where_str VARCHAR2(5000);
20787: lv_sql_stmt VARCHAR2(5000);
20788: lv_column_names VARCHAR2(5000); --stores concatenated column names
20789: lv_batch_id msc_st_item_substitutes.batch_id%TYPE;
20790: lv_message_text msc_errors.error_text%TYPE;
20791:
20792: ex_logging_err EXCEPTION;
20793:

Line 20796: FROM msc_st_item_substitutes

20792: ex_logging_err EXCEPTION;
20793:
20794: CURSOR c1(p_batch_id NUMBER) IS
20795: SELECT rowid
20796: FROM msc_st_item_substitutes
20797: WHERE process_flag IN (G_IN_PROCESS,G_ERROR_FLG)
20798: AND batch_id = p_batch_id
20799: AND sr_instance_code = v_instance_code;
20800:

Line 20816: ' UPDATE msc_st_item_substitutes msu1'

20812:
20813: --Duplicate records check for the records whose source is XML
20814: v_sql_stmt := 01;
20815: lv_sql_stmt :=
20816: ' UPDATE msc_st_item_substitutes msu1'
20817: ||' SET process_flag = '||G_ERROR_FLG||','
20818: ||' error_text = '||''''||lv_message_text||''''
20819: ||' WHERE message_id < (SELECT MAX(message_id)'
20820: ||' FROM msc_st_item_substitutes msu2'

Line 20820: ||' FROM msc_st_item_substitutes msu2'

20816: ' UPDATE msc_st_item_substitutes msu1'
20817: ||' SET process_flag = '||G_ERROR_FLG||','
20818: ||' error_text = '||''''||lv_message_text||''''
20819: ||' WHERE message_id < (SELECT MAX(message_id)'
20820: ||' FROM msc_st_item_substitutes msu2'
20821: ||' WHERE msu2.sr_instance_code = msu1.sr_instance_code'
20822: ||' AND NVL(msu2.RELATIONSHIP_TYPE, '||''''||NULL_CHAR||''''||') = '
20823: ||' NVL(msu1.RELATIONSHIP_TYPE, '||''''||NULL_CHAR||''''||')'
20824: ||' AND msu2.ITEM_NAME = msu1.ITEM_NAME'

Line 20864: ' UPDATE msc_st_item_substitutes msu1'

20860: --whereas in batch load we cannot.
20861:
20862: v_sql_stmt := 02;
20863: lv_sql_stmt :=
20864: ' UPDATE msc_st_item_substitutes msu1'
20865: ||' SET process_flag = '||G_ERROR_FLG||','
20866: ||' error_text = '||''''||lv_message_text||''''
20867: ||' WHERE EXISTS( SELECT 1 '
20868: ||' FROM msc_st_item_substitutes msu2'

Line 20868: ||' FROM msc_st_item_substitutes msu2'

20864: ' UPDATE msc_st_item_substitutes msu1'
20865: ||' SET process_flag = '||G_ERROR_FLG||','
20866: ||' error_text = '||''''||lv_message_text||''''
20867: ||' WHERE EXISTS( SELECT 1 '
20868: ||' FROM msc_st_item_substitutes msu2'
20869: ||' WHERE msu2.sr_instance_code = msu1.sr_instance_code'
20870: ||' AND NVL(msu2.RELATIONSHIP_TYPE, '||''''||NULL_CHAR||''''||') = '
20871: ||' NVL(msu1.RELATIONSHIP_TYPE, '||''''||NULL_CHAR||''''||')'
20872: ||' AND msu2.ITEM_NAME = msu1.ITEM_NAME'

Line 20921: ' UPDATE msc_st_item_substitutes '

20917: FROM dual;
20918:
20919: v_sql_stmt := 04;
20920: lv_sql_stmt :=
20921: ' UPDATE msc_st_item_substitutes '
20922: ||' SET batch_id = :lv_batch_id'
20923: ||' WHERE process_flag IN ('||G_IN_PROCESS||','||G_ERROR_FLG||')'
20924: ||' AND sr_instance_code = :v_instance_code'
20925: ||' AND NVL(batch_id,'||NULL_VALUE||') = '||NULL_VALUE

Line 20943: UPDATE msc_st_item_substitutes

20939: CLOSE c1;
20940:
20941: v_sql_stmt := 05;
20942: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
20943: UPDATE msc_st_item_substitutes
20944: SET st_transaction_id = msc_st_item_substitute_s.NEXTVAL,
20945: last_update_date = v_current_date,
20946: last_updated_by = v_current_user,
20947: creation_date = v_current_date,

Line 20957: p_token_value1 => 'MSC_ST_ITEM_SUBSTITUTES');

20953: p_error_code => 'MSC_PP_NO_DELETION',
20954: p_message_text => lv_message_text,
20955: p_error_text => lv_error_text,
20956: p_token1 => 'TABLE_NAME',
20957: p_token_value1 => 'MSC_ST_ITEM_SUBSTITUTES');
20958:
20959: IF lv_return <> 0 THEN
20960: RAISE ex_logging_err;
20961: END IF;

Line 20966: ' UPDATE msc_st_item_substitutes '

20962:
20963: --Deletion is not allowed for this entity.
20964: v_sql_stmt := 06;
20965: lv_sql_stmt :=
20966: ' UPDATE msc_st_item_substitutes '
20967: ||' SET process_flag = '||G_ERROR_FLG||','
20968: ||' error_text = '||''''||lv_message_text||''''
20969: ||' WHERE deleted_flag = '||SYS_YES
20970: ||' AND process_flag = '||G_IN_PROCESS

Line 20987: (p_table_name => 'msc_st_item_substitutes',

20983: -- RELATIONSHIP_TYPE default to 2
20984:
20985: lv_where_str := ' AND RELATIONSHIP_TYPE IS NULL';
20986: lv_return := MSC_ST_UTIL.LOG_ERROR
20987: (p_table_name => 'msc_st_item_substitutes',
20988: p_instance_code => v_instance_code,
20989: p_row => lv_column_names,
20990: p_severity => G_SEV_WARNING,
20991: p_message_text => lv_message_text,

Line 21022: ' UPDATE msc_st_item_substitutes '

21018:
21019: v_sql_stmt := 99;
21020: lv_sql_stmt :=
21021:
21022: ' UPDATE msc_st_item_substitutes '
21023: ||' SET process_flag = '||G_ERROR_FLG||','
21024: ||' error_text = '||''''||lv_message_text||''''
21025: ||' WHERE nvl(RECIPROCAL_FLAG,'||NULL_VALUE||') not in(1,2)'
21026: ||' AND process_flag = '||G_IN_PROCESS

Line 21056: ' UPDATE msc_st_item_substitutes '

21052:
21053: v_sql_stmt := 98;
21054: lv_sql_stmt :=
21055:
21056: ' UPDATE msc_st_item_substitutes '
21057: ||' SET process_flag = '||G_ERROR_FLG||','
21058: ||' error_text = '||''''||lv_message_text||''''
21059: ||' WHERE nvl(PARTIAL_FULFILLMENT_FLAG,'||NULL_VALUE||') not in(1,2)'
21060: ||' AND relationship_type = 2 '

Line 21086: (p_table_name => 'MSC_ST_ITEM_SUBSTITUTES',

21082: END IF;
21083:
21084: -- Derive organization_id
21085: lv_return :=msc_st_util.derive_partner_org_id
21086: (p_table_name => 'MSC_ST_ITEM_SUBSTITUTES',
21087: p_org_partner_name => 'ORGANIZATION_CODE',
21088: p_org_partner_id => 'ORGANIZATION_ID',
21089: p_instance_code => v_instance_code,
21090: p_partner_type => G_ORGANIZATION,

Line 21115: (p_table_name => 'MSC_ST_ITEM_SUBSTITUTES',

21111: lv_where_str := ' AND RELATIONSHIP_TYPE = 2';
21112:
21113: -- Derive sr_tp_id(Customer)
21114: lv_return :=msc_st_util.derive_partner_org_id
21115: (p_table_name => 'MSC_ST_ITEM_SUBSTITUTES',
21116: p_org_partner_name => 'CUSTOMER_NAME',
21117: p_org_partner_id => 'CUSTOMER_ID',
21118: p_instance_code => v_instance_code,
21119: p_partner_type => G_CUSTOMER,

Line 21144: (p_table_name => 'MSC_ST_ITEM_SUBSTITUTES',

21140:
21141: lv_where_str := ' AND RELATIONSHIP_TYPE = 2';
21142: -- Derive sr_tp_site_id(Customer)
21143: lv_return := MSC_ST_UTIL.DERIVE_PARTNER_SITE_ID
21144: (p_table_name => 'MSC_ST_ITEM_SUBSTITUTES',
21145: p_partner_name => 'CUSTOMER_NAME',
21146: p_partner_site_code => 'CUSTOMER_SITE_CODE',
21147: p_partner_site_id => 'CUSTOMER_SITE_ID',
21148: p_instance_code => v_instance_code,

Line 21173: (p_table_name =>'MSC_ST_ITEM_SUBSTITUTES',

21169: p_token_value1 => 'ITEM_NAME');
21170:
21171:
21172: lv_return := MSC_ST_UTIL.DERIVE_ITEM_ID
21173: (p_table_name =>'MSC_ST_ITEM_SUBSTITUTES',
21174: p_item_col_name =>'ITEM_NAME',
21175: p_item_col_id =>'LOWER_ITEM_ID',
21176: p_instance_code => v_instance_code,
21177: p_instance_id => v_instance_id,

Line 21203: (p_table_name =>'msc_st_item_substitutes',

21199: p_token_value1 => 'SUBSTITUTE_ITEM_NAME');
21200:
21201:
21202: lv_return := MSC_ST_UTIL.DERIVE_ITEM_ID
21203: (p_table_name =>'msc_st_item_substitutes',
21204: p_item_col_name =>'SUBSTITUTE_ITEM_NAME',
21205: p_item_col_id =>'HIGHER_ITEM_ID',
21206: p_instance_code => v_instance_code,
21207: p_instance_id => v_instance_id,

Line 21225: (p_table_name => 'msc_st_item_substitutes',

21221:
21222: lv_where_str :=
21223: ' AND NVL(SUBSTITUTION_SET,'||''''||NULL_CHAR||''''||') = '||''''||NULL_CHAR||'''';
21224: lv_return := MSC_ST_UTIL.LOG_ERROR
21225: (p_table_name => 'msc_st_item_substitutes',
21226: p_instance_code => v_instance_code,
21227: p_row => lv_column_names,
21228: p_severity => G_SEV_WARNING,
21229: p_message_text => lv_message_text,

Line 21248: p_token_value1 => 'MSC_ST_ITEM_SUBSTITUTES');

21244: p_error_code => 'MSC_PP_NO_UPDATION',
21245: p_message_text => lv_message_text,
21246: p_error_text => lv_error_text,
21247: p_token1 => 'TABLE_NAME',
21248: p_token_value1 => 'MSC_ST_ITEM_SUBSTITUTES');
21249:
21250: IF lv_return <> 0 THEN
21251: RAISE ex_logging_err;
21252: END IF;

Line 21259: ' UPDATE msc_st_item_substitutes mis1'

21255: --Post-Processor in sync with the collection program).
21256:
21257: v_sql_stmt := 09;
21258: lv_sql_stmt :=
21259: ' UPDATE msc_st_item_substitutes mis1'
21260: ||' SET process_flag = '||G_ERROR_FLG||','
21261: ||' error_text = '||''''||lv_message_text||''''
21262: ||' WHERE process_flag = '||G_IN_PROCESS
21263: ||' AND batch_id = :lv_batch_id'

Line 21302: ' UPDATE msc_st_item_substitutes '

21298: -- check if relationship_type not in 2,5,8,18
21299:
21300: v_sql_stmt := 12;
21301: lv_sql_stmt :=
21302: ' UPDATE msc_st_item_substitutes '
21303: ||' SET process_flag = '||G_ERROR_FLG||','
21304: ||' error_text = '||''''||lv_message_text||''''
21305: ||' WHERE RELATIONSHIP_TYPE not in(2,5,8,18)'
21306: ||' AND process_flag = '||G_IN_PROCESS

Line 21325: pEntityName => 'MSC_ST_ITEM_SUBSTITUTES',

21321: (ERRBUF => lv_error_text,
21322: RETCODE => lv_return,
21323: pBatchID => lv_batch_id,
21324: pInstanceCode => v_instance_code,
21325: pEntityName => 'MSC_ST_ITEM_SUBSTITUTES',
21326: pInstanceID => v_instance_id);
21327:
21328: IF NVL(lv_return,0) <> 0 THEN
21329: RAISE ex_logging_err;

Line 21335: (p_table_name => 'MSC_ST_ITEM_SUBSTITUTES',

21331:
21332: -- Set the process flag as Valid and derive sr_instance_id
21333:
21334: lv_return := MSC_ST_UTIL.SET_PROCESS_FLAG
21335: (p_table_name => 'MSC_ST_ITEM_SUBSTITUTES',
21336: p_instance_id => v_instance_id,
21337: p_instance_code => v_instance_code,
21338: p_process_flag => G_VALID,
21339: p_error_text => lv_error_text,

Line 21350: (p_table_name => 'MSC_ST_ITEM_SUBSTITUTES',

21346:
21347: -- Inserting all the errored out records into MSC_ERRORS
21348:
21349: lv_return := MSC_ST_UTIL.LOG_ERROR
21350: (p_table_name => 'MSC_ST_ITEM_SUBSTITUTES',
21351: p_instance_code => v_instance_code,
21352: p_row => lv_column_names,
21353: p_severity => G_SEV_ERROR,
21354: p_error_text => lv_error_text,

Line 21410: ||' FROM msc_st_item_substitutes'

21406: ||' LAST_UPDATED_BY,'
21407: ||' LAST_UPDATE_LOGIN,'
21408: ||' CREATION_DATE,'
21409: ||' CREATED_BY '
21410: ||' FROM msc_st_item_substitutes'
21411: ||' WHERE sr_instance_code = :sr_instance_code'
21412: ||' AND batch_id = :lv_batch_id'
21413: ||' AND process_flag = '||G_VALID;
21414:

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

31796: p_token1 => 'COLUMN_NAMES',
31797: p_token_value1 => 'ORIGINAL_ITEM,ITEM_NAME,'
31798: ||'RECIPROCAL_FLAG,SR_INSTANCE_CODE',
31799: p_token2 => 'MASTER_TABLE',
31800: p_token_value2 => 'MSC_ST_ITEM_SUBSTITUTES/MSC_ITEM_SUBSTITUTES',
31801: p_token3 => 'CHILD_TABLE' ,
31802: p_token_value3 => 'MSC_ST_SALES_ORDERS' );
31803:
31804: IF lv_return <> 0 THEN

Line 31831: FROM msc_st_item_substitutes mss

31827: AND mis.sr_instance_id = v_instance_id
31828: AND mis.plan_id = -1
31829: UNION
31830: SELECT 1
31831: FROM msc_st_item_substitutes mss
31832: WHERE ((mss.reciprocal_flag = 1
31833: AND ((mss.lower_item_id = original_item_id
31834: AND mss.higher_item_id = inventory_item_id)
31835: OR (mss.higher_item_id = original_item_id

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

51107:
51108: -- Product Item Substitute
51109:
51110: IF v_item_substitute_enabled = SYS_YES THEN
51111: lv_count:= SET_IN_PROCESS (p_table_name => 'MSC_ST_ITEM_SUBSTITUTES');
51112: IF lv_count > 0 Then
51113: prec.item_subst_flag:= SYS_YES;
51114: End IF;
51115: END IF;