1 PACKAGE BODY AML_PURGE_IMPORT_INTERFACE as
2 /* $Header: amlsprgb.pls 115.7 2004/03/04 13:02:03 bmuthukr noship $ */
3 -- Start of Comments
4 -- Package name : AML_PURGE_IMPORT_INTERFACE
5 -- Purpose : Sales Leads Management
6 -- NOTE :
7 -- History :
8 -- 08/19/2003 BMUTHUKR Created
9 -- 14/11/2003 BMUTHUKR Modified. Now using bulk delete to improve the performance.
10 -- 01/20/2004 BMUTHUKR Implemented the requirements given in bug # 3354412.
11 -- Instead of No of days, records are deleted based on the date
12 -- range given.
13 -- 04/04/2004 BMUTHUKR Commented out the statement for deleting the PV_ENTITY_RULES_APPLIED table
14 -- As per bug 3481717.
15 -- END of Comments
16
17 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AML_PURGE_IMPORT_INTERFACE';
18 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amlsprgb.pls';
19 G_DEBUG_MODE VARCHAR2(1) := 'Y';
20 TYPE del_count IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
21 RECS_DELETED del_count;
22
23 PROCEDURE write_log(p_flag in number default 2,P_mesg in Varchar2)
24 IS
25 BEGIN
26 if nvl(g_debug_mode,'N') = 'Y' and p_flag = 2 then
27 fnd_file.put(1, substr(p_mesg,1,255));
28 fnd_file.new_line(1,1);
29 elsif p_flag = 1 then
30 fnd_file.put(1, substr(p_mesg,1,255));
31 fnd_file.new_line(1,1);
32 end if;
33 END write_log;
34
35 PROCEDURE initialize_count IS
36 BEGIN
37 Recs_deleted(1) := 0;
38 Recs_deleted(2) := 0;
39 Recs_deleted(3) := 0;
40 Recs_deleted(4) := 0;
41 Recs_deleted(5) := 0;
42 Recs_deleted(6) := 0;
43 -- Recs_deleted(7) := 0;
44 Recs_deleted(8) := 0;
45 END initialize_count;
46
47 PROCEDURE write_count IS
48 l_no_of_rows_deleted CONSTANT varchar2(100) := ' rows deleted from the table ';
49 BEGIN
50 write_log(1,Recs_deleted(1)||l_no_of_rows_deleted||'AS_LEAD_IMPORT_ERRORS');
51 write_log(1,Recs_deleted(2)||l_no_of_rows_deleted||'AS_IMP_SL_FLEX');
52 write_log(1,Recs_deleted(3)||l_no_of_rows_deleted||'AS_IMP_CNT_ROL_INTERFACE');
53 write_log(1,Recs_deleted(4)||l_no_of_rows_deleted||'AS_IMP_CNT_PNT_INTERFACE');
54 write_log(1,Recs_deleted(5)||l_no_of_rows_deleted||'AS_IMP_LINES_INTERFACE');
55 write_log(1,Recs_deleted(6)||l_no_of_rows_deleted||'AML_INTERACTION_LEADS');
56 -- write_log(1,Recs_deleted(7)||l_no_of_rows_deleted||'PV_ENTITY_RULES_APPLIED');
57 write_log(1,Recs_deleted(8)||l_no_of_rows_deleted||'AS_IMPORT_INTERFACE');
58 END write_count;
59
60 PROCEDURE Delete_Identified_Leads(P_Id_Tab IN AML_PURGE_IMPORT_INTERFACE.Import_Interface_Id_Tab) IS
61 BEGIN
62
63 -- Lead Import Error table
64 FORALL I IN P_Id_Tab.FIRST..P_Id_Tab.LAST
65 DELETE
66 FROM AS_LEAD_IMPORT_ERRORS
67 WHERE import_interface_id = P_Id_Tab(I);
68 Recs_deleted(1) := Recs_deleted(1) + SQL%ROWCOUNT;
69
70 -- Lead Import Flex table
71 FORALL I IN P_Id_Tab.FIRST..P_Id_Tab.LAST
72 DELETE
73 FROM AS_IMP_SL_FLEX
74 WHERE import_interface_id = P_Id_Tab(I);
75 Recs_deleted(2) := Recs_deleted(2) + SQL%ROWCOUNT;
76
77 -- Lead Import Contact Role table
78 FORALL I IN P_Id_Tab.FIRST..P_Id_Tab.LAST
79 DELETE
80 FROM AS_IMP_CNT_ROL_INTERFACE
81 WHERE import_interface_id = P_Id_Tab(I);
82 Recs_deleted(3) := Recs_deleted(3) + SQL%ROWCOUNT;
83
84 -- Lead Import Contact Point table
85 FORALL I IN P_Id_Tab.FIRST..P_Id_Tab.LAST
86 DELETE
87 FROM AS_IMP_CNT_PNT_INTERFACE
88 WHERE import_interface_id = P_Id_Tab(I);
89 Recs_deleted(4) := Recs_deleted(4) + SQL%ROWCOUNT;
90
91 -- Lead Import Lines table
92 FORALL I IN P_Id_Tab.FIRST..P_Id_Tab.LAST
93 DELETE
94 FROM AS_IMP_LINES_INTERFACE
95 WHERE import_interface_id = P_Id_Tab(I);
96 Recs_deleted(5) := Recs_deleted(5) + SQL%ROWCOUNT;
97
98 -- Interaction table
99 FORALL I IN P_Id_Tab.FIRST..P_Id_Tab.LAST
100 DELETE
101 FROM AML_INTERACTION_LEADS
102 WHERE import_interface_id = P_Id_Tab(I);
103 Recs_deleted(6) := Recs_deleted(6) + SQL%ROWCOUNT;
104
105 /* --PV Entity rules applied
106 FORALL I IN P_Id_Tab.FIRST..P_Id_Tab.LAST
107 DELETE
108 FROM PV_ENTITY_RULES_APPLIED
109 WHERE entity_id = P_Id_Tab(I)
110 AND entity = 'RESPONSE';
111 Recs_deleted(7) := Recs_deleted(7) + SQL%ROWCOUNT; */
112
113 -- Lead Import Base table
114 FORALL I IN P_Id_Tab.FIRST..P_Id_Tab.LAST
115 DELETE
116 FROM AS_IMPORT_INTERFACE
117 WHERE import_interface_id = P_Id_Tab(I);
118 Recs_deleted(8) := Recs_deleted(8) + SQL%ROWCOUNT;
119
120 END Delete_Identified_Leads;
121
122 PROCEDURE Purge_Import_Interface(
123 ERRBUF OUT NOCOPY VARCHAR2,
124 RETCODE OUT NOCOPY VARCHAR2,
125 P_START_DATE IN VARCHAR2,
126 P_END_DATE IN VARCHAR2,
127 P_STATUS IN VARCHAR2,
128 P_DEBUG_MODE IN VARCHAR2 DEFAULT 'N',
129 P_TRACE_MODE IN VARCHAR2 DEFAULT 'N'
130 )
131 IS
132 l_wrong_days CONSTANT varchar2(100) := 'ERROR: Could not purge. Number of days entered is not greater than zero';
133 l_no_of_rows_deleted CONSTANT varchar2(100) := ' rows deleted from the table ';
134 l_status boolean;
135 Id_Tab AML_PURGE_IMPORT_INTERFACE.Import_Interface_Id_Tab;
136 l_start_date date;
137 l_end_date date;
138
139 CURSOR Collect_Imp_Interface_Ids(l_start_date in date,l_end_date in date,p_status in varchar2) IS
140 SELECT import_interface_id
141 FROM AS_IMPORT_INTERFACE
142 WHERE TRUNC(creation_date) BETWEEN TRUNC(l_start_date) AND TRUNC(l_end_date)
143 AND load_status = P_STATUS;
144
145 BEGIN
146
147 l_start_date := to_date(p_start_date,'YYYY/MM/DD HH24:MI:SS');
148 l_end_date := to_date(p_end_date, 'YYYY/MM/DD HH24:MI:SS');
149
150 IF p_trace_mode = 'Y' THEN
151 dbms_session.set_sql_trace(TRUE);
152 ELSE
153 dbms_session.set_sql_trace(FALSE);
154 END IF;
155 G_DEBUG_MODE := P_DEBUG_MODE;
156
157 write_log(2,'Purging import interface starts');
158
159 initialize_count;
160 --Since the no of records deleted will be large, bulk delete is used here.
161 --Initially the import interface ids of the records that are to be deleted
162 --are taken first. These are taken in batches of 10000. These ids are passed
163 --to a procedure that deletes the records in import interface and its child
164 --tables.
165 OPEN Collect_Imp_Interface_Ids(l_start_date,l_end_date, P_status);
166 write_log(2,'Fetching data');
167 LOOP
168 IF Id_Tab.FIRST IS NOT NULL THEN
169 Id_Tab.DELETE;
170 END IF;
171 FETCH Collect_Imp_Interface_Ids BULK COLLECT INTO Id_Tab LIMIT 10000;
172 EXIT WHEN Collect_Imp_Interface_Ids%NOTFOUND; --If < 10000 records are remaining.
173 Delete_Identified_Leads(Id_Tab); --Pass the lead nos to the Purge_Qualified_Leads procedure to delete them.
174 END LOOP;
175 CLOSE Collect_Imp_Interface_Ids;
176
177 --The remaining records to be deleted.
178 SELECT import_interface_id BULK COLLECT
179 INTO Id_Tab
180 FROM AS_IMPORT_INTERFACE
181 WHERE TRUNC(creation_date) BETWEEN TRUNC(l_start_date) AND TRUNC(l_end_date)
182 AND load_status = P_STATUS;
183
184 IF Id_Tab.FIRST IS NOT NULL THEN --This should be executed only if atleast there is one record.
185 Delete_Identified_Leads(Id_Tab);
186 END IF;
187 COMMIT;
188 write_count;
189 write_log(2,'Purging import interface completed successfully');
190
191 EXCEPTION
192 WHEN FND_API.G_EXC_ERROR THEN
193 write_log(2,'Expected error');
194
195 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
196 write_log(2,'Unexpected error');
197
198 WHEN others THEN
199 write_log(2,'SQLCODE ' || to_char(SQLCODE) ||
200 ' SQLERRM ' || substr(SQLERRM, 1, 100));
201
202 errbuf := SQLERRM;
203 retcode := FND_API.G_RET_STS_UNEXP_ERROR;
204 l_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', SQLERRM);
205 END Purge_Import_Interface;
206
207 END AML_PURGE_IMPORT_INTERFACE;
[Home] [Help]