[Home] [Help]
PACKAGE BODY: APPS.PO_FUNDS_CHECKER
Source
1 PACKAGE BODY PO_FUNDS_CHECKER AS
2 /* $Header: POXPOFCB.pls 115.8 2003/03/06 10:07:11 krsethur ship $ */
3
4 -- Types :
5 --
6
7 -- Constants :
8 -- This is used as a delimiter in the Debug Info String
9
10 g_delim CONSTANT VARCHAR2(1) := '
11 ';
12
13
14 -- Private Global Variables :
15 --
16
17 -- Action
18
19 g_action VARCHAR2(25);
20
21 -- Packet ID
22
23 g_packetid gl_bc_packets.packet_id%TYPE;
24
25 -- Funds Check Return Code
26
27 g_return_code VARCHAR2(1);
28
29 -- Concurrent Processing of Funds Checker ?
30
31 g_conc_flag VARCHAR2(1);
32
33 -- Set of Books ID
34
35 g_sobid financials_system_parameters.set_of_books_id%TYPE;
36
37 -- PO Mode
38
39 g_pomode VARCHAR2(15);
40
41 -- Funds Check Mode
42
43 g_fcmode VARCHAR2(1);
44
45 -- Funds Check Level
46
47 g_fclevel VARCHAR2(15);
48
49 -- Partial Reservation Allowed ?
50
51 g_partial_resv_flag VARCHAR2(1);
52
53 -- Document Type
54
55 g_doctyp VARCHAR2(25);
56
57 -- Document Subtype
58
59 g_docsubtyp VARCHAR2(25);
60
61 -- Header ID
62
63 g_docid NUMBER;
64
65 -- Line ID
66
67 g_lineid NUMBER;
68
69 -- Shipment ID
70
71 g_shipid NUMBER;
72
73 -- Dist ID
74
75 g_distid NUMBER;
76
77 -- Override Period
78
79 g_override_period VARCHAR2(25);
80
81 -- Recreate Demand
82
83 g_recreate_demand VARCHAR2(1);
84
85 -- User ID
86
87 g_userid NUMBER;
88
89 -- Login ID
90
91 g_loginid NUMBER;
92
93 -- SQL String for Inserting into Funds Checker Queue
94
95 g_sql_insert VARCHAR2(10000);
96
97 -- Debug String
98 -- FRKHAN bug 941171 9/14/99
99 x_max_length CONSTANT NUMBER := 32760;
100 g_dbug VARCHAR2(32767) := null;
101
102
103 /* ----------------------------------------------------------------------- */
104 /* */
105 /* Private Function Definition */
106 /* */
107 /* ----------------------------------------------------------------------- */
108
109 FUNCTION po_fc_init RETURN BOOLEAN;
110
111
112 FUNCTION po_fc_sel(p_goodstmt IN OUT NOCOPY BOOLEAN) RETURN BOOLEAN;
113
114
115 FUNCTION po_fc_selreq RETURN BOOLEAN;
116
117
118 FUNCTION po_fc_selpo RETURN BOOLEAN;
119
120
121 FUNCTION po_fc_selblnkrel RETURN BOOLEAN;
122
123
124 FUNCTION po_fc_selschrel RETURN BOOLEAN;
125
126
127 FUNCTION po_fc_run(p_packetid IN OUT NOCOPY NUMBER) RETURN BOOLEAN;
128
129
130 FUNCTION po_rollup_enc RETURN BOOLEAN;
131
132
133 FUNCTION po_rollup_req RETURN BOOLEAN;
134
135
136 FUNCTION po_rollup_po RETURN BOOLEAN;
137
138
139 FUNCTION po_rollup_blnkrel RETURN BOOLEAN;
140
141
142 FUNCTION po_rollup_schrel RETURN BOOLEAN;
143
144
145 FUNCTION po_fc_dist RETURN BOOLEAN;
146
147
148 FUNCTION po_err_insert RETURN BOOLEAN;
149
150 /* ----------------------------------------------------------------------- */
151 /* */
152 /* Funds Control Action on an Entity */
153 /* */
154 /* If Funds Check succeeds, update the Source Distributions; otherwise */
155 /* log errors in the Online Reporting table and purge the packet */
156 /* */
157 /* ----------------------------------------------------------------------- */
158
159 -- Parameters :
160
161 -- p_docid : Header ID
162
163 -- p_doctyp : Document Type
164
165 -- p_docsubtyp : Document Subtype
166
167 -- p_lineid : Line ID
168
169 -- p_shipid : Shipment ID
170
171 -- p_distid : Distribution ID
172
173 -- p_action : Action
174
175 -- p_override_period : Override Period
176
177 -- p_recreate_demand : Recreate Demand ?
178
179 -- p_conc_flag : Concurrent Processing of Funds Checker
180
181 -- p_return_code : Funds Checker Return Code
182
183 FUNCTION po_funds_control(p_docid IN NUMBER,
184 p_doctyp IN VARCHAR2,
185 p_docsubtyp IN VARCHAR2,
186 p_lineid IN NUMBER,
187 p_shipid IN NUMBER,
188 p_distid IN NUMBER DEFAULT 0,
189 p_action IN VARCHAR2,
190 p_override_period IN VARCHAR2 DEFAULT NULL,
191 p_recreate_demand IN VARCHAR2 DEFAULT 'N',
192 p_conc_flag IN VARCHAR2 DEFAULT 'N',
193 p_return_code IN OUT NOCOPY VARCHAR2) RETURN BOOLEAN IS
194
195 l_fc_ok BOOLEAN := FALSE;
196 l_dummy VARCHAR2(1);
197 l_dummy2 VARCHAR2(1);
198 l_min_acct_unit NUMBER; -- Bug#2310026
199 l_precision NUMBER; -- Bug#2310026
200
201 cursor pkt_po(packet_id NUMBER) is
202 select distinct 'Y'
203 from gl_bc_packets
204 where exists
205 (select 'Y'
206 from gl_bc_packets
207 where reference1 = 'PO'
208 and reference5 is not null
209 and packet_id = packet_id);
210
211 cursor req_enc is
212 select 'Y'
213 from financials_system_parameters
214 where req_encumbrance_flag = 'Y';
215
216 BEGIN
217
218 -- dbms_output.put_line('in funds control');
219 -- FRKHAN BUG 941171 9/14/99
220 IF LENGTH (g_dbug) < x_max_length THEN
221 g_dbug := g_dbug ||
222 'Starting PO Funds Checker:' || g_delim ||
223 'Hdr:' || p_docid || g_delim ||
224 'Type:' || p_doctyp || g_delim ||
225 'Subtype:' || p_docsubtyp || g_delim ||
226 'Line:' || p_lineid || g_delim ||
227 'Ship:' || p_shipid || g_delim ||
228 'Dist:' || p_distid || g_delim ||
229 'Action:' || p_action || g_delim ||
230 'Override Period:' || p_override_period || g_delim ||
231 'Recreate Demand:' || p_recreate_demand || g_delim ||
232 'Conc:' || p_conc_flag || g_delim;
233 END IF;
234
235 -- Setup Global Variables
236
237 g_docid := p_docid;
238 g_doctyp := p_doctyp;
239 g_docsubtyp := p_docsubtyp;
240 g_lineid := p_lineid;
241 g_shipid := p_shipid;
242 g_distid := p_distid;
243 g_action := p_action;
244 g_override_period := p_override_period;
245 g_recreate_demand := p_recreate_demand;
246 g_conc_flag := p_conc_flag;
247
248 -- Check whether it is OK to invoke Funds Checker
249
250 -- dbms_output.put_line('before po_fc_ok');
251
252 if not po_fc_ok(p_doctyp => p_doctyp,
253 p_lineid => p_lineid,
254 p_shipid => p_shipid,
255 p_distid => p_distid,
256 p_action => p_action,
257 p_fc_ok => l_fc_ok) then
258
259 -- dbms_output.put_line('funds control not ok');
260
261 PO_MESSAGE_S.APP_ERROR(error_name => 'PO_ALL_TRACE_ERROR',
262 token1 => 'FILE',
263 value1 => 'PO_FUNDS_CHECKER',
264 token2 => 'ERR_NUMBER',
265 value2 => '005',
266 token3 => 'SUBROUTINE',
267 value3 => 'PO_FUNDS_CONTROL()');
268 return(FALSE);
269
270 end if;
271
272 if not l_fc_ok then
273 return(TRUE);
274 -- dbms_output.put_line('not l_fc_ok');
275
276 end if;
277
278
279 -- Initialize for Funds Check
280
281 -- dbms_output.put_line('init funds checker');
282
283 if not po_fc_init then
284
285 PO_MESSAGE_S.APP_ERROR(error_name => 'PO_ALL_TRACE_ERROR',
286 token1 => 'FILE',
287 value1 => 'PO_FUNDS_CHECKER',
291 value3 => 'PO_FUNDS_CONTROL()');
288 token2 => 'ERR_NUMBER',
289 value2 => '010',
290 token3 => 'SUBROUTINE',
292 return(FALSE);
293
294 end if;
295
296 -- dbms_output.put_line('insert into gl_bc_packtets');
297
298 -- Insert Records into gl_bc_packets
299
300 if not po_fc_ins(p_docid => p_docid,
301 p_doctyp => g_doctyp,
302 p_docsubtyp => g_docsubtyp,
303 p_lineid => g_lineid,
304 p_shipid => g_shipid,
305 p_distid => g_distid,
306 p_action => g_action,
307 p_override_period => g_override_period,
308 p_recreate_demand => g_recreate_demand,
309 p_packetid => g_packetid) then
310
311 PO_MESSAGE_S.APP_ERROR(error_name => 'PO_ALL_TRACE_ERROR',
312 token1 => 'FILE',
313 value1 => 'PO_FUNDS_CHECKER',
314 token2 => 'ERR_NUMBER',
315 value2 => '015',
316 token3 => 'SUBROUTINE',
317 value3 => 'PO_FUNDS_CONTROL()');
318
319 -- dbms_output.put_line('insert failed into bc packets');
320
321 return(FALSE);
322
323
324 end if;
325
326 /* Bug#2310026 : Following logic will round the entered_dr, entered_cr,
327 accounted_dr, accounted_cr to the functional currency
328 precision/minimum accountable unit, by updating these fields
329 for all the records inserted in the previous step using the
330 packet_id. */
331
332 SELECT FC.MINIMUM_ACCOUNTABLE_UNIT, FC.PRECISION
333 INTO l_min_acct_unit , l_precision
334 FROM GL_SETS_OF_BOOKS GLSOB,
335 FINANCIALS_SYSTEM_PARAMETERS FSP,
336 FND_CURRENCIES FC
337 WHERE GLSOB.set_of_books_id = FSP.set_of_books_id
338 AND FC.currency_code = GLSOB.currency_code;
339
340 IF (l_min_acct_unit is not null) THEN
341 UPDATE GL_BC_PACKETS
342 SET ENTERED_DR = ROUND(ENTERED_DR/l_min_acct_unit) * l_min_acct_unit ,
343 ENTERED_CR = ROUND(ENTERED_CR/l_min_acct_unit) * l_min_acct_unit,
344 ACCOUNTED_DR = ROUND(ACCOUNTED_DR/l_min_acct_unit ) * l_min_acct_unit ,
345 ACCOUNTED_CR = ROUND(ACCOUNTED_CR/l_min_acct_unit) * l_min_acct_unit
346 WHERE PACKET_ID = g_packetid;
347 ELSE
348 UPDATE GL_BC_PACKETS
349 SET ENTERED_DR = ROUND(ENTERED_DR,l_precision),
350 ENTERED_CR = ROUND(ENTERED_CR,l_precision),
351 ACCOUNTED_DR = ROUND(ACCOUNTED_DR,l_precision),
352 ACCOUNTED_CR = ROUND(ACCOUNTED_CR,l_precision)
353 WHERE PACKET_ID = g_packetid;
354 END IF;
355
356
357
361 'Packet:' || g_packetid || g_delim;
358 -- FRKHAN bug 941171
359 IF LENGTH (g_dbug) < x_max_length THEN
360 g_dbug := g_dbug ||
362 END IF;
363
364 -- If Packet includes a PO with backing requisition, Partial Reservation
365 -- should not be allowed
366
367 open pkt_po(g_packetid);
368
369 fetch pkt_po
370 into l_dummy;
371
372 close pkt_po;
373
374 open req_enc;
375
376 fetch req_enc
377 into l_dummy2;
378
379 close req_enc;
380
381 if ((l_dummy = 'Y') and
382 (l_dummy2 = 'Y')) then
383 g_partial_resv_flag := 'N';
384 end if;
385
386
387 -- Call Funds Checker
388 -- dbms_output.put_line('call funds checker');
389
390
391 if not GL_FUNDS_CHECKER_PKG.GLXFCK(p_sobid => g_sobid,
392 p_packetid => g_packetid,
393 p_mode => g_fcmode,
394 p_partial_resv_flag => g_partial_resv_flag,
395 p_conc_flag => g_conc_flag,
396 p_return_code => g_return_code) then
397
398 GL_FUNDS_CHECKER_PKG.GLXFPP(p_packetid => g_packetid);
399
400 PO_MESSAGE_S.APP_ERROR(error_name => 'PO_ALL_TRACE_ERROR',
401 token1 => 'FILE',
402 value1 => 'PO_FUNDS_CHECKER',
403 token2 => 'ERR_NUMBER',
404 value2 => '020',
405 token3 => 'SUBROUTINE',
406 value3 => 'PO_FUNDS_CONTROL()');
407 return(FALSE);
408
409 end if;
410
411
412 -- Check Return Code
413
414 if ((g_return_code in ('A', 'S')) and
415 (g_fcmode in ('R', 'A'))) then
416
417 if not po_rollup_enc then
418
419 PO_MESSAGE_S.APP_ERROR(error_name => 'PO_ALL_TRACE_ERROR',
420 token1 => 'FILE',
421 value1 => 'PO_FUNDS_CHECKER',
422 token2 => 'ERR_NUMBER',
423 value2 => '025',
424 token3 => 'SUBROUTINE',
425 value3 => 'PO_FUNDS_CONTROL()');
426 return(FALSE);
427
428 end if;
429
430 end if;
431
432 if ((g_return_code = 'P') and
433 (g_fcmode in ('R', 'A'))) then
434
435 if not po_rollup_enc then
436
437 PO_MESSAGE_S.APP_ERROR(error_name => 'PO_ALL_TRACE_ERROR',
438 token1 => 'FILE',
439 value1 => 'PO_FUNDS_CHECKER',
440 token2 => 'ERR_NUMBER',
441 value2 => '030',
442 token3 => 'SUBROUTINE',
443 value3 => 'PO_FUNDS_CONTROL()');
444 return(FALSE);
445
446 end if;
447
448
449 -- Insert into Errors table
450
454 token1 => 'FILE',
451 if not po_err_insert then
452
453 PO_MESSAGE_S.APP_ERROR(error_name => 'PO_ALL_TRACE_ERROR',
455 value1 => 'PO_FUNDS_CHECKER',
456 token2 => 'ERR_NUMBER',
457 value2 => '035',
458 token3 => 'SUBROUTINE',
459 value3 => 'PO_FUNDS_CONTROL()');
460 return(FALSE);
461
462 end if;
463
464 end if;
465
466 if g_return_code in ('T', 'F') then
467
468 -- Insert into Errors table
469
470 if not po_err_insert then
471
472 PO_MESSAGE_S.APP_ERROR(error_name => 'PO_ALL_TRACE_ERROR',
473 token1 => 'FILE',
474 value1 => 'PO_FUNDS_CHECKER',
475 token2 => 'ERR_NUMBER',
476 value2 => '040',
477 token3 => 'SUBROUTINE',
478 value3 => 'PO_FUNDS_CONTROL()');
479 return(FALSE);
480
481 end if;
482
483 GL_FUNDS_CHECKER_PKG.GLXFPP(p_packetid => g_packetid);
484 return(TRUE);
485
486 end if;
487
488 return(TRUE);
489
490
491 EXCEPTION
492
493 WHEN OTHERS THEN
494
495 if pkt_po%ISOPEN then
496 close pkt_po;
497 end if;
498
499 if req_enc%ISOPEN then
500 close req_enc;
501 end if;
502
503 PO_MESSAGE_S.SQL_ERROR(routine => 'PO_FUNDS_CHECKER',
504 location => '045',
505 error_code => SQLCODE);
506
507 return(FALSE);
508
509 END po_funds_control;
510
511 /* ----------------------------------------------------------------------- */
512 /* */
513 /* Check if it is OK to invoke Funds Checker */
514 /* */
515 /* ----------------------------------------------------------------------- */
516
517 -- Parameters :
518
519 -- p_doctyp : Document Type
520
521 -- p_lineid : Line ID
522
523 -- p_shipid : Shipment ID
524
525 -- p_distid : Distribution ID
526
527 -- p_action : Action
528
529 -- p_fc_ok : OK to invoke Funds Checker ?
530
531 FUNCTION po_fc_ok(p_doctyp IN VARCHAR2,
532 p_lineid IN NUMBER,
533 p_shipid IN NUMBER,
534 p_distid IN NUMBER DEFAULT 0,
535 p_action IN VARCHAR2,
536 p_fc_ok IN OUT NOCOPY BOOLEAN) RETURN BOOLEAN IS
537
538 BEGIN
539
540 p_fc_ok := TRUE;
541
542 if p_action not in ('RESERVE', 'APPROVE AND RESERVE', 'CANCEL',
543 'FINALLY CLOSE', 'CHECK FUNDS', 'RETURN', 'REJECT',
544 'UNENCUMBER REQ', 'LIQUIDATE REQ') then
545
549 return(TRUE);
546 -- dbms_output.put_line('paction failed');
547
548 p_fc_ok := FALSE;
550
551 end if;
552
553
554 -- Check for Action if Distribution Num is entered
555
556 if ((p_distid <> 0) and
557 (p_action not in ('CHECK FUNDS', 'LIQUIDATE REQ'))) then
558
559 -- dbms_output.put_line('second check failed');
560
561 p_fc_ok := FALSE;
562 return(TRUE);
563
564 end if;
565
566
567 -- Check for Action if Line Num is entered
568
569 if (((p_lineid <> 0) or (p_shipid <> 0))and
570 (p_action in ('RESERVE', 'RETURN', 'REJECT',
571 'APPROVE AND RESERVE'))) then
572
573 -- dbms_output.put_line('3 check failed');
574
575 p_fc_ok := FALSE;
576 return(TRUE);
577
578 end if;
579
580
581 -- Check for Requisitions
582
583 if ((p_action = 'RETURN') and
584 (p_doctyp <> 'REQUISITION')) then
585
586 -- dbms_output.put_line('4 check failed');
587 p_fc_ok := FALSE;
588 return(TRUE);
589
590 end if;
591
592 -- dbms_output.put_line('return true from po_fc_ok');
593
594 return(TRUE);
595
596 END po_fc_ok;
597
598 /* ----------------------------------------------------------------------- */
599 /* */
600 /* Insert into the Funds Checker queue */
601 /* */
602 /* ----------------------------------------------------------------------- */
603
604 -- Parameters :
605
606 -- p_doctyp : Header Type
607
608 -- p_docsubtyp : Header Subtype
609
610 -- p_lineid : Line ID
611
612 -- p_shipid : Shipment ID
613
614 -- p_distid : Distribution ID
615
616 -- p_action : Action
617
618 -- p_override_period : Override Period
619
620 -- p_recreate_demand : Recreate Demand ?
621
622 -- p_packetid : Funds Checker Queue Packet ID
623
624 FUNCTION po_fc_ins(p_docid IN NUMBER,
625 p_doctyp IN VARCHAR2,
626 p_docsubtyp IN VARCHAR2,
627 p_lineid IN NUMBER,
628 p_shipid IN NUMBER,
629 p_distid IN NUMBER DEFAULT 0,
630 p_action IN VARCHAR2,
631 p_override_period IN VARCHAR2,
632 p_recreate_demand IN VARCHAR2,
636
633 p_packetid IN OUT NOCOPY NUMBER) RETURN BOOLEAN IS
634
635 l_goodstmt BOOLEAN := FALSE;
637 BEGIN
638
639 -- Setup Global Variables
640
641 if g_docid is null then
642 g_docid := p_docid;
643 end if;
644
645 if g_doctyp is null then
646 g_doctyp := p_doctyp;
647 end if;
648
649 if g_docsubtyp is null then
650 g_docsubtyp := p_docsubtyp;
651 end if;
652
653 if g_lineid is null then
654 g_lineid := p_lineid;
655 end if;
656
657 if g_shipid is null then
658 g_shipid := p_shipid;
659 end if;
660
661 if g_distid is null then
662 g_distid := p_distid;
663 end if;
664
665 if g_action is null then
666 g_action := p_action;
667 end if;
668
669 if g_override_period is null then
670 g_override_period := p_override_period;
671 end if;
672
673 if g_recreate_demand is null then
674 g_recreate_demand := p_recreate_demand;
675 end if;
676
677
678 -- Insert Clause
679 -- dbms_output.put_line('before setting up insert');
680
681 g_sql_insert := 'insert into gl_bc_packets ' ||
682 '(packet_id, ' ||
683 'set_of_books_id, ' ||
684 'je_source_name, ' ||
685 'je_category_name, ' ||
686 'code_combination_id, ' ||
687 'actual_flag, ' ||
688 'period_name, ' ||
689 'period_year, ' ||
690 'period_num, ' ||
691 'quarter_num, ' ||
692 'currency_code, ' ||
693 'status_code, ' ||
694 'last_update_date, ' ||
695 'last_updated_by, ' ||
696 'budget_version_id, ' ||
697 'encumbrance_type_id, ' ||
698 'entered_dr, ' ||
699 'entered_cr, ' ||
700 'accounted_dr, ' ||
701 'accounted_cr, ' ||
702 'ussgl_transaction_code, ' ||
703 'reference1, ' ||
704 'reference2, ' ||
705 'reference3, ' ||
706 'reference4, ' ||
707 'reference5, ' ||
708 'je_line_description) ';
709
710
711 -- Check Mode for Funds Check
712
713 if g_pomode is null then
714
715 -- dbms_output.put_line('g_pomode is null');
716
717 if not po_fc_init then
718
719 -- dbms_output.put_line('po-fc_init = false');
720
721 PO_MESSAGE_S.APP_ERROR(error_name => 'PO_ALL_TRACE_ERROR',
722 token1 => 'FILE',
726 token3 => 'SUBROUTINE',
723 value1 => 'PO_FUNDS_CHECKER',
724 token2 => 'ERR_NUMBER',
725 value2 => '050',
727 value3 => 'PO_FC_INS()');
728
729 return(FALSE);
730
731 end if;
732
733 end if;
734
735
736
737 -- Check Level for Funds Check
738 -- dbms_output.put_line('check level of funds control');
739
740 if not po_fc_level(p_docid => p_docid,
741 p_lineid => p_lineid,
742 p_shipid => p_shipid,
743 p_distid => p_distid,
744 p_fclevel => g_fclevel) then
745
746 PO_MESSAGE_S.APP_ERROR(error_name => 'PO_ALL_TRACE_ERROR',
747 token1 => 'FILE',
748 value1 => 'PO_FUNDS_CHECKER',
749 token2 => 'ERR_NUMBER',
750 value2 => '055',
751 token3 => 'SUBROUTINE',
752 value3 => 'PO_FC_INS()');
753
754 -- dbms_output.put_line('fc level failed');
755
756 return(FALSE);
757
758 end if;
759
760
761 -- Get Select Clause of the Insert Statement
762
763 -- dbms_output.put_line('before get select clause');
764
765 if not po_fc_sel(p_goodstmt => l_goodstmt) then
766
767 PO_MESSAGE_S.APP_ERROR(error_name => 'PO_ALL_TRACE_ERROR',
768 token1 => 'FILE',
769 value1 => 'PO_FUNDS_CHECKER',
770 token2 => 'ERR_NUMBER',
771 value2 => '060',
772 token3 => 'SUBROUTINE',
773 value3 => 'PO_FC_INS()');
774 return(FALSE);
775
776 end if;
777
778
779 -- Insert into the Funds Checker queue
780
781 -- dbms_output.put_line('before insert into funds checker queue');
782
783 if l_goodstmt then
784
785 -- dbms_output.put_line('after l_goodstmt');
786
787 if not po_fc_run(p_packetid => p_packetid) then
788
789
790 PO_MESSAGE_S.APP_ERROR(error_name => 'PO_ALL_TRACE_ERROR',
791 token1 => 'FILE',
792 value1 => 'PO_FUNDS_CHECKER',
793 token2 => 'ERR_NUMBER',
794 value2 => '065',
795 token3 => 'SUBROUTINE',
796 value3 => 'PO_FC_INS()');
797 -- dbms_output.put_line('po_fc_run return false');
798
799 return(FALSE);
800
801 end if;
802
803 end if;
804
805 return(TRUE);
806
807 END po_fc_ins;
808
809 /* ----------------------------------------------------------------------- */
810 /* */
814
811 /* Check Level for Funds Check */
812 /* */
813 /* ----------------------------------------------------------------------- */
815 -- Parameters :
816
817 -- p_docid : Header ID
818
819 -- p_lineid : Line ID
820
821 -- p_shipid : Shipment ID
822
823 -- p_distid : Distribution ID
824
825 -- p_fclevel : Funds Check Level
826
827 FUNCTION po_fc_level(p_docid IN NUMBER,
828 p_lineid IN NUMBER,
829 p_shipid IN NUMBER,
830 p_distid IN NUMBER DEFAULT 0,
831 p_fclevel IN OUT NOCOPY VARCHAR2) RETURN BOOLEAN IS
832
833 BEGIN
834
835 if p_distid <> 0 then
836 p_fclevel := 'DISTRIBUTION';
837 elsif p_shipid <> 0 then
838 p_fclevel := 'SHIPMENT';
839 elsif p_lineid <> 0 then
840 p_fclevel := 'LINE';
841 elsif p_docid <> 0 then
842 p_fclevel := 'HEADER';
843 else
844 return(FALSE);
845 end if;
846
847 return(TRUE);
848
849 END po_fc_level;
850
851 /* ----------------------------------------------------------------------- */
852
853 -- Initialize for Funds Check
854
855 FUNCTION po_fc_init RETURN BOOLEAN IS
856
857 cursor pkt is
858 select gl_bc_packets_s.nextval,
859 fsp.set_of_books_id
860 from financials_system_parameters fsp;
861
862 BEGIN
863
864 open pkt;
865
866 fetch pkt
867 into g_packetid, g_sobid;
868
869 close pkt;
870
871
872 -- Get Funds Check Mode from Action
873
874 if g_action in ('RESERVE', 'APPROVE AND RESERVE', 'CHECK FUNDS') then
875 g_pomode := 'RESERVE';
876 elsif g_action in ('CANCEL', 'UNENCUMBER REQ') then
877 g_pomode := 'REVERSE';
878 elsif g_action in ('FINALLY CLOSE', 'RETURN', 'LIQUIDATE REQ') then
879 g_pomode := 'LIQUIDATE';
880 elsif g_action = 'REJECT' then
881 g_pomode := 'REJECT';
882 end if;
883
884 if g_pomode = 'RESERVE' then
885
886 if g_action = 'CHECK FUNDS' then
887 g_fcmode := 'C';
888 else
889 g_fcmode := 'R';
890 end if;
891
892 else
893 g_fcmode := 'A';
894 end if;
895
896 if g_pomode = 'REVERSE' then
897 g_partial_resv_flag := 'N';
898 else
899 g_partial_resv_flag := 'Y';
900 end if;
901
902 return(TRUE);
903
904
905 EXCEPTION
906
907 WHEN OTHERS THEN
911 end if;
908
909 if pkt%ISOPEN then
910 close pkt;
912
913 PO_MESSAGE_S.SQL_ERROR(routine => 'PO_FUNDS_CHECKER',
914 location => '070',
915 error_code => SQLCODE);
916
917 return(FALSE);
918
919 END po_fc_init;
920
921 /* ----------------------------------------------------------------------- */
922
923 -- Get Select Clause for Insert Statement
924
925 FUNCTION po_fc_sel(p_goodstmt IN OUT NOCOPY BOOLEAN) RETURN BOOLEAN IS
926
927 BEGIN
928
929 p_goodstmt := FALSE;
930
931 if g_doctyp = 'REQUISITION' then
932
933 if not po_fc_selreq then
934
935 PO_MESSAGE_S.APP_ERROR(error_name => 'PO_ALL_TRACE_ERROR',
936 token1 => 'FILE',
937 value1 => 'PO_FUNDS_CHECKER',
938 token2 => 'ERR_NUMBER',
939 value2 => '075',
940 token3 => 'SUBROUTINE',
941 value3 => 'PO_FC_SEL()');
942 return(FALSE);
943
944 end if;
945
946 elsif g_doctyp = 'PO' then
947
948 if not po_fc_selpo then
949
950 PO_MESSAGE_S.APP_ERROR(error_name => 'PO_ALL_TRACE_ERROR',
951 token1 => 'FILE',
952 value1 => 'PO_FUNDS_CHECKER',
953 token2 => 'ERR_NUMBER',
954 value2 => '080',
955 token3 => 'SUBROUTINE',
956 value3 => 'PO_FC_SEL()');
957 return(FALSE);
958
959 end if;
960
961 elsif g_doctyp = 'RELEASE' then
962
963 if g_docsubtyp = 'BLANKET' then
964
965 if not po_fc_selblnkrel then
966
967 PO_MESSAGE_S.APP_ERROR(error_name => 'PO_ALL_TRACE_ERROR',
968 token1 => 'FILE',
969 value1 => 'PO_FUNDS_CHECKER',
970 token2 => 'ERR_NUMBER',
971 value2 => '085',
972 token3 => 'SUBROUTINE',
973 value3 => 'PO_FC_SEL()');
974 return(FALSE);
975
976 end if;
977
978 elsif g_docsubtyp = 'SCHEDULED' then
979
980 if not po_fc_selschrel then
981
982 PO_MESSAGE_S.APP_ERROR(error_name => 'PO_ALL_TRACE_ERROR',
983 token1 => 'FILE',
984 value1 => 'PO_FUNDS_CHECKER',
985 token2 => 'ERR_NUMBER',
986 value2 => '090',
987 token3 => 'SUBROUTINE',
988 value3 => 'PO_FC_SEL()');
989 return(FALSE);
990
991 end if;
992
993 else
994
995 PO_MESSAGE_S.APP_ERROR(error_name => 'PO_ALL_TRACE_ERROR',
996 token1 => 'FILE',
997 value1 => 'PO_FUNDS_CHECKER',
998 token2 => 'ERR_NUMBER',
999 value2 => '095',
1000 token3 => 'SUBROUTINE',
1001 value3 => 'PO_FC_SEL()');
1002 return(FALSE);
1003
1004 end if;
1005
1006 else
1007
1008 PO_MESSAGE_S.APP_ERROR(error_name => 'PO_ALL_TRACE_ERROR',
1009 token1 => 'FILE',
1013 token3 => 'SUBROUTINE',
1010 value1 => 'PO_FUNDS_CHECKER',
1011 token2 => 'ERR_NUMBER',
1012 value2 => '100',
1014 value3 => 'PO_FC_SEL()');
1015 return(FALSE);
1016
1017 end if;
1018
1019 p_goodstmt := TRUE;
1020
1021 return(TRUE);
1022
1023
1024 EXCEPTION
1025
1026 WHEN OTHERS THEN
1027
1028 PO_MESSAGE_S.SQL_ERROR(routine => 'PO_FUNDS_CHECKER',
1029 location => '105',
1030 error_code => SQLCODE);
1031
1032 return(FALSE);
1033
1034 END po_fc_sel;
1035
1036 /* ----------------------------------------------------------------------- */
1037
1038 -- Build Select Statement for Requisition
1039
1040 -- Status Code is 'P' for Pending Funds Reservation and 'C' for Pending
1041 -- Funds Check
1042
1043 -- Round the Entered Amounts to the precision of the Currency that they
1044 -- are in because the Unit Price could be specified with a larger precision
1045
1046 -- Accounted DR and CR are the same as the Entered Amounts because there is
1047 -- no Currency Conversion for Requisitions
1048
1049 FUNCTION po_fc_selreq RETURN BOOLEAN IS
1050
1051 l_stmt VARCHAR2(3000);
1052
1053 BEGIN
1054
1055 l_stmt := 'select :packet_id, ' ||
1056 'glsob.set_of_books_id, ' ||
1057 '''Purchasing'', ' ||
1058 '''Requisitions'', ' ||
1059 'pord.budget_account_id, ' ||
1060 '''E'', ' ||
1061 'glp.period_name, ' ||
1062 'glp.period_year, ' ||
1063 'glp.period_num, ' ||
1064 'glp.quarter_num, ' ||
1065 'glsob.currency_code, ' ||
1066 ':status_code, ' ||
1067 'sysdate, ' ||
1068 ':user_id, ' ||
1069 'null, ' ||
1070 'fsp.req_encumbrance_type_id, ' ||
1071 'decode(base_cur.minimum_accountable_unit, null, ' ||
1072 'round((porl.unit_price + ' ||
1073 'po_tax_sv.get_tax(''REQ'',PORD.distribution_id) ' ||
1074 '/ PORD.req_line_quantity) * :dr_quantity, ' ||
1075 'base_cur.precision), round((porl.unit_price + ' ||
1076 'po_tax_sv.get_tax(''REQ'',PORD.distribution_id) ' ||
1077 '/ PORD.req_line_quantity) * :dr_quantity / ' ||
1078 'base_cur.minimum_accountable_unit) * ' ||
1079 'base_cur.minimum_accountable_unit), ' ||
1080 'decode(base_cur.minimum_accountable_unit, null, ' ||
1081 'round((porl.unit_price + ' ||
1082 'po_tax_sv.get_tax(''REQ'',PORD.distribution_id) ' ||
1083 '/ PORD.req_line_quantity) * :cr_quantity, ' ||
1084 'base_cur.precision), round((porl.unit_price + ' ||
1085 'po_tax_sv.get_tax(''REQ'',PORD.distribution_id) ' ||
1086 '/ PORD.req_line_quantity) * :cr_quantity / ' ||
1090 'round((porl.unit_price + ' ||
1087 'base_cur.minimum_accountable_unit) * ' ||
1088 'base_cur.minimum_accountable_unit), ' ||
1089 'decode(base_cur.minimum_accountable_unit, null, ' ||
1091 'po_tax_sv.get_tax(''REQ'',PORD.distribution_id) ' ||
1092 '/ PORD.req_line_quantity) * :dr_quantity, ' ||
1093 'base_cur.precision), round((porl.unit_price + ' ||
1094 'po_tax_sv.get_tax(''REQ'',PORD.distribution_id) ' ||
1095 '/ PORD.req_line_quantity) * :dr_quantity / ' ||
1096 'base_cur.minimum_accountable_unit) * ' ||
1097 'base_cur.minimum_accountable_unit), ' ||
1098 'decode(base_cur.minimum_accountable_unit, null, ' ||
1099 'round((porl.unit_price + ' ||
1100 'po_tax_sv.get_tax(''REQ'',PORD.distribution_id) ' ||
1101 '/ PORD.req_line_quantity) * :cr_quantity, ' ||
1102 'base_cur.precision), round((porl.unit_price + ' ||
1103 'po_tax_sv.get_tax(''REQ'',PORD.distribution_id) ' ||
1104 '/ PORD.req_line_quantity) * :cr_quantity / ' ||
1105 'base_cur.minimum_accountable_unit) * ' ||
1106 'base_cur.minimum_accountable_unit), ' ||
1107 'pord.ussgl_transaction_code, ' ||
1108 '''REQ'', ' ||
1109 'porl.requisition_header_id, ' ||
1110 'pord.distribution_id, ' ||
1111 'prh.segment1, ' ||
1112 'porl.reference_num, ' ||
1113 'substr(porl.item_description, 1, 40) ' ||
1114 'from gl_periods glp, ' ||
1115 'gl_sets_of_books glsob, ' ||
1116 'financials_system_parameters fsp, ' ||
1117 'fnd_currencies base_cur, ' ||
1118 'po_req_distributions pord, ' ||
1119 'po_requisition_lines porl, ' ||
1120 'po_requisition_headers prh ' ||
1121 'where glsob.set_of_books_id = fsp.set_of_books_id ' ||
1122 'and glp.period_set_name = glsob.period_set_name ' ||
1123 'and glp.period_name = nvl(:override_period, pord.gl_encumbered_period_name) ' ||
1124 'and base_cur.currency_code = glsob.currency_code ' ||
1125 'and nvl(pord.encumbered_flag, ''N'') = '':encumbrance_state'' ' ||
1126 'and nvl(porl.cancel_flag, ''N'') = '':cancel_state'' ' ||
1127 'and porl.line_location_id is null ' ||
1128 'and :entity_level = :object_id ' ||
1129 'and porl.requisition_line_id = pord.requisition_line_id ' ||
1130 'and nvl(pord.prevent_encumbrance_flag, ''N'') = ''N'' ' ||
1131 'and nvl(fsp.req_encumbrance_flag, ''N'') = ''Y'' ' ||
1132 'and porl.requisition_header_id = prh.requisition_header_id';
1133
1134
1135 -- Substitute the tokens
1136
1137 if g_pomode = 'RESERVE' then
1138
1139 l_stmt := replace(l_stmt, ':dr_quantity', 'pord.req_line_quantity');
1140 l_stmt := replace(l_stmt, ':cr_quantity', '0');
1141 l_stmt := replace(l_stmt, ':encumbrance_state', 'N');
1142 l_stmt := replace(l_stmt, ':cancel_state', 'N');
1143
1144 elsif g_pomode = 'REVERSE' then
1145
1146 l_stmt := replace(l_stmt, ':dr_quantity', 'decode(porl.quantity_cancelled, null, -(pord.req_line_quantity), -(porl.quantity_cancelled * pord.req_line_quantity / porl.quantity))');
1147 l_stmt := replace(l_stmt, ':cr_quantity', '0');
1148 l_stmt := replace(l_stmt, ':encumbrance_state', 'Y');
1149 l_stmt := replace(l_stmt, ':cancel_state', 'I');
1150
1151 elsif g_pomode = 'LIQUIDATE' then
1152
1153 l_stmt := replace(l_stmt, ':dr_quantity', '0');
1154 l_stmt := replace(l_stmt, ':cr_quantity', '(pord.req_line_quantity - ((pord.req_line_quantity / porl.quantity) * porl.quantity_delivered))');
1155 l_stmt := replace(l_stmt, ':encumbrance_state', 'Y');
1156 l_stmt := replace(l_stmt, ':cancel_state', 'N');
1157
1158 elsif g_pomode = 'REJECT' then
1159
1160 l_stmt := replace(l_stmt, ':dr_quantity', '0');
1161 l_stmt := replace(l_stmt, ':cr_quantity', '(pord.req_line_quantity - ((pord.req_line_quantity / porl.quantity) * porl.quantity_delivered))');
1162 l_stmt := replace(l_stmt, ':encumbrance_state', 'Y');
1163 l_stmt := replace(l_stmt, ':cancel_state', 'N');
1164
1165 end if;
1166
1167 if g_fclevel = 'DISTRIBUTION' then
1168
1169 l_stmt := replace(l_stmt, ':entity_level', 'pord.distribution_id');
1170
1174
1171 elsif g_fclevel = 'SHIPMENT' then
1172
1173 l_stmt := replace(l_stmt, ':entity_level', '');
1175 elsif g_fclevel = 'LINE' then
1176
1177 l_stmt := replace(l_stmt, ':entity_level', 'porl.requisition_line_id');
1178
1179 elsif g_fclevel = 'HEADER' then
1180
1181 l_stmt := replace(l_stmt, ':entity_level', 'prh.requisition_header_id');
1182
1183 end if;
1184
1185 g_sql_insert := g_sql_insert ||
1186 l_stmt;
1187
1188 return(TRUE);
1189
1190 END po_fc_selreq;
1191
1192 /* ----------------------------------------------------------------------- */
1193
1194 -- Build Select Statement for PO
1195
1196 -- Status Code is 'P' for Pending Funds Reservation and 'C' for Pending
1197 -- Funds Check
1198
1199 -- Round the Entered Amounts to the precision of the Currency that they
1200 -- are in because the Unit Price could be specified with a larger precision
1201
1202 FUNCTION po_fc_selpo RETURN BOOLEAN IS
1203
1204 l_stmt VARCHAR2(4000);
1205 l_bstmt VARCHAR2(3000);
1206
1207 BEGIN
1208 /* Bug#2181793 : Modified the l_stmt and the b_stmt to take account
1209 of the tax portion also for encumbrance. similar modifications done in
1210 the functions po_fc_selblnkrel and po_fc_selschrel */
1211
1212 /* Bug#2310026 :Entered_amount should be first rounded to entered_currency
1213 precision/minimum_accountable_unit. This should be then multiplied with the
1214 POD.rate to get the functional amount which should be rounded off to the
1215 base currency precision. Accordingly, changed the BASE_CUR table to DOC_CUR
1216 in the calculatation of the fields entered_dr, entered_cr, accounted_dr,
1217 accounted_cr and moved the nvl(POD.rate) out of the round function. The
1218 entered_amounts thus got are then multiplied with the nvl(POD.rate,1). This
1219 amount should be rounded off to the functional currency precision. Now
1220 incorporating the code to round this value to functional currency precision,
1221 will require two more decode statements for each decode statement. Thus,
1222 to avoid this, we multiply with the rate alone here. This functional amount
1223 will be rounded to the functional currency precision/minimum accountable unit
1224 after inserting the records in the gl_bc_packets in the procedure
1225 po_funds_control after the call po_fc_ins(insertion of gl_bc_packet).
1226
1227 Similar changes made in the function po_fc_selblnkrel and po_fc_selschrel */
1228
1229 l_stmt := 'select :packet_id, ' ||
1230 'glsob.set_of_books_id, ' ||
1231 '''Purchasing'', ' ||
1232 '''Purchases'', ' ||
1233 'pod.budget_account_id, ' ||
1234 '''E'', ' ||
1235 'glp.period_name, ' ||
1236 'glp.period_year, ' ||
1237 'glp.period_num, ' ||
1238 'glp.quarter_num, ' ||
1239 'glsob.currency_code, ' ||
1240 ':status_code, ' ||
1241 'sysdate, ' ||
1242 ':user_id, ' ||
1243 'null, ' ||
1244 'fsp.purch_encumbrance_type_id, ' ||
1245 'decode(doc_cur.minimum_accountable_unit, null, ' ||
1246 'round((poll.price_override + ' ||
1247 'po_tax_sv.get_tax(''PO'',POD.po_distribution_id)'||
1248 '/ POD.quantity_ordered) * :dr_quantity ' ||
1249 ', doc_cur.precision) * nvl(pod.rate, 1) , ' ||
1250 'round((poll.price_override + ' ||
1251 'po_tax_sv.get_tax(''PO'',POD.po_distribution_id)'||
1252 '/ POD.quantity_ordered) * :dr_quantity' ||
1256 'decode(doc_cur.minimum_accountable_unit, null, ' ||
1253 ' / doc_cur.minimum_accountable_unit) ' ||
1254 ' * doc_cur.minimum_accountable_unit' ||
1255 ' * nvl(pod.rate, 1) ), ' ||
1257 'round((poll.price_override + ' ||
1258 'po_tax_sv.get_tax(''PO'',POD.po_distribution_id)'||
1259 '/ POD.quantity_ordered) * :cr_quantity ' ||
1260 ', doc_cur.precision) * nvl(pod.rate, 1) , ' ||
1261 'round((poll.price_override + ' ||
1262 'po_tax_sv.get_tax(''PO'',POD.po_distribution_id)'||
1263 '/ POD.quantity_ordered) * :cr_quantity' ||
1264 ' / doc_cur.minimum_accountable_unit) ' ||
1265 ' * doc_cur.minimum_accountable_unit' ||
1266 ' * nvl(pod.rate, 1) ), ' ||
1267 'decode(doc_cur.minimum_accountable_unit, null, ' ||
1268 'round((poll.price_override + ' ||
1269 'po_tax_sv.get_tax(''PO'',POD.po_distribution_id)'||
1270 '/ POD.quantity_ordered) * :dr_quantity ' ||
1271 ', doc_cur.precision) * nvl(pod.rate, 1) , ' ||
1272 'round((poll.price_override + ' ||
1273 'po_tax_sv.get_tax(''PO'',POD.po_distribution_id)'||
1274 '/ POD.quantity_ordered) * :dr_quantity' ||
1275 ' / doc_cur.minimum_accountable_unit) ' ||
1276 ' * doc_cur.minimum_accountable_unit' ||
1277 ' * nvl(pod.rate, 1) ), ' ||
1278 'decode(doc_cur.minimum_accountable_unit, null, ' ||
1279 'round((poll.price_override + ' ||
1280 'po_tax_sv.get_tax(''PO'',POD.po_distribution_id)'||
1281 '/ POD.quantity_ordered) * :cr_quantity ' ||
1282 ', doc_cur.precision) * nvl(pod.rate, 1) , ' ||
1283 'round((poll.price_override + ' ||
1284 'po_tax_sv.get_tax(''PO'',POD.po_distribution_id)'||
1285 '/ POD.quantity_ordered) * :cr_quantity' ||
1286 ' / doc_cur.minimum_accountable_unit) ' ||
1287 ' * doc_cur.minimum_accountable_unit' ||
1288 ' * nvl(pod.rate, 1) ), ' ||
1289 'pod.ussgl_transaction_code, ' ||
1290 '''PO'', ' ||
1291 'poll.po_header_id, ' ||
1292 'pod.po_distribution_id, ' ||
1293 'poh.segment1, ' ||
1294 'decode(pod.req_distribution_id, null, null, ' ||
1295 'prh.segment1), ' ||
1296 'substr(pol.item_description, 1, 40) ' ||
1297 'from gl_periods glp, ' ||
1298 'gl_sets_of_books glsob, ' ||
1299 'financials_system_parameters fsp, ' ||
1300 'fnd_currencies doc_cur, ' ||
1301 'po_distributions pod, ' ||
1302 'po_line_locations poll, ' ||
1303 'po_lines pol, ' ||
1304 'po_headers poh, ' ||
1305 'po_requisition_headers prh, ' ||
1306 'po_requisition_lines porl, ' ||
1307 'po_req_distributions pord ' ||
1308 'where glsob.set_of_books_id = fsp.set_of_books_id ' ||
1309 'and glp.period_set_name = glsob.period_set_name ' ||
1310 'and glp.period_name = nvl(:override_period, pod.gl_encumbered_period_name) ' ||
1311 'and doc_cur.currency_code = poh.currency_code ' ||
1315 'and pod.po_line_id = pol.po_line_id ' ||
1312 'and poll.po_header_id = poh.po_header_id ' ||
1313 'and poll.shipment_type in (''STANDARD'', ''PLANNED'') ' ||
1314 'and pod.line_location_id = poll.line_location_id ' ||
1316 'and nvl(pod.encumbered_flag, ''N'') = '':encumbrance_state'' ' ||
1317 'and nvl(poll.cancel_flag, ''N'') = '':cancel_state'' ' ||
1318 'and nvl(pod.prevent_encumbrance_flag, ''N'') = ''N'' ' ||
1319 'and :entity_level = :object_id ' ||
1320 'and pod.req_distribution_id = pord.distribution_id(+) ' ||
1321 'and pord.requisition_line_id = porl.requisition_line_id(+) ' ||
1322 'and porl.requisition_header_id = prh.requisition_header_id (+)';
1323
1324
1325 -- Statement to recreate Requisition Encumbrances
1326
1327 l_bstmt := 'select :packet_id, ' ||
1328 'glsob.set_of_books_id, ' ||
1329 '''Purchasing'', ' ||
1330 '''Requisitions'', ' ||
1331 'pord.budget_account_id, ' ||
1332 '''E'', ' ||
1333 'glp.period_name, ' ||
1334 'glp.period_year, ' ||
1335 'glp.period_num, ' ||
1336 'glp.quarter_num, ' ||
1337 'glsob.currency_code, ' ||
1338 ':status_code, ' ||
1339 'sysdate, ' ||
1340 ':user_id, ' ||
1341 'null, ' ||
1342 'fsp.req_encumbrance_type_id, ' ||
1343 'decode(base_cur.minimum_accountable_unit, null, ' ||
1344 'round((porl.unit_price + ' ||
1345 'po_tax_sv.get_tax(''REQ'',PORD.distribution_id) '||
1346 '/ PORD.req_line_quantity) * :dr_quantity, ' ||
1347 'base_cur.precision), ' ||
1348 'round((porl.unit_price + ' ||
1349 'po_tax_sv.get_tax(''REQ'',PORD.distribution_id) '||
1350 '/ PORD.req_line_quantity) * :dr_quantity / ' ||
1351 'base_cur.minimum_accountable_unit) * ' ||
1352 'base_cur.minimum_accountable_unit), ' ||
1353 'decode(base_cur.minimum_accountable_unit, null, ' ||
1354 'round((porl.unit_price + ' ||
1355 'po_tax_sv.get_tax(''REQ'',PORD.distribution_id) '||
1356 '/ PORD.req_line_quantity) * :cr_quantity, ' ||
1357 'base_cur.precision), ' ||
1358 'round((porl.unit_price + ' ||
1359 'po_tax_sv.get_tax(''REQ'',PORD.distribution_id) '||
1360 '/ PORD.req_line_quantity) * :cr_quantity / ' ||
1361 'base_cur.minimum_accountable_unit) * ' ||
1362 'base_cur.minimum_accountable_unit), ' ||
1363 'decode(base_cur.minimum_accountable_unit, null, ' ||
1364 'round((porl.unit_price + ' ||
1365 'po_tax_sv.get_tax(''REQ'',PORD.distribution_id) '||
1366 '/ PORD.req_line_quantity) * :dr_quantity, ' ||
1367 'base_cur.precision), ' ||
1368 'round((porl.unit_price + ' ||
1369 'po_tax_sv.get_tax(''REQ'',PORD.distribution_id) '||
1370 '/ PORD.req_line_quantity) * :dr_quantity / ' ||
1371 'base_cur.minimum_accountable_unit) * ' ||
1372 'base_cur.minimum_accountable_unit), ' ||
1373 'decode(base_cur.minimum_accountable_unit, null, ' ||
1374 'round((porl.unit_price + ' ||
1378 'round((porl.unit_price + ' ||
1375 'po_tax_sv.get_tax(''REQ'',PORD.distribution_id) '||
1376 '/ PORD.req_line_quantity) * :cr_quantity, ' ||
1377 'base_cur.precision), ' ||
1379 'po_tax_sv.get_tax(''REQ'',PORD.distribution_id) '||
1380 '/ PORD.req_line_quantity) * :cr_quantity / ' ||
1381 'base_cur.minimum_accountable_unit) * ' ||
1382 'base_cur.minimum_accountable_unit), ' ||
1383 'pord.ussgl_transaction_code, ' ||
1384 '''REQ'', ' ||
1385 'porl.requisition_header_id, ' ||
1386 'pord.distribution_id, ' ||
1387 'prh.segment1, ' ||
1388 'porl.reference_num, ' ||
1389 'substr(porl.item_description, 1, 40) ' ||
1390 'from gl_periods glp, ' ||
1391 'gl_sets_of_books glsob, ' ||
1392 'financials_system_parameters fsp, ' ||
1393 'fnd_currencies base_cur, ' ||
1394 'po_req_distributions pord, ' ||
1395 'po_requisition_lines porl, ' ||
1396 'po_requisition_headers prh, ' ||
1397 'po_distributions pod, ' ||
1398 'po_line_locations poll ' ||
1399 'where glsob.set_of_books_id = fsp.set_of_books_id ' ||
1400 'and glp.period_set_name = glsob.period_set_name ' ||
1401 'and glp.period_name = nvl(:override_period, pod.gl_encumbered_period_name) ' ||
1402 'and base_cur.currency_code = glsob.currency_code ' ||
1403 'and poll.shipment_type in (''STANDARD'', ''PLANNED'') ' ||
1404 'and pod.line_location_id = poll.line_location_id ' ||
1405 'and nvl(pod.encumbered_flag, ''N'') = '':encumbrance_state'' ' ||
1406 'and nvl(poll.cancel_flag, ''N'') = '':cancel_state'' ' ||
1407 'and nvl(pod.prevent_encumbrance_flag, ''N'') = ''N'' ' ||
1408 'and :entity_level = :object_id ' ||
1409 'and :backing_doc_join_column = pod.req_distribution_id ' ||
1410 'and porl.requisition_line_id = pord.requisition_line_id ' ||
1411 'and nvl(fsp.req_encumbrance_flag, ''N'') = ''Y'' ' ||
1412 'and nvl(pord.prevent_encumbrance_flag, ''N'') = ''N'' ' ||
1413 'and porl.requisition_header_id = prh.requisition_header_id ' ||
1414 'and nvl(:recreate_demand, ''Y'') = ''Y''';
1415
1416
1417 -- Substitute the tokens
1418
1419 if g_pomode = 'RESERVE' then
1420
1421 l_stmt := replace(l_stmt, ':dr_quantity', 'pod.quantity_ordered');
1422 l_stmt := replace(l_stmt, ':cr_quantity', '0');
1423 l_stmt := replace(l_stmt, ':encumbrance_state', 'N');
1424 l_stmt := replace(l_stmt, ':cancel_state', 'N');
1425
1426 l_bstmt := replace(l_bstmt, ':dr_quantity', '0');
1427 l_bstmt := replace(l_bstmt, ':cr_quantity', 'pord.req_line_quantity');
1428 l_bstmt := replace(l_bstmt, ':backing_doc_join_column', 'pord.distribution_id');
1429 l_bstmt := replace(l_bstmt, ':encumbrance_state', 'N');
1430 l_bstmt := replace(l_bstmt, ':cancel_state', 'N');
1431
1432 elsif g_pomode = 'REVERSE' then
1433
1434 l_stmt := replace(l_stmt, ':dr_quantity', '-(decode(nvl(poll.accrue_on_receipt_flag, ''N''), ''N'', (pod.quantity_ordered - nvl(pod.quantity_billed, 0)), ''Y'', (pod.quantity_ordered - nvl(pod.quantity_delivered, 0))))');
1435 l_stmt := replace(l_stmt, ':cr_quantity', '0');
1436 l_stmt := replace(l_stmt, ':encumbrance_state', 'Y');
1437 l_stmt := replace(l_stmt, ':cancel_state', 'I');
1438
1439 l_bstmt := replace(l_bstmt, ':dr_quantity', '0');
1443 (pod.quantity_ordered - nvl(pod.quantity_delivered, 0))))');
1440 l_bstmt := replace(l_bstmt, ':cr_quantity',
1441 '(poll.price_override / porl.unit_price) * -(decode(nvl(poll.accrue_on_receipt_flag, ''N''), ''N'',
1442 (pod.quantity_ordered - nvl(pod.quantity_billed, 0)), ''Y'',
1444 l_bstmt := replace(l_bstmt, ':backing_doc_join_column', 'pord.source_req_distribution_id');
1445 l_bstmt := replace(l_bstmt, ':encumbrance_state', 'Y');
1446 l_bstmt := replace(l_bstmt, ':cancel_state', 'I');
1447
1448 elsif g_pomode = 'LIQUIDATE' then
1449
1450 l_stmt := replace(l_stmt, ':dr_quantity', '0');
1451 l_stmt := replace(l_stmt, ':cr_quantity', 'greatest(decode(nvl(poll.accrue_on_receipt_flag, ''N''), ''N'', (pod.quantity_ordered - nvl(pod.quantity_billed, 0)), ''Y'', (pod.quantity_ordered - nvl(pod.quantity_delivered, 0))), 0)');
1452 l_stmt := replace(l_stmt, ':encumbrance_state', 'Y');
1453 l_stmt := replace(l_stmt, ':cancel_state', 'N');
1454
1455 l_bstmt := replace(l_bstmt, ':dr_quantity', '0');
1456 l_bstmt := replace(l_bstmt, ':cr_quantity', '0');
1457 l_bstmt := replace(l_bstmt, ':backing_doc_join_column', 'pord.distribution_id');
1458 l_bstmt := replace(l_bstmt, ':encumbrance_state', 'Y');
1459 l_bstmt := replace(l_bstmt, ':cancel_state', 'N');
1460
1461 elsif g_pomode = 'REJECT' then
1462
1463 l_stmt := replace(l_stmt, ':dr_quantity', '0');
1464 l_stmt := replace(l_stmt, ':cr_quantity', '(decode(nvl(poll.approved_flag, ''N''), ''Y'', 0, ''N'', 0, ''R'', (decode(nvl(poll.quantity_received, 0), 0, (decode(nvl(poll.quantity_billed, 0), 0, pod.quantity_ordered, 0)), 0))))');
1465 l_stmt := replace(l_stmt, ':encumbrance_state', 'Y');
1466 l_stmt := replace(l_stmt, ':cancel_state', 'N');
1467
1468 l_bstmt := replace(l_bstmt, ':dr_quantity', '(decode(nvl(poll.approved_flag, ''N''), ''Y'', 0, ''N'', 0, ''R'', (decode(nvl(poll.quantity_received, 0), 0, (decode(nvl(poll.quantity_billed, 0), 0, pord.req_line_quantity, 0)), 0))))');
1469 l_bstmt := replace(l_bstmt, ':cr_quantity', '0');
1470 l_bstmt := replace(l_bstmt, ':backing_doc_join_column', 'pord.distribution_id');
1471 l_bstmt := replace(l_bstmt, ':encumbrance_state', 'Y');
1472 l_bstmt := replace(l_bstmt, ':cancel_state', 'N');
1473
1474 end if;
1475
1476 if g_fclevel = 'DISTRIBUTION' then
1477
1478 l_stmt := replace(l_stmt, ':entity_level', 'pod.po_distribution_id');
1479 l_bstmt := replace(l_bstmt, ':entity_level', 'pod.po_distribution_id');
1480
1481 elsif g_fclevel = 'SHIPMENT' then
1482
1483 l_stmt := replace(l_stmt, ':entity_level', 'poll.line_location_id');
1484 l_bstmt := replace(l_bstmt, ':entity_level', 'poll.line_location_id');
1485
1486 elsif g_fclevel = 'LINE' then
1487
1488 l_stmt := replace(l_stmt, ':entity_level', 'poll.po_line_id');
1489 l_bstmt := replace(l_bstmt, ':entity_level', 'poll.po_line_id');
1490
1491 elsif g_fclevel = 'HEADER' then
1492
1493 l_stmt := replace(l_stmt, ':entity_level', 'poll.po_header_id');
1494 l_bstmt := replace(l_bstmt, ':entity_level', 'poll.po_header_id');
1495
1496 end if;
1497
1498 g_sql_insert := g_sql_insert ||
1499 l_stmt || ' UNION ALL ' ||
1500 l_bstmt;
1501
1502 return(TRUE);
1503
1504 END po_fc_selpo;
1505
1506 /* ----------------------------------------------------------------------- */
1507
1508 -- Build Select Statement for Blanket PO backed by a Requisition
1509
1513 -- Round the Entered Amounts to the precision of the Currency that they
1510 -- Status Code is 'P' for Pending Funds Reservation and 'C' for Pending
1511 -- Funds Check
1512
1514 -- are in because the Unit Price could be specified with a larger precision
1515
1516 FUNCTION po_fc_selblnkrel RETURN BOOLEAN IS
1517
1518 l_stmt VARCHAR2(4000);
1519 l_bstmt VARCHAR2(3000);
1520
1521 BEGIN
1522
1523 l_stmt := 'select :packet_id, ' ||
1524 'glsob.set_of_books_id, ' ||
1525 '''Purchasing'', ' ||
1526 '''Purchases'', ' ||
1527 'pod.budget_account_id, ' ||
1528 '''E'', ' ||
1529 'glp.period_name, ' ||
1530 'glp.period_year, ' ||
1531 'glp.period_num, ' ||
1532 'glp.quarter_num, ' ||
1533 'glsob.currency_code, ' ||
1534 ':status_code, ' ||
1535 'sysdate, ' ||
1536 ':user_id, ' ||
1537 'null, ' ||
1538 'fsp.purch_encumbrance_type_id, ' ||
1539 'decode(doc_cur.minimum_accountable_unit, null, ' ||
1540 'round((poll.price_override + ' ||
1541 'po_tax_sv.get_tax(''RELEASE'',POD.po_distribution_id)'||
1542 '/ POD.quantity_ordered) * :dr_quantity ' ||
1543 ', doc_cur.precision) * nvl(pod.rate, 1) , ' ||
1544 'round((poll.price_override + ' ||
1545 'po_tax_sv.get_tax(''RELEASE'',POD.po_distribution_id)'||
1546 '/ POD.quantity_ordered) * :dr_quantity' ||
1547 ' / doc_cur.minimum_accountable_unit) ' ||
1548 ' * doc_cur.minimum_accountable_unit' ||
1549 ' * nvl(pod.rate, 1) ), ' ||
1550 'decode(doc_cur.minimum_accountable_unit, null, ' ||
1551 'round((poll.price_override + ' ||
1552 'po_tax_sv.get_tax(''RELEASE'',POD.po_distribution_id)'||
1553 '/ POD.quantity_ordered) * :cr_quantity ' ||
1554 ', doc_cur.precision) * nvl(pod.rate, 1) , ' ||
1555 'round((poll.price_override + ' ||
1556 'po_tax_sv.get_tax(''RELEASE'',POD.po_distribution_id)'||
1557 '/ POD.quantity_ordered) * :cr_quantity' ||
1558 ' / doc_cur.minimum_accountable_unit) ' ||
1559 ' * doc_cur.minimum_accountable_unit' ||
1560 ' * nvl(pod.rate, 1) ), ' ||
1561 'decode(doc_cur.minimum_accountable_unit, null, ' ||
1562 'round((poll.price_override + ' ||
1563 'po_tax_sv.get_tax(''RELEASE'',POD.po_distribution_id)'||
1564 '/ POD.quantity_ordered) * :dr_quantity ' ||
1565 ', doc_cur.precision) * nvl(pod.rate, 1) , ' ||
1566 'round((poll.price_override + ' ||
1567 'po_tax_sv.get_tax(''RELEASE'',POD.po_distribution_id)'||
1568 '/ POD.quantity_ordered) * :dr_quantity' ||
1569 ' / doc_cur.minimum_accountable_unit) ' ||
1570 ' * doc_cur.minimum_accountable_unit' ||
1574 'po_tax_sv.get_tax(''RELEASE'',POD.po_distribution_id)'||
1571 ' * nvl(pod.rate, 1) ), ' ||
1572 'decode(doc_cur.minimum_accountable_unit, null, ' ||
1573 'round((poll.price_override + ' ||
1575 '/ POD.quantity_ordered) * :cr_quantity ' ||
1576 ', doc_cur.precision) * nvl(pod.rate, 1) , ' ||
1577 'round((poll.price_override + ' ||
1578 'po_tax_sv.get_tax(''RELEASE'',POD.po_distribution_id)'||
1579 '/ POD.quantity_ordered) * :cr_quantity' ||
1580 ' / doc_cur.minimum_accountable_unit) ' ||
1581 ' * doc_cur.minimum_accountable_unit' ||
1582 ' * nvl(pod.rate, 1) ), ' ||
1583 'pod.ussgl_transaction_code, ' ||
1584 '''PO'', ' ||
1585 'poll.po_header_id, ' ||
1586 'pod.po_distribution_id, ' ||
1587 'poh.segment1, ' ||
1588 'decode(pod.req_distribution_id, null, null, ' ||
1589 'prh.segment1), ' ||
1590 'substr(pol.item_description, 1, 40) ' ||
1591 'from gl_periods glp, ' ||
1592 'gl_sets_of_books glsob, ' ||
1593 'financials_system_parameters fsp, ' ||
1594 'fnd_currencies doc_cur, ' ||
1595 'po_distributions pod, ' ||
1596 'po_line_locations poll, ' ||
1597 'po_lines pol, ' ||
1598 'po_releases por, ' ||
1599 'po_headers poh, ' ||
1600 'po_requisition_headers prh, ' ||
1601 'po_requisition_lines porl, ' ||
1602 'po_req_distributions pord ' ||
1603 'where glsob.set_of_books_id = fsp.set_of_books_id ' ||
1604 'and glp.period_set_name = glsob.period_set_name ' ||
1605 'and glp.period_name = nvl(:override_period, pod.gl_encumbered_period_name) ' ||
1606 'and doc_cur.currency_code = poh.currency_code ' ||
1607 'and poll.po_release_id = por.po_release_id ' ||
1608 'and por.po_header_id = poh.po_header_id ' ||
1609 'and poll.shipment_type = ''BLANKET'' ' ||
1610 'and pod.line_location_id = poll.line_location_id ' ||
1611 'and pod.po_line_id = pol.po_line_id ' ||
1612 'and nvl(pod.encumbered_flag, ''N'') = '':encumbrance_state'' ' ||
1613 'and nvl(poll.cancel_flag, ''N'') = '':cancel_state'' ' ||
1614 'and nvl(pod.prevent_encumbrance_flag, ''N'') = ''N'' ' ||
1615 'and :entity_level = :object_id ' ||
1616 'and pod.req_distribution_id = pord.distribution_id(+) ' ||
1617 'and pord.requisition_line_id = porl.requisition_line_id(+) ' ||
1618 'and porl.requisition_header_id = prh.requisition_header_id (+)';
1619
1620
1621 -- Statement to recreate Requisition Encumbrances
1622
1623 l_bstmt := 'select :packet_id, ' ||
1624 'glsob.set_of_books_id, ' ||
1625 '''Purchasing'', ' ||
1626 '''Requisitions'', ' ||
1627 'pord.budget_account_id, ' ||
1628 '''E'', ' ||
1629 'glp.period_name, ' ||
1630 'glp.period_year, ' ||
1631 'glp.period_num, ' ||
1632 'glp.quarter_num, ' ||
1633 'glsob.currency_code, ' ||
1634 ':status_code, ' ||
1635 'sysdate, ' ||
1636 ':user_id, ' ||
1637 'null, ' ||
1638 'fsp.req_encumbrance_type_id, ' ||
1639 'decode(base_cur.minimum_accountable_unit, null, ' ||
1640 'round((porl.unit_price + ' ||
1641 'po_tax_sv.get_tax(''REQ'',PORD.distribution_id) ' ||
1642 '/ PORD.req_line_quantity) * :dr_quantity, ' ||
1643 'base_cur.precision), ' ||
1644 'round((porl.unit_price + ' ||
1645 'po_tax_sv.get_tax(''REQ'',PORD.distribution_id) ' ||
1646 '/ PORD.req_line_quantity) * :dr_quantity / ' ||
1647 'base_cur.minimum_accountable_unit) * ' ||
1648 'base_cur.minimum_accountable_unit), ' ||
1649 'decode(base_cur.minimum_accountable_unit, null, ' ||
1650 'round((porl.unit_price + ' ||
1651 'po_tax_sv.get_tax(''REQ'',PORD.distribution_id) ' ||
1652 '/ PORD.req_line_quantity) * :cr_quantity, ' ||
1653 'base_cur.precision), ' ||
1654 'round((porl.unit_price + ' ||
1655 'po_tax_sv.get_tax(''REQ'',PORD.distribution_id) ' ||
1656 '/ PORD.req_line_quantity) * :cr_quantity / ' ||
1657 'base_cur.minimum_accountable_unit) * ' ||
1658 'base_cur.minimum_accountable_unit), ' ||
1659 'decode(base_cur.minimum_accountable_unit, null, ' ||
1660 'round((porl.unit_price + ' ||
1661 'po_tax_sv.get_tax(''REQ'',PORD.distribution_id) ' ||
1662 '/ PORD.req_line_quantity) * :dr_quantity, ' ||
1666 '/ PORD.req_line_quantity) * :dr_quantity / ' ||
1663 'base_cur.precision), ' ||
1664 'round((porl.unit_price + ' ||
1665 'po_tax_sv.get_tax(''REQ'',PORD.distribution_id) ' ||
1667 'base_cur.minimum_accountable_unit) * ' ||
1668 'base_cur.minimum_accountable_unit), ' ||
1669 'decode(base_cur.minimum_accountable_unit, null, ' ||
1670 'round((porl.unit_price + ' ||
1671 'po_tax_sv.get_tax(''REQ'',PORD.distribution_id) ' ||
1672 '/ PORD.req_line_quantity) * :cr_quantity, ' ||
1673 'base_cur.precision), ' ||
1674 'round((porl.unit_price + ' ||
1675 'po_tax_sv.get_tax(''REQ'',PORD.distribution_id) ' ||
1676 '/ PORD.req_line_quantity) * :cr_quantity / ' ||
1677 'base_cur.minimum_accountable_unit) * ' ||
1678 'base_cur.minimum_accountable_unit), ' ||
1679 'pord.ussgl_transaction_code, ' ||
1680 '''REQ'', ' ||
1681 'porl.requisition_header_id, ' ||
1682 'pord.distribution_id, ' ||
1683 'prh.segment1, ' ||
1684 'porl.reference_num, ' ||
1685 'substr(porl.item_description, 1, 40) ' ||
1686 'from gl_periods glp, ' ||
1687 'gl_sets_of_books glsob, ' ||
1688 'financials_system_parameters fsp, ' ||
1689 'fnd_currencies base_cur, ' ||
1690 'po_req_distributions pord, ' ||
1691 'po_requisition_lines porl, ' ||
1692 'po_requisition_headers prh, ' ||
1693 'po_distributions pod, ' ||
1694 'po_line_locations poll ' ||
1695 'where glsob.set_of_books_id = fsp.set_of_books_id ' ||
1696 'and glp.period_set_name = glsob.period_set_name ' ||
1697 'and glp.period_name = nvl(:override_period, pod.gl_encumbered_period_name) ' ||
1698 'and base_cur.currency_code = glsob.currency_code ' ||
1699 'and poll.shipment_type = ''BLANKET'' ' ||
1700 'and pod.line_location_id = poll.line_location_id ' ||
1701 'and nvl(pod.encumbered_flag, ''N'') = '':encumbrance_state'' ' ||
1702 'and nvl(poll.cancel_flag, ''N'') = '':cancel_state'' ' ||
1703 'and nvl(pod.prevent_encumbrance_flag, ''N'') = ''N'' ' ||
1704 'and :entity_level = :object_id ' ||
1705 'and :backing_doc_join_column = pod.req_distribution_id ' ||
1706 'and porl.requisition_line_id = pord.requisition_line_id ' ||
1707 'and nvl(fsp.req_encumbrance_flag, ''N'') = ''Y'' ' ||
1708 'and nvl(pord.prevent_encumbrance_flag, ''N'') = ''N'' ' ||
1709 'and porl.requisition_header_id = prh.requisition_header_id ' ||
1710 'and nvl(:recreate_demand, ''Y'') = ''Y''';
1711
1712
1713 -- Substitute the tokens
1714
1715 if g_pomode = 'RESERVE' then
1716
1717 l_stmt := replace(l_stmt, ':dr_quantity', 'pod.quantity_ordered');
1718 l_stmt := replace(l_stmt, ':cr_quantity', '0');
1719 l_stmt := replace(l_stmt, ':encumbrance_state', 'N');
1720 l_stmt := replace(l_stmt, ':cancel_state', 'N');
1721
1722 l_bstmt := replace(l_bstmt, ':dr_quantity', '0');
1723 l_bstmt := replace(l_bstmt, ':cr_quantity', 'pord.req_line_quantity');
1724 l_bstmt := replace(l_bstmt, ':backing_doc_join_column', 'pord.distribution_id');
1725 l_bstmt := replace(l_bstmt, ':encumbrance_state', 'N');
1726 l_bstmt := replace(l_bstmt, ':cancel_state', 'N');
1727
1728 elsif g_pomode = 'REVERSE' then
1729
1730 l_stmt := replace(l_stmt, ':dr_quantity', '-(decode(nvl(poll.accrue_on_receipt_flag, ''N''), ''N'', (pod.quantity_ordered - nvl(pod.quantity_billed, 0)), ''Y'', (pod.quantity_ordered - nvl(pod.quantity_delivered, 0))))');
1731 l_stmt := replace(l_stmt, ':cr_quantity', '0');
1732 l_stmt := replace(l_stmt, ':encumbrance_state', 'Y');
1733 l_stmt := replace(l_stmt, ':cancel_state', 'I');
1734
1735 l_bstmt := replace(l_bstmt, ':dr_quantity', '0');
1736 l_bstmt := replace(l_bstmt, ':cr_quantity',
1737 'poll.price_override / porl.unit_price * -(decode(nvl(poll.accrue_on_receipt_flag, ''N''), ''N'',
1738 (pod.quantity_ordered - nvl(pod.quantity_billed, 0)), ''Y'',
1739 (pod.quantity_ordered - nvl(pod.quantity_delivered, 0))))');
1740 l_bstmt := replace(l_bstmt, ':backing_doc_join_column', 'pord.source_req_distribution_id');
1741 l_bstmt := replace(l_bstmt, ':encumbrance_state', 'Y');
1742 l_bstmt := replace(l_bstmt, ':cancel_state', 'I');
1743
1744 elsif g_pomode = 'LIQUIDATE' then
1745
1746 l_stmt := replace(l_stmt, ':dr_quantity', '0');
1747 l_stmt := replace(l_stmt, ':cr_quantity', 'greatest(decode(nvl(poll.accrue_on_receipt_flag, ''N''), ''N'', (pod.quantity_ordered - nvl(pod.quantity_billed, 0)), ''Y'', (pod.quantity_ordered - nvl(pod.quantity_delivered, 0))), 0)');
1748 l_stmt := replace(l_stmt, ':encumbrance_state', 'Y');
1749 l_stmt := replace(l_stmt, ':cancel_state', 'N');
1750
1751 l_bstmt := replace(l_bstmt, ':dr_quantity', '0');
1752 l_bstmt := replace(l_bstmt, ':cr_quantity', '0');
1753 l_bstmt := replace(l_bstmt, ':backing_doc_join_column', 'pord.distribution_id');
1754 l_bstmt := replace(l_bstmt, ':encumbrance_state', 'Y');
1758
1755 l_bstmt := replace(l_bstmt, ':cancel_state', 'N');
1756
1757 elsif g_pomode = 'REJECT' then
1759 l_stmt := replace(l_stmt, ':dr_quantity', '0');
1760 l_stmt := replace(l_stmt, ':cr_quantity', '(decode(nvl(poll.approved_flag, ''N''), ''Y'', 0, ''N'', 0, ''R'', (decode(nvl(poll.quantity_received, 0), 0, (decode(nvl(poll.quantity_billed, 0), 0, pod.quantity_ordered, 0)), 0))))');
1761 l_stmt := replace(l_stmt, ':encumbrance_state', 'Y');
1762 l_stmt := replace(l_stmt, ':cancel_state', 'N');
1763
1764 l_bstmt := replace(l_bstmt, ':dr_quantity', '(decode(nvl(poll.approved_flag, ''N''), ''Y'', 0, ''N'', 0, ''R'', (decode(nvl(poll.quantity_received, 0), 0, (decode(nvl(poll.quantity_billed, 0), 0, pord.req_line_quantity, 0)), 0))))');
1765 l_bstmt := replace(l_bstmt, ':cr_quantity', '0');
1766 l_bstmt := replace(l_bstmt, ':backing_doc_join_column', 'pord.distribution_id');
1767 l_bstmt := replace(l_bstmt, ':encumbrance_state', 'Y');
1768 l_bstmt := replace(l_bstmt, ':cancel_state', 'N');
1769
1770 end if;
1771
1772 if g_fclevel = 'DISTRIBUTION' then
1773
1774 l_stmt := replace(l_stmt, ':entity_level', 'pod.po_distribution_id');
1775 l_bstmt := replace(l_bstmt, ':entity_level', 'pod.po_distribution_id');
1776
1777 elsif g_fclevel = 'SHIPMENT' then
1778
1779 l_stmt := replace(l_stmt, ':entity_level', 'poll.line_location_id');
1780 l_bstmt := replace(l_bstmt, ':entity_level', 'poll.line_location_id');
1781
1782 elsif g_fclevel = 'LINE' then
1783
1784 l_stmt := replace(l_stmt, ':entity_level', 'poll.po_line_id');
1785 l_bstmt := replace(l_bstmt, ':entity_level', 'poll.po_line_id');
1786
1787 elsif g_fclevel = 'HEADER' then
1788
1789 l_stmt := replace(l_stmt, ':entity_level', 'poll.po_release_id');
1790 l_bstmt := replace(l_bstmt, ':entity_level', 'poll.po_release_id');
1791
1792 end if;
1793
1794 g_sql_insert := g_sql_insert ||
1795 l_stmt || ' UNION ALL ' ||
1796 l_bstmt;
1797
1798 return(TRUE);
1799
1800 END po_fc_selblnkrel;
1801
1802 /* ----------------------------------------------------------------------- */
1803
1804 -- Build Select Statement for Scheduled Release backed by a Planned PO
1805
1806 -- Status Code is 'P' for Pending Funds Reservation and 'C' for Pending
1807 -- Funds Check
1808
1809 -- Round the Entered Amounts to the precision of the Currency that they
1810 -- are in because the Unit Price could be specified with a larger precision
1811
1812 FUNCTION po_fc_selschrel RETURN BOOLEAN IS
1813
1814 l_stmt VARCHAR2(4500);
1815 l_bstmt VARCHAR2(4500);
1816
1817 BEGIN
1818
1819 l_stmt := 'select :packet_id, ' ||
1820 'glsob.set_of_books_id, ' ||
1821 '''Purchasing'', ' ||
1822 '''Purchases'', ' ||
1823 'prd.budget_account_id, ' ||
1824 '''E'', ' ||
1825 'glp.period_name, ' ||
1826 'glp.period_year, ' ||
1827 'glp.period_num, ' ||
1828 'glp.quarter_num, ' ||
1829 'glsob.currency_code, ' ||
1830 ':status_code, ' ||
1831 'sysdate, ' ||
1832 ':user_id, ' ||
1833 'null, ' ||
1834 'fsp.purch_encumbrance_type_id, ' ||
1835 'decode(doc_cur.minimum_accountable_unit, null, ' ||
1836 'round((prll.price_override + ' ||
1837 'po_tax_sv.get_tax(''RELEASE'',PRD.po_distribution_id)'||
1838 '/ PRD.quantity_ordered) * :dr_quantity ' ||
1839 ', doc_cur.precision) * nvl(prd.rate, 1) , ' ||
1840 'round((prll.price_override + ' ||
1841 'po_tax_sv.get_tax(''RELEASE'',PRD.po_distribution_id)'||
1842 '/ PRD.quantity_ordered) * :dr_quantity' ||
1843 ' / doc_cur.minimum_accountable_unit) ' ||
1844 ' * doc_cur.minimum_accountable_unit' ||
1845 ' * nvl(prd.rate, 1) ), ' ||
1846 'decode(doc_cur.minimum_accountable_unit, null, ' ||
1847 'round((prll.price_override + ' ||
1848 'po_tax_sv.get_tax(''RELEASE'',PRD.po_distribution_id)'||
1849 '/ PRD.quantity_ordered) * :cr_quantity ' ||
1850 ', doc_cur.precision) * nvl(prd.rate, 1) , ' ||
1851 'round((prll.price_override + ' ||
1852 'po_tax_sv.get_tax(''RELEASE'',PRD.po_distribution_id)'||
1853 '/ PRD.quantity_ordered) * :cr_quantity' ||
1854 ' / doc_cur.minimum_accountable_unit) ' ||
1855 ' * doc_cur.minimum_accountable_unit' ||
1856 ' * nvl(prd.rate, 1) ), ' ||
1857 'decode(doc_cur.minimum_accountable_unit, null, ' ||
1858 'round((prll.price_override + ' ||
1859 'po_tax_sv.get_tax(''RELEASE'',PRD.po_distribution_id)'||
1860 '/ PRD.quantity_ordered) * :dr_quantity ' ||
1861 ', doc_cur.precision) * nvl(prd.rate, 1) , ' ||
1862 'round((prll.price_override + ' ||
1866 ' * doc_cur.minimum_accountable_unit' ||
1863 'po_tax_sv.get_tax(''RELEASE'',PRD.po_distribution_id)'||
1864 '/ PRD.quantity_ordered) * :dr_quantity' ||
1865 ' / doc_cur.minimum_accountable_unit) ' ||
1867 ' * nvl(prd.rate, 1) ), ' ||
1868 'decode(doc_cur.minimum_accountable_unit, null, ' ||
1869 'round((prll.price_override + ' ||
1870 'po_tax_sv.get_tax(''RELEASE'',PRD.po_distribution_id)'||
1871 '/ PRD.quantity_ordered) * :cr_quantity ' ||
1872 ', doc_cur.precision) * nvl(prd.rate, 1) , ' ||
1873 'round((prll.price_override + ' ||
1874 'po_tax_sv.get_tax(''RELEASE'',PRD.po_distribution_id)'||
1875 '/ PRD.quantity_ordered) * :cr_quantity' ||
1876 ' / doc_cur.minimum_accountable_unit) ' ||
1877 ' * doc_cur.minimum_accountable_unit' ||
1878 ' * nvl(prd.rate, 1) ), ' ||
1879 'prd.ussgl_transaction_code, ' ||
1880 '''PO'', ' ||
1881 'prll.po_header_id, ' ||
1882 'prd.po_distribution_id, ' ||
1883 'poh.segment1, ' ||
1884 'decode(pod.req_distribution_id, null, null, ' ||
1885 'prh.segment1), ' ||
1886 'substr(pol.item_description, 1, 40) ' ||
1887 'from gl_periods glp, ' ||
1888 'gl_sets_of_books glsob, ' ||
1889 'financials_system_parameters fsp, ' ||
1890 'fnd_currencies doc_cur, ' ||
1891 'po_distributions prd, ' ||
1892 'po_line_locations prll, ' ||
1893 'po_lines pol, ' ||
1894 'po_headers poh, ' ||
1895 'po_releases por, ' ||
1896 'po_distributions pod, ' ||
1897 'po_requisition_headers prh, ' ||
1898 'po_requisition_lines porl, ' ||
1899 'po_req_distributions pord ' ||
1900 'where glsob.set_of_books_id = fsp.set_of_books_id ' ||
1901 'and glp.period_set_name = glsob.period_set_name ' ||
1902 'and glp.period_name = nvl(:override_period, prd.gl_encumbered_period_name) ' ||
1903 'and doc_cur.currency_code = poh.currency_code ' ||
1904 'and prll.po_release_id = por.po_release_id ' ||
1905 'and por.po_header_id = poh.po_header_id ' ||
1906 'and prll.shipment_type = ''SCHEDULED'' ' ||
1907 'and prd.line_location_id = prll.line_location_id ' ||
1908 'and prd.po_line_id = pol.po_line_id ' ||
1909 'and nvl(prd.encumbered_flag, ''N'') = '':encumbrance_state'' ' ||
1910 'and nvl(prll.cancel_flag, ''N'') = '':cancel_state'' ' ||
1911 'and nvl(prd.prevent_encumbrance_flag, ''N'') = ''N'' ' ||
1912 'and :entity_level = :object_id ' ||
1913 'and prd.source_distribution_id = pod.po_distribution_id(+) ' ||
1914 'and pod.req_distribution_id = pord.distribution_id(+) ' ||
1915 'and pord.requisition_line_id = porl.requisition_line_id(+) ' ||
1916 'and porl.requisition_header_id = prh.requisition_header_id (+)';
1917
1918
1919 -- Statement to recreate Planned PO Encumbrances
1920
1921 l_bstmt := 'select :packet_id, ' ||
1922 'glsob.set_of_books_id, ' ||
1923 '''Purchasing'', ' ||
1924 '''Purchases'', ' ||
1925 'pod.budget_account_id, ' ||
1926 '''E'', ' ||
1927 'glp.period_name, ' ||
1928 'glp.period_year, ' ||
1929 'glp.period_num, ' ||
1930 'glp.quarter_num, ' ||
1931 'glsob.currency_code, ' ||
1932 ':status_code, ' ||
1933 'sysdate, ' ||
1934 ':user_id, ' ||
1935 'null, ' ||
1936 'fsp.purch_encumbrance_type_id, ' ||
1937 'decode(doc_cur.minimum_accountable_unit, null, ' ||
1938 'round((poll.price_override + ' ||
1939 'po_tax_sv.get_tax(''PO'',POD.po_distribution_id)'||
1940 '/ POD.quantity_ordered) * :dr_quantity ' ||
1941 ', doc_cur.precision) * nvl(pod.rate, 1) , ' ||
1942 'round((poll.price_override + ' ||
1943 'po_tax_sv.get_tax(''PO'',POD.po_distribution_id)'||
1944 '/ POD.quantity_ordered) * :dr_quantity' ||
1945 ' / doc_cur.minimum_accountable_unit) ' ||
1946 ' * doc_cur.minimum_accountable_unit' ||
1947 ' * nvl(pod.rate, 1) ), ' ||
1948 'decode(doc_cur.minimum_accountable_unit, null, ' ||
1949 'round((poll.price_override + ' ||
1950 'po_tax_sv.get_tax(''PO'',POD.po_distribution_id)'||
1951 '/ POD.quantity_ordered) * :cr_quantity ' ||
1952 ', doc_cur.precision) * nvl(pod.rate, 1) , ' ||
1953 'round((poll.price_override + ' ||
1954 'po_tax_sv.get_tax(''PO'',POD.po_distribution_id)'||
1958 ' * nvl(pod.rate, 1) ), ' ||
1955 '/ POD.quantity_ordered) * :cr_quantity' ||
1956 ' / doc_cur.minimum_accountable_unit) ' ||
1957 ' * doc_cur.minimum_accountable_unit' ||
1959 'decode(doc_cur.minimum_accountable_unit, null, ' ||
1960 'round((poll.price_override + ' ||
1961 'po_tax_sv.get_tax(''PO'',POD.po_distribution_id)'||
1962 '/ POD.quantity_ordered) * :dr_quantity ' ||
1963 ', doc_cur.precision) * nvl(pod.rate, 1) , ' ||
1964 'round((poll.price_override + ' ||
1965 'po_tax_sv.get_tax(''PO'',POD.po_distribution_id)'||
1966 '/ POD.quantity_ordered) * :dr_quantity' ||
1967 ' / doc_cur.minimum_accountable_unit) ' ||
1968 ' * doc_cur.minimum_accountable_unit' ||
1969 ' * nvl(pod.rate, 1) ), ' ||
1970 'decode(doc_cur.minimum_accountable_unit, null, ' ||
1971 'round((poll.price_override + ' ||
1972 'po_tax_sv.get_tax(''PO'',POD.po_distribution_id)'||
1973 '/ POD.quantity_ordered) * :cr_quantity ' ||
1974 ', doc_cur.precision) * nvl(pod.rate, 1) , ' ||
1975 'round((poll.price_override + ' ||
1976 'po_tax_sv.get_tax(''PO'',POD.po_distribution_id)'||
1977 '/ POD.quantity_ordered) * :cr_quantity' ||
1978 ' / doc_cur.minimum_accountable_unit) ' ||
1979 ' * doc_cur.minimum_accountable_unit' ||
1980 ' * nvl(pod.rate, 1) ), ' ||
1981 'pod.ussgl_transaction_code, ' ||
1982 '''PO'', ' ||
1983 'poll.po_header_id, ' ||
1984 'pod.po_distribution_id, ' ||
1985 'poh.segment1, ' ||
1986 'decode(pod.req_distribution_id, null, null, ' ||
1987 'prh.segment1), ' ||
1988 'substr(porl.item_description, 1, 40) ' ||
1989 'from gl_periods glp, ' ||
1990 'gl_sets_of_books glsob, ' ||
1991 'financials_system_parameters fsp, ' ||
1992 'fnd_currencies doc_cur, ' ||
1993 'po_distributions prd, ' ||
1994 'po_line_locations prll, ' ||
1995 'po_lines pol, ' ||
1996 'po_headers poh, ' ||
1997 'po_releases por, ' ||
1998 'po_distributions pod, ' ||
1999 'po_line_locations poll, ' ||
2000 'po_requisition_headers prh, ' ||
2001 'po_requisition_lines porl, ' ||
2002 'po_req_distributions pord ' ||
2003 'where glsob.set_of_books_id = fsp.set_of_books_id ' ||
2004 'and glp.period_set_name = glsob.period_set_name ' ||
2005 'and glp.period_name = nvl(:override_period, pod.gl_encumbered_period_name) ' ||
2006 'and doc_cur.currency_code = poh.currency_code ' ||
2007 'and prll.po_release_id = por.po_release_id ' ||
2008 'and por.po_header_id = poh.po_header_id ' ||
2009 'and prll.shipment_type = ''SCHEDULED'' ' ||
2010 'and prd.line_location_id = prll.line_location_id ' ||
2011 'and nvl(prd.encumbered_flag, ''N'') = '':encumbrance_state'' ' ||
2012 'and nvl(prll.cancel_flag, ''N'') = '':cancel_state'' ' ||
2013 'and nvl(prd.prevent_encumbrance_flag, ''N'') = ''N'' ' ||
2014 'and :entity_level = :object_id ' ||
2015 'and pod.po_distribution_id = prd.source_distribution_id ' ||
2016 'and poll.line_location_id = pod.line_location_id ' ||
2017 'and pol.po_line_id = pod.po_line_id ' ||
2018 'and pod.req_distribution_id = pord.distribution_id(+) ' ||
2019 'and pord.requisition_line_id = porl.requisition_line_id(+) ' ||
2020 'and porl.requisition_header_id = prh.requisition_header_id (+)';
2021
2022
2023 -- Substitute the tokens
2024
2025 if g_pomode = 'RESERVE' then
2026
2027 l_stmt := replace(l_stmt, ':dr_quantity', 'prd.quantity_ordered');
2028 l_stmt := replace(l_stmt, ':cr_quantity', '0');
2029 l_stmt := replace(l_stmt, ':encumbrance_state', 'N');
2030 l_stmt := replace(l_stmt, ':cancel_state', 'N');
2031
2032 l_bstmt := replace(l_bstmt, ':dr_quantity', '0');
2033 l_bstmt := replace(l_bstmt, ':cr_quantity', 'prd.quantity_ordered');
2034 l_bstmt := replace(l_bstmt, ':encumbrance_state', 'N');
2035 l_bstmt := replace(l_bstmt, ':cancel_state', 'N');
2036
2037 elsif g_pomode = 'REVERSE' then
2038
2039 l_stmt := replace(l_stmt, ':dr_quantity', '-(decode(nvl(prll.accrue_on_receipt_flag, ''N''), ''N'', (prd.quantity_ordered - nvl(prd.quantity_billed, 0)), ''Y'', (prd.quantity_ordered - nvl(prd.quantity_delivered, 0))))');
2040 l_stmt := replace(l_stmt, ':cr_quantity', '0');
2041 l_stmt := replace(l_stmt, ':encumbrance_state', 'Y');
2042 l_stmt := replace(l_stmt, ':cancel_state', 'I');
2043
2044 l_bstmt := replace(l_bstmt, ':dr_quantity', '0');
2048
2045 l_bstmt := replace(l_stmt, ':cr_quantity', '-(decode(nvl(prll.accrue_on_receipt_flag, ''N''), ''N'', (prd.quantity_ordered - nvl(prd.quantity_billed, 0)), ''Y'', (prd.quantity_ordered - nvl(prd.quantity_delivered, 0))))');
2046 l_bstmt := replace(l_bstmt, ':encumbrance_state', 'Y');
2047 l_bstmt := replace(l_bstmt, ':cancel_state', 'I');
2049 elsif g_pomode = 'LIQUIDATE' then
2050
2051 l_stmt := replace(l_stmt, ':dr_quantity', '0');
2052 l_stmt := replace(l_stmt, ':cr_quantity', 'greatest(decode(nvl(prll.accrue_on_receipt_flag, ''N''), ''N'', (prd.quantity_ordered - nvl(prd.quantity_billed, 0)), ''Y'', (prd.quantity_ordered - nvl(prd.quantity_delivered, 0))), 0)');
2053 l_stmt := replace(l_stmt, ':encumbrance_state', 'Y');
2054 l_stmt := replace(l_stmt, ':cancel_state', 'N');
2055
2056 l_bstmt := replace(l_bstmt, ':dr_quantity', '0');
2057 l_bstmt := replace(l_bstmt, ':cr_quantity', '0');
2058 l_bstmt := replace(l_bstmt, ':encumbrance_state', 'Y');
2059 l_bstmt := replace(l_bstmt, ':cancel_state', 'N');
2060
2061 elsif g_pomode = 'REJECT' then
2062
2063 l_stmt := replace(l_stmt, ':dr_quantity', '0');
2064 l_stmt := replace(l_stmt, ':cr_quantity', 'decode(nvl(prll.approved_flag, ''N''), ''Y'', 0, ''N'', 0, ''R'', (decode(nvl(prll.quantity_received, 0), 0, (decode(nvl(prll.quantity_billed, 0), 0, prd.quantity_ordered, 0)), 0)))');
2065 l_stmt := replace(l_stmt, ':encumbrance_state', 'Y');
2066 l_stmt := replace(l_stmt, ':cancel_state', 'N');
2067
2068 l_bstmt := replace(l_stmt, ':dr_quantity', 'decode(nvl(prll.approved_flag, ''N''), ''Y'', 0, ''N'', 0, ''R'', (decode(nvl(prll.quantity_received, 0), 0, (decode(nvl(prll.quantity_billed, 0), 0, prd.quantity_ordered, 0)), 0)))');
2069 l_bstmt := replace(l_bstmt, ':cr_quantity', '0');
2070 l_bstmt := replace(l_bstmt, ':encumbrance_state', 'Y');
2071 l_bstmt := replace(l_bstmt, ':cancel_state', 'N');
2072
2073 end if;
2074
2075 if g_fclevel = 'DISTRIBUTION' then
2076
2077 l_stmt := replace(l_stmt, ':entity_level', 'prd.po_distribution_id');
2078 l_bstmt := replace(l_bstmt, ':entity_level', 'prd.po_distribution_id');
2079
2080 elsif g_fclevel = 'SHIPMENT' then
2081
2082 l_stmt := replace(l_stmt, ':entity_level', 'prll.line_location_id');
2083 l_bstmt := replace(l_bstmt, ':entity_level', 'prll.line_location_id');
2084
2085 elsif g_fclevel = 'LINE' then
2086
2087 l_stmt := replace(l_stmt, ':entity_level', 'prll.po_line_id');
2088 l_bstmt := replace(l_bstmt, ':entity_level', 'prll.po_line_id');
2089
2090 elsif g_fclevel = 'HEADER' then
2091
2092 l_stmt := replace(l_stmt, ':entity_level', 'prll.po_release_id');
2093 l_bstmt := replace(l_bstmt, ':entity_level', 'prll.po_release_id');
2094
2095 end if;
2096
2097 g_sql_insert := g_sql_insert ||
2098 l_stmt || ' UNION ALL ' ||
2099 l_bstmt;
2100
2101 return(TRUE);
2102
2103 END po_fc_selschrel;
2104
2105 /* ----------------------------------------------------------------------- */
2106
2107 -- Insert into the Funds Checker queue
2108
2109 FUNCTION po_fc_run(p_packetid IN OUT NOCOPY NUMBER) RETURN BOOLEAN IS
2110
2111 l_objectid NUMBER;
2112 l_userid NUMBER;
2113 l_status VARCHAR2(1);
2114
2115 cur_insert INTEGER;
2116 num_insert INTEGER;
2117
2118 cursor pkt_id is
2119 select gl_bc_packets_s.nextval
2120 from dual;
2121
2122 BEGIN
2123
2124 if g_fclevel = 'DISTRIBUTION' then
2125
2126 l_objectid := g_distid;
2127
2128 elsif g_fclevel = 'SHIPMENT' then
2129
2130 l_objectid := g_shipid;
2131
2132 elsif g_fclevel = 'LINE' then
2133
2134 l_objectid := g_lineid;
2135
2136 elsif g_fclevel = 'HEADER' then
2137
2138 l_objectid := g_docid;
2139
2140 end if;
2141
2142
2143 -- Get User ID
2144
2145 g_userid := FND_GLOBAL.USER_ID;
2146
2147 /* DEBUG g_userid := 1; */
2148
2149 if g_userid = -1 then
2150
2151 PO_MESSAGE_S.APP_ERROR(error_name => 'PO_ALL_TRACE_ERROR_WITH_MSG',
2152 token1 => 'FILE',
2153 value1 => 'PO_FUNDS_CHECKER',
2154 token2 => 'ERR_NUMBER',
2155 value2 => '110',
2156 token3 => 'SUBROUTINE',
2157 value3 => 'PO_FC_RUN()',
2158 token4 => 'ERROR_MSG',
2159 value4 => 'CANNOT GET USER ID');
2160 return(FALSE);
2161
2162 end if;
2163
2164
2165 -- Get Login ID
2166
2167 -- FRKHAN: BUG 747290 Get concurrent login id
2168 -- if there is one else get login id
2169
2170 IF NVL(g_conc_flag,'N') = 'Y' THEN
2171 g_loginid := FND_GLOBAL.CONC_LOGIN_ID;
2172 ELSE
2173 g_loginid := FND_GLOBAL.LOGIN_ID;
2174 END IF;
2175
2176 /* DEBUG g_loginid := 1; */
2177
2178
2179 if g_loginid = -1 then
2180
2181 PO_MESSAGE_S.APP_ERROR(error_name => 'PO_ALL_TRACE_ERROR_WITH_MSG',
2182 token1 => 'FILE',
2183 value1 => 'PO_FUNDS_CHECKER',
2187 value3 => 'PO_FC_RUN()',
2184 token2 => 'ERR_NUMBER',
2185 value2 => '115',
2186 token3 => 'SUBROUTINE',
2188 token4 => 'ERROR_MSG',
2189 value4 => 'CANNOT GET LAST LOGIN ID');
2190 return(FALSE);
2191
2192 end if;
2193
2194 if g_action = 'CHECK FUNDS' then
2195 l_status := 'C';
2196 -- dbms_output.put_line('c');
2197 else
2198 l_status := 'P';
2199 -- dbms_output.put_line('p');
2200 end if;
2201
2202
2203 -- Get Packet ID
2204
2205 open pkt_id;
2206
2207 fetch pkt_id
2208 into g_packetid;
2209
2210 close pkt_id;
2211
2212
2213 -- Execute the Dynamic SQL Insert Statement
2214
2215 -- dbms_output.put_line('build dynamic sql');
2216
2217 cur_insert := dbms_sql.open_cursor;
2218 dbms_sql.parse(cur_insert, g_sql_insert, dbms_sql.v7);
2219
2220 dbms_sql.bind_variable(cur_insert, 'packet_id', g_packetid);
2221 dbms_sql.bind_variable(cur_insert, 'status_code', l_status);
2222 dbms_sql.bind_variable(cur_insert, 'user_id', g_userid);
2223 dbms_sql.bind_variable(cur_insert, 'override_period', g_override_period);
2224 dbms_sql.bind_variable(cur_insert, 'object_id', l_objectid);
2225
2226 if INSTR(g_sql_insert, ':recreate_demand', 1) > 0 then
2227 dbms_sql.bind_variable(cur_insert, 'recreate_demand', g_recreate_demand);
2228 end if;
2229
2230 -- dbms_output.put_line('before execute of sql statement'||cur_insert);
2231
2232 num_insert := dbms_sql.execute(cur_insert);
2233 dbms_sql.close_cursor(cur_insert);
2234
2235 -- dbms_output.put_line('inserted'||num_insert||' records in gl_bc');
2236
2237 -- FRKHAN bug 941171
2238 IF LENGTH (g_dbug) < x_max_length THEN
2239 g_dbug := g_dbug ||
2240 'Inserted ' || num_insert || ' Records into gl_bc_packets' ||
2241 g_delim;
2242 END IF;
2243 p_packetid := g_packetid;
2244
2245 return(TRUE);
2246
2247
2248 EXCEPTION
2249
2250 WHEN OTHERS THEN
2251
2252 -- dbms_output.put_line('exception handler');
2253
2254 if pkt_id%ISOPEN then
2255 close pkt_id;
2256 end if;
2257
2258 if dbms_sql.is_open(cur_insert) then
2259 dbms_sql.close_cursor(cur_insert);
2260 end if;
2261
2262 PO_MESSAGE_S.SQL_ERROR(routine => 'PO_FUNDS_CHECKER',
2263 location => '120',
2264 error_code => SQLCODE);
2265
2266 return(FALSE);
2267
2268 END po_fc_run;
2269
2270 /* ----------------------------------------------------------------------- */
2271
2272 -- Rollup encumbered_flag on the Distributions to po_line_locations and
2273 -- po_requisition_lines
2274
2275 FUNCTION po_rollup_enc RETURN BOOLEAN IS
2276
2277 BEGIN
2278
2279 if g_doctyp = 'REQUISITION' then
2280
2281 if not po_rollup_req then
2282
2283 PO_MESSAGE_S.APP_ERROR(error_name => 'PO_ALL_TRACE_ERROR',
2284 token1 => 'FILE',
2285 value1 => 'PO_FUNDS_CHECKER',
2286 token2 => 'ERR_NUMBER',
2287 value2 => '125',
2288 token3 => 'SUBROUTINE',
2289 value3 => 'PO_ROLLUP_ENC()');
2290 return(FALSE);
2291
2292 end if;
2293
2294 elsif g_doctyp = 'PO' then
2295
2296 if not po_rollup_po then
2297
2298 PO_MESSAGE_S.APP_ERROR(error_name => 'PO_ALL_TRACE_ERROR',
2299 token1 => 'FILE',
2300 value1 => 'PO_FUNDS_CHECKER',
2301 token2 => 'ERR_NUMBER',
2302 value2 => '130',
2303 token3 => 'SUBROUTINE',
2304 value3 => 'PO_ROLLUP_ENC()');
2305 return(FALSE);
2306
2307 end if;
2308
2309 elsif g_doctyp = 'RELEASE' then
2310
2311 if g_docsubtyp = 'BLANKET' then
2312
2313 if not po_rollup_blnkrel then
2314
2315 PO_MESSAGE_S.APP_ERROR(error_name => 'PO_ALL_TRACE_ERROR',
2316 token1 => 'FILE',
2317 value1 => 'PO_FUNDS_CHECKER',
2318 token2 => 'ERR_NUMBER',
2319 value2 => '135',
2320 token3 => 'SUBROUTINE',
2321 value3 => 'PO_ROLLUP_ENC()');
2322 return(FALSE);
2323
2324 end if;
2325
2326 elsif g_docsubtyp = 'SCHEDULED' then
2327
2328 if not po_rollup_schrel then
2329
2330 PO_MESSAGE_S.APP_ERROR(error_name => 'PO_ALL_TRACE_ERROR',
2331 token1 => 'FILE',
2332 value1 => 'PO_FUNDS_CHECKER',
2333 token2 => 'ERR_NUMBER',
2334 value2 => '140',
2335 token3 => 'SUBROUTINE',
2339 end if;
2336 value3 => 'PO_ROLLUP_ENC()');
2337 return(FALSE);
2338
2340
2341 else
2342
2343 PO_MESSAGE_S.APP_ERROR(error_name => 'PO_ALL_TRACE_ERROR',
2344 token1 => 'FILE',
2345 value1 => 'PO_FUNDS_CHECKER',
2346 token2 => 'ERR_NUMBER',
2347 value2 => '145',
2348 token3 => 'SUBROUTINE',
2349 value3 => 'PO_ROLLUP_ENC()');
2350 return(FALSE);
2351
2352 end if;
2353
2354 else
2355
2356 PO_MESSAGE_S.APP_ERROR(error_name => 'PO_ALL_TRACE_ERROR',
2357 token1 => 'FILE',
2358 value1 => 'PO_FUNDS_CHECKER',
2359 token2 => 'ERR_NUMBER',
2360 value2 => '150',
2361 token3 => 'SUBROUTINE',
2362 value3 => 'PO_ROLLUP_ENC()');
2363 return(FALSE);
2364
2365 end if;
2366
2367 return(TRUE);
2368
2369 END po_rollup_enc;
2370
2371 /* ----------------------------------------------------------------------- */
2372
2373 -- Rollup encumbered_flag for Requisitions
2374
2375 FUNCTION po_rollup_req RETURN BOOLEAN IS
2376
2377 BEGIN
2378
2379 -- Preset encumbered_flag to 'Y' if Action is 'RESERVE', to 'N' if
2380 -- Action is 'REVERSE' or 'LIQUIDATE'
2381
2382 if g_fclevel = 'HEADER' then
2383
2384 update po_req_distributions
2385 set encumbered_flag = decode(g_fcmode, 'RESERVE', 'Y', 'N')
2386 where requisition_line_id in
2387 (select porl.requisition_line_id
2388 from po_requisition_lines porl
2389 where porl.requisition_header_id = g_docid
2390 and porl.line_location_id is null);
2391 -- FRKHAN bug 941171
2392 IF LENGTH (g_dbug) < x_max_length THEN
2393 g_dbug := g_dbug ||
2394 'Rolled up Encumbrance Flag for ' || SQL%ROWCOUNT || ' Requisitions' || g_delim;
2395 END IF;
2396 elsif g_fclevel = 'LINE' then
2397
2398 update po_req_distributions
2399 set encumbered_flag = decode(g_fcmode, 'RESERVE', 'Y', 'N')
2400 where requisition_line_id = g_lineid;
2401
2402 -- FRKHAN bug 941171
2403 IF LENGTH (g_dbug) < x_max_length THEN
2404 g_dbug := g_dbug ||
2405 'Rolled up Encumbrance Flag for ' || SQL%ROWCOUNT || ' Requisitions' || g_delim;
2406 END IF;
2407 end if;
2408
2409
2410 -- Update Distributions with Funds Check Results
2411
2412 if not po_fc_dist then
2413
2414 PO_MESSAGE_S.APP_ERROR(error_name => 'PO_ALL_TRACE_ERROR',
2415 token1 => 'FILE',
2416 value1 => 'PO_FUNDS_CHECKER',
2417 token2 => 'ERR_NUMBER',
2418 value2 => '155',
2419 token3 => 'SUBROUTINE',
2420 value3 => 'PO_ROLLUP_REQ()');
2421 return(FALSE);
2422
2423 end if;
2424
2425
2426 -- Rollup Encumbrance State to Requisition Lines
2427
2428 update po_requisition_lines porl
2429 set encumbered_flag =
2430 (select decode(count(pord.distribution_id), 0, 'Y', 'N')
2431 from po_req_distributions pord
2432 where pord.requisition_line_id = porl.requisition_line_id
2433 and pord.encumbered_flag = 'N')
2434 where porl.requisition_header_id = g_docid;
2435 -- FRKHAN bug 941171
2436 IF LENGTH (g_dbug) < x_max_length THEN
2437 g_dbug := g_dbug ||
2438 'Rolled up Encumbrance Flag for ' || SQL%ROWCOUNT || ' Requisition Lines' || g_delim;
2439 END IF;
2440 return(TRUE);
2441
2442
2443 EXCEPTION
2444
2445 WHEN OTHERS THEN
2446
2447 PO_MESSAGE_S.SQL_ERROR(routine => 'PO_FUNDS_CHECKER',
2448 location => '160',
2449 error_code => SQLCODE);
2450
2451 return(FALSE);
2452
2453 END po_rollup_req;
2454
2455 /* ----------------------------------------------------------------------- */
2456
2457 -- Rollup encumbered_flag for PO
2458
2459 FUNCTION po_rollup_po RETURN BOOLEAN IS
2460
2461 BEGIN
2462
2463 if g_fclevel = 'HEADER' then
2464
2465 -- Preset encumbered_flag on Main Doc
2466
2467 update po_distributions
2468 set encumbered_flag = decode(g_pomode, 'RESERVE', 'Y', 'REVERSE', 'N',
2469 'LIQUIDATE', 'N', 'REJECT',
2470 encumbered_flag, 'N')
2471 where po_header_id = g_docid;
2472
2473
2474 -- Preset encumbered_flag on Backing Doc
2475
2476 if g_pomode in ('RESERVE', 'LIQUIDATE', 'REJECT') then
2477
2478 update po_req_distributions
2479 set encumbered_flag = decode(g_pomode, 'RESERVE', 'N',
2483 from po_distributions
2480 'REVERSE', 'Y', encumbered_flag)
2481 where distribution_id in
2482 (select req_distribution_id
2484 where po_header_id = g_docid);
2485
2486 else
2487
2488 if g_recreate_demand = 'Y' then
2489
2490 update po_req_distributions
2491 set encumbered_flag = decode(g_pomode, 'RESERVE', 'N',
2492 'REVERSE', 'Y', encumbered_flag)
2493 where source_req_distribution_id in
2494 (select req_distribution_id
2495 from po_distributions
2496 where po_header_id = g_docid);
2497
2498 end if;
2499
2500 end if;
2501
2502 elsif g_fclevel = 'LINE' then
2503
2504 -- Preset encumbered_flag on Main Doc
2505
2506 update po_distributions
2507 set encumbered_flag = decode(g_pomode, 'RESERVE', 'Y', 'REVERSE', 'N',
2508 'LIQUIDATE', 'N', 'REJECT',
2509 encumbered_flag, 'N')
2510 where po_line_id = g_lineid;
2511
2512
2513 -- Preset encumbered_flag on Backing Doc
2514
2515 if g_pomode in ('RESERVE', 'LIQUIDATE', 'REJECT') then
2516
2517 update po_req_distributions
2518 set encumbered_flag = decode(g_pomode, 'RESERVE', 'N',
2519 'REVERSE', 'Y', encumbered_flag)
2520 where distribution_id in
2521 (select req_distribution_id
2522 from po_distributions
2523 where po_line_id = g_lineid);
2524
2525 else
2526
2527 if g_recreate_demand = 'Y' then
2528
2529 update po_req_distributions
2530 set encumbered_flag = decode(g_pomode, 'RESERVE', 'N',
2531 'REVERSE', 'Y', encumbered_flag)
2532 where source_req_distribution_id in
2533 (select req_distribution_id
2534 from po_distributions
2535 where po_line_id = g_lineid);
2536
2537 end if;
2538
2539 end if;
2540
2541 elsif g_fclevel = 'SHIPMENT' then
2542
2543 -- Preset encumbered_flag on Main Doc
2544
2545 update po_distributions
2546 set encumbered_flag = decode(g_pomode, 'RESERVE', 'Y', 'REVERSE', 'N',
2547 'LIQUIDATE', 'N', 'REJECT',
2548 encumbered_flag, 'N')
2549 where line_location_id = g_shipid;
2550
2551
2552 -- Preset encumbered_flag on Backing Doc
2553
2554 if g_pomode in ('RESERVE', 'LIQUIDATE', 'REJECT') then
2555
2556 update po_req_distributions
2557 set encumbered_flag = decode(g_pomode, 'RESERVE', 'N',
2558 'REVERSE', 'Y', encumbered_flag)
2559 where distribution_id in
2560 (select req_distribution_id
2561 from po_distributions
2562 where line_location_id = g_shipid);
2563
2564 else
2565
2566 if g_recreate_demand = 'Y' then
2567
2568 update po_req_distributions
2569 set encumbered_flag = decode(g_pomode, 'RESERVE', 'N',
2570 'REVERSE', 'Y', encumbered_flag)
2571 where source_req_distribution_id in
2572 (select req_distribution_id
2573 from po_distributions
2574 where line_location_id = g_shipid);
2575
2576 end if;
2577
2578 end if;
2579
2580 end if;
2581
2582
2583 -- Update Distributions with Funds Check Results
2584
2585 if not po_fc_dist then
2586
2587 PO_MESSAGE_S.APP_ERROR('PO_ALL_TRACE_ERROR',
2588 'FILE', 'PO_FUNDS_CHECKER',
2589 'ERR_NUMBER', '165',
2590 'SUBROUTINE', 'PO_ROLLUP_PO()');
2591 return(FALSE);
2592
2593 end if;
2594
2595
2596 -- Rollup to Line Locations
2597
2598 update po_line_locations poll
2599 set encumbered_flag =
2600 (select decode(count(pod.po_distribution_id), 0, 'Y', 'N')
2601 from po_distributions pod
2602 where pod.line_location_id = poll.line_location_id
2603 and pod.encumbered_flag = 'N')
2604 where poll.po_header_id = g_docid;
2605
2606
2607 -- Rollup to Requisition Lines on Backing Document
2608
2609 if g_pomode in ('RESERVE', 'LIQUIDATE', 'REJECT') then
2610
2611 update po_requisition_lines porl
2612 set encumbered_flag =
2613 (select decode(count(pord.distribution_id), 0, 'Y', 'N')
2614 from po_req_distributions pord
2615 where pord.requisition_line_id = porl.requisition_line_id
2616 and pord.encumbered_flag = 'N')
2617 where porl.requisition_line_id in
2618 (select prd.requisition_line_id
2619 from po_requisition_lines prl,
2620 po_req_distributions prd,
2621 po_distributions pod
2625
2622 where prd.requisition_line_id = prl.requisition_line_id
2623 and prd.distribution_id = pod.req_distribution_id
2624 and pod.po_header_id = g_docid);
2626 else
2627
2628 update po_requisition_lines porl
2629 set encumbered_flag =
2630 (select decode(count(pord.distribution_id), 0, 'Y', 'N')
2631 from po_req_distributions pord
2632 where pord.requisition_line_id = porl.requisition_line_id
2633 and pord.encumbered_flag = 'N')
2634 where porl.requisition_line_id in
2635 (select prd.requisition_line_id
2636 from po_requisition_lines prl,
2637 po_req_distributions prd,
2638 po_distributions pod
2639 where prd.requisition_line_id = prl.requisition_line_id
2640 and prd.source_req_distribution_id = pod.req_distribution_id
2641 and pod.po_header_id = g_docid);
2642
2643 end if;
2644
2645 return(TRUE);
2646
2647
2648 EXCEPTION
2649
2650 WHEN OTHERS THEN
2651
2652 PO_MESSAGE_S.SQL_ERROR(routine => 'PO_FUNDS_CHECKER',
2653 location => '170',
2654 error_code => SQLCODE);
2655
2656 return(FALSE);
2657
2658 END po_rollup_po;
2659
2660 /* ----------------------------------------------------------------------- */
2661
2662 -- Rollup encumbered_flag for Blanket Release
2663
2664 FUNCTION po_rollup_blnkrel RETURN BOOLEAN IS
2665
2666 BEGIN
2667
2668 if g_fclevel = 'HEADER' then
2669
2670 -- Preset encumbered_flag on Main Doc
2671
2672 update po_distributions
2673 set encumbered_flag = decode(g_pomode, 'RESERVE', 'Y', 'REVERSE', 'N',
2674 'LIQUIDATE', 'N', 'REJECT',
2675 encumbered_flag, 'N')
2676 where po_release_id = g_docid;
2677
2678
2679 -- Preset encumbered_flag on Backing Doc
2680
2681 if g_pomode in ('RESERVE', 'LIQUIDATE', 'REJECT') then
2682
2683 update po_req_distributions
2684 set encumbered_flag = decode(g_pomode, 'RESERVE', 'N',
2685 'REVERSE', 'Y', encumbered_flag)
2686 where distribution_id in
2687 (select req_distribution_id
2688 from po_distributions
2689 where po_release_id = g_docid);
2690
2691 else
2692
2693 if g_recreate_demand = 'Y' then
2694
2695 update po_req_distributions
2696 set encumbered_flag = decode(g_pomode, 'RESERVE', 'N',
2697 'REVERSE', 'Y', encumbered_flag)
2698 where source_req_distribution_id in
2699 (select req_distribution_id
2700 from po_distributions
2701 where po_release_id = g_docid);
2702
2703 end if;
2704
2705 end if;
2706
2707 elsif g_fclevel = 'LINE' then
2708
2709 -- Preset encumbered_flag on Main Doc
2710
2711 update po_distributions
2712 set encumbered_flag = decode(g_pomode, 'RESERVE', 'Y', 'REVERSE', 'N',
2713 'LIQUIDATE', 'N', 'REJECT',
2714 encumbered_flag, 'N')
2715 where po_line_id = g_lineid;
2716
2717
2718 -- Preset encumbered_flag on Backing Doc
2719
2720 if g_pomode in ('RESERVE', 'LIQUIDATE', 'REJECT') then
2721
2722 update po_req_distributions
2723 set encumbered_flag = decode(g_pomode, 'RESERVE', 'N',
2724 'REVERSE', 'Y', encumbered_flag)
2725 where distribution_id in
2726 (select req_distribution_id
2727 from po_distributions
2728 where po_line_id = g_lineid);
2729
2730 else
2731
2732 if g_recreate_demand = 'Y' then
2733
2734 update po_req_distributions
2735 set encumbered_flag = decode(g_pomode, 'RESERVE', 'N',
2736 'REVERSE', 'Y', encumbered_flag)
2737 where source_req_distribution_id in
2738 (select req_distribution_id
2739 from po_distributions
2740 where po_line_id = g_lineid);
2741
2742 end if;
2743
2744 end if;
2745
2746 elsif g_fclevel = 'SHIPMENT' then
2747
2748 -- Preset encumbered_flag on Main Doc
2749
2750 update po_distributions
2751 set encumbered_flag = decode(g_pomode, 'RESERVE', 'Y', 'REVERSE', 'N',
2752 'LIQUIDATE', 'N', 'REJECT',
2753 encumbered_flag, 'N')
2754 where line_location_id = g_shipid;
2755
2756
2757 -- Preset encumbered_flag on Backing Doc
2758
2759 if g_pomode in ('RESERVE', 'LIQUIDATE', 'REJECT') then
2760
2761 update po_req_distributions
2762 set encumbered_flag = decode(g_pomode, 'RESERVE', 'N',
2766 from po_distributions
2763 'REVERSE', 'Y', encumbered_flag)
2764 where distribution_id in
2765 (select req_distribution_id
2767 where line_location_id = g_shipid);
2768
2769 else
2770
2771 if g_recreate_demand = 'Y' then
2772
2773 update po_req_distributions
2774 set encumbered_flag = decode(g_pomode, 'RESERVE', 'N',
2775 'REVERSE', 'Y', encumbered_flag)
2776 where source_req_distribution_id in
2777 (select req_distribution_id
2778 from po_distributions
2779 where line_location_id = g_shipid);
2780
2781 end if;
2782
2783 end if;
2784
2785 end if;
2786
2787
2788 -- Update Distributions with Funds Check Results
2789
2790 if not po_fc_dist then
2791
2792 PO_MESSAGE_S.APP_ERROR('PO_ALL_TRACE_ERROR',
2793 'FILE', 'PO_FUNDS_CHECKER',
2794 'ERR_NUMBER', '175',
2795 'SUBROUTINE', 'PO_ROLLUP_BLNKREL()');
2796 return(FALSE);
2797
2798 end if;
2799
2800
2801 -- Rollup to Line Locations
2802
2803 update po_line_locations poll
2804 set encumbered_flag =
2805 (select decode(count(pod.po_distribution_id), 0, 'Y', 'N')
2806 from po_distributions pod
2807 where pod.line_location_id = poll.line_location_id
2808 and pod.encumbered_flag = 'N')
2809 where poll.po_release_id = g_docid;
2810
2811
2812 -- Rollup to Requisition Lines on Backing Document
2813
2814 if g_pomode in ('RESERVE', 'LIQUIDATE', 'REJECT') then
2815
2816 update po_requisition_lines porl
2817 set encumbered_flag =
2818 (select decode(count(pord.distribution_id), 0, 'Y', 'N')
2819 from po_req_distributions pord
2820 where pord.requisition_line_id = porl.requisition_line_id
2821 and pord.encumbered_flag = 'N')
2822 where porl.requisition_line_id in
2823 (select prd.requisition_line_id
2824 from po_requisition_lines prl,
2825 po_req_distributions prd,
2826 po_distributions pod
2827 where prd.requisition_line_id = prl.requisition_line_id
2828 and prd.distribution_id = pod.req_distribution_id
2829 and pod.po_release_id = g_docid);
2830
2831 else
2832
2833 update po_requisition_lines porl
2834 set encumbered_flag =
2835 (select decode(count(pord.distribution_id), 0, 'Y', 'N')
2836 from po_req_distributions pord
2837 where pord.requisition_line_id = porl.requisition_line_id
2838 and pord.encumbered_flag = 'N')
2839 where porl.requisition_line_id in
2840 (select prd.requisition_line_id
2841 from po_requisition_lines prl,
2842 po_req_distributions prd,
2843 po_distributions pod
2844 where prd.requisition_line_id = prl.requisition_line_id
2845 and prd.source_req_distribution_id = pod.req_distribution_id
2846 and pod.po_release_id = g_docid);
2847
2848 end if;
2849
2850 return(TRUE);
2851
2852
2853 EXCEPTION
2854
2855 WHEN OTHERS THEN
2856
2857 PO_MESSAGE_S.SQL_ERROR(routine => 'PO_FUNDS_CHECKER',
2858 location => '180',
2859 error_code => SQLCODE);
2860
2861 return(FALSE);
2862
2863 END po_rollup_blnkrel;
2864
2865 /* ----------------------------------------------------------------------- */
2866
2867 -- Rollup encumbered_flag for Scheduled Release
2868
2869 FUNCTION po_rollup_schrel RETURN BOOLEAN IS
2870
2871 BEGIN
2872
2873 if g_fclevel = 'HEADER' then
2874
2875 -- Preset encumbered_flag on Main Doc
2876
2877 update po_distributions
2878 set encumbered_flag = decode(g_pomode, 'RESERVE', 'Y', 'REVERSE', 'N',
2879 'LIQUIDATE', 'N', 'REJECT',
2880 encumbered_flag, 'N')
2881 where po_release_id = g_docid;
2882
2883
2884 -- Preset encumbered_flag on Backing Doc
2885
2886 update po_distributions
2887 set encumbered_flag = decode(g_pomode, 'RESERVE', 'N',
2888 'REVERSE', 'Y', encumbered_flag)
2889 where po_distribution_id in
2890 (select po_distribution_id
2891 from po_distributions
2892 where po_release_id = g_docid);
2893
2894 elsif g_fclevel = 'LINE' then
2895
2896 -- Preset encumbered_flag on Main Doc
2897
2898 update po_distributions
2899 set encumbered_flag = decode(g_pomode, 'RESERVE', 'Y', 'REVERSE', 'N',
2900 'LIQUIDATE', 'N', 'REJECT',
2901 encumbered_flag, 'N')
2902 where po_line_id = g_lineid;
2903
2904
2908 set encumbered_flag = decode(g_pomode, 'RESERVE', 'N',
2905 -- Preset encumbered_flag on Backing Doc
2906
2907 update po_distributions
2909 'REVERSE', 'Y', encumbered_flag)
2910 where po_distribution_id in
2911 (select po_distribution_id
2912 from po_distributions
2913 where po_line_id = g_lineid);
2914
2915 elsif g_fclevel = 'SHIPMENT' then
2916
2917 -- Preset encumbered_flag on Main Doc
2918
2919 update po_distributions
2920 set encumbered_flag = decode(g_pomode, 'RESERVE', 'Y', 'REVERSE', 'N',
2921 'LIQUIDATE', 'N', 'REJECT',
2922 encumbered_flag, 'N')
2923 where line_location_id = g_shipid;
2924
2925
2926 -- Preset encumbered_flag on Backing Doc
2927
2928 update po_distributions
2929 set encumbered_flag = decode(g_pomode, 'RESERVE', 'N',
2930 'REVERSE', 'Y', encumbered_flag)
2931 where po_distribution_id in
2932 (select po_distribution_id
2933 from po_distributions
2934 where line_location_id = g_shipid);
2935
2936 end if;
2937
2938
2939 -- Update Distributions with Funds Check Results
2940
2941 if not po_fc_dist then
2942
2943 PO_MESSAGE_S.APP_ERROR('PO_ALL_TRACE_ERROR',
2944 'FILE', 'PO_FUNDS_CHECKER',
2945 'ERR_NUMBER', '185',
2946 'SUBROUTINE', 'PO_ROLLUP_SCHREL()');
2947 return(FALSE);
2948
2949 end if;
2950
2951
2952 -- Rollup to Line Locations
2953
2954 update po_line_locations poll
2955 set encumbered_flag =
2956 (select decode(count(pod.po_distribution_id), 0, 'Y', 'N')
2957 from po_distributions pod
2958 where pod.line_location_id = poll.line_location_id
2959 and pod.encumbered_flag = 'N')
2960 where poll.po_release_id = g_docid;
2961
2962
2963 -- Rollup to Requisition Lines on Backing Document
2964
2965 update po_line_locations poll
2966 set encumbered_flag =
2967 (select decode(count(pod.po_distribution_id), 0, 'Y', 'N')
2968 from po_distributions pod
2969 where pod.line_location_id = poll.line_location_id
2970 and pod.encumbered_flag = 'N')
2971 where poll.line_location_id in
2972 (select pod.line_location_id
2973 from po_distributions pod,
2974 po_distributions prd
2975 where pod.po_distribution_id = prd.source_distribution_id
2976 and prd.po_release_id = g_docid);
2977
2978 return(TRUE);
2979
2980
2981 EXCEPTION
2982
2983 WHEN OTHERS THEN
2984
2985 PO_MESSAGE_S.SQL_ERROR(routine => 'PO_FUNDS_CHECKER',
2986 location => '190',
2987 error_code => SQLCODE);
2988
2989 return(FALSE);
2990
2991 END po_rollup_schrel;
2992
2993 /* ----------------------------------------------------------------------- */
2994
2995 -- Update Distributions with Funds Check Results
2996
2997 FUNCTION po_fc_dist RETURN BOOLEAN IS
2998
2999 /* Bug 968872 change the parameter name from packet_id
3000 to dist_packet_id to make the real restrict condition
3001 Base Bug 826203 in 11.0 */
3002
3003 cursor pkt(dist_packet_id NUMBER) is
3004 select glbp.reference1 doc_type,
3005 to_number(glbp.reference2) doc_id,
3006 to_number(glbp.reference3) dist_id,
3007 glbp.result_code,
3008 glbp.status_code,
3009 glbp.funds_check_level_code,
3010 glbp.accounted_dr,
3011 glbp.accounted_cr,
3012 glbp.automatic_encumbrance_flag
3013 from gl_bc_packets glbp
3014 where glbp.originating_rowid is null
3015 and glbp.packet_id = dist_packet_id
3016 and glbp.template_id is NULL
3017 order by packet_id, to_number(reference3);
3018
3019 BEGIN
3020
3021 -- Fetch Cursor Rows
3022
3023 for c_pkt in pkt(g_packetid) loop
3024
3025 -- Update encumbered_flag and encumbered_amount on the PO and REQ
3026 -- distributions if the Funds Check Return Code is 'Advisory',
3027 -- 'Success' or 'Partial' and the Status for that row in the Funds
3028 -- Checker queue is 'Approved'
3029
3030 if g_return_code in ('A', 'S', 'P') then
3031
3032 if c_pkt.doc_type = 'PO' then
3033
3034 if c_pkt.status_code = 'A' then
3035
3036 -- Update encumbered_flag if packet has passed
3037
3038 update po_distributions
3039 set encumbered_flag =
3040 decode(sign(c_pkt.accounted_dr - c_pkt.accounted_cr), 1, 'Y', 0, encumbered_flag, 'N')
3041 where po_distribution_id = c_pkt.dist_id;
3042
3043
3044 if c_pkt.automatic_encumbrance_flag = 'Y' then
3045
3046 -- Update encumbered_amount if packet has passed
3047
3048 update po_distributions
3052
3049 set encumbered_amount =
3050 round((nvl(encumbered_amount, 0) + c_pkt.accounted_dr - c_pkt.accounted_cr), 3)
3051 where po_distribution_id = c_pkt.dist_id;
3053 end if;
3054
3055 elsif c_pkt.status_code = 'R' then
3056
3057 -- Update encumbered_flag if packet has failed
3058
3059 update po_distributions
3060 set encumbered_flag =
3061 decode(sign(c_pkt.accounted_dr - c_pkt.accounted_cr), 1, 'N', 0, encumbered_flag, 'Y')
3062 where po_distribution_id = c_pkt.dist_id;
3063
3064 end if;
3065
3066 elsif c_pkt.doc_type = 'REQ' then
3067
3068 if c_pkt.status_code = 'A' then
3069
3070 -- Update encumbered_flag if packet has passed
3071
3072 update po_req_distributions
3073 set encumbered_flag =
3074 decode(sign(c_pkt.accounted_dr - c_pkt.accounted_cr), 1, 'Y', 0, encumbered_flag, 'N')
3075 where distribution_id = c_pkt.dist_id;
3076
3077
3078 if c_pkt.automatic_encumbrance_flag = 'Y' then
3079
3080 -- Update encumbered_amount if packet has passed
3081
3082 update po_req_distributions
3083 set encumbered_amount =
3084 round((nvl(encumbered_amount, 0) + c_pkt.accounted_dr - c_pkt.accounted_cr), 3)
3085 where distribution_id = c_pkt.dist_id;
3086
3087 end if;
3088
3089 elsif c_pkt.status_code = 'R' then
3090
3091 -- Update encumbered_flag if packet has failed
3092
3093 update po_req_distributions
3094 set encumbered_flag =
3095 decode(sign(c_pkt.accounted_dr - c_pkt.accounted_cr), 1, 'N', 0, encumbered_flag, 'Y')
3096 where distribution_id = c_pkt.dist_id;
3097
3098 end if;
3099
3100 end if;
3101
3102 end if;
3103
3104
3105 -- Update failed_funds_lookup_code on the PO and REQ distributions if
3106 -- the Funds Check Return Code is 'Failure', 'Fatal' or 'Partial' and
3107 -- status_code for that row in the Funds Checker queue is 'Rejected',
3108 -- 'Failed Check' or 'Fatal'
3109
3110 if g_return_code in ('T', 'F', 'P') then
3111
3112 if ((c_pkt.doc_type = 'PO') and
3113 (c_pkt.status_code in ('R', 'F', 'T'))) then
3114
3115 update po_distributions
3116 set failed_funds_lookup_code = c_pkt.result_code
3117 where po_distribution_id = c_pkt.dist_id;
3118
3119 elsif ((c_pkt.doc_type = 'REQ') and
3120 (c_pkt.status_code in ('R', 'F', 'T'))) then
3121
3122 update po_req_distributions
3123 set failed_funds_lookup_code = c_pkt.result_code
3124 where distribution_id = c_pkt.dist_id;
3125
3126 end if;
3127
3128 end if;
3129
3130 end loop;
3131
3132 return(TRUE);
3133
3134
3135 EXCEPTION
3136
3137 WHEN OTHERS THEN
3138
3139 PO_MESSAGE_S.SQL_ERROR(routine => 'PO_FUNDS_CHECKER',
3140 location => '195',
3141 error_code => SQLCODE);
3142
3143 return(FALSE);
3144
3145 END po_fc_dist;
3146
3147 /* ----------------------------------------------------------------------- */
3148
3149 -- Insert errors into po_online_report_text
3150
3151 FUNCTION po_err_insert RETURN BOOLEAN IS
3152
3153 l_linemsg VARCHAR2(25);
3154 l_shipmsg VARCHAR2(25);
3155 l_distmsg VARCHAR2(25);
3156
3157 l_reportid po_online_report_text.online_report_id%TYPE;
3158
3159 cursor report_seq is
3160 select po_online_report_text_s.nextval
3161 from dual;
3162
3163 BEGIN
3164
3165 l_linemsg := FND_MESSAGE.GET_STRING('PO', 'PO_ZMVOR_LINE');
3166 l_shipmsg := FND_MESSAGE.GET_STRING('PO', 'PO_ZMVOR_SHIPMENT');
3167 l_distmsg := FND_MESSAGE.GET_STRING('PO', 'PO_ZMVOR_DISTRIBUTION');
3168
3169 open report_seq;
3170
3171 fetch report_seq
3172 into l_reportid;
3173
3174 close report_seq;
3175
3176 if g_doctyp = 'REQUISITION' then
3177
3178 insert into po_online_report_text(online_report_id,
3179 last_update_login,
3180 last_updated_by,
3181 last_update_date,
3182 created_by,
3183 creation_date,
3184 line_num,
3185 shipment_num,
3186 distribution_num,
3187 sequence,
3188 text_line)
3189 select l_reportid,
3190 g_loginid,
3191 g_userid,
3195 prl.line_num,
3192 sysdate,
3193 g_userid,
3194 sysdate,
3196 0,
3197 prd.distribution_num,
3198 rownum,
3199 l_linemsg || to_char(prl.line_num) ||
3200 ' ' || l_distmsg ||
3201 to_char(prd.distribution_num) || ' ' ||
3202 gll.meaning
3203 from gl_bc_packets gbp,
3204 gl_lookups gll,
3205 po_requisition_lines prl,
3206 po_req_distributions prd
3207 where gbp.packet_id = g_packetid
3208 and gbp.status_code in ('R', 'F', 'T')
3209 and gbp.result_code = gll.lookup_code
3210 and gll.lookup_type = 'FUNDS_CHECK_RESULT_CODE'
3211 and gbp.reference3 = prd.distribution_id
3212 and prd.requisition_line_id = prl.requisition_line_id;
3213 -- FRKHAN bug 941171
3214 IF LENGTH (g_dbug) < x_max_length THEN
3215 g_dbug := g_dbug ||
3216 'Inserted ' || SQL%ROWCOUNT || ' Records into po_online_report_text' || g_delim;
3217 END IF;
3218 elsif g_doctyp in ('PO', 'RELEASE') then
3219
3220 insert into po_online_report_text(online_report_id,
3221 last_update_login,
3222 last_updated_by,
3223 last_update_date,
3224 created_by,
3225 creation_date,
3226 line_num,
3227 shipment_num,
3228 distribution_num,
3229 sequence,
3230 text_line)
3231 select l_reportid,
3232 g_loginid,
3233 g_userid,
3234 sysdate,
3235 g_userid,
3236 sysdate,
3237 pol.line_num,
3238 poll.shipment_num,
3239 pod.distribution_num,
3240 rownum,
3241 l_linemsg || to_char(pol.line_num) ||
3242 ' ' || l_shipmsg ||
3243 to_char(poll.shipment_num) || ' ' ||
3244 l_distmsg ||
3245 to_char(pod.distribution_num) || ' ' ||
3246 gll.meaning
3247 from gl_bc_packets gbp,
3248 gl_lookups gll,
3249 po_lines pol,
3250 po_line_locations poll,
3251 po_distributions pod
3252 where gbp.packet_id = g_packetid
3253 and gbp.status_code in ('R', 'F', 'T')
3254 and gbp.result_code = gll.lookup_code
3255 and gll.lookup_type = 'FUNDS_CHECK_RESULT_CODE'
3256 and gbp.reference3 = pod.po_distribution_id
3257 and pod.po_line_id = pol.po_line_id
3258 and pod.line_location_id = poll.line_location_id;
3259 -- FRKHAN bug 941171
3260 IF LENGTH (g_dbug) < x_max_length THEN
3261 g_dbug := g_dbug ||
3262 'Inserted ' || SQL%ROWCOUNT || ' Records into po_online_report_text' || g_delim;
3263 END IF;
3264 end if;
3265
3266 return(TRUE);
3267
3268
3269 EXCEPTION
3270
3271 WHEN OTHERS THEN
3272
3273 if report_seq%ISOPEN then
3274 close report_seq;
3275 end if;
3276
3277 PO_MESSAGE_S.SQL_ERROR(routine => 'PO_FUNDS_CHECKER',
3278 location => '200',
3279 error_code => SQLCODE);
3280
3281 return(FALSE);
3282
3283 END po_err_insert;
3284
3285 /* ----------------------------------------------------------------------- */
3286
3287 -- Get Debug Information
3288
3289 -- This Module is used to retrieve Debug Information for the Routines. It
3290 -- prints Debug Information when run as a Batch Process from SQL*Plus. For
3291 -- the Debug Information to be printed on the Screen, the SQL*Plus parameter
3292 -- 'Serveroutput' should be set to 'ON'
3293
3294 FUNCTION get_debug RETURN VARCHAR2 IS
3295
3296 BEGIN
3297
3298 return(g_dbug);
3299
3300 END get_debug;
3301
3302 /* ----------------------------------------------------------------------- */
3303
3304 END PO_FUNDS_CHECKER;
3305