1 package body hr_au_routes as
2 -- $Header: pyaurout.pkb 115.5 2003/10/20 02:13:44 puchil ship $
3
4 -- Copyright (c) 1999 Oracle Corporation
5 -- All rights reserved
6
7 -- Date Author Bug/CR Num Notes
8 -- -----------+--------+----------+-----------------------------------------
9 -- 20-Oct-2003 puchil 3198671 Made the calls to hr_utility as conditional.
10 -- 20-Oct-2003 puchil 3198671 Removed functions which were not
11 -- used after BRA implementation.
12 -- 18 Jun 2003 Ragovind 3004966 Peformance Fix. Added /*+RULE */ to route code
13 -- 24 Mar 2003 Kaverma 2856638 Modified cursor cur_asg_td and added dbdrv commands
14 -- 18 Feb 2000 JTurner Fixed problem with date format in
15 -- get_fiscal_date function
16 -- 13-NOV-1999 sgoggin Created
17
18 g_debug boolean := hr_utility.debug_enabled;
19 g_package varchar2(33) := 'hr_au_routes.';
20 g_fin_year_start constant varchar2(6) := '01-07-';
21 g_fbt_year_start constant varchar2(6) := '01-04-';
22 g_cal_year_start constant varchar2(6) := '01-01-';
23 --
24 ------------------------------span_Start----------------------------------------
25 -- return the start of the span (year/quarter/month/week)
26 --
27 function span_start ( p_input_date date
28 , p_frequency number default 1
29 , p_start_dd_mm varchar2
30 )
31 return date is
32 l_year number(4);
33 l_start date;
34 --
35 begin
36 if g_debug then
37 hr_utility.set_location('Entering: hr_au_routes.span_start',1);
38 end if;
39 -- Get the year component of the input date
40 l_year := to_number(to_char(p_input_date,'yyyy'));
41 if g_debug then
42 hr_utility.trace(' span_start: l_year='||to_char(l_year));
43 hr_utility.trace(' span_start: p_frequency='||to_char(p_frequency));
44 hr_utility.trace(' span_start: p_start_dd_mm='||p_start_dd_mm);
45 hr_utility.trace(' span_start: p_input_date='||to_char(p_input_date,'DD-MON-YYYY'));
46 end if;
47 --
48 if p_input_date >= to_date(p_start_dd_mm||to_char(l_year),'dd-mm-yyyy') then
49 l_start := to_date(p_start_dd_mm||to_char(l_year),'dd-mm-yyyy');
50 else
51 l_start := to_date(p_start_dd_mm||to_char(l_year -1),'dd-mm-yyyy');
52 end if;
53 if g_debug then
54 hr_utility.trace(' span_start: l_start='||to_char(l_year));
55 end if;
56 --
57 -- cater for weekly based frequency based on 52 per annum
58 --
59 if p_frequency in (52,26,13) then
60 l_start := p_input_date - mod(p_input_date - l_start,7 * 52/p_frequency);
61 else
62 -- cater for monthly based frequency based on 12 per annum
63 l_start := add_months(l_start, (12/p_frequency) * trunc(months_between(
64 p_input_date,l_start)/(12/p_frequency)));
65 end if;
66 --
67 if g_debug then
68 hr_utility.trace(' span_start: l_start='||to_char(l_start,'DD-MON-YYYY'));
69 hr_utility.set_location('Exiting: span_start',10);
70 end if;
71 return l_start;
72 --
73 end span_start;
74 --
75 -------------------------------get_fiscal_date-------------------------------------
76 -- The fiscal year start date is stored in a flex field and is user definable.
77 --
78 function get_fiscal_date( p_business_group_id in number)
79 return date is
80 --
81 cursor csr_fiscal_start is
82 select to_date(hoi.org_information11,'yyyy/mm/dd hh24:mi:ss')
83 from hr_organization_information hoi
84 where lower(hoi.org_information_context) = 'business group information'
85 and hoi.organization_id = p_business_group_id;
86 --
87 l_fiscal_start varchar2(11);
88 --
89 begin
90 --
91 open csr_fiscal_start;
92 fetch csr_fiscal_start
93 into l_fiscal_start;
94 close csr_fiscal_start;
95 --
96 return l_fiscal_start;
97 --
98 end get_fiscal_date;
99 --
100 -------------------------------fiscal_span_start------------------------------------
101 -- Because the fiscal date is a flexfield we need a special wrapper to get the
102 -- span start date.
103 --
104 function fiscal_span_start( p_input_date in date
105 , p_frequency in number
106 , p_business_group_id in number
107 )
108 return date is
109 --
110 begin
111 --
112 return span_start( p_input_date, p_frequency, to_char(hr_au_routes.get_fiscal_date(p_business_group_id),'dd-mm-') );
113 --
114 end fiscal_span_start;
115 --
116 ------------------------------get_anniversary_date--------------------------------
117 --
118 function get_anniversary_date ( p_assignment_action_id in number
119 , p_effective_date in date
120 )
121 return date is
122 --
123 cursor csr_ann_date is
124 select to_date(segment2,'YYYY/MM/DD HH24:MI:SS')
125 from hr_soft_coding_keyflex hsck
126 , per_all_assignments_f paaf
127 , pay_assignment_actions_v paav
128 where p_effective_date between paaf.effective_start_date and paaf.effective_end_date
129 and paav.assignment_action_id = p_assignment_action_id
130 and paav.assignment_id = paaf.assignment_id
131 and paaf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id;
132 --
133 l_anniversary_date varchar2(11);
134 --
135 begin
136 --
137 -- get the anniversary hire date
138 --
139 open csr_ann_date;
140 fetch csr_ann_date into l_anniversary_date;
141 close csr_ann_date;
142 --
143 return l_anniversary_date;
144 --
145 end get_anniversary_date;
146 --
147 ------------------------------anniversary_span_start--------------------------------
148 --
149 function anniversary_span_start
150 ( p_assignment_action_id number
151 , p_input_date date
152 )
153 return date is
154 --
155 begin
156 --
157 -- calculate the span start of the anniversary date
158 --
159 return span_start(p_input_date, 1, to_char(hr_au_routes.get_anniversary_date(p_assignment_action_id, p_input_date), 'dd-mm-'));
160 --
161 end anniversary_span_start;
162 --
163 end hr_au_routes;
[Home] [Help]