[Home] [Help]
PACKAGE BODY: APPS.PO_DRAFTS_GRP
Source
1 PACKAGE BODY PO_DRAFTS_GRP AS
2 /* $Header: PO_DRAFTS_GRP.plb 120.12.12020000.2 2013/02/10 16:35:48 vegajula ship $ */
3
4 d_pkg_name CONSTANT varchar2(50) :=
5 PO_LOG.get_package_base('PO_DRAFTS_GRP');
6
7 -----------------------------------------------------------------------
8 --Start of Comments
9 --Name: get_online_auth_status_code
10 --Function:
11 -- Gets the online authoring status. It states who is making draft changes
12 -- and at what stage the draft chages are at.
13 --Parameters:
14 --IN:
15 --p_api_version
16 -- API Version
17 --p_po_header_id
18 -- document header id
19 --IN OUT:
20 --OUT:
21 --x_return_status
22 -- Return Status
23 --x_online_auth_status_code
24 -- Online Authoring status of the document. Possible values:
25 -- All of the following contants are defined in this package:
26 -- g_NO_DRAFT if no draft changes exist
27 -- g_SUPPLIER_SUBMISSION_PENDING if supplier is making draft changes
28 -- g_SUPPLIER_CHANGES_SUBMITTED if supplier's changes are pending acceptance
29 -- g_CAT_ADMIN_SUBMISSION_PENDING if cat admin is making draft changes
30 -- g_CAT_ADMIN_CHANGES_SUBMITTED if cat admin's changes are pending acceptance
31 --End of Comments
32 ------------------------------------------------------------------------
33 PROCEDURE get_online_auth_status_code
34 ( p_api_version IN NUMBER,
35 x_return_status OUT NOCOPY VARCHAR2,
36 p_po_header_id IN NUMBER,
37 x_online_auth_status_code OUT NOCOPY VARCHAR2
38 ) IS
39
40 d_api_name CONSTANT VARCHAR2(30) := 'get_online_auth_status_code';
41 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || '.' || d_api_name || '.';
42 d_position NUMBER;
43
44 l_api_version NUMBER := 1.0;
45
46 l_draft_id PO_DRAFTS.draft_id%TYPE;
47 l_draft_status PO_DRAFTS.status%TYPE;
48 l_draft_owner_role PO_DRAFTS.owner_role%TYPE;
49
50 BEGIN
51
52 d_position := 0;
53 IF (PO_LOG.d_proc) THEN
54 PO_LOG.proc_begin(d_module);
55 END IF;
56
57 x_return_status := FND_API.G_RET_STS_SUCCESS;
58
59 IF (NOT FND_API.compatible_api_call
60 ( p_current_version_number => l_api_version,
61 p_caller_version_number => p_api_version,
62 p_api_name => d_api_name,
63 p_pkg_name => d_pkg_name
64 )
65 ) THEN
66
67 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
68 END IF;
69
70 x_online_auth_status_code := g_NO_DRAFT;
71
72 PO_DRAFTS_PVT.find_draft
73 ( p_po_header_id => p_po_header_id,
74 x_draft_id => l_draft_id,
75 x_draft_status => l_draft_status,
76 x_draft_owner_role => l_draft_owner_role
77 );
78
79 d_position := 10;
80
81 IF (l_draft_id IS NOT NULL) THEN
82 IF (l_draft_owner_role = PO_GLOBAL.g_ROLE_SUPPLIER) THEN
83 IF (l_draft_status IN (PO_DRAFTS_PVT.g_status_DRAFT,
84 PO_DRAFTS_PVT.g_status_PDOI_PROCESSING)) THEN
85
86 d_position := 20;
87 x_online_auth_status_code := g_SUPPLIER_SUBMISSION_PENDING;
88
89 ELSIF (l_draft_status = PO_DRAFTS_PVT.g_status_IN_PROCESS) THEN
90
91 d_position := 30;
92 x_online_auth_status_code := g_SUPPLIER_CHANGES_SUBMITTED;
93 END IF;
94
95 ELSIF (l_draft_owner_role = PO_GLOBAL.g_ROLE_CAT_ADMIN) THEN
96 IF (l_draft_status IN (PO_DRAFTS_PVT.g_status_DRAFT,
97 PO_DRAFTS_PVT.g_status_PDOI_PROCESSING)) THEN
98
99 d_position := 40;
100 x_online_auth_status_code := g_CAT_ADMIN_SUBMISSION_PENDING;
101
102 ELSIF (l_draft_status = PO_DRAFTS_PVT.g_status_IN_PROCESS) THEN
103 d_position := 50;
104 x_online_auth_status_code := g_CAT_ADMIN_CHANGES_SUBMITTED;
105 END IF;
106
107 END IF;
108 END IF;
109
110 IF (PO_LOG.d_proc) THEN
111 PO_LOG.proc_end(d_module, 'x_online_auth_status_code', x_online_auth_status_code);
112 END IF;
113
114 EXCEPTION
115 WHEN OTHERS THEN
116 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
117 PO_MESSAGE_S.add_exc_msg
118 ( p_pkg_name => d_pkg_name,
119 p_procedure_name => d_api_name || '.' || d_position
120 );
121 END get_online_auth_status_code;
122
123
124 -----------------------------------------------------------------------
125 --Start of Comments
126 --Name: supplier_auth_allowed
127 --Function:
128 -- Checks whether supplier authoring is allowed. To satisfy, document has to
129 -- meet the following:
130 -- 1) Has to be a global agreement
131 -- 2) Functional Lock can be obtained
132 -- 3) Document is in updatable status (Not Cancelled, etc.)
133 --Parameters:
134 --IN:
135 --p_api_version
136 -- API Version
137 --p_po_header_id
138 -- document header id
139 --IN OUT:
140 --OUT:
141 --x_return_status
142 -- Return Status
143 --x_authoring_allowed
144 -- returns whether supplier authoring is allowed
145 -- FND_API.G_TRUE if authoring is allowed
146 -- FND_API.G_FALSE if authoring is not allowed
147 --x_message
148 -- reason for the failure, if authoring is not allowed
149 --End of Comments
150 ------------------------------------------------------------------------
151 PROCEDURE supplier_auth_allowed
152 ( p_api_version IN NUMBER,
153 x_return_status OUT NOCOPY VARCHAR2,
154 p_po_header_id IN NUMBER,
155 x_authoring_allowed OUT NOCOPY VARCHAR2,
156 x_message OUT NOCOPY VARCHAR2
157 ) IS
158
159 d_api_name CONSTANT VARCHAR2(30) := 'supplier_auth_allowed';
160 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || '.' || d_api_name || '.';
161 d_position NUMBER;
162
163 l_api_version NUMBER := 1.0;
164
165 l_locking_applicable VARCHAR2(1);
166 l_unlock_required VARCHAR2(1);
167
168 l_po_status_rec PO_STATUS_REC_TYPE;
169 l_update_allowed VARCHAR2(1);
170 l_return_status VARCHAR2(1);
171 BEGIN
172 d_position := 0;
173 IF (PO_LOG.d_proc) THEN
174 PO_LOG.proc_begin(d_module);
175 END IF;
176
177 x_return_status := FND_API.G_RET_STS_SUCCESS;
178
179 IF (NOT FND_API.compatible_api_call
180 ( p_current_version_number => l_api_version,
181 p_caller_version_number => p_api_version,
182 p_api_name => d_api_name,
183 p_pkg_name => d_pkg_name
184 )
185 ) THEN
186
187 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
188 END IF;
189
190 x_authoring_allowed := FND_API.G_TRUE;
191 x_message := NULL;
192
193 d_position := 10;
194
195 IF (NOT PO_GA_PVT.is_global_agreement(p_po_header_id => p_po_header_id)) THEN
196 x_authoring_allowed := FND_API.G_FALSE;
197 x_message := 'PO_ONLINE_AUTH_NA';
198 RETURN;
199 END IF;
200
201 d_position := 20;
202
203 -- check whether supplier can take the functional lock of the document
204 PO_DRAFTS_PVT.update_permission_check
205 ( p_calling_module => PO_DRAFTS_PVT.g_call_mod_HTML_UI,
206 p_po_header_id => p_po_header_id,
207 p_role => PO_GLOBAL.g_role_SUPPLIER,
208 x_update_allowed => x_authoring_allowed,
209 x_locking_applicable => l_locking_applicable,
210 x_unlock_required => l_unlock_required,
211 x_message => x_message
212 );
213
214 IF (x_authoring_allowed = FND_API.G_FALSE) THEN
215 IF (PO_LOG.d_stmt) THEN
216 PO_LOG.stmt(d_module, d_position, 'update permission check failed');
217 END IF;
218 END IF;
219
220 d_position := 30;
221
222 -- bug4862194
223 -- Removed the call to po_status_check because the checks for
224 -- po status is already done when the agreement edit page is accessed.
225
226
227 IF (PO_LOG.d_proc) THEN
228 PO_LOG.proc_end(d_module);
229 END IF;
230
231 EXCEPTION
232 WHEN OTHERS THEN
233 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
234 PO_MESSAGE_S.add_exc_msg
235 ( p_pkg_name => d_pkg_name,
236 p_procedure_name => d_api_name || '.' || d_position
237 );
238 END supplier_auth_allowed;
239
240 -----------------------------------------------------------------------
241 --Start of Comments
242 --Name: get_upload_status_info
243 --Pre-reqs: None
244 --Modifies:
245 --Locks:
246 -- None
247 --Parameters:
248 --IN:
249 --p_po_header_id
250 -- header_id of the document
251 --p_role
252 -- Role of the user
253 --IN OUT:
254 --OUT:
255 -- upload_status_code
256 -- upload_requestor_role_id id of the role
257 -- upload_job_id latest upload job id
258 -- upload_status_display
259 -- upload_is_error
260 --Returns:
261 --Notes:
262 --Testing:
263 --End of Comments
264 ------------------------------------------------------------------------
265 PROCEDURE get_upload_status_info
266 ( p_api_version IN NUMBER,
267 x_return_status OUT NOCOPY VARCHAR2,
268 p_po_header_id IN NUMBER,
269 p_role IN VARCHAR2,
270 x_upload_status_code OUT NOCOPY VARCHAR2,
271 x_upload_requestor_role_id OUT NOCOPY NUMBER,
272 x_upload_job_number OUT NOCOPY NUMBER,
273 x_upload_status_display OUT NOCOPY VARCHAR2,
274 x_upload_is_error OUT NOCOPY NUMBER
275 ) IS
276
277 d_api_name CONSTANT VARCHAR2(30) := 'get_upload_status_info';
278 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || '.' || d_api_name || '.';
279 d_position NUMBER;
280
281 l_api_version NUMBER := 1.0;
282
283 BEGIN
284 d_position := 0;
285
286 IF (PO_LOG.d_proc) THEN
287 PO_LOG.proc_begin(d_module);
288 END IF;
289
290 x_return_status := FND_API.G_RET_STS_SUCCESS;
291
292 IF (NOT FND_API.compatible_api_call
293 ( p_current_version_number => l_api_version,
294 p_caller_version_number => p_api_version,
295 p_api_name => d_api_name,
296 p_pkg_name => d_pkg_name
297 )
298 ) THEN
299
300 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
301 END IF;
302
303 d_position := 10;
304
305 PO_DRAFTS_PVT.get_upload_status_info
306 ( p_po_header_id => p_po_header_id,
307 p_role => p_role,
308 x_upload_status_code => x_upload_status_code,
309 x_upload_requestor_role_id => x_upload_requestor_role_id,
310 x_upload_job_number => x_upload_job_number,
311 x_upload_status_display => x_upload_status_display,
312 x_upload_is_error => x_upload_is_error --Bug#5518826
313 );
314
315
316 IF (PO_LOG.d_proc) THEN
317 PO_LOG.proc_end(d_module);
318 END IF;
319
320 EXCEPTION
321 WHEN OTHERS THEN
322 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
323 PO_MESSAGE_S.add_exc_msg
324 ( p_pkg_name => d_pkg_name,
325 p_procedure_name => d_api_name || '.' || d_position
326 );
327 END get_upload_status_info;
328
329 -----------------------------------------------------------------------
330 --Start of Comments
331 --Name: get_in_process_upload_info
332 --Function:
333 -- Return the information for the upload that in progress regardless
334 -- of the role
335 --Parameters:
336 --IN:
337 --p_po_header_id
338 -- header_id of the document
339 --p_role
340 -- role of the user
341 --IN OUT:
342 --OUT:
343 -- upload_in_progress: FND_API.G_TRUE if there's one upload that's not yet
344 -- complete
345 -- upload_status_code
346 -- upload_requestor_role
347 -- upload_requestor_role_id id of the role
348 -- upload_job_id latest upload job id
349 -- upload_status_display
350 --Returns:
351 --Notes:
352 --Testing:
353 --End of Comments
354 ------------------------------------------------------------------------
355 PROCEDURE get_in_process_upload_info
356 ( p_api_version IN NUMBER,
357 x_return_status OUT NOCOPY VARCHAR2,
358 p_po_header_id IN NUMBER,
359 x_upload_in_progress OUT NOCOPY VARCHAR2,
360 x_upload_status_code OUT NOCOPY VARCHAR2,
361 x_upload_requestor_role OUT NOCOPY VARCHAR2,
362 x_upload_requestor_role_id OUT NOCOPY NUMBER,
363 x_upload_job_number OUT NOCOPY NUMBER,
364 x_upload_status_display OUT NOCOPY VARCHAR2
365 ) IS
366
367 d_api_name CONSTANT VARCHAR2(30) := 'get_in_process_upload_info';
368 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || '.' || d_api_name || '.';
369 d_position NUMBER;
370
371 l_api_version NUMBER := 1.0;
372
373 BEGIN
374 d_position := 0;
375
376 IF (PO_LOG.d_proc) THEN
377 PO_LOG.proc_begin(d_module);
378 END IF;
379
380 x_return_status := FND_API.G_RET_STS_SUCCESS;
381
382 IF (NOT FND_API.compatible_api_call
383 ( p_current_version_number => l_api_version,
384 p_caller_version_number => p_api_version,
385 p_api_name => d_api_name,
386 p_pkg_name => d_pkg_name
387 )
388 ) THEN
389
390 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
391 END IF;
392
393 d_position := 10;
394
395 PO_DRAFTS_PVT.get_in_process_upload_info
396 ( p_po_header_id => p_po_header_id,
397 x_upload_in_progress => x_upload_in_progress,
398 x_upload_status_code => x_upload_status_code,
399 x_upload_requestor_role => x_upload_requestor_role,
400 x_upload_requestor_role_id => x_upload_requestor_role_id,
401 x_upload_job_number => x_upload_job_number,
402 x_upload_status_display => x_upload_status_display
403 );
404
405
406 IF (PO_LOG.d_proc) THEN
407 PO_LOG.proc_end(d_module);
408 END IF;
409
410 EXCEPTION
411 WHEN OTHERS THEN
412 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
413 PO_MESSAGE_S.add_exc_msg
414 ( p_pkg_name => d_pkg_name,
415 p_procedure_name => d_api_name || '.' || d_position
416 );
417 END get_in_process_upload_info;
418
419 -----------------------------------------------------------------------
420 --Start of Comments
421 --Name: discard_upload_error
422 --Pre-reqs: None
423 --Modifies:
424 --Locks:
425 -- None
426 --Function:
427 -- This procedure removes draft control record and unlock the document when
428 -- there is no draft changes for the doucment. This is called when
429 -- supplier/cat admin discards error.
430 --Parameters:
431 --IN:
432 --p_api_version
433 -- API Version
434 --p_po_header_id
435 -- document header id
436 --IN OUT:
437 --OUT:
438 --x_return_status
439 -- Return Status
440 --Returns:
441 --Notes:
442 --Testing:
443 --End of Comments
444 ------------------------------------------------------------------------
445 PROCEDURE discard_upload_error
446 ( p_api_version IN NUMBER,
447 x_return_status OUT NOCOPY VARCHAR2,
448 p_po_header_id IN NUMBER
449 ) IS
450
451 d_api_name CONSTANT VARCHAR2(30) := 'discard_upload_error';
452 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || '.' || d_api_name || '.';
453 d_position NUMBER;
454
455 l_api_version NUMBER := 1.0;
456 l_return_status VARCHAR2(1);
457
458 l_draft_id NUMBER;
459 -- Bug 12700941
460 l_draft_status PO_DRAFTS.status%TYPE;
461 l_draft_owner_role PO_DRAFTS.owner_role%TYPE;
462
463 l_changes_exist_tbl PO_TBL_VARCHAR1;
464 l_unlock VARCHAR2(1) := FND_API.G_FALSE;
465
466 BEGIN
467 d_position := 0;
468
469 IF (PO_LOG.d_proc) THEN
470 PO_LOG.proc_begin(d_module);
471 END IF;
472
473 x_return_status := FND_API.G_RET_STS_SUCCESS;
474
475 IF (NOT FND_API.compatible_api_call
476 ( p_current_version_number => l_api_version,
477 p_caller_version_number => p_api_version,
478 p_api_name => d_api_name,
479 p_pkg_name => d_pkg_name
480 )
481 ) THEN
482
483 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
484 END IF;
485
486 -- Bug 12700941 starts
487 -- Getting the lock owner role as well
488 PO_DRAFTS_PVT.find_draft
489 ( p_po_header_id => p_po_header_id,
490 x_draft_id => l_draft_id,
491 x_draft_status =>l_draft_status,
492 x_draft_owner_role =>l_draft_owner_role
493 );
494
495 d_position := 10;
496 IF (PO_LOG.d_proc) THEN
497 PO_LOG.stmt(d_module, d_position, 'draft id for headerid ' ||
498 p_po_header_id || ' : ' || l_draft_id);
499 END IF;
500
501 -- document should be unlocked when:
502 -- draft does not exist
503 -- draft exists, but no changes have been inserted into draft tables.
504 IF (l_draft_id IS NOT NULL) THEN
505 d_position := 20;
506
507 l_changes_exist_tbl :=
508 PO_DRAFTS_PVT.changes_exist_for_draft
509 ( p_draft_id_tbl => PO_TBL_NUMBER(l_draft_id)
510 );
511
512 -- Bug 12700941 starts
513 -- changes_exist_for_draft returns true even when the current requset for uplaod fails
514 -- if the any of teh previous upload requests was successful but
515 -- In such case, the draft (po_drafts) status need to be set back to 'DRAFT'
516 -- for cat.admin/supplier
517 -- and draft record (po_drafts) should be deleted for role= Buyer as For Buyer the draft record is not retained
518
519 IF (l_changes_exist_tbl(1) = FND_API.G_FALSE OR l_draft_owner_role =PO_GLOBAL.g_role_BUYER) THEN
520 d_position := 30;
521
522 l_unlock := FND_API.G_TRUE;
523
524 PO_DRAFTS_PVT.remove_draft_changes
525 ( p_draft_id => l_draft_id,
526 p_exclude_ctrl_tbl => FND_API.G_FALSE,
527 x_return_status => l_return_status
528 );
529
530 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
531 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
532 END IF;
533
534 ELSE
535
536 PO_DRAFTS_PVT.update_draft_status( p_draft_id => l_draft_id,
537 p_new_status =>PO_DRAFTS_PVT.g_status_DRAFT);
538
539 END IF;
540 -- Bug 12700941 ends
541 ELSE
542 d_position := 40;
543
544 IF (PO_LOG.d_proc) THEN
545 PO_LOG.stmt(d_module, d_position, 'draft changes do not exist. Simply unlock doc.');
546 END IF;
547
548 l_unlock := FND_API.G_TRUE;
549 END IF;
550
551 d_position := 50;
552
553 IF ( l_unlock = FND_API.G_TRUE ) THEN
554 PO_DRAFTS_PVT.unlock_document
555 ( p_po_header_id => p_po_header_id
556 );
557 END IF;
558
559 IF (PO_LOG.d_proc) THEN
560 PO_LOG.proc_end(d_module);
561 END IF;
562
563 EXCEPTION
564 WHEN OTHERS THEN
565 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
566 PO_MESSAGE_S.add_exc_msg
567 ( p_pkg_name => d_pkg_name,
568 p_procedure_name => d_api_name || '.' || d_position
569 );
570 END discard_upload_error;
571
572 -----------------------------------------------------------------------
573 --Start of Comments
574 --Name: lock_document_with_validate
575 --Function:
576 -- Same as lock_document, except that it performs update_permission_check
577 -- procedure before going to lock_document procedure.
578 --Parameters:
579 --IN:
580 --p_api_version
581 -- API Version
582 --p_calling_module
583 -- indicates where the procedure is called from. Possible Values
584 --p_po_header_id
585 -- document header id
586 --p_role
587 -- role of the user
588 --p_role_user_id
589 -- role id of the user
590 --OUT:
591 --x_locking_allowed
592 -- indicate whether locking was permitted
593 --x_message
594 -- error message when locking has not been permitted
595 --x_message_text
596 -- Display message when locking has not been permitted
597 --End of Comments
598 ------------------------------------------------------------------------
599 PROCEDURE lock_document_with_validate
600 ( p_api_version IN NUMBER,
601 x_return_status OUT NOCOPY VARCHAR2,
602 p_calling_module IN VARCHAR2,
603 p_po_header_id IN NUMBER,
604 p_role IN VARCHAR2,
605 p_role_user_id IN NUMBER,
606 x_locking_allowed OUT NOCOPY VARCHAR2,
607 x_message OUT NOCOPY VARCHAR2,
608 x_message_text OUT NOCOPY VARCHAR2
609 ) IS
610 d_api_name CONSTANT VARCHAR2(30) := 'lock_document_with_validate';
611 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
612 d_position NUMBER;
613 l_api_version NUMBER := 1.0;
614
615 l_message VARCHAR2(2000);
616 l_locking_applicable VARCHAR2(1);
617 l_unlock_required VARCHAR2(1);
618
619 BEGIN
620
621 d_position := 0;
622 IF (PO_LOG.d_proc) THEN
623 PO_LOG.proc_begin(d_module);
624 END IF;
625
626 x_return_status := FND_API.G_RET_STS_SUCCESS;
627
628 IF (NOT FND_API.compatible_api_call
629 ( p_current_version_number => l_api_version,
630 p_caller_version_number => p_api_version,
631 p_api_name => d_api_name,
632 p_pkg_name => d_pkg_name
633 )
634 ) THEN
635
636 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
637 END IF;
638
639 d_position := 10;
640
641 PO_DRAFTS_PVT.lock_document_with_validate
642 ( p_calling_module => p_calling_module,
643 p_po_header_id => p_po_header_id,
644 p_role => p_role,
645 p_role_user_id => p_role_user_id,
646 x_locking_allowed => x_locking_allowed,
647 x_message => x_message,
648 x_message_text => x_message_text
649 );
650
651 IF (PO_LOG.d_proc) THEN
652 PO_LOG.proc_end(d_module);
653 END IF;
654
655 EXCEPTION
656 WHEN OTHERS THEN
657 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
658 PO_MESSAGE_S.add_exc_msg
659 ( p_pkg_name => d_pkg_name,
660 p_procedure_name => d_api_name || '.' || d_position
661 );
662 END lock_document_with_validate;
663
664 END PO_DRAFTS_GRP;