[Home] [Help]
PACKAGE BODY: APPS.JAI_DF_DROP
Source
1 PACKAGE BODY jai_df_drop AS
2 /* $Header: jai_df_drop.plb 120.0 2006/10/06 15:40:04 rallamse noship $ */
3
4 procedure remove_context ( errbuf OUT NOCOPY VARCHAR2 ,
5 retcode OUT NOCOPY VARCHAR2
6 ) as
7
8 lv_appl_short_name varchar2(10);
9 lv_flexfield_name varchar2(30);
10
11 ln_application_id number;
12 lv_value_set_name fnd_flex_value_sets.flex_value_set_name%type;
13
14 cursor cur_segment(cpn_application_id number, cpv_flex_name varchar2, cpv_context varchar2) is
15 select
16 end_user_column_name,
17 flex_value_set_id
18 from fnd_descr_flex_column_usages cu
19 where cu.descriptive_flexfield_name = cpv_flex_name
20 and cu.descriptive_flex_context_code = cpv_context
21 and application_id = cpn_application_id;
22
23 cursor cur_value_set(cpn_value_set_id number) is
24 select flex_value_set_name
25 from fnd_flex_value_sets
26 where flex_value_set_id = cpn_value_set_id;
27
28 cursor cur_appl_id(cpv_app_short_name varchar2) is
29 select application_id
30 from fnd_application
31 where application_short_name = cpv_app_short_name;
32
33 /* Added cpn_application_id parameter for bug 4924146 */
34 cursor c_get_dff_details (cpn_application_id number, p_segment_name varchar2) is
35 select descriptive_flex_context_code,
36 application_column_name,
37 flex_value_set_id
38 from fnd_descr_flex_column_usages
39 where application_id = cpn_application_id
40 and descriptive_flexfield_name = 'AP_INVOICES'
41 and end_user_column_name = p_segment_name;
42
43 lv_context fnd_descr_flex_column_usages.descriptive_flex_context_code%type;
44 lv_attribute_vat_invoice fnd_descr_flex_column_usages.application_column_name%type;
45 lv_attribute_vat_date fnd_descr_flex_column_usages.application_column_name%type;
46 lv_attribute_vat_receipt_date fnd_descr_flex_column_usages.application_column_name%type;
47 lv_sql_string varchar2(2000);
48 ln_value_set_inv_id number;
49 ln_value_set_date_id number;
50 ln_value_set_rdate_id number;
51 lv_value_set_inv_name fnd_flex_value_sets.flex_value_set_name%type;
52 lv_value_set_date_name fnd_flex_value_sets.flex_value_set_name%type;
53 lv_value_set_rdate_name fnd_flex_value_sets.flex_value_set_name%type;
54 lv_flag VARCHAR2(1);
55
56 --Function added by Sanjikum, Bug#5443693
57 FUNCTION check_before_delete_dff(pv_flexfield_name VARCHAR2,
58 pv_context VARCHAR2)
59 RETURN VARCHAR2
60 IS
61 CURSOR c_flex
62 IS
63 SELECT application_table_name,
64 context_column_name
65 FROM fnd_descriptive_flexs
66 WHERE descriptive_flexfield_name = pv_flexfield_name;
67
68 r_flex c_flex%ROWTYPE;
69 lv_sql VARCHAR2(2000);
70 lv_flag VARCHAR2(1);
71
72 BEGIN
73 OPEN c_flex;
74 FETCH c_flex INTO r_flex;
75 CLOSE c_flex;
76
77 lv_sql := 'SELECT /*+ parallel(e) */ ''1'' from '||r_flex.application_table_name||'e WHERE '||r_flex.context_column_name||' = :a AND rownum < 2' ;
78
79 BEGIN
80 EXECUTE IMMEDIATE lv_sql INTO lv_flag USING pv_context;
81 EXCEPTION
82 WHEN NO_DATA_FOUND THEN
83 lv_flag := '0';
84 END;
85
86 RETURN NVL(lv_flag,'0');
87
88 EXCEPTION
89 WHEN OTHERS THEN
90 RETURN '1';
91 END check_before_delete_dff;
92
93 procedure delete_context is
94 lv_flag VARCHAR2(1);
95 begin
96 --Start of addition by Sanjikum, Bug#5443693
97 lv_flag := check_before_delete_dff(pv_flexfield_name => lv_flexfield_name,
98 pv_context => lv_context);
99 IF lv_flag = '1' THEN
100 RETURN;
101 END IF;
102 --End of addition by Sanjikum, Bug#5443693
103
104
105 /* Delete ValueSets */
106 for rec_segment in cur_segment(ln_application_id, lv_flexfield_name, lv_context) loop
107
108 fnd_flex_dsc_api.delete_segment
109 (
110 appl_short_name => lv_appl_short_name,
111 flexfield_name => lv_flexfield_name ,
112 context => lv_context ,
113 segment => rec_segment.end_user_column_name
114 );
115
116 if rec_segment.flex_value_set_id is not null then
117
118 open cur_value_set(rec_segment.flex_value_set_id);
119 fetch cur_value_set into lv_value_set_name;
120 close cur_value_set;
121
122 fnd_flex_val_api.delete_valueset(lv_value_set_name);
123 end if;
124
125 end loop;
126
127 /* Delete Context */
128 fnd_flex_dsc_api.delete_context (
129 appl_short_name => lv_appl_short_name ,
130 flexfield_name => lv_flexfield_name ,
131 context => lv_context
132 );
133
134 end delete_context;
135
136 BEGIN
137
138 retcode := 0 ; /* rallamse setting retcode = 0 */
139
140 /* DFF # 1 : FA_ADDITIONS DFF */
141 begin
142 lv_appl_short_name := 'OFA';
143 lv_flexfield_name := 'FA_ADDITIONS';
144 lv_context := 'India B Of Assets';
145 ln_application_id := null;
146
147 open cur_appl_id(lv_appl_short_name);
148 fetch cur_appl_id into ln_application_id;
149 close cur_appl_id;
150
151 delete_context;
152 EXCEPTION
153 WHEN OTHERS THEN
154 null;
155 end;
156
157 commit;
158
159 /* DFF # 2 : FA_MASS_ADDITIONS DFF */
160 begin
161 lv_appl_short_name := 'OFA';
162 lv_flexfield_name := 'FA_MASS_ADDITIONS';
163 lv_context := 'India B Of Assets';
164 ln_application_id := null;
165
166 open cur_appl_id(lv_appl_short_name);
167 fetch cur_appl_id into ln_application_id;
168 close cur_appl_id;
169
170 delete_context;
171 exception
172 when others then
173 null;
174 end;
175
176 /* DFF # 3 : MTL_MATERIAL_TRANSACTIONS DFF */
177 begin
178 lv_appl_short_name := 'INV';
179 lv_flexfield_name := 'MTL_MATERIAL_TRANSACTIONS';
180 lv_context := 'India Other Transaction';
181 ln_application_id := null;
182
183 open cur_appl_id(lv_appl_short_name);
184 fetch cur_appl_id into ln_application_id;
185 close cur_appl_id;
186
187 delete_context;
188 exception
189 when others then
190 null;
191 end;
192
193 /* DFF # 4 : RCV_SHIPMENT_HEADERS - India Receipt DFF */
194 begin
195 lv_appl_short_name := 'PO';
196 lv_flexfield_name := 'RCV_SHIPMENT_HEADERS';
197 lv_context := 'India Receipt';
198 ln_application_id := null;
199
200 open cur_appl_id(lv_appl_short_name);
201 fetch cur_appl_id into ln_application_id;
202 close cur_appl_id;
203
204 delete_context;
205 exception
206 when others then
207 null;
208 end;
209
210 /* DFF # 5 : AP_INVOICES - India Original Invoice for TDS DFF */
211 begin
212 lv_appl_short_name := 'SQLAP';
213 lv_flexfield_name := 'AP_INVOICES';
214 lv_context := 'India Original Invoice for TDS';
215 ln_application_id := null;
216
217 open cur_appl_id(lv_appl_short_name);
218 fetch cur_appl_id into ln_application_id;
219 close cur_appl_id;
220
221 delete_context;
222 exception
223 when others then
224 null;
225 end;
226
227 /* DFF # 6 : AP_INVOICE_DISTRIBUTIONS - India Distributions DFF */
228 begin
229 lv_appl_short_name := 'SQLAP';
230 lv_flexfield_name := 'AP_INVOICE_DISTRIBUTIONS';
231 lv_context := 'India Distributions';
232 ln_application_id := null;
233
234 open cur_appl_id(lv_appl_short_name);
235 fetch cur_appl_id into ln_application_id;
236 close cur_appl_id;
237
238 delete_context;
239 exception
240 when others then
241 null;
242 end;
243
244 /* DFF # 7 : AP_CHECKS - India Payment Information */
245 begin
246 lv_appl_short_name := 'SQLAP';
247 lv_flexfield_name := 'AP_CHECKS';
248 lv_context := 'India Payment Information';
249 ln_application_id := null;
250
251 open cur_appl_id(lv_appl_short_name);
252 fetch cur_appl_id into ln_application_id;
253 close cur_appl_id;
254
255 delete_context;
256 exception
257 when others then
258 null;
259 end;
260
261
262 /* DFF # 8 : PER_ORGANIZATION_UNITS - India Org Info */
263 begin
264 lv_appl_short_name := 'PER';
265 lv_flexfield_name := 'PER_ORGANIZATION_UNITS';
266 lv_context := 'India Org Info';
267 ln_application_id := null;
268
269 open cur_appl_id(lv_appl_short_name);
270 fetch cur_appl_id into ln_application_id;
271 close cur_appl_id;
272
273 delete_context;
274 exception
275 when others then
276 null;
277 end;
278
279 /* DFF # 9 : MTL_SYSTEM_ITEMS - India Items */
280 begin
281 lv_appl_short_name := 'INV';
282 lv_flexfield_name := 'MTL_SYSTEM_ITEMS';
283 lv_context := 'India Items';
284 ln_application_id := null;
285
286 open cur_appl_id(lv_appl_short_name);
287 fetch cur_appl_id into ln_application_id;
288 close cur_appl_id;
289
290 delete_context;
291 exception
292 when others then
293 null;
294 end;
295
296 /* DFF # 10 : RCV_TRANSACTIONS - India Return to Vendor */
297 begin
298 lv_appl_short_name := 'PO';
299 lv_flexfield_name := 'RCV_TRANSACTIONS';
300 lv_context := 'India Return to Vendor';
301 ln_application_id := null;
302
303 open cur_appl_id(lv_appl_short_name);
304 fetch cur_appl_id into ln_application_id;
305 close cur_appl_id;
306
307 delete_context;
308 exception
309 when others then
310 null;
311 end;
312
313 /* DFF # 11 : RCV_TRANSACTIONS - India RMA Receipt */
314 begin
315 lv_appl_short_name := 'PO';
316 lv_flexfield_name := 'RCV_TRANSACTIONS';
317 lv_context := 'India RMA Receipt';
318 ln_application_id := null;
319
320 open cur_appl_id(lv_appl_short_name);
321 fetch cur_appl_id into ln_application_id;
322 close cur_appl_id;
323
324 delete_context;
325 exception
326 when others then
327 null;
328 end;
329
330 /* DFF # 12 : RCV_TRANSACTIONS - India Return to Vendor */
331 begin
332 lv_appl_short_name := 'PO';
333 lv_flexfield_name := 'RCV_TRANSACTIONS';
334 lv_context := 'India Receipt';
335 ln_application_id := null;
336
337 open cur_appl_id(lv_appl_short_name);
338 fetch cur_appl_id into ln_application_id;
339 close cur_appl_id;
340
341 delete_context;
342 exception
343 when others then
344 null;
345 end;
346
347 /* DFF # 13 : RCV_SHIPMENT_HEADERS - India RMA Receipt DFF */
348 begin
349 lv_appl_short_name := 'PO';
350 lv_flexfield_name := 'RCV_SHIPMENT_HEADERS';
351 lv_context := 'India RMA Receipt';
352 ln_application_id := null;
353
354 open cur_appl_id(lv_appl_short_name);
355 fetch cur_appl_id into ln_application_id;
356 close cur_appl_id;
357
358 delete_context;
359 exception
360 when others then
361 null;
362 end;
363
364 /* DFF # 14 : FND_COMMON_LOOKUPS - India Lookup Codes */
365 begin
366 lv_appl_short_name := 'FND';
367 lv_flexfield_name := 'FND_COMMON_LOOKUPS';
368 lv_context := 'India Lookup Codes';
369 ln_application_id := null;
370
371 open cur_appl_id(lv_appl_short_name);
372 fetch cur_appl_id into ln_application_id;
373 close cur_appl_id;
374
375 delete_context;
376 exception
377 when others then
378 null;
379 end;
380
381
382 /* DFF # 15 : RA_INTERFACE_LINES - SUPPLEMENT CM' */
383 begin
384 lv_appl_short_name := 'AR';
385 lv_flexfield_name := 'RA_INTERFACE_LINES';
386 lv_context := 'SUPPLEMENT CM';
387 ln_application_id := null;
388
389 open cur_appl_id(lv_appl_short_name);
390 fetch cur_appl_id into ln_application_id;
391 close cur_appl_id;
392
393 delete_context;
394 exception
395 when others then
396 null;
397 end;
398
399 /* DFF # 16 : RA_INTERFACE_LINES - SUPPLEMENT DM */
400 begin
401 lv_appl_short_name := 'AR';
402 lv_flexfield_name := 'RA_INTERFACE_LINES';
403 lv_context := 'SUPPLEMENT DM';
404 ln_application_id := null;
405
406 open cur_appl_id(lv_appl_short_name);
407 fetch cur_appl_id into ln_application_id;
408 close cur_appl_id;
409
410 delete_context;
411 exception
412 when others then
413 null;
414 end;
415
416 /* DFF # 17 : RA_INTERFACE_LINES - SUPPLEMENT DM */
417 begin
418 lv_appl_short_name := 'AR';
419 lv_flexfield_name := 'RA_INTERFACE_LINES';
420 lv_context := 'SUPPLEMENT INVOICE';
421 ln_application_id := null;
422
423 open cur_appl_id(lv_appl_short_name);
424 fetch cur_appl_id into ln_application_id;
425 close cur_appl_id;
426
427 delete_context;
428 exception
429 when others then
430 null;
431 end;
432
433 /* DFF # 18 : RA_INTERFACE_LINES - TDS CREDIT */
434 begin
435 lv_appl_short_name := 'AR';
436 lv_flexfield_name := 'RA_INTERFACE_LINES';
437 lv_context := 'TDS CREDIT';
438 ln_application_id := null;
439
440 open cur_appl_id(lv_appl_short_name);
441 fetch cur_appl_id into ln_application_id;
442 close cur_appl_id;
443
444 delete_context;
445 exception
446 when others then
447 null;
448 end;
449
450 /* DFF # 19 : OE_LINE_ATTRIBUTES - Sales Order India*/
451 begin
452 lv_appl_short_name := 'ONT';
453 lv_flexfield_name := 'OE_LINE_ATTRIBUTES';
454 lv_context := 'Sales Order India';
455 ln_application_id := null;
456
457 open cur_appl_id(lv_appl_short_name);
458 fetch cur_appl_id into ln_application_id;
459 close cur_appl_id;
460
461 delete_context;
462 exception
463 when others then
464 null;
465 end;
466
467 /* DFF # 20 : OE_LINE_ATTRIBUTES - Invoice India */
468 begin
469 lv_appl_short_name := 'ONT';
470 lv_flexfield_name := 'OE_LINE_ATTRIBUTES';
471 lv_context := 'Invoice India';
472 ln_application_id := null;
473
474 open cur_appl_id(lv_appl_short_name);
475 fetch cur_appl_id into ln_application_id;
476 close cur_appl_id;
477
478 delete_context;
479 exception
480 when others then
481 null;
482 end;
483
484
488 lv_flexfield_name := 'OE_LINE_ATTRIBUTES';
485 /* DFF # 21 : OE_LINE_ATTRIBUTES - Sales Order India*/
486 begin
487 lv_appl_short_name := 'ONT';
489 lv_context := 'Customer PO India';
490 ln_application_id := null;
491
492 open cur_appl_id(lv_appl_short_name);
493 fetch cur_appl_id into ln_application_id;
494 close cur_appl_id;
495
496 delete_context;
497 exception
498 when others then
499 null;
500 end;
501
502 /* VAT- Dynamic Segments ..could be allocated to any context */
503 /* Added ln_application_id population code for bug 4924146 */
504 select application_id into ln_application_id
505 from fnd_application
506 where application_short_name = 'SQLAP';
507
508 open c_get_dff_details(ln_application_id, 'VAT Invoice Number'); /* Added appl_id for bug 4924146 */
509 fetch c_get_dff_details into lv_context, lv_attribute_vat_invoice,ln_value_set_inv_id;
510 close c_get_dff_details;
511
512 if ln_value_set_inv_id is not null then
513 open cur_value_set(ln_value_set_inv_id);
514 fetch cur_value_set into lv_value_set_inv_name;
515 close cur_value_set;
516 end if;
517
518 open c_get_dff_details(ln_application_id, 'VAT Invoice Date'); /* Added appl_id for bug 4924146 */
519 fetch c_get_dff_details into lv_context, lv_attribute_vat_date,ln_value_set_date_id;
520 close c_get_dff_details;
521
522 if ln_value_set_date_id is not null then
523 open cur_value_set(ln_value_set_date_id);
524 fetch cur_value_set into lv_value_set_date_name;
525 close cur_value_set;
526 end if;
527
528 open c_get_dff_details(ln_application_id, 'VAT invoice Receipt Date'); /* Added appl_id for bug 4924146 */
529 fetch c_get_dff_details into lv_context, lv_attribute_vat_receipt_date, ln_value_set_rdate_id;
530 close c_get_dff_details;
531
532 if ln_value_set_rdate_id is not null then
533 open cur_value_set(ln_value_set_rdate_id);
534 fetch cur_value_set into lv_value_set_rdate_name;
535 close cur_value_set;
536 end if;
537
538 --Start of addition by Sanjikum, Bug#5443693
539 lv_flag := check_before_delete_dff(pv_flexfield_name => 'AP_INVOICES',
540 pv_context => lv_context);
541 IF lv_flag = '1' THEN
542 goto end_of_proc;
543 END IF;
544 --End of addition by Sanjikum, Bug#5443693
545
546 if lv_attribute_vat_invoice is not null then
547
548 fnd_flex_dsc_api.delete_segment
549 (
550 appl_short_name => 'SQLAP',
551 flexfield_name => 'AP_INVOICES',
552 context => lv_context,
553 segment => lv_attribute_vat_invoice
554 );
555
556 if lv_value_set_inv_name is not null then
557 begin
558 fnd_flex_val_api.delete_valueset(lv_value_set_inv_name);
559 exception
560 when others then
561 null;
562 end;
563
564 end if;
565
566 end if;
567
568 if lv_attribute_vat_date is not null then
569
570 fnd_flex_dsc_api.delete_segment
571 (
572 appl_short_name => 'SQLAP',
573 flexfield_name => 'AP_INVOICES',
574 context => lv_context,
575 segment => lv_attribute_vat_date
576 );
577
578 if lv_value_set_date_name is not null then
579 begin
580 fnd_flex_val_api.delete_valueset(lv_value_set_date_name);
581 exception
582 when others then
583 null;
584 end;
585
586 end if;
587
588 end if;
589
590 if lv_attribute_vat_receipt_date is not null then
591 fnd_flex_dsc_api.delete_segment
592 (
593 appl_short_name => 'SQLAP',
594 flexfield_name => 'AP_INVOICES',
595 context => lv_context,
596 segment => lv_attribute_vat_receipt_date
597 );
598
599 if lv_value_set_rdate_name is not null then
600 begin
601 fnd_flex_val_api.delete_valueset(lv_value_set_rdate_name);
602 exception
603 when others then
604 null;
605 end;
606 end if;
607
608 end if;
609
610 if lv_context = 'India VAT' then
611 fnd_flex_dsc_api.delete_context
612 (
613 appl_short_name => 'SQLAP' ,
614 flexfield_name => 'AP_INVOICES' ,
615 context => 'India VAT'
616 );
617 end if;
618
619 <<END_OF_PROC>>
620 NULL;
621
622 EXCEPTION
623 WHEN others THEN
624 retcode := '2';
625 errbuf := substr(sqlerrm,1,1999);
626
627 END remove_context ;
628
629 END jai_df_drop ;