1 PACKAGE BODY pa_po_integration_utils AS
2 /* $Header: PAPOUTLB.pls 120.28 2012/02/01 10:49:16 mokukuma ship $ */
3
4
5
6
7 /*
8 Function : Allow_Project_Info_Change.
9 Description: Checks to see if the sum of receipts interfaced to projects for the po distribution
10 is zero, if sum is zero return 'Y' else 'N'.
11 Further is there is any Un-InterfacedLine to Projects, Return 'N'.--bmurthy bug 4049925
12 Arguments : p_po_distribution_id - Purchase order distribution on which project information needs to be changed.
13 Return : 'Y', if project information on the purchase order distribution can be updated.
14 'N', if project information on the purchase order distribution cannot be updated.
15 */
16 FUNCTION Allow_Project_Info_Change ( p_po_distribution_id IN po_distributions_all.po_distribution_id%type)
17 RETURN varchar2 IS
18
19 l_sum_amount_interfaced number := 0;
20 l_uninterfaced_to_pa number := 0;
21 l_po_distribution_id number := 0;
22
23 BEGIN
24 l_po_distribution_id := nvl(p_po_distribution_id,-999);
25
26 BEGIN
27 select sum(nvl(entered_cr,0) - nvl(entered_dr,0))
28 into l_sum_amount_interfaced
29 from rcv_transactions rcv_txn,
30 rcv_receiving_sub_ledger rcv_sub
31 where rcv_txn.po_distribution_id = l_po_distribution_id
32 and rcv_sub.rcv_transaction_id = rcv_txn.transaction_id
33 and rcv_sub.pa_addition_flag in ('Y','I')
34 and ((rcv_txn.destination_type_code ='EXPENSE') OR
35 /*and ((rcv_txn.destination_type_code ='EXPENSE' AND rcv_txn.transaction_type <> 'RETURN TO RECEIVING') OR */--Bug4630478
36 (rcv_txn.destination_type_code='RECEIVING' AND (rcv_txn.transaction_type IN ('RETURN TO VENDOR','RETURN TO RECEIVING')) ) );
37
38 IF l_sum_amount_interfaced <> 0 THEN
39 Return 'N';
40 ELSIF l_sum_amount_interfaced = 0 THEN -- added bug 11776830
41 Return 'Y'; -- added bug 11776830
42 END IF;
43
44 EXCEPTION
45
46 WHEN NO_DATA_FOUND THEN
47
48 BEGIN
49
50 select 1
51 into l_uninterfaced_to_pa
52 FROM dual
53 WHERE EXISTS
54 (SELECT 1 FROM rcv_transactions rcv_txn,
55 rcv_receiving_sub_ledger rcv_sub
56 ,po_distributions_all podist/*Bug 3905697*/
57 where rcv_txn.po_distribution_id = l_po_distribution_id
58 and podist.po_distribution_id=rcv_txn.po_distribution_id/*Bug 3905697*/
59 and rcv_sub.code_combination_id = podist.code_combination_id/*Bug 3905697*/
60 and rcv_sub.rcv_transaction_id = rcv_txn.transaction_id
61 and ((rcv_txn.destination_type_code ='EXPENSE') OR
62 (rcv_txn.destination_type_code='RECEIVING' AND (rcv_txn.transaction_type IN ('RETURN TO VENDOR','RETURN TO RECEIVING')) ) )
63 and rcv_sub.pa_addition_flag ='N');
64
65 If l_uninterfaced_to_pa = 1 THEN
66 Return 'N';
67 Else
68 Return 'Y';
69 End If;
70
71
72 EXCEPTION
73
74 WHEN NO_DATA_FOUND THEN
75 Return 'Y';
76 WHEN OTHERS THEN
77 G_err_code := SQLCODE;
78 raise;
79
80 END;
81
82 END;
83
84
85
86 BEGIN
87
88 select 1
89 into l_uninterfaced_to_pa
90 FROM dual
91 WHERE EXISTS
92 (SELECT 1 FROM rcv_transactions rcv_txn,
93 rcv_receiving_sub_ledger rcv_sub
94 ,po_distributions_all podist/*Bug 3905697*/
95 where rcv_txn.po_distribution_id = l_po_distribution_id
96 and podist.po_distribution_id=rcv_txn.po_distribution_id/*Bug 3905697*/
97 and rcv_sub.code_combination_id = podist.code_combination_id/*Bug 3905697*/
98 and rcv_sub.rcv_transaction_id = rcv_txn.transaction_id
99 and ((rcv_txn.destination_type_code ='EXPENSE') OR
100 (rcv_txn.destination_type_code='RECEIVING' AND (rcv_txn.transaction_type IN ('RETURN TO VENDOR','RETURN TO RECEIVING')) ) )
101 and rcv_sub.pa_addition_flag ='N');
102
103 If l_uninterfaced_to_pa = 1 OR l_sum_amount_interfaced <> 0 THEN
104 Return 'N';
105 Else
106 Return 'Y';
107 End If;
108
109
110 EXCEPTION
111
112 WHEN NO_DATA_FOUND THEN
113
114 If l_sum_amount_interfaced <> 0 THEN
115 Return 'N';
116 Else
117 Return 'Y';
118 End If;
119
120 WHEN OTHERS THEN
121 G_err_code := SQLCODE;
122 raise;
123 END;
124
125 Exception
126
127 WHEN OTHERS THEN
128 G_err_code := SQLCODE;
129 raise;
130
131 End Allow_Project_Info_Change;
132
133
134 /*
135 Bug: 9462109
136 Function : Getpolinenum
137 Description: As a part of the CLM enhancment if the procurment document is CLM enabled
138 then the alpha-numeric column LINE_NUM_DISPLAY must be shown as the PO line number
139 else the old column (line_num) . */
140
141
142 Function Getpolinenum (p_po_line_id number) return varchar2 is
143 v_clm_flag varchar2(1);
144 v_clm_line_num varchar2(50);
145 v_line_num varchar2(50);
146
147 begin
148
149 select nvl(clm_flag,'N'), nvl(LINE_NUM_DISPLAY,'XXXX'), to_char(line_num)
150 into v_clm_flag,
151 v_clm_line_num,
152 v_line_num
153 from po_doc_style_headers st, po_headers_all poh, po_lines_all pol
154 where poh.style_id=st.style_id
155 and poh.po_header_id= pol.po_header_id
156 and pol.po_line_id=p_po_line_id;
157
158 if (v_clm_flag='Y') then
159 return v_clm_line_num;
160 else
161 return v_line_num;
162 end if;
163
164 end Getpolinenum;
165
166
167 --Added for bug 4407908
168 /*This is a public API, which will update PA_ADDITION_FLAG in
169 rcv_receiving_sub_ledger table. This API will be called from
170 purchasing module at the time of receipt creation.*/
171
172 PROCEDURE Update_PA_Addition_Flg (p_api_version IN NUMBER,
173 p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE,
174 p_commit IN VARCHAR2 default FND_API.G_FALSE,
175 p_validation_level IN NUMBER default FND_API.G_VALID_LEVEL_FULL,
176 x_return_status OUT NOCOPY VARCHAR2,
177 x_msg_count OUT NOCOPY NUMBER,
178 x_msg_data OUT NOCOPY VARCHAR2,
179 p_rcv_transaction_id IN NUMBER,
180 p_po_distribution_id IN NUMBER,
181 p_accounting_event_id IN NUMBER)
182 IS
183 /*l_project_id po_distributions_all.project_id%type; Bug 5585218 */
184 l_po_distribution_id po_distributions_all.po_distribution_id%type;
185 l_rcv_transaction_id rcv_receiving_sub_ledger.rcv_transaction_id%type;
186 l_processed Number := 0;
187
188 PROCEDURE net_zero_adj_po IS
189
190 l_old_stack VARCHAR2(630);
191 l_rcv_transaction_id1 NUMBER(15);
192 l_po_dist_id NUMBER(15);
193 l_num_rows NUMBER(15):=0;
194
195 l_rcv_txn_id_tbl PA_PLSQL_DATATYPES.IdTabTyp;
196 l_po_dist_id_tbl PA_PLSQL_DATATYPES.IdTabTyp;
197 l_rcv_acct_evt_tbl PA_PLSQL_DATATYPES.IdTabTyp; -- pricing changes
198
199
200 CURSOR net_zero_po_proj (p_transaction_id IN number) IS /* Modified the cursor query for Bug 5585218 */
201 SELECT /*+ leading(rcv_txn) index(rcvsub RCV_RECEIVING_SUB_LEDGER_N1) */ rcv_txn.transaction_id /*4338075*/
202 ,rcv_txn.po_distribution_id
203 ,rcvsub.accounting_event_id -- pricing changes
204 FROM rcv_transactions rcv_txn
205 ,po_distributions podist
206 ,rcv_receiving_sub_ledger rcvsub
207 WHERE rcv_txn.transaction_id = rcvsub.rcv_transaction_id
208 AND rcv_txn.parent_transaction_id = (SELECT parent_transaction_id
209 FROM rcv_transactions rcv_txn3
210 WHERE rcv_txn3.transaction_id = p_transaction_id)
211 and rcv_txn.po_distribution_id = podist.po_distribution_id
212 and podist.code_combination_id = rcvsub.code_combination_id
213 and rcvsub.actual_flag = 'A'
214 and podist.accrue_on_receipt_flag = 'Y'
215 /*and podist.project_id = p_project_id Bug 5585218 */
216 and rcvsub.pa_addition_flag = 'N' -- pricing changes
217 and ((rcv_txn.destination_type_code = 'EXPENSE' ) OR
218 (rcv_txn.destination_type_code = 'RECEIVING' AND
219 rcv_txn.transaction_type in ('RETURN TO VENDOR','RETURN TO RECEIVING')
220 ))
221 and 0 = (SELECT /*+ INDEX(RCV_TXN2 RCV_TRANSACTIONS_N1) */sum(nvl(rcvsub2.entered_dr,0)-nvl(rcvsub2.entered_cr,0))/*4338075*/
222 FROM rcv_transactions rcv_txn2
223 ,rcv_receiving_sub_ledger rcvsub2
224 ,po_distributions podist2
225 WHERE rcv_txn2.transaction_id = rcvsub2.rcv_transaction_id
226 and podist2.po_distribution_id = rcv_txn2.po_distribution_id
227 and podist2.code_combination_id = rcvsub2.code_combination_id
228 and rcvsub2.actual_flag = 'A'
229 and rcv_txn2.parent_transaction_id = rcv_txn.parent_transaction_id
230 and rcvsub2.code_combination_id = rcvsub.code_combination_id
231 and trunc(rcv_txn2.transaction_date) = trunc(rcv_txn.transaction_date)
232 and rcvsub2.pa_addition_flag = 'N' -- pricing changes
233 and rcv_txn2.po_distribution_id = rcv_txn.po_distribution_id
234 and ((rcv_txn2.destination_type_code = 'EXPENSE' ) OR
235 (rcv_txn2.destination_type_code = 'RECEIVING' AND
236 rcv_txn2.transaction_type in ('RETURN TO VENDOR','RETURN TO RECEIVING')
237 ))
238 );
239
240 BEGIN
241
242 l_po_dist_id := l_po_distribution_id;
243
244 -- l_old_stack := G_err_stack;
245 -- G_err_stack := G_err_stack || '->PAAPIMP_PKG.net_zero_adj_po';
246 -- G_err_code := 0;
247 -- G_err_stage := 'UPDATING RCV TRANSACTIONS FOR net_zero_adj_po';
248
249 -- write_log(LOG, G_err_stack);
250
251 OPEN net_zero_po_proj (l_rcv_transaction_id);
252
253 l_rcv_txn_id_tbl.delete;
254 l_po_dist_id_tbl.delete;
255 l_rcv_acct_evt_tbl.delete; -- pricing changes
256
257 FETCH net_zero_po_proj BULK COLLECT INTO l_rcv_txn_id_tbl
258 ,l_po_dist_id_tbl
259 ,l_rcv_acct_evt_tbl; -- pricing changes
260
261 IF l_rcv_txn_id_tbl.COUNT <> 0 THEN
262
263 FORALL i IN l_rcv_txn_id_tbl.FIRST..l_rcv_txn_id_tbl.LAST
264
265 UPDATE rcv_receiving_sub_ledger rcv_sub
266 SET rcv_sub.pa_addition_flag = 'Z'
267 WHERE rcv_sub.rcv_transaction_id = l_rcv_txn_id_tbl(i) --pricing changes
268 AND rcv_sub.pa_addition_flag = 'N'
269 AND (rcv_sub.accounting_event_id = l_rcv_acct_evt_tbl(i) --pricing changes
270 OR rcv_sub.accounting_event_id IS NULL); --pricing changes
271 l_num_rows := SQL%ROWCOUNT;
272
273 END IF;
274
275 -- write_log (LOG,'Total number of transctions updated to Z:'||l_num_rows);
276
277 l_rcv_txn_id_tbl.delete;
278 l_po_dist_id_tbl.delete;
279 l_rcv_acct_evt_tbl.delete; -- pricing changes
280
281 CLOSE net_zero_po_proj;
282
283 EXCEPTION
284 WHEN Others THEN
285
286 IF net_zero_po_proj%ISOPEN THEN
287 CLOSE net_zero_po_proj;
288 END IF;
289
290 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
291
292 -- G_err_stack := l_old_stack;
293 -- G_err_code := SQLCODE;
294 raise;
295 END net_zero_adj_po;
296
297 BEGIN
298
299 x_return_status := FND_API.G_RET_STS_SUCCESS;
300
301 l_po_distribution_id := p_po_distribution_id;
302 l_rcv_transaction_id := p_rcv_transaction_id;
303
304
305 UPDATE rcv_receiving_sub_ledger rcv_sub
306 SET rcv_sub.pa_addition_flag = NULL
307 WHERE rcv_sub.pa_addition_flag ='N'
308 AND rcv_sub.rcv_transaction_id = l_rcv_transaction_id
309 AND EXISTS
310 (
311 SELECT 'X'
312 FROM rcv_transactions rcv_txn
313 WHERE rcv_txn.TRANSACTION_ID = rcv_sub.RCV_TRANSACTION_ID
314 AND ((rcv_txn.destination_type_code IN ('INVENTORY','MULTIPLE','SHOP FLOOR')
315 OR
316 (rcv_txn.destination_type_code = 'RECEIVING'
317 AND
318 (rcv_txn.transaction_type NOT IN ('RETURN TO VENDOR','RETURN TO RECEIVING')
319 )
320 )
321 )
322 OR
323 (EXISTS
324 (SELECT po_distribution_id
325 FROM po_distributions po_dist
326 WHERE po_dist.po_distribution_id = rcv_txn.po_distribution_id
327 AND ((rcv_txn.destination_type_code = 'EXPENSE' AND
328 po_dist.project_id IS NULL)
329 OR
330 (rcv_txn.destination_type_code = 'EXPENSE' AND
331 nvl(po_dist.project_id,0) > 0 AND
332 po_dist.accrue_on_receipt_flag = 'N')
333 OR
334 (rcv_txn.destination_type_code = 'RECEIVING' AND
335 po_dist.project_id IS NULL)
336 OR
337 (rcv_txn.destination_type_code = 'RECEIVING' AND
338 po_dist.project_id IS NOT NULL AND
339 po_dist.accrue_on_receipt_flag = 'N')
340 )
341 )
342 ) OR
343 ( pa_nl_installed.is_nl_installed = 'Y' --EIB trackable items
344 AND EXISTS (SELECT 'X'
345 FROM mtl_system_items si,
346 po_lines_all pol,
347 po_distributions_all po_dist1
348 WHERE po_dist1.po_line_id = pol.po_line_id
349 AND po_dist1.po_distribution_id = rcv_txn.po_distribution_id
350 AND si.inventory_item_id = pol.item_id
351 AND po_dist1.project_id IS NOT NULL
352 AND si.comms_nl_trackable_flag = 'Y')
353 ) OR
354 (
355 rcv_sub.actual_flag <> 'A'
356 )
357 )
358 );
359
360 l_processed := SQL%ROWCOUNT ;
361
362 UPDATE rcv_receiving_sub_ledger rcv_sub
363 SET rcv_sub.pa_Addition_Flag = 'X'
364 WHERE rcv_sub.pa_addition_flag IN ('N','I')
365 AND rcv_sub.rcv_transaction_id = l_rcv_transaction_id
366 AND EXISTS
367 ( SELECT po_dist.code_combination_id
368 FROM Rcv_Transactions rcv_txn, PO_Distributions po_dist
369 WHERE
370 (
371 (rcv_txn.destination_type_code ='EXPENSE' )
372 OR (rcv_txn.destination_type_code = 'RECEIVING'
373 AND (rcv_txn.transaction_type
374 IN ('RETURN TO VENDOR','RETURN TO RECEIVING')))
375 )
376 -- AND rcv_txn.transaction_date <= nvl(G_GL_DATE,rcv_txn.transaction_date)
377 AND rcv_txn.PO_DISTRIBUTION_ID = po_dist.po_distribution_id
378 AND rcv_sub.code_combination_id <> po_dist.code_combination_id
379 AND rcv_sub.rcv_transaction_id = rcv_txn.transaction_id
380 AND rcv_sub.actual_flag = 'A'
381 -- AND po_dist.expenditure_item_date <= nvl(G_TRANSACTION_DATE,po_dist.expenditure_item_date)
382 AND po_dist.project_ID > 0
383 AND po_dist.accrue_on_receipt_flag= 'Y') ;
384
385 l_processed := l_processed + SQL%ROWCOUNT ;
386
387 IF (l_processed = 0) THEN
388 net_zero_adj_po();
389 END IF ;
390
391 EXCEPTION
392 WHEN OTHERS THEN
393 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
394 raise;
395 END Update_PA_Addition_Flg;
396
397 /* Function to check whether the user has access to a particular project ie, he is a key member on
398 that project. This is used in PA_PO_PROJECTS_EXPEND_V to restrict the projects which user is key member on,in
399 Project LOV in Procurement.
400 This will be called from PO context and the profile PO_ENFORCE_PROJ_SECURITY will be used to enforce the same.
401 */
402 FUNCTION PA_USER_PO_ACCESS_PROJ(x_proj_id IN NUMBER,
403 x_proj_user_id IN NUMBER)
404 RETURN varchar2
405 is
406 l_user_c NUMBER :=0;
407 begin
408
409 /*To check if employee has key member access for a project*/
410 -- Bug 8937044 joined with fnd_user
411 select count(*)
412 into l_user_c
413 from per_all_people_f papf
414 where papf.person_id=(select max(employee_id) from fnd_user where user_id = x_proj_user_id)
415 and trunc(sysdate) between papf.EFFECTIVE_START_DATE
416 and Nvl(papf.effective_end_date, Sysdate + 1)
417 and papf.person_id in
418 (
419 select f.person_id
420 from pa_project_parties pp, pa_resources pr,PA_PROJ_ROLES_V ppr
421 ,per_all_people_f f, pa_resource_txn_attributes ptn
422 where pp.resource_id = pr.resource_id
423 and trunc(sysdate) between pp.start_date_active and Nvl(pp.end_date_active, Sysdate + 1) -- Bug 8943693
424 and pp.project_role_id = ppr.project_role_id
425 and ptn.resource_id=pp.resource_id
426 and f.person_id=ptn.person_id
427 and pp.project_id=x_proj_id);
428
429 if l_user_c = 0
430 then return 'N';
431 else
432 return 'Y';
433 end if;
434
435 EXCEPTION
436 WHEN NO_DATA_FOUND THEN
437 return 'N';
438 WHEN OTHERS THEN
439 RAISE;
440
441 end PA_USER_PO_ACCESS_PROJ;
442
443 /* Function to impose project based security in Procurement.It has two modes :UPDATE and VIEW
444 * UPDATE : This mode should check if a person has access to 'ALL' the projects
445 * in a PO.
446 * VIEW :This mode should check if person has access to atleast 'ONE' project on
447 * a PO.
448 * This is used to restrict the access on PO for which the user is not key member on included projects.
449 *This will be called from PO context and the profile PO_ENFORCE_PROJ_SECURITY will be used to enforce the same.
450 */
451 FUNCTION PA_USER_PO_ACCESS_CHECK(x_po_header_id IN NUMBER,
452 x_proj_user_id IN NUMBER,
453 x_mode IN VARCHAR2 DEFAULT 'VIEW' /* Mode can have 2 values 'VIEW' or 'UPDATE'*/
454 )
455 RETURN VARCHAR2 IS
456
457 l_profile_value VARCHAR2(1) := NULL;
458 l_proj_id NUMBER := 0;
459 l_user_c NUMBER :=0;
460
461 cursor c_proj is
462 select distinct project_id from po_distributions_all
463 where po_header_id = x_po_header_id
464 and project_id is not NULL; /* added condition for 8920005 */
465
466 BEGIN
467 /*Profile to check project based security in procurement*/
468 l_profile_value := NVL(FND_PROFILE.value('PO_ENFORCE_PROJ_SECURITY'), 'N');
469 --l_profile_value :='Y';
470 if l_profile_value = 'Y' then
471 /*To check if employee has access to all projects on a PO*/
472 if x_mode = 'UPDATE' then
473 open c_proj;
474 loop
475 fetch c_proj into l_proj_id;
476 exit when c_proj%NOTFOUND;
477
478 select count(*)
479 into l_user_c
480 from per_all_people_f papf
481 where papf.person_id=x_proj_user_id
482 and trunc(sysdate) between papf.EFFECTIVE_START_DATE
483 and Nvl(papf.effective_end_date, Sysdate + 1)
484 and papf.person_id in
485 (
486 select f.person_id
487 from pa_project_parties pp, pa_resources pr,PA_PROJ_ROLES_V ppr
488 ,per_all_people_f f, pa_resource_txn_attributes ptn
489 where pp.resource_id = pr.resource_id
490 and trunc(sysdate) between pp.start_date_active and Nvl(pp.end_date_active, Sysdate + 1) -- Bug 8943693
491 and pp.project_role_id = ppr.project_role_id
492 and ptn.resource_id=pp.resource_id
493 and f.person_id=ptn.person_id
494 and pp.project_id=l_proj_id);
495
496 if l_user_c = 0
497 then return 'N';
498 end if;
499 end loop;
500 close c_proj;
501 return 'Y';
502
503 end if; --end for mode=update
504
505 /*To check if employee has access to atleast one project on a PO*/
506 if x_mode = 'VIEW' then
507 open c_proj;
508 loop
509 fetch c_proj into l_proj_id;
510 exit when c_proj%NOTFOUND;
511
512 select count(*)
513 into l_user_c
514 from per_all_people_f papf
515 where papf.person_id=x_proj_user_id
516 and trunc(sysdate) between papf.EFFECTIVE_START_DATE
517 and Nvl(papf.effective_end_date, Sysdate + 1)
518 and papf.person_id in
519 (
520 select f.person_id
521 from pa_project_parties pp, pa_resources pr,PA_PROJ_ROLES_V ppr
522 ,per_all_people_f f, pa_resource_txn_attributes ptn
523 where pp.resource_id = pr.resource_id
524 and trunc(sysdate) between pp.start_date_active and Nvl(pp.end_date_active, Sysdate + 1) -- Bug 8943693
525 and pp.project_role_id = ppr.project_role_id
526 and ptn.resource_id=pp.resource_id
527 and f.person_id=ptn.person_id
528 and pp.project_id=l_proj_id);
529
530 if l_user_c = 1
531 then return 'Y';
532 end if;
533 end loop;
534 --Changes for 8830122 follows
535 if c_proj%ROWCOUNT = 0
536 then
537 return 'Y';
538 else
539 return 'N';
540 end if; --end for %ROWCOUNT check
541 --Changes for 8830122 end
542 end if; --end for mode=view
543 else
544 return 'Y';
545 end if; --end for profile check
546 EXCEPTION
547 WHEN NO_DATA_FOUND THEN
548 return 'N';
549 WHEN OTHERS THEN
550 RAISE;
551 END PA_USER_PO_ACCESS_CHECK;
552
553
554
555 -- Added the below procedure as a part of Novation Enhancement.
556
557 PROCEDURE validate_novation_projects (
558 p_request_id IN NUMBER,
559 p_vendor_id IN NUMBER,
560 p_novation_date IN DATE,
561 p_header_id_tbl IN PO_TBL_NUMBER,
562 x_validation_results IN OUT NOCOPY po_multi_mod_val_results_type,
563 x_validation_result_type OUT NOCOPY VARCHAR2,
564 x_return_status OUT NOCOPY VARCHAR2,
565 x_error_msg OUT NOCOPY VARCHAR2 ) IS
566
567 --Cursor to check the budget which is defined for the Old supplier.
568
569 CURSOR c_transf_budg_tonewsupp(p_po_header_id IN NUMBER, p_vendor_id IN NUMBER) IS
570 SELECT DISTINCT ppa.segment1 -- Added for the bug 13617899
571 FROM po_distributions_all pod ,
572 pa_budget_versions pbv ,
573 pa_budgetary_control_options pbco,
574 pa_budget_entry_methods pbe ,
575 pa_budget_lines pbl ,
576 pa_resource_assignments pra ,
577 pa_resource_list_members prlm,
578 pa_projects_all ppa -- Added for the bug 13617899
579 WHERE pod.po_header_id =p_po_header_id
580 AND ppa.project_id = pod.project_id -- Added for the bug 13617899
581 AND pod.project_id = pbv.project_id
582 AND pod.project_id =pbco.project_id
583 AND pbco.bdgt_cntrl_flag ='Y'
584 AND pbv.budget_type_code ='AC'
585 AND ((pbv.budget_status_code ='B'
586 AND pbv.current_flag ='Y')
587 OR (pbv.budget_status_code ='W')) -- Modified for the bug 13566375
588 AND pbe.budget_entry_method_code =pbv.budget_entry_method_code
589 AND pbe.categorization_code ='R'
590 AND pbl.budget_version_id =pbv.budget_version_id
591 AND pbl.resource_assignment_id =pra.resource_assignment_id
592 AND prlm.resource_list_member_id =pra.resource_list_member_id
593 AND prlm.vendor_id =p_vendor_id;
594
595
596 --Cursor to check if there are any Pending Receipts.
597
598 CURSOR c_pending_receipts(p_po_header_id IN NUMBER) IS
599 SELECT DISTINCT rsh.receipt_num, rsh.creation_date -- Added for the bug 13617899
600 FROM Rcv_Transactions rcv_txn ,
601 rcv_shipment_headers rsh ,
602 PO_Distributions_all po_dist ,
603 rcv_receiving_sub_ledger rcv_sub1
604 WHERE po_dist.po_header_id =p_po_header_id
605 AND rcv_txn.destination_type_code ='EXPENSE'
606 AND rcv_txn.PO_DISTRIBUTION_ID = po_dist.po_distribution_id
607 AND rcv_sub1.code_combination_id = po_dist.code_combination_id
608 AND rcv_sub1.rcv_transaction_id = rcv_txn.transaction_id
609 AND rsh.shipment_header_id = rcv_txn.shipment_header_id
610 AND po_dist.accrue_on_receipt_flag = 'Y'
611 AND rcv_sub1.pa_addition_flag = 'N'
612 AND rcv_sub1.actual_flag = 'A';
613
614
615 --Cursor to check if there are any Commitments for the PO.
616
617 CURSOR c_po_commitments(p_po_header_id IN NUMBER) IS
618 SELECT DISTINCT ppd.po_number, ppd.creation_date -- Added for the bug 13617899
619 FROM pa_proj_po_distributions ppd
620 WHERE ppd.po_header_id=p_po_header_id
621 AND ppd.cmt_qty <> 0; -- Modified for the bug 13566375
622
623
624 --Cursor to check if there are any budgetary control defined for the Old Supplier.
625 -- modified below cursor for the bug 13617899 and added pa_projects_all,pa_budget_types and pa_budget_versions tables
626
627 CURSOR c_check_supp_budgctrl(p_po_header_id IN NUMBER, p_vendor_id IN NUMBER) IS
628 SELECT DISTINCT ppa.segment1, pbt.budget_type -- Added for the bug 13617899
629 FROM pa_budgetary_controls pbc,
630 pa_resource_list_members prlm,
631 pa_projects_all ppa,
632 pa_budget_types pbt,
633 pa_budget_versions pbv
634 WHERE prlm.vendor_id = p_vendor_id
635 AND ppa.project_id=pbc.project_id
636 AND pbv.project_id=pbc.project_id
637 AND ((pbv.budget_status_code ='B'
638 AND pbv.current_flag ='Y')
639 OR (pbv.budget_status_code ='W'))
640 AND pbt.budget_type_code=pbc.budget_type_code
641 AND pbc.resource_list_member_id=prlm.resource_list_member_id;
642
643
644 --Cursor to check if there are any pending deliverables.
645
646 CURSOR c_pending_deliverables(p_po_header_id IN NUMBER) IS
647 SELECT DISTINCT OD.business_document_number, OD.creation_date -- Added for the bug 13617899
648 FROM OKC_DELIVERABLES OD ,
649 hz_parties HP ,
650 HR_ALL_ORGANIZATION_UNITS org ,
651 okc_resp_parties_tl resp_party_tl
652 WHERE OD.BUSINESS_DOCUMENT_ID = p_po_header_id
653 AND OD.BUSINESS_DOCUMENT_TYPE = 'PO_STANDARD'
654 AND OD.external_party_contact_id = HP.party_id (+)
655 AND OD.internal_party_id = org.organization_id (+)
656 AND OD.DELIVERABLE_STATUS IN ('OPEN','SUBMITTED')
657 AND OD.MANAGE_YN = 'Y'
658 AND OD.responsible_party = resp_party_tl.resp_party_code
659 AND resp_party_tl.language = USERENV('LANG')
660 AND resp_party_tl.document_type_class = 'PO';
661
662
663 --Cursor to update the change document to reference the new supplier.
664
665 CURSOR c_update_doc(p_po_header_id IN NUMBER) IS
666 SELECT DISTINCT pcsd.ci_id, pcsd.creation_date -- Added for the bug 13617899
667 FROM PA_CI_SUPPLIER_DETAILS pcsd,
668 PA_CONTROL_ITEMS pci
669 WHERE pcsd.PO_HEADER_ID = p_po_header_id
670 AND pcsd.CI_ID = pci.CI_ID
671 AND pci.STATUS_CODE NOT IN ('CI_APPROVED', 'CI_CANCELED', 'CI_CLOSED' );
672
673
674 l_po_header_id po_headers_all.po_header_id%TYPE;
675 l_vendor_id po_headers_all.vendor_id%TYPE;
676 l_pji_source_flag pa_projects_all.pji_source_flag%TYPE;
677 l_multi_mod_val_result_id NUMBER;
678 l_exception_type VARCHAR2(200) := NULL ;
679 l_message_name VARCHAR2(200) := NULL;
680 -- l_count NUMBER :=0; commented for the bug 13617899
681 l_po_number po_headers_all.segment1%TYPE;
682 l_related_document_number VARCHAR2(200) := NULL ; -- Added for the bug 13617899
683
684 BEGIN
685 x_validation_result_type := 'SUCCESS';
686
687 IF (x_validation_results IS NULL) THEN
688 x_validation_results := po_multi_mod_val_results_type.new_instance();
689 End IF;
690
691 l_vendor_id := p_vendor_id;
692
693 FOR i IN 1 .. p_header_id_tbl.Count LOOP
694
695 l_po_header_id := p_header_id_tbl(i);
696
697 SELECT segment1
698 INTO l_po_number
699 FROM po_headers_all
700 WHERE po_header_id=l_po_header_id;
701
702 ----------------Transfering the budget, which is defined for the Old supplier on to the New supplier.-----------------
703
704
705 l_exception_type := 'PA_NOV_TRFBUDG_OLD_TO_NEW_SUP';
706 l_message_name := 'PA_NOV_TRFBUDG_OLD_TO_NEW_SUP';
707 x_validation_result_type := 'WARNING';
708
709
710 FOR erec IN c_transf_budg_tonewsupp(l_po_header_id, l_vendor_id) LOOP
711
712 SELECT po_multi_mod_val_results_s.nextval
713 INTO l_multi_mod_val_result_id
714 FROM dual;
715
716 x_validation_results.add_result (p_multi_mod_val_result_id => l_multi_mod_val_result_id,
717 p_multi_mod_request_id => p_request_id,
718 p_result_type => 'WARNING',
719 p_validation_type => 'PA_PROJECT',
720 p_exception_type => l_exception_type,
721 p_document_id => l_po_header_id,
722 p_document_number => l_po_number,
723 p_related_document_id => NULL,
724 p_related_document_number => erec.segment1,
725 p_related_document_date => NULL,
726 p_related_document_amount => NULL,
727 p_message_application => 'PA',
728 p_message_name => l_message_name);
729
730 END LOOP;
731
732
733
734 ------------END for Transfering the budget, which is defined for the Old supplier on to the New supplier.-----------------
735
736
737 l_exception_type := NULL;
738 l_message_name := NULL;
739 -- l_count :=0;
740
741
742 --------------- Interface the pending receipts to the porjects.-----------------
743
744
745 l_exception_type := 'PA_NOV_INT_PEND_RECEI_TO_PRJ';
746 l_message_name := 'PA_NOV_INT_PEND_RECEI_TO_PRJ';
747 x_validation_result_type := 'WARNING';
748
749 FOR erec IN c_pending_receipts( l_po_header_id ) LOOP
750
751 SELECT po_multi_mod_val_results_s.nextval
752 INTO l_multi_mod_val_result_id
753 FROM dual;
754
755 x_validation_results.add_result (p_multi_mod_val_result_id => l_multi_mod_val_result_id,
756 p_multi_mod_request_id => p_request_id,
757 p_result_type => 'WARNING',
758 p_validation_type => 'REC',
759 p_exception_type => l_exception_type,
760 p_document_id => l_po_header_id,
761 p_document_number => l_po_number,
762 p_related_document_id => NULL,
763 p_related_document_number => erec.receipt_num,
764 p_related_document_date => erec.creation_date,
765 p_related_document_amount => NULL,
766 p_message_application => 'PA',
767 p_message_name => l_message_name);
768
769 END LOOP;
770
771
772 -----------END Interface the pending receipts to the porjects.----------------
773
774
775
776 l_exception_type := NULL;
777 l_message_name := NULL;
778 -- l_count :=0;
779
780
781 ---------------Running Summarization process after completing the Novation.-----------------
782
783
784 SELECT NVL(pp.pji_source_flag,'N')
785 INTO l_pji_source_flag
786 FROM po_headers_all poh,
787 po_distributions_all pod,
788 pa_projects_all pp
789 WHERE poh.po_header_id=l_po_header_id
790 AND poh.po_header_id =pod.po_header_id
791 AND pod.project_id =pp.project_id;
792
793
794 IF l_pji_source_flag='Y' then
795 l_exception_type := 'PA_NOV_RUN_SUMM_NEW_PROSS';
796 l_message_name := 'PA_NOV_RUN_SUMM_NEW_PROSS';
797 ELSE
798 l_exception_type := 'PA_NOV_RUN_SUMM_OLD_PROSS';
799 l_message_name := 'PA_NOV_RUN_SUMM_OLD_PROSS';
800 END IF;
801
802 x_validation_result_type := 'WARNING';
803
804
805 FOR erec IN c_po_commitments( l_po_header_id ) LOOP
806
807 SELECT po_multi_mod_val_results_s.nextval
808 INTO l_multi_mod_val_result_id
809 FROM dual;
810
811 x_validation_results.add_result (p_multi_mod_val_result_id => l_multi_mod_val_result_id,
812 p_multi_mod_request_id => p_request_id,
813 p_result_type => 'WARNING',
814 p_validation_type => 'AWARD',
815 p_exception_type => l_exception_type,
816 p_document_id => l_po_header_id,
817 p_document_number => l_po_number,
818 p_related_document_id => NULL,
819 p_related_document_number => erec.po_number,
820 p_related_document_date => erec.creation_date,
821 p_related_document_amount => NULL,
822 p_message_application => 'PA',
823 p_message_name => l_message_name);
824
825 END LOOP;
826
827
828 ---------------END Running Summarization process after completing the Novation.-----------------
829
830
831 l_exception_type := NULL;
832 l_message_name := NULL;
833 -- l_count :=0;
834
835
836 -----------Budgetary control defined for the Old Supplier.----------------
837
838
839 l_exception_type := 'PA_NOV_CHK_BUDG_CTRL_FOR_SUP';
840 l_message_name := 'PA_NOV_CHK_BUDG_CTRL_FOR_SUP';
841 x_validation_result_type := 'WARNING';
842
843 FOR erec IN c_check_supp_budgctrl(l_po_header_id, l_vendor_id) LOOP
844
845 SELECT po_multi_mod_val_results_s.nextval
846 INTO l_multi_mod_val_result_id
847 FROM dual;
848
849 l_related_document_number :=erec.segment1||'/'||erec.budget_type;
850
851 x_validation_results.add_result (p_multi_mod_val_result_id => l_multi_mod_val_result_id,
852 p_multi_mod_request_id => p_request_id,
853 p_result_type => 'WARNING',
854 p_validation_type => 'PA_PROJECT_BUDGTYPE',
855 p_exception_type => l_exception_type,
856 p_document_id => l_po_header_id,
857 p_document_number => l_po_number,
858 p_related_document_id => NULL,
859 p_related_document_number => l_related_document_number,
860 p_related_document_date => NULL,
861 p_related_document_amount => NULL,
862 p_message_application => 'PA',
863 p_message_name => l_message_name);
864
865 END LOOP;
866
867
868 -----------END Budgetary control defined for the Old Supplier.----------------
869
870 l_exception_type := NULL;
871 l_message_name := NULL;
872 -- l_count :=0;
873
874
875 -----------Pending deliverables available for the old supplier ----------------
876
877
878 l_exception_type := 'PA_NOV_PEND_DELIVERABLES';
879 l_message_name := 'PA_NOV_PEND_DELIVERABLES';
880 x_validation_result_type := 'WARNING';
881
882 FOR erec IN c_pending_deliverables(l_po_header_id) LOOP
883
884 SELECT po_multi_mod_val_results_s.nextval
885 INTO l_multi_mod_val_result_id
886 FROM dual;
887
888 x_validation_results.add_result (p_multi_mod_val_result_id => l_multi_mod_val_result_id,
889 p_multi_mod_request_id => p_request_id,
890 p_result_type => 'WARNING',
891 p_validation_type => 'PA_DELIVERABLE',
892 p_exception_type => l_exception_type,
893 p_document_id => l_po_header_id,
894 p_document_number => l_po_number,
895 p_related_document_id => NULL,
896 p_related_document_number => erec.business_document_number,
897 p_related_document_date => erec.creation_date,
898 p_related_document_amount => NULL,
899 p_message_application => 'PA',
900 p_message_name => l_message_name);
901
902 END LOOP;
903
904
905 -----------END Pending deliverables available for the old supplier ----------------
906
907
908 l_exception_type := NULL;
909 l_message_name := NULL;
910 -- l_count :=0;
911
912
913 ------------Change document to reference the new supplier----------------
914
915
916 l_exception_type := 'PA_NOV_SUPPLIER_CHNG';
917 l_message_name := 'PA_NOV_SUPPLIER_CHNG';
918 x_validation_result_type := 'WARNING';
919
920
921 FOR erec IN c_update_doc(l_po_header_id) LOOP
922
923 SELECT po_multi_mod_val_results_s.nextval
924 INTO l_multi_mod_val_result_id
925 FROM dual;
926
927 x_validation_results.add_result (p_multi_mod_val_result_id => l_multi_mod_val_result_id,
928 p_multi_mod_request_id => p_request_id,
929 p_result_type => 'WARNING',
930 p_validation_type => 'PA_CHANGE_REQUEST_ORDER',
931 p_exception_type => l_exception_type,
932 p_document_id => l_po_header_id,
933 p_document_number => l_po_number,
934 p_related_document_id => NULL,
935 p_related_document_number => erec.ci_id,
936 p_related_document_date => erec.creation_date,
937 p_related_document_amount => NULL,
938 p_message_application => 'PA',
939 p_message_name => l_message_name);
940
941 END LOOP;
942
943
944 ------------Change document to reference the new supplier----------------
945
946 END LOOP;
947
948 EXCEPTION
949 WHEN OTHERS
950 THEN
951
952 x_validation_result_type := 'WARNING';
953 x_return_status := fnd_api.g_ret_sts_error;
954 x_error_msg := SUBSTR(SQLERRM, 1, 200);
955 END validate_novation_projects;
956
957 End pa_po_integration_utils;
[Home] [Help]