[Home] [Help]
1460: application_short_name => 'MSC',
1461: statement_type => AD_DDL.CREATE_INDEX,
1462: statement =>
1463: 'create index MSC_ST_SUP_CAP_N1_'||v_instance_code
1464: ||' on MSC_ST_SUPPLIER_CAPACITIES '
1465: ||'(sr_instance_code, item_name, vendor_name, from_date,organization_code,company_name,vendor_site_code) '
1466: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
1467: object_name =>'MSC_ST_SUP_CAP_N1_'||v_instance_code);
1468:
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;
1499:
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
21454: TYPE RowidTab IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
21472: AND batch_id = p_batch_id;
21473:
21474: CURSOR c2(p_batch_id NUMBER) IS
21475: SELECT rowid
21476: FROM msc_st_supplier_capacities
21477: WHERE process_flag IN (G_IN_PROCESS,G_ERROR_FLG)
21478: AND sr_instance_code = v_instance_code
21479: AND batch_id = p_batch_id;
21480:
22147:
22148: COMMIT;
22149: END LOOP;
22150:
22151: --******Validation for MSC_ST_SUPPLIER_CAPACITIES***********
22152:
22153: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
22154: (p_app_short_name => 'MSC',
22155: p_error_code => 'MSC_PP_DUP_REC_FOR_XML',
22161: END IF;
22162: --Duplicate records check for the records whose source is XML
22163: v_sql_stmt := 09;
22164: lv_sql_stmt :=
22165: 'UPDATE msc_st_supplier_capacities msc1 '
22166: ||' SET process_flag = '||G_ERROR_FLG||','
22167: ||' error_text = '||''''||lv_message_text||''''
22168: ||' WHERE message_id < (SELECT MAX(message_id)'
22169: ||' FROM msc_st_supplier_capacities msc2'
22165: 'UPDATE msc_st_supplier_capacities msc1 '
22166: ||' SET process_flag = '||G_ERROR_FLG||','
22167: ||' error_text = '||''''||lv_message_text||''''
22168: ||' WHERE message_id < (SELECT MAX(message_id)'
22169: ||' FROM msc_st_supplier_capacities msc2'
22170: ||' WHERE msc2.sr_instance_code = msc1.sr_instance_code'
22171: ||' AND msc2.organization_code = msc1.organization_code'
22172: ||' AND msc2.vendor_name = msc1.vendor_name'
22173: ||' AND msc2.vendor_site_code = msc1.vendor_site_code'
22204: --whereas in batch load we cannot.
22205:
22206: v_sql_stmt := 10;
22207: lv_sql_stmt :=
22208: ' UPDATE msc_st_supplier_capacities msc1'
22209: ||' SET process_flag = '||G_ERROR_FLG||','
22210: ||' error_text = '||''''||lv_message_text||''''
22211: ||' WHERE EXISTS( SELECT 1 '
22212: ||' FROM msc_st_supplier_capacities msc2'
22208: ' UPDATE msc_st_supplier_capacities msc1'
22209: ||' SET process_flag = '||G_ERROR_FLG||','
22210: ||' error_text = '||''''||lv_message_text||''''
22211: ||' WHERE EXISTS( SELECT 1 '
22212: ||' FROM msc_st_supplier_capacities msc2'
22213: ||' WHERE msc2.sr_instance_code = msc1.sr_instance_code'
22214: ||' AND msc2.organization_code = msc1.organization_code'
22215: ||' AND msc2.vendor_name = msc1.vendor_name'
22216: ||' AND msc2.vendor_site_code = msc1.vendor_site_code'
22253: FROM dual;
22254:
22255: v_sql_stmt := 12;
22256: lv_sql_stmt :=
22257: 'UPDATE msc_st_supplier_capacities '
22258: ||' SET batch_id = :lv_batch_id'
22259: ||' WHERE process_flag IN ('||G_IN_PROCESS||','||G_ERROR_FLG||')'
22260: ||' AND sr_instance_code = :v_instance_code'
22261: ||' AND NVL(batch_id,'||NULL_VALUE||') = '||NULL_VALUE
22275: CLOSE c2;
22276:
22277: v_sql_stmt := 13;
22278: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
22279: UPDATE msc_st_supplier_capacities
22280: SET st_transaction_id = msc_st_supplier_capacities_s.NEXTVAL,
22281: refresh_id = v_refresh_id,
22282: last_update_date = v_current_date,
22283: last_updated_by = v_current_user,
22276:
22277: v_sql_stmt := 13;
22278: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
22279: UPDATE msc_st_supplier_capacities
22280: SET st_transaction_id = msc_st_supplier_capacities_s.NEXTVAL,
22281: refresh_id = v_refresh_id,
22282: last_update_date = v_current_date,
22283: last_updated_by = v_current_user,
22284: creation_date = v_current_date,
22305: ||' NOT IN(1,2)';
22306: --Log a warning for those records where the deleted_flag has a value other
22307: --than SYS_NO
22308: lv_return := MSC_ST_UTIL.LOG_ERROR
22309: (p_table_name => 'MSC_ST_SUPPLIER_CAPACITIES',
22310: p_instance_code => v_instance_code,
22311: p_row => lv_column_names,
22312: p_severity => G_SEV_WARNING,
22313: p_message_text => lv_message_text,
22331: p_token_value1 => 'ORGANIZATION_CODE');
22332:
22333: --Derive Organization_id
22334: lv_return := MSC_ST_UTIL.DERIVE_PARTNER_ORG_ID
22335: (p_table_name => 'MSC_ST_SUPPLIER_CAPACITIES',
22336: p_org_partner_name => 'ORGANIZATION_CODE',
22337: p_org_partner_id => 'ORGANIZATION_ID',
22338: p_instance_code => v_instance_code,
22339: p_partner_type => G_ORGANIZATION,
22361: END IF;
22362:
22363: --Derive Inventory_item_id
22364: lv_return := MSC_ST_UTIL.DERIVE_ITEM_ID
22365: (p_table_name => 'MSC_ST_SUPPLIER_CAPACITIES',
22366: p_item_col_name => 'ITEM_NAME',
22367: p_item_col_id => 'INVENTORY_ITEM_ID',
22368: p_instance_id => v_instance_id,
22369: p_instance_code => v_instance_code,
22391: END IF;
22392:
22393: --Derive Supplier_id
22394: lv_return := MSC_ST_UTIL.DERIVE_PARTNER_ORG_ID
22395: (p_table_name => 'MSC_ST_SUPPLIER_CAPACITIES',
22396: p_org_partner_name => 'VENDOR_NAME',
22397: p_org_partner_id => 'SUPPLIER_ID',
22398: p_instance_code => v_instance_code,
22399: p_partner_type => G_VENDOR,
22421: END IF;
22422:
22423: --Derive Supplier_site_id
22424: lv_return := MSC_ST_UTIL.DERIVE_PARTNER_SITE_ID
22425: (p_table_name => 'MSC_ST_SUPPLIER_CAPACITIES',
22426: p_partner_name => 'VENDOR_NAME',
22427: p_partner_site_code => 'VENDOR_SITE_CODE',
22428: p_partner_site_id => 'SUPPLIER_SITE_ID',
22429: p_instance_code => v_instance_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');
22457: IF lv_return <> 0 THEN
22458: RAISE ex_logging_err;
22460:
22461: --Checking for the referential integrity.
22462: v_sql_stmt := 14;
22463: lv_sql_stmt :=
22464: 'UPDATE msc_st_supplier_capacities mis1 '
22465: ||' SET error_text = '||''''||lv_message_text||''''||','
22466: ||' process_flag = '||G_ERROR_FLG
22467: ||' WHERE NOT EXISTS(SELECT 1'
22468: ||' FROM msc_item_suppliers mis2, MSC_TP_ID_LID mtil, MSC_TP_SITE_ID_LID mtsil, MSC_ITEM_ID_LID mil'
22510: --globally valid supplier rules.
22511: v_sql_stmt := 15;
22512:
22513: lv_sql_stmt :=
22514: 'UPDATE msc_st_supplier_capacities mis '
22515: ||' SET using_organization_id = '||G_USING_ORG_ID
22516: ||' WHERE sr_instance_code = :v_instance_code'
22517: ||' AND batch_id = :lv_batch_id'
22518: ||' AND process_flag = '||G_IN_PROCESS
22540: --From_date cannot be null.
22541: v_sql_stmt := 16;
22542:
22543: lv_sql_stmt :=
22544: 'UPDATE msc_st_supplier_capacities mis '
22545: ||' SET error_text = '||''''||lv_message_text||''''||','
22546: ||' process_flag = '||G_ERROR_FLG
22547: ||' WHERE NVL(from_date,(sysdate-36500)) = (sysdate-36500)'
22548: ||' AND deleted_flag = '||SYS_NO
22563: (ERRBUF => lv_error_text,
22564: RETCODE => lv_return,
22565: pBatchID => lv_batch_id,
22566: pInstanceCode => v_instance_code,
22567: pEntityName => 'MSC_ST_SUPPLIER_CAPACITIES',
22568: pInstanceID => v_instance_id);
22569:
22570: IF NVL(lv_return,0) <> 0 THEN
22571: RAISE ex_logging_err;
22571: RAISE ex_logging_err;
22572: END IF;
22573:
22574: lv_return := MSC_ST_UTIL.SET_PROCESS_FLAG
22575: (p_table_name => 'MSC_ST_SUPPLIER_CAPACITIES',
22576: p_instance_id => v_instance_id,
22577: p_instance_code => v_instance_code,
22578: p_process_flag => G_VALID,
22579: p_error_text => lv_error_text,
22584: RAISE ex_logging_err;
22585: END IF;
22586:
22587: lv_return := MSC_ST_UTIL.LOG_ERROR
22588: (p_table_name => 'MSC_ST_SUPPLIER_CAPACITIES',
22589: p_instance_code => v_instance_code,
22590: p_row => lv_column_names,
22591: p_severity => G_SEV_ERROR,
22592: p_message_text => NULL,
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');
51035: IF lv_count > 0 Then
51036: prec.app_supp_cap_flag:= SYS_YES;
51037: End IF;
51038: lv_count:= SET_IN_PROCESS (p_table_name => 'MSC_ST_SUPPLIER_FLEX_FENCES', p_erp_enabled => 'Y');