[Home] [Help]
1449: application_short_name => 'MSC',
1450: statement_type => AD_DDL.CREATE_INDEX,
1451: statement =>
1452: 'create index MSC_ST_ITEM_SUP_N1_'||v_instance_code
1453: ||' on MSC_ST_ITEM_SUPPLIERS '
1454: ||'(sr_instance_code, item_name, vendor_name, organization_code, company_name, vendor_site_code) '
1455: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
1456: object_name =>'MSC_ST_ITEM_SUP_N1_'||v_instance_code);
1457:
1498: WHEN OTHERS THEN
1499: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_WARNING,'Error creating Index MSC_ST_SUP_FENCES_N1_'||v_instance_code||'. Error:'||substr(SQLERRM,1,240));
1500: END;
1501:
1502: msc_analyse_tables_pk.analyse_table( 'MSC_ST_ITEM_SUPPLIERS', v_instance_id, -1);
1503: msc_analyse_tables_pk.analyse_table( 'MSC_ST_SUPPLIER_CAPACITIES', v_instance_id, -1);
1504: msc_analyse_tables_pk.analyse_table( 'MSC_ST_SUPPLIER_FLEX_FENCES', v_instance_id, -1);
1505:
1506: END IF;
24934: END LOAD_ITEM_SUBSTITUTES;
24935:
24936:
24937: /*==========================================================================+
24938: | DESCRIPTION : This procedure validates the data in MSC_ST_ITEM_SUPPLIERS,|
24939: | MSC_ST_SUPPLIER_CAPACITIES and MSC_ST_SUPPLIER_FLEX_FENCES |
24940: | table. |
24941: +==========================================================================*/
24942: PROCEDURE LOAD_SUPP_CAPACITY IS
24946: lv_column_names VARCHAR2(5000);--stores concatenated column names
24947: lv_where_str VARCHAR2(5000);
24948: lv_sql_stmt VARCHAR2(5000);
24949: lv_return NUMBER;
24950: lv_batch_id msc_st_item_suppliers.batch_id%TYPE;
24951: lv_message_text msc_errors.error_text%TYPE;
24952:
24953: ex_logging_err EXCEPTION;
24954:
24954:
24955:
24956: CURSOR c1(p_batch_id NUMBER) IS
24957: SELECT rowid
24958: FROM msc_st_item_suppliers
24959: WHERE process_flag IN (G_IN_PROCESS,G_ERROR_FLG)
24960: AND sr_instance_code = v_instance_code
24961: AND batch_id = p_batch_id;
24962:
24981:
24982: v_sql_stmt := 00;
24983:
24984: lv_sql_stmt :=
24985: 'UPDATE msc_st_item_suppliers '
24986: ||' SET using_organization_code = organization_code'
24987: ||' WHERE asl_level = 2'
24988: ||' AND process_flag = '||G_IN_PROCESS
24989: ||' AND sr_instance_code = :v_instance_code';
24994: EXECUTE IMMEDIATE lv_sql_stmt
24995: USING v_instance_code;
24996:
24997:
24998: --Validation check fot the table MSC_ST_ITEM_SUPPLIERS.
24999: --Duplicate records check for the records whose source is XML
25000:
25001: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
25002: (p_app_short_name => 'MSC',
25009: END IF;
25010:
25011: v_sql_stmt := 01;
25012: lv_sql_stmt :=
25013: 'UPDATE msc_st_item_suppliers mis1 '
25014: ||' SET process_flag = '||G_ERROR_FLG||','
25015: ||' error_text = '||''''||lv_message_text||''''
25016: ||' WHERE message_id < (SELECT MAX(message_id) '
25017: ||' FROM msc_st_item_suppliers mis2 '
25013: 'UPDATE msc_st_item_suppliers mis1 '
25014: ||' SET process_flag = '||G_ERROR_FLG||','
25015: ||' error_text = '||''''||lv_message_text||''''
25016: ||' WHERE message_id < (SELECT MAX(message_id) '
25017: ||' FROM msc_st_item_suppliers mis2 '
25018: ||' WHERE mis2.sr_instance_code = mis1.sr_instance_code'
25019: ||' AND mis2.vendor_name = mis1.vendor_name'
25020: ||' AND mis2.organization_code = mis1.organization_code'
25021: ||' AND mis2.vendor_site_code = mis1.vendor_site_code'
25052: END IF;
25053:
25054: v_sql_stmt := 02;
25055: lv_sql_stmt :=
25056: 'UPDATE msc_st_item_suppliers mis1 '
25057: ||' SET process_flag = '||G_ERROR_FLG||','
25058: ||' error_text = '||''''||lv_message_text||''''
25059: ||' WHERE EXISTS( SELECT 1 '
25060: ||' FROM msc_st_item_suppliers mis2'
25056: 'UPDATE msc_st_item_suppliers mis1 '
25057: ||' SET process_flag = '||G_ERROR_FLG||','
25058: ||' error_text = '||''''||lv_message_text||''''
25059: ||' WHERE EXISTS( SELECT 1 '
25060: ||' FROM msc_st_item_suppliers mis2'
25061: ||' WHERE mis2.sr_instance_code = mis1.sr_instance_code'
25062: ||' AND mis2.vendor_name = mis1.vendor_name'
25063: ||' AND mis2.organization_code = mis1.organization_code'
25064: ||' AND mis2.vendor_site_code = mis1.vendor_site_code'
25107: FROM dual;
25108:
25109: v_sql_stmt := 04;
25110: lv_sql_stmt :=
25111: 'UPDATE msc_st_item_suppliers '
25112: ||' SET batch_id = :lv_batch_id'
25113: ||' WHERE process_flag IN ('||G_IN_PROCESS||','||G_ERROR_FLG||')'
25114: ||' AND sr_instance_code = :v_instance_code'
25115: ||' AND NVL(batch_id,'||NULL_VALUE||') = '||NULL_VALUE
25129: CLOSE c1;
25130:
25131: v_sql_stmt := 05;
25132: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
25133: UPDATE msc_st_item_suppliers
25134: SET st_transaction_id = msc_st_item_suppliers_s.NEXTVAL,
25135: last_update_date = v_current_date,
25136: last_updated_by = v_current_user,
25137: creation_date = v_current_date,
25130:
25131: v_sql_stmt := 05;
25132: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
25133: UPDATE msc_st_item_suppliers
25134: SET st_transaction_id = msc_st_item_suppliers_s.NEXTVAL,
25135: last_update_date = v_current_date,
25136: last_updated_by = v_current_user,
25137: creation_date = v_current_date,
25138: created_by = v_current_user
25145: p_error_code => 'MSC_PP_NO_DELETION',
25146: p_message_text => lv_message_text,
25147: p_error_text => lv_error_text,
25148: p_token1 => 'TABLE_NAME',
25149: p_token_value1 => 'MSC_ST_ITEM_SUPPLIERS');
25150:
25151: IF lv_return <> 0 THEN
25152: RAISE ex_logging_err;
25153: END IF;
25153: END IF;
25154:
25155: v_sql_stmt := 06;
25156: lv_sql_stmt :=
25157: 'UPDATE msc_st_item_suppliers '
25158: ||' SET process_flag = '||G_ERROR_FLG||','
25159: ||' error_text = '||''''||lv_message_text||''''
25160: ||' WHERE deleted_flag = '||SYS_YES
25161: ||' AND process_flag = '||G_IN_PROCESS
25183:
25184: v_sql_stmt := 06;
25185:
25186: lv_sql_stmt :=
25187: 'UPDATE msc_st_item_suppliers '
25188: ||' SET process_flag = '||G_ERROR_FLG||','
25189: ||' error_text = '||''''||lv_message_text||''''
25190: ||' WHERE asl_level not in(1,2)'
25191: ||' AND process_flag = '||G_IN_PROCESS
25210: ||' PURCHASING_UNIT_OF_MEASURE',
25211: p_token2 => 'MASTER_TABLE',
25212: p_token_value2 => 'MSC_ST_UNITS_OF_MEASURE',
25213: p_token3 => 'CHILD_TABLE',
25214: p_token_value3 => 'MSC_ST_ITEM_SUPPLIERS');
25215:
25216: IF lv_return <> 0 THEN
25217: RAISE ex_logging_err;
25218: END IF;
25219:
25220: -- Validate all UOM code.
25221: v_sql_stmt := 07;
25222: lv_sql_stmt :=
25223: 'UPDATE msc_st_item_suppliers mstp'
25224: ||' SET process_flag = '||G_ERROR_FLG||','
25225: ||' error_text = '||''''||lv_message_text||''''
25226: ||' WHERE NOT EXISTS(SELECT 1 '
25227: ||' FROM msc_units_of_measure muom '
25268: --Log a warning for those records where the deleted_flag has a value other
25269: --than SYS_NO
25270:
25271: lv_return := MSC_ST_UTIL.LOG_ERROR
25272: (p_table_name => 'MSC_ST_ITEM_SUPPLIERS',
25273: p_instance_code => v_instance_code,
25274: p_row => lv_column_names,
25275: p_severity => G_SEV_WARNING,
25276: p_message_text => lv_message_text,
25298: END IF;
25299:
25300: --Derive Organization_id
25301: lv_return := MSC_ST_UTIL.DERIVE_PARTNER_ORG_ID
25302: (p_table_name => 'MSC_ST_ITEM_SUPPLIERS',
25303: p_org_partner_name => 'ORGANIZATION_CODE',
25304: p_org_partner_id => 'ORGANIZATION_ID',
25305: p_instance_code => v_instance_code,
25306: p_partner_type => G_ORGANIZATION,
25324: p_token_value1 => 'ITEM_NAME');
25325:
25326: --Derive Inventory_item_id
25327: lv_return := MSC_ST_UTIL.DERIVE_ITEM_ID
25328: (p_table_name => 'MSC_ST_ITEM_SUPPLIERS',
25329: p_item_col_name => 'ITEM_NAME',
25330: p_item_col_id => 'INVENTORY_ITEM_ID',
25331: p_instance_id => v_instance_id,
25332: p_instance_code => v_instance_code,
25354: RAISE ex_logging_err;
25355: END IF;
25356:
25357: lv_return := MSC_ST_UTIL.DERIVE_PARTNER_ORG_ID
25358: (p_table_name => 'MSC_ST_ITEM_SUPPLIERS',
25359: p_org_partner_name => 'VENDOR_NAME',
25360: p_org_partner_id => 'SUPPLIER_ID',
25361: p_instance_code => v_instance_code,
25362: p_partner_type => G_VENDOR,
25384: END IF;
25385:
25386: --Derive Supplier_site_id
25387: lv_return := MSC_ST_UTIL.DERIVE_PARTNER_SITE_ID
25388: (p_table_name => 'MSC_ST_ITEM_SUPPLIERS',
25389: p_partner_name => 'VENDOR_NAME',
25390: p_partner_site_code => 'VENDOR_SITE_CODE',
25391: p_partner_site_id => 'SUPPLIER_SITE_ID',
25392: p_instance_code => v_instance_code,
25404: /* Bug 2172537
25405: -- Update all calendar_code as instance_code concatenated with name
25406: v_sql_stmt := 12;
25407: lv_sql_stmt :=
25408: 'UPDATE msc_st_item_suppliers '
25409: ||' SET delivery_calendar_code = sr_instance_code'||'||'':''||'||'delivery_calendar_code'
25410: ||' WHERE delivery_calendar_code IS NOT NULL'
25411: ||' AND sr_instance_code = :v_instance_code'
25412: ||' AND batch_id = :lv_batch_id'
25428: p_token_value1 => 'SR_INSTANCE_ID AND CALENDAR_CODE',
25429: p_token2 => 'MASTER_TABLE',
25430: p_token_value2 => 'MSC_CALENDARS',
25431: p_token3 => 'CHILD_TABLE',
25432: p_token_value3 => 'MSC_ST_ITEM_SUPPLIERS');
25433: IF lv_return <> 0 THEN
25434: RAISE ex_logging_err;
25435: END IF;
25436:
25436:
25437: -- Validate the calendar code for org
25438: v_sql_stmt := 14;
25439: lv_sql_stmt :=
25440: 'UPDATE msc_st_item_suppliers mis'
25441: ||' SET process_flag = '||G_ERROR_FLG||','
25442: ||' error_text = '||''''||lv_message_text||''''
25443: ||' WHERE NOT EXISTS ( SELECT 1 '
25444: ||' FROM msc_calendars mc '
25465:
25466: v_sql_stmt := 08;
25467:
25468: lv_sql_stmt :=
25469: 'UPDATE msc_st_item_suppliers mis '
25470: ||' SET using_organization_id = decode(asl_level,1,'||G_USING_ORG_ID||',organization_id),'
25471: ||' sr_instance_id2 = :v_instance_id'
25472: ||' WHERE sr_instance_code = :v_instance_code'
25473: ||' AND batch_id = :lv_batch_id'
25502: lv_where_str :=
25503: ' AND NVL(REPLENISHMENT_METHOD,'||NULL_VALUE||') NOT IN(1,2,3,4)';
25504:
25505: lv_return := MSC_ST_UTIL.LOG_ERROR
25506: (p_table_name => 'MSC_ST_ITEM_SUPPLIERS',
25507: p_instance_code => v_instance_code,
25508: p_row => lv_column_names,
25509: p_severity => G_SEV_WARNING,
25510: p_message_text => lv_message_text,
25526: (ERRBUF => lv_error_text,
25527: RETCODE => lv_return,
25528: pBatchID => lv_batch_id,
25529: pInstanceCode => v_instance_code,
25530: pEntityName => 'MSC_ST_ITEM_SUPPLIERS',
25531: pInstanceID => v_instance_id);
25532:
25533: IF NVL(lv_return,0) <> 0 THEN
25534: RAISE ex_logging_err;
25553: RAISE ex_logging_err;
25554: END IF;
25555:
25556: lv_return := MSC_ST_UTIL.DERIVE_COMPANY_ID
25557: (p_table_name => 'MSC_ST_ITEM_SUPPLIERS',
25558: p_company_name => 'COMPANY_NAME',
25559: p_company_id => 'COMPANY_ID',
25560: p_instance_code => v_instance_code,
25561: p_error_text => lv_error_text,
25568: END IF;
25569:
25570: /*
25571: lv_sql_stmt :=
25572: ' UPDATE MSC_ST_ITEM_SUPPLIERS mic '
25573: ||' SET company_id = '
25574: ||' (select company_id from msc_companies mc'
25575: ||' where mic.company_name = mc.company_name) '
25576: ||' WHERE NVL(COMPANY_NAME,''-1'') <> ''-1'''
25585: USING lv_batch_id,
25586: v_instance_code;
25587:
25588: lv_sql_stmt :=
25589: ' UPDATE MSC_ST_ITEM_SUPPLIERS '
25590: ||' SET process_flag = '||G_ERROR_FLG||','
25591: ||' error_text = '||''''||lv_message_text||''''
25592: ||' WHERE NVL(COMPANY_NAME,''-1'') <> ''-1'''
25593: ||' AND COMPANY_ID IS NULL '
25607:
25608: -- SCE Changes end here
25609:
25610: lv_return := MSC_ST_UTIL.SET_PROCESS_FLAG
25611: (p_table_name => 'MSC_ST_ITEM_SUPPLIERS',
25612: p_instance_id => v_instance_id,
25613: p_instance_code => v_instance_code,
25614: p_process_flag => G_VALID,
25615: p_error_text => lv_error_text,
25620: RAISE ex_logging_err;
25621: END IF;
25622:
25623: lv_return := MSC_ST_UTIL.LOG_ERROR
25624: (p_table_name => 'MSC_ST_ITEM_SUPPLIERS',
25625: p_instance_code => v_instance_code,
25626: p_row => lv_column_names,
25627: p_severity => G_SEV_ERROR,
25628: p_message_text => NULL,
25937: p_token1 => 'COLUMN_NAMES',
25938: p_token_value1 => 'SR_INSTANCE_CODE, COMPANY_NAME, ORGANIZATION_CODE,'
25939: ||' ITEM_NAME, VENDOR_NAME AND VENDOR_SITE_CODE',
25940: p_token2 => 'MASTER_TABLE',
25941: p_token_value2 => 'MSC_ST_ITEM_SUPPLIERS',
25942: p_token3 => 'CHILD_TABLE',
25943: p_token_value3 => 'MSC_ST_SUPPLIER_CAPACITIES',
25944: p_token4 => 'VALUE',
25945: p_token_value4 => 'ASL_LEVEL = 1');
25971: ||' AND mis2.using_organization_id = -1'
25972: ||' AND mis2.plan_id = -1'
25973: ||' UNION'
25974: ||' SELECT 1'
25975: ||' FROM msc_st_item_suppliers mis3'
25976: ||' WHERE mis3.vendor_name = mis1.vendor_name'
25977: ||' AND mis3.vendor_site_code = mis1.vendor_site_code'
25978: ||' AND mis3.item_name = mis1.item_name'
25979: ||' AND mis3.sr_instance_code = mis1.sr_instance_code'
26420: p_token1 => 'COLUMN_NAMES',
26421: p_token_value1 => 'SR_INSTANCE_CODE, COMPANY_NAME, ORGANIZATION_CODE,'
26422: ||' ITEM_NAME, VENDOR_NAME AND VENDOR_SITE_CODE',
26423: p_token2 => 'MASTER_TABLE',
26424: p_token_value2 => 'MSC_ST_ITEM_SUPPLIERS',
26425: p_token3 => 'CHILD_TABLE',
26426: p_token_value3 => 'MSC_ST_SUPPLIER_FLEX_FENCES ',
26427: p_token4 => 'VALUE',
26428: p_token_value4 => 'ASL_LEVEL = 1');
26454: ||' AND msf2.using_organization_id = -1'
26455: ||' AND msf2.plan_id = -1'
26456: ||' UNION'
26457: ||' SELECT 1'
26458: ||' FROM msc_st_item_suppliers msf3'
26459: ||' WHERE msf3.vendor_name = msf1.vendor_name'
26460: ||' AND msf3.vendor_site_code = msf1.vendor_site_code'
26461: ||' AND msf3.item_name = msf1.item_name'
26462: ||' AND msf3.sr_instance_code = msf1.sr_instance_code'
55102:
55103:
55104: -- Error out those records where the deleted_flag is set to 'Y' if the collection program doesn't support deletion for this BO.
55105: -- Insert the records into the error_table with the severity warning where deleted_flag has a null value.
55106: -- Update the MSC_ST_ITEM_SUPPLIERS table with the default value 2.
55107:
55108: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
55109: (p_app_short_name => 'MSC',
55110: p_error_code => 'MSC_PP_NO_DELETION',
55948: End IF;
55949: END IF;
55950:
55951: IF v_sup_cap_enabled = SYS_YES THEN
55952: lv_count:= SET_IN_PROCESS (p_table_name => 'MSC_ST_ITEM_SUPPLIERS');
55953: IF lv_count > 0 Then
55954: prec.app_supp_cap_flag:= SYS_YES;
55955: End IF;
55956: lv_count:= SET_IN_PROCESS (p_table_name => 'MSC_ST_SUPPLIER_CAPACITIES', p_erp_enabled => 'Y');