1 package body hr_nz_routes as
2 -- $Header: pynzrout.pkb 120.1 2005/09/01 21:54:17 snekkala noship $
3
4 -- Copyright (c) 1999 Oracle Corporation
5 -- All rights reserved
6
7 -- Date Author Bug/CR Num Notes
8 -- -----------+--------+----------+-----------------------------------------
9 -- 23 Jun 2003 puchil 3019293 Same as the previous one. Added comments
10 -- 23 Jun 2003 puchil 3019293 Changed the route cursors to use RBO
11 -- 24 Mar 2003 srrajago 2856694 Performance issue - Modified cursor cur_asg_td and added dbdrv commands.
12 -- 22 Feb 2000 jturner 1200795 updated date format in get_fiscal_date fn
13 -- 24-Sep-1999 sclarke 1003064
14 -- 13-Aug-1999 sclarke Created
15 -- 03-Mar-2004 sshankar 3480748 Changed Rule Hint to CBO hint in all cursors using route codes.
16 -- 04-Aug-2004 sshankar 3181581 Removed functions which use route code to fetch balances, instead
17 -- pay_balance_pkg.get_value will be used to fetch balance values.
18 -- The functions are as following:
19 -- _ASG_4WEEK
20 -- _ASG_FY_QTD
21 -- _ASG_FY_YTD
22 -- _ASG_HOL_YTD
23 -- _ASG_PAYMENT
24 -- _ASG_PTD
25 -- _ASG_RUN
26 -- _ASG_TD
27 -- _ASG_YTD
28 -- 01-Aug-2005 snekkala 4259438 Modified cursor csr_ann_date for performance
29
30 --
31 -- financial year start date for nz
32 --
33 g_fin_year_start constant varchar2(6) := '01-04-';
34 --
35 --------------------------------------------------------------------------------
36 --
37 --return the start of the span (year/quarter/week)
38 --
39 function span_start ( p_input_date date
40 , p_frequency number default 1
41 , p_start_dd_mm varchar2
42 )
43 return date is
44 l_year number(4);
45 l_start date;
46 --
47 begin
48 l_year := to_number(to_char(p_input_date,'yyyy'));
49 --
50 if p_input_date >= to_date(p_start_dd_mm||to_char(l_year),'dd-mm-yyyy') then
51 l_start := to_date(p_start_dd_mm||to_char(l_year),'dd-mm-yyyy');
52 else
53 l_start := to_date(p_start_dd_mm||to_char(l_year -1),'dd-mm-yyyy');
54 end if;
55 --
56 -- cater for weekly based frequency based on 52 per annum
57 --
58 if p_frequency in (52,26,13) then
59 l_start := p_input_date - mod(p_input_date - l_start,7 * 52/p_frequency);
60 else
61 -- cater for monthly based frequency based on 12 per annum
62 l_start := add_months(l_start, (12/p_frequency) * trunc(months_between(
63 p_input_date,l_start)/(12/p_frequency)));
64 end if;
65 --
66 return l_start;
67 --
68 end span_start;
69 --
70 -------------------------------get_fiscal_date-------------------------------------
71 --
72 function get_fiscal_date( p_business_group_id in number)
73 return date is
74 --
75 cursor csr_fiscal_start is
76 select to_date(hoi.org_information11,'yyyy/mm/dd hh24:mi:ss')
77 from hr_organization_information hoi
78 where lower(hoi.org_information_context) = 'business group information'
79 and hoi.organization_id = p_business_group_id;
80 --
81 l_fiscal_start varchar2(11);
82 --
83 begin
84 --
85 open csr_fiscal_start;
86 fetch csr_fiscal_start
87 into l_fiscal_start;
88 close csr_fiscal_start;
89 --
90 return l_fiscal_start;
91 --
92 end get_fiscal_date;
93 --
94 -------------------------------fiscal_span_start------------------------------------
95 --
96 function fiscal_span_start( p_input_date in date
97 , p_frequency in number
98 , p_business_group_id in number
99 )
100 return date is
101 --
102 begin
103 --
104 return span_start( p_input_date, p_frequency, to_char(hr_nz_routes.get_fiscal_date(p_business_group_id),'dd-mm-') );
105 --
106 end fiscal_span_start;
107 --
108 ------------------------------get_anniversary_date--------------------------------
109 --
110 function get_anniversary_date ( p_assignment_action_id in number
111 , p_effective_date in date
112 )
113 return date is
114 --
115 /* Bug 4259438 : Modified Cursor as part of performance fix */
116 CURSOR csr_ann_date
117 IS
118 SELECT to_date(segment2,'YYYY/MM/DD HH24:MI:SS')
119 FROM hr_soft_coding_keyflex hsck
120 , per_assignments_f paaf
121 , pay_assignment_actions paav
122 WHERE p_effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
123 AND paav.assignment_action_id = p_assignment_action_id
124 AND paav.assignment_id = paaf.assignment_id
125 AND paaf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id;
126 --
127 l_anniversary_date varchar2(11);
128 --
129 begin
130 --
131 -- get the anniversary hire date
132 --
133 open csr_ann_date;
134 fetch csr_ann_date into l_anniversary_date;
135 close csr_ann_date;
136 --
137 return l_anniversary_date;
138 --
139 end get_anniversary_date;
140 --
141 ------------------------------anniversary_span_start--------------------------------
142 --
143 function anniversary_span_start
144 ( p_assignment_action_id number
145 , p_input_date date
146 )
147 return date is
148 --
149 begin
150 --
151 -- calculate the span start of the anniversary date
152 --
153 return span_start(p_input_date, 1, to_char(hr_nz_routes.get_anniversary_date(p_assignment_action_id, p_input_date), 'dd-mm-'));
154 --
155 end anniversary_span_start;
156 --
157 ---------------------------------------------------------------------------
158 --
159 end hr_nz_routes;
[Home] [Help]