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