[Home] [Help]
PACKAGE BODY: APPS.ASO_TRADEIN_PVT
Source
1 PACKAGE BODY ASO_TRADEIN_PVT as
2 /* $Header: asovtrdb.pls 120.4 2005/09/02 13:09:51 hagrawal ship $ */
3 -- Start of Comments
4 -- Package name : ASO_TRADEIN_PVT
5 -- Purpose :
6 -- History :
7 -- 10/07/2002 hyang - 2611381, performance fix for 1158
8 -- 10/18/2002 hyang - 2633507, performance fix
9 -- NOTE :
10 -- End of Comments
11
12
13 G_PKG_NAME CONSTANT VARCHAR2(30) := 'ASO_TRADEIN_PVT';
14 G_FILE_NAME CONSTANT VARCHAR2(12) := 'asovtrdb.pls';
15
16
17 PROCEDURE Validate_Line_Tradein(
18 p_init_msg_list IN VARCHAR2,
19 p_qte_header_rec IN ASO_QUOTE_PUB.Qte_Header_Rec_Type,
20 P_Qte_Line_rec IN ASO_QUOTE_PUB.Qte_Line_Rec_Type,
21 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
22 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
23 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
24 IS
25
26 /* 2633507 - hyang: use mtl_system_items_b instead of vl */
27
28 CURSOR C_Validate_Item(l_inv_item_id NUMBER) IS
29 SELECT returnable_flag, customer_order_enabled_flag, serviceable_product_flag FROM MTL_SYSTEM_ITEMS_B
30 WHERE inventory_item_id = l_inv_item_id
31 AND organization_id = p_qte_line_rec.organization_id;
32
33 CURSOR C_inventory_item is
34 SELECT inventory_item_id, line_category_code from aso_quote_lines_all
35 where quote_line_id = p_qte_line_rec.quote_line_id;
36
37 CURSOR C_item_type IS
38 SELECT item_type_code FROM aso_quote_lines_all
39 WHERE quote_line_id = p_qte_line_rec.quote_line_id;
40
41 CURSOR C_Service_Available IS
42 SELECT count(related_quote_line_id) FROM aso_line_relationships
43 WHERE quote_line_id = p_qte_line_rec.quote_line_id
44 AND relationship_type_code = 'SERVICE';
45
46 CURSOR C_workflow IS
47 SELECT start_date_active, end_date_active
48 FROM OE_WF_LINE_ASSIGN_V
49 WHERE order_type_id = p_qte_header_rec.order_type_id
50 and line_type_id = p_qte_line_rec.order_line_type_id
51 and (trunc(sysdate) BETWEEN NVL(start_date_active, sysdate) AND
52 NVL(end_date_active, sysdate));
53
54 l_serviceable_product_flag VARCHAR2(1);
55 l_returnable_flag VARCHAR2(1);
56 l_order_line_type_id NUMBER;
57 l_line_category_code VARCHAR2(30);
58 l_inventory_item_id NUMBER;
59 l_customer_order_enabled_flag VARCHAR2(1);
60 l_item_type_code VARCHAR2(30);
61 l_service_count NUMBER;
62 l_start_date date;
63 l_end_date date;
64 BEGIN
65 -- Initialize message list if p_init_msg_list is set to TRUE.
66 IF FND_API.to_Boolean( p_init_msg_list ) THEN
67 FND_MSG_PUB.initialize;
68 END IF;
69
70 -- Initialize API return status to success
71 x_return_status := FND_API.G_RET_STS_SUCCESS;
72 IF aso_debug_pub.g_debug_flag = 'Y' THEN
73 aso_debug_pub.add('Entering Validate_Line_Tradein ', 2, 'Y');
74 aso_debug_pub.add('Quote category code = ' || p_qte_header_rec.quote_category_code, 2, 'N');
75 aso_debug_pub.add('Line category code = ' || p_qte_line_rec.line_category_code, 2, 'N');
76 aso_debug_pub.add('order type id = ' || p_qte_header_rec.order_type_id, 2, 'N');
77 aso_debug_pub.add('Line Type = ' || p_qte_line_rec.order_line_type_id, 2, 'N');
78 END IF;
79
80
81 IF p_qte_line_rec.operation_code = 'UPDATE' then
82 OPEN C_inventory_item;
83 FETCH C_inventory_item into l_inventory_item_id, l_line_category_code;
84
85 IF p_qte_line_rec.line_category_code is not null and
86 p_qte_line_rec.line_category_code <> FND_API.G_MISS_CHAR then
87 l_line_category_code := p_qte_line_rec.line_category_code;
88 end if;
89 IF p_qte_line_rec.inventory_item_id is not null and
90 p_qte_line_rec.inventory_item_id <> FND_API.G_MISS_NUM then
91 l_inventory_item_id := p_qte_line_rec.inventory_item_id;
92 end if;
93 CLOSE C_inventory_item;
94 else
95 IF p_qte_line_rec.line_category_code is not null and
96 p_qte_line_rec.line_category_code <> FND_API.G_MISS_CHAR then
97 l_line_category_code := p_qte_line_rec.line_category_code;
98 end if;
99 IF p_qte_line_rec.inventory_item_id is not null and
100 p_qte_line_rec.inventory_item_id <> FND_API.G_MISS_NUM then
101 l_inventory_item_id := p_qte_line_rec.inventory_item_id;
102 end if;
103 end if; -- end check update operation.
104
105 IF aso_debug_pub.g_debug_flag = 'Y' THEN
106 aso_debug_pub.add('Line category code after update check = ' || l_line_category_code, 2, 'N');
107 aso_debug_pub.add('inventory item after update check = ' || l_inventory_item_id,2, 'N');
108 END IF;
109
110
111 -- Check if item is returnable
112 OPEN C_Validate_Item(l_inventory_item_id);
113 FETCH C_Validate_Item INTO l_returnable_flag, l_customer_order_enabled_flag, l_serviceable_product_flag;
114 CLOSE C_Validate_Item;
115
116 -- Line level validations
117 IF l_line_category_code = 'RETURN' THEN
118 IF (p_qte_header_rec.quote_category_code <> 'MIXED') THEN
119
120 x_return_status := FND_API.G_RET_STS_ERROR;
121 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
122 FND_MESSAGE.Set_Name('ASO', 'ASO_INVALID_LINE_CATEGORY_CODE');
123 FND_MSG_PUB.ADD;
124 END IF;
125 END IF;
126
127 -- Check if item is returnable
128 IF (NVL(l_returnable_flag,'Y') = 'N') THEN
129 x_return_status := FND_API.G_RET_STS_ERROR;
130 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
131 FND_MESSAGE.Set_Name('ASO', 'ASO_NOT_RETURNABLE');
132 FND_MSG_PUB.ADD;
133 END IF;
134 END IF;
135
136 IF p_qte_line_rec.operation_code = 'UPDATE' AND
137 l_serviceable_product_flag = 'Y' THEN
138 /* p_qte_line_rec.item_type_code = 'SVA' THEN */
139 OPEN C_Service_Available;
140 FETCH C_Service_Available INTO l_service_count;
141 CLOSE C_Service_Available;
142
143 IF l_service_count > 0 THEN
144 x_return_status := FND_API.G_RET_STS_ERROR;
145 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
146 FND_MESSAGE.Set_Name('ASO', 'ASO_SERVICE_NOT_RETURNABLE');
147 FND_MSG_PUB.ADD;
148 END IF;
149 END IF;
150 END IF;
151
152 ELSE -- RETURN
153
154 IF (NVL(l_customer_order_enabled_flag,'Y') = 'N') THEN
155 IF p_qte_line_rec.operation_code = 'CREATE' THEN
156 IF p_qte_line_rec.item_type_code <> 'CFG' AND
157 p_qte_line_rec.item_type_code IS NOT NULL AND
158 p_qte_line_rec.item_type_code <> FND_API.G_MISS_CHAR THEN
159
160 x_return_status := FND_API.G_RET_STS_ERROR;
161 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
162 FND_MESSAGE.Set_Name('ASO', 'ASO_NOT_ORDERABLE');
163 FND_MSG_PUB.ADD;
164 END IF;
165
166 END IF;
167 ELSE -- 'CREATE'
168 IF p_qte_line_rec.operation_code = 'UPDATE' THEN
169 IF p_qte_line_rec.item_type_code <> 'CFG' AND
170 p_qte_line_rec.item_type_code IS NOT NULL AND
171 p_qte_line_rec.item_type_code <> FND_API.G_MISS_CHAR THEN
172
173 x_return_status := FND_API.G_RET_STS_ERROR;
174 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
175 FND_MESSAGE.Set_Name('ASO', 'ASO_NOT_ORDERABLE');
176 FND_MSG_PUB.ADD;
177 END IF;
178
179 ELSE -- 'CFG'
180
181 IF p_qte_line_rec.item_type_code IS NULL OR
182 p_qte_line_rec.item_type_code = FND_API.G_MISS_CHAR THEN
183 OPEN C_item_type;
184 FETCH C_item_type INTO l_item_type_code;
185 CLOSE C_item_type;
186
187 IF l_item_type_code <> 'CFG' AND
188 l_item_type_code IS NOT NULL AND
189 l_item_type_code <> FND_API.G_MISS_CHAR THEN
190
191 x_return_status := FND_API.G_RET_STS_ERROR;
192 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
193 FND_MESSAGE.Set_Name('ASO', 'ASO_NOT_ORDERABLE');
194 FND_MSG_PUB.ADD;
195 END IF;
196 END IF;
197 END IF;
198
199 END IF; -- 'CFG'
200
201 END IF; -- 'UPDATE'
202
203 END IF; -- 'CREATE'
204
205 END IF; -- order_enabled_flag
206
207 END IF; -- 'RETURN'
208
209
210
211 -- Check if the workflow exists for this line type
212 /* IF p_qte_line_rec.order_line_type_id IS NOT NULL AND
213 p_qte_line_rec.order_line_type_id <> FND_API.G_MISS_NUM THEN
214
215 OPEN C_workflow;
216 FETCH C_Workflow into l_start_date, l_end_Date;
217 IF C_workflow%NOTFOUND THEN
218 x_return_status := FND_API.G_RET_STS_ERROR;
219 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
220 FND_MESSAGE.Set_Name('ASO', 'ASO_NO_WORKFLOW');
221 FND_MSG_PUB.ADD;
222 END IF;
223 END IF;
224 END IF; */
225
226 END Validate_Line_Tradein;
227
228
229
230 PROCEDURE OrderType(
231 p_init_msg_list IN VARCHAR2,
232 p_qte_header_rec IN OUT NOCOPY ASO_QUOTE_PUB.Qte_Header_Rec_Type,
233 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
234 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
235 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
236 IS
237
238 /*
239 * 2633507 - hyang: use oe_transaction_types_all instead of aso_i_order_types_v
240 */
241
242 CURSOR C_Order_Type(l_order_type_id NUMBER) IS
243 SELECT order_category_code, start_date_active, end_date_active
244 --FROM OE_TRANSACTION_TYPES_ALL Commented Code yogeshwar (MOAC)
245 FROM OE_TRANSACTION_TYPES_VL --New Code Yogeshwar (MOAC)
246 WHERE transaction_type_id = l_order_type_id
247 and Transaction_type_code = 'ORDER' ;
248 --Commented Code Start Yogeshwar
249 /*
250 and NVL(ORG_ID,NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ',NULL, SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)) = NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1), ' ', NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99);
251 */
252 --Commented Code End Yogeshwar
253 l_order_category_code VARCHAR2(30);
254 l_start_date DATE;
255 l_end_date DATE;
256
257 BEGIN
258 -- Initialize message list if p_init_msg_list is set to TRUE.
259 IF FND_API.to_Boolean( p_init_msg_list ) THEN
260 FND_MSG_PUB.initialize;
261 END IF;
262
263 -- Initialize API return status to success
264 x_return_status := FND_API.G_RET_STS_SUCCESS;
265 IF aso_debug_pub.g_debug_flag = 'Y' THEN
266 aso_debug_pub.add('Entering OrderType ', 2, 'Y');
267 aso_debug_pub.add('Quote category code = ' || p_qte_header_rec.quote_category_code, 2, 'N');
268 aso_debug_pub.add('order type id = ' || p_qte_header_rec.order_type_id, 2, 'N');
269 END IF;
270
271 IF (p_qte_header_rec.order_type_id IS NOT NULL AND p_qte_header_rec.order_type_id <> FND_API.G_MISS_NUM) THEN
272 OPEN C_Order_Type(p_qte_header_rec.order_type_id);
273 FETCH C_Order_Type INTO l_order_category_code, l_start_date, l_end_date;
274 IF aso_debug_pub.g_debug_flag = 'Y' THEN
275 aso_debug_pub.add('l_Quote category code = ' || l_order_category_code , 2, 'N');
276 aso_debug_pub.add('start date= ' || l_start_date, 2, 'N');
277 aso_debug_pub.add('end date = ' || l_end_date, 2, 'N');
278 END IF;
279
280 IF (C_Order_Type%NOTFOUND OR
281 (sysdate NOT BETWEEN NVL(l_start_date, sysdate) AND
282 NVL(l_end_date, sysdate))) THEN
283 CLOSE C_Order_Type;
284 x_return_status := FND_API.G_RET_STS_ERROR;
285 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
286 FND_MESSAGE.Set_Name('ASO', 'API_INVALID_ID');
287 FND_MESSAGE.Set_Token('COLUMN', 'ORDER_TYPE_ID', FALSE);
288 FND_MSG_PUB.ADD;
289 END IF;
290 ELSE
291 IF (p_qte_header_rec.quote_category_code IS NULL OR
292 p_qte_header_rec.quote_category_code = FND_API.G_MISS_CHAR) THEN
293 p_qte_header_rec.quote_category_code := l_order_category_code;
294
295 ELSE
296 IF (p_qte_header_rec.quote_category_code <> l_order_category_code) THEN
297 x_return_status := FND_API.G_RET_STS_ERROR;
298 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
299 FND_MESSAGE.Set_Name('ASO', 'API_INVALID_ID');
300 FND_MESSAGE.Set_Token('COLUMN', 'QUOTE_CATEGORY_CODE', FALSE);
301 FND_MSG_PUB.ADD;
302 END IF;
303 END IF;
304 END IF;
305 CLOSE C_Order_Type;
306
307 END IF;
308 END IF;
309
310 IF aso_debug_pub.g_debug_flag = 'Y' THEN
311 aso_debug_pub.add('Quote category code = ' || p_qte_header_rec.quote_category_code, 2, 'N');
312 aso_debug_pub.add('order type id = ' || p_qte_header_rec.order_type_id, 2, 'N');
313 END IF;
314
315 END OrderType;
316
317
318 PROCEDURE LineType(
319 p_init_msg_list IN VARCHAR2,
320 p_qte_header_rec IN OUT NOCOPY ASO_QUOTE_PUB.Qte_Header_Rec_Type,
321 p_qte_line_rec IN OUT NOCOPY ASO_QUOTE_PUB.Qte_Line_Rec_Type,
322 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
323 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
324 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
325 IS
326 /*
327 * 2633507 - hyang: use oe_transaction_types_all instead of aso_i_line_types_v
328 */
329
330 CURSOR C_Order_Line_Type(l_order_line_type_id NUMBER) IS
331 SELECT order_category_code, start_date_active, end_date_active
332 --FROM OE_TRANSACTION_TYPES_ALL Commented Code yogeshwar (MOAC)
333 FROM OE_TRANSACTION_TYPES_VL --New Code Yogeshwar (MOAC)
334 WHERE transaction_type_id = l_order_line_type_id
335 and Transaction_type_code = 'LINE' ;
336 --Commented Code Start Yogeshwar (MOAC)
337 /*
338 and NVL(ORG_ID,NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ',NULL, SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)) = NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1), ' ', NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99);
339 */
340 --Commented Code End Yogeshwar (MOAC)
341
342 l_line_category_code VARCHAR2(30);
343 l_start_date DATE;
344 l_end_date DATE;
345
346 BEGIN
347 -- Initialize message list if p_init_msg_list is set to TRUE.
348 IF FND_API.to_Boolean( p_init_msg_list ) THEN
349 FND_MSG_PUB.initialize;
350 END IF;
351
352 -- Initialize API return status to success
353 x_return_status := FND_API.G_RET_STS_SUCCESS;
354 IF aso_debug_pub.g_debug_flag = 'Y' THEN
355 aso_debug_pub.add('Entering LineType ', 2, 'Y');
356 aso_debug_pub.add('Line category code = ' || p_qte_line_rec.line_category_code, 2, 'N');
357 aso_debug_pub.add('Line Type = ' || p_qte_line_rec.order_line_type_id, 2, 'N');
358 END IF;
359
360 IF (p_qte_line_rec.order_line_type_id IS NOT NULL AND p_qte_line_rec.order_line_type_id <> FND_API.G_MISS_NUM) THEN
361 OPEN C_Order_Line_Type(p_qte_line_rec.order_line_type_id);
362 FETCH C_Order_Line_Type INTO l_line_category_code, l_start_date, l_end_date;
363 IF aso_debug_pub.g_debug_flag = 'Y' THEN
364 aso_debug_pub.add('l Line category code = ' || l_line_category_code, 2, 'N');
365 aso_debug_pub.add('start date= ' || l_start_date, 2, 'N');
366 aso_debug_pub.add('start date= ' || l_start_date, 2, 'N');
367 END IF;
368 IF (C_Order_Line_Type%NOTFOUND OR
369 (sysdate NOT BETWEEN NVL(l_start_date, sysdate) AND
370 NVL(l_end_date, sysdate))) THEN
371 CLOSE C_Order_Line_Type;
372 x_return_status := FND_API.G_RET_STS_ERROR;
373 IF aso_debug_pub.g_debug_flag = 'Y' THEN
374 aso_debug_pub.add('Create_Quote_lines - check line type 1', 1, 'Y');
375 END IF;
376 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
377 FND_MESSAGE.Set_Name('ASO', 'API_INVALID_ID');
378 FND_MESSAGE.Set_Token('COLUMN', 'ORDER_LINE_TYPE_ID', FALSE);
379 FND_MSG_PUB.ADD;
380 END IF;
381 ELSE
382 IF (p_qte_line_rec.line_category_code IS NULL OR
383 p_qte_line_rec.line_category_code = FND_API.G_MISS_CHAR) THEN
384 p_qte_line_rec.line_category_code := l_line_category_code;
385
386 ELSE
387 IF (p_qte_line_rec.line_category_code <> l_line_category_code) THEN
388 x_return_status := FND_API.G_RET_STS_ERROR;
389 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
390 FND_MESSAGE.Set_Name('ASO', 'API_INVALID_ID');
391 FND_MESSAGE.Set_Token('COLUMN', 'LINE_CATEGORY_CODE', FALSE);
392 FND_MSG_PUB.ADD;
393 END IF;
394 END IF;
395 CLOSE C_Order_Line_Type;
396 END IF;
397 END IF;
398 ELSE -- order_line_type_id is null
399 IF ((p_qte_line_rec.line_category_code IS NULL OR
400 p_qte_line_rec.line_category_code = FND_API.G_MISS_CHAR)
401 AND p_qte_line_rec.operation_code = 'CREATE') THEN
402 p_qte_line_rec.line_category_code := 'ORDER';
403 END IF;
404 END IF;
405
406 IF aso_debug_pub.g_debug_flag = 'Y' THEN
407 aso_debug_pub.add('Line category code = ' || p_qte_line_rec.line_category_code, 2, 'N');
408 aso_debug_pub.add('Line Type = ' || p_qte_line_rec.order_line_type_id, 2, 'N');
409 END IF;
410 END LineType;
411
412
413 PROCEDURE Add_Lines_from_InstallBase(
414 P_Api_Version_Number IN NUMBER,
415 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
416 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
417 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
418 P_Control_Rec IN ASO_QUOTE_PUB.Control_Rec_Type
419 := ASO_QUOTE_PUB.G_Miss_Control_Rec,
420 P_Qte_Header_Rec IN ASO_QUOTE_PUB.Qte_Header_Rec_Type
421 := ASO_QUOTE_PUB.G_Miss_Qte_Header_Rec,
422 P_instance_tbl IN ASO_QUOTE_HEADERS_PVT.Instance_Tbl_Type
423 := ASO_QUOTE_HEADERS_PVT.G_MISS_Instance_Tbl,
424 X_Qte_Header_Rec OUT NOCOPY /* file.sql.39 change */ ASO_QUOTE_PUB.Qte_Header_Rec_Type,
425 X_Qte_Line_Tbl OUT NOCOPY /* file.sql.39 change */ ASO_QUOTE_PUB.Qte_Line_Tbl_Type,
426 X_Qte_Line_Dtl_Tbl OUT NOCOPY /* file.sql.39 change */ ASO_QUOTE_PUB.Qte_Line_Dtl_Tbl_Type,
427 X_ln_Shipment_Tbl OUT NOCOPY /* file.sql.39 change */ ASO_QUOTE_PUB.Shipment_Tbl_Type,
428 X_Return_Status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
429 X_Msg_Count OUT NOCOPY /* file.sql.39 change */ NUMBER,
430 X_Msg_Data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
431 )
432
433 IS
434
435
436 /*
437 * 2611381: using base table instead of vl view
438 */
439 CURSOR C_Get_Item_Details(l_instance_id NUMBER, l_organization_id NUMBER) IS
440 SELECT a.inventory_item_id, a.quantity, a.unit_of_measure,
441 a.last_oe_order_line_id, b.bom_item_type, b.returnable_flag
442 FROM CSI_ITEM_INSTANCES a, MTL_SYSTEM_ITEMS_B b
443 WHERE a.inventory_item_id = b.inventory_item_id
444 AND a.instance_id = l_instance_id
445 AND b.organization_id = l_organization_id;
446
447 CURSOR C_Get_Children(l_instance_id NUMBER) IS
448 SELECT subject_id
449 FROM CSI_II_RELATIONSHIPS
450 WHERE relationship_type_code='COMPONENT-OF'
451 START WITH object_id = l_instance_id
452 CONNECT BY object_id = prior subject_id;
453
454 CURSOR C_Ln_Dtl_Instances(l_instance_id NUMBER, l_header_id NUMBER) IS
455 SELECT 'Y'
456 FROM ASO_QUOTE_LINES_ALL a, ASO_QUOTE_LINE_DETAILS b
457 WHERE a.quote_line_id = b.quote_line_id
458 AND a.quote_header_id = l_header_id
459 AND b.instance_id = l_instance_id;
460
461 CURSOR C_Get_Order_Header(l_line_id NUMBER) IS
462 SELECT header_id
463 FROM OE_ORDER_LINES_ALL
464 WHERE line_id = l_line_id;
465
466 CURSOR C_Get_Header_Org(l_header_id NUMBER) IS
467 SELECT org_id
468 FROM ASO_QUOTE_HEADERS_ALL
469 WHERE quote_header_id = l_header_id;
470
471 l_Cur_Inst C_Get_Item_Details%ROWTYPE;
472 l_Cur_Child C_Get_Item_Details%ROWTYPE;
473 l_used_inst_tbl ASO_QUOTE_HEADERS_PVT.Instance_Tbl_Type
474 := ASO_QUOTE_HEADERS_PVT.G_MISS_Instance_Tbl;
475
476 l_qte_line_tbl ASO_QUOTE_PUB.Qte_Line_Tbl_Type;
477 l_qte_line_dtl_tbl ASO_QUOTE_PUB.Qte_Line_Dtl_Tbl_Type;
478 l_ln_shipment_tbl ASO_QUOTE_PUB.Shipment_Tbl_Type;
479 lx_hd_Price_Attr_Tbl ASO_QUOTE_PUB.Price_Attributes_Tbl_Type;
480 lx_hd_payment_tbl ASO_QUOTE_PUB.Payment_Tbl_Type;
481 lx_hd_shipment_tbl ASO_QUOTE_PUB.Shipment_Tbl_Type;
482 lx_hd_freight_charge_tbl ASO_QUOTE_PUB.Freight_Charge_Tbl_Type;
483 lx_hd_tax_detail_tbl ASO_QUOTE_PUB.Tax_Detail_Tbl_Type;
484 lX_hd_Attr_Ext_Tbl ASO_QUOTE_PUB.Line_Attribs_Ext_Tbl_Type;
485 lx_Line_Attr_Ext_Tbl ASO_QUOTE_PUB.Line_Attribs_Ext_Tbl_Type;
486 lx_line_rltship_tbl ASO_QUOTE_PUB.Line_Rltship_Tbl_Type;
487 lx_Price_Adjustment_Tbl ASO_QUOTE_PUB.Price_Adj_Tbl_Type;
488 lx_Price_Adj_Attr_Tbl ASO_QUOTE_PUB.Price_Adj_Attr_Tbl_Type;
489 lx_price_adj_rltship_tbl ASO_QUOTE_PUB.Price_Adj_Rltship_Tbl_Type;
490 lx_hd_Sales_Credit_Tbl ASO_QUOTE_PUB.Sales_Credit_Tbl_Type;
491 lx_Quote_Party_Tbl ASO_QUOTE_PUB.Quote_Party_Tbl_Type;
492 lX_Ln_Sales_Credit_Tbl ASO_QUOTE_PUB.Sales_Credit_Tbl_Type;
493 lX_Ln_Quote_Party_Tbl ASO_QUOTE_PUB.Quote_Party_Tbl_Type;
494 lx_ln_Price_Attr_Tbl ASO_QUOTE_PUB.Price_Attributes_Tbl_Type;
495 lx_ln_payment_tbl ASO_QUOTE_PUB.Payment_Tbl_Type;
496 lx_ln_shipment_tbl ASO_QUOTE_PUB.Shipment_Tbl_Type;
497 lx_ln_freight_charge_tbl ASO_QUOTE_PUB.Freight_Charge_Tbl_Type;
498 lx_ln_tax_detail_tbl ASO_QUOTE_PUB.Tax_Detail_Tbl_Type;
499
500 l_used VARCHAR2(1);
501 j NUMBER := 0;
502 l_org_id NUMBER;
503 l_ord_hdr NUMBER;
504 l_top_model_index NUMBER;
505
506 l_api_version_number CONSTANT NUMBER := 1.0;
507 l_api_name CONSTANT VARCHAR2(45) := 'Add_Lines_from_InstallBase';
508 -- 2929469
509 l_organization_id NUMBER;
510
511 l_control_rec ASO_QUOTE_PUB.Control_Rec_Type := p_control_rec;
512 l_prof_val varchar2(240);
513
514 BEGIN
515
516 -- Standard Start of API savepoint
517 SAVEPOINT Add_Lines_from_InstallBase_PVT;
518
519 -- Standard call to check for call compatibility.
520 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
521 p_api_version_number,
522 l_api_name,
523 G_PKG_NAME) THEN
524 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
525 END IF;
526
527 -- Initialize message list if p_init_msg_list is set to TRUE.
528 IF FND_API.to_Boolean( p_init_msg_list )
529 THEN
530 FND_MSG_PUB.initialize;
531 END IF;
532
533 IF aso_debug_pub.g_debug_flag = 'Y' THEN
534 aso_debug_pub.add('Add_Lines_from_InstallBase - Begin ', 1, 'Y');
535 END IF;
536
537 -- Initialize API return status to SUCCESS
538 x_return_status := FND_API.G_RET_STS_SUCCESS;
539
540 IF p_qte_header_rec.org_id IS NULL OR
541 p_qte_header_rec.org_id = FND_API.G_MISS_NUM THEN
542
543 OPEN C_Get_Header_Org(p_qte_header_rec.quote_header_id);
544 FETCH C_Get_Header_Org INTO l_org_id;
545 CLOSE C_Get_Header_Org;
546 ELSE
547 l_org_id := p_qte_header_rec.org_id;
548 END IF;
549
550 l_organization_id := oe_profile.value('OE_ORGANIZATION_ID', l_org_id);
551
552 IF aso_debug_pub.g_debug_flag = 'Y' THEN
553 aso_debug_pub.add('Add_Lines_from_InstallBase - l_org_id '||l_org_id, 1, 'N');
554 aso_debug_pub.add('Add_Lines_from_InstallBase - l_organization_id '||l_organization_id, 1, 'N');
555 aso_debug_pub.add('Add_Lines_from_InstallBase - P_instance_tbl.count '||P_instance_tbl.count, 1, 'N');
556 END IF;
557
558 FOR i IN 1..P_instance_tbl.count LOOP
559
560 IF aso_debug_pub.g_debug_flag = 'Y' THEN
561 aso_debug_pub.add('Add_Lines_from_InstallBase - P_Instance_Tbl(i).Instance_Id: '||P_Instance_Tbl(i).Instance_Id, 1, 'N');
562 END IF;
563
564 l_used := 'N';
565 OPEN C_Ln_Dtl_Instances(P_Instance_Tbl(i).Instance_Id, P_Qte_Header_Rec.quote_header_id);
566 FETCH C_Ln_Dtl_Instances INTO l_used;
567 CLOSE C_Ln_Dtl_Instances;
568
569 IF aso_debug_pub.g_debug_flag = 'Y' THEN
570 aso_debug_pub.add('Add_Lines_from_InstallBase - exists in quote: '||l_used, 1, 'N');
571 END IF;
572
573 IF l_used = 'N' THEN
574
575 FOR k IN 1..l_used_inst_tbl.count LOOP
576 IF l_used_inst_tbl(k).Instance_Id = P_Instance_Tbl(i).Instance_Id THEN
577 l_used := 'Y';
578 EXIT;
579 END IF;
580 END LOOP;
581 IF aso_debug_pub.g_debug_flag = 'Y' THEN
582 aso_debug_pub.add('Add_Lines_from_InstallBase - already in quote: '||l_used, 1, 'N');
583 END IF;
584
585 END IF;
586
587 IF l_used = 'N' THEN
588
589 l_used_inst_tbl(l_used_inst_tbl.count+1).Instance_Id := P_Instance_Tbl(i).Instance_Id;
590
591 OPEN C_Get_Item_Details(P_instance_tbl(i).Instance_Id, l_organization_id);
592 FETCH C_Get_Item_Details INTO l_Cur_Inst;
593
594 IF C_Get_Item_Details%NOTFOUND THEN
595 IF aso_debug_pub.g_debug_flag = 'Y' THEN
596 aso_debug_pub.add('Add_Lines_from_InstallBase - Invalid instance: ', 1, 'N');
597 END IF;
598
599 CLOSE C_Get_Item_Details;
600 x_return_status := FND_API.G_RET_STS_ERROR;
601
602 FND_MESSAGE.Set_Name('ASO', 'ASO_INVALID_INSTANCE');
603 FND_MSG_PUB.ADD;
604 RAISE FND_API.G_EXC_ERROR;
605
606 END IF;
607
608 CLOSE C_Get_Item_Details;
609
610 IF NVL(l_Cur_Inst.Returnable_Flag, 'Y') <> 'Y' THEN
611 IF aso_debug_pub.g_debug_flag = 'Y' THEN
612 aso_debug_pub.add('Add_Lines_from_InstallBase - Not Returnable instance: ', 1, 'N');
613 END IF;
614
615 x_return_status := FND_API.G_RET_STS_ERROR;
616
617 FND_MESSAGE.Set_Name('ASO', 'ASO_NOT_RETURNABLE');
618 FND_MSG_PUB.ADD;
619 RAISE FND_API.G_EXC_ERROR;
620
621 ELSE
622
623 IF aso_debug_pub.g_debug_flag = 'Y' THEN
624 aso_debug_pub.add('Add_Lines_from_InstallBase - l_Cur_Inst.Inventory_Item_Id: '||l_Cur_Inst.Inventory_Item_Id, 1, 'N');
625 aso_debug_pub.add('Add_Lines_from_InstallBase - l_Cur_Inst.Quantity: '||l_Cur_Inst.Quantity, 1, 'N');
626 aso_debug_pub.add('Add_Lines_from_InstallBase - l_Cur_Inst.Unit_Of_Measure: '||l_Cur_Inst.Unit_Of_Measure, 1, 'N');
627 END IF;
628
629 j := j + 1;
630 l_qte_line_tbl(j).quote_header_id := p_qte_header_rec.quote_header_id;
631 l_qte_line_tbl(j).inventory_item_id := l_Cur_Inst.Inventory_Item_Id;
632 l_qte_line_tbl(j).organization_id := l_organization_id;
633 l_qte_line_tbl(j).Quantity := l_Cur_Inst.Quantity;
634 l_qte_line_tbl(j).UOM_Code := l_Cur_Inst.Unit_Of_Measure;
635 l_qte_line_tbl(j).Line_Category_Code := 'RETURN';
636 l_qte_line_tbl(j).Operation_Code := 'CREATE';
637 IF P_Instance_Tbl(i).Price_List_Id <> FND_API.G_MISS_NUM AND
638 P_Instance_Tbl(i).Price_List_Id IS NOT NULL THEN
639 l_qte_line_tbl(j).Price_List_Id := P_Instance_Tbl(i).Price_List_Id;
640 ELSE
641 l_qte_line_tbl(j).Price_List_Id := FND_API.G_MISS_NUM;
642 END IF;
643 /*
644 IF l_Cur_Inst.last_oe_order_line_id IS NOT NULL THEN
645 IF aso_debug_pub.g_debug_flag = 'Y' THEN
646 aso_debug_pub.add('Add_Lines_from_InstallBase - l_Cur_Inst.last_oe_order_line_id: '||l_Cur_Inst.last_oe_order_line_id, 1, 'N');
647 END IF;
648
649 OPEN C_Get_Order_Header(l_Cur_Inst.last_oe_order_line_id);
650 FETCH C_Get_Order_Header INTO l_ord_hdr;
651 CLOSE C_Get_Order_Header;
652
653 IF aso_debug_pub.g_debug_flag = 'Y' THEN
654 aso_debug_pub.add('Add_Lines_from_InstallBase - referenced ord hdr: '||l_ord_hdr, 1, 'N');
655 END IF;
656
657 l_qte_line_dtl_tbl(j).return_ref_type := 'ORDER';
658 l_qte_line_dtl_tbl(j).return_ref_header_id := l_ord_hdr;
659 l_qte_line_dtl_tbl(j).return_ref_line_id := l_Cur_Inst.last_oe_order_line_id;
660 l_qte_line_dtl_tbl(j).return_attribute1 := l_ord_hdr;
661 l_qte_line_dtl_tbl(j).return_attribute2 := l_Cur_Inst.last_oe_order_line_id;
662
663 END IF;
664 */
665 l_qte_line_dtl_tbl(j).instance_id := P_Instance_Tbl(i).Instance_Id;
666 l_qte_line_dtl_tbl(j).Operation_Code := 'CREATE';
667 l_qte_line_dtl_tbl(j).qte_line_index := j;
668
669 l_ln_shipment_tbl(j).qte_line_index := j;
670
671 IF l_Cur_Inst.BOM_Item_Type = 1 THEN
672 IF aso_debug_pub.g_debug_flag = 'Y' THEN
673 aso_debug_pub.add('Add_Lines_from_InstallBase - model ', 1, 'N');
674 END IF;
675
676 l_qte_line_dtl_tbl(j).ref_type_code :='TOP_MODEL';
677 l_top_model_index := j;
678
679 FOR Inst_Children IN C_Get_Children(P_Instance_Tbl(i).Instance_Id) LOOP
680
681 l_used := 'N';
682 OPEN C_Ln_Dtl_Instances(Inst_Children.Subject_Id, P_Qte_Header_Rec.quote_header_id);
683 FETCH C_Ln_Dtl_Instances INTO l_used;
684 CLOSE C_Ln_Dtl_Instances;
685 IF aso_debug_pub.g_debug_flag = 'Y' THEN
686 aso_debug_pub.add('Add_Lines_from_InstallBase - exists in quote: '||l_used, 1, 'N');
687 END IF;
688
689 FOR k IN 1..l_used_inst_tbl.count LOOP
690 IF l_used_inst_tbl(k).Instance_Id = Inst_Children.Subject_Id THEN
691 l_used := 'Y';
692 EXIT;
693 END IF;
694 END LOOP;
695 IF aso_debug_pub.g_debug_flag = 'Y' THEN
696 aso_debug_pub.add('Add_Lines_from_InstallBase - already in quote: '||l_used, 1, 'N');
697 END IF;
698
699 IF l_used = 'N' THEN
700
701 l_used_inst_tbl(l_used_inst_tbl.count+1).Instance_Id := Inst_Children.Subject_Id;
702
703 OPEN C_Get_Item_Details(Inst_Children.Subject_Id, l_organization_id);
704 FETCH C_Get_Item_Details INTO l_Cur_Child;
705
706 IF C_Get_Item_Details%NOTFOUND THEN
707 IF aso_debug_pub.g_debug_flag = 'Y' THEN
708 aso_debug_pub.add('Add_Lines_from_InstallBase - Invalid child instance: ', 1, 'N');
709 END IF;
710
711 CLOSE C_Get_Item_Details;
712 x_return_status := FND_API.G_RET_STS_ERROR;
713
714 FND_MESSAGE.Set_Name('ASO', 'ASO_INVALID_INSTANCE');
715 FND_MSG_PUB.ADD;
716 RAISE FND_API.G_EXC_ERROR;
717
718 END IF;
719
720 CLOSE C_Get_Item_Details;
721
722 IF NVL(l_Cur_Child.Returnable_Flag, 'Y') = 'Y' THEN
723 IF aso_debug_pub.g_debug_flag = 'Y' THEN
724 aso_debug_pub.add('Add_Lines_from_InstallBase - l_Cur_Child.Inventory_Item_Id: '||l_Cur_Child.Inventory_Item_Id, 1, 'N');
725 aso_debug_pub.add('Add_Lines_from_InstallBase - l_Cur_Child.Quantity: '||l_Cur_Child.Quantity, 1, 'N');
726 aso_debug_pub.add('Add_Lines_from_InstallBase - l_Cur_Child.Unit_Of_Measure: '||l_Cur_Child.Unit_Of_Measure, 1, 'N');
727 END IF;
728
729 j := j + 1;
730 l_qte_line_tbl(j).quote_header_id := p_qte_header_rec.quote_header_id;
731 l_qte_line_tbl(j).inventory_item_id := l_Cur_Child.Inventory_Item_Id;
732 l_qte_line_tbl(j).organization_id := l_organization_id;
733 l_qte_line_tbl(j).Quantity := l_Cur_Child.Quantity;
734 l_qte_line_tbl(j).UOM_Code := l_Cur_Child.Unit_Of_Measure;
735 l_qte_line_tbl(j).Line_Category_Code := 'RETURN';
736 l_qte_line_tbl(j).Operation_Code := 'CREATE';
737 IF P_Instance_Tbl(i).Price_List_Id <> FND_API.G_MISS_NUM AND
738 P_Instance_Tbl(i).Price_List_Id IS NOT NULL THEN
739 l_qte_line_tbl(j).Price_List_Id := P_Instance_Tbl(i).Price_List_Id;
740 ELSE
741 l_qte_line_tbl(j).Price_List_Id := FND_API.G_MISS_NUM;
742 END IF;
743
744 l_qte_line_tbl(j).Price_List_Id := P_Instance_Tbl(i).Price_List_Id;
745 /*
746 IF l_Cur_Child.last_oe_order_line_id IS NOT NULL THEN
747 IF aso_debug_pub.g_debug_flag = 'Y' THEN
748 aso_debug_pub.add('Add_Lines_from_InstallBase - l_Cur_Inst.last_oe_order_line_id: '||l_Cur_Inst.last_oe_order_line_id, 1, 'N');
749 END IF;
750
751 OPEN C_Get_Order_Header(l_Cur_Child.last_oe_order_line_id);
752 FETCH C_Get_Order_Header INTO l_ord_hdr;
753 CLOSE C_Get_Order_Header;
754 IF aso_debug_pub.g_debug_flag = 'Y' THEN
755 aso_debug_pub.add('Add_Lines_from_InstallBase - referenced ord hdr: '||l_ord_hdr, 1, 'N');
756 END IF:
757
758 l_qte_line_dtl_tbl(j).return_ref_type := 'ORDER';
759 l_qte_line_dtl_tbl(j).return_ref_header_id := l_ord_hdr;
760 l_qte_line_dtl_tbl(j).return_ref_line_id := l_Cur_Child.last_oe_order_line_id;
761 l_qte_line_dtl_tbl(j).return_attribute1 := l_ord_hdr;
762 l_qte_line_dtl_tbl(j).return_attribute2 := l_Cur_Child.last_oe_order_line_id;
763
764 END IF;
765 */
766 IF aso_debug_pub.g_debug_flag = 'Y' THEN
767 aso_debug_pub.add('Add_Lines_from_InstallBase - Inst_Children.Subject_Id: '||Inst_Children.Subject_Id, 1, 'N');
768 END IF;
769
770 l_qte_line_dtl_tbl(j).instance_id := Inst_Children.Subject_Id;
771 l_qte_line_dtl_tbl(j).Operation_Code := 'CREATE';
772 l_qte_line_dtl_tbl(j).qte_line_index := j;
773
774 l_qte_line_dtl_tbl(j).ref_type_code :='TOP_MODEL';
775 l_qte_line_dtl_tbl(j).ref_line_index := l_top_model_index;
776
777 l_ln_shipment_tbl(j).qte_line_index := j;
778
779 END IF;
780
781 END IF; -- used = N
782
783 END LOOP; -- Inst_Children
784
785 END IF;
786
787 END IF; -- Returnable
788
789 END IF; -- used = N
790
791 END LOOP;
792
793 IF aso_debug_pub.g_debug_flag = 'Y' THEN
794 aso_debug_pub.add('p_qte_header_rec.last_update_date = '||
795 to_char(p_qte_header_rec.last_update_date,'DD-MM-YYYY HH:MI:SS'),1,'N');
796 aso_debug_pub.add('l_Qte_line_tbl.count = '|| l_Qte_line_tbl.count,1,'N');
797 END IF;
798
799
800 IF j > 0 THEN
801
802 l_prof_val := fnd_profile.value('ASO_ENABLE_DEFAULTING_RULE');
803
804 IF aso_debug_pub.g_debug_flag = 'Y' THEN
805 aso_debug_pub.add('l_prof_val: '|| l_prof_val,1,'N');
806 END IF;
807
808 if l_prof_val = 'Y' then
809 l_control_rec.DEFAULTING_FWK_FLAG := 'Y';
810 l_control_rec.DEFAULTING_FLAG := FND_API.G_TRUE;
811 l_control_rec.APPLICATION_TYPE_CODE := 'QUOTING HTML';
812 else
813 l_control_rec.DEFAULTING_FWK_FLAG := 'N';
814 l_control_rec.DEFAULTING_FLAG := FND_API.G_FALSE;
815 end if;
816
817 IF aso_debug_pub.g_debug_flag = 'Y' THEN
818 aso_debug_pub.add('Add_Lines_from_InstallBase - calling ASO_QUOTE_PUB.Update_Quote: ', 1, 'N');
819 END IF;
820
821 ASO_QUOTE_PUB.Update_Quote(
822 p_api_version_number => 1.0,
823 p_init_msg_list => p_init_msg_list,
824 p_commit => p_commit,
825 p_control_rec => l_control_rec,
826 p_qte_header_rec => p_qte_header_rec,
827 P_Qte_Line_Tbl => l_Qte_Line_Tbl,
828 P_Qte_Line_dtl_Tbl => l_Qte_Line_dtl_Tbl,
829 P_ln_Shipment_Tbl => l_ln_shipment_tbl,
830 X_Qte_Header_Rec => x_qte_header_rec,
831 X_Qte_Line_Tbl => x_Qte_Line_Tbl,
832 X_Qte_Line_Dtl_Tbl => x_Qte_Line_Dtl_Tbl,
833 X_hd_Price_Attributes_Tbl => lx_hd_Price_Attr_Tbl,
834 X_hd_Payment_Tbl => lx_hd_Payment_Tbl,
835 X_hd_Shipment_Tbl => lx_hd_Shipment_Tbl,
836 X_hd_Freight_Charge_Tbl => lx_hd_Freight_Charge_Tbl,
837 X_hd_Tax_Detail_Tbl => lx_hd_Tax_Detail_Tbl,
838 X_hd_Attr_Ext_Tbl => lX_hd_Attr_Ext_Tbl,
839 X_hd_Sales_Credit_Tbl => lx_hd_Sales_Credit_Tbl,
840 X_hd_Quote_Party_Tbl => lx_Quote_Party_Tbl,
841 X_Line_Attr_Ext_Tbl => lx_Line_Attr_Ext_Tbl,
842 X_line_rltship_tbl => lx_line_rltship_tbl,
843 X_Price_Adjustment_Tbl => lx_Price_Adjustment_Tbl,
844 X_Price_Adj_Attr_Tbl => lx_Price_Adj_Attr_Tbl,
845 X_Price_Adj_Rltship_Tbl => lx_Price_Adj_Rltship_Tbl,
846 X_ln_Price_Attributes_Tbl => lx_ln_Price_Attr_Tbl,
847 X_ln_Payment_Tbl => lx_ln_Payment_Tbl,
848 X_ln_Shipment_Tbl => x_ln_Shipment_Tbl,
849 X_ln_Freight_Charge_Tbl => lx_ln_Freight_Charge_Tbl,
850 X_ln_Tax_Detail_Tbl => lx_ln_Tax_Detail_Tbl,
851 X_Ln_Sales_Credit_Tbl => lX_Ln_Sales_Credit_Tbl,
852 X_Ln_Quote_Party_Tbl => lX_Ln_Quote_Party_Tbl,
853 X_Return_Status => x_Return_Status,
854 X_Msg_Count => x_Msg_Count,
855 X_Msg_Data => x_Msg_Data);
856
857 IF aso_debug_pub.g_debug_flag = 'Y' THEN
858 aso_debug_pub.add('Add_Lines_from_InstallBase - after update_quote: x_ret_status: '||x_return_status, 1, 'N');
859 END IF;
860
861 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
862 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
863 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
864 RAISE FND_API.G_EXC_ERROR;
865 END IF;
866
867 ELSE
868 x_qte_header_rec := p_qte_header_rec;
869
870 END IF; -- end j >0
871
872 IF aso_debug_pub.g_debug_flag = 'Y' THEN
873 aso_debug_pub.add('x_qte_header_rec.last_update_date = '||
874 to_char(x_qte_header_rec.last_update_date,'DD-MM-YYYY HH:MI:SS'),1,'N');
875 aso_debug_pub.add('x_Qte_line_tbl.count = '|| x_Qte_line_tbl.count,1,'N');
876 END IF;
877
878 FND_MSG_PUB.Count_And_Get
879 ( p_count => x_msg_count,
880 p_data => x_msg_data
881 );
882
883 EXCEPTION
884 WHEN FND_API.G_EXC_ERROR THEN
885 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
886 P_API_NAME => L_API_NAME
887 ,P_PKG_NAME => G_PKG_NAME
888 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
889 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
890 ,P_SQLCODE => SQLCODE
891 ,P_SQLERRM => SQLERRM
892 ,X_MSG_COUNT => X_MSG_COUNT
893 ,X_MSG_DATA => X_MSG_DATA
894 ,X_RETURN_STATUS => X_RETURN_STATUS);
895
896 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
897 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
898 P_API_NAME => L_API_NAME
899 ,P_PKG_NAME => G_PKG_NAME
900 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
901 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
902 ,P_SQLCODE => SQLCODE
903 ,P_SQLERRM => SQLERRM
904 ,X_MSG_COUNT => X_MSG_COUNT
905 ,X_MSG_DATA => X_MSG_DATA
906 ,X_RETURN_STATUS => X_RETURN_STATUS);
907
908 WHEN OTHERS THEN
909 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
910 P_API_NAME => L_API_NAME
911 ,P_PKG_NAME => G_PKG_NAME
912 ,P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS
913 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
914 ,P_SQLCODE => SQLCODE
915 ,P_SQLERRM => SQLERRM
916 ,X_MSG_COUNT => X_MSG_COUNT
917 ,X_MSG_DATA => X_MSG_DATA
918 ,X_RETURN_STATUS => X_RETURN_STATUS);
919
920 END Add_Lines_from_InstallBase;
921
922
923 PROCEDURE Validate_IB_Return_Qty(
924 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
925 p_Qte_Line_rec IN ASO_QUOTE_PUB.Qte_Line_Rec_Type,
926 p_Qte_Line_Dtl_Tbl IN ASO_QUOTE_PUB.Qte_Line_Dtl_tbl_Type,
927 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
928 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
929 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
930
931 IS
932
933 CURSOR C_Get_Dtl_Info(l_line_id NUMBER) IS
934 SELECT instance_id, return_ref_type, return_ref_line_id
935 FROM ASO_QUOTE_LINE_DETAILS
936 WHERE quote_line_id = l_line_id;
937
938 CURSOR C_Get_Qot_Qty(l_line_id NUMBER) IS
939 SELECT quantity
940 FROM ASO_QUOTE_LINES_ALL
941 WHERE quote_line_id = l_line_id;
942
943 CURSOR C_Get_Ord_Qty(l_line_id NUMBER) IS
944 SELECT ordered_quantity
945 FROM OE_ORDER_LINES_ALL
946 WHERE line_id = l_line_id;
947
948 CURSOR C_Get_Inst_Qty(l_inst_id NUMBER) IS
949 SELECT quantity
950 FROM CSI_ITEM_INSTANCES
951 WHERE instance_id = l_inst_id;
952
953 CURSOR C_Get_Inst_Ret_Info(l_inst_id NUMBER) IS
954 SELECT last_oe_order_line_id
955 FROM CSI_ITEM_INSTANCES
956 WHERE instance_id = l_inst_id;
957
958 l_qty NUMBER;
959 l_qte_quantity NUMBER;
960 l_inst_id NUMBER;
961 l_ref_id NUMBER;
962 l_ref_type VARCHAR2(30);
963
964 BEGIN
965
966 -- Initialize message list if p_init_msg_list is set to TRUE.
967 IF FND_API.to_Boolean( p_init_msg_list ) THEN
968 FND_MSG_PUB.initialize;
969 END IF;
970
971 -- Initialize API return status to success
972 x_return_status := FND_API.G_RET_STS_SUCCESS;
973 IF aso_debug_pub.g_debug_flag = 'Y' THEN
974 aso_debug_pub.add('Entering Validate_IB_Return_Qty ', 2, 'Y');
975 aso_debug_pub.add('Validate_IB_Return_Qty - p_qte_line_rec.operation_code: '||p_qte_line_rec.operation_code, 1, 'N');
976 aso_debug_pub.add('Validate_IB_Return_Qty - p_qte_line_rec.operation_code: '||p_qte_line_rec.operation_code, 1, 'N');
977 aso_debug_pub.add('Validate_IB_Return_Qty - p_qte_line_rec.quantity: '||p_qte_line_rec.quantity, 1, 'N');
978 END IF;
979
980 IF p_qte_line_rec.operation_code = 'UPDATE' THEN
981
982 OPEN C_Get_Dtl_Info(p_qte_line_rec.quote_line_id);
983 FETCH C_Get_Dtl_Info INTO l_inst_id, l_ref_type, l_ref_id;
984 CLOSE C_Get_Dtl_Info;
985
986 IF p_qte_line_rec.quantity IS NOT NULL AND p_qte_line_rec.quantity <> FND_API.G_MISS_NUM THEN
987 l_qte_quantity := p_qte_line_rec.quantity;
988 ELSE
989 OPEN C_Get_Qot_Qty(p_qte_line_rec.quote_line_id);
990 FETCH C_Get_Qot_Qty INTO l_qte_quantity;
991 CLOSE C_Get_Qot_Qty;
992 END IF;
993
994 ELSE
995 IF p_qte_line_rec.operation_code = 'CREATE' THEN
996 IF p_Qte_Line_Dtl_Tbl.count > 0 THEN
997 l_inst_id := p_qte_line_dtl_tbl(1).instance_id;
998
999 IF l_inst_id IS NOT NULL THEN
1000
1001 OPEN C_Get_Inst_Ret_Info(l_inst_id);
1002 FETCH C_Get_Inst_Ret_Info INTO l_ref_id;
1003 CLOSE C_Get_Inst_Ret_Info;
1004
1005 IF l_ref_id IS NOT NULL THEN
1006 l_ref_type := 'ORDER';
1007 ELSE
1008
1009 l_ref_type := p_qte_line_dtl_tbl(1).return_ref_type;
1010 l_ref_id := p_qte_line_dtl_tbl(1).return_ref_line_id;
1011 END IF;
1012
1013 END IF;
1014
1015 ELSE
1016 l_inst_id := NULL;
1017 l_ref_type := NULL;
1018 l_ref_id := NULL;
1019 END IF; -- ln_dtl_tbl.count
1020
1021 l_qte_quantity := p_qte_line_rec.quantity;
1022
1023 END IF; -- 'CREATE'
1024
1025 END IF;
1026
1027
1028 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1029 aso_debug_pub.add('Validate_IB_Return_Qty - l_inst_id: '||l_inst_id, 1, 'N');
1030 aso_debug_pub.add('Validate_IB_Return_Qty - l_ref_type: '||l_ref_type, 1, 'N');
1031 aso_debug_pub.add('Validate_IB_Return_Qty - l_ref_id: '||l_ref_id, 1, 'N');
1032 aso_debug_pub.add('Validate_IB_Return_Qty - l_qte_quantity: '||l_qte_quantity, 1, 'N');
1033 END IF;
1034
1035 IF l_qte_quantity IS NOT NULL AND
1036 l_qte_quantity <> FND_API.G_MISS_NUM THEN
1037
1038 IF l_inst_id IS NOT NULL AND
1039 l_inst_id <> FND_API.G_MISS_NUM THEN
1040
1041 IF p_qte_line_rec.line_category_code IS NOT NULL AND
1042 p_qte_line_rec.line_category_code <> FND_API.G_MISS_CHAR THEN
1043
1044 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1045 aso_debug_pub.add('Validate_IB_Return_Qty - p_qte_line_rec.line_category_code: '||p_qte_line_rec.line_category_code, 1, 'N');
1046 END IF;
1047
1048 IF p_qte_line_rec.line_category_code <> 'RETURN' THEN
1049 x_return_status := FND_API.G_RET_STS_ERROR;
1050 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1051 FND_MESSAGE.Set_Name('ASO', 'ASO_INST_RETURN_CODE');
1052 FND_MSG_PUB.ADD;
1053 END IF;
1054 END IF;
1055 END IF;
1056 /*
1057 IF l_ref_type = 'ORDER' THEN
1058 IF l_ref_id IS NOT NULL AND
1059 l_ref_id <> FND_API.G_MISS_NUM THEN
1060
1061 OPEN C_Get_Ord_Qty(l_ref_id);
1062 FETCH C_Get_Ord_Qty INTO l_qty;
1063 CLOSE C_Get_Ord_Qty;
1064
1065 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1066 aso_debug_pub.add('Validate_IB_Return_Qty - l_qty: '||l_qty, 1, 'N');
1067 END IF;
1068 IF l_qty <> l_qte_quantity THEN
1069 x_return_status := FND_API.G_RET_STS_ERROR;
1070 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1071 FND_MESSAGE.Set_Name('ASO', 'ASO_REFERENCED_RET_QTY');
1072 FND_MSG_PUB.ADD;
1073 END IF;
1074 END IF;
1075 END IF;
1076
1077 ELSE
1078 */
1079 OPEN C_Get_Inst_Qty(l_inst_id);
1080 FETCH C_Get_Inst_Qty INTO l_qty;
1081 CLOSE C_Get_Inst_Qty;
1082
1083 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1084 aso_debug_pub.add('Validate_IB_Return_Qty - l_qty: '||l_qty, 1, 'N');
1085 END IF;
1086 IF l_qty < l_qte_quantity THEN
1087 x_return_status := FND_API.G_RET_STS_ERROR;
1088 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1089 FND_MESSAGE.Set_Name('ASO', 'ASO_NOT_REFERENCED_RET_QTY');
1090 FND_MSG_PUB.ADD;
1091 END IF;
1092 END IF;
1093 /*
1094 END IF;
1095 */
1096 END IF; -- Instance_id
1097
1098 END IF; -- quantity
1099
1100 END Validate_IB_Return_Qty;
1101
1102
1103 END ASO_TRADEIN_PVT;