[Home] [Help]
1425: application_short_name => 'MSC',
1426: statement_type => AD_DDL.CREATE_INDEX,
1427: statement =>
1428: 'create index MSC_ST_UOM_CLS_CONV_N1_'||v_instance_code
1429: ||' on MSC_ST_UOM_CLASS_CONVERSIONS '
1430: ||'( from_uom_code, to_uom_code, item_name, company_name) '
1431: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
1432: object_name =>'MSC_ST_UOM_CLS_CONV_N1_'||v_instance_code);
1433:
1437: WHEN OTHERS THEN
1438: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_WARNING,'Error creating Index MSC_ST_UOM_CLS_CONV_N1_'||v_instance_code||'. Error:'||substr(SQLERRM,1,240));
1439: END;
1440:
1441: msc_analyse_tables_pk.analyse_table( 'MSC_ST_UOM_CLASS_CONVERSIONS', v_instance_id, -1);
1442:
1443: END IF;
1444:
1445: IF v_sup_cap_enabled = SYS_YES THEN
20065: END LOAD_UOM;
20066:
20067: /*==========================================================================+
20068: | DESCRIPTION : This procedure performs validation and loads data into the |
20069: | tables msc_st_uom_class_conversions and |
20070: | msc_st_uom_conversions. |
20071: +==========================================================================*/
20072: PROCEDURE LOAD_UOM_CLASS_CONV IS
20073: TYPE RowidTab IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
20082: lv_error_text VARCHAR2(250);
20083: lv_where_str VARCHAR2(5000);
20084: lv_sql_stmt VARCHAR2(5000);
20085: lv_column_names VARCHAR2(5000);
20086: lv_batch_id msc_st_uom_class_conversions.batch_id%TYPE;
20087: lv_message_text msc_errors.error_text%TYPE;
20088:
20089: ex_logging_err EXCEPTION;
20090:
20089: ex_logging_err EXCEPTION;
20090:
20091: CURSOR c_udk1 is
20092: SELECT from_uom_code, to_uom_code, item_name, company_name
20093: FROM msc_st_uom_class_conversions mcc2
20094: WHERE mcc2.process_flag = G_IN_PROCESS
20095: AND item_name is not null
20096: AND sr_instance_code = v_instance_code
20097: AND NVL(mcc2.message_id, NULL_VALUE) = NULL_VALUE
20098: GROUP BY from_uom_code, to_uom_code, item_name, company_name
20099: HAVING COUNT(*) > 1;
20100:
20101: CURSOR c_udk2 is select from_uom_code, to_uom_code, item_name, company_name
20102: FROM msc_st_uom_class_conversions mcc2
20103: WHERE mcc2.process_flag = G_IN_PROCESS
20104: AND item_name is null
20105: AND sr_instance_code = v_instance_code
20106: AND NVL(mcc2.message_id, NULL_VALUE) = NULL_VALUE
20108: HAVING COUNT(*) > 1;
20109:
20110: CURSOR c2(p_batch_id NUMBER) IS
20111: SELECT rowid
20112: FROM msc_st_uom_class_conversions
20113: WHERE process_flag IN (G_IN_PROCESS,G_ERROR_FLG)
20114: AND batch_id = p_batch_id
20115: AND sr_instance_code = v_instance_code;
20116:
20121: AND batch_id = p_batch_id
20122: AND sr_instance_code = v_instance_code;
20123:
20124: BEGIN
20125: --*****Validation for the table msc_st_uom_class_conversions.*****
20126:
20127: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
20128: (p_app_short_name => 'MSC',
20129: p_error_code => 'MSC_PP_DUP_REC_FOR_XML',
20135: END IF;
20136:
20137: /* --Remove organization code from records which have item name null
20138:
20139: UPDATE msc_st_uom_class_conversions
20140: SET organization_code = ''
20141: WHERE sr_instance_code = v_instance_code
20142: AND process_flag = G_IN_PROCESS
20143: AND item_name is null;*/
20144:
20145: --Duplicate records check for the records whose source is XML.
20146: v_sql_stmt := 10;
20147: lv_sql_stmt :=
20148: 'UPDATE msc_st_uom_class_conversions mcc1 '
20149: ||' SET process_flag = '||G_ERROR_FLG||','
20150: ||' error_text = '||''''||lv_message_text||''''
20151: ||' WHERE message_id < (SELECT MAX(message_id)'
20152: ||' FROM msc_st_uom_class_conversions mcc2'
20148: 'UPDATE msc_st_uom_class_conversions mcc1 '
20149: ||' SET process_flag = '||G_ERROR_FLG||','
20150: ||' error_text = '||''''||lv_message_text||''''
20151: ||' WHERE message_id < (SELECT MAX(message_id)'
20152: ||' FROM msc_st_uom_class_conversions mcc2'
20153: ||' WHERE NVL(mcc2.item_name, '||''''||NULL_CHAR||''''||') = '
20154: ||' NVL(mcc1.item_name, '||''''||NULL_CHAR||''''||')'
20155: ||' AND NVL(mcc2.from_uom_code, '||''''||NULL_CHAR||''''||') = '
20156: ||' NVL(mcc1.from_uom_code, '||''''||NULL_CHAR||''''||')'
20187: --whereas in batch load we cannot.
20188: v_sql_stmt := 11;
20189: /*
20190: lv_sql_stmt :=
20191: 'UPDATE msc_st_uom_class_conversions mcc1 '
20192: ||' SET process_flag = '||G_ERROR_FLG||','
20193: ||' error_text = '||''''||lv_message_text||''''
20194: ||' WHERE EXISTS( SELECT 1 '
20195: ||' FROM msc_st_uom_class_conversions mcc2'
20191: 'UPDATE msc_st_uom_class_conversions mcc1 '
20192: ||' SET process_flag = '||G_ERROR_FLG||','
20193: ||' error_text = '||''''||lv_message_text||''''
20194: ||' WHERE EXISTS( SELECT 1 '
20195: ||' FROM msc_st_uom_class_conversions mcc2'
20196: ||' WHERE NVL(mcc2.item_name, '||''''||NULL_CHAR||''''||') = '
20197: ||' NVL(mcc1.item_name, '||''''||NULL_CHAR||''''||')'
20198: ||' AND NVL(mcc2.organization_code, '||''''||NULL_CHAR||''''||') = '
20199: ||' NVL(mcc1.organization_code, '||''''||NULL_CHAR||''''||')'
20226: FETCH c_udk1 BULK COLLECT INTO lb_from_uom_code,lb_to_uom_code, lb_item,lb_comp;
20227: CLOSE c_udk1;
20228:
20229: FORALL j IN lb_item.FIRST..lb_item.LAST
20230: UPDATE msc_st_uom_class_conversions SET process_flag = G_ERROR_FLG, error_text =lv_message_text
20231: WHERE from_uom_code= lb_from_uom_code(j)
20232: AND to_uom_code = lb_to_uom_code(j)
20233: AND item_name= lb_item(j)
20234: AND NVL(company_name, NULL_CHAR) = NVL(lb_comp(j), NULL_CHAR)
20249: FETCH c_udk2 BULK COLLECT INTO lb_from_uom_code,lb_to_uom_code, lb_item,lb_comp;
20250: CLOSE c_udk2;
20251:
20252: FORALL j IN lb_item.FIRST..lb_item.LAST
20253: UPDATE msc_st_uom_class_conversions SET process_flag = G_ERROR_FLG, error_text =lv_message_text
20254: WHERE from_uom_code= lb_from_uom_code(j)
20255: AND to_uom_code = lb_to_uom_code(j)
20256: AND NVL(company_name, NULL_CHAR) = NVL(lb_comp(j), NULL_CHAR)
20257: AND item_name is null
20287: FROM dual;
20288:
20289: v_sql_stmt := 13;
20290: lv_sql_stmt :=
20291: 'UPDATE msc_st_uom_class_conversions '
20292: ||' SET batch_id = :lv_batch_id'
20293: ||' WHERE process_flag IN ('||G_IN_PROCESS||','||G_ERROR_FLG||')'
20294: ||' AND sr_instance_code = :v_instance_code'
20295: ||' AND NVL(batch_id,'||NULL_VALUE||') = '||NULL_VALUE
20309: CLOSE c2;
20310:
20311: v_sql_stmt := 14;
20312: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
20313: UPDATE msc_st_uom_class_conversions
20314: SET st_transaction_id = msc_st_uom_class_conversions_s.NEXTVAL,
20315: refresh_id = v_refresh_id,
20316: last_update_date = v_current_date,
20317: last_updated_by = v_current_user,
20310:
20311: v_sql_stmt := 14;
20312: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
20313: UPDATE msc_st_uom_class_conversions
20314: SET st_transaction_id = msc_st_uom_class_conversions_s.NEXTVAL,
20315: refresh_id = v_refresh_id,
20316: last_update_date = v_current_date,
20317: last_updated_by = v_current_user,
20318: creation_date = v_current_date,
20324: p_error_code => 'MSC_PP_NO_DELETION',
20325: p_message_text => lv_message_text,
20326: p_error_text => lv_error_text,
20327: p_token1 => 'TABLE_NAME',
20328: p_token_value1 => 'MSC_ST_UOM_CLASS_CONVERSIONS');
20329:
20330: IF lv_return <> 0 THEN
20331: RAISE ex_logging_err;
20332: END IF;
20333:
20334: --Deletion is not allowed on this table.
20335: v_sql_stmt := 15;
20336: lv_sql_stmt :=
20337: 'UPDATE msc_st_uom_class_conversions '
20338: ||' SET process_flag = '||G_ERROR_FLG||','
20339: ||' error_text = '||''''||lv_message_text||''''
20340: ||' WHERE deleted_flag = '||SYS_YES
20341: ||' AND process_flag = '||G_IN_PROCESS
20368:
20369: --Log a warning for those records where the deleted_flag has a value other
20370: --than SYS_NO
20371: lv_return := MSC_ST_UTIL.LOG_ERROR
20372: (p_table_name => 'MSC_ST_UOM_CLASS_CONVERSIONS',
20373: p_instance_code => v_instance_code,
20374: p_row => lv_column_names,
20375: p_severity => G_SEV_WARNING,
20376: p_message_text => lv_message_text,
20399: END IF;
20400:
20401: v_sql_stmt := 16;
20402: lv_sql_stmt :=
20403: 'UPDATE msc_st_uom_class_conversions'
20404: ||' SET process_flag = '||G_ERROR_FLG||','
20405: ||' error_text = '||''''||lv_message_text||''''
20406: ||' WHERE (NVL(from_uom_code,'||''''||NULL_CHAR||''''||') '
20407: ||' = '||''''||NULL_CHAR||''''
20424: v_instance_code;
20425:
20426: v_sql_stmt := 17;
20427: lv_sql_stmt :=
20428: 'UPDATE msc_st_uom_class_conversions'
20429: ||' SET from_unit_of_measure = from_uom_code'
20430: ||' WHERE NVL(from_unit_of_measure,'||''''||NULL_CHAR||''''||')'
20431: ||' = '||''''||NULL_CHAR||''''
20432: ||' AND process_flag = '||G_IN_PROCESS
20441: v_instance_code;
20442:
20443: v_sql_stmt := 18;
20444: lv_sql_stmt :=
20445: 'UPDATE msc_st_uom_class_conversions '
20446: ||' SET to_unit_of_measure = to_uom_code'
20447: ||' WHERE NVL(to_unit_of_measure,'||''''||NULL_CHAR||''''||')'
20448: ||' = '||''''||NULL_CHAR||''''
20449: ||' AND process_flag = '||G_IN_PROCESS
20468: p_token_value2 => 'MSC_ST_UNITS_OF_MEASURE',
20469: p_token3 => 'MASTER_TABLE',
20470: p_token_value3 => 'MSC_ST_UNITS_OF_MEASURE',
20471: p_token4 => 'CHILD_TABLE',
20472: p_token_value4 => 'MSC_ST_UOM_CLASS_CONVERSIONS');
20473: IF lv_return <> 0 THEN
20474: RAISE ex_logging_err;
20475: END IF;
20476:
20475: END IF;
20476:
20477: v_sql_stmt := 19;
20478: lv_sql_stmt :=
20479: 'UPDATE msc_st_uom_class_conversions mcc '
20480: ||' SET process_flag = '||G_ERROR_FLG||','
20481: ||' error_text = '||''''||lv_message_text||''''
20482: ||' WHERE NOT EXISTS(SELECT 1'
20483: ||' FROM msc_units_of_measure mum'
20516: ||' AND TO_UNIT_OF_MEASURE',
20517: p_token2 => 'MASTER_TABLE',
20518: p_token_value2 => 'MSC_ST_UNITS_OF_MEASURE',
20519: p_token3 => 'CHILD_TABLE',
20520: p_token_value3 => 'MSC_ST_UOM_CLASS_CONVERSIONS');
20521: IF lv_return <> 0 THEN
20522: RAISE ex_logging_err;
20523: END IF;
20524:
20523: END IF;
20524:
20525: v_sql_stmt := 20;
20526: lv_sql_stmt :=
20527: 'UPDATE msc_st_uom_class_conversions mcc '
20528: ||' SET process_flag = '||G_ERROR_FLG||','
20529: ||' error_text = '||''''||lv_message_text||''''
20530: ||' WHERE NOT EXISTS(SELECT 1'
20531: ||' FROM msc_units_of_measure mum '
20566: END IF;
20567:
20568: v_sql_stmt := 21;
20569: lv_sql_stmt :=
20570: ' UPDATE msc_st_uom_class_conversions'
20571: ||' SET process_flag = '||G_ERROR_FLG||', '
20572: ||' error_text = '||''''||lv_message_text||''''
20573: ||' WHERE NVL(conversion_rate,'||NULL_VALUE||') <= 0'
20574: ||' AND process_flag = '||G_IN_PROCESS
20597:
20598: --Org code is mandatory where item name is not null
20599: v_sql_stmt := 22;
20600: lv_sql_stmt :=
20601: ' UPDATE msc_st_uom_class_conversions'
20602: ||' SET process_flag = '||G_ERROR_FLG||', '
20603: ||' error_text = '||''''||lv_message_text||''''
20604: ||' WHERE NVL(item_name, '||''''||NULL_CHAR||''''||')'
20605: ||' = '||''''||NULL_CHAR||''''
20629: END IF;
20630:
20631: --Derive Organization_id
20632: lv_return := MSC_ST_UTIL.DERIVE_PARTNER_ORG_ID
20633: (p_table_name => 'MSC_ST_UOM_CLASS_CONVERSIONS',
20634: p_org_partner_name => 'ORGANIZATION_CODE',
20635: p_org_partner_id => 'ORGANIZATION_ID',
20636: p_instance_code => v_instance_code,
20637: p_partner_type => G_ORGANIZATION,
20647: END IF; */
20648: v_sql_stmt := 23;
20649: --Update inventory_item_id
20650: lv_sql_stmt :=
20651: 'UPDATE msc_st_uom_class_conversions mcc '
20652: ||' SET inventory_item_id = (SELECT local_id'
20653: ||' FROM msc_local_id_item mli'
20654: ||' WHERE mli.char1 = mcc.sr_instance_code '
20655: ||' AND NVL(mli.char2, '||''''||NULL_CHAR||''''||')='
20684: RAISE ex_logging_err;
20685: END IF;
20686: v_sql_stmt := 24;
20687: lv_sql_stmt :=
20688: 'UPDATE msc_st_uom_class_conversions '
20689: ||' SET error_text = '||''''||lv_message_text||''''||','
20690: ||' process_flag = '||g_error
20691: ||' WHERE inventory_item_id IS NULL '
20692: ||' AND item_name IS NOT NULL '
20707: (ERRBUF => lv_error_text,
20708: RETCODE => lv_return,
20709: pBatchID => lv_batch_id,
20710: pInstanceCode => v_instance_code,
20711: pEntityName => 'MSC_ST_UOM_CLASS_CONVERSIONS',
20712: pInstanceID => v_instance_id);
20713:
20714: IF NVL(lv_return,0) <> 0 THEN
20715: RAISE ex_logging_err;
20717:
20718: v_sql_stmt := 25;
20719: --Fixed against the bug#2734588.
20720:
20721: UPDATE msc_st_uom_class_conversions
20722: SET inventory_item_id = 0
20723: WHERE item_name IS NULL
20724: AND process_flag = G_IN_PROCESS
20725: AND batch_id = lv_batch_id
20725: AND batch_id = lv_batch_id
20726: AND sr_instance_code = v_instance_code;
20727:
20728: lv_return := MSC_ST_UTIL.SET_PROCESS_FLAG
20729: (p_table_name => 'MSC_ST_UOM_CLASS_CONVERSIONS',
20730: p_instance_id => v_instance_id,
20731: p_instance_code => v_instance_code,
20732: p_process_flag => G_VALID,
20733: p_error_text => lv_error_text,
20738: RAISE ex_logging_err;
20739: END IF;
20740:
20741: lv_return := MSC_ST_UTIL.LOG_ERROR
20742: (p_table_name => 'MSC_ST_UOM_CLASS_CONVERSIONS',
20743: p_instance_code => v_instance_code,
20744: p_row => lv_column_names,
20745: p_severity => G_SEV_ERROR,
20746: p_message_text => NULL,
20754:
20755: COMMIT;
20756: END LOOP;
20757:
20758: --Validation check for the table msc_st_uom_class_conversions.
20759:
20760: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
20761: (p_app_short_name => 'MSC',
20762: p_error_code => 'MSC_PP_DUP_REC_FOR_XML',
21211: RAISE ex_logging_err;
21212: END IF;
21213:
21214: lv_sql_stmt :=
21215: 'UPDATE msc_st_uom_class_conversions '
21216: ||' SET error_text = '||''''||lv_message_text||''''||','
21217: ||' process_flag = '||g_error
21218: ||' WHERE inventory_item_id IS NULL '
21219: ||' AND item_name IS NOT NULL '
55988: End IF;
55989: END IF;
55990:
55991: IF v_uom_class_enabled = SYS_YES THEN
55992: lv_count:= SET_IN_PROCESS (p_table_name => 'MSC_ST_UOM_CLASS_CONVERSIONS');
55993: IF lv_count > 0 Then
55994: prec.uom_flag:= SYS_YES;
55995: End IF;
55996: lv_count:= SET_IN_PROCESS (p_table_name => 'MSC_ST_UOM_CONVERSIONS');