[Home] [Help]
PACKAGE BODY: APPS.PA_DDC_PVT
Source
1 PACKAGE BODY pa_ddc_pvt AS
2 --$Header: PAXUDDCB.pls 120.1 2005/08/19 17:21:56 mwasowic noship $
3
4 -- ----------------------------------------------------
5 -- FORWARD DECLARATION
6 -- ----------------------------------------------------
7
8 FUNCTION Check_Alias (x_alias IN VARCHAR2
9 , x_folder_code IN VARCHAR2
10 ) RETURN VARCHAR2;
11
12
13 -- ----------------------------------------------------
14 -- PROCEDURES
15 -- ----------------------------------------------------
16
17
18 --
19 -- Name: Create_View_DDL
20 -- Type: PL/SQL Procedure
21 --
22 -- Description: This is the main view generation procedure for
23 -- Project Status Columns.
24 --
25 -- Note:
26 -- This package assumes that the appropriate Apps environment
27 -- globals have been instantiated before running this procedure.
28 --
29 -- !!! The AD_DDL.DO_DDL API call does an implicit COMMIT !!!
30 --
31 --
32 --
33 -- Called Subprograms: AD_DDL.DO_DDL
34 --
35 -- History:
36 -- 31-OCT-2001 jwhite Created.
37 --
38
39 PROCEDURE Create_View_DDL
40 (p_view_name IN VARCHAR2
41 , x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
42 , x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
43 , x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
44 )
45
46 IS
47
48
49 l_api_name CONSTANT VARCHAR2(30) := 'Create_View_DDL';
50
51 l_ddl_stmt VARCHAR2(7000) := NULL;
52 l_stmt VARCHAR2(5000) := NULL;
53 l_from_clause VARCHAR2(1000) := NULL;
54 l_where_clause VARCHAR2(1000) := NULL;
55
56 l_applsys VARCHAR2(80) := NULL;
57 l_status VARCHAR2(1);
58 l_industry VARCHAR2(1);
59 l_return BOOLEAN;
60
61
62 CURSOR proj_csr IS
63 SELECT format_code, column_name, currency_format_flag
64 FROM
65 pa_status_column_setup
66 WHERE folder_code = 'P'
67 order by folder_code, format_code, column_order;
68
69 CURSOR task_csr IS
70 SELECT format_code, column_name, currency_format_flag
71 FROM pa_status_column_setup
72 WHERE folder_code = 'T'
73 order by folder_code, format_code, column_order;
74
75 CURSOR rsrc_csr IS
76 SELECT format_code, column_name, currency_format_flag
77 FROM pa_status_column_setup
78 WHERE folder_code = 'R'
79 order by folder_code, format_code, column_order;
80
81
82 BEGIN
83
84
85
86 x_return_status := FND_API.G_RET_STS_SUCCESS;
87
88
89 --
90 -- Build SQL Strings by View ------------------------------------
91 --
92
93 IF (p_view_name = 'PA_STATUS_PROJ_GENERIC_V')
94 THEN
95 -- Project Status Base View ----------------------------------
96
97 -- SELECT: First Part --------------------------
98
99 l_ddl_stmt := 'CREATE OR REPLACE FORCE VIEW PA_STATUS_PROJ_GENERIC_V
100 ( PROJECT_ID
101 , VIEW_LABOR_COSTS_ALLOWED
102 , COST_BUDGET_TYPE_CODE
103 , REV_BUDGET_TYPE_CODE
104 , COLUMN1
105 , COLUMN2
106 , COLUMN3
107 , COLUMN4
108 , COLUMN5
109 , COLUMN6
110 , COLUMN7
111 , COLUMN8
112 , COLUMN9
113 , COLUMN10
114 , COLUMN11
115 , COLUMN12
116 , COLUMN13
117 , COLUMN14
118 , COLUMN15
119 , COLUMN16
120 , COLUMN17
121 , COLUMN18
122 , COLUMN19
123 , COLUMN20
124 , COLUMN21
125 , COLUMN22
126 , COLUMN23
127 , COLUMN24
128 , COLUMN25
129 , COLUMN26
130 , COLUMN27
131 , COLUMN28
132 , COLUMN29
133 , COLUMN30
134 , COLUMN31
135 , COLUMN32
136 , COLUMN33
137 ) as SELECT
138 p.project_id
139 , SUBSTR(pa_security.view_labor_costs(p.project_id),1,1)
140 , c.budget_type_code
141 , r.budget_type_code';
142
143 -- FROM_CLAUSE: ------------------------------------------
144
145 l_from_clause := 'pa_projects p,pa_project_accum_headers pah,pa_status_proj_bgt_rev_v r,pa_status_proj_bgt_cost_v c';
146
147 -- WHERE_CLAUSE: -----------------------------------------
148
149 l_where_clause := '''Y'''||' in (SELECT pa_security.allow_query(p.project_id) from sys.dual) AND p.project_id = pah.project_id AND pah.task_id = 0 AND pah.resource_list_id = 0
150 AND pah.project_id = r.project_id AND pah.project_id = c.project_id';
151
152 -- Append Basic From- and Where-Clauses as Required -------------------
153
154 -- Actuals
155 IF (Check_Alias('A.','P') = 'Y')
156 THEN
157
158 l_from_clause := l_from_clause||',pa_project_accum_actuals a';
159
160 l_where_clause := l_where_clause||' AND pah.project_accum_id = a.project_accum_id (+)';
161
162 END IF;
163
164 -- Commitments
165 IF (Check_Alias('M.','P') = 'Y')
166 THEN
167
168 l_from_clause := l_from_clause||',pa_project_accum_commitments m';
169
170 l_where_clause := l_where_clause||' AND pah.project_accum_id = m.project_accum_id (+)';
171
172 END IF;
173
174 -- SELECT: Last Part -------------------------------------
175
176 FOR proj_csrrec IN proj_csr LOOP
177 IF (proj_csrrec.format_code = 'C')
178 THEN
179 -- Character Column
180
181 IF (proj_csrrec.column_name IS NULL) THEN
182 l_stmt :=l_stmt||',null';
183 ELSE
184 l_stmt :=l_stmt||','||proj_csrrec.column_name;
185 END IF;
186 ELSE
187 -- Numeric Column
188 IF (proj_csrrec.column_name IS NULL) THEN
189 l_stmt :=l_stmt||',0';
190 ELSE
191 IF (proj_csrrec.currency_format_flag IS NULL) THEN
192 l_stmt :=l_stmt||','||proj_csrrec.column_name;
193 ELSE
194 l_stmt := l_stmt||',('||proj_csrrec.column_name||')/(PA_STATUS.Get_factor)';
195 END IF;
196 END IF;
197 END IF;
198 END LOOP;
199
200
201 ELSIF (p_view_name = 'PA_STATUS_TASK_GENERIC_V')
202 THEN
203
204 -- Task Status Base View -------------------------------------------
205
206 -- SELECT: First Part -------------------
207
208
209 l_ddl_stmt := 'CREATE OR REPLACE FORCE VIEW PA_STATUS_TASK_GENERIC_V
210 ( PROJECT_ID
211 , TASK_ID
212 , PARENT_TASK_ID
213 , WBS_LEVEL
214 , COST_BUDGET_TYPE_CODE
215 , REV_BUDGET_TYPE_CODE
216 , CHILD_EXIST_FLAG
217 , COLUMN1
218 , COLUMN2
219 , COLUMN3
220 , COLUMN4
221 , COLUMN5
222 , COLUMN6
223 , COLUMN7
224 , COLUMN8
225 , COLUMN9
226 , COLUMN10
227 , COLUMN11
228 , COLUMN12
229 , COLUMN13
230 , COLUMN14
231 , COLUMN15
232 , COLUMN16
233 , COLUMN17
234 , COLUMN18
235 , COLUMN19
236 , COLUMN20
237 , COLUMN21
238 , COLUMN22
239 , COLUMN23
240 , COLUMN24
241 , COLUMN25
242 , COLUMN26
243 , COLUMN27
244 , COLUMN28
245 , COLUMN29
246 , COLUMN30
247 , COLUMN31
248 , COLUMN32
249 , COLUMN33
250 ) as SELECT
251 t.project_id
252 , t.task_id
253 , t.parent_task_id
254 , t.wbs_level
255 , c.budget_type_code
256 , r.budget_type_code
257 , decode(pa_task_utils.check_child_exists(t.task_id),1,
258 ''+'' ,0,'' '' ) ';
259
260
261 -- FROM_CLAUSE: ----------------------
262
263 l_from_clause := 'pa_tasks t,pa_status_task_bgt_cost_high_v c,pa_status_task_bgt_rev_high_v r';
264
265 -- WHERE_CLAUSE: ----------------------
266
267 l_where_clause :='t.project_id = PA_STATUS.GetProjId AND t.task_id = c.task_id (+) AND t.task_id = r.task_id (+)';
268
269 -- Append Basic From- and Where-Clauses as Required -------------------
270
271 -- Actuals
272 IF (Check_Alias('A.','T') = 'Y') THEN
273
274 l_from_clause := l_from_clause||', pa_status_task_act_v a';
275
276 l_where_clause := l_where_clause||' AND t.task_id = a.task_id (+)';
277
278 END IF;
279
280 -- Commitments
281 IF (Check_Alias('M.','T') = 'Y') THEN
282
283 l_from_clause := l_from_clause||', pa_status_task_cmt_v m';
284
285 l_where_clause := l_where_clause||' AND t.task_id = m.task_id (+)';
286
287 END IF;
288
289 -- SELECT: Last Part -----------------------------------------
290
291 FOR task_csrrec IN task_csr LOOP
292 IF (task_csrrec.format_code = 'C')
293 THEN
294 -- Character Column
295 IF (task_csrrec.column_name IS NULL) THEN
296 l_stmt :=l_stmt||',null';
297 ELSE
298 l_stmt :=l_stmt||','||task_csrrec.column_name;
299 END IF;
300 ELSE
301 -- Numeric Column
302 IF (task_csrrec.column_name IS NULL) THEN
303 l_stmt :=l_stmt||',0';
304 ELSE
305 IF (task_csrrec.currency_format_flag IS NULL) THEN
306 l_stmt :=l_stmt||','||task_csrrec.column_name;
307 ELSE
308 l_stmt := l_stmt||',('||task_csrrec.column_name||')/(PA_STATUS.Get_factor)';
309 END IF;
310 END IF;
311 END IF;
312 END LOOP;
313
314 ELSIF (p_view_name = 'PA_STATUS_RSRC_GENERIC_V')
315 THEN
316
317
318 -- Resource Status Base View ----------------------------------
319
320
321 -- SELECT: First Part
322
323
324 l_ddl_stmt := 'CREATE OR REPLACE FORCE VIEW PA_STATUS_RSRC_GENERIC_V
325 ( PROJECT_ID
326 , RESOURCE_LIST_MEMBER_ID
327 , PARENT_MEMBER_ID
328 , MEMBER_LEVEL
329 , SORT_ORDER
330 , TASK_ID
331 , RESOURCE_LIST_ID
332 , RESOURCE_LIST_ASSIGNMENT_ID
333 , PROJECT_LEVEL_FLAG
334 , CHILD_EXIST_FLAG
335 , COLUMN1
336 , COLUMN2
337 , COLUMN3
338 , COLUMN4
339 , COLUMN5
340 , COLUMN6
341 , COLUMN7
342 , COLUMN8
343 , COLUMN9
344 , COLUMN10
345 , COLUMN11
346 , COLUMN12
347 , COLUMN13
348 , COLUMN14
349 , COLUMN15
350 , COLUMN16
351 , COLUMN17
352 , COLUMN18
353 , COLUMN19
354 , COLUMN20
355 , COLUMN21
356 , COLUMN22
357 , COLUMN23
358 , COLUMN24
359 , COLUMN25
360 , COLUMN26
361 , COLUMN27
362 , COLUMN28
363 , COLUMN29
364 , COLUMN30
365 , COLUMN31
366 , COLUMN32
367 , COLUMN33
368 ) as SELECT
369 pah.project_id
370 , pah.resource_list_member_id
371 , rlm1.parent_member_id
372 , rlm1.member_level
373 , rlm1.sort_order
374 , pah.task_id
375 , pah.resource_list_id
376 , pah.resource_list_assignment_id
377 , decode(pah.task_id, 0, ''Y'',''N'')
378 , decode(pa_get_resource.child_resource_exists(pah.resource_list_member_id,
379 pah.task_id,pah.project_id),
380 ''Y'',''+'',''N'','' '')';
381
382 -- FROM_CLAUSE: --------------------------------
383
384 l_from_clause := 'pa_status_proj_accum_headers_v pah,pa_resource_list_members rlm1';
385
386 -- WHERE_CLAUSE: ---------------------------------
387
388 l_where_clause :='pah.project_id = PA_STATUS.GetProjId AND pah.resource_list_id = PA_STATUS.GetRsrcListId AND pah.task_id = PA_STATUS.GetTaskId AND pah.resource_list_member_id = rlm1.resource_list_member_id';
389
390 -- Append Basic From- and Where-Clauses as Required -------------------
391
392 -- Resources
393 IF (Check_Alias('RES.','R') = 'Y') THEN
394
395 l_from_clause := l_from_clause||',pa_resources res';
396
397 l_where_clause := l_where_clause||' AND rlm1.resource_id = res.resource_id';
398
399 END IF;
400
401
402
403 -- Actuals
404 IF (Check_Alias('A.','R') = 'Y') THEN
405
406 l_from_clause := l_from_clause||',pa_status_rsrc_act_high_v a';
407
408 l_where_clause := l_where_clause||' AND pah.resource_list_member_id = a.resource_list_member_id (+)';
409
410 END IF;
411
412
413 -- Commitments
414 IF (Check_Alias('M.','R') = 'Y') THEN
415
416 l_from_clause := l_from_clause||',pa_status_rsrc_cmt_high_v m';
417
418 l_where_clause := l_where_clause||' AND pah.resource_list_member_id = m.resource_list_member_id (+)';
419
420 END IF;
421
422
423 -- Cost Budgets
424 IF (Check_Alias('C.','R') = 'Y') THEN
425
426 l_from_clause := l_from_clause||',pa_status_rsrc_bgt_cost_high_v c';
427
428 l_where_clause := l_where_clause||' AND pah.resource_list_member_id = c.resource_list_member_id (+)';
429
430 END IF;
431
432
433 -- Revenue Budgets
434 IF (Check_Alias('R.','R') = 'Y') THEN
435
436 l_from_clause := l_from_clause||',pa_status_rsrc_bgt_rev_high_v r';
437
438 l_where_clause := l_where_clause||' AND pah.resource_list_member_id = r.resource_list_member_id (+)';
439
440 END IF;
441
442
443 -- SELECT: Last Part --------------------------------------
444
445 FOR rsrc_csrrec IN rsrc_csr LOOP
446
447 IF (rsrc_csrrec.format_code = 'C')
448 THEN
449 -- Character Column
450
451 IF (rsrc_csrrec.column_name IS NULL) THEN
452 l_stmt :=l_stmt||',null';
456 ELSE
453 ELSE
454 l_stmt :=l_stmt||','||rsrc_csrrec.column_name;
455 END IF;
457 -- Numeric Column
458 IF (rsrc_csrrec.column_name IS NULL) THEN
459 l_stmt :=l_stmt||',0';
460 ELSE
461 IF (rsrc_csrrec.currency_format_flag IS NULL) THEN
462 l_stmt :=l_stmt||','||rsrc_csrrec.column_name;
463 ELSE
464 l_stmt := l_stmt||',('||rsrc_csrrec.column_name||')/(PA_STATUS.Get_factor)';
465 END IF;
466 END IF;
467 END IF;
468 END LOOP;
469
470
471
472 ELSIF (p_view_name = 'PA_STATUS_PROJ_LIST_V')
473 THEN
474
475
476
477 -- Project Status PROJECT LIST View ----------------------------------
478
479 -- SELECT: First Part --------------------------
480
481 l_ddl_stmt := 'CREATE OR REPLACE FORCE VIEW PA_STATUS_PROJ_LIST_V
482 ( PROJECT_ID
483 , PROJFUNC_CURRENCY_CODE
484 , COLUMN4
485 , COLUMN5
486 , COLUMN6
487 , COLUMN7
488 , COLUMN8
489 , COLUMN9
490 , COLUMN10
491 , COLUMN11
492 , COLUMN12
493 , COLUMN13
494 , COLUMN14
495 , COLUMN15
496 , COLUMN16
497 , COLUMN17
498 , COLUMN18
499 , COLUMN19
500 , COLUMN20
501 , COLUMN21
502 , COLUMN22
503 , COLUMN23
504 , COLUMN24
505 , COLUMN25
506 , COLUMN26
507 , COLUMN27
508 , COLUMN28
509 , COLUMN29
510 , COLUMN30
511 , COLUMN31
512 , COLUMN32
513 , COLUMN33
514 ) as SELECT
515 p.project_id,
516 p.projfunc_currency_code';
517
518
519
520 -- BASE FROM_CLAUSE: ------------------------------------------
521 -- Must join to pa_projects_all since myoracle portal queries ACROSS
522 -- operating units.
523
524 l_from_clause := 'pa_projects_all p,pa_project_accum_headers pah';
525
526
527
528 -- BASE WHERE_CLAUSE: -----------------------------------------
529
530 l_where_clause := ' p.project_id = pah.project_id AND pah.task_id = 0 AND pah.resource_list_member_id = 0';
531
532
533 -- Append Basic From- and Where-Clauses as Required -------------------
534
535
536
537 -- Actuals
538 IF (Check_Alias('A.','P') = 'Y')
539 THEN
540
541 l_from_clause := l_from_clause||',pa_project_accum_actuals a';
542
543 l_where_clause := l_where_clause||' AND pah.project_accum_id = a.project_accum_id';
544
545 END IF;
546
547
548 -- Commitments
549 IF (Check_Alias('M.','P') = 'Y')
550 THEN
551
552 l_from_clause := l_from_clause||',pa_project_accum_commitments m';
553
554 l_where_clause := l_where_clause||' AND pah.project_accum_id = m.project_accum_id';
555
556 END IF;
557
558
559 -- Cost Budgets
560 IF (Check_Alias('C.','P') = 'Y')
561 THEN
562
563 l_from_clause := l_from_clause||',PA_PROJECT_ACCUM_BUDGETS_V c';
564
565 l_where_clause := l_where_clause||' AND pah.project_accum_id = c.project_accum_id AND C.BUDGET_TYPE_CODE = '||'''AC''';
566
567 END IF;
568
569
570 -- Revenue Budgets
571 IF (Check_Alias('R.','P') = 'Y')
572 THEN
573
574 l_from_clause := l_from_clause||',PA_PROJECT_ACCUM_BUDGETS_V r';
575
576 l_where_clause := l_where_clause||' AND pah.project_accum_id = r.project_accum_id AND R.BUDGET_TYPE_CODE = '||'''AR''';
577
578
579 END IF;
580
581
582 -- SELECT: Last Part: ONLY Map Numeric Columns! -------------------------------------
583 -- For now, do NOT implement Factoring as this will not be used by the myportal
584 -- Project List.
585
586 FOR proj_csrrec IN proj_csr LOOP
587 IF (proj_csrrec.format_code = 'N')
588 THEN
589 -- Numeric Column
590 IF (proj_csrrec.column_name IS NULL)
591 THEN
592 l_stmt :=l_stmt||',0';
593 ELSE
594 l_stmt :=l_stmt||','||proj_csrrec.column_name;
595 END IF;
596 END IF;
597 END LOOP;
598
599
600 END IF; -- CASE for p_view_name
601
602
603 --
604 -- Generate View (p_view_name) -----------------------------------------
605 --
606
607 l_ddl_stmt := l_ddl_stmt||l_stmt;
608 l_ddl_stmt := l_ddl_stmt||' FROM '||l_from_clause;
612 -- Get Client Specific Schema Name
609 l_ddl_stmt := l_ddl_stmt||' WHERE '||l_where_clause;
610
611
613
614 l_return := FND_INSTALLATION.Get_App_Info(
615 application_short_name => 'FND'
616 , status => l_status
617 , industry => l_industry
618 , oracle_schema => l_applsys
619 );
620
621 -- Call AOL API to Generate View
622
623 AD_DDL.DO_DDL(l_applsys,'PA', 2,l_ddl_stmt, 'VIEW');
624
625
626
627
628
629
630 EXCEPTION
631
632 WHEN OTHERS THEN
633 x_msg_count := 1;
634 x_msg_data := SUBSTR(SQLERRM, 1, 240);
635 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
636 FND_MSG_PUB.add_Exc_msg(
637 p_pkg_name => G_PKG_NAME,
638 p_procedure_name => l_api_name);
639
640
641
642 END Create_View_DDL;
643
644 --
645 -- Name: Update_Ak_Item_Long_Label
646 -- Type: PL/SQL Procedure
647 --
648 -- Description: This procedure updates the AK PSI Project List column labels
649 -- with the user-defined prompts from the pa_status_column_setup
650 -- table.
651 --
652 -- This procedure is called from two places:
653 -- 1) The Project Status Column Setup form (PAXURDDC.fmb)
654 -- 2) An upgrade script
655 --
656 -- Note:
657 --
658 --
659 --
660 -- Called Subprograms: AK_REGIONS_UTIL_PKG.Update_Item_Long_Label
661 --
662 -- History:
663 -- 31-OCT-2001 jwhite Created.
664 --
665
666
667 PROCEDURE Update_Ak_Item_Long_Label
668 (x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
669 , x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
670 , x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
671 )
672
673 IS
674
675
676 l_api_name CONSTANT VARCHAR2(30) := 'Update_Ak_Item_Long_Label';
677
678 l_counter NUMBER := 0;
679 l_col_prompt_val VARCHAR2(30) :=NULL;
680 l_attribute_code VARCHAR2(30) :=NULL;
681
682
683 CURSOR prompt_csr
684 IS
685 SELECT column_prompt
686 FROM pa_status_column_setup
687 WHERE folder_code = 'P'
688 order by column_order;
689
690
691
692 BEGIN
693
694 SAVEPOINT AK_Label_Pvt;
695
696 x_return_status := FND_API.G_RET_STS_SUCCESS;
697
698 l_counter := 1;
699
700 /* Commenting the call to Update_Item_Long_Label for bug 3684384
701 OPEN prompt_csr;
702
703 LOOP
704
705 FETCH prompt_csr INTO l_col_prompt_val;
706 EXIT WHEN prompt_csr%NOTFOUND;
707
708 IF (l_counter > 3)
709 THEN
710
711 IF (l_col_prompt_val is NOT NULL)
712 THEN
713
714 l_attribute_code := 'PSI_COLUMN'||to_char(l_counter);
715
716
717
718 AK_REGIONS_UTIL_PKG.Update_Item_Long_Label
719 ( x_region_application_id => 275
720 , x_region_code => 'PA_MY_PROJECTS_RESULT_LIST'
721 , x_attribute_application_id => 275
722 , x_attribute_code => l_attribute_code
723 , x_attribute_label_long => l_col_prompt_val
724 , x_last_update_date => sysdate
725 , x_last_updated_by => G_last_updated_by
726 , x_last_update_login => G_last_update_login
727 );
728
729
730
731 END IF; -- l_col_prompt_val is NOT NULL
732
733 END IF; --(l_counter > 3)
734
735 l_counter := l_counter +1;
736
737 END LOOP;
738
739 CLOSE prompt_csr;
740 Bug 3684384 End */
741
742 EXCEPTION
743
744 WHEN OTHERS THEN
745 x_msg_count := 1;
746 x_msg_data := SUBSTR(SQLERRM, 1, 240);
747 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
748 ROLLBACK TO AK_Label_Pvt;
749 FND_MSG_PUB.add_Exc_msg(
750 p_pkg_name => G_PKG_NAME,
751 p_procedure_name => l_api_name);
752
753
754 END Update_Ak_Item_Long_Label;
755
756
757
758 -- ----------------------------------------------------
759 -- FUNCTIONS
760 -- ----------------------------------------------------
761
762 --
763 -- Name: Check_Alias
764 -- Type: Function
765 --
766 -- Description: For a given view, find if of IN-parameter
767 -- alias is used.
768 --
769 -- Note:
770 --
771 -- Called Subprograms: None.
772 --
773 -- History:
774 -- 31-OCT-2001 jwhite Created.
775 --
776
777 FUNCTION Check_Alias (x_alias IN VARCHAR2
778 , x_folder_code IN VARCHAR2
779 ) RETURN VARCHAR2
780 IS
781
782 l_found VARCHAR(1) := 'Y';
783
784 CURSOR check_csr
785 IS
786 SELECT 'Y'
787 FROM dual
788 WHERE EXISTS (select '1'
789 FROM pa_status_column_setup
790 WHERE folder_code = x_folder_code
791 AND INSTR(column_name,x_alias) > 0
792 );
793
794 BEGIN
795
796 OPEN check_csr;
797 FETCH check_csr INTO l_found;
798 IF check_csr%NOTFOUND
799 THEN
800 l_found := 'N';
801 END IF;
802 CLOSE check_csr;
803
804 RETURN l_found;
805
806 END Check_Alias;
807
808
809
810 END pa_ddc_pvt;