1 package body hr_pyoint as
2 /* $Header: pyasgint.pkb 120.0 2005/05/29 03:01:14 appldev noship $ */
3 /*
4 /*
5 Copyright (c) Oracle Corporation 1991,1992,1993. All rights reserved
6 --
7 Name : hr_pyoint
8 --
9 Description : Package contains procedure that does interlock checking.
10 --
11 Test List
12 ---------
13 Procedure Name Date Test Id Status
14 +----------------------------+----------+-----------+-------+--------------+
15 validate dsaxby 20-JAN-1993 Completed.
16 --
17 Change List
18 -----------
19 Date Name Vers Bug No Description
20 ----------- ---------- ----- ------- -----------------------------------
21 24-SEP-2004 NBRISTOW 115.4 Fixed GSCC errors.
22 24-SEP-2004 NBRISTOW 115.3 Changes for the Process Groups.
23 30-OCT-1996 ARASHID 40.3 397298 Modified return codes to indicate
24 why the interlock failed. Also
25 return assignment_number in an
26 extra parameter.
27 Also, fixed to use cursors because
28 the code SQL can return multiple
29 rows.
30 04-SEP-1995 DSAXBY 40.2 Missing '/' after the select from
31 user_errors at bottom of file.
32 28-SEP-1993 DSAXBY 40.1 Alter time dependent leg check to
33 join via per_periods_of_service.
34 22-APR-1993 DSAXBY 3.1 Chge per_all_ass to per_ass.
35 29-JAN-1993 DSAXBY 3.0 First created.
36 */
37 ----------------------------- validate -------------------------------------
38 /*
39 NAME
40 validate - validates assignment interlocks.
41 DESCRIPTION
42 This procedure validates assignment level interlock rules
43 for the run on an individual assignment basis.
44 It is called from the main run code when interlock flag
45 is set to 'Y'.
46 NOTES
47 There is currently no code within this package to
48 report those assignments failing interlock rules.
49 This can only currently be done by setting logging
50 on and examining the resultant log file.
51 The reporting ability will be added in due course.
52 */
53 procedure validate
54 (
55 pactid in out nocopy number, -- payroll_action_id.
56 assignid in out nocopy number, -- assignment_id to check.
57 itpflag in out nocopy varchar2, -- independent time periods flag.
58 assnum in out nocopy varchar2, -- returned assignment_number.
59 intstat in out nocopy number -- interlock status.
60 ) is
61 SUCC constant number := 0;
62 FUTURE constant number := 1; -- Action in payroll with later start date.
63 PAST constant number := 2; -- Action incomplete in past payroll.
64 failed number; -- used to tell if interlock failed or not.
65 -- Effective dates used to determine why the interlock failed.
66 ed1 date;
67 ed2 date;
68 -- Independent time periods cursor.
69 cursor c_indep(pactid in number, assignid in number) is
70 select 1,
71 pa1.effective_date,
72 pa2.effective_date
73 from pay_payroll_actions pa1,
74 pay_payroll_actions pa2,
75 pay_action_classifications pcl,
76 pay_assignment_actions ac2
77 where pa1.payroll_action_id = pactid
78 and ac2.assignment_id = assignid
79 and pa2.payroll_action_id = ac2.payroll_action_id
80 and pcl.classification_name = 'SEQUENCED'
81 and pa2.action_type = pcl.action_type
82 and (pa2.effective_date > pa1.effective_date
83 or (ac2.action_status not in ('C', 'S')
84 and pa2.effective_date <= pa1.effective_date));
85 --
86 -- Group Dependent time periods cursor.
87 cursor c_grpdep(pactid in number, assignid in number) is
88 select 1,
89 pa1.effective_date,
90 pa2.effective_date,
91 as1.assignment_number
92 from per_assignments_f as1,
93 pay_payroll_actions pa1,
94 pay_payroll_actions pa2,
95 pay_action_classifications pcl,
96 pay_assignment_actions ac2,
97 pay_object_groups pog_act,
98 pay_object_groups pog_grp
99 where pa1.payroll_action_id = pactid
100 and as1.assignment_id = assignid
101 and pa1.effective_date between
102 as1.effective_start_date and as1.effective_end_date
103 and pog_act.source_id = as1.assignment_id
104 and pog_act.source_type = 'PAF'
105 and pog_act.parent_object_group_id = pog_grp.parent_object_group_id
106 and pog_grp.source_type = 'PAF'
107 and pog_grp.source_id = ac2.assignment_id
108 and pa2.payroll_action_id = ac2.payroll_action_id
109 and pcl.classification_name = 'SEQUENCED'
110 and pa2.action_type = pcl.action_type
111 and (pa2.effective_date > pa1.effective_date
112 or (ac2.action_status not in ('C', 'S')
113 and pa2.effective_date <= pa1.effective_date));
114 --
115 -- Dependent time periods cursor.
116 cursor c_dep(pactid in number, assignid in number) is
117 select 1,
118 pa1.effective_date,
119 pa2.effective_date,
120 as1.assignment_number
121 from per_assignments_f as1,
122 per_assignments_f as2,
123 pay_payroll_actions pa1,
124 pay_payroll_actions pa2,
125 pay_action_classifications pcl,
126 pay_assignment_actions ac2,
127 per_periods_of_service pos
128 where pa1.payroll_action_id = pactid
129 and as1.assignment_id = assignid
130 and pa1.effective_date between
131 as1.effective_start_date and as1.effective_end_date
132 and pos.period_of_service_id = as1.period_of_service_id
133 and as2.period_of_service_id = pos.period_of_service_id
134 and ac2.assignment_id = as2.assignment_id
135 and pa2.payroll_action_id = ac2.payroll_action_id
136 and pcl.classification_name = 'SEQUENCED'
137 and pa2.action_type = pcl.action_type
138 and (pa2.effective_date > pa1.effective_date
139 or (ac2.action_status not in ('C', 'S')
140 and pa2.effective_date <= pa1.effective_date));
141 begin
142 failed := NULL;
143 if(itpflag = 'Y') then
144 -- check interlock status for the independent
145 -- time periods case (e.g. the US).
146 begin
147 hr_utility.set_location('hr_pyoint.validate',1);
148 open c_indep(pactid,assignid);
149 fetch c_indep into failed, ed1, ed2;
150
151 -- Get the assignment_number (note: per_assignments_f is
152 -- datetracked).
153 hr_utility.set_location('hr_pyoint.validate',2);
154 if(not c_indep%NOTFOUND) then
155 select ass.assignment_number
156 into assnum
157 from pay_payroll_actions ppa,
158 per_assignments_f ass
159 where ppa.payroll_action_id = pactid
160 and ass.assignment_id = assignid
161 and ass.payroll_id = ppa.payroll_id
162 and (ppa.effective_date between
163 ass.effective_start_date and ass.effective_end_date);
164 end if;
165
166 -- Close the cursor.
167 close c_indep;
168 end;
169 elsif(itpflag = 'G') then
170 -- check interlock status for the dependent
171 -- time periods case (e.g. the UK).
172 begin
173 hr_utility.set_location('hr_pyoint.validate',3);
174 open c_grpdep(pactid,assignid);
175 fetch c_grpdep into failed, ed1, ed2, assnum;
176 close c_grpdep;
177 end;
178 else
179 -- check interlock status for the dependent
180 -- time periods case (e.g. the UK).
181 begin
182 hr_utility.set_location('hr_pyoint.validate',4);
183 open c_dep(pactid,assignid);
184 fetch c_dep into failed, ed1, ed2, assnum;
185 close c_dep;
186 end;
187 end if;
188 if(failed is not null) then
189 if(ed2 > ed1) then
190 intstat := FUTURE;
191 else
192 intstat := PAST;
193 end if;
194 else
195 intstat := SUCC;
196 end if;
197 end validate;
198 end hr_pyoint;
[Home] [Help]