[Home] [Help]
PACKAGE BODY: APPS.IGS_UC_PURGE_INT_DATA
Source
1 PACKAGE BODY igs_uc_purge_int_data AS
2 /* $Header: IGSUC39B.pls 120.2 2006/08/21 03:52:55 jbaber noship $ */
3 PROCEDURE purge_data( errbuf OUT NOCOPY VARCHAR2,
4 retcode OUT NOCOPY NUMBER,
5 p_del_obsolete_data IN VARCHAR2,
6 p_del_proc_data IN VARCHAR2
7 ) IS
8 /*************************************************************
9 Created By : DSRIDHAR
10 Date Created On : 05-JUN-2003
11 Purpose : Created the Document w.r.t. MUS Build, Bug No: 2669208.
12 This process is created to delete all records from the Interface tables
13 which have been successfully processed and populated into the main UCAS
14 tables or have been obsoleted by a change in data coming from UCAS.
15
16 Know limitations, enhancements or remarks
17 Change History
18 Who When What
19 (reverse chronological order - newest change first)
20 anwest 18-JAN-2006 Bug# 4950285 R12 Disable OSS Mandate
21 dsridhar 14-JUL-2003 Changed package name to CAPS, changed concatenation
22 ***************************************************************/
23
24 TYPE InterfaceTableNames IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
25 InterfaceTables InterfaceTableNames;
26 l_index BINARY_INTEGER;
27 l_statement VARCHAR2(3000);
28 l_where VARCHAR2(100);
29
30 BEGIN
31
32 --anwest 18-JAN-2006 Bug# 4950285 R12 Disable OSS Mandate
33 IGS_GE_GEN_003.SET_ORG_ID;
34
35 --Checking if both the parameters p_del_obsolete_data and p_del_proc_data are 'NO'
36 --If both are 'NO' then logging a message "Both parameters cannot be NO"
37 IF p_del_obsolete_data = 'N' AND p_del_proc_data = 'N' THEN
38
39 fnd_message.set_name('IGS','IGS_UC_BOTH_CANT_BE_NO');
40 fnd_file.put_line(fnd_file.log, fnd_message.get);
41 retcode := 2;
42 RETURN;
43
44 END IF;
45
46 --Set the where clause for delete
47 IF p_del_obsolete_data = 'Y' AND p_del_proc_data = 'Y' THEN
48 l_where := ' WHERE record_status IN (''O'', ''D'') ';
49 ELSIF p_del_obsolete_data = 'Y' AND p_del_proc_data = 'N' THEN
50 l_where := ' WHERE record_status = ''O'' ';
51 ELSIF p_del_obsolete_data = 'N' AND p_del_proc_data = 'Y' THEN
52 l_where := ' WHERE record_status = ''D'' ';
53 END IF;
54
55 --Initialising the TABLE InterfaceTables with the Interface Table Names
56 InterfaceTables(1) := 'IGS_UC_CCRSE_INTS';
57 InterfaceTables(2) := 'IGS_UC_CEBLSBJ_INTS';
58 InterfaceTables(3) := 'IGS_UC_CINST_INTS';
59 InterfaceTables(4) := 'IGS_UC_CJNTADM_INTS';
60 InterfaceTables(5) := 'IGS_UC_CCONTRL_INTS';
61 InterfaceTables(6) := 'IGS_UC_CRAPR_INTS';
62 InterfaceTables(7) := 'IGS_UC_CRAWDBD_INTS';
63 InterfaceTables(8) := 'IGS_UC_CRFCODE_INTS';
64 InterfaceTables(9) := 'IGS_UC_CRKYWD_INTS';
65 InterfaceTables(10) := 'IGS_UC_CROFFAB_INTS';
66 InterfaceTables(11) := 'IGS_UC_CREFPOC_INTS';
67 InterfaceTables(12) := 'IGS_UC_CRPREPO_INTS';
68 InterfaceTables(13) := 'IGS_UC_CRSUBJ_INTS';
69 InterfaceTables(14) := 'IGS_UC_CTARIFF_INTS';
70 InterfaceTables(15) := 'IGS_UC_CSCHCNT_INTS';
71 InterfaceTables(16) := 'IGS_UC_CVSCH_INTS';
72 InterfaceTables(17) := 'IGS_UC_IOFFER_INTS';
73 InterfaceTables(18) := 'IGS_UC_IQUAL_INTS';
74 InterfaceTables(19) := 'IGS_UC_ISTARA_INTS';
75 InterfaceTables(20) := 'IGS_UC_ISTARC_INTS';
76 InterfaceTables(21) := 'IGS_UC_ISTARG_INTS';
77 InterfaceTables(22) := 'IGS_UC_ISTARH_INTS';
78 InterfaceTables(23) := 'IGS_UC_ISTARK_INTS';
79 InterfaceTables(24) := 'IGS_UC_ISTARN_INTS';
80 InterfaceTables(25) := 'IGS_UC_ISTRPQR_INTS';
81 InterfaceTables(26) := 'IGS_UC_ISTART_INTS';
82 InterfaceTables(27) := 'IGS_UC_ISTARW_INTS';
83 InterfaceTables(28) := 'IGS_UC_ISTARX_INTS';
84 InterfaceTables(29) := 'IGS_UC_ISTARZ1_INTS';
85 InterfaceTables(30) := 'IGS_UC_ISTARZ2_INTS';
86 InterfaceTables(31) := 'IGS_UC_ISTMNT_INTS';
87 InterfaceTables(32) := 'IGS_UC_UCNTACT_INTS';
88 InterfaceTables(33) := 'IGS_UC_UCNTGRP_INTS';
89 InterfaceTables(34) := 'IGS_UC_UCRSKWD_INTS';
90 InterfaceTables(35) := 'IGS_UC_UCRSVAC_INTS';
91 InterfaceTables(36) := 'IGS_UC_UCRSVOP_INTS';
92 InterfaceTables(37) := 'IGS_UC_UCRSE_INTS';
93 InterfaceTables(38) := 'IGS_UC_UINST_INTS';
94 InterfaceTables(39) := 'IGS_UC_UOFABRV_INTS';
95 InterfaceTables(40) := 'IGS_UC_IREFRNC_INTS';
96 InterfaceTables(41) := 'IGS_UC_IFRMQUL_INTS';
97 InterfaceTables(42) := 'IGS_UC_ISTARJ_INTS';
98 InterfaceTables(43) := 'IGS_UC_COUNTRY_INTS';
99
100 FOR j IN InterfaceTables.First..InterfaceTables.Last LOOP
101
102 --Delete statement to delete obsolete and processed data
103 IF p_del_obsolete_data = 'Y' OR p_del_proc_data = 'Y' THEN
104
105 l_statement := 'DECLARE
106 BEGIN
107 DELETE FROM ' || InterfaceTables(j) || l_where || ' ; ' || '
108 IF SQL%ROWCOUNT > 0 THEN
109 fnd_message.set_name(''IGS'',''IGS_UC_DEL_OBSOL_INT_DATA'');
110 fnd_message.set_token(''TNAME'',''' || InterfaceTables(j) || ''');
111 fnd_file.put_line(fnd_file.log, fnd_message.get);
112 END IF;
113 EXCEPTION
114 WHEN OTHERS THEN
115 ROLLBACK;
116 END; ';
117
118 EXECUTE IMMEDIATE l_statement;
119
120 END IF;
121 END LOOP;
122
123 EXCEPTION
124 WHEN OTHERS THEN
125 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
126 fnd_message.set_token('NAME','IGS_UC_PURGE_INT_DATA.purge_data');
127 fnd_file.put_line(fnd_file.log, fnd_message.get);
128 errbuf := fnd_message.get ;
129 retcode := 2;
130
131 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
132
133 END purge_data;
134
135 END igs_uc_purge_int_data;