[Home] [Help]
1468: application_short_name => 'MSC',
1469: statement_type => AD_DDL.CREATE_INDEX,
1470: statement =>
1471: 'create index MSC_ST_SUP_CAP_N1_'||v_instance_code
1472: ||' on MSC_ST_SUPPLIER_CAPACITIES '
1473: ||'(sr_instance_code, item_name, vendor_name, from_date,organization_code,company_name,vendor_site_code) '
1474: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
1475: object_name =>'MSC_ST_SUP_CAP_N1_'||v_instance_code);
1476:
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;
1507:
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
24943: TYPE RowidTab IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
24961: AND batch_id = p_batch_id;
24962:
24963: CURSOR c2(p_batch_id NUMBER) IS
24964: SELECT rowid
24965: FROM msc_st_supplier_capacities
24966: WHERE process_flag IN (G_IN_PROCESS,G_ERROR_FLG)
24967: AND sr_instance_code = v_instance_code
24968: AND batch_id = p_batch_id;
24969:
25636:
25637: COMMIT;
25638: END LOOP;
25639:
25640: --******Validation for MSC_ST_SUPPLIER_CAPACITIES***********
25641:
25642: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
25643: (p_app_short_name => 'MSC',
25644: p_error_code => 'MSC_PP_DUP_REC_FOR_XML',
25650: END IF;
25651: --Duplicate records check for the records whose source is XML
25652: v_sql_stmt := 09;
25653: lv_sql_stmt :=
25654: 'UPDATE msc_st_supplier_capacities msc1 '
25655: ||' SET process_flag = '||G_ERROR_FLG||','
25656: ||' error_text = '||''''||lv_message_text||''''
25657: ||' WHERE message_id < (SELECT MAX(message_id)'
25658: ||' FROM msc_st_supplier_capacities msc2'
25654: 'UPDATE msc_st_supplier_capacities msc1 '
25655: ||' SET process_flag = '||G_ERROR_FLG||','
25656: ||' error_text = '||''''||lv_message_text||''''
25657: ||' WHERE message_id < (SELECT MAX(message_id)'
25658: ||' FROM msc_st_supplier_capacities msc2'
25659: ||' WHERE msc2.sr_instance_code = msc1.sr_instance_code'
25660: ||' AND msc2.organization_code = msc1.organization_code'
25661: ||' AND msc2.vendor_name = msc1.vendor_name'
25662: ||' AND msc2.vendor_site_code = msc1.vendor_site_code'
25693: --whereas in batch load we cannot.
25694:
25695: v_sql_stmt := 10;
25696: lv_sql_stmt :=
25697: ' UPDATE msc_st_supplier_capacities msc1'
25698: ||' SET process_flag = '||G_ERROR_FLG||','
25699: ||' error_text = '||''''||lv_message_text||''''
25700: ||' WHERE EXISTS( SELECT 1 '
25701: ||' FROM msc_st_supplier_capacities msc2'
25697: ' UPDATE msc_st_supplier_capacities msc1'
25698: ||' SET process_flag = '||G_ERROR_FLG||','
25699: ||' error_text = '||''''||lv_message_text||''''
25700: ||' WHERE EXISTS( SELECT 1 '
25701: ||' FROM msc_st_supplier_capacities msc2'
25702: ||' WHERE msc2.sr_instance_code = msc1.sr_instance_code'
25703: ||' AND msc2.organization_code = msc1.organization_code'
25704: ||' AND msc2.vendor_name = msc1.vendor_name'
25705: ||' AND msc2.vendor_site_code = msc1.vendor_site_code'
25742: FROM dual;
25743:
25744: v_sql_stmt := 12;
25745: lv_sql_stmt :=
25746: 'UPDATE msc_st_supplier_capacities '
25747: ||' SET batch_id = :lv_batch_id'
25748: ||' WHERE process_flag IN ('||G_IN_PROCESS||','||G_ERROR_FLG||')'
25749: ||' AND sr_instance_code = :v_instance_code'
25750: ||' AND NVL(batch_id,'||NULL_VALUE||') = '||NULL_VALUE
25764: CLOSE c2;
25765:
25766: v_sql_stmt := 13;
25767: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
25768: UPDATE msc_st_supplier_capacities
25769: SET st_transaction_id = msc_st_supplier_capacities_s.NEXTVAL,
25770: refresh_id = v_refresh_id,
25771: last_update_date = v_current_date,
25772: last_updated_by = v_current_user,
25765:
25766: v_sql_stmt := 13;
25767: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
25768: UPDATE msc_st_supplier_capacities
25769: SET st_transaction_id = msc_st_supplier_capacities_s.NEXTVAL,
25770: refresh_id = v_refresh_id,
25771: last_update_date = v_current_date,
25772: last_updated_by = v_current_user,
25773: creation_date = v_current_date,
25794: ||' NOT IN(1,2)';
25795: --Log a warning for those records where the deleted_flag has a value other
25796: --than SYS_NO
25797: lv_return := MSC_ST_UTIL.LOG_ERROR
25798: (p_table_name => 'MSC_ST_SUPPLIER_CAPACITIES',
25799: p_instance_code => v_instance_code,
25800: p_row => lv_column_names,
25801: p_severity => G_SEV_WARNING,
25802: p_message_text => lv_message_text,
25820: p_token_value1 => 'ORGANIZATION_CODE');
25821:
25822: --Derive Organization_id
25823: lv_return := MSC_ST_UTIL.DERIVE_PARTNER_ORG_ID
25824: (p_table_name => 'MSC_ST_SUPPLIER_CAPACITIES',
25825: p_org_partner_name => 'ORGANIZATION_CODE',
25826: p_org_partner_id => 'ORGANIZATION_ID',
25827: p_instance_code => v_instance_code,
25828: p_partner_type => G_ORGANIZATION,
25850: END IF;
25851:
25852: --Derive Inventory_item_id
25853: lv_return := MSC_ST_UTIL.DERIVE_ITEM_ID
25854: (p_table_name => 'MSC_ST_SUPPLIER_CAPACITIES',
25855: p_item_col_name => 'ITEM_NAME',
25856: p_item_col_id => 'INVENTORY_ITEM_ID',
25857: p_instance_id => v_instance_id,
25858: p_instance_code => v_instance_code,
25880: END IF;
25881:
25882: --Derive Supplier_id
25883: lv_return := MSC_ST_UTIL.DERIVE_PARTNER_ORG_ID
25884: (p_table_name => 'MSC_ST_SUPPLIER_CAPACITIES',
25885: p_org_partner_name => 'VENDOR_NAME',
25886: p_org_partner_id => 'SUPPLIER_ID',
25887: p_instance_code => v_instance_code,
25888: p_partner_type => G_VENDOR,
25910: END IF;
25911:
25912: --Derive Supplier_site_id
25913: lv_return := MSC_ST_UTIL.DERIVE_PARTNER_SITE_ID
25914: (p_table_name => 'MSC_ST_SUPPLIER_CAPACITIES',
25915: p_partner_name => 'VENDOR_NAME',
25916: p_partner_site_code => 'VENDOR_SITE_CODE',
25917: p_partner_site_id => 'SUPPLIER_SITE_ID',
25918: p_instance_code => v_instance_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');
25946: IF lv_return <> 0 THEN
25947: RAISE ex_logging_err;
25949:
25950: --Checking for the referential integrity.
25951: v_sql_stmt := 14;
25952: lv_sql_stmt :=
25953: 'UPDATE msc_st_supplier_capacities mis1 '
25954: ||' SET error_text = '||''''||lv_message_text||''''||','
25955: ||' process_flag = '||G_ERROR_FLG
25956: ||' WHERE NOT EXISTS(SELECT 1'
25957: ||' FROM msc_item_suppliers mis2, MSC_TP_ID_LID mtil, MSC_TP_SITE_ID_LID mtsil, MSC_ITEM_ID_LID mil'
25999: --globally valid supplier rules.
26000: v_sql_stmt := 15;
26001:
26002: lv_sql_stmt :=
26003: 'UPDATE msc_st_supplier_capacities mis '
26004: ||' SET using_organization_id = '||G_USING_ORG_ID
26005: ||' WHERE sr_instance_code = :v_instance_code'
26006: ||' AND batch_id = :lv_batch_id'
26007: ||' AND process_flag = '||G_IN_PROCESS
26029: --From_date cannot be null.
26030: v_sql_stmt := 16;
26031:
26032: lv_sql_stmt :=
26033: 'UPDATE msc_st_supplier_capacities mis '
26034: ||' SET error_text = '||''''||lv_message_text||''''||','
26035: ||' process_flag = '||G_ERROR_FLG
26036: ||' WHERE NVL(from_date,(sysdate-36500)) = (sysdate-36500)'
26037: ||' AND deleted_flag = '||SYS_NO
26052: (ERRBUF => lv_error_text,
26053: RETCODE => lv_return,
26054: pBatchID => lv_batch_id,
26055: pInstanceCode => v_instance_code,
26056: pEntityName => 'MSC_ST_SUPPLIER_CAPACITIES',
26057: pInstanceID => v_instance_id);
26058:
26059: IF NVL(lv_return,0) <> 0 THEN
26060: RAISE ex_logging_err;
26060: RAISE ex_logging_err;
26061: END IF;
26062:
26063: lv_return := MSC_ST_UTIL.SET_PROCESS_FLAG
26064: (p_table_name => 'MSC_ST_SUPPLIER_CAPACITIES',
26065: p_instance_id => v_instance_id,
26066: p_instance_code => v_instance_code,
26067: p_process_flag => G_VALID,
26068: p_error_text => lv_error_text,
26073: RAISE ex_logging_err;
26074: END IF;
26075:
26076: lv_return := MSC_ST_UTIL.LOG_ERROR
26077: (p_table_name => 'MSC_ST_SUPPLIER_CAPACITIES',
26078: p_instance_code => v_instance_code,
26079: p_row => lv_column_names,
26080: p_severity => G_SEV_ERROR,
26081: p_message_text => NULL,
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');
55957: IF lv_count > 0 Then
55958: prec.app_supp_cap_flag:= SYS_YES;
55959: End IF;
55960: lv_count:= SET_IN_PROCESS (p_table_name => 'MSC_ST_SUPPLIER_FLEX_FENCES', p_erp_enabled => 'Y');