247: l_dimension_name pay_balance_dimensions.dimension_name%type;
248: l_business_group_id pay_payroll_actions.business_group_id%type;
249: --
250: begin
251: hr_utility.set_location('Entering: asg_span_ec', 1);
252: l_dimension_name := upper(p_dimension_name);
253: -- Get the year component of the input date
254: hr_utility.trace(' asg_span_ec: p_owner_payroll_action_id='||to_char(p_owner_payroll_action_id));
255: hr_utility.trace(' asg_span_ec: p_user_payroll_action_id='||to_char(p_user_payroll_action_id));
250: begin
251: hr_utility.set_location('Entering: asg_span_ec', 1);
252: l_dimension_name := upper(p_dimension_name);
253: -- Get the year component of the input date
254: hr_utility.trace(' asg_span_ec: p_owner_payroll_action_id='||to_char(p_owner_payroll_action_id));
255: hr_utility.trace(' asg_span_ec: p_user_payroll_action_id='||to_char(p_user_payroll_action_id));
256: hr_utility.trace(' asg_span_ec: p_owner_assignment_action_id='||to_char(p_owner_assignment_action_id));
257: hr_utility.trace(' asg_span_ec: p_user_assignment_action_id='||to_char(p_user_assignment_action_id));
258: hr_utility.trace(' asg_span_ec: p_dimension_name ='||p_dimension_name );
251: hr_utility.set_location('Entering: asg_span_ec', 1);
252: l_dimension_name := upper(p_dimension_name);
253: -- Get the year component of the input date
254: hr_utility.trace(' asg_span_ec: p_owner_payroll_action_id='||to_char(p_owner_payroll_action_id));
255: hr_utility.trace(' asg_span_ec: p_user_payroll_action_id='||to_char(p_user_payroll_action_id));
256: hr_utility.trace(' asg_span_ec: p_owner_assignment_action_id='||to_char(p_owner_assignment_action_id));
257: hr_utility.trace(' asg_span_ec: p_user_assignment_action_id='||to_char(p_user_assignment_action_id));
258: hr_utility.trace(' asg_span_ec: p_dimension_name ='||p_dimension_name );
259: hr_utility.trace(' asg_span_ec: p_owner_effective_date='||to_char(p_owner_effective_date,'DD-MON-YYYY'));
252: l_dimension_name := upper(p_dimension_name);
253: -- Get the year component of the input date
254: hr_utility.trace(' asg_span_ec: p_owner_payroll_action_id='||to_char(p_owner_payroll_action_id));
255: hr_utility.trace(' asg_span_ec: p_user_payroll_action_id='||to_char(p_user_payroll_action_id));
256: hr_utility.trace(' asg_span_ec: p_owner_assignment_action_id='||to_char(p_owner_assignment_action_id));
257: hr_utility.trace(' asg_span_ec: p_user_assignment_action_id='||to_char(p_user_assignment_action_id));
258: hr_utility.trace(' asg_span_ec: p_dimension_name ='||p_dimension_name );
259: hr_utility.trace(' asg_span_ec: p_owner_effective_date='||to_char(p_owner_effective_date,'DD-MON-YYYY'));
260: hr_utility.trace(' asg_span_ec: p_user_effective_date='||to_char(p_user_effective_date,'DD-MON-YYYY'));
253: -- Get the year component of the input date
254: hr_utility.trace(' asg_span_ec: p_owner_payroll_action_id='||to_char(p_owner_payroll_action_id));
255: hr_utility.trace(' asg_span_ec: p_user_payroll_action_id='||to_char(p_user_payroll_action_id));
256: hr_utility.trace(' asg_span_ec: p_owner_assignment_action_id='||to_char(p_owner_assignment_action_id));
257: hr_utility.trace(' asg_span_ec: p_user_assignment_action_id='||to_char(p_user_assignment_action_id));
258: hr_utility.trace(' asg_span_ec: p_dimension_name ='||p_dimension_name );
259: hr_utility.trace(' asg_span_ec: p_owner_effective_date='||to_char(p_owner_effective_date,'DD-MON-YYYY'));
260: hr_utility.trace(' asg_span_ec: p_user_effective_date='||to_char(p_user_effective_date,'DD-MON-YYYY'));
261:
254: hr_utility.trace(' asg_span_ec: p_owner_payroll_action_id='||to_char(p_owner_payroll_action_id));
255: hr_utility.trace(' asg_span_ec: p_user_payroll_action_id='||to_char(p_user_payroll_action_id));
256: hr_utility.trace(' asg_span_ec: p_owner_assignment_action_id='||to_char(p_owner_assignment_action_id));
257: hr_utility.trace(' asg_span_ec: p_user_assignment_action_id='||to_char(p_user_assignment_action_id));
258: hr_utility.trace(' asg_span_ec: p_dimension_name ='||p_dimension_name );
259: hr_utility.trace(' asg_span_ec: p_owner_effective_date='||to_char(p_owner_effective_date,'DD-MON-YYYY'));
260: hr_utility.trace(' asg_span_ec: p_user_effective_date='||to_char(p_user_effective_date,'DD-MON-YYYY'));
261:
262: --
255: hr_utility.trace(' asg_span_ec: p_user_payroll_action_id='||to_char(p_user_payroll_action_id));
256: hr_utility.trace(' asg_span_ec: p_owner_assignment_action_id='||to_char(p_owner_assignment_action_id));
257: hr_utility.trace(' asg_span_ec: p_user_assignment_action_id='||to_char(p_user_assignment_action_id));
258: hr_utility.trace(' asg_span_ec: p_dimension_name ='||p_dimension_name );
259: hr_utility.trace(' asg_span_ec: p_owner_effective_date='||to_char(p_owner_effective_date,'DD-MON-YYYY'));
260: hr_utility.trace(' asg_span_ec: p_user_effective_date='||to_char(p_user_effective_date,'DD-MON-YYYY'));
261:
262: --
263: -- select the start span for the using action.
256: hr_utility.trace(' asg_span_ec: p_owner_assignment_action_id='||to_char(p_owner_assignment_action_id));
257: hr_utility.trace(' asg_span_ec: p_user_assignment_action_id='||to_char(p_user_assignment_action_id));
258: hr_utility.trace(' asg_span_ec: p_dimension_name ='||p_dimension_name );
259: hr_utility.trace(' asg_span_ec: p_owner_effective_date='||to_char(p_owner_effective_date,'DD-MON-YYYY'));
260: hr_utility.trace(' asg_span_ec: p_user_effective_date='||to_char(p_user_effective_date,'DD-MON-YYYY'));
261:
262: --
263: -- select the start span for the using action.
264: -- if the owning action associated with the latest balance, is
328: l_user_span_start := hr_au_routes.span_start( p_user_effective_date
329: , l_frequency
330: , l_date_dd_mm);
331: --
332: hr_utility.trace(' asg_span_ec: l_user_span_start='||to_char(l_user_span_start,'DD-MON-YYYY'));
333: --
334: if p_owner_effective_date < l_user_span_start then
335: p_expiry_information := 1;
336: hr_utility.set_location(' asg_span_ec: EXPIRED', 10);
332: hr_utility.trace(' asg_span_ec: l_user_span_start='||to_char(l_user_span_start,'DD-MON-YYYY'));
333: --
334: if p_owner_effective_date < l_user_span_start then
335: p_expiry_information := 1;
336: hr_utility.set_location(' asg_span_ec: EXPIRED', 10);
337: else
338: p_expiry_information := 0;
339: hr_utility.set_location(' asg_span_ec: NOT EXPIRED', 10);
340: end if;
335: p_expiry_information := 1;
336: hr_utility.set_location(' asg_span_ec: EXPIRED', 10);
337: else
338: p_expiry_information := 0;
339: hr_utility.set_location(' asg_span_ec: NOT EXPIRED', 10);
340: end if;
341: --
342: end asg_span_ec;
343:
375:
376:
377: BEGIN
378:
379: hr_utility.trace('p_owner_payroll_action_id :'||p_owner_payroll_action_id);
380: hr_utility.trace('p_user_payroll_action_id :'||p_user_payroll_action_id);
381: hr_utility.trace('p_owner_assignment_action_id :' ||p_owner_assignment_action_id);
382: hr_utility.trace('p_user_assignment_action_id :' ||p_user_assignment_action_id );
383: hr_utility.trace('p_owner_effective_date :' ||p_owner_effective_date );
376:
377: BEGIN
378:
379: hr_utility.trace('p_owner_payroll_action_id :'||p_owner_payroll_action_id);
380: hr_utility.trace('p_user_payroll_action_id :'||p_user_payroll_action_id);
381: hr_utility.trace('p_owner_assignment_action_id :' ||p_owner_assignment_action_id);
382: hr_utility.trace('p_user_assignment_action_id :' ||p_user_assignment_action_id );
383: hr_utility.trace('p_owner_effective_date :' ||p_owner_effective_date );
384: hr_utility.trace('p_user_effective_date :' ||p_user_effective_date );
377: BEGIN
378:
379: hr_utility.trace('p_owner_payroll_action_id :'||p_owner_payroll_action_id);
380: hr_utility.trace('p_user_payroll_action_id :'||p_user_payroll_action_id);
381: hr_utility.trace('p_owner_assignment_action_id :' ||p_owner_assignment_action_id);
382: hr_utility.trace('p_user_assignment_action_id :' ||p_user_assignment_action_id );
383: hr_utility.trace('p_owner_effective_date :' ||p_owner_effective_date );
384: hr_utility.trace('p_user_effective_date :' ||p_user_effective_date );
385: hr_utility.trace('p_dimension_name :' ||p_dimension_name );
378:
379: hr_utility.trace('p_owner_payroll_action_id :'||p_owner_payroll_action_id);
380: hr_utility.trace('p_user_payroll_action_id :'||p_user_payroll_action_id);
381: hr_utility.trace('p_owner_assignment_action_id :' ||p_owner_assignment_action_id);
382: hr_utility.trace('p_user_assignment_action_id :' ||p_user_assignment_action_id );
383: hr_utility.trace('p_owner_effective_date :' ||p_owner_effective_date );
384: hr_utility.trace('p_user_effective_date :' ||p_user_effective_date );
385: hr_utility.trace('p_dimension_name :' ||p_dimension_name );
386:
379: hr_utility.trace('p_owner_payroll_action_id :'||p_owner_payroll_action_id);
380: hr_utility.trace('p_user_payroll_action_id :'||p_user_payroll_action_id);
381: hr_utility.trace('p_owner_assignment_action_id :' ||p_owner_assignment_action_id);
382: hr_utility.trace('p_user_assignment_action_id :' ||p_user_assignment_action_id );
383: hr_utility.trace('p_owner_effective_date :' ||p_owner_effective_date );
384: hr_utility.trace('p_user_effective_date :' ||p_user_effective_date );
385: hr_utility.trace('p_dimension_name :' ||p_dimension_name );
386:
387: /*6159013 These Two Dimension Never Expires hence Returning 31-DEC-4712 as expiry Date*/
380: hr_utility.trace('p_user_payroll_action_id :'||p_user_payroll_action_id);
381: hr_utility.trace('p_owner_assignment_action_id :' ||p_owner_assignment_action_id);
382: hr_utility.trace('p_user_assignment_action_id :' ||p_user_assignment_action_id );
383: hr_utility.trace('p_owner_effective_date :' ||p_owner_effective_date );
384: hr_utility.trace('p_user_effective_date :' ||p_user_effective_date );
385: hr_utility.trace('p_dimension_name :' ||p_dimension_name );
386:
387: /*6159013 These Two Dimension Never Expires hence Returning 31-DEC-4712 as expiry Date*/
388: IF p_dimension_name in ('_ASG_TD','_ASG_LE_TD') THEN
381: hr_utility.trace('p_owner_assignment_action_id :' ||p_owner_assignment_action_id);
382: hr_utility.trace('p_user_assignment_action_id :' ||p_user_assignment_action_id );
383: hr_utility.trace('p_owner_effective_date :' ||p_owner_effective_date );
384: hr_utility.trace('p_user_effective_date :' ||p_user_effective_date );
385: hr_utility.trace('p_dimension_name :' ||p_dimension_name );
386:
387: /*6159013 These Two Dimension Never Expires hence Returning 31-DEC-4712 as expiry Date*/
388: IF p_dimension_name in ('_ASG_TD','_ASG_LE_TD') THEN
389: p_expiry_information := fnd_date.canonical_to_date('4712/12/31');
391: /* These balance dimensions never expire and hence considered as special cases
392: return the p_owner_effective_date for these cases */
393: ELSIF p_dimension_name in ('_ASG_RUN','_ASG_LE_RUN') THEN --2610141
394: p_expiry_information := p_owner_effective_date;
395: hr_utility.trace('p_expiry_information'||p_dimension_name||':'||p_expiry_information);
396:
397: /* The balance dimension '_ASG_MTD' is checking for feed using the normal calendar year
398: so, we use next_month function to get the expiry date of the dimension */
399: ELSIF p_dimension_name in ('_ASG_MTD','_ASG_LE_MTD') THEN --2610141
397: /* The balance dimension '_ASG_MTD' is checking for feed using the normal calendar year
398: so, we use next_month function to get the expiry date of the dimension */
399: ELSIF p_dimension_name in ('_ASG_MTD','_ASG_LE_MTD') THEN --2610141
400: p_expiry_information := next_month(p_owner_effective_date)-1;
401: hr_utility.trace('p_expiry_information'||p_dimension_name||':'||p_expiry_information);
402:
403: /* The balance dimension '_ASG_QTD' is checking for feed using the normal calendar year
404: so, we use next_quarter function to get the expiry date of the dimension */
405: ELSIF p_dimension_name in ('_ASG_QTD','_ASG_LE_QTD') THEN --2610141
403: /* The balance dimension '_ASG_QTD' is checking for feed using the normal calendar year
404: so, we use next_quarter function to get the expiry date of the dimension */
405: ELSIF p_dimension_name in ('_ASG_QTD','_ASG_LE_QTD') THEN --2610141
406: p_expiry_information := next_quarter(p_owner_effective_date)-1;
407: hr_utility.trace('p_expiry_information'||p_dimension_name||':'||p_expiry_information);
408:
409: /* The balance dimension '_ASG_CAL_YTD' is checking for feed using the normal calendar year
410: so, we use next_year function to get the expiry date of the dimension */
411: ELSIF p_dimension_name in ('_ASG_CAL_YTD','_ASG_LE_CAL_YTD') THEN --2610141
409: /* The balance dimension '_ASG_CAL_YTD' is checking for feed using the normal calendar year
410: so, we use next_year function to get the expiry date of the dimension */
411: ELSIF p_dimension_name in ('_ASG_CAL_YTD','_ASG_LE_CAL_YTD') THEN --2610141
412: p_expiry_information := next_year(p_owner_effective_date)-1;
413: hr_utility.trace('p_expiry_information'||p_dimension_name||':'||p_expiry_information);
414:
415: /* The balance dimension '_ASG_YTD' is checking for feed using the financial year
416: so, we use next_fin_year function to get the expiry date of the dimension */
417: ELSIF p_dimension_name in ('_ASG_YTD','_ASG_LE_YTD') THEN --2610141
415: /* The balance dimension '_ASG_YTD' is checking for feed using the financial year
416: so, we use next_fin_year function to get the expiry date of the dimension */
417: ELSIF p_dimension_name in ('_ASG_YTD','_ASG_LE_YTD') THEN --2610141
418: p_expiry_information := next_fin_year(to_date(g_fin_year_start,'DD-MM-'), p_owner_effective_date)-1;
419: hr_utility.trace('p_expiry_information'||p_dimension_name||':'||p_expiry_information);
420:
421: /* The balance dimension '_ASG_FY_QTD' is checking for feed using the financial year
422: so, we use next_fin_quarter function to get the expiry date of the dimension */
423: ELSIF p_dimension_name in ('_ASG_FY_QTD','_ASG_LE_FY_QTD') THEN --2610141
424: open get_beg_of_fiscal_year(p_owner_payroll_action_id);
425: fetch get_beg_of_fiscal_year into l_beg_of_fiscal_year;
426: close get_beg_of_fiscal_year;
427:
428: hr_utility.trace('l_beg_of_fiscal_year :'||l_beg_of_fiscal_year);
429: p_expiry_information := next_fin_quarter(l_beg_of_fiscal_year, p_owner_effective_date)-1;
430: hr_utility.trace('p_expiry_information'||p_dimension_name||':'||p_expiry_information);
431:
432: /* The balance dimension '_ASG_FY_YTD' is checking for feed using the financial year
426: close get_beg_of_fiscal_year;
427:
428: hr_utility.trace('l_beg_of_fiscal_year :'||l_beg_of_fiscal_year);
429: p_expiry_information := next_fin_quarter(l_beg_of_fiscal_year, p_owner_effective_date)-1;
430: hr_utility.trace('p_expiry_information'||p_dimension_name||':'||p_expiry_information);
431:
432: /* The balance dimension '_ASG_FY_YTD' is checking for feed using the financial year
433: so, we use next_fin_year function to get the expiry date of the dimension */
434: ELSIF p_dimension_name in ('_ASG_FY_YTD','_ASG_LE_FY_YTD') THEN --2610141
435: open get_beg_of_fiscal_year(p_owner_payroll_action_id);
436: fetch get_beg_of_fiscal_year into l_beg_of_fiscal_year;
437: close get_beg_of_fiscal_year;
438:
439: hr_utility.trace('l_beg_of_fiscal_year :'||l_beg_of_fiscal_year);
440: p_expiry_information := next_fin_year(l_beg_of_fiscal_year, p_owner_effective_date)-1;
441: hr_utility.trace('p_expiry_information'||p_dimension_name||':'||p_expiry_information);
442:
443: /* The balance dimension '_ASG_FBT_YTD' is checking for feed using the fbt year
437: close get_beg_of_fiscal_year;
438:
439: hr_utility.trace('l_beg_of_fiscal_year :'||l_beg_of_fiscal_year);
440: p_expiry_information := next_fin_year(l_beg_of_fiscal_year, p_owner_effective_date)-1;
441: hr_utility.trace('p_expiry_information'||p_dimension_name||':'||p_expiry_information);
442:
443: /* The balance dimension '_ASG_FBT_YTD' is checking for feed using the fbt year
444: so, we use next_fbt_year function to get the expiry date of the dimension */
445: ELSIF p_dimension_name in ('_ASG_FBT_YTD','_ASG_LE_FBT_YTD') THEN --2610141
443: /* The balance dimension '_ASG_FBT_YTD' is checking for feed using the fbt year
444: so, we use next_fbt_year function to get the expiry date of the dimension */
445: ELSIF p_dimension_name in ('_ASG_FBT_YTD','_ASG_LE_FBT_YTD') THEN --2610141
446: p_expiry_information := next_fbt_year(p_owner_effective_date)-1;
447: hr_utility.trace('p_expiry_information'||p_dimension_name||':'||p_expiry_information);
448:
449: ELSE
450: hr_utility.set_message(801, 'NO_EXP_CHECK_FOR_THIS_DIMENSION');
451: hr_utility.raise_error;
446: p_expiry_information := next_fbt_year(p_owner_effective_date)-1;
447: hr_utility.trace('p_expiry_information'||p_dimension_name||':'||p_expiry_information);
448:
449: ELSE
450: hr_utility.set_message(801, 'NO_EXP_CHECK_FOR_THIS_DIMENSION');
451: hr_utility.raise_error;
452: END IF;
453:
454:
447: hr_utility.trace('p_expiry_information'||p_dimension_name||':'||p_expiry_information);
448:
449: ELSE
450: hr_utility.set_message(801, 'NO_EXP_CHECK_FOR_THIS_DIMENSION');
451: hr_utility.raise_error;
452: END IF;
453:
454:
455: END asg_span_ec;