[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.9 2008/04/24 06:20:35 rakchakr noship $*/
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
663 UPDATE po_headers_all
664 SET agent_id = p_new_personid,
665 last_update_date = sysdate,
666 last_updated_by = fnd_global.user_id,
667 last_update_login = fnd_global.login_id
668 WHERE rowid = l_po_rowid;
669
670 l_progress := '005';
671
672 IF g_debug_stmt THEN
673
674 PO_DEBUG.debug_var(l_log_head,l_progress,'p_new_personid',p_new_personid );
675
676 END IF;
677
678 IF ( (l_auth_status='PRE-APPROVED') OR (l_auth_status='IN PROCESS') ) THEN
679
680 l_preparer_id := po_wf_util_pkg.GetItemAttrText ( itemtype => l_itemtype,
681 itemkey => l_itemkey,
682 aname => 'PREPARER_ID');
683
684 IF (l_preparer_id = p_old_personid) THEN
685
686 po_wf_util_pkg.SetItemAttrText ( itemtype => l_itemtype,
687 itemkey => l_itemkey,
688 aname => 'PREPARER_USER_NAME' ,
689 avalue => p_new_username);
690
691 po_wf_util_pkg.SetItemAttrText ( itemtype => l_itemtype,
692 itemkey => l_itemkey,
693 aname => 'PREPARER_DISPLAY_NAME' ,
694 avalue => p_new_user_display_name);
695
696 po_wf_util_pkg.SetItemAttrText ( itemtype => l_itemtype,
697 itemkey => l_itemkey,
698 aname => 'PREPARER_ID' ,
699 avalue => p_new_personid);
700
701 END IF;
702
703 l_buyer_user_id := po_wf_util_pkg.GetItemAttrText ( itemtype => l_itemtype,
704 itemkey => l_itemkey,
705 aname => 'BUYER_USER_ID');
706
707 IF (l_buyer_user_id = p_old_buyer_user_id) THEN
708
709 po_wf_util_pkg.SetItemAttrText ( itemtype => l_itemtype,
710 itemkey => l_itemkey,
711 aname => 'BUYER_USER_ID' ,
712 avalue => p_new_buyer_user_id);
713
714 po_wf_util_pkg.SetItemAttrText ( itemtype => l_itemtype,
715 itemkey => l_itemkey,
716 aname => 'BUYER_USER_NAME' ,
717 avalue => p_new_username);
718
719 END IF;
720
721 l_forward_from_id := po_wf_util_pkg.GetItemAttrText ( itemtype => l_itemtype,
722 itemkey => l_itemkey,
723 aname => 'FORWARD_FROM_ID');
724
725 IF (l_forward_from_id = p_old_personid) THEN
726
727 po_wf_util_pkg.SetItemAttrText ( itemtype => l_itemtype,
728 itemkey => l_itemkey,
729 aname => 'FORWARD_FROM_DISP_NAME' ,
730 avalue => p_new_user_display_name);
731
732 po_wf_util_pkg.SetItemAttrText ( itemtype => l_itemtype,
733 itemkey => l_itemkey,
734 aname => 'FORWARD_FROM_ID' ,
735 avalue => p_new_personid);
736
737 po_wf_util_pkg.SetItemAttrText ( itemtype => l_itemtype,
738 itemkey => l_itemkey,
739 aname => 'FORWARD_FROM_USER_NAME' ,
740 avalue => p_new_username);
741
742 END IF;
743 END IF; /* End of l_auth_status = 'PRE APPROVED' */
744
745 l_commit_count := l_commit_count + 1;
746
747 l_progress := '006';
748
749 IF g_debug_stmt THEN
750
751 PO_DEBUG.debug_var(l_log_head,l_progress,'l_commit_count',l_commit_count );
752
753 END IF;
754
755 IF (UPPER(l_conterms_exist_flag)='Y') THEN
756
757 l_row_index := l_row_index + 1;
758
759 l_busdocs_tbl(l_row_index).bus_doc_id := l_document_id;
760 l_busdocs_tbl(l_row_index).bus_doc_version := l_document_version;
761
762 IF (p_document_type IN ('BLANKET', 'CONTRACT')) THEN
763
764 l_contracts_document_type := 'PA_'||p_document_type;
765
766 ELSIF (p_document_type = 'STANDARD') THEN
767
768 l_contracts_document_type := 'PO_'||p_document_type;
769
770 END IF;
771
772 l_busdocs_tbl(l_row_index).bus_doc_type := l_contracts_document_type;
773 END IF;
774
775 IF l_commit_count = p_commit_interval THEN
776
777 IF (l_busdocs_tbl.COUNT >= 1) THEN
778
779 okc_manage_deliverables_grp.updateIntContactOnDeliverables (
780 p_api_version => 1.0,
781 p_init_msg_list => FND_API.G_FALSE,
782 p_commit => FND_API.G_FALSE,
783 p_bus_docs_tbl => l_busdocs_tbl,
784 p_original_internal_contact_id => p_old_personid,
785 p_new_internal_contact_id => p_new_personid,
786 x_msg_data => p_msg_data,
787 x_msg_count => p_msg_count,
788 x_return_status => p_return_status);
789
790 IF (p_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
791
792 FND_MSG_PUB.Count_and_Get(p_count => p_msg_count,p_data => p_msg_data);
793 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
794
795 END IF;
796
797 END IF;
798
799 COMMIT;
800 l_commit_count := 0;
801
802 l_busdocs_tbl := l_empty_busdocs_tbl;
803 l_row_index := 0;
804
805 END IF;
806
807 fnd_file.put_line(fnd_file.output, rpad(l_po_num,26) || rpad(l_doc_type,32) || l_msg15 );
808
809 EXCEPTION
810
811 WHEN OTHERS THEN
812
813 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
814
815 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,l_log_head,l_progress|| SQLCODE || SUBSTR(SQLERRM,1,200));
816
817 END IF;
818
819 ROLLBACK TO Update_Buyer_REC_PO_SP;
820
821 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
822
823 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
824
825 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_log_head );
826
827 END IF;
828
829 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => p_msg_count, p_data => p_msg_data );
830
831 END;
832
833
834 END LOOP;
835
836 CLOSE c_po;
837
838 END IF; -- < End of p_document_type >
839
840 IF (l_busdocs_tbl.COUNT >= 1) THEN
841
842 okc_manage_deliverables_grp.updateIntContactOnDeliverables (
843 p_api_version => 1.0,
844 p_init_msg_list => FND_API.G_FALSE,
845 p_commit => FND_API.G_FALSE,
846 p_bus_docs_tbl => l_busdocs_tbl,
847 p_original_internal_contact_id => p_old_personid,
848 p_new_internal_contact_id => p_new_personid,
849 x_msg_data => p_msg_data,
850 x_msg_count => p_msg_count,
851 x_return_status => p_return_status);
852
853 IF (p_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
854
855 FND_MSG_PUB.Count_and_Get(p_count => p_msg_count
856 ,p_data => p_msg_data);
857
858 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
859
860 END IF;
861
862 END IF;
863
864 stmt_rel := 'SELECT por.ROWID,
865 poh.segment1,
866 por.release_num,
867 pdt.type_name,
868 por.authorization_status,
869 por.wf_item_type,
870 por.wf_item_key
871 FROM po_headers poh,
872 po_releases por,
873 po_document_types_vl pdt
874 WHERE poh.po_header_id = por.po_header_id
875 AND por.agent_id = PO_MASS_UPDATE_PO_PVT.get_old_personid
876 AND pdt.document_type_code = ''RELEASE''
877 AND pdt.document_subtype = por.release_type
878 AND Nvl(por.authorization_status,''INCOMPLETE'') IN (''APPROVED'',''REQUIRES REAPPROVAL'',''INCOMPLETE'',''REJECTED'',''IN PROCESS'',''PRE-APPROVED'')
879 AND Nvl(por.cancel_flag,''N'') = ''N''
880 AND Nvl(por.frozen_flag,''N'') = ''N''';
881
882
883 IF p_document_type IS NOT NULL AND p_document_type <> 'ALL' THEN -- <BUG 6988269>
884
885 stmt_rel := stmt_rel || ' AND ((PO_MASS_UPDATE_PO_PVT.get_document_type = ''PLANNED'' and por.release_type = ''SCHEDULED'')
886
887 OR (por.release_type = Nvl(PO_MASS_UPDATE_PO_PVT.get_document_type,por.release_type)))';
888
889 END IF;
890
891
892 IF ( po_num_type = 'NUMERIC' ) THEN
893
894 IF p_document_no_from IS NULL AND p_document_no_to IS NULL THEN
895
896 stmt_rel := stmt_rel || ' AND 1 = 1 ';
897
898 ELSIF p_document_no_from IS NOT NULL AND p_document_no_to IS NULL THEN
899
900 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)';
901
902 ELSIF p_document_no_from IS NULL AND p_document_no_to IS NOT NULL THEN
903
904 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)';
905
906 ELSE
907
908 stmt_rel := stmt_rel || ' AND DECODE ( RTRIM ( POH.SEGMENT1,''0123456789'' ), NULL, To_Number ( POH.SEGMENT1 ) , NULL )
909
910 BETWEEN to_number(PO_MASS_UPDATE_PO_PVT.get_document_no_from) AND to_number(PO_MASS_UPDATE_PO_PVT.get_document_no_to)';
911
912 END IF;
913
914 ELSE
915
916 IF p_document_no_from IS NULL AND p_document_no_to IS NULL THEN
917
918 stmt_rel := stmt_rel || ' AND 1 = 1 ';
919
920 ELSIF p_document_no_from IS NOT NULL AND p_document_no_to IS NULL THEN
921
922 stmt_rel := stmt_rel || ' AND POH.SEGMENT1 >= PO_MASS_UPDATE_PO_PVT.get_document_no_from';
923
924 ELSIF p_document_no_from IS NULL AND p_document_no_to IS NOT NULL THEN
925
926 stmt_rel := stmt_rel || ' AND POH.SEGMENT1 <= PO_MASS_UPDATE_PO_PVT.get_document_no_to';
927
928 ELSE
929
930 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';
931
932 END IF;
933
934
935 END IF; /* End of po_num_type = 'NUMERIC' */
936
937 /* Bug 6899092 Added Trunc condition in validating the date ranges */
938
939 IF p_date_from IS NULL AND p_date_to IS NULL THEN
940
941 stmt_rel := stmt_rel || ' AND 1 = 1 ';
942
943 ELSIF p_date_from IS NOT NULL AND p_date_to IS NULL THEN
944
945 stmt_rel := stmt_rel || ' AND POR.creation_date >= Trunc(PO_MASS_UPDATE_PO_PVT.get_date_from)';
946
947 ELSIF p_date_from IS NULL AND p_date_to IS NOT NULL THEN
948
949 stmt_rel := stmt_rel || ' AND POR.creation_date <= Trunc(PO_MASS_UPDATE_PO_PVT.get_date_to)';
950
951 ELSE
952
953 stmt_rel := stmt_rel || ' AND POR.creation_date >= Trunc(PO_MASS_UPDATE_PO_PVT.get_date_from)
954 AND POR.creation_date < Trunc(PO_MASS_UPDATE_PO_PVT.get_date_to)+1';
955
956 END IF;
957
958
959 IF p_supplier_id IS NOT NULL THEN
960
961 stmt_rel := stmt_rel || ' AND POH.vendor_id = PO_MASS_UPDATE_PO_PVT.get_supplier_id';
962
963 END IF;
964
965
966 IF p_include_close_po = 'YES' THEN
967
968 stmt_rel := stmt_rel || ' AND Nvl(POR.closed_code,''OPEN'') NOT IN (''FINALLY CLOSED'') '; /* 6868589 */
969
970 ELSE
971
972 stmt_rel := stmt_rel || ' AND Nvl(POR.closed_code,''OPEN'') NOT IN (''CLOSED'',''FINALLY CLOSED'') '; /* 6868589 */
973
974 END IF;
975
976 stmt_rel := stmt_rel || ' ORDER BY POH.segment1,POR.release_num';
977
978
979 IF (p_document_type IS NULL OR p_document_type IN ('BLANKET','PLANNED','ALL')) THEN -- <BUG 6988269 Added 'ALL' condition>
980
981 OPEN c_rel for stmt_rel;
982
983 LOOP
984
985 FETCH c_rel INTO l_rel_rowid,
986 l_po_num,
987 l_rel_num,
988 l_doc_type,
989 l_auth_status,
990 l_itemtype,
991 l_itemkey;
992
993 EXIT WHEN c_rel%NOTFOUND;
994
995 BEGIN
996
997 SAVEPOINT Update_Buyer_REC_REL_SP;
998
999 l_progress := '007';
1000
1001 IF g_debug_stmt THEN
1002
1003 PO_DEBUG.debug_var(l_log_head,l_progress,'l_rel_rowid',l_rel_rowid );
1004 PO_DEBUG.debug_var(l_log_head,l_progress,'l_po_num',l_po_num );
1005 PO_DEBUG.debug_var(l_log_head,l_progress,'l_rel_num',l_rel_num );
1006 PO_DEBUG.debug_var(l_log_head,l_progress,'l_doc_type',l_doc_type );
1007 PO_DEBUG.debug_var(l_log_head,l_progress,'l_auth_status',l_auth_status );
1008 PO_DEBUG.debug_var(l_log_head,l_progress,'l_itemtype',l_itemtype );
1009 PO_DEBUG.debug_var(l_log_head,l_progress,'l_itemkey',l_itemkey );
1010
1011 END IF;
1012
1013 UPDATE po_releases_all
1014 SET agent_id = p_new_personid,
1015 last_update_date = sysdate,
1016 last_updated_by = fnd_global.user_id,
1017 last_update_login = fnd_global.login_id
1018 WHERE rowid = l_rel_rowid;
1019
1020 l_progress := '008';
1021
1022 IF g_debug_stmt THEN
1023
1024 PO_DEBUG.debug_var(l_log_head,l_progress,'p_new_personid',p_new_personid );
1025
1026 END IF;
1027
1028
1029
1030 IF ( (l_auth_status='PRE-APPROVED') OR (l_auth_status='IN PROCESS') ) THEN
1031
1032
1033 l_preparer_id := po_wf_util_pkg.GetItemAttrText ( itemtype => l_itemtype,
1034 itemkey => l_itemkey,
1035 aname => 'PREPARER_ID');
1036
1037 IF (l_preparer_id = p_old_personid) THEN
1038
1039 po_wf_util_pkg.SetItemAttrText ( itemtype => l_itemtype,
1040 itemkey => l_itemkey,
1041 aname => 'PREPARER_USER_NAME' ,
1042 avalue => p_new_username);
1043
1044 po_wf_util_pkg.SetItemAttrText ( itemtype => l_itemtype,
1045 itemkey => l_itemkey,
1046 aname => 'PREPARER_DISPLAY_NAME' ,
1047 avalue => p_new_user_display_name);
1048
1049 po_wf_util_pkg.SetItemAttrText ( itemtype => l_itemtype,
1050 itemkey => l_itemkey,
1051 aname => 'PREPARER_ID' ,
1052 avalue => p_new_personid);
1053
1054 END IF;
1055
1056 l_buyer_user_id := po_wf_util_pkg.GetItemAttrText ( itemtype => l_itemtype,
1057 itemkey => l_itemkey,
1058 aname => 'BUYER_USER_ID');
1059
1060 IF (l_buyer_user_id = p_old_buyer_user_id) THEN
1061
1062 po_wf_util_pkg.SetItemAttrText ( itemtype => l_itemtype,
1063 itemkey => l_itemkey,
1064 aname => 'BUYER_USER_ID' ,
1065 avalue => p_new_buyer_user_id);
1066
1067 po_wf_util_pkg.SetItemAttrText ( itemtype => l_itemtype,
1068 itemkey => l_itemkey,
1069 aname => 'BUYER_USER_NAME' ,
1070 avalue => p_new_username);
1071
1072 END IF;
1073
1074 l_forward_from_id := po_wf_util_pkg.GetItemAttrText ( itemtype => l_itemtype,
1075 itemkey => l_itemkey,
1076 aname => 'FORWARD_FROM_ID');
1077
1078
1079 IF (l_forward_from_id = p_old_personid) THEN
1080
1081 po_wf_util_pkg.SetItemAttrText ( itemtype => l_itemtype,
1082 itemkey => l_itemkey,
1083 aname => 'FORWARD_FROM_DISP_NAME' ,
1084 avalue => p_new_user_display_name);
1085
1086 po_wf_util_pkg.SetItemAttrText ( itemtype => l_itemtype,
1087 itemkey => l_itemkey,
1088 aname => 'FORWARD_FROM_ID' ,
1089 avalue => p_new_personid);
1090
1091 po_wf_util_pkg.SetItemAttrText ( itemtype => l_itemtype,
1092 itemkey => l_itemkey,
1093 aname => 'FORWARD_FROM_USER_NAME' ,
1094 avalue => p_new_username);
1095
1096 END IF;
1097
1098 END IF; /* End of l_auth_status = 'PRE APPROVED' */
1099
1100 l_commit_count := l_commit_count + 1;
1101
1102 l_progress := '009';
1103
1104 IF g_debug_stmt THEN
1105
1106 PO_DEBUG.debug_var(l_log_head,l_progress,'l_commit_count',l_commit_count );
1107
1108 END IF;
1109
1110 IF l_commit_count = p_commit_interval THEN
1111
1112 COMMIT;
1113 l_commit_count := 0;
1114
1115 END IF;
1116
1117 fnd_file.put_line(fnd_file.output, rpad(l_po_num || '-' || l_rel_num,32) || rpad(l_doc_type,26) || l_msg15 );
1118
1119 EXCEPTION
1120
1121 WHEN OTHERS THEN
1122
1123 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1124
1125 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,l_log_head,l_progress|| SQLCODE || SUBSTR(SQLERRM,1,200));
1126
1127 END IF;
1128
1129 ROLLBACK TO Update_Buyer_REC_REL_SP;
1130
1131 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1132
1133 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1134
1135 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_log_head );
1136
1137 END IF;
1138
1139 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => p_msg_count, p_data => p_msg_data );
1140
1141 END;
1142
1143 END LOOP;
1144
1145 CLOSE c_rel;
1146
1147 END IF; /* End of p_document_type IS NULL */
1148
1149 EXCEPTION
1150
1151 WHEN OTHERS THEN
1152
1153 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1154
1155 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,l_log_head,l_progress|| SQLCODE || SUBSTR(SQLERRM,1,200));
1156
1157 END IF;
1158
1159 ROLLBACK TO PO_Mass_Update_Buyer_SP;
1160
1161 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1162
1163 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1164
1165 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_log_head );
1166
1167 END IF;
1168
1169 END Update_Buyer;
1170
1171 --------------------------------------------------------------------------------------------------
1172 -- Start of Comments
1173
1174 -- API Name : Update_Approver
1175 -- Type : Private
1176 -- Pre-reqs : None
1177 -- Function : Updates the old approver with the new approver provided and also forwards
1178 -- the notification from old approver to new approver in case of In process
1179 -- and Pre-approved PO's and releases.
1180
1181 -- Parameters :
1182
1183 -- IN : p_update_person Person needs to be updated(Buyer/Approver/Deliver_To).
1184 -- p_old_personid Id of the old person.
1185 -- p_new_personid Id of the new person.
1186 -- p_document_type Type of the document(STANDARD,BLANKET.CONTRACT,PLANNED).
1187 -- p_document_no_from Document number from.
1188 -- p_document_no_to Document number to.
1189 -- p_date_from Date from.
1190 -- p_date_to Date to.
1191 -- p_supplier_id Supplier id.
1192 -- p_include_close_po Include Close PO's or not (Value as Yes or No).
1193 -- p_commit_interval Commit interval.
1194
1195 -- OUT : p_msg_data Actual message in encoded format.
1196 -- p_msg_count Holds the number of messages in the API list.
1197 -- p_return_status Return status of the API (Includes 'S','E','U').
1198
1199 -- End of Comments
1200 --------------------------------------------------------------------------------------------------
1201
1202 PROCEDURE Update_Approver(p_update_person IN VARCHAR2,
1203 p_old_personid IN NUMBER,
1204 p_new_personid IN NUMBER,
1205 p_document_type IN VARCHAR2,
1206 p_document_no_from IN VARCHAR2,
1207 p_document_no_to IN VARCHAR2,
1208 p_date_from IN DATE,
1209 p_date_to IN DATE,
1210 p_supplier_id IN NUMBER,
1211 p_include_close_po IN VARCHAR2,
1212 p_commit_interval IN NUMBER,
1213 p_msg_data OUT NOCOPY VARCHAR2,
1214 p_msg_count OUT NOCOPY NUMBER,
1215 p_return_status OUT NOCOPY VARCHAR2) IS
1216
1217 c_po_approver g_po_approver;
1218 c_rel_approver g_rel_approver;
1219 stmt_rel VARCHAR2(4000);
1220 stmt_po VARCHAR2(4000);
1221 l_po_rowid ROWID;
1222 l_rel_rowid ROWID;
1223 l_po_num po_headers.segment1%TYPE;
1224 l_rel_num po_releases.release_num%TYPE;
1225 po_num_type VARCHAR2(100);
1226 l_commit_count NUMBER := 0;
1227 l_progress VARCHAR2(3) := '000';
1228 l_log_head CONSTANT VARCHAR2(1000) := g_log_head||'Update_Approver';
1229 l_doc_type po_document_types_all.type_name%TYPE;
1230 l_auth_status po_headers.authorization_status%TYPE;
1231 l_notification_id wf_notifications.notification_id%TYPE;
1232 l_msg16 VARCHAR2(240);
1233 l_msg17 VARCHAR2(240);
1234
1235 BEGIN
1236
1237 --package variable intialization
1238
1239 g_old_personid := p_old_personid;
1240 g_document_type := p_document_type;
1241 g_document_no_from := p_document_no_from;
1242 g_document_no_to := p_document_no_to;
1243 g_date_from := p_date_from;
1244 g_date_to := p_date_to;
1245 g_supplier_id := p_supplier_id;
1246
1247
1248 IF g_debug_stmt THEN
1249
1250 PO_DEBUG.debug_begin(l_log_head);
1251 PO_DEBUG.debug_var(l_log_head,l_progress,'p_update_person',p_update_person );
1252 PO_DEBUG.debug_var(l_log_head,l_progress,'p_old_personid', p_old_personid);
1253 PO_DEBUG.debug_var(l_log_head,l_progress,'p_new_personid', p_new_personid);
1254 PO_DEBUG.debug_var(l_log_head,l_progress,'p_document_type',p_document_type );
1255 PO_DEBUG.debug_var(l_log_head,l_progress,'p_document_no_from',p_document_no_from );
1256 PO_DEBUG.debug_var(l_log_head,l_progress,'p_date_from',p_date_from);
1257 PO_DEBUG.debug_var(l_log_head,l_progress,'p_date_to',p_date_to);
1258 PO_DEBUG.debug_var(l_log_head,l_progress,'p_supplier_id',p_supplier_id);
1259 PO_DEBUG.debug_var(l_log_head,l_progress,'p_include_close_po',p_include_close_po);
1260 PO_DEBUG.debug_var(l_log_head,l_progress,'p_commit_interval',p_commit_interval);
1261
1262 END IF;
1263
1264 SAVEPOINT PO_Mass_Update_Approver_SP;
1265
1266 l_progress := '001';
1267
1268 IF g_debug_stmt THEN
1269
1270 PO_DEBUG.debug_var(l_log_head,l_progress,'Before Calling Buyer_Info','Before Calling Buyer_Info' );
1271
1272 END IF;
1273
1274 BEGIN
1275
1276 SAVEPOINT Update_Approver_REC_SP;
1277
1278 Buyer_Info(p_old_personid,
1279 p_new_personid,
1280 p_supplier_id,
1281 p_old_buyer_name,
1282 p_new_buyer_name,
1283 p_old_username,
1284 p_new_username,
1285 p_new_user_display_name,
1286 p_old_buyer_user_id,
1287 p_new_buyer_user_id,
1288 p_supplier_name,
1289 p_org_name,
1290 p_msg_data,
1291 p_msg_count,
1292 p_return_status);
1293
1294 IF p_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1295 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1296 RETURN;
1297 END IF;
1298
1299
1300 g_old_username := p_old_username;
1301
1302 l_progress := '002';
1303
1304 IF g_debug_stmt THEN
1305
1306 PO_DEBUG.debug_var(l_log_head,l_progress,'Before Calling Print_Output','Before Calling Print_Output' );
1307
1308 END IF;
1309
1310 IF (p_update_person = 'APPROVER' ) THEN
1311
1312 Print_Output(p_update_person,
1313 p_old_buyer_name,
1314 p_new_buyer_name,
1315 p_org_name,
1316 p_document_type,
1317 p_document_no_from,
1318 p_document_no_to,
1319 p_date_from,
1320 p_date_to,
1321 p_supplier_name,
1322 p_msg_data,
1323 p_msg_count,
1324 p_return_status);
1325
1326 END IF;
1327
1328 SELECT manual_po_num_type
1329 INTO po_num_type
1330 FROM po_system_parameters;
1331
1332 fnd_message.set_name('PO','PO_MUB_MSG_NEW_APPROVER');
1333 l_msg16 := fnd_message.get;
1334
1335 fnd_message.set_name('PO','PO_MUB_MSG_APPROVER');
1336 l_msg17 := fnd_message.get;
1337
1338 l_progress := '003';
1339
1340 IF g_debug_stmt THEN
1341
1342 PO_DEBUG.debug_var(l_log_head,l_progress,'po_num_type',po_num_type );
1343
1344 END IF;
1345
1346 EXCEPTION
1347
1348 WHEN OTHERS THEN
1349
1350 ROLLBACK TO Update_Approver_REC_SP;
1351
1352 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1353
1354 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,l_log_head,l_progress|| SQLCODE || SUBSTR(SQLERRM,1,200));
1355
1356 END IF;
1357
1358 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1359
1360 FND_MSG_PUB.Count_And_Get(
1361 p_count => p_msg_count,
1362 p_data => p_msg_data);
1363
1364 END;
1365
1366
1367 stmt_po := 'SELECT wfn.notification_id,
1368 poh.segment1,
1369 pdt.type_name
1370 FROM wf_notifications wfn,
1371 wf_item_activity_statuses wfa,
1372 po_headers poh,
1373 po_document_types_vl pdt
1374 WHERE wfn.notification_id = wfa.notification_id
1375 AND wfa.item_type = poh.wf_item_type
1376 AND wfa.item_key = poh.wf_item_key
1377 AND wfn.status NOT IN (''CLOSED'',''CANCELED'')
1378 AND Nvl(poh.authorization_status,''INCOMPLETE'') IN (''IN PROCESS'',''PRE-APPROVED'')
1379 AND wfn.recipient_role = PO_Mass_Update_PO_PVT.get_old_username
1380 AND pdt.document_type_code in (''PO'',''PA'')
1381 AND pdt.document_subtype = poh.type_lookup_code';
1382
1383
1384 IF p_document_type IS NOT NULL AND p_document_type <> 'ALL' THEN -- <BUG 6988269>
1385
1386 stmt_po := stmt_po || ' AND poh.type_lookup_code = PO_MASS_UPDATE_PO_PVT.get_document_type';
1387
1388 END IF;
1389
1390
1391 IF ( po_num_type = 'NUMERIC' ) THEN
1392
1393 IF p_document_no_from IS NULL AND p_document_no_to IS NULL THEN
1394
1395 stmt_po := stmt_po || ' AND 1 = 1 ';
1396
1397 ELSIF p_document_no_from IS NOT NULL AND p_document_no_to IS NULL THEN
1398
1399 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)';
1400
1401 ELSIF p_document_no_from IS NULL AND p_document_no_to IS NOT NULL THEN
1402
1403 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)';
1404
1405 ELSE
1406
1407 stmt_po := stmt_po || ' AND DECODE ( RTRIM ( POH.SEGMENT1,''0123456789'' ), NULL, To_Number ( POH.SEGMENT1 ) , NULL )
1408
1409 BETWEEN to_number(PO_MASS_UPDATE_PO_PVT.get_document_no_from) AND to_number(PO_MASS_UPDATE_PO_PVT.get_document_no_to)';
1410
1411 END IF;
1412
1413 ELSE
1414
1415 IF p_document_no_from IS NULL AND p_document_no_to IS NULL THEN
1416
1417 stmt_po := stmt_po || ' AND 1 = 1 ';
1418
1419 ELSIF p_document_no_from IS NOT NULL AND p_document_no_to IS NULL THEN
1420
1421 stmt_po := stmt_po || ' AND POH.SEGMENT1 >= PO_MASS_UPDATE_PO_PVT.get_document_no_from';
1422
1423 ELSIF p_document_no_from IS NULL AND p_document_no_to IS NOT NULL THEN
1424
1425 stmt_po := stmt_po || ' AND POH.SEGMENT1 <= PO_MASS_UPDATE_PO_PVT.get_document_no_to';
1426
1427 ELSE
1428
1429 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';
1430
1431 END IF;
1432
1433 END IF; /* End of po_num_type = 'NUMERIC' */
1434
1435 /* Bug 6899092 Added Trunc condition in validating the date ranges */
1436
1437 IF p_date_from IS NULL AND p_date_to IS NULL THEN
1438
1439 stmt_po := stmt_po || ' AND 1 = 1 ';
1440
1441 ELSIF p_date_from IS NOT NULL AND p_date_to IS NULL THEN
1442
1443 stmt_po := stmt_po || ' AND POH.creation_date >= Trunc(PO_MASS_UPDATE_PO_PVT.get_date_from)';
1444
1445 ELSIF p_date_from IS NULL AND p_date_to IS NOT NULL THEN
1446
1447 stmt_po := stmt_po || ' AND POH.creation_date <= Trunc(PO_MASS_UPDATE_PO_PVT.get_date_to)';
1448
1449 ELSE
1450
1451 stmt_po := stmt_po || ' AND POH.creation_date >= Trunc(PO_MASS_UPDATE_PO_PVT.get_date_from)
1452 AND POH.creation_date < Trunc(PO_MASS_UPDATE_PO_PVT.get_date_to)+1';
1453
1454 END IF;
1455
1456
1457 IF p_supplier_id IS NOT NULL THEN
1458
1459 stmt_po := stmt_po || ' AND POH.vendor_id = PO_MASS_UPDATE_PO_PVT.get_supplier_id';
1460
1461 END IF;
1462
1463 IF p_include_close_po = 'YES' THEN
1464
1465 stmt_po := stmt_po || ' AND Nvl(POH.closed_code,''OPEN'') NOT IN (''FINALLY CLOSED'') '; /* 6868589 */
1466
1467 ELSE
1468
1469 stmt_po := stmt_po || ' AND Nvl(POH.closed_code,''OPEN'') NOT IN (''CLOSED'',''FINALLY CLOSED'') '; /* 6868589 */
1470
1471 END IF;
1472
1473 stmt_po := stmt_po || ' ORDER BY POH.segment1';
1474
1475 IF (p_document_type IS NULL OR p_document_type IN ('STANDARD','BLANKET','PLANNED','CONTRACT','ALL')) THEN -- <BUG 6988269>
1476
1477 OPEN c_po_approver for stmt_po;
1478 LOOP
1479
1480 FETCH c_po_approver INTO l_notification_id,
1481 l_po_num,
1482 l_doc_type;
1483
1484 EXIT WHEN c_po_approver%NOTFOUND;
1485
1486 l_progress := '004';
1487
1488 IF g_debug_stmt THEN
1489
1490 PO_DEBUG.debug_var(l_log_head,l_progress,'l_notification_id',l_notification_id );
1491 PO_DEBUG.debug_var(l_log_head,l_progress,'l_po_num',l_po_num );
1492 PO_DEBUG.debug_var(l_log_head,l_progress,'l_doc_type', l_doc_type);
1493
1494 END IF;
1495
1496
1497 BEGIN
1498
1499 SAVEPOINT Mass_Update_Forward_SP;
1500
1501 l_progress := '005';
1502
1503 Wf_Notification.Forward(l_notification_id, p_new_username,l_msg16);
1504
1505 IF g_debug_stmt THEN
1506
1507 PO_DEBUG.debug_var(l_log_head,l_progress,'p_new_username',p_new_username );
1508
1509 END IF;
1510
1511 l_commit_count := l_commit_count + 1;
1512
1513 IF l_commit_count = p_commit_interval THEN
1514
1515 COMMIT;
1516 l_commit_count := 0;
1517
1518 END IF;
1519
1520 l_progress := '006';
1521
1522 IF g_debug_stmt THEN
1523
1524 PO_DEBUG.debug_var(l_log_head,l_progress,'l_commit_count',l_commit_count );
1525
1526 END IF;
1527
1528
1529
1530 fnd_file.put_line(fnd_file.output, rpad(l_po_num,26) || rpad(l_doc_type,32) || l_msg17 );
1531
1532 EXCEPTION
1533
1534 WHEN OTHERS THEN
1535
1536 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1537
1538 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,l_log_head,l_progress|| SQLCODE || SUBSTR(SQLERRM,1,200));
1539
1540 END IF;
1541
1542 ROLLBACK TO Mass_Update_Forward_SP;
1543
1544 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1545
1546 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1547
1548 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_log_head );
1549
1550 END IF;
1551
1552 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => p_msg_count, p_data => p_msg_data );
1553
1554
1555 END;
1556
1557 END LOOP;
1558
1559 CLOSE c_po_approver;
1560
1561 END IF; -- < End of p_document_type >
1562
1563
1564 IF (p_document_type IS NULL OR p_document_type IN ('BLANKET','PLANNED','ALL') ) THEN -- <BUG 6988269 Added 'ALL' condition>
1565
1566
1567 stmt_rel := 'SELECT wfn.notification_id,
1568 poh.segment1,
1569 por.release_num,
1570 pdt.type_name
1571 FROM wf_notifications wfn,
1572 wf_item_activity_statuses wfa,
1573 po_headers poh,
1574 po_releases por,
1575 po_document_types_vl pdt
1576 WHERE wfn.notification_id = wfa.notification_id
1577 AND wfa.item_type = por.wf_item_type
1578 AND wfa.item_key = por.wf_item_key
1579 AND wfn.status NOT IN (''CLOSED'',''CANCELED'')
1580 AND Nvl(por.authorization_status,''INCOMPLETE'') IN (''IN PROCESS'',''PRE-APPROVED'')
1581 AND recipient_role = PO_Mass_Update_PO_PVT.get_old_username
1582 AND por.po_header_id = poh.po_header_id
1583 AND pdt.document_type_code = ''RELEASE''
1584 AND pdt.document_subtype = por.release_type';
1585
1586
1587 IF p_document_type IS NOT NULL AND p_document_type <> 'ALL' THEN -- <BUG 6988269>
1588
1589 stmt_rel := stmt_rel || ' AND ((PO_MASS_UPDATE_PO_PVT.get_document_type = ''PLANNED'' and por.release_type = ''SCHEDULED'')
1590
1591 OR (por.release_type = Nvl(PO_MASS_UPDATE_PO_PVT.get_document_type,por.release_type)))';
1592
1593 END IF;
1594
1595 IF ( po_num_type = 'NUMERIC' ) THEN
1596
1597 IF p_document_no_from IS NULL AND p_document_no_to IS NULL THEN
1598
1599 stmt_rel := stmt_rel || ' AND 1 = 1 ';
1600
1601 ELSIF p_document_no_from IS NOT NULL AND p_document_no_to IS NULL THEN
1602
1603 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)';
1604
1605 ELSIF p_document_no_from IS NULL AND p_document_no_to IS NOT NULL THEN
1606
1607 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)';
1608
1609 ELSE
1610
1611 stmt_rel := stmt_rel || ' AND DECODE ( RTRIM ( POH.SEGMENT1,''0123456789'' ), NULL, To_Number ( POH.SEGMENT1 ) , NULL )
1612
1613 BETWEEN to_number(PO_MASS_UPDATE_PO_PVT.get_document_no_from) AND to_number(PO_MASS_UPDATE_PO_PVT.get_document_no_to)';
1614
1615 END IF;
1616
1617 ELSE
1618
1619 IF p_document_no_from IS NULL AND p_document_no_to IS NULL THEN
1620
1621 stmt_rel := stmt_rel || ' AND 1 = 1 ';
1622
1623 ELSIF p_document_no_from IS NOT NULL AND p_document_no_to IS NULL THEN
1624
1625 stmt_rel := stmt_rel || ' AND POH.SEGMENT1 >= PO_MASS_UPDATE_PO_PVT.get_document_no_from';
1626
1627 ELSIF p_document_no_from IS NULL AND p_document_no_to IS NOT NULL THEN
1628
1629 stmt_rel := stmt_rel || ' AND POH.SEGMENT1 <= PO_MASS_UPDATE_PO_PVT.get_document_no_to';
1630
1631 ELSE
1632
1633 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';
1634
1635 END IF;
1636
1637 END IF; /* End of po_num_type = 'NUMERIC' */
1638
1639 /* Bug 6899092 Added Trunc condition in validating the date ranges */
1640
1641 IF p_date_from IS NULL AND p_date_to IS NULL THEN
1642
1643 stmt_rel := stmt_rel || ' AND 1 = 1 ';
1644
1645 ELSIF p_date_from IS NOT NULL AND p_date_to IS NULL THEN
1646
1647 stmt_rel := stmt_rel || ' AND POR.creation_date >= Trunc(PO_MASS_UPDATE_PO_PVT.get_date_from)';
1648
1649 ELSIF p_date_from IS NULL AND p_date_to IS NOT NULL THEN
1650
1651 stmt_rel := stmt_rel || ' AND POR.creation_date <= Trunc(PO_MASS_UPDATE_PO_PVT.get_date_to)';
1652
1653 ELSE
1654
1655 stmt_rel := stmt_rel || ' AND POR.creation_date >= Trunc(PO_MASS_UPDATE_PO_PVT.get_date_from)
1656 AND POR.creation_date < Trunc(PO_MASS_UPDATE_PO_PVT.get_date_to)+1';
1657
1658 END IF;
1659
1660 IF p_supplier_id IS NOT NULL THEN
1661
1662 stmt_rel := stmt_rel || ' AND POH.vendor_id = PO_MASS_UPDATE_PO_PVT.get_supplier_id';
1663
1664 END IF;
1665
1666 IF p_include_close_po = 'YES' THEN
1667
1668 stmt_rel := stmt_rel || ' AND Nvl(POR.closed_code,''OPEN'') NOT IN (''FINALLY CLOSED'') '; /* Bug 6868589 */
1669
1670 ELSE
1671
1672 stmt_rel := stmt_rel || ' AND Nvl(POR.closed_code,''OPEN'') NOT IN (''CLOSED'',''FINALLY CLOSED'') '; /* Bug 6868589 */
1673
1674 END IF;
1675
1676 stmt_rel := stmt_rel || ' ORDER BY poh.segment1, por.release_num';
1677
1678 OPEN c_rel_approver for stmt_rel;
1679
1680 LOOP
1681
1682 FETCH c_rel_approver INTO l_notification_id,
1683 l_po_num,
1684 l_rel_num,
1685 l_doc_type;
1686
1687 EXIT WHEN c_rel_approver%NOTFOUND;
1688
1689 l_progress := '007';
1690
1691 IF g_debug_stmt THEN
1692
1693 PO_DEBUG.debug_var(l_log_head,l_progress,'l_notification_id',l_notification_id );
1694 PO_DEBUG.debug_var(l_log_head,l_progress,'l_po_num',l_po_num );
1695 PO_DEBUG.debug_var(l_log_head,l_progress,'l_rel_num',l_rel_num );
1696 PO_DEBUG.debug_var(l_log_head,l_progress,'l_doc_type', l_doc_type);
1697
1698 END IF;
1699
1700 BEGIN
1701
1702 SAVEPOINT Mass_Update_Forward_SP;
1703
1704 Wf_Notification.Forward(l_notification_id, p_new_username,l_msg16);
1705
1706 l_progress := '008';
1707
1708 IF g_debug_stmt THEN
1709
1710 PO_DEBUG.debug_var(l_log_head,l_progress,'p_new_username',p_new_username );
1711
1712 END IF;
1713
1714 l_commit_count := l_commit_count + 1;
1715
1716 IF l_commit_count = p_commit_interval THEN
1717
1718 COMMIT;
1719 l_commit_count := 0;
1720
1721 END IF;
1722
1723 l_progress := '009';
1724
1725 IF g_debug_stmt THEN
1726
1727 PO_DEBUG.debug_var(l_log_head,l_progress,'l_commit_count',l_commit_count );
1728
1729 END IF;
1730
1731
1732 fnd_file.put_line(fnd_file.output, rpad(l_po_num || '-' || l_rel_num,32) || rpad(l_doc_type,26) || l_msg17);
1733
1734 EXCEPTION
1735
1736 WHEN OTHERS THEN
1737
1738 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1739
1740 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,l_log_head,l_progress|| SQLCODE || SUBSTR(SQLERRM,1,200));
1741
1742 END IF;
1743
1744 ROLLBACK TO Mass_Update_Forward_SP;
1745
1746 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1747
1748 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1749
1750 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_log_head );
1751
1752 END IF;
1753
1754 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => p_msg_count, p_data => p_msg_data );
1755
1756 END;
1757
1758 END LOOP;
1759
1760 CLOSE c_rel_approver;
1761
1762 END IF; /* End of p_document_type IS NULL */
1763
1764 EXCEPTION
1765
1766 WHEN OTHERS THEN
1767
1768 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1769
1770 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,l_log_head,l_progress|| SQLCODE || SUBSTR(SQLERRM,1,200));
1771
1772 END IF;
1773
1774 ROLLBACK TO PO_Mass_Update_Approver_SP;
1775
1776 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1777
1778 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1779
1780 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_log_head );
1781
1782 END IF;
1783
1784 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => p_msg_count, p_data => p_msg_data );
1785
1786 END Update_Approver;
1787
1788 --------------------------------------------------------------------------------------------------
1789 -- Start of Comments
1790
1791 -- API Name : Update_Deliver_To
1792 -- Type : Private
1793 -- Pre-reqs : None
1794 -- Function : Updates the old deliver to person with the new deliver to person provided.
1795
1796 -- Parameters :
1797
1798 -- IN : p_update_person Person needs to be updated(Buyer/Approver/Deliver_To).
1799 -- p_old_personid Id of the old person.
1800 -- p_new_personid Id of the new person.
1801 -- p_document_type Type of the document(STANDARD,BLANKET.CONTRACT,PLANNED).
1802 -- p_document_no_from Document number from.
1803 -- p_document_no_to Document number to.
1804 -- p_date_from Date from.
1805 -- p_date_to Date to.
1806 -- p_supplier_id Supplier id.
1807 -- p_include_close_po Include Close PO's or not (Value as Yes or No).
1808 -- p_commit_interval Commit interval.
1809
1810 -- OUT : p_msg_data Actual message in encoded format.
1811 -- p_msg_count Holds the number of messages in the API list.
1812 -- p_return_status Return status of the API (Includes 'S','E','U').
1813
1814 -- End of Comments
1815 --------------------------------------------------------------------------------------------------
1816
1817 PROCEDURE Update_Deliver_To(p_update_person IN VARCHAR2,
1818 p_old_personid IN NUMBER,
1819 p_new_personid IN NUMBER,
1820 p_document_type IN VARCHAR2,
1821 p_document_no_from IN VARCHAR2,
1822 p_document_no_to IN VARCHAR2,
1823 p_date_from IN DATE,
1824 p_date_to IN DATE,
1825 p_supplier_id IN NUMBER,
1826 p_include_close_po IN VARCHAR2,
1827 p_commit_interval IN NUMBER,
1828 p_msg_data OUT NOCOPY VARCHAR2,
1829 p_msg_count OUT NOCOPY NUMBER,
1830 p_return_status OUT NOCOPY VARCHAR2) IS
1831
1832 c_po g_po;
1833 c_rel g_rel;
1834 stmt_rel VARCHAR2(4000);
1835 stmt_po VARCHAR2(4000);
1836 po_num_type VARCHAR2(100);
1837 l_po_rowid ROWID;
1838 l_rel_rowid ROWID;
1839 l_po_num po_headers.segment1%TYPE;
1840 l_rel_num po_releases.release_num%TYPE;
1841 l_commit_count NUMBER := 0;
1842 l_progress VARCHAR2(3) := '000';
1843 l_log_head CONSTANT VARCHAR2(1000) := g_log_head||'Update_Deliver_To';
1844 l_doc_type po_document_types_all.type_name%TYPE;
1845 l_auth_status po_headers.authorization_status%TYPE;
1846 l_msg18 VARCHAR2(240);
1847 l_release_id po_releases.po_release_id%TYPE;
1848
1849 BEGIN
1850
1851 -- Intializing package variables
1852
1853 g_old_personid := p_old_personid;
1854 g_document_type := p_document_type;
1855 g_document_no_from := p_document_no_from;
1856 g_document_no_to := p_document_no_to;
1857 g_date_from := p_date_from;
1858 g_date_to := p_date_to;
1859 g_supplier_id := p_supplier_id;
1860
1861
1862 IF g_debug_stmt THEN
1863
1864 PO_DEBUG.debug_begin(l_log_head);
1865 PO_DEBUG.debug_var(l_log_head,l_progress,'p_update_person',p_update_person );
1866 PO_DEBUG.debug_var(l_log_head,l_progress,'p_old_personid', p_old_personid);
1867 PO_DEBUG.debug_var(l_log_head,l_progress,'p_new_personid', p_new_personid);
1868 PO_DEBUG.debug_var(l_log_head,l_progress,'p_document_type',p_document_type );
1869 PO_DEBUG.debug_var(l_log_head,l_progress,'p_document_no_from',p_document_no_from );
1870 PO_DEBUG.debug_var(l_log_head,l_progress,'p_date_from',p_date_from);
1871 PO_DEBUG.debug_var(l_log_head,l_progress,'p_date_to',p_date_to);
1872 PO_DEBUG.debug_var(l_log_head,l_progress,'p_supplier_id',p_supplier_id);
1873 PO_DEBUG.debug_var(l_log_head,l_progress,'p_include_close_po',p_include_close_po);
1874 PO_DEBUG.debug_var(l_log_head,l_progress,'p_commit_interval',p_commit_interval);
1875
1876 END IF;
1877
1878 SAVEPOINT PO_Mass_Update_DeliverTo_SP;
1879
1880 l_progress := '001';
1881
1882 IF g_debug_stmt THEN
1883
1884 PO_DEBUG.debug_var(l_log_head,l_progress,'Before Calling Buyer_Info','Before Calling Buyer_Info' );
1885
1886 END IF;
1887
1888 BEGIN
1889
1890 SAVEPOINT Update_DeliverTo_REC_SP;
1891
1892 Buyer_Info(p_old_personid,
1893 p_new_personid,
1894 p_supplier_id,
1895 p_old_buyer_name,
1896 p_new_buyer_name,
1897 p_old_username,
1898 p_new_username,
1899 p_new_user_display_name,
1900 p_old_buyer_user_id,
1901 p_new_buyer_user_id,
1902 p_supplier_name,
1903 p_org_name,
1904 p_msg_data,
1905 p_msg_count,
1906 p_return_status);
1907
1908 IF p_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1909 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1910 RETURN;
1911 END IF;
1912
1913
1914 l_progress := '002';
1915
1916 IF g_debug_stmt THEN
1917
1918 PO_DEBUG.debug_var(l_log_head,l_progress,'Before Calling Print_Output','Before Calling Print_Output' );
1919
1920 END IF;
1921
1922 IF (p_update_person = 'DELIVER TO' ) THEN
1923
1924 Print_Output(p_update_person,
1925 p_old_buyer_name,
1926 p_new_buyer_name,
1927 p_org_name,
1928 p_document_type,
1929 p_document_no_from,
1930 p_document_no_to,
1931 p_date_from,
1932 p_date_to,
1933 p_supplier_name,
1934 p_msg_data,
1935 p_msg_count,
1936 p_return_status);
1937
1938 END IF;
1939
1940 SELECT manual_po_num_type
1941 INTO po_num_type
1942 FROM po_system_parameters;
1943
1944 fnd_message.set_name('PO','PO_MUB_MSG_DELIVER_TO');
1945 l_msg18 := fnd_message.get;
1946
1947 l_progress := '003';
1948
1949 IF g_debug_stmt THEN
1950
1951 PO_DEBUG.debug_var(l_log_head,l_progress,'po_num_type',po_num_type );
1952
1953 END IF;
1954
1955 EXCEPTION
1956
1957 WHEN OTHERS THEN
1958
1959 ROLLBACK TO Update_DeliverTo_REC_SP;
1960
1961 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1962
1963 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,l_log_head,l_progress|| SQLCODE || SUBSTR(SQLERRM,1,200));
1964
1965 END IF;
1966
1967 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1968
1969 FND_MSG_PUB.Count_And_Get(
1970 p_count => p_msg_count,
1971 p_data => p_msg_data);
1972
1973 END;
1974
1975
1976 stmt_po := 'SELECT pod.ROWID,
1977 poh.segment1,
1978 pdt.type_name,
1979 pod.po_release_id
1980 FROM po_headers poh,
1981 po_document_types_vl pdt,
1982 po_distributions pod
1983 WHERE pod.deliver_to_person_id = PO_MASS_UPDATE_PO_PVT.get_old_personid
1984 AND poh.po_header_id = pod.po_header_id
1985 AND pdt.document_type_code IN (''PO'',''PA'')
1986 AND pdt.document_subtype = poh.type_lookup_code
1987 AND Nvl(poh.authorization_status,''INCOMPLETE'') IN (''APPROVED'',''REQUIRES REAPPROVAL'',''INCOMPLETE'',''REJECTED'',''IN PROCESS'',''PRE-APPROVED'')
1988 AND Nvl(poh.cancel_flag,''N'') = ''N''
1989 AND Nvl(poh.frozen_flag,''N'') = ''N''';
1990
1991 IF p_document_type IS NOT NULL AND p_document_type <> 'ALL' THEN -- <BUG 6988269>
1992
1993 stmt_po := stmt_po || 'AND poh.type_lookup_code = PO_MASS_UPDATE_PO_PVT.get_document_type';
1994
1995 END IF;
1996
1997 IF ( po_num_type = 'NUMERIC' ) THEN
1998
1999 IF p_document_no_from IS NULL AND p_document_no_to IS NULL THEN
2000
2001 stmt_po := stmt_po || ' AND 1 = 1 ';
2002
2003 ELSIF p_document_no_from IS NOT NULL AND p_document_no_to IS NULL THEN
2004
2005 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)';
2006
2007 ELSIF p_document_no_from IS NULL AND p_document_no_to IS NOT NULL THEN
2008
2009 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)';
2010
2011 ELSE
2012
2013 stmt_po := stmt_po || ' AND DECODE ( RTRIM ( POH.SEGMENT1,''0123456789'' ), NULL, To_Number ( POH.SEGMENT1 ) , NULL )
2014
2015 BETWEEN to_number(PO_MASS_UPDATE_PO_PVT.get_document_no_from) AND to_number(PO_MASS_UPDATE_PO_PVT.get_document_no_to)';
2016
2017 END IF;
2018
2019 ELSE
2020
2021 IF p_document_no_from IS NULL AND p_document_no_to IS NULL THEN
2022
2023 stmt_po := stmt_po || ' AND 1 = 1 ';
2024
2025 ELSIF p_document_no_from IS NOT NULL AND p_document_no_to IS NULL THEN
2026
2027 stmt_po := stmt_po || ' AND POH.SEGMENT1 >= PO_MASS_UPDATE_PO_PVT.get_document_no_from';
2028
2029 ELSIF p_document_no_from IS NULL AND p_document_no_to IS NOT NULL THEN
2030
2031 stmt_po := stmt_po || ' AND POH.SEGMENT1 <= PO_MASS_UPDATE_PO_PVT.get_document_no_to';
2032
2033 ELSE
2034
2035 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';
2036
2037 END IF;
2038
2039 END IF; /* End of po_num_type = 'NUMERIC' */
2040
2041 /* Bug 6899092 Added Trunc condition in validating the date ranges */
2042
2043 IF p_date_from IS NULL AND p_date_to IS NULL THEN
2044
2045 stmt_po := stmt_po || ' AND 1 = 1 ';
2046
2047 ELSIF p_date_from IS NOT NULL AND p_date_to IS NULL THEN
2048
2049 stmt_po := stmt_po || ' AND POH.creation_date >= Trunc(PO_MASS_UPDATE_PO_PVT.get_date_from)';
2050
2051 ELSIF p_date_from IS NULL AND p_date_to IS NOT NULL THEN
2052
2053 stmt_po := stmt_po || ' AND POH.creation_date <= Trunc(PO_MASS_UPDATE_PO_PVT.get_date_to)';
2054
2055 ELSE
2056 stmt_po := stmt_po || ' AND POH.creation_date >= Trunc(PO_MASS_UPDATE_PO_PVT.get_date_from)
2057 AND POH.creation_date < Trunc(PO_MASS_UPDATE_PO_PVT.get_date_to)+1';
2058
2059 END IF;
2060
2061 IF p_supplier_id IS NOT NULL THEN
2062
2063 stmt_po := stmt_po || ' AND POH.vendor_id = PO_MASS_UPDATE_PO_PVT.get_supplier_id';
2064
2065 END IF;
2066
2067 IF p_include_close_po = 'YES' THEN
2068
2069 stmt_po := stmt_po || ' AND Nvl(POH.closed_code,''OPEN'') NOT IN (''FINALLY CLOSED'') '; /* Bug 6868589 */
2070
2071 ELSE
2072
2073 stmt_po := stmt_po || ' AND Nvl(POH.closed_code,''OPEN'') NOT IN (''CLOSED'',''FINALLY CLOSED'') '; /* Bug 6868589 */
2074
2075 END IF;
2076
2077 stmt_po := stmt_po || ' ORDER BY POH.segment1';
2078
2079 IF (p_document_type IS NULL OR p_document_type IN ('STANDARD','BLANKET','PLANNED','CONTRACT','ALL')) THEN -- <BUG 6988269>
2080
2081 OPEN c_po for stmt_po;
2082
2083 LOOP
2084
2085 FETCH c_po INTO l_po_rowid,
2086 l_po_num,
2087 l_doc_type,
2088 l_release_id;
2089
2090 EXIT when c_po%NOTFOUND;
2091
2092 BEGIN
2093
2094 SAVEPOINT Update_DeliverTo_RECPO_SP;
2095
2096 l_progress := '004';
2097
2098 IF g_debug_stmt THEN
2099
2100 PO_DEBUG.debug_var(l_log_head,l_progress,'l_po_rowid',l_po_rowid );
2101 PO_DEBUG.debug_var(l_log_head,l_progress,'l_po_num',l_po_num );
2102 PO_DEBUG.debug_var(l_log_head,l_progress,'l_doc_type',l_doc_type );
2103
2104 END IF;
2105
2106
2107
2108 UPDATE po_distributions_all
2109 SET deliver_to_person_id = p_new_personid,
2110 last_update_date = sysdate,
2111 last_updated_by = fnd_global.user_id,
2112 last_update_login = fnd_global.login_id
2113 WHERE rowid = l_po_rowid
2114 AND po_release_id IS NULL;
2115
2116
2117 l_progress := '005';
2118
2119 IF g_debug_stmt THEN
2120
2121 PO_DEBUG.debug_var(l_log_head,l_progress,'p_new_personid',p_new_personid );
2122
2123 END IF;
2124
2125
2126 l_commit_count := l_commit_count + 1;
2127
2128 IF l_commit_count = p_commit_interval THEN
2129
2130 COMMIT;
2131 l_commit_count := 0;
2132
2133 END IF;
2134
2135 l_progress := '006';
2136
2137 IF g_debug_stmt THEN
2138
2139 PO_DEBUG.debug_var(l_log_head,l_progress,'l_commit_count',l_commit_count );
2140
2141 END IF;
2142
2143 IF l_release_id IS NULL THEN
2144
2145 fnd_file.put_line(fnd_file.output, rpad(l_po_num,26) || rpad(l_doc_type,32) || l_msg18 );
2146
2147 END IF;
2148
2149 EXCEPTION
2150
2151 WHEN OTHERS THEN
2152
2153 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2154
2155 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,l_log_head,l_progress||SQLCODE||SUBSTR(SQLERRM,1,200));
2156
2157 END IF;
2158
2159 ROLLBACK TO Update_DeliverTo_RECPO_SP;
2160
2161 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2162
2163 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2164
2165 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_log_head );
2166
2167 END IF;
2168
2169 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => p_msg_count, p_data => p_msg_data );
2170
2171 END;
2172
2173 END LOOP;
2174
2175 CLOSE c_po;
2176
2177 END IF; -- <End of p_document_type>
2178
2179 stmt_rel := 'SELECT pod.ROWID,
2180 poh.segment1,
2181 por.release_num,
2182 pdt.type_name
2183 FROM po_releases por,
2184 po_headers poh,
2185 po_document_types_vl pdt,
2186 po_distributions_all pod
2187 WHERE por.po_header_id = poh.po_header_id
2188 AND poh.po_header_id = pod.po_header_id
2189 AND pod.po_release_id = por.po_release_id /* Bug 6868589 */
2190 AND pod.deliver_to_person_id = PO_MASS_UPDATE_PO_PVT.get_old_personid
2191 AND pdt.document_type_code =''RELEASE''
2192 AND pdt.document_subtype = por.release_type
2193 AND Nvl(por.authorization_status,''INCOMPLETE'') IN (''APPROVED'',''REQUIRES REAPPROVAL'',''INCOMPLETE'',''REJECTED'',''IN PROCESS'',''PRE-APPROVED'')
2194 AND Nvl(por.cancel_flag,''N'') = ''N''
2195 AND Nvl(por.frozen_flag,''N'') = ''N''';
2196
2197 IF p_document_type IS NOT NULL AND p_document_type <> 'ALL' THEN -- <BUG 6988269>
2198
2199 stmt_rel := stmt_rel || ' AND ((PO_MASS_UPDATE_PO_PVT.get_document_type = ''PLANNED'' and por.release_type = ''SCHEDULED'')
2200
2201 OR (por.release_type = Nvl(PO_MASS_UPDATE_PO_PVT.get_document_type,por.release_type)))';
2202
2203 END IF;
2204
2205
2206 IF ( po_num_type = 'NUMERIC' ) THEN
2207
2208 IF p_document_no_from IS NULL AND p_document_no_to IS NULL THEN
2209
2210 stmt_rel := stmt_rel || ' AND 1 = 1 ';
2211
2212 ELSIF p_document_no_from IS NOT NULL AND p_document_no_to IS NULL THEN
2213
2214 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)';
2215
2216 ELSIF p_document_no_from IS NULL AND p_document_no_to IS NOT NULL THEN
2217
2218 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 )';
2219
2220 ELSE
2221
2222 stmt_rel := stmt_rel || ' AND DECODE ( RTRIM ( POH.SEGMENT1,''0123456789'' ), NULL, To_Number ( POH.SEGMENT1 ) , NULL )
2223
2224 BETWEEN to_number(PO_MASS_UPDATE_PO_PVT.get_document_no_from) AND to_number(PO_MASS_UPDATE_PO_PVT.get_document_no_to)';
2225
2226 END IF;
2227
2228 ELSE
2229
2230 IF p_document_no_from IS NULL AND p_document_no_to IS NULL THEN
2231
2232 stmt_rel := stmt_rel || ' AND 1 = 1 ';
2233
2234 ELSIF p_document_no_from IS NOT NULL AND p_document_no_to IS NULL THEN
2235
2236 stmt_rel := stmt_rel || ' AND POH.SEGMENT1 >= PO_MASS_UPDATE_PO_PVT.get_document_no_from';
2237
2238 ELSIF p_document_no_from IS NULL AND p_document_no_to IS NOT NULL THEN
2239
2240 stmt_rel := stmt_rel || ' AND POH.SEGMENT1 <= PO_MASS_UPDATE_PO_PVT.get_document_no_to';
2241
2242 ELSE
2243
2244 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';
2245
2246 END IF;
2247
2248 END IF; /* End of po_num_type = 'NUMERIC' */
2249
2250 /* Bug 6899092 Added Trunc condition in validating the date ranges */
2251
2252 IF p_date_from IS NULL AND p_date_to IS NULL THEN
2253
2254 stmt_rel := stmt_rel || ' AND 1 = 1 ';
2255
2256 ELSIF p_date_from IS NOT NULL AND p_date_to IS NULL THEN
2257
2258 stmt_rel := stmt_rel || ' AND POR.creation_date >= Trunc(PO_MASS_UPDATE_PO_PVT.get_date_from)';
2259
2260 ELSIF p_date_from IS NULL AND p_date_to IS NOT NULL THEN
2261
2262 stmt_rel := stmt_rel || ' AND POR.creation_date <= Trunc(PO_MASS_UPDATE_PO_PVT.get_date_to)';
2263
2264 ELSE
2265
2266 stmt_rel := stmt_rel || ' AND POR.creation_date >= Trunc(PO_MASS_UPDATE_PO_PVT.get_date_from)
2267 AND POR.creation_date < Trunc(PO_MASS_UPDATE_PO_PVT.get_date_to)+1';
2268
2269 END IF;
2270
2271 IF p_supplier_id IS NOT NULL THEN
2272
2273 stmt_rel := stmt_rel || ' AND POH.vendor_id = PO_MASS_UPDATE_PO_PVT.get_supplier_id';
2274
2275 END IF;
2276
2277 IF p_include_close_po = 'YES' THEN
2278
2279 stmt_rel := stmt_rel || ' AND Nvl(POR.closed_code,''OPEN'') NOT IN (''FINALLY CLOSED'') '; /* Bug 6868589 */
2280
2281 ELSE
2282
2283 stmt_rel := stmt_rel || ' AND Nvl(POR.closed_code,''OPEN'') NOT IN (''CLOSED'',''FINALLY CLOSED'') '; /* Bug 6868589 */
2284
2285 END IF;
2286
2287 stmt_rel := stmt_rel || ' ORDER BY POH.segment1,POR.release_num';
2288
2289
2290 IF (p_document_type IS NULL OR p_document_type IN ('BLANKET','PLANNED','ALL')) THEN -- <BUG 6988269 Added 'ALL' condition>
2291
2292 OPEN c_rel for stmt_rel;
2293
2294 LOOP
2295
2296 FETCH c_rel INTO l_rel_rowid,
2297 l_po_num,
2298 l_rel_num,
2299 l_doc_type;
2300
2301 EXIT WHEN c_rel%NOTFOUND;
2302
2303 BEGIN
2304
2305 SAVEPOINT Update_DeliverTo_RECREL_SP;
2306
2307 l_progress := '007';
2308
2309 IF g_debug_stmt THEN
2310
2311 PO_DEBUG.debug_var(l_log_head,l_progress,'l_rel_rowid',l_rel_rowid );
2312 PO_DEBUG.debug_var(l_log_head,l_progress,'l_po_num',l_po_num );
2313 PO_DEBUG.debug_var(l_log_head,l_progress,'l_rel_num',l_rel_num );
2314 PO_DEBUG.debug_var(l_log_head,l_progress,'l_doc_type',l_doc_type );
2315
2316 END IF;
2317
2318 UPDATE po_distributions_all
2319 SET deliver_to_person_id = p_new_personid,
2320 last_update_date = sysdate,
2321 last_updated_by = fnd_global.user_id,
2322 last_update_login = fnd_global.login_id
2323 WHERE rowid = l_rel_rowid;
2324
2325 l_progress := '007';
2326
2327 IF g_debug_stmt THEN
2328
2329 PO_DEBUG.debug_var(l_log_head,l_progress,'p_new_personid', p_new_personid);
2330
2331 END IF;
2332
2333
2334 l_commit_count := l_commit_count + 1;
2335
2336 IF l_commit_count = p_commit_interval then
2337
2338 COMMIT;
2339 l_commit_count := 0;
2340
2341 END IF;
2342
2343
2344 l_progress := '007';
2345
2346 IF g_debug_stmt THEN
2347
2348 PO_DEBUG.debug_var(l_log_head,l_progress,'l_commit_count', l_commit_count);
2349
2350 END IF;
2351
2352 fnd_file.put_line(fnd_file.output, rpad(l_po_num || '-' || l_rel_num,32) || rpad(l_doc_type,26) || l_msg18);
2353
2354 EXCEPTION
2355
2356 WHEN OTHERS THEN
2357
2358 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2359
2360 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,l_log_head,l_progress|| SQLCODE || SUBSTR(SQLERRM,1,200));
2361
2362 END IF;
2363
2364 ROLLBACK TO Update_DeliverTo_RECREL_SP;
2365
2366 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2367
2368 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2369
2370 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_log_head );
2371
2372 END IF;
2373
2374 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => p_msg_count, p_data => p_msg_data );
2375
2376 END;
2377
2378 END LOOP;
2379
2380 CLOSE c_rel;
2381
2382 END IF; /* End of p_document_type IS NULL */
2383
2384 EXCEPTION
2385
2386 WHEN OTHERS THEN
2387
2388 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2389
2390 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,l_log_head,l_progress|| SQLCODE || SUBSTR(SQLERRM,1,200));
2391
2392 END IF;
2393
2394 ROLLBACK TO PO_Mass_Update_DeliverTo_SP;
2395
2396 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2397
2398 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2399
2400 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_log_head );
2401
2402 END IF;
2403
2404 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => p_msg_count, p_data => p_msg_data );
2405
2406 END Update_Deliver_To;
2407
2408 --------------------------------------------------------------------------------------------------
2409 -- Start of Comments
2410
2411 -- API Name : Print_Output
2412 -- Type : Private
2413 -- Pre-reqs : None
2414 -- Function : Prints the header and body of the output file showing the documents and
2415 -- document types updated along with the person who have been updated in the
2416 -- document.
2417
2418 -- Parameters :
2419
2420 -- IN : p_update_person Person needs to be updated(Buyer/Approver/Deliver_To).
2421 -- p_old_buyer_name Buyer name of the old person.
2422 -- p_new_buyer_name Buyer name of the new person.
2423 -- p_org_name Operating unit name.
2424 -- p_document_type Type of the document(STANDARD,BLANKET.CONTRACT,PLANNED).
2425 -- p_document_no_from Document number from.
2426 -- p_document_no_to Document number to.
2427 -- p_date_from Date from.
2428 -- p_date_to Date to.
2429 -- p_supplier_name Supplier name.
2430
2431 -- OUT : p_msg_data Actual message in encoded format.
2432 -- p_msg_count Holds the number of messages in the API list.
2433 -- p_return_status Return status of the API (Includes 'S','E','U').
2434
2435 -- End of Comments
2436 --------------------------------------------------------------------------------------------------
2437
2438 PROCEDURE Print_Output(p_update_person IN VARCHAR2,
2439 p_old_buyer_name IN VARCHAR2,
2440 p_new_buyer_name IN VARCHAR2,
2441 p_org_name IN VARCHAR2,
2442 p_document_type IN VARCHAR2,
2443 p_document_no_from IN VARCHAR2,
2444 p_document_no_to IN VARCHAR2,
2445 p_date_from IN DATE,
2446 p_date_to IN DATE,
2447 p_supplier_name IN VARCHAR2,
2448 p_msg_data OUT NOCOPY VARCHAR2,
2449 p_msg_count OUT NOCOPY NUMBER,
2450 p_return_status OUT NOCOPY VARCHAR2) IS
2451
2452 l_msg1 VARCHAR2(240);
2453 l_msg2 VARCHAR2(240);
2454 l_msg3 VARCHAR2(240);
2455 l_msg4 VARCHAR2(240);
2456 l_msg5 VARCHAR2(240);
2457 l_msg6 VARCHAR2(240);
2458 l_msg7 VARCHAR2(240);
2459 l_msg8 VARCHAR2(240);
2460 l_msg9 VARCHAR2(240);
2461 l_msg10 VARCHAR2(240);
2462 l_msg11 VARCHAR2(240);
2463 l_msg12 VARCHAR2(240);
2464 l_msg13 VARCHAR2(240);
2465 l_msg14 VARCHAR2(240);
2466 l_msg15 VARCHAR2(240);
2467
2468
2469 l_progress VARCHAR2(3);
2470 l_log_head CONSTANT VARCHAR2(1000) := g_log_head||'Print_Output';
2471
2472 BEGIN
2473
2474 l_progress := '000';
2475
2476 IF g_debug_stmt THEN
2477
2478 PO_DEBUG.debug_begin(l_log_head);
2479 PO_DEBUG.debug_var(l_log_head,l_progress,'p_update_person',p_update_person );
2480 PO_DEBUG.debug_var(l_log_head,l_progress,'p_old_buyer_name',p_old_buyer_name );
2481 PO_DEBUG.debug_var(l_log_head,l_progress,'p_new_buyer_name',p_new_buyer_name );
2482 PO_DEBUG.debug_var(l_log_head,l_progress,'p_org_name',p_org_name );
2483 PO_DEBUG.debug_var(l_log_head,l_progress,'p_document_type',p_document_type );
2484 PO_DEBUG.debug_var(l_log_head,l_progress,'p_document_no_from',p_document_no_from );
2485 PO_DEBUG.debug_var(l_log_head,l_progress,'p_document_no_to',p_document_no_to );
2486 PO_DEBUG.debug_var(l_log_head,l_progress,'p_date_from', p_date_from);
2487 PO_DEBUG.debug_var(l_log_head,l_progress,'p_date_to', p_date_to);
2488 PO_DEBUG.debug_var(l_log_head,l_progress,'p_supplier_name',p_supplier_name );
2489
2490 END IF;
2491
2492 fnd_message.set_name('PO','PO_MUB_MSG_BUYER_HEADER1');
2493 l_msg1 := fnd_message.get;
2494
2495 fnd_message.set_name('PO','PO_MUB_MSG_DATE');
2496 l_msg2 := fnd_message.get;
2497
2498 fnd_message.set_name('PO','PO_MUB_MSG_OU');
2499 l_msg3 := fnd_message.get;
2500
2501 fnd_message.set_name('PO','PO_MUB_MSG_OLD_PERSON');
2502 l_msg4 := fnd_message.get;
2503
2504 fnd_message.set_name('PO','PO_MUB_MSG_NEW_PERSON');
2505 l_msg5 := fnd_message.get;
2506
2507 fnd_message.set_name('PO','PO_MUB_MSG_DOC_SUB_TYPE');
2508 l_msg6 := fnd_message.get;
2509
2510 fnd_message.set_name('PO','PO_MUB_MSG_DOC_NUM_FROM');
2511 l_msg7 := fnd_message.get;
2512
2513 fnd_message.set_name('PO','PO_MUB_MSG_DOC_NUM_TO');
2514 l_msg8 := fnd_message.get;
2515
2516 fnd_message.set_name('PO','PO_MUB_MSG_DATE_FROM');
2517 l_msg9 := fnd_message.get;
2518
2519 fnd_message.set_name('PO','PO_MUB_MSG_DATE_TO');
2520 l_msg10 := fnd_message.get;
2521
2522 fnd_message.set_name('PO','PO_MUB_MSG_SUPPLIER');
2523 l_msg11 := fnd_message.get;
2524
2525 SAVEPOINT Print_SP;
2526
2527 IF (p_update_person = 'BUYER') THEN
2528
2529 fnd_message.set_name('PO','PO_MUB_MSG_BUYER_HEADER2');
2530 fnd_message.set_token('OLD_BUYER',p_old_buyer_name);
2531 fnd_message.set_token('NEW_BUYER',p_new_buyer_name);
2532
2533 ELSIF (p_update_person = 'APPROVER') THEN
2534
2535 fnd_message.set_name('PO','PO_MUB_MSG_BUYER_HEADER3');
2536 fnd_message.set_token('OLD_APPROVER',p_old_buyer_name);
2537 fnd_message.set_token('NEW_APPROVER',p_new_buyer_name);
2538
2539 ELSIF (p_update_person = 'DELIVER TO') THEN
2540
2541 fnd_message.set_name('PO','PO_MUB_MSG_BUYER_HEADER4');
2542 fnd_message.set_token('OLD_DELIVER_TO_PERSON',p_old_buyer_name);
2543 fnd_message.set_token('NEW_DELIVER_TO_PERSON',p_new_buyer_name);
2544
2545 ELSIF (p_update_person = 'ALL') THEN
2546
2547 fnd_message.set_name('PO','PO_MUB_MSG_BUYER_HEADER5');
2548 fnd_message.set_token('OLD_PERSON',p_old_buyer_name);
2549 fnd_message.set_token('NEW_PERSON',p_new_buyer_name);
2550
2551 END IF;
2552
2553 l_progress := '001';
2554
2555 IF g_debug_stmt THEN
2556
2557 PO_DEBUG.debug_var(l_log_head,l_progress,'p_update_person',p_update_person );
2558
2559 END IF;
2560
2561 l_msg12 := fnd_message.get;
2562
2563 fnd_message.set_name('PO','PO_MUB_MSG_DOC_NUM');
2564 l_msg13 := fnd_message.get;
2565
2566 fnd_message.set_name('PO','PO_MUB_MSG_DOC_TYPE');
2567 l_msg14 := fnd_message.get;
2568
2569 fnd_message.set_name('PO','PO_MUB_MSG_PERSON');
2570 l_msg15 := fnd_message.get;
2571
2572 l_progress := '002';
2573
2574 fnd_file.put_line(fnd_file.output, l_msg1);
2575 fnd_file.put_line(fnd_file.output, ' ');
2576 fnd_file.put_line(fnd_file.output, rpad(l_msg2,21) || ' : ' || sysdate);
2577 fnd_file.put_line(fnd_file.output, rpad(l_msg3,21) || ' : ' || p_org_name);
2578 fnd_file.put_line(fnd_file.output, rpad(l_msg4,21) || ' : ' || p_old_buyer_name);
2579 fnd_file.put_line(fnd_file.output, rpad(l_msg5,21) || ' : ' || p_new_buyer_name);
2580 fnd_file.put_line(fnd_file.output, rpad(l_msg6,21) || ' : ' || p_document_type);
2581 l_progress := '003';
2582 fnd_file.put_line(fnd_file.output, rpad(l_msg7,21) || ' : ' || p_document_no_from);
2583 fnd_file.put_line(fnd_file.output, rpad(l_msg8,21) || ' : ' || p_document_no_to);
2584 fnd_file.put_line(fnd_file.output, rpad(l_msg9,21) || ' : ' || p_date_from);
2585 fnd_file.put_line(fnd_file.output, rpad(l_msg10,21) || ' : ' || p_date_to);
2586 fnd_file.put_line(fnd_file.output, rpad(l_msg11,21) || ' : ' || p_supplier_name);
2587 l_progress := '004';
2588 fnd_file.put_line(fnd_file.output, ' ');
2589 fnd_file.put_line(fnd_file.output, l_msg12);
2590 fnd_file.put_line(fnd_file.output, ' ');
2591 fnd_file.put_line(fnd_file.output, rpad(l_msg13,26) || rpad(l_msg14,32) || l_msg15);
2592 fnd_file.put_line(fnd_file.output, rpad('-',70,'-'));
2593
2594 EXCEPTION
2595
2596 WHEN OTHERS THEN
2597
2598 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2599
2600 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,l_log_head,l_progress|| SQLCODE || SUBSTR(SQLERRM,1,200));
2601
2602 END IF;
2603
2604 ROLLBACK TO Print_SP;
2605
2606 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2607
2608 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2609
2610 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_log_head );
2611
2612 END IF;
2613
2614 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => p_msg_count, p_data => p_msg_data );
2615
2616 END Print_Output;
2617
2618 --------------------------------------------------------------------------------------------------
2619 -- Start of Comments
2620
2621 -- API Name : Buyer_Info
2622 -- Type : Private
2623 -- Pre-reqs : None
2624 -- Function : Gets the Buyer Information.
2625
2626 -- Parameters :
2627
2628 -- IN : p_old_personid Id of the old person.
2629 -- p_new_personid Id of the new person.
2630 -- p_supplier_id Supplier id.
2631
2632 -- OUT : p_old_buyer_name Buyer name of the old person.
2633 -- p_new_buyer_name Buyer name of the new person.
2634 -- p_old_username User name of the old person.
2635 -- p_new_username User name of the new person.
2636 -- p_new_user_display_name Display name of the new person.
2637 -- p_old_buyer_user_id Old person's Buyer user id.
2638 -- p_new_buyer_user_id New person's Buyer user id.
2639 -- p_supplier_name Supplier name.
2640 -- p_org_name Operating unit name.
2641 -- p_msg_data Actual message in encoded format.
2642 -- p_msg_count Holds the number of messages in the API list.
2643 -- p_return_status Return status of the API (Includes 'S','E','U').
2644
2645 -- End of Comments
2646 --------------------------------------------------------------------------------------------------
2647
2648 PROCEDURE Buyer_Info(p_old_personid IN NUMBER,
2649 p_new_personid IN NUMBER,
2650 p_supplier_id IN NUMBER,
2651 p_old_buyer_name OUT NOCOPY VARCHAR2,
2652 p_new_buyer_name OUT NOCOPY VARCHAR2,
2653 p_old_username OUT NOCOPY VARCHAR2,
2654 p_new_username OUT NOCOPY VARCHAR2,
2655 p_new_user_display_name OUT NOCOPY VARCHAR2,
2656 p_old_buyer_user_id OUT NOCOPY NUMBER,
2657 p_new_buyer_user_id OUT NOCOPY NUMBER,
2658 p_supplier_name OUT NOCOPY VARCHAR2,
2659 p_org_name OUT NOCOPY VARCHAR2,
2660 p_msg_data OUT NOCOPY VARCHAR2,
2661 p_msg_count OUT NOCOPY NUMBER,
2662 p_return_status OUT NOCOPY VARCHAR2) IS
2663
2664 l_progress VARCHAR2(3);
2665 l_log_head CONSTANT VARCHAR2(1000) := g_log_head||'Buyer_Info';
2666 l_org_id NUMBER;
2667
2668 BEGIN
2669
2670 l_progress := '000';
2671
2672 IF g_debug_stmt THEN
2673
2674 PO_DEBUG.debug_begin(l_log_head);
2675 PO_DEBUG.debug_var(l_log_head,l_progress,'p_old_personid',p_old_personid );
2676 PO_DEBUG.debug_var(l_log_head,l_progress,'p_new_personid',p_new_personid );
2677 PO_DEBUG.debug_var(l_log_head,l_progress,'p_supplier_id',p_supplier_id );
2678
2679 END IF;
2680
2681 SAVEPOINT Buyer_Info_SP;
2682
2683 l_progress := '001';
2684
2685 p_old_buyer_name := PO_EMPLOYEES_SV.get_emp_name(p_old_personid);
2686
2687 IF g_debug_stmt THEN
2688
2689 PO_DEBUG.debug_var(l_log_head,l_progress,'p_old_buyer_name',p_old_buyer_name );
2690
2691 END IF;
2692
2693 p_new_buyer_name := PO_EMPLOYEES_SV.get_emp_name(p_new_personid);
2694
2695
2696 IF g_debug_stmt THEN
2697
2698 PO_DEBUG.debug_var(l_log_head,l_progress,'p_new_buyer_name',p_new_buyer_name );
2699
2700 END IF;
2701
2702 WF_DIRECTORY.GetUserName('PER',p_old_personid, p_old_username,p_old_user_display_name);
2703 IF g_debug_stmt THEN
2704
2705 PO_DEBUG.debug_var(l_log_head,l_progress,'p_old_username',p_old_username );
2706 PO_DEBUG.debug_var(l_log_head,l_progress,'p_old_user_display_name',p_old_user_display_name );
2707
2708
2709 END IF;
2710
2711
2712 WF_DIRECTORY.GetUserName('PER',p_new_personid, p_new_username,p_new_user_display_name);
2713 IF g_debug_stmt THEN
2714
2715 PO_DEBUG.debug_var(l_log_head,l_progress,'p_new_username',p_new_username );
2716 PO_DEBUG.debug_var(l_log_head,l_progress,'p_new_user_display_name',p_new_user_display_name );
2717
2718
2719 END IF;
2720
2721 SELECT user_id
2722 INTO p_old_buyer_user_id
2723 FROM fnd_user
2724 WHERE employee_id = p_old_personid
2725 AND user_name = p_old_username;
2726
2727 IF g_debug_stmt THEN
2728
2729 PO_DEBUG.debug_var(l_log_head,l_progress,'p_old_buyer_user_id',p_old_buyer_user_id );
2730
2731 END IF;
2732
2733 SELECT user_id
2734 INTO p_new_buyer_user_id
2735 FROM fnd_user
2736 WHERE employee_id = p_new_personid
2737 AND user_name = p_new_username;
2738
2739 IF g_debug_stmt THEN
2740
2741 PO_DEBUG.debug_var(l_log_head,l_progress,'p_new_buyer_user_id',p_new_buyer_user_id );
2742
2743 END IF;
2744
2745 IF (p_supplier_id IS NOT NULL) then
2746
2747 SELECT vendor_name
2748 INTO p_supplier_name
2749 FROM po_vendors
2750 WHERE vendor_id = p_supplier_id;
2751
2752 END IF;
2753
2754 SELECT org_id
2755 INTO l_org_id
2756 FROM po_system_parameters;
2757
2758 SELECT hou.name
2759 INTO p_org_name
2760 FROM hr_all_organization_units hou,
2761 hr_all_organization_units_tl hout
2762 WHERE hou.organization_id = hout.organization_id
2763 AND hout.LANGUAGE = UserEnv('LANG')
2764 AND hou.organization_id = l_org_id;
2765
2766 IF g_debug_stmt THEN
2767
2768 PO_DEBUG.debug_var(l_log_head,l_progress,'p_old_buyer_name', p_old_buyer_name);
2769 PO_DEBUG.debug_var(l_log_head,l_progress,'p_new_buyer_name', p_new_buyer_name);
2770 PO_DEBUG.debug_var(l_log_head,l_progress,'p_old_username', p_old_username);
2771 PO_DEBUG.debug_var(l_log_head,l_progress,'p_new_username', p_new_username);
2772 PO_DEBUG.debug_var(l_log_head,l_progress,'p_new_user_display_name', p_new_user_display_name);
2773 PO_DEBUG.debug_var(l_log_head,l_progress,'p_old_buyer_user_id', p_old_buyer_user_id);
2774 PO_DEBUG.debug_var(l_log_head,l_progress,'p_new_buyer_user_id', p_new_buyer_user_id);
2775 PO_DEBUG.debug_var(l_log_head,l_progress,'p_supplier_name',p_supplier_name);
2776 PO_DEBUG.debug_var(l_log_head,l_progress,'p_org_name ', p_org_name);
2777
2778 END IF;
2779
2780 EXCEPTION
2781
2782 WHEN OTHERS THEN
2783 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2784
2785 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,l_log_head,l_progress || SQLCODE || SUBSTR(SQLERRM,1,200));
2786
2787 END IF;
2788
2789 ROLLBACK TO Buyer_Info_SP;
2790
2791 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2792
2793 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2794
2795 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_log_head );
2796
2797 END IF;
2798
2799 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => p_msg_count, p_data => p_msg_data );
2800
2801 END Buyer_Info;
2802
2803 --------------------------------------------------------------------------------------------------
2804
2805 -- Functions declared to return the value of the parameters passed in this API.
2806
2807 --------------------------------------------------------------------------------------------------
2808
2809 FUNCTION get_old_personid RETURN NUMBER
2810 IS
2811 BEGIN
2812 RETURN g_old_personid;
2813 END;
2814
2815 FUNCTION get_document_type RETURN VARCHAR2
2816 IS
2817 BEGIN
2818 RETURN g_document_type;
2819 END;
2820
2821 FUNCTION get_document_no_from RETURN VARCHAR2
2822 IS
2823 BEGIN
2824 RETURN g_document_no_from;
2825 END;
2826
2827 FUNCTION get_document_no_to RETURN VARCHAR2
2828 IS
2829 BEGIN
2830 RETURN g_document_no_to;
2831 END;
2832
2833 FUNCTION get_date_from RETURN DATE
2834 IS
2835 BEGIN
2836 RETURN g_date_from;
2837 END;
2838
2839 FUNCTION get_date_to RETURN DATE
2840 IS
2841 BEGIN
2842 RETURN g_date_to;
2843 END;
2844
2845 FUNCTION get_supplier_id RETURN NUMBER
2846 IS
2847 BEGIN
2848 RETURN g_supplier_id;
2849 END;
2850
2851 FUNCTION get_old_username RETURN VARCHAR2
2852 IS
2853 BEGIN
2854 RETURN g_old_username;
2855 END;
2856
2857 END PO_Mass_Update_PO_PVT;