[Home] [Help]
PACKAGE BODY: APPS.PAY_VOID_PAYMENTS
Source
1 PACKAGE BODY pay_void_payments AS
2 -- $Header: pyvoidpy.pkb 120.0 2005/05/29 10:15:58 appldev noship $
3 --
4 --==============================================================================
5 -- VOID_PAYMENTS
6 --
7 --
8 --==============================================================================
9 PROCEDURE void_run (p_errmsg OUT NOCOPY VARCHAR2,
10 p_errcode OUT NOCOPY NUMBER,
11 p_payroll_action_id NUMBER,
12 p_effective_date VARCHAR2,
13 p_reason VARCHAR2,
14 p_start_cheque NUMBER default null,
15 p_end_cheque NUMBER default null,
16 p_start_assignment NUMBER default null,
17 p_end_assignment NUMBER default null
18 ) IS
19
20 l_effective_date DATE;
21 --
22 l_start_cheque number;
23 l_end_cheque number;
24 l_start_assignment number;
25 l_end_assignment number;
26 --
27 CURSOR overlap_csr IS
28 SELECT void_pa.effective_date,
29 fnd_number.canonical_to_number(chq_or_mag_aa.serial_number) cheque_number,
30 chq_or_mag_aa.assignment_action_id
31 FROM pay_assignment_actions chq_or_mag_aa,
32 pay_action_interlocks,
33 pay_assignment_actions void_aa,
34 pay_payroll_actions void_pa
35 WHERE chq_or_mag_aa.payroll_action_id = p_payroll_action_id
36 AND ((l_start_cheque is not null
37 AND l_end_cheque is not null
38 AND fnd_number.canonical_to_number(chq_or_mag_aa.serial_number)
39 BETWEEN l_start_cheque AND l_end_cheque)
40 OR (l_start_cheque is null and l_start_cheque is null))
41 AND ((l_start_assignment is not null
42 AND l_end_assignment is not null
43 AND chq_or_mag_aa.assignment_action_id
44 BETWEEN l_start_assignment AND l_end_assignment)
45 OR (l_start_assignment is null and l_end_assignment is null))
46 AND locked_action_id = chq_or_mag_aa.assignment_action_id
47 AND locking_action_id = void_aa.assignment_action_id
48 AND void_pa.payroll_action_id = void_aa.payroll_action_id
49 AND void_pa.action_type = 'D';
50 --
51 CURSOR void_csr IS
52 SELECT act.assignment_action_id,
53 act.assignment_id,
54 fnd_number.canonical_to_number(act.serial_number) cheque_number,
55 act.object_version_number,
56 act.action_status,
57 act.tax_unit_id,
58 -- nvl((select distinct 'Y'
59 -- from pay_ce_reconciled_payments prp
60 -- where prp.assignment_action_id = act.assignment_action_id)
61 -- ,'N') recon_exists,
62 nvl(pos.final_process_date, hr_general.end_of_time) final_process_date
63 FROM pay_assignment_actions act,
64 per_assignments_f asg,
65 per_periods_of_service pos
66 WHERE act.payroll_action_id = p_payroll_action_id
67 AND ((l_start_assignment is null and l_end_assignment is null)
68 OR
69 (l_start_assignment is not null
70 and l_end_assignment is not null
71 and act.assignment_action_id between
72 l_start_assignment and l_end_assignment))
73 AND ((l_start_cheque is not null
74 and l_end_cheque is not null
75 and fnd_number.canonical_to_number(act.serial_number)
76 BETWEEN l_start_cheque AND l_end_cheque)
77 or
78 (l_start_cheque is null and l_end_cheque is null))
79 AND act.assignment_id = asg.assignment_id
80 AND pos.period_of_service_id = asg.period_of_service_id
81 AND l_effective_date BETWEEN asg.effective_start_date
82 AND asg.effective_end_date ;
83 --
84 CURSOR csr_payment_reconciled (v_asg_act_id number) IS
85 SELECT 'Y'
86 FROM pay_ce_reconciled_payments prp
87 WHERE prp.assignment_action_id = v_asg_act_id;
88 --
89 l_dummy varchar2(1);
90 --
91 overlap_row overlap_csr%ROWTYPE;
92 void_row void_csr%ROWTYPE;
93 bgid pay_payroll_actions.business_group_id%TYPE;
94 csid pay_payroll_actions.consolidation_set_id%TYPE;
95 pid pay_payroll_actions.payroll_id%TYPE;
96 ovn pay_payroll_actions.object_version_number%TYPE;
97 action_type pay_payroll_actions.action_type%TYPE;
98 BEGIN
99 hr_utility.set_location ('void_run',1);
100 --------------------------------------------------------------------------------
101 -- Convert date. NB: This will have to change to generic data format in 10.7
102 --------------------------------------------------------------------------------
103 l_effective_date := trunc(fnd_date.canonical_to_date (p_effective_date));
104 --
105 if p_start_assignment is not null then
106 l_start_cheque := null;
107 l_end_cheque := null;
108 l_start_assignment := p_start_assignment;
109 l_end_assignment := p_end_assignment;
110 else
111 l_start_assignment := null;
112 l_end_assignment := null;
113 l_start_cheque := p_start_cheque;
114 l_end_cheque := p_end_cheque;
115 end if;
116 --
117 hr_utility.set_location ('void_run',2);
118 --------------------------------------------------------------------------------
119 -- Get template information from source payroll action
120 --------------------------------------------------------------------------------
121 SELECT business_group_id,
122 consolidation_set_id,
123 payroll_id,
124 object_version_number,
125 action_type
126 INTO bgid,csid,pid,ovn,action_type
127 FROM pay_payroll_actions
128 WHERE payroll_action_id = p_payroll_action_id;
129 --------------------------------------------------------------------------------
130 -- For chequewriter : Check there is no overlap with another void run
131 --------------------------------------------------------------------------------
132 OPEN overlap_csr;
133 FETCH overlap_csr INTO overlap_row;
134 --
135 hr_utility.set_location ('void_run',3);
136 --
137 IF overlap_csr%FOUND THEN
138 hr_utility.set_location ('void_run',31);
139 IF action_type = 'H' THEN
140 p_errmsg := 'Overlap detected. Cheque #' || overlap_row.cheque_number ||
141 ' already voided in run performed on ' ||
142 fnd_date.date_to_canonical(overlap_row.effective_date);
143 p_errcode := 2; -- Error
144 ELSIF action_type = 'M' THEN
145 p_errmsg := 'Overlap detected. MagTape Assignment Action #' ||
146 overlap_row.assignment_action_id ||
147 ' already voided in run performed on ' ||
148 fnd_date.date_to_canonical(overlap_row.effective_date);
149 p_errcode := 2; -- Error
150 END IF;
151 RETURN;
152 END IF;
153 --
154 hr_utility.set_location ('void_run',4);
155 CLOSE overlap_csr;
156 --
157 hr_utility.set_location ('void_run',5);
158 --------------------------------------------------------------------------------
159 -- Create new payroll action for void run
160 --------------------------------------------------------------------------------
161 INSERT INTO pay_payroll_actions
162 ( payroll_action_id,
163 action_type,
164 business_group_id,
165 consolidation_set_id,
166 payroll_id,
167 target_payroll_action_id,
168 action_population_status,
169 action_status,
170 effective_date,
171 comments,
172 start_cheque_number,
173 end_cheque_number,
174 request_id,
175 object_version_number ) VALUES
176 ( pay_payroll_actions_s.nextval, -- payroll_action_id
177 'D', -- action_type
178 bgid, -- business_group_id
179 csid, -- consolidation_set_id
180 pid, -- payroll_id
181 p_payroll_action_id, -- target_payroll_action_id
182 'C', -- action_population_status
183 'C', -- action_status
184 l_effective_date, -- effective_date
185 p_reason, -- comments
186 l_start_cheque, -- start_cheque_number
187 l_end_cheque, -- end_cheque_number
188 fnd_profile.value('REQUEST_ID'),-- request_id
189 ovn ); -- object_version_number
190 --
191 hr_utility.set_location ('void_run',6);
192 --------------------------------------------------------------------------------
193 -- Loop through assignment actions
194 --------------------------------------------------------------------------------
195 FOR void_row IN void_csr LOOP
196 hr_utility.set_location ('void_run',61);
197 IF action_type = 'H' THEN
198 --------------------------------------------------------------------------------
199 -- Check the cheque is complete
200 --------------------------------------------------------------------------------
201 IF void_row.action_status NOT IN ('C', 'S') THEN
202 p_errmsg := 'Cheque #' || void_row.cheque_number || ' status is ' ||
203 void_row.action_status;
204 p_errcode := 2; -- Error
205 RETURN;
206 END IF;
207 --
208 hr_utility.set_location ('void_run',62);
209 --------------------------------------------------------------------------------
210 -- Check whether the cheque is reconcilled.
211 --------------------------------------------------------------------------------
212 open csr_payment_reconciled(void_row.assignment_action_id);
213 fetch csr_payment_reconciled into l_dummy;
214 IF csr_payment_reconciled%found THEN
215 close csr_payment_reconciled;
216 p_errmsg := 'Cheque #' || void_row.cheque_number || ' is reconcilled.';
217 p_errcode := 2; -- Error
218 RETURN;
219 END IF;
220 close csr_payment_reconciled;
221 --
222 END IF;
223 hr_utility.set_location ('void_run',63);
224 --------------------------------------------------------------------------------
225 -- Create new assignment action for void
226 --------------------------------------------------------------------------------
227 INSERT INTO pay_assignment_actions
228 ( assignment_action_id,
229 assignment_id,
230 payroll_action_id,
231 action_status,
232 serial_number,
233 object_version_number,
234 action_sequence,
235 tax_unit_id ) VALUES
236 ( pay_assignment_actions_s.nextval, -- assignment_action_id
237 void_row.assignment_id, -- assignment_id
238 pay_payroll_actions_s.currval, -- payroll_action_id
239 'C', -- action_status
240 void_row.cheque_number, -- serial_number
241 void_row.object_version_number, -- object_version_number
242 pay_assignment_actions_s.nextval,
243 void_row.tax_unit_id ); -- action_sequence
244 --
245 hr_utility.set_location ('void_run',63);
246 --------------------------------------------------------------------------------
247 -- Create interlock from void aa to cheque aa
248 --------------------------------------------------------------------------------
249 INSERT INTO pay_action_interlocks
250 ( locking_action_id, locked_action_id )
251 VALUES
252 ( pay_assignment_actions_s.currval,void_row.assignment_action_id );
253 --
254 hr_utility.set_location ('void_run',64);
255 END LOOP;
256 --
257 hr_utility.set_location ('void_run',7);
258 COMMIT;
259 END void_run;
260 --
261 END pay_void_payments;