eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.HR_BUILD_NAME_TRG_PKG

Source


1 PACKAGE BODY HR_BUILD_NAME_TRG_PKG as
2 /* $Header: peppftrg.pkb 115.7 2004/01/08 00:03:52 njaladi ship $ */
3 PROCEDURE build_trigger IS
4   sql_proc_cursor INTEGER;
5   sql_proc_rows   INTEGER;
6   sql_str VARCHAR2(32000);
7   l_full_name VARCHAR2(30);
8   l_order_name VARCHAR2(30);
9   l_lgsl_code VARCHAR2(2);
10   l_lgsl_cd VARCHAR2(2);
11   l_pkb_name VARCHAR2(13);
12   l_param VARCHAR2(4000);    -- list of parameters for per_xx_full_name/per_xx_order_name
13   l_text_str VARCHAR2(2100);
14   l_text_inp VARCHAR2(2100);
15   l_pos INTEGER := 0;
16 
17  -- Start of fix for Bug #2459815
18  -- cursor instl_prod selects all the installed Legislations
19   CURSOR instl_prod IS
20     SELECT legislation_code
21     FROM   hr_legislation_installations
22     WHERE  application_short_name = 'PER'
23 		   AND ( (status='I' OR action IS NOT NULL)
24                          or (legislation_code = 'JP') -- Added for fix of #3290184
25                        )
26 	ORDER BY legislation_code;
27 
28   -- cursor lgsl_pkb delivers all package body names created by localization team
29   CURSOR lgsl_pkb(p_lgsl_cd VARCHAR2) IS
30     SELECT object_name
31     FROM user_objects
32     WHERE object_type='PACKAGE BODY'
33       AND object_name LIKE 'HR_'||(p_lgsl_cd)||'_UTILITY'
34       AND length(object_name)=13
35     ORDER BY object_name;
36 -- End of Fix for Bug #2459815
37 
38   -- cursor text_line returns all source code lines of the package body
39   CURSOR text_line(p_pkb_name VARCHAR2) IS
40     SELECT text
41     FROM user_source
42     WHERE name = p_pkb_name
43     and type = 'PACKAGE' --Bug# 2858437
44     ORDER BY line;
45 BEGIN
46   -- l_param variable contains a list of parameters for per_xx_full_name/
47   -- per_xx_order_name call
48   l_param := ':new.first_name,';
49   l_param := l_param || ':new.middle_names,';
50   l_param := l_param || ':new.last_name,';
51   l_param := l_param || ':new.known_as,';
52   l_param := l_param || ':new.title,';
53   l_param := l_param || ':new.suffix,';
54   l_param := l_param || ':new.pre_name_adjunct,';
55   l_param := l_param || ':new.per_information1,';
56   l_param := l_param || ':new.per_information2,';
57   l_param := l_param || ':new.per_information3,';
58   l_param := l_param || ':new.per_information4,';
59   l_param := l_param || ':new.per_information5,';
60   l_param := l_param || ':new.per_information6,';
61   l_param := l_param || ':new.per_information7,';
62   l_param := l_param || ':new.per_information8,';
63   l_param := l_param || ':new.per_information9,';
64   l_param := l_param || ':new.per_information10,';
65   l_param := l_param || ':new.per_information11,';
66   l_param := l_param || ':new.per_information12,';
67   l_param := l_param || ':new.per_information13,';
68   l_param := l_param || ':new.per_information14,';
69   l_param := l_param || ':new.per_information15,';
70   l_param := l_param || ':new.per_information16,';
71   l_param := l_param || ':new.per_information17,';
72   l_param := l_param || ':new.per_information18,';
73   l_param := l_param || ':new.per_information19,';
74   l_param := l_param || ':new.per_information20,';
75   l_param := l_param || ':new.per_information21,';
76   l_param := l_param || ':new.per_information22,';
77   l_param := l_param || ':new.per_information23,';
78   l_param := l_param || ':new.per_information24,';
79   l_param := l_param || ':new.per_information25,';
80   l_param := l_param || ':new.per_information26,';
81   l_param := l_param || ':new.per_information27,';
82   l_param := l_param || ':new.per_information28,';
83   l_param := l_param || ':new.per_information29,';
84   l_param := l_param || ':new.per_information30';
85   -- constant part of sql_str that starts source code for the trigger PER_ALL_PEOPLE_F_NAME
86   sql_str := 'CREATE OR REPLACE TRIGGER PER_ALL_PEOPLE_F_NAME ' ||
87     'BEFORE INSERT OR UPDATE ON PER_ALL_PEOPLE_F FOR EACH ROW ' ||
88     'DECLARE l_legislation_code VARCHAR2(2); BEGIN '        ||
89     'if hr_general.g_data_migrator_mode <> ''Y'' then '       ||
90     'l_legislation_code := HR_API.'         ||
91     'return_legislation_code(:new.business_group_id);';
92 
93 -- Start of fix for Bug #2459815
94 OPEN instl_prod;
95 LOOP
96   FETCH instl_prod INTO l_lgsl_cd;
97   IF instl_prod%NOTFOUND THEN
98     CLOSE instl_prod;
99     EXIT;
100   END IF;
101 
102   OPEN lgsl_pkb(l_lgsl_cd);
103 -- End of fix for Bug #2459815
104   LOOP
105     FETCH lgsl_pkb INTO l_pkb_name;
106     IF lgsl_pkb%NOTFOUND THEN
107       CLOSE lgsl_pkb;
108       EXIT;
109     ELSE
110       -- package body HR_XX_UTILITY found
111       l_lgsl_code := SUBSTR(l_pkb_name,4,2);
112       l_full_name  := 'per_' || LOWER(l_lgsl_code) || '_full_name';
113       l_order_name := 'per_' || LOWER(l_lgsl_code) || '_order_name';
114       -- check if legislation code of the current row is the same as
115       -- in the package body name HR_XX_UTILITY created by
116       -- localization team
117       sql_str := sql_str || 'IF l_legislation_code = ' ||
118         '''' || UPPER(l_lgsl_code) || '''' || ' THEN ';
119       -- get all text lines of the package body HR_XX_UTILITY
120       OPEN text_line(l_pkb_name);
121       LOOP
122         FETCH text_line INTO l_text_inp;
123         IF text_line%NOTFOUND THEN
124           CLOSE text_line;
125           EXIT;
126         ELSE
127           -- check if the current text line has per_xx_full_name call
128           l_text_str := LOWER(l_text_inp);
129           l_pos := 0;
130           l_pos := INSTR(l_text_str,l_full_name);
131           IF l_pos>0 THEN
132             -- build per_xx_full_name function call in trigger body
133             sql_str := sql_str || ':new.full_name := ' || l_pkb_name ||
134               '.' || l_full_name || '(';
135             sql_str := sql_str || l_param || ');';
136           END IF;
137           -- check if the current text line has per_xx_order_name call
138           l_pos := 0;
139           l_pos := INSTR(l_text_str,l_order_name);
140           IF l_pos>0 THEN
141             -- build per_xx_order_name function call in trigger body
142             sql_str := sql_str || ':new.order_name := ' || l_pkb_name ||
143               '.' || l_order_name || '(';
144             sql_str := sql_str || l_param || ');';
145           END IF;
146         END IF;
147       END LOOP;
148       sql_str := sql_str || 'NULL;END IF;';
149     END IF;
150   END LOOP;
151 END LOOP; -- instl_prod
152   sql_str := sql_str || 'NULL;END IF;END;';
153   -- Dynamic SQL processing of sql_str
154   sql_proc_cursor := dbms_sql.open_cursor;
155   dbms_sql.parse(sql_proc_cursor,sql_str,dbms_sql.v7);
156   sql_proc_rows := dbms_sql.execute(sql_proc_cursor);
157   IF dbms_sql.is_open(sql_proc_cursor) THEN
158     dbms_sql.close_cursor(sql_proc_cursor);
159   END IF;
160 END;
161 END HR_BUILD_NAME_TRG_PKG;