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_SOURCING

Line 1301: ||' on MSC_ST_ITEM_SOURCING '

1297: application_short_name => 'MSC',
1298: statement_type => AD_DDL.CREATE_INDEX,
1299: statement =>
1300: 'create index MSC_ST_ITEM_SRC_N1_'||v_instance_code
1301: ||' on MSC_ST_ITEM_SOURCING '
1302: ||'(sr_instance_code, sourcing_rule_name, assignment_set_name, organization_code, company_name, receipt_organization_code, source_organization_code) '
1303: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
1304: object_name =>'MSC_ST_ITEM_SRC_N1_'||v_instance_code);
1305:

Line 1387: msc_analyse_tables_pk.analyse_table( 'MSC_ST_ITEM_SOURCING', v_instance_id, -1);

1383: WHEN OTHERS THEN
1384: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_WARNING,'Error creating Index MSC_ST_REGION_SITE_N1_'||v_instance_code||'. Error:'||substr(SQLERRM,1,240));
1385: END;
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);

Line 27638: lv_default_sourcing_rule msc_st_item_sourcing.sourcing_rule_name%TYPE

27634: lv_where_str VARCHAR2(5000);
27635: lv_sql_stmt VARCHAR2(5000);
27636: lv_cursor_stmt VARCHAR2(5000);
27637:
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 ;

Line 27640: lv_default_assignment_set msc_st_item_sourcing.sourcing_rule_name%TYPE

27636: lv_cursor_stmt VARCHAR2(5000);
27637:
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;

Line 27643: lv_batch_id msc_st_item_sourcing.batch_id%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;
27647:

Line 27651: FROM msc_st_item_sourcing

27647:
27648:
27649: CURSOR c2(p_batch_id NUMBER) IS
27650: SELECT rowid
27651: FROM msc_st_item_sourcing
27652: WHERE process_flag IN (G_IN_PROCESS,G_ERROR_FLG)
27653: AND sr_instance_code = v_instance_code
27654: AND batch_id = p_batch_id;
27655:

Line 27658: FROM msc_st_item_sourcing

27654: AND batch_id = p_batch_id;
27655:
27656: CURSOR c3(p_batch_id NUMBER) IS
27657: SELECT max(rowid)
27658: FROM msc_st_item_sourcing
27659: WHERE process_flag = G_IN_PROCESS
27660: AND sr_instance_code = v_instance_code
27661: AND batch_id = p_batch_id
27662: AND NVL(sourcing_rule_id,NULL_VALUE) = NULL_VALUE

Line 27669: FROM msc_st_item_sourcing

27665: organization_code,sourcing_rule_name;
27666:
27667: CURSOR c4(p_batch_id NUMBER) IS
27668: SELECT max(rowid)
27669: FROM msc_st_item_sourcing
27670: WHERE process_flag = G_IN_PROCESS
27671: AND sr_instance_code = v_instance_code
27672: AND batch_id = p_batch_id
27673: AND NVL(assignment_set_id,NULL_VALUE) = NULL_VALUE

Line 27680: FROM msc_st_item_sourcing

27676: assignment_set_name;
27677:
27678: CURSOR c5(p_batch_id NUMBER) IS
27679: SELECT max(rowid)
27680: FROM msc_st_item_sourcing
27681: WHERE process_flag = G_IN_PROCESS
27682: AND sr_instance_code = v_instance_code
27683: AND batch_id = p_batch_id
27684: AND NVL(sr_receipt_id,NULL_VALUE) = NULL_VALUE

Line 27692: FROM msc_st_item_sourcing

27688: receipt_organization_code,receipt_org_instance_code,effective_date;
27689:
27690: CURSOR c6(p_batch_id NUMBER) IS
27691: SELECT max(rowid)
27692: FROM msc_st_item_sourcing
27693: WHERE process_flag = G_IN_PROCESS
27694: AND sr_instance_code = v_instance_code
27695: AND batch_id = p_batch_id
27696: AND NVL(sr_source_id,NULL_VALUE) = NULL_VALUE

Line 27707: FROM msc_st_item_sourcing

27703: effective_date;
27704:
27705: CURSOR c7(p_batch_id NUMBER) IS
27706: SELECT max(rowid)
27707: FROM msc_st_item_sourcing
27708: WHERE process_flag = G_IN_PROCESS
27709: AND sr_instance_code = v_instance_code
27710: AND batch_id = p_batch_id
27711: AND NVL(assignment_id,NULL_VALUE) = NULL_VALUE

Line 27735: 'UPDATE msc_st_item_sourcing msis1'

27731:
27732: v_sql_stmt := 01;
27733:
27734: lv_sql_stmt :=
27735: 'UPDATE msc_st_item_sourcing msis1'
27736: ||' SET process_flag ='||G_ERROR_FLG||','
27737: ||' error_text = '||''''||lv_message_text||''''
27738: ||' WHERE message_id < ( SELECT max(message_id) FROM msc_st_item_sourcing msis2'
27739: ||' WHERE msis2.sr_instance_code = msis1.sr_instance_code'

Line 27738: ||' WHERE message_id < ( SELECT max(message_id) FROM msc_st_item_sourcing msis2'

27734: lv_sql_stmt :=
27735: 'UPDATE msc_st_item_sourcing msis1'
27736: ||' SET process_flag ='||G_ERROR_FLG||','
27737: ||' error_text = '||''''||lv_message_text||''''
27738: ||' WHERE message_id < ( SELECT max(message_id) FROM msc_st_item_sourcing msis2'
27739: ||' WHERE msis2.sr_instance_code = msis1.sr_instance_code'
27740: ||' AND NVL(msis2.organization_code,'||''''||NULL_CHAR||''''||') '
27741: ||' = NVL(msis1.organization_code,'||''''||NULL_CHAR||''''||') '
27742: ||' AND NVL(msis2.sourcing_rule_name,'||''''||NULL_CHAR||''''||') '

Line 27785: 'UPDATE msc_st_item_sourcing msis1'

27781:
27782: v_sql_stmt := 02;
27783:
27784: lv_sql_stmt :=
27785: 'UPDATE msc_st_item_sourcing msis1'
27786: ||' SET process_flag ='||G_ERROR_FLG||','
27787: ||' error_text = '||''''||lv_message_text||''''
27788: ||' WHERE EXISTS ( SELECT 1 FROM msc_st_item_sourcing msis2'
27789: ||' WHERE msis2.sr_instance_code = msis1.sr_instance_code'

Line 27788: ||' WHERE EXISTS ( SELECT 1 FROM msc_st_item_sourcing msis2'

27784: lv_sql_stmt :=
27785: 'UPDATE msc_st_item_sourcing msis1'
27786: ||' SET process_flag ='||G_ERROR_FLG||','
27787: ||' error_text = '||''''||lv_message_text||''''
27788: ||' WHERE EXISTS ( SELECT 1 FROM msc_st_item_sourcing msis2'
27789: ||' WHERE msis2.sr_instance_code = msis1.sr_instance_code'
27790: ||' AND NVL(msis2.organization_code,'||''''||NULL_CHAR||''''||') '
27791: ||' = NVL(msis1.organization_code,'||''''||NULL_CHAR||''''||') '
27792: ||' AND NVL(msis2.sourcing_rule_name,'||''''||NULL_CHAR||''''||') '

Line 27925: ' UPDATE msc_st_item_sourcing '

27921: CLOSE c1;
27922:
27923: v_sql_stmt := 04;
27924: lv_sql_stmt :=
27925: ' UPDATE msc_st_item_sourcing '
27926: ||' SET batch_id = :lv_batch_id'
27927: ||' WHERE process_flag IN ('||G_IN_PROCESS||','||G_ERROR_FLG||')'
27928: ||' AND sr_instance_code = :v_instance_code'
27929: ||' AND NVL(batch_id,'||NULL_VALUE||') = '||NULL_VALUE

Line 27945: UPDATE msc_st_item_sourcing

27941: CLOSE c2;
27942:
27943: v_sql_stmt := 05;
27944: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
27945: UPDATE msc_st_item_sourcing
27946: SET st_transaction_id = msc_st_item_sourcing_s.NEXTVAL,
27947: refresh_id = v_refresh_id,
27948: last_update_date = v_current_date,
27949: last_updated_by = v_current_user,

Line 27946: SET st_transaction_id = msc_st_item_sourcing_s.NEXTVAL,

27942:
27943: v_sql_stmt := 05;
27944: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
27945: UPDATE msc_st_item_sourcing
27946: SET st_transaction_id = msc_st_item_sourcing_s.NEXTVAL,
27947: refresh_id = v_refresh_id,
27948: last_update_date = v_current_date,
27949: last_updated_by = v_current_user,
27950: creation_date = v_current_date,

Line 27962: p_token_value1 => 'MSC_ST_ITEM_SOURCING');

27958: p_error_code => 'MSC_PP_NO_DELETION',
27959: p_message_text => lv_message_text,
27960: p_error_text => lv_error_text,
27961: p_token1 => 'TABLE_NAME',
27962: p_token_value1 => 'MSC_ST_ITEM_SOURCING');
27963:
27964: IF lv_return <> 0 THEN
27965: RAISE ex_logging_err;
27966: END IF;

Line 27972: ' UPDATE msc_st_item_sourcing '

27968: --Deletion is not allowed on this table.
27969:
27970: v_sql_stmt := 06;
27971: lv_sql_stmt :=
27972: ' UPDATE msc_st_item_sourcing '
27973: ||' SET process_flag = '||G_ERROR_FLG||','
27974: ||' error_text = '||''''||lv_message_text||''''
27975: ||' WHERE deleted_flag = '||SYS_YES
27976: ||' AND process_flag = '||G_IN_PROCESS

Line 28008: (p_table_name => 'MSC_ST_ITEM_SOURCING',

28004: lv_where_str :=
28005: ' AND NVL(deleted_flag,'||NULL_VALUE||') NOT IN(1,2)';
28006:
28007: lv_return := MSC_ST_UTIL.LOG_ERROR
28008: (p_table_name => 'MSC_ST_ITEM_SOURCING',
28009: p_instance_code => v_instance_code,
28010: p_row => lv_column_names,
28011: p_severity => G_SEV_WARNING,
28012: p_message_text => lv_message_text,

Line 28040: 'UPDATE msc_st_item_sourcing'

28036:
28037: -- Error out record where assignment_type is NOT IN (1,2,3,4,5,6)
28038: v_sql_stmt := 07;
28039: lv_sql_stmt:=
28040: 'UPDATE msc_st_item_sourcing'
28041: ||' SET process_flag = '||G_ERROR_FLG||','
28042: ||' error_text = '||''''||lv_message_text||''''
28043: ||' WHERE sr_instance_code = :v_instance_code'
28044: ||' AND batch_id = :lv_batch_id'

Line 28070: 'UPDATE msc_st_item_sourcing'

28066: -- Error out record where source_type is NOT IN (1,2,3)
28067:
28068: v_sql_stmt := 08;
28069: lv_sql_stmt:=
28070: 'UPDATE msc_st_item_sourcing'
28071: ||' SET process_flag = '||G_ERROR_FLG||','
28072: ||' error_text = '||''''||lv_message_text||''''
28073: ||' WHERE sr_instance_code = :v_instance_code'
28074: ||' AND batch_id = :lv_batch_id'

Line 28101: MSC_ST_UTIL.DERIVE_PARTNER_ORG_ID(p_table_name => 'MSC_ST_ITEM_SOURCING',

28097:
28098: -- Udpate organization_id for all the records
28099:
28100: lv_return :=
28101: MSC_ST_UTIL.DERIVE_PARTNER_ORG_ID(p_table_name => 'MSC_ST_ITEM_SOURCING',
28102: p_org_partner_name =>'ORGANIZATION_CODE',
28103: p_org_partner_id =>'ORGANIZATION_ID',
28104: p_instance_code => v_instance_code,
28105: p_partner_type => G_ORGANIZATION,

Line 28121: 'UPDATE msc_st_item_sourcing'

28117:
28118: v_sql_stmt := 09;
28119:
28120: lv_sql_stmt:=
28121: 'UPDATE msc_st_item_sourcing'
28122: ||' SET sourcing_rule_name = '||''''||lv_default_sourcing_rule||''''
28123: ||' WHERE sr_instance_code = :v_instance_code'
28124: ||' AND batch_id = :lv_batch_id'
28125: ||' AND process_flag ='||G_IN_PROCESS

Line 28140: 'UPDATE msc_st_item_sourcing'

28136:
28137: v_sql_stmt := 10;
28138:
28139: lv_sql_stmt:=
28140: 'UPDATE msc_st_item_sourcing'
28141: ||' SET assignment_set_name = '||''''||lv_default_assignment_set||''''
28142: ||' WHERE sr_instance_code = :v_instance_code'
28143: ||' AND batch_id = :lv_batch_id'
28144: ||' AND process_flag ='||G_IN_PROCESS

Line 28175: (p_table_name => 'MSC_ST_ITEM_SOURCING',

28171:
28172: lv_where_str := ' AND NVL(planning_active,'||NULL_VALUE||') NOT IN (1,2)';
28173:
28174: lv_return := MSC_ST_UTIL.LOG_ERROR
28175: (p_table_name => 'MSC_ST_ITEM_SOURCING',
28176: p_instance_code => v_instance_code,
28177: p_row => lv_column_names,
28178: p_severity => G_SEV_WARNING,
28179: p_message_text => lv_message_text,

Line 28211: MSC_ST_UTIL.DERIVE_PARTNER_ORG_ID(p_table_name => 'MSC_ST_ITEM_SOURCING',

28207: -- As of now our assumption is that receipt_org_instance_code is same as
28208: -- v_instance_code ,
28209:
28210: lv_return :=
28211: MSC_ST_UTIL.DERIVE_PARTNER_ORG_ID(p_table_name => 'MSC_ST_ITEM_SOURCING',
28212: p_org_partner_name =>'RECEIPT_ORGANIZATION_CODE',
28213: p_org_partner_id =>'RECEIPT_ORGANIZATION_ID',
28214: p_instance_code => v_instance_code,
28215: p_partner_type => G_ORGANIZATION,

Line 28231: 'UPDATE msc_st_item_sourcing msis'

28227: /* -- Update sr_receipt_org_instance_id
28228: v_sql_stmt := 09;
28229:
28230: lv_sql_stmt:=
28231: 'UPDATE msc_st_item_sourcing msis'
28232: ||' SET receipt_org_instance_id = ( SELECT instance_id FROM msc_apps_instances mai'
28233: ||' WHERE mai.instance_code = msis.sr_instance_code)'
28234: ||' WHERE sr_instance_code = :v_instance_code'
28235: ||' AND batch_id = :lv_batch_id'

Line 28248: 'UPDATE msc_st_item_sourcing msis'

28244:
28245: v_sql_stmt := 11;
28246:
28247: lv_sql_stmt:=
28248: 'UPDATE msc_st_item_sourcing msis'
28249: ||' SET receipt_org_instance_code = :v_instance_code ,'
28250: ||' source_org_instance_code = :v_instance_code'
28251: ||' WHERE sr_instance_code = :v_instance_code'
28252: ||' AND batch_id = :lv_batch_id'

Line 28279: msc_st_util.derive_item_id(p_table_name => 'MSC_ST_ITEM_SOURCING',

28275:
28276: -- Derive inventory_item_id
28277:
28278: lv_return :=
28279: msc_st_util.derive_item_id(p_table_name => 'MSC_ST_ITEM_SOURCING',
28280: p_item_col_name => 'ITEM_NAME',
28281: p_item_col_id => 'INVENTORY_ITEM_ID',
28282: p_instance_code => v_instance_code,
28283: p_instance_id => v_instance_id,

Line 28300: 'UPDATE msc_st_item_sourcing'

28296: -- If assignment_type = 3 or 6 ITEM_NAME should be valid
28297:
28298: v_sql_stmt:= 12;
28299: lv_sql_stmt:=
28300: 'UPDATE msc_st_item_sourcing'
28301: ||' SET process_flag = '||G_ERROR_FLG||','
28302: ||' error_text = '||''''||lv_message_text||''''
28303: ||' WHERE (assignment_type IN (3,6) '
28304: ||' AND NVL(inventory_item_id,'||NULL_VALUE||')'

Line 28332: 'UPDATE msc_st_item_sourcing '

28328:
28329: -- If assignment_type = 2 and 5 then category_name should not be NULL
28330: v_sql_stmt := 13;
28331: lv_sql_stmt :=
28332: 'UPDATE msc_st_item_sourcing '
28333: ||' SET process_flag = '||G_ERROR_FLG||','
28334: ||' error_text = '||''''||lv_message_text||''''
28335: ||' WHERE NVL(category_name,'||''''||NULL_CHAR||''''||') '
28336: ||' = '||''''||NULL_CHAR||''''

Line 28352: 'UPDATE MSC_ST_ITEM_SOURCING msis'

28348:
28349: v_sql_stmt :=14;
28350:
28351: lv_sql_stmt :=
28352: 'UPDATE MSC_ST_ITEM_SOURCING msis'
28353: ||' SET category_set_id = ( SELECT local_id FROM msc_local_id_misc mlim'
28354: ||' WHERE entity_name = ''SR_CATEGORY_SET_ID'' '
28355: ||' AND instance_id = '||v_instance_id
28356: ||' AND mlim.char1 = msis.sr_instance_code'

Line 28376: 'UPDATE MSC_ST_ITEM_SOURCING msis'

28372:
28373: v_sql_stmt :=15;
28374:
28375: lv_sql_stmt :=
28376: 'UPDATE MSC_ST_ITEM_SOURCING msis'
28377: ||' SET category_id = ( SELECT local_id FROM msc_local_id_misc mlim'
28378: ||' WHERE entity_name = ''SR_CATEGORY_ID'' '
28379: ||' AND instance_id = '||v_instance_id
28380: ||' AND mlim.char1 = msis.sr_instance_code'

Line 28412: 'UPDATE msc_st_item_sourcing '

28408: END IF;
28409:
28410: -- Error our records if category_id/category_set_id is NULL
28411: lv_sql_stmt :=
28412: 'UPDATE msc_st_item_sourcing '
28413: ||' SET process_flag = '||G_ERROR_FLG||','
28414: ||' error_text = '||''''||lv_message_text||''''
28415: ||' WHERE (NVL(category_id,'||NULL_VALUE||') = '||NULL_VALUE
28416: ||' OR NVL(category_set_id,'||NULL_VALUE||') = '||NULL_VALUE||')'

Line 28448: 'UPDATE MSC_ST_ITEM_SOURCING msis'

28444:
28445: v_sql_stmt :=16;
28446:
28447: lv_sql_stmt :=
28448: 'UPDATE MSC_ST_ITEM_SOURCING msis'
28449: ||' SET process_flag ='||G_ERROR_FLG||','
28450: ||' error_text = '||''''||lv_message_text||''''
28451: ||' WHERE NVL(source_partner_name,'||''''||NULL_CHAR||''''||') '
28452: ||' <> '||''''||NULL_CHAR||''''

Line 28485: 'UPDATE MSC_ST_ITEM_SOURCING msis'

28481:
28482: v_sql_stmt :=17;
28483:
28484: lv_sql_stmt :=
28485: 'UPDATE MSC_ST_ITEM_SOURCING msis'
28486: ||' SET process_flag ='||G_ERROR_FLG||','
28487: ||' error_text = '||''''||lv_message_text||''''
28488: ||' WHERE NVL(source_partner_name,'||''''||NULL_CHAR||''''||') '
28489: ||' = '||''''||NULL_CHAR||''''

Line 28517: msc_st_util.derive_partner_org_id(p_table_name => 'MSC_ST_ITEM_SOURCING',

28513:
28514:
28515: -- Now derive the source_partner_id
28516: lv_return :=
28517: msc_st_util.derive_partner_org_id(p_table_name => 'MSC_ST_ITEM_SOURCING',
28518: p_org_partner_name =>'SOURCE_PARTNER_NAME',
28519: p_org_partner_id =>'SOURCE_PARTNER_ID',
28520: p_instance_code => v_instance_code,
28521: p_partner_type => G_VENDOR,

Line 28550: (p_table_name => 'MSC_ST_ITEM_SOURCING',

28546:
28547: -- Derive source_partner_site_id
28548:
28549: lv_return := MSC_ST_UTIL.DERIVE_PARTNER_SITE_ID
28550: (p_table_name => 'MSC_ST_ITEM_SOURCING',
28551: p_partner_name => 'SOURCE_PARTNER_NAME',
28552: p_partner_site_code => 'SOURCE_PARTNER_SITE_CODE',
28553: p_partner_site_id => 'SOURCE_PARTNER_SITE_ID',
28554: p_instance_code => v_instance_code,

Line 28587: 'UPDATE MSC_ST_ITEM_SOURCING msis'

28583:
28584: v_sql_stmt :=17;
28585:
28586: lv_sql_stmt :=
28587: 'UPDATE MSC_ST_ITEM_SOURCING msis'
28588: ||' SET process_flag ='||G_ERROR_FLG||','
28589: ||' error_text = '||''''||lv_message_text||''''
28590: ||' WHERE NVL(source_organization_code,'||''''||NULL_CHAR||''''||') '
28591: ||' = '||''''||NULL_CHAR||''''

Line 28620: msc_st_util.derive_partner_org_id(p_table_name => 'MSC_ST_ITEM_SOURCING',

28616:
28617: -- Derive source_organization_id
28618:
28619: lv_return :=
28620: msc_st_util.derive_partner_org_id(p_table_name => 'MSC_ST_ITEM_SOURCING',
28621: p_org_partner_name =>'SOURCE_ORGANIZATION_CODE',
28622: p_org_partner_id =>'SOURCE_ORGANIZATION_ID',
28623: p_instance_code => v_instance_code,
28624: p_partner_type => G_ORGANIZATION,

Line 28639: 'UPDATE MSC_ST_ITEM_SOURCING msis'

28635: -- Udpate SOURCING_RULE_ID
28636: v_sql_stmt := 18;
28637:
28638: lv_sql_stmt:=
28639: 'UPDATE MSC_ST_ITEM_SOURCING msis'
28640: ||' SET sourcing_rule_id = (SELECT local_id '
28641: ||' FROM msc_local_id_misc mlim'
28642: ||' WHERE mlim.entity_name= ''SOURCING_RULE_ID'' '
28643: ||' AND mlim.char1 = msis.sr_instance_code'

Line 28661: 'UPDATE MSC_ST_ITEM_SOURCING msis'

28657:
28658: -- Udpate ASSIGNMENT_SET_ID
28659: v_sql_stmt := 19;
28660: lv_sql_stmt:=
28661: 'UPDATE MSC_ST_ITEM_SOURCING msis'
28662: ||' SET assignment_set_id = (SELECT local_id'
28663: ||' FROM msc_local_id_misc mlim'
28664: ||' WHERE mlim.entity_name= ''ASSIGNMENT_SET_ID'' '
28665: ||' AND mlim.char1 = msis.sr_instance_code'

Line 28683: 'UPDATE MSC_ST_ITEM_SOURCING msis'

28679:
28680: v_sql_stmt := 20;
28681:
28682: lv_sql_stmt:=
28683: 'UPDATE MSC_ST_ITEM_SOURCING msis'
28684: ||' SET sr_receipt_id = (SELECT local_id'
28685: ||' FROM msc_local_id_misc mlim'
28686: ||' WHERE mlim.entity_name= ''SR_RECEIPT_ID'' '
28687: ||' AND mlim.char1 = msis.sr_instance_code'

Line 28713: 'UPDATE MSC_ST_ITEM_SOURCING msis'

28709: -- Udpate SR_SOURCE_ID
28710: v_sql_stmt := 21;
28711:
28712: lv_sql_stmt:=
28713: 'UPDATE MSC_ST_ITEM_SOURCING msis'
28714: ||' SET sr_source_id = (SELECT local_id'
28715: ||' FROM msc_local_id_misc mlim'
28716: ||' WHERE mlim.entity_name= ''SR_SOURCE_ID'' '
28717: ||' AND mlim.char1 = msis.sr_instance_code'

Line 28750: 'UPDATE MSC_ST_ITEM_SOURCING msis'

28746: -- Udpate assignment_id
28747: v_sql_stmt := 22;
28748:
28749: lv_sql_stmt:=
28750: 'UPDATE MSC_ST_ITEM_SOURCING msis'
28751: ||' SET assignment_id = (SELECT local_id '
28752: ||' FROM msc_local_id_misc mlim'
28753: ||' WHERE mlim.entity_name= ''ASSIGNMENT_ID'' '
28754: ||' AND mlim.instance_id = '||v_instance_id

Line 28789: ' UPDATE MSC_ST_ITEM_SOURCING msis'

28785:
28786: -- Error out records if any id derivation fails and deleted_flag = 1
28787: v_sql_stmt := 23;
28788: lv_sql_stmt:=
28789: ' UPDATE MSC_ST_ITEM_SOURCING msis'
28790: ||' SET process_flag = '||G_ERROR_FLG||','
28791: ||' error_text = '||''''||lv_message_text||''''
28792: ||' WHERE ( NVL(sourcing_rule_id,'||NULL_VALUE||') ='||NULL_VALUE
28793: ||' OR NVL(sr_receipt_id,'||NULL_VALUE||') = '||NULL_VALUE

Line 28812: pEntityName => 'MSC_ST_ITEM_SOURCING',

28808: (ERRBUF => lv_error_text,
28809: RETCODE => lv_return,
28810: pBatchID => lv_batch_id,
28811: pInstanceCode => v_instance_code,
28812: pEntityName => 'MSC_ST_ITEM_SOURCING',
28813: pInstanceID => v_instance_id);
28814:
28815: IF NVL(lv_return,0) <> 0 THEN
28816: RAISE ex_logging_err;

Line 28829: UPDATE msc_st_item_sourcing

28825: IF c3%ROWCOUNT > 0 THEN
28826:
28827: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
28828:
28829: UPDATE msc_st_item_sourcing
28830: SET sourcing_rule_id = msc_st_sourcing_rule_id_s.NEXTVAL
28831: WHERE rowid = lb_rowid(j);
28832:
28833: v_sql_stmt := 25;

Line 28881: FROM msc_st_item_sourcing

28877: v_current_date,
28878: v_current_user,
28879: v_current_date,
28880: v_current_user
28881: FROM msc_st_item_sourcing
28882: WHERE rowid = lb_rowid(j);
28883:
28884: END IF;
28885: CLOSE c3;

Line 28891: 'UPDATE MSC_ST_ITEM_SOURCING msis'

28887: -- Udpate SOURCING_RULE_ID
28888: v_sql_stmt := 26;
28889:
28890: lv_sql_stmt:=
28891: 'UPDATE MSC_ST_ITEM_SOURCING msis'
28892: ||' SET sourcing_rule_id = (SELECT local_id '
28893: ||' FROM msc_local_id_misc mlim'
28894: ||' WHERE mlim.entity_name= ''SOURCING_RULE_ID'' '
28895: ||' AND mlim.char1 = msis.sr_instance_code'

Line 28923: UPDATE msc_st_item_sourcing

28919: IF c4%ROWCOUNT > 0 THEN
28920:
28921: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
28922:
28923: UPDATE msc_st_item_sourcing
28924: SET assignment_set_id = msc_st_assignment_set_id_s.NEXTVAL
28925: WHERE rowid = lb_rowid(j);
28926:
28927: v_sql_stmt := 28;

Line 28961: FROM msc_st_item_sourcing

28957: v_current_date,
28958: v_current_user,
28959: v_current_date,
28960: v_current_user
28961: FROM msc_st_item_sourcing
28962: WHERE rowid = lb_rowid(j);
28963:
28964: END IF;
28965: CLOSE c4;

Line 28970: 'UPDATE MSC_ST_ITEM_SOURCING msis'

28966:
28967: -- Udpate ASSIGNMENT_SET_ID
28968: v_sql_stmt := 29;
28969: lv_sql_stmt:=
28970: 'UPDATE MSC_ST_ITEM_SOURCING msis'
28971: ||' SET assignment_set_id = (SELECT local_id'
28972: ||' FROM msc_local_id_misc mlim'
28973: ||' WHERE mlim.entity_name= ''ASSIGNMENT_SET_ID'' '
28974: ||' AND mlim.char1 = msis.sr_instance_code'

Line 28998: UPDATE msc_st_item_sourcing

28994: IF c5%ROWCOUNT > 0 THEN
28995:
28996: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
28997:
28998: UPDATE msc_st_item_sourcing
28999: SET sr_receipt_id = msc_st_sr_receipt_id_s.NEXTVAL
29000: WHERE rowid = lb_rowid(j);
29001:
29002: v_sql_stmt := 31;

Line 29060: FROM msc_st_item_sourcing

29056: v_current_date,
29057: v_current_user,
29058: v_current_date,
29059: v_current_user
29060: FROM msc_st_item_sourcing
29061: WHERE rowid = lb_rowid(j);
29062:
29063: END IF;
29064: CLOSE c5;

Line 29071: 'UPDATE MSC_ST_ITEM_SOURCING msis'

29067:
29068: v_sql_stmt := 32;
29069:
29070: lv_sql_stmt:=
29071: 'UPDATE MSC_ST_ITEM_SOURCING msis'
29072: ||' SET sr_receipt_id = (SELECT local_id'
29073: ||' FROM msc_local_id_misc mlim'
29074: ||' WHERE mlim.entity_name= ''SR_RECEIPT_ID'' '
29075: ||' AND mlim.char1 = msis.sr_instance_code'

Line 29108: UPDATE msc_st_item_sourcing

29104: IF c6%ROWCOUNT > 0 THEN
29105:
29106: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
29107:
29108: UPDATE msc_st_item_sourcing
29109: SET sr_source_id = msc_st_sr_source_id_s.NEXTVAL
29110: WHERE rowid = lb_rowid(j);
29111:
29112: v_sql_stmt := 34;

Line 29186: FROM msc_st_item_sourcing

29182: v_current_date,
29183: v_current_user,
29184: v_current_date,
29185: v_current_user
29186: FROM msc_st_item_sourcing
29187: WHERE rowid = lb_rowid(j);
29188:
29189: END IF;
29190: CLOSE c6;

Line 29196: 'UPDATE MSC_ST_ITEM_SOURCING msis'

29192: -- Udpate SR_SOURCE_ID
29193: v_sql_stmt := 35;
29194:
29195: lv_sql_stmt:=
29196: 'UPDATE MSC_ST_ITEM_SOURCING msis'
29197: ||' SET sr_source_id = (SELECT local_id'
29198: ||' FROM msc_local_id_misc mlim'
29199: ||' WHERE mlim.entity_name= ''SR_SOURCE_ID'' '
29200: ||' AND mlim.char1 = msis.sr_instance_code'

Line 29242: UPDATE msc_st_item_sourcing

29238: IF c7%ROWCOUNT > 0 THEN
29239:
29240: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
29241:
29242: UPDATE msc_st_item_sourcing
29243: SET assignment_id = msc_st_assignment_id_s.NEXTVAL
29244: WHERE rowid = lb_rowid(j);
29245:
29246: v_sql_stmt := 37;

Line 29297: FROM msc_st_item_sourcing

29293: v_current_date,
29294: v_current_user,
29295: v_current_date,
29296: v_current_user
29297: FROM msc_st_item_sourcing
29298: WHERE rowid = lb_rowid(j);
29299:
29300: END IF;
29301: CLOSE c7;

Line 29307: 'UPDATE MSC_ST_ITEM_SOURCING msis'

29303: -- Update assignment_id
29304: v_sql_stmt := 38;
29305:
29306: lv_sql_stmt:=
29307: 'UPDATE MSC_ST_ITEM_SOURCING msis'
29308: ||' SET assignment_id = (SELECT local_id '
29309: ||' FROM msc_local_id_misc mlim'
29310: ||' WHERE mlim.entity_name= ''ASSIGNMENT_ID'' '
29311: ||' AND mlim.instance_id = '||v_instance_id

Line 29365: ||' FROM MSC_ST_ITEM_SOURCING mis'

29361: ||' LAST_UPDATED_BY,'
29362: ||' LAST_UPDATE_DATE,'
29363: ||' CREATED_BY,'
29364: ||' CREATION_DATE '
29365: ||' FROM MSC_ST_ITEM_SOURCING mis'
29366: ||' WHERE SR_INSTANCE_CODE = :v_instance_code'
29367: ||' AND process_flag = '||G_IN_PROCESS
29368: ||' AND batch_id = :lv_batch_id'
29369: ||' AND (SOURCING_RULE_ID)' /* Added for Bug 8485612*/

Line 29410: ||' FROM MSC_ST_ITEM_SOURCING mis'

29406: ||' LAST_UPDATED_BY,'
29407: ||' LAST_UPDATE_DATE,'
29408: ||' CREATED_BY,'
29409: ||' CREATION_DATE '
29410: ||' FROM MSC_ST_ITEM_SOURCING mis'
29411: ||' WHERE SR_INSTANCE_CODE = :v_instance_code'
29412: ||' AND PROCESS_FLAG = '||G_IN_PROCESS
29413: ||' AND batch_id = :lv_batch_id'
29414: ||' AND (SR_RECEIPT_ID)'

Line 29464: ||' FROM MSC_ST_ITEM_SOURCING mis'

29460: ||' LAST_UPDATED_BY,'
29461: ||' LAST_UPDATE_DATE,'
29462: ||' CREATED_BY,'
29463: ||' CREATION_DATE '
29464: ||' FROM MSC_ST_ITEM_SOURCING mis'
29465: ||' where SR_INSTANCE_CODE = :v_instance_code'
29466: ||' AND PROCESS_FLAG = '||G_IN_PROCESS
29467: ||' AND batch_id = :lv_batch_id'
29468: ||' AND (SR_SOURCE_ID)'

Line 29504: ||' FROM MSC_ST_ITEM_SOURCING mis'

29500: ||' LAST_UPDATED_BY,'
29501: ||' LAST_UPDATE_DATE,'
29502: ||' CREATED_BY,'
29503: ||' CREATION_DATE '
29504: ||' FROM MSC_ST_ITEM_SOURCING mis'
29505: ||' where SR_INSTANCE_CODE = :v_instance_code'
29506: ||' AND PROCESS_FLAG = '||G_IN_PROCESS
29507: ||' AND batch_id = :lv_batch_id'
29508: ||' AND (ASSIGNMENT_SET_ID)'

Line 29557: ||' from MSC_ST_ITEM_SOURCING mis'

29553: ||' LAST_UPDATED_BY,'
29554: ||' LAST_UPDATE_DATE,'
29555: ||' CREATED_BY,'
29556: ||' CREATION_DATE '
29557: ||' from MSC_ST_ITEM_SOURCING mis'
29558: ||' where SR_INSTANCE_CODE = :v_instance_code'
29559: ||' AND PROCESS_FLAG = '||G_IN_PROCESS
29560: ||' AND batch_id = :lv_batch_id'
29561: ||' AND (ASSIGNMENT_ID)'

Line 29575: MSC_ST_UTIL.SET_PROCESS_FLAG(p_table_name => 'MSC_ST_ITEM_SOURCING',

29571: EXECUTE IMMEDIATE lv_sql_stmt USING v_instance_code,lv_batch_id;
29572:
29573: -- Set the processf flag as Valid and poulate instance_id
29574: lv_return :=
29575: MSC_ST_UTIL.SET_PROCESS_FLAG(p_table_name => 'MSC_ST_ITEM_SOURCING',
29576: p_instance_id => v_instance_id,
29577: p_instance_code => v_instance_code,
29578: p_process_flag => G_VALID,
29579: p_error_text => lv_error_text,

Line 29590: msc_st_util.log_error(p_table_name => 'MSC_ST_ITEM_SOURCING',

29586:
29587: -- At the end calling the LOG_ERROR for logging all errored out records
29588:
29589: lv_return :=
29590: msc_st_util.log_error(p_table_name => 'MSC_ST_ITEM_SOURCING',
29591: p_instance_code => v_instance_code,
29592: p_row => lv_column_names,
29593: p_severity => G_SEV_ERROR,
29594: p_error_text => lv_error_text,

Line 55802: lv_count:= SET_IN_PROCESS (p_table_name => 'MSC_ST_ITEM_SOURCING');

55798: End IF;
55799: END IF;
55800:
55801: IF v_sourcing_enabled = SYS_YES THEN
55802: lv_count:= SET_IN_PROCESS (p_table_name => 'MSC_ST_ITEM_SOURCING');
55803: IF lv_count > 0 Then
55804: prec.sourcing_rule_flag:= SYS_YES;
55805: End IF;
55806: lv_count:= SET_IN_PROCESS (p_table_name => 'MSC_ST_REGIONS');