[Home] [Help]
PACKAGE BODY: APPS.AR_CMGT_DNB_TABLE_HANDLER
Source
1 Package BODY AR_CMGT_DNB_TABLE_HANDLER AS
2 /* $Header: ARCMDNTB.pls 120.0 2005/07/26 22:54:35 bsarkar noship $ */
3
4 procedure INSERT_ROW
5 ( p_data_element_name IN VARCHAR2,
6 p_scorable_flag IN VARCHAR2,
7 p_source_table_name IN VARCHAR2,
8 p_source_column_name IN VARCHAR2,
9 p_created_by IN NUMBER,
10 p_last_updated_by IN NUMBER,
11 p_last_update_login IN NUMBER,
12 p_data_element_id IN NUMBER,
13 p_application_id IN NUMBER,
14 p_return_data_type IN VARCHAR2,
15 p_return_date_format IN VARCHAR2
16 ) AS
17 BEGIN
18 INSERT INTO AR_CMGT_DNB_ELEMENTS_B
19 ( data_element_id,
20 scorable_flag,
21 source_table_name,
22 source_column_name,
23 last_updated_by,
24 last_update_date,
25 created_by,
26 creation_date,
27 last_update_login,
28 application_id,
29 return_data_type,
30 return_date_format) values
31 ( p_data_element_id,
32 p_scorable_flag,
33 p_source_table_name,
34 p_source_column_name,
35 p_last_updated_by,
36 sysdate,
37 p_created_by,
38 sysdate,
39 p_last_update_login,
40 p_application_id,
41 p_return_data_type,
42 p_return_date_format
43 );
44
45 INSERT INTO AR_CMGT_DNB_ELEMENTS_TL
46 ( data_element_id,
47 data_element_name,
48 LANGUAGE,
49 source_lang,
50 last_updated_by,
51 last_update_date,
52 created_by,
53 creation_date,
54 last_update_login) select
55 p_data_element_id,
56 p_data_element_name,
57 l.language_code,
58 userenv('LANG'),
59 p_last_updated_by,
60 sysdate,
61 p_created_by,
62 sysdate,
63 p_last_update_login
64 from fnd_languages l
65 where l.installed_flag in ('B','I')
66 and not exists (select NULL
67 from ar_cmgt_dnb_elements_TL t
68 where T.data_element_id = p_data_element_id
69 and T.LANGUAGE = L.LANGUAGE_CODE);
70
71
72 END;
73
74
75 procedure UPDATE_ROW
76 ( p_data_element_id IN NUMBER,
77 p_data_element_name IN VARCHAR2,
78 p_scorable_flag IN VARCHAR2,
79 p_source_table_name IN VARCHAR2,
80 p_source_column_name IN VARCHAR2,
81 p_last_updated_by IN NUMBER,
82 p_last_update_login IN NUMBER,
83 p_application_id IN NUMBER,
84 p_return_data_type IN VARCHAR2,
85 p_return_date_format IN VARCHAR2) AS
86
87 BEGIN
88
89 update ar_cmgt_dnb_elements_b
90 set scorable_flag = p_SCORABLE_FLAG,
91 source_table_name = p_source_table_name,
92 source_column_name = p_source_column_name,
93 last_update_date = sysdate,
94 last_updated_by = p_last_updated_by,
95 last_update_login = p_last_update_login,
96 application_id = p_application_id,
97 return_data_type = p_return_data_type,
98 return_date_format = p_return_date_format
99 where data_element_id = p_data_element_id;
100
101 if (sql%notfound) then
102 raise no_data_found;
103 end if;
104 update ar_cmgt_dnb_elements_tl
105 set data_element_name = p_data_element_name,
106 last_update_date = sysdate,
107 last_updated_by = p_last_updated_by,
108 last_update_login = p_last_update_login,
109 source_lang = userenv('LANG')
110 WHERE data_element_id = p_data_element_id
111 AND userenv('LANG') in (language, source_lang);
112
113 if sql%notfound
114 then
115 raise no_data_found;
116 end if;
117
118
119 END;
120
121 procedure DELETE_ROW (
122 p_data_element_id in NUMBER
123 ) is
124 begin
125 delete from ar_cmgt_dnb_elements_b
126 where data_element_id = p_data_element_id;
127
128 if (sql%notfound) then
129 raise no_data_found;
130 end if;
131
132 delete from ar_cmgt_dnb_elements_tl
133 where data_element_id = p_data_element_id;
134
135 if (sql%notfound) then
136 raise no_data_found;
137 end if;
138 end DELETE_ROW;
139
140 procedure ADD_LANGUAGE
141 is
142 begin
143 delete from ar_cmgt_dnb_elements_TL T
144 where not exists
145 (select NULL
146 from ar_cmgt_dnb_elements_B B
147 where B.data_element_id = T.data_element_id
148 );
149
150 update ar_cmgt_dnb_elements_TL T set (
151 data_element_name
152 ) = (select
153 B.data_element_NAME
154 from ar_cmgt_dnb_elements_TL B
155 where B.data_element_id = T.data_element_id
156 and B.LANGUAGE = T.SOURCE_LANG)
157 where (
158 T.data_element_id,
159 T.LANGUAGE
160 ) in (select
161 SUBT.data_element_id,
162 SUBT.LANGUAGE
163 from ar_cmgt_dnb_elements_TL SUBB, ar_cmgt_dnb_elements_TL SUBT
164 where SUBB.data_element_ID = SUBT.data_element_ID
165 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
166 and SUBB.data_element_name <> SUBT.data_element_name
167
168 );
169
170 INSERT INTO ar_cmgt_dnb_elements_TL
171 ( data_element_id,
172 data_element_name,
173 LANGUAGE,
174 source_lang,
175 last_updated_by,
176 last_update_date,
177 created_by,
178 creation_date,
179 last_update_login ) select
180 t.data_element_id,
181 t.data_element_name,
182 l.language_code,
183 t.source_lang,
184 t.last_updated_by,
185 t.last_update_date,
186 t.created_by,
187 t.creation_date,
188 t.last_update_login
189 FROM ar_cmgt_dnb_elements_tl t, fnd_languages l
190 WHERE l.installed_flag in ( 'I', 'B')
191 AND t.language = userenv('LANG')
192 AND not exists ( select NULL FROM
193 ar_cmgt_dnb_elements_tl t1
194 where t1.data_element_id = t.data_element_id
195 and t1.language = l.language_code);
196 end ADD_LANGUAGE;
197
198 procedure TRANSLATE_ROW (
199 P_data_element_id IN NUMBER,
200 P_data_element_name IN VARCHAR2,
201 P_OWNER IN VARCHAR2) IS
202 begin
203
204 -- only update rows that have not been altered by user
205
206 update ar_cmgt_dnb_elements_TL
207 set data_element_name = p_data_element_name,
208 source_lang = userenv('LANG'),
209 last_update_date = sysdate,
210 last_updated_by = decode(P_OWNER, 'SEED', 1, 0)
211 where data_element_id = p_data_element_id
212 and userenv('LANG') in (language, source_lang);
213
214 end TRANSLATE_ROW;
215
216 procedure LOAD_ROW
217 ( p_data_element_id IN VARCHAR2,
218 p_data_element_name IN VARCHAR2,
219 p_scorable_flag IN VARCHAR2,
220 p_source_table_name IN VARCHAR2,
221 p_source_column_name IN VARCHAR2,
222 p_created_by IN NUMBER,
223 p_last_updated_by IN NUMBER,
224 p_last_update_login IN NUMBER,
225 p_application_id IN NUMBER,
226 p_return_data_type IN VARCHAR2,
227 p_return_date_format IN VARCHAR2
228 ) AS
229
230
231 BEGIN
232 UPDATE_ROW
233 ( p_data_element_id => p_data_element_id,
234 p_data_element_name => p_data_element_name,
235 p_scorable_flag => p_scorable_flag,
236 p_source_table_name => p_source_table_name,
237 p_source_column_name => p_source_column_name,
238 p_last_updated_by => p_last_updated_by,
239 p_last_update_login => p_last_update_login,
240 p_application_id => p_application_id,
241 p_return_data_type => p_return_data_type,
242 p_return_date_format => p_return_date_format);
243
244 EXCEPTION
245 WHEN NO_DATA_FOUND THEN
246 INSERT_ROW
247 ( p_data_element_name => p_data_element_name,
248 p_scorable_flag => p_scorable_flag,
249 p_source_table_name => p_source_table_name,
250 p_source_column_name => p_source_column_name,
251 p_created_by => p_created_by,
252 p_last_updated_by => p_last_updated_by,
253 p_last_update_login => p_last_update_login,
254 p_data_element_id => p_data_element_id,
255 p_application_id => p_application_id,
256 p_return_data_type => p_return_data_type,
257 p_return_date_format => p_return_date_format);
258
259 END;
260
261 END AR_CMGT_DNB_TABLE_HANDLER;