[Home] [Help]
3429: p_transaction_id,
3430: v_instance_code,
3431: p_calendar_code;
3432:
3433: ELSIF p_table_name = 'MSC_ST_WORKDAY_PATTERNS' THEN
3434:
3435: v_sql_stmt := 2;
3436: lv_sql_stmt :=
3437: 'UPDATE msc_st_workday_patterns'
3433: ELSIF p_table_name = 'MSC_ST_WORKDAY_PATTERNS' THEN
3434:
3435: v_sql_stmt := 2;
3436: lv_sql_stmt :=
3437: 'UPDATE msc_st_workday_patterns'
3438: ||' set process_flag = '||G_ERROR_FLG||','
3439: ||' error_text = :p_message_text'
3440: ||' WHERE st_transaction_id = :p_transaction_id'
3441: ||' AND sr_instance_code = :instance_code'
3531: END IF;
3532:
3533: v_sql_stmt := 7;
3534: lv_sql_stmt :=
3535: 'UPDATE msc_st_workday_patterns'
3536: ||' set process_flag = '||G_PROPAGATION
3537: ||' WHERE sr_instance_code = :instance_code'
3538: ||' AND process_flag = '||G_IN_PROCESS
3539: ||' AND calendar_code = :calendar_code';
3688: END IF;
3689:
3690: v_sql_stmt := 3;
3691: lv_sql_stmt :=
3692: 'UPDATE msc_st_workday_patterns'
3693: ||' SET shift_num = :lv_shift_num'
3694: ||' WHERE st_transaction_id = :p_transaction_id'
3695: ||' AND process_flag = '||G_IN_PROCESS
3696: ||' AND sr_instance_code = :v_instance_code';
3762: ||' created_by,'
3763: ||' message_id,'
3764: ||' process_flag,'
3765: ||' data_source_type'
3766: ||' FROM msc_st_workday_patterns mwp'
3767: ||' WHERE st_transaction_id = :p_transaction_id'
3768: ||' AND process_flag = '||G_IN_PROCESS
3769: ||' AND sr_instance_code = :instance_code'
3770: ||' AND NOT EXISTS( SELECT 1 '
3814: lv_calendar_code msc_st_calendars.calendar_code%TYPE;
3815: lv_transaction_id msc_st_calendars.st_transaction_id%TYPE;
3816: lv_calendar_start_date msc_st_calendars.calendar_start_date%TYPE;
3817: lv_calendar_end_date msc_st_calendars.calendar_end_date%TYPE;
3818: lv_seq_num msc_st_workday_patterns.seq_num%TYPE := 0;
3819: lv_message_text msc_errors.error_text%TYPE;
3820: lv_shift_num msc_st_calendar_shifts.shift_num%TYPE;
3821:
3822: ex_logging_err EXCEPTION;
3842: seq_num,
3843: days_on,
3844: days_off,
3845: deleted_flag
3846: FROM msc_st_workday_patterns
3847: WHERE sr_instance_code = v_instance_code
3848: AND calendar_code = p_calendar_code
3849: AND process_flag = G_IN_PROCESS;
3850:
4173: lv_sql_stmt :=
4174: 'SELECT 1'
4175: ||' FROM msc_calendar_shifts mcs, msc_apps_instances mai '
4176: ||' WHERE NOT EXISTS(SELECT 1 '
4177: ||' FROM msc_st_workday_patterns mwp '
4178: ||' WHERE mwp.shift_name = mcs.shift_name'
4179: ||' AND mcs.calendar_code = mwp.calendar_code'
4180: ||' AND mwp.sr_instance_code = mai.instance_code'
4181: ||' AND mwp.process_flag = '||G_IN_PROCESS||')'
4223:
4224: v_sql_stmt := 7;
4225: lv_sql_stmt :=
4226: 'SELECT nvl(SUM(nvl(days_on,0) + nvl(days_off,0)),0)'
4227: ||' FROM msc_st_workday_patterns'
4228: ||' WHERE calendar_code = :calendar_code'
4229: ||' AND NVL(shift_name,'||''''||NULL_CHAR||''''||')'
4230: ||' = '||''''||NULL_CHAR||''''
4231: ||' AND deleted_flag <> '||SYS_YES
4290:
4291: SELECT MIN(to_number(decode(length(rtrim(shift_name,'0123456789')),
4292: NULL,shift_name,'0')))
4293: INTO lv_shift_num_gen
4294: FROM msc_st_workday_patterns
4295: WHERE sr_instance_code = v_instance_code
4296: AND calendar_code = rec1.calendar_code
4297: AND process_flag = G_IN_PROCESS
4298: AND shift_name IS NOT NULL;
4299:
4300: --Workday Pattern is validated for a calendar.
4301: FOR rec2 IN c2(rec1.calendar_code)
4302: LOOP
4303: lv_table_name := 'MSC_ST_WORKDAY_PATTERNS';
4304: lv_seq_num := lv_seq_num + 1;
4305:
4306: lv_column_names :=
4307: 'CALENDAR_CODE ||''~''||'
4314: ||'COMPANY_NAME ||''~''||'
4315: ||'DELETED_FLAG';
4316:
4317: v_sql_stmt := 8;
4318: SELECT msc_st_workday_patterns_s.NEXTVAL
4319: INTO lv_transaction_id
4320: FROM dual;
4321:
4322: v_sql_stmt := 9;
4319: INTO lv_transaction_id
4320: FROM dual;
4321:
4322: v_sql_stmt := 9;
4323: UPDATE msc_st_workday_patterns
4324: SET st_transaction_id = lv_transaction_id,
4325: refresh_id = v_refresh_id,
4326: seq_num = lv_seq_num,
4327: last_update_date = v_current_date,
4337: p_error_code => 'MSC_PP_NO_DELETION',
4338: p_message_text => lv_message_text,
4339: p_error_text => lv_error_text,
4340: p_token1 => 'TABLE_NAME',
4341: p_token_value1 => 'MSC_ST_WORKDAY_PATTERNS');
4342:
4343: IF lv_return <> 0 THEN
4344: RAISE ex_logging_err;
4345: END IF;
4379:
4380: --Logging a warning for those records where the deleted_flag value
4381: --is other than Yes/No.
4382: lv_return := MSC_ST_UTIL.LOG_ERROR
4383: (p_table_name => 'MSC_ST_WORKDAY_PATTERNS',
4384: p_instance_code => v_instance_code,
4385: p_row => lv_column_names,
4386: p_severity => G_SEV_WARNING,
4387: p_message_text => lv_message_text,
4401: (ERRBUF => lv_error_text,
4402: RETCODE => lv_return,
4403: pBatchID => null,
4404: pInstanceCode => v_instance_code,
4405: pEntityName => 'MSC_ST_WORKDAY_PATTERNS',
4406: pInstanceID => v_instance_id);
4407:
4408: IF lv_return <> 0 THEN
4409: RAISE ex_logging_err;
5161: RAISE ex_logging_err;
5162: END IF;
5163:
5164: lv_return := MSC_ST_UTIL.SET_PROCESS_FLAG
5165: (p_table_name => 'MSC_ST_WORKDAY_PATTERNS',
5166: p_instance_id => v_instance_id,
5167: p_instance_code => v_instance_code,
5168: p_process_flag => G_VALID,
5169: p_where_str => lv_where_str,
5367: ||' program_application_id,'
5368: ||' program_id,'
5369: ||' program_update_date,'
5370: ||' refresh_id'
5371: ||' FROM msc_st_workday_patterns'
5372: ||' WHERE calendar_code = :calendar_code'
5373: ||' AND sr_instance_code = :sr_instance_code'
5374: ||' AND process_flag = '||G_VALID;
5375:
50934: END IF;
50935:
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');