[Home] [Help]
PACKAGE BODY: APPS.PO_ACTIONS
Source
1 PACKAGE BODY PO_ACTIONS AS
2 /* $Header: POXPOACB.pls 120.0 2005/06/01 19:37:03 appldev noship $ */
3
4 --<DBI Req Fulfillment 11.5.11 start>
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'PO_ACTIONS';
6
7 g_log_head CONSTANT VARCHAR2(50) := 'po.plsql.' || g_pkg_name || '.';
8
9 g_debug_stmt BOOLEAN := PO_DEBUG.is_debug_stmt_on;
10
11 g_debug_unexp BOOLEAN := PO_DEBUG.is_debug_unexp_on;
12 --<DBI Req Fulfillment 11.5.11 End>
13
14 -- Constants :
15 -- This is used as a delimiter in the Debug Info String
16
17 g_delim CONSTANT VARCHAR2(1) := '
18 ';
19
20
21 -- Debug String
22
23 -- bug 456040
24 -- added x_max_lenth to check for the length of g_dbug
25 --
26 x_max_length CONSTANT NUMBER := 32760;
27 g_dbug VARCHAR2(32767) := null;
28
29 -- bug 572638
30 -- global variable to indicate if it is called by a concurrent program
31
32 g_conc_flag VARCHAR2(1);
33
34 --bug 3425540: add separate exception handlers to consolidate rollbacks
35 g_return_true_exc EXCEPTION;
36 g_return_false_exc EXCEPTION;
37
38 /* ----------------------------------------------------------------------- */
39 /* */
40 /* Private Function Definition */
41 /* */
42 /* ----------------------------------------------------------------------- */
43
44 FUNCTION close_auto(p_docid IN NUMBER,
45 p_doctyp IN VARCHAR2,
46 p_docsubtyp IN VARCHAR2,
47 p_lineid IN NUMBER,
48 p_shipid IN NUMBER,
49 p_action IN VARCHAR2,
50 p_calling_mode IN VARCHAR2,
51 p_return_code IN OUT NOCOPY VARCHAR2) RETURN BOOLEAN;
52
53 /* bug 1007829: frkhan
54 ** New parameter p_action_date has been added into function close_manual
55 */
56
57 FUNCTION close_manual(p_docid IN NUMBER,
58 p_doctyp IN VARCHAR2,
59 p_docsubtyp IN VARCHAR2,
60 p_lineid IN NUMBER,
61 p_shipid IN NUMBER,
62 p_action IN VARCHAR2,
63 p_action_date IN DATE DEFAULT SYSDATE,
64 p_reason IN VARCHAR2,
65 p_conc_flag IN VARCHAR2,
66 -- <JFMIP:Re-open Finally Match Shipment FPI>
67 p_calling_mode IN VARCHAR2,
68 p_return_code IN OUT NOCOPY VARCHAR2
69 -- JFMIP : PO needs to have reference to invoice
70 , p_origin_doc_id IN NUMBER
71 ) RETURN BOOLEAN;
72
73
74
75
76
77 /* ----------------------------------------------------------------------- */
78 /* */
79 /* Determine and Update the Close Status of Purchase Order Shipments */
80 /* and rollup if necessary */
81 /* */
82 /* Auto Closing determines and updates the Close Status of the Shipments */
83 /* and rolls up to the Lines and Headers */
84 /* */
85 /* Manual Closing determines and updates the Close Status of Shipments, */
86 /* Lines or Headers and rolls up if required */
87 /* */
88 /* When the parameter p_auto_close is set to 'Y', Auto Closing is */
89 /* invoked; otherwise Manual Closing is invoked */
90 /* */
91 /* ----------------------------------------------------------------------- */
92
93 -- Parameters :
94
95 -- p_docid : Header ID for Document
96
97 -- p_doctyp : Document Type
98
99 -- p_lineid : Line ID for Document
100
101 -- p_shipid : Ship ID for Document
102
103 -- p_action : Action to be performed
104
105 -- p_reason : Reason for Closing. This must be entered for Manual Closing
106
107 -- p_calling_mode : Whether being invoked from 'PO', 'RCV' or 'AP'. This
108 -- determines which of the Closed States needs to be
109 -- checked (receiving, invoicing or both). This must be
110 -- entered for Auto Closing
111
112 -- p_conc_flag : Whether invoked from a Concurrent Process. This must be
113 -- entered for Manual Closing and is used by the Funds Checker
114
115 -- p_return_code : Return Status of PO Closing
116
117 -- p_auto_close : Whether to invoke Auto Closing or Manual Closing
118
119 /* bug 1007829: frkhan
120 ** New parameter p_action_date is added to function close_po()
121 */
122
123 FUNCTION close_po(p_docid IN NUMBER,
124 p_doctyp IN VARCHAR2,
125 p_docsubtyp IN VARCHAR2,
126 p_lineid IN NUMBER,
127 p_shipid IN NUMBER,
128 p_action IN VARCHAR2,
129 p_reason IN VARCHAR2 DEFAULT NULL,
130 p_calling_mode IN VARCHAR2 DEFAULT 'PO',
131 p_conc_flag IN VARCHAR2 DEFAULT 'N',
132 p_return_code IN OUT NOCOPY VARCHAR2,
133 p_auto_close IN VARCHAR2 DEFAULT 'Y',
134 p_action_date IN DATE DEFAULT SYSDATE,
135 -- JFMIP : PO needs to have reference to invoice
136 p_origin_doc_id IN NUMBER DEFAULT NULL) RETURN BOOLEAN IS
137
138 BEGIN
139
140 -- bug 456040
141 -- Initialize g_dbug and check for its length to prevent from
142 -- getting bigger than the max_length allowed.
143
144 g_dbug := 'Debug' || g_delim;
145
146 IF LENGTH (g_dbug) < x_max_length THEN
147 g_dbug := g_dbug ||
148 'Starting PO Closing:' || g_delim ||
149 'Auto Close:' || p_auto_close || g_delim ||
150 'Hdr:' || p_docid || g_delim ||
151 'Type:' || p_doctyp || g_delim ||
152 'Subtype:' || p_docsubtyp || g_delim ||
153 'Line:' || p_lineid || g_delim ||
154 'Ship:' || p_shipid || g_delim ||
155 'Action:' || p_action || g_delim ||
156 'Reason:' || p_reason || g_delim ||
157 'Calling Mode:' || p_calling_mode || g_delim ||
158 'Conc:' || p_conc_flag || g_delim;
159 END IF;
160
161 -- bug 572638
162 -- set g_conc_flag
163 g_conc_flag := p_conc_flag;
164
165 if p_auto_close = 'N' then
166
167 /* bug 1007829: frkhan
168 ** Passing p_action_date as the value of new parameter
169 ** p_action_date in function close_manual()
170 */
171
172 if not close_manual(p_docid => p_docid,
173 p_doctyp => p_doctyp,
174 p_docsubtyp => p_docsubtyp,
175 p_lineid => p_lineid,
176 p_shipid => p_shipid,
177 p_action => p_action,
178 p_action_date => p_action_date,
179 p_reason => p_reason,
180 p_conc_flag => p_conc_flag,
181 -- <JFMIP:Re-open Finally Match Shipment FPI>
182 p_calling_mode => p_calling_mode,
183 p_return_code => p_return_code
184 -- JFMIP: PO needs to have reference to invoice
185 , p_origin_doc_id => p_origin_doc_id
186 )
187 then
188
189 PO_MESSAGE_S.APP_ERROR(error_name => 'PO_ALL_TRACE_ERROR',
190 token1 => 'FILE',
191 value1 => 'PO_ACTIONS',
192 token2 => 'ERR_NUMBER',
193 value2 => '005',
194 token3 => 'SUBROUTINE',
195 value3 => 'CLOSE_PO()');
196 return(FALSE);
197
198 end if;
199
200 else
201
202 if not close_auto(p_docid => p_docid,
203 p_doctyp => p_doctyp,
204 p_docsubtyp => p_docsubtyp,
205 p_lineid => p_lineid,
206 p_shipid => p_shipid,
207 p_action => p_action,
208 p_calling_mode => p_calling_mode,
209 p_return_code => p_return_code) then
210
211 PO_MESSAGE_S.APP_ERROR(error_name => 'PO_ALL_TRACE_ERROR',
212 token1 => 'FILE',
213 value1 => 'PO_ACTIONS',
214 token2 => 'ERR_NUMBER',
215 value2 => '010',
216 token3 => 'SUBROUTINE',
217 value3 => 'CLOSE_PO()');
218 return(FALSE);
219
220 end if;
221
222 end if;
223
224 return(TRUE);
225
226
227 EXCEPTION
228
229 WHEN OTHERS THEN
230
231 PO_MESSAGE_S.SQL_ERROR(routine => 'PO_ACTIONS',
232 location => '015',
233 error_code => SQLCODE);
234
235 return(FALSE);
236
237 END close_po;
238
239
240 /* ----------------------------------------------------------------------- */
241
242 -- Update the Status of Shipments, based on the Receiving and Invoicing
243 -- Closure Point Quantities, and Rollup if necessary
244
245 -- Closure Points and Tolerance Levels are set in po_line_locations (for
246 -- tolerances) and in po_system_parameters (for closed codes) respectively
247
248 -- Since AP may call this module as a non-employee (clerk), we do not
249 -- generate an error if the user_id is not an employee
250
251 -- Since Auto Close may be invoked for any Authorization Status, we do not
252 -- check the Approved Flag; however, the Shipment must not be Finally Closed
253
254 -- <Doc Manager Rewrite 11.5.11>: Removed logic from this package; This
255 -- method is now wraps PO_DOCUMENT_ACTION_PVT.auto_close_update_state.
256
257 FUNCTION close_auto(p_docid IN NUMBER,
258 p_doctyp IN VARCHAR2,
259 p_docsubtyp IN VARCHAR2,
260 p_lineid IN NUMBER,
261 p_shipid IN NUMBER,
262 p_action IN VARCHAR2,
263 p_calling_mode IN VARCHAR2,
264 p_return_code IN OUT NOCOPY VARCHAR2) RETURN BOOLEAN IS
265
266 l_ret_sts VARCHAR2(1);
267 l_ret_code VARCHAR2(40);
268 l_exc_msg VARCHAR2(2000);
269
270 l_from_conc BOOLEAN;
271
272 BEGIN
273
274 -- If called for an Agreement, return now
275
276 if p_doctyp = 'PA' then
277 return(TRUE);
278 end if;
279
280 -- Check that Calling Mode is Valid
281
282 if p_calling_mode not in ('PO', 'RCV', 'AP') then
283
284 PO_MESSAGE_S.APP_ERROR(error_name => 'PO_ALL_UNKNOWN_CODE',
285 token1 => 'FILE',
286 value1 => 'PO_ACTIONS',
287 token2 => 'ERR_NUMBER',
288 value2 => '055',
289 token3 => 'CODE',
290 value3 => 'CALL_MODE');
291 return(FALSE);
292
293 end if;
294
295 IF (NVL(g_conc_flag, 'N') = 'Y')
296 THEN
297 l_from_conc := TRUE;
298 ELSE
299 l_from_conc := FALSE;
300 END IF;
301
302 PO_DOCUMENT_ACTION_PVT.auto_update_close_state(
303 p_document_id => p_docid
304 , p_document_type => p_doctyp
305 , p_document_subtype => p_docsubtyp
306 , p_line_id => p_lineid
307 , p_shipment_id => p_shipid
308 , p_calling_mode => p_calling_mode
309 , p_called_from_conc => l_from_conc
310 , x_return_status => l_ret_sts
311 , x_exception_msg => l_exc_msg
312 , x_return_code => l_ret_code
313 );
314
315
316 IF (l_ret_sts <> 'S')
317 THEN
318
319 PO_MESSAGE_S.APP_ERROR(error_name => 'PO_ALL_TRACE_ERROR',
320 token1 => 'FILE',
321 value1 => 'PO_ACTIONS',
322 token2 => 'ERR_NUMBER',
323 value2 => '065',
324 token3 => 'SUBROUTINE',
325 value3 => 'CLOSE_AUTO()');
326 return(FALSE);
327
328 END IF;
329
330 IF (l_ret_code = 'STATE_FAILED')
331 THEN
332
333 p_return_code := 'STATE_FAILED';
334 return(TRUE);
335
336 END IF;
337
338 return(TRUE);
339
340
341 EXCEPTION
342
343 WHEN OTHERS THEN
344
345
346 PO_MESSAGE_S.SQL_ERROR(routine => 'PO_ACTIONS',
347 location => '080',
348 error_code => SQLCODE);
349
350 return(FALSE);
351
352 END close_auto;
353
354
355 /* ----------------------------------------------------------------------- */
356
357 -- Determine the Close Status of PO Shipments and rollup to the PO Lines,
358 -- Releases or Headers
359
360 /* bug 1007829: frkhan
361 ** New parameter p_action_date has been added into function close_manual()
362 ** and declaring variables l_override_period, open_date, X_row_exists.
363 ** Also added cursor get_next_open_prd_sdate
364 */
365
366 -- <Doc Manager Rewrite 11.5.11>: Removed logic from this package; This
367 -- method is now wraps PO_DOCUMENT_ACTION_PVT.do_manual_close.
368
369 FUNCTION close_manual(p_docid IN NUMBER,
370 p_doctyp IN VARCHAR2,
371 p_docsubtyp IN VARCHAR2,
372 p_lineid IN NUMBER,
373 p_shipid IN NUMBER,
374 p_action IN VARCHAR2,
375 p_action_date IN DATE DEFAULT SYSDATE,
376 p_reason IN VARCHAR2,
377 p_conc_flag IN VARCHAR2,
378 -- <JFMIP:Re-open Finally Match Shipment FPI>
379 p_calling_mode IN VARCHAR2,
380 p_return_code IN OUT NOCOPY VARCHAR2
381 -- JFMIP : PO needs to have reference to invoice
382 , p_origin_doc_id IN NUMBER
383 ) RETURN BOOLEAN IS
384
385 l_ret_sts VARCHAR2(1);
386 l_ret_code VARCHAR2(40);
387 l_exc_msg VARCHAR2(2000);
388 l_online_report_id NUMBER;
389
390 l_progress VARCHAR2(3);
391
392 l_from_conc BOOLEAN;
393
394 BEGIN
395
396 l_progress := '010';
397
398 IF (NVL(p_conc_flag, 'N') = 'Y')
399 THEN
400 l_from_conc := TRUE;
401 ELSE
402 l_from_conc := FALSE;
403 END IF;
404
405 PO_DOCUMENT_ACTION_PVT.do_manual_close(
406 p_action => p_action
407 , p_document_id => p_docid
408 , p_document_type => p_doctyp
409 , p_document_subtype => p_docsubtyp
410 , p_line_id => p_lineid
411 , p_shipment_id => p_shipid
412 , p_reason => p_reason
413 , p_action_date => p_action_date
414 , p_calling_mode => p_calling_mode
415 , p_origin_doc_id => p_origin_doc_id
416 , p_called_from_conc => l_from_conc
417 , p_use_gl_date => PO_DOCUMENT_FUNDS_PVT.g_parameter_NO
418 , x_return_status => l_ret_sts
419 , x_exception_msg => l_exc_msg
420 , x_return_code => l_ret_code
421 , x_online_report_id => l_online_report_id
422 );
423
424 l_progress := '020';
425
426 IF (l_ret_sts <> 'S') THEN
427
428 PO_MESSAGE_S.APP_ERROR(error_name => 'PO_ALL_TRACE_ERROR',
429 token1 => 'FILE',
430 value1 => 'PO_ACTIONS',
431 token2 => 'ERR_NUMBER',
432 value2 => '115',
433 token3 => 'SUBROUTINE',
434 value3 => 'CLOSE_MANUAL()');
435
436 RAISE g_return_false_exc;
437
438 END IF;
439
440 IF (l_ret_code = 'STATE_FAILED') THEN
441
442 p_return_code := 'STATE_FAILED';
443 raise g_return_true_exc;
444
445 END IF;
446
447 IF (l_ret_code IS NOT NULL) THEN
448
449 -- for backwards compatibility with callers,
450 -- mask encumbrance return codes as submission_failed.
451
452 p_return_code := 'SUBMISSION_FAILED';
453 raise g_return_true_exc;
454
455 END IF;
456
457 return(TRUE);
458
459 EXCEPTION
460
461 WHEN g_return_true_exc THEN
462 return(TRUE);
463
464 WHEN g_return_false_exc THEN
465 return(FALSE);
466
467 WHEN OTHERS THEN
468
469 PO_MESSAGE_S.SQL_ERROR(routine => 'PO_ACTIONS',
470 location => l_progress,
471 error_code => SQLCODE);
472
473 return(FALSE);
474
475 END close_manual;
476
477
478
479 /* ----------------------------------------------------------------------- */
480 --<DBI Req Fulfillment 11.5.11 Start >
481 -------------------------------------------------------------------------------
482 --Start of Comments
483 --Name:get_closure_dates
484 --Pre-reqs:
485 -- None.
486 --Modifies:
487 -- None.
488 --Locks:
489 -- None.
490 --Function:
491 -- This function returns the closure date for a line_location_id
492 -- depending upon the call mode
493 -- INVOICE CLOSE: maximum invoice date from the ap_invoices_all
494 -- RECEIVE CLOSE: maximum transaction date from the rcv_transactions
495 -- CLOSE: maximum of the invoice_date and receving transaction date
496 --Parameters:
497 --IN:
498 --p_call_mode
499 -- Valid values are 'CLOSE','INVOICE CLOSE', or 'RECEIVE CLOSE'.
500 -- It determines the type of closure date to be determined
501 --p_line_location_id
502 -- Line Location id for Document
503 --IN OUT:
504 --N/A
505 --Testing:
506 -- Refer the Technical Design for 'DBI requisition Fulfillment'
507 --End of Comments
508 -----------------------------------------------------------------------------
509
510 FUNCTION get_closure_dates(p_call_mode IN VARCHAR2,
511 p_line_location_id IN NUMBER
512 ) RETURN DATE
513 IS
514 l_closed_date po_line_locations_all.closed_date%type;
515 l_progress VARCHAR2(3);
516 l_log_head CONSTANT VARCHAR2(100) := g_log_head || 'GET_CLOSURE_DATES';
517
518 BEGIN
519 l_progress := '001';
520 l_closed_date := NULL;
521
522 if p_call_mode = 'CLOSE' then
523 select max(action_date)
524 into l_closed_date
525 from
526 ( select RT.transaction_date action_date
527 from rcv_transactions RT
528 where RT.TRANSACTION_TYPE IN ('RECEIVE','ACCEPT','CORRECT','MATCH')
529 and RT.po_line_location_id = p_line_location_id
530 union
531 select AP.invoice_date action_date
532 from ap_invoice_distributions_all AD,
533 ap_invoices_all AP,
534 po_distributions_all POD
535 where AD.invoice_id = AP.invoice_id
536 and AD.po_distribution_id = POD.po_distribution_id
537 and POD.line_location_id = p_line_location_id
538 and nvl(AD.reversal_flag,'N') NOT IN ('Y')
539 );
540 l_progress := '010';
541 IF g_debug_stmt THEN
542 PO_DEBUG.debug_begin(l_log_head);
543 PO_DEBUG.debug_stmt(l_log_head,
544 l_progress,
545 'End of CLOSE mode call');
546 END IF;
547 elsif p_call_mode = 'INVOICE CLOSE' then
548 select max(AP.invoice_date)
549 into l_closed_date
550 from ap_invoice_distributions_all AD,
551 ap_invoices_all AP,
552 po_distributions_all POD
553 where AD.invoice_id = AP.invoice_id
554 and AD.po_distribution_id = POD.po_distribution_id
555 and POD.line_location_id = p_line_location_id
556 and nvl(AD.reversal_flag,'N') NOT IN ('Y');
557
558 l_progress := '020';
559 IF g_debug_stmt THEN
560 PO_DEBUG.debug_stmt(l_log_head,
561 l_progress,
562 'End of INVOICE CLOSE mode call');
563 END IF;
564 else -- call_mode = 'RECEIVE CLOSE'
565 select max(RT.transaction_date)
566 into l_closed_date
567 from rcv_transactions RT
568 where RT.TRANSACTION_TYPE IN ('RECEIVE','ACCEPT','CORRECT','MATCH')
569 and RT.po_line_location_id = p_line_location_id;
570 l_progress := '030';
571 IF g_debug_stmt THEN
572 PO_DEBUG.debug_stmt(l_log_head,
573 l_progress,
574 'End of RECEIVE CLOSE mode call');
575 PO_DEBUG.debug_end(l_log_head);
576 END IF;
577 end if;
578
579 return(l_closed_date);
580
581 EXCEPTION
582 WHEN OTHERS THEN
583 IF g_debug_unexp THEN
584 PO_DEBUG.debug_exc(l_log_head, l_progress);
585 END IF;
586 raise;
587
588
589 END get_closure_dates;
590
591
592
593
594 END PO_ACTIONS;
595