[Home] [Help]
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;
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
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);
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:
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
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
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),
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,
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
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;
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;
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
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
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
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),
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: (
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,
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
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'
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:
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
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:
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:
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;
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
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));
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;
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
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;
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:
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);
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:
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
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
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: --
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;
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);
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: --
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:
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;
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
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,
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.');
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,
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
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
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;
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:
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
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:
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
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;
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:
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
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:
2197: l_msg_data VARCHAR2(2000);
2198:
2199: BEGIN
2200:
2201: oe_debug_pub.add('Entering OE_ORDER_PURGE_PVT.OE_Purge_Headers : '||to_char(p_header_id));
2202:
2203: --16193599 Audit History Purge Start
2204: OPEN c_lock_audit_hist_ent1;
2205: CLOSE c_lock_audit_hist_ent1;
2312: p_entity_id => p_header_id,
2313: x_return_status => l_return_status
2314: );
2315: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2316: oe_debug_pub.add('Attachments delete failed : ');
2317: x_return_status := l_return_status;
2318: RETURN;
2319: END IF;
2320:
2324: END IF;
2325:
2326: -- Delete record from CTO tables
2327:
2328: oe_debug_pub.add('Calling CTOs API ',3);
2329:
2330: IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2331: CTO_ORDER_PURGE.Cto_Purge_Tables
2332: (
2334: x_error_msg => l_error_msg,
2335: x_return_status => l_return_status
2336: );
2337:
2338: oe_debug_pub.add('Return from CTOs API : '||l_return_status,3);
2339:
2340: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2341:
2342: x_return_status := l_return_status;
2376: OPEN c_lock_header_hist;
2377: CLOSE c_lock_header_hist;
2378: DELETE FROM oe_order_header_history WHERE header_id = p_header_id;
2379:
2380: OE_DEBUG_PUB.Add('After Deleting header history='|| to_char(p_header_id));
2381:
2382: OPEN c_lock_price_adj_hist;
2383: CLOSE c_lock_price_adj_hist;
2384: DELETE FROM oe_price_adjs_history WHERE header_id = p_header_id;
2382: OPEN c_lock_price_adj_hist;
2383: CLOSE c_lock_price_adj_hist;
2384: DELETE FROM oe_price_adjs_history WHERE header_id = p_header_id;
2385:
2386: OE_DEBUG_PUB.Add('After Deleting Price Adj history='|| to_char(p_header_id));
2387:
2388: OPEN c_lock_sales_credit_hist;
2389: CLOSE c_lock_sales_credit_hist;
2390: DELETE FROM oe_sales_credit_history WHERE header_id = p_header_id;
2388: OPEN c_lock_sales_credit_hist;
2389: CLOSE c_lock_sales_credit_hist;
2390: DELETE FROM oe_sales_credit_history WHERE header_id = p_header_id;
2391:
2392: OE_DEBUG_PUB.Add('After Deleting Sales Credit history='|| to_char(p_header_id));
2393:
2394: OPEN c_purge_set_history; --bug#5631508
2395: CLOSE c_purge_set_history;
2396: DELETE FROM oe_sets_history WHERE header_id = p_header_id;
2412: , x_msg_count => l_msg_count
2413: , x_msg_data => l_msg_data
2414: );
2415:
2416: OE_DEBUG_PUB.Add('Purged the Articles for Header:'||to_char(p_header_id));
2417:
2418: END IF;
2419:
2420: END IF;
2419:
2420: END IF;
2421: DELETE FROM oe_order_headers
2422: WHERE header_id = p_header_id;
2423: oe_debug_pub.add('deleted header='|| to_char(p_header_id));
2424:
2425: oe_debug_pub.add('Exiting OE_ORDER_PURGE_PVT.OE_Purge_Headers : ');
2426:
2427: x_return_status := FND_API.G_RET_STS_SUCCESS;
2421: DELETE FROM oe_order_headers
2422: WHERE header_id = p_header_id;
2423: oe_debug_pub.add('deleted header='|| to_char(p_header_id));
2424:
2425: oe_debug_pub.add('Exiting OE_ORDER_PURGE_PVT.OE_Purge_Headers : ');
2426:
2427: x_return_status := FND_API.G_RET_STS_SUCCESS;
2428:
2429: EXCEPTION -- Exception handler to record error
2492: WHERE ds.header_id = p_header_id;
2493:
2494: BEGIN
2495:
2496: oe_debug_pub.add('Entering OE_ORDER_PURGE_PVT.OE_Purge_Lines : '||to_char(p_header_id));
2497:
2498:
2499: OPEN c_purge_lines;
2500: LOOP
2519: ( p_config_hdr_id => l_config_header_id,
2520: p_config_rev_nbr => l_config_rev_nbr,
2521: x_return_status => l_return_status );
2522:
2523: OE_DEBUG_PUB.Add('After Calling Delete_Config',1);
2524: END IF;
2525:
2526:
2527: IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2575: RETURN;
2576: END IF;
2577:
2578:
2579: oe_debug_pub.add('Before RMA : ',1);
2580:
2581: IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2582: IF l_line_category_code = 'RETURN' THEN
2583:
2593: x_return_status := l_return_status;
2594: RETURN;
2595: END IF;
2596:
2597: oe_debug_pub.add('Before RMA_LOT : ',1);
2598:
2599: IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2600: IF l_line_category_code = 'RETURN' THEN
2601:
2613:
2614:
2615: -- Delete the attachments.
2616:
2617: oe_debug_pub.add('Before before attach : ',1);
2618:
2619: IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2620: OE_Atchmt_Util.Delete_Attachments
2621: (
2623: p_entity_id => l_line_id,
2624: x_return_status => l_return_status
2625: );
2626: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2627: oe_debug_pub.add('Attachments delete failed : ');
2628: x_return_status := l_return_status;
2629: RETURN;
2630: END IF;
2631:
2634: RETURN;
2635: END IF;
2636:
2637: -- Delete the Line work flow.
2638: oe_debug_pub.add('Before workflow : ',1);
2639: BEGIN
2640:
2641:
2642: OE_Order_WF_Util.Delete_Row
2652: END;
2653:
2654: -- Purge the OTA lines. Currently, OTA lines are identified by UOM.
2655:
2656: oe_debug_pub.add('Before ota : ',1);
2657: l_is_ota_line := OE_OTA_UTIL.Is_OTA_Line(l_order_quantity_uom);
2658: IF (l_is_ota_line) THEN
2659: OE_OTA_UTIL.Notify_OTA
2660: (p_line_id => l_line_id,
2673: OPEN c_purge_lines_hist;
2674: CLOSE c_purge_lines_hist;
2675: DELETE FROM oe_order_lines_history WHERE header_id = p_header_id;
2676:
2677: OE_DEBUG_PUB.Add('Before line delete : ',1);
2678:
2679: DELETE FROM oe_order_lines
2680: WHERE line_id = l_line_id;
2681:
2687: IF PO_CODE_RELEASE_GRP.Current_Release >=
2688: PO_CODE_RELEASE_GRP.PRC_11i_Family_Pack_J AND
2689: OE_CODE_CONTROL.Code_Release_Level >= '110510' THEN
2690:
2691: OE_DEBUG_PUB.Add('Purge Externally Sourced Lines');
2692:
2693: FOR c_ds_loc in c_ds_line_loc LOOP
2694:
2695: IF c_ds_loc.line_location_id IS NOT NULL THEN
2692:
2693: FOR c_ds_loc in c_ds_line_loc LOOP
2694:
2695: IF c_ds_loc.line_location_id IS NOT NULL THEN
2696: OE_DEBUG_PUB.Add('Purge Line Loc : '||c_ds_loc.line_location_id);
2697: l_entity_id_tbl.extend(1);
2698: l_entity_id_tbl(I) := c_ds_loc.line_location_id;
2699: I := I + 1;
2700: END IF;
2704: CLOSE c_purge_ds;
2705:
2706: DELETE FROM oe_drop_ship_sources where header_id = p_header_id;
2707:
2708: OE_DEBUG_PUB.Add('Before Calling PO Purge API ');
2709:
2710: PO_OM_INTEGRATION_GRP.Purge
2711: ( p_api_version => 1.0
2712: ,p_init_msg_list => FND_API.G_FALSE
2717: ,p_entity => 'PO_LINE_LOCATIONS'
2718: ,p_entity_id_tbl => l_entity_id_tbl
2719: );
2720:
2721: OE_DEBUG_PUB.Add('After Calling PO Purge API '||x_return_status);
2722:
2723: IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2724: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2725: ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
2723: IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2724: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2725: ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
2726: OE_MSG_PUB.Add_Text(l_msg_data);
2727: OE_DEBUG_PUB.Add('Errors from Purge: '||l_msg_data,2) ;
2728: RAISE FND_API.G_EXC_ERROR;
2729: END IF;
2730: END IF;
2731:
2729: END IF;
2730: END IF;
2731:
2732:
2733: oe_debug_pub.add('Number of lines deleted : '||to_char(c_purge_lines%ROWCOUNT),1);
2734: CLOSE c_purge_lines;
2735:
2736: oe_debug_pub.add('deleted lines for header='|| to_char(p_header_id));
2737: oe_debug_pub.add('Exiting OE_ORDER_PURGE_PVT.OE_Purge_Lines : ');
2732:
2733: oe_debug_pub.add('Number of lines deleted : '||to_char(c_purge_lines%ROWCOUNT),1);
2734: CLOSE c_purge_lines;
2735:
2736: oe_debug_pub.add('deleted lines for header='|| to_char(p_header_id));
2737: oe_debug_pub.add('Exiting OE_ORDER_PURGE_PVT.OE_Purge_Lines : ');
2738:
2739: x_return_status := FND_API.G_RET_STS_SUCCESS;
2740:
2733: oe_debug_pub.add('Number of lines deleted : '||to_char(c_purge_lines%ROWCOUNT),1);
2734: CLOSE c_purge_lines;
2735:
2736: oe_debug_pub.add('deleted lines for header='|| to_char(p_header_id));
2737: oe_debug_pub.add('Exiting OE_ORDER_PURGE_PVT.OE_Purge_Lines : ');
2738:
2739: x_return_status := FND_API.G_RET_STS_SUCCESS;
2740:
2741: EXCEPTION -- Exception handler to record error
2795: l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
2796:
2797: BEGIN
2798:
2799: oe_debug_pub.add('Entering OE_ORDER_PURGE_PVT.OE_Purge_Header_Adj : '||to_char(p_header_id));
2800:
2801: OPEN c_header_adj;
2802:
2803: LOOP
2802:
2803: LOOP
2804:
2805: FETCH c_header_adj INTO l_price_adjustment_id;
2806: oe_debug_pub.add('price adjust ment : '||to_char(l_price_adjustment_id));
2807: EXIT WHEN c_header_adj%NOTFOUND -- end of fetch
2808: OR c_header_adj%NOTFOUND IS NULL; -- empty cursor
2809:
2810: -- Lock the price adjustments record.
2831: WHERE price_adj_attrib_id = l_price_adj_attrib_id;
2832:
2833: END LOOP;
2834:
2835: oe_debug_pub.add('Number of price_adj deleted : '||to_char(c_price_adj%ROWCOUNT),1);
2836: CLOSE c_price_adj;
2837:
2838: -- Delete from price_adj_assocs.
2839: OPEN c_price_adj_assocs(l_price_adjustment_id);
2840: LOOP
2841: FETCH c_price_adj_assocs INTO l_price_adj_assoc_id;
2842: EXIT WHEN c_price_adj_assocs%NOTFOUND -- end of fetch
2843: OR c_price_adj_assocs%NOTFOUND IS NULL; -- empty cursor
2844: oe_debug_pub.add('price adjust ment 4 : '||to_char(l_price_adjustment_id));
2845:
2846: SELECT price_adj_assoc_id
2847: INTO l_lock_price_adj_assoc_id
2848: FROM oe_price_adj_assocs
2853: WHERE price_adj_assoc_id = l_lock_price_adj_assoc_id;
2854:
2855: END LOOP;
2856:
2857: oe_debug_pub.add('Number of price_adj_assocs deleted : '||to_char(c_price_adj_assocs%ROWCOUNT),1);
2858: CLOSE c_price_adj_assocs;
2859:
2860:
2861: DELETE FROM OE_PRICE_ADJUSTMENTS
2862: WHERE price_adjustment_id = l_lock_adjustment_id;
2863:
2864: END LOOP;
2865:
2866: oe_debug_pub.add('Number of price adjustments deleted : '||to_char(c_header_adj%ROWCOUNT),1);
2867: CLOSE c_header_adj;
2868:
2869: oe_debug_pub.add('Exiting OE_ORDER_PURGE_PVT.OE_Purge_Header_Adj : ');
2870:
2865:
2866: oe_debug_pub.add('Number of price adjustments deleted : '||to_char(c_header_adj%ROWCOUNT),1);
2867: CLOSE c_header_adj;
2868:
2869: oe_debug_pub.add('Exiting OE_ORDER_PURGE_PVT.OE_Purge_Header_Adj : ');
2870:
2871: RETURN FND_API.G_RET_STS_SUCCESS;
2872:
2873: EXCEPTION -- Exception handler to record error
2929:
2930:
2931: BEGIN
2932:
2933: oe_debug_pub.add('Entering OE_ORDER_PURGE_PVT.OE_Purge_Line_Adj : '||to_char(p_line_id));
2934: OPEN c_line_adj;
2935:
2936: LOOP
2937:
2965: WHERE price_adj_attrib_id = l_price_adj_attrib_id;
2966:
2967: END LOOP;
2968:
2969: oe_debug_pub.add('Number of price_adj deleted : '||to_char(c_price_adj%ROWCOUNT),1);
2970: CLOSE c_price_adj;
2971:
2972: -- Commented for Bug # 4701261
2973:
3000: WHERE price_adj_assoc_id = l_lock_price_adj_assoc_id;
3001:
3002: END LOOP;
3003:
3004: oe_debug_pub.add('Number of price_adj_assocs deleted : '||to_char(c_price_adj_assocs%ROWCOUNT),1);
3005: CLOSE c_price_adj_assocs;
3006:
3007: DELETE FROM OE_PRICE_ADJUSTMENTS
3008: WHERE price_adjustment_id = l_lock_adjustment_id;
3008: WHERE price_adjustment_id = l_lock_adjustment_id;
3009:
3010: END LOOP;
3011:
3012: oe_debug_pub.add('Number of price adjustments deleted : '||to_char(c_line_adj%ROWCOUNT),1);
3013: CLOSE c_line_adj;
3014: oe_debug_pub.add('Exiting OE_ORDER_PURGE_PVT.OE_Purge_Line_Adj : ');
3015:
3016: RETURN FND_API.G_RET_STS_SUCCESS;
3010: END LOOP;
3011:
3012: oe_debug_pub.add('Number of price adjustments deleted : '||to_char(c_line_adj%ROWCOUNT),1);
3013: CLOSE c_line_adj;
3014: oe_debug_pub.add('Exiting OE_ORDER_PURGE_PVT.OE_Purge_Line_Adj : ');
3015:
3016: RETURN FND_API.G_RET_STS_SUCCESS;
3017:
3018: EXCEPTION -- Exception handler to record error
3049: l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
3050:
3051: BEGIN
3052:
3053: oe_debug_pub.add('Entering OE_ORDER_PURGE_PVT.OE_Purge_Price_Attribs : '||to_char(p_header_id));
3054: OPEN c_order_price_attribs;
3055:
3056: LOOP
3057:
3070: WHERE ORDER_PRICE_ATTRIB_ID = l_order_price_attrib_id;
3071:
3072: END LOOP;
3073:
3074: oe_debug_pub.add('Number of order price attribs deleted : '||to_char(c_order_price_attribs%ROWCOUNT),1);
3075: CLOSE c_order_price_attribs;
3076:
3077: oe_debug_pub.add('Exiting OE_ORDER_PURGE_PVT.OE_Purge_Price_Attribs : ');
3078:
3073:
3074: oe_debug_pub.add('Number of order price attribs deleted : '||to_char(c_order_price_attribs%ROWCOUNT),1);
3075: CLOSE c_order_price_attribs;
3076:
3077: oe_debug_pub.add('Exiting OE_ORDER_PURGE_PVT.OE_Purge_Price_Attribs : ');
3078:
3079: RETURN FND_API.G_RET_STS_SUCCESS;
3080:
3081: EXCEPTION -- Exception handler to record error
3110: l_lock_sales_credit_id NUMBER;
3111: l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
3112:
3113: BEGIN
3114: oe_debug_pub.add('Entering OE_ORDER_PURGE_PVT.OE_Purge_Order_Sales_Credits : '||to_char(p_header_id));
3115:
3116: OPEN c_order_sales_credits;
3117:
3118: LOOP
3132: WHERE SALES_CREDIT_ID = l_sales_credit_id;
3133:
3134: END LOOP;
3135:
3136: oe_debug_pub.add('Number of order sales credit deleted : '||to_char(c_order_sales_credits%ROWCOUNT),1);
3137: CLOSE c_order_sales_credits;
3138:
3139: oe_debug_pub.add('Exiting OE_ORDER_PURGE_PVT.OE_Purge_Order_Sales_Credits : ');
3140: RETURN FND_API.G_RET_STS_SUCCESS;
3135:
3136: oe_debug_pub.add('Number of order sales credit deleted : '||to_char(c_order_sales_credits%ROWCOUNT),1);
3137: CLOSE c_order_sales_credits;
3138:
3139: oe_debug_pub.add('Exiting OE_ORDER_PURGE_PVT.OE_Purge_Order_Sales_Credits : ');
3140: RETURN FND_API.G_RET_STS_SUCCESS;
3141:
3142: EXCEPTION -- Exception handler to record error
3143:
3173: l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
3174:
3175: BEGIN
3176:
3177: oe_debug_pub.add('Entering OE_ORDER_PURGE_PVT.OE_Purge_Line_Sales_Credits : '||to_char(p_line_id));
3178: OPEN c_line_sales_credits;
3179:
3180: LOOP
3181:
3194: WHERE SALES_CREDIT_ID = l_sales_credit_id;
3195:
3196: END LOOP;
3197:
3198: oe_debug_pub.add('Number of line sales credit deleted : '||to_char(c_line_sales_credits%ROWCOUNT),1);
3199: CLOSE c_line_sales_credits;
3200:
3201: oe_debug_pub.add('Exiting OE_ORDER_PURGE_PVT.OE_Purge_Line_Sales_Credits : ');
3202: RETURN FND_API.G_RET_STS_SUCCESS;
3197:
3198: oe_debug_pub.add('Number of line sales credit deleted : '||to_char(c_line_sales_credits%ROWCOUNT),1);
3199: CLOSE c_line_sales_credits;
3200:
3201: oe_debug_pub.add('Exiting OE_ORDER_PURGE_PVT.OE_Purge_Line_Sales_Credits : ');
3202: RETURN FND_API.G_RET_STS_SUCCESS;
3203:
3204: EXCEPTION -- Exception handler to record error
3205:
3234: l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
3235:
3236: BEGIN
3237:
3238: oe_debug_pub.add('Entering OE_ORDER_PURGE_PVT.OE_Purge_Order_Sets : '||to_char(p_header_id));
3239: OPEN c_order_sets;
3240:
3241: LOOP
3242:
3255: WHERE SET_ID = l_set_id;
3256:
3257: END LOOP;
3258:
3259: oe_debug_pub.add('Number of order sets deleted : '||to_char(c_order_sets%ROWCOUNT),1);
3260: CLOSE c_order_sets;
3261:
3262: oe_debug_pub.add('Exiting OE_ORDER_PURGE_PVT.OE_Purge_Order_Sets : ');
3263: RETURN FND_API.G_RET_STS_SUCCESS;
3258:
3259: oe_debug_pub.add('Number of order sets deleted : '||to_char(c_order_sets%ROWCOUNT),1);
3260: CLOSE c_order_sets;
3261:
3262: oe_debug_pub.add('Exiting OE_ORDER_PURGE_PVT.OE_Purge_Order_Sets : ');
3263: RETURN FND_API.G_RET_STS_SUCCESS;
3264:
3265: EXCEPTION -- Exception handler to record error
3266:
3297: l_lock_set_id NUMBER;
3298: l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
3299:
3300: BEGIN
3301: oe_debug_pub.add('Entering OE_ORDER_PURGE_PVT.OE_Purge_Line_Sets : '||to_char(p_line_id));
3302:
3303: OPEN c_line_sets;
3304:
3305: LOOP
3321: AND LINE_ID = l_line_id;
3322:
3323: END LOOP;
3324:
3325: oe_debug_pub.add('Number of Line sets deleted : '||to_char(c_line_sets%ROWCOUNT),1);
3326: CLOSE c_line_sets;
3327:
3328: oe_debug_pub.add('Exiting OE_ORDER_PURGE_PVT.OE_Purge_Line_Sets : ');
3329: RETURN FND_API.G_RET_STS_SUCCESS;
3324:
3325: oe_debug_pub.add('Number of Line sets deleted : '||to_char(c_line_sets%ROWCOUNT),1);
3326: CLOSE c_line_sets;
3327:
3328: oe_debug_pub.add('Exiting OE_ORDER_PURGE_PVT.OE_Purge_Line_Sets : ');
3329: RETURN FND_API.G_RET_STS_SUCCESS;
3330:
3331: EXCEPTION -- Exception handler to record error
3332:
3379: l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
3380: l_count NUMBER := 0; -- bug 6148214
3381:
3382: BEGIN
3383: oe_debug_pub.add('Entering OE_ORDER_PURGE_PVT.OE_Purge_Order_Hold : '||to_char(p_header_id));
3384:
3385: OPEN c_order_holds;
3386:
3387: LOOP
3417: DELETE FROM OE_HOLD_SOURCES
3418: WHERE HOLD_SOURCE_ID = l_hold_source_id;
3419:
3420: END LOOP;
3421: oe_debug_pub.add('Number of hold sources deleted : '||to_char(c_hold_sources%ROWCOUNT),1);
3422: CLOSE c_hold_sources;
3423:
3424: OPEN c_hold_releases(l_hold_release_id);
3425: LOOP
3441: DELETE FROM OE_HOLD_RELEASES
3442: WHERE HOLD_RELEASE_ID = l_hold_release_id;
3443: END IF;
3444: END LOOP;
3445: oe_debug_pub.add('Number of hold releases deleted : '||to_char(c_hold_releases%ROWCOUNT),1);
3446: CLOSE c_hold_releases;
3447:
3448: DELETE FROM OE_ORDER_HOLDS
3449: WHERE ORDER_HOLD_ID = l_order_hold_id;
3449: WHERE ORDER_HOLD_ID = l_order_hold_id;
3450:
3451: END LOOP;
3452:
3453: oe_debug_pub.add('Number of order holds deleted : '||to_char(c_order_holds%ROWCOUNT),1);
3454: CLOSE c_order_holds;
3455:
3456: oe_debug_pub.add('Exiting OE_ORDER_PURGE_PVT.OE_Purge_Order_Holds : ');
3457: RETURN FND_API.G_RET_STS_SUCCESS;
3452:
3453: oe_debug_pub.add('Number of order holds deleted : '||to_char(c_order_holds%ROWCOUNT),1);
3454: CLOSE c_order_holds;
3455:
3456: oe_debug_pub.add('Exiting OE_ORDER_PURGE_PVT.OE_Purge_Order_Holds : ');
3457: RETURN FND_API.G_RET_STS_SUCCESS;
3458:
3459: EXCEPTION -- Exception handler to record error
3460:
3479: IS
3480: l_return_status VARCHAR2(10);
3481: l_message VARCHAR2(2000);
3482: BEGIN
3483: oe_debug_pub.add('Entering OE_ORDER_PURGE_PVT.OE_Purge_RMA_Line_Receipts: '||to_char(p_line_id));
3484:
3485: RCV_RMA_RCPT_PURGE.Purge_Receipts(p_line_id,l_return_status,l_message);
3486:
3487: IF l_return_status = 'FALSE' THEN
3487: IF l_return_status = 'FALSE' THEN
3488: RETURN FND_API.G_RET_STS_ERROR;
3489: END IF;
3490:
3491: oe_debug_pub.add('Exiting OE_ORDER_PURGE_PVT.OE_Purge_RMA_Line_Receipts.');
3492: RETURN FND_API.G_RET_STS_SUCCESS;
3493:
3494: EXCEPTION -- Exception handler to record error
3495: WHEN OTHERS THEN
3528: l_lock_lot_serial_id NUMBER;
3529: l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
3530: l_message VARCHAR2(2000);
3531: BEGIN
3532: oe_debug_pub.add('Entering OE_ORDER_PURGE_PVT.OE_Purge_RMA_Line_Lot_Srl: '||to_char(p_line_id));
3533:
3534:
3535: OPEN c_line_lot_serials;
3536:
3552: END LOOP;
3553:
3554: CLOSE c_line_lot_serials;
3555:
3556: oe_debug_pub.add('Exiting OE_ORDER_PURGE_PVT.OE_Purge_RMA_Line_Lot_Srl.');
3557: RETURN FND_API.G_RET_STS_SUCCESS;
3558:
3559: EXCEPTION -- Exception handler to record error
3560: WHEN OTHERS THEN
3580: IS
3581:
3582: BEGIN
3583:
3584: oe_debug_pub.add('Error Message : '||p_error_message);
3585: UPDATE oe_purge_orders
3586: SET ERROR_TEXT = p_error_message
3587: ,IS_PURGED = 'N'
3588: WHERE purge_set_id = p_purge_set_id