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