[Home] [Help]
1486: application_short_name => 'MSC',
1487: statement_type => AD_DDL.CREATE_INDEX,
1488: statement =>
1489: 'create index MSC_ST_SUP_FENCES_N1_'||v_instance_code
1490: ||' on MSC_ST_SUPPLIER_FLEX_FENCES '
1491: ||'(sr_instance_code, item_name,vendor_name,fence_days,organization_code,company_name,vendor_site_code) '
1492: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
1493: object_name =>'MSC_ST_SUP_FENCES_N1_'||v_instance_code);
1494:
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:
1508: IF v_safety_stk_enabled = SYS_YES THEN
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;
24968: AND batch_id = p_batch_id;
24969:
24970: CURSOR c3(p_batch_id NUMBER) IS
24971: SELECT rowid
24972: FROM msc_st_supplier_flex_fences
24973: WHERE process_flag IN (G_IN_PROCESS,G_ERROR_FLG)
24974: AND sr_instance_code = v_instance_code
24975: AND batch_id = p_batch_id;
24976:
26088: END IF;
26089: COMMIT;
26090: END LOOP;
26091:
26092: --*********Validation for MSC_ST_SUPPLIER_FLEX_FENCES***********
26093:
26094: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
26095: (p_app_short_name => 'MSC',
26096: p_error_code => 'MSC_PP_DUP_REC_FOR_XML',
26103:
26104: --Duplicate records check for the records whose source is XML
26105: v_sql_stmt := 17;
26106: lv_sql_stmt :=
26107: 'UPDATE msc_st_supplier_flex_fences msf1 '
26108: ||' SET process_flag = '||G_ERROR_FLG||','
26109: ||' error_text = '||''''||lv_message_text||''''
26110: ||' WHERE message_id < (SELECT MAX(message_id)'
26111: ||' FROM msc_st_supplier_flex_fences msf2'
26107: 'UPDATE msc_st_supplier_flex_fences msf1 '
26108: ||' SET process_flag = '||G_ERROR_FLG||','
26109: ||' error_text = '||''''||lv_message_text||''''
26110: ||' WHERE message_id < (SELECT MAX(message_id)'
26111: ||' FROM msc_st_supplier_flex_fences msf2'
26112: ||' WHERE msf2.sr_instance_code = msf1.sr_instance_code'
26113: ||' AND msf2.organization_code = msf1.organization_code'
26114: ||' AND NVL(msf2.company_name, '||''''||NULL_CHAR||''''||') = '
26115: ||' NVL(msf1.company_name, '||''''||NULL_CHAR||''''||')'
26144: END IF;
26145:
26146: v_sql_stmt := 18;
26147: lv_sql_stmt :=
26148: 'UPDATE msc_st_supplier_flex_fences msf1 '
26149: ||' SET process_flag = '||G_ERROR_FLG||','
26150: ||' error_text = '||''''||lv_message_text||''''
26151: ||' WHERE EXISTS( SELECT 1 '
26152: ||' FROM msc_st_supplier_flex_fences msf2'
26148: 'UPDATE msc_st_supplier_flex_fences msf1 '
26149: ||' SET process_flag = '||G_ERROR_FLG||','
26150: ||' error_text = '||''''||lv_message_text||''''
26151: ||' WHERE EXISTS( SELECT 1 '
26152: ||' FROM msc_st_supplier_flex_fences msf2'
26153: ||' WHERE msf2.sr_instance_code = msf1.sr_instance_code'
26154: ||' AND msf2.organization_code = msf1.organization_code'
26155: ||' AND NVL(msf2.company_name, '||''''||NULL_CHAR||''''||') = '
26156: ||' NVL(msf1.company_name, '||''''||NULL_CHAR||''''||')'
26192: FROM dual;
26193:
26194: v_sql_stmt := 20;
26195: lv_sql_stmt :=
26196: 'UPDATE msc_st_supplier_flex_fences '
26197: ||' SET batch_id = :lv_batch_id'
26198: ||' WHERE process_flag IN ('||G_IN_PROCESS||','||G_ERROR_FLG||')'
26199: ||' AND sr_instance_code = :v_instance_code'
26200: ||' AND NVL(batch_id,'||NULL_VALUE||') = '||NULL_VALUE
26214: CLOSE c3;
26215:
26216: v_sql_stmt := 21;
26217: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
26218: UPDATE msc_st_supplier_flex_fences
26219: SET st_transaction_id = msc_st_supplier_flex_fences_s.NEXTVAL,
26220: refresh_id = v_refresh_id,
26221: last_update_date = v_current_date,
26222: last_updated_by = v_current_user,
26215:
26216: v_sql_stmt := 21;
26217: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
26218: UPDATE msc_st_supplier_flex_fences
26219: SET st_transaction_id = msc_st_supplier_flex_fences_s.NEXTVAL,
26220: refresh_id = v_refresh_id,
26221: last_update_date = v_current_date,
26222: last_updated_by = v_current_user,
26223: creation_date = v_current_date,
26230: p_error_code => 'MSC_PP_NO_DELETION',
26231: p_message_text => lv_message_text,
26232: p_error_text => lv_error_text,
26233: p_token1 => 'TABLE_NAME',
26234: p_token_value1 => 'MSC_ST_SUPPLIER_FLEX_FENCES');
26235:
26236: IF lv_return <> 0 THEN
26237: RAISE ex_logging_err;
26238: END IF;
26239:
26240: --Deletion is not allowed on this table.
26241: v_sql_stmt := 22;
26242: lv_sql_stmt :=
26243: 'UPDATE msc_st_supplier_flex_fences'
26244: ||' SET process_flag = '||G_ERROR_FLG||','
26245: ||' error_text = '||''''||lv_message_text||''''
26246: ||' WHERE deleted_flag = '||SYS_YES
26247: ||' AND process_flag = '||G_IN_PROCESS
26274:
26275: --Log a warning for those records where the deleted_flag has a value other
26276: --than SYS_NO
26277: lv_return := MSC_ST_UTIL.LOG_ERROR
26278: (p_table_name => 'MSC_ST_SUPPLIER_FLEX_FENCES',
26279: p_instance_code => v_instance_code,
26280: p_row => lv_column_names,
26281: p_severity => G_SEV_WARNING,
26282: p_message_text => lv_message_text,
26304: END IF;
26305:
26306: --Derive Organization_id
26307: lv_return := MSC_ST_UTIL.DERIVE_PARTNER_ORG_ID
26308: (p_table_name => 'MSC_ST_SUPPLIER_FLEX_FENCES',
26309: p_org_partner_name => 'ORGANIZATION_CODE',
26310: p_org_partner_id => 'ORGANIZATION_ID',
26311: p_instance_code => v_instance_code,
26312: p_partner_type => G_ORGANIZATION,
26334: END IF;
26335:
26336: --Derive Inventory_item_id
26337: lv_return := MSC_ST_UTIL.DERIVE_ITEM_ID
26338: (p_table_name => 'MSC_ST_SUPPLIER_FLEX_FENCES',
26339: p_item_col_name => 'ITEM_NAME',
26340: p_item_col_id => 'INVENTORY_ITEM_ID',
26341: p_instance_id => v_instance_id,
26342: p_instance_code => v_instance_code,
26364: END IF;
26365:
26366: --Derive Supplier_id
26367: lv_return := MSC_ST_UTIL.DERIVE_PARTNER_ORG_ID
26368: (p_table_name => 'MSC_ST_SUPPLIER_FLEX_FENCES',
26369: p_org_partner_name => 'VENDOR_NAME',
26370: p_org_partner_id => 'SUPPLIER_ID',
26371: p_instance_code => v_instance_code,
26372: p_partner_type => G_VENDOR,
26394: END IF;
26395:
26396: --Derive Supplier_site_id
26397: lv_return := MSC_ST_UTIL.DERIVE_PARTNER_SITE_ID
26398: (p_table_name => 'MSC_ST_SUPPLIER_FLEX_FENCES',
26399: p_partner_name => 'VENDOR_NAME',
26400: p_partner_site_code=> 'VENDOR_SITE_CODE',
26401: p_partner_site_id => 'SUPPLIER_SITE_ID',
26402: p_instance_code => v_instance_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');
26429: IF lv_return <> 0 THEN
26430: RAISE ex_logging_err;
26432:
26433: --Checking for the referential integrity.
26434: v_sql_stmt := 23;
26435: lv_sql_stmt :=
26436: 'UPDATE msc_st_supplier_flex_fences msf1'
26437: ||' SET error_text = '||''''||lv_message_text||''''||','
26438: ||' process_flag = '||G_ERROR_FLG
26439: ||' WHERE NOT EXISTS(SELECT 1'
26440: ||' FROM msc_item_suppliers msf2, MSC_TP_ID_LID mtil, MSC_TP_SITE_ID_LID mtsil, MSC_ITEM_ID_LID mil'
26480: --Setting using_organization_id to the default value -1 which is for the
26481: --globally valid supplier rules.
26482: v_sql_stmt := 24;
26483: lv_sql_stmt :=
26484: 'UPDATE msc_st_supplier_flex_fences mis '
26485: ||' SET using_organization_id = '||G_USING_ORG_ID
26486: ||' WHERE deleted_flag = '||SYS_NO
26487: ||' AND process_flag = '||G_IN_PROCESS
26488: ||' AND batch_id = :lv_batch_id'
26509:
26510: --Fence days should not be null.
26511: v_sql_stmt := 25;
26512: lv_sql_stmt :=
26513: 'UPDATE msc_st_supplier_flex_fences '
26514: ||' SET error_text = '||''''||lv_message_text||''''||','
26515: ||' process_flag = '||G_ERROR_FLG
26516: ||' WHERE NVL(fence_days,'||NULL_VALUE||' )= '||NULL_VALUE
26517: ||' AND deleted_flag ='|| SYS_NO
26531: (ERRBUF => lv_error_text,
26532: RETCODE => lv_return,
26533: pBatchID => lv_batch_id,
26534: pInstanceCode => v_instance_code,
26535: pEntityName => 'MSC_ST_SUPPLIER_FLEX_FENCES',
26536: pInstanceID => v_instance_id);
26537:
26538: IF NVL(lv_return,0) <> 0 THEN
26539: RAISE ex_logging_err;
26539: RAISE ex_logging_err;
26540: END IF;
26541:
26542: lv_return := MSC_ST_UTIL.SET_PROCESS_FLAG
26543: (p_table_name => 'MSC_ST_SUPPLIER_FLEX_FENCES',
26544: p_instance_id => v_instance_id,
26545: p_instance_code => v_instance_code,
26546: p_process_flag => G_VALID,
26547: p_error_text => lv_error_text,
26552: RAISE ex_logging_err;
26553: END IF;
26554:
26555: lv_return := MSC_ST_UTIL.LOG_ERROR
26556: (p_table_name => 'MSC_ST_SUPPLIER_FLEX_FENCES',
26557: p_instance_code => v_instance_code,
26558: p_row => lv_column_names,
26559: p_severity => G_ERROR_FLG,
26560: p_message_text => NULL,
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');
55961: IF lv_count > 0 Then
55962: prec.app_supp_cap_flag:= SYS_YES;
55963: End IF;
55964: END IF;