[Home] [Help]
PACKAGE BODY: APPS.OE_OE_HTML_LINE_EXT
Source
1 PACKAGE BODY Oe_Oe_Html_Line_Ext AS
2 /* $Header: ONTHLIEB.pls 120.0 2005/05/31 22:32:01 appldev noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'Oe_Oe_Html_Line_Ext';
5 G_line_dff_tbl Oe_Oe_Html_Line_Ext.Line_Dff_Tbl_Type;
6 G_LINE_NUMBER NUMBER;
7 G_SHIPMENT_NUMBER NUMBER;
8
9 PROCEDURE GET_LINE_SHIPMENT_NUMBER(
10 x_return_status OUT NOCOPY VARCHAR2
11 , p_header_id IN Number
12 , x_line_number OUT NOCOPY Number
13 , x_shipment_number OUT NOCOPY Number
14 );
15
16 PROCEDURE Save_Lines
17 (x_return_status OUT NOCOPY VARCHAR2
18 , x_msg_count OUT NOCOPY NUMBER
19 , x_msg_data OUT NOCOPY VARCHAR2
20 , x_cascade_flag OUT NOCOPY BOOLEAN
21 , p_line_tbl IN OE_ORDER_PUB.Line_Tbl_Type
22 , p_old_line_tbl IN OE_ORDER_PUB.Line_Tbl_Type
23 ) IS
24 l_control_rec OE_GLOBALS.Control_Rec_Type;
25 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
26 l_return_status VARCHAR2(1);
27 I Number;
28 J Number;
29 l_num_lines NUMBER;
30 l_num_dff_lines NUMBER;
31 l_fname varchar2(1000);
32 l_old_line_tbl OE_ORDER_PUB.Line_Tbl_Type;
33 l_line_tbl OE_ORDER_PUB.Line_Tbl_Type;
34 L_CASCADE_FLAG BOOLEAN;
35 BEGIN
36
37 -- Consider changing the line_tbl to OUT type so that the old record
38 -- can be cached after save.
39
40 oe_debug_pub.g_debug_level := FND_PROFILE.VALUE('ONT_DEBUG_LEVEL');
41 l_fname := oe_Debug_pub.set_debug_mode('FILE');
42 oe_debug_pub.debug_on;
43 OE_GLOBALS.G_UI_FLAG := TRUE;
44 l_control_rec.controlled_operation := TRUE;
45 l_control_rec.validate_entity := FALSE;
46 l_control_rec.write_to_DB := FALSE;
47
48 l_control_rec.check_security := TRUE;
49 l_control_rec.clear_dependents := TRUE;
50 l_control_rec.default_attributes := TRUE;
51 l_control_rec.change_attributes := FALSE;
52 l_control_rec.process := FALSE;
53 l_old_line_tbl:=p_old_line_tbl;
54 l_line_tbl:=p_line_tbl;
55
56 -- Instruct API to retain its caches
57
58 l_control_rec.clear_api_cache := FALSE;
59 l_control_rec.clear_api_requests := FALSE;
60
61 -- Read line from cache
62
63 -- Operation Should be Populated in the EO as multiple lines
64 -- will be passed to the API. It is easier to set the flag
65 -- through the EO instead of looping.
66
67 -- Populate line table
68
69
70
71 -- Call Oe_Order_Pvt.Process_order
72
73 IF l_debug_level > 0 THEN
74 oe_debug_pub.add( 'IN BEGINING HTML LINE- SAVE' ) ;
75 END IF;
76 oe_debug_pub.add( 'AT Beg HTML LINE- SAVE FIRST CALL-Line Type'
77 || l_line_tbl(1).line_type_id ) ;
78 oe_debug_pub.add( 'AT Beg HTML LINE- SAVE FIRST CALL-Line Type'
79 || l_old_line_tbl(1).line_type_id ) ;
80
81 Oe_Order_Pvt.Lines
82 ( p_validation_level => FND_API.G_VALID_LEVEL_NONE
83 , p_init_msg_list => FND_API.G_FALSE
84 , p_control_rec => l_control_rec
85 , p_x_line_tbl => l_line_tbl
86 , p_x_old_line_tbl => l_old_line_tbl
87 , x_return_Status => l_return_status
88 );
89 IF l_debug_level > 0 THEN
90 oe_debug_pub.add( 'AT END HTML LINE- SAVE FIRST CALL' ) ;
91 oe_debug_pub.add( 'AT END HTML LINE- SAVE FIRST CALL-Line Type'
92 || l_line_tbl(1).line_type_id ) ;
93 END IF;
94
95 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
96 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
97 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
98 RAISE FND_API.G_EXC_ERROR;
99 END IF;
100
101
102 IF l_debug_level > 0 THEN
103 oe_debug_pub.add( 'AT BEGINING HTML LINE- SAVE SECOND CALL' ) ;
104 END IF;
105
106 l_control_rec.controlled_operation := TRUE;
107 l_control_rec.validate_entity := TRUE;
108 l_control_rec.write_to_DB := TRUE;
109
110 l_control_rec.check_security := FALSE;
111 l_control_rec.clear_dependents := FALSE;
112 l_control_rec.default_attributes := FALSE;
113 l_control_rec.change_attributes := TRUE;
114 l_control_rec.process := FALSE;
115
116
117 I := l_line_tbl.FIRST;
118 l_num_lines := l_line_tbl.COUNT;
119 WHILE I IS NOT NULL AND l_num_lines > 0
120 LOOP
121 IF FND_API.To_Boolean(l_line_tbl(i).db_flag) OR
122 l_line_tbl(i).operation = OE_GLOBALS.G_OPR_UPDATE THEN
123 oe_debug_pub.add('Operation -Update');
124 -- The operation is set to handle if it doesn't get set in middle tier.
125 l_line_tbl(i).operation:=OE_GLOBALS.G_OPR_UPDATE;
126
127 -- PO would have already queried the record if the old record is missing.
128 -- So this is a redundant call. Commenting for performance reasons.
129 OE_Line_Util.Query_Row
130 ( p_line_id => l_line_tbl(i).line_id
131 , x_line_rec => l_old_line_tbl(i) );
132 ELSE
133 -- This needs to be changed once deletes are handled.
134 oe_debug_pub.add('Operation -CREATE');
135 -- The operation is set to handle if it doesn't get set in middle tier.
136 l_line_tbl(i).operation:=OE_GLOBALS.G_OPR_CREATE;
137 GET_LINE_SHIPMENT_NUMBER(
138 x_return_Status => l_return_status
139 , p_header_id => l_line_tbl(i).header_id
140 , x_line_number => l_line_tbl(i).line_number
141 , x_shipment_number => l_line_tbl(i).shipment_number
142 );
143 oe_debug_pub.add( 'IN GET_LINE_SHIPMENT_NUMBER'||l_line_tbl(i).line_number);
144 oe_debug_pub.add( 'IN GET_LINE_SHIPMENT_NUMBER'||l_line_tbl(i).shipment_number);
145 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
146 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
147 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
148 RAISE FND_API.G_EXC_ERROR;
149 END IF;
150 END IF;
151
152 J := G_line_dff_tbl.FIRST;
153 l_num_dff_lines := l_line_tbl.COUNT;
154 WHILE J IS NOT NULL AND l_num_dff_lines > 0
155 LOOP
156 IF NVL(G_line_dff_tbl(j).line_id,-1)=NVL(l_line_tbl(i).line_id,-2) THEN
157 l_line_tbl(i).attribute1:=G_line_dff_tbl(j).attribute1;
158 l_line_tbl(i).attribute2:=G_line_dff_tbl(j).attribute2;
159 l_line_tbl(i).attribute3:=G_line_dff_tbl(j).attribute3;
160 l_line_tbl(i).attribute4:=G_line_dff_tbl(j).attribute4;
161 l_line_tbl(i).attribute5:=G_line_dff_tbl(j).attribute5;
162 l_line_tbl(i).attribute6:=G_line_dff_tbl(j).attribute6;
163 l_line_tbl(i).attribute7:=G_line_dff_tbl(j).attribute7;
164 l_line_tbl(i).attribute8:=G_line_dff_tbl(j).attribute8;
165 l_line_tbl(i).attribute9:=G_line_dff_tbl(j).attribute9;
166 l_line_tbl(i).attribute10:=G_line_dff_tbl(j).attribute10;
167 l_line_tbl(i).attribute11:=G_line_dff_tbl(j).attribute11;
168 l_line_tbl(i).attribute12:=G_line_dff_tbl(j).attribute12;
169 l_line_tbl(i).attribute13:=G_line_dff_tbl(j).attribute13;
170 l_line_tbl(i).attribute14:=G_line_dff_tbl(j).attribute14;
171 l_line_tbl(i).attribute15:=G_line_dff_tbl(j).attribute15;
172 Exit;
173 END IF;
174 j := G_line_dff_tbl.NEXT(I);
175 END LOOP;
176 I := l_line_tbl.NEXT(I);
177 END LOOP;
178
179
180
181 -- Instruct API to retain its caches
182
183 l_control_rec.clear_api_cache := FALSE;
184 l_control_rec.clear_api_requests := FALSE;
185
186 Oe_Order_Pvt.Lines
187 ( p_validation_level => FND_API.G_VALID_LEVEL_NONE
188 , p_init_msg_list => FND_API.G_TRUE
189 , p_control_rec => l_control_rec
190 , p_x_line_tbl => l_line_tbl
191 , p_x_old_line_tbl => l_old_line_tbl
192 , x_return_Status => l_return_status
193 );
194 IF l_debug_level > 0 THEN
195 oe_debug_pub.add( 'AT END HTML LINE- SAVE SECOND CALL' ) ;
196 END IF;
197
198 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
199 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
200 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
201 RAISE FND_API.G_EXC_ERROR;
202 END IF;
203
204 /* Oe_Oe_Html_Header.Process_Object
205 ( x_return_status =>l_return_status
206 , x_msg_count => x_msg_count
207 , x_msg_data => x_msg_data
208 , x_cascade_flag => l_cascade_flag
209 ); */
210 oe_debug_pub.add( 'AT END HTML LINE- PROCESS OBJECT CALL' ) ;
211
212 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
213 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
214 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
215 RAISE FND_API.G_EXC_ERROR;
216 END IF;
217
218 x_return_status := FND_API.G_RET_STS_SUCCESS;
219 G_LINE_NUMBER:=Null;
220 G_SHIPMENT_NUMBER:=Null;
221 oe_msg_pub.count_and_get
222 ( p_count => x_msg_count
223 , p_data => x_msg_data
224 );
225 oe_debug_pub.add( 'IN END HTML LINE- SAVE' ) ;
226 EXCEPTION
227 WHEN FND_API.G_EXC_ERROR THEN
228
229 OE_GLOBALS.G_UI_FLAG := FALSE;
230
231 x_return_status := FND_API.G_RET_STS_ERROR;
232
233 -- Get message count and data
234
235 oe_msg_pub.count_and_get
236 ( p_count => x_msg_count
237 , p_data => x_msg_data
238 );
239
240 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
241
242 OE_GLOBALS.G_UI_FLAG := FALSE;
243
244 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
245
246 -- Get message count and data
247
248 oe_msg_pub.count_and_get
249 ( p_count => x_msg_count
250 , p_data => x_msg_data
251 );
252
253 WHEN OTHERS THEN
254
255 IF oe_msg_pub.Check_Msg_Level(oe_msg_pub.G_MSG_LVL_UNEXP_ERROR)
256 THEN
257 oe_msg_pub.Add_Exc_Msg
258 ( G_PKG_NAME
259 , 'Save_Lines'
260 );
261 END IF;
262
263 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
264
265 -- Get message count and data
266
267 oe_msg_pub.count_and_get
268 ( p_count => x_msg_count
269 , p_data => x_msg_data
270 );
271
272 END Save_Lines;
273
274
275 PROCEDURE Prepare_Lines_Dff_For_Save
276 (x_return_status OUT NOCOPY VARCHAR2
277 , x_msg_count OUT NOCOPY NUMBER
278 , x_msg_data OUT NOCOPY VARCHAR2
279 , x_line_dff_tbl IN Oe_Oe_Html_Line_Ext.Line_Dff_Tbl_Type
280 )
281 IS
282 BEGIN
283
284
285 G_line_dff_tbl :=x_line_dff_tbl;
286
287 END Prepare_Lines_Dff_For_Save;
288
289 PROCEDURE GET_LINE_SHIPMENT_NUMBER(
290 x_return_status OUT NOCOPY VARCHAR2
291 , p_header_id IN Number
292 , x_line_number OUT NOCOPY Number
293 , x_shipment_number OUT NOCOPY Number
294 ) IS
295 l_line_number Number;
296 --
297 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
298 --
299 BEGIN
300 -- Also this will need to be enhanced to support manual line numbers.
301 -- For Manual Line Numbers, we will also have to check that the line
302 -- number generated doesn't conflict with the manual number that is
303 -- entered by the user.
304
305 IF G_LINE_NUMBER IS NULL THEN
306 SELECT NVL(MAX(LINE_NUMBER)+1,1)
307 INTO x_line_number
308 FROM OE_ORDER_LINES_ALL
309 WHERE HEADER_ID = p_header_id;
310 l_line_number:=x_line_number;
311 G_LINE_NUMBER:=x_line_number;
312 IF x_line_number IS NOT NULL THEN
313 SELECT NVL(MAX(SHIPMENT_NUMBER)+1,1)
314 INTO x_shipment_number
315 FROM OE_ORDER_LINES
316 WHERE HEADER_ID = p_header_id
317 AND LINE_NUMBER = l_line_number;
318 G_SHIPMENT_NUMBER:=x_shipment_number;
319 END IF;
320 ELSE
321 G_LINE_NUMBER:=G_LINE_NUMBER+1;
322 -- G_SHIPMENT_NUMBER:=G_SHIPMENT_NUMBER+1;
323 x_line_number:=G_LINE_NUMBER;
324 x_shipment_number:=G_SHIPMENT_NUMBER;
325 END IF;
326 x_return_status := FND_API.G_RET_STS_SUCCESS;
327 EXCEPTION
328
329 WHEN OTHERS THEN
330
331 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
332 THEN
333 OE_MSG_PUB.Add_Exc_Msg
334 ( G_PKG_NAME
335 , 'Get_Line_Shipment_Number'
336 );
337 END IF;
338 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
339
340 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
341
342 END GET_LINE_SHIPMENT_NUMBER;
343
344
345 Procedure Populate_Transient_Attributes
346 (
347 P_line_rec IN Oe_Order_Pub.line_rec_type
348 , x_line_val_rec OUT NOCOPY /* file.sql.39 change */ line_Ext_Val_Rec_Type
349 , x_return_status OUT NOCOPY VARCHAR2
350 , x_msg_count OUT NOCOPY NUMBER
351 , x_msg_data OUT NOCOPY VARCHAR2
352 ) IS
353
354 CURSOR c_line_info IS
355
356 select decode(line.line_Number,null,null,line.line_number)||
357 decode(line.shipment_Number,null,null,'.'||line.shipment_number)||
358 decode(line.option_number,null,null,'.'||line.option_number) line_num,
359 line.unit_selling_price*decode(line.line_category_code,'RETURN',-1,1) unit_selling_price,
360 (nvl(line.ordered_quantity,line.ordered_quantity)*line.unit_selling_price)*decode(line.line_category_code,'RETURN',-1,1) extended_price,
361 DECODE(line.ITEM_IDENTIFIER_TYPE, 'CUST', NVL(C.CUSTOMER_ITEM_DESC,ITEMSTL.DESCRIPTION), 'INT', ITEMSTL.DESCRIPTION, null, ITEMSTL.DESCRIPTION, NVL(REF.DESCRIPTION,ITEMSTL.DESCRIPTION)) ITEM_DESCRIPTION
362 from oe_order_lines_all line,
363 mtl_system_items_tl itemstl,
364 mtl_customer_items c,
365 mtl_cross_references ref,
366 mtl_system_items_b_kfv items
367 Where line.inventory_item_id =items.inventory_item_id(+)
368 and line.line_id= p_line_rec.line_id
369 and oe_sys_parameters.value('MASTER_ORGANIZATION_ID') = items.organization_id
370 and items.organization_id = itemstl.organization_id
371 and items.inventory_item_id =itemstl.inventory_item_id
372 and itemstl.language = userenv('LANG')
373 and line.item_identifier_type = ref.cross_reference_type(+)
374 and line.ordered_item = ref.cross_reference(+)
375 and line.inventory_item_id = ref.inventory_item_id(+)
376 and line.ordered_item_id = c.customer_item_id(+);
377 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
378
379 BEGIN
380
381 x_return_status := FND_API.G_RET_STS_SUCCESS;
382
383 OPEN c_line_info;
384 FETCH c_line_info
385 INTO x_line_val_rec.line_number,
386 x_line_val_rec.unit_selling_price,
387 x_line_val_rec.extended_price,
388 x_line_val_rec.item_description;
389 CLOSE c_line_info;
390
391 EXCEPTION
392
393 WHEN OTHERS THEN
394 IF l_debug_level > 0 THEN
395 oe_debug_pub.add( 'WHEN OTHERS OF CALL_MRP_ATP' ) ;
396 oe_debug_pub.add( 'CODE='||SQLCODE||' MSG='||SQLERRM ) ;
397 END IF;
398 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
399 -- Get message count and data
400 OE_MSG_PUB.Count_And_Get
401 ( p_count => x_msg_count
402 , p_data => x_msg_data
403 );
404
405
406 END populate_transient_attributes;
407
408
409
410 END Oe_Oe_Html_Line_Ext;