1 PACKAGE BODY OE_ELECMSGS_PVT AS
2 /* $Header: OEXVELMB.pls 120.5 2005/12/09 05:09:21 kmuruges ship $ */
3
4 PROCEDURE do_query(p_elec_msgs_tbl IN OUT NOCOPY /* file.sql.39 change */ Elec_Msgs_Summary_Tbl,
5 p_order_source_id IN NUMBER,
6 p_orig_sys_document_ref IN VARCHAR2,
7 p_sold_to_org_id IN NUMBER,
8 p_transaction_type IN VARCHAR2,
9 p_start_date_from IN DATE,
10 p_start_date_to IN DATE,
11 p_update_date_from IN DATE,
12 p_update_date_to IN DATE,
13 p_message_status_code IN VARCHAR2
14 )
15 IS
16 l_place_holder_table Elec_Msgs_Summary_Tbl;
17 l_start_date_from DATE;
18 l_start_date_to DATE;
19 l_update_date_from DATE;
20 l_update_date_to DATE;
21 DT_mask VARCHAR2(25) := fnd_date.canonical_DT_mask;
22 l_min_order_source_id NUMBER;
23 l_max_order_source_id NUMBER;
24
25 cursor results_cursor_1 is
26 select x.order_source_id,
27 x.orig_sys_document_ref,
28 x.sold_to_org_id,
29 decode(nvl(max(x.order_number), 0), 0, NULL, max(x.order_number)),
30 max(order_type_id),
31 count(*),
32 min(x.creation_date),
33 max(x.last_update_date),
34 'x',
35 x.org_id
36 from oe_em_information x
37 where x.order_source_id = p_order_source_id
38 and (x.orig_sys_document_ref = p_orig_sys_document_ref or p_orig_sys_document_ref IS NULL)
39 and x.sold_to_org_id = p_sold_to_org_id
40 and (x.em_transaction_type_code = p_transaction_type or p_transaction_type IS NULL)
41 group by x.order_source_id, x.orig_sys_document_ref, x.sold_to_org_id, x.org_id
42 having min(x.creation_date) between nvl(l_start_date_from, min(x.creation_date)) and nvl(l_start_date_to, min(x.creation_date))
43 and max(x.last_update_date) between nvl(l_update_date_from, max(x.last_update_date)) and nvl(l_update_date_to, max(x.last_update_date))
44 order by x.org_id, x.order_source_id, x.sold_to_org_id;
45
46 cursor results_cursor_2 is
47 select x.order_source_id,
48 x.orig_sys_document_ref,
49 x.sold_to_org_id,
50 decode(nvl(max(x.order_number), 0), 0, NULL, max(x.order_number)),
51 max(order_type_id),
52 count(*),
53 min(x.creation_date),
54 max(x.last_update_date),
55 'x',
56 x.org_id
57 from oe_em_information x
58 where x.order_source_id = p_order_source_id
59 and (x.orig_sys_document_ref = p_orig_sys_document_ref or p_orig_sys_document_ref IS NULL)
60 and (x.sold_to_org_id = p_sold_to_org_id or p_sold_to_org_id IS NULL)
61 and (x.em_transaction_type_code = p_transaction_type or p_transaction_type IS NULL)
62 group by x.order_source_id, x.orig_sys_document_ref, x.sold_to_org_id, x.org_id
63 having min(x.creation_date) between nvl(l_start_date_from, min(x.creation_date)) and nvl(l_start_date_to, min(x.creation_date))
64 and max(x.last_update_date) between nvl(l_update_date_from, max(x.last_update_date)) and nvl(l_update_date_to, max(x.last_update_date))
65 order by x.org_id, x.order_source_id, x.sold_to_org_id;
66
67 cursor results_cursor_3 is
68 select x.order_source_id,
69 x.orig_sys_document_ref,
70 x.sold_to_org_id,
71 decode(nvl(max(x.order_number), 0), 0, NULL, max(x.order_number)),
72 max(order_type_id),
73 count(*),
74 min(x.creation_date),
75 max(x.last_update_date),
76 'x',
77 x.org_id
78 from oe_em_information x
79 where x.order_source_id between l_min_order_source_id and l_max_order_source_id
80 and (x.orig_sys_document_ref = p_orig_sys_document_ref or p_orig_sys_document_ref IS NULL)
81 and x.sold_to_org_id = p_sold_to_org_id
82 and (x.em_transaction_type_code = p_transaction_type or p_transaction_type IS NULL)
83 group by x.order_source_id, x.orig_sys_document_ref, x.sold_to_org_id, x.org_id
84 having min(x.creation_date) between nvl(l_start_date_from, min(x.creation_date)) and nvl(l_start_date_to, min(x.creation_date))
85 and max(x.last_update_date) between nvl(l_update_date_from, max(x.last_update_date)) and nvl(l_update_date_to, max(x.last_update_date))
86 order by x.org_id, x.order_source_id, x.sold_to_org_id;
87
88 cursor results_cursor_4 is
89 select x.order_source_id,
90 x.orig_sys_document_ref,
91 x.sold_to_org_id,
92 decode(nvl(max(x.order_number), 0), 0, NULL, max(x.order_number)),
93 max(order_type_id),
94 count(*),
95 min(x.creation_date),
96 max(x.last_update_date),
97 'x',
98 x.org_id
99 from oe_em_information x
100 where x.order_source_id between l_min_order_source_id and l_max_order_source_id
101 and (x.orig_sys_document_ref = p_orig_sys_document_ref or p_orig_sys_document_ref IS NULL)
102 and (x.sold_to_org_id = p_sold_to_org_id or p_sold_to_org_id IS NULL)
103 and (x.em_transaction_type_code = p_transaction_type or p_transaction_type IS NULL)
104 group by x.order_source_id, x.orig_sys_document_ref, x.sold_to_org_id, x.org_id
105 having min(x.creation_date) between nvl(l_start_date_from, min(x.creation_date)) and nvl(l_start_date_to, min(x.creation_date))
106 and max(x.last_update_date) between nvl(l_update_date_from, max(x.last_update_date)) and nvl(l_update_date_to, max(x.last_update_date))
107 order by x.org_id, x.order_source_id, x.sold_to_org_id;
108
109 cursor results_cursor_5 is
110 select x.order_source_id,
111 x.orig_sys_document_ref,
112 x.sold_to_org_id,
113 decode(nvl(max(x.order_number), 0), 0, NULL, max(x.order_number)),
114 max(order_type_id),
115 count(*),
116 min(x.creation_date),
117 max(x.last_update_date),
118 'x',
119 x.org_id
120 from oe_em_information x
121 where x.order_source_id = p_order_source_id
122 and (x.orig_sys_document_ref = p_orig_sys_document_ref or p_orig_sys_document_ref IS NULL)
123 and x.sold_to_org_id = p_sold_to_org_id
124 and (x.em_transaction_type_code = p_transaction_type or p_transaction_type IS NULL)
125 and exists (select 1 from oe_processing_msgs msg
126 where msg.order_source_id = x.order_source_id
127 and msg.original_sys_document_ref = x.orig_sys_document_ref
128 and ((msg.entity_code like 'ELECMSG%' and msg.entity_id = to_number(x.item_key)) or x.em_conc_request_id = msg.request_id)
129 and msg.message_status_code = p_message_status_code)
130
131 group by x.order_source_id, x.orig_sys_document_ref, x.sold_to_org_id, x.org_id
132 having min(x.creation_date) between nvl(l_start_date_from, min(x.creation_date)) and nvl(l_start_date_to, min(x.creation_date))
133 and max(x.last_update_date) between nvl(l_update_date_from, max(x.last_update_date)) and nvl(l_update_date_to, max(x.last_update_date))
134 order by x.org_id, x.order_source_id, x.sold_to_org_id;
135
136 cursor results_cursor_6 is
137 select x.order_source_id,
138 x.orig_sys_document_ref,
139 x.sold_to_org_id,
140 decode(nvl(max(x.order_number), 0), 0, NULL, max(x.order_number)),
141 max(order_type_id),
142 count(*),
143 min(x.creation_date),
144 max(x.last_update_date),
145 'x',
146 x.org_id
147 from oe_em_information x
148 where x.order_source_id = p_order_source_id
149 and (x.orig_sys_document_ref = p_orig_sys_document_ref or p_orig_sys_document_ref IS NULL)
150 and (x.sold_to_org_id = p_sold_to_org_id or p_sold_to_org_id IS NULL)
151 and (x.em_transaction_type_code = p_transaction_type or p_transaction_type IS NULL)
152 and exists (select 1 from oe_processing_msgs msg
153 where msg.order_source_id = x.order_source_id
154 and msg.original_sys_document_ref = x.orig_sys_document_ref
155 and ((msg.entity_code like 'ELECMSG%' and msg.entity_id = to_number(x.item_key)) or x.em_conc_request_id = msg.request_id)
156 and msg.message_status_code = p_message_status_code)
157 group by x.order_source_id, x.orig_sys_document_ref, x.sold_to_org_id, x.org_id
158 having min(x.creation_date) between nvl(l_start_date_from, min(x.creation_date)) and nvl(l_start_date_to, min(x.creation_date))
159 and max(x.last_update_date) between nvl(l_update_date_from, max(x.last_update_date)) and nvl(l_update_date_to, max(x.last_update_date))
160 order by x.org_id, x.order_source_id, x.sold_to_org_id;
161
162 cursor results_cursor_7 is
163 select x.order_source_id,
164 x.orig_sys_document_ref,
165 x.sold_to_org_id,
166 decode(nvl(max(x.order_number), 0), 0, NULL, max(x.order_number)),
167 max(order_type_id),
168 count(*),
169 min(x.creation_date),
170 max(x.last_update_date),
171 'x',
172 x.org_id
173 from oe_em_information x
174 where x.order_source_id between l_min_order_source_id and l_max_order_source_id
175 and (x.orig_sys_document_ref = p_orig_sys_document_ref or p_orig_sys_document_ref IS NULL)
176 and x.sold_to_org_id = p_sold_to_org_id
177 and (x.em_transaction_type_code = p_transaction_type or p_transaction_type IS NULL)
178 and exists (select 1 from oe_processing_msgs msg
179 where msg.order_source_id = x.order_source_id
180 and msg.original_sys_document_ref = x.orig_sys_document_ref
181 and ((msg.entity_code like 'ELECMSG%' and msg.entity_id = to_number(x.item_key)) or x.em_conc_request_id = msg.request_id)
182 and msg.message_status_code = p_message_status_code)
183 group by x.order_source_id, x.orig_sys_document_ref, x.sold_to_org_id, x.org_id
184 having min(x.creation_date) between nvl(l_start_date_from, min(x.creation_date)) and nvl(l_start_date_to, min(x.creation_date))
185 and max(x.last_update_date) between nvl(l_update_date_from, max(x.last_update_date)) and nvl(l_update_date_to, max(x.last_update_date))
186 order by x.org_id, x.order_source_id, x.sold_to_org_id;
187
188 cursor results_cursor_8 is
189 select x.order_source_id,
190 x.orig_sys_document_ref,
191 x.sold_to_org_id,
192 decode(nvl(max(x.order_number), 0), 0, NULL, max(x.order_number)),
193 max(order_type_id),
194 count(*),
195 min(x.creation_date),
196 max(x.last_update_date),
197 'x',
198 x.org_id
199 from oe_em_information x
200 where x.order_source_id between l_min_order_source_id and l_max_order_source_id
201 and (x.orig_sys_document_ref = p_orig_sys_document_ref or p_orig_sys_document_ref IS NULL)
202 and (x.sold_to_org_id = p_sold_to_org_id or p_sold_to_org_id IS NULL)
203 and (x.em_transaction_type_code = p_transaction_type or p_transaction_type IS NULL)
204 and exists (select 1 from oe_processing_msgs msg
205 where msg.order_source_id = x.order_source_id
206 and msg.original_sys_document_ref = x.orig_sys_document_ref
207 and ((msg.entity_code like 'ELECMSG%' and msg.entity_id = to_number(x.item_key)) or x.em_conc_request_id = msg.request_id)
208 and msg.message_status_code = p_message_status_code)
209 group by x.order_source_id, x.orig_sys_document_ref, x.sold_to_org_id, x.org_id
210 having min(x.creation_date) between nvl(l_start_date_from, min(x.creation_date)) and nvl(l_start_date_to, min(x.creation_date))
211 and max(x.last_update_date) between nvl(l_update_date_from, max(x.last_update_date)) and nvl(l_update_date_to, max(x.last_update_date))
212 order by x.org_id, x.order_source_id, x.sold_to_org_id;
213
214 idx number := 1;
215 j number := 1;
216 n number := 1;
217
218 l_results_rec Elec_Msgs_Summary_Type;
219 cursor results_Cursor2 is
220 SELECT em_transaction_type_code, message_text, document_status
221 FROM oe_em_information
222 WHERE order_source_id = p_elec_msgs_tbl(j).order_source_id
223 AND orig_sys_document_ref = p_elec_msgs_tbl(j).orig_sys_document_ref
224 AND sold_to_org_id = p_elec_msgs_tbl(j).sold_to_org_id
225 AND org_id = p_elec_msgs_tbl(j).org_id
226 ORDER BY creation_date desc;
227 BEGIN
228
229 -- do the following date conversion in order so that if the user inputs '1-JAN-03'
230 -- we search between '1-JAN-03 00:00:00' and '1-JAN-03 23:59:59'
231
232 l_start_date_from := to_date(to_char(trunc(p_start_date_from), DT_mask),DT_mask);
233 l_start_date_to := to_date(to_char(p_start_date_to+(1-1/(24*60*60)), DT_mask),DT_mask);
234 l_update_date_from := to_date(to_char(trunc(p_update_date_from), DT_mask),DT_mask);
235 l_update_date_to := to_date(to_char(p_update_date_to+(1-1/(24*60*60)), DT_mask),DT_mask);
236
237 IF p_order_source_id IS NULL THEN
238 SELECT max(order_source_id), min(order_source_id)
239 INTO l_max_order_source_id, l_min_order_source_id
240 FROM OE_Order_Sources;
241 END IF;
242
243 IF p_order_source_id IS NOT NULL
244 AND p_sold_to_org_id IS NOT NULL THEN
245
246 IF p_message_status_code IS NULL THEN
247
248 OPEN results_cursor_1;
249 LOOP
250 FETCH results_cursor_1 INTO
251 l_results_rec.order_source_id,
252 l_results_rec.orig_sys_document_ref,
253 l_results_rec.sold_to_org_id,
254 l_results_rec.order_number,
255 l_results_rec.order_type_id,
256 l_results_rec.num_msgs,
257 l_results_rec.creation_date,
258 l_results_rec.last_update_date,
259 l_results_rec.last_transaction_type,
260 l_results_rec.org_id;
261 EXIT WHEN results_cursor_1%NOTFOUND;
262 p_elec_msgs_tbl(idx) := l_results_rec;
263 idx := idx + 1;
264 END LOOP;
265 CLOSE results_cursor_1;
266
267 ELSE
268
269 OPEN results_cursor_5;
270 LOOP
271 FETCH results_cursor_5 INTO
272 l_results_rec.order_source_id,
273 l_results_rec.orig_sys_document_ref,
274 l_results_rec.sold_to_org_id,
275 l_results_rec.order_number,
276 l_results_rec.order_type_id,
277 l_results_rec.num_msgs,
278 l_results_rec.creation_date,
279 l_results_rec.last_update_date,
280 l_results_rec.last_transaction_type,
281 l_results_rec.org_id;
282 EXIT WHEN results_cursor_5%NOTFOUND;
283 p_elec_msgs_tbl(idx) := l_results_rec;
284 idx := idx + 1;
285 END LOOP;
286 CLOSE results_cursor_5;
287 END IF;
288 ELSIF p_order_source_id IS NOT NULL THEN
289 IF p_message_status_code IS NULL THEN
290 OPEN results_cursor_2;
291 LOOP
292 FETCH results_cursor_2 INTO
293 l_results_rec.order_source_id,
294 l_results_rec.orig_sys_document_ref,
295 l_results_rec.sold_to_org_id,
296 l_results_rec.order_number,
297 l_results_rec.order_type_id,
298 l_results_rec.num_msgs,
299 l_results_rec.creation_date,
300 l_results_rec.last_update_date,
301 l_results_rec.last_transaction_type,
302 l_results_rec.org_id;
303 EXIT WHEN results_cursor_2%NOTFOUND;
304
305 p_elec_msgs_tbl(idx) := l_results_rec;
306 idx := idx + 1;
307 END LOOP;
308 CLOSE results_cursor_2;
309
310 ELSE
311 OPEN results_cursor_6;
312 LOOP
313 FETCH results_cursor_6 INTO
314 l_results_rec.order_source_id,
315 l_results_rec.orig_sys_document_ref,
316 l_results_rec.sold_to_org_id,
317 l_results_rec.order_number,
318 l_results_rec.order_type_id,
319 l_results_rec.num_msgs,
320 l_results_rec.creation_date,
321 l_results_rec.last_update_date,
322 l_results_rec.last_transaction_type,
323 l_results_rec.org_id;
324 EXIT WHEN results_cursor_6%NOTFOUND;
325
326 p_elec_msgs_tbl(idx) := l_results_rec;
327 idx := idx + 1;
328
329 END LOOP;
330 CLOSE results_cursor_6;
331 END IF;
332
333 ELSIF p_sold_to_org_id IS NOT NULL THEN
334 IF p_message_status_code IS NULL THEN
335
336 OPEN results_cursor_3;
337 LOOP
338 FETCH results_cursor_3 INTO
339 l_results_rec.order_source_id,
340 l_results_rec.orig_sys_document_ref,
341 l_results_rec.sold_to_org_id,
342 l_results_rec.order_number,
343 l_results_rec.order_type_id,
344 l_results_rec.num_msgs,
345 l_results_rec.creation_date,
346 l_results_rec.last_update_date,
347 l_results_rec.last_transaction_type,
348 l_results_rec.org_id;
349 EXIT WHEN results_cursor_3%NOTFOUND;
350 p_elec_msgs_tbl(idx) := l_results_rec;
351 idx := idx + 1;
352 END LOOP;
353 CLOSE results_cursor_3;
354
355 ELSE
356
357 OPEN results_cursor_7;
358 LOOP
359 FETCH results_cursor_7 INTO
360 l_results_rec.order_source_id,
361 l_results_rec.orig_sys_document_ref,
362 l_results_rec.sold_to_org_id,
363 l_results_rec.order_number,
364 l_results_rec.order_type_id,
365 l_results_rec.num_msgs,
366 l_results_rec.creation_date,
367 l_results_rec.last_update_date,
368 l_results_rec.last_transaction_type,
369 l_results_rec.org_id;
370 EXIT WHEN results_cursor_7%NOTFOUND;
371 p_elec_msgs_tbl(idx) := l_results_rec;
372 idx := idx + 1;
373 END LOOP;
374 CLOSE results_cursor_7;
375 END IF;
376 ELSE
377 IF p_message_status_code IS NULL THEN
378
379 OPEN results_cursor_4;
380 LOOP
381 FETCH results_cursor_4 INTO
382 l_results_rec.order_source_id,
383 l_results_rec.orig_sys_document_ref,
384 l_results_rec.sold_to_org_id,
385 l_results_rec.order_number,
386 l_results_rec.order_type_id,
387 l_results_rec.num_msgs,
388 l_results_rec.creation_date,
389 l_results_rec.last_update_date,
390 l_results_rec.last_transaction_type,
391 l_results_rec.org_id;
392 EXIT WHEN results_cursor_4%NOTFOUND;
393 p_elec_msgs_tbl(idx) := l_results_rec;
394 idx := idx + 1;
395 END LOOP;
396 CLOSE results_cursor_4;
397
398 ELSE
399 OPEN results_cursor_8;
400 LOOP
401 FETCH results_cursor_8 INTO
402 l_results_rec.order_source_id,
403 l_results_rec.orig_sys_document_ref,
404 l_results_rec.sold_to_org_id,
405 l_results_rec.order_number,
406 l_results_rec.order_type_id,
407 l_results_rec.num_msgs,
408 l_results_rec.creation_date,
409 l_results_rec.last_update_date,
410 l_results_rec.last_transaction_type,
411 l_results_rec.org_id;
412 EXIT WHEN results_cursor_8%NOTFOUND;
413 p_elec_msgs_tbl(idx) := l_results_rec;
414 idx := idx + 1;
415 END LOOP;
416 CLOSE results_cursor_8;
417 END IF;
418 END IF;
419 --oe_debug_pub.add('434'||idx||' fsdf' || results_cursor%ROWCOUNT);
420
421 IF p_elec_msgs_tbl.count > 0 then
422
423
424 /* summaries need to recomputed if transaction type is passed in*/
425 IF p_transaction_type IS NOT NULL THEN
426 LOOP
427 EXIT WHEN n = idx;
428 SELECT decode(nvl(max(order_number), 0), 0, NULL, max(order_number)),max (order_type_id), count(*), min(creation_Date), max(last_update_Date)
429 INTO p_elec_msgs_tbl(n).order_number,p_elec_msgs_tbl(n).order_type_id, p_elec_msgs_tbl(n).num_msgs, p_elec_msgs_tbl(n).creation_date, p_elec_msgs_tbl(n).last_update_date
430 FROM oe_em_information
431 WHERE order_source_id = p_elec_msgs_tbl(n).order_source_id
432 AND orig_sys_document_ref = p_elec_msgs_tbl(n).orig_sys_document_ref
433 AND sold_to_org_id = p_elec_msgs_tbl(n).sold_to_org_id
434 AND org_id = p_elec_msgs_tbl(n).org_id;
435 n := n+1;
436 END LOOP;
437 END IF;
438
439 LOOP
440 OPEN results_cursor2;
441 EXIT WHEN j = idx;
442 FETCH results_cursor2 INTO
443 p_elec_msgs_tbl(j).last_transaction_type,
444 p_elec_msgs_tbl(j).last_transaction_message,
445 p_elec_msgs_tbl(j).last_transaction_status;
446 j := j + 1;
447
448 CLOSE results_cursor2;
449 END LOOP;
450 END IF;
451
452 IF p_elec_msgs_tbl.count = 0 then
453 p_elec_msgs_tbl := l_place_holder_table;
454 END IF;
455
456 EXCEPTION
457 WHEN OTHERS THEN
458 --oe_debug_pub.add('others'||SQLERRM);
459 IF p_elec_msgs_tbl.count = 0 then
460 p_elec_msgs_tbl := l_place_holder_table;
461 END IF;
462 End do_query;
463
464
465 PROCEDURE Create_History_Entry (
466 p_order_source_id IN NUMBER,
467 p_sold_to_org_id IN NUMBER,
468 p_orig_sys_document_ref IN VARCHAR2,
469 p_transaction_type IN VARCHAR2,
470 p_document_id IN NUMBER,
471 p_parent_document_id IN NUMBER,
472 p_org_id IN NUMBER,
473 p_change_sequence IN VARCHAR2,
474 p_itemtype IN VARCHAR2,
475 p_itemkey IN VARCHAR2,
476 p_order_number IN NUMBER,
477 p_order_type_id IN NUMBER,
478 p_status IN VARCHAR2,
479 p_message_text IN VARCHAR2,
480 p_request_id IN NUMBER,
481 p_header_id IN NUMBER,
482 p_document_disposition IN VARCHAR2,
483 p_last_update_itemkey IN VARCHAR2,
484 x_return_status OUT NOCOPY VARCHAR2)
485 IS
486
487 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
488
489 BEGIN
490 /* Validation to ensure that none of the primary key elements is NULL */
491 IF (p_order_source_id IS NULL OR p_sold_to_org_id IS NULL
492 OR p_orig_sys_document_ref IS NULL OR p_transaction_type IS NULL OR p_document_id IS NULL) THEN
493 --oe_debug_pub.add('Insufficient key params to insert,returning');
494 x_return_status := FND_API.G_RET_STS_ERROR;
495 return;
496 END IF;
497 --oe_debug_pub.add('before insert');
498
499 /* Insertion -- what should the behaviour be if org_id is null */
500 INSERT INTO Oe_Em_Information (
501 order_source_id,
502 orig_sys_document_ref,
503 sold_to_org_id,
504 em_transaction_type_code,
505 document_id,
506 parent_document_id,
507 org_id,
508 change_sequence,
509 item_type,
510 item_key,
511 order_number,
512 order_type_id,
513 document_status,
514 message_text,
515 em_conc_request_id,
516 created_by,
517 creation_date,
518 last_update_login,
519 last_updated_by,
520 last_update_date,
521 header_id,
522 document_disposition,
523 last_update_itemkey)
524 VALUES (
525 p_order_source_id,
526 p_orig_sys_document_ref,
527 p_sold_to_org_id,
528 p_transaction_type,
529 p_document_id,
530 p_parent_document_id,
531 p_org_id,
532 p_change_sequence,
533 p_itemtype,
534 p_itemkey,
535 p_order_number,
536 p_order_type_id,
537 p_status,
538 p_message_text,
539 p_request_id,
540 FND_GLOBAL.USER_ID,
541 SYSDATE,
542 FND_GLOBAL.LOGIN_ID,
543 FND_GLOBAL.USER_ID,
544 SYSDATE,
545 p_header_id,
546 p_document_disposition,
547 p_last_update_itemkey
548 );
549 x_return_status := FND_API.G_RET_STS_SUCCESS;
550 IF l_debug_level > 0 THEN
551 oe_debug_pub.add('Insert succeeded'||SQL%ROWCOUNT||' de'||SQLERRM);
552 END IF;
553 EXCEPTION
554 WHEN DUP_VAL_ON_INDEX THEN
555 IF l_debug_level > 0 THEN
556 oe_debug_pub.add('DUPLICATE INDEX VALUE IN OEXVELMB.Create History Entry, update instead of insert'||SQLERRM);
557 END IF;
558 x_return_status := FND_API.G_RET_STS_ERROR;
559 WHEN OTHERS THEN
560 IF l_debug_level > 0 THEN
561 oe_debug_pub.add('OTHERS IN OEXVELMB.Create History Entry'||SQLERRM);
562 END IF;
563 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
564 END Create_History_Entry;
565
566 PROCEDURE Update_History_Entry (
567 p_order_source_id IN NUMBER,
568 p_sold_to_org_id IN NUMBER,
569 p_orig_sys_document_ref IN VARCHAR2,
570 p_transaction_type IN VARCHAR2,
571 p_document_id IN NUMBER,
572 p_parent_document_id IN NUMBER,
573 p_org_id IN NUMBER,
574 p_change_sequence IN VARCHAR2,
575 p_itemtype IN VARCHAR2,
576 p_itemkey IN VARCHAR2,
577 p_order_number IN NUMBER,
578 p_order_type_id IN NUMBER,
579 p_status IN VARCHAR2,
580 p_message_text IN VARCHAR2,
581 p_request_id IN NUMBER,
582 p_header_id IN NUMBER,
583 p_document_disposition IN VARCHAR2,
584 p_last_update_itemkey IN VARCHAR2,
585 x_return_status OUT NOCOPY VARCHAR2)
586 IS
587 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
588
589 BEGIN
590 /* Validation to ensure that none of the primary key elements is NULL */
591 IF (p_order_source_id IS NULL OR p_sold_to_org_id IS NULL
592 OR p_orig_sys_document_ref IS NULL OR p_transaction_type IS NULL OR p_document_id IS NULL) THEN
593 IF l_debug_level > 0 THEN
594 oe_debug_pub.add ('Electronic Messages: One or more key elements are null');
595 END IF;
596 x_return_status := FND_API.G_RET_STS_ERROR;
597 return;
598 END IF;
599
600 /* Insertion -- what should the behaviour be if org_id is null */
601 UPDATE Oe_Em_Information Set
602 item_type = nvl(p_itemtype, item_type),
603 item_key = nvl(p_itemkey, item_key),
604 change_sequence = nvl(p_change_sequence, change_sequence),
605 order_number = nvl(p_order_number, order_number),
606 document_status = nvl(p_status, document_status),
607 message_text = nvl(p_message_text, message_text),
608 em_conc_request_id = nvl(p_request_id, em_conc_request_id),
609 order_type_id = nvl(p_order_type_id, order_type_id),
610 last_update_login = FND_GLOBAL.LOGIN_ID,
611 last_updated_by = FND_GLOBAL.USER_ID,
612 last_update_date = SYSDATE,
613 header_id = nvl(p_header_id, header_id),
614 document_disposition = nvl(p_document_disposition, document_disposition),
615 last_update_itemkey = nvl(p_last_update_itemkey, last_update_itemkey)
616 WHERE order_source_id = p_order_source_id
617 AND orig_sys_document_ref = p_orig_sys_document_ref
618 AND sold_to_org_id = p_sold_to_org_id
619 AND em_transaction_type_code = p_transaction_type
620 AND document_id = p_document_id;
621
622 IF l_debug_level > 0 THEN
623 oe_debug_pub.add('Number of rows updated: ' || SQL%ROWCOUNT);
624 END IF;
625 x_return_status := FND_API.G_RET_STS_SUCCESS;
626
627 EXCEPTION
628 WHEN OTHERS THEN
629 /* log appropriate debug/error messages and return */
630 IF l_debug_level > 0 THEN
631 oe_debug_pub.add('OTHERS IN OEXVELMB.Update History Entry');
632 END IF;
633 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
634
635 END Update_History_Entry;
636
637
638
639 FUNCTION Find_History_Entry (
640 p_order_source_id IN NUMBER,
641 p_orig_sys_document_ref IN VARCHAR2,
642 p_sold_to_org_id IN NUMBER,
643 p_transaction_type IN VARCHAR2,
644 p_document_id IN NUMBER,
645 x_last_itemkey OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
646 x_last_request_id OUT NOCOPY /* file.sql.39 change */ NUMBER)
647 RETURN BOOLEAN
648 IS
649 l_dummy NUMBER;
650 l_last_itemkey VARCHAR2(240);
651 l_last_request_id NUMBER;
652 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
653
654 BEGIN
655
656 SELECT order_source_id, last_update_itemkey, em_conc_request_id
657 INTO l_dummy, l_last_itemkey, l_last_request_id
658 FROM oe_em_information
659 WHERE order_source_id = p_order_source_id
660 AND orig_sys_document_ref = p_orig_sys_document_ref
661 AND sold_to_org_id = p_sold_to_org_id
662 AND em_transaction_type_code = p_transaction_type
663 AND document_id = p_document_id
664 FOR UPDATE;
665
666 x_last_request_id := l_last_request_id;
667 x_last_itemkey := l_last_itemkey;
668 return TRUE;
669 EXCEPTION
670 WHEN OTHERS THEN
671 IF l_debug_level > 0 THEN
672 oe_debug_pub.add ('Others in Find_History_entry: '||SQLERRM);
673 END IF;
674 return FALSE;
675 END;
676
677 /* This function returns the document id for the last 3A7
678 sent for a particular order source, orig sys doc ref and customer
679 It is to be used to identify the 3A7 for which a 3a8
680 response is sent */
681
682 FUNCTION Find_Parent_Document_Id (
683 p_order_source_id IN NUMBER,
684 p_orig_sys_document_ref IN VARCHAR2,
685 p_sold_to_org_id IN NUMBER,
686 p_org_id IN NUMBER
687 )
688 RETURN NUMBER
689 IS
690 l_document_id NUMBER;
691 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
692 BEGIN
693 /* SELECT document_id
694 INTO l_document_id
695 FROM oe_em_information
696 WHERE order_source_id = p_order_source_id
697 AND orig_sys_document_ref = p_orig_sys_document_ref
698 AND sold_to_org_id = p_sold_to_org_id
699 AND em_transaction_type_code = 'CSO'
700 AND rownum = 1
701 ORDER BY 1 DESC;
702 */
703 SELECT max(document_id)
704 INTO l_document_id
705 FROM oe_em_information
706 WHERE order_source_id = p_order_source_id
707 AND orig_sys_document_ref = p_orig_sys_document_ref
708 AND sold_to_org_id = p_sold_to_org_id
709 AND em_transaction_type_code = 'CSO'
710 AND org_id = p_org_id;
711
712 return l_document_id;
713 EXCEPTION
714 WHEN OTHERS THEN
715 IF l_debug_level > 0 THEN
716 oe_debug_pub.add ('Others in Get_Parent_Document_Id: '||SQLERRM);
717 END IF;
718 return NULL;
719 END;
720
721 -----------------------------------------------------------------
722 -- WORKFLOW APIS
723 -----------------------------------------------------------------
724
725 PROCEDURE OEEM_SELECTOR
726 ( p_itemtype in varchar2,
727 p_itemkey in varchar2,
728 p_actid in number,
729 p_funcmode in varchar2,
730 p_x_result in out NOCOPY /* file.sql.39 change */ varchar2
731 )
732 IS
733 l_user_id NUMBER;
734 l_resp_id NUMBER;
735 l_resp_appl_id NUMBER;
736 l_org_id NUMBER;
737 l_current_org_id NUMBER;
738 l_client_org_id NUMBER;
739 l_parameter1 NUMBER;
740
741 --
742 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
743 --
744 BEGIN
745
746 IF l_debug_level > 0 THEN
747 oe_debug_pub.add( 'ENTERING OEEM_SELECTOR PROCEDURE' ) ;
748
749 oe_debug_pub.add( 'THE WORKFLOW FUNCTION MODE IS: FUNCMODE='||P_FUNCMODE ) ;
750 END IF;
751
752 -- {
753 IF (p_funcmode = 'RUN') THEN
754
755 IF l_debug_level > 0 THEN
756 oe_debug_pub.add( 'P_FUNCMODE IS RUN' ) ;
757 END IF;
758 p_x_result := 'COMPLETE';
759
760 -- Engine calls SET_CTX just before activity execution
761
762 ELSIF(p_funcmode = 'SET_CTX') THEN
763
764 IF l_debug_level > 0 THEN
765 oe_debug_pub.add( 'P_FUNCMODE IS SET_CTX' ) ;
766 END IF;
767
768 l_org_id := wf_engine.GetItemAttrNumber( p_itemtype
769 , p_itemkey
770 , 'ORG_ID'
771 );
772
773
774 IF l_debug_level > 0 THEN
775 oe_debug_pub.add('l_org_id =>' || l_org_id);
776 END IF;
777
778 mo_global.set_policy_context(p_access_mode => 'S', p_org_id=>l_Org_Id);
779 p_x_result := 'COMPLETE';
780
781 ELSIF (p_funcmode = 'TEST_CTX') THEN
782
783 l_org_id := wf_engine.GetItemAttrNumber( p_itemtype
784 , p_itemkey
785 , 'ORG_ID'
786 );
787
788 IF l_debug_level > 0 THEN
789 oe_debug_pub.add( 'l_org_id (from workflow)=>'|| l_org_id ) ;
790 END IF;
791
792 IF (NVL(mo_global.get_current_org_id,-99) <> l_Org_Id)
793 THEN
794 p_x_result := 'FALSE';
795 ELSE
796 p_x_result := 'TRUE';
797 END IF;
798
799
800 END IF;
801 -- p_funcmode }
802
803 EXCEPTION
804 WHEN OTHERS THEN NULL;
805 WF_CORE.Context('OE_ELEC_MSGS_WF', 'OEEM_SELECTOR',
806 p_itemtype, p_itemkey, p_actid, p_funcmode);
807 RAISE;
808
809 END OEEM_SELECTOR;
810
811 PROCEDURE Create_Or_Update_Hist_WF (
812 p_itemtype IN VARCHAR2,
813 p_itemkey IN VARCHAR2,
814 p_actid IN NUMBER,
815 p_funcmode IN VARCHAR2,
816 p_x_result IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
817 IS
818 l_order_source_id NUMBER;
819 l_orig_sys_document_ref VARCHAR2(50);
820 l_sold_to_org_id NUMBER;
821 l_transaction_type VARCHAR2(30);
822 l_document_id NUMBER;
823 l_order_number NUMBER;
824 l_wf_itemtype VARCHAR2(8);
825 l_wf_itemkey VARCHAR2(240);
826 l_status VARCHAR2(240);
827 l_message_text VARCHAR2(2000);
828 l_xml_msg_id VARCHAR2(240);
829 l_org_id NUMBER;
830 l_result VARCHAR2(30);
831 l_processing VARCHAR2(30);
832 l_change_sequence VARCHAR2(50);
833 l_parent_document_id NUMBER;
834 l_response_flag VARCHAR2(1);
835 l_request_id NUMBER;
836 l_order_type_id NUMBER;
837 l_subscriber_list VARCHAR2(2000);
838 l_header_id NUMBER;
839 l_document_disposition VARCHAR2(20);
840 l_last_itemkey VARCHAR2(240);
841 l_last_request_id NUMBER;
842 l_curr_itemkey VARCHAR2(240) := p_itemkey;
843 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
844 l_order_processed_flag VARCHAR2(1) := NULL;
845 BEGIN
846 OE_STANDARD_WF.Set_Msg_Context(p_actid);
847
848 IF OE_Code_Control.Code_Release_Level < '110510' THEN
849 p_x_result := 'COMPLETE:COMPLETE';
850 Return;
851 END IF;
852
853
854 -- for bug 3103495, we screen events based on the subscriber list
855 -- parameter. Null subscriber list is also consumed.
856 l_subscriber_list := wf_engine.GetItemAttrText( p_itemtype
857 , p_itemkey
858 , 'SUBSCRIBER_LIST');
859 IF l_subscriber_list IS NOT NULL AND INSTR(l_subscriber_list,'ONT') = 0 THEN
860 p_x_result := 'COMPLETE:COMPLETE';
861 Return;
862 END IF;
863
864 -----------------------------------------------------------
865 -- KEY params for history
866 -----------------------------------------------------------
867 l_order_source_id := wf_engine.GetItemAttrNumber( p_itemtype
868 , p_itemkey
869 , 'ORDER_SOURCE_ID');
870
871 l_sold_to_org_id := wf_engine.GetItemAttrNumber( p_itemtype
872 , p_itemkey
873 , 'SOLD_TO_ORG_ID');
874 IF l_debug_level > 0 THEN
875 oe_debug_pub.add('before partner document no');
876 END IF;
877 l_orig_sys_document_ref := wf_engine.GetItemAttrText( p_itemtype
878 , p_itemkey
879 , 'PARTNER_DOCUMENT_NO');
880 IF l_debug_level > 0 THEN
881 oe_debug_pub.add('before trading partner id');
882 END IF;
883 l_transaction_type := wf_engine.GetItemAttrText( p_itemtype
884 , p_itemkey
885 , 'XMLG_INTERNAL_TXN_SUBTYPE');
886
887 IF l_transaction_type IN ('POA','CBODO', 'SSO','CSO', '855','865') THEN
888 l_document_id := wf_engine.GetItemAttrNumber( p_itemtype
889 , p_itemkey
890 , 'XMLG_DOCUMENT_ID');
891 ELSE
892 l_document_id := wf_engine.GetItemAttrNumber( p_itemtype
893 , p_itemkey
894 , 'XMLG_INTERNAL_CONTROL_NUMBER');
895 END IF;
896
897 -----------------------------------------------------------
898 -- NON-KEY params for history
899 -----------------------------------------------------------
900 l_wf_itemkey := wf_engine.GetItemAttrText ( p_itemtype
901 , p_itemkey
902 , 'WF_ITEM_KEY');
903 l_wf_itemtype := wf_engine.GetItemAttrText ( p_itemtype
904 , p_itemkey
905 , 'WF_ITEM_TYPE');
906
907 l_org_id := wf_engine.GetItemAttrNumber ( p_itemtype
908 , p_itemkey
909 , 'ORG_ID');
910 IF l_debug_level > 0 THEN
911 oe_debug_pub.add('after org id');
912 END IF;
913 IF l_org_id IS NULL THEN
914 /* MOAC_SQL_CHANGE */
915 -- SELECT TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1), ' ',
916 -- NULL, SUBSTRB(USERENV('CLIENT_INFO'),1,10))) into l_org_id from DUAL;
917 l_org_id := MO_GLOBAL.Get_Current_Org_Id;
918 IF l_debug_level > 0 THEN
919 oe_debug_pub.add('set org to :'||l_org_id);
920 END IF;
921 END IF;
922
923 l_order_number := wf_engine.GetItemAttrNumber( p_itemtype
924 , p_itemkey
925 , 'DOCUMENT_NO');
926 l_order_type_id := wf_engine.GetItemAttrNumber( p_itemtype
927 , p_itemkey
928 , 'ORDER_TYPE_ID');
929
930 IF l_order_number IS NULL THEN
931 IF l_debug_level > 0 THEN
932 oe_debug_pub.add('order number is null');
933 END IF;
934 END IF;
935 l_status := wf_engine.GetItemAttrText( p_itemtype
936 , p_itemkey
937 , 'ONT_DOC_STATUS');
938 l_message_text := wf_engine.GetItemAttrText( p_itemtype
939 , p_itemkey
940 , 'MESSAGE_TEXT');
941 l_processing := wf_engine.GetItemAttrText( p_itemtype
942 , p_itemkey
943 , 'PROCESSING_STAGE');
944 l_change_sequence := wf_engine.GetItemAttrText( p_itemtype
945 , p_itemkey
946 , 'DOCUMENT_REVISION_NO');
947 l_request_id := wf_engine.GetItemAttrNumber( p_itemtype
948 , p_itemkey
949 , 'CONC_REQUEST_ID');
950 l_header_id := wf_engine.GetItemAttrNumber( p_itemtype
951 , p_itemkey
952 , 'HEADER_ID');
953 l_response_flag := wf_engine.GetItemAttrText( p_itemtype
954 , p_itemkey
955 , 'RESPONSE_FLAG');
956 l_order_processed_flag := wf_engine.GetItemAttrText (p_itemtype
957 , p_itemkey
958 , 'ORDER_PROCESSED_FLAG'
959 , true);
960 -- Bug 4179657
961 -- Don't write the order number if the order was not imported successfully.
962 -- The IF condition also ensure backwards compatibility with EDI in the
963 -- case where the OM side of this fix is present, but the customer has not
964 -- taken the EDI side of the fix. In this case, l_order_processed_flag
965 -- will be NULL. This can occur when the event is raised by either the
966 -- EDI inbound or outbound spreadsheets, but in both cases we can
967 -- unambiguously write the order number since the inbound EDI
968 -- spreadsheets never populate the order number and the outbound
969 -- spreadsheets always do so since EDI doesn't have failure Acks.
970
971 IF l_order_processed_flag = 'N' THEN
972 l_order_number := NULL;
973 l_order_type_id := NULL;
974 l_header_id := NULL;
975 END IF;
976
977
978 -- start bug 3688227
979 OE_MSG_PUB.set_msg_context(
980 p_entity_code => 'HEADER'
981 ,p_entity_ref => null
982 ,p_entity_id => null
983 ,p_header_id => l_header_id
984 ,p_line_id => null
985 ,p_order_source_id => l_order_source_id
986 ,p_orig_sys_document_ref => l_orig_sys_document_ref
987 ,p_change_sequence => l_change_sequence
988 ,p_orig_sys_document_line_ref => null
989 ,p_orig_sys_shipment_ref => null
990 ,p_source_document_type_id => null
991 ,p_source_document_id => null
992 ,p_source_document_line_id => null
993 ,p_attribute_code => null
994 ,p_constraint_id => null
995 );
996 -- end bug 3688227
997
998 IF l_transaction_type = 'CHO'
999 AND l_response_flag = 'Y'
1000 AND FND_PROFILE.VALUE('ONT_3A7_RESPONSE_REQUIRED') = 'Y'
1001 THEN
1002 l_parent_document_id := OE_ELECMSGS_PVT.Find_Parent_Document_Id (l_order_source_id,
1003 l_orig_sys_document_ref,
1004 l_sold_to_org_id,
1005 l_org_id
1006 );
1007 END IF;
1008 IF l_debug_level > 0 THEN
1009 oe_debug_pub.add(l_order_Source_id||l_orig_sys_document_ref||l_sold_to_org_id||l_transaction_type||l_document_id||l_status||l_message_text||l_response_flag);
1010 END IF;
1011 -- here check if the record already exists for those key values
1012 -- if yes, then update, otherwise create
1013 OE_ELECMSGS_PVT.Create_History_Entry (
1014 p_order_source_id => l_order_source_id,
1015 p_sold_to_org_id => l_sold_to_org_id,
1016 p_orig_sys_document_ref => l_orig_sys_document_ref,
1017 p_transaction_type => l_transaction_type,
1018 p_document_id => l_document_id,
1019 p_parent_document_id => l_parent_document_id,
1020 p_change_sequence => l_change_sequence,
1021 p_order_number => l_order_number,
1022 p_order_type_id => l_order_type_id,
1023 p_itemtype => l_wf_itemtype,
1024 p_itemkey => l_wf_itemkey,
1025 p_org_id => l_org_id,
1026 p_status => l_status,
1027 p_message_text => l_message_text,
1028 p_request_id => l_request_id,
1029 p_header_id => l_header_id,
1030 p_document_disposition => l_document_disposition,
1031 p_last_update_itemkey => l_curr_itemkey,
1032 x_return_status => l_result
1033 );
1034
1035 IF l_result = FND_API.G_RET_STS_ERROR THEN -- this return status indicates duplicate index value
1036 IF OE_ELECMSGS_PVT.Find_History_Entry ( p_order_source_id => l_order_source_id,
1037 p_sold_to_org_id => l_sold_to_org_id,
1038 p_orig_sys_document_ref => l_orig_sys_document_ref,
1039 p_transaction_type => l_transaction_type,
1040 p_document_id => l_document_id,
1041 x_last_itemkey => l_last_itemkey,
1042 x_last_request_id => l_last_request_id) THEN
1043 IF l_curr_itemkey < nvl(l_last_itemkey, -1) THEN
1044 l_status := NULL;
1045 l_message_text := NULL;
1046 l_curr_itemkey := NULL;
1047 IF l_last_request_id IS NOT NULL THEN
1048 l_request_id := NULL;
1049 END IF;
1050 IF l_debug_level > 0 THEN
1051 oe_debug_pub.add('Out of sequence: p_itemkey : ' || p_itemkey || 'last_itemkey ' || l_last_itemkey);
1052 END IF;
1053 END IF;
1054
1055 OE_ELECMSGS_PVT.Update_History_Entry (
1056 p_order_source_id => l_order_source_id,
1057 p_sold_to_org_id => l_sold_to_org_id,
1058 p_orig_sys_document_ref => l_orig_sys_document_ref,
1059 p_transaction_type => l_transaction_type,
1060 p_document_id => l_document_id,
1061 -- p_parent_document_id => l_parent_document_id,
1062 p_change_sequence => l_change_sequence,
1063 p_order_number => l_order_number,
1064 p_order_type_id => l_order_type_id,
1065 p_itemtype => l_wf_itemtype,
1066 p_itemkey => l_wf_itemkey,
1067 p_org_id => l_org_id,
1068 p_status => l_status,
1069 p_message_text => l_message_text,
1070 p_request_id => l_request_id,
1071 p_header_id => l_header_id,
1072 p_document_disposition => l_document_disposition,
1073 p_last_update_itemkey => l_curr_itemkey,
1074 x_return_status => l_result
1075 );
1076 END IF;
1077 END IF;
1078 wf_engine.SetItemUserKey (p_itemtype,
1079 p_itemkey,
1080 l_order_source_id||','||l_sold_to_org_id||','||l_orig_sys_document_ref
1081 ||','||l_transaction_type||','||l_processing);
1082
1083 p_x_result := l_result;
1084
1085 EXCEPTION
1086 WHEN OTHERS THEN
1087 -- The line below records this function call in the error system
1088 -- in the case of an exception.
1089 wf_core.context('OE_Elecmsgs_Pvt', 'Create_Or_Update_Hist_WF',
1090 p_itemtype, p_itemkey, to_char(p_actid), p_funcmode);
1091 IF l_debug_level > 0 THEN
1092 oe_debug_pub.add('OTHERS in Create_Or_Update_Hist_WF ' || SQLERRM );
1093 END IF;
1094 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1095 OE_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, 'Create_Or_Update_Hist_Wf');
1096 END IF;
1097 -- don't put the error activity call for exception management
1098 -- since it doesn't make sense here
1099 OE_STANDARD_WF.Save_Messages;
1100 OE_STANDARD_WF.Clear_Msg_Context;
1101 --p_x_result := FND_API.G_RET_STS_UNEXP_ERROR;
1102
1103 raise;
1104
1105
1106 END Create_Or_Update_Hist_WF;
1107
1108 -----------------------------------------------------------------
1109 -- END WORKFLOW APIS
1110 -----------------------------------------------------------------
1111 -----------------------------------------------------------------
1112 -- CONC PGM API
1113 -----------------------------------------------------------------
1114
1115 PROCEDURE Open_Interface_Purge_Conc_Pgm
1116 ( errbuf OUT NOCOPY /* file.sql.39 change */ VARCHAR,
1117 retcode OUT NOCOPY /* file.sql.39 change */ NUMBER,
1118 p_operating_unit IN NUMBER DEFAULT NULL,
1119 p_view_name IN VARCHAR2,
1120 -- p_sold_to_org_id IN NUMBER,
1121 p_customer_number IN NUMBER,
1122 p_order_source_id IN NUMBER,
1123 p_default_org_id IN NUMBER DEFAULT NULL,
1124 p_process_null_org_id IN VARCHAR2 DEFAULT NULL,
1125 p_orig_sys_document_ref_from IN VARCHAR2,
1126 p_orig_sys_document_ref_to IN VARCHAR2,
1127 p_purge_child_tables IN VARCHAR2 DEFAULT NULL
1128 )
1129 IS
1130
1131 l_msg_count NUMBER := 0 ;
1132 l_msg_data VARCHAR2(2000):= NULL ;
1133 l_message_text VARCHAR2(2000);
1134
1135 l_filename VARCHAR2(200);
1136 l_request_id NUMBER;
1137 l_return_status VARCHAR2(30);
1138 --l_debug_level CONSTANT NUMBER := to_number(nvl(fnd_profile.value('ONT_DEBUG_LEVEL'),'0'));
1139 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1140
1141 l_sold_to_org_Id NUMBER := p_customer_number;
1142 l_header_iface BOOLEAN := FALSE;
1143 l_line_iface BOOLEAN := FALSE;
1144 l_header_cust_info BOOLEAN := FALSE;
1145 l_line_cust_info BOOLEAN := FALSE;
1146 l_header_credits BOOLEAN := FALSE;
1147 l_header_price_adj BOOLEAN := FALSE;
1148 l_header_price_att BOOLEAN := FALSE;
1149 l_header_reservtns BOOLEAN := FALSE;
1150 l_header_lotserial BOOLEAN := FALSE;
1151 l_header_actions BOOLEAN := FALSE;
1152 l_header_payments BOOLEAN := FALSE;
1153 l_line_credits BOOLEAN := FALSE;
1154 l_line_price_adj BOOLEAN := FALSE;
1155 l_line_price_att BOOLEAN := FALSE;
1156 l_line_reservtns BOOLEAN := FALSE;
1157 l_line_lotserial BOOLEAN := FALSE;
1158 l_line_actions BOOLEAN := FALSE;
1159 l_line_payments BOOLEAN := FALSE;
1160 l_elecmsgs BOOLEAN := FALSE;
1161 l_header_acks BOOLEAN := FALSE;
1162 l_line_acks BOOLEAN := FALSE;
1163 MIN_DOC_REF VARCHAR2(50) := ' ';
1164 MAX_DOC_REF VARCHAR2(50) := '999999999999999999';
1165 l_sold_to_org VARCHAR2(360);
1166 l_dummy_cust_no VARCHAR2(30);
1167 l_cust_rows NUMBER := 0;
1168 l_min_order_source_id NUMBER;
1169 l_max_order_source_id NUMBER;
1170 l_c_min_ord_src NUMBER;
1171 l_c_max_ord_src NUMBER;
1172 l_purge_child_tables VARCHAR2(1) := nvl(p_purge_child_tables,'Y');
1173 l_yes CONSTANT VARCHAR2(1) := 'Y';
1174 l_count NUMBER;
1175
1176 BEGIN
1177
1178 -----------------------------------------------------------
1179 -- Initialization
1180 -----------------------------------------------------------
1181 l_count := 0;
1182
1183 -----------------------------------------------------------
1184 -- Log Output file
1185 -----------------------------------------------------------
1186
1187 fnd_file.put_line(FND_FILE.OUTPUT, 'Purge Open Interface Data Concurrent Program');
1188 fnd_file.put_line(FND_FILE.OUTPUT, '');
1189 fnd_file.put_line(FND_FILE.OUTPUT, 'Concurrent Program Parameters');
1190 fnd_file.put_line(FND_FILE.OUTPUT, 'View Name: '||p_view_name);
1191 fnd_file.put_line(FND_FILE.OUTPUT, 'Purge Child Tables: '||l_purge_child_tables);
1192 fnd_file.put_line(FND_FILE.OUTPUT, 'Sold To Org Id: '|| p_customer_number);
1193 fnd_file.put_line(FND_FILE.OUTPUT, 'Order Source Id: '|| p_order_source_id);
1194 fnd_file.put_line(FND_FILE.OUTPUT, 'Orig Sys Document Ref From: '||p_orig_sys_document_ref_from);
1195 fnd_file.put_line(FND_FILE.OUTPUT, 'Orig Sys Document Ref To: '||p_orig_sys_document_ref_to);
1196 fnd_file.put_line(FND_FILE.OUTPUT, 'Org Id: '||p_operating_unit);
1197 fnd_file.put_line(FND_FILE.OUTPUT, 'Default Org Id: '||p_default_org_id);
1198 fnd_file.put_line(FND_FILE.OUTPUT, 'Process Records with No Org Specified: '||p_process_null_org_id);
1199 fnd_file.put_line(FND_FILE.OUTPUT, '');
1200 fnd_file.put_line(FND_FILE.OUTPUT,'Debug Level: '||l_debug_level);
1201
1202 -----------------------------------------------------------
1203 -- Setting Debug Mode and File
1204 -----------------------------------------------------------
1205
1206 If nvl(l_debug_level, 1) > 0 Then
1207 l_filename := oe_debug_pub.set_debug_mode ('FILE');
1208 fnd_file.put_line(FND_FILE.OUTPUT,'Debug File: ' || l_filename);
1209 fnd_file.put_line(FND_FILE.OUTPUT, '');
1210 l_filename := OE_DEBUG_PUB.set_debug_mode ('CONC');
1211 END IF;
1212
1213 -----------------------------------------------------------
1214 -- Get Concurrent Request Id
1215 -----------------------------------------------------------
1216 fnd_profile.get('CONC_REQUEST_ID', l_request_id);
1217 fnd_file.put_line(FND_FILE.OUTPUT, 'Request Id: '|| to_char(l_request_id));
1218 fnd_file.put_line(FND_FILE.OUTPUT, '');
1219 -----------------------------------------------------------
1220 -- Pre-processing
1221 -----------------------------------------------------------
1222
1223 -- Set the policy context
1224 IF p_operating_unit IS NOT NULL THEN
1225 MO_GLOBAL.Set_Policy_Context ('S', p_operating_unit);
1226 END IF;
1227
1228 /* this code is no longer necessary as the sold_to_org_id will now be passed in (bug 3396735)
1229 IF p_customer_number IS NOT NULL THEN
1230
1231 l_sold_to_org_id := OE_Value_To_Id.sold_to_org (p_sold_to_org => NULL,p_customer_number => p_customer_number);
1232 IF l_sold_to_org_Id = FND_API.G_MISS_NUM THEN
1233 IF l_debug_level > 0 THEN
1234 oe_debug_pub.add('Sold To Org Id cannot be derived - returning');
1235 END IF;
1236 retcode := 2;
1237 errbuf := SQLERRM;
1238 Return;
1239 END IF;
1240 END IF;
1241 */
1242 IF l_sold_to_org_id IS NOT NULL THEN
1243 OE_Id_To_Value.sold_to_org (p_sold_to_org_id => l_sold_to_org_id, x_org => l_sold_to_org, x_customer_number => l_dummy_cust_no);
1244 END IF;
1245 IF l_debug_level > 0 THEN
1246 oe_debug_pub.add ('Sold To Org Id : ' || l_sold_to_org_Id);
1247 oe_debug_pub.add ('Sold To Org derived : ' || l_sold_to_org);
1248 oe_debug_pub.add ('Org Id: ' || p_operating_unit);
1249 oe_debug_pub.add ('Default Org Id: ' || p_default_org_id);
1250 oe_debug_pub.add ('Process Records with No Org Specified: ' || p_process_null_org_id);
1251 END IF;
1252
1253 -----------------------------------------------------------
1254 -- Delete Rows
1255 -----------------------------------------------------------
1256 /* Depending on Table/View name (parameter p_view_name), execute the
1257 SQL statements given below */
1258
1259 IF p_view_name IN ('OE_HEADERS_INTERFACE') THEN
1260 l_header_iface := TRUE;
1261 IF l_purge_child_tables = 'Y' THEN
1262 l_header_price_adj := TRUE;
1263 l_header_cust_info := TRUE;
1264 l_header_credits := TRUE;
1265 l_header_price_att := TRUE;
1266 l_header_actions := TRUE;
1267 l_header_payments := TRUE;
1268 l_line_iface := TRUE;
1269 l_line_cust_info := TRUE;
1270 l_line_price_adj := TRUE;
1271 l_line_credits := TRUE;
1272 l_line_price_att := TRUE;
1273 l_line_reservtns := TRUE;
1274 l_line_lotserial := TRUE;
1275 l_line_actions := TRUE;
1276 l_line_payments := TRUE;
1277 END IF;
1278 END IF;
1279 IF p_view_name IN ('OE_LINES_INTERFACE') THEN
1280 l_line_iface := TRUE;
1281 IF l_purge_child_tables = 'Y' THEN
1282 l_line_cust_info := TRUE;
1283 l_line_price_adj := TRUE;
1284 l_line_credits := TRUE;
1285 l_line_price_att := TRUE;
1286 l_line_reservtns := TRUE;
1287 l_line_lotserial := TRUE;
1288 l_line_actions := TRUE;
1289 l_line_payments := TRUE;
1290 END IF;
1291 END IF;
1292 IF p_view_name IN ('OE_HEADER_ACKS') THEN
1293 l_header_acks := TRUE;
1294 IF l_purge_child_tables = 'Y' THEN
1295 l_line_acks := TRUE;
1296 END IF;
1297 END IF;
1298 IF p_view_name IN ('OE_LINE_ACKS') THEN
1299 l_line_acks := TRUE;
1300 END IF;
1301 IF p_view_name IN ('OE_EM_INFORMATION') THEN
1302 l_elecmsgs := TRUE;
1303 END IF;
1304
1305 /* to use the index on tracking and interface tables we gather the max and min order source id
1306 in appropriate cases */
1307 IF p_order_source_id IS NULL THEN
1308 IF p_view_name IN ('OE_HEADERS_INTERFACE', 'OE_LINES_INTERFACE','OE_EM_INFORMATION') THEN
1309
1310 SELECT max(order_source_id), min(order_source_id)
1311 INTO l_max_order_source_id, l_min_order_source_id
1312 FROM OE_Order_Sources;
1313
1314 IF l_debug_level > 0 THEN
1315 oe_debug_pub.add('Fetched min order source id ' || l_min_order_source_id || ' and max order source id ' || l_max_order_source_id);
1316 END IF;
1317 END IF;
1318 END IF;
1319
1320 IF l_header_cust_info THEN
1321
1322 IF p_order_source_id IS NOT NULL THEN
1323 l_c_min_ord_src := p_order_source_id;
1324 l_c_max_ord_src := p_order_source_id;
1325 ELSE
1326 l_c_min_ord_src := l_min_order_source_id;
1327 l_c_max_ord_src := l_max_order_source_id;
1328 END IF;
1329 DELETE
1330 FROM OE_Customer_Info_Iface_All c
1331 WHERE c.customer_info_ref IN (SELECT h.orig_sys_customer_ref
1332 FROM OE_Headers_Interface h
1333 WHERE order_source_id between l_c_min_ord_src and l_c_max_ord_src
1334 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
1335 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
1336 AND (l_sold_to_org_id IS NULL
1337 OR l_sold_to_org_id = sold_to_org_id
1338 OR l_sold_to_org = sold_to_org)
1339 AND h.orig_sys_customer_ref IS NOT NULL)
1340 AND c.customer_info_type_code = 'ACCOUNT';
1341 l_cust_rows := l_cust_rows + SQL%ROWCOUNT;
1342
1343 IF l_debug_level > 0 THEN
1344 oe_debug_pub.add('Deleted '|| l_cust_rows || ' records for customer ref with org_id');
1345 END IF;
1346
1347 -- Start MOAC
1348 IF p_process_null_org_id = 'Y' AND (p_operating_unit IS NULL OR p_operating_unit = p_default_org_id) THEN
1349 DELETE
1350 FROM OE_Customer_Info_Iface_All c
1351 WHERE c.customer_info_ref IN (SELECT h.orig_sys_customer_ref
1352 FROM OE_Headers_Iface_All h
1353 WHERE order_source_id between l_c_min_ord_src and l_c_max_ord_src
1354 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
1355 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
1356 AND (l_sold_to_org_id IS NULL
1357 OR l_sold_to_org_id = sold_to_org_id
1358 OR l_sold_to_org = sold_to_org)
1359 AND h.orig_sys_customer_ref IS NOT NULL
1360 AND h.org_id IS NULL)
1361 AND c.customer_info_type_code = 'ACCOUNT';
1362 l_cust_rows := l_cust_rows + SQL%ROWCOUNT;
1363 IF l_debug_level > 0 THEN
1364 oe_debug_pub.add('Deleted '|| SQL%ROWCOUNT || ' records with NULL Org Id');
1365 END IF;
1366 END IF;
1367 -- End MOAC
1368
1369 DELETE
1370 FROM OE_Customer_Info_Iface_All c
1371 WHERE c.customer_info_ref IN (SELECT h.sold_to_contact_ref
1372 FROM OE_Headers_Interface h
1373 WHERE order_source_id between l_c_min_ord_src and l_c_max_ord_src
1374 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
1375 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
1376 AND (l_sold_to_org_id IS NULL
1377 OR l_sold_to_org_id = sold_to_org_id
1378 OR l_sold_to_org = sold_to_org)
1379 AND h.sold_to_contact_ref IS NOT NULL)
1380 AND c.customer_info_type_code = 'CONTACT';
1381 l_cust_rows := l_cust_rows + SQL%ROWCOUNT;
1382 IF l_debug_level > 0 THEN
1383 oe_debug_pub.add('Deleted '|| SQL%ROWCOUNT || ' records for sold to contact with org id');
1384 END IF;
1385
1386 -- Start MOAC
1387 IF p_process_null_org_id = 'Y' AND (p_operating_unit IS NULL OR p_operating_unit = p_default_org_id) THEN
1388 DELETE
1389 FROM OE_Customer_Info_Iface_All c
1390 WHERE c.customer_info_ref IN (SELECT h.sold_to_contact_ref
1391 FROM OE_Headers_Iface_All h
1392 WHERE order_source_id between l_c_min_ord_src and l_c_max_ord_src
1393 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
1394 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
1395 AND (l_sold_to_org_id IS NULL
1396 OR l_sold_to_org_id = sold_to_org_id
1397 OR l_sold_to_org = sold_to_org)
1398 AND h.sold_to_contact_ref IS NOT NULL
1399 AND h.org_id IS NULL)
1400 AND c.customer_info_type_code = 'CONTACT';
1401 l_cust_rows := l_cust_rows + SQL%ROWCOUNT;
1402 IF l_debug_level > 0 THEN
1403 oe_debug_pub.add('Deleted '|| SQL%ROWCOUNT || ' records with NULL Org Id');
1404 END IF;
1405 END IF;
1406 -- End MOAC
1407
1408 DELETE
1409 FROM OE_Customer_Info_Iface_All c
1410 WHERE c.customer_info_ref IN (SELECT h.bill_to_contact_ref
1411 FROM OE_Headers_Interface h
1412 WHERE order_source_id between l_c_min_ord_src and l_c_max_ord_src
1413 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
1414 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
1415 AND (l_sold_to_org_id IS NULL
1416 OR l_sold_to_org_id = sold_to_org_id
1417 OR l_sold_to_org = sold_to_org)
1418 AND h.bill_to_contact_ref IS NOT NULL)
1419 AND c.customer_info_type_code = 'CONTACT';
1420 l_cust_rows := l_cust_rows + SQL%ROWCOUNT;
1421 IF l_debug_level > 0 THEN
1422 oe_debug_pub.add('Deleted '|| l_cust_rows || ' records for bill to contact ref with org id');
1423 END IF;
1424
1425 -- Start MOAC
1426 IF p_process_null_org_id = 'Y' AND (p_operating_unit IS NULL OR p_operating_unit = p_default_org_id) THEN
1427 DELETE
1428 FROM OE_Customer_Info_Iface_All c
1429 WHERE c.customer_info_ref IN (SELECT h.bill_to_contact_ref
1430 FROM OE_Headers_Iface_All h
1431 WHERE order_source_id between l_c_min_ord_src and l_c_max_ord_src
1432 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
1433 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
1434 AND (l_sold_to_org_id IS NULL
1435 OR l_sold_to_org_id = sold_to_org_id
1436 OR l_sold_to_org = sold_to_org)
1437 AND h.bill_to_contact_ref IS NOT NULL
1438 AND h.org_id IS NULL)
1439 AND c.customer_info_type_code = 'CONTACT';
1440 l_cust_rows := l_cust_rows + SQL%ROWCOUNT;
1441 IF l_debug_level > 0 THEN
1442 oe_debug_pub.add('Deleted '|| SQL%ROWCOUNT || ' records with NULL Org Id');
1443 END IF;
1444 END IF;
1445 -- End MOAC
1446
1447
1448 DELETE
1449 FROM OE_Customer_Info_Iface_All c
1450 WHERE c.customer_info_ref IN (SELECT h.ship_to_contact_ref
1451 FROM OE_Headers_Interface h
1452 WHERE order_source_id between l_c_min_ord_src and l_c_max_ord_src
1453 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
1454 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
1455 AND (l_sold_to_org_id IS NULL
1456 OR l_sold_to_org_id = sold_to_org_id
1457 OR l_sold_to_org = sold_to_org)
1458 AND h.ship_to_contact_ref IS NOT NULL)
1459 AND c.customer_info_type_code = 'CONTACT';
1460 l_cust_rows := l_cust_rows + SQL%ROWCOUNT;
1461 IF l_debug_level > 0 THEN
1462 oe_debug_pub.add('Deleted '|| l_cust_rows || ' records for ship to contact ref with org id');
1463 END IF;
1464
1465 -- Start MOAC
1466 IF p_process_null_org_id = 'Y' AND (p_operating_unit IS NULL OR p_operating_unit = p_default_org_id) THEN
1467 DELETE
1468 FROM OE_Customer_Info_Iface_All c
1469 WHERE c.customer_info_ref IN (SELECT h.ship_to_contact_ref
1470 FROM OE_Headers_Iface_All h
1471 WHERE order_source_id between l_c_min_ord_src and l_c_max_ord_src
1472 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
1473 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
1474 AND (l_sold_to_org_id IS NULL
1475 OR l_sold_to_org_id = sold_to_org_id
1476 OR l_sold_to_org = sold_to_org)
1477 AND h.ship_to_contact_ref IS NOT NULL
1478 AND h.org_id IS NULL)
1479 AND c.customer_info_type_code = 'CONTACT';
1480 l_cust_rows := l_cust_rows + SQL%ROWCOUNT;
1481 IF l_debug_level > 0 THEN
1482 oe_debug_pub.add('Deleted '|| SQL%ROWCOUNT || ' records with NULL Org Id');
1483 END IF;
1484 END IF;
1485 -- End MOAC
1486
1487 DELETE
1488 FROM OE_Customer_Info_Iface_All c
1489 WHERE c.customer_info_ref IN (SELECT h.deliver_to_contact_ref
1490 FROM OE_Headers_Interface h
1491 WHERE order_source_id between l_c_min_ord_src and l_c_max_ord_src
1492 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
1493 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
1494 AND (l_sold_to_org_id IS NULL
1495 OR l_sold_to_org_id = sold_to_org_id
1496 OR l_sold_to_org = sold_to_org)
1497 AND h.deliver_to_contact_ref IS NOT NULL)
1498 AND c.customer_info_type_code = 'CONTACT';
1499 l_cust_rows := l_cust_rows + SQL%ROWCOUNT;
1500 IF l_debug_level > 0 THEN
1501 oe_debug_pub.add('Deleted '|| l_cust_rows || ' records for deliver to contact ref with org id');
1502 END IF;
1503
1504 -- Start MOAC
1505 IF p_process_null_org_id = 'Y' AND (p_operating_unit IS NULL OR p_operating_unit = p_default_org_id) THEN
1506 DELETE
1507 FROM OE_Customer_Info_Iface_All c
1508 WHERE c.customer_info_ref IN (SELECT h.deliver_to_contact_ref
1509 FROM OE_Headers_Iface_All h
1510 WHERE order_source_id between l_c_min_ord_src and l_c_max_ord_src
1511 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
1512 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
1513 AND (l_sold_to_org_id IS NULL
1514 OR l_sold_to_org_id = sold_to_org_id
1515 OR l_sold_to_org = sold_to_org)
1516 AND h.deliver_to_contact_ref IS NOT NULL
1517 AND h.org_id IS NULL)
1518 AND c.customer_info_type_code = 'CONTACT';
1519 l_cust_rows := l_cust_rows + SQL%ROWCOUNT;
1520 IF l_debug_level > 0 THEN
1521 oe_debug_pub.add('Deleted '|| SQL%ROWCOUNT || ' records with NULL Org Id');
1522 END IF;
1523 END IF;
1524 -- End MOAC
1525
1526 DELETE
1527 FROM OE_Customer_Info_Iface_All c
1528 WHERE c.customer_info_ref IN (SELECT h.orig_ship_address_ref
1529 FROM OE_Headers_Interface h
1530 WHERE order_source_id between l_c_min_ord_src and l_c_max_ord_src
1531 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
1532 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
1533 AND (l_sold_to_org_id IS NULL
1534 OR l_sold_to_org_id = sold_to_org_id
1535 OR l_sold_to_org = sold_to_org)
1536 AND h.orig_ship_address_ref IS NOT NULL)
1537 AND c.customer_info_type_code = 'ADDRESS';
1538 l_cust_rows := l_cust_rows + SQL%ROWCOUNT;
1539
1540 IF l_debug_level > 0 THEN
1541 oe_debug_pub.add('Deleted '|| l_cust_rows || ' records for ship address ref with org id');
1542 END IF;
1543
1544 -- Start MOAC
1545 IF p_process_null_org_id = 'Y' AND (p_operating_unit IS NULL OR p_operating_unit = p_default_org_id) THEN
1546 DELETE
1547 FROM OE_Customer_Info_Iface_All c
1548 WHERE c.customer_info_ref IN (SELECT h.orig_ship_address_ref
1549 FROM OE_Headers_Iface_All h
1550 WHERE order_source_id between l_c_min_ord_src and l_c_max_ord_src
1551 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
1552 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
1553 AND (l_sold_to_org_id IS NULL
1554 OR l_sold_to_org_id = sold_to_org_id
1555 OR l_sold_to_org = sold_to_org)
1556 AND h.orig_ship_address_ref IS NOT NULL
1557 AND h.org_id IS NULL)
1558 AND c.customer_info_type_code = 'ADDRESS';
1559 l_cust_rows := l_cust_rows + SQL%ROWCOUNT;
1560 IF l_debug_level > 0 THEN
1561 oe_debug_pub.add('Deleted '|| SQL%ROWCOUNT || ' records with NULL Org Id');
1562 END IF;
1563 END IF;
1564 -- End MOAC
1565
1566 DELETE
1567 FROM OE_Customer_Info_Iface_All c
1568 WHERE c.customer_info_ref IN (SELECT h.orig_bill_address_ref
1569 FROM OE_Headers_Interface h
1570 WHERE order_source_id between l_c_min_ord_src and l_c_max_ord_src
1571 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
1572 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
1573 AND (l_sold_to_org_id IS NULL
1574 OR l_sold_to_org_id = sold_to_org_id
1575 OR l_sold_to_org = sold_to_org)
1576 AND h.orig_bill_address_ref IS NOT NULL)
1577 AND c.customer_info_type_code = 'ADDRESS';
1578 l_cust_rows := l_cust_rows + SQL%ROWCOUNT;
1579 IF l_debug_level > 0 THEN
1580 oe_debug_pub.add('Deleted '|| l_cust_rows || ' records for bill address ref with org id');
1581 END IF;
1582
1583 -- Start MOAC
1584 IF p_process_null_org_id = 'Y' AND (p_operating_unit IS NULL OR p_operating_unit = p_default_org_id) THEN
1585 DELETE
1586 FROM OE_Customer_Info_Iface_All c
1587 WHERE c.customer_info_ref IN (SELECT h.orig_bill_address_ref
1588 FROM OE_Headers_Iface_All h
1589 WHERE order_source_id between l_c_min_ord_src and l_c_max_ord_src
1590 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
1591 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
1592 AND (l_sold_to_org_id IS NULL
1593 OR l_sold_to_org_id = sold_to_org_id
1594 OR l_sold_to_org = sold_to_org)
1595 AND h.orig_bill_address_ref IS NOT NULL
1596 AND h.org_id IS NULL)
1597 AND c.customer_info_type_code = 'ADDRESS';
1598 l_cust_rows := l_cust_rows + SQL%ROWCOUNT;
1599 IF l_debug_level > 0 THEN
1600 oe_debug_pub.add('Deleted '|| SQL%ROWCOUNT || ' records with NULL Org Id');
1601 END IF;
1602 END IF;
1603 -- End MOAC
1604
1605 DELETE
1606 FROM OE_Customer_Info_Iface_All c
1607 WHERE c.customer_info_ref IN (SELECT h.orig_deliver_address_ref
1608 FROM OE_Headers_Interface h
1609 WHERE order_source_id between l_c_min_ord_src and l_c_max_ord_src
1610 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
1611 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
1612 AND (l_sold_to_org_id IS NULL
1613 OR l_sold_to_org_id = sold_to_org_id
1614 OR l_sold_to_org = sold_to_org)
1615 AND h.orig_deliver_address_ref IS NOT NULL)
1616 AND c.customer_info_type_code = 'ADDRESS';
1617 l_cust_rows := l_cust_rows + SQL%ROWCOUNT;
1618 IF l_debug_level > 0 THEN
1619 oe_debug_pub.add('Deleted '|| l_cust_rows || ' records for deliver address ref with org id');
1620 END IF;
1621
1622 -- Start MOAC
1623 IF p_process_null_org_id = 'Y' AND (p_operating_unit IS NULL OR p_operating_unit = p_default_org_id) THEN
1624 DELETE
1625 FROM OE_Customer_Info_Iface_All c
1626 WHERE c.customer_info_ref IN (SELECT h.orig_deliver_address_ref
1627 FROM OE_Headers_Iface_All h
1628 WHERE order_source_id between l_c_min_ord_src and l_c_max_ord_src
1629 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
1630 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
1631 AND (l_sold_to_org_id IS NULL
1632 OR l_sold_to_org_id = sold_to_org_id
1633 OR l_sold_to_org = sold_to_org)
1634 AND h.orig_deliver_address_ref IS NOT NULL
1635 AND h.org_id IS NULL)
1636 AND c.customer_info_type_code = 'ADDRESS';
1637 l_cust_rows := l_cust_rows + SQL%ROWCOUNT;
1638 IF l_debug_level > 0 THEN
1639 oe_debug_pub.add('Deleted '|| SQL%ROWCOUNT || ' records with NULL Org Id');
1640 END IF;
1641 END IF;
1642 -- End MOAC
1643
1644 IF l_debug_level > 0 THEN
1645 oe_debug_pub.add(l_cust_rows || ' rows deleted');
1646 END IF;
1647 fnd_file.put_line(FND_FILE.OUTPUT, 'Rows Deleted from Customer Info Interface (Header): '|| l_cust_rows);
1648 END IF;
1649
1650 IF l_header_iface THEN
1651 IF l_debug_level > 0 THEN
1652 oe_debug_pub.add('Deleting from OE_HEADERS_INTERFACE');
1653 END IF;
1654 l_count := 0;
1655
1656 -- For the interface tables, we delete if either the sold to org or sold to org id
1657 -- are populated with the value corresponding to the conc pgm
1658 -- For the other tables, the data is always populated internally
1659 -- and hence we can always be sure that sold_to_org_id is populated, if at all
1660 IF p_order_source_id IS NOT NULL THEN
1661 DELETE
1662 FROM OE_Headers_Interface
1663 WHERE order_source_id = p_order_source_id
1664 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
1665 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
1666 AND (l_sold_to_org_id IS NULL
1667 OR l_sold_to_org_id = sold_to_org_id
1668 OR l_sold_to_org = sold_to_org);
1669 l_count := l_count + SQL%ROWCOUNT;
1670
1671 -- Start MOAC
1672 IF p_process_null_org_id = 'Y' AND (p_operating_unit IS NULL OR p_operating_unit = p_default_org_id) THEN
1673 DELETE
1674 FROM OE_Headers_Iface_All
1675 WHERE order_source_id = p_order_source_id
1676 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
1677 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
1678 AND (l_sold_to_org_id IS NULL
1679 OR l_sold_to_org_id = sold_to_org_id
1680 OR l_sold_to_org = sold_to_org)
1681 AND org_id IS NULL;
1682 l_count := l_count + SQL%ROWCOUNT;
1683 IF l_debug_level > 0 THEN
1684 oe_debug_pub.add('Deleted '|| SQL%ROWCOUNT || ' records with NULL Org Id');
1685 END IF;
1686 END IF;
1687 -- End MOAC
1688 ELSE
1689 DELETE
1690 FROM OE_Headers_Interface
1691 WHERE order_source_id BETWEEN l_min_order_source_id and l_max_order_source_id
1692 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
1693 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
1694 AND (l_sold_to_org_id IS NULL
1695 OR l_sold_to_org_id = sold_to_org_id
1696 OR l_sold_to_org = sold_to_org);
1697 l_count := l_count + SQL%ROWCOUNT;
1698
1699 -- Start MOAC
1700 IF p_process_null_org_id = 'Y' AND (p_operating_unit IS NULL OR p_operating_unit = p_default_org_id) THEN
1701 DELETE
1702 FROM OE_Headers_Iface_All
1703 WHERE order_source_id BETWEEN l_min_order_source_id and l_max_order_source_id
1704 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
1705 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
1706 AND (l_sold_to_org_id IS NULL
1707 OR l_sold_to_org_id = sold_to_org_id
1708 OR l_sold_to_org = sold_to_org)
1709 AND org_id IS NULL;
1710 l_count := l_count + SQL%ROWCOUNT;
1711 IF l_debug_level > 0 THEN
1712 oe_debug_pub.add('Deleted '|| SQL%ROWCOUNT || ' records with NULL Org Id');
1713 END IF;
1714 END IF;
1715 -- End MOAC
1716 END IF;
1717
1718
1719 IF l_debug_level > 0 THEN
1720 oe_debug_pub.add(l_count || ' rows deleted');
1721 END IF;
1722 fnd_file.put_line(FND_FILE.OUTPUT, 'Rows Deleted from Headers Interface: '|| l_count);
1723 END IF;
1724
1725 IF l_header_actions THEN
1726 IF l_debug_level > 0 THEN
1727 oe_debug_pub.add('Deleting Header records from OE_ACTIONS_INTERFACE');
1728 END IF;
1729 l_count := 0;
1730
1731 IF p_order_source_id IS NOT NULL THEN
1732 DELETE
1733 FROM OE_Actions_Interface
1734 WHERE order_source_id = p_order_source_id
1735 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
1736 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
1737 AND orig_sys_line_ref IS NULL
1738 AND (l_sold_to_org_id IS NULL
1739 OR l_sold_to_org_id = sold_to_org_id
1740 OR l_sold_to_org = sold_to_org);
1741 l_count := l_count + SQL%ROWCOUNT;
1742
1743
1744 -- Start MOAC
1745 IF p_process_null_org_id = 'Y' AND (p_operating_unit IS NULL OR p_operating_unit = p_default_org_id) THEN
1746 DELETE
1747 FROM OE_Actions_Iface_All
1748 WHERE order_source_id = p_order_source_id
1749 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
1750 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
1751 AND orig_sys_line_ref IS NULL
1752 AND (l_sold_to_org_id IS NULL
1753 OR l_sold_to_org_id = sold_to_org_id
1754 OR l_sold_to_org = sold_to_org)
1755 AND org_id IS NULL;
1756 l_count := l_count + SQL%ROWCOUNT;
1757 IF l_debug_level > 0 THEN
1758 oe_debug_pub.add('Deleted '|| SQL%ROWCOUNT || ' records with NULL Org Id');
1759 END IF;
1760 END IF;
1761 -- End MOAC
1762 ELSE
1763 DELETE
1764 FROM OE_Actions_Interface
1765 WHERE order_source_id BETWEEN l_min_order_source_id AND l_max_order_source_id
1766 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
1767 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
1768 AND orig_sys_line_ref IS NULL
1769 AND (l_sold_to_org_id IS NULL
1770 OR l_sold_to_org_id = sold_to_org_id
1771 OR l_sold_to_org = sold_to_org);
1772 l_count := l_count + SQL%ROWCOUNT;
1773
1774 -- Start MOAC
1775 IF p_process_null_org_id = 'Y' AND (p_operating_unit IS NULL OR p_operating_unit = p_default_org_id) THEN
1776 DELETE
1777 FROM OE_Actions_Iface_All
1778 WHERE order_source_id BETWEEN l_min_order_source_id AND l_max_order_source_id
1779 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
1780 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
1781 AND orig_sys_line_ref IS NULL
1782 AND (l_sold_to_org_id IS NULL
1783 OR l_sold_to_org_id = sold_to_org_id
1784 OR l_sold_to_org = sold_to_org)
1785 AND org_id IS NULL;
1786 l_count := l_count + SQL%ROWCOUNT;
1787 IF l_debug_level > 0 THEN
1788 oe_debug_pub.add('Deleted '|| SQL%ROWCOUNT || ' records with NULL Org Id');
1789 END IF;
1790 END IF;
1791 -- End MOAC
1792 END IF;
1793
1794 IF l_debug_level > 0 THEN
1795 oe_debug_pub.add(SQL%ROWCOUNT || ' rows deleted');
1796 END IF;
1797 fnd_file.put_line(FND_FILE.OUTPUT, 'Rows Deleted from Actions Interface (Header): '|| l_count);
1798 END IF;
1799 IF l_header_credits THEN
1800 IF l_debug_level > 0 THEN
1801 oe_debug_pub.add('Deleting Header records from OE_CREDITS_INTERFACE');
1802 END IF;
1803 l_count := 0;
1804
1805 IF p_order_source_id IS NOT NULL THEN
1806 DELETE
1807 FROM OE_Credits_Interface
1808 WHERE order_source_id = p_order_source_id
1809 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
1810 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
1811 AND orig_sys_line_ref IS NULL
1812 AND (l_sold_to_org_id IS NULL
1813 OR l_sold_to_org_id = sold_to_org_id
1814 OR l_sold_to_org = sold_to_org);
1815 l_count := l_count + SQL%ROWCOUNT;
1816
1817 -- Start MOAC
1818 IF p_process_null_org_id = 'Y' AND (p_operating_unit IS NULL OR p_operating_unit = p_default_org_id) THEN
1819 DELETE
1820 FROM OE_Credits_Iface_All
1821 WHERE order_source_id = p_order_source_id
1822 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
1823 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
1824 AND orig_sys_line_ref IS NULL
1825 AND (l_sold_to_org_id IS NULL
1826 OR l_sold_to_org_id = sold_to_org_id
1827 OR l_sold_to_org = sold_to_org)
1828 AND org_id IS NULL;
1829 l_count := l_count + SQL%ROWCOUNT;
1830 IF l_debug_level > 0 THEN
1831 oe_debug_pub.add('Deleted '|| SQL%ROWCOUNT || ' records with NULL Org Id');
1832 END IF;
1833 END IF;
1834 -- End MOAC
1835 ELSE
1836 DELETE
1837 FROM OE_Credits_Interface
1838 WHERE order_source_id BETWEEN l_min_order_source_id AND l_max_order_source_id
1839 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
1840 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
1841 AND orig_sys_line_ref IS NULL
1842 AND (l_sold_to_org_id IS NULL
1843 OR l_sold_to_org_id = sold_to_org_id
1844 OR l_sold_to_org = sold_to_org);
1845 l_count := l_count + SQL%ROWCOUNT;
1846
1847 -- Start MOAC
1848 IF p_process_null_org_id = 'Y' AND (p_operating_unit IS NULL OR p_operating_unit = p_default_org_id) THEN
1849 DELETE
1850 FROM OE_Credits_Iface_All
1851 WHERE order_source_id BETWEEN l_min_order_source_id AND l_max_order_source_id
1852 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
1853 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
1854 AND orig_sys_line_ref IS NULL
1855 AND (l_sold_to_org_id IS NULL
1856 OR l_sold_to_org_id = sold_to_org_id
1857 OR l_sold_to_org = sold_to_org)
1858 AND org_id IS NULL;
1859 l_count := l_count + SQL%ROWCOUNT;
1860 IF l_debug_level > 0 THEN
1861 oe_debug_pub.add('Deleted '|| SQL%ROWCOUNT || ' records with NULL Org Id');
1862 END IF;
1863 END IF;
1864 -- End MOAC
1865 END IF;
1866
1867 IF l_debug_level > 0 THEN
1868 oe_debug_pub.add(l_count || ' rows deleted');
1869 END IF;
1870 fnd_file.put_line(FND_FILE.OUTPUT, 'Rows Deleted from Credits Interface (Header): '|| l_count);
1871 END IF;
1872 IF l_header_price_adj THEN
1873 IF l_debug_level > 0 THEN
1874 oe_debug_pub.add('Deleting Header records from OE_PRICE_ADJS_INTERFACE');
1875 END IF;
1876 l_count := 0;
1877
1878 IF p_order_source_id IS NOT NULL THEN
1879 DELETE
1880 FROM OE_Price_Adjs_Interface
1881 WHERE order_source_id = p_order_source_id
1882 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
1883 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
1884 AND orig_sys_line_ref IS NULL
1885 AND (l_sold_to_org_id IS NULL
1886 OR l_sold_to_org_id = sold_to_org_id
1887 OR l_sold_to_org = sold_to_org);
1888 l_count := l_count + SQL%ROWCOUNT;
1889
1890 -- Start MOAC
1891 IF p_process_null_org_id = 'Y' AND (p_operating_unit IS NULL OR p_operating_unit = p_default_org_id) THEN
1892 DELETE
1893 FROM OE_Price_Adjs_Iface_All
1894 WHERE order_source_id = p_order_source_id
1895 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
1896 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
1897 AND orig_sys_line_ref IS NULL
1898 AND (l_sold_to_org_id IS NULL
1899 OR l_sold_to_org_id = sold_to_org_id
1900 OR l_sold_to_org = sold_to_org)
1901 AND org_id IS NULL;
1902 l_count := l_count + SQL%ROWCOUNT;
1903 IF l_debug_level > 0 THEN
1904 oe_debug_pub.add('Deleted '|| SQL%ROWCOUNT || ' records with NULL Org Id');
1905 END IF;
1906 END IF;
1907 -- End MOAC
1908 ELSE
1909 DELETE
1910 FROM OE_Price_Adjs_Interface
1911 WHERE order_source_id BETWEEN l_min_order_source_id AND l_max_order_source_id
1912 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
1913 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
1914 AND orig_sys_line_ref IS NULL
1915 AND (l_sold_to_org_id IS NULL
1916 OR l_sold_to_org_id = sold_to_org_id
1917 OR l_sold_to_org = sold_to_org);
1918 l_count := l_count + SQL%ROWCOUNT;
1919
1920 -- Start MOAC
1921 IF p_process_null_org_id = 'Y' AND (p_operating_unit IS NULL OR p_operating_unit = p_default_org_id) THEN
1922 DELETE
1923 FROM OE_Price_Adjs_Iface_All
1924 WHERE order_source_id BETWEEN l_min_order_source_id AND l_max_order_source_id
1925 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
1926 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
1927 AND orig_sys_line_ref IS NULL
1928 AND (l_sold_to_org_id IS NULL
1929 OR l_sold_to_org_id = sold_to_org_id
1930 OR l_sold_to_org = sold_to_org)
1931 AND org_id IS NULL;
1932 l_count := l_count + SQL%ROWCOUNT;
1933 IF l_debug_level > 0 THEN
1934 oe_debug_pub.add('Deleted '|| SQL%ROWCOUNT || ' records with NULL Org Id');
1935 END IF;
1936 END IF;
1937 -- End MOAC
1938 END IF;
1939
1940 IF l_debug_level > 0 THEN
1941 oe_debug_pub.add(l_count || ' rows deleted');
1942 END IF;
1943 fnd_file.put_line(FND_FILE.OUTPUT, 'Rows Deleted from Price Adjustments Interface (Header): '|| l_count);
1944 END IF;
1945 IF l_header_price_att THEN
1946 IF l_debug_level > 0 THEN
1947 oe_debug_pub.add('Deleting Header records from OE_PRICE_ATTS_INTERFACE');
1948 END IF;
1949 l_count := 0;
1950
1951 IF p_order_source_id IS NOT NULL THEN
1952 DELETE
1953 FROM OE_Price_Atts_Interface
1954 WHERE order_source_id = p_order_source_id
1955 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
1956 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
1957 AND orig_sys_line_ref IS NULL
1958 AND (l_sold_to_org_id IS NULL
1959 OR l_sold_to_org_id = sold_to_org_id
1960 OR l_sold_to_org = sold_to_org);
1961 l_count := l_count + SQL%ROWCOUNT;
1962
1963 -- Start MOAC
1964 IF p_process_null_org_id = 'Y' AND (p_operating_unit IS NULL OR p_operating_unit = p_default_org_id) THEN
1965 DELETE
1966 FROM OE_Price_Atts_Iface_All
1967 WHERE order_source_id = p_order_source_id
1968 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
1969 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
1970 AND orig_sys_line_ref IS NULL
1971 AND (l_sold_to_org_id IS NULL
1972 OR l_sold_to_org_id = sold_to_org_id
1973 OR l_sold_to_org = sold_to_org)
1974 AND org_id IS NULL;
1975 l_count := l_count + SQL%ROWCOUNT;
1976 IF l_debug_level > 0 THEN
1977 oe_debug_pub.add('Deleted '|| SQL%ROWCOUNT || ' records with NULL Org Id');
1978 END IF;
1979 END IF;
1980 -- End MOAC
1981 ELSE
1982 DELETE
1983 FROM OE_Price_Atts_Interface
1984 WHERE order_source_id BETWEEN l_min_order_source_id and l_max_order_source_id
1985 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
1986 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
1987 AND orig_sys_line_ref IS NULL
1988 AND (l_sold_to_org_id IS NULL
1989 OR l_sold_to_org_id = sold_to_org_id
1990 OR l_sold_to_org = sold_to_org);
1991 l_count := l_count + SQL%ROWCOUNT;
1992
1993 -- Start MOAC
1994 IF p_process_null_org_id = 'Y' AND (p_operating_unit IS NULL OR p_operating_unit = p_default_org_id) THEN
1995 DELETE
1996 FROM OE_Price_Atts_Iface_All
1997 WHERE order_source_id BETWEEN l_min_order_source_id and l_max_order_source_id
1998 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
1999 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
2000 AND orig_sys_line_ref IS NULL
2001 AND (l_sold_to_org_id IS NULL
2002 OR l_sold_to_org_id = sold_to_org_id
2003 OR l_sold_to_org = sold_to_org)
2004 AND org_id IS NULL;
2005 l_count := l_count + SQL%ROWCOUNT;
2006 IF l_debug_level > 0 THEN
2007 oe_debug_pub.add('Deleted '|| SQL%ROWCOUNT || ' records with NULL Org Id');
2008 END IF;
2009 END IF;
2010 -- End MOAC
2011 END IF;
2012
2013 IF l_debug_level > 0 THEN
2014 oe_debug_pub.add(l_count || ' rows deleted');
2015 END IF;
2016 fnd_file.put_line(FND_FILE.OUTPUT, 'Rows Deleted from Pricing Attributes Interface (Header): '|| l_count);
2017 END IF;
2018
2019 IF l_header_payments THEN
2020 IF l_debug_level > 0 THEN
2021 oe_debug_pub.add('Deleting Header records from OE_PAYMENTS_INTERFACE');
2022 END IF;
2023 l_count := 0;
2024
2025 IF p_order_source_id IS NOT NULL THEN
2026 DELETE
2027 FROM OE_Payments_Interface
2028 WHERE order_source_id = p_order_source_id
2029 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
2030 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
2031 AND orig_sys_line_ref IS NULL
2032 AND (l_sold_to_org_id IS NULL
2033 OR l_sold_to_org_id = sold_to_org_id
2034 OR l_sold_to_org = sold_to_org);
2035 l_count := l_count + SQL%ROWCOUNT;
2036
2037 -- Start MOAC
2038 IF p_process_null_org_id = 'Y' AND (p_operating_unit IS NULL OR p_operating_unit = p_default_org_id) THEN
2039 DELETE
2040 FROM OE_Payments_Iface_All
2041 WHERE order_source_id = p_order_source_id
2042 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
2043 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
2044 AND orig_sys_line_ref IS NULL
2045 AND (l_sold_to_org_id IS NULL
2046 OR l_sold_to_org_id = sold_to_org_id
2047 OR l_sold_to_org = sold_to_org)
2048 AND org_id IS NULL;
2049 l_count := l_count + SQL%ROWCOUNT;
2050 IF l_debug_level > 0 THEN
2051 oe_debug_pub.add('Deleted '|| SQL%ROWCOUNT || ' records with NULL Org Id');
2052 END IF;
2053 END IF;
2054 -- End MOAC
2055 ELSE
2056 DELETE
2057 FROM OE_Payments_Interface
2058 WHERE order_source_id BETWEEN l_min_order_source_id AND l_max_order_source_id
2059 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
2060 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
2061 AND orig_sys_line_ref IS NULL
2062 AND (l_sold_to_org_id IS NULL
2063 OR l_sold_to_org_id = sold_to_org_id
2064 OR l_sold_to_org = sold_to_org);
2065 l_count := l_count + SQL%ROWCOUNT;
2066
2067 -- Start MOAC
2068 IF p_process_null_org_id = 'Y' AND (p_operating_unit IS NULL OR p_operating_unit = p_default_org_id) THEN
2069 DELETE
2070 FROM OE_Payments_Iface_All
2071 WHERE order_source_id BETWEEN l_min_order_source_id AND l_max_order_source_id
2072 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
2073 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
2074 AND orig_sys_line_ref IS NULL
2075 AND (l_sold_to_org_id IS NULL
2076 OR l_sold_to_org_id = sold_to_org_id
2077 OR l_sold_to_org = sold_to_org)
2078 AND org_id IS NULL;
2079 l_count := l_count + SQL%ROWCOUNT;
2080 IF l_debug_level > 0 THEN
2081 oe_debug_pub.add('Deleted '|| SQL%ROWCOUNT || ' records with NULL Org Id');
2082 END IF;
2083 END IF;
2084 -- End MOAC
2085 END IF;
2086
2087 IF l_debug_level > 0 THEN
2088 oe_debug_pub.add(l_count || ' rows deleted');
2089 END IF;
2090 fnd_file.put_line(FND_FILE.OUTPUT, 'Rows Deleted from Payments Interface (Header): '|| l_count);
2091 END IF;
2092
2093 IF l_line_cust_info THEN
2094 l_cust_rows := 0; -- reinitialize
2095
2096 IF p_order_source_id IS NOT NULL THEN
2097 l_c_min_ord_src := p_order_source_id;
2098 l_c_max_ord_src := p_order_source_id;
2099 ELSE
2100 l_c_min_ord_src := l_min_order_source_id;
2101 l_c_max_ord_src := l_max_order_source_id;
2102 END IF;
2103
2104
2105 DELETE
2106 FROM OE_Customer_Info_Iface_All c
2107 WHERE c.customer_info_ref IN (SELECT h.bill_to_contact_ref
2108 FROM OE_Lines_Interface h
2109 WHERE order_source_id between l_c_min_ord_src and l_c_max_ord_src
2110 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
2111 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
2112 AND (l_sold_to_org_id IS NULL
2113 OR l_sold_to_org_id = sold_to_org_id
2114 OR l_sold_to_org = sold_to_org)
2115 AND h.bill_to_contact_ref IS NOT NULL)
2116 AND c.customer_info_type_code = 'CONTACT';
2117 l_cust_rows := l_cust_rows + SQL%ROWCOUNT;
2118 IF l_debug_level > 0 THEN
2119 oe_debug_pub.add('Deleted '|| l_cust_rows || ' records for bill to contact ref with org id');
2120 END IF;
2121
2122 -- Start MOAC
2123 IF p_process_null_org_id = 'Y' AND (p_operating_unit IS NULL OR p_operating_unit = p_default_org_id) THEN
2124 DELETE
2125 FROM OE_Customer_Info_Iface_All c
2126 WHERE c.customer_info_ref IN (SELECT h.bill_to_contact_ref
2127 FROM OE_Lines_Iface_All h
2128 WHERE order_source_id between l_c_min_ord_src and l_c_max_ord_src
2129 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
2130 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
2131 AND (l_sold_to_org_id IS NULL
2132 OR l_sold_to_org_id = sold_to_org_id
2133 OR l_sold_to_org = sold_to_org)
2134 AND h.bill_to_contact_ref IS NOT NULL
2135 AND h.org_id IS NULL)
2136 AND c.customer_info_type_code = 'CONTACT';
2137 l_cust_rows := l_cust_rows + SQL%ROWCOUNT;
2138 IF l_debug_level > 0 THEN
2139 oe_debug_pub.add('Deleted '|| SQL%ROWCOUNT || ' records with NULL Org Id');
2140 END IF;
2141 END IF;
2142 -- End MOAC
2143
2144 DELETE
2145 FROM OE_Customer_Info_Iface_All c
2146 WHERE c.customer_info_ref IN (SELECT h.ship_to_contact_ref
2147 FROM OE_Lines_Interface h
2148 WHERE order_source_id between l_c_min_ord_src and l_c_max_ord_src
2149 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
2150 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
2151 AND (l_sold_to_org_id IS NULL
2152 OR l_sold_to_org_id = sold_to_org_id
2153 OR l_sold_to_org = sold_to_org)
2154 AND h.ship_to_contact_ref IS NOT NULL)
2155 AND c.customer_info_type_code = 'CONTACT';
2156 l_cust_rows := l_cust_rows + SQL%ROWCOUNT;
2157 IF l_debug_level > 0 THEN
2158 oe_debug_pub.add('Deleted '|| l_cust_rows || ' records for ship to contact ref with org id');
2159 END IF;
2160
2161 -- Start MOAC
2162 IF p_process_null_org_id = 'Y' AND (p_operating_unit IS NULL OR p_operating_unit = p_default_org_id) THEN
2163 DELETE
2164 FROM OE_Customer_Info_Iface_All c
2165 WHERE c.customer_info_ref IN (SELECT h.ship_to_contact_ref
2166 FROM OE_Lines_Iface_All h
2167 WHERE order_source_id between l_c_min_ord_src and l_c_max_ord_src
2168 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
2169 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
2170 AND (l_sold_to_org_id IS NULL
2171 OR l_sold_to_org_id = sold_to_org_id
2172 OR l_sold_to_org = sold_to_org)
2173 AND h.ship_to_contact_ref IS NOT NULL
2174 AND h.org_id IS NULL)
2175 AND c.customer_info_type_code = 'CONTACT';
2176 l_cust_rows := l_cust_rows + SQL%ROWCOUNT;
2177 IF l_debug_level > 0 THEN
2178 oe_debug_pub.add('Deleted '|| SQL%ROWCOUNT || ' records with NULL Org Id');
2179 END IF;
2180 END IF;
2181 -- End MOAC
2182
2183 DELETE
2184 FROM OE_Customer_Info_Iface_All c
2185 WHERE c.customer_info_ref IN (SELECT h.deliver_to_contact_ref
2186 FROM OE_Lines_Interface h
2187 WHERE order_source_id between l_c_min_ord_src and l_c_max_ord_src
2188 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
2189 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
2190 AND (l_sold_to_org_id IS NULL
2191 OR l_sold_to_org_id = sold_to_org_id
2192 OR l_sold_to_org = sold_to_org)
2193 AND h.deliver_to_contact_ref IS NOT NULL)
2194 AND c.customer_info_type_code = 'CONTACT';
2195 l_cust_rows := l_cust_rows + SQL%ROWCOUNT;
2196 IF l_debug_level > 0 THEN
2197 oe_debug_pub.add('Deleted '|| l_cust_rows || ' records for deliver to contact ref with org id');
2198 END IF;
2199
2200 -- Start MOAC
2201 IF p_process_null_org_id = 'Y' AND (p_operating_unit IS NULL OR p_operating_unit = p_default_org_id) THEN
2202 DELETE
2203 FROM OE_Customer_Info_Iface_All c
2204 WHERE c.customer_info_ref IN (SELECT h.deliver_to_contact_ref
2205 FROM OE_Lines_Iface_All h
2206 WHERE order_source_id between l_c_min_ord_src and l_c_max_ord_src
2207 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
2208 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
2209 AND (l_sold_to_org_id IS NULL
2210 OR l_sold_to_org_id = sold_to_org_id
2211 OR l_sold_to_org = sold_to_org)
2212 AND h.deliver_to_contact_ref IS NOT NULL
2213 AND h.org_id IS NULL)
2214 AND c.customer_info_type_code = 'CONTACT';
2215 l_cust_rows := l_cust_rows + SQL%ROWCOUNT;
2216 IF l_debug_level > 0 THEN
2217 oe_debug_pub.add('Deleted '|| SQL%ROWCOUNT || ' records with NULL Org Id');
2218 END IF;
2219 END IF;
2220 -- End MOAC
2221
2222 DELETE
2223 FROM OE_Customer_Info_Iface_All c
2224 WHERE c.customer_info_ref IN (SELECT h.orig_ship_address_ref
2225 FROM OE_Lines_Interface h
2226 WHERE order_source_id between l_c_min_ord_src and l_c_max_ord_src
2227 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
2228 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
2229 AND (l_sold_to_org_id IS NULL
2230 OR l_sold_to_org_id = sold_to_org_id
2231 OR l_sold_to_org = sold_to_org)
2232 AND h.orig_ship_address_ref IS NOT NULL)
2233 AND c.customer_info_type_code = 'ADDRESS';
2234 l_cust_rows := l_cust_rows + SQL%ROWCOUNT;
2235 IF l_debug_level > 0 THEN
2236 oe_debug_pub.add('Deleted '|| l_cust_rows || ' records for ship to address ref with org id');
2237 END IF;
2238
2239 -- Start MOAC
2240 IF p_process_null_org_id = 'Y' AND (p_operating_unit IS NULL OR p_operating_unit = p_default_org_id) THEN
2241 DELETE
2242 FROM OE_Customer_Info_Iface_All c
2243 WHERE c.customer_info_ref IN (SELECT h.orig_ship_address_ref
2244 FROM OE_Lines_Iface_All h
2245 WHERE order_source_id between l_c_min_ord_src and l_c_max_ord_src
2246 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
2247 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
2248 AND (l_sold_to_org_id IS NULL
2249 OR l_sold_to_org_id = sold_to_org_id
2250 OR l_sold_to_org = sold_to_org)
2251 AND h.orig_ship_address_ref IS NOT NULL
2252 AND h.org_id IS NULL)
2253 AND c.customer_info_type_code = 'ADDRESS';
2254 l_cust_rows := l_cust_rows + SQL%ROWCOUNT;
2255 IF l_debug_level > 0 THEN
2256 oe_debug_pub.add('Deleted '|| SQL%ROWCOUNT || ' records with NULL Org Id');
2257 END IF;
2258 END IF;
2259 -- End MOAC
2260
2261 DELETE
2262 FROM OE_Customer_Info_Iface_All c
2263 WHERE c.customer_info_ref IN (SELECT h.orig_bill_address_ref
2264 FROM OE_Lines_Interface h
2265 WHERE order_source_id between l_c_min_ord_src and l_c_max_ord_src
2266 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
2267 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
2268 AND (l_sold_to_org_id IS NULL
2269 OR l_sold_to_org_id = sold_to_org_id
2270 OR l_sold_to_org = sold_to_org)
2271 AND h.orig_bill_address_ref IS NOT NULL)
2272 AND c.customer_info_type_code = 'ADDRESS';
2273 l_cust_rows := l_cust_rows + SQL%ROWCOUNT;
2274 IF l_debug_level > 0 THEN
2275 oe_debug_pub.add('Deleted '|| l_cust_rows || ' records for bill to address ref with org id');
2276 END IF;
2277
2278 -- Start MOAC
2279 IF p_process_null_org_id = 'Y' AND (p_operating_unit IS NULL OR p_operating_unit = p_default_org_id) THEN
2280 DELETE
2281 FROM OE_Customer_Info_Iface_All c
2282 WHERE c.customer_info_ref IN (SELECT h.orig_bill_address_ref
2283 FROM OE_Lines_Iface_All h
2284 WHERE order_source_id between l_c_min_ord_src and l_c_max_ord_src
2285 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
2286 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
2287 AND (l_sold_to_org_id IS NULL
2288 OR l_sold_to_org_id = sold_to_org_id
2289 OR l_sold_to_org = sold_to_org)
2290 AND h.orig_bill_address_ref IS NOT NULL
2291 AND h.org_id IS NULL)
2292 AND c.customer_info_type_code = 'ADDRESS';
2293 l_cust_rows := l_cust_rows + SQL%ROWCOUNT;
2294 IF l_debug_level > 0 THEN
2295 oe_debug_pub.add('Deleted '|| SQL%ROWCOUNT || ' records with NULL Org Id');
2296 END IF;
2297 END IF;
2298 -- End MOAC
2299
2300 DELETE
2301 FROM OE_Customer_Info_Iface_All c
2302 WHERE c.customer_info_ref IN (SELECT h.orig_deliver_address_ref
2303 FROM OE_Lines_Interface h
2304 WHERE order_source_id between l_c_min_ord_src and l_c_max_ord_src
2305 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
2306 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
2307 AND (l_sold_to_org_id IS NULL
2308 OR l_sold_to_org_id = sold_to_org_id
2309 OR l_sold_to_org = sold_to_org)
2310 AND h.orig_deliver_address_ref IS NOT NULL)
2311 AND c.customer_info_type_code = 'ADDRESS';
2312 l_cust_rows := l_cust_rows + SQL%ROWCOUNT;
2313 IF l_debug_level > 0 THEN
2314 oe_debug_pub.add('Deleted '|| l_cust_rows || ' records for deliver address ref with org id');
2315 END IF;
2316
2317 -- Start MOAC
2318 IF p_process_null_org_id = 'Y' AND (p_operating_unit IS NULL OR p_operating_unit = p_default_org_id) THEN
2319 DELETE
2320 FROM OE_Customer_Info_Iface_All c
2321 WHERE c.customer_info_ref IN (SELECT h.orig_deliver_address_ref
2322 FROM OE_Lines_Iface_All h
2323 WHERE order_source_id between l_c_min_ord_src and l_c_max_ord_src
2324 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
2325 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
2326 AND (l_sold_to_org_id IS NULL
2327 OR l_sold_to_org_id = sold_to_org_id
2328 OR l_sold_to_org = sold_to_org)
2329 AND h.orig_deliver_address_ref IS NOT NULL
2330 AND h.org_id IS NULL)
2331 AND c.customer_info_type_code = 'ADDRESS';
2332 l_cust_rows := l_cust_rows + SQL%ROWCOUNT;
2333 IF l_debug_level > 0 THEN
2334 oe_debug_pub.add('Deleted '|| SQL%ROWCOUNT || ' records with NULL Org Id');
2335 END IF;
2336 END IF;
2337 -- End MOAC
2338
2339 IF l_debug_level > 0 THEN
2340 oe_debug_pub.add(l_cust_rows || ' rows deleted');
2341 END IF;
2342 fnd_file.put_line(FND_FILE.OUTPUT, 'Rows Deleted from Customer Info Interface (Line): '|| l_cust_rows);
2343 END IF;
2344 IF l_line_iface THEN
2345 IF l_debug_level > 0 THEN
2346 oe_debug_pub.add('Deleting from OE_LINES_INTERFACE');
2347 END IF;
2348 l_count := 0;
2349 IF p_order_source_id IS NOT NULL THEN
2350 DELETE
2351 FROM OE_Lines_Interface
2352 WHERE order_source_id = p_order_source_id
2353 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
2354 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
2355 AND (l_sold_to_org_id IS NULL
2356 OR l_sold_to_org_id = sold_to_org_id
2357 OR l_sold_to_org = sold_to_org);
2358 l_count := l_count + SQL%ROWCOUNT;
2359
2360 -- Start MOAC
2361 IF p_process_null_org_id = 'Y' AND (p_operating_unit IS NULL OR p_operating_unit = p_default_org_id) THEN
2362 DELETE
2363 FROM OE_Lines_Iface_All
2364 WHERE order_source_id = p_order_source_id
2365 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
2366 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
2367 AND (l_sold_to_org_id IS NULL
2368 OR l_sold_to_org_id = sold_to_org_id
2369 OR l_sold_to_org = sold_to_org)
2370 AND org_id IS NULL;
2371 l_count := l_count + SQL%ROWCOUNT;
2372 IF l_debug_level > 0 THEN
2373 oe_debug_pub.add('Deleted '|| SQL%ROWCOUNT || ' records with NULL Org Id');
2374 END IF;
2375 END IF;
2376 -- End MOAC
2377 ELSE
2378 DELETE
2379 FROM OE_Lines_Interface
2380 WHERE order_source_id BETWEEN l_min_order_source_id and l_max_order_source_id
2381 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
2382 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
2383 AND (l_sold_to_org_id IS NULL
2384 OR l_sold_to_org_id = sold_to_org_id
2385 OR l_sold_to_org = sold_to_org);
2386 l_count := l_count + SQL%ROWCOUNT;
2387
2388 -- Start MOAC
2389 IF p_process_null_org_id = 'Y' AND (p_operating_unit IS NULL OR p_operating_unit = p_default_org_id) THEN
2390 DELETE
2391 FROM OE_Lines_Iface_All
2392 WHERE order_source_id BETWEEN l_min_order_source_id and l_max_order_source_id
2393 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
2394 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
2395 AND (l_sold_to_org_id IS NULL
2396 OR l_sold_to_org_id = sold_to_org_id
2397 OR l_sold_to_org = sold_to_org)
2398 AND org_id IS NULL;
2399 l_count := l_count + SQL%ROWCOUNT;
2400 IF l_debug_level > 0 THEN
2401 oe_debug_pub.add('Deleted '|| SQL%ROWCOUNT || ' records with NULL Org Id');
2402 END IF;
2403 END IF;
2404 -- End MOAC
2405 END IF;
2406
2407 IF l_debug_level > 0 THEN
2408 oe_debug_pub.add(l_count || ' rows deleted');
2409 END IF;
2410 fnd_file.put_line(FND_FILE.OUTPUT, 'Rows Deleted from Lines Interface: '|| l_count);
2411 END IF;
2412
2413 IF l_line_actions THEN
2414 IF l_debug_level > 0 THEN
2415 oe_debug_pub.add('Deleting Line records from OE_ACTIONS_INTERFACE');
2416 END IF;
2417 l_count := 0;
2418
2419 IF p_order_source_id IS NOT NULL THEN
2420 DELETE
2421 FROM OE_Actions_Interface
2422 WHERE order_source_id = p_order_source_id
2423 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
2424 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
2425 AND orig_sys_line_ref IS NOT NULL
2426 AND (l_sold_to_org_id IS NULL
2427 OR l_sold_to_org_id = sold_to_org_id
2428 OR l_sold_to_org = sold_to_org);
2429 l_count := l_count + SQL%ROWCOUNT;
2430
2431 -- Start MOAC
2432 IF p_process_null_org_id = 'Y' AND (p_operating_unit IS NULL OR p_operating_unit = p_default_org_id) THEN
2433 DELETE
2434 FROM OE_Actions_Iface_All
2435 WHERE order_source_id = p_order_source_id
2436 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
2437 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
2438 AND orig_sys_line_ref IS NOT NULL
2439 AND (l_sold_to_org_id IS NULL
2440 OR l_sold_to_org_id = sold_to_org_id
2441 OR l_sold_to_org = sold_to_org)
2442 AND org_id IS NULL;
2443 l_count := l_count + SQL%ROWCOUNT;
2444 IF l_debug_level > 0 THEN
2445 oe_debug_pub.add('Deleted '|| SQL%ROWCOUNT || ' records with NULL Org Id');
2446 END IF;
2447 END IF;
2448 -- End MOAC
2449 ELSE
2450 DELETE
2451 FROM OE_Actions_Interface
2452 WHERE order_source_id BETWEEN l_min_order_source_id and l_max_order_source_id
2453 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
2454 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
2455 AND orig_sys_line_ref IS NOT NULL
2456 AND (l_sold_to_org_id IS NULL
2457 OR l_sold_to_org_id = sold_to_org_id
2458 OR l_sold_to_org = sold_to_org);
2459 l_count := l_count + SQL%ROWCOUNT;
2460
2461 -- Start MOAC
2462 IF p_process_null_org_id = 'Y' AND (p_operating_unit IS NULL OR p_operating_unit = p_default_org_id) THEN
2463 DELETE
2464 FROM OE_Actions_Iface_All
2465 WHERE order_source_id BETWEEN l_min_order_source_id and l_max_order_source_id
2466 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
2467 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
2468 AND orig_sys_line_ref IS NOT NULL
2469 AND (l_sold_to_org_id IS NULL
2470 OR l_sold_to_org_id = sold_to_org_id
2471 OR l_sold_to_org = sold_to_org)
2472 AND org_id IS NULL;
2473 l_count := l_count + SQL%ROWCOUNT;
2474 IF l_debug_level > 0 THEN
2475 oe_debug_pub.add('Deleted '|| SQL%ROWCOUNT || ' records with NULL Org Id');
2476 END IF;
2477 END IF;
2478 -- End MOAC
2479 END IF;
2480 IF l_debug_level > 0 THEN
2481 oe_debug_pub.add(l_count || ' rows deleted');
2482 END IF;
2483 fnd_file.put_line(FND_FILE.OUTPUT, 'Rows Deleted from Actions Interface (Line): '|| l_count);
2484 END IF;
2485 IF l_line_credits THEN
2486 IF l_debug_level > 0 THEN
2487 oe_debug_pub.add('Deleting Line records from OE_CREDITS_INTERFACE');
2488 END IF;
2489 l_count := 0;
2490
2491 IF p_order_source_id IS NOT NULL THEN
2492 DELETE
2493 FROM OE_Credits_Interface
2494 WHERE order_source_id = p_order_source_id
2495 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
2496 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
2497 AND orig_sys_line_ref IS NOT NULL
2498 AND (l_sold_to_org_id IS NULL
2499 OR l_sold_to_org_id = sold_to_org_id
2500 OR l_sold_to_org = sold_to_org);
2501 l_count := l_count + SQL%ROWCOUNT;
2502
2503 -- Start MOAC
2504 IF p_process_null_org_id = 'Y' AND (p_operating_unit IS NULL OR p_operating_unit = p_default_org_id) THEN
2505 DELETE
2506 FROM OE_Credits_Iface_All
2507 WHERE order_source_id = p_order_source_id
2508 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
2509 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
2510 AND orig_sys_line_ref IS NOT NULL
2511 AND (l_sold_to_org_id IS NULL
2512 OR l_sold_to_org_id = sold_to_org_id
2513 OR l_sold_to_org = sold_to_org)
2514 AND org_id IS NULL;
2515 l_count := l_count + SQL%ROWCOUNT;
2516 IF l_debug_level > 0 THEN
2517 oe_debug_pub.add('Deleted '|| SQL%ROWCOUNT || ' records with NULL Org Id');
2518 END IF;
2519 END IF;
2520 -- End MOAC
2521 ELSE
2522 DELETE
2523 FROM OE_Credits_Interface
2524 WHERE order_source_id BETWEEN l_min_order_source_id and l_max_order_source_id
2525 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
2526 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
2527 AND orig_sys_line_ref IS NOT NULL
2528 AND (l_sold_to_org_id IS NULL
2529 OR l_sold_to_org_id = sold_to_org_id
2530 OR l_sold_to_org = sold_to_org);
2531 l_count := l_count + SQL%ROWCOUNT;
2532
2533 -- Start MOAC
2534 IF p_process_null_org_id = 'Y' AND (p_operating_unit IS NULL OR p_operating_unit = p_default_org_id) THEN
2535 DELETE
2536 FROM OE_Credits_Iface_All
2537 WHERE order_source_id BETWEEN l_min_order_source_id and l_max_order_source_id
2538 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
2539 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
2540 AND orig_sys_line_ref IS NOT NULL
2541 AND (l_sold_to_org_id IS NULL
2542 OR l_sold_to_org_id = sold_to_org_id
2543 OR l_sold_to_org = sold_to_org)
2544 AND org_id IS NULL;
2545 l_count := l_count + SQL%ROWCOUNT;
2546 IF l_debug_level > 0 THEN
2547 oe_debug_pub.add('Deleted '|| SQL%ROWCOUNT || ' records with NULL Org Id');
2548 END IF;
2549 END IF;
2550 -- End MOAC
2551 END IF;
2552 IF l_debug_level > 0 THEN
2553 oe_debug_pub.add(l_count || ' rows deleted');
2554 END IF;
2555 fnd_file.put_line(FND_FILE.OUTPUT, 'Rows Deleted from Credits Interface (Line): '|| l_count);
2556 END IF;
2557 IF l_line_lotserial THEN
2558 IF l_debug_level > 0 THEN
2559 oe_debug_pub.add('Deleting Line records from OE_LOTSERIALS_INTERFACE');
2560 END IF;
2561 l_count := 0;
2562
2563 IF p_order_source_id IS NOT NULL THEN
2564 DELETE
2565 FROM OE_LotSerials_Interface
2566 WHERE order_source_id = p_order_source_id
2567 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
2568 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
2569 AND orig_sys_line_ref IS NOT NULL
2570 AND (l_sold_to_org_id IS NULL
2571 OR l_sold_to_org_id = sold_to_org_id
2572 OR l_sold_to_org = sold_to_org);
2573 l_count := l_count + SQL%ROWCOUNT;
2574
2575 -- Start MOAC
2576 IF p_process_null_org_id = 'Y' AND (p_operating_unit IS NULL OR p_operating_unit = p_default_org_id) THEN
2577 DELETE
2578 FROM OE_LotSerials_Iface_All
2579 WHERE order_source_id = p_order_source_id
2580 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
2581 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
2582 AND orig_sys_line_ref IS NOT NULL
2583 AND (l_sold_to_org_id IS NULL
2584 OR l_sold_to_org_id = sold_to_org_id
2585 OR l_sold_to_org = sold_to_org)
2586 AND org_id IS NULL;
2587 l_count := l_count + SQL%ROWCOUNT;
2588 IF l_debug_level > 0 THEN
2589 oe_debug_pub.add('Deleted '|| SQL%ROWCOUNT || ' records with NULL Org Id');
2590 END IF;
2591 END IF;
2592 -- End MOAC
2593 ELSE
2594 DELETE
2595 FROM OE_LotSerials_Interface
2596 WHERE order_source_id BETWEEN l_min_order_source_id and l_max_order_source_id
2597 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
2598 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
2599 AND orig_sys_line_ref IS NOT NULL
2600 AND (l_sold_to_org_id IS NULL
2601 OR l_sold_to_org_id = sold_to_org_id
2602 OR l_sold_to_org = sold_to_org);
2603 l_count := l_count + SQL%ROWCOUNT;
2604
2605 -- Start MOAC
2606 IF p_process_null_org_id = 'Y' AND (p_operating_unit IS NULL OR p_operating_unit = p_default_org_id) THEN
2607 DELETE
2608 FROM OE_LotSerials_Iface_All
2609 WHERE order_source_id BETWEEN l_min_order_source_id and l_max_order_source_id
2610 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
2611 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
2612 AND orig_sys_line_ref IS NOT NULL
2613 AND (l_sold_to_org_id IS NULL
2614 OR l_sold_to_org_id = sold_to_org_id
2615 OR l_sold_to_org = sold_to_org)
2616 AND org_id IS NULL;
2617 l_count := l_count + SQL%ROWCOUNT;
2618 IF l_debug_level > 0 THEN
2619 oe_debug_pub.add('Deleted '|| SQL%ROWCOUNT || ' records with NULL Org Id');
2620 END IF;
2621 END IF;
2622 -- End MOAC
2623 END IF;
2624 IF l_debug_level > 0 THEN
2625 oe_debug_pub.add(l_count || ' rows deleted');
2626 END IF;
2627 fnd_file.put_line(FND_FILE.OUTPUT, 'Rows Deleted from Lotserials Interface (Line): '|| l_count );
2628 END IF;
2629 IF l_line_price_adj THEN
2630 IF l_debug_level > 0 THEN
2631 oe_debug_pub.add('Deleting Line records from OE_PRICE_ADJS_INTERFACE');
2632 END IF;
2633 l_count := 0;
2634
2635 IF p_order_source_id IS NOT NULL THEN
2636 DELETE
2637 FROM OE_Price_Adjs_Interface
2638 WHERE order_source_id = p_order_source_id
2639 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
2640 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
2641 AND orig_sys_line_ref IS NOT NULL
2642 AND (l_sold_to_org_id IS NULL
2643 OR l_sold_to_org_id = sold_to_org_id
2644 OR l_sold_to_org = sold_to_org);
2645 l_count := l_count + SQL%ROWCOUNT;
2646
2647 -- Start MOAC
2648 IF p_process_null_org_id = 'Y' AND (p_operating_unit IS NULL OR p_operating_unit = p_default_org_id) THEN
2649 DELETE
2650 FROM OE_Price_Adjs_Iface_All
2651 WHERE order_source_id = p_order_source_id
2652 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
2653 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
2654 AND orig_sys_line_ref IS NOT NULL
2655 AND (l_sold_to_org_id IS NULL
2656 OR l_sold_to_org_id = sold_to_org_id
2657 OR l_sold_to_org = sold_to_org)
2658 AND org_id IS NULL;
2659 l_count := l_count + SQL%ROWCOUNT;
2660 IF l_debug_level > 0 THEN
2661 oe_debug_pub.add('Deleted '|| SQL%ROWCOUNT || ' records with NULL Org Id');
2662 END IF;
2663 END IF;
2664 -- End MOAC
2665 ELSE
2666 DELETE
2667 FROM OE_Price_Adjs_Interface
2668 WHERE order_source_id BETWEEN l_min_order_source_id and l_max_order_source_id
2669 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
2670 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
2671 AND orig_sys_line_ref IS NOT NULL
2672 AND (l_sold_to_org_id IS NULL
2673 OR l_sold_to_org_id = sold_to_org_id
2674 OR l_sold_to_org = sold_to_org);
2675 l_count := l_count + SQL%ROWCOUNT;
2676
2677 -- Start MOAC
2678 IF p_process_null_org_id = 'Y' AND (p_operating_unit IS NULL OR p_operating_unit = p_default_org_id) THEN
2679 DELETE
2680 FROM OE_Price_Adjs_Iface_All
2681 WHERE order_source_id BETWEEN l_min_order_source_id and l_max_order_source_id
2682 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
2683 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
2684 AND orig_sys_line_ref IS NOT NULL
2685 AND (l_sold_to_org_id IS NULL
2686 OR l_sold_to_org_id = sold_to_org_id
2687 OR l_sold_to_org = sold_to_org)
2688 AND org_id IS NULL;
2689 l_count := l_count + SQL%ROWCOUNT;
2690 IF l_debug_level > 0 THEN
2691 oe_debug_pub.add('Deleted '|| SQL%ROWCOUNT || ' records with NULL Org Id');
2692 END IF;
2693 END IF;
2694 -- End MOAC
2695 END IF;
2696 IF l_debug_level > 0 THEN
2697 oe_debug_pub.add(l_count || ' rows deleted');
2698 END IF;
2699 fnd_file.put_line(FND_FILE.OUTPUT, 'Rows Deleted from Price Adjustments Interface (Line): '|| l_count );
2700 END IF;
2701 IF l_line_price_att THEN
2702 IF l_debug_level > 0 THEN
2703 oe_debug_pub.add('Deleting Line records from OE_PRICE_ATTS_INTERFACE');
2704 END IF;
2705 l_count := 0;
2706
2707 IF p_order_source_id IS NOT NULL THEN
2708 DELETE
2709 FROM OE_Price_Atts_Interface
2710 WHERE order_source_id = p_order_source_id
2711 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
2712 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
2713 AND orig_sys_line_ref IS NOT NULL
2714 AND (l_sold_to_org_id IS NULL
2715 OR l_sold_to_org_id = sold_to_org_id
2716 OR l_sold_to_org = sold_to_org);
2717 l_count := l_count + SQL%ROWCOUNT;
2718
2719 -- Start MOAC
2720 IF p_process_null_org_id = 'Y' AND (p_operating_unit IS NULL OR p_operating_unit = p_default_org_id) THEN
2721 DELETE
2722 FROM OE_Price_Atts_Iface_All
2723 WHERE order_source_id = p_order_source_id
2724 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
2725 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
2726 AND orig_sys_line_ref IS NOT NULL
2727 AND (l_sold_to_org_id IS NULL
2728 OR l_sold_to_org_id = sold_to_org_id
2729 OR l_sold_to_org = sold_to_org)
2730 AND org_id IS NULL;
2731 l_count := l_count + SQL%ROWCOUNT;
2732 IF l_debug_level > 0 THEN
2733 oe_debug_pub.add('Deleted '|| SQL%ROWCOUNT || ' records with NULL Org Id');
2734 END IF;
2735 END IF;
2736 -- End MOAC
2737 ELSE
2738 DELETE
2739 FROM OE_Price_Atts_Interface
2740 WHERE order_source_id BETWEEN l_min_order_source_id and l_max_order_source_id
2741 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
2742 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
2743 AND orig_sys_line_ref IS NOT NULL
2744 AND (l_sold_to_org_id IS NULL
2745 OR l_sold_to_org_id = sold_to_org_id
2746 OR l_sold_to_org = sold_to_org);
2747 l_count := l_count + SQL%ROWCOUNT;
2748
2749 -- Start MOAC
2750 IF p_process_null_org_id = 'Y' AND (p_operating_unit IS NULL OR p_operating_unit = p_default_org_id) THEN
2751 DELETE
2752 FROM OE_Price_Atts_Iface_All
2753 WHERE order_source_id BETWEEN l_min_order_source_id and l_max_order_source_id
2754 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
2755 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
2756 AND orig_sys_line_ref IS NOT NULL
2757 AND (l_sold_to_org_id IS NULL
2758 OR l_sold_to_org_id = sold_to_org_id
2759 OR l_sold_to_org = sold_to_org)
2760 AND org_id IS NULL;
2761 l_count := l_count + SQL%ROWCOUNT;
2762 IF l_debug_level > 0 THEN
2763 oe_debug_pub.add('Deleted '|| SQL%ROWCOUNT || ' records with NULL Org Id');
2764 END IF;
2765 END IF;
2766 -- End MOAC
2767 END IF;
2768 IF l_debug_level > 0 THEN
2769 oe_debug_pub.add(l_count || ' rows deleted');
2770 END IF;
2771 fnd_file.put_line(FND_FILE.OUTPUT, 'Rows Deleted from Pricing Attributes Interface (Line): '|| l_count );
2772 END IF;
2773 IF l_line_reservtns THEN
2774 IF l_debug_level > 0 THEN
2775 oe_debug_pub.add('Deleting Line records from OE_RESERVTNS_INTERFACE');
2776 END IF;
2777 l_count := 0;
2778
2779 IF p_order_source_id IS NOT NULL THEN
2780 DELETE
2781 FROM OE_Reservtns_Interface
2782 WHERE order_source_id = p_order_source_id
2783 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
2784 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
2785 AND orig_sys_line_ref IS NOT NULL
2786 AND (l_sold_to_org_id IS NULL
2787 OR l_sold_to_org_id = sold_to_org_id
2788 OR l_sold_to_org = sold_to_org);
2789 l_count := l_count + SQL%ROWCOUNT;
2790
2791 -- Start MOAC
2792 IF p_process_null_org_id = 'Y' AND (p_operating_unit IS NULL OR p_operating_unit = p_default_org_id) THEN
2793 DELETE
2794 FROM OE_Reservtns_Iface_All
2795 WHERE order_source_id = p_order_source_id
2796 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
2797 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
2798 AND orig_sys_line_ref IS NOT NULL
2799 AND (l_sold_to_org_id IS NULL
2800 OR l_sold_to_org_id = sold_to_org_id
2801 OR l_sold_to_org = sold_to_org)
2802 AND org_id IS NULL;
2803 l_count := l_count + SQL%ROWCOUNT;
2804 IF l_debug_level > 0 THEN
2805 oe_debug_pub.add('Deleted '|| SQL%ROWCOUNT || ' records with NULL Org Id');
2806 END IF;
2807 END IF;
2808 -- End MOAC
2809 ELSE
2810 DELETE
2811 FROM OE_Reservtns_Interface
2812 WHERE order_source_id BETWEEN l_min_order_source_id and l_max_order_source_id
2813 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
2814 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
2815 AND orig_sys_line_ref IS NOT NULL
2816 AND (l_sold_to_org_id IS NULL
2817 OR l_sold_to_org_id = sold_to_org_id
2818 OR l_sold_to_org = sold_to_org);
2819 l_count := l_count + SQL%ROWCOUNT;
2820
2821 -- Start MOAC
2822 IF p_process_null_org_id = 'Y' AND (p_operating_unit IS NULL OR p_operating_unit = p_default_org_id) THEN
2823 DELETE
2824 FROM OE_Reservtns_Iface_All
2825 WHERE order_source_id BETWEEN l_min_order_source_id and l_max_order_source_id
2826 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
2827 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
2828 AND orig_sys_line_ref IS NOT NULL
2829 AND (l_sold_to_org_id IS NULL
2830 OR l_sold_to_org_id = sold_to_org_id
2831 OR l_sold_to_org = sold_to_org)
2832 AND org_id IS NULL;
2833 l_count := l_count + SQL%ROWCOUNT;
2834 IF l_debug_level > 0 THEN
2835 oe_debug_pub.add('Deleted '|| SQL%ROWCOUNT || ' records with NULL Org Id');
2836 END IF;
2837 END IF;
2838 -- End MOAC
2839 END IF;
2840 IF l_debug_level > 0 THEN
2841 oe_debug_pub.add(l_count || ' rows deleted');
2842 END IF;
2843 fnd_file.put_line(FND_FILE.OUTPUT, 'Rows Deleted from Reservations Interface (Line): '|| l_count );
2844 END IF;
2845
2846 IF l_line_payments THEN
2847 IF l_debug_level > 0 THEN
2848 oe_debug_pub.add('Deleting Line records from OE_PAYMENTS_INTERFACE');
2849 END IF;
2850 l_count := 0;
2851
2852 IF p_order_source_id IS NOT NULL THEN
2853 DELETE
2854 FROM OE_Payments_Interface
2855 WHERE order_source_id = p_order_source_id
2856 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
2857 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
2858 AND orig_sys_line_ref IS NOT NULL
2859 AND (l_sold_to_org_id IS NULL
2860 OR l_sold_to_org_id = sold_to_org_id
2861 OR l_sold_to_org = sold_to_org);
2862 l_count := l_count + SQL%ROWCOUNT;
2863
2864 -- Start MOAC
2865 IF p_process_null_org_id = 'Y' AND (p_operating_unit IS NULL OR p_operating_unit = p_default_org_id) THEN
2866 DELETE
2867 FROM OE_Payments_Iface_All
2868 WHERE order_source_id = p_order_source_id
2869 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
2870 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
2871 AND orig_sys_line_ref IS NOT NULL
2872 AND (l_sold_to_org_id IS NULL
2873 OR l_sold_to_org_id = sold_to_org_id
2874 OR l_sold_to_org = sold_to_org)
2875 AND org_id IS NULL;
2876 l_count := l_count + SQL%ROWCOUNT;
2877 IF l_debug_level > 0 THEN
2878 oe_debug_pub.add('Deleted '|| SQL%ROWCOUNT || ' records with NULL Org Id');
2879 END IF;
2880 END IF;
2881 -- End MOAC
2882 ELSE
2883 DELETE
2884 FROM OE_Payments_Interface
2885 WHERE order_source_id BETWEEN l_min_order_source_id and l_max_order_source_id
2886 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
2887 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
2888 AND orig_sys_line_ref IS NOT NULL
2889 AND (l_sold_to_org_id IS NULL
2890 OR l_sold_to_org_id = sold_to_org_id
2891 OR l_sold_to_org = sold_to_org);
2892 l_count := l_count + SQL%ROWCOUNT;
2893
2894 -- Start MOAC
2895 IF p_process_null_org_id = 'Y' AND (p_operating_unit IS NULL OR p_operating_unit = p_default_org_id) THEN
2896 DELETE
2897 FROM OE_Payments_Iface_All
2898 WHERE order_source_id BETWEEN l_min_order_source_id and l_max_order_source_id
2899 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
2900 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
2901 AND orig_sys_line_ref IS NOT NULL
2902 AND (l_sold_to_org_id IS NULL
2903 OR l_sold_to_org_id = sold_to_org_id
2904 OR l_sold_to_org = sold_to_org)
2905 AND org_id IS NULL;
2906 l_count := l_count + SQL%ROWCOUNT;
2907 IF l_debug_level > 0 THEN
2908 oe_debug_pub.add('Deleted '|| SQL%ROWCOUNT || ' records with NULL Org Id');
2909 END IF;
2910 END IF;
2911 -- End MOAC
2912 END IF;
2913 IF l_debug_level > 0 THEN
2914 oe_debug_pub.add(l_count || ' rows deleted');
2915 END IF;
2916 fnd_file.put_line(FND_FILE.OUTPUT, 'Rows Deleted from Payments Interface (Line): '|| l_count );
2917 END IF;
2918
2919 IF l_header_acks THEN
2920 IF l_debug_level > 0 THEN
2921 oe_debug_pub.add('Deleting Acknowledged Header records from OE_HEADER_ACKS');
2922 END IF;
2923 l_count := 0;
2924
2925 IF p_orig_sys_document_ref_from IS NOT NULL
2926 AND p_orig_sys_document_ref_to IS NOT NULL THEN
2927 DELETE
2928 FROM OE_Header_Acks
2929 WHERE order_source_id = nvl(p_order_source_id, order_source_id)
2930 AND orig_sys_document_ref BETWEEN p_orig_sys_document_ref_from
2931 AND p_orig_sys_document_ref_to
2932 AND sold_to_org_id = nvl(l_sold_to_org_id, sold_to_org_id)
2933 AND nvl(acknowledgment_flag, 'N') = 'Y'
2934 AND ((p_operating_unit IS NULL AND MO_GLOBAL.Check_Access (org_id) = l_yes) OR org_id = p_operating_unit);
2935 ELSIF p_orig_sys_document_ref_from IS NOT NULL THEN
2936 DELETE
2937 FROM OE_Header_Acks
2938 WHERE order_source_id = nvl(p_order_source_id, order_source_id)
2939 AND orig_sys_document_ref >= p_orig_sys_document_ref_from
2940 AND sold_to_org_id = nvl(l_sold_to_org_id, sold_to_org_id)
2941 AND nvl(acknowledgment_flag, 'N') = 'Y'
2942 AND ((p_operating_unit IS NULL AND MO_GLOBAL.Check_Access (org_id) = l_yes) OR org_id = p_operating_unit);
2943 ELSIF p_orig_sys_document_ref_to IS NOT NULL THEN
2944 DELETE
2945 FROM OE_Header_Acks
2946 WHERE order_source_id = nvl(p_order_source_id, order_source_id)
2947 AND orig_sys_document_ref <= p_orig_sys_document_ref_to
2948 AND sold_to_org_id = nvl(l_sold_to_org_id, sold_to_org_id)
2949 AND nvl(acknowledgment_flag, 'N') = 'Y'
2950 AND ((p_operating_unit IS NULL AND MO_GLOBAL.Check_Access (org_id) = l_yes) OR org_id = p_operating_unit);
2951 ELSE
2952 DELETE
2953 FROM OE_Header_Acks
2954 WHERE order_source_id = nvl(p_order_source_id, order_source_id)
2955 AND orig_sys_document_ref >= ' '
2956 AND sold_to_org_id = nvl(l_sold_to_org_id, sold_to_org_id)
2957 AND nvl(acknowledgment_flag, 'N') = 'Y'
2958 AND ((p_operating_unit IS NULL AND MO_GLOBAL.Check_Access (org_id) = l_yes) OR org_id = p_operating_unit);
2959 END IF;
2960 l_count := l_count + SQL%ROWCOUNT;
2961
2962 IF l_debug_level > 0 THEN
2963 oe_debug_pub.add(l_count || ' rows deleted');
2964 END IF;
2965 fnd_file.put_line(FND_FILE.OUTPUT, 'Rows Deleted from Header Acknowledgments: '|| l_count );
2966 END IF;
2967 IF l_line_acks THEN
2968 IF l_debug_level > 0 THEN
2969 oe_debug_pub.add('Deleting Acknowledged Line records from OE_LINE_ACKS');
2970 END IF;
2971 l_count := 0;
2972
2973 IF p_orig_sys_document_ref_from IS NOT NULL
2974 AND p_orig_sys_document_ref_to IS NOT NULL THEN
2975 DELETE
2976 FROM OE_Line_Acks
2977 WHERE order_source_id = nvl(p_order_source_id, order_source_id)
2978 AND orig_sys_document_ref BETWEEN p_orig_sys_document_ref_from
2979 AND p_orig_sys_document_ref_to
2980 AND sold_to_org_id = nvl(l_sold_to_org_id, sold_to_org_id)
2981 AND nvl(acknowledgment_flag, 'N') = 'Y'
2982 AND ((p_operating_unit IS NULL AND MO_GLOBAL.Check_Access (org_id) = l_yes) OR org_id = p_operating_unit);
2983 ELSIF p_orig_sys_document_ref_from IS NOT NULL THEN
2984 DELETE
2985 FROM OE_Line_Acks
2986 WHERE order_source_id = nvl(p_order_source_id, order_source_id)
2987 AND orig_sys_document_ref >= p_orig_sys_document_ref_from
2988 AND sold_to_org_id = nvl(l_sold_to_org_id, sold_to_org_id)
2989 AND nvl(acknowledgment_flag, 'N') = 'Y'
2990 AND ((p_operating_unit IS NULL AND MO_GLOBAL.Check_Access (org_id) = l_yes) OR org_id = p_operating_unit);
2991 ELSIF p_orig_sys_document_ref_to IS NOT NULL THEN
2992 DELETE
2993 FROM OE_Line_Acks
2994 WHERE order_source_id = nvl(p_order_source_id, order_source_id)
2995 AND orig_sys_document_ref <= p_orig_sys_document_ref_to
2996 AND sold_to_org_id = nvl(l_sold_to_org_id, sold_to_org_id)
2997 AND nvl(acknowledgment_flag, 'N') = 'Y'
2998 AND ((p_operating_unit IS NULL AND MO_GLOBAL.Check_Access (org_id) = l_yes) OR org_id = p_operating_unit);
2999 ELSE
3000 DELETE
3001 FROM OE_Line_Acks
3002 WHERE order_source_id = nvl(p_order_source_id, order_source_id)
3003 AND orig_sys_document_ref >= ' '
3004 AND sold_to_org_id = nvl(l_sold_to_org_id, sold_to_org_id)
3005 AND nvl(acknowledgment_flag, 'N') = 'Y'
3006 AND ((p_operating_unit IS NULL AND MO_GLOBAL.Check_Access (org_id) = l_yes) OR org_id = p_operating_unit);
3007 END IF;
3008
3009 l_count := l_count + SQL%ROWCOUNT;
3010 IF l_debug_level > 0 THEN
3011 oe_debug_pub.add(l_count || ' rows deleted');
3012 END IF;
3013 fnd_file.put_line(FND_FILE.OUTPUT, 'Rows Deleted from Line Acknowledgments: '|| l_count);
3014 END IF;
3015 IF l_elecmsgs THEN
3016 IF l_debug_level > 0 THEN
3017 oe_debug_pub.add('Deleting records from OE_EM_INFORMATION');
3018 END IF;
3019 l_count := 0;
3020
3021 IF p_order_source_id IS NOT NULL AND l_sold_to_org_id IS NOT NULL THEN
3022 DELETE
3023 FROM OE_EM_Information
3024 WHERE order_source_id = p_order_source_id
3025 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
3026 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
3027 AND sold_to_org_id = l_sold_to_org_id;
3028 ELSIF p_order_source_id IS NOT NULL THEN
3029 DELETE
3030 FROM OE_EM_Information
3031 WHERE order_source_id = p_order_source_id
3032 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
3033 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
3034 AND sold_to_org_id = nvl(l_sold_to_org_id,sold_to_org_id);
3035 ELSIF l_sold_to_org_id IS NOT NULL THEN
3036 DELETE
3037 FROM OE_EM_Information
3038 WHERE order_source_id BETWEEN l_min_order_source_id AND l_max_order_source_id
3039 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
3040 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
3041 AND sold_to_org_id = l_sold_to_org_id;
3042 ELSE
3043 DELETE
3044 FROM OE_EM_Information
3045 WHERE order_source_id BETWEEN l_min_order_source_id AND l_max_order_source_id
3046 AND orig_sys_document_ref BETWEEN nvl(p_orig_sys_document_ref_from, orig_sys_document_ref)
3047 AND nvl(p_orig_sys_document_ref_to, orig_sys_document_ref)
3048 AND sold_to_org_id = nvl(l_sold_to_org_id,sold_to_org_id);
3049 END IF;
3050 l_count := l_count + SQL%ROWCOUNT;
3051
3052 IF l_debug_level > 0 THEN
3053 oe_debug_pub.add(l_count || ' rows deleted');
3054 END IF;
3055 fnd_file.put_line(FND_FILE.OUTPUT, 'Rows Deleted from Open Interface Tracking table: '|| l_count);
3056 END IF;
3057
3058 retcode := 0;
3059 IF l_debug_level > 0 THEN
3060 oe_debug_pub.add('Program exited normally');
3061 END IF;
3062 fnd_file.put_line(FND_FILE.OUTPUT, '');
3063 fnd_file.put_line(FND_FILE.OUTPUT, 'Program exited with code : ' || retcode);
3064 EXCEPTION
3065
3066 WHEN OTHERS THEN
3067 retcode := 2;
3068 errbuf := SQLERRM;
3069 IF l_debug_level > 0 THEN
3070 oe_debug_pub.add( 'SQLERRM: '||SQLERRM||' SQLCODE:'||SQLCODE ) ;
3071 END IF;
3072 fnd_file.put_line(FND_FILE.OUTPUT, '');
3073 fnd_file.put_line(FND_FILE.OUTPUT, 'Program exited with code : '||retcode);
3074 fnd_file.put_line(FND_FILE.OUTPUT, 'SQLERRM: '||SQLERRM||' SQLCODE:'||SQLCODE );
3075 IF OE_MSG_PUB.Check_Msg_level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3076 OE_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, 'Open_Interface_Purge_Conc_Pgm');
3077 End if;
3078 End Open_Interface_Purge_Conc_Pgm;
3079
3080 -----------------------------------------------------------------
3081 -- END CONC PGM API
3082 -----------------------------------------------------------------
3083
3084 PROCEDURE Initialize_EM_Access_List (X_Access_List OUT NOCOPY OE_GLOBALS.ACCESS_LIST)
3085 IS
3086 --
3087 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3088 --
3089 BEGIN
3090 IF l_debug_level > 0 THEN
3091 oe_debug_pub.add( 'ENTERING OE_ELECMSGS_PVT.INITIALIZE_ACCESS_LIST') ;
3092 END IF;
3093
3094 IF FND_FUNCTION.TEST('ONT_OEXOEORD_RETRY_WF') THEN
3095 Add_Access('RETRY_WF');
3096 END IF;
3097
3098 IF FND_FUNCTION.TEST('ONT_OEXOEORD_PROCESS_MESSAGES') THEN
3099 Add_Access('PROCESS_MESSAGES');
3100 END IF;
3101
3102 IF l_debug_level > 0 THEN
3103 oe_debug_pub.add( 'EXITING OE_ELECMSGS_PVT.INITIALIZE_ACCESS_LIST') ;
3104 END IF;
3105
3106 X_Access_List := OE_GLOBALS.G_EM_ACCESS_LIST;
3107 END Initialize_EM_Access_List;
3108
3109 PROCEDURE Add_Access(Function_Name VARCHAR2)
3110 IS
3111 i number:=0;
3112 --
3113 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3114 --
3115 BEGIN
3116 IF l_debug_level > 0 THEN
3117 oe_debug_pub.add( 'ENTERING OE_ELECMSGS_PVT.ADD_ACCESS' ) ;
3118 END IF;
3119 IF OE_GLOBALS.G_EM_ACCESS_List.Count=0 THEN
3120 OE_GLOBALS.G_EM_Access_List(1):=Function_Name;
3121 ELSIF OE_GLOBALS.G_EM_ACCESS_List.Count>0 THEN
3122 i:=OE_GLOBALS.G_EM_ACCESS_List.Last+1;
3123 OE_GLOBALS.G_EM_ACCESS_List(i):=Function_Name;
3124 END IF;
3125 IF l_debug_level > 0 THEN
3126 oe_debug_pub.add( 'EXITING OE_ELECMSGS_PVT.ADD_ACCESS') ;
3127 END IF;
3128 EXCEPTION
3129 When Others Then
3130 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3131 THEN
3132 OE_MSG_PUB.Add_Exc_Msg
3133 ( G_PKG_NAME
3134 , 'ADD_ACCESS'
3135 );
3136 END IF;
3137
3138 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3139 END Add_Access;
3140
3141
3142 END OE_ELECMSGS_PVT;
[Home] [Help]