1 PACKAGE BODY AP_WEB_DB_EXPDIST_PKG AS
2 /* $Header: apwdbedb.pls 120.24 2012/01/11 13:27:38 meesubra ship $ */
3
4 ---------------------------------------------
5 -- Some global types, constants and cursors
6 ---------------------------------------------
7
8 cursor cflex(p_chart_accounts_id IN NUMBER) is
9 SELECT flex.application_column_name
10 FROM fnd_id_flex_segments flex
11 WHERE flex.application_id = 101
12 AND flex.id_flex_code = 'GL#'
13 AND flex.id_flex_num = p_chart_accounts_id
14 AND flex.enabled_flag='Y'
15 AND flex.display_flag='Y'
16 order by segment_num;
17
18 PROCEDURE updateAccountValuesForForms(
19 p_report_header_id IN expDist_report_header_ID,
20 p_report_line_id IN expDist_REPORT_LINE_ID,
21 p_report_distribution_id IN expDist_REPORT_DISTRIBUTION_ID,
22 p_ccid IN expDist_CODE_COMBINATION_ID,
23 p_line_amount IN NUMBER);
24
25 --------------------------------------------------------------------------------
26 FUNCTION foundCCID(p_report_distribution_id IN expDist_REPORT_DISTRIBUTION_ID,
27 x_line_ccid OUT NOCOPY expDist_CODE_COMBINATION_ID)
28 RETURN BOOLEAN IS
29
30 --------------------------------------------------------------------------------
31 l_line_ccid VARCHAR2(15);
32 BEGIN
33
34 SELECT code_combination_id
35 INTO l_line_ccid
36 FROM ap_exp_report_dists aerd
37 WHERE aerd.report_distribution_id = p_report_distribution_id
38 AND aerd.code_combination_id is not null;
39
40 x_line_ccid := l_line_ccid;
41
42 IF (l_line_ccid = -1) THEN
43 return false;
44 ELSE
45 return true;
46 END IF;
47
48 EXCEPTION
49 WHEN NO_DATA_FOUND THEN
50 return FALSE;
51 WHEN OTHERS THEN
52 AP_WEB_DB_UTIL_PKG.RaiseException('foundCCID');
53 APP_EXCEPTION.RAISE_EXCEPTION;
54 return FALSE;
55 END foundCCID;
56
57
58 --------------------------------------------------------------------------------
59 FUNCTION foundDistributions(p_report_distribution_id IN expDist_REPORT_DISTRIBUTION_ID)
60 RETURN BOOLEAN IS
61 --------------------------------------------------------------------------------
62 l_temp VARCHAR2(1);
63 BEGIN
64
65 SELECT 'Y'
66 INTO l_temp
67 FROM ap_exp_report_dists rd
68 WHERE rd.report_distribution_id = p_report_distribution_id;
69
70 return TRUE;
71
72 EXCEPTION
73 WHEN NO_DATA_FOUND THEN
74 return FALSE;
75 WHEN OTHERS THEN
76 AP_WEB_DB_UTIL_PKG.RaiseException('foundDistributions');
77 APP_EXCEPTION.RAISE_EXCEPTION;
78 return FALSE;
79 END foundDistributions;
80
81
82 -----------------------------------------------------------------------------
83 FUNCTION SetDistCCID(
84 p_report_header_id IN expDist_REPORT_HEADER_ID,
85 p_report_line_id IN expDist_REPORT_LINE_ID,
86 p_exp_line_ccid IN expDist_CODE_COMBINATION_ID)
87 RETURN BOOLEAN IS
88 -----------------------------------------------------------------------------
89 BEGIN
90 UPDATE ap_exp_report_dists RD
91 SET RD.code_combination_id = p_exp_line_ccid
92 WHERE RD.report_header_id = p_report_header_id
93 AND RD.report_line_id = p_report_line_id;
94
95 return TRUE;
96
97 EXCEPTION
98 WHEN NO_DATA_FOUND THEN
99 return FALSE;
100 WHEN OTHERS THEN
101 AP_WEB_DB_UTIL_PKG.RaiseException('SetDistCCID');
102 APP_EXCEPTION.RAISE_EXCEPTION;
103 return FALSE;
104 END SetDistCCID;
105
106
107 --------------------------------------------------------------------------------
108 FUNCTION UpdateDistCCID(
109 p_report_header_id IN expDist_REPORT_HEADER_ID,
110 p_report_distribution_id IN expDist_REPORT_DISTRIBUTION_ID,
111 p_exp_dist_ccid IN expDist_CODE_COMBINATION_ID
112 ) RETURN BOOLEAN IS
113 --------------------------------------------------------------------------------
114 BEGIN
115
116 UPDATE ap_exp_report_dists RD
117 SET RD.code_combination_id = p_exp_dist_ccid
118 WHERE RD.report_header_id = p_report_header_id
119 AND RD.report_distribution_id = p_report_distribution_id;
120
121 return TRUE;
122
123 EXCEPTION
124 WHEN NO_DATA_FOUND THEN
125 return FALSE;
126 WHEN OTHERS THEN
127 AP_WEB_DB_UTIL_PKG.RaiseException('UpdateDistCCID');
128 APP_EXCEPTION.RAISE_EXCEPTION;
129 return FALSE;
130 END UpdateDistCCID;
131
132 -----------------------------------------------------------------------------
133 PROCEDURE DeleteReportDistributions(P_ReportID IN expDist_report_header_ID) IS
134 --------------------------------------------------------------------------------
135 l_TempReportHeaderID expDist_report_header_ID;
136 l_curr_calling_sequence VARCHAR2(100) := 'DeleteReportDistributions';
137
138 -- Selects report lines to delete. The actual value being selected does not
139 -- matter. For some reason the compiler complains when the OF column-name
140 -- in the FOR UPDATE is missing and NOWAIT is used, so the OF
141 -- REPORT_HEADER_ID is used as a place holder.
142 CURSOR ReportDistributions IS
143 SELECT REPORT_HEADER_ID
144 FROM AP_EXP_REPORT_DISTS
145 WHERE (REPORT_HEADER_ID = P_ReportID)
146 FOR UPDATE OF REPORT_HEADER_ID NOWAIT;
147
148 BEGIN
149 -- Delete the report distributions from table. An exception will occur if the row
150 -- locks cannot be attained because of the NOWAIT argument for select.
151 -- We are guaranteed a lock on the records because of the FOR UPDATE
152 OPEN ReportDistributions;
153
154 LOOP
155 FETCH ReportDistributions into l_TempReportHeaderID;
156 EXIT WHEN ReportDistributions%NOTFOUND;
157
158 -- Delete matching line
159 DELETE AP_EXP_REPORT_DISTS WHERE CURRENT OF ReportDistributions;
160 END LOOP;
161
162 CLOSE ReportDistributions;
163
164
165 EXCEPTION
166 WHEN NO_DATA_FOUND THEN
167 NULL;
168 WHEN OTHERS THEN
169 AP_WEB_DB_UTIL_PKG.RaiseException('ReportDistributions');
170 APP_EXCEPTION.RAISE_EXCEPTION;
171 END DeleteReportDistributions;
172
173
174 -------------------------------------------------------------------
175 -- Name: MoveDistributions
176 -- Desc: moves Expense Report Distributions from souce to destination
177 -- Input: p_target_report_header_id - target expense report header id
178 -- Input: p_source_report_line_id - source expense report line id
179 -- Input: p_target_report_line_id - target expense report line id
180 -------------------------------------------------------------------
181 PROCEDURE MoveDistributions(
182 p_target_report_header_id IN expDist_report_header_ID,
183 p_source_report_line_id IN expDist_report_line_ID,
184 p_target_report_line_id IN expDist_report_line_ID) IS
185
186 l_has_dist NUMBER;
187
188 BEGIN
189 AP_WEB_UTILITIES_PKG.LogProcedure('AP_WEB_DB_EXPDIST_PKG',
190 'start MoveDistributions');
191
192 /* If there is no distribution line, add one. */
193 BEGIN
194 /* Bug# 6131435 :
195 Added rownum condition to avoid TOO_MANY_ROWS_RETURNED exception. */
196
197 SELECT report_line_id
198 INTO l_has_dist
199 FROM ap_exp_report_dists
200 WHERE report_line_id = p_source_report_line_id
201 AND ROWNUM = 1;
202
203
204 EXCEPTION
205 WHEN NO_DATA_FOUND THEN
206 AddDistributionLine( p_report_line_id => p_target_report_line_id);
207 RETURN;
208 WHEN OTHERS THEN
209 AP_WEB_DB_UTIL_PKG.RaiseException('MoveDistributions');
210 APP_EXCEPTION.RAISE_EXCEPTION;
211 END;
212
213
214 -- For the given line, move its distributions from original ER
215 -- to the new ER
216 UPDATE AP_EXP_REPORT_DISTS
217 SET report_header_id = p_target_report_header_id,
218 report_line_id = p_target_report_line_id
219 WHERE report_line_id = p_source_report_line_id;
220
221 AP_WEB_UTILITIES_PKG.LogProcedure('AP_WEB_DB_EXPDIST_PKG',
222 'end MoveDistributions');
223
224 EXCEPTION
225 WHEN NO_DATA_FOUND THEN
226 NULL;
227 WHEN OTHERS THEN
228 AP_WEB_DB_UTIL_PKG.RaiseException('MoveDistributions');
229 APP_EXCEPTION.RAISE_EXCEPTION;
230 END MoveDistributions;
231
232 -------------------------------------------------------------------
233 -- Name: DuplicateDistributions
234 -- Desc: duplicates Expense Report Distributions
235 -- Input: p_target_report_header_id - target expense report header id
236 -- Input: p_source_report_line_id - source expense report line id
237 -- Input: p_target_report_line_id - target expense report line id
238 -------------------------------------------------------------------
239 PROCEDURE DuplicateDistributions(
240 p_user_id IN NUMBER,
241 p_target_report_header_id IN expDist_report_header_ID,
242 p_source_report_line_id IN expDist_report_line_ID,
243 p_target_report_line_id IN expDist_report_line_ID) IS
244
245 BEGIN
246 AP_WEB_UTILITIES_PKG.LogProcedure('AP_WEB_DB_EXPDIST_PKG',
247 'start DuplicateDistributions');
248
249 -- For the given line, duplicate its distributions
250 INSERT INTO AP_EXP_REPORT_DISTS
251 (
252 report_header_id,
253 report_line_id,
254 report_distribution_id,
255 org_id,
256 sequence_num,
257 last_update_date,
258 last_updated_by,
259 creation_date,
260 created_by,
261 code_combination_id,
262 segment1,
263 segment2,
264 segment3,
265 segment4,
266 segment5,
267 segment6,
268 segment7,
269 segment8,
270 segment9,
271 segment10,
272 segment11,
273 segment12,
274 segment13,
275 segment14,
276 segment15,
277 segment16,
278 segment17,
279 segment18,
280 segment19,
281 segment20,
282 segment21,
283 segment22,
284 segment23,
285 segment24,
286 segment25,
287 segment26,
288 segment27,
289 segment28,
290 segment29,
291 segment30,
292 amount,
293 project_id,
294 task_id,
295 award_id,
296 expenditure_organization_id,
297 cost_center,
298 preparer_modified_flag
299 )
300 SELECT
301 p_target_report_header_id AS report_header_id,
302 p_target_report_line_id AS report_line_id,
303 AP_EXP_REPORT_DISTS_S.NEXTVAL AS report_distribution_id,
304 org_id,
305 sequence_num,
306 SYSDATE AS last_update_date,
307 nvl(p_user_id,last_updated_by) AS last_updated_by,
308 SYSDATE AS creation_date,
309 nvl(p_user_id, created_by) AS created_by,
310 code_combination_id,
311 segment1,
312 segment2,
313 segment3,
314 segment4,
315 segment5,
316 segment6,
317 segment7,
318 segment8,
319 segment9,
320 segment10,
321 segment11,
322 segment12,
323 segment13,
324 segment14,
325 segment15,
326 segment16,
327 segment17,
328 segment18,
329 segment19,
330 segment20,
331 segment21,
332 segment22,
333 segment23,
334 segment24,
335 segment25,
336 segment26,
337 segment27,
338 segment28,
339 segment29,
340 segment30,
341 amount,
342 project_id,
343 task_id,
344 award_id,
345 expenditure_organization_id,
346 cost_center,
347 preparer_modified_flag
348 FROM AP_EXP_REPORT_DISTS
349 WHERE
350 report_line_id = p_source_report_line_id;
351
352
353 AP_WEB_UTILITIES_PKG.LogProcedure('AP_WEB_DB_EXPDIST_PKG',
354 'end DuplicateDistributions');
355
356 EXCEPTION
357 WHEN NO_DATA_FOUND THEN
358 NULL;
359 WHEN OTHERS THEN
360 AP_WEB_DB_UTIL_PKG.RaiseException('DuplicateDistributions');
361 APP_EXCEPTION.RAISE_EXCEPTION;
362 END DuplicateDistributions;
363
364 --------------------------------------------------------------------------------
365 PROCEDURE updateAccountValues(
366 p_report_header_id IN expDist_report_header_ID,
367 p_report_line_id IN expDist_REPORT_LINE_ID,
368 p_report_distribution_id IN expDist_REPORT_DISTRIBUTION_ID,
369 p_ccid IN expDist_CODE_COMBINATION_ID)
370 IS
371 --------------------------------------------------------------------------------
372 l_sequence_num NUMBER := 0;
373 l_has_dist NUMBER;
374 l_report_distribution_id NUMBER;
375
376 BEGIN
377
378 l_report_distribution_id := p_report_distribution_id;
379
380 /* If there is no distribution line, add one.
381 p_report_distribution_id is null indicates that there is no
382 distribution for the line. */
383
384 if (p_report_distribution_id is null) then
385
386 AddDistributionLine( p_report_line_id => p_report_line_id);
387
388
389 BEGIN
390 SELECT report_distribution_id
391 INTO l_report_distribution_id
392 FROM ap_exp_report_dists
393 WHERE report_header_id = p_report_header_id
394 AND report_line_id = p_report_line_id
395 AND rownum = 1;
396
397 EXCEPTION
398 WHEN NO_DATA_FOUND THEN
399 RETURN;
400 WHEN OTHERS THEN
401 AP_WEB_DB_UTIL_PKG.RaiseException('updateAccountValues');
402 APP_EXCEPTION.RAISE_EXCEPTION;
403 END;
404
405 end if;
406
407 UPDATE ap_exp_report_dists
408 SET (code_combination_id,
409 segment1,
410 segment2,
411 segment3,
412 segment4,
413 segment5,
414 segment6,
415 segment7,
416 segment8,
417 segment9,
418 segment10,
419 segment11,
420 segment12,
421 segment13,
422 segment14,
423 segment15,
424 segment16,
425 segment17,
426 segment18,
427 segment19,
428 segment20,
429 segment21,
430 segment22,
431 segment23,
432 segment24,
433 segment25,
434 segment26,
435 segment27,
436 segment28,
437 segment29,
438 segment30,
439 cost_center) = (SELECT nvl(code_combination_id,p_ccid),
440 segment1,
441 segment2,
442 segment3,
443 segment4,
444 segment5,
445 segment6,
446 segment7,
447 segment8,
448 segment9,
449 segment10,
450 segment11,
451 segment12,
452 segment13,
453 segment14,
454 segment15,
455 segment16,
456 segment17,
457 segment18,
458 segment19,
459 segment20,
460 segment21,
461 segment22,
462 segment23,
463 segment24,
464 segment25,
465 segment26,
466 segment27,
467 segment28,
468 segment29,
469 segment30,
470 AP_WEB_DB_EXPDIST_PKG.GetCCSegmentValue(p_ccid) -- Bug 7395568
471 FROM GL_CODE_COMBINATIONS
472 WHERE code_combination_id = p_ccid)
473 WHERE report_distribution_id = l_report_distribution_id
474 AND report_header_id = p_report_header_id;
475
476 EXCEPTION
477 WHEN OTHERS THEN
478 AP_WEB_DB_UTIL_PKG.RaiseException('updateAccountValues');
479 APP_EXCEPTION.RAISE_EXCEPTION;
480 END updateAccountValues;
481
482 --------------------------------------------------------------------------------
483 PROCEDURE updateAccountValuesForForms(
484 p_report_header_id IN expDist_report_header_ID,
485 p_report_line_id IN expDist_REPORT_LINE_ID,
486 p_report_distribution_id IN expDist_REPORT_DISTRIBUTION_ID,
487 p_ccid IN expDist_CODE_COMBINATION_ID,
488 p_line_amount IN NUMBER)
489 IS
490 --------------------------------------------------------------------------------
491 l_sequence_num NUMBER := 0;
492 l_has_dist NUMBER;
493 l_report_distribution_id NUMBER;
494
495 BEGIN
496
497 l_report_distribution_id := p_report_distribution_id;
498
499 /* If there is no distribution line, add one.
500 p_report_distribution_id is null indicates that there is no
501 distribution for the line. */
502
503 if (p_report_distribution_id is null) then
504
505 AddDistributionLine( p_report_line_id => p_report_line_id);
506
507
508 BEGIN
509 SELECT report_distribution_id
510 INTO l_report_distribution_id
511 FROM ap_exp_report_dists
512 WHERE report_header_id = p_report_header_id
513 AND report_line_id = p_report_line_id
514 AND rownum = 1;
515
516 EXCEPTION
517 WHEN NO_DATA_FOUND THEN
518 RETURN;
519 WHEN OTHERS THEN
520 AP_WEB_DB_UTIL_PKG.RaiseException('updateAccountValues');
521 APP_EXCEPTION.RAISE_EXCEPTION;
522 END;
523
524 end if;
525
526 UPDATE ap_exp_report_dists
527 SET (code_combination_id,
528 segment1,
529 segment2,
530 segment3,
531 segment4,
532 segment5,
533 segment6,
534 segment7,
535 segment8,
536 segment9,
537 segment10,
538 segment11,
539 segment12,
540 segment13,
541 segment14,
542 segment15,
543 segment16,
544 segment17,
545 segment18,
546 segment19,
547 segment20,
548 segment21,
549 segment22,
550 segment23,
551 segment24,
552 segment25,
553 segment26,
554 segment27,
555 segment28,
556 segment29,
557 segment30,
558 cost_center) = (SELECT nvl(code_combination_id,p_ccid),
559 segment1,
560 segment2,
561 segment3,
562 segment4,
563 segment5,
564 segment6,
565 segment7,
566 segment8,
567 segment9,
568 segment10,
569 segment11,
570 segment12,
571 segment13,
572 segment14,
573 segment15,
574 segment16,
575 segment17,
576 segment18,
577 segment19,
578 segment20,
579 segment21,
580 segment22,
581 segment23,
582 segment24,
583 segment25,
584 segment26,
585 segment27,
586 segment28,
587 segment29,
588 segment30,
589 AP_WEB_DB_EXPDIST_PKG.GetCCSegmentValue(p_ccid) -- Bug 7395568
590 FROM GL_CODE_COMBINATIONS
591 WHERE code_combination_id = p_ccid),
592 amount = p_line_amount
593 WHERE report_distribution_id = l_report_distribution_id
594 AND report_header_id = p_report_header_id;
595
596 EXCEPTION
597 WHEN OTHERS THEN
598 AP_WEB_DB_UTIL_PKG.RaiseException('updateAccountValuesForForms');
599 APP_EXCEPTION.RAISE_EXCEPTION;
600 END updateAccountValuesForForms;
601
602 --------------------------------------------------------------------------------
603 PROCEDURE updateDistAccountValues(
604 p_report_header_id IN expDist_report_header_ID)
605 IS
606 --------------------------------------------------------------------------------
607 CURSOR dist_lines_c IS
608 SELECT *
609 FROM AP_EXP_REPORT_DISTS_ALL
610 WHERE REPORT_HEADER_ID = p_report_header_id
611 FOR UPDATE OF REPORT_HEADER_ID NOWAIT;
612
613 dist_lines_rec dist_lines_c%ROWTYPE;
614
615 BEGIN
616
617 OPEN dist_lines_c;
618
619 LOOP
620 FETCH dist_lines_c into dist_lines_rec;
621 EXIT WHEN dist_lines_c%NOTFOUND;
622
623 IF dist_lines_rec.CODE_COMBINATION_ID IS NOT NULL THEN
624 UPDATE ap_exp_report_dists_all
625 SET (segment1,
626 segment2,
627 segment3,
628 segment4,
629 segment5,
630 segment6,
631 segment7,
632 segment8,
633 segment9,
634 segment10,
635 segment11,
636 segment12,
637 segment13,
638 segment14,
639 segment15,
640 segment16,
641 segment17,
642 segment18,
643 segment19,
644 segment20,
645 segment21,
646 segment22,
647 segment23,
648 segment24,
649 segment25,
650 segment26,
651 segment27,
652 segment28,
653 segment29,
654 segment30,
655 cost_center) = (SELECT segment1,
656 segment2,
657 segment3,
658 segment4,
659 segment5,
660 segment6,
661 segment7,
662 segment8,
663 segment9,
664 segment10,
665 segment11,
666 segment12,
667 segment13,
668 segment14,
669 segment15,
670 segment16,
671 segment17,
672 segment18,
673 segment19,
674 segment20,
675 segment21,
676 segment22,
677 segment23,
678 segment24,
679 segment25,
680 segment26,
681 segment27,
682 segment28,
683 segment29,
684 segment30,
685 AP_WEB_DB_EXPDIST_PKG.GetCCSegmentValue(dist_lines_rec.CODE_COMBINATION_ID) -- Bug 7395568
686 FROM GL_CODE_COMBINATIONS
687 WHERE code_combination_id = dist_lines_rec.CODE_COMBINATION_ID)
688 WHERE CURRENT OF dist_lines_c;
689
690 END IF;
691 END LOOP;
692
693 CLOSE dist_lines_c;
694
695 EXCEPTION
696 WHEN OTHERS THEN
697 AP_WEB_DB_UTIL_PKG.RaiseException('updateDistAccountValues');
698 APP_EXCEPTION.RAISE_EXCEPTION;
699 END updateDistAccountValues;
700
701 --------------------------------------------------------------------------------
702 PROCEDURE updateDistAcctValuesForForms(
703 p_report_header_id IN expDist_report_header_ID)
704 IS
705 --------------------------------------------------------------------------------
706 l_report_distribution_id expDist_REPORT_DISTRIBUTION_ID;
707 c_line_ccid_cursor AP_WEB_DB_EXPLINE_PKG.ExpLineCCIDCursor;
708 l_report_line_id AP_WEB_DB_EXPLINE_PKG.expLines_report_line_id;
709 l_line_ccid AP_WEB_DB_EXPLINE_PKG.expLines_codeCombID;
710 l_dist_id AP_EXP_REPORT_DISTS.REPORT_DISTRIBUTION_ID%TYPE;
711 l_line_amount NUMBER;
712 BEGIN
713
714 IF (AP_WEB_DB_EXPLINE_PKG.GetLineCCIDCursor(p_report_header_id, c_line_ccid_cursor)) THEN
715
716 LOOP
717 FETCH c_line_ccid_cursor INTO l_report_line_id, l_line_ccid, l_line_amount;
718 EXIT WHEN c_line_ccid_cursor%NOTFOUND;
719
720 BEGIN
721 /* If there is no distribution line, add one. */
722 SELECT report_distribution_id
723 INTO l_dist_id
724 FROM ap_exp_report_dists
725 WHERE report_header_id = p_report_header_id
726 AND report_line_id = l_report_line_id;
727
728 EXCEPTION
729 WHEN NO_DATA_FOUND THEN
730 l_dist_id := null;
731 WHEN OTHERS THEN
732 AP_WEB_DB_UTIL_PKG.RaiseException('updateAccountValues');
733 APP_EXCEPTION.RAISE_EXCEPTION;
734 END;
735
736 updateAccountValuesForForms(p_report_header_id, l_report_line_id, l_dist_id, l_line_ccid, l_line_amount);
737
738 END LOOP;
739 END IF;
740
741 END updateDistAcctValuesForForms;
742
743 --------------------------------------------------------------------------------
744 PROCEDURE AddDistributionLine(
745 p_report_line_id IN AP_EXPENSE_REPORT_LINES.REPORT_LINE_ID%TYPE)
746 IS
747 --------------------------------------------------------------------------------
748 l_debug_info varchar2(1000);
749 l_sequence_num NUMBER;
750
751 BEGIN
752
753 AP_WEB_UTILITIES_PKG.LogProcedure('AP_WEB_DB_EXPDIST_PKG',
754 'start AddDistributionLine');
755 l_sequence_num := 0;
756
757 -- For the given line, duplicate its distributions
758 INSERT INTO AP_EXP_REPORT_DISTS
759 (
760 report_header_id,
761 report_line_id,
762 report_distribution_id,
763 sequence_num,
764 last_update_date,
765 last_updated_by,
766 creation_date,
767 created_by,
768 code_combination_id,
769 segment1,
770 segment2,
771 segment3,
772 segment4,
773 segment5,
774 segment6,
775 segment7,
776 segment8,
777 segment9,
778 segment10,
779 segment11,
780 segment12,
781 segment13,
782 segment14,
783 segment15,
784 segment16,
785 segment17,
786 segment18,
787 segment19,
788 segment20,
789 segment21,
790 segment22,
791 segment23,
792 segment24,
793 segment25,
794 segment26,
795 segment27,
796 segment28,
797 segment29,
798 segment30,
799 org_id,
800 amount,
801 project_id,
802 task_id,
803 award_id,
804 expenditure_organization_id,
805 cost_center,
806 web_parameter_id
807 )
808 SELECT
809 XL.report_header_id,
810 XL.report_line_id,
811 AP_EXP_REPORT_DISTS_S.NEXTVAL,
812 l_sequence_num,
813 SYSDATE,
814 XL.last_updated_by,
815 SYSDATE,
816 XL.created_by,
817 XL.code_combination_id,
818 GL.segment1,
819 GL.segment2,
820 GL.segment3,
821 GL.segment4,
822 GL.segment5,
823 GL.segment6,
824 GL.segment7,
825 GL.segment8,
826 GL.segment9,
827 GL.segment10,
828 GL.segment11,
829 GL.segment12,
830 GL.segment13,
831 GL.segment14,
832 GL.segment15,
833 GL.segment16,
834 GL.segment17,
835 GL.segment18,
836 GL.segment19,
837 GL.segment20,
838 GL.segment21,
839 GL.segment22,
840 GL.segment23,
841 GL.segment24,
842 GL.segment25,
843 GL.segment26,
844 GL.segment27,
845 GL.segment28,
846 GL.segment29,
847 GL.segment30,
848 XL.org_id,
849 XL.amount,
850 XL.project_id,
851 XL.task_id,
852 XL.award_id,
853 XL.expenditure_organization_id,
854 XH.flex_concatenated, -- Bug: 6735020, flex_concatenated should be fetched from headers
855 XL.web_parameter_id
856 FROM AP_EXPENSE_REPORT_LINES XL,
857 AP_EXPENSE_REPORT_HEADERS XH,
858 GL_CODE_COMBINATIONS GL
859 WHERE XL.report_line_id = p_report_line_id
860 AND XL.report_header_id = XH.report_header_id
861 AND GL.code_combination_id(+) = XL.code_combination_id;
862
863
864
865
866 AP_WEB_UTILITIES_PKG.LogProcedure('AP_WEB_DB_EXPDIST_PKG',
867 'end AddDistributionLine');
868
869
870 EXCEPTION
871 WHEN OTHERS THEN
872 AP_WEB_DB_UTIL_PKG.RaiseException('AddDistributionLine');
873 APP_EXCEPTION.RAISE_EXCEPTION;
874 END AddDistributionLine;
875
876 --------------------------------------------------------------------------------
877 PROCEDURE AddDistributionLine(
878 p_segments IN AP_OIE_KFF_SEGMENTS_T,
879 p_report_line_id IN AP_EXPENSE_REPORT_LINES.REPORT_LINE_ID%TYPE,
880 p_chart_of_accounts_id IN NUMBER)
881 IS
882 --------------------------------------------------------------------------------
883 l_debug_info varchar2(1000);
884 l_sequence_num NUMBER;
885 l_colname fnd_id_flex_segments.application_column_name%type;
886 l_sqlstmt varchar2(20000);
887 l_col_stmt varchar2(5000);
888 l_temp long;
889 l_cursor integer;
890 l_rows integer;
891 l_report_header_id AP_WEB_DB_EXPLINE_PKG.expLines_headerID;
892 l_last_updated_by NUMBER;
893 l_created_by NUMBER;
894 l_report_distribution_id expDist_REPORT_DISTRIBUTION_ID;
895 l_code_combination_id expDist_CODE_COMBINATION_ID;
896 l_org_id NUMBER;
897 l_amount ap_exp_report_dists_all.amount%TYPE;
898 l_project_id ap_exp_report_dists_all.project_id%TYPE;
899 l_task_id ap_exp_report_dists_all.task_id%TYPE;
900 l_award_id ap_exp_report_dists_all.award_id%TYPE;
901 l_expenditure_organization_id ap_exp_report_dists_all.expenditure_organization_id%TYPE;
902 l_cost_center ap_exp_report_dists_all.cost_center%TYPE;
903 l_cost_center_seg_num NUMBER;
904 l_chart_of_accounts_id gl_sets_of_books.chart_of_accounts_id%type;
905
906 -- BUG 7025517
907 p_seg1 varchar2(25) := null;
908 p_seg2 varchar2(25) := null;
909 p_seg3 varchar2(25) := null;
910 p_seg4 varchar2(25) := null;
911 p_seg5 varchar2(25) := null;
912 p_seg6 varchar2(25) := null;
913 p_seg7 varchar2(25) := null;
914 p_seg8 varchar2(25) := null;
915 p_seg9 varchar2(25) := null;
916 p_seg10 varchar2(25) := null;
917 p_seg11 varchar2(25) := null;
918 p_seg12 varchar2(25) := null;
919 p_seg13 varchar2(25) := null;
920 p_seg14 varchar2(25) := null;
921 p_seg15 varchar2(25) := null;
922 p_seg16 varchar2(25) := null;
923 p_seg17 varchar2(25) := null;
924 p_seg18 varchar2(25) := null;
925 p_seg19 varchar2(25) := null;
926 p_seg20 varchar2(25) := null;
927 p_seg21 varchar2(25) := null;
928 p_seg22 varchar2(25) := null;
929 p_seg23 varchar2(25) := null;
930 p_seg24 varchar2(25) := null;
931 p_seg25 varchar2(25) := null;
932 p_seg26 varchar2(25) := null;
933 p_seg27 varchar2(25) := null;
934 p_seg28 varchar2(25) := null;
935 p_seg29 varchar2(25) := null;
936 p_seg30 varchar2(25) := null;
937
938
939
940
941
942 BEGIN
943
944 AP_WEB_UTILITIES_PKG.LogProcedure('AP_WEB_DB_EXPDIST_PKG',
945 'start AddDistributionLine');
946
947 -----------------------------------------------------
948 l_debug_info := 'AP_WEB_DB_AP_INT_PKG.GetCOAofSOB';
949 -----------------------------------------------------
950 IF (NOT AP_WEB_DB_AP_INT_PKG.GetCOAofSOB(l_chart_of_accounts_id)) THEN
951 l_chart_of_accounts_id := NULL;
952 END IF;
953
954 -----------------------------------------------------
955 l_debug_info := 'Get the segment number corresponding to the costcenter qualifier.';
956 -----------------------------------------------------
957 IF (NOT FND_FLEX_APIS.GET_QUALIFIER_SEGNUM(
958 101,
959 'GL#',
960 l_chart_of_accounts_id,
961 'FA_COST_CTR',
962 l_cost_center_seg_num)) then
963 return;
964 END IF;
965 -- Bug 7395568, get the cost center
966 l_cost_center := p_segments(l_cost_center_seg_num);
967
968 -----------------------------------------------------
969 l_debug_info := 'select from ap_expense_report_lines';
970 -----------------------------------------------------
971 l_sequence_num := 0;
972 SELECT XL.report_header_id,
973 XL.code_combination_id,
974 XL.last_updated_by,
975 XL.created_by,
976 XL.org_id,
977 XL.amount,
978 XL.project_id,
979 XL.task_id,
980 XL.award_id,
981 XL.expenditure_organization_id,
982 XH.flex_concatenated -- Bug 6735020, flex_concatenated should be fetched from headers
983 INTO l_report_header_id,
984 l_code_combination_id,
985 l_last_updated_by,
986 l_created_by,
987 l_org_id,
988 l_amount,
989 l_project_id,
990 l_task_id,
991 l_award_id,
992 l_expenditure_organization_id,
993 l_cost_center
994 FROM ap_expense_report_lines XL,
995 ap_expense_report_headers XH
996 WHERE XL.report_line_id = p_report_line_id
997 AND XL.report_header_id = XH.report_header_id;
998
999 -----------------------------------------------------
1000 l_debug_info := 'get next AP_EXP_REPORT_DISTS_S';
1001 -----------------------------------------------------
1002 SELECT AP_EXP_REPORT_DISTS_S.NEXTVAL
1003 INTO l_report_distribution_id
1004 FROM DUAL;
1005
1006 l_sqlstmt := 'INSERT INTO AP_EXP_REPORT_DISTS ( ';
1007 l_sqlstmt := l_sqlstmt || 'report_header_id, ';
1008 l_sqlstmt := l_sqlstmt || 'report_line_id, ';
1009 l_sqlstmt := l_sqlstmt || 'report_distribution_id, ';
1010 l_sqlstmt := l_sqlstmt || 'sequence_num, ';
1011 l_sqlstmt := l_sqlstmt || 'last_update_date, ';
1012 l_sqlstmt := l_sqlstmt || 'last_updated_by, ';
1013 l_sqlstmt := l_sqlstmt || 'creation_date, ';
1014 l_sqlstmt := l_sqlstmt || 'created_by, ';
1015 if (l_code_combination_id is not null) then
1016 l_sqlstmt := l_sqlstmt || 'code_combination_id, ';
1017 end if;
1018 if (l_org_id is not null) then
1019 l_sqlstmt := l_sqlstmt || 'org_id, ';
1020 end if;
1021 if (l_amount is not null) then
1022 l_sqlstmt := l_sqlstmt || 'amount, ';
1023 end if;
1024 if (l_project_id is not null) then
1025 l_sqlstmt := l_sqlstmt || 'project_id, ';
1026 end if;
1027 if (l_task_id is not null) then
1028 l_sqlstmt := l_sqlstmt || 'task_id, ';
1029 end if;
1030 if (l_award_id is not null) then
1031 l_sqlstmt := l_sqlstmt || 'award_id, ';
1032 end if;
1033 if (l_expenditure_organization_id is not null) then
1034 l_sqlstmt := l_sqlstmt || 'expenditure_organization_id, ';
1035 end if;
1036 if (l_cost_center is not null) then
1037 l_sqlstmt := l_sqlstmt || 'cost_center, ';
1038 end if;
1039
1040 -- Construct the segment columns in the insert clause
1041 if cflex%isopen then close cflex;
1042 end if;
1043
1044 open cflex(p_chart_of_accounts_id);
1045 LOOP
1046 FETCH cflex INTO l_colname;
1047 EXIT WHEN cflex%NOTFOUND;
1048
1049 IF l_col_stmt IS NOT NULL THEN
1050 l_col_stmt := l_col_stmt || ',' || l_colname;
1051 ELSE
1052 l_col_stmt := l_colname;
1053 END IF;
1054 END LOOP;
1055
1056 l_sqlstmt := l_sqlstmt || l_col_stmt;
1057
1058
1059 l_sqlstmt := l_sqlstmt || ') values (';
1060
1061
1062 /* BUG 7025517 -- refer to bind vars in values clause of insert statement */
1063 /* l_sqlstmt := l_sqlstmt || l_report_header_id;
1064 ** l_sqlstmt := l_sqlstmt || ', ' || p_report_line_id;
1065 ** l_sqlstmt := l_sqlstmt || ', ' || l_report_distribution_id;
1066 ** l_sqlstmt := l_sqlstmt || ', ' || l_sequence_num;
1067 ** l_sqlstmt := l_sqlstmt || ', ' || '''' || sysdate || '''';
1068 ** l_sqlstmt := l_sqlstmt || ', ' || l_last_updated_by;
1069 ** l_sqlstmt := l_sqlstmt || ', ' || '''' || sysdate || '''';
1070 ** l_sqlstmt := l_sqlstmt || ', ' || l_created_by;
1071 ** if (l_code_combination_id is not null) then
1072 ** l_sqlstmt := l_sqlstmt || ', ' || '''' || l_code_combination_id || '''';
1073 ** end if;
1074 ** if (l_org_id is not null) then
1075 ** l_sqlstmt := l_sqlstmt || ', ' || l_org_id;
1076 ** end if;
1077 ** if (l_amount is not null) then
1078 ** l_sqlstmt := l_sqlstmt || ', ' || l_amount;
1079 ** end if;
1080 ** if (l_project_id is not null) then
1081 ** l_sqlstmt := l_sqlstmt || ', ' || l_project_id;
1082 ** end if;
1083 ** if (l_task_id is not null) then
1084 ** l_sqlstmt := l_sqlstmt || ', ' || l_task_id;
1085 ** end if;
1086 ** if (l_award_id is not null) then
1087 ** l_sqlstmt := l_sqlstmt || ', ' || l_award_id;
1088 ** end if;
1089 ** if (l_expenditure_organization_id is not null) then
1090 ** l_sqlstmt := l_sqlstmt || ', ' || l_expenditure_organization_id;
1091 ** end if;
1092 ** if (l_cost_center is not null) then
1093 ** l_sqlstmt := l_sqlstmt || ', ' || '''' || l_cost_center || '''';
1094 ** end if;
1095 */
1096 /* BUG 7025517 -- refer to bind vars in values clause of insert statement */
1097 l_sqlstmt := l_sqlstmt || ' :l_report_header_id' ;
1098 l_sqlstmt := l_sqlstmt || ', :p_report_line_id' ;
1099 l_sqlstmt := l_sqlstmt || ', :l_report_distribution_id' ;
1100 l_sqlstmt := l_sqlstmt || ', :l_sequence_num' ;
1101 l_sqlstmt := l_sqlstmt || ', sysdate' ;
1102 l_sqlstmt := l_sqlstmt || ', :l_last_updated_by' ;
1103 l_sqlstmt := l_sqlstmt || ', sysdate' ;
1104 l_sqlstmt := l_sqlstmt || ', :l_created_by' ;
1105 if (l_code_combination_id is not null) then
1106 l_sqlstmt := l_sqlstmt || ', :l_code_combination_id' ;
1107 end if;
1108 if (l_org_id is not null) then
1109 l_sqlstmt := l_sqlstmt || ', :l_org_id' ;
1110 end if;
1111 if (l_amount is not null) then
1112 l_sqlstmt := l_sqlstmt || ', :l_amount' ;
1113 end if;
1114 if (l_project_id is not null) then
1115 l_sqlstmt := l_sqlstmt || ', :l_project_id' ;
1116 end if;
1117 if (l_task_id is not null) then
1118 l_sqlstmt := l_sqlstmt || ', :l_task_id' ;
1119 end if;
1120 if (l_award_id is not null) then
1121 l_sqlstmt := l_sqlstmt || ', :l_award_id' ;
1122 end if;
1123 if (l_expenditure_organization_id is not null) then
1124 l_sqlstmt := l_sqlstmt || ', :l_expenditure_organization_id' ;
1125 end if;
1126 if (l_cost_center is not null) then
1127 l_sqlstmt := l_sqlstmt || ', :l_cost_center' ;
1128 end if;
1129
1130 -- BUG 7025517
1131 -- BUG 7698138
1132 FOR i IN 1..p_segments.count LOOP
1133 case i
1134 when 1 then p_seg1 := p_segments(1);
1135 when 2 then p_seg2 := p_segments(2);
1136 when 3 then p_seg3 := p_segments(3);
1137 when 4 then p_seg4 := p_segments(4);
1138 when 5 then p_seg5 := p_segments(5);
1139 when 6 then p_seg6 := p_segments(6);
1140 when 7 then p_seg7 := p_segments(7);
1141 when 8 then p_seg8 := p_segments(8);
1142 when 9 then p_seg9 := p_segments(9);
1143 when 10 then p_seg10 := p_segments(10);
1144 when 11 then p_seg11 := p_segments(11);
1145 when 12 then p_seg12 := p_segments(12);
1146 when 13 then p_seg13 := p_segments(13);
1147 when 14 then p_seg14 := p_segments(14);
1148 when 15 then p_seg15 := p_segments(15);
1149 when 16 then p_seg16 := p_segments(16);
1150 when 17 then p_seg17 := p_segments(17);
1151 when 18 then p_seg18 := p_segments(18);
1152 when 19 then p_seg19 := p_segments(19);
1153 when 20 then p_seg20 := p_segments(20);
1154 when 21 then p_seg21 := p_segments(21);
1155 when 22 then p_seg22 := p_segments(22);
1156 when 23 then p_seg23 := p_segments(23);
1157 when 24 then p_seg24 := p_segments(24);
1158 when 25 then p_seg25 := p_segments(25);
1159 when 26 then p_seg26 := p_segments(26);
1160 when 27 then p_seg27 := p_segments(27);
1161 when 28 then p_seg28 := p_segments(28);
1162 when 29 then p_seg29 := p_segments(29);
1163 when 30 then p_seg30 := p_segments(30);
1164
1165 end case;
1166 END LOOP;
1167
1168 --FOR i IN 1..p_segments.count LOOP
1169 -- if (p_segments(i) is not null) then
1170 -- l_sqlstmt := l_sqlstmt || ',' || '''' || p_segments(i) || '''';
1171 -- end if;
1172 --END LOOP;
1173 -- ........... l_sqlstmt := l_sqlstmt || ', :l_cost_center' ;
1174
1175 -- BUG 7698138
1176 if (p_seg1 is not null) then l_sqlstmt := l_sqlstmt || ', :p_seg1' ; end if ;
1177 if (p_seg2 is not null) then l_sqlstmt := l_sqlstmt || ', :p_seg2' ; end if ;
1178 if (p_seg3 is not null) then l_sqlstmt := l_sqlstmt || ', :p_seg3' ; end if ;
1179 if (p_seg4 is not null) then l_sqlstmt := l_sqlstmt || ', :p_seg4' ; end if ;
1180 if (p_seg5 is not null) then l_sqlstmt := l_sqlstmt || ', :p_seg5' ; end if ;
1181 if (p_seg6 is not null) then l_sqlstmt := l_sqlstmt || ', :p_seg6' ; end if ;
1182 if (p_seg7 is not null) then l_sqlstmt := l_sqlstmt || ', :p_seg7' ; end if ;
1183 if (p_seg8 is not null) then l_sqlstmt := l_sqlstmt || ', :p_seg8' ; end if ;
1184 if (p_seg9 is not null) then l_sqlstmt := l_sqlstmt || ', :p_seg9' ; end if ;
1185 if (p_seg10 is not null) then l_sqlstmt := l_sqlstmt || ', :p_seg10' ; end if ;
1186 if (p_seg11 is not null) then l_sqlstmt := l_sqlstmt || ', :p_seg11' ; end if ;
1187 if (p_seg12 is not null) then l_sqlstmt := l_sqlstmt || ', :p_seg12' ; end if ;
1188 if (p_seg13 is not null) then l_sqlstmt := l_sqlstmt || ', :p_seg13' ; end if ;
1189 if (p_seg14 is not null) then l_sqlstmt := l_sqlstmt || ', :p_seg14' ; end if ;
1190 if (p_seg15 is not null) then l_sqlstmt := l_sqlstmt || ', :p_seg15' ; end if ;
1191 if (p_seg16 is not null) then l_sqlstmt := l_sqlstmt || ', :p_seg16' ; end if ;
1192 if (p_seg17 is not null) then l_sqlstmt := l_sqlstmt || ', :p_seg17' ; end if ;
1193 if (p_seg18 is not null) then l_sqlstmt := l_sqlstmt || ', :p_seg18' ; end if ;
1194 if (p_seg19 is not null) then l_sqlstmt := l_sqlstmt || ', :p_seg19' ; end if ;
1195 if (p_seg20 is not null) then l_sqlstmt := l_sqlstmt || ', :p_seg20' ; end if ;
1196 if (p_seg21 is not null) then l_sqlstmt := l_sqlstmt || ', :p_seg21' ; end if ;
1197 if (p_seg22 is not null) then l_sqlstmt := l_sqlstmt || ', :p_seg22' ; end if ;
1198 if (p_seg23 is not null) then l_sqlstmt := l_sqlstmt || ', :p_seg23' ; end if ;
1199 if (p_seg24 is not null) then l_sqlstmt := l_sqlstmt || ', :p_seg24' ; end if ;
1200 if (p_seg25 is not null) then l_sqlstmt := l_sqlstmt || ', :p_seg25' ; end if ;
1201 if (p_seg26 is not null) then l_sqlstmt := l_sqlstmt || ', :p_seg26' ; end if ;
1202 if (p_seg27 is not null) then l_sqlstmt := l_sqlstmt || ', :p_seg27' ; end if ;
1203 if (p_seg28 is not null) then l_sqlstmt := l_sqlstmt || ', :p_seg28' ; end if ;
1204 if (p_seg29 is not null) then l_sqlstmt := l_sqlstmt || ', :p_seg29' ; end if ;
1205 if (p_seg30 is not null) then l_sqlstmt := l_sqlstmt || ', :p_seg30' ; end if ;
1206
1207
1208 l_sqlstmt := l_sqlstmt || ')';
1209
1210 -----------------------------------------------------
1211 l_debug_info := 'l_sqlstmt = '||l_sqlstmt;
1212 -----------------------------------------------------
1213 -----------------
1214 -- Now execute it
1215 -----------------
1216 l_cursor := dbms_sql.open_cursor;
1217
1218 -----------------------------------------------------
1219 l_debug_info := 'parse cursor';
1220 -----------------------------------------------------
1221 dbms_sql.parse(l_cursor, l_sqlstmt, dbms_sql.native);
1222
1223
1224 /* BUG 7025517 -- bind the input vars */
1225 dbms_sql.bind_variable(l_cursor,':l_report_header_id', l_report_header_id) ;
1226 dbms_sql.bind_variable(l_cursor,':p_report_line_id', p_report_line_id) ;
1227 dbms_sql.bind_variable(l_cursor,':l_report_distribution_id', l_report_distribution_id) ;
1228 dbms_sql.bind_variable(l_cursor,':l_sequence_num', l_sequence_num) ;
1229 dbms_sql.bind_variable(l_cursor,':l_last_updated_by', l_last_updated_by) ;
1230 dbms_sql.bind_variable(l_cursor,':l_created_by', l_created_by) ;
1231 if (l_code_combination_id is not null) then
1232 dbms_sql.bind_variable(l_cursor,':l_code_combination_id', l_code_combination_id) ;
1233 end if;
1234 if (l_org_id is not null) then
1235 dbms_sql.bind_variable(l_cursor,':l_org_id', l_org_id) ;
1236 end if;
1237 if (l_amount is not null) then
1238 dbms_sql.bind_variable(l_cursor,':l_amount', l_amount) ;
1239 end if;
1240 if (l_project_id is not null) then
1241 dbms_sql.bind_variable(l_cursor,':l_project_id', l_project_id) ;
1242 end if;
1243 if (l_task_id is not null) then
1244 dbms_sql.bind_variable(l_cursor,':l_task_id', l_task_id) ;
1245 end if;
1246 if (l_award_id is not null) then
1247 dbms_sql.bind_variable(l_cursor,':l_award_id', l_award_id) ;
1248 end if;
1249 if (l_expenditure_organization_id is not null) then
1250 dbms_sql.bind_variable(l_cursor,':l_expenditure_organization_id', l_expenditure_organization_id) ;
1251 end if;
1252 if (l_cost_center is not null) then
1253 dbms_sql.bind_variable(l_cursor,':l_cost_center', l_cost_center) ;
1254 end if;
1255
1256 if (p_seg1 is not null) then dbms_sql.bind_variable(l_cursor,':p_seg1', p_seg1) ; end if ;
1257 if (p_seg2 is not null) then dbms_sql.bind_variable(l_cursor,':p_seg2', p_seg2) ; end if ;
1258 if (p_seg3 is not null) then dbms_sql.bind_variable(l_cursor,':p_seg3', p_seg3) ; end if ;
1259 if (p_seg4 is not null) then dbms_sql.bind_variable(l_cursor,':p_seg4', p_seg4) ; end if ;
1260 if (p_seg5 is not null) then dbms_sql.bind_variable(l_cursor,':p_seg5', p_seg5) ; end if ;
1261 if (p_seg6 is not null) then dbms_sql.bind_variable(l_cursor,':p_seg6', p_seg6) ; end if ;
1262 if (p_seg7 is not null) then dbms_sql.bind_variable(l_cursor,':p_seg7', p_seg7) ; end if ;
1263 if (p_seg8 is not null) then dbms_sql.bind_variable(l_cursor,':p_seg8', p_seg8) ; end if ;
1264 if (p_seg9 is not null) then dbms_sql.bind_variable(l_cursor,':p_seg9', p_seg9) ; end if ;
1265 if (p_seg10 is not null) then dbms_sql.bind_variable(l_cursor,':p_seg10', p_seg10) ; end if ;
1266 if (p_seg11 is not null) then dbms_sql.bind_variable(l_cursor,':p_seg11', p_seg11) ; end if ;
1267 if (p_seg12 is not null) then dbms_sql.bind_variable(l_cursor,':p_seg12', p_seg12) ; end if ;
1268 if (p_seg13 is not null) then dbms_sql.bind_variable(l_cursor,':p_seg13', p_seg13) ; end if ;
1269 if (p_seg14 is not null) then dbms_sql.bind_variable(l_cursor,':p_seg14', p_seg14) ; end if ;
1270 if (p_seg15 is not null) then dbms_sql.bind_variable(l_cursor,':p_seg15', p_seg15) ; end if ;
1271 if (p_seg16 is not null) then dbms_sql.bind_variable(l_cursor,':p_seg16', p_seg16) ; end if ;
1272 if (p_seg17 is not null) then dbms_sql.bind_variable(l_cursor,':p_seg17', p_seg17) ; end if ;
1273 if (p_seg18 is not null) then dbms_sql.bind_variable(l_cursor,':p_seg18', p_seg18) ; end if ;
1274 if (p_seg19 is not null) then dbms_sql.bind_variable(l_cursor,':p_seg19', p_seg19) ; end if ;
1275 if (p_seg20 is not null) then dbms_sql.bind_variable(l_cursor,':p_seg20', p_seg20) ; end if ;
1276 if (p_seg21 is not null) then dbms_sql.bind_variable(l_cursor,':p_seg21', p_seg21) ; end if ;
1277 if (p_seg22 is not null) then dbms_sql.bind_variable(l_cursor,':p_seg22', p_seg22) ; end if ;
1278 if (p_seg23 is not null) then dbms_sql.bind_variable(l_cursor,':p_seg23', p_seg23) ; end if ;
1279 if (p_seg24 is not null) then dbms_sql.bind_variable(l_cursor,':p_seg24', p_seg24) ; end if ;
1280 if (p_seg25 is not null) then dbms_sql.bind_variable(l_cursor,':p_seg25', p_seg25) ; end if ;
1281 if (p_seg26 is not null) then dbms_sql.bind_variable(l_cursor,':p_seg26', p_seg26) ; end if ;
1282 if (p_seg27 is not null) then dbms_sql.bind_variable(l_cursor,':p_seg27', p_seg27) ; end if ;
1283 if (p_seg28 is not null) then dbms_sql.bind_variable(l_cursor,':p_seg28', p_seg28) ; end if ;
1284 if (p_seg29 is not null) then dbms_sql.bind_variable(l_cursor,':p_seg29', p_seg29) ; end if ;
1285 if (p_seg30 is not null) then dbms_sql.bind_variable(l_cursor,':p_seg30', p_seg30) ; end if ;
1286
1287
1288 /* BUG 7025517 ... End */
1289
1290
1291 -----------------------------------------------------
1292 l_debug_info := 'execute cursor';
1293 -----------------------------------------------------
1294 l_rows := dbms_sql.execute(l_cursor);
1295
1296
1297 AP_WEB_UTILITIES_PKG.LogProcedure('AP_WEB_DB_EXPDIST_PKG',
1298 'end AddDistributionLine');
1299
1300
1301 EXCEPTION
1302 WHEN OTHERS THEN
1303 IF (SQLCODE <> -20001) THEN
1304 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1305 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1306 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'AddDistributionLine');
1307 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
1308 END IF;
1309 APP_EXCEPTION.RAISE_EXCEPTION;
1310 END AddDistributionLine;
1311 --------------------------------------------------------------------------------
1312
1313
1314 --------------------------------------------------------------------------------
1315 FUNCTION ContainsProjectRelatedDist(
1316 p_report_header_id IN expDist_report_header_ID
1317 ) RETURN BOOLEAN IS
1318 --------------------------------------------------------------------------------
1319 V_Temp VARCHAR2(20);
1320 BEGIN
1321
1322 -- Check if project-related dist exists
1323 SELECT 'dist exists'
1324 INTO V_Temp
1325 FROM AP_EXP_REPORT_DISTS
1326 WHERE REPORT_HEADER_ID = p_report_header_id
1327 AND PROJECT_ID IS NOT NULL
1328 AND TASK_ID IS NOT NULL;
1329
1330 return TRUE;
1331
1332 EXCEPTION
1333 WHEN TOO_MANY_ROWS THEN
1334 return TRUE;
1335 WHEN NO_DATA_FOUND THEN
1336 return FALSE;
1337
1338 WHEN OTHERS THEN
1339 AP_WEB_DB_UTIL_PKG.RaiseException( 'ContainsProjectRelatedDist' );
1340 APP_EXCEPTION.RAISE_EXCEPTION;
1341 return FALSE;
1342 END ContainsProjectRelatedDist;
1343
1344
1345 --------------------------------------------------------------------------------
1346 FUNCTION ContainsNonProjectRelatedDist(
1347 p_report_header_id IN expDist_report_header_ID
1348 ) RETURN BOOLEAN IS
1349 --------------------------------------------------------------------------------
1350 V_Temp VARCHAR2(20);
1351 BEGIN
1352
1353 -- Check if non-project-related dist exists
1354 SELECT 'dist exists'
1355 INTO V_Temp
1356 FROM AP_EXP_REPORT_DISTS
1357 WHERE REPORT_HEADER_ID = p_report_header_id
1358 AND PROJECT_ID IS NULL
1359 AND TASK_ID IS NULL;
1360
1361 return TRUE;
1362
1363 EXCEPTION
1364 WHEN TOO_MANY_ROWS THEN
1365 return TRUE;
1366 WHEN NO_DATA_FOUND THEN
1367 return FALSE;
1368 WHEN OTHERS THEN
1369 AP_WEB_DB_UTIL_PKG.RaiseException( 'ContainsNonProjectRelatedDist' );
1370
1371 APP_EXCEPTION.RAISE_EXCEPTION;
1372 return FALSE;
1373 END ContainsNonProjectRelatedDist;
1374
1375 PROCEDURE ResplitDistAmounts(p_report_line_id IN NUMBER,
1376 p_line_amt IN NUMBER,
1377 p_currency_code IN VARCHAR2) IS
1378
1379 l_dist_sum NUMBER;
1380 l_dist_id NUMBER;
1381 l_dist_amt NUMBER;
1382 l_remainder NUMBER;
1383 l_last_dist_id NUMBER;
1384
1385 cursor c_dists is
1386 select report_distribution_id, amount
1387 from ap_exp_report_dists_all
1388 where report_line_id = p_report_line_id;
1389
1390 BEGIN
1391 select sum(amount) into l_dist_sum
1392 from ap_exp_report_dists_all
1393 where report_line_id = p_report_line_id;
1394
1395 IF (l_dist_sum <> 0) THEN
1396 l_remainder := p_line_amt;
1397 open c_dists;
1398 loop
1399 fetch c_dists into l_dist_id, l_dist_amt;
1400 exit when c_dists%NOTFOUND;
1401
1402 -- Ex: If the New line has an amount of 200 and the previous dists have amounts say
1403 -- 300 and 300, the new dists will be 200 * (300/600) = 100 and 100
1404 -- This logic will work in all cases and is used in ExpenseAllocationAMImpl.java as well.
1405 l_dist_amt := AP_WEB_UTILITIES_PKG.OIE_ROUND_CURRENCY(p_line_amt * (l_dist_amt/l_dist_sum), p_currency_code);
1406
1407 update ap_exp_report_dists_all set amount = l_dist_amt
1408 where report_distribution_id = l_dist_id;
1409 l_remainder := l_remainder - l_dist_amt;
1410 l_last_dist_id := l_dist_id;
1411 end loop;
1412 close c_dists;
1413 -- If there is any line amount still remaining add it to the last line.
1414 if(l_remainder <> 0) then
1415 update ap_exp_report_dists_all set amount = (amount + l_remainder)
1416 where report_distribution_id = l_last_dist_id;
1417 end if;
1418 END IF;
1419 exception
1420 when others then
1421 AP_WEB_DB_UTIL_PKG.RaiseException('ResplitDistAmounts');
1422 APP_EXCEPTION.RAISE_EXCEPTION;
1423 END ResplitDistAmounts;
1424
1425 -- Bug: 7395568
1426 ---------------------------------------------------------------------------------------
1427 FUNCTION GetCCSegmentValue(p_ccid IN expDist_CODE_COMBINATION_ID) RETURN VARCHAR2 IS
1428 ---------------------------------------------------------------------------------------
1429 l_cc_segment_name varchar2(10);
1430 l_segment_value varchar2(25);
1431
1432
1433 BEGIN
1434 -- Fetch the cost center segment name
1435 AP_WEB_ACCTG_PKG.GetCostCenterSegmentName(l_cc_segment_name);
1436
1437 -- Fetch the costcenter segment value.
1438 EXECUTE IMMEDIATE 'select '|| l_cc_segment_name || ' from gl_code_combinations where code_combination_id = :1'
1439 into l_segment_value
1440 using p_ccid;
1441
1442 RETURN l_segment_value;
1443
1444 EXCEPTION WHEN OTHERS THEN
1445 AP_WEB_UTILITIES_PKG.LogProcedure('AP_WEB_DB_EXPDIST_PKG',
1446 'Exception getting segment value ' || SQLERRM);
1447 -- Return Null for exception.
1448 RETURN NULL;
1449 END GetCCSegmentValue;
1450
1451 END AP_WEB_DB_EXPDIST_PKG;
[Home] [Help]