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