1 package body PO_DOCUMENT_TYPES_ALL_PKG as
2 /* $Header: POXSTDTB.pls 120.7 2008/03/25 08:40:40 lgoyal ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_DOCUMENT_TYPE_CODE in VARCHAR2,
6 X_DOCUMENT_SUBTYPE in VARCHAR2,
7 X_WF_CREATEDOC_ITEMTYPE in VARCHAR2,
8 X_ARCHIVE_EXTERNAL_REVISION_CO in VARCHAR2,
9 X_CAN_PREPARER_APPROVE_FLAG in VARCHAR2,
10 X_FORWARDING_MODE_CODE in VARCHAR2,
11 X_CAN_CHANGE_FORWARD_FROM_FLAG in VARCHAR2,
12 X_CAN_APPROVER_MODIFY_DOC_FLAG in VARCHAR2,
13 X_CAN_CHANGE_APPROVAL_PATH_FLA in VARCHAR2,
14 X_CAN_CHANGE_FORWARD_TO_FLAG in VARCHAR2,
15 X_QUOTATION_CLASS_CODE in VARCHAR2,
16 X_DEFAULT_APPROVAL_PATH_ID in NUMBER,
17 X_ATTRIBUTE_CATEGORY in VARCHAR2,
18 X_ATTRIBUTE1 in VARCHAR2,
19 X_ATTRIBUTE2 in VARCHAR2,
20 X_ATTRIBUTE3 in VARCHAR2,
21 X_ATTRIBUTE4 in VARCHAR2,
22 X_ATTRIBUTE5 in VARCHAR2,
23 X_ATTRIBUTE6 in VARCHAR2,
24 X_ATTRIBUTE7 in VARCHAR2,
25 X_ATTRIBUTE8 in VARCHAR2,
26 X_ATTRIBUTE9 in VARCHAR2,
27 X_ATTRIBUTE10 in VARCHAR2,
28 X_ATTRIBUTE11 in VARCHAR2,
29 X_ATTRIBUTE12 in VARCHAR2,
30 X_ATTRIBUTE13 in VARCHAR2,
31 X_ATTRIBUTE14 in VARCHAR2,
32 X_ATTRIBUTE15 in VARCHAR2,
33 X_SECURITY_LEVEL_CODE in VARCHAR2,
34 X_ACCESS_LEVEL_CODE in VARCHAR2,
35 X_DISABLED_FLAG in VARCHAR2,
36 X_REQUEST_ID in NUMBER,
37 X_WF_APPROVAL_ITEMTYPE in VARCHAR2,
38 X_WF_APPROVAL_PROCESS in VARCHAR2,
39 X_WF_CREATEDOC_PROCESS in VARCHAR2,
40 p_ame_transaction_type IN VARCHAR2, -- Bug 3028744 New column
41 X_TYPE_NAME in VARCHAR2,
42 X_CREATION_DATE in DATE,
43 X_CREATED_BY in NUMBER,
44 X_LAST_UPDATE_DATE in DATE,
45 X_LAST_UPDATED_BY in NUMBER,
46 X_LAST_UPDATE_LOGIN in NUMBER,
47 P_DOCUMENT_TEMPLATE_CODE in VARCHAR2, --POC FPJ
48 P_CONTRACT_TEMPLATE_CODE in VARCHAR2, --POC FPJ
49 p_use_contract_for_sourcing IN VARCHAR2,-- <Contract AutoSourcing FPJ>
50 p_include_noncatalog_flag IN VARCHAR2 , -- <Contract AutoSourcing FPJ>
51 p_org_id IN NUMBER
52 )
53 is
54 X_ORG_ID NUMBER;
55 cursor C is select ROWID from PO_DOCUMENT_TYPES_ALL_B
56 where DOCUMENT_TYPE_CODE = X_DOCUMENT_TYPE_CODE
57 and DOCUMENT_SUBTYPE = X_DOCUMENT_SUBTYPE
58 ;
59 begin
60
61 X_ORG_ID := p_org_id;
62
63 insert into PO_DOCUMENT_TYPES_ALL_B (
64 ORG_ID,
65 WF_CREATEDOC_ITEMTYPE,
66 DOCUMENT_TYPE_CODE,
67 DOCUMENT_SUBTYPE,
68 ARCHIVE_EXTERNAL_REVISION_CODE,
69 CAN_PREPARER_APPROVE_FLAG,
70 FORWARDING_MODE_CODE,
71 CAN_CHANGE_FORWARD_FROM_FLAG,
72 CAN_APPROVER_MODIFY_DOC_FLAG,
73 CAN_CHANGE_APPROVAL_PATH_FLAG,
74 CAN_CHANGE_FORWARD_TO_FLAG,
75 QUOTATION_CLASS_CODE,
76 DEFAULT_APPROVAL_PATH_ID,
77 ATTRIBUTE_CATEGORY,
78 ATTRIBUTE1,
79 ATTRIBUTE2,
80 ATTRIBUTE3,
81 ATTRIBUTE4,
82 ATTRIBUTE5,
83 ATTRIBUTE6,
84 ATTRIBUTE7,
85 ATTRIBUTE8,
86 ATTRIBUTE9,
87 ATTRIBUTE10,
88 ATTRIBUTE11,
89 ATTRIBUTE12,
90 ATTRIBUTE13,
91 ATTRIBUTE14,
92 ATTRIBUTE15,
93 SECURITY_LEVEL_CODE,
94 ACCESS_LEVEL_CODE,
95 DISABLED_FLAG,
96 REQUEST_ID,
97 WF_APPROVAL_ITEMTYPE,
98 WF_APPROVAL_PROCESS,
99 WF_CREATEDOC_PROCESS,
100 AME_TRANSACTION_TYPE, -- Bug 3028744 New column
101 CREATION_DATE,
102 CREATED_BY,
103 LAST_UPDATE_DATE,
104 LAST_UPDATED_BY,
105 LAST_UPDATE_LOGIN,
106 DOCUMENT_TEMPLATE_CODE,
107 CONTRACT_TEMPLATE_CODE,
108 USE_CONTRACT_FOR_SOURCING_FLAG, -- <Contract AutoSourcing FPJ>
109 INCLUDE_NONCATALOG_FLAG -- <Contract AutoSourcing FPJ>
110 ) values (
111 X_ORG_ID,
112 X_WF_CREATEDOC_ITEMTYPE,
113 X_DOCUMENT_TYPE_CODE,
114 X_DOCUMENT_SUBTYPE,
115 X_ARCHIVE_EXTERNAL_REVISION_CO,
116 X_CAN_PREPARER_APPROVE_FLAG,
117 X_FORWARDING_MODE_CODE,
118 X_CAN_CHANGE_FORWARD_FROM_FLAG,
119 X_CAN_APPROVER_MODIFY_DOC_FLAG,
120 X_CAN_CHANGE_APPROVAL_PATH_FLA,
121 X_CAN_CHANGE_FORWARD_TO_FLAG,
122 X_QUOTATION_CLASS_CODE,
123 X_DEFAULT_APPROVAL_PATH_ID,
124 X_ATTRIBUTE_CATEGORY,
125 X_ATTRIBUTE1,
126 X_ATTRIBUTE2,
127 X_ATTRIBUTE3,
128 X_ATTRIBUTE4,
129 X_ATTRIBUTE5,
130 X_ATTRIBUTE6,
131 X_ATTRIBUTE7,
132 X_ATTRIBUTE8,
133 X_ATTRIBUTE9,
134 X_ATTRIBUTE10,
135 X_ATTRIBUTE11,
136 X_ATTRIBUTE12,
137 X_ATTRIBUTE13,
138 X_ATTRIBUTE14,
139 X_ATTRIBUTE15,
140 X_SECURITY_LEVEL_CODE,
141 X_ACCESS_LEVEL_CODE,
142 X_DISABLED_FLAG,
143 X_REQUEST_ID,
144 X_WF_APPROVAL_ITEMTYPE,
145 X_WF_APPROVAL_PROCESS,
146 X_WF_CREATEDOC_PROCESS,
147 p_ame_transaction_type, -- Bug 3028744 New column
148 X_CREATION_DATE,
149 X_CREATED_BY,
150 X_LAST_UPDATE_DATE,
151 X_LAST_UPDATED_BY,
152 X_LAST_UPDATE_LOGIN,
153 P_DOCUMENT_TEMPLATE_CODE , -- POC FPJ
154 P_CONTRACT_TEMPLATE_CODE , -- POC FPJ
155 p_use_contract_for_sourcing, -- <Contract AutoSourcing FPJ>
156 p_include_noncatalog_flag); -- <Contract AutoSourcing FPJ>
157
158 insert into PO_DOCUMENT_TYPES_ALL_TL (
159 ORG_ID,
160 DOCUMENT_TYPE_CODE,
161 DOCUMENT_SUBTYPE,
162 TYPE_NAME,
163 LAST_UPDATE_DATE,
164 LAST_UPDATED_BY,
165 CREATED_BY,
166 CREATION_DATE,
167 LAST_UPDATE_LOGIN,
168 LANGUAGE,
169 SOURCE_LANG
170 ) select
171 X_ORG_ID,
172 X_DOCUMENT_TYPE_CODE,
173 X_DOCUMENT_SUBTYPE,
174 X_TYPE_NAME,
175 X_LAST_UPDATE_DATE,
176 X_LAST_UPDATED_BY,
177 X_CREATED_BY,
178 X_CREATION_DATE,
179 X_LAST_UPDATE_LOGIN,
180 L.LANGUAGE_CODE,
181 userenv('LANG')
182 from FND_LANGUAGES L
183 where L.INSTALLED_FLAG in ('I', 'B')
184 and not exists
185 (select NULL
186 from PO_DOCUMENT_TYPES_ALL_TL T
187 where T.DOCUMENT_TYPE_CODE = X_DOCUMENT_TYPE_CODE
188 and T.DOCUMENT_SUBTYPE = X_DOCUMENT_SUBTYPE
189 and T.LANGUAGE = L.LANGUAGE_CODE
190 and T.ORG_ID = X_ORG_ID) ; -- <R12 MOAC> added
191
192 open c;
193 fetch c into X_ROWID;
194 if (c%notfound) then
195 close c;
196 raise no_data_found;
197 end if;
198 close c;
199
200 end INSERT_ROW;
201
202 procedure LOCK_ROW (
203 X_DOCUMENT_TYPE_CODE in VARCHAR2,
204 X_DOCUMENT_SUBTYPE in VARCHAR2,
205 X_WF_CREATEDOC_ITEMTYPE in VARCHAR2,
206 X_ARCHIVE_EXTERNAL_REVISION_CO in VARCHAR2,
207 X_CAN_PREPARER_APPROVE_FLAG in VARCHAR2,
208 X_FORWARDING_MODE_CODE in VARCHAR2,
209 X_CAN_CHANGE_FORWARD_FROM_FLAG in VARCHAR2,
210 X_CAN_APPROVER_MODIFY_DOC_FLAG in VARCHAR2,
211 X_CAN_CHANGE_APPROVAL_PATH_FLA in VARCHAR2,
212 X_CAN_CHANGE_FORWARD_TO_FLAG in VARCHAR2,
213 X_QUOTATION_CLASS_CODE in VARCHAR2,
214 X_DEFAULT_APPROVAL_PATH_ID in NUMBER,
215 X_ATTRIBUTE_CATEGORY in VARCHAR2,
216 X_ATTRIBUTE1 in VARCHAR2,
217 X_ATTRIBUTE2 in VARCHAR2,
218 X_ATTRIBUTE3 in VARCHAR2,
219 X_ATTRIBUTE4 in VARCHAR2,
220 X_ATTRIBUTE5 in VARCHAR2,
221 X_ATTRIBUTE6 in VARCHAR2,
222 X_ATTRIBUTE7 in VARCHAR2,
223 X_ATTRIBUTE8 in VARCHAR2,
224 X_ATTRIBUTE9 in VARCHAR2,
225 X_ATTRIBUTE10 in VARCHAR2,
226 X_ATTRIBUTE11 in VARCHAR2,
227 X_ATTRIBUTE12 in VARCHAR2,
228 X_ATTRIBUTE13 in VARCHAR2,
229 X_ATTRIBUTE14 in VARCHAR2,
230 X_ATTRIBUTE15 in VARCHAR2,
231 X_SECURITY_LEVEL_CODE in VARCHAR2,
232 X_ACCESS_LEVEL_CODE in VARCHAR2,
233 X_DISABLED_FLAG in VARCHAR2,
234 X_REQUEST_ID in NUMBER,
235 X_WF_APPROVAL_ITEMTYPE in VARCHAR2,
236 X_WF_APPROVAL_PROCESS in VARCHAR2,
237 X_WF_CREATEDOC_PROCESS in VARCHAR2,
238 p_ame_transaction_type IN VARCHAR2, -- Bug 3028744 New column
239 X_TYPE_NAME in VARCHAR2,
240 P_DOCUMENT_TEMPLATE_CODE in VARCHAR2, --POC FPJ
241 P_CONTRACT_TEMPLATE_CODE in VARCHAR2, --POC FPJ
242 p_use_contract_for_sourcing IN VARCHAR2, -- <Contract AutoSourcing FPJ>
243 p_include_noncatalog_flag IN VARCHAR2 -- <Contract AutoSourcing FPJ>
244 )
245 is
246 X_ORG_ID NUMBER; -- <R12 MOAC>
247 cursor c is select
248 WF_CREATEDOC_ITEMTYPE,
249 ARCHIVE_EXTERNAL_REVISION_CODE,
250 CAN_PREPARER_APPROVE_FLAG,
251 FORWARDING_MODE_CODE,
252 CAN_CHANGE_FORWARD_FROM_FLAG,
253 CAN_APPROVER_MODIFY_DOC_FLAG,
254 CAN_CHANGE_APPROVAL_PATH_FLAG,
255 CAN_CHANGE_FORWARD_TO_FLAG,
256 QUOTATION_CLASS_CODE,
257 DEFAULT_APPROVAL_PATH_ID,
258 ATTRIBUTE_CATEGORY,
259 ATTRIBUTE1,
260 ATTRIBUTE2,
261 ATTRIBUTE3,
262 ATTRIBUTE4,
263 ATTRIBUTE5,
264 ATTRIBUTE6,
265 ATTRIBUTE7,
266 ATTRIBUTE8,
267 ATTRIBUTE9,
268 ATTRIBUTE10,
269 ATTRIBUTE11,
270 ATTRIBUTE12,
271 ATTRIBUTE13,
272 ATTRIBUTE14,
273 ATTRIBUTE15,
274 SECURITY_LEVEL_CODE,
275 ACCESS_LEVEL_CODE,
276 DISABLED_FLAG,
277 REQUEST_ID,
278 WF_APPROVAL_ITEMTYPE,
279 WF_APPROVAL_PROCESS,
280 WF_CREATEDOC_PROCESS,
281 AME_TRANSACTION_TYPE, -- Bug 3028744 New column
282 DOCUMENT_TEMPLATE_CODE, -- Bug # 3274065
283 CONTRACT_TEMPLATE_CODE, -- Bug # 3274065
284 USE_CONTRACT_FOR_SOURCING_FLAG, -- <Contract AutoSourcing FPJ>
285 INCLUDE_NONCATALOG_FLAG -- <Contract AutoSourcing FPJ>
286 from PO_DOCUMENT_TYPES_ALL_B
287 where DOCUMENT_TYPE_CODE = X_DOCUMENT_TYPE_CODE
288 and DOCUMENT_SUBTYPE = X_DOCUMENT_SUBTYPE
289 and org_id = X_ORG_ID -- <R12 MOAC>
290 for update of DOCUMENT_TYPE_CODE nowait;
291 recinfo c%rowtype;
292
293 cursor c1 is select
294 TYPE_NAME,
295 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
296 from PO_DOCUMENT_TYPES_ALL_TL
297 where DOCUMENT_TYPE_CODE = X_DOCUMENT_TYPE_CODE
298 and DOCUMENT_SUBTYPE = X_DOCUMENT_SUBTYPE
299 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
300 and ORG_ID = X_ORG_ID -- <R12 MOAC>
301 for update of DOCUMENT_TYPE_CODE nowait;
302 begin
303 X_ORG_ID := PO_MOAC_UTILS_PVT.get_current_org_id ; -- <R12 MOAC>
304 open c;
305 fetch c into recinfo;
306 if (c%notfound) then
307 close c;
308 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
309 app_exception.raise_exception;
310 end if;
311 close c;
312 if ( ((recinfo.WF_CREATEDOC_ITEMTYPE = X_WF_CREATEDOC_ITEMTYPE)
313 OR ((recinfo.WF_CREATEDOC_ITEMTYPE is null) AND (X_WF_CREATEDOC_ITEMTYPE is null)))
314 AND ((recinfo.ARCHIVE_EXTERNAL_REVISION_CODE = X_ARCHIVE_EXTERNAL_REVISION_CO)
315 OR ((recinfo.ARCHIVE_EXTERNAL_REVISION_CODE is null) AND (X_ARCHIVE_EXTERNAL_REVISION_CO is null)))
316 AND ((recinfo.CAN_PREPARER_APPROVE_FLAG = X_CAN_PREPARER_APPROVE_FLAG)
317 OR ((recinfo.CAN_PREPARER_APPROVE_FLAG is null) AND (X_CAN_PREPARER_APPROVE_FLAG is null)))
318
319 AND ((recinfo.FORWARDING_MODE_CODE = X_FORWARDING_MODE_CODE)
320 OR ((recinfo.FORWARDING_MODE_CODE is null) AND (X_FORWARDING_MODE_CODE is null)))
321
322 AND ((recinfo.CAN_CHANGE_FORWARD_FROM_FLAG = X_CAN_CHANGE_FORWARD_FROM_FLAG)
323 OR ((recinfo.CAN_CHANGE_FORWARD_FROM_FLAG is null) AND (X_CAN_CHANGE_FORWARD_FROM_FLAG is null)))
324 AND ((recinfo.CAN_APPROVER_MODIFY_DOC_FLAG = X_CAN_APPROVER_MODIFY_DOC_FLAG)
325 OR ((recinfo.CAN_APPROVER_MODIFY_DOC_FLAG is null) AND (X_CAN_APPROVER_MODIFY_DOC_FLAG is null)))
326 AND ((recinfo.CAN_CHANGE_APPROVAL_PATH_FLAG = X_CAN_CHANGE_APPROVAL_PATH_FLA)
327 OR ((recinfo.CAN_CHANGE_APPROVAL_PATH_FLAG is null) AND (X_CAN_CHANGE_APPROVAL_PATH_FLA is null)))
328 AND ((recinfo.CAN_CHANGE_FORWARD_TO_FLAG = X_CAN_CHANGE_FORWARD_TO_FLAG)
329 OR ((recinfo.CAN_CHANGE_FORWARD_TO_FLAG is null) AND (X_CAN_CHANGE_FORWARD_TO_FLAG is null)))
330 AND ((recinfo.QUOTATION_CLASS_CODE = X_QUOTATION_CLASS_CODE)
331 OR ((recinfo.QUOTATION_CLASS_CODE is null) AND (X_QUOTATION_CLASS_CODE is null)))
332 AND ((recinfo.DEFAULT_APPROVAL_PATH_ID = X_DEFAULT_APPROVAL_PATH_ID)
333 OR ((recinfo.DEFAULT_APPROVAL_PATH_ID is null) AND (X_DEFAULT_APPROVAL_PATH_ID is null)))
334 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
335 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
336 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
337 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
338 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
339 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
340 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
341 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
342 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
343 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
344 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
345 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
346 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
347 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
348 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
349 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
350 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
351 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
352 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
353 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
354 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
355 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
356 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
357 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
358 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
359 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
360 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
361 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
362 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
363 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
364 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
365 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
366 AND ((recinfo.SECURITY_LEVEL_CODE = X_SECURITY_LEVEL_CODE)
367 OR ((recinfo.SECURITY_LEVEL_CODE is null) AND (X_SECURITY_LEVEL_CODE is null)))
368 AND ((recinfo.ACCESS_LEVEL_CODE = X_ACCESS_LEVEL_CODE)
369 OR ((recinfo.ACCESS_LEVEL_CODE is null) AND (X_ACCESS_LEVEL_CODE is null)))
370 AND (recinfo.DISABLED_FLAG = X_DISABLED_FLAG)
371 AND ((recinfo.REQUEST_ID = X_REQUEST_ID)
372 OR ((recinfo.REQUEST_ID is null) AND (X_REQUEST_ID is null)))
373 AND ((recinfo.WF_APPROVAL_ITEMTYPE = X_WF_APPROVAL_ITEMTYPE)
374 OR ((recinfo.WF_APPROVAL_ITEMTYPE is null) AND (X_WF_APPROVAL_ITEMTYPE is null)))
375 AND ((recinfo.WF_APPROVAL_PROCESS = X_WF_APPROVAL_PROCESS)
376 OR ((recinfo.WF_APPROVAL_PROCESS is null) AND (X_WF_APPROVAL_PROCESS is null)))
377 AND ((recinfo.WF_CREATEDOC_PROCESS = X_WF_CREATEDOC_PROCESS)
378 OR ((recinfo.WF_CREATEDOC_PROCESS is null) AND (X_WF_CREATEDOC_PROCESS is null)))
379 -- Bug 3028744 START - New column
380 AND ((recinfo.ame_transaction_type = p_ame_transaction_type)
381 OR ((recinfo.ame_transaction_type IS NULL) AND (p_ame_transaction_type IS NULL)))
382 -- Bug 3028744 END
383 -- Bug 3274065 START - New columns
384 AND ((recinfo.DOCUMENT_TEMPLATE_CODE = P_DOCUMENT_TEMPLATE_CODE)
385 OR ((recinfo.DOCUMENT_TEMPLATE_CODE IS NULL) AND (P_DOCUMENT_TEMPLATE_CODE IS NULL)))
386 AND ((recinfo.CONTRACT_TEMPLATE_CODE = P_CONTRACT_TEMPLATE_CODE)
387 OR ((recinfo.CONTRACT_TEMPLATE_CODE IS NULL) AND (P_CONTRACT_TEMPLATE_CODE IS NULL)))
388 -- Bug 3274065 End - New columns
389 -- <Contract AutoSourcing FPJ Start>
393 AND ((recinfo.INCLUDE_NONCATALOG_FLAG = P_INCLUDE_NONCATALOG_FLAG)
390 AND ((recinfo.USE_CONTRACT_FOR_SOURCING_FLAG = P_USE_CONTRACT_FOR_SOURCING)
391 OR ((recinfo.USE_CONTRACT_FOR_SOURCING_FLAG IS NULL)
392 AND (P_USE_CONTRACT_FOR_SOURCING IS NULL)))
394 OR ((recinfo.INCLUDE_NONCATALOG_FLAG IS NULL)
395 AND (P_INCLUDE_NONCATALOG_FLAG IS NULL)))
396 -- <Contract AutoSourcing FPJ End>
397 ) then
398 null;
399 else
400 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
401 app_exception.raise_exception;
402 end if;
403
404 for tlinfo in c1 loop
405 if (tlinfo.BASELANG = 'Y') then
406 if ( (tlinfo.TYPE_NAME = X_TYPE_NAME)
407 ) then
408 null;
409 else
410 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
411 app_exception.raise_exception;
412 end if;
413 end if;
414 end loop;
415
416 return;
417 end LOCK_ROW;
418
419 procedure UPDATE_ROW (
420 X_DOCUMENT_TYPE_CODE in VARCHAR2,
421 X_DOCUMENT_SUBTYPE in VARCHAR2,
422 X_WF_CREATEDOC_ITEMTYPE in VARCHAR2,
423 X_ARCHIVE_EXTERNAL_REVISION_CO in VARCHAR2,
424 X_CAN_PREPARER_APPROVE_FLAG in VARCHAR2,
425 X_FORWARDING_MODE_CODE in VARCHAR2,
426 X_CAN_CHANGE_FORWARD_FROM_FLAG in VARCHAR2,
427 X_CAN_APPROVER_MODIFY_DOC_FLAG in VARCHAR2,
428 X_CAN_CHANGE_APPROVAL_PATH_FLA in VARCHAR2,
429 X_CAN_CHANGE_FORWARD_TO_FLAG in VARCHAR2,
430 X_QUOTATION_CLASS_CODE in VARCHAR2,
431 X_DEFAULT_APPROVAL_PATH_ID in NUMBER,
432 X_ATTRIBUTE_CATEGORY in VARCHAR2,
433 X_ATTRIBUTE1 in VARCHAR2,
434 X_ATTRIBUTE2 in VARCHAR2,
435 X_ATTRIBUTE3 in VARCHAR2,
436 X_ATTRIBUTE4 in VARCHAR2,
437 X_ATTRIBUTE5 in VARCHAR2,
438 X_ATTRIBUTE6 in VARCHAR2,
439 X_ATTRIBUTE7 in VARCHAR2,
440 X_ATTRIBUTE8 in VARCHAR2,
441 X_ATTRIBUTE9 in VARCHAR2,
442 X_ATTRIBUTE10 in VARCHAR2,
443 X_ATTRIBUTE11 in VARCHAR2,
444 X_ATTRIBUTE12 in VARCHAR2,
445 X_ATTRIBUTE13 in VARCHAR2,
446 X_ATTRIBUTE14 in VARCHAR2,
447 X_ATTRIBUTE15 in VARCHAR2,
448 X_SECURITY_LEVEL_CODE in VARCHAR2,
449 X_ACCESS_LEVEL_CODE in VARCHAR2,
450 X_DISABLED_FLAG in VARCHAR2,
451 X_REQUEST_ID in NUMBER,
452 X_WF_APPROVAL_ITEMTYPE in VARCHAR2,
453 X_WF_APPROVAL_PROCESS in VARCHAR2,
454 X_WF_CREATEDOC_PROCESS in VARCHAR2,
455 p_ame_transaction_type IN VARCHAR2, -- Bug 3028744 New column
456 X_TYPE_NAME in VARCHAR2,
457 X_LAST_UPDATE_DATE in DATE,
458 X_LAST_UPDATED_BY in NUMBER,
459 X_LAST_UPDATE_LOGIN in NUMBER,
460 P_DOCUMENT_TEMPLATE_CODE in VARCHAR2, --POC FPJ
461 P_CONTRACT_TEMPLATE_CODE in VARCHAR2, --POC FPJ
462 p_use_contract_for_sourcing IN VARCHAR2,-- <Contract AutoSourcing FPJ>
463 p_include_noncatalog_flag IN VARCHAR2, -- <Contract AutoSourcing FPJ>
464 p_org_id IN NUMBER
465 )
466 is
467 X_ORG_ID NUMBER; -- <R12 MOAC>
468 begin
469 -- Bug 5081289: Passed org id instead of using session context
470 X_ORG_ID := p_org_id;
471
472 update PO_DOCUMENT_TYPES_ALL_B set
473 WF_CREATEDOC_ITEMTYPE = X_WF_CREATEDOC_ITEMTYPE,
474 ARCHIVE_EXTERNAL_REVISION_CODE = X_ARCHIVE_EXTERNAL_REVISION_CO,
475 CAN_PREPARER_APPROVE_FLAG = X_CAN_PREPARER_APPROVE_FLAG,
476 FORWARDING_MODE_CODE = X_FORWARDING_MODE_CODE,
477 CAN_CHANGE_FORWARD_FROM_FLAG = X_CAN_CHANGE_FORWARD_FROM_FLAG,
478 CAN_APPROVER_MODIFY_DOC_FLAG = X_CAN_APPROVER_MODIFY_DOC_FLAG,
479 CAN_CHANGE_APPROVAL_PATH_FLAG = X_CAN_CHANGE_APPROVAL_PATH_FLA,
480 CAN_CHANGE_FORWARD_TO_FLAG = X_CAN_CHANGE_FORWARD_TO_FLAG,
481 QUOTATION_CLASS_CODE = X_QUOTATION_CLASS_CODE,
482 DEFAULT_APPROVAL_PATH_ID = X_DEFAULT_APPROVAL_PATH_ID,
483 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
484 ATTRIBUTE1 = X_ATTRIBUTE1,
485 ATTRIBUTE2 = X_ATTRIBUTE2,
486 ATTRIBUTE3 = X_ATTRIBUTE3,
487 ATTRIBUTE4 = X_ATTRIBUTE4,
488 ATTRIBUTE5 = X_ATTRIBUTE5,
489 ATTRIBUTE6 = X_ATTRIBUTE6,
490 ATTRIBUTE7 = X_ATTRIBUTE7,
491 ATTRIBUTE8 = X_ATTRIBUTE8,
492 ATTRIBUTE9 = X_ATTRIBUTE9,
493 ATTRIBUTE10 = X_ATTRIBUTE10,
494 ATTRIBUTE11 = X_ATTRIBUTE11,
495 ATTRIBUTE12 = X_ATTRIBUTE12,
496 ATTRIBUTE13 = X_ATTRIBUTE13,
497 ATTRIBUTE14 = X_ATTRIBUTE14,
498 ATTRIBUTE15 = X_ATTRIBUTE15,
499 SECURITY_LEVEL_CODE = X_SECURITY_LEVEL_CODE,
500 ACCESS_LEVEL_CODE = X_ACCESS_LEVEL_CODE,
501 DISABLED_FLAG = X_DISABLED_FLAG,
502 REQUEST_ID = X_REQUEST_ID,
503 WF_APPROVAL_ITEMTYPE = X_WF_APPROVAL_ITEMTYPE,
504 WF_APPROVAL_PROCESS = X_WF_APPROVAL_PROCESS,
505 WF_CREATEDOC_PROCESS = X_WF_CREATEDOC_PROCESS,
506 ame_transaction_type = p_ame_transaction_type, -- Bug 3028744 New column
507 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
508 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
509 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
510 DOCUMENT_TEMPLATE_CODE = P_DOCUMENT_TEMPLATE_CODE, -- POC FPJ
511 CONTRACT_TEMPLATE_CODE = P_CONTRACT_TEMPLATE_CODE, -- POC FPJ
512 -- <Contract AutoSourcing FPJ Start>
513 USE_CONTRACT_FOR_SOURCING_FLAG = p_use_contract_for_sourcing,
514 INCLUDE_NONCATALOG_FLAG = p_include_noncatalog_flag
515 -- <Contract AutoSourcing FPJ End>
516 where DOCUMENT_TYPE_CODE = X_DOCUMENT_TYPE_CODE
517 and DOCUMENT_SUBTYPE = X_DOCUMENT_SUBTYPE
521 raise no_data_found;
518 and ORG_ID= X_ORG_ID ; -- <R12 MOAC>
519
520 if (sql%notfound) then
522 end if;
523
524 update PO_DOCUMENT_TYPES_ALL_TL set
525 TYPE_NAME = X_TYPE_NAME,
526 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
527 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
528 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
529 SOURCE_LANG = userenv('LANG')
530 where DOCUMENT_TYPE_CODE = X_DOCUMENT_TYPE_CODE
531 and DOCUMENT_SUBTYPE = X_DOCUMENT_SUBTYPE
532 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
533 and ORG_ID = X_ORG_ID ; -- <R12 MOAC>
534
535 if (sql%notfound) then
536 raise no_data_found;
537 end if;
538 end UPDATE_ROW;
539
540 procedure DELETE_ROW (
541 X_DOCUMENT_TYPE_CODE in VARCHAR2,
542 X_DOCUMENT_SUBTYPE in VARCHAR2
543 )
544 is
545 X_ORG_ID NUMBER; -- <R12 MOAC>
546 begin
547 X_ORG_ID := PO_MOAC_UTILS_PVT.get_current_org_id ; -- <R12 MOAC>
548 delete from PO_DOCUMENT_TYPES_ALL_TL
549 where DOCUMENT_TYPE_CODE = X_DOCUMENT_TYPE_CODE
550 and DOCUMENT_SUBTYPE = X_DOCUMENT_SUBTYPE
551 and ORG_ID = X_ORG_ID ; -- <R12 MOAC>
552
553 if (sql%notfound) then
554 raise no_data_found;
555 end if;
556
557 delete from PO_DOCUMENT_TYPES_ALL_B
558 where DOCUMENT_TYPE_CODE = X_DOCUMENT_TYPE_CODE
559 and DOCUMENT_SUBTYPE = X_DOCUMENT_SUBTYPE
560 and ORG_ID = X_ORG_ID ; -- <R12 MOAC>
561
562 if (sql%notfound) then
563 raise no_data_found;
564 end if;
565 end DELETE_ROW;
566
567 procedure ADD_LANGUAGE
568 is
569 -- Bug# 4899490: Removing X_ORG_ID, as there is no MOAC context needed
570 begin
571 -- Bug# 4899490: Removing X_ORG_ID, as there is no MOAC context needed
572 delete from PO_DOCUMENT_TYPES_ALL_TL T
573 where not exists
574 (select NULL
575 from PO_DOCUMENT_TYPES_ALL_B B
576 where B.DOCUMENT_TYPE_CODE = T.DOCUMENT_TYPE_CODE
577 and B.DOCUMENT_SUBTYPE = T.DOCUMENT_SUBTYPE
578 and B.ORG_ID = T.ORG_ID -- Bug# 4899490: Retaining 11.5.10 behavior
579 );
580
581 update PO_DOCUMENT_TYPES_ALL_TL T set (
582 TYPE_NAME
583 ) = (select
584 B.TYPE_NAME
585 from PO_DOCUMENT_TYPES_ALL_TL B
586 where B.DOCUMENT_TYPE_CODE = T.DOCUMENT_TYPE_CODE
587 and B.DOCUMENT_SUBTYPE = T.DOCUMENT_SUBTYPE
588 and B.LANGUAGE = T.SOURCE_LANG
589 and B.ORG_ID = T.ORG_ID -- Bug# 4899490: Retaining 11.5.10 behavior
590 )
591 where (
592 T.DOCUMENT_TYPE_CODE,
593 T.DOCUMENT_SUBTYPE,
594 T.LANGUAGE
595 ) in (select
596 SUBT.DOCUMENT_TYPE_CODE,
597 SUBT.DOCUMENT_SUBTYPE,
598 SUBT.LANGUAGE
599 from PO_DOCUMENT_TYPES_ALL_TL SUBB, PO_DOCUMENT_TYPES_ALL_TL SUBT
600 where SUBB.DOCUMENT_TYPE_CODE = SUBT.DOCUMENT_TYPE_CODE
601 and SUBB.DOCUMENT_SUBTYPE = SUBT.DOCUMENT_SUBTYPE
602 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
603 and SUBB.ORG_ID = SUBT.ORG_ID -- Bug# 4899490: Retaining 11.5.10 behavior
604 and (SUBB.TYPE_NAME <> SUBT.TYPE_NAME
605 or (SUBB.TYPE_NAME is null and SUBT.TYPE_NAME is not null)
606 or (SUBB.TYPE_NAME is not null and SUBT.TYPE_NAME is null)
607 ));
608
609 insert into PO_DOCUMENT_TYPES_ALL_TL (
610 ORG_ID,
611 DOCUMENT_TYPE_CODE,
612 DOCUMENT_SUBTYPE,
613 TYPE_NAME,
614 LAST_UPDATE_DATE,
615 LAST_UPDATED_BY,
616 CREATED_BY,
617 CREATION_DATE,
618 LAST_UPDATE_LOGIN,
619 LANGUAGE,
620 SOURCE_LANG
621 ) select
622 B.ORG_ID,
623 B.DOCUMENT_TYPE_CODE,
624 B.DOCUMENT_SUBTYPE,
625 B.TYPE_NAME,
626 B.LAST_UPDATE_DATE,
627 B.LAST_UPDATED_BY,
628 B.CREATED_BY,
629 B.CREATION_DATE,
630 B.LAST_UPDATE_LOGIN,
631 L.LANGUAGE_CODE,
632 B.SOURCE_LANG
633 from PO_DOCUMENT_TYPES_ALL_TL B, FND_LANGUAGES L
634 where L.INSTALLED_FLAG in ('I', 'B')
635 and B.LANGUAGE = userenv('LANG')
636 and not exists
637 (select NULL
638 from PO_DOCUMENT_TYPES_ALL_TL T
639 where T.DOCUMENT_TYPE_CODE = B.DOCUMENT_TYPE_CODE
640 and T.DOCUMENT_SUBTYPE = B.DOCUMENT_SUBTYPE
641 and T.LANGUAGE = L.LANGUAGE_CODE
642 and T.ORG_ID = B.ORG_ID -- Bug# 4899490: Retaining 11.5.10 behavior
643 );
644 end ADD_LANGUAGE;
645
646 procedure TRANSLATE_ROW (X_DOCUMENT_TYPE_CODE in VARCHAR2,
647 X_DOCUMENT_SUBTYPE in VARCHAR2,
648 X_TYPE_NAME in VARCHAR2,
649 X_ORG_ID in NUMBER,
650 X_OWNER in VARCHAR2,
651 X_LAST_UPDATE_DATE in VARCHAR2,
652 X_CUSTOM_MODE in VARCHAR2) IS
653
654 f_luby number; -- entity owner in file
655 f_ludate date; -- entity update date in file
656 db_luby number; -- entity owner in db
657 db_ludate date; -- entity update date in db
658
659 begin
660 f_luby := fnd_load_util.owner_id(X_OWNER);
661 f_ludate := nvl(to_date(X_LAST_UPDATE_DATE, 'DD/MM/YYYY'), sysdate);
662
663 -- bug3703523
664 -- for old see data we have last_updated_by as -1.
665 -- upload_test procedure will consider the record as being customized,
666 -- which is not the case here. Therefore we need to update
670 into db_luby, db_ludate
667 -- last_updated_by to 1 when it is -1 so that the record can be updated.
668
669 select DECODE(LAST_UPDATED_BY, -1, 1, LAST_UPDATED_BY), LAST_UPDATE_DATE
671 from PO_DOCUMENT_TYPES_ALL_TL
672 where document_type_code = X_DOCUMENT_TYPE_CODE
673 and document_subtype = X_DOCUMENT_SUBTYPE
674 and ( (X_ORG_ID is null and org_id = -3113 )
675 or (X_ORG_ID is not null and org_id = X_ORG_ID))
676 and language = userenv('LANG') ;
677
678 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
679 db_ludate, X_CUSTOM_MODE)) then
680
681 update PO_DOCUMENT_TYPES_ALL_TL set
682 type_name = X_TYPE_NAME ,
683 last_update_date = f_ludate,
684 last_updated_by = f_luby,
685 last_update_login = 0,
686 source_lang = userenv('LANG')
687 where document_type_code = X_DOCUMENT_TYPE_CODE
688 and document_subtype = X_DOCUMENT_SUBTYPE
689 and userenv('LANG') in (language, source_lang);
690
691 end if;
692
693 exception
694 when no_data_found then
695 -- Do not insert missing translations, skip this row
696 null;
697 end TRANSLATE_ROW;
698
699 procedure LOAD_ROW (X_DOCUMENT_TYPE_CODE in VARCHAR2,
700 X_DOCUMENT_SUBTYPE in VARCHAR2,
701 X_ORG_ID in NUMBER,
702 X_WF_CREATEDOC_ITEMTYPE in VARCHAR2,
703 X_ARCHIVE_EXTERNAL_REVISION_CO in VARCHAR2,
704 X_CAN_PREPARER_APPROVE_FLAG in VARCHAR2,
705 X_FORWARDING_MODE_CODE in VARCHAR2,
706 X_CAN_CHANGE_FORWARD_FROM_FLAG in VARCHAR2,
707 X_CAN_APPROVER_MODIFY_DOC_FLAG in VARCHAR2,
708 X_CAN_CHANGE_APPROVAL_PATH_FLA in VARCHAR2,
709 X_CAN_CHANGE_FORWARD_TO_FLAG in VARCHAR2,
710 X_QUOTATION_CLASS_CODE in VARCHAR2,
711 X_DEFAULT_APPROVAL_PATH_ID in NUMBER,
712 X_ATTRIBUTE_CATEGORY in VARCHAR2,
713 X_ATTRIBUTE1 in VARCHAR2,
714 X_ATTRIBUTE2 in VARCHAR2,
715 X_ATTRIBUTE3 in VARCHAR2,
716 X_ATTRIBUTE4 in VARCHAR2,
717 X_ATTRIBUTE5 in VARCHAR2,
718 X_ATTRIBUTE6 in VARCHAR2,
719 X_ATTRIBUTE7 in VARCHAR2,
720 X_ATTRIBUTE8 in VARCHAR2,
721 X_ATTRIBUTE9 in VARCHAR2,
722 X_ATTRIBUTE10 in VARCHAR2,
723 X_ATTRIBUTE11 in VARCHAR2,
724 X_ATTRIBUTE12 in VARCHAR2,
725 X_ATTRIBUTE13 in VARCHAR2,
726 X_ATTRIBUTE14 in VARCHAR2,
727 X_ATTRIBUTE15 in VARCHAR2,
728 X_SECURITY_LEVEL_CODE in VARCHAR2,
729 X_ACCESS_LEVEL_CODE in VARCHAR2,
730 X_DISABLED_FLAG in VARCHAR2,
731 X_REQUEST_ID in NUMBER,
732 X_WF_APPROVAL_ITEMTYPE in VARCHAR2,
733 X_WF_APPROVAL_PROCESS in VARCHAR2,
734 X_WF_CREATEDOC_PROCESS in VARCHAR2,
735 p_ame_transaction_type IN VARCHAR2, -- Bug 3028744
736 X_TYPE_NAME in VARCHAR2,
737 X_OWNER in VARCHAR2,
738 X_LAST_UPDATE_DATE in VARCHAR2,
739 X_CUSTOM_MODE in VARCHAR2,
740 P_DOCUMENT_TEMPLATE_CODE in VARCHAR2, -- POC FPJ
741 P_CONTRACT_TEMPLATE_CODE in VARCHAR2) IS
742
743
744 l_row_id varchar2(64);
745 f_luby number; -- entity owner in file
746 f_ludate date; -- entity update date in file
747 db_luby number; -- entity owner in db
748 db_ludate date; -- entity update date in db
749
750 --Bug 6810625 Start
751 /* Changed table in query from PO_DOCUMENT_TYPES_ALL_B to PO_DOCUMENT_TYPES_ALL_VL
752 because table PO_DOCUMENT_TYPES_ALL_B doesnot contain TYPE_NAME Column.
753 This column is in table PO_DOCUMENT_TYPES_ALL_TL.
754 but view PO_DOCUMENT_TYPES_ALL_VL contains this column as its queries from both
755 the tables PO_DOCUMENT_TYPES_ALL_B and PO_DOCUMENT_TYPES_ALL_TL. */
756
757 --rec PO_DOCUMENT_TYPES_ALL_B%Rowtype; -- Bug6086648 FP:Bug 5985709
758 rec PO_DOCUMENT_TYPES_ALL_VL%Rowtype;
759 --End Bug 6810625
760
761 begin
762
763 -- Bug6910423 Moved these statements before Select statement.
764 f_luby := fnd_load_util.owner_id(X_OWNER);
765 f_ludate := nvl(to_date(X_LAST_UPDATE_DATE, 'DD/MM/YYYY'), sysdate);
766 --End Bug6910423
767
768 -- Bug6086648 FP:Bug 5985709. Query added to get the existing data.
769 -- Bug 6810625
770 SELECT * into rec FROM PO_DOCUMENT_TYPES_ALL_VL
771 where DOCUMENT_TYPE_CODE = X_DOCUMENT_TYPE_CODE
772 and DOCUMENT_SUBTYPE = X_DOCUMENT_SUBTYPE
773 and NVL(X_ORG_ID, -99) = NVL(ORG_ID, -99) ;
774 -- End Bug 6810625
775
776 -- bug3703523
777 -- for old see data we have last_updated_by as -1.
778 -- upload_test procedure will consider the record as being customized,
779 -- which is not the case here. Therefore we need to update
780 -- last_updated_by to 1 when it is -1 so that the record can be updated.
781
782 select DECODE(LAST_UPDATED_BY, -1, 1, LAST_UPDATED_BY), LAST_UPDATE_DATE
783 into db_luby, db_ludate
784 from PO_DOCUMENT_TYPES_ALL_TL
785 where document_type_code = X_DOCUMENT_TYPE_CODE
786 and document_subtype = X_DOCUMENT_SUBTYPE
787 and ( (X_ORG_ID is null and org_id = -3113 )
788 or (X_ORG_ID is not null and org_id = X_ORG_ID))
789 and language = userenv('LANG');
790
791 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
792 db_ludate, X_CUSTOM_MODE)) then
793 /* Bug6086648 FP:Bug 5985709 Start.
794 Modified the parameters in call to UPDATE_ROW to prioritise the
795 existing values, using NVL, over the seed values. */
796
797 /* UPDATE_ROW (X_DOCUMENT_TYPE_CODE ,
798 X_DOCUMENT_SUBTYPE ,
799 X_WF_CREATEDOC_ITEMTYPE ,
800 X_ARCHIVE_EXTERNAL_REVISION_CO ,
801 X_CAN_PREPARER_APPROVE_FLAG ,
802 X_FORWARDING_MODE_CODE ,
803 X_CAN_CHANGE_FORWARD_FROM_FLAG ,
804 X_CAN_APPROVER_MODIFY_DOC_FLAG ,
805 X_CAN_CHANGE_APPROVAL_PATH_FLA ,
806 X_CAN_CHANGE_FORWARD_TO_FLAG ,
807 X_QUOTATION_CLASS_CODE ,
808 X_DEFAULT_APPROVAL_PATH_ID ,
809 X_ATTRIBUTE_CATEGORY ,
810 X_ATTRIBUTE1 ,
811 X_ATTRIBUTE2 ,
812 X_ATTRIBUTE3 ,
813 X_ATTRIBUTE4 ,
814 X_ATTRIBUTE5 ,
815 X_ATTRIBUTE6 ,
816 X_ATTRIBUTE7 ,
817 X_ATTRIBUTE8 ,
818 X_ATTRIBUTE9 ,
819 X_ATTRIBUTE10 ,
820 X_ATTRIBUTE11 ,
821 X_ATTRIBUTE12 ,
822 X_ATTRIBUTE13 ,
823 X_ATTRIBUTE14 ,
824 X_ATTRIBUTE15 ,
825 X_SECURITY_LEVEL_CODE ,
826 X_ACCESS_LEVEL_CODE ,
827 X_DISABLED_FLAG ,
828 X_REQUEST_ID ,
829 X_WF_APPROVAL_ITEMTYPE ,
830 X_WF_APPROVAL_PROCESS ,
831 X_WF_CREATEDOC_PROCESS ,
832 p_ame_transaction_type, -- Bug 3028744 New column
833 X_TYPE_NAME ,
834 f_ludate ,
835 f_luby,
836 0,
837 P_DOCUMENT_TEMPLATE_CODE , --POC FPJ
838 P_CONTRACT_TEMPLATE_CODE , --POC FPJ
839 --<Contract AutoSourcing FPJ Start>
840 -- Pass NULL to use_contract_for_sourcing_flag and include_noncatalog_flag
841 -- so that they do not need to be defaulted from Seed data
842 NULL,
843 NULL ,
844 --<Contract AutoSourcing FPJ End>
845 X_ORG_ID
846 );*/
847 UPDATE_ROW (X_DOCUMENT_TYPE_CODE ,
848 X_DOCUMENT_SUBTYPE ,
852 NVL( rec.FORWARDING_MODE_CODE , X_FORWARDING_MODE_CODE ) ,
849 NVL( rec.WF_CREATEDOC_ITEMTYPE , X_WF_CREATEDOC_ITEMTYPE ) ,
850 NVL( rec.ARCHIVE_EXTERNAL_REVISION_CODE ,X_ARCHIVE_EXTERNAL_REVISION_CO ) ,
851 NVL( rec.CAN_PREPARER_APPROVE_FLAG ,X_CAN_PREPARER_APPROVE_FLAG ) ,
853 NVL( rec.CAN_CHANGE_FORWARD_FROM_FLAG ,X_CAN_CHANGE_FORWARD_FROM_FLAG ) ,
854 NVL( rec.CAN_APPROVER_MODIFY_DOC_FLAG ,X_CAN_APPROVER_MODIFY_DOC_FLAG ) ,
855 NVL( rec.CAN_CHANGE_APPROVAL_PATH_FLAG ,X_CAN_CHANGE_APPROVAL_PATH_FLA ) ,
856 NVL( rec.CAN_CHANGE_FORWARD_TO_FLAG ,X_CAN_CHANGE_FORWARD_TO_FLAG ) ,
857 NVL( rec.QUOTATION_CLASS_CODE , X_QUOTATION_CLASS_CODE ) ,
858 NVL( rec.DEFAULT_APPROVAL_PATH_ID , X_DEFAULT_APPROVAL_PATH_ID) ,
859 NVL( rec.ATTRIBUTE_CATEGORY , X_ATTRIBUTE_CATEGORY ) ,
860 NVL( rec.ATTRIBUTE1 , X_ATTRIBUTE1 ) ,
861 NVL( rec.ATTRIBUTE2 , X_ATTRIBUTE2 ) ,
862 NVL( rec.ATTRIBUTE3 , X_ATTRIBUTE3 ) ,
863 NVL( rec.ATTRIBUTE4 , X_ATTRIBUTE4 ) ,
864 NVL( rec.ATTRIBUTE5 , X_ATTRIBUTE5 ) ,
865 NVL( rec.ATTRIBUTE6 , X_ATTRIBUTE6 ) ,
866 NVL( rec.ATTRIBUTE7 , X_ATTRIBUTE7 ) ,
867 NVL( rec.ATTRIBUTE8 , X_ATTRIBUTE8 ) ,
868 NVL( rec.ATTRIBUTE9 , X_ATTRIBUTE9 ) ,
869 NVL( rec.ATTRIBUTE10 , X_ATTRIBUTE10 ) ,
870 NVL( rec.ATTRIBUTE11 , X_ATTRIBUTE11 ) ,
871 NVL( rec.ATTRIBUTE12 , X_ATTRIBUTE12 ) ,
872 NVL( rec.ATTRIBUTE13 , X_ATTRIBUTE13 ) ,
873 NVL( rec.ATTRIBUTE14 , X_ATTRIBUTE14 ) ,
874 NVL( rec.ATTRIBUTE15 , X_ATTRIBUTE15 ) ,
875 NVL( rec.SECURITY_LEVEL_CODE , X_SECURITY_LEVEL_CODE ) ,
876 NVL( rec.ACCESS_LEVEL_CODE , X_ACCESS_LEVEL_CODE ) ,
877 NVL( rec.DISABLED_FLAG , X_DISABLED_FLAG ) ,
878 NVL( rec.REQUEST_ID , X_REQUEST_ID ) ,
879 NVL( rec.WF_APPROVAL_ITEMTYPE , X_WF_APPROVAL_ITEMTYPE ) ,
880 NVL( rec.WF_APPROVAL_PROCESS , X_WF_APPROVAL_PROCESS ) ,
881 NVL( rec.WF_CREATEDOC_PROCESS , X_WF_CREATEDOC_PROCESS ) ,
882 NVL( rec.ame_transaction_type , p_ame_transaction_type ) , --Bug 3028744 New column
883 NVL( rec.TYPE_NAME , X_TYPE_NAME ) ,
884 f_ludate ,
885 f_luby,
886 0,
887 NVL( rec.DOCUMENT_TEMPLATE_CODE , P_DOCUMENT_TEMPLATE_CODE ) ,--POC FPJ
888 NVL( rec.CONTRACT_TEMPLATE_CODE , P_CONTRACT_TEMPLATE_CODE ) ,--POC FPJ
889 --<Contract AutoSourcing FPJ Start>
890 -- Pass NULL to use_contract_for_sourcing_flag and include_noncatalog_flag
891 -- so that they do not need to be defaulted from Seed data
892 NULL,
893 NULL,
894 --<Contract AutoSourcing FPJ End>
895 X_ORG_ID
896 );
897
898 /* Bug6086648 FP:Bug 5985709 End. */
899
900 end if;
901
902 exception
903 when NO_DATA_FOUND then
904 INSERT_ROW (l_row_id ,
905 X_DOCUMENT_TYPE_CODE ,
906 X_DOCUMENT_SUBTYPE ,
907 X_WF_CREATEDOC_ITEMTYPE ,
908 X_ARCHIVE_EXTERNAL_REVISION_CO ,
909 X_CAN_PREPARER_APPROVE_FLAG ,
910 X_FORWARDING_MODE_CODE ,
911 X_CAN_CHANGE_FORWARD_FROM_FLAG ,
912 X_CAN_APPROVER_MODIFY_DOC_FLAG ,
916 X_DEFAULT_APPROVAL_PATH_ID ,
913 X_CAN_CHANGE_APPROVAL_PATH_FLA ,
914 X_CAN_CHANGE_FORWARD_TO_FLAG ,
915 X_QUOTATION_CLASS_CODE ,
917 X_ATTRIBUTE_CATEGORY ,
918 X_ATTRIBUTE1 ,
919 X_ATTRIBUTE2 ,
920 X_ATTRIBUTE3 ,
921 X_ATTRIBUTE4 ,
922 X_ATTRIBUTE5 ,
923 X_ATTRIBUTE6 ,
924 X_ATTRIBUTE7 ,
925 X_ATTRIBUTE8 ,
926 X_ATTRIBUTE9 ,
927 X_ATTRIBUTE10 ,
928 X_ATTRIBUTE11 ,
929 X_ATTRIBUTE12 ,
930 X_ATTRIBUTE13 ,
931 X_ATTRIBUTE14 ,
932 X_ATTRIBUTE15 ,
933 X_SECURITY_LEVEL_CODE ,
934 X_ACCESS_LEVEL_CODE ,
935 X_DISABLED_FLAG ,
936 X_REQUEST_ID ,
937 X_WF_APPROVAL_ITEMTYPE ,
938 X_WF_APPROVAL_PROCESS ,
939 X_WF_CREATEDOC_PROCESS ,
940 p_ame_transaction_type, -- Bug 3028744 New column
941 X_TYPE_NAME ,
942 f_ludate ,
943 f_luby ,
944 f_ludate ,
945 f_luby ,
946 0,
947 P_DOCUMENT_TEMPLATE_CODE , --POC FPJ
948 P_CONTRACT_TEMPLATE_CODE , --POC FPJ
949 --<Contract AutoSourcing FPJ Start>
950 -- Pass NULL to use_contract_for_sourcing_flag and include_noncatalog_flag
951 -- so that they do not need to be defaulted from Seed data
952 NULL,
953 NULL,
954 --<Contract AutoSourcing FPJ End>
955 X_ORG_ID
956 );
957
958 end LOAD_ROW;
959
960 -------------------------------------------------------------------------------
961 --Start of Comments
962 --Name: insert_lookup_row
963 --Pre-reqs:
964 -- None.
965 --Modifies:
966 -- FND_LOOKUP_VALUES
967 --Locks:
968 -- None.
969 --Function:
970 -- This procedure acts as a pl/sql wrapper over the existing fnd api
971 -- FND_LOOKUP_VALUES_PKG.INSERT_ROW. It is used to insert the user
972 -- defined document subtype into fnd_lookup_values.It defaults the
973 -- values and limits the input parameters to a minimum.
974 --Parameters:
975 --IN:
976 --p_lookup_type
977 -- The lookup type for the row to be inserted in fnd_lookup_values.
978 -- This value is derived from the document type as explained in comments below.
979 --p_lookup_code
980 -- The lookup code for the row to be inserted in fnd_lookup_values.
981 -- This is equal to the document subtype entered by the user. The same
982 -- value is stored as the meaning and description for the row in fnd lookups.
983 --p_creation_date
984 -- Standard who column.
985 --p_created_by
986 -- Standard who column.
987 --p_last_update_date
988 -- Standard who column.
989 --p_last_updated_by
990 -- Standard who column.
991 --p_last_update_login
992 -- Standard who column.
993 --Notes:
994 -- This wrapper has been added as a part of the R12 HTML Setup enhancement
995 -- for inserting the user defined document subtypes into fnd lookups. This
996 -- api is essentially called via a jdbc call from the Document Types Helper.
997 -- The lookup type is determined based on the document type. It equals 'RFQ
998 -- SUBTYPE' for RFQ Document Types and 'QUOTATION SUBTYPE' for QUOTATION
999 -- Document Types.
1000 --Testing:
1001 -- On creating a new Document Type from the setup page, check that a record
1002 -- has been inserted in fnd_lookup_values table (or the po_lookup_codes
1003 -- view based on it). The lookup type would depend on the Document Type as
1004 -- explained above. The lookup code, description and meaning (or displayed
1005 -- field) should be equal to the user entered document subtype value.
1006 --End of Comments
1007 -------------------------------------------------------------------------------
1008 procedure INSERT_LOOKUP_ROW (P_LOOKUP_TYPE in VARCHAR2,
1009 P_LOOKUP_CODE in VARCHAR2,
1010 P_CREATION_DATE in DATE,
1011 P_CREATED_BY in NUMBER,
1012 P_LAST_UPDATE_DATE in DATE,
1013 P_LAST_UPDATED_BY in NUMBER,
1014 P_LAST_UPDATE_LOGIN in NUMBER) IS
1015 l_rowid varchar2(64);
1016 begin
1017 FND_LOOKUP_VALUES_PKG.INSERT_ROW(l_rowid,
1018 P_LOOKUP_TYPE,
1019 0, --security group id
1020 201, --view application id
1021 P_LOOKUP_CODE,
1022 null, --tag
1023 null, --attribute_category
1024 null, --attribute1
1025 null, --attribute2
1026 null, --attribute3
1027 null, --attribute4
1028 'Y', --enabled_flag
1029 null, --start_date_active
1030 null, --end_date_active
1031 null, --territory_code
1032 null, --attribute5
1033 null, --attribute6
1034 null, --attribute7
1035 null, --attribute8
1036 null, --attribute9
1040 null, --attribute13
1037 null, --attribute10
1038 null, --attribute11
1039 null, --attribute12
1041 null, --attribute14
1042 null, --attribute15
1043 P_LOOKUP_CODE, --meaning
1044 P_LOOKUP_CODE, --description
1045 P_CREATION_DATE,
1046 P_CREATED_BY,
1047 P_LAST_UPDATE_DATE,
1048 P_LAST_UPDATED_BY,
1049 P_LAST_UPDATE_LOGIN);
1050 end INSERT_LOOKUP_ROW;
1051
1052 -------------------------------------------------------------------------------
1053 --Start of Comments
1054 --Name: delete_lookup_row
1055 --Pre-reqs:
1056 -- None.
1057 --Modifies:
1058 -- FND_LOOKUP_VALUES
1059 --Locks:
1060 -- None.
1061 --Function:
1062 -- This procedure acts as a pl/sql wrapper over the existing fnd api
1063 -- FND_LOOKUP_VALUES_PKG.DELETE_ROW. It is used to delete the user
1064 -- defined document subtype from fnd_lookup_values.It defaults the
1065 -- values and limits the input parameters to a minimum.
1066 --Parameters:
1067 --IN:
1068 --p_lookup_type
1069 -- The lookup type for the row to be deleted from fnd_lookup_values.
1070 -- This value is derived from the document type as explained in comments below.
1071 --p_lookup_code
1072 -- The lookup code for the row to be deleted from fnd_lookup_values.
1073 -- This is equal to the document subtype entered by the user.
1074 --Notes:
1075 -- This wrapper has been added as a part of the R12 HTML Setup enhancement
1076 -- for deleting the user defined document subtypes from fnd lookups. This
1077 -- api is essentially called via a jdbc call from the Document Types Helper.
1078 -- The lookup type is determined based on the document type. It equals 'RFQ
1079 -- SUBTYPE' for RFQ Document Types and 'QUOTATION SUBTYPE' for QUOTATION
1080 -- Document Types.
1081 --Testing:
1082 -- On deleting the Document Type from the setup page, check that the
1083 -- corresponding record has been deleted from fnd_lookup_values
1084 -- (or po_lookup_codes).
1085 --End of Comments
1086 -------------------------------------------------------------------------------
1087 procedure DELETE_LOOKUP_ROW (P_LOOKUP_TYPE in VARCHAR2,
1088 P_LOOKUP_CODE in VARCHAR2) IS
1089 begin
1090 FND_LOOKUP_VALUES_PKG.DELETE_ROW(P_LOOKUP_TYPE,
1091 0, --security group id
1092 201, --view application id
1093 P_LOOKUP_CODE);
1094 end DELETE_LOOKUP_ROW;
1095
1096 end PO_DOCUMENT_TYPES_ALL_PKG;
[Home] [Help]