1 package body pynzexc as
2 /* $Header: pynzexc.pkb 115.1 2002/12/03 05:08:25 srrajago ship $ */
3 --
4 -- Change List
5 -- ----------
6 -- DATE Name Vers Bug No Description
7 -- -----------+---------------+--------+--------+-----------------------+
8 -- 13-Aug-1999 sclarke 1.0 Created
9 -- 03-Dec-2002 srrajago 1.1 2689221 Included 'nocopy' option for the 'out'
10 -- parameters of all the procedures,dbdrv
11 -- and checkfile commands.
12 -- -----------+---------------+--------+--------+-----------------------+
13 --
14 --
15 g_nz_fin_year_start constant varchar2(6) := '01-04-';
16 --
17 -------------------------------- asg_ptd_ec ----------------------------------------------------
18 --
19 -- name
20 -- asg_ptd_ec - assignment processing period to date expiry check.
21 -- description
22 -- expiry checking code for the following:
23 -- nz assignment-level process period to date balance dimension
24 -- notes
25 -- the associated dimension is expiry checked at payroll action level
26 --
27 procedure asg_ptd_ec
28 ( p_owner_payroll_action_id in number -- run created balance.
29 , p_user_payroll_action_id in number -- current run.
30 , p_owner_assignment_action_id in number -- assact created balance.
31 , p_user_assignment_action_id in number -- current assact..
32 , p_owner_effective_date in date -- eff date of balance.
33 , p_user_effective_date in date -- eff date of current run.
34 , p_dimension_name in varchar2 -- balance dimension name.
35 , p_expiry_information out nocopy number -- dimension expired flag.
36 ) is
37 --
38 cursor csr_time
39 ( p_payroll_action_id number
40 , p_assignment_action_id number
41 , p_effective_date date) is
42 select ptp.time_period_id
43 from pay_payroll_actions act
44 , per_time_periods ptp
45 where payroll_action_id = p_payroll_action_id
46 and act.date_earned between ptp.start_date and ptp.end_date
47 and act.payroll_id = ptp.payroll_id
48 and act.effective_date = p_effective_date;
49 --
50 l_user_time_period_id number;
51 l_owner_time_period_id number;
52 --
53 begin
54 --
55 -- select the period of the owning and using action and if they are
56 -- the same then the dimension has expired - either a prior period
57 -- or a different payroll
58 --
59 open csr_time
60 ( p_user_payroll_action_id
61 , p_user_assignment_action_id
62 , p_user_effective_date );
63 fetch csr_time
64 into l_user_time_period_id;
65 close csr_time;
66 --
67 open csr_time
68 ( p_owner_payroll_action_id
69 , p_owner_assignment_action_id
70 , p_owner_effective_date );
71 fetch csr_time
72 into l_owner_time_period_id;
73 close csr_time;
74 --
75 if l_user_time_period_id = l_owner_time_period_id then
76 p_expiry_information := 0;
77 else
78 p_expiry_information := 1;
79 end if;
80 --
81 end asg_ptd_ec;
82 --
83 -------------------------------- asg_span_ec -----------------------------------------------
84 --
85 -- name
86 -- asg_span_ec - assignment processing year to date expiry check.
87 -- description
88 -- expiry checking code for the following:
89 -- nz assignment-level process year to date balance dimension
90 -- notes
91 -- the associated dimension is expiry checked at payroll action level
92 --
93 procedure asg_span_ec
94 ( p_owner_payroll_action_id in number -- run created balance.
95 , p_user_payroll_action_id in number -- current run.
96 , p_owner_assignment_action_id in number -- assact created balance.
97 , p_user_assignment_action_id in number -- current assact.
98 , p_owner_effective_date in date -- eff date of balance.
99 , p_user_effective_date in date -- eff date of current run.
100 , p_dimension_name in varchar2 -- balance dimension name.
101 , p_expiry_information out nocopy number -- dimension expired flag.
102 ) is
103 --
104 cursor csr_get_business_group is
105 select business_group_id
106 from pay_assignment_actions_v
107 where assignment_action_id = p_user_assignment_action_id;
108 --
109 cursor csr_user_span_start(p_frequency number, p_span_start varchar2) is
110 select hr_nz_routes.span_start ( bptp.regular_payment_date
111 , p_frequency
112 , p_span_start )
113 from per_time_periods bptp
114 , pay_payroll_actions bact
115 where bact.payroll_action_id = p_user_payroll_action_id
116 and bact.effective_date = p_user_effective_date
117 and bact.payroll_id = bptp.payroll_id
118 and bact.date_earned between bptp.start_date and bptp.end_date;
119 --
120 cursor csr_owner_start is
121 select pptp.regular_payment_date
122 from per_time_periods pptp
123 , pay_payroll_actions pact
124 where pact.payroll_action_id = p_owner_payroll_action_id
125 and pact.effective_date = p_owner_effective_date
126 and pact.payroll_id = pptp.payroll_id
127 and pact.date_earned between pptp.start_date and pptp.end_date;
128 --
129 l_user_span_start date;
130 l_owner_start date;
131 l_date_dd_mm varchar2(11);
132 l_fy_user_span_start date;
133 l_frequency number;
134 l_dimension_name pay_balance_dimensions.dimension_name%type := upper(p_dimension_name);
135 l_business_group_id pay_payroll_actions.business_group_id%type;
136 --
137 begin
138 --
139 -- select the start span for the using action.
140 -- if the owning action associated with the latest balance, is
141 -- before the start of the span for the using regular payment date
142 -- then it has expired.
143 --
144 if lower(l_dimension_name) = '_asg_ytd' then
145 --
146 l_frequency := 1;
147 l_date_dd_mm := g_nz_fin_year_start;
148 --
149 elsif lower(l_dimension_name) in ('_asg_fy_ytd','_asg_fy_qtd') then
150 --
151 open csr_get_business_group;
152 fetch csr_get_business_group into l_business_group_id;
153 close csr_get_business_group;
154 --
155 l_fy_user_span_start := hr_nz_routes.get_fiscal_date( l_business_group_id);
156 --
157 if lower(l_dimension_name) = '_asg_fy_ytd' then
158 l_frequency := 1;
159 else
160 l_frequency := 4;
161 end if;
162 --
163 l_date_dd_mm := to_char(l_fy_user_span_start,'dd-mm-');
164 --
165 elsif lower(l_dimension_name) = '_asg_hol_ytd' then
166 --
167 l_frequency := 1;
168 l_date_dd_mm := to_char(hr_nz_routes.get_anniversary_date ( p_user_assignment_action_id
169 , p_user_effective_date), 'dd-mm-');
170 --
171 end if;
172 --
173 open csr_user_span_start (l_frequency, l_date_dd_mm);
174 fetch csr_user_span_start into l_user_span_start;
175 close csr_user_span_start;
176 --
177 open csr_owner_start;
178 fetch csr_owner_start into l_owner_start;
179 close csr_owner_start;
180 --
181 if l_owner_start < l_user_span_start then
182 p_expiry_information := 1;
183 else
184 p_expiry_information := 0;
185 end if;
186 --
187 end asg_span_ec;
188 --
189 end pynzexc;
[Home] [Help]