1 PACKAGE BODY PO_ENCUMBRANCE_POSTPROCESSING AS
2 -- $Header: POXENC2B.pls 120.59.12020000.7 2013/04/10 14:26:21 gjyothi ship $
3
4 G_PKG_NAME CONSTANT varchar2(30) := 'PO_ENCUMBRANCE_POSTPROCESSING';
5
6 g_log_head CONSTANT VARCHAR2(50) := 'po.plsql.'|| G_PKG_NAME || '.' ;
7
8 -- Read the profile option that enables/disables the debug log
9 g_debug_stmt CONSTANT BOOLEAN := PO_DEBUG.is_debug_stmt_on;
10 g_debug_unexp CONSTANT BOOLEAN := PO_DEBUG.is_debug_unexp_on;
11
12 -- Private package exceptions
13 g_GL_VALIDATE_API_EXC EXCEPTION;
14 g_GL_FUNDS_API_EXC EXCEPTION;
15
16 --------------------------------------------------------------------------------
17 -- Private package constants
18 --------------------------------------------------------------------------------
19
20 -- encumbrance actions
21 g_action_RESERVE CONSTANT VARCHAR2(30)
22 := PO_DOCUMENT_FUNDS_PVT.g_action_RESERVE;
23
24 g_action_UNRESERVE CONSTANT VARCHAR2(30)
25 := PO_DOCUMENT_FUNDS_PVT.g_action_UNRESERVE;
26
27 g_action_ADJUST CONSTANT VARCHAR2(30)
28 := PO_DOCUMENT_FUNDS_PVT.g_action_ADJUST;
29
30 g_action_REQ_SPLIT CONSTANT
31 VARCHAR2(30)
32 := PO_DOCUMENT_FUNDS_PVT.g_action_REQ_SPLIT
33 ;
34 g_action_CANCEL CONSTANT VARCHAR2(30)
35 := PO_DOCUMENT_FUNDS_PVT.g_action_CANCEL;
36
37 g_action_FINAL_CLOSE CONSTANT VARCHAR2(30)
38 := PO_DOCUMENT_FUNDS_PVT.g_action_FINAL_CLOSE;
39
40 g_action_UNDO_FINAL_CLOSE CONSTANT VARCHAR2(30)
41 := PO_DOCUMENT_FUNDS_PVT.g_action_UNDO_FINAL_CLOSE;
42
43 g_action_REJECT CONSTANT VARCHAR2(30)
44 := PO_DOCUMENT_FUNDS_PVT.g_action_REJECT;
45
46 g_action_RETURN CONSTANT VARCHAR2(30)
47 := PO_DOCUMENT_FUNDS_PVT.g_action_RETURN;
48
49 g_action_CBC_RESERVE CONSTANT VARCHAR2(30)
50 := PO_DOCUMENT_FUNDS_PVT.g_action_CBC_RESERVE;
51
52 g_action_CBC_UNRESERVE CONSTANT VARCHAR2(30)
53 := PO_DOCUMENT_FUNDS_PVT.g_action_CBC_UNRESERVE;
54
55 g_action_INVOICE_CANCEL CONSTANT VARCHAR2(30)
56 := PO_DOCUMENT_FUNDS_PVT.g_action_INVOICE_CANCEL;
57
58 g_action_CR_MEMO_CANCEL CONSTANT VARCHAR2(30)
59 := PO_DOCUMENT_FUNDS_PVT.g_action_CR_MEMO_CANCEL;
60
61
62 -- doc types
63
64 g_doc_type_REQUISITION CONSTANT
65 PO_DOCUMENT_TYPES.document_type_code%TYPE
66 := PO_DOCUMENT_FUNDS_PVT.g_doc_type_REQUISITION;
67
68 g_doc_type_PO CONSTANT
69 PO_DOCUMENT_TYPES.document_type_code%TYPE
70 := PO_DOCUMENT_FUNDS_PVT.g_doc_type_PO;
71
72 g_doc_type_PA CONSTANT
73 PO_DOCUMENT_TYPES.document_type_code%TYPE
74 := PO_DOCUMENT_FUNDS_PVT.g_doc_type_PA;
75
76 g_doc_type_RELEASE CONSTANT
77 PO_DOCUMENT_TYPES.document_type_code%TYPE
78 := PO_DOCUMENT_FUNDS_PVT.g_doc_type_RELEASE;
79
80 g_doc_type_MIXED_PO_RELEASE CONSTANT
81 PO_DOCUMENT_TYPES.document_type_code%TYPE
82 := PO_DOCUMENT_FUNDS_PVT.g_doc_type_MIXED_PO_RELEASE;
83
84 -- doc subtypes
85
86 g_doc_subtype_STANDARD CONSTANT
87 PO_HEADERS_ALL.type_lookup_code%TYPE
88 := PO_DOCUMENT_FUNDS_PVT.g_doc_subtype_STANDARD;
89
90 g_doc_subtype_PLANNED CONSTANT
91 PO_HEADERS_ALL.type_lookup_code%TYPE
92 := PO_DOCUMENT_FUNDS_PVT.g_doc_subtype_PLANNED;
93
94 g_doc_subtype_BLANKET CONSTANT
95 PO_RELEASES_ALL.release_type%TYPE
96 := PO_DOCUMENT_FUNDS_PVT.g_doc_subtype_BLANKET;
97
98 g_doc_subtype_SCHEDULED CONSTANT
99 PO_RELEASES_ALL.release_type%TYPE
100 := PO_DOCUMENT_FUNDS_PVT.g_doc_subtype_SCHEDULED;
101
102 g_doc_subtype_MIXED_PO_RELEASE CONSTANT
103 PO_HEADERS_ALL.type_lookup_code%TYPE
104 := PO_DOCUMENT_FUNDS_PVT.g_doc_subtype_MIXED_PO_RELEASE;
105
106 -- doc levels
107
108 g_doc_level_HEADER CONSTANT VARCHAR2(25)
109 := PO_DOCUMENT_FUNDS_PVT.g_doc_level_HEADER;
110
111 g_doc_level_LINE CONSTANT VARCHAR2(25)
112 := PO_DOCUMENT_FUNDS_PVT.g_doc_level_LINE;
113
114 g_doc_level_SHIPMENT CONSTANT VARCHAR2(25)
115 := PO_DOCUMENT_FUNDS_PVT.g_doc_level_SHIPMENT;
116
117 g_doc_level_DISTRIBUTION CONSTANT VARCHAR2(25)
118 := PO_DOCUMENT_FUNDS_PVT.g_doc_level_DISTRIBUTION;
119
120 -- distribution types
121
122 g_dist_type_STANDARD CONSTANT
123 PO_DISTRIBUTIONS_ALL.distribution_type%TYPE
124 := PO_DOCUMENT_FUNDS_PVT.g_dist_type_STANDARD;
125
126 g_dist_type_PLANNED CONSTANT
127 PO_DISTRIBUTIONS_ALL.distribution_type%TYPE
128 := PO_DOCUMENT_FUNDS_PVT.g_dist_type_PLANNED;
129
130 g_dist_type_SCHEDULED CONSTANT
131 PO_DISTRIBUTIONS_ALL.distribution_type%TYPE
132 := PO_DOCUMENT_FUNDS_PVT.g_dist_type_SCHEDULED;
133
134 g_dist_type_BLANKET CONSTANT
135 PO_DISTRIBUTIONS_ALL.distribution_type%TYPE
136 := PO_DOCUMENT_FUNDS_PVT.g_dist_type_BLANKET;
137
138 g_dist_type_AGREEMENT CONSTANT
139 PO_DISTRIBUTIONS_ALL.distribution_type%TYPE
140 := PO_DOCUMENT_FUNDS_PVT.g_dist_type_AGREEMENT;
141
142 g_dist_type_REQUISITION CONSTANT
143 PO_DISTRIBUTIONS_ALL.distribution_type%TYPE
144 := PO_DOCUMENT_FUNDS_PVT.g_dist_type_REQUISITION;
145
146 g_dist_type_MIXED_PO_RELEASE CONSTANT
147 PO_DISTRIBUTIONS_ALL.distribution_type%TYPE
148 := PO_DOCUMENT_FUNDS_PVT.g_dist_type_MIXED_PO_RELEASE;
149
150 -- parameter values
151
152 g_parameter_YES CONSTANT VARCHAR2(1)
153 := PO_DOCUMENT_FUNDS_PVT.g_parameter_YES;
154
155 g_parameter_NO CONSTANT VARCHAR2(1)
156 := PO_DOCUMENT_FUNDS_PVT.g_parameter_NO;
157
158 g_parameter_USE_PROFILE CONSTANT VARCHAR2(1)
159 := PO_DOCUMENT_FUNDS_PVT.g_parameter_USE_PROFILE;
160
161 -- return status
162
163 g_return_SUCCESS CONSTANT VARCHAR2(10)
164 := PO_DOCUMENT_FUNDS_PVT.g_return_SUCCESS;
165
166 g_return_WARNING CONSTANT VARCHAR2(10)
167 := PO_DOCUMENT_FUNDS_PVT.g_return_WARNING;
168
169 g_return_PARTIAL CONSTANT VARCHAR2(10)
170 := PO_DOCUMENT_FUNDS_PVT.g_return_PARTIAL;
171
172 g_return_FAILURE CONSTANT VARCHAR2(10)
173 := PO_DOCUMENT_FUNDS_PVT.g_return_FAILURE;
174
175 g_return_FATAL CONSTANT VARCHAR2(10)
176 := PO_DOCUMENT_FUNDS_PVT.g_return_FATAL;
177
178 -- gl packet constants
179
180 g_actual_flag_Encumbrance VARCHAR2(1) := 'E';
181
182 g_je_source_name_Purchasing VARCHAR2(25) := 'Purchasing';
183
184
185 -- adjustment status
186 g_adjustment_status_OLD CONSTANT
187 PO_ENCUMBRANCE_GT.adjustment_status%TYPE
188 := PO_DOCUMENT_FUNDS_PVT.g_adjustment_status_OLD;
189
190 g_adjustment_status_NEW CONSTANT
191 PO_ENCUMBRANCE_GT.adjustment_status%TYPE
192 := PO_DOCUMENT_FUNDS_PVT.g_adjustment_status_NEW;
193
194
195 -- result classifications
196 g_result_SUCCESS CONSTANT PO_ENCUMBRANCE_GT.result_type%TYPE
197 := PO_DOCUMENT_FUNDS_PVT.g_result_SUCCESS;
198
199 g_result_WARNING CONSTANT PO_ENCUMBRANCE_GT.result_type%TYPE
200 := PO_DOCUMENT_FUNDS_PVT.g_result_WARNING;
201
202 g_result_ERROR CONSTANT PO_ENCUMBRANCE_GT.result_type%TYPE
203 := PO_DOCUMENT_FUNDS_PVT.g_result_ERROR;
204 --note: this classification currently maps to Warning, but is
205 --given a seperate label so as to easily identify this condition
206 g_result_NOT_PROCESSED CONSTANT PO_ENCUMBRANCE_GT.result_type%TYPE
207 := PO_DOCUMENT_FUNDS_PVT.g_result_WARNING;
208
209 --note: this classification currently maps to ERROR, but is
210 --given a seperate label so as to easily identify this condition,
211 --which occurs when there is a single high-level failure message
212 --instead of detailed results for each distribution
213 g_result_TRANSACTION CONSTANT PO_ENCUMBRANCE_GT.result_type%TYPE
214 := PO_DOCUMENT_FUNDS_PVT.g_result_ERROR;
215
216 -- current module
217 g_module_ENCUMBRANCE CONSTANT
218 PO_ONLINE_REPORT_TEXT.transaction_type%TYPE := 'ENCUMBRANCE';
219
220 --bug#5353223
221 g_CREDIT CONSTANT VARCHAR2(2) :='Cr';
222 g_DEBIT CONSTANT VARCHAR2(2) :='Dr';
223 --bug#5353223
224
225 --------------------------------------------------------------------------------
226 -- Private procedures
227 --------------------------------------------------------------------------------
228 --<bug#5523323 START>
229 --Renamed the procedure insert_packet_autonomous as insert_packet_create_event
230 --This is done because we do not have any autonomous txns anymore.
231 --Also removed the in parameters passing data as tables because they can now be
232 --populated directly as part of a sql.
233 --<bug#5523323 END>
234
235 --FUNCTION get_event_type_code(p_distribution_type IN VARCHAR2,
236 -- p_action IN VARCHAR2) RETURN VARCHAR2;
237
238 PROCEDURE insert_packet_create_event
239 (
240 p_status_code IN VARCHAR2,
241 p_user_id IN NUMBER,
242 p_set_of_books_id IN NUMBER,
243 p_currency_code IN VARCHAR2,
244 p_action IN VARCHAR2,--bug#5646605
245 --<SLA R12 End>
246 x_packet_id OUT NOCOPY NUMBER
247 );
248
249
250 PROCEDURE update_successful_rows(
251 p_doc_type IN VARCHAR2
252 , p_doc_subtype IN VARCHAR2
253 , p_action IN VARCHAR2
254 , p_gl_return_code IN VARCHAR2
255 );
256
257 PROCEDURE update_failed_rows(
258 p_doc_type IN VARCHAR2
259 , p_action IN VARCHAR2
260 );
261
262 -- Bug 3537764: Added p_action parameter.
263 PROCEDURE rollup_encumbrance_changes (p_action IN VARCHAR2);
264
265 PROCEDURE insert_report_autonomous(
266 p_reporting_level IN VARCHAR2
267 , p_message_text IN VARCHAR2
268 , p_user_id IN NUMBER
269 , p_sequence_num_tbl IN po_tbl_number
270 , p_line_num_tbl IN po_tbl_number
271 , p_shipment_num_tbl IN po_tbl_number
272 , p_distribution_num_tbl IN po_tbl_number
273 , p_distribution_id_tbl IN po_tbl_number
274 , p_result_code_tbl IN po_tbl_varchar5
275 , p_message_type_tbl IN po_tbl_varchar1
276 , p_text_line_tbl IN po_tbl_varchar2000
277 , p_show_in_psa_flag IN po_tbl_varchar1 --<bug#5010001>
278 , p_segment1_tbl IN po_tbl_varchar20 --<bug#5010001>
279 , p_distribution_type_tbl IN po_tbl_varchar25 --<bug#5010001>
280 , x_online_report_id OUT NOCOPY NUMBER
281 );
282
283 --<SLA R12 Start>
284 PROCEDURE delete_po_bc_distributions
285 (
286 p_packet_id IN NUMBER
287 ) ;
288
289 FUNCTION get_entity_type_code(p_distribution_type IN VARCHAR2,
290 p_action IN VARCHAR2) RETURN VARCHAR2;
291
292 PROCEDURE get_doc_detail_from_dist_type(p_distribution_type IN VARCHAR2,
293 x_doc_type OUT NOCOPY VARCHAR2 ,
294 x_doc_subtype OUT NOCOPY VARCHAR2);
295 PROCEDURE get_event_and_entity_codes(
296 p_doc_type IN VARCHAR2,
297 p_doc_subtype IN VARCHAR2,
298 p_action IN VARCHAR2,
299 x_entity_type_code OUT NOCOPY VARCHAR2,
300 x_event_type_code OUT NOCOPY VARCHAR2
301 );
302
303 --<SLA R12 End>
304
305 -------------------------------------------------------------------------------
306 --Start of Comments
307 --Name: insert_packet
308 --Pre-reqs:
309 -- PO_ENCUMBRANCE_GT has been updated with the data that should
310 -- be inserted into PO_BC_DISTRIBUTIONS.
311 --Modifies:
312 -- GL_BC_PACKETS
313 --Locks:
314 -- None.
315 --Function:
316 -- This procedure inserts all of the entries in
317 -- PO_ENCUMBRANCE_GT into PO_BC_DISTRIBUTIONS. It does this through
318 -- an autonomous transaction helper procedure, as GL needs the data
319 -- committed, but we don't want to commit anything else.
320 --Parameters:
321 --IN:
322 --p_status_code
323 -- Specifies whether this is a Funds Check (C) or a real (pending) action (P).
324 --p_user_id
325 -- The user_id that is taking this action.
326 --p_set_of_books_id
327 -- The set of books for the current org.
328 --p_currency_code
329 -- The currency code for the current org.
330 --OUT:
331 --x_packet_id
332 -- The PO_BC_DISTRIBUTIONS.packet_id that was used for this packet.
333 -- If a packet was not created, this will be NULL.
334 --Testing:
335 --
336 --End of Comments
337 -------------------------------------------------------------------------------
338 PROCEDURE insert_packet
339 (
340 p_status_code IN VARCHAR2,
341 p_user_id IN NUMBER,
342 p_set_of_books_id IN NUMBER,
343 p_currency_code IN VARCHAR2,
344 p_action IN VARCHAR2,--bug#5646605
345 x_packet_id OUT NOCOPY NUMBER
346 )
347 IS
348 l_proc_name CONSTANT VARCHAR2(30) := 'INSERT_PACKET';
349 l_log_head CONSTANT VARCHAR2(100) := g_log_head || l_proc_name;
350 l_progress VARCHAR2(3) := '000';
351 BEGIN
352 IF g_debug_stmt THEN
353 PO_DEBUG.debug_begin(l_log_head);
354 PO_DEBUG.debug_var(l_log_head,l_progress,'p_status_code',p_status_code);
355 PO_DEBUG.debug_var(l_log_head,l_progress,'p_user_id',p_user_id);
356 PO_DEBUG.debug_var(l_log_head,l_progress,'p_set_of_books_id',p_set_of_books_id);
357 PO_DEBUG.debug_var(l_log_head,l_progress,'p_currency_code',p_currency_code);
358 END IF;
359 l_progress := '100';
360 --bug#5523323 We have removed the autonomous transaction in the procedure
361 --insert_packet_autonomous. Hence renamed the procedure as
362 --insert_packet_create_event. Also removed the select statment that
363 --collects data in pl/sql tables. This is because we can do direct
364 --insert inside insert_packet_create_event.
365 INSERT_PACKET_CREATE_EVENT(
366 p_status_code => p_status_code,
367 p_user_id => p_user_id,
368 p_set_of_books_id=> p_set_of_books_id,
369 p_currency_code => p_currency_code,
370 p_action => p_action,--bug#5646605
371 x_packet_id => x_packet_id
372 );
373
374 l_progress := '200';
375 -- Populate eventids in psa_bc_xla_events_gt for these events
376 -- to be considered by Budgetary Control API
377 IF g_debug_stmt THEN
378 PO_DEBUG.debug_var(l_log_head,l_progress,'x_packet_id',x_packet_id);
379 END IF;
380
381 IF(x_packet_id IS NOT NULL) THEN
382
383 -- BUG 6442530
384 -- For any Mutliple line change PO Change Request from IProocurement.
385 -- We are unreserving the document in a LOOP for every Line change done.
386 -- We are creating one event for every line. But all the event get processed
387 -- in one transaction boundary, PSA is getting the event id of previous lines
388 -- also which it has allready processed in this tranaction.
389 -- So clearing the event GT table before we insert any new event.
390
391 DELETE FROM psa_bc_xla_events_gt
392 WHERE event_id IN ( SELECT distinct ae_event_id
393 FROM po_bc_distributions
394 WHERE packet_id <> x_packet_id
395 );
396
397
398 INSERT into psa_bc_xla_events_gt
399 (
400 event_id,
401 result_code -- Bug #4637958
402 )
403 SELECT distinct ae_event_id,'XLA_ERROR'
404 FROM po_bc_distributions
405 WHERE packet_id = x_packet_id;
406 END IF;
407
408 l_progress := '300';
409 IF g_debug_stmt THEN
410 PO_DEBUG.debug_var(l_log_head,l_progress,'x_packet_id',x_packet_id);
411 PO_DEBUG.debug_end(l_log_head);
412 END IF;
413
414 EXCEPTION
415 WHEN OTHERS THEN
416 IF g_debug_unexp THEN
417 PO_DEBUG.debug_exc(l_log_head,l_progress);
418 END IF;
419 RAISE;
420 END insert_packet;
421
422
423 -------------------------------------------------------------------------------
424 --Start of Comments
425 --Name: INSERT_PACKET_CREATE_EVENT
426 --Pre-reqs:
427 -- None.
428 --Modifies:
429 -- PO_BC_DISTRIBUTIONS
430 --Locks:
431 -- None.
432 --Function:
433 -- This procedure takes its input parameters and inserts and
434 -- transfers data from po_encumbrance_gt to po_bc_distributions.
435 --Parameters:
436 --IN:
437 -- p_status_code
438 -- Specifies whether this is a Funds Check (C) or a real (pending) action (P).
439 -- p_user_id
440 -- The user_id that is taking this action.
441 -- p_set_of_books_id
442 -- The set of books for the current org.
443 -- p_currency_code
444 -- The currency code for the current org.
445 --OUT:
446 --x_packet_id
447 -- The PO_BC_DISTRIBUTIONS.packet_id that was used for this packet.
448 --Testing:
449 --
450 --End of Comments
451 -------------------------------------------------------------------------------
452 --bug#5523323 We have removed the autonomous transaction in the procedure
453 --insert_packet_autonomous. Hence renamed the procedure as
454 --insert_packet_create_event. Also removed the collection parameters
455 --This is because we can do direct insert inside insert_packet_create_event.
456
457 -- <Bug 13503748: Edit without unreserve ER.>
458 -- Revamping insert_packet_create_event code as part of
459 -- ER edit without unreserve.
460
461 PROCEDURE insert_packet_create_event
462 (
463 p_status_code IN VARCHAR2,
464 p_user_id IN NUMBER,
465 p_set_of_books_id IN NUMBER,
466 p_currency_code IN VARCHAR2,
467 p_action IN VARCHAR2,--bug#5646605
468 --<SLA R12 End>
469 x_packet_id OUT NOCOPY NUMBER
470 )
471 IS
472
473
474 l_proc_name CONSTANT VARCHAR2(30) := 'INSERT_PACKET_CREATE_EVENT';
475 l_log_head CONSTANT VARCHAR2(100) := g_log_head || l_proc_name;
476 l_progress VARCHAR2(3) := '000';
477
478 l_appl_id NUMBER; --<SLA R12>
479 l_login_id NUMBER; --<SLA R12>
480 l_event_type_code VARCHAR2(50) ;
481
482 l_event_source_info xla_events_pub_pkg.t_event_source_info;
483 l_security_context xla_events_pub_pkg.t_security;
484 l_reference_info xla_events_pub_pkg.t_event_reference_info;
485 l_event_date DATE;
486 l_event_status_code VARCHAR2(30);
487 l_event_number NUMBER;
488 l_valuation_method VARCHAR2(30);
489 l_legal_entity_id NUMBER;
490 l_ledger_id NUMBER;
491 l_transaction_number NUMBER;
492 l_entity_id NUMBER;
493 l_event_id NUMBER;
494 l_current_org_id HR_ALL_ORGANIZATION_UNITS_TL.ORGANIZATION_ID%TYPE;
495 l_seq_num_tbl po_tbl_number ;
496 l_num_of_rows_deleted NUMBER;
497 l_num_of_rows_inserted NUMBER;
498
499 l_id_tbl po_tbl_number;
500
501 TYPE Event_tab_type IS TABLE OF XLA_EVENTS_INT_GT%ROWTYPE INDEX BY BINARY_INTEGER;
502 l_events_Tab Event_tab_type;
503 l_event_count NUMBER;
504
505 BEGIN
506 IF g_debug_stmt THEN
507 PO_DEBUG.debug_begin(l_log_head);
508 PO_DEBUG.debug_var(l_log_head,l_progress,'p_status_code',p_status_code);
509 PO_DEBUG.debug_var(l_log_head,l_progress,'p_user_id',p_user_id);
510 PO_DEBUG.debug_var(l_log_head,l_progress,'p_set_of_books_id',p_set_of_books_id);
511 PO_DEBUG.debug_var(l_log_head,l_progress,'p_currency_code',p_currency_code);
512 PO_DEBUG.debug_var(l_log_head,l_progress,'p_action',p_action);
513 END IF;
514
515 l_progress := '010';
516
517 -- Get the next packet_id from the sequence. This is required as
518 -- we insert all the records with same packet ID.
519
520 SELECT GL_BC_PACKETS_S.nextval
521 INTO x_packet_id
522 FROM DUAL;
523
524 l_progress := '020';
525
526 IF g_debug_stmt THEN
527 PO_DEBUG.debug_var(l_log_head,l_progress,'x_packet_id',x_packet_id);
528 END IF;
529
530 --<SLA R12 START>
531 l_appl_id := 201 ; -- Bug 5013999
532 l_login_id := FND_GLOBAL.login_id ;
533
534
535
536 -- Insert the data into PO_BC_DISTRIBUTIONS from PO_ENCUMBRANCE_GT,
537 --bug#5523323 Modified the insert statement to fetch data directly
538 --from the po_encumbrance_gt table rather than collections. This
539 --is possible because the autonomous transaction no longer exists.
540 INSERT INTO PO_BC_DISTRIBUTIONS
541 ( BC_DISTRIBUTION_ID,
542 PACKET_ID,
543 STATUS_CODE,
544 CREATION_DATE,
545 CREATED_BY,
546 LAST_UPDATE_LOGIN,
547 LAST_UPDATE_DATE,
548 LAST_UPDATED_BY,
549 LEDGER_ID,
550 CURRENCY_CODE,
551 JE_SOURCE_NAME,
552 JE_CATEGORY_NAME,
553 ENTERED_AMT,
554 ACCOUNTED_AMT,
555 GL_DATE,
556 CODE_COMBINATION_ID,
557 DISTRIBUTION_TYPE,
558 HEADER_ID,
559 DISTRIBUTION_ID,
560 SEQUENCE_NUMBER,
561 SEGMENT1,
562 REFERENCE_NUMBER,
563 APPLIED_TO_APPL_ID,
564 APPLIED_TO_DIST_LINK_TYPE,
565 PA_PROJECT_ID,
566 PA_AWARD_ID,
567 PA_TASK_ID,
568 PA_EXP_ORG_ID,
569 PA_EXP_TYPE,
570 PA_EXP_ITEM_DATE,
571 --EVENT_TYPE_CODE,
572 MAIN_OR_BACKING_CODE,
573 JE_LINE_DESCRIPTION,
574 PO_RELEASE_ID,
575 LINE_ID,
576 LINE_LOCATION_ID,
577 ENCUMBRANCE_TYPE_ID,
578 APPLIED_TO_DIST_ID_1,
579 APPLIED_TO_ENTITY_CODE,
580 APPLIED_TO_HEADER_ID_1,
581 REFERENCE1,
582 REFERENCE2,
583 REFERENCE3,
584 REFERENCE4,
585 REFERENCE5,
586 REFERENCE6,
587 REFERENCE7,
588 REFERENCE8,
589 REFERENCE9,
590 REFERENCE10,
591 REFERENCE11,
592 REFERENCE12,
593 REFERENCE13,
594 REFERENCE14,
595 REFERENCE15,
596 ADJUSTMENT_STATUS,
597 ORIGIN_SEQUENCE_NUM,
598 CLM_DOC_FLAG /* <<CLM Partial Funding Code Changes>> */
599 )
600 SELECT
601 PO_BC_DISTRIBUTIONS_S.nextval,
602 x_packet_id,
603 p_status_code,
604 SYSDATE,
605 p_user_id,
606 l_login_id,
607 SYSDATE,
608 p_user_id,
609 p_set_of_books_id,
610 nvl(DIST.CURRENCY_CODE,p_currency_code),
611 g_je_source_name_Purchasing,
612 DIST.JE_CATEGORY_NAME,
613 DIST.ENTERED_AMOUNT,
614 DIST.ACCOUNTED_AMOUNT,
615 DIST.gl_period_date, --<bug#5098665>
616 DIST.CODE_COMBINATION_ID,
617 DIST.DISTRIBUTION_TYPE,
618 DIST.HEADER_ID,
619 DIST.DISTRIBUTION_ID,
620 DIST.SEQUENCE_NUM,
621 DIST.SEGMENT1,
622 --DIST.SEGMENT1,
623 ( CASE
624 WHEN reference1 = 'REQ'
625 AND ( EXISTS (SELECT 1
626 FROM po_requisition_headers_all
627 WHERE To_char(requisition_header_id) =
628 dist.reference2
629 AND par_flag = 'Y') ) THEN (SELECT
630 modification_number
631 FROM
632 po_drafts
633 WHERE
634 draft_id = To_number(dist.reference4))
635 WHEN reference1 = 'PO'
636 AND EXISTS (SELECT 1
637 FROM po_headers_all pha,
638 po_doc_style_headers pdsh
639 WHERE pdsh.style_id = pha.style_id
640 AND pdsh.clm_flag = 'Y'
641 AND segment1 = dist.reference4) THEN
642 (SELECT clm_document_number
643 FROM
644 po_headers_all
645 WHERE
646 segment1 = dist.reference4)
647 ELSE dist.reference4
648 END ) reference_number,
649 l_appl_id,
650 DECODE(DIST.DISTRIBUTION_TYPE,'REQUISITION','PO_REQ_DISTRIBUTIONS_ALL','PO_DISTRIBUTIONS_ALL') DIST_LINK_TYPE,
651 DIST.PROJECT_ID,
652 DIST.AWARD_NUM,
653 DIST.TASK_ID,
654 DIST.EXPENDITURE_ORGANIZATION_ID,
655 DIST.EXPENDITURE_TYPE,
656 DIST.EXPENDITURE_ITEM_DATE,
657 --l_event_type_code,
658 DECODE(DIST.ORIGIN_SEQUENCE_NUM, NULL,'M', 'B_'||DIST.REFERENCE1) MAIN_OR_BACKING_CODE,
659 DIST.JE_LINE_DESCRIPTION,
660 DIST.PO_RELEASE_ID,
661 DIST.LINE_ID,
662 DIST.LINE_LOCATION_ID,
663 DIST.ENCUMBRANCE_TYPE_ID,
664 DIST.DISTRIBUTION_ID,
665 DECODE(DIST.DISTRIBUTION_TYPE,'REQUISITION','REQUISITION','SCHEDULED', 'RELEASE','BLANKET', 'RELEASE','PURCHASE_ORDER') APPLIED_TO_ENTITY_CODE, -- Bug 4760589
666 DECODE(DIST.DISTRIBUTION_TYPE,'SCHEDULED',DIST.PO_RELEASE_ID,'BLANKET',DIST.PO_RELEASE_ID,DIST.HEADER_ID), ----APPLIED_TO_HEADER_ID_1
667 DIST.REFERENCE1,
668 DIST.REFERENCE2,
669 DIST.REFERENCE3,
670 DIST.REFERENCE4,
671 DIST.REFERENCE5,
672 DIST.REFERENCE6,
673 DIST.REFERENCE7,
674 DIST.REFERENCE8,
675 DIST.REFERENCE9,
676 DIST.REFERENCE10,
677 DIST.REFERENCE11,
678 DIST.REFERENCE12,
679 DIST.REFERENCE13,
680 DIST.REFERENCE14,
681 DIST.REFERENCE15,
682 DIST.ADJUSTMENT_STATUS,
683 DIST.ORIGIN_SEQUENCE_NUM,
684 DIST.CLM_DOC_FLAG /* <<CLM Partial Funding Code Changes>> */
685 FROM PO_ENCUMBRANCE_GT DIST
686 WHERE SEND_TO_GL_FLAG = 'Y';
687 -- Bug 12866050 : Reverting the fix 10329412
688 --AND Nvl(final_amt,0) <> 0 ; -- Bug 10329412
689 /* Bug 10329412: Stopping event creation for 0$ amount */
690
691 l_progress := '100';
692 l_num_of_rows_inserted :=sql%rowcount;
693 IF g_debug_stmt THEN
694 PO_DEBUG.debug_var(l_log_head,l_progress,'l_num_of_rows_inserted',l_num_of_rows_inserted);
695 END IF;
696
697 IF l_num_of_rows_inserted = 0 THEN
698 x_packet_id :=NULL;
699 IF g_debug_stmt THEN
700 PO_DEBUG.debug_stmt(l_log_head,l_progress,'GL packet not created.');
701 END IF;
702 END IF;
703
704 IF(x_packet_id is not null)THEN
705
706 IF g_debug_stmt THEN
707 PO_DEBUG.debug_var(l_log_head,l_progress,'x_packet_id',x_packet_id);
708
709 END IF;
710
711 l_progress := '110';
712
713 -- Updating event_type_code for main record.
714 UPDATE po_bc_distributions pbd
715 SET event_type_code = (select get_event_type_code(pbd.distribution_type,p_action) from dual)
716 WHERE packet_id = x_packet_id
717 AND main_or_backing_code = 'M'
718 RETURNING sequence_number
719 BULK COLLECT
720 INTO
721 l_id_tbl;
722
723 -- Updating event_type_code for backing record.
724
725 FORALL i IN 1..l_id_tbl.count
726 UPDATE po_bc_distributions pbd
727 SET event_type_code = (SELECT event_type_code
728 FROM po_bc_distributions pbd1
729 WHERE pbd1.packet_id = x_packet_id
730 AND pbd1.sequence_number=l_id_tbl(i))
731 WHERE packet_id = x_packet_id
732 AND main_or_backing_code <> 'M'
733 and pbd.origin_sequence_num = l_id_tbl(i);
734
735 l_id_tbl.delete;
736
737 IF g_debug_stmt THEN
738 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Event type code updation is successful');
739 END IF;
740
741 --<Bug7138036 - STARTS>
742 -- Logic added to handle a specific case : [ CANCEL ACTION WITH REQ RECREATED OPTION ]
743 -- Recreate demand option come only in cancel action for a SPO/PPO/Blanket Release and the backing
744 -- document is requisition. In that case the requisition is not considered as backing document.
745 -- We pass the requisition as Main Document with REQ_RESERVE as the action. to reserve the requisition.
746 -- Key to identify recreated req distribution is source_req_distribution_id field.
747
748
749 l_progress := '120';
750
751 IF p_action = 'CANCEL' THEN
752
753 SELECT prd.distribution_id BULK COLLECT INTO l_id_tbl
754 FROM po_req_distributions_all prd,
755 po_bc_distributions pbd1, -- for Backing
756 po_bc_distributions pbd2 -- For Main
757 WHERE pbd1.packet_id = x_packet_id
758 AND prd.distribution_id = pbd1.distribution_id
759 AND prd.source_req_distribution_id IS NOT NULL
760 AND pbd1.main_or_backing_code = 'B_REQ'
761 AND pbd1.origin_sequence_num = pbd2.sequence_number
762 AND pbd2.event_type_code IN ('PO_PA_CANCELLED','RELEASE_CANCELLED');
763
764
765 FORALL i IN 1..l_id_tbl.COUNT
766 UPDATE po_bc_distributions pbd
767 SET main_or_backing_code = 'M' ,event_type_code = 'REQ_RESERVED' , origin_sequence_num = NULL ,
768 entered_amt = entered_amt * -1 ,accounted_amt = accounted_amt * -1
769 WHERE pbd.packet_id = x_packet_id
770 AND pbd.main_or_backing_code = 'B_REQ'
771 AND pbd.distribution_id = l_id_tbl(i) ;
772
773 IF g_debug_stmt THEN
774 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Event type code updation in reqcreate demand is successful');
775 END IF;
776
777 END IF;
778
779 l_progress := '130';
780
781 --<Bug7138036 - ENDS>
782
783 --<bug#5201733 START>
784 --We need to set the value of APPLIED_TO_DIST_ID_2 this would be passed
785 --onto the allocation attributes so that a 1-1 mapping can be created
786 --between po_bc_distributions and xla_distribution_links. A 1-1 mapping
787 --already exists between xla_distribution_links and gl_bc_packets. This
788 --is the route to be followed to establish a 1-1 mapping between
789 --po_bc_distributions and xla_distribution_links
790 --<bug#7437681 START>
791 -- Performance fix : Also added packet id filter in the inner query to
792 -- avoid a full table scan and to use index based on packet id.
793 UPDATE PO_BC_DISTRIBUTIONS PBD
794 SET PBD.line_number = PBD.bc_distribution_id,
795 PBD.APPLIED_TO_DIST_ID_2=
796 (
797 SELECT ORIG.distribution_id
798 FROM PO_BC_DISTRIBUTIONS ORIG
799 WHERE ORIG.sequence_number=PBD.origin_sequence_num
800 AND ORIG.packet_id = x_packet_id
801 )
802 WHERE PBD.packet_id = x_packet_id ;
803
804
805 --<bug#7437681 END>
806 --<bug#5201733 END>
807 IF g_debug_stmt THEN
808 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Bulk Insertion Successful');
809 END IF;
810 --<SLA R12 End>
811
812 -- Get the current org id
813 l_current_org_id := PO_MOAC_UTILS_PVT.Get_Current_Org_Id ;
814 l_progress := '140' ;
815
816 -- SQL What: Querying for legal entity and set of books
817 -- SQL Why : Need legal entity and set of books applicable for current org
818 -- SQL Join: None
819 SELECT set_of_books_id
820 INTO l_ledger_id
821 FROM hr_operating_units hou
822 WHERE hou.organization_id = l_current_org_id ;
823
824 l_progress := '150' ;
825
826 -- Bug 4654758 : get the legal entity id using the API
827 l_legal_entity_id := xle_utilities_grp.Get_DefaultLegalContext_OU(l_current_org_id);
828
829 IF g_debug_stmt THEN
830 PO_DEBUG.debug_var(l_log_head,l_progress,'l_legal_entity_id ',l_legal_entity_id );
831 END IF;
832
833 l_event_status_code := xla_events_pub_pkg.c_event_unprocessed;
834 IF g_debug_stmt THEN
835 PO_DEBUG.debug_var(l_log_head,l_progress,'l_event_status_code ',l_event_status_code );
836 END IF;
837
838 l_event_number := NULL;
839 l_valuation_method := NULL;
840 l_event_count:=0;
841
842
843 l_progress := '160' ;
844
845 -- Deleting all the prior unprocessed events.
846 -- All the prior draft/invalid events are deleted before new event is created
847 -- for this transaction
848
849 FOR rec_events IN ( SELECT DISTINCT xe.event_id,xe.entity_id,
850 xe.event_type_code,
851 xe.event_date,
852 xe.event_status_code,
853 xe.process_status_code,
854 pbd.applied_to_header_id_1,
855 pbd.applied_to_entity_code
856 FROM xla_events xe,
857 po_bc_distributions pbd
858 WHERE NVL(xe.budgetary_control_flag, 'N') ='Y'
859 AND xe.EVENT_STATUS_CODE in ('U' ,'I')
860 AND xe.PROCESS_STATUS_CODE IN ('I','D')
861 AND xe.event_id =pbd.ae_event_id
862 AND pbd.packet_id <> x_packet_id
863 AND pbd.ae_event_id IS NOT NULL
864 AND main_or_backing_code = 'M'
865 -- AND NVL(status_code,'I') <> 'P'
866 --Bug 16010392. If event status is U in xla_events and P in po_bc_dists,
867 --Such event has to be deleted.
868 AND (pbd.header_id,pbd.event_type_code) IN (SELECT DISTINCT header_id,event_type_code FROM po_bc_distributions WHERE packet_id = x_packet_id)
869 ) LOOP
870
871 l_event_count := l_event_count+1;
872 l_events_tab(l_event_count).entity_id := rec_events.entity_id;
873 l_events_tab(l_event_count).application_id := 201;
874 l_events_tab(l_event_count).ledger_id := l_ledger_id;
875 l_events_tab(l_event_count).legal_entity_id := l_legal_entity_id;
876 l_events_tab(l_event_count).entity_code := rec_events.applied_to_entity_code;
877 l_events_tab(l_event_count).event_id := rec_events.event_id;
878 l_events_tab(l_event_count).event_status_code := rec_events.event_status_code;
879 l_events_tab(l_event_count).process_status_code := rec_events.process_status_code;
880 l_events_tab(l_event_count).source_id_int_1 := rec_events.applied_to_header_id_1;
881
882 END LOOP;
883
884 IF g_debug_stmt THEN
885 PO_DEBUG.debug_var(l_log_head,l_progress,'Number of Draft events ',l_event_count );
886 END IF;
887
888 IF l_event_count > 0 THEN
889
890 FORALL i IN 1..l_event_count
891
892 INSERT INTO XLA_EVENTS_INT_GT
893 VALUES l_events_tab(i) ;
894
895 XLA_EVENTS_PUB_PKG.DELETE_BULK_EVENTS(p_application_id => 201);
896
897 FOR i in 1..l_event_count LOOP
898
899 DELETE FROM po_bc_distributions
900 WHERE applied_to_header_id_1 = l_events_tab(i).source_id_int_1
901 AND packet_id <> x_packet_id
902 AND ae_event_id = l_events_tab(i).event_id;
903
904 l_num_of_rows_deleted := SQL%rowcount;
905
906 IF g_debug_stmt THEN
907 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Number of rows deleted for event '
908 ||l_events_tab(i).event_id||' are '||l_num_of_rows_deleted );
909 END IF;
910 END LOOP;
911 END IF;
912
913 l_progress := '170' ;
914
915 -- Loop for all distributions in po_bc_distributions for generation of event ids
916 -- Event id is unique for distribution type and gl date combination
917
918
919 FOR rec_po_bc_dist IN (SELECT DISTINCT applied_to_header_id_1,segment1,distribution_type,gl_date,event_type_code,reference_number
920 FROM po_bc_distributions
921 WHERE packet_id = x_packet_id
922 AND main_or_backing_code = 'M')
923 LOOP
924
925
926 IF g_debug_stmt THEN
927
928 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Applied_to_header_id_1 ' || rec_po_bc_dist.applied_to_header_id_1);
929 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Distribution type ' || rec_po_bc_dist.distribution_type);
930 PO_DEBUG.debug_stmt(l_log_head,l_progress,'GL date ' || rec_po_bc_dist.gl_date);
931
932 END IF;
933
934 l_event_id := NULL;
935 l_event_source_info.source_application_id := NULL;
936 l_event_source_info.application_id := 201 ;
937 l_event_source_info.legal_entity_id := l_legal_entity_id;
938 l_event_source_info.ledger_id := l_ledger_id;
939 l_event_source_info.entity_type_code := get_entity_type_code(rec_po_bc_dist.distribution_type,p_action);
940 l_event_source_info.transaction_number := rec_po_bc_dist.reference_number;
941 l_event_source_info.source_id_int_1 := rec_po_bc_dist.applied_to_header_id_1;
942 l_security_context.security_id_int_1 := l_current_org_id ;
943
944 l_event_id := xla_events_pub_pkg.create_event
945 (
946 p_event_source_info => l_event_source_info,
947 p_event_type_code => rec_po_bc_dist.event_type_code,
948 p_event_date => rec_po_bc_dist.gl_date,
949 p_event_status_code => l_event_status_code,
950 p_event_number => l_event_number,
951 p_transaction_date => rec_po_bc_dist.gl_date,
952 p_reference_info => l_reference_info,
953 p_valuation_method => l_valuation_method,
954 p_security_context => l_security_context,
955 p_budgetary_control_flag => 'Y'
956 );
957 -- Update po_bc_distributions ae_event_id with the l_event_id.
958
959 IF g_debug_stmt THEN
960
961 PO_DEBUG.debug_stmt(l_log_head,l_progress,'l_event_id ' || l_event_id);
962 END IF;
963
964
965 IF l_event_id IS NOT NULL then
966
967 UPDATE po_bc_distributions
968 SET ae_event_id = l_event_id
969 WHERE packet_id = x_packet_id
970 AND applied_to_header_id_1 = rec_po_bc_dist.applied_to_header_id_1
971 AND gl_date = rec_po_bc_dist.gl_date
972 AND main_or_backing_code = 'M'
973 returning sequence_number
974 BULK COLLECT INTO l_seq_num_tbl;
975
976 FORALL i IN 1..l_seq_num_tbl.count
977 UPDATE po_bc_distributions pobd
978 SET pobd.ae_event_id = l_event_id
979 WHERE pobd.packet_id = x_packet_id
980 AND pobd.origin_sequence_num = l_seq_num_tbl(i);
981
982 l_Seq_num_tbl.delete;
983 END if;
984 END LOOP;
985 END IF;--if x_packet_id is not null
986
987 IF g_debug_stmt THEN
988 PO_DEBUG.debug_end(l_log_head);
989 END IF;
990
991 EXCEPTION
992 WHEN OTHERS THEN
993 x_packet_id := NULL; -- Bug 3218669
994 --add message to the stack and log a debug msg if necessary
995 po_message_s.sql_error(g_pkg_name, l_proc_name, l_progress, SQLCODE, SQLERRM);
996 fnd_msg_pub.add;
997 RAISE;
998 END INSERT_PACKET_CREATE_EVENT;
999
1000
1001 -------------------------------------------------------------------------------
1002 --Start of Comments
1003 --Name: execute_gl_call
1004 --Pre-reqs:
1005 -- The packet has already been inserted and committed to GL_BC_PACKETS.
1006 --Modifies:
1007 -- GL_BC_PACKETS
1008 --Locks:
1009 -- None.
1010 --Function:
1011 -- This procedure calls the GL funds checker that operates on the
1012 -- data in GL_BC_PACKETS and updates that table with the
1013 -- success/failure results.
1014 --Parameters:
1015 --IN:
1016 --p_set_of_books_id
1017 -- The set of books of this org.
1018 --p_packet_id
1019 -- The packet_id in GL_BC_PACKETS that should be operated on.
1020 --p_gl_mode
1021 -- Specifies whether to call GL in 'R'eserve, 'A'djust or
1022 -- 'F'orce mode
1023 --p_partial_resv_flag
1024 -- Indicates whether or not partial successes are allowed.
1025 --p_override
1026 -- Whether to use override authority in case of Funds
1027 -- Reservation failure due to lack of Funds.
1028 --p_conc_flag
1029 -- Whether invoked from a Concurrent Process.
1030 --p_user_id
1031 -- The user_id who is doing this action.
1032 --p_user_resp_id
1033 -- The responsibility id of the executer of this action.
1034 --OUT:
1035 --x_return_code
1036 -- The return code of the GL funds checker.
1037 --Testing:
1038 --
1039 --End of Comments
1040 -------------------------------------------------------------------------------
1041
1042 PROCEDURE execute_gl_call(
1043 p_set_of_books_id IN NUMBER,
1044 p_packet_id IN OUT NOCOPY NUMBER,
1045 p_gl_mode IN VARCHAR2,
1046 p_partial_resv_flag IN VARCHAR2,
1047 p_override IN VARCHAR2,
1048 p_conc_flag IN VARCHAR2,
1049 p_user_id IN NUMBER,
1050 p_user_resp_id IN NUMBER,
1051 x_return_code OUT NOCOPY VARCHAR2
1052 ) IS
1053
1054 l_api_name CONSTANT varchar2(40) := 'EXECUTE_GL_CALL';
1055 l_log_head CONSTANT VARCHAR2(100) := g_log_head || l_api_name;
1056 l_progress VARCHAR2(3) := '000';
1057 l_return_code BOOLEAN;
1058
1059
1060 l_validate_return_status VARCHAR2(1) := 'E';
1061 l_validate_msg_count NUMBER;
1062 l_validate_msg_data FND_NEW_MESSAGES.message_text%TYPE;
1063
1064 l_return_status VARCHAR2(20);
1065 l_status_code VARCHAR2(20);
1066 l_bc_mode VARCHAR2(1); -- Bug 4995509
1067 BEGIN
1068
1069 IF g_debug_stmt THEN
1070 PO_DEBUG.debug_begin(l_log_head);
1071 PO_DEBUG.debug_var(l_log_head,l_progress,'p_set_of_books_id',p_set_of_books_id);
1072 PO_DEBUG.debug_var(l_log_head,l_progress,'p_packet_id',p_packet_id);
1073 PO_DEBUG.debug_var(l_log_head,l_progress,'p_gl_mode',p_gl_mode);
1074 PO_DEBUG.debug_var(l_log_head,l_progress,'p_partial_resv_flag',p_partial_resv_flag);
1075 PO_DEBUG.debug_var(l_log_head,l_progress,'p_override',p_override);
1076 PO_DEBUG.debug_var(l_log_head,l_progress,'p_conc_flag',p_conc_flag);
1077 PO_DEBUG.debug_var(l_log_head,l_progress,'p_user_id',p_user_id);
1078 PO_DEBUG.debug_var(l_log_head,l_progress,'p_user_resp_id',p_user_resp_id);
1079 END IF;
1080
1081 l_progress := '100';
1082 -- Bug 4995509 Begin
1083 IF (p_partial_resv_flag = 'Y') AND (p_gl_mode <> 'C') THEN
1084 l_bc_mode := 'P' ;
1085 ELSE
1086 l_bc_mode := p_gl_mode ;
1087 END IF;
1088 -- Bug 4995509 End
1089 --<bug#5200462 START>
1090 --Force mode would be set for requisition split.
1091 --We should be calling Req Split with force mode
1092 --for all non-federal cases. This is to ensure that
1093 --requisition split always succeeds. This is allright
1094 --because we are not increasing funds in any way. We
1095 --can only keep it constant or decrease funds.
1096 -- IF (p_gl_mode <> 'F') THEN
1097
1098 l_progress := '110';
1099 --<SLA R12 Begin>
1100
1101 -- Call the Budgetary Control API
1102 PSA_BC_XLA_PUB.Budgetary_Control
1103 (
1104 p_api_version => 1.0, --bug5304012
1105 p_application_id => 201, -- Bug 5013999
1106 x_packet_id => p_packet_id,
1107 p_bc_mode => l_bc_mode,
1108 p_override_flag => p_override,
1109 p_user_id => p_user_id,
1110 p_user_resp_id => p_user_resp_id,
1111 x_msg_count => l_validate_msg_count,
1112 x_msg_data => l_validate_msg_data,
1113 x_return_status => l_return_status,
1114 x_status_code => l_status_code
1115 );
1116 IF (l_status_code = 'SUCCESS') THEN
1117 x_return_code := 'S';
1118 ELSIF (l_status_code = 'PARTIAL') THEN
1119 x_return_code := 'P';
1120 ELSIF (l_status_code = 'ADVISORY') THEN
1121 x_return_code := 'A';
1122 ELSIF (l_status_code = 'FAIL') THEN
1123 x_return_code := 'F';
1124 ELSE
1125 x_return_code := 'F';
1126 END IF;
1127 l_progress := '120';
1128 --<SLA R12 End>
1129
1130 IF g_debug_stmt THEN
1131 PO_DEBUG.debug_var(l_log_head,l_progress,'PSA function return_code', l_status_code);
1132 END IF;
1133
1134 --<R12 SLA Begin>
1135 -- Raise exception if return status is Unexpected error('U') or
1136 -- if return code is FATAL/XLA_ERROR/XLA_NO_JOURNAL
1137 IF ( l_return_status IN ('U')
1138 OR l_status_code IN ('FATAL', 'XLA_ERROR', 'XLA_NO_JOURNAL' ) -- Bug 5009730
1139 )
1140 THEN
1141 l_progress := '125';
1142 -- Delete records from po_bc_distributions
1143 --bug#5523323 once we remove the autonomous txn we don't have to explicity delete
1144 --data from po_bc_distributions.
1145 --Delete_PO_BC_Distributions(p_packet_id => p_packet_id); -- Bug #4637958
1146
1147 l_progress := '130';
1148 RAISE g_GL_FUNDS_API_EXC;
1149 END IF;
1150 --<R12 SLA End>
1151
1152 l_progress := '140';
1153
1154 IF g_debug_stmt THEN
1155 PO_DEBUG.debug_stmt(l_log_head,l_progress,'GL Call Executed Successfully');
1156 END IF;
1157
1158 -- END IF; -- p_gl_mode <> 'F' or l_validate_return_status...
1159 --<bug#5200462 END>
1160
1161 l_progress := '900';
1162
1163 IF g_debug_stmt THEN
1164 PO_DEBUG.debug_var(l_log_head,l_progress,'GL Call x_return_code',x_return_code);
1165 PO_DEBUG.debug_end(l_log_head);
1166 END IF;
1167
1168 EXCEPTION
1169 WHEN g_GL_FUNDS_API_EXC THEN
1170 --<BEGIN Bug:12824154>---
1171 /*FND_MESSAGE.set_name('PO', 'PO_API_ERROR');
1172 FND_MESSAGE.set_token('PROC_CALLED',
1173 'PSA_BC_XLA_PUB.Budgetary_Control');
1174 FND_MESSAGE.set_token('PROC_CALLER',
1175 'PO_ENCUMBRANCE_POSTPROCESSING.execute_gl_call');
1176 fnd_msg_pub.add;
1177 IF g_debug_unexp THEN
1178 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_code',x_return_code);
1179 END IF; */
1180 RAISE PO_ENCUMBRANCE_POSTPROCESSING.g_EXECUTE_GL_CALL_API_EXC;
1181 --<END Bug:12824154>---
1182
1183 WHEN OTHERS THEN
1184 --add message to the stack and log a debug msg if necessary
1185 po_message_s.sql_error(g_pkg_name, l_api_name, l_progress,
1186 SQLCODE, SQLERRM);
1187 fnd_msg_pub.add;
1188 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1189
1190 END execute_gl_call;
1191
1192
1193
1194 -------------------------------------------------------------------------------
1195 --Start of Comments
1196 --To address bug 12405805
1197 --Name: delete_unnecessary_eventss
1198 --Pre-reqs:
1199 --None
1200 --Modifies:
1201 -- PO_BC_DISTRIBUTIONS
1202 --Locks:
1203 -- None.
1204 --Function:
1205 --Based on records inserted into po_bc_distributions during insert_packet_create_event,
1206 --this function identifies the unprocessed events associated with current encumbrance action
1207 --which are in draft/invalid status. Records in PO_BC corresponding to such events are deleted and those
1208 --events are also deleted.
1209 --Parameters:
1210 --IN:
1211 --p_packet_id
1212 -- ID of the packet inserted into PO_BC_DISTRIBUTIONS table.
1213 --p_action
1214 -- The current encumbrance action
1215 --Testing:
1216 --
1217 --End of Comments
1218 -------------------------------------------------------------------------------
1219
1220 PROCEDURE delete_unnecessary_events(
1221 p_packet_id IN NUMBER,
1222 p_action IN VARCHAR2
1223
1224 )
1225 IS
1226
1227 TYPE t_event_id IS TABLE OF po_bc_distributions.ae_event_id%TYPE;
1228 l_event_id t_event_id := t_event_id();
1229 l_event_source_info xla_events_pub_pkg.t_event_source_info;
1230 l_delete_event NUMBER;
1231 l_security_context xla_events_pub_pkg.t_security;
1232 l_api_name CONSTANT varchar2(30) := 'delete_unnecessary_events';
1233 l_log_head CONSTANT varchar2(100) := g_log_head || l_api_name;
1234 l_progress VARCHAR2(3) := '000';
1235
1236 CURSOR to_delete_checkfunds IS
1237 SELECT DISTINCT pbd.ae_event_id,
1238 pbd.segment1,
1239 pbd.applied_to_header_id_1,
1240 pbd.distribution_type
1241 FROM
1242 po_bc_distributions pbd,
1243 xla_events xe,
1244 xla_transaction_entities xte
1245 WHERE
1246 xe.event_id = pbd.ae_event_id
1247 AND xe.EVENT_STATUS_CODE = 'U'
1248 AND xe.PROCESS_STATUS_CODE in ('D', 'I')
1249 AND pbd.packet_id = p_packet_id
1250 AND xte.application_id = 201
1251 AND xte.entity_id = xe.entity_id
1252 AND xte.source_id_int_1 = pbd.header_id;
1253
1254 CURSOR to_delete_invalids IS
1255 SELECT DISTINCT pbd.ae_event_id,
1256 pbd.segment1,
1257 pbd.applied_to_header_id_1,
1258 pbd.distribution_type
1259 FROM
1260 po_bc_distributions pbd,
1261 xla_events xe,
1262 xla_transaction_entities xte
1263 WHERE
1264 xe.event_id = pbd.ae_event_id
1265 AND xe.EVENT_STATUS_CODE = 'P'
1266 AND xe.PROCESS_STATUS_CODE = 'I'
1267 AND pbd.packet_id = p_packet_id
1268 AND xte.application_id = 201
1269 AND xte.entity_id = xe.entity_id
1270 AND xte.source_id_int_1 = pbd.header_id;
1271
1272 BEGIN
1273
1274 IF g_debug_stmt THEN
1275 PO_DEBUG.debug_var(l_log_head,l_progress,'p_packet_id', p_packet_id);
1276 PO_DEBUG.debug_var(l_log_head,l_progress,'p_action', p_action);
1277 END IF;
1278
1279 /*Gathering the event source info to delete event */
1280 l_event_source_info.legal_entity_id := xle_utilities_grp.Get_DefaultLegalContext_OU(PO_MOAC_UTILS_PVT.Get_Current_Org_Id );
1281 l_event_source_info.source_application_id := NULL;
1282 l_event_source_info.application_id := 201 ;
1283
1284 SELECT set_of_books_id
1285 INTO l_event_source_info.ledger_id
1286 FROM hr_operating_units hou
1287 WHERE hou.organization_id = PO_MOAC_UTILS_PVT.Get_Current_Org_Id ;
1288
1289 l_security_context.security_id_int_1 := PO_MOAC_UTILS_PVT.Get_Current_Org_Id ;
1290
1291 l_progress := '010';
1292
1293 IF g_debug_stmt THEN
1294 PO_DEBUG.debug_var(l_log_head,l_progress,'legal_entity_id', l_event_source_info.legal_entity_id);
1295 PO_DEBUG.debug_var(l_log_head,l_progress,'ledger_id', l_event_source_info.ledger_id);
1296 PO_DEBUG.debug_var(l_log_head,l_progress,'security_id_int_1', l_security_context.security_id_int_1);
1297 END IF;
1298
1299
1300 /*delete draft and invalid events*/
1301
1302 IF (p_action= g_action_RESERVE) THEN
1303 l_progress := '030';
1304 FOR rec_to_del IN to_delete_checkfunds loop
1305 IF g_debug_stmt THEN
1306 PO_DEBUG.debug_var(l_log_head,l_progress,'iteration for event_id', rec_to_del.ae_event_id);
1307 END IF;
1308
1309 /*event_ids are collected now*/
1310 /*have to delete these events*/
1311
1312 l_event_source_info.entity_type_code := get_entity_type_code(rec_to_del.distribution_type,p_action);
1313 l_event_source_info.transaction_number := rec_to_del.segment1;
1314 l_event_source_info.source_id_int_1 := rec_to_del.applied_to_header_id_1;
1315
1316
1317 IF g_debug_stmt THEN
1318 PO_DEBUG.debug_var(l_log_head,l_progress,'l_event_source_info.entity_type_code', l_event_source_info.entity_type_code);
1319 PO_DEBUG.debug_var(l_log_head,l_progress,'l_event_source_info.transaction_number', l_event_source_info.transaction_number);
1320 PO_DEBUG.debug_var(l_log_head,l_progress,'l_event_source_info.source_id_int_1', l_event_source_info.source_id_int_1 );
1321 END IF;
1322
1323 xla_events_pub_pkg.DELETE_EVENT
1324 (
1325 p_event_source_info => l_event_source_info,
1326 p_event_id => rec_to_del.ae_event_id,
1327 p_valuation_method => NULL,
1328 p_security_context => l_security_context
1329 );
1330 END LOOP;
1331 DELETE FROM po_bc_distributions WHERE packet_id = p_packet_id;
1332 ELSE
1333
1334 FOR rec_to_del IN to_delete_invalids loop
1335
1336 /*event_ids are collected now*/
1337 /*have to delete these events*/
1338
1339 l_event_source_info.entity_type_code := get_entity_type_code(rec_to_del.distribution_type,p_action);
1340 l_event_source_info.transaction_number := rec_to_del.segment1;
1341 l_event_source_info.source_id_int_1 := rec_to_del.applied_to_header_id_1;
1342
1343 IF g_debug_stmt THEN
1344 PO_DEBUG.debug_var(l_log_head,l_progress,'l_event_source_info.entity_type_code', l_event_source_info.entity_type_code);
1345 PO_DEBUG.debug_var(l_log_head,l_progress,'l_event_source_info.transaction_number', l_event_source_info.transaction_number);
1346 PO_DEBUG.debug_var(l_log_head,l_progress,'l_event_source_info.source_id_int_1', l_event_source_info.source_id_int_1 );
1347 END IF;
1348
1349 xla_events_pub_pkg.DELETE_EVENT
1350 (
1351 p_event_source_info => l_event_source_info,
1352 p_event_id => rec_to_del.ae_event_id,
1353 p_valuation_method => NULL,
1354 p_security_context => l_security_context
1355 );
1356
1357 END LOOP;
1358 DELETE FROM po_bc_distributions WHERE packet_id = p_packet_id ;
1359
1360 END IF;
1361
1362 EXCEPTION
1363 WHEN OTHERS THEN
1364 IF g_debug_stmt THEN
1365 PO_DEBUG.debug_var(l_log_head,l_progress, 'Exception block of Delete_unnecessary_events', SQLERRM);
1366 END IF;
1367 RAISE;
1368 END;
1369
1370 /* End of Bug 12405805 */
1371
1372 -------------------------------------------------------------------------------
1373 --Start of Comments
1374 --Name: copy_detailed_gl_results
1375 --Pre-reqs:
1376 -- PO_ENCUMBRANCE_GT is populated with all calculations.
1377 -- Call to GL Funds Checker already made on given packet ID.
1378 --Modifies:
1379 -- GL_BC_PACKETS
1380 --Locks:
1381 -- None.
1382 --Function:
1383 -- Copies result information from gl_bc_packets into PO_ENCUMBRANCE_GT
1384 -- Further post-processing/error reporting relies on this information from GTT
1385 --Parameters:
1386 --IN:
1387 --p_packet_id
1388 -- ID of the packet inserted into gl_bc_packets table.
1389 --p_gl_return_code
1390 -- The overall transaction return code provided by GL from the glxfck() call
1391 --Testing:
1392 --
1393 --End of Comments
1394 -------------------------------------------------------------------------------
1395 PROCEDURE copy_detailed_gl_results(
1396 p_packet_id IN NUMBER,
1397 p_gl_return_code IN VARCHAR2
1398 )
1399 IS
1400
1401 l_api_name CONSTANT varchar2(40) := 'COPY_DETAILED_GL_RESULTS';
1402 l_log_head CONSTANT varchar2(100) := g_log_head || l_api_name;
1403 l_progress VARCHAR2(3) := '000';
1404
1405 l_debug_count NUMBER;
1406 l_po_prevent_text FND_NEW_MESSAGES.message_text%TYPE;
1407 l_not_processed_msg FND_NEW_MESSAGES.message_text%TYPE;
1408
1409 -- bug3543542 START
1410 TYPE gl_status_tbl_type IS TABLE OF PO_ENCUMBRANCE_GT.gl_status_code%TYPE;
1411 TYPE gl_result_code_tbl_type IS TABLE OF PO_ENCUMBRANCE_GT.gl_result_code%TYPE;
1412 TYPE update_enc_amt_flag_tbl_type IS TABLE OF PO_ENCUMBRANCE_GT.update_encumbered_amount_flag%TYPE;
1413 TYPE dist_id_tbl_type IS TABLE OF PO_ENCUMBRANCE_GT.distribution_id%TYPE;
1414 TYPE reference15_tbl_type IS TABLE OF PO_ENCUMBRANCE_GT.reference15%TYPE; --<bug#5201733 START>
1415 l_gl_status_tbl gl_status_tbl_type;
1416 l_gl_result_code_tbl gl_result_code_tbl_type;
1417 l_update_enc_amt_flag_tbl update_enc_amt_flag_tbl_type;
1418 l_dist_id_tbl dist_id_tbl_type;
1419 l_dist_type_tbl po_tbl_varchar30;
1420 -- bug3543542 END
1421 l_encumbered_amount_change po_tbl_number; -- Bug 4878973
1422 l_reference15_tbl reference15_tbl_type;--bug#5201733
1423 BEGIN
1424
1425 IF g_debug_stmt THEN
1426 PO_DEBUG.debug_begin(l_log_head);
1427 PO_DEBUG.debug_var(l_log_head,l_progress,'p_packet_id',p_packet_id);
1428 PO_DEBUG.debug_var(l_log_head,l_progress,'p_gl_return_code',p_gl_return_code);
1429 END IF;
1430 --<bug#5353223 START>
1431 l_progress := '010';
1432
1433 -- Bug#14593047 START: Revamped the query to removed the
1434 -- XLA_DISTRIBUTIONS_LINKS table as the join between po_bc_distributions
1435 -- and gl_bc_packets can be achieved with event id condition
1436 -- This is necessary to improve the performance of the query which will
1437 -- avoid FTS on the XLA table.
1438 --<bug#5112144 START>
1439 --Modified the query below to join with gl_bc_packets_hists
1440 --This is so that the error rows are always found. GL moves
1441 --all errored rows to gl_bc_packets_hist for performance reasons.
1442 --<bug#5201733 START>
1443 --We have to join with xla_distribution_links to get a mapping between
1444 --po_bc_distributions and gl_bc_packets. We need a distinct to ensure that
1445 --for federal /customization cases where there could be multiple rows in
1446 --gl_bc_packets for the same distribution po_bc_distributions we are returned
1447 --just one row.
1448 --<Bug#7138036 START>
1449 -- We will get all the packets id from gl_bc_packets for the events which we have populated in
1450 -- psa_bc_xla_events_gt table for the current packet Id. We won't use the packet id returned by
1451 -- Execute GL call as there can be mutiple packet id from which we need to take the result from
1452 -- GL BC PACKETS. In case of Cancel action with recreate demand we create two events, which belong
1453 -- to different entity. So two packets will be created by PSA.
1454 --<Bug#7437681 START>
1455 -- Performance Fix : Changed in Inner query based on psa_bc_xla_events_gt to
1456 -- change the Plan of the Overall SQL.
1457 /* bug 13562823 - To improve performance added hint and column code_combination_id so that index on packet_id and code_combination_id
1458 on table GL_BC_PACKETS would be considered. */
1459 /*
1460 SELECT
1461 DISTINCT
1462 STATUS_CODE,
1463 RESULT_CODE,
1464 AUTOMATIC_ENCUMBRANCE_FLAG,
1465 SOURCE_DISTRIBUTION_ID_NUM_1,
1466 SOURCE_DISTRIBUTION_TYPE,
1467 TRANSACTION_AMOUNT,
1468 REFERENCE15
1469 BULK COLLECT
1470 INTO l_gl_status_tbl,
1471 l_gl_result_code_tbl,
1472 l_update_enc_amt_flag_tbl,
1473 l_dist_id_tbl,
1474 l_dist_type_tbl,
1475 l_encumbered_amount_change,
1476 l_reference15_tbl
1477 FROM (
1478
1479 SELECT GLBC.status_code STATUS_CODE,
1480 GLBC.result_code RESULT_CODE,
1481 nvl(GLBC.automatic_encumbrance_flag, 'Y') AUTOMATIC_ENCUMBRANCE_FLAG,
1482 GLBC.SOURCE_DISTRIBUTION_ID_NUM_1 SOURCE_DISTRIBUTION_ID_NUM_1,
1483 GLBC.SOURCE_DISTRIBUTION_TYPE SOURCE_DISTRIBUTION_TYPE,
1484 PO_ENCUMBRANCE_POSTPROCESSING.get_sign_for_amount(pbd.event_type_code,
1485 pbd.main_or_backing_code,
1486 pbd.adjustment_status,
1487 pbd.distribution_type) *
1488 PBD.accounted_amt TRANSACTION_AMOUNT,
1489 PBD.reference15 REFERENCE15
1490 FROM XLA_DISTRIBUTION_LINKS XLD,
1491 PO_BC_DISTRIBUTIONS PBD,
1492 GL_BC_PACKETS GLBC
1493 WHERE (GLBC.PACKET_ID, GLBC.CODE_COMBINATION_ID) IN (
1494 SELECT /*+ unnest */
1495 /* DISTINCT glbc1.packet_id,
1496 glbc1.code_combination_id
1497 FROM psa_bc_xla_events_gt ps_ev_Gt,
1498 GL_BC_PACKETS glbc1
1499 WHERE ps_ev_Gt.event_id = glbc1.event_id)
1500 AND XLD.AE_HEADER_ID = GLBC.ae_header_id
1501 AND xld.ae_line_num = GLBC.ae_line_num
1502 AND xld.event_id = GLBC.event_id
1503 AND GLBC.application_id = xld.application_id
1504 AND GLBC.source_distribution_type = xld.source_distribution_type
1505 AND GLBC.SOURCE_DISTRIBUTION_ID_NUM_1 =
1506 xld.SOURCE_DISTRIBUTION_ID_NUM_1
1507 AND pbd.distribution_id = xld.SOURCE_DISTRIBUTION_ID_NUM_1
1508 AND decode(pbd.distribution_type,g_dist_type_REQUISITION,
1509 'PO_REQ_DISTRIBUTIONS_ALL','PO_DISTRIBUTIONS_ALL') = xld.source_distribution_type
1510 AND pbd.ae_event_id = xld.event_id
1511 AND NVL(PBD.applied_to_dist_id_2, pbd.distribution_id) =
1512 XLD.ALLOC_TO_DIST_ID_NUM_1
1513 AND xld.application_id = 201
1514 AND xld.event_id = pbd.ae_event_id
1515 AND glbc.template_id is null
1516 UNION ALL
1517 SELECT GLBCH.status_code STATUS_CODE,
1518 GLBCH.result_code RESULT_CODE,
1519 nvl(GLBCH.automatic_encumbrance_flag, 'Y') AUTOMATIC_ENCUMBRANCE_FLAG,
1520 GLBCH.SOURCE_DISTRIBUTION_ID_NUM_1 SOURCE_DISTRIBUTION_ID_NUM_1,
1521 GLBCH.SOURCE_DISTRIBUTION_TYPE SOURCE_DISTRIBUTION_TYPE,
1522 PO_ENCUMBRANCE_POSTPROCESSING.get_sign_for_amount(pbd.event_type_code,
1523 pbd.main_or_backing_code,
1524 pbd.adjustment_status,
1525 pbd.distribution_type) *
1526 PBD.accounted_amt TRANSACTION_AMOUNT,
1527 PBD.reference15 REFERENCE15
1528 FROM XLA_DISTRIBUTION_LINKS XLD,
1529 PO_BC_DISTRIBUTIONS PBD,
1530 GL_BC_PACKETS_HISTS GLBCH
1531 WHERE GLBCH.PACKET_ID IN (SELECT DISTINCT glbch1.packet_id
1532 FROM psa_bc_xla_events_gt ps_ev_Gt ,
1533 GL_BC_PACKETS_HISTS glbch1
1534 WHERE ps_ev_Gt.event_id = glbch1.event_id)
1535 AND XLD.AE_HEADER_ID = GLBCH.ae_header_id
1536 AND xld.ae_line_num = GLBCH.ae_line_num
1537 AND xld.event_id = GLBCH.event_id
1538 AND GLBCH.application_id = xld.application_id
1539 AND GLBCH.source_distribution_type =
1540 xld.source_distribution_type
1541 AND GLBCH.SOURCE_DISTRIBUTION_ID_NUM_1 =
1542 xld.SOURCE_DISTRIBUTION_ID_NUM_1
1543 AND pbd.distribution_id = xld.SOURCE_DISTRIBUTION_ID_NUM_1
1544 AND decode(pbd.distribution_type,g_dist_type_REQUISITION,
1545 'PO_REQ_DISTRIBUTIONS_ALL','PO_DISTRIBUTIONS_ALL') = xld.source_distribution_type
1546 AND pbd.ae_event_id = xld.event_id
1547 AND NVL(PBD.applied_to_dist_id_2, pbd.distribution_id) =
1548 XLD.ALLOC_TO_DIST_ID_NUM_1
1549 AND xld.application_id = 201
1550 AND xld.event_id = pbd.ae_event_id
1551 AND glbch.template_id is null
1552 ); */
1553 --<Bug#7437681 END>
1554 --<Bug#7138036 END>
1555 --<bug#5201733 END>
1556 --<bug#5112144 END>
1557 --<bug#5353223 END>
1558
1559 SELECT
1560 DISTINCT
1561 STATUS_CODE,
1562 RESULT_CODE,
1563 AUTOMATIC_ENCUMBRANCE_FLAG,
1564 SOURCE_DISTRIBUTION_ID_NUM_1,
1565 SOURCE_DISTRIBUTION_TYPE,
1566 TRANSACTION_AMOUNT,
1567 REFERENCE15
1568 BULK COLLECT
1569 INTO l_gl_status_tbl,
1570 l_gl_result_code_tbl,
1571 l_update_enc_amt_flag_tbl,
1572 l_dist_id_tbl,
1573 l_dist_type_tbl,
1574 l_encumbered_amount_change,
1575 l_reference15_tbl
1576 FROM ( SELECT GLBC.STATUS_CODE STATUS_CODE,
1577 GLBC.RESULT_CODE RESULT_CODE,
1578 NVL (GLBC.AUTOMATIC_ENCUMBRANCE_FLAG, 'Y')
1579 AUTOMATIC_ENCUMBRANCE_FLAG,
1580 GLBC.SOURCE_DISTRIBUTION_ID_NUM_1
1581 SOURCE_DISTRIBUTION_ID_NUM_1,
1582 GLBC.SOURCE_DISTRIBUTION_TYPE SOURCE_DISTRIBUTION_TYPE,
1583 PO_ENCUMBRANCE_POSTPROCESSING.GET_SIGN_FOR_AMOUNT (
1584 PBD.EVENT_TYPE_CODE,
1585 PBD.MAIN_OR_BACKING_CODE,
1586 PBD.ADJUSTMENT_STATUS,
1587 PBD.DISTRIBUTION_TYPE
1588 )
1589 * PBD.ACCOUNTED_AMT
1590 TRANSACTION_AMOUNT,
1591 PBD.REFERENCE15 REFERENCE15
1592 FROM PO_BC_DISTRIBUTIONS PBD,
1593 GL_BC_PACKETS GLBC
1594 WHERE pbd.packet_id = p_packet_id
1595 AND pbd.ae_EVENT_ID = GLBC.EVENT_ID
1596 AND GLBC.TEMPLATE_ID IS NULL
1597 AND GLBC.SOURCE_DISTRIBUTION_ID_NUM_1 = pbd.distribution_id --Bug 16437550
1598
1599
1600 UNION ALL
1601 SELECT GLBCH.STATUS_CODE STATUS_CODE,
1602 GLBCH.RESULT_CODE RESULT_CODE,
1603 NVL (GLBCH.AUTOMATIC_ENCUMBRANCE_FLAG, 'Y')
1604 AUTOMATIC_ENCUMBRANCE_FLAG,
1605 GLBCH.SOURCE_DISTRIBUTION_ID_NUM_1
1606 SOURCE_DISTRIBUTION_ID_NUM_1,
1607 GLBCH.SOURCE_DISTRIBUTION_TYPE SOURCE_DISTRIBUTION_TYPE,
1608 PO_ENCUMBRANCE_POSTPROCESSING.GET_SIGN_FOR_AMOUNT (
1609 PBD.EVENT_TYPE_CODE,
1610 PBD.MAIN_OR_BACKING_CODE,
1611 PBD.ADJUSTMENT_STATUS,
1612 PBD.DISTRIBUTION_TYPE
1613 )
1614 * PBD.ACCOUNTED_AMT
1615 TRANSACTION_AMOUNT,
1616 PBD.REFERENCE15 REFERENCE15
1617 FROM PO_BC_DISTRIBUTIONS PBD,
1618 GL_BC_PACKETS_HISTS GLBCH
1619 WHERE pbd.packet_id = p_packet_id
1620 AND pbd.ae_EVENT_ID = GLBCH.EVENT_ID
1621 AND GLBCH.TEMPLATE_ID IS NULL
1622 AND GLBCH.SOURCE_DISTRIBUTION_ID_NUM_1 = pbd.distribution_id --Bug 16437550
1623 );
1624 -- Bug#14593047 END--
1625
1626 IF g_debug_stmt THEN
1627 l_debug_count := SQL%ROWCOUNT;
1628 PO_DEBUG.debug_stmt(l_log_head,l_progress, 'Status/Result codes returned for '
1629 || l_debug_count || ' rows');
1630 END IF;
1631
1632 l_progress := '020';
1633
1634 FORALL i IN 1..l_gl_status_tbl.COUNT
1635 UPDATE PO_ENCUMBRANCE_GT TEMP
1636 SET TEMP.gl_status_code = l_gl_status_tbl(i),
1637 TEMP.gl_result_code = l_gl_result_code_tbl(i),
1638 TEMP.update_encumbered_amount_flag = l_update_enc_amt_flag_tbl(i),
1639 TEMP.encumbered_amount_change = l_encumbered_amount_change(i) -- Bug 4878973
1640 WHERE TEMP.reference15=l_reference15_tbl(i);--bug#5201733 joining using reference15 as this is the unique key
1641
1642 --NOTE: The Temp Table field encumbered_amount_change is used throughout these
1643 -- update calculations in update_successful_rows.
1644
1645 --Note: in bug 3568512, we already set update_encumbered_amount_flag = 'N'
1646 --for backing BPA/PPO rows that are unreserved (in update_encumbrance_gt).
1647 --these rows also have a send_to_gl_flag = 'N'. We have to maintain
1648 --the UNencumbered_amount for these rows, but do not want to update
1649 --the encumbered_amount, since no trxn was sent to GL.
1650
1651 IF g_debug_stmt THEN
1652 l_debug_count := SQL%ROWCOUNT;
1653 PO_DEBUG.debug_stmt(l_log_head,l_progress, 'Status/Result codes updated on '
1654 || l_debug_count || ' rows');
1655 END IF;
1656
1657 --<bug#5112144 START>
1658 --Reinstating this piece of code so that online_report gets populated
1659 --as before and this would work for Workflow , Modify Requisition and
1660 --Requisition Split in oa as well as isp and messages would be displayed.
1661
1662 l_progress := '030';
1663
1664 l_po_prevent_text := FND_MESSAGE.get_string('PO', 'PO_ENC_DIST_PREVENTED');
1665
1666 UPDATE PO_ENCUMBRANCE_GT DISTS
1667 SET DISTS.result_text = l_po_prevent_text
1668 , DISTS.result_type = g_result_WARNING
1669 WHERE DISTS.prevent_encumbrance_flag = 'Y'
1670 AND DISTS.result_text IS NULL;
1671
1672 IF g_debug_stmt THEN
1673 l_debug_count := SQL%ROWCOUNT;
1674 PO_DEBUG.debug_stmt(l_log_head,l_progress,
1675 'Number of prevent rows updated: ' || l_debug_count);
1676 END IF;
1677
1678 l_progress := '040';
1679
1680 UPDATE PO_ENCUMBRANCE_GT DISTS
1681 SET DISTS.result_text =
1682 (SELECT GL_TEXT.description
1683 FROM GL_LOOKUPS GL_TEXT
1684 WHERE GL_TEXT.lookup_type = 'FUNDS_CHECK_RESULT_CODE'
1685 AND GL_TEXT.lookup_code(+) = DISTS.gl_result_code
1686 )
1687 WHERE DISTS.gl_result_code IS NOT NULL
1688 AND DISTS.result_text IS NULL;
1689
1690 IF g_debug_stmt THEN
1691 l_debug_count := SQL%ROWCOUNT;
1692 PO_DEBUG.debug_stmt(l_log_head,l_progress,
1693 'Number of result text rows updated: ' || l_debug_count);
1694 END IF;
1695
1696 l_progress := '050';
1697
1698 IF p_gl_return_code IN ('A', 'S', 'P') THEN
1699
1700 l_progress := '060';
1701 IF g_debug_stmt THEN
1702 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Updating successful rows.');
1703 END IF;
1704
1705 UPDATE PO_ENCUMBRANCE_GT DISTS
1706 SET DISTS.result_type = g_result_WARNING
1707 WHERE DISTS.gl_result_code IN
1708 /*Bug13887688 Adding few more lookup_codes which are also WARNINGS*/
1709 ('P20','P21','P22','P23','P25','P26','P27', 'P39','P29','P31', 'P35', 'P36', 'P37', 'P38')
1710 AND DISTS.gl_status_code IN ('A', 'S');
1711
1712 l_progress := '070';
1713
1714 IF g_debug_stmt THEN
1715 l_debug_count := SQL%ROWCOUNT;
1716 PO_DEBUG.debug_stmt(l_log_head,l_progress,
1717 'Number of rows set to Warning: ' || l_debug_count);
1718 END IF;
1719
1720 UPDATE PO_ENCUMBRANCE_GT DISTS
1721 SET DISTS.result_type = g_result_SUCCESS
1722 WHERE DISTS.result_type IS NULL
1723 AND DISTS.gl_status_code IN ('A', 'S');
1724
1725 l_progress := '080';
1726
1727 IF g_debug_stmt THEN
1728 l_debug_count := SQL%ROWCOUNT;
1729 PO_DEBUG.debug_stmt(l_log_head,l_progress,
1730 'Number of rows set to Success: ' || l_debug_count);
1731 END IF;
1732
1733 END IF; -- gl_return_code is S, A or P
1734
1735 l_progress := '100';
1736
1737 IF p_gl_return_code IN ('P', 'F', 'T') THEN
1738
1739 l_progress := '110';
1740
1741 IF g_debug_stmt THEN
1742 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Updating failed rows.');
1743 END IF;
1744
1745 l_not_processed_msg := FND_MESSAGE.get_string('PO', 'PO_ENC_DIST_NOT_PROCESSED');
1746
1747 UPDATE PO_ENCUMBRANCE_GT DISTS
1748 SET DISTS.result_type = g_result_ERROR
1749 WHERE DISTS.result_type IS NULL
1750 AND DISTS.gl_result_code like 'F%'
1751 AND DISTS.gl_status_code IN ('R', 'F');
1752
1753 l_progress := '120';
1754
1755 IF g_debug_stmt THEN
1756 l_debug_count := SQL%ROWCOUNT;
1757 PO_DEBUG.debug_stmt(l_log_head,l_progress,
1758 'Number of rows failed: ' || l_debug_count);
1759 END IF;
1760
1761 UPDATE PO_ENCUMBRANCE_GT DISTS
1762 SET DISTS.result_type = g_result_NOT_PROCESSED
1763 , DISTS.result_text = l_not_processed_msg
1764 WHERE DISTS.result_type IS NULL
1765 AND DISTS.gl_result_code like 'P%'
1766 AND DISTS.gl_status_code IN ('R', 'F');
1767
1768 l_progress := '130';
1769
1770 IF g_debug_stmt THEN
1771 l_debug_count := SQL%ROWCOUNT;
1772 PO_DEBUG.debug_stmt(l_log_head,l_progress,
1773 'Number of rows not processed: ' || l_debug_count);
1774 END IF;
1775
1776 END IF; -- gl_return_code is P, F or T
1777
1778 l_progress := '900';
1779
1780 IF g_debug_stmt THEN
1781 PO_DEBUG.debug_stmt(l_log_head,l_progress,
1782 'PO update of text/message type successful');
1783 PO_DEBUG.debug_table(l_log_head, l_progress, 'PO_ENCUMBRANCE_GT',
1784 PO_DEBUG.g_all_rows,
1785 po_tbl_varchar30('result_text', 'result_type',
1786 'prevent_encumbrance_flag')
1787 );
1788 PO_DEBUG.debug_end(l_log_head);
1789 END IF;
1790 --<bug#5112144 END>
1791 EXCEPTION
1792
1793 WHEN OTHERS THEN
1794 --add message to the stack and log a debug msg if necessary
1795 po_message_s.sql_error(g_pkg_name, l_api_name, l_progress, SQLCODE, SQLERRM);
1796 fnd_msg_pub.add;
1797 RAISE;
1798
1799 END copy_detailed_gl_results;
1800
1801 -------------------------------------------------------------------------------
1802 --Start of Comments
1803 --Name: update_document_encumbrance
1804 --Pre-reqs:
1805 -- PO_ENCUMBRANCE_GT has all necessary information populated after
1806 -- making a call to the GL Funds Checker
1807 --Modifies:
1808 -- PO_REQ_DISTRIBUTIONS_ALL, PO_DISTRIBUTIONS_ALL, PO_REQUISITION_LINES_ALL,
1809 -- PO_LINE_LOCATIONS_ALL
1810 --Locks:
1811 -- None.
1812 --Function:
1813 -- This procedure calls helpers to update the distributions appropriately based
1814 -- on whether transaction was successful or not. It is only called for
1815 -- actual encumbrance actions (Pending mode), but NOT Funds Check (Checking
1816 -- mode)
1817 --Parameters:
1818 --IN:
1819 --p_doc_type
1820 -- Differentiates between the main doc being a REQUISITION, AGREEMENT,
1821 -- PURCHASE ORDER, or RELEASE which is used to identify the tables to look at
1822 -- (PO vs. Req) and the join conditions
1823 --p_doc_subtype
1824 -- Differentiates between the possible subtypes of the main document
1825 -- REQUISITION: NULL
1826 -- PURCHASE ORDER: STANDARD, PLANNED
1827 -- AGREEMENT: CONTRACT, BLANKET
1828 -- RELEASE: SCHEDULED, BLANKET
1829 --p_action
1830 -- Encumbrance action requested on the main document:
1831 -- RESERVE/UNRESERVE/CANCEL/FINAL CLOSE/RETURN/REJECT/ADJUST
1832 --p_gl_return_code
1833 -- Return value from the call to GL funds checker
1834 -- (obtained in the execute_gl_call procedure)
1835 --Testing:
1836 --
1837 --End of Comments
1838 -------------------------------------------------------------------------------
1839 PROCEDURE update_document_encumbrance(
1840 p_doc_type IN VARCHAR2
1841 , p_doc_subtype IN VARCHAR2
1842 , p_action IN VARCHAR2
1843 , p_gl_return_code IN VARCHAR2
1844 ) IS
1845
1846 l_api_name CONSTANT varchar2(40) := 'UPDATE_DOCUMENT_ENCUMBRANCE';
1847 l_log_head CONSTANT varchar2(100) := g_log_head || l_api_name;
1848 l_progress VARCHAR2(3);
1849 l_return_code BOOLEAN;
1850
1851 BEGIN
1852
1853 l_progress := '000';
1854
1855 IF g_debug_stmt THEN
1856 PO_DEBUG.debug_begin(l_log_head);
1857 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_type',p_doc_type);
1858 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_subtype',p_doc_subtype);
1859 PO_DEBUG.debug_var(l_log_head,l_progress,'p_action',p_action);
1860 PO_DEBUG.debug_var(l_log_head,l_progress,'p_gl_return_code',p_gl_return_code);
1861 END IF;
1862
1863 l_progress := '010';
1864
1865 IF p_gl_return_code IN ('S','A','P') THEN
1866
1867 -- THE ENCUMBRANCE ACTION WAS AT LEAST PARTIALLY SUCCESSFUL
1868 update_successful_rows(
1869 p_doc_type => p_doc_type
1870 , p_doc_subtype => p_doc_subtype
1871 , p_action => p_action
1872 , p_gl_return_code => p_gl_return_code
1873 );
1874
1875 END IF; -- gl_return_code is S, A or P
1876
1877 l_progress := '030';
1878
1879 IF p_gl_return_code IN ('P','T','F') THEN
1880
1881 -- THE ENCUMBRANCE ACTION WAS NOT FULLY SUCCESSFUL
1882 update_failed_rows(
1883 p_doc_type => p_doc_type
1884 , p_action => p_action
1885 );
1886
1887 END IF; -- gl_return_code is P, F or T
1888
1889 l_progress := '040';
1890
1891 IF g_debug_stmt THEN
1892 PO_DEBUG.debug_end(l_log_head);
1893 END IF;
1894
1895 EXCEPTION
1896 WHEN OTHERS THEN
1897 --add message to the stack and log a debug msg if necessary
1898 po_message_s.sql_error(g_pkg_name, l_api_name, l_progress, SQLCODE, SQLERRM);
1899 fnd_msg_pub.add;
1900 RAISE;
1901
1902 END update_document_encumbrance;
1903
1904
1905 -------------------------------------------------------------------------------
1906 --Start of Comments
1907 --Name: update_successful_rows
1908 --Pre-reqs:
1909 -- PO_ENCUMBRANCE_GT has all necessary information populated after
1910 -- making a call to the GL Funds Checker
1911 --Modifies:
1912 -- PO_REQ_DISTRIBUTIONS, PO_DISTRIBUTIONS
1913 --Locks:
1914 -- None.
1915 --Function:
1916 -- This procedure updates the accounting information on the Req/PO
1917 -- distributions involved in a successful encumbrance transaction.
1918 -- This procedure is only called for actual encumbrance actions (Pending mode)
1919 -- but NOT Funds Check (Checking mode)
1920 -- The encumbered flags are not modified for the Adjust Action
1921 --Parameters:
1922 --IN:
1923 --p_doc_type
1924 -- Differentiates between the main doc being a REQUISITION, AGREEMENT,
1925 -- PURCHASE ORDER, or RELEASE which is used to identify the tables to look at
1926 -- (PO vs. Req) and the join conditions
1927 --p_doc_subtype
1928 -- Differentiates between the possible subtypes of the main document
1929 -- REQUISITION: NULL
1930 -- PURCHASE ORDER: STANDARD, PLANNED
1931 -- AGREEMENT: CONTRACT, BLANKET
1932 -- RELEASE: SCHEDULED, BLANKET
1933 --p_action
1934 -- Encumbrance action requested on the main document:
1935 -- Valid values: g_action_<> pkg vars
1936 --p_gl_return_code
1937 -- Return value from the call to GL funds checker
1938 -- (obtained in the execute_gl_call procedure)
1939 --Testing:
1940 --
1941 --End of Comments
1942 -------------------------------------------------------------------------------
1943 PROCEDURE update_successful_rows(
1944 p_doc_type IN VARCHAR2
1945 , p_doc_subtype IN VARCHAR2
1946 , p_action IN VARCHAR2
1947 , p_gl_return_code IN VARCHAR2
1948 ) IS
1949
1950 l_api_name CONSTANT varchar2(40) := 'UPDATE_SUCCESSFUL_ROWS';
1951 l_log_head CONSTANT VARCHAR2(100) := g_log_head || l_api_name;
1952 l_progress VARCHAR2(3);
1953
1954 l_flip_enc_flag varchar2(1);
1955 l_main_doc_enc_flag_success varchar2(1);
1956 l_backing_req_enc_flag_success varchar2(1);
1957
1958 l_debug_count NUMBER;
1959 l_return_status VARCHAR2(1);
1960 l_msg_count NUMBER;
1961 l_msg_data VARCHAR2(2000);
1962
1963 BEGIN
1964
1965 l_progress := '000';
1966
1967 IF g_debug_stmt THEN
1968 PO_DEBUG.debug_begin(l_log_head);
1969 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_type',p_doc_type);
1970 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_subtype',p_doc_subtype);
1971 PO_DEBUG.debug_var(l_log_head,l_progress,'p_action',p_action);
1972 PO_DEBUG.debug_var(l_log_head,l_progress,'p_gl_return_code',p_gl_return_code);
1973 END IF;
1974
1975 l_progress := '010';
1976
1977 -- Note: we only update the encumbered_flag on backing Reqs, but not other
1978 -- backing documents i.e. PAs, PPO. An explicit encumbrance action on the
1979 -- PA/PPO is required for any encumbered_flag change on that document.
1980 -- For Adjust action, the encumbered_flag is not modified.
1981
1982 IF p_action IN (g_action_RESERVE,
1983 g_action_UNDO_FINAL_CLOSE) THEN --bug 3414955
1984
1985 l_flip_enc_flag := 'Y';
1986 l_main_doc_enc_flag_success := 'Y';
1987 l_backing_req_enc_flag_success := 'N';
1988
1989 ELSIF p_action in (g_action_UNRESERVE
1990 , g_action_REJECT
1991 , g_action_RETURN
1992 , g_action_FINAL_CLOSE
1993 , g_action_CANCEL) THEN
1994
1995 l_flip_enc_flag := 'Y';
1996 l_main_doc_enc_flag_success := 'N';
1997 l_backing_req_enc_flag_success := 'Y';
1998
1999 ELSIF p_action in (g_action_ADJUST
2000 , g_action_INVOICE_CANCEL
2001 , g_action_CR_MEMO_CANCEL) THEN
2002
2003 --Do not update the main or backing doc's encumbered_flag
2004 l_flip_enc_flag := 'N';
2005
2006 -- Bug 3537764: Set flags for req split action, previously left out
2007 -- Setting l_flip_enc_flag to 'Y' does not change the previously
2008 -- existing logic for setting the encumbered flag, but now
2009 -- the changes to req. distribution encumbered flags will be rolled up
2010 ELSIF p_action in (g_action_REQ_SPLIT) THEN
2011
2012 l_flip_enc_flag := 'Y';
2013
2014 END IF;
2015
2016 IF g_debug_stmt THEN
2017 PO_DEBUG.debug_var(l_log_head,l_progress,'l_flip_enc_flag',l_flip_enc_flag);
2018 PO_DEBUG.debug_var(l_log_head,l_progress,'l_main_doc_enc_flag',
2019 l_main_doc_enc_flag_success);
2020 PO_DEBUG.debug_var(l_log_head,l_progress,'l_backing_req_enc_flag',
2021 l_backing_req_enc_flag_success);
2022 END IF;
2023
2024 --NOTE: The Temp Table field encumbered_amount_change is used throughout these
2025 -- update calculations. The code for this has been merged with the update in
2026 -- copy_detailed_gl_results.
2027
2028 l_progress := '020';
2029 IF g_debug_stmt THEN
2030 PO_DEBUG.debug_stmt(l_log_head,l_progress,
2031 'Updated encumbered_amount_change');
2032 PO_DEBUG.debug_table(l_log_head, l_progress, 'PO_ENCUMBRANCE_GT',
2033 PO_DEBUG.g_all_rows,
2034 po_tbl_varchar30('encumbered_flag',
2035 'encumbered_amount_change')
2036 );
2037 END IF;
2038
2039
2040 IF p_doc_type = g_doc_type_REQUISITION THEN
2041
2042 --SQL What: Update the encumbered_flag and encumbered_amount on the Req
2043 -- distribution, if the Req was the main document.
2044 --SQL Where: Only updates those Requisition distributions where the
2045 -- distribution row was sent to and successful in the GL packet
2046 --SQL Why: If the distribution transaction succeeded, the distribution table
2047 -- fields must be updated to reflect the effect of the transaction
2048
2049 UPDATE PO_REQ_DISTRIBUTIONS_ALL PRD
2050 SET
2051 (
2052 PRD.encumbered_flag
2053 , PRD.encumbered_amount
2054 )
2055 =
2056 (
2057 SELECT
2058 --encumbered flag:
2059 DECODE( l_flip_enc_flag
2060 , 'N', PRD.encumbered_flag -- don't flip flag
2061 , l_main_doc_enc_flag_success),
2062
2063 --encumbered amt:
2064 nvl(PRD.encumbered_amount, 0) +
2065 SUM (decode(TEMP.update_encumbered_amount_flag,
2066 'Y', TEMP.encumbered_amount_change,
2067 0)
2068 )
2069 FROM PO_ENCUMBRANCE_GT TEMP
2070 WHERE TEMP.distribution_id = PRD.distribution_id
2071 AND TEMP.distribution_type = g_dist_type_REQUISITION
2072 GROUP BY TEMP.distribution_id
2073 ), /* Updating these cols also for bug#13930578 */
2074 PRD.last_update_date = sysdate,
2075 PRD.last_updated_by = fnd_global.user_id,
2076 PRD.last_update_login = fnd_global.login_id
2077 WHERE PRD.distribution_id in
2078 (
2079 SELECT MAIN_REQ.distribution_id
2080 FROM PO_ENCUMBRANCE_GT MAIN_REQ
2081 WHERE MAIN_REQ.distribution_type = g_dist_type_REQUISITION -- doc is Req
2082 AND MAIN_REQ.origin_sequence_num IS NULL -- doc is main doc
2083 AND MAIN_REQ.gl_status_code = 'A'
2084 AND MAIN_REQ.send_to_gl_flag = 'Y' --bug 3568512: use new column
2085 );
2086
2087 --funds liquidated: Line Type Changes: Liquidate iProcurement Requisition
2088 UPDATE PO_REQ_DISTRIBUTIONS_ALL PRD
2089 SET PRD.funds_liquidated
2090 =
2091 (
2092 SELECT
2093 Nvl(PRD.funds_liquidated,0) + DECODE(p_action, g_action_UNRESERVE,
2094 Decode(TEMP.CLM_DOC_FLAG,'Y',TEMP.CHANGE_IN_FUNDED_VALUE),0)
2095 FROM PO_ENCUMBRANCE_GT TEMP
2096 WHERE TEMP.distribution_id = PRD.distribution_id
2097 AND TEMP.distribution_type = g_dist_type_REQUISITION
2098 )
2099 WHERE PRD.distribution_id in
2100 (
2101 SELECT MAIN_REQ.distribution_id
2102 FROM PO_ENCUMBRANCE_GT MAIN_REQ
2103 WHERE MAIN_REQ.distribution_type = g_dist_type_REQUISITION -- doc is Req
2104 AND MAIN_REQ.origin_sequence_num IS NULL -- doc is main doc
2105 AND MAIN_REQ.gl_status_code = 'A'
2106 AND MAIN_REQ.send_to_gl_flag = 'Y' --bug 3568512: use new column
2107 );
2108
2109 l_progress := '030';
2110 IF g_debug_stmt THEN
2111 l_debug_count := SQL%ROWCOUNT;
2112 PO_DEBUG.debug_stmt(l_log_head,l_progress,
2113 'Updated Main Req dists: ' || l_debug_count);
2114 END IF;
2115
2116 --bug 3537764: added following code to handle flags for Req Split
2117 IF (p_action = g_action_REQ_SPLIT) THEN
2118
2119 --SQL What: Update the encumbered_flag and prevent_encumbrance_flag
2120 -- of the old and new Req distributions from a Req Split
2121 --SQL Where: Only updates those Requisition distributions where the
2122 -- distribution row was sent to and successful in GL packet
2123 --SQL Why: In Req Split, encumbrance is moved from old lines to
2124 -- new lines, so status of the enc flags are flipped to
2125 -- reflect that. Also, once a Req line is split (old lines),
2126 -- you can no longer act on it, so we set the prevent-enc
2127 -- flag on these lines -- as a marker to ignore them for
2128 -- future actions
2129
2130 UPDATE PO_REQ_DISTRIBUTIONS_ALL PRD
2131 SET (
2132 PRD.encumbered_flag
2133 , PRD.prevent_encumbrance_flag
2134 )
2135 =
2136 ( SELECT
2137 --encumbered_flag:
2138 --unreserve old rows, reserve new rows
2139 DECODE( TEMP.adjustment_status
2140 , g_adjustment_status_OLD, 'N'
2141 , g_adjustment_status_NEW, 'Y'
2142 , TEMP.encumbered_flag
2143 ),
2144
2145 --prevent_encumbrance_flag:
2146 --old rows are marked prevent-enc for future actions
2147 DECODE( TEMP.adjustment_status
2148 , g_adjustment_status_OLD, 'Y'
2149 , TEMP.prevent_encumbrance_flag
2150 )
2151 FROM PO_ENCUMBRANCE_GT TEMP
2152 WHERE TEMP.distribution_id = PRD.distribution_id
2153 AND TEMP.distribution_type = g_dist_type_REQUISITION
2154 ), /* Updating these cols also for bug#13930578 */
2155 PRD.last_update_date = sysdate,
2156 PRD.last_updated_by = fnd_global.user_id,
2157 PRD.last_update_login = fnd_global.login_id
2158 WHERE PRD.distribution_id IN
2159 (
2160 SELECT MAIN_REQ.distribution_id
2161 FROM PO_ENCUMBRANCE_GT MAIN_REQ
2162 WHERE MAIN_REQ.distribution_type = g_dist_type_REQUISITION
2163 AND MAIN_REQ.origin_sequence_num is NULL
2164 AND MAIN_REQ.gl_status_code = 'A'
2165 );
2166
2167 l_progress := '032';
2168 IF g_debug_stmt THEN
2169 l_debug_count := SQL%ROWCOUNT;
2170 PO_DEBUG.debug_stmt(l_log_head,l_progress,
2171 'Updated Req Split dists: ' || l_debug_count);
2172 END IF;
2173 END IF; -- if action is req split
2174
2175 ELSE -- p_doctype is Not A Req
2176
2177 --SQL What: Update the encumbered_flag and encumbered_amount on PA/PO/Rel
2178 -- distribution, if the PA/PO/Rel was the main document.
2179 --SQL Where: Only updates those distributions where the distribution
2180 -- row was sent to and successful in the GL packet
2181 --SQL Why: If the distribution transaction succeeded, the distribution table
2182 -- fields must be updated to reflect the effect of the transaction
2183
2184 -- <13503748: Edit without Unreserve ER:>
2185 -- Null out the amount_changed_flag for the successful encumbrance
2186 -- transactions
2187
2188
2189 UPDATE PO_DISTRIBUTIONS_ALL POD
2190 SET
2191 (
2192 POD.encumbered_flag,
2193 POD.encumbered_amount,
2194 POD.amount_changed_flag
2195 )
2196 =
2197 (
2198 SELECT
2199 --encumbered flag:
2200 DECODE( l_flip_enc_flag
2201 , 'N', POD.encumbered_flag -- don't flip flag
2202 , l_main_doc_enc_flag_success),
2203
2204 --encumbered amt:
2205 nvl(POD.encumbered_amount, 0) +
2206 SUM (decode(TEMP.update_encumbered_amount_flag,
2207 'Y',TEMP.encumbered_amount_change,
2208 0)
2209 ),
2210 NULL -- <13503748>
2211 FROM PO_ENCUMBRANCE_GT TEMP
2212 WHERE TEMP.distribution_id = POD.po_distribution_id
2213 AND TEMP.distribution_type <> g_dist_type_REQUISITION
2214 GROUP BY TEMP.distribution_id
2215 ), /* Updating these cols also for bug#13930578 */
2216 POD.last_update_date = sysdate,
2217 POD.last_updated_by = fnd_global.user_id,
2218 POD.last_update_login = fnd_global.login_id
2219 WHERE POD.po_distribution_id in
2220 (
2221 SELECT MAIN_PURCH.distribution_id
2222 FROM PO_ENCUMBRANCE_GT MAIN_PURCH
2223 WHERE MAIN_PURCH.distribution_type <> g_dist_type_REQUISITION
2224 -- doc is PO/PA/Release
2225 AND MAIN_PURCH.origin_sequence_num IS NULL -- doc is main doc
2226 AND MAIN_PURCH.gl_status_code = 'A'
2227 AND MAIN_PURCH.send_to_gl_flag = 'Y' --bug 3568512: use new column
2228 );
2229
2230 l_progress := '040';
2231 IF g_debug_stmt THEN
2232 l_debug_count := SQL%ROWCOUNT;
2233 PO_DEBUG.debug_stmt(l_log_head,l_progress,
2234 'Updated Main PO/Rel dists: ' || l_debug_count);
2235 END IF;
2236
2237 l_progress := '045';
2238
2239 -- Since the main doc lived in PO tables, it could have backing documents
2240 -- Now update the encumbrance fields on the backing requisitions
2241
2242 IF p_action NOT IN (g_action_FINAL_CLOSE,
2243 g_action_UNDO_FINAL_CLOSE, --bug 3414955
2244 g_action_ADJUST) THEN
2245
2246 -- no backing Requisition updates for FC, Undo FC and Adjust
2247
2248 --SQL What: Update the encumbered_flag and encumbered_amount on the Req
2249 -- distribution, if the Req is a backing document.
2250 --SQL Where: Only updates those Requisition distributions where the
2251 -- distribution row was sent to and successful in the GL packet
2252 --SQL Why: If the distribution transaction succeeded, the distribution
2253 -- table fields must be updated to reflect the effect of the
2254 -- transaction
2255
2256 UPDATE PO_REQ_DISTRIBUTIONS_ALL PRD
2257 SET
2258 (
2259 PRD.encumbered_flag,
2260 PRD.encumbered_amount
2261 )
2262 =
2263 (
2264 SELECT
2265 --encumbered flag:
2266 MAX(DECODE( l_flip_enc_flag
2267 , 'N', PRD.encumbered_flag --don't flip flag
2268 , l_backing_req_enc_flag_success)),
2269
2270 --encumbered amt:
2271 nvl(PRD.encumbered_amount, 0) +
2272 SUM(decode(TEMP.update_encumbered_amount_flag
2273 , 'Y',TEMP.encumbered_amount_change
2274 , 0))
2275 FROM PO_ENCUMBRANCE_GT TEMP
2276 WHERE TEMP.distribution_id = PRD.distribution_id
2277 AND TEMP.distribution_type = g_dist_type_REQUISITION
2278 GROUP BY TEMP.distribution_id
2279 --<Complex Work R12>: added MAX, SUM and GROUP BY operators
2280 ), /* Updating these cols also for bug#13930578 */
2281 PRD.last_update_date = sysdate,
2282 PRD.last_updated_by = fnd_global.user_id,
2283 PRD.last_update_login = fnd_global.login_id
2284 WHERE PRD.distribution_id in
2285 (
2286 SELECT BACKING_REQ.distribution_id
2287 FROM PO_ENCUMBRANCE_GT BACKING_REQ
2288 WHERE BACKING_REQ.distribution_type = g_dist_type_REQUISITION
2289 AND BACKING_REQ.origin_sequence_num IS NOT NULL
2290 AND BACKING_REQ.gl_status_code = 'A'
2291 AND BACKING_REQ.send_to_gl_flag = 'Y' --bug 3568512: use new column
2292 );
2293
2294 l_progress := '050';
2295 IF g_debug_stmt THEN
2296 l_debug_count := SQL%ROWCOUNT;
2297 PO_DEBUG.debug_stmt(l_log_head,l_progress,
2298 'Updated Backing Req dists: ' || l_debug_count);
2299 END IF;
2300
2301 END IF; -- backing Reqs
2302
2303 -- Since the main doc lived in PO tables, it could have backing documents
2304 -- Now update the encumbrance fields on the backing PPO/PA
2305 -- For FC, Undo FC the backing docs are not updated
2306
2307 IF p_action NOT IN (g_action_FINAL_CLOSE,
2308 g_action_UNDO_FINAL_CLOSE) THEN --bug 3414955
2309
2310 --SQL What: Update the encumbered_amount and unencumbered_amount on
2311 -- backing PAs and PPOs.
2312 -- Note: encumbered_flag is not updated on backing PA/PPO.
2313 -- The flag on these docs is only affected if they are the
2314 -- main doc in an action.
2315 --SQL Where: Only updates those backing PA/PPO distributions where the
2316 -- distribution row was sent to and successful in the GL packet
2317 --SQL Why: If the distribution transaction succeeded, the distribution
2318 -- table fields must be updated to reflect the effect of the
2319 -- transaction
2320
2321 UPDATE PO_DISTRIBUTIONS_ALL POD
2322 SET
2323 (
2324 POD.encumbered_amount,
2325 POD.unencumbered_amount
2326 )
2327 =
2328 (
2329 SELECT
2330 nvl(POD.encumbered_amount, 0) +
2331 SUM(decode(CURRENT_DOC.update_encumbered_amount_flag,
2332 'Y',CURRENT_DOC.encumbered_amount_change,
2333 0)
2334 ),
2335 nvl(POD.unencumbered_amount, 0) -
2336 SUM(CURRENT_DOC.encumbered_amount_change)
2337 FROM PO_ENCUMBRANCE_GT CURRENT_DOC
2338 WHERE CURRENT_DOC.distribution_id = POD.po_distribution_id
2339 AND CURRENT_DOC.distribution_type IN
2340 (g_dist_type_AGREEMENT, g_dist_type_PLANNED)
2341 GROUP BY CURRENT_DOC.distribution_id
2342 )
2343 WHERE POD.po_distribution_id in
2344 (
2345 SELECT BACKING_PURCH.distribution_id
2346 FROM PO_ENCUMBRANCE_GT BACKING_PURCH
2347 WHERE BACKING_PURCH.distribution_type IN
2348 (g_dist_type_AGREEMENT, g_dist_type_PLANNED) -- PA/PPO
2349 AND BACKING_PURCH.origin_sequence_num IS NOT NULL -- backing doc
2350 AND (BACKING_PURCH.gl_status_code = 'A'
2351 OR (BACKING_PURCH.gl_status_code IS NULL
2352 AND BACKING_PURCH.prevent_encumbrance_flag = 'N')
2353 --bug 3568512: do not filter on send_to_gl_flag = 'Y' because
2354 --even if backing BPA/GA was not sent to GL, its
2355 --unencumbered_amount needs to be updated. for these rows,
2356 --we do not update encumbered_amount unless the row was sent
2357 --to GL; the setting of update_enc_amt_flag checks this
2358 )
2359 );
2360
2361 l_progress := '060';
2362 IF g_debug_stmt THEN
2363 l_debug_count := SQL%ROWCOUNT;
2364 PO_DEBUG.debug_stmt(l_log_head,l_progress,
2365 'Updated Backing PA/PPO dists: ' || l_debug_count);
2366 END IF;
2367
2368 l_progress := '065';
2369
2370 -- Update encumbered and unencumbered qty on backing PPOs
2371 -- Note: Adjust is not supported for PPO/Scheduled Release
2372
2373 IF (p_doc_subtype = g_doc_subtype_SCHEDULED AND
2374 p_action <> g_action_ADJUST) THEN
2375
2376 --SQL What: Update the unencumbered_quantity on the backing PPO.
2377 -- Note: this field is only maintained for PPOs, no
2378 -- other backing docs
2379 --SQL Where: Only updates those backing PPO distributions where the
2380 -- distribution row was sent to and successful in the GL
2381 -- packet
2382 --SQL Why: If the distribution transaction succeeded, the
2383 -- distribution table fields must be updated to reflect
2384 -- the effect of the transaction
2385
2386 -- note: the qty_open was calculated for SR, but also copied onto the
2387 -- PPO dist
2388
2389 -- Bug 3292870: Modified the query to make it compatible with 8i DB
2390 -- Changes: Moved all the GREATEST/NVL/DECODE logic to be inside
2391 -- the select statement.
2392
2393 UPDATE PO_DISTRIBUTIONS_ALL POD
2394 SET
2395 POD.unencumbered_quantity =
2396 (SELECT
2397 GREATEST
2398 ( 0,
2399 nvl(POD.unencumbered_quantity, 0)
2400 +
2401 (DECODE( p_action
2402 -- if Reserving an SR, add to unenc qty
2403 , g_action_RESERVE, 1
2404 -- if cancelling credit memo, add to unenc qty
2405 , g_action_CR_MEMO_CANCEL, 1
2406 -- all other actions on SR reduce PPO unenc qty
2407 , -1
2408 )
2409 *
2410 SUM (PPO_DISTS.qty_open))
2411 )
2412 FROM PO_ENCUMBRANCE_GT PPO_DISTS
2413 WHERE PPO_DISTS.distribution_id = POD.po_distribution_id
2414 AND PPO_DISTS.distribution_type = g_dist_type_PLANNED
2415 GROUP BY PPO_DISTS.distribution_id
2416 )
2417 WHERE POD.po_distribution_id IN
2418 (
2419 SELECT MAIN_SR.source_distribution_id -- get backing PPO's id
2420 FROM PO_ENCUMBRANCE_GT MAIN_SR
2421 WHERE MAIN_SR.distribution_type = g_dist_type_SCHEDULED
2422 AND MAIN_SR.origin_sequence_num IS NULL
2423 -- the main doc is a Scheduled Release
2424 AND MAIN_SR.gl_status_code = 'A'
2425 AND MAIN_SR.send_to_gl_flag = 'Y' --bug 3568512: use new column
2426 );
2427
2428 l_progress := '070';
2429
2430 IF g_debug_stmt THEN
2431 l_debug_count := SQL%ROWCOUNT;
2432 PO_DEBUG.debug_stmt(l_log_head,l_progress,
2433 'Updated Backing PPO dists: ' || l_debug_count);
2434 END IF;
2435
2436 END IF; -- if main doc is a Scheduled Release
2437
2438 END IF; -- backing PPO/PA and if action is not Final Close
2439
2440 END IF; -- if/else for p_doc_type of Req or not
2441
2442
2443 IF (p_action = g_action_RESERVE) AND
2444 p_doc_type IN (g_doc_type_PO, g_doc_type_RELEASE)
2445 AND (p_doc_subtype <> g_doc_subtype_SCHEDULED) -- Bug 5035240
2446 THEN
2447
2448 -- In addition to the encumbered flag/amts/quantity, there are 2 special
2449 -- cases in which we need to update the prevent_encumbrance_flag of a
2450 -- backing Requisition of an Execution Document (PO/Release) as part of
2451 -- the post-processing
2452
2453 -- Case 1: Double backing encumbrance
2454 -- Both the backing Req and backing PA were encumbered. After
2455 -- Reserve of the Execution Document, we set the prevent flag on
2456 -- the Req to Y to ensure that further backing transactions only
2457 -- look at the PA
2458 -- Case 2: No backing encumbrance
2459 -- Neither the backing Req and backing PA were encumbered. After
2460 -- Reserve of the Execution Document, we set the prevent flag on
2461 -- the Req to N to ensure that further backing transactions actually
2462 -- look at the Req
2463
2464 --SQL What: Flip the prevent encumbrance flag on the backing Requisition
2465 -- for the 2 special cases described above. This SQL will also
2466 -- update non-special case Reqs, but in this case we should be
2467 -- setting the flag to N, which is the value it should already
2468 -- have had.
2469 --SQL Where: Affects Backing Req distributions of Std POs and Blanket
2470 -- Releases if the main doc distribution was sent to and
2471 -- successful in GL.
2472 -- This should also be done for the rare case of PPOs
2473 -- whose backing Req is source to an encumbered BPA/GA.
2474 --SQL Why: For the special cases, we want to reset the Requisition prevent
2475 -- flag so that going forward, we no longer have the either
2476 -- Double or No backing encumbrance case.
2477 --Bug 5348161: Added MAX aggregator to the logic for updating the Req dist's
2478 --prevent_enc_flag. For CWPOs, multiple PO dists point to the same backing Req
2479 --dist, so the EXEC_D.req_distribution_id = PRD.distribution_id returns multiple
2480 --rows so an aggregator is needed to avoid multiple-row-subquery error.
2481
2482 /* Bug : 13984592 : Modifying the update statement to update the
2483 prevent encumbrance flag of backing req to 'Y' only when backing GBPA is encumbered.
2484 The case 2: where there is no backing GBPA , the backing Requistion prevent encumbrance
2485 flag is not updated to 'Y' or 'N'. This is required so that if the backing req flag is
2486 explictly updated to 'Y' the code will not flip to 'N'.
2487
2488 UPDATE PO_REQ_DISTRIBUTIONS_ALL PRD
2489 SET PRD.prevent_encumbrance_flag
2490 =
2491 (
2492 SELECT
2493 DECODE(
2494 MAX(
2495 DECODE( EXEC_D.agreement_dist_id -- only present if main doc is encumbered
2496 , NULL, 1 -- no backing PA or backing PA not encumberable
2497 , 2 -- this PO dist has backing encumbered PA
2498 )
2499 )
2500 , 1, 'N' --if max is 1, then no backing Enc PA, so prevent_enc_flag <= N
2501 , 'Y' -- if max is 2, then there is backing Enc PA, so prevent_enc_flag <= Y
2502 )
2503 FROM PO_ENCUMBRANCE_GT EXEC_D
2504 WHERE EXEC_D.req_distribution_id = PRD.distribution_id
2505 )
2506 WHERE PRD.distribution_id IN
2507 (
2508 SELECT EXEC_DOC.req_distribution_id
2509 FROM PO_ENCUMBRANCE_GT EXEC_DOC
2510 WHERE EXEC_DOC.distribution_type
2511 IN (g_dist_type_STANDARD, g_dist_type_BLANKET, g_dist_type_PLANNED)
2512 AND EXEC_DOC.req_distribution_id IS NOT NULL
2513 AND EXEC_DOC.gl_status_code = 'A'
2514 AND EXEC_DOC.send_to_gl_flag = 'Y' --bug 3568512
2515 ); */
2516
2517 update PO_REQ_DISTRIBUTIONS_ALL PRD
2518 set PRD.prevent_encumbrance_flag = 'Y'
2519 where PRD.distribution_id IN
2520 (
2521 SELECT EXEC_DOC.req_distribution_id
2522 FROM PO_ENCUMBRANCE_GT EXEC_DOC
2523 WHERE EXEC_DOC.distribution_type
2524 IN (g_dist_type_STANDARD, g_dist_type_BLANKET, g_dist_type_PLANNED)
2525 AND EXEC_DOC.req_distribution_id IS NOT NULL
2526 AND EXEC_DOC.gl_status_code = 'A'
2527 AND EXEC_DOC.send_to_gl_flag = 'Y' --bug 3568512
2528 AND EXEC_DOC.agreement_dist_id IS NOT NULL
2529 ) ;
2530 ---END Bug 13984592
2531
2532 l_progress := '075';
2533
2534 IF g_debug_stmt THEN
2535 l_debug_count := SQL%ROWCOUNT;
2536 PO_DEBUG.debug_stmt(l_log_head,l_progress,
2537 'Flipped prevent enc flag: ' || l_debug_count);
2538 END IF;
2539
2540 END IF; -- RESERVE action and p_doc_type is PO or Release
2541
2542
2543 /* <<CLM Partial Funding Code Changes>> */
2544 -- Update the Encumbrance Flags on CLM PO/REQs To sync up them with Encumbered Amounts
2545 BEGIN
2546 IF (po_partial_funding_pkg.is_CLM_Enabled = 'Y'
2547 AND p_action = g_action_RESERVE) THEN
2548
2549 IF (p_doc_type = g_doc_type_PO
2550 AND p_doc_subtype = g_doc_subtype_STANDARD) THEN
2551
2552 l_progress := '076';
2553 IF g_debug_stmt THEN
2554 PO_DEBUG.debug_stmt(l_log_head,l_progress,'CLM is Installed and Reserve Action is on a Standard PO');
2555 END IF;
2556 -- Update the Encumbered Flag for the Main Document and Set the Change In funded Value to Zero for successful rows
2557 --Bug 15871378 commented out the update on encumbered_flag = 'N' when encumbered_amount = 0
2558 UPDATE PO_DISTRIBUTIONS_ALL POD
2559 SET /*POD.ENCUMBERED_FLAG = DECODE(GREATEST(NVL(POD.ENCUMBERED_AMOUNT,0), 0),
2560 0,'N',
2561 'Y'),*/
2562 POD.CHANGE_IN_FUNDED_VALUE = 0
2563 WHERE POD.PO_DISTRIBUTION_ID IN
2564 ( SELECT MAIN_PURCH.DISTRIBUTION_ID
2565 FROM PO_ENCUMBRANCE_GT MAIN_PURCH
2566 WHERE MAIN_PURCH.DISTRIBUTION_TYPE <> G_DIST_TYPE_REQUISITION
2567 -- DOC IS PO/PA/RELEASE
2568 AND MAIN_PURCH.ORIGIN_SEQUENCE_NUM IS NULL -- DOC IS MAIN DOC
2569 AND MAIN_PURCH.GL_STATUS_CODE = 'A'
2570 AND MAIN_PURCH.CLM_DOC_FLAG = 'Y'
2571 AND MAIN_PURCH.SEND_TO_GL_FLAG = 'Y'
2572 );
2573
2574 l_progress := '077';
2575 IF g_debug_stmt THEN
2576 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Updating the Enc Flag and UnEnc Amt for backing CLM Requisitions.');
2577 END IF;
2578 -- Also set the Encumbered Flag and the unencumbered Amount for clmBacking Reqs
2579 -- Need to set Encumbered Flag to N, if Enc Amt is Zero. This is required to pick only the Backing REQs with Act.Enc for CLM POs
2580 --Bug 15871378 commented out the update on encumbered_flag = 'N' when encumbered_amount = 0
2581 UPDATE PO_REQ_DISTRIBUTIONS_ALL PRD
2582 SET /*PRD.Encumbered_Flag = DECODE(GREATEST(NVL(PRD.Encumbered_Amount,0), 0),
2583 0,'N',
2584 'Y')
2585 ,*/
2586 PRD.Unencumbered_Amount =
2587 ( SELECT MAX(NVL(PRD.Unencumbered_Amount,0))
2588 FROM PO_ENCUMBRANCE_GT TEMP
2589 WHERE TEMP.distribution_id = PRD.distribution_id
2590 AND TEMP.CLM_DOC_FLAG = 'Y'
2591 AND TEMP.distribution_type = g_dist_type_REQUISITION
2592 GROUP BY TEMP.distribution_id
2593 )
2594 WHERE PRD.distribution_id IN
2595 ( SELECT BACKING_REQ.distribution_id
2596 FROM PO_ENCUMBRANCE_GT BACKING_REQ
2597 WHERE BACKING_REQ.distribution_type = g_dist_type_REQUISITION
2598 AND BACKING_REQ.origin_sequence_num IS NOT NULL
2599 AND BACKING_REQ.CLM_DOC_FLAG = 'Y'
2600 AND BACKING_REQ.gl_status_code = 'A'
2601 AND BACKING_REQ.send_to_gl_flag = 'Y'
2602 );
2603
2604 ELSIF p_doc_type = g_doc_type_REQUISITION THEN
2605 l_progress := '078';
2606 IF g_debug_stmt THEN
2607 PO_DEBUG.debug_stmt(l_log_head,l_progress,'CLM is Installed and Reserve Action is on REQ');
2608 END IF;
2609 --Bug 15871378 commented out the update on encumbered_flag = 'N' when encumbered_amount = 0
2610 UPDATE PO_REQ_DISTRIBUTIONS_ALL PRD
2611 SET /* PRD.ENCUMBERED_FLAG = DECODE(GREATEST(NVL(PRD.ENCUMBERED_AMOUNT,0), 0),
2612 0,'N',
2613 'Y'),*/
2614 PRD.CHANGE_IN_FUNDED_VALUE = 0
2615 WHERE PRD.DISTRIBUTION_ID IN
2616 ( SELECT MAIN_REQ.DISTRIBUTION_ID
2617 FROM PO_ENCUMBRANCE_GT MAIN_REQ
2618 WHERE MAIN_REQ.DISTRIBUTION_TYPE = G_DIST_TYPE_REQUISITION
2619 -- DOC IS PO/PA/RELEASE
2620 AND MAIN_REQ.ORIGIN_SEQUENCE_NUM IS NULL -- DOC IS MAIN DOC
2621 AND MAIN_REQ.GL_STATUS_CODE = 'A'
2622 AND MAIN_REQ.CLM_DOC_FLAG = 'Y'
2623 AND MAIN_REQ.SEND_TO_GL_FLAG = 'Y'
2624 );
2625
2626 END IF; --P_DOC_TYPE
2627 ELSE
2628 l_progress := '078';
2629 IF g_debug_stmt THEN
2630 PO_DEBUG.debug_stmt(l_log_head,l_progress,'CLM is Not Installed.');
2631 END IF;
2632 END IF; -- CLM INSTALLED CHECK
2633 EXCEPTION
2634 WHEN Others THEN
2635 --add message to the stack and log a debug msg if necessary
2636 po_message_s.sql_error(g_pkg_name, l_api_name, l_progress, SQLCODE, SQLERRM);
2637 fnd_msg_pub.add;
2638 RAISE;
2639 END;
2640 /* <<CLM Partial Funding Code Changes>> */
2641
2642
2643 -- Now the updation of po_distributions/po_req_distributions for all the
2644 -- approved GL_BC_PACKETS records is done. Just rollup the encumbered_flag
2645 -- to the PO line locations and Requisition lines
2646
2647 If l_flip_enc_flag = 'Y' Then
2648
2649 l_progress := '079';
2650
2651 -- Bug 3537764: Passed p_action to rollup_encumbrance_changes
2652 rollup_encumbrance_changes(p_action => p_action);
2653
2654 End If;
2655
2656 l_progress := '080';
2657
2658 IF g_debug_stmt THEN
2659 PO_DEBUG.debug_end(l_log_head);
2660 END IF;
2661
2662
2663 EXCEPTION
2664 WHEN OTHERS THEN
2665 --add message to the stack and log a debug msg if necessary
2666 po_message_s.sql_error(g_pkg_name, l_api_name, l_progress, SQLCODE, SQLERRM);
2667 fnd_msg_pub.add;
2668 RAISE;
2669
2670 END update_successful_rows;
2671
2672 -------------------------------------------------------------------------------
2673 --Start of Comments
2674 --Name: update_failed_rows
2675 --Pre-reqs:
2676 -- PO_ENCUMBRANCE_GT has all necessary information populated after
2677 -- making a call to the GL Funds Checker
2678 --Modifies:
2679 -- PO_REQ_DISTRIBUTIONS, PO_DISTRIBUTIONS
2680 --Locks:
2681 -- None.
2682 --Function:
2683 -- This procedure updates the failed_funds_lookup_code on Req/PO distributions
2684 -- involved in a failed or partially failed encumbrance transaction. This
2685 -- procedure is only called for actual encumbrance actions (Pending mode),
2686 -- but NOT Funds Check (Checking mode)
2687 --Parameters:
2688 --IN:
2689 --p_doc_type
2690 -- Differentiates between the main doc being a REQUISITION, AGREEMENT,
2691 -- PURCHASE ORDER, or RELEASE which is used to identify the tables to look at
2692 -- (PO vs. Req) and the join conditions
2693 --p_action
2694 -- Encumbrance action requested on the main document:
2695 -- RESERVE/UNRESERVE/CANCEL/FINAL CLOSE/RETURN/REJECT/ADJUST
2696 --Testing:
2697 --
2698 --End of Comments
2699 -------------------------------------------------------------------------------
2700
2701 PROCEDURE update_failed_rows(
2702 p_doc_type IN VARCHAR2
2703 , p_action IN VARCHAR2
2704 ) IS
2705
2706 l_api_name CONSTANT varchar2(40) := 'UPDATE_FAILED_ROWS';
2707 l_log_head CONSTANT varchar2(100) := g_log_head || l_api_name;
2708 l_progress VARCHAR2(3);
2709 l_debug_count NUMBER;
2710
2711 BEGIN
2712
2713 l_progress := '000';
2714
2715 IF g_debug_stmt THEN
2716 PO_DEBUG.debug_begin(l_log_head);
2717 PO_DEBUG.debug_var(l_log_head, l_progress, 'p_doc_type', p_doc_type);
2718 PO_DEBUG.debug_var(l_log_head, l_progress, 'p_action', p_action);
2719 END IF;
2720
2721 l_progress := '010';
2722
2723 IF p_doc_type = g_doc_type_REQUISITION THEN
2724
2725 --SQL What: Update the failed_funds_lookup_code of relevant Requisition
2726 -- distributions from this transaction
2727 --SQL Where: Only updates the distributions if the distribution transaction
2728 -- failed in GL
2729
2730 UPDATE PO_REQ_DISTRIBUTIONS_ALL PRD
2731 SET PRD.failed_funds_lookup_code
2732 =
2733 (
2734 SELECT TEMP.gl_result_code
2735 FROM PO_ENCUMBRANCE_GT TEMP
2736 WHERE TEMP.distribution_id = PRD.distribution_id
2737 AND TEMP.distribution_type = g_dist_type_REQUISITION
2738 AND rownum = 1
2739 -- handles case with 2 same dist_ids in same packet (Adjust)
2740 )
2741 WHERE PRD.distribution_id in
2742 (
2743 SELECT REQ_DISTS.distribution_id
2744 FROM PO_ENCUMBRANCE_GT REQ_DISTS
2745 WHERE REQ_DISTS.distribution_type = g_dist_type_REQUISITION
2746 AND REQ_DISTS.origin_sequence_num IS NULL
2747 AND REQ_DISTS.gl_status_code = 'R'
2748 AND REQ_DISTS.prevent_encumbrance_flag = 'N'
2749 );
2750
2751 l_progress := '020';
2752 IF g_debug_stmt THEN
2753 l_debug_count := SQL%ROWCOUNT;
2754 PO_DEBUG.debug_stmt(l_log_head,l_progress,
2755 'Updated Req dists failed funds code: ' || l_debug_count);
2756 END IF;
2757
2758 ELSE -- p_doc_type is Not a Req
2759
2760 --SQL What: Update the failed_funds_lookup_code of relevant PO/PA/Release
2761 -- distributions from this transaction
2762 --SQL Where: Only updates the distributions if the distribution transaction
2763 -- failed in GL
2764
2765 UPDATE PO_DISTRIBUTIONS_ALL POD
2766 SET POD.failed_funds_lookup_code
2767 =
2768 (
2769 SELECT TEMP.gl_result_code
2770 FROM PO_ENCUMBRANCE_GT TEMP
2771 WHERE TEMP.distribution_id = POD.po_distribution_id
2772 AND TEMP.distribution_type <> g_dist_type_REQUISITION
2773 AND rownum = 1
2774 -- handles case with 2 same dist_ids in same packet (Adjust)
2775 )
2776 WHERE POD.po_distribution_id in
2777 (
2778 SELECT PO_DISTS.distribution_id
2779 FROM PO_ENCUMBRANCE_GT PO_DISTS
2780 WHERE PO_DISTS.distribution_type <> g_dist_type_REQUISITION
2781 AND PO_DISTS.origin_sequence_num IS NULL
2782 AND PO_DISTS.gl_status_code = 'R'
2783 AND PO_DISTS.prevent_encumbrance_flag = 'N'
2784 );
2785
2786 l_progress := '030';
2787 IF g_debug_stmt THEN
2788 l_debug_count := SQL%ROWCOUNT;
2789 PO_DEBUG.debug_stmt(l_log_head,l_progress,
2790 'Updated PO/Rel dists failed funds code: ' || l_debug_count);
2791 END IF;
2792
2793 END IF; -- p_doc_type check
2794
2795 l_progress := '040';
2796
2797 IF g_debug_stmt THEN
2798 PO_DEBUG.debug_end(l_log_head);
2799 END IF;
2800
2801 EXCEPTION
2802 WHEN OTHERS THEN
2803 --add message to the stack and log a debug msg if necessary
2804 po_message_s.sql_error(g_pkg_name, l_api_name, l_progress, SQLCODE, SQLERRM);
2805 fnd_msg_pub.add;
2806 RAISE;
2807
2808 END update_failed_rows;
2809
2810 -------------------------------------------------------------------------------
2811 --Start of Comments
2812 --Name: rollup_encumbrance_changes
2813 --Pre-reqs:
2814 -- An encumbrance action (excluding Funds Check) was successful or partially
2815 -- successful
2816 --Modifies:
2817 -- Updates Req lines and/or PO Shipments tables based on result of GL transaction
2818 --Locks:
2819 -- None.
2820 --Function:
2821 -- This procedure rolls up changes to the encumbered_flag from the Req/PO
2822 -- distributions to the Req line or PO shipment level.
2823 --Parameters:
2824 --IN:
2825 -- p_action: -- Bug 3537764
2826 -- Encumbrance action requested on the main document:
2827 -- Valid values: g_action_<> pkg vars
2828 --OUT:
2829 -- N/A
2830 --Testing:
2831 --
2832 --End of Comments
2833 -------------------------------------------------------------------------------
2834
2835 PROCEDURE rollup_encumbrance_changes (p_action IN VARCHAR2)
2836 IS
2837
2838 l_api_name CONSTANT varchar2(40) := 'ROLLUP_ENCUMBRANCE_CHANGES';
2839 l_log_head CONSTANT varchar2(100) := g_log_head || l_api_name;
2840 l_progress VARCHAR2(3);
2841 l_debug_count NUMBER;
2842
2843 BEGIN
2844
2845 l_progress := '000';
2846
2847 IF g_debug_stmt THEN
2848 PO_DEBUG.debug_begin(l_log_head);
2849 END IF;
2850
2851 l_progress := '010';
2852
2853 --SQL What: Update the encumbered_flag from the affected Requisition
2854 -- distributions (main or backing) up to the Requisition line
2855 --SQL Where: Only updates the distributions if the distribution transaction
2856 -- succeeded in GL
2857 --SQL Why: The Req line's encumbered_flag needs to be in synch with the
2858 -- update already done to the Req distribution's encumbered_flag
2859
2860 UPDATE PO_REQUISITION_LINES_ALL PRL
2861 SET encumbered_flag
2862 =
2863 (
2864 -- Bug 3537764: Modified SET logic to handle the all distributions prevented case
2865 SELECT NVL(min(prd.encumbered_flag), 'N')
2866 FROM PO_REQ_DISTRIBUTIONS_ALL PRD
2867 WHERE PRD.requisition_line_id = PRL.requisition_line_id
2868 AND NVL(PRD.prevent_encumbrance_flag, 'N') = 'N'
2869
2870 ), /* Updating these cols also for bug#13930578 */
2871 PRL.last_update_date = sysdate,
2872 PRL.last_updated_by = fnd_global.user_id,
2873 PRL.last_update_login = fnd_global.login_id
2874
2875 WHERE PRL.requisition_line_id IN
2876 (
2877 SELECT TEMP.line_id
2878 FROM PO_ENCUMBRANCE_GT TEMP
2879 WHERE TEMP.gl_status_code = 'A'
2880 AND TEMP.distribution_type = g_dist_type_REQUISITION
2881 AND (
2882 (TEMP.send_to_gl_flag = 'Y') --bug 3568512: use new column
2883 or
2884 ((p_action = g_action_REQ_SPLIT) and (TEMP.modified_by_agent_flag = 'Y'))
2885 )
2886 -- Bug 3537764: do not filter on prevent_encumbrance flag for req split action
2887 -- This is so we can set the encumbered flag to 'N' for the old pre-split line
2888 -- That old line has prevent_enc_flag = 'Y', and was being missed before this fix.
2889 -- Also, this means that the rollup query in the SET clause can no longer assume
2890 -- that it doesn't need to worry about the all distributions prevented case.
2891 );
2892
2893 l_progress := '020';
2894 IF g_debug_stmt THEN
2895 l_debug_count := SQL%ROWCOUNT;
2896 PO_DEBUG.debug_stmt(l_log_head,l_progress,
2897 'Updated Req lines: ' || l_debug_count);
2898 END IF;
2899
2900 --SQL What: Update the encumbered_flag from the affected PO/Release
2901 -- distributions up to the PO/Release shipment
2902 --SQL Where: Only updates the distributions if the distribution transaction
2903 -- succeeded in GL
2904 --SQL Why: The shipment's encumbered_flag needs to be in synch with the
2905 -- update already done to the distribution's encumbered_flag
2906
2907 UPDATE PO_LINE_LOCATIONS_ALL POLL
2908 SET encumbered_flag
2909 =
2910 (
2911 -- Bug 3537764: Modified SET logic to handle the all distributions prevented case
2912 SELECT NVL(min(pod.encumbered_flag), 'N')
2913 FROM PO_DISTRIBUTIONS_ALL POD
2914 WHERE POD.line_location_id = POLL.line_location_id
2915 AND NVL(POD.prevent_encumbrance_flag, 'N') = 'N'
2916 ), /* Updating these cols also for bug#13930578 */
2917 POLL.last_update_date = sysdate,
2918 POLL.last_updated_by = fnd_global.user_id,
2919 POLL.last_update_login = fnd_global.login_id
2920 WHERE POLL.line_location_id IN
2921 (
2922 SELECT TEMP.line_location_id
2923 FROM PO_ENCUMBRANCE_GT TEMP
2924 WHERE TEMP.gl_status_code = 'A'
2925 AND TEMP.distribution_type IN (g_dist_type_STANDARD, g_dist_type_PLANNED,
2926 g_dist_type_SCHEDULED, g_dist_type_BLANKET)
2927 AND TEMP.send_to_gl_flag = 'Y' --bug 3568512: use new column
2928 -- this makes sure that there is atleast one distribution that can be reserved
2929 );
2930
2931 l_progress := '030';
2932 IF g_debug_stmt THEN
2933 l_debug_count := SQL%ROWCOUNT;
2934 PO_DEBUG.debug_stmt(l_log_head,l_progress,
2935 'Updated PO shipments: ' || l_debug_count);
2936 PO_DEBUG.debug_end(l_log_head);
2937 END IF;
2938
2939 EXCEPTION
2940 WHEN OTHERS THEN
2941 --add message to the stack and log a debug msg if necessary
2942 po_message_s.sql_error(g_pkg_name, l_api_name, l_progress, SQLCODE, SQLERRM);
2943 fnd_msg_pub.add;
2944 RAISE;
2945
2946 END rollup_encumbrance_changes;
2947
2948 -------------------------------------------------------------------------------
2949 --Start of Comments
2950 --Name: create_enc_action_history
2951 --Pre-reqs:
2952 -- Encumbrance action completed successfully.
2953 --Modifies:
2954 -- PO_ACTION_HISTORY
2955 --Locks:
2956 -- None.
2957 --Function:
2958 -- This procedure calls the Action History routine, which
2959 -- inserts/updates the po_action_history with the action
2960 -- passed to this procedure.
2961 --Parameters:
2962 --IN:
2963 --p_doc_type
2964 -- Differentiates between the doc being a REQ, PA, PO, or RELEASE,
2965 -- which is used to identify the tables to look at (PO vs. Req)
2966 -- and the join conditions.
2967 -- Use the g_doc_type_<> variables, where <> is:
2968 -- REQUISITION
2969 -- PA
2970 -- PO
2971 -- RELEASE
2972 --p_doc_id_tbl
2973 -- The header ids of the documents on which the encumbrance action is
2974 -- performed.
2975 --p_employee_id
2976 -- ID of the user taking the encumbrance action.
2977 --p_action
2978 -- The encumbrance action being performed.
2979 -- Use the g_action_<> variables.
2980 --p_cbc_flag
2981 -- Indicates whether or not to this action is one of the special
2982 -- CBC Year-End Reserve/Unreserve actions, which have different
2983 -- action history entries.
2984 -- g_parameter_YES - CBC action
2985 -- g_parameter_NO - regular action
2986 --Testing:
2987 --
2988 --End of Comments
2989 -------------------------------------------------------------------------------
2990 PROCEDURE create_enc_action_history(
2991 p_doc_type IN VARCHAR2
2992 , p_doc_id_tbl IN po_tbl_number
2993 , p_employee_id IN NUMBER
2994 , p_action IN VARCHAR2
2995 , p_cbc_flag IN VARCHAR2
2996 )
2997 IS
2998
2999 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_ENC_ACTION_HISTORY';
3000 l_log_head CONSTANT VARCHAR2(100) := g_log_head || l_api_name;
3001 l_progress VARCHAR2(3) := '000';
3002
3003 l_record_action PO_ACTION_HISTORY.action_code%TYPE;
3004
3005 l_gt_key NUMBER;
3006 l_debug_rowid_tbl po_tbl_varchar2000;
3007 l_debug_column_tbl po_tbl_varchar30;
3008
3009 l_orig_doc_id_tbl po_tbl_number;
3010 l_orig_rev_num_tbl po_tbl_number;
3011 l_orig_auth_status_tbl po_tbl_varchar30;
3012 l_orig_doc_subtype_tbl po_tbl_varchar30;
3013 l_orig_null_flag_tbl po_tbl_varchar1;
3014
3015 l_update_doc_type_tbl po_tbl_varchar30;
3016 l_update_doc_id_tbl po_tbl_number;
3017
3018 l_insert_doc_type_tbl po_tbl_varchar30;
3019 l_insert_doc_id_tbl po_tbl_number;
3020 l_insert_doc_subtype_tbl po_tbl_varchar30;
3021 l_insert_action_code_tbl po_tbl_varchar30;
3022 l_insert_rev_num_tbl po_tbl_number;
3023
3024 l_doc_id NUMBER;
3025 l_doc_subtype PO_HEADERS_ALL.type_lookup_code%TYPE;
3026 l_rev_num NUMBER;
3027
3028 l_index NUMBER;
3029 l_update_count NUMBER;
3030 l_insert_count NUMBER;
3031
3032 BEGIN
3033
3034 IF g_debug_stmt THEN
3035 PO_DEBUG.debug_begin(l_log_head);
3036 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_type',p_doc_type);
3037 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_id_tbl',p_doc_id_tbl);
3038 PO_DEBUG.debug_var(l_log_head,l_progress,'p_employee_id',p_employee_id);
3039 PO_DEBUG.debug_var(l_log_head,l_progress,'p_action',p_action);
3040 PO_DEBUG.debug_var(l_log_head,l_progress,'p_cbc_flag',p_cbc_flag);
3041 END IF;
3042
3043 l_progress := '010';
3044
3045 -- CBC Reserve/Unreserve record a different Action History code
3046 IF (p_cbc_flag = g_parameter_YES) THEN
3047 l_progress := '020';
3048 IF (p_action = g_action_RESERVE) THEN
3049 l_record_action := PO_CONSTANTS_SV.IGC_YEAR_END_RESERVE;
3050 ELSIF (p_action = g_action_UNRESERVE) THEN
3051 l_record_action := PO_CONSTANTS_SV.IGC_YEAR_END_UNRESERVE;
3052 END IF;
3053 ELSIF (p_action = g_action_REQ_SPLIT) THEN
3054 l_progress := '023';
3055 l_record_action := PO_CONSTANTS_SV.ADJUST;
3056 ELSE
3057 l_progress := '026';
3058 l_record_action := p_action;
3059 END IF;
3060
3061 IF g_debug_stmt THEN
3062 PO_DEBUG.debug_var(l_log_head,l_progress,'l_record_action',l_record_action);
3063 END IF;
3064
3065 l_progress := '050';
3066
3067 --
3068 -- Load the data into the temp table for bulk processing.
3069 --
3070 -- PO_SESSION_GT mapping
3071 --
3072 -- num1 document header_id
3073 -- num2 revision_num
3074 -- char1 authorization_status
3075 -- char2 document subtype
3076 -- char3 NULL action exists flag
3077 --
3078
3079 l_gt_key := PO_CORE_S.get_session_gt_nextval();
3080
3081 l_progress := '060';
3082
3083 FORALL i IN 1 .. p_doc_id_tbl.COUNT
3084 INSERT INTO PO_SESSION_GT
3085 ( key
3086 , num1
3087 )
3088 VALUES
3089 ( l_gt_key
3090 , p_doc_id_tbl(i)
3091 );
3092
3093 l_progress := '100';
3094
3095 -- Fill in the revision number, authorization status, doc subtype
3096
3097 IF (p_doc_type IN (g_doc_type_PO, g_doc_type_PA)) THEN
3098
3099 l_progress := '110';
3100
3101 UPDATE PO_SESSION_GT SCRATCH
3102 SET
3103 ( num2
3104 , char1
3105 , char2
3106 )
3107 =
3108 ( SELECT
3109 POH.revision_num
3110 , POH.authorization_status
3111 , POH.type_lookup_code
3112 FROM
3113 PO_HEADERS_ALL POH
3114 WHERE POH.po_header_id = SCRATCH.num1
3115 )
3116 WHERE SCRATCH.key = l_gt_key
3117 ;
3118
3119 l_progress := '115';
3120
3121 ELSIF (p_doc_type = g_doc_type_RELEASE) THEN
3122
3123 l_progress := '120';
3124
3125 UPDATE PO_SESSION_GT SCRATCH
3126 SET
3127 ( num2
3128 , char1
3129 , char2
3130 )
3131 =
3132 ( SELECT
3133 POR.revision_num
3134 , POR.authorization_status
3135 , POR.release_type
3136 FROM
3137 PO_RELEASES_ALL POR
3138 WHERE POR.po_release_id = SCRATCH.num1
3139 )
3140 WHERE SCRATCH.key = l_gt_key
3141 ;
3142
3143 l_progress := '125';
3144
3145 ELSE -- requisitions
3146
3147 l_progress := '130';
3148
3149 -- no revision_num for Reqs.
3150
3151 UPDATE PO_SESSION_GT SCRATCH
3152 SET
3153 ( char1
3154 , char2
3155 )
3156 =
3157 ( SELECT
3158 PRH.authorization_status
3159 , PRH.type_lookup_code
3160 FROM
3161 PO_REQUISITION_HEADERS_ALL PRH
3162 WHERE PRH.requisition_header_id = SCRATCH.num1
3163 )
3164 WHERE SCRATCH.key = l_gt_key
3165 ;
3166
3167 l_progress := '135';
3168
3169 END IF;
3170
3171 -- Determine if a NULL action already exists for the doc.
3172
3173 l_progress := '150';
3174
3175 UPDATE PO_SESSION_GT SCRATCH
3176 SET char3 =
3177 ( SELECT 'Y'
3178 FROM PO_ACTION_HISTORY POAH
3179 WHERE POAH.object_type_code = p_doc_type
3180 AND POAH.object_id = SCRATCH.num1
3181 AND POAH.action_code IS NULL
3182 AND POAH.employee_id = p_employee_id
3183 )
3184 WHERE SCRATCH.key = l_gt_key
3185 ;
3186
3187 l_progress := '160';
3188
3189 IF g_debug_stmt THEN
3190 PO_DEBUG.debug_session_gt(l_log_head,l_progress,l_gt_key
3191 , po_tbl_varchar30('num1','num2','char1','char2','char3')
3192 );
3193 END IF;
3194
3195 l_progress := '200';
3196
3197 --
3198 -- Based on whether or not there is a NULL action existing for the doc,
3199 -- we either need to update the NULL action entry or create a new entry.
3200 -- Also, if the doc is In Process or Pre-Approved, we would need to make
3201 -- a new NULL action after updating the old one.
3202 --
3203
3204 -- Retrieve the data to decide what updates/inserts we need to do.
3205
3206 SELECT
3207 SCRATCH.num1
3208 , SCRATCH.num2
3209 , SCRATCH.char1
3210 , SCRATCH.char2
3211 , SCRATCH.char3
3212 BULK COLLECT INTO
3213 l_orig_doc_id_tbl
3214 , l_orig_rev_num_tbl
3215 , l_orig_auth_status_tbl
3216 , l_orig_doc_subtype_tbl
3217 , l_orig_null_flag_tbl
3218 FROM
3219 PO_SESSION_GT SCRATCH
3220 WHERE SCRATCH.key = l_gt_key
3221 ;
3222
3223 l_progress := '210';
3224
3225 -- Initialize the update / insert tables.
3226
3227 l_update_doc_id_tbl := po_tbl_number();
3228
3229 l_insert_doc_id_tbl := po_tbl_number();
3230 l_insert_doc_subtype_tbl := po_tbl_varchar30();
3231 l_insert_action_code_tbl := po_tbl_varchar30();
3232 l_insert_rev_num_tbl := po_tbl_number();
3233
3234 l_progress := '220';
3235
3236 -- Determine which updates and inserts need to occur.
3237
3238 FOR i IN 1 .. l_orig_doc_id_tbl.COUNT LOOP
3239
3240 -- cache a few vars
3241 l_doc_id := l_orig_doc_id_tbl(i);
3242 l_doc_subtype := l_orig_doc_subtype_tbl(i);
3243 l_rev_num := l_orig_rev_num_tbl(i);
3244
3245 IF g_debug_stmt THEN
3246 PO_DEBUG.debug_var(l_log_head,l_progress,'l_doc_id',l_doc_id);
3247 PO_DEBUG.debug_var(l_log_head,l_progress,'l_doc_subtype',l_doc_subtype);
3248 PO_DEBUG.debug_var(l_log_head,l_progress,'l_rev_num',l_rev_num);
3249 END IF;
3250
3251 -- Is there a NULL action?
3252
3253 IF (l_orig_null_flag_tbl(i) = 'Y') THEN
3254
3255 IF g_debug_stmt THEN
3256 PO_DEBUG.debug_stmt(l_log_head,l_progress, 'A NULL Action Histroy Exists' );
3257 END IF;
3258 -- There is a NULL action for this doc, so update it.
3259
3260 l_update_doc_id_tbl.EXTEND;
3261 l_update_doc_id_tbl(l_update_doc_id_tbl.LAST) := l_doc_id;
3262
3263 -- For In Process / Pre-Approved docs, we need to
3264 -- make a new NULL action since we used the old one.
3265
3266 IF (l_orig_auth_status_tbl(i) IN
3267 (PO_CONSTANTS_SV.IN_PROCESS, PO_CONSTANTS_SV.PRE_APPROVED)
3268 -- Bug7387775(adding following condition)
3269 -- Added the Following Condition to Avoid Creation a NULL Action Histroy
3270 -- for requisition. As when Requisition are imported as APPROVED then
3271 -- no WF is initated and this NULL action histroy wont get updated to
3272 -- APPROVE , which will create problem latter.
3273 and (p_doc_type <> g_doc_type_REQUISITION)
3274 ) THEN
3275
3276 IF g_debug_stmt THEN
3277 PO_DEBUG.debug_stmt(l_log_head,l_progress, 'Creating Blank Action Histroy' );
3278 END IF;
3279 l_insert_doc_id_tbl.EXTEND;
3280 l_insert_doc_subtype_tbl.EXTEND;
3281 l_insert_action_code_tbl.EXTEND;
3282 l_insert_rev_num_tbl.EXTEND;
3283
3284 l_index := l_insert_doc_id_tbl.LAST;
3285
3286 l_insert_doc_id_tbl(l_index) := l_doc_id;
3287 l_insert_doc_subtype_tbl(l_index) := l_doc_subtype;
3288 l_insert_action_code_tbl(l_index) := NULL;
3289 l_insert_rev_num_tbl(l_index) := l_rev_num;
3290
3291 END IF;
3292
3293 ELSE -- no NULL action
3294
3295 -- bug 3568559
3296 -- If there is not an existing NULL action,
3297 -- just record (insert) the action being taken.
3298 -- There is not need to enter as SUBMIT (...for Approval) action,
3299 -- especially for UNRESERVE, ADJUST.
3300
3301 l_insert_doc_id_tbl.EXTEND;
3302 l_insert_doc_subtype_tbl.EXTEND;
3303 l_insert_action_code_tbl.EXTEND;
3304 l_insert_rev_num_tbl.EXTEND;
3305
3306 l_index := l_insert_doc_id_tbl.LAST;
3307
3308 l_insert_doc_id_tbl(l_index) := l_doc_id;
3309 l_insert_doc_subtype_tbl(l_index) := l_doc_subtype;
3310 l_insert_action_code_tbl(l_index) := l_record_action;
3311 l_insert_rev_num_tbl(l_index) := l_rev_num;
3312
3313 END IF;
3314
3315 END LOOP;
3316
3317 l_progress := '300';
3318
3319 l_update_count := l_update_doc_id_tbl.COUNT;
3320 l_insert_count := l_insert_doc_id_tbl.COUNT;
3321
3322 l_progress := '310';
3323
3324 IF (l_update_count > 0) THEN
3325
3326 l_progress := '320';
3327
3328 --
3329 -- Update the NULL action records to the action being taken.
3330 --
3331
3332 l_update_doc_type_tbl := po_tbl_varchar30(p_doc_type);
3333 l_update_doc_type_tbl.EXTEND(l_update_count-1, 1);
3334
3335 l_progress := '330';
3336
3337 PO_ACTION_HISTORY_SV.update_action_history(
3338 p_doc_id_tbl => l_update_doc_id_tbl
3339 , p_doc_type_tbl => l_update_doc_type_tbl
3340 , p_action_code => l_record_action
3341 , p_employee_id => p_employee_id
3342 );
3343
3344 l_progress := '340';
3345
3346 END IF;
3347
3348 l_progress := '400';
3349
3350 IF (l_insert_count > 0) THEN
3351
3352 l_progress := '410';
3353
3354 --
3355 -- Insert action records.
3356 --
3357
3358 l_insert_doc_type_tbl := po_tbl_varchar30(p_doc_type);
3359 l_insert_doc_type_tbl.EXTEND(l_insert_count-1, 1);
3360
3361 l_progress := '420';
3362
3363 PO_ACTION_HISTORY_SV.insert_action_history(
3364 p_doc_id_tbl => l_insert_doc_id_tbl
3365 , p_doc_type_tbl => l_insert_doc_type_tbl
3366 , p_doc_subtype_tbl => l_insert_doc_subtype_tbl
3367 , p_doc_revision_num_tbl => l_insert_rev_num_tbl
3368 , p_action_code_tbl => l_insert_action_code_tbl
3369 , p_employee_id => p_employee_id
3370 );
3371
3372 l_progress := '430';
3373
3374 END IF;
3375
3376 l_progress := '900';
3377
3378 IF g_debug_stmt THEN
3379 PO_DEBUG.debug_end(l_log_head);
3380 END IF;
3381
3382 EXCEPTION
3383 WHEN OTHERS THEN
3384 -- Log a debug message and set the message on the FND dictionary stack.
3385 po_message_s.sql_error(g_pkg_name,l_api_name,l_progress,SQLCODE,SQLERRM);
3386 fnd_msg_pub.add;
3387 RAISE;
3388
3389 END create_enc_action_history;
3390
3391
3392
3393
3394 -------------------------------------------------------------------------------
3395 --Start of Comments
3396 --Name: set_status_requires_reapproval
3397 --Pre-reqs:
3398 -- Unreserve action completed successfully.
3399 --Modifies:
3400 -- PO_HEADERS_ALL, PO_RELEASES_ALL, PO_LINE_LOCATIONS_ALL
3401 --Locks:
3402 -- None.
3403 --Function:
3404 -- This procedure updates the 'Approved' Shipments to 'REQUIRES REAPPROVAL'
3405 -- if atleast one of the distribution of that shipment is unreserved and
3406 -- rolls up the same to Headers.
3407 --Parameters:
3408 --IN:
3409 --p_document_type
3410 -- Differentiates between the main doc being a REQUISITION, AGREEMENT,
3411 -- PURCHASE ORDER, or RELEASE which is used to identify the tables to look at
3412 -- (PO vs. Req) and the join conditions
3413 --OUT:
3414 -- N/A
3415 --Testing:
3416 --
3417 --End of Comments
3418 -------------------------------------------------------------------------------
3419 PROCEDURE set_status_requires_reapproval(
3420 p_document_type IN VARCHAR2
3421 , p_action IN VARCHAR2
3422 , p_cbc_flag IN VARCHAR2
3423 ) IS
3424
3425 l_api_name CONSTANT varchar2(40) := 'SET_STATUS_REQUIRES_REAPPROVAL';
3426 l_log_head CONSTANT varchar2(100) := g_log_head || l_api_name;
3427 l_progress VARCHAR2(3);
3428
3429 l_debug_count NUMBER;
3430 l_affected_gl_status_code VARCHAR2(1);
3431
3432 BEGIN
3433
3434 l_progress := '000';
3435
3436 IF g_debug_stmt THEN
3437 PO_DEBUG.debug_begin(l_log_head);
3438 PO_DEBUG.debug_var(l_log_head,l_progress,'p_document_type',p_document_type);
3439 PO_DEBUG.debug_var(l_log_head,l_progress,'p_action',p_action);
3440 PO_DEBUG.debug_var(l_log_head,l_progress,'p_cbc_flag',p_cbc_flag);
3441 END IF;
3442
3443 -- Only change doc status to Requires Reapproval when
3444 -- 1. Normal Unreserve action succeeds
3445 -- 2. CBC Year End Reserve fails
3446 l_progress := '010';
3447 IF (p_action = g_action_UNRESERVE and p_cbc_flag = 'N') THEN
3448
3449 l_affected_gl_status_code := 'A';
3450
3451 ELSIF (p_action = g_action_RESERVE and p_cbc_flag = 'Y') THEN
3452
3453 l_affected_gl_status_code := 'R';
3454
3455 ELSE
3456 --for all other actions, do not change the document status
3457 IF g_debug_stmt THEN
3458 PO_DEBUG.debug_stmt(l_log_head,l_progress,'No status change; early return');
3459 END IF;
3460
3461 RETURN;
3462 END IF;
3463
3464 IF g_debug_stmt THEN
3465 PO_DEBUG.debug_var(l_log_head,l_progress,
3466 'l_affected_gl_status_code',l_affected_gl_status_code);
3467 END IF;
3468
3469
3470 IF (p_document_type = g_doc_type_PO) THEN
3471
3472 -- For an approved shipment, if atleast one distribution's
3473 -- encumbered_flag is 'N', then the Approved flag of the shipment is set
3474 -- to 'R'
3475
3476 --SQL What: Update the PO shipment approved flag to 'R' if one of its
3477 -- distributions has been unreserved.
3478 --SQL Where: Only updates the shipment if the unreserve transaction
3479 -- succeeded in GL and shipment is approved.
3480 --SQL Why: The shipment should be unapproved if one of its distribution
3481 -- is unreserved.
3482
3483 l_progress := '020';
3484
3485 UPDATE PO_LINE_LOCATIONS_ALL POLL
3486 SET POLL.approved_flag = 'R',
3487 POLL.last_update_date = sysdate,
3488 POLL.last_updated_by = fnd_global.user_id,
3489 POLL.last_update_login = fnd_global.login_id
3490 WHERE POLL.po_release_id is NULL
3491 AND nvl(POLL.approved_flag,'N') = 'Y'
3492 AND EXISTS
3493 (
3494 SELECT 'UNRESERVED DISTRIBUTION EXISTS'
3495 FROM PO_ENCUMBRANCE_GT TEMP
3496 WHERE TEMP.gl_status_code = l_affected_gl_status_code
3497 AND TEMP.send_to_gl_flag = 'Y' --bug 3568512: use new column
3498 AND TEMP.line_location_id = POLL.line_location_id
3499 AND TEMP.distribution_type IN
3500 (g_dist_type_STANDARD, g_dist_type_PLANNED)
3501 );
3502
3503 IF g_debug_stmt THEN
3504 l_debug_count := SQL%ROWCOUNT;
3505 PO_DEBUG.debug_stmt(l_log_head,l_progress,
3506 'PO Shipment flags updated : ' || l_debug_count);
3507 END IF;
3508
3509 -- Now rollup the Approval flag from Shipments to Headers.
3510
3511 --SQL What: Update the PO authorization status to 'REQUIRES REAPPROVAL'
3512 -- if one of its distributions has been unreserved.
3513 --SQL Where: Only updates the PO if the unreserve transaction
3514 -- succeeded in GL and PO is approved.
3515 --SQL Why: The PO should be unapproved if one of its distribution
3516 -- is unreserved.
3517
3518 l_progress := '030';
3519
3520 UPDATE PO_HEADERS_ALL POH
3521 SET POH.authorization_status = 'REQUIRES REAPPROVAL',
3522 POH.approved_flag = 'R',
3523 POH.last_update_date = sysdate,
3524 POH.last_updated_by = fnd_global.user_id,
3525 POH.last_update_login = fnd_global.login_id
3526 WHERE nvl(POH.approved_flag,'N') = 'Y' -- if approved
3527 AND EXISTS
3528 (
3529 SELECT 'UNRESERVED DISTRIBUTION EXISTS'
3530 FROM PO_ENCUMBRANCE_GT TEMP
3531 WHERE TEMP.gl_status_code = l_affected_gl_status_code
3532 AND TEMP.send_to_gl_flag = 'Y' --bug 3568512: use new column
3533 AND TEMP.header_id = POH.po_header_id
3534 AND TEMP.distribution_type IN
3535 (g_dist_type_STANDARD, g_dist_type_PLANNED)
3536 );
3537
3538 IF g_debug_stmt THEN
3539 l_debug_count := SQL%ROWCOUNT;
3540 PO_DEBUG.debug_stmt(l_log_head,l_progress,
3541 'PO Headers updated : ' || l_debug_count);
3542 END IF;
3543
3544 ELSIF (p_document_type = g_doc_type_PA) THEN
3545
3546 -- Entity_id will be the same as document_id for PA, as the action
3547 -- can be taken only from the Header level
3548
3549 --SQL What: Update the PO authorization status to 'REQUIRES REAPPROVAL'
3550 -- if one of its distributions has been unreserved.
3551 --SQL Where: Only updates the PO if the unreserve transaction
3552 -- succeeded in GL and PO is approved.
3553 --SQL Why: The PO should be unapproved if one of its distribution
3554 -- is unreserved.
3555
3556 l_progress := '040';
3557
3558 UPDATE PO_HEADERS_ALL POH
3559 SET POH.authorization_status = 'REQUIRES REAPPROVAL',
3560 POH.approved_flag = 'R',
3561 POH.last_update_date = sysdate,
3562 POH.last_updated_by = fnd_global.user_id,
3563 POH.last_update_login = fnd_global.login_id
3564 WHERE nvl(POH.approved_flag,'N') = 'Y' -- if approved
3565 AND EXISTS
3566 (
3567 SELECT 'UNRESERVED SINGLE DISTRIBUTION EXISTS'
3568 FROM PO_ENCUMBRANCE_GT TEMP
3569 WHERE TEMP.gl_status_code = l_affected_gl_status_code
3570 AND TEMP.send_to_gl_flag = 'Y' --bug 3568512: use new column
3571 AND TEMP.header_id = POH.po_header_id
3572 AND TEMP.distribution_type = g_dist_type_AGREEMENT
3573 );
3574
3575 IF g_debug_stmt THEN
3576 l_debug_count := SQL%ROWCOUNT;
3577 PO_DEBUG.debug_stmt(l_log_head,l_progress,
3578 'PA Headers updated : ' || l_debug_count);
3579 END IF;
3580
3581 ELSIF (p_document_type = g_doc_type_RELEASE) THEN
3582
3583 l_progress := '050';
3584
3585 --SQL What: Update the Release shipment approved flag to 'R' if one of its
3586 -- distributions has been unreserved.
3587 --SQL Where: Only updates the shipment if the unreserve transaction
3588 -- succeeded in GL and shipment is approved.
3589 --SQL Why: The shipment should be unapproved if one of its distribution
3590 -- is unreserved.
3591
3592 UPDATE PO_LINE_LOCATIONS_ALL POLL
3593 SET POLL.approved_flag = 'R',
3594 POLL.last_update_date = sysdate,
3595 POLL.last_updated_by = fnd_global.user_id,
3596 POLL.last_update_login = fnd_global.login_id
3597 WHERE POLL.po_release_id is NOT NULL
3598 AND nvl(POLL.approved_flag,'N') = 'Y' -- if approved
3599 AND EXISTS
3600 (
3601 SELECT 'UNRESERVED DISTRIBUTION EXISTS'
3602 FROM PO_ENCUMBRANCE_GT TEMP
3603 WHERE TEMP.gl_status_code = l_affected_gl_status_code
3604 AND TEMP.send_to_gl_flag = 'Y' --bug 3568512: use new column
3605 AND TEMP.line_location_id = POLL.line_location_id
3606 AND TEMP.distribution_type IN
3607 (g_dist_type_SCHEDULED, g_dist_type_BLANKET)
3608 );
3609
3610 IF g_debug_stmt THEN
3611 l_debug_count := SQL%ROWCOUNT;
3612 PO_DEBUG.debug_stmt(l_log_head,l_progress,
3613 'Release shipments updated : ' || l_debug_count);
3614 END IF;
3615
3616 l_progress := '060';
3617
3618 --SQL What: Update the Release authorization status to 'REQUIRES REAPPROVAL'
3619 -- if one of its distributions has been unreserved.
3620 --SQL Where: Only updates the Release if the unreserve transaction
3621 -- succeeded in GL and PO is approved.
3622 --SQL Why: The PO should be unapproved if one of its distribution
3623 -- is unreserved.
3624
3625 UPDATE PO_RELEASES_ALL POR
3626 SET POR.authorization_status = 'REQUIRES REAPPROVAL',
3627 POR.approved_flag = 'R',
3628 POR.last_update_date = sysdate,
3629 POR.last_updated_by = fnd_global.user_id,
3630 POR.last_update_login = fnd_global.login_id
3631 WHERE nvl(POR.approved_flag,'N') = 'Y' -- if approved
3632 AND EXISTS
3633 (
3634 SELECT 'UNRESERVED DISTRIBUTION EXISTS'
3635 FROM PO_ENCUMBRANCE_GT TEMP
3636 WHERE TEMP.gl_status_code = l_affected_gl_status_code
3637 AND TEMP.send_to_gl_flag = 'Y' --bug 3568512: use new column
3638 AND TEMP.po_release_id = POR.po_release_id
3639 AND TEMP.distribution_type IN
3640 (g_dist_type_SCHEDULED, g_dist_type_BLANKET)
3641 );
3642
3643 IF g_debug_stmt THEN
3644 l_debug_count := SQL%ROWCOUNT;
3645 PO_DEBUG.debug_stmt(l_log_head,l_progress,
3646 'Release Headers updated : ' || l_debug_count);
3647 END IF;
3648
3649 ELSE -- This is the case of requisitions. For requisitions, once the document is
3650 -- approved, it is not possible to take unreserve action. If the unreserve
3651 -- action is taken on the Incomplete Requisition, then updating of
3652 -- authorization_status is not required
3653
3654 NULL;
3655
3656 END IF;
3657
3658 l_progress := '070';
3659
3660 IF g_debug_stmt THEN
3661 PO_DEBUG.debug_end(l_log_head);
3662 END IF;
3663
3664 EXCEPTION
3665 WHEN OTHERS THEN
3666 --add message to the stack and log a debug msg if necessary
3667 po_message_s.sql_error(g_pkg_name, l_api_name, l_progress, SQLCODE, SQLERRM);
3668 fnd_msg_pub.add;
3669 RAISE;
3670
3671 END set_status_requires_reapproval;
3672
3673
3674
3675 ------------------------------------------------------------------------------
3676 --Start of Comments
3677 --Name: create_detailed_report
3678 --Pre-reqs:
3679 -- PO_ENCUMBRANCE_GT result fields have been populated
3680 --Modifies:
3681 -- PO_ENCUMBRANCE_GT
3682 -- PO_ONLINE_REPORT_TEXT
3683 --Locks:
3684 -- None.
3685 --Function:
3686 --
3687 --Parameters:
3688 --IN:
3689 --p_gl_return_code
3690 -- The return code from the call to the GL funds checker
3691 --p_user_id
3692 -- ID of the current user
3693 --OUT:
3694 --x_online_report_id
3695 -- The unique ID of the result information in the
3696 -- PO_ONLINE_REPORT_TEXT table
3697 --x_po_return_code
3698 -- The return code for this transaction, based on the GL result
3699 -- code and PO warning conditions
3700 --x_po_return_msg
3701 -- The message name (from FND_NEW_MESSAGES) that corresponds to the
3702 -- x_po_return_code
3703 --Testing:
3704 --
3705 --End of Comments
3706 -------------------------------------------------------------------------------
3707 PROCEDURE create_detailed_report(
3708 p_gl_return_code IN VARCHAR2
3709 , p_user_id IN NUMBER
3710 , x_online_report_id OUT NOCOPY VARCHAR2
3711 , x_po_return_code OUT NOCOPY VARCHAR2
3712 , x_po_return_msg_name OUT NOCOPY VARCHAR2
3713 )
3714 IS
3715
3716 l_api_name CONSTANT varchar2(30) := 'CREATE_DETAILED_REPORT';
3717 l_log_head CONSTANT varchar2(100) := g_log_head || l_api_name;
3718 l_progress varchar2(3) := '000';
3719
3720 l_debug_count NUMBER;
3721 l_delim CONSTANT VARCHAR2(1) := ' ';
3722
3723 l_line_token FND_NEW_MESSAGES.message_text%TYPE;
3724 l_shipment_token FND_NEW_MESSAGES.message_text%TYPE;
3725 l_distribution_token FND_NEW_MESSAGES.message_text%TYPE;
3726 l_warning_rows_flag VARCHAR2(1) := 'N';
3727
3728 l_sequence_num_tbl PO_TBL_NUMBER;
3729 l_line_num_tbl PO_TBL_NUMBER;
3730 l_shipment_num_tbl PO_TBL_NUMBER;
3731 l_distribution_num_tbl PO_TBL_NUMBER;
3732 l_distribution_id_tbl PO_TBL_NUMBER;
3733 l_result_code_tbl PO_TBL_VARCHAR5;
3734 l_message_type_tbl PO_TBL_VARCHAR1;
3735 l_text_line_tbl PO_TBL_VARCHAR2000;
3736 l_show_in_psa_tbl PO_TBL_VARCHAR1; --<bug#5010001>
3737 l_segment1_tbl PO_TBL_VARCHAR20; --<bug#5010001>
3738 l_distribution_type_tbl po_tbl_varchar25; --<bug#5010001>
3739 l_error_rows_flag VARCHAR2(1);
3740 BEGIN
3741
3742 IF g_debug_stmt THEN
3743 PO_DEBUG.debug_begin(l_log_head);
3744 PO_DEBUG.debug_var(l_log_head,l_progress,'p_gl_return_code',
3745 p_gl_return_code);
3746 PO_DEBUG.debug_var(l_log_head,l_progress,'p_user_id', p_user_id);
3747 END IF;
3748
3749 l_progress := '010';
3750
3751 l_line_token := fnd_message.get_string('PO', 'PO_ZMVOR_LINE');
3752 l_shipment_token := fnd_message.get_string('PO', 'PO_ZMVOR_SHIPMENT');
3753 l_distribution_token := fnd_message.get_string('PO', 'PO_ZMVOR_DISTRIBUTION');
3754
3755 l_progress := '020';
3756
3757 -- Update the result info with identifying string info
3758 -- i.e. 'Line 1 Shipment 2 Distribution 1'
3759 UPDATE PO_ENCUMBRANCE_GT DISTS
3760 SET DISTS.result_text =
3761 (
3762 DECODE( DISTS.distribution_type
3763 , g_dist_type_AGREEMENT, ''
3764 , g_dist_type_REQUISITION,
3765 ( l_line_token || DISTS.line_num
3766 || l_delim || l_distribution_token || DISTS.distribution_num
3767 || l_delim
3768 )
3769 , -- all other docs
3770 ( l_line_token || DISTS.line_num
3771 || l_delim || l_shipment_token || DISTS.shipment_num
3772 || l_delim || l_distribution_token || DISTS.distribution_num
3773 || l_delim
3774 )
3775 )
3776 || DISTS.result_text
3777 )
3778 WHERE DISTS.origin_sequence_num IS NULL
3779 AND DISTS.result_text IS NOT NULL;
3780
3781 l_progress := '030';
3782 IF g_debug_stmt THEN
3783 l_debug_count := SQL%ROWCOUNT;
3784 PO_DEBUG.debug_stmt(l_log_head,l_progress,
3785 'Appended entity nums to lines: ' || l_debug_count);
3786 END IF;
3787
3788
3789 SELECT
3790 nvl(DISTS.row_index, DISTS.sequence_num)
3791 , DISTS.line_num
3792 , DISTS.shipment_num
3793 , DISTS.distribution_num
3794 , DISTS.distribution_id
3795 , DISTS.gl_result_code
3796 , DISTS.result_type
3797 , DISTS.result_text
3798 , CASE --<bug#5378759>
3799 WHEN nvl(DISTS.prevent_encumbrance_flag,'N')='Y' THEN
3800 'Y'
3801 WHEN DISTS.period_name IS NULL THEN
3802 'Y'
3803 ELSE
3804 'N'
3805 END CASE
3806 , DISTS.segment1 --<bug#5010001>
3807 , DISTS.distribution_type --<bug#5010001>
3808 BULK COLLECT INTO
3809 l_sequence_num_tbl
3810 , l_line_num_tbl
3811 , l_shipment_num_tbl
3812 , l_distribution_num_tbl
3813 , l_distribution_id_tbl
3814 , l_result_code_tbl
3815 , l_message_type_tbl
3816 , l_text_line_tbl
3817 , l_show_in_psa_tbl --<bug#5010001>
3818 , l_segment1_tbl --<bug#5010001>
3819 , l_distribution_type_tbl --<bug#5010001>
3820 FROM PO_ENCUMBRANCE_GT DISTS
3821 WHERE DISTS.origin_sequence_num IS NULL --main doc only
3822 AND DISTS.result_text IS NOT NULL
3823 AND (DISTS.adjustment_status IS NULL OR
3824 DISTS.adjustment_status = g_adjustment_status_NEW
3825 --bug 3378198: for Adjust, only report on new distributions
3826 )
3827 ;
3828
3829 l_progress := '040';
3830 IF g_debug_stmt THEN
3831 l_debug_count := SQL%ROWCOUNT;
3832 PO_DEBUG.debug_stmt(l_log_head,l_progress,
3833 'Retrieved result info for rows: ' || l_debug_count);
3834 END IF;
3835
3836 -- Make the autonomous call out to insert the result information.
3837 -- Autonomous guarantees results are available even if caller rolls
3838 -- back on failure.
3839 insert_report_autonomous(
3840 p_reporting_level => g_REPORT_LEVEL_DISTRIBUTION
3841 , p_message_text => NULL
3842 , p_user_id => p_user_id
3843 , p_sequence_num_tbl => l_sequence_num_tbl
3844 , p_line_num_tbl => l_line_num_tbl
3845 , p_shipment_num_tbl => l_shipment_num_tbl
3846 , p_distribution_num_tbl => l_distribution_num_tbl
3847 , p_distribution_id_tbl => l_distribution_id_tbl
3848 , p_result_code_tbl => l_result_code_tbl
3849 , p_message_type_tbl => l_message_type_tbl
3850 , p_text_line_tbl => l_text_line_tbl
3851 , p_show_in_psa_flag => l_show_in_psa_tbl --<bug#5010001>
3852 , p_segment1_tbl => l_segment1_tbl --<bug#5010001>
3853 , p_distribution_type_tbl=>l_distribution_type_tbl --<bug#5010001>
3854 , x_online_report_id => x_online_report_id
3855 );
3856
3857 l_progress := '050';
3858
3859
3860 IF (p_gl_return_code IN ('S', 'A')) THEN
3861 -- GL returned Success or Advisory
3862 l_progress := '052';
3863
3864 --bug#5413111 If any of the rows had failed due to a gl date
3865 --failure and if the result was Error we should show an error.
3866 --It is possible that none of the remaining rows sent to GL
3867 --errored out. In such case we would see success while some of
3868 --the rows had a gl date exception.
3869 BEGIN
3870 SELECT 'Y'
3871 INTO l_error_rows_flag
3872 FROM PO_ENCUMBRANCE_GT DISTS
3873 WHERE DISTS.result_type = g_result_ERROR
3874 AND rownum = 1;
3875 EXCEPTION
3876 WHEN NO_DATA_FOUND THEN
3877 l_error_rows_flag := 'N';
3878 END;
3879
3880 -- Bug 3295875: If GL returns an 'A', it could be due
3881 -- to one of their own expanded GL lines in the packet.
3882 -- In this case, switch the status to 'S' if all OUR
3883 -- distribution lines are success.
3884 -- The reverse case is that GL can return an 'S'
3885 -- but we have prevent encumbrance rows that GL did not
3886 -- see, so PO flips the status to 'A' manually
3887 -- Bug 3589694: Added a WHERE condition to only do this for
3888 -- main doc rows, since those are the only rows we report on.
3889
3890 IF(nvl(l_error_rows_flag,'N')='N')THEN
3891 BEGIN
3892 SELECT 'Y'
3893 INTO l_warning_rows_flag
3894 FROM PO_ENCUMBRANCE_GT DISTS
3895 WHERE DISTS.result_type = g_result_WARNING
3896 AND DISTS.origin_sequence_num IS NULL --bug 3589694
3897 AND rownum = 1;
3898 EXCEPTION
3899 WHEN NO_DATA_FOUND THEN
3900 l_warning_rows_flag := 'N';
3901 END;
3902 END IF;
3903
3904 l_progress := '054';
3905 IF g_debug_stmt THEN
3906 PO_DEBUG.debug_var(l_log_head,l_progress,
3907 'l_warning_rows_flag',l_warning_rows_flag);
3908 END IF;
3909
3910 IF (nvl(l_warning_rows_flag,'N') = 'Y') THEN
3911 x_po_return_code := g_return_WARNING;
3912 x_po_return_msg_name := 'PO_ENC_API_WARNING';
3913 --<bug#5413111 START>
3914 ELSIF (nvl(l_error_rows_flag,'N')='Y') THEN
3915 x_po_return_code := g_return_FAILURE;
3916 x_po_return_msg_name := 'PO_ENC_API_FAILURE';
3917 --<bug#5413111 END>
3918 ELSE
3919 x_po_return_code := g_return_SUCCESS;
3920 x_po_return_msg_name := 'PO_ENC_API_SUCCESS';
3921 END IF;
3922
3923 ELSIF (p_gl_return_code = 'P') THEN
3924 -- GL returned Partial (some rows passed, some failed)
3925
3926 l_progress := '056';
3927 x_po_return_code := g_return_PARTIAL;
3928 x_po_return_msg_name := 'PO_ENC_API_FAILURE';
3929
3930 ELSIF (p_gl_return_code = 'F') THEN
3931 -- GL returned Failure
3932
3933 l_progress := '058';
3934 x_po_return_code := g_return_FAILURE;
3935 x_po_return_msg_name := 'PO_ENC_API_FAILURE';
3936
3937 ELSIF (p_gl_return_code = 'T') THEN
3938 -- GL returned Fatal
3939
3940 l_progress := '059';
3941 x_po_return_code := g_return_FATAL;
3942 x_po_return_msg_name := 'PO_ENC_API_FAILURE';
3943
3944 END IF;
3945
3946 l_progress := '060';
3947 IF g_debug_stmt THEN
3948 PO_DEBUG.debug_var(l_log_head,l_progress,'x_online_report_id',
3949 x_online_report_id);
3950 PO_DEBUG.debug_var(l_log_head,l_progress,'x_po_return_code',
3951 x_po_return_code);
3952 PO_DEBUG.debug_var(l_log_head,l_progress,'x_po_return_msg_name',
3953 x_po_return_msg_name);
3954 PO_DEBUG.debug_end(l_log_head);
3955 END IF;
3956
3957 EXCEPTION
3958 WHEN OTHERS THEN
3959 --add message to the stack and log a debug msg if necessary
3960 po_message_s.sql_error(g_pkg_name, l_api_name, l_progress, SQLCODE, SQLERRM);
3961 fnd_msg_pub.add;
3962 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3963
3964 END create_detailed_report;
3965
3966
3967 ------------------------------------------------------------------------------
3968 --Start of Comments
3969 --Name: create_exception_report
3970 --Pre-reqs:
3971 -- None.
3972 --Modifies:
3973 -- PO_ONLINE_REPORT_TEXT
3974 --Locks:
3975 -- None.
3976 --Function:
3977 -- Inserts a single row into the PO_ONLINE_REPORT_TEXT table,
3978 -- which provides information about the cause of an exception.
3979 -- This message is an overall-transaction level message.
3980 --Parameters:
3981 --IN:
3982 --p_message_text
3983 -- The detail message to be put into PO_ONLINE_REPORT_TEXT
3984 -- This message indicates why the overall transaction was unsuccessful
3985 --p_user_id
3986 -- ID of the current user
3987 --OUT:
3988 --x_online_report_id
3989 -- The unique ID of the result information in the
3990 -- PO_ONLINE_REPORT_TEXT table
3991 --Testing:
3992 --
3993 --End of Comments
3994 -------------------------------------------------------------------------------
3995 PROCEDURE create_exception_report(
3996 p_message_text IN VARCHAR2
3997 , p_user_id IN NUMBER
3998 , x_online_report_id OUT NOCOPY NUMBER
3999 )
4000 IS
4001
4002 l_api_name CONSTANT varchar2(30) := 'CREATE_EXCEPTION_REPORT';
4003 l_log_head CONSTANT varchar2(100) := g_log_head || l_api_name;
4004 l_progress varchar2(3) := '000';
4005
4006 BEGIN
4007
4008 IF g_debug_stmt THEN
4009 PO_DEBUG.debug_begin(l_log_head);
4010 PO_DEBUG.debug_var(l_log_head,l_progress,'p_message_text', p_message_text);
4011 PO_DEBUG.debug_var(l_log_head,l_progress,'p_user_id', p_user_id);
4012 END IF;
4013
4014 insert_report_autonomous(
4015 p_reporting_level => g_REPORT_LEVEL_TRANSACTION
4016 , p_message_text => p_message_text
4017 , p_user_id => p_user_id
4018 , p_sequence_num_tbl => NULL
4019 , p_line_num_tbl => NULL
4020 , p_shipment_num_tbl => NULL
4021 , p_distribution_num_tbl => NULL
4022 , p_distribution_id_tbl => NULL
4023 , p_result_code_tbl => NULL
4024 , p_message_type_tbl => NULL
4025 , p_text_line_tbl => NULL
4026 , p_show_in_psa_flag => NULL --<bug#5010001>
4027 , p_segment1_tbl => NULL --<bug#5010001>
4028 , p_distribution_type_tbl=> NULL --<bug#5010001>
4029 , x_online_report_id => x_online_report_id
4030 );
4031
4032 l_progress := '010';
4033 IF g_debug_stmt THEN
4034 PO_DEBUG.debug_var(l_log_head,l_progress,'x_online_report_id',
4035 x_online_report_id);
4036 PO_DEBUG.debug_end(l_log_head);
4037 END IF;
4038
4039 EXCEPTION
4040 WHEN OTHERS THEN
4041 --add message to the stack and log a debug msg if necessary
4042 po_message_s.sql_error(g_pkg_name, l_api_name, l_progress, SQLCODE, SQLERRM);
4043 fnd_msg_pub.add;
4044 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4045
4046 END create_exception_report;
4047
4048
4049 ------------------------------------------------------------------------------
4050 --Start of Comments
4051 --Name: insert_report_autonomous
4052 --Pre-reqs:
4053 -- None
4054 --Modifies:
4055 -- PO_ONLINE_REPORT_TEXT
4056 --Locks:
4057 -- None.
4058 --Function:
4059 -- Inserts rows into the PO_ONLINE_REPORT_TEXT table to
4060 -- represent the transaction result "details". This is either
4061 -- the distribution-specific result information or the specific
4062 -- exception information for the overall transaction.
4063 --Parameters:
4064 --IN:
4065 --p_reporting_level
4066 -- Indicates whether we are inserting a single row (an exception
4067 -- msg that applies to the whole transaction) or multiple
4068 -- rows (result information for each distribution)
4069 -- VALUES: g_report_level_TRANSACTION, g_report_level_DISTRIBUTION
4070 --p_message_text
4071 -- Used only if reporting level is g_report_level_TRANSACTION
4072 -- The detail message to be put into PO_ONLINE_REPORT_TEXT
4073 -- This message indicates why the overall transaction was unsuccessful
4074 --p_user_id
4075 -- ID of the current user
4076 --p_sequence_num_tbl
4077 -- Used only if reporting level is g_report_level_DISTRIBUTION
4078 -- A collection of sequence numbers for the rows from
4079 -- PO_ENCUMBRANCE_GT which we are reporting on
4080 --p_line_num_tbl
4081 -- A collection of line numbers for the rows from
4082 -- PO_ENCUMBRANCE_GT which we are reporting on
4083 --p_shipment_num_tbl
4084 -- A collection of shipment numbers for the rows from
4085 -- PO_ENCUMBRANCE_GT which we are reporting on
4086 --p_distribution_num_tbl
4087 -- A collection of distribution numbers for the rows from
4088 -- PO_ENCUMBRANCE_GT which we are reporting on
4089 --p_distribution_id_tbl
4090 -- A collection of distribution ids for the rows from
4091 -- PO_ENCUMBRANCE_GT which we are reporting on
4092 --p_result_code_tbl
4093 -- A collection of result codes for the rows from
4094 -- PO_ENCUMBRANCE_GT which we are reporting on
4095 --p_message_type_tbl
4096 -- A collection of result classifications for the rows from
4097 -- PO_ENCUMBRANCE_GT which we are reporting on
4098 --p_text_line_tbl
4099 -- A collection of result messages for the rows from
4100 -- PO_ENCUMBRANCE_GT which we are reporting on
4101 --OUT:
4102 --x_online_report_id
4103 -- The unique ID of the result information in the
4104 -- PO_ONLINE_REPORT_TEXT table
4105 --Testing:
4106 --
4107 --End of Comments
4108 -------------------------------------------------------------------------------
4109 PROCEDURE insert_report_autonomous(
4110 p_reporting_level IN VARCHAR2
4111 , p_message_text IN VARCHAR2
4112 , p_user_id IN NUMBER
4113 , p_sequence_num_tbl IN po_tbl_number
4114 , p_line_num_tbl IN po_tbl_number
4115 , p_shipment_num_tbl IN po_tbl_number
4116 , p_distribution_num_tbl IN po_tbl_number
4117 , p_distribution_id_tbl IN po_tbl_number
4118 , p_result_code_tbl IN po_tbl_varchar5
4119 , p_message_type_tbl IN po_tbl_varchar1
4120 , p_text_line_tbl IN po_tbl_varchar2000
4121 , p_show_in_psa_flag IN po_tbl_varchar1 --<bug#5010001>
4122 , p_segment1_tbl IN po_tbl_varchar20 --<bug#5010001>
4123 , p_distribution_type_tbl IN po_tbl_varchar25--<bug#5010001>
4124 , x_online_report_id OUT NOCOPY NUMBER
4125 )
4126 IS
4127 PRAGMA AUTONOMOUS_TRANSACTION;
4128
4129 l_api_name CONSTANT varchar2(30) := 'INSERT_REPORT_AUTONOMOUS';
4130 l_log_head CONSTANT varchar2(100) := g_log_head || l_api_name;
4131 l_progress varchar2(3) := '000';
4132 l_report_id PO_ONLINE_REPORT_TEXT.online_report_id%TYPE;
4133
4134 l_user_id NUMBER;
4135 l_message_text PO_ONLINE_REPORT_TEXT.text_line%TYPE;
4136
4137 l_single_message_flag BOOLEAN;
4138
4139 BEGIN
4140
4141 IF g_debug_stmt THEN
4142 PO_DEBUG.debug_begin(l_log_head);
4143 PO_DEBUG.debug_var(l_log_head,l_progress,'p_reporting_level',
4144 p_reporting_level);
4145 PO_DEBUG.debug_var(l_log_head,l_progress,'p_message_text', p_message_text);
4146 PO_DEBUG.debug_var(l_log_head,l_progress,'p_user_id', p_user_id);
4147 PO_DEBUG.debug_var(l_log_head,l_progress,'p_text_line_tbl',p_text_line_tbl);
4148 PO_DEBUG.debug_var(l_log_head,l_progress,'p_message_type_tbl',p_message_type_tbl);
4149 END IF;
4150
4151 x_online_report_id := NULL;
4152
4153 l_progress := '010';
4154
4155 SELECT PO_ONLINE_REPORT_TEXT_S.nextval
4156 INTO l_report_id
4157 FROM dual;
4158
4159 l_progress := '020';
4160
4161 l_user_id := NVL(p_user_id,0);
4162
4163 IF (p_message_text IS NULL) THEN
4164 FND_MESSAGE.set_name('PO', 'PO_MSG_NULL_MESSAGE');
4165 l_message_text := FND_MESSAGE.get;
4166 ELSE
4167 l_message_text := p_message_text;
4168 END IF;
4169
4170 IF g_debug_stmt THEN
4171 PO_DEBUG.debug_var(l_log_head,l_progress,'l_report_id',l_report_id);
4172 PO_DEBUG.debug_var(l_log_head,l_progress,'l_user_id',l_user_id);
4173 END IF;
4174
4175 ---<BUG 12824154 : Modified the code to show detailed messages
4176 --from psa_xla_accounting_errors table if there is an
4177 --unexpected error returned from execute_gl_call
4178 if l_message_text = 'GL_FUNDS_API_EXC' then
4179
4180
4181 INSERT INTO PO_ONLINE_REPORT_TEXT(
4182 online_report_id
4183 , sequence
4184 , last_updated_by
4185 , last_update_date
4186 , created_by
4187 , creation_date
4188 , transaction_type
4189 , message_type
4190 , text_line
4191 , show_in_psa_flag --<bug#5010001>
4192 , segment1
4193 )
4194 select l_report_id
4195 , 0 -- sequence
4196 , l_user_id -- updated by
4197 , SYSDATE -- update date
4198 , l_user_id -- created by
4199 , SYSDATE -- creation date
4200 , g_module_ENCUMBRANCE -- transaction type
4201 , g_result_TRANSACTION -- message type
4202 , pba.ENCODED_MSG
4203 , 'Y' --<bug#5010001>
4204 , poh.segment1
4205 from psa_xla_accounting_errors pba,po_headers_all poh
4206 where pba.event_id = PO_ENCUMBRANCE_POSTPROCESSING.g_event_id
4207 and pba.source_id_int_1 = poh.po_header_id
4208
4209 UNION ALL
4210
4211 select l_report_id
4212 , 0 -- sequence
4213 , l_user_id -- updated by
4214 , SYSDATE -- update date
4215 , l_user_id -- created by
4216 , SYSDATE -- creation date
4217 , g_module_ENCUMBRANCE -- transaction type
4218 , g_result_TRANSACTION -- message type
4219 , pba.ENCODED_MSG
4220 , 'Y' --<bug#5010001>
4221 , prh.segment1
4222 from psa_xla_accounting_errors pba,po_requisition_headers_all prh
4223 where pba.event_id = PO_ENCUMBRANCE_POSTPROCESSING.g_event_id
4224 and pba.source_id_int_1 = prh.requisition_header_id;
4225 else
4226
4227 -- l_single_message_flag indicates what type of detailed message we show.
4228 -- In some scenarios (i.e. a SQL exception or GL Fatal error), we
4229 -- show only a single high level message for the whole trxn, and
4230 -- this message is passed in as p_message_text, or we pop it off
4231 -- the stack if that parameter is NULL (happens for SQL exceptions).
4232 -- In this 1st case, l_single_message_flag is True.
4233 -- In other scenarios, we use the pl/sql table parameters and
4234 -- populate multiple detailed messages for the trxn: one for each
4235 -- distribution. In the 2nd case, l_single_message_flag is False.
4236
4237 l_single_message_flag := FALSE;
4238
4239 IF (p_reporting_level = g_REPORT_LEVEL_TRANSACTION) THEN
4240 l_single_message_flag := TRUE;
4241 END IF;
4242
4243 IF (p_sequence_num_tbl IS NULL) THEN
4244 l_single_message_flag := TRUE;
4245 ELSIF (p_sequence_num_tbl.COUNT = 0) THEN
4246 l_single_message_flag := TRUE;
4247 END IF;
4248
4249 l_progress := '050';
4250 IF g_debug_stmt THEN
4251 PO_DEBUG.debug_var(l_log_head,l_progress,
4252 'l_single_message_flag',l_single_message_flag);
4253 END IF;
4254
4255 IF l_single_message_flag THEN
4256
4257 l_progress := '100';
4258
4259 INSERT INTO PO_ONLINE_REPORT_TEXT(
4260 online_report_id
4261 , sequence
4262 , last_updated_by
4263 , last_update_date
4264 , created_by
4265 , creation_date
4266 , transaction_type
4267 , message_type
4268 , text_line
4269 , show_in_psa_flag --<bug#5010001>
4270 )
4271 VALUES(
4272 l_report_id
4273 , 0 -- sequence
4274 , l_user_id -- updated by
4275 , SYSDATE -- update date
4276 , l_user_id -- created by
4277 , SYSDATE -- creation date
4278 , g_module_ENCUMBRANCE -- transaction type
4279 , g_result_TRANSACTION -- message type
4280 , l_message_text
4281 , 'Y' --<bug#5010001>
4282 );
4283
4284 ELSE
4285 l_progress := '200';
4286
4287 FORALL i IN 1 .. p_sequence_num_tbl.COUNT
4288 INSERT INTO PO_ONLINE_REPORT_TEXT(
4289 online_report_id
4290 , sequence
4291 , last_updated_by
4292 , last_update_date
4293 , created_by
4294 , creation_date
4295 , line_num
4296 , shipment_num
4297 , distribution_num
4298 , transaction_id
4299 , transaction_type
4300 , transaction_location
4301 , message_type
4302 , text_line
4303 , show_in_psa_flag --<bug#5010001>
4304 , segment1 --<bug#5010001>
4305 , distribution_type --<bug#5010001>
4306 )
4307 VALUES(
4308 l_report_id
4309 , NVL(p_sequence_num_tbl(i),0)
4310 , l_user_id
4311 , SYSDATE
4312 , l_user_id
4313 , SYSDATE
4314 , p_line_num_tbl(i)
4315 , p_shipment_num_tbl(i)
4316 , p_distribution_num_tbl(i)
4317 , p_distribution_id_tbl(i)
4318 , g_module_ENCUMBRANCE
4319 , p_result_code_tbl(i)
4320 , p_message_type_tbl(i)
4321 , NVL(p_text_line_tbl(i),l_message_text)
4322 , p_show_in_psa_flag(i) --<bug#5010001>
4323 , p_segment1_tbl(i) --<bug#5010001>
4324 , p_distribution_type_tbl(i) --<bug#5010001>
4325 );
4326
4327 END IF; --reporting level
4328 END if;
4329 x_online_report_id := l_report_id;
4330
4331 l_progress := '900';
4332 IF g_debug_stmt THEN
4333 PO_DEBUG.debug_var(l_log_head,l_progress,'x_online_report_id',
4334 x_online_report_id);
4335 PO_DEBUG.debug_end(l_log_head);
4336 END IF;
4337
4338 COMMIT;
4339
4340 EXCEPTION
4341
4342 WHEN OTHERS THEN
4343 --add message to the stack and log a debug msg if necessary
4344 po_message_s.sql_error(g_pkg_name, l_api_name, l_progress, SQLCODE, SQLERRM);
4345 fnd_msg_pub.add;
4346 COMMIT;
4347 RAISE;
4348
4349 END insert_report_autonomous;
4350
4351
4352 --<Start Bug 3218669>
4353
4354 -------------------------------------------------------------------------------
4355 --Start of Comments
4356 --Name: delete_packet_autonomous
4357 --Pre-reqs:
4358 -- None.
4359 --Modifies:
4360 -- GL_BC_PACKETS
4361 --Locks:
4362 -- None.
4363 --Function:
4364 -- This procedure takes a packet id and deletes and
4365 -- commits it to GL_BC_PACKETS in an autonomous transaction.
4366 --Parameters:
4367 --IN:
4368 --p_packet_id
4369 -- The GL_BC_PACKETS.packet_id for the packet that is to be deleted.
4370 --Testing:
4371 --
4372 --End of Comments
4373 -------------------------------------------------------------------------------
4374
4375 PROCEDURE delete_packet_autonomous(
4376 p_packet_id IN NUMBER
4377 )
4378 IS
4379
4380 l_proc_name CONSTANT VARCHAR2(30) := 'DELETE_PACKET_AUTONOMOUS';
4381 l_log_head CONSTANT VARCHAR2(100) := g_log_head || l_proc_name;
4382 l_progress VARCHAR2(3) := '000';
4383 --<SLA R12>
4384 -- SQL What: Querying for events currently in PSA_BC_XLA_EVENTS_GT
4385 -- SQL Why : Need to delete all data related to these events from PSA tables
4386 -- SQL Join: None
4387 CURSOR cur_events IS
4388 SELECT event_id
4389 FROM PSA_BC_XLA_EVENTS_GT;
4390
4391 BEGIN
4392
4393 IF g_debug_stmt THEN
4394 PO_DEBUG.debug_begin(l_log_head);
4395 PO_DEBUG.debug_var(l_log_head,l_progress,'p_packet_id',p_packet_id);
4396 END IF;
4397
4398 l_progress := '010';
4399
4400 --<R12 SLA start>
4401 FOR REC_EVENTS IN cur_events LOOP
4402 -- call the XLA API to delete events
4403 xla_events_pub_pkg.DELETE_EVENT
4404 (
4405 p_event_source_info => NULL,
4406 p_event_id => REC_EVENTS.event_id,
4407 p_valuation_method => NULL,
4408 p_security_context => NULL
4409 );
4410 END LOOP;
4411 --<R12 SLA End>
4412
4413 IF g_debug_stmt THEN
4414 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Delete Packet GL API Called');
4415 END IF;
4416
4417 l_progress := '020';
4418
4419
4420 IF g_debug_stmt THEN
4421 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Delete Packet Committed');
4422 PO_DEBUG.debug_end(l_log_head);
4423 END IF;
4424
4425 EXCEPTION
4426 WHEN OTHERS THEN
4427 --add message to the stack and log a debug msg if necessary
4428 po_message_s.sql_error(g_pkg_name, l_proc_name, l_progress, SQLCODE, SQLERRM);
4429 fnd_msg_pub.add;
4430 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4431
4432 END delete_packet_autonomous;
4433
4434 --<End Bug 3218669>
4435
4436 --<SLA R12 Start>
4437
4438 -------------------------------------------------------------------------------
4439 --Start of Comments
4440 --Name: DELETE_PO_BC_DISTRIBUTIONS
4441 --Pre-reqs:
4442 -- None.
4443 --Modifies:
4444 -- PO_BC_DISTRIBUTIONS.
4445 --Locks:
4446 -- None.
4447 --Function:
4448 -- This procedure deletes from po_bc_distributions all rows corresponding to
4449 -- a specified packetid.
4450 --Parameters:
4451 --IN:
4452 -- p_packet_id.
4453 --IN OUT:
4454 -- None.
4455 --OUT:
4456 -- None.
4457 --Notes:
4458 -- The algorithm of the procedure is as follows :
4459 -- This procedure deletes from po_bc_distributions all rows corresponding to
4460 -- a specified packetid.
4461 --Testing:
4462 --
4463 --End of Comments
4464 -------------------------------------------------------------------------------
4465 PROCEDURE Delete_PO_BC_Distributions (
4466 p_packet_id IN NUMBER
4467 )
4468 IS
4469 BEGIN
4470
4471 -- Delete all records for the packet processed from po_bc_distributions
4472 DELETE FROM po_bc_distributions
4473 WHERE packet_id = p_packet_id;
4474
4475 END Delete_PO_BC_Distributions;
4476
4477 --<bug#5010001 START>
4478 -------------------------------------------------------------------------------
4479 --Start of Comments
4480 --Name: populate_bc_report_id
4481 --Pre-reqs:
4482 -- None.
4483 --Modifies:
4484 -- PO_BC_DISTRIBUTIONS.
4485 --Locks:
4486 -- None.
4487 --Function:
4488 -- This procedure populates online_report_id on po_bc_distributions rows corresponding to
4489 -- a given encumbrance transaction.
4490 --Parameters:
4491 --IN:
4492 -- p_online_report_id.
4493 -- Value of online report id unique identifier for the current transaction
4494 --IN OUT:
4495 -- None.
4496 --OUT:
4497 -- None.
4498 --Notes:
4499 -- The algorithm of the procedure is as follows :
4500 -- Get all distributions in the global temporary table and populate
4501 -- their counterparts in po_bc_distributions with online_report_id
4502 --Testing:
4503 --
4504 --End of Comments
4505 -------------------------------------------------------------------------------
4506
4507 PROCEDURE populate_bc_report_id (
4508 p_online_report_id IN NUMBER
4509 )IS
4510 l_module_name CONSTANT VARCHAR2(100) := 'populate_bc_report_id';
4511 l_log_head CONSTANT VARCHAR2(100) := g_log_head || l_module_name ;
4512
4513 l_progress VARCHAR2(3);
4514 BEGIN
4515 l_progress :='000';
4516 If g_debug_stmt Then
4517 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Start of procedure');
4518 PO_DEBUG.debug_var(l_log_head,l_progress,'p_online_report_id',p_online_report_id);
4519 End If;
4520 --bug#5523323 Remove the call to populate_aut_bc_report_id as this is not necessary.
4521 --we have removed the autonomous transaction.
4522 UPDATE
4523 PO_BC_DISTRIBUTIONS
4524 SET online_report_id=p_online_report_id
4525 WHERE reference15 in
4526 (
4527 SELECT reference15
4528 FROM po_encumbrance_gt
4529 WHERE send_to_gl_flag='Y'
4530 );
4531
4532 l_progress :='001';
4533
4534 If g_debug_stmt Then
4535 PO_DEBUG.debug_var(l_log_head,l_progress,'sql%rowcount',sql%rowcount);
4536 End If;
4537
4538 l_progress :='002';
4539
4540 If g_debug_stmt Then
4541 PO_DEBUG.debug_stmt(l_log_head,l_progress,'End of Procedure');
4542 End If;
4543 EXCEPTION
4544 WHEN OTHERS THEN
4545 --add message to the stack and log a debug msg if necessary
4546 po_message_s.sql_error(g_pkg_name, l_module_name, l_progress, SQLCODE, SQLERRM);
4547 fnd_msg_pub.add;
4548
4549 If g_debug_stmt Then
4550 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Exception raised:'||SQLCODE||' '||SQLERRM);
4551 End If;
4552
4553 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4554 END;
4555
4556 --<bug#5353223 START>
4557 -------------------------------------------------------------------------------
4558 --Start of Comments
4559 --Name: get_sign_for_amount
4560 --Pre-reqs:
4561 -- None.
4562 --Modifies:
4563 -- n/a.
4564 --Locks:
4565 -- None.
4566 --Function:
4567 -- This procedure returns the sign i.e -1/1 to be multiplied with amount based on
4568 -- the event type code/distribution type/adjustment status/main or backing doc.
4569 --Parameters:
4570 --IN:
4571 -- p_event_type_code
4572 -- Event type code that is created for the given transaction.
4573 -- p_main_or_backing_doc
4574 -- Indicates wether the document is a main/backing document.
4575 -- p_adjustment_status
4576 -- this could have a value of NEW/OLD
4577 -- p_distribution_type
4578 -- Indicates the distribution type. e.g STANDARD/AGREEMENT/REQUISITION etc.
4579 --IN OUT:
4580 -- None.
4581 --OUT:
4582 -- None.
4583 --Notes:
4584 -- The algorithm of the procedure is as follows :
4585 -- Populate all rows in po_bc_distributions matching the reference15 column with
4586 -- p_online_report_id
4587 --Testing:
4588 --
4589 --End of Comments
4590 -------------------------------------------------------------------------------
4591 FUNCTION get_sign_for_amount(p_event_type_code IN VARCHAR2,
4592 p_main_or_backing_doc IN VARCHAR2,
4593 p_adjustment_status IN VARCHAR2,
4594 p_distribution_type IN VARCHAR2) RETURN NUMBER IS
4595 l_multiplying_factor NUMBER := 0;
4596 l_cr_or_dr VARCHAR2(2);
4597 l_api_name CONSTANT varchar2(40) := 'get_sign_for_amount';
4598 l_log_head CONSTANT VARCHAR2(100) := g_log_head || l_api_name;
4599 l_progress VARCHAR2(3) := '000';
4600 BEGIN
4601 IF g_debug_stmt THEN
4602 PO_DEBUG.debug_begin(l_log_head);
4603 PO_DEBUG.debug_var(l_log_head,l_progress,'p_event_type_code',p_event_type_code);
4604 PO_DEBUG.debug_var(l_log_head,l_progress,'p_main_or_backing_doc',p_main_or_backing_doc);
4605 PO_DEBUG.debug_var(l_log_head,l_progress,'p_adjustment_status',p_adjustment_status);
4606 PO_DEBUG.debug_var(l_log_head,l_progress,'p_distribution_type',p_distribution_type);
4607 END IF;
4608 l_progress := '010';
4609 IF p_event_type_code = 'PO_REOPEN_FINAL_MATCH' AND
4610 p_main_or_backing_doc = 'M' THEN
4611 l_cr_or_dr := g_DEBIT;
4612 ELSIF (p_event_type_code = 'PO_PA_CANCELLED' OR
4613 p_event_type_code = 'PO_PA_CR_MEMO_CANCELLED') THEN
4614 IF p_main_or_backing_doc in ('B_PA','B_REQ' ) THEN
4615 l_cr_or_dr := g_CREDIT;
4616 ELSIF p_main_or_backing_doc = 'M' THEN
4617 l_cr_or_dr := g_DEBIT;
4618 END IF;
4619 ELSIF (p_event_type_code = 'PO_PA_UNRESERVED' OR
4620 p_event_type_code = 'PO_PA_REJECTED' OR
4621 p_event_type_code = 'PO_PA_INV_CANCELLED') AND
4622 p_main_or_backing_doc IN ('B_PA','B_REQ') THEN
4623 l_cr_or_dr := g_DEBIT;
4624 ELSIF (p_event_type_code = 'PO_PA_UNRESERVED' OR
4625 p_event_type_code = 'PO_PA_REJECTED' OR
4626 p_event_type_code = 'PO_PA_FINAL_CLOSED' OR
4627 p_event_type_code = 'PO_PA_INV_CANCELLED') AND
4628 p_main_or_backing_doc = 'M' THEN
4629 l_cr_or_dr := g_CREDIT;
4630 ELSIF p_event_type_code = 'PO_PA_RESERVED' THEN
4631 IF p_main_or_backing_doc IN ('B_PA','B_REQ') THEN
4632 l_cr_or_dr := g_CREDIT;
4633 ELSIF p_main_or_backing_doc = 'M' THEN
4634 l_cr_or_dr := g_DEBIT;
4635 END IF;
4636 ELSIF p_event_type_code = 'RELEASE_REOPEN_FINAL_CLOSED' AND
4637 p_main_or_backing_doc = 'M' THEN
4638 l_cr_or_dr := g_DEBIT;
4639 ELSIF (p_event_type_code = 'RELEASE_CANCELLED' OR
4640 p_event_type_code = 'RELEASE_CR_MEMO_CANCELLED') THEN
4641 IF p_main_or_backing_doc IN('B_PO' ,'B_PA','B_REQ') THEN
4642 l_cr_or_dr := g_CREDIT;
4643 ELSIF p_main_or_backing_doc = 'M' THEN
4644 l_cr_or_dr := g_DEBIT;
4645 END IF;
4646 ELSIF (p_event_type_code = 'RELEASE_UNRESERVED' OR
4647 p_event_type_code = 'RELEASE_REJECTED' OR
4648 p_event_type_code = 'RELEASE_INV_CANCELLED') AND
4649 p_main_or_backing_doc IN ('B_PO','B_PA','B_REQ') THEN
4650 l_cr_or_dr := g_DEBIT;
4651 ELSIF (p_event_type_code = 'RELEASE_REJECTED' OR
4652 p_event_type_code = 'RELEASE_UNRESERVED' OR
4653 p_event_type_code = 'RELEASE_FINAL_CLOSED' OR
4654 p_event_type_code = 'RELEASE_INV_CANCELLED') AND
4655 p_main_or_backing_doc = 'M' THEN
4656 l_cr_or_dr := g_CREDIT;
4657 ELSIF p_event_type_code = 'RELEASE_RESERVED' THEN
4658 IF p_main_or_backing_doc IN ('B_PO','B_PA','B_REQ') THEN
4659 l_cr_or_dr := g_CREDIT;
4660 ELSIF p_main_or_backing_doc = 'M' THEN
4661 l_cr_or_dr := g_DEBIT;
4662 END IF;
4663 ELSIF (p_event_type_code = 'REQ_RESERVED' OR
4664 p_event_type_code = 'REQ_ADJUSTED' AND p_adjustment_status = g_adjustment_status_NEW) AND
4665 p_main_or_backing_doc = 'M' THEN
4666 l_cr_or_dr := g_DEBIT;
4667 ELSIF (p_event_type_code = 'REQ_UNRESERVED' OR
4668 p_event_type_code = 'REQ_FINAL_CLOSED' OR
4669 (p_event_type_code = 'REQ_ADJUSTED' AND
4670 p_adjustment_status = g_adjustment_status_OLD) OR
4671 p_event_type_code = 'REQ_REJECTED' OR
4672 p_event_type_code = 'REQ_RETURNED') AND
4673 p_main_or_backing_doc = 'M' THEN
4674 l_cr_or_dr := g_CREDIT;
4675 ELSIF p_event_type_code = 'REQ_CANCELLED' THEN
4676 l_cr_or_dr := g_DEBIT;
4677 ELSIF p_event_type_code = 'PO_PA_REOPEN_FINAL_MATCH' THEN
4678 IF p_main_or_backing_doc = 'M' THEN
4679 l_cr_or_dr := g_DEBIT;
4680 ELSE
4681 l_cr_or_dr := g_CREDIT;
4682 END IF;
4683 --<bug#5646605 START>
4684 --PSA has added two new event type codes PO_PA_ADJUSTED and RELEASE_ADJUSTED
4685 --Need to determine the sign for these event type codes for the main and backing
4686 --documents
4687 ELSIF p_event_type_code = 'PO_PA_ADJUSTED' THEN
4688 IF p_adjustment_status = 'NEW' THEN
4689 IF p_main_or_backing_doc = 'M' THEN
4690 l_cr_or_dr := g_DEBIT;
4691 ELSE
4692 l_cr_or_dr := g_CREDIT;
4693 END IF;
4694 ELSE
4695 IF p_main_or_backing_doc = 'M' THEN
4696 l_cr_or_dr := g_CREDIT;
4697 ELSE
4698 l_cr_or_dr := g_DEBIT;
4699 END IF;
4700 END IF;
4701 ELSIF p_event_type_code='RELEASE_ADJUSTED' THEN
4702 IF p_adjustment_status = 'NEW' THEN
4703 IF p_main_or_backing_doc = 'M' THEN
4704 l_cr_or_dr := g_DEBIT;
4705 ELSE
4706 l_cr_or_dr := g_CREDIT;
4707 END IF;
4708 ELSE
4709 IF p_main_or_backing_doc = 'M' THEN
4710 l_cr_or_dr := g_CREDIT;
4711 ELSE
4712 l_cr_or_dr := g_DEBIT;
4713 END IF;
4714 END IF;
4715 --<bug#5646605 END>
4716 END IF;
4717
4718 l_progress := '020';
4719
4720 IF l_cr_or_dr = g_DEBIT THEN
4721 l_multiplying_factor := 1;
4722 ELSIF l_cr_or_dr = g_CREDIT THEN
4723 l_multiplying_factor := -1 ;
4724 ELSE
4725 RAISE FND_API.g_EXC_UNEXPECTED_ERROR;
4726 END IF;
4727
4728 l_progress := '030';
4729
4730 IF g_debug_stmt THEN
4731 PO_DEBUG.debug_end(l_log_head);
4732 PO_DEBUG.debug_var(l_log_head,l_progress,'l_cr_or_dr',l_cr_or_dr);
4733 PO_DEBUG.debug_var(l_log_head,l_progress,'l_multiplying_factor',l_multiplying_factor);
4734 END IF;
4735
4736 return l_multiplying_factor;
4737
4738 EXCEPTION
4739 WHEN OTHERS THEN
4740 --add message to the stack and log a debug msg if necessary
4741 po_message_s.sql_error(g_pkg_name, l_api_name, l_progress,SQLCODE, SQLERRM);
4742 fnd_msg_pub.add;
4743 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4744 END;
4745
4746 --<bug#5353223 END>
4747 --<SLA R12 End>
4748 --<bug#5646605 START added helper functions>
4749 -------------------------------------------------------------------------------
4750 --Start of Comments
4751 --Name: get_event_type_code
4752 --Pre-reqs:
4753 -- None.
4754 --Modifies:
4755 -- n/a.
4756 --Locks:
4757 -- None.
4758 --Function:
4759 -- This function gets the event_type_code given the distribution type and action.
4760 --Parameters:
4761 --IN:
4762 -- p_distribution_type
4763 -- Indicates the distribution type. e.g STANDARD/AGREEMENT/REQUISITION etc.
4764 -- p_action
4765 -- Indicates the action type. This could be RESERVE/UNRESERVE etc.
4766 --IN OUT:
4767 -- None.
4768 --OUT:
4769 -- None.
4770 --Notes:
4771 --Testing:
4772 --
4773 --End of Comments
4774 -------------------------------------------------------------------------------
4775 FUNCTION get_event_type_code(p_distribution_type IN VARCHAR2,
4776 p_action IN VARCHAR2)
4777 RETURN VARCHAR2 IS
4778 l_event_type_code varchar2(30);
4779 l_entity_type_code varchar2(30);
4780 l_doc_type VARCHAR2(30);
4781 l_doc_subtype VARCHAR2(30);
4782 l_api_name CONSTANT varchar2(40) := 'get_event_type_code';
4783 l_log_head CONSTANT VARCHAR2(100) := g_log_head || l_api_name;
4784 l_progress VARCHAR2(3) := '000';
4785 BEGIN
4786
4787 IF g_debug_stmt THEN
4788 PO_DEBUG.debug_begin(l_log_head);
4789 PO_DEBUG.debug_var(l_log_head,l_progress,'p_distribution_type',p_distribution_type);
4790 PO_DEBUG.debug_var(l_log_head,l_progress,'p_action',p_action);
4791 END IF;
4792
4793 get_doc_detail_from_dist_type(p_distribution_type => p_distribution_type,
4794 x_doc_type => l_doc_type,
4795 x_doc_subtype => l_doc_subtype);
4796 IF g_debug_stmt THEN
4797 PO_DEBUG.debug_var(l_log_head,l_progress,'l_doc_type',l_doc_type);
4798 PO_DEBUG.debug_var(l_log_head,l_progress,'l_doc_subtype',l_doc_subtype);
4799 END IF;
4800
4801 get_event_and_entity_codes(p_doc_type => l_doc_type,
4802 p_doc_subtype => l_doc_subtype,
4803 p_action => p_action,
4804 x_event_type_code => l_event_type_code,
4805 x_entity_type_code => l_entity_type_code);
4806
4807 IF g_debug_stmt THEN
4808 PO_DEBUG.debug_end(l_log_head);
4809 PO_DEBUG.debug_var(l_log_head,l_progress,'l_event_type_code',l_event_type_code);
4810 PO_DEBUG.debug_var(l_log_head,l_progress,'l_entity_type_code',l_entity_type_code);
4811 END IF;
4812
4813 return l_event_type_code;
4814 END;
4815 -------------------------------------------------------------------------------
4816 --Start of Comments
4817 --Name: get_entity_type_code
4818 --Pre-reqs:
4819 -- None.
4820 --Modifies:
4821 -- n/a.
4822 --Locks:
4823 -- None.
4824 --Function:
4825 -- This function gets the entity_type_code given the distribution type and action.
4826 --Parameters:
4827 --IN:
4828 -- p_distribution_type
4829 -- Indicates the distribution type. e.g STANDARD/AGREEMENT/REQUISITION etc.
4830 -- p_action
4831 -- Indicates the action type. This could be RESERVE/UNRESERVE etc.
4832 --IN OUT:
4833 -- None.
4834 --OUT:
4835 -- None.
4836 --Notes:
4837 --Testing:
4838 --
4839 --End of Comments
4840 -------------------------------------------------------------------------------
4841 FUNCTION get_entity_type_code(p_distribution_type IN VARCHAR2,
4842 p_action IN VARCHAR2)
4843 RETURN VARCHAR2 IS
4844 l_event_type_code varchar2(30);
4845 l_entity_type_code varchar2(30);
4846 l_doc_type VARCHAR2(30);
4847 l_doc_subtype VARCHAR2(30);
4848 l_api_name CONSTANT varchar2(40) := 'get_entity_type_code';
4849 l_log_head CONSTANT VARCHAR2(100) := g_log_head || l_api_name;
4850 l_progress VARCHAR2(3) := '000';
4851 BEGIN
4852
4853 IF g_debug_stmt THEN
4854 PO_DEBUG.debug_begin(l_log_head);
4855 PO_DEBUG.debug_var(l_log_head,l_progress,'p_distribution_type',p_distribution_type);
4856 PO_DEBUG.debug_var(l_log_head,l_progress,'p_action',p_action);
4857 END IF;
4858
4859 get_doc_detail_from_dist_type(p_distribution_type => p_distribution_type,
4860 x_doc_type => l_doc_type,
4861 x_doc_subtype => l_doc_subtype);
4862 IF g_debug_stmt THEN
4863 PO_DEBUG.debug_var(l_log_head,l_progress,'l_doc_type',l_doc_type);
4864 PO_DEBUG.debug_var(l_log_head,l_progress,'l_doc_subtype',l_doc_subtype);
4865 END IF;
4866
4867
4868 get_event_and_entity_codes(p_doc_type => l_doc_type,
4869 p_doc_subtype => l_doc_subtype,
4870 p_action => p_action,
4871 x_event_type_code => l_event_type_code,
4872 x_entity_type_code => l_entity_type_code);
4873 IF g_debug_stmt THEN
4874 PO_DEBUG.debug_end(l_log_head);
4875 PO_DEBUG.debug_var(l_log_head,l_progress,'l_event_type_code',l_event_type_code);
4876 PO_DEBUG.debug_var(l_log_head,l_progress,'l_entity_type_code',l_entity_type_code);
4877 END IF;
4878
4879 return l_entity_type_code;
4880 END;
4881 -------------------------------------------------------------------------------
4882 --Start of Comments
4883 --Name: get_doc_detail_from_dist_type
4884 --Pre-reqs:
4885 -- None.
4886 --Modifies:
4887 -- n/a.
4888 --Locks:
4889 -- None.
4890 --Function:
4891 -- This procedure derives the document type and subtype given the distribution type.
4892 --Parameters:
4893 --IN:
4894 -- p_distribution_type
4895 -- Indicates the distribution type. e.g STANDARD/AGREEMENT/REQUISITION etc.
4896 --IN OUT:
4897 -- None.
4898 --OUT:
4899 -- x_doc_type
4900 -- Indicates the document type e.g PO/PA/RELEASE etc
4901 -- x_doc_subtype
4902 -- Indicates the document sub type e.g STANDARD/BLANKET/SCHEDULED etc.
4903 --Notes:
4904 --Testing:
4905 --
4906 --End of Comments
4907 -------------------------------------------------------------------------------
4908 PROCEDURE get_doc_detail_from_dist_type(p_distribution_type IN VARCHAR2,
4909 x_doc_type OUT NOCOPY VARCHAR2 ,
4910 x_doc_subtype OUT NOCOPY VARCHAR2) IS
4911 l_doc_type VARCHAR2(30);
4912 l_doc_subtype VARCHAR2(30);
4913 l_api_name CONSTANT varchar2(40) := 'get_doc_detail_from_dist_type';
4914 l_log_head CONSTANT VARCHAR2(100) := g_log_head || l_api_name;
4915 l_progress VARCHAR2(3) := '000';
4916 BEGIN
4917 IF g_debug_stmt THEN
4918 PO_DEBUG.debug_begin(l_log_head);
4919 PO_DEBUG.debug_var(l_log_head,l_progress,'p_distribution_type',p_distribution_type);
4920 END IF;
4921
4922 CASE p_distribution_type
4923 WHEN g_dist_type_AGREEMENT THEN
4924 l_doc_type := g_doc_type_PA;
4925 l_doc_subtype := g_doc_subtype_BLANKET;
4926 WHEN g_dist_type_PLANNED THEN
4927 l_doc_type := g_doc_type_PO;
4928 l_doc_subtype := g_doc_subtype_PLANNED;
4929 WHEN g_dist_type_REQUISITION THEN
4930 l_doc_type := g_doc_type_REQUISITION;
4931 l_doc_subtype := 'PURCHASE';
4932 WHEN g_dist_type_BLANKET THEN
4933 l_doc_type := g_doc_type_RELEASE;
4934 l_doc_subtype := g_doc_subtype_BLANKET;
4935 WHEN g_dist_type_STANDARD THEN
4936 l_doc_type := g_doc_type_PO;
4937 l_doc_subtype := g_doc_subtype_STANDARD;
4938 WHEN g_dist_type_SCHEDULED THEN
4939 l_doc_type := g_doc_type_RELEASE;
4940 l_doc_subtype := g_doc_subtype_SCHEDULED;
4941 END CASE;
4942 x_doc_type := l_doc_type;
4943 x_doc_subtype := l_doc_subtype;
4944
4945 IF g_debug_stmt THEN
4946 PO_DEBUG.debug_end(l_log_head);
4947 PO_DEBUG.debug_var(l_log_head,l_progress,'l_doc_type',l_doc_type);
4948 PO_DEBUG.debug_var(l_log_head,l_progress,'l_doc_subtype',l_doc_subtype);
4949 END IF;
4950
4951 END;
4952
4953 --<SLA R12 Start>
4954 -------------------------------------------------------------------------------
4955 --Start of Comments
4956 --Name: get_event_and_entity_codes
4957 --Pre-reqs:
4958 -- None.
4959 --Modifies:
4960 -- None.
4961 --Locks:
4962 -- None.
4963 --Function:
4964 -- This procedure returns the values of entity_type_code and event_type_code
4965 -- based on the document type, document sub type and the action to be performed
4966 -- These two values are assigned to corresponding global variables which are.
4967 -- used by the new Budgetary Control API.
4968 --Parameters:
4969 --IN:
4970 -- p_doc_type : specifies the document type ('PO','REQ')
4971 -- p_doc_subtype : specifies the document sub type
4972 -- p_action : specifies the encumbrance action ('RESERVE','CANCEL',etc)
4973 --IN OUT:
4974 -- None.
4975 --OUT:
4976 -- x_entity_type_code : to return entity type code ('REQUISITION'/'PURCHASE_ORDER')
4977 -- x_event_type_code : to return event type code
4978 --Notes:
4979 -- The algorithm of the procedure is as follows :
4980 -- This procedure returns the entity_type_code and event_type_code based on the
4981 -- values of document type, document sub type and the action to be performed.
4982 --Testing:
4983 --
4984 --End of Comments
4985 -------------------------------------------------------------------------------
4986 PROCEDURE get_event_and_entity_codes(
4987 p_doc_type IN VARCHAR2,
4988 p_doc_subtype IN VARCHAR2,
4989 p_action IN VARCHAR2,
4990 x_entity_type_code OUT NOCOPY VARCHAR2,
4991 x_event_type_code OUT NOCOPY VARCHAR2
4992 )
4993 IS
4994 l_entity_str VARCHAR2(30);
4995 l_action_str VARCHAR2(30);
4996 l_error_flag VARCHAR2(1);
4997 l_api_name CONSTANT varchar2(40) := 'get_event_and_entity_codes';
4998 l_log_head CONSTANT VARCHAR2(100) := g_log_head || l_api_name;
4999 l_progress VARCHAR2(3) := '000';
5000 BEGIN
5001
5002 IF g_debug_stmt THEN
5003 PO_DEBUG.debug_begin(l_log_head);
5004 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_type',p_doc_type);
5005 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_subtype',p_doc_subtype);
5006 PO_DEBUG.debug_var(l_log_head,l_progress,'p_action',p_action);
5007 END IF;
5008
5009 l_error_flag := 'N' ;
5010 IF (p_doc_type = 'REQUISITION') THEN
5011 x_entity_type_code := 'REQUISITION';
5012 l_entity_str := 'REQ';
5013 ELSIF (p_doc_type IN ('PO','PA')) THEN
5014 x_entity_type_code := 'PURCHASE_ORDER';
5015 l_entity_str := 'PO_PA';
5016 ELSIF (p_doc_type = 'RELEASE') THEN
5017 x_entity_type_code := 'RELEASE'; -- Bug 5015010
5018 l_entity_str := 'RELEASE';
5019 ELSE
5020 l_error_flag := 'Y' ;
5021 END IF;
5022
5023 IF (p_action = g_action_RESERVE) THEN
5024 l_action_str := 'RESERVED';
5025 ELSIF (p_action = g_action_UNRESERVE) THEN
5026 l_action_str := 'UNRESERVED';
5027 ELSIF (p_action = g_action_ADJUST) THEN
5028 l_action_str := 'ADJUSTED';
5029 ELSIF (p_action = g_action_CANCEL) THEN
5030 l_action_str := 'CANCELLED';
5031 ELSIF (p_action = g_action_FINAL_CLOSE) THEN
5032 l_action_str := 'FINAL_CLOSED';
5033 ELSIF (p_action = g_action_UNDO_FINAL_CLOSE) THEN
5034 l_action_str := 'UNDO_FINAL_CLOSED';
5035 ELSIF (p_action = g_action_REJECT) THEN
5036 l_action_str := 'REJECTED';
5037 ELSIF (p_action = g_action_RETURN) THEN
5038 l_action_str := 'RETURNED';
5039 -- Bug 4684263 Begin
5040 ELSIF (p_action = g_action_INVOICE_CANCEL) THEN
5041 l_action_str := 'INV_CANCELLED';
5042 ELSIF (p_action = g_action_CR_MEMO_CANCEL) THEN
5043 l_action_str := 'CR_MEMO_CANCELLED';
5044 -- Bug 4684263 End
5045 ELSE
5046 l_error_flag := 'Y' ;
5047 END IF;
5048
5049 x_event_type_code := l_entity_str||'_'||l_action_str;
5050
5051 -- Special cases wherein seeded data is different from the above
5052 -- derivations of event type code
5053 IF (x_event_type_code='PO_PA_UNDO_FINAL_CLOSED') THEN
5054 x_event_type_code := 'PO_REOPEN_FINAL_MATCH' ;
5055 ELSIF (x_event_type_code='RELEASE_UNDO_FINAL_CLOSED') THEN
5056 x_event_type_code := 'RELEASE_REOPEN_FINAL_CLOSED' ;
5057 END IF;
5058
5059 IF (l_error_flag = 'Y') THEN
5060
5061 x_event_type_code := NULL ;
5062 x_entity_type_code := NULL ;
5063 END IF;
5064
5065 IF g_debug_stmt THEN
5066 PO_DEBUG.debug_end(l_log_head);
5067 PO_DEBUG.debug_var(l_log_head,l_progress,'x_event_type_code',x_event_type_code);
5068 PO_DEBUG.debug_var(l_log_head,l_progress,'x_entity_type_code',x_entity_type_code);
5069 END IF;
5070
5071 END get_event_and_entity_codes;
5072
5073 --<bug#5646605 END>
5074 END PO_ENCUMBRANCE_POSTPROCESSING;
[Home] [Help]