[Home] [Help]
PACKAGE BODY: APPS.PO_DOC_STYLE_LINES_PKG
Source
1 PACKAGE BODY PO_DOC_STYLE_LINES_PKG AS
2 /* $Header: PO_DOC_STYLE_LINES_PKG.plb 120.2 2006/06/21 07:29:46 scolvenk noship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'PO_DOC_STYLE_LINES_PKG';
5
6
7
8
9 PROCEDURE TRANSLATE_ROW(
10 X_STYLE_ID IN NUMBER,
11 X_DOCUMENT_SUBTYPE in VARCHAR2,
12 X_DISPLAY_NAME in VARCHAR2,
13 X_OWNER in VARCHAR2,
14 X_LAST_UPDATE_DATE in VARCHAR2,
15 X_CUSTOM_MODE in VARCHAR2)
16 IS
17
18 f_luby number; -- entity owner in file
19 f_ludate date; -- entity update date in file
20 db_luby number; -- entity owner in db
21 db_ludate date; -- entity update date in db
22
23 begin
24
25 f_luby := fnd_load_util.owner_id(X_OWNER);
26 f_ludate := nvl(to_date(X_LAST_UPDATE_DATE, 'DD/MM/YYYY'), sysdate);
27
28 select DECODE(LAST_UPDATED_BY, -1, 1, LAST_UPDATED_BY), LAST_UPDATE_DATE
29 into db_luby, db_ludate
30 from PO_DOC_STYLE_LINES_TL
31 where style_id = X_STYLE_ID
32 and document_subtype = X_DOCUMENT_SUBTYPE
33 and language = userenv('LANG') ;
34
35 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
36 db_ludate, X_CUSTOM_MODE)) then
37
38
39 update po_doc_style_lines_tl
40 set source_lang = userenv('LANG')
41 ,display_name = X_DISPLAY_NAME
42 ,last_updated_by = f_luby
43 ,last_update_login = 0
44 ,last_update_date = f_ludate
45 where style_id = X_STYLE_ID
46 and document_subtype = X_DOCUMENT_SUBTYPE
47 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
48
49 end if;
50
51 exception
52 when no_data_found then
53 -- Do not insert missing translations, skip this row
54 null;
55 end TRANSLATE_ROW;
56
57
58
59
60 procedure UPDATE_ROW(
61 X_STYLE_ID in NUMBER,
62 X_DOCUMENT_SUBTYPE in VARCHAR2,
63 X_ENABLED_FLAG in VARCHAR2,
64 X_DISPLAY_NAME in VARCHAR2,
65 X_LAST_UPDATE_DATE in DATE,
66 X_LAST_UPDATED_BY in NUMBER,
67 X_LAST_UPDATE_LOGIN in NUMBER) IS
68
69 begin
70 update po_doc_style_lines_b
71 set enabled_flag = X_ENABLED_FLAG
72 ,last_updated_by = X_LAST_UPDATED_BY
73 ,last_update_login = 0
74 ,last_update_date = X_LAST_UPDATE_DATE
75 where style_id = X_STYLE_ID
76 and document_subtype = X_DOCUMENT_SUBTYPE;
77
78 if (sql%notfound) then
79 raise no_data_found;
80 end if;
81
82 update po_doc_style_lines_tl
83 set source_lang = userenv('LANG')
84 ,display_name = X_DISPLAY_NAME
85 ,last_updated_by = X_LAST_UPDATED_BY
86 ,last_update_login = X_LAST_UPDATE_LOGIN
87 ,last_update_date = X_LAST_UPDATE_DATE
88 where style_id = X_STYLE_ID
89 and document_subtype = X_DOCUMENT_SUBTYPE
90 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
91
92 if (sql%notfound) then
93 raise no_data_found;
94 end if;
95 end UPDATE_ROW;
96
97 procedure INSERT_ROW(
98 X_STYLE_ID in NUMBER,
99 X_DOCUMENT_SUBTYPE in VARCHAR2,
100 X_ENABLED_FLAG in VARCHAR2,
101 X_DISPLAY_NAME in VARCHAR2,
102 X_LAST_UPDATE_DATE in DATE,
103 X_LAST_UPDATED_BY in NUMBER,
104 X_LAST_UPDATE_LOGIN in NUMBER) IS
105 begin
106
107
108 insert into po_doc_style_lines_b (
109 style_id
110 ,document_subtype
111 ,enabled_flag
112 ,creation_date
113 ,created_by
114 ,last_updated_by
115 ,last_update_login
116 ,last_update_date)
117 values (
118 X_STYLE_ID
119 ,X_DOCUMENT_SUBTYPE
120 ,X_ENABLED_FLAG
121 ,X_LAST_UPDATE_DATE
122 ,X_LAST_UPDATED_BY
123 ,X_LAST_UPDATED_BY
124 ,X_LAST_UPDATE_LOGIN
125 ,X_LAST_UPDATE_DATE);
126
127
128 insert into po_doc_style_lines_tl (
129 style_id
130 ,document_subtype
131 ,language
132 ,source_lang
133 ,display_name
134 ,creation_date
135 ,created_by
136 ,last_updated_by
137 ,last_update_login
138 ,last_update_date)
139 select
140 X_STYLE_ID
141 ,X_DOCUMENT_SUBTYPE
142 ,l.language_code
143 ,userenv('LANG')
144 ,X_DISPLAY_NAME
145 ,X_LAST_UPDATE_DATE
146 ,X_LAST_UPDATED_BY
147 ,X_LAST_UPDATED_BY
148 ,X_LAST_UPDATE_LOGIN
149 ,X_LAST_UPDATE_DATE
150 from fnd_languages l
151 where l.installed_flag in ('I', 'B')
152 and not exists
153 (select null
154 from po_doc_style_lines_tl t
155 where t.language = l.language_code
156 and t.style_id = X_STYLE_ID
157 and t.document_subtype =X_DOCUMENT_SUBTYPE);
158
159
160
161 end INSERT_ROW;
162
163 PROCEDURE LOAD_ROW(
164 X_STYLE_ID in NUMBER,
165 X_DOCUMENT_SUBTYPE in VARCHAR2,
166 X_ENABLED_FLAG in VARCHAR2,
167 X_DISPLAY_NAME in VARCHAR2,
168 X_OWNER in VARCHAR2,
169 X_LAST_UPDATE_DATE in DATE,
170 X_CUSTOM_MODE in VARCHAR2) IS
171
172 l_row_id varchar2(64);
173 l_category_id number;
174 f_luby number; -- entity owner in file
175 f_ludate date; -- entity update date in file
176 db_luby number; -- entity owner in db
177 db_ludate date; -- entity update date in db
178
179 begin
180
181 f_luby := fnd_load_util.owner_id(X_OWNER);
182 f_ludate := nvl(to_date(X_LAST_UPDATE_DATE, 'DD/MM/YYYY'), sysdate);
183
184 select LAST_UPDATED_BY, LAST_UPDATE_DATE
185 into db_luby, db_ludate
186 from po_doc_style_lines_tl
187 where style_id = X_STYLE_ID
188 and document_subtype =X_DOCUMENT_SUBTYPE
189 and language = userenv('LANG') ;
190
191
192 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
193 db_ludate, X_CUSTOM_MODE)) then
194
195 UPDATE_ROW(
196 X_STYLE_ID => X_STYLE_ID,
197 X_DOCUMENT_SUBTYPE => X_DOCUMENT_SUBTYPE,
198 X_ENABLED_FLAG => X_ENABLED_FLAG,
199 X_DISPLAY_NAME => X_DISPLAY_NAME,
200 X_LAST_UPDATE_DATE => f_ludate ,
201 X_LAST_UPDATED_BY => f_luby,
202 X_LAST_UPDATE_LOGIN => 0);
203 end if;
204
205 exception
206 when NO_DATA_FOUND then
207
208 INSERT_ROW(
209 X_STYLE_ID => X_STYLE_ID,
210 X_DOCUMENT_SUBTYPE => X_DOCUMENT_SUBTYPE,
211 X_ENABLED_FLAG => X_ENABLED_FLAG,
212 X_DISPLAY_NAME => X_DISPLAY_NAME,
213 X_LAST_UPDATE_DATE => f_ludate,
214 X_LAST_UPDATED_BY => f_luby,
215 X_LAST_UPDATE_LOGIN => 0);
216 end LOAD_ROW;
217
218 procedure ADD_LANGUAGE
219 IS
220
221 begin
222
223 delete from po_doc_style_lines_tl T
224 where not exists
225 (select NULL
226 from po_doc_style_lines_b B
227 where B.STYLE_ID = T.STYLE_ID
228 and B.DOCUMENT_SUBTYPE = T.DOCUMENT_SUBTYPE);
229
230 update po_doc_style_lines_tl T
231 set (
232 DISPLAY_NAME
233 ) = (select
234 DISPLAY_NAME
235 from po_doc_style_lines_tl B
236 where B.STYLE_ID = T.STYLE_ID
237 and B.DOCUMENT_SUBTYPE = T.DOCUMENT_SUBTYPE
238 and B.LANGUAGE = T.SOURCE_LANG)
239 where (
240 T.STYLE_ID,
241 T.LANGUAGE
242 ) in (select
243 SUBT.STYLE_ID,
244 SUBT.LANGUAGE
245 from po_doc_style_lines_tl SUBB,
246 po_doc_style_lines_tl SUBT
247 where SUBB.STYLE_ID = SUBT.STYLE_ID
248 and SUBB.DOCUMENT_SUBTYPE = SUBT.DOCUMENT_SUBTYPE
249 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
250 and SUBB.DISPLAY_NAME <> SUBT.DISPLAY_NAME
251 );
252 --Bug5237451: Last_update_date was not updated properly
253
254 insert into po_doc_style_lines_tl (
255 style_id
256 ,document_subtype
257 ,language
258 ,source_lang
259 ,display_name
260 ,last_update_date --Bug5237451
261 ,creation_date
262 ,created_by
263 ,last_updated_by
264 )
265 select
266 B.STYLE_ID
267 ,B.DOCUMENT_SUBTYPE
268 ,l.language_code
269 ,userenv('LANG')
270 ,B.DISPLAY_NAME
271 ,B.LAST_UPDATE_DATE
272 ,B.CREATION_DATE --Bug5237451
273 ,B.CREATED_BY
274 ,B.LAST_UPDATED_BY
275 from fnd_languages l, po_doc_style_lines_tl B
276 where l.installed_flag in ('I', 'B')
277 and B.LANGUAGE = userenv('LANG')
278 and not exists
279 (select null
280 from po_doc_style_lines_tl t
281 where t.language = l.language_code
282 and t.style_id = B.STYLE_ID
283 and t.document_subtype =B.DOCUMENT_SUBTYPE);
284
285
286 end ADD_LANGUAGE;
287
288
289 END PO_DOC_STYLE_LINES_PKG;