[Home] [Help]
1478: application_short_name => 'MSC',
1479: statement_type => AD_DDL.CREATE_INDEX,
1480: statement =>
1481: 'create index MSC_ST_SUP_FENCES_N1_'||v_instance_code
1482: ||' on MSC_ST_SUPPLIER_FLEX_FENCES '
1483: ||'(sr_instance_code, item_name,vendor_name,fence_days,organization_code,company_name,vendor_site_code) '
1484: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
1485: object_name =>'MSC_ST_SUP_FENCES_N1_'||v_instance_code);
1486:
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:
1500: IF v_safety_stk_enabled = SYS_YES THEN
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;
21479: AND batch_id = p_batch_id;
21480:
21481: CURSOR c3(p_batch_id NUMBER) IS
21482: SELECT rowid
21483: FROM msc_st_supplier_flex_fences
21484: WHERE process_flag IN (G_IN_PROCESS,G_ERROR_FLG)
21485: AND sr_instance_code = v_instance_code
21486: AND batch_id = p_batch_id;
21487:
22599: END IF;
22600: COMMIT;
22601: END LOOP;
22602:
22603: --*********Validation for MSC_ST_SUPPLIER_FLEX_FENCES***********
22604:
22605: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
22606: (p_app_short_name => 'MSC',
22607: p_error_code => 'MSC_PP_DUP_REC_FOR_XML',
22614:
22615: --Duplicate records check for the records whose source is XML
22616: v_sql_stmt := 17;
22617: lv_sql_stmt :=
22618: 'UPDATE msc_st_supplier_flex_fences msf1 '
22619: ||' SET process_flag = '||G_ERROR_FLG||','
22620: ||' error_text = '||''''||lv_message_text||''''
22621: ||' WHERE message_id < (SELECT MAX(message_id)'
22622: ||' FROM msc_st_supplier_flex_fences msf2'
22618: 'UPDATE msc_st_supplier_flex_fences msf1 '
22619: ||' SET process_flag = '||G_ERROR_FLG||','
22620: ||' error_text = '||''''||lv_message_text||''''
22621: ||' WHERE message_id < (SELECT MAX(message_id)'
22622: ||' FROM msc_st_supplier_flex_fences msf2'
22623: ||' WHERE msf2.sr_instance_code = msf1.sr_instance_code'
22624: ||' AND msf2.organization_code = msf1.organization_code'
22625: ||' AND NVL(msf2.company_name, '||''''||NULL_CHAR||''''||') = '
22626: ||' NVL(msf1.company_name, '||''''||NULL_CHAR||''''||')'
22655: END IF;
22656:
22657: v_sql_stmt := 18;
22658: lv_sql_stmt :=
22659: 'UPDATE msc_st_supplier_flex_fences msf1 '
22660: ||' SET process_flag = '||G_ERROR_FLG||','
22661: ||' error_text = '||''''||lv_message_text||''''
22662: ||' WHERE EXISTS( SELECT 1 '
22663: ||' FROM msc_st_supplier_flex_fences msf2'
22659: 'UPDATE msc_st_supplier_flex_fences msf1 '
22660: ||' SET process_flag = '||G_ERROR_FLG||','
22661: ||' error_text = '||''''||lv_message_text||''''
22662: ||' WHERE EXISTS( SELECT 1 '
22663: ||' FROM msc_st_supplier_flex_fences msf2'
22664: ||' WHERE msf2.sr_instance_code = msf1.sr_instance_code'
22665: ||' AND msf2.organization_code = msf1.organization_code'
22666: ||' AND NVL(msf2.company_name, '||''''||NULL_CHAR||''''||') = '
22667: ||' NVL(msf1.company_name, '||''''||NULL_CHAR||''''||')'
22703: FROM dual;
22704:
22705: v_sql_stmt := 20;
22706: lv_sql_stmt :=
22707: 'UPDATE msc_st_supplier_flex_fences '
22708: ||' SET batch_id = :lv_batch_id'
22709: ||' WHERE process_flag IN ('||G_IN_PROCESS||','||G_ERROR_FLG||')'
22710: ||' AND sr_instance_code = :v_instance_code'
22711: ||' AND NVL(batch_id,'||NULL_VALUE||') = '||NULL_VALUE
22725: CLOSE c3;
22726:
22727: v_sql_stmt := 21;
22728: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
22729: UPDATE msc_st_supplier_flex_fences
22730: SET st_transaction_id = msc_st_supplier_flex_fences_s.NEXTVAL,
22731: refresh_id = v_refresh_id,
22732: last_update_date = v_current_date,
22733: last_updated_by = v_current_user,
22726:
22727: v_sql_stmt := 21;
22728: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
22729: UPDATE msc_st_supplier_flex_fences
22730: SET st_transaction_id = msc_st_supplier_flex_fences_s.NEXTVAL,
22731: refresh_id = v_refresh_id,
22732: last_update_date = v_current_date,
22733: last_updated_by = v_current_user,
22734: creation_date = v_current_date,
22741: p_error_code => 'MSC_PP_NO_DELETION',
22742: p_message_text => lv_message_text,
22743: p_error_text => lv_error_text,
22744: p_token1 => 'TABLE_NAME',
22745: p_token_value1 => 'MSC_ST_SUPPLIER_FLEX_FENCES');
22746:
22747: IF lv_return <> 0 THEN
22748: RAISE ex_logging_err;
22749: END IF;
22750:
22751: --Deletion is not allowed on this table.
22752: v_sql_stmt := 22;
22753: lv_sql_stmt :=
22754: 'UPDATE msc_st_supplier_flex_fences'
22755: ||' SET process_flag = '||G_ERROR_FLG||','
22756: ||' error_text = '||''''||lv_message_text||''''
22757: ||' WHERE deleted_flag = '||SYS_YES
22758: ||' AND process_flag = '||G_IN_PROCESS
22785:
22786: --Log a warning for those records where the deleted_flag has a value other
22787: --than SYS_NO
22788: lv_return := MSC_ST_UTIL.LOG_ERROR
22789: (p_table_name => 'MSC_ST_SUPPLIER_FLEX_FENCES',
22790: p_instance_code => v_instance_code,
22791: p_row => lv_column_names,
22792: p_severity => G_SEV_WARNING,
22793: p_message_text => lv_message_text,
22815: END IF;
22816:
22817: --Derive Organization_id
22818: lv_return := MSC_ST_UTIL.DERIVE_PARTNER_ORG_ID
22819: (p_table_name => 'MSC_ST_SUPPLIER_FLEX_FENCES',
22820: p_org_partner_name => 'ORGANIZATION_CODE',
22821: p_org_partner_id => 'ORGANIZATION_ID',
22822: p_instance_code => v_instance_code,
22823: p_partner_type => G_ORGANIZATION,
22845: END IF;
22846:
22847: --Derive Inventory_item_id
22848: lv_return := MSC_ST_UTIL.DERIVE_ITEM_ID
22849: (p_table_name => 'MSC_ST_SUPPLIER_FLEX_FENCES',
22850: p_item_col_name => 'ITEM_NAME',
22851: p_item_col_id => 'INVENTORY_ITEM_ID',
22852: p_instance_id => v_instance_id,
22853: p_instance_code => v_instance_code,
22875: END IF;
22876:
22877: --Derive Supplier_id
22878: lv_return := MSC_ST_UTIL.DERIVE_PARTNER_ORG_ID
22879: (p_table_name => 'MSC_ST_SUPPLIER_FLEX_FENCES',
22880: p_org_partner_name => 'VENDOR_NAME',
22881: p_org_partner_id => 'SUPPLIER_ID',
22882: p_instance_code => v_instance_code,
22883: p_partner_type => G_VENDOR,
22905: END IF;
22906:
22907: --Derive Supplier_site_id
22908: lv_return := MSC_ST_UTIL.DERIVE_PARTNER_SITE_ID
22909: (p_table_name => 'MSC_ST_SUPPLIER_FLEX_FENCES',
22910: p_partner_name => 'VENDOR_NAME',
22911: p_partner_site_code=> 'VENDOR_SITE_CODE',
22912: p_partner_site_id => 'SUPPLIER_SITE_ID',
22913: p_instance_code => v_instance_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');
22940: IF lv_return <> 0 THEN
22941: RAISE ex_logging_err;
22943:
22944: --Checking for the referential integrity.
22945: v_sql_stmt := 23;
22946: lv_sql_stmt :=
22947: 'UPDATE msc_st_supplier_flex_fences msf1'
22948: ||' SET error_text = '||''''||lv_message_text||''''||','
22949: ||' process_flag = '||G_ERROR_FLG
22950: ||' WHERE NOT EXISTS(SELECT 1'
22951: ||' FROM msc_item_suppliers msf2, MSC_TP_ID_LID mtil, MSC_TP_SITE_ID_LID mtsil, MSC_ITEM_ID_LID mil'
22991: --Setting using_organization_id to the default value -1 which is for the
22992: --globally valid supplier rules.
22993: v_sql_stmt := 24;
22994: lv_sql_stmt :=
22995: 'UPDATE msc_st_supplier_flex_fences mis '
22996: ||' SET using_organization_id = '||G_USING_ORG_ID
22997: ||' WHERE deleted_flag = '||SYS_NO
22998: ||' AND process_flag = '||G_IN_PROCESS
22999: ||' AND batch_id = :lv_batch_id'
23020:
23021: --Fence days should not be null.
23022: v_sql_stmt := 25;
23023: lv_sql_stmt :=
23024: 'UPDATE msc_st_supplier_flex_fences '
23025: ||' SET error_text = '||''''||lv_message_text||''''||','
23026: ||' process_flag = '||G_ERROR_FLG
23027: ||' WHERE NVL(fence_days,'||NULL_VALUE||' )= '||NULL_VALUE
23028: ||' AND deleted_flag ='|| SYS_NO
23042: (ERRBUF => lv_error_text,
23043: RETCODE => lv_return,
23044: pBatchID => lv_batch_id,
23045: pInstanceCode => v_instance_code,
23046: pEntityName => 'MSC_ST_SUPPLIER_FLEX_FENCES',
23047: pInstanceID => v_instance_id);
23048:
23049: IF NVL(lv_return,0) <> 0 THEN
23050: RAISE ex_logging_err;
23050: RAISE ex_logging_err;
23051: END IF;
23052:
23053: lv_return := MSC_ST_UTIL.SET_PROCESS_FLAG
23054: (p_table_name => 'MSC_ST_SUPPLIER_FLEX_FENCES',
23055: p_instance_id => v_instance_id,
23056: p_instance_code => v_instance_code,
23057: p_process_flag => G_VALID,
23058: p_error_text => lv_error_text,
23063: RAISE ex_logging_err;
23064: END IF;
23065:
23066: lv_return := MSC_ST_UTIL.LOG_ERROR
23067: (p_table_name => 'MSC_ST_SUPPLIER_FLEX_FENCES',
23068: p_instance_code => v_instance_code,
23069: p_row => lv_column_names,
23070: p_severity => G_ERROR_FLG,
23071: p_message_text => NULL,
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');
51039: IF lv_count > 0 Then
51040: prec.app_supp_cap_flag:= SYS_YES;
51041: End IF;
51042: END IF;