[Home] [Help]
3449: p_transaction_id,
3450: v_instance_code,
3451: p_calendar_code;
3452:
3453: ELSIF p_table_name = 'MSC_ST_SHIFT_TIMES' THEN
3454:
3455: v_sql_stmt := 3;
3456: lv_sql_stmt :=
3457: 'UPDATE msc_st_shift_times'
3453: ELSIF p_table_name = 'MSC_ST_SHIFT_TIMES' THEN
3454:
3455: v_sql_stmt := 3;
3456: lv_sql_stmt :=
3457: 'UPDATE msc_st_shift_times'
3458: ||' set process_flag = '||G_ERROR_FLG||','
3459: ||' error_text = :p_message_text'
3460: ||' WHERE st_transaction_id = :p_transaction_id'
3461: ||' AND sr_instance_code = :instance_code'
3559: p_calendar_code;
3560:
3561: v_sql_stmt := 9;
3562: lv_sql_stmt :=
3563: 'UPDATE msc_st_shift_times'
3564: ||' set process_flag = '||G_PROPAGATION
3565: ||' WHERE sr_instance_code = :instance_code'
3566: ||' AND process_flag = '||G_IN_PROCESS
3567: ||' AND calendar_code = :calendar_code';
3624: /*==========================================================================+
3625: | DESCRIPTION : This function inserts a record into the LID table for the |
3626: | new shifts, derives the shift information from the |
3627: | workday pattern table and updates the shift_num in the |
3628: | tables msc_st_shift_exceptions and msc_st_shift_times |
3629: +==========================================================================*/
3630: FUNCTION derive_shift_details
3631: (p_transaction_id NUMBER,
3632: p_company_name VARCHAR2,
3702: v_instance_code;
3703:
3704: v_sql_stmt := 4;
3705: lv_sql_stmt :=
3706: 'UPDATE msc_st_shift_times '
3707: ||' SET shift_num = :lv_shift_num'
3708: ||' WHERE calendar_code = :p_calendar_code'
3709: ||' AND shift_name = :p_shift_name'
3710: ||' AND process_flag = '||G_IN_PROCESS
3854: shift_num,
3855: from_time,
3856: to_time,
3857: deleted_flag
3858: FROM msc_st_shift_times
3859: WHERE sr_instance_code = v_instance_code
3860: AND calendar_code = p_calendar_code
3861: AND process_flag = G_IN_PROCESS;
3862:
4476:
4477: --Validation for shift times table.
4478: FOR rec3 IN c3(rec1.calendar_code)
4479: LOOP
4480: lv_table_name := 'MSC_ST_SHIFT_TIMES';
4481: lv_column_names :=
4482: 'CALENDAR_CODE ||''~''||'
4483: ||'SHIFT_NAME ||''~''||'
4484: ||'SHIFT_NUM ||''~''||'
4488: ||'COMPANY_NAME ||''~''||'
4489: ||'DELETED_FLAG';
4490:
4491: v_sql_stmt := 13;
4492: SELECT msc_st_shift_times_s.NEXTVAL
4493: INTO lv_transaction_id
4494: FROM dual;
4495:
4496: v_sql_stmt := 14;
4493: INTO lv_transaction_id
4494: FROM dual;
4495:
4496: v_sql_stmt := 14;
4497: UPDATE msc_st_shift_times
4498: SET st_transaction_id = lv_transaction_id,
4499: refresh_id = v_refresh_id,
4500: last_update_date = v_current_date,
4501: last_updated_by = v_current_user,
4547:
4548: --Logging a warning for those records where the deleted_flag value
4549: --is other than Yes/No.
4550: lv_return := MSC_ST_UTIL.LOG_ERROR
4551: (p_table_name => 'MSC_ST_SHIFT_TIMES',
4552: p_instance_code => v_instance_code,
4553: p_row => lv_column_names,
4554: p_severity => G_SEV_WARNING,
4555: p_message_text => lv_message_text,
4569: lv_sql_stmt :=
4570: 'SELECT 1'
4571: ||' FROM DUAL'
4572: ||' WHERE NOT EXISTS(SELECT 1'
4573: ||' FROM msc_st_shift_times'
4574: ||' WHERE sr_instance_code = :sr_instance_code'
4575: ||' AND process_flag = '||G_IN_PROCESS
4576: ||' AND deleted_flag = '||SYS_NO
4577: ||' AND calendar_code = :calendar_code'
4580: ||' AND to_time = :to_time'
4581: ||' AND st_transaction_id <> :lv_transaction_id'
4582: ||' UNION'
4583: ||' SELECT 1'
4584: ||' FROM msc_st_shift_times'
4585: ||' WHERE sr_instance_code = :sr_instance_code'
4586: ||' AND process_flag = '||G_IN_PROCESS
4587: ||' AND deleted_flag = '||SYS_NO
4588: ||' AND calendar_code = :calendar_code'
4591: ||' AND from_time < :to_time'
4592: ||' AND st_transaction_id <> :lv_transaction_id'
4593: ||' UNION'
4594: ||' SELECT 1'
4595: ||' FROM msc_st_shift_times'
4596: ||' WHERE sr_instance_code = :sr_instance_code'
4597: ||' AND process_flag = '||G_IN_PROCESS
4598: ||' AND deleted_flag = '||SYS_NO
4599: ||' AND calendar_code = :calendar_code'
4643: (ERRBUF => lv_error_text,
4644: RETCODE => lv_return,
4645: pBatchID => null,
4646: pInstanceCode => v_instance_code,
4647: pEntityName => 'MSC_ST_SHIFT_TIMES',
4648: pInstanceID => v_instance_id);
4649:
4650: IF lv_return <> 0 THEN
4651: RAISE ex_logging_err;
5188: RAISE ex_logging_err;
5189: END IF;
5190:
5191: lv_return := MSC_ST_UTIL.SET_PROCESS_FLAG
5192: (p_table_name => 'MSC_ST_SHIFT_TIMES',
5193: p_instance_id => v_instance_id,
5194: p_instance_code => v_instance_code,
5195: p_process_flag => G_VALID,
5196: p_where_str => lv_where_str,
5533: ||' request_id,'
5534: ||' program_application_id,'
5535: ||' program_id,'
5536: ||' program_update_date'
5537: ||' FROM msc_st_shift_times '
5538: ||' WHERE calendar_code = :calendar_code'
5539: ||' AND sr_instance_code = :sr_instance_code'
5540: ||' AND process_flag = '||G_VALID;
5541:
5618:
5619: v_sql_stmt := 40;
5620:
5621: lv_sql_stmt :=
5622: ' DELETE FROM msc_st_shift_times '
5623: ||' WHERE calendar_code = :calendar_code'
5624: ||' AND sr_instance_code = :sr_instance_code'
5625: ||' AND process_flag = '||G_VALID;
5626:
50936: IF v_cal_enabled = SYS_YES THEN
50937: lv_count:= SET_IN_PROCESS (p_table_name => 'MSC_ST_CALENDARS');
50938: lv_count:= SET_IN_PROCESS (p_table_name => 'MSC_ST_WORKDAY_PATTERNS');
50939: lv_count:= SET_IN_PROCESS (p_table_name => 'MSC_ST_CALENDAR_SHIFTS');
50940: lv_count:= SET_IN_PROCESS (p_table_name => 'MSC_ST_SHIFT_TIMES');
50941: lv_count:= SET_IN_PROCESS (p_table_name => 'MSC_ST_SHIFT_EXCEPTIONS');
50942: lv_count:= SET_IN_PROCESS (p_table_name => 'MSC_ST_CALENDAR_EXCEPTIONS');
50943: END IF;
50944: