[Home] [Help]
PACKAGE BODY: APPS.AMS_ADI_COMMON_PVT
Source
1 PACKAGE BODY AMS_ADI_COMMON_PVT AS
2 /* $Header: amsvadcb.pls 120.0 2005/07/01 04:00:07 appldev noship $ */
3
4
5 -- package level count for success rows
6 g_success_row_count PLS_INTEGER := 0;
7
8 -- package level count for error rows
9 g_error_row_count PLS_INTEGER := 0;
10
11 TYPE ams_object_names_t IS TABLE OF VARCHAR2(240);
12 TYPE ams_error_messages_t IS TABLE OF VARCHAR2(4000);
13
14
15 AMS_DEBUG_HIGH_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
16
17
18 --========================================================================
19 -- PROCEDURE
20 -- initializes for all operations
21 -- Purpose
22 --
23 -- HISTORY
24 --
25 --========================================================================
26 PROCEDURE init
27 IS
28 BEGIN
29 -- initalizes error rec collections
30 g_success_row_count := 0;
31 g_error_row_count := 0;
32 END init;
33
34
35 --========================================================================
36 -- PROCEDURE
37 -- initializes for batch operations
38 -- Purpose
39 -- initializes error structure table
40 -- HISTORY
41 --
42 --========================================================================
43 PROCEDURE init_for_batch(
44 p_error_records IN OUT NOCOPY ams_adi_error_rec_t
45 )
46 IS
47 BEGIN
48 -- initializes error rec collections
49 IF p_error_records IS NOT NULL THEN
50 p_error_records.TRIM(p_error_records.COUNT);
51 END IF;
52 END init_for_batch;
53
54
55 --========================================================================
56 -- PROCEDURE
57 -- handles successful API call for a row during Web ADI ->
58 -- Marketing integration call
59 -- Purpose
60 -- COMMIT successful row in database
61 -- HISTORY
62 --
63 --========================================================================
64 PROCEDURE handle_success_row(
65 p_commit IN VARCHAR2 := FND_API.G_TRUE
66 )
67 IS
68 BEGIN
69 IF(p_commit = FND_API.G_TRUE) THEN
70 COMMIT; --commits the current row processed
71 END IF;
72 g_success_row_count := g_success_row_count+1;
73 END handle_success_row;
74
75
76 --========================================================================
77 -- PROCEDURE
78 -- handles ERROR in API call for a row during Web ADI ->
79 -- Marketing integration call
80 -- Purpose
81 -- does the following things
82 -- 1. rollbacks current transaction
83 -- 2. write conc log
84 -- 3. adds error record in p_error_records
85 -- HISTORY
86 --
87 --========================================================================
88 PROCEDURE handle_error_row(
89 p_write_conc_log IN VARCHAR2 := FND_API.G_TRUE,
90 p_rollback IN VARCHAR2 := FND_API.G_TRUE,
91 p_error_code IN VARCHAR2 := NULL,
92 p_error_message IN VARCHAR2 := NULL,
93 p_object_id IN NUMBER,
94 p_object_name IN VARCHAR2,
95 p_parent_object_id IN NUMBER,
96 p_error_records IN OUT NOCOPY ams_adi_error_rec_t
97 )
98 IS
99 l_count PLS_INTEGER;
100 l_cum_length PLS_INTEGER;
101 l_cur_length PLS_INTEGER;
102 l_error_rec ams_adi_error_rec;
103 l_error_message VARCHAR2(4000) := ' ';
104 l_temp_message VARCHAR2(4000);
105 BEGIN
106 IF (p_rollback = FND_API.G_TRUE) THEN
107 ROLLBACK;
108 END IF;
109
110 IF(p_write_conc_log = FND_API.G_TRUE) THEN
111 AMS_Utility_PVT.Write_Conc_Log();
112 END IF;
113
114 g_error_row_count := g_error_row_count+1;
115
116 IF (p_error_code IS NOT NULL OR p_error_message IS NOT NULL) THEN
117 l_error_rec.error_code := p_error_code;
118 l_error_rec.error_message := p_error_message;
119 l_error_rec.object_id := p_object_id;
120 l_error_rec.object_name := p_object_name;
121 l_error_rec.parent_object_id:= p_parent_object_id;
122 ELSE
123
124 l_count := FND_MSG_PUB.count_msg;
125
126 FOR l_cnt IN 1..l_count
127 LOOP
128 IF(l_cnt > g_max_error_messages) THEN
129 EXIT; -- g_max_error_messages messages are enough, rest in concurrent log
130 END IF;
131
132 l_temp_message := FND_MSG_PUB.get(l_count-l_cnt+1, FND_API.g_false);
133
134 l_cum_length := LENGTH(l_error_message);
135 l_cur_length := LENGTH(l_temp_message)+10;
136
137 IF((4000 - l_cur_length) > l_cum_length) THEN
138 l_error_message := concat(concat(concat(l_error_message , '<br>'),l_temp_message),'</br> ');
139 ELSE
140 EXIT;
141 END IF;
142 END LOOP;
143
144 l_error_rec.error_code := p_error_code;
145 l_error_rec.error_message := l_error_message;
146 l_error_rec.object_id := p_object_id;
147 l_error_rec.object_name := p_object_name;
148 l_error_rec.parent_object_id:= p_parent_object_id;
149 END IF;
150
151 p_error_records.EXTEND(1);
152 p_error_records(p_error_records.COUNT) := l_error_rec;
153
154 END handle_error_row;
155
156
157
158 --========================================================================
159 -- PROCEDURE
160 -- handles FATAL ERROR in API call for a row during Web ADI ->
161 -- Marketing integration call
162 -- Purpose
163 -- ROLLBACK, log messages to conc log
164 -- HISTORY
165 --
166 --========================================================================
167 PROCEDURE handle_fatal_error(
168 p_write_conc_log IN VARCHAR2 := FND_API.G_TRUE,
169 p_rollback IN VARCHAR2 := FND_API.G_TRUE
170 )
171 IS
172 l_count PLS_INTEGER;
173 l_error_rec ams_adi_error_rec;
174 l_error_message VARCHAR2(4000);
175 BEGIN
176 IF (p_rollback = FND_API.G_TRUE) THEN
177 ROLLBACK;
178 END IF;
179
180 IF(p_write_conc_log = FND_API.G_TRUE) THEN
181 AMS_Utility_PVT.Write_Conc_Log();
182 END IF;
183 END handle_fatal_error;
184
185
186
187
188 --========================================================================
189 -- PROCEDURE
190 -- updates all staging table rows with error information accumulated so far
191 -- Purpose
192 -- does the following things
193 -- 1. updates error information in staging table using p_stmt (dynamic SQL using bind variables)
194 -- 2. commits(so that the staging table gets updated)
195 -- HISTORY
196 --
197 --========================================================================
198 PROCEDURE complete_batch(
199 p_update_table_name IN VARCHAR2,
200 p_upload_batch_id IN NUMBER,
201 p_use_object_id_as_pk IN VARCHAR2 := FND_API.G_TRUE,
202 p_commit IN VARCHAR2 := FND_API.G_TRUE,
203 p_error_records IN OUT NOCOPY ams_adi_error_rec_t
204 )
205 IS
206 l_count PLS_INTEGER := 0;
207 i PLS_INTEGER;
208 l_update_stmt VARCHAR2(4000);
209 l_error_messages ams_error_messages_t := ams_error_messages_t();
210 l_object_ids JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
211 l_object_names ams_object_names_t := ams_object_names_t();
212 l_parent_ids JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
213 BEGIN
214 IF(p_commit = FND_API.G_TRUE) THEN
215 COMMIT; --commits the current row processed
216 END IF;
217
218 IF (AMS_DEBUG_HIGH_ON) THEN
219 AMS_UTILITY_PVT.debug_message('Error records count '||p_error_records.COUNT);
220 END IF;
221
222 l_count := p_error_records.COUNT;
223
224 IF(p_use_object_id_as_pk = FND_API.G_TRUE) THEN
225 -- use object id as primary key
226
227 IF (AMS_DEBUG_HIGH_ON) THEN
228 AMS_UTILITY_PVT.debug_message('Using Id primary key');
229 END IF;
230
231 FOR i IN 1 .. l_count
232 LOOP
233 l_object_ids.EXTEND;
234 l_object_ids(i) := p_error_records(i).object_id;
235 l_error_messages.EXTEND;
236 l_error_messages(i) := p_error_records(i).error_message;
237
238 IF (AMS_DEBUG_HIGH_ON) THEN
239 AMS_UTILITY_PVT.debug_message('object id : '||p_error_records(i).object_id);
240 AMS_UTILITY_PVT.debug_message('error message : '||p_error_records(i).error_message);
241 END IF;
242
243 END LOOP;
244
245 IF (l_count > 0) THEN
246 IF(UPPER(p_update_table_name) = 'AMS_ADI_MEDIA_PLANNER') THEN
247 FORALL i in l_object_ids.first..l_object_ids.last
248 update ams_adi_media_planner set operation_status = 'FAILED',error_message = l_error_messages(i)
249 where upload_batch_id = p_upload_batch_id and object_id = l_object_ids(i);
250 ELSE
251 FORALL i in l_object_ids.first..l_object_ids.last
252 update ams_adi_campaigns_interface set operation_status = 'FAILED',error_message = l_error_messages(i)
253 where upload_batch_id = p_upload_batch_id and object_id = l_object_ids(i);
254 END IF;
255 END IF;
256
257 ELSE
258
259 IF (AMS_DEBUG_HIGH_ON) THEN
260 AMS_UTILITY_PVT.debug_message('Using name and parent id primary key');
261 END IF;
262
263 -- use object name and parent id as identifier
264 FOR i IN 1 .. l_count
265 LOOP
266 l_object_names.EXTEND;
267 l_object_names(i) := p_error_records(i).object_name;
268 l_parent_ids .EXTEND;
269 l_parent_ids(i) := p_error_records(i).parent_object_id;
270 l_error_messages.EXTEND;
271 l_error_messages(i) := p_error_records(i).error_message;
272
273 IF (AMS_DEBUG_HIGH_ON) THEN
274 AMS_UTILITY_PVT.debug_message('object name : '||p_error_records(i).object_name);
275 AMS_UTILITY_PVT.debug_message('parent object id : '||p_error_records(i).parent_object_id);
276 AMS_UTILITY_PVT.debug_message('error message : '||p_error_records(i).error_message);
277 END IF;
278
279 END LOOP;
280
281 IF (l_count > 0) THEN
282 FORALL i in l_object_names.first..l_object_names.last
283 update ams_adi_campaigns_interface set operation_status = 'FAILED',error_message = l_error_messages(i)
284 where upload_batch_id = p_upload_batch_id and object_name = l_object_names(i) and parent_object_id = l_parent_ids(i);
285 END IF;
286 END IF;
287 END complete_batch;
288
289
290 --========================================================================
291 -- PROCEDURE
292 --
293 -- Purpose
294 -- 1. writes to conc output no. of success rows and failure rows
295 -- HISTORY
296 --
297 --========================================================================
298 PROCEDURE complete_all(
299 p_write_conc_out IN VARCHAR2 := FND_API.G_TRUE,
300 p_commit IN VARCHAR2 := FND_API.G_TRUE,
301 p_upload_batch_id IN NUMBER := 0
302 )
303 IS
304 BEGIN
305 update ams_adi_campaigns_interface
306 set operation_status = 'SUCCESS'
307 where upload_batch_id = p_upload_batch_id
308 and operation_status = 'NEW';
309
310 IF(p_commit = FND_API.G_TRUE) THEN
311 COMMIT; -- commits all
312 END IF;
313
314 IF(p_write_conc_out = FND_API.G_TRUE) THEN
315 AMS_Utility_PVT.Write_Conc_Log();
316 END IF;
317 END complete_all;
318
319
320
321 END AMS_ADI_COMMON_PVT;