[Home] [Help]
PACKAGE BODY: APPS.ICX_NONCAT_TEMPLATES_PKG
Source
1 PACKAGE BODY ICX_NONCAT_TEMPLATES_PKG AS
2 /* $Header: ICXNTMPB.pls 120.2 2006/03/09 03:36:26 rdey noship $ */
3
4 procedure INSERT_ROW (
5 X_ROWID in out NOCOPY VARCHAR2,
6 X_TEMPLATE_ID in NUMBER,
7 X_ORG_ID in NUMBER,
8 X_TEMPLATE_NAME in VARCHAR2,
9 X_ITEM_DESCRIPTION in VARCHAR2,
10 X_CREATION_DATE in DATE,
11 X_CREATED_BY in NUMBER,
12 X_LAST_UPDATE_DATE in DATE,
13 X_LAST_UPDATED_BY in NUMBER,
14 X_LAST_UPDATE_LOGIN in NUMBER
15 ) is
16 cursor C is select ROWID from POR_NONCAT_TEMPLATES_ALL_B
17 where TEMPLATE_ID = X_TEMPLATE_ID;
18 begin
19
20 insert into POR_NONCAT_TEMPLATES_ALL_B (
21 TEMPLATE_ID,
22 ORG_ID,
23 CREATION_DATE,
24 CREATED_BY,
25 LAST_UPDATE_DATE,
26 LAST_UPDATED_BY,
27 LAST_UPDATE_LOGIN,
28 RFQ_REQUIRED_FLAG,
29 RFQ_REQ_EDITABLE_FLAG
30 ) values (
31 X_TEMPLATE_ID,
32 X_ORG_ID,
33 X_CREATION_DATE,
34 X_CREATED_BY,
35 X_LAST_UPDATE_DATE,
36 X_LAST_UPDATED_BY,
37 X_LAST_UPDATE_LOGIN,
38 'N',
39 'Y'
40 );
41
42 insert into POR_NONCAT_TEMPLATES_ALL_TL (
43 TEMPLATE_ID,
44 TEMPLATE_NAME,
45 ITEM_DESCRIPTION,
46 CREATION_DATE,
47 CREATED_BY,
48 LAST_UPDATE_DATE,
49 LAST_UPDATED_BY,
50 LAST_UPDATE_LOGIN,
51 LANGUAGE,
52 SOURCE_LANG
53 ) select
54 X_TEMPLATE_ID,
55 X_TEMPLATE_NAME,
56 X_ITEM_DESCRIPTION,
57 X_CREATION_DATE,
58 X_CREATED_BY,
59 X_LAST_UPDATE_DATE,
60 X_LAST_UPDATED_BY,
61 X_LAST_UPDATE_LOGIN,
62 L.LANGUAGE_CODE,
63 userenv('LANG')
64 from FND_LANGUAGES L
65 where L.INSTALLED_FLAG in ('I', 'B')
66 and not exists
67 (select NULL
68 from POR_NONCAT_TEMPLATES_ALL_TL T
69 where T.TEMPLATE_ID = X_TEMPLATE_ID
70 and T.LANGUAGE = L.LANGUAGE_CODE);
71
72 open c;
73 fetch c into X_ROWID;
74 if (c%notfound) then
75 close c;
76 raise no_data_found;
77 end if;
78 close c;
79
80 end INSERT_ROW;
81
82 procedure UPDATE_ROW (
83 X_TEMPLATE_ID in NUMBER,
84 X_ORG_ID in NUMBER,
85 X_TEMPLATE_NAME in VARCHAR2,
86 X_ITEM_DESCRIPTION in VARCHAR2,
87 X_LAST_UPDATE_DATE in DATE,
88 X_LAST_UPDATED_BY in NUMBER,
89 X_LAST_UPDATE_LOGIN in NUMBER
90 ) is
91 begin
92 update POR_NONCAT_TEMPLATES_ALL_B set
93 ORG_ID = X_ORG_ID,
94 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
95 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
96 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
97 where TEMPLATE_ID = X_TEMPLATE_ID;
98
99 if (sql%notfound) then
100 raise no_data_found;
101 end if;
102
103 update POR_NONCAT_TEMPLATES_ALL_TL set
104 TEMPLATE_NAME = X_TEMPLATE_NAME,
105 ITEM_DESCRIPTION = X_ITEM_DESCRIPTION,
106 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
107 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
108 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
109 SOURCE_LANG = userenv('LANG')
110 where TEMPLATE_ID = X_TEMPLATE_ID
111 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
112
113 if (sql%notfound) then
114 insert into POR_NONCAT_TEMPLATES_ALL_TL (
115 TEMPLATE_ID,
116 TEMPLATE_NAME,
117 ITEM_DESCRIPTION,
118 CREATION_DATE,
119 CREATED_BY,
120 LAST_UPDATE_DATE,
121 LAST_UPDATED_BY,
122 LAST_UPDATE_LOGIN,
123 LANGUAGE,
124 SOURCE_LANG
125 ) values (
126 X_TEMPLATE_ID,
127 X_TEMPLATE_NAME,
128 X_ITEM_DESCRIPTION,
129 X_LAST_UPDATE_DATE,
130 X_LAST_UPDATED_BY,
131 X_LAST_UPDATE_DATE,
132 X_LAST_UPDATED_BY,
133 X_LAST_UPDATE_LOGIN,
134 userenv('LANG'),
135 userenv('LANG'));
136 end if;
137 end UPDATE_ROW;
138
139 procedure TRANSLATE_ROW(
140 X_TEMPLATE_ID in VARCHAR2,
141 X_OWNER in VARCHAR2,
142 X_TEMPLATE_NAME in VARCHAR2,
143 X_ITEM_DESCRIPTION in VARCHAR2,
144 X_CUSTOM_MODE in VARCHAR2,
145 X_LAST_UPDATE_DATE in VARCHAR2) IS
146 begin
147 declare
148 f_luby number; -- entity owner in file
149 f_ludate date; -- entity update in file
150 db_luby number; -- entity owner in db
151 db_ludate date; -- entity update in db
152 begin
153 -- Translate owner to file_last_updated_by
154 f_luby := fnd_load_util.OWNER_ID(X_OWNER);
155 f_ludate := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
156
157 select LAST_UPDATED_BY, LAST_UPDATE_DATE
158 into db_luby, db_ludate
159 from POR_NONCAT_TEMPLATES_ALL_TL
160 where LANGUAGE = userenv('LANG')
161 and TEMPLATE_ID = to_number(X_TEMPLATE_ID);
162
163 -- Update record, honoring customization mode.
164 -- Record should be updated only if:
165 -- a. CUSTOM_MODE = FORCE, or
166 -- b. file owner is CUSTOM, db owner is SEED
167 -- c. owners are the same, and file_date > db_date
168 if (fnd_load_util.UPLOAD_TEST(
169 p_file_id => f_luby,
170 p_file_lud => f_ludate,
171 p_db_id => db_luby,
172 p_db_lud => db_ludate,
173 p_custom_mode => X_CUSTOM_MODE))
174 then
175 update POR_NONCAT_TEMPLATES_ALL_TL set
176 TEMPLATE_NAME = nvl(X_TEMPLATE_NAME, TEMPLATE_NAME),
177 ITEM_DESCRIPTION = nvl(X_ITEM_DESCRIPTION, ITEM_DESCRIPTION),
178 last_update_date = f_ludate,
179 last_updated_by = f_luby,
180 last_update_login = 0,
181 source_lang = userenv('LANG')
182 where TEMPLATE_ID = to_number(X_TEMPLATE_ID)
183 and userenv('LANG') in (language, source_lang);
184 end if;
185 end;
186
187 end TRANSLATE_ROW;
188
189
190 procedure LOAD_ROW(
191 X_TEMPLATE_ID in VARCHAR2,
192 X_OWNER in VARCHAR2,
193 X_ORG_ID in VARCHAR2,
194 X_TEMPLATE_NAME in VARCHAR2,
195 X_ITEM_DESCRIPTION in VARCHAR2,
196 X_CUSTOM_MODE in VARCHAR2,
197 X_LAST_UPDATE_DATE in VARCHAR2) IS
198 begin
199
200 declare
201 row_id varchar2(64);
202 f_luby number; -- entity owner in file
203 f_ludate date; -- entity update in file
204 db_luby number; -- entity owner in db
205 db_ludate date; -- entity update in db
206
207 begin
208 -- Translate owner to file_last_updated_by
209 f_luby := fnd_load_util.OWNER_ID(X_OWNER);
210 f_ludate := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
211
212 select LAST_UPDATED_BY, LAST_UPDATE_DATE
213 into db_luby, db_ludate
214 from POR_NONCAT_TEMPLATES_ALL_TL
215 where LANGUAGE = userenv('LANG')
216 and TEMPLATE_ID = to_number(X_TEMPLATE_ID);
217
218 -- Update record, honoring customization mode.
219 -- Record should be updated only if:
220 -- a. CUSTOM_MODE = FORCE, or
221 -- b. file owner is CUSTOM, db owner is SEED
222 -- c. owners are the same, and file_date > db_date
223 if (fnd_load_util.UPLOAD_TEST(
224 p_file_id => f_luby,
225 p_file_lud => f_ludate,
226 p_db_id => db_luby,
227 p_db_lud => db_ludate,
228 p_custom_mode => X_CUSTOM_MODE))
229 then
230 ICX_NONCAT_TEMPLATES_PKG.UPDATE_ROW (
231 X_TEMPLATE_ID => to_number(X_TEMPLATE_ID),
232 X_ORG_ID => to_number(X_ORG_ID),
233 X_TEMPLATE_NAME => X_TEMPLATE_NAME,
234 X_ITEM_DESCRIPTION => X_ITEM_DESCRIPTION,
235 X_LAST_UPDATE_DATE => f_ludate,
236 X_LAST_UPDATED_BY => f_luby,
237 X_LAST_UPDATE_LOGIN => 0 );
238 end if;
239 exception
240 when NO_DATA_FOUND then
241
242 ICX_NONCAT_TEMPLATES_PKG.INSERT_ROW (
243 X_ROWID => row_id,
244 X_TEMPLATE_ID => to_number(X_TEMPLATE_ID),
245 X_ORG_ID => to_number(X_ORG_ID),
246 X_TEMPLATE_NAME => X_TEMPLATE_NAME,
247 X_ITEM_DESCRIPTION => X_ITEM_DESCRIPTION,
248 X_CREATION_DATE => f_ludate,
249 X_CREATED_BY => f_luby,
250 X_LAST_UPDATE_DATE => f_ludate,
251 X_LAST_UPDATED_BY => f_luby,
252 X_LAST_UPDATE_LOGIN => 0 );
253 end;
254 end LOAD_ROW;
255
256
257 procedure ADD_LANGUAGE
258 is
259 begin
260 delete from POR_NONCAT_TEMPLATES_ALL_TL T
261 where not exists
262 (select NULL
263 from POR_NONCAT_TEMPLATES_ALL_B B
264 where B.TEMPLATE_ID = T.TEMPLATE_ID
265 );
266
267 insert into POR_NONCAT_TEMPLATES_ALL_TL (
268 LAST_UPDATE_LOGIN,
269 LAST_UPDATE_DATE,
270 LAST_UPDATED_BY,
271 CREATION_DATE,
272 CREATED_BY,
273 TEMPLATE_ID,
274 TEMPLATE_NAME,
275 ITEM_DESCRIPTION,
276 LANGUAGE,
277 SOURCE_LANG
278 ) select
279 B.LAST_UPDATE_LOGIN,
280 B.LAST_UPDATE_DATE,
281 B.LAST_UPDATED_BY,
282 B.CREATION_DATE,
283 B.CREATED_BY,
284 B.TEMPLATE_ID,
285 B.TEMPLATE_NAME,
286 B.ITEM_DESCRIPTION,
287 L.LANGUAGE_CODE,
288 B.SOURCE_LANG
289 from POR_NONCAT_TEMPLATES_ALL_TL B, FND_LANGUAGES L
290 where L.INSTALLED_FLAG in ('I', 'B')
291 and B.LANGUAGE = userenv('LANG')
292 and not exists
293 (select NULL
294 from POR_NONCAT_TEMPLATES_ALL_TL T
295 where T.TEMPLATE_ID = B.TEMPLATE_ID
296 and T.LANGUAGE = L.LANGUAGE_CODE);
297 end ADD_LANGUAGE;
298
299 end ICX_NONCAT_TEMPLATES_PKG;