[Home] [Help]
PACKAGE BODY: APPS.MSC_CL_POST_PUBLISH
Source
1 PACKAGE BODY MSC_CL_POST_PUBLISH AS -- body
2 /* $Header: MSCXPODB.pls 120.0 2005/05/25 19:06:29 appldev noship $ */
3
4 l_site_string varchar2(2000) := NULL;
5 v_sql_stmt varchar2(4000);
6
7 CURSOR excepSummary IS
8 select plan_id,
9 inventory_item_id,
10 company_id,
11 company_site_id,
12 exception_group,
13 exception_type,
14 count(*)
15 from msc_x_exception_details
16 where plan_id = -1
17 and exception_group IN (1,2,4,6,7,8)
18 group by plan_id,
19 inventory_item_id,
20 company_id,
21 company_site_id,
22 exception_group,
23 exception_type;
24
25 a_plan_id number_arr;
26 a_inventory_item_id number_arr;
27 a_company_id number_arr;
28 a_company_site_id number_arr;
29 a_exception_group number_arr;
30 a_exception_type number_arr;
31 a_count number_arr;
32
33 PROCEDURE LOG_MESSAGE( pBUFF IN VARCHAR2)
34 IS
35 BEGIN
36
37 IF fnd_global.conc_request_id > 0 THEN -- concurrent program
38
39 FND_FILE.PUT_LINE( FND_FILE.LOG, pBUFF);
40
41 ELSE
42
43 --dbms_output.put_line(pBUFF);
44 null;
45
46 END IF;
47
48 END LOG_MESSAGE;
49
50 PROCEDURE UPDATE_EXCEPTION_SUMMARY(p_summary_status OUT NOCOPY NUMBER) IS
51 BEGIN
52
53 BEGIN
54
55 OPEN excepSummary;
56
57 FETCH excepSummary BULK COLLECT INTO
58 a_plan_id,
59 a_inventory_item_id,
60 a_company_id,
61 a_company_site_id,
62 a_exception_group,
63 a_exception_type,
64 a_count;
65
66 CLOSE excepSummary;
67
68 EXCEPTION WHEN OTHERS THEN
69 LOG_MESSAGE('Delete Exceptions : Error while fetching exception summary');
70 LOG_MESSAGE(SQLERRM);
71 p_summary_status := G_ERROR;
72 ROLLBACK;
73 RETURN;
74 END;
75
76 IF a_plan_id.COUNT > 0 THEN
77
78 LOG_MESSAGE('Updated Exception Summary - '||a_plan_id.COUNT);
79
80 BEGIN
81
82 FORALL i in 1..a_plan_id.COUNT
83
84 update msc_item_exceptions
85 set exception_count = a_count(i)
86 where plan_id = a_plan_id(i)
87 and company_id = a_company_id(i)
88 and company_site_id = a_company_site_id(i)
89 and inventory_item_id = a_inventory_item_id(i)
90 and exception_type = a_exception_type(i)
91 and exception_group = a_exception_group(i)
92 and version = 0;
93
94 COMMIT;
95
96 EXCEPTION WHEN OTHERS THEN
97 LOG_MESSAGE('Delete Exceptions : Error while updating Exception Summary');
98 LOG_MESSAGE(SQLERRM);
99 p_summary_status := G_ERROR;
100 ROLLBACK;
101 RETURN;
102 END;
103
104 END IF;
105
106 p_summary_status := G_SUCCESS;
107
108 END UPDATE_EXCEPTION_SUMMARY;
109
110
111 --=====================================================
112 -- This is main type of procedure in this package body.
113 --=====================================================
114
115 PROCEDURE POST_CLEANUP(p_org_str IN VARCHAR2,
116 p_lrtype IN VARCHAR2,
117 p_status OUT NOCOPY NUMBER) IS
118 l_summary_status NUMBER;
119 BEGIN
120
121 LOG_MESSAGE('Exception Deletion started');
122 LOG_MESSAGE('==========================');
123
124 IF (p_lrtype = 'C') THEN
125
126 l_site_string := p_org_str;
127
128 --======================================
129 -- Step 1. Delete OEM related Exceptions
130 --======================================
131
132 v_sql_stmt := null;
133
134 BEGIN
135
136 v_sql_stmt :=
137 ' delete msc_x_exception_details '
138 ||' where exception_type '|| G_EXCEP_TYPES
139 ||' and exception_group '|| G_EXCEP_GROUPS
140 ||' and company_id = 1 '
141 ||' and company_site_id '|| l_site_string
142 ||' and plan_id = -1 ';
143
144 EXECUTE IMMEDIATE v_sql_stmt;
145
146 LOG_MESSAGE('OEM side Exceptions - '||SQL%ROWCOUNT);
147
148 EXCEPTION WHEN OTHERS THEN
149 LOG_MESSAGE('Error while deleting exceptions in which OEM is Exception owner company');
150 LOG_MESSAGE(SQLERRM);
151 p_status := G_ERROR;
152 RETURN;
153 END;
154
155
156 --===========================================
157 -- Step 2. Delete Customer related Exceptions
158 --===========================================
159
160 v_sql_stmt := null;
161
162 BEGIN
163
164 v_sql_stmt :=
165 ' delete msc_x_exception_details '
166 ||' where exception_type '|| G_EXCEP_TYPES
167 ||' and exception_group '|| G_EXCEP_GROUPS
168 ||' and customer_id = 1 '
169 ||' and customer_site_id '||l_site_string
170 ||' and plan_id = -1';
171
172 EXECUTE IMMEDIATE v_sql_stmt;
173
174 COMMIT;
175
176 LOG_MESSAGE('Customer side Exceptions - '||SQL%ROWCOUNT);
177
178 EXCEPTION WHEN OTHERS THEN
179 LOG_MESSAGE('Error while deleting exceptions in which OEM is Customer');
180 LOG_MESSAGE(SQLERRM);
181 p_status := G_ERROR;
182 ROLLBACK;
183 RETURN;
184 END;
185
186
187 --===========================================
188 -- Step 3. Delete Supplier related Exceptions
189 --===========================================
190
191 v_sql_stmt := null;
192
193 BEGIN
194
195 v_sql_stmt :=
196 ' delete msc_x_exception_details '
197 ||' where exception_type '|| G_EXCEP_TYPES
198 ||' and exception_group '|| G_EXCEP_GROUPS
199 ||' and supplier_id = 1 '
200 ||' and supplier_site_id '|| l_site_string
201 ||' and plan_id = -1';
202
203 EXECUTE IMMEDIATE v_sql_stmt;
204
205 COMMIT;
206
207 LOG_MESSAGE('Supplier site Exceptions - '||SQL%ROWCOUNT);
208
209 EXCEPTION WHEN OTHERS THEN
210 LOG_MESSAGE('Error while deleting exceptions in which OEM is Supplier');
211 LOG_MESSAGE(SQLERRM);
212 p_status := G_ERROR;
213 ROLLBACK;
214 RETURN;
215 END;
216
217 --==================================
218 -- Step 4. Delete Exception Headers
219 --==================================
220
221 BEGIN
222
223 delete msc_item_exceptions mie
224 where
225 plan_id = -1
226 and exception_group in (1,2,4,6,7,8)
227 and not exists( select 1
228 from msc_x_exception_details med
229 where med.company_id = mie.company_id
230 and med.company_site_id = mie.company_site_id
231 and med.plan_id = mie.plan_id
232 and med.plan_id = -1
233 and med.inventory_item_id = mie.inventory_item_id
234 and med.exception_type = mie.exception_type
235 and med.exception_group = mie.exception_group);
236
237 COMMIT;
238
239
240 EXCEPTION WHEN OTHERS THEN
241 LOG_MESSAGE('Error while deleting msc_item_exceptions');
242 LOG_MESSAGE(SQLERRM);
243 p_status := G_ERROR;
244 ROLLBACK;
245 RETURN;
246 END;
247
248 --==================================
249 -- Step 4. Update Exception Headers
250 --==================================
251
252 BEGIN
253
254 OPEN excepSummary;
255
256 FETCH excepSummary BULK COLLECT INTO
257 a_plan_id,
258 a_inventory_item_id,
259 a_company_id,
260 a_company_site_id,
261 a_exception_group,
262 a_exception_type,
263 a_count;
264
265 CLOSE excepSummary;
266
267 EXCEPTION WHEN OTHERS THEN
268 LOG_MESSAGE('Delete Exceptions : Error while fetching exception summary');
269 LOG_MESSAGE(SQLERRM);
270 p_status := G_ERROR;
271 ROLLBACK;
272 RETURN;
273 END;
274
275 IF a_plan_id.COUNT > 0 THEN
276
277 LOG_MESSAGE('Updated Exception Summary - '||a_plan_id.COUNT);
278
279 BEGIN
280
281 FORALL i in 1..a_plan_id.COUNT
282
283 update msc_item_exceptions
284 set exception_count = a_count(i)
285 where plan_id = a_plan_id(i)
286 and company_id = a_company_id(i)
287 and company_site_id = a_company_site_id(i)
288 and inventory_item_id = a_inventory_item_id(i)
289 and exception_type = a_exception_type(i)
290 and exception_group = a_exception_group(i)
291 and version = 0;
292
293 COMMIT;
294
295 EXCEPTION WHEN OTHERS THEN
296 LOG_MESSAGE('Delete Exceptions : Error while updating Exception Summary');
297 LOG_MESSAGE(SQLERRM);
298 p_status := G_ERROR;
299 ROLLBACK;
300 RETURN;
301 END;
302
303 END IF;
304
305 p_status := G_SUCCESS;
306
307 ELSE -- (p_lrtype <> 'C')
308
309 --===============================================================
310 -- Delete exceptions which do not have transaction_ids present in
311 -- msc_sup_dem_entries
312 --===============================================================
313
314 v_sql_stmt := null;
315
316 BEGIN
317
318 v_sql_stmt :=
319 ' delete msc_x_exception_details med '
320 ||' where transaction_id1 is not null'
321 ||' and exception_type '||G_DUPLICATE_EXCEP_TYPES
322 ||' and exception_group '||G_EXCEP_GROUPS
323 ||' and not exists ( select 1 '
324 ||' from msc_sup_dem_entries msde'
325 ||' where msde.transaction_id = med.transaction_id1)';
326
327 EXECUTE IMMEDIATE v_sql_stmt;
328
329 LOG_MESSAGE('No. of deleted exceptions where first transaction does not present in CP transactions - '||SQL%ROWCOUNT);
330
331 COMMIT;
332
333 EXCEPTION WHEN OTHERS THEN
334 LOG_MESSAGE('Error while deleting exception details related to first transaction in case of non Complete refresh collection mode');
335 LOG_MESSAGE(SQLERRM);
336 p_status := G_ERROR;
337 ROLLBACK;
338 RETURN;
339 END;
340
341
342 v_sql_stmt := null;
343
344 BEGIN
345
346 v_sql_stmt :=
347 ' delete msc_x_exception_details med '
348 ||' where transaction_id2 is not null '
349 ||' and exception_type '||G_DUPLICATE_EXCEP_TYPES
350 ||' and exception_group '||G_EXCEP_GROUPS
351 ||' and not exists ( select 1 '
352 ||' from msc_sup_dem_entries msde '
353 ||' where msde.transaction_id = med.transaction_id2)';
354
355 EXECUTE IMMEDIATE v_sql_stmt;
356
357 LOG_MESSAGE('No. of deleted exceptions where second transaction does not present in CP transactions - '||SQL%ROWCOUNT);
358
359 COMMIT;
360
361 EXCEPTION WHEN OTHERS THEN
362 LOG_MESSAGE('Error while deleting exception details related to second transaction in case of non Complete refresh collection mode');
363 LOG_MESSAGE(SQLERRM);
364 p_status := G_ERROR;
365 ROLLBACK;
366 RETURN;
367 END;
368
369 UPDATE_EXCEPTION_SUMMARY(l_summary_status);
370
371 P_status := l_summary_status;
372
373
374 END IF; -- (p_lrtype = 'C')
375 END POST_CLEANUP;
376
377 END MSC_CL_POST_PUBLISH;