[Home] [Help]
PACKAGE BODY: APPS.PO_DOCUMENT_ACTION_PVT
Source
1 PACKAGE BODY PO_DOCUMENT_ACTION_PVT AS
2 -- $Header: POXVDACB.pls 120.21.12020000.3 2013/02/11 13:21:54 vegajula ship $
3
4 -- Private package constants
5
6 g_pkg_name CONSTANT varchar2(30) := 'PO_DOCUMENT_ACTION_PVT';
7 g_log_head CONSTANT VARCHAR2(50) := 'po.plsql.'|| g_pkg_name || '.';
8
9 -- Private package variables
10
11 -- variable that stores the value that will be put into
12 -- error_msg variable of the record upon action completion
13 -- re-initialized at beginning of do_action
14 g_err_message VARCHAR2(2000);
15
16 ------------------------------------------------------------------------------
17 --Start of Comments
18 --Name: do_action
19 --Pre-reqs:
20 -- None
21 --Modifies:
22 -- None, directly.
23 --Locks:
24 -- None, directly.Calls PO_DOCUMENT_LOCK_GRP to lock document
25 -- if action ctl record's lock_document = TRUE
26 --Function:
27 -- This procedure is the switchboard for all document actions in
28 -- package PO_DOCUMENT_ACTION_PVT. Performs all the common logic
29 -- for these actions.
30 -- This includes:
31 -- setting the org context to that of the document
32 -- initializing g_err_message, the shared error string
33 -- locking the document, if necessary
34 -- calling the appropriate action handler
35 -- inbound logistics, if necessary (PO_DELREC_PVT call)
36 -- rolling back when action returns 'U'
37 -- resetting the org context back to the original org context
38 --Replaces:
39 -- This method covers some of the logic in poxdmaction in poxdm.lpc.
40 --Parameters:
41 --IN:
42 -- p_action_ctl_rec
43 -- Record containing all necessary parameters for action.
44 -- Should be populated by the individual do_XXXX methods.
45 --OUT:
46 -- p_action_ctl_rec
47 -- Record contains variables that record output values depending
48 -- on the action. All actions will populate at least a return_status.
49 -- See individual actions and package spec for more info on outputs.
50 --End of Comments
51 -------------------------------------------------------------------------------
52 PROCEDURE do_action(
53 p_action_ctl_rec IN OUT NOCOPY PO_DOCUMENT_ACTION_PVT.doc_action_call_rec_type
54 )
55 IS
56
57 l_doc_org_id PO_HEADERS_ALL.org_id%TYPE;
58 l_old_org_id PO_HEADERS_ALL.org_id%TYPE;
59 l_lock_status VARCHAR2(1);
60
61 l_ret_sts VARCHAR2(1);
62 l_msg_count NUMBER;
63 l_msg_data VARCHAR2(2000) := NULL;
64
65 d_progress NUMBER;
66 d_module VARCHAR2(70) := 'po.plsql.PO_DOCUMENT_ACTION_PVT.do_action';
67 d_log_msg VARCHAR2(200);
68
69 -- variables required for locking
70 -- resource_busy_exc definition copied from PO_DOCUMENT_LOCK_GRP
71 resource_busy_exc EXCEPTION;
72 PRAGMA EXCEPTION_INIT(resource_busy_exc, -00054);
73 l_locked_doc BOOLEAN := FALSE;
74 l_doc_id_tbl po_tbl_number;
75
76 -- <HTML Agreement Release 12>
77 l_update_allowed VARCHAR2(1);
78 l_locking_applicable VARCHAR2(1);
79 l_unlock_required VARCHAR2(1);
80 l_error_message VARCHAR2(30);
81 l_error_message_text FND_NEW_MESSAGES.message_text%type;
82
83 BEGIN
84
85 d_progress := 0;
86 IF (PO_LOG.d_proc) THEN
87 PO_LOG.proc_begin(d_module);
88 PO_LOG.proc_begin(d_module, 'p_action_ctl_rec.action', p_action_ctl_rec.action);
89 PO_LOG.proc_begin(d_module, 'p_action_ctl_rec.document_type', p_action_ctl_rec.document_type); --Bug#4962625
90 END IF;
91
92 SAVEPOINT DA_DO_ACTION_SP;
93
94 -- initialize shared concatenated string to be used as error stack
95 g_err_message := NULL;
96
97 BEGIN
98
99 d_progress := 10;
100
101 -- Set the org context to that of the document
102 -- Keep track of old org context so that we can reset it.
103
104 l_old_org_id := NULL;
105 l_doc_org_id := NULL;
106
107 IF (p_action_ctl_rec.document_type in ('PO', 'PA'))
108 THEN
109
110 d_progress := 11.1;
111
112 SELECT org_id
113 INTO l_doc_org_id
114 FROM po_headers_all poh
115 WHERE poh.po_header_id = p_action_ctl_rec.document_id;
116
117 ELSIF (p_action_ctl_rec.document_type = 'RELEASE')
118 THEN
119
120 d_progress := 11.2;
121
122 SELECT org_id
123 INTO l_doc_org_id
124 FROM po_releases_all por
125 WHERE por.po_release_id = p_action_ctl_rec.document_id;
126
127 ELSIF (p_action_ctl_rec.document_type = 'REQUISITION')
128 THEN
129
130 d_progress := 11.3;
131
132 SELECT org_id
133 INTO l_doc_org_id
134 FROM po_requisition_headers_all porh
135 WHERE porh.requisition_header_id = p_action_ctl_rec.document_id;
136
137 ELSE
138
139 d_progress := 11.4;
140 d_log_msg := 'invalid document type';
141 l_ret_sts := 'U';
142 RAISE PO_CORE_S.g_early_return_exc;
143
144 END IF; -- p_aciton_ctl_rec.document_type = ...
145
146 d_progress := 12;
147
148 --the current org id is now derived using the get_current_org_id
149 --function because org context is not set in java
150 l_old_org_id := PO_MOAC_UTILS_PVT.get_current_org_id;
151
152 d_progress := 13;
153 IF (PO_LOG.d_stmt) THEN
154 PO_LOG.stmt(d_module, d_progress, 'l_old_org_id', l_old_org_id);
155 PO_LOG.stmt(d_module, d_progress, 'l_doc_org_id', l_doc_org_id);
156 PO_LOG.stmt(d_module, d_progress, 'Setting org context.');
157 END IF;
158
159 po_moac_utils_pvt.set_org_context(l_doc_org_id); --<R12 MOAC>
160
161
162 -- if necessary, lock the document
163
164 IF (p_action_ctl_rec.lock_document)
165 THEN
166
167 d_progress := 15;
168
169 IF (PO_LOG.d_stmt) THEN
170 PO_LOG.stmt(d_module, d_progress, 'Locking the document.');
171 END IF;
172
173 l_doc_id_tbl := po_tbl_number(p_action_ctl_rec.document_id);
174
175 -- Ported over functionality from document manager
176 -- It would try to lock the document 1000 times.
177 FOR i IN 1..1000
178 LOOP
179
180 BEGIN
181
182 d_progress := 16;
183
184 /*Bug8512125 We pass the calling mode from here which will execute a different set of cursors if the mode is RCV*/
185
186 PO_LOCKS.lock_headers(
187 p_doc_type => p_action_ctl_rec.document_type
188 , p_doc_level => PO_CORE_S.g_doc_level_HEADER
189 , p_doc_level_id_tbl => l_doc_id_tbl
190 , p_calling_mode => p_action_ctl_rec.calling_mode
191 );
192
193 l_locked_doc := TRUE;
194
195 EXIT;
196
197 EXCEPTION
198 WHEN resource_busy_exc THEN
199 NULL;
200 END;
201
202 END LOOP; -- for i in 1..1000
203
204 IF (NOT l_locked_doc)
205 THEN
206
207 d_log_msg := 'failed to lock document after 1000 tries';
208 l_ret_sts := 'U';
209 RAISE PO_CORE_S.g_early_return_exc;
210
211 END IF;
212
213
214 -- <HTML Agreement R12 START>
215 -- Obtain functional lock of the document
216 -- <Bug#4651122>
217 -- Added l_error_message_text as an argument to match the singature
218 -- <Conc Mods Project> Bypass permission check for Mod enabled styles
219 IF (p_action_ctl_rec.document_type = 'PA'
220 AND PO_DRAFTS_PVT.is_mod_enabled(p_action_ctl_rec.document_id) = FND_API.G_FALSE) THEN
221
222 PO_DRAFTS_PVT.update_permission_check
223 ( p_calling_module => PO_DRAFTS_PVT.g_call_mod_API,
224 p_po_header_id => p_action_ctl_rec.document_id,
225 p_role => PO_GLOBAL.g_role_BUYER,
226 p_skip_cat_upload_chk => FND_API.G_TRUE,
227 x_update_allowed => l_update_allowed,
228 x_locking_applicable => l_locking_applicable,
229 x_unlock_required => l_unlock_required,
230 x_message => l_error_message,
231 x_message_text => l_error_message_text --Bug#4651122
232 );
233
234 IF (l_update_allowed = FND_API.G_FALSE) THEN
235 d_log_msg := 'unable to perform control action to doc: ' ||
236 l_error_message_text;
237 l_ret_sts := 'E';
238 RAISE PO_CORE_S.g_early_return_exc;
239 END IF;
240
241 END IF;
242
243 -- <HTML Agreement R12 END>
244
245 ELSE
246
247 d_progress := 20;
248 IF (PO_LOG.d_stmt) THEN
249 PO_LOG.stmt(d_module, d_progress, 'Not locking the document.');
250 END IF;
251
252 END IF; -- IF p_action_ctl_rec.lock_document
253
254
255 -- Switchboard: run appropriate handler routine based on action
256
257 IF (p_action_ctl_rec.action = PO_DOCUMENT_ACTION_PVT.g_doc_action_APPROVE)
258 THEN
259
260 d_progress := 30.1;
261 PO_DOCUMENT_ACTION_AUTH.approve(p_action_ctl_rec => p_action_ctl_rec);
262
263 ELSIF (p_action_ctl_rec.action = PO_DOCUMENT_ACTION_PVT.g_doc_action_REJECT)
264 THEN
265
266 d_progress := 30.2;
267 PO_DOCUMENT_ACTION_AUTH.reject(p_action_ctl_rec => p_action_ctl_rec);
268
269 ELSIF (p_action_ctl_rec.action = PO_DOCUMENT_ACTION_PVT.g_doc_action_FORWARD)
270 THEN
271
272 d_progress := 30.3;
273 PO_DOCUMENT_ACTION_AUTH.forward(p_action_ctl_rec => p_action_ctl_rec);
274
275 ELSIF (p_action_ctl_rec.action = PO_DOCUMENT_ACTION_PVT.g_doc_action_RETURN)
276 THEN
277
278 d_progress := 30.4;
279 PO_DOCUMENT_ACTION_AUTH.return_action(p_action_ctl_rec => p_action_ctl_rec);
280
281 ELSIF (p_action_ctl_rec.action = PO_DOCUMENT_ACTION_PVT.g_doc_action_CHECK_APPROVE)
282 THEN
283
284 d_progress := 40.1;
285 PO_DOCUMENT_ACTION_CHECK.approve_status_check(p_action_ctl_rec => p_action_ctl_rec);
286
287 ELSIF (p_action_ctl_rec.action = PO_DOCUMENT_ACTION_PVT.g_doc_action_CHECK_REJECT)
288 THEN
289
290 d_progress := 40.2;
291 PO_DOCUMENT_ACTION_CHECK.reject_status_check(p_action_ctl_rec => p_action_ctl_rec);
292
293 ELSIF (p_action_ctl_rec.action = PO_DOCUMENT_ACTION_PVT.g_doc_action_CHECK_AUTHORITY)
294 THEN
295
296 d_progress := 40.3;
297 PO_DOCUMENT_ACTION_CHECK.authority_check(p_action_ctl_rec => p_action_ctl_rec);
298
299 ELSIF (p_action_ctl_rec.action IN (PO_DOCUMENT_ACTION_PVT.g_doc_action_FREEZE,
300 PO_DOCUMENT_ACTION_PVT.g_doc_action_UNFREEZE))
301 THEN
302
303 d_progress := 50.1;
304 PO_DOCUMENT_ACTION_HOLD.freeze_unfreeze(p_action_ctl_rec => p_action_ctl_rec);
305
306 ELSIF (p_action_ctl_rec.action IN (PO_DOCUMENT_ACTION_PVT.g_doc_action_HOLD,
307 PO_DOCUMENT_ACTION_PVT.g_doc_action_RELEASE_HOLD))
308 THEN
309
310 d_progress := 50.2;
311 PO_DOCUMENT_ACTION_HOLD.hold_unhold(p_action_ctl_rec => p_action_ctl_rec);
312
313 ELSIF (p_action_ctl_rec.action IN (PO_DOCUMENT_ACTION_PVT.g_doc_action_CLOSE,
314 PO_DOCUMENT_ACTION_PVT.g_doc_action_CLOSE_RCV,
315 PO_DOCUMENT_ACTION_PVT.g_doc_action_CLOSE_INV,
316 PO_DOCUMENT_ACTION_PVT.g_doc_action_FINALLY_CLOSE,
317 PO_DOCUMENT_ACTION_PVT.g_doc_action_OPEN,
318 PO_DOCUMENT_ACTION_PVT.g_doc_action_OPEN_RCV,
319 PO_DOCUMENT_ACTION_PVT.g_doc_action_OPEN_INV))
320 THEN
321
322 d_progress := 60.1;
323 PO_DOCUMENT_ACTION_CLOSE.manual_close_po(p_action_ctl_rec => p_action_ctl_rec);
324
325 ELSIF (p_action_ctl_rec.action = PO_DOCUMENT_ACTION_PVT.g_doc_action_UPDATE_CLOSE_AUTO)
326 THEN
327
328 d_progress := 60.2;
329 PO_DOCUMENT_ACTION_CLOSE.auto_close_po(p_action_ctl_rec => p_action_ctl_rec);
330
331 ELSE
332
333 d_progress := 100;
334 d_log_msg := 'unsupported action type';
335 l_ret_sts := 'U';
336 RAISE PO_CORE_S.g_early_return_exc;
337
338 END IF; -- IF (p_action_ctl_rec.action = ...)
339
340 IF (p_action_ctl_rec.return_status = 'U')
341 THEN
342
343 d_progress := 110;
344 d_log_msg := 'unexpected error in action call';
345 l_ret_sts := 'U';
346 RAISE PO_CORE_S.g_early_return_exc;
347
348 END IF;
349
350 IF (p_action_ctl_rec.return_status = 'E')
351 THEN
352
353 d_progress := 115;
354 d_log_msg := 'functional error in action call';
355 l_ret_sts := 'E';
356 RAISE PO_CORE_S.g_early_return_exc;
357
358 END IF;
359
360 d_progress := 120;
361
362 IF (PO_LOG.d_stmt) THEN
363 PO_LOG.stmt(d_module, d_progress, 'action call complete');
364 END IF;
365
366
367 -- Handle inbound logistics for SPOs and Blanket Releases
368
369 IF (((p_action_ctl_rec.document_type = 'PO') AND (p_action_ctl_rec.document_subtype = 'STANDARD'))
370 OR ((p_action_ctl_rec.document_type = 'RELEASE') AND (p_action_ctl_rec.document_subtype = 'BLANKET')))
371 THEN
372
373 --<Bug# 5766607> PO-OTM: HOLD/UNHOLD ACTIONS RAISED FROM HTML DO NOT COMMUNICATED TO OTM.
374 --Remove the filter on the action types. All actions will be handled properly
375 --in the create_update_delrec procedure.
376 d_progress := 130;
377
378 PO_DELREC_PVT.create_update_delrec(
379 p_api_version => 1.0
380 , x_return_status => l_ret_sts
381 , x_msg_count => l_msg_count
382 , x_msg_data => l_msg_data
383 , p_action => p_action_ctl_rec.action
384 , p_doc_type => p_action_ctl_rec.document_type
385 , p_doc_subtype => p_action_ctl_rec.document_subtype
386 , p_doc_id => p_action_ctl_rec.document_id
387 , p_line_id => p_action_ctl_rec.line_id
388 , p_line_location_id => p_action_ctl_rec.shipment_id
389 );
390
391 IF (l_ret_sts <> 'S')
392 THEN
393
394 d_progress := 140;
395 d_log_msg := 'create_update_delrec not successful';
396 FND_MSG_PUB.Count_And_Get(p_count => l_msg_count, p_data => l_msg_data);
397 error_msg_append(d_module, d_progress, l_msg_data);
398 l_ret_sts := 'U';
399 RAISE PO_CORE_S.g_early_return_exc;
400
401 END IF;
402
403 END IF; -- p_action_ctl_rec.document_type = 'PO' AND ...
404
405 d_progress := 150;
406 p_action_ctl_rec.error_msg := NULL;
407 l_ret_sts := 'S';
408
409 EXCEPTION
410 WHEN PO_CORE_S.g_early_return_exc THEN
411 IF (l_ret_sts = 'U') THEN
412 IF (l_msg_data IS NOT NULL) THEN
413 error_msg_append(d_module, d_progress, l_msg_data);
414 END IF;
415 error_msg_append(d_module, d_progress, d_log_msg);
416 get_error_message(p_action_ctl_rec.error_msg);
417 IF (PO_LOG.d_exc) THEN
418 PO_LOG.exc(d_module, d_progress, d_log_msg);
419 END IF;
420 ROLLBACK TO DA_DO_ACTION_SP;
421 ELSIF (l_ret_sts = 'E') THEN
422 IF (PO_LOG.d_stmt) THEN
423 PO_LOG.stmt(d_module, d_progress, d_log_msg);
424 END IF;
425 END IF;
426 END;
427
428 d_progress := 160;
429 --<R12 MOAC IMPACT>
430 -- Reset org context to what it was before we set
431 -- the org context to document's org context
432
433 -- We do not need to check for org context being
434 -- set to null as this is a valid scenario from HTML
435 -- A null org id implies multiple org context
436
437 po_moac_utils_pvt.set_org_context(l_old_org_id); --<R12 MOAC>
438
439 --<R12 MOAC IMPACT>
440
441 p_action_ctl_rec.return_status := l_ret_sts;
442
443 IF (PO_LOG.d_proc) THEN
444 PO_LOG.proc_end(d_module, 'p_action_ctl_rec.return_status', p_action_ctl_rec.return_status);
445 PO_LOG.proc_end(d_module, 'p_action_ctl_rec.return_code', p_action_ctl_rec.return_code);
446 PO_LOG.proc_end(d_module, 'p_action_ctl_rec.functional_error', p_action_ctl_rec.functional_error);
447 PO_LOG.proc_end(d_module, 'p_action_ctl_rec.error_msg', p_action_ctl_rec.error_msg);
448 PO_LOG.proc_end(d_module);
449 END IF;
450
451
452 RETURN;
453
454 EXCEPTION
455 WHEN OTHERS THEN
456 p_action_ctl_rec.return_status := 'U';
457
458 error_msg_append(d_module, d_progress, SQLCODE, SQLERRM);
459 get_error_message(p_action_ctl_rec.error_msg);
460 IF (PO_LOG.d_exc) THEN
461 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
462 PO_LOG.proc_end(d_module, 'p_action_ctl_rec.return_status', p_action_ctl_rec.return_status);
463 PO_LOG.proc_end(d_module, 'p_action_ctl_rec.functional_error', p_action_ctl_rec.functional_error);
464 PO_LOG.proc_end(d_module, 'p_action_ctl_rec.error_msg', p_action_ctl_rec.error_msg);
465 PO_LOG.proc_end(d_module);
466 END IF;
467
468 ROLLBACK TO DA_DO_ACTION_SP;
469
470 -- Reset org context to what it was before we set
471 -- the org context to document's org context
472 --<R12 MOAC IMPACT>
473 --IF (l_old_org_id IS NOT NULL)
474 --THEN
475 po_moac_utils_pvt.set_org_context(l_old_org_id); --<R12 MOAC>
476 -- END IF;
477 --<R12 MOAC IMPACT>
478 RETURN;
479
480 END do_action;
481
482
483 ------------------------------------------------------------------------------
484 --Start of Comments
485 --Name: do_approve
486 --Pre-reqs:
487 -- None
488 --Modifies:
489 -- None, directly.
490 --Locks:
491 -- None, directly. Through do_action, locks the document header.
492 --Function:
493 -- Approves a document as current user.
494 -- Does not do any kind of status or state checking.
495 -- Uses do_action switchboard
496 --Parameters:
497 --IN:
498 -- p_document_id
499 -- ID of the document's header (e.g. po_release_id, po_header_id, ...)
500 -- p_document_type
501 -- 'RELEASE', 'PO', 'PA', or 'REQUISITION'
502 -- p_document_subtype
503 -- REQUISITION: 'INTERNAL', 'PURCHASE'
504 -- PO: 'STANDARD', 'PLANNED'
505 -- PA: 'CONTRACT', 'BLANKET'
506 -- RELEASE: 'SCHEDULED', 'BLANKET'
507 -- p_note
508 -- To be stored in action history table.
509 -- p_approval_path_id
510 -- To be stored in action history table.
511 --OUT:
512 -- x_return_status
513 -- 'S': Approve action was successful
514 -- 'U': Approve action failed
515 -- x_exception_message
516 -- If x_return_status = 'U', this parameter will
517 -- contain an error stack in concatenated string form.
518 --End of Comments
519 -------------------------------------------------------------------------------
520 PROCEDURE do_approve(
521 p_document_id IN VARCHAR2
522 , p_document_type IN VARCHAR2
523 , p_document_subtype IN VARCHAR2
524 , p_draft_id IN NUMBER := -1 --Bug 13444730
525 , p_note IN VARCHAR2
526 , p_approval_path_id IN NUMBER
527 , x_return_status OUT NOCOPY VARCHAR2
528 , x_exception_msg OUT NOCOPY VARCHAR2
529 )
530 IS
531
532 l_da_call_rec DOC_ACTION_CALL_REC_TYPE;
533
534 BEGIN
535
536 l_da_call_rec.action := g_doc_action_APPROVE;
537 l_da_call_rec.lock_document := TRUE;
538 l_da_call_rec.document_id := p_document_id;
539 l_da_call_rec.document_type := p_document_type;
540 l_da_call_rec.document_subtype := p_document_subtype;
541 l_da_call_rec.new_document_status := g_doc_status_APPROVED;
542 l_da_call_rec.forward_to_id := NULL;
543 l_da_call_rec.note := p_note;
544 l_da_call_rec.approval_path_id := p_approval_path_id;
545 l_da_call_rec.line_id := NULL;
546 l_da_call_rec.shipment_id := NULL;
547 l_da_call_rec.offline_code := NULL;
548 l_da_call_rec.draft_id := p_draft_id; --Bug 13444730
549
550 do_action(p_action_ctl_rec => l_da_call_rec);
551
552 x_exception_msg := l_da_call_rec.error_msg;
553 x_return_status := l_da_call_rec.return_status;
554
555 END do_approve;
556
557
558 ------------------------------------------------------------------------------
559 --Start of Comments
560 --Name: do_reject
561 --Pre-reqs:
562 -- None
563 --Modifies:
564 -- None, directly.
565 --Locks:
566 -- None, directly. Through do_action, locks the document header.
567 --Function:
568 -- Rejects a document as current user.
569 -- Does a document state check before attempting to reject.
570 -- Uses do_action switchboard
571 --Parameters:
572 --IN:
573 -- p_document_id
574 -- ID of the document's header (e.g. po_release_id, po_header_id, ...)
575 -- p_document_type
576 -- 'RELEASE', 'PO', 'PA'
577 -- p_document_subtype
578 -- PO: 'STANDARD', 'PLANNED'
579 -- PA: 'CONTRACT', 'BLANKET'
580 -- RELEASE: 'SCHEDULED', 'BLANKET'
581 -- p_note
582 -- To be stored in action history table.
583 -- p_approval_path_id
584 -- To be stored in action history table.
585 --OUT:
586 -- x_return_status
587 -- 'S': Reject action had no unexpected errors
588 -- In this case, check return_code for success/failure.
589 -- 'U': Reject action failed with unexpected errors
590 -- x_return_code
591 -- 'STATE_FAILED': Document state check failed
592 -- 'P', 'F', 'T': Encumbrance call not fully successful.
593 -- NULL: reject action was successful
594 -- x_exception_message
595 -- If x_return_status = 'U', this parameter will
596 -- contain an error stack in concatenated string form.
597 -- x_online_report_id
598 -- ID to online report containing more detailed encumbrance results
599 --End of Comments
600 -------------------------------------------------------------------------------
601 PROCEDURE do_reject(
602 p_document_id IN VARCHAR2
603 , p_document_type IN VARCHAR2
604 , p_document_subtype IN VARCHAR2
605 , p_draft_id IN NUMBER :=-1 -- CLM Apprvl
606 , p_note IN VARCHAR2
607 , p_approval_path_id IN NUMBER
608 , x_return_status OUT NOCOPY VARCHAR2
609 , x_return_code OUT NOCOPY VARCHAR2
610 , x_exception_msg OUT NOCOPY VARCHAR2
611 , x_online_report_id OUT NOCOPY NUMBER
612 )
613 IS
614
615 l_da_call_rec DOC_ACTION_CALL_REC_TYPE;
616
617 BEGIN
618
619 l_da_call_rec.action := g_doc_action_REJECT;
620 l_da_call_rec.lock_document := TRUE;
621 l_da_call_rec.document_id := p_document_id;
622 l_da_call_rec.document_type := p_document_type;
623 l_da_call_rec.document_subtype := p_document_subtype;
624 l_da_call_rec.draft_id := p_draft_id; --CLM Apprvl
625 l_da_call_rec.forward_to_id := NULL;
626 l_da_call_rec.new_document_status := g_doc_status_REJECTED;
627 l_da_call_rec.note := p_note;
628 l_da_call_rec.approval_path_id := p_approval_path_id;
629 l_da_call_rec.offline_code := NULL;
630 l_da_call_rec.online_report_id := NULL;
631
632 do_action(p_action_ctl_rec => l_da_call_rec);
633
634 x_exception_msg := l_da_call_rec.error_msg;
635 x_return_code := l_da_call_rec.return_code;
636 x_online_report_id := l_da_call_rec.online_report_id;
637 x_return_status := l_da_call_rec.return_status;
638
639
640 END do_reject;
641
642 ------------------------------------------------------------------------------
643 --Start of Comments
644 --Name: do_return
645 --Pre-reqs:
646 -- None
647 --Modifies:
648 -- None, directly.
649 --Locks:
650 -- None, directly. Through do_action, locks the document header.
651 --Function:
652 -- Returns a requisition as current user, removing it from
653 -- the requisition pool.
654 -- Does a document state check before attempting to return.
655 -- Uses do_action switchboard
656 --Parameters:
657 --IN:
658 -- p_document_id
659 -- ID of the document's header (e.g. po_release_id, po_header_id, ...)
660 -- p_document_type
661 -- 'REQUISITION'
662 -- p_document_subtype
663 -- REQUISITION: 'INTERNAL', 'PURCHASE'
664 -- p_note
665 -- To be stored in action history table.
666 -- p_approval_path_id
667 -- To be stored in action history table.
668 --OUT:
669 -- x_return_status
670 -- 'S': Return action had no unexpected errors
671 -- In this case, check return_code for success/failure.
672 -- 'U': Return action failed with unexpected errors
673 -- x_return_code
674 -- 'STATE_FAILED': Document state check failed
675 -- 'P', 'F', 'T': Encumbrance call not fully successful.
676 -- NULL: return action was successful
677 -- x_exception_message
678 -- If x_return_status = 'U', this parameter will
679 -- contain an error stack in concatenated string form.
680 -- x_online_report_id
681 -- ID to online report containing more detailed encumbrance results
682 --End of Comments
683 -------------------------------------------------------------------------------
684 PROCEDURE do_return(
685 p_document_id IN VARCHAR2
686 , p_document_type IN VARCHAR2
687 , p_document_subtype IN VARCHAR2
688 , p_note IN VARCHAR2
689 , p_approval_path_id IN NUMBER
690 , x_return_status OUT NOCOPY VARCHAR2
691 , x_return_code OUT NOCOPY VARCHAR2
692 , x_exception_msg OUT NOCOPY VARCHAR2
693 , x_online_report_id OUT NOCOPY NUMBER
694 )
695 IS
696
697 l_da_call_rec DOC_ACTION_CALL_REC_TYPE;
698
699 BEGIN
700
701 l_da_call_rec.action := g_doc_action_RETURN;
702 l_da_call_rec.lock_document := TRUE;
703 l_da_call_rec.document_id := p_document_id;
704 l_da_call_rec.document_type := p_document_type;
705 l_da_call_rec.document_subtype := p_document_subtype;
706 l_da_call_rec.forward_to_id := NULL;
707 l_da_call_rec.new_document_status := NULL;
708 l_da_call_rec.note := p_note;
709 l_da_call_rec.approval_path_id := p_approval_path_id;
710 l_da_call_rec.offline_code := NULL;
711 l_da_call_rec.online_report_id := NULL;
712
713 do_action(p_action_ctl_rec => l_da_call_rec);
714
715 x_exception_msg := l_da_call_rec.error_msg;
716 x_return_code := l_da_call_rec.return_code;
717 x_online_report_id := l_da_call_rec.online_report_id;
718 x_return_status := l_da_call_rec.return_status;
719
720
721 END do_return;
722
723 ------------------------------------------------------------------------------
724 --Start of Comments
725 --Name: do_forward
726 --Pre-reqs:
727 -- None
728 --Modifies:
729 -- None, directly.
730 --Locks:
731 -- None, directly. Through do_action, locks the document header.
732 --Function:
733 -- Forwards a document from the current user.
734 -- Does not do any kind of status or state checking.
735 -- Uses do_action switchboard
736 --Parameters:
737 --IN:
738 -- p_document_id
739 -- ID of the document's header (e.g. po_release_id, po_header_id, ...)
740 -- p_document_type
741 -- 'RELEASE', 'PO', 'PA', or 'REQUISITION'
742 -- p_document_subtype
743 -- REQUISITION: 'INTERNAL', 'PURCHASE'
744 -- PO: 'STANDARD', 'PLANNED'
745 -- PA: 'CONTRACT', 'BLANKET'
746 -- RELEASE: 'SCHEDULED', 'BLANKET'
747 -- p_new_doc_status
748 -- status the document should be in after forward action completes.
749 -- Should be g_doc_action_PREAPPROVED or g_doc_action_INPROCESS
750 -- p_note
751 -- To be stored in action history table.
752 -- p_approval_path_id
753 -- To be stored in action history table.
754 -- p_forward_to_id
755 -- ID of employee to forward document to
756 --OUT:
757 -- x_return_status
758 -- 'S': Forward action was successful
759 -- 'U': Forward action failed
760 -- x_exception_message
761 -- If x_return_status = 'U', this parameter will
762 -- contain an error stack in concatenated string form.
763 --End of Comments
764 ------------------------------------------------------------------------------
765 PROCEDURE do_forward(
766 p_document_id IN VARCHAR2
767 , p_document_type IN VARCHAR2
768 , p_document_subtype IN VARCHAR2
769 , p_new_doc_status IN VARCHAR2
770 , p_note IN VARCHAR2
771 , p_approval_path_id IN NUMBER
772 , p_forward_to_id IN NUMBER
773 , x_return_status OUT NOCOPY VARCHAR2
774 , x_exception_msg OUT NOCOPY VARCHAR2
775 )
776 IS
777
778 l_da_call_rec DOC_ACTION_CALL_REC_TYPE;
779
780 BEGIN
781
782 l_da_call_rec.action := g_doc_action_FORWARD;
783 l_da_call_rec.lock_document := TRUE;
784 l_da_call_rec.document_id := p_document_id;
785 l_da_call_rec.document_type := p_document_type;
786 l_da_call_rec.document_subtype := p_document_subtype;
787 l_da_call_rec.new_document_status := p_new_doc_status;
788 l_da_call_rec.note := p_note;
789 l_da_call_rec.approval_path_id := p_approval_path_id;
790 l_da_call_rec.forward_to_id := p_forward_to_id;
791 l_da_call_rec.offline_code := NULL;
792
793 do_action(p_action_ctl_rec => l_da_call_rec);
794
795 x_exception_msg := l_da_call_rec.error_msg;
796 x_return_status := l_da_call_rec.return_status;
797
798 END do_forward;
799
800
801
802 ------------------------------------------------------------------------------
803 --Start of Comments
804 --Name: verify_authority.
805 --Pre-reqs:
806 -- None
807 --Modifies:
808 -- None.
809 --Locks:
810 -- None.
811 --Function:
812 -- Verify the authority of an employee to approve a document.
813 -- Verifies against the various po control rules.
814 -- Uses do_action switchboard
815 --Parameters:
816 --IN:
817 -- p_document_id
818 -- ID of the document's header (e.g. po_release_id, po_header_id, ...)
819 -- p_document_type
820 -- 'RELEASE', 'PO', 'PA', or 'REQUISITION'
821 -- p_document_subtype
822 -- REQUISITION: 'INTERNAL', 'PURCHASE'
823 -- PO: 'STANDARD', 'PLANNED'
824 -- PA: 'CONTRACT', 'BLANKET'
825 -- RELEASE: 'SCHEDULED', 'BLANKET'
826 -- p_employee_id
827 -- The id of the employee to verify approval authority for.
828 --OUT:
829 -- x_return_status
830 -- 'S': Verification encountered no unexpected errors.
831 -- In this case, check return_code for success/failure.
832 -- 'U': Verification encountered unexpected errors.
833 -- x_return_code
834 -- 'AUTHORIZATION_FAILED': user does not have sufficient authority
835 -- NULL: user has sufficient authority to approve the document
836 -- x_exception_message
837 -- If x_return_status = 'U', this parameter will
838 -- contain an error stack in concatenated string form.
839 -- x_auth_failed_msg
840 -- If return_code is AUTHORIZATION_FAILED, then this will contain
841 -- a user friendly message indicating the check that failed.
842 -- e.g.: the value of FND_MESSAGE.get_string(PO, PO_AUT_DOC_TOTAL_FAIL);
843 --End of Comments
844 ------------------------------------------------------------------------------
845 PROCEDURE verify_authority(
846 p_document_id IN VARCHAR2
847 , p_document_type IN VARCHAR2
848 , p_document_subtype IN VARCHAR2
849 , p_employee_id IN VARCHAR2
850 , x_return_status OUT NOCOPY VARCHAR2
851 , x_return_code OUT NOCOPY VARCHAR2
852 , x_exception_msg OUT NOCOPY VARCHAR2
853 , x_auth_failed_msg OUT NOCOPY VARCHAR2
854 )
855 IS
856
857 l_da_call_rec DOC_ACTION_CALL_REC_TYPE;
858
859 BEGIN
860
861 l_da_call_rec.action := g_doc_action_CHECK_AUTHORITY;
862 l_da_call_rec.lock_document := FALSE;
863 l_da_call_rec.document_id := p_document_id;
864 l_da_call_rec.document_type := p_document_type;
865 l_da_call_rec.document_subtype := p_document_subtype;
866 l_da_call_rec.employee_id := p_employee_id;
867
868 do_action(p_action_ctl_rec => l_da_call_rec);
869
870 x_exception_msg := l_da_call_rec.error_msg;
871 x_return_code := l_da_call_rec.return_code;
872 x_auth_failed_msg := l_da_call_rec.functional_error;
873 x_return_status := l_da_call_rec.return_status;
874
875 END verify_authority;
876
877
878 ------------------------------------------------------------------------------
879 --Start of Comments
880 --Name: check_doc_status_approve
881 --Pre-reqs:
882 -- None
883 --Modifies:
884 -- None.
885 --Locks:
886 -- None.
887 --Function:
888 -- Verify that a document is in appropriate state for the approve action.
889 -- Verifies authorization status, closed status, frozen flag, etc.
890 -- Uses do_action switchboard
891 --Parameters:
892 --IN:
893 -- p_document_id
894 -- ID of the document's header (e.g. po_release_id, po_header_id, ...)
895 -- p_document_type
896 -- 'RELEASE', 'PO', 'PA', or 'REQUISITION'
897 -- p_document_subtype
898 -- REQUISITION: 'INTERNAL', 'PURCHASE'
899 -- PO: 'STANDARD', 'PLANNED'
900 -- PA: 'CONTRACT', 'BLANKET'
901 -- RELEASE: 'SCHEDULED', 'BLANKET'
902 --OUT:
903 -- x_return_status
904 -- 'S': Verification encountered no unexpected errors.
905 -- In this case, check return_code for success/failure.
906 -- 'U': State verification encountered unexpected errors.
907 -- x_return_code
908 -- 'STATE_FAILED': document is not in valid state for approve action
909 -- NULL: document is in valid state for approve action
910 -- x_exception_message
911 -- If x_return_status = 'U', this parameter will
912 -- contain an error stack in concatenated string form.
913 --End of Comments
914 ------------------------------------------------------------------------------
915 PROCEDURE check_doc_status_approve(
916 p_document_id IN VARCHAR2
917 , p_document_type IN VARCHAR2
918 , p_document_subtype IN VARCHAR2
919 , x_return_status OUT NOCOPY VARCHAR2
920 , x_return_code OUT NOCOPY VARCHAR2
921 , x_exception_msg OUT NOCOPY VARCHAR2
922 )
923 IS
924
925 l_da_call_rec DOC_ACTION_CALL_REC_TYPE;
926
927 BEGIN
928
929 l_da_call_rec.action := g_doc_action_CHECK_APPROVE;
930 l_da_call_rec.lock_document := FALSE;
931 l_da_call_rec.document_id := p_document_id;
932 l_da_call_rec.document_type := p_document_type;
933 l_da_call_rec.document_subtype := p_document_subtype;
934
935 do_action(p_action_ctl_rec => l_da_call_rec);
936
937 x_exception_msg := l_da_call_rec.error_msg;
938 x_return_code := l_da_call_rec.return_code;
939 x_return_status := l_da_call_rec.return_status;
940
941 END check_doc_status_approve;
942
943 ------------------------------------------------------------------------------
944 --Start of Comments
945 --Name: check_doc_status_reject
946 --Pre-reqs:
947 -- None
948 --Modifies:
949 -- None.
950 --Locks:
951 -- None.
952 --Function:
953 -- Verify that a document is in appropriate state for the reject action.
954 -- Verifies authorization status, closed status, frozen flag, etc.
955 -- Uses do_action switchboard
956 --Parameters:
957 --IN:
958 -- p_document_id
959 -- ID of the document's header (e.g. po_release_id, po_header_id, ...)
960 -- p_document_type
961 -- 'RELEASE', 'PO', 'PA', or 'REQUISITION'
962 -- p_document_subtype
963 -- REQUISITION: 'INTERNAL', 'PURCHASE'
964 -- PO: 'STANDARD', 'PLANNED'
965 -- PA: 'CONTRACT', 'BLANKET'
966 -- RELEASE: 'SCHEDULED', 'BLANKET'
967 --OUT:
968 -- x_return_status
969 -- 'S': Verification encountered no unexpected errors.
970 -- In this case, check return_code for success/failure.
971 -- 'U': State verification encountered unexpected errors.
972 -- x_return_code
973 -- 'STATE_FAILED': document is not in valid state for reject action
974 -- NULL: document is in valid state for reject action
975 -- x_exception_message
976 -- If x_return_status = 'U', this parameter will
977 -- contain an error stack in concatenated string form.
978 --End of Comments
979 ------------------------------------------------------------------------------
980 PROCEDURE check_doc_status_reject(
981 p_document_id IN VARCHAR2
982 , p_document_type IN VARCHAR2
983 , p_document_subtype IN VARCHAR2
984 , x_return_status OUT NOCOPY VARCHAR2
985 , x_return_code OUT NOCOPY VARCHAR2
986 , x_exception_msg OUT NOCOPY VARCHAR2
987 )
988 IS
989
990 l_da_call_rec DOC_ACTION_CALL_REC_TYPE;
991
992 BEGIN
993
994 l_da_call_rec.action := g_doc_action_CHECK_REJECT;
995 l_da_call_rec.lock_document := FALSE;
996 l_da_call_rec.document_id := p_document_id;
997 l_da_call_rec.document_type := p_document_type;
998 l_da_call_rec.document_subtype := p_document_subtype;
999
1000 do_action(p_action_ctl_rec => l_da_call_rec);
1001
1002 x_exception_msg := l_da_call_rec.error_msg;
1003 x_return_code := l_da_call_rec.return_code;
1004 x_return_status := l_da_call_rec.return_status;
1005
1006 END check_doc_status_reject;
1007
1008
1009 ------------------------------------------------------------------------------
1010 --Start of Comments
1011 --Name: do_freeze
1012 --Pre-reqs:
1013 -- None
1014 --Modifies:
1015 -- None, directly.
1016 --Locks:
1017 -- None, directly. Through do_action, locks the document header.
1018 --Function:
1019 -- Freezes a document as current user.
1020 -- Does a document state check before attempting to freeze.
1021 -- Uses do_action switchboard
1022 --Parameters:
1023 --IN:
1024 -- p_document_id
1025 -- ID of the document's header (e.g. po_release_id, po_header_id, ...)
1026 -- p_document_type
1027 -- 'RELEASE', 'PO', 'PA'
1028 -- p_document_subtype
1029 -- PO: 'STANDARD', 'PLANNED'
1030 -- PA: 'CONTRACT', 'BLANKET'
1031 -- RELEASE: 'SCHEDULED', 'BLANKET'
1032 -- p_reason
1033 -- To be stored in action history table.
1034 --OUT:
1035 -- x_return_status
1036 -- 'S': Freeze action had no unexpected errors
1037 -- In this case, check return_code for success/failure.
1038 -- 'U': Freeze action failed with unexpected errors
1039 -- x_return_code
1040 -- 'STATE_FAILED': Document state check failed
1041 -- NULL: freeze action was successful
1042 -- x_exception_message
1043 -- If x_return_status = 'U', this parameter will
1044 -- contain an error stack in concatenated string form.
1045 --End of Comments
1046 -------------------------------------------------------------------------------
1047 PROCEDURE do_freeze(
1048 p_document_id IN VARCHAR2
1049 , p_document_type IN VARCHAR2
1050 , p_document_subtype IN VARCHAR2
1051 , p_reason IN VARCHAR2
1052 , x_return_status OUT NOCOPY VARCHAR2
1053 , x_return_code OUT NOCOPY VARCHAR2
1054 , x_exception_msg OUT NOCOPY VARCHAR2
1055 )
1056 IS
1057
1058 l_da_call_rec DOC_ACTION_CALL_REC_TYPE;
1059
1060 BEGIN
1061
1062 l_da_call_rec.action := g_doc_action_FREEZE;
1063 l_da_call_rec.lock_document := TRUE;
1064 l_da_call_rec.document_id := p_document_id;
1065 l_da_call_rec.document_type := p_document_type;
1066 l_da_call_rec.document_subtype := p_document_subtype;
1067 l_da_call_rec.note := p_reason;
1068
1069 do_action(p_action_ctl_rec => l_da_call_rec);
1070
1071 x_exception_msg := l_da_call_rec.error_msg;
1072 x_return_code := l_da_call_rec.return_code;
1073 x_return_status := l_da_call_rec.return_status;
1074
1075 END do_freeze;
1076
1077
1078 ------------------------------------------------------------------------------
1079 --Start of Comments
1080 --Name: do_unfreeze
1081 --Pre-reqs:
1082 -- None
1083 --Modifies:
1084 -- None, directly.
1085 --Locks:
1086 -- None, directly. Through do_action, locks the document header.
1087 --Function:
1088 -- Unfreezes a document as current user.
1089 -- Does a document state check before attempting to unfreeze.
1090 -- Uses do_action switchboard
1091 --Parameters:
1092 --IN:
1093 -- p_document_id
1094 -- ID of the document's header (e.g. po_release_id, po_header_id, ...)
1095 -- p_document_type
1096 -- 'RELEASE', 'PO', 'PA'
1097 -- p_document_subtype
1098 -- PO: 'STANDARD', 'PLANNED'
1099 -- PA: 'CONTRACT', 'BLANKET'
1100 -- RELEASE: 'SCHEDULED', 'BLANKET'
1101 -- p_reason
1102 -- To be stored in action history table.
1103 --OUT:
1104 -- x_return_status
1105 -- 'S': Unfreeze action had no unexpected errors
1106 -- In this case, check return_code for success/failure.
1107 -- 'U': Unfreeze action failed with unexpected errors
1108 -- x_return_code
1109 -- 'STATE_FAILED': Document state check failed
1110 -- NULL: Unfreeze action was successful
1111 -- x_exception_message
1112 -- If x_return_status = 'U', this parameter will
1113 -- contain an error stack in concatenated string form.
1114 --End of Comments
1115 -------------------------------------------------------------------------------
1116 PROCEDURE do_unfreeze(
1117 p_document_id IN VARCHAR2
1118 , p_document_type IN VARCHAR2
1119 , p_document_subtype IN VARCHAR2
1120 , p_reason IN VARCHAR2
1121 , x_return_status OUT NOCOPY VARCHAR2
1122 , x_return_code OUT NOCOPY VARCHAR2
1123 , x_exception_msg OUT NOCOPY VARCHAR2
1124 )
1125 IS
1126
1127 l_da_call_rec DOC_ACTION_CALL_REC_TYPE;
1128
1129 BEGIN
1130
1131 l_da_call_rec.action := g_doc_action_UNFREEZE;
1132 l_da_call_rec.lock_document := TRUE;
1133 l_da_call_rec.document_id := p_document_id;
1134 l_da_call_rec.document_type := p_document_type;
1135 l_da_call_rec.document_subtype := p_document_subtype;
1136 l_da_call_rec.note := p_reason;
1137
1138 do_action(p_action_ctl_rec => l_da_call_rec);
1139
1140 x_exception_msg := l_da_call_rec.error_msg;
1141 x_return_code := l_da_call_rec.return_code;
1142 x_return_status := l_da_call_rec.return_status;
1143
1144 END do_unfreeze;
1145
1146
1147 ------------------------------------------------------------------------------
1148 --Start of Comments
1149 --Name: do_hold
1150 --Pre-reqs:
1151 -- None
1152 --Modifies:
1153 -- None, directly.
1154 --Locks:
1155 -- None, directly. Through do_action, locks the document header.
1156 --Function:
1157 -- Puts a hold on a document as current user.
1158 -- Does a document state check before attempting to hold.
1159 -- Uses do_action switchboard
1160 --Parameters:
1161 --IN:
1162 -- p_document_id
1163 -- ID of the document's header (e.g. po_release_id, po_header_id, ...)
1164 -- p_document_type
1165 -- 'RELEASE', 'PO', 'PA'
1166 -- p_document_subtype
1167 -- PO: 'STANDARD', 'PLANNED'
1168 -- PA: 'CONTRACT', 'BLANKET'
1169 -- RELEASE: 'SCHEDULED', 'BLANKET'
1170 -- p_reason
1171 -- To be stored in action history table.
1172 --OUT:
1173 -- x_return_status
1174 -- 'S': Hold action had no unexpected errors
1175 -- In this case, check return_code for success/failure.
1176 -- 'U': Hold action failed with unexpected errors
1177 -- x_return_code
1178 -- 'STATE_FAILED': Document state check failed
1179 -- NULL: hold action was successful
1180 -- x_exception_message
1181 -- If x_return_status = 'U', this parameter will
1182 -- contain an error stack in concatenated string form.
1183 --End of Comments
1184 -------------------------------------------------------------------------------
1185 PROCEDURE do_hold(
1186 p_document_id IN VARCHAR2
1187 , p_document_type IN VARCHAR2
1188 , p_document_subtype IN VARCHAR2
1189 , p_reason IN VARCHAR2
1190 , x_return_status OUT NOCOPY VARCHAR2
1191 , x_return_code OUT NOCOPY VARCHAR2
1192 , x_exception_msg OUT NOCOPY VARCHAR2
1193 )
1194 IS
1195
1196 l_da_call_rec DOC_ACTION_CALL_REC_TYPE;
1197
1198 BEGIN
1199
1200 l_da_call_rec.action := g_doc_action_HOLD;
1201 l_da_call_rec.lock_document := TRUE;
1202 l_da_call_rec.document_id := p_document_id;
1203 l_da_call_rec.document_type := p_document_type;
1204 l_da_call_rec.document_subtype := p_document_subtype;
1205 l_da_call_rec.note := p_reason;
1206
1207 do_action(p_action_ctl_rec => l_da_call_rec);
1208
1209 x_exception_msg := l_da_call_rec.error_msg;
1210 x_return_code := l_da_call_rec.return_code;
1211 x_return_status := l_da_call_rec.return_status;
1212
1213 END do_hold;
1214
1215 ------------------------------------------------------------------------------
1216 --Start of Comments
1217 --Name: do_release_hold
1218 --Pre-reqs:
1219 -- None
1220 --Modifies:
1221 -- None, directly.
1222 --Locks:
1223 -- None, directly. Through do_action, locks the document header.
1224 --Function:
1225 -- Releases a hold on a document as current user.
1226 -- Does a document state check before attempting to release hold.
1227 -- Uses do_action switchboard
1228 --Parameters:
1229 --IN:
1230 -- p_document_id
1231 -- ID of the document's header (e.g. po_release_id, po_header_id, ...)
1232 -- p_document_type
1233 -- 'RELEASE', 'PO', 'PA'
1234 -- p_document_subtype
1235 -- PO: 'STANDARD', 'PLANNED'
1236 -- PA: 'CONTRACT', 'BLANKET'
1237 -- RELEASE: 'SCHEDULED', 'BLANKET'
1238 -- p_reason
1239 -- To be stored in action history table.
1240 --OUT:
1241 -- x_return_status
1242 -- 'S': Release hold action had no unexpected errors
1243 -- In this case, check return_code for success/failure.
1244 -- 'U': Release hold action failed with unexpected errors
1245 -- x_return_code
1246 -- 'STATE_FAILED': Document state check failed
1247 -- NULL: release hold action was successful
1248 -- x_exception_message
1249 -- If x_return_status = 'U', this parameter will
1250 -- contain an error stack in concatenated string form.
1251 --End of Comments
1252 -------------------------------------------------------------------------------
1253 PROCEDURE do_release_hold(
1254 p_document_id IN VARCHAR2
1255 , p_document_type IN VARCHAR2
1256 , p_document_subtype IN VARCHAR2
1257 , p_reason IN VARCHAR2
1258 , x_return_status OUT NOCOPY VARCHAR2
1259 , x_return_code OUT NOCOPY VARCHAR2
1260 , x_exception_msg OUT NOCOPY VARCHAR2
1261 )
1262 IS
1263
1264 l_da_call_rec DOC_ACTION_CALL_REC_TYPE;
1265
1266 BEGIN
1267
1268 l_da_call_rec.action := g_doc_action_RELEASE_HOLD;
1269 l_da_call_rec.lock_document := TRUE;
1270 l_da_call_rec.document_id := p_document_id;
1271 l_da_call_rec.document_type := p_document_type;
1272 l_da_call_rec.document_subtype := p_document_subtype;
1273 l_da_call_rec.note := p_reason;
1274
1275 do_action(p_action_ctl_rec => l_da_call_rec);
1276
1277 x_exception_msg := l_da_call_rec.error_msg;
1278 x_return_code := l_da_call_rec.return_code;
1279 x_return_status := l_da_call_rec.return_status;
1280
1281 END do_release_hold;
1282
1283 ------------------------------------------------------------------------------
1284 --Start of Comments
1285 --Name: find_forward_to_id
1286 --Pre-reqs:
1287 -- Org Context must be set.
1288 --Modifies:
1289 -- None.
1290 --Locks:
1291 -- None.
1292 --Function:
1293 -- Find the next employee in the approval chain that
1294 -- has the authority to approve the document.
1295 -- Unlike other actions in this package, find_forward_to_id does
1296 -- not directly call the do_action switchboard;
1297 -- instead its logic calls verify_authority many times.
1298 --Parameters:
1299 --IN:
1300 -- p_document_id
1301 -- ID of the document's header (e.g. po_release_id, po_header_id, ...)
1302 -- p_document_type
1303 -- 'RELEASE', 'PO', 'PA', or 'REQUISITION'
1304 -- p_document_subtype
1305 -- REQUISITION: 'INTERNAL', 'PURCHASE'
1306 -- PO: 'STANDARD', 'PLANNED'
1307 -- PA: 'CONTRACT', 'BLANKET'
1308 -- RELEASE: 'SCHEDULED', 'BLANKET'
1309 -- p_employee_id
1310 -- The id of the employee to forward from.
1311 -- p_approval_path_id
1312 -- The position structure id to use in po_employee_hierarchies
1313 --OUT:
1314 -- x_return_status
1315 -- 'S': Method encountered no unexpected errors.
1316 -- 'U': Method encountered unexpected errors.
1317 -- x_forward_to_od
1318 -- Contains forward_to_id of supervisor that can approve document.
1319 -- Can return null if no one with authority is found.
1320 -- Only valid if x_return_status = 'S'
1321 --End of Comments
1322 ------------------------------------------------------------------------------
1323 PROCEDURE find_forward_to_id(
1324 p_document_id IN NUMBER
1325 , p_document_type IN VARCHAR2
1326 , p_document_subtype IN VARCHAR2
1327 , p_employee_id IN NUMBER
1328 , p_approval_path_id IN NUMBER
1329 , x_return_status OUT NOCOPY VARCHAR2
1330 , x_forward_to_id OUT NOCOPY NUMBER
1331 )
1332 IS
1333
1334 d_progress NUMBER;
1335 d_module VARCHAR2(70) := 'po.plsql.PO_DOCUMENT_ACTION_PVT.find_forward_to_id';
1336 d_msg VARCHAR2(200);
1337
1338 l_ret_sts VARCHAR2(1) := 'S'; -- Bug 4448215
1339 l_ret_code VARCHAR2(25);
1340 l_fwd_to_id NUMBER;
1341 l_exc_msg VARCHAR2(2000);
1342 l_fail_msg VARCHAR2(2000);
1343
1344 l_forwarding_mode PO_DOCUMENT_TYPES.forwarding_mode_code%TYPE;
1345 l_using_positions FINANCIALS_SYSTEM_PARAMETERS.use_positions_flag%TYPE;
1346 l_bus_group_id FINANCIALS_SYSTEM_PARAMETERS.business_group_id%TYPE;
1347 l_hr_xbg_profile VARCHAR2(1);
1348
1349 -- Bug 5386007: Replaced hr_employees_current_v with base tables to
1350 -- improve performance
1351 -- Bug12360617 Contingent worker should be defaulted on the forward_to
1352 -- field.
1353 CURSOR direct_pos(p_emp_id NUMBER, p_path_id NUMBER) IS
1354 SELECT /*+ ordered use_nl (poeh a p past b) */ poeh.superior_id
1355 FROM po_employee_hierarchies_all poeh,
1356 per_all_people_f p,
1357 per_all_assignments_f a,
1358 per_assignment_status_types past
1359 WHERE a.person_id = p.person_id
1360 AND poeh.business_group_id in (select fsp.business_group_id
1361 from financials_system_parameters fsp)
1362 AND a.primary_flag = 'Y'
1363 AND Trunc(SYSDATE) BETWEEN p.effective_start_date AND p.effective_end_date
1364 AND Trunc(SYSDATE) BETWEEN a.effective_start_date AND a.effective_end_date
1365 AND (NVL(CURRENT_EMPLOYEE_FLAG,'N') = 'Y'
1366 OR NVL(CURRENT_NPW_FLAG,'N') = 'Y')
1367 AND a.assignment_type in ('E',decode(
1368 nvl(fnd_profile.value('HR_TREAT_CWK_AS_EMP'),'N'),'Y','C','E'))
1369 AND a.assignment_status_type_id = past.assignment_status_type_id
1370 AND past.per_system_status IN ('ACTIVE_ASSIGN', 'SUSP_ASSIGN','ACTIVE_CWK')
1371 AND poeh.position_structure_id = p_path_id
1372 AND poeh.employee_id = p_emp_id
1373 AND p.person_id = poeh.superior_id
1374 AND poeh.superior_level > 0
1375 AND 'TRUE' = Decode(hr_security.view_all, 'Y', 'TRUE',
1376 hr_security.Show_person(p.person_id,
1377 p.current_applicant_flag,
1378 p.current_employee_flag,
1379 p.current_npw_flag,
1380 p.employee_number,
1381 p.applicant_number,
1382 p.npw_number))
1383 AND 'TRUE' = Decode(hr_security.view_all, 'Y', 'TRUE',
1384 hr_security.Show_record('PER_ALL_ASSIGNMENTS_F',
1385 a.assignment_id,
1386 a.person_id,
1387 a.assignment_type))
1388 ORDER BY poeh.superior_level, p.full_name;
1389
1390 CURSOR direct_assign(p_emp_id NUMBER, p_bus_group_id NUMBER) IS
1391 SELECT pera.supervisor_id
1392 FROM per_assignments_f pera
1393 WHERE pera.business_group_id = p_bus_group_id
1394 AND trunc(SYSDATE) BETWEEN pera.effective_start_date
1395 AND pera.effective_end_date
1396 START WITH pera.person_id = p_emp_id
1397 AND pera.business_group_id = p_bus_group_id
1398 AND trunc(SYSDATE) BETWEEN pera.effective_start_date
1399 AND pera.effective_end_date
1400 CONNECT BY pera.person_id = PRIOR pera.supervisor_id
1401 AND pera.business_group_id = p_bus_group_id
1402 AND trunc(SYSDATE) BETWEEN pera.effective_start_date
1403 AND pera.effective_end_date;
1404
1405 CURSOR direct_assign_xbg(p_emp_id NUMBER) IS
1406 SELECT pera.supervisor_id
1407 FROM per_assignments_f pera
1408 WHERE trunc(SYSDATE) BETWEEN pera.effective_start_date
1409 AND pera.effective_end_date
1410 START WITH pera.person_id = p_emp_id
1411 AND trunc(SYSDATE) BETWEEN pera.effective_start_date
1412 AND pera.effective_end_date
1413 CONNECT BY pera.person_id = PRIOR pera.supervisor_id
1414 AND trunc(SYSDATE) BETWEEN pera.effective_start_date
1415 AND pera.effective_end_date;
1416
1417 -- Bug12360617 Contingent worker should be defaulted on the forward_to
1418 -- field.
1419 CURSOR hier_pos(p_emp_id NUMBER, p_path_id NUMBER) IS
1420 SELECT /*+ ordered use_nl (poeh a p past b) */ poeh.superior_id
1421 FROM po_employee_hierarchies_all poeh,
1422 per_all_people_f p,
1423 per_all_assignments_f a,
1424 per_assignment_status_types past
1425 WHERE a.person_id = p.person_id
1426 AND poeh.business_group_id in (select fsp.business_group_id
1427 from financials_system_parameters fsp)
1428 AND a.primary_flag = 'Y'
1429 AND Trunc(SYSDATE) BETWEEN p.effective_start_date AND p.effective_end_date
1430 AND Trunc(SYSDATE) BETWEEN a.effective_start_date AND a.effective_end_date
1431 AND (NVL(CURRENT_EMPLOYEE_FLAG,'N') = 'Y'
1432 OR NVL(CURRENT_NPW_FLAG,'N') = 'Y')
1433 AND a.assignment_type in ('E',decode(
1434 nvl(fnd_profile.value('HR_TREAT_CWK_AS_EMP'),'N'),'Y','C','E'))
1435 AND a.assignment_status_type_id = past.assignment_status_type_id
1436 AND past.per_system_status IN ('ACTIVE_ASSIGN', 'SUSP_ASSIGN','ACTIVE_CWK')
1437 AND poeh.position_structure_id = p_path_id
1438 AND poeh.employee_id = p_emp_id
1439 AND p.person_id = poeh.superior_id
1440 AND poeh.superior_level = 1
1441 AND 'TRUE' = Decode(hr_security.view_all, 'Y', 'TRUE',
1442 hr_security.Show_person(p.person_id,
1443 p.current_applicant_flag,
1444 p.current_employee_flag,
1445 p.current_npw_flag,
1446 p.employee_number,
1447 p.applicant_number,
1448 p.npw_number))
1449 AND 'TRUE' = Decode(hr_security.view_all, 'Y', 'TRUE',
1450 hr_security.Show_record('PER_ALL_ASSIGNMENTS_F',
1451 a.assignment_id,
1452 a.person_id,
1453 a.assignment_type))
1454 ORDER BY p.full_name;
1455
1456 BEGIN
1457
1458 d_progress := 0;
1459 IF (PO_LOG.d_proc) THEN
1460 PO_LOG.proc_begin(d_module);
1461 PO_LOG.proc_begin(d_module, 'p_document_id', p_document_id);
1462 PO_LOG.proc_begin(d_module, 'p_document_type', p_document_type);
1463 PO_LOG.proc_begin(d_module, 'p_document_subtype', p_document_subtype);
1464 PO_LOG.proc_begin(d_module, 'p_employee_id', p_employee_id);
1465 PO_LOG.proc_begin(d_module, 'p_approval_path_id', p_approval_path_id);
1466 END IF;
1467
1468 d_progress := 10;
1469
1470 l_exc_msg := NULL;
1471 l_fwd_to_id := NULL;
1472
1473 SELECT podt.forwarding_mode_code
1474 INTO l_forwarding_mode
1475 FROM po_document_types podt
1476 WHERE podt.document_type_code = p_document_type
1477 AND podt.document_subtype = p_document_subtype;
1478
1479 d_progress := 15;
1480
1481 SELECT NVL(fsp.use_positions_flag, 'N'), fsp.business_group_id
1482 INTO l_using_positions, l_bus_group_id
1483 FROM financials_system_parameters fsp;
1484
1485 d_progress := 16;
1486 l_hr_xbg_profile := NVL(hr_general.get_xbg_profile, 'N');
1487
1488 d_progress := 20;
1489 IF (PO_LOG.d_stmt) THEN
1490 PO_LOG.stmt(d_module, d_progress, 'l_forwarding_mode', l_forwarding_mode);
1491 PO_LOG.stmt(d_module, d_progress, 'l_using_positions', l_using_positions);
1492 PO_LOG.stmt(d_module, d_progress, 'l_bus_group_id', l_bus_group_id);
1493 PO_LOG.stmt(d_module, d_progress, 'l_hr_xbg_profile', l_hr_xbg_profile);
1494 END IF;
1495
1496 BEGIN
1497
1498 IF (l_forwarding_mode = 'DIRECT')
1499 THEN
1500
1501 d_progress := 30;
1502
1503 IF (l_using_positions = 'Y')
1504 THEN
1505
1506 d_progress := 40;
1507
1508 OPEN direct_pos(p_employee_id, p_approval_path_id);
1509 LOOP
1510 d_progress := 50;
1511 FETCH direct_pos INTO l_fwd_to_id;
1512 EXIT WHEN (direct_pos%NOTFOUND IS NULL) OR (direct_pos%NOTFOUND);
1513
1514 d_progress := 60;
1515 IF (PO_LOG.d_stmt) THEN
1516 PO_LOG.stmt(d_module, d_progress, 'l_fwd_to_id', l_fwd_to_id);
1517 END IF;
1518
1519 verify_authority(
1520 p_document_id => p_document_id
1521 , p_document_type => p_document_type
1522 , p_document_subtype => p_document_subtype
1523 , p_employee_id => l_fwd_to_id
1524 , x_return_status => l_ret_sts
1525 , x_return_code => l_ret_code
1526 , x_exception_msg => l_exc_msg
1527 , x_auth_failed_msg => l_fail_msg
1528 );
1529
1530 IF (l_ret_sts <> 'S')
1531 THEN
1532 d_progress := 70;
1533 d_msg := 'verify_authority threw unexpected error';
1534 l_ret_sts := 'U';
1535 RAISE PO_CORE_S.g_early_return_exc;
1536 END IF;
1537
1538 IF (l_ret_code IS NULL)
1539 THEN
1540 -- this supervisor can approve the document;
1541 d_progress := 80;
1542 EXIT;
1543 END IF;
1544
1545 END LOOP;
1546
1547 ELSIF (l_hr_xbg_profile <> 'Y') THEN
1548
1549 d_progress := 90;
1550
1551 OPEN direct_assign(p_employee_id, p_approval_path_id);
1552 LOOP
1553 d_progress := 100;
1554 FETCH direct_assign INTO l_fwd_to_id;
1555 EXIT WHEN (direct_assign%NOTFOUND IS NULL) OR (direct_assign%NOTFOUND);
1556
1557 d_progress := 110;
1558 IF (PO_LOG.d_stmt) THEN
1559 PO_LOG.stmt(d_module, d_progress, 'l_fwd_to_id', l_fwd_to_id);
1560 END IF;
1561
1562 verify_authority(
1563 p_document_id => p_document_id
1564 , p_document_type => p_document_type
1565 , p_document_subtype => p_document_subtype
1566 , p_employee_id => l_fwd_to_id
1567 , x_return_status => l_ret_sts
1568 , x_return_code => l_ret_code
1569 , x_exception_msg => l_exc_msg
1570 , x_auth_failed_msg => l_fail_msg
1571 );
1572
1573 IF (l_ret_sts <> 'S')
1574 THEN
1575 d_progress := 120;
1576 d_msg := 'verify_authority threw unexpected error';
1577 l_ret_sts := 'U';
1578 RAISE PO_CORE_S.g_early_return_exc;
1579 END IF;
1580
1581 IF (l_ret_code IS NULL)
1582 THEN
1583 -- this supervisor can approve the document;
1584 d_progress := 130;
1585 EXIT;
1586 END IF;
1587
1588 END LOOP;
1589
1590 ELSE
1591
1592 d_progress := 140;
1593
1594 OPEN direct_assign_xbg(p_employee_id);
1595 LOOP
1596 d_progress := 150;
1597 FETCH direct_assign_xbg INTO l_fwd_to_id;
1598 EXIT WHEN (direct_assign_xbg%NOTFOUND IS NULL) OR (direct_assign_xbg%NOTFOUND);
1599
1600 d_progress := 160;
1601 IF (PO_LOG.d_stmt) THEN
1602 PO_LOG.stmt(d_module, d_progress, 'l_fwd_to_id', l_fwd_to_id);
1603 END IF;
1604
1605 verify_authority(
1606 p_document_id => p_document_id
1607 , p_document_type => p_document_type
1608 , p_document_subtype => p_document_subtype
1609 , p_employee_id => l_fwd_to_id
1610 , x_return_status => l_ret_sts
1611 , x_return_code => l_ret_code
1612 , x_exception_msg => l_exc_msg
1613 , x_auth_failed_msg => l_fail_msg
1614 );
1615
1616 IF (l_ret_sts <> 'S')
1617 THEN
1618 d_progress := 170;
1619 d_msg := 'verify_authority threw unexpected error';
1620 l_ret_sts := 'U';
1621 RAISE PO_CORE_S.g_early_return_exc;
1622 END IF;
1623
1624 IF (l_ret_code IS NULL)
1625 THEN
1626 -- this supervisor can approve the document;
1627 d_progress := 180;
1628 EXIT;
1629 END IF;
1630
1631 END LOOP;
1632
1633 END IF; -- l_using_positions = 'Y'
1634
1635 IF (PO_LOG.d_stmt) THEN
1636 PO_LOG.stmt(d_module, d_progress, 'l_fwd_to_id', l_fwd_to_id);
1637 END IF;
1638
1639 ELSIF (l_forwarding_mode = 'HIERARCHY')
1640 THEN
1641
1642 IF (l_using_positions = 'Y')
1643 THEN
1644
1645 d_progress := 200;
1646 OPEN hier_pos(p_employee_id, p_approval_path_id);
1647 FETCH hier_pos INTO l_fwd_to_id;
1648 IF ((hier_pos%NOTFOUND IS NULL) or (hier_pos%NOTFOUND))
1649 THEN
1650 l_fwd_to_id := NULL;
1651 END IF;
1652
1653 d_progress := 210;
1654
1655 ELSE
1656
1657 d_progress := 220;
1658
1659 BEGIN
1660
1661 SELECT hre.supervisor_id
1662 INTO l_fwd_to_id
1663 FROM per_workforce_current_x hre --R12 CWK Enhancement
1664 WHERE hre.person_id = p_employee_id;
1665
1666 EXCEPTION
1667 WHEN no_data_found THEN
1668 l_fwd_to_id := NULL;
1669 END;
1670
1671 d_progress := 230;
1672
1673 END IF; -- l_using_positions = 'Y'
1674
1675 IF (PO_LOG.d_stmt) THEN
1676 PO_LOG.stmt(d_module, d_progress, 'l_fwd_to_id', l_fwd_to_id);
1677 END IF;
1678
1679 ELSE
1680
1681 l_ret_sts := 'U';
1682 d_msg := 'Invalid forwarding mode from po_document_types';
1683 RAISE PO_CORE_S.g_early_return_exc;
1684
1685 END IF; -- l_forwarding_mode = ...
1686
1687 EXCEPTION
1688 WHEN PO_CORE_S.g_early_return_exc THEN
1689 IF (PO_LOG.d_exc) THEN
1690 PO_LOG.exc(d_module, d_progress, d_msg);
1691 PO_LOG.stmt(d_module, d_progress, 'l_exc_msg', l_exc_msg);
1692 END IF;
1693 END;
1694
1695 IF direct_pos%ISOPEN THEN
1696 CLOSE direct_pos;
1697 END IF;
1698
1699 IF direct_assign%ISOPEN THEN
1700 CLOSE direct_assign;
1701 END IF;
1702
1703 IF direct_assign_xbg%ISOPEN THEN
1704 CLOSE direct_assign_xbg;
1705 END IF;
1706
1707 IF hier_pos%ISOPEN THEN
1708 CLOSE hier_pos;
1709 END IF;
1710
1711 x_return_status := l_ret_sts;
1712 x_forward_to_id := l_fwd_to_id;
1713
1714 IF (PO_LOG.d_proc) THEN
1715 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
1716 PO_LOG.proc_end(d_module, 'x_forward_to_id', x_forward_to_id);
1717 PO_LOG.proc_end(d_module);
1718 END IF;
1719
1720 EXCEPTION
1721 WHEN OTHERS THEN
1722
1723 IF direct_pos%ISOPEN THEN
1724 CLOSE direct_pos;
1725 END IF;
1726
1727 IF direct_assign%ISOPEN THEN
1728 CLOSE direct_assign;
1729 END IF;
1730
1731 IF direct_assign_xbg%ISOPEN THEN
1732 CLOSE direct_assign_xbg;
1733 END IF;
1734
1735 IF hier_pos%ISOPEN THEN
1736 CLOSE hier_pos;
1737 END IF;
1738
1739 x_return_status := 'U';
1740
1741 IF (PO_LOG.d_exc) THEN
1742 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
1743 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
1744 PO_LOG.proc_end(d_module);
1745 END IF;
1746
1747 RETURN;
1748 END find_forward_to_id;
1749
1750
1751
1752 ------------------------------------------------------------------------------
1753 --Start of Comments
1754 --Name: auto_update_close_state
1755 --Pre-reqs:
1756 -- None
1757 --Modifies:
1758 -- None, directly.
1759 --Locks:
1760 -- None, directly. Through do_action, locks the document header.
1761 --Function:
1762 -- Automatically updates the closed status of a document entity,
1763 -- based on the quantities received and/or billed. For example, if
1764 -- all of a shipment has been received, then the shipment is closed for
1765 -- receiving.
1766 -- Rolls up the close state as necessary.
1767 -- Uses do_action switchboard
1768 -- Replaces the UPDATE_CLOSE_STATE action in the Pro*C document manager
1769 --Parameters:
1770 --IN:
1771 -- p_document_id
1772 -- ID of the document's header (e.g. po_release_id, po_header_id, ...)
1773 -- p_document_type
1774 -- 'RELEASE', 'PO', 'PA'
1775 -- This method does nothing for 'PA' except return successfully
1776 -- p_document_subtype
1777 -- PO: 'STANDARD', 'PLANNED'
1778 -- PA: 'CONTRACT', 'BLANKET'
1779 -- RELEASE: 'SCHEDULED', 'BLANKET'
1780 -- p_line_id
1781 -- If acting on a header, pass NULL
1782 -- If acting on a line, pass in the po_line_id of the line.
1783 -- If acting on a shipment, pass in the po_line_id of the shipment's line.
1784 -- p_shipment_id
1785 -- If acting on a header, pass NULL
1786 -- If acting on a line, pass NULL
1787 -- If acting on a shipment, pass in the line_location_id of the shipment
1788 -- p_action_date
1789 -- Used for encumbrance purposes for final close and invoice open actions
1790 -- Defaults to SYSDATE
1791 -- p_calling_mode
1792 -- 'PO', 'RCV', or 'AP'
1793 -- Defaults to 'PO'
1794 -- p_called_from_conc
1795 -- Pass TRUE if this procedure is being called from within a concurrent program.
1796 -- Pass FALSE otherwise
1797 -- Defaults to FALSE
1798 -- Used for getting the correct login_id.
1799 --OUT:
1800 -- x_return_status
1801 -- 'S': auto update close state action had no unexpected errors
1802 -- In this case, check return_code for success/failure.
1803 -- 'U': auto update close state action failed with unexpected errors
1804 -- x_return_code
1805 -- 'STATE_FAILED': Document state check failed
1806 -- NULL: auto update close action action was successful
1807 -- x_exception_message
1808 -- If x_return_status = 'U', this parameter will
1809 -- contain an error stack in concatenated string form.
1810 --End of Comments
1811 -------------------------------------------------------------------------------
1812 PROCEDURE auto_update_close_state(
1813 p_document_id IN NUMBER
1814 , p_document_type IN VARCHAR2
1815 , p_document_subtype IN VARCHAR2
1816 , p_line_id IN NUMBER
1817 , p_shipment_id IN NUMBER
1818 , p_calling_mode IN VARCHAR2 DEFAULT 'PO'
1819 , p_called_from_conc IN BOOLEAN DEFAULT FALSE
1820 , x_return_status OUT NOCOPY VARCHAR2
1821 , x_exception_msg OUT NOCOPY VARCHAR2
1822 , x_return_code OUT NOCOPY VARCHAR2
1823 )
1824 IS
1825
1826 l_da_call_rec DOC_ACTION_CALL_REC_TYPE;
1827
1828 BEGIN
1829
1830 l_da_call_rec.action := g_doc_action_UPDATE_CLOSE_AUTO;
1831 l_da_call_rec.lock_document := TRUE;
1832
1833 l_da_call_rec.document_id := p_document_id;
1834 l_da_call_rec.document_type := p_document_type;
1835 l_da_call_rec.document_subtype := p_document_subtype;
1836 l_da_call_rec.line_id := p_line_id;
1837 l_da_call_rec.shipment_id := p_shipment_id;
1838 l_da_call_rec.calling_mode := p_calling_mode;
1839 l_da_call_rec.called_from_conc := p_called_from_conc;
1840
1841 do_action(p_action_ctl_rec => l_da_call_rec);
1842
1843 x_exception_msg := l_da_call_rec.error_msg;
1844 x_return_code := l_da_call_rec.return_code;
1845 x_return_status := l_da_call_rec.return_status;
1846
1847 END auto_update_close_state;
1848
1849
1850 ------------------------------------------------------------------------------
1851 --Start of Comments
1852 --Name: do_manual_close
1853 --Pre-reqs:
1854 -- None
1855 --Modifies:
1856 -- None, directly.
1857 --Locks:
1858 -- None, directly. Through do_action, locks the document header.
1859 --Function:
1860 -- Sets the closed status of a document entity, depending on
1861 -- the close or open action passed in via p_action.
1862 -- Rolls up the closed status as necessary when closing a line or shipment.
1863 -- Uses do_action switchboard
1864 --Parameters:
1865 --IN:
1866 -- p_action
1867 -- Use one of PO_DOCUMENT_ACTION_PVT.g_doc_action<>
1868 -- Where <> could be:
1869 -- OPEN, CLOSE, CLOSE_RCV, OPEN_RCV, CLOSE_INV, OPEN_INV, or FINALLY_CLOSE
1870 -- p_document_id
1871 -- ID of the document's header (e.g. po_release_id, po_header_id, ...)
1872 -- p_document_type
1873 -- 'RELEASE', 'PO', 'PA'
1874 -- p_document_subtype
1875 -- PO: 'STANDARD', 'PLANNED'
1876 -- PA: 'CONTRACT', 'BLANKET'
1877 -- RELEASE: 'SCHEDULED', 'BLANKET'
1878 -- p_line_id
1879 -- If acting on a header, pass NULL
1880 -- If acting on a line, pass in the po_line_id of the line.
1881 -- If acting on a shipment, pass in the po_line_id of the shipment's line.
1882 -- p_shipment_id
1883 -- If acting on a header, pass NULL
1884 -- If acting on a line, pass NULL
1885 -- If acting on a shipment, pass in the line_location_id of the shipment
1886 -- p_reason
1887 -- To be stored as the closed_reason on the line or shipment,
1888 -- or as the note in the action history table.
1889 -- p_action_date
1890 -- Used for encumbrance purposes for final close and invoice open actions
1891 -- Defaults to SYSDATE
1892 -- p_calling_mode
1893 -- 'PO', 'RCV', or 'AP'
1894 -- Defaults to 'PO'
1895 -- p_origin_doc_id
1896 -- For final close and invoice open actions, the id of the invoice
1897 -- NULL otherwise
1898 -- Defaults to NULL
1899 -- p_called_from_conc
1900 -- Pass TRUE if this procedure is being called from within a concurrent program.
1901 -- Pass FALSE otherwise
1902 -- Defaults to FALSE
1903 -- Used for getting the correct login_id.
1904 -- p_use_gl_date
1905 -- 'Y' or 'N'
1906 -- Defaults to 'N'
1907 -- Needed for encumbrance purposes, for final_close and invoice_open actions
1908 --OUT:
1909 -- x_return_status
1910 -- 'S': manual close action had no unexpected errors
1911 -- In this case, check return_code for success/failure.
1912 -- 'U': manual close action failed with unexpected errors
1913 -- x_return_code
1914 -- 'STATE_FAILED': Document state check failed
1915 -- 'SUBMISSION_FAILED': Submission check failed for final close action
1916 -- 'P', 'F', 'T': Encumbrance call not fully successful.
1917 -- NULL: manual close action was successful
1918 -- x_exception_message
1919 -- If x_return_status = 'U', this parameter will
1920 -- contain an error stack in concatenated string form.
1921 -- x_online_report_id
1922 -- ID to online report containing more detailed submission check
1923 -- or encumbrance results
1924 --End of Comments
1925 -------------------------------------------------------------------------------
1926 PROCEDURE do_manual_close(
1927 p_action IN VARCHAR2
1928 , p_document_id IN NUMBER
1929 , p_document_type IN VARCHAR2
1930 , p_document_subtype IN VARCHAR2
1931 , p_line_id IN NUMBER
1932 , p_shipment_id IN NUMBER
1933 , p_reason IN VARCHAR2
1934 , p_action_date IN DATE DEFAULT SYSDATE
1935 , p_calling_mode IN VARCHAR2 DEFAULT 'PO'
1936 , p_origin_doc_id IN NUMBER DEFAULT NULL
1937 , p_called_from_conc IN BOOLEAN DEFAULT FALSE
1938 , p_use_gl_date IN VARCHAR2 DEFAULT 'N'
1939 , x_return_status OUT NOCOPY VARCHAR2
1940 , x_exception_msg OUT NOCOPY VARCHAR2
1941 , x_return_code OUT NOCOPY VARCHAR2
1942 , x_online_report_id OUT NOCOPY NUMBER
1943 )
1944 IS
1945
1946 l_da_call_rec DOC_ACTION_CALL_REC_TYPE;
1947
1948 BEGIN
1949
1950 l_da_call_rec.action := p_action;
1951 l_da_call_rec.lock_document := TRUE;
1952
1953 l_da_call_rec.document_id := p_document_id;
1954 l_da_call_rec.document_type := p_document_type;
1955 l_da_call_rec.document_subtype := p_document_subtype;
1956 l_da_call_rec.line_id := p_line_id;
1957 l_da_call_rec.shipment_id := p_shipment_id;
1958 l_da_call_rec.note := p_reason;
1959 l_da_call_rec.action_date := p_action_date;
1960 l_da_call_rec.calling_mode := p_calling_mode;
1961 l_da_call_rec.origin_doc_id := p_origin_doc_id;
1962 l_da_call_rec.called_from_conc := p_called_from_conc;
1963 l_da_call_rec.use_gl_date := p_use_gl_date;
1964
1965 do_action(p_action_ctl_rec => l_da_call_rec);
1966
1967 x_online_report_id := l_da_call_rec.online_report_id;
1968 x_exception_msg := l_da_call_rec.error_msg;
1969 x_return_code := l_da_call_rec.return_code;
1970 x_return_status := l_da_call_rec.return_status;
1971
1972 END do_manual_close;
1973
1974
1975 -- Methods intended to be used only within PO_DOCUMENT_ACTION_XXXX packages
1976
1977 -- get the current error stack
1978 PROCEDURE get_error_message(
1979 x_error_message OUT NOCOPY VARCHAR2
1980 )
1981 IS
1982 BEGIN
1983
1984 x_error_message := g_err_message;
1985
1986 END get_error_message;
1987
1988 -- append to the error stack
1989 PROCEDURE error_msg_append(
1990 p_subprogram_name IN VARCHAR2
1991 , p_position IN NUMBER
1992 , p_message_text IN VARCHAR2
1993 )
1994 IS
1995 BEGIN
1996
1997 IF (g_err_message IS NULL)
1998 THEN
1999
2000 g_err_message := substr(p_subprogram_name || ':' || p_position || ':' || p_message_text, 1, 2000);
2001
2002 ELSE
2003
2004 g_err_message := substr(g_err_message || ' - ' || p_subprogram_name || ':' || p_position || ':' || p_message_text, 1, 2000);
2005
2006 END IF; -- g_err_message IS NULL
2007
2008 END error_msg_append;
2009
2010 -- append to the error stack
2011 PROCEDURE error_msg_append(
2012 p_subprogram_name IN VARCHAR2
2013 , p_position IN NUMBER
2014 , p_sqlcode IN NUMBER
2015 , p_sqlerrm IN VARCHAR2
2016 )
2017 IS
2018 BEGIN
2019
2020 error_msg_append(p_subprogram_name, p_position, p_sqlcode || p_sqlerrm);
2021
2022 END error_msg_append;
2023
2024 -- <R12 BEGIN INVCONV>
2025 PROCEDURE update_secondary_qty_cancelled (
2026 p_join_column IN VARCHAR2
2027 , p_entity_id IN NUMBER
2028 )
2029 IS
2030 CURSOR cur_ship_lines
2031 IS
2032 SELECT pol.item_id, poll.ship_to_organization_id, poll.po_header_id, poll.po_line_id,
2033 poll.line_location_id, poll.po_release_id, poll.quantity_cancelled,
2034 pol.unit_meas_lookup_code, poll.secondary_unit_of_measure
2035 FROM po_line_locations poll, po_lines pol;
2036
2037 TYPE rc IS REF CURSOR;
2038
2039 l_cursor rc;
2040 l_ship_rec cur_ship_lines%ROWTYPE;
2041 l_ship_column_list VARCHAR2 (2000);
2042 l_ship_table_list VARCHAR2 (2000);
2043 l_ship_where_clause VARCHAR2 (2000);
2044 l_converted_qty NUMBER;
2045 BEGIN
2046 -- assign column list
2047 l_ship_column_list :=
2048 'pol.item_id, poll.ship_to_organization_id, poll.po_header_id, poll.po_line_id, '
2049 || 'poll.line_location_id, poll.po_release_id, poll.quantity_cancelled, '
2050 || 'pol.unit_meas_lookup_code, poll.secondary_unit_of_measure ';
2051
2052 -- assign table list
2053 l_ship_table_list := 'po_line_locations poll, po_lines pol ';
2054
2055 -- build where clause
2056 l_ship_where_clause := 'poll.' || p_join_column || ' = ' || p_entity_id;
2057 l_ship_where_clause := l_ship_where_clause || ' AND poll.po_line_id = pol.po_line_id ';
2058 l_ship_where_clause := l_ship_where_clause
2059 || ' AND nvl(poll.cancel_flag, ' || '''N''' || ') = ' || '''I''';
2060 l_ship_where_clause := l_ship_where_clause
2061 || ' AND nvl(poll.closed_code, ' || '''OPEN''' || ') != ' || '''FINALLY CLOSED''';
2062 l_ship_where_clause := l_ship_where_clause || ' AND poll.secondary_unit_of_measure is not null ';
2063
2064 OPEN l_cursor
2065 FOR 'select '
2066 || l_ship_column_list
2067 || ' from '
2068 || l_ship_table_list
2069 || ' where '
2070 || l_ship_where_clause;
2071
2072 LOOP
2073 FETCH l_cursor
2074 INTO l_ship_rec;
2075
2076 EXIT WHEN l_cursor%NOTFOUND;
2077 l_converted_qty :=
2078 inv_convert.inv_um_convert (organization_id => l_ship_rec.ship_to_organization_id,
2079 item_id => l_ship_rec.item_id,
2080 lot_number => NULL,
2081 precision => 5,
2082 from_quantity => l_ship_rec.quantity_cancelled,
2083 from_unit => NULL,
2084 to_unit => NULL,
2085 from_name => l_ship_rec.unit_meas_lookup_code,
2086 to_name => l_ship_rec.secondary_unit_of_measure
2087 );
2088
2089 IF (l_converted_qty <> -99999)
2090 THEN
2091 UPDATE po_line_locations
2092 SET secondary_quantity_cancelled = l_converted_qty
2093 WHERE line_location_id = l_ship_rec.line_location_id;
2094 END IF;
2095 END LOOP;
2096
2097 CLOSE l_cursor;
2098 END update_secondary_qty_cancelled;
2099 -- <R12 END INVCONV>
2100
2101 ------------------------------------------------------------------------------
2102 --Start of Comments
2103 --Name: do_cancel
2104 --Pre-reqs:
2105 -- None
2106 --Modifies:
2107 -- All cancel columns and who columns for this document at the entity
2108 -- level of cancellation. API message list.
2109
2110 --Locks:
2111 -- None, directly. Through do_action, locks the document header.
2112 --Function:
2113 -- Cancels the document at the header, line, or shipment level
2114 -- depending upon the document ID parameters after performing validations.
2115 -- Validations include state checks and cancel submission checks. If
2116 -- p_cbc_enabled is 'Y', then the CBC accounting date is updated to be
2117 -- p_action_date. If p_cancel_reqs_flag is 'Y', then backing requisitions will
2118 -- also be cancelled if allowable. Otherwise, they will be recreated.
2119 -- Encumbrance is recalculated for cancelled entities if enabled. If the
2120 -- cancel action is successful, the document's cancel and who columns will be
2121 -- updated at the specified entity level. Otherwise, the document will remain
2122 -- unchanged. All changes will be committed upon success if p_commit is
2123 -- FND_API.G_TRUE.
2124
2125 --Parameters:
2126 --IN:
2127 -- p_entity_dtl_rec
2128 -- p_reason
2129 -- p_action
2130 -- p_action_date
2131 -- p_use_gl_date
2132 -- p_cancel_reqs_flag
2133 -- p_note_to_vendor
2134 -- p_launch_approvals_flag
2135 -- p_communication_method_option
2136 -- p_communication_method_value
2137 -- p_caller
2138 -- p_commit
2139 -- p_revert_pending_chg_flag
2140
2141
2142 --OUT:
2143 -- x_online_report_id
2144 -- ID to online report containing more detailed submission check
2145 -- or encumbrance results
2146 -- x_return_status
2147 -- 'S': manual close action had no unexpected errors
2148 -- In this case, check return_code for success/failure.
2149 -- 'U': manual close action failed with unexpected errors
2150 -- x_exception_msg
2151 -- If x_return_status = 'U', this parameter will
2152 -- contain an error stack in concatenated string form.
2153 -- x_return_code
2154 -- 'STATE_FAILED': Document state check failed
2155 -- 'SUBMISSION_FAILED': Submission check failed for final close action
2156 -- 'P', 'F', 'T': Encumbrance call not fully successful.
2157 -- NULL: manual close action was successful
2158
2159
2160
2161 -- Reference :
2162 -- <Bug 14254141 :Cancel Refactoring Project>
2163
2164 --End of Comments
2165 -------------------------------------------------------------------------------
2166
2167
2168 PROCEDURE do_cancel(
2169
2170 p_entity_dtl_rec IN entity_dtl_rec_type_tbl
2171 , p_reason IN VARCHAR2
2172 , p_action IN VARCHAR2
2173 , p_action_date IN DATE DEFAULT SYSDATE
2174 , p_use_gl_date IN VARCHAR2 DEFAULT 'N'
2175 , p_cancel_reqs_flag IN VARCHAR2
2176 , p_note_to_vendor IN VARCHAR2 DEFAULT NULL
2177 , p_launch_approvals_flag IN VARCHAR2 DEFAULT 'N'
2178 , p_communication_method_option IN VARCHAR2
2179 , p_communication_method_value IN VARCHAR2
2180 , p_caller IN VARCHAR2
2181 , p_commit IN VARCHAR2
2182 , p_revert_pending_chg_flag IN VARCHAR2
2183 , x_online_report_id OUT NOCOPY NUMBER
2184 , x_return_status OUT NOCOPY VARCHAR2
2185 , x_exception_msg OUT NOCOPY VARCHAR2
2186 , x_return_code OUT NOCOPY VARCHAR2
2187 )
2188
2189 IS
2190 l_da_call_rec DOC_ACTION_CALL_TBL_REC_TYPE;
2191 BEGIN
2192
2193 l_da_call_rec.action := p_action;
2194 l_da_call_rec.entity_dtl_record_tbl := p_entity_dtl_rec;
2195 l_da_call_rec.reason := p_reason;
2196 l_da_call_rec.action_date := p_action_date;
2197 l_da_call_rec.use_gl_date := p_use_gl_date;
2198 l_da_call_rec.cancel_reqs_flag := p_cancel_reqs_flag;
2199 l_da_call_rec.note_to_vendor := p_note_to_vendor;
2200 l_da_call_rec.launch_approval_flag := p_launch_approvals_flag;
2201 l_da_call_rec.communication_method_option := p_communication_method_option;
2202 l_da_call_rec.communication_method_value := p_communication_method_value;
2203 l_da_call_rec.caller := p_caller;
2204 l_da_call_rec.revert_pending_chg_flag := p_revert_pending_chg_flag;
2205
2206 l_da_call_rec.commit_flag := p_commit;
2207
2208 PO_Document_Cancel_PVT.cancel_document(
2209 p_da_call_rec => l_da_call_rec,
2210 p_api_version =>1.0,
2211 p_init_msg_list => FND_API.G_FALSE,
2212 x_return_status =>x_return_status,
2213 x_msg_data => x_exception_msg,
2214 x_return_code=>x_return_code);
2215
2216 x_online_report_id := l_da_call_rec.online_report_id;
2217
2218
2219 END do_cancel;
2220
2221
2222
2223 END PO_DOCUMENT_ACTION_PVT;