[Home] [Help]
PACKAGE BODY: APPS.OE_ORDER_PURGE_PVT
Source
1 PACKAGE BODY OE_ORDER_PURGE_PVT AS
2 /* $Header: OEXVPURB.pls 120.19 2008/01/08 19:38:44 shrgupta 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 cursor c_purge_set_history is --bug#5631508
2164 select set_id from oe_sets_history
2165 where header_id= p_header_id
2166 FOR UPDATE NOWAIT;
2167
2168 l_doc_tbl OE_CONTRACTS_UTIL.doc_tbl_type;
2169 l_doc_rec OKC_TERMS_UTIL_GRP.doc_rec_type;
2170 l_msg_count NUMBER;
2171 l_msg_data VARCHAR2(2000);
2172
2173 BEGIN
2174
2175 oe_debug_pub.add('Entering OE_ORDER_PURGE_PVT.OE_Purge_Headers : '||to_char(p_header_id));
2176
2177 OPEN c_lock_header; -- Lock all rows to be purged
2178
2179 CLOSE c_lock_header;
2180
2181 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2182 l_return_status := OE_Order_Purge_PVT.OE_Purge_Header_Adj
2183 (
2184 p_purge_set_id => p_purge_set_id,
2185 p_header_id => p_header_id
2186 );
2187
2188 ELSE
2189 x_return_status := l_return_status;
2190 RETURN;
2191 END IF;
2192
2193 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2194 l_return_status := OE_Order_Purge_PVT.OE_Purge_Price_Attribs
2195 (
2196 p_purge_set_id => p_purge_set_id,
2197 p_header_id => p_header_id
2198 );
2199
2200 ELSE
2201 x_return_status := l_return_status;
2202 RETURN;
2203 END IF;
2204
2205 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2206 l_return_status := OE_Order_Purge_PVT.OE_Purge_Order_Sales_Credits
2207 (
2208 p_purge_set_id => p_purge_set_id,
2209 p_header_id => p_header_id
2210 );
2211
2212 ELSE
2213 x_return_status := l_return_status;
2214 RETURN;
2215 END IF;
2216
2217 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2218 l_return_status := OE_Order_Purge_PVT.OE_Purge_Order_Sets
2219 (
2220 p_purge_set_id => p_purge_set_id,
2221 p_header_id => p_header_id
2222 );
2223
2224 ELSE
2225 x_return_status := l_return_status;
2226 RETURN;
2227 END IF;
2228
2229 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2230 l_return_status := OE_Order_Purge_PVT.OE_Purge_Order_Holds
2231 (
2232 p_purge_set_id => p_purge_set_id,
2233 p_header_id => p_header_id
2234 );
2235
2236 ELSE
2237 x_return_status := l_return_status;
2238 RETURN;
2239 END IF;
2240
2241 -- purge for multiple payments
2242 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2243 l_return_status := OE_Order_Purge_PVT.OE_Purge_Header_payments
2244 ( p_purge_set_id => p_purge_set_id,
2245 p_header_id => p_header_id
2246 );
2247 ELSE
2248 x_return_status := l_return_status;
2249 RETURN;
2250 END IF;
2251
2252 -- Delete the attachments.
2253
2254 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2255 OE_Atchmt_Util.Delete_Attachments
2256 (
2257 p_entity_code => OE_GLOBALS.G_ENTITY_HEADER,
2258 p_entity_id => p_header_id,
2259 x_return_status => l_return_status
2260 );
2261 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2262 oe_debug_pub.add('Attachments delete failed : ');
2263 x_return_status := l_return_status;
2264 RETURN;
2265 END IF;
2266
2267 ELSE
2268 x_return_status := l_return_status;
2269 RETURN;
2270 END IF;
2271
2272 -- Delete record from CTO tables
2273
2274 oe_debug_pub.add('Calling CTOs API ',3);
2275
2276 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2277 CTO_ORDER_PURGE.Cto_Purge_Tables
2278 (
2279 p_header_id => p_header_id,
2280 x_error_msg => l_error_msg,
2281 x_return_status => l_return_status
2282 );
2283
2284 oe_debug_pub.add('Return from CTOs API : '||l_return_status,3);
2285
2286 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2287
2288 x_return_status := l_return_status;
2289 ROLLBACK TO SAVEPOINT ORDER_HEADER;
2290 record_errors( l_return_status
2291 ,p_purge_set_id
2292 ,p_header_id
2293 ,'ORDPUR: CTO Package Errored '||l_error_msg);
2294 RETURN;
2295
2296 END IF;
2297
2298 ELSE
2299 x_return_status := l_return_status;
2300 RETURN;
2301 END IF;
2302
2303 -- Delete the header work flow.
2304
2305 BEGIN
2306
2307 OE_Order_WF_Util.Delete_Row
2308 (
2309 p_type => 'HEADER',
2310 p_id => p_header_id
2311 );
2312
2313 EXCEPTION
2314 WHEN OTHERS THEN
2315 NULL;
2316
2317 END;
2318
2319 -- Purge Changes for 11i.10
2320 -- Purge the History tables
2321
2322 OPEN c_lock_header_hist;
2323 CLOSE c_lock_header_hist;
2324 DELETE FROM oe_order_header_history WHERE header_id = p_header_id;
2325
2326 OE_DEBUG_PUB.Add('After Deleting header history='|| to_char(p_header_id));
2327
2328 OPEN c_lock_price_adj_hist;
2329 CLOSE c_lock_price_adj_hist;
2330 DELETE FROM oe_price_adjs_history WHERE header_id = p_header_id;
2331
2332 OE_DEBUG_PUB.Add('After Deleting Price Adj history='|| to_char(p_header_id));
2333
2334 OPEN c_lock_sales_credit_hist;
2335 CLOSE c_lock_sales_credit_hist;
2336 DELETE FROM oe_sales_credit_history WHERE header_id = p_header_id;
2337
2338 OE_DEBUG_PUB.Add('After Deleting Sales Credit history='|| to_char(p_header_id));
2339
2340 OPEN c_purge_set_history; --bug#5631508
2341 CLOSE c_purge_set_history;
2342 DELETE FROM oe_sets_history WHERE header_id = p_header_id;
2343
2344 -- Purging Contract Articles
2345
2346 IF OE_CODE_CONTROL.Code_Release_Level >= '110510' THEN
2347
2348 IF OE_CONTRACTS_UTIL.Check_License = 'Y' THEN
2349
2350 l_doc_rec.doc_type := 'O';
2351 l_doc_rec.doc_id := p_header_id;
2352 l_doc_tbl(1) := l_doc_rec;
2353
2354 OE_CONTRACTS_UTIL.Purge_articles
2355 ( p_api_version => 1.0
2356 , p_doc_tbl => l_doc_tbl
2357 , x_return_status => x_return_status
2358 , x_msg_count => l_msg_count
2359 , x_msg_data => l_msg_data
2360 );
2361
2362 OE_DEBUG_PUB.Add('Purged the Articles for Header:'||to_char(p_header_id));
2363
2364 END IF;
2365
2366 END IF;
2367 DELETE FROM oe_order_headers
2368 WHERE header_id = p_header_id;
2369 oe_debug_pub.add('deleted header='|| to_char(p_header_id));
2370
2371 oe_debug_pub.add('Exiting OE_ORDER_PURGE_PVT.OE_Purge_Headers : ');
2372
2373 x_return_status := FND_API.G_RET_STS_SUCCESS;
2374
2375 EXCEPTION -- Exception handler to record error
2376
2377 WHEN OTHERS THEN
2378
2379 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2380 ROLLBACK TO SAVEPOINT ORDER_HEADER;
2381 record_errors( l_return_status
2382 ,p_purge_set_id
2383 ,p_header_id
2384 ,'ORDPUR: OE_ORDER_HEADERS '||substr(sqlerrm,1,200)
2385 );
2386 CLOSE c_lock_header;
2387
2388 END Oe_Purge_Headers;
2389
2390 PROCEDURE Oe_Purge_Lines
2391 (
2392 p_purge_set_id IN NUMBER
2393 , p_header_id IN NUMBER
2394 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
2395
2396 IS
2397
2398 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
2399 l_line_id NUMBER;
2400 l_lock_line_id NUMBER;
2401 l_order_quantity_uom VARCHAR2(3);
2402 l_is_ota_line BOOLEAN;
2403 l_org_id NUMBER;
2404 l_line_category_code VARCHAR2(30);
2405 l_source_type_code VARCHAR2(30);
2406 I NUMBER := 1;
2407 l_entity_id_tbl PO_TBL_NUMBER := PO_TBL_NUMBER();
2408 l_msg_count NUMBER;
2409 l_msg_data VARCHAR2(2000);
2410 l_top_model_line_id NUMBER; --added for bug 3664878
2411 l_config_header_id NUMBER; --added for bug 3664878
2412 l_config_rev_nbr NUMBER; --added for bug 3664878
2413
2414 --cursor definition of c_purge_lines modified for bug 3664878
2415 CURSOR c_purge_lines IS
2416 SELECT line_id,order_quantity_uom,
2417 org_id,line_category_code,source_type_code,
2418 top_model_line_id,config_header_id,
2419 config_rev_nbr
2420 FROM oe_order_lines
2421 WHERE header_id = p_header_id;
2422
2423 CURSOR c_purge_lines_hist IS
2424 SELECT line_id
2425 FROM oe_order_lines_history
2426 WHERE header_id = p_header_id
2427 FOR UPDATE NOWAIT;
2428
2429 CURSOR c_purge_ds IS
2430 SELECT line_id
2431 FROM oe_drop_ship_sources
2432 WHERE header_id = p_header_id
2433 FOR UPDATE NOWAIT;
2434
2435 CURSOR c_ds_line_loc IS
2436 SELECT ds.line_location_id
2437 FROM oe_drop_ship_sources ds
2438 WHERE ds.header_id = p_header_id;
2439
2440 BEGIN
2441
2442 oe_debug_pub.add('Entering OE_ORDER_PURGE_PVT.OE_Purge_Lines : '||to_char(p_header_id));
2443
2444
2445 OPEN c_purge_lines;
2446 LOOP
2447 FETCH c_purge_lines INTO l_line_id, l_order_quantity_uom, l_org_id
2448 , l_line_category_code,l_source_type_code,l_top_model_line_id
2449 , l_config_header_id, l_config_rev_nbr;
2450 EXIT WHEN c_purge_lines%NOTFOUND -- end of fetch
2451 OR c_purge_lines%NOTFOUND IS NULL; -- empty cursor
2452
2453 SELECT line_id
2454 INTO l_lock_line_id
2455 FROM oe_order_lines
2456 WHERE line_id = l_line_id
2457 FOR UPDATE NOWAIT;
2458
2459 --IF condition added for bug 3664878
2460 --to delete data from CZ tables in case of configurations
2461 IF l_line_id = l_top_model_line_id AND
2462 l_config_header_id is not null THEN
2463
2464 OE_Config_Pvt.Delete_Config
2465 ( p_config_hdr_id => l_config_header_id,
2466 p_config_rev_nbr => l_config_rev_nbr,
2467 x_return_status => l_return_status );
2468
2469 OE_DEBUG_PUB.Add('After Calling Delete_Config',1);
2470 END IF;
2471
2472
2473 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2474 l_return_status := OE_Order_Purge_PVT.OE_Purge_Line_Adj
2475 (
2476 p_purge_set_id => p_purge_set_id,
2477 p_header_id => p_header_id,
2478 p_line_id => l_line_id
2479 );
2480
2481 ELSE
2482 x_return_status := l_return_status;
2483 RETURN;
2484 END IF;
2485
2486 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2487 l_return_status := OE_Order_Purge_PVT.OE_Purge_Line_Sales_Credits
2488 (
2489 p_purge_set_id => p_purge_set_id,
2490 p_header_id => p_header_id,
2491 p_line_id => l_line_id
2492 );
2493
2494 ELSE
2495 x_return_status := l_return_status;
2496 RETURN;
2497 END IF;
2498
2499 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2500 l_return_status := OE_Order_Purge_PVT.OE_Purge_Line_Sets
2501 (
2502 p_purge_set_id => p_purge_set_id,
2503 p_header_id => p_header_id,
2504 p_line_id => l_line_id
2505 );
2506
2507 ELSE
2508 x_return_status := l_return_status;
2509 RETURN;
2510 END IF;
2511
2512 -- purge for multiple payments.
2513 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2514 l_return_status := OE_Order_Purge_PVT.OE_Purge_Line_payments
2515 ( p_purge_set_id => p_purge_set_id,
2516 p_header_id => p_header_id,
2517 p_line_id => l_line_id
2518 );
2519 ELSE
2520 x_return_status := l_return_status;
2521 RETURN;
2522 END IF;
2523
2524
2525 oe_debug_pub.add('Before RMA : ',1);
2526
2527 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2528 IF l_line_category_code = 'RETURN' THEN
2529
2530 l_return_status := OE_Purge_RMA_Line_Receipts
2531 ( p_purge_set_id => p_purge_set_id,
2532 p_header_id => p_header_id,
2533 p_line_id => l_line_id
2534 );
2535
2536 END IF;
2537
2538 ELSE
2539 x_return_status := l_return_status;
2540 RETURN;
2541 END IF;
2542
2543 oe_debug_pub.add('Before RMA_LOT : ',1);
2544
2545 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2546 IF l_line_category_code = 'RETURN' THEN
2547
2548 l_return_status := OE_Purge_RMA_Line_Lot_Srl
2549 ( p_purge_set_id => p_purge_set_id,
2550 p_header_id => p_header_id,
2551 p_line_id => l_line_id
2552 );
2553
2554 END IF;
2555 ELSE
2556 x_return_status := l_return_status;
2557 RETURN;
2558 END IF;
2559
2560
2561 -- Delete the attachments.
2562
2563 oe_debug_pub.add('Before before attach : ',1);
2564
2565 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2566 OE_Atchmt_Util.Delete_Attachments
2567 (
2568 p_entity_code => OE_GLOBALS.G_ENTITY_LINE,
2569 p_entity_id => l_line_id,
2570 x_return_status => l_return_status
2571 );
2572 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2573 oe_debug_pub.add('Attachments delete failed : ');
2574 x_return_status := l_return_status;
2575 RETURN;
2576 END IF;
2577
2578 ELSE
2579 x_return_status := l_return_status;
2580 RETURN;
2581 END IF;
2582
2583 -- Delete the Line work flow.
2584 oe_debug_pub.add('Before workflow : ',1);
2585 BEGIN
2586
2587
2588 OE_Order_WF_Util.Delete_Row
2589 (
2590 p_type => 'LINE',
2591 p_id => l_line_id
2592 );
2593
2594 EXCEPTION
2595 WHEN OTHERS THEN
2596 NULL;
2597
2598 END;
2599
2600 -- Purge the OTA lines. Currently, OTA lines are identified by UOM.
2601
2602 oe_debug_pub.add('Before ota : ',1);
2603 l_is_ota_line := OE_OTA_UTIL.Is_OTA_Line(l_order_quantity_uom);
2604 IF (l_is_ota_line) THEN
2605 OE_OTA_UTIL.Notify_OTA
2606 (p_line_id => l_line_id,
2607 p_org_id => l_org_id,
2608 p_order_quantity_uom => l_order_quantity_uom,
2609 p_daemon_type => 'P',
2610 x_return_status => l_return_status);
2611
2612 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2613 null;
2614 end if;
2615 End IF;
2616
2617 -- Purge the History tables
2618
2619 OPEN c_purge_lines_hist;
2620 CLOSE c_purge_lines_hist;
2621 DELETE FROM oe_order_lines_history WHERE header_id = p_header_id;
2622
2623 OE_DEBUG_PUB.Add('Before line delete : ',1);
2624
2625 DELETE FROM oe_order_lines
2626 WHERE line_id = l_line_id;
2627
2628 END LOOP;
2629
2630 -- Purge Changes for 11i.10
2631 -- Purge PO/Req
2632
2633 IF PO_CODE_RELEASE_GRP.Current_Release >=
2634 PO_CODE_RELEASE_GRP.PRC_11i_Family_Pack_J AND
2635 OE_CODE_CONTROL.Code_Release_Level >= '110510' THEN
2636
2637 OE_DEBUG_PUB.Add('Purge Externally Sourced Lines');
2638
2639 FOR c_ds_loc in c_ds_line_loc LOOP
2640
2641 IF c_ds_loc.line_location_id IS NOT NULL THEN
2642 OE_DEBUG_PUB.Add('Purge Line Loc : '||c_ds_loc.line_location_id);
2643 l_entity_id_tbl.extend(1);
2644 l_entity_id_tbl(I) := c_ds_loc.line_location_id;
2645 I := I + 1;
2646 END IF;
2647 END LOOP;
2648
2649 OPEN c_purge_ds;
2650 CLOSE c_purge_ds;
2651
2652 DELETE FROM oe_drop_ship_sources where header_id = p_header_id;
2653
2654 OE_DEBUG_PUB.Add('Before Calling PO Purge API ');
2655
2656 PO_OM_INTEGRATION_GRP.Purge
2657 ( p_api_version => 1.0
2658 ,p_init_msg_list => FND_API.G_FALSE
2659 ,p_commit => FND_API.G_FALSE
2660 ,x_return_status => x_return_status
2661 ,x_msg_count => l_msg_count
2662 ,x_msg_data => l_msg_data
2663 ,p_entity => 'PO_LINE_LOCATIONS'
2664 ,p_entity_id_tbl => l_entity_id_tbl
2665 );
2666
2667 OE_DEBUG_PUB.Add('After Calling PO Purge API '||x_return_status);
2668
2669 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2670 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2671 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
2672 OE_MSG_PUB.Add_Text(l_msg_data);
2673 OE_DEBUG_PUB.Add('Errors from Purge: '||l_msg_data,2) ;
2674 RAISE FND_API.G_EXC_ERROR;
2675 END IF;
2676 END IF;
2677
2678
2679 oe_debug_pub.add('Number of lines deleted : '||to_char(c_purge_lines%ROWCOUNT),1);
2680 CLOSE c_purge_lines;
2681
2682 oe_debug_pub.add('deleted lines for header='|| to_char(p_header_id));
2683 oe_debug_pub.add('Exiting OE_ORDER_PURGE_PVT.OE_Purge_Lines : ');
2684
2685 x_return_status := FND_API.G_RET_STS_SUCCESS;
2686
2687 EXCEPTION -- Exception handler to record error
2688
2689 WHEN OTHERS THEN
2690 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2691 ROLLBACK TO SAVEPOINT ORDER_HEADER;
2692 record_errors( l_return_status
2693 ,p_purge_set_id
2694 ,p_header_id
2695 ,'ORDPUR: OE_ORDER_LINES '||substr(sqlerrm,1,200)
2696 );
2697 CLOSE c_purge_lines;
2698
2699 END Oe_Purge_Lines;
2700
2701 FUNCTION OE_Purge_Header_Adj
2702 (
2703 p_purge_set_id IN NUMBER
2704 , p_header_id IN NUMBER)
2705
2706 RETURN VARCHAR2
2707 IS
2708 CURSOR c_header_adj IS
2709 SELECT PRICE_ADJUSTMENT_ID
2710 FROM OE_PRICE_ADJUSTMENTS
2711 WHERE HEADER_ID = p_header_id;
2712
2713 CURSOR c_price_adj(p_price_adjustment_id NUMBER) IS
2714 SELECT price_adj_attrib_id
2715 FROM oe_price_adj_attribs
2716 WHERE price_adjustment_id = p_price_adjustment_id;
2717
2718 CURSOR c_price_adj_assocs(p_price_adjustment_id NUMBER) IS
2719 SELECT price_adj_assoc_id
2720 FROM OE_PRICE_ADJ_ASSOCS OPAA
2721 WHERE opaa.rltd_price_adj_id IN (SELECT TO_NUMBER (p_price_adjustment_id)
2722 FROM DUAL
2723 UNION ALL
2724 SELECT opaa1.rltd_price_adj_id
2725 FROM oe_price_adj_assocs opaa1
2726 WHERE opaa1.price_adjustment_id = p_price_adjustment_id);
2727 /*
2728 WHERE (OPAA.price_adjustment_id = p_price_adjustment_id or
2729 OPAA.rltd_price_adj_id = p_price_adjustment_id) or
2730 OPAA.rltd_price_adj_id in( select opaa1.rltd_price_adj_id from
2731 oe_price_adj_assocs opaa1 where
2732 opaa1.price_adjustment_id = p_price_adjustment_id);
2733 ========= Commented for the bug 3053445 =========
2734 */
2735 l_price_adjustment_id NUMBER;
2736 l_lock_adjustment_id NUMBER;
2737 l_price_adj_assoc_id NUMBER;
2738 l_lock_price_adj_assoc_id NUMBER;
2739 l_price_adj_attrib_id NUMBER;
2740 l_lock_price_adj_attrib_id NUMBER;
2741 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
2742
2743 BEGIN
2744
2745 oe_debug_pub.add('Entering OE_ORDER_PURGE_PVT.OE_Purge_Header_Adj : '||to_char(p_header_id));
2746
2747 OPEN c_header_adj;
2748
2749 LOOP
2750
2751 FETCH c_header_adj INTO l_price_adjustment_id;
2752 oe_debug_pub.add('price adjust ment : '||to_char(l_price_adjustment_id));
2753 EXIT WHEN c_header_adj%NOTFOUND -- end of fetch
2754 OR c_header_adj%NOTFOUND IS NULL; -- empty cursor
2755
2756 -- Lock the price adjustments record.
2757 SELECT price_adjustment_id
2758 INTO l_lock_adjustment_id
2759 FROM oe_price_adjustments
2760 WHERE price_adjustment_id = l_price_adjustment_id
2761 FOR UPDATE NOWAIT;
2762
2763 -- Delete from price_adj_attribs.
2764 OPEN c_price_adj(l_price_adjustment_id);
2765 LOOP
2766 FETCH c_price_adj INTO l_price_adj_attrib_id;
2767 EXIT WHEN c_price_adj%NOTFOUND -- end of fetch
2768 OR c_price_adj%NOTFOUND IS NULL; -- empty cursor
2769
2770 SELECT price_adj_attrib_id
2771 INTO l_lock_price_adj_attrib_id
2772 FROM oe_price_adj_attribs
2773 WHERE price_adj_attrib_id = l_price_adj_attrib_id
2774 FOR UPDATE NOWAIT;
2775
2776 DELETE FROM oe_price_adj_attribs
2777 WHERE price_adj_attrib_id = l_price_adj_attrib_id;
2778
2779 END LOOP;
2780
2781 oe_debug_pub.add('Number of price_adj deleted : '||to_char(c_price_adj%ROWCOUNT),1);
2782 CLOSE c_price_adj;
2783
2784 -- Delete from price_adj_assocs.
2785 OPEN c_price_adj_assocs(l_price_adjustment_id);
2786 LOOP
2787 FETCH c_price_adj_assocs INTO l_price_adj_assoc_id;
2788 EXIT WHEN c_price_adj_assocs%NOTFOUND -- end of fetch
2789 OR c_price_adj_assocs%NOTFOUND IS NULL; -- empty cursor
2790 oe_debug_pub.add('price adjust ment 4 : '||to_char(l_price_adjustment_id));
2791
2792 SELECT price_adj_assoc_id
2793 INTO l_lock_price_adj_assoc_id
2794 FROM oe_price_adj_assocs
2795 WHERE price_adj_assoc_id = l_price_adj_assoc_id
2796 FOR UPDATE NOWAIT;
2797
2798 DELETE FROM oe_price_adj_assocs
2799 WHERE price_adj_assoc_id = l_lock_price_adj_assoc_id;
2800
2801 END LOOP;
2802
2803 oe_debug_pub.add('Number of price_adj_assocs deleted : '||to_char(c_price_adj_assocs%ROWCOUNT),1);
2804 CLOSE c_price_adj_assocs;
2805
2806
2807 DELETE FROM OE_PRICE_ADJUSTMENTS
2808 WHERE price_adjustment_id = l_lock_adjustment_id;
2809
2810 END LOOP;
2811
2812 oe_debug_pub.add('Number of price adjustments deleted : '||to_char(c_header_adj%ROWCOUNT),1);
2813 CLOSE c_header_adj;
2814
2815 oe_debug_pub.add('Exiting OE_ORDER_PURGE_PVT.OE_Purge_Header_Adj : ');
2816
2817 RETURN FND_API.G_RET_STS_SUCCESS;
2818
2819 EXCEPTION -- Exception handler to record error
2820
2821 WHEN OTHERS THEN
2822 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2823 ROLLBACK TO SAVEPOINT ORDER_HEADER;
2824 record_errors( l_return_status
2825 ,p_purge_set_id
2826 ,p_header_id
2827 ,'ORDPUR: Header Price Adjustments '||substr(sqlerrm,1,200)
2828 );
2829 CLOSE c_header_adj;
2830 CLOSE c_price_adj_assocs;
2831 RETURN l_return_status;
2832
2833 END OE_Purge_Header_Adj;
2834
2835 FUNCTION OE_Purge_Line_Adj
2836 (
2837 p_purge_set_id IN NUMBER
2838 , p_header_id IN NUMBER
2839 , p_line_id IN NUMBER)
2840
2841 RETURN VARCHAR2
2842 IS
2843 CURSOR c_line_adj IS
2844 SELECT PRICE_ADJUSTMENT_ID
2845 FROM OE_PRICE_ADJUSTMENTS
2846 WHERE LINE_ID = p_line_id;
2847
2848 -- Added cursor c_price_adj for bug # 4701261
2849
2850 CURSOR c_price_adj(p_price_adjustment_id NUMBER) IS
2851 SELECT price_adj_attrib_id
2852 FROM oe_price_adj_attribs
2853 WHERE price_adjustment_id = p_price_adjustment_id;
2854
2855 -- Modified for the SQLREP changes for SQL_ID = 14882948
2856 CURSOR c_price_adj_assocs(p_price_adjustment_id NUMBER) IS
2857 SELECT price_adj_assoc_id
2858 FROM OE_PRICE_ADJ_ASSOCS OPAA
2859 WHERE opaa.rltd_price_adj_id IN (
2860 SELECT p_price_adjustment_id
2861 FROM DUAL
2862 UNION ALL
2863 SELECT opaa1.rltd_price_adj_id
2864 FROM oe_price_adj_assocs opaa1
2865 WHERE opaa1.price_adjustment_id = p_price_adjustment_id);
2866
2867
2868 l_price_adjustment_id NUMBER;
2869 l_lock_adjustment_id NUMBER;
2870 l_price_adj_assoc_id NUMBER;
2871 l_lock_price_adj_assoc_id NUMBER;
2872 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
2873 l_price_adj_attrib_id NUMBER;
2874 l_lock_price_adj_attrib_id NUMBER;
2875
2876
2877 BEGIN
2878
2879 oe_debug_pub.add('Entering OE_ORDER_PURGE_PVT.OE_Purge_Line_Adj : '||to_char(p_line_id));
2880 OPEN c_line_adj;
2881
2882 LOOP
2883
2884 FETCH c_line_adj INTO l_price_adjustment_id;
2885 EXIT WHEN c_line_adj%NOTFOUND -- end of fetch
2886 OR c_line_adj%NOTFOUND IS NULL; -- empty cursor
2887
2888 -- Lock the price adjustments record.
2889 SELECT price_adjustment_id
2890 INTO l_lock_adjustment_id
2891 FROM oe_price_adjustments
2892 WHERE price_adjustment_id = l_price_adjustment_id
2893 FOR UPDATE NOWAIT;
2894
2895
2896 -- Added the following cursor for bug # 4701261
2897
2898 OPEN c_price_adj(l_price_adjustment_id);
2899 LOOP
2900 FETCH c_price_adj INTO l_price_adj_attrib_id;
2901 EXIT WHEN c_price_adj%NOTFOUND
2902 OR c_price_adj%NOTFOUND IS NULL;
2903
2904 SELECT price_adj_attrib_id
2905 INTO l_lock_price_adj_attrib_id
2906 FROM oe_price_adj_attribs
2907 WHERE price_adj_attrib_id = l_price_adj_attrib_id
2908 FOR UPDATE NOWAIT;
2909
2910 DELETE FROM oe_price_adj_attribs
2911 WHERE price_adj_attrib_id = l_price_adj_attrib_id;
2912
2913 END LOOP;
2914
2915 oe_debug_pub.add('Number of price_adj deleted : '||to_char(c_price_adj%ROWCOUNT),1);
2916 CLOSE c_price_adj;
2917
2918 -- Commented for Bug # 4701261
2919
2920 /*
2921 -- Delete from price_adj_attribs.
2922 SELECT price_adjustment_id
2923 INTO l_lock_adjustment_id
2924 FROM oe_price_adj_attribs
2925 WHERE price_adjustment_id = l_price_adjustment_id
2926 FOR UPDATE NOWAIT;
2927
2928 DELETE FROM oe_price_adj_attribs
2929 WHERE price_adjustment_id = l_price_adjustment_id;
2930 */
2931
2932 -- Delete from price_adj_assocs.
2933 OPEN c_price_adj_assocs(l_price_adjustment_id);
2934 LOOP
2935 FETCH c_price_adj_assocs INTO l_price_adj_assoc_id;
2936 EXIT WHEN c_price_adj_assocs%NOTFOUND -- end of fetch
2937 OR c_price_adj_assocs%NOTFOUND IS NULL; -- empty cursor
2938
2939 SELECT price_adj_assoc_id
2940 INTO l_lock_price_adj_assoc_id
2941 FROM oe_price_adj_assocs
2942 WHERE price_adj_assoc_id = l_price_adj_assoc_id
2943 FOR UPDATE NOWAIT;
2944
2945 DELETE FROM oe_price_adj_assocs
2946 WHERE price_adj_assoc_id = l_lock_price_adj_assoc_id;
2947
2948 END LOOP;
2949
2950 oe_debug_pub.add('Number of price_adj_assocs deleted : '||to_char(c_price_adj_assocs%ROWCOUNT),1);
2951 CLOSE c_price_adj_assocs;
2952
2953 DELETE FROM OE_PRICE_ADJUSTMENTS
2954 WHERE price_adjustment_id = l_lock_adjustment_id;
2955
2956 END LOOP;
2957
2958 oe_debug_pub.add('Number of price adjustments deleted : '||to_char(c_line_adj%ROWCOUNT),1);
2959 CLOSE c_line_adj;
2960 oe_debug_pub.add('Exiting OE_ORDER_PURGE_PVT.OE_Purge_Line_Adj : ');
2961
2962 RETURN FND_API.G_RET_STS_SUCCESS;
2963
2964 EXCEPTION -- Exception handler to record error
2965
2966 WHEN OTHERS THEN
2967 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2968 ROLLBACK TO SAVEPOINT ORDER_HEADER;
2969 record_errors( l_return_status
2970 ,p_purge_set_id
2971 ,p_header_id
2972 ,'ORDPUR: Line Price Adjustments '||substr(sqlerrm,1,200)
2973 );
2974 CLOSE c_line_adj;
2975 CLOSE c_price_adj_assocs;
2976 RETURN l_return_status;
2977
2978 END OE_Purge_Line_Adj;
2979
2980 FUNCTION OE_Purge_Price_Attribs
2981 (
2982 p_purge_set_id IN NUMBER
2983 , p_header_id IN NUMBER)
2984
2985 RETURN VARCHAR2
2986 IS
2987
2988 CURSOR c_order_price_attribs IS
2989 SELECT ORDER_PRICE_ATTRIB_ID
2990 FROM OE_ORDER_PRICE_ATTRIBS
2991 WHERE HEADER_ID = p_header_id;
2992
2993 l_order_price_attrib_id NUMBER;
2994 l_lock_price_attrib_id NUMBER;
2995 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
2996
2997 BEGIN
2998
2999 oe_debug_pub.add('Entering OE_ORDER_PURGE_PVT.OE_Purge_Price_Attribs : '||to_char(p_header_id));
3000 OPEN c_order_price_attribs;
3001
3002 LOOP
3003
3004 FETCH c_order_price_attribs INTO l_order_price_attrib_id;
3005 EXIT WHEN c_order_price_attribs%NOTFOUND -- end of fetch
3006 OR c_order_price_attribs%NOTFOUND IS NULL; -- empty cursor
3007
3008 -- Lock the sales credits record.
3009 SELECT ORDER_PRICE_ATTRIB_ID
3010 INTO l_lock_price_attrib_id
3011 FROM oe_order_price_attribs
3012 WHERE ORDER_PRICE_ATTRIB_ID = l_order_price_attrib_id
3013 FOR UPDATE NOWAIT;
3014
3015 DELETE FROM OE_ORDER_PRICE_ATTRIBS
3016 WHERE ORDER_PRICE_ATTRIB_ID = l_order_price_attrib_id;
3017
3018 END LOOP;
3019
3020 oe_debug_pub.add('Number of order price attribs deleted : '||to_char(c_order_price_attribs%ROWCOUNT),1);
3021 CLOSE c_order_price_attribs;
3022
3023 oe_debug_pub.add('Exiting OE_ORDER_PURGE_PVT.OE_Purge_Price_Attribs : ');
3024
3025 RETURN FND_API.G_RET_STS_SUCCESS;
3026
3027 EXCEPTION -- Exception handler to record error
3028
3029 WHEN OTHERS THEN
3030 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3031 ROLLBACK TO SAVEPOINT ORDER_HEADER;
3032 record_errors( l_return_status
3033 ,p_purge_set_id
3034 ,p_header_id
3035 ,'ORDPUR: Order Price Attributes '||substr(sqlerrm,1,200)
3036 );
3037 CLOSE c_order_price_attribs;
3038 RETURN l_return_status;
3039
3040 END OE_Purge_Price_Attribs;
3041
3042 FUNCTION OE_Purge_Order_Sales_Credits
3043 (
3044 p_purge_set_id IN NUMBER
3045 , p_header_id IN NUMBER)
3046
3047 RETURN VARCHAR2
3048 IS
3049
3050 CURSOR c_order_sales_credits IS
3051 SELECT SALES_CREDIT_ID
3052 FROM OE_SALES_CREDITS
3053 WHERE HEADER_ID = p_header_id;
3054
3055 l_sales_credit_id NUMBER;
3056 l_lock_sales_credit_id NUMBER;
3057 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
3058
3059 BEGIN
3060 oe_debug_pub.add('Entering OE_ORDER_PURGE_PVT.OE_Purge_Order_Sales_Credits : '||to_char(p_header_id));
3061
3062 OPEN c_order_sales_credits;
3063
3064 LOOP
3065
3066 FETCH c_order_sales_credits INTO l_sales_credit_id;
3067 EXIT WHEN c_order_sales_credits%NOTFOUND -- end of fetch
3068 OR c_order_sales_credits%NOTFOUND IS NULL; -- empty cursor
3069
3070 -- Lock the sales credits record.
3071 SELECT sales_credit_id
3072 INTO l_lock_sales_credit_id
3073 FROM oe_sales_credits
3074 WHERE sales_credit_id = l_sales_credit_id
3075 FOR UPDATE NOWAIT;
3076
3077 DELETE FROM OE_SALES_CREDITS
3078 WHERE SALES_CREDIT_ID = l_sales_credit_id;
3079
3080 END LOOP;
3081
3082 oe_debug_pub.add('Number of order sales credit deleted : '||to_char(c_order_sales_credits%ROWCOUNT),1);
3083 CLOSE c_order_sales_credits;
3084
3085 oe_debug_pub.add('Exiting OE_ORDER_PURGE_PVT.OE_Purge_Order_Sales_Credits : ');
3086 RETURN FND_API.G_RET_STS_SUCCESS;
3087
3088 EXCEPTION -- Exception handler to record error
3089
3090 WHEN OTHERS THEN
3091 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3092 ROLLBACK TO SAVEPOINT ORDER_HEADER;
3093 record_errors( l_return_status
3094 ,p_purge_set_id
3095 ,p_header_id
3096 ,'ORDPUR: Order Sales Credits '||substr(sqlerrm,1,200)
3097 );
3098 CLOSE c_order_sales_credits;
3099 RETURN l_return_status;
3100
3101 END OE_Purge_Order_Sales_Credits;
3102
3103 FUNCTION OE_Purge_Line_Sales_Credits
3104 (
3105 p_purge_set_id IN NUMBER
3106 , p_header_id IN NUMBER
3107 , p_line_id IN NUMBER)
3108
3109 RETURN VARCHAR2
3110 IS
3111
3112 CURSOR c_line_sales_credits IS
3113 SELECT SALES_CREDIT_ID
3114 FROM OE_SALES_CREDITS
3115 WHERE LINE_ID = p_line_id;
3116
3117 l_sales_credit_id NUMBER;
3118 l_lock_sales_credit_id NUMBER;
3119 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
3120
3121 BEGIN
3122
3123 oe_debug_pub.add('Entering OE_ORDER_PURGE_PVT.OE_Purge_Line_Sales_Credits : '||to_char(p_line_id));
3124 OPEN c_line_sales_credits;
3125
3126 LOOP
3127
3128 FETCH c_line_sales_credits INTO l_sales_credit_id;
3129 EXIT WHEN c_line_sales_credits%NOTFOUND -- end of fetch
3130 OR c_line_sales_credits%NOTFOUND IS NULL; -- empty cursor
3131
3132 -- Lock the sales credits record.
3133 SELECT sales_credit_id
3134 INTO l_lock_sales_credit_id
3135 FROM oe_sales_credits
3136 WHERE sales_credit_id = l_sales_credit_id
3137 FOR UPDATE NOWAIT;
3138
3139 DELETE FROM OE_SALES_CREDITS
3140 WHERE SALES_CREDIT_ID = l_sales_credit_id;
3141
3142 END LOOP;
3143
3144 oe_debug_pub.add('Number of line sales credit deleted : '||to_char(c_line_sales_credits%ROWCOUNT),1);
3145 CLOSE c_line_sales_credits;
3146
3147 oe_debug_pub.add('Exiting OE_ORDER_PURGE_PVT.OE_Purge_Line_Sales_Credits : ');
3148 RETURN FND_API.G_RET_STS_SUCCESS;
3149
3150 EXCEPTION -- Exception handler to record error
3151
3152 WHEN OTHERS THEN
3153 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3154 ROLLBACK TO SAVEPOINT ORDER_HEADER;
3155 record_errors( l_return_status
3156 ,p_purge_set_id
3157 ,p_header_id
3158 ,'ORDPUR: Line Sales Credits '||substr(sqlerrm,1,200)
3159 );
3160 CLOSE c_line_sales_credits;
3161 RETURN l_return_status;
3162
3163 END OE_Purge_line_Sales_Credits;
3164
3165 FUNCTION OE_Purge_Order_Sets
3166 (
3167 p_purge_set_id IN NUMBER
3168 , p_header_id IN NUMBER)
3169
3170 RETURN VARCHAR2
3171 IS
3172
3173 CURSOR c_order_sets IS
3174 SELECT SET_ID
3175 FROM OE_SETS
3176 WHERE HEADER_ID = p_header_id;
3177
3178 l_set_id NUMBER;
3179 l_lock_set_id NUMBER;
3180 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
3181
3182 BEGIN
3183
3184 oe_debug_pub.add('Entering OE_ORDER_PURGE_PVT.OE_Purge_Order_Sets : '||to_char(p_header_id));
3185 OPEN c_order_sets;
3186
3187 LOOP
3188
3189 FETCH c_order_sets INTO l_set_id;
3190 EXIT WHEN c_order_sets%NOTFOUND -- end of fetch
3191 OR c_order_sets%NOTFOUND IS NULL; -- empty cursor
3192
3193 -- Lock the set record.
3194 SELECT set_id
3195 INTO l_lock_set_id
3196 FROM oe_sets
3197 WHERE set_id = l_set_id
3198 FOR UPDATE NOWAIT;
3199
3200 DELETE FROM OE_SETS
3201 WHERE SET_ID = l_set_id;
3202
3203 END LOOP;
3204
3205 oe_debug_pub.add('Number of order sets deleted : '||to_char(c_order_sets%ROWCOUNT),1);
3206 CLOSE c_order_sets;
3207
3208 oe_debug_pub.add('Exiting OE_ORDER_PURGE_PVT.OE_Purge_Order_Sets : ');
3209 RETURN FND_API.G_RET_STS_SUCCESS;
3210
3211 EXCEPTION -- Exception handler to record error
3212
3213 WHEN OTHERS THEN
3214 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3215 ROLLBACK TO SAVEPOINT ORDER_HEADER;
3216 record_errors( l_return_status
3217 ,p_purge_set_id
3218 ,p_header_id
3219 ,'ORDPUR: Order Sets '||substr(sqlerrm,1,200)
3220 );
3221 CLOSE c_order_sets;
3222 RETURN l_return_status;
3223
3224 END OE_Purge_Order_Sets;
3225
3226 FUNCTION OE_Purge_Line_Sets
3227 (
3228 p_purge_set_id IN NUMBER
3229 , p_header_id IN NUMBER
3230 , p_line_id IN NUMBER)
3231
3232 RETURN VARCHAR2
3233 IS
3234
3235 CURSOR c_line_sets IS
3236 SELECT LINE_ID,SET_ID
3237 FROM OE_LINE_SETS
3238 WHERE LINE_ID = p_line_id;
3239
3240 l_set_id NUMBER;
3241 l_line_id NUMBER;
3242 l_lock_line_id NUMBER;
3243 l_lock_set_id NUMBER;
3244 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
3245
3246 BEGIN
3247 oe_debug_pub.add('Entering OE_ORDER_PURGE_PVT.OE_Purge_Line_Sets : '||to_char(p_line_id));
3248
3249 OPEN c_line_sets;
3250
3251 LOOP
3252
3253 FETCH c_line_sets INTO l_line_id,l_set_id;
3254 EXIT WHEN c_line_sets%NOTFOUND -- end of fetch
3255 OR c_line_sets%NOTFOUND IS NULL; -- empty cursor
3256
3257 -- Lock the line sets record.
3258 SELECT set_id,line_id
3259 INTO l_lock_set_id,l_lock_line_id
3260 FROM oe_line_sets
3261 WHERE set_id = l_set_id
3262 AND line_id = l_line_id
3263 FOR UPDATE NOWAIT;
3264
3265 DELETE FROM OE_LINE_SETS
3266 WHERE SET_ID = l_set_id
3267 AND LINE_ID = l_line_id;
3268
3269 END LOOP;
3270
3271 oe_debug_pub.add('Number of Line sets deleted : '||to_char(c_line_sets%ROWCOUNT),1);
3272 CLOSE c_line_sets;
3273
3274 oe_debug_pub.add('Exiting OE_ORDER_PURGE_PVT.OE_Purge_Line_Sets : ');
3275 RETURN FND_API.G_RET_STS_SUCCESS;
3276
3277 EXCEPTION -- Exception handler to record error
3278
3279 WHEN OTHERS THEN
3280 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3281 ROLLBACK TO SAVEPOINT ORDER_HEADER;
3282 record_errors( l_return_status
3283 ,p_purge_set_id
3284 ,p_header_id
3285 ,'ORDPUR: Line Sets '||substr(sqlerrm,1,200)
3286 );
3287 CLOSE c_line_sets;
3288 RETURN l_return_status;
3289
3290 END OE_Purge_Line_Sets;
3291
3292 FUNCTION OE_Purge_Order_Holds
3293 (
3294 p_purge_set_id IN NUMBER
3295 , p_header_id IN NUMBER)
3296
3297 RETURN VARCHAR2
3298 IS
3299
3300 CURSOR c_order_holds IS
3301 SELECT DISTINCT NVL(HOLD_RELEASE_ID,0),
3302 NVL(HOLD_SOURCE_ID,0),
3303 ORDER_HOLD_ID
3304 FROM OE_ORDER_HOLDS
3305 WHERE HEADER_ID = p_header_id;
3306
3307 CURSOR c_hold_sources(p_hold_source_id NUMBER) IS
3308 SELECT hold_source_id
3309 FROM OE_HOLD_SOURCES
3310 WHERE hold_source_id = p_hold_source_id
3311 AND hold_entity_id = p_header_id
3312 AND hold_entity_code = 'O';
3313
3314 CURSOR c_hold_releases(p_hold_release_id NUMBER) IS
3315 SELECT hold_release_id
3316 FROM OE_HOLD_RELEASES
3317 WHERE hold_release_id = p_hold_release_id;
3318
3319 l_order_hold_id NUMBER;
3320 l_lock_order_hold_id NUMBER;
3321 l_hold_release_id NUMBER;
3322 l_hold_source_id NUMBER;
3323 l_lock_hold_release_id NUMBER;
3324 l_lock_hold_source_id NUMBER;
3325 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
3326 l_count NUMBER := 0; -- bug 6148214
3327
3328 BEGIN
3329 oe_debug_pub.add('Entering OE_ORDER_PURGE_PVT.OE_Purge_Order_Hold : '||to_char(p_header_id));
3330
3331 OPEN c_order_holds;
3332
3333 LOOP
3334
3335 FETCH c_order_holds INTO
3336 l_hold_release_id,
3337 l_hold_source_id,
3338 l_order_hold_id;
3339 EXIT WHEN c_order_holds%NOTFOUND -- end of fetch
3340 OR c_order_holds%NOTFOUND IS NULL; -- empty cursor
3341
3342 -- Lock the order holds record.
3343 SELECT order_hold_id
3344 INTO l_lock_order_hold_id
3345 FROM oe_order_holds
3346 WHERE order_hold_id = l_order_hold_id
3347 FOR UPDATE NOWAIT;
3348
3349 OPEN c_hold_sources(l_hold_source_id);
3350 LOOP
3351
3352 FETCH c_hold_sources INTO l_hold_source_id;
3353 EXIT WHEN c_hold_sources%NOTFOUND -- end of fetch
3354 OR c_hold_sources%NOTFOUND IS NULL; -- empty cursor
3355
3356 -- Lock the order holds source record.
3357 SELECT hold_source_id
3358 INTO l_lock_hold_source_id
3359 FROM oe_hold_sources
3360 WHERE hold_source_id = l_hold_source_id
3361 FOR UPDATE NOWAIT;
3362
3363 DELETE FROM OE_HOLD_SOURCES
3364 WHERE HOLD_SOURCE_ID = l_hold_source_id;
3365
3366 END LOOP;
3367 oe_debug_pub.add('Number of hold sources deleted : '||to_char(c_hold_sources%ROWCOUNT),1);
3368 CLOSE c_hold_sources;
3369
3370 OPEN c_hold_releases(l_hold_release_id);
3371 LOOP
3372
3373 FETCH c_hold_releases INTO l_hold_release_id;
3374 EXIT WHEN c_hold_releases%NOTFOUND -- end of fetch
3375 OR c_hold_releases%NOTFOUND IS NULL; -- empty cursor
3376 --bug 6148214
3377 select count(*) into l_count from OE_ORDER_HOLDS where
3378 hold_release_id = l_hold_release_id and HEADER_ID <> p_header_id;
3379 IF (l_count = 0) THEN
3380 -- Lock the order holds release record.
3381 SELECT hold_release_id
3382 INTO l_lock_hold_release_id
3383 FROM oe_hold_releases
3384 WHERE hold_release_id = l_hold_release_id
3385 FOR UPDATE NOWAIT;
3386
3387 DELETE FROM OE_HOLD_RELEASES
3388 WHERE HOLD_RELEASE_ID = l_hold_release_id;
3389 END IF;
3390 END LOOP;
3391 oe_debug_pub.add('Number of hold releases deleted : '||to_char(c_hold_releases%ROWCOUNT),1);
3392 CLOSE c_hold_releases;
3393
3394 DELETE FROM OE_ORDER_HOLDS
3395 WHERE ORDER_HOLD_ID = l_order_hold_id;
3396
3397 END LOOP;
3398
3399 oe_debug_pub.add('Number of order holds deleted : '||to_char(c_order_holds%ROWCOUNT),1);
3400 CLOSE c_order_holds;
3401
3402 oe_debug_pub.add('Exiting OE_ORDER_PURGE_PVT.OE_Purge_Order_Holds : ');
3403 RETURN FND_API.G_RET_STS_SUCCESS;
3404
3405 EXCEPTION -- Exception handler to record error
3406
3407 WHEN OTHERS THEN
3408 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3409 ROLLBACK TO SAVEPOINT ORDER_HEADER;
3410 record_errors( l_return_status
3411 ,p_purge_set_id
3412 ,p_header_id
3413 ,'ORDPUR: Order Holds '||substr(sqlerrm,1,200)
3414 );
3415 CLOSE c_order_holds;
3416 RETURN l_return_status;
3417
3418 END OE_Purge_Order_Holds;
3419
3420 FUNCTION OE_Purge_RMA_Line_Receipts
3421 ( p_purge_set_id IN NUMBER
3422 , p_header_id IN NUMBER
3423 , p_line_id IN NUMBER)
3424 RETURN VARCHAR2
3425 IS
3426 l_return_status VARCHAR2(10);
3427 l_message VARCHAR2(2000);
3428 BEGIN
3429 oe_debug_pub.add('Entering OE_ORDER_PURGE_PVT.OE_Purge_RMA_Line_Receipts: '||to_char(p_line_id));
3430
3431 RCV_RMA_RCPT_PURGE.Purge_Receipts(p_line_id,l_return_status,l_message);
3432
3433 IF l_return_status = 'FALSE' THEN
3434 RETURN FND_API.G_RET_STS_ERROR;
3435 END IF;
3436
3437 oe_debug_pub.add('Exiting OE_ORDER_PURGE_PVT.OE_Purge_RMA_Line_Receipts.');
3438 RETURN FND_API.G_RET_STS_SUCCESS;
3439
3440 EXCEPTION -- Exception handler to record error
3441 WHEN OTHERS THEN
3442 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3443 ROLLBACK TO SAVEPOINT ORDER_HEADER;
3444 IF l_message is not null then
3445 record_errors( l_return_status
3446 ,p_purge_set_id
3447 ,p_header_id
3448 ,l_message
3449 );
3450 else
3451 record_errors( l_return_status
3452 ,p_purge_set_id
3453 ,p_header_id
3454 ,'ORDPUR: OE_Purge_RMA_Line_Receipts'||substr(sqlerrm,1,200)
3455 );
3456 end if;
3457
3458 RETURN l_return_status;
3459
3460 END OE_Purge_RMA_Line_Receipts;
3461
3462 FUNCTION OE_Purge_RMA_Line_Lot_Srl
3463 ( p_purge_set_id IN NUMBER
3464 , p_header_id IN NUMBER
3465 , p_line_id IN NUMBER)
3466 RETURN VARCHAR2
3467 IS
3468 CURSOR c_line_lot_serials IS
3469 SELECT lot_serial_id
3470 FROM oe_lot_serial_numbers
3471 WHERE LINE_ID = p_line_id;
3472
3473 l_lot_serial_id NUMBER;
3474 l_lock_lot_serial_id NUMBER;
3475 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
3476 l_message VARCHAR2(2000);
3477 BEGIN
3478 oe_debug_pub.add('Entering OE_ORDER_PURGE_PVT.OE_Purge_RMA_Line_Lot_Srl: '||to_char(p_line_id));
3479
3480
3481 OPEN c_line_lot_serials;
3482
3483 LOOP
3484 FETCH c_line_lot_serials INTO l_lot_serial_id;
3485 EXIT WHEN c_line_lot_serials%NOTFOUND -- end of fetch
3486 OR c_line_lot_serials%NOTFOUND IS NULL; -- empty cursor
3487
3488 -- Lock the sales credits record.
3489 SELECT lot_serial_id
3490 INTO l_lock_lot_serial_id
3491 FROM oe_lot_serial_numbers
3492 WHERE lot_serial_id = l_lot_serial_id
3493 FOR UPDATE NOWAIT;
3494
3495 delete from oe_lot_serial_numbers
3496 where lot_serial_id = l_lot_serial_id;
3497
3498 END LOOP;
3499
3500 CLOSE c_line_lot_serials;
3501
3502 oe_debug_pub.add('Exiting OE_ORDER_PURGE_PVT.OE_Purge_RMA_Line_Lot_Srl.');
3503 RETURN FND_API.G_RET_STS_SUCCESS;
3504
3505 EXCEPTION -- Exception handler to record error
3506 WHEN OTHERS THEN
3507 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3508 ROLLBACK TO SAVEPOINT ORDER_HEADER;
3509 record_errors( l_return_status
3510 ,p_purge_set_id
3511 ,p_header_id
3512 ,'ORDPUR: OE_Purge_RMA_Line_Lot_Srl'||substr(sqlerrm,1,200)
3513 );
3514 CLOSE c_line_lot_serials;
3515 RETURN l_return_status;
3516
3517 END OE_Purge_RMA_Line_Lot_Srl;
3518
3519
3520 PROCEDURE Record_Errors
3521 (
3522 p_return_status IN VARCHAR2
3523 , p_purge_set_id IN NUMBER
3524 , p_header_id IN NUMBER
3525 , p_error_message IN VARCHAR2 )
3526 IS
3527
3528 BEGIN
3529
3530 oe_debug_pub.add('Error Message : '||p_error_message);
3531 UPDATE oe_purge_orders
3532 SET ERROR_TEXT = p_error_message
3533 ,IS_PURGED = 'N'
3534 WHERE purge_set_id = p_purge_set_id
3535 AND header_id = p_header_id;
3536
3537 IF p_return_status <> FND_API.G_RET_STS_SUCCESS -- If writting a SQL error
3538 THEN -- then commit the record
3539 COMMIT; -- else (assume) it is commited by the caller
3540 END IF;
3541
3542 END Record_Errors;
3543
3544 -- Linda: added for multiple payments
3545 FUNCTION OE_Purge_Header_Payments
3546 ( p_purge_set_id IN NUMBER
3547 , p_header_id IN NUMBER)
3548 RETURN VARCHAR2 IS
3549
3550 l_header_id NUMBER;
3551 l_lock_header_id NUMBER;
3552 l_payment_number NUMBER;
3553
3554 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
3555
3556 Cursor c_header_pmts IS
3557 Select payment_number
3558 From oe_payments
3559 Where header_id = p_header_id
3560 and line_id is null
3561 FOR UPDATE NOWAIT;
3562
3563 BEGIN
3564
3565 OPEN c_header_pmts;
3566 LOOP
3567
3568 FETCH c_header_pmts into l_payment_number;
3569 EXIT WHEN c_header_pmts%NOTFOUND or c_header_pmts%NOTFOUND is NULL;
3570
3571 Delete from oe_payments
3572 Where header_id = p_header_id
3573 and nvl(payment_number,0) = nvl(l_payment_number,0);
3574
3575 END LOOP;
3576 CLOSE c_header_pmts;
3577
3578 RETURN FND_API.G_RET_STS_SUCCESS;
3579
3580 EXCEPTION
3581 WHEN OTHERS THEN
3582 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3583 ROLLBACK TO SAVEPOINT ORDER_HEADER;
3584 record_errors( l_return_status
3585 ,p_purge_set_id
3586 ,p_header_id
3587 ,'ORDPUR: Header Payments '||substr(sqlerrm,1,200)
3588 );
3589 CLOSE c_header_pmts;
3590 RETURN l_return_status;
3591 END OE_Purge_Header_Payments;
3592
3593 -- Added for multiple payments
3594 FUNCTION OE_Purge_Line_Payments
3595 ( p_purge_set_id IN NUMBER
3596 , p_header_id IN NUMBER
3597 , p_line_id IN NUMBER)
3598 RETURN VARCHAR2 IS
3599
3600 l_line_id NUMBER;
3601 l_lock_line_id NUMBER;
3602 l_pmt_count NUMBER;
3603 l_payment_number NUMBER;
3604 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
3605
3606 cursor c_lock_pmts is
3607 select payment_number
3608 from oe_payments
3609 where line_id = p_line_id
3610 and header_id = p_header_id
3611 FOR UPDATE NOWAIT;
3612
3613 BEGIN
3614
3615 OPEN c_lock_pmts;
3616 LOOP
3617
3618 FETCH c_lock_pmts into l_payment_number;
3619 EXIT WHEN c_lock_pmts%NOTFOUND or c_lock_pmts%NOTFOUND is NULL;
3620
3621 Delete from oe_payments
3622 Where line_id = p_line_id
3623 and nvl(payment_number,0) = nvl(l_payment_number,0);
3624
3625 END LOOP;
3626 CLOSE c_lock_pmts;
3627
3628 RETURN FND_API.G_RET_STS_SUCCESS;
3629
3630 EXCEPTION
3631 WHEN OTHERS THEN
3632 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3633 ROLLBACK TO SAVEPOINT ORDER_HEADER;
3634 record_errors( l_return_status
3635 ,p_purge_set_id
3636 ,p_line_id
3637 ,'ORDPUR: Line Payments '||substr(sqlerrm,1,200)
3638 );
3639 CLOSE c_lock_pmts;
3640 RETURN l_return_status;
3641
3642 END OE_Purge_Line_Payments;
3643
3644
3645 -- This procedure is called from the OEXOEPUR.pld .
3646 -- This checks if the Order is eligible for Purging.
3647
3648 PROCEDURE check_is_purgable( p_purge_set_id IN NUMBER
3649 , p_header_id IN NUMBER
3650 , p_order_number IN NUMBER
3651 , p_order_type_name IN VARCHAR2
3652 , p_quote_number IN NUMBER
3653 , p_is_purgable OUT NOCOPY VARCHAR2
3654 , p_error_message OUT NOCOPY VARCHAR2
3655 ) IS
3656
3657
3658 l_return_status VARCHAR2(1) := FND_API.G_TRUE;
3659 l_error_message VARCHAR2(2000);
3660 l_temp_mesg VARCHAR2(2000);
3661 l_is_purgable VARCHAR2(1);
3662 l_order_type_name VARCHAR2(30);
3663 l_flow_status VARCHAR2(80);
3664 l_transaction_phase_code VARCHAR2(1);
3665 l_cnt NUMBER;
3666
3667 CURSOR cur_transaction_code IS
3668 SELECT transaction_phase_code
3669 FROM oe_order_headers
3670 WHERE header_id=p_header_id;
3671
3672 BEGIN
3673
3674 -- Setting a Multiple Org access.
3675 mo_global.init('ONT');
3676
3677 --Quote purge changes.To Select Transaction Phase code
3678
3679 OPEN cur_transaction_code ;
3680 FETCH cur_transaction_code INTO l_transaction_phase_code;
3681
3682
3683 IF (cur_transaction_code%NOTFOUND) THEN
3684
3685 CLOSE cur_transaction_code;
3686
3687 IF (p_order_number IS NOT NULL) THEN
3688 FND_MESSAGE.SET_NAME('ONT','ONT_ORDER_ALREADY_PURGED');
3689 FND_MESSAGE.SET_TOKEN('ORDER', p_order_number);
3690 ELSE
3691 FND_MESSAGE.SET_NAME('ONT','ONT_ORDER_ALREADY_PURGED');
3692 FND_MESSAGE.SET_TOKEN('ORDER', p_quote_number);
3693 END IF;
3694
3695 l_temp_mesg := FND_MESSAGE.GET_ENCODED;
3696 FND_MESSAGE.SET_ENCODED(l_temp_mesg);
3697 l_error_message := FND_MESSAGE.GET;
3698 p_error_message := l_error_message;
3699 p_is_purgable := 'N';
3700 RETURN;
3701 ELSE
3702 CLOSE cur_transaction_code;
3703 END IF;
3704
3705
3706
3707
3708 IF OE_CODE_CONTROL.Get_Code_Release_Level >= '110510'
3709 AND nvl(l_transaction_phase_code,'F')='N' THEN
3710
3711 IF l_return_status = FND_API.G_TRUE THEN
3712
3713 FND_MESSAGE.SET_NAME('ONT','OE_PUR_OPEN_QUOTE');
3714 l_temp_mesg := FND_MESSAGE.GET_ENCODED;
3715 FND_MESSAGE.SET_ENCODED(l_temp_mesg);
3716 l_error_message := FND_MESSAGE.GET;
3717 l_return_status := OE_ORDER_PURGE_PVT.Check_Open_Quotes(p_header_id);
3718
3719 END IF;
3720
3721 ELSE
3722
3723 IF l_return_status = FND_API.G_TRUE THEN
3724 FND_MESSAGE.SET_NAME('ONT','OE_PUR_OPEN_ORDER');
3725 l_temp_mesg := FND_MESSAGE.GET_ENCODED;
3726 FND_MESSAGE.SET_ENCODED(l_temp_mesg);
3727 l_error_message := FND_MESSAGE.GET;
3728 l_return_status := OE_ORDER_PURGE_PVT.Check_Open_Orders( p_header_id);
3729 END IF;
3730
3731 IF l_return_status = FND_API.G_TRUE THEN
3732
3733 SELECT otl.name
3734 INTO l_order_type_name
3735 FROM oe_transaction_types_tl otl,
3736 oe_order_headers ooh
3737 WHERE otl.language = (SELECT language_code
3738 FROM fnd_languages
3739 WHERE installed_flag = 'B')
3740 AND otl.transaction_type_id = ooh.order_type_id
3741 AND ooh.header_id = p_header_id;
3742
3743 FND_MESSAGE.SET_NAME('ONT','OE_PUR_OPEN_INVOICES');
3744 l_temp_mesg := FND_MESSAGE.GET_ENCODED;
3745 FND_MESSAGE.SET_ENCODED(l_temp_mesg);
3746 l_error_message := FND_MESSAGE.GET;
3747 l_return_status := OE_ORDER_PURGE_PVT.check_open_invoiced_orders
3748 ( TO_CHAR(p_order_number), l_order_type_name );
3749 END IF;
3750
3751
3752 IF l_return_status = FND_API.G_TRUE THEN
3753 FND_MESSAGE.SET_NAME('ONT','OE_PUR_OPEN_RETURNS');
3754 l_temp_mesg := FND_MESSAGE.GET_ENCODED;
3755 FND_MESSAGE.SET_ENCODED(l_temp_mesg);
3756 l_error_message := FND_MESSAGE.GET;
3757 l_return_status := OE_ORDER_PURGE_PVT.check_open_returns
3758 (p_order_number, p_order_type_name);
3759 END IF;
3760
3761
3762 IF l_return_status = FND_API.G_TRUE THEN
3763 Check_Open_RMA_Receipts(p_header_id,l_return_status, l_error_message);
3764 END IF;
3765
3766
3767
3768 IF l_return_status = FND_API.G_TRUE THEN
3769 FND_MESSAGE.SET_NAME('ONT','OE_PUR_OPEN_DELIVERIES');
3770 l_temp_mesg := FND_MESSAGE.GET_ENCODED;
3771 FND_MESSAGE.SET_ENCODED(l_temp_mesg);
3772 l_error_message := FND_MESSAGE.GET;
3773
3774 SELECT count(*)
3775 INTO l_cnt
3776 FROM wsh_delivery_details dd,
3777 oe_order_lines l
3778 WHERE l.header_id = p_header_id
3779 AND dd.source_line_id = l.line_id
3780 AND dd.org_id = l.org_id
3781 AND dd.source_code = 'OE'
3782 AND (nvl(dd.released_status, 'N') not in ('C', 'D') or
3783 ( dd.released_status = 'C' and
3784 ( nvl(dd.inv_interfaced_flag, 'N') in ( 'N','P') or
3785 nvl(dd.oe_interfaced_flag, 'N') in ( 'N','P')
3786 )
3787 )
3788 );
3789 IF l_cnt > 0 THEN
3790 l_return_status := FND_API.G_FALSE;
3791 END IF;
3792 END IF;
3793
3794
3795 IF l_return_status = FND_API.G_TRUE THEN
3796
3797 IF PO_CODE_RELEASE_GRP.Current_Release >=
3798 PO_CODE_RELEASE_GRP.PRC_11i_Family_Pack_J AND
3799 OE_CODE_CONTROL.Code_Release_Level >= '110510' THEN
3800
3801 l_return_status :=
3802 OE_ORDER_PURGE_PVT.Check_Open_PO_Reqs_Dropship
3803 (p_header_id => p_header_id );
3804
3805 IF l_return_status = FND_API.G_FALSE THEN
3806
3807 FND_MESSAGE.SET_NAME('ONT','OE_PURGE_OPEN_PO_REQ');
3808 l_temp_mesg := FND_MESSAGE.GET_ENCODED;
3809 FND_MESSAGE.SET_ENCODED(l_temp_mesg);
3810 l_error_message := FND_MESSAGE.GET;
3811
3812 END IF;
3813
3814 END IF;
3815 END IF;
3816
3817 END IF;
3818
3819 IF l_return_status = FND_API.G_TRUE THEN
3820 p_error_message := NULL;
3821 p_is_purgable := 'Y' ;
3822
3823 ELSE
3824 p_error_message := l_error_message;
3825 p_is_purgable := 'N';
3826 END IF;
3827
3828 END check_is_purgable;
3829
3830
3831 END OE_Order_Purge_PVT;