1 package body ZX_FC_TYPES_PKG as
2 /* $Header: zxcfctypesb.pls 120.11 2006/05/05 17:53:21 vramamur ship $ */
3
4 procedure INSERT_ROW (
5 X_ROWID in out nocopy VARCHAR2,
6 X_CLASSIFICATION_TYPE_ID in NUMBER,
7 X_CLASSIFICATION_TYPE_CODE in VARCHAR2,
8 X_Classification_Type_Categ_Co in VARCHAR2,
9 X_CLASSIFICATION_TYPE_GROUP_CO in VARCHAR2,
10 X_DELIMITER in VARCHAR2,
11 X_Owner_Table_Code in VARCHAR2,
12 X_OWNER_ID_NUM in NUMBER,
13 X_OWNER_ID_CHAR in VARCHAR2,
14 X_EFFECTIVE_FROM in DATE,
15 X_EFFECTIVE_TO in DATE,
16 X_START_POSITION in NUMBER,
17 X_NUM_CHARACTERS in NUMBER,
18 X_CLASSIFICATION_TYPE_LEVEL_CO in VARCHAR2,
19 X_REQUEST_ID in NUMBER,
20 X_Record_Type_Code in VARCHAR2,
21 X_ATTRIBUTE1 in VARCHAR2,
22 X_ATTRIBUTE2 in VARCHAR2,
23 X_ATTRIBUTE3 in VARCHAR2,
24 X_ATTRIBUTE4 in VARCHAR2,
25 X_ATTRIBUTE5 in VARCHAR2,
26 X_ATTRIBUTE6 in VARCHAR2,
27 X_ATTRIBUTE7 in VARCHAR2,
28 X_ATTRIBUTE8 in VARCHAR2,
29 X_ATTRIBUTE9 in VARCHAR2,
30 X_ATTRIBUTE10 in VARCHAR2,
31 X_ATTRIBUTE11 in VARCHAR2,
32 X_ATTRIBUTE12 in VARCHAR2,
33 X_ATTRIBUTE13 in VARCHAR2,
34 X_ATTRIBUTE14 in VARCHAR2,
35 X_ATTRIBUTE15 in VARCHAR2,
36 X_ATTRIBUTE_CATEGORY in VARCHAR2,
37 X_CLASSIFICATION_TYPE_NAME in VARCHAR2,
38 X_CREATION_DATE in DATE,
39 X_CREATED_BY in NUMBER,
40 X_LAST_UPDATE_DATE in DATE,
41 X_LAST_UPDATED_BY in NUMBER,
42 X_LAST_UPDATE_LOGIN in NUMBER,
43 X_PROGRAM_APPLICATION_ID in NUMBER,
44 X_PROGRAM_ID in NUMBER,
45 X_Program_Login_Id in NUMBER,
46 X_OBJECT_VERSION_NUMBER in NUMBER
47 ) is
48 cursor C is select ROWID from ZX_FC_TYPES_B
49 where CLASSIFICATION_TYPE_ID = X_CLASSIFICATION_TYPE_ID
50 ;
51 l_row_id VARCHAR2(80);
52 l_seq_val NUMBER;
53 l_count NUMBER;
54 begin
55
56 insert into ZX_FC_TYPES_B (
57 CLASSIFICATION_TYPE_ID,
58 CLASSIFICATION_TYPE_CODE,
59 Classification_Type_Categ_Code,
60 CLASSIFICATION_TYPE_GROUP_CODE,
61 DELIMITER,
62 Owner_Table_Code,
63 OWNER_ID_NUM,
64 OWNER_ID_CHAR,
65 EFFECTIVE_FROM,
66 EFFECTIVE_TO,
67 START_POSITION,
68 NUM_CHARACTERS,
69 Classification_Type_Level_Code,
70 REQUEST_ID,
71 Record_Type_Code,
72 ATTRIBUTE1,
73 ATTRIBUTE2,
74 ATTRIBUTE3,
75 ATTRIBUTE4,
76 ATTRIBUTE5,
77 ATTRIBUTE6,
78 ATTRIBUTE7,
79 ATTRIBUTE8,
80 ATTRIBUTE9,
81 ATTRIBUTE10,
82 ATTRIBUTE11,
83 ATTRIBUTE12,
84 ATTRIBUTE13,
85 ATTRIBUTE14,
86 ATTRIBUTE15,
87 ATTRIBUTE_CATEGORY,
88 CREATION_DATE,
89 CREATED_BY,
90 LAST_UPDATE_DATE,
91 LAST_UPDATED_BY,
92 LAST_UPDATE_LOGIN,
93 PROGRAM_APPLICATION_ID,
94 PROGRAM_ID ,
95 Program_Login_Id,
96 OBJECT_VERSION_NUMBER
97 ) values (
98 X_CLASSIFICATION_TYPE_ID,
99 X_CLASSIFICATION_TYPE_CODE,
100 X_Classification_Type_Categ_Co,
101 X_CLASSIFICATION_TYPE_GROUP_CO,
102 X_DELIMITER,
103 X_Owner_Table_Code,
104 X_OWNER_ID_NUM,
105 X_OWNER_ID_CHAR,
106 X_EFFECTIVE_FROM,
107 X_EFFECTIVE_TO,
108 X_START_POSITION,
109 X_NUM_CHARACTERS,
110 X_CLASSIFICATION_TYPE_LEVEL_CO,
111 X_REQUEST_ID,
112 X_Record_Type_Code,
113 X_ATTRIBUTE1,
114 X_ATTRIBUTE2,
115 X_ATTRIBUTE3,
116 X_ATTRIBUTE4,
117 X_ATTRIBUTE5,
118 X_ATTRIBUTE6,
119 X_ATTRIBUTE7,
120 X_ATTRIBUTE8,
121 X_ATTRIBUTE9,
122 X_ATTRIBUTE10,
123 X_ATTRIBUTE11,
124 X_ATTRIBUTE12,
125 X_ATTRIBUTE13,
126 X_ATTRIBUTE14,
127 X_ATTRIBUTE15,
128 X_ATTRIBUTE_CATEGORY,
129 X_CREATION_DATE,
130 X_CREATED_BY,
131 X_LAST_UPDATE_DATE,
132 X_LAST_UPDATED_BY,
133 X_LAST_UPDATE_LOGIN,
134 X_PROGRAM_APPLICATION_ID,
135 X_PROGRAM_ID,
136 X_Program_Login_Id,
137 X_OBJECT_VERSION_NUMBER
138 );
139 insert into ZX_FC_TYPES_TL (
140 CLASSIFICATION_TYPE_ID,
141 CLASSIFICATION_TYPE_NAME,
142 CREATED_BY,
143 CREATION_DATE,
144 LAST_UPDATED_BY,
145 LAST_UPDATE_DATE,
146 LAST_UPDATE_LOGIN,
147 LANGUAGE,
148 SOURCE_LANG
149 ) select
150 X_CLASSIFICATION_TYPE_ID,
151 X_CLASSIFICATION_TYPE_NAME,
152 X_CREATED_BY,
153 X_CREATION_DATE,
154 X_LAST_UPDATED_BY,
155 X_LAST_UPDATE_DATE,
156 X_LAST_UPDATE_LOGIN,
157 L.LANGUAGE_CODE,
158 userenv('LANG')
159 from FND_LANGUAGES L
160 where L.INSTALLED_FLAG in ('I', 'B')
161 and not exists
162 (select NULL
163 from ZX_FC_TYPES_TL T
164 where T.CLASSIFICATION_TYPE_ID = X_CLASSIFICATION_TYPE_ID
165 and T.LANGUAGE = L.LANGUAGE_CODE);
166 open c;
167 fetch c into X_ROWID;
168 if (c%notfound) then
169 close c;
170 raise no_data_found;
171 end if;
172 close c;
173 SELECT Zx_Determining_Factors_B_S.nextval into l_seq_val FROM DUAL;
174
175 ZX_DETERMINING_FACTORS_PKG.INSERT_ROW(
176 X_ROWID,
177 l_seq_val, --X_DETERMINING_FACTOR_ID,
178 x_classification_type_code, --X_DETERMINING_FACTOR_CODE,
179 X_Classification_Type_Categ_Co, --X_DETERMINING_FACTOR_CLASS_COD,
180 NULL, --X_VALUE_SET,
181 NULL, --X_TAX_PARAMETER_CODE,
182 'ALPHANUMERIC', --X_DATA_TYPE_CODE,
183 NULL, --X_TAX_FUNCTION_CODE,
184 X_Record_Type_Code, --X_RECORD_TYPE_CODE,
185 'N', --X_TAX_REGIME_DET_FLAG,
186 'Y', --X_TAX_SUMMARIZATION_FLAG,
187 'Y', --X_TAX_RULES_FLAG,
188 'N', --X_TAXABLE_BASIS_FLAG,
189 'N', --X_TAX_CALCULATION_FLAG,
190 'Y', --X_INTERNAL_FLAG,
191 'N', --X_RECORD_ONLY_FLAG,
192 X_REQUEST_ID, --X_REQUEST_ID,
193 X_CLASSIFICATION_TYPE_NAME, --X_DETERMINING_FACTOR_NAME,
194 NULL, --X_DETERMINING_FACTOR_DESC,
195 X_CREATION_DATE, --X_CREATION_DATE,
196 X_CREATED_BY, --X_CREATED_BY,
197 X_LAST_UPDATE_DATE, --X_LAST_UPDATE_DATE,
198 X_LAST_UPDATED_BY, --X_LAST_UPDATED_BY,
199 X_LAST_UPDATE_LOGIN, --X_LAST_UPDATE_LOGIN
200 X_OBJECT_VERSION_NUMBER);
201
202 -- If FC Group Code is not null then insert into determining factors. Bug # 5111304
203 IF X_CLASSIFICATION_TYPE_GROUP_CO IS NOT NULL and
204 X_CLASSIFICATION_TYPE_CATEG_CO = 'PRODUCT_FISCAL_CLASS' THEN
205
206 select count(*) into l_count from ZX_DETERMINING_FACTORS_B where
207 DETERMINING_FACTOR_CLASS_CODE = X_CLASSIFICATION_TYPE_CATEG_CO and
208 DETERMINING_FACTOR_CODE = X_CLASSIFICATION_TYPE_GROUP_CO;
209
210 -- If FC Group code not exists then insert into determining factors
211 IF l_count = 0 THEN
212
213 SELECT Zx_Determining_Factors_B_S.nextval into l_seq_val FROM DUAL;
214
215 ZX_DETERMINING_FACTORS_PKG.INSERT_ROW(
216 X_ROWID,
217 l_seq_val, --X_DETERMINING_FACTOR_ID,
218 X_CLASSIFICATION_TYPE_GROUP_CO,--X_DETERMINING_FACTOR_CODE,
219 X_CLASSIFICATION_TYPE_CATEG_CO,--X_DETERMINING_FACTOR_CLASS_COD,
220 NULL, --X_VALUE_SET,
221 NULL, --X_TAX_PARAMETER_CODE,
222 'ALPHANUMERIC', --X_DATA_TYPE_CODE,
223 NULL, --X_TAX_FUNCTION_CODE,
224 X_Record_Type_Code, --X_RECORD_TYPE_CODE,
225 'N', --X_TAX_REGIME_DET_FLAG,
226 'Y', --X_TAX_SUMMARIZATION_FLAG,
227 'Y', --X_TAX_RULES_FLAG,
228 'N', --X_TAXABLE_BASIS_FLAG,
229 'N', --X_TAX_CALCULATION_FLAG,
230 'Y', --X_INTERNAL_FLAG,
231 'N', --X_RECORD_ONLY_FLAG,
232 X_REQUEST_ID, --X_REQUEST_ID,
233 X_CLASSIFICATION_TYPE_GROUP_CO,--X_DETERMINING_FACTOR_NAME,
234 NULL, --X_DETERMINING_FACTOR_DESC,
235 X_CREATION_DATE, --X_CREATION_DATE,
236 X_CREATED_BY, --X_CREATED_BY,
237 X_LAST_UPDATE_DATE, --X_LAST_UPDATE_DATE,
238 X_LAST_UPDATED_BY, --X_LAST_UPDATED_BY,
239 X_LAST_UPDATE_LOGIN, --X_LAST_UPDATE_LOGIN
240 X_OBJECT_VERSION_NUMBER);
241 END IF;
242
243 END IF;
244
245 end INSERT_ROW;
246
247 procedure LOCK_ROW (
248 X_CLASSIFICATION_TYPE_ID in NUMBER,
249 X_CLASSIFICATION_TYPE_CODE in VARCHAR2,
250 X_Classification_Type_Categ_Co in VARCHAR2,
251 X_CLASSIFICATION_TYPE_GROUP_CO in VARCHAR2,
252 X_DELIMITER in VARCHAR2,
253 X_Owner_Table_Code in VARCHAR2,
254 X_OWNER_ID_NUM in NUMBER,
255 X_OWNER_ID_CHAR in VARCHAR2,
256 X_EFFECTIVE_FROM in DATE,
257 X_EFFECTIVE_TO in DATE,
258 X_START_POSITION in NUMBER,
259 X_NUM_CHARACTERS in NUMBER,
260 X_CLASSIFICATION_TYPE_LEVEL_CO in VARCHAR2,
261 X_REQUEST_ID in NUMBER,
262 X_Record_Type_Code in VARCHAR2,
263 X_ATTRIBUTE1 in VARCHAR2,
264 X_ATTRIBUTE2 in VARCHAR2,
265 X_ATTRIBUTE3 in VARCHAR2,
266 X_ATTRIBUTE4 in VARCHAR2,
267 X_ATTRIBUTE5 in VARCHAR2,
268 X_ATTRIBUTE6 in VARCHAR2,
269 X_ATTRIBUTE7 in VARCHAR2,
270 X_ATTRIBUTE8 in VARCHAR2,
271 X_ATTRIBUTE9 in VARCHAR2,
272 X_ATTRIBUTE10 in VARCHAR2,
273 X_ATTRIBUTE11 in VARCHAR2,
274 X_ATTRIBUTE12 in VARCHAR2,
275 X_ATTRIBUTE13 in VARCHAR2,
276 X_ATTRIBUTE14 in VARCHAR2,
277 X_ATTRIBUTE15 in VARCHAR2,
278 X_ATTRIBUTE_CATEGORY in VARCHAR2,
279 X_CLASSIFICATION_TYPE_NAME in VARCHAR2,
280 X_PROGRAM_APPLICATION_ID in NUMBER,
281 X_PROGRAM_ID in NUMBER,
282 X_Program_Login_Id in NUMBER,
283 X_OBJECT_VERSION_NUMBER in NUMBER
284 ) is
285 cursor c is select
286 CLASSIFICATION_TYPE_CODE,
287 Classification_Type_Categ_Code,
288 CLASSIFICATION_TYPE_GROUP_CODE,
289 DELIMITER,
290 Owner_Table_Code,
291 OWNER_ID_NUM,
292 OWNER_ID_CHAR,
293 EFFECTIVE_FROM,
294 EFFECTIVE_TO,
295 START_POSITION,
296 NUM_CHARACTERS,
297 Classification_Type_Level_Code,
298 REQUEST_ID,
299 Record_Type_Code,
300 ATTRIBUTE1,
301 ATTRIBUTE2,
302 ATTRIBUTE3,
303 ATTRIBUTE4,
304 ATTRIBUTE5,
305 ATTRIBUTE6,
306 ATTRIBUTE7,
307 ATTRIBUTE8,
308 ATTRIBUTE9,
309 ATTRIBUTE10,
310 ATTRIBUTE11,
311 ATTRIBUTE12,
312 ATTRIBUTE13,
313 ATTRIBUTE14,
314 ATTRIBUTE15,
315 ATTRIBUTE_CATEGORY,
316 PROGRAM_APPLICATION_ID,
317 PROGRAM_ID,
318 Program_Login_Id,
319 OBJECT_VERSION_NUMBER
320 from ZX_FC_TYPES_B
321 where CLASSIFICATION_TYPE_ID = X_CLASSIFICATION_TYPE_ID
322 for update of CLASSIFICATION_TYPE_ID nowait;
323 recinfo c%rowtype;
324 cursor c1 is select
325 CLASSIFICATION_TYPE_NAME,
326 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
327 from ZX_FC_TYPES_TL
328 where CLASSIFICATION_TYPE_ID = X_CLASSIFICATION_TYPE_ID
329 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
330 for update of CLASSIFICATION_TYPE_ID nowait;
331 begin
332 open c;
333 fetch c into recinfo;
334 if (c%notfound) then
335 close c;
336 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
337 app_exception.raise_exception;
338 end if;
339 close c;
340 if ( ((recinfo.CLASSIFICATION_TYPE_CODE = X_CLASSIFICATION_TYPE_CODE)
341 OR ((recinfo.CLASSIFICATION_TYPE_CODE is null) AND (X_CLASSIFICATION_TYPE_CODE is null)))
342 AND (recinfo.Classification_Type_Categ_Code = X_Classification_Type_Categ_Co)
343 AND ((recinfo.CLASSIFICATION_TYPE_GROUP_CODE = X_CLASSIFICATION_TYPE_GROUP_CO)
344 OR ((recinfo.CLASSIFICATION_TYPE_GROUP_CODE is null) AND (X_CLASSIFICATION_TYPE_GROUP_CO is null)))
345 AND ((recinfo.DELIMITER = X_DELIMITER)
346 OR ((recinfo.DELIMITER is null) AND (X_DELIMITER is null)))
347 AND ((recinfo.Owner_Table_Code = X_Owner_Table_Code)
348 OR ((recinfo.Owner_Table_Code is null) AND (X_Owner_Table_Code is null)))
349 AND ((recinfo.OWNER_ID_NUM = X_OWNER_ID_NUM)
350 OR ((recinfo.OWNER_ID_NUM is null) AND (X_OWNER_ID_NUM is null)))
351 AND ((recinfo.OWNER_ID_CHAR = X_OWNER_ID_CHAR)
352 OR ((recinfo.OWNER_ID_CHAR is null) AND (X_OWNER_ID_CHAR is null)))
353 AND ((recinfo.EFFECTIVE_FROM = X_EFFECTIVE_FROM)
354 OR ((recinfo.EFFECTIVE_FROM is null) AND (X_EFFECTIVE_FROM is null)))
355 AND ((recinfo.EFFECTIVE_TO = X_EFFECTIVE_TO)
356 OR ((recinfo.EFFECTIVE_TO is null) AND (X_EFFECTIVE_TO is null)))
357 AND ((recinfo.START_POSITION = X_START_POSITION)
358 OR ((recinfo.START_POSITION is null) AND (X_START_POSITION is null)))
359 AND ((recinfo.NUM_CHARACTERS = X_NUM_CHARACTERS)
360 OR ((recinfo.NUM_CHARACTERS is null) AND (X_NUM_CHARACTERS is null)))
361 AND ((recinfo.Classification_Type_Level_Code = X_CLASSIFICATION_TYPE_LEVEL_CO)
362 OR ((recinfo.Classification_Type_Level_Code is null) AND (X_CLASSIFICATION_TYPE_LEVEL_CO is null)))
363 AND ((recinfo.REQUEST_ID = X_REQUEST_ID)
364 OR ((recinfo.REQUEST_ID is null) AND (X_REQUEST_ID is null)))
365 AND ((recinfo.Record_Type_Code = X_Record_Type_Code)
366 OR ((recinfo.Record_Type_Code is null) AND (X_Record_Type_Code is null)))
367 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
368 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
369 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
370 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
371 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
372 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
373 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
374 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
375 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
376 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
377 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
378 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
379 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
380 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
381 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
382 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
383 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
384 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
385 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
386 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
387 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
388 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
389 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
390 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
391 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
392 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
393 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
394 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
395 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
396 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
397 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
398 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
399 AND ((recinfo.PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID)
400 OR ((recinfo.PROGRAM_APPLICATION_ID is null) AND (X_PROGRAM_APPLICATION_ID is null)))
401 AND ((recinfo.PROGRAM_ID = X_PROGRAM_ID)
402 OR ((recinfo.PROGRAM_ID is null) AND (X_PROGRAM_ID is null)))
403 AND ((recinfo.Program_Login_Id = X_Program_Login_Id)
404 OR ((recinfo.Program_Login_Id is null) AND (X_Program_Login_Id is null)))
405 AND (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
406 ) then
407 null;
408 else
409 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
410 app_exception.raise_exception;
411 end if;
412 for tlinfo in c1 loop
413 if (tlinfo.BASELANG = 'Y') then
414 if ( ((tlinfo.CLASSIFICATION_TYPE_NAME = X_CLASSIFICATION_TYPE_NAME)
415 OR ((tlinfo.CLASSIFICATION_TYPE_NAME is null) AND (X_CLASSIFICATION_TYPE_NAME is null)))
416 ) then
417 null;
418 else
419 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
420 app_exception.raise_exception;
421 end if;
422 end if;
423 end loop;
424 return;
425 end LOCK_ROW;
426
427 procedure UPDATE_ROW (
428 X_CLASSIFICATION_TYPE_ID in NUMBER,
429 X_CLASSIFICATION_TYPE_CODE in VARCHAR2,
430 X_Classification_Type_Categ_Co in VARCHAR2,
431 X_CLASSIFICATION_TYPE_GROUP_CO in VARCHAR2,
432 X_DELIMITER in VARCHAR2,
433 X_Owner_Table_Code in VARCHAR2,
434 X_OWNER_ID_NUM in NUMBER,
438 X_START_POSITION in NUMBER,
435 X_OWNER_ID_CHAR in VARCHAR2,
436 X_EFFECTIVE_FROM in DATE,
437 X_EFFECTIVE_TO in DATE,
439 X_NUM_CHARACTERS in NUMBER,
440 X_CLASSIFICATION_TYPE_LEVEL_CO in VARCHAR2,
441 X_REQUEST_ID in NUMBER,
442 X_Record_Type_Code in VARCHAR2,
443 X_ATTRIBUTE1 in VARCHAR2,
444 X_ATTRIBUTE2 in VARCHAR2,
445 X_ATTRIBUTE3 in VARCHAR2,
446 X_ATTRIBUTE4 in VARCHAR2,
447 X_ATTRIBUTE5 in VARCHAR2,
448 X_ATTRIBUTE6 in VARCHAR2,
449 X_ATTRIBUTE7 in VARCHAR2,
450 X_ATTRIBUTE8 in VARCHAR2,
451 X_ATTRIBUTE9 in VARCHAR2,
452 X_ATTRIBUTE10 in VARCHAR2,
453 X_ATTRIBUTE11 in VARCHAR2,
454 X_ATTRIBUTE12 in VARCHAR2,
455 X_ATTRIBUTE13 in VARCHAR2,
456 X_ATTRIBUTE14 in VARCHAR2,
457 X_ATTRIBUTE15 in VARCHAR2,
458 X_ATTRIBUTE_CATEGORY in VARCHAR2,
459 X_CLASSIFICATION_TYPE_NAME in VARCHAR2,
460 X_LAST_UPDATE_DATE in DATE,
461 X_LAST_UPDATED_BY in NUMBER,
462 X_LAST_UPDATE_LOGIN in NUMBER,
463 X_PROGRAM_APPLICATION_ID in NUMBER,
464 X_PROGRAM_ID in NUMBER,
465 X_Program_Login_Id in NUMBER,
466 X_OBJECT_VERSION_NUMBER in NUMBER
467 ) is
468 l_det_factor_id NUMBER;
469 begin
470
471 update ZX_FC_TYPES_B set
472 CLASSIFICATION_TYPE_CODE = X_CLASSIFICATION_TYPE_CODE,
473 Classification_Type_Categ_Code = X_Classification_Type_Categ_Co,
474 CLASSIFICATION_TYPE_GROUP_CODE = X_CLASSIFICATION_TYPE_GROUP_CO,
475 DELIMITER = X_DELIMITER,
476 Owner_Table_Code = X_Owner_Table_Code,
477 OWNER_ID_NUM = X_OWNER_ID_NUM,
478 OWNER_ID_CHAR = X_OWNER_ID_CHAR,
479 EFFECTIVE_FROM = X_EFFECTIVE_FROM,
480 EFFECTIVE_TO = X_EFFECTIVE_TO,
481 START_POSITION = X_START_POSITION,
482 NUM_CHARACTERS = X_NUM_CHARACTERS,
483 Classification_Type_Level_Code = X_CLASSIFICATION_TYPE_LEVEL_CO,
484 REQUEST_ID = X_REQUEST_ID,
485 Record_Type_Code = X_Record_Type_Code,
486 ATTRIBUTE1 = X_ATTRIBUTE1,
487 ATTRIBUTE2 = X_ATTRIBUTE2,
488 ATTRIBUTE3 = X_ATTRIBUTE3,
489 ATTRIBUTE4 = X_ATTRIBUTE4,
490 ATTRIBUTE5 = X_ATTRIBUTE5,
491 ATTRIBUTE6 = X_ATTRIBUTE6,
492 ATTRIBUTE7 = X_ATTRIBUTE7,
493 ATTRIBUTE8 = X_ATTRIBUTE8,
494 ATTRIBUTE9 = X_ATTRIBUTE9,
495 ATTRIBUTE10 = X_ATTRIBUTE10,
496 ATTRIBUTE11 = X_ATTRIBUTE11,
497 ATTRIBUTE12 = X_ATTRIBUTE12,
498 ATTRIBUTE13 = X_ATTRIBUTE13,
499 ATTRIBUTE14 = X_ATTRIBUTE14,
500 ATTRIBUTE15 = X_ATTRIBUTE15,
501 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
502 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
503 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
504 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
505 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
506 PROGRAM_ID = X_PROGRAM_ID,
507 Program_Login_Id = X_Program_Login_Id,
508 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER
512 end if;
509 where CLASSIFICATION_TYPE_ID = X_CLASSIFICATION_TYPE_ID;
510 if (sql%notfound) then
511 raise no_data_found;
513 update ZX_FC_TYPES_TL set
514 CLASSIFICATION_TYPE_NAME = X_CLASSIFICATION_TYPE_NAME,
515 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
516 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
517 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
518 SOURCE_LANG = userenv('LANG')
519 where CLASSIFICATION_TYPE_ID = X_CLASSIFICATION_TYPE_ID
520 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
521 if (sql%notfound) then
522 raise no_data_found;
523 end if;
524
525 /* No need to update the determing factor name for seeded data and FC Group Code.
526 Since Classification Type Name and FC Group Code is non updatable field in the UI.*/
527
528 if( X_Record_Type_Code <> 'SEEDED' ) then
529
530 SELECT DETERMINING_FACTOR_ID INTO l_det_factor_id FROM ZX_DETERMINING_FACTORS_B
531 WHERE DETERMINING_FACTOR_CODE = X_CLASSIFICATION_TYPE_CODE AND Tax_Rules_Flag = 'Y';
532
533 ZX_DETERMINING_FACTORS_PKG.UPDATE_ROW
534 (l_det_factor_id, --X_DETERMINING_FACTOR_ID
535 x_classification_type_code, --X_DETERMINING_FACTOR_CODE
536 x_classification_type_categ_co, --X_DETERMINING_FACTOR_CLASS_COD,
537 NULL, --X_VALUE_SET
538 NULL, --X_TAX_PARAMETER_CODE
539 'ALPHANUMERIC', --X_DATA_TYPE_CODE
540 NULL, --X_TAX_FUNCTION_CODE
541 x_record_type_code, --X_RECORD_TYPE_CODE
542 'N', --X_TAX_REGIME_DET_FLAG,
543 'Y', --X_TAX_SUMMARIZATION_FLAG,
544 'Y', --X_TAX_RULES_FLAG,
545 'N', --X_TAXABLE_BASIS_FLAG,
546 'N', --X_TAX_CALCULATION_FLAG,
547 'Y', --X_INTERNAL_FLAG,
548 'N', --X_RECORD_ONLY_FLAG,
549 X_REQUEST_ID, --X_REQUEST_ID
553 X_LAST_UPDATED_BY, --X_LAST_UPDATED_BY
550 X_CLASSIFICATION_TYPE_NAME, --X_DETERMINING_FACTOR_NAME
551 NULL, --X_DETERMINING_FACTOR_DESC
552 X_LAST_UPDATE_DATE, --X_LAST_UPDATE_DATE
554 X_LAST_UPDATE_LOGIN, --X_LAST_UPDATE_LOGIN
555 X_OBJECT_VERSION_NUMBER);
556
557 end if;
558
559 end UPDATE_ROW;
560
561 procedure DELETE_ROW (
562 X_CLASSIFICATION_TYPE_ID in NUMBER
563 ) is
564 begin
565 delete from ZX_FC_TYPES_TL
566 where CLASSIFICATION_TYPE_ID = X_CLASSIFICATION_TYPE_ID;
567 if (sql%notfound) then
568 raise no_data_found;
569 end if;
570 delete from ZX_FC_TYPES_B
571 where CLASSIFICATION_TYPE_ID = X_CLASSIFICATION_TYPE_ID;
572 if (sql%notfound) then
573 raise no_data_found;
574 end if;
575 end DELETE_ROW;
576
577 procedure ADD_LANGUAGE
578 is
579 begin
580 delete from ZX_FC_TYPES_TL T
581 where not exists
582 (select NULL
583 from ZX_FC_TYPES_B B
584 where B.CLASSIFICATION_TYPE_ID = T.CLASSIFICATION_TYPE_ID
585 );
586 update ZX_FC_TYPES_TL T set (
587 CLASSIFICATION_TYPE_NAME
588 ) = (select
589 B.CLASSIFICATION_TYPE_NAME
590 from ZX_FC_TYPES_TL B
591 where B.CLASSIFICATION_TYPE_ID = T.CLASSIFICATION_TYPE_ID
592 and B.LANGUAGE = T.SOURCE_LANG)
593 where (
594 T.CLASSIFICATION_TYPE_ID,
595 T.LANGUAGE
596 ) in (select
597 SUBT.CLASSIFICATION_TYPE_ID,
598 SUBT.LANGUAGE
599 from ZX_FC_TYPES_TL SUBB, ZX_FC_TYPES_TL SUBT
600 where SUBB.CLASSIFICATION_TYPE_ID = SUBT.CLASSIFICATION_TYPE_ID
601 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
602 and (SUBB.CLASSIFICATION_TYPE_NAME <> SUBT.CLASSIFICATION_TYPE_NAME
603 or (SUBB.CLASSIFICATION_TYPE_NAME is null and SUBT.CLASSIFICATION_TYPE_NAME is not null)
604 or (SUBB.CLASSIFICATION_TYPE_NAME is not null and SUBT.CLASSIFICATION_TYPE_NAME is null)
605 ));
606 insert into ZX_FC_TYPES_TL (
607 CLASSIFICATION_TYPE_ID,
608 CLASSIFICATION_TYPE_NAME,
609 CREATED_BY,
610 CREATION_DATE,
611 LAST_UPDATED_BY,
612 LAST_UPDATE_DATE,
613 LAST_UPDATE_LOGIN,
614 LANGUAGE,
615 SOURCE_LANG
616 ) select
617 B.CLASSIFICATION_TYPE_ID,
618 B.CLASSIFICATION_TYPE_NAME,
619 B.CREATED_BY,
620 B.CREATION_DATE,
621 B.LAST_UPDATED_BY,
622 B.LAST_UPDATE_DATE,
623 B.LAST_UPDATE_LOGIN,
624 L.LANGUAGE_CODE,
625 B.SOURCE_LANG
626 from ZX_FC_TYPES_TL B, FND_LANGUAGES L
627 where L.INSTALLED_FLAG in ('I', 'B')
628 and B.LANGUAGE = userenv('LANG')
629 and not exists
630 (select NULL
631 from ZX_FC_TYPES_TL T
632 where T.CLASSIFICATION_TYPE_ID = B.CLASSIFICATION_TYPE_ID
633 and T.LANGUAGE = L.LANGUAGE_CODE);
634 end ADD_LANGUAGE;
635
636 end ZX_FC_TYPES_PKG;
[Home] [Help]