[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:
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;
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:
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: (
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;
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;
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;
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;
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;
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;
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
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
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
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:
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:
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: (
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:
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
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,
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:
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
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;
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
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
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:
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 : ');
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:
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
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
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.
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
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:
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
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:
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:
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;
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;
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
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:
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:
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
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
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;
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:
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:
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;
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:
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:
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;
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:
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
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;
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:
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
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
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;
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;
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:
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
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
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:
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
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