[Home] [Help]
PACKAGE BODY: APPS.PO_MASS_UPDATE_PO_PVT
Source
1 PACKAGE BODY PO_Mass_Update_PO_PVT AS
2 /* $Header: PO_Mass_Update_PO_PVT.plb 120.11.12020000.3 2013/02/10 20:55:25 vegajula ship $*/
3
4 --------------------------------------------------------------------------------------------------
5
6 -- Call is made such that the sql file POXMUB.sql calls the procedure
7 -- PO_Mass_Update_PO_GRP.Update_Persons
8 -- PO_Mass_Update_PO_GRP.Update_Persons calls the procedure PO_Mass_Update_PO_PVT.DO_Update
9
10 --------------------------------------------------------------------------------------------------
11
12 g_debug_stmt CONSTANT BOOLEAN := PO_DEBUG.is_debug_stmt_on;
13 g_debug_unexp CONSTANT BOOLEAN := PO_DEBUG.is_debug_unexp_on;
14 g_pkg_name CONSTANT VARCHAR2(100) := 'PO_Mass_Update_PO_PVT';
15 g_log_head CONSTANT VARCHAR2(1000) := 'po.plsql.' || g_pkg_name || '.';
16
17 TYPE g_po IS REF CURSOR;
18
19 TYPE g_rel IS REF CURSOR;
20
21 TYPE g_po_approver is REF CURSOR;
22
23 TYPE g_rel_approver is REF CURSOR;
24
25 --------------------------------------------------------------------------------------------------
26 -- Start of Comments
27
28 -- API Name : Do_Update
29 -- Type : Private
30 -- Pre-reqs : None
31 -- Function : Calls the procedure Update_Buyer/Update_Approver/Update_Deliver_To
32 -- or All of the above to update the Buyer/Approver/Deliver_To person
33 -- accordingly to the input received from the Update_Person parameter value set.
34 -- Parameters :
35
36 -- IN : p_update_person Person needs to be updated(Buyer/Approver/Deliver_To).
37 -- p_old_personid Id of the old person.
38 -- p_new_personid Id of the new person.
39 -- p_document_type Type of the document(STANDARD,BLANKET.CONTRACT,PLANNED).
40 -- p_document_no_from Document number from.
41 -- p_document_no_to Document number to.
42 -- p_date_from Date from.
43 -- p_date_to Date to.
44 -- p_supplier_id Supplier id.
45 -- p_include_close_po Include Close PO's or not (Value as Yes or No).
46 -- p_commit_interval Commit interval.
47
48 -- OUT : p_msg_data Actual message in encoded format.
49 -- p_msg_count Holds the number of messages in the API list.
50 -- p_return_status Return status of the API (Includes 'S','E','U').
51
52 -- End of Comments
53 --------------------------------------------------------------------------------------------------
54
55 PROCEDURE DO_Update(p_update_person IN VARCHAR2,
56 p_old_personid IN NUMBER,
57 p_new_personid IN NUMBER,
58 p_document_type IN VARCHAR2,
59 p_document_no_from IN VARCHAR2,
60 p_document_no_to IN VARCHAR2,
61 p_date_from IN DATE,
62 p_date_to IN DATE,
63 p_supplier_id IN NUMBER,
64 p_include_close_po IN VARCHAR2,
65 p_commit_interval IN NUMBER,
66 p_msg_data OUT NOCOPY VARCHAR2,
67 p_msg_count OUT NOCOPY NUMBER,
68 p_return_status OUT NOCOPY VARCHAR2) IS
69
70 l_progress VARCHAR2(3) := '000';
71 l_log_head CONSTANT VARCHAR2(1000) := g_log_head||'Do_Update';
72 x_valid_buyer VARCHAR2(10) := 'N';
73 l_return_status VARCHAR2(1);
74
75 BEGIN
76
77 IF g_debug_stmt THEN
78
79 PO_DEBUG.debug_begin(l_log_head);
80 PO_DEBUG.debug_var(l_log_head,l_progress,'p_update_person',p_update_person );
81 PO_DEBUG.debug_var(l_log_head,l_progress,'p_old_personid', p_old_personid);
82 PO_DEBUG.debug_var(l_log_head,l_progress,'p_new_personid', p_new_personid);
83 PO_DEBUG.debug_var(l_log_head,l_progress,'p_document_type',p_document_type );
84 PO_DEBUG.debug_var(l_log_head,l_progress,'p_document_no_from',p_document_no_from );
85 PO_DEBUG.debug_var(l_log_head,l_progress,'p_date_from',p_date_from);
86 PO_DEBUG.debug_var(l_log_head,l_progress,'p_date_to',p_date_to);
87 PO_DEBUG.debug_var(l_log_head,l_progress,'p_supplier_id',p_supplier_id);
88 PO_DEBUG.debug_var(l_log_head,l_progress,'p_include_close_po',p_include_close_po);
89 PO_DEBUG.debug_var(l_log_head,l_progress,'p_commit_interval',p_commit_interval);
90
91 END IF;
92
93 SAVEPOINT Do_Update_SP;
94
95 l_progress := '001';
96
97 IF (p_update_person = 'BUYER' OR p_update_person = 'ALL') THEN
98
99 BEGIN
100
101 l_progress := '002';
102
103 IF g_debug_stmt THEN
104
105 PO_DEBUG.debug_var(l_log_head,l_progress,'p_update_person',p_update_person );
106
107 END IF;
108
109 SAVEPOINT PO_Mass_Update_Buyer_SP;
110
111 BEGIN
112 SELECT 'Y' INTO x_valid_buyer
113 FROM po_buyers_val_v
114 WHERE employee_id = p_new_personid;
115 EXCEPTION
116 WHEN NO_DATA_FOUND THEN
117 po_message_s.sql_error('Not a Valid Buyer','Not a Valid Buyer',l_progress,SQLCODE,SQLERRM);
118 fnd_file.put_line(fnd_file.log,fnd_message.get);
119 x_valid_buyer := 'N';
120 END;
121
122 IF x_valid_buyer = 'Y' THEN
123
124
125 Update_Buyer(p_update_person,
126 p_old_personid,
127 p_new_personid,
128 p_document_type,
129 p_document_no_from,
130 p_document_no_to,
131 p_date_from,
132 p_date_to,
133 p_supplier_id,
134 p_include_close_po,
135 p_commit_interval,
136 p_msg_data,
137 p_msg_count,
138 l_return_status);
139
140 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
141 ROLLBACK TO PO_Mass_Update_Buyer_SP;
142 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
143 END IF;
144
145 END IF;
146
147 EXCEPTION
148
149 WHEN OTHERS THEN
150
151 ROLLBACK TO PO_Mass_Update_Buyer_SP;
152
153 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
154
155 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,l_log_head,l_progress|| SQLCODE || SUBSTR(SQLERRM,1,200));
156
157 END IF;
158
159 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
160
161 FND_MSG_PUB.Count_And_Get(
162 p_count => p_msg_count,
163 p_data => p_msg_data);
164
165 END;
166
167 END IF;
168
169 IF (p_update_person = 'APPROVER' OR p_update_person = 'ALL') THEN
170
171 BEGIN
172
173 l_progress := '003';
174
175 IF g_debug_stmt THEN
176
177 PO_DEBUG.debug_var(l_log_head,l_progress,'p_update_person',p_update_person );
178
179 END IF;
180
181 SAVEPOINT PO_Mass_Update_Approver_SP;
182
183 Update_Approver(p_update_person,
184 p_old_personid,
185 p_new_personid,
186 p_document_type,
187 p_document_no_from,
188 p_document_no_to,
189 p_date_from,
190 p_date_to,
191 p_supplier_id,
192 p_include_close_po,
193 p_commit_interval,
194 p_msg_data,
195 p_msg_count,
196 l_return_status);
197
198 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
199 ROLLBACK TO PO_Mass_Update_Approver_SP;
200 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
201 END IF;
202
203
204 EXCEPTION
205
206 WHEN OTHERS THEN
207
208 ROLLBACK TO PO_Mass_Update_Approver_SP;
209
210 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
211
212 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,l_log_head,l_progress|| SQLCODE || SUBSTR(SQLERRM,1,200));
213
214 END IF;
215
216 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
217
218 FND_MSG_PUB.Count_And_Get(
219 p_count => p_msg_count,
220 p_data => p_msg_data);
221
222 END;
223
224 END IF;
225
226 IF (p_update_person = 'DELIVER TO' OR p_update_person = 'ALL') THEN
227
228 BEGIN
229
230 l_progress := '004';
231
232 IF g_debug_stmt THEN
233
234 PO_DEBUG.debug_var(l_log_head,l_progress,'p_update_person',p_update_person );
235
236 END IF;
237
238 SAVEPOINT PO_Mass_Update_Deliver_To_SP;
239
240 Update_Deliver_To(p_update_person,
241 p_old_personid,
242 p_new_personid,
243 p_document_type,
244 p_document_no_from,
245 p_document_no_to,
246 p_date_from,
247 p_date_to,
248 p_supplier_id,
249 p_include_close_po,
250 p_commit_interval,
251 p_msg_data,
252 p_msg_count,
253 l_return_status);
254
255 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
256 ROLLBACK TO PO_Mass_Update_Deliver_To_SP;
257 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
258 END IF;
259
260
261
262
263 EXCEPTION
264
265 WHEN OTHERS THEN
266
267 ROLLBACK TO PO_Mass_Update_Deliver_To_SP;
268
269 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
270
271 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,l_log_head,l_progress|| SQLCODE || SUBSTR(SQLERRM,1,200));
272
273 END IF;
274
275 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
276
277 FND_MSG_PUB.Count_And_Get(
278 p_count => p_msg_count,
279 p_data => p_msg_data);
280
281 END;
282
283 END IF;
284
285 EXCEPTION
286
287 WHEN OTHERS THEN
288
289 ROLLBACK TO Do_Update_SP;
290
291 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
292
293 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,l_log_head,l_progress|| SQLCODE || SUBSTR(SQLERRM,1,200));
294
295 END IF;
296
297 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
298
299 FND_MSG_PUB.Count_And_Get(
300 p_count => p_msg_count,
301 p_data => p_msg_data);
302
303 END DO_Update;
304
305 --------------------------------------------------------------------------------------------------
306 -- Start of Comments
307
308 -- API Name : Update_Buyer
309 -- Type : Private
310 -- Pre-reqs : None
311 -- Function : Updates the old buyer with the new buyer provided and also updates the
312 -- worklfow attributes when the PO and release are in Inprocess and
313 -- Pre-approved status.
314
315 -- Parameters :
316
317 -- IN : p_update_person Person needs to be updated(Buyer/Approver/Deliver_To).
318 -- p_old_personid Id of the old person.
319 -- p_new_personid Id of the new person.
320 -- p_document_type Type of the document(STANDARD,BLANKET.CONTRACT,PLANNED).
321 -- p_document_no_from Document number from.
322 -- p_document_no_to Document number to.
323 -- p_date_from Date from.
324 -- p_date_to Date to.
325 -- p_supplier_id Supplier id.
326 -- p_include_close_po Include Close PO's or not (Value as Yes or No).
327 -- p_commit_interval Commit interval.
328
329 -- OUT : p_msg_data Actual message in encoded format.
330 -- p_msg_count Holds the number of messages in the API list.
331 -- p_return_status Return status of the API (Includes 'S','E','U').
332
333 -- End of Comments
334 --------------------------------------------------------------------------------------------------
335
336 PROCEDURE Update_Buyer (p_update_person IN VARCHAR2,
337 p_old_personid IN NUMBER,
338 p_new_personid IN NUMBER,
339 p_document_type IN VARCHAR2,
340 p_document_no_from IN VARCHAR2,
341 p_document_no_to IN VARCHAR2,
342 p_date_from IN DATE,
343 p_date_to IN DATE,
344 p_supplier_id IN NUMBER,
345 p_include_close_po IN VARCHAR2,
346 p_commit_interval IN NUMBER,
347 p_msg_data OUT NOCOPY VARCHAR2,
348 p_msg_count OUT NOCOPY NUMBER,
349 p_return_status OUT NOCOPY VARCHAR2) IS
350
351 c_po g_po;
352 c_rel g_rel;
353
354 stmt_rel VARCHAR2(4000);
355 stmt_po VARCHAR2(4000);
356 po_num_type VARCHAR2(100);
357 l_commit_count NUMBER := 0;
358 l_progress VARCHAR2(3) := '000';
359 l_log_head CONSTANT VARCHAR2(1000) := g_log_head||'Update_Buyer';
360
361 -- Cursor Output Variables.
362
363 l_po_rowid ROWID;
364 l_rel_rowid ROWID;
365 l_po_num po_headers.segment1%TYPE;
366 l_rel_num po_releases.release_num%TYPE;
367 l_doc_type po_document_types_all.type_name%TYPE;
368 l_auth_status po_headers.authorization_status%TYPE;
369 l_itemtype wf_items.item_type%TYPE;
370 l_itemkey wf_items.item_key%TYPE;
371
372 -- Variables used in OKC API.
373
374 l_document_id po_headers.po_header_id%TYPE;
375 l_document_type po_headers.type_lookup_code%TYPE;
376 l_document_version po_headers.revision_num%TYPE;
377 l_conterms_exist_flag po_headers.conterms_exist_flag%TYPE;
378 l_contracts_document_type VARCHAR2(150);
379 SUBTYPE busdocs_tbl_type IS okc_manage_deliverables_grp.busdocs_tbl_type;
380 l_busdocs_tbl busdocs_tbl_type;
381 l_empty_busdocs_tbl busdocs_tbl_type;
382 l_row_index PLS_INTEGER := 0;
383
384 -- Local Variables used in the same procedure.
385
386 l_preparer_id NUMBER;
387 l_buyer_user_id NUMBER;
388 l_forward_from_id NUMBER;
389 l_msg15 VARCHAR2(240);
390
391 BEGIN
392
393 g_old_personid := p_old_personid;
394 g_document_type := p_document_type;
395 g_document_no_from := p_document_no_from;
396 g_document_no_to := p_document_no_to;
397 g_date_from := p_date_from;
398 g_date_to := p_date_to;
399 g_supplier_id := p_supplier_id;
400
401
402 IF g_debug_stmt THEN
403
404 PO_DEBUG.debug_begin(l_log_head);
405 PO_DEBUG.debug_var(l_log_head,l_progress,'p_update_person',p_update_person );
406 PO_DEBUG.debug_var(l_log_head,l_progress,'p_old_personid', p_old_personid);
407 PO_DEBUG.debug_var(l_log_head,l_progress,'p_new_personid', p_new_personid);
408 PO_DEBUG.debug_var(l_log_head,l_progress,'p_document_type',p_document_type );
409 PO_DEBUG.debug_var(l_log_head,l_progress,'p_document_no_from',p_document_no_from );
410 PO_DEBUG.debug_var(l_log_head,l_progress,'p_date_from',p_date_from);
411 PO_DEBUG.debug_var(l_log_head,l_progress,'p_date_to',p_date_to);
412 PO_DEBUG.debug_var(l_log_head,l_progress,'p_supplier_id',p_supplier_id);
413 PO_DEBUG.debug_var(l_log_head,l_progress,'p_include_close_po',p_include_close_po);
414 PO_DEBUG.debug_var(l_log_head,l_progress,'p_commit_interval',p_commit_interval);
415
416 END IF;
417
418 SAVEPOINT PO_Mass_Update_Buyer_SP;
419
420 l_progress := '001';
421
422 IF g_debug_stmt THEN
423
424 PO_DEBUG.debug_var(l_log_head,l_progress,'Before Calling Buyer_Info','Before Calling Buyer_Info' );
425
426 END IF;
427
428 BEGIN
429
430 SAVEPOINT Update_Buyer_REC_SP;
431
432 Buyer_Info(p_old_personid,
433 p_new_personid,
434 p_supplier_id,
435 p_old_buyer_name,
436 p_new_buyer_name,
437 p_old_username,
438 p_new_username,
439 p_new_user_display_name,
440 p_old_buyer_user_id,
441 p_new_buyer_user_id,
442 p_supplier_name,
443 p_org_name,
444 p_msg_data,
445 p_msg_count,
446 p_return_status);
447
448 IF p_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
449 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
450 RETURN;
451 END IF;
452
453
454 l_progress := '002';
455
456 IF g_debug_stmt THEN
457
458 PO_DEBUG.debug_var(l_log_head,l_progress,'Before Calling Print_Output','Before Calling Print_Output' );
459
460 END IF;
461
462 IF (p_update_person = 'BUYER' OR p_update_person = 'ALL') THEN
463
464 Print_Output(p_update_person,
465 p_old_buyer_name,
466 p_new_buyer_name,
467 p_org_name,
468 p_document_type,
469 p_document_no_from,
470 p_document_no_to,
471 p_date_from,
472 p_date_to,
473 p_supplier_name,
474 p_msg_data,
475 p_msg_count,
476 p_return_status);
477
478 END IF;
479
480 SELECT manual_po_num_type
481 INTO po_num_type
482 FROM po_system_parameters;
483
484 fnd_message.set_name('PO','PO_MUB_MSG_BUYER');
485 l_msg15 := fnd_message.get;
486
487 l_progress := '003';
488
489 IF g_debug_stmt THEN
490
491 PO_DEBUG.debug_var(l_log_head,l_progress,'po_num_type',po_num_type );
492
493 END IF;
494
495 EXCEPTION
496
497 WHEN OTHERS THEN
498
499 ROLLBACK TO Update_Buyer_REC_SP;
500
501 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
502
503 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,l_log_head,l_progress|| SQLCODE || SUBSTR(SQLERRM,1,200));
504
505 END IF;
506
507 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
508
509 FND_MSG_PUB.Count_And_Get(
510 p_count => p_msg_count,
511 p_data => p_msg_data);
512
513 END;
514
515 stmt_po := 'SELECT poh.ROWID,
516 poh.segment1,
517 pdt.type_name,
518 poh.authorization_status,
519 poh.wf_item_type,
520 poh.wf_item_key,
521 poh.po_header_id,
522 poh.type_lookup_code,
523 poh.revision_num,
524 Nvl(poh.conterms_exist_flag, ''N'')
525 FROM po_headers poh,
526 po_document_types_vl pdt
527 WHERE poh.agent_id = PO_MASS_UPDATE_PO_PVT.get_old_personid
528 AND pdt.document_type_code IN (''PO'',''PA'')
529 AND pdt.document_subtype = poh.type_lookup_code
530 AND Nvl(poh.authorization_status,''INCOMPLETE'') IN (''APPROVED'',''REQUIRES REAPPROVAL'',''INCOMPLETE'',''REJECTED'',''IN PROCESS'',''PRE-APPROVED'')
531 AND Nvl(poh.cancel_flag,''N'') = ''N''
532 AND Nvl(poh.frozen_flag,''N'') = ''N'' ';
533
534 IF p_document_type IS NOT NULL AND p_document_type <> 'ALL' THEN -- <BUG 6988269>
535
536 stmt_po := stmt_po || ' AND poh.type_lookup_code = PO_MASS_UPDATE_PO_PVT.get_document_type';
537
538 END IF;
539
540 IF ( po_num_type = 'NUMERIC' ) THEN
541
542 IF p_document_no_from IS NULL AND p_document_no_to IS NULL THEN
543
544 stmt_po := stmt_po || ' AND 1 = 1 ';
545
546 ELSIF p_document_no_from IS NOT NULL AND p_document_no_to IS NULL THEN
547
548 stmt_po := stmt_po || ' AND DECODE ( RTRIM ( POH.SEGMENT1,''0123456789'' ), NULL, To_Number ( POH.SEGMENT1 ) , NULL ) >= to_number(PO_MASS_UPDATE_PO_PVT.get_document_no_from) ';
549
550 ELSIF p_document_no_from IS NULL AND p_document_no_to IS NOT NULL THEN
551
552 stmt_po := stmt_po || ' AND DECODE ( RTRIM ( POH.SEGMENT1,''0123456789'' ), NULL, To_Number ( POH.SEGMENT1 ) , NULL ) <= to_number(PO_MASS_UPDATE_PO_PVT.get_document_no_to ||) ';
553
554 ELSE
555
556 stmt_po := stmt_po || ' AND DECODE ( RTRIM ( POH.SEGMENT1,''0123456789'' ), NULL, To_Number ( POH.SEGMENT1 ) , NULL )
557
558 BETWEEN to_number(PO_MASS_UPDATE_PO_PVT.get_document_no_from) AND to_number(PO_MASS_UPDATE_PO_PVT.get_document_no_to) ';
559
560 END IF;
561
562 ELSE
563
564 IF p_document_no_from IS NULL AND p_document_no_to IS NULL THEN
565
566 stmt_po := stmt_po || ' AND 1 = 1 ';
567
568 ELSIF p_document_no_from IS NOT NULL AND p_document_no_to IS NULL THEN
569
570 stmt_po := stmt_po || ' AND POH.SEGMENT1 >= PO_MASS_UPDATE_PO_PVT.get_document_no_from';
571
572 ELSIF p_document_no_from IS NULL AND p_document_no_to IS NOT NULL THEN
573
574 stmt_po := stmt_po || ' AND POH.SEGMENT1 <= PO_MASS_UPDATE_PO_PVT.get_document_no_to';
575
576 ELSE
577
578 stmt_po := stmt_po || ' AND POH.SEGMENT1 BETWEEN PO_MASS_UPDATE_PO_PVT.get_document_no_from AND PO_MASS_UPDATE_PO_PVT.get_document_no_to';
579
580 END IF;
581
582 END IF; /* End of po_num_type = 'NUMERIC' */
583
584 /* Bug 6899092 Added Trunc condition in validating the date ranges */
585
586 IF p_date_from IS NULL AND p_date_to IS NULL THEN
587
588 stmt_po := stmt_po || ' AND 1 = 1 ';
589
590 ELSIF p_date_from IS NOT NULL AND p_date_to IS NULL THEN
591
592 stmt_po := stmt_po || ' AND POH.creation_date >= Trunc(PO_MASS_UPDATE_PO_PVT.get_date_from)';
593
594 ELSIF p_date_from IS NULL AND p_date_to IS NOT NULL THEN
595
596 stmt_po := stmt_po || ' AND POH.creation_date <= Trunc(PO_MASS_UPDATE_PO_PVT.get_date_to)';
597
598 ELSE
599 stmt_po := stmt_po || ' AND POH.creation_date >= Trunc(PO_MASS_UPDATE_PO_PVT.get_date_from)
600 AND POH.creation_date < Trunc(PO_MASS_UPDATE_PO_PVT.get_date_to)+1';
601
602 END IF;
603
604
605 IF p_supplier_id IS NOT NULL THEN
606
607 stmt_po := stmt_po || ' AND POH.vendor_id = PO_MASS_UPDATE_PO_PVT.get_supplier_id';
608
609 END IF;
610
611 IF p_include_close_po = 'YES' THEN
612
613 stmt_po := stmt_po || ' AND Nvl(POH.closed_code,''OPEN'') NOT IN (''FINALLY CLOSED'') '; /* 6868589 */
614
615 ELSE
616
617 stmt_po := stmt_po || ' AND Nvl(POH.closed_code,''OPEN'') NOT IN (''CLOSED'',''FINALLY CLOSED'') '; /* 6868589 */
618
619 END IF;
620
621 stmt_po := stmt_po || ' ORDER BY POH.segment1';
622
623 IF (p_document_type IS NULL OR p_document_type IN ('STANDARD','BLANKET','PLANNED','CONTRACT','ALL')) THEN -- <BUG 6988269>
624
625 OPEN c_po for stmt_po;
626
627 LOOP
628
629 FETCH c_po INTO l_po_rowid,
630 l_po_num,
631 l_doc_type,
632 l_auth_status,
633 l_itemtype,
634 l_itemkey,
635 l_document_id,
636 l_document_type,
637 l_document_version,
638 l_conterms_exist_flag;
639
640 EXIT when c_po%NOTFOUND;
641
642 BEGIN
643
644 SAVEPOINT Update_Buyer_REC_PO_SP;
645
646 l_progress := '004';
647
648 IF g_debug_stmt THEN
649
650 PO_DEBUG.debug_var(l_log_head,l_progress,'l_po_rowid',l_po_rowid );
651 PO_DEBUG.debug_var(l_log_head,l_progress,'l_po_num',l_po_num );
652 PO_DEBUG.debug_var(l_log_head,l_progress,'l_doc_type',l_doc_type );
653 PO_DEBUG.debug_var(l_log_head,l_progress,'l_auth_status',l_auth_status );
654 PO_DEBUG.debug_var(l_log_head,l_progress,'l_itemtype',l_itemtype );
655 PO_DEBUG.debug_var(l_log_head,l_progress,'l_itemkey',l_itemkey );
656 PO_DEBUG.debug_var(l_log_head,l_progress,'l_document_id',l_document_id );
657 PO_DEBUG.debug_var(l_log_head,l_progress,'l_document_type',l_document_type );
658 PO_DEBUG.debug_var(l_log_head,l_progress,'l_document_version',l_document_version );
659 PO_DEBUG.debug_var(l_log_head,l_progress,'l_conterms_exist_flag',l_conterms_exist_flag );
660
661 END IF;
662 BEGIN
663
664 UPDATE po_headers_all
665 SET agent_id = p_new_personid,
666 last_update_date = sysdate,
667 last_updated_by = fnd_global.user_id,
668 last_update_login = fnd_global.login_id
669 WHERE rowid = l_po_rowid;
670
671 --Bug 8846315, update archive also.
672 UPDATE po_headers_archive_all
673 SET agent_id = p_new_personid,
674 last_update_date = sysdate,
675 last_updated_by = fnd_global.user_id,
676 last_update_login = fnd_global.login_id
677 WHERE po_header_id=l_document_id
678 AND latest_external_flag= 'Y';
679 EXCEPTION
680 WHEN NO_DATA_FOUND THEN
681 NULL;
682 END;
683 --Bug for 8846315 end.
684
685 l_progress := '005';
686
687 IF g_debug_stmt THEN
688
689 PO_DEBUG.debug_var(l_log_head,l_progress,'p_new_personid',p_new_personid );
690
691 END IF;
692
693 IF ( (l_auth_status='PRE-APPROVED') OR (l_auth_status='IN PROCESS') ) THEN
694
695 l_preparer_id := po_wf_util_pkg.GetItemAttrText ( itemtype => l_itemtype,
696 itemkey => l_itemkey,
697 aname => 'PREPARER_ID');
698
699 IF (l_preparer_id = p_old_personid) THEN
700
701 po_wf_util_pkg.SetItemAttrText ( itemtype => l_itemtype,
702 itemkey => l_itemkey,
703 aname => 'PREPARER_USER_NAME' ,
704 avalue => p_new_username);
705
706 po_wf_util_pkg.SetItemAttrText ( itemtype => l_itemtype,
707 itemkey => l_itemkey,
708 aname => 'PREPARER_DISPLAY_NAME' ,
709 avalue => p_new_user_display_name);
710
711 po_wf_util_pkg.SetItemAttrText ( itemtype => l_itemtype,
712 itemkey => l_itemkey,
713 aname => 'PREPARER_ID' ,
714 avalue => p_new_personid);
715
716 END IF;
717
718 l_buyer_user_id := po_wf_util_pkg.GetItemAttrText ( itemtype => l_itemtype,
719 itemkey => l_itemkey,
720 aname => 'BUYER_USER_ID');
721
722 IF (l_buyer_user_id = p_old_buyer_user_id) THEN
723
724 po_wf_util_pkg.SetItemAttrText ( itemtype => l_itemtype,
725 itemkey => l_itemkey,
726 aname => 'BUYER_USER_ID' ,
727 avalue => p_new_buyer_user_id);
728
729 po_wf_util_pkg.SetItemAttrText ( itemtype => l_itemtype,
730 itemkey => l_itemkey,
731 aname => 'BUYER_USER_NAME' ,
732 avalue => p_new_username);
733
734 /*
735 Bug 14078118
736 POXPOPDF uses 'USER_ID' and not 'BUYER_USER_ID', so this will make sure that, even after 'Mass Update' is run to
737 change a terminated buyer, POXPOPDF would not run with the terminated user's USER_ID
738 */
739 po_wf_util_pkg.SetItemAttrText ( itemtype => l_itemtype,
740 itemkey => l_itemkey,
741 aname => 'USER_ID' ,
742 avalue => p_new_buyer_user_id);
743
744 END IF;
745
746 l_forward_from_id := po_wf_util_pkg.GetItemAttrText ( itemtype => l_itemtype,
747 itemkey => l_itemkey,
748 aname => 'FORWARD_FROM_ID');
749
750 IF (l_forward_from_id = p_old_personid) THEN
751
752 po_wf_util_pkg.SetItemAttrText ( itemtype => l_itemtype,
753 itemkey => l_itemkey,
754 aname => 'FORWARD_FROM_DISP_NAME' ,
755 avalue => p_new_user_display_name);
756
757 po_wf_util_pkg.SetItemAttrText ( itemtype => l_itemtype,
758 itemkey => l_itemkey,
759 aname => 'FORWARD_FROM_ID' ,
760 avalue => p_new_personid);
761
762 po_wf_util_pkg.SetItemAttrText ( itemtype => l_itemtype,
763 itemkey => l_itemkey,
764 aname => 'FORWARD_FROM_USER_NAME' ,
765 avalue => p_new_username);
766
767 END IF;
768
769 /*
770 Bug 14078118
771 Added this condition to take care of the case in which Mass Update is run on an 'Approved' PO.
772 */
773 ELSIF (l_auth_status='APPROVED') THEN
774
775 l_buyer_user_id := po_wf_util_pkg.GetItemAttrText ( itemtype => l_itemtype,
776 itemkey => l_itemkey,
777 aname => 'BUYER_USER_ID');
778
779 IF (l_buyer_user_id = p_old_buyer_user_id) THEN
780
781 po_wf_util_pkg.SetItemAttrText ( itemtype => l_itemtype,
782 itemkey => l_itemkey,
783 aname => 'BUYER_USER_ID' ,
784 avalue => p_new_buyer_user_id);
785
786 po_wf_util_pkg.SetItemAttrText ( itemtype => l_itemtype,
787 itemkey => l_itemkey,
788 aname => 'BUYER_USER_NAME' ,
789 avalue => p_new_username);
790
791 po_wf_util_pkg.SetItemAttrText ( itemtype => l_itemtype,
792 itemkey => l_itemkey,
793 aname => 'USER_ID' ,
794 avalue => p_new_buyer_user_id);
795
796 END IF;
797
798 END IF; /* End of l_auth_status = 'PRE APPROVED' */
799
800 l_commit_count := l_commit_count + 1;
801
802 l_progress := '006';
803
804 IF g_debug_stmt THEN
805
806 PO_DEBUG.debug_var(l_log_head,l_progress,'l_commit_count',l_commit_count );
807
808 END IF;
809
810 IF (UPPER(l_conterms_exist_flag)='Y') THEN
811
812 l_row_index := l_row_index + 1;
813
814 l_busdocs_tbl(l_row_index).bus_doc_id := l_document_id;
815 l_busdocs_tbl(l_row_index).bus_doc_version := l_document_version;
816
817 IF (p_document_type IN ('BLANKET', 'CONTRACT')) THEN
818
819 l_contracts_document_type := 'PA_'||p_document_type;
820
821 ELSIF (p_document_type = 'STANDARD') THEN
822
823 l_contracts_document_type := 'PO_'||p_document_type;
824
825 END IF;
826
827 l_busdocs_tbl(l_row_index).bus_doc_type := l_contracts_document_type;
828 END IF;
829
830 IF l_commit_count = p_commit_interval THEN
831
832 IF (l_busdocs_tbl.COUNT >= 1) THEN
833
834 okc_manage_deliverables_grp.updateIntContactOnDeliverables (
835 p_api_version => 1.0,
836 p_init_msg_list => FND_API.G_FALSE,
837 p_commit => FND_API.G_FALSE,
838 p_bus_docs_tbl => l_busdocs_tbl,
839 p_original_internal_contact_id => p_old_personid,
840 p_new_internal_contact_id => p_new_personid,
841 x_msg_data => p_msg_data,
842 x_msg_count => p_msg_count,
843 x_return_status => p_return_status);
844
845 IF (p_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
846
847 FND_MSG_PUB.Count_and_Get(p_count => p_msg_count,p_data => p_msg_data);
848 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
849
850 END IF;
851
852 END IF;
853
854 COMMIT;
855 l_commit_count := 0;
856
857 l_busdocs_tbl := l_empty_busdocs_tbl;
858 l_row_index := 0;
859
860 END IF;
861
862 fnd_file.put_line(fnd_file.output, rpad(l_po_num,26) || rpad(l_doc_type,32) || l_msg15 );
863
864 EXCEPTION
865
866 WHEN OTHERS THEN
867
868 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
869
870 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,l_log_head,l_progress|| SQLCODE || SUBSTR(SQLERRM,1,200));
871
872 END IF;
873
874 ROLLBACK TO Update_Buyer_REC_PO_SP;
875
876 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
877
878 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
879
880 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_log_head );
881
882 END IF;
883
884 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => p_msg_count, p_data => p_msg_data );
885
886 END;
887
888
889 END LOOP;
890
891 CLOSE c_po;
892
893 END IF; -- < End of p_document_type >
894
895 IF (l_busdocs_tbl.COUNT >= 1) THEN
896
897 okc_manage_deliverables_grp.updateIntContactOnDeliverables (
898 p_api_version => 1.0,
899 p_init_msg_list => FND_API.G_FALSE,
900 p_commit => FND_API.G_FALSE,
901 p_bus_docs_tbl => l_busdocs_tbl,
902 p_original_internal_contact_id => p_old_personid,
903 p_new_internal_contact_id => p_new_personid,
904 x_msg_data => p_msg_data,
905 x_msg_count => p_msg_count,
906 x_return_status => p_return_status);
907
908 IF (p_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
909
910 FND_MSG_PUB.Count_and_Get(p_count => p_msg_count
911 ,p_data => p_msg_data);
912
913 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
914
915 END IF;
916
917 END IF;
918
919 stmt_rel := 'SELECT por.ROWID,
920 poh.segment1,
921 por.release_num,
922 pdt.type_name,
923 por.authorization_status,
924 por.wf_item_type,
925 por.wf_item_key,
926 por.po_release_id --8846315 fix
927 FROM po_headers poh,
928 po_releases por,
929 po_document_types_vl pdt
930 WHERE poh.po_header_id = por.po_header_id
931 AND por.agent_id = PO_MASS_UPDATE_PO_PVT.get_old_personid
932 AND pdt.document_type_code = ''RELEASE''
933 AND pdt.document_subtype = por.release_type
934 AND Nvl(por.authorization_status,''INCOMPLETE'') IN (''APPROVED'',''REQUIRES REAPPROVAL'',''INCOMPLETE'',''REJECTED'',''IN PROCESS'',''PRE-APPROVED'')
935 AND Nvl(por.cancel_flag,''N'') = ''N''
936 AND Nvl(por.frozen_flag,''N'') = ''N''';
937
938
939 IF p_document_type IS NOT NULL AND p_document_type <> 'ALL' THEN -- <BUG 6988269>
940
941 stmt_rel := stmt_rel || ' AND ((PO_MASS_UPDATE_PO_PVT.get_document_type = ''PLANNED'' and por.release_type = ''SCHEDULED'')
942
943 OR (por.release_type = Nvl(PO_MASS_UPDATE_PO_PVT.get_document_type,por.release_type)))';
944
945 END IF;
946
947
948 IF ( po_num_type = 'NUMERIC' ) THEN
949
950 IF p_document_no_from IS NULL AND p_document_no_to IS NULL THEN
951
952 stmt_rel := stmt_rel || ' AND 1 = 1 ';
953
954 ELSIF p_document_no_from IS NOT NULL AND p_document_no_to IS NULL THEN
955
956 stmt_rel := stmt_rel || ' AND DECODE ( RTRIM ( POH.SEGMENT1,''0123456789'' ), NULL, To_Number ( POH.SEGMENT1 ) , NULL ) >= to_number(PO_MASS_UPDATE_PO_PVT.get_document_no_from)';
957
958 ELSIF p_document_no_from IS NULL AND p_document_no_to IS NOT NULL THEN
959
960 stmt_rel := stmt_rel || ' AND DECODE ( RTRIM ( POH.SEGMENT1,''0123456789'' ), NULL, To_Number ( POH.SEGMENT1 ) , NULL ) <= to_number(PO_MASS_UPDATE_PO_PVT.get_document_no_to)';
961
962 ELSE
963
964 stmt_rel := stmt_rel || ' AND DECODE ( RTRIM ( POH.SEGMENT1,''0123456789'' ), NULL, To_Number ( POH.SEGMENT1 ) , NULL )
965
966 BETWEEN to_number(PO_MASS_UPDATE_PO_PVT.get_document_no_from) AND to_number(PO_MASS_UPDATE_PO_PVT.get_document_no_to)';
967
968 END IF;
969
970 ELSE
971
972 IF p_document_no_from IS NULL AND p_document_no_to IS NULL THEN
973
974 stmt_rel := stmt_rel || ' AND 1 = 1 ';
975
976 ELSIF p_document_no_from IS NOT NULL AND p_document_no_to IS NULL THEN
977
978 stmt_rel := stmt_rel || ' AND POH.SEGMENT1 >= PO_MASS_UPDATE_PO_PVT.get_document_no_from';
979
980 ELSIF p_document_no_from IS NULL AND p_document_no_to IS NOT NULL THEN
981
982 stmt_rel := stmt_rel || ' AND POH.SEGMENT1 <= PO_MASS_UPDATE_PO_PVT.get_document_no_to';
983
984 ELSE
985
986 stmt_rel := stmt_rel || ' AND POH.SEGMENT1 BETWEEN PO_MASS_UPDATE_PO_PVT.get_document_no_from AND PO_MASS_UPDATE_PO_PVT.get_document_no_to';
987
988 END IF;
989
990
991 END IF; /* End of po_num_type = 'NUMERIC' */
992
993 /* Bug 6899092 Added Trunc condition in validating the date ranges */
994
995 IF p_date_from IS NULL AND p_date_to IS NULL THEN
996
997 stmt_rel := stmt_rel || ' AND 1 = 1 ';
998
999 ELSIF p_date_from IS NOT NULL AND p_date_to IS NULL THEN
1000
1001 stmt_rel := stmt_rel || ' AND POR.creation_date >= Trunc(PO_MASS_UPDATE_PO_PVT.get_date_from)';
1002
1003 ELSIF p_date_from IS NULL AND p_date_to IS NOT NULL THEN
1004
1005 stmt_rel := stmt_rel || ' AND POR.creation_date <= Trunc(PO_MASS_UPDATE_PO_PVT.get_date_to)';
1006
1007 ELSE
1008
1009 stmt_rel := stmt_rel || ' AND POR.creation_date >= Trunc(PO_MASS_UPDATE_PO_PVT.get_date_from)
1010 AND POR.creation_date < Trunc(PO_MASS_UPDATE_PO_PVT.get_date_to)+1';
1011
1012 END IF;
1013
1014
1015 IF p_supplier_id IS NOT NULL THEN
1016
1017 stmt_rel := stmt_rel || ' AND POH.vendor_id = PO_MASS_UPDATE_PO_PVT.get_supplier_id';
1018
1019 END IF;
1020
1021
1022 IF p_include_close_po = 'YES' THEN
1023
1024 stmt_rel := stmt_rel || ' AND Nvl(POR.closed_code,''OPEN'') NOT IN (''FINALLY CLOSED'') '; /* 6868589 */
1025
1026 ELSE
1027
1028 stmt_rel := stmt_rel || ' AND Nvl(POR.closed_code,''OPEN'') NOT IN (''CLOSED'',''FINALLY CLOSED'') '; /* 6868589 */
1029
1030 END IF;
1031
1032 stmt_rel := stmt_rel || ' ORDER BY POH.segment1,POR.release_num';
1033
1034
1035 IF (p_document_type IS NULL OR p_document_type IN ('BLANKET','PLANNED','ALL')) THEN -- <BUG 6988269 Added 'ALL' condition>
1036
1037 OPEN c_rel for stmt_rel;
1038
1039 LOOP
1040
1041 FETCH c_rel INTO l_rel_rowid,
1042 l_po_num,
1043 l_rel_num,
1044 l_doc_type,
1045 l_auth_status,
1046 l_itemtype,
1047 l_itemkey,
1048 l_document_id;--Bug 8846315
1049
1050 EXIT WHEN c_rel%NOTFOUND;
1051
1052 BEGIN
1053
1054 SAVEPOINT Update_Buyer_REC_REL_SP;
1055
1056 l_progress := '007';
1057
1058 IF g_debug_stmt THEN
1059
1060 PO_DEBUG.debug_var(l_log_head,l_progress,'l_rel_rowid',l_rel_rowid );
1061 PO_DEBUG.debug_var(l_log_head,l_progress,'l_po_num',l_po_num );
1062 PO_DEBUG.debug_var(l_log_head,l_progress,'l_rel_num',l_rel_num );
1063 PO_DEBUG.debug_var(l_log_head,l_progress,'l_doc_type',l_doc_type );
1064 PO_DEBUG.debug_var(l_log_head,l_progress,'l_auth_status',l_auth_status );
1065 PO_DEBUG.debug_var(l_log_head,l_progress,'l_itemtype',l_itemtype );
1066 PO_DEBUG.debug_var(l_log_head,l_progress,'l_itemkey',l_itemkey );
1067
1068 END IF;
1069
1070 BEGIN
1071 UPDATE po_releases_all
1072 SET agent_id = p_new_personid,
1073 last_update_date = sysdate,
1074 last_updated_by = fnd_global.user_id,
1075 last_update_login = fnd_global.login_id
1076 WHERE rowid = l_rel_rowid;
1077 --Bug 8846315, update archive also.
1078
1079 UPDATE po_releases_archive_all
1080 SET agent_id = p_new_personid,
1081 last_update_date = sysdate,
1082 last_updated_by = fnd_global.user_id,
1083 last_update_login = fnd_global.login_id
1084 WHERE po_release_id=l_document_id
1085 AND latest_external_flag= 'Y';
1086
1087 EXCEPTION
1088 WHEN NO_DATA_FOUND THEN
1089 NULL;
1090 END;
1091 --Bug 8846315
1092
1093 l_progress := '008';
1094
1095 IF g_debug_stmt THEN
1096
1097 PO_DEBUG.debug_var(l_log_head,l_progress,'p_new_personid',p_new_personid );
1098
1099 END IF;
1100
1101
1102
1103 IF ( (l_auth_status='PRE-APPROVED') OR (l_auth_status='IN PROCESS') ) THEN
1104
1105
1106 l_preparer_id := po_wf_util_pkg.GetItemAttrText ( itemtype => l_itemtype,
1107 itemkey => l_itemkey,
1108 aname => 'PREPARER_ID');
1109
1110 IF (l_preparer_id = p_old_personid) THEN
1111
1112 po_wf_util_pkg.SetItemAttrText ( itemtype => l_itemtype,
1113 itemkey => l_itemkey,
1114 aname => 'PREPARER_USER_NAME' ,
1115 avalue => p_new_username);
1116
1117 po_wf_util_pkg.SetItemAttrText ( itemtype => l_itemtype,
1118 itemkey => l_itemkey,
1119 aname => 'PREPARER_DISPLAY_NAME' ,
1120 avalue => p_new_user_display_name);
1121
1122 po_wf_util_pkg.SetItemAttrText ( itemtype => l_itemtype,
1123 itemkey => l_itemkey,
1124 aname => 'PREPARER_ID' ,
1125 avalue => p_new_personid);
1126
1127 END IF;
1128
1129 l_buyer_user_id := po_wf_util_pkg.GetItemAttrText ( itemtype => l_itemtype,
1130 itemkey => l_itemkey,
1131 aname => 'BUYER_USER_ID');
1132
1133 IF (l_buyer_user_id = p_old_buyer_user_id) THEN
1134
1135 po_wf_util_pkg.SetItemAttrText ( itemtype => l_itemtype,
1136 itemkey => l_itemkey,
1137 aname => 'BUYER_USER_ID' ,
1138 avalue => p_new_buyer_user_id);
1139
1140 po_wf_util_pkg.SetItemAttrText ( itemtype => l_itemtype,
1141 itemkey => l_itemkey,
1142 aname => 'BUYER_USER_NAME' ,
1143 avalue => p_new_username);
1144
1145 END IF;
1146
1147 l_forward_from_id := po_wf_util_pkg.GetItemAttrText ( itemtype => l_itemtype,
1148 itemkey => l_itemkey,
1149 aname => 'FORWARD_FROM_ID');
1150
1151
1152 IF (l_forward_from_id = p_old_personid) THEN
1153
1154 po_wf_util_pkg.SetItemAttrText ( itemtype => l_itemtype,
1155 itemkey => l_itemkey,
1156 aname => 'FORWARD_FROM_DISP_NAME' ,
1157 avalue => p_new_user_display_name);
1158
1159 po_wf_util_pkg.SetItemAttrText ( itemtype => l_itemtype,
1160 itemkey => l_itemkey,
1161 aname => 'FORWARD_FROM_ID' ,
1162 avalue => p_new_personid);
1163
1164 po_wf_util_pkg.SetItemAttrText ( itemtype => l_itemtype,
1165 itemkey => l_itemkey,
1166 aname => 'FORWARD_FROM_USER_NAME' ,
1167 avalue => p_new_username);
1168
1169 END IF;
1170
1171 END IF; /* End of l_auth_status = 'PRE APPROVED' */
1172
1173 l_commit_count := l_commit_count + 1;
1174
1175 l_progress := '009';
1176
1177 IF g_debug_stmt THEN
1178
1179 PO_DEBUG.debug_var(l_log_head,l_progress,'l_commit_count',l_commit_count );
1180
1181 END IF;
1182
1183 IF l_commit_count = p_commit_interval THEN
1184
1185 COMMIT;
1186 l_commit_count := 0;
1187
1188 END IF;
1189
1190 fnd_file.put_line(fnd_file.output, rpad(l_po_num || '-' || l_rel_num,32) || rpad(l_doc_type,26) || l_msg15 );
1191
1192 EXCEPTION
1193
1194 WHEN OTHERS THEN
1195
1196 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1197
1198 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,l_log_head,l_progress|| SQLCODE || SUBSTR(SQLERRM,1,200));
1199
1200 END IF;
1201
1202 ROLLBACK TO Update_Buyer_REC_REL_SP;
1203
1204 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1205
1206 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1207
1208 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_log_head );
1209
1210 END IF;
1211
1212 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => p_msg_count, p_data => p_msg_data );
1213
1214 END;
1215
1216 END LOOP;
1217
1218 CLOSE c_rel;
1219
1220 END IF; /* End of p_document_type IS NULL */
1221
1222 EXCEPTION
1223
1224 WHEN OTHERS THEN
1225
1226 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1227
1228 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,l_log_head,l_progress|| SQLCODE || SUBSTR(SQLERRM,1,200));
1229
1230 END IF;
1231
1232 ROLLBACK TO PO_Mass_Update_Buyer_SP;
1233
1234 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1235
1236 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1237
1238 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_log_head );
1239
1240 END IF;
1241
1242 END Update_Buyer;
1243
1244 --------------------------------------------------------------------------------------------------
1245 -- Start of Comments
1246
1247 -- API Name : Update_Approver
1248 -- Type : Private
1249 -- Pre-reqs : None
1250 -- Function : Updates the old approver with the new approver provided and also forwards
1251 -- the notification from old approver to new approver in case of In process
1252 -- and Pre-approved PO's and releases.
1253
1254 -- Parameters :
1255
1256 -- IN : p_update_person Person needs to be updated(Buyer/Approver/Deliver_To).
1257 -- p_old_personid Id of the old person.
1258 -- p_new_personid Id of the new person.
1259 -- p_document_type Type of the document(STANDARD,BLANKET.CONTRACT,PLANNED).
1260 -- p_document_no_from Document number from.
1261 -- p_document_no_to Document number to.
1262 -- p_date_from Date from.
1263 -- p_date_to Date to.
1264 -- p_supplier_id Supplier id.
1265 -- p_include_close_po Include Close PO's or not (Value as Yes or No).
1266 -- p_commit_interval Commit interval.
1267
1268 -- OUT : p_msg_data Actual message in encoded format.
1269 -- p_msg_count Holds the number of messages in the API list.
1270 -- p_return_status Return status of the API (Includes 'S','E','U').
1271
1272 -- End of Comments
1273 --------------------------------------------------------------------------------------------------
1274
1275 PROCEDURE Update_Approver(p_update_person IN VARCHAR2,
1276 p_old_personid IN NUMBER,
1277 p_new_personid IN NUMBER,
1278 p_document_type IN VARCHAR2,
1279 p_document_no_from IN VARCHAR2,
1280 p_document_no_to IN VARCHAR2,
1281 p_date_from IN DATE,
1282 p_date_to IN DATE,
1283 p_supplier_id IN NUMBER,
1284 p_include_close_po IN VARCHAR2,
1285 p_commit_interval IN NUMBER,
1286 p_msg_data OUT NOCOPY VARCHAR2,
1287 p_msg_count OUT NOCOPY NUMBER,
1288 p_return_status OUT NOCOPY VARCHAR2) IS
1289
1290 c_po_approver g_po_approver;
1291 c_rel_approver g_rel_approver;
1292 stmt_rel VARCHAR2(4000);
1293 stmt_po VARCHAR2(4000);
1294 l_po_rowid ROWID;
1295 l_rel_rowid ROWID;
1296 l_po_num po_headers.segment1%TYPE;
1297 l_rel_num po_releases.release_num%TYPE;
1298 po_num_type VARCHAR2(100);
1299 l_commit_count NUMBER := 0;
1300 l_progress VARCHAR2(3) := '000';
1301 l_log_head CONSTANT VARCHAR2(1000) := g_log_head||'Update_Approver';
1302 l_doc_type po_document_types_all.type_name%TYPE;
1303 l_auth_status po_headers.authorization_status%TYPE;
1304 l_notification_id wf_notifications.notification_id%TYPE;
1305 l_msg16 VARCHAR2(240);
1306 l_msg17 VARCHAR2(240);
1307
1308 BEGIN
1309
1310 --package variable intialization
1311
1312 g_old_personid := p_old_personid;
1313 g_document_type := p_document_type;
1314 g_document_no_from := p_document_no_from;
1315 g_document_no_to := p_document_no_to;
1316 g_date_from := p_date_from;
1317 g_date_to := p_date_to;
1318 g_supplier_id := p_supplier_id;
1319
1320
1321 IF g_debug_stmt THEN
1322
1323 PO_DEBUG.debug_begin(l_log_head);
1324 PO_DEBUG.debug_var(l_log_head,l_progress,'p_update_person',p_update_person );
1325 PO_DEBUG.debug_var(l_log_head,l_progress,'p_old_personid', p_old_personid);
1326 PO_DEBUG.debug_var(l_log_head,l_progress,'p_new_personid', p_new_personid);
1327 PO_DEBUG.debug_var(l_log_head,l_progress,'p_document_type',p_document_type );
1328 PO_DEBUG.debug_var(l_log_head,l_progress,'p_document_no_from',p_document_no_from );
1329 PO_DEBUG.debug_var(l_log_head,l_progress,'p_date_from',p_date_from);
1330 PO_DEBUG.debug_var(l_log_head,l_progress,'p_date_to',p_date_to);
1331 PO_DEBUG.debug_var(l_log_head,l_progress,'p_supplier_id',p_supplier_id);
1332 PO_DEBUG.debug_var(l_log_head,l_progress,'p_include_close_po',p_include_close_po);
1333 PO_DEBUG.debug_var(l_log_head,l_progress,'p_commit_interval',p_commit_interval);
1334
1335 END IF;
1336
1337 SAVEPOINT PO_Mass_Update_Approver_SP;
1338
1339 l_progress := '001';
1340
1341 IF g_debug_stmt THEN
1342
1343 PO_DEBUG.debug_var(l_log_head,l_progress,'Before Calling Buyer_Info','Before Calling Buyer_Info' );
1344
1345 END IF;
1346
1347 BEGIN
1348
1349 SAVEPOINT Update_Approver_REC_SP;
1350
1351 Buyer_Info(p_old_personid,
1352 p_new_personid,
1353 p_supplier_id,
1354 p_old_buyer_name,
1355 p_new_buyer_name,
1356 p_old_username,
1357 p_new_username,
1358 p_new_user_display_name,
1359 p_old_buyer_user_id,
1360 p_new_buyer_user_id,
1361 p_supplier_name,
1362 p_org_name,
1363 p_msg_data,
1364 p_msg_count,
1365 p_return_status);
1366
1367 IF p_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1368 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1369 RETURN;
1370 END IF;
1371
1372
1373 g_old_username := p_old_username;
1374
1375 l_progress := '002';
1376
1377 IF g_debug_stmt THEN
1378
1379 PO_DEBUG.debug_var(l_log_head,l_progress,'Before Calling Print_Output','Before Calling Print_Output' );
1380
1381 END IF;
1382
1383 IF (p_update_person = 'APPROVER' ) THEN
1384
1385 Print_Output(p_update_person,
1386 p_old_buyer_name,
1387 p_new_buyer_name,
1388 p_org_name,
1389 p_document_type,
1390 p_document_no_from,
1391 p_document_no_to,
1392 p_date_from,
1393 p_date_to,
1394 p_supplier_name,
1395 p_msg_data,
1396 p_msg_count,
1397 p_return_status);
1398
1399 END IF;
1400
1401 SELECT manual_po_num_type
1402 INTO po_num_type
1403 FROM po_system_parameters;
1404
1405 fnd_message.set_name('PO','PO_MUB_MSG_NEW_APPROVER');
1406 l_msg16 := fnd_message.get;
1407
1408 fnd_message.set_name('PO','PO_MUB_MSG_APPROVER');
1409 l_msg17 := fnd_message.get;
1410
1411 l_progress := '003';
1412
1413 IF g_debug_stmt THEN
1414
1415 PO_DEBUG.debug_var(l_log_head,l_progress,'po_num_type',po_num_type );
1416
1417 END IF;
1418
1419 EXCEPTION
1420
1421 WHEN OTHERS THEN
1422
1423 ROLLBACK TO Update_Approver_REC_SP;
1424
1425 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1426
1427 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,l_log_head,l_progress|| SQLCODE || SUBSTR(SQLERRM,1,200));
1428
1429 END IF;
1430
1431 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1432
1433 FND_MSG_PUB.Count_And_Get(
1434 p_count => p_msg_count,
1435 p_data => p_msg_data);
1436
1437 END;
1438
1439
1440 stmt_po := 'SELECT wfn.notification_id,
1441 poh.segment1,
1442 pdt.type_name
1443 FROM wf_notifications wfn,
1444 wf_item_activity_statuses wfa,
1445 po_headers poh,
1446 po_document_types_vl pdt
1447 WHERE wfn.notification_id = wfa.notification_id
1448 AND wfa.item_type = poh.wf_item_type
1449 AND wfa.item_key = poh.wf_item_key
1450 AND wfn.status NOT IN (''CLOSED'',''CANCELED'')
1451 AND Nvl(poh.authorization_status,''INCOMPLETE'') IN (''IN PROCESS'',''PRE-APPROVED'')
1452 AND wfn.recipient_role = PO_Mass_Update_PO_PVT.get_old_username
1453 AND pdt.document_type_code in (''PO'',''PA'')
1454 AND pdt.document_subtype = poh.type_lookup_code';
1455
1456
1457 IF p_document_type IS NOT NULL AND p_document_type <> 'ALL' THEN -- <BUG 6988269>
1458
1459 stmt_po := stmt_po || ' AND poh.type_lookup_code = PO_MASS_UPDATE_PO_PVT.get_document_type';
1460
1461 END IF;
1462
1463
1464 IF ( po_num_type = 'NUMERIC' ) THEN
1465
1466 IF p_document_no_from IS NULL AND p_document_no_to IS NULL THEN
1467
1468 stmt_po := stmt_po || ' AND 1 = 1 ';
1469
1470 ELSIF p_document_no_from IS NOT NULL AND p_document_no_to IS NULL THEN
1471
1472 stmt_po := stmt_po || ' AND DECODE ( RTRIM ( POH.SEGMENT1,''0123456789'' ), NULL, To_Number ( POH.SEGMENT1 ) , NULL ) >= to_number(PO_MASS_UPDATE_PO_PVT.get_document_no_from)';
1473
1474 ELSIF p_document_no_from IS NULL AND p_document_no_to IS NOT NULL THEN
1475
1476 stmt_po := stmt_po || ' AND DECODE ( RTRIM ( POH.SEGMENT1,''0123456789'' ), NULL, To_Number ( POH.SEGMENT1 ) , NULL ) <= to_number(PO_MASS_UPDATE_PO_PVT.get_document_no_to)';
1477
1478 ELSE
1479
1480 stmt_po := stmt_po || ' AND DECODE ( RTRIM ( POH.SEGMENT1,''0123456789'' ), NULL, To_Number ( POH.SEGMENT1 ) , NULL )
1481
1482 BETWEEN to_number(PO_MASS_UPDATE_PO_PVT.get_document_no_from) AND to_number(PO_MASS_UPDATE_PO_PVT.get_document_no_to)';
1483
1484 END IF;
1485
1486 ELSE
1487
1488 IF p_document_no_from IS NULL AND p_document_no_to IS NULL THEN
1489
1490 stmt_po := stmt_po || ' AND 1 = 1 ';
1491
1492 ELSIF p_document_no_from IS NOT NULL AND p_document_no_to IS NULL THEN
1493
1494 stmt_po := stmt_po || ' AND POH.SEGMENT1 >= PO_MASS_UPDATE_PO_PVT.get_document_no_from';
1495
1496 ELSIF p_document_no_from IS NULL AND p_document_no_to IS NOT NULL THEN
1497
1498 stmt_po := stmt_po || ' AND POH.SEGMENT1 <= PO_MASS_UPDATE_PO_PVT.get_document_no_to';
1499
1500 ELSE
1501
1502 stmt_po := stmt_po || ' AND POH.SEGMENT1 BETWEEN PO_MASS_UPDATE_PO_PVT.get_document_no_from AND PO_MASS_UPDATE_PO_PVT.get_document_no_to';
1503
1504 END IF;
1505
1506 END IF; /* End of po_num_type = 'NUMERIC' */
1507
1508 /* Bug 6899092 Added Trunc condition in validating the date ranges */
1509
1510 IF p_date_from IS NULL AND p_date_to IS NULL THEN
1511
1512 stmt_po := stmt_po || ' AND 1 = 1 ';
1513
1514 ELSIF p_date_from IS NOT NULL AND p_date_to IS NULL THEN
1515
1516 stmt_po := stmt_po || ' AND POH.creation_date >= Trunc(PO_MASS_UPDATE_PO_PVT.get_date_from)';
1517
1518 ELSIF p_date_from IS NULL AND p_date_to IS NOT NULL THEN
1519
1520 stmt_po := stmt_po || ' AND POH.creation_date <= Trunc(PO_MASS_UPDATE_PO_PVT.get_date_to)';
1521
1522 ELSE
1523
1524 stmt_po := stmt_po || ' AND POH.creation_date >= Trunc(PO_MASS_UPDATE_PO_PVT.get_date_from)
1525 AND POH.creation_date < Trunc(PO_MASS_UPDATE_PO_PVT.get_date_to)+1';
1526
1527 END IF;
1528
1529
1530 IF p_supplier_id IS NOT NULL THEN
1531
1532 stmt_po := stmt_po || ' AND POH.vendor_id = PO_MASS_UPDATE_PO_PVT.get_supplier_id';
1533
1534 END IF;
1535
1536 IF p_include_close_po = 'YES' THEN
1537
1538 stmt_po := stmt_po || ' AND Nvl(POH.closed_code,''OPEN'') NOT IN (''FINALLY CLOSED'') '; /* 6868589 */
1539
1540 ELSE
1541
1542 stmt_po := stmt_po || ' AND Nvl(POH.closed_code,''OPEN'') NOT IN (''CLOSED'',''FINALLY CLOSED'') '; /* 6868589 */
1543
1544 END IF;
1545
1546 stmt_po := stmt_po || ' ORDER BY POH.segment1';
1547
1548 IF (p_document_type IS NULL OR p_document_type IN ('STANDARD','BLANKET','PLANNED','CONTRACT','ALL')) THEN -- <BUG 6988269>
1549
1550 OPEN c_po_approver for stmt_po;
1551 LOOP
1552
1553 FETCH c_po_approver INTO l_notification_id,
1554 l_po_num,
1555 l_doc_type;
1556
1557 EXIT WHEN c_po_approver%NOTFOUND;
1558
1559 l_progress := '004';
1560
1561 IF g_debug_stmt THEN
1562
1563 PO_DEBUG.debug_var(l_log_head,l_progress,'l_notification_id',l_notification_id );
1564 PO_DEBUG.debug_var(l_log_head,l_progress,'l_po_num',l_po_num );
1565 PO_DEBUG.debug_var(l_log_head,l_progress,'l_doc_type', l_doc_type);
1566
1567 END IF;
1568
1569
1570 BEGIN
1571
1572 SAVEPOINT Mass_Update_Forward_SP;
1573
1574 l_progress := '005';
1575
1576 Wf_Notification.Forward(l_notification_id, p_new_username,l_msg16);
1577
1578 IF g_debug_stmt THEN
1579
1580 PO_DEBUG.debug_var(l_log_head,l_progress,'p_new_username',p_new_username );
1581
1582 END IF;
1583
1584 l_commit_count := l_commit_count + 1;
1585
1586 IF l_commit_count = p_commit_interval THEN
1587
1588 COMMIT;
1589 l_commit_count := 0;
1590
1591 END IF;
1592
1593 l_progress := '006';
1594
1595 IF g_debug_stmt THEN
1596
1597 PO_DEBUG.debug_var(l_log_head,l_progress,'l_commit_count',l_commit_count );
1598
1599 END IF;
1600
1601
1602
1603 fnd_file.put_line(fnd_file.output, rpad(l_po_num,26) || rpad(l_doc_type,32) || l_msg17 );
1604
1605 EXCEPTION
1606
1607 WHEN OTHERS THEN
1608
1609 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1610
1611 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,l_log_head,l_progress|| SQLCODE || SUBSTR(SQLERRM,1,200));
1612
1613 END IF;
1614
1615 ROLLBACK TO Mass_Update_Forward_SP;
1616
1617 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1618
1619 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1620
1621 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_log_head );
1622
1623 END IF;
1624
1625 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => p_msg_count, p_data => p_msg_data );
1626
1627
1628 END;
1629
1630 END LOOP;
1631
1632 CLOSE c_po_approver;
1633
1634 END IF; -- < End of p_document_type >
1635
1636
1637 IF (p_document_type IS NULL OR p_document_type IN ('BLANKET','PLANNED','ALL') ) THEN -- <BUG 6988269 Added 'ALL' condition>
1638
1639
1640 stmt_rel := 'SELECT wfn.notification_id,
1641 poh.segment1,
1642 por.release_num,
1643 pdt.type_name
1644 FROM wf_notifications wfn,
1645 wf_item_activity_statuses wfa,
1646 po_headers poh,
1647 po_releases por,
1648 po_document_types_vl pdt
1649 WHERE wfn.notification_id = wfa.notification_id
1650 AND wfa.item_type = por.wf_item_type
1651 AND wfa.item_key = por.wf_item_key
1652 AND wfn.status NOT IN (''CLOSED'',''CANCELED'')
1653 AND Nvl(por.authorization_status,''INCOMPLETE'') IN (''IN PROCESS'',''PRE-APPROVED'')
1654 AND recipient_role = PO_Mass_Update_PO_PVT.get_old_username
1655 AND por.po_header_id = poh.po_header_id
1656 AND pdt.document_type_code = ''RELEASE''
1657 AND pdt.document_subtype = por.release_type';
1658
1659
1660 IF p_document_type IS NOT NULL AND p_document_type <> 'ALL' THEN -- <BUG 6988269>
1661
1662 stmt_rel := stmt_rel || ' AND ((PO_MASS_UPDATE_PO_PVT.get_document_type = ''PLANNED'' and por.release_type = ''SCHEDULED'')
1663
1664 OR (por.release_type = Nvl(PO_MASS_UPDATE_PO_PVT.get_document_type,por.release_type)))';
1665
1666 END IF;
1667
1668 IF ( po_num_type = 'NUMERIC' ) THEN
1669
1670 IF p_document_no_from IS NULL AND p_document_no_to IS NULL THEN
1671
1672 stmt_rel := stmt_rel || ' AND 1 = 1 ';
1673
1674 ELSIF p_document_no_from IS NOT NULL AND p_document_no_to IS NULL THEN
1675
1676 stmt_rel := stmt_rel || ' AND DECODE ( RTRIM ( POH.SEGMENT1,''0123456789'' ), NULL, To_Number ( POH.SEGMENT1 ) , NULL ) >= to_number(PO_MASS_UPDATE_PO_PVT.get_document_no_from)';
1677
1678 ELSIF p_document_no_from IS NULL AND p_document_no_to IS NOT NULL THEN
1679
1680 stmt_rel := stmt_rel || ' AND DECODE ( RTRIM ( POH.SEGMENT1,''0123456789'' ), NULL, To_Number ( POH.SEGMENT1 ) , NULL ) <= to_number(PO_MASS_UPDATE_PO_PVT.get_document_no_to)';
1681
1682 ELSE
1683
1684 stmt_rel := stmt_rel || ' AND DECODE ( RTRIM ( POH.SEGMENT1,''0123456789'' ), NULL, To_Number ( POH.SEGMENT1 ) , NULL )
1685
1686 BETWEEN to_number(PO_MASS_UPDATE_PO_PVT.get_document_no_from) AND to_number(PO_MASS_UPDATE_PO_PVT.get_document_no_to)';
1687
1688 END IF;
1689
1690 ELSE
1691
1692 IF p_document_no_from IS NULL AND p_document_no_to IS NULL THEN
1693
1694 stmt_rel := stmt_rel || ' AND 1 = 1 ';
1695
1696 ELSIF p_document_no_from IS NOT NULL AND p_document_no_to IS NULL THEN
1697
1698 stmt_rel := stmt_rel || ' AND POH.SEGMENT1 >= PO_MASS_UPDATE_PO_PVT.get_document_no_from';
1699
1700 ELSIF p_document_no_from IS NULL AND p_document_no_to IS NOT NULL THEN
1701
1702 stmt_rel := stmt_rel || ' AND POH.SEGMENT1 <= PO_MASS_UPDATE_PO_PVT.get_document_no_to';
1703
1704 ELSE
1705
1706 stmt_rel := stmt_rel || ' AND POH.SEGMENT1 BETWEEN PO_MASS_UPDATE_PO_PVT.get_document_no_from AND PO_MASS_UPDATE_PO_PVT.get_document_no_to';
1707
1708 END IF;
1709
1710 END IF; /* End of po_num_type = 'NUMERIC' */
1711
1712 /* Bug 6899092 Added Trunc condition in validating the date ranges */
1713
1714 IF p_date_from IS NULL AND p_date_to IS NULL THEN
1715
1716 stmt_rel := stmt_rel || ' AND 1 = 1 ';
1717
1718 ELSIF p_date_from IS NOT NULL AND p_date_to IS NULL THEN
1719
1720 stmt_rel := stmt_rel || ' AND POR.creation_date >= Trunc(PO_MASS_UPDATE_PO_PVT.get_date_from)';
1721
1722 ELSIF p_date_from IS NULL AND p_date_to IS NOT NULL THEN
1723
1724 stmt_rel := stmt_rel || ' AND POR.creation_date <= Trunc(PO_MASS_UPDATE_PO_PVT.get_date_to)';
1725
1726 ELSE
1727
1728 stmt_rel := stmt_rel || ' AND POR.creation_date >= Trunc(PO_MASS_UPDATE_PO_PVT.get_date_from)
1729 AND POR.creation_date < Trunc(PO_MASS_UPDATE_PO_PVT.get_date_to)+1';
1730
1731 END IF;
1732
1733 IF p_supplier_id IS NOT NULL THEN
1734
1735 stmt_rel := stmt_rel || ' AND POH.vendor_id = PO_MASS_UPDATE_PO_PVT.get_supplier_id';
1736
1737 END IF;
1738
1739 IF p_include_close_po = 'YES' THEN
1740
1741 stmt_rel := stmt_rel || ' AND Nvl(POR.closed_code,''OPEN'') NOT IN (''FINALLY CLOSED'') '; /* Bug 6868589 */
1742
1743 ELSE
1744
1745 stmt_rel := stmt_rel || ' AND Nvl(POR.closed_code,''OPEN'') NOT IN (''CLOSED'',''FINALLY CLOSED'') '; /* Bug 6868589 */
1746
1747 END IF;
1748
1749 stmt_rel := stmt_rel || ' ORDER BY poh.segment1, por.release_num';
1750
1751 OPEN c_rel_approver for stmt_rel;
1752
1753 LOOP
1754
1755 FETCH c_rel_approver INTO l_notification_id,
1756 l_po_num,
1757 l_rel_num,
1758 l_doc_type;
1759
1760 EXIT WHEN c_rel_approver%NOTFOUND;
1761
1762 l_progress := '007';
1763
1764 IF g_debug_stmt THEN
1765
1766 PO_DEBUG.debug_var(l_log_head,l_progress,'l_notification_id',l_notification_id );
1767 PO_DEBUG.debug_var(l_log_head,l_progress,'l_po_num',l_po_num );
1768 PO_DEBUG.debug_var(l_log_head,l_progress,'l_rel_num',l_rel_num );
1769 PO_DEBUG.debug_var(l_log_head,l_progress,'l_doc_type', l_doc_type);
1770
1771 END IF;
1772
1773 BEGIN
1774
1775 SAVEPOINT Mass_Update_Forward_SP;
1776
1777 Wf_Notification.Forward(l_notification_id, p_new_username,l_msg16);
1778
1779 l_progress := '008';
1780
1781 IF g_debug_stmt THEN
1782
1783 PO_DEBUG.debug_var(l_log_head,l_progress,'p_new_username',p_new_username );
1784
1785 END IF;
1786
1787 l_commit_count := l_commit_count + 1;
1788
1789 IF l_commit_count = p_commit_interval THEN
1790
1791 COMMIT;
1792 l_commit_count := 0;
1793
1794 END IF;
1795
1796 l_progress := '009';
1797
1798 IF g_debug_stmt THEN
1799
1800 PO_DEBUG.debug_var(l_log_head,l_progress,'l_commit_count',l_commit_count );
1801
1802 END IF;
1803
1804
1805 fnd_file.put_line(fnd_file.output, rpad(l_po_num || '-' || l_rel_num,32) || rpad(l_doc_type,26) || l_msg17);
1806
1807 EXCEPTION
1808
1809 WHEN OTHERS THEN
1810
1811 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1812
1813 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,l_log_head,l_progress|| SQLCODE || SUBSTR(SQLERRM,1,200));
1814
1815 END IF;
1816
1817 ROLLBACK TO Mass_Update_Forward_SP;
1818
1819 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1820
1821 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1822
1823 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_log_head );
1824
1825 END IF;
1826
1827 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => p_msg_count, p_data => p_msg_data );
1828
1829 END;
1830
1831 END LOOP;
1832
1833 CLOSE c_rel_approver;
1834
1835 END IF; /* End of p_document_type IS NULL */
1836
1837 EXCEPTION
1838
1839 WHEN OTHERS THEN
1840
1841 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1842
1843 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,l_log_head,l_progress|| SQLCODE || SUBSTR(SQLERRM,1,200));
1844
1845 END IF;
1846
1847 ROLLBACK TO PO_Mass_Update_Approver_SP;
1848
1849 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1850
1851 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1852
1853 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_log_head );
1854
1855 END IF;
1856
1857 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => p_msg_count, p_data => p_msg_data );
1858
1859 END Update_Approver;
1860
1861 --------------------------------------------------------------------------------------------------
1862 -- Start of Comments
1863
1864 -- API Name : Update_Deliver_To
1865 -- Type : Private
1866 -- Pre-reqs : None
1867 -- Function : Updates the old deliver to person with the new deliver to person provided.
1868
1869 -- Parameters :
1870
1871 -- IN : p_update_person Person needs to be updated(Buyer/Approver/Deliver_To).
1872 -- p_old_personid Id of the old person.
1873 -- p_new_personid Id of the new person.
1874 -- p_document_type Type of the document(STANDARD,BLANKET.CONTRACT,PLANNED).
1875 -- p_document_no_from Document number from.
1876 -- p_document_no_to Document number to.
1877 -- p_date_from Date from.
1878 -- p_date_to Date to.
1879 -- p_supplier_id Supplier id.
1880 -- p_include_close_po Include Close PO's or not (Value as Yes or No).
1881 -- p_commit_interval Commit interval.
1882
1883 -- OUT : p_msg_data Actual message in encoded format.
1884 -- p_msg_count Holds the number of messages in the API list.
1885 -- p_return_status Return status of the API (Includes 'S','E','U').
1886
1887 -- End of Comments
1888 --------------------------------------------------------------------------------------------------
1889
1890 PROCEDURE Update_Deliver_To(p_update_person IN VARCHAR2,
1891 p_old_personid IN NUMBER,
1892 p_new_personid IN NUMBER,
1893 p_document_type IN VARCHAR2,
1894 p_document_no_from IN VARCHAR2,
1895 p_document_no_to IN VARCHAR2,
1896 p_date_from IN DATE,
1897 p_date_to IN DATE,
1898 p_supplier_id IN NUMBER,
1899 p_include_close_po IN VARCHAR2,
1900 p_commit_interval IN NUMBER,
1901 p_msg_data OUT NOCOPY VARCHAR2,
1902 p_msg_count OUT NOCOPY NUMBER,
1903 p_return_status OUT NOCOPY VARCHAR2) IS
1904
1905 c_po g_po;
1906 c_rel g_rel;
1907 stmt_rel VARCHAR2(4000);
1908 stmt_po VARCHAR2(4000);
1909 po_num_type VARCHAR2(100);
1910 l_po_rowid ROWID;
1911 l_rel_rowid ROWID;
1912 l_po_num po_headers.segment1%TYPE;
1913 l_rel_num po_releases.release_num%TYPE;
1914 l_commit_count NUMBER := 0;
1915 l_progress VARCHAR2(3) := '000';
1916 l_log_head CONSTANT VARCHAR2(1000) := g_log_head||'Update_Deliver_To';
1917 l_doc_type po_document_types_all.type_name%TYPE;
1918 l_auth_status po_headers.authorization_status%TYPE;
1919 l_msg18 VARCHAR2(240);
1920 l_release_id po_releases.po_release_id%TYPE;
1921
1922 BEGIN
1923
1924 -- Intializing package variables
1925
1926 g_old_personid := p_old_personid;
1927 g_document_type := p_document_type;
1928 g_document_no_from := p_document_no_from;
1929 g_document_no_to := p_document_no_to;
1930 g_date_from := p_date_from;
1931 g_date_to := p_date_to;
1932 g_supplier_id := p_supplier_id;
1933
1934
1935 IF g_debug_stmt THEN
1936
1937 PO_DEBUG.debug_begin(l_log_head);
1938 PO_DEBUG.debug_var(l_log_head,l_progress,'p_update_person',p_update_person );
1939 PO_DEBUG.debug_var(l_log_head,l_progress,'p_old_personid', p_old_personid);
1940 PO_DEBUG.debug_var(l_log_head,l_progress,'p_new_personid', p_new_personid);
1941 PO_DEBUG.debug_var(l_log_head,l_progress,'p_document_type',p_document_type );
1942 PO_DEBUG.debug_var(l_log_head,l_progress,'p_document_no_from',p_document_no_from );
1943 PO_DEBUG.debug_var(l_log_head,l_progress,'p_date_from',p_date_from);
1944 PO_DEBUG.debug_var(l_log_head,l_progress,'p_date_to',p_date_to);
1945 PO_DEBUG.debug_var(l_log_head,l_progress,'p_supplier_id',p_supplier_id);
1946 PO_DEBUG.debug_var(l_log_head,l_progress,'p_include_close_po',p_include_close_po);
1947 PO_DEBUG.debug_var(l_log_head,l_progress,'p_commit_interval',p_commit_interval);
1948
1949 END IF;
1950
1951 SAVEPOINT PO_Mass_Update_DeliverTo_SP;
1952
1953 l_progress := '001';
1954
1955 IF g_debug_stmt THEN
1956
1957 PO_DEBUG.debug_var(l_log_head,l_progress,'Before Calling Buyer_Info','Before Calling Buyer_Info' );
1958
1959 END IF;
1960
1961 BEGIN
1962
1963 SAVEPOINT Update_DeliverTo_REC_SP;
1964
1965 Buyer_Info(p_old_personid,
1966 p_new_personid,
1967 p_supplier_id,
1968 p_old_buyer_name,
1969 p_new_buyer_name,
1970 p_old_username,
1971 p_new_username,
1972 p_new_user_display_name,
1973 p_old_buyer_user_id,
1974 p_new_buyer_user_id,
1975 p_supplier_name,
1976 p_org_name,
1977 p_msg_data,
1978 p_msg_count,
1979 p_return_status);
1980
1981 IF p_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1982 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1983 RETURN;
1984 END IF;
1985
1986
1987 l_progress := '002';
1988
1989 IF g_debug_stmt THEN
1990
1991 PO_DEBUG.debug_var(l_log_head,l_progress,'Before Calling Print_Output','Before Calling Print_Output' );
1992
1993 END IF;
1994
1995 IF (p_update_person = 'DELIVER TO' ) THEN
1996
1997 Print_Output(p_update_person,
1998 p_old_buyer_name,
1999 p_new_buyer_name,
2000 p_org_name,
2001 p_document_type,
2002 p_document_no_from,
2003 p_document_no_to,
2004 p_date_from,
2005 p_date_to,
2006 p_supplier_name,
2007 p_msg_data,
2008 p_msg_count,
2009 p_return_status);
2010
2011 END IF;
2012
2013 SELECT manual_po_num_type
2014 INTO po_num_type
2015 FROM po_system_parameters;
2016
2017 fnd_message.set_name('PO','PO_MUB_MSG_DELIVER_TO');
2018 l_msg18 := fnd_message.get;
2019
2020 l_progress := '003';
2021
2022 IF g_debug_stmt THEN
2023
2024 PO_DEBUG.debug_var(l_log_head,l_progress,'po_num_type',po_num_type );
2025
2026 END IF;
2027
2028 EXCEPTION
2029
2030 WHEN OTHERS THEN
2031
2032 ROLLBACK TO Update_DeliverTo_REC_SP;
2033
2034 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2035
2036 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,l_log_head,l_progress|| SQLCODE || SUBSTR(SQLERRM,1,200));
2037
2038 END IF;
2039
2040 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2041
2042 FND_MSG_PUB.Count_And_Get(
2043 p_count => p_msg_count,
2044 p_data => p_msg_data);
2045
2046 END;
2047
2048
2049 stmt_po := 'SELECT pod.ROWID,
2050 poh.segment1,
2051 pdt.type_name,
2052 pod.po_release_id
2053 FROM po_headers poh,
2054 po_document_types_vl pdt,
2055 po_distributions pod
2056 WHERE pod.deliver_to_person_id = PO_MASS_UPDATE_PO_PVT.get_old_personid
2057 AND poh.po_header_id = pod.po_header_id
2058 AND pdt.document_type_code IN (''PO'',''PA'')
2059 AND pdt.document_subtype = poh.type_lookup_code
2060 AND Nvl(poh.authorization_status,''INCOMPLETE'') IN (''APPROVED'',''REQUIRES REAPPROVAL'',''INCOMPLETE'',''REJECTED'',''IN PROCESS'',''PRE-APPROVED'')
2061 AND Nvl(poh.cancel_flag,''N'') = ''N''
2062 AND Nvl(poh.frozen_flag,''N'') = ''N''';
2063
2064 IF p_document_type IS NOT NULL AND p_document_type <> 'ALL' THEN -- <BUG 6988269>
2065
2066 stmt_po := stmt_po || 'AND poh.type_lookup_code = PO_MASS_UPDATE_PO_PVT.get_document_type';
2067
2068 END IF;
2069
2070 IF ( po_num_type = 'NUMERIC' ) THEN
2071
2072 IF p_document_no_from IS NULL AND p_document_no_to IS NULL THEN
2073
2074 stmt_po := stmt_po || ' AND 1 = 1 ';
2075
2076 ELSIF p_document_no_from IS NOT NULL AND p_document_no_to IS NULL THEN
2077
2078 stmt_po := stmt_po || ' AND DECODE ( RTRIM ( POH.SEGMENT1,''0123456789'' ), NULL, To_Number ( POH.SEGMENT1 ) , NULL ) >= to_number(PO_MASS_UPDATE_PO_PVT.get_document_no_from)';
2079
2080 ELSIF p_document_no_from IS NULL AND p_document_no_to IS NOT NULL THEN
2081
2082 stmt_po := stmt_po || ' AND DECODE ( RTRIM ( POH.SEGMENT1,''0123456789'' ), NULL, To_Number ( POH.SEGMENT1 ) , NULL ) <= to_number(PO_MASS_UPDATE_PO_PVT.get_document_no_to)';
2083
2084 ELSE
2085
2086 stmt_po := stmt_po || ' AND DECODE ( RTRIM ( POH.SEGMENT1,''0123456789'' ), NULL, To_Number ( POH.SEGMENT1 ) , NULL )
2087
2088 BETWEEN to_number(PO_MASS_UPDATE_PO_PVT.get_document_no_from) AND to_number(PO_MASS_UPDATE_PO_PVT.get_document_no_to)';
2089
2090 END IF;
2091
2092 ELSE
2093
2094 IF p_document_no_from IS NULL AND p_document_no_to IS NULL THEN
2095
2096 stmt_po := stmt_po || ' AND 1 = 1 ';
2097
2098 ELSIF p_document_no_from IS NOT NULL AND p_document_no_to IS NULL THEN
2099
2100 stmt_po := stmt_po || ' AND POH.SEGMENT1 >= PO_MASS_UPDATE_PO_PVT.get_document_no_from';
2101
2102 ELSIF p_document_no_from IS NULL AND p_document_no_to IS NOT NULL THEN
2103
2104 stmt_po := stmt_po || ' AND POH.SEGMENT1 <= PO_MASS_UPDATE_PO_PVT.get_document_no_to';
2105
2106 ELSE
2107
2108 stmt_po := stmt_po || ' AND POH.SEGMENT1 BETWEEN PO_MASS_UPDATE_PO_PVT.get_document_no_from AND PO_MASS_UPDATE_PO_PVT.get_document_no_to';
2109
2110 END IF;
2111
2112 END IF; /* End of po_num_type = 'NUMERIC' */
2113
2114 /* Bug 6899092 Added Trunc condition in validating the date ranges */
2115
2116 IF p_date_from IS NULL AND p_date_to IS NULL THEN
2117
2118 stmt_po := stmt_po || ' AND 1 = 1 ';
2119
2120 ELSIF p_date_from IS NOT NULL AND p_date_to IS NULL THEN
2121
2122 stmt_po := stmt_po || ' AND POH.creation_date >= Trunc(PO_MASS_UPDATE_PO_PVT.get_date_from)';
2123
2124 ELSIF p_date_from IS NULL AND p_date_to IS NOT NULL THEN
2125
2126 stmt_po := stmt_po || ' AND POH.creation_date <= Trunc(PO_MASS_UPDATE_PO_PVT.get_date_to)';
2127
2128 ELSE
2129 stmt_po := stmt_po || ' AND POH.creation_date >= Trunc(PO_MASS_UPDATE_PO_PVT.get_date_from)
2130 AND POH.creation_date < Trunc(PO_MASS_UPDATE_PO_PVT.get_date_to)+1';
2131
2132 END IF;
2133
2134 IF p_supplier_id IS NOT NULL THEN
2135
2136 stmt_po := stmt_po || ' AND POH.vendor_id = PO_MASS_UPDATE_PO_PVT.get_supplier_id';
2137
2138 END IF;
2139
2140 IF p_include_close_po = 'YES' THEN
2141
2142 stmt_po := stmt_po || ' AND Nvl(POH.closed_code,''OPEN'') NOT IN (''FINALLY CLOSED'') '; /* Bug 6868589 */
2143
2144 ELSE
2145
2146 stmt_po := stmt_po || ' AND Nvl(POH.closed_code,''OPEN'') NOT IN (''CLOSED'',''FINALLY CLOSED'') '; /* Bug 6868589 */
2147
2148 END IF;
2149
2150 stmt_po := stmt_po || ' ORDER BY POH.segment1';
2151
2152 IF (p_document_type IS NULL OR p_document_type IN ('STANDARD','BLANKET','PLANNED','CONTRACT','ALL')) THEN -- <BUG 6988269>
2153
2154 OPEN c_po for stmt_po;
2155
2156 LOOP
2157
2158 FETCH c_po INTO l_po_rowid,
2159 l_po_num,
2160 l_doc_type,
2161 l_release_id;
2162
2163 EXIT when c_po%NOTFOUND;
2164
2165 BEGIN
2166
2167 SAVEPOINT Update_DeliverTo_RECPO_SP;
2168
2169 l_progress := '004';
2170
2171 IF g_debug_stmt THEN
2172
2173 PO_DEBUG.debug_var(l_log_head,l_progress,'l_po_rowid',l_po_rowid );
2174 PO_DEBUG.debug_var(l_log_head,l_progress,'l_po_num',l_po_num );
2175 PO_DEBUG.debug_var(l_log_head,l_progress,'l_doc_type',l_doc_type );
2176
2177 END IF;
2178
2179
2180
2181 UPDATE po_distributions_all
2182 SET deliver_to_person_id = p_new_personid,
2183 last_update_date = sysdate,
2184 last_updated_by = fnd_global.user_id,
2185 last_update_login = fnd_global.login_id
2186 WHERE rowid = l_po_rowid
2187 AND po_release_id IS NULL;
2188
2189
2190 l_progress := '005';
2191
2192 IF g_debug_stmt THEN
2193
2194 PO_DEBUG.debug_var(l_log_head,l_progress,'p_new_personid',p_new_personid );
2195
2196 END IF;
2197
2198
2199 l_commit_count := l_commit_count + 1;
2200
2201 IF l_commit_count = p_commit_interval THEN
2202
2203 COMMIT;
2204 l_commit_count := 0;
2205
2206 END IF;
2207
2208 l_progress := '006';
2209
2210 IF g_debug_stmt THEN
2211
2212 PO_DEBUG.debug_var(l_log_head,l_progress,'l_commit_count',l_commit_count );
2213
2214 END IF;
2215
2216 IF l_release_id IS NULL THEN
2217
2218 fnd_file.put_line(fnd_file.output, rpad(l_po_num,26) || rpad(l_doc_type,32) || l_msg18 );
2219
2220 END IF;
2221
2222 EXCEPTION
2223
2224 WHEN OTHERS THEN
2225
2226 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2227
2228 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,l_log_head,l_progress||SQLCODE||SUBSTR(SQLERRM,1,200));
2229
2230 END IF;
2231
2232 ROLLBACK TO Update_DeliverTo_RECPO_SP;
2233
2234 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2235
2236 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2237
2238 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_log_head );
2239
2240 END IF;
2241
2242 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => p_msg_count, p_data => p_msg_data );
2243
2244 END;
2245
2246 END LOOP;
2247
2248 CLOSE c_po;
2249
2250 END IF; -- <End of p_document_type>
2251
2252 stmt_rel := 'SELECT pod.ROWID,
2253 poh.segment1,
2254 por.release_num,
2255 pdt.type_name
2256 FROM po_releases por,
2257 po_headers poh,
2258 po_document_types_vl pdt,
2259 po_distributions_all pod
2260 WHERE por.po_header_id = poh.po_header_id
2261 AND poh.po_header_id = pod.po_header_id
2262 AND pod.po_release_id = por.po_release_id /* Bug 6868589 */
2263 AND pod.deliver_to_person_id = PO_MASS_UPDATE_PO_PVT.get_old_personid
2264 AND pdt.document_type_code =''RELEASE''
2265 AND pdt.document_subtype = por.release_type
2266 AND Nvl(por.authorization_status,''INCOMPLETE'') IN (''APPROVED'',''REQUIRES REAPPROVAL'',''INCOMPLETE'',''REJECTED'',''IN PROCESS'',''PRE-APPROVED'')
2267 AND Nvl(por.cancel_flag,''N'') = ''N''
2268 AND Nvl(por.frozen_flag,''N'') = ''N''';
2269
2270 IF p_document_type IS NOT NULL AND p_document_type <> 'ALL' THEN -- <BUG 6988269>
2271
2272 stmt_rel := stmt_rel || ' AND ((PO_MASS_UPDATE_PO_PVT.get_document_type = ''PLANNED'' and por.release_type = ''SCHEDULED'')
2273
2274 OR (por.release_type = Nvl(PO_MASS_UPDATE_PO_PVT.get_document_type,por.release_type)))';
2275
2276 END IF;
2277
2278
2279 IF ( po_num_type = 'NUMERIC' ) THEN
2280
2281 IF p_document_no_from IS NULL AND p_document_no_to IS NULL THEN
2282
2283 stmt_rel := stmt_rel || ' AND 1 = 1 ';
2284
2285 ELSIF p_document_no_from IS NOT NULL AND p_document_no_to IS NULL THEN
2286
2287 stmt_rel := stmt_rel || ' AND DECODE ( RTRIM ( POH.SEGMENT1,''0123456789'' ), NULL, To_Number ( POH.SEGMENT1 ) , NULL ) >= to_number(PO_MASS_UPDATE_PO_PVT.get_document_no_from)';
2288
2289 ELSIF p_document_no_from IS NULL AND p_document_no_to IS NOT NULL THEN
2290
2291 stmt_rel := stmt_rel || ' AND DECODE ( RTRIM ( POH.SEGMENT1,''0123456789'' ), NULL, To_Number ( POH.SEGMENT1 ) , NULL ) <= to_number(PO_MASS_UPDATE_PO_PVT.get_document_no_to )';
2292
2293 ELSE
2294
2295 stmt_rel := stmt_rel || ' AND DECODE ( RTRIM ( POH.SEGMENT1,''0123456789'' ), NULL, To_Number ( POH.SEGMENT1 ) , NULL )
2296
2297 BETWEEN to_number(PO_MASS_UPDATE_PO_PVT.get_document_no_from) AND to_number(PO_MASS_UPDATE_PO_PVT.get_document_no_to)';
2298
2299 END IF;
2300
2301 ELSE
2302
2303 IF p_document_no_from IS NULL AND p_document_no_to IS NULL THEN
2304
2305 stmt_rel := stmt_rel || ' AND 1 = 1 ';
2306
2307 ELSIF p_document_no_from IS NOT NULL AND p_document_no_to IS NULL THEN
2308
2309 stmt_rel := stmt_rel || ' AND POH.SEGMENT1 >= PO_MASS_UPDATE_PO_PVT.get_document_no_from';
2310
2311 ELSIF p_document_no_from IS NULL AND p_document_no_to IS NOT NULL THEN
2312
2313 stmt_rel := stmt_rel || ' AND POH.SEGMENT1 <= PO_MASS_UPDATE_PO_PVT.get_document_no_to';
2314
2315 ELSE
2316
2317 stmt_rel := stmt_rel || ' AND POH.SEGMENT1 BETWEEN PO_MASS_UPDATE_PO_PVT.get_document_no_from AND PO_MASS_UPDATE_PO_PVT.get_document_no_to';
2318
2319 END IF;
2320
2321 END IF; /* End of po_num_type = 'NUMERIC' */
2322
2323 /* Bug 6899092 Added Trunc condition in validating the date ranges */
2324
2325 IF p_date_from IS NULL AND p_date_to IS NULL THEN
2326
2327 stmt_rel := stmt_rel || ' AND 1 = 1 ';
2328
2329 ELSIF p_date_from IS NOT NULL AND p_date_to IS NULL THEN
2330
2331 stmt_rel := stmt_rel || ' AND POR.creation_date >= Trunc(PO_MASS_UPDATE_PO_PVT.get_date_from)';
2332
2333 ELSIF p_date_from IS NULL AND p_date_to IS NOT NULL THEN
2334
2335 stmt_rel := stmt_rel || ' AND POR.creation_date <= Trunc(PO_MASS_UPDATE_PO_PVT.get_date_to)';
2336
2337 ELSE
2338
2339 stmt_rel := stmt_rel || ' AND POR.creation_date >= Trunc(PO_MASS_UPDATE_PO_PVT.get_date_from)
2340 AND POR.creation_date < Trunc(PO_MASS_UPDATE_PO_PVT.get_date_to)+1';
2341
2342 END IF;
2343
2344 IF p_supplier_id IS NOT NULL THEN
2345
2346 stmt_rel := stmt_rel || ' AND POH.vendor_id = PO_MASS_UPDATE_PO_PVT.get_supplier_id';
2347
2348 END IF;
2349
2350 IF p_include_close_po = 'YES' THEN
2351
2352 stmt_rel := stmt_rel || ' AND Nvl(POR.closed_code,''OPEN'') NOT IN (''FINALLY CLOSED'') '; /* Bug 6868589 */
2353
2354 ELSE
2355
2356 stmt_rel := stmt_rel || ' AND Nvl(POR.closed_code,''OPEN'') NOT IN (''CLOSED'',''FINALLY CLOSED'') '; /* Bug 6868589 */
2357
2358 END IF;
2359
2360 stmt_rel := stmt_rel || ' ORDER BY POH.segment1,POR.release_num';
2361
2362
2363 IF (p_document_type IS NULL OR p_document_type IN ('BLANKET','PLANNED','ALL')) THEN -- <BUG 6988269 Added 'ALL' condition>
2364
2365 OPEN c_rel for stmt_rel;
2366
2367 LOOP
2368
2369 FETCH c_rel INTO l_rel_rowid,
2370 l_po_num,
2371 l_rel_num,
2372 l_doc_type;
2373
2374 EXIT WHEN c_rel%NOTFOUND;
2375
2376 BEGIN
2377
2378 SAVEPOINT Update_DeliverTo_RECREL_SP;
2379
2380 l_progress := '007';
2381
2382 IF g_debug_stmt THEN
2383
2384 PO_DEBUG.debug_var(l_log_head,l_progress,'l_rel_rowid',l_rel_rowid );
2385 PO_DEBUG.debug_var(l_log_head,l_progress,'l_po_num',l_po_num );
2386 PO_DEBUG.debug_var(l_log_head,l_progress,'l_rel_num',l_rel_num );
2387 PO_DEBUG.debug_var(l_log_head,l_progress,'l_doc_type',l_doc_type );
2388
2389 END IF;
2390
2391 UPDATE po_distributions_all
2392 SET deliver_to_person_id = p_new_personid,
2393 last_update_date = sysdate,
2394 last_updated_by = fnd_global.user_id,
2395 last_update_login = fnd_global.login_id
2396 WHERE rowid = l_rel_rowid;
2397
2398 l_progress := '007';
2399
2400 IF g_debug_stmt THEN
2401
2402 PO_DEBUG.debug_var(l_log_head,l_progress,'p_new_personid', p_new_personid);
2403
2404 END IF;
2405
2406
2407 l_commit_count := l_commit_count + 1;
2408
2409 IF l_commit_count = p_commit_interval then
2410
2411 COMMIT;
2412 l_commit_count := 0;
2413
2414 END IF;
2415
2416
2417 l_progress := '007';
2418
2419 IF g_debug_stmt THEN
2420
2421 PO_DEBUG.debug_var(l_log_head,l_progress,'l_commit_count', l_commit_count);
2422
2423 END IF;
2424
2425 fnd_file.put_line(fnd_file.output, rpad(l_po_num || '-' || l_rel_num,32) || rpad(l_doc_type,26) || l_msg18);
2426
2427 EXCEPTION
2428
2429 WHEN OTHERS THEN
2430
2431 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2432
2433 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,l_log_head,l_progress|| SQLCODE || SUBSTR(SQLERRM,1,200));
2434
2435 END IF;
2436
2437 ROLLBACK TO Update_DeliverTo_RECREL_SP;
2438
2439 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2440
2441 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2442
2443 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_log_head );
2444
2445 END IF;
2446
2447 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => p_msg_count, p_data => p_msg_data );
2448
2449 END;
2450
2451 END LOOP;
2452
2453 CLOSE c_rel;
2454
2455 END IF; /* End of p_document_type IS NULL */
2456
2457 EXCEPTION
2458
2459 WHEN OTHERS THEN
2460
2461 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2462
2463 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,l_log_head,l_progress|| SQLCODE || SUBSTR(SQLERRM,1,200));
2464
2465 END IF;
2466
2467 ROLLBACK TO PO_Mass_Update_DeliverTo_SP;
2468
2469 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2470
2471 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2472
2473 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_log_head );
2474
2475 END IF;
2476
2477 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => p_msg_count, p_data => p_msg_data );
2478
2479 END Update_Deliver_To;
2480
2481 --------------------------------------------------------------------------------------------------
2482 -- Start of Comments
2483
2484 -- API Name : Print_Output
2485 -- Type : Private
2486 -- Pre-reqs : None
2487 -- Function : Prints the header and body of the output file showing the documents and
2488 -- document types updated along with the person who have been updated in the
2489 -- document.
2490
2491 -- Parameters :
2492
2493 -- IN : p_update_person Person needs to be updated(Buyer/Approver/Deliver_To).
2494 -- p_old_buyer_name Buyer name of the old person.
2495 -- p_new_buyer_name Buyer name of the new person.
2496 -- p_org_name Operating unit name.
2497 -- p_document_type Type of the document(STANDARD,BLANKET.CONTRACT,PLANNED).
2498 -- p_document_no_from Document number from.
2499 -- p_document_no_to Document number to.
2500 -- p_date_from Date from.
2501 -- p_date_to Date to.
2502 -- p_supplier_name Supplier name.
2503
2504 -- OUT : p_msg_data Actual message in encoded format.
2505 -- p_msg_count Holds the number of messages in the API list.
2506 -- p_return_status Return status of the API (Includes 'S','E','U').
2507
2508 -- End of Comments
2509 --------------------------------------------------------------------------------------------------
2510
2511 PROCEDURE Print_Output(p_update_person IN VARCHAR2,
2512 p_old_buyer_name IN VARCHAR2,
2513 p_new_buyer_name IN VARCHAR2,
2514 p_org_name IN VARCHAR2,
2515 p_document_type IN VARCHAR2,
2516 p_document_no_from IN VARCHAR2,
2517 p_document_no_to IN VARCHAR2,
2518 p_date_from IN DATE,
2519 p_date_to IN DATE,
2520 p_supplier_name IN VARCHAR2,
2521 p_msg_data OUT NOCOPY VARCHAR2,
2522 p_msg_count OUT NOCOPY NUMBER,
2523 p_return_status OUT NOCOPY VARCHAR2) IS
2524
2525 l_msg1 VARCHAR2(240);
2526 l_msg2 VARCHAR2(240);
2527 l_msg3 VARCHAR2(240);
2528 l_msg4 VARCHAR2(240);
2529 l_msg5 VARCHAR2(240);
2530 l_msg6 VARCHAR2(240);
2531 l_msg7 VARCHAR2(240);
2532 l_msg8 VARCHAR2(240);
2533 l_msg9 VARCHAR2(240);
2534 l_msg10 VARCHAR2(240);
2535 l_msg11 VARCHAR2(240);
2536 l_msg12 VARCHAR2(240);
2537 l_msg13 VARCHAR2(240);
2538 l_msg14 VARCHAR2(240);
2539 l_msg15 VARCHAR2(240);
2540
2541
2542 l_progress VARCHAR2(3);
2543 l_log_head CONSTANT VARCHAR2(1000) := g_log_head||'Print_Output';
2544
2545 BEGIN
2546
2547 l_progress := '000';
2548
2549 IF g_debug_stmt THEN
2550
2551 PO_DEBUG.debug_begin(l_log_head);
2552 PO_DEBUG.debug_var(l_log_head,l_progress,'p_update_person',p_update_person );
2553 PO_DEBUG.debug_var(l_log_head,l_progress,'p_old_buyer_name',p_old_buyer_name );
2554 PO_DEBUG.debug_var(l_log_head,l_progress,'p_new_buyer_name',p_new_buyer_name );
2555 PO_DEBUG.debug_var(l_log_head,l_progress,'p_org_name',p_org_name );
2556 PO_DEBUG.debug_var(l_log_head,l_progress,'p_document_type',p_document_type );
2557 PO_DEBUG.debug_var(l_log_head,l_progress,'p_document_no_from',p_document_no_from );
2558 PO_DEBUG.debug_var(l_log_head,l_progress,'p_document_no_to',p_document_no_to );
2559 PO_DEBUG.debug_var(l_log_head,l_progress,'p_date_from', p_date_from);
2560 PO_DEBUG.debug_var(l_log_head,l_progress,'p_date_to', p_date_to);
2561 PO_DEBUG.debug_var(l_log_head,l_progress,'p_supplier_name',p_supplier_name );
2562
2563 END IF;
2564
2565 fnd_message.set_name('PO','PO_MUB_MSG_BUYER_HEADER1');
2566 l_msg1 := fnd_message.get;
2567
2568 fnd_message.set_name('PO','PO_MUB_MSG_DATE');
2569 l_msg2 := fnd_message.get;
2570
2571 fnd_message.set_name('PO','PO_MUB_MSG_OU');
2572 l_msg3 := fnd_message.get;
2573
2574 fnd_message.set_name('PO','PO_MUB_MSG_OLD_PERSON');
2575 l_msg4 := fnd_message.get;
2576
2577 fnd_message.set_name('PO','PO_MUB_MSG_NEW_PERSON');
2578 l_msg5 := fnd_message.get;
2579
2580 fnd_message.set_name('PO','PO_MUB_MSG_DOC_SUB_TYPE');
2581 l_msg6 := fnd_message.get;
2582
2583 fnd_message.set_name('PO','PO_MUB_MSG_DOC_NUM_FROM');
2584 l_msg7 := fnd_message.get;
2585
2586 fnd_message.set_name('PO','PO_MUB_MSG_DOC_NUM_TO');
2587 l_msg8 := fnd_message.get;
2588
2589 fnd_message.set_name('PO','PO_MUB_MSG_DATE_FROM');
2590 l_msg9 := fnd_message.get;
2591
2592 fnd_message.set_name('PO','PO_MUB_MSG_DATE_TO');
2593 l_msg10 := fnd_message.get;
2594
2595 fnd_message.set_name('PO','PO_MUB_MSG_SUPPLIER');
2596 l_msg11 := fnd_message.get;
2597
2598 SAVEPOINT Print_SP;
2599
2600 IF (p_update_person = 'BUYER') THEN
2601
2602 fnd_message.set_name('PO','PO_MUB_MSG_BUYER_HEADER2');
2603 fnd_message.set_token('OLD_BUYER',p_old_buyer_name);
2604 fnd_message.set_token('NEW_BUYER',p_new_buyer_name);
2605
2606 ELSIF (p_update_person = 'APPROVER') THEN
2607
2608 fnd_message.set_name('PO','PO_MUB_MSG_BUYER_HEADER3');
2609 fnd_message.set_token('OLD_APPROVER',p_old_buyer_name);
2610 fnd_message.set_token('NEW_APPROVER',p_new_buyer_name);
2611
2612 ELSIF (p_update_person = 'DELIVER TO') THEN
2613
2614 fnd_message.set_name('PO','PO_MUB_MSG_BUYER_HEADER4');
2615 fnd_message.set_token('OLD_DELIVER_TO_PERSON',p_old_buyer_name);
2616 fnd_message.set_token('NEW_DELIVER_TO_PERSON',p_new_buyer_name);
2617
2618 ELSIF (p_update_person = 'ALL') THEN
2619
2620 fnd_message.set_name('PO','PO_MUB_MSG_BUYER_HEADER5');
2621 fnd_message.set_token('OLD_PERSON',p_old_buyer_name);
2622 fnd_message.set_token('NEW_PERSON',p_new_buyer_name);
2623
2624 END IF;
2625
2626 l_progress := '001';
2627
2628 IF g_debug_stmt THEN
2629
2630 PO_DEBUG.debug_var(l_log_head,l_progress,'p_update_person',p_update_person );
2631
2632 END IF;
2633
2634 l_msg12 := fnd_message.get;
2635
2636 fnd_message.set_name('PO','PO_MUB_MSG_DOC_NUM');
2637 l_msg13 := fnd_message.get;
2638
2639 fnd_message.set_name('PO','PO_MUB_MSG_DOC_TYPE');
2640 l_msg14 := fnd_message.get;
2641
2642 fnd_message.set_name('PO','PO_MUB_MSG_PERSON');
2643 l_msg15 := fnd_message.get;
2644
2645 l_progress := '002';
2646
2647 fnd_file.put_line(fnd_file.output, l_msg1);
2648 fnd_file.put_line(fnd_file.output, ' ');
2649 fnd_file.put_line(fnd_file.output, rpad(l_msg2,21) || ' : ' || sysdate);
2650 fnd_file.put_line(fnd_file.output, rpad(l_msg3,21) || ' : ' || p_org_name);
2651 fnd_file.put_line(fnd_file.output, rpad(l_msg4,21) || ' : ' || p_old_buyer_name);
2652 fnd_file.put_line(fnd_file.output, rpad(l_msg5,21) || ' : ' || p_new_buyer_name);
2653 fnd_file.put_line(fnd_file.output, rpad(l_msg6,21) || ' : ' || p_document_type);
2654 l_progress := '003';
2655 fnd_file.put_line(fnd_file.output, rpad(l_msg7,21) || ' : ' || p_document_no_from);
2656 fnd_file.put_line(fnd_file.output, rpad(l_msg8,21) || ' : ' || p_document_no_to);
2657 fnd_file.put_line(fnd_file.output, rpad(l_msg9,21) || ' : ' || p_date_from);
2658 fnd_file.put_line(fnd_file.output, rpad(l_msg10,21) || ' : ' || p_date_to);
2659 fnd_file.put_line(fnd_file.output, rpad(l_msg11,21) || ' : ' || p_supplier_name);
2660 l_progress := '004';
2661 fnd_file.put_line(fnd_file.output, ' ');
2662 fnd_file.put_line(fnd_file.output, l_msg12);
2663 fnd_file.put_line(fnd_file.output, ' ');
2664 fnd_file.put_line(fnd_file.output, rpad(l_msg13,26) || rpad(l_msg14,32) || l_msg15);
2665 fnd_file.put_line(fnd_file.output, rpad('-',70,'-'));
2666
2667 EXCEPTION
2668
2669 WHEN OTHERS THEN
2670
2671 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2672
2673 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,l_log_head,l_progress|| SQLCODE || SUBSTR(SQLERRM,1,200));
2674
2675 END IF;
2676
2677 ROLLBACK TO Print_SP;
2678
2679 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2680
2681 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2682
2683 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_log_head );
2684
2685 END IF;
2686
2687 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => p_msg_count, p_data => p_msg_data );
2688
2689 END Print_Output;
2690
2691 --------------------------------------------------------------------------------------------------
2692 -- Start of Comments
2693
2694 -- API Name : Buyer_Info
2695 -- Type : Private
2696 -- Pre-reqs : None
2697 -- Function : Gets the Buyer Information.
2698
2699 -- Parameters :
2700
2701 -- IN : p_old_personid Id of the old person.
2702 -- p_new_personid Id of the new person.
2703 -- p_supplier_id Supplier id.
2704
2705 -- OUT : p_old_buyer_name Buyer name of the old person.
2706 -- p_new_buyer_name Buyer name of the new person.
2707 -- p_old_username User name of the old person.
2708 -- p_new_username User name of the new person.
2709 -- p_new_user_display_name Display name of the new person.
2710 -- p_old_buyer_user_id Old person's Buyer user id.
2711 -- p_new_buyer_user_id New person's Buyer user id.
2712 -- p_supplier_name Supplier name.
2713 -- p_org_name Operating unit name.
2714 -- p_msg_data Actual message in encoded format.
2715 -- p_msg_count Holds the number of messages in the API list.
2716 -- p_return_status Return status of the API (Includes 'S','E','U').
2717
2718 -- End of Comments
2719 --------------------------------------------------------------------------------------------------
2720
2721 PROCEDURE Buyer_Info(p_old_personid IN NUMBER,
2722 p_new_personid IN NUMBER,
2723 p_supplier_id IN NUMBER,
2724 p_old_buyer_name OUT NOCOPY VARCHAR2,
2725 p_new_buyer_name OUT NOCOPY VARCHAR2,
2726 p_old_username OUT NOCOPY VARCHAR2,
2727 p_new_username OUT NOCOPY VARCHAR2,
2728 p_new_user_display_name OUT NOCOPY VARCHAR2,
2729 p_old_buyer_user_id OUT NOCOPY NUMBER,
2730 p_new_buyer_user_id OUT NOCOPY NUMBER,
2731 p_supplier_name OUT NOCOPY VARCHAR2,
2732 p_org_name OUT NOCOPY VARCHAR2,
2733 p_msg_data OUT NOCOPY VARCHAR2,
2734 p_msg_count OUT NOCOPY NUMBER,
2735 p_return_status OUT NOCOPY VARCHAR2) IS
2736
2737 l_progress VARCHAR2(3);
2738 l_log_head CONSTANT VARCHAR2(1000) := g_log_head||'Buyer_Info';
2739 l_org_id NUMBER;
2740
2741 cursor c_old_buyer IS
2742 select name,
2743 substrb(display_name,1,360)
2744 p_display_name
2745 from wf_local_roles
2746 where orig_system = 'PER'
2747 and orig_system_id = p_old_personid
2748 order by status, start_date; ---Bug 9949640
2749
2750 BEGIN
2751
2752 l_progress := '000';
2753
2754 IF g_debug_stmt THEN
2755
2756 PO_DEBUG.debug_begin(l_log_head);
2757 PO_DEBUG.debug_var(l_log_head,l_progress,'p_old_personid',p_old_personid );
2758 PO_DEBUG.debug_var(l_log_head,l_progress,'p_new_personid',p_new_personid );
2759 PO_DEBUG.debug_var(l_log_head,l_progress,'p_supplier_id',p_supplier_id );
2760
2761 END IF;
2762
2763 SAVEPOINT Buyer_Info_SP;
2764
2765 l_progress := '001';
2766
2767 p_old_buyer_name := PO_EMPLOYEES_SV.get_emp_name(p_old_personid);
2768
2769 IF g_debug_stmt THEN
2770
2771 PO_DEBUG.debug_var(l_log_head,l_progress,'p_old_buyer_name',p_old_buyer_name );
2772
2773 END IF;
2774
2775 p_new_buyer_name := PO_EMPLOYEES_SV.get_emp_name(p_new_personid);
2776
2777
2778 IF g_debug_stmt THEN
2779
2780 PO_DEBUG.debug_var(l_log_head,l_progress,'p_new_buyer_name',p_new_buyer_name );
2781
2782 END IF;
2783
2784 WF_DIRECTORY.GetUserName('PER',p_old_personid, p_old_username,p_old_user_display_name);
2785 IF g_debug_stmt THEN
2786
2787 PO_DEBUG.debug_var(l_log_head,l_progress,'p_old_username',p_old_username );
2788 PO_DEBUG.debug_var(l_log_head,l_progress,'p_old_user_display_name',p_old_user_display_name );
2789
2790
2791 END IF;
2792
2793 ---Bug 9949640Start
2794
2795 IF p_old_username IS NULL AND p_old_user_display_name IS NULL
2796 THEN
2797
2798 open c_old_buyer;
2799 fetch c_old_buyer into p_old_username, p_old_user_display_name;
2800 close c_old_buyer;
2801
2802 IF g_debug_stmt THEN
2803
2804 PO_DEBUG.debug_var(l_log_head,l_progress,'p_old_user_display_name',p_old_user_display_name );
2805 PO_DEBUG.debug_var(l_log_head,l_progress,'p_old_username',p_old_username );
2806
2807 END IF;
2808 END IF;
2809
2810 ---- 9949640 End
2811
2812
2813 WF_DIRECTORY.GetUserName('PER',p_new_personid, p_new_username,p_new_user_display_name);
2814 IF g_debug_stmt THEN
2815
2816 PO_DEBUG.debug_var(l_log_head,l_progress,'p_new_username',p_new_username );
2817 PO_DEBUG.debug_var(l_log_head,l_progress,'p_new_user_display_name',p_new_user_display_name );
2818
2819
2820 END IF;
2821
2822 --For Bug 13711743
2823 --Handling this SQL in exception to continue the process
2824 --though Fnd User id of old buyer is fetched or not.
2825 BEGIN
2826 SELECT user_id
2827 INTO p_old_buyer_user_id
2828 FROM fnd_user
2829 WHERE employee_id = p_old_personid
2830 AND user_name = p_old_username;
2831 EXCEPTION
2832 WHEN OTHERS THEN
2833 NULL;
2834 END;
2835
2836 IF g_debug_stmt THEN
2837
2838 PO_DEBUG.debug_var(l_log_head,l_progress,'p_old_buyer_user_id',p_old_buyer_user_id );
2839
2840 END IF;
2841
2842 /*Bug 14475766 start.
2843 Added exception handling in case the new person is not an apps user
2844 */
2845 BEGIN
2846 SELECT user_id
2847 INTO p_new_buyer_user_id
2848 FROM fnd_user
2849 WHERE employee_id = p_new_personid
2850 AND user_name = p_new_username;
2851 EXCEPTION
2852 WHEN NO_DATA_FOUND THEN
2853 IF g_debug_stmt THEN
2854 PO_DEBUG.debug_var(l_log_head,l_progress,'The new person is not APPS user.','The new person is not APPS user.' );
2855 END IF;
2856 p_new_buyer_user_id := null;
2857 END;
2858 --Bug 14475766 end.
2859
2860 IF g_debug_stmt THEN
2861
2862 PO_DEBUG.debug_var(l_log_head,l_progress,'p_new_buyer_user_id',p_new_buyer_user_id );
2863
2864 END IF;
2865
2866 IF (p_supplier_id IS NOT NULL) then
2867
2868 SELECT vendor_name
2869 INTO p_supplier_name
2870 FROM po_vendors
2871 WHERE vendor_id = p_supplier_id;
2872
2873 END IF;
2874
2875 SELECT org_id
2876 INTO l_org_id
2877 FROM po_system_parameters;
2878
2879 SELECT hou.name
2880 INTO p_org_name
2881 FROM hr_all_organization_units hou,
2882 hr_all_organization_units_tl hout
2883 WHERE hou.organization_id = hout.organization_id
2884 AND hout.LANGUAGE = UserEnv('LANG')
2885 AND hou.organization_id = l_org_id;
2886
2887 IF g_debug_stmt THEN
2888
2889 PO_DEBUG.debug_var(l_log_head,l_progress,'p_old_buyer_name', p_old_buyer_name);
2890 PO_DEBUG.debug_var(l_log_head,l_progress,'p_new_buyer_name', p_new_buyer_name);
2891 PO_DEBUG.debug_var(l_log_head,l_progress,'p_old_username', p_old_username);
2892 PO_DEBUG.debug_var(l_log_head,l_progress,'p_new_username', p_new_username);
2893 PO_DEBUG.debug_var(l_log_head,l_progress,'p_new_user_display_name', p_new_user_display_name);
2894 PO_DEBUG.debug_var(l_log_head,l_progress,'p_old_buyer_user_id', p_old_buyer_user_id);
2895 PO_DEBUG.debug_var(l_log_head,l_progress,'p_new_buyer_user_id', p_new_buyer_user_id);
2896 PO_DEBUG.debug_var(l_log_head,l_progress,'p_supplier_name',p_supplier_name);
2897 PO_DEBUG.debug_var(l_log_head,l_progress,'p_org_name ', p_org_name);
2898
2899 END IF;
2900
2901 EXCEPTION
2902
2903 WHEN OTHERS THEN
2904 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2905
2906 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,l_log_head,l_progress || SQLCODE || SUBSTR(SQLERRM,1,200));
2907
2908 END IF;
2909
2910 ROLLBACK TO Buyer_Info_SP;
2911
2912 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2913
2914 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2915
2916 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_log_head );
2917
2918 END IF;
2919
2920 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => p_msg_count, p_data => p_msg_data );
2921
2922 END Buyer_Info;
2923
2924 --------------------------------------------------------------------------------------------------
2925
2926 -- Functions declared to return the value of the parameters passed in this API.
2927
2928 --------------------------------------------------------------------------------------------------
2929
2930 FUNCTION get_old_personid RETURN NUMBER
2931 IS
2932 BEGIN
2933 RETURN g_old_personid;
2934 END;
2935
2936 FUNCTION get_document_type RETURN VARCHAR2
2937 IS
2938 BEGIN
2939 RETURN g_document_type;
2940 END;
2941
2942 FUNCTION get_document_no_from RETURN VARCHAR2
2943 IS
2944 BEGIN
2945 RETURN g_document_no_from;
2946 END;
2947
2948 FUNCTION get_document_no_to RETURN VARCHAR2
2949 IS
2950 BEGIN
2951 RETURN g_document_no_to;
2952 END;
2953
2954 FUNCTION get_date_from RETURN DATE
2955 IS
2956 BEGIN
2957 RETURN g_date_from;
2958 END;
2959
2960 FUNCTION get_date_to RETURN DATE
2961 IS
2962 BEGIN
2963 RETURN g_date_to;
2964 END;
2965
2966 FUNCTION get_supplier_id RETURN NUMBER
2967 IS
2968 BEGIN
2969 RETURN g_supplier_id;
2970 END;
2971
2972 FUNCTION get_old_username RETURN VARCHAR2
2973 IS
2974 BEGIN
2975 RETURN g_old_username;
2976 END;
2977
2978 END PO_Mass_Update_PO_PVT;