[Home] [Help]
38: WHERE organization_id = arg_org_id
39: AND sr_instance_id = arg_instance_id;
40:
41: -- Select the calendar code and exception set id for this org
42: msc_calendar.select_calendar_defaults(
43: arg_org_id,
44: arg_instance_id,
45: var_calendar_code,
46: var_exception_set_id);
47:
48: -- Get the last date in this calendar
49: SELECT MAX(calendar_date)
50: INTO var_max_date
51: FROM msc_calendar_dates
52: WHERE calendar_code = var_calendar_code
53: AND exception_set_id = var_exception_set_id
54: AND seq_num IS NOT NULL;
55:
61:
62: -- Calculate the end date for each bucket
63: IF param.period_type = WORKDATE_PERIODS THEN
64: -- Set the end dates according to workdates
65: var_end_date1 := msc_calendar.date_offset(
66: arg_org_id,
67: arg_instance_id,
68: BUCKET_TYPE,
69: param.repetitive_anchor_date,
68: BUCKET_TYPE,
69: param.repetitive_anchor_date,
70: param.repetitive_horizon1);
71:
72: var_end_date2 := msc_calendar.date_offset(
73: arg_org_id,
74: arg_instance_id,
75: BUCKET_TYPE,
76: var_end_date1,
75: BUCKET_TYPE,
76: var_end_date1,
77: param.repetitive_horizon2);
78:
79: var_last_date1 := msc_calendar.date_offset(
80: arg_org_id,
81: arg_instance_id,
82: BUCKET_TYPE,
83: var_max_date,
82: BUCKET_TYPE,
83: var_max_date,
84: -1 * param.repetitive_bucket_size1);
85:
86: var_last_date2 := msc_calendar.date_offset(
87: arg_org_id,
88: arg_instance_id,
89: BUCKET_TYPE,
90: var_max_date,
89: BUCKET_TYPE,
90: var_max_date,
91: -1 * param.repetitive_bucket_size2);
92:
93: var_last_date3 := msc_calendar.date_offset(
94: arg_org_id,
95: arg_instance_id,
96: BUCKET_TYPE,
97: var_max_date,
113: -- Get the current workday
114:
115: SELECT next_date
116: INTO var_curr_workday
117: FROM msc_calendar_dates
118: WHERE calendar_code = var_calendar_code
119: AND exception_set_id = var_exception_set_id
120: AND calendar_date = var_curr_date
121: AND sr_instance_id = arg_instance_id;
150: -- If that brings you to the next bucket type, then shift the
151: -- variable var_curr_bucket
152: IF var_curr_bucket = 1 THEN -- First bucket
153: IF param.period_type = WORKDATE_PERIODS THEN
154: var_curr_date := msc_calendar.date_offset(
155: arg_org_id,
156: arg_instance_id,
157: BUCKET_TYPE,
158: var_curr_date,
165: var_curr_bucket := 2;
166: END IF;
167: ELSIF var_curr_bucket = 2 THEN -- Second bucket
168: IF param.period_type = WORKDATE_PERIODS THEN
169: var_curr_date := msc_calendar.date_offset(
170: arg_org_id,
171: arg_instance_id,
172: BUCKET_TYPE,
173: var_curr_date,
180: var_curr_bucket := 3;
181: END IF;
182: ELSE
183: IF param.period_type = WORKDATE_PERIODS THEN
184: var_curr_date := msc_calendar.date_offset(
185: arg_org_id,
186: arg_instance_id,
187: BUCKET_TYPE,
188: var_curr_date,
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
216: AND exception_set_id = var_exception_set_id
217: AND calendar_date = s.period_start_date)
218: WHERE EXISTS
216: AND exception_set_id = var_exception_set_id
217: AND calendar_date = s.period_start_date)
218: WHERE EXISTS
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
235: WHERE organization_id = arg_org_id
236: AND sr_instance_id = arg_instance_id
237: AND EXISTS
238: (SELECT NULL
239: FROM msc_calendar_dates d
240: WHERE seq_num IS NULL
241: AND calendar_code = var_calendar_code
242: AND exception_set_id = var_exception_set_id
243: AND calendar_date = s.period_start_date);