[Home] [Help]
13591: lv_error_text VARCHAR2(250);
13592: lv_where_str VARCHAR2(5000);
13593: lv_sql_stmt VARCHAR2(5000);
13594: lv_column_names VARCHAR2(5000); --stores concatenated column names
13595: lv_batch_id msc_st_wo_attributes.batch_id%TYPE;
13596: lv_message_text msc_errors.error_text%TYPE;
13597:
13598: ex_logging_err EXCEPTION;
13599:
13598: ex_logging_err EXCEPTION;
13599:
13600: CURSOR c1(p_batch_id NUMBER) IS
13601: SELECT rowid
13602: FROM msc_st_wo_attributes
13603: WHERE process_flag IN (G_IN_PROCESS,G_ERROR_FLG)
13604: AND batch_id = p_batch_id
13605: AND sr_instance_code = v_instance_code;
13606:
13616: IF lv_return <> 0 THEN
13617: RAISE ex_logging_err;
13618: END IF;
13619:
13620: --Validation check for the table msc_st_wo_attributes
13621:
13622: --Duplicate records check for the records whose source is other than XML
13623: --Different SQL is used because in XML we can identify the latest records
13624: --whereas in batch load we cannot.
13624: --whereas in batch load we cannot.
13625:
13626: v_sql_stmt := 01;
13627: lv_sql_stmt :=
13628: 'UPDATE msc_st_wo_attributes mswoa1 '
13629: ||' SET process_flag = '||G_ERROR_FLG||','
13630: ||' error_text = '||''''||lv_message_text||''''
13631: ||' WHERE EXISTS ( SELECT 1 FROM msc_st_wo_attributes mswoa2 '
13632: ||' WHERE mswoa2.sr_instance_code = mswoa1.sr_instance_code'
13627: lv_sql_stmt :=
13628: 'UPDATE msc_st_wo_attributes mswoa1 '
13629: ||' SET process_flag = '||G_ERROR_FLG||','
13630: ||' error_text = '||''''||lv_message_text||''''
13631: ||' WHERE EXISTS ( SELECT 1 FROM msc_st_wo_attributes mswoa2 '
13632: ||' WHERE mswoa2.sr_instance_code = mswoa1.sr_instance_code'
13633: ||' AND mswoa2.wip_entity_code = mswoa1.wip_entity_code'
13634: ||' AND mswoa2.organization_code = mswoa1.organization_code'
13635: ||' AND mswoa2.visit_name = mswoa1.visit_name'
13654:
13655: --Duplicate records check for the records whose source is XML
13656: v_sql_stmt := 02;
13657: lv_sql_stmt :=
13658: 'UPDATE msc_st_wo_attributes mswoa1 '
13659: ||' SET process_flag = '||G_ERROR_FLG||','
13660: ||' error_text = '||''''||lv_message_text||''''
13661: ||' WHERE message_id < ( SELECT max(message_id) '
13662: ||' FROM msc_st_wo_attributes mswoa2'
13658: 'UPDATE msc_st_wo_attributes mswoa1 '
13659: ||' SET process_flag = '||G_ERROR_FLG||','
13660: ||' error_text = '||''''||lv_message_text||''''
13661: ||' WHERE message_id < ( SELECT max(message_id) '
13662: ||' FROM msc_st_wo_attributes mswoa2'
13663: ||' WHERE mswoa2.sr_instance_code = mswoa1.sr_instance_code'
13664: ||' AND mswoa2.wip_entity_code = mswoa1.wip_entity_code'
13665: ||' AND mswoa2.organization_code = mswoa1.organization_code'
13666: ||' AND mswoa2.visit_name = mswoa1.visit_name'
13709: FROM dual;
13710:
13711: v_sql_stmt := 04;
13712: lv_sql_stmt :=
13713: 'UPDATE msc_st_wo_attributes '
13714: ||' SET batch_id = :lv_batch_id'
13715: ||' WHERE process_flag IN ('||G_IN_PROCESS||','||G_ERROR_FLG||')'
13716: ||' AND sr_instance_code = :v_instance_code'
13717: ||' AND NVL(batch_id,'||NULL_VALUE||') = '||NULL_VALUE
13729: FETCH c1 BULK COLLECT INTO lb_rowid;
13730: CLOSE c1;
13731:
13732: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
13733: UPDATE msc_st_wo_attributes
13734: SET st_transaction_id = msc_st_wo_attributes_s.nextval,
13735: refresh_id = v_refresh_id,
13736: last_update_date = v_current_date,
13737: last_updated_by = v_current_user,
13730: CLOSE c1;
13731:
13732: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
13733: UPDATE msc_st_wo_attributes
13734: SET st_transaction_id = msc_st_wo_attributes_s.nextval,
13735: refresh_id = v_refresh_id,
13736: last_update_date = v_current_date,
13737: last_updated_by = v_current_user,
13738: creation_date = v_current_date,
13759: ||' NOT IN(1,2)';
13760: --Log a warning for those records where the deleted_flag has a value other
13761: --than SYS_NO
13762: lv_return := MSC_ST_UTIL.LOG_ERROR
13763: (p_table_name => 'MSC_ST_WO_ATTRIBUTES',
13764: p_instance_code => v_instance_code,
13765: p_row => lv_column_names,
13766: p_severity => G_SEV_WARNING,
13767: p_message_text => lv_message_text,
13791:
13792: -- Error out records where milestone or visit_name is NULL
13793: v_sql_stmt := 06;
13794: lv_sql_stmt :=
13795: 'UPDATE msc_st_wo_attributes'
13796: ||' SET process_flag = '||G_ERROR_FLG||','
13797: ||' error_text = '||''''||lv_message_text||''''
13798: ||' WHERE ( VISIT_NAME IS NULL'
13799: ||' OR WIP_ENTITY_CODE IS NULL )'
13824:
13825: -- Populate organization id
13826:
13827: lv_return :=
13828: MSC_ST_UTIL.DERIVE_PARTNER_ORG_ID(p_table_name => 'MSC_ST_WO_ATTRIBUTES',
13829: p_org_partner_name => 'ORGANIZATION_CODE',
13830: p_org_partner_id => 'ORGANIZATION_ID',
13831: p_instance_code => v_instance_code,
13832: p_partner_type => G_ORGANIZATION,
13861: lv_where_str := ' AND NVL(PRODUCES_TO_STOCK,'||NULL_VALUE||') '
13862: ||' NOT IN(1,2)';
13863:
13864: lv_return := MSC_ST_UTIL.LOG_ERROR
13865: (p_table_name => 'MSC_ST_WO_ATTRIBUTES',
13866: p_instance_code => v_instance_code,
13867: p_row => lv_column_names,
13868: p_severity => G_SEV_WARNING,
13869: p_message_text => lv_message_text,
13896:
13897: v_sql_stmt := 07;
13898: lv_sql_stmt :=
13899:
13900: ' UPDATE msc_st_wo_attributes '
13901: ||' SET process_flag = '||G_ERROR_FLG||','
13902: ||' error_text = '||''''||lv_message_text||''''
13903: ||' WHERE nvl(MASTER_WO,'||NULL_VALUE||') not in(1,2)'
13904: ||' AND process_flag = '||G_IN_PROCESS
13914:
13915: --Deriving visit_id
13916: v_sql_stmt := 08;
13917: lv_sql_stmt :=
13918: 'UPDATE msc_st_wo_attributes mswa'
13919: ||' SET visit_id = (SELECT local_id'
13920: ||' FROM msc_local_id_setup mls'
13921: ||' WHERE mls.char1 = mswa.sr_instance_code'
13922: ||' AND mls.char4 = mswa.visit_name'
13943:
13944: -- Validate the visit name with data in msc_local_id_setup
13945: v_sql_stmt := 09;
13946: lv_sql_stmt :=
13947: 'UPDATE msc_st_wo_attributes '
13948: ||' SET process_flag = '||G_ERROR_FLG||','
13949: ||' error_text = '||''''||lv_message_text||''''
13950: ||' WHERE VISIT_ID IS NULL'
13951: ||' AND process_flag = '||G_IN_PROCESS
13976:
13977: -- Validate
13978: v_sql_stmt := 10;
13979: lv_sql_stmt :=
13980: 'UPDATE msc_st_wo_attributes mswa'
13981: ||' SET process_flag = '||G_ERROR_FLG||','
13982: ||' error_text = '||''''||lv_message_text||''''
13983: ||' WHERE NOT EXISTS(SELECT 1 '
13984: ||' FROM msc_wo_milestones mwm '
14041:
14042: -- Validate
14043: v_sql_stmt := 11;
14044: lv_sql_stmt :=
14045: 'UPDATE msc_st_wo_attributes mswa'
14046: ||' SET process_flag = '||G_ERROR_FLG||','
14047: ||' error_text = '||''''||lv_message_text||''''
14048: ||' WHERE NOT EXISTS(SELECT 1 '
14049: ||' FROM msc_wo_milestones mwm '
14076: lv_batch_id;
14077: v_sql_stmt := 12;
14078: lv_sql_stmt :=
14079:
14080: ' UPDATE msc_st_wo_attributes '
14081: ||' SET process_flag = '||G_ERROR_FLG||','
14082: ||' error_text = '||''''||lv_message_text||''''
14083: ||' WHERE nvl(MASTER_WO,'||NULL_VALUE||') not in(1,2)'
14084: ||' AND process_flag = '||G_IN_PROCESS
14094:
14095: --Deriving supply_id
14096: v_sql_stmt := 13;
14097: lv_sql_stmt :=
14098: 'UPDATE msc_st_wo_attributes mswa'
14099: ||' SET supply_id = (SELECT transaction_id'
14100: ||' FROM msc_supplies ms'
14101: ||' WHERE ms.sr_instance_id = :v_instance_id'
14102: ||' AND ms.order_number = mswa.wip_entity_code'
14123: p_token_value1 => 'WIP_ENTITY_CODE');
14124:
14125: v_sql_stmt := 14;
14126: lv_sql_stmt :=
14127: 'UPDATE msc_st_wo_attributes '
14128: ||' SET process_flag = '||G_ERROR_FLG||','
14129: ||' error_text = '||''''||lv_message_text||''''
14130: ||' WHERE SUPPLY_ID IS NULL'
14131: ||' AND process_flag = '||G_IN_PROCESS
14146: (ERRBUF => lv_error_text,
14147: RETCODE => lv_return,
14148: pBatchID => NULL,
14149: pInstanceCode => v_instance_code,
14150: pEntityName => 'MSC_ST_WO_ATTRIBUTES',
14151: pInstanceID => v_instance_id);
14152:
14153: IF NVL(lv_return,0) <> 0 THEN
14154: RAISE ex_logging_err;
14154: RAISE ex_logging_err;
14155: END IF;
14156: -- Set the process flag as Valid and populate instance_id
14157: lv_return := MSC_ST_UTIL.SET_PROCESS_FLAG
14158: (p_table_name => 'MSC_ST_WO_ATTRIBUTES',
14159: p_instance_id => v_instance_id,
14160: p_instance_code => v_instance_code,
14161: p_process_flag => G_VALID,
14162: p_error_text => lv_error_text,
14169: -- At the end calling the LOG_ERROR for logging all
14170: -- errored out records.
14171:
14172: lv_return := MSC_ST_UTIL.LOG_ERROR
14173: (p_table_name => 'MSC_ST_WO_ATTRIBUTES',
14174: p_instance_code => v_instance_code,
14175: p_row => lv_column_names,
14176: p_severity => G_SEV_ERROR,
14177: p_message_text => NULL,
56080: IF lv_count > 0 Then
56081: prec.CMRO_flag:= SYS_YES;
56082: End IF;
56083:
56084: lv_count:= SET_IN_PROCESS (p_table_name => 'MSC_ST_WO_ATTRIBUTES', p_company_name_col => FALSE, p_erp_enabled => 'Y');
56085: IF lv_count > 0 Then
56086: prec.CMRO_flag:= SYS_YES;
56087: End IF;
56088: