1 PACKAGE PO_DOCUMENT_ACTION_PVT AUTHID CURRENT_USER AS
2 -- $Header: POXVDACS.pls 120.9.12020000.2 2013/02/10 16:26:00 vegajula ship $
3
4 /*
5 * Document Actions
6 * One note: it is best not to call a PO_DOCUMENT_ACTION_PVT method
7 * from within the code flow of another PO_DOCUMENT_ACTION_PVT method.
8 * These actions use a common switchboard, do_action(), as well as some
9 * common error handling (via pl/sql package varaible) which could
10 * conflict.
11 * Exception: find_forward_to_id, which calls verify_authority
12 * The find_forward_to_id does not use the switchboard; it is a
13 * complicated wrapper around many calls to verify_authority.
14 */
15
16 -- Global Constants
17
18
19 -- Previously doc manager actions
20 g_doc_action_APPROVE CONSTANT VARCHAR2(30)
21 := 'APPROVE';
22 g_doc_action_REJECT CONSTANT VARCHAR2(30)
23 := 'REJECT';
24 g_doc_action_FORWARD CONSTANT VARCHAR2(30)
25 := 'FORWARD';
26 g_doc_action_CHECK_APPROVE CONSTANT VARCHAR2(30)
27 := 'DOCUMENT_STATUS_CHECK_APPROVE';
28 g_doc_action_CHECK_REJECT CONSTANT VARCHAR2(30)
29 := 'DOCUMENT_STATUS_CHECK_APPROVE';
30 g_doc_action_CHECK_AUTHORITY CONSTANT VARCHAR2(30)
31 := 'VERIFY_AUTHORITY_CHECK';
32 g_doc_action_UPDATE_CLOSE_AUTO VARCHAR2(30)
33 := 'UPDATE_CLOSE_STATE';
34
35
36 -- Previously User exit actions
37 g_doc_action_RETURN CONSTANT VARCHAR2(30)
38 := 'RETURN';
39 g_doc_action_FREEZE CONSTANT VARCHAR2(30)
40 := 'FREEZE';
41 g_doc_action_UNFREEZE CONSTANT VARCHAR2(30)
42 := 'UNFREEZE';
43 g_doc_action_HOLD CONSTANT VARCHAR2(30)
44 := 'HOLD';
45 g_doc_action_RELEASE_HOLD CONSTANT VARCHAR2(30)
46 := 'RELEASE HOLD';
47 g_doc_action_OPEN CONSTANT VARCHAR2(30)
48 := 'OPEN';
49 g_doc_action_CLOSE CONSTANT VARCHAR2(30)
50 := 'CLOSE';
51 g_doc_action_CLOSE_RCV CONSTANT VARCHAR2(30)
52 := 'RECEIVE CLOSE';
53 g_doc_action_OPEN_RCV CONSTANT VARCHAR2(30)
54 := 'RECEIVE OPEN';
55 g_doc_action_CLOSE_INV CONSTANT VARCHAR2(30)
56 := 'INVOICE CLOSE';
57 g_doc_action_OPEN_INV CONSTANT VARCHAR2(30)
58 := 'INVOICE OPEN';
59 g_doc_action_FINALLY_CLOSE CONSTANT VARCHAR2(30)
60 := 'FINALLY CLOSE';
61
62
63 -- Not yet converted
64 g_doc_action_CANCEL CONSTANT VARCHAR2(30)
65 := 'CANCEL';
66
67
68 -- Possibly Deprecated actions - use PO_DOCUMENT_FUNDS_PVT instead?
69 g_doc_action_RESERVE CONSTANT VARCHAR2(30)
70 := 'RESERVE';
71 g_doc_action_UNRESERVE CONSTANT VARCHAR2(30)
72 := 'UNRESERVE';
73 g_doc_action_APPRV_RESERVE CONSTANT VARCHAR2(30)
74 := 'APPROVE AND RESERVE';
75 g_doc_action_CHECK_FUNDS CONSTANT VARCHAR2(30)
76 := 'CHECK FUNDS';
77
78 -- Intentionally Deprecated actions
79 g_doc_action_APPROVE_DOC CONSTANT VARCHAR2(30)
80 := 'APPROVE_DOCUMENT';
81 g_doc_action_FORWARD_DOC CONSTANT VARCHAR2(30)
82 := 'FORWARD_DOCUMENT';
83 g_doc_action_REJECT_DOC CONSTANT VARCHAR2(30)
84 := 'REJECT_DOCUMENT';
85
86
87
88 -- document authorization statuses
89 g_doc_status_APPROVED CONSTANT VARCHAR2(30)
90 := 'APPROVED';
91 g_doc_status_REJECTED CONSTANT VARCHAR2(30)
92 := 'REJECTED';
93 g_doc_status_PREAPPROVED CONSTANT VARCHAR2(30)
94 := 'PRE-APPROVED';
95 g_doc_status_INPROCESS CONSTANT VARCHAR2(30)
96 := 'IN PROCESS';
97 g_doc_status_INCOMPLETE CONSTANT VARCHAR2(30)
98 := 'INCOMPLETE';
99 g_doc_status_REAPPROVAL CONSTANT VARCHAR2(30)
100 := 'REQUIRES REAPPROVAL';
101 g_doc_status_RETURNED CONSTANT VARCHAR2(30)
102 := 'RETURNED';
103 g_doc_status_SENT CONSTANT VARCHAR2(30)
104 := 'SENT';
105 -- CLM Apprvl - document authorization statuses for Modifications
106 g_doc_status_DRAFT CONSTANT VARCHAR2(30)
107 := 'DRAFT';
108
109 g_doc_status_COMPLETED CONSTANT VARCHAR2(30)
110 := 'COMPLETED';
111 g_doc_status_APP_AND_PROCESSED CONSTANT VARCHAR2(30)
112 := 'APPROVED AND PROCESSED';
113
114
115 -- document closed statuses
116 g_doc_closed_sts_OPEN CONSTANT VARCHAR2(30)
117 := 'OPEN';
118 g_doc_closed_sts_CLOSED CONSTANT VARCHAR2(30)
119 := 'CLOSED';
120 g_doc_closed_sts_CLOSED_INV CONSTANT VARCHAR2(30)
121 := 'CLOSED FOR INVOICE';
122 g_doc_closed_sts_CLOSED_RCV CONSTANT VARCHAR2(30)
123 := 'CLOSED FOR RECEIVING';
124 g_doc_closed_sts_FIN_CLOSED CONSTANT VARCHAR2(30)
125 := 'FINALLY CLOSED';
126
127
128
129 -- Global Types
130
131 /* TYPE: DOC_ACTION_CALL_REC_TYPE
132 *
133 * Note: for a given action, only a few of the following
134 * container variables are actually used.
135 *
136 * Descriptions and examples:
137 *
138 * Used primarily as IN values:
139 *
140 * The following are used by most actions:
141 * action: one of g_doc_action_XXXX, e.g. g_doc_action_APPROVE
142 * lock_document: pass as true if you the document should be locked
143 * before handling the logic for the action
144 * document_type: 'PO', 'PA', 'REQUISITION', or 'RELEASE'
145 * document_subtype: 'STANDARD', 'BLANKET', 'CONTRACT', 'SCHEDULED', etc.
146 * document_id: po_header_id, requisition_header_id, or po_release_id
147 * note: usually used for action history purposes
148 *
149 * The following are primarily used by workflow actions (approve, reject, etc.)
150 * employee_id: ID of the employee taking the action
151 * new_document_status: one of g_doc_status_XXXX, e.g. g_doc_status_APPROVED
152 * needed by some actions so that they know what
153 * status to set the document to on completion
154 * approval_path_id: usually used for discovering next approver
155 * forward_to_id: ID of an employee to forward the document to
156 *
157 * The following are primarily used by closed status related actions:
158 * line_id/shipment_id: needed for some actions
159 * calling_mode: either 'PO', 'RCV', or 'AP'; needed for close actions. 'AP'
160 * code flow is slightly different for certain actions.
161 * called_from_conc: TRUE means we are being called from within a
162 * concurrent program. Used only in the close
163 * actions, to get the right login_id. Should be false
164 * for all other actions.
165 * action_date: Used in invoice open and finally close actions to
166 * determine gl_override_date for encumbrance calls.
167 * origin_doc_id : Needed for JFMIP for final close and invoice open actions.
168 * For those cases, this is the invoice id. Should be NULL
169 * if not coming from AP.
170 * use_gl_date : Needed only for encumbrance purposes, for final close
171 * and invoice open. 'Y' or 'N'
172 * offline_code: does not seem to be used; often passed in as NULL
173 *
174 *
175 * Used primarily as OUT values:
176 *
177 * return_code: used by certain actions to indicate functional success or
178 * error. Often, a null value means unconditional success.
179 * return_status: 'S' or 'U'. A 'U' indicates an unexpected technical
180 * error/exception occurred. Can also be 'E', but rarely used
181 * in that capacity, as the return_code variable is often used
182 * instead to denote functional errors.
183 * error_msg: concatenated string representing a stack of error messages
184 * often only relevant if return_status is 'U'
185 * functional_error: string representing a functional error. Ofen
186 * contains a translated error string from fnd. Should
187 * usually be used only when return_status is 'S'
188 * online_report_id: stores ID of online report with more error info.
189 * Used by certain actions only.
190 *
191 */
192 TYPE DOC_ACTION_CALL_REC_TYPE IS RECORD
193 (
194 action VARCHAR2(30),
195 lock_document BOOLEAN,
196 document_type VARCHAR2(25),
197 document_subtype VARCHAR2(25),
198 document_id NUMBER,
199 draft_id NUMBER, -- CLM Apprvl
200 employee_id NUMBER,
201 new_document_status VARCHAR2(25),
202 approval_path_id NUMBER,
203 forward_to_id NUMBER,
204 note PO_ACTION_HISTORY.NOTE%TYPE,
205 return_code VARCHAR2(25),
206 return_status VARCHAR2(1), -- replaces return_value
207 error_msg VARCHAR2(2000),
208 functional_error VARCHAR2(2000),
209 line_id NUMBER,
210 shipment_id NUMBER,
211 calling_mode VARCHAR2(4),
212 called_from_conc BOOLEAN,
213 origin_doc_id NUMBER,
214 action_date DATE,
215 online_report_id NUMBER,
216 use_gl_date VARCHAR2(1),
217 offline_code VARCHAR2(1)
218 );
219
220
221 /* <Bug 14254141 :Cancel Refactoring Project >
222 * TYPE: entity_dtl_rec_type
223 *
224 * Description:
225 *
226 * Used primarily as IN value For Cancel Action:
227 * doc_id : Document Header Id of the entity being canceled
228 * For document Type PO/PA, it will PO Header Id
229 * For Docuemnt Type Release, it will be release id
230 * document_type :Document Type PO/PA/Release
231 * document_subtype :Document SubType:STANDARD/PLANNED/BLANKET/..
232 * entity_id :Id of the entity being canceled, ex:
233 * Po_hedaer_id if PO Header is canceled,
234 * Line_Loctaion_id if PO shipment is canceled,
235 * entity_level :Level at which cancel action is performed ex:
236 * HEADER/LINE/LINE_LOCATION
237 * entity_action_date :cancel action date
238 * process_entity_flag : This is used during the cancel action processing
239 * recreate_demand_flag :This is used during the cancel action processing
240 *
241 */
242
243
244 TYPE entity_dtl_rec_type IS RECORD(
245 doc_id number,
246 document_type VARCHAR2(25),
247 document_subtype VARCHAR2(25),
248 entity_id NUMBER,
249 entity_level VARCHAR2(25),
250 entity_action_date DATE ,
251 process_entity_flag varchar2(1) :='Y',
252 recreate_demand_flag varchar2(1) :='N'
253 ) ;
254
255 -- <Bug 14254141 :Cancel Refactoring Project >
256 TYPE entity_dtl_rec_type_tbl IS TABLE OF entity_dtl_rec_type;
257
258
259
260 /* <Bug 14254141 :Cancel Refactoring Project >
261 * TYPE: DOC_ACTION_CALL_TBL_REC_TYPE
262 *
263 *
264 * Descriptions and examples:
265 *
266 * Used primarily as IN value For Cancel Action:
267 *
268 * entity_dtl_record_tbl
269 * reason
270 * action :'CANCEL'
271 * action_date : Used to get gl_override_date for encumbrance calls and
272 * also to update the cancel_date/closed date on the document
273 * use_gl_date : Needed only for encumbrance purposes 'Y' or 'N'
274 * cancel_reqs_flag
275 * revert_pending_chg_flag
276 * note_to_vendor :usually used for action history purposes
277 * launch_approval_flag
278 * communication_method_option
279 * communication_method_value
280 * caller
281 * commit_flag
282 * Used primarily as OUT values:
283 *
284 * return_code: used by certain actions to indicate functional success or
285 * error. Often, a null value means unconditional success.
286 * return_status: 'S' or 'U'. A 'U' indicates an unexpected technical
287 * error/exception occurred. Can also be 'E', but rarely used
288 * in that capacity, as the return_code variable is often used
289 * instead to denote functional errors.
290 * online_report_id: stores ID of online report with more error info.
291 * Used by certain actions only.
292
293 */
294
295
296 TYPE DOC_ACTION_CALL_TBL_REC_TYPE IS RECORD
297 (
298 entity_dtl_record_tbl entity_dtl_rec_type_tbl
299 , reason VARCHAR2(240) --Bug 15836292
300 , action VARCHAR2(30)
301 , action_date DATE
302 , use_gl_date VARCHAR2(30)
303 , cancel_reqs_flag VARCHAR2(1)
304 , revert_pending_chg_flag VARCHAR2(1)
305 , note_to_vendor VARCHAR2(1000)
306 , launch_approval_flag VARCHAR2(1)
307 , communication_method_option VARCHAR2(30)
308 , communication_method_value VARCHAR2(2000) --Bug 15984307
309 , caller VARCHAR2(30)
310 , commit_flag VARCHAR2(1)
311 , online_report_id NUMBER
312 , return_status VARCHAR2(30)
313 , return_code VARCHAR2(30)
314 );
315
316
317 -- Methods
318
319 PROCEDURE do_action(
320 p_action_ctl_rec IN OUT NOCOPY PO_DOCUMENT_ACTION_PVT.doc_action_call_rec_type
321 );
322
323 PROCEDURE do_approve(
324 p_document_id IN VARCHAR2
325 , p_document_type IN VARCHAR2
326 , p_document_subtype IN VARCHAR2
327 , p_draft_id IN NUMBER := -1 --Bug 13444730
328 , p_note IN VARCHAR2
329 , p_approval_path_id IN NUMBER
330 , x_return_status OUT NOCOPY VARCHAR2
331 , x_exception_msg OUT NOCOPY VARCHAR2
332 );
333
334 PROCEDURE do_reject(
335 p_document_id IN VARCHAR2
336 , p_document_type IN VARCHAR2
337 , p_document_subtype IN VARCHAR2
341 , x_return_status OUT NOCOPY VARCHAR2
338 , p_draft_id IN NUMBER := -1 -- CLM Apprvl
339 , p_note IN VARCHAR2
340 , p_approval_path_id IN NUMBER
342 , x_return_code OUT NOCOPY VARCHAR2
343 , x_exception_msg OUT NOCOPY VARCHAR2
344 , x_online_report_id OUT NOCOPY NUMBER
345 );
346
347
348 PROCEDURE do_forward(
349 p_document_id IN VARCHAR2
350 , p_document_type IN VARCHAR2
351 , p_document_subtype IN VARCHAR2
352 , p_new_doc_status IN VARCHAR2
353 , p_note IN VARCHAR2
354 , p_approval_path_id IN NUMBER
355 , p_forward_to_id IN NUMBER
356 , x_return_status OUT NOCOPY VARCHAR2
357 , x_exception_msg OUT NOCOPY VARCHAR2
358 );
359
360 PROCEDURE do_return(
361 p_document_id IN VARCHAR2
362 , p_document_type IN VARCHAR2
363 , p_document_subtype IN VARCHAR2
364 , p_note IN VARCHAR2
365 , p_approval_path_id IN NUMBER
366 , x_return_status OUT NOCOPY VARCHAR2
367 , x_return_code OUT NOCOPY VARCHAR2
368 , x_exception_msg OUT NOCOPY VARCHAR2
369 , x_online_report_id OUT NOCOPY NUMBER
370 );
371
372 PROCEDURE do_freeze(
373 p_document_id IN VARCHAR2
374 , p_document_type IN VARCHAR2
375 , p_document_subtype IN VARCHAR2
376 , p_reason IN VARCHAR2
377 , x_return_status OUT NOCOPY VARCHAR2
378 , x_return_code OUT NOCOPY VARCHAR2
379 , x_exception_msg OUT NOCOPY VARCHAR2
380 );
381
382 PROCEDURE do_unfreeze(
383 p_document_id IN VARCHAR2
384 , p_document_type IN VARCHAR2
385 , p_document_subtype IN VARCHAR2
386 , p_reason IN VARCHAR2
387 , x_return_status OUT NOCOPY VARCHAR2
388 , x_return_code OUT NOCOPY VARCHAR2
389 , x_exception_msg OUT NOCOPY VARCHAR2
390 );
391
392 PROCEDURE do_hold(
393 p_document_id IN VARCHAR2
394 , p_document_type IN VARCHAR2
395 , p_document_subtype IN VARCHAR2
396 , p_reason IN VARCHAR2
397 , x_return_status OUT NOCOPY VARCHAR2
398 , x_return_code OUT NOCOPY VARCHAR2
399 , x_exception_msg OUT NOCOPY VARCHAR2
400 );
401
402 PROCEDURE do_release_hold(
403 p_document_id IN VARCHAR2
404 , p_document_type IN VARCHAR2
405 , p_document_subtype IN VARCHAR2
406 , p_reason IN VARCHAR2
407 , x_return_status OUT NOCOPY VARCHAR2
408 , x_return_code OUT NOCOPY VARCHAR2
409 , x_exception_msg OUT NOCOPY VARCHAR2
410 );
411
412 PROCEDURE verify_authority(
413 p_document_id IN VARCHAR2
414 , p_document_type IN VARCHAR2
415 , p_document_subtype IN VARCHAR2
416 , p_employee_id IN VARCHAR2
417 , x_return_status OUT NOCOPY VARCHAR2
418 , x_return_code OUT NOCOPY VARCHAR2
419 , x_exception_msg OUT NOCOPY VARCHAR2
420 , x_auth_failed_msg OUT NOCOPY VARCHAR2
421 );
422
423 PROCEDURE check_doc_status_approve(
424 p_document_id IN VARCHAR2
425 , p_document_type IN VARCHAR2
426 , p_document_subtype IN VARCHAR2
427 , x_return_status OUT NOCOPY VARCHAR2
428 , x_return_code OUT NOCOPY VARCHAR2
429 , x_exception_msg OUT NOCOPY VARCHAR2
430 );
431
432 PROCEDURE check_doc_status_reject(
433 p_document_id IN VARCHAR2
434 , p_document_type IN VARCHAR2
435 , p_document_subtype IN VARCHAR2
436 , x_return_status OUT NOCOPY VARCHAR2
437 , x_return_code OUT NOCOPY VARCHAR2
438 , x_exception_msg OUT NOCOPY VARCHAR2
439 );
440
441 PROCEDURE find_forward_to_id(
442 p_document_id IN NUMBER
443 , p_document_type IN VARCHAR2
444 , p_document_subtype IN VARCHAR2
445 , p_employee_id IN NUMBER
446 , p_approval_path_id IN NUMBER
447 , x_return_status OUT NOCOPY VARCHAR2
448 , x_forward_to_id OUT NOCOPY NUMBER
449 );
450
451 PROCEDURE auto_update_close_state(
452 p_document_id IN NUMBER
453 , p_document_type IN VARCHAR2
454 , p_document_subtype IN VARCHAR2
455 , p_line_id IN NUMBER DEFAULT NULL
456 , p_shipment_id IN NUMBER DEFAULT NULL
457 , p_calling_mode IN VARCHAR2 DEFAULT 'PO'
458 , p_called_from_conc IN BOOLEAN DEFAULT FALSE
459 , x_return_status OUT NOCOPY VARCHAR2
460 , x_exception_msg OUT NOCOPY VARCHAR2
461 , x_return_code OUT NOCOPY VARCHAR2
462 );
463
464 PROCEDURE do_manual_close(
465 p_action IN VARCHAR2
466 , p_document_id IN NUMBER
467 , p_document_type IN VARCHAR2
468 , p_document_subtype IN VARCHAR2
469 , p_line_id IN NUMBER
470 , p_shipment_id IN NUMBER
471 , p_reason IN VARCHAR2
472 , p_action_date IN DATE DEFAULT SYSDATE
473 , p_calling_mode IN VARCHAR2 DEFAULT 'PO'
474 , p_origin_doc_id IN NUMBER DEFAULT NULL
475 , p_called_from_conc IN BOOLEAN DEFAULT FALSE
476 , p_use_gl_date IN VARCHAR2 DEFAULT 'N'
477 , x_return_status OUT NOCOPY VARCHAR2
478 , x_exception_msg OUT NOCOPY VARCHAR2
479 , x_return_code OUT NOCOPY VARCHAR2
483 --<Bug 14254141 :Cancel Refactoring Project >
480 , x_online_report_id OUT NOCOPY NUMBER
481 );
482
484 PROCEDURE do_cancel(
485 p_entity_dtl_rec IN entity_dtl_rec_type_tbl
486 , p_reason IN VARCHAR2
487 , p_action IN VARCHAR2
488 , p_action_date IN DATE DEFAULT SYSDATE
489 , p_use_gl_date IN VARCHAR2 DEFAULT 'N'
490 , p_cancel_reqs_flag IN VARCHAR2
491 , p_note_to_vendor IN VARCHAR2 DEFAULT NULL
492 , p_launch_approvals_flag IN VARCHAR2 DEFAULT 'N'
493 , p_communication_method_option IN VARCHAR2 DEFAULT NULL
494 , p_communication_method_value IN VARCHAR2 DEFAULT NULL
495 , p_caller IN VARCHAR2
496 , p_commit IN VARCHAR2 DEFAULT 'N'
497 , p_revert_pending_chg_flag IN VARCHAR2 DEFAULT 'Y'
498 , x_online_report_id OUT NOCOPY NUMBER
499 , x_return_status OUT NOCOPY VARCHAR2
500 , x_exception_msg OUT NOCOPY VARCHAR2
501 , x_return_code OUT NOCOPY VARCHAR2
502 );
503 -- PO_DOCUMENT_ACTION_XXXX Shared Error Message Trace Handlers
504 -- These should not be called outside of DOCUMENT_ACTION code.
505 PROCEDURE get_error_message(
506 x_error_message OUT NOCOPY VARCHAR2
507 );
508
509 PROCEDURE error_msg_append(
510 p_subprogram_name IN VARCHAR2
511 , p_position IN NUMBER
512 , p_message_text IN VARCHAR2
513 );
514
515 PROCEDURE error_msg_append(
516 p_subprogram_name IN VARCHAR2
517 , p_position IN NUMBER
518 , p_sqlcode IN NUMBER
519 , p_sqlerrm IN VARCHAR2
520 );
521
522 -- <R12 BEGIN INVCONV>
523 PROCEDURE update_secondary_qty_cancelled (
524 p_join_column IN VARCHAR2
525 , p_entity_id IN NUMBER
526 );
527 -- <R12 END INVCONV>
528
529 END PO_DOCUMENT_ACTION_PVT;
[Home] [Help]