1 PACKAGE BODY PO_DOCUMENT_FUNDS_GRP AS
2 -- $Header: POXGENCB.pls 120.1 2006/07/06 11:56:22 asista noship $
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 -------------------------------------------------------------------------------
1149 --Start of Comments
1150 --Name: do_return
1151 --Pre-reqs:
1152 -- None.
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 --Locks:
1158 -- None.
1159 --Function:
1160 -- This procedure unreserves encumbrance on a Requisition that has
1161 -- been returned
1162 --Parameters:
1163 --IN:
1164 --p_api_version
1165 -- Apps API Std - To control correct version in use
1166 --p_commit
1167 -- Apps API Std - Should data be commited?
1168 --p_init_msg_list
1169 -- Apps API Std - Initialize the message list?
1170 --p_validation_level
1171 -- Apps API Std - Level of validations to be done
1172 --p_doc_type
1173 -- Differentiates between the doc being a REQ, PA, PO, or RELEASE,
1174 -- which is used to identify the tables to look at (PO vs. Req)
1175 -- and the join conditions
1176 --p_doc_subtype
1177 -- Differentiates between the subtypes of documents
1178 -- REQ: NULL
1179 -- PO: STANDARD, PLANNED
1180 -- PA: CONTRACT, BLANKET
1181 -- RELEASE: SCHEDULED, BLANKET
1182 --p_doc_level
1183 -- Specifies the level of the document that is being checked:
1184 -- HEADER, LINE, SHIPMENT, DISTRIBUTION
1185 --p_doc_level_id
1186 -- The id corresponding to the doc level type:
1187 -- header_id/release_id, line_id, line_location_id, distribution_id
1188 --p_override_date
1189 -- Caller-specified date to use for distribution encumbrance date in GL entries
1190 --p_use_gl_date
1191 -- Flag that specifies whether to always prefer using the existing distribution
1192 -- GL date instead of the override date, when possible
1193 --p_report_successes
1194 -- Determines whether the x_detailed_results object contains information about
1195 -- distributions for which encumbrance actions were successful. Information for
1196 -- distributions with Warnings and Errors are always included.
1197 --OUT:
1198 --x_return_status
1199 -- FND_API.G_RET_STS_SUCCESS if validation succeeds
1200 -- FND_API.G_RET_STS_ERROR if validation fails
1201 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
1202 --x_detailed_results
1203 -- Object table that stores distribution specific
1204 -- reporting information for the encumbrance transaction
1205 --x_po_return_code
1206 -- Indicates whether PO is classifying this transaction as an
1207 -- Error/Warning/Partial Success/Success
1208 --Testing:
1209 --
1210 --End of Comments
1211 -------------------------------------------------------------------------------
1212 PROCEDURE do_return(
1213 p_api_version IN NUMBER
1214 , p_commit IN VARCHAR2 default FND_API.G_FALSE
1215 , p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE
1216 , p_validation_level IN NUMBER default FND_API.G_VALID_LEVEL_FULL
1217 , x_return_status OUT NOCOPY VARCHAR2
1218 , p_doc_type IN VARCHAR2
1219 , p_doc_subtype IN VARCHAR2
1220 , p_doc_level IN VARCHAR2
1221 , p_doc_level_id IN NUMBER
1222 , p_override_date IN DATE
1223 , p_use_gl_date IN VARCHAR2
1224 , p_report_successes IN VARCHAR2
1225 , x_po_return_code OUT NOCOPY VARCHAR2
1226 , x_detailed_results OUT NOCOPY po_fcout_type
1227 )
1228 IS
1229
1230 l_api_name CONSTANT varchar2(30) := 'DO_RETURN';
1231 l_log_head CONSTANT VARCHAR2(100) := c_log_head || l_api_name;
1232 l_api_version CONSTANT NUMBER := 1.0;
1233 l_progress VARCHAR2(3) := '000';
1234 l_report_return_status VARCHAR2(1);
1235 l_online_report_id NUMBER;
1236
1237 BEGIN
1238
1239 IF g_debug_stmt THEN
1240 PO_DEBUG.debug_begin(l_log_head);
1241 PO_DEBUG.debug_var(l_log_head,l_progress,'p_api_version',p_api_version);
1242 PO_DEBUG.debug_var(l_log_head,l_progress,'p_commit',p_commit);
1243 PO_DEBUG.debug_var(l_log_head,l_progress,'p_init_msg_list',p_init_msg_list);
1244 PO_DEBUG.debug_var(l_log_head,l_progress,'p_validation_level',p_validation_level);
1245 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_type',p_doc_type);
1246 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_subtype',p_doc_subtype);
1247 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_level',p_doc_level);
1248 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_level_id',p_doc_level_id);
1249 PO_DEBUG.debug_var(l_log_head,l_progress,'p_override_date',p_override_date);
1250 PO_DEBUG.debug_var(l_log_head,l_progress,'p_use_gl_date',p_use_gl_date);
1251 PO_DEBUG.debug_var(l_log_head,l_progress,'p_report_successes',p_report_successes);
1252 END IF;
1253
1254 l_progress := '010';
1255
1256 -- Standard Start of API savepoint
1257 SAVEPOINT DO_RETURN_SP;
1258
1259 l_progress := '020';
1260
1261 -- Standard call to check for call compatibility
1262
1263 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1264 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1265 END IF;
1266
1267 l_progress := '030';
1268
1269 -- Initialize message list if p_init_msg_list is set to TRUE.
1270
1271 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1272 FND_MSG_PUB.initialize;
1273 END IF;
1274
1275 l_progress := '040';
1276
1277 -- Initialize API return status to success
1278
1279 x_return_status := FND_API.G_RET_STS_SUCCESS;
1280
1281 l_progress := '050';
1282
1283 IF g_debug_stmt THEN
1284 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Calling Private Procedure');
1285 END IF;
1286
1287 l_progress := '060';
1288
1289 PO_DOCUMENT_FUNDS_PVT.do_return(
1290 x_return_status => x_return_status
1291 , p_doc_type => p_doc_type
1292 , p_doc_subtype => p_doc_subtype
1293 , p_doc_level => p_doc_level
1294 , p_doc_level_id => p_doc_level_id
1295 , p_use_enc_gt_flag => g_parameter_NO
1296 , p_use_gl_date => p_use_gl_date
1297 , p_override_date => p_override_date
1298 , x_po_return_code => x_po_return_code
1299 , x_online_report_id => l_online_report_id
1300 );
1301
1302 l_progress := '100';
1303
1304 IF ( x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
1305 OR x_return_status IS NULL
1306 ) THEN
1307 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1308 END IF;
1309
1310 l_progress := '110';
1311
1312 IF g_debug_stmt THEN
1313 PO_DEBUG.debug_stmt(l_log_head,l_progress,
1314 'Returned from Private Procedure successfully');
1315 END IF;
1316
1317 l_progress := '120';
1318
1319 PO_DOCUMENT_FUNDS_PVT.create_report_object(
1320 x_return_status => l_report_return_status
1321 , p_online_report_id => l_online_report_id
1322 , p_report_successes => p_report_successes
1323 , x_report_object => x_detailed_results
1324 );
1325
1326 l_progress := '200';
1327
1328 IF ( l_report_return_status = FND_API.G_RET_STS_UNEXP_ERROR
1329 OR l_report_return_status IS NULL
1330 ) THEN
1331 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1332 END IF;
1333
1334 l_progress := '300';
1335
1336 -- Standard check of p_commit.
1337 IF FND_API.To_Boolean(p_commit) THEN
1338 COMMIT WORK;
1339 END IF;
1340
1341 l_progress := '900';
1342
1343 IF g_debug_stmt THEN
1344 PO_DEBUG.debug_var(l_log_head,l_progress,'l_report_return_status',l_report_return_status);
1345 PO_DEBUG.debug_var(l_log_head,l_progress,'l_online_report_id',l_online_report_id);
1346 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
1347 PO_DEBUG.debug_var(l_log_head,l_progress,'x_po_return_code',x_po_return_code);
1348 PO_DEBUG.debug_end(l_log_head);
1349 END IF;
1350
1351 EXCEPTION
1352 WHEN OTHERS THEN
1353 BEGIN
1354 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS Start');
1355 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1356 IF g_debug_stmt THEN
1357 PO_DEBUG.debug_var(l_log_head,l_progress,'l_report_return_status',l_report_return_status);
1358 PO_DEBUG.debug_var(l_log_head,l_progress,'l_online_report_id',l_online_report_id);
1359 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
1360 PO_DEBUG.debug_var(l_log_head,l_progress,'x_po_return_code',x_po_return_code);
1361 END IF;
1362 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1363 FND_MSG_PUB.add_exc_msg(G_PKG_NAME,l_api_name,SQLERRM);
1364 END IF;
1365 ROLLBACK TO DO_RETURN_SP;
1366 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS End');
1367 EXCEPTION
1368 WHEN OTHERS THEN
1369 NULL;
1370 END;
1371
1372 END do_return;
1373
1374
1375 -------------------------------------------------------------------------------
1376 --Start of Comments
1377 --Name: do_reject
1378 --Pre-reqs:
1379 -- None.
1380 --Modifies:
1381 -- Creates encumbrance entries in the gl_bc_packets table
1382 -- Adds distribution-specific transaction information into the
1383 -- po_online_report_text table
1384 --Locks:
1385 -- None.
1386 --Function:
1387 -- This procedure unreserves encumbrance from unapproved shipments on
1388 -- a document that has been rejected
1389 --Parameters:
1390 --IN:
1391 --p_api_version
1392 -- Apps API Std - To control correct version in use
1393 --p_commit
1394 -- Apps API Std - Should data be commited?
1395 --p_init_msg_list
1396 -- Apps API Std - Initialize the message list?
1397 --p_validation_level
1398 -- Apps API Std - Level of validations to be done
1399 --p_doc_type
1400 -- Differentiates between the doc being a REQ, PA, PO, or RELEASE,
1401 -- which is used to identify the tables to look at (PO vs. Req)
1402 -- and the join conditions
1403 --p_doc_subtype
1404 -- Differentiates between the subtypes of documents
1405 -- REQ: NULL
1406 -- PO: STANDARD, PLANNED
1407 -- PA: CONTRACT, BLANKET
1408 -- RELEASE: SCHEDULED, BLANKET
1409 --p_doc_level
1410 -- Specifies the level of the document that is being checked:
1411 -- For Reject, this should always be g_HEADER
1412 --p_doc_level_id
1413 -- The id corresponding to the doc level type:
1414 -- For Reject, this should always be header_id (or release_id for Releases)
1415 --p_override_funds
1416 -- Indicates whether funds override capability can be used if needed, to make a
1417 -- transaction succeed.
1418 --p_use_gl_date
1419 -- Flag that specifies whether to always prefer using the existing distribution
1420 -- GL date instead of the override date, when possible
1421 --p_override_date
1422 -- Caller-specified date to use for distribution encumbrance date in GL entries
1423 --p_report_successes
1424 -- Determines whether the x_detailed_results object contains information about
1425 -- distributions for which encumbrance actions were successful. Information for
1426 -- distributions with Warnings and Errors are always included.
1427 --OUT:
1428 --x_return_status
1429 -- FND_API.G_RET_STS_SUCCESS if validation succeeds
1430 -- FND_API.G_RET_STS_ERROR if validation fails
1431 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
1432 --x_detailed_results
1433 -- Object table that stores distribution specific
1434 -- reporting information for the encumbrance transaction
1435 --x_po_return_code
1436 -- Indicates whether PO is classifying this transaction as an
1437 -- Error/Warning/Partial Success/Success
1438 --Testing:
1439 --
1440 --End of Comments
1441 -------------------------------------------------------------------------------
1442 PROCEDURE do_reject(
1443 p_api_version IN NUMBER
1444 , p_commit IN VARCHAR2 default FND_API.G_FALSE
1445 , p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE
1446 , p_validation_level IN NUMBER default FND_API.G_VALID_LEVEL_FULL
1447 , x_return_status OUT NOCOPY VARCHAR2
1448 , p_doc_type IN VARCHAR2
1449 , p_doc_subtype IN VARCHAR2
1450 , p_doc_level IN VARCHAR2
1451 , p_doc_level_id IN NUMBER
1452 , p_override_funds IN VARCHAR2
1453 , p_use_gl_date IN VARCHAR2
1454 , p_override_date IN DATE
1455 , p_report_successes IN VARCHAR2
1456 , x_po_return_code OUT NOCOPY VARCHAR2
1457 , x_detailed_results OUT NOCOPY po_fcout_type
1458 )
1459 IS
1460
1461 l_api_name CONSTANT varchar2(30) := 'DO_REJECT';
1462 l_api_version CONSTANT NUMBER := 1.0;
1463 l_log_head CONSTANT VARCHAR2(100) := c_log_head || l_api_name;
1464 l_progress VARCHAR2(3) := '000';
1465 l_report_return_status VARCHAR2(1);
1466 l_online_report_id NUMBER;
1467
1468 BEGIN
1469
1470 IF g_debug_stmt THEN
1471 PO_DEBUG.debug_begin(l_log_head);
1472 PO_DEBUG.debug_var(l_log_head,l_progress,'p_api_version',p_api_version);
1473 PO_DEBUG.debug_var(l_log_head,l_progress,'p_commit',p_commit);
1474 PO_DEBUG.debug_var(l_log_head,l_progress,'p_init_msg_list',p_init_msg_list);
1475 PO_DEBUG.debug_var(l_log_head,l_progress,'p_validation_level',p_validation_level);
1476 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_type',p_doc_type);
1477 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_subtype',p_doc_subtype);
1478 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_level',p_doc_level);
1479 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_level_id',p_doc_level_id);
1480 PO_DEBUG.debug_var(l_log_head,l_progress,'p_override_funds',p_override_funds);
1481 PO_DEBUG.debug_var(l_log_head,l_progress,'p_use_gl_date',p_use_gl_date);
1482 PO_DEBUG.debug_var(l_log_head,l_progress,'p_override_date',p_override_date);
1483 PO_DEBUG.debug_var(l_log_head,l_progress,'p_report_successes',p_report_successes);
1484 END IF;
1485
1486 l_progress := '010';
1487
1488 -- Standard Start of API savepoint
1489 SAVEPOINT DO_REJECT_SP;
1490
1491 l_progress := '020';
1492
1493 -- Standard call to check for call compatibility
1494
1495 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1496 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1497 END IF;
1498
1499 l_progress := '030';
1500
1501 -- Initialize message list if p_init_msg_list is set to TRUE.
1502
1503 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1504 FND_MSG_PUB.initialize;
1505 END IF;
1506
1507 l_progress := '040';
1508
1509 -- Initialize API return status to success
1510
1511 x_return_status := FND_API.G_RET_STS_SUCCESS;
1512
1513 l_progress := '050';
1514
1515 IF g_debug_stmt THEN
1516 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Calling Private Procedure');
1517 END IF;
1518
1519 l_progress := '060';
1520
1521 PO_DOCUMENT_FUNDS_PVT.do_reject(
1522 x_return_status => x_return_status
1523 , p_doc_type => p_doc_type
1524 , p_doc_subtype => p_doc_subtype
1525 , p_doc_level => p_doc_level
1526 , p_doc_level_id => p_doc_level_id
1527 , p_use_enc_gt_flag => g_parameter_NO
1528 , p_override_funds => p_override_funds
1529 , p_use_gl_date => p_use_gl_date
1530 , p_override_date => p_override_date
1531 , x_po_return_code => x_po_return_code
1532 , x_online_report_id => l_online_report_id
1533 );
1534
1535 l_progress := '100';
1536
1537 IF ( x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
1538 OR x_return_status IS NULL
1539 ) THEN
1540 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1541 END IF;
1542
1543 l_progress := '110';
1544
1545 IF g_debug_stmt THEN
1546 PO_DEBUG.debug_stmt(l_log_head,l_progress,
1547 'Returned from Private Procedure successfully');
1548 END IF;
1549
1550 l_progress := '120';
1551
1552 PO_DOCUMENT_FUNDS_PVT.create_report_object(
1553 x_return_status => l_report_return_status
1554 , p_online_report_id => l_online_report_id
1555 , p_report_successes => p_report_successes
1556 , x_report_object => x_detailed_results
1557 );
1558
1559 l_progress := '200';
1560
1561 IF ( l_report_return_status = FND_API.G_RET_STS_UNEXP_ERROR
1562 OR l_report_return_status IS NULL
1563 ) THEN
1564 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1565 END IF;
1566
1567 l_progress := '300';
1568
1569 -- Standard check of p_commit.
1570 IF FND_API.To_Boolean(p_commit) THEN
1571 COMMIT WORK;
1572 END IF;
1573
1574 l_progress := '900';
1575
1576 IF g_debug_stmt THEN
1577 PO_DEBUG.debug_var(l_log_head,l_progress,'l_report_return_status',l_report_return_status);
1578 PO_DEBUG.debug_var(l_log_head,l_progress,'l_online_report_id',l_online_report_id);
1579 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
1580 PO_DEBUG.debug_var(l_log_head,l_progress,'x_po_return_code',x_po_return_code);
1581 PO_DEBUG.debug_end(l_log_head);
1582 END IF;
1583
1584 EXCEPTION
1585 WHEN OTHERS THEN
1586 BEGIN
1587 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS Start');
1588 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1589 IF g_debug_stmt THEN
1590 PO_DEBUG.debug_var(l_log_head,l_progress,'l_report_return_status',l_report_return_status);
1591 PO_DEBUG.debug_var(l_log_head,l_progress,'l_online_report_id',l_online_report_id);
1592 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
1593 PO_DEBUG.debug_var(l_log_head,l_progress,'x_po_return_code',x_po_return_code);
1594 END IF;
1595 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1596 FND_MSG_PUB.add_exc_msg(G_PKG_NAME,l_api_name,SQLERRM);
1597 END IF;
1598 ROLLBACK TO DO_REJECT_SP;
1599 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS End');
1600 EXCEPTION
1601 WHEN OTHERS THEN
1602 NULL;
1603 END;
1604
1605 END do_reject;
1606
1607
1608 -------------------------------------------------------------------------------
1609 --Start of Comments
1610 --Name: do_cancel
1611 --Pre-reqs:
1612 -- The cancel code must already have set the cancel_flag on the relevant entity
1613 -- to be 'I' (is this always required?)
1614 -- The cancel code must have already created a new Req distribution if Recreate
1615 -- (and hence re-encumber) Demand is requested.
1616 --Modifies:
1617 -- Creates encumbrance entries in the gl_bc_packets table
1618 -- Adds distribution-specific transaction information into the
1619 -- po_online_report_text table
1620 --Locks:
1621 -- None.
1622 --Function:
1623 -- This procedure cancels the encumbrance on all eligible distributions of
1624 -- the requested document or document subentity
1625 --Parameters:
1626 --IN:
1627 --p_api_version
1628 -- Apps API Std - To control correct version in use
1629 --p_commit
1630 -- Apps API Std - Should data be commited?
1631 --p_init_msg_list
1632 -- Apps API Std - Initialize the message list?
1633 --p_validation_level
1634 -- Apps API Std - Level of validations to be done
1635 --p_doc_type
1636 -- Differentiates between the doc being a REQ, PA, PO, or RELEASE,
1637 -- which is used to identify the tables to look at (PO vs. Req)
1638 -- and the join conditions
1639 --p_doc_subtype
1640 -- Differentiates between the subtypes of documents
1641 -- REQ: NULL
1642 -- PO: STANDARD, PLANNED
1643 -- PA: CONTRACT, BLANKET
1644 -- RELEASE: SCHEDULED, BLANKET
1645 --p_doc_level
1646 -- Specifies the level of the document that is being checked:
1647 -- HEADER, LINE, SHIPMENT, DISTRIBUTION
1648 --p_doc_level_id
1649 -- The id corresponding to the doc level type:
1650 -- header_id/release_id, line_id, line_location_id, distribution_id
1651 --p_override_date
1652 -- Caller-specified date to use for distribution encumbrance date in GL entries
1653 --p_use_gl_date
1654 -- Flag that specifies whether to always prefer using the existing distribution
1655 -- GL date instead of the override date, when possible
1656 --p_override_funds
1657 -- Indicates whether funds override capability can be used if needed, to make a
1658 -- transaction succeed.
1659 --p_report_successes
1660 -- Determines whether the x_detailed_results object contains information about
1661 -- distributions for which encumbrance actions were successful. Information for
1662 -- distributions with Warnings and Errors are always included.
1663 --OUT:
1664 --x_return_status
1665 -- FND_API.G_RET_STS_SUCCESS if validation succeeds
1666 -- FND_API.G_RET_STS_ERROR if validation fails
1667 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
1668 --x_detailed_results
1669 -- Object table that stores distribution specific
1670 -- reporting information for the encumbrance transaction
1671 --x_po_return_code
1672 -- Indicates whether PO is classifying this transaction as an
1673 -- Error/Warning/Partial Success/Success
1674 --Testing:
1675 --
1676 --End of Comments
1677 -------------------------------------------------------------------------------
1678 PROCEDURE do_cancel(
1679 p_api_version IN NUMBER
1680 , p_commit IN VARCHAR2 default FND_API.G_FALSE
1681 , p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE
1682 , p_validation_level IN NUMBER default FND_API.G_VALID_LEVEL_FULL
1683 , x_return_status OUT NOCOPY VARCHAR2
1684 , p_doc_type IN VARCHAR2
1685 , p_doc_subtype IN VARCHAR2
1686 , p_doc_level IN VARCHAR2
1687 , p_doc_level_id IN NUMBER
1688 , p_override_funds IN VARCHAR2
1689 , p_use_gl_date IN VARCHAR2
1690 , p_override_date IN DATE
1691 , p_report_successes IN VARCHAR2
1692 , x_po_return_code OUT NOCOPY VARCHAR2
1693 , x_detailed_results OUT NOCOPY po_fcout_type
1694 )
1695 IS
1696
1697 l_api_name CONSTANT varchar2(30) := 'DO_CANCEL';
1698 l_api_version CONSTANT NUMBER := 1.0;
1699 l_log_head CONSTANT VARCHAR2(100) := c_log_head || l_api_name;
1700 l_progress VARCHAR2(3) := '000';
1701 l_report_return_status VARCHAR2(1);
1702 l_online_report_id NUMBER;
1703
1704 BEGIN
1705
1706 IF g_debug_stmt THEN
1707 PO_DEBUG.debug_begin(l_log_head);
1708 PO_DEBUG.debug_var(l_log_head,l_progress,'p_api_version',p_api_version);
1709 PO_DEBUG.debug_var(l_log_head,l_progress,'p_commit',p_commit);
1710 PO_DEBUG.debug_var(l_log_head,l_progress,'p_init_msg_list',p_init_msg_list);
1711 PO_DEBUG.debug_var(l_log_head,l_progress,'p_validation_level',p_validation_level);
1712 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_type',p_doc_type);
1713 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_subtype',p_doc_subtype);
1714 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_level',p_doc_level);
1715 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_level_id',p_doc_level_id);
1716 PO_DEBUG.debug_var(l_log_head,l_progress,'p_override_funds',p_override_funds);
1717 PO_DEBUG.debug_var(l_log_head,l_progress,'p_use_gl_date',p_use_gl_date);
1718 PO_DEBUG.debug_var(l_log_head,l_progress,'p_override_date',p_override_date);
1719 PO_DEBUG.debug_var(l_log_head,l_progress,'p_report_successes',p_report_successes);
1720 END IF;
1721
1722 l_progress := '010';
1723
1724 -- Standard Start of API savepoint
1725 SAVEPOINT DO_CANCEL_SP;
1726
1727 l_progress := '020';
1728
1729 -- Standard call to check for call compatibility
1730
1731 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1732 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1733 END IF;
1734
1735 l_progress := '030';
1736
1737 -- Initialize message list if p_init_msg_list is set to TRUE.
1738
1739 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1740 FND_MSG_PUB.initialize;
1741 END IF;
1742
1743 l_progress := '040';
1744
1745 -- Initialize API return status to success
1746
1747 x_return_status := FND_API.G_RET_STS_SUCCESS;
1748
1749 l_progress := '050';
1750
1751 IF g_debug_stmt THEN
1752 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Calling Private Procedure');
1753 END IF;
1754
1755 l_progress := '060';
1756
1757 PO_DOCUMENT_FUNDS_PVT.do_cancel(
1758 x_return_status => x_return_status
1759 , p_doc_type => p_doc_type
1760 , p_doc_subtype => p_doc_subtype
1761 , p_doc_level => p_doc_level
1762 , p_doc_level_id => p_doc_level_id
1763 , p_use_enc_gt_flag => g_parameter_NO
1764 , p_override_funds => p_override_funds
1765 , p_use_gl_date => p_use_gl_date
1766 , p_override_date => p_override_date
1767 , x_po_return_code => x_po_return_code
1768 , x_online_report_id => l_online_report_id
1769 );
1770
1771 l_progress := '100';
1772
1773 IF ( x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
1774 OR x_return_status IS NULL
1775 ) THEN
1776 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1777 END IF;
1778
1779 l_progress := '110';
1780
1781 IF g_debug_stmt THEN
1782 PO_DEBUG.debug_stmt(l_log_head,l_progress,
1783 'Returned from Private Procedure successfully');
1784 END IF;
1785
1786 l_progress := '120';
1787
1788 PO_DOCUMENT_FUNDS_PVT.create_report_object(
1789 x_return_status => l_report_return_status
1790 , p_online_report_id => l_online_report_id
1791 , p_report_successes => p_report_successes
1792 , x_report_object => x_detailed_results
1793 );
1794
1795 l_progress := '200';
1796
1797 IF ( l_report_return_status = FND_API.G_RET_STS_UNEXP_ERROR
1798 OR l_report_return_status IS NULL
1799 ) THEN
1800 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1801 END IF;
1802
1803 l_progress := '300';
1804
1805 -- Standard check of p_commit.
1806 IF FND_API.To_Boolean(p_commit) THEN
1807 COMMIT WORK;
1808 END IF;
1809
1810 l_progress := '900';
1811
1812 IF g_debug_stmt THEN
1813 PO_DEBUG.debug_var(l_log_head,l_progress,'l_report_return_status',l_report_return_status);
1814 PO_DEBUG.debug_var(l_log_head,l_progress,'l_online_report_id',l_online_report_id);
1815 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
1816 PO_DEBUG.debug_var(l_log_head,l_progress,'x_po_return_code',x_po_return_code);
1817 PO_DEBUG.debug_end(l_log_head);
1818 END IF;
1819
1820 EXCEPTION
1821 WHEN OTHERS THEN
1822 BEGIN
1823 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS Start');
1824 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
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 END IF;
1831 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1832 FND_MSG_PUB.add_exc_msg(G_PKG_NAME,l_api_name,SQLERRM);
1833 END IF;
1834 ROLLBACK TO DO_CANCEL_SP;
1835 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS End');
1836 EXCEPTION
1837 WHEN OTHERS THEN
1838 NULL;
1839 END;
1840
1841 END do_cancel;
1842
1843
1844
1845 -------------------------------------------------------------------------------
1846 --Start of Comments
1847 --Name: do_adjust
1848 --Pre-reqs:
1849 -- N/A.
1850 --Modifies:
1851 -- Creates encumbrance entries in the gl_bc_packets table
1852 -- Adds distribution-specific transaction information into the
1853 -- po_online_report_text table
1854 --Locks:
1855 -- None.
1856 --Function:
1857 -- This procedure cancels the encumbrance on all eligible distributions of
1858 -- the requested document or document subentity
1859 --Parameters:
1860 --IN:
1861 --p_api_version
1862 -- Apps API Std - To control correct version in use
1863 --p_commit
1864 -- Apps API Std - Should data be commited?
1865 --p_init_msg_list
1866 -- Apps API Std - Initialize the message list?
1867 --p_validation_level
1868 -- Apps API Std - Level of validations to be done
1869 --p_doc_type
1870 -- Differentiates between the doc being a REQ, PA, PO, or RELEASE,
1871 -- which is used to identify the tables to look at (PO vs. Req)
1872 -- and the join conditions
1873 --p_doc_subtype
1874 -- Differentiates between the subtypes of documents
1875 -- REQ: NULL
1876 -- PO: STANDARD, PLANNED
1877 -- PA: CONTRACT, BLANKET
1878 -- RELEASE: SCHEDULED, BLANKET
1879 --p_employee_id
1880 -- Employee Id of the user taking the action
1881 --p_override_funds
1882 -- Indicates whether funds override capability can be used if needed, to make a
1883 -- transaction succeed.
1884 --p_use_gl_date
1885 -- Flag that specifies whether to always prefer using the existing distribution
1886 -- GL date instead of the override date, when possible
1887 --p_override_date
1888 -- Caller-specified date to use for distribution encumbrance date in GL entries
1889 --p_report_successes
1890 -- Determines whether the x_detailed_results object contains information about
1891 -- distributions for which encumbrance actions were successful. Information for
1892 -- distributions with Warnings and Errors are always included.
1893 --OUT:
1894 --x_return_status
1895 -- FND_API.G_RET_STS_SUCCESS if validation succeeds
1896 -- FND_API.G_RET_STS_ERROR if validation fails
1897 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
1898 --x_detailed_results
1899 -- Object table that stores distribution specific
1900 -- reporting information for the encumbrance transaction
1901 --x_po_return_code
1902 -- Indicates whether PO is classifying this transaction as an
1903 -- Error/Warning/Partial Success/Success
1904 --Testing:
1905 --
1906 --End of Comments
1907 -------------------------------------------------------------------------------
1908 PROCEDURE do_adjust(
1909 p_api_version IN NUMBER
1910 , p_commit IN VARCHAR2 default FND_API.G_FALSE
1911 , p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE
1912 , p_validation_level IN NUMBER default FND_API.G_VALID_LEVEL_FULL
1913 , x_return_status OUT NOCOPY VARCHAR2
1914 , p_doc_type IN VARCHAR2
1915 , p_doc_subtype IN VARCHAR2
1916 , p_employee_id IN NUMBER
1917 , p_override_funds IN VARCHAR2
1918 , p_use_gl_date IN VARCHAR2
1919 , p_override_date IN DATE
1920 , p_report_successes IN VARCHAR2
1921 , x_po_return_code OUT NOCOPY VARCHAR2
1922 , x_detailed_results OUT NOCOPY po_fcout_type
1923 )
1924 IS
1925
1926 l_api_name CONSTANT varchar2(30) := 'DO_ADJUST';
1927 l_api_version CONSTANT NUMBER := 1.0;
1928 l_log_head CONSTANT VARCHAR2(100) := c_log_head || l_api_name;
1929 l_progress VARCHAR2(3) := '000';
1930 l_report_return_status VARCHAR2(1);
1931 l_online_report_id NUMBER;
1932
1933 BEGIN
1934
1935 IF g_debug_stmt THEN
1936 PO_DEBUG.debug_begin(l_log_head);
1937 PO_DEBUG.debug_var(l_log_head,l_progress,'p_api_version',p_api_version);
1938 PO_DEBUG.debug_var(l_log_head,l_progress,'p_commit',p_commit);
1939 PO_DEBUG.debug_var(l_log_head,l_progress,'p_init_msg_list',p_init_msg_list);
1940 PO_DEBUG.debug_var(l_log_head,l_progress,'p_validation_level',p_validation_level);
1941 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_type',p_doc_type);
1942 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_subtype',p_doc_subtype);
1943 PO_DEBUG.debug_var(l_log_head,l_progress,'p_employee_id',p_employee_id);
1944 PO_DEBUG.debug_var(l_log_head,l_progress,'p_override_funds',p_override_funds);
1945 PO_DEBUG.debug_var(l_log_head,l_progress,'p_use_gl_date',p_use_gl_date);
1946 PO_DEBUG.debug_var(l_log_head,l_progress,'p_override_date',p_override_date);
1947 PO_DEBUG.debug_var(l_log_head,l_progress,'p_report_successes',p_report_successes);
1948 END IF;
1949
1950 l_progress := '010';
1951
1952 -- Standard Start of API savepoint
1953 SAVEPOINT DO_ADJUST_SP;
1954
1955 l_progress := '020';
1956
1957 -- Standard call to check for call compatibility
1958
1959 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1960 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1961 END IF;
1962
1963 l_progress := '030';
1964
1965 -- Initialize message list if p_init_msg_list is set to TRUE.
1966
1967 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1968 FND_MSG_PUB.initialize;
1969 END IF;
1970
1971 l_progress := '040';
1972
1973 -- Initialize API return status to success
1974
1975 x_return_status := FND_API.G_RET_STS_SUCCESS;
1976
1977 l_progress := '050';
1978
1979 IF g_debug_stmt THEN
1980 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Calling Private Procedure');
1981 END IF;
1982
1983 l_progress := '060';
1984
1985 PO_DOCUMENT_FUNDS_PVT.do_adjust(
1986 x_return_status => x_return_status
1987 , p_doc_type => p_doc_type
1988 , p_doc_subtype => p_doc_subtype
1989 , p_override_funds => p_override_funds
1990 , p_use_gl_date => p_use_gl_date
1991 , p_validate_document => g_parameter_YES
1992 , p_override_date => p_override_date
1993 , p_employee_id => p_employee_id
1994 , x_po_return_code => x_po_return_code
1995 , x_online_report_id => l_online_report_id
1996 );
1997
1998 l_progress := '100';
1999
2000 IF ( x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
2001 OR x_return_status IS NULL
2002 ) THEN
2003 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2004 END IF;
2005
2006 l_progress := '110';
2007
2008 IF g_debug_stmt THEN
2009 PO_DEBUG.debug_stmt(l_log_head,l_progress,
2010 'Returned from Private Procedure successfully');
2011 END IF;
2012
2013 l_progress := '120';
2014
2015 PO_DOCUMENT_FUNDS_PVT.create_report_object(
2016 x_return_status => l_report_return_status
2017 , p_online_report_id => l_online_report_id
2018 , p_report_successes => p_report_successes
2019 , x_report_object => x_detailed_results
2020 );
2021
2022 l_progress := '200';
2023
2024 IF ( l_report_return_status = FND_API.G_RET_STS_UNEXP_ERROR
2025 OR l_report_return_status IS NULL
2026 ) THEN
2027 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2028 END IF;
2029
2030 l_progress := '300';
2031
2032 -- Standard check of p_commit.
2033 IF FND_API.To_Boolean(p_commit) THEN
2034 COMMIT WORK;
2035 END IF;
2036
2037 l_progress := '900';
2038
2039 IF g_debug_stmt THEN
2040 PO_DEBUG.debug_var(l_log_head,l_progress,'l_report_return_status',l_report_return_status);
2041 PO_DEBUG.debug_var(l_log_head,l_progress,'l_online_report_id',l_online_report_id);
2042 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
2043 PO_DEBUG.debug_var(l_log_head,l_progress,'x_po_return_code',x_po_return_code);
2044 PO_DEBUG.debug_end(l_log_head);
2045 END IF;
2046
2047 EXCEPTION
2048 WHEN OTHERS THEN
2049 BEGIN
2050 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS Start');
2051 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2052 IF g_debug_stmt THEN
2053 PO_DEBUG.debug_var(l_log_head,l_progress,'l_report_return_status',l_report_return_status);
2054 PO_DEBUG.debug_var(l_log_head,l_progress,'l_online_report_id',l_online_report_id);
2055 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
2056 PO_DEBUG.debug_var(l_log_head,l_progress,'x_po_return_code',x_po_return_code);
2057 END IF;
2058 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2059 FND_MSG_PUB.add_exc_msg(G_PKG_NAME,l_api_name,SQLERRM);
2060 END IF;
2061 ROLLBACK TO DO_ADJUST_SP;
2062 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS End');
2063 EXCEPTION
2064 WHEN OTHERS THEN
2065 NULL;
2066 END;
2067
2068 END do_adjust;
2069
2070
2071 -------------------------------------------------------------------------------
2072 --Start of Comments
2073 --Name: do_final_close
2074 --Pre-reqs:
2075 -- N/A.
2076 --Modifies:
2077 -- Creates encumbrance entries in the gl_bc_packets table
2078 -- Adds distribution-specific transaction information into the
2079 -- po_online_report_text table
2080 --Locks:
2081 -- None.
2082 --Function:
2083 -- This procedure liquidates the encumbrance on all eligible distributions of
2084 -- the requested document or document subentity
2085 --Parameters:
2086 --IN:
2087 --p_api_version
2088 -- Apps API Std - To control correct version in use
2089 --p_commit
2090 -- Apps API Std - Should data be commited?
2091 --p_init_msg_list
2092 -- Apps API Std - Initialize the message list?
2093 --p_validation_level
2094 -- Apps API Std - Level of validations to be done
2095 --p_doc_type
2096 -- Differentiates between the doc being a REQ, PA, PO, or RELEASE,
2097 -- which is used to identify the tables to look at (PO vs. Req)
2098 -- and the join conditions
2099 --p_doc_subtype
2100 -- Differentiates between the subtypes of documents
2101 -- REQ: NULL
2102 -- PO: STANDARD, PLANNED
2103 -- PA: CONTRACT, BLANKET
2104 -- RELEASE: SCHEDULED, BLANKET
2105 --p_doc_level
2106 -- Specifies the level of the document that is being checked:
2107 -- HEADER, LINE, SHIPMENT, DISTRIBUTION
2108 --p_doc_level_id
2109 -- The id corresponding to the doc level type:
2110 -- header_id/release_id, line_id, line_location_id, distribution_id
2111 --p_override_date
2112 -- Caller-specified date to use for distribution encumbrance date in GL entries
2113 --p_use_gl_date
2114 -- Flag that specifies whether to always prefer using the existing distribution
2115 -- GL date instead of the override date, when possible
2116 --p_report_successes
2117 -- Determines whether the x_detailed_results object contains information about
2118 -- distributions for which encumbrance actions were successful. Information for
2119 -- distributions with Warnings and Errors are always included.
2120 --OUT:
2121 --x_return_status
2122 -- FND_API.G_RET_STS_SUCCESS if validation succeeds
2123 -- FND_API.G_RET_STS_ERROR if validation fails
2124 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
2125 --x_detailed_results
2126 -- Object table that stores distribution specific
2127 -- reporting information for the encumbrance transaction
2128 --x_po_return_code
2129 -- Indicates whether PO is classifying this transaction as an
2130 -- Error/Warning/Partial Success/Success
2131 --Testing:
2132 --
2133 --End of Comments
2134 -------------------------------------------------------------------------------
2135 PROCEDURE do_final_close(
2136 p_api_version IN NUMBER
2137 , p_commit IN VARCHAR2 default FND_API.G_FALSE
2138 , p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE
2139 , p_validation_level IN NUMBER default FND_API.G_VALID_LEVEL_FULL
2140 , x_return_status OUT NOCOPY VARCHAR2
2141 , p_doc_type IN VARCHAR2
2142 , p_doc_subtype IN VARCHAR2
2143 , p_doc_level IN VARCHAR2
2144 , p_doc_level_id IN NUMBER
2145 , p_override_date IN DATE
2146 , p_use_gl_date IN VARCHAR2
2147 , p_report_successes IN VARCHAR2
2148 , x_po_return_code OUT NOCOPY VARCHAR2
2149 , x_detailed_results OUT NOCOPY po_fcout_type
2150 )
2151 IS
2152
2153 l_api_name CONSTANT varchar2(30) := 'DO_FINAL_CLOSE';
2154 l_api_version CONSTANT NUMBER := 1.0;
2155 l_log_head CONSTANT VARCHAR2(100) := c_log_head || l_api_name;
2156 l_progress VARCHAR2(3) := '000';
2157 l_report_return_status VARCHAR2(1);
2158 l_online_report_id NUMBER;
2159
2160 BEGIN
2161
2162 IF g_debug_stmt THEN
2163 PO_DEBUG.debug_begin(l_log_head);
2164 PO_DEBUG.debug_var(l_log_head,l_progress,'p_api_version',p_api_version);
2165 PO_DEBUG.debug_var(l_log_head,l_progress,'p_commit',p_commit);
2166 PO_DEBUG.debug_var(l_log_head,l_progress,'p_init_msg_list',p_init_msg_list);
2167 PO_DEBUG.debug_var(l_log_head,l_progress,'p_validation_level',p_validation_level);
2168 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_type',p_doc_type);
2169 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_subtype',p_doc_subtype);
2170 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_level',p_doc_level);
2171 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_level_id',p_doc_level_id);
2172 PO_DEBUG.debug_var(l_log_head,l_progress,'p_override_date',p_override_date);
2173 PO_DEBUG.debug_var(l_log_head,l_progress,'p_use_gl_date',p_use_gl_date);
2174 PO_DEBUG.debug_var(l_log_head,l_progress,'p_report_successes',p_report_successes);
2175 END IF;
2176
2177 l_progress := '010';
2178
2179 -- Standard Start of API savepoint
2180 SAVEPOINT DO_FINAL_CLOSE_SP;
2181
2182 l_progress := '020';
2183
2184 -- Standard call to check for call compatibility
2185
2186 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
2187 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2188 END IF;
2189
2190 l_progress := '030';
2191
2192 -- Initialize message list if p_init_msg_list is set to TRUE.
2193
2194 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2195 FND_MSG_PUB.initialize;
2196 END IF;
2197
2198 l_progress := '040';
2199
2200 -- Initialize API return status to success
2201
2202 x_return_status := FND_API.G_RET_STS_SUCCESS;
2203
2204 l_progress := '050';
2205
2206 IF g_debug_stmt THEN
2207 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Calling Private Procedure');
2208 END IF;
2209
2210 l_progress := '060';
2211
2212 PO_DOCUMENT_FUNDS_PVT.do_final_close(
2213 x_return_status => x_return_status
2214 , p_doc_type => p_doc_type
2215 , p_doc_subtype => p_doc_subtype
2216 , p_doc_level => p_doc_level
2217 , p_doc_level_id => p_doc_level_id
2218 , p_use_enc_gt_flag => g_parameter_NO
2219 , p_use_gl_date => p_use_gl_date
2220 , p_override_date => p_override_date
2221 , x_po_return_code => x_po_return_code
2222 , x_online_report_id => l_online_report_id
2223 );
2224
2225 l_progress := '100';
2226
2227 IF ( x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
2228 OR x_return_status IS NULL
2229 ) THEN
2230 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2231 END IF;
2232
2233 l_progress := '110';
2234
2235 IF g_debug_stmt THEN
2236 PO_DEBUG.debug_stmt(l_log_head,l_progress,
2237 'Returned from Private Procedure successfully');
2238 END IF;
2239
2240 l_progress := '120';
2241
2242 PO_DOCUMENT_FUNDS_PVT.create_report_object(
2243 x_return_status => l_report_return_status
2244 , p_online_report_id => l_online_report_id
2245 , p_report_successes => p_report_successes
2246 , x_report_object => x_detailed_results
2247 );
2248
2249 l_progress := '200';
2250
2251 IF ( l_report_return_status = FND_API.G_RET_STS_UNEXP_ERROR
2252 OR l_report_return_status IS NULL
2253 ) THEN
2254 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2255 END IF;
2256
2257 l_progress := '300';
2258
2259 -- Standard check of p_commit.
2260 IF FND_API.To_Boolean(p_commit) THEN
2261 COMMIT WORK;
2262 END IF;
2263
2264 l_progress := '900';
2265
2266 IF g_debug_stmt THEN
2267 PO_DEBUG.debug_var(l_log_head,l_progress,'l_report_return_status',l_report_return_status);
2268 PO_DEBUG.debug_var(l_log_head,l_progress,'l_online_report_id',l_online_report_id);
2269 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
2270 PO_DEBUG.debug_var(l_log_head,l_progress,'x_po_return_code',x_po_return_code);
2271 PO_DEBUG.debug_end(l_log_head);
2272 END IF;
2273
2274 EXCEPTION
2275 WHEN OTHERS THEN
2276 BEGIN
2277 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS Start');
2278 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2279 IF g_debug_stmt THEN
2280 PO_DEBUG.debug_var(l_log_head,l_progress,'l_report_return_status',l_report_return_status);
2281 PO_DEBUG.debug_var(l_log_head,l_progress,'l_online_report_id',l_online_report_id);
2282 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
2283 PO_DEBUG.debug_var(l_log_head,l_progress,'x_po_return_code',x_po_return_code);
2284 END IF;
2285 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2286 FND_MSG_PUB.add_exc_msg(G_PKG_NAME,l_api_name,SQLERRM);
2287 END IF;
2288 ROLLBACK TO DO_FINAL_CLOSE_SP;
2289 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS End');
2290 EXCEPTION
2291 WHEN OTHERS THEN
2292 NULL;
2293 END;
2294
2295 END do_final_close;
2296
2297
2298 -------------------------------------------------------------------------------
2299 --Start of Comments
2300 --Name: reinstate_po_encumbrance
2301 --Pre-reqs:
2302 -- None.
2303 --Modifies:
2304 -- None.
2305 --Locks:
2306 -- None.
2307 --Function:
2308 -- It should created GL reversal entries if the distribution is
2309 -- either unreserved or the its shipment is Finally closed.
2310 -- Examine Private API for AP usage notes.
2311 --Parameters:
2312 --IN:
2313 --p_api_version
2314 -- Apps API Std - To control correct version in use
2315 --p_commit
2316 -- Apps API Std - Should data be commited?
2317 --p_init_msg_list
2318 -- Apps API Std - Initialize the message list?
2319 --p_validation_level
2320 -- Apps API Std - Level of validations to be done
2321 --p_distribution_id
2322 -- po_distribution_id
2323 --p_invoice_id
2324 -- po_invoice_id
2325 --p_encumbrance_amt
2326 -- encumbrance amount in functional currency for which AP will reinstate the
2327 -- PO encumbrance on Invoice cancellation. AP should take care of the
2328 -- overbilled case and any variances.
2329 -- IF (p_encumbrance_amt >0) THEN
2330 -- Invoice Cancellation, PO API does Cr (AP is doing -Cr)
2331 -- ELSE
2332 -- Memo Cancellation, PO API does Dr (AP is doing -Dr)
2333 --p_qty_cancelled
2334 -- Invoice qty cancelled for the PO distribution. This should be PO UOM
2335 -- p_qty_cancelled is -ve for Invoice Cancellation
2336 -- +ve for Credit Memo Cancellation
2337 --p_budget_account_id
2338 -- Budget account id - account on which the AP does PO reversal
2339 --p_gl_date
2340 -- Valid open Date on which AP will reinstate PO encumbrance on Invoice
2341 -- cancellation. We want the Dr and Cr to go in the same period.
2342 --p_period_name
2343 -- period name
2344 --p_period_year
2345 -- period year
2346 --p_period_num
2347 -- period num
2348 --p_quarter_num
2349 -- quarter num
2350 --p_tax_line_flag -- Bug 3480949
2351 -- Set depending upon which values of p_encumbered_amt AP calls the API with.
2352 -- g_parameter_NO - the original amounts before tax applied
2353 -- g_parameter_YES - the tax on the original amounts only
2354 -- Default NULL, which will be assumed to be g_parameter_NO
2355 -- Check Priavate API comments for more usage notes.
2356 --OUT:
2357 --x_packet_id
2358 -- GL PACKET ID, if gl entries are made otherwise null
2359 --x_return_status
2360 -- FND_API.G_RET_STS_SUCCESS if validation succeeds
2361 -- FND_API.G_RET_STS_ERROR if validation fails
2362 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
2363 --Testing:
2364 --
2365 --End of Comments
2366 -------------------------------------------------------------------------------
2367 PROCEDURE reinstate_po_encumbrance(
2368 p_api_version IN NUMBER,
2369 p_commit IN VARCHAR2 default FND_API.G_FALSE,
2370 p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE,
2371 p_validation_level IN NUMBER default FND_API.G_VALID_LEVEL_FULL,
2372 p_distribution_id IN NUMBER,
2373 p_invoice_id IN NUMBER,
2374 p_encumbrance_amt IN NUMBER,
2375 p_qty_cancelled IN NUMBER,
2376 p_budget_account_id IN NUMBER,
2377 p_gl_date IN DATE,
2378 p_period_name IN VARCHAR2,
2379 p_period_year IN VARCHAR2,
2380 p_period_num IN VARCHAR2,
2381 p_quarter_num IN VARCHAR2,
2382 p_tax_line_flag IN VARCHAR2 default NULL, -- Bug 3480949
2383 x_packet_id OUT NOCOPY NUMBER,
2384 x_return_status OUT NOCOPY VARCHAR2
2385 )
2386 IS
2387
2388 l_api_name CONSTANT varchar2(30) := 'REINSTATE_PO_ENCUMBRANCE';
2389 l_api_version CONSTANT NUMBER := 1.0;
2390 l_log_head CONSTANT VARCHAR2(100) := c_log_head || l_api_name;
2391 l_progress VARCHAR2(3) := '000';
2392
2393 BEGIN
2394
2395 IF g_debug_stmt THEN
2396 PO_DEBUG.debug_begin(l_log_head);
2397 PO_DEBUG.debug_var(l_log_head,l_progress,'p_api_version',p_api_version);
2398 PO_DEBUG.debug_var(l_log_head,l_progress,'p_commit',p_commit);
2399 PO_DEBUG.debug_var(l_log_head,l_progress,'p_init_msg_list',p_init_msg_list);
2400 PO_DEBUG.debug_var(l_log_head,l_progress,'p_validation_level',p_validation_level);
2401 PO_DEBUG.debug_var(l_log_head,l_progress,'p_distribution_id',p_distribution_id);
2402 PO_DEBUG.debug_var(l_log_head,l_progress,'p_invoice_id',p_invoice_id);
2403 PO_DEBUG.debug_var(l_log_head,l_progress,'p_encumbrance_amt',p_encumbrance_amt);
2404 PO_DEBUG.debug_var(l_log_head,l_progress,'p_qty_cancelled',p_qty_cancelled);
2405 PO_DEBUG.debug_var(l_log_head,l_progress,'p_budget_account_id',p_budget_account_id);
2406 PO_DEBUG.debug_var(l_log_head,l_progress,'p_gl_date',p_gl_date);
2407 PO_DEBUG.debug_var(l_log_head,l_progress,'p_period_name',p_period_name);
2408 PO_DEBUG.debug_var(l_log_head,l_progress,'p_period_year',p_period_year);
2409 PO_DEBUG.debug_var(l_log_head,l_progress,'p_period_num',p_period_num);
2410 PO_DEBUG.debug_var(l_log_head,l_progress,'p_quarter_num',p_quarter_num);
2411
2412 -- Bug 3480949: log p_tax_line_flag
2413 PO_DEBUG.debug_var(l_log_head,l_progress,'p_tax_line_flag',p_tax_line_flag);
2414
2415 END IF;
2416
2417 l_progress := '010';
2418
2419 -- Standard Start of API savepoint
2420 SAVEPOINT REINSTATE_PO_ENCUMBRANCE_SP;
2421
2422 l_progress := '020';
2423
2424 -- Standard call to check for call compatibility
2425
2426 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
2427 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2428 END IF;
2429
2430 l_progress := '030';
2431
2432 -- Initialize message list if p_init_msg_list is set to TRUE.
2433
2434 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2435 FND_MSG_PUB.initialize;
2436 END IF;
2437
2438 l_progress := '040';
2439
2440 -- Initialize API return status to success
2441
2442 x_return_status := FND_API.G_RET_STS_SUCCESS;
2443
2444 l_progress := '050';
2445
2446 IF g_debug_stmt THEN
2447 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Calling Private Procedure');
2448 END IF;
2449
2450 l_progress := '060';
2451
2452 PO_DOCUMENT_FUNDS_PVT.reinstate_po_encumbrance(
2453 x_return_status => x_return_status,
2454 p_distribution_id => p_distribution_id,
2455 p_invoice_id => p_invoice_id,
2456 p_encumbrance_amt => p_encumbrance_amt,
2457 p_qty_cancelled => p_qty_cancelled,
2458 p_budget_account_id => p_budget_account_id,
2459 p_gl_date => p_gl_date,
2460 p_period_name => p_period_name,
2461 p_period_year => p_period_year,
2462 p_period_num => p_period_num,
2463 p_quarter_num => p_quarter_num,
2464 p_tax_line_flag => p_tax_line_flag, -- Bug 3480949
2465 x_packet_id => x_packet_id
2466 );
2467
2468 l_progress := '100';
2469
2470 IF ( x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
2471 OR x_return_status IS NULL
2472 ) THEN
2473 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2474 END IF;
2475
2476 l_progress := '110';
2477
2478 IF g_debug_stmt THEN
2479 PO_DEBUG.debug_stmt(l_log_head,l_progress,
2480 'Returned from Private Procedure successfully');
2481 END IF;
2482
2483 l_progress := '200';
2484
2485 -- Standard check of p_commit.
2486 IF FND_API.To_Boolean(p_commit) THEN
2487 COMMIT WORK;
2488 END IF;
2489
2490 l_progress := '900';
2491
2492 IF g_debug_stmt THEN
2493 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
2494 PO_DEBUG.debug_var(l_log_head,l_progress,'x_packet_id',x_packet_id);
2495 PO_DEBUG.debug_end(l_log_head);
2496 END IF;
2497
2498 EXCEPTION
2499 WHEN OTHERS THEN
2500 BEGIN
2501 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS Start');
2502 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2503 IF g_debug_stmt THEN
2504 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
2505 PO_DEBUG.debug_var(l_log_head,l_progress,'x_packet_id',x_packet_id);
2506 END IF;
2507 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2508 FND_MSG_PUB.add_exc_msg(G_PKG_NAME,l_api_name,SQLERRM);
2509 END IF;
2510 ROLLBACK TO REINSTATE_PO_ENCUMBRANCE_SP;
2511 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS End');
2512 EXCEPTION
2513 WHEN OTHERS THEN
2514 NULL;
2515 END;
2516
2517 END reinstate_po_encumbrance;
2518
2519
2520
2521 ------------------------------------------------------------------------------
2522 --Start of Comments
2523 --Name: is_agreement_encumbered
2524 --Pre-reqs:
2525 -- N/A.
2526 --Modifies:
2527 -- N/A.
2528 --Locks:
2529 -- None.
2530 --Function:
2531 -- This procedure is an API that informs callers whether a particular agreement
2532 -- is/can be encumbered, based on its header level encumbrance_required flag.
2533 -- The output table contains results in the same ordering as the input table.
2534 --Parameters:
2535 --IN:
2536 --p_api_version
2537 -- Apps API Std - To control correct version in use
2538 --p_commit
2539 -- Apps API Std - Should data be commited?
2540 --p_init_msg_list
2541 -- Apps API Std - Initialize the message list?
2542 --p_validation_level
2543 -- Apps API Std - Level of validations to be done
2544 --p_agreement_ids_tbl
2545 -- A table of po_header_ids corresponding to the PAs that we are checking
2546 -- the encumbered state of.
2547 --OUT:
2548 --x_return_status
2549 -- FND_API.G_RET_STS_SUCCESS if validation succeeds
2550 -- FND_API.G_RET_STS_ERROR if validation fails
2551 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
2552 --x_results_tbl
2553 -- A table of Y/N results indicating whether each PA is encumbered or not.
2554 -- Y = the given PA is/can be encumbered.
2555 -- N = the PA is not eligible for encumbrance
2556 --Testing:
2557 --
2558 --End of Comments
2559 -------------------------------------------------------------------------------
2560 PROCEDURE is_agreement_encumbered(
2561 p_api_version IN NUMBER
2562 , p_commit IN VARCHAR2 default FND_API.G_FALSE
2563 , p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE
2564 , p_validation_level IN NUMBER default FND_API.G_VALID_LEVEL_FULL
2565 , x_return_status OUT NOCOPY VARCHAR2
2566 , p_agreement_id_tbl IN PO_TBL_NUMBER
2567 , x_agreement_encumbered_tbl OUT NOCOPY PO_TBL_VARCHAR1
2568 )
2569 IS
2570
2571 l_api_name CONSTANT varchar2(30) := 'IS_AGREEMENT_ENCUMBERED';
2572 l_api_version CONSTANT NUMBER := 1.0;
2573 l_log_head CONSTANT VARCHAR2(100) := c_log_head || l_api_name;
2574 l_progress VARCHAR2(3) := '000';
2575
2576 BEGIN
2577
2578 IF g_debug_stmt THEN
2579 PO_DEBUG.debug_begin(l_log_head);
2580 PO_DEBUG.debug_var(l_log_head,l_progress,'p_api_version',p_api_version);
2581 PO_DEBUG.debug_var(l_log_head,l_progress,'p_commit',p_commit);
2582 PO_DEBUG.debug_var(l_log_head,l_progress,'p_init_msg_list',p_init_msg_list);
2583 PO_DEBUG.debug_var(l_log_head,l_progress,'p_validation_level',p_validation_level);
2584 PO_DEBUG.debug_var(l_log_head,l_progress,'p_agreement_id_tbl',p_agreement_id_tbl);
2585 END IF;
2586
2587 l_progress := '010';
2588
2589 -- Standard Start of API savepoint
2590 SAVEPOINT IS_AGREEMENT_ENCUMBERED_SP;
2591
2592 l_progress := '020';
2593
2594 -- Standard call to check for call compatibility
2595
2596 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
2597 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2598 END IF;
2599
2600 l_progress := '030';
2601
2602 -- Initialize message list if p_init_msg_list is set to TRUE.
2603
2604 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2605 FND_MSG_PUB.initialize;
2606 END IF;
2607
2608 l_progress := '040';
2609
2610 -- Initialize API return status to success
2611
2612 x_return_status := FND_API.G_RET_STS_SUCCESS;
2613
2614 l_progress := '050';
2615
2616 IF g_debug_stmt THEN
2617 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Calling Private Procedure');
2618 END IF;
2619
2620 l_progress := '060';
2621
2622 PO_DOCUMENT_FUNDS_PVT.is_agreement_encumbered(
2623 x_return_status => x_return_status
2624 , p_agreement_id_tbl => p_agreement_id_tbl
2625 , x_agreement_encumbered_tbl => x_agreement_encumbered_tbl
2626 );
2627
2628 l_progress := '100';
2629
2630 IF ( x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
2631 OR x_return_status IS NULL
2632 ) THEN
2633 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2634 END IF;
2635
2636 l_progress := '110';
2637
2638 IF g_debug_stmt THEN
2639 PO_DEBUG.debug_stmt(l_log_head,l_progress,
2640 'Returned from Private Procedure successfully');
2641 END IF;
2642
2643 l_progress := '200';
2644
2645 -- Standard check of p_commit.
2646 IF FND_API.To_Boolean(p_commit) THEN
2647 COMMIT WORK;
2648 END IF;
2649
2650 l_progress := '900';
2651
2652 IF g_debug_stmt THEN
2653 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
2654 PO_DEBUG.debug_var(l_log_head,l_progress,'x_agreement_encumbered_tbl',x_agreement_encumbered_tbl);
2655 PO_DEBUG.debug_end(l_log_head);
2656 END IF;
2657
2658 EXCEPTION
2659 WHEN OTHERS THEN
2660 BEGIN
2661 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS Start');
2662 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2663 IF g_debug_stmt THEN
2664 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
2665 PO_DEBUG.debug_var(l_log_head,l_progress,'x_agreement_encumbered_tbl',x_agreement_encumbered_tbl);
2666 END IF;
2667 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2668 FND_MSG_PUB.add_exc_msg(G_PKG_NAME,l_api_name,SQLERRM);
2669 END IF;
2670 ROLLBACK TO IS_AGREEMENT_ENCUMBERED_SP;
2671 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS End');
2672 EXCEPTION
2673 WHEN OTHERS THEN
2674 NULL;
2675 END;
2676
2677 END is_agreement_encumbered;
2678
2679
2680 ------------------------------------------------------------------------------
2681 --Start of Comments
2682 --Name: is_reservable
2683 --Pre-reqs:
2684 -- N/A.
2685 --Modifies:
2686 -- N/A.
2687 --Locks:
2688 -- None.
2689 --Function:
2690 -- This procedure determines whether a given document has any distributions that
2691 -- are eligible for funds reservation.
2692 --Parameters:
2693 --IN:
2694 --p_api_version
2695 -- Apps API Std - To control correct version in use
2696 --p_commit
2697 -- Apps API Std - Should data be commited?
2698 --p_init_msg_list
2699 -- Apps API Std - Initialize the message list?
2700 --p_validation_level
2701 -- Apps API Std - Level of validations to be done
2702 --p_doc_type
2703 -- Differentiates between the doc being a REQ, PA, PO, or RELEASE,
2704 -- which is used to identify the tables to look at (PO vs. Req)
2705 -- and the join conditions
2706 --p_doc_subtype
2707 -- Differentiates between the subtypes of documents
2708 -- REQ: NULL
2709 -- PO: STANDARD, PLANNED
2710 -- PA: CONTRACT, BLANKET
2711 -- RELEASE: SCHEDULED, BLANKET
2712 --p_doc_level
2713 -- Specifies the level of the document that is being checked:
2714 -- HEADER, LINE, SHIPMENT, DISTRIBUTION
2715 --p_doc_level_id
2716 -- The id corresponding to the doc level type:
2717 -- header_id/release_id, line_id, line_location_id, distribution_id
2718 --OUT:
2719 --x_return_status
2720 -- FND_API.G_RET_STS_SUCCESS if validation succeeds
2721 -- FND_API.G_RET_STS_ERROR if validation fails
2722 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
2723 --x_result
2724 -- Indicates whether funds reservation is possible on this entity
2725 -- 'Y' means it is possible, 'N' means it isn't.
2726 --Testing:
2727 --
2728 --End of Comments
2729 -------------------------------------------------------------------------------
2730 PROCEDURE is_reservable(
2731 p_api_version IN NUMBER
2732 , p_commit IN VARCHAR2 default FND_API.G_FALSE
2733 , p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE
2734 , p_validation_level IN NUMBER default FND_API.G_VALID_LEVEL_FULL
2735 , x_return_status OUT NOCOPY VARCHAR2
2736 , p_doc_type IN VARCHAR2
2737 , p_doc_subtype IN VARCHAR2
2738 , p_doc_level IN VARCHAR2
2739 , p_doc_level_id IN NUMBER
2740 , x_reservable_flag OUT NOCOPY VARCHAR2
2741 )
2742 IS
2743
2744 l_api_name CONSTANT varchar2(30) := 'IS_RESERVABLE';
2745 l_api_version CONSTANT NUMBER := 1.0;
2746 l_log_head CONSTANT VARCHAR2(100) := c_log_head || l_api_name;
2747 l_progress VARCHAR2(3) := '000';
2748
2749 BEGIN
2750
2751 IF g_debug_stmt THEN
2752 PO_DEBUG.debug_begin(l_log_head);
2753 PO_DEBUG.debug_var(l_log_head,l_progress,'p_api_version',p_api_version);
2754 PO_DEBUG.debug_var(l_log_head,l_progress,'p_commit',p_commit);
2755 PO_DEBUG.debug_var(l_log_head,l_progress,'p_init_msg_list',p_init_msg_list);
2756 PO_DEBUG.debug_var(l_log_head,l_progress,'p_validation_level',p_validation_level);
2757 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_type',p_doc_type);
2758 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_subtype',p_doc_subtype);
2759 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_level',p_doc_level);
2760 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_level_id',p_doc_level_id);
2761 END IF;
2762
2763 l_progress := '010';
2764
2765 -- Standard Start of API savepoint
2766 SAVEPOINT IS_RESERVABLE_SP;
2767
2768 l_progress := '020';
2769
2770 -- Standard call to check for call compatibility
2771
2772 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
2773 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2774 END IF;
2775
2776 l_progress := '030';
2777
2778 -- Initialize message list if p_init_msg_list is set to TRUE.
2779
2780 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2781 FND_MSG_PUB.initialize;
2782 END IF;
2783
2784 l_progress := '040';
2785
2786 -- Initialize API return status to success
2787
2788 x_return_status := FND_API.G_RET_STS_SUCCESS;
2789
2790 l_progress := '050';
2791
2792 IF g_debug_stmt THEN
2793 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Calling Private Procedure');
2794 END IF;
2795
2796 l_progress := '060';
2797
2798 PO_DOCUMENT_FUNDS_PVT.is_reservable(
2799 x_return_status => x_return_status
2800 , p_doc_type => p_doc_type
2801 , p_doc_subtype => p_doc_subtype
2802 , p_doc_level => p_doc_level
2803 , p_doc_level_id => p_doc_level_id
2804 , x_reservable_flag => x_reservable_flag
2805 );
2806
2807 l_progress := '100';
2808
2809 IF ( x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
2810 OR x_return_status IS NULL
2811 ) THEN
2812 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2813 END IF;
2814
2815 l_progress := '110';
2816
2817 IF g_debug_stmt THEN
2818 PO_DEBUG.debug_stmt(l_log_head,l_progress,
2819 'Returned from Private Procedure successfully');
2820 END IF;
2821
2822 l_progress := '200';
2823
2824 -- Standard check of p_commit.
2825 IF FND_API.To_Boolean(p_commit) THEN
2826 COMMIT WORK;
2827 END IF;
2828
2829 l_progress := '900';
2830
2831 IF g_debug_stmt THEN
2832 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
2833 PO_DEBUG.debug_var(l_log_head,l_progress,'x_reservable_flag',x_reservable_flag);
2834 PO_DEBUG.debug_end(l_log_head);
2835 END IF;
2836
2837 EXCEPTION
2838 WHEN OTHERS THEN
2839 BEGIN
2840 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS Start');
2841 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2842 IF g_debug_stmt THEN
2843 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
2844 PO_DEBUG.debug_var(l_log_head,l_progress,'x_reservable_flag',x_reservable_flag);
2845 END IF;
2846 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2847 FND_MSG_PUB.add_exc_msg(G_PKG_NAME,l_api_name,SQLERRM);
2848 END IF;
2849 ROLLBACK TO IS_RESERVABLE_SP;
2850 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS End');
2851 EXCEPTION
2852 WHEN OTHERS THEN
2853 NULL;
2854 END;
2855
2856 END is_reservable;
2857
2858
2859 ------------------------------------------------------------------------------
2860 --Start of Comments
2861 --Name: is_unreservable
2862 --Pre-reqs:
2863 -- N/A.
2864 --Modifies:
2865 -- N/A.
2866 --Locks:
2867 -- None.
2868 --Function:
2869 -- This procedure determines whether a given document has any distributions that
2870 -- are eligible for funds reservation.
2871 --Parameters:
2872 --IN:
2873 --p_api_version
2874 -- Apps API Std - To control correct version in use
2875 --p_commit
2876 -- Apps API Std - Should data be commited?
2877 --p_init_msg_list
2878 -- Apps API Std - Initialize the message list?
2879 --p_validation_level
2880 -- Apps API Std - Level of validations to be done
2881 --p_doc_type
2882 -- Differentiates between the doc being a REQ, PA, PO, or RELEASE,
2883 -- which is used to identify the tables to look at (PO vs. Req)
2884 -- and the join conditions
2885 --p_doc_subtype
2886 -- Differentiates between the subtypes of documents
2887 -- REQ: NULL
2888 -- PO: STANDARD, PLANNED
2889 -- PA: CONTRACT, BLANKET
2890 -- RELEASE: SCHEDULED, BLANKET
2891 --p_doc_level
2892 -- Specifies the level of the document that is being checked:
2893 -- HEADER, LINE, SHIPMENT, DISTRIBUTION
2894 --p_doc_level_id
2895 -- The id corresponding to the doc level type:
2896 -- header_id/release_id, line_id, line_location_id, distribution_id
2897 --OUT:
2898 --x_return_status
2899 -- FND_API.G_RET_STS_SUCCESS if validation succeeds
2900 -- FND_API.G_RET_STS_ERROR if validation fails
2901 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
2902 --x_result
2903 -- Indicates whether funds reservation is possible on this entity
2904 -- 'Y' means it is possible, 'N' means it isn't.
2905 --Testing:
2906 --
2907 --End of Comments
2908 -------------------------------------------------------------------------------
2909 PROCEDURE is_unreservable(
2910 p_api_version IN NUMBER
2911 , p_commit IN VARCHAR2 default FND_API.G_FALSE
2912 , p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE
2913 , p_validation_level IN NUMBER default FND_API.G_VALID_LEVEL_FULL
2914 , x_return_status OUT NOCOPY VARCHAR2
2915 , p_doc_type IN VARCHAR2
2916 , p_doc_subtype IN VARCHAR2
2917 , p_doc_level IN VARCHAR2
2918 , p_doc_level_id IN NUMBER
2919 , x_unreservable_flag OUT NOCOPY VARCHAR2
2920 )
2921 IS
2922
2923 l_api_name CONSTANT varchar2(30) := 'IS_UNRESERVABLE';
2924 l_api_version CONSTANT NUMBER := 1.0;
2925 l_log_head CONSTANT VARCHAR2(100) := c_log_head || l_api_name;
2926 l_progress VARCHAR2(3) := '000';
2927
2928 BEGIN
2929
2930 IF g_debug_stmt THEN
2931 PO_DEBUG.debug_begin(l_log_head);
2932 PO_DEBUG.debug_var(l_log_head,l_progress,'p_api_version',p_api_version);
2933 PO_DEBUG.debug_var(l_log_head,l_progress,'p_commit',p_commit);
2934 PO_DEBUG.debug_var(l_log_head,l_progress,'p_init_msg_list',p_init_msg_list);
2935 PO_DEBUG.debug_var(l_log_head,l_progress,'p_validation_level',p_validation_level);
2936 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_type',p_doc_type);
2937 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_subtype',p_doc_subtype);
2938 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_level',p_doc_level);
2939 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_level_id',p_doc_level_id);
2940 END IF;
2941
2942 l_progress := '010';
2943
2944 -- Standard Start of API savepoint
2945 SAVEPOINT IS_UNRESERVABLE_SP;
2946
2947 l_progress := '020';
2948
2949 -- Standard call to check for call compatibility
2950
2951 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
2952 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2953 END IF;
2954
2955 l_progress := '030';
2956
2957 -- Initialize message list if p_init_msg_list is set to TRUE.
2958
2959 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2960 FND_MSG_PUB.initialize;
2961 END IF;
2962
2963 l_progress := '040';
2964
2965 -- Initialize API return status to success
2966
2967 x_return_status := FND_API.G_RET_STS_SUCCESS;
2968
2969 l_progress := '050';
2970
2971 IF g_debug_stmt THEN
2972 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Calling Private Procedure');
2973 END IF;
2974
2975 l_progress := '060';
2976
2977 PO_DOCUMENT_FUNDS_PVT.is_unreservable(
2978 x_return_status => x_return_status
2979 , p_doc_type => p_doc_type
2980 , p_doc_subtype => p_doc_subtype
2981 , p_doc_level => p_doc_level
2982 , p_doc_level_id => p_doc_level_id
2983 , x_unreservable_flag => x_unreservable_flag
2984 );
2985
2986 l_progress := '100';
2987
2988 IF ( x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
2989 OR x_return_status IS NULL
2990 ) THEN
2991 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2992 END IF;
2993
2994 l_progress := '110';
2995
2996 IF g_debug_stmt THEN
2997 PO_DEBUG.debug_stmt(l_log_head,l_progress,
2998 'Returned from Private Procedure successfully');
2999 END IF;
3000
3001 l_progress := '200';
3002
3003 -- Standard check of p_commit.
3004 IF FND_API.To_Boolean(p_commit) THEN
3005 COMMIT WORK;
3006 END IF;
3007
3008 l_progress := '900';
3009
3010 IF g_debug_stmt THEN
3011 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
3012 PO_DEBUG.debug_var(l_log_head,l_progress,'x_unreservable_flag',x_unreservable_flag);
3013 PO_DEBUG.debug_end(l_log_head);
3014 END IF;
3015
3016 EXCEPTION
3017 WHEN OTHERS THEN
3018 BEGIN
3019 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS Start');
3020 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3021 IF g_debug_stmt THEN
3022 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
3023 PO_DEBUG.debug_var(l_log_head,l_progress,'x_unreservable_flag',x_unreservable_flag);
3024 END IF;
3025 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3026 FND_MSG_PUB.add_exc_msg(G_PKG_NAME,l_api_name,SQLERRM);
3027 END IF;
3028 ROLLBACK TO IS_UNRESERVABLE_SP;
3029 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS End');
3030 EXCEPTION
3031 WHEN OTHERS THEN
3032 NULL;
3033 END;
3034
3035 END is_unreservable;
3036
3037
3038
3039 -------------------------------------------------------------------------------
3040 --Start of Comments
3041 --Name: populate_encumbrance_gt
3042 --Pre-reqs:
3043 -- None.
3044 --Modifies:
3045 -- PO_ENCUMBRANCE_GT
3046 --Locks:
3047 -- PO_HEADERS_ALL
3048 -- PO_RELEASES_ALL
3049 -- PO_DISTRIBUTIONS_ALL
3050 -- PO_REQUISITION_HEADERS_ALL
3051 -- PO_REQ_DISTRIBUTIONS_ALL
3052 --Function:
3053 -- Flattens the PO transaction tables to retrieve all of the
3054 -- data needed by the encumbrance code and put it in the
3055 -- encumbrance table. Also locks the document headers and
3056 -- distributions if requested to do so.
3057 --Parameters:
3058 --IN:
3059 --p_api_version
3060 -- Apps API Std - To control correct version in use
3061 --p_init_msg_list
3062 -- Apps API Std - Initialize the message list?
3063 --p_validation_level
3064 -- Apps API Std - Level of validations to be done
3065 --p_doc_type
3066 -- Document type. Use the g_doc_type_<> variables, where <> is:
3067 -- REQUISITION
3068 -- PA
3069 -- PO
3070 -- RELEASE
3071 --p_doc_level
3072 -- The type of ids that are being passed. Use g_doc_level_<>
3073 -- HEADER
3074 -- LINE
3075 -- SHIPMENT
3076 -- DISTRIBUTION
3077 --p_doc_level_id_tbl
3078 -- Ids of the doc level type with which to populate the encumbrance
3079 -- table. Each distribution with a link to the specified id
3080 -- will be used in the population.
3081 --
3082 --p_make_old_copies_flag
3083 --p_make_new_copies_flag
3084 --
3085 -- Specify what to populate in the adjustment_status column of
3086 -- the encumbrance table. Use g_parameter_YES/g_parameter_NO.
3087 -- Examples:
3088 -- OLD NEW Result
3089 -- --- --- ------
3090 -- YES YES Two copies will be made for each distribution,
3091 -- one with a value of g_adjustment_status_OLD
3092 -- and one with a value of g_adjustment_status_NEW
3093 -- in the adjustment_status column.
3094 -- YES NO One copy per dist. with a value of g_adjustment_status_OLD.
3095 -- NO YES One copy per dist. with a value of g_adjustment_status_NEW.
3096 -- NO NO One copy per dist. with a value of NULL.
3097 --
3098 --p_check_only_flag
3099 -- Indicates whether or not to lock the document headers and distributions.
3100 -- g_parameter_NO lock them
3101 -- g_parameter_YES don't lock them
3102 --
3103 --OUT:
3104 --x_return_status
3105 -- Apps standard parameter
3106 -- VARCHAR2(1)
3107 --Testing:
3108 --
3109 --End of Comments
3110 -------------------------------------------------------------------------------
3111 PROCEDURE populate_encumbrance_gt(
3112 p_api_version IN NUMBER
3113 , p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE
3114 , p_validation_level IN NUMBER default FND_API.G_VALID_LEVEL_FULL
3115 , x_return_status OUT NOCOPY VARCHAR2
3116 , p_doc_type IN VARCHAR2
3117 , p_doc_level IN VARCHAR2
3118 , p_doc_level_id_tbl IN po_tbl_number
3119 , p_make_old_copies_flag IN VARCHAR2
3120 , p_make_new_copies_flag IN VARCHAR2
3121 , p_check_only_flag IN VARCHAR2
3122 )
3123 IS
3124
3125 l_api_name CONSTANT VARCHAR2(30) := 'POPULATE_ENCUMBRANCE_GT';
3126 l_api_version CONSTANT NUMBER := 1.0;
3127
3128 l_log_head CONSTANT VARCHAR2(100) := c_log_head || l_api_name;
3129 l_progress VARCHAR2(3) := '000';
3130
3131 l_adjustment_status_tbl po_tbl_varchar5;
3132
3133 BEGIN
3134
3135 IF g_debug_stmt THEN
3136 PO_DEBUG.debug_begin(l_log_head);
3137 PO_DEBUG.debug_var(l_log_head,l_progress,'p_api_version',p_api_version);
3138 PO_DEBUG.debug_var(l_log_head,l_progress,'p_init_msg_list',p_init_msg_list);
3139 PO_DEBUG.debug_var(l_log_head,l_progress,'p_validation_level',p_validation_level);
3140 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_type', p_doc_type);
3141 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_level', p_doc_level);
3142 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_level_id_tbl', p_doc_level_id_tbl);
3143 PO_DEBUG.debug_var(l_log_head,l_progress,'p_make_old_copies_flag',p_make_old_copies_flag);
3144 PO_DEBUG.debug_var(l_log_head,l_progress,'p_make_new_copies_flag',p_make_new_copies_flag);
3145 PO_DEBUG.debug_var(l_log_head,l_progress,'p_check_only_flag', p_check_only_flag);
3146 END IF;
3147
3148 l_progress := '010';
3149
3150 -- Standard Start of API savepoint
3151 SAVEPOINT POPULATE_ENCUMBRANCE_GT_GRP;
3152
3153 l_progress := '020';
3154
3155 -- Standard call to check for call compatibility
3156 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
3157 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3158 END IF;
3159
3160 l_progress := '030';
3161
3162 -- Initialize message list if p_init_msg_list is set to TRUE.
3163 IF FND_API.to_Boolean( p_init_msg_list ) THEN
3164 FND_MSG_PUB.initialize;
3165 END IF;
3166
3167 l_progress := '040';
3168
3169 -- Initialize API return status to success
3170 x_return_status := FND_API.G_RET_STS_SUCCESS;
3171
3172 l_progress := '050';
3173
3174 -- Set up the parameters for the call to the private API.
3175
3176 -- Determine the adjustment status labels,
3177 -- and validate those parameters at the same time.
3178
3179 IF (p_make_old_copies_flag = g_parameter_YES
3180 AND p_make_new_copies_flag = g_parameter_YES)
3181 THEN
3182 l_progress := '130';
3183 l_adjustment_status_tbl := po_tbl_varchar5(g_adjustment_status_OLD,g_adjustment_status_NEW);
3184 ELSIF (p_make_old_copies_flag = g_parameter_YES
3185 AND p_make_new_copies_flag = g_parameter_NO)
3186 THEN
3187 l_progress := '140';
3188 l_adjustment_status_tbl := po_tbl_varchar5(g_adjustment_status_OLD);
3189 ELSIF (p_make_old_copies_flag = g_parameter_NO
3190 AND p_make_new_copies_flag = g_parameter_YES)
3191 THEN
3192 l_progress := '150';
3193 l_adjustment_status_tbl := po_tbl_varchar5(g_adjustment_status_NEW);
3194 ELSIF (p_make_old_copies_flag = g_parameter_NO
3195 AND p_make_new_copies_flag = g_parameter_NO)
3196 THEN
3197 l_progress := '160';
3198 l_adjustment_status_tbl := po_tbl_varchar5(NULL);
3199 ELSE
3200 l_progress := '170';
3201 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3202 END IF;
3203
3204
3205 l_progress := '200';
3206
3207 -- Call the private API.
3208
3209 PO_DOCUMENT_FUNDS_PVT.populate_encumbrance_gt(
3210 x_return_status => x_return_status
3211 , p_doc_type => p_doc_type
3212 , p_doc_level => p_doc_level
3213 , p_doc_level_id_tbl => p_doc_level_id_tbl
3214 , p_adjustment_status_tbl => l_adjustment_status_tbl
3215 , p_check_only_flag => p_check_only_flag
3216 );
3217
3218 l_progress := '900';
3219
3220 IF g_debug_stmt THEN
3221 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
3222 PO_DEBUG.debug_end(l_log_head);
3223 END IF;
3224
3225 EXCEPTION
3226 WHEN OTHERS THEN
3227 BEGIN
3228 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS Start');
3229 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3230 IF g_debug_stmt THEN
3231 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
3232 END IF;
3233 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3234 FND_MSG_PUB.add_exc_msg(G_PKG_NAME,l_api_name,SQLERRM);
3235 END IF;
3236 ROLLBACK TO POPULATE_ENCUMBRANCE_GT_GRP;
3237 PO_DEBUG.debug_unexp(l_log_head,l_progress,'OTHERS End');
3238 EXCEPTION
3239 WHEN OTHERS THEN
3240 NULL;
3241 END;
3242
3243 END populate_encumbrance_gt;
3244
3245 --<bug#5010001 START>
3246 -------------------------------------------------------------------------------
3247 --Start of Comments
3248 --Name: get_online_report_id
3249 --Pre-reqs:
3250 -- None.
3251 --Modifies:
3252 --
3253 --Locks:
3254 --
3255 --Function:
3256 -- Returns the value of online_report_id generated in the current tranaction
3257 -- This is to be used only inside the function PO_DOCUMENT_FUNDS_PVT.populate_and_create_bc_report
3258 --Parameters:
3259 --IN:
3260 --
3261 --OUT:
3262 --Testing:
3263 --
3264 --End of Comments
3265 -------------------------------------------------------------------------------
3266 FUNCTION get_online_report_id RETURN NUMBER IS
3267 l_module_name CONSTANT VARCHAR2(100) := 'get_online_report_id';
3268 d_module_base CONSTANT VARCHAR2(100) := PO_LOG.get_subprogram_base(D_PACKAGE_BASE, l_module_name);
3269 l_api_version CONSTANT NUMBER := 1.0;
3270 d_progress NUMBER;
3271 BEGIN
3272 d_progress:=0;
3273 IF (PO_LOG.d_proc) THEN
3274 PO_LOG.proc_begin(d_module_base);
3275 END IF;
3276
3277 IF (PO_LOG.d_proc) THEN
3278 PO_LOG.proc_end(d_module_base);
3279 PO_LOG.proc_end(d_module_base, 'PO_DOCUMENT_FUNDS_PVT.g_ONLINE_REPORT_ID', PO_DOCUMENT_FUNDS_PVT.g_ONLINE_REPORT_ID);
3280 END IF;
3281 d_progress:=1;
3282
3283 return PO_DOCUMENT_FUNDS_PVT.g_ONLINE_REPORT_ID;
3284
3285 EXCEPTION
3286 WHEN OTHERS THEN
3287 IF (PO_LOG.d_exc) THEN
3288 PO_LOG.exc(d_module_base, d_progress, 'Unhandled exception in '
3289 ||l_module_name||' '||SQLCODE||' '||SQLERRM);
3290 PO_LOG.proc_end(d_module_base, 'PO_DOCUMENT_FUNDS_PVT.g_ONLINE_REPORT_ID', PO_DOCUMENT_FUNDS_PVT.g_ONLINE_REPORT_ID);
3291 END IF;
3292 RAISE FND_API.g_exc_unexpected_error;
3293 END;
3294
3295 --<bug#5010001 END>
3296 END PO_DOCUMENT_FUNDS_GRP;