[Home] [Help]
1441: application_short_name => 'MSC',
1442: statement_type => AD_DDL.CREATE_INDEX,
1443: statement =>
1444: 'create index MSC_ST_ITEM_SUP_N1_'||v_instance_code
1445: ||' on MSC_ST_ITEM_SUPPLIERS '
1446: ||'(sr_instance_code, item_name, vendor_name, organization_code, company_name, vendor_site_code) '
1447: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
1448: object_name =>'MSC_ST_ITEM_SUP_N1_'||v_instance_code);
1449:
1490: WHEN OTHERS THEN
1491: 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));
1492: END;
1493:
1494: msc_analyse_tables_pk.analyse_table( 'MSC_ST_ITEM_SUPPLIERS', v_instance_id, -1);
1495: msc_analyse_tables_pk.analyse_table( 'MSC_ST_SUPPLIER_CAPACITIES', v_instance_id, -1);
1496: msc_analyse_tables_pk.analyse_table( 'MSC_ST_SUPPLIER_FLEX_FENCES', v_instance_id, -1);
1497:
1498: END IF;
21445: END LOAD_ITEM_SUBSTITUTES;
21446:
21447:
21448: /*==========================================================================+
21449: | DESCRIPTION : This procedure validates the data in MSC_ST_ITEM_SUPPLIERS,|
21450: | MSC_ST_SUPPLIER_CAPACITIES and MSC_ST_SUPPLIER_FLEX_FENCES |
21451: | table. |
21452: +==========================================================================*/
21453: PROCEDURE LOAD_SUPP_CAPACITY IS
21457: lv_column_names VARCHAR2(5000);--stores concatenated column names
21458: lv_where_str VARCHAR2(5000);
21459: lv_sql_stmt VARCHAR2(5000);
21460: lv_return NUMBER;
21461: lv_batch_id msc_st_item_suppliers.batch_id%TYPE;
21462: lv_message_text msc_errors.error_text%TYPE;
21463:
21464: ex_logging_err EXCEPTION;
21465:
21465:
21466:
21467: CURSOR c1(p_batch_id NUMBER) IS
21468: SELECT rowid
21469: FROM msc_st_item_suppliers
21470: WHERE process_flag IN (G_IN_PROCESS,G_ERROR_FLG)
21471: AND sr_instance_code = v_instance_code
21472: AND batch_id = p_batch_id;
21473:
21492:
21493: v_sql_stmt := 00;
21494:
21495: lv_sql_stmt :=
21496: 'UPDATE msc_st_item_suppliers '
21497: ||' SET using_organization_code = organization_code'
21498: ||' WHERE asl_level = 2'
21499: ||' AND process_flag = '||G_IN_PROCESS
21500: ||' AND sr_instance_code = :v_instance_code';
21505: EXECUTE IMMEDIATE lv_sql_stmt
21506: USING v_instance_code;
21507:
21508:
21509: --Validation check fot the table MSC_ST_ITEM_SUPPLIERS.
21510: --Duplicate records check for the records whose source is XML
21511:
21512: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
21513: (p_app_short_name => 'MSC',
21520: END IF;
21521:
21522: v_sql_stmt := 01;
21523: lv_sql_stmt :=
21524: 'UPDATE msc_st_item_suppliers mis1 '
21525: ||' SET process_flag = '||G_ERROR_FLG||','
21526: ||' error_text = '||''''||lv_message_text||''''
21527: ||' WHERE message_id < (SELECT MAX(message_id) '
21528: ||' FROM msc_st_item_suppliers mis2 '
21524: 'UPDATE msc_st_item_suppliers mis1 '
21525: ||' SET process_flag = '||G_ERROR_FLG||','
21526: ||' error_text = '||''''||lv_message_text||''''
21527: ||' WHERE message_id < (SELECT MAX(message_id) '
21528: ||' FROM msc_st_item_suppliers mis2 '
21529: ||' WHERE mis2.sr_instance_code = mis1.sr_instance_code'
21530: ||' AND mis2.vendor_name = mis1.vendor_name'
21531: ||' AND mis2.organization_code = mis1.organization_code'
21532: ||' AND mis2.vendor_site_code = mis1.vendor_site_code'
21563: END IF;
21564:
21565: v_sql_stmt := 02;
21566: lv_sql_stmt :=
21567: 'UPDATE msc_st_item_suppliers mis1 '
21568: ||' SET process_flag = '||G_ERROR_FLG||','
21569: ||' error_text = '||''''||lv_message_text||''''
21570: ||' WHERE EXISTS( SELECT 1 '
21571: ||' FROM msc_st_item_suppliers mis2'
21567: 'UPDATE msc_st_item_suppliers mis1 '
21568: ||' SET process_flag = '||G_ERROR_FLG||','
21569: ||' error_text = '||''''||lv_message_text||''''
21570: ||' WHERE EXISTS( SELECT 1 '
21571: ||' FROM msc_st_item_suppliers mis2'
21572: ||' WHERE mis2.sr_instance_code = mis1.sr_instance_code'
21573: ||' AND mis2.vendor_name = mis1.vendor_name'
21574: ||' AND mis2.organization_code = mis1.organization_code'
21575: ||' AND mis2.vendor_site_code = mis1.vendor_site_code'
21618: FROM dual;
21619:
21620: v_sql_stmt := 04;
21621: lv_sql_stmt :=
21622: 'UPDATE msc_st_item_suppliers '
21623: ||' SET batch_id = :lv_batch_id'
21624: ||' WHERE process_flag IN ('||G_IN_PROCESS||','||G_ERROR_FLG||')'
21625: ||' AND sr_instance_code = :v_instance_code'
21626: ||' AND NVL(batch_id,'||NULL_VALUE||') = '||NULL_VALUE
21640: CLOSE c1;
21641:
21642: v_sql_stmt := 05;
21643: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
21644: UPDATE msc_st_item_suppliers
21645: SET st_transaction_id = msc_st_item_suppliers_s.NEXTVAL,
21646: last_update_date = v_current_date,
21647: last_updated_by = v_current_user,
21648: creation_date = v_current_date,
21641:
21642: v_sql_stmt := 05;
21643: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
21644: UPDATE msc_st_item_suppliers
21645: SET st_transaction_id = msc_st_item_suppliers_s.NEXTVAL,
21646: last_update_date = v_current_date,
21647: last_updated_by = v_current_user,
21648: creation_date = v_current_date,
21649: created_by = v_current_user
21656: p_error_code => 'MSC_PP_NO_DELETION',
21657: p_message_text => lv_message_text,
21658: p_error_text => lv_error_text,
21659: p_token1 => 'TABLE_NAME',
21660: p_token_value1 => 'MSC_ST_ITEM_SUPPLIERS');
21661:
21662: IF lv_return <> 0 THEN
21663: RAISE ex_logging_err;
21664: END IF;
21664: END IF;
21665:
21666: v_sql_stmt := 06;
21667: lv_sql_stmt :=
21668: 'UPDATE msc_st_item_suppliers '
21669: ||' SET process_flag = '||G_ERROR_FLG||','
21670: ||' error_text = '||''''||lv_message_text||''''
21671: ||' WHERE deleted_flag = '||SYS_YES
21672: ||' AND process_flag = '||G_IN_PROCESS
21694:
21695: v_sql_stmt := 06;
21696:
21697: lv_sql_stmt :=
21698: 'UPDATE msc_st_item_suppliers '
21699: ||' SET process_flag = '||G_ERROR_FLG||','
21700: ||' error_text = '||''''||lv_message_text||''''
21701: ||' WHERE asl_level not in(1,2)'
21702: ||' AND process_flag = '||G_IN_PROCESS
21721: ||' PURCHASING_UNIT_OF_MEASURE',
21722: p_token2 => 'MASTER_TABLE',
21723: p_token_value2 => 'MSC_ST_UNITS_OF_MEASURE',
21724: p_token3 => 'CHILD_TABLE',
21725: p_token_value3 => 'MSC_ST_ITEM_SUPPLIERS');
21726:
21727: IF lv_return <> 0 THEN
21728: RAISE ex_logging_err;
21729: END IF;
21730:
21731: -- Validate all UOM code.
21732: v_sql_stmt := 07;
21733: lv_sql_stmt :=
21734: 'UPDATE msc_st_item_suppliers mstp'
21735: ||' SET process_flag = '||G_ERROR_FLG||','
21736: ||' error_text = '||''''||lv_message_text||''''
21737: ||' WHERE NOT EXISTS(SELECT 1 '
21738: ||' FROM msc_units_of_measure muom '
21779: --Log a warning for those records where the deleted_flag has a value other
21780: --than SYS_NO
21781:
21782: lv_return := MSC_ST_UTIL.LOG_ERROR
21783: (p_table_name => 'MSC_ST_ITEM_SUPPLIERS',
21784: p_instance_code => v_instance_code,
21785: p_row => lv_column_names,
21786: p_severity => G_SEV_WARNING,
21787: p_message_text => lv_message_text,
21809: END IF;
21810:
21811: --Derive Organization_id
21812: lv_return := MSC_ST_UTIL.DERIVE_PARTNER_ORG_ID
21813: (p_table_name => 'MSC_ST_ITEM_SUPPLIERS',
21814: p_org_partner_name => 'ORGANIZATION_CODE',
21815: p_org_partner_id => 'ORGANIZATION_ID',
21816: p_instance_code => v_instance_code,
21817: p_partner_type => G_ORGANIZATION,
21835: p_token_value1 => 'ITEM_NAME');
21836:
21837: --Derive Inventory_item_id
21838: lv_return := MSC_ST_UTIL.DERIVE_ITEM_ID
21839: (p_table_name => 'MSC_ST_ITEM_SUPPLIERS',
21840: p_item_col_name => 'ITEM_NAME',
21841: p_item_col_id => 'INVENTORY_ITEM_ID',
21842: p_instance_id => v_instance_id,
21843: p_instance_code => v_instance_code,
21865: RAISE ex_logging_err;
21866: END IF;
21867:
21868: lv_return := MSC_ST_UTIL.DERIVE_PARTNER_ORG_ID
21869: (p_table_name => 'MSC_ST_ITEM_SUPPLIERS',
21870: p_org_partner_name => 'VENDOR_NAME',
21871: p_org_partner_id => 'SUPPLIER_ID',
21872: p_instance_code => v_instance_code,
21873: p_partner_type => G_VENDOR,
21895: END IF;
21896:
21897: --Derive Supplier_site_id
21898: lv_return := MSC_ST_UTIL.DERIVE_PARTNER_SITE_ID
21899: (p_table_name => 'MSC_ST_ITEM_SUPPLIERS',
21900: p_partner_name => 'VENDOR_NAME',
21901: p_partner_site_code => 'VENDOR_SITE_CODE',
21902: p_partner_site_id => 'SUPPLIER_SITE_ID',
21903: p_instance_code => v_instance_code,
21915: /* Bug 2172537
21916: -- Update all calendar_code as instance_code concatenated with name
21917: v_sql_stmt := 12;
21918: lv_sql_stmt :=
21919: 'UPDATE msc_st_item_suppliers '
21920: ||' SET delivery_calendar_code = sr_instance_code'||'||'':''||'||'delivery_calendar_code'
21921: ||' WHERE delivery_calendar_code IS NOT NULL'
21922: ||' AND sr_instance_code = :v_instance_code'
21923: ||' AND batch_id = :lv_batch_id'
21939: p_token_value1 => 'SR_INSTANCE_ID AND CALENDAR_CODE',
21940: p_token2 => 'MASTER_TABLE',
21941: p_token_value2 => 'MSC_CALENDARS',
21942: p_token3 => 'CHILD_TABLE',
21943: p_token_value3 => 'MSC_ST_ITEM_SUPPLIERS');
21944: IF lv_return <> 0 THEN
21945: RAISE ex_logging_err;
21946: END IF;
21947:
21947:
21948: -- Validate the calendar code for org
21949: v_sql_stmt := 14;
21950: lv_sql_stmt :=
21951: 'UPDATE msc_st_item_suppliers mis'
21952: ||' SET process_flag = '||G_ERROR_FLG||','
21953: ||' error_text = '||''''||lv_message_text||''''
21954: ||' WHERE NOT EXISTS ( SELECT 1 '
21955: ||' FROM msc_calendars mc '
21976:
21977: v_sql_stmt := 08;
21978:
21979: lv_sql_stmt :=
21980: 'UPDATE msc_st_item_suppliers mis '
21981: ||' SET using_organization_id = decode(asl_level,1,'||G_USING_ORG_ID||',organization_id),'
21982: ||' sr_instance_id2 = :v_instance_id'
21983: ||' WHERE sr_instance_code = :v_instance_code'
21984: ||' AND batch_id = :lv_batch_id'
22013: lv_where_str :=
22014: ' AND NVL(REPLENISHMENT_METHOD,'||NULL_VALUE||') NOT IN(1,2,3,4)';
22015:
22016: lv_return := MSC_ST_UTIL.LOG_ERROR
22017: (p_table_name => 'MSC_ST_ITEM_SUPPLIERS',
22018: p_instance_code => v_instance_code,
22019: p_row => lv_column_names,
22020: p_severity => G_SEV_WARNING,
22021: p_message_text => lv_message_text,
22037: (ERRBUF => lv_error_text,
22038: RETCODE => lv_return,
22039: pBatchID => lv_batch_id,
22040: pInstanceCode => v_instance_code,
22041: pEntityName => 'MSC_ST_ITEM_SUPPLIERS',
22042: pInstanceID => v_instance_id);
22043:
22044: IF NVL(lv_return,0) <> 0 THEN
22045: RAISE ex_logging_err;
22064: RAISE ex_logging_err;
22065: END IF;
22066:
22067: lv_return := MSC_ST_UTIL.DERIVE_COMPANY_ID
22068: (p_table_name => 'MSC_ST_ITEM_SUPPLIERS',
22069: p_company_name => 'COMPANY_NAME',
22070: p_company_id => 'COMPANY_ID',
22071: p_instance_code => v_instance_code,
22072: p_error_text => lv_error_text,
22079: END IF;
22080:
22081: /*
22082: lv_sql_stmt :=
22083: ' UPDATE MSC_ST_ITEM_SUPPLIERS mic '
22084: ||' SET company_id = '
22085: ||' (select company_id from msc_companies mc'
22086: ||' where mic.company_name = mc.company_name) '
22087: ||' WHERE NVL(COMPANY_NAME,''-1'') <> ''-1'''
22096: USING lv_batch_id,
22097: v_instance_code;
22098:
22099: lv_sql_stmt :=
22100: ' UPDATE MSC_ST_ITEM_SUPPLIERS '
22101: ||' SET process_flag = '||G_ERROR_FLG||','
22102: ||' error_text = '||''''||lv_message_text||''''
22103: ||' WHERE NVL(COMPANY_NAME,''-1'') <> ''-1'''
22104: ||' AND COMPANY_ID IS NULL '
22118:
22119: -- SCE Changes end here
22120:
22121: lv_return := MSC_ST_UTIL.SET_PROCESS_FLAG
22122: (p_table_name => 'MSC_ST_ITEM_SUPPLIERS',
22123: p_instance_id => v_instance_id,
22124: p_instance_code => v_instance_code,
22125: p_process_flag => G_VALID,
22126: p_error_text => lv_error_text,
22131: RAISE ex_logging_err;
22132: END IF;
22133:
22134: lv_return := MSC_ST_UTIL.LOG_ERROR
22135: (p_table_name => 'MSC_ST_ITEM_SUPPLIERS',
22136: p_instance_code => v_instance_code,
22137: p_row => lv_column_names,
22138: p_severity => G_SEV_ERROR,
22139: p_message_text => NULL,
22448: p_token1 => 'COLUMN_NAMES',
22449: p_token_value1 => 'SR_INSTANCE_CODE, COMPANY_NAME, ORGANIZATION_CODE,'
22450: ||' ITEM_NAME, VENDOR_NAME AND VENDOR_SITE_CODE',
22451: p_token2 => 'MASTER_TABLE',
22452: p_token_value2 => 'MSC_ST_ITEM_SUPPLIERS',
22453: p_token3 => 'CHILD_TABLE',
22454: p_token_value3 => 'MSC_ST_SUPPLIER_CAPACITIES',
22455: p_token4 => 'VALUE',
22456: p_token_value4 => 'ASL_LEVEL = 1');
22482: ||' AND mis2.using_organization_id = -1'
22483: ||' AND mis2.plan_id = -1'
22484: ||' UNION'
22485: ||' SELECT 1'
22486: ||' FROM msc_st_item_suppliers mis3'
22487: ||' WHERE mis3.vendor_name = mis1.vendor_name'
22488: ||' AND mis3.vendor_site_code = mis1.vendor_site_code'
22489: ||' AND mis3.item_name = mis1.item_name'
22490: ||' AND mis3.sr_instance_code = mis1.sr_instance_code'
22931: p_token1 => 'COLUMN_NAMES',
22932: p_token_value1 => 'SR_INSTANCE_CODE, COMPANY_NAME, ORGANIZATION_CODE,'
22933: ||' ITEM_NAME, VENDOR_NAME AND VENDOR_SITE_CODE',
22934: p_token2 => 'MASTER_TABLE',
22935: p_token_value2 => 'MSC_ST_ITEM_SUPPLIERS',
22936: p_token3 => 'CHILD_TABLE',
22937: p_token_value3 => 'MSC_ST_SUPPLIER_FLEX_FENCES ',
22938: p_token4 => 'VALUE',
22939: p_token_value4 => 'ASL_LEVEL = 1');
22965: ||' AND msf2.using_organization_id = -1'
22966: ||' AND msf2.plan_id = -1'
22967: ||' UNION'
22968: ||' SELECT 1'
22969: ||' FROM msc_st_item_suppliers msf3'
22970: ||' WHERE msf3.vendor_name = msf1.vendor_name'
22971: ||' AND msf3.vendor_site_code = msf1.vendor_site_code'
22972: ||' AND msf3.item_name = msf1.item_name'
22973: ||' AND msf3.sr_instance_code = msf1.sr_instance_code'
50188:
50189:
50190: -- Error out those records where the deleted_flag is set to 'Y' if the collection program doesn't support deletion for this BO.
50191: -- Insert the records into the error_table with the severity warning where deleted_flag has a null value.
50192: -- Update the MSC_ST_ITEM_SUPPLIERS table with the default value 2.
50193:
50194: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
50195: (p_app_short_name => 'MSC',
50196: p_error_code => 'MSC_PP_NO_DELETION',
51026: End IF;
51027: END IF;
51028:
51029: IF v_sup_cap_enabled = SYS_YES THEN
51030: lv_count:= SET_IN_PROCESS (p_table_name => 'MSC_ST_ITEM_SUPPLIERS');
51031: IF lv_count > 0 Then
51032: prec.app_supp_cap_flag:= SYS_YES;
51033: End IF;
51034: lv_count:= SET_IN_PROCESS (p_table_name => 'MSC_ST_SUPPLIER_CAPACITIES', p_erp_enabled => 'Y');