eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.QP_UPG_OE_PVT

Source


1 PACKAGE BODY QP_Upg_OE_PVT as
2 /* $Header: QPXIUOEB.pls 120.4 2005/09/20 12:41:41 hwong noship $ */
3 
4 PROCEDURE Upg_Price_Adj_OE_to_QP(  p_discount_id       IN NUMBER,
5                                    p_discount_line_id  IN NUMBER,
6                                    p_percent           IN NUMBER,
7                                    p_unit_list_price   IN NUMBER,
8                                    p_pricing_context   IN VARCHAR2,
9                                    p_line_id           IN NUMBER,
10                          	   x_output	      OUT NOCOPY /* file.sql.39 change */ PRICE_ADJ_REC_TYPE )
11 IS
12 
13      cursor oepadj is
14      select     /* + Ordered index(dmap QP_DISCOUNT_MAPPING_T1) */
15 		dmap.new_list_header_id,
16 		dmap.new_list_line_id,
17 		dmap.new_type,
18 		dmap.old_price_break_lines_low,
19 		dmap.old_price_break_lines_high,
20 		dmap.old_method_type_code,
21 		lh.source_system_code,
22 		ll.modifier_level_code,
23 		ll.pricing_group_sequence,
24 		ll.list_line_type_code,
25 		ll.override_flag,
26 		ll.pricing_phase_id,
27 		ll.charge_type_code,
28 		ll.charge_subtype_code,
29 		ll.list_line_no,
30 		ll.benefit_qty,
31 		ll.benefit_uom_code,
32 		ll.print_on_invoice_flag,
33 		ll.expiration_date
34 	from
35 		qp_discount_mapping dmap, qp_list_lines ll, qp_list_headers_b lh
36 	where 	p_discount_id     = dmap.old_discount_id
37 	and 	(p_discount_line_id   = dmap.old_discount_line_id  or
38 		p_discount_line_id is null or p_discount_line_id = -1)
39 	and  dmap.new_list_header_id  = ll.list_header_id
40 	and  dmap.new_list_line_id  = ll.list_line_id
41 	and  dmap.new_list_header_id  = lh.list_header_id
42 	and  (p_pricing_context = dmap.pricing_context
43 	     or dmap.pricing_context is null);
44 
45      cursor price_breaks(p_line_id NUMBER, p_discount_id NUMBER) is
46      select
47 		dmap.old_discount_id,
48 		dmap.new_list_header_id,
49 		dmap.new_list_line_id,
50 		dmap.old_price_break_lines_low,
51           	dmap.old_price_break_lines_high,
52 		dmap.old_method_type_code,
53 		dmap.new_type,
54 		ll.list_line_type_code,
55           	ll.override_flag,
56 		oeordl.ordered_quantity
57      from
58 		qp_discount_mapping dmap, oe_order_lines_all oeordl, qp_list_lines ll
59      where
60             (	dmap.old_discount_id = p_discount_id  and
61 		oeordl.line_id = p_line_id and
62 		dmap.new_list_line_id = ll.list_line_id  and
63 		dmap.new_type = 'B'  and
64 		decode( dmap.old_method_type_code,
65 			'UNITS', oeordl.ordered_quantity,
66 			'DOLLARS', oeordl.ordered_quantity * oeordl.unit_list_price,
67 			0)
68 		between nvl( dmap.old_price_break_lines_low,
69 			     decode( dmap.old_method_type_code,
70 			     	     'UNITS', oeordl.ordered_quantity,
71 				     'DOLLARS', oeordl.ordered_quantity * oeordl.unit_list_price,
72 				     0)) and
73 		nvl( dmap.old_price_break_lines_high,
74                              decode( dmap.old_method_type_code,
75                                      'UNITS', oeordl.ordered_quantity,
76                                      'DOLLARS', oeordl.ordered_quantity * oeordl.unit_list_price,
77                                      0))
78             );
79 
80 
81      	v_modified_from	number;
82      	v_updated_flag	varchar2(1);
83      	v_applied_flag	varchar2(1);
84      	v_operator	varchar2(30);
85      	v_id1		number;
86      	v_err_msg	varchar2(2000);
87 		qppadj          oepadj%ROWTYPE;
88      	qpprice_breaks  price_breaks%ROWTYPE;
89 
90 
91      BEGIN
92 
93 	v_modified_from := 0;
94 	v_updated_flag  := 'N';
95 	v_applied_flag  := 'Y';
96 
97 	OPEN oepadj;
98 
99 	FETCH oepadj INTO qppadj;
100 
101 
102 	IF  qppadj.new_type = 'B' THEN
103 		open price_breaks( p_line_id, p_discount_id);
104 		fetch price_breaks into qpprice_breaks;
105 
106 		qppadj.new_list_line_id := qpprice_breaks.new_list_line_id;
107 		qppadj.list_line_type_code := qpprice_breaks.list_line_type_code;
108 		qppadj.override_flag := qpprice_breaks.override_flag;
109 
110 		close price_breaks;
111 	END IF;
112 
113 	x_output.list_header_id 	:=	qppadj.new_list_header_id;
114 	x_output.list_line_id		:=	qppadj.new_list_line_id;
115 	x_output.list_line_type_code	:=	qppadj.list_line_type_code;
116 	x_output.modified_from		:=	v_modified_from;
117 	x_output.modified_to		:=	p_percent;
118 	x_output.update_allowed		:=	qppadj.override_flag;
119 	x_output.operand		:=	p_percent;
120 	x_output.updated_flag		:=	v_updated_flag;
121 	x_output.applied_flag		:=	v_applied_flag;
122 	x_output.arithmetic_operator	:=	'%';
123 	x_output.price_break_type_code	:=	'POINT';
124 	x_output.adjusted_amount	:=	p_percent * p_unit_list_price / 100;
125 	x_output.pricing_phase_id	:=	qppadj.pricing_phase_id;
126 	x_output.charge_type_code	:=	qppadj.charge_type_code;
127 	x_output.charge_subtype_code	:=	qppadj.charge_subtype_code;
128 	x_output.list_line_no		:=	qppadj.list_line_no;
129 	x_output.source_system_code	:=	qppadj.source_system_code;
130 	x_output.benefit_qty		:=	qppadj.benefit_qty;
131 	x_output.benefit_uom_code	:=	qppadj.benefit_uom_code;
132 	x_output.print_on_invoice_flag  :=	qppadj.print_on_invoice_flag;
133 	x_output.modifier_level_code  :=	qppadj.modifier_level_code;
134 	x_output.pricing_group_sequence  := qppadj.pricing_group_sequence;
135 	x_output.expiration_date	:=	qppadj.expiration_date;
136 
137 
138 	EXCEPTION
139 	 WHEN OTHERS THEN
140 	   	v_err_msg := SQLERRM;
141 	   	rollback;
142 	   	QP_Util.Log_Error(p_id1 => p_line_id,
143 				  p_error_type =>'PRICE_ADJUSTMENT',
144 		   		  p_error_desc => v_err_msg,
145 				  p_error_module => 'Upg_Price_Adj_OE_to_QP');
146 		raise;
147 
148 end Upg_Price_Adj_OE_to_QP;
149 
150 
151 PROCEDURE Upg_Pricing_Attribs
152 IS
153 
154  v_order_price_attrib_id	NUMBER;
155  v_err_msg			varchar2(2000);
156 
157  begin
158 
159 	IF (OE_Upg_SO_NEW.g_line_rec.pricing_attribute1 IS NOT NULL)  OR
160 	   (OE_Upg_SO_NEW.g_line_rec.pricing_attribute2 IS NOT NULL)  OR
161 	   (OE_Upg_SO_NEW.g_line_rec.pricing_attribute3 IS NOT NULL)  OR
162 	   (OE_Upg_SO_NEW.g_line_rec.pricing_attribute4 IS NOT NULL)  OR
163 	   (OE_Upg_SO_NEW.g_line_rec.pricing_attribute5 IS NOT NULL)  OR
164 	   (OE_Upg_SO_NEW.g_line_rec.pricing_attribute6 IS NOT NULL)  OR
165 	   (OE_Upg_SO_NEW.g_line_rec.pricing_attribute7 IS NOT NULL)  OR
166 	   (OE_Upg_SO_NEW.g_line_rec.pricing_attribute8 IS NOT NULL)  OR
167 	   (OE_Upg_SO_NEW.g_line_rec.pricing_attribute9 IS NOT NULL)  OR
168 	   (OE_Upg_SO_NEW.g_line_rec.pricing_attribute10 IS NOT NULL) OR
169 	   (OE_Upg_SO_NEW.g_line_rec.pricing_attribute11 IS NOT NULL) OR
170 	   (OE_Upg_SO_NEW.g_line_rec.pricing_attribute12 IS NOT NULL) OR
171 	   (OE_Upg_SO_NEW.g_line_rec.pricing_attribute13 IS NOT NULL) OR
172 	   (OE_Upg_SO_NEW.g_line_rec.pricing_attribute14 IS NOT NULL) OR
173 	   (OE_Upg_SO_NEW.g_line_rec.pricing_attribute15 IS NOT NULL)
174 
175 	THEN
176 		SELECT OE_ORDER_PRICE_ATTRIBS_S.nextval
177 		INTO v_order_price_attrib_id
178 		FROM dual;
179 
180 		insert into oe_order_price_attribs
181 		(	header_id,
182 			line_id,
183 			creation_date,
184 			created_by,
185 			last_update_date,
186 			last_updated_by,
187 			last_update_login,
188 			program_application_id,
189 			program_id,
190 			program_update_date,
191 			request_id,
192 			pricing_context,
193 			pricing_attribute1,
194 			pricing_attribute2,
195 			pricing_attribute3,
196 			pricing_attribute4,
197 			pricing_attribute5,
198 			pricing_attribute6,
199 			pricing_attribute7,
200 			pricing_attribute8,
201 			pricing_attribute9,
202 			pricing_attribute10,
203 			pricing_attribute11,
204 			pricing_attribute12,
205 			pricing_attribute13,
206 			pricing_attribute14,
207 			pricing_attribute15,
208 			flex_title,
209 			order_price_attrib_id,
210 			override_flag	,
211 			lock_control)
212 		values
213 		(	OE_Upg_SO_NEW.g_line_rec.header_id,
214 			OE_Upg_SO_NEW.g_line_rec.line_id,
215 			SYSDATE,
216 			FND_GLOBAL.USER_ID,
217 			SYSDATE,
218 			FND_GLOBAL.USER_ID,
219 			FND_GLOBAL.LOGIN_ID,
220 			OE_Upg_SO_NEW.g_line_rec.program_application_id,
221 			OE_Upg_SO_NEW.g_line_rec.program_id,
222 			OE_Upg_SO_NEW.g_line_rec.program_update_date,
223 			OE_Upg_SO_NEW.g_line_rec.request_id,
224 			NVL(OE_Upg_SO_NEW.g_line_rec.pricing_context,'Upgrade Context'),
225 			OE_Upg_SO_NEW.g_line_rec.pricing_attribute1,
226 			OE_Upg_SO_NEW.g_line_rec.pricing_attribute2,
227 			OE_Upg_SO_NEW.g_line_rec.pricing_attribute3,
228 			OE_Upg_SO_NEW.g_line_rec.pricing_attribute4,
229 			OE_Upg_SO_NEW.g_line_rec.pricing_attribute5,
230 			OE_Upg_SO_NEW.g_line_rec.pricing_attribute6,
231 			OE_Upg_SO_NEW.g_line_rec.pricing_attribute7,
232 			OE_Upg_SO_NEW.g_line_rec.pricing_attribute8,
233 			OE_Upg_SO_NEW.g_line_rec.pricing_attribute9,
234 			OE_Upg_SO_NEW.g_line_rec.pricing_attribute10,
235 			OE_Upg_SO_NEW.g_line_rec.pricing_attribute11,
236 			OE_Upg_SO_NEW.g_line_rec.pricing_attribute12,
237 			OE_Upg_SO_NEW.g_line_rec.pricing_attribute13,
238 			OE_Upg_SO_NEW.g_line_rec.pricing_attribute14,
239 			OE_Upg_SO_NEW.g_line_rec.pricing_attribute15,
240 			'QP_ATTR_DEFNS_PRICING',
241 			v_order_price_attrib_id,
242 			'N',
243 			1);
244 	END IF;
245 
246 		EXCEPTION
247 	 		WHEN OTHERS THEN
248 	   			v_err_msg := SQLERRM;
249 	   			rollback;
250 	   			QP_Util.Log_Error(p_id1 => v_order_price_attrib_id,
251 				  		  p_error_type =>'PRICE_ATTRIBUTES',
252 		   		  		  p_error_desc => v_err_msg,
253 				  		  p_error_module => 'Upg_Pricing_Attribs');
254 				raise;
255 
256 
257  end Upg_Pricing_Attribs;
258 
259 PROCEDURE Upg_Pricing_Attribs(p_line_rec IN OE_Order_PUB.Line_Rec_Type)
260 IS
261 
262  v_order_price_attrib_id	NUMBER;
263  v_err_msg			varchar2(2000);
264 
265  begin
266 
267 	IF (p_line_rec.pricing_attribute1 IS NOT NULL)  OR
268 	   (p_line_rec.pricing_attribute2 IS NOT NULL)  OR
269 	   (p_line_rec.pricing_attribute3 IS NOT NULL)  OR
270 	   (p_line_rec.pricing_attribute4 IS NOT NULL)  OR
271 	   (p_line_rec.pricing_attribute5 IS NOT NULL)  OR
272 	   (p_line_rec.pricing_attribute6 IS NOT NULL)  OR
273 	   (p_line_rec.pricing_attribute7 IS NOT NULL)  OR
274 	   (p_line_rec.pricing_attribute8 IS NOT NULL)  OR
275 	   (p_line_rec.pricing_attribute9 IS NOT NULL)  OR
276 	   (p_line_rec.pricing_attribute10 IS NOT NULL) OR
277 	   (oe_upg_so_new.g_line_rec.pricing_attribute11 IS NOT NULL) OR
278 	   (oe_upg_so_new.g_line_rec.pricing_attribute12 IS NOT NULL) OR
279 	   (oe_upg_so_new.g_line_rec.pricing_attribute13 IS NOT NULL) OR
280 	   (oe_upg_so_new.g_line_rec.pricing_attribute14 IS NOT NULL) OR
281 	   (oe_upg_so_new.g_line_rec.pricing_attribute15 IS NOT NULL)
282 
283 	THEN
284 
285 		SELECT OE_ORDER_PRICE_ATTRIBS_S.nextval
286 		INTO v_order_price_attrib_id
287 		FROM dual;
288 
289 		insert into oe_order_price_attribs
290 		(	header_id,
291 			line_id,
292 			creation_date,
293 			created_by,
294 			last_update_date,
295 			last_updated_by,
296 			last_update_login,
297 			program_application_id,
298 			program_id,
299 			program_update_date,
300 			request_id,
301 			pricing_context,
302 			pricing_attribute1,
303 			pricing_attribute2,
304 			pricing_attribute3,
305 			pricing_attribute4,
306 			pricing_attribute5,
307 			pricing_attribute6,
308 			pricing_attribute7,
309 			pricing_attribute8,
310 			pricing_attribute9,
311 			pricing_attribute10,
312 			pricing_attribute11,
313 			pricing_attribute12,
314 			pricing_attribute13,
315 			pricing_attribute14,
316 			pricing_attribute15,
317 			flex_title,
318 			order_price_attrib_id,
319 			override_flag	,
320 			lock_control)
321 		values
322 		(	p_line_rec.header_id,
323 			p_line_rec.line_id,
324 			SYSDATE,
325 			FND_GLOBAL.USER_ID,
326 			SYSDATE,
327 			FND_GLOBAL.USER_ID,
328 			FND_GLOBAL.LOGIN_ID,
329 			p_line_rec.program_application_id,
330 			p_line_rec.program_id,
331 			p_line_rec.program_update_date,
332 			p_line_rec.request_id,
333 			NVL(p_line_rec.pricing_context,'Upgrade Context'),
334 			p_line_rec.pricing_attribute1,
335 			p_line_rec.pricing_attribute2,
336 			p_line_rec.pricing_attribute3,
337 			p_line_rec.pricing_attribute4,
338 			p_line_rec.pricing_attribute5,
339 			p_line_rec.pricing_attribute6,
340 			p_line_rec.pricing_attribute7,
341 			p_line_rec.pricing_attribute8,
342 			p_line_rec.pricing_attribute9,
343 			p_line_rec.pricing_attribute10,
344 			oe_upg_so_new.g_line_rec.pricing_attribute11,
345 			oe_upg_so_new.g_line_rec.pricing_attribute12,
346 			oe_upg_so_new.g_line_rec.pricing_attribute13,
347 			oe_upg_so_new.g_line_rec.pricing_attribute14,
348 			oe_upg_so_new.g_line_rec.pricing_attribute15,
349 			'QP_ATTR_DEFNS_PRICING',
350 			v_order_price_attrib_id,
351 			'N',
352 			1);
353 	END IF;
354 
355 		EXCEPTION
356 	 		WHEN OTHERS THEN
357 	   			v_err_msg := SQLERRM;
358 	   			rollback;
359 	   			QP_Util.Log_Error(p_id1 => v_order_price_attrib_id,
360 				  		  p_error_type =>'PRICE_ATTRIBUTES',
361 		   		  		  p_error_desc => v_err_msg,
362 				  		  p_error_module => 'Upg_Pricing_Attribs');
363 				raise;
364 
365 
366  end Upg_Pricing_Attribs;
367 
368 
369 
370 PROCEDURE Upg_Pricing_Attribs (p_upg_line_rec IN OE_UPG_SO_NEW.LINE_REC_TYPE)
371 IS
372 
373  v_order_price_attrib_id	NUMBER;
374  v_err_msg			varchar2(2000);
375 
376  begin
377 
378 	IF (p_upg_line_rec.pricing_attribute1 IS NOT NULL)  OR
379 	   (p_upg_line_rec.pricing_attribute2 IS NOT NULL)  OR
380 	   (p_upg_line_rec.pricing_attribute3 IS NOT NULL)  OR
381 	   (p_upg_line_rec.pricing_attribute4 IS NOT NULL)  OR
382 	   (p_upg_line_rec.pricing_attribute5 IS NOT NULL)  OR
383 	   (p_upg_line_rec.pricing_attribute6 IS NOT NULL)  OR
384 	   (p_upg_line_rec.pricing_attribute7 IS NOT NULL)  OR
385 	   (p_upg_line_rec.pricing_attribute8 IS NOT NULL)  OR
386 	   (p_upg_line_rec.pricing_attribute9 IS NOT NULL)  OR
387 	   (p_upg_line_rec.pricing_attribute10 IS NOT NULL) OR
388 	   (p_upg_line_rec.pricing_attribute11 IS NOT NULL) OR
389 	   (p_upg_line_rec.pricing_attribute12 IS NOT NULL) OR
390 	   (p_upg_line_rec.pricing_attribute13 IS NOT NULL) OR
391 	   (p_upg_line_rec.pricing_attribute14 IS NOT NULL) OR
392 	   (p_upg_line_rec.pricing_attribute15 IS NOT NULL)
393 
394 	THEN
395 		SELECT OE_ORDER_PRICE_ATTRIBS_S.nextval
396 		INTO v_order_price_attrib_id
397 		FROM dual;
398 
399 		insert into oe_order_price_attribs
400 		(	header_id,
401 			line_id,
402 			creation_date,
403 			created_by,
404 			last_update_date,
405 			last_updated_by,
406 			last_update_login,
407 			program_application_id,
408 			program_id,
409 			program_update_date,
410 			request_id,
411 			pricing_context,
412 			pricing_attribute1,
413 			pricing_attribute2,
414 			pricing_attribute3,
415 			pricing_attribute4,
416 			pricing_attribute5,
417 			pricing_attribute6,
418 			pricing_attribute7,
419 			pricing_attribute8,
420 			pricing_attribute9,
421 			pricing_attribute10,
422 			pricing_attribute11,
423 			pricing_attribute12,
424 			pricing_attribute13,
425 			pricing_attribute14,
426 			pricing_attribute15,
427 			flex_title,
428 			order_price_attrib_id,
429 			override_flag	,
430 			lock_control)
431 		values
432 		(	p_upg_line_rec.header_id,
433 			p_upg_line_rec.line_id,
434 			SYSDATE,
435 			FND_GLOBAL.USER_ID,
436 			SYSDATE,
437 			FND_GLOBAL.USER_ID,
438 			FND_GLOBAL.LOGIN_ID,
439 			p_upg_line_rec.program_application_id,
440 			p_upg_line_rec.program_id,
441 			p_upg_line_rec.program_update_date,
442 			p_upg_line_rec.request_id,
443 			NVL(p_upg_line_rec.pricing_context,'Upgrade Context'),
444 			p_upg_line_rec.pricing_attribute1,
445 			p_upg_line_rec.pricing_attribute2,
446 			p_upg_line_rec.pricing_attribute3,
447 			p_upg_line_rec.pricing_attribute4,
448 			p_upg_line_rec.pricing_attribute5,
449 			p_upg_line_rec.pricing_attribute6,
450 			p_upg_line_rec.pricing_attribute7,
451 			p_upg_line_rec.pricing_attribute8,
452 			p_upg_line_rec.pricing_attribute9,
453 			p_upg_line_rec.pricing_attribute10,
454 			p_upg_line_rec.pricing_attribute11,
455 			p_upg_line_rec.pricing_attribute12,
456 			p_upg_line_rec.pricing_attribute13,
457 			p_upg_line_rec.pricing_attribute14,
458 			p_upg_line_rec.pricing_attribute15,
459 			'QP_ATTR_DEFNS_PRICING',
460 			v_order_price_attrib_id,
461 			'N',
462 			1);
463 	END IF;
464 
465 		EXCEPTION
466 	 		WHEN OTHERS THEN
467 	   			v_err_msg := SQLERRM;
468 	   			rollback;
469 	   			QP_Util.Log_Error(p_id1 => v_order_price_attrib_id,
470 				  		  p_error_type =>'PRICE_ATTRIBUTES',
471 		   		  		  p_error_desc => v_err_msg,
472 				  		  p_error_module => 'Upg_Pricing_Attribs');
473 				raise;
474 
475 
476  end Upg_Pricing_Attribs;
477 
478 
479 END QP_Upg_OE_PVT;