[Home] [Help]
PACKAGE BODY: APPS.PO_CLOSEOUT_PVT
Source
1 PACKAGE BODY PO_CLOSEOUT_PVT AS
2 --$Header: PO_CLOSEOUT_PVT.plb 120.9.12020000.22 2013/05/09 11:52:04 mabaig noship $
3
4 d_pkg_name CONSTANT varchar2(50) := PO_LOG.get_package_base('PO_CLOSEOUT_PVT');
5
6
7 g_debug_stmt CONSTANT BOOLEAN := PO_DEBUG.is_debug_stmt_on;
8 --------------------------------------------------------------------------------
9 --Start of Comments
10
11 --Name: log
12
13 --Function:
14 -- For logging messages
15
16 --Parameters:
17
18 --IN:
19 --p_log_message VARCHAR2
20
21 --End of Comments
22 --------------------------------------------------------------------------------
23
24 PROCEDURE log(
25 p_module_base IN VARCHAR2,
26 p_position IN VARCHAR2,
27 p_log_message IN VARCHAR2
28 )
29 AS
30
31 BEGIN
32
33 IF (PO_LOG.d_stmt) THEN
34 PO_LOG.stmt(p_module_base => p_module_base,
35 p_position => p_position ,
36 p_message_text => p_log_message);
37 END IF;
38
39 END log;
40 --------------------------------------------------------------------------------
41 --Start of Comments
42
43 --Name: create_contract
44
45 --Function:
46 -- This will call out the Contract API to create the contract
47
48 --Parameters:
49
50 --IN:
51 -- p_doc_id NUMBER
52 -- p_closeout_type VARCHAR2
53
54 --OUT:
55 -- x_contract_id NUMBER
56 -- x_contract_ver NUMBER
57 -- x_contract_type VARCHAR2
58 -- x_return_status VARCHAR2
59 -- x_return_msg VARCHAR2
60
61 --End of Comments
62 --------------------------------------------------------------------------------
63
64 PROCEDURE create_contract (
65 p_doc_id IN NUMBER
66 , p_closeout_type IN VARCHAR
67 , x_contract_id OUT NOCOPY NUMBER
68 , x_contract_ver OUT NOCOPY NUMBER
69 , x_contract_type OUT NOCOPY VARCHAR2
70 , x_return_status OUT NOCOPY VARCHAR2
71 , x_return_msg OUT NOCOPY VARCHAR2
72 )
73 IS
74 l_progress NUMBER := 0;
75 l_po_doc_type VARCHAR2(25);
76 l_contr_type VARCHAR2(25);
77 l_rev_number NUMBER;
78 l_doc_num VARCHAR2(100);
79 l_org_id NUMBER;
80 l_vendor_id NUMBER;
81 l_vendor_name VARCHAR2(100);
82 l_vendor_contact_id NUMBER;
83 l_vendor_site_id NUMBER;
84 l_vendor_contact_name VARCHAR2(100);
85 l_org_name VARCHAR2(100);
86 l_msg_count NUMBER;
87 l_document_rec OKC_IMP_RECORD_TYPES.document_rec_type;
88 l_contract_rec OKC_IMP_RECORD_TYPES.contract_rec_type;
89 l_party_contacts OKC_IMP_RECORD_TYPES.party_contacts_tbl_type;
90 l_contract_parties OKC_IMP_RECORD_TYPES.contract_parties_tbl_type;
91 l_risks_tbl OKC_IMP_RECORD_TYPES.contract_risks_tbl_type;
92
93 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || '.' || 'create_contract';
94 d_position NUMBER;
95
96 BEGIN
97
98 d_position := 0;
99 IF (PO_LOG.d_proc) THEN
100 PO_LOG.proc_begin(d_module);
101 END IF;
102
103 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'p_doc_id ' || p_doc_id);
104 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'p_closeout_type ' || p_closeout_type);
105
106 IF g_CLOSEOUT_TYPE_INDIVIDUAL = p_closeout_type THEN
107 SELECT type_lookup_code,revision_num,clm_document_number,org_id,vendor_id, vendor_contact_id,vendor_site_id
108 INTO l_po_doc_type, l_rev_number, l_doc_num,l_org_id,l_vendor_id, l_vendor_contact_id,l_vendor_site_id
109 FROM PO_HEADERS_ALL
110 WHERE po_header_id = p_doc_id;
111
112 l_progress := 10;
113 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'l_po_doc_type ' || l_po_doc_type);
114 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'l_rev_number ' || l_rev_number);
115 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'l_doc_num ' || l_doc_num);
116 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'l_org_id ' || l_org_id);
117 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'l_vendor_id ' || l_vendor_id);
118 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'l_vendor_contact_id ' || l_vendor_contact_id);
119 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'l_vendor_site_id ' || l_vendor_site_id);
120
121
122 SELECT name
123 INTO l_org_name
124 FROM hr_all_organization_units_tl
125 WHERE organization_id = l_org_id
126 AND language = UserEnv('lang');
127
128 l_progress := 20;
129 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'l_org_name ' || l_org_name);
130 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'fnd_global.USER_NAME ' || fnd_global.USER_NAME);
131
132 l_contr_type := po_conterms_utl_grp.get_po_contract_doctype(l_po_doc_type);
133 l_document_rec.document_number := l_doc_num;
134 ELSE
135 l_contr_type := 'PO_CLOSEOUT_SET';
136 l_org_id := fnd_global.ORG_ID;
137 l_org_name := PO_MOAC_UTILS_PVT.GET_OU_NAME(l_org_id);
138 l_doc_num := p_doc_id;
139 l_vendor_contact_id := NULL;
140 END IF;
141
142 l_progress := 30;
143 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'l_contr_type ' || l_contr_type);
144
145 l_document_rec.business_document_type := l_contr_type;
146 l_document_rec.business_document_id := p_doc_id;
147 --l_document_rec.business_document_version := l_rev_number;
148 l_document_rec.source_code := 'INTERNAL';
149 l_document_rec.relationship_id := 10006;
150 l_document_rec.display_in_contract := 'N';
151
152 l_progress := 40;
153
154 l_contract_rec.contract_number := l_doc_num;
155 l_contract_rec.contract_name := l_doc_num;
156 l_contract_rec.org_name := l_org_name;
157 l_contract_rec.contract_status_txt := 'Draft';
158 l_contract_rec.contract_effective_date := TO_CHAR(SYSDATE, 'MM/DD/YYYY');
159 l_contract_rec.contract_type_txt := 'Contract Closeout';
160 l_contract_rec.contract_version_num := 1 ;
161 l_contract_rec.description := 'Closeout :' || l_doc_num;
162 l_contract_rec.authoring_party_txt := 'Internal' ;
163 l_contract_rec.owner_user_name := fnd_global.USER_NAME ;
164 l_contract_rec.source_code := 'INTERNAL' ;
165
166 l_progress := 50;
167
168 l_contract_parties(1).party_role_txt := 'Internal';
169 l_contract_parties(1).party_name_txt := l_org_name;
170 l_contract_parties(1).party_index := 1;
171
172 IF g_CLOSEOUT_TYPE_INDIVIDUAL = p_closeout_type THEN
173 SELECT pv.vendor_name
174 INTO l_vendor_name
175 FROM po_vendors pv
176 WHERE pv.vendor_id = l_vendor_id;
177
178 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'l_vendor_name ' || l_vendor_name);
179 l_progress := 60;
180 l_contract_parties(2).party_role_txt := 'Supplier';
181 l_contract_parties(2).party_name_txt := l_vendor_name;
182 l_contract_parties(2).party_index := 2;
183 END IF;
184
185 l_progress := 70;
186
187 IF l_vendor_contact_id IS NOT NULL THEN
188
189 BEGIN
190 SELECT Nvl(LAST_NAME, '') || ', ' || Nvl(FIRST_NAME, '')
191 INTO l_vendor_contact_name
192 FROM po_vendor_contacts
193 WHERE vendor_contact_id = l_vendor_contact_id
194 AND vendor_site_id = l_vendor_site_id;
195
196 EXCEPTION
197 WHEN No_Data_Found THEN
198 l_vendor_contact_name := '';
199 END;
200
201 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'l_vendor_contact_name ' || l_vendor_contact_name);
202 --contacts
203 l_party_contacts(1).contact_index := 1;
204 l_party_contacts(1).party_role_txt := 'Supplier';
205 l_party_contacts(1).party_name_txt := l_vendor_name;
206 l_party_contacts(1).contact_name_txt := l_vendor_contact_name;
207 l_party_contacts(1).contact_role_txt := 'Contracting Officer';
208 l_party_contacts(1).contact_id := l_vendor_contact_id;
209
210 END IF;
211
212 l_progress := 80;
213
214 OKC_REP_CLOSEOUT_PVT.create_contract(p_api_version => 1.0
215 ,p_document_rec => l_document_rec
216 ,p_contract_rec => l_contract_rec
217 ,p_party_contacts_tbl => l_party_contacts
218 ,p_contract_parties_tbl => l_contract_parties
219 ,p_risks_tbl => l_risks_tbl
220 ,p_commit => fnd_api.g_true
221 ,x_msg_data => x_return_msg
222 ,x_return_status=> x_return_status
223 ,x_msg_count => l_msg_count);
224
225 l_progress := 90;
226
227 x_contract_id := l_contract_rec.contract_id;
228 x_contract_ver := l_contract_rec.contract_version_num;
229 x_contract_type := l_contract_rec.contract_type;
230
231 l_progress := 100;
232
233 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'x_contract_id ' || x_contract_id);
234 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'x_contract_ver ' || x_contract_ver);
235 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'x_contract_type ' || x_contract_type);
236 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'x_return_status ' || x_return_status);
237
238 IF (PO_LOG.d_proc) THEN PO_LOG.proc_end(d_module); END IF;
239
240 EXCEPTION
241 WHEN OTHERS THEN
242
243 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'PO_CLOSEOUT_PVT.create_contract : when others exception at '
244 || l_progress || ';' || SQLERRM );
245 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
246 x_return_msg := SQLERRM;
247 END create_contract;
248 --------------------------------------------------------------------------------
249 --Start of Comments
250
251 --Name: check_contract_exists
252
253 --Function:
254 -- This will call out the Contract API to check contract exists or not
255
256 --Parameters:
257
258 --IN:
259 -- p_doc_id NUMBER
260 -- p_closeout_type VARCHAR2
261
262 --OUT:
263 -- x_contract_id NUMBER
264 -- x_contract_ver NUMBER
265 -- x_contract_type VARCHAR2
266 -- x_return_status VARCHAR2
267 -- x_return_msg VARCHAR2
268
269 --End of Comments
270 --------------------------------------------------------------------------------
271
272 PROCEDURE check_contract_exists(
273 p_doc_id IN NUMBER
274 , p_closeout_type IN VARCHAR
275 , x_contract_id OUT NOCOPY NUMBER
276 , x_contract_ver OUT NOCOPY NUMBER
277 , x_contract_type OUT NOCOPY VARCHAR2
278 , x_return_status OUT NOCOPY VARCHAR2
279 , x_return_msg OUT NOCOPY VARCHAR2
280 )
281 IS
282
283 l_progress NUMBER := 0;
284 l_po_doc_type VARCHAR2(25);
285 l_contr_type VARCHAR2(25);
286 l_rev_number NUMBER;
287 l_doc_num VARCHAR2(100);
288 l_msg_count NUMBER;
289 l_document_rec OKC_IMP_RECORD_TYPES.document_rec_type;
290
291 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || '.' || 'check_contract_exists';
292 d_position NUMBER;
293
294 BEGIN
295 d_position := 0;
296 IF (PO_LOG.d_proc) THEN
297 PO_LOG.proc_begin(d_module);
298 END IF;
299
300 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'p_doc_id ' || p_doc_id);
301 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'p_closeout_type ' || p_closeout_type);
302
303 IF g_CLOSEOUT_TYPE_INDIVIDUAL = p_closeout_type THEN
304 SELECT type_lookup_code,revision_num,clm_document_number
305 INTO l_po_doc_type, l_rev_number, l_doc_num
306 FROM PO_HEADERS_ALL
307 WHERE po_header_id = p_doc_id;
308
309 l_progress := 10;
310 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'l_po_doc_type ' || l_po_doc_type);
311 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'l_rev_number ' || l_rev_number);
312 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'l_doc_num ' || l_doc_num);
313
314 IF l_po_doc_type = 'STANDARD' THEN
315 l_contr_type := 'PO_STANDARD';
316 ELSIF l_po_doc_type = 'BLANKET' THEN
317 l_contr_type := 'PA_BLANKET';
318 ELSE
319 l_contr_type := 'PA_CONTRACT';
320 END IF;
321 l_document_rec.document_number := l_doc_num;
322 ELSE
323 l_contr_type := 'PO_CLOSEOUT_SET';
324 END IF;
325
326 l_progress := 20;
327
328 l_document_rec.business_document_type := l_contr_type;
329 l_document_rec.business_document_id := p_doc_id;
330 --l_document_rec.business_document_version := l_rev_number;
331 --l_document_rec.source_code := 'INTERNAL';
332 --l_document_rec.relationship_id := 10006;
333 --l_document_rec.display_in_contract := 'N';
334
335 l_progress := 30;
336
337 OKC_REP_CLOSEOUT_PVT.check_contract_exists(p_api_version => 1.0,
338 p_document_rec => l_document_rec,
339 x_contract_type => x_contract_type,
340 x_contract_id => x_contract_id,
341 x_contract_version_num => x_contract_ver,
342 x_msg_data => x_return_msg,
343 x_msg_count => l_msg_count,
344 x_return_status => x_return_status);
345 l_progress := 40;
346
347 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'x_contract_type ' || x_contract_type);
348 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'x_contract_id ' || x_contract_id);
349 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'x_contract_ver ' || x_contract_ver);
350
351 IF (PO_LOG.d_proc) THEN PO_LOG.proc_end(d_module); END IF;
352
353 EXCEPTION
354 WHEN OTHERS THEN
355
356 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'PO_CLOSEOUT_PVT.check_contract_exists : when others exception at '
357 || l_progress || ';' || SQLERRM );
358 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
359 x_return_msg := SQLERRM;
360
361 END check_contract_exists;
362
363 --------------------------------------------------------------------------------
364 --Start of Comments
365
366 --Name: get_contract
367
368 --Function:
369 -- This will call out the Contract API to check contract exists or not. If
370 -- It contract id not found. It will call the create_contract procedure of
371 -- this package
372
373 --Parameters:
374
375 --IN:
376 -- p_doc_id NUMBER
377 -- p_closeout_type VARCHAR2
378
379 --OUT:
380 -- x_contract_id NUMBER
381 -- x_contract_ver NUMBER
382 -- x_contract_type VARCHAR2
383 -- x_return_status VARCHAR2
384 -- x_return_msg VARCHAR2
385
386 --End of Comments
387 --------------------------------------------------------------------------------
388
389 PROCEDURE get_contract(
390 p_doc_id IN NUMBER
391 , p_closeout_type IN VARCHAR
392 , x_contract_id OUT NOCOPY NUMBER
393 , x_contract_ver OUT NOCOPY NUMBER
394 , x_contract_type OUT NOCOPY VARCHAR2
395 , x_return_status OUT NOCOPY VARCHAR2
396 , x_return_msg OUT NOCOPY VARCHAR2
397 )
398 IS
399
400 l_progress NUMBER := 0;
401 l_po_doc_type VARCHAR2(25);
402 l_contr_type VARCHAR2(25);
403 l_rev_number NUMBER;
404 l_doc_num VARCHAR2(100);
405 l_msg_count NUMBER;
406 l_document_rec OKC_IMP_RECORD_TYPES.document_rec_type;
407
408 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || '.' || 'get_contract';
409 d_position NUMBER;
410
411
412 BEGIN
413 d_position := 0;
414 IF (PO_LOG.d_proc) THEN
415 PO_LOG.proc_begin(d_module);
416 END IF;
417
418 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'p_doc_id ' || p_doc_id);
419 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'p_closeout_type ' || p_closeout_type);
420
421 l_progress := 10;
422
423 PO_CLOSEOUT_PVT.check_contract_exists(p_doc_id => p_doc_id,
424 p_closeout_type => p_closeout_type,
425 x_contract_type => x_contract_type,
426 x_contract_id => x_contract_id,
427 x_contract_ver => x_contract_ver,
428 x_return_msg => x_return_msg,
429 x_return_status => x_return_status);
430 l_progress := 20;
431
432 IF x_contract_id IS NULL THEN
433 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'Contract does not exists ');
434 PO_CLOSEOUT_PVT.create_contract(p_doc_id => p_doc_id,
435 p_closeout_type => p_closeout_type,
436 x_contract_type => x_contract_type,
437 x_contract_id => x_contract_id,
438 x_contract_ver => x_contract_ver,
439 x_return_msg => x_return_msg,
440 x_return_status => x_return_status);
441 END IF;
442
443
444 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'x_contract_type ' || x_contract_type);
445 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'x_contract_id ' || x_contract_id);
446 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'x_contract_ver ' || x_contract_ver);
447
448 IF (PO_LOG.d_proc) THEN PO_LOG.proc_end(d_module); END IF;
449
450 EXCEPTION
451 WHEN OTHERS THEN
452
453 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'PO_CLOSEOUT_PVT.get_contract : when others exception at '
454 || l_progress || ';' || SQLERRM );
455 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
456 x_return_msg := SQLERRM;
457
458 END get_contract;
459
460 --------------------------------------------------------------------------------
461 --Start of Comments
462
463 --Name: open_mod_exists
464
465 --Function:
466 -- Checks if there already exists an open modification
467
468 --Parameters:
469
470 --IN:
471 -- p_doc_id NUMBER
472
473 --OUT:
474 --x_result_type VARCHAR2
475
476 --End of Comments
477 --------------------------------------------------------------------------------
478
479 PROCEDURE open_mod_exists(
480 p_doc_id IN NUMBER
481 , x_result_type OUT NOCOPY VARCHAR2
482 )
483 IS
484 l_progress NUMBER := 0;
485 l_count VARCHAR2(1);
486
487 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || '.' || 'open_mod_exists';
488 d_position NUMBER;
489
490
491 BEGIN
492 d_position := 0;
493 IF (PO_LOG.d_proc) THEN
494 PO_LOG.proc_begin(d_module);
495 END IF;
496
497 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'p_doc_id ' || p_doc_id);
498
499 SELECT Count(1)
500 INTO l_count
501 FROM po_drafts dft
502 WHERE dft.document_id = p_doc_id
503 AND dft.draft_type= 'MOD'
504 AND dft.status IN ('DRAFT', 'IN PROCESS', 'REJECTED', 'SUPPLIER SIGN', 'PRE-APPROVED');
505
506 l_progress := 20;
507
508 IF l_count > 0 THEN
509 x_result_type := c_result_type_FAILURE;
510 ELSE
511 x_result_type := c_result_type_SUCCESS;
512 END IF;
513
514 IF (PO_LOG.d_proc) THEN PO_LOG.proc_end(d_module); END IF;
515
516 EXCEPTION
517 WHEN NO_DATA_FOUND THEN
518 x_result_type := c_result_type_SUCCESS;
519
520
521 END open_mod_exists;
522
523 --------------------------------------------------------------------------------
524 --Start of Comments
525
526 --Name: is_doc_fully_received
527
528 --Function:
529 -- Checks if the document is fully receieved
530
531 --Parameters:
532
533 --IN:
534 -- p_doc_id NUMBER
535
536 --OUT:
537 --x_result_type VARCHAR2
538
539 --End of Comments
540 --------------------------------------------------------------------------------
541
542 PROCEDURE is_doc_fully_received (
543 p_doc_id IN NUMBER
544 , x_result_type OUT NOCOPY VARCHAR2
545 )IS
546 l_progress NUMBER := 0;
547 l_count VARCHAR2(1);
548
549 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || '.' || 'is_doc_fully_received';
550 d_position NUMBER;
551
552
553 BEGIN
554 d_position := 0;
555 IF (PO_LOG.d_proc) THEN
556 PO_LOG.proc_begin(d_module);
557 END IF;
558
559 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'p_doc_id ' || p_doc_id);
560
561 SELECT Count(1)
562 INTO l_count
563 FROM po_line_locations_all poll
564 WHERE poll.po_header_id = p_doc_id
565 AND Nvl(poll.closed_code,'OPEN') IN ('OPEN')
566 AND Nvl(poll.cancel_flag, 'N') = 'N';
567
568 l_progress := 20;
569
570 IF l_count > 0 THEN
571 x_result_type := c_result_type_FAILURE;
572 ELSE
573 x_result_type := c_result_type_SUCCESS;
574 END IF;
575
576 IF (PO_LOG.d_proc) THEN PO_LOG.proc_end(d_module); END IF;
577
578 EXCEPTION
579
580 WHEN NO_DATA_FOUND THEN
581 x_result_type := c_result_type_SUCCESS;
582
583 END is_doc_fully_received;
584
585 --------------------------------------------------------------------------------
586 --Start of Comments
587
588 --Name: is_doc_fully_invoiced
589
590 --Function:
591 -- Checks if the document is fully invoiced
592
593 --Parameters:
594
595 --IN:
596 -- p_doc_id NUMBER
597
598 --OUT:
599 --x_result_type VARCHAR2
600
601 --End of Comments
602 --------------------------------------------------------------------------------
603
604 PROCEDURE is_doc_fully_invoiced (
605 p_doc_id IN NUMBER
606 , x_result_type OUT NOCOPY VARCHAR2
607 ) IS
608 l_progress NUMBER := 0;
609 l_count VARCHAR2(1);
610
611 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || '.' || 'is_doc_fully_invoiced';
612 d_position NUMBER;
613
614
615 BEGIN
616 d_position := 0;
617 IF (PO_LOG.d_proc) THEN
618 PO_LOG.proc_begin(d_module);
619 END IF;
620
621 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'p_doc_id ' || p_doc_id);
622
623 SELECT Count(1)
624 INTO l_count
625 FROM po_line_locations_all poll
626 WHERE poll.po_header_id = p_doc_id
627 AND nvl(poll.closed_code,'OPEN') IN ('OPEN','CLOSED FOR RECEIVING',' CLOSED FOR INVOICE');
628
629 l_progress := 20;
630
631 IF l_count > 0 THEN
632 x_result_type := c_result_type_FAILURE;
633 ELSE
634 x_result_type := c_result_type_SUCCESS;
635 END IF;
636
637 IF (PO_LOG.d_proc) THEN PO_LOG.proc_end(d_module); END IF;
638
639 EXCEPTION
640 WHEN NO_DATA_FOUND THEN
641 x_result_type := c_result_type_SUCCESS;
642
643 END is_doc_fully_invoiced;
644
645 --------------------------------------------------------------------------------
646 --Start of Comments
647
648 --Name: is_doc_fully_paid
649
650 --Function:
651 -- Checks if the document is fully paid
652
653 --Parameters:
654
655 --IN:
656 -- p_doc_id NUMBER
657
658 --OUT:
659 --x_result_type VARCHAR2
660
661 --End of Comments
662 --------------------------------------------------------------------------------
663
664 PROCEDURE is_doc_fully_paid (
665 p_doc_id IN NUMBER
666 , x_result_type OUT NOCOPY VARCHAR2
667 ) IS
668 l_progress NUMBER := 0;
669 l_count VARCHAR2(1);
670
671 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || '.' || 'is_doc_fully_paid';
672 d_position NUMBER;
673
674
675 BEGIN
676 d_position := 0;
677 IF (PO_LOG.d_proc) THEN
678 PO_LOG.proc_begin(d_module);
679 END IF;
680
681 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'p_doc_id ' || p_doc_id);
682
683 SELECT Count(1)
684 INTO l_count
685 FROM ap_invoice_distributions_all aid,
686 ap_invoices_all aia,
687 po_distributions_all pod,
688 po_line_locations_all poll
689 WHERE aid.po_distribution_id = pod.po_distribution_id
690 AND aia.invoice_id = aid.invoice_id
691 AND pod.po_header_id = poll.po_header_id
692 AND poll.po_header_id = p_doc_id;
693
694 l_progress := 10;
695 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'Fully paid entries count ' || l_count);
696
697 IF l_count > 0 THEN
698 SELECT Count(1)
699 INTO l_count
700 FROM ap_invoice_distributions_all aid,
701 ap_invoices_all aia,
702 po_distributions_all pod,
703 po_line_locations_all poll
704 WHERE aid.po_distribution_id = pod.po_distribution_id
705 AND aia.invoice_id = aid.invoice_id
706 AND pod.po_header_id = poll.po_header_id
707 AND poll.po_header_id = p_doc_id
708 AND nvl(poll.closed_code,'OPEN') <> 'CLOSED FOR INVOICE'
709 AND aia.payment_status_flag <> 'Y';
710
711 l_progress := 20;
712
713 IF l_count > 0 THEN
714 x_result_type := c_result_type_FAILURE;
715 ELSE
716 x_result_type := c_result_type_SUCCESS;
717 END IF;
718 ELSE
719 x_result_type := c_result_type_FAILURE;
720
721 END IF;
722
723 IF (PO_LOG.d_proc) THEN PO_LOG.proc_end(d_module); END IF;
724
725 EXCEPTION
726 WHEN NO_DATA_FOUND THEN
727 x_result_type := c_result_type_FAILURE;
728
729 END is_doc_fully_paid;
730
731 --------------------------------------------------------------------------------
732 --Start of Comments
733
734 --Name: chklist_task_available
735
736 --Function:
737 -- Check if there are any checklists/tasks are available
738
739 --Parameters:
740
741 --IN:
742 -- p_doc_id NUMBER
743
744 --OUT:
745 --x_result_type VARCHAR2
746
747 --End of Comments
748 --------------------------------------------------------------------------------
749
750 PROCEDURE chklist_task_available (
751 p_doc_id IN NUMBER
752 , x_result_type OUT NOCOPY VARCHAR2
753 ) IS
754 l_progress NUMBER := 0;
755 l_count VARCHAR2(1);
756 l_po_doc_type VARCHAR2(25);
757 l_contr_type VARCHAR2(25);
758 l_rev_number NUMBER;
759 l_doc_rec OKC_IMP_RECORD_TYPES.document_rec_type;
760 l_msg_data VARCHAR2(1000);
761 l_msg_count NUMBER;
762
763 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || '.' || 'chklist_task_available';
764 d_position NUMBER;
765
766
767 BEGIN
768 d_position := 0;
769 IF (PO_LOG.d_proc) THEN
770 PO_LOG.proc_begin(d_module);
771 END IF;
772
773 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'p_doc_id ' || p_doc_id);
774
775 SELECT type_lookup_code,revision_num
776 INTO l_po_doc_type, l_rev_number
777 FROM PO_HEADERS_ALL
778 WHERE po_header_id = p_doc_id;
779
780 l_progress := 10;
781
782 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'l_po_doc_type ' || l_po_doc_type);
783 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'l_rev_number ' || l_rev_number);
784
785 l_contr_type := po_conterms_utl_grp.get_po_contract_doctype(l_po_doc_type);
786
787 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'l_contr_type ' || l_contr_type);
788
789 l_progress := 20;
790
791 l_doc_rec.business_document_type := l_contr_type;
792 l_doc_rec.business_document_id := p_doc_id;
793
794 OKC_REP_CLOSEOUT_PVT.check_tasks_exists(p_api_version => 1.0,
795 p_init_msg_list => 'TRUE',
796 p_document_rec => l_doc_rec,
797 x_del_count => l_count,
798 x_msg_data => l_msg_data,
799 x_msg_count => l_msg_count,
800 x_return_status => x_result_type);
801
802 /*SELECT Count(1)
803 INTO l_count
804 FROM okc_rep_contract_usages orcu, okc_deliverables del
805 WHERE orcu.business_document_type = l_contr_type
806 AND orcu.business_document_id = p_doc_id -- po_header_id
807 --AND orcu.business_document_version = l_rev_number revision_num
808 AND del.business_document_type = orcu.contract_type
809 AND del.business_document_id = orcu.contract_id
810 AND del.business_document_version = -99; */
811
812 l_progress := 30;
813
814 IF l_count > 0 THEN
815 x_result_type := c_result_type_SUCCESS;
816 ELSE
817 x_result_type := c_result_type_FAILURE;
818 END IF;
819
820 IF (PO_LOG.d_proc) THEN PO_LOG.proc_end(d_module); END IF;
821
822 EXCEPTION
823 WHEN NO_DATA_FOUND THEN
824 x_result_type := c_result_type_FAILURE;
825
826 END chklist_task_available;
827
828
829 --------------------------------------------------------------------------------
830 --Start of Comments
831
832 --Name: validate_manage_closeout
833
834 --Function:
835 -- Validate the Manage Closeout action
836
837 --Parameters:
838
839 --IN:
840 -- p_doc_id NUMBER
841 -- p_chk_list_req VARCHAR2
842
843 --OUT:
844 --x_result_type VARCHAR2
845 --x_results PO_MULTI_MOD_VAL_RESULTS_TYPE
846
847 --End of Comments
848 --------------------------------------------------------------------------------
849
850 PROCEDURE validate_manage_closeout (
851 p_doc_id IN NUMBER
852 , p_chk_list_req IN VARCHAR2
853 , x_result_type OUT NOCOPY VARCHAR2
854 , x_results IN OUT NOCOPY PO_MULTI_MOD_VAL_RESULTS_TYPE
855 ) IS
856 l_progress NUMBER := 0;
857 l_result VARCHAR2(50);
858 l_tasklist_result VARCHAR2(50);
859 l_open_mod_fail_type VARCHAR2(20);
860 l_document_rec OKC_IMP_RECORD_TYPES.document_rec_type;
861 l_contr_type VARCHAR2(50);
862 l_type_lookup_code VARCHAR2(50);
863 x_msg_data VARCHAR2(1000);
864 x_msg_count NUMBER;
865 x_return_status VARCHAR2(20);
866 x_qa_return_status VARCHAR2(20);
867 x_qa_result_tbl OKC_TERMS_QA_PVT.qa_result_tbl_type;
868 l_clm_doc_num VARCHAR2(100);
869
870 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || '.' || 'validate_manage_closeout';
871
872
873 BEGIN
874 IF (PO_LOG.d_proc) THEN
875 PO_LOG.proc_begin(d_module);
876 END IF;
877
878 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'p_doc_id ' || p_doc_id);
879 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'p_chk_list_req ' || p_chk_list_req);
880
881 x_result_type := c_result_type_SUCCESS;
882
883 IF p_chk_list_req = 'Y' THEN
884 l_open_mod_fail_type := 'WARNING';
885 ELSE
886 l_open_mod_fail_type := 'ERROR';
887 END IF;
888
889 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'l_open_mod_fail_type:' || l_open_mod_fail_type);
890
891 l_progress := 5;
892
893 SELECT clm_document_number
894 INTO l_clm_doc_num
895 FROM po_headers_all
896 WHERE po_header_id = p_doc_id;
897
898 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'l_clm_doc_num:' || l_clm_doc_num);
899
900 l_progress := 10;
901
902 IF (X_RESULTS IS NULL) THEN
903 X_RESULTS := PO_MULTI_MOD_VAL_RESULTS_TYPE.NEW_INSTANCE();
904 END IF;
905
906 SELECT type_lookup_code
907 INTO l_type_lookup_code
908 FROM PO_HEADERS_ALL
909 WHERE po_header_id = p_doc_id;
910
911 l_progress := 15;
912
913 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'l_type_lookup_code:' || l_type_lookup_code);
914 IF p_chk_list_req = 'Y' THEN
915 PO_CLOSEOUT_PVT.chklist_task_available(p_doc_id => p_doc_id
916 ,x_result_type => l_tasklist_result);
917
918 l_progress := 20;
919
920 IF l_tasklist_result = c_result_type_FAILURE THEN
921 x_results.add_result(p_multi_mod_val_result_id => po_multi_mod_val_results_s.nextval
922 , p_multi_mod_request_id => p_doc_id
923 , p_validation_type => 'CHECKLIST'
924 , p_exception_type => c_result_type_FAILURE
925 , p_document_number => l_clm_doc_num
926 , p_message_application => 'PO'
927 , p_message_name => 'CHKLIST_TASK_NOT_AVAILABLE');
928
929 ELSE
930
931 l_contr_type := po_conterms_utl_grp.get_po_contract_doctype(l_type_lookup_code);
932 l_document_rec.business_document_type := l_contr_type;
933 l_document_rec.business_document_id := p_doc_id;
934
935 OKC_REP_CLOSEOUT_PVT.validate_closeout_tasks(
936 p_api_version => 1.0,
937 p_init_msg_list => fnd_api.g_false,
938 p_commit => fnd_api.g_true,
939 p_document_rec => l_document_rec,
940 x_msg_data => x_msg_data,
941 x_msg_count => x_msg_count,
942 x_return_status => x_return_status,
943 x_qa_return_status => x_qa_return_status,
944 x_qa_result_tbl => x_qa_result_tbl);
945 l_progress := 30;
946
947 IF x_qa_result_tbl.COUNT > 0 THEN
948 FOR i IN x_qa_result_tbl.FIRST .. x_qa_result_tbl.LAST
949 LOOP
950 IF (x_qa_result_tbl(i).error_severity_name = OKC_REP_CLOSEOUT_PVT.G_QA_STS_WARNING) THEN
951 x_results.add_result(p_multi_mod_val_result_id => po_multi_mod_val_results_s.nextval
952 , p_multi_mod_request_id => p_doc_id
953 , p_validation_type => 'CONTRACT'
954 , p_exception_type => c_result_type_WARNING
955 , p_document_number => l_clm_doc_num
956 , p_message_application => 'OKC'
957 , p_message_name => ''
958 , p_message_text => x_qa_result_tbl(i).Problem_details);
959 ELSE
960 l_tasklist_result := c_result_type_FAILURE;
961 x_results.add_result(p_multi_mod_val_result_id => po_multi_mod_val_results_s.nextval
962 , p_multi_mod_request_id => p_doc_id
963 , p_validation_type => 'CONTRACT'
964 , p_exception_type => c_result_type_FAILURE
965 , p_document_number => l_clm_doc_num
966 , p_message_application => 'OKC'
967 , p_message_name => ''
968 , p_message_text => x_qa_result_tbl(i).Problem_details);
969 END IF;
970 END LOOP;
971 END IF;
972 l_progress := 35;
973
974 END IF;
975 ELSE
976 l_tasklist_result := c_result_type_SUCCESS;
977 END IF;
978
979 IF (l_tasklist_result = c_result_type_FAILURE) THEN
980 x_result_type := c_result_type_FAILURE;
981 RETURN;
982 END IF;
983
984 PO_CLOSEOUT_PVT.open_mod_exists(p_doc_id => p_doc_id
985 ,x_result_type => l_result);
986
987 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'open_mod_exists result :' || l_result);
988
989 l_progress := 40;
990
991 IF l_result = c_result_type_FAILURE THEN
992 x_results.add_result(p_multi_mod_val_result_id => po_multi_mod_val_results_s.nextval
993 , p_multi_mod_request_id => p_doc_id
994 , p_validation_type => 'MODIFICATION'
995 , p_exception_type => l_open_mod_fail_type
996 , p_document_number => l_clm_doc_num
997 , p_message_application => 'PO'
998 , p_message_name => 'MANAGE_CLOSEOUT_OPEN_MOD');
999
1000 END IF;
1001
1002 l_progress := 45;
1003
1004 IF l_type_lookup_code = 'STANDARD' THEN
1005 IF (l_result = c_result_type_FAILURE AND
1006 l_open_mod_fail_type = c_result_type_FAILURE) THEN
1007 x_result_type := c_result_type_FAILURE;
1008 RETURN;
1009 END IF;
1010
1011 l_progress := 50;
1012
1013 PO_CLOSEOUT_PVT.is_doc_fully_received(p_doc_id => p_doc_id
1014 ,x_result_type => l_result);
1015
1016 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'is_doc_fully_received result :' || l_result);
1017
1018 l_progress := 55;
1019
1020 IF l_result = c_result_type_FAILURE THEN
1021 x_results.add_result(p_multi_mod_val_result_id => po_multi_mod_val_results_s.nextval
1022 , p_multi_mod_request_id => p_doc_id
1023 , p_validation_type => 'RECEIPTS'
1024 , p_exception_type => c_result_type_WARNING
1025 , p_document_number => l_clm_doc_num
1026 , p_message_application => 'PO'
1027 , p_message_name => 'MANAGE_CLOSEOUT_FULLY_RECEIVED');
1028
1029 x_result_type := c_result_type_WARNING;
1030 END IF;
1031
1032 l_progress := 60;
1033
1034 PO_CLOSEOUT_PVT.is_doc_fully_invoiced(p_doc_id => p_doc_id
1035 ,x_result_type => l_result);
1036
1037 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'is_doc_fully_invoiced result :' || l_result);
1038
1039 l_progress := 70;
1040
1041 IF l_result = c_result_type_FAILURE THEN
1042 x_results.add_result(p_multi_mod_val_result_id => po_multi_mod_val_results_s.nextval
1043 , p_multi_mod_request_id => p_doc_id
1044 , p_validation_type => 'INVOICE'
1045 , p_exception_type => c_result_type_WARNING
1046 , p_document_number => l_clm_doc_num
1047 , p_message_application => 'PO'
1048 , p_message_name => 'MANAGE_CLOSEOUT_FULLY_INVOICED');
1049
1050 x_result_type := c_result_type_WARNING;
1051 END IF;
1052
1053 l_progress := 80;
1054
1055 PO_CLOSEOUT_PVT.is_doc_fully_paid(p_doc_id => p_doc_id
1056 ,x_result_type => l_result);
1057
1058 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'is_doc_fully_paid result :' || l_result);
1059
1060 l_progress := 90;
1061
1062 IF l_result = c_result_type_FAILURE THEN
1063 x_results.add_result(p_multi_mod_val_result_id => po_multi_mod_val_results_s.nextval
1064 , p_multi_mod_request_id => p_doc_id
1065 , p_validation_type => 'PAYMENT'
1066 , p_exception_type => c_result_type_WARNING
1067 , p_document_number => l_clm_doc_num
1068 , p_message_application => 'PO'
1069 , p_message_name => 'MANAGE_CLOSEOUT_FULLY_PAID');
1070
1071 x_result_type := c_result_type_WARNING;
1072 END IF;
1073 END IF;
1074
1075 l_progress := 100;
1076
1077 IF (PO_LOG.d_proc) THEN PO_LOG.proc_end(d_module); END IF;
1078
1079 EXCEPTION
1080 WHEN OTHERS THEN
1081
1082 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'validate_manage_closeout : when others exception at '
1083 || l_progress || ';' || SQLERRM );
1084
1085 END validate_manage_closeout;
1086
1087 --------------------------------------------------------------------------------
1088 --Start of Comments
1089
1090 --Name: check_exceptions
1091
1092 --Function:
1093 -- Validate the Mass Closeout documents
1094
1095 --Parameters:
1096
1097 --IN:
1098 -- p_doc_id NUMBER
1099
1100 --OUT:
1101 --x_result_type VARCHAR2
1102
1103 --End of Comments
1104 --------------------------------------------------------------------------------
1105
1106 PROCEDURE check_exceptions (
1107 p_req_id IN NUMBER
1108 , p_chk_list_req IN VARCHAR2
1109 , x_result_type OUT NOCOPY VARCHAR2
1110 )
1111 IS
1112
1113 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || '.' || 'check_exceptions';
1114 l_progress NUMBER := 0;
1115 l_doc_id NUMBER;
1116 l_result_type VARCHAR2(20);
1117 l_results PO_MULTI_MOD_VAL_RESULTS_TYPE;
1118 l_fail_count NUMBER := 0;
1119 l_warn_count NUMBER := 0;
1120 l_validation_status VARCHAR2(20);
1121
1122 CURSOR closeoutDocs IS
1123 SELECT document_id
1124 FROM po_multi_mod_docs
1125 WHERE MULTI_MOD_REQUEST_ID = p_req_id;
1126
1127
1128 BEGIN
1129 IF (PO_LOG.d_proc) THEN PO_LOG.proc_begin(d_module); END IF;
1130 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'p_req_id ' || p_req_id);
1131 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'p_chk_list_req ' || p_chk_list_req);
1132
1133 l_results := PO_MULTI_MOD_VAL_RESULTS_TYPE.NEW_INSTANCE();
1134 l_progress := 10;
1135
1136 DELETE
1137 FROM po_multi_mod_val_results
1138 WHERE multi_mod_request_id = p_req_id;
1139
1140 l_progress := 20;
1141
1142 OPEN closeoutDocs;
1143 LOOP
1144 FETCH closeoutDocs INTO l_doc_id;
1145 EXIT WHEN closeoutDocs%NOTFOUND;
1146
1147 l_progress := 30;
1148 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'l_doc_id ' || l_doc_id);
1149
1150 PO_CLOSEOUT_PVT.validate_manage_closeout(p_doc_id => l_doc_id
1151 , p_chk_list_req => p_chk_list_req
1152 , x_result_type => l_result_type
1153 , x_results => l_results);
1154 l_progress := 40;
1155
1156 IF (l_result_type = c_result_type_FAILURE) THEN
1157 l_fail_count := l_fail_count + 1;
1158 ELSIF (l_result_type = c_result_type_WARNING) THEN
1159 l_warn_count := l_warn_count + 1;
1160 END IF;
1161
1162 l_progress := 50;
1163
1164 FOR i IN l_results.MESSAGE_NAME.FIRST .. l_results.MESSAGE_NAME.LAST
1165 LOOP
1166 INSERT INTO po_multi_mod_val_results
1167 ( MULTI_MOD_VAL_RESULT_ID
1168 ,MULTI_MOD_REQUEST_ID
1169 ,MULTI_MOD_DOC_ID
1170 ,VALIDATION_TYPE
1171 ,EXCEPTION_TYPE
1172 ,RESULT_TYPE
1173 ,DOCUMENT_ID
1174 ,DOCUMENT_NUMBER
1175 ,RELATED_DOCUMENT_ID
1176 ,RELATED_DOCUMENT_NUMBER
1177 ,RELATED_DOCUMENT_DATE
1178 ,RELATED_DOCUMENT_AMOUNT
1179 ,MESSAGE_APPLICATION
1180 ,MESSAGE_NAME
1181 ,TOKEN1_NAME
1182 ,TOKEN1_VALUE
1183 ,TOKEN2_NAME
1184 ,TOKEN2_VALUE
1185 ,TOKEN3_NAME
1186 ,TOKEN3_VALUE
1187 ,TOKEN4_NAME
1188 ,TOKEN4_VALUE
1189 ,TOKEN5_NAME
1190 ,TOKEN5_VALUE
1191 ,TOKEN6_NAME
1192 ,TOKEN6_VALUE
1193 ,LAST_UPDATE_DATE
1194 ,LAST_UPDATED_BY
1195 ,LAST_UPDATE_LOGIN
1196 ,CREATION_DATE
1197 ,CREATED_BY
1198 ,REQUEST_ID
1199 )
1200 VALUES
1201 ( po_multi_mod_val_results_s.nextval
1202 , p_req_id
1203 , l_doc_id
1204 , l_results.validation_type(i)
1205 , l_results.exception_type(i)
1206 , l_results.result_type(i)
1207 , l_doc_id
1208 , l_results.document_number(i)
1209 , l_results.related_document_id(i)
1210 , l_results.related_document_number(i)
1211 , l_results.related_document_date(i)
1212 , l_results.related_document_amount(i)
1213 , l_results.message_application(i)
1214 , l_results.message_name(i)
1215 , l_results.token1_name(i)
1216 , l_results.token1_value(i)
1217 , l_results.token2_name(i)
1218 , l_results.token2_value(i)
1219 , l_results.token3_name(i)
1220 , l_results.token3_value(i)
1221 , l_results.token4_name(i)
1222 , l_results.token4_value(i)
1223 , l_results.token5_name(i)
1224 , l_results.token5_value(i)
1225 , l_results.token6_name(i)
1226 , l_results.token6_value(i)
1227 , sysdate
1228 , fnd_global.user_id
1229 , fnd_global.login_id
1230 , sysdate
1231 , fnd_global.user_id
1232 , fnd_global.conc_request_id
1233 );
1234 l_progress := 60;
1235 END LOOP;
1236
1237 END LOOP;
1238
1239 IF l_fail_count > 0 THEN
1240 l_validation_status := c_result_type_FAILURE;
1241 ELSIF l_warn_count > 0 THEN
1242 l_validation_status := c_result_type_WARNING;
1243 ELSE
1244 l_validation_status := c_result_type_SUCCESS;
1245 END IF;
1246
1247 l_progress := 70;
1248 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'l_validation_status ' || l_validation_status);
1249
1250 UPDATE po_multi_mod_requests
1251 SET VALIDATION_STATUS = l_validation_status
1252 WHERE MULTI_MOD_REQUEST_ID = p_req_id;
1253
1254 l_progress := 100;
1255
1256 IF (PO_LOG.d_proc) THEN PO_LOG.proc_end(d_module); END IF;
1257 EXCEPTION
1258 WHEN OTHERS THEN
1259
1260 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'check_exceptions : when others exception at '
1261 || l_progress || ';' || SQLERRM );
1262 x_result_type := c_result_type_FAILURE;
1263 END check_exceptions;
1264
1265 --------------------------------------------------------------------------------
1266 --Start of Comments
1267
1268 --Name: mass_closeout_submit
1269
1270 --Function:
1271 -- Process Mass Closeout action.
1272
1273 --Parameters:
1274
1275 --IN:
1276 -- p_doc_id NUMBER
1277
1278 --OUT:
1279 -- errbuf VARCHAR2
1280 -- retcode VARCHAR2
1281
1282 --End of Comments
1283 --------------------------------------------------------------------------------
1284
1285 PROCEDURE mass_closeout_submit (
1286 errbuf OUT NOCOPY VARCHAR2
1287 , retcode OUT NOCOPY VARCHAR2
1288 , p_req_id IN NUMBER
1289 )
1290 IS
1291 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || '.' || 'mass_closeout_submit';
1292 l_progress NUMBER := 0;
1293 l_doc_id NUMBER;
1294 l_po_doc_type VARCHAR2(25);
1295 l_contr_type VARCHAR2(25);
1296 l_rev_number NUMBER;
1297 l_doc_num VARCHAR2(100);
1298 l_org_id NUMBER;
1299 l_vendor_id NUMBER;
1300 l_vendor_name VARCHAR2(100);
1301 l_vendor_site_id NUMBER;
1302 l_vendor_contact_id NUMBER;
1303 l_vendor_contact_name VARCHAR2(100);
1304 l_org_name VARCHAR2(100);
1305 l_src_document_rec OKC_IMP_RECORD_TYPES.document_rec_type;
1306 l_tgt_document_rec OKC_IMP_RECORD_TYPES.document_rec_type;
1307 l_contract_rec OKC_IMP_RECORD_TYPES.contract_rec_type;
1308 l_contract_parties OKC_IMP_RECORD_TYPES.contract_parties_tbl_type;
1309 l_risks_tbl OKC_IMP_RECORD_TYPES.contract_risks_tbl_type;
1310 l_party_contacts OKC_IMP_RECORD_TYPES.party_contacts_tbl_type;
1311 l_return_status VARCHAR2(25);
1312 l_msg_count NUMBER;
1313 l_closeout_doc_count NUMBER;
1314 l_checklist_req VARCHAR2(25);
1315 l_result_type VARCHAR2(500);
1316 l_msg_data VARCHAR2(2000);
1317 l_doc_contr_type VARCHAR2(50);
1318
1319 CURSOR closeoutDocs IS
1320 SELECT document_id
1321 FROM po_multi_mod_docs
1322 WHERE MULTI_MOD_REQUEST_ID = p_req_id;
1323 BEGIN
1324 IF (PO_LOG.d_proc) THEN PO_LOG.proc_begin(d_module); END IF;
1325 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'p_req_id ' || p_req_id);
1326 fnd_file.put_line(FND_FILE.LOG,'Current Concurrent Request Id : '||FND_GLOBAL.CONC_REQUEST_ID);
1327 fnd_file.put_line(FND_FILE.LOG,'Parameters ');
1328 fnd_file.put_line(FND_FILE.LOG,'Closeout RequestSet Id: '|| p_req_id);
1329 fnd_file.put_line(FND_FILE.LOG,'--------------------------------------------------------------');
1330
1331 SELECT check_list_required
1332 INTO l_checklist_req
1333 FROM po_closeout_details
1334 WHERE reference_doc_id = p_req_id
1335 AND closeout_doc_type = PO_CLOSEOUT_PVT.g_CLOSEOUT_TYPE_MASS;
1336
1337 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'l_checklist_req ' || l_checklist_req);
1338 l_progress := 5;
1339
1340 OPEN closeoutDocs;
1341 LOOP
1342 FETCH closeoutDocs INTO l_doc_id;
1343 EXIT WHEN closeoutDocs%NOTFOUND;
1344
1345 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'l_doc_id ' || l_doc_id);
1346
1347 SELECT type_lookup_code,revision_num,clm_document_number,org_id,vendor_id,vendor_contact_id,vendor_site_id
1348 INTO l_po_doc_type, l_rev_number, l_doc_num,l_org_id,l_vendor_id,l_vendor_contact_id,l_vendor_site_id
1349 FROM PO_HEADERS_ALL
1350 WHERE po_header_id = l_doc_id;
1351
1352 l_progress := 5;
1353 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'l_po_doc_type ' || l_po_doc_type);
1354 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'l_rev_number ' || l_rev_number);
1355 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'l_doc_num ' || l_doc_num);
1356 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'l_org_id ' || l_org_id);
1357 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'l_vendor_id ' || l_vendor_id);
1358 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'l_vendor_contact_id ' || l_vendor_contact_id);
1359 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'l_vendor_site_id ' || l_vendor_site_id);
1360
1361 fnd_file.put_line(FND_FILE.LOG, 'Processing started for the document: ' || l_doc_num);
1362 SELECT Count(reference_doc_id)
1363 INTO l_closeout_doc_count
1364 FROM PO_CLOSEOUT_DETAILS
1365 WHERE reference_doc_id = l_doc_id
1366 AND closeout_doc_type = PO_CLOSEOUT_PVT.g_CLOSEOUT_TYPE_INDIVIDUAL;
1367
1368 l_progress := 10;
1369 IF l_closeout_doc_count > 0 THEN
1370 retcode := '1';
1371 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'Closeout process has already been initiated for this award: ' || l_doc_id);
1372 fnd_file.put_line(FND_FILE.LOG, 'Closeout process has already been initiated for this award: ' || l_doc_num);
1373
1374 ELSE
1375 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'validation is successful for award ' || l_doc_id);
1376 fnd_file.put_line(FND_FILE.LOG, 'Initiating closeout process for this award ');
1377
1378 BEGIN
1379 INSERT
1380 INTO PO_CLOSEOUT_DETAILS (
1381 closeout_id,
1382 closeout_doc_type,
1383 reference_doc_id,
1384 check_list_required,
1385 ccs_required,
1386 check_list_sign_required,
1387 ccs_sign_required,
1388 check_list_signer_id,
1389 aco_signature_type,
1390 aco_signer_id,
1391 pco_signature_type,
1392 pco_signer_id,
1393 rentention_period_uom,
1394 rentention_period,
1395 staging_period_uom,
1396 staging_period,
1397 aco_remarks,
1398 created_by,
1399 creation_date,
1400 last_updated_by,
1401 last_update_date,
1402 last_update_login,
1403 attribute_category,
1404 attribute1,
1405 attribute2,
1406 attribute3,
1407 attribute4,
1408 attribute5,
1409 attribute6,
1410 attribute7,
1411 attribute8,
1412 attribute9,
1413 attribute10,
1414 attribute11,
1415 attribute12,
1416 attribute13,
1417 attribute14,
1418 attribute15,
1419 incloseout,
1420 closeout_date,
1421 pco_remarks,
1422 aco_signer_name,
1423 pco_signer_name,
1424 aco_signed_date,
1425 pco_signed_date,
1426 checklist_signed_date,
1427 is_reopened)
1428 (SELECT PO_CLOSEOUT_DETAILS_S.NEXTVAL,
1429 PO_CLOSEOUT_PVT.g_CLOSEOUT_TYPE_INDIVIDUAL,
1430 l_doc_id,
1431 check_list_required,
1432 ccs_required,
1433 check_list_sign_required,
1434 ccs_sign_required,
1435 check_list_signer_id,
1436 aco_signature_type,
1437 aco_signer_id,
1438 pco_signature_type,
1439 pco_signer_id,
1440 rentention_period_uom,
1441 rentention_period,
1442 staging_period_uom,
1443 staging_period,
1444 aco_remarks,
1445 fnd_global.USER_ID,
1446 SYSDATE,
1447 fnd_global.USER_ID,
1448 SYSDATE,
1449 fnd_global.LOGIN_ID,
1450 attribute_category,
1451 attribute1,
1452 attribute2,
1453 attribute3,
1454 attribute4,
1455 attribute5,
1456 attribute6,
1457 attribute7,
1458 attribute8,
1459 attribute9,
1460 attribute10,
1461 attribute11,
1462 attribute12,
1463 attribute13,
1464 attribute14,
1465 attribute15,
1466 'Y',
1467 closeout_date,
1468 pco_remarks,
1469 aco_signer_name,
1470 pco_signer_name,
1471 aco_signed_date,
1472 pco_signed_date,
1473 checklist_signed_date,
1474 is_reopened
1475 FROM PO_CLOSEOUT_DETAILS
1476 WHERE reference_doc_id = p_req_id
1477 AND closeout_doc_type = PO_CLOSEOUT_PVT.g_CLOSEOUT_TYPE_MASS);
1478
1479 l_progress := 20;
1480 fnd_file.put_line(FND_FILE.LOG, 'New record has been added to the PO_CLOSEOUT_DETAILS table for this award.');
1481
1482
1483 IF l_checklist_req = 'Y' THEN
1484 fnd_file.put_line(FND_FILE.LOG, 'Contract creation process has been started for the award ' || l_doc_num);
1485 SELECT name
1486 INTO l_org_name
1487 FROM hr_all_organization_units_tl
1488 WHERE organization_id = l_org_id
1489 AND language = UserEnv('lang');
1490
1491 l_progress := 30;
1492 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'l_org_name ' || l_org_name);
1493
1494 l_doc_contr_type := po_conterms_utl_grp.get_po_contract_doctype(l_po_doc_type);
1495
1496 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'l_doc_contr_type ' || l_doc_contr_type);
1497
1498 l_tgt_document_rec.document_number := l_doc_num;
1499 l_tgt_document_rec.business_document_type := l_doc_contr_type;
1500 l_tgt_document_rec.business_document_id := l_doc_id;
1501 l_tgt_document_rec.source_code := 'INTERNAL';
1502 l_tgt_document_rec.relationship_id := 10006;
1503 l_tgt_document_rec.display_in_contract := 'N';
1504
1505 l_progress := 40;
1506
1507 --l_contr_type := po_conterms_utl_grp.get_po_contract_doctype(l_po_doc_type);
1508 l_contr_type := 'PO_CLOSEOUT_SET';
1509 l_src_document_rec.business_document_type := l_contr_type;
1510 l_src_document_rec.business_document_id := p_req_id;
1511 l_src_document_rec.source_code := 'INTERNAL';
1512 l_src_document_rec.relationship_id := 10006;
1513 l_src_document_rec.display_in_contract := 'N';
1514
1515 l_progress := 50;
1516
1517 l_contract_rec.contract_number := l_doc_num;
1518 l_contract_rec.contract_name := l_doc_num;
1519 l_contract_rec.org_name := l_org_name;
1520 l_contract_rec.contract_status_txt := 'Draft';
1521 l_contract_rec.contract_effective_date := TO_CHAR(SYSDATE, 'MM/DD/YYYY');
1522 l_contract_rec.contract_type_txt := 'Contract Closeout';
1523 l_contract_rec.contract_version_num := 1 ;
1524 l_contract_rec.description := 'Closeout :' || l_doc_num;
1525 l_contract_rec.authoring_party_txt := 'Internal' ;
1526 l_contract_rec.owner_user_name := fnd_global.USER_NAME ;
1527 l_contract_rec.source_code := 'INTERNAL' ;
1528
1529 l_progress := 60;
1530
1531 SELECT pv.vendor_name
1532 INTO l_vendor_name
1533 FROM po_vendors pv
1534 WHERE pv.vendor_id = l_vendor_id;
1535
1536 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'l_vendor_name ' || l_vendor_name);
1537 l_progress := 65;
1538
1539 l_contract_parties(1).party_role_txt := 'Internal';
1540 l_contract_parties(1).party_name_txt := l_org_name;
1541 l_contract_parties(1).party_index := 1;
1542
1543 l_contract_parties(2).party_role_txt := 'Supplier';
1544 l_contract_parties(2).party_name_txt := l_vendor_name;
1545 l_contract_parties(2).party_index := 2;
1546
1547 l_progress := 70;
1548
1549 IF l_vendor_contact_id IS NOT NULL THEN
1550
1551 BEGIN
1552 SELECT Nvl(LAST_NAME, '') || ', ' || Nvl(FIRST_NAME, '')
1553 INTO l_vendor_contact_name
1554 FROM po_vendor_contacts
1555 WHERE vendor_contact_id = l_vendor_contact_id
1556 AND vendor_site_id= l_vendor_site_id;
1557
1558 EXCEPTION
1559 WHEN No_Data_Found THEN
1560 l_vendor_contact_name := '';
1561 END;
1562
1563 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'l_vendor_contact_name ' || l_vendor_contact_name);
1564 --contacts
1565 l_party_contacts(1).contact_index := 1;
1566 l_party_contacts(1).party_role_txt := 'Supplier';
1567 l_party_contacts(1).party_name_txt := l_vendor_name;
1568 l_party_contacts(1).contact_name_txt := l_vendor_contact_name;
1569 l_party_contacts(1).contact_role_txt := 'Contracting Officer';
1570 l_party_contacts(1).contact_id := l_vendor_contact_id;
1571
1572 END IF;
1573
1574 l_progress := 75;
1575 OKC_REP_CLOSEOUT_PVT.copy_set_tasks_to_each_po(p_api_version => 1.0
1576 , p_init_msg_list => fnd_api.g_false
1577 , p_commit => fnd_api.g_false
1578 , p_src_document_rec => l_src_document_rec
1579 , p_tgt_document_rec => l_tgt_document_rec
1580 , p_contract_rec => l_contract_rec
1581 , p_contract_parties_tbl => l_contract_parties
1582 , p_party_contacts_tbl => l_party_contacts
1583 , p_risks_tbl => l_risks_tbl
1584 , x_msg_data => l_result_type
1585 , x_msg_count => l_msg_count
1586 , x_return_status => l_return_status);
1587 l_progress := 80;
1588 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'contract_id ' || l_contract_rec.contract_id);
1589 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'contract_ver ' || l_contract_rec.contract_version_num);
1590 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'contract_type ' || l_contract_rec.contract_type_txt);
1591 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'l_return_status ' || l_return_status);
1592 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
1593 fnd_file.put_line(FND_FILE.LOG, 'Contract creation is successful. Contract id is ' || l_contract_rec.contract_id);
1594 ELSE
1595 fnd_file.put_line(FND_FILE.LOG, 'There are warnings/Errors while creating the contract.');
1596 fnd_file.put_line(FND_FILE.LOG, 'Message:' || l_result_type);
1597 END IF;
1598 END IF; --Checklist required
1599
1600 fnd_file.put_line(FND_FILE.LOG, 'Workflow process has been intiated');
1601 PO_CLOSEOUT_PVT.start_closeout_wf_process(p_doc_id => l_doc_id,
1602 x_return_status => l_return_status);
1603
1604 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
1605 fnd_file.put_line(FND_FILE.LOG, 'Workflow is successfully initiated');
1606 COMMIT;
1607 ELSE
1608 fnd_file.put_line(FND_FILE.LOG, 'Error(s) occured while initiating the workflow');
1609 END IF;
1610 EXCEPTION
1611 WHEN OTHERS THEN
1612 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'Exception occured while processing the award '
1613 || l_doc_id || ' - ' || SQLERRM );
1614 fnd_file.put_line(FND_FILE.LOG, 'Fatal error occurred while processing the award ' || l_doc_num);
1615 END;
1616 l_progress := 90;
1617 END IF;
1618 END LOOP;
1619
1620 po_multi_mod_pvt.remove_multi_mod_request(p_multi_mod_request_id => p_req_id
1621 , x_return_status => l_return_status);
1622 fnd_file.put_line(FND_FILE.LOG, 'The multimod request ' || p_req_id || ' details have been cleared from the system');
1623 PO_CLOSEOUT_PVT.delete_contract(p_doc_id => p_req_id,
1624 p_closeout_type => PO_CLOSEOUT_PVT.g_CLOSEOUT_TYPE_MASS,
1625 x_return_status => l_return_status,
1626 x_return_msg => l_msg_data);
1627 l_progress := 95;
1628 fnd_file.put_line(FND_FILE.LOG, 'Deleting the contract which is created for closeout set ');
1629 fnd_file.put_line(FND_FILE.LOG, 'Delete contract status ' || l_return_status);
1630 fnd_file.put_line(FND_FILE.LOG, 'return message from Delete contract : ' || l_msg_data);
1631 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'Delete contract status ' || l_return_status);
1632 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'return message from Delete contract : ' || l_msg_data);
1633 l_progress := 100;
1634 IF (PO_LOG.d_proc) THEN PO_LOG.proc_end(d_module); END IF;
1635 EXCEPTION
1636 WHEN OTHERS THEN
1637 errbuf := sqlerrm;
1638 retcode := '2';
1639 fnd_file.put_line(FND_FILE.LOG, 'Unexpected error occurred during this process');
1640 fnd_file.put_line(FND_FILE.LOG, SQLERRM);
1641 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'mass_closeout_submit : when others exception at '
1642 || l_progress || ';' || SQLERRM );
1643 END mass_closeout_submit;
1644
1645 --------------------------------------------------------------------------------
1646 --Start of Comments
1647
1648 --Name: mass_closeout_validate_set
1649
1650 --Function:
1651 -- Validates the selected documents.
1652
1653 --Parameters:
1654
1655 --IN:
1656 -- p_selected_docs NUMBER
1657
1658 --OUT:
1659 -- x_failed_docs VARCHAR2
1660 -- x_result_type VARCHAR2
1661
1662 --End of Comments
1663 --------------------------------------------------------------------------------
1664
1665 PROCEDURE mass_closeout_validate_set (
1666 p_selected_docs IN PO_TBL_NUMBER
1667 , x_failed_docs OUT NOCOPY VARCHAR2
1668 , x_result_type OUT NOCOPY VARCHAR2
1669 )
1670 AS
1671 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || '.' || 'mass_closeout_validate_set';
1672 l_progress NUMBER := 0;
1673 l_doc_ids VARCHAR2(2000);
1674 l_query VARCHAR2(2000);
1675
1676 BEGIN
1677 IF (PO_LOG.d_proc) THEN
1678 PO_LOG.proc_begin(d_module);
1679 END IF;
1680 --PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'p_selected_docs ' || p_selected_docs);
1681
1682 SELECT listagg(DOCUMENT_ID,',') within GROUP (ORDER BY DOCUMENT_ID)
1683 INTO l_doc_ids
1684 FROM po_multi_mod_docs docs,
1685 po_multi_mod_requests req
1686 WHERE docs.MULTI_MOD_REQUEST_ID = req.MULTI_MOD_REQUEST_ID
1687 AND req.MULTI_MOD_REQUEST_TYPE = 'INITIATE_CLOSEOUT'
1688 AND docs.DOCUMENT_ID IN (SELECT *
1689 FROM TABLE (p_selected_docs));
1690
1691 l_progress := 50;
1692 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'l_doc_ids ' || l_doc_ids);
1693 IF l_doc_ids IS NULL THEN
1694 x_result_type := c_result_type_SUCCESS;
1695 ELSE
1696 x_result_type := c_result_type_FAILURE;
1697 l_query := 'SELECT listagg(clm_document_number,'','') within GROUP (ORDER BY clm_document_number)
1698 FROM po_headers_all
1699 WHERE po_header_id IN (' || l_doc_ids || ')';
1700 EXECUTE IMMEDIATE l_query INTO x_failed_docs;
1701 END IF;
1702
1703 l_progress := 100;
1704 IF (PO_LOG.d_proc) THEN PO_LOG.proc_end(d_module); END IF;
1705 EXCEPTION
1706 WHEN OTHERS THEN
1707
1708 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'mass_closeout_validate_set : when others exception at '
1709 || l_progress || ';' || SQLERRM );
1710 x_result_type := c_result_type_FAILURE;
1711 END mass_closeout_validate_set;
1712
1713 --------------------------------------------------------------------------------
1714 --Start of Comments
1715
1716 --Name: validate_mass_closeout
1717
1718 --Function:
1719 -- Validate the Mass Closeout action
1720
1721 --Parameters:
1722
1723 --IN:
1724 -- p_req_id NUMBER
1725 -- p_chk_list_req VARCHAR2
1726
1727 --OUT:
1728 -- errbuf VARCHAR2
1729 -- retcode VARCHAR2
1730
1731 --End of Comments
1732 --------------------------------------------------------------------------------
1733 PROCEDURE validate_mass_closeout (
1734 errbuf OUT NOCOPY VARCHAR2
1735 , retcode OUT NOCOPY VARCHAR2
1736 , p_req_id IN NUMBER
1737 , p_chk_list_req IN VARCHAR2
1738 ) IS
1739 l_progress NUMBER := 0;
1740 l_result VARCHAR2(50);
1741 l_tasklist_result VARCHAR2(50);
1742 l_open_mod_fail_type VARCHAR2(20);
1743 l_document_rec OKC_IMP_RECORD_TYPES.document_rec_type;
1744 l_contr_type VARCHAR2(50);
1745 l_type_lookup_code VARCHAR2(50);
1746 x_msg_data VARCHAR2(1000);
1747 x_msg_count NUMBER;
1748 x_return_status VARCHAR2(20);
1749 x_qa_return_status VARCHAR2(20);
1750 x_qa_result_tbl OKC_TERMS_QA_PVT.qa_result_tbl_type;
1751 l_clm_doc_num VARCHAR2(100);
1752 l_document_id_tbl PO_TBL_NUMBER;
1753 l_document_num_tbl PO_TBL_VARCHAR100;
1754 l_mod_doc_id_tbl PO_TBL_NUMBER;
1755 l_doc_id NUMBER;
1756 l_excep_type VARCHAR2(50);
1757 l_result_type VARCHAR2(100);
1758 l_fail_count NUMBER := 0;
1759 l_warn_count NUMBER := 0;
1760 l_validation_status VARCHAR2(20);
1761 l_module_status VARCHAR2(20);
1762 l_ref_col1 VARCHAR2(100);
1763 l_ref_col2 VARCHAR2(100);
1764 l_ref_col3 VARCHAR2(100);
1765 l_ref_col4 VARCHAR2(100);
1766 l_ref_col5 VARCHAR2(100);
1767
1768 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || '.' || 'validate_mass_closeout';
1769
1770 BEGIN
1771 IF (PO_LOG.d_proc) THEN
1772 PO_LOG.proc_begin(d_module);
1773 END IF;
1774
1775 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'p_req_id ' || p_req_id);
1776 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'p_chk_list_req ' || p_chk_list_req);
1777 fnd_file.put_line(FND_FILE.LOG,'Current Concurrent Request Id : '||FND_GLOBAL.CONC_REQUEST_ID);
1778 fnd_file.put_line(FND_FILE.LOG,'Parameters ');
1779 fnd_file.put_line(FND_FILE.LOG,'Closeout RequestSet Id: ' || p_req_id);
1780 fnd_file.put_line(FND_FILE.LOG,'Checklist Required: ' || p_chk_list_req);
1781 fnd_file.put_line(FND_FILE.LOG,'--------------------------------------------------------------');
1782
1783 DELETE
1784 FROM po_multi_mod_val_results
1785 WHERE multi_mod_request_id = p_req_id;
1786
1787 l_progress := 5;
1788 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'Num of Records deleted from po_multi_mod_val_results:' || SQL%ROWCOUNT);
1789 fnd_file.put_line(FND_FILE.LOG,'Existing validation records have been cleared');
1790
1791 IF p_chk_list_req = 'Y' THEN
1792 l_open_mod_fail_type := 'WARNING';
1793 ELSE
1794 l_open_mod_fail_type := 'ERROR';
1795 END IF;
1796
1797 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'l_open_mod_fail_type:' || l_open_mod_fail_type);
1798
1799 l_progress := 10;
1800
1801 IF p_chk_list_req = 'Y' THEN
1802
1803 SELECT reference_doc_id,null,null
1804 BULK COLLECT INTO l_document_id_tbl,l_document_num_tbl, l_mod_doc_id_tbl
1805 FROM po_closeout_details
1806 WHERE closeout_doc_type = 'MASS'
1807 AND reference_doc_id = p_req_id
1808 AND NOT EXISTS (SELECT 'Tasks not available'
1809 FROM okc_rep_contract_usages orcu, okc_deliverables del
1810 WHERE orcu.business_document_type = 'PO_CLOSEOUT_SET'
1811 AND orcu.business_document_id = p_req_id
1812 AND del.business_document_type = orcu.contract_type
1813 AND del.business_document_id = orcu.contract_id
1814 AND del.business_document_version = -99
1815 );
1816
1817 l_progress := 15;
1818
1819 IF l_document_num_tbl.Count() > 0 THEN
1820 FOR i IN 1..l_document_num_tbl.Count() LOOP
1821 PO_CLOSEOUT_PVT.store_validations(p_doc_id => l_document_id_tbl(i)
1822 , p_doc_num => l_document_num_tbl(i)
1823 , p_mod_doc_id => l_mod_doc_id_tbl(i)
1824 , p_req_id => p_req_id
1825 , p_validation_type => 'CHECKLIST'
1826 , p_exception_type => c_result_type_FAILURE
1827 , p_message_app => 'PO'
1828 , p_message_name => 'CHKLIST_TASK_NOT_AVAILABLE');
1829 l_fail_count := l_fail_count + 1;
1830 l_module_status := 'with errors';
1831 END LOOP;
1832 END IF;
1833 fnd_file.put_line(FND_FILE.LOG,'Checklist tasks validation is completed ' || l_module_status);
1834
1835 IF l_fail_count = 0 THEN
1836
1837 l_progress := 20;
1838
1839 l_document_rec.business_document_type := 'PO_CLOSEOUT_SET';
1840 l_document_rec.business_document_id := p_req_id;
1841
1842 OKC_REP_CLOSEOUT_PVT.validate_closeout_tasks(
1843 p_api_version => 1.0,
1844 p_init_msg_list => fnd_api.g_false,
1845 p_commit => fnd_api.g_true,
1846 p_document_rec => l_document_rec,
1847 x_msg_data => x_msg_data,
1848 x_msg_count => x_msg_count,
1849 x_return_status => x_return_status,
1850 x_qa_return_status => x_qa_return_status,
1851 x_qa_result_tbl => x_qa_result_tbl);
1852 l_progress := 30;
1853
1854 l_module_status := '';
1855 IF x_qa_result_tbl.Count() > 0 THEN
1856 FOR i IN x_qa_result_tbl.FIRST .. x_qa_result_tbl.LAST
1857 LOOP
1858 IF (x_qa_result_tbl(i).error_severity_name = OKC_REP_CLOSEOUT_PVT.G_QA_STS_WARNING) THEN
1859 l_excep_type := c_result_type_WARNING;
1860 l_warn_count := l_warn_count + 1;
1861 SELECT Decode(l_module_status, 'with errors',
1862 'with errors',
1863 'with warnings')
1864 INTO l_module_status
1865 FROM dual;
1866 ELSE
1867 l_excep_type := c_result_type_FAILURE;
1868 l_fail_count := l_fail_count + 1;
1869 l_module_status := 'with errors';
1870 END IF;
1871 l_progress := 32;
1872 begin
1873 SELECT clm_document_number
1874 INTO l_clm_doc_num
1875 FROM po_headers_all
1876 WHERE po_header_id = x_qa_result_tbl(i).Document_id;
1877 EXCEPTION
1878 WHEN No_Data_Found THEN
1879 l_clm_doc_num := '';
1880 END;
1881
1882 l_ref_col1 := x_qa_result_tbl(i).Reference_Column1;
1883 l_ref_col2 := x_qa_result_tbl(i).Reference_Column2;
1884 l_ref_col3 := x_qa_result_tbl(i).Reference_Column3;
1885 l_ref_col4 := x_qa_result_tbl(i).Reference_Column4;
1886 l_ref_col5 := x_qa_result_tbl(i).Reference_Column5;
1887 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'header id from x_qa_result_tbl: ' || x_qa_result_tbl(i).Document_id);
1888 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'clm doc number for above header: ' || l_clm_doc_num);
1889 PO_CLOSEOUT_PVT.store_validations(p_doc_id => x_qa_result_tbl(i).Document_id
1890 , p_doc_num => l_clm_doc_num
1891 , p_mod_doc_id => NULL
1892 , p_req_id => p_req_id
1893 , p_validation_type => 'CONTRACT'
1894 , p_exception_type => l_excep_type
1895 , p_message_app => 'OKC'
1896 , p_message_name => x_qa_result_tbl(i).message_name
1897 , p_token_name1 => substr(l_ref_col1,1,INSTR(l_ref_col1,'~')-1)
1898 , p_token_value1 => substr(l_ref_col1,INSTR(l_ref_col1,'~')+1,LENGTH(l_ref_col1))
1899 , p_token_name2 => substr(l_ref_col2,1,INSTR(l_ref_col2,'~')-1)
1900 , p_token_value2 => substr(l_ref_col2,INSTR(l_ref_col2,'~')+1,LENGTH(l_ref_col2))
1901 , p_token_name3 => substr(l_ref_col3,1,INSTR(l_ref_col3,'~')-1)
1902 , p_token_value3 => substr(l_ref_col3,INSTR(l_ref_col3,'~')+1,LENGTH(l_ref_col3))
1903 , p_token_name4 => substr(l_ref_col4,1,INSTR(l_ref_col4,'~')-1)
1904 , p_token_value4 => substr(l_ref_col4,INSTR(l_ref_col4,'~')+1,LENGTH(l_ref_col4))
1905 , p_token_name5 => substr(l_ref_col5,1,INSTR(l_ref_col5,'~')-1)
1906 , p_token_value5 => substr(l_ref_col5,INSTR(l_ref_col5,'~')+1,LENGTH(l_ref_col5)));
1907
1908 END LOOP;
1909 END IF;
1910
1911 l_progress := 35;
1912
1913 END IF;
1914 END IF;
1915 fnd_file.put_line(FND_FILE.LOG,'Contract Deliverables validation is completed ' || l_module_status);
1916 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'Contract Deliverables validation is completed ' || l_module_status);
1917
1918
1919 l_document_id_tbl := NEW po_tbl_number();
1920 l_document_num_tbl := NEW po_tbl_varchar100();
1921 l_mod_doc_id_tbl := NEW po_tbl_number();
1922
1923 SELECT pha.po_header_id,pha.clm_document_number, pmmd.multi_mod_doc_id
1924 BULK COLLECT INTO l_document_id_tbl,l_document_num_tbl,l_mod_doc_id_tbl
1925 FROM po_multi_mod_docs pmmd,
1926 po_headers_all pha
1927 WHERE pmmd.multi_mod_request_id = p_req_id
1928 AND pmmd.document_id = pha.po_header_id
1929 AND EXISTS (SELECT 'Open Mod Exists'
1930 FROM po_drafts dft
1931 WHERE dft.document_id = pmmd.document_id
1932 AND dft.status IN ('DRAFT', 'IN PROCESS', 'REJECTED', 'SUPPLIER SIGN', 'PRE-APPROVED')
1933 AND dft.draft_type= 'MOD');
1934
1935 l_progress := 40;
1936
1937 l_module_status := '';
1938 IF l_document_num_tbl.Count() >0 THEN
1939 FOR i IN 1..l_document_num_tbl.Count() LOOP
1940 PO_CLOSEOUT_PVT.store_validations(p_doc_id => l_document_id_tbl(i)
1941 , p_doc_num => l_document_num_tbl(i)
1942 , p_mod_doc_id => l_mod_doc_id_tbl(i)
1943 , p_req_id => p_req_id
1944 , p_validation_type => 'MODIFICATION'
1945 , p_exception_type => l_open_mod_fail_type
1946 , p_message_app => 'PO'
1947 , p_message_name => 'MANAGE_CLOSEOUT_OPEN_MOD');
1948 IF p_chk_list_req = 'Y' THEN
1949 l_warn_count := l_warn_count + 1;
1950 l_module_status := 'with warnings';
1951 ELSE
1952 l_fail_count := l_fail_count + 1;
1953 l_module_status := 'with errors';
1954 END IF;
1955 END LOOP;
1956 END IF;
1957
1958 fnd_file.put_line(FND_FILE.LOG,'Open modifications validation is completed ' || l_module_status);
1959 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'Open modifications validation is completed ' || l_module_status);
1960 l_progress := 45;
1961
1962 l_document_id_tbl := NEW po_tbl_number();
1963 l_document_num_tbl := NEW po_tbl_varchar100();
1964 l_mod_doc_id_tbl := NEW po_tbl_number();
1965
1966 SELECT DISTINCT pha.po_header_id,pha.clm_document_number,pmmd.multi_mod_doc_id
1967 BULK COLLECT INTO l_document_id_tbl,l_document_num_tbl,l_mod_doc_id_tbl
1968 FROM po_multi_mod_docs pmmd,
1969 po_headers_all pha
1970 WHERE pmmd.multi_mod_request_id = p_req_id
1971 AND pmmd.document_id = pha.po_header_id
1972 AND pha.type_lookup_code = 'STANDARD'
1973 AND EXISTS ( SELECT 1
1974 FROM po_line_locations_all poll
1975 WHERE pha.po_header_id = poll.po_header_id
1976 AND Nvl(poll.closed_code,'OPEN') = 'OPEN'
1977 AND Nvl(poll.cancel_flag, 'N') = 'N');
1978
1979 l_progress := 55;
1980
1981 l_module_status := '';
1982 IF l_document_num_tbl.Count() >0 THEN
1983 FOR i IN 1..l_document_num_tbl.Count() LOOP
1984 PO_CLOSEOUT_PVT.store_validations(p_doc_id => l_document_id_tbl(i)
1985 , p_doc_num => l_document_num_tbl(i)
1986 , p_mod_doc_id => l_mod_doc_id_tbl(i)
1987 , p_req_id => p_req_id
1988 , p_validation_type => 'RECEIPTS'
1989 , p_exception_type => c_result_type_WARNING
1990 , p_message_app => 'PO'
1991 , p_message_name => 'MANAGE_CLOSEOUT_FULLY_RECEIVED');
1992 l_warn_count := l_warn_count + 1;
1993 l_module_status := 'with warnings';
1994 END LOOP;
1995 END IF;
1996
1997 fnd_file.put_line(FND_FILE.LOG,'Fully received validation is completed ' || l_module_status);
1998 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'Fully received validation is completed ' || l_module_status);
1999
2000 l_progress := 60;
2001
2002 l_document_id_tbl := NEW po_tbl_number();
2003 l_document_num_tbl := NEW po_tbl_varchar100();
2004 l_mod_doc_id_tbl := NEW po_tbl_number();
2005
2006 SELECT DISTINCT pha.po_header_id,pha.clm_document_number,pmmd.multi_mod_doc_id
2007 BULK COLLECT INTO l_document_id_tbl,l_document_num_tbl,l_mod_doc_id_tbl
2008 FROM po_multi_mod_docs pmmd,
2009 po_headers_all pha
2010 WHERE pmmd.multi_mod_request_id = p_req_id
2011 AND pmmd.document_id = pha.po_header_id
2012 AND pha.type_lookup_code = 'STANDARD'
2013 AND EXISTS (SELECT 1
2014 FROM po_line_locations_all poll
2015 WHERE pha.po_header_id = poll.po_header_id
2016 AND nvl(poll.closed_code,'OPEN') IN ('OPEN','CLOSED FOR RECEIVING',' CLOSED FOR INVOICE')
2017 );
2018
2019 l_progress := 70;
2020
2021 l_module_status := '';
2022 IF l_document_num_tbl.Count() >0 THEN
2023 FOR i IN 1..l_document_num_tbl.Count() LOOP
2024 PO_CLOSEOUT_PVT.store_validations(p_doc_id => l_document_id_tbl(i)
2025 , p_doc_num => l_document_num_tbl(i)
2026 , p_mod_doc_id => l_mod_doc_id_tbl(i)
2027 , p_req_id => p_req_id
2028 , p_validation_type => 'INVOICE'
2029 , p_exception_type => c_result_type_WARNING
2030 , p_message_app => 'PO'
2031 , p_message_name => 'MANAGE_CLOSEOUT_FULLY_INVOICED');
2032 l_warn_count := l_warn_count + 1;
2033 l_module_status := 'with warnings';
2034 END LOOP;
2035 END IF;
2036
2037 fnd_file.put_line(FND_FILE.LOG,'Fully invoiced validation is completed ' || l_module_status);
2038 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'Fully invoiced validation is completed ' || l_module_status);
2039 l_progress := 80;
2040
2041 l_document_id_tbl := NEW po_tbl_number();
2042 l_document_num_tbl := NEW po_tbl_varchar100();
2043 l_mod_doc_id_tbl := NEW po_tbl_number();
2044
2045 l_module_status := '';
2046 SELECT DISTINCT pha.po_header_id,pha.clm_document_number,pmmd.multi_mod_doc_id
2047 BULK COLLECT INTO l_document_id_tbl,l_document_num_tbl,l_mod_doc_id_tbl
2048 FROM po_headers_all pha,
2049 po_multi_mod_docs pmmd
2050 WHERE pmmd.multi_mod_request_id = p_req_id
2051 AND pmmd.document_id = pha.po_header_id
2052 AND pha.type_lookup_code = 'STANDARD'
2053 AND NOT EXISTS (SELECT 1
2054 FROM ap_invoice_distributions_all aid,
2055 ap_invoices_all aia,
2056 po_distributions_all pod,
2057 po_line_locations_all poll
2058 WHERE aid.po_distribution_id = pod.po_distribution_id
2059 AND aia.invoice_id = aid.invoice_id
2060 AND pod.po_header_id = poll.po_header_id
2061 AND pha.po_header_id = poll.po_header_id);
2062
2063 IF l_document_num_tbl.Count() >0 THEN
2064 FOR i IN 1..l_document_num_tbl.Count() LOOP
2065 PO_CLOSEOUT_PVT.store_validations(p_doc_id => l_document_id_tbl(i)
2066 , p_doc_num => l_document_num_tbl(i)
2067 , p_mod_doc_id => l_mod_doc_id_tbl(i)
2068 , p_req_id => p_req_id
2069 , p_validation_type => 'PAYMENT'
2070 , p_exception_type => c_result_type_WARNING
2071 , p_message_app => 'PO'
2072 , p_message_name => 'MANAGE_CLOSEOUT_FULLY_PAID');
2073 l_warn_count := l_warn_count + 1;
2074 l_module_status := 'with warnings';
2075 END LOOP;
2076 END IF;
2077
2078 l_progress := 85;
2079
2080 l_document_id_tbl := NEW po_tbl_number();
2081 l_document_num_tbl := NEW po_tbl_varchar100();
2082 l_mod_doc_id_tbl := NEW po_tbl_number();
2083
2084 SELECT DISTINCT pha.po_header_id,pha.clm_document_number,pmmd.multi_mod_doc_id
2085 BULK COLLECT INTO l_document_id_tbl,l_document_num_tbl,l_mod_doc_id_tbl
2086 FROM po_headers_all pha,
2087 po_multi_mod_docs pmmd
2088 WHERE pmmd.multi_mod_request_id = p_req_id
2089 AND pmmd.document_id = pha.po_header_id
2090 AND pha.type_lookup_code = 'STANDARD'
2091 AND EXISTS (SELECT 1
2092 FROM ap_invoice_distributions_all aid,
2093 ap_invoices_all aia,
2094 po_distributions_all pod,
2095 po_line_locations_all poll
2096 WHERE aid.po_distribution_id = pod.po_distribution_id
2097 AND aia.invoice_id = aid.invoice_id
2098 AND pod.po_header_id = poll.po_header_id
2099 AND pha.po_header_id = poll.po_header_id
2100 AND nvl(poll.closed_code,'OPEN') <> 'CLOSED FOR INVOICE'
2101 AND aia.payment_status_flag <> 'Y');
2102
2103 l_progress := 90;
2104
2105 IF l_document_num_tbl.Count() >0 THEN
2106 FOR i IN 1..l_document_num_tbl.Count() LOOP
2107 PO_CLOSEOUT_PVT.store_validations(p_doc_id => l_document_id_tbl(i)
2108 , p_doc_num => l_document_num_tbl(i)
2109 , p_mod_doc_id => l_mod_doc_id_tbl(i)
2110 , p_req_id => p_req_id
2111 , p_validation_type => 'FULLY_PAID'
2112 , p_exception_type => c_result_type_WARNING
2113 , p_message_app => 'PO'
2114 , p_message_name => 'MANAGE_CLOSEOUT_FULLY_PAID');
2115 l_warn_count := l_warn_count + 1;
2116 l_module_status := 'with warnings';
2117 END LOOP;
2118 END IF;
2119 fnd_file.put_line(FND_FILE.LOG,'Fully paid validation is completed ' || l_module_status);
2120 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'Fully paid validation is completed ' || l_module_status);
2121
2122 IF l_fail_count > 0 THEN
2123 l_validation_status := c_result_type_FAILURE;
2124 ELSIF l_warn_count > 0 THEN
2125 l_validation_status := c_result_type_WARNING;
2126 ELSE
2127 l_validation_status := c_result_type_SUCCESS;
2128 END IF;
2129
2130 l_progress := 95;
2131 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'l_validation_status ' || l_validation_status);
2132 fnd_file.put_line(FND_FILE.LOG,'Validation Status : ' || l_validation_status);
2133
2134 UPDATE po_multi_mod_requests
2135 SET VALIDATION_STATUS = l_validation_status
2136 WHERE MULTI_MOD_REQUEST_ID = p_req_id;
2137
2138 l_progress := 100;
2139
2140 IF (PO_LOG.d_proc) THEN PO_LOG.proc_end(d_module); END IF;
2141
2142 EXCEPTION
2143 WHEN OTHERS THEN
2144 errbuf := sqlerrm;
2145 retcode := '2';
2146 fnd_file.put_line(FND_FILE.LOG, 'Unexpected error occurred during this process');
2147 fnd_file.put_line(FND_FILE.LOG, SQLERRM);
2148 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'validate_manage_closeout : when others exception at '
2149 || l_progress || ';' || SQLERRM );
2150
2151 END validate_mass_closeout;
2152
2153 --------------------------------------------------------------------------------
2154 --Start of Comments
2155
2156 --Name: eligible_docs_for_closeout
2157
2158 --Function:
2159 -- Retrieve the eligible documents for closeout
2160
2161 --Parameters:
2162
2163 --OUT:
2164 -- errbuf VARCHAR2
2165 -- retcode VARCHAR2
2166
2167 --End of Comments
2168 --------------------------------------------------------------------------------
2169
2170 PROCEDURE eligible_docs_for_closeout (
2171 errbuf OUT NOCOPY VARCHAR2
2172 , retcode OUT NOCOPY VARCHAR2
2173 )
2174 AS
2175 l_progress NUMBER := 0;
2176 l_admin_id NUMBER;
2177 l_doc_num_tbl PO_TBL_VARCHAR100;
2178 l_comments_tbl PO_TBL_VARCHAR240;
2179 l_doc_type_tbl PO_TBL_VARCHAR25;
2180 l_doc_status_tbl PO_TBL_VARCHAR25;
2181 l_supplier_tbl PO_TBL_VARCHAR240;
2182 l_tot_amt_tbl PO_TBL_NUMBER;
2183 l_doc_curr_tbl PO_TBL_VARCHAR15;
2184 l_messages VARCHAR2(4000);
2185 p_orig_system VARCHAR2(100);
2186 l_username VARCHAR2(240);
2187 l_user_display_name VARCHAR2(50);
2188 l_notification_id NUMBER;
2189 l_document CLOB;
2190
2191
2192 CURSOR clmAwardAdmins IS
2193 SELECT DISTINCT clm_award_administrator
2194 FROM po_headers ph,
2195 po_doc_style_headers pdsh
2196 WHERE clm_award_administrator IS NOT NULL
2197 AND authorization_status = 'APPROVED'
2198 AND Nvl(clm_closeout_status, 'OPEN') = 'OPEN'
2199 AND ph.style_id = pdsh.style_id
2200 AND pdsh.clm_flag = 'Y';
2201
2202 /*CURSOR eligibleDocs (p_admin NUMBER) IS
2203 SELECT clm_document_number
2204 FROM po_headers
2205 WHERE clm_award_administrator = p_admin; */
2206
2207 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || '.' || 'eligible_docs_for_closeout';
2208
2209 BEGIN
2210 IF (PO_LOG.d_proc) THEN
2211 PO_LOG.proc_begin(d_module);
2212 END IF;
2213
2214 fnd_file.put_line(FND_FILE.LOG,'Current Concurrent Request Id : '||FND_GLOBAL.CONC_REQUEST_ID);
2215 p_orig_system:= 'PER';
2216 OPEN clmAwardAdmins;
2217 LOOP
2218 FETCH clmAwardAdmins INTO l_admin_id;
2219 EXIT WHEN clmAwardAdmins%NOTFOUND;
2220
2221 l_doc_num_tbl := NEW po_tbl_varchar100();
2222 l_comments_tbl := NEW PO_TBL_VARCHAR240();
2223 l_doc_type_tbl := NEW PO_TBL_VARCHAR25();
2224 l_doc_status_tbl := NEW PO_TBL_VARCHAR25();
2225 l_supplier_tbl := NEW PO_TBL_VARCHAR240();
2226 l_tot_amt_tbl := NEW PO_TBL_NUMBER();
2227 l_doc_curr_tbl := NEW PO_TBL_VARCHAR15();
2228
2229 l_messages := ' ';
2230 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'processing started for admin ' || l_admin_id);
2231 WF_DIRECTORY.GetUserName(p_orig_system, l_admin_id, l_username, l_user_display_name);
2232 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'l_username ' || l_username);
2233 fnd_file.put_line(FND_FILE.LOG,'Filtering documents for administrator ' || l_user_display_name);
2234 SELECT clm_document_number, comments,doc_type,status,supplier,totAmt,currency_code
2235 BULK COLLECT INTO l_doc_num_tbl,l_comments_tbl,l_doc_type_tbl,
2236 l_doc_status_tbl,l_supplier_tbl,l_tot_amt_tbl,
2237 l_doc_curr_tbl
2238 FROM (SELECT clm_document_number, comments , plc1.DISPLAYED_FIELD doc_type,
2239 PLC2.DISPLAYED_FIELD status, pv.vendor_name supplier,
2240 PO_DOCUMENT_TOTALS_PVT.getAmountOrdered('HEADER',po_header_id,'TRANSACTION',revision_num,-1) totAmt,
2241 currency_code
2242 FROM po_headers ph,
2243 po_doc_style_headers pdsh,
2244 po_lookup_codes plc1,
2245 po_lookup_codes plc2,
2246 po_vendors pv
2247 WHERE clm_award_administrator = l_admin_id
2248 AND authorization_status = 'APPROVED'
2249 AND Nvl(clm_closeout_status, 'OPEN') = 'OPEN'
2250 AND ph.style_id = pdsh.style_id
2251 AND pdsh.clm_flag = 'Y'
2252 AND plc1.lookup_code = ph.type_lookup_code
2253 AND plc1.lookup_type = 'PO TYPE'
2254 AND plc2.lookup_type = 'AUTHORIZATION STATUS'
2255 AND PLC2.lookup_code = ph.authorization_status
2256 AND pv.vendor_id = ph.vendor_id
2257 --Award should not contain any open shipments or line associated to the shipment is cancelled
2258 AND NOT EXISTS(SELECT 1
2259 FROM po_line_locations_all pll,
2260 po_lines_all pl
2261 WHERE pll.po_header_id = ph.po_header_id
2262 AND pll.po_line_id = pl.po_line_id
2263 AND (Nvl(pll.closed_code,'OPEN') IN ('OPEN')
2264 OR pl.user_document_status IS NOT NULL)
2265 )
2266 --Award should not contain any unexcersized open options
2267 AND NOT EXISTS(SELECT 1
2268 FROM po_lines_all pl
2269 WHERE pl.po_header_id = ph.po_header_id
2270 AND pl.clm_option_indicator = 'O'
2271 AND Nvl(pl.CLM_EXERCISED_FLAG,'N') = 'N'
2272 AND (SYSDATE BETWEEN pl.start_date AND pl.expiration_date
2273 OR user_document_status IS NULL)
2274 )
2275 --Award should not any draft closeout details
2276 AND NOT EXISTS(SELECT 1
2277 FROM po_closeout_details pcd
2278 WHERE pcd.reference_doc_id = ph.po_header_id
2279 AND closeout_doc_type = PO_CLOSEOUT_PVT.g_CLOSEOUT_TYPE_INDIVIDUAL
2280 AND Nvl(pcd.incloseout,'N') = 'Y'
2281 )
2282 ORDER BY ph.last_update_date)
2283 WHERE ROWNUM<11;
2284
2285 l_progress := 10;
2286
2287 IF(l_doc_num_tbl.Count > 0) THEN
2288
2289 --dbms_lob.createtemporary(l_document, true);
2290 --dbms_lob.open(l_document, dbms_lob.lob_readwrite);
2291 l_messages := l_messages || '<tr><td class="tableHeaderCell" align="left"> '|| fnd_message.get_string('PO','PO_CLSOUT_ELIGIBLE_NOTIF_BODY') ||
2292 '</td></tr><tr><td class="tableHeaderCell" align="left">';
2293
2294 l_messages := l_messages ||'<TABLE width="100%" border="1" class="gridtable">';
2295 l_messages := l_messages ||'<TR><TH>' || fnd_message.get_string('PO','PO_MUB_MSG_DOC_NUM') || '</TH>';
2296 l_messages := l_messages ||'<TH>' || fnd_message.get_string('PO','PO_WF_NOTIF_DESCRIPTION') || '</TH>';
2297 l_messages := l_messages ||'<TH>' || fnd_message.get_string('PO','PO_MUB_MSG_DOC_TYPE') || '</TH>';
2298 l_messages := l_messages ||'<TH>' || fnd_message.get_string('PO','PO_SOURCING_STATUS') || '</TH>';
2299 l_messages := l_messages ||'<TH>' || fnd_message.get_string('PO','PO_ALL_SUPPLIER') || '</TH>';
2300 l_messages := l_messages ||'<TH>' || fnd_message.get_string('PO','PO_WF_NOTIF_AMOUNT') || '</TH>';
2301 l_messages := l_messages ||'<TH>' || fnd_message.get_string('PO','PO_CURRENCY_CODE') || '</TH></TR>';
2302 l_progress := 20;
2303 --DBMS_LOB.writeappend(l_document,Length(l_messages),l_messages);
2304 FOR i IN l_doc_num_tbl.FIRST .. l_doc_num_tbl.LAST
2305 LOOP
2306 l_messages := l_messages ||'<TR><TD>' || l_doc_num_tbl(i) || '</TD>';
2307 l_messages := l_messages ||'<TD>' || l_comments_tbl(i) || '</TD>';
2308 l_messages := l_messages ||'<TD>' || l_doc_type_tbl(i) || '</TD>';
2309 l_messages := l_messages ||'<TD>' || l_doc_status_tbl(i) || '</TD>';
2310 l_messages := l_messages ||'<TD>' || l_supplier_tbl(i) || '</TD>';
2311 l_messages := l_messages ||'<TD>' || l_tot_amt_tbl(i) || '</TD>';
2312 l_messages := l_messages ||'<TD>' || l_doc_curr_tbl(i) || '</TD></TR>';
2313 --l_messages := l_messages || '<li>' || l_document_num_tbl(i) || '</li>';
2314 --DBMS_LOB.writeappend(l_document,Length(l_messages),l_messages);
2315 END LOOP;
2316 l_progress := 25;
2317 l_messages := l_messages || '</TABLE>';
2318 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'l_messages ' || l_messages);
2319 --l_progress := 26;
2320 --DBMS_LOB.writeappend(l_document,Length('/TABLE>'),'/TABLE>');
2321 l_progress := 26;
2322 l_notification_id := wf_notification.Send(ROLE => l_username,
2323 msg_type => 'POCLSOUT',
2324 msg_name => 'ELIGIBLE_DOCS_FOR_CLSOUT'
2325 );
2326
2327 wf_notification.Setattrtext(nid => l_notification_id,
2328 aname =>'MESSAGE_SUBJECT',
2329 avalue => fnd_message.get_string('PO','PO_CLSOUT_ELIGIBLE_NOTIF_SUB') || ' <' || po_moac_utils_pvt.GET_OU_NAME(po_moac_utils_pvt.GET_CURRENT_ORG_ID) || '>');
2330
2331 wf_notification.Setattrtext(nid => l_notification_id,
2332 aname => 'CONTRACT_ADMIN_PAGE',
2333 avalue => 'JSP:/OA_HTML/OA.jsp?OAFunc=PO_MULTI_MOD'||'&'||
2334 'poCallingModule=notification' || '&' ||
2335 'poCallingNotifId=-&#NID-' || '&' ||
2336 'retainAM=Y' || '&' ||
2337 'addBreadCrumb=Y'
2338 );
2339
2340 l_progress := 27;
2341 wf_notification.Setattrtext(nid => l_notification_id,
2342 aname => 'MESSAGE_BODY',
2343 avalue => 'PLSQLCLOB:PO_CLOSEOUT_PVT.get_eligible_doc_notfn_body/'|| l_messages ||'#' );
2344 l_progress := 30;
2345 fnd_file.put_line(FND_FILE.LOG,'Notification initiated for this administrator');
2346 --dbms_lob.close(l_document);
2347 ELSE
2348 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'There are no valid documents to closeout for this administrator');
2349 fnd_file.put_line(FND_FILE.LOG,'There are no valid documents to closeout for this administrator');
2350 END IF;
2351
2352 END LOOP;
2353 CLOSE clmAwardAdmins;
2354 IF (PO_LOG.d_proc) THEN PO_LOG.proc_end(d_module); END IF;
2355 fnd_file.put_line(FND_FILE.LOG,'Process Completed');
2356 EXCEPTION
2357 WHEN OTHERS THEN
2358 errbuf := sqlerrm;
2359 retcode := '2';
2360 fnd_file.put_line(FND_FILE.LOG, 'Unexpected error occurred during this process');
2361 fnd_file.put_line(FND_FILE.LOG, SQLERRM);
2362 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'when others exception at '
2363 || l_progress || ';' || SQLERRM );
2364 END eligible_docs_for_closeout;
2365
2366 --------------------------------------------------------------------------------
2367 --Start of Comments
2368
2369 --Name: get_eligible_doc_notfn_body
2370
2371 --Function:
2372 -- Get the notification body for the eligible docs
2373
2374 --Parameters:
2375
2376 --IN:
2377 --messages IN VARCHAR2,
2378 --display_type IN VARCHAR2,
2379 --document IN OUT NOCOPY CLOB,
2380 --document_type IN OUT NOCOPY VARCHAR2
2381
2382
2383 -- OUT:
2384
2385 --End of Comments
2386 --------------------------------------------------------------------------------
2387 PROCEDURE get_eligible_doc_notfn_body
2388 (
2389 messages IN VARCHAR2,
2390 display_type IN VARCHAR2,
2391 document IN OUT NOCOPY CLOB,
2392 document_type IN OUT NOCOPY VARCHAR2
2393 )
2394 IS
2395
2396 l_msgbody VARCHAR2(32000);
2397 l_progress VARCHAR2(300);
2398
2399 l_messages VARCHAR2(32000);
2400 l_firstcolon NUMBER;
2401 l_secondcolon NUMBER;
2402
2403 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || '.' || 'get_eligible_doc_notfn_body';
2404 d_position NUMBER;
2405
2406
2407 BEGIN
2408
2409 d_position := 0;
2410 IF (PO_LOG.d_proc) THEN
2411 PO_LOG.proc_begin(d_module);
2412 END IF;
2413
2414 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'l_messages ' || messages);
2415 l_firstcolon := instr(messages, '#');
2416 l_secondcolon := instr(messages, '#', 1,2);
2417 l_messages := substr(messages, 1, l_firstcolon - 1);
2418
2419 l_msgbody := '<html><style> .tableHeaderCell { font-family: Arial; font-size: 10pt;} .tableDataCell { font-family: Arial; font-size: 10pt; font-weight: bold; }
2420 table.gridtable{ font-family: arial; font-size: 10pt; color:#333333; border-width: 1px; border-color: #666666; border-collapse: collapse; }
2421 table.gridtable th{ border-width: 1px; padding: 8px; border-style: solid; border-color: #666666; background-color: #dedede;}
2422 table.gridtable td{ border-width: 1px; padding: 8px; border-style: solid; border-color: #666666; background-color: #ffffff;}
2423 </style>
2424 <body class="tableHeaderCell"><table>';
2425 l_msgbody := l_msgbody || l_messages || '</td></tr><tr/><tr><td class="tableHeaderCell" align="left">'||fnd_message.get_string('PO','PO_CLSOUT_NOTIF_CHOOSE_ACTION')||'</td></tr></table></body></html>';
2426
2427 WF_NOTIFICATION.WriteToClob(document, l_msgbody);
2428
2429 l_progress := 4;
2430
2431 IF (PO_LOG.d_proc) THEN PO_LOG.proc_end(d_module); END IF;
2432
2433 EXCEPTION
2434 WHEN OTHERS THEN
2435 WF_CORE.context('PO_CLOSEOUT_PVT','get_eligible_doc_notfn_body',l_progress);
2436 RAISE;
2437
2438 END get_eligible_doc_notfn_body;
2439
2440 --------------------------------------------------------------------------------
2441 --Start of Comments
2442
2443 --Name: store_validations
2444
2445 --Function:
2446 -- Inserts the validation record into po_multi_mod_val_results
2447
2448 --Parameters:
2449
2450 --IN:
2451 -- p_doc_id NUMBER
2452 -- p_doc_num VARCHAR2
2453 -- p_mod_doc_id NUMBER
2454 -- p_req_id VARCHAR2
2455 -- p_exception_type VARCHAR2
2456 -- p_message_app VARCHAR2
2457 -- p_message_name VARCHAR2
2458 -- p_validation_type VARCHAR2
2459 -- p_token_name1 VARCHAR2
2460 -- p_token_value1 VARCHAR2
2461 -- p_token_name2 VARCHAR2
2462 -- p_token_value2 VARCHAR2
2463 -- p_token_name3 VARCHAR2
2464 -- p_token_value3 VARCHAR2
2465 -- p_token_name4 VARCHAR2
2466 -- p_token_value4 VARCHAR2
2467 -- p_token_name5 VARCHAR2
2468 -- p_token_value5 VARCHAR2
2469
2470 --End of Comments
2471 --------------------------------------------------------------------------------
2472 PROCEDURE store_validations (
2473 p_doc_id IN NUMBER
2474 , p_doc_num IN VARCHAR2
2475 , p_mod_doc_id IN NUMBER
2476 , p_req_id IN NUMBER
2477 , p_exception_type IN VARCHAR2
2478 , p_message_app IN VARCHAR2
2479 , p_message_name IN VARCHAR2
2480 , p_validation_type IN VARCHAR2
2481 , p_token_name1 IN VARCHAR2 DEFAULT NULL
2482 , p_token_value1 IN VARCHAR2 DEFAULT NULL
2483 , p_token_name2 IN VARCHAR2 DEFAULT NULL
2484 , p_token_value2 IN VARCHAR2 DEFAULT NULL
2485 , p_token_name3 IN VARCHAR2 DEFAULT NULL
2486 , p_token_value3 IN VARCHAR2 DEFAULT NULL
2487 , p_token_name4 IN VARCHAR2 DEFAULT NULL
2488 , p_token_value4 IN VARCHAR2 DEFAULT NULL
2489 , p_token_name5 IN VARCHAR2 DEFAULT NULL
2490 , p_token_value5 IN VARCHAR2 DEFAULT NULL
2491 )
2492 IS
2493
2494 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || '.' || 'check_exceptions';
2495 l_progress NUMBER := 0;
2496
2497 BEGIN
2498 INSERT INTO po_multi_mod_val_results
2499 ( MULTI_MOD_VAL_RESULT_ID
2500 ,MULTI_MOD_REQUEST_ID
2501 ,MULTI_MOD_DOC_ID
2502 ,VALIDATION_TYPE
2503 ,EXCEPTION_TYPE
2504 ,RESULT_TYPE
2505 ,DOCUMENT_ID
2506 ,DOCUMENT_NUMBER
2507 ,RELATED_DOCUMENT_ID
2508 ,RELATED_DOCUMENT_NUMBER
2509 ,RELATED_DOCUMENT_DATE
2510 ,RELATED_DOCUMENT_AMOUNT
2511 ,MESSAGE_APPLICATION
2512 ,MESSAGE_NAME
2513 ,TOKEN1_NAME
2514 ,TOKEN1_VALUE
2515 ,TOKEN2_NAME
2516 ,TOKEN2_VALUE
2517 ,TOKEN3_NAME
2518 ,TOKEN3_VALUE
2519 ,TOKEN4_NAME
2520 ,TOKEN4_VALUE
2521 ,TOKEN5_NAME
2522 ,TOKEN5_VALUE
2523 ,TOKEN6_NAME
2524 ,TOKEN6_VALUE
2525 ,LAST_UPDATE_DATE
2526 ,LAST_UPDATED_BY
2527 ,LAST_UPDATE_LOGIN
2528 ,CREATION_DATE
2529 ,CREATED_BY
2530 ,REQUEST_ID
2531 )
2532 VALUES
2533 ( po_multi_mod_val_results_s.nextval
2534 , p_req_id
2535 , p_mod_doc_id
2536 , p_validation_type
2537 , p_exception_type
2538 , null
2539 , p_doc_id
2540 , p_doc_num
2541 , null
2542 , null
2543 , null
2544 , null
2545 , p_message_app
2546 , p_message_name
2547 , p_token_name1
2548 , p_token_value1
2549 , p_token_name2
2550 , p_token_value2
2551 , p_token_name3
2552 , p_token_value3
2553 , p_token_name4
2554 , p_token_value4
2555 , p_token_name5
2556 , p_token_value5
2557 , null
2558 , null
2559 , sysdate
2560 , fnd_global.user_id
2561 , fnd_global.login_id
2562 , sysdate
2563 , fnd_global.user_id
2564 , fnd_global.conc_request_id
2565 );
2566
2567 END store_validations;
2568
2569 --------------------------------------------------------------------------------
2570 --Start of Comments
2571
2572 --Name: delete_contract
2573
2574 --Function:
2575 -- Deletes the contract if exist
2576
2577 --Parameters:
2578
2579 --IN:
2580 -- p_doc_id NUMBER
2581 -- p_closeout_type VARCHAR2
2582
2583 --OUT:
2584 -- x_return_status VARCHAR2
2585 -- x_return_msg VARCHAR2
2586
2587 --End of Comments
2588 --------------------------------------------------------------------------------
2589 procedure delete_contract (
2590 p_doc_id IN NUMBER
2591 , p_closeout_type IN VARCHAR
2592 , x_return_status OUT NOCOPY VARCHAR2
2593 , x_return_msg OUT NOCOPY VARCHAR2
2594 )
2595 IS
2596 l_progress NUMBER := 0;
2597 l_document_rec OKC_IMP_RECORD_TYPES.document_rec_type;
2598 l_contract_type VARCHAR2(50);
2599 l_contract_id NUMBER;
2600 l_contract_ver NUMBER;
2601 l_msg_count NUMBER;
2602 l_po_doc_type VARCHAR2(50);
2603 l_doc_num VARCHAR2(60);
2604
2605 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || '.' || 'delete_contract';
2606
2607 BEGIN
2608 IF (PO_LOG.d_proc) THEN
2609 PO_LOG.proc_begin(d_module);
2610 END IF;
2611
2612 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'p_doc_id ' || p_doc_id);
2613 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'p_closeout_type ' || p_closeout_type);
2614
2615 PO_CLOSEOUT_PVT.check_contract_exists(p_doc_id => p_doc_id,
2616 p_closeout_type => p_closeout_type,
2617 x_contract_type => l_contract_type,
2618 x_contract_id => l_contract_id,
2619 x_contract_ver => l_contract_ver,
2620 x_return_msg => x_return_msg,
2621 x_return_status => x_return_status);
2622
2623 l_progress := 20;
2624
2625 IF l_contract_id IS NOT NULL THEN
2626 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'Contract exists ');
2627 IF g_CLOSEOUT_TYPE_INDIVIDUAL = p_closeout_type THEN
2628 SELECT type_lookup_code, clm_document_number
2629 INTO l_po_doc_type, l_doc_num
2630 FROM PO_HEADERS_ALL
2631 WHERE po_header_id = p_doc_id;
2632
2633 l_progress := 30;
2634 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'l_po_doc_type ' || l_po_doc_type);
2635 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'l_doc_num ' || l_doc_num);
2636
2637 IF l_po_doc_type = 'STANDARD' THEN
2638 l_contract_type := 'PO_STANDARD';
2639 ELSIF l_po_doc_type = 'BLANKET' THEN
2640 l_contract_type := 'PA_BLANKET';
2641 ELSE
2642 l_contract_type := 'PA_CONTRACT';
2643 END IF;
2644 l_document_rec.document_number := l_doc_num;
2645 ELSE
2646 l_contract_type := 'PO_CLOSEOUT_SET';
2647 END IF;
2648
2649 l_progress := 40;
2650 l_document_rec.business_document_type := l_contract_type;
2651 l_document_rec.business_document_id := p_doc_id;
2652 l_document_rec.source_code := 'INTERNAL';
2653 l_document_rec.relationship_id := 10006;
2654 l_document_rec.display_in_contract := 'N';
2655
2656 okc_rep_closeout_pvt.delete_contract(p_api_version => 1.0,
2657 p_document_rec => l_document_rec,
2658 p_contract_type => l_contract_type,
2659 p_contract_id => l_contract_id,
2660 p_commit => fnd_api.g_true,
2661 x_msg_data => x_return_msg,
2662 x_msg_count => l_msg_count,
2663 x_return_status => x_return_status);
2664 ELSE
2665 x_return_msg := 'Contract does not exits for this document';
2666 x_return_status := FND_API.G_RET_STS_SUCCESS;
2667 END IF;
2668
2669 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'x_return_msg ' || x_return_msg);
2670 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'l_msg_count ' || l_msg_count);
2671 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'x_return_status ' || x_return_status);
2672
2673 l_progress := 50;
2674
2675 IF (PO_LOG.d_proc) THEN PO_LOG.proc_end(d_module); END IF;
2676
2677 EXCEPTION
2678 WHEN OTHERS THEN
2679
2680 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'when others exception at '
2681 || l_progress || ';' || SQLERRM );
2682 END delete_contract;
2683
2684 --------------------------------------------------------------------------------
2685 --Start of Comments
2686
2687 --Name: set_closeout_status
2688
2689 --Function:
2690 -- Set the closeout status. This will get called from Closeout workflow.
2691 -- Reads the status value from WF attribute.
2692
2693 --End of Comments
2694 --------------------------------------------------------------------------------
2695
2696 PROCEDURE set_closeout_status (
2697 itemtype IN VARCHAR2
2698 ,itemkey IN VARCHAR2
2699 ,actid IN NUMBER
2700 ,funcmode IN VARCHAR2
2701 ,resultout OUT NOCOPY VARCHAR2
2702 )
2703 IS
2704 l_progress NUMBER := 0;
2705 l_document_id NUMBER;
2706 l_closeout_status VARCHAR2(50);
2707 l_po_doc_type VARCHAR2(25);
2708 l_rev_number NUMBER;
2709 l_seq_num NUMBER;
2710 l_agent_id NUMBER;
2711 l_closeout_dtate DATE;
2712
2713 l_aco_signed_date DATE;
2714 l_pco_signed_date DATE;
2715
2716 l_aco_singer_name VARCHAR2(50);
2717 l_pco_singer_name VARCHAR2(50);
2718
2719 l_aco_remarks VARCHAR2(4000);
2720 l_pco_remarks VARCHAR2(4000);
2721 l_doc_num VARCHAR2(100);
2722 l_return_status VARCHAR2(1);
2723 l_doc_subtype VARCHAR2(50);
2724 l_doc_type VARCHAR2(25);
2725 l_return_code VARCHAR2(25);
2726 l_exc_msg VARCHAR2(2000);
2727
2728 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || '.' || 'set_closeout_status';
2729
2730 BEGIN
2731 IF (PO_LOG.d_proc) THEN
2732 PO_LOG.proc_begin(d_module);
2733 END IF;
2734
2735 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'itemkey ' || itemkey);
2736 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'itemtype ' || itemtype);
2737
2738 l_document_id := wf_engine.GetItemAttrNumber
2739 (itemtype => itemtype,
2740 itemkey => itemkey,
2741 aname => 'DOCUMENT_ID');
2742 l_closeout_dtate := wf_engine.GetItemAttrDate
2743 (itemtype => itemtype,
2744 itemkey => itemkey,
2745 aname => 'CLOSEOUT_DATE');
2746
2747 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'l_document_id ' || l_document_id);
2748 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'l_closeout_dtate ' || l_closeout_dtate);
2749
2750 l_progress := 10;
2751 l_closeout_status := wf_engine.GetActivityAttrText
2752 (itemtype => itemtype,
2753 itemkey => itemkey,
2754 actid => actid,
2755 aname => 'CLOSEOUT_STATUS');
2756
2757 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'l_closeout_status ' || l_closeout_status);
2758
2759 UPDATE po_headers_all
2760 SET clm_closeout_status = l_closeout_status
2761 WHERE po_header_id = l_document_id;
2762
2763 l_progress := 11;
2764
2765 IF (l_closeout_status = 'ADMIN_COMPLETE') THEN
2766
2767 l_aco_signed_date := wf_engine.GetItemAttrDate
2768 (itemtype => itemtype,
2769 itemkey => itemkey,
2770 aname => 'ACO_SIGNED_DATE');
2771 l_aco_singer_name := wf_engine.GetItemAttrText
2772 (itemtype => itemtype,
2773 itemkey => itemkey,
2774 aname => 'ACO_SIGNER_NAME');
2775
2776 l_aco_remarks := wf_engine.GetItemAttrText
2777 (itemtype => itemtype,
2778 itemkey => itemkey,
2779 aname => 'ACO_REMARKS');
2780
2781 l_progress := 12;
2782 UPDATE po_closeout_details
2783 SET aco_signer_name = l_aco_singer_name,
2784 aco_signed_date = Nvl(l_aco_signed_date, sysdate),
2785 aco_remarks = l_aco_remarks
2786 WHERE reference_doc_id = l_document_id
2787 AND closeout_doc_type = PO_CLOSEOUT_PVT.g_CLOSEOUT_TYPE_INDIVIDUAL;
2788
2789 l_progress := 13;
2790
2791 ELSIF (l_closeout_status = 'CLOSED_OUT') THEN
2792
2793 l_pco_signed_date := wf_engine.GetItemAttrDate
2794 (itemtype => itemtype,
2795 itemkey => itemkey,
2796 aname => 'PCO_SIGNED_DATE');
2797 l_pco_singer_name := wf_engine.GetItemAttrText
2798 (itemtype => itemtype,
2799 itemkey => itemkey,
2800 aname => 'PCO_SIGNER_NAME');
2801
2802 l_pco_remarks := wf_engine.GetItemAttrText
2803 (itemtype => itemtype,
2804 itemkey => itemkey,
2805 aname => 'PCO_REMARKS');
2806
2807 l_progress := 14;
2808
2809 UPDATE po_closeout_details
2810 SET closeout_date = nvl(Nvl(l_pco_signed_date,aco_signed_date), sysdate),
2811 pco_signer_name = l_pco_singer_name,
2812 pco_signed_date = Nvl(l_pco_signed_date, sysdate),
2813 pco_remarks = l_pco_remarks,
2814 incloseout = 'C' -- Completed
2815 WHERE reference_doc_id = l_document_id
2816 AND closeout_doc_type = PO_CLOSEOUT_PVT.g_CLOSEOUT_TYPE_INDIVIDUAL;
2817
2818 l_progress := 15;
2819
2820 select clm_document_number,type_lookup_code, Decode(type_lookup_code, 'STANDARD', 'PO', 'PA')
2821 into l_doc_num,l_doc_subtype,l_doc_type
2822 from po_headers_all
2823 where po_header_id = l_document_id;
2824
2825 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'l_doc_num ' || l_doc_num);
2826 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'l_doc_subtype ' || l_doc_subtype);
2827 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'l_doc_type ' || l_doc_type);
2828 l_progress := 16;
2829
2830 PO_DOCUMENT_ACTION_PVT.do_freeze
2831 (
2832 p_document_id => l_document_id,
2833 p_document_type => l_doc_type,
2834 p_document_subtype => l_doc_subtype,
2835 p_reason => 'Document ' || l_doc_subtype || ' status is moving to CLOSED_OUT',
2836 x_return_status => l_return_status,
2837 x_return_code => l_return_code,
2838 x_exception_msg => l_exc_msg
2839 );
2840 l_progress := 17;
2841
2842 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'l_return_status ' || l_return_status);
2843 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'l_return_code ' || l_return_code);
2844 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'l_exc_msg ' || l_exc_msg);
2845 END IF;
2846 --closeout_date pco_remarks aco_signer_name pco_signer_name aco_signed_date pco_signed_date
2847
2848
2849 l_progress := 20;
2850 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'update query count ' || SQL%ROWCOUNT);
2851
2852 SELECT type_lookup_code,revision_num, agent_id
2853 INTO l_po_doc_type, l_rev_number , l_agent_id
2854 FROM PO_HEADERS_ALL
2855 WHERE po_header_id = l_document_id;
2856 l_progress := 30;
2857
2858 SELECT Max(sequence_num)
2859 INTO l_seq_num
2860 FROM po_action_history;
2861
2862 l_progress := 40;
2863
2864 INSERT INTO po_action_history(
2865 object_id
2866 , object_type_code
2867 , object_sub_type_code
2868 , sequence_num
2869 , last_update_date
2870 , last_updated_by
2871 , creation_date
2872 , created_by
2873 , action_code
2874 , action_date
2875 , employee_id
2876 , note
2877 , object_revision_num
2878 , last_update_login
2879 , request_id
2880 , program_application_id
2881 , program_id
2882 , program_update_date
2883 , approval_path_id
2884 , offline_code
2885 ) VALUES (
2886 l_document_id
2887 , 'PO'
2888 , l_po_doc_type
2889 , l_seq_num+1
2890 , SYSDATE
2891 , fnd_global.USER_ID
2892 , SYSDATE
2893 , fnd_global.USER_ID
2894 , l_closeout_status --ActionCode
2895 , SYSDATE
2896 , l_agent_id
2897 , null
2898 , l_rev_number
2899 , fnd_global.LOGIN_ID
2900 , 0
2901 , 0
2902 , 0
2903 , ''
2904 , 0
2905 , ''
2906 );
2907
2908 l_progress := 50;
2909 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'insert query count ' || SQL%ROWCOUNT);
2910
2911 resultout := wf_engine.eng_completed || ':' || 'ACTIVITY_PERFORMED' ;
2912
2913 IF (PO_LOG.d_proc) THEN PO_LOG.proc_end(d_module); END IF;
2914
2915 EXCEPTION
2916 WHEN OTHERS THEN
2917
2918 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'PO_CLOSEOUT_PVT.set_closeout_status : when others exception at '
2919 || l_progress || ';' || SQLERRM );
2920 RAISE;
2921 END set_closeout_status;
2922
2923
2924 --------------------------------------------------------------------------------
2925 --Start of Comments
2926
2927 --Name: get_val_notfn_body
2928
2929 --Function:
2930 -- Get the notification body for the task list activation error and PDF generation error.
2931
2932 --Parameters:
2933
2934 --IN:
2935 --messages IN VARCHAR2,
2936 --display_type IN VARCHAR2,
2937 --document IN OUT NOCOPY CLOB,
2938 --document_type IN OUT NOCOPY VARCHAR2
2939
2940
2941 -- OUT:
2942
2943 --End of Comments
2944 --------------------------------------------------------------------------------
2945
2946 PROCEDURE get_clsout_err_msg_body
2947 (
2948 message_type IN VARCHAR2,
2949 display_type IN VARCHAR2,
2950 document IN OUT NOCOPY CLOB,
2951 document_type IN OUT NOCOPY VARCHAR2
2952 )
2953 IS
2954
2955 l_msgbody VARCHAR2(32000);
2956 l_progress VARCHAR2(300);
2957
2958 l_doc_sub_type VARCHAR2(240);
2959 l_clm_document_number VARCHAR2(50);
2960 l_pdf_type VARCHAR2(50);
2961
2962 l_message_type VARCHAR2(300);
2963 l_firstcolon NUMBER;
2964 l_secondcolon NUMBER;
2965 l_itemtype WF_ITEMS.item_type%TYPE;
2966 l_itemkey WF_ITEMS.item_key%TYPE;
2967
2968 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || '.' || 'get_clsout_err_msg_body';
2969 d_position NUMBER;
2970
2971 BEGIN
2972
2973 d_position := 0;
2974 IF (PO_LOG.d_proc) THEN
2975 PO_LOG.proc_begin(d_module);
2976 END IF;
2977
2978 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'PO_CLOSEOUT_PVT.get_clsout_err_msg_body: 01');
2979 l_firstcolon := instr(message_type, ':');
2980 l_secondcolon := instr(message_type, ':', 1,2);
2981 l_message_type := substr(message_type, 1, l_firstcolon - 1);
2982 l_itemtype := substr(message_type, l_firstcolon + 1, l_secondcolon - l_firstcolon - 1);
2983 l_itemkey := substr(message_type, l_secondcolon+1,length(message_type) - l_secondcolon);
2984
2985 l_clm_document_number := wf_engine.GetItemAttrText (itemtype => l_itemtype, itemkey => l_itemkey, aname => 'CLM_DOCUMENT_NUMBER');
2986 l_doc_sub_type := wf_engine.GetItemAttrText (itemtype => l_itemtype, itemkey => l_itemkey, aname => 'DOCUMENT_TYPE_DISP');
2987 l_pdf_type := wf_engine.GetItemAttrText (itemtype => l_itemtype, itemkey => l_itemkey, aname => 'PDF_TYPE');
2988
2989 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'l_message_type ' || l_message_type);
2990 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'l_clm_document_number ' || l_clm_document_number);
2991 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'l_doc_sub_type ' || l_doc_sub_type);
2992
2993
2994 IF ( l_message_type = 'CLSOUT_TASK_ACT_ERR') THEN
2995
2996 fnd_message.set_name('PO','PO_CLSOUT_TASK_ACT_ERR_BODY');
2997
2998 ELSIF (l_message_type = 'CLSOUT_PDF_GEN_ERR') THEN
2999
3000 fnd_message.set_name('PO','PO_CLSOUT_PDF_GEN_ERR_BODY');
3001 fnd_message.set_token('PDF_TYPE',l_pdf_type);
3002
3003 END IF;
3004
3005 fnd_message.set_token('DOC_SUB_TYPE',l_doc_sub_type);
3006 fnd_message.set_token('CLM_DOCUMENT_NUMBER',l_clm_document_number);
3007
3008 l_msgbody := fnd_message.get;
3009
3010 WF_NOTIFICATION.WriteToClob(document, l_msgbody);
3011
3012 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'PO_CLOSEOUT_PVT.get_clsout_err_msg_body: 03');
3013
3014 l_progress := 1;
3015
3016 IF (PO_LOG.d_proc) THEN PO_LOG.proc_end(d_module); END IF;
3017
3018 EXCEPTION
3019 WHEN OTHERS THEN
3020 --l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(l_item_type, l_item_key);
3021 --l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(l_item_type, l_item_key);
3022 WF_CORE.context('PO_CLOSEOUT_PVT','get_clsout_err_msg_body',l_progress);
3023 --PO_REQAPPROVAL_INIT1.send_error_notif(l_item_type, l_item_key, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_SIGNATURE_PVT.GET_SIGNATURE_NOTFN_BODY');
3024 RAISE;
3025
3026 END get_clsout_err_msg_body;
3027
3028
3029 --------------------------------------------------------------------------------
3030 --Start of Comments
3031
3032 --Name: get_val_notfn_body
3033
3034 --Function:
3035 -- Get the notification body for the validations error/warning message.
3036
3037 --Parameters:
3038
3039 --IN:
3040 --messages IN VARCHAR2,
3041 --display_type IN VARCHAR2,
3042 --document IN OUT NOCOPY CLOB,
3043 --document_type IN OUT NOCOPY VARCHAR2
3044
3045
3046 -- OUT:
3047
3048 --End of Comments
3049 --------------------------------------------------------------------------------
3050 PROCEDURE get_val_notfn_body
3051 (
3052 messages IN VARCHAR2,
3053 display_type IN VARCHAR2,
3054 document IN OUT NOCOPY CLOB,
3055 document_type IN OUT NOCOPY VARCHAR2
3056 )
3057 IS
3058
3059 l_msgbody VARCHAR2(32000);
3060 l_progress VARCHAR2(300);
3061
3062 l_messages VARCHAR2(32000);
3063 l_firstcolon NUMBER;
3064 l_secondcolon NUMBER;
3065 l_itemtype WF_ITEMS.item_type%TYPE;
3066 l_itemkey WF_ITEMS.item_key%TYPE;
3067
3068 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || '.' || 'get_val_notfn_body';
3069 d_position NUMBER;
3070
3071
3072 BEGIN
3073
3074 d_position := 0;
3075 IF (PO_LOG.d_proc) THEN
3076 PO_LOG.proc_begin(d_module);
3077 END IF;
3078
3079 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'l_messages ' || messages);
3080 l_firstcolon := instr(messages, '#');
3081 l_secondcolon := instr(messages, '#', 1,2);
3082 l_messages := substr(messages, 1, l_firstcolon - 1);
3083 l_itemtype := substr(messages, l_firstcolon + 1, l_secondcolon - l_firstcolon - 1);
3084 l_itemkey := substr(messages, l_secondcolon+1,length(messages) - l_secondcolon);
3085
3086 l_msgbody := '<html><style> .tableHeaderCell { font-family: Arial; font-size: 10pt;} .tableDataCell { font-family: Arial; font-size: 10pt; font-weight: bold; }</style>
3087 <body class="tableHeaderCell"><table>';
3088 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'PO_CLOSEOUT_PVT.get_val_notfn_body: 07');
3089 l_msgbody := l_msgbody || l_messages || '</ol></td></tr><tr/><tr><td class="tableHeaderCell" align="left">'||fnd_message.get_string('PO','PO_CONTROL_CHOOSE_ACTION')||'</td></tr></table></body></html>';
3090
3091 WF_NOTIFICATION.WriteToClob(document, l_msgbody);
3092
3093 l_progress := 4;
3094
3095 IF (PO_LOG.d_proc) THEN PO_LOG.proc_end(d_module); END IF;
3096
3097 EXCEPTION
3098 WHEN OTHERS THEN
3099 --l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(l_item_type, l_item_key);
3100 --l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(l_item_type, l_item_key);
3101 WF_CORE.context('PO_CLOSEOUT_PVT','get_val_notfn_body',l_progress);
3102 --PO_REQAPPROVAL_INIT1.send_error_notif(l_item_type, l_item_key, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_SIGNATURE_PVT.GET_SIGNATURE_NOTFN_BODY');
3103 RAISE;
3104
3105 END get_val_notfn_body;
3106
3107
3108 --------------------------------------------------------------------------------
3109 --Start of Comments
3110
3111 --Name: Validate_Document
3112
3113 --Function:
3114 -- Validate the Manage Closeout from WF. It is wrapper for the procedure
3115 -- "validate_manage_closeout"
3116
3117 --Parameters:
3118
3119 --IN:
3120 -- itemtype NUMBER
3121 -- itemkey VARCHAR2
3122 -- actid NUMBER
3123 -- funcmode VARCHAR2
3124
3125 --OUT:
3126 --resultout VARCHAR2
3127
3128 --End of Comments
3129 --------------------------------------------------------------------------------
3130
3131 PROCEDURE Validate_Document (
3132 itemtype IN VARCHAR2
3133 , itemkey IN VARCHAR2
3134 , actid IN NUMBER
3135 , funcmode IN VARCHAR2
3136 , resultout OUT NOCOPY VARCHAR2
3137 )
3138 IS
3139 l_progress NUMBER := 0;
3140 l_document_id NUMBER;
3141 l_closeout_status VARCHAR2(50);
3142 l_results PO_MULTI_MOD_VAL_RESULTS_TYPE;
3143 l_result_type VARCHAR2(50) := 'SUCCESS';
3144 l_msg VARCHAR2(254);
3145 l_message_subject VARCHAR2(254);
3146 l_messages VARCHAR2(32000);
3147 l_doc_sub_type VARCHAR2(240);
3148 l_clm_document_number VARCHAR2(50);
3149
3150 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || '.' || 'Validate_Document';
3151 d_position NUMBER;
3152
3153 BEGIN
3154 d_position := 0;
3155 IF (PO_LOG.d_proc) THEN
3156 PO_LOG.proc_begin(d_module);
3157 END IF;
3158
3159 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'itemkey ' || itemkey);
3160 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'itemtype ' || itemtype);
3161
3162 l_document_id := wf_engine.GetItemAttrNumber
3163 (itemtype => itemtype,
3164 itemkey => itemkey,
3165 aname => 'DOCUMENT_ID');
3166 l_clm_document_number := wf_engine.GetItemAttrText (itemtype => itemtype, itemkey => itemkey, aname => 'CLM_DOCUMENT_NUMBER');
3167 l_doc_sub_type := wf_engine.GetItemAttrText (itemtype => itemtype, itemkey => itemkey, aname => 'DOCUMENT_TYPE_DISP');
3168
3169 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'l_document_id ' || l_document_id);
3170
3171 l_progress := 10;
3172
3173 l_results := PO_MULTI_MOD_VAL_RESULTS_TYPE.NEW_INSTANCE();
3174
3175 PO_CLOSEOUT_PVT.validate_manage_closeout (p_doc_id => l_document_id
3176 , p_chk_list_req => 'N' -- CheckList Error and Open Mod error are mutual exc, so the checklist added error not req for wf val.
3177 , x_result_type => l_result_type
3178 , x_results => l_results);
3179 l_progress := 30;
3180
3181 IF (l_result_type = c_result_type_FAILURE) THEN
3182 resultout := wf_engine.eng_completed || ':' || 'ERROR' ;
3183 fnd_message.set_name('PO','PO_CLSOUT_VALIDATION_ERR_SUB');
3184 fnd_message.set_token('DOC_SUB_TYPE',l_doc_sub_type);
3185 fnd_message.set_token('CLM_DOCUMENT_NUMBER',l_clm_document_number);
3186
3187 l_message_subject := fnd_message.get;
3188
3189 fnd_message.set_name('PO','PO_CLSOUT_VALIDATION_ERR_BODY');
3190 fnd_message.set_token('DOC_SUB_TYPE',l_doc_sub_type);
3191 fnd_message.set_token('CLM_DOCUMENT_NUMBER',l_clm_document_number);
3192
3193 l_messages := l_messages || '<tr><td class="tableHeaderCell" align="left"> '||fnd_message.get||
3194 '</td></tr><tr><td class="tableHeaderCell" align="left"><ol>';
3195
3196 ELSIF (l_result_type = c_result_type_WARNING) THEN
3197 resultout := wf_engine.eng_completed || ':' || 'WARNING' ;
3198 fnd_message.set_name('PO','PO_CLSOUT_VAL_WARNING_SUB');
3199 fnd_message.set_token('DOC_SUB_TYPE',l_doc_sub_type);
3200 fnd_message.set_token('CLM_DOCUMENT_NUMBER',l_clm_document_number);
3201
3202 l_message_subject := fnd_message.get;
3203
3204 fnd_message.set_name('PO','PO_CLSOUT_VAL_WARNING_BODY');
3205 fnd_message.set_token('DOC_SUB_TYPE',l_doc_sub_type);
3206 fnd_message.set_token('CLM_DOCUMENT_NUMBER',l_clm_document_number);
3207
3208 l_messages := l_messages || '<tr><td class="tableHeaderCell" align="left"> '||fnd_message.get||
3209 '</td></tr><tr><td class="tableHeaderCell" align="left"><ol>';
3210
3211 ELSIF (l_result_type = c_result_type_SUCCESS) THEN
3212
3213 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'l_result_type ' || l_result_type);
3214 resultout := wf_engine.eng_completed || ':' || 'SUCCESS' ;
3215 RETURN;
3216 END IF;
3217
3218 -- PO_CLOSEOUT_PVT.Log( d_module, d_position, 'l_message_subject'||l_message_subject);
3219
3220 wf_engine.setitemattrtext(itemtype => itemtype,
3221 itemkey => itemkey,
3222 aname => 'MESSAGE_SUBJECT',
3223 avalue => l_message_subject);
3224
3225 FOR i IN l_results.MESSAGE_NAME.FIRST .. l_results.MESSAGE_NAME.LAST
3226 LOOP
3227 l_msg := fnd_message.get_string('PO', l_results.MESSAGE_NAME(i));
3228 l_messages := l_messages || '<li>' || l_msg || '</li>';
3229 END LOOP;
3230
3231 wf_engine.setitemattrtext(itemtype => itemtype,
3232 itemkey => itemkey,
3233 aname => 'MESSAGE_BODY',
3234 avalue => 'PLSQLCLOB:PO_CLOSEOUT_PVT.get_val_notfn_body/'|| l_messages ||'#'||itemtype||'#'||itemkey);
3235
3236
3237 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'wf_validate_closeout result ' || resultout);
3238 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'l_result_type ' || l_result_type);
3239
3240 IF (PO_LOG.d_proc) THEN PO_LOG.proc_end(d_module); END IF;
3241
3242 EXCEPTION
3243 WHEN OTHERS THEN
3244
3245 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'PO_CLOSEOUT_PVT.Validate_Document : when others exception at '
3246 || l_progress || ';' || SQLERRM );
3247 resultout := wf_engine.eng_completed || ':' || 'ERROR' ;
3248
3249 RAISE;
3250 END Validate_Document;
3251
3252 --------------------------------------------------------------------------------
3253 --Start of Comments
3254
3255 --Name: Activate_task_list
3256
3257 --Function:
3258 -- Activating the Contract's Task list
3259
3260 --Parameters:
3261
3262 --IN:
3263 -- itemtype NUMBER
3264 -- itemkey VARCHAR2
3265 -- actid NUMBER
3266 -- funcmode VARCHAR2
3267
3268 --OUT:
3269 --resultout VARCHAR2
3270
3271 --End of Comments
3272 --------------------------------------------------------------------------------
3273
3274 PROCEDURE Activate_tasks_list (
3275 itemtype IN VARCHAR2
3276 , itemkey IN VARCHAR2
3277 , actid IN NUMBER
3278 , funcmode IN VARCHAR2
3279 , resultout OUT NOCOPY VARCHAR2
3280 ) IS
3281 l_progress NUMBER := 0;
3282 l_document_id NUMBER;
3283
3284 l_document_rec OKC_IMP_RECORD_TYPES.document_rec_type;
3285 l_msg_data VARCHAR2(1000);
3286 l_msg_count NUMBER;
3287 l_return_status VARCHAR2(100);
3288 l_task_status VARCHAR2(30);
3289 l_po_doc_type VARCHAR2(25);
3290 l_contr_type VARCHAR2(25);
3291 l_rev_number NUMBER;
3292 l_doc_num VARCHAR2(100);
3293
3294 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || '.' || 'Activate_tasks_list';
3295 d_position NUMBER;
3296
3297 BEGIN
3298 d_position := 0;
3299 IF (PO_LOG.d_proc) THEN
3300 PO_LOG.proc_begin(d_module);
3301 END IF;
3302
3303 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'itemkey ' || itemkey);
3304 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'itemtype ' || itemtype);
3305
3306 l_document_id := wf_engine.GetItemAttrNumber
3307 (itemtype => itemtype,
3308 itemkey => itemkey,
3309 aname => 'DOCUMENT_ID');
3310
3311 SELECT type_lookup_code,revision_num,clm_document_number
3312 INTO l_po_doc_type, l_rev_number, l_doc_num
3313 FROM PO_HEADERS_ALL
3314 WHERE po_header_id = l_document_id;
3315
3316 IF l_po_doc_type = 'STANDARD' THEN
3317 l_contr_type := 'PO_STANDARD';
3318 ELSIF l_po_doc_type = 'BLANKET' THEN
3319 l_contr_type := 'PA_BLANKET';
3320 ELSE
3321 l_contr_type := 'PA_CONTRACT';
3322 END IF;
3323
3324 l_document_rec.business_document_type := l_contr_type;
3325 l_document_rec.business_document_id := l_document_id;
3326 l_document_rec.business_document_version := l_rev_number;
3327 l_document_rec.document_number := l_doc_num;
3328 l_document_rec.source_code := 'INTERNAL';
3329 l_document_rec.display_in_contract := 'N';
3330
3331 OKC_REP_CLOSEOUT_PVT.Activate_tasks_closeout( p_api_version => 1.0,
3332 p_document_rec => l_document_rec,
3333 p_physical_completion_date => sysdate,
3334 p_init_msg_list => fnd_api.g_false ,
3335 p_commit => fnd_api.g_true,
3336 x_task_status => l_task_status,
3337 x_msg_data => l_msg_data,
3338 x_msg_count => l_msg_count,
3339 x_return_status => l_return_status);
3340
3341 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'l_msg_data:'||l_msg_data);
3342 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'l_task_status: '||l_task_status);
3343 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'l_return_status: '||l_return_status);
3344 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'l_document_id: '||l_document_id);
3345 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'l_po_doc_type: '||l_po_doc_type);
3346 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'l_rev_number: '||l_rev_number);
3347 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'l_doc_num: '||l_doc_num);
3348
3349
3350 IF (l_return_status = fnd_api.g_ret_sts_success AND l_task_status = 'COMPLETED') THEN
3351 resultout := wf_engine.eng_completed || ':' || 'COMPLETED' ;
3352 --When a document is reopened and If the tasks are already completed, workflow will directly initiates the signing process
3353 update PO_CLOSEOUT_DETAILS
3354 set incloseout = 'W'
3355 where reference_doc_id = l_document_id
3356 and closeout_doc_type = PO_CLOSEOUT_PVT.g_CLOSEOUT_TYPE_INDIVIDUAL;
3357 ELSIF (l_return_status = fnd_api.g_ret_sts_success AND l_task_status = 'INITIATED') THEN
3358 resultout := wf_engine.eng_completed || ':' || 'ACTIVATED' ;
3359 ELSIF (l_return_status = fnd_api.g_ret_sts_error OR l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
3360 resultout := wf_engine.eng_completed || ':' || 'ERROR' ;
3361 END IF;
3362
3363 IF (PO_LOG.d_proc) THEN PO_LOG.proc_end(d_module); END IF;
3364
3365 EXCEPTION
3366 WHEN OTHERS THEN
3367
3368 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'PO_CLOSEOUT_PVT.Activate_tasks_list : when others exception at '
3369 || l_progress || ';' || SQLERRM );
3370
3371 resultout := wf_engine.eng_completed || ':' || 'ERROR' ;
3372 RAISE;
3373
3374 END Activate_tasks_list;
3375
3376
3377
3378
3379 --------------------------------------------------------------------------------
3380 --Start of Comments
3381
3382 --Name: Set_Doc_TO_Prev_State
3383
3384 --Function:
3385 -- Set the document to the previuous state as its error while activating the Contract's Task list
3386
3387 --Parameters:
3388
3389 --IN:
3390 -- itemtype NUMBER
3391 -- itemkey VARCHAR2
3392 -- actid NUMBER
3393 -- funcmode VARCHAR2
3394
3395 --OUT:
3396 --resultout VARCHAR2
3397
3398 --End of Comments
3399 --------------------------------------------------------------------------------
3400
3401 PROCEDURE Set_Doc_TO_Prev_State (
3402 itemtype IN VARCHAR2
3403 , itemkey IN VARCHAR2
3404 , actid IN NUMBER
3405 , funcmode IN VARCHAR2
3406 , resultout OUT NOCOPY VARCHAR2
3407 ) IS
3408
3409 l_progress NUMBER := 0;
3410 l_document_id NUMBER;
3411 l_closeout_status VARCHAR2(50);
3412 l_results PO_MULTI_MOD_VAL_RESULTS_TYPE;
3413 l_msg VARCHAR2(30);
3414
3415 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || '.' || 'Set_Doc_TO_Prev_State';
3416 d_position NUMBER;
3417
3418 BEGIN
3419 d_position := 0;
3420 IF (PO_LOG.d_proc) THEN
3421 PO_LOG.proc_begin(d_module);
3422 END IF;
3423
3424 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'itemkey ' || itemkey);
3425 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'itemtype ' || itemtype);
3426
3427 resultout := wf_engine.eng_completed || ':' || 'ACTIVITY_PERFORMED' ;
3428
3429 IF (PO_LOG.d_proc) THEN PO_LOG.proc_end(d_module); END IF;
3430
3431 EXCEPTION
3432 WHEN OTHERS THEN
3433
3434 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'PO_CLOSEOUT_PVT.Set_Doc_TO_Prev_State : when others exception at '
3435 || l_progress || ';' || SQLERRM );
3436 resultout := c_result_type_FAILURE;
3437
3438 resultout := wf_engine.eng_completed || ':' || 'ACTIVATED' ;
3439
3440
3441 RAISE;
3442 END Set_Doc_TO_Prev_State ;
3443
3444
3445 --------------------------------------------------------------------------------
3446 --Start of Comments
3447
3448 --Name: IS_STATUS_PHYSICAL_COMPLETE
3449
3450 --Function:
3451 -- Activating the Contract's Task list
3452
3453 --Parameters:
3454
3455 --IN:
3456 -- itemtype NUMBER
3457 -- itemkey VARCHAR2
3458 -- actid NUMBER
3459 -- funcmode VARCHAR2
3460
3461 --OUT:
3462 --resultout VARCHAR2
3463
3464 --End of Comments
3465 --------------------------------------------------------------------------------
3466
3467 PROCEDURE IS_STATUS_PHYSICAL_COMPLETE (
3468 itemtype IN VARCHAR2
3469 , itemkey IN VARCHAR2
3470 , actid IN NUMBER
3471 , funcmode IN VARCHAR2
3472 , resultout OUT NOCOPY VARCHAR2
3473 ) IS
3474
3475 l_progress NUMBER := 0;
3476 l_document_id NUMBER;
3477 l_is_physical_complete VARCHAR2(1) := 'N';
3478 l_results PO_MULTI_MOD_VAL_RESULTS_TYPE;
3479 l_msg VARCHAR2(30);
3480
3481 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || '.' || 'IS_STATUS_PHYSICAL_COMPLETE';
3482 d_position NUMBER;
3483
3484 BEGIN
3485 d_position := 0;
3486 IF (PO_LOG.d_proc) THEN
3487 PO_LOG.proc_begin(d_module);
3488 END IF;
3489
3490 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'itemkey ' || itemkey);
3491 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'itemtype ' || itemtype);
3492
3493
3494
3495 l_document_id := wf_engine.GetItemAttrNumber
3496 (itemtype => itemtype,
3497 itemkey => itemkey,
3498 aname => 'DOCUMENT_ID');
3499
3500 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'l_document_id ' || l_document_id);
3501
3502 l_progress := 10;
3503
3504 BEGIN
3505
3506 SELECT 'Y'
3507 INTO l_is_physical_complete
3508 FROM po_headers_all ph
3509 WHERE ph.po_header_id = l_document_id
3510 AND ph.clm_closeout_status = 'PHYSICAL_COMPLETE';
3511
3512 EXCEPTION WHEN No_Data_Found THEN
3513 l_is_physical_complete := 'N';
3514 END;
3515
3516 resultout := wf_engine.eng_completed || ':' || l_is_physical_complete;
3517
3518 IF (PO_LOG.d_proc) THEN PO_LOG.proc_end(d_module); END IF;
3519
3520 EXCEPTION
3521 WHEN OTHERS THEN
3522
3523 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'PO_CLOSEOUT_PVT.IS_STATUS_PHYSICAL_COMPLETE : when others exception at '
3524 || l_progress || ';' || SQLERRM );
3525 resultout := wf_engine.eng_completed || ':' || 'N';
3526 RAISE;
3527
3528 END IS_STATUS_PHYSICAL_COMPLETE;
3529
3530
3531
3532 --------------------------------------------------------------------------------
3533 --Start of Comments
3534
3535 --Name: get_user_name
3536
3537 --Function:
3538 -- Get the user name.
3539
3540 --Parameters:
3541
3542 --IN:
3543 --p_employee_id NUMBER,
3544 --x_username NOCOPY VARCHAR2,
3545 --x_user_display_name NOCOPY VARCHAR2
3546
3547 --OUT:
3548
3549 --End of Comments
3550 --------------------------------------------------------------------------------
3551
3552 PROCEDURE get_user_name
3553 (
3554 p_employee_id IN NUMBER,
3555 x_username OUT NOCOPY VARCHAR2,
3556 x_user_display_name OUT NOCOPY VARCHAR2)
3557 IS
3558 p_orig_system VARCHAR2(20);
3559
3560 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || '.' || 'get_user_name';
3561 d_position NUMBER;
3562
3563
3564 BEGIN
3565 d_position := 0;
3566 IF (PO_LOG.d_proc) THEN
3567 PO_LOG.proc_begin(d_module);
3568 END IF;
3569
3570 p_orig_system:= 'PER';
3571 WF_DIRECTORY.GetUserName(p_orig_system, p_employee_id, x_username, x_user_display_name);
3572
3573 IF (PO_LOG.d_proc) THEN PO_LOG.proc_end(d_module); END IF;
3574
3575 EXCEPTION
3576 WHEN OTHERS THEN
3577 wf_core.context('PO_CLOSEOUTOUT_PVT','get_user_name',TO_CHAR(p_employee_id));
3578 raise;
3579 END get_user_name;
3580
3581
3582
3583 --------------------------------------------------------------------------------
3584 --Start of Comments
3585
3586 --Name: get_administrator_user_name
3587
3588 --Function:
3589 -- Get the admin user name.
3590
3591 --Parameters:
3592
3593 --IN:
3594 -- itemtype VARCHAR2
3595 -- itemkey VARCHAR2
3596
3597 --OUT:
3598 --resultout VARCHAR2
3599
3600 --End of Comments
3601 --------------------------------------------------------------------------------
3602
3603 FUNCTION get_administrator_user_name
3604 (
3605 itemtype IN VARCHAR2,
3606 itemkey IN VARCHAR2)
3607 RETURN VARCHAR2
3608 IS
3609 l_name VARCHAR2(100);
3610 l_administrator_id NUMBER;
3611 l_disp VARCHAR2(240);
3612
3613 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || '.' || 'get_administrator_user_name';
3614 d_position NUMBER;
3615
3616 BEGIN
3617 d_position := 0;
3618 IF (PO_LOG.d_proc) THEN
3619 PO_LOG.proc_begin(d_module);
3620 END IF;
3621
3622 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'itemkey ' || itemkey);
3623 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'itemtype ' || itemtype);
3624
3625 l_administrator_id := wf_engine.GetItemAttrNumber (itemtype => itemtype, itemkey => itemkey, aname => 'ADMINISTRATOR_ID');
3626 get_user_name(l_administrator_id, l_name, l_disp);
3627
3628 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'l_name ' || l_name);
3629 IF (PO_LOG.d_proc) THEN PO_LOG.proc_end(d_module); END IF;
3630
3631 RETURN(l_name);
3632 END;
3633
3634
3635
3636 --------------------------------------------------------------------------------
3637 --Start of Comments
3638
3639 --Name: get_sign_notfn_body
3640
3641 --Function:
3642 -- Get the signer name based on the input p_signer_type.
3643
3644 --Parameters:
3645
3646 --IN:
3647 -- itemtype VARCHAR2
3648 -- itemkey VARCHAR2
3649 -- p_signer_type VARCHAR2
3650
3651 --OUT:
3652 --resultout VARCHAR2
3653
3654 --End of Comments
3655 --------------------------------------------------------------------------------
3656 FUNCTION get_signer
3657 (
3658 p_itemtype IN VARCHAR2,
3659 p_itemkey IN VARCHAR2,
3660 p_signer_type VARCHAR2)
3661 RETURN VARCHAR2
3662 IS
3663 l_name VARCHAR2(100);
3664 l_signer_id NUMBER;
3665 l_disp_name VARCHAR2(240);
3666
3667 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || '.' || 'get_signer';
3668 d_position NUMBER;
3669
3670 BEGIN
3671
3672 d_position := 0;
3673 IF (PO_LOG.d_proc) THEN
3674 PO_LOG.proc_begin(d_module);
3675 END IF;
3676
3677 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'p_itemtype ' || p_itemtype);
3678 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'p_itemkey ' || p_itemkey);
3679 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'p_signer_type ' || p_signer_type);
3680
3681 IF ( p_signer_type = 'CHECKLIST') THEN
3682 l_signer_id := wf_engine.GetItemAttrNumber (itemtype => p_itemtype, itemkey => p_itemkey, aname => 'CHECK_LIST_SIGNER_ID');
3683 ELSIF ( p_signer_type = 'ADMIN_OFFICER') THEN
3684 l_signer_id := wf_engine.GetItemAttrNumber (itemtype => p_itemtype, itemkey => p_itemkey, aname => 'ADMIN_OFFICE_SIGNER_ID');
3685 ELSIF ( p_signer_type = 'CONTRACTING_OFFICER') THEN
3686 l_signer_id := wf_engine.GetItemAttrNumber (itemtype => p_itemtype, itemkey => p_itemkey, aname => 'CONTRACTING_OFFICE_SIGNER_ID');
3687 END IF;
3688
3689 get_user_name(l_signer_id, l_name, l_disp_name);
3690
3691 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'l_name ' || l_name);
3692 IF (PO_LOG.d_proc) THEN PO_LOG.proc_end(d_module); END IF;
3693
3694 RETURN(l_name);
3695 END get_signer;
3696
3697 --------------------------------------------------------------------------------
3698 --Start of Comments
3699
3700 --Name: get_sign_notfn_body
3701
3702 --Function:
3703 -- Get the notification body for the Signature type message. This is same for Checklist and CCS Signatures.
3704
3705 --Parameters:
3706
3707 --IN:
3708 -- itemtype NUMBER
3709 -- itemkey VARCHAR2
3710 -- actid NUMBER
3711 -- funcmode VARCHAR2
3712
3713 -- OUT:
3714
3715 --End of Comments
3716 --------------------------------------------------------------------------------
3717
3718 PROCEDURE get_sign_notfn_body
3719 (
3720 document_id IN VARCHAR2,
3721 display_type IN VARCHAR2,
3722 document IN OUT NOCOPY CLOB,
3723 document_type IN OUT NOCOPY VARCHAR2
3724 )
3725 IS
3726 l_doc_type_disp VARCHAR2(30);
3727 l_revision_num VARCHAR2(30);
3728 l_clm_document_number VARCHAR2(30);
3729 l_vendor_name VARCHAR2(30);
3730 l_comments VARCHAR2(254);
3731 l_buyer_id NUMBER;
3732 l_admin_id NUMBER;
3733 l_buyer_name VARCHAR2(30);
3734 l_buyer_dsp_name VARCHAR2(30);
3735
3736 l_msgbody VARCHAR2(32000);
3737 l_progress VARCHAR2(300);
3738
3739 l_document_id PO_HEADERS_ALL.po_header_id%TYPE;
3740 l_item_type WF_ITEMS.item_type%TYPE;
3741 l_item_key WF_ITEMS.item_key%TYPE;
3742 l_firstcolon NUMBER;
3743 l_secondcolon NUMBER;
3744 l_itemtype WF_ITEMS.item_type%TYPE;
3745 l_itemkey WF_ITEMS.item_key%TYPE;
3746
3747 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || '.' || 'get_sign_notfn_body';
3748 d_position NUMBER;
3749 BEGIN
3750
3751 d_position := 0;
3752 IF (PO_LOG.d_proc) THEN
3753 PO_LOG.proc_begin(d_module);
3754 END IF;
3755
3756 l_firstcolon := instr(document_id, ':');
3757 l_secondcolon := instr(document_id, ':', 1,2);
3758 l_document_id := to_number(substr(document_id, 1, l_firstcolon - 1));
3759 l_itemtype := substr(document_id, l_firstcolon + 1, l_secondcolon - l_firstcolon - 1);
3760 l_itemkey := substr(document_id, l_secondcolon+1,length(document_id) - l_secondcolon);
3761
3762
3763 SELECT PHA.clm_document_number,
3764 PHA.revision_num,
3765 PHA.comments,
3766 VO.vendor_name,
3767 PHA.agent_id,
3768 Nvl(PHA.clm_award_administrator, PHA.agent_id)
3769 INTO
3770 l_clm_document_number,
3771 l_revision_num,
3772 l_comments,
3773 l_vendor_name,
3774 l_buyer_id,
3775 l_admin_id
3776
3777 FROM PO_HEADERS_ALL PHA,
3778 PO_VENDORS VO
3779 WHERE PHA.po_header_id = l_document_id
3780 AND PHA.vendor_id = VO.vendor_id;
3781
3782 l_doc_type_disp:= PO_DOC_STYLE_PVT.get_style_display_name(l_document_id);
3783
3784 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'l_admin_id ' || l_admin_id);
3785 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'l_clm_document_number ' || l_clm_document_number);
3786 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'l_revision_num ' || l_revision_num);
3787 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'l_comments ' || l_comments);
3788 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'l_vendor_name ' || l_vendor_name);
3789 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'l_buyer_id ' || l_buyer_id);
3790
3791 get_user_name(l_buyer_id, l_buyer_name,l_buyer_dsp_name);
3792
3793 l_msgbody := '<html><style> .tableHeaderCell { font-family: Arial; font-size: 10pt;} .tableDataCell { font-family: Arial; font-size: 10pt; font-weight: bold; }</style>
3794 <body class="tableHeaderCell"><table><tr><td class="tableHeaderCell" align="right"> '||fnd_message.get_string('PO', 'PO_WF_SIGN_NOTIF_DOCTYPE')||' : </td>
3795 <td class="tableDataCell"> ' || l_doc_type_disp || ' </td>
3796 <td class="tableHeaderCell" align="right"> '||fnd_message.get_string('PO', 'PO_WF_SIGN_NOTIF_PO_NUMBER') ||': </td>
3797 <td class="tableDataCell"> ' ||l_clm_document_number||','|| l_revision_num || ' </td></tr>
3798 <tr><td class="tableHeaderCell" align="right"> '||fnd_message.get_string('PO', 'PO_WF_SIGN_NOTIF_SUPPLIER')||' : </td>';
3799
3800 l_msgbody := l_msgbody || '<td class="tableDataCell"> ' || l_vendor_name || ' </td><td class="tableHeaderCell" align="right">
3801 '||fnd_message.get_string('PO', 'PO_WF_SIGN_NOTIF_BUYER') ||': </td>
3802 <td class="tableDataCell"> ' || l_buyer_dsp_name || ' </td> </tr> <tr>';
3803
3804 l_msgbody := l_msgbody || '</tr> <tr>
3805 <td class="tableHeaderCell" align="right"> '||fnd_message.get_string('PO', 'PO_WF_SIGN_NOTIF_DESC')||' :</td>
3806 <td COLSPAN="3" class="tableDataCell">' || l_comments || ' </td></tr> </table>';
3807
3808 l_msgbody := l_msgbody || '</body></html>';
3809
3810 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'PO_CLOSEOUT_PVT.get_sign_notfn_body: 02');
3811
3812 WF_NOTIFICATION.WriteToClob(document, l_msgbody);
3813
3814 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'PO_CLOSEOUT_PVT.get_sign_notfn_body: 03');
3815
3816 l_progress := 1;
3817
3818 IF (PO_LOG.d_proc) THEN PO_LOG.proc_end(d_module); END IF;
3819
3820 EXCEPTION
3821 WHEN OTHERS THEN
3822 --l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(l_item_type, l_item_key);
3823 --l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(l_item_type, l_item_key);
3824 WF_CORE.context('PO_CLOSEOUT_PVT','get_sign_notfn_body',l_progress);
3825 --PO_REQAPPROVAL_INIT1.send_error_notif(l_item_type, l_item_key, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_SIGNATURE_PVT.GET_SIGNATURE_NOTFN_BODY');
3826 RAISE;
3827
3828 END get_sign_notfn_body;
3829
3830
3831
3832
3833 --------------------------------------------------------------------------------
3834 --Start of Comments
3835
3836 --Name: start_closeout_wf_process
3837
3838 --Function:
3839 -- Starts the Closeout WF process and the sets the attributes
3840
3841 --End of Comments
3842 --------------------------------------------------------------------------------
3843
3844 PROCEDURE start_closeout_wf_process (
3845 p_doc_id IN NUMBER,
3846 x_return_status OUT NOCOPY VARCHAR2
3847 )IS
3848 l_progress NUMBER := 0;
3849 l_document_id NUMBER;
3850 l_chklist_req VARCHAR2(1);
3851 l_ccs_req VARCHAR2(1);
3852 l_chklist_sign_req VARCHAR2(1);
3853 l_ccs_sign_req VARCHAR2(1);
3854 l_aco_sign_type VARCHAR2(25);
3855 l_pco_sign_type VARCHAR2(25);
3856 l_chklist_signer NUMBER;
3857 l_admin_signer NUMBER;
3858 l_cntr_signer NUMBER;
3859 l_admin_id NUMBER;
3860 l_itemkey VARCHAR2(50);
3861 l_itemtype VARCHAR2(50);
3862 l_process VARCHAR2(50);
3863 l_aco_remarks VARCHAR2(4000);
3864 l_pco_remarks VARCHAR2(4000);
3865 l_comments VARCHAR2(4000);
3866 l_msg_data VARCHAR2(2000);
3867
3868 l_user_id NUMBER;
3869 l_resp_id NUMBER;
3870 l_appl_id NUMBER;
3871
3872 l_clm_document_number PO_HEADERS_ALL.clm_document_number%TYPE;
3873 l_doc_type_disp VARCHAR2(50);
3874 l_clm_closeout_status PO_HEADERS_ALL.clm_closeout_status%type;
3875
3876 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || '.' || 'start_closeout_wf_process';
3877 d_position NUMBER;
3878
3879 BEGIN
3880 d_position := 0;
3881 IF (PO_LOG.d_proc) THEN
3882 PO_LOG.proc_begin(d_module);
3883 END IF;
3884
3885 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'p_doc_id ' || p_doc_id);
3886
3887 BEGIN
3888
3889 SELECT reference_doc_id, check_list_required, ccs_required, check_list_sign_required,
3890 ccs_sign_required, aco_signature_type, pco_signature_type,
3891 check_list_signer_id, aco_signer_id, pco_signer_id, aco_remarks, pco_remarks
3892 INTO l_document_id, l_chklist_req, l_ccs_req, l_chklist_sign_req, l_ccs_sign_req,
3893 l_aco_sign_type, l_pco_sign_type, l_chklist_signer, l_admin_signer,
3894 l_cntr_signer,l_aco_remarks, l_pco_remarks
3895 FROM PO_CLOSEOUT_DETAILS
3896 WHERE reference_doc_id = p_doc_id
3897 AND incloseout = 'Y'
3898 AND closeout_doc_type = PO_CLOSEOUT_PVT.g_CLOSEOUT_TYPE_INDIVIDUAL
3899 FOR UPDATE ;
3900
3901 EXCEPTION
3902 WHEN No_Data_Found THEN
3903 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'No records found to start workflow. Exited ');
3904 x_return_status := FND_API.G_RET_STS_SUCCESS;
3905 RETURN;
3906
3907 END;
3908 l_progress := 10;
3909
3910 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'l_document_id ' || l_document_id);
3911 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'l_chklist_req ' || l_chklist_req);
3912 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'l_ccs_req ' || l_ccs_req);
3913 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'l_chklist_sign_req ' || l_chklist_sign_req);
3914 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'l_ccs_sign_req ' || l_ccs_sign_req);
3915 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'l_aco_sign_type ' || l_aco_sign_type);
3916 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'l_pco_sign_type ' || l_pco_sign_type);
3917 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'l_chklist_signer ' || l_chklist_signer);
3918 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'l_admin_signer ' || l_admin_signer);
3919 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'l_cntr_signer ' || l_cntr_signer);
3920 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'l_aco_remarks ' || l_aco_remarks);
3921 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'l_pco_remarks ' || l_pco_remarks);
3922
3923 l_progress := 20;
3924
3925 SELECT PHA.clm_document_number,
3926 Nvl(PHA.clm_award_administrator, PHA.agent_id),
3927 PHA.clm_closeout_status
3928 INTO
3929 l_clm_document_number,
3930 l_admin_id,
3931 l_clm_closeout_status
3932
3933 FROM PO_HEADERS_ALL PHA
3934 WHERE PHA.po_header_id = p_doc_id;
3935
3936 l_doc_type_disp:= PO_DOC_STYLE_PVT.get_style_display_name(p_doc_id);
3937
3938 l_process := 'PO_INIT_CLOSEOUT';
3939 l_itemtype := 'POCLSOUT';
3940 l_itemkey := l_itemtype || '_KEY_' || TO_CHAR(SYSDATE, 'MMDDYYYY_HH24MISS')
3941 || '_' || p_doc_id;
3942
3943 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'l_clm_document_number ' || l_clm_document_number);
3944 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'l_admin_id ' || l_admin_id);
3945 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'l_clm_closeout_status ' || l_clm_closeout_status);
3946 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'l_itemkey ' || l_itemkey);
3947
3948 -- create workflow process
3949 wf_engine.createprocess (itemtype => l_itemtype,
3950 itemkey => l_itemkey,
3951 process => l_process);
3952
3953 wf_engine.setitemattrtext(itemtype => l_itemtype,
3954 itemkey => l_itemkey,
3955 aname => 'DOCUMENT_ID',
3956 avalue => l_document_id);
3957
3958 wf_engine.setitemattrtext(itemtype => l_itemtype,
3959 itemkey => l_itemkey,
3960 aname => 'IS_CHECK_LIST_REQUIRED',
3961 avalue => l_chklist_req);
3962
3963 wf_engine.setitemattrtext(itemtype => l_itemtype,
3964 itemkey => l_itemkey,
3965 aname => 'IS_CCS_REQUIRED',
3966 avalue => l_ccs_req);
3967
3968 wf_engine.setitemattrtext(itemtype => l_itemtype,
3969 itemkey => l_itemkey,
3970 aname => 'IS_CHECK_LIST_SIGN_REQUIRED',
3971 avalue => l_chklist_sign_req);
3972
3973 wf_engine.setitemattrtext(itemtype => l_itemtype,
3974 itemkey => l_itemkey,
3975 aname => 'IS_CCS_SIGN_REQUIRED',
3976 avalue => l_ccs_sign_req);
3977
3978 wf_engine.setitemattrtext(itemtype => l_itemtype,
3979 itemkey => l_itemkey,
3980 aname => 'ACO_SIGNATURE_TYPE',
3981 avalue => l_aco_sign_type);
3982
3983 wf_engine.setitemattrtext(itemtype => l_itemtype,
3984 itemkey => l_itemkey,
3985 aname => 'PCO_SIGNATURE_TYPE',
3986 avalue => l_pco_sign_type);
3987
3988 wf_engine.setitemattrtext(itemtype => l_itemtype,
3989 itemkey => l_itemkey,
3990 aname => 'CHECK_LIST_SIGNER_ID',
3991 avalue => l_chklist_signer);
3992
3993 wf_engine.setitemattrtext(itemtype => l_itemtype,
3994 itemkey => l_itemkey,
3995 aname => 'ADMIN_OFFICE_SIGNER_ID',
3996 avalue => l_admin_signer);
3997
3998 wf_engine.setitemattrtext(itemtype => l_itemtype,
3999 itemkey => l_itemkey,
4000 aname => 'CONTRACTING_OFFICE_SIGNER_ID',
4001 avalue => l_cntr_signer);
4002
4003 wf_engine.setitemattrtext(itemtype => l_itemtype,
4004 itemkey => l_itemkey,
4005 aname => 'ADMINISTRATOR_ID',
4006 avalue => l_admin_id);
4007
4008
4009 wf_engine.setitemattrtext(itemtype => l_itemtype,
4010 itemkey => l_itemkey,
4011 aname => 'ADMINISTRATOR_NAME',
4012 avalue => get_administrator_user_name(l_itemtype, l_itemkey));
4013
4014 wf_engine.setitemattrtext(itemtype => l_itemtype,
4015 itemkey => l_itemkey,
4016 aname => 'CHECK_LIST_SIGNER',
4017 avalue => get_signer(l_itemtype, l_itemkey,'CHECKLIST'));
4018
4019
4020 wf_engine.setitemattrtext(itemtype => l_itemtype,
4021 itemkey => l_itemkey,
4022 aname => 'ADMIN_OFFICE_SIGNER',
4023 avalue => get_signer(l_itemtype, l_itemkey,'ADMIN_OFFICER'));
4024
4025 wf_engine.setitemattrtext(itemtype => l_itemtype,
4026 itemkey => l_itemkey,
4027 aname => 'CONTRACTING_OFFICE_SIGNER',
4028 avalue => get_signer(l_itemtype, l_itemkey, 'CONTRACTING_OFFICER'));
4029
4030 wf_engine.setitemattrtext(itemtype => l_itemtype,
4031 itemkey => l_itemkey,
4032 aname => 'CLM_DOCUMENT_NUMBER',
4033 avalue => l_clm_document_number);
4034
4035 wf_engine.setitemattrtext(itemtype => l_itemtype,
4036 itemkey => l_itemkey,
4037 aname => 'DOCUMENT_TYPE_DISP',
4038 avalue => l_doc_type_disp);
4039
4040
4041 wf_engine.setitemattrtext(itemtype => l_itemtype,
4042 itemkey => l_itemkey,
4043 aname => 'SIGNATURE_COMMENTS',
4044 avalue => l_comments);
4045
4046 wf_engine.setitemattrtext(itemtype => l_itemtype,
4047 itemkey => l_itemkey,
4048 aname => 'ACO_REMARKS',
4049 avalue => l_aco_remarks);
4050
4051 wf_engine.setitemattrtext(itemtype => l_itemtype,
4052 itemkey => l_itemkey,
4053 aname => 'PCO_REMARKS',
4054 avalue => l_pco_remarks);
4055
4056 -- Testing Hard coded
4057
4058 -- wf_engine.setitemattrtext(itemtype => l_itemtype,
4059 -- itemkey => l_itemkey,
4060 -- aname => 'REVALIDATE_DOCUMENT',
4061 -- avalue => 'Y');
4062
4063
4064 wf_engine.setitemattrtext(itemtype => l_itemtype,
4065 itemkey => l_itemkey,
4066 aname => 'PO_CLSOUT_SIGNATURE_BODY',
4067 avalue =>
4068 'PLSQLCLOB:PO_CLOSEOUT_PVT.get_sign_notfn_body/'|| l_document_id ||':'||l_itemtype||':'||l_itemkey);
4069
4070
4071 wf_engine.setitemattrtext(itemtype => l_itemtype,
4072 itemkey => l_itemkey,
4073 aname => 'PDF_CHECKLIST_ATTACHMENT',
4074 avalue =>
4075 'PLSQLBLOB:PO_CLOSEOUT_PVT.get_closeout_pdf/'|| l_document_id ||':'||l_itemtype||':'||'CT');
4076
4077
4078 wf_engine.setitemattrtext(itemtype => l_itemtype,
4079 itemkey => l_itemkey,
4080 aname => 'PDF_CCS_ATTACHMENT',
4081 avalue =>
4082 'PLSQLBLOB:PO_CLOSEOUT_PVT.get_closeout_pdf/'|| l_document_id ||':'||l_itemtype||':'||'CCS');
4083
4084 wf_engine.setitemattrtext(itemtype => l_itemtype,
4085 itemkey => l_itemkey,
4086 aname => 'VIEW_DOC_CLOSEOUT_URL',
4087 avalue => 'JSP:/OA_HTML/OA.jsp?OAFunc=MANAGE_CLOSEOUT'||'&'||
4088 'documentHeaderId='||l_document_id||'&'||
4089 'documentNumber='||l_clm_document_number||'&'||
4090 'poDocumentSubtype='||l_doc_type_disp||
4091 'poCallingModule=notification' || '&' ||
4092 'poCallingNotifId=-&#NID-' || '&' ||
4093 'retainAM=Y' || '&' ||
4094 'addBreadCrumb=Y'
4095 );
4096
4097 l_progress := 30 ;
4098
4099 WF_ENGINE.STARTPROCESS(ITEMTYPE => L_ITEMTYPE,
4100 ITEMKEY => L_ITEMKEY );
4101
4102 l_progress := 40;
4103
4104 IF l_chklist_req <> 'Y' THEN
4105 PO_CLOSEOUT_PVT.delete_contract(p_doc_id => p_doc_id,
4106 p_closeout_type => PO_CLOSEOUT_PVT.g_CLOSEOUT_TYPE_INDIVIDUAL,
4107 x_return_status => x_return_status,
4108 x_return_msg => l_msg_data);
4109 l_progress := 45;
4110 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'Delete contract status ' || x_return_status);
4111 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'return message from Delete contract : ' || l_msg_data);
4112 END IF;
4113
4114 -- If the workflow is launced again for this document. This is the case when its launched after completing all the tasks from business event
4115 IF ('PHYSICAL_COMPLETE' = l_clm_closeout_status) THEN
4116
4117 UPDATE po_closeout_details
4118 SET incloseout = 'W' -- Workflow
4119 WHERE reference_doc_id = p_doc_id
4120 AND incloseout = 'Y'
4121 AND closeout_doc_type = PO_CLOSEOUT_PVT.g_CLOSEOUT_TYPE_INDIVIDUAL;
4122
4123 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'Updated incloseout to W '||SQL%ROWCOUNT);
4124
4125 -- Set the REVALIDATE_DOCUMENT to Y, as this is invoked from business event after all tasks has been completed and it needs revalidation.
4126 wf_engine.setitemattrtext(itemtype => l_itemtype,
4127 itemkey => l_itemkey,
4128 aname => 'REVALIDATE_DOCUMENT',
4129 avalue => 'Y');
4130
4131 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'Set REVALIDATE_DOCUMENT to Y ');
4132 ELSE
4133
4134 l_user_id := fnd_global.user_id;
4135 l_resp_id := fnd_global.resp_id;
4136 l_appl_id := fnd_global.resp_appl_id;
4137
4138 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'l_user_id ' || l_user_id);
4139 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'l_resp_id ' || l_resp_id);
4140 PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'l_appl_id ' || l_appl_id);
4141
4142 UPDATE po_closeout_details
4143 SET attribute15 = l_user_id||':'||l_resp_id||':'||l_appl_id
4144 WHERE reference_doc_id = p_doc_id
4145 AND incloseout = 'Y'
4146 AND closeout_doc_type = PO_CLOSEOUT_PVT.g_CLOSEOUT_TYPE_INDIVIDUAL;
4147
4148 END IF;
4149
4150 x_return_status := FND_API.G_RET_STS_SUCCESS;
4151
4152 IF (PO_LOG.d_proc) THEN PO_LOG.proc_end(d_module); END IF;
4153
4154 EXCEPTION
4155 WHEN OTHERS THEN
4156 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4157
4158 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'PO_CLOSEOUT_PVT.start_closeout_wf_process : when others exception at '
4159 || l_progress || ';' || SQLERRM );
4160 RAISE;
4161 END start_closeout_wf_process;
4162
4163
4164 --------------------------------------------------------------------------------
4165 --Start of Comments
4166
4167 --Name: Generate_Closeout_PDF
4168
4169 --Function:
4170 -- Generate Closeout PDFs CCS and CheckList"
4171
4172 --Parameters:
4173
4174 --IN:
4175 -- itemtype NUMBER
4176 -- itemkey VARCHAR2
4177 -- actid NUMBER
4178 -- funcmode VARCHAR2
4179
4180 --OUT:
4181 --resultout VARCHAR2
4182
4183 --End of Comments
4184 --------------------------------------------------------------------------------
4185
4186
4187 PROCEDURE Generate_Closeout_PDF (
4188 itemtype IN VARCHAR2
4189 , itemkey IN VARCHAR2
4190 , actid IN NUMBER
4191 , funcmode IN VARCHAR2
4192 , resultout OUT NOCOPY VARCHAR2
4193 )
4194 IS
4195
4196 l_document_id NUMBER;
4197 l_clm_document_number VARCHAR2(50);
4198 l_pdf_generation_type VARCHAR2(50);
4199 l_request_id NUMBER;
4200
4201 l_session_user_id NUMBER;
4202 l_session_resp_id NUMBER;
4203 l_user_id_to_set NUMBER;
4204 l_resp_id_to_set NUMBER;
4205 l_appl_id_to_set NUMBER;
4206 l_counter NUMBER;
4207 l_context_ids VARCHAR2(30);
4208
4209 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || '.' || 'Generate_Closeout_PDF';
4210 d_position NUMBER;
4211
4212
4213 BEGIN
4214
4215 d_position := 0;
4216 IF (PO_LOG.d_proc) THEN
4217 PO_LOG.proc_begin(d_module);
4218 END IF;
4219
4220 l_document_id := wf_engine.GetItemAttrNumber (itemtype => itemtype, itemkey => itemkey, aname => 'DOCUMENT_ID');
4221 l_clm_document_number := wf_engine.GetItemAttrText (itemtype => itemtype, itemkey => itemkey, aname => 'CLM_DOCUMENT_NUMBER');
4222 l_pdf_generation_type := wf_engine.GetActivityAttrText (itemtype => itemtype, itemkey => itemkey, actid => actid, aname => 'PDF_GENERATION_TYPE');
4223
4224 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'l_document_id: '||l_document_id);
4225 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'l_clm_document_number: '||l_clm_document_number);
4226 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'l_pdf_generation_type: '||l_pdf_generation_type);
4227
4228 l_session_user_id := fnd_global.user_id;
4229
4230 l_session_resp_id := fnd_global.resp_id;
4231
4232 IF ( l_session_user_id = -1 ) THEN
4233 l_session_user_id := NULL;
4234 END IF;
4235
4236 IF ( l_session_resp_id = -1 ) THEN
4237 l_session_resp_id := NULL;
4238 END IF;
4239
4240 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'l_session_user_id: '||l_session_user_id);
4241 PO_CLOSEOUT_PVT.Log( d_module, d_position, ' l_session_resp_id: '|| l_session_resp_id);
4242 -- This context values will actually be taken care by the callback procedure poclsout_selector of this workflow .
4243 -- But this piece of code is written again to initiliase context, as the callback(TEST_CTX) is not been called in few scenerios
4244 -- while the multiple notifications are been opened in same browser session by the same user.
4245 -- Just to make sure, we are setting the context again only in case no context exists.
4246 IF ( l_session_resp_id IS NULL OR l_session_user_id IS NULL ) THEN
4247
4248 SELECT ATTRIBUTE15
4249 INTO l_context_ids
4250 FROM po_closeout_details
4251 WHERE reference_doc_id = l_document_id
4252 AND closeout_doc_type = PO_CLOSEOUT_PVT.g_CLOSEOUT_TYPE_INDIVIDUAL;
4253
4254 l_counter := 0;
4255
4256 FOR context_cur IN
4257 (
4258 SELECT regexp_substr(l_context_ids,'[^:]+', 1, LEVEL) internal_id FROM dual
4259 CONNECT BY regexp_substr(l_context_ids, '[^:]+', 1, LEVEL) IS NOT NULL
4260
4261 ) LOOP
4262 l_counter := l_counter +1;
4263 IF(l_counter = 1 ) THEN
4264 l_user_id_to_set := To_Number(context_cur.internal_id);
4265 ELSIF(l_counter = 2 ) THEN
4266 l_resp_id_to_set := To_Number(context_cur.internal_id);
4267 ELSIF(l_counter = 3 ) THEN
4268 l_appl_id_to_set := To_Number(context_cur.internal_id);
4269 END IF;
4270
4271 END LOOP;
4272
4273
4274 PO_CLOSEOUT_PVT.Log( d_module, d_position, ' l_user_id_to_set'||l_user_id_to_set);
4275 PO_CLOSEOUT_PVT.Log( d_module, d_position, ' l_resp_id_to_set'||l_resp_id_to_set);
4276 PO_CLOSEOUT_PVT.Log( d_module, d_position, ' l_appl_id_to_set'||l_appl_id_to_set);
4277
4278 fnd_global.Apps_initialize
4279 (
4280 l_user_id_to_set,
4281 l_resp_id_to_set,
4282 l_appl_id_to_set
4283 );
4284
4285 END IF;
4286
4287
4288 --Submitting concurrent program 'Generate Closeout PDF'
4289 l_request_id := fnd_request.submit_request(application =>'PO',
4290 program =>'POCLSOUTPDF',
4291 description => NULL ,
4292 start_time => SYSDATE,
4293 sub_request => FALSE,
4294 argument1 => l_document_id,
4295 argument2 => l_pdf_generation_type,
4296 argument3 => l_clm_document_number);
4297
4298 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'PO_CLOSEOUT_PVT.Generate_Closeout_PDF: : Request id is - '|| l_request_id);
4299
4300 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
4301 itemkey => itemkey,
4302 aname => 'REQUEST_ID',
4303 avalue => l_request_id);
4304 wf_engine.SetItemAttrText ( itemtype => itemtype,
4305 itemkey => itemkey,
4306 aname => 'PDF_TYPE',
4307 avalue => l_pdf_generation_type);
4308
4309
4310 resultout := wf_engine.eng_completed || ':' || 'ACTIVITY_PERFORMED';
4311 IF (PO_LOG.d_proc) THEN PO_LOG.proc_end(d_module); END IF;
4312
4313 EXCEPTION
4314 WHEN OTHERS THEN
4315 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'PO_CLOSEOUT_PVT.Generate_Closeout_PDF: Error: '||SQLERRM);
4316 resultout := wf_engine.eng_completed || ':' || '';
4317
4318 END Generate_Closeout_PDF;
4319
4320
4321
4322 --------------------------------------------------------------------------------
4323 --Start of Comments
4324
4325 --Name: Set_Notification_Message
4326
4327 --Function:
4328 -- Set the subject and the body for the notification based on the MessageType attribute.
4329
4330 --Parameters:
4331
4332 --IN:
4333 -- itemtype NUMBER
4334 -- itemkey VARCHAR2
4335 -- actid NUMBER
4336 -- funcmode VARCHAR2
4337
4338 --OUT:
4339 --resultout VARCHAR2
4340
4341 --End of Comments
4342 --------------------------------------------------------------------------------
4343
4344 PROCEDURE Set_Notification_Message (
4345 itemtype IN VARCHAR2
4346 , itemkey IN VARCHAR2
4347 , actid IN NUMBER
4348 , funcmode IN VARCHAR2
4349 , resultout OUT NOCOPY VARCHAR2
4350 )
4351 IS
4352 l_message_type VARCHAR2(50);
4353 l_doc_sub_type VARCHAR2(240);
4354 l_clm_document_number VARCHAR2(50);
4355 l_message_subject VARCHAR2(240);
4356 l_pdf_type VARCHAR2(240);
4357 l_document_id NUMBER;
4358
4359 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || '.' || 'Set_Notification_Message';
4360 d_position NUMBER;
4361
4362 BEGIN
4363 d_position := 0;
4364 IF (PO_LOG.d_proc) THEN
4365 PO_LOG.proc_begin(d_module);
4366 END IF;
4367
4368 l_message_type := wf_engine.GetActivityAttrText (itemtype => itemtype, itemkey => itemkey, actid => actid, aname => 'MESSAGE_TYPE');
4369 l_clm_document_number := wf_engine.GetItemAttrText (itemtype => itemtype, itemkey => itemkey, aname => 'CLM_DOCUMENT_NUMBER');
4370 l_doc_sub_type := wf_engine.GetItemAttrText (itemtype => itemtype, itemkey => itemkey, aname => 'DOCUMENT_TYPE_DISP');
4371 l_pdf_type := wf_engine.GetItemAttrText (itemtype => itemtype, itemkey => itemkey, aname => 'PDF_TYPE');
4372 l_document_id := wf_engine.GetItemAttrNumber (itemtype => itemtype, itemkey => itemkey, aname => 'DOCUMENT_ID');
4373
4374 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'l_message_type'||l_message_type);
4375 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'l_clm_document_number'||l_clm_document_number);
4376 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'l_doc_sub_type'||l_doc_sub_type);
4377 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'l_document_id'||l_document_id);
4378 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'l_pdf_type'||l_pdf_type);
4379
4380
4381 IF ( l_message_type = 'CLSOUT_TASK_ACT_ERR') THEN
4382
4383 fnd_message.set_name('PO','PO_CLSOUT_TASK_ACT_ERR_SUB');
4384 fnd_message.set_token('DOC_SUB_TYPE',l_doc_sub_type);
4385 fnd_message.set_token('CLM_DOCUMENT_NUMBER',l_clm_document_number);
4386
4387 l_message_subject := fnd_message.get;
4388
4389 wf_engine.setitemattrtext(itemtype => itemtype,
4390 itemkey => itemkey,
4391 aname => 'MESSAGE_BODY',
4392 avalue => 'PLSQLCLOB:PO_CLOSEOUT_PVT.get_clsout_err_msg_body/'|| l_message_type ||':'||itemtype||':'||itemkey);
4393
4394 ELSIF ( l_message_type = 'CLSOUT_PDF_GEN_ERR') THEN
4395
4396 fnd_message.set_name('PO','PO_CLSOUT_PDF_GEN_ERR_SUB');
4397 fnd_message.set_token('DOC_SUB_TYPE',l_doc_sub_type);
4398 fnd_message.set_token('CLM_DOCUMENT_NUMBER',l_clm_document_number);
4399 fnd_message.set_token('PDF_GENERATION_TYPE',l_pdf_type);
4400
4401 l_message_subject := fnd_message.get;
4402
4403 wf_engine.setitemattrtext(itemtype => itemtype,
4404 itemkey => itemkey,
4405 aname => 'MESSAGE_BODY',
4406 avalue => 'PLSQLCLOB:PO_CLOSEOUT_PVT.get_clsout_err_msg_body/'|| l_message_type ||':'||itemtype||':'||itemkey);
4407
4408 ELSIF ( l_message_type = 'CLSOUT_CHECKLIST_SIGN') THEN
4409
4410 fnd_message.set_name('PO','PO_CLSOUT_CHECKLIST_SIGN_SUB');
4411 fnd_message.set_token('DOC_SUB_TYPE',l_doc_sub_type);
4412 fnd_message.set_token('CLM_DOCUMENT_NUMBER',l_clm_document_number);
4413
4414 l_message_subject := fnd_message.get;
4415
4416 ELSIF ( l_message_type = 'CLSOUT_CCS_SELF_SIGN') THEN
4417
4418 fnd_message.set_name('PO','PO_CLSOUT_CCS_SELF_SIGN_SUB');
4419 fnd_message.set_token('DOC_SUB_TYPE',l_doc_sub_type);
4420 fnd_message.set_token('CLM_DOCUMENT_NUMBER',l_clm_document_number);
4421
4422 l_message_subject := fnd_message.get;
4423
4424 ELSIF ( l_message_type = 'CLSOUT_CCS_ACK_SIGN') THEN
4425
4426 fnd_message.set_name('PO','PO_CLSOUT_CCS_ACK_SIGN_SUB');
4427 fnd_message.set_token('DOC_SUB_TYPE',l_doc_sub_type);
4428 fnd_message.set_token('CLM_DOCUMENT_NUMBER',l_clm_document_number);
4429
4430 l_message_subject := fnd_message.get;
4431
4432 END IF;
4433
4434 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'l_message_subject'||l_message_subject);
4435
4436 wf_engine.setitemattrtext(itemtype => itemtype,
4437 itemkey => itemkey,
4438 aname => 'MESSAGE_SUBJECT',
4439 avalue => l_message_subject);
4440
4441
4442 resultout := wf_engine.eng_completed || ':' || 'ACTIVITY_PERFORMED' ;
4443 PO_CLOSEOUT_PVT.Log(d_module, d_position, 'End ::: PO_CLOSEOUT_PVT.Set_Notification_Message ');
4444 END Set_Notification_Message;
4445
4446
4447
4448 --------------------------------------------------------------------------------
4449 --Start of Comments
4450
4451 --Name: get_checklist_pdf
4452
4453 --Function:
4454 -- Get the pdf blob and write to the clob
4455
4456 --Parameters:
4457
4458 --IN:
4459 --messages IN VARCHAR2,
4460 --display_type IN VARCHAR2,
4461 --document IN OUT NOCOPY CLOB,
4462 --document_type IN OUT NOCOPY VARCHAR2
4463
4464
4465 -- OUT:
4466
4467 --End of Comments
4468 --------------------------------------------------------------------------------
4469 PROCEDURE get_closeout_pdf
4470 (
4471 document_id IN VARCHAR2,
4472 display_type IN VARCHAR2,
4473 document IN OUT NOCOPY BLOB,
4474 document_type IN OUT NOCOPY VARCHAR2
4475 )
4476 IS
4477
4478 l_progress VARCHAR2(300);
4479 l_document_id NUMBER;
4480 l_firstcolon NUMBER;
4481 l_secondcolon NUMBER;
4482 l_itemtype WF_ITEMS.item_type%TYPE;
4483 l_pdf_type VARCHAR2(50);
4484
4485 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || '.' || 'get_closeout_pdf';
4486 d_position NUMBER;
4487
4488 l_document_length NUMBER;
4489 l_filename fnd_lobs.file_name%TYPE;
4490 l_filecontent_type fnd_lobs.file_content_type%TYPE;
4491 l_document fnd_lobs.file_data%TYPE;
4492
4493
4494 BEGIN
4495
4496 d_position := 0;
4497 IF (PO_LOG.d_proc) THEN
4498 PO_LOG.proc_begin(d_module);
4499 END IF;
4500
4501 l_firstcolon := instr(document_id, ':');
4502 l_secondcolon := instr(document_id, ':', 1,2);
4503 l_document_id := to_number(substr(document_id, 1, l_firstcolon - 1));
4504 l_itemtype := substr(document_id, l_firstcolon + 1, l_secondcolon - l_firstcolon - 1);
4505 l_pdf_type := substr(document_id, l_secondcolon+1,length(document_id) - l_secondcolon);
4506
4507 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'l_document_id ' || l_document_id);
4508 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'l_pdf_type ' || l_pdf_type);
4509
4510
4511 -- Obtain the BLOB version of the document
4512 SELECT l.FILE_NAME, l.FILE_CONTENT_TYPE, l.FILE_DATA
4513 INTO l_filename, l_filecontent_type, l_document
4514 FROM
4515 FND_LOBS L,
4516 FND_DOCUMENTS D,
4517 FND_ATTACHED_DOCUMENTS A
4518 WHERE D.DOCUMENT_ID = A.DOCUMENT_ID
4519 AND A.ENTITY_NAME = 'PO_CLOSEOUT_DETAILS'
4520 AND D.MEDIA_ID = L.FILE_ID
4521 AND D.FILE_NAME LIKE '%'||l_pdf_type||'.pdf'
4522 AND A.PK1_VALUE =l_document_id ;
4523
4524 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'filename ' || l_filename);
4525 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'content_type ' || l_filecontent_type);
4526
4527 l_document_length := dbms_lob.GetLength(l_document);
4528
4529 dbms_lob.Copy(document, l_document, l_document_length, 1, 1);
4530
4531 document_type := l_filecontent_type ||'; name=' || l_filename;
4532
4533 IF (PO_LOG.d_proc) THEN PO_LOG.proc_end(d_module); END IF;
4534
4535 EXCEPTION
4536 WHEN OTHERS THEN
4537 wf_core.context('PO_CLOSEOUT_PVT', 'get_closeout_pdf', l_progress);
4538 RAISE;
4539
4540 END get_closeout_pdf;
4541
4542
4543
4544 -------------------------------------------------------------------------------
4545 --Start of Comments
4546 --Name: POCLSOUT_SELECTOR
4547 --Pre-reqs:
4548 -- None.
4549 --Modifies:
4550 -- Application user id
4551 -- Application responsibility id
4552 -- Application application id
4553 --Locks:
4554 -- None.
4555 --Function:
4556 -- This procedure sets the correct application context when a process is
4557 -- picked up by the workflow background engine. When called in
4558 -- TEST_CTX mode it compares workflow attribute org id with the current
4559 -- org id and workflow attributes user id, responsibility id and
4560 -- application id with their corresponding profile values. It returns TRUE
4561 -- if these values match and FALSE otherwise. When called in SET_CTX mode
4562 -- it sets the correct apps context based on workflow parameters.
4563 --Parameters:
4564 --IN:
4565 --p_itemtype
4566 -- Specifies the itemtype of the workflow process
4567 --p_itemkey
4568 -- Specifies the itemkey of the workflow process
4569 --p_actid
4570 -- activity id passed by the workflow
4571 --p_funcmode
4572 -- Input values can be TEST_CTX or SET_CTX (RUN not implemented)
4573 -- TEST_CTX to test if current context is correct
4574 -- SET_CTX to set the correct context if current context is wrong
4575 --IN OUT:
4576 --p_x_result
4577 -- For TEST_CTX a TRUE value means that the context is correct and
4578 -- SET_CTX need not be called. A FALSE value means that current context
4579 -- is incorrect and SET_CTX need to set correct context
4580 --Testing:
4581 -- There is not script to test this procedure but the correct functioning
4582 -- may be tested by verifying from the debug_message in table po_wf_debug
4583 -- that if at any time the workflow process gets started with a wrong
4584 -- context then the selector is called in TEST_CTX and SET_CTX modes and
4585 -- correct context is set.
4586 --End of Comments
4587 -------------------------------------------------------------------------------
4588 PROCEDURE Poclsout_selector (p_itemtype IN VARCHAR2,
4589 p_itemkey IN VARCHAR2,
4590 p_actid IN NUMBER,
4591 p_funcmode IN VARCHAR2,
4592 p_x_result IN OUT nocopy VARCHAR2)
4593 IS
4594 -- Context setting revamp <declare variables start>
4595 l_session_user_id NUMBER;
4596 l_session_resp_id NUMBER;
4597 l_responder_id NUMBER;
4598 l_user_id_to_set NUMBER;
4599 l_resp_id_to_set NUMBER;
4600 l_appl_id_to_set NUMBER;
4601 l_progress VARCHAR2(1000);
4602 l_preserved_ctx VARCHAR2(5) := 'TRUE';
4603 l_document_id NUMBER;
4604 l_context_ids VARCHAR2(30);
4605 l_counter NUMBER;
4606
4607 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || '.' || 'Poclsout_selector';
4608 d_position NUMBER;
4609
4610
4611 -- Context setting revamp <declare variables end>
4612 BEGIN
4613
4614 d_position := 0;
4615 IF (PO_LOG.d_proc) THEN
4616 PO_LOG.proc_begin(d_module);
4617 END IF;
4618
4619 --Context setting revamp <start>
4620 -- <debug start>
4621 IF ( g_po_wf_debug = 'Y' ) THEN
4622 po_wf_debug_pkg.Insert_debug (itemtype => p_itemtype, itemkey => p_itemkey
4623 ,
4624 x_progress => 'POCLSOUT_SELECTOR called with mode: '
4625 ||p_funcmode
4626 ||' itemtype: '
4627 ||p_itemtype
4628 ||' itemkey: '
4629 ||p_itemkey);
4630 END IF;
4631
4632 -- <debug end>
4633 l_session_user_id := fnd_global.user_id;
4634
4635 l_session_resp_id := fnd_global.resp_id;
4636
4637 IF ( l_session_user_id = -1 ) THEN
4638 l_session_user_id := NULL;
4639 END IF;
4640
4641 IF ( l_session_resp_id = -1 ) THEN
4642 l_session_resp_id := NULL;
4643 END IF;
4644
4645 --<debug start>
4646 l_progress := '010 selector fn - sess_user_id:'
4647 ||l_session_user_id
4648 ||' ses_resp_id '
4649 ||l_session_resp_id;
4650
4651 IF ( g_po_wf_debug = 'Y' ) THEN
4652 po_wf_debug_pkg.Insert_debug(p_itemtype, p_itemkey, l_progress);
4653 END IF;
4654
4655 --<debug end>
4656 IF ( p_funcmode = 'TEST_CTX' ) THEN
4657
4658 IF ( g_po_wf_debug = 'Y' ) THEN
4659 po_wf_debug_pkg.Insert_debug(itemtype => p_itemtype,
4660 itemkey => p_itemkey,
4661 x_progress => 'POCLSOUT_SELECTOR: inside Test Ctx ');
4662 END IF;
4663 PO_CLOSEOUT_PVT.Log( d_module, d_position, ' inside TEST_CTX');
4664
4665 --<Bug 6144768 End>
4666 -- we cannot afford to run the wf without the session user, hence
4667 -- always set the ctx if session user id is null.
4668 IF ( l_session_user_id IS NULL OR l_session_resp_id IS NULL ) THEN
4669 p_x_result := 'NOTSET';
4670 RETURN;
4671
4672 ELSE
4673 p_x_result := 'TRUE';
4674 RETURN;
4675
4676 END IF; -- l_session_user_id is null
4677 ELSIF ( p_funcmode = 'SET_CTX' ) THEN
4678
4679 PO_CLOSEOUT_PVT.Log( d_module, d_position, ' inside SET_CTX');
4680
4681 l_document_id := wf_engine.GetItemAttrNumber (itemtype => p_itemtype, itemkey => p_itemkey, aname => 'DOCUMENT_ID');
4682
4683 SELECT ATTRIBUTE15
4684 INTO l_context_ids
4685 FROM po_closeout_details
4686 WHERE reference_doc_id = l_document_id
4687 AND closeout_doc_type = PO_CLOSEOUT_PVT.g_CLOSEOUT_TYPE_INDIVIDUAL;
4688
4689 l_counter := 0;
4690
4691 FOR cont_cur IN
4692 (
4693 SELECT regexp_substr(l_context_ids,'[^:]+', 1, LEVEL) internal_id FROM dual
4694 CONNECT BY regexp_substr(l_context_ids, '[^:]+', 1, LEVEL) IS NOT NULL
4695
4696 ) LOOP
4697 l_counter := l_counter +1;
4698 IF(l_counter = 1 ) THEN
4699 l_user_id_to_set := To_Number(cont_cur.internal_id);
4700 ELSIF(l_counter = 2 ) THEN
4701 l_resp_id_to_set := To_Number(cont_cur.internal_id);
4702 ELSIF(l_counter = 3 ) THEN
4703 l_appl_id_to_set := To_Number(cont_cur.internal_id);
4704 END IF;
4705
4706 END LOOP;
4707
4708 IF( l_counter > 3) THEN
4709 IF (g_po_wf_debug = 'Y') THEN
4710 PO_WF_DEBUG_PKG.insert_debug(itemtype => p_itemtype,
4711 itemkey => p_itemkey,
4712 x_progress => 'Exception in Selector');
4713 END IF;
4714 END IF;
4715
4716 --<debug start>
4717 l_progress := '050 selector fn : set user '
4718 ||l_user_id_to_set
4719 ||' resp id '
4720 ||l_resp_id_to_set
4721 ||' appl id '
4722 ||l_appl_id_to_set;
4723
4724 IF ( g_po_wf_debug = 'Y' ) THEN
4725 /* DEBUG */
4726 po_wf_debug_pkg.Insert_debug(p_itemtype, p_itemkey, l_progress);
4727 END IF;
4728
4729 PO_CLOSEOUT_PVT.Log( d_module, d_position, ' l_user_id_to_set'||l_user_id_to_set);
4730 PO_CLOSEOUT_PVT.Log( d_module, d_position, ' l_resp_id_to_set'||l_resp_id_to_set);
4731 PO_CLOSEOUT_PVT.Log( d_module, d_position, ' l_appl_id_to_set'||l_appl_id_to_set);
4732
4733 fnd_global.Apps_initialize
4734 (
4735 l_user_id_to_set,
4736 l_resp_id_to_set,
4737 l_appl_id_to_set
4738 );
4739
4740 END IF;
4741
4742 IF (PO_LOG.d_proc) THEN PO_LOG.proc_end(d_module); END IF;
4743
4744 -- Context setting revamp <end>
4745 EXCEPTION
4746 WHEN OTHERS THEN
4747 IF ( g_po_wf_debug = 'Y' ) THEN
4748 po_wf_debug_pkg.Insert_debug(itemtype => p_itemtype,
4749 itemkey => p_itemkey,
4750 x_progress => 'Exception in Selector');
4751 END IF;
4752
4753 wf_core.Context('PO_CLOSEOUT_PVT', 'POCLSOUT_SELECTOR',
4754 p_itemtype,
4755 p_itemkey, p_actid, p_funcmode);
4756
4757 RAISE;
4758 END poclsout_selector;
4759
4760
4761 --------------------------------------------------------------------------------
4762 --Start of Comments
4763
4764 --Name: set_checklist_signed_date
4765
4766 --Function:
4767 -- Set the checklist signed date
4768
4769 --Parameters:
4770
4771 --IN:
4772 -- itemtype NUMBER
4773 -- itemkey VARCHAR2
4774 -- actid NUMBER
4775 -- funcmode VARCHAR2
4776
4777 --OUT:
4778 --resultout VARCHAR2
4779
4780 --End of Comments
4781 --------------------------------------------------------------------------------
4782 PROCEDURE set_checklist_signed_date (
4783 itemtype IN VARCHAR2
4784 , itemkey IN VARCHAR2
4785 , actid IN NUMBER
4786 , funcmode IN VARCHAR2
4787 , resultout OUT NOCOPY VARCHAR2
4788 )
4789 IS
4790 l_document_id NUMBER;
4791
4792 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || '.' || 'set_checklist_signed_date';
4793 d_position NUMBER;
4794
4795 BEGIN
4796 d_position := 0;
4797 IF (PO_LOG.d_proc) THEN
4798 PO_LOG.proc_begin(d_module);
4799 END IF;
4800
4801 IF (funcmode = 'RUN') THEN
4802
4803 l_document_id := wf_engine.GetItemAttrNumber (itemtype => itemtype, itemkey => itemkey, aname => 'DOCUMENT_ID');
4804
4805 PO_CLOSEOUT_PVT.Log( d_module, d_position, 'l_document_id ' || l_document_id);
4806
4807 UPDATE po_closeout_details
4808 SET checklist_signed_date = SYSDATE
4809 WHERE reference_doc_id = l_document_id
4810 AND closeout_doc_type = PO_CLOSEOUT_PVT.g_CLOSEOUT_TYPE_INDIVIDUAL;
4811
4812 END IF;
4813
4814 IF (PO_LOG.d_proc) THEN PO_LOG.proc_end(d_module); END IF;
4815
4816 EXCEPTION
4817 WHEN OTHERS THEN
4818 wf_core.context('PO_CLOSEOUT_PVT', 'get_closeout_pdf', d_position);
4819 RAISE;
4820
4821 END set_checklist_signed_date;
4822
4823 END PO_CLOSEOUT_PVT;