[Home] [Help]
PACKAGE BODY: APPS.PON_AUC_DOCTYPES_PKG
Source
1 PACKAGE BODY pon_auc_doctypes_pkg AS
2 /* $Header: PONDOCTB.pls 120.4 2006/10/26 22:17:40 mxfang noship $ */
3
4
5
6 PROCEDURE insert_row (
7 p_doctype_id IN pon_auc_doctypes.doctype_id%TYPE
8 ,p_internal_name IN pon_auc_doctypes.internal_name%TYPE
9 ,p_scope IN pon_auc_doctypes.scope%TYPE
10 ,p_status IN pon_auc_doctypes.status%TYPE
11 ,p_transaction_type IN pon_auc_doctypes.transaction_type%TYPE
12 ,p_message_suffix IN pon_auc_doctypes.message_suffix%TYPE
13 ,p_created_by IN pon_auc_doctypes.created_by%TYPE
14 ,p_creation_date IN pon_auc_doctypes.creation_date%TYPE
15 ,p_last_updated_by IN pon_auc_doctypes.last_updated_by%TYPE
16 ,p_last_update_date IN pon_auc_doctypes.last_update_date%TYPE
17 ,p_doctype_group_name IN pon_auc_doctypes.doctype_group_name%TYPE
18 ,p_document_type_code IN pon_auc_doctypes.document_type_code%TYPE
19 ,p_document_subtype IN pon_auc_doctypes.document_subtype%TYPE
20 ,p_name IN pon_auc_doctypes_tl.name%TYPE) IS
21 BEGIN
22
23
24 INSERT INTO pon_auc_doctypes
25 ( doctype_id
26 ,internal_name
27 ,scope
28 ,status
29 ,transaction_type
30 ,message_suffix
31 ,created_by
32 ,creation_date
33 ,last_updated_by
34 ,last_update_date
35 ,doctype_group_name
36 ,document_type_code
37 ,document_subtype)
38 VALUES
39 ( p_doctype_id
40 ,p_internal_name
41 ,p_scope
42 ,p_status
43 ,p_transaction_type
44 ,p_message_suffix
45 ,p_created_by
46 ,p_creation_date
47 ,p_last_updated_by
48 ,p_last_update_date
49 ,p_doctype_group_name
50 ,p_document_type_code
51 ,p_document_subtype);
52
53
54 INSERT INTO pon_auc_doctypes_tl doctl
55 (doctype_id
56 ,name
57 ,language
58 ,created_by
59 ,creation_date
60 ,last_updated_by
61 ,last_update_date
62 ,source_lang)
63 SELECT
64 p_doctype_id
65 ,p_name
66 ,l.language_code
67 ,p_created_by
68 ,p_creation_date
69 ,p_last_updated_by
70 ,p_last_update_date
71 ,USERENV('LANG')
72 FROM
73 fnd_languages l
74 WHERE installed_flag in ('I', 'B')
75 AND NOT EXISTS
76 (SELECT NULL
77 FROM pon_auc_doctypes_tl doctl
78 WHERE doctl.doctype_id = p_doctype_id
79 AND doctl.language = l.language_code);
80
81 END insert_row;
82
83 PROCEDURE update_row (
84 p_internal_name IN pon_auc_doctypes.internal_name%TYPE,
85 p_scope IN pon_auc_doctypes.scope%TYPE,
86 p_status IN pon_auc_doctypes.status%TYPE,
87 p_transaction_type IN pon_auc_doctypes.transaction_type%TYPE,
88 p_message_suffix IN pon_auc_doctypes.message_suffix%TYPE,
89 p_last_updated_by IN pon_auc_doctypes.last_updated_by%TYPE,
90 p_last_update_date IN pon_auc_doctypes.last_update_date%TYPE,
91 p_doctype_group_name IN pon_auc_doctypes.doctype_group_name%TYPE,
92 p_document_type_code IN pon_auc_doctypes.document_type_code%TYPE,
93 p_document_subtype IN pon_auc_doctypes.document_subtype%TYPE,
94 p_name IN pon_auc_doctypes_tl.name%TYPE) IS
95
96 BEGIN
97
98 UPDATE pon_auc_doctypes doctl
99 SET scope = p_scope
100 ,status = p_status
101 ,transaction_type = p_transaction_type
102 ,message_suffix = p_message_suffix
103 ,last_updated_by = p_last_updated_by
104 ,last_update_date = p_last_update_date
105 ,doctype_group_name = p_doctype_group_name
106 ,document_type_code = p_document_type_code
107 ,document_subtype = p_document_subtype
108 WHERE doctl.internal_name = p_internal_name;
109
110 IF SQL%NOTFOUND
111 THEN
112 RAISE NO_DATA_FOUND;
113 END IF;
114
115
116 UPDATE pon_auc_doctypes_tl doctl
117 SET doctl.name = p_name
118 ,doctl.last_updated_by = p_last_updated_by
119 ,doctl.last_update_date = p_last_update_date
120 ,doctl.source_lang = userenv('LANG')
121 WHERE doctype_id = (
122 SELECT doc.doctype_id
123 FROM pon_auc_doctypes doc
124 WHERE doc.internal_name = p_internal_name)
125 AND USERENV('LANG') IN ( doctl.language, doctl.source_lang);
126
127 IF SQL%NOTFOUND
128 THEN
129 RAISE NO_DATA_FOUND;
130 END IF;
131
132 END update_row;
133
134 -- Translate_row is called during NLS translation during FNDLOAD
135
136 PROCEDURE translate_row (
137 p_internal_name IN pon_auc_doctypes.internal_name%TYPE,
138 p_name IN pon_auc_doctypes_tl.name%TYPE,
139 p_owner IN VARCHAR2,
140 p_custom_mode IN VARCHAR2,
141 p_last_update_date IN VARCHAR2
142 ) IS
143
144 f_luby number; -- entity owner in file
145 f_ludate date; -- entity update date in file
146 db_luby number; -- entity owner in db
147 db_ludate date; -- entity update date in db
148
149 BEGIN
150
151 f_luby := fnd_load_util.owner_id(p_owner);
152
153 -- Translate char last_update_date to date
154 f_ludate := nvl(to_date(p_last_update_date, 'YYYY/MM/DD'), sysdate);
155
156 select LAST_UPDATED_BY, LAST_UPDATE_DATE
157 into db_luby, db_ludate
158 from pon_auc_doctypes_tl
159 where doctype_id = (
160 SELECT doc.doctype_id
161 FROM pon_auc_doctypes doc
162 WHERE doc.internal_name = p_internal_name)
163 and userenv('LANG') = LANGUAGE;
164
165 UPDATE pon_auc_doctypes_tl doctl
166 SET doctl.name = p_name
167 ,doctl.last_updated_by = f_luby
168 ,doctl.last_update_date = f_ludate
169 ,source_lang = userenv('LANG')
170 WHERE doctype_id = (
171 SELECT doc.doctype_id
172 FROM pon_auc_doctypes doc
173 WHERE doc.internal_name = p_internal_name)
174 AND USERENV('LANG') IN ( doctl.language, doctl.source_lang);
175
176 IF SQL%NOTFOUND THEN
177 RAISE NO_DATA_FOUND;
178 END IF;
179
180 END translate_row;
181
182 -- Load_row is called during normal insertion/updates during FNDLOAD
183 -- It UPDATEs the row if available, else INSERTs
184
185 PROCEDURE load_row (
186 p_internal_name IN pon_auc_doctypes.internal_name%TYPE,
187 p_owner IN VARCHAR2,
188 p_last_update_date IN VARCHAR2,
189 p_custom_mode IN VARCHAR2, -- Custom mode can be FORCE
190 -- to force data to be uploaded
191 -- irrespective of current status
192 p_scope IN pon_auc_doctypes.scope%TYPE,
193 p_status IN pon_auc_doctypes.status%TYPE,
194 p_transaction_type IN pon_auc_doctypes.transaction_type%TYPE,
195 p_message_suffix IN pon_auc_doctypes.message_suffix%TYPE,
196 p_doctype_group_name IN pon_auc_doctypes.doctype_group_name%TYPE,
197 p_document_type_code IN pon_auc_doctypes.document_type_code%TYPE,
198 p_document_subtype IN pon_auc_doctypes.document_subtype%TYPE,
199 p_name IN pon_auc_doctypes_tl.name%TYPE) IS
200
201
202 -- Last update information from the file being uploaded
203 l_f_last_updated_by pon_auc_doctypes.last_updated_by%TYPE;
204 l_f_last_update_date pon_auc_doctypes.last_update_date%TYPE;
205
206 -- Last updated information for the row currently in the database
207 l_db_last_updated_by pon_auc_doctypes.last_updated_by%TYPE;
208 l_db_last_update_date pon_auc_doctypes.last_update_date%TYPE;
209
210 l_doctype_id pon_auc_doctypes.doctype_id%TYPE;
211
212 BEGIN
213
214 -- Translate owner to file_last_updated_by
215 l_f_last_updated_by := fnd_load_util.OWNER_ID(p_owner);
216
217 -- Translate char last_update_date to date
218 l_f_last_update_date := NVL(TO_DATE(p_last_update_date, 'YYYY/MM/DD'), SYSDATE);
219
220 SELECT last_updated_by,
221 last_update_date
222 INTO l_db_last_updated_by,
223 l_db_last_update_date
224 FROM pon_auc_doctypes doc
225 WHERE doc.internal_name = p_internal_name;
226
227 update_row (
228 p_internal_name => p_internal_name,
229 p_scope => p_scope,
230 p_status => p_status,
231 p_transaction_type => p_transaction_type,
232 p_message_suffix => p_message_suffix,
233 p_last_updated_by => l_f_last_updated_by,
234 p_last_update_date => l_f_last_update_date,
235 p_doctype_group_name => p_doctype_group_name,
236 p_document_type_code => p_document_type_code,
237 p_document_subtype => p_document_subtype,
238 p_name => p_name);
239
240 EXCEPTION
241
242 WHEN NO_DATA_FOUND
243 THEN
244
245
246 -- Need to create a new row. Get a sequence number
247
248 SELECT pon_auc_doctypes_s.NEXTVAL
249 INTO l_doctype_id
250 FROM dual;
251
252 insert_row (
253 p_doctype_id => l_doctype_id
254 ,p_internal_name => p_internal_name
255 ,p_scope => p_scope
256 ,p_status => p_status
257 ,p_transaction_type => p_transaction_type
258 ,p_message_suffix => p_message_suffix
259 ,p_created_by => l_f_last_updated_by
260 ,p_creation_date => l_f_last_update_date
261 ,p_last_updated_by => l_f_last_updated_by
262 ,p_last_update_date => l_f_last_update_date
263 ,p_doctype_group_name => p_doctype_group_name
264 ,p_document_type_code => p_document_type_code
265 ,p_document_subtype => p_document_subtype
266 ,p_name => p_name);
267
268 END load_row;
269
270 PROCEDURE delete_row (
271 p_internal_name pon_auc_doctypes.internal_name%TYPE
272 ) IS
273
274 l_doctype_id pon_auc_doctypes.doctype_id%TYPE;
275
276 BEGIN
277
278 DELETE FROM pon_auc_doctypes doc
279 WHERE doc.internal_name = p_internal_name
280 RETURNING doctype_id
281 INTO l_doctype_id;
282
283 IF SQL%NOTFOUND
284 THEN
285 RAISE NO_DATA_FOUND;
286 END IF;
287
288 DELETE FROM pon_auc_doctypes_tl doctl
289 WHERE doctl.doctype_id = l_doctype_id;
290
291 END delete_row;
292
293
294 PROCEDURE add_language IS
295
296 BEGIN
297
298 INSERT INTO PON_AUC_DOCTYPES_TL (
299 doctype_id,
300 name,
301 language,
302 created_by,
303 creation_date,
304 last_updated_by,
305 last_update_date,
306 source_lang
307 )
308 SELECT
309 doctl.doctype_id,
310 doctl.name,
311 lang.language_code,
312 doctl.created_by,
313 doctl.creation_date,
314 doctl.last_updated_by,
315 doctl.last_update_date,
316 doctl.source_lang
317 FROM pon_auc_doctypes_tl doctl,
318 fnd_languages lang
319 WHERE doctl.language = USERENV('LANG')
320 AND lang.INSTALLED_FLAG in ('I', 'B')
321 AND NOT EXISTS (SELECT NULL
322 FROM PON_AUC_DOCTYPES_TL doc2
323 WHERE doc2.doctype_id = doctl.doctype_id
324 AND doc2.language = lang.language_code);
325
326 END add_language;
327
328 END pon_auc_doctypes_pkg;