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