[Home] [Help]
27: VERSION CONSTANT CHAR(80) :=
28: '$Header: MSCPRPDB.pls 120.2 2007/10/16 17:06:24 navgoel ship $';
29: BEGIN
30:
31: -- Lock table msc_repetitive_periods before insert/delete/update
32: LOCK TABLE msc_repetitive_periods IN EXCLUSIVE MODE;
33:
34: -- Select the organization information
35: SELECT *
28: '$Header: MSCPRPDB.pls 120.2 2007/10/16 17:06:24 navgoel ship $';
29: BEGIN
30:
31: -- Lock table msc_repetitive_periods before insert/delete/update
32: LOCK TABLE msc_repetitive_periods IN EXCLUSIVE MODE;
33:
34: -- Select the organization information
35: SELECT *
36: INTO param
52: WHERE calendar_code = var_calendar_code
53: AND exception_set_id = var_exception_set_id
54: AND seq_num IS NOT NULL;
55:
56: DELETE FROM msc_repetitive_periods
57: WHERE organization_id = arg_org_id
58: AND sr_instance_id = arg_instance_id;
59:
60: var_curr_date := param.repetitive_anchor_date;
107: END IF;
108:
109: WHILE TRUE LOOP
110: -- Keep looping, until you reach the end of the calendar...for each
111: -- period, insert into the table msc_repetitive_periods
112:
113: -- Get the current workday
114:
115: SELECT next_date
120: AND calendar_date = var_curr_date
121: AND sr_instance_id = arg_instance_id;
122:
123: IF var_curr_workday <> var_prev_workday THEN
124: INSERT INTO msc_repetitive_periods
125: (
126: period_start_date,
127: organization_id,
128: sr_instance_id,
207: -- We want to adjust all non-workday periods to the next valid workday.
208: -- However, we want to make sure that this does not result in duplicates.
209: -- Therefore only update those where a row does not exist for the next
210: -- workday. We'll delete those that are not updated in the next step.
211: UPDATE msc_repetitive_periods s
212: SET period_start_date =
213: (SELECT next_date
214: FROM msc_calendar_dates
215: WHERE calendar_code = var_calendar_code
219: (SELECT NULL
220: FROM msc_calendar_dates d
221: WHERE NOT EXISTS
222: (SELECT NULL
223: FROM msc_repetitive_periods
224: WHERE organization_id = s.organization_id
225: AND sr_instance_id = s.sr_instance_id
226: AND period_start_date = d.next_date)
227: AND seq_num IS NULL
230: AND calendar_date = s.period_start_date)
231: AND sr_instance_id = arg_instance_id
232: AND organization_id = arg_org_id;
233:
234: DELETE FROM msc_repetitive_periods s
235: WHERE organization_id = arg_org_id
236: AND sr_instance_id = arg_instance_id
237: AND EXISTS
238: (SELECT NULL