[Home] [Help]
PACKAGE BODY: APPS.AP_WEB_DB_EXPDIST_PKG
Source
1 PACKAGE BODY AP_WEB_DB_EXPDIST_PKG AS
2 /* $Header: apwdbedb.pls 120.13.12010000.3 2008/09/29 19:55:44 skoukunt 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 )
299 SELECT
300 p_target_report_header_id AS report_header_id,
301 p_target_report_line_id AS report_line_id,
302 AP_EXP_REPORT_DISTS_S.NEXTVAL AS report_distribution_id,
303 org_id,
304 sequence_num,
305 SYSDATE AS last_update_date,
306 p_user_id AS last_updated_by,
307 SYSDATE AS creation_date,
308 p_user_id AS created_by,
309 code_combination_id,
310 segment1,
311 segment2,
312 segment3,
313 segment4,
314 segment5,
315 segment6,
316 segment7,
317 segment8,
318 segment9,
319 segment10,
320 segment11,
321 segment12,
322 segment13,
323 segment14,
324 segment15,
325 segment16,
326 segment17,
327 segment18,
328 segment19,
329 segment20,
330 segment21,
331 segment22,
332 segment23,
333 segment24,
334 segment25,
335 segment26,
336 segment27,
337 segment28,
338 segment29,
339 segment30,
340 amount,
341 project_id,
342 task_id,
343 award_id,
344 expenditure_organization_id,
345 cost_center
346 FROM AP_EXP_REPORT_DISTS
347 WHERE
348 report_line_id = p_source_report_line_id;
349
350
351
352 AP_WEB_UTILITIES_PKG.LogProcedure('AP_WEB_DB_EXPDIST_PKG',
353 'end DuplicateDistributions');
354
355 EXCEPTION
356 WHEN NO_DATA_FOUND THEN
357 NULL;
358 WHEN OTHERS THEN
359 AP_WEB_DB_UTIL_PKG.RaiseException('DuplicateDistributions');
360 APP_EXCEPTION.RAISE_EXCEPTION;
361 END DuplicateDistributions;
362
363 --------------------------------------------------------------------------------
364 PROCEDURE updateAccountValues(
365 p_report_header_id IN expDist_report_header_ID,
366 p_report_line_id IN expDist_REPORT_LINE_ID,
367 p_report_distribution_id IN expDist_REPORT_DISTRIBUTION_ID,
368 p_ccid IN expDist_CODE_COMBINATION_ID)
369 IS
370 --------------------------------------------------------------------------------
371 l_sequence_num NUMBER := 0;
372 l_has_dist NUMBER;
373 l_report_distribution_id NUMBER;
374
375 BEGIN
376
377 l_report_distribution_id := p_report_distribution_id;
378
379 /* If there is no distribution line, add one.
380 p_report_distribution_id is null indicates that there is no
381 distribution for the line. */
382
383 if (p_report_distribution_id is null) then
384
385 AddDistributionLine( p_report_line_id => p_report_line_id);
386
387
388 BEGIN
389 SELECT report_distribution_id
390 INTO l_report_distribution_id
391 FROM ap_exp_report_dists
392 WHERE report_header_id = p_report_header_id
393 AND report_line_id = p_report_line_id
394 AND rownum = 1;
395
396 EXCEPTION
397 WHEN NO_DATA_FOUND THEN
398 RETURN;
399 WHEN OTHERS THEN
400 AP_WEB_DB_UTIL_PKG.RaiseException('updateAccountValues');
401 APP_EXCEPTION.RAISE_EXCEPTION;
402 END;
403
404 end if;
405
406 UPDATE ap_exp_report_dists
407 SET (code_combination_id,
408 segment1,
409 segment2,
410 segment3,
411 segment4,
412 segment5,
416 segment9,
413 segment6,
414 segment7,
415 segment8,
417 segment10,
418 segment11,
419 segment12,
420 segment13,
421 segment14,
422 segment15,
423 segment16,
424 segment17,
425 segment18,
426 segment19,
427 segment20,
428 segment21,
429 segment22,
430 segment23,
431 segment24,
432 segment25,
433 segment26,
434 segment27,
435 segment28,
436 segment29,
437 segment30) = (SELECT nvl(code_combination_id,p_ccid),
438 segment1,
439 segment2,
440 segment3,
441 segment4,
442 segment5,
443 segment6,
444 segment7,
445 segment8,
446 segment9,
447 segment10,
448 segment11,
449 segment12,
450 segment13,
451 segment14,
452 segment15,
453 segment16,
454 segment17,
455 segment18,
456 segment19,
457 segment20,
458 segment21,
459 segment22,
460 segment23,
461 segment24,
462 segment25,
463 segment26,
464 segment27,
465 segment28,
466 segment29,
467 segment30
468 FROM GL_CODE_COMBINATIONS
469 WHERE code_combination_id = p_ccid)
470 WHERE report_distribution_id = l_report_distribution_id
471 AND report_header_id = p_report_header_id;
472
473 EXCEPTION
474 WHEN OTHERS THEN
475 AP_WEB_DB_UTIL_PKG.RaiseException('updateAccountValues');
476 APP_EXCEPTION.RAISE_EXCEPTION;
477 END updateAccountValues;
478
479 --------------------------------------------------------------------------------
480 PROCEDURE updateAccountValuesForForms(
481 p_report_header_id IN expDist_report_header_ID,
482 p_report_line_id IN expDist_REPORT_LINE_ID,
483 p_report_distribution_id IN expDist_REPORT_DISTRIBUTION_ID,
484 p_ccid IN expDist_CODE_COMBINATION_ID,
485 p_line_amount IN NUMBER)
486 IS
487 --------------------------------------------------------------------------------
488 l_sequence_num NUMBER := 0;
489 l_has_dist NUMBER;
490 l_report_distribution_id NUMBER;
491
492 BEGIN
493
494 l_report_distribution_id := p_report_distribution_id;
495
496 /* If there is no distribution line, add one.
497 p_report_distribution_id is null indicates that there is no
498 distribution for the line. */
499
500 if (p_report_distribution_id is null) then
501
502 AddDistributionLine( p_report_line_id => p_report_line_id);
503
504
505 BEGIN
506 SELECT report_distribution_id
507 INTO l_report_distribution_id
508 FROM ap_exp_report_dists
509 WHERE report_header_id = p_report_header_id
510 AND report_line_id = p_report_line_id
511 AND rownum = 1;
512
513 EXCEPTION
514 WHEN NO_DATA_FOUND THEN
515 RETURN;
516 WHEN OTHERS THEN
517 AP_WEB_DB_UTIL_PKG.RaiseException('updateAccountValues');
518 APP_EXCEPTION.RAISE_EXCEPTION;
519 END;
520
521 end if;
522
523 UPDATE ap_exp_report_dists
524 SET (code_combination_id,
525 segment1,
526 segment2,
527 segment3,
528 segment4,
529 segment5,
530 segment6,
531 segment7,
532 segment8,
533 segment9,
534 segment10,
535 segment11,
536 segment12,
537 segment13,
538 segment14,
539 segment15,
540 segment16,
541 segment17,
542 segment18,
543 segment19,
544 segment20,
545 segment21,
546 segment22,
547 segment23,
548 segment24,
549 segment25,
550 segment26,
551 segment27,
552 segment28,
553 segment29,
557 segment3,
554 segment30) = (SELECT nvl(code_combination_id,p_ccid),
555 segment1,
556 segment2,
558 segment4,
559 segment5,
560 segment6,
561 segment7,
562 segment8,
563 segment9,
564 segment10,
565 segment11,
566 segment12,
567 segment13,
568 segment14,
569 segment15,
570 segment16,
571 segment17,
572 segment18,
573 segment19,
574 segment20,
575 segment21,
576 segment22,
577 segment23,
578 segment24,
579 segment25,
580 segment26,
581 segment27,
582 segment28,
583 segment29,
584 segment30
585 FROM GL_CODE_COMBINATIONS
586 WHERE code_combination_id = p_ccid),
587 amount = p_line_amount
588 WHERE report_distribution_id = l_report_distribution_id
589 AND report_header_id = p_report_header_id;
590
591 EXCEPTION
592 WHEN OTHERS THEN
593 AP_WEB_DB_UTIL_PKG.RaiseException('updateAccountValuesForForms');
594 APP_EXCEPTION.RAISE_EXCEPTION;
595 END updateAccountValuesForForms;
596
597 --------------------------------------------------------------------------------
598 PROCEDURE updateDistAccountValues(
599 p_report_header_id IN expDist_report_header_ID)
600 IS
601 --------------------------------------------------------------------------------
602 CURSOR dist_lines_c IS
603 SELECT *
604 FROM AP_EXP_REPORT_DISTS_ALL
605 WHERE REPORT_HEADER_ID = p_report_header_id
606 FOR UPDATE OF REPORT_HEADER_ID NOWAIT;
607
608 dist_lines_rec dist_lines_c%ROWTYPE;
609
610 BEGIN
611
612 OPEN dist_lines_c;
613
614 LOOP
615 FETCH dist_lines_c into dist_lines_rec;
616 EXIT WHEN dist_lines_c%NOTFOUND;
617
618 IF dist_lines_rec.CODE_COMBINATION_ID IS NOT NULL THEN
619 UPDATE ap_exp_report_dists_all
620 SET (segment1,
621 segment2,
622 segment3,
623 segment4,
624 segment5,
625 segment6,
626 segment7,
627 segment8,
628 segment9,
629 segment10,
630 segment11,
631 segment12,
632 segment13,
633 segment14,
634 segment15,
635 segment16,
636 segment17,
637 segment18,
638 segment19,
639 segment20,
640 segment21,
641 segment22,
642 segment23,
643 segment24,
644 segment25,
645 segment26,
646 segment27,
647 segment28,
648 segment29,
649 segment30) = (SELECT segment1,
650 segment2,
651 segment3,
652 segment4,
653 segment5,
654 segment6,
655 segment7,
656 segment8,
657 segment9,
658 segment10,
659 segment11,
660 segment12,
661 segment13,
662 segment14,
663 segment15,
664 segment16,
665 segment17,
666 segment18,
667 segment19,
668 segment20,
669 segment21,
670 segment22,
671 segment23,
672 segment24,
673 segment25,
674 segment26,
675 segment27,
676 segment28,
677 segment29,
678 segment30
682
679 FROM GL_CODE_COMBINATIONS
680 WHERE code_combination_id = dist_lines_rec.CODE_COMBINATION_ID)
681 WHERE CURRENT OF dist_lines_c;
683 END IF;
684 END LOOP;
685
686 CLOSE dist_lines_c;
687
688 EXCEPTION
689 WHEN OTHERS THEN
690 AP_WEB_DB_UTIL_PKG.RaiseException('updateDistAccountValues');
691 APP_EXCEPTION.RAISE_EXCEPTION;
692 END updateDistAccountValues;
693
694 --------------------------------------------------------------------------------
695 PROCEDURE updateDistAcctValuesForForms(
696 p_report_header_id IN expDist_report_header_ID)
697 IS
698 --------------------------------------------------------------------------------
699 l_report_distribution_id expDist_REPORT_DISTRIBUTION_ID;
700 c_line_ccid_cursor AP_WEB_DB_EXPLINE_PKG.ExpLineCCIDCursor;
701 l_report_line_id AP_WEB_DB_EXPLINE_PKG.expLines_report_line_id;
702 l_line_ccid AP_WEB_DB_EXPLINE_PKG.expLines_codeCombID;
703 l_dist_id AP_EXP_REPORT_DISTS.REPORT_DISTRIBUTION_ID%TYPE;
704 l_line_amount NUMBER;
705 BEGIN
706
707 IF (AP_WEB_DB_EXPLINE_PKG.GetLineCCIDCursor(p_report_header_id, c_line_ccid_cursor)) THEN
708
709 LOOP
710 FETCH c_line_ccid_cursor INTO l_report_line_id, l_line_ccid, l_line_amount;
711 EXIT WHEN c_line_ccid_cursor%NOTFOUND;
712
713 BEGIN
714 /* If there is no distribution line, add one. */
715 SELECT report_distribution_id
716 INTO l_dist_id
717 FROM ap_exp_report_dists
718 WHERE report_header_id = p_report_header_id
719 AND report_line_id = l_report_line_id;
720
721 EXCEPTION
722 WHEN NO_DATA_FOUND THEN
723 l_dist_id := null;
724 WHEN OTHERS THEN
725 AP_WEB_DB_UTIL_PKG.RaiseException('updateAccountValues');
726 APP_EXCEPTION.RAISE_EXCEPTION;
727 END;
728
729 updateAccountValuesForForms(p_report_header_id, l_report_line_id, l_dist_id, l_line_ccid, l_line_amount);
730
731 END LOOP;
732 END IF;
733
734 END updateDistAcctValuesForForms;
735
736 --------------------------------------------------------------------------------
737 PROCEDURE AddDistributionLine(
738 p_report_line_id IN AP_EXPENSE_REPORT_LINES.REPORT_LINE_ID%TYPE)
739 IS
740 --------------------------------------------------------------------------------
741 l_debug_info varchar2(1000);
742 l_sequence_num NUMBER;
743
744 BEGIN
745
746 AP_WEB_UTILITIES_PKG.LogProcedure('AP_WEB_DB_EXPDIST_PKG',
747 'start AddDistributionLine');
748 l_sequence_num := 0;
749
750 -- For the given line, duplicate its distributions
751 INSERT INTO AP_EXP_REPORT_DISTS
752 (
753 report_header_id,
754 report_line_id,
755 report_distribution_id,
756 sequence_num,
757 last_update_date,
758 last_updated_by,
759 creation_date,
760 created_by,
761 code_combination_id,
762 segment1,
763 segment2,
764 segment3,
765 segment4,
766 segment5,
767 segment6,
768 segment7,
769 segment8,
770 segment9,
771 segment10,
772 segment11,
773 segment12,
774 segment13,
775 segment14,
776 segment15,
777 segment16,
778 segment17,
779 segment18,
780 segment19,
781 segment20,
782 segment21,
783 segment22,
784 segment23,
785 segment24,
786 segment25,
787 segment26,
788 segment27,
789 segment28,
790 segment29,
791 segment30,
792 org_id,
793 amount,
794 project_id,
795 task_id,
796 award_id,
797 expenditure_organization_id,
798 cost_center
799 )
800 SELECT
801 XL.report_header_id,
802 XL.report_line_id,
803 AP_EXP_REPORT_DISTS_S.NEXTVAL,
804 l_sequence_num,
805 SYSDATE,
806 XL.last_updated_by,
807 SYSDATE,
808 XL.created_by,
809 XL.code_combination_id,
810 GL.segment1,
811 GL.segment2,
812 GL.segment3,
813 GL.segment4,
814 GL.segment5,
815 GL.segment6,
816 GL.segment7,
817 GL.segment8,
818 GL.segment9,
819 GL.segment10,
820 GL.segment11,
821 GL.segment12,
822 GL.segment13,
823 GL.segment14,
824 GL.segment15,
825 GL.segment16,
826 GL.segment17,
827 GL.segment18,
828 GL.segment19,
829 GL.segment20,
830 GL.segment21,
831 GL.segment22,
832 GL.segment23,
833 GL.segment24,
834 GL.segment25,
835 GL.segment26,
836 GL.segment27,
837 GL.segment28,
838 GL.segment29,
839 GL.segment30,
840 XL.org_id,
841 XL.amount,
842 XL.project_id,
843 XL.task_id,
844 XL.award_id,
845 XL.expenditure_organization_id,
846 XH.flex_concatenated -- Bug: 6735020, flex_concatenated should be fetched from headers
847 FROM AP_EXPENSE_REPORT_LINES XL,
848 AP_EXPENSE_REPORT_HEADERS XH,
849 GL_CODE_COMBINATIONS GL
850 WHERE XL.report_line_id = p_report_line_id
851 AND XL.report_header_id = XH.report_header_id
852 AND GL.code_combination_id(+) = XL.code_combination_id;
853
854
855
856
857 AP_WEB_UTILITIES_PKG.LogProcedure('AP_WEB_DB_EXPDIST_PKG',
858 'end AddDistributionLine');
859
860
861 EXCEPTION
862 WHEN OTHERS THEN
863 AP_WEB_DB_UTIL_PKG.RaiseException('AddDistributionLine');
864 APP_EXCEPTION.RAISE_EXCEPTION;
865 END AddDistributionLine;
866
867 --------------------------------------------------------------------------------
868 PROCEDURE AddDistributionLine(
869 p_segments IN AP_OIE_KFF_SEGMENTS_T,
870 p_report_line_id IN AP_EXPENSE_REPORT_LINES.REPORT_LINE_ID%TYPE,
871 p_chart_of_accounts_id IN NUMBER)
872 IS
873 --------------------------------------------------------------------------------
874 l_debug_info varchar2(1000);
875 l_sequence_num NUMBER;
876 l_colname fnd_id_flex_segments.application_column_name%type;
877 l_sqlstmt varchar2(20000);
878 l_col_stmt varchar2(5000);
879 l_temp long;
880 l_cursor integer;
881 l_rows integer;
882 l_report_header_id AP_WEB_DB_EXPLINE_PKG.expLines_headerID;
883 l_last_updated_by NUMBER;
884 l_created_by NUMBER;
885 l_report_distribution_id expDist_REPORT_DISTRIBUTION_ID;
886 l_code_combination_id expDist_CODE_COMBINATION_ID;
887 l_org_id NUMBER;
888 l_amount ap_exp_report_dists_all.amount%TYPE;
889 l_project_id ap_exp_report_dists_all.project_id%TYPE;
890 l_task_id ap_exp_report_dists_all.task_id%TYPE;
891 l_award_id ap_exp_report_dists_all.award_id%TYPE;
892 l_expenditure_organization_id ap_exp_report_dists_all.expenditure_organization_id%TYPE;
893 l_cost_center ap_exp_report_dists_all.cost_center%TYPE;
894
895 -- BUG 7025517
896 p_seg1 varchar2(25);
897 p_seg2 varchar2(25);
898 p_seg3 varchar2(25);
899 p_seg4 varchar2(25);
900 p_seg5 varchar2(25);
901 p_seg6 varchar2(25);
902 p_seg7 varchar2(25);
903 p_seg8 varchar2(25);
904 p_seg9 varchar2(25);
905 p_seg10 varchar2(25);
906 p_seg11 varchar2(25);
907 p_seg12 varchar2(25);
908 p_seg13 varchar2(25);
909 p_seg14 varchar2(25);
910 p_seg15 varchar2(25);
911 p_seg16 varchar2(25);
912 p_seg17 varchar2(25);
913 p_seg18 varchar2(25);
914 p_seg19 varchar2(25);
915 p_seg20 varchar2(25);
916 p_seg21 varchar2(25);
917 p_seg22 varchar2(25);
918 p_seg23 varchar2(25);
919 p_seg24 varchar2(25);
920 p_seg25 varchar2(25);
921 p_seg26 varchar2(25);
922 p_seg27 varchar2(25);
923 p_seg28 varchar2(25);
924 p_seg29 varchar2(25);
925 p_seg30 varchar2(25);
926
927
928
929
930
931 BEGIN
932
933 AP_WEB_UTILITIES_PKG.LogProcedure('AP_WEB_DB_EXPDIST_PKG',
934 'start AddDistributionLine');
935
936 -----------------------------------------------------
937 l_debug_info := 'select from ap_expense_report_lines';
938 -----------------------------------------------------
939 l_sequence_num := 0;
940 SELECT XL.report_header_id,
941 XL.code_combination_id,
942 XL.last_updated_by,
943 XL.created_by,
944 XL.org_id,
945 XL.amount,
946 XL.project_id,
947 XL.task_id,
948 XL.award_id,
952 l_code_combination_id,
949 XL.expenditure_organization_id,
950 XH.flex_concatenated -- Bug 6735020, flex_concatenated should be fetched from headers
951 INTO l_report_header_id,
953 l_last_updated_by,
954 l_created_by,
955 l_org_id,
956 l_amount,
957 l_project_id,
958 l_task_id,
959 l_award_id,
960 l_expenditure_organization_id,
961 l_cost_center
962 FROM ap_expense_report_lines XL,
963 ap_expense_report_headers XH
964 WHERE XL.report_line_id = p_report_line_id
965 AND XL.report_header_id = XH.report_header_id;
966
967 -----------------------------------------------------
968 l_debug_info := 'get next AP_EXP_REPORT_DISTS_S';
969 -----------------------------------------------------
970 SELECT AP_EXP_REPORT_DISTS_S.NEXTVAL
971 INTO l_report_distribution_id
972 FROM DUAL;
973
974 l_sqlstmt := 'INSERT INTO AP_EXP_REPORT_DISTS ( ';
975 l_sqlstmt := l_sqlstmt || 'report_header_id, ';
976 l_sqlstmt := l_sqlstmt || 'report_line_id, ';
977 l_sqlstmt := l_sqlstmt || 'report_distribution_id, ';
978 l_sqlstmt := l_sqlstmt || 'sequence_num, ';
979 l_sqlstmt := l_sqlstmt || 'last_update_date, ';
980 l_sqlstmt := l_sqlstmt || 'last_updated_by, ';
981 l_sqlstmt := l_sqlstmt || 'creation_date, ';
982 l_sqlstmt := l_sqlstmt || 'created_by, ';
983 if (l_code_combination_id is not null) then
984 l_sqlstmt := l_sqlstmt || 'code_combination_id, ';
985 end if;
986 if (l_org_id is not null) then
987 l_sqlstmt := l_sqlstmt || 'org_id, ';
988 end if;
989 if (l_amount is not null) then
990 l_sqlstmt := l_sqlstmt || 'amount, ';
991 end if;
992 if (l_project_id is not null) then
993 l_sqlstmt := l_sqlstmt || 'project_id, ';
994 end if;
995 if (l_task_id is not null) then
996 l_sqlstmt := l_sqlstmt || 'task_id, ';
997 end if;
998 if (l_award_id is not null) then
999 l_sqlstmt := l_sqlstmt || 'award_id, ';
1000 end if;
1001 if (l_expenditure_organization_id is not null) then
1002 l_sqlstmt := l_sqlstmt || 'expenditure_organization_id, ';
1003 end if;
1004 if (l_cost_center is not null) then
1005 l_sqlstmt := l_sqlstmt || 'cost_center, ';
1006 end if;
1007
1008 -- Construct the segment columns in the insert clause
1009 if cflex%isopen then close cflex;
1010 end if;
1011
1012 open cflex(p_chart_of_accounts_id);
1013 LOOP
1014 FETCH cflex INTO l_colname;
1015 EXIT WHEN cflex%NOTFOUND;
1016
1017 IF l_col_stmt IS NOT NULL THEN
1018 l_col_stmt := l_col_stmt || ',' || l_colname;
1019 ELSE
1020 l_col_stmt := l_colname;
1021 END IF;
1022 END LOOP;
1023
1024 l_sqlstmt := l_sqlstmt || l_col_stmt;
1025
1026
1027 l_sqlstmt := l_sqlstmt || ') values (';
1028
1029
1030 /* BUG 7025517 -- refer to bind vars in values clause of insert statement */
1031 /* l_sqlstmt := l_sqlstmt || l_report_header_id;
1032 ** l_sqlstmt := l_sqlstmt || ', ' || p_report_line_id;
1033 ** l_sqlstmt := l_sqlstmt || ', ' || l_report_distribution_id;
1034 ** l_sqlstmt := l_sqlstmt || ', ' || l_sequence_num;
1035 ** l_sqlstmt := l_sqlstmt || ', ' || '''' || sysdate || '''';
1036 ** l_sqlstmt := l_sqlstmt || ', ' || l_last_updated_by;
1037 ** l_sqlstmt := l_sqlstmt || ', ' || '''' || sysdate || '''';
1038 ** l_sqlstmt := l_sqlstmt || ', ' || l_created_by;
1039 ** if (l_code_combination_id is not null) then
1040 ** l_sqlstmt := l_sqlstmt || ', ' || '''' || l_code_combination_id || '''';
1041 ** end if;
1042 ** if (l_org_id is not null) then
1043 ** l_sqlstmt := l_sqlstmt || ', ' || l_org_id;
1044 ** end if;
1045 ** if (l_amount is not null) then
1046 ** l_sqlstmt := l_sqlstmt || ', ' || l_amount;
1047 ** end if;
1048 ** if (l_project_id is not null) then
1049 ** l_sqlstmt := l_sqlstmt || ', ' || l_project_id;
1050 ** end if;
1051 ** if (l_task_id is not null) then
1052 ** l_sqlstmt := l_sqlstmt || ', ' || l_task_id;
1053 ** end if;
1054 ** if (l_award_id is not null) then
1055 ** l_sqlstmt := l_sqlstmt || ', ' || l_award_id;
1056 ** end if;
1057 ** if (l_expenditure_organization_id is not null) then
1058 ** l_sqlstmt := l_sqlstmt || ', ' || l_expenditure_organization_id;
1059 ** end if;
1060 ** if (l_cost_center is not null) then
1061 ** l_sqlstmt := l_sqlstmt || ', ' || '''' || l_cost_center || '''';
1062 ** end if;
1063 */
1064 /* BUG 7025517 -- refer to bind vars in values clause of insert statement */
1065 l_sqlstmt := l_sqlstmt || ' :l_report_header_id' ;
1066 l_sqlstmt := l_sqlstmt || ', :p_report_line_id' ;
1067 l_sqlstmt := l_sqlstmt || ', :l_report_distribution_id' ;
1068 l_sqlstmt := l_sqlstmt || ', :l_sequence_num' ;
1069 l_sqlstmt := l_sqlstmt || ', sysdate' ;
1070 l_sqlstmt := l_sqlstmt || ', :l_last_updated_by' ;
1071 l_sqlstmt := l_sqlstmt || ', sysdate' ;
1072 l_sqlstmt := l_sqlstmt || ', :l_created_by' ;
1073 if (l_code_combination_id is not null) then
1074 l_sqlstmt := l_sqlstmt || ', :l_code_combination_id' ;
1075 end if;
1076 if (l_org_id is not null) then
1077 l_sqlstmt := l_sqlstmt || ', :l_org_id' ;
1078 end if;
1079 if (l_amount is not null) then
1080 l_sqlstmt := l_sqlstmt || ', :l_amount' ;
1081 end if;
1082 if (l_project_id is not null) then
1083 l_sqlstmt := l_sqlstmt || ', :l_project_id' ;
1084 end if;
1085 if (l_task_id is not null) then
1086 l_sqlstmt := l_sqlstmt || ', :l_task_id' ;
1087 end if;
1088 if (l_award_id is not null) then
1089 l_sqlstmt := l_sqlstmt || ', :l_award_id' ;
1090 end if;
1094 if (l_cost_center is not null) then
1091 if (l_expenditure_organization_id is not null) then
1092 l_sqlstmt := l_sqlstmt || ', :l_expenditure_organization_id' ;
1093 end if;
1095 l_sqlstmt := l_sqlstmt || ', :l_cost_center' ;
1096 end if;
1097
1098 -- BUG 7025517
1099 p_seg1 := p_segments(1) ;
1100 p_seg2 := p_segments(2) ;
1101 p_seg3 := p_segments(3) ;
1102 p_seg4 := p_segments(4) ;
1103 p_seg5 := p_segments(5) ;
1104 p_seg6 := p_segments(6) ;
1105 p_seg7 := p_segments(7) ;
1106 p_seg8 := p_segments(8) ;
1107 p_seg9 := p_segments(9) ;
1108 p_seg10 := p_segments(10) ;
1109 p_seg11 := p_segments(11) ;
1110 p_seg12 := p_segments(12) ;
1111 p_seg13 := p_segments(13) ;
1112 p_seg14 := p_segments(14) ;
1113 p_seg15 := p_segments(15) ;
1114 p_seg16 := p_segments(16) ;
1115 p_seg17 := p_segments(17) ;
1116 p_seg18 := p_segments(18) ;
1117 p_seg19 := p_segments(19) ;
1118 p_seg20 := p_segments(20) ;
1119 p_seg21 := p_segments(21) ;
1120 p_seg22 := p_segments(22) ;
1121 p_seg23 := p_segments(23) ;
1122 p_seg24 := p_segments(24) ;
1123 p_seg25 := p_segments(25) ;
1124 p_seg26 := p_segments(26) ;
1125 p_seg27 := p_segments(27) ;
1126 p_seg28 := p_segments(28) ;
1127 p_seg29 := p_segments(29) ;
1128 p_seg30 := p_segments(30) ;
1129
1130 --FOR i IN 1..p_segments.count LOOP
1131 -- if (p_segments(i) is not null) then
1132 -- l_sqlstmt := l_sqlstmt || ',' || '''' || p_segments(i) || '''';
1133 -- end if;
1134 --END LOOP;
1135 -- ........... l_sqlstmt := l_sqlstmt || ', :l_cost_center' ;
1136
1137 if (p_segments(1) is not null) then l_sqlstmt := l_sqlstmt || ', :p_seg1' ; end if ;
1138 if (p_segments(2) is not null) then l_sqlstmt := l_sqlstmt || ', :p_seg2' ; end if ;
1139 if (p_segments(3) is not null) then l_sqlstmt := l_sqlstmt || ', :p_seg3' ; end if ;
1140 if (p_segments(4) is not null) then l_sqlstmt := l_sqlstmt || ', :p_seg4' ; end if ;
1141 if (p_segments(5) is not null) then l_sqlstmt := l_sqlstmt || ', :p_seg5' ; end if ;
1142 if (p_segments(6) is not null) then l_sqlstmt := l_sqlstmt || ', :p_seg6' ; end if ;
1143 if (p_segments(7) is not null) then l_sqlstmt := l_sqlstmt || ', :p_seg7' ; end if ;
1144 if (p_segments(8) is not null) then l_sqlstmt := l_sqlstmt || ', :p_seg8' ; end if ;
1145 if (p_segments(9) is not null) then l_sqlstmt := l_sqlstmt || ', :p_seg9' ; end if ;
1146 if (p_segments(10) is not null) then l_sqlstmt := l_sqlstmt || ', :p_seg10' ; end if ;
1147 if (p_segments(11) is not null) then l_sqlstmt := l_sqlstmt || ', :p_seg11' ; end if ;
1148 if (p_segments(12) is not null) then l_sqlstmt := l_sqlstmt || ', :p_seg12' ; end if ;
1149 if (p_segments(13) is not null) then l_sqlstmt := l_sqlstmt || ', :p_seg13' ; end if ;
1150 if (p_segments(14) is not null) then l_sqlstmt := l_sqlstmt || ', :p_seg14' ; end if ;
1151 if (p_segments(15) is not null) then l_sqlstmt := l_sqlstmt || ', :p_seg15' ; end if ;
1152 if (p_segments(16) is not null) then l_sqlstmt := l_sqlstmt || ', :p_seg16' ; end if ;
1153 if (p_segments(17) is not null) then l_sqlstmt := l_sqlstmt || ', :p_seg17' ; end if ;
1154 if (p_segments(18) is not null) then l_sqlstmt := l_sqlstmt || ', :p_seg18' ; end if ;
1155 if (p_segments(19) is not null) then l_sqlstmt := l_sqlstmt || ', :p_seg19' ; end if ;
1156 if (p_segments(20) is not null) then l_sqlstmt := l_sqlstmt || ', :p_seg20' ; end if ;
1157 if (p_segments(21) is not null) then l_sqlstmt := l_sqlstmt || ', :p_seg21' ; end if ;
1158 if (p_segments(22) is not null) then l_sqlstmt := l_sqlstmt || ', :p_seg22' ; end if ;
1159 if (p_segments(23) is not null) then l_sqlstmt := l_sqlstmt || ', :p_seg23' ; end if ;
1160 if (p_segments(24) is not null) then l_sqlstmt := l_sqlstmt || ', :p_seg24' ; end if ;
1161 if (p_segments(25) is not null) then l_sqlstmt := l_sqlstmt || ', :p_seg25' ; end if ;
1162 if (p_segments(26) is not null) then l_sqlstmt := l_sqlstmt || ', :p_seg26' ; end if ;
1163 if (p_segments(27) is not null) then l_sqlstmt := l_sqlstmt || ', :p_seg27' ; end if ;
1164 if (p_segments(28) is not null) then l_sqlstmt := l_sqlstmt || ', :p_seg28' ; end if ;
1165 if (p_segments(29) is not null) then l_sqlstmt := l_sqlstmt || ', :p_seg29' ; end if ;
1166 if (p_segments(30) is not null) then l_sqlstmt := l_sqlstmt || ', :p_seg30' ; end if ;
1167
1168
1169 l_sqlstmt := l_sqlstmt || ')';
1170
1171 -----------------------------------------------------
1172 l_debug_info := 'l_sqlstmt = '||l_sqlstmt;
1173 -----------------------------------------------------
1174 -----------------
1175 -- Now execute it
1176 -----------------
1177 l_cursor := dbms_sql.open_cursor;
1178
1179 -----------------------------------------------------
1180 l_debug_info := 'parse cursor';
1181 -----------------------------------------------------
1182 dbms_sql.parse(l_cursor, l_sqlstmt, dbms_sql.native);
1183
1184
1185 /* BUG 7025517 -- bind the input vars */
1186 dbms_sql.bind_variable(l_cursor,':l_report_header_id', l_report_header_id) ;
1187 dbms_sql.bind_variable(l_cursor,':p_report_line_id', p_report_line_id) ;
1188 dbms_sql.bind_variable(l_cursor,':l_report_distribution_id', l_report_distribution_id) ;
1189 dbms_sql.bind_variable(l_cursor,':l_sequence_num', l_sequence_num) ;
1190 dbms_sql.bind_variable(l_cursor,':l_last_updated_by', l_last_updated_by) ;
1191 dbms_sql.bind_variable(l_cursor,':l_created_by', l_created_by) ;
1192 if (l_code_combination_id is not null) then
1193 dbms_sql.bind_variable(l_cursor,':l_code_combination_id', l_code_combination_id) ;
1194 end if;
1195 if (l_org_id is not null) then
1196 dbms_sql.bind_variable(l_cursor,':l_org_id', l_org_id) ;
1197 end if;
1198 if (l_amount is not null) then
1199 dbms_sql.bind_variable(l_cursor,':l_amount', l_amount) ;
1200 end if;
1201 if (l_project_id is not null) then
1202 dbms_sql.bind_variable(l_cursor,':l_project_id', l_project_id) ;
1203 end if;
1204 if (l_task_id is not null) then
1208 dbms_sql.bind_variable(l_cursor,':l_award_id', l_award_id) ;
1205 dbms_sql.bind_variable(l_cursor,':l_task_id', l_task_id) ;
1206 end if;
1207 if (l_award_id is not null) then
1209 end if;
1210 if (l_expenditure_organization_id is not null) then
1211 dbms_sql.bind_variable(l_cursor,':l_expenditure_organization_id', l_expenditure_organization_id) ;
1212 end if;
1213 if (l_cost_center is not null) then
1214 dbms_sql.bind_variable(l_cursor,':l_cost_center', l_cost_center) ;
1215 end if;
1216
1217 if (p_seg1 is not null) then dbms_sql.bind_variable(l_cursor,':p_seg1', p_seg1) ; end if ;
1218 if (p_seg2 is not null) then dbms_sql.bind_variable(l_cursor,':p_seg2', p_seg2) ; end if ;
1219 if (p_seg3 is not null) then dbms_sql.bind_variable(l_cursor,':p_seg3', p_seg3) ; end if ;
1220 if (p_seg4 is not null) then dbms_sql.bind_variable(l_cursor,':p_seg4', p_seg4) ; end if ;
1221 if (p_seg5 is not null) then dbms_sql.bind_variable(l_cursor,':p_seg5', p_seg5) ; end if ;
1222 if (p_seg6 is not null) then dbms_sql.bind_variable(l_cursor,':p_seg6', p_seg6) ; end if ;
1223 if (p_seg7 is not null) then dbms_sql.bind_variable(l_cursor,':p_seg7', p_seg7) ; end if ;
1224 if (p_seg8 is not null) then dbms_sql.bind_variable(l_cursor,':p_seg8', p_seg8) ; end if ;
1225 if (p_seg9 is not null) then dbms_sql.bind_variable(l_cursor,':p_seg9', p_seg9) ; end if ;
1226 if (p_seg10 is not null) then dbms_sql.bind_variable(l_cursor,':p_seg10', p_seg10) ; end if ;
1227 if (p_seg11 is not null) then dbms_sql.bind_variable(l_cursor,':p_seg11', p_seg11) ; end if ;
1228 if (p_seg12 is not null) then dbms_sql.bind_variable(l_cursor,':p_seg12', p_seg12) ; end if ;
1229 if (p_seg13 is not null) then dbms_sql.bind_variable(l_cursor,':p_seg13', p_seg13) ; end if ;
1230 if (p_seg14 is not null) then dbms_sql.bind_variable(l_cursor,':p_seg14', p_seg14) ; end if ;
1231 if (p_seg15 is not null) then dbms_sql.bind_variable(l_cursor,':p_seg15', p_seg15) ; end if ;
1232 if (p_seg16 is not null) then dbms_sql.bind_variable(l_cursor,':p_seg16', p_seg16) ; end if ;
1233 if (p_seg17 is not null) then dbms_sql.bind_variable(l_cursor,':p_seg17', p_seg17) ; end if ;
1234 if (p_seg18 is not null) then dbms_sql.bind_variable(l_cursor,':p_seg18', p_seg18) ; end if ;
1235 if (p_seg19 is not null) then dbms_sql.bind_variable(l_cursor,':p_seg19', p_seg19) ; end if ;
1236 if (p_seg20 is not null) then dbms_sql.bind_variable(l_cursor,':p_seg20', p_seg20) ; end if ;
1237 if (p_seg21 is not null) then dbms_sql.bind_variable(l_cursor,':p_seg21', p_seg21) ; end if ;
1238 if (p_seg22 is not null) then dbms_sql.bind_variable(l_cursor,':p_seg22', p_seg22) ; end if ;
1239 if (p_seg23 is not null) then dbms_sql.bind_variable(l_cursor,':p_seg23', p_seg23) ; end if ;
1240 if (p_seg24 is not null) then dbms_sql.bind_variable(l_cursor,':p_seg24', p_seg24) ; end if ;
1241 if (p_seg25 is not null) then dbms_sql.bind_variable(l_cursor,':p_seg25', p_seg25) ; end if ;
1242 if (p_seg26 is not null) then dbms_sql.bind_variable(l_cursor,':p_seg26', p_seg26) ; end if ;
1243 if (p_seg27 is not null) then dbms_sql.bind_variable(l_cursor,':p_seg27', p_seg27) ; end if ;
1244 if (p_seg28 is not null) then dbms_sql.bind_variable(l_cursor,':p_seg28', p_seg28) ; end if ;
1245 if (p_seg29 is not null) then dbms_sql.bind_variable(l_cursor,':p_seg29', p_seg29) ; end if ;
1246 if (p_seg30 is not null) then dbms_sql.bind_variable(l_cursor,':p_seg30', p_seg30) ; end if ;
1247
1248
1249 /* BUG 7025517 ... End */
1250
1251
1252 -----------------------------------------------------
1253 l_debug_info := 'execute cursor';
1254 -----------------------------------------------------
1255 l_rows := dbms_sql.execute(l_cursor);
1256
1257
1258 AP_WEB_UTILITIES_PKG.LogProcedure('AP_WEB_DB_EXPDIST_PKG',
1259 'end AddDistributionLine');
1260
1261
1262 EXCEPTION
1263 WHEN OTHERS THEN
1264 IF (SQLCODE <> -20001) THEN
1265 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1266 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1267 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'AddDistributionLine');
1268 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
1269 END IF;
1270 APP_EXCEPTION.RAISE_EXCEPTION;
1271 END AddDistributionLine;
1272 --------------------------------------------------------------------------------
1273
1274
1275 --------------------------------------------------------------------------------
1276 FUNCTION ContainsProjectRelatedDist(
1277 p_report_header_id IN expDist_report_header_ID
1278 ) RETURN BOOLEAN IS
1279 --------------------------------------------------------------------------------
1280 V_Temp VARCHAR2(20);
1281 BEGIN
1282
1283 -- Check if project-related dist exists
1284 SELECT 'dist exists'
1285 INTO V_Temp
1286 FROM AP_EXP_REPORT_DISTS
1287 WHERE REPORT_HEADER_ID = p_report_header_id
1288 AND PROJECT_ID IS NOT NULL
1289 AND TASK_ID IS NOT NULL;
1290
1291 return TRUE;
1292
1293 EXCEPTION
1294 WHEN TOO_MANY_ROWS THEN
1295 return TRUE;
1296 WHEN NO_DATA_FOUND THEN
1297 return FALSE;
1298
1299 WHEN OTHERS THEN
1300 AP_WEB_DB_UTIL_PKG.RaiseException( 'ContainsProjectRelatedDist' );
1301 APP_EXCEPTION.RAISE_EXCEPTION;
1302 return FALSE;
1303 END ContainsProjectRelatedDist;
1304
1305
1306 --------------------------------------------------------------------------------
1307 FUNCTION ContainsNonProjectRelatedDist(
1308 p_report_header_id IN expDist_report_header_ID
1309 ) RETURN BOOLEAN IS
1310 --------------------------------------------------------------------------------
1311 V_Temp VARCHAR2(20);
1312 BEGIN
1313
1314 -- Check if non-project-related dist exists
1315 SELECT 'dist exists'
1316 INTO V_Temp
1317 FROM AP_EXP_REPORT_DISTS
1318 WHERE REPORT_HEADER_ID = p_report_header_id
1319 AND PROJECT_ID IS NULL
1320 AND TASK_ID IS NULL;
1321
1322 return TRUE;
1323
1324 EXCEPTION
1325 WHEN TOO_MANY_ROWS THEN
1326 return TRUE;
1327 WHEN NO_DATA_FOUND THEN
1328 return FALSE;
1329 WHEN OTHERS THEN
1330 AP_WEB_DB_UTIL_PKG.RaiseException( 'ContainsNonProjectRelatedDist' );
1331
1332 APP_EXCEPTION.RAISE_EXCEPTION;
1333 return FALSE;
1334 END ContainsNonProjectRelatedDist;
1335
1336
1337 END AP_WEB_DB_EXPDIST_PKG;