[Home] [Help]
216: c_start_period VARCHAR2(15);
217:
218: CURSOR get_future_period_this_run IS
219: SELECT period_name
220: FROM gl_period_statuses
221: WHERE application_id = 101
222: AND ledger_id = x_ledger_id
223: AND adjustment_period_flag = 'N'
224: AND effective_period_num >= c_start_period_num
225: ORDER BY effective_period_num ASC;
226:
227: CURSOR get_past_period_this_run IS
228: SELECT period_name
229: FROM gl_period_statuses
230: WHERE application_id = 101
231: AND ledger_id = x_ledger_id
232: AND adjustment_period_flag = 'N'
233: AND effective_period_num <= c_start_period_num
242: error_code := -11;
243:
244: SELECT effective_period_num
245: INTO l_eff_period_num
246: FROM gl_period_statuses p
247: WHERE p.application_id = 101
248: AND p.ledger_id = x_ledger_id
249: AND p.period_name = x_period_last_run
250: AND p.adjustment_period_flag = 'N';
250: AND p.adjustment_period_flag = 'N';
251:
252: -- Get period of last start run date
253: -- We add 0.99998843 to the end_date because the dates are stored with their
254: -- timestamp information truncated in GL_PERIOD_STATUSES. This means that
255: -- a daily period will have exactly the same start date/time and end
256: -- date/time. Since the scheduling feature is extremely time sensitive,
257: -- we need to add the timestamp while retrieving the value from the table.
258: -- 0.99998843 stands for 23 Hours, 59 Minutes and 59 seconds.
259: error_code := -12;
260:
261: SELECT effective_period_num, period_name
262: INTO l_start_period_num, dummy
263: FROM gl_period_statuses p
264: WHERE p.application_id = 101
265: AND p.ledger_id = x_ledger_id
266: AND x_start_date_last_run BETWEEN p.start_date
267: AND p.end_date
272: -- dbms_output.put_line('last start num = '||to_char(l_start_period_num));
273:
274: -- Get period of current start run date
275: -- We add 0.99998843 to the end_date because the dates are stored with their
276: -- timestamp information truncated in GL_PERIOD_STATUSES. This means that
277: -- a daily period will have exactly the same start date/time and end
278: -- date/time. Since the scheduling feature is extremely time sensitive,
279: -- we need to add the timestamp while retrieving the value from the table.
280: -- 0.99998843 stands for 23 Hours, 59 Minutes and 59 seconds.
281: error_code := -13;
282:
283: SELECT effective_period_num, period_name
284: INTO c_start_period_num, c_start_period
285: FROM gl_period_statuses p
286: WHERE p.application_id = 101
287: AND p.ledger_id = x_ledger_id
288: AND x_start_date_this_run BETWEEN p.start_date
289: AND p.end_date
301: RETURN (1);
302: ELSIF l_eff_period_num > l_start_period_num THEN
303: SELECT COUNT(*)
304: INTO period_offset
305: FROM gl_period_statuses p
306: WHERE p.application_id = 101
307: AND p.ledger_id = x_ledger_id
308: AND p.effective_period_num BETWEEN l_start_period_num
309: AND l_eff_period_num
325: CLOSE get_future_period_this_run;
326: ELSE
327: SELECT COUNT(*)
328: INTO period_offset
329: FROM gl_period_statuses p
330: WHERE p.application_id = 101
331: AND p.ledger_id = x_ledger_id
332: AND p.effective_period_num BETWEEN l_eff_period_num
333: AND l_start_period_num
462: error_code := -200;
463:
464: SELECT start_date
465: INTO v_pstart_date
466: FROM gl_period_statuses
467: WHERE application_id = 101
468: AND ledger_id = x_ledger_id
469: AND period_name = v_period;
470:
1216: IF (x_period_flag = 'Y') THEN
1217: BEGIN
1218: SELECT period_name
1219: INTO c_start_period
1220: FROM gl_period_statuses p
1221: WHERE p.application_id = 101
1222: AND p.ledger_id = x_ledger_id
1223: AND c_date BETWEEN p.start_date
1224: AND p.end_date + 0.99998843