[Home] [Help]
PACKAGE BODY: APPS.QP_UTIL_PUB
Source
1 PACKAGE BODY Qp_Util_Pub AS
2 /* $Header: QPXRTCNB.pls 120.19.12020000.3 2012/12/19 07:24:38 smbalara ship $ */
3
4 l_debug VARCHAR2(3);
5 /************************************************************************
6 Utility procedure to update the qp_limit_balances table as an autonomous
7 transaction. This procedure is called by the Reverse_Limits procedure below.
8 *************************************************************************/
9
10 PROCEDURE Update_Balance(p_new_trxn_amount IN NUMBER,
11 p_old_trxn_amount IN NUMBER,
12 p_limit_balance_id IN NUMBER)
13 IS
14 PRAGMA AUTONOMOUS_TRANSACTION;
15 BEGIN
16
17 IF l_debug = Fnd_Api.G_TRUE THEN
18 Qp_Preq_Grp.engine_debug('***Begin Update_Balance***');
19
20 END IF;
21 UPDATE qp_limit_balances
22 SET available_amount = available_amount - p_new_trxn_amount +
23 p_old_trxn_amount,
24 last_update_date = SYSDATE,
25 last_updated_by = Fnd_Global.user_id
26 WHERE limit_balance_id = p_limit_balance_id;
27
28 COMMIT;
29
30 IF l_debug = Fnd_Api.G_TRUE THEN
31 Qp_Preq_Grp.engine_debug('***End Update_Balance***');
32
33 END IF;
34 END Update_Balance;
35
36
37 /***********************************************************************
38 Procedure to Reverse the Limit Balances and Transactions for a return
39 or cancellation.(Public API).
40 ***********************************************************************/
41 PROCEDURE Reverse_Limits (p_action_code IN VARCHAR2,
42 p_cons_price_request_code IN VARCHAR2,
43 p_orig_ordered_qty IN NUMBER DEFAULT NULL,
44 p_amended_qty IN NUMBER DEFAULT NULL,
45 p_ret_price_request_code IN VARCHAR2 DEFAULT NULL,
46 p_returned_qty IN NUMBER DEFAULT NULL,
47 x_return_status OUT NOCOPY VARCHAR2,
48 x_return_message OUT NOCOPY VARCHAR2)
49 IS
50
51 CURSOR trans_cur(a_cons_price_request_code VARCHAR2)
52 IS
53 SELECT limit_balance_id, list_header_id, list_line_id,
54 price_request_type_code, price_request_code,
55 pricing_phase_id, amount
56 FROM qp_limit_transactions
57 WHERE price_request_code = a_cons_price_request_code;
58
59 l_proration NUMBER;
60 l_returned_amount NUMBER;
61 l_consumed_amount NUMBER;
62 --bug 13521835
63 amount_rounded NUMBER;
64 l_basis VARCHAR2(30);
65 --bug 13521835
66 BEGIN
67
68 l_debug := Qp_Preq_Grp.G_DEBUG_ENGINE;
69 IF l_debug = Fnd_Api.G_TRUE THEN
70 Qp_Preq_Grp.engine_debug('***Begin Procedure Reverse_Limit *** ');
71
72 END IF;
73 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
74
75 --Price Request Code corresponding to the consuming order line is mandatory.
76 IF p_cons_price_request_code IS NULL THEN
77 Fnd_Message.SET_NAME('QP','QP_PARAMETER_REQUIRED');
78 Fnd_Message.SET_TOKEN('PARAMETER',p_cons_price_request_code);
79 x_return_message := Fnd_Message.GET;
80 RAISE Fnd_Api.G_EXC_ERROR;
81
82 END IF;
83
84 --Action Code is mandatory.
85 IF p_action_code IS NULL THEN
86
87 Fnd_Message.SET_NAME('QP','QP_PARAMETER_REQUIRED');
88 Fnd_Message.SET_TOKEN('PARAMETER',p_action_code);
89 x_return_message := Fnd_Message.GET;
90 RAISE Fnd_Api.G_EXC_ERROR;
91
92 END IF;
93
94 --Originally ordered qty must be not-null and non-zero.
95 IF p_action_code <> 'CANCEL' AND
96 (p_orig_ordered_qty IS NULL OR p_orig_ordered_qty = 0) THEN
97
98 Fnd_Message.SET_NAME('QP','QP_NONZERO_PARAMETER_REQD');
99 Fnd_Message.SET_TOKEN('PARAMETER',p_orig_ordered_qty);
100 x_return_message := Fnd_Message.GET;
101 RAISE Fnd_Api.G_EXC_ERROR;
102
103 END IF;
104
105 --If Action Code is 'RETURN' then the returned qty must be not null.
106 IF p_action_code = 'RETURN' AND p_returned_qty IS NULL THEN
107
108 Fnd_Message.SET_NAME('QP','QP_PARAMETER_REQUIRED');
109 Fnd_Message.SET_TOKEN('PARAMETER',p_returned_qty);
110 x_return_message := Fnd_Message.GET;
111 RAISE Fnd_Api.G_EXC_ERROR;
112
113 END IF;
114
115 --If Action Code is 'RETURN' then the price_request_code of the return
116 --line must be not null.
117 IF p_action_code = 'RETURN' AND p_ret_price_request_code IS NULL THEN
118
119 Fnd_Message.SET_NAME('QP','QP_PARAMETER_REQUIRED');
120 Fnd_Message.SET_TOKEN('PARAMETER',p_ret_price_request_code);
121 x_return_message := Fnd_Message.GET;
122 RAISE Fnd_Api.G_EXC_ERROR;
123
124 END IF;
125
126 --If Action Code is 'AMEND' then the amended qty must be not null.
127 IF p_action_code = 'AMEND' AND p_amended_qty IS NULL THEN
128
129 Fnd_Message.SET_NAME('QP','QP_PARAMETER_REQUIRED');
130 Fnd_Message.SET_TOKEN('PARAMETER',p_amended_qty);
131 x_return_message := Fnd_Message.GET;
132 RAISE Fnd_Api.G_EXC_ERROR;
133
134 END IF;
135
136 --If Action Code is 'AMEND' then the amended qty must not be greater
137 --than the orignally ordered qty.
138 IF p_action_code = 'AMEND' AND p_amended_qty > p_orig_ordered_qty THEN
139
140 Fnd_Message.SET_NAME('QP','QP_PARAMETER_MUST_BE_LESSER');
141 Fnd_Message.SET_TOKEN('PARAMETER1',p_amended_qty);
142 Fnd_Message.SET_TOKEN('PARAMETER2',p_orig_ordered_qty);
143 x_return_message := Fnd_Message.GET;
144 RAISE Fnd_Api.G_EXC_ERROR;
145
146 END IF;
147
148 --Proration Ratio Calculation
149 IF p_action_code = 'CANCEL' THEN
150 l_proration := 0;
151 ELSIF p_action_code = 'RETURN' THEN
152 l_proration := -1 * (p_returned_qty/p_orig_ordered_qty); --Sign Change
153 ELSIF p_action_code = 'AMEND' THEN
154 l_proration := p_amended_qty/p_orig_ordered_qty;
155 --bug#7540503
156 ELSIF p_action_code = 'SPLIT_ORIG' THEN
157 l_proration := p_amended_qty/p_orig_ordered_qty;
158 ELSIF p_action_code = 'SPLIT_NEW' THEN
159 l_proration := p_returned_qty/p_orig_ordered_qty;
160 --bug#7540503
161 ELSE
162 Fnd_Message.SET_NAME('QP','QP_INVALID_ACTION_CODE');
163 x_return_message := Fnd_Message.GET;
164 RAISE Fnd_Api.G_EXC_ERROR;
165 END IF;
166
167
168 --For each Limit Transaction Record for Consuming line's price_request_code,
169 --insert or update a return limit transaction record(if action_code is RETURN)
170 --or update the consuming record if the action_code is AMEND or CANCEL.
171
172 FOR l_cons_trans_rec IN trans_cur(p_cons_price_request_code)
173 LOOP
174
175 IF p_action_code = 'RETURN' THEN
176
177 BEGIN
178 --Check if a return record exists.
179 SELECT amount
180 INTO l_returned_amount
181 FROM qp_limit_transactions
182 WHERE price_request_code = p_ret_price_request_code
183 AND list_header_id = l_cons_trans_rec.list_header_id
184 AND list_line_id = l_cons_trans_rec.list_line_id
185 AND limit_balance_id = l_cons_trans_rec.limit_balance_id;
186
187 EXCEPTION
188 WHEN NO_DATA_FOUND THEN
189
190 --Record does not already exist,insert a new return trxn record
191 INSERT INTO qp_limit_transactions
192 (limit_balance_id,
193 creation_date,
194 created_by,
195 last_update_date,
196 last_updated_by,
197 list_header_id,
198 list_line_id,
199 price_request_date,
200 price_request_type_code,
201 price_request_code,
202 pricing_phase_id,
203 amount
204 )
205 VALUES
206 (l_cons_trans_rec.limit_balance_id,
207 SYSDATE,
208 Fnd_Global.user_id,
209 SYSDATE,
210 Fnd_Global.user_id,
211 l_cons_trans_rec.list_header_id,
212 l_cons_trans_rec.list_line_id,
213 SYSDATE,
214 l_cons_trans_rec.price_request_type_code,
215 p_ret_price_request_code,
216 l_cons_trans_rec.pricing_phase_id,
217 l_proration * l_cons_trans_rec.amount
218 );
219
220 --Update Limit Balance record
221 Update_Balance(p_new_trxn_amount =>
222 l_proration * l_cons_trans_rec.amount,
223 p_old_trxn_amount => 0,
224 p_limit_balance_id => l_cons_trans_rec.limit_balance_id
225 );
226
227 GOTO next_in_loop; --To next record in loop
228
229 END; --Block around SELECT stmt to check if returned rec exists
230
231 --Return Transaction Record exists. Update trxn amount
232 UPDATE qp_limit_transactions
233 SET amount = (l_proration * l_cons_trans_rec.amount),
234 last_update_date = SYSDATE,
235 last_updated_by = Fnd_Global.user_id,
236 price_request_date = SYSDATE --bug#12715755
237 WHERE price_request_code = p_ret_price_request_code
238 AND list_header_id = l_cons_trans_rec.list_header_id
239 AND list_line_id = l_cons_trans_rec.list_line_id
240 AND limit_balance_id = l_cons_trans_rec.limit_balance_id;
241
242 --Update Limit_Balance record
243 Update_Balance(p_new_trxn_amount => l_proration * l_cons_trans_rec.amount,
244 p_old_trxn_amount => l_returned_amount,
245 p_limit_balance_id => l_cons_trans_rec.limit_balance_id);
246
247 ELSIF p_action_code IN ('CANCEL','AMEND') THEN
248 amount_rounded := l_proration * l_cons_trans_rec.amount; --13521835
249 BEGIN
250 --A record must exist for it to be cancelled or amended.
251 SELECT amount
252 INTO l_consumed_amount
253 FROM qp_limit_transactions
254 WHERE price_request_code = p_cons_price_request_code
255 AND list_header_id = l_cons_trans_rec.list_header_id
256 AND list_line_id = l_cons_trans_rec.list_line_id
257 AND limit_balance_id = l_cons_trans_rec.limit_balance_id;
258
259 EXCEPTION
260 WHEN NO_DATA_FOUND THEN
261 Fnd_Message.SET_NAME('QP','QP_LIMIT_TXN_NOT_FOUND');
262 x_return_message := Fnd_Message.GET;
263 RAISE Fnd_Api.G_EXC_ERROR;
264 END;
265 --Transaction Record to be cancelled/amended exists. Update trxn amount.
266 --bug 13521835
267 BEGIN
268 select ql.BASIS into l_basis
269 from qp_limits ql, qp_limit_balances qlb
270 where ql.list_header_id = l_cons_trans_rec.list_header_id
271 and ql.list_line_id = l_cons_trans_rec.list_line_id
272 and ql.limit_id = qlb.limit_id
273 and qlb.limit_balance_id = l_cons_trans_rec.limit_balance_id;
274
275 IF (l_basis='QUANTITY') then
276 amount_rounded := round(amount_rounded);
277 END IF;
278 EXCEPTION
279 WHEN OTHERS THEN
280 NULL;
281 END;
282 --bug 13521835
283
284 --Transaction Record to be cancelled/amended exists. Update trxn amount.
285 UPDATE qp_limit_transactions
286 SET amount = amount_rounded, --bug 13521835
287 last_update_date = SYSDATE,
288 last_updated_by = Fnd_Global.user_id,
289 price_request_date = sysdate --bug#12715755
290 WHERE price_request_code = p_cons_price_request_code
291 AND list_header_id = l_cons_trans_rec.list_header_id
292 AND list_line_id = l_cons_trans_rec.list_line_id
293 AND limit_balance_id = l_cons_trans_rec.limit_balance_id;
294
295 --Update Limit_Balance record
296 Update_Balance(p_new_trxn_amount =>amount_rounded, --bug13521835
297 p_old_trxn_amount => l_consumed_amount,
298 p_limit_balance_id => l_cons_trans_rec.limit_balance_id);
299
300 --bug#7540503
301 ELSIF p_action_code = 'SPLIT_NEW' THEN
302 amount_rounded := l_proration * l_cons_trans_rec.amount; --bug13521835
303 qp_Preq_Grp.engine_debug('SPLIT NEW amount_rounded :'||amount_rounded);
304 BEGIN
305 --Check if a split child record exists.
306 SELECT amount
307 INTO l_returned_amount
308 FROM qp_limit_transactions
309 WHERE price_request_code = p_ret_price_request_code
310 AND list_header_id = l_cons_trans_rec.list_header_id
311 AND list_line_id = l_cons_trans_rec.list_line_id
312 AND limit_balance_id = l_cons_trans_rec.limit_balance_id;
313
314 EXCEPTION
315 WHEN NO_DATA_FOUND THEN
316 --bug13521835
317 BEGIN
318 select ql.BASIS into l_basis
319 from qp_limits ql, qp_limit_balances qlb
320 where ql.list_header_id = l_cons_trans_rec.list_header_id
321 and ql.list_line_id = l_cons_trans_rec.list_line_id
322 and ql.limit_id = qlb.limit_id
323 and qlb.limit_balance_id = l_cons_trans_rec.limit_balance_id;
324
325 qp_Preq_Grp.engine_debug(' SPLIT NEW l_basis :'||l_basis);
326 IF (l_basis='QUANTITY') then
327 amount_rounded:= round(amount_rounded);
328 qp_Preq_Grp.engine_debug('SPLIT NEW amount_rounded :'||amount_rounded);
329 END IF;
330 EXCEPTION
331 WHEN OTHERS THEN
332 NULL;
333 END;
334 --bug13521835
335
336 --Record does not already exist,insert a new return trxn record
337 INSERT INTO qp_limit_transactions
338 (limit_balance_id,
339 creation_date,
340 created_by,
341 last_update_date,
342 last_updated_by,
343 list_header_id,
344 list_line_id,
345 price_request_date,
346 price_request_type_code,
347 price_request_code,
348 pricing_phase_id,
349 amount
350 )
351 VALUES
352 (l_cons_trans_rec.limit_balance_id,
353 sysdate,
354 fnd_global.user_id,
355 sysdate,
356 fnd_global.user_id,
357 l_cons_trans_rec.list_header_id,
358 l_cons_trans_rec.list_line_id,
359 sysdate,
360 l_cons_trans_rec.price_request_type_code,
361 p_ret_price_request_code,
362 l_cons_trans_rec.pricing_phase_id,
363 amount_rounded --bug13521835
364 );
365
366 --Update Limit Balance record
367 Update_Balance(p_new_trxn_amount =>
368 amount_rounded, --bug13521835
369 p_old_trxn_amount => 0,
370 p_limit_balance_id => l_cons_trans_rec.limit_balance_id
371 );
372
373 GOTO next_in_loop; --To next record in loop
374
375 END; --Block around SELECT stmt to check if split child rec exists
376
377 ELSIF p_action_code = 'SPLIT_ORIG' THEN
378 amount_rounded := l_proration * l_cons_trans_rec.amount; --bug13521835
379 qp_Preq_Grp.engine_debug('amount_rounded :'||amount_rounded);
380 BEGIN
381 --A record must exist for it to updated.
382 SELECT amount
383 INTO l_consumed_amount
384 FROM qp_limit_transactions
385 WHERE price_request_code = p_cons_price_request_code
386 AND list_header_id = l_cons_trans_rec.list_header_id
387 AND list_line_id = l_cons_trans_rec.list_line_id
388 AND limit_balance_id = l_cons_trans_rec.limit_balance_id;
389
390 EXCEPTION
391 WHEN NO_DATA_FOUND THEN
392 FND_MESSAGE.SET_NAME('QP','QP_LIMIT_TXN_NOT_FOUND');
393 x_return_message := FND_MESSAGE.GET;
394 RAISE FND_API.G_EXC_ERROR;
395 END;
396 qp_Preq_Grp.engine_debug('l_consumed_amount :'||l_consumed_amount);
397 --bug13521835
398 BEGIN
399 select ql.BASIS into l_basis
400 from qp_limits ql, qp_limit_balances qlb
401 where ql.list_header_id = l_cons_trans_rec.list_header_id
402 and ql.list_line_id = l_cons_trans_rec.list_line_id
403 and ql.limit_id = qlb.limit_id
404 and qlb.limit_balance_id = l_cons_trans_rec.limit_balance_id;
405
406 IF (l_basis='QUANTITY') then
407 amount_rounded := round(amount_rounded);
408 END IF;
409 EXCEPTION
410 WHEN OTHERS THEN
411 NULL;
412 END;
413 qp_Preq_Grp.engine_debug('l_basis :'||l_basis);
414 qp_Preq_Grp.engine_debug('amount_rounded :'||amount_rounded);
415 --bug13521835
416 --Transaction Record to be updated exists. Update trxn amount.
417 UPDATE qp_limit_transactions
418 SET amount = amount_rounded, --bug13521835
419 last_update_date = sysdate,
420 last_updated_by = fnd_global.user_id,
421 price_request_date = sysdate --bug#12715755
422 WHERE price_request_code = p_cons_price_request_code
423 AND list_header_id = l_cons_trans_rec.list_header_id
424 AND list_line_id = l_cons_trans_rec.list_line_id
425 AND limit_balance_id = l_cons_trans_rec.limit_balance_id;
426
427 --Update Limit_Balance record
428 Update_Balance(p_new_trxn_amount => amount_rounded, --bug13521835
429 p_old_trxn_amount => l_consumed_amount,
430 p_limit_balance_id => l_cons_trans_rec.limit_balance_id);
431 --bug#7540503
432
433 END IF; --If p_action_code is 'RETURN', 'CANCEL' or 'AMEND'
434
435 <<next_in_loop>>
436 NULL;
437 END LOOP;--Loop over limit trxn records of consuming price_request_code
438
439 IF l_debug = Fnd_Api.G_TRUE THEN
440 Qp_Preq_Grp.engine_debug('***End Procedure Reverse_Limit*** ');
441
442 END IF;
443 EXCEPTION
444 WHEN Fnd_Api.G_EXC_ERROR THEN
445 x_return_status := Fnd_Api.G_RET_STS_ERROR;
446
447 WHEN OTHERS THEN
448 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
449 x_return_message := SUBSTR(SQLERRM,1,2000);
450
451 END Reverse_Limits;
452
453 /***********************************************************************
454 Procedure to Determine how many lines to pass to the pricing engine
455 ***********************************************************************/
456
457 -- p_freight_call_flag added below for bug 3006670
458 PROCEDURE Get_Order_Lines_Status(p_event_code IN VARCHAR2,
459 x_order_status_rec OUT NOCOPY ORDER_LINES_STATUS_REC_TYPE,
460 p_freight_call_flag IN VARCHAR2 := 'N',
461 p_request_type_code IN VARCHAR2 DEFAULT NULL) IS
462
463 CURSOR l_all_lines_info_cur(p_event_code1 VARCHAR2) IS
464 SELECT 'X'
465 FROM qp_pricing_phases a , qp_event_phases b
466 WHERE a.pricing_phase_id = b.pricing_phase_id
467 AND (a.oid_exists = 'Y' OR a.line_group_exists = 'Y' OR a.rltd_exists = 'Y')
468 AND b.pricing_event_code IN (SELECT DECODE(ROWNUM
469 ,1 ,SUBSTR(p_event_code,1,INSTR(p_event_code1,',',1,1)-1)
470 ,2 ,SUBSTR(p_event_code , INSTR(p_event_code1,',',1,ROWNUM-1) + 1,
471 INSTR(p_event_code1,',',1,ROWNUM)-1 - INSTR(p_event_code1,',',1,ROWNUM-1))
472 ,3 ,SUBSTR(p_event_code , INSTR(p_event_code1,',',1,ROWNUM-1) + 1,
473 INSTR(p_event_code1,',',1,ROWNUM)-1 - INSTR(p_event_code1,',',1,ROWNUM-1))
474 ,4 ,SUBSTR(p_event_code , INSTR(p_event_code1,',',1,ROWNUM-1) + 1,
475 INSTR(p_event_code1,',',1,ROWNUM)-1 - INSTR(p_event_code1,',',1,ROWNUM-1))
476 ,5 ,SUBSTR(p_event_code , INSTR(p_event_code1,',',1,ROWNUM-1) + 1,
477 INSTR(p_event_code1,',',1,ROWNUM)-1 - INSTR(p_event_code1,',',1,ROWNUM-1))
478 ,6 ,SUBSTR(p_event_code , INSTR(p_event_code1,',',1,ROWNUM-1) + 1,
479 INSTR(p_event_code1,',',1,ROWNUM)-1 - INSTR(p_event_code1,',',1,ROWNUM-1)))
480 FROM qp_event_phases
481 WHERE ROWNUM < 7)
482 AND ROWNUM = 1;
483
484 /* For bug 3006670 (if p_freight_call_flag = 'Y')
485 * all_lines_flag should return 'N' if additional buylines exist for a PRG
486 * that means if rltd_exists='Y' but oid_exists and line_group_exists both ='N',
487 * then all_lines_flag should be 'N'
488 * (logically, this is equivalent to checking (oid_exists='Y' OR line_group='Y'))
489 * the l_all_lines_info_cur_freight cursor has been modified to reflect this
490 */
491 CURSOR l_all_lines_info_cur_freight(p_event_code1 VARCHAR2) IS
492 SELECT 'X'
493 FROM qp_pricing_phases a , qp_event_phases b
494 WHERE a.pricing_phase_id = b.pricing_phase_id
495 AND (a.oid_exists = 'Y' OR a.line_group_exists = 'Y')
496 AND b.pricing_event_code IN (SELECT DECODE(ROWNUM
497 ,1 ,SUBSTR(p_event_code,1,INSTR(p_event_code1,',',1,1)-1)
498 ,2 ,SUBSTR(p_event_code , INSTR(p_event_code1,',',1,ROWNUM-1) + 1,
499 INSTR(p_event_code1,',',1,ROWNUM)-1 - INSTR(p_event_code1,',',1,ROWNUM-1))
500 ,3 ,SUBSTR(p_event_code , INSTR(p_event_code1,',',1,ROWNUM-1) + 1,
501 INSTR(p_event_code1,',',1,ROWNUM)-1 - INSTR(p_event_code1,',',1,ROWNUM-1))
502 ,4 ,SUBSTR(p_event_code , INSTR(p_event_code1,',',1,ROWNUM-1) + 1,
503 INSTR(p_event_code1,',',1,ROWNUM)-1 - INSTR(p_event_code1,',',1,ROWNUM-1))
504 ,5 ,SUBSTR(p_event_code , INSTR(p_event_code1,',',1,ROWNUM-1) + 1,
505 INSTR(p_event_code1,',',1,ROWNUM)-1 - INSTR(p_event_code1,',',1,ROWNUM-1))
506 ,6 ,SUBSTR(p_event_code , INSTR(p_event_code1,',',1,ROWNUM-1) + 1,
507 INSTR(p_event_code1,',',1,ROWNUM)-1 - INSTR(p_event_code1,',',1,ROWNUM-1)))
508 FROM qp_event_phases
509 WHERE ROWNUM < 7)
510 AND ROWNUM = 1;
511
512 -- l_summary_line_info_cur and l_changed_lines_info_cur were combined into one
513 -- cursor for performance bug 3756506.
514 -- passing p_mod_level_code='ORDER' is equiv. to l_summary_line_info_cur
515 -- passing p_mod_level_code='LINE' is equiv. to l_changed_lines_info_cur
516 -- [julin/4676740] tuned EXISTS clause
517 CURSOR l_line_info_cur(p_event_code1 VARCHAR2, p_mod_level_code VARCHAR2) IS
518 SELECT /*+ leading(C) use_nl(c,b) */ 'X' --bug 12677276
519 FROM
520 (SELECT DECODE(ROWNUM
521 ,1 ,SUBSTR(p_event_code,1,INSTR(p_event_code1,',',1,1)-1)
522 ,2 ,SUBSTR(p_event_code , INSTR(p_event_code1,',',1,ROWNUM-1) + 1,
523 INSTR(p_event_code1,',',1,ROWNUM)-1 - INSTR(p_event_code1,',',1,ROWNUM-1))
524 ,3 ,SUBSTR(p_event_code , INSTR(p_event_code1,',',1,ROWNUM-1) + 1,
525 INSTR(p_event_code1,',',1,ROWNUM)-1 - INSTR(p_event_code1,',',1,ROWNUM-1))
526 ,4 ,SUBSTR(p_event_code , INSTR(p_event_code1,',',1,ROWNUM-1) + 1,
527 INSTR(p_event_code1,',',1,ROWNUM)-1 - INSTR(p_event_code1,',',1,ROWNUM-1))
528 ,5 ,SUBSTR(p_event_code , INSTR(p_event_code1,',',1,ROWNUM-1) + 1,
529 INSTR(p_event_code1,',',1,ROWNUM)-1 - INSTR(p_event_code1,',',1,ROWNUM-1))
530 ,6 ,SUBSTR(p_event_code , INSTR(p_event_code1,',',1,ROWNUM-1) + 1,
531 INSTR(p_event_code1,',',1,ROWNUM)-1 - INSTR(p_event_code1,',',1,ROWNUM-1))) PRICING_EVENT_CD
532 FROM qp_event_phases
533 WHERE pricing_phase_id > 1
534 AND ROWNUM < 7) C,
535 QP_EVENT_PHASES B
536 WHERE B.PRICING_EVENT_CODE = C.PRICING_EVENT_CD
537 AND EXISTS (SELECT /*+ no_push_subq no_unnest index(a QP_LIST_LINES_N4) */ 'x' --bug 12677276 14842950
538 FROM qp_list_lines a
539 WHERE a.pricing_phase_id = b.pricing_phase_id
540 AND a.modifier_level_code=p_mod_level_code
541 AND ROWNUM=1)
542 AND ROWNUM=1;
543
544 CURSOR l_line_info_ptess_cur(p_event_code1 VARCHAR2, p_mod_level_code VARCHAR2) IS
545 SELECT 'X'
546 FROM qp_event_phases b
547 WHERE b.pricing_event_code IN (SELECT DECODE(ROWNUM
548 ,1 ,SUBSTR(p_event_code,1,INSTR(p_event_code1,',',1,1)-1)
549 ,2 ,SUBSTR(p_event_code , INSTR(p_event_code1,',',1,ROWNUM-1) + 1,
550 INSTR(p_event_code1,',',1,ROWNUM)-1 - INSTR(p_event_code1,',',1,ROWNUM-1))
551 ,3 ,SUBSTR(p_event_code , INSTR(p_event_code1,',',1,ROWNUM-1) + 1,
552 INSTR(p_event_code1,',',1,ROWNUM)-1 - INSTR(p_event_code1,',',1,ROWNUM-1))
553 ,4 ,SUBSTR(p_event_code , INSTR(p_event_code1,',',1,ROWNUM-1) + 1,
554 INSTR(p_event_code1,',',1,ROWNUM)-1 - INSTR(p_event_code1,',',1,ROWNUM-1))
555 ,5 ,SUBSTR(p_event_code , INSTR(p_event_code1,',',1,ROWNUM-1) + 1,
556 INSTR(p_event_code1,',',1,ROWNUM)-1 - INSTR(p_event_code1,',',1,ROWNUM-1))
557 ,6 ,SUBSTR(p_event_code , INSTR(p_event_code1,',',1,ROWNUM-1) + 1,
558 INSTR(p_event_code1,',',1,ROWNUM)-1 - INSTR(p_event_code1,',',1,ROWNUM-1)))
559 FROM qp_event_phases
560 WHERE pricing_phase_id > 1
561 AND ROWNUM < 7)
562 AND EXISTS (SELECT /*+ ORDERED */ 'x' -- [julin/4261562] added active_flag and PTE/SS filters
563 FROM qp_list_header_phases lhb, qp_list_headers_b qph, qp_price_req_sources_v qprs, qp_list_lines a
564 WHERE lhb.pricing_phase_id = b.pricing_phase_id
565 AND qph.list_header_id = lhb.list_header_id
566 AND qph.active_flag = 'Y'
567 AND qprs.request_type_code = p_request_type_code
568 AND qprs.source_system_code = qph.source_system_code
569 AND a.pricing_phase_id = b.pricing_phase_id
570 AND a.list_header_id = qph.list_header_id
571 AND a.modifier_level_code = p_mod_level_code
572 AND ROWNUM=1)
573 AND ROWNUM=1;
574
575 CURSOR l_pricing_phase_exists_cur IS
576 SELECT 'X'
577 FROM qp_event_phases
578 WHERE pricing_event_code = p_event_code
579 AND pricing_phase_id = 1;
580
581
582 l_pricing_phase_id NUMBER;
583 l_line_group_exists QP_PRICING_PHASES.LINE_GROUP_EXISTS%TYPE :='U';
584 l_oid_exists QP_PRICING_PHASES.OID_EXISTS%TYPE :='U';
585 l_rltd_exists QP_PRICING_PHASES.RLTD_EXISTS%TYPE :='U';
586 l_list_line_type QP_LIST_LINES.LIST_LINE_TYPE_CODE%TYPE :='XXX';
587 l_order_status_rec Qp_Util_Pub.ORDER_LINES_STATUS_REC_TYPE;
588 l_dummy VARCHAR2(1);
589 BEGIN
590 l_debug := Qp_Preq_Grp.G_DEBUG_ENGINE;
591 l_dummy := NULL;
592
593 -- bug 3006670
594 -- use alternate cursor if freight call
595 IF p_freight_call_flag = 'Y' THEN
596 OPEN l_all_lines_info_cur_freight(p_event_code || ',');
597 FETCH l_all_lines_info_cur_freight INTO l_dummy;
598 CLOSE l_all_lines_info_cur_freight;
599 ELSE
600 OPEN l_all_lines_info_cur(p_event_code || ',') ;
601 FETCH l_all_lines_info_cur INTO l_dummy;
602 CLOSE l_all_lines_info_cur;
603 END IF;
604
605 IF (l_dummy = 'X') THEN
606 l_order_status_rec.ALL_LINES_FLAG := 'Y';
607 ELSE
608 l_order_status_rec.ALL_LINES_FLAG := 'N';
609 END IF;
610
611 l_dummy := NULL;
612
613 -- 3756506, this used to call l_summary_line_info_cur
614 -- [julin/4676740] using separate cursor when req type given
615 IF (p_request_type_code IS NOT NULL) THEN
616 OPEN l_line_info_ptess_cur(p_event_code || ',', 'ORDER');
617 FETCH l_line_info_ptess_cur INTO l_dummy;
618 CLOSE l_line_info_ptess_cur;
619 ELSE
620 OPEN l_line_info_cur(p_event_code || ',', 'ORDER');
621 FETCH l_line_info_cur INTO l_dummy;
622 CLOSE l_line_info_cur;
623 END IF;
624
625 IF (l_dummy = 'X') THEN
626 l_order_status_rec.SUMMARY_LINE_FLAG := 'Y';
627 ELSE
628 l_order_status_rec.SUMMARY_LINE_FLAG := 'N';
629 END IF;
630
631 l_dummy := NULL;
632
633 OPEN l_pricing_phase_exists_cur;
634 FETCH l_pricing_phase_exists_cur INTO l_dummy;
635 CLOSE l_pricing_phase_exists_cur;
636
637 IF (l_dummy = 'X') THEN
638 l_order_status_rec.CHANGED_LINES_FLAG := 'Y';
639 ELSE
640 l_order_status_rec.CHANGED_LINES_FLAG := 'N';
641 END IF;
642
643 IF (l_order_status_rec.CHANGED_LINES_FLAG = 'N') THEN
644
645 -- 3756506, this used to call l_changed_lines_info_cur
646 -- [julin/4676740] using separate cursor when req type given
647 IF (p_request_type_code IS NOT NULL) THEN
648 OPEN l_line_info_ptess_cur(p_event_code || ',', 'LINE');
649 FETCH l_line_info_ptess_cur INTO l_dummy;
650 CLOSE l_line_info_ptess_cur;
651 ELSE
652 OPEN l_line_info_cur(p_event_code || ',', 'LINE');
653 FETCH l_line_info_cur INTO l_dummy;
654 CLOSE l_line_info_cur;
655 END IF;
656
657 IF (l_dummy = 'X') THEN
658 l_order_status_rec.CHANGED_LINES_FLAG := 'Y';
659 ELSE
660 l_order_status_rec.CHANGED_LINES_FLAG := 'N';
661 END IF;
662
663 END IF;
664
665 x_order_status_rec := l_order_status_rec;
666
667 EXCEPTION
668 WHEN OTHERS THEN
669 IF l_debug = FND_API.G_TRUE THEN
670 qp_preq_grp.engine_debug('Error in Procedure Get_Order_Lines_Status');
671
672 END IF;
673 END Get_Order_Lines_Status;
674
675 Procedure Get_Order_Lines_Status(p_event_code IN VARCHAR2,
676 p_header_id in NUMBER,
677 p_line_id in NUMBER,
678 x_order_status_rec OUT NOCOPY ORDER_LINES_STATUS_REC_TYPE,
679 p_freight_call_flag IN VARCHAR2 := 'N',
680 p_request_type_code IN VARCHAR2 DEFAULT NULL) is
681 CURSOR l_all_lines_info_cur(p_event_code1 VARCHAR2) IS
682 SELECT 'X'
683 FROM qp_pricing_phases a , qp_event_phases b
684 WHERE a.pricing_phase_id = b.pricing_phase_id
685 AND (a.oid_exists = 'Y' OR a.line_group_exists = 'Y' OR a.rltd_exists = 'Y')
686 AND b.pricing_event_code in (SELECT decode(rownum
687 ,1 ,substr(p_event_code,1,instr(p_event_code1,',',1,1)-1)
688 ,2 ,substr(p_event_code , instr(p_event_code1,',',1,rownum-1) + 1,
689 instr(p_event_code1,',',1,rownum)-1 - instr(p_event_code1,',',1,rownum-1))
690 ,3 ,substr(p_event_code , instr(p_event_code1,',',1,rownum-1) + 1,
691 instr(p_event_code1,',',1,rownum)-1 - instr(p_event_code1,',',1,rownum-1))
692 ,4 ,substr(p_event_code , instr(p_event_code1,',',1,rownum-1) + 1,
693 instr(p_event_code1,',',1,rownum)-1 - instr(p_event_code1,',',1,rownum-1))
694 ,5 ,substr(p_event_code , instr(p_event_code1,',',1,rownum-1) + 1,
695 instr(p_event_code1,',',1,rownum)-1 - instr(p_event_code1,',',1,rownum-1))
696 ,6 ,substr(p_event_code , instr(p_event_code1,',',1,rownum-1) + 1,
697 instr(p_event_code1,',',1,rownum)-1 - instr(p_event_code1,',',1,rownum-1)))
698 FROM qp_event_phases
699 WHERE rownum < 7)
700 AND ROWNUM = 1;
701
702 /* For bug 3006670 (if p_freight_call_flag = 'Y')
703 * all_lines_flag should return 'N' if additional buylines exist for a PRG
704 * that means if rltd_exists='Y' but oid_exists and line_group_exists both ='N',
705 * then all_lines_flag should be 'N'
706 * (logically, this is equivalent to checking (oid_exists='Y' OR line_group='Y'))
707 * the l_all_lines_info_cur_freight cursor has been modified to reflect this
708 */
709 CURSOR l_all_lines_info_cur_freight(p_event_code1 VARCHAR2) IS
710 SELECT 'X'
711 FROM qp_pricing_phases a , qp_event_phases b
712 WHERE a.pricing_phase_id = b.pricing_phase_id
713 AND (a.oid_exists = 'Y' OR a.line_group_exists = 'Y')
714 AND b.pricing_event_code in (SELECT decode(rownum
715 ,1 ,substr(p_event_code,1,instr(p_event_code1,',',1,1)-1)
716 ,2 ,substr(p_event_code , instr(p_event_code1,',',1,rownum-1) + 1,
717 instr(p_event_code1,',',1,rownum)-1 - instr(p_event_code1,',',1,rownum-1))
718 ,3 ,substr(p_event_code , instr(p_event_code1,',',1,rownum-1) + 1,
719 instr(p_event_code1,',',1,rownum)-1 - instr(p_event_code1,',',1,rownum-1))
720 ,4 ,substr(p_event_code , instr(p_event_code1,',',1,rownum-1) + 1,
721 instr(p_event_code1,',',1,rownum)-1 - instr(p_event_code1,',',1,rownum-1))
722 ,5 ,substr(p_event_code , instr(p_event_code1,',',1,rownum-1) + 1,
723 instr(p_event_code1,',',1,rownum)-1 - instr(p_event_code1,',',1,rownum-1))
724 ,6 ,substr(p_event_code , instr(p_event_code1,',',1,rownum-1) + 1,
725 instr(p_event_code1,',',1,rownum)-1 - instr(p_event_code1,',',1,rownum-1)))
726 FROM qp_event_phases
727 WHERE rownum < 7)
728 AND ROWNUM = 1;
729
730 -- l_summary_line_info_cur and l_changed_lines_info_cur were combined into one
731 -- cursor for performance bug 3756506.
732 -- passing p_mod_level_code='ORDER' is equiv. to l_summary_line_info_cur
733 -- passing p_mod_level_code='LINE' is equiv. to l_changed_lines_info_cur
734 --Modified cursor query for Bug 12677276
735 /*CURSOR l_line_info_cur(p_event_code1 VARCHAR2, p_mod_level_code VARCHAR2) IS
736 SELECT 'X'
737 FROM qp_event_phases b
738 WHERE b.pricing_event_code in (SELECT decode(rownum
739 ,1 ,substr(p_event_code,1,instr(p_event_code1,',',1,1)-1)
740 ,2 ,substr(p_event_code , instr(p_event_code1,',',1,rownum-1) + 1,
741 instr(p_event_code1,',',1,rownum)-1 - instr(p_event_code1,',',1,rownum-1))
742 ,3 ,substr(p_event_code , instr(p_event_code1,',',1,rownum-1) + 1,
743 instr(p_event_code1,',',1,rownum)-1 - instr(p_event_code1,',',1,rownum-1))
744 ,4 ,substr(p_event_code , instr(p_event_code1,',',1,rownum-1) + 1,
745 instr(p_event_code1,',',1,rownum)-1 - instr(p_event_code1,',',1,rownum-1))
746 ,5 ,substr(p_event_code , instr(p_event_code1,',',1,rownum-1) + 1,
747 instr(p_event_code1,',',1,rownum)-1 - instr(p_event_code1,',',1,rownum-1))
748 ,6 ,substr(p_event_code , instr(p_event_code1,',',1,rownum-1) + 1,
749 instr(p_event_code1,',',1,rownum)-1 - instr(p_event_code1,',',1,rownum-1)))
750 FROM qp_event_phases
751 WHERE pricing_phase_id > 1
752 AND rownum < 7)
753 AND EXISTS (SELECT 'x'
754 FROM qp_list_lines a
755 WHERE a.pricing_phase_id = b.pricing_phase_id
756 AND a.modifier_level_code=p_mod_level_code
757 AND ROWNUM=1)
758 AND ROWNUM=1;*/
759
760 CURSOR l_line_info_cur(p_event_code1 VARCHAR2, p_mod_level_code VARCHAR2) IS
761 SELECT /*+ leading(C) use_nl(c,b) */ 'X'
762 FROM
763 ( SELECT decode(rownum
764 ,1 ,substr(p_event_code,1,instr(p_event_code1,',',1,1)-1)
765 ,2 ,substr(p_event_code , instr(p_event_code1,',',1,rownum-1) + 1,
766 instr(p_event_code1,',',1,rownum)-1 - instr(p_event_code1,',',1,rownum-1))
767 ,3 ,substr(p_event_code , instr(p_event_code1,',',1,rownum-1) + 1,
768 instr(p_event_code1,',',1,rownum)-1 - instr(p_event_code1,',',1,rownum-1))
769 ,4 ,substr(p_event_code , instr(p_event_code1,',',1,rownum-1) + 1,
770 instr(p_event_code1,',',1,rownum)-1 - instr(p_event_code1,',',1,rownum-1))
771 ,5 ,substr(p_event_code , instr(p_event_code1,',',1,rownum-1) + 1,
772 instr(p_event_code1,',',1,rownum)-1 - instr(p_event_code1,',',1,rownum-1))
773 ,6 ,substr(p_event_code , instr(p_event_code1,',',1,rownum-1) + 1,
774 instr(p_event_code1,',',1,rownum)-1 - instr(p_event_code1,',',1,rownum-1))) PRICING_EVENT_CD
775 FROM qp_event_phases
776 WHERE pricing_phase_id > 1
777 AND rownum < 7) C,
778 QP_EVENT_PHASES B
779 WHERE B.PRICING_EVENT_CODE = C.PRICING_EVENT_CD
780 AND EXISTS (SELECT /*+ no_push_subq no_unnest index(a QP_LIST_LINES_N4) */ 'x' -- 14842950
781 FROM qp_list_lines a
782 WHERE a.pricing_phase_id = b.pricing_phase_id
783 AND a.modifier_level_code=p_mod_level_code
784 AND ROWNUM=1)
785 AND ROWNUM=1;
786 --End bug 12677276
787
788 CURSOR l_line_info_ptess_cur(p_event_code1 VARCHAR2, p_mod_level_code VARCHAR2) IS
789 SELECT 'X'
790 FROM qp_event_phases b
791 WHERE b.pricing_event_code IN (SELECT DECODE(ROWNUM
792 ,1 ,SUBSTR(p_event_code,1,INSTR(p_event_code1,',',1,1)-1)
793 ,2 ,SUBSTR(p_event_code , INSTR(p_event_code1,',',1,ROWNUM-1) + 1,
794 INSTR(p_event_code1,',',1,ROWNUM)-1 - INSTR(p_event_code1,',',1,ROWNUM-1))
795 ,3 ,SUBSTR(p_event_code , INSTR(p_event_code1,',',1,ROWNUM-1) + 1,
796 INSTR(p_event_code1,',',1,ROWNUM)-1 - INSTR(p_event_code1,',',1,ROWNUM-1))
797 ,4 ,SUBSTR(p_event_code , INSTR(p_event_code1,',',1,ROWNUM-1) + 1,
798 INSTR(p_event_code1,',',1,ROWNUM)-1 - INSTR(p_event_code1,',',1,ROWNUM-1))
799 ,5 ,SUBSTR(p_event_code , INSTR(p_event_code1,',',1,ROWNUM-1) + 1,
800 INSTR(p_event_code1,',',1,ROWNUM)-1 - INSTR(p_event_code1,',',1,ROWNUM-1))
801 ,6 ,SUBSTR(p_event_code , INSTR(p_event_code1,',',1,ROWNUM-1) + 1,
802 INSTR(p_event_code1,',',1,ROWNUM)-1 - INSTR(p_event_code1,',',1,ROWNUM-1)))
803 FROM qp_event_phases
804 WHERE pricing_phase_id > 1
805 AND ROWNUM < 7)
806 AND EXISTS (SELECT /*+ ORDERED */ 'x' -- [julin/4261562] added active_flag and PTE/SS filters
807 FROM qp_list_header_phases lhb, qp_list_headers_b qph, qp_price_req_sources_v qprs, qp_list_lines a
808 WHERE lhb.pricing_phase_id = b.pricing_phase_id
809 AND qph.list_header_id = lhb.list_header_id
810 AND qph.active_flag = 'Y'
811 AND qprs.request_type_code = p_request_type_code
812 AND qprs.source_system_code = qph.source_system_code
813 AND a.pricing_phase_id = b.pricing_phase_id
814 AND a.list_header_id = qph.list_header_id
815 AND a.modifier_level_code=p_mod_level_code
816 AND ROWNUM=1)
817 AND ROWNUM=1;
818
819 CURSOR l_pricing_phase_exists_cur IS
820 SELECT 'X'
821 FROM qp_event_phases
822 WHERE pricing_event_code = p_event_code
823 AND pricing_phase_id = 1;
824
825
826 l_pricing_phase_id NUMBER;
827 l_line_group_exists QP_PRICING_PHASES.LINE_GROUP_EXISTS%TYPE :='U';
828 l_oid_exists QP_PRICING_PHASES.OID_EXISTS%TYPE :='U';
829 l_rltd_exists QP_PRICING_PHASES.RLTD_EXISTS%TYPE :='U';
830 l_list_line_type QP_LIST_LINES.LIST_LINE_TYPE_CODE%TYPE :='XXX';
831 l_order_status_rec QP_UTIL_PUB.ORDER_LINES_STATUS_REC_TYPE;
832 l_dummy VARCHAR2(1);
833 x_pass_all_lines varchar2(1):='Y'; -- 9251187
834 l_QP_CUSTOM_HOOK4_ADV_MOD varchar2(1):=nvl(fnd_profile.value('QP_CUSTOM_HOOK_ADV_MOD'),'N'); -- 9251187
835 BEGIN
836
837 IF (l_QP_CUSTOM_HOOK4_ADV_MOD='Y') THEN -- for profile 'ON' 9251187
838 QP_ADV_MOD_CUSTOM_HOOK.pass_all_lines(p_header_id ,p_line_id , x_pass_all_lines); -- custom hook call
839 end if;
840
841 if ( x_pass_all_lines='N') then -- 9251187
842 l_order_status_rec.ALL_LINES_FLAG := 'N'; -- 9251187
843 else -- 9251187
844 l_debug := qp_preq_grp.G_DEBUG_ENGINE;
845 l_dummy := NULL;
846
847 -- bug 3006670
848 -- use alternate cursor if freight call
849 IF p_freight_call_flag = 'Y' THEN
850 OPEN l_all_lines_info_cur_freight(p_event_code || ',');
851 FETCH l_all_lines_info_cur_freight INTO l_dummy;
852 CLOSE l_all_lines_info_cur_freight;
853 ELSE
854 OPEN l_all_lines_info_cur(p_event_code || ',') ;
855 FETCH l_all_lines_info_cur INTO l_dummy;
856 CLOSE l_all_lines_info_cur;
857 END IF;
858
859 IF (l_dummy = 'X') THEN
860 l_order_status_rec.ALL_LINES_FLAG := 'Y';
861 ELSE
862 l_order_status_rec.ALL_LINES_FLAG := 'N';
863 END IF;
864 end if;-- 9251187
865
866 l_dummy := NULL;
867
868 -- 3756506, this used to call l_summary_line_info_cur
869 -- [julin/4676740] using separate cursor when req type given
870 IF (p_request_type_code IS NOT NULL) THEN
871 OPEN l_line_info_ptess_cur(p_event_code || ',', 'ORDER');
872 FETCH l_line_info_ptess_cur INTO l_dummy;
873 CLOSE l_line_info_ptess_cur;
874 ELSE
875 OPEN l_line_info_cur(p_event_code || ',', 'ORDER');
876 FETCH l_line_info_cur INTO l_dummy;
877 CLOSE l_line_info_cur;
878 END IF;
879
880 IF (l_dummy = 'X') THEN
881 l_order_status_rec.SUMMARY_LINE_FLAG := 'Y';
882 ELSE
883 l_order_status_rec.SUMMARY_LINE_FLAG := 'N';
884 END IF;
885
886 l_dummy := NULL;
887
888 OPEN l_pricing_phase_exists_cur;
889 FETCH l_pricing_phase_exists_cur INTO l_dummy;
890 CLOSE l_pricing_phase_exists_cur;
891
892 IF (l_dummy = 'X') THEN
893 l_order_status_rec.CHANGED_LINES_FLAG := 'Y';
894 ELSE
895 l_order_status_rec.CHANGED_LINES_FLAG := 'N';
896 END IF;
897
898 IF (l_order_status_rec.CHANGED_LINES_FLAG = 'N') THEN
899
900 -- 3756506, this used to call l_changed_lines_info_cur
901 -- [julin/4676740] using separate cursor when req type given
902 IF (p_request_type_code IS NOT NULL) THEN
903 OPEN l_line_info_ptess_cur(p_event_code || ',', 'LINE');
904 FETCH l_line_info_ptess_cur INTO l_dummy;
905 CLOSE l_line_info_ptess_cur;
906 ELSE
907 OPEN l_line_info_cur(p_event_code || ',', 'LINE');
908 FETCH l_line_info_cur INTO l_dummy;
909 CLOSE l_line_info_cur;
910 END IF;
911
912
913
914 IF (l_dummy = 'X') THEN
915 l_order_status_rec.CHANGED_LINES_FLAG := 'Y';
916 ELSE
917 l_order_status_rec.CHANGED_LINES_FLAG := 'N';
918 END IF;
919
920 END IF;
921
922 x_order_status_rec := l_order_status_rec;
923
924 EXCEPTION
925 WHEN OTHERS THEN
926 IF l_debug = Fnd_Api.G_TRUE THEN
927 Qp_Preq_Grp.engine_debug('Error in Procedure Get_Order_Lines_Status');
928
929 END IF;
930 END Get_Order_Lines_Status;
931
932 /***********************************************************************
933 Change done for bug 7241731/7596981.
934 Procedure to Determine how many lines to pass to the pricing engine
935 during a manual modifier call. Currently there is no column in
936 qp_pricing_phases that will indicate exclusive presence of a manual
937 linegroup modifier for particular phases.
938 Due to which a query on qp_list_lines was used earlier in
939 GET_MANUAL_ADV_STATUS procedure of OEXVADJB package. That cursor is
940 causing performance issue for some customers.
941 Final fix will be to add such a column in qp_pricing_phases table.
942 Meanwhile, this procedure is written to use manual_modifier_flag and
943 line_group_exists from qp_pricing_phases to determine the manual Linegroup
944 status which will take care of most of the customer set-up cases.
945 Once the change is made to add a column to the qp_pricing_table this
946 procedure will be changed to look at that column value.
947 ***********************************************************************/
948
949
950 PROCEDURE Get_Manual_All_Lines_Status(p_event_code IN VARCHAR2,
951 x_manual_all_lines_status OUT NOCOPY VARCHAR2) IS
952
953 CURSOR l_manual_all_lines_info_cur(p_event_code1 VARCHAR2) IS
954 SELECT 'Y'
955 FROM qp_pricing_phases a , qp_event_phases b
956 WHERE a.pricing_phase_id = b.pricing_phase_id
957 AND a.line_group_exists = 'Y' -- no need to consider PRG/OID for manual mod call
958 AND a.manual_modifier_flag in ('M','B') -- phases tagged to have manual modifiers
959 AND b.pricing_event_code IN (SELECT DECODE(ROWNUM
960 ,1 ,SUBSTR(p_event_code,1,INSTR(p_event_code1,',',1,1)-1)
961 ,2 ,SUBSTR(p_event_code , INSTR(p_event_code1,',',1,ROWNUM-1) + 1,
962 INSTR(p_event_code1,',',1,ROWNUM)-1 - INSTR(p_event_code1,',',1,ROWNUM-1))
963 ,3 ,SUBSTR(p_event_code , INSTR(p_event_code1,',',1,ROWNUM-1) + 1,
964 INSTR(p_event_code1,',',1,ROWNUM)-1 - INSTR(p_event_code1,',',1,ROWNUM-1))
965 ,4 ,SUBSTR(p_event_code , INSTR(p_event_code1,',',1,ROWNUM-1) + 1,
966 INSTR(p_event_code1,',',1,ROWNUM)-1 - INSTR(p_event_code1,',',1,ROWNUM-1))
967 ,5 ,SUBSTR(p_event_code , INSTR(p_event_code1,',',1,ROWNUM-1) + 1,
968 INSTR(p_event_code1,',',1,ROWNUM)-1 - INSTR(p_event_code1,',',1,ROWNUM-1))
969 ,6 ,SUBSTR(p_event_code , INSTR(p_event_code1,',',1,ROWNUM-1) + 1,
970 INSTR(p_event_code1,',',1,ROWNUM)-1 - INSTR(p_event_code1,',',1,ROWNUM-1)))
971 FROM qp_event_phases
972 WHERE ROWNUM < 7)
973 AND ROWNUM = 1;
974
975 l_manual_grpline_profile_value Varchar2(1):= Nvl(Fnd_Profile.Value('QP_MANUAL_LINEGROUP'),'N');
976 BEGIN
977 l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
978
979 IF l_debug = FND_API.G_TRUE THEN
980 QP_PREQ_GRP.engine_debug(' Inside Get_Manual_All_Lines_Status ');
981 END IF;
982
983 x_manual_all_lines_status := 'N';
984
985 IF l_manual_grpline_profile_value = 'Y' THEN
986 OPEN l_manual_all_lines_info_cur(p_event_code || ',') ;
987 FETCH l_manual_all_lines_info_cur INTO x_manual_all_lines_status;
988 CLOSE l_manual_all_lines_info_cur;
989 ELSE
990 x_manual_all_lines_status := 'N';
991 END IF;
992
993 IF l_debug = FND_API.G_TRUE THEN
994 QP_PREQ_GRP.engine_debug(' x_manual_all_lines_status :'||x_manual_all_lines_status);
995 QP_PREQ_GRP.engine_debug(' Leaving Get_Manual_All_Lines_Status ');
996 END IF;
997
998 EXCEPTION
999 WHEN OTHERS THEN
1000
1001 IF l_debug = FND_API.G_TRUE THEN
1002 QP_PREQ_GRP.engine_debug('Error in Procedure Get_Manual_All_Lines_Status');
1003 END IF;
1004
1005 END Get_Manual_All_Lines_Status;
1006
1007 /***********************************************************************
1008 Procedure to Validate a Given Price List with a Currency_code
1009 ***********************************************************************/
1010
1011 PROCEDURE Validate_Price_list_Curr_code
1012 (
1013 l_price_list_id IN NUMBER
1014 ,l_currency_code IN VARCHAR2
1015 ,l_pricing_effective_date IN DATE
1016 ,l_validate_result OUT NOCOPY VARCHAR2
1017 )
1018 IS
1019
1020 l_select VARCHAR2(1);
1021 l_temp_date DATE;
1022
1023 CURSOR c_validate_plist_curr_multi
1024 IS
1025
1026 SELECT 'X'
1027 FROM qp_currency_details a
1028 ,qp_list_headers_b b
1029 WHERE a.currency_header_id = b.currency_header_id
1030 AND a.to_currency_code = l_currency_code
1031 AND b.list_header_id = l_price_list_id
1032 AND TRUNC(l_temp_date) >= TRUNC(NVL(a.start_date_active, l_temp_date))
1033 AND TRUNC(l_temp_date) <= TRUNC(NVL(a.end_date_active, l_temp_date))
1034 AND TRUNC(l_temp_date) >= TRUNC(NVL(b.start_date_active, l_temp_date))
1035 AND TRUNC(l_temp_date) <= TRUNC(NVL(b.end_date_active, l_temp_date));
1036
1037
1038 CURSOR c_validate_pl_curr_no_multi
1039 IS
1040
1041 SELECT 'X'
1042 FROM qp_list_headers_b
1043 WHERE currency_code = l_currency_code
1044 AND list_header_id = l_price_list_id
1045 AND TRUNC(l_temp_date) >= TRUNC(NVL(start_date_active, l_temp_date))
1046 AND TRUNC(l_temp_date) <= TRUNC(NVL(end_date_active, l_temp_date));
1047
1048 BEGIN
1049
1050 IF l_pricing_effective_date IS NULL THEN
1051 l_temp_date := SYSDATE;
1052 ELSE
1053 l_temp_date := l_pricing_effective_date;
1054 END IF;
1055
1056 -- Added new profile (QP_MULTI_CURRENCY_USAGE) with default value 'Y' to maintain current behaviour,
1057 -- bug 2943033
1058 IF UPPER(Fnd_Profile.value('QP_MULTI_CURRENCY_INSTALLED')) IN ('Y', 'YES') AND
1059 (NVL(Fnd_Profile.value('QP_MULTI_CURRENCY_USAGE'), 'Y') = 'Y') THEN
1060 IF l_debug = Fnd_Api.G_TRUE THEN
1061 Qp_Preq_Grp.engine_debug('validate price list - multi-currency');
1062
1063 END IF;
1064
1065 --Multi-Currency is installed
1066 OPEN c_validate_plist_curr_multi;
1067 FETCH c_validate_plist_curr_multi INTO l_select;
1068
1069 IF c_validate_plist_curr_multi%FOUND THEN
1070
1071 l_validate_result := 'Y';
1072 CLOSE c_validate_plist_curr_multi;
1073
1074 ELSE
1075 l_validate_result := 'N';
1076 CLOSE c_validate_plist_curr_multi;
1077
1078 END IF;
1079
1080 ELSE --Multi-Currency is not installed
1081
1082 IF l_debug = Fnd_Api.G_TRUE THEN
1083 Qp_Preq_Grp.engine_debug('validate price list - no multi-currency');
1084
1085 END IF;
1086 OPEN c_validate_pl_curr_no_multi;
1087 FETCH c_validate_pl_curr_no_multi INTO l_select;
1088
1089 IF c_validate_pl_curr_no_multi%FOUND THEN
1090
1091 l_validate_result := 'Y';
1092 CLOSE c_validate_pl_curr_no_multi;
1093
1094 ELSE
1095 l_validate_result := 'N';
1096 CLOSE c_validate_pl_curr_no_multi;
1097
1098 END IF;
1099
1100 END IF; --Multi-Currency is installed
1101
1102
1103 IF l_debug = Fnd_Api.G_TRUE THEN
1104 Qp_Preq_Grp.engine_debug('validate price list - result is ' || l_validate_result);
1105
1106 END IF;
1107 EXCEPTION
1108 WHEN OTHERS THEN
1109 l_validate_result := 'N';
1110
1111 IF Fnd_Msg_Pub.Check_Msg_Level(Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
1112 Fnd_Message.SET_NAME('QP', 'QP_ERR_VALID_PRICELIST_N_CURR');
1113 END IF;
1114
1115 IF c_validate_plist_curr_multi%ISOPEN THEN
1116 CLOSE c_validate_plist_curr_multi;
1117 END IF;
1118
1119 IF c_validate_pl_curr_no_multi%ISOPEN THEN
1120 CLOSE c_validate_pl_curr_no_multi;
1121 END IF;
1122 END Validate_Price_list_Curr_code;
1123
1124 /***********************************************************************
1125 Procedure to get all the currency code(s) for a given price list
1126 ***********************************************************************/
1127 PROCEDURE Get_Currency
1128 (
1129 l_price_list_id IN NUMBER
1130 ,l_pricing_effective_date IN DATE
1131 ,l_currency_code_tbl OUT NOCOPY CURRENCY_CODE_TBL
1132 )
1133 IS
1134
1135 l_temp_date DATE;
1136 -- Cursor to get currency code list without multi-currency installed
1137 CURSOR c_currency_no_multi
1138 IS
1139
1140 SELECT currency_code
1141 ,name currency_name
1142 ,PRECISION currency_precision
1143 FROM fnd_currencies_vl
1144 WHERE currency_flag = 'Y'
1145 AND enabled_flag = 'Y'
1146 AND TRUNC(NVL(start_date_active, SYSDATE)) <= TRUNC(SYSDATE)
1147 AND TRUNC(NVL(end_date_active, SYSDATE)) >= TRUNC(SYSDATE)
1148 ORDER BY currency_code;
1149
1150 -- Cursor to get currency code list with multi-currency installed
1151 CURSOR c_currency_multi
1152 IS
1153
1154 SELECT DISTINCT a.currency_code currency_code
1155 ,a.name currency_name
1156 ,a.PRECISION currency_precision
1157 FROM fnd_currencies_vl a
1158 ,qp_currency_details b
1159 ,qp_list_headers_b c
1160 WHERE c.list_header_id = l_price_list_id
1161 AND b.currency_header_id = c.currency_header_id
1162 AND a.currency_code = b.to_currency_code
1163 AND c.list_type_code IN ('PRL', 'AGR')
1164 AND a.currency_flag = 'Y'
1165 AND a.enabled_flag = 'Y'
1166 AND TRUNC(l_temp_date) >= TRUNC(NVL(b.start_date_active, l_temp_date))
1167 AND TRUNC(l_temp_date) <= TRUNC(NVL(b.end_date_active, l_temp_date))
1168 AND TRUNC(l_temp_date) >= TRUNC(NVL(c.start_date_active, l_temp_date))
1169 AND TRUNC(l_temp_date) <= TRUNC(NVL(c.end_date_active, l_temp_date))
1170 ORDER BY a.currency_code;
1171
1172 l_currency_no_multi c_currency_no_multi%ROWTYPE;
1173 l_currency_multi c_currency_multi%ROWTYPE;
1174
1175 l_currency_header_id NUMBER;
1176 l_counter NUMBER;
1177
1178
1179 BEGIN
1180
1181 IF l_pricing_effective_date IS NULL THEN
1182 l_temp_date := SYSDATE;
1183 ELSE
1184 l_temp_date := l_pricing_effective_date;
1185 END IF;
1186
1187
1188 -- Added new profile (QP_MULTI_CURRENCY_USAGE) with default value 'Y' to maintain current behaviour,
1189 -- bug 2943033
1190 IF UPPER(Fnd_Profile.value('QP_MULTI_CURRENCY_INSTALLED')) IN ('Y', 'YES')
1191 AND (NVL(Fnd_Profile.value('QP_MULTI_CURRENCY_USAGE'), 'Y') = 'Y')
1192 AND l_price_list_id IS NOT NULL THEN
1193
1194 -- Multi Currency is installed on and calling prog pass a price list
1195 l_counter := 1;
1196
1197 OPEN c_currency_multi;
1198 LOOP
1199
1200 FETCH c_currency_multi INTO l_currency_multi;
1201 EXIT WHEN c_currency_multi%NOTFOUND;
1202
1203 l_currency_code_tbl(l_counter).currency_code := l_currency_multi.currency_code;
1204 l_currency_code_tbl(l_counter).currency_name := l_currency_multi.currency_name;
1205 l_currency_code_tbl(l_counter).currency_precision :=l_currency_multi.currency_precision;
1206
1207 l_counter := l_counter + 1;
1208
1209 END LOOP;
1210
1211 CLOSE c_currency_multi;
1212
1213
1214 ELSE
1215
1216 -- Multi Currency is not installed or Multi Currency is installed but calling prog pass no price list
1217 l_counter := 1;
1218
1219 OPEN c_currency_no_multi;
1220 LOOP
1221
1222 FETCH c_currency_no_multi INTO l_currency_no_multi;
1223 EXIT WHEN c_currency_no_multi%NOTFOUND;
1224
1225 l_currency_code_tbl(l_counter).currency_code := l_currency_no_multi.currency_code;
1226 l_currency_code_tbl(l_counter).currency_name := l_currency_no_multi.currency_name;
1227 l_currency_code_tbl(l_counter).currency_precision :=l_currency_no_multi.currency_precision;
1228
1229 l_counter := l_counter + 1;
1230
1231 END LOOP;
1232
1233 CLOSE c_currency_no_multi;
1234
1235 END IF;
1236
1237
1238 EXCEPTION
1239
1240 WHEN OTHERS THEN
1241
1242 IF Fnd_Msg_Pub.Check_Msg_Level(Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
1243 Fnd_Message.SET_NAME('QP', 'QP_ERROR_GET_CURR_F_PRICELIST');
1244 END IF;
1245
1246 IF c_currency_multi%ISOPEN THEN
1247 CLOSE c_currency_multi;
1248 END IF;
1249
1250 IF c_currency_no_multi%ISOPEN THEN
1251 CLOSE c_currency_no_multi;
1252 END IF;
1253
1254 END Get_Currency;
1255
1256 /***********************************************************************
1257 Procedure to get price list(s) for a given currency code
1258 Bug 3018412 - added the condition to select the data for all source systems belonging to a pte_code
1259 ***********************************************************************/
1260 PROCEDURE Get_Price_List
1261 (
1262 l_currency_code IN VARCHAR2
1263 ,l_pricing_effective_date IN DATE
1264 ,l_agreement_id IN NUMBER
1265 ,l_blanket_reference_id IN VARCHAR2 DEFAULT NULL
1266 ,l_price_list_tbl OUT NOCOPY price_list_tbl
1267 ,l_sold_to_org_id IN NUMBER DEFAULT NULL
1268 )
1269
1270 IS
1271
1272 l_temp_date DATE;
1273 --passing null org_id to OE_Sys_Parameters for moac so that it will return CUSTOMER_RELATIONSHIPS_FLAG
1274 --for the org_context set -- build_contexts API or calling app would have set 'single' org context
1275 l_org_id NUMBER := Qp_Util.get_org_id;
1276 l_cust_relation_flag VARCHAR2(30);
1277 --OE_Sys_Parameters.VALUE('CUSTOMER_RELATIONSHIPS_FLAG', l_org_id);
1278
1279 CURSOR c_price_list_multi
1280 IS
1281
1282 SELECT DISTINCT qlhv.list_header_id price_list_id
1283 ,qlhv.name name
1284 ,qlhv.description description
1285 ,qlhv.start_date_active start_date_active
1286 ,qlhv.end_date_active end_date_active
1287 FROM qp_list_headers_vl qlhv
1288 ,qp_currency_details qdt
1289 WHERE qlhv.currency_header_id = qdt.currency_header_id
1290 AND qdt.to_currency_code = l_currency_code
1291 AND qlhv.active_flag = 'Y'
1292 --added for MOAC
1293 --commented out below 2 lines for MOAC as the ORG_ID check is built into the view qp_list_headers_vl
1294 --AND (((nvl(qlhv.global_flag,'Y') = 'Y' or qlhv.orig_org_id = fnd_profile.Value('ORG_ID')) and
1295 -- qp_security.security_on = 'Y') or qp_security.security_on = 'N')
1296 AND qlhv.list_type_code = 'PRL'
1297 -- If there is a blanket reference show all standard pricelist and
1298 -- all shareable BSO PL's and all PL's attached to the referenced blanket.
1299 -- Otherwise show only standard PL's.
1300 AND (
1301 ( l_blanket_reference_id IS NULL
1302 AND NVL(qlhv.list_source_code,' ') <> 'BSO'
1303 )
1304 OR
1305 ( l_blanket_reference_id IS NOT NULL
1306 AND
1307 ( ( NVL(qlhv.shareable_flag,'Y') = 'Y'
1308 AND NVL(qlhv.list_source_code,' ') = 'BSO'
1309 )
1310 OR NVL(qlhv.orig_system_header_ref,-9999) = l_blanket_reference_id
1311 OR NVL(qlhv.list_source_code,' ') <> 'BSO'
1312 )
1313 )
1314 ) -- Blanket Pricing
1315 --AND NVL(to_date(:parameter.lov_char_param1), TRUNC(sysdate))
1316 AND l_temp_date
1317 BETWEEN
1318 NVL(TRUNC(qlhv.start_date_active), l_temp_date) AND
1319 NVL(TRUNC(qlhv.end_date_active), l_temp_date)
1320 --AND NVL(to_date(:parameter.lov_char_param1), TRUNC(sysdate))
1321 AND l_temp_date
1322 BETWEEN
1323 NVL(TRUNC(qdt.start_date_active), l_temp_date) AND
1324 NVL(TRUNC(qdt.end_date_active), l_temp_date)
1325 --AND :parameter.lov_num_param1 IS NULL
1326 AND l_agreement_id IS NULL
1327 --AND qdt.to_currency_code = NVL(:order.transactional_curr_code, qdt.to_currency_code)
1328 --AND qdt.to_currency_code = NVL(l_order_transac_curr_code, qdt.to_currency_code)
1329 AND qlhv.source_system_code IN (SELECT qpss.application_short_name
1330 FROM qp_pte_source_systems qpss
1331 WHERE qpss.pte_code = Fnd_Profile.value('QP_PRICING_TRANSACTION_ENTITY'))
1332 UNION
1333 SELECT DISTINCT qlhv.list_header_id price_list_id
1334 ,qlhv.name name
1335 ,qlhv.description description
1336 ,qlhv.start_date_active start_date_active
1337 ,qlhv.end_date_active end_date_active
1338 FROM qp_list_headers_vl qlhv
1339 ,oe_agreements oa
1340 ,qp_currency_details qdt
1341 WHERE ( ( oa.price_list_id = qlhv.list_header_id
1342 AND qlhv.list_type_code IN ('PRL', 'AGR') )
1343 OR
1344 qlhv.list_type_code = 'PRL'
1345 )
1346 AND qlhv.active_flag = 'Y'
1347 -- If there is a blanket reference show all standard pricelist and
1348 -- all shareable BSO PL's and all PL's attached to the referenced blanket.
1349 -- Otherwise show only standard PL's.
1350 AND (
1351 ( l_blanket_reference_id IS NULL
1352 AND NVL(qlhv.list_source_code,' ') <> 'BSO'
1353 )
1354 OR
1355 ( l_blanket_reference_id IS NOT NULL
1356 AND
1357 ( ( NVL(qlhv.shareable_flag,'Y') = 'Y'
1358 AND NVL(qlhv.list_source_code,' ') = 'BSO'
1359 )
1360 OR NVL(qlhv.orig_system_header_ref,-9999) = l_blanket_reference_id
1361 OR NVL(qlhv.list_source_code,' ') <> 'BSO'
1362 )
1363 )
1364 ) -- Blanket Pricing
1365 --added for MOAC
1366 --commented out below 2 lines for MOAC as the ORG_ID check is built into the view qp_list_headers_vl
1367 --AND (((nvl(qlhv.global_flag,'Y') = 'Y' or qlhv.orig_org_id = fnd_profile.Value('ORG_ID')) and
1368 -- qp_security.security_on = 'Y') or qp_security.security_on = 'N')
1369 AND qlhv.currency_header_id = qdt.currency_header_id
1370 AND qdt.to_currency_code = l_currency_code
1371 --AND NVL(to_date(:parameter.lov_char_param1), TRUNC(sysdate))
1372 AND l_temp_date
1373 BETWEEN
1374 NVL(TRUNC(qlhv.start_date_active), l_temp_date) AND
1375 NVL(TRUNC(qlhv.end_date_active), l_temp_date)
1376 --AND NVL(to_date(:parameter.lov_char_param1), TRUNC(sysdate))
1377 AND l_temp_date
1378 BETWEEN
1379 NVL(TRUNC(qdt.start_date_active), l_temp_date) AND
1380 NVL(TRUNC(qdt.end_date_active), l_temp_date)
1381 --AND :parameter.lov_num_param1 = oa.agreement_id
1382 AND l_agreement_id = oa.agreement_id
1383 --AND :parameter.lov_num_param1 IS NOT NULL
1384 AND l_agreement_id IS NOT NULL
1385 --AND qdt.to_currency_code = NVL(:order.transactional_curr_code, qdt.to_currency_code)
1386 --AND qdt.to_currency_code = NVL(l_order_transac_curr_code, qdt.to_currency_code)
1387 AND qdt.to_currency_code = NVL(l_currency_code, qdt.to_currency_code)
1388 AND qlhv.source_system_code IN (SELECT qpss.application_short_name
1389 FROM qp_pte_source_systems qpss
1390 WHERE qpss.pte_code = Fnd_Profile.value('QP_PRICING_TRANSACTION_ENTITY'))
1391 UNION
1392 SELECT DISTINCT qlhv.list_header_id price_list_id
1393 ,qlhv.name name
1394 ,qlhv.description description
1395 ,qlhv.start_date_active start_date_active
1396 ,qlhv.end_date_active end_date_active
1397 FROM qp_list_headers_vl qlhv
1398 ,oe_agreements oa
1399 ,qp_currency_details qdt
1400 WHERE ( ( oa.price_list_id = qlhv.list_header_id
1401 AND qlhv.list_type_code IN ('PRL', 'AGR') )
1402 OR
1403 qlhv.list_type_code = 'PRL'
1404 )
1405 AND qlhv.active_flag = 'Y'
1406 -- If there is a blanket reference show all standard pricelist and
1407 -- all shareable BSO PL's and all PL's attached to the referenced blanket.
1408 -- Otherwise show only standard PL's.
1409 AND (
1410 ( l_blanket_reference_id IS NULL
1411 AND NVL(qlhv.list_source_code,' ') <> 'BSO'
1412 )
1413 OR
1414 ( l_blanket_reference_id IS NOT NULL
1415 AND
1416 ( ( NVL(qlhv.shareable_flag,'Y') = 'Y'
1417 AND NVL(qlhv.list_source_code,' ') = 'BSO'
1418 )
1419 OR NVL(qlhv.orig_system_header_ref,-9999) = l_blanket_reference_id
1420 OR NVL(qlhv.list_source_code,' ') <> 'BSO'
1421 )
1422 )
1423 ) -- Blanket Pricing
1424 --added for MOAC
1425 --commented out below 2 lines for MOAC as the ORG_ID check is built into the view qp_list_headers_vl
1426 --AND (((nvl(qlhv.global_flag,'Y') = 'Y' or qlhv.orig_org_id = fnd_profile.Value('ORG_ID')) and
1427 -- qp_security.security_on = 'Y') or qp_security.security_on = 'N')
1428 AND qlhv.currency_header_id = qdt.currency_header_id
1429 AND qdt.to_currency_code = l_currency_code
1430 AND l_temp_date
1431 BETWEEN
1432 NVL(TRUNC(qlhv.start_date_active), l_temp_date) AND
1433 NVL(TRUNC(qlhv.end_date_active), l_temp_date)
1434 AND l_temp_date
1435 BETWEEN
1436 NVL(TRUNC(qdt.start_date_active), l_temp_date) AND
1437 NVL(TRUNC(qdt.end_date_active), l_temp_date)
1438 AND l_agreement_id IS NULL
1439 AND qdt.to_currency_code = NVL(l_currency_code, qdt.to_currency_code)
1440 AND l_sold_to_org_id IS NOT NULL
1441 AND( oa.sold_to_org_id = l_sold_to_org_id OR
1442 oa.sold_to_org_id IS NULL OR
1443 oa.sold_to_org_id = -1 OR
1444 oa.sold_to_org_id IN (
1445 SELECT r.cust_account_id FROM
1446 hz_cust_acct_relate r
1447 WHERE r.related_cust_account_id = l_sold_to_org_id AND
1448 r.status = 'A' AND l_cust_relation_flag = 'Y'))
1449 AND qlhv.source_system_code IN (SELECT qpss.application_short_name
1450 FROM qp_pte_source_systems qpss
1451 WHERE qpss.pte_code = Fnd_Profile.value('QP_PRICING_TRANSACTION_ENTITY'))
1452 ORDER BY name;
1453
1454
1455 CURSOR c_price_list_no_multi
1456 IS
1457
1458 SELECT qlhv.list_header_id price_list_id
1459 ,qlhv.name name
1460 ,qlhv.description description
1461 ,qlhv.start_date_active start_date_active
1462 ,qlhv.end_date_active end_date_active
1463 FROM qp_list_headers_vl qlhv
1464 WHERE list_type_code = 'PRL'
1465 AND qlhv.active_flag = 'Y'
1466 --added for MOAC
1467 --commented out below 2 lines for MOAC as the ORG_ID check is built into the view qp_list_headers_vl
1468 --AND (((nvl(qlhv.global_flag,'Y') = 'Y' or qlhv.orig_org_id = fnd_profile.Value('ORG_ID')) and
1469 -- qp_security.security_on = 'Y') or qp_security.security_on = 'N')
1470 --AND NVL(to_date(:parameter.lov_char_param1), TRUNC(sysdate))
1471 -- If there is a blanket reference show all standard pricelist and
1472 -- all shareable BSO PL's and all PL's attached to the referenced blanket.
1473 -- Otherwise show only standard PL's.
1474 AND (
1475 ( l_blanket_reference_id IS NULL
1476 AND NVL(qlhv.list_source_code,' ') <> 'BSO'
1477 )
1478 OR
1479 ( l_blanket_reference_id IS NOT NULL
1480 AND
1481 ( ( NVL(qlhv.shareable_flag,'Y') = 'Y'
1482 AND NVL(qlhv.list_source_code,' ') = 'BSO'
1483 )
1484 OR NVL(qlhv.orig_system_header_ref,-9999) = l_blanket_reference_id
1485 OR NVL(qlhv.list_source_code,' ') <> 'BSO'
1486 )
1487 )
1488 ) -- Blanket Pricing
1489 AND l_temp_date
1490 BETWEEN
1491 NVL(TRUNC(start_date_active), l_temp_date) AND
1492 NVL(TRUNC(end_date_active), l_temp_date)
1493 --AND :parameter.lov_num_param1 IS NULL
1494 AND l_agreement_id IS NULL
1495 --AND currency_code = NVL(l_order_transac_curr_code, currency_code)
1496 AND currency_code = NVL(l_currency_code, currency_code)
1497 AND qlhv.source_system_code IN (SELECT qpss.application_short_name
1498 FROM qp_pte_source_systems qpss
1499 WHERE qpss.pte_code = Fnd_Profile.value('QP_PRICING_TRANSACTION_ENTITY'))
1500 UNION
1501 SELECT
1502 qlhv.list_header_id price_list_id
1503 ,qlhv.name name
1504 ,qlhv.description description
1505 ,qlhv.start_date_active start_date_active
1506 ,qlhv.end_date_active end_date_active
1507 FROM
1508 qp_list_headers_vl qlhv
1509 ,oe_agreements oa
1510 WHERE ( (oa.price_list_id = qlhv.list_header_id AND
1511 qlhv.list_type_code IN ('PRL', 'AGR'))
1512 OR
1513 qlhv.list_type_code = 'PRL'
1514 )
1515 --AND NVL(to_date(:parameter.lov_char_param1), TRUNC(sysdate)) BETWEEN
1516 -- If there is a blanket reference show all standard pricelist and
1517 -- all shareable BSO PL's and all PL's attached to the referenced blanket.
1518 -- Otherwise show only standard PL's.
1519 AND (
1520 ( l_blanket_reference_id IS NULL
1521 AND NVL(qlhv.list_source_code,' ') <> 'BSO'
1522 )
1523 OR
1524 ( l_blanket_reference_id IS NOT NULL
1525 AND
1526 ( ( NVL(qlhv.shareable_flag,'Y') = 'Y'
1527 AND NVL(qlhv.list_source_code,' ') = 'BSO'
1528 )
1529 OR NVL(qlhv.orig_system_header_ref,-9999) = l_blanket_reference_id
1530 OR NVL(qlhv.list_source_code,' ') <> 'BSO'
1531 )
1532 )
1533 ) -- Blanket Pricing
1534 --added for MOAC
1535 --commented out below 2 lines for MOAC as the ORG_ID check is built into the view qp_list_headers_vl
1536 --AND (((nvl(qlhv.global_flag,'Y') = 'Y' or qlhv.orig_org_id = fnd_profile.Value('ORG_ID')) and
1537 -- qp_security.security_on = 'Y') or qp_security.security_on = 'N')
1538 AND l_temp_date
1539 BETWEEN
1540 NVL(TRUNC(qlhv.start_date_active), l_temp_date) AND
1541 NVL(TRUNC(qlhv.end_date_active), l_temp_date)
1542 --AND :parameter.lov_num_param1 = oa.agreement_id
1543 AND l_agreement_id = oa.agreement_id
1544 --AND :parameter.lov_num_param1 IS NOT NULL
1545 AND l_agreement_id IS NOT NULL
1546 --AND currency_code = NVL(l_order_transac_curr_code, currency_code)
1547 AND currency_code = NVL(l_currency_code, currency_code)
1548 AND qlhv.source_system_code IN (SELECT qpss.application_short_name
1549 FROM qp_pte_source_systems qpss
1550 WHERE qpss.pte_code = Fnd_Profile.value('QP_PRICING_TRANSACTION_ENTITY'))
1551 UNION
1552 SELECT
1553 qlhv.list_header_id price_list_id
1554 ,qlhv.name name
1555 ,qlhv.description description
1556 ,qlhv.start_date_active start_date_active
1557 ,qlhv.end_date_active end_date_active
1558 FROM
1559 qp_list_headers_vl qlhv
1560 ,oe_agreements oa
1561 WHERE ( (oa.price_list_id = qlhv.list_header_id AND
1562 qlhv.list_type_code IN ('PRL', 'AGR'))
1563 OR
1564 qlhv.list_type_code = 'PRL'
1565 )
1566 -- If there is a blanket reference show all standard pricelist and
1567 -- all shareable BSO PL's and all PL's attached to the referenced blanket.
1568 -- Otherwise show only standard PL's.
1569 AND (
1570 ( l_blanket_reference_id IS NULL
1571 AND NVL(qlhv.list_source_code,' ') <> 'BSO'
1572 )
1573 OR
1574 ( l_blanket_reference_id IS NOT NULL
1575 AND
1576 ( ( NVL(qlhv.shareable_flag,'Y') = 'Y'
1577 AND NVL(qlhv.list_source_code,' ') = 'BSO'
1578 )
1579 OR NVL(qlhv.orig_system_header_ref,-9999) = l_blanket_reference_id
1580 OR NVL(qlhv.list_source_code,' ') <> 'BSO'
1581 )
1582 )
1583 ) -- Blanket Pricing
1584 --added for MOAC
1585 --commented out below 2 lines for MOAC as the ORG_ID check is built into the view qp_list_headers_vl
1586 --AND (((nvl(qlhv.global_flag,'Y') = 'Y' or qlhv.orig_org_id = fnd_profile.Value('ORG_ID')) and
1587 -- qp_security.security_on = 'Y') or qp_security.security_on = 'N')
1588 AND l_temp_date
1589 BETWEEN
1590 NVL(TRUNC(qlhv.start_date_active), l_temp_date) AND
1591 NVL(TRUNC(qlhv.end_date_active), l_temp_date)
1592 AND l_agreement_id IS NULL
1593 AND currency_code = NVL(l_currency_code, currency_code)
1594 AND l_sold_to_org_id IS NOT NULL
1595 AND( oa.sold_to_org_id = l_sold_to_org_id OR
1596 oa.sold_to_org_id IS NULL OR
1597 oa.sold_to_org_id = -1 OR
1598 oa.sold_to_org_id IN (
1599 SELECT r.cust_account_id FROM
1600 hz_cust_acct_relate r
1601 WHERE r.related_cust_account_id = l_sold_to_org_id AND
1602 r.status = 'A' AND l_cust_relation_flag = 'Y'))
1603 AND qlhv.source_system_code IN (SELECT qpss.application_short_name
1604 FROM qp_pte_source_systems qpss
1605 WHERE qpss.pte_code = Fnd_Profile.value('QP_PRICING_TRANSACTION_ENTITY'))
1606 ORDER BY name;
1607
1608 l_price_list_multi c_price_list_multi%ROWTYPE;
1609 l_price_list_no_multi c_price_list_no_multi%ROWTYPE;
1610 l_counter NUMBER;
1611
1612
1613 BEGIN
1614 --added for moac to call oe_sys_params with a valid org_id
1615 IF l_org_id IS NOT NULL THEN
1616 l_cust_relation_flag := Oe_Sys_Parameters.VALUE('CUSTOMER_RELATIONSHIPS_FLAG',l_org_id);
1617 ELSE
1618 l_cust_relation_flag := '';
1619 END IF;
1620 l_debug := Qp_Preq_Grp.G_DEBUG_ENGINE;
1621 IF l_debug = Fnd_Api.G_TRUE THEN
1622 Oe_Debug_Pub.ADD('QP_UTIL_PUB.Get_Price_List BEGIN ');
1623 Oe_Debug_Pub.ADD('l_currency_code = ' || l_currency_code);
1624 Oe_Debug_Pub.ADD('l_pricing_effective_date = ' ||l_pricing_effective_date);
1625 Oe_Debug_Pub.ADD('l_agreement_id = ' || l_agreement_id);
1626
1627 END IF;
1628 IF l_pricing_effective_date IS NULL THEN
1629 l_temp_date := TRUNC(SYSDATE);
1630 ELSE
1631 l_temp_date := TRUNC(l_pricing_effective_date);
1632 END IF;
1633
1634 -- Added new profile (QP_MULTI_CURRENCY_USAGE) with default value 'Y' to maintain current behaviour,
1635 -- bug 2943033
1636 IF UPPER(Fnd_Profile.value('QP_MULTI_CURRENCY_INSTALLED')) IN ('Y', 'YES')
1637 AND (NVL(Fnd_Profile.value('QP_MULTI_CURRENCY_USAGE'), 'Y') = 'Y')
1638 AND l_currency_code IS NOT NULL THEN
1639
1640 -- Multi Currency is installed on and calling prog pass a currency_code
1641 l_counter := 1;
1642
1643 OPEN c_price_list_multi;
1644 LOOP
1645
1646 FETCH c_price_list_multi INTO l_price_list_multi;
1647 EXIT WHEN c_price_list_multi%NOTFOUND;
1648
1649 l_price_list_tbl(l_counter).price_list_id := l_price_list_multi.price_list_id;
1650 --dbms_output.put_line('price_list_id: '|| l_price_list_tbl(l_counter).price_list_id);
1651 l_price_list_tbl(l_counter).name := l_price_list_multi.name;
1652 --dbms_output.put_line('name: '|| l_price_list_tbl(l_counter).name);
1653 l_price_list_tbl(l_counter).description := l_price_list_multi.description;
1654 --dbms_output.put_line('description: ' ||l_price_list_tbl(l_counter).description);
1655 l_price_list_tbl(l_counter).start_date_active := l_price_list_multi.start_date_active;
1656 --dbms_output.put_line('start_date_active: '|| l_price_list_tbl(l_counter).start_date_active);
1657 l_price_list_tbl(l_counter).end_date_active := l_price_list_multi.start_date_active;
1658 --dbms_output.put_line('end_date_active: '|| l_price_list_tbl(l_counter).end_date_active);
1659 l_counter := l_counter + 1;
1660
1661 END LOOP;
1662
1663 CLOSE c_price_list_multi;
1664
1665 ELSE
1666
1667 -- Multi Currency is not installed or Multi Currency is installed but calling prog pass no currency_code
1668 l_counter := 1;
1669
1670 OPEN c_price_list_no_multi;
1671 LOOP
1672
1673 FETCH c_price_list_no_multi INTO l_price_list_no_multi;
1674 EXIT WHEN c_price_list_no_multi%NOTFOUND;
1675
1676 l_price_list_tbl(l_counter).price_list_id := l_price_list_no_multi.price_list_id;
1677 l_price_list_tbl(l_counter).name := l_price_list_no_multi.name;
1678 l_price_list_tbl(l_counter).description := l_price_list_no_multi.description;
1679 l_price_list_tbl(l_counter).start_date_active := l_price_list_no_multi.start_date_active;
1680 l_price_list_tbl(l_counter).end_date_active := l_price_list_no_multi.start_date_active;
1681
1682 l_counter := l_counter + 1;
1683
1684 END LOOP;
1685
1686 CLOSE c_price_list_no_multi;
1687
1688 END IF;
1689
1690
1691 EXCEPTION
1692 WHEN OTHERS THEN
1693
1694 IF Fnd_Msg_Pub.Check_Msg_Level(Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
1695 Fnd_Message.SET_NAME('QP', 'QP_ERROR_GET_PRICELIST_F_CURR');
1696 END IF;
1697
1698 IF c_price_list_multi%ISOPEN THEN
1699 CLOSE c_price_list_multi;
1700 END IF;
1701
1702 IF c_price_list_no_multi%ISOPEN THEN
1703 CLOSE c_price_list_no_multi;
1704 END IF;
1705
1706 END Get_Price_list;
1707
1708 /*
1709 Bug 3018412 - added the condition to select the data for all source systems belonging to a pte_code
1710 */
1711 PROCEDURE Get_Price_Lists
1712 (
1713 p_currency_code IN VARCHAR2 DEFAULT NULL
1714 ,p_price_lists_tbl OUT NOCOPY price_lists_tbl
1715 )
1716 IS
1717
1718 l_temp_date DATE;
1719 l_counter NUMBER;
1720
1721 CURSOR c_price_list_blkt
1722 IS
1723 SELECT list_header_id price_list_id,
1724 name name,
1725 description description,
1726 -rounding_factor rounding_factor,
1727 start_date_active start_date_active,
1728 end_date_active end_date_active
1729 FROM qp_list_headers_vl
1730 WHERE list_type_code IN ('PRL' ,'AGR') AND
1731 NVL(active_flag,'N') ='Y'
1732 --added for MOAC
1733 --commented out below 2 lines for MOAC as the ORG_ID check is built into the view qp_list_headers_vl
1734 -- (((nvl(global_flag,'Y') = 'Y' or orig_org_id = fnd_profile.Value('ORG_ID'))
1735 -- and qp_security.security_on = 'Y') or qp_security.security_on = 'N')
1736 AND source_system_code IN (SELECT qpss.application_short_name
1737 FROM qp_pte_source_systems qpss
1738 WHERE qpss.pte_code = Fnd_Profile.value('QP_PRICING_TRANSACTION_ENTITY'))
1739 ORDER BY name;
1740
1741 CURSOR c_price_list_ttyp
1742 IS
1743 SELECT list_header_id price_list_id,
1744 name name,
1745 description description,
1746 -rounding_factor rounding_factor,
1747 start_date_active start_date_active,
1748 end_date_active end_date_active
1749 FROM qp_list_headers_vl
1750 WHERE currency_code = p_currency_code
1751 --added for MOAC
1752 --commented out below 2 lines for MOAC as the ORG_ID check is built into the view qp_list_headers_vl
1753 -- (((nvl(global_flag,'Y') = 'Y' or orig_org_id = fnd_profile.Value('ORG_ID'))
1754 -- and qp_security.security_on = 'Y') or qp_security.security_on = 'N') and
1755 AND TRUNC(l_temp_date) BETWEEN NVL(TRUNC(start_date_active), TRUNC(l_temp_date)) AND
1756 NVL(TRUNC(end_date_active), TRUNC(l_temp_date)) AND
1757 list_type_code = 'PRL'
1758 AND source_system_code IN (SELECT qpss.application_short_name
1759 FROM qp_pte_source_systems qpss
1760 WHERE qpss.pte_code = Fnd_Profile.value('QP_PRICING_TRANSACTION_ENTITY'))
1761 ORDER BY name;
1762
1763 l_price_list_ttyp c_price_list_ttyp%ROWTYPE;
1764 l_price_list_blkt c_price_list_blkt%ROWTYPE;
1765
1766 BEGIN
1767
1768 l_debug := Qp_Preq_Grp.G_DEBUG_ENGINE;
1769 IF l_debug = Fnd_Api.G_TRUE THEN
1770 Oe_Debug_Pub.ADD('QP_UTIL_PUB.Get_Price_Lists BEGIN ');
1771 Oe_Debug_Pub.ADD('p_currency_code = ' || p_currency_code);
1772 END IF;
1773
1774 l_temp_date := TRUNC(SYSDATE);
1775
1776 IF p_currency_code IS NOT NULL THEN
1777
1778 -- calling program passed currency_code
1779 l_counter := 1;
1780
1781 OPEN c_price_list_ttyp;
1782 LOOP
1783
1784 FETCH c_price_list_ttyp INTO l_price_list_ttyp;
1785 EXIT WHEN c_price_list_ttyp%NOTFOUND;
1786
1787 p_price_lists_tbl(l_counter).price_list_id := l_price_list_ttyp.price_list_id;
1788 p_price_lists_tbl(l_counter).name := l_price_list_ttyp.name;
1789 p_price_lists_tbl(l_counter).description := l_price_list_ttyp.description;
1790 p_price_lists_tbl(l_counter).rounding_factor := l_price_list_ttyp.rounding_factor;
1791 p_price_lists_tbl(l_counter).start_date_active := l_price_list_ttyp.start_date_active;
1792 p_price_lists_tbl(l_counter).end_date_active := l_price_list_ttyp.start_date_active;
1793
1794 l_counter := l_counter + 1;
1795
1796 END LOOP;
1797
1798 CLOSE c_price_list_ttyp;
1799 ELSE
1800
1801 -- calling program did not pass currency_code
1802 l_counter := 1;
1803
1804 OPEN c_price_list_blkt;
1805 LOOP
1806
1807 FETCH c_price_list_blkt INTO l_price_list_blkt;
1808 EXIT WHEN c_price_list_blkt%NOTFOUND;
1809
1810 p_price_lists_tbl(l_counter).price_list_id := l_price_list_blkt.price_list_id;
1811 p_price_lists_tbl(l_counter).name := l_price_list_blkt.name;
1812 p_price_lists_tbl(l_counter).description := l_price_list_blkt.description;
1813 p_price_lists_tbl(l_counter).rounding_factor := l_price_list_blkt.rounding_factor;
1814 p_price_lists_tbl(l_counter).start_date_active := l_price_list_blkt.start_date_active;
1815 p_price_lists_tbl(l_counter).end_date_active := l_price_list_blkt.start_date_active;
1816
1817 l_counter := l_counter + 1;
1818
1819 END LOOP;
1820
1821 CLOSE c_price_list_blkt;
1822
1823 END IF;
1824
1825
1826 EXCEPTION
1827 WHEN OTHERS THEN
1828
1829 IF Fnd_Msg_Pub.Check_Msg_Level(Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
1830 Fnd_Message.SET_NAME('QP', 'QP_ERROR_GET_PRICING_OBJECT');
1831 Fnd_Message.SET_TOKEN('PRICING_OBJECT','Price Lists');
1832 END IF;
1833
1834 IF c_price_list_blkt%ISOPEN THEN
1835 CLOSE c_price_list_blkt;
1836 END IF;
1837
1838 IF c_price_list_ttyp%ISOPEN THEN
1839 CLOSE c_price_list_ttyp;
1840 END IF;
1841
1842 END Get_Price_Lists;
1843
1844 PROCEDURE Get_Agreement
1845 (
1846 p_sold_to_org_id IN NUMBER DEFAULT NULL
1847 ,p_transaction_type_id IN NUMBER DEFAULT NULL
1848 ,p_pricing_effective_date IN DATE
1849 ,p_agreement_tbl OUT NOCOPY agreement_tbl
1850 )
1851 IS
1852
1853 l_temp_date DATE;
1854 --passing null org_id to OE_Sys_Parameters for moac so that it will return CUSTOMER_RELATIONSHIPS_FLAG
1855 --for the org_context set -- build_contexts API or calling app would have set 'single' org context
1856 l_cust_relation_flag VARCHAR2(30) := Oe_Sys_Parameters.VALUE('CUSTOMER_RELATIONSHIPS_FLAG', NULL);
1857
1858 -- 4865226, SQL repositroy performance fix for shareable memory
1859 CURSOR c_agreement
1860 IS
1861 SELECT SUBSTRB(a.agreement_name,1,300) agreement_name, a.agreement_id agreement_id,
1862 a.agreement_type, q.name price_list_name, p.party_name customer_name,
1863 t.name payment_term_name, a.start_date_active, a.end_date_active
1864 FROM oe_agreements_lov_v a, qp_list_headers_vl q,
1865 hz_parties p, hz_cust_accounts c, ra_terms_tl t,
1866 -- qp_list_headers_b l,
1867 oe_transaction_types_all ot
1868 WHERE a.sold_to_org_id IN (
1869 SELECT TO_NUMBER(p_sold_to_org_id) FROM dual
1870 UNION
1871 SELECT -1 FROM dual
1872 UNION
1873 SELECT r.cust_account_id FROM hz_cust_acct_relate r
1874 WHERE r.related_cust_account_id = p_sold_to_org_id AND
1875 l_cust_relation_flag = 'Y' AND
1876 r.status = 'A' ) AND
1877 l_temp_date BETWEEN
1878 TRUNC(NVL(a.start_date_active, ADD_MONTHS(SYSDATE, -10000))) AND
1879 TRUNC(NVL(a.end_date_active, ADD_MONTHS(SYSDATE, +10000))) AND
1880 a.price_list_id = q.list_header_id
1881 --added for MOAC
1882 --commented out below 2 lines for MOAC as the ORG_ID check is built into the view qp_list_headers_vl
1883 -- (((nvl(q.global_flag,'Y') = 'Y' or q.orig_org_id = fnd_profile.Value('ORG_ID')) and
1884 -- qp_security.security_on = 'Y') or qp_security.security_on = 'N') and
1885 AND a.price_list_id = q.list_header_id AND
1886 -- q.language = userenv('LANG') and
1887 a.sold_to_org_id = c.cust_account_id(+) AND
1888 c.party_id = p.party_id(+) AND
1889 a.term_id = t.term_id(+) AND
1890 ot.transaction_type_id = p_transaction_type_id AND
1891 DECODE(ot.agreement_type_code,NULL,NVL(a.agreement_type, -9999),
1892 ot.agreement_type_code) = NVL(a.agreement_type, -9999) AND
1893 t.LANGUAGE(+) = USERENV('LANG')
1894 ORDER BY 1;
1895
1896 --bug7192230 Adding two new cursors
1897
1898 CURSOR c_agreement_no_org
1899 IS
1900 select substr(a.agreement_name,1,300) agreement_name, a.agreement_id agreement_id,
1901 a.agreement_type, q.name price_list_name, null customer_name,
1902 t.name payment_term_name, a.start_date_active, a.end_date_active
1903 from oe_agreements_lov_v a, qp_list_headers_vl q, ra_terms_tl t,
1904 oe_transaction_types_all ot
1905 where l_temp_date between
1906 trunc(nvl(a.start_date_active, add_months(sysdate, -10000))) and
1907 trunc(nvl(a.end_date_active, add_months(sysdate, +10000))) and
1908 a.price_list_id = q.list_header_id and
1909 --(((nvl(l.global_flag,'Y') = 'Y' or l.orig_org_id = fnd_profile.Value('ORG_ID')) and
1910 --qp_security.security_on = 'Y') or qp_security.security_on = 'N') and
1911 --a.price_list_id = q.list_header_id and
1912 --q.language = userenv('LANG') and
1913 a.term_id = t.term_id(+) and
1914 ot.transaction_type_id = p_transaction_type_id and
1915 decode(ot.agreement_type_code,null,nvl(a.agreement_type, -9999),
1916 ot.agreement_type_code) = nvl(a.agreement_type, -9999) and
1917 t.language(+) = userenv('LANG')
1918 order by 1;
1919
1920 CURSOR c_agreement_no_trtype
1921 IS
1922 select substr(a.agreement_name,1,300) agreement_name, a.agreement_id agreement_id,
1923 a.agreement_type, q.name price_list_name, p.party_name customer_name,
1924 null payment_term_name, a.start_date_active, a.end_date_active
1925 from oe_agreements_lov_v a, qp_list_headers_vl q,
1926 hz_parties p, hz_cust_accounts c --, qp_list_headers_b l
1927 where a.sold_to_org_id in (
1928 select to_number(p_sold_to_org_id) from dual
1929 union
1930 select -1 from dual
1931 union
1932 select r.cust_account_id from hz_cust_acct_relate r
1933 where r.related_cust_account_id = p_sold_to_org_id and
1934 l_cust_relation_flag = 'Y' and
1935 r.status = 'A' ) and
1936 l_temp_date between
1937 trunc(nvl(a.start_date_active, add_months(sysdate, -10000))) and
1938 trunc(nvl(a.end_date_active, add_months(sysdate, +10000))) and
1939 a.price_list_id = q.list_header_id and
1940 --(((nvl(l.global_flag,'Y') = 'Y' or l.orig_org_id = fnd_profile.Value('ORG_ID')) and
1941 --qp_security.security_on = 'Y') or qp_security.security_on = 'N') and
1942 --a.price_list_id = q.list_header_id and
1943 --q.language = userenv('LANG') and
1944 a.sold_to_org_id = c.cust_account_id(+) and
1945 c.party_id = p.party_id(+)
1946 order by 1;
1947
1948 --bug7192230 Adding cursors end
1949
1950 --bug7192230 Updating name of the cursor from c_agreement_no_org to c_agreement_no_org_no_trtype
1951
1952 CURSOR c_agreement_no_org_no_trtype
1953 IS
1954 SELECT SUBSTRB(a.agreement_name,1,300) agreement_name, a.agreement_id agreement_id,
1955 a.agreement_type, q.name price_list_name, NULL customer_name,
1956 NULL payment_term_name, a.start_date_active, a.end_date_active
1957 FROM oe_agreements_lov_v a, qp_list_headers_vl q
1958 --, qp_list_headers_b l
1959 WHERE l_temp_date BETWEEN
1960 TRUNC(NVL(a.start_date_active, ADD_MONTHS(SYSDATE, -10000))) AND
1961 TRUNC(NVL(a.end_date_active, ADD_MONTHS(SYSDATE, +10000))) AND
1962 a.price_list_id = q.list_header_id
1963 --added for MOAC
1964 --commented out below 2 lines for MOAC as the ORG_ID check is built into the view qp_list_headers_vl
1965 -- (((nvl(q.global_flag,'Y') = 'Y' or q.orig_org_id = fnd_profile.Value('ORG_ID')) and
1966 -- qp_security.security_on = 'Y') or qp_security.security_on = 'N') and
1967 AND a.price_list_id = q.list_header_id
1968 -- q.language = userenv('LANG')
1969 ORDER BY 1;
1970
1971 l_agreement c_agreement%ROWTYPE;
1972 l_counter NUMBER := 0;
1973
1974 BEGIN
1975 Oe_Debug_Pub.ADD('QP_UTIL_PUB.Get_Agreement BEGIN ');
1976 Oe_Debug_Pub.ADD('p_pricing_effective_date = ' ||p_pricing_effective_date);
1977
1978 IF p_pricing_effective_date IS NULL THEN
1979 l_temp_date := TRUNC(SYSDATE);
1980 ELSE
1981 l_temp_date := TRUNC(p_pricing_effective_date);
1982 END IF;
1983
1984 IF p_sold_to_org_id IS NOT NULL AND p_transaction_type_id IS NOT NULL THEN
1985 -- calling program passed sold_to_org_id and transaction_type_id
1986
1987 Oe_Debug_Pub.ADD('p_sold_to_org_id = ' || p_sold_to_org_id);
1988 Oe_Debug_Pub.ADD('p_transaction_type_id = ' || p_transaction_type_id);
1989
1990
1991 OPEN c_agreement;
1992 LOOP
1993
1994 FETCH c_agreement INTO l_agreement;
1995 EXIT WHEN c_agreement%NOTFOUND;
1996
1997 l_counter := l_counter + 1;
1998
1999 p_agreement_tbl(l_counter).agreement_name := l_agreement.agreement_name;
2000 p_agreement_tbl(l_counter).agreement_id := l_agreement.agreement_id;
2001 p_agreement_tbl(l_counter).agreement_type := l_agreement.agreement_type;
2002 p_agreement_tbl(l_counter).price_list_name := l_agreement.price_list_name;
2003 p_agreement_tbl(l_counter).customer_name := l_agreement.customer_name;
2004 p_agreement_tbl(l_counter).payment_term_name := l_agreement.payment_term_name;
2005 p_agreement_tbl(l_counter).start_date_active := l_agreement.start_date_active;
2006 p_agreement_tbl(l_counter).end_date_active := l_agreement.end_date_active;
2007
2008 END LOOP;
2009
2010 CLOSE c_agreement;
2011
2012 ELSIF p_sold_to_org_id IS NULL and p_transaction_type_id IS NOT NULL THEN -- added condition for bug7192230
2013 -- calling program did not pass sold_to_org_id and passed only transaction_type_id
2014
2015 oe_debug_pub.add('p_sold_to_org_id = ' || p_sold_to_org_id);
2016 oe_debug_pub.add('p_transaction_type_id = ' || p_transaction_type_id);
2017
2018
2019 OPEN c_agreement_no_org;
2020 LOOP
2021
2022 FETCH c_agreement_no_org INTO l_agreement;
2023 EXIT WHEN c_agreement_no_org%NOTFOUND;
2024
2025 l_counter := l_counter + 1;
2026
2027 p_agreement_tbl(l_counter).agreement_name :=l_agreement.agreement_name;
2028 p_agreement_tbl(l_counter).agreement_id := l_agreement.agreement_id;
2029 p_agreement_tbl(l_counter).agreement_type :=l_agreement.agreement_type;
2030 p_agreement_tbl(l_counter).price_list_name :=l_agreement.price_list_name;
2031 p_agreement_tbl(l_counter).customer_name := l_agreement.customer_name;
2032 p_agreement_tbl(l_counter).payment_term_name :=l_agreement.payment_term_name;
2033 p_agreement_tbl(l_counter).start_date_active :=l_agreement.start_date_active;
2034 p_agreement_tbl(l_counter).end_date_active :=l_agreement.end_date_active;
2035
2036 END LOOP;
2037
2038 CLOSE c_agreement_no_org;
2039
2040 ELSIF p_sold_to_org_id IS NOT NULL and p_transaction_type_id IS NULL THEN -- added condition for bug7192230
2041 -- calling program passed sold_to_org_id and did not pass transaction_type_id
2042
2043 oe_debug_pub.add('p_sold_to_org_id = ' || p_sold_to_org_id);
2044 oe_debug_pub.add('p_transaction_type_id = ' || p_transaction_type_id);
2045
2046
2047 OPEN c_agreement_no_trtype;
2048 LOOP
2049
2050 FETCH c_agreement_no_trtype INTO l_agreement;
2051 EXIT WHEN c_agreement_no_trtype%NOTFOUND;
2052
2053 l_counter := l_counter + 1;
2054
2055 p_agreement_tbl(l_counter).agreement_name :=l_agreement.agreement_name;
2056 p_agreement_tbl(l_counter).agreement_id := l_agreement.agreement_id;
2057 p_agreement_tbl(l_counter).agreement_type :=l_agreement.agreement_type;
2058 p_agreement_tbl(l_counter).price_list_name :=l_agreement.price_list_name;
2059 p_agreement_tbl(l_counter).customer_name := l_agreement.customer_name;
2060 p_agreement_tbl(l_counter).payment_term_name :=l_agreement.payment_term_name;
2061 p_agreement_tbl(l_counter).start_date_active :=l_agreement.start_date_active;
2062 p_agreement_tbl(l_counter).end_date_active :=l_agreement.end_date_active;
2063
2064 END LOOP;
2065
2066 CLOSE c_agreement_no_trtype;
2067
2068 ELSE
2069 -- calling program did not pass sold_to_org_id and transaction_type_id
2070
2071 OPEN c_agreement_no_org_no_trtype; -- bug7192230
2072 LOOP
2073
2074 FETCH c_agreement_no_org_no_trtype INTO l_agreement; -- bug7192230
2075 EXIT WHEN c_agreement_no_org_no_trtype%NOTFOUND; -- bug7192230
2076
2077 l_counter := l_counter + 1;
2078
2079 p_agreement_tbl(l_counter).agreement_name := l_agreement.agreement_name;
2080 p_agreement_tbl(l_counter).agreement_id := l_agreement.agreement_id;
2081 p_agreement_tbl(l_counter).agreement_type := l_agreement.agreement_type;
2082 p_agreement_tbl(l_counter).price_list_name := l_agreement.price_list_name;
2083 p_agreement_tbl(l_counter).customer_name := l_agreement.customer_name;
2084 p_agreement_tbl(l_counter).payment_term_name := l_agreement.payment_term_name;
2085 p_agreement_tbl(l_counter).start_date_active := l_agreement.start_date_active;
2086 p_agreement_tbl(l_counter).end_date_active := l_agreement.end_date_active;
2087
2088 END LOOP;
2089
2090 CLOSE c_agreement_no_org_no_trtype; -- bug7192230
2091 END IF;
2092
2093 EXCEPTION
2094 WHEN OTHERS THEN
2095
2096 IF Fnd_Msg_Pub.Check_Msg_Level(Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
2097 Fnd_Message.SET_NAME('QP', 'QP_ERROR_GET_PRICING_OBJECT');
2098 Fnd_Message.SET_TOKEN('PRICING_OBJECT','Agreements');
2099 END IF;
2100
2101 IF c_agreement%ISOPEN THEN
2102 CLOSE c_agreement;
2103 END IF;
2104
2105 IF c_agreement_no_org%ISOPEN THEN
2106 CLOSE c_agreement_no_org;
2107 END IF;
2108
2109 IF c_agreement_no_org_no_trtype%ISOPEN THEN -- added conditions for bug7192230
2110 CLOSE c_agreement_no_org_no_trtype;
2111 END IF;
2112
2113 IF c_agreement_no_trtype%ISOPEN THEN -- added conditions for bug7192230
2114 CLOSE c_agreement_no_trtype;
2115 END IF;
2116 END Get_Agreement;
2117
2118
2119 /***********************************************************************
2120 called by pricing engine
2121 ***********************************************************************/
2122 FUNCTION get_rounding_factor
2123 (
2124 p_use_multi_currency IN VARCHAR2
2125 ,p_price_list_id IN NUMBER
2126 ,p_currency_code IN VARCHAR2
2127 ,p_pricing_effective_date IN DATE
2128 ) RETURN NUMBER
2129 IS
2130 l_rounding_factor NUMBER;
2131 l_status_code VARCHAR2(1);
2132 BEGIN
2133 l_debug := Qp_Preq_Grp.G_DEBUG_ENGINE;
2134 IF l_debug = Fnd_Api.G_TRUE THEN
2135 Oe_Debug_Pub.ADD('BEGIN qp_util_pub.get_rounding_factor');
2136 Oe_Debug_Pub.ADD('p_use_multi_currency = ' || p_use_multi_currency);
2137 Oe_Debug_Pub.ADD('p_price_list_id = ' || p_price_list_id);
2138 Oe_Debug_Pub.ADD('p_currency_code = ' || p_currency_code);
2139 Oe_Debug_Pub.ADD('p_pricing_effective_date = ' || p_pricing_effective_date);
2140
2141 END IF;
2142 round_price(p_operand => NULL
2143 ,p_rounding_factor => NULL
2144 ,p_use_multi_currency => p_use_multi_currency
2145 ,p_price_list_id => p_price_list_id
2146 ,p_currency_code => p_currency_code
2147 ,p_pricing_effective_date => p_pricing_effective_date
2148 ,x_rounded_operand => l_rounding_factor
2149 ,x_status_code => l_status_code
2150 ,p_operand_type => 'R'
2151 );
2152
2153 IF l_status_code = Fnd_Api.G_RET_STS_UNEXP_ERROR THEN
2154 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
2155 END IF;
2156 IF l_debug = Fnd_Api.G_TRUE THEN
2157 Oe_Debug_Pub.ADD('l_rounding_factor = ' || l_rounding_factor);
2158 Oe_Debug_Pub.ADD('l_status_code = ' || l_status_code);
2159 Oe_Debug_Pub.ADD('END qp_util_pub.get_rounding_factor');
2160
2161 END IF;
2162 RETURN(l_rounding_factor);
2163
2164 END get_rounding_factor;
2165
2166 /***********************************************************************
2167 Procedure to Get Rounded Value
2168 ***********************************************************************/
2169 -- round_price.p_operand_type could be 'A' for adjustment amount or 'S' for item price
2170 -- or 'R' when called from get_rounding_factor
2171 -- when p_operand_type get the value 'R' then x_rounded_operand returns rounding factor
2172 PROCEDURE round_price
2173 (p_operand IN NUMBER
2174 ,p_rounding_factor IN NUMBER
2175 ,p_use_multi_currency IN VARCHAR2
2176 ,p_price_list_id IN NUMBER
2177 ,p_currency_code IN VARCHAR2
2178 ,p_pricing_effective_date IN DATE
2179 ,x_rounded_operand IN OUT NOCOPY NUMBER
2180 ,x_status_code IN OUT NOCOPY VARCHAR2
2181 ,p_operand_type IN VARCHAR2 DEFAULT 'S'
2182 )
2183 IS
2184
2185 l_multi_currency_installed VARCHAR2(1);
2186 l_use_multi_currency VARCHAR2(1);
2187 l_rounding_factor NUMBER;
2188 l_pricing_effective_date DATE;
2189 l_rounding_options VARCHAR2(30);
2190 l_oe_unit_price_rounding VARCHAR2(30);
2191
2192 BEGIN
2193 l_debug := Qp_Preq_Grp.G_DEBUG_ENGINE;
2194 IF l_debug = Fnd_Api.G_TRUE THEN
2195 Oe_Debug_Pub.ADD('BEGIN round_price');
2196 Oe_Debug_Pub.ADD('p_operand = ' || p_operand);
2197 Oe_Debug_Pub.ADD('p_rounding_factor = ' || p_rounding_factor);
2198 Oe_Debug_Pub.ADD('p_use_multi_currency = ' || p_use_multi_currency);
2199 Oe_Debug_Pub.ADD('p_price_list_id = ' || p_price_list_id);
2200 Oe_Debug_Pub.ADD('p_currency_code = ' || p_currency_code);
2201 Oe_Debug_Pub.ADD('p_pricing_effective_date = ' || p_pricing_effective_date);
2202 Oe_Debug_Pub.ADD('p_operand_type = ' || p_operand_type);
2203
2204 END IF;
2205 IF p_operand_type <> 'R' AND p_operand IS NULL THEN
2206 IF Oe_Msg_Pub.Check_Msg_Level(Oe_Msg_Pub.G_MSG_LVL_ERROR) THEN
2207 Fnd_Message.SET_NAME('QP','QP_ATTRIBUTE_REQUIRED');
2208 Fnd_Message.SET_TOKEN('ATTRIBUTE','Operand');
2209 Oe_Msg_Pub.ADD;
2210 END IF;
2211 RAISE Fnd_Api.G_EXC_ERROR;
2212 END IF;
2213
2214 IF p_operand_type <> 'R' AND G_ROUNDING_OPTIONS IS NULL THEN
2215 G_ROUNDING_OPTIONS := NVL(Fnd_Profile.Value('QP_SELLING_PRICE_ROUNDING_OPTIONS'), 'NO_ROUND');
2216 END IF;
2217
2218 l_rounding_options := G_ROUNDING_OPTIONS;
2219
2220 IF l_debug = Fnd_Api.G_TRUE THEN
2221 Oe_Debug_Pub.ADD('Profile : rounding options = ' || l_rounding_options);
2222
2223 END IF;
2224 -- check the Om profile for backward compatibility
2225 -- in scenario like this file is shipped to customer but the corresponding OM change is not shipped
2226 -- The OM change is to delete this OM profile and reply totally on QP_SELLING_PRICE_ROUNDING_OPTIONS
2227 IF p_operand_type <> 'R' AND G_OE_UNIT_PRICE_ROUNDING IS NULL THEN
2228 G_OE_UNIT_PRICE_ROUNDING := NVL(Fnd_Profile.Value('OE_UNIT_PRICE_ROUNDING'), 'N');
2229 END IF;
2230
2231 l_oe_unit_price_rounding := G_OE_UNIT_PRICE_ROUNDING;
2232
2233 IF l_debug = Fnd_Api.G_TRUE THEN
2234 Oe_Debug_Pub.ADD('Profile : OE unit price rounding = ' || l_oe_unit_price_rounding);
2235
2236 END IF;
2237 IF ( (p_operand_type = 'A' AND l_rounding_options = 'ROUND_ADJ')
2238 OR
2239 ((p_operand_type = 'S') AND (l_rounding_options = 'ROUND_ADJ' OR
2240 l_rounding_options = 'NO_ROUND_ADJ')
2241 )
2242 OR
2243 (p_operand_type = 'R')
2244 OR
2245 (l_oe_unit_price_rounding = 'Y')
2246 ) THEN
2247 IF l_debug = Fnd_Api.G_TRUE THEN
2248 Oe_Debug_Pub.ADD('Do rounding ');
2249 END IF;
2250 --dbms_output.put_line('Do rounding ');
2251
2252 IF p_rounding_factor IS NOT NULL THEN
2253 l_rounding_factor := p_rounding_factor;
2254 END IF;
2255
2256 IF p_pricing_effective_date IS NULL THEN
2257 l_pricing_effective_date := TRUNC(SYSDATE);
2258 ELSE
2259 l_pricing_effective_date := p_pricing_effective_date;
2260 END IF;
2261
2262 IF p_price_list_id IS NOT NULL AND
2263 p_currency_code IS NOT NULL AND
2264 l_pricing_effective_date IS NOT NULL THEN
2265 -- it means called by OM
2266
2267 IF G_MULTI_CURRENCY IS NULL THEN
2268 Fnd_Profile.get('QP_MULTI_CURRENCY_INSTALLED', l_multi_currency_installed);
2269 l_multi_currency_installed := NVL(l_multi_currency_installed, 'N');
2270 G_MULTI_CURRENCY := l_multi_currency_installed;
2271 END IF;
2272
2273 l_multi_currency_installed := G_MULTI_CURRENCY;
2274 IF l_debug = Fnd_Api.G_TRUE THEN
2275 Oe_Debug_Pub.ADD('l_multi_currency_installed = ' || l_multi_currency_installed);
2276
2277 END IF;
2278
2279 IF p_use_multi_currency = 'Y' THEN
2280 l_use_multi_currency := p_use_multi_currency;
2281 ELSE
2282 IF G_MULTI_CURRENCY_USAGE IS NULL THEN
2283 -- Added new profile (QP_MULTI_CURRENCY_USAGE) with default value 'N' to maintain
2284 -- current behaviour,bug 2943033
2285 G_MULTI_CURRENCY_USAGE := NVL(Fnd_Profile.value('QP_MULTI_CURRENCY_USAGE'), 'N');
2286 END IF;
2287 l_use_multi_currency := G_MULTI_CURRENCY_USAGE;
2288 END IF;
2289
2290 IF l_debug = Fnd_Api.G_TRUE THEN
2291 Oe_Debug_Pub.ADD('l_use_multi_currency = ' || l_use_multi_currency);
2292 END IF;
2293
2294 IF l_multi_currency_installed = 'Y' AND l_use_multi_currency = 'Y' THEN
2295 -- using rownum < 2 because there could be more than 1 effective records for a
2296 -- to_currency_code but note that selling_rounding_factor will be same
2297
2298 IF l_debug = Fnd_Api.G_TRUE THEN
2299 Oe_Debug_Pub.ADD('round_price - multi-currency installed');
2300
2301 END IF;
2302 -- cache the rounding_factor for price list id, currency and date
2303 IF p_price_list_id = G_PRICE_LIST_ID AND
2304 p_currency_code = G_CURRENCY_CODE AND
2305 l_pricing_effective_date = G_PRICING_EFF_DATE THEN
2306
2307 IF l_debug = Fnd_Api.G_TRUE THEN
2308 Oe_Debug_Pub.ADD('round_price - getting rounding factor from cache');
2309 END IF;
2310 l_rounding_factor := G_ROUNDING_FACTOR;
2311 ELSE
2312 IF l_debug = Fnd_Api.G_TRUE THEN
2313 Oe_Debug_Pub.ADD('round_price - getting rounding factor from database');
2314
2315 END IF;
2316 SELECT qcdt.selling_rounding_factor
2317 INTO l_rounding_factor
2318 FROM qp_list_headers_b qb, qp_currency_details qcdt
2319 WHERE qb.list_header_id = p_price_list_id
2320 AND qcdt.currency_header_id = qb.currency_header_id
2321 AND qcdt.to_currency_code = p_currency_code
2322 AND l_pricing_effective_date BETWEEN
2323 NVL(TRUNC(QCDT.START_DATE_ACTIVE),l_PRICING_EFFECTIVE_DATE)
2324 AND NVL(TRUNC(QCDT.END_DATE_ACTIVE),l_PRICING_EFFECTIVE_DATE)
2325 AND ROWNUM < 2;
2326
2327 G_PRICE_LIST_ID := p_price_list_id;
2328 G_CURRENCY_CODE := p_currency_code;
2329 G_PRICING_EFF_DATE := l_pricing_effective_date;
2330 G_ROUNDING_FACTOR := l_rounding_factor;
2331
2332 END IF;
2333
2334 ELSE -- multi_currency not installed, not used
2335
2336 IF l_debug = Fnd_Api.G_TRUE THEN
2337 Oe_Debug_Pub.ADD('round_price - multi-currency NOT installed');
2338
2339 END IF;
2340 -- cache the rounding_factor for price list id
2341 IF p_price_list_id = G_PRICE_LIST_ID THEN
2342 IF l_debug = Fnd_Api.G_TRUE THEN
2343 Oe_Debug_Pub.ADD('round_price - getting rounding factor from cache');
2344 END IF;
2345 l_rounding_factor := G_ROUNDING_FACTOR;
2346 ELSE
2347 IF l_debug = Fnd_Api.G_TRUE THEN
2348 Oe_Debug_Pub.ADD('round_price - getting rounding factor from database');
2349
2350 END IF;
2351 SELECT qb.rounding_factor
2352 INTO l_rounding_factor
2353 FROM qp_list_headers_b qb
2354 WHERE qb.list_header_id = p_price_list_id;
2355 /* for bug 2350218
2356 and qb.currency_code = p_currency_code
2357 and l_pricing_effective_date BETWEEN
2358 NVL(TRUNC(QB.START_DATE_ACTIVE),l_PRICING_EFFECTIVE_DATE)
2359 AND NVL(TRUNC(QB.END_DATE_ACTIVE),l_PRICING_EFFECTIVE_DATE);
2360 */
2361
2362 G_PRICE_LIST_ID := p_price_list_id;
2363 G_ROUNDING_FACTOR := l_rounding_factor;
2364 END IF;
2365
2366 END IF;
2367
2368 END IF; --p_price_list_id, p_currency_code values not null
2369
2370 IF l_debug = Fnd_Api.G_TRUE THEN
2371 Oe_Debug_Pub.ADD('l_rounding_factor = ' || l_rounding_factor);
2372
2373 END IF;
2374 IF l_rounding_factor IS NOT NULL THEN
2375 IF p_operand_type = 'R' THEN
2376 x_rounded_operand := l_rounding_factor;
2377 ELSE
2378 x_rounded_operand := ROUND(p_operand, l_rounding_factor * -1);
2379 END IF;
2380 ELSE
2381 x_rounded_operand := p_operand;
2382 END IF;
2383
2384 ELSE /* if p_operand_type = 'A' .......*/
2385 IF l_debug = Fnd_Api.G_TRUE THEN
2386 Oe_Debug_Pub.ADD('NO rounding');
2387 END IF;
2388 --dbms_output.put_line('NO rounding');
2389 x_rounded_operand := p_operand;
2390 END IF; /* p_operand_type */
2391
2392 x_status_code := Fnd_Api.G_RET_STS_SUCCESS;
2393
2394 IF l_debug = Fnd_Api.G_TRUE THEN
2395 Oe_Debug_Pub.ADD('x_rounded_operand = ' || x_rounded_operand);
2396 Oe_Debug_Pub.ADD('x_status_code = ' || x_status_code);
2397 Oe_Debug_Pub.ADD('END round_price');
2398 END IF;
2399 EXCEPTION
2400 WHEN NO_DATA_FOUND THEN
2401 IF l_debug = Fnd_Api.G_TRUE THEN
2402 Oe_Debug_Pub.ADD('round_price - NO_DATA_FOUND exception');
2403 END IF;
2404 x_status_code := Fnd_Api.G_RET_STS_ERROR;
2405 IF Fnd_Msg_Pub.Check_Msg_Level(Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
2406 Fnd_Message.SET_NAME('QP','QP_NO_RECORD_FOR_ROUNDING');
2407 Fnd_Message.SET_TOKEN('PRICE_LIST', p_price_list_id);
2408 Fnd_Message.SET_TOKEN('CURRENCY', p_currency_code);
2409 END IF;
2410
2411 WHEN Fnd_Api.G_EXC_ERROR THEN
2412
2413 IF l_debug = Fnd_Api.G_TRUE THEN
2414 Oe_Debug_Pub.ADD('round_price - EXPECTED exception');
2415 END IF;
2416 x_status_code := Fnd_Api.G_RET_STS_ERROR;
2417
2418 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
2419 IF l_debug = Fnd_Api.G_TRUE THEN
2420 Oe_Debug_Pub.ADD('round_price - UNEXPECTED exception');
2421
2422 END IF;
2423 x_status_code := Fnd_Api.G_RET_STS_UNEXP_ERROR;
2424
2425 WHEN OTHERS THEN
2426 IF l_debug = Fnd_Api.G_TRUE THEN
2427 Oe_Debug_Pub.ADD('round_price - OTHERS exception');
2428 END IF;
2429 x_status_code := Fnd_Api.G_RET_STS_UNEXP_ERROR;
2430
2431 IF Fnd_Msg_Pub.Check_Msg_Level(Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR)
2432 THEN
2433 Fnd_Msg_Pub.Add_Exc_Msg
2434 (G_PKG_NAME
2435 , 'Round Price'
2436 );
2437 END IF;
2438
2439 END round_price;
2440
2441
2442 --to be used by OM-QP Integration to call new code path for QP.G
2443 --if there are only basic modifiers in setup
2444 FUNCTION Basic_Pricing_Setup RETURN VARCHAR2 IS
2445 x_basic_pricing_setup VARCHAR2(1) := 'N';
2446 BEGIN
2447 Fnd_Profile.Get('QP_BASIC_MODIFIERS_SETUP',x_basic_pricing_setup);
2448 RETURN x_basic_pricing_setup;
2449 END Basic_Pricing_Setup;
2450
2451 PROCEDURE Reprice_Debug_Engine_Request(
2452 p_request_id IN NUMBER,
2453 x_request_id OUT NOCOPY NUMBER,
2454 x_return_status OUT NOCOPY VARCHAR2,
2455 x_return_status_text OUT NOCOPY VARCHAR2)
2456 IS
2457 p_line_tbl Qp_Preq_Grp.LINE_TBL_TYPE;
2458 p_qual_tbl Qp_Preq_Grp.QUAL_TBL_TYPE;
2459 p_line_attr_tbl Qp_Preq_Grp.LINE_ATTR_TBL_TYPE;
2460 p_related_lines_tbl Qp_Preq_Grp.RELATED_LINES_TBL_TYPE;
2461
2462 p_line_detail_tbl Qp_Preq_Grp.LINE_DETAIL_TBL_TYPE;
2463 p_line_detail_qual_tbl Qp_Preq_Grp.LINE_DETAIL_QUAL_TBL_TYPE;
2464 p_line_detail_attr_tbl Qp_Preq_Grp.LINE_DETAIL_ATTR_TBL_TYPE;
2465
2466 p_control_rec Qp_Preq_Grp.CONTROL_RECORD_TYPE;
2467
2468 x_line_tbl Qp_Preq_Grp.LINE_TBL_TYPE;
2469 x_line_qual Qp_Preq_Grp.QUAL_TBL_TYPE;
2470 x_line_attr_tbl Qp_Preq_Grp.LINE_ATTR_TBL_TYPE;
2471 x_line_detail_tbl Qp_Preq_Grp.LINE_DETAIL_TBL_TYPE;
2472 x_line_detail_qual_tbl Qp_Preq_Grp.LINE_DETAIL_QUAL_TBL_TYPE;
2473 x_line_detail_attr_tbl Qp_Preq_Grp.LINE_DETAIL_ATTR_TBL_TYPE;
2474 x_related_lines_tbl Qp_Preq_Grp.RELATED_LINES_TBL_TYPE;
2475
2476 l_return_status VARCHAR2(240);
2477 l_return_status_text VARCHAR2(240);
2478
2479 CURSOR cl_req_dbg IS
2480 SELECT *
2481 FROM qp_debug_req
2482 WHERE request_id = p_request_id;
2483
2484 CURSOR cl_lines_dbg(p_req_id NUMBER) IS
2485 SELECT *
2486 FROM qp_debug_req_lines
2487 WHERE request_id = p_req_id;
2488
2489 CURSOR cl_qual_dbg(p_req_id NUMBER) IS
2490 SELECT *
2491 FROM qp_debug_req_line_attrs
2492 WHERE request_id = p_req_id AND
2493 attribute_type = 'QUALIFIER' AND
2494 line_detail_index IS NULL;
2495
2496 CURSOR cl_line_attr_dbg(p_req_id NUMBER) IS
2497 SELECT *
2498 FROM qp_debug_req_line_attrs
2499 WHERE request_id = p_req_id AND
2500 attribute_type IN ('PRODUCT','PRICING') AND
2501 line_detail_index IS NULL;
2502
2503 CURSOR cl_ldets_dbg(p_req_id NUMBER) IS
2504 SELECT *
2505 FROM qp_debug_req_ldets
2506 WHERE request_id = p_req_id;
2507
2508
2509 CURSOR cl_rltd_dbg(p_req_id NUMBER) IS
2510 SELECT *
2511 FROM qp_debug_req_rltd_lines
2512 WHERE request_id = p_req_id AND
2513 relationship_type_code = Qp_Preq_Grp.G_SERVICE_LINE;
2514
2515
2516 l_dbg_req_rec qp_debug_req%ROWTYPE;
2517
2518 --l_dbg_req_lines qp_debug_req_lines%ROWTYPE;
2519 --l_dbg_req_ldets qp_debug_req_ldets%ROWTYPE;
2520 --l_dbg_req_qual qp_debug_req_line_attrs%ROWTYPE;
2521 --l_dbg_req_attr qp_debug_req_line_attrs%ROWTYPE;
2522 --l_dbg_req_rltd qp_debug_req_rltd_lines%ROWTYPE;
2523
2524 I NUMBER;
2525 BEGIN
2526 l_debug := Qp_Preq_Grp.G_DEBUG_ENGINE;
2527 IF l_debug = Fnd_Api.G_TRUE THEN
2528 Oe_Debug_Pub.ADD('QP_UTIL_PUB.Reprice_Debug_Engine_Request BEGIN');
2529 Oe_Debug_Pub.ADD('p_request_id = ' || p_request_id);
2530 END IF;
2531 --dbms_output.put_line('Reprice_Debug_Engine_Request Begins');
2532
2533 /* profile settings */
2534 Fnd_Profile.PUT('QP_DEBUG','Y');
2535 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
2536 x_return_status_text := 'Routine: QP_UTIL_PUB.Reprice_Debug_Engine_Request SUCCESS';
2537
2538 IF ( p_request_id IS NULL) THEN
2539 x_return_status := Fnd_Api.G_RET_STS_ERROR;
2540 x_return_status_text := 'Need request_id for Reprice_Debug_Engine_Request API';
2541 RETURN;
2542 END IF;
2543
2544 /* reconstruct p_contrl_rec */
2545 OPEN cl_req_dbg;
2546 FETCH cl_req_dbg INTO l_dbg_req_rec;
2547 CLOSE cl_req_dbg;
2548
2549 IF (l_dbg_req_rec.request_id IS NULL) THEN
2550 x_return_status := Fnd_Api.G_RET_STS_ERROR;
2551 x_return_status_text := 'No debug record exists for request_id'||p_request_id;
2552 RETURN;
2553 END IF;
2554
2555 p_control_rec.pricing_event := l_dbg_req_rec.pricing_event;
2556 p_control_rec.calculate_flag := l_dbg_req_rec.calculate_flag;
2557 p_control_rec.simulation_flag := l_dbg_req_rec.simulation_flag;
2558 p_control_rec.rounding_flag := l_dbg_req_rec.rounding_flag;
2559 p_control_rec.GSA_CHECK_FLAG:=l_dbg_req_rec.GSA_CHECK_FLAG;
2560 p_control_rec.GSA_DUP_CHECK_FLAG:= l_dbg_req_rec.GSA_DUP_CHECK_FLAG
2561 ;
2562 p_control_rec.temp_table_insert_flag := l_dbg_req_rec.temp_table_insert_flag;
2563 --p_control_rec.temp_table_insert_flag := 'Y';
2564 p_control_rec.manual_discount_flag := l_dbg_req_rec.manual_discount_flag;
2565 p_control_rec.debug_flag := l_dbg_req_rec.debug_flag;
2566 p_control_rec.source_order_amount_flag := l_dbg_req_rec.source_order_amount_flag;
2567 p_control_rec.public_api_call_flag := l_dbg_req_rec.public_api_call_flag;
2568 p_control_rec.manual_adjustments_call_flag:= l_dbg_req_rec.manual_adjustments_call_flag;
2569 p_control_rec.check_cust_view_flag := l_dbg_req_rec.check_cust_view_flag;
2570 p_control_rec.request_type_code:=l_dbg_req_rec.request_type_code;
2571 p_control_rec.view_code:=l_dbg_req_rec.view_code;
2572 --USE_MULTI_CURRENCY VARCHAR2(1) default 'N'; vivek
2573 --USER_CONVERSION_RATE NUMBER default NULL; vivek
2574 --USER_CONVERSION_TYPE VARCHAR2(30) default NULL; vivek
2575 --function_currency:= l_dbg_req_rec.currency_code;vivek
2576 p_control_rec.function_currency:= l_dbg_req_rec.currency_code;
2577
2578
2579 /* recontruct p_line_tbl */
2580 I := 0;
2581 FOR cl IN cl_lines_dbg(p_request_id)
2582 LOOP
2583 I:= I+1;
2584 --dbms_output.put_line('line index '||cl.line_Index||' cnt '|| I);
2585
2586 p_line_tbl(I).request_type_code :=cl.request_type_code;
2587 --p_line_tbl(I).pricing_event :=cl.request_type_code;
2588 --p_line_tbl(I).header_id :=cl.price_list_header_id;
2589 p_line_tbl(I).line_Index :=cl.line_Index;
2590 p_line_tbl(I).line_id :=cl.line_id;
2591 p_line_tbl(I).line_type_code := cl.line_type_code;
2592 p_line_tbl(I).pricing_effective_date := cl.pricing_effective_date;
2593 p_line_tbl(I).active_date_first := cl.start_date_active_first;
2594 p_line_tbl(I).active_date_first_type := cl.active_date_first_type;
2595 p_line_tbl(I).active_date_second := cl.start_date_active_second;
2596 p_line_tbl(I).active_date_second_type :=cl.active_date_second_type;
2597 p_line_tbl(I).line_quantity := cl.line_quantity;
2598 p_line_tbl(I).line_uom_code := cl.line_uom_code;
2599 p_line_tbl(I).uom_quantity := cl.uom_quantity;
2600 p_line_tbl(I).priced_quantity := cl.priced_quantity;
2601 p_line_tbl(I).priced_uom_code := cl.priced_uom_code;
2602 p_line_tbl(I).currency_code := cl.currency_code;
2603 --dbms_output.put_line('currency '||p_line_tbl(I).currency_code);
2604 p_line_tbl(I).unit_price := cl.unit_price;
2605 p_line_tbl(I).percent_price := cl.percent_price;
2606 p_line_tbl(I).adjusted_unit_price := cl.adjusted_unit_price;
2607 p_line_tbl(I).updated_adjusted_unit_price := cl.updated_adjusted_unit_price;
2608 p_line_tbl(I).parent_price := cl.parent_price;
2609 p_line_tbl(I).parent_quantity := cl.parent_quantity;
2610 p_line_tbl(I).rounding_factor := cl.rounding_factor;
2611 p_line_tbl(I).parent_uom_code := cl.parent_uom_code;
2612 --p_line_tbl(I).price_phase_id := cl.price_flag;
2613 p_line_tbl(I).price_flag := cl.price_flag;
2614 p_line_tbl(I).processed_code := cl.processed_code;
2615 p_line_tbl(I).price_request_code := cl.price_request_code;
2616 p_line_tbl(I).hold_code := cl.hold_code;
2617 p_line_tbl(I).hold_text := cl.hold_text;
2618 p_line_tbl(I).status_code := cl.pricing_status_code;
2619 p_line_tbl(I).status_text := cl.pricing_status_text;
2620 p_line_tbl(I).usage_pricing_type := cl.usage_pricing_type;
2621 --p_line_tbl(I).line_category := cl.usage_pricing_type;
2622 --p_line_tbl(I).contract_start_date := cl.usage_pricing_type;
2623 --p_line_tbl(I).contract_end_date := cl.usage_pricing_type;
2624 --p_line_tbl(I).line_unit_price := cl.usage_pricing_type;
2625 END LOOP;
2626
2627 /* reconstruct p_line_attr_tbl */
2628 I := 0;
2629 FOR cl IN cl_line_attr_dbg(p_request_id)
2630 LOOP
2631 I := I+1;
2632 --dbms_output.put_line('line index '||cl.line_Index||' cnt '||i);
2633 p_line_attr_tbl(I).LINE_INDEX := cl.LINE_INDEX;
2634 p_line_attr_tbl(I).PRICING_CONTEXT :=cl.context;
2635 p_line_attr_tbl(I).PRICING_ATTRIBUTE :=cl.attribute;
2636 p_line_attr_tbl(I).PRICING_ATTR_VALUE_FROM := cl.value_from;
2637 p_line_attr_tbl(I).PRICING_ATTR_VALUE_TO := cl.value_to;
2638 p_line_attr_tbl(I).VALIDATED_FLAG :=cl.VALIDATED_FLAG;
2639 p_line_attr_tbl(I).STATUS_CODE :=cl.PRICING_STATUS_CODE;
2640 p_line_attr_tbl(I).STATUS_TEXT :=cl.PRICING_STATUS_TEXT;
2641 END LOOP;
2642
2643 /* reconstruct p_qual_tbl */
2644 I := 0;
2645 FOR cl IN cl_qual_dbg(p_request_id)
2646 LOOP
2647 I := I+1;
2648 --dbms_output.put_line('line index '||cl.line_Index||' cnt '||i);
2649 p_qual_tbl(i).LINE_INDEX := cl.LINE_INDEX;
2650 p_qual_tbl(i).QUALIFIER_CONTEXT :=cl.context;
2651 p_qual_tbl(i).QUALIFIER_ATTRIBUTE :=cl.attribute;
2652 p_qual_tbl(i).QUALIFIER_ATTR_VALUE_FROM := cl.value_from;
2653 p_qual_tbl(i).QUALIFIER_ATTR_VALUE_TO := cl.value_to;
2654 p_qual_tbl(i).COMPARISON_OPERATOR_CODE := cl.COMPARISON_OPERATOR_TYPE_CODE;
2655 p_qual_tbl(i).VALIDATED_FLAG :=cl.VALIDATED_FLAG;
2656 p_qual_tbl(i).STATUS_CODE :=cl.PRICING_STATUS_CODE;
2657 p_qual_tbl(i).STATUS_TEXT :=cl.PRICING_STATUS_TEXT;
2658 END LOOP;
2659
2660 /* reconstruct p_line_detail_tbl */
2661 I := 0;
2662 FOR cl IN cl_ldets_dbg(p_request_id)
2663 LOOP
2664 I := I+1;
2665 --dbms_output.put_line('line index '||cl.line_Index||' cnt '||i);
2666 p_line_detail_tbl(i).LINE_DETAIL_INDEX := cl.LINE_DETAIL_INDEX;
2667 --p_line_detail_tbl(i).LINE_DETAIL_ID := cl.LINE_INDEX;
2668 p_line_detail_tbl(i).LINE_DETAIL_TYPE_CODE := cl.LINE_DETAIL_TYPE_CODE;
2669 p_line_detail_tbl(i).LINE_INDEX := cl.LINE_INDEX;
2670 p_line_detail_tbl(i).LIST_HEADER_ID := cl.CREATED_FROM_LIST_HEADER_ID;
2671 p_line_detail_tbl(i).LIST_LINE_ID := cl.CREATED_FROM_LIST_LINE_ID;
2672 p_line_detail_tbl(i).LIST_LINE_TYPE_CODE := cl.CREATED_FROM_LIST_TYPE_CODE;
2673 p_line_detail_tbl(i).SUBSTITUTION_TYPE_CODE := cl.SUBSTITUTION_TYPE_CODE;
2674 p_line_detail_tbl(i).SUBSTITUTION_FROM := cl.SUBSTITUTION_VALUE_FROM;
2675 p_line_detail_tbl(i).SUBSTITUTION_TO := cl.SUBSTITUTION_VALUE_TO;
2676 p_line_detail_tbl(i).AUTOMATIC_FLAG := cl.AUTOMATIC_FLAG;
2677 p_line_detail_tbl(i).OPERAND_CALCULATION_CODE := cl.OPERAND_CALCULATION_CODE;
2678 p_line_detail_tbl(i).OPERAND_VALUE := cl.OPERAND_VALUE;
2679 p_line_detail_tbl(i).PRICING_GROUP_SEQUENCE := cl.PRICING_GROUP_SEQUENCE;
2680 p_line_detail_tbl(i).PRICE_BREAK_TYPE_CODE := cl.PRICE_BREAK_TYPE_CODE;
2681 p_line_detail_tbl(i).CREATED_FROM_LIST_TYPE_CODE := cl.CREATED_FROM_LIST_TYPE_CODE;
2682 p_line_detail_tbl(i).PRICING_PHASE_ID := cl.PRICING_PHASE_ID;
2683 --p_line_detail_tbl(i).LIST_PRICE := cl.LINE_INDEX;
2684 p_line_detail_tbl(i).LINE_QUANTITY := cl.LINE_QUANTITY;
2685 p_line_detail_tbl(i).ADJUSTMENT_AMOUNT := cl.ADJUSTMENT_AMOUNT;
2686 p_line_detail_tbl(i).APPLIED_FLAG := cl.APPLIED_FLAG;
2687 p_line_detail_tbl(i).MODIFIER_LEVEL_CODE := cl.MODIFIER_LEVEL_CODE;
2688 p_line_detail_tbl(i).STATUS_CODE := cl.PRICING_STATUS_CODE;
2689 p_line_detail_tbl(i).STATUS_TEXT := cl.PRICING_STATUS_TEXT;
2690 --p_line_detail_tbl(i).SUBSTITUTION_ATTRIBUTE := cl
2691 p_line_detail_tbl(i).ACCRUAL_FLAG := cl.ACCRUAL_FLAG;
2692 p_line_detail_tbl(i).LIST_LINE_NO := cl.LIST_LINE_NO;
2693 --p_line_detail_tbl(i).ESTIM_GL_VALUE := cl.
2694 p_line_detail_tbl(i).ACCRUAL_CONVERSION_RATE := cl.ACCRUAL_CONVERSION_RATE;
2695 p_line_detail_tbl(i).OVERRIDE_FLAG := cl.OVERRIDE_FLAG;
2696 p_line_detail_tbl(i).PRINT_ON_INVOICE_FLAG := cl.PRINT_ON_INVOICE_FLAG;
2697 --p_line_detail_tbl(i).INVENTORY_ITEM_ID := cl.
2698 --p_line_detail_tbl(i).ORGANIZATION_ID := cl.LINE_INDEX;
2699 --p_line_detail_tbl(i).RELATED_ITEM_ID := cl.LINE_INDEX;
2700 --p_line_detail_tbl(i).RELATIONSHIP_TYPE_ID := cl.LINE_INDEX;
2701 p_line_detail_tbl(i).ESTIM_ACCRUAL_RATE := cl.ESTIM_ACCRUAL_RATE;
2702 --p_line_detail_tbl(i).EXPIRATION_DATE := cl.
2703 --p_line_detail_tbl(i).BENEFIT_PRICE_LIST_LINE_ID := cl.LINE_INDEX;
2704 p_line_detail_tbl(i).RECURRING_FLAG := cl.RECURRING_FLAG;
2705 --p_line_detail_tbl(i).BENEFIT_LIMIT := cl.LINE_INDEX;
2706 p_line_detail_tbl(i).CHARGE_TYPE_CODE := cl.CHARGE_TYPE_CODE;
2707 p_line_detail_tbl(i).CHARGE_SUBTYPE_CODE := cl.CHARGE_SUBTYPE_CODE;
2708 --p_line_detail_tbl(i).INCLUDE_ON_RETURNS_FLAG := cl.LINE_INDEX;
2709 p_line_detail_tbl(i).BENEFIT_QTY := cl.BENEFIT_QTY;
2710 p_line_detail_tbl(i).BENEFIT_UOM_CODE := cl.BENEFIT_UOM_CODE;
2711 --p_line_detail_tbl(i).PRORATION_TYPE_CODE := cl.
2712 --p_line_detail_tbl(i).SOURCE_SYSTEM_CODE := cl.LINE_INDEX;
2713 --p_line_detail_tbl(i).REBATE_TRANSACTION_TYPE_CODE := cl.LINE_INDEX;
2714 p_line_detail_tbl(i).SECONDARY_PRICELIST_IND := cl.SECONDARY_PRICELIST_IND;
2715 --p_line_detail_tbl(i).GROUP_VALUE := cl.LINE_INDEX;
2716 --p_line_detail_tbl(i).COMMENTS := cl.LINE_INDEX;
2717 p_line_detail_tbl(i).UPDATED_FLAG := cl.UPDATED_FLAG;
2718 p_line_detail_tbl(i).PROCESS_CODE := cl.PROCESS_CODE;
2719 p_line_detail_tbl(i).LIMIT_CODE := cl.LIMIT_CODE;
2720 p_line_detail_tbl(i).LIMIT_TEXT := cl.LIMIT_TEXT;
2721 p_line_detail_tbl(i).FORMULA_ID := cl.PRICE_FORMULA_ID;
2722 p_line_detail_tbl(i).CALCULATION_CODE := cl.OPERAND_CALCULATION_CODE;
2723 p_line_detail_tbl(i).ROUNDING_FACTOR := cl.ROUNDING_FACTOR;
2724 --p_line_detail_tbl(i).currency_detail_id := cl.LINE_INDEX;
2725 --p_line_detail_tbl(i).currency_header_id:= cl.LINE_INDEX;
2726 --p_line_detail_tbl(i).selling_rounding_factor:= cl.LINE_INDEX;
2727 --p_line_detail_tbl(i).order_currency:= cl.LINE_INDEX;
2728 --p_line_detail_tbl(i).pricing_effective_date:= cl.LINE_INDEX;
2729 --p_line_detail_tbl(i).base_currency_code:= cl.LINE_INDEX;
2730 p_line_detail_tbl(i).change_reason_code:= cl.LINE_INDEX;
2731 p_line_detail_tbl(i).change_reason_text:= cl.LINE_INDEX;
2732 END LOOP;
2733
2734 /* recontruct p_related_lines_tbl */
2735 I := 0;
2736 FOR cl IN cl_rltd_dbg(p_request_id)
2737 LOOP
2738 I := I+1;
2739 --dbms_output.put_line('line index '||cl.line_Index||' cnt '||i);
2740 p_related_lines_tbl(i).LINE_INDEX := cl.LINE_INDEX;
2741 p_related_lines_tbl(i).LINE_DETAIL_INDEX :=cl.LINE_DETAIL_INDEX;
2742 p_related_lines_tbl(i).RELATIONSHIP_TYPE_CODE :=cl.RELATIONSHIP_TYPE_CODE;
2743 p_related_lines_tbl(i).RELATED_LINE_INDEX := cl.RELATED_LINE_INDEX;
2744 p_related_lines_tbl(i).RELATED_LINE_DETAIL_INDEX := cl.RELATED_LINE_DETAIL_INDEX;
2745 p_related_lines_tbl(i).STATUS_CODE :=cl.PRICING_STATUS_CODE;
2746 p_related_lines_tbl(i).STATUS_TEXT :=cl.PRICING_STATUS_TEXT;
2747 END LOOP;
2748
2749 /* debug info */
2750 Oe_Debug_Pub.G_DIR :='/sqlcom/log';
2751 Oe_Debug_Pub.Initialize;
2752 Oe_Debug_Pub.debug_on;
2753 Oe_Debug_Pub.SetDebugLevel(10);
2754 IF l_debug = Fnd_Api.G_TRUE THEN
2755 Oe_Debug_Pub.ADD('Before Process_Order',1);
2756
2757 END IF;
2758 --dbms_output.put_line('The File is'|| oe_debug_pub.Set_Debug_Mode('FILE'));
2759 --dbms_output.put_line('The debug is ' || oe_debug_pub.g_debug);
2760 --l_version := qp_preq_grp.GET_VERSION;
2761 --dbms_output.put_line('Testing version '||l_version);
2762 --dbms_output.put_line('Debug2: ' || oe_debug_pub.g_debug);
2763 --dbms_output.put_line('Debug2: ');
2764
2765 /* call engine pub API */
2766 Qp_Preq_Pub.PRICE_REQUEST
2767 (p_line_tbl,
2768 p_qual_tbl,
2769 p_line_attr_tbl,
2770 p_line_detail_tbl,
2771 p_line_detail_qual_tbl,
2772 p_line_detail_attr_tbl,
2773 p_related_lines_tbl,
2774 p_control_rec,
2775 x_line_tbl,
2776 x_line_qual,
2777 x_line_attr_tbl,
2778 x_line_detail_tbl,
2779 x_line_detail_qual_tbl,
2780 x_line_detail_attr_tbl,
2781 x_related_lines_tbl,
2782 l_return_status,
2783 l_return_status_text);
2784 --dbms_output.put_line('Return Status l_return_status '|| l_return_status);
2785 --dbms_output.put_line('Return Status text l_return_status_text '|| l_return_status_text);
2786 --dbms_output.put_line('+-----Information returned to caller:----------+ ');
2787
2788 IF l_return_status <> Fnd_Api.G_RET_STS_SUCCESS THEN
2789 x_return_status := l_return_status;
2790 x_return_status_text := l_return_status_text;
2791 END IF;
2792
2793 x_request_id := Qp_Copy_Debug_Pvt.REQUEST_ID;
2794
2795 EXCEPTION
2796 WHEN OTHERS THEN
2797 x_return_status := Fnd_Api.G_RET_STS_ERROR;
2798 x_return_status_text := 'Routine: QP_UTIL_PUB.Reprice_Debug_Engine_Request ERROR'||SQLERRM;
2799 END Reprice_Debug_Engine_Request;
2800
2801 -- New procedure as per bug 2943038, required by Quoting
2802 PROCEDURE Get_Price_List_Currency
2803 (
2804 p_price_list_id IN NUMBER
2805 ,x_sql_string OUT NOCOPY VARCHAR2
2806 )
2807 IS
2808 l_no_multi_curr_sql VARCHAR2(2000) := 'SELECT distinct fnd.currency_code,fnd.name
2809 FROM fnd_currencies_vl fnd, qp_list_headers_b qlh
2810 WHERE fnd.currency_code = qlh.currency_code AND qlh.list_type_code in (''PRL'', ''AGR'')
2811 AND fnd.currency_flag = ''Y'' AND fnd.enabled_flag = ''Y''';
2812
2813 l_multi_curr_sql VARCHAR2(2000) := 'SELECT distinct fnd.currency_code,fnd.name
2814 FROM fnd_currencies_vl fnd, qp_currency_details qcd, qp_list_headers_b qlh
2815 WHERE qcd.currency_header_id = qlh.currency_header_id AND fnd.currency_code = qcd.to_currency_code
2816 AND qlh.list_type_code in (''PRL'', ''AGR'') and fnd.currency_flag = ''Y''
2817 AND fnd.enabled_flag = ''Y''';
2818 BEGIN
2819 IF NVL(Fnd_Profile.value('QP_MULTI_CURRENCY_INSTALLED'), 'N') = 'Y'
2820 AND (NVL(Fnd_Profile.value('QP_MULTI_CURRENCY_USAGE'), 'Y') = 'Y') THEN
2821 x_sql_string := l_multi_curr_sql;
2822
2823 ELSE
2824 x_sql_string := l_no_multi_curr_sql;
2825
2826 END IF;
2827
2828 IF p_price_list_id IS NOT NULL THEN
2829 x_sql_string := x_sql_string || ' and qlh.list_header_id = ' || p_price_list_id;
2830 END IF;
2831
2832 EXCEPTION
2833 WHEN OTHERS THEN
2834 IF Fnd_Msg_Pub.Check_Msg_Level(Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
2835 Fnd_Message.SET_NAME('QP', 'QP_ERROR_GET_CURR_F_PRICELIST');
2836 END IF;
2837
2838 END Get_Price_List_Currency;
2839
2840 FUNCTION HVOP_Pricing_Setup RETURN VARCHAR2 IS
2841 ret_val VARCHAR2(1) := 'N';
2842 BEGIN
2843 IF Qp_Java_Engine_Util_Pub.Java_Engine_Running = 'Y' THEN
2844 ret_val := NVL (Fnd_Profile.Value('QP_HVOP_PRICING_SETUP'), 'N');
2845 END IF;
2846 RETURN ret_val;
2847
2848 EXCEPTION
2849 WHEN OTHERS THEN
2850 RETURN 'N';
2851 END HVOP_Pricing_Setup;
2852
2853
2854 FUNCTION HVOP_Pricing_ON RETURN VARCHAR2 IS
2855 ret_val VARCHAR2(1) := 'N';
2856 BEGIN
2857 IF Qp_Java_Engine_Util_Pub.Java_Engine_Running = 'Y' THEN
2858 ret_val := NVL(Qp_Bulk_Preq_Grp.G_HVOP_pricing_ON,'N');
2859 END IF;
2860 RETURN ret_val;
2861 EXCEPTION
2862 WHEN OTHERS THEN
2863 RETURN 'N';
2864 END HVOP_Pricing_ON;
2865
2866 --Fix for bug 3550303 to reset the QP_BULK_PREQ_GRP.G_HVOP_pricing_ON
2867 --at the end of HVOP pricing call
2868 PROCEDURE RESET_HVOP_PRICING_ON IS
2869 BEGIN
2870 l_debug := Qp_Preq_Grp.G_DEBUG_ENGINE;
2871 IF HVOP_Pricing_ON = 'Y' THEN
2872 IF l_debug = Fnd_Api.G_TRUE THEN
2873 Qp_Preq_Grp.Engine_debug('HVOP_Pricing_On is: '||Qp_Bulk_Preq_Grp.G_HVOP_pricing_ON);
2874 Qp_Preq_Grp.Engine_debug('Resetting HVOP_Pricing_On at the end');
2875 END IF;--l_debug
2876 Qp_Bulk_Preq_Grp.G_HVOP_pricing_ON := 'N';
2877 IF l_debug = Fnd_Api.G_TRUE THEN
2878 Qp_Preq_Grp.Engine_debug('HVOP_Pricing_On Now Reset to: '||Qp_Bulk_Preq_Grp.G_HVOP_pricing_ON);
2879 END IF;--l_debug
2880 END IF;--HVOP_Pricing_ON
2881 EXCEPTION
2882 WHEN OTHERS THEN
2883 Qp_Bulk_Preq_Grp.G_HVOP_pricing_ON := 'N';
2884 END RESET_HVOP_PRICING_ON;
2885
2886 -- New procedure for bug 3118385
2887 PROCEDURE Get_Attribute_Text(p_attributes_tbl IN OUT NOCOPY attribute_tbl)
2888 IS
2889 l_segment_code VARCHAR2(80);
2890 BEGIN
2891 IF p_attributes_tbl.COUNT > 0 THEN
2892 FOR i IN 1..p_attributes_tbl.COUNT
2893 LOOP
2894 -- find context text
2895 IF p_attributes_tbl(i).attribute_type IN ('PRODUCT', 'PRICING') THEN
2896 p_attributes_tbl(i).context_text := Qp_Util.get_context('QP_ATTR_DEFNS_PRICING',
2897 p_attributes_tbl(i).context_code);
2898 ELSIF p_attributes_tbl(i).attribute_type = 'QUALIFIER' THEN
2899 p_attributes_tbl(i).context_text := Qp_Util.get_context('QP_ATTR_DEFNS_QUALIFIER',
2900 p_attributes_tbl(i).context_code);
2901 END IF;
2902
2903 -- find attribute text
2904 IF p_attributes_tbl(i).attribute_type IN ('PRODUCT', 'PRICING') THEN
2905 Qp_Util.Get_Attribute_Code(p_FlexField_Name => 'QP_ATTR_DEFNS_PRICING',
2906 p_Context_Name => p_attributes_tbl(i).context_code,
2907 p_attribute => p_attributes_tbl(i).attribute_code,
2908 x_attribute_code => p_attributes_tbl(i).attribute_text,
2909 x_segment_name => l_segment_code);
2910 ELSIF p_attributes_tbl(i).attribute_type = 'QUALIFIER' THEN
2911 Qp_Util.Get_Attribute_Code(p_FlexField_Name => 'QP_ATTR_DEFNS_QUALIFIER',
2912 p_Context_Name => p_attributes_tbl(i).context_code,
2913 p_attribute => p_attributes_tbl(i).attribute_code,
2914 x_attribute_code => p_attributes_tbl(i).attribute_text,
2915 x_segment_name => l_segment_code);
2916 END IF;
2917
2918 -- find attribute value text
2919 IF p_attributes_tbl(i).attribute_type = 'PRODUCT' THEN
2920 p_attributes_tbl(i).attribute_value_from_text :=
2921 Qp_Price_List_Line_Util.Get_Product_Value('QP_ATTR_DEFNS_PRICING'
2922 ,p_attributes_tbl(i).context_code
2923 ,p_attributes_tbl(i).attribute_code
2924 ,p_attributes_tbl(i).attribute_value_from
2925 );
2926
2927 ELSIF p_attributes_tbl(i).attribute_type = 'PRICING' THEN
2928 p_attributes_tbl(i).attribute_value_from_text :=
2929 Qp_Util.Get_Attribute_Value('QP_ATTR_DEFNS_PRICING'
2930 ,p_attributes_tbl(i).context_code
2931 ,p_attributes_tbl(i).attribute_code
2932 ,p_attributes_tbl(i).attribute_value_from
2933 ,p_attributes_tbl(i).operator
2934 );
2935 ELSIF p_attributes_tbl(i).attribute_type = 'QUALIFIER' THEN
2936 p_attributes_tbl(i).attribute_value_from_text :=
2937 Qp_Util.Get_Attribute_Value('QP_ATTR_DEFNS_QUALIFIER'
2938 ,p_attributes_tbl(i).context_code
2939 ,p_attributes_tbl(i).attribute_code
2940 ,p_attributes_tbl(i).attribute_value_from
2941 ,p_attributes_tbl(i).operator
2942 );
2943 END IF;
2944 END LOOP;
2945 END IF;
2946
2947 END Get_Attribute_Text;
2948
2949 -- This procedure fetchs price lists and modifier lists specific to a blanket.
2950 -- i.e. pricing data with list_source_code of Blanket and orig_system_header_ref of this blanket header
2951 PROCEDURE Get_Blanket_Pricelist_Modifier(
2952 p_blanket_header_id IN NUMBER
2953 ,x_price_list_tbl OUT NOCOPY Qp_Price_List_Pub.Price_List_Tbl_Type
2954 ,x_modifier_list_tbl OUT NOCOPY Qp_Modifiers_Pub.Modifier_List_Tbl_Type
2955 ,x_return_status OUT NOCOPY VARCHAR2
2956 ,x_msg_count OUT NOCOPY NUMBER
2957 ,x_msg_data OUT NOCOPY VARCHAR2
2958 )
2959 IS
2960 l_PRICE_LIST_rec Qp_Price_List_Pub.Price_List_Rec_Type;
2961 l_PRICE_LIST_tbl Qp_Price_List_Pub.Price_List_Tbl_Type;
2962 l_MODIFIER_LIST_rec Qp_Modifiers_Pub.Modifier_List_Rec_Type;
2963 l_MODIFIER_LIST_tbl Qp_Modifiers_Pub.Modifier_List_Tbl_Type;
2964
2965 CURSOR blanket_price_lists( p_blkt_header_id NUMBER) IS
2966 SELECT list_header_id FROM qp_list_headers_b
2967 WHERE list_source_code = 'BSO'
2968 AND orig_system_header_ref = p_blkt_header_id
2969 AND list_type_code = 'PRL';
2970
2971 CURSOR blanket_modifier_lists( p_blkt_header_id NUMBER) IS
2972 SELECT list_header_id FROM qp_list_headers_b
2973 WHERE list_source_code = 'BSO'
2974 AND orig_system_header_ref = p_blkt_header_id
2975 AND list_type_code NOT IN ('PRL','AGR');
2976
2977 BEGIN
2978 -- Get Pricelist
2979 FOR I IN blanket_price_lists(p_blanket_header_id) LOOP
2980
2981 l_PRICE_LIST_rec := Qp_Price_List_Util.Query_Row
2982 ( p_list_header_id => I.list_header_id
2983 );
2984
2985 l_PRICE_LIST_tbl(l_PRICE_LIST_tbl.COUNT + 1) := l_PRICE_LIST_rec;
2986
2987 END LOOP;
2988
2989 x_PRICE_LIST_tbl := l_PRICE_LIST_tbl;
2990
2991 -- Get Modifier list
2992 FOR I IN blanket_modifier_lists(p_blanket_header_id) LOOP
2993
2994 l_MODIFIER_LIST_rec := Qp_Modifier_List_Util.Query_Row
2995 ( p_list_header_id => I.list_header_id
2996 );
2997
2998 l_MODIFIER_LIST_tbl(l_MODIFIER_LIST_tbl.COUNT + 1) := l_MODIFIER_LIST_rec;
2999
3000 END LOOP;
3001
3002 x_MODIFIER_LIST_tbl := l_MODIFIER_LIST_tbl;
3003
3004 -- Set return status.
3005
3006 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
3007
3008 -- Get message count and data
3009
3010 Oe_Msg_Pub.Count_And_Get
3011 ( p_count => x_msg_count
3012 , p_data => x_msg_data
3013 );
3014
3015
3016 EXCEPTION
3017 WHEN NO_DATA_FOUND THEN
3018 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
3019
3020 IF Oe_Msg_Pub.Check_Msg_Level(Oe_Msg_Pub.G_MSG_LVL_UNEXP_ERROR)
3021 THEN
3022 Oe_Msg_Pub.Add_Exc_Msg
3023 ( G_PKG_NAME
3024 , 'Get_Blanket_Pricelist_Modifier'
3025 );
3026 END IF;
3027
3028 -- Get message count and data
3029
3030 Oe_Msg_Pub.Count_And_Get
3031 ( p_count => x_msg_count
3032 , p_data => x_msg_data
3033 );
3034
3035 Oe_Debug_Pub.ADD('END Get_Blanket_Price in QPXRTCNB');
3036
3037 WHEN OTHERS THEN
3038
3039 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
3040
3041 IF Oe_Msg_Pub.Check_Msg_Level(Oe_Msg_Pub.G_MSG_LVL_UNEXP_ERROR)
3042 THEN
3043 Oe_Msg_Pub.Add_Exc_Msg
3044 ( G_PKG_NAME
3045 , 'Get_Blanket_Pricelist_Modifier'
3046 );
3047 END IF;
3048
3049 -- Get message count and data
3050
3051 Oe_Msg_Pub.Count_And_Get
3052 ( p_count => x_msg_count
3053 , p_data => x_msg_data
3054 );
3055
3056 Oe_Debug_Pub.ADD('END Get_Blanket_Pricelist_Modifier in QPXRTCNB');
3057 END Get_Blanket_Pricelist_Modifier;
3058
3059 PROCEDURE Check_Pricing_Attributes (
3060 P_Api_Version_Number IN NUMBER := 1,
3061 P_Init_Msg_List IN VARCHAR2 := Fnd_Api.G_FALSE,
3062 P_Commit IN VARCHAR2 := Fnd_Api.G_FALSE,
3063 P_Inventory_Id IN NUMBER := Fnd_Api.G_MISS_NUM,
3064 P_Price_List_Id IN NUMBER := Fnd_Api.G_MISS_NUM,
3065 X_Check_Return_Status_qp OUT NOCOPY VARCHAR2,
3066 x_msg_count OUT NOCOPY NUMBER,
3067 x_msg_data OUT NOCOPY VARCHAR2)
3068 IS
3069
3070
3071
3072 CURSOR c_check_qpprc_atr IS
3073 SELECT 'X'
3074 FROM
3075 QP_PRICING_ATTRIBUTES A,
3076 QP_LIST_LINES L,
3077 QP_LIST_HEADERS_B QLH
3078 WHERE
3079 A.LIST_HEADER_ID = P_Price_List_Id AND
3080 A.PRODUCT_ATTRIBUTE_CONTEXT = 'ITEM' AND
3081 A.PRODUCT_ATTRIBUTE IN ( 'PRICING_ATTRIBUTE1','PRICING_ATTRIBUTE3' ) AND
3082 A.PRODUCT_ATTR_VALUE IN ( TO_CHAR(P_Inventory_Id),'ALL' ) AND
3083 A.PRICING_PHASE_ID = 1 AND
3084 A.QUALIFICATION_IND IN (4,6,20,22) AND
3085 A.EXCLUDER_FLAG = 'N' AND
3086 (EXISTS (SELECT NULL
3087 FROM QP_PRICE_FORMULA_LINES FL
3088 WHERE FL.PRICE_FORMULA_LINE_TYPE_CODE IN ('PRA','ML') AND
3089 FL.PRICE_FORMULA_ID = L.PRICE_BY_FORMULA_ID)
3090 OR
3091 (A.PRICING_ATTRIBUTE_CONTEXT <> 'VOLUME')) AND
3092 L.LIST_LINE_ID = A.LIST_LINE_ID AND
3093 L.LIST_LINE_TYPE_CODE = 'PLL' AND
3094 QLH.LIST_HEADER_ID = L.LIST_HEADER_ID AND
3095 QLH.LIST_TYPE_CODE = 'PRL' AND
3096 NVL(QLH.START_DATE_ACTIVE, SYSDATE) <= SYSDATE AND NVL(QLH.END_DATE_ACTIVE,SYSDATE) >= SYSDATE
3097 AND
3098 NVL(L.START_DATE_ACTIVE,SYSDATE) <= SYSDATE AND NVL(L.END_DATE_ACTIVE, SYSDATE) >= SYSDATE
3099 AND
3100 ROWNUM < 2;
3101
3102
3103 l_count NUMBER;
3104 l_list_line_id NUMBER;
3105 c_check_qpprc_atr_rec c_check_qpprc_atr%ROWTYPE;
3106
3107 BEGIN
3108 X_Check_Return_Status_qp := Fnd_Api.G_FALSE;
3109
3110 OPEN c_check_qpprc_atr;
3111 FETCH c_check_qpprc_atr INTO c_check_qpprc_atr_rec;
3112 IF c_check_qpprc_atr%FOUND THEN
3113 X_Check_Return_Status_qp := Fnd_Api.G_TRUE;
3114 END IF;
3115 CLOSE c_check_qpprc_atr;
3116
3117 END Check_Pricing_Attributes;
3118
3119
3120 PROCEDURE Check_Pricing_Attributes (
3121 P_Api_Version_Number IN NUMBER := 1,
3122 P_Init_Msg_List IN VARCHAR2 := Fnd_Api.G_FALSE,
3123 P_Commit IN VARCHAR2 := Fnd_Api.G_FALSE,
3124 P_Inventory_Id IN NUMBER := Fnd_Api.G_MISS_NUM,
3125 P_Price_List_Id IN NUMBER := Fnd_Api.G_MISS_NUM,
3126 X_Check_Return_Status_qp OUT NOCOPY VARCHAR2,
3127 x_return_status OUT NOCOPY VARCHAR2,
3128 x_msg_count OUT NOCOPY NUMBER,
3129 x_msg_data OUT NOCOPY VARCHAR2)
3130 IS
3131
3132
3133 CURSOR c_check_qpprc_atr IS
3134 SELECT 'X'
3135 FROM
3136 QP_PRICING_ATTRIBUTES A,
3137 QP_LIST_LINES L,
3138 QP_LIST_HEADERS_B QLH
3139 WHERE
3140 A.LIST_HEADER_ID = P_Price_List_Id AND
3141 A.PRODUCT_ATTRIBUTE_CONTEXT = 'ITEM' AND
3142 A.PRODUCT_ATTRIBUTE IN ( 'PRICING_ATTRIBUTE1','PRICING_ATTRIBUTE3' ) AND
3143 A.PRODUCT_ATTR_VALUE IN ( TO_CHAR(P_Inventory_Id),'ALL' ) AND
3144 A.PRICING_PHASE_ID = 1 AND
3145 A.QUALIFICATION_IND IN (4,6,20,22) AND
3146 A.EXCLUDER_FLAG = 'N' AND
3147 (EXISTS (SELECT NULL
3148 FROM QP_PRICE_FORMULA_LINES FL
3149 WHERE FL.PRICE_FORMULA_LINE_TYPE_CODE IN ('PRA','ML') AND
3150 FL.PRICE_FORMULA_ID = L.PRICE_BY_FORMULA_ID)
3151 OR
3152 (A.PRICING_ATTRIBUTE_CONTEXT <> 'VOLUME')) AND
3153 L.LIST_LINE_ID = A.LIST_LINE_ID AND
3154 L.LIST_LINE_TYPE_CODE = 'PLL' AND
3155 QLH.LIST_HEADER_ID = L.LIST_HEADER_ID AND
3156 QLH.LIST_TYPE_CODE = 'PRL' AND
3157 NVL(QLH.START_DATE_ACTIVE, SYSDATE) <= SYSDATE AND NVL(QLH.END_DATE_ACTIVE,SYSDATE) >= SYSDATE
3158 AND
3159 NVL(L.START_DATE_ACTIVE,SYSDATE) <= SYSDATE AND NVL(L.END_DATE_ACTIVE, SYSDATE) >= SYSDATE
3160 AND
3161 ROWNUM < 2;
3162
3163 /*
3164 CURSOR l_pricing_attribs IS
3165 SELECT distinct pricing_attribute_context, pricing_attribute
3166 FROM qp_pricing_attributes
3167 WHERE product_attribute IN ('PRICING_ATTRIBUTE1','PRICING_ATTRIBUTE3')
3168 AND product_attribute_context = 'ITEM'
3169 AND product_attr_value IN (to_char(P_Inventory_Id),'ALL')
3170 AND ((pricing_attribute_context IS NOT NULL AND pricing_attribute IS NOT NULL )
3171 AND (pricing_attribute_context <> 'VOLUME'
3172 AND pricing_attribute NOT IN ('PRICING_ATTRIBUTE10','PRICING_ATTRIBUTE12'
3173 ))
3174 );
3175 */
3176
3177 CURSOR l_pricing_attribs IS
3178 SELECT DISTINCT pricing_attribute_context, pricing_attribute
3179 FROM qp_pricing_attributes
3180 WHERE list_header_id = P_Price_List_Id
3181 AND ( product_attribute IN ('PRICING_ATTRIBUTE1','PRICING_ATTRIBUTE3')
3182 AND product_attribute_context = 'ITEM'
3183 AND product_attr_value IN (TO_CHAR(P_Inventory_Id),'ALL')
3184 AND ((pricing_attribute_context IS NOT NULL AND pricing_attribute IS NOT NULL )
3185 AND (pricing_attribute_context <> 'VOLUME'
3186 AND pricing_attribute NOT IN ('PRICING_ATTRIBUTE10','PRICING_ATTRIBUTE12'))) )
3187 UNION ALL
3188 SELECT DISTINCT pricing_attribute_context, pricing_attribute
3189 FROM qp_pricing_attributes
3190 WHERE list_header_id IN (
3191 SELECT FL.price_modifier_list_id
3192 FROM
3193 QP_PRICING_ATTRIBUTES A,
3194 QP_LIST_LINES L,
3195 QP_LIST_HEADERS_B QLH,
3196 QP_PRICE_FORMULA_LINES FL
3197 WHERE
3198 A.LIST_HEADER_ID = P_Price_List_Id AND
3199 A.PRODUCT_ATTRIBUTE_CONTEXT = 'ITEM' AND
3200 A.PRODUCT_ATTRIBUTE IN ( 'PRICING_ATTRIBUTE1','PRICING_ATTRIBUTE3' ) AND
3201 A.PRODUCT_ATTR_VALUE IN ( TO_CHAR(P_Inventory_Id),'ALL' ) AND
3202 A.PRICING_PHASE_ID = 1 AND
3203 A.QUALIFICATION_IND IN (4,6,20,22) AND
3204 A.EXCLUDER_FLAG = 'N' AND
3205 FL.PRICE_FORMULA_LINE_TYPE_CODE IN ('PRA','ML') AND
3206 FL.PRICE_FORMULA_ID = L.PRICE_BY_FORMULA_ID AND
3207 L.LIST_LINE_ID = A.LIST_LINE_ID AND
3208 L.LIST_LINE_TYPE_CODE = 'PLL' AND
3209 QLH.LIST_HEADER_ID = L.LIST_HEADER_ID AND
3210 QLH.LIST_TYPE_CODE = 'PRL' AND
3211 NVL(QLH.START_DATE_ACTIVE, SYSDATE) <= SYSDATE AND NVL(QLH.END_DATE_ACTIVE,SYSDATE) >= SYSDATE AND
3212 NVL(L.START_DATE_ACTIVE,SYSDATE) <= SYSDATE AND NVL(L.END_DATE_ACTIVE, SYSDATE) >= SYSDATE
3213 )
3214 UNION ALL
3215 SELECT DISTINCT FL.pricing_attribute_context, FL.pricing_attribute
3216 FROM
3217 QP_PRICING_ATTRIBUTES A,
3218 QP_LIST_LINES L,
3219 QP_LIST_HEADERS_B QLH,
3220 QP_PRICE_FORMULA_LINES FL
3221 WHERE
3222 A.LIST_HEADER_ID = P_Price_List_Id AND
3223 A.PRODUCT_ATTRIBUTE_CONTEXT = 'ITEM' AND
3224 A.PRODUCT_ATTRIBUTE IN ( 'PRICING_ATTRIBUTE1','PRICING_ATTRIBUTE3' ) AND
3225 A.PRODUCT_ATTR_VALUE IN ( TO_CHAR(P_Inventory_Id),'ALL' ) AND
3226 L.LIST_LINE_ID = A.LIST_LINE_ID AND
3227 L.LIST_LINE_TYPE_CODE = 'PLL' AND
3228 FL.PRICE_FORMULA_LINE_TYPE_CODE = 'PRA' AND --IN ('PRA','ML') AND, BUG No: 9155255
3229 FL.PRICE_FORMULA_ID = L.PRICE_BY_FORMULA_ID AND
3230 QLH.LIST_HEADER_ID = L.LIST_HEADER_ID AND
3231 QLH.LIST_TYPE_CODE = 'PRL' AND
3232 NVL(QLH.START_DATE_ACTIVE, SYSDATE) <= SYSDATE AND NVL(QLH.END_DATE_ACTIVE,SYSDATE) >= SYSDATE AND
3233 NVL(L.START_DATE_ACTIVE,SYSDATE) <= SYSDATE AND NVL(L.END_DATE_ACTIVE, SYSDATE) >= SYSDATE
3234 ;
3235
3236
3237 l_api_name CONSTANT VARCHAR2(30) := 'Check_Pricing_Attributes';
3238 l_api_version_number CONSTANT NUMBER := 1.0;
3239 l_count NUMBER;
3240 l_list_line_id NUMBER;
3241 c_check_qpprc_atr_rec c_check_qpprc_atr%ROWTYPE;
3242 l_found VARCHAR2(1);
3243 v_pricing_attr_ctxt VARCHAR2(60);
3244 v_pricing_attr VARCHAR2(60);
3245 l_condition_id VARCHAR2(60);
3246 l_context_name VARCHAR2(60);
3247 l_attr_def_condition_id VARCHAR2(60);
3248
3249
3250 G_USER_ID NUMBER := Fnd_Global.USER_ID;
3251 G_LOGIN_ID NUMBER := Fnd_Global.CONC_LOGIN_ID;
3252
3253 BEGIN
3254
3255
3256 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
3257 X_Check_Return_Status_qp := Fnd_Api.G_FALSE;
3258
3259 OPEN c_check_qpprc_atr;
3260 FETCH c_check_qpprc_atr INTO c_check_qpprc_atr_rec;
3261 IF c_check_qpprc_atr%FOUND THEN
3262 X_Check_Return_Status_qp := Fnd_Api.G_TRUE;
3263 END IF;
3264 CLOSE c_check_qpprc_atr;
3265
3266
3267 IF X_Check_Return_Status_qp = Fnd_Api.G_TRUE THEN
3268 l_found := 0;
3269 OPEN l_pricing_attribs;
3270 LOOP
3271 FETCH l_pricing_attribs INTO v_pricing_attr_ctxt, v_pricing_attr;
3272 EXIT WHEN l_pricing_attribs%NOTFOUND;
3273
3274 BEGIN
3275 SELECT 1
3276 INTO l_found
3277 FROM qp_prc_contexts_b con, qp_segments_b seg, qp_pte_segments pte
3278 WHERE con.prc_context_code = v_pricing_attr_ctxt
3279 AND seg.prc_context_id = con.prc_context_id
3280 AND seg.segment_mapping_column = v_pricing_attr
3281 AND seg.segment_id = pte.segment_id
3282 AND pte.pte_code = 'ORDFUL' -- 4055210
3283 AND ROWNUM < 2; -- 4055210
3284 EXCEPTION
3285 WHEN NO_DATA_FOUND THEN
3286 l_found := 0;
3287 END;
3288
3289 IF l_found = 1 THEN
3290 BEGIN
3291 SELECT 1
3292 INTO l_found
3293 FROM qp_prc_contexts_b con, qp_segments_b seg, qp_attribute_sourcing src
3294 WHERE con.prc_context_code = v_pricing_attr_ctxt
3295 AND seg.prc_context_id = con.prc_context_id
3296 AND seg.segment_mapping_column = v_pricing_attr
3297 AND seg.segment_id = src.segment_id
3298 AND src.request_type_code = 'ASO'-- 4055210
3299 AND ROWNUM < 2; -- 4055210
3300
3301 EXCEPTION
3302 WHEN NO_DATA_FOUND THEN
3303 l_found := 0;
3304 END;
3305
3306 END IF;
3307
3308 END LOOP;
3309 CLOSE l_pricing_attribs;
3310 IF l_found = 1 THEN
3311 X_Check_Return_Status_qp := 'SOURCED';
3312 ELSE
3313 X_Check_Return_Status_qp := 'NOT_SOURCED';
3314 END IF;
3315 ELSE
3316 X_Check_Return_Status_qp := 'NO_ATTRIBUTES';
3317 END IF;-- IF X_Check_Return_Status_qp = FND_API.G_TRUE
3318
3319
3320 EXCEPTION
3321 WHEN OTHERS THEN
3322 Oe_Debug_Pub.ADD('Error in Get_Pricing_attributes');
3323
3324 END Check_Pricing_Attributes;
3325
3326
3327 /*--bug 3228829
3328 OM needs API to update the lines_tmp table
3329 this API will take care of updating i/f tables java engine is installed
3330 and update temp tables when plsql engine is installed*/
3331 PROCEDURE Update_Lines(p_update_type IN VARCHAR2, p_line_id IN NUMBER,
3332 p_line_index IN NUMBER, p_priced_quantity IN NUMBER) IS
3333 BEGIN
3334 l_debug := Qp_Preq_Grp.G_DEBUG_ENGINE;
3335 IF Qp_Java_Engine_Util_Pub.Java_Engine_Running = 'N' THEN
3336 IF l_debug = Fnd_Api.G_TRUE THEN
3337 Qp_Preq_Grp.engine_debug('Java engine not installed '
3338 ||' line_id '||p_line_id||' line_index '||p_line_index
3339 ||' p_priced_quantity '||p_priced_quantity||' p_update_type '||p_update_type);
3340 END IF;
3341 IF p_update_type = 'UPDATE_LINE_ID' THEN
3342 IF p_line_index IS NOT NULL THEN
3343 UPDATE qp_npreq_lines_tmp SET line_id = p_line_id
3344 WHERE line_index = p_line_index;
3345 END IF;--p_line_index IS NOT NULL
3346 ELSIF p_update_type = 'UPDATE_PRICED_QUANTITY' THEN
3347 IF p_line_id IS NOT NULL THEN
3348 UPDATE qp_npreq_lines_tmp SET priced_quantity = p_priced_quantity
3349 WHERE line_id = p_line_id;
3350 END IF;--p_line_id IS NOT NULL
3351 ELSIF p_update_type = 'MAKE_STATUS_INVALID' THEN
3352 IF p_line_id IS NOT NULL THEN
3353 UPDATE qp_npreq_lines_tmp SET process_status = 'NOT_VALID'
3354 WHERE line_id = p_line_id;
3355 END IF;--p_line_id IS NOT NULL
3356 END IF;
3357 ELSE
3358 IF l_debug = Fnd_Api.G_TRUE THEN
3359 Qp_Preq_Grp.engine_debug('Java engine installed '
3360 ||' line_id '||p_line_id||' line_index '||p_line_index
3361 ||' p_priced_quantity '||p_priced_quantity||' p_update_type '||p_update_type);
3362 END IF;
3363 IF p_update_type = 'UPDATE_LINE_ID' THEN
3364 IF p_line_index IS NOT NULL THEN
3365 UPDATE qp_int_lines SET line_id = p_line_id
3366 WHERE line_index = p_line_index;
3367 END IF;--p_line_index IS NOT NULL
3368 ELSIF p_update_type = 'UPDATE_PRICED_QUANTITY' THEN
3369 IF p_line_id IS NOT NULL THEN
3370 UPDATE qp_int_lines SET priced_quantity = p_priced_quantity
3371 WHERE line_id = p_line_id;
3372 END IF;--p_line_id IS NOT NULL
3373 ELSIF p_update_type = 'MAKE_STATUS_INVALID' THEN
3374 IF p_line_id IS NOT NULL THEN
3375 UPDATE qp_int_lines SET process_status = 'NOT_VALID'
3376 WHERE line_id = p_line_id;
3377 END IF;--p_line_id IS NOT NULL
3378 END IF;
3379 END IF;--java engine installed
3380
3381 END Update_Lines;
3382
3383 PROCEDURE Flex_Enabled_Status (p_flexfields_name IN VARCHAR2, x_status OUT NOCOPY VARCHAR2)
3384 IS
3385 BEGIN
3386 IF (FND_FLEX_APIS.is_descr_setup(661, p_flexfields_name)) THEN
3387 x_status:= 'Y';
3388 ELSE
3389 x_status:='N';
3390 END IF;
3391 END Flex_Enabled_Status;
3392
3393 END Qp_Util_Pub;