1 PACKAGE BODY OE_ORDER_IMPORT_UTIL_PVT AS
2 /* $Header: OEXVIMUB.pls 120.1 2005/08/05 15:29:37 sphatarp noship $ */
3
4 /* ---------------------------------------------------------------
5 -- Start of Comments
6 -- API name OE_ORDER_IMPORT_UTIL_PVT
7 -- Type Private
8 -- Function
9 -- Pre-reqs
10 -- Parameters
11 -- Version Current version = 1.0
12 -- Initial version = 1.0
13 -- Notes
14 --
15 -- End of Comments
16 ------------------------------------------------------------------
17 */
18
19 /* -----------------------------------------------------------
20 Procedure: Delete_Order
21 -----------------------------------------------------------
22 */
23 PROCEDURE Delete_Order(
24 p_request_id IN NUMBER
25 ,p_order_source_id IN NUMBER
26 ,p_orig_sys_document_ref IN VARCHAR2
27 ,p_sold_to_org_id IN NUMBER
28 ,p_sold_to_org IN VARCHAR2
29 ,p_change_sequence IN VARCHAR2
30 ,p_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
31 ) IS
32 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Order';
33 l_exists_reject_line VARCHAR2(1);
34 BEGIN
35
36 DELETE FROM oe_actions_interface
37 WHERE order_source_id = p_order_source_id
38 AND orig_sys_document_ref = p_orig_sys_document_ref
39 AND nvl( sold_to_org_id, FND_API.G_MISS_NUM)
40 = nvl(p_sold_to_org_id, FND_API.G_MISS_NUM)
41 AND nvl( sold_to_org, FND_API.G_MISS_CHAR)
42 = nvl(p_sold_to_org, FND_API.G_MISS_CHAR)
43 AND nvl( change_sequence,FND_API.G_MISS_CHAR)
44 = nvl(p_change_sequence,FND_API.G_MISS_CHAR)
45 AND nvl( request_id, FND_API.G_MISS_NUM)
46 = nvl(p_request_id, FND_API.G_MISS_NUM);
47
48 DELETE FROM oe_reservtns_interface
49 WHERE order_source_id = p_order_source_id
50 AND orig_sys_document_ref = p_orig_sys_document_ref
51 AND nvl( sold_to_org_id, FND_API.G_MISS_NUM)
52 = nvl(p_sold_to_org_id, FND_API.G_MISS_NUM)
53 AND nvl( sold_to_org, FND_API.G_MISS_CHAR)
54 = nvl(p_sold_to_org, FND_API.G_MISS_CHAR)
55 AND nvl( change_sequence,FND_API.G_MISS_CHAR)
56 = nvl(p_change_sequence,FND_API.G_MISS_CHAR)
57 AND nvl( request_id, FND_API.G_MISS_NUM)
58 = nvl(p_request_id, FND_API.G_MISS_NUM);
59
60 DELETE FROM oe_lotserials_interface
61 WHERE order_source_id = p_order_source_id
62 AND orig_sys_document_ref = p_orig_sys_document_ref
63 AND nvl( sold_to_org_id, FND_API.G_MISS_NUM)
64 = nvl(p_sold_to_org_id, FND_API.G_MISS_NUM)
65 AND nvl( sold_to_org, FND_API.G_MISS_CHAR)
66 = nvl(p_sold_to_org, FND_API.G_MISS_CHAR)
67 AND nvl( change_sequence,FND_API.G_MISS_CHAR)
68 = nvl(p_change_sequence,FND_API.G_MISS_CHAR)
69 AND nvl( request_id, FND_API.G_MISS_NUM)
70 = nvl(p_request_id, FND_API.G_MISS_NUM);
71
72 DELETE FROM oe_credits_interface
73 WHERE order_source_id = p_order_source_id
74 AND orig_sys_document_ref = p_orig_sys_document_ref
75 AND nvl( sold_to_org_id, FND_API.G_MISS_NUM)
76 = nvl(p_sold_to_org_id, FND_API.G_MISS_NUM)
77 AND nvl( sold_to_org, FND_API.G_MISS_CHAR)
78 = nvl(p_sold_to_org, FND_API.G_MISS_CHAR)
79 AND nvl( change_sequence,FND_API.G_MISS_CHAR)
80 = nvl(p_change_sequence,FND_API.G_MISS_CHAR)
81 AND nvl( request_id, FND_API.G_MISS_NUM)
82 = nvl(p_request_id, FND_API.G_MISS_NUM);
83
84 DELETE FROM oe_payments_interface
85 WHERE order_source_id = p_order_source_id
86 AND orig_sys_document_ref = p_orig_sys_document_ref
87 AND nvl( change_sequence,FND_API.G_MISS_CHAR)
88 = nvl(p_change_sequence,FND_API.G_MISS_CHAR)
89 AND nvl( request_id, FND_API.G_MISS_NUM)
90 = nvl(p_request_id, FND_API.G_MISS_NUM);
91
92 DELETE FROM oe_price_adjs_interface
93 WHERE order_source_id = p_order_source_id
94 AND orig_sys_document_ref = p_orig_sys_document_ref
95 AND nvl( sold_to_org_id, FND_API.G_MISS_NUM)
96 = nvl(p_sold_to_org_id, FND_API.G_MISS_NUM)
97 AND nvl( sold_to_org, FND_API.G_MISS_CHAR)
98 = nvl(p_sold_to_org, FND_API.G_MISS_CHAR)
99 AND nvl( change_sequence,FND_API.G_MISS_CHAR)
100 = nvl(p_change_sequence,FND_API.G_MISS_CHAR)
101 AND nvl( request_id, FND_API.G_MISS_NUM)
102 = nvl(p_request_id, FND_API.G_MISS_NUM);
103 /* 1433292 */
104 DELETE FROM oe_price_atts_interface
105 WHERE order_source_id = p_order_source_id
106 AND orig_sys_document_ref = p_orig_sys_document_ref
107 AND nvl( sold_to_org_id, FND_API.G_MISS_NUM)
108 = nvl(p_sold_to_org_id, FND_API.G_MISS_NUM)
109 AND nvl( sold_to_org, FND_API.G_MISS_CHAR)
110 = nvl(p_sold_to_org, FND_API.G_MISS_CHAR)
111 AND nvl( change_sequence,FND_API.G_MISS_CHAR)
112 = nvl(p_change_sequence,FND_API.G_MISS_CHAR)
113 AND nvl( request_id, FND_API.G_MISS_NUM)
114 = nvl(p_request_id, FND_API.G_MISS_NUM);
115
116 -- { Following changes are made to fix the bug 2922709
117 -- The issue is that the acknowledgment are not being send for the
118 -- Rejected Order and Line.
119 -- Fix is that we should not delete the lines if the
120 -- Reject_flag = 'Y' and if there is such line in that case
121 -- Order Header should also be not deleted.
122 -- And when the acknowledgments are being processed we should
123 -- use this information to create the data.
124
125 -- Check if there is any Line with the Reject_Flag = 'Y'
126 Begin
127
128 SELECT 1
129 INTO l_exists_reject_line
130 FROM oe_lines_interface
131 WHERE order_source_id = p_order_source_id
132 AND orig_sys_document_ref = p_orig_sys_document_ref
133 AND nvl( sold_to_org_id, FND_API.G_MISS_NUM)
134 = nvl(p_sold_to_org_id, FND_API.G_MISS_NUM)
135 AND nvl( sold_to_org, FND_API.G_MISS_CHAR)
136 = nvl(p_sold_to_org, FND_API.G_MISS_CHAR)
137 AND nvl( change_sequence,FND_API.G_MISS_CHAR)
138 = nvl(p_change_sequence,FND_API.G_MISS_CHAR)
139 AND nvl( request_id, FND_API.G_MISS_NUM)
140 = nvl(p_request_id, FND_API.G_MISS_NUM)
141 AND nvl(rejected_flag,'N')= 'Y'
142 AND rownum = 1;
143
144 DELETE FROM oe_lines_interface
145 WHERE order_source_id = p_order_source_id
146 AND orig_sys_document_ref = p_orig_sys_document_ref
147 AND nvl( sold_to_org_id, FND_API.G_MISS_NUM)
148 = nvl(p_sold_to_org_id, FND_API.G_MISS_NUM)
149 AND nvl( sold_to_org, FND_API.G_MISS_CHAR)
150 = nvl(p_sold_to_org, FND_API.G_MISS_CHAR)
151 AND nvl( change_sequence,FND_API.G_MISS_CHAR)
152 = nvl(p_change_sequence,FND_API.G_MISS_CHAR)
153 AND nvl( request_id, FND_API.G_MISS_NUM)
154 = nvl(p_request_id, FND_API.G_MISS_NUM)
155 AND nvl(rejected_flag,'N')= 'N';
156 EXCEPTION
157 When NO_DATA_FOUND Then
158
159 -- This is the OLD code path, let me put debug statement so
160 -- developer know this is executed..
161
162 oe_debug_pub.add('Delete_Order - No_DATA - Old Path of execution');
163
164 DELETE FROM oe_lines_interface
165 WHERE order_source_id = p_order_source_id
166 AND orig_sys_document_ref = p_orig_sys_document_ref
167 AND nvl( sold_to_org_id, FND_API.G_MISS_NUM)
168 = nvl(p_sold_to_org_id, FND_API.G_MISS_NUM)
169 AND nvl( sold_to_org, FND_API.G_MISS_CHAR)
170 = nvl(p_sold_to_org, FND_API.G_MISS_CHAR)
171 AND nvl( change_sequence,FND_API.G_MISS_CHAR)
172 = nvl(p_change_sequence,FND_API.G_MISS_CHAR)
173 AND nvl( request_id, FND_API.G_MISS_NUM)
174 = nvl(p_request_id, FND_API.G_MISS_NUM);
175
176 DELETE FROM oe_headers_interface
177 WHERE order_source_id = p_order_source_id
178 AND orig_sys_document_ref = p_orig_sys_document_ref
179 AND nvl( sold_to_org_id, FND_API.G_MISS_NUM)
180 = nvl(p_sold_to_org_id, FND_API.G_MISS_NUM)
181 AND nvl( sold_to_org, FND_API.G_MISS_CHAR)
182 = nvl(p_sold_to_org, FND_API.G_MISS_CHAR)
183 AND nvl( change_sequence,FND_API.G_MISS_CHAR)
184 = nvl(p_change_sequence,FND_API.G_MISS_CHAR)
185 AND nvl( request_id, FND_API.G_MISS_NUM)
186 = nvl(p_request_id, FND_API.G_MISS_NUM);
187
188
189 END;
190
191 -- End of fix the bug 2922709}
192
193 p_return_status := FND_API.G_RET_STS_SUCCESS;
194
195
196 EXCEPTION
197 WHEN OTHERS THEN
198 oe_debug_pub.add('Unexpected error: '||sqlerrm);
199
200 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
201 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
202 OE_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,'Delete_Order',substr(sqlerrm,1,2000));
203 END IF;
204
205 END Delete_Order;
206
207
208 /* -----------------------------------------------------------
209 Procedure: Delete_Messages
210 -----------------------------------------------------------
211 */
212 PROCEDURE Delete_Messages(
213 p_request_id IN NUMBER
214 ,p_order_source_id IN NUMBER
215 ,p_orig_sys_document_ref IN VARCHAR2
216 ,p_sold_to_org_id IN NUMBER
217 ,p_sold_to_org IN VARCHAR2
218 ,p_change_sequence IN VARCHAR2
219 ,p_org_id IN VARCHAR2
220 ,p_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
221 ) IS
222 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Messages';
223 l_request_id NUMBER := p_request_id;
224 l_order_source_id NUMBER := p_order_source_id;
225 l_orig_sys_document_ref VARCHAR2(50) := p_orig_sys_document_ref;
226 l_sold_to_org_id NUMBER := p_sold_to_org_id;
227 l_sold_to_org VARCHAR2(360) := p_sold_to_org;
228
229 l_change_sequence VARCHAR2(50) := p_change_sequence;
230 l_org_id Number := p_org_id;
231 BEGIN
232 --Commented this particular SQL statement as part of the
233 --fix for bug#2110646
234
235 /*DELETE FROM OE_PROCESSING_MSGS_VL
236 WHERE order_source_id = p_order_source_id
237 AND original_sys_document_ref = p_orig_sys_document_ref
238 AND nvl( change_sequence, FND_API.G_MISS_CHAR)
239 = nvl(p_change_sequence, FND_API.G_MISS_CHAR);*/
240
241
242 --Call to DELETE_OI_MESSAGE to delete the messages from oe_processing_msgs
243 --and oe_processing_msgs_tl.Fix for bug#2110646.
244 OE_MSG_PUB.DELETE_OI_MESSAGE(
245 p_order_source_id => l_order_source_id,
246 p_orig_sys_document_ref => l_orig_sys_document_ref,
247 p_change_sequence => l_change_sequence,
248 p_org_id => l_org_id
249 );
250
251
252
253 p_return_status := FND_API.G_RET_STS_SUCCESS;
254
255 EXCEPTION
256 WHEN OTHERS THEN
257 oe_debug_pub.add('Unexpected error: '||sqlerrm);
258
259 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
260 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
261 OE_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'Delete_Messages');
262 END IF;
263
264 END Delete_Messages;
265
266
267 /* -----------------------------------------------------------
268 Function: Get_Line_Index
269 -----------------------------------------------------------
270 */
271 FUNCTION Get_Line_Index(
272 p_line_tbl IN OE_Order_Pub.Line_Tbl_Type
273 ,p_orig_sys_line_ref IN VARCHAR2
274 ,p_orig_sys_shipment_ref IN VARCHAR2
275 )
276 RETURN NUMBER
277 IS
278 l_api_name CONSTANT VARCHAR2(30) := 'Get_Line_Index';
279 BEGIN
280
281 FOR i IN 1 .. p_line_tbl.count
282 LOOP
283 IF p_line_tbl(i).orig_sys_line_ref = p_orig_sys_line_ref AND
284 p_line_tbl(i).orig_sys_shipment_ref = p_orig_sys_shipment_ref
285 THEN
286 RETURN i;
287 END IF;
288 END LOOP;
289
290 END Get_Line_Index;
291
292
293 END OE_ORDER_IMPORT_UTIL_PVT;
[Home] [Help]