1 PACKAGE BODY PO_DOCUMENT_FUNDS_GRP AS
2 -- $Header: POXGENCB.pls 120.2.12020000.2 2013/02/10 13:40:09 vegajula ship $
3
4 G_PKG_NAME CONSTANT varchar2(30) := 'PO_DOCUMENT_FUNDS_GRP';
5 -- Logging global constants
6 D_PACKAGE_BASE CONSTANT VARCHAR2(100) := PO_LOG.get_package_base(G_PKG_NAME);
7
8 c_log_head CONSTANT VARCHAR2(50) := 'po.plsql.'|| G_PKG_NAME || '.';
9
10 -- Debugging
11 g_debug_stmt CONSTANT BOOLEAN := PO_DEBUG.is_debug_stmt_on;
12 g_debug_unexp CONSTANT BOOLEAN := PO_DEBUG.is_debug_unexp_on;
13
14
15
16
17
18 -------------------------------------------------------------------------------
19 --Start of Comments
20 --Name: check_reserve
21 --Pre-reqs:
22 -- If the encumbrance table is being used (p_use_enc_gt_flag = YES),
23 -- then the data needs to be populated in PO_ENCUMBRANCE_GT before
24 -- calling this function.
25 --Modifies:
26 -- Creates funds check entries in the gl_bc_packets table.
27 -- Adds distribution-specific transaction information into the
28 -- po_online_report_text table.
29 -- PO_ENCUMBRANCE_GT
30 --Locks:
31 -- None.
32 --Function:
33 -- Checks to see if a document would pass a funds reservation.
34 --Parameters:
35 --IN:
36 --p_api_version
37 -- Apps API Std - To control correct version in use
38 --p_commit
39 -- Apps API Std - Should data be commited?
40 --p_init_msg_list
41 -- Apps API Std - Initialize the message list?
42 --p_validation_level
43 -- Apps API Std - Level of validations to be done
44 --p_doc_type
45 -- Differentiates between the doc being a REQ, PA, PO, or RELEASE,
46 -- which is used to identify the tables to look at (PO vs. Req)
47 -- and the join conditions.
48 -- Use the g_doc_type_<> variables, where <> is:
49 -- REQUISITION
50 -- PA
51 -- PO
52 -- RELEASE
53 --p_doc_subtype
54 -- Differentiates between the subtypes of documents.
55 -- Use the g_doc_subtype_<> variables:
56 -- STANDARD Standard PO
57 -- PLANNED Planned PO
58 -- BLANKET Blanket Release, Blanket Agreement, Global Agreement
59 -- SCHEDULED Scheduled Release
60 -- This parameter is not checked for requisitions (okay to use NULL).
61 --p_doc_level
62 -- Specifies the level of the document that is being checked:
63 -- Use g_doc_level_<>
64 -- HEADER
65 -- LINE
66 -- SHIPMENT
67 -- DISTRIBUTION
68 -- If the encumbrance table has already been populated
69 -- (multiple ids, unsaved data), use NULL.
70 --p_doc_level_id
71 -- The id corresponding to the doc level type:
72 -- header_id/release_id, line_id, line_location_id, distribution_id
73 -- If the encumbrance table has already been populated, use NULL.
74 --p_use_enc_gt_flag
75 -- Indicates whether or not the data/ids have already been populated
76 -- in the encumbrance table.
77 -- g_parameter_NO
78 -- Retrieve appropriate dists based on p_doc_level/p_doc_level_id
79 -- g_parameter_YES validate the data already in the table
80 -- PO_ENCUMBRANCE_GT already has information on the distributions we are
81 -- performing funds check on
82 --p_override_funds
83 -- Indicates whether funds override capability can be used, if needed,
84 -- to make a transaction succeed.
85 -- g_parameter_NO - don't use override capability
86 -- g_parameter_YES - okay to use override
87 -- g_parameter_USE_PROFILE - base the decision on the profile option
88 -- PO:Override Funds Reservation
89 --p_report_successes
90 -- Determines whether the x_detailed_results object contains information about
91 -- distributions for which encumbrance actions were successful.
92 -- Information for distributions with Warnings and Errors are always included.
93 -- Use g_parameter_YES/NO.
94 --OUT:
95 --x_return_status
96 -- VARCHAR2(1)
97 -- FND_API.G_RET_STS_SUCCESS if validation succeeds
98 -- FND_API.G_RET_STS_ERROR if validation fails
99 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
100 --x_detailed_results
101 -- Object table that stores distribution specific
102 -- reporting information for the encumbrance transaction.
103 --x_po_return_code
104 -- VARCHAR2(10)
105 -- Indicates the PO classification of the results of this transaction.
106 -- g_return_<>
107 -- SUCCESS
108 -- WARNING
109 -- PARTIAL
110 -- FAILURE
111 -- FATAL
112 --Testing:
113 --
114 --End of Comments
115 -------------------------------------------------------------------------------
116 PROCEDURE check_reserve(
117 p_api_version IN NUMBER
118 , p_commit IN VARCHAR2
119 default FND_API.G_FALSE
120 , p_init_msg_list IN VARCHAR2
121 default FND_API.G_FALSE
122 , p_validation_level IN NUMBER
123 default FND_API.G_VALID_LEVEL_FULL
124 , x_return_status OUT NOCOPY VARCHAR2
125 , p_doc_type IN VARCHAR2
126 , p_doc_subtype IN VARCHAR2
127 , p_doc_level IN VARCHAR2
128 , p_doc_level_id IN NUMBER
129 , p_use_enc_gt_flag IN VARCHAR2
130 , p_override_funds IN VARCHAR2
131 , p_report_successes IN VARCHAR2
132 , x_po_return_code OUT NOCOPY VARCHAR2
133 , x_detailed_results OUT NOCOPY po_fcout_type
134 )
135 IS
136
137 l_api_name CONSTANT VARCHAR2(30) := 'CHECK_RESERVE';
138 l_api_version CONSTANT NUMBER := 1.0;
139 l_log_head CONSTANT VARCHAR2(100) := c_log_head || l_api_name;
140 l_progress VARCHAR2(3) := '000';
141 l_report_return_status VARCHAR2(1);
142 l_online_report_id NUMBER;
143
144 BEGIN
145
146 IF g_debug_stmt THEN
147 PO_DEBUG.debug_begin(l_log_head);
148 PO_DEBUG.debug_var(l_log_head,l_progress,'p_api_version',p_api_version);
149 PO_DEBUG.debug_var(l_log_head,l_progress,'p_commit',p_commit);
150 PO_DEBUG.debug_var(l_log_head,l_progress,'p_init_msg_list',p_init_msg_list);
151 PO_DEBUG.debug_var(l_log_head,l_progress,'p_validation_level',p_validation_level);
152 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_type',p_doc_type);
153 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_subtype',p_doc_subtype);
154 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_level',p_doc_level);
155 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_level_id',p_doc_level_id);
156 PO_DEBUG.debug_var(l_log_head,l_progress,'p_use_enc_gt_flag',p_use_enc_gt_flag);
157 PO_DEBUG.debug_var(l_log_head,l_progress,'p_override_funds',p_override_funds);
158 PO_DEBUG.debug_var(l_log_head,l_progress,'p_report_successes',p_report_successes);
159 END IF;
160
161 l_progress := '010';
162
163 -- Standard Start of API savepoint
164 SAVEPOINT CHECK_RESERVE_SP;
165
166 l_progress := '020';
167
168 -- Standard call to check for call compatibility
169
170 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
171 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
172 END IF;
173
174 l_progress := '030';
175
176 -- Initialize message list if p_init_msg_list is set to TRUE.
177
178 IF FND_API.to_Boolean( p_init_msg_list ) THEN
179 FND_MSG_PUB.initialize;
180 END IF;
181
182 l_progress := '040';
183
184 -- Initialize API return status to success
185
186 x_return_status := FND_API.G_RET_STS_SUCCESS;
187
188 l_progress := '050';
189
190 IF g_debug_stmt THEN
191 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Calling Private Procedure');
192 END IF;
193
194 l_progress := '060';
195
196 PO_DOCUMENT_FUNDS_PVT.check_reserve(
197 x_return_status => x_return_status
198 , p_doc_type => p_doc_type
199 , p_doc_subtype => p_doc_subtype
200 , p_doc_level => p_doc_level
201 , p_doc_level_id => p_doc_level_id
202 , p_use_enc_gt_flag => p_use_enc_gt_flag
203 , p_override_funds => p_override_funds
204 , x_po_return_code => x_po_return_code
205 , x_online_report_id => l_online_report_id
206 );
207
208 l_progress := '100';
209
210 IF ( x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
211 OR x_return_status IS NULL
212 ) THEN
213 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
214 END IF;
215
216 l_progress := '110';
217
218 IF g_debug_stmt THEN
219 PO_DEBUG.debug_stmt(l_log_head,l_progress,
220 'Returned from Private Procedure successfully');
221 END IF;
222
223 l_progress := '120';
224
225 PO_DOCUMENT_FUNDS_PVT.create_report_object(
226 x_return_status => l_report_return_status
227 , p_online_report_id => l_online_report_id
228 , p_report_successes => p_report_successes
229 , x_report_object => x_detailed_results
230 );
231
232 l_progress := '200';
233
234 IF ( l_report_return_status = FND_API.G_RET_STS_UNEXP_ERROR
235 OR l_report_return_status IS NULL
236 ) THEN
237 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
238 END IF;
239
240 l_progress := '300';
241
242 -- Standard check of p_commit.
243 IF FND_API.To_Boolean(p_commit) THEN
244 COMMIT WORK;
245 END IF;
246
247 l_progress := '900';
248
249 IF g_debug_stmt THEN
250 PO_DEBUG.debug_var(l_log_head,l_progress,'l_report_return_status',l_report_return_status);
251 PO_DEBUG.debug_var(l_log_head,l_progress,'l_online_report_id',l_online_report_id);
252 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
253 PO_DEBUG.debug_var(l_log_head,l_progress,'x_po_return_code',x_po_return_code);
254 PO_DEBUG.debug_end(l_log_head);
255 END IF;
256
257 EXCEPTION
258 WHEN OTHERS THEN
259 BEGIN
260 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS Start');
261 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
262 IF g_debug_stmt THEN
263 PO_DEBUG.debug_var(l_log_head,l_progress,'l_report_return_status',l_report_return_status);
264 PO_DEBUG.debug_var(l_log_head,l_progress,'l_online_report_id',l_online_report_id);
265 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
266 PO_DEBUG.debug_var(l_log_head,l_progress,'x_po_return_code',x_po_return_code);
267 END IF;
268 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
269 FND_MSG_PUB.add_exc_msg(G_PKG_NAME,l_api_name,SQLERRM);
270 END IF;
271 ROLLBACK TO CHECK_RESERVE_SP;
272 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS End');
273 EXCEPTION
274 WHEN OTHERS THEN
275 NULL;
276 END;
277
278 END check_reserve;
279
280
281 -------------------------------------------------------------------------------
282 --Start of Comments
283 --Name: check_adjust
284 --Pre-reqs:
285 -- This procedure requires that all of the necessary encumbrance-related
286 -- document data has been populated correctly in PO_ENCUMBRANCE_GT.
287 -- The procedure populate_encumbrance_gt will assist with this.
288 --Modifies:
289 -- Creates funds check entries in the gl_bc_packets table.
290 -- Adds distribution-specific transaction information into the
291 -- po_online_report_text table.
292 -- PO_ENCUMBRANCE_GT
293 --Locks:
294 -- None.
295 --Function:
296 -- Checks to see if the requested modifications of funds reservation
297 -- would succeed.
298 --Parameters:
299 --IN:
300 --p_api_version
301 -- Apps API Std - To control correct version in use
302 --p_commit
303 -- Apps API Std - Should data be commited?
304 --p_init_msg_list
305 -- Apps API Std - Initialize the message list?
306 --p_validation_level
307 -- Apps API Std - Level of validations to be done
308 --p_doc_type
309 -- Differentiates between the doc being a REQ, PA, PO, or RELEASE.
310 -- Use the g_doc_type_<> variables, where <> is:
311 -- REQUISITION
312 -- PA
313 -- PO
314 -- RELEASE
315 -- MIXED_PO_RELEASE - supported only for Adjust (Requester Change Order)
316 --p_doc_subtype
317 -- Differentiates between the subtypes of documents
318 -- Use the g_doc_subtype_<> variables:
319 -- STANDARD Standard PO
320 -- PLANNED Planned PO
321 -- BLANKET Blanket Release, Blanket Agreement, Global Agreement
322 -- SCHEDULED Scheduled Release
323 -- MIXED_PO_RELEASE supported only for Adjust
324 -- This parameter is not checked for requisitions (okay to use NULL).
325 --p_override_funds
326 -- Indicates whether funds override capability can be used, if needed,
327 -- to make a transaction succeed.
328 -- g_parameter_NO - don't use override capability
329 -- g_parameter_YES - okay to use override
330 -- g_parameter_USE_PROFILE - base the decision on the profile option
331 -- PO:Override Funds Reservation
332 --p_use_gl_date
333 -- Indicates whether to prefer using the GL_ENCUMBERED_DATE on the
334 -- distribution instead of the override date, when possible.
335 -- g_parameter_NO - only use the override date, never the dist. date
336 -- g_parameter_YES - use the dist. date if it's open (per distribution)
337 -- g_parameter_USE_PROFILE - base the decision on the profile option
338 -- PO:Use Document GL Date to Unreserve
339 --p_override_date
340 -- Caller-specified date to use instead of distribution encumbrance date
341 -- in GL entries.
342 --p_report_successes
343 -- Determines whether the x_detailed_results object contains information about
344 -- distributions for which encumbrance actions were successful.
345 -- Information for distributions with Warnings and Errors are always included.
346 -- Use g_parameter_YES/NO.
347 --OUT:
348 --x_return_status
349 -- VARCHAR2(1)
350 -- FND_API.G_RET_STS_SUCCESS if validation succeeds
351 -- FND_API.G_RET_STS_ERROR if validation fails
352 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
353 --x_po_return_code
354 -- VARCHAR2(10)
355 -- Indicates the PO classification of the results of this transaction.
356 -- g_return_<>
357 -- SUCCESS
358 -- WARNING
359 -- FAILURE
360 -- FATAL
361 --x_detailed_results
362 -- Object table that stores distribution specific
363 -- reporting information for the encumbrance transaction.
364 --Testing:
365 --
366 --End of Comments
367 -------------------------------------------------------------------------------
368 PROCEDURE check_adjust(
369 p_api_version IN NUMBER
370 , p_commit IN VARCHAR2 default FND_API.G_FALSE
371 , p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE
372 , p_validation_level IN NUMBER default FND_API.G_VALID_LEVEL_FULL
373 , x_return_status OUT NOCOPY VARCHAR2
374 , p_doc_type IN VARCHAR2
375 , p_doc_subtype IN VARCHAR2
376 , p_override_funds IN VARCHAR2
377 , p_use_gl_date IN VARCHAR2
378 , p_override_date IN DATE
379 , p_report_successes IN VARCHAR2
380 , x_po_return_code OUT NOCOPY VARCHAR2
381 , x_detailed_results OUT NOCOPY po_fcout_type
382 )
383 IS
384
385
386 l_api_name CONSTANT varchar2(30) := 'CHECK_ADJUST';
387 l_api_version CONSTANT NUMBER := 1.0;
388 l_log_head CONSTANT VARCHAR2(100) := c_log_head || l_api_name;
389 l_progress VARCHAR2(3) := '000';
390 l_report_return_status VARCHAR2(1);
391 l_online_report_id NUMBER;
392
393 BEGIN
394
395 IF g_debug_stmt THEN
396 PO_DEBUG.debug_begin(l_log_head);
397 PO_DEBUG.debug_var(l_log_head,l_progress,'p_api_version',p_api_version);
398 PO_DEBUG.debug_var(l_log_head,l_progress,'p_commit',p_commit);
399 PO_DEBUG.debug_var(l_log_head,l_progress,'p_init_msg_list',p_init_msg_list);
400 PO_DEBUG.debug_var(l_log_head,l_progress,'p_validation_level',p_validation_level);
401 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_type',p_doc_type);
402 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_subtype',p_doc_subtype);
403 PO_DEBUG.debug_var(l_log_head,l_progress,'p_override_funds',p_override_funds);
404 PO_DEBUG.debug_var(l_log_head,l_progress,'p_use_gl_date',p_use_gl_date);
405 PO_DEBUG.debug_var(l_log_head,l_progress,'p_override_date',p_override_date);
406 PO_DEBUG.debug_var(l_log_head,l_progress,'p_report_successes',p_report_successes);
407 END IF;
408
409 l_progress := '010';
410
411 -- Standard Start of API savepoint
412 SAVEPOINT CHECK_ADJUST_SP;
413
414 l_progress := '020';
415
416 -- Standard call to check for call compatibility
417
418 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
419 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
420 END IF;
421
422 l_progress := '030';
423
424 -- Initialize message list if p_init_msg_list is set to TRUE.
425
426 IF FND_API.to_Boolean( p_init_msg_list ) THEN
427 FND_MSG_PUB.initialize;
428 END IF;
429
430 l_progress := '040';
431
432 -- Initialize API return status to success
433
434 x_return_status := FND_API.G_RET_STS_SUCCESS;
435
436 l_progress := '050';
437
438 IF g_debug_stmt THEN
439 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Calling Private Procedure');
440 END IF;
441
442 l_progress := '060';
443
444 PO_DOCUMENT_FUNDS_PVT.check_adjust(
445 x_return_status => x_return_status
446 , p_doc_type => p_doc_type
447 , p_doc_subtype => p_doc_subtype
448 , p_override_funds => p_override_funds
449 , p_use_gl_date => p_use_gl_date
450 , p_override_date => p_override_date
451 , x_po_return_code => x_po_return_code
452 , x_online_report_id => l_online_report_id
453 );
454
455 l_progress := '100';
456
457 IF ( x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
458 OR x_return_status IS NULL
459 ) THEN
460 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
461 END IF;
462
463 l_progress := '110';
464
465 IF g_debug_stmt THEN
466 PO_DEBUG.debug_stmt(l_log_head,l_progress,
467 'Returned from Private Procedure successfully');
468 END IF;
469
470 l_progress := '120';
471
472 PO_DOCUMENT_FUNDS_PVT.create_report_object(
473 x_return_status => l_report_return_status
474 , p_online_report_id => l_online_report_id
475 , p_report_successes => p_report_successes
476 , x_report_object => x_detailed_results
477 );
478
479 l_progress := '200';
480
481 IF ( l_report_return_status = FND_API.G_RET_STS_UNEXP_ERROR
482 OR l_report_return_status IS NULL
483 ) THEN
484 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
485 END IF;
486
487 l_progress := '300';
488
489 -- Standard check of p_commit.
490 IF FND_API.To_Boolean(p_commit) THEN
491 COMMIT WORK;
492 END IF;
493
494 l_progress := '900';
495
496 IF g_debug_stmt THEN
497 PO_DEBUG.debug_var(l_log_head,l_progress,'l_report_return_status',l_report_return_status);
498 PO_DEBUG.debug_var(l_log_head,l_progress,'l_online_report_id',l_online_report_id);
499 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
500 PO_DEBUG.debug_var(l_log_head,l_progress,'x_po_return_code',x_po_return_code);
501 PO_DEBUG.debug_end(l_log_head);
502 END IF;
503
504 EXCEPTION
505 WHEN OTHERS THEN
506 BEGIN
507 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS Start');
508 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
509 IF g_debug_stmt THEN
510 PO_DEBUG.debug_var(l_log_head,l_progress,'l_report_return_status',l_report_return_status);
511 PO_DEBUG.debug_var(l_log_head,l_progress,'l_online_report_id',l_online_report_id);
512 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
513 PO_DEBUG.debug_var(l_log_head,l_progress,'x_po_return_code',x_po_return_code);
514 END IF;
515 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
516 FND_MSG_PUB.add_exc_msg(G_PKG_NAME,l_api_name,SQLERRM);
517 END IF;
518 ROLLBACK TO CHECK_ADJUST_SP;
519 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS End');
520 EXCEPTION
521 WHEN OTHERS THEN
522 NULL;
523 END;
524
525 END check_adjust;
526
527
528
529 -------------------------------------------------------------------------------
530 --Start of Comments
531 --Name: do_reserve
532 --Pre-reqs:
533 -- None.
534 --Modifies:
535 -- Creates funds check entries in the gl_bc_packets table.
536 -- Adds distribution-specific transaction information into the
537 -- po_online_report_text table.
538 -- Adds entries to the action history.
539 -- PO_ENCUMBRANCE_GT
540 -- Updates the base document tables with encumbrance results.
541 --Locks:
542 -- None.
543 --Function:
544 -- This procedure performs funds reservation on all eligible
545 -- distributions of a document entity.
546 --Parameters:
547 --IN:
548 --p_api_version
549 -- Apps API Std - To control correct version in use
550 --p_commit
551 -- Apps API Std - Should data be commited?
552 --p_init_msg_list
553 -- Apps API Std - Initialize the message list?
554 --p_validation_level
555 -- Apps API Std - Level of validations to be done
556 --p_doc_type
557 -- Differentiates between the doc being a REQ, PA, PO, or RELEASE,
558 -- which is used to identify the tables to look at (PO vs. Req)
559 -- and the join conditions.
560 -- Use the g_doc_type_<> variables, where <> is:
561 -- REQUISITION
562 -- PA
563 -- PO
564 -- RELEASE
565 --p_doc_subtype
566 -- Differentiates between the subtypes of documents.
567 -- Use the g_doc_subtype_<> variables:
568 -- STANDARD Standard PO
569 -- PLANNED Planned PO
570 -- BLANKET Blanket Release, Blanket Agreement, Global Agreement
571 -- SCHEDULED Scheduled Release
572 -- This parameter is not checked for requisitions (okay to use NULL).
573 --p_doc_level
574 -- Specifies the level of the document on which the action is being taken.
575 -- Use g_doc_level_<>
576 -- HEADER
577 -- LINE
578 -- SHIPMENT
579 -- DISTRIBUTION
580 --p_doc_level_id_tbl
581 -- The ids corresponding to the doc level type:
582 -- header_id/release_id, line_id, line_location_id, distribution_id
583 -- At the header level, only one ID is supported.
584 -- At other levels, all entities must share the same header.
585 -- This is due to limitations in the submission check
586 -- and action history procedures.
587 --p_prevent_partial_flag
588 -- Indicates whether an attempt should be made to allow some of the
589 -- distributions to be reserved even if others fail.
590 -- g_parameter_YES - do not allow partial reservations
591 -- g_parameter_NO - try to allow partial reservations
592 -- This parameter is just a suggestion, and may be overridden.
593 -- E.g., partials are always prevented when there are backing docs.
594 --p_employee_id
595 -- Employee Id of the user taking the action.
596 -- This is used in the action history entry.
597 -- If NULL is passed, the value will be taken from the current FND user.
598 --p_override_funds
599 -- Indicates whether funds override capability can be used, if needed,
600 -- to make a transaction succeed.
601 -- g_parameter_NO - don't use override capability
602 -- g_parameter_YES - okay to use override
603 -- g_parameter_USE_PROFILE - base the decision on the profile option
604 -- PO:Override Funds Reservation
605 --p_report_successes
606 -- Determines whether the x_detailed_results object contains information about
607 -- distributions for which encumbrance actions were successful.
608 -- Information for distributions with Warnings and Errors are always included.
609 -- Use g_parameter_YES/NO.
610 --OUT:
611 --x_return_status
612 -- VARCHAR2(1)
613 -- FND_API.G_RET_STS_SUCCESS if validation succeeds
614 -- FND_API.G_RET_STS_ERROR if validation fails
615 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
616 --x_po_return_code
617 -- VARCHAR2(10)
618 -- Indicates the PO classification of the results of this transaction.
619 -- g_return_<>
620 -- SUCCESS
621 -- WARNING
622 -- PARTIAL
623 -- FAILURE
624 -- FATAL
625 --x_detailed_results
626 -- Object table that stores distribution specific
627 -- reporting information for the encumbrance transaction
628 --Testing:
629 --
630 --End of Comments
631 -------------------------------------------------------------------------------
632 PROCEDURE do_reserve(
633 p_api_version IN NUMBER
634 , p_commit IN VARCHAR2
635 default FND_API.G_FALSE
636 , p_init_msg_list IN VARCHAR2
637 default FND_API.G_FALSE
638 , p_validation_level IN NUMBER
639 default FND_API.G_VALID_LEVEL_FULL
640 , x_return_status OUT NOCOPY VARCHAR2
641 , p_doc_type IN VARCHAR2
642 , p_doc_subtype IN VARCHAR2
643 , p_doc_level IN VARCHAR2
644 , p_doc_level_id_tbl IN po_tbl_number
645 , p_prevent_partial_flag IN VARCHAR2
646 , p_employee_id IN NUMBER
647 , p_override_funds IN VARCHAR2
648 , p_report_successes IN VARCHAR2
649 , x_po_return_code OUT NOCOPY VARCHAR2
650 , x_detailed_results OUT NOCOPY po_fcout_type
651 )
652 IS
653
654 l_api_name CONSTANT VARCHAR2(30) := 'DO_RESERVE';
655 l_log_head CONSTANT VARCHAR2(100) := c_log_head || l_api_name;
656 l_api_version CONSTANT NUMBER := 1.0;
657 l_progress VARCHAR2(3) := '000';
658 l_report_return_status VARCHAR2(1);
659 l_online_report_id NUMBER;
660
661 -- bug 3435714
662 l_use_enc_gt_flag VARCHAR2(1);
663 l_doc_level_id NUMBER;
664
665 BEGIN
666
667 IF g_debug_stmt THEN
668 PO_DEBUG.debug_begin(l_log_head);
669 PO_DEBUG.debug_var(l_log_head,l_progress,'p_api_version',p_api_version);
670 PO_DEBUG.debug_var(l_log_head,l_progress,'p_commit',p_commit);
671 PO_DEBUG.debug_var(l_log_head,l_progress,'p_init_msg_list',p_init_msg_list);
672 PO_DEBUG.debug_var(l_log_head,l_progress,'p_validation_level',p_validation_level);
673 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_type',p_doc_type);
674 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_subtype',p_doc_subtype);
675 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_level',p_doc_level);
676 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_level_id_tbl',p_doc_level_id_tbl);
677 PO_DEBUG.debug_var(l_log_head,l_progress,'p_prevent_partial_flag',p_prevent_partial_flag);
678 PO_DEBUG.debug_var(l_log_head,l_progress,'p_employee_id',p_employee_id);
679 PO_DEBUG.debug_var(l_log_head,l_progress,'p_override_funds',p_override_funds);
680 PO_DEBUG.debug_var(l_log_head,l_progress,'p_report_successes',p_report_successes);
681 END IF;
682
683 l_progress := '010';
684
685 -- Standard Start of API savepoint
686 SAVEPOINT DO_RESERVE_SP;
687
688 l_progress := '020';
689
690 -- Standard call to check for call compatibility
691
692 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
693 l_progress := '025';
694 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
695 END IF;
696
697 l_progress := '030';
698
699 -- Initialize message list if p_init_msg_list is set to TRUE.
700
701 IF FND_API.to_Boolean( p_init_msg_list ) THEN
702 FND_MSG_PUB.initialize;
703 END IF;
704
705 l_progress := '040';
706
707 -- Initialize API return status to success
708
709 x_return_status := FND_API.G_RET_STS_SUCCESS;
710
711 l_progress := '100';
712
713 -- bug 3435714
714 -- The private procedure cannot take a table of IDs, as Forms is not
715 -- able to deal with database object tables.
716 -- We use the encumbrance table to work around this restriction,
717 -- by passing the IDs in the table.
718 -- If only acting on one ID, we can skip some work (validations, etc.)
719 -- by passing the ID directly.
720
721 IF (p_doc_level_id_tbl.COUNT > 1) THEN
722 l_progress := '110';
723
724 PO_DOCUMENT_FUNDS_PVT.populate_enc_gt_action_ids(
725 x_return_status => x_return_status
726 , p_doc_type => p_doc_type
727 , p_doc_subtype => p_doc_subtype
728 , p_doc_level => p_doc_level
729 , p_doc_level_id_tbl => p_doc_level_id_tbl
730 );
731
732 l_progress := '120';
733
734 IF ( x_return_status <> FND_API.G_RET_STS_SUCCESS
735 OR x_return_status IS NULL
736 ) THEN
737 l_progress := '130';
738 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
739 END IF;
740
741 l_use_enc_gt_flag := g_parameter_YES;
742 l_doc_level_id := NULL;
743
744 ELSE
745 l_progress := '140';
746 l_use_enc_gt_flag := g_parameter_NO;
747 l_doc_level_id := p_doc_level_id_tbl(1);
748 END IF;
749
750 l_progress := '200';
751
752 PO_DOCUMENT_FUNDS_PVT.do_reserve(
753 x_return_status => x_return_status
754 , p_doc_type => p_doc_type
755 , p_doc_subtype => p_doc_subtype
756 , p_doc_level => p_doc_level
757 , p_doc_level_id => l_doc_level_id
758 , p_use_enc_gt_flag => l_use_enc_gt_flag
759 , p_prevent_partial_flag => p_prevent_partial_flag
760 , p_validate_document => g_parameter_YES
761 , p_override_funds => p_override_funds
762 , p_employee_id => p_employee_id
763 , x_po_return_code => x_po_return_code
764 , x_online_report_id => l_online_report_id
765 );
766
767 l_progress := '300';
768
769 -- let an expected error bubble up to the caller.
770 IF ( x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
771 OR x_return_status IS NULL
772 ) THEN
773 l_progress := '310';
774 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
775 END IF;
776
777 l_progress := '400';
778
779 PO_DOCUMENT_FUNDS_PVT.create_report_object(
780 x_return_status => l_report_return_status
781 , p_online_report_id => l_online_report_id
782 , p_report_successes => p_report_successes
783 , x_report_object => x_detailed_results
784 );
785
786 l_progress := '500';
787
788 IF ( l_report_return_status = FND_API.G_RET_STS_UNEXP_ERROR
789 OR l_report_return_status IS NULL
790 ) THEN
791 l_progress := '510';
792 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
793 END IF;
794
795 l_progress := '600';
796
797 -- Standard check of p_commit.
798 IF FND_API.To_Boolean(p_commit) THEN
799 COMMIT WORK;
800 END IF;
801
802 l_progress := '900';
803
804 IF g_debug_stmt THEN
805 PO_DEBUG.debug_var(l_log_head,l_progress,'l_report_return_status',l_report_return_status);
806 PO_DEBUG.debug_var(l_log_head,l_progress,'l_online_report_id',l_online_report_id);
807 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
808 PO_DEBUG.debug_var(l_log_head,l_progress,'x_po_return_code',x_po_return_code);
809 PO_DEBUG.debug_end(l_log_head);
810 END IF;
811
812 EXCEPTION
813 WHEN OTHERS THEN
814 BEGIN
815 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS Start');
816 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
817 IF g_debug_stmt THEN
818 PO_DEBUG.debug_var(l_log_head,l_progress,'l_report_return_status',l_report_return_status);
819 PO_DEBUG.debug_var(l_log_head,l_progress,'l_online_report_id',l_online_report_id);
820 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
821 PO_DEBUG.debug_var(l_log_head,l_progress,'x_po_return_code',x_po_return_code);
822 END IF;
823 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
824 FND_MSG_PUB.add_exc_msg(G_PKG_NAME,l_api_name,SQLERRM);
825 END IF;
826 ROLLBACK TO DO_RESERVE_SP;
827 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS End');
828 EXCEPTION
829 WHEN OTHERS THEN
830 NULL;
831 END;
832
833 END do_reserve;
834
835
836
837
838 -------------------------------------------------------------------------------
839 --Start of Comments
840 --Name: do_unreserve
841 --Pre-reqs:
842 -- None.
843 --Modifies:
844 -- Creates encumbrance entries in the gl_bc_packets table.
845 -- Adds distribution-specific transaction information into the
846 -- po_online_report_text table.
847 -- Adds entries to the action history.
848 -- PO_ENCUMBRANCE_GT
849 -- Updates the base document tables with encumbrance results.
850 --Locks:
851 -- None.
852 --Function:
853 -- This procedure unreserves the encumbrance on all eligible distributions
854 -- of the requested document or document subentity.
855 --Parameters:
856 --IN:
857 --p_api_version
858 -- Apps API Std - To control correct version in use
859 --p_commit
860 -- Apps API Std - Should data be commited?
861 --p_init_msg_list
862 -- Apps API Std - Initialize the message list?
863 --p_validation_level
864 -- Apps API Std - Level of validations to be done
865 --p_doc_type
866 -- Differentiates between the doc being a REQ, PA, PO, or RELEASE,
867 -- which is used to identify the tables to look at (PO vs. Req)
868 -- and the join conditions.
869 -- Use the g_doc_type_<> variables, where <> is:
870 -- REQUISITION
871 -- PA
872 -- PO
873 -- RELEASE
874 --p_doc_subtype
875 -- Differentiates between the subtypes of documents.
876 -- Use the g_doc_subtype_<> variables:
877 -- STANDARD Standard PO
878 -- PLANNED Planned PO
879 -- BLANKET Blanket Release, Blanket Agreement, Global Agreement
880 -- SCHEDULED Scheduled Release
881 -- This parameter is not checked for requisitions (okay to use NULL).
882 --p_doc_level
883 -- Specifies the level of the document on which the action is being taken.
884 -- Use g_doc_level_<>
885 -- HEADER
886 -- LINE
887 -- SHIPMENT
888 -- DISTRIBUTION
889 --p_doc_level_id_tbl
890 -- The ids corresponding to the doc level type:
891 -- header_id/release_id, line_id, line_location_id, distribution_id
892 -- At the header level, only one ID is supported.
893 -- At other levels, all entities must share the same header.
894 -- This is due to limitations in the submission check
895 -- and action history procedures.
896 --p_employee_id
897 -- Employee Id of the user taking the action.
898 -- This is used in the action history entry.
899 -- If NULL is passed, the value will be taken from the current FND user.
900 --p_override_funds
901 -- Indicates whether funds override capability can be used, if needed,
902 -- to make a transaction succeed.
903 -- g_parameter_NO - don't use override capability
904 -- g_parameter_YES - okay to use override
905 -- g_parameter_USE_PROFILE - base the decision on the profile option
906 -- PO:Override Funds Reservation
907 --p_use_gl_date
908 -- Indicates whether to prefer using the GL_ENCUMBERED_DATE on the
909 -- distribution instead of the override date, when possible.
910 -- g_parameter_NO - only use the override date, never the dist. date
911 -- g_parameter_YES - use the dist. date if it's open (per distribution)
912 -- g_parameter_USE_PROFILE - base the decision on the profile option
913 -- PO:Use Document GL Date to Unreserve
914 --p_override_date
915 -- Caller-specified date to use instead of distribution encumbrance date
916 -- in GL entries.
917 --p_report_successes
918 -- Determines whether the x_detailed_results object contains information about
919 -- distributions for which encumbrance actions were successful.
920 -- Information for distributions with Warnings and Errors are always included.
921 -- Use g_parameter_YES/NO.
922 --OUT:
923 --x_return_status
924 -- VARCHAR2(1)
925 -- FND_API.G_RET_STS_SUCCESS if validation succeeds
926 -- FND_API.G_RET_STS_ERROR if validation fails
927 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
928 --x_po_return_code
929 -- VARCHAR2(10)
930 -- Indicates the PO classification of the results of this transaction.
931 -- g_return_<>
932 -- SUCCESS
933 -- WARNING
934 -- FAILURE
935 -- FATAL
936 --x_detailed_results
937 -- Object table that stores distribution specific
938 -- reporting information for the encumbrance transaction
939 --Testing:
940 --
941 --End of Comments
942 -------------------------------------------------------------------------------
943 PROCEDURE do_unreserve(
944 p_api_version IN NUMBER
945 , p_commit IN VARCHAR2
946 default FND_API.G_FALSE
947 , p_init_msg_list IN VARCHAR2
948 default FND_API.G_FALSE
949 , p_validation_level IN NUMBER
950 default FND_API.G_VALID_LEVEL_FULL
951 , x_return_status OUT NOCOPY VARCHAR2
952 , p_doc_type IN VARCHAR2
953 , p_doc_subtype IN VARCHAR2
954 , p_doc_level IN VARCHAR2
955 , p_doc_level_id_tbl IN po_tbl_number
956 , p_employee_id IN NUMBER
957 , p_override_funds IN VARCHAR2
958 , p_use_gl_date IN VARCHAR2
959 , p_override_date IN DATE
960 , p_report_successes IN VARCHAR2
961 , x_po_return_code OUT NOCOPY VARCHAR2
962 , x_detailed_results OUT NOCOPY po_fcout_type
963 )
964 IS
965
966 l_api_name CONSTANT VARCHAR2(30) := 'DO_UNRESERVE';
967 l_log_head CONSTANT VARCHAR2(100) := c_log_head || l_api_name;
968 l_api_version CONSTANT NUMBER := 1.0;
969 l_progress VARCHAR2(3) := '000';
970 l_report_return_status VARCHAR2(1);
971 l_online_report_id NUMBER;
972
973 -- bug 3435714
974 l_use_enc_gt_flag VARCHAR2(1);
975 l_doc_level_id NUMBER;
976
977 BEGIN
978
979 IF g_debug_stmt THEN
980 PO_DEBUG.debug_begin(l_log_head);
981 PO_DEBUG.debug_var(l_log_head,l_progress,'p_api_version',p_api_version);
982 PO_DEBUG.debug_var(l_log_head,l_progress,'p_commit',p_commit);
983 PO_DEBUG.debug_var(l_log_head,l_progress,'p_init_msg_list',p_init_msg_list);
984 PO_DEBUG.debug_var(l_log_head,l_progress,'p_validation_level',p_validation_level);
985 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_type',p_doc_type);
986 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_subtype',p_doc_subtype);
987 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_level',p_doc_level);
988 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_level_id_tbl',p_doc_level_id_tbl);
989 PO_DEBUG.debug_var(l_log_head,l_progress,'p_employee_id',p_employee_id);
990 PO_DEBUG.debug_var(l_log_head,l_progress,'p_override_funds',p_override_funds);
991 PO_DEBUG.debug_var(l_log_head,l_progress,'p_use_gl_date',p_use_gl_date);
992 PO_DEBUG.debug_var(l_log_head,l_progress,'p_override_date',p_override_date);
993 PO_DEBUG.debug_var(l_log_head,l_progress,'p_report_successes',p_report_successes);
994 END IF;
995
996 l_progress := '010';
997
998 -- Standard Start of API savepoint
999 SAVEPOINT DO_UNRESERVE_SP;
1000
1001 l_progress := '020';
1002
1003 -- Standard call to check for call compatibility
1004
1005 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1006 l_progress := '025';
1007 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1008 END IF;
1009
1010 l_progress := '030';
1011
1012 -- Initialize message list if p_init_msg_list is set to TRUE.
1013
1014 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1015 FND_MSG_PUB.initialize;
1016 END IF;
1017
1018 l_progress := '040';
1019
1020 -- Initialize API return status to success
1021
1022 x_return_status := FND_API.G_RET_STS_SUCCESS;
1023
1024 l_progress := '100';
1025
1026 -- bug 3435714
1027 -- The private procedure cannot take a table of IDs, as Forms is not
1028 -- able to deal with database object tables.
1029 -- We use the encumbrance table to work around this restriction,
1030 -- by passing the IDs in the table.
1031 -- If only acting on one ID, we can skip some work (validations, etc.)
1032 -- by passing the ID directly.
1033
1034 IF (p_doc_level_id_tbl.COUNT > 1) THEN
1035 l_progress := '110';
1036
1037 PO_DOCUMENT_FUNDS_PVT.populate_enc_gt_action_ids(
1038 x_return_status => x_return_status
1039 , p_doc_type => p_doc_type
1040 , p_doc_subtype => p_doc_subtype
1041 , p_doc_level => p_doc_level
1042 , p_doc_level_id_tbl => p_doc_level_id_tbl
1043 );
1044
1045 l_progress := '120';
1046
1047 IF ( x_return_status <> FND_API.G_RET_STS_SUCCESS
1048 OR x_return_status IS NULL
1049 ) THEN
1050 l_progress := '130';
1051 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1052 END IF;
1053
1054 l_use_enc_gt_flag := g_parameter_YES;
1055 l_doc_level_id := NULL;
1056
1057 ELSE
1058 l_progress := '140';
1059 l_use_enc_gt_flag := g_parameter_NO;
1060 l_doc_level_id := p_doc_level_id_tbl(1);
1061 END IF;
1062
1063 l_progress := '200';
1064
1065 PO_DOCUMENT_FUNDS_PVT.do_unreserve(
1066 x_return_status => x_return_status
1067 , p_doc_type => p_doc_type
1068 , p_doc_subtype => p_doc_subtype
1069 , p_doc_level => p_doc_level
1070 , p_doc_level_id => l_doc_level_id
1071 , p_use_enc_gt_flag => l_use_enc_gt_flag
1072 , p_validate_document => g_parameter_YES
1073 , p_override_funds => p_override_funds
1074 , p_use_gl_date => p_use_gl_date
1075 , p_override_date => p_override_date
1076 , p_employee_id => p_employee_id
1077 , x_po_return_code => x_po_return_code
1078 , x_online_report_id => l_online_report_id
1079 );
1080
1081 l_progress := '300';
1082
1083 -- let an expected error bubble up to the caller.
1084 IF ( x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
1085 OR x_return_status IS NULL
1086 ) THEN
1087 l_progress := '310';
1088 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1089 END IF;
1090
1091 l_progress := '400';
1092
1093 PO_DOCUMENT_FUNDS_PVT.create_report_object(
1094 x_return_status => l_report_return_status
1095 , p_online_report_id => l_online_report_id
1096 , p_report_successes => p_report_successes
1097 , x_report_object => x_detailed_results
1098 );
1099
1100 l_progress := '500';
1101
1102 IF l_report_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1103 l_progress := '510';
1104 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1105 END IF;
1106
1107 l_progress := '600';
1108
1109 -- Standard check of p_commit.
1110 IF FND_API.To_Boolean(p_commit) THEN
1111 COMMIT WORK;
1112 END IF;
1113
1114 l_progress := '900';
1115
1116 IF g_debug_stmt THEN
1117 PO_DEBUG.debug_var(l_log_head,l_progress,'l_report_return_status',l_report_return_status);
1118 PO_DEBUG.debug_var(l_log_head,l_progress,'l_online_report_id',l_online_report_id);
1119 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
1120 PO_DEBUG.debug_var(l_log_head,l_progress,'x_po_return_code',x_po_return_code);
1121 PO_DEBUG.debug_end(l_log_head);
1122 END IF;
1123
1124 EXCEPTION
1125 WHEN OTHERS THEN
1126 BEGIN
1127 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS Start');
1128 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1129 IF g_debug_stmt THEN
1130 PO_DEBUG.debug_var(l_log_head,l_progress,'l_report_return_status',l_report_return_status);
1131 PO_DEBUG.debug_var(l_log_head,l_progress,'l_online_report_id',l_online_report_id);
1132 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
1133 PO_DEBUG.debug_var(l_log_head,l_progress,'x_po_return_code',x_po_return_code);
1134 END IF;
1135 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1136 FND_MSG_PUB.add_exc_msg(G_PKG_NAME,l_api_name,SQLERRM);
1137 END IF;
1138 ROLLBACK TO DO_UNRESERVE_SP;
1139 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS End');
1140 EXCEPTION
1141 WHEN OTHERS THEN
1142 NULL;
1143 END;
1144
1145 END do_unreserve;
1146
1147 -------------------------------------------------------------------------------
1148 --Bug#13587378: Encumbrance changes for Line Structure changes.
1149 --Start of Comments
1150 --Name: do_liquidate
1151 --Pre-reqs:
1152 -- This API is supposed to be called from Requisition only.
1153 --Modifies:
1154 -- Creates encumbrance entries in the gl_bc_packets table.
1155 -- Adds distribution-specific transaction information into the
1156 -- po_online_report_text table.
1157 -- Adds entries to the action history.
1158 -- PO_ENCUMBRANCE_GT
1159 -- Updates the base document tables with encumbrance results.
1160 --Locks:
1161 -- None.
1162 --Function:
1163 -- This procedure liquidates the Funds Available on all eligible distributions
1164 -- of the requested Requisition document or Requisition document subentity.
1165 --Parameters:
1166 --IN:
1167 --p_api_version
1168 -- Apps API Std - To control correct version in use
1169 --p_commit
1170 -- Apps API Std - Should data be commited?
1171 --p_init_msg_list
1172 -- Apps API Std - Initialize the message list?
1173 --p_validation_level
1174 -- Apps API Std - Level of validations to be done
1175 --p_doc_level
1176 -- Specifies the level of the document on which the action is being taken.
1177 -- Use g_doc_level_<>
1178 -- HEADER
1179 -- LINE
1180 -- SHIPMENT
1181 -- DISTRIBUTION
1182 --p_doc_level_id_tbl
1183 -- The ids corresponding to the doc level type:
1184 -- header_id/release_id, line_id, line_location_id, distribution_id
1185 -- At the header level, only one ID is supported.
1186 -- At other levels, all entities must share the same header.
1187 -- This is due to limitations in the submission check
1188 -- and action history procedures.
1189 --p_employee_id
1190 -- Employee Id of the user taking the action.
1191 -- This is used in the action history entry.
1192 -- If NULL is passed, the value will be taken from the current FND user.
1193 --p_override_funds
1194 -- Indicates whether funds override capability can be used, if needed,
1195 -- to make a transaction succeed.
1196 -- g_parameter_NO - don't use override capability
1197 -- g_parameter_YES - okay to use override
1198 -- g_parameter_USE_PROFILE - base the decision on the profile option
1199 -- PO:Override Funds Reservation
1200 --p_use_gl_date
1201 -- Indicates whether to prefer using the GL_ENCUMBERED_DATE on the
1202 -- distribution instead of the override date, when possible.
1203 -- g_parameter_NO - only use the override date, never the dist. date
1204 -- g_parameter_YES - use the dist. date if it's open (per distribution)
1205 -- g_parameter_USE_PROFILE - base the decision on the profile option
1206 -- PO:Use Document GL Date to Unreserve
1207 --p_override_date
1208 -- Caller-specified date to use instead of distribution encumbrance date
1209 -- in GL entries.
1210 --p_report_successes
1211 -- Determines whether the x_detailed_results object contains information about
1212 -- distributions for which encumbrance actions were successful.
1213 -- Information for distributions with Warnings and Errors are always included.
1214 -- Use g_parameter_YES/NO.
1215 --OUT:
1216 --x_return_status
1217 -- VARCHAR2(1)
1218 -- FND_API.G_RET_STS_SUCCESS if validation succeeds
1219 -- FND_API.G_RET_STS_ERROR if validation fails
1220 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
1221 --x_po_return_code
1222 -- VARCHAR2(10)
1223 -- Indicates the PO classification of the results of this transaction.
1224 -- g_return_<>
1225 -- SUCCESS
1226 -- WARNING
1227 -- FAILURE
1228 -- FATAL
1229 --x_detailed_results
1230 -- Object table that stores distribution specific
1231 -- reporting information for the encumbrance transaction
1232 --Testing:
1233 --
1234 --End of Comments
1235 -------------------------------------------------------------------------------
1236 PROCEDURE do_liquidate(
1237 p_api_version IN NUMBER
1238 , p_commit IN VARCHAR2
1239 default FND_API.G_FALSE
1240 , p_init_msg_list IN VARCHAR2
1241 default FND_API.G_FALSE
1242 , p_validation_level IN NUMBER
1243 default FND_API.G_VALID_LEVEL_FULL
1244 , x_return_status OUT NOCOPY VARCHAR2
1245 , p_doc_level IN VARCHAR2
1246 , p_doc_level_id_tbl IN po_tbl_number
1247 , p_employee_id IN NUMBER
1248 , p_override_funds IN VARCHAR2
1249 , p_use_gl_date IN VARCHAR2
1250 , p_override_date IN DATE
1251 , p_report_successes IN VARCHAR2
1252 , x_po_return_code OUT NOCOPY VARCHAR2
1253 , x_detailed_results OUT NOCOPY po_fcout_type
1254 )
1255 IS
1256
1257 l_api_name CONSTANT VARCHAR2(30) := 'DO_LIQUIDATE';
1258 l_log_head CONSTANT VARCHAR2(100) := c_log_head || l_api_name;
1259 l_progress VARCHAR2(3) := '000';
1260 l_doc_level_id NUMBER;
1261 l_amendment_inprogress VARCHAR2(1) := 'N';
1262 l_award_mod_inprogress VARCHAR2(1) := 'N';
1263 l_error_msg po_tbl_varchar2000;
1264
1265 BEGIN
1266
1267 IF g_debug_stmt THEN
1268 PO_DEBUG.debug_begin(l_log_head);
1269 PO_DEBUG.debug_var(l_log_head,l_progress,'p_api_version',p_api_version);
1270 PO_DEBUG.debug_var(l_log_head,l_progress,'p_commit',p_commit);
1271 PO_DEBUG.debug_var(l_log_head,l_progress,'p_init_msg_list',p_init_msg_list);
1272 PO_DEBUG.debug_var(l_log_head,l_progress,'p_validation_level',p_validation_level);
1273 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_level',p_doc_level);
1274 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_level_id_tbl',p_doc_level_id_tbl);
1275 PO_DEBUG.debug_var(l_log_head,l_progress,'p_employee_id',p_employee_id);
1276 PO_DEBUG.debug_var(l_log_head,l_progress,'p_override_funds',p_override_funds);
1277 PO_DEBUG.debug_var(l_log_head,l_progress,'p_use_gl_date',p_use_gl_date);
1278 PO_DEBUG.debug_var(l_log_head,l_progress,'p_override_date',p_override_date);
1279 PO_DEBUG.debug_var(l_log_head,l_progress,'p_report_successes',p_report_successes);
1280 END IF;
1281
1282 l_progress := '010';
1283 l_error_msg := po_tbl_varchar2000();
1284 l_error_msg.EXTEND(1);
1285
1286 FOR i IN 1..p_doc_level_id_tbl.COUNT LOOP
1287
1288 l_progress := '020';
1289 BEGIN
1290 SELECT 'Y'
1291 INTO l_amendment_inprogress
1292 FROM po_requisition_headers_all
1293 WHERE conformed_header_id = p_doc_level_id_tbl(i)
1294 AND Nvl(authorization_status,'INCOMPLETE') NOT IN ('APPROVED','REJECTED')
1295 AND ROWNUM = 1;
1296 EXCEPTION
1297 WHEN NO_DATA_FOUND THEN
1298 IF g_debug_stmt THEN
1299 PO_DEBUG.debug_stmt(l_log_head,l_progress,'There is no Active PR amendment in progress for the this PR');
1300 END IF;
1301 END;
1302
1303 l_progress := '030';
1304 BEGIN
1305 SELECT 'Y'
1306 INTO l_award_mod_inprogress
1307 FROM po_clmreq_line_awd_refs_v
1308 WHERE requisition_header_id = p_doc_level_id_tbl(i)
1309 AND Nvl(authorization_status,'INCOMPLETE') NOT IN ('APPROVED','REJECTED')
1310 AND ROWNUM = 1;
1311 EXCEPTION
1312 WHEN NO_DATA_FOUND THEN
1313 IF g_debug_stmt THEN
1314 PO_DEBUG.debug_stmt(l_log_head,l_progress,'There is no Draft Award or Draft Modification document for this PR');
1315 END IF;
1316 END;
1317
1318 l_progress := '030';
1319 IF g_debug_stmt THEN
1320 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_level_id_tbl(i)',p_doc_level_id_tbl(i));
1321 PO_DEBUG.debug_var(l_log_head,l_progress,'l_amendment_inprogress',l_amendment_inprogress);
1322 PO_DEBUG.debug_var(l_log_head,l_progress,'l_award_mod_inprogress',l_award_mod_inprogress);
1323 END IF;
1324
1325 IF l_amendment_inprogress = 'Y' THEN
1326 l_progress := '040';
1327 x_return_status := FND_API.g_ret_sts_ERROR;
1328 x_po_return_code := g_return_FAILURE;
1329 l_error_msg(i) := 'PO_ACTIVE_AMD_IN_PROGRESS';
1330
1331 ELSIF l_award_mod_inprogress = 'Y' THEN
1332 l_progress := '050';
1333 x_return_status := FND_API.g_ret_sts_ERROR;
1334 x_po_return_code := g_return_FAILURE;
1335 l_error_msg(i) := 'PO_POR_MAPPED_DFT_AWARD_MOD';
1336
1337 ELSE
1338
1339 l_progress := '060';
1340 do_unreserve(
1341 p_api_version => p_api_version,
1342 p_commit => p_commit,
1343 p_init_msg_list => p_init_msg_list,
1344 p_validation_level => p_validation_level,
1345 x_return_status => x_return_status,
1346 p_doc_type => PO_DOCUMENT_FUNDS_GRP.g_doc_type_REQUISITION,
1347 p_doc_subtype => NULL,
1348 p_doc_level => p_doc_level,
1349 p_doc_level_id_tbl => p_doc_level_id_tbl,
1350 p_override_funds => p_override_funds,
1351 p_employee_id => p_employee_id,
1352 p_use_gl_date => p_use_gl_date,
1353 p_override_date => p_override_date,
1354 p_report_successes => p_report_successes,
1355 x_po_return_code => x_po_return_code,
1356 x_detailed_results => x_detailed_results);
1357
1358 l_progress := '070';
1359 -- let an expected error bubble up to the caller.
1360 IF ( x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
1361 OR x_return_status IS NULL
1362 ) THEN
1363 l_progress := '080';
1364 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1365 END IF;
1366 END IF;
1367 END LOOP;
1368
1369 x_detailed_results := po_fcout_type(null,null,null,null,null,null,null,l_error_msg);
1370 IF g_debug_stmt THEN
1371 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
1372 PO_DEBUG.debug_var(l_log_head,l_progress,'x_po_return_code',x_po_return_code);
1373 PO_DEBUG.debug_var(l_log_head,l_progress,'l_error_msg',l_error_msg);
1374 PO_DEBUG.debug_end(l_log_head);
1375 END IF;
1376
1377 EXCEPTION
1378 WHEN OTHERS THEN
1379 BEGIN
1380 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS Start');
1381 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1382 IF g_debug_stmt THEN
1383 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
1384 PO_DEBUG.debug_var(l_log_head,l_progress,'x_po_return_code',x_po_return_code);
1385 END IF;
1386 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1387 FND_MSG_PUB.add_exc_msg(G_PKG_NAME,l_api_name,SQLERRM);
1388 END IF;
1389 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS End');
1390 EXCEPTION
1391 WHEN OTHERS THEN
1392 NULL;
1393 END;
1394
1395 END do_liquidate;
1396
1397 -------------------------------------------------------------------------------
1398 --Start of Comments
1399 --Name: do_return
1400 --Pre-reqs:
1401 -- None.
1402 --Modifies:
1403 -- Creates encumbrance entries in the gl_bc_packets table
1404 -- Adds distribution-specific transaction information into the
1405 -- po_online_report_text table
1406 --Locks:
1407 -- None.
1408 --Function:
1409 -- This procedure unreserves encumbrance on a Requisition that has
1410 -- been returned
1411 --Parameters:
1412 --IN:
1413 --p_api_version
1414 -- Apps API Std - To control correct version in use
1415 --p_commit
1416 -- Apps API Std - Should data be commited?
1417 --p_init_msg_list
1418 -- Apps API Std - Initialize the message list?
1419 --p_validation_level
1420 -- Apps API Std - Level of validations to be done
1421 --p_doc_type
1422 -- Differentiates between the doc being a REQ, PA, PO, or RELEASE,
1423 -- which is used to identify the tables to look at (PO vs. Req)
1424 -- and the join conditions
1425 --p_doc_subtype
1426 -- Differentiates between the subtypes of documents
1427 -- REQ: NULL
1428 -- PO: STANDARD, PLANNED
1429 -- PA: CONTRACT, BLANKET
1430 -- RELEASE: SCHEDULED, BLANKET
1431 --p_doc_level
1432 -- Specifies the level of the document that is being checked:
1433 -- HEADER, LINE, SHIPMENT, DISTRIBUTION
1434 --p_doc_level_id
1435 -- The id corresponding to the doc level type:
1436 -- header_id/release_id, line_id, line_location_id, distribution_id
1437 --p_override_date
1438 -- Caller-specified date to use for distribution encumbrance date in GL entries
1439 --p_use_gl_date
1440 -- Flag that specifies whether to always prefer using the existing distribution
1441 -- GL date instead of the override date, when possible
1442 --p_report_successes
1443 -- Determines whether the x_detailed_results object contains information about
1444 -- distributions for which encumbrance actions were successful. Information for
1445 -- distributions with Warnings and Errors are always included.
1446 --OUT:
1447 --x_return_status
1448 -- FND_API.G_RET_STS_SUCCESS if validation succeeds
1449 -- FND_API.G_RET_STS_ERROR if validation fails
1450 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
1451 --x_detailed_results
1452 -- Object table that stores distribution specific
1453 -- reporting information for the encumbrance transaction
1454 --x_po_return_code
1455 -- Indicates whether PO is classifying this transaction as an
1456 -- Error/Warning/Partial Success/Success
1457 --Testing:
1458 --
1459 --End of Comments
1460 -------------------------------------------------------------------------------
1461 PROCEDURE do_return(
1462 p_api_version IN NUMBER
1463 , p_commit IN VARCHAR2 default FND_API.G_FALSE
1464 , p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE
1465 , p_validation_level IN NUMBER default FND_API.G_VALID_LEVEL_FULL
1466 , x_return_status OUT NOCOPY VARCHAR2
1467 , p_doc_type IN VARCHAR2
1468 , p_doc_subtype IN VARCHAR2
1469 , p_doc_level IN VARCHAR2
1470 , p_doc_level_id IN NUMBER
1471 , p_override_date IN DATE
1472 , p_use_gl_date IN VARCHAR2
1473 , p_report_successes IN VARCHAR2
1474 , x_po_return_code OUT NOCOPY VARCHAR2
1475 , x_detailed_results OUT NOCOPY po_fcout_type
1476 )
1477 IS
1478
1479 l_api_name CONSTANT varchar2(30) := 'DO_RETURN';
1480 l_log_head CONSTANT VARCHAR2(100) := c_log_head || l_api_name;
1481 l_api_version CONSTANT NUMBER := 1.0;
1482 l_progress VARCHAR2(3) := '000';
1483 l_report_return_status VARCHAR2(1);
1484 l_online_report_id NUMBER;
1485
1486 BEGIN
1487
1488 IF g_debug_stmt THEN
1489 PO_DEBUG.debug_begin(l_log_head);
1490 PO_DEBUG.debug_var(l_log_head,l_progress,'p_api_version',p_api_version);
1491 PO_DEBUG.debug_var(l_log_head,l_progress,'p_commit',p_commit);
1492 PO_DEBUG.debug_var(l_log_head,l_progress,'p_init_msg_list',p_init_msg_list);
1493 PO_DEBUG.debug_var(l_log_head,l_progress,'p_validation_level',p_validation_level);
1494 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_type',p_doc_type);
1495 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_subtype',p_doc_subtype);
1496 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_level',p_doc_level);
1497 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_level_id',p_doc_level_id);
1498 PO_DEBUG.debug_var(l_log_head,l_progress,'p_override_date',p_override_date);
1499 PO_DEBUG.debug_var(l_log_head,l_progress,'p_use_gl_date',p_use_gl_date);
1500 PO_DEBUG.debug_var(l_log_head,l_progress,'p_report_successes',p_report_successes);
1501 END IF;
1502
1503 l_progress := '010';
1504
1505 -- Standard Start of API savepoint
1506 SAVEPOINT DO_RETURN_SP;
1507
1508 l_progress := '020';
1509
1510 -- Standard call to check for call compatibility
1511
1512 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1513 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1514 END IF;
1515
1516 l_progress := '030';
1517
1518 -- Initialize message list if p_init_msg_list is set to TRUE.
1519
1520 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1521 FND_MSG_PUB.initialize;
1522 END IF;
1523
1524 l_progress := '040';
1525
1526 -- Initialize API return status to success
1527
1528 x_return_status := FND_API.G_RET_STS_SUCCESS;
1529
1530 l_progress := '050';
1531
1532 IF g_debug_stmt THEN
1533 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Calling Private Procedure');
1534 END IF;
1535
1536 l_progress := '060';
1537
1538 PO_DOCUMENT_FUNDS_PVT.do_return(
1539 x_return_status => x_return_status
1540 , p_doc_type => p_doc_type
1541 , p_doc_subtype => p_doc_subtype
1542 , p_doc_level => p_doc_level
1543 , p_doc_level_id => p_doc_level_id
1544 , p_use_enc_gt_flag => g_parameter_NO
1545 , p_use_gl_date => p_use_gl_date
1546 , p_override_date => p_override_date
1547 , x_po_return_code => x_po_return_code
1548 , x_online_report_id => l_online_report_id
1549 );
1550
1551 l_progress := '100';
1552
1553 IF ( x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
1554 OR x_return_status IS NULL
1555 ) THEN
1556 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1557 END IF;
1558
1559 l_progress := '110';
1560
1561 IF g_debug_stmt THEN
1562 PO_DEBUG.debug_stmt(l_log_head,l_progress,
1563 'Returned from Private Procedure successfully');
1564 END IF;
1565
1566 l_progress := '120';
1567
1568 PO_DOCUMENT_FUNDS_PVT.create_report_object(
1569 x_return_status => l_report_return_status
1570 , p_online_report_id => l_online_report_id
1571 , p_report_successes => p_report_successes
1572 , x_report_object => x_detailed_results
1573 );
1574
1575 l_progress := '200';
1576
1577 IF ( l_report_return_status = FND_API.G_RET_STS_UNEXP_ERROR
1578 OR l_report_return_status IS NULL
1579 ) THEN
1580 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1581 END IF;
1582
1583 l_progress := '300';
1584
1585 -- Standard check of p_commit.
1586 IF FND_API.To_Boolean(p_commit) THEN
1587 COMMIT WORK;
1588 END IF;
1589
1590 l_progress := '900';
1591
1592 IF g_debug_stmt THEN
1593 PO_DEBUG.debug_var(l_log_head,l_progress,'l_report_return_status',l_report_return_status);
1594 PO_DEBUG.debug_var(l_log_head,l_progress,'l_online_report_id',l_online_report_id);
1595 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
1596 PO_DEBUG.debug_var(l_log_head,l_progress,'x_po_return_code',x_po_return_code);
1597 PO_DEBUG.debug_end(l_log_head);
1598 END IF;
1599
1600 EXCEPTION
1601 WHEN OTHERS THEN
1602 BEGIN
1603 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS Start');
1604 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1605 IF g_debug_stmt THEN
1606 PO_DEBUG.debug_var(l_log_head,l_progress,'l_report_return_status',l_report_return_status);
1607 PO_DEBUG.debug_var(l_log_head,l_progress,'l_online_report_id',l_online_report_id);
1608 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
1609 PO_DEBUG.debug_var(l_log_head,l_progress,'x_po_return_code',x_po_return_code);
1610 END IF;
1611 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1612 FND_MSG_PUB.add_exc_msg(G_PKG_NAME,l_api_name,SQLERRM);
1613 END IF;
1614 ROLLBACK TO DO_RETURN_SP;
1615 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS End');
1616 EXCEPTION
1617 WHEN OTHERS THEN
1618 NULL;
1619 END;
1620
1621 END do_return;
1622
1623
1624 -------------------------------------------------------------------------------
1625 --Start of Comments
1626 --Name: do_reject
1627 --Pre-reqs:
1628 -- None.
1629 --Modifies:
1630 -- Creates encumbrance entries in the gl_bc_packets table
1631 -- Adds distribution-specific transaction information into the
1632 -- po_online_report_text table
1633 --Locks:
1634 -- None.
1635 --Function:
1636 -- This procedure unreserves encumbrance from unapproved shipments on
1637 -- a document that has been rejected
1638 --Parameters:
1639 --IN:
1640 --p_api_version
1641 -- Apps API Std - To control correct version in use
1642 --p_commit
1643 -- Apps API Std - Should data be commited?
1644 --p_init_msg_list
1645 -- Apps API Std - Initialize the message list?
1646 --p_validation_level
1647 -- Apps API Std - Level of validations to be done
1648 --p_doc_type
1649 -- Differentiates between the doc being a REQ, PA, PO, or RELEASE,
1650 -- which is used to identify the tables to look at (PO vs. Req)
1651 -- and the join conditions
1652 --p_doc_subtype
1653 -- Differentiates between the subtypes of documents
1654 -- REQ: NULL
1655 -- PO: STANDARD, PLANNED
1656 -- PA: CONTRACT, BLANKET
1657 -- RELEASE: SCHEDULED, BLANKET
1658 --p_doc_level
1659 -- Specifies the level of the document that is being checked:
1660 -- For Reject, this should always be g_HEADER
1661 --p_doc_level_id
1662 -- The id corresponding to the doc level type:
1663 -- For Reject, this should always be header_id (or release_id for Releases)
1664 --p_override_funds
1665 -- Indicates whether funds override capability can be used if needed, to make a
1666 -- transaction succeed.
1667 --p_use_gl_date
1668 -- Flag that specifies whether to always prefer using the existing distribution
1669 -- GL date instead of the override date, when possible
1670 --p_override_date
1671 -- Caller-specified date to use for distribution encumbrance date in GL entries
1672 --p_report_successes
1673 -- Determines whether the x_detailed_results object contains information about
1674 -- distributions for which encumbrance actions were successful. Information for
1675 -- distributions with Warnings and Errors are always included.
1676 --OUT:
1677 --x_return_status
1678 -- FND_API.G_RET_STS_SUCCESS if validation succeeds
1679 -- FND_API.G_RET_STS_ERROR if validation fails
1680 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
1681 --x_detailed_results
1682 -- Object table that stores distribution specific
1683 -- reporting information for the encumbrance transaction
1684 --x_po_return_code
1685 -- Indicates whether PO is classifying this transaction as an
1686 -- Error/Warning/Partial Success/Success
1687 --Testing:
1688 --
1689 --End of Comments
1690 -------------------------------------------------------------------------------
1691 PROCEDURE do_reject(
1692 p_api_version IN NUMBER
1693 , p_commit IN VARCHAR2 default FND_API.G_FALSE
1694 , p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE
1695 , p_validation_level IN NUMBER default FND_API.G_VALID_LEVEL_FULL
1696 , x_return_status OUT NOCOPY VARCHAR2
1697 , p_doc_type IN VARCHAR2
1698 , p_doc_subtype IN VARCHAR2
1699 , p_doc_level IN VARCHAR2
1700 , p_doc_level_id IN NUMBER
1701 , p_override_funds IN VARCHAR2
1702 , p_use_gl_date IN VARCHAR2
1703 , p_override_date IN DATE
1704 , p_report_successes IN VARCHAR2
1705 , x_po_return_code OUT NOCOPY VARCHAR2
1706 , x_detailed_results OUT NOCOPY po_fcout_type
1707 )
1708 IS
1709
1710 l_api_name CONSTANT varchar2(30) := 'DO_REJECT';
1711 l_api_version CONSTANT NUMBER := 1.0;
1712 l_log_head CONSTANT VARCHAR2(100) := c_log_head || l_api_name;
1713 l_progress VARCHAR2(3) := '000';
1714 l_report_return_status VARCHAR2(1);
1715 l_online_report_id NUMBER;
1716
1717 BEGIN
1718
1719 IF g_debug_stmt THEN
1720 PO_DEBUG.debug_begin(l_log_head);
1721 PO_DEBUG.debug_var(l_log_head,l_progress,'p_api_version',p_api_version);
1722 PO_DEBUG.debug_var(l_log_head,l_progress,'p_commit',p_commit);
1723 PO_DEBUG.debug_var(l_log_head,l_progress,'p_init_msg_list',p_init_msg_list);
1724 PO_DEBUG.debug_var(l_log_head,l_progress,'p_validation_level',p_validation_level);
1725 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_type',p_doc_type);
1726 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_subtype',p_doc_subtype);
1727 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_level',p_doc_level);
1728 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_level_id',p_doc_level_id);
1729 PO_DEBUG.debug_var(l_log_head,l_progress,'p_override_funds',p_override_funds);
1730 PO_DEBUG.debug_var(l_log_head,l_progress,'p_use_gl_date',p_use_gl_date);
1731 PO_DEBUG.debug_var(l_log_head,l_progress,'p_override_date',p_override_date);
1732 PO_DEBUG.debug_var(l_log_head,l_progress,'p_report_successes',p_report_successes);
1733 END IF;
1734
1735 l_progress := '010';
1736
1737 -- Standard Start of API savepoint
1738 SAVEPOINT DO_REJECT_SP;
1739
1740 l_progress := '020';
1741
1742 -- Standard call to check for call compatibility
1743
1744 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1745 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1746 END IF;
1747
1748 l_progress := '030';
1749
1750 -- Initialize message list if p_init_msg_list is set to TRUE.
1751
1752 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1753 FND_MSG_PUB.initialize;
1754 END IF;
1755
1756 l_progress := '040';
1757
1758 -- Initialize API return status to success
1759
1760 x_return_status := FND_API.G_RET_STS_SUCCESS;
1761
1762 l_progress := '050';
1763
1764 IF g_debug_stmt THEN
1765 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Calling Private Procedure');
1766 END IF;
1767
1768 l_progress := '060';
1769
1770 PO_DOCUMENT_FUNDS_PVT.do_reject(
1771 x_return_status => x_return_status
1772 , p_doc_type => p_doc_type
1773 , p_doc_subtype => p_doc_subtype
1774 , p_doc_level => p_doc_level
1775 , p_doc_level_id => p_doc_level_id
1776 , p_use_enc_gt_flag => g_parameter_NO
1777 , p_override_funds => p_override_funds
1778 , p_use_gl_date => p_use_gl_date
1779 , p_override_date => p_override_date
1780 , x_po_return_code => x_po_return_code
1781 , x_online_report_id => l_online_report_id
1782 );
1783
1784 l_progress := '100';
1785
1786 IF ( x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
1787 OR x_return_status IS NULL
1788 ) THEN
1789 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1790 END IF;
1791
1792 l_progress := '110';
1793
1794 IF g_debug_stmt THEN
1795 PO_DEBUG.debug_stmt(l_log_head,l_progress,
1796 'Returned from Private Procedure successfully');
1797 END IF;
1798
1799 l_progress := '120';
1800
1801 PO_DOCUMENT_FUNDS_PVT.create_report_object(
1802 x_return_status => l_report_return_status
1803 , p_online_report_id => l_online_report_id
1804 , p_report_successes => p_report_successes
1805 , x_report_object => x_detailed_results
1806 );
1807
1808 l_progress := '200';
1809
1810 IF ( l_report_return_status = FND_API.G_RET_STS_UNEXP_ERROR
1811 OR l_report_return_status IS NULL
1812 ) THEN
1813 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1814 END IF;
1815
1816 l_progress := '300';
1817
1818 -- Standard check of p_commit.
1819 IF FND_API.To_Boolean(p_commit) THEN
1820 COMMIT WORK;
1821 END IF;
1822
1823 l_progress := '900';
1824
1825 IF g_debug_stmt THEN
1826 PO_DEBUG.debug_var(l_log_head,l_progress,'l_report_return_status',l_report_return_status);
1827 PO_DEBUG.debug_var(l_log_head,l_progress,'l_online_report_id',l_online_report_id);
1828 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
1829 PO_DEBUG.debug_var(l_log_head,l_progress,'x_po_return_code',x_po_return_code);
1830 PO_DEBUG.debug_end(l_log_head);
1831 END IF;
1832
1833 EXCEPTION
1834 WHEN OTHERS THEN
1835 BEGIN
1836 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS Start');
1837 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1838 IF g_debug_stmt THEN
1839 PO_DEBUG.debug_var(l_log_head,l_progress,'l_report_return_status',l_report_return_status);
1840 PO_DEBUG.debug_var(l_log_head,l_progress,'l_online_report_id',l_online_report_id);
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 END IF;
1844 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1845 FND_MSG_PUB.add_exc_msg(G_PKG_NAME,l_api_name,SQLERRM);
1846 END IF;
1847 ROLLBACK TO DO_REJECT_SP;
1848 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS End');
1849 EXCEPTION
1850 WHEN OTHERS THEN
1851 NULL;
1852 END;
1853
1854 END do_reject;
1855
1856
1857 -------------------------------------------------------------------------------
1858 --Start of Comments
1859 --Name: do_cancel
1860 --Pre-reqs:
1861 -- The cancel code must already have set the cancel_flag on the relevant entity
1862 -- to be 'I' (is this always required?)
1863 -- The cancel code must have already created a new Req distribution if Recreate
1864 -- (and hence re-encumber) Demand is requested.
1865 --Modifies:
1866 -- Creates encumbrance entries in the gl_bc_packets table
1867 -- Adds distribution-specific transaction information into the
1868 -- po_online_report_text table
1869 --Locks:
1870 -- None.
1871 --Function:
1872 -- This procedure cancels the encumbrance on all eligible distributions of
1873 -- the requested document or document subentity
1874 --Parameters:
1875 --IN:
1876 --p_api_version
1877 -- Apps API Std - To control correct version in use
1878 --p_commit
1879 -- Apps API Std - Should data be commited?
1880 --p_init_msg_list
1881 -- Apps API Std - Initialize the message list?
1882 --p_validation_level
1883 -- Apps API Std - Level of validations to be done
1884 --p_doc_type
1885 -- Differentiates between the doc being a REQ, PA, PO, or RELEASE,
1886 -- which is used to identify the tables to look at (PO vs. Req)
1887 -- and the join conditions
1888 --p_doc_subtype
1889 -- Differentiates between the subtypes of documents
1890 -- REQ: NULL
1891 -- PO: STANDARD, PLANNED
1892 -- PA: CONTRACT, BLANKET
1893 -- RELEASE: SCHEDULED, BLANKET
1894 --p_doc_level
1895 -- Specifies the level of the document that is being checked:
1896 -- HEADER, LINE, SHIPMENT, DISTRIBUTION
1897 --p_doc_level_id
1898 -- The id corresponding to the doc level type:
1899 -- header_id/release_id, line_id, line_location_id, distribution_id
1900 --p_override_date
1901 -- Caller-specified date to use for distribution encumbrance date in GL entries
1902 --p_use_gl_date
1903 -- Flag that specifies whether to always prefer using the existing distribution
1904 -- GL date instead of the override date, when possible
1905 --p_override_funds
1906 -- Indicates whether funds override capability can be used if needed, to make a
1907 -- transaction succeed.
1908 --p_report_successes
1909 -- Determines whether the x_detailed_results object contains information about
1910 -- distributions for which encumbrance actions were successful. Information for
1911 -- distributions with Warnings and Errors are always included.
1912 --OUT:
1913 --x_return_status
1914 -- FND_API.G_RET_STS_SUCCESS if validation succeeds
1915 -- FND_API.G_RET_STS_ERROR if validation fails
1916 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
1917 --x_detailed_results
1918 -- Object table that stores distribution specific
1919 -- reporting information for the encumbrance transaction
1920 --x_po_return_code
1921 -- Indicates whether PO is classifying this transaction as an
1922 -- Error/Warning/Partial Success/Success
1923 --Testing:
1924 --
1925 --End of Comments
1926 -------------------------------------------------------------------------------
1927 PROCEDURE do_cancel(
1928 p_api_version IN NUMBER
1929 , p_commit IN VARCHAR2 default FND_API.G_FALSE
1930 , p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE
1931 , p_validation_level IN NUMBER default FND_API.G_VALID_LEVEL_FULL
1932 , x_return_status OUT NOCOPY VARCHAR2
1933 , p_doc_type IN VARCHAR2
1934 , p_doc_subtype IN VARCHAR2
1935 , p_doc_level IN VARCHAR2
1936 , p_doc_level_id IN NUMBER
1937 , p_override_funds IN VARCHAR2
1938 , p_use_gl_date IN VARCHAR2
1939 , p_override_date IN DATE
1940 , p_report_successes IN VARCHAR2
1941 , x_po_return_code OUT NOCOPY VARCHAR2
1942 , x_detailed_results OUT NOCOPY po_fcout_type
1943 )
1944 IS
1945
1946 l_api_name CONSTANT varchar2(30) := 'DO_CANCEL';
1947 l_api_version CONSTANT NUMBER := 1.0;
1948 l_log_head CONSTANT VARCHAR2(100) := c_log_head || l_api_name;
1949 l_progress VARCHAR2(3) := '000';
1950 l_report_return_status VARCHAR2(1);
1951 l_online_report_id NUMBER;
1952
1953 BEGIN
1954
1955 IF g_debug_stmt THEN
1956 PO_DEBUG.debug_begin(l_log_head);
1957 PO_DEBUG.debug_var(l_log_head,l_progress,'p_api_version',p_api_version);
1958 PO_DEBUG.debug_var(l_log_head,l_progress,'p_commit',p_commit);
1959 PO_DEBUG.debug_var(l_log_head,l_progress,'p_init_msg_list',p_init_msg_list);
1960 PO_DEBUG.debug_var(l_log_head,l_progress,'p_validation_level',p_validation_level);
1961 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_type',p_doc_type);
1962 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_subtype',p_doc_subtype);
1963 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_level',p_doc_level);
1964 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_level_id',p_doc_level_id);
1965 PO_DEBUG.debug_var(l_log_head,l_progress,'p_override_funds',p_override_funds);
1966 PO_DEBUG.debug_var(l_log_head,l_progress,'p_use_gl_date',p_use_gl_date);
1967 PO_DEBUG.debug_var(l_log_head,l_progress,'p_override_date',p_override_date);
1968 PO_DEBUG.debug_var(l_log_head,l_progress,'p_report_successes',p_report_successes);
1969 END IF;
1970
1971 l_progress := '010';
1972
1973 -- Standard Start of API savepoint
1974 SAVEPOINT DO_CANCEL_SP;
1975
1976 l_progress := '020';
1977
1978 -- Standard call to check for call compatibility
1979
1980 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1981 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1982 END IF;
1983
1984 l_progress := '030';
1985
1986 -- Initialize message list if p_init_msg_list is set to TRUE.
1987
1988 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1989 FND_MSG_PUB.initialize;
1990 END IF;
1991
1992 l_progress := '040';
1993
1994 -- Initialize API return status to success
1995
1996 x_return_status := FND_API.G_RET_STS_SUCCESS;
1997
1998 l_progress := '050';
1999
2000 IF g_debug_stmt THEN
2001 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Calling Private Procedure');
2002 END IF;
2003
2004 l_progress := '060';
2005
2006 PO_DOCUMENT_FUNDS_PVT.do_cancel(
2007 x_return_status => x_return_status
2008 , p_doc_type => p_doc_type
2009 , p_doc_subtype => p_doc_subtype
2010 , p_doc_level => p_doc_level
2011 , p_doc_level_id => p_doc_level_id
2012 , p_use_enc_gt_flag => g_parameter_NO
2013 , p_override_funds => p_override_funds
2014 , p_use_gl_date => p_use_gl_date
2015 , p_override_date => p_override_date
2016 , x_po_return_code => x_po_return_code
2017 , x_online_report_id => l_online_report_id
2018 );
2019
2020 l_progress := '100';
2021
2022 IF ( x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
2023 OR x_return_status IS NULL
2024 ) THEN
2025 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2026 END IF;
2027
2028 l_progress := '110';
2029
2030 IF g_debug_stmt THEN
2031 PO_DEBUG.debug_stmt(l_log_head,l_progress,
2032 'Returned from Private Procedure successfully');
2033 END IF;
2034
2035 l_progress := '120';
2036
2037 PO_DOCUMENT_FUNDS_PVT.create_report_object(
2038 x_return_status => l_report_return_status
2039 , p_online_report_id => l_online_report_id
2040 , p_report_successes => p_report_successes
2041 , x_report_object => x_detailed_results
2042 );
2043
2044 l_progress := '200';
2045
2046 IF ( l_report_return_status = FND_API.G_RET_STS_UNEXP_ERROR
2047 OR l_report_return_status IS NULL
2048 ) THEN
2049 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2050 END IF;
2051
2052 l_progress := '300';
2053
2054 -- Standard check of p_commit.
2055 IF FND_API.To_Boolean(p_commit) THEN
2056 COMMIT WORK;
2057 END IF;
2058
2059 l_progress := '900';
2060
2061 IF g_debug_stmt THEN
2062 PO_DEBUG.debug_var(l_log_head,l_progress,'l_report_return_status',l_report_return_status);
2063 PO_DEBUG.debug_var(l_log_head,l_progress,'l_online_report_id',l_online_report_id);
2064 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
2065 PO_DEBUG.debug_var(l_log_head,l_progress,'x_po_return_code',x_po_return_code);
2066 PO_DEBUG.debug_end(l_log_head);
2067 END IF;
2068
2069 EXCEPTION
2070 WHEN OTHERS THEN
2071 BEGIN
2072 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS Start');
2073 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2074 IF g_debug_stmt THEN
2075 PO_DEBUG.debug_var(l_log_head,l_progress,'l_report_return_status',l_report_return_status);
2076 PO_DEBUG.debug_var(l_log_head,l_progress,'l_online_report_id',l_online_report_id);
2077 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
2078 PO_DEBUG.debug_var(l_log_head,l_progress,'x_po_return_code',x_po_return_code);
2079 END IF;
2080 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2081 FND_MSG_PUB.add_exc_msg(G_PKG_NAME,l_api_name,SQLERRM);
2082 END IF;
2083 ROLLBACK TO DO_CANCEL_SP;
2084 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS End');
2085 EXCEPTION
2086 WHEN OTHERS THEN
2087 NULL;
2088 END;
2089
2090 END do_cancel;
2091
2092
2093
2094 -------------------------------------------------------------------------------
2095 --Start of Comments
2096 --Name: do_adjust
2097 --Pre-reqs:
2098 -- N/A.
2099 --Modifies:
2100 -- Creates encumbrance entries in the gl_bc_packets table
2101 -- Adds distribution-specific transaction information into the
2102 -- po_online_report_text table
2103 --Locks:
2104 -- None.
2105 --Function:
2106 -- This procedure cancels the encumbrance on all eligible distributions of
2107 -- the requested document or document subentity
2108 --Parameters:
2109 --IN:
2110 --p_api_version
2111 -- Apps API Std - To control correct version in use
2112 --p_commit
2113 -- Apps API Std - Should data be commited?
2114 --p_init_msg_list
2115 -- Apps API Std - Initialize the message list?
2116 --p_validation_level
2117 -- Apps API Std - Level of validations to be done
2118 --p_doc_type
2119 -- Differentiates between the doc being a REQ, PA, PO, or RELEASE,
2120 -- which is used to identify the tables to look at (PO vs. Req)
2121 -- and the join conditions
2122 --p_doc_subtype
2123 -- Differentiates between the subtypes of documents
2124 -- REQ: NULL
2125 -- PO: STANDARD, PLANNED
2126 -- PA: CONTRACT, BLANKET
2127 -- RELEASE: SCHEDULED, BLANKET
2128 --p_employee_id
2129 -- Employee Id of the user taking the action
2130 --p_override_funds
2131 -- Indicates whether funds override capability can be used if needed, to make a
2132 -- transaction succeed.
2133 --p_use_gl_date
2134 -- Flag that specifies whether to always prefer using the existing distribution
2135 -- GL date instead of the override date, when possible
2136 --p_override_date
2137 -- Caller-specified date to use for distribution encumbrance date in GL entries
2138 --p_report_successes
2139 -- Determines whether the x_detailed_results object contains information about
2140 -- distributions for which encumbrance actions were successful. Information for
2141 -- distributions with Warnings and Errors are always included.
2142 --OUT:
2143 --x_return_status
2144 -- FND_API.G_RET_STS_SUCCESS if validation succeeds
2145 -- FND_API.G_RET_STS_ERROR if validation fails
2146 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
2147 --x_detailed_results
2148 -- Object table that stores distribution specific
2149 -- reporting information for the encumbrance transaction
2150 --x_po_return_code
2151 -- Indicates whether PO is classifying this transaction as an
2152 -- Error/Warning/Partial Success/Success
2153 --Testing:
2154 --
2155 --End of Comments
2156 -------------------------------------------------------------------------------
2157 PROCEDURE do_adjust(
2158 p_api_version IN NUMBER
2159 , p_commit IN VARCHAR2 default FND_API.G_FALSE
2160 , p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE
2161 , p_validation_level IN NUMBER default FND_API.G_VALID_LEVEL_FULL
2162 , x_return_status OUT NOCOPY VARCHAR2
2163 , p_doc_type IN VARCHAR2
2164 , p_doc_subtype IN VARCHAR2
2165 , p_employee_id IN NUMBER
2166 , p_override_funds IN VARCHAR2
2167 , p_use_gl_date IN VARCHAR2
2168 , p_override_date IN DATE
2169 , p_report_successes IN VARCHAR2
2170 , x_po_return_code OUT NOCOPY VARCHAR2
2171 , x_detailed_results OUT NOCOPY po_fcout_type
2172 )
2173 IS
2174
2175 l_api_name CONSTANT varchar2(30) := 'DO_ADJUST';
2176 l_api_version CONSTANT NUMBER := 1.0;
2177 l_log_head CONSTANT VARCHAR2(100) := c_log_head || l_api_name;
2178 l_progress VARCHAR2(3) := '000';
2179 l_report_return_status VARCHAR2(1);
2180 l_online_report_id NUMBER;
2181
2182 BEGIN
2183
2184 IF g_debug_stmt THEN
2185 PO_DEBUG.debug_begin(l_log_head);
2186 PO_DEBUG.debug_var(l_log_head,l_progress,'p_api_version',p_api_version);
2187 PO_DEBUG.debug_var(l_log_head,l_progress,'p_commit',p_commit);
2188 PO_DEBUG.debug_var(l_log_head,l_progress,'p_init_msg_list',p_init_msg_list);
2189 PO_DEBUG.debug_var(l_log_head,l_progress,'p_validation_level',p_validation_level);
2190 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_type',p_doc_type);
2191 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_subtype',p_doc_subtype);
2192 PO_DEBUG.debug_var(l_log_head,l_progress,'p_employee_id',p_employee_id);
2193 PO_DEBUG.debug_var(l_log_head,l_progress,'p_override_funds',p_override_funds);
2194 PO_DEBUG.debug_var(l_log_head,l_progress,'p_use_gl_date',p_use_gl_date);
2195 PO_DEBUG.debug_var(l_log_head,l_progress,'p_override_date',p_override_date);
2196 PO_DEBUG.debug_var(l_log_head,l_progress,'p_report_successes',p_report_successes);
2197 END IF;
2198
2199 l_progress := '010';
2200
2201 -- Standard Start of API savepoint
2202 SAVEPOINT DO_ADJUST_SP;
2203
2204 l_progress := '020';
2205
2206 -- Standard call to check for call compatibility
2207
2208 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
2209 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2210 END IF;
2211
2212 l_progress := '030';
2213
2214 -- Initialize message list if p_init_msg_list is set to TRUE.
2215
2216 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2217 FND_MSG_PUB.initialize;
2218 END IF;
2219
2220 l_progress := '040';
2221
2222 -- Initialize API return status to success
2223
2224 x_return_status := FND_API.G_RET_STS_SUCCESS;
2225
2226 l_progress := '050';
2227
2228 IF g_debug_stmt THEN
2229 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Calling Private Procedure');
2230 END IF;
2231
2232 l_progress := '060';
2233
2234 PO_DOCUMENT_FUNDS_PVT.do_adjust(
2235 x_return_status => x_return_status
2236 , p_doc_type => p_doc_type
2237 , p_doc_subtype => p_doc_subtype
2238 , p_override_funds => p_override_funds
2239 , p_use_gl_date => p_use_gl_date
2240 , p_validate_document => g_parameter_YES
2241 , p_override_date => p_override_date
2242 , p_employee_id => p_employee_id
2243 , x_po_return_code => x_po_return_code
2244 , x_online_report_id => l_online_report_id
2245 );
2246
2247 l_progress := '100';
2248
2249 IF ( x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
2250 OR x_return_status IS NULL
2251 ) THEN
2252 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2253 END IF;
2254
2255 l_progress := '110';
2256
2257 IF g_debug_stmt THEN
2258 PO_DEBUG.debug_stmt(l_log_head,l_progress,
2259 'Returned from Private Procedure successfully');
2260 END IF;
2261
2262 l_progress := '120';
2263
2264 PO_DOCUMENT_FUNDS_PVT.create_report_object(
2265 x_return_status => l_report_return_status
2266 , p_online_report_id => l_online_report_id
2267 , p_report_successes => p_report_successes
2268 , x_report_object => x_detailed_results
2269 );
2270
2271 l_progress := '200';
2272
2273 IF ( l_report_return_status = FND_API.G_RET_STS_UNEXP_ERROR
2274 OR l_report_return_status IS NULL
2275 ) THEN
2276 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2277 END IF;
2278
2279 l_progress := '300';
2280
2281 -- Standard check of p_commit.
2282 IF FND_API.To_Boolean(p_commit) THEN
2283 COMMIT WORK;
2284 END IF;
2285
2286 l_progress := '900';
2287
2288 IF g_debug_stmt THEN
2289 PO_DEBUG.debug_var(l_log_head,l_progress,'l_report_return_status',l_report_return_status);
2290 PO_DEBUG.debug_var(l_log_head,l_progress,'l_online_report_id',l_online_report_id);
2291 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
2292 PO_DEBUG.debug_var(l_log_head,l_progress,'x_po_return_code',x_po_return_code);
2293 PO_DEBUG.debug_end(l_log_head);
2294 END IF;
2295
2296 EXCEPTION
2297 WHEN OTHERS THEN
2298 BEGIN
2299 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS Start');
2300 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2301 IF g_debug_stmt THEN
2302 PO_DEBUG.debug_var(l_log_head,l_progress,'l_report_return_status',l_report_return_status);
2303 PO_DEBUG.debug_var(l_log_head,l_progress,'l_online_report_id',l_online_report_id);
2304 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
2305 PO_DEBUG.debug_var(l_log_head,l_progress,'x_po_return_code',x_po_return_code);
2306 END IF;
2307 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2308 FND_MSG_PUB.add_exc_msg(G_PKG_NAME,l_api_name,SQLERRM);
2309 END IF;
2310 ROLLBACK TO DO_ADJUST_SP;
2311 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS End');
2312 EXCEPTION
2313 WHEN OTHERS THEN
2314 NULL;
2315 END;
2316
2317 END do_adjust;
2318
2319
2320 -------------------------------------------------------------------------------
2321 --Start of Comments
2322 --Name: do_final_close
2323 --Pre-reqs:
2324 -- N/A.
2325 --Modifies:
2326 -- Creates encumbrance entries in the gl_bc_packets table
2327 -- Adds distribution-specific transaction information into the
2328 -- po_online_report_text table
2329 --Locks:
2330 -- None.
2331 --Function:
2332 -- This procedure liquidates the encumbrance on all eligible distributions of
2333 -- the requested document or document subentity
2334 --Parameters:
2335 --IN:
2336 --p_api_version
2337 -- Apps API Std - To control correct version in use
2338 --p_commit
2339 -- Apps API Std - Should data be commited?
2340 --p_init_msg_list
2341 -- Apps API Std - Initialize the message list?
2342 --p_validation_level
2343 -- Apps API Std - Level of validations to be done
2344 --p_doc_type
2345 -- Differentiates between the doc being a REQ, PA, PO, or RELEASE,
2346 -- which is used to identify the tables to look at (PO vs. Req)
2347 -- and the join conditions
2348 --p_doc_subtype
2349 -- Differentiates between the subtypes of documents
2350 -- REQ: NULL
2351 -- PO: STANDARD, PLANNED
2352 -- PA: CONTRACT, BLANKET
2353 -- RELEASE: SCHEDULED, BLANKET
2354 --p_doc_level
2355 -- Specifies the level of the document that is being checked:
2356 -- HEADER, LINE, SHIPMENT, DISTRIBUTION
2357 --p_doc_level_id
2358 -- The id corresponding to the doc level type:
2359 -- header_id/release_id, line_id, line_location_id, distribution_id
2360 --p_override_date
2361 -- Caller-specified date to use for distribution encumbrance date in GL entries
2362 --p_use_gl_date
2363 -- Flag that specifies whether to always prefer using the existing distribution
2364 -- GL date instead of the override date, when possible
2365 --p_report_successes
2366 -- Determines whether the x_detailed_results object contains information about
2367 -- distributions for which encumbrance actions were successful. Information for
2368 -- distributions with Warnings and Errors are always included.
2369 --OUT:
2370 --x_return_status
2371 -- FND_API.G_RET_STS_SUCCESS if validation succeeds
2372 -- FND_API.G_RET_STS_ERROR if validation fails
2373 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
2374 --x_detailed_results
2375 -- Object table that stores distribution specific
2376 -- reporting information for the encumbrance transaction
2377 --x_po_return_code
2378 -- Indicates whether PO is classifying this transaction as an
2379 -- Error/Warning/Partial Success/Success
2380 --Testing:
2381 --
2382 --End of Comments
2383 -------------------------------------------------------------------------------
2384 PROCEDURE do_final_close(
2385 p_api_version IN NUMBER
2386 , p_commit IN VARCHAR2 default FND_API.G_FALSE
2387 , p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE
2388 , p_validation_level IN NUMBER default FND_API.G_VALID_LEVEL_FULL
2389 , x_return_status OUT NOCOPY VARCHAR2
2390 , p_doc_type IN VARCHAR2
2391 , p_doc_subtype IN VARCHAR2
2392 , p_doc_level IN VARCHAR2
2393 , p_doc_level_id IN NUMBER
2394 , p_override_date IN DATE
2395 , p_use_gl_date IN VARCHAR2
2396 , p_report_successes IN VARCHAR2
2397 , x_po_return_code OUT NOCOPY VARCHAR2
2398 , x_detailed_results OUT NOCOPY po_fcout_type
2399 )
2400 IS
2401
2402 l_api_name CONSTANT varchar2(30) := 'DO_FINAL_CLOSE';
2403 l_api_version CONSTANT NUMBER := 1.0;
2404 l_log_head CONSTANT VARCHAR2(100) := c_log_head || l_api_name;
2405 l_progress VARCHAR2(3) := '000';
2406 l_report_return_status VARCHAR2(1);
2407 l_online_report_id NUMBER;
2408
2409 BEGIN
2410
2411 IF g_debug_stmt THEN
2412 PO_DEBUG.debug_begin(l_log_head);
2413 PO_DEBUG.debug_var(l_log_head,l_progress,'p_api_version',p_api_version);
2414 PO_DEBUG.debug_var(l_log_head,l_progress,'p_commit',p_commit);
2415 PO_DEBUG.debug_var(l_log_head,l_progress,'p_init_msg_list',p_init_msg_list);
2416 PO_DEBUG.debug_var(l_log_head,l_progress,'p_validation_level',p_validation_level);
2417 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_type',p_doc_type);
2418 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_subtype',p_doc_subtype);
2419 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_level',p_doc_level);
2420 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_level_id',p_doc_level_id);
2421 PO_DEBUG.debug_var(l_log_head,l_progress,'p_override_date',p_override_date);
2422 PO_DEBUG.debug_var(l_log_head,l_progress,'p_use_gl_date',p_use_gl_date);
2423 PO_DEBUG.debug_var(l_log_head,l_progress,'p_report_successes',p_report_successes);
2424 END IF;
2425
2426 l_progress := '010';
2427
2428 -- Standard Start of API savepoint
2429 SAVEPOINT DO_FINAL_CLOSE_SP;
2430
2431 l_progress := '020';
2432
2433 -- Standard call to check for call compatibility
2434
2435 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
2436 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2437 END IF;
2438
2439 l_progress := '030';
2440
2441 -- Initialize message list if p_init_msg_list is set to TRUE.
2442
2443 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2444 FND_MSG_PUB.initialize;
2445 END IF;
2446
2447 l_progress := '040';
2448
2449 -- Initialize API return status to success
2450
2451 x_return_status := FND_API.G_RET_STS_SUCCESS;
2452
2453 l_progress := '050';
2454
2455 IF g_debug_stmt THEN
2456 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Calling Private Procedure');
2457 END IF;
2458
2459 l_progress := '060';
2460
2461 PO_DOCUMENT_FUNDS_PVT.do_final_close(
2462 x_return_status => x_return_status
2463 , p_doc_type => p_doc_type
2464 , p_doc_subtype => p_doc_subtype
2465 , p_doc_level => p_doc_level
2466 , p_doc_level_id => p_doc_level_id
2467 , p_use_enc_gt_flag => g_parameter_NO
2468 , p_use_gl_date => p_use_gl_date
2469 , p_override_date => p_override_date
2470 , x_po_return_code => x_po_return_code
2471 , x_online_report_id => l_online_report_id
2472 );
2473
2474 l_progress := '100';
2475
2476 IF ( x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
2477 OR x_return_status IS NULL
2478 ) THEN
2479 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2480 END IF;
2481
2482 l_progress := '110';
2483
2484 IF g_debug_stmt THEN
2485 PO_DEBUG.debug_stmt(l_log_head,l_progress,
2486 'Returned from Private Procedure successfully');
2487 END IF;
2488
2489 l_progress := '120';
2490
2491 PO_DOCUMENT_FUNDS_PVT.create_report_object(
2492 x_return_status => l_report_return_status
2493 , p_online_report_id => l_online_report_id
2494 , p_report_successes => p_report_successes
2495 , x_report_object => x_detailed_results
2496 );
2497
2498 l_progress := '200';
2499
2500 IF ( l_report_return_status = FND_API.G_RET_STS_UNEXP_ERROR
2501 OR l_report_return_status IS NULL
2502 ) THEN
2503 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2504 END IF;
2505
2506 l_progress := '300';
2507
2508 -- Standard check of p_commit.
2509 IF FND_API.To_Boolean(p_commit) THEN
2510 COMMIT WORK;
2511 END IF;
2512
2513 l_progress := '900';
2514
2515 IF g_debug_stmt THEN
2516 PO_DEBUG.debug_var(l_log_head,l_progress,'l_report_return_status',l_report_return_status);
2517 PO_DEBUG.debug_var(l_log_head,l_progress,'l_online_report_id',l_online_report_id);
2518 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
2519 PO_DEBUG.debug_var(l_log_head,l_progress,'x_po_return_code',x_po_return_code);
2520 PO_DEBUG.debug_end(l_log_head);
2521 END IF;
2522
2523 EXCEPTION
2524 WHEN OTHERS THEN
2525 BEGIN
2526 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS Start');
2527 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2528 IF g_debug_stmt THEN
2529 PO_DEBUG.debug_var(l_log_head,l_progress,'l_report_return_status',l_report_return_status);
2530 PO_DEBUG.debug_var(l_log_head,l_progress,'l_online_report_id',l_online_report_id);
2531 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
2532 PO_DEBUG.debug_var(l_log_head,l_progress,'x_po_return_code',x_po_return_code);
2533 END IF;
2534 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2535 FND_MSG_PUB.add_exc_msg(G_PKG_NAME,l_api_name,SQLERRM);
2536 END IF;
2537 ROLLBACK TO DO_FINAL_CLOSE_SP;
2538 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS End');
2539 EXCEPTION
2540 WHEN OTHERS THEN
2541 NULL;
2542 END;
2543
2544 END do_final_close;
2545
2546
2547 -------------------------------------------------------------------------------
2548 --Start of Comments
2549 --Name: reinstate_po_encumbrance
2550 --Pre-reqs:
2551 -- None.
2552 --Modifies:
2553 -- None.
2554 --Locks:
2555 -- None.
2556 --Function:
2557 -- It should created GL reversal entries if the distribution is
2558 -- either unreserved or the its shipment is Finally closed.
2559 -- Examine Private API for AP usage notes.
2560 --Parameters:
2561 --IN:
2562 --p_api_version
2563 -- Apps API Std - To control correct version in use
2564 --p_commit
2565 -- Apps API Std - Should data be commited?
2566 --p_init_msg_list
2567 -- Apps API Std - Initialize the message list?
2568 --p_validation_level
2569 -- Apps API Std - Level of validations to be done
2570 --p_distribution_id
2571 -- po_distribution_id
2572 --p_invoice_id
2573 -- po_invoice_id
2574 --p_encumbrance_amt
2575 -- encumbrance amount in functional currency for which AP will reinstate the
2576 -- PO encumbrance on Invoice cancellation. AP should take care of the
2577 -- overbilled case and any variances.
2578 -- IF (p_encumbrance_amt >0) THEN
2579 -- Invoice Cancellation, PO API does Cr (AP is doing -Cr)
2580 -- ELSE
2581 -- Memo Cancellation, PO API does Dr (AP is doing -Dr)
2582 --p_qty_cancelled
2583 -- Invoice qty cancelled for the PO distribution. This should be PO UOM
2584 -- p_qty_cancelled is -ve for Invoice Cancellation
2585 -- +ve for Credit Memo Cancellation
2586 --p_budget_account_id
2587 -- Budget account id - account on which the AP does PO reversal
2588 --p_gl_date
2589 -- Valid open Date on which AP will reinstate PO encumbrance on Invoice
2590 -- cancellation. We want the Dr and Cr to go in the same period.
2591 --p_period_name
2592 -- period name
2593 --p_period_year
2594 -- period year
2595 --p_period_num
2596 -- period num
2597 --p_quarter_num
2598 -- quarter num
2599 --p_tax_line_flag -- Bug 3480949
2600 -- Set depending upon which values of p_encumbered_amt AP calls the API with.
2601 -- g_parameter_NO - the original amounts before tax applied
2602 -- g_parameter_YES - the tax on the original amounts only
2603 -- Default NULL, which will be assumed to be g_parameter_NO
2604 -- Check Priavate API comments for more usage notes.
2605 --OUT:
2606 --x_packet_id
2607 -- GL PACKET ID, if gl entries are made otherwise null
2608 --x_return_status
2609 -- FND_API.G_RET_STS_SUCCESS if validation succeeds
2610 -- FND_API.G_RET_STS_ERROR if validation fails
2611 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
2612 --Testing:
2613 --
2614 --End of Comments
2615 -------------------------------------------------------------------------------
2616 PROCEDURE reinstate_po_encumbrance(
2617 p_api_version IN NUMBER,
2618 p_commit IN VARCHAR2 default FND_API.G_FALSE,
2619 p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE,
2620 p_validation_level IN NUMBER default FND_API.G_VALID_LEVEL_FULL,
2621 p_distribution_id IN NUMBER,
2622 p_invoice_id IN NUMBER,
2623 p_encumbrance_amt IN NUMBER,
2624 p_qty_cancelled IN NUMBER,
2625 p_budget_account_id IN NUMBER,
2626 p_gl_date IN DATE,
2627 p_period_name IN VARCHAR2,
2628 p_period_year IN VARCHAR2,
2629 p_period_num IN VARCHAR2,
2630 p_quarter_num IN VARCHAR2,
2631 p_tax_line_flag IN VARCHAR2 default NULL, -- Bug 3480949
2632 x_packet_id OUT NOCOPY NUMBER,
2633 x_return_status OUT NOCOPY VARCHAR2
2634 )
2635 IS
2636
2637 l_api_name CONSTANT varchar2(30) := 'REINSTATE_PO_ENCUMBRANCE';
2638 l_api_version CONSTANT NUMBER := 1.0;
2639 l_log_head CONSTANT VARCHAR2(100) := c_log_head || l_api_name;
2640 l_progress VARCHAR2(3) := '000';
2641
2642 BEGIN
2643
2644 IF g_debug_stmt THEN
2645 PO_DEBUG.debug_begin(l_log_head);
2646 PO_DEBUG.debug_var(l_log_head,l_progress,'p_api_version',p_api_version);
2647 PO_DEBUG.debug_var(l_log_head,l_progress,'p_commit',p_commit);
2648 PO_DEBUG.debug_var(l_log_head,l_progress,'p_init_msg_list',p_init_msg_list);
2649 PO_DEBUG.debug_var(l_log_head,l_progress,'p_validation_level',p_validation_level);
2650 PO_DEBUG.debug_var(l_log_head,l_progress,'p_distribution_id',p_distribution_id);
2651 PO_DEBUG.debug_var(l_log_head,l_progress,'p_invoice_id',p_invoice_id);
2652 PO_DEBUG.debug_var(l_log_head,l_progress,'p_encumbrance_amt',p_encumbrance_amt);
2653 PO_DEBUG.debug_var(l_log_head,l_progress,'p_qty_cancelled',p_qty_cancelled);
2654 PO_DEBUG.debug_var(l_log_head,l_progress,'p_budget_account_id',p_budget_account_id);
2655 PO_DEBUG.debug_var(l_log_head,l_progress,'p_gl_date',p_gl_date);
2656 PO_DEBUG.debug_var(l_log_head,l_progress,'p_period_name',p_period_name);
2657 PO_DEBUG.debug_var(l_log_head,l_progress,'p_period_year',p_period_year);
2658 PO_DEBUG.debug_var(l_log_head,l_progress,'p_period_num',p_period_num);
2659 PO_DEBUG.debug_var(l_log_head,l_progress,'p_quarter_num',p_quarter_num);
2660
2661 -- Bug 3480949: log p_tax_line_flag
2662 PO_DEBUG.debug_var(l_log_head,l_progress,'p_tax_line_flag',p_tax_line_flag);
2663
2664 END IF;
2665
2666 l_progress := '010';
2667
2668 -- Standard Start of API savepoint
2669 SAVEPOINT REINSTATE_PO_ENCUMBRANCE_SP;
2670
2671 l_progress := '020';
2672
2673 -- Standard call to check for call compatibility
2674
2675 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
2676 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2677 END IF;
2678
2679 l_progress := '030';
2680
2681 -- Initialize message list if p_init_msg_list is set to TRUE.
2682
2683 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2684 FND_MSG_PUB.initialize;
2685 END IF;
2686
2687 l_progress := '040';
2688
2689 -- Initialize API return status to success
2690
2691 x_return_status := FND_API.G_RET_STS_SUCCESS;
2692
2693 l_progress := '050';
2694
2695 IF g_debug_stmt THEN
2696 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Calling Private Procedure');
2697 END IF;
2698
2699 l_progress := '060';
2700
2701 PO_DOCUMENT_FUNDS_PVT.reinstate_po_encumbrance(
2702 x_return_status => x_return_status,
2703 p_distribution_id => p_distribution_id,
2704 p_invoice_id => p_invoice_id,
2705 p_encumbrance_amt => p_encumbrance_amt,
2706 p_qty_cancelled => p_qty_cancelled,
2707 p_budget_account_id => p_budget_account_id,
2708 p_gl_date => p_gl_date,
2709 p_period_name => p_period_name,
2710 p_period_year => p_period_year,
2711 p_period_num => p_period_num,
2712 p_quarter_num => p_quarter_num,
2713 p_tax_line_flag => p_tax_line_flag, -- Bug 3480949
2714 x_packet_id => x_packet_id
2715 );
2716
2717 l_progress := '100';
2718
2719 IF ( x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
2720 OR x_return_status IS NULL
2721 ) THEN
2722 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2723 END IF;
2724
2725 l_progress := '110';
2726
2727 IF g_debug_stmt THEN
2728 PO_DEBUG.debug_stmt(l_log_head,l_progress,
2729 'Returned from Private Procedure successfully');
2730 END IF;
2731
2732 l_progress := '200';
2733
2734 -- Standard check of p_commit.
2735 IF FND_API.To_Boolean(p_commit) THEN
2736 COMMIT WORK;
2737 END IF;
2738
2739 l_progress := '900';
2740
2741 IF g_debug_stmt THEN
2742 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
2743 PO_DEBUG.debug_var(l_log_head,l_progress,'x_packet_id',x_packet_id);
2744 PO_DEBUG.debug_end(l_log_head);
2745 END IF;
2746
2747 EXCEPTION
2748 WHEN OTHERS THEN
2749 BEGIN
2750 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS Start');
2751 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2752 IF g_debug_stmt THEN
2753 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
2754 PO_DEBUG.debug_var(l_log_head,l_progress,'x_packet_id',x_packet_id);
2755 END IF;
2756 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2757 FND_MSG_PUB.add_exc_msg(G_PKG_NAME,l_api_name,SQLERRM);
2758 END IF;
2759 ROLLBACK TO REINSTATE_PO_ENCUMBRANCE_SP;
2760 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS End');
2761 EXCEPTION
2762 WHEN OTHERS THEN
2763 NULL;
2764 END;
2765
2766 END reinstate_po_encumbrance;
2767
2768
2769
2770 ------------------------------------------------------------------------------
2771 --Start of Comments
2772 --Name: is_agreement_encumbered
2773 --Pre-reqs:
2774 -- N/A.
2775 --Modifies:
2776 -- N/A.
2777 --Locks:
2778 -- None.
2779 --Function:
2780 -- This procedure is an API that informs callers whether a particular agreement
2781 -- is/can be encumbered, based on its header level encumbrance_required flag.
2782 -- The output table contains results in the same ordering as the input table.
2783 --Parameters:
2784 --IN:
2785 --p_api_version
2786 -- Apps API Std - To control correct version in use
2787 --p_commit
2788 -- Apps API Std - Should data be commited?
2789 --p_init_msg_list
2790 -- Apps API Std - Initialize the message list?
2791 --p_validation_level
2792 -- Apps API Std - Level of validations to be done
2793 --p_agreement_ids_tbl
2794 -- A table of po_header_ids corresponding to the PAs that we are checking
2795 -- the encumbered state of.
2796 --OUT:
2797 --x_return_status
2798 -- FND_API.G_RET_STS_SUCCESS if validation succeeds
2799 -- FND_API.G_RET_STS_ERROR if validation fails
2800 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
2801 --x_results_tbl
2802 -- A table of Y/N results indicating whether each PA is encumbered or not.
2803 -- Y = the given PA is/can be encumbered.
2804 -- N = the PA is not eligible for encumbrance
2805 --Testing:
2806 --
2807 --End of Comments
2808 -------------------------------------------------------------------------------
2809 PROCEDURE is_agreement_encumbered(
2810 p_api_version IN NUMBER
2811 , p_commit IN VARCHAR2 default FND_API.G_FALSE
2812 , p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE
2813 , p_validation_level IN NUMBER default FND_API.G_VALID_LEVEL_FULL
2814 , x_return_status OUT NOCOPY VARCHAR2
2815 , p_agreement_id_tbl IN PO_TBL_NUMBER
2816 , x_agreement_encumbered_tbl OUT NOCOPY PO_TBL_VARCHAR1
2817 )
2818 IS
2819
2820 l_api_name CONSTANT varchar2(30) := 'IS_AGREEMENT_ENCUMBERED';
2821 l_api_version CONSTANT NUMBER := 1.0;
2822 l_log_head CONSTANT VARCHAR2(100) := c_log_head || l_api_name;
2823 l_progress VARCHAR2(3) := '000';
2824
2825 BEGIN
2826
2827 IF g_debug_stmt THEN
2828 PO_DEBUG.debug_begin(l_log_head);
2829 PO_DEBUG.debug_var(l_log_head,l_progress,'p_api_version',p_api_version);
2830 PO_DEBUG.debug_var(l_log_head,l_progress,'p_commit',p_commit);
2831 PO_DEBUG.debug_var(l_log_head,l_progress,'p_init_msg_list',p_init_msg_list);
2832 PO_DEBUG.debug_var(l_log_head,l_progress,'p_validation_level',p_validation_level);
2833 PO_DEBUG.debug_var(l_log_head,l_progress,'p_agreement_id_tbl',p_agreement_id_tbl);
2834 END IF;
2835
2836 l_progress := '010';
2837
2838 -- Standard Start of API savepoint
2839 SAVEPOINT IS_AGREEMENT_ENCUMBERED_SP;
2840
2841 l_progress := '020';
2842
2843 -- Standard call to check for call compatibility
2844
2845 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
2846 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2847 END IF;
2848
2849 l_progress := '030';
2850
2851 -- Initialize message list if p_init_msg_list is set to TRUE.
2852
2853 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2854 FND_MSG_PUB.initialize;
2855 END IF;
2856
2857 l_progress := '040';
2858
2859 -- Initialize API return status to success
2860
2861 x_return_status := FND_API.G_RET_STS_SUCCESS;
2862
2863 l_progress := '050';
2864
2865 IF g_debug_stmt THEN
2866 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Calling Private Procedure');
2867 END IF;
2868
2869 l_progress := '060';
2870
2871 PO_DOCUMENT_FUNDS_PVT.is_agreement_encumbered(
2872 x_return_status => x_return_status
2873 , p_agreement_id_tbl => p_agreement_id_tbl
2874 , x_agreement_encumbered_tbl => x_agreement_encumbered_tbl
2875 );
2876
2877 l_progress := '100';
2878
2879 IF ( x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
2880 OR x_return_status IS NULL
2881 ) THEN
2882 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2883 END IF;
2884
2885 l_progress := '110';
2886
2887 IF g_debug_stmt THEN
2888 PO_DEBUG.debug_stmt(l_log_head,l_progress,
2889 'Returned from Private Procedure successfully');
2890 END IF;
2891
2892 l_progress := '200';
2893
2894 -- Standard check of p_commit.
2895 IF FND_API.To_Boolean(p_commit) THEN
2896 COMMIT WORK;
2897 END IF;
2898
2899 l_progress := '900';
2900
2901 IF g_debug_stmt THEN
2902 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
2903 PO_DEBUG.debug_var(l_log_head,l_progress,'x_agreement_encumbered_tbl',x_agreement_encumbered_tbl);
2904 PO_DEBUG.debug_end(l_log_head);
2905 END IF;
2906
2907 EXCEPTION
2908 WHEN OTHERS THEN
2909 BEGIN
2910 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS Start');
2911 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2912 IF g_debug_stmt THEN
2913 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
2914 PO_DEBUG.debug_var(l_log_head,l_progress,'x_agreement_encumbered_tbl',x_agreement_encumbered_tbl);
2915 END IF;
2916 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2917 FND_MSG_PUB.add_exc_msg(G_PKG_NAME,l_api_name,SQLERRM);
2918 END IF;
2919 ROLLBACK TO IS_AGREEMENT_ENCUMBERED_SP;
2920 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS End');
2921 EXCEPTION
2922 WHEN OTHERS THEN
2923 NULL;
2924 END;
2925
2926 END is_agreement_encumbered;
2927
2928
2929 ------------------------------------------------------------------------------
2930 --Start of Comments
2931 --Name: is_reservable
2932 --Pre-reqs:
2933 -- N/A.
2934 --Modifies:
2935 -- N/A.
2936 --Locks:
2937 -- None.
2938 --Function:
2939 -- This procedure determines whether a given document has any distributions that
2940 -- are eligible for funds reservation.
2941 --Parameters:
2942 --IN:
2943 --p_api_version
2944 -- Apps API Std - To control correct version in use
2945 --p_commit
2946 -- Apps API Std - Should data be commited?
2947 --p_init_msg_list
2948 -- Apps API Std - Initialize the message list?
2949 --p_validation_level
2950 -- Apps API Std - Level of validations to be done
2951 --p_doc_type
2952 -- Differentiates between the doc being a REQ, PA, PO, or RELEASE,
2953 -- which is used to identify the tables to look at (PO vs. Req)
2954 -- and the join conditions
2955 --p_doc_subtype
2956 -- Differentiates between the subtypes of documents
2957 -- REQ: NULL
2958 -- PO: STANDARD, PLANNED
2959 -- PA: CONTRACT, BLANKET
2960 -- RELEASE: SCHEDULED, BLANKET
2961 --p_doc_level
2962 -- Specifies the level of the document that is being checked:
2963 -- HEADER, LINE, SHIPMENT, DISTRIBUTION
2964 --p_doc_level_id
2965 -- The id corresponding to the doc level type:
2966 -- header_id/release_id, line_id, line_location_id, distribution_id
2967 --OUT:
2968 --x_return_status
2969 -- FND_API.G_RET_STS_SUCCESS if validation succeeds
2970 -- FND_API.G_RET_STS_ERROR if validation fails
2971 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
2972 --x_result
2973 -- Indicates whether funds reservation is possible on this entity
2974 -- 'Y' means it is possible, 'N' means it isn't.
2975 --Testing:
2976 --
2977 --End of Comments
2978 -------------------------------------------------------------------------------
2979 PROCEDURE is_reservable(
2980 p_api_version IN NUMBER
2981 , p_commit IN VARCHAR2 default FND_API.G_FALSE
2982 , p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE
2983 , p_validation_level IN NUMBER default FND_API.G_VALID_LEVEL_FULL
2984 , x_return_status OUT NOCOPY VARCHAR2
2985 , p_doc_type IN VARCHAR2
2986 , p_doc_subtype IN VARCHAR2
2987 , p_doc_level IN VARCHAR2
2988 , p_doc_level_id IN NUMBER
2989 , x_reservable_flag OUT NOCOPY VARCHAR2
2990 )
2991 IS
2992
2993 l_api_name CONSTANT varchar2(30) := 'IS_RESERVABLE';
2994 l_api_version CONSTANT NUMBER := 1.0;
2995 l_log_head CONSTANT VARCHAR2(100) := c_log_head || l_api_name;
2996 l_progress VARCHAR2(3) := '000';
2997
2998 BEGIN
2999
3000 IF g_debug_stmt THEN
3001 PO_DEBUG.debug_begin(l_log_head);
3002 PO_DEBUG.debug_var(l_log_head,l_progress,'p_api_version',p_api_version);
3003 PO_DEBUG.debug_var(l_log_head,l_progress,'p_commit',p_commit);
3004 PO_DEBUG.debug_var(l_log_head,l_progress,'p_init_msg_list',p_init_msg_list);
3005 PO_DEBUG.debug_var(l_log_head,l_progress,'p_validation_level',p_validation_level);
3006 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_type',p_doc_type);
3007 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_subtype',p_doc_subtype);
3008 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_level',p_doc_level);
3009 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_level_id',p_doc_level_id);
3010 END IF;
3011
3012 l_progress := '010';
3013
3014 -- Standard Start of API savepoint
3015 SAVEPOINT IS_RESERVABLE_SP;
3016
3017 l_progress := '020';
3018
3019 -- Standard call to check for call compatibility
3020
3021 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
3022 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3023 END IF;
3024
3025 l_progress := '030';
3026
3027 -- Initialize message list if p_init_msg_list is set to TRUE.
3028
3029 IF FND_API.to_Boolean( p_init_msg_list ) THEN
3030 FND_MSG_PUB.initialize;
3031 END IF;
3032
3033 l_progress := '040';
3034
3035 -- Initialize API return status to success
3036
3037 x_return_status := FND_API.G_RET_STS_SUCCESS;
3038
3039 l_progress := '050';
3040
3041 IF g_debug_stmt THEN
3042 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Calling Private Procedure');
3043 END IF;
3044
3045 l_progress := '060';
3046
3047 PO_DOCUMENT_FUNDS_PVT.is_reservable(
3048 x_return_status => x_return_status
3049 , p_doc_type => p_doc_type
3050 , p_doc_subtype => p_doc_subtype
3051 , p_doc_level => p_doc_level
3052 , p_doc_level_id => p_doc_level_id
3053 , x_reservable_flag => x_reservable_flag
3054 );
3055
3056 l_progress := '100';
3057
3058 IF ( x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
3059 OR x_return_status IS NULL
3060 ) THEN
3061 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3062 END IF;
3063
3064 l_progress := '110';
3065
3066 IF g_debug_stmt THEN
3067 PO_DEBUG.debug_stmt(l_log_head,l_progress,
3068 'Returned from Private Procedure successfully');
3069 END IF;
3070
3071 l_progress := '200';
3072
3073 -- Standard check of p_commit.
3074 IF FND_API.To_Boolean(p_commit) THEN
3075 COMMIT WORK;
3076 END IF;
3077
3078 l_progress := '900';
3079
3080 IF g_debug_stmt THEN
3081 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
3082 PO_DEBUG.debug_var(l_log_head,l_progress,'x_reservable_flag',x_reservable_flag);
3083 PO_DEBUG.debug_end(l_log_head);
3084 END IF;
3085
3086 EXCEPTION
3087 WHEN OTHERS THEN
3088 BEGIN
3089 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS Start');
3090 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3091 IF g_debug_stmt THEN
3092 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
3093 PO_DEBUG.debug_var(l_log_head,l_progress,'x_reservable_flag',x_reservable_flag);
3094 END IF;
3095 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3096 FND_MSG_PUB.add_exc_msg(G_PKG_NAME,l_api_name,SQLERRM);
3097 END IF;
3098 ROLLBACK TO IS_RESERVABLE_SP;
3099 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS End');
3100 EXCEPTION
3101 WHEN OTHERS THEN
3102 NULL;
3103 END;
3104
3105 END is_reservable;
3106
3107
3108 ------------------------------------------------------------------------------
3109 --Start of Comments
3110 --Name: is_unreservable
3111 --Pre-reqs:
3112 -- N/A.
3113 --Modifies:
3114 -- N/A.
3115 --Locks:
3116 -- None.
3117 --Function:
3118 -- This procedure determines whether a given document has any distributions that
3119 -- are eligible for funds reservation.
3120 --Parameters:
3121 --IN:
3122 --p_api_version
3123 -- Apps API Std - To control correct version in use
3124 --p_commit
3125 -- Apps API Std - Should data be commited?
3126 --p_init_msg_list
3127 -- Apps API Std - Initialize the message list?
3128 --p_validation_level
3129 -- Apps API Std - Level of validations to be done
3130 --p_doc_type
3131 -- Differentiates between the doc being a REQ, PA, PO, or RELEASE,
3132 -- which is used to identify the tables to look at (PO vs. Req)
3133 -- and the join conditions
3134 --p_doc_subtype
3135 -- Differentiates between the subtypes of documents
3136 -- REQ: NULL
3137 -- PO: STANDARD, PLANNED
3138 -- PA: CONTRACT, BLANKET
3139 -- RELEASE: SCHEDULED, BLANKET
3140 --p_doc_level
3141 -- Specifies the level of the document that is being checked:
3142 -- HEADER, LINE, SHIPMENT, DISTRIBUTION
3143 --p_doc_level_id
3144 -- The id corresponding to the doc level type:
3145 -- header_id/release_id, line_id, line_location_id, distribution_id
3146 --OUT:
3147 --x_return_status
3148 -- FND_API.G_RET_STS_SUCCESS if validation succeeds
3149 -- FND_API.G_RET_STS_ERROR if validation fails
3150 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
3151 --x_result
3152 -- Indicates whether funds reservation is possible on this entity
3153 -- 'Y' means it is possible, 'N' means it isn't.
3154 --Testing:
3155 --
3156 --End of Comments
3157 -------------------------------------------------------------------------------
3158 PROCEDURE is_unreservable(
3159 p_api_version IN NUMBER
3160 , p_commit IN VARCHAR2 default FND_API.G_FALSE
3161 , p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE
3162 , p_validation_level IN NUMBER default FND_API.G_VALID_LEVEL_FULL
3163 , x_return_status OUT NOCOPY VARCHAR2
3164 , p_doc_type IN VARCHAR2
3165 , p_doc_subtype IN VARCHAR2
3166 , p_doc_level IN VARCHAR2
3167 , p_doc_level_id IN NUMBER
3168 , x_unreservable_flag OUT NOCOPY VARCHAR2
3169 )
3170 IS
3171
3172 l_api_name CONSTANT varchar2(30) := 'IS_UNRESERVABLE';
3173 l_api_version CONSTANT NUMBER := 1.0;
3174 l_log_head CONSTANT VARCHAR2(100) := c_log_head || l_api_name;
3175 l_progress VARCHAR2(3) := '000';
3176
3177 BEGIN
3178
3179 IF g_debug_stmt THEN
3180 PO_DEBUG.debug_begin(l_log_head);
3181 PO_DEBUG.debug_var(l_log_head,l_progress,'p_api_version',p_api_version);
3182 PO_DEBUG.debug_var(l_log_head,l_progress,'p_commit',p_commit);
3183 PO_DEBUG.debug_var(l_log_head,l_progress,'p_init_msg_list',p_init_msg_list);
3184 PO_DEBUG.debug_var(l_log_head,l_progress,'p_validation_level',p_validation_level);
3185 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_type',p_doc_type);
3186 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_subtype',p_doc_subtype);
3187 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_level',p_doc_level);
3188 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_level_id',p_doc_level_id);
3189 END IF;
3190
3191 l_progress := '010';
3192
3193 -- Standard Start of API savepoint
3194 SAVEPOINT IS_UNRESERVABLE_SP;
3195
3196 l_progress := '020';
3197
3198 -- Standard call to check for call compatibility
3199
3200 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
3201 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3202 END IF;
3203
3204 l_progress := '030';
3205
3206 -- Initialize message list if p_init_msg_list is set to TRUE.
3207
3208 IF FND_API.to_Boolean( p_init_msg_list ) THEN
3209 FND_MSG_PUB.initialize;
3210 END IF;
3211
3212 l_progress := '040';
3213
3214 -- Initialize API return status to success
3215
3216 x_return_status := FND_API.G_RET_STS_SUCCESS;
3217
3218 l_progress := '050';
3219
3220 IF g_debug_stmt THEN
3221 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Calling Private Procedure');
3222 END IF;
3223
3224 l_progress := '060';
3225
3226 PO_DOCUMENT_FUNDS_PVT.is_unreservable(
3227 x_return_status => x_return_status
3228 , p_doc_type => p_doc_type
3229 , p_doc_subtype => p_doc_subtype
3230 , p_doc_level => p_doc_level
3231 , p_doc_level_id => p_doc_level_id
3232 , x_unreservable_flag => x_unreservable_flag
3233 );
3234
3235 l_progress := '100';
3236
3237 IF ( x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
3238 OR x_return_status IS NULL
3239 ) THEN
3240 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3241 END IF;
3242
3243 l_progress := '110';
3244
3245 IF g_debug_stmt THEN
3246 PO_DEBUG.debug_stmt(l_log_head,l_progress,
3247 'Returned from Private Procedure successfully');
3248 END IF;
3249
3250 l_progress := '200';
3251
3252 -- Standard check of p_commit.
3253 IF FND_API.To_Boolean(p_commit) THEN
3254 COMMIT WORK;
3255 END IF;
3256
3257 l_progress := '900';
3258
3259 IF g_debug_stmt THEN
3260 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
3261 PO_DEBUG.debug_var(l_log_head,l_progress,'x_unreservable_flag',x_unreservable_flag);
3262 PO_DEBUG.debug_end(l_log_head);
3263 END IF;
3264
3265 EXCEPTION
3266 WHEN OTHERS THEN
3267 BEGIN
3268 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS Start');
3269 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3270 IF g_debug_stmt THEN
3271 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
3272 PO_DEBUG.debug_var(l_log_head,l_progress,'x_unreservable_flag',x_unreservable_flag);
3273 END IF;
3274 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3275 FND_MSG_PUB.add_exc_msg(G_PKG_NAME,l_api_name,SQLERRM);
3276 END IF;
3277 ROLLBACK TO IS_UNRESERVABLE_SP;
3278 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS End');
3279 EXCEPTION
3280 WHEN OTHERS THEN
3281 NULL;
3282 END;
3283
3284 END is_unreservable;
3285
3286
3287
3288 -------------------------------------------------------------------------------
3289 --Start of Comments
3290 --Name: populate_encumbrance_gt
3291 --Pre-reqs:
3292 -- None.
3293 --Modifies:
3294 -- PO_ENCUMBRANCE_GT
3295 --Locks:
3296 -- PO_HEADERS_ALL
3297 -- PO_RELEASES_ALL
3298 -- PO_DISTRIBUTIONS_ALL
3299 -- PO_REQUISITION_HEADERS_ALL
3300 -- PO_REQ_DISTRIBUTIONS_ALL
3301 --Function:
3302 -- Flattens the PO transaction tables to retrieve all of the
3303 -- data needed by the encumbrance code and put it in the
3304 -- encumbrance table. Also locks the document headers and
3305 -- distributions if requested to do so.
3306 --Parameters:
3307 --IN:
3308 --p_api_version
3309 -- Apps API Std - To control correct version in use
3310 --p_init_msg_list
3311 -- Apps API Std - Initialize the message list?
3312 --p_validation_level
3313 -- Apps API Std - Level of validations to be done
3314 --p_doc_type
3315 -- Document type. Use the g_doc_type_<> variables, where <> is:
3316 -- REQUISITION
3317 -- PA
3318 -- PO
3319 -- RELEASE
3320 --p_doc_level
3321 -- The type of ids that are being passed. Use g_doc_level_<>
3322 -- HEADER
3323 -- LINE
3324 -- SHIPMENT
3325 -- DISTRIBUTION
3326 --p_doc_level_id_tbl
3327 -- Ids of the doc level type with which to populate the encumbrance
3328 -- table. Each distribution with a link to the specified id
3329 -- will be used in the population.
3330 --
3331 --p_make_old_copies_flag
3332 --p_make_new_copies_flag
3333 --
3334 -- Specify what to populate in the adjustment_status column of
3335 -- the encumbrance table. Use g_parameter_YES/g_parameter_NO.
3336 -- Examples:
3337 -- OLD NEW Result
3338 -- --- --- ------
3339 -- YES YES Two copies will be made for each distribution,
3340 -- one with a value of g_adjustment_status_OLD
3341 -- and one with a value of g_adjustment_status_NEW
3342 -- in the adjustment_status column.
3343 -- YES NO One copy per dist. with a value of g_adjustment_status_OLD.
3344 -- NO YES One copy per dist. with a value of g_adjustment_status_NEW.
3345 -- NO NO One copy per dist. with a value of NULL.
3346 --
3347 --p_check_only_flag
3348 -- Indicates whether or not to lock the document headers and distributions.
3349 -- g_parameter_NO lock them
3350 -- g_parameter_YES don't lock them
3351 --
3352 --OUT:
3353 --x_return_status
3354 -- Apps standard parameter
3355 -- VARCHAR2(1)
3356 --Testing:
3357 --
3358 --End of Comments
3359 -------------------------------------------------------------------------------
3360 PROCEDURE populate_encumbrance_gt(
3361 p_api_version IN NUMBER
3362 , p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE
3363 , p_validation_level IN NUMBER default FND_API.G_VALID_LEVEL_FULL
3364 , x_return_status OUT NOCOPY VARCHAR2
3365 , p_doc_type IN VARCHAR2
3366 , p_doc_level IN VARCHAR2
3367 , p_doc_level_id_tbl IN po_tbl_number
3368 , p_make_old_copies_flag IN VARCHAR2
3369 , p_make_new_copies_flag IN VARCHAR2
3370 , p_check_only_flag IN VARCHAR2
3371 )
3372 IS
3373
3374 l_api_name CONSTANT VARCHAR2(30) := 'POPULATE_ENCUMBRANCE_GT';
3375 l_api_version CONSTANT NUMBER := 1.0;
3376
3377 l_log_head CONSTANT VARCHAR2(100) := c_log_head || l_api_name;
3378 l_progress VARCHAR2(3) := '000';
3379
3380 l_adjustment_status_tbl po_tbl_varchar5;
3381
3382 BEGIN
3383
3384 IF g_debug_stmt THEN
3385 PO_DEBUG.debug_begin(l_log_head);
3386 PO_DEBUG.debug_var(l_log_head,l_progress,'p_api_version',p_api_version);
3387 PO_DEBUG.debug_var(l_log_head,l_progress,'p_init_msg_list',p_init_msg_list);
3388 PO_DEBUG.debug_var(l_log_head,l_progress,'p_validation_level',p_validation_level);
3389 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_type', p_doc_type);
3390 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_level', p_doc_level);
3391 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_level_id_tbl', p_doc_level_id_tbl);
3392 PO_DEBUG.debug_var(l_log_head,l_progress,'p_make_old_copies_flag',p_make_old_copies_flag);
3393 PO_DEBUG.debug_var(l_log_head,l_progress,'p_make_new_copies_flag',p_make_new_copies_flag);
3394 PO_DEBUG.debug_var(l_log_head,l_progress,'p_check_only_flag', p_check_only_flag);
3395 END IF;
3396
3397 l_progress := '010';
3398
3399 -- Standard Start of API savepoint
3400 SAVEPOINT POPULATE_ENCUMBRANCE_GT_GRP;
3401
3402 l_progress := '020';
3403
3404 -- Standard call to check for call compatibility
3405 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
3406 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3407 END IF;
3408
3409 l_progress := '030';
3410
3411 -- Initialize message list if p_init_msg_list is set to TRUE.
3412 IF FND_API.to_Boolean( p_init_msg_list ) THEN
3413 FND_MSG_PUB.initialize;
3414 END IF;
3415
3416 l_progress := '040';
3417
3418 -- Initialize API return status to success
3419 x_return_status := FND_API.G_RET_STS_SUCCESS;
3420
3421 l_progress := '050';
3422
3423 -- Set up the parameters for the call to the private API.
3424
3425 -- Determine the adjustment status labels,
3426 -- and validate those parameters at the same time.
3427
3428 IF (p_make_old_copies_flag = g_parameter_YES
3429 AND p_make_new_copies_flag = g_parameter_YES)
3430 THEN
3431 l_progress := '130';
3432 l_adjustment_status_tbl := po_tbl_varchar5(g_adjustment_status_OLD,g_adjustment_status_NEW);
3433 ELSIF (p_make_old_copies_flag = g_parameter_YES
3434 AND p_make_new_copies_flag = g_parameter_NO)
3435 THEN
3436 l_progress := '140';
3437 l_adjustment_status_tbl := po_tbl_varchar5(g_adjustment_status_OLD);
3438 ELSIF (p_make_old_copies_flag = g_parameter_NO
3439 AND p_make_new_copies_flag = g_parameter_YES)
3440 THEN
3441 l_progress := '150';
3442 l_adjustment_status_tbl := po_tbl_varchar5(g_adjustment_status_NEW);
3443 ELSIF (p_make_old_copies_flag = g_parameter_NO
3444 AND p_make_new_copies_flag = g_parameter_NO)
3445 THEN
3446 l_progress := '160';
3447 l_adjustment_status_tbl := po_tbl_varchar5(NULL);
3448 ELSE
3449 l_progress := '170';
3450 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3451 END IF;
3452
3453
3454 l_progress := '200';
3455
3456 -- Call the private API.
3457
3458 PO_DOCUMENT_FUNDS_PVT.populate_encumbrance_gt(
3459 x_return_status => x_return_status
3460 , p_doc_type => p_doc_type
3461 , p_doc_level => p_doc_level
3462 , p_doc_level_id_tbl => p_doc_level_id_tbl
3463 , p_adjustment_status_tbl => l_adjustment_status_tbl
3464 , p_check_only_flag => p_check_only_flag
3465 );
3466
3467 l_progress := '900';
3468
3469 IF g_debug_stmt THEN
3470 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
3471 PO_DEBUG.debug_end(l_log_head);
3472 END IF;
3473
3474 EXCEPTION
3475 WHEN OTHERS THEN
3476 BEGIN
3477 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS Start');
3478 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3479 IF g_debug_stmt THEN
3480 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
3481 END IF;
3482 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3483 FND_MSG_PUB.add_exc_msg(G_PKG_NAME,l_api_name,SQLERRM);
3484 END IF;
3485 ROLLBACK TO POPULATE_ENCUMBRANCE_GT_GRP;
3486 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS End');
3487 EXCEPTION
3488 WHEN OTHERS THEN
3489 NULL;
3490 END;
3491
3492 END populate_encumbrance_gt;
3493
3494 --<bug#5010001 START>
3495 -------------------------------------------------------------------------------
3496 --Start of Comments
3497 --Name: get_online_report_id
3498 --Pre-reqs:
3499 -- None.
3500 --Modifies:
3501 --
3502 --Locks:
3503 --
3504 --Function:
3505 -- Returns the value of online_report_id generated in the current tranaction
3506 -- This is to be used only inside the function PO_DOCUMENT_FUNDS_PVT.populate_and_create_bc_report
3507 --Parameters:
3508 --IN:
3509 --
3510 --OUT:
3511 --Testing:
3512 --
3513 --End of Comments
3514 -------------------------------------------------------------------------------
3515 FUNCTION get_online_report_id RETURN NUMBER IS
3516 l_module_name CONSTANT VARCHAR2(100) := 'get_online_report_id';
3517 d_module_base CONSTANT VARCHAR2(100) := PO_LOG.get_subprogram_base(D_PACKAGE_BASE, l_module_name);
3518 l_api_version CONSTANT NUMBER := 1.0;
3519 d_progress NUMBER;
3520 BEGIN
3521 d_progress:=0;
3522 IF (PO_LOG.d_proc) THEN
3523 PO_LOG.proc_begin(d_module_base);
3524 END IF;
3525
3526 IF (PO_LOG.d_proc) THEN
3527 PO_LOG.proc_end(d_module_base);
3528 PO_LOG.proc_end(d_module_base, 'PO_DOCUMENT_FUNDS_PVT.g_ONLINE_REPORT_ID', PO_DOCUMENT_FUNDS_PVT.g_ONLINE_REPORT_ID);
3529 END IF;
3530 d_progress:=1;
3531
3532 return PO_DOCUMENT_FUNDS_PVT.g_ONLINE_REPORT_ID;
3533
3534 EXCEPTION
3535 WHEN OTHERS THEN
3536 IF (PO_LOG.d_exc) THEN
3537 PO_LOG.exc(d_module_base, d_progress, 'Unhandled exception in '
3538 ||l_module_name||' '||SQLCODE||' '||SQLERRM);
3539 PO_LOG.proc_end(d_module_base, 'PO_DOCUMENT_FUNDS_PVT.g_ONLINE_REPORT_ID', PO_DOCUMENT_FUNDS_PVT.g_ONLINE_REPORT_ID);
3540 END IF;
3541 RAISE FND_API.g_exc_unexpected_error;
3542 END;
3543
3544 --<bug#5010001 END>
3545 END PO_DOCUMENT_FUNDS_GRP;