1 PACKAGE BODY PO_DOCUMENT_FUNDS_PVT AS
2 -- $Header: POXVENCB.pls 120.35.12020000.5 2013/03/11 03:34:09 jozhong ship $
3
4
5 -- Private package constants
6
7 G_PKG_NAME CONSTANT varchar2(30) := 'PO_DOCUMENT_FUNDS_PVT';
8 g_log_head CONSTANT VARCHAR2(50) := 'po.plsql.'|| G_PKG_NAME || '.';
9 -- Logging global constants
10 D_PACKAGE_BASE CONSTANT VARCHAR2(100) := PO_LOG.get_package_base(G_PKG_NAME);
11
12
13 -- Debugging
14
15 g_debug_stmt CONSTANT BOOLEAN := PO_DEBUG.is_debug_stmt_on;
16 g_debug_unexp CONSTANT BOOLEAN := PO_DEBUG.is_debug_unexp_on;
17
18
19 -- Exception handling
20
21 g_SUBMISSION_CHECK_EXC_CODE CONSTANT
22 NUMBER
23 := 1
24 ;
25 g_GET_ALL_DISTS_EXC_CODE CONSTANT
26 NUMBER
27 := 2
28 ;
29 g_NO_ROWS_EXC_CODE CONSTANT
30 NUMBER
31 := 3
32 ;
33 g_EXECUTE_GL_CALL_EXC_CODE CONSTANT
34 NUMBER
35 := 4
36 ;
37
38 -- Bug 3480949: Added order_type constants
39 -- order type lookup codes
40 g_order_type_FIXED_PRICE CONSTANT
41 PO_LINE_TYPES_B.order_type_lookup_code%TYPE
42 := 'FIXED PRICE'
43 ;
44
45 g_order_type_RATE CONSTANT
46 PO_LINE_TYPES_B.order_type_lookup_code%TYPE
47 := 'RATE'
48 ;
49
50 g_GL_FUNDS_API_EXC constant number :=5; ---Bug 12824154
51
52
53 --------------------------------------------------------------------------------
54 -- Forward procedure declarations
55 --------------------------------------------------------------------------------
56
57 PROCEDURE do_action(
58 x_return_status OUT NOCOPY VARCHAR2
59 , p_action IN VARCHAR2
60 , p_check_only_flag IN VARCHAR2
61 , p_cbc_flag IN VARCHAR2
62 , p_doc_type IN VARCHAR2
63 , p_doc_subtype IN VARCHAR2
64 , p_doc_level IN VARCHAR2
65 , p_doc_level_id IN NUMBER
66 , p_use_enc_gt_flag IN VARCHAR2
67 , p_employee_id IN NUMBER
68 , p_override_funds IN VARCHAR2
69 , p_prevent_partial_flag IN VARCHAR2
70 , p_use_gl_date IN VARCHAR2
71 , p_override_date IN DATE
72 , p_validate_document IN VARCHAR2
73 , p_use_force_mode IN VARCHAR2
74 -- The following params are only revelant to
75 -- the invoice cancel API
76 , p_invoice_id IN NUMBER DEFAULT NULL
77 , p_ap_encumbered_amount IN NUMBER DEFAULT NULL
78 , p_ap_cancelled_quantity IN NUMBER DEFAULT NULL
79 , p_budget_acct_id IN NUMBER DEFAULT NULL
80 , x_packet_id OUT NOCOPY NUMBER
81 -- End of invoice-cancel specific parameters
82 , x_online_report_id OUT NOCOPY NUMBER
83 , x_po_return_code OUT NOCOPY VARCHAR2);
84
85
86 PROCEDURE handle_exception(
87 p_api_name IN VARCHAR2
88 , p_progress IN VARCHAR2
89 , x_return_status OUT NOCOPY VARCHAR2
90 , x_po_return_code OUT NOCOPY VARCHAR2
91 );
92
93 --<SLA R12 Start>
94 --<SLA R12 End>
95
96 --------------------------------------------------------------------------------
97 -- Procedure definitions
98 --------------------------------------------------------------------------------
99
100
101 ------------------------------------------------------------------------------
102 --Start of Comments
103 --Name: check_reserve
104 --Pre-reqs:
105 -- None.
106 --Modifies:
107 -- Creates funds check entries in the gl_bc_packets table
108 -- Adds distribution-specific transaction information into the
109 -- po_online_report_text table
110 --Locks:
111 -- None.
112 --Function:
113 -- This procedure performs a funds check on a document or doc level of a
114 -- document.
115 --Parameters:
116 --IN:
117 --p_doc_type
118 -- Differentiates between the doc being a REQ, PA, PO, or RELEASE,
119 -- which is used to identify the tables to look at (PO vs. Req)
120 -- and the join conditions
121 --p_doc_subtype
122 -- Differentiates between the subtypes of documents
123 -- REQ: NULL
124 -- PO: STANDARD, PLANNED
125 -- PA: CONTRACT, BLANKET
126 -- RELEASE: SCHEDULED, BLANKET
127 --p_doc_level
128 -- Specifies the level of the document that is being checked:
129 -- HEADER, LINE, SHIPMENT, DISTRIBUTION
130 --p_doc_level_id
131 -- The id corresponding to the doc level type:
132 -- header_id/release_id, line_id, line_location_id, distribution_id
133 --p_use_enc_gt_flag
134 -- Set to g_parameter_NO if calling with p_doc_level_id
135 -- Else if using the inteface table g_parameter_YES
136 --p_override_funds
137 -- Indicates whether funds override capability can be used if needed, to make a
138 -- transaction succeed.
139 --p_draft_id /* <<CLM Partial Funding Code Changes>> */
140 -- The Draft id of the Modification
141 -- Will be null for all cases except CheckFunds on Modification
142 --OUT:
143 --x_return_status
144 -- FND_API.G_RET_STS_SUCCESS if validation succeeds
145 -- FND_API.G_RET_STS_ERROR if validation fails
146 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
147 --x_po_return_code
148 -- Indicates whether PO is classifying this transaction as an
149 -- Error/Warning/Partial Success/Success
150 --x_online_report_id
151 -- Unique id into po_online_report_text rows that store distribution specific
152 -- reporting information for a specific encumbrance transaction
153 --Testing:
154 --
155 --End of Comments
156 -------------------------------------------------------------------------------
157 PROCEDURE check_reserve(
158 x_return_status OUT NOCOPY VARCHAR2
159 , p_doc_type IN VARCHAR2
160 , p_doc_subtype IN VARCHAR2
161 , p_doc_level IN VARCHAR2
162 , p_doc_level_id IN NUMBER
163 , p_use_enc_gt_flag IN VARCHAR2
164 , p_override_funds IN VARCHAR2
165 , p_draft_id IN NUMBER DEFAULT NULL /* <<CLM Partial Funding Code Changes>> */
166 , x_po_return_code OUT NOCOPY VARCHAR2
167 , x_online_report_id OUT NOCOPY NUMBER
168 )
169 IS
170
171 l_api_name CONSTANT varchar2(30) := 'CHECK_RESERVE';
172 l_log_head CONSTANT VARCHAR2(100) := g_log_head || l_api_name;
173 l_progress VARCHAR2(3) := '000';
174 l_packet_id NUMBER;
175 l_use_enc_gt_flag VARCHAR2(1) := p_use_enc_gt_flag; /* <<CLM Partial Funding Code Changes>> */
176
177 BEGIN
178
179 IF g_debug_stmt THEN
180 PO_DEBUG.debug_begin(l_log_head);
181 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_type',p_doc_type);
182 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_subtype',p_doc_subtype);
183 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_level',p_doc_level);
184 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_level_id',p_doc_level_id);
185 PO_DEBUG.debug_var(l_log_head,l_progress,'p_use_enc_gt_flag',p_use_enc_gt_flag);
186 PO_DEBUG.debug_var(l_log_head,l_progress,'p_override_funds',p_override_funds);
187 PO_DEBUG.debug_var(l_log_head,l_progress,'p_draft_id',p_draft_id);
188 END IF;
189
190 l_progress := '010';
191
192 -- Standard Start of API savepoint
193 SAVEPOINT CHECK_RESERVE_SP;
194
195 l_progress := '020';
196
197 x_online_report_id := NULL;
198 x_po_return_code := NULL;
199
200 /* <<CLM Partial Funding Code Changes>> */
201 IF Nvl(p_draft_id,-1) <> -1 Then
202
203 l_progress := '030';
204 IF g_debug_stmt THEN
205 PO_DEBUG.debug_stmt(l_log_head,l_progress,
206 'Calling Populate_Enc_Gt_With_Draft for Modification');
207 END IF;
208
209 -- Encumbrance is Not Supported for BPAs in CLM
210 If (p_doc_type = g_doc_type_PO AND p_doc_subtype = g_doc_subtype_STANDARD) THEN
211 Populate_Enc_Gt_With_Draft(x_return_status => x_return_status,
212 p_draft_id => p_draft_id);
213 End If;
214
215 l_progress := '040';
216 IF g_debug_stmt THEN
217 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
218 End If;
219
220 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
221 l_use_enc_gt_flag := g_parameter_YES;
222 ELSE
223 IF ( x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
224 OR x_return_status IS NULL ) THEN
225 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
226 END IF;
227 END IF;
228
229 END IF;
230 /* <<CLM Partial Funding Code Changes>> */
231
232 do_action(x_return_status => x_return_status,
233 p_action => g_action_RESERVE,
234 p_check_only_flag => g_parameter_YES,
235 p_cbc_flag => g_parameter_NO,
236 p_doc_type => p_doc_type,
237 p_doc_subtype => p_doc_subtype,
238 p_doc_level => p_doc_level,
239 p_doc_level_id => p_doc_level_id,
240 p_use_enc_gt_flag => l_use_enc_gt_flag, /* <<CLM Partial Funding Code Changes>> */
241 p_employee_id => NULL,
242 p_override_funds => p_override_funds,
243 p_prevent_partial_flag => NULL,
244 p_use_gl_date => g_parameter_YES,
245 p_override_date => NULL,
246 p_validate_document => g_parameter_NO, -- no validation for Check Funds
247 p_use_force_mode => g_parameter_NO,
248 x_packet_id => l_packet_id,
249 x_po_return_code => x_po_return_code,
250 x_online_report_id => x_online_report_id);
251
252 l_progress := '100';
253
254 IF ( x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
255 OR x_return_status IS NULL
256 ) THEN
257 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
258 END IF;
259
260 l_progress := '900';
261
262 IF g_debug_stmt THEN
263 PO_DEBUG.debug_var(l_log_head,l_progress,'l_packet_id',l_packet_id);
264 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
265 PO_DEBUG.debug_var(l_log_head,l_progress,'x_po_return_code',x_po_return_code);
266 PO_DEBUG.debug_var(l_log_head,l_progress,'x_online_report_id',x_online_report_id);
267 PO_DEBUG.debug_end(l_log_head);
268 END IF;
269
270 EXCEPTION
271 WHEN OTHERS THEN
272 -- bug 3454804 - robust exception handling
273 BEGIN
274 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS Start');
275
276 -- Do all of the common exception handling.
277 handle_exception(l_api_name,l_progress,x_return_status,x_po_return_code);
278
279 PO_DEBUG.debug_unexp(l_log_head,l_progress,'handle_exception done');
280
281 IF g_debug_stmt THEN
282 PO_DEBUG.debug_var(l_log_head,l_progress,'l_packet_id',l_packet_id);
283 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
284 PO_DEBUG.debug_var(l_log_head,l_progress,'x_po_return_code',x_po_return_code);
285 PO_DEBUG.debug_var(l_log_head,l_progress,'x_online_report_id',x_online_report_id);
286 END IF;
287
288 -- Rollback as per API standards.
289 ROLLBACK TO CHECK_RESERVE_SP;
290
291 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS End');
292
293 EXCEPTION
294 WHEN OTHERS THEN
295 NULL;
296 END;
297
298 END check_reserve;
299
300 ------------------------------------------------------------------------------
301 --Start of Comments
302 --Name: check_adjust
303 --Pre-reqs:
304 -- None.
305 --Modifies:
306 -- Creates funds check entries in the gl_bc_packets table
307 -- Adds distribution-specific transaction information into the
308 -- po_online_report_text table
309 --Locks:
310 -- None.
311 --Function:
312 -- This procedure performs a funds check on a document or doc level of a
313 -- document.
314 --Parameters:
315 --IN:
316 --p_doc_type
317 -- Differentiates between the doc being a REQ, PA, PO, or RELEASE,
318 -- which is used to identify the tables to look at (PO vs. Req)
319 -- and the join conditions
320 --p_doc_subtype
321 -- Differentiates between the subtypes of documents
322 -- REQ: NULL
323 -- PO: STANDARD, PLANNED
324 -- PA: CONTRACT, BLANKET
325 -- RELEASE: SCHEDULED, BLANKET
326 --p_override_funds
327 -- Indicates whether funds override capability can be used if needed, to make a
328 -- transaction succeed.
329 --p_use_gl_date
330 -- Flag that specifies whether to always prefer using the existing distribution
331 -- GL date instead of the override date, when possible
332 --p_override_date
333 -- Caller-specified date to use for distribution encumbrance date in GL entries
334 --OUT:
335 --x_return_status
336 -- FND_API.G_RET_STS_SUCCESS if validation succeeds
337 -- FND_API.G_RET_STS_ERROR if validation fails
338 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
339 --x_po_return_code
340 -- Indicates whether PO is classifying this transaction as an
341 -- Error/Warning/Partial Success/Success
342 --x_online_report_id
343 -- Unique id into po_online_report_text rows that store distribution specific
344 -- reporting information for a specific encumbrance transaction
345 --Testing:
346 --
347 --End of Comments
348 -------------------------------------------------------------------------------
349 PROCEDURE check_adjust(
350 x_return_status OUT NOCOPY VARCHAR2
351 , p_doc_type IN VARCHAR2
352 , p_doc_subtype IN VARCHAR2
353 , p_override_funds IN VARCHAR2
354 , p_use_gl_date IN VARCHAR2
355 , p_override_date IN DATE
356 , x_po_return_code OUT NOCOPY VARCHAR2
357 , x_online_report_id OUT NOCOPY NUMBER
358 )
359 IS
360
361 l_api_name CONSTANT varchar2(30) := 'CHECK_ADJUST';
362 l_log_head CONSTANT VARCHAR2(100) := g_log_head || l_api_name;
363 l_progress VARCHAR2(3) := '000';
364 l_packet_id NUMBER;
365
366 BEGIN
367
368 IF g_debug_stmt THEN
369 PO_DEBUG.debug_begin(l_log_head);
370 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_type',p_doc_type);
371 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_subtype',p_doc_subtype);
372 PO_DEBUG.debug_var(l_log_head,l_progress,'p_override_funds',p_override_funds);
373 PO_DEBUG.debug_var(l_log_head,l_progress,'p_use_gl_date',p_use_gl_date);
374 PO_DEBUG.debug_var(l_log_head,l_progress,'p_override_date',p_override_date);
375 END IF;
376
377 l_progress := '010';
378
379 -- Standard Start of API savepoint
380 SAVEPOINT CHECK_ADJUST_SP;
381
382 l_progress := '020';
383
384 x_online_report_id := NULL;
385 x_po_return_code := NULL;
386
387 do_action(x_return_status => x_return_status,
388 p_action => g_action_ADJUST,
389 p_check_only_flag => g_parameter_YES,
390 p_cbc_flag => NULL,
391 p_doc_type => p_doc_type,
392 p_doc_subtype => p_doc_subtype,
393 p_doc_level => NULL,
394 p_doc_level_id => NULL,
395 p_use_enc_gt_flag => g_parameter_YES,
396 p_employee_id => NULL,
397 p_override_funds => p_override_funds,
398 p_prevent_partial_flag => NULL,
399 p_use_gl_date => p_use_gl_date,
400 p_override_date => p_override_date,
401 p_validate_document => g_parameter_NO, -- no validation for Check Funds
402 p_use_force_mode => g_parameter_NO,
403 x_packet_id => l_packet_id,
404 x_po_return_code => x_po_return_code,
405 x_online_report_id => x_online_report_id);
406
407 l_progress := '100';
408
409 IF ( x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
410 OR x_return_status IS NULL
411 ) THEN
412 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
413 END IF;
414
415 l_progress := '900';
416
417 IF g_debug_stmt THEN
418 PO_DEBUG.debug_var(l_log_head,l_progress,'l_packet_id',l_packet_id);
419 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
420 PO_DEBUG.debug_var(l_log_head,l_progress,'x_po_return_code',x_po_return_code);
421 PO_DEBUG.debug_var(l_log_head,l_progress,'x_online_report_id',x_online_report_id);
422 PO_DEBUG.debug_end(l_log_head);
423 END IF;
424
425 EXCEPTION
426 WHEN OTHERS THEN
427 -- bug 3454804 - robust exception handling
428 BEGIN
429 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS Start');
430
431 -- Do all of the common exception handling.
432 handle_exception(l_api_name,l_progress,x_return_status,x_po_return_code);
433
434 PO_DEBUG.debug_unexp(l_log_head,l_progress,'handle_exception done');
435
436 IF g_debug_stmt THEN
437 PO_DEBUG.debug_var(l_log_head,l_progress,'l_packet_id',l_packet_id);
438 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
439 PO_DEBUG.debug_var(l_log_head,l_progress,'x_po_return_code',x_po_return_code);
440 PO_DEBUG.debug_var(l_log_head,l_progress,'x_online_report_id',x_online_report_id);
441 END IF;
442
443 -- Rollback as per API standards.
444 ROLLBACK TO CHECK_ADJUST_SP;
445
446 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS End');
447
448 EXCEPTION
449 WHEN OTHERS THEN
450 NULL;
451 END;
452
453 END check_adjust;
454
455
456
457 ------------------------------------------------------------------------------
458 --Start of Comments
459 --Name: do_reserve
460 --Pre-reqs:
461 -- None.
462 --Modifies:
463 -- Creates encumbrance entries in the gl_bc_packets table
464 -- Adds distribution-specific transaction information into the
465 -- po_online_report_text table
466 --Locks:
467 -- None.
468 --Function:
469 -- This procedure performs funds reservation on all eligible
470 -- distributions of a document
471 --Parameters:
472 --IN:
473 --p_doc_type
474 -- Differentiates between the doc being a REQ, PA, PO, or RELEASE,
475 -- which is used to identify the tables to look at (PO vs. Req)
476 -- and the join conditions
477 --p_doc_subtype
478 -- Differentiates between the subtypes of documents
479 -- REQ: NULL
480 -- PO: STANDARD, PLANNED
481 -- PA: CONTRACT, BLANKET
482 -- RELEASE: SCHEDULED, BLANKET
483 --p_doc_level
484 -- Specifies the level of the document that is being checked:
485 -- For Reserve, this should always be g_HEADER
486 --p_doc_level_id
487 -- The id corresponding to the doc level type:
488 -- For Reserve, this should always be header_id (or release_id for Releases)
489 --p_use_enc_gt_flag
490 -- Set to g_parameter_NO if calling with p_doc_level_id
491 -- Else if using the inteface table g_parameter_YES
492 --p_prevent_partial_flag
493 -- Relevant only to g_action_RESERVE
494 -- 'Y'= the caller specifically does not want partials allowed
495 -- Note: setting this to 'N' does not guarantee partial will be allowed (i.e.
496 -- partial is never allowed when there are backing docs)
497 --p_validate_document
498 -- Indicates whether to perform general document state/submission checks
499 -- If 'Y', then encumbrance code will make calls to check doc correctness
500 -- If 'N', then the caller has already done these validations.
501 --p_override_funds
502 -- Indicates whether funds override capability can be used if needed, to make a
503 -- transaction succeed.
504 --p_employee_id
505 -- Employee Id of the user taking the action
506 --OUT:
507 --x_return_status
508 -- FND_API.G_RET_STS_SUCCESS if validation succeeds
509 -- FND_API.G_RET_STS_ERROR if validation fails
510 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
511 --x_po_return_code
512 -- Indicates whether PO is classifying this transaction as an
513 -- Error/Warning/Partial Success/Success
514 --x_online_report_id
515 -- Unique id into po_online_report_text rows that store distribution specific
516 -- reporting information for a specific encumbrance transaction
517 --Testing:
518 --
519 --End of Comments
520 -------------------------------------------------------------------------------
521 PROCEDURE do_reserve(
522 x_return_status OUT NOCOPY VARCHAR2
523 , p_doc_type IN VARCHAR2
524 , p_doc_subtype IN VARCHAR2
525 , p_doc_level IN VARCHAR2
526 , p_doc_level_id IN NUMBER
527 , p_use_enc_gt_flag IN VARCHAR2
528 , p_prevent_partial_flag IN VARCHAR2
529 , p_validate_document IN VARCHAR2
530 , p_override_funds IN VARCHAR2
531 , p_employee_id IN NUMBER
532 , x_po_return_code OUT NOCOPY VARCHAR2
533 , x_online_report_id OUT NOCOPY NUMBER) IS
534
535 l_api_name CONSTANT varchar2(30) := 'DO_RESERVE';
536 l_log_head CONSTANT VARCHAR2(100) := g_log_head || l_api_name;
537 l_progress VARCHAR2(3) := '000';
538 l_packet_id NUMBER;
539
540 BEGIN
541
542 IF g_debug_stmt THEN
543 PO_DEBUG.debug_begin(l_log_head);
544 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_type',p_doc_type);
545 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_subtype',p_doc_subtype);
546 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_level',p_doc_level);
547 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_level_id',p_doc_level_id);
548 PO_DEBUG.debug_var(l_log_head,l_progress,'p_use_enc_gt_flag',p_use_enc_gt_flag);
549 PO_DEBUG.debug_var(l_log_head,l_progress,'p_prevent_partial_flag',p_prevent_partial_flag);
550 PO_DEBUG.debug_var(l_log_head,l_progress,'p_validate_document',p_validate_document);
551 PO_DEBUG.debug_var(l_log_head,l_progress,'p_override_funds',p_override_funds);
552 PO_DEBUG.debug_var(l_log_head,l_progress,'p_employee_id',p_employee_id);
553 END IF;
554
555 l_progress := '010';
556
557 -- Standard Start of API savepoint
558 SAVEPOINT DO_RESERVE_SP;
559
560 l_progress := '020';
561
562 x_po_return_code := NULL;
563 x_online_report_id := NULL;
564
565 do_action(x_return_status => x_return_status,
566 p_action => g_action_RESERVE,
567 p_check_only_flag => g_parameter_NO,
568 p_cbc_flag => g_parameter_NO,
569 p_doc_type => p_doc_type,
570 p_doc_subtype => p_doc_subtype,
571 p_doc_level => p_doc_level,
572 p_doc_level_id => p_doc_level_id,
573 p_use_enc_gt_flag => p_use_enc_gt_flag,
574 p_employee_id => p_employee_id,
575 p_override_funds => p_override_funds,
576 p_prevent_partial_flag => p_prevent_partial_flag,
577 p_use_gl_date => g_parameter_YES,
578 p_override_date => NULL,
579 p_validate_document => p_validate_document,
580 p_use_force_mode => g_parameter_NO,
581 x_packet_id => l_packet_id,
582 x_po_return_code => x_po_return_code,
583 x_online_report_id => x_online_report_id);
584
585 l_progress := '100';
586
587 IF ( x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
588 OR x_return_status IS NULL
589 ) THEN
590 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
591 END IF;
592
593 l_progress := '900';
594
595 IF g_debug_stmt THEN
596 PO_DEBUG.debug_var(l_log_head,l_progress,'l_packet_id',l_packet_id);
597 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
598 PO_DEBUG.debug_var(l_log_head,l_progress,'x_po_return_code',x_po_return_code);
599 PO_DEBUG.debug_var(l_log_head,l_progress,'x_online_report_id',x_online_report_id);
600 PO_DEBUG.debug_end(l_log_head);
601 END IF;
602
603 EXCEPTION
604 WHEN OTHERS THEN
605 -- bug 3454804 - robust exception handling
606 BEGIN
607 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS Start');
608
609 -- Do all of the common exception handling.
610 handle_exception(l_api_name,l_progress,x_return_status,x_po_return_code);
611
612 PO_DEBUG.debug_unexp(l_log_head,l_progress,'handle_exception done');
613
614 IF g_debug_stmt THEN
615 PO_DEBUG.debug_var(l_log_head,l_progress,'l_packet_id',l_packet_id);
616 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
617 PO_DEBUG.debug_var(l_log_head,l_progress,'x_po_return_code',x_po_return_code);
618 PO_DEBUG.debug_var(l_log_head,l_progress,'x_online_report_id',x_online_report_id);
619 END IF;
620
621 -- Rollback as per API standards.
622 ROLLBACK TO DO_RESERVE_SP;
623
624 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS End');
625
626 EXCEPTION
627 WHEN OTHERS THEN
628 NULL;
629 END;
630
631 END do_reserve;
632
633
634 ------------------------------------------------------------------------------
635 --Start of Comments
636 --Name: do_unreserve
637 --Pre-reqs:
638 -- None.
639 --Modifies:
640 -- Creates encumbrance entries in the gl_bc_packets table
641 -- Adds distribution-specific transaction information into the
642 -- po_online_report_text table
643 --Locks:
644 -- None.
645 --Function:
646 -- This procedure unreserves the encumbrance on all eligible distributions of
647 -- the requested document or document doc level
648 --Parameters:
649 --IN:
650 --p_doc_type
651 -- Differentiates between the doc being a REQ, PA, PO, or RELEASE,
652 -- which is used to identify the tables to look at (PO vs. Req)
653 -- and the join conditions
654 --p_doc_subtype
655 -- Differentiates between the subtypes of documents
656 -- REQ: NULL
657 -- PO: STANDARD, PLANNED
658 -- PA: CONTRACT, BLANKET
659 -- RELEASE: SCHEDULED, BLANKET
660 --p_doc_level
661 -- Specifies the level of the document that is being checked:
662 -- HEADER, LINE, SHIPMENT, DISTRIBUTION
663 --p_doc_level_id
664 -- The id corresponding to the doc level type:
665 -- header_id/release_id, line_id, line_location_id, distribution_id
666 --p_use_enc_gt_flag
667 -- Set to g_parameter_NO if calling with p_doc_level_id
668 -- Else if using the inteface table g_parameter_YES
669 --p_validate_document
670 -- Indicates whether to perform general document state/submission checks
671 -- If 'Y', then encumbrance code will make calls to check doc correctness
672 -- If 'N', then the caller has already done these validations.
673 --p_override_funds
674 -- Indicates whether funds override capability can be used if needed, to make a
675 -- transaction succeed.
676 --p_use_gl_date
677 -- Flag that specifies whether to always prefer using the existing distribution
678 -- GL date instead of the override date, when possible
679 --p_override_date
680 -- Caller-specified date to use for distribution encumbrance date in GL entries
681 --p_employee_id
682 -- Employee Id of the user taking the action
683 --OUT:
684 --x_return_status
685 -- FND_API.G_RET_STS_SUCCESS if validation succeeds
686 -- FND_API.G_RET_STS_ERROR if validation fails
687 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
688 --x_po_return_code
689 -- Indicates whether PO is classifying this transaction as an
690 -- Error/Warning/Partial Success/Success
691 --x_online_report_id
692 -- Unique id into po_online_report_text rows that store distribution specific
693 -- reporting information for a specific encumbrance transaction
694 --Testing:
695 --
696 --End of Comments
697 -------------------------------------------------------------------------------
698 PROCEDURE do_unreserve(
699 x_return_status OUT NOCOPY VARCHAR2
700 , p_doc_type IN VARCHAR2
701 , p_doc_subtype IN VARCHAR2
702 , p_doc_level IN VARCHAR2
703 , p_doc_level_id IN NUMBER
704 , p_use_enc_gt_flag IN VARCHAR2
705 , p_validate_document IN VARCHAR2
706 , p_override_funds IN VARCHAR2
707 , p_use_gl_date IN VARCHAR2
708 , p_override_date IN DATE
709 , p_employee_id IN NUMBER
710 , x_po_return_code OUT NOCOPY VARCHAR2
711 , x_online_report_id OUT NOCOPY NUMBER
712 )
713 IS
714
715 l_api_name CONSTANT varchar2(30) := 'DO_UNRESERVE';
716 l_log_head CONSTANT VARCHAR2(100) := g_log_head || l_api_name;
717 l_progress VARCHAR2(3) := '000';
718 l_packet_id NUMBER;
719
720 BEGIN
721
722 IF g_debug_stmt THEN
723 PO_DEBUG.debug_begin(l_log_head);
724 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_type',p_doc_type);
725 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_subtype',p_doc_subtype);
726 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_level',p_doc_level);
727 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_level_id',p_doc_level_id);
728 PO_DEBUG.debug_var(l_log_head,l_progress,'p_use_enc_gt_flag',p_use_enc_gt_flag);
729 PO_DEBUG.debug_var(l_log_head,l_progress,'p_validate_document',p_validate_document);
730 PO_DEBUG.debug_var(l_log_head,l_progress,'p_override_funds',p_override_funds);
731 PO_DEBUG.debug_var(l_log_head,l_progress,'p_use_gl_date',p_use_gl_date);
732 PO_DEBUG.debug_var(l_log_head,l_progress,'p_override_date',p_override_date);
733 PO_DEBUG.debug_var(l_log_head,l_progress,'p_employee_id',p_employee_id);
734 END IF;
735
736 l_progress := '010';
737
738 -- Standard Start of API savepoint
739 SAVEPOINT DO_UNRESERVE_SP;
740
741 l_progress := '020';
742
743 x_po_return_code := NULL;
744 x_online_report_id := NULL;
745
746 do_action(x_return_status => x_return_status,
747 p_action => g_action_UNRESERVE,
748 p_check_only_flag => g_parameter_NO,
749 p_cbc_flag => g_parameter_NO,
750 p_doc_type => p_doc_type,
751 p_doc_subtype => p_doc_subtype,
752 p_doc_level => p_doc_level,
753 p_doc_level_id => p_doc_level_id,
754 p_use_enc_gt_flag => p_use_enc_gt_flag,
755 p_employee_id => p_employee_id,
756 p_override_funds => p_override_funds,
757 p_prevent_partial_flag => NULL,
758 p_use_gl_date => p_use_gl_date,
759 p_override_date => p_override_date,
760 p_validate_document => p_validate_document,
761 p_use_force_mode => g_parameter_NO,
762 x_packet_id => l_packet_id,
763 x_po_return_code => x_po_return_code,
764 x_online_report_id => x_online_report_id);
765
766 l_progress := '100';
767
768 IF ( x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
769 OR x_return_status IS NULL
770 ) THEN
771 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
772 END IF;
773
774 l_progress := '900';
775
776 IF g_debug_stmt THEN
777 PO_DEBUG.debug_var(l_log_head,l_progress,'l_packet_id',l_packet_id);
778 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
779 PO_DEBUG.debug_var(l_log_head,l_progress,'x_po_return_code',x_po_return_code);
780 PO_DEBUG.debug_var(l_log_head,l_progress,'x_online_report_id',x_online_report_id);
781 PO_DEBUG.debug_end(l_log_head);
782 END IF;
783
784 EXCEPTION
785 WHEN OTHERS THEN
786 -- bug 3454804 - robust exception handling
787 BEGIN
788 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS Start');
789
790 -- Do all of the common exception handling.
791 handle_exception(l_api_name,l_progress,x_return_status,x_po_return_code);
792
793 PO_DEBUG.debug_unexp(l_log_head,l_progress,'handle_exception done');
794
795 IF g_debug_stmt THEN
796 PO_DEBUG.debug_var(l_log_head,l_progress,'l_packet_id',l_packet_id);
797 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
798 PO_DEBUG.debug_var(l_log_head,l_progress,'x_po_return_code',x_po_return_code);
799 PO_DEBUG.debug_var(l_log_head,l_progress,'x_online_report_id',x_online_report_id);
800 END IF;
801
802 -- Rollback as per API standards.
803 ROLLBACK TO DO_UNRESERVE_SP;
804
805 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS End');
806
807 EXCEPTION
808 WHEN OTHERS THEN
809 NULL;
810 END;
811
812 END do_unreserve;
813
814 ------------------------------------------------------------------------------
815 --Start of Comments
816 --Name: do_return
817 --Pre-reqs:
818 -- None.
819 --Modifies:
820 -- Creates encumbrance entries in the gl_bc_packets table
821 -- Adds distribution-specific transaction information into the
822 -- po_online_report_text table
823 --Locks:
824 -- None.
825 --Function:
826 -- This procedure unreserves encumbrance on a Requisition that has been
827 -- returned
828 --Parameters:
829 --IN:
830 --p_doc_type
831 -- Differentiates between the doc being a REQ, PA, PO, or RELEASE,
832 -- which is used to identify the tables to look at (PO vs. Req)
833 -- and the join conditions
834 --p_doc_subtype
835 -- Differentiates between the subtypes of documents
836 -- REQ: NULL
837 -- PO: STANDARD, PLANNED
838 -- PA: CONTRACT, BLANKET
839 -- RELEASE: SCHEDULED, BLANKET
840 --p_doc_level
841 -- Specifies the level of the document that is being checked:
842 -- HEADER, LINE, SHIPMENT, DISTRIBUTION
843 --p_doc_level_id
844 -- The id corresponding to the doc level type:
845 -- header_id/release_id, line_id, line_location_id, distribution_id
846 --p_use_enc_gt_flag
847 -- Set to g_parameter_NO if calling with p_doc_level_id
848 -- Else if using the inteface table g_parameter_YES
849 --p_use_gl_date
850 -- Flag that specifies whether to always prefer using the existing distribution
851 -- GL date instead of the override date, when possible
852 --p_override_date
853 -- Caller-specified date to use for distribution encumbrance date in GL entries
854 --OUT:
855 --x_return_status
856 -- FND_API.G_RET_STS_SUCCESS if validation succeeds
857 -- FND_API.G_RET_STS_ERROR if validation fails
858 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
859 --x_po_return_code
860 -- Indicates whether PO is classifying this transaction as an
861 -- Error/Warning/Partial Success/Success
862 --x_online_report_id
863 -- Unique id into po_online_report_text rows that store distribution specific
864 -- reporting information for a specific encumbrance transaction
865 --Testing:
866 --
867 --End of Comments
868 -------------------------------------------------------------------------------
869 PROCEDURE do_return(
870 x_return_status OUT NOCOPY VARCHAR2
871 , p_doc_type IN VARCHAR2
872 , p_doc_subtype IN VARCHAR2
873 , p_doc_level IN VARCHAR2
874 , p_doc_level_id IN NUMBER
875 , p_use_enc_gt_flag IN VARCHAR2
876 , p_use_gl_date IN VARCHAR2
877 , p_override_date IN DATE
878 , x_po_return_code OUT NOCOPY VARCHAR2
879 , x_online_report_id OUT NOCOPY NUMBER
880 )
881 IS
882
883 l_api_name CONSTANT varchar2(30) := 'DO_RETURN';
884 l_log_head CONSTANT VARCHAR2(100) := g_log_head || l_api_name;
885 l_progress VARCHAR2(3) := '000';
886 l_packet_id NUMBER;
887
888 BEGIN
889
890 IF g_debug_stmt THEN
891 PO_DEBUG.debug_begin(l_log_head);
892 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_type',p_doc_type);
893 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_subtype',p_doc_subtype);
894 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_level',p_doc_level);
895 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_level_id',p_doc_level_id);
896 PO_DEBUG.debug_var(l_log_head,l_progress,'p_use_enc_gt_flag',p_use_enc_gt_flag);
897 PO_DEBUG.debug_var(l_log_head,l_progress,'p_use_gl_date',p_use_gl_date);
898 PO_DEBUG.debug_var(l_log_head,l_progress,'p_override_date',p_override_date);
899 END IF;
900
901 l_progress := '010';
902
903 -- Standard Start of API savepoint
904 SAVEPOINT DO_RETURN_SP;
905
906 l_progress := '020';
907
908 x_po_return_code := NULL;
909 x_online_report_id := NULL;
910
911 do_action(x_return_status => x_return_status,
912 p_action => g_action_RETURN,
913 p_check_only_flag => g_parameter_NO,
914 p_cbc_flag => NULL,
915 p_doc_type => p_doc_type,
916 p_doc_subtype => p_doc_subtype,
917 p_doc_level => p_doc_level,
918 p_doc_level_id => p_doc_level_id,
919 p_use_enc_gt_flag => p_use_enc_gt_flag,
920 p_employee_id => NULL,
921 p_override_funds => NULL,
922 p_prevent_partial_flag => NULL,
923 p_use_gl_date => p_use_gl_date,
924 p_override_date => p_override_date,
925 p_validate_document => g_parameter_NO,
926 p_use_force_mode => g_parameter_NO,
927 x_packet_id => l_packet_id,
928 x_po_return_code => x_po_return_code,
929 x_online_report_id => x_online_report_id);
930
931 l_progress := '100';
932
933 IF ( x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
934 OR x_return_status IS NULL
935 ) THEN
936 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
937 END IF;
938
939 l_progress := '900';
940
941 IF g_debug_stmt THEN
942 PO_DEBUG.debug_var(l_log_head,l_progress,'l_packet_id',l_packet_id);
943 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
944 PO_DEBUG.debug_var(l_log_head,l_progress,'x_po_return_code',x_po_return_code);
945 PO_DEBUG.debug_var(l_log_head,l_progress,'x_online_report_id',x_online_report_id);
946 PO_DEBUG.debug_end(l_log_head);
947 END IF;
948
949 EXCEPTION
950 WHEN OTHERS THEN
951 -- bug 3454804 - robust exception handling
952 BEGIN
953 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS Start');
954
955 -- Do all of the common exception handling.
956 handle_exception(l_api_name,l_progress,x_return_status,x_po_return_code);
957
958 PO_DEBUG.debug_unexp(l_log_head,l_progress,'handle_exception done');
959
960 IF g_debug_stmt THEN
961 PO_DEBUG.debug_var(l_log_head,l_progress,'l_packet_id',l_packet_id);
962 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
963 PO_DEBUG.debug_var(l_log_head,l_progress,'x_po_return_code',x_po_return_code);
964 PO_DEBUG.debug_var(l_log_head,l_progress,'x_online_report_id',x_online_report_id);
965 END IF;
966
967 -- Rollback as per API standards.
968 ROLLBACK TO DO_RETURN_SP;
969
970 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS End');
971
972 EXCEPTION
973 WHEN OTHERS THEN
974 NULL;
975 END;
976
977 END do_return;
978
979
980 ------------------------------------------------------------------------------
981 --Start of Comments
982 --Name: do_reject
983 --Pre-reqs:
984 -- None.
985 --Modifies:
986 -- Creates encumbrance entries in the gl_bc_packets table
987 -- Adds distribution-specific transaction information into the
988 -- po_online_report_text table
989 --Locks:
990 -- None.
991 --Function:
992 -- This procedure unreserves encumbrance from unapproved shipments on
993 -- a document that has been rejected
994 --Parameters:
995 --IN:
996 --p_doc_type
997 -- Differentiates between the doc being a REQ, PA, PO, or RELEASE,
998 -- which is used to identify the tables to look at (PO vs. Req)
999 -- and the join conditions
1000 --p_doc_subtype
1001 -- Differentiates between the subtypes of documents
1002 -- REQ: NULL
1003 -- PO: STANDARD, PLANNED
1004 -- PA: CONTRACT, BLANKET
1005 -- RELEASE: SCHEDULED, BLANKET
1006 --p_doc_level
1007 -- Specifies the level of the document that is being checked:
1008 -- For Reject, this should always be g_HEADER
1009 --p_doc_level_id
1010 -- The id corresponding to the doc level type:
1011 -- For Reject, this should always be header_id (or release_id for Releases)
1012 --p_use_enc_gt_flag
1013 -- Set to g_parameter_NO if calling with p_doc_level_id
1014 -- Else if using the inteface table g_parameter_YES
1015 --p_override_funds
1016 -- Indicates whether funds override capability can be used if needed, to make a
1017 -- transaction succeed.
1018 --p_use_gl_date
1019 -- Flag that specifies whether to always prefer using the existing distribution
1020 -- GL date instead of the override date, when possible
1021 --p_override_date
1022 -- Caller-specified date to use for distribution encumbrance date in GL entries
1023 --OUT:
1024 --x_return_status
1025 -- FND_API.G_RET_STS_SUCCESS if validation succeeds
1026 -- FND_API.G_RET_STS_ERROR if validation fails
1027 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
1028 --x_po_return_code
1029 -- Indicates whether PO is classifying this transaction as an
1030 -- Error/Warning/Partial Success/Success
1031 --x_online_report_id
1032 -- Unique id into po_online_report_text rows that store distribution specific
1033 -- reporting information for a specific encumbrance transaction
1034 --Testing:
1035 --
1036 --End of Comments
1037 -------------------------------------------------------------------------------
1038 PROCEDURE do_reject(
1039 x_return_status OUT NOCOPY VARCHAR2
1040 , p_doc_type IN VARCHAR2
1041 , p_doc_subtype IN VARCHAR2
1042 , p_doc_level IN VARCHAR2
1043 , p_doc_level_id IN NUMBER
1044 , p_use_enc_gt_flag IN VARCHAR2
1045 , p_override_funds IN VARCHAR2
1046 , p_use_gl_date IN VARCHAR2
1047 , p_override_date IN DATE
1048 , x_po_return_code OUT NOCOPY VARCHAR2
1049 , x_online_report_id OUT NOCOPY NUMBER
1050 )
1051 IS
1052
1053 l_api_name CONSTANT varchar2(30) := 'DO_REJECT';
1054 l_log_head CONSTANT VARCHAR2(100) := g_log_head || l_api_name;
1055 l_progress VARCHAR2(3) := '000';
1056 l_packet_id NUMBER;
1057
1058 BEGIN
1059
1060 IF g_debug_stmt THEN
1061 PO_DEBUG.debug_begin(l_log_head);
1062 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_type',p_doc_type);
1063 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_subtype',p_doc_subtype);
1064 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_level',p_doc_level);
1065 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_level_id',p_doc_level_id);
1066 PO_DEBUG.debug_var(l_log_head,l_progress,'p_use_enc_gt_flag',p_use_enc_gt_flag);
1067 PO_DEBUG.debug_var(l_log_head,l_progress,'p_override_funds',p_override_funds);
1068 PO_DEBUG.debug_var(l_log_head,l_progress,'p_use_gl_date',p_use_gl_date);
1069 PO_DEBUG.debug_var(l_log_head,l_progress,'p_override_date',p_override_date);
1070 END IF;
1071
1072 l_progress := '010';
1073
1074 -- Standard Start of API savepoint
1075 SAVEPOINT DO_REJECT_SP;
1076
1077 l_progress := '020';
1078
1079 x_po_return_code := NULL;
1080 x_online_report_id := NULL;
1081
1082 do_action(x_return_status => x_return_status,
1083 p_action => g_action_REJECT,
1084 p_check_only_flag => g_parameter_NO,
1085 p_cbc_flag => NULL,
1086 p_doc_type => p_doc_type,
1087 p_doc_subtype => p_doc_subtype,
1088 p_doc_level => p_doc_level,
1089 p_doc_level_id => p_doc_level_id,
1090 p_use_enc_gt_flag => p_use_enc_gt_flag,
1091 p_employee_id => NULL,
1092 p_override_funds => p_override_funds,
1093 p_prevent_partial_flag => NULL,
1094 p_use_gl_date => p_use_gl_date,
1095 p_override_date => p_override_date,
1096 p_validate_document => g_parameter_NO,
1097 p_use_force_mode => g_parameter_NO,
1098 x_packet_id => l_packet_id,
1099 x_po_return_code => x_po_return_code,
1100 x_online_report_id => x_online_report_id);
1101
1102 l_progress := '100';
1103
1104 IF ( x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
1105 OR x_return_status IS NULL
1106 ) THEN
1107 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1108 END IF;
1109
1110 l_progress := '900';
1111
1112 IF g_debug_stmt THEN
1113 PO_DEBUG.debug_var(l_log_head,l_progress,'l_packet_id',l_packet_id);
1114 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
1115 PO_DEBUG.debug_var(l_log_head,l_progress,'x_po_return_code',x_po_return_code);
1116 PO_DEBUG.debug_var(l_log_head,l_progress,'x_online_report_id',x_online_report_id);
1117 PO_DEBUG.debug_end(l_log_head);
1118 END IF;
1119
1120 EXCEPTION
1121 WHEN OTHERS THEN
1122 -- bug 3454804 - robust exception handling
1123 BEGIN
1124 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS Start');
1125
1126 -- Do all of the common exception handling.
1127 handle_exception(l_api_name,l_progress,x_return_status,x_po_return_code);
1128
1129 PO_DEBUG.debug_unexp(l_log_head,l_progress,'handle_exception done');
1130
1131 IF g_debug_stmt THEN
1132 PO_DEBUG.debug_var(l_log_head,l_progress,'l_packet_id',l_packet_id);
1133 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
1134 PO_DEBUG.debug_var(l_log_head,l_progress,'x_po_return_code',x_po_return_code);
1135 PO_DEBUG.debug_var(l_log_head,l_progress,'x_online_report_id',x_online_report_id);
1136 END IF;
1137
1138 -- Rollback as per API standards.
1139 ROLLBACK TO DO_REJECT_SP;
1140
1141 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS End');
1142
1143 EXCEPTION
1144 WHEN OTHERS THEN
1145 NULL;
1146 END;
1147
1148 END do_reject;
1149
1150
1151 ------------------------------------------------------------------------------
1152 --Start of Comments
1153 --Name: do_cancel
1154 --Pre-reqs:
1155 -- The cancel code must already have set the cancel_flag on the relevant doc
1156 -- level to be 'I' (is this always required?)
1157 -- The cancel code must have already created a new Req distribution if Recreate
1158 -- (and hence re-encumber) Demand is requested.
1159 --Modifies:
1160 -- Creates encumbrance entries in the gl_bc_packets table
1161 -- Adds distribution-specific transaction information into the
1162 -- po_online_report_text table
1163 --Locks:
1164 -- None.
1165 --Function:
1166 -- This procedure cancels the encumbrance on all eligible distributions of
1167 -- the requested document or document doc level
1168 --Parameters:
1169 --IN:
1170 --p_doc_type
1171 -- Differentiates between the doc being a REQ, PA, PO, or RELEASE,
1172 -- which is used to identify the tables to look at (PO vs. Req)
1173 -- and the join conditions
1174 --p_doc_subtype
1175 -- Differentiates between the subtypes of documents
1176 -- REQ: NULL
1177 -- PO: STANDARD, PLANNED
1178 -- PA: CONTRACT, BLANKET
1179 -- RELEASE: SCHEDULED, BLANKET
1180 --p_doc_level
1181 -- Specifies the level of the document that is being checked:
1182 -- HEADER, LINE, SHIPMENT, DISTRIBUTION
1183 --p_doc_level_id
1184 -- The id corresponding to the doc level type:
1185 -- header_id/release_id, line_id, line_location_id, distribution_id
1186 --p_use_enc_gt_flag
1187 -- Set to g_parameter_NO if calling with p_doc_level_id
1188 -- Else if using the inteface table g_parameter_YES
1189 --p_override_funds
1190 -- Indicates whether funds override capability can be used if needed, to make a
1191 -- transaction succeed.
1192 --p_use_gl_date
1193 -- Flag that specifies whether to always prefer using the existing distribution
1194 -- GL date instead of the override date, when possible
1195 --p_override_date
1196 -- Caller-specified date to use for distribution encumbrance date in GL entries
1197 --OUT:
1198 --x_return_status
1199 -- FND_API.G_RET_STS_SUCCESS if validation succeeds
1200 -- FND_API.G_RET_STS_ERROR if validation fails
1201 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
1202 --x_po_return_code
1203 -- Indicates whether PO is classifying this transaction as an
1204 -- Error/Warning/Partial Success/Success
1205 --x_online_report_id
1206 -- Unique id into po_online_report_text rows that store distribution specific
1207 -- reporting information for a specific encumbrance transaction
1208 --Testing:
1209 --
1210 --End of Comments
1211 -------------------------------------------------------------------------------
1212 PROCEDURE do_cancel(
1213 x_return_status OUT NOCOPY VARCHAR2
1214 , p_doc_type IN VARCHAR2
1215 , p_doc_subtype IN VARCHAR2
1216 , p_doc_level IN VARCHAR2
1217 , p_doc_level_id IN NUMBER
1218 , p_use_enc_gt_flag IN VARCHAR2
1219 , p_override_funds IN VARCHAR2
1220 , p_use_gl_date IN VARCHAR2
1221 , p_override_date IN DATE
1222 , x_po_return_code OUT NOCOPY VARCHAR2
1223 , x_online_report_id OUT NOCOPY NUMBER
1224 )
1225 IS
1226
1227 l_api_name CONSTANT varchar2(30) := 'DO_CANCEL';
1228 l_log_head CONSTANT VARCHAR2(100) := g_log_head || l_api_name;
1229 l_progress VARCHAR2(3) := '000';
1230 l_packet_id NUMBER;
1231
1232 BEGIN
1233
1234 IF g_debug_stmt THEN
1235 PO_DEBUG.debug_begin(l_log_head);
1236 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_type',p_doc_type);
1237 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_subtype',p_doc_subtype);
1238 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_level',p_doc_level);
1239 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_level_id',p_doc_level_id);
1240 PO_DEBUG.debug_var(l_log_head,l_progress,'p_use_enc_gt_flag',p_use_enc_gt_flag);
1241 PO_DEBUG.debug_var(l_log_head,l_progress,'p_override_funds',p_override_funds);
1242 PO_DEBUG.debug_var(l_log_head,l_progress,'p_use_gl_date',p_use_gl_date);
1243 PO_DEBUG.debug_var(l_log_head,l_progress,'p_override_date',p_override_date);
1244 END IF;
1245
1246 l_progress := '010';
1247
1248 -- Standard Start of API savepoint
1249 SAVEPOINT DO_CANCEL_SP;
1250
1251 l_progress := '020';
1252
1253 x_po_return_code := NULL;
1254 x_online_report_id := NULL;
1255
1256 do_action(x_return_status => x_return_status,
1257 p_action => g_action_CANCEL,
1258 p_check_only_flag => g_parameter_NO,
1259 p_cbc_flag => NULL,
1260 p_doc_type => p_doc_type,
1261 p_doc_subtype => p_doc_subtype,
1262 p_doc_level => p_doc_level,
1263 p_doc_level_id => p_doc_level_id,
1264 p_use_enc_gt_flag => p_use_enc_gt_flag,
1265 p_employee_id => NULL,
1266 p_override_funds => p_override_funds,
1267 p_prevent_partial_flag => NULL,
1268 p_use_gl_date => p_use_gl_date,
1269 p_override_date => p_override_date,
1270 p_validate_document => g_parameter_NO,
1271 p_use_force_mode => g_parameter_NO,
1272 x_packet_id => l_packet_id,
1273 x_po_return_code => x_po_return_code,
1274 x_online_report_id => x_online_report_id);
1275
1276 l_progress := '100';
1277
1278 IF ( x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
1279 OR x_return_status IS NULL
1280 ) THEN
1281 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1282 END IF;
1283
1284 l_progress := '900';
1285
1286 IF g_debug_stmt THEN
1287 PO_DEBUG.debug_var(l_log_head,l_progress,'l_packet_id',l_packet_id);
1288 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
1289 PO_DEBUG.debug_var(l_log_head,l_progress,'x_po_return_code',x_po_return_code);
1290 PO_DEBUG.debug_var(l_log_head,l_progress,'x_online_report_id',x_online_report_id);
1291 PO_DEBUG.debug_end(l_log_head);
1292 END IF;
1293
1294 EXCEPTION
1295 WHEN OTHERS THEN
1296 -- bug 3454804 - robust exception handling
1297 BEGIN
1298 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS Start');
1299
1300 -- Do all of the common exception handling.
1301 handle_exception(l_api_name,l_progress,x_return_status,x_po_return_code);
1302
1303 PO_DEBUG.debug_unexp(l_log_head,l_progress,'handle_exception done');
1304
1305 IF g_debug_stmt THEN
1306 PO_DEBUG.debug_var(l_log_head,l_progress,'l_packet_id',l_packet_id);
1307 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
1308 PO_DEBUG.debug_var(l_log_head,l_progress,'x_po_return_code',x_po_return_code);
1309 PO_DEBUG.debug_var(l_log_head,l_progress,'x_online_report_id',x_online_report_id);
1310 END IF;
1311
1312 -- Rollback as per API standards.
1313 ROLLBACK TO DO_CANCEL_SP;
1314
1315 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS End');
1316
1317 EXCEPTION
1318 WHEN OTHERS THEN
1319 NULL;
1320 END;
1321
1322 END do_cancel;
1323
1324
1325 ------------------------------------------------------------------------------
1326 --Start of Comments
1327 --Name: do_adjust
1328 --Pre-reqs:
1329 -- N/A.
1330 --Modifies:
1331 -- Creates encumbrance entries in the gl_bc_packets table
1332 -- Adds distribution-specific transaction information into the
1333 -- po_online_report_text table
1334 --Locks:
1335 -- None.
1336 --Function:
1337 -- This procedure cancels the encumbrance on all eligible distributions of
1338 -- the requested document or document doc level
1339 --Parameters:
1340 --IN:
1341 --p_doc_type
1342 -- Differentiates between the doc being a REQ, PA, PO, or RELEASE,
1343 -- which is used to identify the tables to look at (PO vs. Req)
1344 -- and the join conditions
1345 --p_doc_subtype
1346 -- Differentiates between the subtypes of documents
1347 -- REQ: NULL
1348 -- PO: STANDARD, PLANNED
1349 -- PA: CONTRACT, BLANKET
1350 -- RELEASE: SCHEDULED, BLANKET
1351 --p_use_gl_date
1352 -- Flag that specifies whether to always prefer using the existing distribution
1353 -- GL date instead of the override date, when possible
1354 --p_override_date
1355 -- Caller-specified date to use for distribution encumbrance date in GL entries
1356 --p_override_funds
1357 -- Indicates whether funds override capability can be used if needed, to make a
1358 -- transaction succeed.
1359 --p_employee_id
1360 -- Employee Id of the user taking the action
1361 --p_validate_document
1362 -- Indicates whether to perform general document state/submission checks
1363 -- If 'Y', then encumbrance code will make calls to check doc correctness
1364 -- If 'N', then the caller has already done these validations.
1365 --OUT:
1366 --x_return_status
1367 -- FND_API.G_RET_STS_SUCCESS if validation succeeds
1368 -- FND_API.G_RET_STS_ERROR if validation fails
1369 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
1370 --x_po_return_code
1371 -- Indicates whether PO is classifying this transaction as an
1372 -- Error/Warning/Partial Success/Success
1373 --x_online_report_id
1374 -- Unique id into po_online_report_text rows that store distribution specific
1375 -- reporting information for a specific encumbrance transaction
1376 --Testing:
1377 --
1378 --End of Comments
1379 -------------------------------------------------------------------------------
1380 PROCEDURE do_adjust(
1381 x_return_status OUT NOCOPY VARCHAR2
1382 , p_doc_type IN VARCHAR2
1383 , p_doc_subtype IN VARCHAR2
1384 , p_override_funds IN VARCHAR2
1385 , p_use_gl_date IN VARCHAR2
1386 , p_validate_document IN VARCHAR2
1387 , p_override_date IN DATE
1388 , p_employee_id IN NUMBER
1389 , x_po_return_code OUT NOCOPY VARCHAR2
1390 , x_online_report_id OUT NOCOPY NUMBER
1391 )
1392 IS
1393
1394 l_api_name CONSTANT varchar2(30) := 'DO_ADJUST';
1395 l_log_head CONSTANT VARCHAR2(100) := g_log_head || l_api_name;
1396 l_progress VARCHAR2(3) := '000';
1397 l_packet_id NUMBER;
1398
1399
1400
1401 BEGIN
1402
1403 IF g_debug_stmt THEN
1404 PO_DEBUG.debug_begin(l_log_head);
1405 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_type',p_doc_type);
1406 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_subtype',p_doc_subtype);
1407 PO_DEBUG.debug_var(l_log_head,l_progress,'p_override_funds',p_override_funds);
1408 PO_DEBUG.debug_var(l_log_head,l_progress,'p_use_gl_date',p_use_gl_date);
1409 PO_DEBUG.debug_var(l_log_head,l_progress,'p_validate_document',p_validate_document);
1410 PO_DEBUG.debug_var(l_log_head,l_progress,'p_override_date',p_override_date);
1411 PO_DEBUG.debug_var(l_log_head,l_progress,'p_employee_id',p_employee_id);
1412 END IF;
1413
1414 l_progress := '010';
1415
1416 -- Standard Start of API savepoint
1417 SAVEPOINT DO_ADJUST_SP;
1418
1419 l_progress := '020';
1420
1421 x_po_return_code := NULL;
1422 x_online_report_id := NULL;
1423
1424 do_action(x_return_status => x_return_status,
1425 p_action => g_action_ADJUST,
1426 p_check_only_flag => g_parameter_NO,
1427 p_cbc_flag => NULL,
1428 p_doc_type => p_doc_type,
1429 p_doc_subtype => p_doc_subtype,
1430 p_doc_level => NULL,
1431 p_doc_level_id => NULL,
1432 p_use_enc_gt_flag => g_parameter_YES,
1433 p_employee_id => p_employee_id,
1434 p_override_funds => p_override_funds,
1435 p_prevent_partial_flag => NULL,
1436 p_use_gl_date => p_use_gl_date,
1437 p_override_date => p_override_date,
1438 p_validate_document => p_validate_document,
1439 p_use_force_mode => g_parameter_NO,
1440 x_packet_id => l_packet_id,
1441 x_po_return_code => x_po_return_code,
1442 x_online_report_id => x_online_report_id);
1443
1444 l_progress := '100';
1445
1446 IF ( x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
1447 OR x_return_status IS NULL
1448 ) THEN
1449 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1450 END IF;
1451
1452 l_progress := '900';
1453
1454 IF g_debug_stmt THEN
1455 PO_DEBUG.debug_var(l_log_head,l_progress,'l_packet_id',l_packet_id);
1456 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
1457 PO_DEBUG.debug_var(l_log_head,l_progress,'x_po_return_code',x_po_return_code);
1458 PO_DEBUG.debug_var(l_log_head,l_progress,'x_online_report_id',x_online_report_id);
1459 PO_DEBUG.debug_end(l_log_head);
1460 END IF;
1461
1462 EXCEPTION
1463 WHEN OTHERS THEN
1464 -- bug 3454804 - robust exception handling
1465 BEGIN
1466 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS Start');
1467
1468 -- Do all of the common exception handling.
1469 handle_exception(l_api_name,l_progress,x_return_status,x_po_return_code);
1470
1471 PO_DEBUG.debug_unexp(l_log_head,l_progress,'handle_exception done');
1472
1473 IF g_debug_stmt THEN
1474 PO_DEBUG.debug_var(l_log_head,l_progress,'l_packet_id',l_packet_id);
1475 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
1476 PO_DEBUG.debug_var(l_log_head,l_progress,'x_po_return_code',x_po_return_code);
1477 PO_DEBUG.debug_var(l_log_head,l_progress,'x_online_report_id',x_online_report_id);
1478 END IF;
1479
1480 -- Rollback as per API standards.
1481 ROLLBACK TO DO_ADJUST_SP;
1482
1483 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS End');
1484
1485 EXCEPTION
1486 WHEN OTHERS THEN
1487 NULL;
1488 END;
1489
1490 END do_adjust;
1491
1492
1493 --------------------------------------------------------------------------------
1494 --Start of Comments
1495 --Name: do_req_split
1496 --Pre-reqs:
1497 -- None.
1498 --Modifies:
1499 -- Creates encumbrance entries in the gl_bc_packets table.
1500 -- Adds distribution-specific transaction information into the
1501 -- po_online_report_text table.
1502 -- Adds entries to the action history.
1503 -- Manipulates PO_ENCUMBRANCE_GT.
1504 -- Updates the base document tables with encumbrance results.
1505 --Function:
1506 -- This procedure transfers encumbrance from OLD requisition
1507 -- lines/distributions to NEW lines/distributions.
1508 -- The OLD distributions will be unreserved and the NEW distributions
1509 -- will be reserved.
1510 --Parameters:
1511 --IN:
1512 --p_before_distribution_ids_tbl
1513 -- A table of req distribution ids that exist before the split.
1514 --p_after_distribution_ids_tbl
1515 -- A table of req distribution ids that should be reserved after the split.
1516 --p_employee_id
1517 -- Employee Id of the user taking the action.
1518 -- This is used in the action history entry.
1519 -- If NULL is passed, the value will be derived from the current FND user.
1520 --p_override_funds
1521 -- Indicates whether funds override capability can be used, if needed,
1522 -- to make a transaction succeed.
1523 -- g_parameter_NO - don't use override capability
1524 -- g_parameter_YES - okay to use override
1525 -- g_parameter_USE_PROFILE - base the decision on the profile option
1526 -- PO:Override Funds Reservation
1527 --p_override_date
1528 -- Caller-specified date to use instead of distribution encumbrance date
1529 -- in GL entries.
1530 --OUT:
1531 --x_return_status
1532 -- VARCHAR2(1)
1533 -- FND_API.G_RET_STS_SUCCESS for success
1534 -- FND_API.G_RET_STS_ERROR for a forseen error
1535 -- FND_API.G_RET_STS_UNEXP_ERROR if an unexpected error occurs
1536 --x_po_return_code
1537 -- VARCHAR2(10)
1538 -- Indicates the PO classification of the results of this transaction.
1539 -- g_return_<>
1540 -- SUCCESS
1541 -- WARNING
1542 -- PARTIAL
1543 -- FAILURE
1544 -- FATAL
1545 --x_online_report_id
1546 -- Unique id into po_online_report_text rows that store distribution specific
1547 -- reporting information for a specific encumbrance transaction.
1548 --Testing:
1549 --
1550 --End of Comments
1551 -------------------------------------------------------------------------------
1552 PROCEDURE do_req_split(
1553 x_return_status OUT NOCOPY VARCHAR2
1554 , p_before_dist_ids_tbl IN po_tbl_number
1555 , p_after_dist_ids_tbl IN po_tbl_number
1556 , p_employee_id IN NUMBER
1557 , p_override_funds IN VARCHAR2
1558 , p_override_date IN DATE
1559 , x_po_return_code OUT NOCOPY VARCHAR2
1560 , x_online_report_id OUT NOCOPY NUMBER
1561 )
1562 IS
1563
1564 l_api_name CONSTANT VARCHAR2(30) := 'DO_REQ_SPLIT';
1565 l_log_head CONSTANT VARCHAR2(100) := g_log_head || l_api_name;
1566 l_progress VARCHAR2(3) := '000';
1567 l_packet_id NUMBER;
1568 l_force_mode VARCHAR2(1);
1569 BEGIN
1570
1571 IF g_debug_stmt THEN
1572 PO_DEBUG.debug_begin(l_log_head);
1573 PO_DEBUG.debug_var(l_log_head,l_progress,'p_before_dist_ids_tbl', p_before_dist_ids_tbl);
1574 PO_DEBUG.debug_var(l_log_head,l_progress,'p_after_dist_ids_tbl', p_after_dist_ids_tbl);
1575 PO_DEBUG.debug_var(l_log_head,l_progress,'p_override_funds',p_override_funds);
1576 PO_DEBUG.debug_var(l_log_head,l_progress,'p_override_date',p_override_date);
1577 END IF;
1578
1579 l_progress := '010';
1580
1581 -- Standard Start of API savepoint
1582 SAVEPOINT DO_REQ_SPLIT_SP;
1583 x_po_return_code := NULL;
1584 x_online_report_id := NULL;
1585
1586 l_progress := '100';
1587 IF g_debug_stmt THEN
1588 PO_DEBUG.debug_stmt(l_log_head,l_progress,
1589 'Populate po_encumbrance_gt with before distribution ids');
1590 END IF;
1591
1592 -- Fill the GTT with the data from the doc tables.
1593 populate_encumbrance_gt(
1594 x_return_status => x_return_status
1595 , p_doc_type => g_doc_type_REQUISITION
1596 , p_doc_level => g_doc_level_DISTRIBUTION
1597 , p_doc_level_id_tbl => p_before_dist_ids_tbl
1598 , p_adjustment_status_tbl => po_tbl_varchar5(g_adjustment_status_OLD)
1599 , p_check_only_flag => g_parameter_NO
1600 );
1601
1602 l_progress := '200';
1603
1604 IF ( x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
1605 OR x_return_status IS NULL
1606 ) THEN
1607 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1608 END IF;
1609
1610 l_progress := '210';
1611 IF g_debug_stmt THEN
1612 PO_DEBUG.debug_stmt(l_log_head,l_progress,
1613 'Populate po_encumbrance_gt with after distribution ids');
1614 END IF;
1615
1616 -- Fill the GTT with the data from the doc tables.
1617 populate_encumbrance_gt(
1618 x_return_status => x_return_status
1619 , p_doc_type => g_doc_type_REQUISITION
1620 , p_doc_level => g_doc_level_DISTRIBUTION
1621 , p_doc_level_id_tbl => p_after_dist_ids_tbl
1622 , p_adjustment_status_tbl => po_tbl_varchar5(g_adjustment_status_NEW)
1623 , p_check_only_flag => g_parameter_NO
1624 );
1625
1626 l_progress := '300';
1627
1628 IF ( x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
1629 OR x_return_status IS NULL
1630 ) THEN
1631 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1632 END IF;
1633
1634 l_progress := '310';
1635 IF g_debug_stmt THEN
1636 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Call do_action');
1637 END IF;
1638 --<bug#5200462 START>
1639 --We should be calling Req Split with force mode
1640 --for all non-federal cases. This is to ensure that
1641 --requisition split always succeeds. This is allright
1642 --because we are not increasing funds in any way. We
1643 --can only keep it constant or decrease funds.
1644 IF FV_INSTALL.enabled THEN
1645 l_force_mode := g_parameter_NO;
1646 ELSE
1647 l_force_mode := g_parameter_YES;
1648 END IF;
1649 --<bug#5200462 END>
1650 -- Call do_action
1651 do_action(
1652 x_return_status => x_return_status
1653 , p_action => g_action_REQ_SPLIT
1654 , p_check_only_flag => g_parameter_NO
1655 , p_cbc_flag => g_parameter_NO
1656 , p_doc_type => g_doc_type_REQUISITION
1657 , p_doc_subtype => NULL
1658 , p_doc_level => g_doc_level_DISTRIBUTION
1659 , p_doc_level_id => NULL
1660 , p_use_enc_gt_flag => g_parameter_YES
1661 , p_employee_id => p_employee_id
1662 , p_override_funds => p_override_funds
1663 , p_prevent_partial_flag => g_parameter_YES
1664 , p_use_gl_date => g_parameter_YES
1665 , p_override_date => p_override_date
1666 , p_validate_document => g_parameter_NO
1667 , p_use_force_mode => l_force_mode
1668 , x_packet_id => l_packet_id
1669 , x_online_report_id => x_online_report_id
1670 , x_po_return_code => x_po_return_code
1671 );
1672
1673 l_progress := '400';
1674
1675 IF ( x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
1676 OR x_return_status IS NULL
1677 ) THEN
1678 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1679 END IF;
1680
1681 l_progress := '900';
1682
1683 IF g_debug_stmt THEN
1684 PO_DEBUG.debug_var(l_log_head,l_progress,'l_packet_id',l_packet_id);
1685 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
1686 PO_DEBUG.debug_var(l_log_head,l_progress,'x_po_return_code',x_po_return_code);
1687 PO_DEBUG.debug_var(l_log_head,l_progress,'x_online_report_id',x_online_report_id);
1688 PO_DEBUG.debug_end(l_log_head);
1689 END IF;
1690
1691 EXCEPTION
1692 WHEN OTHERS THEN
1693 -- bug 3454804 - robust exception handling
1694 BEGIN
1695 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS Start');
1696
1697 -- Do all of the common exception handling.
1698 handle_exception(l_api_name,l_progress,x_return_status,x_po_return_code);
1699
1700 PO_DEBUG.debug_unexp(l_log_head,l_progress,'handle_exception done');
1701
1702 IF g_debug_stmt THEN
1703 PO_DEBUG.debug_var(l_log_head,l_progress,'l_packet_id',l_packet_id);
1704 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
1705 PO_DEBUG.debug_var(l_log_head,l_progress,'x_po_return_code',x_po_return_code);
1706 PO_DEBUG.debug_var(l_log_head,l_progress,'x_online_report_id',x_online_report_id);
1707 END IF;
1708
1709 -- Rollback as per API standards.
1710 ROLLBACK TO DO_REQ_SPLIT_SP;
1711
1712 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS End');
1713
1714 EXCEPTION
1715 WHEN OTHERS THEN
1716 NULL;
1717 END;
1718
1719 END do_req_split;
1720
1721
1722 ------------------------------------------------------------------------------
1723 --Start of Comments
1724 --Name: do_final_close
1725 --Pre-reqs:
1726 -- N/A.
1727 --Modifies:
1728 -- Creates encumbrance entries in the gl_bc_packets table
1729 -- Adds distribution-specific transaction information into the
1730 -- po_online_report_text table
1731 --Locks:
1732 -- None.
1733 --Function:
1734 -- This procedure liquidates the encumbrance on all eligible distributions of
1735 -- the requested document or document doc level
1736 --Parameters:
1737 --IN:
1738 --p_doc_type
1739 -- Differentiates between the doc being a REQ, PA, PO, or RELEASE,
1740 -- which is used to identify the tables to look at (PO vs. Req)
1741 -- and the join conditions
1742 --p_doc_subtype
1743 -- Differentiates between the subtypes of documents
1744 -- REQ: NULL
1745 -- PO: STANDARD, PLANNED
1746 -- PA: CONTRACT, BLANKET
1747 -- RELEASE: SCHEDULED, BLANKET
1748 --p_doc_level
1749 -- Specifies the level of the document that is being checked:
1750 -- HEADER, LINE, SHIPMENT, DISTRIBUTION
1751 --p_doc_level_id
1752 -- The id corresponding to the doc level type:
1753 -- header_id/release_id, line_id, line_location_id, distribution_id
1754 --p_use_enc_gt_flag
1755 -- Set to g_parameter_NO if calling with p_doc_level_id
1756 -- Else if using the inteface table g_parameter_YES
1757 --p_use_gl_date
1758 -- Flag that specifies whether to always prefer using the existing distribution
1759 -- GL date instead of the override date, when possible
1760 --p_override_date
1761 -- Caller-specified date to use for distribution encumbrance date in GL entries
1762 --p_invoice_id
1763 -- The id of the invoice that is causing the final match, when coming
1764 -- through an AP call.
1765 --OUT:
1766 --x_return_status
1767 -- FND_API.G_RET_STS_SUCCESS if validation succeeds
1768 -- FND_API.G_RET_STS_ERROR if validation fails
1769 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
1770 --x_po_return_code
1771 -- Indicates whether PO is classifying this transaction as an
1772 -- Error/Warning/Partial Success/Success
1773 --x_online_report_id
1774 -- Unique id into po_online_report_text rows that store distribution specific
1775 -- reporting information for a specific encumbrance transaction
1776 --Testing:
1777 --
1778 --End of Comments
1779 -------------------------------------------------------------------------------
1780 PROCEDURE do_final_close(
1781 x_return_status OUT NOCOPY VARCHAR2
1782 , p_doc_type IN VARCHAR2
1783 , p_doc_subtype IN VARCHAR2
1784 , p_doc_level IN VARCHAR2
1785 , p_doc_level_id IN NUMBER
1786 , p_use_enc_gt_flag IN VARCHAR2
1787 , p_use_gl_date IN VARCHAR2
1788 , p_override_date IN DATE
1789 , p_invoice_id IN NUMBER DEFAULT NULL
1790 , x_po_return_code OUT NOCOPY VARCHAR2
1791 , x_online_report_id OUT NOCOPY NUMBER
1792 )
1793 IS
1794
1795 l_api_name CONSTANT varchar2(30) := 'DO_FINAL_CLOSE';
1796 l_log_head CONSTANT VARCHAR2(100) := g_log_head || l_api_name;
1797 l_progress VARCHAR2(3) := '000';
1798 l_packet_id NUMBER;
1799
1800 BEGIN
1801
1802 IF g_debug_stmt THEN
1803 PO_DEBUG.debug_begin(l_log_head);
1804 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_type',p_doc_type);
1805 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_subtype',p_doc_subtype);
1806 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_level',p_doc_level);
1807 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_level_id',p_doc_level_id);
1808 PO_DEBUG.debug_var(l_log_head,l_progress,'p_use_enc_gt_flag',p_use_enc_gt_flag);
1809 PO_DEBUG.debug_var(l_log_head,l_progress,'p_use_gl_date',p_use_gl_date);
1810 PO_DEBUG.debug_var(l_log_head,l_progress,'p_override_date',p_override_date);
1811 PO_DEBUG.debug_var(l_log_head,l_progress,'p_invoice_id',p_invoice_id);
1812 END IF;
1813
1814 l_progress := '010';
1815
1816 -- Standard Start of API savepoint
1817 SAVEPOINT DO_FINAL_CLOSE_SP;
1818
1819 l_progress := '020';
1820
1821 x_po_return_code := NULL;
1822 x_online_report_id := NULL;
1823
1824 do_action(
1825 x_return_status => x_return_status
1826 , p_action => g_action_FINAL_CLOSE
1827 , p_check_only_flag => g_parameter_NO
1828 , p_cbc_flag => NULL
1829 , p_doc_type => p_doc_type
1830 , p_doc_subtype => p_doc_subtype
1831 , p_doc_level => p_doc_level
1832 , p_doc_level_id => p_doc_level_id
1833 , p_use_enc_gt_flag => p_use_enc_gt_flag
1834 , p_employee_id => NULL
1835 , p_override_funds => NULL
1836 , p_prevent_partial_flag => NULL
1837 , p_use_gl_date => p_use_gl_date
1838 , p_override_date => p_override_date
1839 , p_validate_document => g_parameter_NO
1840 , p_use_force_mode => g_parameter_NO
1841 , p_invoice_id => p_invoice_id
1842 , p_ap_encumbered_amount => NULL
1843 , p_ap_cancelled_quantity => NULL
1844 , p_budget_acct_id => NULL
1845 , x_packet_id => l_packet_id
1846 , x_po_return_code => x_po_return_code
1847 , x_online_report_id => x_online_report_id
1848 );
1849
1850 l_progress := '100';
1851
1852 IF ( x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
1853 OR x_return_status IS NULL
1854 ) THEN
1855 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1856 END IF;
1857
1858 l_progress := '900';
1859
1860 IF g_debug_stmt THEN
1861 PO_DEBUG.debug_var(l_log_head,l_progress,'l_packet_id',l_packet_id);
1862 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
1863 PO_DEBUG.debug_var(l_log_head,l_progress,'x_po_return_code',x_po_return_code);
1864 PO_DEBUG.debug_var(l_log_head,l_progress,'x_online_report_id',x_online_report_id);
1865 PO_DEBUG.debug_end(l_log_head);
1866 END IF;
1867
1868 EXCEPTION
1869 WHEN OTHERS THEN
1870 -- bug 3454804 - robust exception handling
1871 BEGIN
1872 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS Start');
1873
1874 -- Do all of the common exception handling.
1875 handle_exception(l_api_name,l_progress,x_return_status,x_po_return_code);
1876
1877 PO_DEBUG.debug_unexp(l_log_head,l_progress,'handle_exception done');
1878
1879 IF g_debug_stmt THEN
1880 PO_DEBUG.debug_var(l_log_head,l_progress,'l_packet_id',l_packet_id);
1881 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
1882 PO_DEBUG.debug_var(l_log_head,l_progress,'x_po_return_code',x_po_return_code);
1883 PO_DEBUG.debug_var(l_log_head,l_progress,'x_online_report_id',x_online_report_id);
1884 END IF;
1885
1886 -- Rollback as per API standards.
1887 ROLLBACK TO DO_FINAL_CLOSE_SP;
1888
1889 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS End');
1890
1891 EXCEPTION
1892 WHEN OTHERS THEN
1893 NULL;
1894 END;
1895
1896 END do_final_close;
1897
1898
1899 ------------------------------------------------------------------------------
1900 --Start of Comments
1901 --Name: undo_final_close
1902 --Pre-reqs:
1903 -- n/a
1904 --Modifies:
1905 -- Creates encumbrance entries in the gl_bc_packets table
1906 -- Adds distribution-specific transaction information into the
1907 -- po_online_report_text table
1908 --Function:
1909 -- This procedure performs funds reservation on all eligible
1910 -- distributions of a finally closed PO Shipment which was
1911 -- finally closed due to AP finally invoice match, now AP cancels
1912 -- the finally invoice match
1913 --Parameters:
1914 --IN:
1915 --p_doc_type
1916 -- Differentiates between the doc being a REQ, PA, PO, or RELEASE,
1917 -- which is used to identify the tables to look at (PO vs. Req)
1918 -- and the join conditions
1919 --p_doc_subtype
1920 -- Differentiates between the subtypes of documents
1921 -- REQ: NULL
1922 -- PO: STANDARD, PLANNED
1923 -- PA: CONTRACT, BLANKET
1924 -- RELEASE: SCHEDULED, BLANKET
1925 --p_doc_level
1926 -- Specifies the level of the document that is being checked:
1927 -- HEADER, LINE, SHIPMENT, DISTRIBUTION
1928 --p_doc_level_id
1929 -- The id corresponding to the entity type:
1930 -- header_id/release_id, line_id, line_location_id, distribution_id
1931 --p_use_enc_gt_flag
1932 -- Specifies if the input values have been populated in the PO_ENCUMBRANCE_GT
1933 -- table (using populate_encumbrance_gt procedure) instead of being passed in
1934 -- as parameters. Has to be used if multiple ids needs to passed in.
1935 --p_override_funds
1936 -- Indicates whether funds override capability can be used if needed, to make a
1937 -- transaction succeed.
1938 --p_use_gl_date
1939 -- Flag that specifies whether to always prefer using the existing distribution
1940 -- GL date instead of the override date, when possible
1941 --p_override_date
1942 -- Caller-specified date to use for distribution encumbrance date in GL entries
1943 --p_invoice_id
1944 -- The id of the invoice that is being cancelled to cause the undo of the
1945 -- final match.
1946 --OUT:
1947 --x_return_status
1948 -- APPS Standard parameter
1949 --x_po_return_code
1950 -- Indicates whether Requisition is classifying this transaction as an
1951 -- Error/Warning/Partial Success/Success
1952 --x_online_report_id
1953 -- Unique id into po_online_report_text rows that store distribution specific
1954 -- reporting information for a specific encumbrance transaction
1955 --Testing:
1956 --
1957 --End of Comments
1958 -------------------------------------------------------------------------------
1959 PROCEDURE undo_final_close(
1960 x_return_status OUT NOCOPY VARCHAR2
1961 , p_doc_type IN VARCHAR2
1962 , p_doc_subtype IN VARCHAR2
1963 , p_doc_level IN VARCHAR2
1964 , p_doc_level_id IN NUMBER
1965 , p_use_enc_gt_flag IN VARCHAR2
1966 , p_override_funds IN VARCHAR2
1967 , p_use_gl_date IN VARCHAR2
1968 , p_override_date IN DATE
1969 , p_invoice_id IN NUMBER
1970 , x_po_return_code OUT NOCOPY VARCHAR2
1971 , x_online_report_id OUT NOCOPY NUMBER
1972 )
1973 IS
1974
1975 l_api_name CONSTANT VARCHAR2(30) := 'UNDO_FINAL_CLOSE';
1976 l_log_head CONSTANT VARCHAR2(100) := g_log_head || l_api_name;
1977 l_progress VARCHAR2(3) := '000';
1978 l_packet_id NUMBER;
1979
1980 BEGIN
1981
1982 IF g_debug_stmt THEN
1983 PO_DEBUG.debug_begin(l_log_head);
1984 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_type',p_doc_type);
1985 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_subtype',p_doc_subtype);
1986 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_level',p_doc_level);
1987 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_level_id',p_doc_level_id);
1988 PO_DEBUG.debug_var(l_log_head,l_progress,'p_use_enc_gt_flag',p_use_enc_gt_flag);
1989 PO_DEBUG.debug_var(l_log_head,l_progress,'p_override_funds',p_override_funds);
1990 PO_DEBUG.debug_var(l_log_head,l_progress,'p_use_gl_date',p_use_gl_date);
1991 PO_DEBUG.debug_var(l_log_head,l_progress,'p_override_date',p_override_date);
1992 PO_DEBUG.debug_var(l_log_head,l_progress,'p_invoice_id',p_invoice_id);
1993 END IF;
1994
1995 l_progress := '010';
1996
1997 -- Standard Start of API savepoint
1998 SAVEPOINT UNDO_FINAL_CLOSE_SP;
1999
2000 l_progress := '020';
2001
2002 x_po_return_code := NULL;
2003 x_online_report_id := NULL;
2004
2005 -- Call do_action
2006 do_action(
2007 x_return_status => x_return_status
2008 , p_action => g_action_UNDO_FINAL_CLOSE
2009 , p_check_only_flag => g_parameter_NO
2010 , p_cbc_flag => NULL
2011 , p_doc_type => p_doc_type
2012 , p_doc_subtype => p_doc_subtype
2013 , p_doc_level => p_doc_level
2014 , p_doc_level_id => p_doc_level_id
2015 , p_use_enc_gt_flag => p_use_enc_gt_flag
2016 , p_employee_id => FND_GLOBAL.USER_ID
2017 , p_override_funds => p_override_funds
2018 , p_prevent_partial_flag => NULL
2019 , p_use_gl_date => p_use_gl_date
2020 , p_override_date => p_override_date
2021 , p_validate_document => g_parameter_NO
2022 , p_use_force_mode => g_parameter_NO
2023 , p_invoice_id => p_invoice_id
2024 , p_ap_encumbered_amount => NULL
2025 , p_ap_cancelled_quantity => NULL
2026 , p_budget_acct_id => NULL
2027 , x_packet_id => l_packet_id
2028 , x_online_report_id => x_online_report_id
2029 , x_po_return_code => x_po_return_code
2030 );
2031
2032 l_progress := '100';
2033
2034 IF ( x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
2035 OR x_return_status IS NULL
2036 ) THEN
2037 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2038 END IF;
2039
2040 l_progress := '900';
2041
2042 IF g_debug_stmt THEN
2043 PO_DEBUG.debug_var(l_log_head,l_progress,'l_packet_id',l_packet_id);
2044 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
2045 PO_DEBUG.debug_var(l_log_head,l_progress,'x_po_return_code',x_po_return_code);
2046 PO_DEBUG.debug_var(l_log_head,l_progress,'x_online_report_id',x_online_report_id);
2047 PO_DEBUG.debug_end(l_log_head);
2048 END IF;
2049
2050 EXCEPTION
2051 WHEN OTHERS THEN
2052 -- bug 3454804 - robust exception handling
2053 BEGIN
2054 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS Start');
2055
2056 -- Do all of the common exception handling.
2057 handle_exception(l_api_name,l_progress,x_return_status,x_po_return_code);
2058
2059 PO_DEBUG.debug_unexp(l_log_head,l_progress,'handle_exception done');
2060
2061 IF g_debug_stmt THEN
2062 PO_DEBUG.debug_var(l_log_head,l_progress,'l_packet_id',l_packet_id);
2063 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
2064 PO_DEBUG.debug_var(l_log_head,l_progress,'x_po_return_code',x_po_return_code);
2065 PO_DEBUG.debug_var(l_log_head,l_progress,'x_online_report_id',x_online_report_id);
2066 END IF;
2067
2068 -- Rollback as per API standards.
2069 ROLLBACK TO UNDO_FINAL_CLOSE_SP;
2070
2071 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS End');
2072
2073 EXCEPTION
2074 WHEN OTHERS THEN
2075 NULL;
2076 END;
2077
2078 END undo_final_close;
2079
2080
2081 ------------------------------------------------------------------------------
2082 --Start of Comments
2083 --Name: do_cbc_yearend_reserve
2084 --Pre-reqs:
2085 -- None.
2086 --Modifies:
2087 -- Creates encumbrance entries in the gl_bc_packets table
2088 -- Adds distribution-specific transaction information into the
2089 -- po_online_report_text table
2090 --Locks:
2091 -- None.
2092 --Function:
2093 -- This procedure performs funds reservation on all eligible
2094 -- distributions of a document
2095 --Parameters:
2096 --IN:
2097 --p_doc_type
2098 -- Differentiates between the doc being a REQ, PA, PO, or RELEASE,
2099 -- which is used to identify the tables to look at (PO vs. Req)
2100 -- and the join conditions
2101 --p_doc_subtype
2102 -- Differentiates between the subtypes of documents
2103 -- REQ: NULL
2104 -- PO: STANDARD, PLANNED
2105 -- PA: CONTRACT, BLANKET
2106 -- RELEASE: SCHEDULED, BLANKET
2107 --p_doc_level
2108 -- Specifies the level of the document that is being checked:
2109 -- For Reserve, this should always be g_HEADER
2110 --p_doc_level_id
2111 -- The id corresponding to the doc level type:
2112 -- For Reserve, this should always be header_id (or release_id for Releases)
2113 --p_override_funds
2114 -- Indicates whether funds override capability can be used if needed, to make a
2115 -- transaction succeed.
2116 --p_employee_id
2117 -- Employee Id of the user taking the action
2118 --OUT:
2119 --x_return_status
2120 -- FND_API.G_RET_STS_SUCCESS if validation succeeds
2121 -- FND_API.G_RET_STS_ERROR if validation fails
2122 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
2123 --x_po_return_code
2124 -- Indicates whether PO is classifying this transaction as an
2125 -- Error/Warning/Partial Success/Success
2126 --x_online_report_id
2127 -- Unique id into po_online_report_text rows that store distribution specific
2128 -- reporting information for a specific encumbrance transaction
2129 --Testing:
2130 --
2131 --End of Comments
2132 -------------------------------------------------------------------------------
2133 PROCEDURE do_cbc_yearend_reserve(
2134 x_return_status OUT NOCOPY VARCHAR2
2135 , p_doc_type IN VARCHAR2
2136 , p_doc_subtype IN VARCHAR2
2137 , p_doc_level IN VARCHAR2
2138 , p_doc_level_id IN NUMBER
2139 , p_override_funds IN VARCHAR2
2140 , p_employee_id IN NUMBER
2141 , x_po_return_code OUT NOCOPY VARCHAR2
2142 , x_online_report_id OUT NOCOPY NUMBER
2143 )
2144 IS
2145
2146 l_api_name CONSTANT varchar2(30) := 'DO_CBC_YEAREND_RESERVE';
2147 l_log_head CONSTANT VARCHAR2(100) := g_log_head || l_api_name;
2148 l_progress VARCHAR2(3) := '000';
2149 l_packet_id NUMBER;
2150
2151 BEGIN
2152
2153 IF g_debug_stmt THEN
2154 PO_DEBUG.debug_begin(l_log_head);
2155 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_type',p_doc_type);
2156 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_subtype',p_doc_subtype);
2157 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_level',p_doc_level);
2158 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_level_id',p_doc_level_id);
2159 PO_DEBUG.debug_var(l_log_head,l_progress,'p_override_funds',p_override_funds);
2160 PO_DEBUG.debug_var(l_log_head,l_progress,'p_employee_id',p_employee_id);
2161 END IF;
2162
2163 l_progress := '010';
2164
2165 -- Standard Start of API savepoint
2166 SAVEPOINT DO_CBC_YEAREND_RESERVE_SP;
2167
2168 l_progress := '020';
2169
2170 x_po_return_code := NULL;
2171 x_online_report_id := NULL;
2172
2173 do_action(x_return_status => x_return_status,
2174 p_action => g_action_RESERVE,
2175 p_check_only_flag => g_parameter_NO,
2176 p_cbc_flag => g_parameter_YES,
2177 p_doc_type => p_doc_type,
2178 p_doc_subtype => p_doc_subtype,
2179 p_doc_level => p_doc_level,
2180 p_doc_level_id => p_doc_level_id,
2181 p_use_enc_gt_flag => g_parameter_NO,
2182 p_employee_id => p_employee_id,
2183 p_override_funds => p_override_funds,
2184 p_prevent_partial_flag => g_parameter_YES,
2185 p_use_gl_date => g_parameter_YES,
2186 p_override_date => NULL,
2187 p_validate_document => g_parameter_NO,
2188 p_use_force_mode => g_parameter_YES,
2189 x_packet_id => l_packet_id,
2190 x_po_return_code => x_po_return_code,
2191 x_online_report_id => x_online_report_id);
2192
2193 l_progress := '100';
2194
2195 IF ( x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
2196 OR x_return_status IS NULL
2197 ) THEN
2198 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2199 END IF;
2200
2201 l_progress := '900';
2202
2203 IF g_debug_stmt THEN
2204 PO_DEBUG.debug_var(l_log_head,l_progress,'l_packet_id',l_packet_id);
2205 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
2206 PO_DEBUG.debug_var(l_log_head,l_progress,'x_po_return_code',x_po_return_code);
2207 PO_DEBUG.debug_var(l_log_head,l_progress,'x_online_report_id',x_online_report_id);
2208 PO_DEBUG.debug_end(l_log_head);
2209 END IF;
2210
2211 EXCEPTION
2212 WHEN OTHERS THEN
2213 -- bug 3454804 - robust exception handling
2214 BEGIN
2215 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS Start');
2216
2217 -- Do all of the common exception handling.
2218 handle_exception(l_api_name,l_progress,x_return_status,x_po_return_code);
2219
2220 PO_DEBUG.debug_unexp(l_log_head,l_progress,'handle_exception done');
2221
2222 IF g_debug_stmt THEN
2223 PO_DEBUG.debug_var(l_log_head,l_progress,'l_packet_id',l_packet_id);
2224 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
2225 PO_DEBUG.debug_var(l_log_head,l_progress,'x_po_return_code',x_po_return_code);
2226 PO_DEBUG.debug_var(l_log_head,l_progress,'x_online_report_id',x_online_report_id);
2227 END IF;
2228
2229 -- Rollback as per API standards.
2230 ROLLBACK TO DO_CBC_YEAREND_RESERVE_SP;
2231
2232 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS End');
2233
2234 EXCEPTION
2235 WHEN OTHERS THEN
2236 NULL;
2237 END;
2238
2239 END do_cbc_yearend_reserve;
2240
2241
2242 ------------------------------------------------------------------------------
2243 --Start of Comments
2244 --Name: do_cbc_yearend_unreserve
2245 --Pre-reqs:
2246 -- None.
2247 --Modifies:
2248 -- Creates encumbrance entries in the gl_bc_packets table
2249 -- Adds distribution-specific transaction information into the
2250 -- po_online_report_text table
2251 --Locks:
2252 -- None.
2253 --Function:
2254 -- This procedure unreserves the encumbrance on all eligible distributions of
2255 -- the requested document or document doc level
2256 --Parameters:
2257 --IN:
2258 --p_doc_type
2259 -- Differentiates between the doc being a REQ, PA, PO, or RELEASE,
2260 -- which is used to identify the tables to look at (PO vs. Req)
2261 -- and the join conditions
2262 --p_doc_subtype
2263 -- Differentiates between the subtypes of documents
2264 -- REQ: NULL
2265 -- PO: STANDARD, PLANNED
2266 -- PA: CONTRACT, BLANKET
2267 -- RELEASE: SCHEDULED, BLANKET
2268 --p_doc_level
2269 -- Specifies the level of the document that is being checked:
2270 -- HEADER, LINE, SHIPMENT, DISTRIBUTION
2271 --p_doc_level_id
2272 -- The id corresponding to the doc level type:
2273 -- header_id/release_id, line_id, line_location_id, distribution_id
2274 --p_override_funds
2275 -- Indicates whether funds override capability can be used if needed, to make a
2276 -- transaction succeed.
2277 --p_use_gl_date
2278 -- Flag that specifies whether to always prefer using the existing distribution
2279 -- GL date instead of the override date, when possible
2280 --p_override_date
2281 -- Caller-specified date to use for distribution encumbrance date in GL entries
2282 --p_employee_id
2283 -- Employee Id of the user taking the action
2284 --OUT:
2285 --x_return_status
2286 -- FND_API.G_RET_STS_SUCCESS if validation succeeds
2287 -- FND_API.G_RET_STS_ERROR if validation fails
2288 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
2289 --x_po_return_code
2290 -- Indicates whether PO is classifying this transaction as an
2291 -- Error/Warning/Partial Success/Success
2292 --x_online_report_id
2293 -- Unique id into po_online_report_text rows that store distribution specific
2294 -- reporting information for a specific encumbrance transaction
2295 --Testing:
2296 --
2297 --End of Comments
2298 -------------------------------------------------------------------------------
2299 PROCEDURE do_cbc_yearend_unreserve(
2300 x_return_status OUT NOCOPY VARCHAR2
2301 , p_doc_type IN VARCHAR2
2302 , p_doc_subtype IN VARCHAR2
2303 , p_doc_level IN VARCHAR2
2304 , p_doc_level_id IN NUMBER
2305 , p_override_funds IN VARCHAR2
2306 , p_use_gl_date IN VARCHAR2
2307 , p_override_date IN DATE
2308 , p_employee_id IN NUMBER
2309 , x_po_return_code OUT NOCOPY VARCHAR2
2310 , x_online_report_id OUT NOCOPY NUMBER
2311 )
2312 IS
2313
2314 l_api_name CONSTANT varchar2(30) := 'DO_CBC_YEAREND_UNRESERVE';
2315 l_log_head CONSTANT VARCHAR2(100) := g_log_head || l_api_name;
2316 l_progress VARCHAR2(3) := '000';
2317 l_packet_id NUMBER;
2318
2319 BEGIN
2320
2321 IF g_debug_stmt THEN
2322 PO_DEBUG.debug_begin(l_log_head);
2323 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_type',p_doc_type);
2324 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_subtype',p_doc_subtype);
2325 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_level',p_doc_level);
2326 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_level_id',p_doc_level_id);
2327 PO_DEBUG.debug_var(l_log_head,l_progress,'p_override_funds',p_override_funds);
2328 PO_DEBUG.debug_var(l_log_head,l_progress,'p_use_gl_date',p_use_gl_date);
2329 PO_DEBUG.debug_var(l_log_head,l_progress,'p_override_date',p_override_date);
2330 PO_DEBUG.debug_var(l_log_head,l_progress,'p_employee_id',p_employee_id);
2331 END IF;
2332
2333 l_progress := '010';
2334
2335 -- Standard Start of API savepoint
2336 SAVEPOINT DO_CBC_YEAREND_UNRESERVE_SP;
2337
2338 l_progress := '020';
2339
2340 x_po_return_code := NULL;
2341 x_online_report_id := NULL;
2342
2343 do_action(x_return_status => x_return_status,
2344 p_action => g_action_UNRESERVE,
2345 p_check_only_flag => g_parameter_NO,
2346 p_cbc_flag => g_parameter_YES,
2347 p_doc_type => p_doc_type,
2348 p_doc_subtype => p_doc_subtype,
2349 p_doc_level => p_doc_level,
2350 p_doc_level_id => p_doc_level_id,
2351 p_use_enc_gt_flag => g_parameter_NO,
2352 p_employee_id => p_employee_id,
2353 p_override_funds => p_override_funds,
2354 p_prevent_partial_flag => NULL,
2355 p_use_gl_date => p_use_gl_date,
2356 p_override_date => p_override_date,
2357 p_validate_document => g_parameter_NO,
2358 p_use_force_mode => g_parameter_YES,
2359 x_packet_id => l_packet_id,
2360 x_po_return_code => x_po_return_code,
2361 x_online_report_id => x_online_report_id);
2362
2363 l_progress := '100';
2364
2365 IF ( x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
2366 OR x_return_status IS NULL
2367 ) THEN
2368 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2369 END IF;
2370
2371 l_progress := '900';
2372
2373 IF g_debug_stmt THEN
2374 PO_DEBUG.debug_var(l_log_head,l_progress,'l_packet_id',l_packet_id);
2375 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
2376 PO_DEBUG.debug_var(l_log_head,l_progress,'x_po_return_code',x_po_return_code);
2377 PO_DEBUG.debug_var(l_log_head,l_progress,'x_online_report_id',x_online_report_id);
2378 PO_DEBUG.debug_end(l_log_head);
2379 END IF;
2380
2381 EXCEPTION
2382 WHEN OTHERS THEN
2383 -- bug 3454804 - robust exception handling
2384 BEGIN
2385 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS Start');
2386
2387 -- Do all of the common exception handling.
2388 handle_exception(l_api_name,l_progress,x_return_status,x_po_return_code);
2389
2390 PO_DEBUG.debug_unexp(l_log_head,l_progress,'handle_exception done');
2391
2392 IF g_debug_stmt THEN
2393 PO_DEBUG.debug_var(l_log_head,l_progress,'l_packet_id',l_packet_id);
2394 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
2395 PO_DEBUG.debug_var(l_log_head,l_progress,'x_po_return_code',x_po_return_code);
2396 PO_DEBUG.debug_var(l_log_head,l_progress,'x_online_report_id',x_online_report_id);
2397 END IF;
2398
2399 -- Rollback as per API standards.
2400 ROLLBACK TO DO_CBC_YEAREND_UNRESERVE_SP;
2401
2402 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS End');
2403
2404 EXCEPTION
2405 WHEN OTHERS THEN
2406 NULL;
2407 END;
2408
2409 END do_cbc_yearend_unreserve;
2410
2411
2412 -------------------------------------------------------------------------------
2413 --Start of Comments
2414 --Name: reinstate_po_encumbrance
2415 --Pre-reqs:
2416 -- None.
2417 --Modifies:
2418 -- None.
2419 --Locks:
2420 -- None.
2421 --Function:
2422 -- It should created GL reversal entries if the distribution is
2423 -- either unreserved or the its shipment is Finally closed.
2424 -- Usage Notes: by AP: AP calls the group API twice, and hence, this
2425 -- private API twice. It is called once with p_tax_line_flag = g_parameter_YES
2426 -- and once with p_tax_line_flag = g_parameter_NO. If flag=NO, then AP passes
2427 -- p_encumbrance_amt as the amount AP reversed, excluding variances AND taxes.
2428 -- If flag=YES, then AP passes only the tax portion of what it reversed.
2429 -- On the PO side, we ignore the YES call, and handle taxes on our own
2430 -- for the NO call. This was done to make the backend simpler by allowing
2431 -- the tax logic to be consolidated. The nonrecoverable_tax_rate is
2432 -- determined and used as used for other documents within get_amounts.
2433 -- Refer to bug 3480949 for more info.
2434 --Parameters:
2435 --IN:
2436 --p_distribution_id
2437 -- po_distribution_id
2438 --p_invoice_id
2439 -- po_invoice_id
2440 --p_encumbrance_amt
2441 -- encumbrance amount in functional currency for which AP will reinstate the
2442 -- PO encumbrance on Invoice cancellation. AP should take care of the
2443 -- overbilled case and any variances.
2444 -- IF (p_encumbrance_amt >0) THEN
2445 -- Invoice Cancellation, PO API does Cr (AP is doing -Cr)
2446 -- ELSE
2447 -- Memo Cancellation, PO API does Dr (AP is doing -Dr)
2448 --p_qty_cancelled
2449 -- Invoice qty cancelled for the PO distribution. This should be PO UOM
2450 -- p_qty_cancelled is -ve for Invoice Cancellation
2451 -- +ve for Credit Memo Cancellation
2452 --p_budget_account_id
2453 -- Budget account id - account on which the AP does PO reversal
2454 --p_gl_date
2455 -- Valid open Date on which AP will reinstate PO encumbrance on Invoice
2456 -- cancellation. We want the Dr and Cr to go in the same period.
2457 --p_period_name
2458 -- period name
2459 --p_period_year
2460 -- period year
2461 --p_period_num
2462 -- period num
2463 --p_quarter_num
2464 -- quarter num
2465 --p_tax_line_flag -- Bug 3480949
2466 -- Set depending upon which values AP calls the API with.
2467 -- g_parameter_NO - the original amounts before tax applied
2468 -- g_parameter_YES - the tax on the original amounts only
2469 --OUT:
2470 --x_packet_id
2471 -- GL PACKET ID, if gl entries are made otherwise null
2472 --x_return_status
2473 -- FND_API.G_RET_STS_SUCCESS if validation succeeds
2474 -- FND_API.G_RET_STS_ERROR if validation fails
2475 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
2476 --Testing:
2477 --
2478 --End of Comments
2479 -------------------------------------------------------------------------------
2480 PROCEDURE reinstate_po_encumbrance(
2481 x_return_status OUT NOCOPY VARCHAR2,
2482 p_distribution_id IN NUMBER,
2483 p_invoice_id IN NUMBER,
2484 p_encumbrance_amt IN NUMBER,
2485 p_qty_cancelled IN NUMBER,
2486 p_budget_account_id IN NUMBER,
2487 p_gl_date IN DATE,
2488 p_period_name IN VARCHAR2,
2489 p_period_year IN VARCHAR2,
2490 p_period_num IN VARCHAR2,
2491 p_quarter_num IN VARCHAR2,
2492 p_tax_line_flag IN VARCHAR2, -- Bug 3480949
2493 x_packet_id OUT NOCOPY NUMBER
2494 )
2495 IS
2496
2497 l_api_name CONSTANT varchar2(30) := 'REINSTATE_PO_ENCUMBRANCE';
2498 l_log_head CONSTANT varchar2(240) := g_pkg_name || l_api_name;
2499 l_progress VARCHAR2(3) := '000';
2500 l_action VARCHAR2(30);
2501 l_doc_type PO_DOCUMENT_TYPES.document_type_code%TYPE;
2502 l_doc_subtype PO_HEADERS_ALL.type_lookup_code%TYPE;
2503 l_distribution_type PO_DISTRIBUTIONS_ALL.distribution_type%TYPE;
2504 l_po_return_code VARCHAR2(10);
2505 l_online_report_id PO_ONLINE_REPORT_TEXT.online_report_id%TYPE;
2506
2507 l_amt_based_line_flag VARCHAR2(1); -- Bug 3480949
2508
2509 BEGIN
2510
2511 IF g_debug_stmt THEN
2512 PO_DEBUG.debug_begin(l_log_head);
2513 PO_DEBUG.debug_var(l_log_head,l_progress,'p_distribution_id',
2514 p_distribution_id);
2515 PO_DEBUG.debug_var(l_log_head,l_progress,'p_invoice_id', p_invoice_id);
2516 PO_DEBUG.debug_var(l_log_head,l_progress,'p_encumbrance_amt',
2517 p_encumbrance_amt);
2518 PO_DEBUG.debug_var(l_log_head,l_progress,'p_qty_cancelled',
2519 p_qty_cancelled);
2520 PO_DEBUG.debug_var(l_log_head,l_progress,'p_budget_account_id',
2521 p_budget_account_id);
2522 PO_DEBUG.debug_var(l_log_head,l_progress,'p_gl_date', p_gl_date);
2523 PO_DEBUG.debug_var(l_log_head,l_progress,'p_period_name',
2524 p_period_name);
2525 PO_DEBUG.debug_var(l_log_head,l_progress,'p_period_year',
2526 p_period_year);
2527 PO_DEBUG.debug_var(l_log_head,l_progress,'p_period_num',
2528 p_period_num);
2529 PO_DEBUG.debug_var(l_log_head,l_progress,'p_quarter_num',
2530 p_quarter_num);
2531 -- Bug 3480949: log p_tax_line_flag
2532 PO_DEBUG.debug_var(l_log_head,l_progress,'p_tax_line_flag',
2533 p_tax_line_flag);
2534 END IF;
2535
2536 l_progress := '001';
2537
2538 SAVEPOINT REINSTATE_PO_ENCUMBRANCE_SP;
2539
2540 l_progress := '002';
2541
2542
2543 -- Start Bug 3480949: If p_tax_line_flag = g_parameter_YES,
2544 -- return success without doing anything.
2545
2546 IF (NVL(p_tax_line_flag, g_parameter_NO) = g_parameter_YES) THEN
2547 x_packet_id := NULL;
2548 x_return_status := FND_API.G_RET_STS_SUCCESS;
2549 RETURN;
2550 END IF;
2551
2552 l_progress := '005';
2553
2554
2555 --<Complex Work R12>: changed this query to use line location value
2556 --basis instead of line level order type lookup code.
2557 -- If a quantity based line loc is passed with quantity = 0, it is assumed
2558 -- that this is the second call by AP, in which the tax amount is being
2559 -- passed. Hence, we ignore the call, since the first call we would
2560 -- have added PO tax in the first call which passed the original amount.
2561
2562 -- For amounts based line locs, the only way for AP to tell us that the
2563 -- particular call is a tax line is to pass p_tax_line_flag = 'Y'.
2564 -- This will only occur after installing a one-off patch AP
2565 -- will provide on top of 11i.AP.M. Until then, taxed amount based line locs
2566 -- will over reinstate encumbrance. Untaxed amount based line locs should
2567 -- still work correctly.
2568
2569 SELECT DECODE( PLL.value_basis --<Complex Work R12>
2570 , g_order_type_FIXED_PRICE, 'Y'
2571 , g_order_type_RATE, 'Y'
2572 , 'N'
2573 )
2574 INTO l_amt_based_line_flag
2575 FROM PO_LINE_LOCATIONS_ALL PLL
2576 , PO_DISTRIBUTIONS_ALL POD
2577 WHERE POD.po_distribution_id = p_distribution_id
2578 AND PLL.line_location_id = POD.line_location_id
2579 ;
2580
2581 IF ((l_amt_based_line_flag = 'N') and (NVL(p_qty_cancelled, 0) = 0))
2582 THEN
2583 x_packet_id := NULL;
2584 x_return_status := FND_API.G_RET_STS_SUCCESS;
2585 RETURN;
2586 END IF;
2587
2588 l_progress := '008';
2589 -- End Bug 3480949
2590
2591
2592 IF p_encumbrance_amt > 0 THEN
2593 -- Invoice Cancellation
2594 l_progress:= '010';
2595 l_action := g_action_INVOICE_CANCEL;
2596
2597 ELSIF p_encumbrance_amt < 0 THEN
2598 -- Credit Memo Cancellation
2599 l_progress := '020';
2600 l_action := g_action_CR_MEMO_CANCEL;
2601
2602 ELSE -- p_encumbrance_amt == 0, we use p_qty_cancelled to decide
2603
2604 IF p_qty_cancelled < 0 THEN
2605 l_progress := '030';
2606 l_action := g_action_INVOICE_CANCEL;
2607
2608 ELSIF p_qty_cancelled > 0 THEN
2609 l_progress := '040';
2610 l_action := g_action_CR_MEMO_CANCEL;
2611
2612 ELSE -- p_qty_cancelled = 0, No entries should be created
2613 l_progress := '050';
2614
2615 x_packet_id := NULL;
2616 x_return_status := FND_API.G_RET_STS_SUCCESS;
2617
2618 return;
2619 END IF;
2620
2621 END IF;
2622
2623 l_progress := '060';
2624 IF g_debug_stmt THEN
2625 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Action type: ' || l_action);
2626 END IF;
2627
2628
2629 -- Get the distribution_type
2630 SELECT POD.distribution_type
2631 INTO l_distribution_type
2632 FROM PO_DISTRIBUTIONS_ALL POD
2633 WHERE POD.po_distribution_id = p_distribution_id;
2634
2635 l_progress := '070';
2636
2637 -- Convert the distribution type into document type and subtype.
2638 PO_ENCUMBRANCE_PREPROCESSING.derive_doc_types_from_dist(
2639 p_distribution_type => l_distribution_type
2640 , x_doc_type => l_doc_type
2641 , x_doc_subtype => l_doc_subtype
2642 );
2643
2644 l_progress := '080';
2645 IF g_debug_stmt THEN
2646 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Document type: ' || l_doc_type ||
2647 ', Document subtype: ' || l_doc_subtype);
2648 END IF;
2649
2650 do_action(x_return_status => x_return_status,
2651 p_action => l_action,
2652 p_check_only_flag => g_parameter_NO,
2653 p_cbc_flag => g_parameter_NO,
2654 p_doc_type => l_doc_type,
2655 p_doc_subtype => l_doc_subtype,
2656 p_doc_level => g_doc_level_DISTRIBUTION,
2657 p_doc_level_id => p_distribution_id,
2658 p_use_enc_gt_flag => g_parameter_NO,
2659 p_employee_id => NULL,
2660 p_override_funds => g_parameter_NO,
2661 p_prevent_partial_flag => NULL,
2662 p_use_gl_date => g_parameter_USE_PROFILE,--bug#5462791
2663 p_override_date => p_gl_date,
2664 p_validate_document => g_parameter_NO,
2665 p_use_force_mode => g_parameter_NO,
2666 p_invoice_id => p_invoice_id,
2667 p_ap_encumbered_amount => abs(p_encumbrance_amt),
2668 p_ap_cancelled_quantity => abs(p_qty_cancelled),
2669 p_budget_acct_id => p_budget_account_id,
2670 x_packet_id => x_packet_id,
2671 x_po_return_code => l_po_return_code,
2672 x_online_report_id => l_online_report_id
2673 );
2674
2675 l_progress := '090';
2676
2677 IF ( x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
2678 OR x_return_status IS NULL
2679 ) THEN
2680 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2681 END IF;
2682
2683 l_progress := '100';
2684
2685 IF g_debug_stmt THEN
2686 PO_DEBUG.debug_var(l_log_head,l_progress,'x_packet_id',
2687 x_packet_id);
2688 PO_DEBUG.debug_var(l_log_head,l_progress,'l_online_report_id',
2689 l_online_report_id);
2690 PO_DEBUG.debug_var(l_log_head,l_progress,'l_po_return_code',
2691 l_po_return_code);
2692 PO_DEBUG.debug_end(l_log_head);
2693 END IF;
2694
2695
2696 EXCEPTION
2697 WHEN OTHERS THEN
2698 -- bug 3454804 - robust exception handling
2699 BEGIN
2700 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS Start');
2701
2702 -- Do all of the common exception handling.
2703 handle_exception(l_api_name,l_progress,x_return_status,l_po_return_code);
2704
2705 PO_DEBUG.debug_unexp(l_log_head,l_progress,'handle_exception done');
2706
2707 IF g_debug_stmt THEN
2708 PO_DEBUG.debug_var(l_log_head,l_progress,'x_packet_id',x_packet_id);
2709 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
2710 PO_DEBUG.debug_var(l_log_head,l_progress,'l_po_return_code',l_po_return_code);
2711 PO_DEBUG.debug_var(l_log_head,l_progress,'l_online_report_id',l_online_report_id);
2712 END IF;
2713
2714 -- Rollback as per API standards.
2715 ROLLBACK TO REINSTATE_PO_ENCUMBRANCE_SP;
2716
2717 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS End');
2718
2719 EXCEPTION
2720 WHEN OTHERS THEN
2721 NULL;
2722 END;
2723
2724 END reinstate_po_encumbrance;
2725
2726
2727 ------------------------------------------------------------------------------
2728 --Start of Comments
2729 --Name: is_agreement_encumbered
2730 --Pre-reqs:
2731 -- The org context must be set before calling this procedure, as it
2732 -- relies on the org-striped view po_headers
2733 --Modifies:
2734 -- N/A.
2735 --Locks:
2736 -- None.
2737 --Function:
2738 -- This procedure is an API that informs callers whether a particular agreement
2739 -- is/can be encumbered, based on its header level encumbrance_required flag,
2740 -- and whether the agreement is in the same OU as the caller.
2741 -- The output table contains results in the same ordering as the input table.
2742 --Parameters:
2743 --IN:
2744 --p_agreement_ids_tbl
2745 -- A table of po_header_ids corresponding to the PAs that we are checking
2746 -- the encumbered state of.
2747 --OUT:
2748 --x_return_status
2749 -- FND_API.G_RET_STS_SUCCESS if validation succeeds
2750 -- FND_API.G_RET_STS_ERROR if validation fails
2751 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
2752 --x_agreement_encumbered_tbl
2753 -- A table of Y/N results indicating whether each PA is encumbered or not.
2754 -- Y = the given PA is/can be encumbered.
2755 -- N = the PA is not eligible for encumbrance
2756 --Testing:
2757 --
2758 --End of Comments
2759 -------------------------------------------------------------------------------
2760 PROCEDURE is_agreement_encumbered(
2761 x_return_status OUT NOCOPY VARCHAR2
2762 , p_agreement_id_tbl IN PO_TBL_NUMBER
2763 , x_agreement_encumbered_tbl OUT NOCOPY PO_TBL_VARCHAR1
2764 )
2765 IS
2766
2767 l_api_name CONSTANT varchar2(30) := 'IS_AGREEMENT_ENCUMBERED';
2768 l_log_head CONSTANT VARCHAR2(100) := g_log_head || l_api_name;
2769 l_progress VARCHAR2(3) := '000';
2770 l_procedure_id NUMBER;
2771
2772 l_sequence_tbl PO_TBL_NUMBER; -- bug3546894
2773
2774 BEGIN
2775
2776 IF g_debug_stmt THEN
2777 PO_DEBUG.debug_begin(l_log_head);
2778 PO_DEBUG.debug_var(l_log_head,l_progress,'p_agreement_id_tbl',p_agreement_id_tbl);
2779 END IF;
2780
2781 l_progress := '001';
2782
2783 -- Standard Start of API savepoint
2784 SAVEPOINT IS_AGREEMENT_ENCUMBERED_SP;
2785
2786 l_progress := '002';
2787
2788 l_procedure_id := PO_CORE_S.get_session_gt_nextval();
2789
2790 l_progress := '010';
2791
2792 IF g_debug_stmt THEN
2793 PO_DEBUG.debug_var(l_log_head,l_progress,'l_procedure_id',l_procedure_id);
2794 END IF;
2795
2796 --SQL What: Populate the global temp table with the po_header_ids from the
2797 -- input table (and note the ordering)
2798 --SQL Why: We can then do SQL operations in bulk on the input ids, while
2799 -- preserving the guarantee that the output values are ordered
2800 -- corresponding to the order of the input values
2801
2802 -- bug3546894
2803 -- Use an indexed column to store the sequence in PO_SESSION_GT. We will also
2804 -- save the same in PL/SQL table l_sequence_tbl
2805 FORALL i IN 1 .. p_agreement_id_tbl.COUNT
2806 INSERT INTO PO_SESSION_GT TEMP
2807 ( key
2808 , num1
2809 , index_num1
2810 )
2811 VALUES
2812 ( l_procedure_id
2813 , p_agreement_id_tbl(i)
2814 , PO_SESSION_GT_S.NEXTVAL
2815 )
2816 RETURNING TEMP.index_num1
2817 BULK COLLECT INTO l_sequence_tbl
2818 ;
2819
2820 l_progress := '020';
2821
2822 IF g_debug_stmt THEN
2823 PO_DEBUG.debug_stmt(l_log_head,l_progress,
2824 'Bulk Insertion into Session Table success');
2825 END IF;
2826
2827 --SQL What: Check whether the input PAs are/can be encumbered in the current OU.
2828 -- We then populate this result in bulk into the global temp table.
2829 --SQL Why: Callers that are sourcing a Requisition against a BPA need to
2830 -- know whether the BPA is encumbered or not, because it determines
2831 -- the setting of the Reqs prevent_encumbrance_flag.
2832
2833 UPDATE PO_SESSION_GT TEMP
2834 SET char1 =
2835 (SELECT POH.encumbrance_required_flag
2836 FROM PO_HEADERS POH
2837 WHERE POH.po_header_id = TEMP.num1
2838 )
2839 WHERE TEMP.key = l_procedure_id
2840 ;
2841
2842 l_progress := '030';
2843
2844 IF g_debug_stmt THEN
2845 PO_DEBUG.debug_stmt(l_log_head,l_progress,
2846 'Encumbrance information updated successfully');
2847
2848 SELECT rowid BULK COLLECT INTO PO_DEBUG.g_rowid_tbl
2849 FROM PO_SESSION_GT WHERE key = l_procedure_id
2850 ;
2851
2852 PO_DEBUG.debug_table(l_log_head,l_progress,'PO_SESSION_GT'
2853 , PO_DEBUG.g_rowid_tbl
2854 , po_tbl_varchar30('key','num1','char1','num2')
2855 );
2856
2857 END IF;
2858
2859 --SQL What: Retrieve the result of the previous SQL query into a PL/SQL table
2860 --SQL Why: The callers will get the result information from this table. Each
2861 -- line in this output table will correspond to a line from the input
2862 -- table, with the ordering preserved.
2863
2864 -- bug3546894
2865 -- we want to obtain the result in the same order as we insert into
2866 -- PO_SESSION_GT in the first place.
2867 FORALL i IN 1..l_sequence_tbl.COUNT
2868 UPDATE PO_SESSION_GT
2869 SET char1 = NVL(char1,'N')
2870 WHERE key = l_procedure_id
2871 AND index_num1 = l_sequence_tbl(i)
2872 RETURNING char1
2873 BULK COLLECT INTO x_agreement_encumbered_tbl
2874 ;
2875
2876 l_progress := '040';
2877
2878 x_return_status := fnd_api.g_ret_sts_success;
2879
2880 l_progress := '900';
2881
2882 IF g_debug_stmt THEN
2883 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
2884 PO_DEBUG.debug_var(l_log_head,l_progress,'x_agreement_encumbered_tbl',x_agreement_encumbered_tbl);
2885 PO_DEBUG.debug_end(l_log_head);
2886 END IF;
2887
2888 EXCEPTION
2889 WHEN OTHERS THEN
2890 BEGIN
2891 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS Start');
2892 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2893 IF g_debug_stmt THEN
2894 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
2895 PO_DEBUG.debug_var(l_log_head,l_progress,'x_agreement_encumbered_tbl',x_agreement_encumbered_tbl);
2896 END IF;
2897 ROLLBACK TO IS_AGREEMENT_ENCUMBERED_SP;
2898 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
2899 FND_MSG_PUB.add_exc_msg(G_PKG_NAME, l_api_name);
2900 END IF;
2901 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS End');
2902 EXCEPTION
2903 WHEN OTHERS THEN
2904 NULL;
2905 END;
2906
2907 END is_agreement_encumbered;
2908
2909
2910 ------------------------------------------------------------------------------
2911 --Start of Comments
2912 --Name: is_agreement_encumbered
2913 --Pre-reqs:
2914 -- org_context is set
2915 --Modifies:
2916 -- N/A.
2917 --Locks:
2918 -- None.
2919 --Function:
2920 -- This procedure is an API that informs callers whether a particular agreement
2921 -- is/can be encumbered, based on its header level encumbrance_required flag.
2922 -- The output table contains results in the same ordering as the input table.
2923 --Parameters:
2924 --IN:
2925 --p_agreement_id
2926 -- A po_header_id corresponding to the PA that we are checking
2927 -- the encumbered state of.
2928 --OUT:
2929 --x_return_status
2930 -- FND_API.G_RET_STS_SUCCESS if validation succeeds
2931 -- FND_API.G_RET_STS_ERROR if validation fails
2932 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
2933 --x_agreement_encumbered_flag
2934 -- Indicates whether the PA is encumbered or not.
2935 -- Y = the given PA is/can be encumbered.
2936 -- N = the PA is not eligible for encumbrance
2937 --Testing:
2938 --
2939 --End of Comments
2940 -------------------------------------------------------------------------------
2941 PROCEDURE is_agreement_encumbered(
2942 x_return_status OUT NOCOPY VARCHAR2
2943 , p_agreement_id IN NUMBER
2944 , x_agreement_encumbered_flag OUT NOCOPY VARCHAR2
2945 )
2946 IS
2947
2948 l_api_name CONSTANT varchar2(30) := 'IS_AGREEMENT_ENCUMBERED';
2949 l_log_head CONSTANT VARCHAR2(100) := g_log_head || l_api_name;
2950 l_progress VARCHAR2(3) := '000';
2951 l_agreement_encumbered_tbl po_tbl_varchar1;
2952
2953 BEGIN
2954 -- Standard Start of API savepoint
2955 SAVEPOINT IS_AGREEMENT_ENCUMBERED_SP;
2956
2957 l_progress := '001';
2958
2959 IF g_debug_stmt THEN
2960 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2961 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,g_log_head || '.'||l_api_name||'.'
2962 || l_progress,'Invoked');
2963 END IF;
2964 END IF;
2965
2966 l_progress := '010';
2967
2968 IF g_debug_stmt THEN
2969 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2970 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,g_log_head || '.'||l_api_name||'.'
2971 || l_progress,'Calling bulk is_agreement_encumbered');
2972 END IF;
2973 END IF;
2974
2975 is_agreement_encumbered(
2976 x_return_status => x_return_status
2977 , p_agreement_id_tbl => po_tbl_number(p_agreement_id)
2978 , x_agreement_encumbered_tbl => l_agreement_encumbered_tbl
2979 );
2980
2981 l_progress := '020';
2982
2983 IF g_debug_stmt THEN
2984 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2985 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,g_log_head || '.'||l_api_name||'.'
2986 || l_progress,'Call to bulk is_agreement_encumbered complete');
2987 END IF;
2988 END IF;
2989
2990 x_agreement_encumbered_flag := l_agreement_encumbered_tbl(1);
2991
2992 IF g_debug_stmt THEN
2993 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2994 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,g_log_head || '.'||l_api_name||'.'
2995 || l_progress,'End of is_agreement_encumbered');
2996 END IF;
2997 END IF;
2998
2999 EXCEPTION
3000 WHEN OTHERS THEN
3001 BEGIN
3002 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS Start');
3003 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3004 ROLLBACK TO IS_AGREEMENT_ENCUMBERED_SP;
3005 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
3006 FND_MSG_PUB.add_exc_msg(G_PKG_NAME, l_api_name);
3007 END IF;
3008 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS End');
3009 EXCEPTION
3010 WHEN OTHERS THEN
3011 NULL;
3012 END;
3013
3014 END is_agreement_encumbered;
3015
3016
3017 ------------------------------------------------------------------------------
3018 --Start of Comments
3019 --Name: do_action
3020 --Pre-reqs:
3021 -- org context is set
3022 --Modifies:
3023 -- GL_BC_PACKETS
3024 -- PO_ONLINE_REPORT_TEXT
3025 -- PO_ENC_DISTRIBUTIONS_GT
3026 --Locks:
3027 -- None.
3028 --Function:
3029 -- This procedure performs funds action on all eligible distributions of
3030 -- a document. Creates encumbrance entries in the gl_bc_packets table.
3031 -- Adds distribution-specific transaction information into the
3032 -- po_online_report_text table.
3033 --Parameters:
3034 --IN:
3035 --p_action
3036 -- The encumbrance action being performed.
3037 --p_check_only_flag
3038 -- Indicates whether the calling action is check_<> or do_<> action
3039 -- (i.e. simple funds check versus an actual encumbrance-modifying action)
3040 --p_cbc_flag
3041 -- This parameter is only set to Y if the action is one of the CBC Year-End
3042 -- processes. If this is Y, p_action is either Reserve or Unreserve
3043 --p_doc_type
3044 -- Differentiates between the doc being a REQ, PA, PO, or RELEASE,
3045 -- which is used to identify the tables to look at (PO vs. Req)
3046 -- and the join conditions
3047 --p_doc_subtype
3048 -- Differentiates between the subtypes of documents
3049 -- REQ: NULL
3050 -- PO: STANDARD, PLANNED
3051 -- PA: CONTRACT, BLANKET
3052 -- RELEASE: SCHEDULED, BLANKET
3053 --p_doc_level
3054 -- Specifies the level of the document that is being checked:
3055 -- HEADER, LINE, SHIPMENT, DISTRIBUTION
3056 --p_doc_level_id
3057 -- The id corresponding to the doc level type:
3058 -- header_id/release_id, line_id, line_location_id, distribution_id
3059 --p_use_enc_gt_flag
3060 -- Set to g_parameter_NO if calling with p_doc_level_id
3061 -- Else if using the inteface table g_parameter_YES
3062 --p_validate_document
3063 -- Indicates whether to perform general document state/submission checks
3064 -- If 'Y', then encumbrance code will make calls to check doc correctness
3065 -- If 'N', then the caller has already done these validations.
3066 --p_override_funds
3067 -- Indicates whether funds override capability can be used if needed, to
3068 -- make a transaction succeed.
3069 --p_use_gl_date
3070 -- Flag that specifies whether to always prefer using the existing
3071 -- distribution GL date instead of the override date, when possible
3072 --p_override_date
3073 -- Caller-specified date to use for distribution encumbrance date in
3074 -- GL entries
3075 --p_use_force_mode
3076 -- Y/N flag indicates whether to send transaction to GL in GL Force mode
3077 --p_employee_id
3078 -- Employee Id of the user taking the action
3079 --OUT:
3080 --x_return_status
3081 -- FND_API.G_RET_STS_SUCCESS if validation succeeds
3082 -- FND_API.G_RET_STS_ERROR if validation fails
3083 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
3084 --x_po_return_code
3085 -- Indicates whether PO is classifying this transaction as an
3086 -- Error/Warning/Partial Success/Success
3087 --x_online_report_id
3088 -- Unique id into po_online_report_text rows that store distribution specific
3089 -- reporting information for a specific encumbrance transaction
3090 --Testing:
3091 --
3092 --End of Comments
3093 -------------------------------------------------------------------------------
3094 PROCEDURE do_action(
3095 x_return_status OUT NOCOPY VARCHAR2
3096 , p_action IN VARCHAR2
3097 , p_check_only_flag IN VARCHAR2
3098 , p_cbc_flag IN VARCHAR2
3099 , p_doc_type IN VARCHAR2
3100 , p_doc_subtype IN VARCHAR2
3101 , p_doc_level IN VARCHAR2
3102 , p_doc_level_id IN NUMBER
3103 , p_use_enc_gt_flag IN VARCHAR2
3104 , p_employee_id IN NUMBER
3105 , p_override_funds IN VARCHAR2
3106 , p_prevent_partial_flag IN VARCHAR2
3107 , p_use_gl_date IN VARCHAR2
3108 , p_override_date IN DATE
3109 , p_validate_document IN VARCHAR2
3110 , p_use_force_mode IN VARCHAR2
3111 -- The following params are only revelant to
3112 -- the invoice cancel API (INs are default NULL)
3113 , p_invoice_id IN NUMBER
3114 , p_ap_encumbered_amount IN NUMBER
3115 , p_ap_cancelled_quantity IN NUMBER
3116 , p_budget_acct_id IN NUMBER
3117 , x_packet_id OUT NOCOPY NUMBER
3118 -- End of invoice-cancel specific parameters
3119 , x_online_report_id OUT NOCOPY NUMBER
3120 , x_po_return_code OUT NOCOPY VARCHAR2)
3121 IS
3122
3123 l_api_name CONSTANT varchar2(30) := 'DO_ACTION';
3124 l_log_head CONSTANT varchar2(100) := g_log_head || l_api_name;
3125 l_progress VARCHAR2(3) := '000';
3126
3127 l_currency_code_func GL_SETS_OF_BOOKS.currency_code%TYPE;
3128 l_set_of_books_id GL_SETS_OF_BOOKS.set_of_books_id%TYPE;
3129 l_req_encumb_type FINANCIALS_SYSTEM_PARAMETERS.req_encumbrance_type_id%TYPE;
3130 l_po_encumb_type FINANCIALS_SYSTEM_PARAMETERS.purch_encumbrance_type_id%TYPE;
3131
3132 l_override_funds VARCHAR2(1);
3133 l_use_gl_date VARCHAR2(1);
3134 l_partial_flag VARCHAR2(1);
3135 l_cbc_flag VARCHAR2(1);
3136 l_get_backing_docs_flag VARCHAR2(1);
3137
3138 --GL call variables
3139 l_gl_packet_status GL_BC_PACKETS.status_code%TYPE; --used in insert_packet
3140 l_gl_call_mode VARCHAR2(1); --used in execute_gl_call
3141 l_packet_id NUMBER := NULL; -- Bug 3218669
3142 l_gl_return_code VARCHAR2(1);
3143
3144 l_user_id NUMBER;
3145 l_user_resp_id NUMBER;
3146 l_document_id NUMBER;
3147
3148 l_validation_successful_flag VARCHAR2(1);
3149 l_sub_check_report_id NUMBER;
3150 l_period_exception_flag VARCHAR2(1) := 'N';
3151
3152 l_po_return_msg_name FND_NEW_MESSAGES.message_name%TYPE;
3153 l_entity_token PO_LOOKUP_CODES.displayed_field%TYPE;
3154 l_exc_code NUMBER;
3155 l_exc_message_text FND_NEW_MESSAGES.message_text%TYPE;
3156
3157 l_dist_count NUMBER;
3158
3159 -- Bug 3280496
3160 l_do_state_check VARCHAR2(1);
3161
3162 -- bug 3518116
3163 l_action VARCHAR2(30);
3164 l_doc_id_tbl po_tbl_number;
3165 --<eTax Integration R12>
3166 l_po_document_id PO_HEADERS_ALL.PO_HEADER_ID%type;
3167 l_req_document_id PO_REQUISITION_HEADERS_ALL.REQUISITION_HEADER_ID%type;
3168 l_rel_document_id PO_HEADERS_ALL.PO_HEADER_ID%type;
3169 l_return_status VARCHAR2(1);
3170 --Bug 11665915 Custom hook to validate funds in an external financial system - variables
3171
3172 l_ext_gl_return_code varchar2(1);
3173
3174
3175 /* bug#6069405 start*/
3176
3177 l_distribution_type_tbl po_tbl_varchar30;
3178 l_distribution_id_tbl PO_TBL_NUMBER;
3179 l_nonrecoverable_tax_tbl PO_TBL_NUMBER;
3180 l_distribution_cnt NUMBER;
3181 /*12405805*/
3182 l_old_pkt_id NUMBER;
3183 l_exec_gl_call_ret_code VARCHAR2(1);
3184 /*12405805*/
3185
3186 /* bug#6069405 end*/
3187
3188 l_uom_conversion_exc_flag VARCHAR2(1); --<Bug 14152238 : Variable to check for uom conversion error >--
3189
3190 --PAR Project Changes Begin.
3191 --Need to skip Submit Check when its a dummy req.
3192 l_is_par_dummy_req VARCHAR2(1) := 'N';
3193 --PAR Project Changes End.
3194 BEGIN
3195
3196 IF g_debug_stmt THEN
3197 PO_DEBUG.debug_begin(l_log_head);
3198 PO_DEBUG.debug_var(l_log_head,l_progress,'p_action', p_action);
3199 PO_DEBUG.debug_var(l_log_head,l_progress,'p_check_only_flag',
3200 p_check_only_flag);
3201 PO_DEBUG.debug_var(l_log_head,l_progress,'p_cbc_flag', p_cbc_flag);
3202 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_type', p_doc_type);
3203 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_subtype', p_doc_subtype);
3204 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_level', p_doc_level);
3205 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_level_id', p_doc_level_id);
3206 PO_DEBUG.debug_var(l_log_head,l_progress,'p_use_enc_gt_flag',
3207 p_use_enc_gt_flag);
3208 PO_DEBUG.debug_var(l_log_head,l_progress,'p_employee_id', p_employee_id);
3209 PO_DEBUG.debug_var(l_log_head,l_progress,'p_override_funds',
3210 p_override_funds);
3211 PO_DEBUG.debug_var(l_log_head,l_progress,'p_prevent_partial_flag',
3212 p_prevent_partial_flag);
3213 PO_DEBUG.debug_var(l_log_head,l_progress,'p_use_gl_date', p_use_gl_date);
3214 PO_DEBUG.debug_var(l_log_head,l_progress,'p_override_date',
3215 p_override_date);
3216 PO_DEBUG.debug_var(l_log_head,l_progress,'p_validate_document',
3217 p_validate_document);
3218 PO_DEBUG.debug_var(l_log_head,l_progress,'p_use_force_mode',
3219 p_use_force_mode);
3220 END IF;
3221
3222 x_po_return_code := NULL;
3223 x_online_report_id := NULL;
3224 l_uom_conversion_exc_flag := 'N'; --<Bug 14152238 : Intialization of the uom conversion exception flag >--
3225
3226 l_progress := '010';
3227
3228 --
3229 -- Get the Financial set-up information
3230 --
3231 SELECT
3232 GL_SOB.currency_code
3233 , FSP.set_of_books_id
3234 , FSP.req_encumbrance_type_id
3235 , FSP.purch_encumbrance_type_id
3236 INTO
3237 l_currency_code_func
3238 , l_set_of_books_id
3239 , l_req_encumb_type
3240 , l_po_encumb_type
3241 FROM
3242 FINANCIALS_SYSTEM_PARAMETERS FSP
3243 , GL_SETS_OF_BOOKS GL_SOB
3244 WHERE
3245 GL_SOB.set_of_books_id = FSP.set_of_books_id
3246 ;
3247
3248 l_progress := '020';
3249 IF g_debug_stmt THEN
3250 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Retrieved financial set-up');
3251 END IF;
3252
3253 --
3254 --Determine other Encumbrance-related settings
3255 --
3256
3257 -- bug 3518116
3258 -- For most subroutines, Req Split is identical to Adjust.
3259 -- use l_action for all subroutines calls (get_dists/get_amts etc)
3260 -- except encumbrance validations and document update/post-processing
3261 IF (p_action = g_action_REQ_SPLIT) THEN
3262 l_action := g_action_ADJUST;
3263 ELSE
3264 l_action := p_action;
3265 END IF;
3266
3267 l_progress := '021';
3268
3269 IF (p_check_only_flag = 'Y') THEN
3270 l_gl_packet_status := 'C';
3271 ELSE
3272 l_gl_packet_status := 'P';
3273 END IF;
3274
3275 IF (p_use_force_mode = 'Y') THEN
3276 l_gl_call_mode := 'F';
3277 ELSIF (p_check_only_flag = g_parameter_YES) THEN
3278 l_gl_call_mode := 'C';
3279 --<bug#5523323 START>
3280 --For all cases other than force mode/funds check we would make the
3281 --gl_call with 'R' (RESERVE) as the gl mode.
3282 ELSE
3283 l_gl_call_mode := 'R';
3284 END IF;
3285 --<bug#5523323 END>
3286
3287 l_progress := '022';
3288
3289 IF (p_override_funds = g_parameter_USE_PROFILE) THEN
3290 FND_PROFILE.get('PO_REQAPPR_OVERRIDE_FUNDS', l_override_funds);
3291 ELSE
3292 l_override_funds := p_override_funds;
3293 END IF;
3294
3295 l_override_funds := nvl(l_override_funds, 'N');
3296
3297 l_progress := '024';
3298
3299 IF (p_use_gl_date = g_parameter_USE_PROFILE) THEN
3300 FND_PROFILE.get('PO_GL_DATE', l_use_gl_date);
3301 ELSE
3302 l_use_gl_date := p_use_gl_date;
3303 END IF;
3304
3305 l_use_gl_date := nvl(l_use_gl_date, 'N');
3306
3307 l_progress := '026';
3308
3309 IF p_action = g_action_RESERVE THEN
3310 --<eTax integration R12 Start>
3311 l_po_document_id :=NULL;
3312 l_req_document_id :=NULL;
3313 l_rel_document_id :=NULL;
3314
3315 l_progress := '027';
3316
3317 IF g_debug_stmt THEN
3318 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3319 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,g_log_head || '.'||
3320 l_api_name||'.' || l_progress,
3321 'Reserve : Calculate tax if the current one is not correct');
3322 END IF;
3323 END IF;
3324
3325 /*Derving the req and po header id that is required for the tax API*/
3326 IF p_doc_type ='PO' OR p_doc_type='PA' THEN
3327 IF p_doc_level ='HEADER' THEN
3328 l_po_document_id := p_doc_level_id;
3329 ELSIF p_doc_level='LINE' THEN
3330 SELECT po_header_id into l_po_document_id FROM po_lines_all
3331 WHERE po_line_id = p_doc_level_id ;
3332 ELSIF p_doc_level='SHIPMENT' THEN
3333 SELECT po_header_id into l_po_document_id FROM po_line_locations_all
3334 WHERE line_location_id = p_doc_level_id ;
3335 ELSIF p_doc_level='DISTRIBUTION' THEN
3336 SELECT po_header_id into l_po_document_id FROM po_distributions_all
3337 WHERE po_distribution_id = p_doc_level_id ;
3338 END IF;
3339 END IF;
3340
3341 IF p_doc_type ='RELEASE' THEN
3342 IF p_doc_level='SHIPMENT' THEN
3343 SELECT po_header_id into l_rel_document_id FROM po_line_locations_all
3344 WHERE line_location_id = p_doc_level_id ;
3345 ELSIF p_doc_level='DISTRIBUTION' THEN
3346 SELECT po_header_id into l_rel_document_id FROM po_distributions_all
3347 WHERE po_distribution_id = p_doc_level_id ;
3348 END IF;
3349 END IF;
3350
3351 --CLM R4: PAR Project Changes Begin
3352 IF p_doc_type = g_doc_type_REQUISITION THEN
3353
3354 IF p_doc_level ='HEADER' THEN
3355 l_req_document_id := p_doc_level_id;
3356
3357 SELECT Nvl(par_flag,'N')
3358 INTO l_is_par_dummy_req
3359 FROM po_requisition_headers_all
3360 WHERE REQUISITION_HEADER_ID = l_req_document_id;
3361
3362 ELSIF p_doc_level='LINE' THEN
3363 SELECT requisition_header_id into l_req_document_id
3364 FROM po_requisition_lines_all
3365 WHERE requisition_line_id = p_doc_level_id ;
3366
3367
3368 SELECT Nvl(par_flag,'N')
3369 INTO l_is_par_dummy_req
3370 FROM po_requisition_headers_all
3371 WHERE REQUISITION_HEADER_ID = l_req_document_id;
3372
3373 ELSIF p_doc_level='DISTRIBUTION' THEN
3374 SELECT requisition_header_id into l_req_document_id
3375 FROM PO_REQUISITION_LINES_ALL POL, PO_REQ_DISTRIBUTIONS_ALL POD
3376 WHERE POL.REQUISITION_LINE_ID = POD.REQUISITION_LINE_ID
3377 AND POD.DISTRIBUTION_ID =p_doc_level_id;
3378
3379
3380 SELECT Nvl(par_flag,'N')
3381 INTO l_is_par_dummy_req
3382 FROM po_requisition_headers_all
3383 WHERE REQUISITION_HEADER_ID = l_req_document_id;
3384
3385 END IF;
3386 END IF;
3387
3388
3389
3390 IF po_tax_interface_pvt.calculate_tax_yes_no(
3391 p_po_header_id => l_po_document_id,
3392 p_po_release_id => l_rel_document_id,
3393 p_req_header_id => l_req_document_id) = 'Y'
3394 THEN
3395 IF g_debug_stmt THEN
3396 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3397 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,g_log_head || '.'||
3398 l_api_name||'.' || l_progress,
3399 'Req 010: Calcualte tax as the current one is not correct');
3400 END IF;
3401 END IF;
3402
3403 IF p_doc_type= g_doc_type_REQUISITION THEN
3404 PO_TAX_INTERFACE_PVT.calculate_tax_requisition(
3405 x_return_status => l_return_status,
3406 p_requisition_header_id => l_req_document_id,
3407 p_calling_program => g_action_RESERVE);
3408 ELSIF p_doc_type='PO' or p_doc_type='PA' or p_doc_type='RELEASE' THEN
3409 PO_TAX_INTERFACE_PVT.calculate_tax(
3410 p_po_header_id => l_po_document_id,
3411 p_po_release_id => l_rel_document_id,
3412 p_calling_program =>g_action_RESERVE,
3413 x_return_status => l_return_status);
3414 END IF;
3415
3416 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3417 IF g_debug_stmt THEN
3418 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3419 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,g_log_head || '.'||
3420 l_api_name||'.' || l_progress,
3421 ' Calculate tax has errored out');
3422 END IF;
3423 END IF;
3424 --write the error message in the online report
3425 FND_MESSAGE.set_name('PO', 'PO_AP_TAX_ENGINE_FAILED_WARN');
3426 l_exc_message_text := FND_MESSAGE.get;
3427 PO_ENCUMBRANCE_POSTPROCESSING.create_exception_report(
3428 p_message_text => l_exc_message_text,
3429 p_user_id => l_user_id,
3430 x_online_report_id => x_online_report_id
3431 );
3432 x_return_status := FND_API.G_RET_STS_ERROR;
3433 x_po_return_code := g_return_FAILURE;
3434 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3435 END IF;
3436 END IF;
3437 --<eTax integration R12 End>
3438 END IF; -- end of check for Reserve action
3439
3440 l_progress := '028';
3441
3442 l_user_id := FND_GLOBAL.user_id;
3443 l_user_resp_id := FND_GLOBAL.resp_id;
3444
3445 l_cbc_flag := nvl(p_cbc_flag, g_parameter_NO);
3446
3447 IF (p_action IN (g_action_FINAL_CLOSE, g_action_UNDO_FINAL_CLOSE)
3448 OR l_cbc_flag = g_parameter_YES) THEN
3449 -- Final Close, Undo Final Close does not operate on backing docs.
3450 -- Additionally, CBC Year-End actions do not act on backing docs
3451
3452 l_get_backing_docs_flag := g_parameter_NO;
3453
3454 --Bug 3480949: removed logic that sets get_backing_docs_flag to
3455 -- NO if the action is Invoice Cancel and the cancelled qty is 0.
3456 -- This case should not happen anyways, and keeping the logic means
3457 -- we would never do backing docs for Services line Invoice cancel
3458
3459 ELSE
3460 -- Setting flag to Yes will cause code to check if there are
3461 -- eligible backing docs. There may or may not be.
3462
3463 l_get_backing_docs_flag := g_parameter_YES;
3464 END IF;
3465
3466 l_progress := '030';
3467 IF g_debug_stmt THEN
3468 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Finished variable set-up');
3469 END IF;
3470
3471
3472 --<Bug 3280496 Start>
3473 -- if cbc_flag = 'Y', then don't do state check in encumbrance validations
3474 IF l_cbc_flag = g_parameter_YES THEN
3475 l_do_state_check := g_parameter_NO;
3476 ELSE
3477 l_do_state_check := g_parameter_YES;
3478 END IF;
3479 --<Bug 3280496 End>
3480
3481 l_progress := '031';
3482 --<SLA R12>
3483
3484 IF p_doc_type = g_doc_type_REQUISITION AND l_is_par_dummy_req = 'Y' THEN
3485 l_validation_successful_flag := g_parameter_YES;
3486 ELSE
3487 PO_ENCUMBRANCE_PREPROCESSING.do_encumbrance_validations(
3488 p_action => p_action
3489 , p_check_only_flag => p_check_only_flag
3490 , p_doc_type => p_doc_type
3491 , p_doc_subtype => p_doc_subtype
3492 , p_doc_level => p_doc_level
3493 , p_doc_level_id => p_doc_level_id
3494 , p_use_enc_gt_flag => p_use_enc_gt_flag
3495 , p_do_state_check_flag => l_do_state_check -- Bug 3280496
3496 , p_validate_document => p_validate_document
3497 , x_validation_successful_flag => l_validation_successful_flag
3498 , x_sub_check_report_id => l_sub_check_report_id
3499 );
3500 END IF;
3501 l_progress := '033';
3502
3503 IF (l_validation_successful_flag <> g_parameter_YES) THEN
3504 l_progress := '035';
3505 l_exc_code := g_SUBMISSION_CHECK_EXC_CODE;
3506 RAISE FND_API.G_EXC_ERROR;
3507 END IF;
3508
3509 l_progress := '040';
3510
3511 BEGIN
3512 PO_ENCUMBRANCE_PREPROCESSING.get_all_distributions(
3513 p_action => l_action
3514 , p_check_only_flag => p_check_only_flag
3515 , p_doc_type => p_doc_type
3516 , p_doc_subtype => p_doc_subtype
3517 , p_doc_level => p_doc_level
3518 , p_doc_level_id => p_doc_level_id
3519 , p_use_enc_gt_flag => p_use_enc_gt_flag
3520 , p_get_backing_docs_flag => l_get_backing_docs_flag
3521 , p_ap_budget_account_id => p_budget_acct_id
3522 , p_possibility_check_flag => g_parameter_NO
3523 , p_cbc_flag => p_cbc_flag
3524 , x_count => l_dist_count
3525 );
3526 EXCEPTION
3527 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3528 l_progress := '041';
3529 l_exc_code := G_GET_ALL_DISTS_EXC_CODE;
3530 RAISE;
3531 END;
3532
3533 l_progress := '043';
3534
3535 IF (NVL(l_dist_count,0) < 1) THEN
3536 l_progress := '045';
3537 l_exc_code := g_NO_ROWS_EXC_CODE;
3538 RAISE FND_API.G_EXC_ERROR;
3539 END IF;
3540
3541 l_progress := '046';
3542 IF g_debug_stmt THEN
3543 PO_DEBUG.debug_table(l_log_head,l_progress,'PO_ENCUMBRANCE_GT',PO_DEBUG.g_all_rows,NULL,'PO');
3544 END IF;
3545
3546 -- Bug 5035240 Begin : Moved this code to check after PO_ENCUMBRANCE_GT is populated
3547 IF p_action = g_action_RESERVE THEN
3548 IF (nvl(p_prevent_partial_flag, 'N') = 'Y'
3549 OR l_gl_call_mode = 'F') THEN
3550
3551 l_progress := '047';
3552 l_partial_flag := 'N';
3553
3554 ELSE
3555 -- Don't allow partials if there is any backing document
3556 BEGIN
3557 SELECT 'N'
3558 INTO l_partial_flag
3559 FROM PO_ENCUMBRANCE_GT
3560 WHERE origin_sequence_num IS NOT NULL
3561 AND rownum = 1;
3562 l_progress := '048';
3563 EXCEPTION
3564 WHEN NO_DATA_FOUND THEN
3565 l_progress := '049';
3566 l_partial_flag := 'Y';
3567 END;
3568 END IF;
3569 ELSE
3570 l_progress := '050';
3571 l_partial_flag := 'N';
3572 END IF; -- end of partial check for Reserve action
3573 -- Bug 5035240 End
3574
3575
3576 --Bug 6069405 start
3577 l_progress := '051';
3578
3579 --If the AD Event is set and the set of books currency is INR then we can assume that the user is IL customer
3580
3581 IF (AD_EVENT_REGISTRY_PKG.Is_Event_Done( p_Owner => 'JA',
3582 p_Event_Name => 'JAI_EXISTENCE_OF_TABLES' ) = TRUE )
3583 AND l_currency_code_func = 'INR' THEN /*l_currency_code_func has the functional currency code.*/
3584
3585 IF g_debug_stmt THEN
3586 PO_DEBUG.debug_stmt(l_log_head,l_progress,'JAI is used');
3587 END IF;
3588
3589 --initialize the counter to zero. This counter would be used as index for the distribution PL/SQL table.
3590
3591 l_distribution_cnt:= 0;
3592
3593 --Use bulk collect to fetch the distribution type and distribution id
3594
3595 SELECT distribution_type,
3596 distribution_id
3597 BULK COLLECT INTO l_distribution_type_tbl, l_distribution_id_tbl
3598 FROM po_encumbrance_gt
3599 ORDER BY line_location_id, distribution_id;
3600
3601 --if any record is present for encumbrance processing then call the IL API
3602 IF l_Distribution_Id_Tbl.COUNT > 0 and l_distribution_type_tbl.count > 0 THEN
3603
3604 l_progress := '052';
3605 IF g_debug_stmt THEN
3606 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Execute the JAI API to fetch the non-recoverable taxes');
3607 END IF;
3608
3609 jai_encum_prc.fetch_nr_tax(l_Distribution_Type_Tbl,l_Distribution_Id_Tbl,p_action,p_doc_type,l_NonRecoverable_Tax_Tbl,l_Return_Status);
3610
3611 l_progress := '053';
3612
3613 IF l_Return_Status = 'S' THEN
3614
3615 IF g_debug_stmt THEN
3616 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Update po_encumbrance_gt with the JAI non-recoverable tax');
3617 END IF;
3618
3619 --Use forall to bulk update the nonrecoverable tax in the encumbrance table
3620 FORALL indx IN 1..l_nonrecoverable_tax_tbl.COUNT
3621 UPDATE po_encumbrance_gt
3622 SET nonrecoverable_tax = nvl(l_nonrecoverable_tax_tbl(indx),0)
3623 WHERE distribution_id = l_distribution_id_tbl(indx)
3624 AND distribution_type = l_distribution_type_tbl(indx);
3625
3626 /* Bug 14482618: reverting the fix made via bug 9980635 as it was creating accounting twice for the NRtax
3627 via the PO distributions NRTax amount populated and the IRTP(India Receiving Transaction processor):
3628
3629 -- Bug 9980635: Non Reoverable Tax amounts in IL tables are not in sync with the PO tables
3630 IF (p_check_only_flag <> 'Y') THEN
3631 FOR indx IN 1..l_distribution_id_tbl.Count LOOP
3632 IF l_distribution_type_tbl(indx) IN ( 'STANDARD','PLANNED', 'SCHEDULED','BLANKET', 'AGREEMENT') THEN
3633 UPDATE po_distributions_all
3634 SET nonrecoverable_tax = nvl(l_nonrecoverable_tax_tbl(indx),0)
3635 WHERE po_distribution_id = l_distribution_id_tbl(indx);
3636 ELSIF l_distribution_type_tbl(indx) IN ('REQUISITION') THEN
3637 UPDATE po_req_distributions_all
3638 SET nonrecoverable_tax = nvl(l_nonrecoverable_tax_tbl(indx),0)
3639 WHERE distribution_id = l_distribution_id_tbl(indx);
3640 END IF;
3641 END LOOP;
3642 END IF;
3643 --Bug 9980635: end
3644
3645 End bug 14482618
3646 */
3647
3648 END IF;
3649 END IF;
3650
3651 l_progress := '054';
3652 IF g_debug_stmt THEN
3653 PO_DEBUG.debug_stmt(l_log_head,l_progress,'JAI Processing - Finished');
3654 END IF;
3655
3656 END IF;
3657
3658 --Bug 6069405 end
3659
3660 l_progress := '055';
3661
3662 IF g_debug_stmt THEN
3663 PO_DEBUG.debug_var(l_log_head,l_progress,'l_partial_flag ',l_partial_flag);
3664 END IF;
3665
3666 BEGIN
3667 PO_ENCUMBRANCE_PREPROCESSING.derive_packet_values(
3668 p_action => l_action
3669 , p_doc_type => p_doc_type
3670 , p_doc_subtype => p_doc_subtype
3671 , p_use_gl_date => l_use_gl_date
3672 , p_override_date => p_override_date
3673 , p_partial_flag => l_partial_flag
3674 , p_cbc_flag => l_cbc_flag
3675 , p_set_of_books_id => l_set_of_books_id
3676 , p_currency_code_func => l_currency_code_func
3677 , p_req_encumb_type_id => l_req_encumb_type
3678 , p_po_encumb_type_id => l_po_encumb_type
3679 , p_ap_reinstated_enc_amt => p_ap_encumbered_amount
3680 , p_ap_cancelled_qty => p_ap_cancelled_quantity
3681 , p_invoice_id => p_invoice_id
3682 );
3683 EXCEPTION
3684 WHEN G_NO_VALID_PERIOD_EXC THEN
3685 l_period_exception_flag := 'Y';
3686 WHEN g_UOM_CONVERSION_EXC THEN --<Bug 14152238 : Set the uom exception flag to 'Y' if UOM exception is raised>--
3687 l_uom_conversion_exc_flag := 'Y';
3688 END;
3689
3690 l_progress := '060';
3691
3692 IF g_debug_stmt THEN
3693 PO_DEBUG.debug_table(l_log_head,l_progress,'PO_ENCUMBRANCE_GT',PO_DEBUG.g_all_rows,NULL,'PO');
3694 END IF;
3695
3696 IF (l_period_exception_flag = 'Y') OR
3697 (l_uom_conversion_exc_flag = 'Y') --<Bug 14152238 : Encumbrance process is stopped if UOM conversion has occured>--
3698
3699 THEN
3700 l_progress := '065';
3701
3702 -- We didn't really send to GL, but the period exception is
3703 -- equivalent to returning from GL with a failure
3704 -- Don't go straight to exception block though, so that we
3705 -- can leverage the create_detailed_report procedure
3706 l_gl_return_code := 'F';
3707
3708 ELSE
3709
3710 l_progress := '070';
3711
3712 PO_ENCUMBRANCE_POSTPROCESSING.insert_packet(
3713 p_status_code => l_gl_packet_status
3714 , p_user_id => l_user_id
3715 , p_set_of_books_id => l_set_of_books_id
3716 , p_currency_code => l_currency_code_func
3717 , p_action => l_action --bug#5646605 added the p_action parameter to derive entity/even type codes
3718 , x_packet_id => l_packet_id
3719 );
3720
3721
3722 l_progress := '072';
3723 l_old_pkt_id := l_packet_id; /* 12405805 execute_gl_call alters packet_id. But this is needed for later reference */
3724 -- If a packet was not created, l_packet_id will be NULL.
3725 -- This happens when all of the rows are prevent_encumbrance.
3726
3727 IF (l_packet_id IS NOT NULL) THEN
3728
3729
3730 SAVEPOINT EXECUTE_GL_CALL_SP; /* Bug 3218669 */
3731
3732 BEGIN
3733 PO_ENCUMBRANCE_POSTPROCESSING.execute_gl_call(
3734 p_set_of_books_id => l_set_of_books_id
3735 , p_packet_id => l_packet_id
3736 , p_gl_mode => l_gl_call_mode
3737 , p_partial_resv_flag => l_partial_flag
3738 , p_override => l_override_funds
3739 , p_conc_flag => g_parameter_NO
3740 , p_user_id => l_user_id
3741 , p_user_resp_id => l_user_resp_id
3742 , x_return_code => l_gl_return_code
3743 );
3744 EXCEPTION
3745 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3746 l_progress := '075';
3747 l_exc_code := g_EXECUTE_GL_CALL_EXC_CODE;
3748 RAISE;
3749 --<BEGIN Bug:12824154>--
3750 WHEN PO_ENCUMBRANCE_POSTPROCESSING.g_EXECUTE_GL_CALL_API_EXC THEN
3751 l_progress := '075';
3752 l_exc_code := g_GL_FUNDS_API_EXC;
3753 RAISE;
3754 --<END Bug:12824154>--
3755 END;
3756
3757 l_exec_gl_call_ret_code := l_gl_return_code; /* 12405805 This value is needed for later reference*/
3758
3759 ELSE
3760
3761 -- Pretend that the call to GL was successful.
3762 -- This lets us handle the case of all prevent rows for free
3763 -- in create_detailed_report.
3764
3765 l_progress := '077';
3766
3767 l_gl_return_code := 'S';
3768
3769 END IF;
3770
3771 -- copy_detailed_gl_results also fills in messages for the
3772 -- prevent_encumbrance rows, so call this whether or not
3773 -- a packet was created.
3774
3775 PO_ENCUMBRANCE_POSTPROCESSING.copy_detailed_gl_results(
3776 p_packet_id => l_old_pkt_id -- Bug#14593047 : Passing Old packet id
3777 -- generated by insert_packet_create_event
3778 --- to get the events processed in this transaction
3779 , p_gl_return_code => l_gl_return_code
3780 );
3781
3782 IF g_debug_stmt THEN
3783 PO_DEBUG.debug_table(l_log_head,l_progress,'PO_ENCUMBRANCE_GT',PO_DEBUG.g_all_rows,NULL,'PO');
3784 END IF;
3785
3786
3787 -- Bug 11665915 Custom hook to validate funds in an external financial system
3788 BEGIN
3789 IF g_debug_stmt THEN
3790 PO_DEBUG.debug_stmt(l_log_head,l_progress,'GL Return Code before Custom Funds API is : '||l_gl_return_code);
3791 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Calling Custom Funds API with Packet ID : '||l_packet_id);
3792 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Calling Custom Funds API with Check Only Flag as : '||p_check_only_flag);
3793 END IF;
3794
3795 PO_CUSTOM_FUNDS_PKG.do_action(
3796 p_packet_id => l_packet_id,
3797 p_check_only_flag => p_check_only_flag,
3798 x_gl_return_code => l_ext_gl_return_code);
3799
3800 IF g_debug_stmt THEN
3801 PO_DEBUG.debug_stmt(l_log_head,l_progress,'External GL Return Code After Custom Funds API is : '||l_ext_gl_return_code);
3802 END IF;
3803
3804 l_gl_return_code := nvl(l_ext_gl_return_code, l_gl_return_code);
3805 IF g_debug_stmt THEN
3806 PO_DEBUG.debug_stmt(l_log_head,l_progress,'GL Return Code After Custom Funds API is : '||l_gl_return_code);
3807 END IF;
3808 Exception
3809 When Others Then
3810 l_progress := '078';
3811 Raise;
3812 End;
3813 -- End Bug 11665915 Custom hook to validate funds in an external financial system
3814
3815 IF (p_check_only_flag = 'N') THEN
3816
3817 l_progress := '800';
3818
3819 -- No need to update anything if we never called GL.
3820
3821 IF (l_packet_id IS NOT NULL) THEN
3822
3823 l_progress := '810';
3824
3825 --bug 3537764: for Req Split, p_action is REQ_SPLIT,
3826 --but l_action is ADJUST. use p_action for the parameter
3827 --here since update is different for Req Split vs.Adjust
3828 PO_ENCUMBRANCE_POSTPROCESSING.update_document_encumbrance(
3829 p_doc_type => p_doc_type
3830 , p_doc_subtype => p_doc_subtype
3831 , p_action => p_action
3832 , p_gl_return_code => l_gl_return_code
3833 );
3834
3835 l_progress := '815';
3836
3837 END IF;
3838
3839 l_progress := '820';
3840
3841 IF (p_action IN ( g_action_RESERVE, g_action_UNRESERVE
3842 , g_action_ADJUST, g_action_REQ_SPLIT
3843 )
3844 ) THEN
3845
3846 -- bug 3518116
3847 -- Sourcing Req Split operates simultaneously on lines
3848 -- from several different headers.
3849 --
3850 -- Multiple docs are not supported for other actions,
3851 -- as this procedure is a bottleneck (it doesn't work in bulk).
3852
3853 IF (p_action = g_action_REQ_SPLIT) THEN
3854
3855 l_progress := '830';
3856
3857 -- For Req Split, there are only Reqs (main doc) in the temp table.
3858
3859 SELECT DISTINCT DISTS.header_id
3860 BULK COLLECT INTO l_doc_id_tbl
3861 FROM PO_ENCUMBRANCE_GT DISTS
3862 ;
3863
3864 l_progress := '835';
3865
3866 ELSE
3867
3868 l_progress := '840';
3869
3870 -- Other non-check actions can only have one main doc
3871 -- in the temp table.
3872
3873 SELECT DECODE( p_doc_type
3874 , g_doc_type_RELEASE, DISTS.po_release_id
3875 , DISTS.header_id
3876 )
3877 INTO l_document_id
3878 FROM PO_ENCUMBRANCE_GT DISTS
3879 WHERE DISTS.origin_sequence_num IS NULL
3880 AND rownum = 1
3881 ;
3882
3883 l_progress := '845';
3884
3885 l_doc_id_tbl := po_tbl_number(l_document_id);
3886
3887 END IF;
3888
3889 l_progress := '850';
3890
3891 IF l_gl_return_code IN ('S', 'A', 'P') THEN
3892 --Added this IF Condition to Avoid Inserting Action History Record, for Funds Check Failed cases. -- Bug 4661095
3893
3894 PO_ENCUMBRANCE_POSTPROCESSING.create_enc_action_history(
3895 p_doc_type => p_doc_type
3896 , p_doc_id_tbl => l_doc_id_tbl
3897 , p_employee_id => p_employee_id
3898 , p_action => p_action
3899 , p_cbc_flag => l_cbc_flag
3900 );
3901
3902 END if;
3903 -- End Of Bug 4661095
3904
3905 l_progress := '860';
3906
3907 IF ( (p_action = g_action_UNRESERVE and l_cbc_flag = 'N')
3908 OR (p_action = g_action_RESERVE and l_cbc_flag = 'Y')
3909 ) THEN
3910
3911 l_progress := '870';
3912
3913 PO_ENCUMBRANCE_POSTPROCESSING.set_status_requires_reapproval(
3914 p_document_type => p_doc_type
3915 , p_action => p_action
3916 , p_cbc_flag => l_cbc_flag
3917 );
3918
3919 l_progress := '875';
3920
3921 END IF;
3922
3923 l_progress := '890';
3924
3925 END IF; -- if encumb action (res, unres, adjust)
3926
3927 END IF; -- if action is check only
3928
3929 END IF; -- if l_period_exception_flag is 'Y' or l_uom_conversion_exc_flag is 'Y'
3930
3931 l_progress := '900';
3932
3933 PO_ENCUMBRANCE_POSTPROCESSING.create_detailed_report(
3934 p_gl_return_code => l_gl_return_code
3935 , p_user_id => l_user_id
3936 , x_online_report_id => x_online_report_id
3937 , x_po_return_code => x_po_return_code
3938 , x_po_return_msg_name => l_po_return_msg_name
3939 );
3940
3941 l_progress := '910';
3942 --<bug#5055417 START>
3943 PO_ENCUMBRANCE_POSTPROCESSING.populate_bc_report_id(x_online_report_id);
3944 --<bug#5055417 END>
3945 IF x_po_return_code IN (g_return_SUCCESS, g_return_WARNING) THEN
3946 x_return_status := FND_API.g_ret_sts_success;
3947 ELSIF x_po_return_code IN (g_return_PARTIAL, g_return_FAILURE) THEN
3948 x_return_status := FND_API.g_ret_sts_error;
3949 ELSE
3950 x_return_status := FND_API.g_ret_sts_unexp_error;
3951 END IF;
3952
3953 -- Push the po_return_msg onto api msg list
3954 -- Bug 3516763: No longer put a message on the message dictionary stack.
3955 -- All callers should get message from api message stack [fnd_msg_pub]
3956 FND_MESSAGE.set_name('PO', l_po_return_msg_name);
3957 FND_MSG_PUB.add;
3958
3959
3960 IF g_debug_stmt THEN
3961 PO_DEBUG.debug_var(l_log_head,l_progress,'x_online_report_id',
3962 x_online_report_id);
3963 PO_DEBUG.debug_var(l_log_head,l_progress,'x_po_return_code',
3964 x_po_return_code);
3965 PO_DEBUG.debug_end(l_log_head);
3966 END IF;
3967
3968 /*BEGIN : Bug 13077585 :Reverting the fix of delete unprocessesed events
3969 to fix the issue of view results screen. The earlier fix has an impact on
3970 federal customers to review the amounts for the account.
3971 /* 12405805
3972 This deletes unprocessed events arised out of checkfunds action
3973 and invalid events arised out of exceptions
3974
3975 IF( p_check_only_flag = 'Y'
3976 OR
3977 (l_exc_code = g_EXECUTE_GL_CALL_EXC_CODE)
3978 OR
3979 (l_gl_return_code = 'F')
3980 ) THEN
3981 IF g_debug_stmt THEN
3982 PO_DEBUG.debug_var(l_log_head,l_progress,'Call made to', 'delete_unnecessary_events');
3983 END IF;
3984 PO_ENCUMBRANCE_POSTPROCESSING.delete_unnecessary_events(l_old_pkt_id, l_action);
3985 END IF;
3986 /*12405805
3987 END : Bug 13077585 */
3988
3989
3990
3991 EXCEPTION
3992 WHEN OTHERS THEN
3993 -- Highest level of exception handling happens here.
3994 -- do_action never intentionally raises exceptions.
3995
3996 IF g_debug_unexp THEN
3997 PO_DEBUG.debug_exc(l_log_head,l_progress);
3998 END IF;
3999
4000 --
4001 -- Set the return statuses.
4002 --
4003
4004 IF (l_exc_code = g_NO_ROWS_EXC_CODE) THEN
4005 x_return_status := FND_API.G_RET_STS_SUCCESS;
4006 x_po_return_code := g_return_SUCCESS;
4007 l_po_return_msg_name := 'PO_ENC_API_SUCCESS';
4008 --Bug : 14536217 Returning error code as FAILURE and status as 'E'
4009 --instead of 'U' when GL_GUNDS_API_EXC is thrown as this is not an
4010 -- unexpected error
4011 ELSIF (l_exc_code = g_SUBMISSION_CHECK_EXC_CODE
4012 OR l_exc_code = g_GL_FUNDS_API_EXC) THEN
4013 x_return_status := FND_API.g_ret_sts_error;
4014 x_po_return_code := g_return_FAILURE;
4015 l_po_return_msg_name := 'PO_ENC_API_FAILURE';
4016 ELSE
4017 x_return_status := FND_API.g_ret_sts_unexp_error;
4018 x_po_return_code := g_return_FATAL;
4019 l_po_return_msg_name := 'PO_ENC_API_FAILURE';
4020 END IF;
4021
4022 --
4023 -- Create the detailed results message.
4024 --
4025
4026 IF (l_exc_code = g_SUBMISSION_CHECK_EXC_CODE) THEN
4027
4028 -- For submission check failures, we already have an online_report_id.
4029 x_online_report_id := l_sub_check_report_id;
4030
4031 ELSE
4032
4033 -- Otherwise, we have to
4034 -- 1. get the appropriate message and
4035 -- 2. turn it into an online report.
4036
4037 -- 1. Get the appropriate message.
4038 /*Bug 9570133 adding nvl around p_doc_level in the following sql */
4039 IF (l_exc_code = g_NO_ROWS_EXC_CODE) THEN
4040
4041 SELECT PLC.displayed_field
4042 INTO l_entity_token
4043 FROM PO_LOOKUP_CODES PLC
4044 WHERE PLC.lookup_type = 'DOCUMENT LEVEL'
4045 AND PLC.lookup_code = NVL(p_doc_level,'HEADER')
4046 ;
4047 FND_MESSAGE.set_name('PO', 'PO_ENC_API_NO_ROWS');
4048 FND_MESSAGE.set_token('ENTITY', l_entity_token);
4049 l_exc_message_text := FND_MESSAGE.get;
4050
4051 ELSIF (l_exc_code IN (g_GET_ALL_DISTS_EXC_CODE,g_EXECUTE_GL_CALL_EXC_CODE)) THEN
4052
4053 -- Assume raiser put a message onto the API message list.
4054 -- Retrieve that message.
4055 l_exc_message_text :=
4056 FND_MSG_PUB.get(
4057 p_msg_index => FND_MSG_PUB.G_LAST
4058 , p_encoded => FND_API.G_FALSE
4059 );
4060 --<BEGIN 12824154>--
4061 ELSIF (l_exc_code = g_GL_FUNDS_API_EXC) then
4062 l_exc_message_text := 'GL_FUNDS_API_EXC';
4063 select ae_event_id INTO PO_ENCUMBRANCE_POSTPROCESSING.g_event_id
4064 from po_bc_distributions
4065 where packet_id = l_old_pkt_id
4066 and rownum = 1;
4067 --<END 12824154>--
4068 ELSE
4069 -- Unrecognized exception.
4070 -- Use the "contact sys admin" message.
4071 po_message_s.sql_error(g_pkg_name,l_api_name,l_progress,SQLCODE,SQLERRM);
4072 l_exc_message_text := FND_MESSAGE.get;
4073
4074 END IF;
4075
4076 -- 2. Create the online report.
4077
4078 PO_ENCUMBRANCE_POSTPROCESSING.create_exception_report(
4079 p_message_text => l_exc_message_text
4080 , p_user_id => l_user_id
4081 , x_online_report_id => x_online_report_id
4082 );
4083
4084 END IF;
4085
4086 -- Push the high-level po_return_msg onto the API msg list
4087 -- Bug 3516763: No longer put a message on the message dictionary stack.
4088 -- All callers should get message from api message stack [fnd_msg_pub]
4089
4090 FND_MESSAGE.set_name('PO', l_po_return_msg_name);
4091 FND_MSG_PUB.add;
4092
4093 -- bug 3218669
4094 -- Clean up GL_BC_PACKETS so that the failed transaction
4095 -- does not continue to hold up funds
4096 -- if the caller decides to ROLLBACK instead of COMMIT.
4097 IF (l_packet_id IS NOT NULL) THEN
4098 ROLLBACK TO EXECUTE_GL_CALL_SP;
4099
4100 /*BEGIN : Bug 13077585 :Reverting the fix of delete unprocessesed events
4101 to fix the issue of view results screen. The earlier fix has an impact on
4102 federal customers to review the amounts for the account.
4103 /* 12405805
4104 This deletes unprocessed events arised out of checkfunds action
4105 and invalid events arised out of exceptions
4106
4107 IF( (p_check_only_flag = 'Y') OR (l_exc_code = g_EXECUTE_GL_CALL_EXC_CODE) OR(l_gl_return_code = 'F')) THEN
4108 PO_ENCUMBRANCE_POSTPROCESSING.delete_unnecessary_events(l_old_pkt_id, l_action);
4109 NULL;
4110 END IF;
4111
4112 /*12405805 END1
4113 END : Bug 13077585 */
4114
4115
4116
4117 --bug#5523323. The rollback in the previous transaction is sufficient
4118 --to remove all unnecessar records / events in case of an unexpected
4119 --failure. The following statement is unnecessary now.
4120 -- PO_ENCUMBRANCE_POSTPROCESSING.delete_packet_autonomous(
4121 -- p_packet_id => l_packet_id);
4122 END IF;
4123
4124 END do_action;
4125
4126
4127
4128 ------------------------------------------------------------------------------
4129 --Start of Comments
4130 --Name: create_report_object
4131 --Pre-reqs:
4132 -- PO_ENCUMBRANCE_GT is populated with all result columns after
4133 -- a call to the FL funds checker
4134 --Modifies:
4135 -- None.
4136 --Locks:
4137 -- None.
4138 --Function:
4139 -- Takes t
4140 --Parameters:
4141 --IN:
4142 --p_online_report_id
4143 -- Unique identifier that points to a set of rows in the
4144 -- PO_ONLINE_REPORT_TEXT table. These rows store the detailed results
4145 -- for a particular encumbrance transaction
4146 --p_report_successes
4147 -- Indicates whether to include information about Successful rows.
4148 -- Warning and Error rows are always included.
4149 -- Values: g_parameter_YES, g_parameter_NO
4150 --OUT:
4151 --x_return_status
4152 -- FND_API.G_RET_STS_SUCCESS if validation succeeds
4153 -- FND_API.G_RET_STS_ERROR if validation fails
4154 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
4155 --x_report_object
4156 -- Object of po_fcout_type
4157 --Testing:
4158 --
4159 --End of Comments
4160 -------------------------------------------------------------------------------
4161 PROCEDURE create_report_object(
4162 x_return_status OUT NOCOPY VARCHAR2
4163 , p_online_report_id IN NUMBER
4164 , p_report_successes IN VARCHAR2
4165 , x_report_object OUT NOCOPY po_fcout_type
4166 )
4167 IS
4168
4169 l_api_name CONSTANT varchar2(30) := 'CREATE_REPORT_OBJECT';
4170 l_log_head CONSTANT varchar2(100) := g_log_head || l_api_name;
4171 l_progress VARCHAR2(3) := '000';
4172 l_report_successes VARCHAR2(1);
4173
4174 BEGIN
4175
4176 l_report_successes := nvl(p_report_successes, g_parameter_YES);
4177
4178 IF g_debug_stmt THEN
4179 PO_DEBUG.debug_begin(l_log_head);
4180 PO_DEBUG.debug_var(l_log_head,l_progress,'p_online_report_id',
4181 p_online_report_id);
4182 PO_DEBUG.debug_var(l_log_head,l_progress,'l_report_successes',
4183 l_report_successes);
4184 END IF;
4185
4186 l_progress := '010';
4187
4188 --<Bug 3278860 Start>
4189
4190 -- Object initialization code added at request of I. Proc.
4191 x_report_object := po_fcout_type(null,null,null,null,
4192 null,null,null,null);
4193
4194 l_progress := '015';
4195
4196 --<Bug 3278860 End>
4197
4198 SELECT
4199 REPORT.sequence
4200 , REPORT.transaction_id
4201 , REPORT.line_num
4202 , REPORT.shipment_num
4203 , REPORT.distribution_num
4204 , REPORT.transaction_location
4205 , REPORT.message_type
4206 , REPORT.text_line
4207 BULK COLLECT INTO
4208 x_report_object.row_index
4209 , x_report_object.distribution_id
4210 , x_report_object.line_num
4211 , x_report_object.shipment_num
4212 , x_report_object.distribution_num
4213 , x_report_object.result_code
4214 , x_report_object.msg_type
4215 , x_report_object.error_msg
4216 FROM PO_ONLINE_REPORT_TEXT REPORT
4217 WHERE REPORT.online_report_id = p_online_report_id
4218 AND ((l_report_successes = g_parameter_NO
4219 AND Nvl(REPORT.message_type,g_result_ERROR) <> g_result_SUCCESS)
4220 OR l_report_successes = g_parameter_YES
4221 )
4222 ;
4223
4224 l_progress := '020';
4225 x_return_status := FND_API.g_ret_sts_success;
4226
4227 IF g_debug_stmt THEN
4228 PO_DEBUG.debug_end(l_log_head);
4229 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',
4230 x_return_status);
4231 END IF;
4232
4233 EXCEPTION
4234 WHEN OTHERS THEN
4235 BEGIN
4236 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS Start');
4237 --unexpected error from this procedure
4238 x_return_status := FND_API.g_ret_sts_unexp_error;
4239 --add message to the stack and log a debug msg if necessary
4240 po_message_s.sql_error(g_pkg_name, l_api_name, l_progress, SQLCODE, SQLERRM);
4241 fnd_msg_pub.add;
4242 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS End');
4243 EXCEPTION
4244 WHEN OTHERS THEN
4245 NULL;
4246 END;
4247
4248 END create_report_object;
4249
4250
4251 ------------------------------------------------------------------------------
4252 --Start of Comments
4253 --Name: is_reservable
4254 --Pre-reqs:
4255 -- N/A.
4256 --Modifies:
4257 -- N/A.
4258 --Locks:
4259 -- None.
4260 --Function:
4261 -- This procedure determines whether a given document has any distributions that
4262 -- are eligible for funds reservation.
4263 --Parameters:
4264 --IN:
4265 --p_doc_type
4266 -- Differentiates between the doc being a REQ, PA, PO, or RELEASE,
4267 -- which is used to identify the tables to look at (PO vs. Req)
4268 -- and the join conditions
4269 --p_doc_subtype
4270 -- Differentiates between the subtypes of documents
4271 -- REQ: NULL
4272 -- PO: STANDARD, PLANNED
4273 -- PA: CONTRACT, BLANKET
4274 -- RELEASE: SCHEDULED, BLANKET
4275 --p_doc_level
4276 -- Specifies the level of the document that is being checked:
4277 -- HEADER, LINE, SHIPMENT, DISTRIBUTION
4278 --p_doc_level_id
4279 -- The id corresponding to the doc level type:
4280 -- header_id/release_id, line_id, line_location_id, distribution_id
4281 --OUT:
4282 --x_return_status
4283 -- FND_API.G_RET_STS_SUCCESS if validation succeeds
4284 -- FND_API.G_RET_STS_ERROR if validation fails
4285 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
4286 --x_reservable_flag
4287 -- Indicates whether funds reservation is possible on this doc level
4288 -- 'Y' means it is possible, 'N' means it isn't.
4289 --Testing:
4290 --
4291 --End of Comments
4292 -------------------------------------------------------------------------------
4293 PROCEDURE is_reservable(
4294 x_return_status OUT NOCOPY VARCHAR2
4295 , p_doc_type IN VARCHAR2
4296 , p_doc_subtype IN VARCHAR2
4297 , p_doc_level IN VARCHAR2
4298 , p_doc_level_id IN NUMBER
4299 , x_reservable_flag OUT NOCOPY VARCHAR2
4300 )
4301 IS
4302
4303 l_api_name CONSTANT varchar2(30) := 'IS_RESERVABLE';
4304 l_log_head CONSTANT VARCHAR2(100) := g_log_head || l_api_name;
4305 l_progress VARCHAR2(3) := '000';
4306
4307 BEGIN
4308
4309 IF g_debug_stmt THEN
4310 PO_DEBUG.debug_begin(l_log_head);
4311 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_type',p_doc_type);
4312 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_subtype',p_doc_subtype);
4313 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_level',p_doc_level);
4314 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_level_id',p_doc_level_id);
4315 END IF;
4316
4317 l_progress := '010';
4318
4319 -- Standard Start of API savepoint
4320 SAVEPOINT IS_RESERVABLE_SP;
4321
4322 l_progress := '020';
4323
4324 PO_ENCUMBRANCE_PREPROCESSING.check_enc_action_possible(
4325 p_action => g_action_RESERVE,
4326 p_doc_type => p_doc_type,
4327 p_doc_subtype => p_doc_subtype,
4328 p_doc_level => p_doc_level,
4329 p_doc_level_id => p_doc_level_id,
4330 x_action_possible_flag => x_reservable_flag);
4331
4332 l_progress := '100';
4333
4334 x_return_status := fnd_api.g_ret_sts_success;
4335
4336 l_progress := '900';
4337
4338 IF g_debug_stmt THEN
4339 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
4340 PO_DEBUG.debug_var(l_log_head,l_progress,'x_reservable_flag',x_reservable_flag);
4341 PO_DEBUG.debug_end(l_log_head);
4342 END IF;
4343
4344 EXCEPTION
4345 WHEN OTHERS THEN
4346 BEGIN
4347 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS Start');
4348 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4349 ROLLBACK TO IS_RESERVABLE_SP;
4350 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
4351 FND_MSG_PUB.add_exc_msg(G_PKG_NAME, l_api_name);
4352 END IF;
4353 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS End');
4354 EXCEPTION
4355 WHEN OTHERS THEN
4356 NULL;
4357 END;
4358
4359 END is_reservable;
4360
4361
4362 ------------------------------------------------------------------------------
4363 --Start of Comments
4364 --Name: is_unreservable
4365 --Pre-reqs:
4366 -- N/A.
4367 --Modifies:
4368 -- N/A.
4369 --Locks:
4370 -- None.
4371 --Function:
4372 -- This procedure determines whether a given document has any distributions
4373 -- that are eligible to be unreserved.
4374 --Parameters:
4375 --IN:
4376 --p_doc_type
4377 -- Differentiates between the doc being a REQ, PA, PO, or RELEASE,
4378 -- which is used to identify the tables to look at (PO vs. Req)
4379 -- and the join conditions
4380 --p_doc_subtype
4381 -- Differentiates between the subtypes of documents
4382 -- REQ: NULL
4383 -- PO: STANDARD, PLANNED
4384 -- PA: CONTRACT, BLANKET
4385 -- RELEASE: SCHEDULED, BLANKET
4386 --p_doc_level
4387 -- Specifies the level of the document that is being checked:
4388 -- HEADER, LINE, SHIPMENT, DISTRIBUTION
4389 --p_doc_level_id
4390 -- The id corresponding to the doc level type:
4391 -- header_id/release_id, line_id, line_location_id, distribution_id
4392 --OUT:
4393 --x_return_status
4394 -- FND_API.G_RET_STS_SUCCESS if validation succeeds
4395 -- FND_API.G_RET_STS_ERROR if validation fails
4396 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
4397 --x_unreservable_flag
4398 -- Indicates whether funds unreservation is possible on this doc level
4399 -- 'Y' means it is possible, 'N' means it isn't.
4400 --Testing:
4401 --
4402 --End of Comments
4403 -------------------------------------------------------------------------------
4404 PROCEDURE is_unreservable(
4405 x_return_status OUT NOCOPY VARCHAR2
4406 , p_doc_type IN VARCHAR2
4407 , p_doc_subtype IN VARCHAR2
4408 , p_doc_level IN VARCHAR2
4409 , p_doc_level_id IN NUMBER
4410 , x_unreservable_flag OUT NOCOPY VARCHAR2
4411 )
4412 IS
4413
4414 l_api_name CONSTANT varchar2(30) := 'IS_UNRESERVABLE';
4415 l_log_head CONSTANT VARCHAR2(100) := g_log_head || l_api_name;
4416 l_progress VARCHAR2(3) := '000';
4417
4418 BEGIN
4419
4420 IF g_debug_stmt THEN
4421 PO_DEBUG.debug_begin(l_log_head);
4422 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_type',p_doc_type);
4423 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_subtype',p_doc_subtype);
4424 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_level',p_doc_level);
4425 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_level_id',p_doc_level_id);
4426 END IF;
4427
4428 l_progress := '010';
4429
4430 -- Standard Start of API savepoint
4431 SAVEPOINT IS_UNRESERVABLE_SP;
4432
4433 l_progress := '020';
4434
4435 PO_ENCUMBRANCE_PREPROCESSING.check_enc_action_possible(
4436 p_action => g_action_UNRESERVE,
4437 p_doc_type => p_doc_type,
4438 p_doc_subtype => p_doc_subtype,
4439 p_doc_level => p_doc_level,
4440 p_doc_level_id => p_doc_level_id,
4441 x_action_possible_flag => x_unreservable_flag);
4442
4443 l_progress := '100';
4444
4445 x_return_status := fnd_api.g_ret_sts_success;
4446
4447 l_progress := '900';
4448
4449 IF g_debug_stmt THEN
4450 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
4451 PO_DEBUG.debug_var(l_log_head,l_progress,'x_unreservable_flag',x_unreservable_flag);
4452 PO_DEBUG.debug_end(l_log_head);
4453 END IF;
4454
4455 EXCEPTION
4456 WHEN OTHERS THEN
4457 BEGIN
4458 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS Start');
4459 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4460 ROLLBACK TO IS_UNRESERVABLE_SP;
4461 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
4462 FND_MSG_PUB.add_exc_msg(G_PKG_NAME, l_api_name);
4463 END IF;
4464 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS End');
4465 EXCEPTION
4466 WHEN OTHERS THEN
4467 NULL;
4468 END;
4469
4470 END is_unreservable;
4471
4472
4473
4474
4475
4476 -------------------------------------------------------------------------------
4477 --Start of Comments
4478 --Name: populate_encumbrance_gt
4479 --Pre-reqs:
4480 -- None.
4481 --Modifies:
4482 -- PO_ENCUMBRANCE_GT
4483 --Locks:
4484 -- PO_HEADERS_ALL
4485 -- PO_RELEASES_ALL
4486 -- PO_DISTRIBUTIONS_ALL
4487 -- PO_REQUISITION_HEADERS_ALL
4488 -- PO_REQ_DISTRIBUTIONS_ALL
4489 --Function:
4490 -- Flattens the PO transaction tables to retrieve all of the
4491 -- data needed by the encumbrance code and put it in the
4492 -- encumbrance table. Also locks the document headers and
4493 -- distributions if requested to do so.
4494 --Parameters:
4495 --IN:
4496 --p_doc_type
4497 -- Document type. Use the g_doc_type_<> variables, where <> is:
4498 -- REQUISITION
4499 -- PA
4500 -- PO
4501 -- RELEASE
4502 --p_doc_level
4503 -- The type of ids that are being passed. Use g_doc_level_<>
4504 -- HEADER
4505 -- LINE
4506 -- SHIPMENT
4507 -- DISTRIBUTION
4508 --p_doc_level_id_tbl
4509 -- Ids of the doc level type with which to populate the encumbrance
4510 -- table. Each distribution with a link to the specified id
4511 -- will be used in the population.
4512 --
4513 --p_adjustment_status_tbl
4514 -- Specifies what to populate in the adjustment_status column of
4515 -- the encumbrance table. A copy will be made for each adjustment
4516 -- status provided. The total number of rows that will be inserted
4517 -- due to this procedure call =
4518 -- total # of distributions below each id
4519 -- *
4520 -- p_adjustment_status_tbl.COUNT
4521 -- To make one copy of each dist. with a blank adjustment_status label, use
4522 -- po_tbl_varchar5( NULL )
4523 -- To make a new and an old copy (typical Adjust action), use
4524 -- po_tbl_varchar5( g_adjustment_status_OLD, g_adjustment_status_NEW )
4525 --
4526 --p_check_only_flag
4527 -- Indicates whether or not to lock the document headers and distributions.
4528 -- g_parameter_NO lock them
4529 -- g_parameter_YES don't lock them
4530 --
4531 --OUT:
4532 --x_return_status
4533 -- Apps standard parameter
4534 -- VARCHAR2(1)
4535 --Testing:
4536 --
4537 --End of Comments
4538 -------------------------------------------------------------------------------
4539 PROCEDURE populate_encumbrance_gt(
4540 x_return_status OUT NOCOPY VARCHAR2
4541 , p_doc_type IN VARCHAR2
4542 , p_doc_level IN VARCHAR2
4543 , p_doc_level_id_tbl IN po_tbl_number
4544 , p_adjustment_status_tbl IN po_tbl_varchar5
4545 , p_check_only_flag IN VARCHAR2
4546 )
4547 IS
4548
4549 l_log_head CONSTANT VARCHAR2(100) := g_log_head||'POPULATE_ENCUMBRANCE_GT';
4550 l_progress VARCHAR2(3) := '000';
4551
4552 l_dist_id_tbl po_tbl_number;
4553 l_dist_id_key NUMBER;
4554
4555 BEGIN
4556
4557 SAVEPOINT populate_encumbrance_gt_PVT;
4558 x_return_status := FND_API.G_RET_STS_SUCCESS;
4559
4560 IF g_debug_stmt THEN
4561 PO_DEBUG.debug_begin(l_log_head);
4562 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_type', p_doc_type);
4563 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_level', p_doc_level);
4564 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_level_id_tbl', p_doc_level_id_tbl);
4565 PO_DEBUG.debug_var(l_log_head,l_progress,'p_adjustment_status_tbl'
4566 ,p_adjustment_status_tbl);
4567 PO_DEBUG.debug_var(l_log_head,l_progress,'p_check_only_flag', p_check_only_flag);
4568 END IF;
4569
4570 l_progress := '010';
4571
4572
4573 -- Get the distribution ids, based on the entity type/ids
4574
4575 PO_CORE_S.get_distribution_ids(
4576 p_doc_type => p_doc_type
4577 , p_doc_level => p_doc_level
4578 , p_doc_level_id_tbl => p_doc_level_id_tbl
4579 , x_distribution_id_tbl => l_dist_id_tbl
4580 );
4581
4582 l_progress := '020';
4583
4584
4585 -- If this is not a Check action, lock the documents
4586
4587 IF (p_check_only_flag = g_parameter_NO) THEN
4588
4589 l_progress := '030';
4590
4591 PO_LOCKS.lock_headers(
4592 p_doc_type => p_doc_type
4593 , p_doc_level => p_doc_level
4594 , p_doc_level_id_tbl => p_doc_level_id_tbl
4595 );
4596
4597 l_progress := '040';
4598
4599 PO_LOCKS.lock_distributions(
4600 p_doc_type => p_doc_type
4601 , p_doc_level => g_doc_level_DISTRIBUTION
4602 , p_doc_level_id_tbl => l_dist_id_tbl
4603 );
4604
4605 l_progress := '050';
4606
4607 ELSE
4608 l_progress := '060';
4609 IF g_debug_stmt THEN
4610 PO_DEBUG.debug_stmt(l_log_head,l_progress,'check only');
4611 END IF;
4612 END IF;
4613
4614 l_progress := '070';
4615
4616 -- Get the document data into the encumbrance table.
4617 -- Make a copy for each value in the adjustment status table.
4618 -- Use the scratchpad to do this in bulk.
4619
4620 ----------------------------------------------------------------
4621 -- PO_SESSION_GT column mapping
4622 --
4623 -- num1 distribution id
4624 ----------------------------------------------------------------
4625
4626 l_dist_id_key := PO_CORE_S.get_session_gt_nextval();
4627
4628 l_progress := '080';
4629
4630 FORALL i IN 1 .. l_dist_id_tbl.COUNT
4631 INSERT INTO PO_SESSION_GT TEMP ( key, num1 )
4632 VALUES ( l_dist_id_key, l_dist_id_tbl(i) )
4633 ;
4634
4635 l_progress := '090';
4636
4637 -- Pull the data from the main document tables to the encumbrance table.
4638
4639 IF (p_doc_type = g_doc_type_REQUISITION) THEN
4640
4641 l_progress := '200';
4642 IF g_debug_stmt THEN
4643 PO_DEBUG.debug_stmt(l_log_head,l_progress,'requisition');
4644 END IF;
4645
4646 FORALL i IN 1 .. p_adjustment_status_tbl.COUNT
4647 INSERT INTO PO_ENCUMBRANCE_GT
4648 ( adjustment_status
4649 , distribution_type
4650 , header_id
4651 , line_id
4652 , line_location_id
4653 , distribution_id
4654 , segment1
4655 , line_num
4656 , distribution_num
4657 , reference_num
4658 , item_description
4659 , budget_account_id
4660 , gl_encumbered_date
4661 , value_basis --<Complex Work R12>
4662 , payment_type --<Complex Work R12>
4663 , encumbered_amount
4664 , amount_ordered
4665 , quantity_ordered
4666 , quantity_delivered
4667 , quantity_on_line
4668 , unit_meas_lookup_code
4669 , item_id
4670 , price
4671 , nonrecoverable_tax
4672 , prevent_encumbrance_flag
4673 , modified_by_agent_flag --bug 3537764
4674 , transferred_to_oe_flag
4675 , source_type_code
4676 , encumbered_flag
4677 , cancel_flag
4678 , closed_code
4679 , project_id
4680 , task_id
4681 , award_num
4682 , expenditure_type
4683 , expenditure_organization_id
4684 , expenditure_item_date
4685 , vendor_id
4686 /* <<CLM Partial Funding Code Changes>> */
4687 , CLM_DOC_FLAG
4688 , FUNDED_VALUE
4689 , QUANTITY_FUNDED
4690 , AMOUNT_FUNDED
4691 , CHANGE_IN_FUNDED_VALUE
4692 /* <<CLM Partial Funding Code Changes>> */
4693 )
4694 SELECT
4695 p_adjustment_status_tbl(i)
4696 , g_dist_type_REQUISITION
4697 , PRH.requisition_header_id
4698 , PRL.requisition_line_id
4699 , PRL.line_location_id
4700 , PRD.distribution_id
4701 , PRH.segment1
4702 , PRL.line_num
4703 , PRD.distribution_num
4704 , PRL.reference_num
4705 , PRL.item_description
4706 , PRD.budget_account_id
4707 , PRD.gl_encumbered_date
4708 , PRL.order_type_lookup_code --<Complex Work R12>
4709 , NULL --<Complex Work R12>
4710 , PRD.encumbered_amount
4711 , PRD.req_line_amount
4712 , PRD.req_line_quantity
4713 , PRL.quantity_delivered
4714 , PRL.quantity
4715 , PRL.unit_meas_lookup_code
4716 , PRL.item_id
4717 , PRL.unit_price
4718 , PRD.nonrecoverable_tax
4719 , PRD.prevent_encumbrance_flag
4720 , PRL.modified_by_agent_flag --bug 3537764
4721 , PRH.transferred_to_oe_flag
4722 , PRL.source_type_code
4723 , PRD.encumbered_flag
4724 , PRL.cancel_flag
4725 , PRL.closed_code
4726 , PRD.project_id
4727 , PRD.task_id
4728 , PRD.award_id-- Bug #4675692
4729 , PRD.expenditure_type
4730 , PRD.expenditure_organization_id
4731 , PRD.expenditure_item_date
4732 , PRL.vendor_id
4733 /* <<CLM Partial Funding Code Changes>> */
4734 , PRH.FEDERAL_FLAG
4735 , PRD.FUNDED_VALUE
4736 , PRD.QUANTITY_FUNDED
4737 , PRD.AMOUNT_FUNDED
4738 , PRD.CHANGE_IN_FUNDED_VALUE
4739 /* <<CLM Partial Funding Code Changes>> */
4740 FROM
4741 PO_REQ_DISTRIBUTIONS_ALL PRD
4742 , PO_REQUISITION_LINES_ALL PRL
4743 , PO_REQUISITION_HEADERS_ALL PRH
4744 , PO_SESSION_GT DIST_IDS
4745 WHERE PRH.requisition_header_id = PRL.requisition_header_id --JOIN
4746 AND PRL.requisition_line_id = PRD.requisition_line_id --JOIN
4747 AND PRD.distribution_id = DIST_IDS.num1 --JOIN
4748 AND DIST_IDS.key = l_dist_id_key
4749 ;
4750
4751 l_progress := '210';
4752
4753 ELSE -- doc is not a req, so it lives in the PO (and Release) tables.
4754
4755 l_progress := '250';
4756 IF g_debug_stmt THEN
4757 PO_DEBUG.debug_stmt(l_log_head,l_progress,'not requisition');
4758 END IF;
4759
4760 FORALL i IN 1 .. p_adjustment_status_tbl.COUNT
4761 INSERT INTO PO_ENCUMBRANCE_GT
4762 ( adjustment_status
4763 , distribution_type
4764 , header_id
4765 , po_release_id
4766 , line_id
4767 , line_location_id
4768 , distribution_id
4769 , from_header_id
4770 , source_distribution_id
4771 , req_distribution_id
4772 , segment1
4773 , line_num
4774 , shipment_num
4775 , distribution_num
4776 , item_description
4777 , comments
4778 , budget_account_id
4779 , gl_encumbered_date
4780 , value_basis --<Complex Work R12>
4781 , payment_type --<Complex Work R12>
4782 , accrue_on_receipt_flag
4783 , amount_to_encumber
4784 , unencumbered_amount
4785 , encumbered_amount
4786 , amount_ordered
4787 , amount_delivered
4788 , amount_billed
4789 , amount_cancelled
4790 , unencumbered_quantity
4791 , quantity_ordered
4792 , quantity_delivered
4793 , quantity_billed
4794 , quantity_cancelled
4795 , unit_meas_lookup_code
4796 , item_id
4797 , price
4798 , nonrecoverable_tax
4799 , currency_code
4800 , rate
4801 , prevent_encumbrance_flag
4802 , encumbrance_required_flag
4803 , encumbered_flag
4804 , cancel_flag
4805 , closed_code
4806 , approved_flag
4807 , project_id
4808 , task_id
4809 , award_num
4810 , expenditure_type
4811 , expenditure_organization_id
4812 , expenditure_item_date
4813 , vendor_id
4814 /* <<CLM Partial Funding Code Changes>> */
4815 , CLM_DOC_FLAG
4816 , FUNDED_VALUE
4817 , QUANTITY_FUNDED
4818 , AMOUNT_FUNDED
4819 , CHANGE_IN_FUNDED_VALUE
4820 /* <<CLM Partial Funding Code Changes>> */
4821 , amount_changed_flag
4822 -- <Bug 13503748: Edit without unreserve ER.>
4823 -- populating amount_changed_flag from po_distributions_all table.
4824 )
4825 SELECT
4826 p_adjustment_status_tbl(i)
4827 , POD.distribution_type
4828 , POD.po_header_id
4829 , POD.po_release_id
4830 , POD.po_line_id
4831 , POD.line_location_id
4832 , POD.po_distribution_id
4833 , POL.from_header_id
4834 , POD.source_distribution_id
4835 , POD.req_distribution_id
4836 , POH.segment1
4837 , POL.line_num
4838 , POLL.shipment_num
4839 , POD.distribution_num
4840 , POL.item_description
4841 , POH.comments
4842 , POD.budget_account_id
4843 , POD.gl_encumbered_date
4844 , POLL.value_basis --<Complex Work R12>
4845 , POLL.payment_type --<Complex Work R12>
4846 , POLL.accrue_on_receipt_flag
4847 , POD.amount_to_encumber
4848 , POD.unencumbered_amount
4849 , POD.encumbered_amount
4850 , POD.amount_ordered
4851 , POD.amount_delivered
4852 , POD.amount_billed
4853 , POD.amount_cancelled
4854 , POD.unencumbered_quantity
4855 , POD.quantity_ordered
4856 , POD.quantity_delivered
4857 , POD.quantity_billed
4858 , POD.quantity_cancelled
4859 , POLL.unit_meas_lookup_code --<Complex Work R12>: use line loc value
4860 , POL.item_id
4861 , POLL.price_override
4862 , POD.nonrecoverable_tax
4863 , POH.currency_code
4864 , POD.rate
4865 , POD.prevent_encumbrance_flag
4866 , POH.encumbrance_required_flag
4867 , POD.encumbered_flag
4868 , DECODE( POD.distribution_type
4869 , g_dist_type_AGREEMENT, POH.cancel_flag
4870 , POLL.cancel_flag
4871 )
4872 , DECODE( POD.distribution_type
4873 , g_dist_type_AGREEMENT, POH.closed_code
4874 , POLL.closed_code
4875 )
4876 , POLL.approved_flag
4877 , POD.project_id
4878 , POD.task_id
4879 , POD.award_id -- Bug #4675692
4880 , POD.expenditure_type
4881 , POD.expenditure_organization_id
4882 , POD.expenditure_item_date
4883 , POH.vendor_id
4884 /* <<CLM Partial Funding Code Changes>> */
4885 , 'N' -- Default for clm doc flag
4886 , POD.FUNDED_VALUE
4887 , POD.QUANTITY_FUNDED
4888 , POD.AMOUNT_FUNDED
4889 , POD.CHANGE_IN_FUNDED_VALUE
4890 /* <<CLM Partial Funding Code Changes>> */
4891 , POD.amount_changed_flag -- <13503748>
4892 FROM
4893 PO_DISTRIBUTIONS_ALL POD
4894 , PO_LINE_LOCATIONS_ALL POLL
4895 , PO_LINES_ALL POL
4896 , PO_HEADERS_ALL POH
4897 , PO_SESSION_GT DIST_IDS
4898 WHERE POH.po_header_id = POD.po_header_id --JOIN
4899 AND POL.po_line_id(+) = POD.po_line_id --JOIN
4900 -- the distributions of PAs don't have associated lines
4901 AND POLL.line_location_id(+) = POD.line_location_id --JOIN
4902 -- the distributions of PAs don't have associated shipments
4903 AND POD.po_distribution_id = DIST_IDS.num1 --JOIN
4904 AND DIST_IDS.key = l_dist_id_key
4905 ;
4906
4907 l_progress := '260';
4908
4909 /* <<CLM Partial Funding Code Changes>> */
4910 IF p_doc_type = g_doc_type_PO THEN
4911 IF g_debug_stmt THEN
4912 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Updating the CLM Doc Flag for Standard PO Distributions.');
4913 END IF;
4914 UPDATE po_encumbrance_gt DISTS
4915 SET DISTS.CLM_DOC_FLAG = PO_PARTIAL_FUNDING_PKG.is_clm_document(g_doc_type_PO, DISTS.HEADER_ID)
4916 WHERE DISTS.distribution_type = 'STANDARD';
4917
4918 l_progress := '270';
4919 IF g_debug_stmt THEN
4920 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Updated the CLM Doc Flag for Standard PO Distributions.');
4921 END IF;
4922 END IF;
4923 /* <<CLM Partial Funding Code Changes>> */
4924 END IF; -- p_doc_type
4925
4926 l_progress := '900';
4927
4928 IF g_debug_stmt THEN
4929 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
4930 PO_DEBUG.debug_end(l_log_head);
4931 END IF;
4932
4933 EXCEPTION
4934 WHEN OTHERS THEN
4935 BEGIN
4936 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS Start');
4937 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4938 ROLLBACK TO populate_encumbrance_gt_PVT;
4939 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS End');
4940 EXCEPTION
4941 WHEN OTHERS THEN
4942 NULL;
4943 END;
4944
4945 END populate_encumbrance_gt;
4946
4947
4948
4949
4950 -------------------------------------------------------------------------------
4951 --Start of Comments
4952 --Name: populate_enc_gt_action_ids
4953 --Pre-reqs:
4954 -- None.
4955 --Modifies:
4956 -- PO_ENCUMBRANCE_GT
4957 --Locks:
4958 -- None.
4959 --Function:
4960 -- Prepares a call to a do_<> action that will operate on multiple ids
4961 -- for the given doc type/level.
4962 -- The ids are placed in PO_ENCUMBRANCE_GT, where the encumbrance actions
4963 -- will find them. This is a workaround to the inability of Forms to
4964 -- deal with database object tables (otherwise, the IDs could be passed
4965 -- as a table parameter to do_<>).
4966 -- Any pre-existing data in PO_ENCUMBRANCE_GT will be deleted.
4967 --Parameters:
4968 --IN:
4969 --p_doc_type
4970 -- Document type. Use the g_doc_type_<> variables, where <> is:
4971 -- REQUISITION
4972 -- PA
4973 -- PO
4974 -- RELEASE
4975 --p_doc_subtype
4976 -- The document subtype. Use the g_doc_subtype_<> variables:
4977 -- STANDARD Standard PO
4978 -- PLANNED Planned PO
4979 -- BLANKET Blanket Release, Blanket Agreement, Global Agreement
4980 -- SCHEDULED Scheduled Release
4981 -- This parameter is not checked for requisitions (okay to use NULL).
4982 --p_doc_level
4983 -- The type of ids that are being passed. Use g_doc_level_<>
4984 -- HEADER
4985 -- LINE
4986 -- SHIPMENT
4987 -- DISTRIBUTION
4988 --p_doc_level_id_tbl
4989 -- IDs corresponding to the doc type/level on which the encumbrance
4990 -- action will be taken.
4991 --OUT:
4992 --x_return_status
4993 -- VARCHAR2(1)
4994 -- Apps standard parameter
4995 --Testing:
4996 --
4997 --End of Comments
4998 -------------------------------------------------------------------------------
4999 PROCEDURE populate_enc_gt_action_ids(
5000 x_return_status OUT NOCOPY VARCHAR2
5001 , p_doc_type IN VARCHAR2
5002 , p_doc_subtype IN VARCHAR2
5003 , p_doc_level IN VARCHAR2
5004 , p_doc_level_id_tbl IN po_tbl_number
5005 )
5006 IS
5007
5008 l_api_name CONSTANT VARCHAR2(30) := 'POPULATE_ENC_GT_ACTION_IDS';
5009 l_log_head CONSTANT VARCHAR2(100) := g_log_head || l_api_name;
5010 l_progress VARCHAR2(3) := '000';
5011
5012 l_distribution_type PO_DISTRIBUTIONS_ALL.distribution_type%TYPE;
5013
5014 BEGIN
5015
5016 -- Standard Start of API savepoint
5017 SAVEPOINT POPULATE_ENC_GT_ACTION_IDS_PVT;
5018
5019 l_progress := '010';
5020
5021 IF g_debug_stmt THEN
5022 PO_DEBUG.debug_begin(l_log_head);
5023 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_type', p_doc_type);
5024 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_subtype', p_doc_subtype);
5025 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_level', p_doc_level);
5026 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_level_id_tbl', p_doc_level_id_tbl);
5027 END IF;
5028
5029 l_progress := '020';
5030
5031 -- Initialize API return status to success
5032 x_return_status := FND_API.G_RET_STS_SUCCESS;
5033
5034 l_progress := '100';
5035
5036 -- Clear the existing data from the encumbrance table.
5037
5038 PO_ENCUMBRANCE_PREPROCESSING.delete_encumbrance_gt();
5039
5040 l_progress := '200';
5041
5042 -- Get the distribution_type based on the doc_type, doc_subtype.
5043
5044 PO_ENCUMBRANCE_PREPROCESSING.derive_dist_from_doc_types(
5045 p_doc_type => p_doc_type
5046 , p_doc_subtype => p_doc_subtype
5047 , x_distribution_type => l_distribution_type
5048 );
5049
5050 l_progress := '210';
5051
5052 -- Populate the ids.
5053 -- Set the prevent_encumbrance_flag for safety.
5054 -- If do_adjust is called after this API is used,
5055 -- hopefully the prevent_encumbrance_flag being set
5056 -- here will stop any data corruption from happening.
5057
5058 FORALL i IN 1 .. p_doc_level_id_tbl.COUNT
5059 INSERT INTO PO_ENCUMBRANCE_GT
5060 ( distribution_type
5061 , doc_level
5062 , doc_level_id
5063 , prevent_encumbrance_flag
5064 )
5065 VALUES
5066 ( l_distribution_type
5067 , p_doc_level
5068 , p_doc_level_id_tbl(i)
5069 , 'Y'
5070 )
5071 ;
5072
5073 l_progress := '900';
5074
5075 IF g_debug_stmt THEN
5076 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
5077 PO_DEBUG.debug_end(l_log_head);
5078 END IF;
5079
5080 EXCEPTION
5081 WHEN OTHERS THEN
5082 BEGIN
5083 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS Start');
5084 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5085 ROLLBACK TO POPULATE_ENC_GT_ACTION_IDS_PVT;
5086 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
5087 FND_MSG_PUB.add_exc_msg(G_PKG_NAME,l_api_name,SQLERRM);
5088 END IF;
5089 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS End');
5090 EXCEPTION
5091 WHEN OTHERS THEN
5092 NULL;
5093 END;
5094
5095 END populate_enc_gt_action_ids;
5096
5097
5098
5099
5100 -------------------------------------------------------------------------------
5101 --Start of Comments
5102 --Name: handle_exception
5103 --Pre-reqs:
5104 -- None.
5105 --Modifies:
5106 -- API message list
5107 --Locks:
5108 -- None.
5109 --Function:
5110 -- Performs necessary manipulation of private encumbrance API
5111 -- return parameters, and adds messages to the API list.
5112 --Parameters:
5113 --IN:
5114 --p_api_name
5115 -- procedure name
5116 --p_progress
5117 -- The location in the procedure at which the exception occurred.
5118 --OUT:
5119 --x_return_status
5120 -- VARCHAR2(1)
5121 -- Will be set to FND_API.g_ret_sts_unexp_error.
5122 --x_po_return_code
5123 -- VARCHAR2(10)
5124 -- Will be set to g_return_FATAL.
5125 --Notes:
5126 -- The need for error handling that is more robust than
5127 -- the Apps API standards require is exemplified by bug 3454804.
5128 --Testing:
5129 --
5130 --End of Comments
5131 -------------------------------------------------------------------------------
5132 PROCEDURE handle_exception(
5133 p_api_name IN VARCHAR2
5134 , p_progress IN VARCHAR2
5135 , x_return_status OUT NOCOPY VARCHAR2
5136 , x_po_return_code OUT NOCOPY VARCHAR2
5137 )
5138 IS
5139
5140 l_api_name CONSTANT VARCHAR2(30) := 'HANDLE_EXCEPTION';
5141 l_log_head CONSTANT VARCHAR2(100) := g_log_head || l_api_name;
5142 l_progress VARCHAR2(3) := '000';
5143
5144 BEGIN
5145
5146 l_progress := '010';
5147
5148 IF g_debug_stmt THEN
5149 PO_DEBUG.debug_begin(l_log_head);
5150 PO_DEBUG.debug_var(l_log_head,l_progress,'p_api_name',p_api_name);
5151 PO_DEBUG.debug_var(l_log_head,l_progress,'p_progress',p_progress);
5152 END IF;
5153
5154
5155 -- 1. Set the API return statuses to indicate a failure.
5156
5157 x_return_status := FND_API.g_ret_sts_unexp_error;
5158 x_po_return_code := g_return_FATAL;
5159
5160 l_progress := '100';
5161
5162 IF g_debug_stmt THEN
5163 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
5164 PO_DEBUG.debug_var(l_log_head,l_progress,'x_po_return_code',x_po_return_code);
5165 END IF;
5166
5167
5168 -- 2. Log the SQL error and add it to the API msg list.
5169
5170 -- Put the SQL error on the message dictionary stack,
5171 -- and log a debug message to FND_LOG_MESSAGES.
5172
5173 PO_MESSAGE_S.sql_error(g_pkg_name,p_api_name,p_progress,SQLCODE,SQLERRM);
5174
5175 l_progress := '150';
5176
5177 -- Take the SQL error message from the message dictionary stack
5178 -- and put it in the API message list.
5179
5180 FND_MSG_PUB.add();
5181
5182 l_progress := '200';
5183
5184
5185 -- 3. Add the encumbrance failure to the API msg list.
5186
5187 -- Push the encumbrance failure message onto the message dictionary stack.
5188
5189 FND_MESSAGE.set_name('PO', 'PO_ENC_API_FAILURE');
5190
5191 l_progress := '250';
5192
5193 -- Pop the message from the message dictionary stack
5194 -- and add it to the API message list.
5195
5196 FND_MSG_PUB.add();
5197
5198 l_progress := '300';
5199
5200
5201 -- 4. Push the encumbrance failure message back onto the
5202 -- message dictionary stack.
5203 -- Bug 3516763: No longer put a message back on the message
5204 -- dictionary stack. Callers should get message from api msg. list.
5205
5206
5207 l_progress := '900';
5208
5209 IF g_debug_stmt THEN
5210 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
5211 PO_DEBUG.debug_var(l_log_head,l_progress,'x_po_return_code',x_po_return_code);
5212 PO_DEBUG.debug_end(l_log_head);
5213 END IF;
5214
5215 EXCEPTION
5216 WHEN OTHERS THEN
5217 IF g_debug_unexp THEN
5218 PO_DEBUG.debug_exc(l_log_head,l_progress);
5219 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
5220 PO_DEBUG.debug_var(l_log_head,l_progress,'x_po_return_code',x_po_return_code);
5221 END IF;
5222
5223 END handle_exception;
5224
5225
5226 -------------------------------------------------------------------------------
5227 --Start of Comments
5228 --Name: POPULATE_BC_REPORT_EVENTS
5229 --Pre-reqs:
5230 -- None.
5231 --Modifies:
5232 -- the PSA_BC_REPORT_EVENTS_GT global temp table
5233 --Locks:
5234 -- None.
5235 --Function:
5236 -- Populates a Global Temp table with the events that are used by the SLA
5237 -- Budgetary Control Results page.
5238 --Parameters:
5239 --IN:
5240 -- p_online_report_id : specifies the unique report id generated for the current
5241 -- enc transaction.
5242 --IN OUT:
5243 -- None.
5244 --OUT:
5245 -- x_return_status :
5246 -- x_events_populated :
5247 --Notes:
5248 --Testing:
5249 --
5250 --End of Comments
5251 -------------------------------------------------------------------------------
5252 PROCEDURE POPULATE_BC_REPORT_EVENTS
5253 (
5254 x_return_status OUT NOCOPY VARCHAR2,
5255 p_online_report_id IN NUMBER, --<bug#5055417>
5256 x_events_populated OUT NOCOPY VARCHAR2
5257 )
5258 IS
5259 l_api_name CONSTANT varchar2(30) := 'POPULATE_BC_REPORT_EVENTS';
5260 BEGIN
5261 x_return_status := FND_API.G_RET_STS_SUCCESS;
5262
5263 DELETE FROM PSA_BC_REPORT_EVENTS_GT;
5264 --<bug#5055417 START>
5265 --Going forward we would populate the events and distribution ids
5266 --directly from po_bc_distributions based on online_report_id
5267 --We stamp all distributions in the current transaction with online
5268 --report_id and then use online_report_id to identify the distributions
5269 --while reporting.
5270 INSERT INTO PSA_BC_REPORT_EVENTS_GT
5271 (
5272 SOURCE_DISTRIBUTION_ID_NUM_1,
5273 EVENT_ID
5274 )
5275 SELECT PBD.distribution_id,
5276 PBD.ae_event_id
5277 FROM PO_BC_DISTRIBUTIONS PBD
5278 WHERE PBD.ONLINE_REPORT_ID= p_online_report_id;
5279 --<bug#5055417 END>
5280 IF (SQL%ROWCOUNT > 0) THEN
5281 x_events_populated := 'Y';
5282 ELSE
5283 x_events_populated := 'N';
5284 END IF;
5285
5286 EXCEPTION
5287 WHEN OTHERS THEN
5288 PO_DEBUG.handle_unexp_error ( p_pkg_name => g_pkg_name,
5289 p_proc_name => l_api_name);
5290 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5291 END POPULATE_BC_REPORT_EVENTS;
5292
5293 -------------------------------------------------------------------------------
5294 --Start of Comments
5295 --Name: POPULATE_AND_CREATE_BC_REPORT
5296 --Pre-reqs:
5297 -- None.
5298 --Modifies:
5299 -- the PSA_BC_REPORT_EVENTS_GT global temp table
5300 --Locks:
5301 -- None.
5302 --Function:
5303 -- Populates a Global Temp table with Budgetary Control events and creates
5304 -- the Budgetary Control transaction report. This is used by the SLA Budgetary
5305 -- Control Results page.
5306 --Parameters:
5307 --IN:
5308 -- p_online_report_id : specifies the unique report id generated for the current
5309 -- enc transaction.
5310 -- p_ledger_id : the ledger id for the document's operating unit
5311 -- p_sequence_id : the id to use for the report (from PSA_BC_XML_REPORT_S)
5312 --IN OUT:
5313 -- None.
5314 --OUT:
5315 -- x_return_status : overall status of the procedure - i.e.
5316 -- FND_API.G_RET_STS_SUCCESS or FND_API.G_RET_STS_UNEXP_ERROR
5317 -- x_report_created : 'Y'/'N' to indicate whether the report was created
5318 -- successfully
5319 --Notes:
5320 --Testing:
5321 --
5322 --End of Comments
5323 -------------------------------------------------------------------------------
5324 PROCEDURE POPULATE_AND_CREATE_BC_REPORT
5325 (
5326 x_return_status OUT NOCOPY VARCHAR2,
5327 p_online_report_id IN NUMBER, --<bug#5055417>
5328 p_ledger_id IN NUMBER,
5329 p_sequence_id IN NUMBER,
5330 x_report_created OUT NOCOPY VARCHAR2
5331 )
5332 IS
5333 l_api_name CONSTANT VARCHAR2(30) := 'POPULATE_AND_CREATE_BC_REPORT';
5334 l_events_populated VARCHAR2(1);
5335 l_bc_xml_count NUMBER;
5336 l_errbuf VARCHAR2(1000);
5337 l_retcode NUMBER := 0;
5338 l_application_id NUMBER := 201; -- PO
5339 l_return_status VARCHAR2(1);
5340 l_progress VARCHAR2(3) := '000';
5341 BEGIN
5342 x_return_status := FND_API.G_RET_STS_SUCCESS;
5343 x_report_created := 'N';
5344 --<bug#5010001 START>
5345 PO_DOCUMENT_FUNDS_PVT.g_ONLINE_REPORT_ID:=p_online_report_id;
5346 --<bug#5010001 END>
5347
5348 populate_bc_report_events (
5349 x_return_status => l_return_status,
5350 p_online_report_id => p_online_report_id,
5351 x_events_populated => l_events_populated
5352 );
5353
5354 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
5355 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5356 END IF;
5357 --<bug#5055417 START>
5358 --If events haven't been populated we needto look into po_online_report_text
5359 -- to figure out if any of the rows in it are marked as show in psa.
5360 --This would indicate that there are rows in the report and we
5361 --would have to launch the OA page from the UI.
5362 IF(l_events_populated ='N')
5363 THEN
5364 BEGIN
5365 select 'Y'
5366 into l_events_populated
5367 from dual
5368 where exists(
5369 select 1
5370 from po_online_report_text
5371 where online_report_id=PO_DOCUMENT_FUNDS_PVT.g_ONLINE_REPORT_ID
5372 and show_in_psa_flag='Y'
5373 );
5374
5375 EXCEPTION
5376 WHEN OTHERS THEN
5377 l_events_populated :='N';
5378 END;
5379 END IF;
5380 --<bug#5055417 END>
5381 IF (l_events_populated = 'Y') THEN
5382
5383 l_progress := '010';
5384
5385 PSA_BC_XML_REPORT_PUB.create_bc_transaction_report (
5386 l_errbuf,
5387 l_retcode,
5388 p_ledger_id,
5389 l_application_id,
5390 'E', -- event_flag
5391 p_sequence_id
5392 );
5393
5394 l_progress := '020';
5395
5396 SELECT count(*)
5397 INTO l_bc_xml_count
5398 FROM PSA_BC_XML_CLOB
5399 WHERE application_id = l_application_id
5400 AND sequence_id = p_sequence_id;
5401
5402 l_progress := '030';
5403
5404 IF (l_bc_xml_count > 0) THEN
5405 x_report_created := 'Y';
5406 END IF;
5407
5408 END IF; -- l_events_populated;
5409 --<bug#5010001 START>
5410 PO_DOCUMENT_FUNDS_PVT.g_ONLINE_REPORT_ID:=null;
5411 --<bug#5010001 END>
5412
5413 EXCEPTION
5414 WHEN OTHERS THEN
5415 PO_DEBUG.handle_unexp_error ( p_pkg_name => g_pkg_name,
5416 p_proc_name => l_api_name,
5417 p_progress => l_progress );
5418 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5419 END POPULATE_AND_CREATE_BC_REPORT;
5420 --<SLA R12 End>
5421
5422 --<bug#5085428 START>
5423 -------------------------------------------------------------------------------
5424 --Start of Comments
5425 --Name: is_req_enc_flipped
5426 --Pre-reqs:
5427 -- None.
5428 --Modifies:
5429 -- None
5430 --Locks:
5431 -- None.
5432 --Function:
5433 -- Returns true or false depending on wether a requisition distribution that is
5434 -- associated with a PO ever had a prevent_encumbrance_flag set to 'Y'.
5435 -- The prevent encumbrance flag would have been 'Y' if it was linked to a backing
5436 -- document that was encumbered.This would have been flipped to 'N' once it was
5437 -- linked to the instead of a Blanket Release.
5438 --Parameters:
5439 --IN:
5440 -- p_req_dist_id : Is the requisition distribution associated with the PO dist.
5441 -- p_event_id : The unique event identifier for the current transaction.
5442 --IN OUT:
5443 -- None.
5444 --OUT:
5445 -- None.
5446 --Notes:
5447 --Testing:
5448 --
5449 --End of Comments
5450 -------------------------------------------------------------------------------
5451
5452 FUNCTION is_req_enc_flipped(p_req_dist_id IN NUMBER, p_event_id IN NUMBER)
5453 RETURN VARCHAR2 IS
5454 l_found VARCHAR2(1);
5455 d_progress NUMBER;
5456 l_module_name CONSTANT VARCHAR2(100) := 'is_req_enc_flipped';
5457 d_module_base CONSTANT VARCHAR2(100) := PO_LOG.get_subprogram_base(D_PACKAGE_BASE,
5458 l_module_name);
5459 BEGIN
5460 IF (PO_LOG.d_proc) THEN
5461 PO_LOG.proc_begin(d_module_base);
5462 PO_LOG.proc_begin(d_module_base, 'p_req_dist_id', p_req_dist_id);
5463 PO_LOG.proc_begin(d_module_base, 'p_event_id', p_event_id);
5464 END IF;
5465
5466 d_progress := 0;
5467
5468 BEGIN
5469 select nvl(pod.encumbered_flag, 'N')
5470 into l_found
5471 from po_req_distributions_all prd,
5472 po_requisition_lines_all prl,
5473 po_distributions_all pod
5474 where prd.distribution_id = p_req_dist_id
5475 and prl.requisition_line_id = prd.requisition_line_id
5476 and prl.document_type_code = 'BLANKET'
5477 and pod.po_header_id = prl.blanket_po_header_id
5478 and pod.distribution_type=g_dist_type_AGREEMENT;--bug#5468417
5479 exception
5480 when others then
5481 IF (PO_LOG.d_exc) THEN
5482 PO_LOG.exc(d_module_base, d_progress, SQLCODE || SQLERRM);
5483 END IF;
5484 l_found := 'N';
5485 end;
5486 d_progress := 1;
5487 IF (PO_LOG.d_proc) THEN
5488 PO_LOG.proc_end(d_module_base);
5489 PO_LOG.proc_end(d_module_base, 'l_found', l_found);
5490 END IF;
5491 return l_found;
5492 END;
5493 --<bug#5085428 END>
5494 /* <<CLM Partial Funding Code Changes>> */
5495 -------------------------------------------------------------------------------
5496 --Start of Comments
5497 --Name: POPULATE_ENC_GT_WITH_DRAFT
5498 --Pre-reqs:
5499 -- None.
5500 --Modifies:
5501 -- the PO_ENCUMBRANCE_GT global temp table
5502 --Locks:
5503 -- None.
5504 --Function:
5505 -- Populates a Global Temp table with data for a given Draft Id
5506 -- This data is going to be used by the Encumbrance api to perform CheckFunds for a given Modification
5507 --
5508 --Parameters:
5509 --IN:
5510 -- p_sequence_id : the Draft Id for a Modification
5511 --IN OUT:
5512 -- None.
5513 --OUT:
5514 -- x_return_status : overall status of the procedure - i.e.
5515 -- FND_API.G_RET_STS_SUCCESS or FND_API.G_RET_STS_UNEXP_ERROR
5516 --Notes:
5517 --Testing:
5518 --
5519 --End of Comments
5520 -------------------------------------------------------------------------------
5521 PROCEDURE Populate_Enc_Gt_With_Draft
5522 (x_return_status OUT NOCOPY VARCHAR2,
5523 p_draft_id IN NUMBER)
5524 IS
5525 l_log_head CONSTANT VARCHAR2(100) := g_log_head
5526 ||'POPULATE_ENC_GT_WITH_DRAFT';
5527 l_progress VARCHAR2(3) := '000';
5528 l_dist_id_tbl PO_TBL_NUMBER;
5529 l_dist_id_key NUMBER;
5530
5531 CURSOR get_dist_ids IS
5532 SELECT po_distribution_id
5533 FROM po_distributions_draft_all
5534 WHERE draft_id = p_draft_id;
5535
5536 BEGIN
5537
5538 SAVEPOINT Populate_Enc_Gt_Draft;
5539 x_return_status := FND_API.G_RET_STS_SUCCESS;
5540
5541 IF g_debug_stmt THEN
5542 po_debug.Debug_begin(l_log_head);
5543 po_debug.Debug_var(l_log_head,l_progress,'p_Draft_Id',p_draft_id);
5544 END IF;
5545
5546 l_progress := '010';
5547 -- Get the distribution ids, based on the Draft iD
5548 OPEN get_dist_ids;
5549 FETCH get_dist_ids BULK COLLECT INTO l_dist_id_tbl;
5550 CLOSE get_dist_ids;
5551
5552 l_progress := '020';
5553 l_dist_id_key := po_core_s.Get_session_gt_nextval();
5554
5555
5556 l_progress := '030';
5557
5558 FORALL i IN 1..l_dist_id_tbl.COUNT
5559 INSERT INTO po_session_gt TEMP
5560 (KEY,
5561 num1)
5562 VALUES (l_dist_id_key,
5563 L_dist_id_tbl(i));
5564
5565 l_progress := '040';
5566 IF g_debug_stmt THEN
5567 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Inserting Data into PO Encumbrance Gt for Draft');
5568 END IF;
5569
5570 INSERT INTO po_encumbrance_gt
5571 (adjustment_status,
5572 distribution_type,
5573 header_id,
5574 po_release_id,
5575 line_id,
5576 line_location_id,
5577 distribution_id,
5578 from_header_id,
5579 source_distribution_id,
5580 req_distribution_id,
5581 segment1,
5582 line_num,
5583 shipment_num,
5584 distribution_num,
5585 item_description,
5586 comments,
5587 budget_account_id,
5588 gl_encumbered_date,
5589 value_basis --
5590 ,
5591 payment_type --
5592 ,
5593 accrue_on_receipt_flag,
5594 amount_to_encumber,
5595 unencumbered_amount,
5596 encumbered_amount,
5597 amount_ordered,
5598 amount_delivered,
5599 amount_billed,
5600 amount_cancelled,
5601 unencumbered_quantity,
5602 quantity_ordered,
5603 quantity_delivered,
5604 quantity_billed,
5605 quantity_cancelled,
5606 unit_meas_lookup_code,
5607 item_id,
5608 price,
5609 nonrecoverable_tax,
5610 currency_code,
5611 rate,
5612 prevent_encumbrance_flag,
5613 encumbrance_required_flag,
5614 encumbered_flag,
5615 cancel_flag,
5616 closed_code,
5617 approved_flag,
5618 project_id,
5619 task_id,
5620 award_num,
5621 expenditure_type,
5622 expenditure_organization_id,
5623 expenditure_item_date,
5624 vendor_id,
5625 clm_doc_flag,
5626 funded_value,
5627 quantity_funded,
5628 amount_funded,
5629 change_in_funded_value)
5630 SELECT NULL,
5631 pod.distribution_type,
5632 pod.po_header_id,
5633 pod.po_release_id,
5634 pod.po_line_id,
5635 pod.line_location_id,
5636 pod.po_distribution_id,
5637 pol.from_header_id,
5638 pod.source_distribution_id,
5639 pod.req_distribution_id,
5640 poh.segment1,
5641 pol.line_num,
5642 poll.shipment_num,
5643 pod.distribution_num,
5644 pol.item_description,
5645 poh.comments,
5646 pod.budget_account_id,
5647 pod.gl_encumbered_date,
5648 poll.value_basis,
5649 poll.payment_type,
5650 poll.accrue_on_receipt_flag,
5651 pod.amount_to_encumber,
5652 pod.unencumbered_amount,
5653 pod.encumbered_amount,
5654 pod.amount_ordered,
5655 pod.amount_delivered,
5656 pod.amount_billed,
5657 pod.amount_cancelled,
5658 pod.unencumbered_quantity,
5659 pod.quantity_ordered,
5660 pod.quantity_delivered,
5661 pod.quantity_billed,
5662 pod.quantity_cancelled,
5663 poll.unit_meas_lookup_code, --: use line loc value
5664 pol.item_id,
5665 poll.price_override,
5666 pod.nonrecoverable_tax,
5667 poh.currency_code,
5668 pod.rate,
5669 pod.prevent_encumbrance_flag,
5670 poh.encumbrance_required_flag,
5671 pod.encumbered_flag,
5672 Decode(pod.distribution_type,g_dist_type_agreement,poh.cancel_flag,
5673 poll.cancel_flag),
5674 Decode(pod.distribution_type,g_dist_type_agreement,poh.closed_code,
5675 poll.closed_code),
5676 poll.approved_flag,
5677 pod.project_id,
5678 pod.task_id,
5679 pod.award_id,
5680 pod.expenditure_type,
5681 pod.expenditure_organization_id,
5682 pod.expenditure_item_date,
5683 poh.vendor_id,
5684 'Y',
5685 pod.funded_value,
5686 pod.quantity_funded,
5687 pod.amount_funded,
5688 pod.change_in_funded_value
5689 FROM po_distributions_merge_v pod,
5690 po_line_locations_merge_v poll,
5691 po_lines_merge_v pol,
5692 po_headers_merge_v poh,
5693 po_session_gt dist_ids
5694 WHERE poh.po_header_id = pod.po_header_id --JOIN
5695 AND pol.po_line_id (+) = pod.po_line_id --JOIN
5696 -- the distributions of PAs don't have associated lines
5697 AND poll.line_location_id (+) = pod.line_location_id --JOIN
5698 -- the distributions of PAs don't have associated shipments
5699 AND pod.po_distribution_id = dist_ids.num1 --JOIN
5700 AND dist_ids.KEY = l_dist_id_key
5701 AND pod.draft_id = p_draft_id
5702 AND pol.draft_id = p_draft_id
5703 AND poh.draft_id = p_draft_id
5704 AND poll.draft_id = p_draft_id;
5705
5706 l_progress := '900';
5707 IF g_debug_stmt THEN
5708 po_debug.Debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
5709 po_debug.Debug_end(l_log_head);
5710 END IF;
5711
5712 EXCEPTION
5713 WHEN OTHERS THEN
5714 BEGIN
5715 po_debug.Debug_unexp(l_log_head,l_progress,'OTHERS Start');
5716 x_return_status := fnd_api.g_ret_sts_unexp_error;
5717 ROLLBACK TO Populate_Enc_Gt_Draft;
5718
5719 po_debug.Debug_unexp(l_log_head,l_progress,'OTHERS End');
5720 EXCEPTION
5721 WHEN OTHERS THEN
5722 NULL;
5723 END;
5724 END Populate_Enc_Gt_With_Draft;
5725 /* <<CLM Partial Funding Code Changes>> */
5726 END PO_DOCUMENT_FUNDS_PVT;
[Home] [Help]