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