1 PACKAGE BODY PA_PURGE_VALIDATE_CAPITAL AS
2 /* $Header: PAXGCPVB.pls 120.1 2005/08/09 04:16:55 avajain noship $ */
3
4 PROCEDURE validate_capital(p_project_id IN NUMBER,
5 p_purge_to_date IN DATE,
6 p_active_flag IN VARCHAR2,
7 p_err_code IN OUT NOCOPY NUMBER,
8 p_err_stack IN OUT NOCOPY VARCHAR2,
9 p_err_stage IN OUT NOCOPY VARCHAR2) IS
10 --
11 -- Invoices in AP that are not yet transfered to PA
12 --
13 /* Commented the cursor IsVenInvPending for the bug# 2389458
14 CURSOR IsVenInvPending IS
15 SELECT 'VENDOR INVOICE PENDING'
16 FROM dual
17 WHERE EXISTS ( SELECT aid.invoice_id
18 FROM ap_invoice_distributions_all aid
19 WHERE aid.project_id = p_project_id
20 AND (p_active_flag = 'C' or
21 aid.expenditure_item_date < trunc(p_purge_to_date))
22 AND aid.pa_addition_flag <> 'Y');
23 */
24
25 /* Modified the cursor IsVenInvPending for the bug# 2389458 as below */
26
27 /* Bug#2429757 Commented out this cursor as this is generating a similar validation
28 error message like the cursor IsCommitmentExist */
29 /* Bug#2407499: Reverted the fix of the bug#2429757 as the cancelled invoices are
30 not handled by the pa_commitment_txns_v of the cursor IsCommitmentExist */
31 CURSOR IsVenInvPending IS
32 SELECT 'VENDOR INVOICE PENDING'
33 FROM dual
34 WHERE EXISTS ( SELECT aid.invoice_id
35 FROM ap_invoice_distributions_all aid,
36 ap_invoices_all ai
37 WHERE aid.project_id = p_project_id
38 AND aid.invoice_id = ai.invoice_id
39 AND ai.invoice_type_lookup_code <> 'EXPENSE REPORT'
40 AND (p_active_flag = 'C' or
41 trunc(aid.expenditure_item_date) <= trunc(p_purge_to_date))
42 -- Bug 2767507 Added 'G' to the list of values which pa_addition_flag
43 -- shouldnot check when looking for pending invoices.
44 AND aid.pa_addition_flag NOT IN ('Z','T','E','Y','G', 'F') --bug# 4351752
45 -- Bug# 2407499
46 AND nvl(aid.reversal_flag, 'N') <> 'Y');
47
48 --
49 -- Asset Lines that are not yet transfered to FA
50 --
51 CURSOR IsAstLinNotXferred IS
52 SELECT 'ASSET LINE NOT TRANSFERRED'
53 FROM dual
54 WHERE EXISTS ( SELECT pal.project_asset_line_id
55 FROM pa_project_asset_lines_all pal
56 WHERE pal.project_id = p_project_id
57 AND pal.transfer_status_code <> 'T');
58
59 --
60 -- CDLs that are not yet transfered to AP
61 --
62 CURSOR IsAdjNotXferToAP IS
63 SELECT 'ADJ NOT TRANSFERRED TO AP'
64 FROM dual
65 WHERE EXISTS (SELECT ei.expenditure_item_id
66 FROM pa_cost_distribution_lines_all cdl,
67 pa_expenditure_items_all ei
68 WHERE ei.expenditure_item_id = cdl.expenditure_item_id
69 AND ((cdl.transfer_status_code in ('P','R','X')
70 and ei.system_linkage_function = 'VI') or
71 (cdl.transfer_status_code in ('P','R','X','T')
72 and ei.system_linkage_function = 'ER'))
73 AND cdl.line_type = 'R'
74 AND (p_active_flag = 'C' or
75 trunc(ei.expenditure_item_date)<= trunc(p_purge_to_date))
76 AND ei.project_id = p_project_id);
77
78 --
79 -- Expenditure items that are split/transfered,reversed and marked for Recalculation
80 -- but not cost distributed.
81 --
82 /*****************************************
83 This is already handled as part of costing validation
84
85 CURSOR IsEiNotCosted IS
86 SELECT 'EI NOT COSTED'
87 FROM dual
88 WHERE EXISTS ( SELECT ei.expenditure_item_id
89 FROM pa_expenditure_items_all ei,
90 pa_tasks pt
91 WHERE ei.system_linkage_function in ( 'VI', 'ER' )
92 AND ei.task_id = pt.task_id
93 AND pt.project_id = p_project_id
94 AND (p_active_flag = 'C' or ei.expenditure_item_date < trunc(p_purge_to_date))
95 AND ei.cost_distributed_flag||'' = 'N');
96 *****************************************/
97 --
98 -- Expenditure items that are not yet Capitalized
99 --
100 CURSOR IsEiNotCapitalized IS
101 SELECT 'EXP ITEM NOT CAPITALIZED'
102 FROM dual
103 WHERE EXISTS ( SELECT pcdl.expenditure_item_id
104 FROM pa_cost_distribution_lines_all pcdl,
105 pa_expenditure_items_all pei,
106 pa_tasks pt,
107 pa_projects pp,
108 pa_project_types ppt
109 WHERE pcdl.expenditure_item_id = pei.expenditure_item_id
110 AND pei.revenue_distributed_flag||'' = 'N'
111 AND pcdl.line_type = DECODE(ppt.capital_cost_type_code,'B','D','R')
112 AND pcdl.billable_flag = 'Y'
113 AND pei.task_id = pt.task_id
114 AND pt.project_id = pp.project_id
115 AND pp.project_id = p_project_id
116 AND (p_active_flag = 'C' or
117 trunc(pei.expenditure_item_date) <= trunc(p_purge_to_date))
118 AND pp.project_type = ppt.project_type
119 AND pei.task_id IN
120 ( select task_id
121 from pa_tasks pt2
122 where project_id = pp.project_id
123 and ( exists
124 (SELECT task_id
125 FROM pa_project_asset_assignments paa
126 WHERE paa.project_id = pp.project_id
127 and (paa.task_id = pt2.task_id or
128 paa.task_id = pt2.top_task_id))
129 or exists --- Return all common tasks
130 (SELECT task_id
131 FROM pa_project_asset_assignments paa
132 WHERE paa.project_id = pp.project_id
133 and task_id = pt2.task_id
134 AND paa.project_asset_id = 0))
135 UNION
136 SELECT task_id
137 FROM pa_tasks
138 WHERE project_id IN
139 (SELECT project_id --- project level asset assignment
140 FROM pa_project_asset_assignments paa
141 WHERE project_id = pp.project_id
142 AND NVL(task_id, 0) = 0
143 UNION
144 SELECT project_id --- return all common tasks
145 FROM pa_project_asset_assignments paa
146 WHERE paa.project_id = pp.project_id
147 and NVL(paa.task_id,0) = 0
148 AND paa.project_asset_id = 0))
149 AND NOT EXISTS
150 (
151 SELECT 'This CDL was summarized before'
152 FROM pa_project_asset_line_details pald
153 WHERE pald.expenditure_item_id = pcdl.expenditure_item_id
154 AND pald.line_num = pcdl.line_num
155 AND pald.reversed_flag||'' = 'N'
156 ));
157
158 --
159 -- Expenditure items that are not yet Capitalized
160 --
161 CURSOR IsCommitmentExist IS
162 SELECT 'COMMITMENT EXISTS'
163 FROM dual
164 WHERE EXISTS ( SELECT pctv.project_id
165 FROM pa_commitment_txns_v pctv
166 WHERE pctv.project_id = p_project_id
167 AND pctv.expenditure_item_date is not null
168 AND (p_active_flag = 'C' or
169 trunc(pctv.expenditure_item_date) <= trunc(p_purge_to_date))
170 AND pctv.line_type not in ('P','I') /* Bug 2503781.*/
171 UNION /* Below lines added for bug 2503781 */
172 SELECT pctv1.project_id
173 FROM pa_commitment_txns_v pctv1
174 WHERE pctv1.project_id = p_project_id
175 AND pctv1.expenditure_item_date is not null
176 AND (p_active_flag = 'C' or
177 trunc(pctv1.expenditure_item_date) <= trunc(p_purge_to_date))
178 AND pctv1.line_type = 'P'
179 AND pctv1.tot_cmt_quantity > 0
180 AND nvl(pctv1.quantity_cancelled,0) =0
181 UNION /* Added for bug 2553822 */
182 /* Bug 2598071 SELECT aid.invoice_id */
183 SELECT aid.invoice_id
184 FROM ap_invoice_distributions_all aid,
185 ap_invoices_all ai
186 WHERE aid.project_id = p_project_id
187 AND aid.invoice_id = ai.invoice_id
188 AND ai.source <> 'Oracle Project Accounting'
189 AND ai.invoice_type_lookup_code = 'EXPENSE REPORT'
190 AND (p_active_flag = 'C' or
191 trunc(aid.expenditure_item_date) <= trunc(p_purge_to_date))
192 AND aid.pa_addition_flag NOT IN ('Z','T','E','Y')
193 AND nvl(aid.reversal_flag, 'N') <> 'Y');
194
195
196 l_err_stack VARCHAR2(2000);
197 l_err_stage VARCHAR2(500);
198 l_exc_err_stage VARCHAR2(500);
199 l_err_code NUMBER;
200 l_dummy VARCHAR2(500) := NULL;
201
202 BEGIN
203
204 l_err_code := 0;
205 l_err_stage := p_err_stage;
206 l_err_stack := p_err_stack;
207
208 pa_debug.debug('-- Performing Capital validation for the project '||to_char(p_project_id));
209
210 /* Bug#2429757 Commented out this cursor as this is generating a similar validation
211 error message like the cursor IsCommitmentExist */
212
213 /* Bug#2407499: Reverted the fix of the bug#2429757 as the cancelled invoices are
214 not handled by the pa_commitment_txns_v of the cursor IsCommitmentExist */
215
216
217 -- Check if there are any pending invoices in AP for this project which are not transferred
218 -- to PA
219
220 l_exc_err_stage := 'Opening Cursor IsVenInvPending';
221
222 Open IsVenInvPending;
223
224 l_exc_err_stage := 'Fetching Cursor IsVenInvPending';
225
226 Fetch IsVenInvPending into l_dummy;
227
228 IF l_dummy is not null then
229
230 fnd_message.set_name('PA','PA_ARPR_VI_NOT_INFCED');
231 fnd_msg_pub.add;
232 l_err_code := 10;
233
234 l_err_stage := 'After Open Cursor IsVenInvPending';
235 l_err_stack := l_err_stack||'->After Open Cursor IsVenInvPending';
236 pa_debug.debug(' *Pending vendor invoices exist for project '||to_char(p_project_id));
237
238 End IF;
239
240 l_exc_err_stage := 'Closing Cursor IsVenInvPending';
241
242 Close IsVenInvPending;
243 l_dummy := NULL;
244
245 pa_debug.debug('Capital validation -- After Cursor IsVenInvPending');
246
247 -- Check if there are any Expenditure items for this project which are not Capitalized
248
249 if pa_purge_validate.g_project_type_class_code = 'CAPITAL' then /* Bug#2387342 */
250
251
252 l_exc_err_stage := 'Opening Cursor IsEiNotCapitalized';
253
254 Open IsEiNotCapitalized;
255
256 l_exc_err_stage := 'Fetching Cursor IsEiNotCapitalized';
257
258 Fetch IsEiNotCapitalized into l_dummy;
259
260 IF l_dummy is not null then
261
262 fnd_message.set_name('PA','PA_ARPR_EI_NOT_CAPTLZED');
263 fnd_msg_pub.add;
264 l_err_code := 10;
265
266 l_err_stage := 'After Open Cursor IsEiNotCapitalized';
267 l_err_stack := l_err_stack||'->After Open Cursor IsEiNotCapitalized';
268 pa_debug.debug(' *UnCapitalized Expenditure Items exist for project '||to_char(p_project_id));
269
270 End IF;
271
272 l_exc_err_stage := 'Closing Cursor IsEiNotCapitalized';
273
274 Close IsEiNotCapitalized;
275 l_dummy := NULL;
276
277 pa_debug.debug('Capital validation -- After Cursor IsEiNotCapitalized');
278
279 end if;
280
281 -- Check if there are any Adjustments for this project which are not Transferred to AP
282
283 l_exc_err_stage := 'Opening Cursor IsAdjNotXferToAP';
284
285 Open IsAdjNotXferToAP;
286
287 l_exc_err_stage := 'Fetching Cursor IsAdjNotXferToAP';
288
289 Fetch IsAdjNotXferToAP into l_dummy;
290
291 IF l_dummy is not null then
292
293 fnd_message.set_name('PA','PA_ARPR_VI_XFERED_NOT_INFCED');
294 fnd_msg_pub.add;
295 l_err_code := 10;
296
297 l_err_stage := 'After Open Cursor IsAdjNotXferToAP';
298 l_err_stack := l_err_stack||'->After Open Cursor IsAdjNotXferToAP';
299 pa_debug.debug(' *Pending vendor invoice adjustments exist for project '||to_char(p_project_id));
300
301 End IF;
302
303 l_exc_err_stage := 'Closing Cursor IsAdjNotXferToAP';
304
305 Close IsAdjNotXferToAP;
306 l_dummy := NULL;
307
308 pa_debug.debug('Capital validation -- After Cursor IsAdjNotXferToAP');
309
310 -- Check if there are any Expenditure items for this project which are not Costed
311
312 /******************************
313 l_exc_err_stage := 'Opening Cursor IsEiNotCosted';
314
315 Open IsEiNotCosted;
316
317 l_exc_err_stage := 'Fetching Cursor IsEiNotCosted';
318
319 Fetch IsEiNotCosted into l_dummy;
320
321 IF l_dummy is not null then
322
323 fnd_message.set_name('PA','PA_ARPR_EI_NOT_COSTED');
324 fnd_msg_pub.add;
325 l_err_code := 10;
326
327 l_err_stage := 'After Open Cursor IsEiNotCosted';
328 l_err_stack := l_err_stack||'->After Open Cursor IsEiNotCosted';
329 pa_debug.debug(' *Uncosted Expenditure items exist for project '||to_char(p_project_id));
330
331 End IF;
332
333 l_exc_err_stage := 'Closing Cursor IsEiNotCosted';
334
335 Close IsEiNotCosted;
336 l_dummy := NULL;
337
338 pa_debug.debug('Capital validation -- After Cursor IsEiNotCosted');
339
340 ********************************/
341 /* IF ( (g_purge_capital_flag = 'Y') AND Commented for Bug 2786753 */
342
343 IF (pa_purge_validate.g_project_type_class_code = 'CAPITAL') then /* Bug#2387342 */
344
345 -- Check if there are any Asset Lines for this project which are not amortized
346
347 l_exc_err_stage := 'Opening Cursor IsAstLinNotXferred';
348
349 Open IsAstLinNotXferred;
350
351 l_exc_err_stage := 'Fetching Cursor IsAstLinNotXferred';
352
353 Fetch IsAstLinNotXferred into l_dummy;
354
355 IF l_dummy is not null then
356
357 fnd_message.set_name('PA','PA_ARPR_ASSET_LN_NOT_INFCED');
358 fnd_msg_pub.add;
359 l_err_code := 10;
360
361 l_err_stage := 'After Open Cursor IsAstLinNotXferred';
362 l_err_stack := l_err_stack||'->After Open Cursor IsAstLinNotXferred';
363 pa_debug.debug(' *Unamortized Asset Lines exist for project '||to_char(p_project_id));
364
365 End IF;
366
367 l_exc_err_stage := 'Closing Cursor IsAstLinNotXferred';
368
369 Close IsAstLinNotXferred;
370 l_dummy := NULL;
371
372 pa_debug.debug('Capital validation -- After Cursor IsAstLinNotXferred');
373
374 End IF;
375
376 Open IsCommitmentExist;
377
378 l_exc_err_stage := 'Fetching Cursor IsCommitmentExist';
379
380 /* Fetch IsEiNotCosted into l_dummy; */
381 Fetch IsCommitmentExist into l_dummy;
382
383 IF l_dummy is not null then
384
385 fnd_message.set_name('PA','PA_ARPR_COMM_EXISTS');
386 fnd_msg_pub.add;
387 l_err_code := 10;
388
389 l_err_stage := 'After Open Cursor IsCommitmentExist';
390 l_err_stack := l_err_stack||'->After Open Cursor IsCommitmentExist';
391 pa_debug.debug(' *Commitments exist for project '||to_char(p_project_id));
392
393 End IF;
394
395 l_exc_err_stage := 'Closing Cursor IsCommitmentExist';
396
397 Close IsCommitmentExist;
398 l_dummy := NULL;
399
400 pa_debug.debug('Capital validation -- After Cursor IsCommitmentExist');
401
402 p_err_code := l_err_code;
403 p_err_stage := l_err_stage;
404 p_err_stack := l_err_stack;
405
406 EXCEPTION
407 WHEN OTHERS THEN
408 p_err_code := -1;
409 p_err_stage := to_char(SQLCODE);
410 fnd_msg_pub.add_exc_msg(
411 p_pkg_name => 'PA_PURGE_VALIDATE_CAPITAL',
412 p_procedure_name => 'VALIDATE_CAPITAL'||'-'||l_exc_err_stage,
413 p_error_text => 'ORA-'||LPAD(substr(p_err_stage,2),5,'0'));
414
415 END validate_capital;
416
417 END PA_PURGE_VALIDATE_CAPITAL;
[Home] [Help]