[Home] [Help]
PACKAGE BODY: APPS.OE_ORDER_PURGE_PVT
Source
1 PACKAGE BODY OE_ORDER_PURGE_PVT AS
2 /* $Header: OEXVPURB.pls 120.19.12020000.2 2013/01/19 00:51:59 cpati ship $ */
3
4
5 Procedure Select_Purge_Orders
6 (
7 p_dummy1 OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
8 p_dummy2 OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
9 p_purge_set_id IN NUMBER
10 )
11 IS
12 l_id_list CLOB;
13 l_Count_Selected NUMBER;
14 l_purge_set_submit_datetime DATE;
15
16 l_buffer VARCHAR2(32000);
17 l_amount BINARY_INTEGER := 32000;
18 l_position INTEGER := 1;
19
20 l_char_id_list VARCHAR2(32000);
21 l_selected_ids_tbl SELECTED_IDS_TBL;
22 l_start_from INTEGER := 1;
23 l_length INTEGER;
24 l_separator VARCHAR2(1) := ',';
25 l_header_id NUMBER;
26 l_orders_per_commit NUMBER;
27
28 Cursor purge_set is
29 Select selected_ids
30 , Count_Selected
31 ,purge_set_submit_datetime
32 , orders_per_commit
33 From OE_PURGE_SETS
34 Where Purge_set_id = p_purge_set_id;
35
36 BEGIN
37
38 oe_debug_pub.add('Entering OE_Order_Purge_PVT.Select_Purge_Orders '||p_purge_set_id,1);
39
40 fnd_file.put_line(FND_FILE.LOG,'Parameters :');
41 fnd_file.put_line(FND_FILE.LOG,' Purge Set Id : '||p_purge_set_id);
42 OPEN purge_set;
43 Fetch purge_set
44 into l_id_list
45 ,l_Count_Selected
46 ,l_purge_set_submit_datetime
47 ,l_orders_per_commit;
48
49 IF nvl(l_count_selected,0) <> 0 THEN
50 DBMS_LOB.OPEN(l_id_list,DBMS_LOB.LOB_READONLY);
51 l_length := DBMS_LOB.GETLENGTH(l_id_list);
52 oe_debug_pub.add('Lenght of the LOB : '||to_char(l_length));
53
54 FOR I IN 1..l_count_selected LOOP
55 l_position := DBMS_LOB.INSTR(l_id_list,l_separator,l_start_from,1);
56 IF l_position <> 0 THEN
57 l_header_id := to_number(DBMS_LOB.SUBSTR(l_id_list,(l_position - l_start_from),l_start_from));
58 ELSE
59 l_header_id := to_number(DBMS_LOB.SUBSTR(l_id_list,(l_length - l_start_from + 1),l_start_from));
60 END IF;
61 l_selected_ids_tbl(I) := l_header_id;
62 oe_debug_pub.add('Header id : '||to_char(l_header_id));
63 l_start_from := l_position + 1;
64 END LOOP;
65
66 DBMS_LOB.CLOSE(l_id_list);
67
68 oe_debug_pub.add('Selected Ids : '||l_char_id_list);
69 END IF;
70
71 l_orders_per_commit := 100;
72
73 IF nvl(l_count_selected,0) <> 0 THEN
74 Select_Ids_Purge
75 (
76 p_purge_set_id,
77 l_selected_ids_tbl,
78 l_count_selected ,
79 l_orders_per_commit);
80 END IF;
81
82 oe_debug_pub.add('Exiting OE_Order_Purge_PVT '||p_purge_set_id,1);
83
84
85
86 EXCEPTION
87 WHEN OTHERS THEN
88 fnd_file.put_line(FND_FILE.LOG,'*** Error In Generate purge set ** '||substr(sqlerrm,1,300));
89
90 End Select_Purge_Orders;
91
92
93 --- for bug 2323045
94 -- Changed the date format MM/DD/RRRR HH24:MM:SS to MM/DD/RRRR HH24:MI:SS
95
96 Procedure Select_Where_Cond_Purge
97 (
98 ERRBUF OUT NOCOPY /* file.sql.39 change */ VARCHAR2
99 , RETCODE OUT NOCOPY /* file.sql.39 change */ VARCHAR2
100 , p_organization_id IN NUMBER
101 , p_purge_set_name IN VARCHAR2
102 , p_purge_set_description IN VARCHAR2
103 , p_order_number_low IN NUMBER
104 , p_order_number_high IN NUMBER
105 , p_order_type_id IN NUMBER
106 , p_order_category IN VARCHAR2
107 , p_customer_id IN NUMBER
108 , p_ordered_date_low IN VARCHAR2
109 , p_ordered_date_high IN VARCHAR2
110 , p_creation_date_low IN VARCHAR2
111 , p_creation_date_high IN VARCHAR2
112 , p_dummy IN VARCHAR2 DEFAULT NULL
113 , p_include_contractual_orders IN VARCHAR2 DEFAULT NULL
114 )
115 IS
116 l_sql_stmt VARCHAR2(4000) := NULL;
117 l_where_condition VARCHAR2(4000) := NULL ;
118 l_header_id NUMBER;
119 l_order_number NUMBER;
120 l_order_type_name VARCHAR2(30);
121 l_sold_to_org_id NUMBER;
122 l_price_list_id NUMBER;
123 l_purge_set_id NUMBER;
124 l_selected_ids SELECTED_IDS_TBL;
125 l_customer_name VARCHAR2(50);
126 l_orders_per_commit NUMBER := 100;
127 l_rec_count NUMBER := 0;
128 l_ordered_date_low DATE;
129 l_ordered_date_high DATE;
130 l_creation_date_low DATE;
131 l_creation_date_high DATE;
132 l_purge VARCHAR2(1);
133 l_quote_number NUMBER;
134 l_flow_status_code VARCHAR2(30);
135 l_upgraded_flag VARCHAR2(1);
136 l_sql_cursor INTEGER;
137 l_dummy NUMBER;
138 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
139 l_org_id NUMBER;
140
141 CURSOR cur_get_org_for_ord_type IS
142 SELECT org_id
143 FROM OE_ORDER_TYPES_V --MOAC view based on multiple objects
144 WHERE order_type_id = p_order_type_id;
145
146 BEGIN
147 --Bug #4220950
148 ERRBUF := '';
149 RETCODE := 0;
150
151 -- Begining MOAC Changes
152
153 IF p_organization_id IS NOT NULL THEN
154
155 -- Setting a Single Org access.
156
157 MO_GLOBAL.set_policy_context ('S', p_organization_id);
158
159 ELSIF p_organization_id IS NULL THEN
160
161 --
162 -- If p_organization_id is NULL, then check if the p_order_type_id is NULL.
163 -- If it is not NULL then get the org_id for this assosiated transaction type
164 -- If the p_order_type_id is NULL the the Multiple Org access is set.
165 --
166
167
168 IF p_order_type_id IS NOT NULL THEN
169
170 OPEN cur_get_org_for_ord_type;
171 FETCH cur_get_org_for_ord_type INTO l_org_id;
172 CLOSE cur_get_org_for_ord_type;
173
174 -- Setting a Single Org access.
175
176 MO_GLOBAL.set_policy_context ('S', l_org_id);
177
178 ELSE
179 -- Setting a Multiple Org access.
180
181 MO_GLOBAL.set_policy_context('M','');
182
183 END IF;
184 END IF;
185
186
187 -- End MOAC Changes
188
189
190
191 --Quote purge changes
192 --Select Flow Status Code,Quote Number,Upgraded Flag.
193 --Check for transaction phase code as F
194
195 l_sql_stmt:=
196 'SELECT OOH.HEADER_ID,
197 OOH.ORDER_NUMBER,
198 OOT.NAME,
199 OOH.SOLD_TO_ORG_ID,
200 OOH.PRICE_LIST_ID,
201 OOH.QUOTE_NUMBER,
202 OOH.FLOW_STATUS_CODE,
203 OOH.UPGRADED_FLAG
204 FROM OE_ORDER_HEADERS_ALL OOH,
205 OE_ORDER_TYPES_V OOT --MOAC view based on multiple obj
206 WHERE OOH.ORDER_TYPE_ID = OOT.ORDER_TYPE_ID
207 AND NVL(OOH.TRANSACTION_PHASE_CODE,''F'')<>''N''';
208
209
210 IF l_debug_level > 0
211 THEN
212 OE_DEBUG_PUB.Add('Inside select_where_cond_quote');
213 END IF;
214
215 SELECT fnd_date.canonical_to_date(p_ordered_date_low),
216 fnd_date.canonical_to_date(p_ordered_date_high),
217 fnd_date.canonical_to_date(p_creation_date_low),
218 fnd_date.canonical_to_date(p_creation_date_high)
219 INTO l_ordered_date_low,
220 l_ordered_date_high,
221 l_creation_date_low,
222 l_creation_date_high
223 FROM DUAL;
224
225
226 -- SQL literal changes
227 IF nvl(p_order_number_low,0) <> 0 AND
228 nvl(p_order_number_high,0) <> 0
229 THEN
230 l_where_condition := 'Order Number between '||to_char(p_order_number_low)||' AND '||to_char(p_order_number_high); -- Bug 5667753
231 l_sql_stmt:= l_sql_stmt || ' AND OOH.ORDER_NUMBER >= :p1';
232 l_sql_stmt := l_sql_stmt || ' AND OOH.ORDER_NUMBER <= :p2';
233 -- l_where_condition := 'Order Number between :p1 AND :p2';
234
235 ELSIF nvl(p_order_number_low,0) = 0 AND
236 nvl(p_order_number_high,0) <> 0
237 THEN
238 l_sql_stmt := l_sql_stmt || ' AND OOH.ORDER_NUMBER <= :p2';
239 l_where_condition := 'Order Number <= '||to_char(p_order_number_high); -- bug 5667753
240 -- l_where_condition := 'Order Number <= :p2';
241 /* Changed the above statement to fix the bug 2745071 */
242
243 ELSIF nvl(p_order_number_low,0) <> 0 AND
244 nvl(p_order_number_high,0) = 0
245 THEN
246 l_sql_stmt := l_sql_stmt || ' AND OOH.ORDER_NUMBER >= :p1';
247 l_where_condition := 'Order Number >= '||to_char(p_order_number_low); -- bug 5667753
248 -- l_where_condition := 'Order Number >= :p1';
249 /* Changed the above statement to fix the bug 2745071 */
250 END IF;
251
252
253 IF nvl(p_order_type_id,0) <> 0
254 THEN
255 l_sql_stmt := l_sql_stmt || ' AND OOH.ORDER_TYPE_ID = :p3';
256 SELECT NAME
257 INTO l_order_type_name
258 FROM OE_ORDER_TYPES_V --MOAC view based on multiple obj
259 WHERE ORDER_TYPE_ID = p_order_type_id;
260
261 IF l_where_condition IS NULL
262 THEN
263 l_where_condition := ' Order Type = '||l_Order_type_name; -- bug 5667753
264 -- l_where_condition := ' Order Type = :p10';
265 ELSE
266 l_where_condition := l_where_condition || ' AND Order Type = '||l_Order_type_name; -- bug 5667753
267 -- l_where_condition := l_where_condition || ' AND Order Type = :p10';
268 END IF;
269 END IF;
270
271 IF p_order_category IS NOT NULL
272 THEN
273 l_sql_stmt := l_sql_stmt || ' AND OOH.ORDER_CATEGORY_CODE = :p4';
274 IF l_where_condition IS NULL
275 THEN
276 l_where_condition := ' Order Category = '||p_order_category; -- bug 5667753
277 -- l_where_condition := ' Order Category = :p4';
278 ELSE
279 l_where_condition := l_where_condition || ' AND Order Category = '||p_order_category; -- bug 5667753
280 -- l_where_condition := l_where_condition || ' AND Order Category = :p4';
281 END IF;
282 END IF;
283
284 IF nvl(p_customer_id,0) <> 0
285 THEN
286 l_sql_stmt := l_sql_stmt || ' AND OOH.SOLD_TO_ORG_ID = :p5';
287 SELECT p.PARTY_NAME
288 INTO l_customer_name
289 FROM HZ_CUST_ACCOUNTS c,
290 HZ_PARTIES p
291 WHERE c.CUST_ACCOUNT_ID = p_customer_id
292 AND c.PARTY_ID = p.PARTY_ID;
293
294 IF l_where_condition IS NULL
295 THEN
296 l_where_condition := ' Customer = '||l_customer_name; -- bug 5667753
297 -- l_where_condition := ' Customer = :p11';
298 ELSE
299 l_where_condition := l_where_condition|| ' AND Customer = '||l_customer_name; -- bug 5667753
300 -- l_where_condition := l_where_condition|| ' AND Customer = :p11';
301 END IF;
302 END IF;
303
304 --Bug5702003
305
306 IF l_ordered_date_low IS NOT NULL AND
307 l_ordered_date_high IS NOT NULL
308 THEN
309 l_sql_stmt := l_sql_stmt || ' AND OOH.ORDERED_DATE >= :p6';
310 l_sql_stmt := l_sql_stmt || ' AND OOH.ORDERED_DATE <= :p7';
311
312 IF l_where_condition IS NULL
313 THEN
314 l_where_condition := 'Ordered Date Between '||to_char(l_ordered_date_low,'MM/DD/RRRR HH24:MI:SS')||' AND '||to_char(l_ordered_date_high,'MM/DD/RRRR HH24:MI:SS'); -- bug 5667753
315 -- l_where_condition := 'Ordered Date Between :p6 AND :p7';
316 ELSE
317 l_where_condition := l_where_condition|| ' AND Ordered Date Between '||to_char(l_ordered_date_low,'MM/DD/RRRR HH24:MI:SS')||' AND '||to_char(l_ordered_date_high,'MM/DD/RRRR HH24:MI:SS'); -- bug 5667753
318 -- l_where_condition := l_where_condition|| ' Ordered Date Between :p6 AND :p7';
319 END IF;
320
321 ELSIF l_ordered_date_low IS NOT NULL AND
322 l_ordered_date_high IS NULL
323 THEN
324 l_sql_stmt := l_sql_stmt || ' AND OOH.ORDERED_DATE >= :p6';
325 IF l_where_condition IS NULL
326 THEN
327 l_where_condition := 'Ordered Date >= '||to_char(l_ordered_date_low,'MM/DD/RRRR HH24:MI:SS'); -- bug 5667753
328 -- l_where_condition := 'Ordered Date >= :p6';
329 ELSE
330 l_where_condition := l_where_condition||' AND Ordered Date >= '||to_char(l_ordered_date_low,'MM/DD/RRRR HH24:MI:SS'); -- bug 5667753
331 -- l_where_condition := l_where_condition||' Ordered Date >= :p6';
332 END IF;
333
334 ELSIF l_ordered_date_low IS NULL AND
335 l_ordered_date_high IS NOT NULL
336 THEN
337 l_sql_stmt := l_sql_stmt || ' AND OOH.ORDERED_DATE <= :p7';
338 IF l_where_condition IS NULL
339 THEN
340 l_where_condition := 'Ordered Date <= '||to_char(l_ordered_date_high,'MM/DD/RRRR HH24:MI:SS'); -- bug 5667753
341 -- l_where_condition := 'Ordered Date <= :p7';
342 ELSE
343 l_where_condition := l_where_condition||' AND Ordered Date <= '||to_char(l_ordered_date_high,'MM/DD/RRRR HH24:MI:SS'); -- bug 5667753
344 -- l_where_condition := l_where_condition||' Ordered Date <= :p7';
345 END IF;
346 END IF;
347
348 --bug5702003
349
350 IF l_creation_date_low IS NOT NULL AND
351 l_creation_date_high IS NOT NULL
352 THEN
353 l_sql_stmt := l_sql_stmt || ' AND OOH.CREATION_DATE >= :p8';
354 l_sql_stmt := l_sql_stmt || ' AND OOH.CREATION_DATE <= :p9';
355 IF l_where_condition IS NULL
356 THEN
357 l_where_condition := 'Creation Date Between '||to_char(l_creation_date_low,'MM/DD/RRRR HH24:MI:SS')||' AND '||to_char(l_creation_date_high,'MM/DD/RRRR HH24:MI:SS'); -- bug 5667753
358 -- l_where_condition := 'Creation Date Between :p8 AND :p9';
359 ELSE
360 l_where_condition := l_where_condition|| ' AND Creation Date Between '||to_char(l_creation_date_low,'MM/DD/RRRR HH24:MI:SS')||' AND '||to_char(l_creation_date_high,'MM/DD/RRRR HH24:MI:SS'); -- bug 5667753
361 -- l_where_condition := l_where_condition|| ' Creation Date Between :p8 AND p9';
362 END IF;
363
364 ELSIF l_creation_date_low IS NOT NULL AND
365 l_creation_date_high IS NULL
366 THEN
367 l_sql_stmt := l_sql_stmt || ' AND OOH.CREATION_DATE >= :p8';
368 IF l_where_condition IS NULL
369 THEN
370 l_where_condition := 'Creation Date >= '||to_char(l_creation_date_low,'MM/DD/RRRR HH24:MI:SS'); -- bug 5667753
371 -- l_where_condition := 'Creation Date >= :p8';
372 ELSE
373 l_where_condition := l_where_condition||' AND Creation Date >= '||to_char(l_creation_date_low,'MM/DD/RRRR HH24:MI:SS'); -- bug 5667753
374 -- l_where_condition := l_where_condition||' Creation Date >= :p8';
375 END IF;
376
377 ELSIF l_creation_date_low IS NULL AND
378 l_creation_date_high IS NOT NULL
379 THEN
380 l_sql_stmt := l_sql_stmt || ' AND OOH.CREATION_DATE <= :p9';
381 IF l_where_condition IS NULL
382 THEN
383 l_where_condition := 'Creation Date <= '||to_char(l_creation_date_high,'MM/DD/RRRR HH24:MI:SS'); -- bug 5667753
384 -- l_where_condition := 'Creation Date <= :p9';
385 ELSE
386 l_where_condition := l_where_condition||' AND Creation Date <= '||to_char(l_creation_date_high,'MM/DD/RRRR HH24:MI:SS'); -- bug 5667753
387 -- l_where_condition := l_where_condition||' Creation Date <= :p9';
388 END IF;
389 END IF;
390
391 -- end SQL literal changes
392
393 -- Purge Changes for 11i.10
394 IF OE_CODE_CONTROL.Code_Release_Level >= '110510' AND
395 OE_CONTRACTS_UTIL.Check_License = 'Y' AND
396 p_include_contractual_orders IS NOT NULL
397 THEN
398 l_where_condition := l_where_condition||
399 'Purge Orders with Contract Terms = '||p_include_contractual_orders;
400 END IF;
401
402
403 fnd_file.put_line(FND_FILE.LOG,'Parameters :');
404 fnd_file.put_line(FND_FILE.LOG,' Organization Id : '|| p_organization_id);
405 fnd_file.put_line(FND_FILE.LOG,' Purge Set Name : '||p_purge_set_name);
406 fnd_file.put_line(FND_FILE.LOG,' Purge Set Description : '||p_purge_set_description);
407 fnd_file.put_line(FND_FILE.LOG,' Order Number Low : '||to_char(p_order_number_low));
408 fnd_file.put_line(FND_FILE.LOG,' Order Number High : '||to_char(p_order_number_high));
409 fnd_file.put_line(FND_FILE.LOG,' Order Order Type : '||l_order_type_name);
410 fnd_file.put_line(FND_FILE.LOG,' Order Category : '||p_order_category);
411 fnd_file.put_line(FND_FILE.LOG,' Customer Name : '||l_customer_name);
412 fnd_file.put_line(FND_FILE.LOG,' Order Date Low : '||to_char(l_ordered_date_low,'MM/DD/RRRR HH24:MI:SS'));
413 fnd_file.put_line(FND_FILE.LOG,' Order Date High : '||to_char(l_ordered_date_high,'MM/DD/RRRR HH24:MI:SS'));
414 fnd_file.put_line(FND_FILE.LOG,' Creation Date Low : '||to_char(l_creation_date_low,'MM/DD/RRRR HH24:MI:SS'));
415 fnd_file.put_line(FND_FILE.LOG,' Creation Date High : '||to_char(l_creation_date_high,'MM/DD/RRRR HH24:MI:SS'));
416 fnd_file.put_line(FND_FILE.LOG,' Purge Orders with Contract Terms: '||p_include_contractual_orders);
417
418 oe_debug_pub.add('Where Condition : '||l_where_condition);
419
420 OE_Order_Purge_PVT.Insert_Purge_Set
421 (
422 p_purge_set_name => p_purge_set_name,
423 p_purge_set_description => p_purge_set_description,
424 p_purge_set_request_Id => 1,
425 p_purge_set_submit_datetime => SYSDATE,
426 p_selected_ids => l_selected_ids,
427 p_count_selected => 0,
428 p_where_condition => l_where_condition,
429 p_created_by => FND_GLOBAL.USER_ID,
430 p_last_updated_by => FND_GLOBAL.USER_ID,
431 x_purge_set_id => l_purge_set_id
432 );
433
434
435
436 l_sql_cursor := DBMS_SQL.Open_Cursor;
437
438 DBMS_SQL.PARSE(l_sql_cursor, l_sql_stmt, DBMS_SQL.NATIVE);
439
440 --Binding the variables
441 IF p_order_number_low is NOT NULL THEN
442 DBMS_SQL.BIND_VARIABLE (l_sql_cursor, ':p1', p_order_number_low);
443 END IF;
444 IF p_order_number_high is NOT NULL THEN
445 DBMS_SQL.BIND_VARIABLE (l_sql_cursor, ':p2', p_order_number_high);
446 END IF;
447 IF p_order_type_id is NOT NULL THEN
448 DBMS_SQL.BIND_VARIABLE (l_sql_cursor, ':p3', p_order_type_id);
449 END IF;
450 IF p_order_category is NOT NULL THEN
451 DBMS_SQL.BIND_VARIABLE (l_sql_cursor, ':p4', p_order_category);
452 END IF;
453 IF p_customer_id is NOT NULL THEN
454 DBMS_SQL.BIND_VARIABLE (l_sql_cursor, ':p5', p_customer_id);
455 END IF;
456 IF l_ordered_date_low is NOT NULL THEN
457 DBMS_SQL.BIND_VARIABLE (l_sql_cursor, ':p6', l_ordered_date_low);
458 END IF;
459 IF l_ordered_date_high is NOT NULL THEN
460 DBMS_SQL.BIND_VARIABLE (l_sql_cursor, ':p7', l_ordered_date_high);
461 END IF;
462 IF l_creation_date_low is NOT NULL THEN
463 DBMS_SQL.BIND_VARIABLE (l_sql_cursor, ':p8', l_creation_date_low);
464 END IF;
465 IF l_creation_date_high is NOT NULL THEN
466 DBMS_SQL.BIND_VARIABLE (l_sql_cursor, ':p9', l_creation_date_high);
467 END IF;
468
469 DBMS_SQL.DEFINE_COLUMN (l_sql_cursor,1,l_header_id);
470 DBMS_SQL.DEFINE_COLUMN (l_sql_cursor,2,l_order_number);
471 DBMS_SQL.DEFINE_COLUMN (l_sql_cursor,3,l_order_type_name,30);
472 DBMS_SQL.DEFINE_COLUMN (l_sql_cursor,4,l_sold_to_org_id);
473 DBMS_SQL.DEFINE_COLUMN (l_sql_cursor,5,l_price_list_id);
474 DBMS_SQL.DEFINE_COLUMN (l_sql_cursor,6,l_quote_number);
475 DBMS_SQL.DEFINE_COLUMN (l_sql_cursor,7,l_flow_status_code,30);
476 DBMS_SQL.DEFINE_COLUMN (l_sql_cursor,8,l_upgraded_flag,1);
477
478 l_dummy := DBMS_SQL.execute(l_sql_cursor);
479
480 LOOP
481
482
483
484 IF DBMS_SQL.FETCH_ROWS(l_sql_cursor) = 0 THEN
485 EXIT;
486 END IF;
487
488 DBMS_SQL.COLUMN_VALUE (l_sql_cursor,1,l_header_id);
489 DBMS_SQL.COLUMN_VALUE (l_sql_cursor,2,l_order_number);
490 DBMS_SQL.COLUMN_VALUE (l_sql_cursor,3,l_order_type_name);
491 DBMS_SQL.COLUMN_VALUE (l_sql_cursor,4,l_sold_to_org_id);
492 DBMS_SQL.COLUMN_VALUE (l_sql_cursor,5,l_price_list_id);
493 DBMS_SQL.COLUMN_VALUE (l_sql_cursor,6,l_quote_number);
494 DBMS_SQL.COLUMN_VALUE (l_sql_cursor,7,l_flow_status_code);
495 DBMS_SQL.COLUMN_VALUE (l_sql_cursor,8,l_upgraded_flag);
496
497 IF l_debug_level > 0 THEN
498 oe_debug_pub.add('Order Number : '||to_char(l_order_number));
499 END IF;
500
501
502 IF OE_CODE_CONTROL.Code_Release_Level >= '110510' THEN
503
504 l_purge := 'Y';
505
506 IF OE_CONTRACTS_UTIL.Check_License = 'Y' THEN
507
508 IF p_include_contractual_orders = 'N' THEN
509
510 IF OE_CONTRACTS_UTIL.Terms_Exists
511 ( p_doc_type => 'O'
512 , p_doc_id => l_header_id) = 'Y' THEN
513
514 l_purge := 'N';
515
516 OE_DEBUG_PUB.Add('Skip the Order:'||l_header_id);
517
518 END IF;
519 END IF;
520 END IF;
521
522 IF l_purge = 'Y' THEN
523
524 check_and_get_detail(l_purge_set_id
525 ,l_header_id
526 ,l_order_number
527 ,l_order_type_name
528 ,l_sold_to_org_id
529 ,l_price_list_id
530 ,l_quote_number
531 ,l_flow_status_code
532 ,l_upgraded_flag);
533
534 l_rec_count := l_rec_count + 1;
535
536 IF l_rec_count >= l_orders_per_commit THEN
537 COMMIT;
538 l_rec_count := 0;
539 END IF;
540
541 END IF;
542
543 ELSE
544 Check_And_Get_Detail(l_purge_set_id
545 ,l_header_id
546 ,l_order_number
547 ,l_order_type_name
548 ,l_sold_to_org_id
549 ,l_price_list_id);
550
551
552 l_rec_count := l_rec_count + 1;
553
554 IF l_rec_count >= l_orders_per_commit THEN
555 COMMIT;
556 l_rec_count := 0;
557 END IF;
558
559 END IF;
560
561 END LOOP;
562
563 DBMS_SQL.CLOSE_CURSOR(l_sql_cursor);
564 OE_DEBUG_PUB.Add('before update='|| l_purge_set_id);
565
566 UPDATE OE_PURGE_SETS
567 SET purge_processed = 'Y'
568 WHERE purge_set_id = l_purge_set_id;
569
570 COMMIT;
571
572 OE_DEBUG_PUB.Add('after update='|| to_char(SQL%ROWCOUNT));
573 OE_DEBUG_PUB.Add('End');
574
575
576 EXCEPTION
577 WHEN OTHERS THEN
578 l_dummy := DBMS_SQL.LAST_ERROR_POSITION;
579 fnd_file.put_line(FND_FILE.LOG,'At_Position '||l_dummy);
580 fnd_file.put_line(FND_FILE.LOG,'*** Error In Generate purge set ** '||substr(sqlerrm,1,300));
581
582 End Select_Where_Cond_Purge;
583
584
585 /*--------------------------------------------------------------------------------------------------------
586 Procedure : Select_Where_Cond_Purge_Quote
587 Description : Called from Quote Purge Selection concurrent program. Based on
588 the parameters it will construct the where condition. Call Insert_Purge_Set.
589 Calls Check_And_Get_Detail for every record satisfying the where condition.
590 DBMS_SQL Package is being used for building the where condition for optimization.
591 Change Record : Version 1
592 -------------------------------------------------------------------------------------------------------------*/
593
594
595 Procedure Select_Where_Cond_Purge_Quote
596 (
597 ERRBUF OUT NOCOPY /* file.sql.39 change */ VARCHAR2
598 ,RETCODE OUT NOCOPY /* file.sql.39 change */ VARCHAR2
599 ,p_organization_id IN NUMBER
600 ,p_purge_set_name IN VARCHAR2
601 ,p_purge_set_description IN VARCHAR2
602 ,p_quote_number_low IN NUMBER
603 ,p_quote_number_high IN NUMBER
604 ,p_order_type_id IN NUMBER
605 ,p_customer_id IN NUMBER
606 ,p_quote_date_low IN VARCHAR2
607 ,p_quote_date_high IN VARCHAR2
608 ,p_creation_date_low IN VARCHAR2
609 ,p_creation_date_high IN VARCHAR2
610 ,p_offer_exp_date_low IN VARCHAR2
611 ,p_offer_exp_date_high IN VARCHAR2
612 ,p_purge_exp_quotes IN VARCHAR2
613 ,p_purge_lost_quotes IN VARCHAR2
614 )
615 IS
616 l_sql_stmt VARCHAR2(4000) := NULL;
617 l_where_condition VARCHAR2(4000) := NULL ;
618 l_creation_date_low DATE;
619 l_quote_date_high DATE;
620 l_quote_date_low DATE;
621 l_creation_date_high DATE;
622 l_offer_exp_date_low DATE;
623 l_offer_exp_date_high DATE;
624 l_order_type_name VARCHAR2(30);
625 l_header_id NUMBER;
626 l_quote_number NUMBER;
627 l_expiration_date DATE;
628 l_flow_status_code VARCHAR2(30);
629 l_upgraded_flag VARCHAR2(1);
630 l_sold_to_org_id NUMBER;
631 l_price_list_id NUMBER;
632 l_purge_set_id NUMBER;
633 l_selected_ids SELECTED_IDS_TBL;
634 l_customer_name VARCHAR2(50);
635 l_orders_per_commit NUMBER := 10;
636 l_rec_count NUMBER := 0;
637 l_sql_cursor INTEGER;
638 l_dummy NUMBER;
639 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
640 l_org_id NUMBER;
641
642 CURSOR cur_get_org_for_ord_type IS
643 SELECT org_id
644 FROM OE_ORDER_TYPES_V --MOAC view based on multiple objects
645 WHERE order_type_id = p_order_type_id;
646
647 BEGIN
648 --Bug #4220950
649 ERRBUF := '';
650 RETCODE := 0;
651
652 -- Begining MOAC Changes
653
654 IF p_organization_id IS NOT NULL THEN
655
656 -- Setting a Single Org access.
657
658 MO_GLOBAL.set_policy_context ('S', p_organization_id);
659
660 ELSIF p_organization_id IS NULL THEN
661
662 --
663 -- If p_organization_id is NULL, then check if the p_order_type_id is NULL.
664 -- If it is not NULL then get the org_id for this assosiated transaction type
665 -- If the p_order_type_id is NULL the the Multiple Org access is set.
666 --
667
668 IF p_order_type_id IS NOT NULL THEN
669
670 OPEN cur_get_org_for_ord_type;
671 FETCH cur_get_org_for_ord_type INTO l_org_id;
672 CLOSE cur_get_org_for_ord_type;
673
674 -- Setting a Single Org access.
675
676 MO_GLOBAL.set_policy_context ('S', l_org_id);
677
678 ELSE
679 -- Setting a Multiple Org access.
680
681 MO_GLOBAL.set_policy_context('M','');
682
683 END IF;
684 END IF;
685
686
687 -- End MOAC Changes
688
689 l_sql_stmt :=
690 ' SELECT OOH.HEADER_ID,
691 OOH.QUOTE_NUMBER,
692 OOT.NAME,
693 OOH.SOLD_TO_ORG_ID,
694 OOH.PRICE_LIST_ID,
695 OOH.EXPIRATION_DATE,
696 OOH.FLOW_STATUS_CODE,
697 OOH.UPGRADED_FLAG
698 FROM OE_ORDER_HEADERS_ALL OOH,
699 OE_ORDER_TYPES_V OOT --MOAC view based on multiple objects
700 WHERE OOH.ORDER_TYPE_ID = OOT.ORDER_TYPE_ID
701 AND NVL(OOH.TRANSACTION_PHASE_CODE,''F'')=''N''';
702
703
704 IF l_debug_level > 0 THEN
705 OE_DEBUG_PUB.Add('Inside select_where_cond_quote');
706 END IF;
707
708
709 SELECT fnd_date.canonical_to_date(p_offer_exp_date_low),
710 fnd_date.canonical_to_date(p_offer_exp_date_high),
711 fnd_date.canonical_to_date(p_creation_date_low),
712 fnd_date.canonical_to_date(p_creation_date_high),
713 fnd_date.canonical_to_date(p_quote_date_low),
714 fnd_date.canonical_to_date(p_quote_date_high)
715 INTO l_offer_exp_date_low,
716 l_offer_exp_date_high,
717 l_creation_date_low,
718 l_creation_date_high,
719 l_quote_date_low,
720 l_quote_date_high
721 FROM DUAL;
722
723
724 --Quote Number Range
725
726 IF p_quote_number_low is NOT NULL AND p_quote_number_high is NOT NULL THEN
727 l_sql_stmt := l_sql_stmt || ' AND OOH.QUOTE_NUMBER >= :p1';
728 l_sql_stmt := l_sql_stmt || ' AND OOH.QUOTE_NUMBER <= :p2';
729 l_where_condition := 'Quote Number between '||to_char(p_quote_number_low)||' AND '||to_char(p_quote_number_high); -- bug 5667753
730 -- l_where_condition := 'Quote Number between :p1 AND :p2';
731 ELSIF p_quote_number_low is NULL AND p_quote_number_high is NOT NULL THEN
732 l_sql_stmt := l_sql_stmt || ' AND OOH.QUOTE_NUMBER <= :p2';
733 l_where_condition := 'Quote Number <= '||to_char(p_quote_number_high); -- bug 5667753
734 -- l_where_condition := 'Quote Number <= :p2';
735 ELSIF p_quote_number_low is NOT NULL AND p_quote_number_high is NULL THEN
736 l_sql_stmt := l_sql_stmt || ' AND OOH.QUOTE_NUMBER >= :p1';
737 l_where_condition := 'Quote Number >= '||to_char(p_quote_number_low); -- bug 5667753
738 -- l_where_condition := 'Quote Number >= :p1';
739 END IF;
740
741
742 --Order Type
743 IF p_order_type_id is NOT NULL THEN
744 SELECT NAME
745 INTO l_order_type_name
746 FROM OE_ORDER_TYPES_V --MOAC view based on multiple objects
747 WHERE ORDER_TYPE_ID = p_order_type_id;
748
749 l_sql_stmt := l_sql_stmt || ' AND OOH.ORDER_TYPE_ID = :p3';
750
751
752 IF l_where_condition IS NULL THEN
753 l_where_condition := 'Order Type = '||l_order_type_name; -- bug 5667753
754 -- l_where_condition := ' Order Type = :p11';
755 ELSE
756 l_where_condition := l_where_condition || ' AND Order Type = '||l_Order_type_name;
757 -- l_where_condition := l_where_condition || ' AND Order Type = :p11';
758 END IF;
759 END IF;
760
761
762 --Customer
763 IF nvl(p_customer_id,0) <> 0 THEN
764
765 SELECT p.PARTY_NAME
766 INTO l_customer_name
767 FROM HZ_CUST_ACCOUNTS c,
768 HZ_PARTIES p
769 WHERE c.CUST_ACCOUNT_ID = p_customer_id
770 AND c.PARTY_ID = p.PARTY_ID;
771
772 l_sql_stmt := l_sql_stmt || ' AND OOH.SOLD_TO_ORG_ID = :p4';
773
774 IF l_where_condition IS NULL THEN
775 l_where_condition := ' Customer = '||l_customer_name; -- bug 5667753
776 -- l_where_condition := ' Customer = :p12';
777 ELSE
778 l_where_condition := l_where_condition|| ' AND Customer = '||l_customer_name; -- bug 5667753
779 -- l_where_condition := l_where_condition|| ' AND Customer = :p12';
780 END IF;
781 END IF;
782
783
784 --Quote Date Range
785 --Bug 5702003
786
787 IF l_quote_date_low IS NOT NULL AND l_quote_date_high IS NOT NULL THEN
788 l_sql_stmt := l_sql_stmt || ' AND OOH.QUOTE_DATE >= :p5';
789 l_sql_stmt := l_sql_stmt || ' AND OOH.QUOTE_DATE <= :p6';
790
791 IF l_where_condition IS NULL THEN
792 l_where_condition := 'Quote Date Between '||to_char(l_quote_date_low,'MM/DD/RRRR HH24:MI:SS')||' AND '||to_char(l_quote_date_high,'MM/DD/RRRR HH24:MI:SS'); -- bug 5667753
793 -- l_where_condition := 'Quote Date Between :p5 and :p6';
794 ELSE
795 l_where_condition := l_where_condition|| ' AND Quote Date Between '||to_char(l_quote_date_low,'MM/DD/RRRR HH24:MI:SS')||' AND '||to_char(l_quote_date_high,'MM/DD/RRRR HH24:MI:SS'); -- bug 5667753
796 -- l_where_condition := l_where_condition|| ' AND Quote Date Between :p5 and :p6';
797 END IF;
798
799 ELSIF l_quote_date_low IS NOT NULL AND l_quote_date_high IS NULL THEN
800 l_sql_stmt := l_sql_stmt || ' AND OOH.QUOTE_DATE >= :p5';
801
802 IF l_where_condition IS NULL THEN
803 l_where_condition := 'Quote Date >= '||to_char(l_quote_date_low,'MM/DD/RRRR HH24:MI:SS'); -- bug 5667753
804 -- l_where_condition := 'Quote Date >= :p5';
805 ELSE
806 l_where_condition := l_where_condition||' AND Quote Date >= '||to_char(l_quote_date_low,'MM/DD/RRRR HH24:MI:SS'); -- bug 5667753
807 -- l_where_condition := l_where_condition|| ' AND Quote Date >= :p5';
808 END IF;
809
810 ELSIF l_quote_date_low IS NULL AND l_quote_date_high IS NOT NULL THEN
811 l_sql_stmt := l_sql_stmt || ' AND OOH.QUOTE_DATE <= :p6';
812
813 IF l_where_condition IS NULL THEN
814 l_where_condition := 'Quote Date <= '||to_char(l_quote_date_high,'MM/DD/RRRR HH24:MI:SS'); -- bug 5667753
815 -- l_where_condition := 'Quote Date <= :p6';
816 ELSE
817 l_where_condition := l_where_condition||' AND Quote Date <= '||to_char(l_quote_date_high,'MM/DD/RRRR HH24:MI:SS'); -- bug 5667753
818 -- l_where_condition := l_where_condition|| ' AND Quote Date >= :p6';
819 END IF;
820 END IF;
821
822
823 --Creation date Range
824 --Bug5702003
825
826 IF l_creation_date_low IS NOT NULL AND l_creation_date_high IS NOT NULL THEN
827 l_sql_stmt := l_sql_stmt || ' AND OOH.CREATION_DATE >= :p7';
828 l_sql_stmt := l_sql_stmt || ' AND OOH.CREATION_DATE <= :p8';
829
830 IF l_where_condition IS NULL THEN
831 l_where_condition := 'Quote Creation Date Between '||to_char(l_creation_date_low,'MM/DD/RRRR HH24:MI:SS')||' AND '||to_char(l_creation_date_high,'MM/DD/RRRR HH24:MI:SS'); -- bug 5667753
832 -- l_where_condition := 'Quote Creation Date Between :p7 and :p8';
833 ELSE
834 l_where_condition := l_where_condition|| ' AND Quote Creation Date Between '||to_char(l_creation_date_low,'MM/DD/RRRR HH24:MI:SS')||' AND '||to_char(l_creation_date_high,'MM/DD/RRRR HH24:MI:SS'); -- bug 5667753
835 -- l_where_condition := l_where_condition|| ' AND Quote Creation Date Between :p7 and :p8';
836 END IF;
837
838 ELSIF l_creation_date_low IS NOT NULL AND l_creation_date_high IS NULL THEN
839 l_sql_stmt := l_sql_stmt || ' AND OOH.CREATION_DATE >= :p7';
840
841 IF l_where_condition IS NULL THEN
842 l_where_condition := 'Quote Creation Date >= '||to_char(l_creation_date_low,'MM/DD/RRRR HH24:MI:SS'); -- bug 5667753
843 -- l_where_condition := 'Quote Creation Date >= :p7';
844 ELSE
845 l_where_condition := l_where_condition||' AND Quote Creation Date >= '||to_char(l_creation_date_low,'MM/DD/RRRR HH24:MI:SS'); -- bug 5667753
846 -- l_where_condition := l_where_condition|| ' AND Quote Creation Date >= :p7';
847 END IF;
848
849 ELSIF l_creation_date_low IS NULL AND l_creation_date_high IS NOT NULL THEN
850 l_sql_stmt := l_sql_stmt || ' AND OOH.CREATION_DATE <= :p8';
851
852 IF l_where_condition IS NULL THEN
853 l_where_condition := 'Quote Creation Date <= '||to_char(l_creation_date_high,'MM/DD/RRRR HH24:MI:SS'); -- bug 5667753
854 -- l_where_condition := 'Quote Creation Date <= :p8';
855 ELSE
856 l_where_condition := l_where_condition||' AND Quote Creation Date <= '||to_char(l_creation_date_high,'MM/DD/RRRR HH24:MI:SS'); -- bug 5667753
857 -- l_where_condition := l_where_condition|| ' AND Quote Creation Date >= :p8';
858 END IF;
859 END IF;
860
861
862 --Offer Expiration Date Range
863 --5702003
864
865 IF l_offer_exp_date_low IS NOT NULL AND l_offer_exp_date_high IS NOT NULL THEN
866 l_sql_stmt := l_sql_stmt || ' AND OOH.EXPIRATION_DATE >= :p9';
867 l_sql_stmt := l_sql_stmt || ' AND OOH.EXPIRATION_DATE <= :p10';
868 IF l_where_condition IS NULL THEN
869 l_where_condition := 'Quote Expiration Date Between '||to_char(l_offer_exp_date_low,'MM/DD/RRRR HH24:MI:SS')||' AND '||to_char(l_offer_exp_date_high,'MM/DD/RRRR HH24:MI:SS'); -- bug 5667753
870 -- l_where_condition := 'Quote Expiration Date Between :p9 and :p10';
871 ELSE
872 l_where_condition := l_where_condition|| ' AND Quote Expiration Date Between '||to_char(l_offer_exp_date_low,'MM/DD/RRRR HH24:MI:SS')||' AND '||to_char(l_offer_exp_date_high,'MM/DD/RRRR HH24:MI:SS'); -- bug 5667753
873 -- l_where_condition := l_where_condition|| ' AND Quote Expiration Date Between :p9 and :p10';
874 END IF;
875
876 ELSIF l_offer_exp_date_low IS NOT NULL AND l_offer_exp_date_high IS NULL THEN
877 l_sql_stmt := l_sql_stmt || ' AND OOH.EXPIRATION_DATE >= :p9';
878
879 IF l_where_condition IS NULL THEN
880 l_where_condition := 'Quote Expiration Date >= '||to_char(l_offer_exp_date_low,'MM/DD/RRRR HH24:MI:SS'); -- bug 5667753
881 -- l_where_condition := 'Quote Expiration Date >= :p9';
882 ELSE
883 l_where_condition := l_where_condition||' AND Quote Expiration Date >= '||to_char(l_offer_exp_date_low,'MM/DD/RRRR HH24:MI:SS'); -- bug 5667753
884 -- l_where_condition := l_where_condition|| ' AND Quote Expiration Date >= :p9';
885 END IF;
886
887 ELSIF l_offer_exp_date_low IS NULL AND l_offer_exp_date_high IS NOT NULL THEN
888 l_sql_stmt := l_sql_stmt || ' AND OOH.EXPIRATION_DATE <= :p10';
889
890 IF l_where_condition IS NULL THEN
891 l_where_condition := 'Quote Expiration Date <= '||to_char(l_offer_exp_date_high,'MM/DD/RRRR HH24:MI:SS'); -- bug 5667753
892 -- l_where_condition := 'Quote Expiration Date <= :p10';
893 ELSE
894 l_where_condition := l_where_condition||' AND Quote Expiration Date <= '||to_char(l_offer_exp_date_high,'MM/DD/RRRR HH24:MI:SS'); -- bug 5667753
895 l_where_condition := l_where_condition|| ' AND Quote Expiration Date >= :p10';
896 END IF;
897 END IF;
898
899
900 --To Purge only Expired Quotes
901 IF nvl(p_purge_exp_quotes,'Y')='Y' THEN
902 IF l_where_condition IS NULL THEN
903 l_where_condition := 'Purge Expired Quotes';
904 ELSE
905 l_where_condition := l_where_condition|| ' AND Purge Expired Quotes';
906 END IF;
907 END IF;
908
909
910 --To Purge only Lost Quotes
911 IF nvl(p_purge_lost_quotes,'Y')='Y' THEN
912 IF l_where_condition IS NULL THEN
913 l_where_condition := 'Purge Lost Quotes';
914 ELSE
915 l_where_condition := l_where_condition|| ' AND Purge Lost Quotes';
916 END IF;
917 END IF;
918
919 --The sql statement for Purge Expired and Lost Quotes.
920 IF nvl(p_purge_exp_quotes,'Y')='Y' THEN
921 IF nvl(p_purge_lost_quotes,'Y') ='Y' THEN
922 l_sql_stmt:= l_sql_stmt|| ' AND OOH.FLOW_STATUS_CODE IN (''LOST'',''OFFER_EXPIRED'')';
923 ELSE
924 l_sql_stmt:=l_sql_stmt||' AND OOH.FLOW_STATUS_CODE=''OFFER_EXPIRED''';
925 END IF;
926 ELSE
927 IF nvl(p_purge_lost_quotes,'Y')='Y' THEN
928 l_sql_stmt:=l_sql_stmt||' AND OOH.FLOW_STATUS_CODE=''LOST''';
929 END IF;
930 END IF;
931
932
933 IF l_debug_level > 0 THEN
934 oe_debug_pub.add('Where Condition : '||l_where_condition);
935 oe_debug_pub.add('Cursor Query : '||l_sql_stmt);
936 END IF;
937 OE_ORDER_PURGE_PVT.Insert_Purge_Set
938 (
939 p_purge_set_name => p_purge_set_name,
940 p_purge_set_description => p_purge_set_description,
941 p_purge_set_request_Id => 1,
942 p_purge_set_submit_datetime => SYSDATE,
943 p_selected_ids => l_selected_ids,
944 p_count_selected => 0,
945 p_where_condition => l_where_condition,
946 p_created_by => FND_GLOBAL.USER_ID,
947 p_last_updated_by => FND_GLOBAL.USER_ID,
948 x_purge_set_id => l_purge_set_id
949 );
950
951
952 l_sql_cursor := DBMS_SQL.Open_Cursor;
953
954 DBMS_SQL.PARSE(l_sql_cursor, l_sql_stmt, DBMS_SQL.NATIVE);
955
956
957 --Binding the variables
958 IF p_quote_number_low is NOT NULL THEN
959 DBMS_SQL.BIND_VARIABLE (l_sql_cursor, ':p1', p_quote_number_low);
960 END IF;
961 IF p_quote_number_high is NOT NULL THEN
962 DBMS_SQL.BIND_VARIABLE (l_sql_cursor, ':p2', p_quote_number_high);
963 END IF;
964 IF p_order_type_id is NOT NULL THEN
965 DBMS_SQL.BIND_VARIABLE (l_sql_cursor, ':p3', p_order_type_id);
966 END IF;
967 IF p_customer_id is NOT NULL THEN
968 DBMS_SQL.BIND_VARIABLE (l_sql_cursor, ':p4', p_customer_id);
969 END IF;
970 IF p_quote_date_low is NOT NULL THEN
971 DBMS_SQL.BIND_VARIABLE (l_sql_cursor, ':p5', l_quote_date_low);
972 END IF;
973 IF p_quote_date_high is NOT NULL THEN
974 DBMS_SQL.BIND_VARIABLE (l_sql_cursor, ':p6', l_quote_date_high);
975 END IF;
976 IF p_creation_date_low is NOT NULL THEN
977 DBMS_SQL.BIND_VARIABLE (l_sql_cursor, ':p7', l_creation_date_low);
978 END IF;
979 IF p_creation_date_high is NOT NULL THEN
980 DBMS_SQL.BIND_VARIABLE (l_sql_cursor, ':p8', l_creation_date_high);
981 END IF;
982 IF p_offer_exp_date_low is NOT NULL THEN
983 DBMS_SQL.BIND_VARIABLE (l_sql_cursor, ':p9', l_offer_exp_date_low);
984 END IF;
985 IF p_offer_exp_date_high is NOT NULL THEN
986 DBMS_SQL.BIND_VARIABLE (l_sql_cursor, ':p10', l_offer_exp_date_high);
987 END IF;
988
989 DBMS_SQL.DEFINE_COLUMN (l_sql_cursor,1,l_header_id);
990 DBMS_SQL.DEFINE_COLUMN (l_sql_cursor,2,l_quote_number);
991 DBMS_SQL.DEFINE_COLUMN (l_sql_cursor,3,l_order_type_name,30);
992 DBMS_SQL.DEFINE_COLUMN (l_sql_cursor,4,l_sold_to_org_id);
993 DBMS_SQL.DEFINE_COLUMN (l_sql_cursor,5,l_price_list_id);
994 DBMS_SQL.DEFINE_COLUMN (l_sql_cursor,6,l_expiration_date);
995 DBMS_SQL.DEFINE_COLUMN (l_sql_cursor,7,l_flow_status_code,30);
996 DBMS_SQL.DEFINE_COLUMN (l_sql_cursor,8,l_upgraded_flag,1);
997
998 l_dummy := DBMS_SQL.execute(l_sql_cursor);
999
1000 LOOP
1001
1002 IF DBMS_SQL.FETCH_ROWS(l_sql_cursor) = 0 THEN
1003 EXIT;
1004 END IF;
1005
1006 DBMS_SQL.COLUMN_VALUE (l_sql_cursor,1,l_header_id);
1007 DBMS_SQL.COLUMN_VALUE (l_sql_cursor,2,l_quote_number);
1008 DBMS_SQL.COLUMN_VALUE (l_sql_cursor,3,l_order_type_name);
1009 DBMS_SQL.COLUMN_VALUE (l_sql_cursor,4,l_sold_to_org_id);
1010 DBMS_SQL.COLUMN_VALUE (l_sql_cursor,5,l_price_list_id);
1011 DBMS_SQL.COLUMN_VALUE (l_sql_cursor,6,l_expiration_date);
1012 DBMS_SQL.COLUMN_VALUE (l_sql_cursor,7,l_flow_status_code);
1013 DBMS_SQL.COLUMN_VALUE (l_sql_cursor,8,l_upgraded_flag);
1014
1015 IF l_debug_level > 0 THEN
1016 oe_debug_pub.add('Quote Number : '||to_char(l_quote_number));
1017 END IF;
1018
1019 Check_And_Get_Detail(l_purge_set_id
1020 ,l_header_id
1021 ,null
1022 ,l_order_type_name
1023 ,l_sold_to_org_id
1024 ,l_price_list_id
1025 ,l_quote_number
1026 ,l_flow_status_code
1027 ,l_upgraded_flag
1028 ,l_expiration_date);
1029
1030 l_rec_count := l_rec_count + 1;
1031 IF l_rec_count >= l_orders_per_commit THEN
1032 COMMIT;
1033 l_rec_count := 0;
1034 END IF;
1035 END LOOP;
1036
1037 DBMS_SQL.CLOSE_CURSOR(l_sql_cursor);
1038
1039 IF l_debug_level > 0 THEN
1040 OE_DEBUG_PUB.Add('before update='|| l_purge_set_id);
1041 END IF;
1042
1043 UPDATE OE_PURGE_SETS
1044 SET purge_processed = 'Y'
1045 WHERE purge_set_id = l_purge_set_id;
1046
1047 COMMIT;
1048
1049 IF l_debug_level > 0 THEN
1050 OE_DEBUG_PUB.Add('after update='|| to_char(SQL%ROWCOUNT));
1051 OE_DEBUG_PUB.Add('End');
1052 END IF;
1053
1054
1055 EXCEPTION
1056 WHEN OTHERS THEN
1057 l_dummy := DBMS_SQL.LAST_ERROR_POSITION;
1058 fnd_file.put_line(FND_FILE.LOG,'At_Position '||l_dummy);
1059 fnd_file.put_line(FND_FILE.LOG,'*** Error In Generate purge set ** '||substr(sqlerrm,1,300));
1060 End Select_Where_Cond_Purge_Quote;
1061
1062
1063 PROCEDURE Select_Ids_Purge
1064 (
1065 p_purge_set_id IN NUMBER
1066 , p_selected_ids_tbl IN SELECTED_IDS_TBL
1067 , p_count_selected IN NUMBER
1068 , p_orders_per_commit IN NUMBER
1069 )
1070 IS
1071 l_order_number NUMBER := 0;
1072 l_order_type_name VARCHAR2(30);
1073 l_customer_number NUMBER := 0;
1074 l_price_list_id NUMBER := 0;
1075 l_error_message VARCHAR2(200);
1076 l_header_id NUMBER;
1077 l_rec_count NUMBER := 0;
1078 l_quote_number NUMBER;
1079 l_expiration_date DATE;
1080 l_flow_status_code VARCHAR2(30);
1081 l_upgraded_flag VARCHAR2(1);
1082 l_transaction_phase_code VARCHAR2(1);
1083
1084
1085 BEGIN
1086
1087 oe_debug_pub.Add('Entering OE_Order_Purge_PVT.Select_Ids_Purge : '||p_purge_set_id,1);
1088
1089 FOR I IN 1..p_count_selected
1090 LOOP
1091
1092 l_header_id := p_selected_ids_tbl(I);
1093
1094 OE_DEBUG_PUB.Add('Header Id '||to_char(l_header_id));
1095
1096 --Quote purge changes.Select the added fields
1097 --and transaction phase code
1098
1099 SELECT ooh.order_number
1100 , oot.name
1101 , ooh.sold_to_org_id
1102 , ooh.price_list_id
1103 , ooh.quote_number
1104 , ooh.expiration_date
1105 , ooh.flow_status_code
1106 , ooh.upgraded_flag
1107 , ooh.transaction_phase_code
1108 INTO
1109 l_order_number
1110 , l_order_type_name
1111 , l_customer_number
1112 , l_price_list_id
1113 , l_quote_number
1114 , l_expiration_date
1115 , l_flow_status_code
1116 , l_upgraded_flag
1117 , l_transaction_phase_code
1118 From oe_order_types_v oot, --MOAC view based on multiple objects
1119 oe_order_headers_all ooh
1120
1121 WHERE
1122 ooh.header_id = l_header_id
1123 AND ooh.order_type_id = oot.order_type_id;
1124
1125 --Quote Purge Changes.To mask order number for a
1126 --quote and Expiration date for orders.
1127
1128 IF OE_CODE_CONTROL.Get_Code_Release_Level >= '110510' THEN
1129 IF nvl(l_transaction_phase_code,'F')='N' THEN
1130 l_order_number := Null;
1131 ELSE
1132 l_expiration_date:= Null;
1133 END IF;
1134 check_and_get_detail(p_purge_set_id
1135 ,l_header_id
1136 ,l_order_number
1137 ,l_order_type_name
1138 ,l_customer_number
1139 ,l_price_list_id
1140 ,l_quote_number
1141 ,l_flow_status_code
1142 ,l_upgraded_flag
1143 ,l_expiration_date);
1144 ELSE
1145 check_and_get_detail(p_purge_set_id
1146 ,l_header_id
1147 ,l_order_number
1148 ,l_order_type_name
1149 ,l_customer_number
1150 ,l_price_list_id);
1151 END IF;
1152 l_rec_count := l_rec_count + 1;
1153
1154 IF l_rec_count >= p_orders_per_commit THEN
1155 COMMIT;
1156 l_rec_count := 0;
1157 END IF;
1158
1159 END LOOP;
1160
1161
1162 OE_DEBUG_PUB.Add('before update='|| p_purge_set_id);
1163
1164 UPDATE OE_PURGE_SETS
1165 SET purge_processed = 'Y'
1166 WHERE purge_set_id = p_purge_set_id;
1167
1168 COMMIT;
1169
1170 OE_DEBUG_PUB.Add('after update='|| to_char(SQL%ROWCOUNT));
1171
1172 oe_debug_pub.Add('Exiting OE_Order_Purge_PVT.Select_Ids_Purge : '||p_purge_set_id,1);
1173
1174 EXCEPTION
1175 WHEN OTHERS THEN
1176 fnd_file.put_line(FND_FILE.LOG,'*** Error In Generate purge set ** '||substr(sqlerrm,1,300));
1177 END Select_Ids_Purge;
1178
1179 PROCEDURE Insert_Purge_Set
1180 (
1181 p_purge_set_name IN VARCHAR2
1182 , p_purge_set_description IN VARCHAR2
1183 , p_purge_set_request_Id IN NUMBER
1184 , p_purge_set_submit_datetime IN DATE
1185 , p_selected_ids IN SELECTED_IDS_TBL
1186 , p_count_selected IN NUMBER
1187 , p_where_condition IN VARCHAR2
1188 , p_created_by IN NUMBER
1189 , p_last_updated_by IN NUMBER
1190 , x_purge_set_id OUT NOCOPY /* file.sql.39 change */ NUMBER
1191 )
1192
1193 IS
1194 l_purge_set_id NUMBER;
1195 l_separator VARCHAR2(1) := ',';
1196 l_selected_ids CLOB;
1197 l_position INTEGER := 1;
1198 l_buffer VARCHAR2(32767);
1199 l_amount BINARY_INTEGER := 32767;
1200 l_orders_per_commit NUMBER;
1201
1202 BEGIN
1203
1204 oe_debug_pub.add('Entering OE_Order_Purge_PVT.Insert_Purge_Set : '||p_purge_set_name,1);
1205
1206 SELECT OE_PURGE_SETS_S.NEXTVAL
1207 INTO l_purge_set_id
1208 FROM DUAL;
1209
1210 l_orders_per_commit := FND_PROFILE.VALUE('OM_ORDERS_PURGE_PER_COMMIT');
1211
1212 INSERT INTO OE_PURGE_SETS
1213 ( PURGE_SET_ID
1214 , PURGE_SET_NAME
1215 , PURGE_SET_DESCRIPTION
1216 , PURGE_SET_REQUEST_ID
1217 , PURGE_SET_SUBMIT_DATETIME
1218 , COUNT_SELECTED
1219 , PURGE_PROCESSED
1220 , PURGE_SET_PURGED
1221 , WHERE_CONDITION
1222 , ORDERS_PER_COMMIT
1223 , SELECTED_IDS
1224 , CREATION_DATE
1225 , CREATED_BY
1226 , LAST_UPDATE_DATE
1227 , LAST_UPDATED_BY
1228 )
1229 VALUES
1230 ( l_purge_set_id
1231 , p_purge_set_name
1232 , p_purge_set_description
1233 , p_purge_set_request_Id
1234 , p_purge_set_submit_datetime
1235 , p_count_selected
1236 , 'N'
1237 , 'N'
1238 , p_where_condition
1239 , l_orders_per_commit
1240 , EMPTY_CLOB()
1241 , sysdate
1242 , p_created_by
1243 , sysdate
1244 , p_last_updated_by);
1245
1246 x_purge_set_id := l_purge_set_id;
1247
1248 oe_debug_pub.add('Purge Set ID : '||to_char(l_purge_set_id));
1249
1250 IF p_count_selected <> 0 THEN
1251
1252 SELECT SELECTED_IDS
1253 INTO l_selected_ids
1254 FROM OE_PURGE_SETS
1255 WHERE PURGE_SET_ID = l_purge_set_id;
1256
1257 DBMS_LOB.OPEN(l_selected_ids,DBMS_LOB.LOB_READWRITE);
1258
1259 FOR I IN 1 .. p_count_selected
1260 LOOP
1261 IF (length(l_buffer) + length(p_selected_ids(I))) > l_amount THEN
1262 oe_debug_pub.add('Reached the limit : '||to_char(length(l_buffer)));
1263 l_amount := length(l_buffer);
1264 DBMS_LOB.WRITE(l_selected_ids,l_amount,l_position,l_buffer);
1265 l_buffer := '';
1266 l_position := l_position + l_amount;
1267 END IF;
1268
1269 IF I = 1 THEN
1270 l_buffer := l_buffer || to_char(p_selected_ids(I));
1271 ELSE
1272 l_buffer := l_buffer||l_separator||to_char(p_selected_ids(I));
1273 END IF;
1274
1275 END LOOP;
1276
1277 oe_debug_pub.add('Length : '||to_char(length(l_buffer)));
1278 l_amount := length(l_buffer);
1279 DBMS_LOB.WRITE(l_selected_ids,l_amount,l_position,l_buffer);
1280 DBMS_LOB.CLOSE(l_selected_ids);
1281
1282 oe_debug_pub.add('Value : '||l_buffer);
1283
1284 END IF;
1285
1286 oe_debug_pub.add('Exiting OE_Order_Purge_PVT.Insert_Purge_Set : '||to_char(x_purge_set_id),1);
1287
1288 END Insert_Purge_Set;
1289
1290 --Quote purge changes:
1291 --1. Modified the signature
1292 --2. Query Transaction Phase Code and make appropriate calls
1293 --3. Added field in insert statement
1294
1295 PROCEDURE Check_And_Get_Detail
1296 (
1297 p_purge_set_id IN NUMBER
1298 , p_header_id IN NUMBER
1299 , p_order_number IN NUMBER
1300 , p_order_type_name IN VARCHAR2
1301 , p_customer_number IN NUMBER
1302 , p_price_list_id IN NUMBER
1303 , p_quote_number IN NUMBER DEFAULT NULL
1304 , p_flow_status_code IN VARCHAR2 DEFAULT NULL
1305 , p_upgraded_flag IN VARCHAR2 DEFAULT NULL
1306 , p_expiration_date IN DATE DEFAULT NULL
1307 )
1308 IS
1309
1310 l_return_status VARCHAR2(1) := FND_API.G_TRUE;
1311 l_error_message VARCHAR2(2000);
1312 l_temp_mesg VARCHAR2(2000);
1313 l_is_purgable VARCHAR2(1);
1314 l_order_type_name VARCHAR2(30);
1315 l_flow_status VARCHAR2(80);
1316 l_transaction_phase_code VARCHAR2(1);
1317 -- 3789233
1318 l_cnt NUMBER;
1319
1320 BEGIN
1321
1322 oe_debug_pub.add('Entering OE_ORDER_PURGE_PVT.Check_And_Get_Detail : '||to_char(p_order_number));
1323 --Quote purge changes.To Select Transaction Phase code
1324 SELECT transaction_phase_code
1325 INTO l_transaction_phase_code
1326 FROM oe_order_headers
1327 WHERE header_id=p_header_id;
1328
1329 IF p_flow_status_code IS NOT NULL THEN
1330 SELECT meaning
1331 INTO l_flow_status
1332 FROM fnd_lookup_values lv
1333 WHERE lv.lookup_code=p_flow_status_code
1334 AND lookup_type='LINE_FLOW_STATUS'
1335 AND LANGUAGE = userenv('LANG')
1336 AND VIEW_APPLICATION_ID = 660
1337 AND SECURITY_GROUP_ID =
1338 fnd_global.Lookup_Security_Group(lv.lookup_type,
1339 lv.view_application_id);
1340 END IF;
1341
1342 IF OE_CODE_CONTROL.Get_Code_Release_Level >= '110510'
1343 AND nvl(l_transaction_phase_code,'F')='N' THEN
1344
1345 IF l_return_status = FND_API.G_TRUE THEN
1346
1347 FND_MESSAGE.SET_NAME('ONT','OE_PUR_OPEN_QUOTE');
1348 l_temp_mesg := FND_MESSAGE.GET_ENCODED;
1349 FND_MESSAGE.SET_ENCODED(l_temp_mesg);
1350 l_error_message := FND_MESSAGE.GET;
1351 l_return_status := OE_ORDER_PURGE_PVT.Check_Open_Quotes(p_header_id);
1352
1353 END IF;
1354
1355 ELSE
1356 IF l_return_status = FND_API.G_TRUE THEN
1357 FND_MESSAGE.SET_NAME('ONT','OE_PUR_OPEN_ORDER');
1358 l_temp_mesg := FND_MESSAGE.GET_ENCODED;
1359 FND_MESSAGE.SET_ENCODED(l_temp_mesg);
1360 l_error_message := FND_MESSAGE.GET;
1361 --for Bug # 4516769
1362 l_return_status := OE_ORDER_PURGE_PVT.Check_Open_Orders
1363 ( p_header_id);
1364 END IF;
1365
1366 IF l_return_status = FND_API.G_TRUE THEN
1367
1368 SELECT otl.name
1369 INTO l_order_type_name
1370 FROM oe_transaction_types_tl otl,
1371 oe_order_headers ooh
1372 WHERE otl.language = (select language_code
1373 from fnd_languages
1374 where installed_flag = 'B')
1375 AND otl.transaction_type_id = ooh.order_type_id
1376 AND ooh.header_id = p_header_id;
1377
1378 FND_MESSAGE.SET_NAME('ONT','OE_PUR_OPEN_INVOICES');
1379 l_temp_mesg := FND_MESSAGE.GET_ENCODED;
1380 FND_MESSAGE.SET_ENCODED(l_temp_mesg);
1381 l_error_message := FND_MESSAGE.GET;
1382 l_return_status := OE_ORDER_PURGE_PVT.check_open_invoiced_orders
1383 ( TO_CHAR(p_order_number), l_order_type_name );
1384 END IF;
1385
1386 IF l_return_status = FND_API.G_TRUE THEN
1387 FND_MESSAGE.SET_NAME('ONT','OE_PUR_OPEN_RETURNS');
1388 l_temp_mesg := FND_MESSAGE.GET_ENCODED;
1389 FND_MESSAGE.SET_ENCODED(l_temp_mesg);
1390 l_error_message := FND_MESSAGE.GET;
1391 l_return_status := OE_ORDER_PURGE_PVT.check_open_returns
1392 (p_order_number, p_order_type_name);
1393 END IF;
1394
1395 IF l_return_status = FND_API.G_TRUE THEN
1396 Check_Open_RMA_Receipts(p_header_id,
1397 l_return_status, l_error_message);
1398 END IF;
1399
1400 -- 3789233
1401 IF l_return_status = FND_API.G_TRUE THEN
1402 FND_MESSAGE.SET_NAME('ONT','OE_PUR_OPEN_DELIVERIES');
1403 l_temp_mesg := FND_MESSAGE.GET_ENCODED;
1404 FND_MESSAGE.SET_ENCODED(l_temp_mesg);
1405 l_error_message := FND_MESSAGE.GET;
1406
1407 select count(*)
1408 into l_cnt
1409 from wsh_delivery_details dd,
1410 oe_order_lines l
1411 where l.header_id = p_header_id
1412 and dd.source_line_id = l.line_id
1413 AND dd.org_id = l.org_id
1414 and dd.source_code = 'OE'
1415 and (nvl(dd.released_status, 'N') not in ('C', 'D') or
1416 ( dd.released_status = 'C' and
1417 ( nvl(dd.inv_interfaced_flag, 'N') in ( 'N','P') or
1418 nvl(dd.oe_interfaced_flag, 'N') in ( 'N','P')
1419 )
1420 )
1421 );
1422 IF l_cnt > 0 THEN
1423 l_return_status := FND_API.G_FALSE;
1424 END IF;
1425 END IF;
1426
1427 -- Purge Changes for 11i.10
1428
1429 IF l_return_status = FND_API.G_TRUE THEN
1430
1431 IF PO_CODE_RELEASE_GRP.Current_Release >=
1432 PO_CODE_RELEASE_GRP.PRC_11i_Family_Pack_J AND
1433 OE_CODE_CONTROL.Code_Release_Level >= '110510' THEN
1434
1435 l_return_status :=
1436 OE_ORDER_PURGE_PVT.Check_Open_PO_Reqs_Dropship
1437 (p_header_id => p_header_id
1438 );
1439
1440 IF l_return_status = FND_API.G_FALSE THEN
1441
1442 FND_MESSAGE.SET_NAME('ONT','OE_PURGE_OPEN_PO_REQ');
1443 l_temp_mesg := FND_MESSAGE.GET_ENCODED;
1444 FND_MESSAGE.SET_ENCODED(l_temp_mesg);
1445 l_error_message := FND_MESSAGE.GET;
1446
1447 END IF;
1448
1449 END IF;
1450 END IF;
1451 END IF;
1452
1453 IF l_return_status = FND_API.G_TRUE THEN
1454 l_error_message := NULL;
1455 l_is_purgable := 'Y' ;
1456 ELSE
1457 l_error_message := l_error_message;
1458 OE_DEBUG_PUB.Add(l_error_message);
1459 l_is_purgable := 'N';
1460 END IF;
1461
1462 INSERT INTO OE_PURGE_ORDERS
1463 ( PURGE_SET_ID,
1464 HEADER_ID,
1465 ORDER_NUMBER,
1466 QUOTE_NUMBER,
1467 ORDER_TYPE_NAME,
1468 CUSTOMER_NUMBER,
1469 PRICE_LIST_ID,
1470 IS_PURGABLE,
1471 IS_PURGED,
1472 ERROR_TEXT,
1473 FLOW_STATUS,
1474 EXPIRATION_DATE,
1475 UPGRADED_FLAG,
1476 CREATION_DATE,
1477 CREATED_BY,
1478 LAST_UPDATE_DATE,
1479 LAST_UPDATED_BY,
1480 LAST_UPDATE_LOGON,
1481 REQUEST_ID,
1482 PROGRAM_ID,
1483 PROGRAM_APPLICATION_ID)
1484 VALUES ( p_purge_set_id
1485 , p_header_id
1486 , p_order_number
1487 , p_quote_number
1488 , p_order_type_name
1489 , p_customer_number
1490 , p_price_list_id
1491 , l_is_purgable
1492 ,'N'
1493 , l_error_message
1494 , l_flow_status
1495 , p_expiration_date
1496 , p_upgraded_flag
1497 , sysdate
1498 , FND_GLOBAL.USER_ID
1499 , sysdate
1500 , FND_GLOBAL.LOGIN_ID
1501 , NULL
1502 , NULL
1503 , 0
1504 ,660);
1505
1506
1507 oe_debug_pub.add('Exiting OE_ORDER_PURGE_PVT.Check_And_Get_Detail : '||to_char(p_order_number));
1508
1509 END check_and_get_detail;
1510
1511 --
1512 -- As per the MOAC changes the 'Order Purge Job' would be MULTI Organizational concurrent Request.
1513 -- But the organization_id is not passed as this would be always passed as NULL.
1514 --
1515
1516 PROCEDURE Submit_Purge
1517 (
1518 p_dummy1 IN VARCHAR2
1519 , p_dummy2 IN VARCHAR2
1520 , p_purge_set_id IN NUMBER
1521 )
1522
1523 IS
1524
1525 l_header_id NUMBER := 0;
1526 l_return_status VARCHAR2(1);
1527 l_number_of_rec NUMBER := 0;
1528 l_orders_per_commit NUMBER;
1529 l_number_of_orders NUMBER := 0; --added for bug 3680441
1530 l_purged_success NUMBER := 0; --added for bug 3680441
1531 l_purge_failure NUMBER := 0; --added for bug 3680441
1532 l_purge_set_name OE_PURGE_SETS.PURGE_SET_NAME%TYPE;
1533 l_savepoint_est VARCHAR2(1) := 'N';
1534
1535 CURSOR c_purge_orders IS
1536 SELECT header_id
1537 FROM oe_purge_orders
1538 Where purge_set_id = p_purge_set_id
1539 AND NVL(is_purgable,'N') = 'Y'
1540 AND NVL(is_purged,'N') = 'N';
1541
1542 CURSOR cur_logged_in_user IS
1543 SELECT created_by
1544 FROM oe_purge_orders
1545 WHERE purge_set_id = p_purge_set_id;
1546
1547 l_created_by oe_purge_sets.created_by%type;
1548 BEGIN
1549
1550 oe_debug_pub.add('Entering OE_ORDER_PURGE_PVT.Submit_Purge : '||to_char(p_purge_set_id));
1551 fnd_file.put_line(FND_FILE.LOG,'Parameters :');
1552 fnd_file.put_line(FND_FILE.LOG,'Purge Set Name : '||p_purge_set_id);
1553
1554 OPEN cur_logged_in_user;
1555 FETCH cur_logged_in_user INTO l_created_by;
1556 CLOSE cur_logged_in_user;
1557
1558
1559 IF l_created_by <> FND_GLOBAL.USER_ID THEN
1560
1561 FND_MESSAGE.SET_NAME ('ONT', 'ONT_ONLY_CREATOR_CAN_PURGE');
1562 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET ());
1563 APP_EXCEPTION.RAISE_EXCEPTION;
1564
1565 END IF;
1566
1567
1568 SELECT orders_per_commit, purge_set_name
1569 INTO l_orders_per_commit, l_purge_set_name
1570 FROM OE_PURGE_SETS
1571 WHERE purge_set_id = p_purge_set_id
1572 AND created_by = fnd_global.user_id;
1573
1574 IF nvl(l_orders_per_commit,0) = 0 THEN
1575 l_orders_per_commit := 1;
1576 oe_debug_pub.add('Orders per commit is not defined ');
1577 END IF;
1578
1579
1580 oe_debug_pub.add('Orders per commit : '||to_char(l_orders_per_commit),1);
1581
1582 IF l_purge_set_name IS NOT NULL THEN
1583
1584 --
1585 -- This code is added because we query the records form the Secured
1586 -- Synonym. The Secured synonym would require the Context to be set else
1587 -- they would not fetch any data. So the data of all the org attached to -
1588 -- the responsibility would be fetched, the segration would be only based
1589 -- on the created_by
1590 --
1591
1592 --
1593 -- We are purging the data based on the crested_by of the Purge Set and
1594 -- Orders in the purge set, so we are not restricting on a Single Org.
1595 --
1596
1597 MO_GLOBAL.set_policy_context('M','');
1598
1599
1600 --added for bug 3680441
1601 SELECT count(*)
1602 INTO l_number_of_orders
1603 FROM oe_purge_orders
1604 where purge_set_id = p_purge_set_id;
1605
1606 OPEN c_purge_orders;
1607
1608 LOOP
1609
1610 SAVEPOINT ORDER_HEADER;
1611 l_savepoint_est := 'Y';
1612
1613 FETCH c_purge_orders INTO l_header_id;
1614 OE_DEBUG_PUB.Add('loop purging='||to_char(l_header_id));
1615
1616 EXIT WHEN c_purge_orders%NOTFOUND OR c_purge_orders%NOTFOUND IS NULL;
1617 -- end of fetch or empty cursor
1618
1619 BEGIN
1620
1621
1622 OE_ORDER_PURGE_PVT.oe_purge_headers
1623 (p_purge_set_id,
1624 l_header_id,
1625 l_return_status);
1626
1627 oe_debug_pub.add('Returned from oe_purge_orders : '||l_return_status);
1628
1629 EXCEPTION
1630 WHEN OTHERS THEN
1631 NULL;
1632
1633 END;
1634
1635 BEGIN
1636
1637 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
1638 OE_ORDER_PURGE_PVT.oe_purge_lines
1639 (p_purge_set_id,
1640 l_header_id,
1641 l_return_status
1642 );
1643 oe_debug_pub.add('Returned from oe_purge_lines : '||l_return_status);
1644
1645 END IF;
1646
1647 EXCEPTION
1648 WHEN OTHERS THEN
1649 NULL;
1650 END;
1651
1652 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
1653
1654 UPDATE OE_PURGE_ORDERS
1655 SET IS_PURGED = 'Y'
1656 WHERE purge_set_id = p_purge_set_id
1657 AND header_id = l_header_id
1658 AND created_by = fnd_global.user_id;
1659
1660 l_number_of_rec := l_number_of_rec + 1;
1661 l_purged_success := l_purged_success + 1; --added for bug 3680441
1662
1663 IF l_number_of_rec = l_orders_per_commit THEN
1664 COMMIT;
1665 l_savepoint_est := 'N';
1666 l_number_of_rec := 0;
1667 END IF;
1668
1669 ELSE
1670 NULL;
1671 END IF;
1672
1673 END LOOP;
1674
1675 CLOSE c_purge_orders;
1676 oe_debug_pub.add('before setting purge_Set_purge');
1677
1678 UPDATE OE_PURGE_SETS
1679 SET PURGE_SET_PURGED = 'Y',
1680 LAST_UPDATE_DATE = SYSDATE,
1681 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
1682 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
1683 WHERE PURGE_SET_ID = p_purge_set_id
1684 AND CREATED_BY = fnd_global.user_id;
1685 COMMIT;
1686 --added for bug 3680441
1687 l_purge_failure := l_number_of_orders - l_purged_success;
1688
1689 IF ( l_purge_failure > 0 )THEN
1690 FND_MESSAGE.SET_NAME ('ONT', 'ONT_FAILED_ORD_SECURITY');
1691 FND_MESSAGE.SET_TOKEN ('SNAME',l_purge_set_name );
1692 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET ());
1693 END IF;
1694
1695 FND_FILE.PUT_LINE(FND_FILE.LOG,'Number of orders/quotes selected for purge : '||l_number_of_orders);
1696 FND_FILE.PUT_LINE(FND_FILE.LOG,'Number of orders/quotes purged successfully : '||l_purged_success);
1697 FND_FILE.PUT_LINE(FND_FILE.LOG,'Number of orders/quotes failed purge : '||l_purge_failure);
1698 --end of change for bug 3680441
1699 oe_debug_pub.add('Exiting OE_ORDER_PURGE_PVT.Submit_Purge : ');
1700
1701 -- Begining of MOAC Changes
1702 ELSE
1703 fnd_file.put_line(FND_FILE.LOG,'Orders could not be Purged as the Purge Set selected is Created by another User.');
1704 END IF;
1705 -- End of MOAC Changes
1706
1707 EXCEPTION -- Exception handler to record error
1708 WHEN OTHERS THEN
1709 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1710
1711 IF l_savepoint_est = 'Y' THEN
1712 ROLLBACK TO SAVEPOINT ORDER_HEADER;
1713 END IF;
1714
1715 OE_DEBUG_PUB.Add('rollback 2');
1716 record_errors
1717 (
1718 l_return_status,
1719 p_purge_set_id,
1720 l_header_id ,
1721 'ORDPUR: '||substr(sqlerrm,1,200)
1722 );
1723 CLOSE c_purge_orders;
1724
1725 END Submit_Purge;
1726
1727 PROCEDURE Delete_Purge_Set
1728 (
1729 p_purge_set_id IN NUMBER
1730 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1731 )
1732 IS
1733
1734 Cursor c_purge_set is
1735 SELECT PURGE_SET_NAME,
1736 PURGE_SET_PURGED
1737 FROM OE_PURGE_SETS
1738 WHERE purge_set_id = p_purge_set_id;
1739
1740 Cursor c_purge_orders IS
1741 SELECT IS_PURGED
1742 FROM OE_PURGE_ORDERS
1743 WHERE PURGE_SET_ID = p_purge_set_id
1744 AND NVL(IS_PURGED,'N') = 'Y';
1745
1746 l_purge_set_purged VARCHAR2(1) := NULL;
1747 l_purge_set_name VARCHAR2(50) := NULL;
1748 l_is_purged VARCHAR2(1) := NULL;
1749
1750 BEGIN
1751
1752 oe_debug_pub.add('Entering OE_Order_Purge_PVT.Delete_Purge_Set : '||to_char(p_purge_set_id),1);
1753
1754 OPEN c_purge_set;
1755 FETCH c_purge_set into
1756 l_purge_set_name
1757 ,l_purge_set_purged;
1758
1759 IF nvl(l_purge_set_purged,'N') = 'Y' THEN
1760 x_return_status := FND_API.G_FALSE;
1761 CLOSE c_purge_set;
1762 RETURN;
1763 ELSE
1764 OPEN c_purge_orders;
1765 LOOP
1766
1767 FETCH c_purge_orders INTO l_is_purged;
1768 EXIT WHEN c_purge_orders%NOTFOUND OR
1769 c_purge_orders%NOTFOUND IS NULL;
1770
1771 x_return_status := FND_API.G_FALSE;
1772 CLOSE c_purge_set;
1773 CLOSE c_purge_orders;
1774 RETURN;
1775
1776 END LOOP;
1777
1778 DELETE FROM OE_PURGE_ORDERS
1779 WHERE purge_set_id = p_purge_set_id;
1780
1781 IF SQLCODE = 0 THEN
1782 DELETE FROM OE_PURGE_SETS
1783 WHERE purge_set_id = p_purge_set_id;
1784 END IF;
1785
1786 END IF;
1787
1788 CLOSE c_purge_set;
1789 CLOSE c_purge_orders;
1790
1791 x_return_status := FND_API.G_TRUE;
1792
1793 oe_debug_pub.add('Exiting OE_Order_Purge_PVT.Delete_Purge_Set : '||to_char(p_purge_set_id),1);
1794
1795 END Delete_Purge_Set;
1796
1797 --Added this function to check for open quotes
1798
1799 FUNCTION Check_Open_Quotes
1800 (
1801 p_header_id NUMBER
1802 )
1803 RETURN VARCHAR2
1804 IS
1805 CURSOR c_open_quotes IS
1806 SELECT 'Open Quotes'
1807 FROM OE_ORDER_HEADERS
1808 WHERE HEADER_ID = p_header_id
1809 AND NVL(OPEN_FLAG,'Y') = 'N';
1810
1811 l_open_quotes VARCHAR2(50);
1812 l_record_exists BOOLEAN;
1813
1814 BEGIN
1815
1816 OPEN c_open_quotes;
1817
1818 FETCH c_open_quotes INTO l_open_quotes;
1819 l_record_exists := c_open_quotes%FOUND;
1820
1821 CLOSE c_open_quotes;
1822
1823 IF (NOT l_record_exists) then
1824 RETURN FND_API.G_FALSE;
1825 END IF;
1826 RETURN FND_API.G_TRUE;
1827 END Check_Open_Quotes;
1828
1829
1830 FUNCTION Check_Open_Orders
1831 (
1832 --for Bug # 4516769
1833 p_header_id NUMBER
1834 )
1835 RETURN VARCHAR2
1836 IS
1837 CURSOR c_open_orders IS
1838 SELECT 'Open Orders'
1839 FROM OE_ORDER_HEADERS
1840 WHERE Header_id=p_header_id --for Bug # 4516769
1841 AND NVL(OPEN_FLAG,'Y') = 'N';
1842
1843 l_open_orders VARCHAR2(50);
1844 l_record_exists BOOLEAN;
1845
1846 BEGIN
1847
1848 OPEN c_open_orders;
1849
1850 FETCH c_open_orders INTO l_open_orders;
1851 l_record_exists := c_open_orders%FOUND;
1852
1853 CLOSE c_open_orders;
1854
1855 IF (NOT l_record_exists) then
1856 RETURN FND_API.G_FALSE;
1857 END IF;
1858
1859 RETURN FND_API.G_TRUE;
1860
1861 END Check_Open_Orders;
1862
1863 FUNCTION Check_Open_Invoiced_Orders
1864 (
1865 p_order_number IN VARCHAR2
1866 , p_order_type_name IN VARCHAR2 )
1867 RETURN VARCHAR2
1868 IS
1869
1870 CURSOR c_oe_ope_invoice IS
1871 SELECT 'Open invoices for this sales order'
1872 --FROM ra_customer_trx_lines rctl, --MOAC
1873 FROM ra_customer_trx_lines_all rctl, --MOAC
1874 RA_CUSTOMER_TRX rct
1875 WHERE rctl.interface_line_attribute1 = p_order_number
1876 AND rctl.interface_line_attribute2 = p_order_type_name
1877 --bug3389049 start
1878 AND rctl.interface_line_context = 'ORDER ENTRY'
1879 --bug3389049 end
1880 AND rctl.customer_trx_id = rct.customer_trx_id
1881 AND rct.complete_flag = 'N';
1882
1883 l_fetch_value VARCHAR2(80);
1884 l_records_exists BOOLEAN;
1885 BEGIN
1886
1887 OPEN c_oe_ope_invoice;
1888 FETCH c_oe_ope_invoice INTO l_fetch_value;
1889 l_records_exists := c_oe_ope_invoice%FOUND;
1890 CLOSE c_oe_ope_invoice;
1891
1892 IF (NOT l_records_exists) THEN
1893 RETURN FND_API.G_TRUE;
1894 END IF;
1895 RETURN FND_API.G_FALSE;
1896
1897 EXCEPTION
1898 WHEN OTHERS THEN
1899 RETURN FND_API.G_TRUE;
1900
1901 END Check_Open_Invoiced_Orders;
1902
1903 FUNCTION Check_Open_Returns
1904 (
1905 p_order_number IN NUMBER
1906 , p_order_type_name IN VARCHAR2 )
1907 RETURN VARCHAR2
1908 IS
1909
1910 CURSOR c_open_returns IS
1911 SELECT 'Open return for this sales order'
1912 /*MOAC*/
1913 --FROM oe_order_lines sl1,
1914 --oe_order_lines sl2,
1915 FROM oe_order_lines_all sl1,
1916 oe_order_lines_all sl2,
1917 oe_order_headers_all sh,
1918 oe_order_types_v ot --MOAC view based on multiple objects
1919 WHERE sh.order_number = p_order_number
1920 AND sh.order_type_id = ot.order_type_id
1921 AND ot.name = p_order_type_name
1922 AND sl1.header_id = sh.header_id
1923 AND sl2.reference_line_id = sl1.line_id
1924 AND sl2.line_category_code = 'RETURN'
1925 -- AND sl2.reference_type IN ( 'ORDER', 'PO' )
1926 AND sl2.return_context IN ( 'ORDER', 'PO' ) --for bug 2784219
1927 AND nvl(sl2.open_flag,'N') = 'Y';
1928
1929 l_fetch_value VARCHAR2(80);
1930 l_records_exists BOOLEAN;
1931
1932 BEGIN
1933
1934 OPEN c_open_returns;
1935
1936 FETCH c_open_returns INTO l_fetch_value;
1937 l_records_exists := c_open_returns%FOUND;
1938 CLOSE c_open_returns;
1939
1940 IF (NOT l_records_exists) THEN
1941 RETURN FND_API.G_TRUE;
1942 END IF;
1943
1944 RETURN FND_API.G_FALSE;
1945
1946 EXCEPTION
1947 WHEN OTHERS THEN
1948 RETURN FND_API.G_FALSE;
1949 END Check_Open_Returns;
1950
1951 PROCEDURE Check_Open_RMA_Receipts
1952 ( p_header_id IN NUMBER,
1953 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
1954 x_message OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
1955 IS
1956 CURSOR c_return_lines IS
1957 SELECT line_id
1958 FROM oe_order_lines
1959 WHERE header_id = p_header_id
1960 AND line_category_code = 'RETURN';
1961 l_line_id NUMBER;
1962 l_return_status varchar2(10);
1963 BEGIN
1964
1965 OPEN c_return_lines;
1966 LOOP
1967 FETCH c_return_lines INTO l_line_id;
1968 EXIT When c_return_lines%NOTFOUND;
1969
1970 RCV_RMA_RCPT_PURGE.Check_Open_Receipts(l_line_id,
1971 l_return_status,x_message);
1972
1973 IF l_return_status = 'FALSE' THEN
1974 CLOSE c_return_lines;
1975 x_return_status := FND_API.G_FALSE;
1976 RETURN;
1977 END IF;
1978 END LOOP;
1979
1980 CLOSE c_return_lines;
1981 x_return_status := FND_API.G_TRUE;
1982
1983 EXCEPTION
1984 WHEN OTHERS THEN
1985 x_return_status := FND_API.G_FALSE;
1986
1987 END Check_Open_RMA_Receipts;
1988
1989 -- Purge Changes for 11i.10
1990
1991 /*--------------------------------------------------------------------
1992 Function : Check_Open_PO_Reqs_Dropship
1993 Description : This function checks if there are any open
1994 PO/Requsitions associated with drop ship order lines.
1995 It will call an API provided by Purchasing. If this API
1996 returns that the PO/Requsition associated with any of
1997 the drop ship order line is open, the order will be marked
1998 for not to be purged, with message OE_PURGE_OPEN_PO_REQ.
1999 ----------------------------------------------------------------------*/
2000 Function Check_Open_PO_Reqs_Dropship
2001 ( p_header_id IN NUMBER
2002 )
2003 RETURN VARCHAR2
2004 IS
2005 CURSOR c_ds_line_loc IS
2006 SELECT ds.line_location_id line_location_id
2007 FROM oe_drop_ship_sources ds
2008 WHERE ds.header_id = p_header_id;
2009
2010 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2011 l_return_status VARCHAR2(1);
2012 l_msg_count NUMBER;
2013 l_msg_data VARCHAR2(1000);
2014 I NUMBER := 1;
2015 l_entity_id_tbl PO_TBL_NUMBER := PO_TBL_NUMBER();
2016 l_purge_allowed_tbl PO_TBL_VARCHAR1 := PO_TBL_VARCHAR1();
2017
2018 BEGIN
2019
2020 IF l_debug_level > 0 THEN
2021 OE_DEBUG_PUB.Add('Entering Check_Open_PO_Reqs_Dropship...',1);
2022 END IF;
2023
2024 FOR c_loc in c_ds_line_loc LOOP
2025
2026 IF c_loc.line_location_id is NOT NULL THEN
2027
2028 l_entity_id_tbl.extend(I);
2029 l_entity_id_tbl(I) := c_loc.line_location_id;
2030 I := I + 1;
2031
2032 END IF;
2033
2034 END LOOP;
2035
2036
2037 IF I > 1 THEN
2038
2039 IF l_debug_level > 0 THEN
2040 OE_DEBUG_PUB.Add('Before Calling PO Validate_Purge...',1);
2041 END IF;
2042
2043 PO_OM_INTEGRATION_GRP.Validate_Purge
2044 ( p_api_version => 1.0
2045 ,p_init_msg_list => FND_API.G_FALSE
2046 ,p_commit => FND_API.G_FALSE
2047 ,p_entity => 'PO_LINE_LOCATIONS'
2048 ,p_entity_id_tbl => l_entity_id_tbl
2049 ,x_return_status => l_return_status
2050 ,x_msg_count => l_msg_count
2051 ,x_msg_data => l_msg_data
2052 ,x_purge_allowed_tbl => l_purge_allowed_tbl
2053 );
2054
2055 IF l_debug_level > 0 THEN
2056 OE_DEBUG_PUB.Add('After Calling PO Validate_Purge...'||l_return_status,1);
2057 END IF;
2058
2059 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2060
2061 FOR J in 1..l_purge_allowed_tbl.COUNT LOOP
2062
2063 IF l_purge_allowed_tbl(J) = 'N' THEN
2064
2065
2066 IF l_debug_level > 0 THEN
2067 OE_DEBUG_PUB.Add('Purge Not Alowed for Loc Id: '||
2068 l_entity_id_tbl(J),2) ;
2069 END IF;
2070
2071 -- Return False if record Exists
2072
2073 RETURN FND_API.G_FALSE;
2074
2075 END IF;
2076
2077 END LOOP;
2078 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2079 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2080 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2081
2082 OE_MSG_PUB.Add_Text(l_msg_data);
2083
2084 IF l_debug_level > 0 THEN
2085 OE_DEBUG_PUB.Add('Errors from Validate Purge: '||l_msg_data,2) ;
2086 END IF;
2087
2088 RAISE FND_API.G_EXC_ERROR;
2089 END IF;
2090
2091
2092 END IF;
2093
2094
2095 IF l_debug_level > 0 THEN
2096 OE_DEBUG_PUB.Add('Exiting Check_Open_PO_Reqs_Dropship...',1);
2097 END IF;
2098
2099 -- Return True if record Exists
2100
2101 RETURN FND_API.G_TRUE;
2102
2103 EXCEPTION
2104 WHEN FND_API.G_EXC_ERROR THEN
2105 IF l_debug_level > 0 THEN
2106 OE_DEBUG_PUB.Add('Exp Error in Check_Open_PO_Reqs_Dropship...',4);
2107 END IF;
2108 RAISE;
2109
2110 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2111 IF l_debug_level > 0 THEN
2112 OE_DEBUG_PUB.Add('UnExp Error in Check_Open_PO_Reqs_Dropship...'
2113 ||sqlerrm,4);
2114 END IF;
2115 RAISE;
2116
2117 WHEN OTHERS THEN
2118 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2119 THEN
2120 OE_MSG_PUB.Add_Exc_Msg
2121 ( 'OE_ORDER_PURGE_PVT'
2122 ,'Check_Open_PO_Reqs_Dropship'
2123 );
2124 END IF;
2125 RAISE;
2126
2127 END Check_Open_PO_Reqs_Dropship;
2128
2129
2130 PROCEDURE Oe_Purge_Headers
2131 (
2132 p_purge_set_id IN NUMBER
2133 , p_header_id IN NUMBER
2134 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
2135 IS
2136
2137 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
2138 l_error_msg VARCHAR2(240);
2139 CURSOR c_lock_header IS
2140 SELECT header_id -- Lock all rows to be purged
2141 FROM oe_order_headers
2142 WHERE header_id = p_header_id
2143 FOR UPDATE NOWAIT;
2144
2145 CURSOR c_lock_header_hist IS
2146 SELECT header_id -- Lock all rows to be purged
2147 FROM oe_order_header_history
2148 WHERE header_id = p_header_id
2149 FOR UPDATE NOWAIT;
2150
2151 CURSOR c_lock_price_adj_hist IS
2152 SELECT header_id -- Lock all rows to be purged
2153 FROM oe_price_adjs_history
2154 WHERE header_id = p_header_id
2155 FOR UPDATE NOWAIT;
2156
2157 CURSOR c_lock_sales_credit_hist IS
2158 SELECT header_id -- Lock all rows to be purged
2159 FROM oe_sales_credit_history
2160 WHERE header_id = p_header_id
2161 FOR UPDATE NOWAIT;
2162
2163 --16193599 Audit History Purge Start
2164 CURSOR c_lock_audit_hist_ent1 IS
2165 SELECT entity_id -- Lock all rows to be purged
2166 FROM OE_AUDIT_ATTR_HISTORY
2167 WHERE (entity_id,entity_number) IN (SELECT 1,header_id FROM oe_order_headers_all WHERE header_id=p_header_id)
2168 FOR UPDATE NOWAIT;
2169
2170 CURSOR c_lock_audit_hist_ent2 IS
2171 SELECT entity_id -- Lock all rows to be purged
2172 FROM OE_AUDIT_ATTR_HISTORY
2173 WHERE (entity_id,entity_number) IN (SELECT 2,line_id FROM oe_order_lines_all WHERE header_id=p_header_id)
2174 FOR UPDATE NOWAIT;
2175
2176 CURSOR c_lock_audit_hist_ent6_8 IS
2177 SELECT entity_id -- Lock all rows to be purged
2178 FROM OE_AUDIT_ATTR_HISTORY
2179 WHERE (entity_id,entity_number) IN (SELECT DECODE(line_id,null,6,8),price_adjustment_id FROM oe_price_adjustments WHERE header_id=p_header_id )
2180 FOR UPDATE NOWAIT;
2181
2182 CURSOR c_lock_audit_hist_ent5_7 IS
2183 SELECT entity_id -- Lock all rows to be purged
2184 FROM OE_AUDIT_ATTR_HISTORY
2185 WHERE (entity_id,entity_number) IN ( SELECT DECODE(line_id,null,5,7),sales_credit_id FROM oe_sales_credits WHERE header_id=p_header_id)
2186 FOR UPDATE NOWAIT;
2187 --16193599 Audit History Purge End
2188
2189 cursor c_purge_set_history is --bug#5631508
2190 select set_id from oe_sets_history
2191 where header_id= p_header_id
2192 FOR UPDATE NOWAIT;
2193
2194 l_doc_tbl OE_CONTRACTS_UTIL.doc_tbl_type;
2195 l_doc_rec OKC_TERMS_UTIL_GRP.doc_rec_type;
2196 l_msg_count NUMBER;
2197 l_msg_data VARCHAR2(2000);
2198
2199 BEGIN
2200
2201 oe_debug_pub.add('Entering OE_ORDER_PURGE_PVT.OE_Purge_Headers : '||to_char(p_header_id));
2202
2203 --16193599 Audit History Purge Start
2204 OPEN c_lock_audit_hist_ent1;
2205 CLOSE c_lock_audit_hist_ent1;
2206 DELETE
2207 FROM OE_AUDIT_ATTR_HISTORY
2208 WHERE (entity_id,entity_number) IN (SELECT 1,header_id FROM oe_order_headers_all WHERE header_id=p_header_id);
2209
2210 OPEN c_lock_audit_hist_ent2;
2211 CLOSE c_lock_audit_hist_ent2;
2212 DELETE
2213 FROM OE_AUDIT_ATTR_HISTORY
2214 WHERE (entity_id,entity_number) IN (SELECT 2,line_id FROM oe_order_lines_all WHERE header_id=p_header_id);
2215
2216
2217 OPEN c_lock_audit_hist_ent6_8;
2218 CLOSE c_lock_audit_hist_ent6_8;
2219 DELETE
2220 FROM OE_AUDIT_ATTR_HISTORY
2221 WHERE (entity_id,entity_number) IN (SELECT DECODE(line_id,null,6,8),price_adjustment_id FROM oe_price_adjustments WHERE header_id=p_header_id );
2222
2223
2224 OPEN c_lock_audit_hist_ent5_7;
2225 CLOSE c_lock_audit_hist_ent5_7;
2226 DELETE
2227 FROM OE_AUDIT_ATTR_HISTORY
2228 WHERE (entity_id,entity_number) IN ( SELECT DECODE(line_id,null,5,7),sales_credit_id FROM oe_sales_credits WHERE header_id=p_header_id);
2229 --16193599 Audit History Purge End
2230
2231 OPEN c_lock_header; -- Lock all rows to be purged
2232
2233 CLOSE c_lock_header;
2234
2235 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2236 l_return_status := OE_Order_Purge_PVT.OE_Purge_Header_Adj
2237 (
2238 p_purge_set_id => p_purge_set_id,
2239 p_header_id => p_header_id
2240 );
2241
2242 ELSE
2243 x_return_status := l_return_status;
2244 RETURN;
2245 END IF;
2246
2247 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2248 l_return_status := OE_Order_Purge_PVT.OE_Purge_Price_Attribs
2249 (
2250 p_purge_set_id => p_purge_set_id,
2251 p_header_id => p_header_id
2252 );
2253
2254 ELSE
2255 x_return_status := l_return_status;
2256 RETURN;
2257 END IF;
2258
2259 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2260 l_return_status := OE_Order_Purge_PVT.OE_Purge_Order_Sales_Credits
2261 (
2262 p_purge_set_id => p_purge_set_id,
2263 p_header_id => p_header_id
2264 );
2265
2266 ELSE
2267 x_return_status := l_return_status;
2268 RETURN;
2269 END IF;
2270
2271 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2272 l_return_status := OE_Order_Purge_PVT.OE_Purge_Order_Sets
2273 (
2274 p_purge_set_id => p_purge_set_id,
2275 p_header_id => p_header_id
2276 );
2277
2278 ELSE
2279 x_return_status := l_return_status;
2280 RETURN;
2281 END IF;
2282
2283 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2284 l_return_status := OE_Order_Purge_PVT.OE_Purge_Order_Holds
2285 (
2286 p_purge_set_id => p_purge_set_id,
2287 p_header_id => p_header_id
2288 );
2289
2290 ELSE
2291 x_return_status := l_return_status;
2292 RETURN;
2293 END IF;
2294
2295 -- purge for multiple payments
2296 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2297 l_return_status := OE_Order_Purge_PVT.OE_Purge_Header_payments
2298 ( p_purge_set_id => p_purge_set_id,
2299 p_header_id => p_header_id
2300 );
2301 ELSE
2302 x_return_status := l_return_status;
2303 RETURN;
2304 END IF;
2305
2306 -- Delete the attachments.
2307
2308 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2309 OE_Atchmt_Util.Delete_Attachments
2310 (
2311 p_entity_code => OE_GLOBALS.G_ENTITY_HEADER,
2312 p_entity_id => p_header_id,
2313 x_return_status => l_return_status
2314 );
2315 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2316 oe_debug_pub.add('Attachments delete failed : ');
2317 x_return_status := l_return_status;
2318 RETURN;
2319 END IF;
2320
2321 ELSE
2322 x_return_status := l_return_status;
2323 RETURN;
2324 END IF;
2325
2326 -- Delete record from CTO tables
2327
2328 oe_debug_pub.add('Calling CTOs API ',3);
2329
2330 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2331 CTO_ORDER_PURGE.Cto_Purge_Tables
2332 (
2333 p_header_id => p_header_id,
2334 x_error_msg => l_error_msg,
2335 x_return_status => l_return_status
2336 );
2337
2338 oe_debug_pub.add('Return from CTOs API : '||l_return_status,3);
2339
2340 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2341
2342 x_return_status := l_return_status;
2343 ROLLBACK TO SAVEPOINT ORDER_HEADER;
2344 record_errors( l_return_status
2345 ,p_purge_set_id
2346 ,p_header_id
2347 ,'ORDPUR: CTO Package Errored '||l_error_msg);
2348 RETURN;
2349
2350 END IF;
2351
2352 ELSE
2353 x_return_status := l_return_status;
2354 RETURN;
2355 END IF;
2356
2357 -- Delete the header work flow.
2358
2359 BEGIN
2360
2361 OE_Order_WF_Util.Delete_Row
2362 (
2363 p_type => 'HEADER',
2364 p_id => p_header_id
2365 );
2366
2367 EXCEPTION
2368 WHEN OTHERS THEN
2369 NULL;
2370
2371 END;
2372
2373 -- Purge Changes for 11i.10
2374 -- Purge the History tables
2375
2376 OPEN c_lock_header_hist;
2377 CLOSE c_lock_header_hist;
2378 DELETE FROM oe_order_header_history WHERE header_id = p_header_id;
2379
2380 OE_DEBUG_PUB.Add('After Deleting header history='|| to_char(p_header_id));
2381
2382 OPEN c_lock_price_adj_hist;
2383 CLOSE c_lock_price_adj_hist;
2384 DELETE FROM oe_price_adjs_history WHERE header_id = p_header_id;
2385
2386 OE_DEBUG_PUB.Add('After Deleting Price Adj history='|| to_char(p_header_id));
2387
2388 OPEN c_lock_sales_credit_hist;
2389 CLOSE c_lock_sales_credit_hist;
2390 DELETE FROM oe_sales_credit_history WHERE header_id = p_header_id;
2391
2392 OE_DEBUG_PUB.Add('After Deleting Sales Credit history='|| to_char(p_header_id));
2393
2394 OPEN c_purge_set_history; --bug#5631508
2395 CLOSE c_purge_set_history;
2396 DELETE FROM oe_sets_history WHERE header_id = p_header_id;
2397
2398 -- Purging Contract Articles
2399
2400 IF OE_CODE_CONTROL.Code_Release_Level >= '110510' THEN
2401
2402 IF OE_CONTRACTS_UTIL.Check_License = 'Y' THEN
2403
2404 l_doc_rec.doc_type := 'O';
2405 l_doc_rec.doc_id := p_header_id;
2406 l_doc_tbl(1) := l_doc_rec;
2407
2408 OE_CONTRACTS_UTIL.Purge_articles
2409 ( p_api_version => 1.0
2410 , p_doc_tbl => l_doc_tbl
2411 , x_return_status => x_return_status
2412 , x_msg_count => l_msg_count
2413 , x_msg_data => l_msg_data
2414 );
2415
2416 OE_DEBUG_PUB.Add('Purged the Articles for Header:'||to_char(p_header_id));
2417
2418 END IF;
2419
2420 END IF;
2421 DELETE FROM oe_order_headers
2422 WHERE header_id = p_header_id;
2423 oe_debug_pub.add('deleted header='|| to_char(p_header_id));
2424
2425 oe_debug_pub.add('Exiting OE_ORDER_PURGE_PVT.OE_Purge_Headers : ');
2426
2427 x_return_status := FND_API.G_RET_STS_SUCCESS;
2428
2429 EXCEPTION -- Exception handler to record error
2430
2431 WHEN OTHERS THEN
2432
2433 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2434 ROLLBACK TO SAVEPOINT ORDER_HEADER;
2435 record_errors( l_return_status
2436 ,p_purge_set_id
2437 ,p_header_id
2438 ,'ORDPUR: OE_ORDER_HEADERS '||substr(sqlerrm,1,200)
2439 );
2440 CLOSE c_lock_header;
2441
2442 END Oe_Purge_Headers;
2443
2444 PROCEDURE Oe_Purge_Lines
2445 (
2446 p_purge_set_id IN NUMBER
2447 , p_header_id IN NUMBER
2448 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
2449
2450 IS
2451
2452 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
2453 l_line_id NUMBER;
2454 l_lock_line_id NUMBER;
2455 l_order_quantity_uom VARCHAR2(3);
2456 l_is_ota_line BOOLEAN;
2457 l_org_id NUMBER;
2458 l_line_category_code VARCHAR2(30);
2459 l_source_type_code VARCHAR2(30);
2460 I NUMBER := 1;
2461 l_entity_id_tbl PO_TBL_NUMBER := PO_TBL_NUMBER();
2462 l_msg_count NUMBER;
2463 l_msg_data VARCHAR2(2000);
2464 l_top_model_line_id NUMBER; --added for bug 3664878
2465 l_config_header_id NUMBER; --added for bug 3664878
2466 l_config_rev_nbr NUMBER; --added for bug 3664878
2467
2468 --cursor definition of c_purge_lines modified for bug 3664878
2469 CURSOR c_purge_lines IS
2470 SELECT line_id,order_quantity_uom,
2471 org_id,line_category_code,source_type_code,
2472 top_model_line_id,config_header_id,
2473 config_rev_nbr
2474 FROM oe_order_lines
2475 WHERE header_id = p_header_id;
2476
2477 CURSOR c_purge_lines_hist IS
2478 SELECT line_id
2479 FROM oe_order_lines_history
2480 WHERE header_id = p_header_id
2481 FOR UPDATE NOWAIT;
2482
2483 CURSOR c_purge_ds IS
2484 SELECT line_id
2485 FROM oe_drop_ship_sources
2486 WHERE header_id = p_header_id
2487 FOR UPDATE NOWAIT;
2488
2489 CURSOR c_ds_line_loc IS
2490 SELECT ds.line_location_id
2491 FROM oe_drop_ship_sources ds
2492 WHERE ds.header_id = p_header_id;
2493
2494 BEGIN
2495
2496 oe_debug_pub.add('Entering OE_ORDER_PURGE_PVT.OE_Purge_Lines : '||to_char(p_header_id));
2497
2498
2499 OPEN c_purge_lines;
2500 LOOP
2501 FETCH c_purge_lines INTO l_line_id, l_order_quantity_uom, l_org_id
2502 , l_line_category_code,l_source_type_code,l_top_model_line_id
2503 , l_config_header_id, l_config_rev_nbr;
2504 EXIT WHEN c_purge_lines%NOTFOUND -- end of fetch
2505 OR c_purge_lines%NOTFOUND IS NULL; -- empty cursor
2506
2507 SELECT line_id
2508 INTO l_lock_line_id
2509 FROM oe_order_lines
2510 WHERE line_id = l_line_id
2511 FOR UPDATE NOWAIT;
2512
2513 --IF condition added for bug 3664878
2514 --to delete data from CZ tables in case of configurations
2515 IF l_line_id = l_top_model_line_id AND
2516 l_config_header_id is not null THEN
2517
2518 OE_Config_Pvt.Delete_Config
2519 ( p_config_hdr_id => l_config_header_id,
2520 p_config_rev_nbr => l_config_rev_nbr,
2521 x_return_status => l_return_status );
2522
2523 OE_DEBUG_PUB.Add('After Calling Delete_Config',1);
2524 END IF;
2525
2526
2527 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2528 l_return_status := OE_Order_Purge_PVT.OE_Purge_Line_Adj
2529 (
2530 p_purge_set_id => p_purge_set_id,
2531 p_header_id => p_header_id,
2532 p_line_id => l_line_id
2533 );
2534
2535 ELSE
2536 x_return_status := l_return_status;
2537 RETURN;
2538 END IF;
2539
2540 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2541 l_return_status := OE_Order_Purge_PVT.OE_Purge_Line_Sales_Credits
2542 (
2543 p_purge_set_id => p_purge_set_id,
2544 p_header_id => p_header_id,
2545 p_line_id => l_line_id
2546 );
2547
2548 ELSE
2549 x_return_status := l_return_status;
2550 RETURN;
2551 END IF;
2552
2553 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2554 l_return_status := OE_Order_Purge_PVT.OE_Purge_Line_Sets
2555 (
2556 p_purge_set_id => p_purge_set_id,
2557 p_header_id => p_header_id,
2558 p_line_id => l_line_id
2559 );
2560
2561 ELSE
2562 x_return_status := l_return_status;
2563 RETURN;
2564 END IF;
2565
2566 -- purge for multiple payments.
2567 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2568 l_return_status := OE_Order_Purge_PVT.OE_Purge_Line_payments
2569 ( p_purge_set_id => p_purge_set_id,
2570 p_header_id => p_header_id,
2571 p_line_id => l_line_id
2572 );
2573 ELSE
2574 x_return_status := l_return_status;
2575 RETURN;
2576 END IF;
2577
2578
2579 oe_debug_pub.add('Before RMA : ',1);
2580
2581 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2582 IF l_line_category_code = 'RETURN' THEN
2583
2584 l_return_status := OE_Purge_RMA_Line_Receipts
2585 ( p_purge_set_id => p_purge_set_id,
2586 p_header_id => p_header_id,
2587 p_line_id => l_line_id
2588 );
2589
2590 END IF;
2591
2592 ELSE
2593 x_return_status := l_return_status;
2594 RETURN;
2595 END IF;
2596
2597 oe_debug_pub.add('Before RMA_LOT : ',1);
2598
2599 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2600 IF l_line_category_code = 'RETURN' THEN
2601
2602 l_return_status := OE_Purge_RMA_Line_Lot_Srl
2603 ( p_purge_set_id => p_purge_set_id,
2604 p_header_id => p_header_id,
2605 p_line_id => l_line_id
2606 );
2607
2608 END IF;
2609 ELSE
2610 x_return_status := l_return_status;
2611 RETURN;
2612 END IF;
2613
2614
2615 -- Delete the attachments.
2616
2617 oe_debug_pub.add('Before before attach : ',1);
2618
2619 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2620 OE_Atchmt_Util.Delete_Attachments
2621 (
2622 p_entity_code => OE_GLOBALS.G_ENTITY_LINE,
2623 p_entity_id => l_line_id,
2624 x_return_status => l_return_status
2625 );
2626 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2627 oe_debug_pub.add('Attachments delete failed : ');
2628 x_return_status := l_return_status;
2629 RETURN;
2630 END IF;
2631
2632 ELSE
2633 x_return_status := l_return_status;
2634 RETURN;
2635 END IF;
2636
2637 -- Delete the Line work flow.
2638 oe_debug_pub.add('Before workflow : ',1);
2639 BEGIN
2640
2641
2642 OE_Order_WF_Util.Delete_Row
2643 (
2644 p_type => 'LINE',
2645 p_id => l_line_id
2646 );
2647
2648 EXCEPTION
2649 WHEN OTHERS THEN
2650 NULL;
2651
2652 END;
2653
2654 -- Purge the OTA lines. Currently, OTA lines are identified by UOM.
2655
2656 oe_debug_pub.add('Before ota : ',1);
2657 l_is_ota_line := OE_OTA_UTIL.Is_OTA_Line(l_order_quantity_uom);
2658 IF (l_is_ota_line) THEN
2659 OE_OTA_UTIL.Notify_OTA
2660 (p_line_id => l_line_id,
2661 p_org_id => l_org_id,
2662 p_order_quantity_uom => l_order_quantity_uom,
2663 p_daemon_type => 'P',
2664 x_return_status => l_return_status);
2665
2666 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2667 null;
2668 end if;
2669 End IF;
2670
2671 -- Purge the History tables
2672
2673 OPEN c_purge_lines_hist;
2674 CLOSE c_purge_lines_hist;
2675 DELETE FROM oe_order_lines_history WHERE header_id = p_header_id;
2676
2677 OE_DEBUG_PUB.Add('Before line delete : ',1);
2678
2679 DELETE FROM oe_order_lines
2680 WHERE line_id = l_line_id;
2681
2682 END LOOP;
2683
2684 -- Purge Changes for 11i.10
2685 -- Purge PO/Req
2686
2687 IF PO_CODE_RELEASE_GRP.Current_Release >=
2688 PO_CODE_RELEASE_GRP.PRC_11i_Family_Pack_J AND
2689 OE_CODE_CONTROL.Code_Release_Level >= '110510' THEN
2690
2691 OE_DEBUG_PUB.Add('Purge Externally Sourced Lines');
2692
2693 FOR c_ds_loc in c_ds_line_loc LOOP
2694
2695 IF c_ds_loc.line_location_id IS NOT NULL THEN
2696 OE_DEBUG_PUB.Add('Purge Line Loc : '||c_ds_loc.line_location_id);
2697 l_entity_id_tbl.extend(1);
2698 l_entity_id_tbl(I) := c_ds_loc.line_location_id;
2699 I := I + 1;
2700 END IF;
2701 END LOOP;
2702
2703 OPEN c_purge_ds;
2704 CLOSE c_purge_ds;
2705
2706 DELETE FROM oe_drop_ship_sources where header_id = p_header_id;
2707
2708 OE_DEBUG_PUB.Add('Before Calling PO Purge API ');
2709
2710 PO_OM_INTEGRATION_GRP.Purge
2711 ( p_api_version => 1.0
2712 ,p_init_msg_list => FND_API.G_FALSE
2713 ,p_commit => FND_API.G_FALSE
2714 ,x_return_status => x_return_status
2715 ,x_msg_count => l_msg_count
2716 ,x_msg_data => l_msg_data
2717 ,p_entity => 'PO_LINE_LOCATIONS'
2718 ,p_entity_id_tbl => l_entity_id_tbl
2719 );
2720
2721 OE_DEBUG_PUB.Add('After Calling PO Purge API '||x_return_status);
2722
2723 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2724 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2725 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
2726 OE_MSG_PUB.Add_Text(l_msg_data);
2727 OE_DEBUG_PUB.Add('Errors from Purge: '||l_msg_data,2) ;
2728 RAISE FND_API.G_EXC_ERROR;
2729 END IF;
2730 END IF;
2731
2732
2733 oe_debug_pub.add('Number of lines deleted : '||to_char(c_purge_lines%ROWCOUNT),1);
2734 CLOSE c_purge_lines;
2735
2736 oe_debug_pub.add('deleted lines for header='|| to_char(p_header_id));
2737 oe_debug_pub.add('Exiting OE_ORDER_PURGE_PVT.OE_Purge_Lines : ');
2738
2739 x_return_status := FND_API.G_RET_STS_SUCCESS;
2740
2741 EXCEPTION -- Exception handler to record error
2742
2743 WHEN OTHERS THEN
2744 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2745 ROLLBACK TO SAVEPOINT ORDER_HEADER;
2746 record_errors( l_return_status
2747 ,p_purge_set_id
2748 ,p_header_id
2749 ,'ORDPUR: OE_ORDER_LINES '||substr(sqlerrm,1,200)
2750 );
2751 CLOSE c_purge_lines;
2752
2753 END Oe_Purge_Lines;
2754
2755 FUNCTION OE_Purge_Header_Adj
2756 (
2757 p_purge_set_id IN NUMBER
2758 , p_header_id IN NUMBER)
2759
2760 RETURN VARCHAR2
2761 IS
2762 CURSOR c_header_adj IS
2763 SELECT PRICE_ADJUSTMENT_ID
2764 FROM OE_PRICE_ADJUSTMENTS
2765 WHERE HEADER_ID = p_header_id;
2766
2767 CURSOR c_price_adj(p_price_adjustment_id NUMBER) IS
2768 SELECT price_adj_attrib_id
2769 FROM oe_price_adj_attribs
2770 WHERE price_adjustment_id = p_price_adjustment_id;
2771
2772 CURSOR c_price_adj_assocs(p_price_adjustment_id NUMBER) IS
2773 SELECT price_adj_assoc_id
2774 FROM OE_PRICE_ADJ_ASSOCS OPAA
2775 WHERE opaa.rltd_price_adj_id IN (SELECT TO_NUMBER (p_price_adjustment_id)
2776 FROM DUAL
2777 UNION ALL
2778 SELECT opaa1.rltd_price_adj_id
2779 FROM oe_price_adj_assocs opaa1
2780 WHERE opaa1.price_adjustment_id = p_price_adjustment_id);
2781 /*
2782 WHERE (OPAA.price_adjustment_id = p_price_adjustment_id or
2783 OPAA.rltd_price_adj_id = p_price_adjustment_id) or
2784 OPAA.rltd_price_adj_id in( select opaa1.rltd_price_adj_id from
2785 oe_price_adj_assocs opaa1 where
2786 opaa1.price_adjustment_id = p_price_adjustment_id);
2787 ========= Commented for the bug 3053445 =========
2788 */
2789 l_price_adjustment_id NUMBER;
2790 l_lock_adjustment_id NUMBER;
2791 l_price_adj_assoc_id NUMBER;
2792 l_lock_price_adj_assoc_id NUMBER;
2793 l_price_adj_attrib_id NUMBER;
2794 l_lock_price_adj_attrib_id NUMBER;
2795 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
2796
2797 BEGIN
2798
2799 oe_debug_pub.add('Entering OE_ORDER_PURGE_PVT.OE_Purge_Header_Adj : '||to_char(p_header_id));
2800
2801 OPEN c_header_adj;
2802
2803 LOOP
2804
2805 FETCH c_header_adj INTO l_price_adjustment_id;
2806 oe_debug_pub.add('price adjust ment : '||to_char(l_price_adjustment_id));
2807 EXIT WHEN c_header_adj%NOTFOUND -- end of fetch
2808 OR c_header_adj%NOTFOUND IS NULL; -- empty cursor
2809
2810 -- Lock the price adjustments record.
2811 SELECT price_adjustment_id
2812 INTO l_lock_adjustment_id
2813 FROM oe_price_adjustments
2814 WHERE price_adjustment_id = l_price_adjustment_id
2815 FOR UPDATE NOWAIT;
2816
2817 -- Delete from price_adj_attribs.
2818 OPEN c_price_adj(l_price_adjustment_id);
2819 LOOP
2820 FETCH c_price_adj INTO l_price_adj_attrib_id;
2821 EXIT WHEN c_price_adj%NOTFOUND -- end of fetch
2822 OR c_price_adj%NOTFOUND IS NULL; -- empty cursor
2823
2824 SELECT price_adj_attrib_id
2825 INTO l_lock_price_adj_attrib_id
2826 FROM oe_price_adj_attribs
2827 WHERE price_adj_attrib_id = l_price_adj_attrib_id
2828 FOR UPDATE NOWAIT;
2829
2830 DELETE FROM oe_price_adj_attribs
2831 WHERE price_adj_attrib_id = l_price_adj_attrib_id;
2832
2833 END LOOP;
2834
2835 oe_debug_pub.add('Number of price_adj deleted : '||to_char(c_price_adj%ROWCOUNT),1);
2836 CLOSE c_price_adj;
2837
2838 -- Delete from price_adj_assocs.
2839 OPEN c_price_adj_assocs(l_price_adjustment_id);
2840 LOOP
2841 FETCH c_price_adj_assocs INTO l_price_adj_assoc_id;
2842 EXIT WHEN c_price_adj_assocs%NOTFOUND -- end of fetch
2843 OR c_price_adj_assocs%NOTFOUND IS NULL; -- empty cursor
2844 oe_debug_pub.add('price adjust ment 4 : '||to_char(l_price_adjustment_id));
2845
2846 SELECT price_adj_assoc_id
2847 INTO l_lock_price_adj_assoc_id
2848 FROM oe_price_adj_assocs
2849 WHERE price_adj_assoc_id = l_price_adj_assoc_id
2850 FOR UPDATE NOWAIT;
2851
2852 DELETE FROM oe_price_adj_assocs
2853 WHERE price_adj_assoc_id = l_lock_price_adj_assoc_id;
2854
2855 END LOOP;
2856
2857 oe_debug_pub.add('Number of price_adj_assocs deleted : '||to_char(c_price_adj_assocs%ROWCOUNT),1);
2858 CLOSE c_price_adj_assocs;
2859
2860
2861 DELETE FROM OE_PRICE_ADJUSTMENTS
2862 WHERE price_adjustment_id = l_lock_adjustment_id;
2863
2864 END LOOP;
2865
2866 oe_debug_pub.add('Number of price adjustments deleted : '||to_char(c_header_adj%ROWCOUNT),1);
2867 CLOSE c_header_adj;
2868
2869 oe_debug_pub.add('Exiting OE_ORDER_PURGE_PVT.OE_Purge_Header_Adj : ');
2870
2871 RETURN FND_API.G_RET_STS_SUCCESS;
2872
2873 EXCEPTION -- Exception handler to record error
2874
2875 WHEN OTHERS THEN
2876 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2877 ROLLBACK TO SAVEPOINT ORDER_HEADER;
2878 record_errors( l_return_status
2879 ,p_purge_set_id
2880 ,p_header_id
2881 ,'ORDPUR: Header Price Adjustments '||substr(sqlerrm,1,200)
2882 );
2883 CLOSE c_header_adj;
2884 CLOSE c_price_adj_assocs;
2885 RETURN l_return_status;
2886
2887 END OE_Purge_Header_Adj;
2888
2889 FUNCTION OE_Purge_Line_Adj
2890 (
2891 p_purge_set_id IN NUMBER
2892 , p_header_id IN NUMBER
2893 , p_line_id IN NUMBER)
2894
2895 RETURN VARCHAR2
2896 IS
2897 CURSOR c_line_adj IS
2898 SELECT PRICE_ADJUSTMENT_ID
2899 FROM OE_PRICE_ADJUSTMENTS
2900 WHERE LINE_ID = p_line_id;
2901
2902 -- Added cursor c_price_adj for bug # 4701261
2903
2904 CURSOR c_price_adj(p_price_adjustment_id NUMBER) IS
2905 SELECT price_adj_attrib_id
2906 FROM oe_price_adj_attribs
2907 WHERE price_adjustment_id = p_price_adjustment_id;
2908
2909 -- Modified for the SQLREP changes for SQL_ID = 14882948
2910 CURSOR c_price_adj_assocs(p_price_adjustment_id NUMBER) IS
2911 SELECT price_adj_assoc_id
2912 FROM OE_PRICE_ADJ_ASSOCS OPAA
2913 WHERE opaa.rltd_price_adj_id IN (
2914 SELECT p_price_adjustment_id
2915 FROM DUAL
2916 UNION ALL
2917 SELECT opaa1.rltd_price_adj_id
2918 FROM oe_price_adj_assocs opaa1
2919 WHERE opaa1.price_adjustment_id = p_price_adjustment_id);
2920
2921
2922 l_price_adjustment_id NUMBER;
2923 l_lock_adjustment_id NUMBER;
2924 l_price_adj_assoc_id NUMBER;
2925 l_lock_price_adj_assoc_id NUMBER;
2926 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
2927 l_price_adj_attrib_id NUMBER;
2928 l_lock_price_adj_attrib_id NUMBER;
2929
2930
2931 BEGIN
2932
2933 oe_debug_pub.add('Entering OE_ORDER_PURGE_PVT.OE_Purge_Line_Adj : '||to_char(p_line_id));
2934 OPEN c_line_adj;
2935
2936 LOOP
2937
2938 FETCH c_line_adj INTO l_price_adjustment_id;
2939 EXIT WHEN c_line_adj%NOTFOUND -- end of fetch
2940 OR c_line_adj%NOTFOUND IS NULL; -- empty cursor
2941
2942 -- Lock the price adjustments record.
2943 SELECT price_adjustment_id
2944 INTO l_lock_adjustment_id
2945 FROM oe_price_adjustments
2946 WHERE price_adjustment_id = l_price_adjustment_id
2947 FOR UPDATE NOWAIT;
2948
2949
2950 -- Added the following cursor for bug # 4701261
2951
2952 OPEN c_price_adj(l_price_adjustment_id);
2953 LOOP
2954 FETCH c_price_adj INTO l_price_adj_attrib_id;
2955 EXIT WHEN c_price_adj%NOTFOUND
2956 OR c_price_adj%NOTFOUND IS NULL;
2957
2958 SELECT price_adj_attrib_id
2959 INTO l_lock_price_adj_attrib_id
2960 FROM oe_price_adj_attribs
2961 WHERE price_adj_attrib_id = l_price_adj_attrib_id
2962 FOR UPDATE NOWAIT;
2963
2964 DELETE FROM oe_price_adj_attribs
2965 WHERE price_adj_attrib_id = l_price_adj_attrib_id;
2966
2967 END LOOP;
2968
2969 oe_debug_pub.add('Number of price_adj deleted : '||to_char(c_price_adj%ROWCOUNT),1);
2970 CLOSE c_price_adj;
2971
2972 -- Commented for Bug # 4701261
2973
2974 /*
2975 -- Delete from price_adj_attribs.
2976 SELECT price_adjustment_id
2977 INTO l_lock_adjustment_id
2978 FROM oe_price_adj_attribs
2979 WHERE price_adjustment_id = l_price_adjustment_id
2980 FOR UPDATE NOWAIT;
2981
2982 DELETE FROM oe_price_adj_attribs
2983 WHERE price_adjustment_id = l_price_adjustment_id;
2984 */
2985
2986 -- Delete from price_adj_assocs.
2987 OPEN c_price_adj_assocs(l_price_adjustment_id);
2988 LOOP
2989 FETCH c_price_adj_assocs INTO l_price_adj_assoc_id;
2990 EXIT WHEN c_price_adj_assocs%NOTFOUND -- end of fetch
2991 OR c_price_adj_assocs%NOTFOUND IS NULL; -- empty cursor
2992
2993 SELECT price_adj_assoc_id
2994 INTO l_lock_price_adj_assoc_id
2995 FROM oe_price_adj_assocs
2996 WHERE price_adj_assoc_id = l_price_adj_assoc_id
2997 FOR UPDATE NOWAIT;
2998
2999 DELETE FROM oe_price_adj_assocs
3000 WHERE price_adj_assoc_id = l_lock_price_adj_assoc_id;
3001
3002 END LOOP;
3003
3004 oe_debug_pub.add('Number of price_adj_assocs deleted : '||to_char(c_price_adj_assocs%ROWCOUNT),1);
3005 CLOSE c_price_adj_assocs;
3006
3007 DELETE FROM OE_PRICE_ADJUSTMENTS
3008 WHERE price_adjustment_id = l_lock_adjustment_id;
3009
3010 END LOOP;
3011
3012 oe_debug_pub.add('Number of price adjustments deleted : '||to_char(c_line_adj%ROWCOUNT),1);
3013 CLOSE c_line_adj;
3014 oe_debug_pub.add('Exiting OE_ORDER_PURGE_PVT.OE_Purge_Line_Adj : ');
3015
3016 RETURN FND_API.G_RET_STS_SUCCESS;
3017
3018 EXCEPTION -- Exception handler to record error
3019
3020 WHEN OTHERS THEN
3021 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3022 ROLLBACK TO SAVEPOINT ORDER_HEADER;
3023 record_errors( l_return_status
3024 ,p_purge_set_id
3025 ,p_header_id
3026 ,'ORDPUR: Line Price Adjustments '||substr(sqlerrm,1,200)
3027 );
3028 CLOSE c_line_adj;
3029 CLOSE c_price_adj_assocs;
3030 RETURN l_return_status;
3031
3032 END OE_Purge_Line_Adj;
3033
3034 FUNCTION OE_Purge_Price_Attribs
3035 (
3036 p_purge_set_id IN NUMBER
3037 , p_header_id IN NUMBER)
3038
3039 RETURN VARCHAR2
3040 IS
3041
3042 CURSOR c_order_price_attribs IS
3043 SELECT ORDER_PRICE_ATTRIB_ID
3044 FROM OE_ORDER_PRICE_ATTRIBS
3045 WHERE HEADER_ID = p_header_id;
3046
3047 l_order_price_attrib_id NUMBER;
3048 l_lock_price_attrib_id NUMBER;
3049 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
3050
3051 BEGIN
3052
3053 oe_debug_pub.add('Entering OE_ORDER_PURGE_PVT.OE_Purge_Price_Attribs : '||to_char(p_header_id));
3054 OPEN c_order_price_attribs;
3055
3056 LOOP
3057
3058 FETCH c_order_price_attribs INTO l_order_price_attrib_id;
3059 EXIT WHEN c_order_price_attribs%NOTFOUND -- end of fetch
3060 OR c_order_price_attribs%NOTFOUND IS NULL; -- empty cursor
3061
3062 -- Lock the sales credits record.
3063 SELECT ORDER_PRICE_ATTRIB_ID
3064 INTO l_lock_price_attrib_id
3065 FROM oe_order_price_attribs
3066 WHERE ORDER_PRICE_ATTRIB_ID = l_order_price_attrib_id
3067 FOR UPDATE NOWAIT;
3068
3069 DELETE FROM OE_ORDER_PRICE_ATTRIBS
3070 WHERE ORDER_PRICE_ATTRIB_ID = l_order_price_attrib_id;
3071
3072 END LOOP;
3073
3074 oe_debug_pub.add('Number of order price attribs deleted : '||to_char(c_order_price_attribs%ROWCOUNT),1);
3075 CLOSE c_order_price_attribs;
3076
3077 oe_debug_pub.add('Exiting OE_ORDER_PURGE_PVT.OE_Purge_Price_Attribs : ');
3078
3079 RETURN FND_API.G_RET_STS_SUCCESS;
3080
3081 EXCEPTION -- Exception handler to record error
3082
3083 WHEN OTHERS THEN
3084 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3085 ROLLBACK TO SAVEPOINT ORDER_HEADER;
3086 record_errors( l_return_status
3087 ,p_purge_set_id
3088 ,p_header_id
3089 ,'ORDPUR: Order Price Attributes '||substr(sqlerrm,1,200)
3090 );
3091 CLOSE c_order_price_attribs;
3092 RETURN l_return_status;
3093
3094 END OE_Purge_Price_Attribs;
3095
3096 FUNCTION OE_Purge_Order_Sales_Credits
3097 (
3098 p_purge_set_id IN NUMBER
3099 , p_header_id IN NUMBER)
3100
3101 RETURN VARCHAR2
3102 IS
3103
3104 CURSOR c_order_sales_credits IS
3105 SELECT SALES_CREDIT_ID
3106 FROM OE_SALES_CREDITS
3107 WHERE HEADER_ID = p_header_id;
3108
3109 l_sales_credit_id NUMBER;
3110 l_lock_sales_credit_id NUMBER;
3111 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
3112
3113 BEGIN
3114 oe_debug_pub.add('Entering OE_ORDER_PURGE_PVT.OE_Purge_Order_Sales_Credits : '||to_char(p_header_id));
3115
3116 OPEN c_order_sales_credits;
3117
3118 LOOP
3119
3120 FETCH c_order_sales_credits INTO l_sales_credit_id;
3121 EXIT WHEN c_order_sales_credits%NOTFOUND -- end of fetch
3122 OR c_order_sales_credits%NOTFOUND IS NULL; -- empty cursor
3123
3124 -- Lock the sales credits record.
3125 SELECT sales_credit_id
3126 INTO l_lock_sales_credit_id
3127 FROM oe_sales_credits
3128 WHERE sales_credit_id = l_sales_credit_id
3129 FOR UPDATE NOWAIT;
3130
3131 DELETE FROM OE_SALES_CREDITS
3132 WHERE SALES_CREDIT_ID = l_sales_credit_id;
3133
3134 END LOOP;
3135
3136 oe_debug_pub.add('Number of order sales credit deleted : '||to_char(c_order_sales_credits%ROWCOUNT),1);
3137 CLOSE c_order_sales_credits;
3138
3139 oe_debug_pub.add('Exiting OE_ORDER_PURGE_PVT.OE_Purge_Order_Sales_Credits : ');
3140 RETURN FND_API.G_RET_STS_SUCCESS;
3141
3142 EXCEPTION -- Exception handler to record error
3143
3144 WHEN OTHERS THEN
3145 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3146 ROLLBACK TO SAVEPOINT ORDER_HEADER;
3147 record_errors( l_return_status
3148 ,p_purge_set_id
3149 ,p_header_id
3150 ,'ORDPUR: Order Sales Credits '||substr(sqlerrm,1,200)
3151 );
3152 CLOSE c_order_sales_credits;
3153 RETURN l_return_status;
3154
3155 END OE_Purge_Order_Sales_Credits;
3156
3157 FUNCTION OE_Purge_Line_Sales_Credits
3158 (
3159 p_purge_set_id IN NUMBER
3160 , p_header_id IN NUMBER
3161 , p_line_id IN NUMBER)
3162
3163 RETURN VARCHAR2
3164 IS
3165
3166 CURSOR c_line_sales_credits IS
3167 SELECT SALES_CREDIT_ID
3168 FROM OE_SALES_CREDITS
3169 WHERE LINE_ID = p_line_id;
3170
3171 l_sales_credit_id NUMBER;
3172 l_lock_sales_credit_id NUMBER;
3173 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
3174
3175 BEGIN
3176
3177 oe_debug_pub.add('Entering OE_ORDER_PURGE_PVT.OE_Purge_Line_Sales_Credits : '||to_char(p_line_id));
3178 OPEN c_line_sales_credits;
3179
3180 LOOP
3181
3182 FETCH c_line_sales_credits INTO l_sales_credit_id;
3183 EXIT WHEN c_line_sales_credits%NOTFOUND -- end of fetch
3184 OR c_line_sales_credits%NOTFOUND IS NULL; -- empty cursor
3185
3186 -- Lock the sales credits record.
3187 SELECT sales_credit_id
3188 INTO l_lock_sales_credit_id
3189 FROM oe_sales_credits
3190 WHERE sales_credit_id = l_sales_credit_id
3191 FOR UPDATE NOWAIT;
3192
3193 DELETE FROM OE_SALES_CREDITS
3194 WHERE SALES_CREDIT_ID = l_sales_credit_id;
3195
3196 END LOOP;
3197
3198 oe_debug_pub.add('Number of line sales credit deleted : '||to_char(c_line_sales_credits%ROWCOUNT),1);
3199 CLOSE c_line_sales_credits;
3200
3201 oe_debug_pub.add('Exiting OE_ORDER_PURGE_PVT.OE_Purge_Line_Sales_Credits : ');
3202 RETURN FND_API.G_RET_STS_SUCCESS;
3203
3204 EXCEPTION -- Exception handler to record error
3205
3206 WHEN OTHERS THEN
3207 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3208 ROLLBACK TO SAVEPOINT ORDER_HEADER;
3209 record_errors( l_return_status
3210 ,p_purge_set_id
3211 ,p_header_id
3212 ,'ORDPUR: Line Sales Credits '||substr(sqlerrm,1,200)
3213 );
3214 CLOSE c_line_sales_credits;
3215 RETURN l_return_status;
3216
3217 END OE_Purge_line_Sales_Credits;
3218
3219 FUNCTION OE_Purge_Order_Sets
3220 (
3221 p_purge_set_id IN NUMBER
3222 , p_header_id IN NUMBER)
3223
3224 RETURN VARCHAR2
3225 IS
3226
3227 CURSOR c_order_sets IS
3228 SELECT SET_ID
3229 FROM OE_SETS
3230 WHERE HEADER_ID = p_header_id;
3231
3232 l_set_id NUMBER;
3233 l_lock_set_id NUMBER;
3234 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
3235
3236 BEGIN
3237
3238 oe_debug_pub.add('Entering OE_ORDER_PURGE_PVT.OE_Purge_Order_Sets : '||to_char(p_header_id));
3239 OPEN c_order_sets;
3240
3241 LOOP
3242
3243 FETCH c_order_sets INTO l_set_id;
3244 EXIT WHEN c_order_sets%NOTFOUND -- end of fetch
3245 OR c_order_sets%NOTFOUND IS NULL; -- empty cursor
3246
3247 -- Lock the set record.
3248 SELECT set_id
3249 INTO l_lock_set_id
3250 FROM oe_sets
3251 WHERE set_id = l_set_id
3252 FOR UPDATE NOWAIT;
3253
3254 DELETE FROM OE_SETS
3255 WHERE SET_ID = l_set_id;
3256
3257 END LOOP;
3258
3259 oe_debug_pub.add('Number of order sets deleted : '||to_char(c_order_sets%ROWCOUNT),1);
3260 CLOSE c_order_sets;
3261
3262 oe_debug_pub.add('Exiting OE_ORDER_PURGE_PVT.OE_Purge_Order_Sets : ');
3263 RETURN FND_API.G_RET_STS_SUCCESS;
3264
3265 EXCEPTION -- Exception handler to record error
3266
3267 WHEN OTHERS THEN
3268 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3269 ROLLBACK TO SAVEPOINT ORDER_HEADER;
3270 record_errors( l_return_status
3271 ,p_purge_set_id
3272 ,p_header_id
3273 ,'ORDPUR: Order Sets '||substr(sqlerrm,1,200)
3274 );
3275 CLOSE c_order_sets;
3276 RETURN l_return_status;
3277
3278 END OE_Purge_Order_Sets;
3279
3280 FUNCTION OE_Purge_Line_Sets
3281 (
3282 p_purge_set_id IN NUMBER
3283 , p_header_id IN NUMBER
3284 , p_line_id IN NUMBER)
3285
3286 RETURN VARCHAR2
3287 IS
3288
3289 CURSOR c_line_sets IS
3290 SELECT LINE_ID,SET_ID
3291 FROM OE_LINE_SETS
3292 WHERE LINE_ID = p_line_id;
3293
3294 l_set_id NUMBER;
3295 l_line_id NUMBER;
3296 l_lock_line_id NUMBER;
3297 l_lock_set_id NUMBER;
3298 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
3299
3300 BEGIN
3301 oe_debug_pub.add('Entering OE_ORDER_PURGE_PVT.OE_Purge_Line_Sets : '||to_char(p_line_id));
3302
3303 OPEN c_line_sets;
3304
3305 LOOP
3306
3307 FETCH c_line_sets INTO l_line_id,l_set_id;
3308 EXIT WHEN c_line_sets%NOTFOUND -- end of fetch
3309 OR c_line_sets%NOTFOUND IS NULL; -- empty cursor
3310
3311 -- Lock the line sets record.
3312 SELECT set_id,line_id
3313 INTO l_lock_set_id,l_lock_line_id
3314 FROM oe_line_sets
3315 WHERE set_id = l_set_id
3316 AND line_id = l_line_id
3317 FOR UPDATE NOWAIT;
3318
3319 DELETE FROM OE_LINE_SETS
3320 WHERE SET_ID = l_set_id
3321 AND LINE_ID = l_line_id;
3322
3323 END LOOP;
3324
3325 oe_debug_pub.add('Number of Line sets deleted : '||to_char(c_line_sets%ROWCOUNT),1);
3326 CLOSE c_line_sets;
3327
3328 oe_debug_pub.add('Exiting OE_ORDER_PURGE_PVT.OE_Purge_Line_Sets : ');
3329 RETURN FND_API.G_RET_STS_SUCCESS;
3330
3331 EXCEPTION -- Exception handler to record error
3332
3333 WHEN OTHERS THEN
3334 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3335 ROLLBACK TO SAVEPOINT ORDER_HEADER;
3336 record_errors( l_return_status
3337 ,p_purge_set_id
3338 ,p_header_id
3339 ,'ORDPUR: Line Sets '||substr(sqlerrm,1,200)
3340 );
3341 CLOSE c_line_sets;
3342 RETURN l_return_status;
3343
3344 END OE_Purge_Line_Sets;
3345
3346 FUNCTION OE_Purge_Order_Holds
3347 (
3348 p_purge_set_id IN NUMBER
3349 , p_header_id IN NUMBER)
3350
3351 RETURN VARCHAR2
3352 IS
3353
3354 CURSOR c_order_holds IS
3355 SELECT DISTINCT NVL(HOLD_RELEASE_ID,0),
3356 NVL(HOLD_SOURCE_ID,0),
3357 ORDER_HOLD_ID
3358 FROM OE_ORDER_HOLDS
3359 WHERE HEADER_ID = p_header_id;
3360
3361 CURSOR c_hold_sources(p_hold_source_id NUMBER) IS
3362 SELECT hold_source_id
3363 FROM OE_HOLD_SOURCES
3364 WHERE hold_source_id = p_hold_source_id
3365 AND hold_entity_id = p_header_id
3366 AND hold_entity_code = 'O';
3367
3368 CURSOR c_hold_releases(p_hold_release_id NUMBER) IS
3369 SELECT hold_release_id
3370 FROM OE_HOLD_RELEASES
3371 WHERE hold_release_id = p_hold_release_id;
3372
3373 l_order_hold_id NUMBER;
3374 l_lock_order_hold_id NUMBER;
3375 l_hold_release_id NUMBER;
3376 l_hold_source_id NUMBER;
3377 l_lock_hold_release_id NUMBER;
3378 l_lock_hold_source_id NUMBER;
3379 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
3380 l_count NUMBER := 0; -- bug 6148214
3381
3382 BEGIN
3383 oe_debug_pub.add('Entering OE_ORDER_PURGE_PVT.OE_Purge_Order_Hold : '||to_char(p_header_id));
3384
3385 OPEN c_order_holds;
3386
3387 LOOP
3388
3389 FETCH c_order_holds INTO
3390 l_hold_release_id,
3391 l_hold_source_id,
3392 l_order_hold_id;
3393 EXIT WHEN c_order_holds%NOTFOUND -- end of fetch
3394 OR c_order_holds%NOTFOUND IS NULL; -- empty cursor
3395
3396 -- Lock the order holds record.
3397 SELECT order_hold_id
3398 INTO l_lock_order_hold_id
3399 FROM oe_order_holds
3400 WHERE order_hold_id = l_order_hold_id
3401 FOR UPDATE NOWAIT;
3402
3403 OPEN c_hold_sources(l_hold_source_id);
3404 LOOP
3405
3406 FETCH c_hold_sources INTO l_hold_source_id;
3407 EXIT WHEN c_hold_sources%NOTFOUND -- end of fetch
3408 OR c_hold_sources%NOTFOUND IS NULL; -- empty cursor
3409
3410 -- Lock the order holds source record.
3411 SELECT hold_source_id
3412 INTO l_lock_hold_source_id
3413 FROM oe_hold_sources
3414 WHERE hold_source_id = l_hold_source_id
3415 FOR UPDATE NOWAIT;
3416
3417 DELETE FROM OE_HOLD_SOURCES
3418 WHERE HOLD_SOURCE_ID = l_hold_source_id;
3419
3420 END LOOP;
3421 oe_debug_pub.add('Number of hold sources deleted : '||to_char(c_hold_sources%ROWCOUNT),1);
3422 CLOSE c_hold_sources;
3423
3424 OPEN c_hold_releases(l_hold_release_id);
3425 LOOP
3426
3427 FETCH c_hold_releases INTO l_hold_release_id;
3428 EXIT WHEN c_hold_releases%NOTFOUND -- end of fetch
3429 OR c_hold_releases%NOTFOUND IS NULL; -- empty cursor
3430 --bug 6148214
3431 select count(*) into l_count from OE_ORDER_HOLDS where
3432 hold_release_id = l_hold_release_id and HEADER_ID <> p_header_id;
3433 IF (l_count = 0) THEN
3434 -- Lock the order holds release record.
3435 SELECT hold_release_id
3436 INTO l_lock_hold_release_id
3437 FROM oe_hold_releases
3438 WHERE hold_release_id = l_hold_release_id
3439 FOR UPDATE NOWAIT;
3440
3441 DELETE FROM OE_HOLD_RELEASES
3442 WHERE HOLD_RELEASE_ID = l_hold_release_id;
3443 END IF;
3444 END LOOP;
3445 oe_debug_pub.add('Number of hold releases deleted : '||to_char(c_hold_releases%ROWCOUNT),1);
3446 CLOSE c_hold_releases;
3447
3448 DELETE FROM OE_ORDER_HOLDS
3449 WHERE ORDER_HOLD_ID = l_order_hold_id;
3450
3451 END LOOP;
3452
3453 oe_debug_pub.add('Number of order holds deleted : '||to_char(c_order_holds%ROWCOUNT),1);
3454 CLOSE c_order_holds;
3455
3456 oe_debug_pub.add('Exiting OE_ORDER_PURGE_PVT.OE_Purge_Order_Holds : ');
3457 RETURN FND_API.G_RET_STS_SUCCESS;
3458
3459 EXCEPTION -- Exception handler to record error
3460
3461 WHEN OTHERS THEN
3462 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3463 ROLLBACK TO SAVEPOINT ORDER_HEADER;
3464 record_errors( l_return_status
3465 ,p_purge_set_id
3466 ,p_header_id
3467 ,'ORDPUR: Order Holds '||substr(sqlerrm,1,200)
3468 );
3469 CLOSE c_order_holds;
3470 RETURN l_return_status;
3471
3472 END OE_Purge_Order_Holds;
3473
3474 FUNCTION OE_Purge_RMA_Line_Receipts
3475 ( p_purge_set_id IN NUMBER
3476 , p_header_id IN NUMBER
3477 , p_line_id IN NUMBER)
3478 RETURN VARCHAR2
3479 IS
3480 l_return_status VARCHAR2(10);
3481 l_message VARCHAR2(2000);
3482 BEGIN
3483 oe_debug_pub.add('Entering OE_ORDER_PURGE_PVT.OE_Purge_RMA_Line_Receipts: '||to_char(p_line_id));
3484
3485 RCV_RMA_RCPT_PURGE.Purge_Receipts(p_line_id,l_return_status,l_message);
3486
3487 IF l_return_status = 'FALSE' THEN
3488 RETURN FND_API.G_RET_STS_ERROR;
3489 END IF;
3490
3491 oe_debug_pub.add('Exiting OE_ORDER_PURGE_PVT.OE_Purge_RMA_Line_Receipts.');
3492 RETURN FND_API.G_RET_STS_SUCCESS;
3493
3494 EXCEPTION -- Exception handler to record error
3495 WHEN OTHERS THEN
3496 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3497 ROLLBACK TO SAVEPOINT ORDER_HEADER;
3498 IF l_message is not null then
3499 record_errors( l_return_status
3500 ,p_purge_set_id
3501 ,p_header_id
3502 ,l_message
3503 );
3504 else
3505 record_errors( l_return_status
3506 ,p_purge_set_id
3507 ,p_header_id
3508 ,'ORDPUR: OE_Purge_RMA_Line_Receipts'||substr(sqlerrm,1,200)
3509 );
3510 end if;
3511
3512 RETURN l_return_status;
3513
3514 END OE_Purge_RMA_Line_Receipts;
3515
3516 FUNCTION OE_Purge_RMA_Line_Lot_Srl
3517 ( p_purge_set_id IN NUMBER
3518 , p_header_id IN NUMBER
3519 , p_line_id IN NUMBER)
3520 RETURN VARCHAR2
3521 IS
3522 CURSOR c_line_lot_serials IS
3523 SELECT lot_serial_id
3524 FROM oe_lot_serial_numbers
3525 WHERE LINE_ID = p_line_id;
3526
3527 l_lot_serial_id NUMBER;
3528 l_lock_lot_serial_id NUMBER;
3529 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
3530 l_message VARCHAR2(2000);
3531 BEGIN
3532 oe_debug_pub.add('Entering OE_ORDER_PURGE_PVT.OE_Purge_RMA_Line_Lot_Srl: '||to_char(p_line_id));
3533
3534
3535 OPEN c_line_lot_serials;
3536
3537 LOOP
3538 FETCH c_line_lot_serials INTO l_lot_serial_id;
3539 EXIT WHEN c_line_lot_serials%NOTFOUND -- end of fetch
3540 OR c_line_lot_serials%NOTFOUND IS NULL; -- empty cursor
3541
3542 -- Lock the sales credits record.
3543 SELECT lot_serial_id
3544 INTO l_lock_lot_serial_id
3545 FROM oe_lot_serial_numbers
3546 WHERE lot_serial_id = l_lot_serial_id
3547 FOR UPDATE NOWAIT;
3548
3549 delete from oe_lot_serial_numbers
3550 where lot_serial_id = l_lot_serial_id;
3551
3552 END LOOP;
3553
3554 CLOSE c_line_lot_serials;
3555
3556 oe_debug_pub.add('Exiting OE_ORDER_PURGE_PVT.OE_Purge_RMA_Line_Lot_Srl.');
3557 RETURN FND_API.G_RET_STS_SUCCESS;
3558
3559 EXCEPTION -- Exception handler to record error
3560 WHEN OTHERS THEN
3561 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3562 ROLLBACK TO SAVEPOINT ORDER_HEADER;
3563 record_errors( l_return_status
3564 ,p_purge_set_id
3565 ,p_header_id
3566 ,'ORDPUR: OE_Purge_RMA_Line_Lot_Srl'||substr(sqlerrm,1,200)
3567 );
3568 CLOSE c_line_lot_serials;
3569 RETURN l_return_status;
3570
3571 END OE_Purge_RMA_Line_Lot_Srl;
3572
3573
3574 PROCEDURE Record_Errors
3575 (
3576 p_return_status IN VARCHAR2
3577 , p_purge_set_id IN NUMBER
3578 , p_header_id IN NUMBER
3579 , p_error_message IN VARCHAR2 )
3580 IS
3581
3582 BEGIN
3583
3584 oe_debug_pub.add('Error Message : '||p_error_message);
3585 UPDATE oe_purge_orders
3586 SET ERROR_TEXT = p_error_message
3587 ,IS_PURGED = 'N'
3588 WHERE purge_set_id = p_purge_set_id
3589 AND header_id = p_header_id;
3590
3591 IF p_return_status <> FND_API.G_RET_STS_SUCCESS -- If writting a SQL error
3592 THEN -- then commit the record
3593 COMMIT; -- else (assume) it is commited by the caller
3594 END IF;
3595
3596 END Record_Errors;
3597
3598 -- Linda: added for multiple payments
3599 FUNCTION OE_Purge_Header_Payments
3600 ( p_purge_set_id IN NUMBER
3601 , p_header_id IN NUMBER)
3602 RETURN VARCHAR2 IS
3603
3604 l_header_id NUMBER;
3605 l_lock_header_id NUMBER;
3606 l_payment_number NUMBER;
3607
3608 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
3609
3610 Cursor c_header_pmts IS
3611 Select payment_number
3612 From oe_payments
3613 Where header_id = p_header_id
3614 and line_id is null
3615 FOR UPDATE NOWAIT;
3616
3617 BEGIN
3618
3619 OPEN c_header_pmts;
3620 LOOP
3621
3622 FETCH c_header_pmts into l_payment_number;
3623 EXIT WHEN c_header_pmts%NOTFOUND or c_header_pmts%NOTFOUND is NULL;
3624
3625 Delete from oe_payments
3626 Where header_id = p_header_id
3627 and nvl(payment_number,0) = nvl(l_payment_number,0);
3628
3629 END LOOP;
3630 CLOSE c_header_pmts;
3631
3632 RETURN FND_API.G_RET_STS_SUCCESS;
3633
3634 EXCEPTION
3635 WHEN OTHERS THEN
3636 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3637 ROLLBACK TO SAVEPOINT ORDER_HEADER;
3638 record_errors( l_return_status
3639 ,p_purge_set_id
3640 ,p_header_id
3641 ,'ORDPUR: Header Payments '||substr(sqlerrm,1,200)
3642 );
3643 CLOSE c_header_pmts;
3644 RETURN l_return_status;
3645 END OE_Purge_Header_Payments;
3646
3647 -- Added for multiple payments
3648 FUNCTION OE_Purge_Line_Payments
3649 ( p_purge_set_id IN NUMBER
3650 , p_header_id IN NUMBER
3651 , p_line_id IN NUMBER)
3652 RETURN VARCHAR2 IS
3653
3654 l_line_id NUMBER;
3655 l_lock_line_id NUMBER;
3656 l_pmt_count NUMBER;
3657 l_payment_number NUMBER;
3658 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
3659
3660 cursor c_lock_pmts is
3661 select payment_number
3662 from oe_payments
3663 where line_id = p_line_id
3664 and header_id = p_header_id
3665 FOR UPDATE NOWAIT;
3666
3667 BEGIN
3668
3669 OPEN c_lock_pmts;
3670 LOOP
3671
3672 FETCH c_lock_pmts into l_payment_number;
3673 EXIT WHEN c_lock_pmts%NOTFOUND or c_lock_pmts%NOTFOUND is NULL;
3674
3675 Delete from oe_payments
3676 Where line_id = p_line_id
3677 and nvl(payment_number,0) = nvl(l_payment_number,0);
3678
3679 END LOOP;
3680 CLOSE c_lock_pmts;
3681
3682 RETURN FND_API.G_RET_STS_SUCCESS;
3683
3684 EXCEPTION
3685 WHEN OTHERS THEN
3686 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3687 ROLLBACK TO SAVEPOINT ORDER_HEADER;
3688 record_errors( l_return_status
3689 ,p_purge_set_id
3690 ,p_line_id
3691 ,'ORDPUR: Line Payments '||substr(sqlerrm,1,200)
3692 );
3693 CLOSE c_lock_pmts;
3694 RETURN l_return_status;
3695
3696 END OE_Purge_Line_Payments;
3697
3698
3699 -- This procedure is called from the OEXOEPUR.pld .
3700 -- This checks if the Order is eligible for Purging.
3701
3702 PROCEDURE check_is_purgable( p_purge_set_id IN NUMBER
3703 , p_header_id IN NUMBER
3704 , p_order_number IN NUMBER
3705 , p_order_type_name IN VARCHAR2
3706 , p_quote_number IN NUMBER
3707 , p_is_purgable OUT NOCOPY VARCHAR2
3708 , p_error_message OUT NOCOPY VARCHAR2
3709 ) IS
3710
3711
3712 l_return_status VARCHAR2(1) := FND_API.G_TRUE;
3713 l_error_message VARCHAR2(2000);
3714 l_temp_mesg VARCHAR2(2000);
3715 l_is_purgable VARCHAR2(1);
3716 l_order_type_name VARCHAR2(30);
3717 l_flow_status VARCHAR2(80);
3718 l_transaction_phase_code VARCHAR2(1);
3719 l_cnt NUMBER;
3720
3721 CURSOR cur_transaction_code IS
3722 SELECT transaction_phase_code
3723 FROM oe_order_headers
3724 WHERE header_id=p_header_id;
3725
3726 BEGIN
3727
3728 -- Setting a Multiple Org access.
3729 mo_global.init('ONT');
3730
3731 --Quote purge changes.To Select Transaction Phase code
3732
3733 OPEN cur_transaction_code ;
3734 FETCH cur_transaction_code INTO l_transaction_phase_code;
3735
3736
3737 IF (cur_transaction_code%NOTFOUND) THEN
3738
3739 CLOSE cur_transaction_code;
3740
3741 IF (p_order_number IS NOT NULL) THEN
3742 FND_MESSAGE.SET_NAME('ONT','ONT_ORDER_ALREADY_PURGED');
3743 FND_MESSAGE.SET_TOKEN('ORDER', p_order_number);
3744 ELSE
3745 FND_MESSAGE.SET_NAME('ONT','ONT_ORDER_ALREADY_PURGED');
3746 FND_MESSAGE.SET_TOKEN('ORDER', p_quote_number);
3747 END IF;
3748
3749 l_temp_mesg := FND_MESSAGE.GET_ENCODED;
3750 FND_MESSAGE.SET_ENCODED(l_temp_mesg);
3751 l_error_message := FND_MESSAGE.GET;
3752 p_error_message := l_error_message;
3753 p_is_purgable := 'N';
3754 RETURN;
3755 ELSE
3756 CLOSE cur_transaction_code;
3757 END IF;
3758
3759
3760
3761
3762 IF OE_CODE_CONTROL.Get_Code_Release_Level >= '110510'
3763 AND nvl(l_transaction_phase_code,'F')='N' THEN
3764
3765 IF l_return_status = FND_API.G_TRUE THEN
3766
3767 FND_MESSAGE.SET_NAME('ONT','OE_PUR_OPEN_QUOTE');
3768 l_temp_mesg := FND_MESSAGE.GET_ENCODED;
3769 FND_MESSAGE.SET_ENCODED(l_temp_mesg);
3770 l_error_message := FND_MESSAGE.GET;
3771 l_return_status := OE_ORDER_PURGE_PVT.Check_Open_Quotes(p_header_id);
3772
3773 END IF;
3774
3775 ELSE
3776
3777 IF l_return_status = FND_API.G_TRUE THEN
3778 FND_MESSAGE.SET_NAME('ONT','OE_PUR_OPEN_ORDER');
3779 l_temp_mesg := FND_MESSAGE.GET_ENCODED;
3780 FND_MESSAGE.SET_ENCODED(l_temp_mesg);
3781 l_error_message := FND_MESSAGE.GET;
3782 l_return_status := OE_ORDER_PURGE_PVT.Check_Open_Orders( p_header_id);
3783 END IF;
3784
3785 IF l_return_status = FND_API.G_TRUE THEN
3786
3787 SELECT otl.name
3788 INTO l_order_type_name
3789 FROM oe_transaction_types_tl otl,
3790 oe_order_headers ooh
3791 WHERE otl.language = (SELECT language_code
3792 FROM fnd_languages
3793 WHERE installed_flag = 'B')
3794 AND otl.transaction_type_id = ooh.order_type_id
3795 AND ooh.header_id = p_header_id;
3796
3797 FND_MESSAGE.SET_NAME('ONT','OE_PUR_OPEN_INVOICES');
3798 l_temp_mesg := FND_MESSAGE.GET_ENCODED;
3799 FND_MESSAGE.SET_ENCODED(l_temp_mesg);
3800 l_error_message := FND_MESSAGE.GET;
3801 l_return_status := OE_ORDER_PURGE_PVT.check_open_invoiced_orders
3802 ( TO_CHAR(p_order_number), l_order_type_name );
3803 END IF;
3804
3805
3806 IF l_return_status = FND_API.G_TRUE THEN
3807 FND_MESSAGE.SET_NAME('ONT','OE_PUR_OPEN_RETURNS');
3808 l_temp_mesg := FND_MESSAGE.GET_ENCODED;
3809 FND_MESSAGE.SET_ENCODED(l_temp_mesg);
3810 l_error_message := FND_MESSAGE.GET;
3811 l_return_status := OE_ORDER_PURGE_PVT.check_open_returns
3812 (p_order_number, p_order_type_name);
3813 END IF;
3814
3815
3816 IF l_return_status = FND_API.G_TRUE THEN
3817 Check_Open_RMA_Receipts(p_header_id,l_return_status, l_error_message);
3818 END IF;
3819
3820
3821
3822 IF l_return_status = FND_API.G_TRUE THEN
3823 FND_MESSAGE.SET_NAME('ONT','OE_PUR_OPEN_DELIVERIES');
3824 l_temp_mesg := FND_MESSAGE.GET_ENCODED;
3825 FND_MESSAGE.SET_ENCODED(l_temp_mesg);
3826 l_error_message := FND_MESSAGE.GET;
3827
3828 SELECT count(*)
3829 INTO l_cnt
3830 FROM wsh_delivery_details dd,
3831 oe_order_lines l
3832 WHERE l.header_id = p_header_id
3833 AND dd.source_line_id = l.line_id
3834 AND dd.org_id = l.org_id
3835 AND dd.source_code = 'OE'
3836 AND (nvl(dd.released_status, 'N') not in ('C', 'D') or
3837 ( dd.released_status = 'C' and
3838 ( nvl(dd.inv_interfaced_flag, 'N') in ( 'N','P') or
3839 nvl(dd.oe_interfaced_flag, 'N') in ( 'N','P')
3840 )
3841 )
3842 );
3843 IF l_cnt > 0 THEN
3844 l_return_status := FND_API.G_FALSE;
3845 END IF;
3846 END IF;
3847
3848
3849 IF l_return_status = FND_API.G_TRUE THEN
3850
3851 IF PO_CODE_RELEASE_GRP.Current_Release >=
3852 PO_CODE_RELEASE_GRP.PRC_11i_Family_Pack_J AND
3853 OE_CODE_CONTROL.Code_Release_Level >= '110510' THEN
3854
3855 l_return_status :=
3856 OE_ORDER_PURGE_PVT.Check_Open_PO_Reqs_Dropship
3857 (p_header_id => p_header_id );
3858
3859 IF l_return_status = FND_API.G_FALSE THEN
3860
3861 FND_MESSAGE.SET_NAME('ONT','OE_PURGE_OPEN_PO_REQ');
3862 l_temp_mesg := FND_MESSAGE.GET_ENCODED;
3863 FND_MESSAGE.SET_ENCODED(l_temp_mesg);
3864 l_error_message := FND_MESSAGE.GET;
3865
3866 END IF;
3867
3868 END IF;
3869 END IF;
3870
3871 END IF;
3872
3873 IF l_return_status = FND_API.G_TRUE THEN
3874 p_error_message := NULL;
3875 p_is_purgable := 'Y' ;
3876
3877 ELSE
3878 p_error_message := l_error_message;
3879 p_is_purgable := 'N';
3880 END IF;
3881
3882 END check_is_purgable;
3883
3884
3885 END OE_Order_Purge_PVT;