[Home] [Help]
PACKAGE BODY: APPS.OKC_OC_INT_PRICING_PVT
Source
1 Package body OKC_OC_INT_PRICING_PVT AS
2 /* $Header: OKCRPRIB.pls 120.0 2005/05/25 18:52:49 appldev noship $ */
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5 /**************************************************************
6 Processing:
7 START OF PROCESSING FOR QTK or OTK pricing information creation
8
9
10 For order to contract:
11 ...
12 Details for order to contract as follows:
13
14 For quote to contract:
15 OKC_OC_INT_QTK_PVT.create_k_from_quote calls this package through
16 a single call to OKC_OC_INT_PRICING_PVT.create_k_pricing passing
17 as parameters the contract header id, quote header id and
18 PL/SQL table p_rel_tab which has contract lines along
19 with related order lines.
20
21 Details for quote to contract as follows:
22
23 --<<GET PRICE ATTRIBUTE INFORMATION>>
24 --call get_price_attr() with header level parameters
25 --this does the following:-
26 --get price attribute information from ASO or ONT at the HEADER level
27 --and store it in global PL/SQL table g_pavv_tab
28
29 --call get_price_attr () with line level parameters this time
30 --this does the following:-
31 --get price attribute information from ASO or ONT at the LINE level
32 --and append it to global PL/SQL table g_pavv_tab which will then
33 --contain BOTH header AND line level information
34
35 --call OKC_PRICE_ADJUSTMENT_PUB.create_price_att_value()
36 --this puts price attributes information in OKC
37 --<<END OF GETTING PRICE ATTRIBUTE INFORMATION>>
38
39
40
41 --<<GET PRICE ADJUSTMENT INFORMATION>>
42 --call get_price_adj() with header level parameters
43 --this does the following:-
44 --get price adjustment information from ASO or ONT at the HEADER level
45 --and store it in global PL/SQL table g_patv_tab
46
47 --call get_price_adj() with line level parameters this time
48 --get price adjustment information from ASO or ONT at the LINE level
49 --and append it to global PL/SQL table g_patv_tab which will then
50 --contain BOTH header AND line level information
51
52 --call OKC_PRICE_ADJUSTMENT_PUB.create_price_adjustment()
53 --this puts price adjustment information in OKC
54
55 --store the old (ASO or ONT) pat_id's along with the new (OKC) pat_id's
56 --in global PL/SQL table g_price_adjustments_tab for reference later
57 --<<END OF GETTING PRICE ADJUSTMENT INFORMATION>>
58
59
60
61 --<<GET PRICE ADJUSTMENT ATTRIBUTES INFORMATION>>
62 --call get_price_adj_attr ()
63 --this does the following:
64 --i) loop through each ASO or ONT pat_id in global PL/SQL table
65 -- g_price_adjustments_tab,
66 --ii) get all the price adjustment attributes
67 -- and store them in global PL/SQL table g_paav_tab
68
69 --call OKC_PRICE_ADJUSTMENT_PUB.create_price_adj_attrib()
70 --this puts price adjustment attribute information in OKC
71 --<<END OF GETTING PRICE ADJUSTMENT ATTRIBUTES INFORMATION>>
72
73
74
75 --<<GET PRICE ADJUSTMENT RELATIONSHIP INFORMATION>>
76 --call get_price_adj_rltship ()
77 --this does the following:
78 --i) loop through each ASO or ONT pat_id in global PL/SQL table
79 -- g_price_adjustments_tab,
80 --ii) get all the price adjustment relationships
81 -- and store them in global PL/SQL table g_pacv_tab
82
83 --call OKC_PRICE_ADJUSTMENT_PUB.create_price_adj_assoc
84 --this puts price adjustment relationship information in OKC
85 --<<END OF GETTING PRICE ADJUSTMENT RELATIONSHIP INFORMATION>>
86
87 The OKX related quote pricing views concerned are:
88 okx_qte_prc_atrbs_v
89 okx_qte_prc_adjmnts_v
90 okx_qte_prc_adj_atrbs_v
91 okx_qte_prc_adj_rlshp_v
92
93 Flow:
94 |---OKC_OC_INT_PRICING.create_k_pricing()
95 | |---get_price_attr() called twice at header level AND line level
96 | | |---get_pavv_tab() at header level OR line level (as called)
97 | |---OKC_PRICE_ADJUSTMENT_PUB.create_price_att_value
98 | |---get_price_adj() called twice at header level AND line level
99 | | |---get_patv_tab() at header level OR line level (as called)
100 | |---OKC_PRICE_ADJUSTMENT_PUB.create_price_adjustment
101 | |---get_price_adj_attr()
102 | | |---get_paav_tab()
103 | |---OKC_PRICE_ADJUSTMENT_PUB.create_price_adj_attrib
104 | |---get_price_adj_rltship()
105 | | |---get_pacv_tab()
106 | |---OKC_PRICE_ADJUSTMENT_PUB.create_price_adj_assoc
107
108 END OF PROCESSING FOR QTK or OTK pricing information creation
109 **************************************************************/
110
111 TYPE price_adjustments_rec_type IS RECORD (
112 old_pat_id NUMBER := OKC_API.G_MISS_NUM,
113 new_pat_id NUMBER := OKC_API.G_MISS_NUM);
114 TYPE price_adjustments_tbl_type IS TABLE OF price_adjustments_rec_type
115 INDEX BY BINARY_INTEGER;
116 g_price_adjustments_tab price_adjustments_tbl_type;
117 -- used to store the old (ASO or ONT) pat_id's along with
118 -- the new (OKC) pat_id's
119
120 --price attributes
121 g_pavv_tab OKC_PRICE_ADJUSTMENT_PUB.pavv_tbl_type;
122 lx_pavv_tab OKC_PRICE_ADJUSTMENT_PUB.pavv_tbl_type;
123
124 --price adjustments
125 g_patv_tab OKC_PRICE_ADJUSTMENT_PUB.patv_tbl_type;
126 lx_patv_tab OKC_PRICE_ADJUSTMENT_PUB.patv_tbl_type;
127
128 --price adjustment attributes
129 g_paav_tab OKC_PRICE_ADJUSTMENT_PUB.paav_tbl_type;
130 lx_paav_tab OKC_PRICE_ADJUSTMENT_PUB.paav_tbl_type;
131
132 --price adjustment relationships
133 g_pacv_tab OKC_PRICE_ADJUSTMENT_PUB.pacv_tbl_type;
134 lx_pacv_tab OKC_PRICE_ADJUSTMENT_PUB.pacv_tbl_type;
135
136
137 -- cursor to retrieve the contract number against a given contract id
138 -- this cursor is used to include the contract number in error messages
139 CURSOR c_knumber (c_k_id NUMBER) IS
140 SELECT contract_number
141 FROM okc_k_headers_b
142 WHERE id = c_k_id;
143
144 -- cursor to retrieve the order number against a given order id
145 -- this cursor is used to include the order number in error messages
146 CURSOR c_onumber (c_o_id NUMBER) IS
147 SELECT TO_CHAR(order_number)
148 FROM okx_order_headers_v
149 WHERE id1 = c_o_id;
150
151 -- cursor to retrieve the quote number against a given quote id
152 -- this cursor is used to include the quote number in error messages
153 CURSOR c_qnumber (c_q_id NUMBER) IS
154 SELECT TO_CHAR(quote_number)
155 FROM okx_quote_headers_v
156 WHERE id1 = c_q_id;
157
158 -- used for including in error messages
159 l_order_number VARCHAR2(120) := OKC_API.G_MISS_CHAR;
160 l_quote_number VARCHAR2(120) := OKC_API.G_MISS_CHAR;
161
162
163 -----------------------------------------------------------------------------
164 -- Procedure: print_error
165 -- Returns:
166 -- Purpose: Print the last error which occured
167 -- In Parameters: pos position on the line to print the message
168 -- Out Parameters:
169
170 PROCEDURE print_error(pos IN NUMBER) IS
171 x_msg_count NUMBER;
172 x_msg_data VARCHAR2(1000);
173 BEGIN
174 IF okc_util.l_trace_flag OR okc_util.l_log_flag THEN
175 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
176 p_data => x_msg_data
177 );
178 IF (l_debug = 'Y') THEN
179 okc_util.print_trace(pos, '==EXCEPTION=================');
180 END IF;
181 x_msg_data := fnd_msg_pub.get( p_msg_index => x_msg_count,
182 p_encoded => 'F'
183 );
184 IF (l_debug = 'Y') THEN
185 okc_util.print_trace(pos, 'Message : '||x_msg_data);
186 okc_util.print_trace(pos, '============================');
187 END IF;
188 END IF;
189 END print_error;
190
191
192
193 ----------------------------------------------------------------------------
194 -- Procedure: Cleanup()
195 -- Returns:
196 -- Purpose: Delete pl/sql tables, so that they are not reused
197 -- when a connection is used by another client
198 -- In Parameters: No Parameters
199 -- Out Parameters: x_return_status Varchar2
200 -----------------------------------------------------------------------------
201 PROCEDURE cleanup(x_return_status OUT NOCOPY varchar2 ) IS
202 BEGIN
203 IF (l_debug = 'Y') THEN
204 okc_util.print_trace(1, '>START - OKC_OC_INT_PRICING_PVT.CLEANUP - Initialize global PL/SQL Tables');
205 okc_util.print_trace(1, ' ');
206 END IF;
207
208 x_return_status := OKC_API.G_RET_STS_SUCCESS;
209
210 IF (l_debug = 'Y') THEN
211 okc_util.print_trace(2, 'Cleaning up plsql tables');
212 END IF;
213
214 g_price_adjustments_tab.DELETE;
215
216 --price attributes
217 g_pavv_tab.DELETE;
218 lx_pavv_tab.DELETE;
219
220 --price adjustments
221 g_patv_tab.DELETE;
222 lx_patv_tab.DELETE;
223
224 --price adjustment attributes
225 g_paav_tab.DELETE;
226 lx_paav_tab.DELETE;
227
228 --price adjustment relationships
229 g_pacv_tab.DELETE;
230 lx_pacv_tab.DELETE;
231
232 IF (l_debug = 'Y') THEN
233 okc_util.print_trace(2, 'Done Cleaning up');
234 okc_util.print_trace(1, '<END - OKC_OC_INT_PRICING_PVT.CLEANUP - Initialize global PL/SQL Tables');
235 END IF;
236 EXCEPTION
237 WHEN OTHERS THEN
238 OKC_API.set_message(G_APP_NAME,
239 G_UNEXPECTED_ERROR,
240 G_SQLCODE_TOKEN,
241 SQLCODE,
242 G_SQLERRM_TOKEN,
243 SQLERRM);
244 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
245 END cleanup;
246
247
248 /************************************************************************
249 ************************************************************************
250 START OF QTK or OTK PRICING INFORMATION CREATION
251 ************************************************************************
252 ***********************************************************************/
253 ---------------------------------------------------------------------------
254 --Procedure to contract pricing information at the header and line levels
255 ----------------------------------------------------------------------------
256 -- Bug : 1686001 Changed references to ASO_QUOTE_HEADERS_ALL.QUOTE_HEADER_ID to OKX_QUOTE_HEADERS_V.ID1
257 PROCEDURE create_k_pricing (
258 x_return_status OUT NOCOPY VARCHAR2,
259
260 p_chr_id IN OKC_K_HEADERS_B.ID%TYPE,
261 p_o_flag IN VARCHAR2 ,
262 p_ohr_id IN NUMBER ,
263 p_q_flag IN VARCHAR2 ,
264 p_qhr_id IN OKX_QUOTE_HEADERS_V.ID1%TYPE
265 ,
266 p_line_inf_tab IN OKC_OC_INT_CONFIG_PVT.line_inf_tbl_type
267 -- this PL/SQL table has quote (or order) lines against contract lines
268
269
270 ) IS
271
272
273 i BINARY_INTEGER := 0;
274
275 l_api_version NUMBER := 1;
276 lx_msg_count NUMBER;
277 lx_msg_data VARCHAR2(2000);
278
279 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
280
281 BEGIN
282 x_return_status := l_return_status;
283
284
285 IF (l_debug = 'Y') THEN
286 OKC_UTIL.print_trace(1, ' ');
287 OKC_UTIL.print_trace(1, '================================================');
288 OKC_UTIL.print_trace(1, 'INITIALIZE GLOBAL PLSQL TABLES ');
289 OKC_UTIL.print_trace(1, '================================================');
290 OKC_UTIL.print_trace(1, ' ');
291 OKC_UTIL.print_trace(1, ' ');
292 END IF;
293
294 cleanup(x_return_status => l_return_status);
295 IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
296 RAISE G_EXCEPTION_HALT_VALIDATION;
297 ELSE
298 x_return_status := l_return_status;
299 END IF;
300
301
302 IF (l_debug = 'Y') THEN
303 OKC_UTIL.print_trace(1, ' ');
304 OKC_UTIL.print_trace(1, 'Create Contract Pricing');
305 OKC_UTIL.print_trace(1, '-----------------------------------------------');
306 OKC_UTIL.print_trace(1, '>START - ******* OKC_OC_INT_PRICING_PVT.create_k_pricing -');
307 END IF;
308
309
310 --<<begin getting price attribute information>>
311
312 --get price attribute information from ASO or ONT at the HEADER level
313 --and store it in global PL/SQL table g_pavv_tab
314 IF (l_debug = 'Y') THEN
315 OKC_UTIL.print_trace(2, 'Calling get_price_attr at HEADER level-');
316 OKC_UTIL.print_trace(2, 'Contract Id- '|| p_chr_id);
317 OKC_UTIL.print_trace(2, 'Quote Id - '|| p_qhr_id);
318 OKC_UTIL.print_trace(2, 'Order Id - '|| p_ohr_id);
319 END IF;
320 get_price_attr (
321 p_chr_id => p_chr_id,
322 p_q_flag => p_q_flag,
323 p_qhr_id => p_qhr_id,
324 p_o_flag => p_o_flag,
325 p_ohr_id => p_ohr_id
326 );
327 IF (l_debug = 'Y') THEN
328 OKC_UTIL.print_trace(2, 'Header level call to get_price_attr finished successfully');
329 OKC_UTIL.print_trace(2, 'Output in global PL/SQL table g_pavv_tab');
330 END IF;
331
332
333 --get price attribute information from ASO or ONT at the LINE level
334 --and append it to global PL/SQL table g_pavv_tab which will then
335 --contain BOTH header AND line level information
336 IF (l_debug = 'Y') THEN
337 OKC_UTIL.print_trace(2, 'Calling get_price_attr at LINE level-');
338 OKC_UTIL.print_trace(2, 'Contract Id- '|| p_chr_id);
339 OKC_UTIL.print_trace(2, 'Quote Id - '|| p_qhr_id);
340 OKC_UTIL.print_trace(2, 'Order Id - '|| p_ohr_id);
341 OKC_UTIL.print_trace(2, 'PL/SQL table p_line_inf_tab- related quote or orderlines and contract lines');
342 END IF;
343 get_price_attr (
344 p_chr_id => p_chr_id,
345 p_q_flag => p_q_flag,
346 p_qhr_id => p_qhr_id,
347 p_o_flag => p_o_flag,
348 p_ohr_id => p_ohr_id,
349 p_line_inf_tab => p_line_inf_tab
350 );
351 IF (l_debug = 'Y') THEN
352 OKC_UTIL.print_trace(2, 'Line level call to get_price_attr finished successfully');
353 OKC_UTIL.print_trace(2, 'Output in global PL/SQL table g_pavv_tab');
354 END IF;
355
356 --now put this price attributes information in OKC
357 IF (l_debug = 'Y') THEN
358 OKC_UTIL.print_trace(2, ' >Calling OKC_PRICE_ADJUSTMENT_PUB.create_price_att_value');
359 OKC_UTIL.print_trace(2, 'input p_pavv_tbl => g_pavv_tab');
360 END IF;
361 IF g_pavv_tab.FIRST IS NOT NULL THEN
362 OKC_PRICE_ADJUSTMENT_PUB.create_price_att_value(
363 p_api_version => l_api_version,
364 p_init_msg_list => OKC_API.G_FALSE,
365 x_return_status => l_return_status,
366 x_msg_count => lx_msg_count,
367 x_msg_data => lx_msg_data,
368 ----p_pavv_rec => l_pavv_rec,
369 ----x_pavv_rec => x_pavv_rec);
370 p_pavv_tbl => g_pavv_tab, --IN: ASO or ONT
371 x_pavv_tbl => lx_pavv_tab); --OUT: OKC
372
373 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
374 -- get quote or order number to display in error message
375 IF p_qhr_id IS NOT NULL THEN
376 BEGIN
377 OPEN c_qnumber(p_qhr_id);
378 FETCH c_qnumber INTO l_quote_number;
379 CLOSE c_qnumber;
380 EXCEPTION
381 WHEN OTHERS THEN
382 NULL;
383 END;
384 ELSIF p_ohr_id IS NOT NULL THEN
385 BEGIN
386 OPEN c_onumber(p_ohr_id);
387 FETCH c_onumber INTO l_order_number;
388 CLOSE c_onumber;
389 EXCEPTION
390 WHEN OTHERS THEN
391 NULL;
392 END;
393 END IF;
394
395 -- put error message on stack
396 --Price Attributes information from ASO or ONT table was not
397 -- created in OKC.
398 IF p_q_flag = OKC_API.G_TRUE THEN
399 OKC_API.set_message(p_app_name => G_APP_NAME,
400 p_msg_name => 'OKC_Q2K_NOPRIATTR',
401 p_token1 => 'QNUMBER',
402 p_token1_value => l_quote_number);
403 ELSIF p_o_flag = OKC_API.G_TRUE THEN
404 OKC_API.set_message(p_app_name => G_APP_NAME,
405 p_msg_name => 'OKC_Q2K_NOPRIATTR_ORD',
406 p_token1 => 'ONUMBER',
407 p_token1_value => l_order_number);
408 END IF;
409 print_error(2);
410
411 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
412 ----x_return_status := l_return_status;
413 ----RAISE G_EXCEPTION_HALT_VALIDATION;
414 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
415 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
416 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
417 ELSE
418 x_return_status := l_return_status;
419 END IF;
420 END IF;
421 END IF;
422
423 IF (l_debug = 'Y') THEN
424 OKC_UTIL.print_trace(2, ' >Call to OKC_PRICE_ADJUSTMENT_PUB.create_price_att_value finished successfully');
425 END IF;
426
427 --<<end of getting price attribute information>>
428 -----------------------------------------------
429
430
431 --<<begin getting price adjustment information>>
432
433 --get price adjustment information from ASO or ONT at the HEADER level
434 --and store it in global PL/SQL table g_patv_tab
435 IF (l_debug = 'Y') THEN
436 OKC_UTIL.print_trace(2, 'Calling get_price_adj at HEADER level-');
437 OKC_UTIL.print_trace(2, 'Contract Id- '|| p_chr_id);
438 OKC_UTIL.print_trace(2, 'Quote Id - '|| p_qhr_id);
439 OKC_UTIL.print_trace(2, 'Order Id - '|| p_ohr_id);
440 END IF;
441 get_price_adj (
442 p_chr_id => p_chr_id,
443 p_q_flag => p_q_flag,
444 p_qhr_id => p_qhr_id,
445 p_o_flag => p_o_flag,
446 p_ohr_id => p_ohr_id
447 );
448
449 IF (l_debug = 'Y') THEN
450 OKC_UTIL.print_trace(2, 'Header level call to get_price_adj finished successfully');
451 OKC_UTIL.print_trace(2, 'Output is global PL/SQL table g_patv_tab');
452 END IF;
453
454
455 --get price adjustment information from ASO or ONT at the LINE level
456 --and append it to global PL/SQL table g_patv_tab which will then
457 --contain BOTH header AND line level information
458 IF (l_debug = 'Y') THEN
459 OKC_UTIL.print_trace(2, 'Calling get_price_adj at LINE level-');
460 OKC_UTIL.print_trace(2, 'Contract Id- '|| p_chr_id);
461 OKC_UTIL.print_trace(2, 'Quote Id - '|| p_qhr_id);
462 OKC_UTIL.print_trace(2, 'Order Id - '|| p_ohr_id);
463 OKC_UTIL.print_trace(2, 'PL/SQL table p_line_inf_tab- related quote or orderlines and contract lines');
464 END IF;
465 get_price_adj (
466 p_chr_id => p_chr_id,
467 p_q_flag => p_q_flag,
468 p_qhr_id => p_qhr_id,
469 p_o_flag => p_o_flag,
470 p_ohr_id => p_ohr_id,
471 p_line_inf_tab => p_line_inf_tab
472 );
473
474 IF (l_debug = 'Y') THEN
475 OKC_UTIL.print_trace(2, 'Line level call to get_price_adj finished successfully');
476 OKC_UTIL.print_trace(2, 'Output is global PL/SQL table g_patv_tab');
477 END IF;
478
479
480 -- now put this price adjustment information in OKC
481 IF (l_debug = 'Y') THEN
482 OKC_UTIL.print_trace(2, ' >Calling OKC_PRICE_ADJUSTMENT_PUB.create_price_adjustment');
483 OKC_UTIL.print_trace(2, 'input p_patv_tbl => g_patv_tab');
484 END IF;
485 IF g_patv_tab.FIRST IS NOT NULL THEN
486 OKC_PRICE_ADJUSTMENT_PUB.create_price_adjustment(
487 p_api_version => l_api_version,
488 p_init_msg_list => OKC_API.G_FALSE,
489 x_return_status => l_return_status,
490 x_msg_count => lx_msg_count,
491 x_msg_data => lx_msg_data,
492 ----p_patv_rec => l_patv_rec,
493 ----x_patv_rec => x_patv_rec);
494 p_patv_tbl => g_patv_tab, --IN: ASO or ONT
495 x_patv_tbl => lx_patv_tab); --OUT: OKC
496
497 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
498 -- get quote or order number to display in error message
499 IF p_qhr_id IS NOT NULL THEN
500 BEGIN
501 OPEN c_qnumber(p_qhr_id);
502 FETCH c_qnumber INTO l_quote_number;
503 CLOSE c_qnumber;
504 EXCEPTION
505 WHEN OTHERS THEN
506 NULL;
507 END;
508 ELSIF p_ohr_id IS NOT NULL THEN
509 BEGIN
510 OPEN c_onumber(p_ohr_id);
511 FETCH c_onumber INTO l_order_number;
512 CLOSE c_onumber;
513 EXCEPTION
514 WHEN OTHERS THEN
515 NULL;
516 END;
517 END IF;
518
519 -- put error message on stack
520 --Price Adjustments information from ASO or ONT table was not
521 -- created in OKC.
522 IF p_q_flag = OKC_API.G_TRUE THEN
523 OKC_API.set_message(p_app_name => G_APP_NAME,
524 p_msg_name => 'OKC_Q2K_NOPRIADJ',
525 p_token1 => 'QNUMBER',
526 p_token1_value => l_quote_number);
527 ELSIF p_o_flag = OKC_API.G_TRUE THEN
528 OKC_API.set_message(p_app_name => G_APP_NAME,
529 p_msg_name => 'OKC_Q2K_NOPRIADJ_ORD',
530 p_token1 => 'ONUMBER',
531 p_token1_value => l_order_number);
532 END IF;
533 print_error(2);
534
535
536 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
537 ----x_return_status := l_return_status;
538 ----RAISE G_EXCEPTION_HALT_VALIDATION;
539 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
540 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
541 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
542 ELSE
543 x_return_status := l_return_status;
544 END IF;
545 END IF;
546
547 IF (l_debug = 'Y') THEN
548 OKC_UTIL.print_trace(2, ' >Call to OKC_PRICE_ADJUSTMENT_PUB.create_price_adjustment finished successfully.');
549 END IF;
550
551 -- store the old (ASO or ONT) pat_id's along with the new (OKC) pat_id's
552 -- in global PL/SQL table g_price_adjustments_tab for reference later
553 IF (g_patv_tab.FIRST IS NOT NULL AND lx_patv_tab.FIRST IS NOT NULL) THEN
554 i := g_patv_tab.FIRST;
555 WHILE i IS NOT NULL LOOP
556 g_price_adjustments_tab(i).old_pat_id := g_patv_tab(i).id;
557 g_price_adjustments_tab(i).new_pat_id := lx_patv_tab(i).id;
558 i := g_patv_tab.NEXT(i); -- both have the same index
559 END LOOP;
560 END IF;
561 END IF;
562
563 --<<end of getting price adjustment information>>
564
565
566 --<<begin getting price adjustment attributes information>>
567
568 --i) loop through each ASO or ONT pat_id in global PL/SQL table
569 -- g_price_adjustments_tab,
570 --ii) get all the price adjustment attributes
571 -- and store them in global PL/SQL table g_paav_tab
572 IF (l_debug = 'Y') THEN
573 OKC_UTIL.print_trace(2, 'Calling get_price_adj_attr-');
574 OKC_UTIL.print_trace(2, 'Quote Id - '|| p_qhr_id);
575 OKC_UTIL.print_trace(2, 'Order Id - '|| p_ohr_id);
576 END IF;
577 get_price_adj_attr (
578 p_q_flag => p_q_flag,
579 p_qhr_id => p_qhr_id,
580 p_o_flag => p_o_flag,
581 p_ohr_id => p_ohr_id
582 );
583
584 IF (l_debug = 'Y') THEN
585 OKC_UTIL.print_trace(2, 'Call to get_price_adj_attr finished successfully');
586 END IF;
587
588
589 --now put this price adjustment attribute information in OKC
590 --by calling OKC_PRICE_ADJUSTMENT_PUB.create_price_adj_attrib
591 IF (l_debug = 'Y') THEN
592 OKC_UTIL.print_trace(2, ' >Calling OKC_PRICE_ADJUSTMENT_PUB.create_price_adj_attrib');
593 OKC_UTIL.print_trace(2, 'input p_paav_tbl => g_paav_tab');
594 END IF;
595
596 IF g_paav_tab.FIRST IS NOT NULL THEN
597 --now put the price adjustment attributes in OKC
598 OKC_PRICE_ADJUSTMENT_PUB.create_price_adj_attrib(
599 p_api_version => l_api_version,
600 p_init_msg_list => OKC_API.G_FALSE,
601 x_return_status => l_return_status,
602 x_msg_count => lx_msg_count,
603 x_msg_data => lx_msg_data,
604 ----p_paav_rec => l_paav_rec,
605 ----x_paav_rec => x_paav_rec
606 p_paav_tbl => g_paav_tab,
607 x_paav_tbl => lx_paav_tab);
608 END IF;
609
610 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
611 -- get quote or order number to display in error message
612 IF p_qhr_id IS NOT NULL THEN
613 BEGIN
614 OPEN c_qnumber(p_qhr_id);
615 FETCH c_qnumber INTO l_quote_number;
616 CLOSE c_qnumber;
617 EXCEPTION
618 WHEN OTHERS THEN
619 NULL;
620 END;
621 ELSIF p_ohr_id IS NOT NULL THEN
622 BEGIN
623 OPEN c_onumber(p_ohr_id);
624 FETCH c_onumber INTO l_order_number;
625 CLOSE c_onumber;
626 EXCEPTION
627 WHEN OTHERS THEN
628 NULL;
629 END;
630 END IF;
631
632 --put error message on stack
633 --Price Adjustments Attributes information from ASO or ONT table
634 --was not created in OKC.
635 IF p_q_flag = OKC_API.G_TRUE THEN
636 OKC_API.set_message(p_app_name => G_APP_NAME,
637 p_msg_name => 'OKC_Q2K_NOPRIADJATTR',
638 p_token1 => 'QNUMBER',
639 p_token1_value => l_quote_number);
640 ELSIF p_o_flag = OKC_API.G_TRUE THEN
641 OKC_API.set_message(p_app_name => G_APP_NAME,
642 p_msg_name => 'OKC_Q2K_NOPRIADJATTR_ORD',
643 p_token1 => 'ONUMBER',
644 p_token1_value => l_order_number);
645 END IF;
646 print_error(2);
647
648 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
649 ----x_return_status := l_return_status;
650 ----RAISE G_EXCEPTION_HALT_VALIDATION;
651 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
652 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
653 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
654 ELSE
655 x_return_status := l_return_status;
656 END IF;
657 END IF;
658
659 IF (l_debug = 'Y') THEN
660 OKC_UTIL.print_trace(2, ' >Call to OKC_PRICE_ADJUSTMENT_PUB.create_price_adj_attrib finished successfully');
661 END IF;
662
663
664 --<<end of getting price adjustment attributes information>>
665
666
667 --<<begin getting price adjustment relationship information>>
668
669 --i) loop through each ASO or ONT pat_id in global PL/SQL table
670 -- g_price_adjustments_tab,
671 --ii) get all the price adjustment relationships
672 -- and store them in global PL/SQL table g_pacv_tab
673 IF (l_debug = 'Y') THEN
674 OKC_UTIL.print_trace(2, 'Calling get_price_adj_rltship- ');
675 OKC_UTIL.print_trace(2, 'Quote Id - '|| p_qhr_id);
676 OKC_UTIL.print_trace(2, 'Order Id - '|| p_ohr_id);
677 END IF;
678 get_price_adj_rltship (
679 p_q_flag => p_q_flag,
680 p_qhr_id => p_qhr_id,
681 p_o_flag => p_o_flag,
682 p_ohr_id => p_ohr_id,
683 p_line_inf_tab => p_line_inf_tab
684 );
685
686 IF (l_debug = 'Y') THEN
687 OKC_UTIL.print_trace(2, 'Call to get_price_adj_rltship finished successfully');
688 END IF;
689
690 --now put this price adjustment relationship information in OKC
691 --by calling OKC_PRICE_ADJUSTMENT_PUB.create_price_adj_assoc
692 IF (l_debug = 'Y') THEN
693 OKC_UTIL.print_trace(2, ' >Calling OKC_PRICE_ADJUSTMENT_PUB.create_price_adj_assoc');
694 OKC_UTIL.print_trace(2, 'input p_pacv_tbl => g_pacv_tab');
695 END IF;
696
697 IF g_pacv_tab.FIRST IS NOT NULL THEN
698 --now put the price adjustment relationships in OKC
699 OKC_PRICE_ADJUSTMENT_PUB.create_price_adj_assoc(
700 p_api_version => l_api_version,
701 p_init_msg_list => OKC_API.G_FALSE,
702 x_return_status => l_return_status,
703 x_msg_count => lx_msg_count,
704 x_msg_data => lx_msg_data,
705 p_pacv_tbl => g_pacv_tab,
706 x_pacv_tbl => lx_pacv_tab);
707 END IF;
708
709 IF (l_debug = 'Y') THEN
710 OKC_UTIL.print_trace(2, ' >Call to OKC_PRICE_ADJUSTMENT_PUB.create_price_adj_assoc finished successfully');
711 END IF;
712
713
714 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
715 -- get quote or order number to display in error message
716 IF p_qhr_id IS NOT NULL THEN
717 BEGIN
718 OPEN c_qnumber(p_qhr_id);
719 FETCH c_qnumber INTO l_quote_number;
720 CLOSE c_qnumber;
721 EXCEPTION
722 WHEN OTHERS THEN
723 NULL;
724 END;
725 ELSIF p_ohr_id IS NOT NULL THEN
726 BEGIN
727 OPEN c_onumber(p_ohr_id);
728 FETCH c_onumber INTO l_order_number;
729 CLOSE c_onumber;
730 EXCEPTION
731 WHEN OTHERS THEN
732 NULL;
733 END;
734 END IF;
735
736 --put error message on stack
737 --Price Adjustment Relationship information from ASO or ONT table
738 --was not created in OKC.
739 IF p_q_flag = OKC_API.G_TRUE THEN
740 OKC_API.set_message(p_app_name => G_APP_NAME,
741 p_msg_name => 'OKC_Q2K_NOPRIADJREL',
742 p_token1 => 'QNUMBER',
743 p_token1_value => l_quote_number);
744 ELSIF p_o_flag = OKC_API.G_TRUE THEN
745 OKC_API.set_message(p_app_name => G_APP_NAME,
746 p_msg_name => 'OKC_Q2K_NOPRIADJREL_ORD',
747 p_token1 => 'ONUMBER',
748 p_token1_value => l_order_number);
749 END IF;
750 print_error(2);
751
752 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
753 ----x_return_status := l_return_status;
754 ----RAISE G_EXCEPTION_HALT_VALIDATION;
755 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
756 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
757 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
758 ELSE
759 x_return_status := l_return_status;
760 END IF;
761 END IF;
762
763 IF (l_debug = 'Y') THEN
764 OKC_UTIL.print_trace(2, ' >Call to OKC_PRICE_ADJUSTMENT_PUB.create_price_adj_assoc finished successfully');
765 END IF;
766
767 --<<end of getting price adjustment relationship information>>
768
769
770 IF (l_debug = 'Y') THEN
771 OKC_UTIL.print_trace(1, '>END - ******* OKC_OC_INT_PRICING_PVT.create_k_pricing -');
772 END IF;
773
774
775 EXCEPTION
776 WHEN OKC_API.G_EXCEPTION_ERROR THEN
777 -- store SQL error message on message stack for caller
778 OKC_API.set_message(G_APP_NAME, G_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
779 print_error(1);
780
781 -- notify caller of an error
782 x_return_status := OKC_API.G_RET_STS_ERROR;
783
784
785 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
786 -- store SQL error message on message stack for caller
787 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
788 print_error(1);
789
790 -- notify caller of an UNEXPECTED error
791 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
792
793
794 WHEN OTHERS THEN
795 -- store SQL error message on message stack for caller
796 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
797 print_error(1);
798
799 -- notify caller of an UNEXPECTED error
800 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
801
802 END create_k_pricing;
803
804
805
806 ----------------------------------------------------------------------------
807 -- Procedure creates price attribute information in OKC from
808 -- ASO or ONT pricing
809 ----------------------------------------------------------------------------
810 -- Bug : 1686001 Changed references to ASO_QUOTE_HEADERS_ALL.QUOTE_HEADER_ID to OKX_QUOTE_HEADERS_V.ID1
811 PROCEDURE get_price_attr(
812 p_chr_id IN NUMBER,
813 p_q_flag IN VARCHAR2 ,
814 p_qhr_id IN OKX_QUOTE_HEADERS_V.ID1%TYPE
815 ,
816 p_o_flag IN VARCHAR2 ,
817 p_ohr_id IN NUMBER,
818 p_line_inf_tab IN OKC_OC_INT_CONFIG_PVT.line_inf_tbl_type
819 ) IS
820
821 i BINARY_INTEGER := 0;
822
823 BEGIN
824 IF (l_debug = 'Y') THEN
825 OKC_UTIL.print_trace(3, 'START --> get_price_attr- ');
826 OKC_UTIL.print_trace(3, 'Contract Id - '|| p_chr_id);
827 OKC_UTIL.print_trace(3, 'Quote Id - '|| p_qhr_id);
828 OKC_UTIL.print_trace(3, 'Order Id - '|| p_ohr_id);
829 END IF;
830
831 --get all the price attributes for the quote header (or order header)
832 --for processing at the header level
833 --and store them in global PL/SQL table g_pavv_tab
834 IF p_q_flag = OKC_API.G_TRUE AND p_qhr_id IS NOT NULL AND p_qhr_id <> OKC_API.G_MISS_NUM AND
835 p_line_inf_tab.FIRST IS NULL
836 THEN
837 IF (l_debug = 'Y') THEN
838 OKC_UTIL.print_trace(3, 'Calling get_pavv_tab with p_chr_id and p_qhr_id/p_ohr_id for processing at header level');
839 END IF;
840 -- get_pavv_tab stores it's output in g_pavv_tab
841 get_pavv_tab(p_chr_id => p_chr_id,
842 p_q_flag => p_q_flag,
843 p_qhr_id => p_qhr_id
844 );
845
846 IF (l_debug = 'Y') THEN
847 OKC_UTIL.print_trace(3, 'Call to get_pavv_tab finished successfully');
848 END IF;
849 ELSIF p_o_flag = OKC_API.G_TRUE AND p_ohr_id IS NOT NULL AND p_ohr_id <> OKC_API.G_MISS_NUM AND
850 p_line_inf_tab.FIRST IS NULL
851 THEN
852 IF (l_debug = 'Y') THEN
853 OKC_UTIL.print_trace(3, 'Calling get_pavv_tab with p_chr_id and p_qhr_id/p_ohr_id for processing at header level');
854 END IF;
855 -- get_pavv_tab stores it's output in g_pavv_tab
856 get_pavv_tab(p_chr_id => p_chr_id,
857 p_o_flag => p_o_flag,
858 p_ohr_id => p_ohr_id
859 );
860 IF (l_debug = 'Y') THEN
861 OKC_UTIL.print_trace(3, 'Call to get_pavv_tab finished successfully');
862 END IF;
863 END IF;
864
865
866
867 --get all the price attributes for each quote line (or order line)
868 --and store them all in global PL/SQL table g_pavv_tab
869 --keeping intact the header level information that g_pavv_tab may
870 --already contain.
871 IF p_line_inf_tab.FIRST IS NOT NULL THEN
872 IF (l_debug = 'Y') THEN
873 OKC_UTIL.print_trace(3, 'Calling get_pavv_tab with p_chr_id and p_qhr_id/p_ohr_id and p_line_inf_tab for processing at line level');
874 END IF;
875
876 i := p_line_inf_tab.FIRST;
877 WHILE i IS NOT NULL LOOP
878 IF p_q_flag = OKC_API.G_TRUE AND p_qhr_id IS NOT NULL AND p_qhr_id <> OKC_API.G_MISS_NUM AND
879 NVL(p_line_inf_tab(i).line_type,OKC_API.G_MISS_CHAR) <> OKC_OC_INT_CONFIG_PVT.G_MODEL_LINE THEN
880
881 -- get PRICE ATTRIBUTES information from ASO tables
882 -- get_pavv_tab stores it's output in g_pavv_tab
883 get_pavv_tab(p_chr_id => p_chr_id,
884 p_q_flag => p_q_flag,
885 p_qhr_id => p_qhr_id,
886 p_cle_id => p_line_inf_tab(i).cle_id,
887 p_qle_id => p_line_inf_tab(i).object1_id1
888 );
889 ELSIF p_o_flag = OKC_API.G_TRUE AND p_ohr_id IS NOT NULL AND p_ohr_id <> OKC_API.G_MISS_NUM AND
890 NVL(p_line_inf_tab(i).line_type,OKC_API.G_MISS_CHAR) <> OKC_OC_INT_CONFIG_PVT.G_MODEL_LINE THEN
891 -- get PRICE ATTRIBUTES information from ONT tables
892 -- get_pavv_tab stores it's output in g_pavv_tab
893 get_pavv_tab(p_chr_id => p_chr_id,
894 p_o_flag => p_o_flag,
895 p_ohr_id => p_ohr_id,
896 p_cle_id => p_line_inf_tab(i).cle_id,
897 p_ole_id => p_line_inf_tab(i).object1_id1
898 );
899 END IF;
900
901 i := p_line_inf_tab.NEXT(i);
902 END LOOP;
903 IF (l_debug = 'Y') THEN
904 OKC_UTIL.print_trace(3, 'Call to get_pavv_tab finished successfully');
905 END IF;
906 END IF;
907
908 IF (l_debug = 'Y') THEN
909 OKC_UTIL.print_trace(3, 'END --> get_price_attr- ');
910 END IF;
911 EXCEPTION
912 WHEN OTHERS THEN
913 IF (l_debug = 'Y') THEN
914 OKC_UTIL.print_trace(3,SQLERRM);
915 END IF;
916 -- Bug#2320635
917 OKC_API.set_message(G_APP_NAME,
918 G_UNEXPECTED_ERROR,
919 G_SQLCODE_TOKEN,
920 SQLCODE,
921 G_SQLERRM_TOKEN,
922 SQLERRM);
923 RAISE OKC_API.G_EXCEPTION_ERROR;
924 END get_price_attr;
925
926
927 --------------------------------------------------------------------------
928 --------get price adjustments from ASO or ONT and put in OKC pricing tables
929 --------------------------------------------------------------------------
930 -- Bug : 1686001 Changed references to ASO_QUOTE_HEADERS_ALL.QUOTE_HEADER_ID to OKX_QUOTE_HEADERS_V.ID1
931 PROCEDURE get_price_adj(
932 p_chr_id IN NUMBER,
933 p_q_flag IN VARCHAR2 ,
934 p_qhr_id IN OKX_QUOTE_HEADERS_V.ID1%TYPE
935 ,
936 p_o_flag IN VARCHAR2 ,
937 p_ohr_id IN NUMBER ,
938 p_line_inf_tab IN OKC_OC_INT_CONFIG_PVT.line_inf_tbl_type
939 ) IS
940
941 i BINARY_INTEGER := 0;
942
943 BEGIN
944 IF (l_debug = 'Y') THEN
945 OKC_UTIL.print_trace(3, 'START --> get_price_adj- ');
946 OKC_UTIL.print_trace(3, 'Contract Id - '|| p_chr_id);
947 OKC_UTIL.print_trace(3, 'Quote Id - '|| p_qhr_id);
948 OKC_UTIL.print_trace(3, 'Order Id - '|| p_ohr_id);
949 END IF;
950
951 --get all the price adjustments for the quote header (or order header)
952 --for processing at the header level
953 --and store them in global PL/SQL table g_patv_tab
954 IF p_q_flag = OKC_API.G_TRUE AND p_qhr_id IS NOT NULL AND p_qhr_id <> OKC_API.G_MISS_NUM AND
955 p_line_inf_tab.FIRST IS NULL
956 THEN
957 IF (l_debug = 'Y') THEN
958 OKC_UTIL.print_trace(3, 'Calling get_patv_tab with p_chr_id and p_qhr_id/p_ohr_id for processing at header level');
959 END IF;
960 -- get_patv_tab stores it's output in g_patv_tab
961 get_patv_tab(p_chr_id => p_chr_id,
962 p_q_flag => p_q_flag,
963 p_qhr_id => p_qhr_id
964 );
965 IF (l_debug = 'Y') THEN
966 OKC_UTIL.print_trace(3, 'Call to get_patv_tab finished successfully');
967 END IF;
968
969 ELSIF p_o_flag = OKC_API.G_TRUE AND p_ohr_id IS NOT NULL AND p_ohr_id <> OKC_API.G_MISS_NUM AND
970 p_line_inf_tab.FIRST IS NULL
971 THEN
972 IF (l_debug = 'Y') THEN
973 OKC_UTIL.print_trace(3, 'Calling get_patv_tab with p_chr_id and p_qhr_id/p_ohr_id for processing at header level');
974 END IF;
975 -- get_patv_tab stores it's output in g_patv_tab
976 get_patv_tab(p_chr_id => p_chr_id,
977 p_o_flag => p_o_flag,
978 p_ohr_id => p_ohr_id
979 );
980 IF (l_debug = 'Y') THEN
981 OKC_UTIL.print_trace(3, 'Call to get_patv_tab finished successfully');
982 END IF;
983 END IF;
984
985
986 --get all the price adjustments for each quote line (or order line)
987 --and store them all in global PL/SQL table g_patv_tab
988 --keeping intact the header level information that g_pavv_tab may
989 --already contain.
990 IF p_line_inf_tab.FIRST IS NOT NULL THEN
991 IF (l_debug = 'Y') THEN
992 OKC_UTIL.print_trace(3, 'Calling get_patv_tab with p_chr_id and p_qhr_id/p_ohr_id and p_line_inf_tab for processing at line level');
993 END IF;
994
995 i := p_line_inf_tab.FIRST;
996 WHILE i IS NOT NULL LOOP
997 IF p_q_flag = OKC_API.G_TRUE AND p_qhr_id IS NOT NULL AND p_qhr_id <> OKC_API.G_MISS_NUM AND
998 NVL(p_line_inf_tab(i).line_type, OKC_API.G_MISS_CHAR) <> OKC_OC_INT_CONFIG_PVT.G_MODEL_LINE THEN
999 -- get PRICE ADJUSTMENTS information from ASO tables
1000 --get_patv_tab stores it's output in g_patv_tab
1001 get_patv_tab(p_chr_id => p_chr_id,
1002 p_q_flag => p_q_flag,
1003 p_qhr_id => p_qhr_id,
1004 p_cle_id => p_line_inf_tab(i).cle_id,
1005 p_qle_id => p_line_inf_tab(i).object1_id1
1006 );
1007 ELSIF p_o_flag = OKC_API.G_TRUE AND p_ohr_id IS NOT NULL AND p_ohr_id <> OKC_API.G_MISS_NUM AND
1008 NVL(p_line_inf_tab(i).line_type, OKC_API.G_MISS_CHAR) <> OKC_OC_INT_CONFIG_PVT.G_MODEL_LINE THEN
1009 --get PRICE ADJUSTMENTS information from ONT tables
1010 --get_patv_tab stores it's output in g_patv_tab
1011 get_patv_tab(p_chr_id => p_chr_id,
1012 p_o_flag => p_o_flag,
1013 p_ohr_id => p_ohr_id,
1014 p_cle_id => p_line_inf_tab(i).cle_id,
1015 p_ole_id => p_line_inf_tab(i).object1_id1
1016 );
1017 END IF;
1018
1019 i := p_line_inf_tab.NEXT(i);
1020 END LOOP;
1021 IF (l_debug = 'Y') THEN
1022 OKC_UTIL.print_trace(3, 'Call to get_patv_tab finished successfully');
1023 END IF;
1024 END IF;
1025
1026 IF (l_debug = 'Y') THEN
1027 OKC_UTIL.print_trace(3, 'END --> get_price_adj- ');
1028 END IF;
1029 EXCEPTION
1030 WHEN OTHERS THEN
1031 IF (l_debug = 'Y') THEN
1032 OKC_UTIL.print_trace(3,SQLERRM);
1033 END IF;
1034 -- Bug#2320635
1035 OKC_API.set_message(G_APP_NAME,
1036 G_UNEXPECTED_ERROR,
1037 G_SQLCODE_TOKEN,
1038 SQLCODE,
1039 G_SQLERRM_TOKEN,
1040 SQLERRM);
1041 RAISE OKC_API.G_EXCEPTION_ERROR;
1042
1043 END get_price_adj;
1044 ---------------------------------------------------------------------------
1045
1046
1047 --put PRICE ADJUSTMENT RELATIONSHIPS FROM ASO (or ONT) into OKC
1048 -- Bug : 1686001 Changed references to ASO_QUOTE_HEADERS_ALL.QUOTE_HEADER_ID to OKX_QUOTE_HEADERS_V.ID1
1049 PROCEDURE get_price_adj_rltship(
1050 p_o_flag IN VARCHAR2 ,
1051 p_ohr_id IN NUMBER ,
1052 p_q_flag IN VARCHAR2 ,
1053 p_qhr_id IN OKX_QUOTE_HEADERS_V.ID1%TYPE
1054 ,
1055 p_line_inf_tab IN OKC_OC_INT_CONFIG_PVT.line_inf_tbl_type
1056 ) IS
1057
1058
1059 l_pacv_tbl OKC_PRICE_ADJUSTMENT_PUB.pacv_tbl_type;
1060 i BINARY_INTEGER;
1061
1062 x_pacv_tbl OKC_PRICE_ADJUSTMENT_PUB.pacv_tbl_type;
1063
1064 l_cle_id NUMBER := OKC_API.G_MISS_NUM;
1065 l_new_pat_id NUMBER := OKC_API.G_MISS_NUM;
1066
1067 BEGIN
1068
1069 IF (l_debug = 'Y') THEN
1070 OKC_UTIL.print_trace(3, 'START --> get_price_adj_rltship- ');
1071 OKC_UTIL.print_trace(3, 'Quote Id - '|| p_qhr_id);
1072 OKC_UTIL.print_trace(3, 'Order Id - '|| p_ohr_id);
1073 END IF;
1074
1075 --loop through global PL/SQL table g_price_adjustments_tab
1076 --for each old (ASO or ONT) pat_id, get the price adjustment relationships
1077 --and store them all in global PL/SQL table g_pacv_tab
1078 IF g_price_adjustments_tab.FIRST IS NOT NULL THEN
1079
1080 i := g_price_adjustments_tab.FIRST;
1081 WHILE i IS NOT NULL LOOP
1082
1083 --get_pacv_tab stores all the price adjustment relationships
1084 --in g_pacv_tab
1085 get_pacv_tab(p_old_pat_id => g_price_adjustments_tab(i).old_pat_id,
1086 p_new_pat_id => g_price_adjustments_tab(i).new_pat_id,
1087 p_q_flag => p_q_flag,
1088 p_qhr_id => p_qhr_id,
1089 p_o_flag => p_o_flag,
1090 p_ohr_id => p_ohr_id,
1091 p_line_inf_tab => p_line_inf_tab);
1092
1093 i := g_price_adjustments_tab.NEXT(i);
1094 END LOOP;
1095 END IF;
1096
1097 IF (l_debug = 'Y') THEN
1098 OKC_UTIL.print_trace(3, 'END --> get_price_adj_rltship- ');
1099 END IF;
1100 EXCEPTION
1101 WHEN OTHERS THEN
1102 IF (l_debug = 'Y') THEN
1103 OKC_UTIL.print_trace(3,SQLERRM);
1104 END IF;
1105 -- Bug#2320635
1106 OKC_API.set_message(G_APP_NAME,
1107 G_UNEXPECTED_ERROR,
1108 G_SQLCODE_TOKEN,
1109 SQLCODE,
1110 G_SQLERRM_TOKEN,
1111 SQLERRM);
1112 RAISE OKC_API.G_EXCEPTION_ERROR;
1113 END get_price_adj_rltship;
1114
1115 ----------------------------------------------------------------
1116 --put PRICE ADJUSTMENT ATTRIBUTES FROM ASO or ONT into OKC
1117 -- Bug : 1686001 Changed references to ASO_QUOTE_HEADERS_ALL.QUOTE_HEADER_ID to OKX_QUOTE_HEADERS_V.ID1
1118 PROCEDURE get_price_adj_attr(
1119 p_o_flag IN VARCHAR2 ,
1120 p_ohr_id IN NUMBER ,
1121 p_q_flag IN VARCHAR2 ,
1122 p_qhr_id IN OKX_QUOTE_HEADERS_V.ID1%TYPE
1123 ) IS
1124
1125 i BINARY_INTEGER := 0;
1126
1127 BEGIN
1128 IF (l_debug = 'Y') THEN
1129 OKC_UTIL.print_trace(3, 'START --> get_price_adj_attr- ');
1130 OKC_UTIL.print_trace(3, 'Quote Id - '|| p_qhr_id);
1131 OKC_UTIL.print_trace(3, 'Order Id - '|| p_ohr_id);
1132 END IF;
1133
1134 --loop through global PL/SQL table g_price_adjustments_tab
1135 --for each old (ASO or ONT) pat_id, get the price adjustment attributes
1136 --and store them all in global PL/SQL table g_paav_tab
1137 IF g_price_adjustments_tab.FIRST IS NOT NULL THEN
1138
1139 i := g_price_adjustments_tab.FIRST;
1140 WHILE i IS NOT NULL LOOP
1141
1142 --get_paav_tab stores all the price adjustment attributes in g_paav_tab
1143 get_paav_tab(p_old_pat_id => g_price_adjustments_tab(i).old_pat_id,
1144 p_new_pat_id => g_price_adjustments_tab(i).new_pat_id,
1145 p_q_flag => p_q_flag,
1146 p_qhr_id => p_qhr_id,
1147 p_o_flag => p_o_flag,
1148 p_ohr_id => p_ohr_id);
1149
1150 i := g_price_adjustments_tab.NEXT(i);
1151 END LOOP;
1152 END IF;
1153
1154 IF (l_debug = 'Y') THEN
1155 OKC_UTIL.print_trace(3, 'END --> get_price_adj_attr- ');
1156 END IF;
1157 EXCEPTION
1158 WHEN OTHERS THEN
1159 IF (l_debug = 'Y') THEN
1160 OKC_UTIL.print_trace(3,SQLERRM);
1161 END IF;
1162 -- Bug#2320635
1163 OKC_API.set_message(G_APP_NAME,
1164 G_UNEXPECTED_ERROR,
1165 G_SQLCODE_TOKEN,
1166 SQLCODE,
1167 G_SQLERRM_TOKEN,
1168 SQLERRM);
1169 RAISE OKC_API.G_EXCEPTION_ERROR;
1170 END get_price_adj_attr;
1171
1172
1173 ----------------------------------------------------------------------------
1174 -- PROCEDURE to get PRICE ADJUSTMENTS information from ASO or ONT tables
1175 -- get_patv_tab stores it's output in global PL/SQL table g_patv_tab
1176 ----------------------------------------------------------------------------
1177 -- Bug : 1686001 Changed references to ASO_QUOTE_HEADERS_ALL.QUOTE_HEADER_ID to OKX_QUOTE_HEADERS_V.ID1
1178 -- ASO_QUOTE_LINES_ALL.LINE_HEADER_ID to OKX_QUOTE_LINES_V.ID1
1179 PROCEDURE get_patv_tab(
1180 p_chr_id IN OKC_K_HEADERS_B.ID%TYPE,
1181 p_cle_id IN OKC_K_LINES_B.ID%TYPE ,
1182 p_o_flag IN VARCHAR2 ,
1183 p_ohr_id IN NUMBER ,
1184 p_ole_id IN NUMBER ,
1185 p_q_flag IN VARCHAR2 ,
1186 p_qhr_id IN OKX_QUOTE_HEADERS_V.ID1%TYPE
1187 ,
1188 p_qle_id IN OKX_QUOTE_LINES_V.ID1%TYPE
1189 ) IS
1190
1191
1192 --------------l_no_data_found BOOLEAN := TRUE;
1193
1194 i BINARY_INTEGER := 0;
1195
1196 --cursor to get okx_qte_prc_adjmnts_v or
1197 -- okx_ord_prc_adjmnts_v information
1198 -- Bug : 1686001 Changed references to ASO_QUOTE_HEADERS_ALL.QUOTE_HEADER_ID to OKX_QUOTE_HEADERS_V.ID1
1199 -- ASO_QUOTE_LINES_ALL.LINE_HEADER_ID to OKX_QUOTE_LINES_V.ID1
1200 CURSOR c_source_patv_rec (cp_q_flag IN VARCHAR2,
1201 cp_qhr_id IN OKX_QUOTE_HEADERS_V.ID1%TYPE,
1202 cp_qle_id IN OKX_QUOTE_LINES_V.ID1%TYPE,
1203 cp_o_flag IN VARCHAR2,
1204 cp_ohr_id IN NUMBER,
1205 cp_ole_id IN NUMBER) IS
1206 --could be either ASO or ONT
1207 --only ONE of the following two queries in the union will be executed in
1208 --a call depending on which flag (p_q_flag or p_o_flag) is true
1209
1210 -- first query to get okx_qte_prc_adjmnts_v information
1211 SELECT --parent_adjustment_id, --not used
1212 pa.price_adjustment_id price_adjustment_id,
1213 pa.quote_header_id source_header_id,
1214 pa.quote_line_id source_line_id,
1215 pa.modified_from modified_from,
1216 pa.modified_to modified_to,
1217 NVL(pa.modifier_mechanism_type_code, qh.list_type_code) modifier_mechanism_type_code, --not used
1218 pa.operand operand,
1219 pa.arithmetic_operator arithmetic_operator,
1220 pa.automatic_flag automatic_flag,
1221 pa.update_allowable_flag update_allowable_flag,
1222 pa.updated_flag updated_flag,
1223 pa.applied_flag applied_flag,
1224 pa.on_invoice_flag on_invoice_flag,
1225 pa.pricing_phase_id pricing_phase_id,
1226 pa.attribute_category attribute_category,
1227 ---list_header_id obsolete columns in ASO
1228 ---list_line_id
1229 ---list_line_type_code
1230 pa.modifier_header_id list_header_id,
1231 pa.modifier_line_id list_line_id,
1232 pa.modifier_line_type_code list_line_type_code,
1233 pa.change_reason_code change_reason_code,
1234 pa.change_reason_text change_reason_text,
1235 pa.estimated_flag estimated_flag,
1236 pa.adjusted_amount adjusted_amount,
1237 pa.charge_type_code charge_type_code,
1238 pa.charge_subtype_code charge_subtype_code,
1239 pa.range_break_quantity range_break_quantity,
1240 pa.accrual_conversion_rate accrual_conversion_rate,
1241 pa.pricing_group_sequence pricing_group_sequence,
1242 pa.accrual_flag accrual_flag,
1243 NVL(pa.list_line_no, ql.list_line_no) list_line_no,
1244 pa.source_system_code source_system_code,
1245 pa.benefit_qty benefit_qty,
1246 pa.benefit_uom_code benefit_uom_code,
1247 pa.expiration_date expiration_date,
1248 pa.modifier_level_code modifier_level_code,
1249 pa.price_break_type_code price_break_type_code,
1250 pa.substitution_attribute substitution_attribute,
1251 pa.proration_type_code proration_type_code,
1252 pa.include_on_returns_flag include_on_returns_flag,
1253 pa.object_version_number object_version_number,
1254 pa.attribute1 attribute1,
1255 pa.attribute2 attribute2,
1256 pa.attribute3 attribute3,
1257 pa.attribute4 attribute4,
1258 pa.attribute5 attribute5,
1259 pa.attribute6 attribute6,
1260 pa.attribute7 attribute7,
1261 pa.attribute8 attribute8,
1262 pa.attribute9 attribute9,
1263 pa.attribute10 attribute10,
1264 pa.attribute11 attribute11,
1265 pa.attribute12 attribute12,
1266 pa.attribute13 attribute13,
1267 pa.attribute14 attribute14,
1268 pa.attribute15 attribute15,
1269 pa.rebate_transaction_type_code rebate_transaction_type_code
1270 FROM okx_qte_prc_adjmnts_v pa,
1271 qp_list_lines ql,
1272 qp_list_headers_b qh
1273 WHERE cp_q_flag = OKC_API.G_TRUE
1274 AND pa.quote_header_id = cp_qhr_id
1275 AND pa.modifier_line_type_code <> 'FREIGHT_CHARGE' -- Bug 2054770
1276 AND ((cp_qle_id = OKC_API.G_MISS_NUM AND pa.quote_line_id IS NULL) OR
1277 (cp_qle_id <> OKC_API.G_MISS_NUM AND pa.quote_line_id = cp_qle_id))
1278 AND pa.modifier_header_id = qh.list_header_id
1279 AND pa.modifier_line_id = ql.list_line_id
1280 AND ( pa.applied_flag IS NULL OR pa.applied_flag = 'Y' ) -- Bug 2801279
1281
1282 UNION ALL -- second query to get okx_ord_prc_adjmnts_v information
1283
1284 SELECT ------price_adjustment_id, --not used
1285 pa.price_adjustment_id,
1286 pa.header_id source_header_id,
1287 pa.line_id source_line_id,
1288 TO_NUMBER(pa.modified_from) modified_from,
1289 TO_NUMBER(pa.modified_to) modified_to,
1290 qh.list_type_code modifier_mechanism_type_code,
1291 pa.operand,
1292 pa.arithmetic_operator,
1293 pa.automatic_flag,
1294 pa.update_allowed,
1295 pa.updated_flag,
1296 pa.applied_flag,
1297 pa.invoiced_flag,
1298 pa.pricing_phase_id,
1299 pa.context,
1300 pa.list_header_id,
1301 pa.list_line_id,
1302 pa.list_line_type_code,
1303 pa.change_reason_code,
1304 pa.change_reason_text,
1305 pa.estimated_flag,
1306 pa.adjusted_amount,
1307 pa.charge_type_code,
1308 pa.charge_subtype_code,
1309 pa.range_break_quantity,
1310 pa.accrual_conversion_rate,
1311 pa.pricing_group_sequence,
1312 pa.accrual_flag,
1313 NVL(pa.list_line_no, ql.list_line_no) list_line_no,
1314 pa.source_system_code,
1315 pa.benefit_qty,
1316 pa.benefit_uom_code,
1317 pa.expiration_date,
1318 pa.modifier_level_code,
1319 pa.price_break_type_code,
1320 pa.substitution_attribute,
1321 pa.proration_type_code,
1322 pa.include_on_returns_flag,
1323 TO_NUMBER(NULL), --object_version_number not present in order table
1324 pa.attribute1,
1325 pa.attribute2,
1326 pa.attribute3,
1327 pa.attribute4,
1328 pa.attribute5,
1329 pa.attribute6,
1330 pa.attribute7,
1331 pa.attribute8,
1332 pa.attribute9,
1333 pa.attribute10,
1334 pa.attribute11,
1335 pa.attribute12,
1336 pa.attribute13,
1337 pa.attribute14,
1338 pa.attribute15,
1339 pa.rebate_transaction_type_code
1340 FROM okx_ord_prc_adjmnts_v pa,
1341 qp_list_lines ql,
1342 qp_list_headers_b qh
1343 WHERE cp_o_flag = OKC_API.G_TRUE
1344 AND pa.header_id = cp_ohr_id
1345 AND pa.list_line_type_code <> 'FREIGHT_CHARGE' -- Bug 2054770
1346 AND ((cp_ole_id = OKC_API.G_MISS_NUM AND pa.line_id IS NULL) OR
1347 (cp_ole_id <> OKC_API.G_MISS_NUM AND pa.line_id = cp_ole_id))
1348 AND pa.list_header_id = qh.list_header_id
1349 AND pa.list_line_id = ql.list_line_id ;
1350
1351 l_source_patv_rec c_source_patv_rec%ROWTYPE;
1352
1353 BEGIN
1354 IF (l_debug = 'Y') THEN
1355 OKC_UTIL.print_trace(4, 'START --> get_patv_tab- ');
1356 OKC_UTIL.print_trace(4, 'Contract Id - '|| p_chr_id);
1357 OKC_UTIL.print_trace(4, 'Contract Line Id - '|| p_cle_id);
1358 OKC_UTIL.print_trace(4, 'Quote Id - '|| p_qhr_id);
1359 OKC_UTIL.print_trace(4, 'Quote Line Id - '|| p_qle_id);
1360 OKC_UTIL.print_trace(4, 'Order Id - '|| p_ohr_id);
1361 OKC_UTIL.print_trace(4, 'Order Line Id - '|| p_ole_id);
1362 END IF;
1363
1364 IF (p_q_flag = OKC_API.G_TRUE AND
1365 p_qhr_id IS NOT NULL AND
1366 p_qhr_id <> OKC_API.G_MISS_NUM) THEN
1367 IF (l_debug = 'Y') THEN
1368 OKC_UTIL.print_trace(4, 'Processing quote pricing information for Quote Id - '|| p_qhr_id);
1369 END IF;
1370 ELSIF (p_o_flag = OKC_API.G_TRUE AND
1371 p_ohr_id IS NOT NULL AND
1372 p_ohr_id <> OKC_API.G_MISS_NUM) THEN
1373 IF (l_debug = 'Y') THEN
1374 OKC_UTIL.print_trace(4, 'Processing order pricing information for Order Id - '|| p_ohr_id);
1375 END IF;
1376 END IF;
1377
1378
1379 OPEN c_source_patv_rec(cp_q_flag => p_q_flag,
1380 cp_qhr_id => p_qhr_id,
1381 cp_qle_id => p_qle_id,
1382 cp_o_flag => p_o_flag,
1383 cp_ohr_id => p_ohr_id,
1384 cp_ole_id => p_ole_id); --ASO or ONT
1385
1386 LOOP
1387 --use COUNT to keep adding to existing records,if any, in g_pavv_tab
1388 --otherwise if table empty, COUNT returns 0
1389 i := g_patv_tab.COUNT + 1;
1390 FETCH c_source_patv_rec INTO l_source_patv_rec;
1391 EXIT WHEN c_source_patv_rec%NOTFOUND;
1392
1393 g_patv_tab(i).id := l_source_patv_rec.price_adjustment_id;
1394
1395 --g_patv_tab(i).PAT_ID := l_source_patv_rec.parent_adjustment_id;
1396 --not used
1397
1398 IF l_source_patv_rec.source_header_id IS NOT NULL THEN
1399 g_patv_tab(i).CHR_ID := p_chr_id;
1400 END IF;
1401
1402 IF l_source_patv_rec.source_line_id IS NOT NULL THEN
1403 g_patv_tab(i).CLE_ID := p_cle_id;
1404 END IF;
1405
1406 g_patv_tab(i).MODIFIED_FROM := l_source_patv_rec.modified_from;
1407 g_patv_tab(i).MODIFIED_TO := l_source_patv_rec.modified_to;
1408 g_patv_tab(i).MODIFIER_MECHANISM_TYPE_CODE := l_source_patv_rec.modifier_mechanism_type_code; -- not used
1409 g_patv_tab(i).OPERAND := l_source_patv_rec.operand;
1410 g_patv_tab(i).ARITHMETIC_OPERATOR := l_source_patv_rec.arithmetic_operator;
1411 g_patv_tab(i).AUTOMATIC_FLAG := l_source_patv_rec.automatic_flag;
1412 g_patv_tab(i).UPDATE_ALLOWED := l_source_patv_rec.update_allowable_flag;
1413 g_patv_tab(i).UPDATED_FLAG := l_source_patv_rec.updated_flag;
1414 g_patv_tab(i).APPLIED_FLAG := l_source_patv_rec.applied_flag;
1415 g_patv_tab(i).ON_INVOICE_FLAG := l_source_patv_rec.on_invoice_flag;
1416 g_patv_tab(i).PRICING_PHASE_ID := l_source_patv_rec.pricing_phase_id;
1417 g_patv_tab(i).CONTEXT := l_source_patv_rec.attribute_category;
1418 --g_patv_tab(i).PROGRAM_APPLICATION_ID := l_source_patv_rec.program_application_id;
1419 --g_patv_tab(i).PROGRAM_ID := l_source_patv_rec.program_id;
1420 --g_patv_tab(i).PROGRAM_UPDATE_DATE := l_source_patv_rec.program_update_date;
1421 --g_patv_tab(i).REQUEST_ID := l_source_patv_rec.request_id;
1422 g_patv_tab(i).LIST_HEADER_ID := l_source_patv_rec.list_header_id;
1423 g_patv_tab(i).LIST_LINE_ID := l_source_patv_rec.list_line_id;
1424 g_patv_tab(i).LIST_LINE_TYPE_CODE := l_source_patv_rec.list_line_type_code;
1425 g_patv_tab(i).CHANGE_REASON_CODE := l_source_patv_rec.change_reason_code;
1426 g_patv_tab(i).CHANGE_REASON_TEXT := l_source_patv_rec.change_reason_text;
1427 g_patv_tab(i).ESTIMATED_FLAG := l_source_patv_rec.estimated_flag;
1428 g_patv_tab(i).ADJUSTED_AMOUNT := l_source_patv_rec.adjusted_amount;
1429 g_patv_tab(i).CHARGE_TYPE_CODE := l_source_patv_rec.charge_type_code;
1430 g_patv_tab(i).CHARGE_SUBTYPE_CODE := l_source_patv_rec.charge_subtype_code;
1431 g_patv_tab(i).RANGE_BREAK_QUANTITY := l_source_patv_rec.range_break_quantity;
1432 g_patv_tab(i).ACCRUAL_CONVERSION_RATE := l_source_patv_rec.accrual_conversion_rate;
1433 g_patv_tab(i).PRICING_GROUP_SEQUENCE := l_source_patv_rec.pricing_group_sequence;
1434 g_patv_tab(i).ACCRUAL_FLAG := l_source_patv_rec.accrual_flag;
1435 g_patv_tab(i).LIST_LINE_NO := l_source_patv_rec.list_line_no;
1436 g_patv_tab(i).SOURCE_SYSTEM_CODE := l_source_patv_rec.source_system_code;
1437 g_patv_tab(i).BENEFIT_QTY := l_source_patv_rec.benefit_qty;
1438 g_patv_tab(i).BENEFIT_UOM_CODE := l_source_patv_rec.benefit_uom_code;
1439 g_patv_tab(i).EXPIRATION_DATE := l_source_patv_rec.expiration_date;
1440 g_patv_tab(i).MODIFIER_LEVEL_CODE := l_source_patv_rec.modifier_level_code;
1441 g_patv_tab(i).PRICE_BREAK_TYPE_CODE := l_source_patv_rec.price_break_type_code;
1442 g_patv_tab(i).SUBSTITUTION_ATTRIBUTE := l_source_patv_rec.substitution_attribute;
1443 g_patv_tab(i).PRORATION_TYPE_CODE := l_source_patv_rec.proration_type_code;
1444 g_patv_tab(i).INCLUDE_ON_RETURNS_FLAG := l_source_patv_rec.include_on_returns_flag;
1445 g_patv_tab(i).OBJECT_VERSION_NUMBER := l_source_patv_rec.object_version_number;
1446 g_patv_tab(i).ATTRIBUTE1 := l_source_patv_rec.ATTRIBUTE1;
1447 g_patv_tab(i).ATTRIBUTE2 := l_source_patv_rec.ATTRIBUTE2;
1448 g_patv_tab(i).ATTRIBUTE3 := l_source_patv_rec.ATTRIBUTE3;
1449 g_patv_tab(i).ATTRIBUTE4 := l_source_patv_rec.ATTRIBUTE4;
1450 g_patv_tab(i).ATTRIBUTE5 := l_source_patv_rec.ATTRIBUTE5;
1451 g_patv_tab(i).ATTRIBUTE6 := l_source_patv_rec.ATTRIBUTE6;
1452 g_patv_tab(i).ATTRIBUTE7 := l_source_patv_rec.ATTRIBUTE7;
1453 g_patv_tab(i).ATTRIBUTE8 := l_source_patv_rec.ATTRIBUTE8;
1454 g_patv_tab(i).ATTRIBUTE9 := l_source_patv_rec.ATTRIBUTE9;
1455 g_patv_tab(i).ATTRIBUTE10 := l_source_patv_rec.ATTRIBUTE10;
1456 g_patv_tab(i).ATTRIBUTE11 := l_source_patv_rec.ATTRIBUTE11;
1457 g_patv_tab(i).ATTRIBUTE12 := l_source_patv_rec.ATTRIBUTE12;
1458 g_patv_tab(i).ATTRIBUTE13 := l_source_patv_rec.ATTRIBUTE13;
1459 g_patv_tab(i).ATTRIBUTE14 := l_source_patv_rec.ATTRIBUTE14;
1460 g_patv_tab(i).ATTRIBUTE15 := l_source_patv_rec.ATTRIBUTE15;
1461 g_patv_tab(i).REBATE_TRANSACTION_TYPE_CODE := l_source_patv_rec.rebate_transaction_type_code;
1462
1463 END LOOP;
1464 ----------IF c_source_patv_rec%ROWCOUNT > 0 THEN
1465 ---------- l_no_data_found := FALSE;
1466 ----------END IF;
1467 CLOSE c_source_patv_rec;
1468
1469
1470 ------IF l_no_data_found THEN
1471 ------- OKC_UTIL.print_trace(4, 'END --> get_patv_tab: returned error- ');
1472 -------- RAISE OKC_API.G_EXCEPTION_ERROR;
1473 ------ELSE
1474 IF (l_debug = 'Y') THEN
1475 OKC_UTIL.print_trace(4, 'Output: PL/SQL global table- g_patv_tab');
1476 OKC_UTIL.print_trace(4, 'END --> get_patv_tab- ');
1477 END IF;
1478 ------END IF;
1479
1480 EXCEPTION
1481 WHEN OTHERS THEN
1482 IF (l_debug = 'Y') THEN
1483 OKC_UTIL.print_trace(4,SQLERRM);
1484 END IF;
1485 -- Bug#2320635
1486 OKC_API.set_message(G_APP_NAME,
1487 G_UNEXPECTED_ERROR,
1488 G_SQLCODE_TOKEN,
1489 SQLCODE,
1490 G_SQLERRM_TOKEN,
1491 SQLERRM);
1492 IF c_source_patv_rec%ISOPEN THEN
1493 CLOSE c_source_patv_rec;
1494 END IF;
1495 RAISE OKC_API.G_EXCEPTION_ERROR;
1496 END get_patv_tab;
1497
1498 ---------------------------------------------------------------
1499 -- PROCEDURE to get PRICE ADJUSTMENT RELATIONSHIP information
1500 -- from ASO or ONT tables
1501 -- get_pacv_tab stores all the price adjustment relationships in
1502 -- global PL/SQL table g_pacv_tab
1503 -- Bug : 1686001 Changed references to ASO_QUOTE_HEADERS_ALL.QUOTE_HEADER_ID to OKX_QUOTE_HEADERS_V.ID1
1504 PROCEDURE get_pacv_tab(
1505 p_old_pat_id IN NUMBER ,
1506 p_new_pat_id IN NUMBER ,
1507 p_o_flag IN VARCHAR2 ,
1508 p_ohr_id IN NUMBER
1509 ,
1510 p_q_flag IN VARCHAR2 ,
1511 p_qhr_id IN OKX_QUOTE_HEADERS_V.ID1%TYPE
1512 ,
1513 p_line_inf_tab IN OKC_OC_INT_CONFIG_PVT.line_inf_tbl_type
1514 )
1515 IS
1516
1517 l_no_data_found BOOLEAN := TRUE;
1518
1519 i BINARY_INTEGER := 0;
1520 j BINARY_INTEGER := 0;
1521 l_cle_found_flg BOOLEAN := FALSE;
1522 l_pat_id_found_flg BOOLEAN := FALSE;
1523
1524 -- cursor to get okx_qte_prc_adj_rlshp_v and
1525 -- okx_ord_prc_adj_rlshp_v information
1526 CURSOR c_source_pacv_rec (cp_q_flag IN VARCHAR2,
1527 cp_o_flag IN VARCHAR2,
1528 cp_old_pat_id IN NUMBER) IS
1529 --could be either ASO or ONT
1530 --only ONE of the following two queries in the union will be executed in
1531 --a call depending on which flag (p_q_flag or p_o_flag) is true
1532
1533 -- first query to get okx_qte_prc_adj_rlshp_v information
1534 SELECT ----price_adjustment_id, --not needed
1535 price_adjustment_id,
1536 rltd_price_adj_id,
1537 quote_line_id source_line_id,
1538 object_version_number
1539 FROM okx_qte_prc_adj_rlshp_v
1540 WHERE cp_q_flag = OKC_API.G_TRUE
1541 AND price_adjustment_id = cp_old_pat_id
1542
1543 UNION ALL
1544
1545 -- second query to get okx_ord_prc_adj_rlshp_v information
1546 SELECT --------price_adjustment_id, --not needed
1547 price_adjustment_id,
1548 rltd_price_adj_id,
1549 line_id source_line_id,
1550 TO_NUMBER(NULL) ----object_version_number --not in order table
1551 FROM okx_ord_prc_adj_rlshp_v
1552 WHERE cp_o_flag = OKC_API.G_TRUE
1553 AND price_adjustment_id = cp_old_pat_id;
1554
1555 l_source_pacv_rec c_source_pacv_rec%ROWTYPE;
1556
1557 BEGIN
1558
1559 IF (l_debug = 'Y') THEN
1560 OKC_UTIL.print_trace(4, 'START --> get_pacv_tab- ');
1561 OKC_UTIL.print_trace(4, 'p_old_pat_id- '|| p_old_pat_id);
1562 OKC_UTIL.print_trace(4, 'p_new_pat_id- '|| p_new_pat_id);
1563 END IF;
1564
1565 IF (l_debug = 'Y') THEN
1566 OKC_UTIL.print_trace(4, 'Quote Id - '|| p_qhr_id);
1567 OKC_UTIL.print_trace(4, 'Order Id - '|| p_ohr_id);
1568 OKC_UTIL.print_trace(4, 'p_line_inf_tab: PL/SQL global table of contract lines against related quote or order lines');
1569 END IF;
1570
1571
1572 IF (p_q_flag = OKC_API.G_TRUE AND
1573 p_qhr_id IS NOT NULL AND
1574 p_qhr_id <> OKC_API.G_MISS_NUM) THEN
1575 IF (l_debug = 'Y') THEN
1576 OKC_UTIL.print_trace(4, 'Processing quote pricing information for Quote Id - '|| p_qhr_id);
1577 END IF;
1578 ELSIF (p_o_flag = OKC_API.G_TRUE AND
1579 p_ohr_id IS NOT NULL AND
1580 p_ohr_id <> OKC_API.G_MISS_NUM) THEN
1581 IF (l_debug = 'Y') THEN
1582 OKC_UTIL.print_trace(4, 'Processing order pricing information for Order Id - '|| p_ohr_id);
1583 END IF;
1584 END IF;
1585
1586
1587 OPEN c_source_pacv_rec (cp_q_flag => p_q_flag,
1588 cp_o_flag => p_o_flag,
1589 cp_old_pat_id => p_old_pat_id);
1590
1591 LOOP
1592 i := g_pacv_tab.COUNT + 1;
1593 --use COUNT to keep adding to existing records, if any,
1594 --in g_paav_tab
1595 --otherwise if table empty, COUNT returns 0
1596 FETCH c_source_pacv_rec INTO l_source_pacv_rec;
1597 EXIT WHEN c_source_pacv_rec%NOTFOUND;
1598
1599 -- we don't need to enter the ID because
1600 -- it is automatically generated
1601
1602 g_pacv_tab(i).PAT_ID_FROM := p_new_pat_id;
1603 -- this is the parent line
1604
1605 --get the child line
1606 --corresponding to PRICE_ADJUSTMENT_ID from
1607 --ASO or ONT price adjustment relationship tables
1608 IF l_source_pacv_rec.price_adjustment_id IS NOT NULL THEN
1609 -- get the new_pat_id against the price_adjustment_id
1610 -- from g_price_adjustments_tab
1611 IF g_price_adjustments_tab.FIRST IS NOT NULL THEN
1612 j := g_price_adjustments_tab.FIRST;
1613 WHILE (j IS NOT NULL OR l_pat_id_found_flg = FALSE) LOOP
1614 -----IF g_price_adjustments_tab(j).old_pat_id = l_source_pacv_rec.price_adjustment_id THEN
1615 IF g_price_adjustments_tab(j).old_pat_id = NVL(l_source_pacv_rec.rltd_price_adj_id,
1616 0) THEN
1617 g_pacv_tab(i).PAT_ID := g_price_adjustments_tab(j).new_pat_id;
1618 l_pat_id_found_flg := TRUE;
1619 END IF;
1620 j := g_price_adjustments_tab.NEXT(j);
1621 END LOOP;
1622 END IF;
1623 END IF;
1624
1625
1626 IF l_source_pacv_rec.source_line_id IS NOT NULL THEN
1627 -- get the contract line against this quote or order line from p_line_inf_tab
1628 IF p_line_inf_tab.FIRST IS NOT NULL THEN
1629 j := p_line_inf_tab.FIRST;
1630 WHILE (j IS NOT NULL OR l_cle_found_flg = FALSE) LOOP
1631 IF p_line_inf_tab(j).object1_id1 = l_source_pacv_rec.source_line_id THEN
1632 g_pacv_tab(i).CLE_ID := p_line_inf_tab(j).cle_id;
1633 l_cle_found_flg := TRUE;
1634 END IF;
1635 j := p_line_inf_tab.NEXT(j);
1636 END LOOP;
1637 END IF;
1638 END IF;
1639
1640 --g_pacv_tab(i).PROGRAM_APPLICATION_ID := l_source_pacv_rec.program_application_id;
1641 --g_pacv_tab(i).PROGRAM_ID := l_source_pacv_rec.program_id;
1642 --g_pacv_tab(i).PROGRAM_UPDATE_DATE := l_source_pacv_rec.program_update_date;
1643 --g_pacv_tab(i).REQUEST_ID := l_source_pacv_rec.request_id;
1644 g_pacv_tab(i).OBJECT_VERSION_NUMBER := l_source_pacv_rec.object_version_number;
1645
1646 END LOOP;
1647 ---------IF c_source_pacv_rec%ROWCOUNT > 0 THEN
1648 ------------l_no_data_found := FALSE;
1649 ---------END IF;
1650 CLOSE c_source_pacv_rec;
1651
1652
1653 --------IF l_no_data_found THEN
1654 ------OKC_UTIL.print_trace(4, 'END --> get_pacv_tab: returned error- ');
1655 ------ RAISE OKC_API.G_EXCEPTION_ERROR;
1656 --------ELSE
1657 IF (l_debug = 'Y') THEN
1658 OKC_UTIL.print_trace(4, 'Output: PL/SQL global table- g_pacv_tab');
1659 OKC_UTIL.print_trace(4, 'END --> get_pacv_tab- ');
1660 END IF;
1661 --------END IF;
1662
1663
1664 EXCEPTION
1665 WHEN OTHERS THEN
1666 IF (l_debug = 'Y') THEN
1667 OKC_UTIL.print_trace(4,SQLERRM);
1668 END IF;
1669 -- Bug#2320635
1670 OKC_API.set_message(G_APP_NAME,
1671 G_UNEXPECTED_ERROR,
1672 G_SQLCODE_TOKEN,
1673 SQLCODE,
1674 G_SQLERRM_TOKEN,
1675 SQLERRM);
1676 IF c_source_pacv_rec%ISOPEN THEN
1677 CLOSE c_source_pacv_rec;
1678 END IF;
1679 RAISE OKC_API.G_EXCEPTION_ERROR;
1680 END get_pacv_tab;
1681
1682
1683 -----------------------------------------------------------------------------
1684 -- to get PRICE ADJUSTMENT ATTRIBUTES information from ASO or ONT tables
1685 -- get_paav_tab stores all the price adjustment attributes in global
1686 -- PL/SQL table g_paav_tab
1687 -----------------------------------------------------------------------------
1688 PROCEDURE get_paav_tab(p_old_pat_id IN NUMBER ,
1689 p_new_pat_id IN NUMBER ,
1690 p_q_flag IN VARCHAR2 ,
1691 p_qhr_id IN NUMBER ,
1692 p_o_flag IN VARCHAR2 ,
1693 p_ohr_id IN NUMBER ) IS
1694
1695 l_no_data_found BOOLEAN := TRUE;
1696
1697 i BINARY_INTEGER := 0;
1698
1699 -- cursor to get okx_qte_prc_adj_atrbs_v or
1700 -- okx_ord_prc_adj_atrbs_v information
1701 CURSOR c_source_paav_rec(cp_q_flag IN VARCHAR2,
1702 cp_o_flag IN VARCHAR2,
1703 cp_old_pat_id IN NUMBER) IS
1704 --could be either ASO or ONT
1705 --only ONE of the following two queries in the union will be executed in
1706 --a call depending on which flag (p_q_flag or p_o_flag) is true
1707
1708 -- first query to get okx_qte_prc_adj_atrbs_v information
1709 SELECT ----price_adjustment_id, --not needed
1710 flex_title,
1711 pricing_context,
1712 pricing_attribute,
1713 pricing_attr_value_from,
1714 pricing_attr_value_to,
1715 comparison_operator,
1716 object_version_number
1717 FROM okx_qte_prc_adj_atrbs_v
1718 WHERE cp_q_flag = OKC_API.G_TRUE
1719 AND price_adjustment_id = cp_old_pat_id
1720
1721 UNION ALL
1722
1723 -- second query to get okx_ord_prc_adj_atrbs_v information
1724 SELECT --------price_adjustment_id, --not needed
1725 flex_title,
1726 pricing_context,
1727 pricing_attribute,
1728 pricing_attr_value_from,
1729 pricing_attr_value_to,
1730 comparison_operator,
1731 TO_NUMBER(NULL) ---object_version_number not in order table
1732 FROM okx_ord_prc_adj_atrbs_v
1733 WHERE cp_o_flag = OKC_API.G_TRUE
1734 AND price_adjustment_id = cp_old_pat_id;
1735
1736 l_source_paav_rec c_source_paav_rec%ROWTYPE;
1737
1738 BEGIN
1739
1740 IF (l_debug = 'Y') THEN
1741 OKC_UTIL.print_trace(4, 'START --> get_paav_tab- ');
1742 OKC_UTIL.print_trace(4, 'p_old_pat_id- '|| p_old_pat_id);
1743 OKC_UTIL.print_trace(4, 'p_new_pat_id- '|| p_new_pat_id);
1744 OKC_UTIL.print_trace(4, 'Quote Id - '|| p_qhr_id);
1745 OKC_UTIL.print_trace(4, 'Order Id - '|| p_ohr_id);
1746 END IF;
1747
1748
1749 IF (p_q_flag = OKC_API.G_TRUE AND
1750 p_qhr_id IS NOT NULL AND
1751 p_qhr_id <> OKC_API.G_MISS_NUM) THEN
1752 IF (l_debug = 'Y') THEN
1753 OKC_UTIL.print_trace(4, 'Processing quote pricing information for Quote Id - '|| p_qhr_id);
1754 END IF;
1755 ELSIF (p_o_flag = OKC_API.G_TRUE AND
1756 p_ohr_id IS NOT NULL AND
1757 p_ohr_id <> OKC_API.G_MISS_NUM) THEN
1758 IF (l_debug = 'Y') THEN
1759 OKC_UTIL.print_trace(4, 'Processing order pricing information for Order Id - '|| p_ohr_id);
1760 END IF;
1761 END IF;
1762
1763 OPEN c_source_paav_rec(cp_q_flag => p_q_flag,
1764 cp_o_flag => p_o_flag,
1765 cp_old_pat_id => p_old_pat_id);
1766
1767 LOOP
1768 --use COUNT to keep adding to existing records, if any, in g_paav_tab
1769 --otherwise if table empty, COUNT returns 0
1770 i := g_paav_tab.COUNT + 1;
1771 FETCH c_source_paav_rec INTO l_source_paav_rec;
1772 EXIT WHEN c_source_paav_rec%NOTFOUND;
1773
1774 -- we don't need to enter the ID because
1775 -- it is automatically generated
1776 g_paav_tab(i).PAT_ID := p_new_pat_id;
1777
1778 IF l_source_paav_rec.flex_title IS NULL OR l_source_paav_rec.flex_title = OKC_API.G_MISS_CHAR THEN
1779 g_paav_tab(i).FLEX_TITLE := 'QP_ATTR_DEFNS_PRICING';
1780 ELSE
1781 g_paav_tab(i).FLEX_TITLE := l_source_paav_rec.flex_title;
1782 END IF;
1783
1784
1785 g_paav_tab(i).PRICING_CONTEXT := l_source_paav_rec.pricing_context;
1786 g_paav_tab(i).PRICING_ATTRIBUTE := l_source_paav_rec.pricing_attribute;
1787 g_paav_tab(i).PRICING_ATTR_VALUE_FROM := l_source_paav_rec.pricing_attr_value_from;
1788 g_paav_tab(i).PRICING_ATTR_VALUE_TO := l_source_paav_rec.pricing_attr_value_to;
1789 g_paav_tab(i).COMPARISON_OPERATOR := l_source_paav_rec.comparison_operator;
1790 --g_paav_tab(i).PROGRAM_APPLICATION_ID := l_source_paav_rec.program_application_id;
1791 --g_paav_tab(i).PROGRAM_ID := l_source_paav_rec.program_id;
1792 --g_paav_tab(i).PROGRAM_UPDATE_DATE := l_source_paav_rec.program_update_date;
1793 --g_paav_tab(i).REQUEST_ID := l_source_paav_rec.request_id;
1794 g_paav_tab(i).OBJECT_VERSION_NUMBER := l_source_paav_rec.object_version_number;
1795
1796 END LOOP;
1797 --------IF c_source_paav_rec%ROWCOUNT > 0 THEN
1798 --------l_no_data_found := FALSE;
1799 --------END IF;
1800 CLOSE c_source_paav_rec;
1801
1802
1803 -----------IF l_no_data_found THEN
1804 ---------OKC_UTIL.print_trace(4, 'END --> get_paav_tab: returned error- ');
1805 ---------- RAISE OKC_API.G_EXCEPTION_ERROR;
1806 ----------ELSE
1807 IF (l_debug = 'Y') THEN
1808 OKC_UTIL.print_trace(4, 'Output: PL/SQL global table- g_paav_tab');
1809 OKC_UTIL.print_trace(4, 'END --> get_paav_tab- ');
1810 END IF;
1811 ----------END IF;
1812
1813
1814 EXCEPTION
1815 WHEN OTHERS THEN
1816 IF (l_debug = 'Y') THEN
1817 OKC_UTIL.print_trace(4,SQLERRM);
1818 END IF;
1819 -- Bug#2320635
1820 OKC_API.set_message(G_APP_NAME,
1821 G_UNEXPECTED_ERROR,
1822 G_SQLCODE_TOKEN,
1823 SQLCODE,
1824 G_SQLERRM_TOKEN,
1825 SQLERRM);
1826 IF c_source_paav_rec%ISOPEN THEN
1827 CLOSE c_source_paav_rec;
1828 END IF;
1829 RAISE OKC_API.G_EXCEPTION_ERROR;
1830 END get_paav_tab;
1831
1832
1833 ----------------------------------------------------------------------
1834 -- PROCEDURE to get PRICE ATTRIBUTES information from ASO or ONT tables
1835 -- get_pavv_tab stores it's output in global PL/SQL table g_pavv_tab
1836 ----------------------------------------------------------------------
1837 -- Bug : 1686001 Changed references to ASO_QUOTE_HEADERS_ALL.QUOTE_HEADER_ID to OKX_QUOTE_HEADERS_V.ID1
1838 -- ASO_QUOTE_LINES_ALL.LINE_HEADER_ID to OKX_QUOTE_LINES_V.ID1
1839 PROCEDURE get_pavv_tab(
1840 p_chr_id IN OKC_K_HEADERS_B.ID%TYPE,
1841 p_cle_id IN OKC_K_LINES_B.ID%TYPE ,
1842
1843 p_o_flag IN VARCHAR2 ,
1844 p_ohr_id IN NUMBER ,
1845 p_ole_id IN NUMBER ,
1846
1847 p_q_flag IN VARCHAR2 ,
1848 p_qhr_id IN OKX_QUOTE_HEADERS_V.ID1%TYPE
1849 ,
1850 p_qle_id IN OKX_QUOTE_LINES_V.ID1%TYPE
1851 ) IS
1852
1853 l_no_data_found BOOLEAN := TRUE;
1854
1855 ------remove later l_aso_pavv_rec okx_qte_prc_atrbs_v%ROWTYPE;
1856 -------remove later l_ont_pavv_rec okx_ord_prc_atrbs_v%ROWTYPE;
1857 i BINARY_INTEGER := 0;
1858
1859 -- cursor to get okx_qte_prc_atrbs_v or
1860 -- okx_ord_prc_atrbs_v information
1861 -- Bug : 1686001 Changed references to ASO_QUOTE_HEADERS_ALL.QUOTE_HEADER_ID to OKX_QUOTE_HEADERS_V.ID1
1862 -- ASO_QUOTE_LINES_ALL.LINE_HEADER_ID to OKX_QUOTE_LINES_V.ID1
1863 CURSOR c_source_pavv_rec (cp_q_flag IN VARCHAR2,
1864 cp_qhr_id IN OKX_QUOTE_HEADERS_V.ID1%TYPE,
1865 cp_qle_id IN OKX_QUOTE_LINES_V.ID1%TYPE,
1866 cp_o_flag IN VARCHAR2,
1867 cp_ohr_id IN NUMBER,
1868 cp_ole_id IN NUMBER) IS
1869 --could be either ASO or ONT
1870 --only ONE of the following two queries in the union will be executed in
1871 --a call depending on which flag (p_q_flag or p_o_flag) is true
1872
1873 -- first query to get okx_qte_prc_atrbs_v information
1874 SELECT quote_header_id source_header_id,
1875 quote_line_id source_line_id,
1876 flex_title,
1877 pricing_context,
1878 pricing_attribute1,
1879 pricing_attribute2,
1880 pricing_attribute3,
1881 pricing_attribute4,
1882 pricing_attribute5,
1883 pricing_attribute6,
1884 pricing_attribute7,
1885 pricing_attribute8,
1886 pricing_attribute9,
1887 pricing_attribute10,
1888 pricing_attribute11,
1889 pricing_attribute12,
1890 pricing_attribute13,
1891 pricing_attribute14,
1892 pricing_attribute15,
1893 pricing_attribute16,
1894 pricing_attribute17,
1895 pricing_attribute18,
1896 pricing_attribute19,
1897 pricing_attribute20,
1898 pricing_attribute21,
1899 pricing_attribute22,
1900 pricing_attribute23,
1901 pricing_attribute24,
1902 pricing_attribute25,
1903 pricing_attribute26,
1904 pricing_attribute27,
1905 pricing_attribute28,
1906 pricing_attribute29,
1907 pricing_attribute30,
1908 pricing_attribute31,
1909 pricing_attribute32,
1910 pricing_attribute33,
1911 pricing_attribute34,
1912 pricing_attribute35,
1913 pricing_attribute36,
1914 pricing_attribute37,
1915 pricing_attribute38,
1916 pricing_attribute39,
1917 pricing_attribute40,
1918 pricing_attribute41,
1919 pricing_attribute42,
1920 pricing_attribute43,
1921 pricing_attribute44,
1922 pricing_attribute45,
1923 pricing_attribute46,
1924 pricing_attribute47,
1925 pricing_attribute48,
1926 pricing_attribute49,
1927 pricing_attribute50,
1928 pricing_attribute51,
1929 pricing_attribute52,
1930 pricing_attribute53,
1931 pricing_attribute54,
1932 pricing_attribute55,
1933 pricing_attribute56,
1934 pricing_attribute57,
1935 pricing_attribute58,
1936 pricing_attribute59,
1937 pricing_attribute60,
1938 pricing_attribute61,
1939 pricing_attribute62,
1940 pricing_attribute63,
1941 pricing_attribute64,
1942 pricing_attribute65,
1943 pricing_attribute66,
1944 pricing_attribute67,
1945 pricing_attribute68,
1946 pricing_attribute69,
1947 pricing_attribute70,
1948 pricing_attribute71,
1949 pricing_attribute72,
1950 pricing_attribute73,
1951 pricing_attribute74,
1952 pricing_attribute75,
1953 pricing_attribute76,
1954 pricing_attribute77,
1955 pricing_attribute78,
1956 pricing_attribute79,
1957 pricing_attribute80,
1958 pricing_attribute81,
1959 pricing_attribute82,
1960 pricing_attribute83,
1961 pricing_attribute84,
1962 pricing_attribute85,
1963 pricing_attribute86,
1964 pricing_attribute87,
1965 pricing_attribute88,
1966 pricing_attribute89,
1967 pricing_attribute90,
1968 pricing_attribute91,
1969 pricing_attribute92,
1970 pricing_attribute93,
1971 pricing_attribute94,
1972 pricing_attribute95,
1973 pricing_attribute96,
1974 pricing_attribute97,
1975 pricing_attribute98,
1976 pricing_attribute99,
1977 pricing_attribute100,
1978 context,
1979 attribute1,
1980 attribute2,
1981 attribute3,
1982 attribute4,
1983 attribute5,
1984 attribute6,
1985 attribute7,
1986 attribute8,
1987 attribute9,
1988 attribute10,
1989 attribute11,
1990 attribute12,
1991 attribute13,
1992 attribute14,
1993 attribute15,
1994 object_version_number
1995 FROM okx_qte_prc_atrbs_v
1996 WHERE cp_q_flag = OKC_API.G_TRUE
1997 AND quote_header_id = cp_qhr_id
1998 AND ((cp_qle_id = OKC_API.G_MISS_NUM AND quote_line_id IS NULL) OR
1999 (cp_qle_id <> OKC_API.G_MISS_NUM AND quote_line_id = cp_qle_id))
2000
2001 UNION ALL
2002
2003 -- second query to get okx_ord_prc_atrbs_v information
2004 SELECT header_id source_header_id,
2005 line_id source_line_id,
2006 flex_title,
2007 pricing_context,
2008 pricing_attribute1,
2009 pricing_attribute2,
2010 pricing_attribute3,
2011 pricing_attribute4,
2012 pricing_attribute5,
2013 pricing_attribute6,
2014 pricing_attribute7,
2015 pricing_attribute8,
2016 pricing_attribute9,
2017 pricing_attribute10,
2018 pricing_attribute11,
2019 pricing_attribute12,
2020 pricing_attribute13,
2021 pricing_attribute14,
2022 pricing_attribute15,
2023 pricing_attribute16,
2024 pricing_attribute17,
2025 pricing_attribute18,
2026 pricing_attribute19,
2027 pricing_attribute20,
2028 pricing_attribute21,
2029 pricing_attribute22,
2030 pricing_attribute23,
2031 pricing_attribute24,
2032 pricing_attribute25,
2033 pricing_attribute26,
2034 pricing_attribute27,
2035 pricing_attribute28,
2036 pricing_attribute29,
2037 pricing_attribute30,
2038 pricing_attribute31,
2039 pricing_attribute32,
2040 pricing_attribute33,
2041 pricing_attribute34,
2042 pricing_attribute35,
2043 pricing_attribute36,
2044 pricing_attribute37,
2045 pricing_attribute38,
2046 pricing_attribute39,
2047 pricing_attribute40,
2048 pricing_attribute41,
2049 pricing_attribute42,
2050 pricing_attribute43,
2051 pricing_attribute44,
2052 pricing_attribute45,
2053 pricing_attribute46,
2054 pricing_attribute47,
2055 pricing_attribute48,
2056 pricing_attribute49,
2057 pricing_attribute50,
2058 pricing_attribute51,
2059 pricing_attribute52,
2060 pricing_attribute53,
2061 pricing_attribute54,
2062 pricing_attribute55,
2063 pricing_attribute56,
2064 pricing_attribute57,
2065 pricing_attribute58,
2066 pricing_attribute59,
2067 pricing_attribute60,
2068 pricing_attribute61,
2069 pricing_attribute62,
2070 pricing_attribute63,
2071 pricing_attribute64,
2072 pricing_attribute65,
2073 pricing_attribute66,
2074 pricing_attribute67,
2075 pricing_attribute68,
2076 pricing_attribute69,
2077 pricing_attribute70,
2078 pricing_attribute71,
2079 pricing_attribute72,
2080 pricing_attribute73,
2081 pricing_attribute74,
2082 pricing_attribute75,
2083 pricing_attribute76,
2084 pricing_attribute77,
2085 pricing_attribute78,
2086 pricing_attribute79,
2087 pricing_attribute80,
2088 pricing_attribute81,
2089 pricing_attribute82,
2090 pricing_attribute83,
2091 pricing_attribute84,
2092 pricing_attribute85,
2093 pricing_attribute86,
2094 pricing_attribute87,
2095 pricing_attribute88,
2096 pricing_attribute89,
2097 pricing_attribute90,
2098 pricing_attribute91,
2099 pricing_attribute92,
2100 pricing_attribute93,
2101 pricing_attribute94,
2102 pricing_attribute95,
2103 pricing_attribute96,
2104 pricing_attribute97,
2105 pricing_attribute98,
2106 pricing_attribute99,
2107 pricing_attribute100,
2108 context,
2109 attribute1,
2110 attribute2,
2111 attribute3,
2112 attribute4,
2113 attribute5,
2114 attribute6,
2115 attribute7,
2116 attribute8,
2117 attribute9,
2118 attribute10,
2119 attribute11,
2120 attribute12,
2121 attribute13,
2122 attribute14,
2123 attribute15,
2124 TO_NUMBER(NULL) --object_version_number not present in order table
2125 FROM okx_ord_prc_atrbs_v
2126 WHERE cp_o_flag = OKC_API.G_TRUE
2127 AND header_id = cp_ohr_id
2128 AND ((cp_ole_id = OKC_API.G_MISS_NUM AND line_id IS NULL) OR
2129 (cp_ole_id <> OKC_API.G_MISS_NUM AND line_id = cp_ole_id));
2130
2131 l_source_pavv_rec c_source_pavv_rec%ROWTYPE;
2132
2133 BEGIN
2134
2135 IF (l_debug = 'Y') THEN
2136 OKC_UTIL.print_trace(4, 'START --> get_pavv_tab- ');
2137 OKC_UTIL.print_trace(4, 'Contract Id - '|| p_chr_id);
2138 OKC_UTIL.print_trace(4, 'Contract Line Id - '|| p_cle_id);
2139 OKC_UTIL.print_trace(4, 'Quote Id - '|| p_qhr_id);
2140 OKC_UTIL.print_trace(4, 'Quote Line Id - '|| p_qle_id);
2141 OKC_UTIL.print_trace(4, 'Order Id - '|| p_ohr_id);
2142 OKC_UTIL.print_trace(4, 'Order Line Id - '|| p_ole_id);
2143 END IF;
2144
2145
2146 IF (p_q_flag = OKC_API.G_TRUE AND
2147 p_qhr_id IS NOT NULL AND
2148 p_qhr_id <> OKC_API.G_MISS_NUM) THEN
2149 IF (l_debug = 'Y') THEN
2150 OKC_UTIL.print_trace(4, 'Processing quote pricing information for Quote Id - '|| p_qhr_id);
2151 END IF;
2152 ELSIF (p_o_flag = OKC_API.G_TRUE AND
2153 p_ohr_id IS NOT NULL AND
2154 p_ohr_id <> OKC_API.G_MISS_NUM) THEN
2155 IF (l_debug = 'Y') THEN
2156 OKC_UTIL.print_trace(4, 'Processing order pricing information for Order Id - '|| p_ohr_id);
2157 END IF;
2158 END IF;
2159
2160
2161 OPEN c_source_pavv_rec (cp_q_flag => p_q_flag,
2162 cp_qhr_id => p_qhr_id,
2163 cp_qle_id => p_qle_id,
2164 cp_o_flag => p_o_flag,
2165 cp_ohr_id => p_ohr_id,
2166 cp_ole_id => p_ole_id); --ASO or ONT
2167 LOOP
2168 --use COUNT to keep adding to existing records, if any, in g_pavv_tab
2169 --otherwise if table empty, COUNT returns 0
2170 i := g_pavv_tab.COUNT + 1;
2171 FETCH c_source_pavv_rec INTO l_source_pavv_rec;
2172 EXIT WHEN c_source_pavv_rec%NOTFOUND;
2173
2174 -- map okx_qte_prc_atrbs_v or
2175 -- okx_ord_prc_atrbs_v to OKC_PRICE_ATT_VALUES
2176
2177 -- we don't need to enter the ID because it is automatically generated
2178
2179 IF l_source_pavv_rec.source_header_id IS NOT NULL THEN--quote or order
2180 g_pavv_tab(i).CHR_ID := p_chr_id;
2181 END IF;
2182 IF l_source_pavv_rec.source_line_id IS NOT NULL THEN --quote or order
2183 g_pavv_tab(i).CLE_ID := p_cle_id;
2184 END IF;
2185
2186 IF l_source_pavv_rec.flex_title IS NULL OR l_source_pavv_rec.flex_title = OKC_API.G_MISS_CHAR THEN
2187 IF l_source_pavv_rec.pricing_context IS NOT NULL OR l_source_pavv_rec.pricing_context <> OKC_API.G_MISS_CHAR THEN
2188 g_pavv_tab(i).FLEX_TITLE := 'QP_ATTR_DEFNS_PRICING';
2189 END IF;
2190 IF l_source_pavv_rec.CONTEXT IS NOT NULL OR l_source_pavv_rec.CONTEXT <> OKC_API.G_MISS_CHAR THEN
2191 g_pavv_tab(i).FLEX_TITLE := 'QP_ATTR_DEFNS_QUALIFIER';
2192 END IF;
2193 ELSE
2194 g_pavv_tab(i).FLEX_TITLE := l_source_pavv_rec.flex_title;
2195 END IF;
2196
2197
2198 g_pavv_tab(i).PRICING_CONTEXT := l_source_pavv_rec.pricing_context;
2199 g_pavv_tab(i).PRICING_ATTRIBUTE1 := l_source_pavv_rec.pricing_attribute1;
2200 g_pavv_tab(i).PRICING_ATTRIBUTE2 := l_source_pavv_rec.pricing_attribute2;
2201 g_pavv_tab(i).PRICING_ATTRIBUTE3 := l_source_pavv_rec.pricing_attribute3;
2202 g_pavv_tab(i).PRICING_ATTRIBUTE4 := l_source_pavv_rec.pricing_attribute4;
2203 g_pavv_tab(i).PRICING_ATTRIBUTE5 := l_source_pavv_rec.PRICING_ATTRIBUTE5;
2204 g_pavv_tab(i).PRICING_ATTRIBUTE6 := l_source_pavv_rec.PRICING_ATTRIBUTE6;
2205 g_pavv_tab(i).PRICING_ATTRIBUTE7 := l_source_pavv_rec.PRICING_ATTRIBUTE7;
2206 g_pavv_tab(i).PRICING_ATTRIBUTE8 := l_source_pavv_rec.PRICING_ATTRIBUTE8;
2207 g_pavv_tab(i).PRICING_ATTRIBUTE9 := l_source_pavv_rec.PRICING_ATTRIBUTE9;
2208 g_pavv_tab(i).PRICING_ATTRIBUTE10 := l_source_pavv_rec.PRICING_ATTRIBUTE10;
2209 g_pavv_tab(i).PRICING_ATTRIBUTE11 := l_source_pavv_rec.PRICING_ATTRIBUTE11;
2210 g_pavv_tab(i).PRICING_ATTRIBUTE12 := l_source_pavv_rec.PRICING_ATTRIBUTE12;
2211 g_pavv_tab(i).PRICING_ATTRIBUTE13 := l_source_pavv_rec.PRICING_ATTRIBUTE13;
2212 g_pavv_tab(i).PRICING_ATTRIBUTE14 := l_source_pavv_rec.PRICING_ATTRIBUTE14;
2213 g_pavv_tab(i).PRICING_ATTRIBUTE15 := l_source_pavv_rec.PRICING_ATTRIBUTE15;
2214 g_pavv_tab(i).PRICING_ATTRIBUTE16 := l_source_pavv_rec.PRICING_ATTRIBUTE16;
2215 g_pavv_tab(i).PRICING_ATTRIBUTE17 := l_source_pavv_rec.PRICING_ATTRIBUTE17;
2216 g_pavv_tab(i).PRICING_ATTRIBUTE18 := l_source_pavv_rec.PRICING_ATTRIBUTE18;
2217 g_pavv_tab(i).PRICING_ATTRIBUTE19 := l_source_pavv_rec.PRICING_ATTRIBUTE19;
2218 g_pavv_tab(i).PRICING_ATTRIBUTE20 := l_source_pavv_rec.PRICING_ATTRIBUTE20;
2219 g_pavv_tab(i).PRICING_ATTRIBUTE21 := l_source_pavv_rec.PRICING_ATTRIBUTE21;
2220 g_pavv_tab(i).PRICING_ATTRIBUTE22 := l_source_pavv_rec.PRICING_ATTRIBUTE22;
2221 g_pavv_tab(i).PRICING_ATTRIBUTE23 := l_source_pavv_rec.PRICING_ATTRIBUTE23;
2222 g_pavv_tab(i).PRICING_ATTRIBUTE24 := l_source_pavv_rec.PRICING_ATTRIBUTE24;
2223 g_pavv_tab(i).PRICING_ATTRIBUTE25 := l_source_pavv_rec.PRICING_ATTRIBUTE25;
2224 g_pavv_tab(i).PRICING_ATTRIBUTE26 := l_source_pavv_rec.PRICING_ATTRIBUTE26;
2225 g_pavv_tab(i).PRICING_ATTRIBUTE27 := l_source_pavv_rec.PRICING_ATTRIBUTE27;
2226 g_pavv_tab(i).PRICING_ATTRIBUTE28 := l_source_pavv_rec.PRICING_ATTRIBUTE28;
2227 g_pavv_tab(i).PRICING_ATTRIBUTE29 := l_source_pavv_rec.PRICING_ATTRIBUTE29;
2228 g_pavv_tab(i).PRICING_ATTRIBUTE30 := l_source_pavv_rec.PRICING_ATTRIBUTE30;
2229 g_pavv_tab(i).PRICING_ATTRIBUTE31 := l_source_pavv_rec.PRICING_ATTRIBUTE31;
2230 g_pavv_tab(i).PRICING_ATTRIBUTE32 := l_source_pavv_rec.PRICING_ATTRIBUTE32;
2231 g_pavv_tab(i).PRICING_ATTRIBUTE33 := l_source_pavv_rec.PRICING_ATTRIBUTE33;
2232 g_pavv_tab(i).PRICING_ATTRIBUTE34 := l_source_pavv_rec.PRICING_ATTRIBUTE34;
2233 g_pavv_tab(i).PRICING_ATTRIBUTE35 := l_source_pavv_rec.PRICING_ATTRIBUTE35;
2234 g_pavv_tab(i).PRICING_ATTRIBUTE36 := l_source_pavv_rec.PRICING_ATTRIBUTE36;
2235 g_pavv_tab(i).PRICING_ATTRIBUTE37 := l_source_pavv_rec.PRICING_ATTRIBUTE37;
2236 g_pavv_tab(i).PRICING_ATTRIBUTE38 := l_source_pavv_rec.PRICING_ATTRIBUTE38;
2237 g_pavv_tab(i).PRICING_ATTRIBUTE39 := l_source_pavv_rec.PRICING_ATTRIBUTE39;
2238 g_pavv_tab(i).PRICING_ATTRIBUTE40 := l_source_pavv_rec.PRICING_ATTRIBUTE40;
2239 g_pavv_tab(i).PRICING_ATTRIBUTE41 := l_source_pavv_rec.PRICING_ATTRIBUTE41;
2240 g_pavv_tab(i).PRICING_ATTRIBUTE42 := l_source_pavv_rec.PRICING_ATTRIBUTE42;
2241 g_pavv_tab(i).PRICING_ATTRIBUTE43 := l_source_pavv_rec.PRICING_ATTRIBUTE43;
2242 g_pavv_tab(i).PRICING_ATTRIBUTE44 := l_source_pavv_rec.PRICING_ATTRIBUTE44;
2243 g_pavv_tab(i).PRICING_ATTRIBUTE45 := l_source_pavv_rec.PRICING_ATTRIBUTE45;
2244 g_pavv_tab(i).PRICING_ATTRIBUTE46 := l_source_pavv_rec.PRICING_ATTRIBUTE46;
2245 g_pavv_tab(i).PRICING_ATTRIBUTE47 := l_source_pavv_rec.PRICING_ATTRIBUTE47;
2246 g_pavv_tab(i).PRICING_ATTRIBUTE48 := l_source_pavv_rec.PRICING_ATTRIBUTE48;
2247 g_pavv_tab(i).PRICING_ATTRIBUTE49 := l_source_pavv_rec.PRICING_ATTRIBUTE49;
2248 g_pavv_tab(i).PRICING_ATTRIBUTE50 := l_source_pavv_rec.PRICING_ATTRIBUTE50;
2249 g_pavv_tab(i).PRICING_ATTRIBUTE51 := l_source_pavv_rec.PRICING_ATTRIBUTE51;
2250 g_pavv_tab(i).PRICING_ATTRIBUTE52 := l_source_pavv_rec.PRICING_ATTRIBUTE52;
2251 g_pavv_tab(i).PRICING_ATTRIBUTE53 := l_source_pavv_rec.PRICING_ATTRIBUTE53;
2252 g_pavv_tab(i).PRICING_ATTRIBUTE54 := l_source_pavv_rec.PRICING_ATTRIBUTE54;
2253 g_pavv_tab(i).PRICING_ATTRIBUTE55 := l_source_pavv_rec.PRICING_ATTRIBUTE55;
2254 g_pavv_tab(i).PRICING_ATTRIBUTE56 := l_source_pavv_rec.PRICING_ATTRIBUTE56;
2255 g_pavv_tab(i).PRICING_ATTRIBUTE57 := l_source_pavv_rec.PRICING_ATTRIBUTE57;
2256 g_pavv_tab(i).PRICING_ATTRIBUTE58 := l_source_pavv_rec.PRICING_ATTRIBUTE58;
2257 g_pavv_tab(i).PRICING_ATTRIBUTE59 := l_source_pavv_rec.PRICING_ATTRIBUTE59;
2258 g_pavv_tab(i).PRICING_ATTRIBUTE60 := l_source_pavv_rec.PRICING_ATTRIBUTE60;
2259 g_pavv_tab(i).PRICING_ATTRIBUTE61 := l_source_pavv_rec.PRICING_ATTRIBUTE61;
2260 g_pavv_tab(i).PRICING_ATTRIBUTE62 := l_source_pavv_rec.PRICING_ATTRIBUTE62;
2261 g_pavv_tab(i).PRICING_ATTRIBUTE63 := l_source_pavv_rec.PRICING_ATTRIBUTE63;
2262 g_pavv_tab(i).PRICING_ATTRIBUTE64 := l_source_pavv_rec.PRICING_ATTRIBUTE64;
2263 g_pavv_tab(i).PRICING_ATTRIBUTE65 := l_source_pavv_rec.PRICING_ATTRIBUTE65;
2264 g_pavv_tab(i).PRICING_ATTRIBUTE66 := l_source_pavv_rec.PRICING_ATTRIBUTE66;
2265 g_pavv_tab(i).PRICING_ATTRIBUTE67 := l_source_pavv_rec.PRICING_ATTRIBUTE67;
2266 g_pavv_tab(i).PRICING_ATTRIBUTE68 := l_source_pavv_rec.PRICING_ATTRIBUTE68;
2267 g_pavv_tab(i).PRICING_ATTRIBUTE69 := l_source_pavv_rec.PRICING_ATTRIBUTE69;
2268 g_pavv_tab(i).PRICING_ATTRIBUTE70 := l_source_pavv_rec.PRICING_ATTRIBUTE70;
2269 g_pavv_tab(i).PRICING_ATTRIBUTE71 := l_source_pavv_rec.PRICING_ATTRIBUTE71;
2270 g_pavv_tab(i).PRICING_ATTRIBUTE72 := l_source_pavv_rec.PRICING_ATTRIBUTE72;
2271 g_pavv_tab(i).PRICING_ATTRIBUTE73 := l_source_pavv_rec.PRICING_ATTRIBUTE73;
2272 g_pavv_tab(i).PRICING_ATTRIBUTE74 := l_source_pavv_rec.PRICING_ATTRIBUTE74;
2273 g_pavv_tab(i).PRICING_ATTRIBUTE75 := l_source_pavv_rec.PRICING_ATTRIBUTE75;
2274 g_pavv_tab(i).PRICING_ATTRIBUTE76 := l_source_pavv_rec.PRICING_ATTRIBUTE76;
2275 g_pavv_tab(i).PRICING_ATTRIBUTE77 := l_source_pavv_rec.PRICING_ATTRIBUTE77;
2276 g_pavv_tab(i).PRICING_ATTRIBUTE78 := l_source_pavv_rec.PRICING_ATTRIBUTE78;
2277 g_pavv_tab(i).PRICING_ATTRIBUTE79 := l_source_pavv_rec.PRICING_ATTRIBUTE79;
2278 g_pavv_tab(i).PRICING_ATTRIBUTE80 := l_source_pavv_rec.PRICING_ATTRIBUTE80;
2279 g_pavv_tab(i).PRICING_ATTRIBUTE81 := l_source_pavv_rec.PRICING_ATTRIBUTE81;
2280 g_pavv_tab(i).PRICING_ATTRIBUTE82 := l_source_pavv_rec.PRICING_ATTRIBUTE82;
2281 g_pavv_tab(i).PRICING_ATTRIBUTE83 := l_source_pavv_rec.PRICING_ATTRIBUTE83;
2282 g_pavv_tab(i).PRICING_ATTRIBUTE84 := l_source_pavv_rec.PRICING_ATTRIBUTE84;
2283 g_pavv_tab(i).PRICING_ATTRIBUTE85 := l_source_pavv_rec.PRICING_ATTRIBUTE85;
2284 g_pavv_tab(i).PRICING_ATTRIBUTE86 := l_source_pavv_rec.PRICING_ATTRIBUTE86;
2285 g_pavv_tab(i).PRICING_ATTRIBUTE87 := l_source_pavv_rec.PRICING_ATTRIBUTE87;
2286 g_pavv_tab(i).PRICING_ATTRIBUTE88 := l_source_pavv_rec.PRICING_ATTRIBUTE88;
2287 g_pavv_tab(i).PRICING_ATTRIBUTE89 := l_source_pavv_rec.PRICING_ATTRIBUTE89;
2288 g_pavv_tab(i).PRICING_ATTRIBUTE90 := l_source_pavv_rec.PRICING_ATTRIBUTE90;
2289 g_pavv_tab(i).PRICING_ATTRIBUTE91 := l_source_pavv_rec.PRICING_ATTRIBUTE91;
2290 g_pavv_tab(i).PRICING_ATTRIBUTE92 := l_source_pavv_rec.PRICING_ATTRIBUTE92;
2291 g_pavv_tab(i).PRICING_ATTRIBUTE93 := l_source_pavv_rec.PRICING_ATTRIBUTE93;
2292 g_pavv_tab(i).PRICING_ATTRIBUTE94 := l_source_pavv_rec.PRICING_ATTRIBUTE94;
2293 g_pavv_tab(i).PRICING_ATTRIBUTE95 := l_source_pavv_rec.PRICING_ATTRIBUTE95;
2294 g_pavv_tab(i).PRICING_ATTRIBUTE96 := l_source_pavv_rec.PRICING_ATTRIBUTE96;
2295 g_pavv_tab(i).PRICING_ATTRIBUTE97 := l_source_pavv_rec.PRICING_ATTRIBUTE97;
2296 g_pavv_tab(i).PRICING_ATTRIBUTE98 := l_source_pavv_rec.PRICING_ATTRIBUTE98;
2297 g_pavv_tab(i).PRICING_ATTRIBUTE99 := l_source_pavv_rec.PRICING_ATTRIBUTE99;
2298 g_pavv_tab(i).PRICING_ATTRIBUTE100 := l_source_pavv_rec.PRICING_ATTRIBUTE100;
2299 g_pavv_tab(i).QUALIFIER_CONTEXT := l_source_pavv_rec.CONTEXT;
2300 g_pavv_tab(i).QUALIFIER_ATTRIBUTE1 := l_source_pavv_rec.ATTRIBUTE1;
2301 g_pavv_tab(i).QUALIFIER_ATTRIBUTE2 := l_source_pavv_rec.ATTRIBUTE2;
2302 g_pavv_tab(i).QUALIFIER_ATTRIBUTE3 := l_source_pavv_rec.ATTRIBUTE3;
2303 g_pavv_tab(i).QUALIFIER_ATTRIBUTE4 := l_source_pavv_rec.ATTRIBUTE4;
2304 g_pavv_tab(i).QUALIFIER_ATTRIBUTE5 := l_source_pavv_rec.ATTRIBUTE5;
2305 g_pavv_tab(i).QUALIFIER_ATTRIBUTE6 := l_source_pavv_rec.ATTRIBUTE6;
2306 g_pavv_tab(i).QUALIFIER_ATTRIBUTE7 := l_source_pavv_rec.ATTRIBUTE7;
2307 g_pavv_tab(i).QUALIFIER_ATTRIBUTE8 := l_source_pavv_rec.ATTRIBUTE8;
2308 g_pavv_tab(i).QUALIFIER_ATTRIBUTE9 := l_source_pavv_rec.ATTRIBUTE9;
2309 g_pavv_tab(i).QUALIFIER_ATTRIBUTE10 := l_source_pavv_rec.ATTRIBUTE10;
2310 g_pavv_tab(i).QUALIFIER_ATTRIBUTE11 := l_source_pavv_rec.ATTRIBUTE11;
2311 g_pavv_tab(i).QUALIFIER_ATTRIBUTE12 := l_source_pavv_rec.ATTRIBUTE12;
2312 g_pavv_tab(i).QUALIFIER_ATTRIBUTE13 := l_source_pavv_rec.ATTRIBUTE13;
2313 g_pavv_tab(i).QUALIFIER_ATTRIBUTE14 := l_source_pavv_rec.ATTRIBUTE14;
2314 g_pavv_tab(i).QUALIFIER_ATTRIBUTE15 := l_source_pavv_rec.ATTRIBUTE15;
2315 g_pavv_tab(i).OBJECT_VERSION_NUMBER := l_source_pavv_rec.OBJECT_VERSION_NUMBER;
2316 END LOOP;
2317 IF c_source_pavv_rec%ROWCOUNT > 0 THEN
2318 l_no_data_found := FALSE;
2319 END IF;
2320 CLOSE c_source_pavv_rec;
2321
2322
2323
2324 --IF l_no_data_found THEN
2325 -- OKC_UTIL.print_trace(4, 'END --> get_pavv_tab: returned error- ');
2326 -- RAISE OKC_API.G_EXCEPTION_ERROR;
2327 --ELSE
2328 IF (l_debug = 'Y') THEN
2329 OKC_UTIL.print_trace(4, 'Output: PL/SQL global table- g_pavv_tab');
2330 OKC_UTIL.print_trace(4, 'END --> get_pavv_tab- ');
2331 END IF;
2332 --END IF;
2333
2334 EXCEPTION
2335 WHEN OTHERS THEN
2336 IF (l_debug = 'Y') THEN
2337 OKC_UTIL.print_trace(4,SQLERRM);
2338 END IF;
2339 -- Bug#2320635
2340 OKC_API.set_message(G_APP_NAME,
2341 G_UNEXPECTED_ERROR,
2342 G_SQLCODE_TOKEN,
2343 SQLCODE,
2344 G_SQLERRM_TOKEN,
2345 SQLERRM);
2346 IF c_source_pavv_rec%ISOPEN THEN
2347 CLOSE c_source_pavv_rec;
2348 END IF;
2349 RAISE OKC_API.G_EXCEPTION_ERROR;
2350 END get_pavv_tab;
2351 ----------------------------------------------------------------------------
2352 /************************************************************************
2353 ************************************************************************
2354 END OF QTK or OTK PRICING INFORMATION CREATION
2355 ************************************************************************
2356 ***********************************************************************/
2357
2358
2359
2360 -- =========================================================================
2361 -- =========================================================================
2362 -- START OF KTQ or KTO PRICING INFORMATION CREATION
2363 -- or UPDATE
2364 -- =========================================================================
2365 -- =========================================================================
2366
2367 ---------------------------------------------------------------------------
2368 PROCEDURE get_price_adj(p_chr_id IN NUMBER,
2369 p_cle_id IN NUMBER,
2370 --
2371 p_qhr_id IN NUMBER,
2372 p_qle_id IN NUMBER,
2373 p_q_flag IN VARCHAR2,
2374 --
2375 p_ohr_id IN NUMBER,
2376 p_ole_id IN NUMBER,
2377 p_o_flag IN VARCHAR2,
2378 --
2379 p_level IN VARCHAR2,
2380 --
2381 p_nqhr_id IN NUMBER,
2382 p_nqle_idx IN NUMBER,
2383 --
2384 x_k_price_adj_tab OUT NOCOPY k_price_adj_tab_type,
2385 x_price_adj_tab OUT NOCOPY ASO_QUOTE_PUB.price_adj_tbl_type) IS
2386
2387 --
2388 -- Cursor to identify the price adjustments which have to be deleted taking into
2389 -- account those marked as to be updated or created.
2390 --
2391 --
2392 CURSOR c_price_adj( b_q_flag VARCHAR,b_qh_id NUMBER,b_ql_id NUMBER,
2393 b_o_flag VARCHAR, b_oh_id NUMBER, b_ol_id NUMBER) IS
2394 SELECT
2395 qpadj.PRICE_ADJUSTMENT_ID -- quote(header or line) price adj ID
2396 FROM
2397 OKX_QTE_PRC_ADJMNTS_V qpadj
2398 WHERE
2399 b_q_flag = OKC_API.G_TRUE
2400 AND qpadj.quote_header_id = b_qh_id
2401 AND ((b_ql_id IS NULL AND qpadj.quote_line_id IS NULL) OR
2402 (b_ql_id IS NOT NULL AND qpadj.quote_line_id=b_ql_id))
2403
2404 UNION
2405
2406 SELECT
2407 PRICE_ADJUSTMENT_ID -- quote(header or line) price adj ID
2408 FROM
2409 OKX_ORD_PRC_ADJMNTS_V opadj
2410 WHERE
2411 b_o_flag = OKC_API.G_TRUE
2412 AND opadj.header_id = b_oh_id
2413 AND ((b_ol_id IS NULL AND opadj.line_id IS NULL) OR
2414 (b_ol_id IS NOT NULL AND opadj.line_id=b_ol_id));
2415
2416 --
2417 -- Cursor to identify the quote price adjustments which have to be created,updated or deleted
2418 --
2419
2420 CURSOR c_k_price_adj( b_kh_id NUMBER,b_kl_id NUMBER,
2421 b_q_flag VARCHAR,b_qh_id NUMBER,b_ql_id NUMBER,
2422 b_o_flag VARCHAR,b_oh_id NUMBER,b_ol_id NUMBER) IS
2423 SELECT
2424 DECODE(qpadj.modifier_header_id,NULL,g_aso_op_code_create,
2425 DECODE(qpadj.modifier_line_id,NULL,g_aso_op_code_create,
2426 DECODE(qpadj.modifier_line_type_code,NULL,
2427 g_aso_op_code_create,g_aso_op_code_update)
2428 )
2429 ) OPERATION_CODE,
2430 qpadj.PRICE_ADJUSTMENT_ID, -- quote(header or line) price adj ID
2431 kpadj.id, -- contract(header or line) price adj ID
2432 kpadj.pat_id,
2433 kpadj.chr_id,
2434 kpadj.cle_id,
2435 kpadj.modified_from,
2436 kpadj.modified_to,
2437 kpadj.modifier_mechanism_type_code,
2438 kpadj.operand,
2439 kpadj.arithmetic_operator,
2440 kpadj.automatic_flag,
2441 kpadj.update_allowed,
2442 kpadj.updated_flag,
2443 kpadj.applied_flag,
2444 kpadj.on_invoice_flag,
2445 kpadj.pricing_phase_id,
2446 kpadj.context,
2447 kpadj.attribute1,
2448 kpadj.attribute2,
2449 kpadj.attribute3,
2450 kpadj.attribute4,
2451 kpadj.attribute5,
2452 kpadj.attribute6,
2453 kpadj.attribute7,
2454 kpadj.attribute8,
2455 kpadj.attribute9,
2456 kpadj.attribute10,
2457 kpadj.attribute11,
2458 kpadj.attribute12,
2459 kpadj.attribute13,
2460 kpadj.attribute14,
2461 kpadj.attribute15,
2462 kpadj.list_header_id,
2463 kpadj.list_line_id,
2464 kpadj.list_line_type_code,
2465 kpadj.change_reason_code,
2466 kpadj.change_reason_text,
2467 kpadj.estimated_flag,
2468 kpadj.adjusted_amount,
2469 kpadj.charge_type_code,
2470 kpadj.charge_subtype_code,
2471 kpadj.range_break_quantity,
2472 kpadj.accrual_conversion_rate,
2473 kpadj.pricing_group_sequence,
2474 kpadj.accrual_flag,
2475 kpadj.list_line_no,
2476 kpadj.source_system_code,
2477 kpadj.benefit_qty,
2478 kpadj.benefit_uom_code,
2479 kpadj.expiration_date,
2480 kpadj.modifier_level_code,
2481 kpadj.price_break_type_code,
2482 kpadj.substitution_attribute,
2483 kpadj.proration_type_code,
2484 kpadj.include_on_returns_flag,
2485 kpadj.rebate_transaction_type_code,
2486 kpadj.creation_date
2487 FROM
2488 OKC_PRICE_ADJUSTMENTS kpadj,
2489 OKX_QTE_PRC_ADJMNTS_V qpadj
2490 WHERE
2491 b_q_flag = OKC_API.g_true
2492 AND (kpadj.chr_id = b_kh_id)
2493 AND ((b_kl_id IS NULL and kpadj.cle_id IS NULL ) OR (b_kl_id IS NOT NULL AND kpadj.cle_id = b_kl_id))
2494 --
2495 AND (qpadj.quote_header_id(+) = b_qh_id)
2496 --
2497 AND NVL(qpadj.quote_line_id(+),0) = NVL(b_ql_id,0)
2498 --
2499 AND qpadj.modifier_header_id(+) = kpadj.list_header_id
2500 AND qpadj.modifier_line_id(+) = kpadj.list_line_id
2501 AND qpadj.modifier_line_type_code(+) = kpadj.list_line_type_code
2502 UNION
2503 SELECT
2504 DECODE(opadj.list_header_id,NULL,g_aso_op_code_create,
2505 DECODE(opadj.list_line_id,NULL,g_aso_op_code_create,
2506 DECODE(opadj.list_line_type_code,NULL,
2507 g_aso_op_code_create,g_aso_op_code_update)
2508 )
2509 ) OPERATION_CODE,
2510 opadj.PRICE_ADJUSTMENT_ID, -- order(header or line) price adj ID
2511 kpadj.id, -- contract(header or line) price adj ID
2512 kpadj.pat_id,
2513 kpadj.chr_id,
2514 kpadj.cle_id,
2515 kpadj.modified_from,
2516 kpadj.modified_to,
2517 kpadj.modifier_mechanism_type_code,
2518 kpadj.operand,
2519 kpadj.arithmetic_operator,
2520 kpadj.automatic_flag,
2521 kpadj.update_allowed,
2522 kpadj.updated_flag,
2523 kpadj.applied_flag,
2524 kpadj.on_invoice_flag,
2525 kpadj.pricing_phase_id,
2526 kpadj.context,
2527 kpadj.attribute1,
2528 kpadj.attribute2,
2529 kpadj.attribute3,
2530 kpadj.attribute4,
2531 kpadj.attribute5,
2532 kpadj.attribute6,
2533 kpadj.attribute7,
2534 kpadj.attribute8,
2535 kpadj.attribute9,
2536 kpadj.attribute10,
2537 kpadj.attribute11,
2538 kpadj.attribute12,
2539 kpadj.attribute13,
2540 kpadj.attribute14,
2541 kpadj.attribute15,
2542 kpadj.list_header_id,
2543 kpadj.list_line_id,
2544 kpadj.list_line_type_code,
2545 kpadj.change_reason_code,
2546 kpadj.change_reason_text,
2547 kpadj.estimated_flag,
2548 kpadj.adjusted_amount,
2549 kpadj.charge_type_code,
2550 kpadj.charge_subtype_code,
2551 kpadj.range_break_quantity,
2552 kpadj.accrual_conversion_rate,
2553 kpadj.pricing_group_sequence,
2554 kpadj.accrual_flag,
2555 kpadj.list_line_no,
2556 kpadj.source_system_code,
2557 kpadj.benefit_qty,
2558 kpadj.benefit_uom_code,
2559 kpadj.expiration_date,
2560 kpadj.modifier_level_code,
2561 kpadj.price_break_type_code,
2562 kpadj.substitution_attribute,
2563 kpadj.proration_type_code,
2564 kpadj.include_on_returns_flag,
2565 kpadj.rebate_transaction_type_code,
2566 kpadj.creation_date
2567 FROM
2568 OKC_PRICE_ADJUSTMENTS kpadj,
2569 OKX_ORD_PRC_ADJMNTS_V opadj
2570 WHERE
2571 b_o_flag = OKC_API.g_true
2572 AND (kpadj.chr_id = b_kh_id)
2573 AND ((b_kl_id IS NULL and kpadj.cle_id IS NULL ) OR (b_kl_id IS NOT NULL AND kpadj.cle_id = b_kl_id))
2574 --
2575 AND (opadj.header_id(+) = b_oh_id)
2576 --
2577 AND NVL(opadj.line_id(+),0) = NVL(b_ol_id,0)
2578 --
2579 AND opadj.list_header_id(+) = kpadj.list_header_id
2580 AND opadj.list_line_id(+) = kpadj.list_line_id
2581 AND opadj.list_line_type_code(+) = kpadj.list_line_type_code
2582 --
2583 ORDER BY
2584 1, -- kpadj.operation_code, -- CREATE, UPDATE
2585 34, -- kpadj.list_header_id,
2586 35, -- kpadj.list_line_id,
2587 36, -- kpadj.list_line_type_code,
2588 3, -- kpadj.id,
2589 58; -- kpadj.creation_date;
2590
2591
2592 -- Variables
2593 --
2594 l_prec_okc_price_adj_id okc_price_adjustments.id%TYPE ;
2595 l_prec_prc_adj_procesd VARCHAR2(1) := OKC_API.G_FALSE;
2596 g_miss_price_adj_rec c_k_price_adj%ROWTYPE;
2597 l_prec_price_adj_rec c_k_price_adj%ROWTYPE := g_miss_price_adj_rec;
2598 l_price_adj_insert VARCHAR2(1) := OKC_API.G_TRUE;
2599
2600 l_price_adj_rec ASO_QUOTE_PUB.price_adj_rec_type;
2601 l_price_adj_tab ASO_QUOTE_PUB.price_adj_tbl_type;
2602 l_k_tmp_price_adj_tab k_price_adj_tab_type;
2603
2604 --
2605 -- Variables to keep track of count for the l_price_adj_tab and
2606 -- l_k_tmp_price_adj_tab tables
2607 --
2608 x BINARY_INTEGER;
2609 y BINARY_INTEGER;
2610
2611 BEGIN
2612
2613 IF (l_debug = 'Y') THEN
2614 okc_util.print_trace(1,'---------------------------------');
2615 okc_util.print_trace(1,'>> start : Get price adjustments ');
2616 okc_util.print_trace(1,'---------------------------------');
2617 END IF;
2618
2619 --
2620 -- housekeeping
2621 --
2622 l_price_adj_tab.DELETE;
2623 l_k_tmp_price_adj_tab.DELETE;
2624
2625 x_k_price_adj_tab.DELETE;
2626 x_price_adj_tab.DELETE;
2627
2628 x := l_price_adj_tab.COUNT;
2629 y := l_k_tmp_price_adj_tab.COUNT;
2630
2631 IF x = 0 THEN
2632 x:=x+1;
2633 END IF;
2634
2635 IF y = 0 THEN
2636 y:=y+1;
2637 END IF;
2638 --
2639 -- Fill in the l_price_adj_tab variable with price adjustment to be created or updated.
2640 --
2641 ----------------------------------------------------------------------------------
2642 -- Display the price adjustment records
2643
2644 -- IF p_level = 'L' THEN
2645 IF (l_debug = 'Y') THEN
2646 okc_util.print_trace(1,'*********************************************');
2647 END IF;
2648 FOR price_adj_rec IN c_k_price_adj(p_chr_id, p_cle_id,
2649 p_q_flag,p_qhr_id ,p_qle_id,
2650 p_o_flag,p_ohr_id,p_ole_id) LOOP
2651 IF c_k_price_adj%FOUND THEN
2652 IF (l_debug = 'Y') THEN
2653 okc_util.print_trace(1,' ');
2654 okc_util.print_trace(1,'---------------------------------------------');
2655 okc_util.print_trace(1,'Values from c_k_price_adj cursor - contract price adjustments');
2656 okc_util.print_trace(1,'operation code '||price_adj_rec.operation_code);
2657 okc_util.print_trace(1,'quote price adj id '||price_adj_rec.price_adjustment_id);
2658 okc_util.print_trace(1,'prc adj ID '||price_adj_rec.id);
2659 okc_util.print_trace(1,'---------------------------------------------');
2660 okc_util.print_trace(1,' ');
2661 END IF;
2662 END IF;
2663
2664 END LOOP;
2665 -- END IF;
2666 -----------------------------------------------------------------------------------------------
2667 FOR price_adj_rec IN c_k_price_adj(p_chr_id, p_cle_id,
2668 p_q_flag,p_qhr_id ,p_qle_id,
2669 p_o_flag,p_ohr_id,p_ole_id) LOOP
2670 l_price_adj_insert := OKC_API.G_TRUE;
2671
2672 IF price_adj_rec.operation_code = g_aso_op_code_create THEN
2673
2674 IF (l_debug = 'Y') THEN
2675 okc_util.print_trace(1,'step 1-1 operation code = '||price_adj_rec.operation_code);
2676 END IF;
2677
2678 -- Populate l_price_adj_rec with infomation from price_adj_rec;
2679
2680
2681 l_price_adj_rec.operation_code := price_adj_rec.operation_code;
2682 l_price_adj_rec.price_adjustment_id := price_adj_rec.price_adjustment_id;
2683 l_price_adj_rec.modifier_mechanism_type_code := price_adj_rec.modifier_mechanism_type_code;
2684 l_price_adj_rec.modified_from := price_adj_rec.modified_from;
2685 l_price_adj_rec.modified_to := price_adj_rec.modified_to;
2686 l_price_adj_rec.operand := price_adj_rec.operand;
2687 l_price_adj_rec.arithmetic_operator := price_adj_rec.arithmetic_operator;
2688 l_price_adj_rec.automatic_flag := price_adj_rec.automatic_flag;
2689 l_price_adj_rec.update_allowable_flag := price_adj_rec.update_allowed;
2690 l_price_adj_rec.updated_flag := price_adj_rec.updated_flag;
2691 l_price_adj_rec.applied_flag := price_adj_rec.applied_flag;
2692 l_price_adj_rec.on_invoice_flag := price_adj_rec.on_invoice_flag;
2693 l_price_adj_rec.pricing_phase_id := price_adj_rec.pricing_phase_id;
2694 l_price_adj_rec.attribute_category := price_adj_rec.context;
2695 l_price_adj_rec.attribute1 := price_adj_rec.attribute1;
2696 l_price_adj_rec.attribute2 := price_adj_rec.attribute2;
2697 l_price_adj_rec.attribute3 := price_adj_rec.attribute3;
2698 l_price_adj_rec.attribute4 := price_adj_rec.attribute4;
2699 l_price_adj_rec.attribute5 := price_adj_rec.attribute5;
2700 l_price_adj_rec.attribute6 := price_adj_rec.attribute6;
2701 l_price_adj_rec.attribute7 := price_adj_rec.attribute7;
2702 l_price_adj_rec.attribute8 := price_adj_rec.attribute8;
2703 l_price_adj_rec.attribute9 := price_adj_rec.attribute9;
2704 l_price_adj_rec.attribute10 := price_adj_rec.attribute10;
2705 l_price_adj_rec.attribute11 := price_adj_rec.attribute11;
2706 l_price_adj_rec.attribute12 := price_adj_rec.attribute12;
2707 l_price_adj_rec.attribute13 := price_adj_rec.attribute13;
2708 l_price_adj_rec.attribute14 := price_adj_rec.attribute14;
2709 l_price_adj_rec.attribute15 := price_adj_rec.attribute15;
2710 l_price_adj_rec.modifier_header_id := price_adj_rec.list_header_id;
2711 l_price_adj_rec.modifier_line_id := price_adj_rec.list_line_id;
2712 l_price_adj_rec.modifier_line_type_code := price_adj_rec.list_line_type_code;
2713 l_price_adj_rec.change_reason_code := price_adj_rec.change_reason_code;
2714 l_price_adj_rec.change_reason_text := price_adj_rec.change_reason_text;
2715 l_price_adj_rec.estimated_flag := price_adj_rec.estimated_flag;
2716 l_price_adj_rec.adjusted_amount := price_adj_rec.adjusted_amount;
2717 l_price_adj_rec.charge_type_code := price_adj_rec.charge_type_code;
2718 l_price_adj_rec.charge_subtype_code := price_adj_rec.charge_subtype_code;
2719 l_price_adj_rec.range_break_quantity := price_adj_rec.range_break_quantity;
2720 l_price_adj_rec.accrual_conversion_rate := price_adj_rec.accrual_conversion_rate;
2721 l_price_adj_rec.pricing_group_sequence := price_adj_rec.pricing_group_sequence;
2722 l_price_adj_rec.accrual_flag := price_adj_rec.accrual_flag;
2723 l_price_adj_rec.list_line_no := price_adj_rec.list_line_no;
2724 l_price_adj_rec.source_system_code := price_adj_rec.source_system_code;
2725 l_price_adj_rec.benefit_qty := price_adj_rec.benefit_qty;
2726 l_price_adj_rec.benefit_uom_code := price_adj_rec.benefit_uom_code;
2727 -- l_price_adj_rec.expiration_date := price_adj_rec.expiration_date;
2728 l_price_adj_rec.modifier_level_code := price_adj_rec.modifier_level_code;
2729 l_price_adj_rec.price_break_type_code := price_adj_rec.price_break_type_code;
2730 l_price_adj_rec.substitution_attribute := price_adj_rec.substitution_attribute;
2731 l_price_adj_rec.proration_type_code := price_adj_rec.proration_type_code;
2732 l_price_adj_rec.include_on_returns_flag := price_adj_rec.include_on_returns_flag;
2733 l_price_adj_rec.rebate_transaction_type_code := price_adj_rec.rebate_transaction_type_code;
2734
2735 l_price_adj_rec.quote_header_id := p_qhr_id;
2736 l_price_adj_rec.quote_line_id := p_qle_id;
2737
2738 IF p_level = 'L' AND p_qhr_id IS NULL AND p_qle_id IS NULL THEN
2739 -- related quote line has to be created
2740 l_price_adj_rec.quote_header_id := p_nqhr_id;
2741 l_price_adj_rec.qte_line_index := p_nqle_idx;
2742 END IF;
2743 l_price_adj_rec.price_adjustment_id := OKC_API.G_MISS_NUM;
2744 END IF;
2745
2746 IF price_adj_rec.operation_code = g_aso_op_code_update THEN
2747 IF (l_debug = 'Y') THEN
2748 okc_util.print_trace(1,'step 1-2 operation code = '||price_adj_rec.operation_code);
2749 END IF;
2750 IF NVL(l_prec_okc_price_adj_id,0) <> price_adj_rec.id THEN
2751
2752 -- Need to check if the related quote price adjustment is not already planned to be
2753 -- updated in the l_price_adj_tab variable
2754 IF l_price_adj_tab.first IS NOT NULL THEN
2755 FOR i IN l_price_adj_tab.first..l_price_adj_tab.last LOOP
2756 IF l_price_adj_tab(i).price_adjustment_id = price_adj_rec.price_adjustment_id THEN
2757 IF (l_debug = 'Y') THEN
2758 okc_util.print_trace(1,'step 1-3 related quote price adjustment is already planned to be updated');
2759 END IF;
2760 l_price_adj_insert := OKC_API.G_FALSE;
2761 exit;
2762 END IF;
2763 END LOOP;
2764 END IF;
2765 ELSE
2766 -- current contract price adjustment matches with multiple quote price adjustments
2767 -- and will be disregarded if already processed or if related quote price adjustment is
2768 -- not already planned to be updated in the l_price_adj_tab variable.
2769 --
2770 IF l_prec_prc_adj_procesd = OKC_API.G_TRUE THEN
2771 l_price_adj_insert := OKC_API.G_FALSE;
2772 l_prec_prc_adj_procesd := OKC_API.G_FALSE;
2773 ELSE
2774 IF l_price_adj_tab.first IS NOT NULL THEN
2775 FOR i IN l_price_adj_tab.first..l_price_adj_tab.last LOOP
2776 IF l_price_adj_tab(i).price_adjustment_id = price_adj_rec.price_adjustment_id THEN
2777 l_price_adj_insert := OKC_API.G_FALSE;
2778 IF (l_debug = 'Y') THEN
2779 okc_util.print_trace(1,'step 1-4 checking ctrct pr adj with multiple qte prc adj');
2780 END IF;
2781 exit;
2782 END IF;
2783 END LOOP;
2784 END IF;
2785 END IF; -- IF l_prec_prc_adj_procesd := okc_api.g_true then..
2786 END IF; -- IF l_prec_okc_price_adj_id <> price_adj_rec.id and ..
2787
2788 IF NVL(l_prec_okc_price_adj_id,0) <> price_adj_rec.id THEN
2789 IF l_prec_okc_price_adj_id IS NOT NULL AND l_prec_prc_adj_procesd = OKC_API.G_FALSE THEN
2790 -- Populate l_price_adj_rec with information from l_prec_price_adj_rec;
2791
2792
2793 l_price_adj_rec.modifier_mechanism_type_code := l_prec_price_adj_rec.modifier_mechanism_type_code;
2794 l_price_adj_rec.modified_from := l_prec_price_adj_rec.modified_from;
2795 l_price_adj_rec.modified_to := l_prec_price_adj_rec.modified_to;
2796 l_price_adj_rec.operand := l_prec_price_adj_rec.operand;
2797 l_price_adj_rec.arithmetic_operator := l_prec_price_adj_rec.arithmetic_operator;
2798 l_price_adj_rec.automatic_flag := l_prec_price_adj_rec.automatic_flag;
2799 l_price_adj_rec.update_allowable_flag := l_prec_price_adj_rec.update_allowed;
2800 l_price_adj_rec.updated_flag := l_prec_price_adj_rec.updated_flag;
2801 l_price_adj_rec.applied_flag := l_prec_price_adj_rec.applied_flag;
2802 l_price_adj_rec.on_invoice_flag := l_prec_price_adj_rec.on_invoice_flag;
2803 l_price_adj_rec.pricing_phase_id := l_prec_price_adj_rec.pricing_phase_id;
2804 l_price_adj_rec.attribute_category := l_prec_price_adj_rec.context;
2805 l_price_adj_rec.attribute1 := l_prec_price_adj_rec.attribute1;
2806 l_price_adj_rec.attribute2 := l_prec_price_adj_rec.attribute2;
2807 l_price_adj_rec.attribute3 := l_prec_price_adj_rec.attribute3;
2808 l_price_adj_rec.attribute4 := l_prec_price_adj_rec.attribute4;
2809 l_price_adj_rec.attribute5 := l_prec_price_adj_rec.attribute5;
2810 l_price_adj_rec.attribute6 := l_prec_price_adj_rec.attribute6;
2811 l_price_adj_rec.attribute7 := l_prec_price_adj_rec.attribute7;
2812 l_price_adj_rec.attribute8 := l_prec_price_adj_rec.attribute8;
2813 l_price_adj_rec.attribute9 := l_prec_price_adj_rec.attribute9;
2814 l_price_adj_rec.attribute10 := l_prec_price_adj_rec.attribute10;
2815 l_price_adj_rec.attribute11 := l_prec_price_adj_rec.attribute11;
2816 l_price_adj_rec.attribute12 := l_prec_price_adj_rec.attribute12;
2817 l_price_adj_rec.attribute13 := l_prec_price_adj_rec.attribute13;
2818 l_price_adj_rec.attribute14 := l_prec_price_adj_rec.attribute14;
2819 l_price_adj_rec.attribute15 := l_prec_price_adj_rec.attribute15;
2820 l_price_adj_rec.modifier_header_id := l_prec_price_adj_rec.list_header_id;
2821 l_price_adj_rec.modifier_line_id := l_prec_price_adj_rec.list_line_id;
2822 l_price_adj_rec.modifier_line_type_code := l_prec_price_adj_rec.list_line_type_code;
2823 l_price_adj_rec.change_reason_code := l_prec_price_adj_rec.change_reason_code;
2824 l_price_adj_rec.change_reason_text := l_prec_price_adj_rec.change_reason_text;
2825 l_price_adj_rec.estimated_flag := l_prec_price_adj_rec.estimated_flag;
2826 l_price_adj_rec.adjusted_amount := l_prec_price_adj_rec.adjusted_amount;
2827 l_price_adj_rec.charge_type_code := l_prec_price_adj_rec.charge_type_code;
2828 l_price_adj_rec.charge_subtype_code := l_prec_price_adj_rec.charge_subtype_code;
2829 l_price_adj_rec.range_break_quantity := l_prec_price_adj_rec.range_break_quantity;
2830 l_price_adj_rec.accrual_conversion_rate := l_prec_price_adj_rec.accrual_conversion_rate;
2831 l_price_adj_rec.pricing_group_sequence := l_prec_price_adj_rec.pricing_group_sequence;
2832 l_price_adj_rec.accrual_flag := l_prec_price_adj_rec.accrual_flag;
2833 l_price_adj_rec.list_line_no := l_prec_price_adj_rec.list_line_no;
2834 l_price_adj_rec.source_system_code := l_prec_price_adj_rec.source_system_code;
2835 l_price_adj_rec.benefit_qty := l_prec_price_adj_rec.benefit_qty;
2836 l_price_adj_rec.benefit_uom_code := l_prec_price_adj_rec.benefit_uom_code;
2837 -- l_price_adj_rec.expiration_date := l_prec_price_adj_rec.expiration_date;
2838 l_price_adj_rec.modifier_level_code := l_prec_price_adj_rec.modifier_level_code;
2839 l_price_adj_rec.price_break_type_code := l_prec_price_adj_rec.price_break_type_code;
2840 l_price_adj_rec.substitution_attribute := l_prec_price_adj_rec.substitution_attribute;
2841 l_price_adj_rec.proration_type_code := l_prec_price_adj_rec.proration_type_code;
2842 l_price_adj_rec.include_on_returns_flag := l_prec_price_adj_rec.include_on_returns_flag;
2843 l_price_adj_rec.rebate_transaction_type_code := l_prec_price_adj_rec.rebate_transaction_type_code;
2844
2845
2846 l_price_adj_rec.operation_code := g_aso_op_code_create;
2847 l_price_adj_rec.quote_header_id := p_qhr_id;
2848 l_price_adj_rec.quote_line_id := p_qle_id;
2849 l_price_adj_rec.price_adjustment_id := OKC_API.G_MISS_NUM;
2850
2851 l_price_adj_tab(x) := l_price_adj_rec;
2852 x := x + 1;
2853 --
2854 l_k_tmp_price_adj_tab(y).id := l_prec_price_adj_rec.id;
2855 l_k_tmp_price_adj_tab(y).level := p_level;
2856 y := y + 1;
2857
2858 l_prec_prc_adj_procesd := okc_api.g_true;
2859 END IF;
2860 l_prec_okc_price_adj_id := price_adj_rec.id;
2861 l_prec_prc_adj_procesd := OKC_API.G_FALSE;
2862 END IF;
2863
2864 IF l_price_adj_insert = OKC_API.G_TRUE THEN
2865 l_price_adj_rec.quote_header_id := p_qhr_id;
2866 l_price_adj_rec.quote_line_id := p_qle_id;
2867
2868 l_price_adj_rec.operation_code := price_adj_rec.operation_code;
2869 l_price_adj_rec.price_adjustment_id := price_adj_rec.price_adjustment_id;
2870 l_price_adj_rec.modifier_mechanism_type_code := price_adj_rec.modifier_mechanism_type_code;
2871 l_price_adj_rec.modified_from := price_adj_rec.modified_from;
2872 l_price_adj_rec.modified_to := price_adj_rec.modified_to;
2873 l_price_adj_rec.operand := price_adj_rec.operand;
2874 l_price_adj_rec.arithmetic_operator := price_adj_rec.arithmetic_operator;
2875 l_price_adj_rec.automatic_flag := price_adj_rec.automatic_flag;
2876 l_price_adj_rec.update_allowable_flag := price_adj_rec.update_allowed;
2877 l_price_adj_rec.updated_flag := price_adj_rec.updated_flag;
2878 l_price_adj_rec.applied_flag := price_adj_rec.applied_flag;
2879 l_price_adj_rec.on_invoice_flag := price_adj_rec.on_invoice_flag;
2880 l_price_adj_rec.pricing_phase_id := price_adj_rec.pricing_phase_id;
2881 l_price_adj_rec.attribute_category := price_adj_rec.context;
2882 l_price_adj_rec.attribute1 := price_adj_rec.attribute1;
2883 l_price_adj_rec.attribute2 := price_adj_rec.attribute2;
2884 l_price_adj_rec.attribute3 := price_adj_rec.attribute3;
2885 l_price_adj_rec.attribute4 := price_adj_rec.attribute4;
2886 l_price_adj_rec.attribute5 := price_adj_rec.attribute5;
2887 l_price_adj_rec.attribute6 := price_adj_rec.attribute6;
2888 l_price_adj_rec.attribute7 := price_adj_rec.attribute7;
2889 l_price_adj_rec.attribute8 := price_adj_rec.attribute8;
2890 l_price_adj_rec.attribute9 := price_adj_rec.attribute9;
2891 l_price_adj_rec.attribute10 := price_adj_rec.attribute10;
2892 l_price_adj_rec.attribute11 := price_adj_rec.attribute11;
2893 l_price_adj_rec.attribute12 := price_adj_rec.attribute12;
2894 l_price_adj_rec.attribute13 := price_adj_rec.attribute13;
2895 l_price_adj_rec.attribute14 := price_adj_rec.attribute14;
2896 l_price_adj_rec.attribute15 := price_adj_rec.attribute15;
2897 l_price_adj_rec.modifier_header_id := price_adj_rec.list_header_id;
2898 l_price_adj_rec.modifier_line_id := price_adj_rec.list_line_id;
2899 l_price_adj_rec.modifier_line_type_code := price_adj_rec.list_line_type_code;
2900 l_price_adj_rec.change_reason_code := price_adj_rec.change_reason_code;
2901 l_price_adj_rec.change_reason_text := price_adj_rec.change_reason_text;
2902 l_price_adj_rec.estimated_flag := price_adj_rec.estimated_flag;
2903 l_price_adj_rec.adjusted_amount := price_adj_rec.adjusted_amount;
2904 l_price_adj_rec.charge_type_code := price_adj_rec.charge_type_code;
2905 l_price_adj_rec.charge_subtype_code := price_adj_rec.charge_subtype_code;
2906 l_price_adj_rec.range_break_quantity := price_adj_rec.range_break_quantity;
2907 l_price_adj_rec.accrual_conversion_rate := price_adj_rec.accrual_conversion_rate;
2908 l_price_adj_rec.pricing_group_sequence := price_adj_rec.pricing_group_sequence;
2909 l_price_adj_rec.accrual_flag := price_adj_rec.accrual_flag;
2910 l_price_adj_rec.list_line_no := price_adj_rec.list_line_no;
2911 l_price_adj_rec.source_system_code := price_adj_rec.source_system_code;
2912 l_price_adj_rec.benefit_qty := price_adj_rec.benefit_qty;
2913 l_price_adj_rec.benefit_uom_code := price_adj_rec.benefit_uom_code;
2914 -- l_price_adj_rec.expiration_date := price_adj_rec.expiration_date;
2915 l_price_adj_rec.modifier_level_code := price_adj_rec.modifier_level_code;
2916 l_price_adj_rec.price_break_type_code := price_adj_rec.price_break_type_code;
2917 l_price_adj_rec.substitution_attribute := price_adj_rec.substitution_attribute;
2918 l_price_adj_rec.proration_type_code := price_adj_rec.proration_type_code;
2919 l_price_adj_rec.include_on_returns_flag := price_adj_rec.include_on_returns_flag;
2920 l_price_adj_rec.rebate_transaction_type_code := price_adj_rec.rebate_transaction_type_code;
2921
2922 END IF;
2923 END IF; -- IF price_adj_rec.operation_code = 'UPADTE' then...
2924
2925
2926 IF l_price_adj_insert = OKC_API.G_TRUE THEN
2927
2928 l_price_adj_tab(x) := l_price_adj_rec;
2929
2930 IF (l_debug = 'Y') THEN
2931 okc_util.print_trace(1,'INDEX VALUE x = '||x);
2932 okc_util.print_trace(1,'=========================================================');
2933 okc_util.print_trace(1,' ');
2934 okc_util.print_trace(1,'operation code = '||l_price_adj_tab(x).operation_code);
2935 okc_util.print_trace(1,'quote header id = '||l_price_adj_tab(x).quote_header_id);
2936 okc_util.print_trace(1,'quote line id = '||l_price_adj_tab(x).quote_line_id);
2937 okc_util.print_trace(1,'price adjustment id = '||l_price_adj_tab(x).price_adjustment_id);
2938 okc_util.print_trace(1,'modifier_mechanism_type_code = '||l_price_adj_tab(x).modifier_mechanism_type_code);
2939 okc_util.print_trace(1,'modified_from = '||l_price_adj_tab(x).modified_from);
2940 okc_util.print_trace(1,'modified_to = '||l_price_adj_tab(x).modified_to);
2941 okc_util.print_trace(1,'operand = '||l_price_adj_tab(x).operand);
2942 okc_util.print_trace(1,'arithmetic_operator = '||l_price_adj_tab(x).arithmetic_operator);
2943 okc_util.print_trace(1,'automatic_flag = '||l_price_adj_tab(x).automatic_flag);
2944 okc_util.print_trace(1,'update_allowable flag = '||l_price_adj_tab(x).update_allowable_flag);
2945 okc_util.print_trace(1,'updated_flag = '||l_price_adj_tab(x).updated_flag);
2946 okc_util.print_trace(1,'applied_flag = '||l_price_adj_tab(x).applied_flag);
2947 okc_util.print_trace(1,'on_invoice_flag = '||l_price_adj_tab(x).on_invoice_flag);
2948 okc_util.print_trace(1,'pricing_phase_id = '||l_price_adj_tab(x).pricing_phase_id);
2949 okc_util.print_trace(1,'attribute_category = '||l_price_adj_tab(x).attribute_category);
2950 okc_util.print_trace(1,'attribute1 = '||l_price_adj_tab(x).attribute1);
2951 okc_util.print_trace(1,'attribute2 = '||l_price_adj_tab(x).attribute2);
2952 okc_util.print_trace(1,'attribute3 = '||l_price_adj_tab(x).attribute3);
2953 okc_util.print_trace(1,'attribute4 = '||l_price_adj_tab(x).attribute4);
2954 okc_util.print_trace(1,'attribute5 = '||l_price_adj_tab(x).attribute5);
2955 okc_util.print_trace(1,'attribute6 = '||l_price_adj_tab(x).attribute6);
2956 okc_util.print_trace(1,'attribute7 = '||l_price_adj_tab(x).attribute7);
2957 okc_util.print_trace(1,'attribute8 = '||l_price_adj_tab(x).attribute8);
2958 okc_util.print_trace(1,'attribute9 = '||l_price_adj_tab(x).attribute9);
2959 okc_util.print_trace(1,'attribute10 = '||l_price_adj_tab(x).attribute10);
2960 okc_util.print_trace(1,'attribute11 = '||l_price_adj_tab(x).attribute11);
2961 okc_util.print_trace(1,'attribute12 = '||l_price_adj_tab(x).attribute12);
2962 okc_util.print_trace(1,'attribute13 = '||l_price_adj_tab(x).attribute13);
2963 okc_util.print_trace(1,'attribute14 = '||l_price_adj_tab(x).attribute14);
2964 okc_util.print_trace(1,'attribute15 = '||l_price_adj_tab(x).attribute15);
2965 okc_util.print_trace(1,'modifier_header_id = '||l_price_adj_tab(x).modifier_header_id);
2966 okc_util.print_trace(1,'modifier_line_id = '||l_price_adj_tab(x).modifier_line_id);
2967 okc_util.print_trace(1,'modifier_line_type_code = '||l_price_adj_tab(x).modifier_line_type_code);
2968 okc_util.print_trace(1,'change_reason_code = '||l_price_adj_tab(x).change_reason_code);
2969 okc_util.print_trace(1,'change_reason_text = '||l_price_adj_tab(x).change_reason_text);
2970 okc_util.print_trace(1,'estimated_flag = '||l_price_adj_tab(x).estimated_flag);
2971 okc_util.print_trace(1,'adjusted_amount = '||l_price_adj_tab(x).adjusted_amount);
2972 okc_util.print_trace(1,'charge_type_code = '||l_price_adj_tab(x).charge_type_code);
2973 okc_util.print_trace(1,'charge_subtype_code = '||l_price_adj_tab(x).charge_subtype_code);
2974 okc_util.print_trace(1,'range_break_quantity = '||l_price_adj_tab(x).range_break_quantity);
2975 okc_util.print_trace(1,'accrual_conversion_rate = '||l_price_adj_tab(x).accrual_conversion_rate);
2976 okc_util.print_trace(1,'pricing_group_sequence = '||l_price_adj_tab(x).pricing_group_sequence);
2977 okc_util.print_trace(1,'accrual_flag = '||l_price_adj_tab(x).accrual_flag);
2978 okc_util.print_trace(1,'list_line_no = '||l_price_adj_tab(x).list_line_no);
2979 okc_util.print_trace(1,'source_system_code = '||l_price_adj_tab(x).source_system_code);
2980 okc_util.print_trace(1,'benefit_qty = '||l_price_adj_tab(x).benefit_qty);
2981 okc_util.print_trace(1,'benefit_uom_code = '||l_price_adj_tab(x).benefit_uom_code);
2982 END IF;
2983 -- okc_util.print_trace(1,'expiration_date = '||l_price_adj_tab(x).expiration_date);
2984 IF (l_debug = 'Y') THEN
2985 okc_util.print_trace(1,'modifier_level_code = '||l_price_adj_tab(x).modifier_level_code);
2986 okc_util.print_trace(1,'price_break_type_code = '||l_price_adj_tab(x).price_break_type_code);
2987 okc_util.print_trace(1,'substitution_attribute = '||l_price_adj_tab(x).substitution_attribute);
2988 okc_util.print_trace(1,'proration_type_code = '||l_price_adj_tab(x).proration_type_code);
2989 okc_util.print_trace(1,'include_on_returns_flag = '||l_price_adj_tab(x).include_on_returns_flag);
2990 okc_util.print_trace(1,'rebate_transaction_type_code = '||l_price_adj_tab(x).rebate_transaction_type_code);
2991 okc_util.print_trace(1,' ');
2992 END IF;
2993
2994 x := x + 1;
2995 --
2996 l_k_tmp_price_adj_tab(y).id := price_adj_rec.id;
2997 l_k_tmp_price_adj_tab(y).level := p_level;
2998 y := y + 1;
2999 --
3000 l_prec_prc_adj_procesd := OKC_API.G_TRUE;
3001 END IF;
3002
3003 l_prec_price_adj_rec := price_adj_rec;
3004
3005 END LOOP; --- FOR price_adj_rec IN c_k_price_adj
3006
3007 -- Case of a new occurance of an existing price adjustment, not processed before because
3008 -- the related quote price adjustments have already been marked to be processed.
3009
3010 IF l_prec_price_adj_rec.operation_code = g_aso_op_code_update AND
3011 l_prec_okc_price_adj_id IS NOT NULL AND l_prec_prc_adj_procesd = OKC_API.G_FALSE THEN
3012 -- populate l_price_adj_rec with infomation from l_prec_price_adj_rec;
3013
3014 l_price_adj_rec.modifier_mechanism_type_code := l_prec_price_adj_rec.modifier_mechanism_type_code;
3015 l_price_adj_rec.modified_from := l_prec_price_adj_rec.modified_from;
3016 l_price_adj_rec.modified_to := l_prec_price_adj_rec.modified_to;
3017 l_price_adj_rec.operand := l_prec_price_adj_rec.operand;
3018 l_price_adj_rec.arithmetic_operator := l_prec_price_adj_rec.arithmetic_operator;
3019 l_price_adj_rec.automatic_flag := l_prec_price_adj_rec.automatic_flag;
3020 l_price_adj_rec.update_allowable_flag := l_prec_price_adj_rec.update_allowed;
3021 l_price_adj_rec.updated_flag := l_prec_price_adj_rec.updated_flag;
3022 l_price_adj_rec.applied_flag := l_prec_price_adj_rec.applied_flag;
3023 l_price_adj_rec.on_invoice_flag := l_prec_price_adj_rec.on_invoice_flag;
3024 l_price_adj_rec.pricing_phase_id := l_prec_price_adj_rec.pricing_phase_id;
3025 l_price_adj_rec.attribute_category := l_prec_price_adj_rec.context;
3026 l_price_adj_rec.attribute1 := l_prec_price_adj_rec.attribute1;
3027 l_price_adj_rec.attribute2 := l_prec_price_adj_rec.attribute2;
3028 l_price_adj_rec.attribute3 := l_prec_price_adj_rec.attribute3;
3029 l_price_adj_rec.attribute4 := l_prec_price_adj_rec.attribute4;
3030 l_price_adj_rec.attribute5 := l_prec_price_adj_rec.attribute5;
3031 l_price_adj_rec.attribute6 := l_prec_price_adj_rec.attribute6;
3032 l_price_adj_rec.attribute7 := l_prec_price_adj_rec.attribute7;
3033 l_price_adj_rec.attribute8 := l_prec_price_adj_rec.attribute8;
3034 l_price_adj_rec.attribute9 := l_prec_price_adj_rec.attribute9;
3035 l_price_adj_rec.attribute10 := l_prec_price_adj_rec.attribute10;
3036 l_price_adj_rec.attribute11 := l_prec_price_adj_rec.attribute11;
3037 l_price_adj_rec.attribute12 := l_prec_price_adj_rec.attribute12;
3038 l_price_adj_rec.attribute13 := l_prec_price_adj_rec.attribute13;
3039 l_price_adj_rec.attribute14 := l_prec_price_adj_rec.attribute14;
3040 l_price_adj_rec.attribute15 := l_prec_price_adj_rec.attribute15;
3041 l_price_adj_rec.modifier_header_id := l_prec_price_adj_rec.list_header_id;
3042 l_price_adj_rec.modifier_line_id := l_prec_price_adj_rec.list_line_id;
3043 l_price_adj_rec.modifier_line_type_code := l_prec_price_adj_rec.list_line_type_code;
3044 l_price_adj_rec.change_reason_code := l_prec_price_adj_rec.change_reason_code;
3045 l_price_adj_rec.change_reason_text := l_prec_price_adj_rec.change_reason_text;
3046 l_price_adj_rec.estimated_flag := l_prec_price_adj_rec.estimated_flag;
3047 l_price_adj_rec.adjusted_amount := l_prec_price_adj_rec.adjusted_amount;
3048 l_price_adj_rec.charge_type_code := l_prec_price_adj_rec.charge_type_code;
3049 l_price_adj_rec.charge_subtype_code := l_prec_price_adj_rec.charge_subtype_code;
3050 l_price_adj_rec.range_break_quantity := l_prec_price_adj_rec.range_break_quantity;
3051 l_price_adj_rec.accrual_conversion_rate := l_prec_price_adj_rec.accrual_conversion_rate;
3052 l_price_adj_rec.pricing_group_sequence := l_prec_price_adj_rec.pricing_group_sequence;
3053 l_price_adj_rec.accrual_flag := l_prec_price_adj_rec.accrual_flag;
3054 l_price_adj_rec.list_line_no := l_prec_price_adj_rec.list_line_no;
3055 l_price_adj_rec.source_system_code := l_prec_price_adj_rec.source_system_code;
3056 l_price_adj_rec.benefit_qty := l_prec_price_adj_rec.benefit_qty;
3057 l_price_adj_rec.benefit_uom_code := l_prec_price_adj_rec.benefit_uom_code;
3058 -- l_price_adj_rec.expiration_date := l_prec_price_adj_rec.expiration_date;
3059 l_price_adj_rec.modifier_level_code := l_prec_price_adj_rec.modifier_level_code;
3060 l_price_adj_rec.price_break_type_code := l_prec_price_adj_rec.price_break_type_code;
3061 l_price_adj_rec.substitution_attribute := l_prec_price_adj_rec.substitution_attribute;
3062 l_price_adj_rec.proration_type_code := l_prec_price_adj_rec.proration_type_code;
3063 l_price_adj_rec.include_on_returns_flag := l_prec_price_adj_rec.include_on_returns_flag;
3064 l_price_adj_rec.rebate_transaction_type_code := l_prec_price_adj_rec.rebate_transaction_type_code;
3065
3066 l_price_adj_rec.operation_code := g_aso_op_code_create;
3067 l_price_adj_rec.quote_header_id := p_qhr_id;
3068 l_price_adj_rec.quote_line_id := p_qle_id;
3069 l_price_adj_rec.price_adjustment_id := OKC_API.G_MISS_NUM;
3070 --
3071 l_price_adj_tab(x) := l_price_adj_rec;
3072 x:= x+1;
3073 --
3074 l_k_tmp_price_adj_tab(y).id := l_prec_price_adj_rec.id;
3075 l_k_tmp_price_adj_tab(y).level := p_level;
3076 y:=y+1;
3077
3078 l_prec_prc_adj_procesd := OKC_API.G_TRUE;
3079 END IF;
3080 --
3081 --
3082 -- Fill in the l_price_adj_tab variable with price adjustment to be deleted.
3083 --
3084
3085 FOR l_price_adj IN c_price_adj(p_q_flag,p_qhr_id, p_qle_id,
3086 p_o_flag,p_ohr_id,p_ole_id) LOOP
3087
3088 l_price_adj_insert := OKC_API.G_TRUE;
3089 --
3090 -- Need to check if the related quote price adjustment is not already planned to be updated
3091 -- in the l_price_adj_tab_variable
3092 --
3093 IF l_price_adj_tab.FIRST IS NOT NULL THEN
3094 FOR i IN l_price_adj_tab.first..l_price_adj_tab.last LOOP
3095 IF l_price_adj_tab(i).price_adjustment_id = l_price_adj.price_adjustment_id THEN
3096 l_price_adj_insert := OKC_API.G_FALSE;
3097 exit;
3098 END IF;
3099 END LOOP;
3100 END IF;
3101 IF l_price_adj_insert = OKC_API.G_TRUE THEN
3102 -- populate l_price_adj_rec with information from l_price_adj
3103 l_price_adj_rec.operation_code := g_aso_op_code_delete;
3104 l_price_adj_rec.quote_header_id := p_qhr_id;
3105 l_price_adj_rec.quote_line_id := p_qle_id;
3106
3107 l_price_adj_rec.price_adjustment_id := l_price_adj.price_adjustment_id;
3108 --
3109 l_price_adj_tab(x) := l_price_adj_rec;
3110 x:=x+1;
3111 END IF;
3112 END LOOP;
3113
3114
3115 IF (l_debug = 'Y') THEN
3116 okc_util.print_trace(1,' ');
3117 okc_util.print_trace(1,'-----------------------------------------');
3118 okc_util.print_trace(1,' values contained in the l_price_adj_tab ');
3119 okc_util.print_trace(1,'-----------------------------------------');
3120 okc_util.print_trace(1,' ');
3121 END IF;
3122
3123 IF l_price_adj_tab.first IS NOT NULL THEN
3124 FOR i IN l_price_adj_tab.first..l_price_adj_tab.last LOOP
3125 IF l_price_adj_tab.EXISTS(i) THEN
3126
3127 IF (l_debug = 'Y') THEN
3128 okc_util.print_trace(1,' ');
3129 okc_util.print_trace(1,'index value = '||i);
3130 okc_util.print_trace(1,'operation code = '||l_price_adj_tab(i).operation_code);
3131 okc_util.print_trace(1,'quote header id = '||l_price_adj_tab(i).quote_header_id);
3132 okc_util.print_trace(1,'quote line id = '||l_price_adj_tab(i).quote_line_id);
3133 okc_util.print_trace(1,'price adjustment id = '||l_price_adj_tab(i).price_adjustment_id);
3134 okc_util.print_trace(1,'modifier_mechanism_type_code = '||l_price_adj_tab(i).modifier_mechanism_type_code);
3135 okc_util.print_trace(1,'modified_from = '||l_price_adj_tab(i).modified_from);
3136 okc_util.print_trace(1,'modified_to = '||l_price_adj_tab(i).modified_to);
3137 okc_util.print_trace(1,'operand = '||l_price_adj_tab(i).operand);
3138 okc_util.print_trace(1,'arithmetic_operator = '||l_price_adj_tab(i).arithmetic_operator);
3139 okc_util.print_trace(1,'automatic_flag = '||l_price_adj_tab(i).automatic_flag);
3140 okc_util.print_trace(1,'update_allowable flag = '||l_price_adj_tab(i).update_allowable_flag);
3141 okc_util.print_trace(1,'updated_flag = '||l_price_adj_tab(i).updated_flag);
3142 okc_util.print_trace(1,'applied_flag = '||l_price_adj_tab(i).applied_flag);
3143 okc_util.print_trace(1,'on_invoice_flag = '||l_price_adj_tab(i).on_invoice_flag);
3144 okc_util.print_trace(1,'pricing_phase_id = '||l_price_adj_tab(i).pricing_phase_id);
3145 okc_util.print_trace(1,'attribute_category = '||l_price_adj_tab(i).attribute_category);
3146 okc_util.print_trace(1,'attribute1 = '||l_price_adj_tab(i).attribute1);
3147 okc_util.print_trace(1,'attribute2 = '||l_price_adj_tab(i).attribute2);
3148 okc_util.print_trace(1,'attribute3 = '||l_price_adj_tab(i).attribute3);
3149 okc_util.print_trace(1,'attribute4 = '||l_price_adj_tab(i).attribute4);
3150 okc_util.print_trace(1,'attribute5 = '||l_price_adj_tab(i).attribute5);
3151 okc_util.print_trace(1,'attribute6 = '||l_price_adj_tab(i).attribute6);
3152 okc_util.print_trace(1,'attribute7 = '||l_price_adj_tab(i).attribute7);
3153 okc_util.print_trace(1,'attribute8 = '||l_price_adj_tab(i).attribute8);
3154 okc_util.print_trace(1,'attribute9 = '||l_price_adj_tab(i).attribute9);
3155 okc_util.print_trace(1,'attribute10 = '||l_price_adj_tab(i).attribute10);
3156 okc_util.print_trace(1,'attribute11 = '||l_price_adj_tab(i).attribute11);
3157 okc_util.print_trace(1,'attribute12 = '||l_price_adj_tab(i).attribute12);
3158 okc_util.print_trace(1,'attribute13 = '||l_price_adj_tab(i).attribute13);
3159 okc_util.print_trace(1,'attribute14 = '||l_price_adj_tab(i).attribute14);
3160 okc_util.print_trace(1,'attribute15 = '||l_price_adj_tab(i).attribute15);
3161 okc_util.print_trace(1,'modifier_header_id = '||l_price_adj_tab(i).modifier_header_id);
3162 okc_util.print_trace(1,'modifier_line_id = '||l_price_adj_tab(i).modifier_line_id);
3163 okc_util.print_trace(1,'modifier_line_type_code = '||l_price_adj_tab(i).modifier_line_type_code);
3164 okc_util.print_trace(1,'change_reason_code = '||l_price_adj_tab(i).change_reason_code);
3165 okc_util.print_trace(1,'change_reason_text = '||l_price_adj_tab(i).change_reason_text);
3166 okc_util.print_trace(1,'estimated_flag = '||l_price_adj_tab(i).estimated_flag);
3167 okc_util.print_trace(1,'adjusted_amount = '||l_price_adj_tab(i).adjusted_amount);
3168 okc_util.print_trace(1,'charge_type_code = '||l_price_adj_tab(i).charge_type_code);
3169 okc_util.print_trace(1,'charge_subtype_code = '||l_price_adj_tab(i).charge_subtype_code);
3170 okc_util.print_trace(1,'range_break_quantity = '||l_price_adj_tab(i).range_break_quantity);
3171 okc_util.print_trace(1,'accrual_conversion_rate = '||l_price_adj_tab(i).accrual_conversion_rate);
3172 okc_util.print_trace(1,'pricing_group_sequence = '||l_price_adj_tab(i).pricing_group_sequence);
3173 okc_util.print_trace(1,'accrual_flag = '||l_price_adj_tab(i).accrual_flag);
3174 okc_util.print_trace(1,'list_line_no = '||l_price_adj_tab(i).list_line_no);
3175 okc_util.print_trace(1,'source_system_code = '||l_price_adj_tab(i).source_system_code);
3176 okc_util.print_trace(1,'benefit_qty = '||l_price_adj_tab(i).benefit_qty);
3177 okc_util.print_trace(1,'benefit_uom_code = '||l_price_adj_tab(i).benefit_uom_code);
3178 END IF;
3179 -- okc_util.print_trace(1,'expiration_date = '||l_price_adj_tab(i).expiration_date);
3180 IF (l_debug = 'Y') THEN
3181 okc_util.print_trace(1,'modifier_level_code = '||l_price_adj_tab(i).modifier_level_code);
3182 okc_util.print_trace(1,'price_break_type_code = '||l_price_adj_tab(i).price_break_type_code);
3183 okc_util.print_trace(1,'substitution_attribute = '||l_price_adj_tab(i).substitution_attribute);
3184 okc_util.print_trace(1,'proration_type_code = '||l_price_adj_tab(i).proration_type_code);
3185 okc_util.print_trace(1,'include_on_returns_flag = '||l_price_adj_tab(i).include_on_returns_flag);
3186 okc_util.print_trace(1,'rebate_transaction_type_code = '||l_price_adj_tab(i).rebate_transaction_type_code);
3187 okc_util.print_trace(1,' ');
3188 END IF;
3189 END IF;
3190 END LOOP;
3191 END IF;
3192
3193
3194
3195 IF l_k_tmp_price_adj_tab.count > 0 THEN
3196 FOR i IN l_k_tmp_price_adj_tab.FIRST..l_k_tmp_price_adj_tab.LAST LOOP
3197 x_k_price_adj_tab(x_k_price_adj_tab.COUNT+1) := l_k_tmp_price_adj_tab(i);
3198 END LOOP;
3199 END IF;
3200
3201 IF l_price_adj_tab.COUNT > 0 THEN
3202 FOR i IN l_price_adj_tab.FIRST..l_price_adj_tab.LAST LOOP
3203 x_price_adj_tab(x_price_adj_tab.COUNT+1) := l_price_adj_tab(i);
3204 END LOOP;
3205 END IF;
3206
3207
3208 IF (l_debug = 'Y') THEN
3209 okc_util.print_trace(1,' ');
3210 okc_util.print_trace(1,'====================================================');
3211 okc_util.print_trace(1,' FINAL VALUES CONTAINED IN THE X_PRICE_ADJ_TAB ');
3212 okc_util.print_trace(1,'====================================================');
3213 okc_util.print_trace(1,' ');
3214 END IF;
3215
3216 IF x_price_adj_tab.first IS NOT NULL THEN
3217 FOR i IN x_price_adj_tab.first..x_price_adj_tab.last LOOP
3218 IF x_price_adj_tab.EXISTS(i) THEN
3219
3220 IF (l_debug = 'Y') THEN
3221 okc_util.print_trace(1,' ');
3222 okc_util.print_trace(1,'index value = '||i);
3223 okc_util.print_trace(1,'operation code = '||x_price_adj_tab(i).operation_code);
3224 okc_util.print_trace(1,'quote header id = '||x_price_adj_tab(i).quote_header_id);
3225 okc_util.print_trace(1,'quote line id = '||x_price_adj_tab(i).quote_line_id);
3226 okc_util.print_trace(1,'price adjustment id = '||x_price_adj_tab(i).price_adjustment_id);
3227 okc_util.print_trace(1,'modifier_mechanism_type_code = '||x_price_adj_tab(i).modifier_mechanism_type_code);
3228 okc_util.print_trace(1,'modified_from = '||x_price_adj_tab(i).modified_from);
3229 okc_util.print_trace(1,'modified_to = '||x_price_adj_tab(i).modified_to);
3230 okc_util.print_trace(1,'operand = '||x_price_adj_tab(i).operand);
3231 okc_util.print_trace(1,'arithmetic_operator = '||x_price_adj_tab(i).arithmetic_operator);
3232 okc_util.print_trace(1,'automatic_flag = '||x_price_adj_tab(i).automatic_flag);
3233 okc_util.print_trace(1,'update_allowable flag = '||x_price_adj_tab(i).update_allowable_flag);
3234 okc_util.print_trace(1,'updated_flag = '||x_price_adj_tab(i).updated_flag);
3235 okc_util.print_trace(1,'applied_flag = '||x_price_adj_tab(i).applied_flag);
3236 okc_util.print_trace(1,'on_invoice_flag = '||x_price_adj_tab(i).on_invoice_flag);
3237 okc_util.print_trace(1,'pricing_phase_id = '||x_price_adj_tab(i).pricing_phase_id);
3238 okc_util.print_trace(1,'attribute_category = '||x_price_adj_tab(i).attribute_category);
3239 okc_util.print_trace(1,'attribute1 = '||x_price_adj_tab(i).attribute1);
3240 okc_util.print_trace(1,'attribute2 = '||x_price_adj_tab(i).attribute2);
3241 okc_util.print_trace(1,'attribute3 = '||x_price_adj_tab(i).attribute3);
3242 okc_util.print_trace(1,'attribute4 = '||x_price_adj_tab(i).attribute4);
3243 okc_util.print_trace(1,'attribute5 = '||x_price_adj_tab(i).attribute5);
3244 okc_util.print_trace(1,'attribute6 = '||x_price_adj_tab(i).attribute6);
3245 okc_util.print_trace(1,'attribute7 = '||x_price_adj_tab(i).attribute7);
3246 okc_util.print_trace(1,'attribute8 = '||x_price_adj_tab(i).attribute8);
3247 okc_util.print_trace(1,'attribute9 = '||x_price_adj_tab(i).attribute9);
3248 okc_util.print_trace(1,'attribute10 = '||x_price_adj_tab(i).attribute10);
3249 okc_util.print_trace(1,'attribute11 = '||x_price_adj_tab(i).attribute11);
3250 okc_util.print_trace(1,'attribute12 = '||x_price_adj_tab(i).attribute12);
3251 okc_util.print_trace(1,'attribute13 = '||x_price_adj_tab(i).attribute13);
3252 okc_util.print_trace(1,'attribute14 = '||x_price_adj_tab(i).attribute14);
3253 okc_util.print_trace(1,'attribute15 = '||x_price_adj_tab(i).attribute15);
3254 okc_util.print_trace(1,'modifier_header_id = '||x_price_adj_tab(i).modifier_header_id);
3255 okc_util.print_trace(1,'modifier_line_id = '||x_price_adj_tab(i).modifier_line_id);
3256 okc_util.print_trace(1,'modifier_line_type_code = '||x_price_adj_tab(i).modifier_line_type_code);
3257 okc_util.print_trace(1,'change_reason_code = '||x_price_adj_tab(i).change_reason_code);
3258 okc_util.print_trace(1,'change_reason_text = '||x_price_adj_tab(i).change_reason_text);
3259 okc_util.print_trace(1,'estimated_flag = '||x_price_adj_tab(i).estimated_flag);
3260 okc_util.print_trace(1,'adjusted_amount = '||x_price_adj_tab(i).adjusted_amount);
3261 okc_util.print_trace(1,'charge_type_code = '||x_price_adj_tab(i).charge_type_code);
3262 okc_util.print_trace(1,'charge_subtype_code = '||x_price_adj_tab(i).charge_subtype_code);
3263 okc_util.print_trace(1,'range_break_quantity = '||x_price_adj_tab(i).range_break_quantity);
3264 okc_util.print_trace(1,'accrual_conversion_rate = '||x_price_adj_tab(i).accrual_conversion_rate);
3265 okc_util.print_trace(1,'pricing_group_sequence = '||x_price_adj_tab(i).pricing_group_sequence);
3266 okc_util.print_trace(1,'accrual_flag = '||x_price_adj_tab(i).accrual_flag);
3267 okc_util.print_trace(1,'list_line_no = '||x_price_adj_tab(i).list_line_no);
3268 okc_util.print_trace(1,'source_system_code = '||x_price_adj_tab(i).source_system_code);
3269 okc_util.print_trace(1,'benefit_qty = '||x_price_adj_tab(i).benefit_qty);
3270 okc_util.print_trace(1,'benefit_uom_code = '||x_price_adj_tab(i).benefit_uom_code);
3271 END IF;
3272 -- okc_util.print_trace(1,'expiration_date = '||x_price_adj_tab(i).expiration_date);
3273 IF (l_debug = 'Y') THEN
3274 okc_util.print_trace(1,'modifier_level_code = '||x_price_adj_tab(i).modifier_level_code);
3275 okc_util.print_trace(1,'price_break_type_code = '||x_price_adj_tab(i).price_break_type_code);
3276 okc_util.print_trace(1,'substitution_attribute = '||x_price_adj_tab(i).substitution_attribute);
3277 okc_util.print_trace(1,'proration_type_code = '||x_price_adj_tab(i).proration_type_code);
3278 okc_util.print_trace(1,'include_on_returns_flag = '||x_price_adj_tab(i).include_on_returns_flag);
3279 okc_util.print_trace(1,'rebate_transaction_type_code = '||x_price_adj_tab(i).rebate_transaction_type_code);
3280 okc_util.print_trace(1,' ');
3281 END IF;
3282 END IF;
3283 END LOOP;
3284 END IF;
3285
3286 -- x_k_price_adj_tab := l_k_tmp_price_adj_tab;
3287 -- x_price_adj_tab := l_price_adj_tab;
3288
3289 IF (l_debug = 'Y') THEN
3290 okc_util.print_trace(1,' ');
3291 okc_util.print_trace(1,'====================================================');
3292 okc_util.print_trace(1,' FINAL VALUES CONTAINED IN THE X_K_PRICE_ADJ_TAB ');
3293 okc_util.print_trace(1,'====================================================');
3294 okc_util.print_trace(1,' ');
3295 END IF;
3296
3297 IF x_k_price_adj_tab.first IS NOT NULL THEN
3298 FOR i IN x_k_price_adj_tab.first..x_k_price_adj_tab.last LOOP
3299 IF x_k_price_adj_tab.EXISTS(i) THEN
3300
3301 IF (l_debug = 'Y') THEN
3302 okc_util.print_trace(1,'INDEX VALUE = '||i);
3303 okc_util.print_trace(1,'Price Adj id = '||x_k_price_adj_tab(i).id);
3304 okc_util.print_trace(1,'Level = '||x_k_price_adj_tab(i).level);
3305 END IF;
3306
3307 END IF;
3308
3309 END LOOP;
3310 END IF;
3311
3312 IF (l_debug = 'Y') THEN
3313 okc_util.print_trace(1,'------------------------------');
3314 okc_util.print_trace(1,'>>END : Get price adjustments ');
3315 okc_util.print_trace(1,'------------------------------');
3316 END IF;
3317
3318 EXCEPTION
3319 WHEN OTHERS THEN
3320 IF (l_debug = 'Y') THEN
3321 okc_util.print_trace(1,'inside get price adj: others exception');
3322 OKC_UTIL.print_trace(3,SQLERRM);
3323 END IF;
3324 -- Bug#2320635
3325 OKC_API.set_message(G_APP_NAME,
3326 G_UNEXPECTED_ERROR,
3327 G_SQLCODE_TOKEN,
3328 SQLCODE,
3329 G_SQLERRM_TOKEN,
3330 SQLERRM);
3331
3332 IF c_k_price_adj%ISOPEN THEN
3333 CLOSE c_k_price_adj;
3334 END IF;
3335 IF c_price_adj%ISOPEN THEN
3336 CLOSE c_price_adj;
3337 END IF;
3338
3339 RAISE OKC_API.G_EXCEPTION_ERROR;
3340
3341 END; --get_price_adj
3342
3343 ----------------------------------------------------------------------------
3344 -- PROCEDURE get_price_adj_attr
3345 --
3346 ----------------------------------------------------------------------------
3347
3348 PROCEDURE get_price_adj_attr ( p_price_adj_tab IN ASO_QUOTE_PUB.price_adj_tbl_type,
3349 p_k_price_adj_tab IN k_price_adj_tab_type,
3350 p_q_flag IN VARCHAR2,
3351 p_o_flag IN VARCHAR2,
3352 p_level IN VARCHAR2,
3353 x_price_adj_attr_tab OUT NOCOPY ASO_QUOTE_PUB.price_adj_attr_tbl_type ) IS
3354
3355 CURSOR c_k_price_adj_attr_c(b_kpat_id NUMBER) IS
3356 SELECT flex_title,
3357 pricing_context,
3358 pricing_attribute,
3359 pricing_attr_value_from,
3360 pricing_attr_value_to,
3361 comparison_operator
3362 FROM
3363 okc_price_adj_attribs kpadj
3364 WHERE
3365 kpadj.pat_id = b_kpat_id;
3366
3367
3368
3369 CURSOR c_k_price_adj_attr_u(b_kpat_id NUMBER,b_q_flag VARCHAR,b_o_flag VARCHAR,b_pat_id NUMBER) IS
3370 SELECT DECODE(qpadj.pricing_context,NULL,g_aso_op_code_create,
3371 DECODE(qpadj.pricing_attribute,NULL,g_aso_op_code_create,g_aso_op_code_update)
3372 ) OPERATION_CODE,
3373 qpadj.price_adj_attrib_id, -- quote price adj atribute ID
3374 kpadj.id, -- contract price adj attribute id
3375 kpadj.flex_title,
3376 kpadj.pricing_context,
3377 kpadj.pricing_attribute,
3378 kpadj.pricing_attr_value_from,
3379 kpadj.pricing_attr_value_to,
3380 kpadj.comparison_operator
3381
3382 FROM
3383 okc_price_adj_attribs kpadj,
3384 OKX_QTE_PRC_ADJ_ATRBS_V qpadj
3385 WHERE
3386 b_q_flag = OKC_API.g_true
3387 AND kpadj.pat_id = b_kpat_id
3388 AND qpadj.price_adjustment_id(+) = b_pat_id
3389 AND qpadj.flex_title(+) =kpadj.flex_title
3390 AND qpadj.pricing_context(+)=kpadj.pricing_context
3391 AND qpadj.pricing_attribute(+) = kpadj.pricing_attribute
3392
3393 UNION
3394
3395 SELECT DECODE(opadj.pricing_context,NULL,g_aso_op_code_create,
3396 DECODE(opadj.pricing_attribute,NULL,g_aso_op_code_create,g_aso_op_code_update)
3397 ) OPERATION_CODE,
3398 opadj.price_adj_attrib_id, -- order price adj atribute ID
3399 kpadj.id, -- contract price adj attribute id
3400 kpadj.flex_title,
3401 kpadj.pricing_context,
3402 kpadj.pricing_attribute,
3403 kpadj.pricing_attr_value_from,
3404 kpadj.pricing_attr_value_to,
3405 kpadj.comparison_operator
3406 FROM
3407 okc_price_adj_attribs kpadj,
3408 OKX_ORD_PRC_ADJ_ATRBS_V opadj
3409 WHERE
3410 b_o_flag = OKC_API.g_true
3411 AND kpadj.pat_id = b_kpat_id
3412 AND opadj.price_adjustment_id(+) = b_pat_id
3413 AND opadj.flex_title(+) =kpadj.flex_title
3414 AND opadj.pricing_context(+)=kpadj.pricing_context
3415 AND opadj.pricing_attribute(+) = kpadj.pricing_attribute
3416 ORDER BY
3417 operation_code;
3418
3419
3420
3421 CURSOR c_price_adj_attr(b_q_flag VARCHAR, b_o_flag VARCHAR,b_pat_id NUMBER) IS
3422 SELECT
3423 qpadj.price_adjustment_id,
3424 qpadj.price_adj_attrib_id, -- quote price adj attribute id
3425 qpadj.flex_title,
3426 qpadj.pricing_context,
3427 qpadj.pricing_attribute,
3428 qpadj.pricing_attr_value_from,
3429 qpadj.pricing_attr_value_to,
3430 qpadj.comparison_operator
3431 FROM OKX_QTE_PRC_ADJ_ATRBS_V qpadj
3432 WHERE b_q_flag = OKC_API.g_true
3433 AND qpadj.price_adjustment_id = b_pat_id
3434
3435 UNION
3436
3437 SELECT
3438 opadj.price_adjustment_id,
3439 opadj.price_adj_attrib_id, -- order price adj attribute id
3440 opadj.flex_title,
3441 opadj.pricing_context,
3442 opadj.pricing_attribute,
3443 opadj.pricing_attr_value_from,
3444 opadj.pricing_attr_value_to,
3445 opadj.comparison_operator
3446 FROM OKX_ORD_PRC_ADJ_ATRBS_V opadj
3447 WHERE b_o_flag = OKC_API.g_true
3448 AND opadj.price_adjustment_id = b_pat_id;
3449
3450
3451 -- variable declaration
3452 l_price_adj_insert VARCHAR2(1) := OKC_API.G_TRUE;
3453 l_price_adj_attr_rec ASO_QUOTE_PUB.price_adj_attr_rec_type;
3454 l_price_adj_attr_tab ASO_QUOTE_PUB.price_adj_attr_tbl_type;
3455
3456 x binary_integer;
3457
3458 BEGIN
3459 IF (l_debug = 'Y') THEN
3460 okc_util.print_trace(1,'-----------------------------------');
3461 okc_util.print_trace(1,'>> start : Get price adj attributes ');
3462 okc_util.print_trace(1,'-----------------------------------');
3463 END IF;
3464
3465 x_price_adj_attr_tab.DELETE;
3466
3467 l_price_adj_attr_tab.DELETE;
3468
3469
3470 x := l_price_adj_attr_tab.count;
3471
3472 IF x = 0 THEN
3473 x:=x+1;
3474 END IF;
3475
3476
3477 --
3478 -- Print the input values to this procedure from the
3479 -- p_k_price_adj_tab and p_price_adj_tab
3480 --
3481
3482 IF (l_debug = 'Y') THEN
3483 okc_util.print_trace(1,'Get price adj attributes: count of p_price_adj_tab = '||p_price_adj_tab.count);
3484 okc_util.print_trace(1,'Get price adj attributes: count of p_k_price_adj_tab = '||p_k_price_adj_tab.count);
3485 END IF;
3486
3487 IF (l_debug = 'Y') THEN
3488 okc_util.print_trace(1,'-------------------------------------');
3489 okc_util.print_trace(1,' Input data from p_price_adj_tab ');
3490 okc_util.print_trace(1,'-------------------------------------');
3491 END IF;
3492
3493 IF p_price_adj_tab.COUNT > 0 THEN
3494 FOR i IN p_price_adj_tab.FIRST..p_price_adj_tab.LAST LOOP
3495 IF (l_debug = 'Y') THEN
3496 okc_util.print_trace(1,'INDEX VALUE = '||i);
3497 okc_util.print_trace(1,'Operation code = '||p_price_adj_tab(i).operation_code);
3498 okc_util.print_trace(1,'Price Adj id = '||p_price_adj_tab(i).price_adjustment_id);
3499 okc_util.print_trace(1,'quote header id= '||p_price_adj_tab(i).quote_header_id);
3500 okc_util.print_trace(1,'quote line id= '||p_price_adj_tab(i).quote_line_id);
3501 END IF;
3502 END LOOP;
3503 END IF;
3504 IF (l_debug = 'Y') THEN
3505 okc_util.print_trace(1,' ');
3506 okc_util.print_trace(1,'-------------------------------------');
3507 okc_util.print_trace(1,' Input data from p_k_price_adj_tab ');
3508 okc_util.print_trace(1,'-------------------------------------');
3509 okc_util.print_trace(1,' ');
3510 END IF;
3511
3512
3513 IF p_k_price_adj_tab.COUNT > 0 THEN
3514 FOR i IN p_k_price_adj_tab.FIRST..p_k_price_adj_tab.LAST LOOP
3515 IF (l_debug = 'Y') THEN
3516 okc_util.print_trace(1,'INDEX VALUE = '||i);
3517 okc_util.print_trace(1,'Price Adj id = '||p_k_price_adj_tab(i).id);
3518 okc_util.print_trace(1,'Level = '||p_k_price_adj_tab(i).level);
3519 END IF;
3520 END LOOP;
3521 END IF;
3522
3523 IF (l_debug = 'Y') THEN
3524 okc_util.print_trace(1,' ');
3525 okc_util.print_trace(1,'-------------------------------------');
3526 okc_util.print_trace(1,' ');
3527 END IF;
3528
3529
3530
3531 IF p_k_price_adj_tab.count > 0 THEN
3532
3533 IF p_k_price_adj_tab.first IS NOT NULL THEN
3534
3535
3536 FOR i in p_k_price_adj_tab.first..p_k_price_adj_tab.last LOOP
3537 IF p_k_price_adj_tab(i).level = p_level THEN
3538
3539 --
3540 -- Fill in the l_price_adj_attr_tab variable with price adj attr to be created or updated
3541 --
3542 IF (l_debug = 'Y') THEN
3543 okc_util.print_trace(1,'i = '||i);
3544 okc_util.print_trace(1,'1- operation_code = '||p_price_adj_tab(i).operation_code);
3545 END IF;
3546 IF p_price_adj_tab(i).operation_code = g_aso_op_code_create THEN
3547
3548 FOR price_adj_attr_rec IN c_k_price_adj_attr_c(p_k_price_adj_tab(i).id) LOOP
3549 -- populate l_price_adj_attr_rec with information from price_adj_attr_rec
3550
3551 l_price_adj_attr_rec.flex_title := price_adj_attr_rec.flex_title;
3552 l_price_adj_attr_rec.pricing_context := price_adj_attr_rec.pricing_context;
3553 l_price_adj_attr_rec.pricing_attribute := price_adj_attr_rec.pricing_attribute;
3554 l_price_adj_attr_rec.pricing_attr_value_from := price_adj_attr_rec.pricing_attr_value_from;
3555 l_price_adj_attr_rec.pricing_attr_value_to := price_adj_attr_rec.pricing_attr_value_to;
3556 l_price_adj_attr_rec.comparison_operator := price_adj_attr_rec.comparison_operator;
3557
3558 l_price_adj_attr_rec.operation_code := g_aso_op_code_create;
3559 l_price_adj_attr_rec.price_adj_index := i;
3560
3561 l_price_adj_attr_tab(x) := l_price_adj_attr_rec;
3562 x:=x+1;
3563
3564
3565
3566 END LOOP;
3567 END IF;
3568
3569 IF p_price_adj_tab(i).operation_code = g_aso_op_code_update THEN
3570 IF (l_debug = 'Y') THEN
3571 okc_util.print_trace(1,'2 - operation_code = '||p_price_adj_tab(i).operation_code);
3572 END IF;
3573 FOR price_adj_attr_rec IN c_k_price_adj_attr_u(p_k_price_adj_tab(i).id,p_q_flag,p_o_flag,
3574 p_price_adj_tab(i).price_adjustment_id) LOOP
3575
3576 -- populate l_price_adj_attr_rec with information from price_adj_attr_rec
3577
3578 IF (l_debug = 'Y') THEN
3579 okc_util.print_trace(1,' 2 - populating l_price_adj_attr_rec with price_adj_attr_rec ');
3580 okc_util.print_trace(1,' 2 - i.e the values from c_k_price_adj_attr_u cursor ');
3581 END IF;
3582
3583
3584 l_price_adj_attr_rec.operation_code := price_adj_attr_rec.operation_code;
3585 l_price_adj_attr_rec.flex_title := price_adj_attr_rec.flex_title;
3586 l_price_adj_attr_rec.pricing_context := price_adj_attr_rec.pricing_context;
3587 l_price_adj_attr_rec.pricing_attribute := price_adj_attr_rec.pricing_attribute;
3588 l_price_adj_attr_rec.pricing_attr_value_from := price_adj_attr_rec.pricing_attr_value_from;
3589 l_price_adj_attr_rec.pricing_attr_value_to := price_adj_attr_rec.pricing_attr_value_to;
3590 l_price_adj_attr_rec.comparison_operator := price_adj_attr_rec.comparison_operator;
3591
3592 l_price_adj_attr_rec.price_adjustment_id := p_price_adj_tab(i).price_adjustment_id;
3593
3594 IF l_price_adj_attr_rec.operation_code = g_aso_op_code_create THEN
3595 IF (l_debug = 'Y') THEN
3596 okc_util.print_trace(1,'the operation code in c_k_price_adj_attr_u = '||price_adj_attr_rec.operation_code);
3597 END IF;
3598 l_price_adj_attr_rec.price_adj_attrib_id := OKC_API.G_MISS_NUM;
3599 END IF;
3600
3601 IF (l_debug = 'Y') THEN
3602 okc_util.print_trace(1,'2- Inserting the l_price_adj_attr_rec into the l_price_adj_attr_rec table');
3603 okc_util.print_trace(1,'2- Index value for insert = '||x);
3604 END IF;
3605
3606 l_price_adj_attr_tab(x) := l_price_adj_attr_rec;
3607
3608 IF (l_debug = 'Y') THEN
3609 okc_util.print_trace(1,'operation code = '||l_price_adj_attr_tab(x).operation_code);
3610 okc_util.print_trace(1,'price adjustment id= '||l_price_adj_attr_tab(x).price_adjustment_id);
3611 okc_util.print_trace(1,'flex title= '||l_price_adj_attr_tab(x).flex_title);
3612 okc_util.print_trace(1,'pricing context= '||l_price_adj_attr_tab(x).pricing_context);
3613 okc_util.print_trace(1,'pricing attribute= '||l_price_adj_attr_tab(x).pricing_attribute);
3614 okc_util.print_trace(1,'pricing attribute value from= '||l_price_adj_attr_tab(x).pricing_attr_value_from);
3615 okc_util.print_trace(1,'pricing attribute value to= '||l_price_adj_attr_tab(x).pricing_attr_value_to);
3616 okc_util.print_trace(1,'comparison operator= '||l_price_adj_attr_tab(x).comparison_operator);
3617 END IF;
3618
3619 x:=x+1;
3620
3621 END LOOP;
3622
3623 FOR l_price_adj_attr IN c_price_adj_attr(p_q_flag,p_o_flag,p_price_adj_tab(i).price_adjustment_id) LOOP
3624 l_price_adj_insert := OKC_API.G_TRUE;
3625 --
3626 -- Need to check if the related quote price adj attribute is not already planned to be updated
3627 -- in the l_price_adj_attr_tab variable.
3628 --
3629 IF l_price_adj_attr_tab.first IS NOT NULL THEN
3630 FOR i IN l_price_adj_attr_tab.first..l_price_adj_attr_tab.last LOOP
3631 IF l_price_adj_attr_tab(i).price_adj_attrib_id = l_price_adj_attr.price_adj_attrib_id AND
3632 l_price_adj_attr_tab(i).price_adjustment_id = p_price_adj_tab(i).price_adjustment_id THEN
3633 l_price_adj_insert := OKC_API.G_FALSE;
3634 exit;
3635 END IF;
3636 END LOOP;
3637 END IF;
3638
3639 IF l_price_adj_insert = OKC_API.G_TRUE THEN
3640 -- It should be inserted, but in fact no longer valid when coming back from the contract
3641 -- Populate l_price_adj_attr_rec with information from l_price_adj_attr.
3642
3643 l_price_adj_attr_rec.price_adj_attrib_id:= l_price_adj_attr.price_adj_attrib_id;
3644 l_price_adj_attr_rec.flex_title := l_price_adj_attr.flex_title;
3645 l_price_adj_attr_rec.pricing_context := l_price_adj_attr.pricing_context;
3646 l_price_adj_attr_rec.pricing_attribute := l_price_adj_attr.pricing_attribute;
3647 l_price_adj_attr_rec.pricing_attr_value_from := l_price_adj_attr.pricing_attr_value_from;
3648 l_price_adj_attr_rec.pricing_attr_value_to := l_price_adj_attr.pricing_attr_value_to;
3649 l_price_adj_attr_rec.comparison_operator := l_price_adj_attr.comparison_operator;
3650
3651 l_price_adj_attr_rec.operation_code := g_aso_op_code_delete;
3652
3653 IF (l_debug = 'Y') THEN
3654 okc_util.print_trace(1,'2 A check for delete - Inserting the l_price_adj_attr_rec ');
3655 okc_util.print_trace(1,'into the l_price_adj_attr_rec table');
3656 okc_util.print_trace(1,'2 A - Index value = '||x);
3657 END IF;
3658
3659 l_price_adj_attr_tab(x) := l_price_adj_attr_rec;
3660
3661 IF (l_debug = 'Y') THEN
3662 okc_util.print_trace(1,'operation code = '||l_price_adj_attr_tab(x).operation_code);
3663 okc_util.print_trace(1,'price adjustment id= '||l_price_adj_attr_tab(x).price_adjustment_id);
3664 okc_util.print_trace(1,'price adj attrib id= '||l_price_adj_attr_tab(x).price_adj_attrib_id);
3665 okc_util.print_trace(1,'flex title= '||l_price_adj_attr_tab(x).flex_title);
3666 okc_util.print_trace(1,'pricing context= '||l_price_adj_attr_tab(x).pricing_context);
3667 okc_util.print_trace(1,'pricing attribute= '||l_price_adj_attr_tab(x).pricing_attribute);
3668 okc_util.print_trace(1,'pricing attribute value from= '||l_price_adj_attr_tab(x).pricing_attr_value_from);
3669 okc_util.print_trace(1,'pricing attribute value to= '||l_price_adj_attr_tab(x).pricing_attr_value_to);
3670 okc_util.print_trace(1,'comparison operator= '||l_price_adj_attr_tab(x).comparison_operator);
3671 END IF;
3672
3673
3674 x := x + 1;
3675
3676 END IF;
3677 END LOOP;
3678 END IF; -- IF p_price_adj_tab(i).operation_code = g_aso_op_code_update then..
3679 END IF; -- IF p_price_adj_tab(i).level = p_level then ...
3680 END LOOP;
3681 END IF;
3682
3683 --
3684 -- Fill in the l_price_adj_attr_tab variable with price adj attr to be deleted
3685 --
3686 IF p_price_adj_tab.first IS NOT NULL THEN
3687 FOR i IN p_price_adj_tab.first..p_price_adj_tab.last LOOP
3688
3689 IF(p_level = 'H' AND
3690 p_price_adj_tab(i).quote_header_id IS NOT NULL AND
3691 p_price_adj_tab(i).quote_line_id IS NOT NULL)
3692 OR
3693 (p_level = 'L' AND
3694 p_price_adj_tab(i).quote_header_id IS NOT NULL AND
3695 p_price_adj_tab(i).quote_line_id IS NOT NULL)
3696 THEN
3697 IF p_price_adj_tab(i).operation_code = g_aso_op_code_delete THEN
3698 FOR l_price_adj_attr IN c_price_adj_attr(p_q_flag, p_o_flag,
3699 p_price_adj_tab(i).price_adjustment_id) LOOP
3700
3701 -- populate l_price_adj_attr_rec with information from l_price_adj_attr
3702
3703 l_price_adj_attr_rec.price_adjustment_id:= l_price_adj_attr.price_adjustment_id;
3704 l_price_adj_attr_rec.flex_title := l_price_adj_attr.flex_title;
3705 l_price_adj_attr_rec.pricing_context := l_price_adj_attr.pricing_context;
3706 l_price_adj_attr_rec.pricing_attribute := l_price_adj_attr.pricing_attribute;
3707 l_price_adj_attr_rec.pricing_attr_value_from := l_price_adj_attr.pricing_attr_value_from;
3708 l_price_adj_attr_rec.pricing_attr_value_to := l_price_adj_attr.pricing_attr_value_to;
3709 l_price_adj_attr_rec.comparison_operator := l_price_adj_attr.comparison_operator;
3710
3711 l_price_adj_attr_rec.operation_code := g_aso_op_code_delete;
3712
3713 l_price_adj_attr_tab(x) := l_price_adj_attr_rec;
3714 x := x + 1;
3715
3716 END LOOP;
3717 END IF;
3718 END IF; -- IF p_level = 'H' then
3719 END LOOP;
3720 END IF;
3721 END IF;
3722
3723
3724 --
3725 -- print out information of the l_price_adj_attr_tab variable
3726 --
3727 IF (l_debug = 'Y') THEN
3728 okc_util.print_trace(1,' ');
3729 okc_util.print_trace(1,'count of l_price_adj_attr_tab is '||l_price_adj_attr_tab.count);
3730 okc_util.print_trace(1,'----------------------------------------------------');
3731 okc_util.print_trace(1,' ');
3732 okc_util.print_trace(1,' values from the l_price_adj_attr_tab ');
3733 okc_util.print_trace(1,'----------------------------------------------------');
3734 okc_util.print_trace(1,' ');
3735 END IF;
3736
3737
3738 IF l_price_adj_attr_tab.first IS NOT NULL THEN
3739 FOR i IN l_price_adj_attr_tab.first..l_price_adj_attr_tab.last LOOP
3740 IF (l_debug = 'Y') THEN
3741 okc_util.print_trace(1,'Index value = '||i);
3742 okc_util.print_trace(1,'operation code = '||l_price_adj_attr_tab(i).operation_code);
3743 okc_util.print_trace(1,'price adjustment id= '||l_price_adj_attr_tab(i).price_adjustment_id);
3744 okc_util.print_trace(1,'flex title= '||l_price_adj_attr_tab(i).flex_title);
3745 okc_util.print_trace(1,'pricing context= '||l_price_adj_attr_tab(i).pricing_context);
3746 okc_util.print_trace(1,'pricing attribute= '||l_price_adj_attr_tab(i).pricing_attribute);
3747 okc_util.print_trace(1,'pricing attribute value from= '||l_price_adj_attr_tab(i).pricing_attr_value_from);
3748 okc_util.print_trace(1,'pricing attribute value to= '||l_price_adj_attr_tab(i).pricing_attr_value_to);
3749 okc_util.print_trace(1,'comparison operator= '||l_price_adj_attr_tab(i).comparison_operator);
3750 END IF;
3751 END LOOP;
3752 END IF;
3753
3754 IF (l_debug = 'Y') THEN
3755 okc_util.print_trace(1,' ');
3756 okc_util.print_trace(1,'----------------------------------------------------');
3757 END IF;
3758
3759 IF l_price_adj_attr_tab.count > 0 THEN
3760 FOR i IN l_price_adj_attr_tab.FIRST..l_price_adj_attr_tab.LAST LOOP
3761 x_price_adj_attr_tab(x_price_adj_attr_tab.COUNT+1) := l_price_adj_attr_tab(i);
3762 END LOOP;
3763 END IF;
3764
3765 IF (l_debug = 'Y') THEN
3766 okc_util.print_trace(1,' ');
3767 okc_util.print_trace(1,'====================================================');
3768 okc_util.print_trace(1,'FINAL OUT NOCOPY VALUES FROM THE X_PRICE_ADJ_ATTR_TAB ');
3769 okc_util.print_trace(1,'====================================================');
3770 okc_util.print_trace(1,' ');
3771 END IF;
3772
3773
3774 IF x_price_adj_attr_tab.first IS NOT NULL THEN
3775 FOR i IN x_price_adj_attr_tab.first..x_price_adj_attr_tab.last LOOP
3776 IF (l_debug = 'Y') THEN
3777 okc_util.print_trace(1,'Index value = '||i);
3778 okc_util.print_trace(1,'operation code = '||x_price_adj_attr_tab(i).operation_code);
3779 okc_util.print_trace(1,'price adjustment id= '||x_price_adj_attr_tab(i).price_adjustment_id);
3780 okc_util.print_trace(1,'flex title= '||x_price_adj_attr_tab(i).flex_title);
3781 okc_util.print_trace(1,'pricing context= '||x_price_adj_attr_tab(i).pricing_context);
3782 okc_util.print_trace(1,'pricing attribute= '||x_price_adj_attr_tab(i).pricing_attribute);
3783 okc_util.print_trace(1,'pricing attribute value from= '||x_price_adj_attr_tab(i).pricing_attr_value_from);
3784 okc_util.print_trace(1,'pricing attribute value to= '||x_price_adj_attr_tab(i).pricing_attr_value_to);
3785 okc_util.print_trace(1,'comparison operator= '||x_price_adj_attr_tab(i).comparison_operator);
3786 okc_util.print_trace(1,'----------------------------------------------------');
3787 END IF;
3788 END LOOP;
3789 END IF;
3790
3791 IF (l_debug = 'Y') THEN
3792 okc_util.print_trace(1,' ');
3793 okc_util.print_trace(1,'----------------------------------------------------');
3794 END IF;
3795
3796 -- x_price_adj_attr_tab := l_price_adj_attr_tab;
3797
3798 IF (l_debug = 'Y') THEN
3799 okc_util.print_trace(1,'-----------------------------------');
3800 okc_util.print_trace(1,'>> End : Get price adj attributes ');
3801 okc_util.print_trace(1,'-----------------------------------');
3802 END IF;
3803
3804 EXCEPTION
3805
3806 WHEN OTHERS THEN
3807
3808 IF (l_debug = 'Y') THEN
3809 okc_util.print_trace(1,'Inside get price adj attributes others exception handler:');
3810 okc_util.print_trace(1,SQLERRM);
3811 END IF;
3812 -- Bug#2320635
3813 OKC_API.set_message(G_APP_NAME,
3814 G_UNEXPECTED_ERROR,
3815 G_SQLCODE_TOKEN,
3816 SQLCODE,
3817 G_SQLERRM_TOKEN,
3818 SQLERRM);
3819
3820 IF c_k_price_adj_attr_c%ISOPEN THEN
3821 CLOSE c_k_price_adj_attr_c;
3822 END IF;
3823
3824 IF c_k_price_adj_attr_u%ISOPEN THEN
3825 CLOSE c_k_price_adj_attr_u;
3826 END IF;
3827
3828 IF c_price_adj_attr%ISOPEN THEN
3829 CLOSE c_price_adj_attr;
3830 END IF;
3831
3832 RAISE OKC_API.G_EXCEPTION_ERROR;
3833
3834 END get_price_adj_attr;
3835
3836
3837 ----------------------------------------------------------------------------
3838 -- PROCEDURE get_price_attr
3839 --
3840 ----------------------------------------------------------------------------
3841
3842 PROCEDURE get_price_attr(p_chr_id IN NUMBER,
3843 p_cle_id IN NUMBER,
3844 --
3845 p_qhr_id IN NUMBER,
3846 p_qle_id IN NUMBER,
3847 p_q_flag IN VARCHAR2,
3848 --
3849 p_ohr_id IN NUMBER,
3850 p_ole_id IN NUMBER,
3851 p_o_flag IN VARCHAR2,
3852 --
3853 p_level IN VARCHAR2,
3854
3855 p_nqhr_id IN NUMBER, -- Used only for new quote line to be created
3856 p_nqle_idx IN NUMBER,
3857 --
3858 x_price_attr_tab OUT NOCOPY ASO_QUOTE_PUB.price_attributes_tbl_type) IS
3859
3860
3861 -- Cursors declaration
3862
3863
3864 CURSOR c_price_attr(b_q_flag IN VARCHAR, b_qh_id NUMBER, b_ql_id NUMBER,
3865 b_o_flag IN VARCHAR, b_oh_id NUMBER, b_ol_id NUMBER ) IS
3866 SELECT
3867 qpattr.PRICE_ATTRIBUTE_ID -- quote header price attribute id
3868 FROM
3869 OKX_QTE_PRC_ATRBS_V qpattr
3870 WHERE b_q_flag = OKC_API.G_TRUE
3871 AND qpattr.quote_header_id = b_qh_id
3872 AND (( b_ql_id IS NULL AND qpattr.quote_line_id IS NULL ) OR
3873 (b_ql_id IS NOT NULL AND qpattr.quote_line_id = b_ql_id ))
3874 UNION
3875
3876 SELECT
3877 opattr.ORDER_PRICE_ATTRIB_ID -- order header price attribute id
3878 FROM
3879 OKX_ORD_PRC_ATRBS_V opattr
3880 WHERE b_o_flag = OKC_API.G_TRUE
3881 AND opattr.header_id = b_oh_id
3882 AND (( b_ol_id IS NULL AND opattr.line_id IS NULL ) OR
3883 (b_ol_id IS NOT NULL AND opattr.line_id = b_ol_id ));
3884
3885
3886 CURSOR c_k_price_attr(b_kh_id NUMBER,b_kl_id NUMBER) IS
3887 SELECT
3888 kpattr.flex_title,
3889 kpattr.pricing_context,
3890 kpattr.pricing_attribute1,
3891 kpattr.pricing_attribute2,
3892 kpattr.pricing_attribute3,
3893 kpattr.pricing_attribute4,
3894 kpattr.pricing_attribute5,
3895 kpattr.pricing_attribute6,
3896 kpattr.pricing_attribute7,
3897 kpattr.pricing_attribute8,
3898 kpattr.pricing_attribute9,
3899 kpattr.pricing_attribute10,
3900 kpattr.pricing_attribute11,
3901 kpattr.pricing_attribute12,
3902 kpattr.pricing_attribute13,
3903 kpattr.pricing_attribute14,
3904 kpattr.pricing_attribute15,
3905 kpattr.pricing_attribute16,
3906 kpattr.pricing_attribute17,
3907 kpattr.pricing_attribute18,
3908 kpattr.pricing_attribute19,
3909 kpattr.pricing_attribute20,
3910 kpattr.pricing_attribute21,
3911 kpattr.pricing_attribute22,
3912 kpattr.pricing_attribute23,
3913 kpattr.pricing_attribute24,
3914 kpattr.pricing_attribute25,
3915 kpattr.pricing_attribute26,
3916 kpattr.pricing_attribute27,
3917 kpattr.pricing_attribute28,
3918 kpattr.pricing_attribute29,
3919 kpattr.pricing_attribute30,
3920 kpattr.pricing_attribute31,
3921 kpattr.pricing_attribute32,
3922 kpattr.pricing_attribute33,
3923 kpattr.pricing_attribute34,
3924 kpattr.pricing_attribute35,
3925 kpattr.pricing_attribute36,
3926 kpattr.pricing_attribute37,
3927 kpattr.pricing_attribute38,
3928 kpattr.pricing_attribute39,
3929 kpattr.pricing_attribute40,
3930 kpattr.pricing_attribute41,
3931 kpattr.pricing_attribute42,
3932 kpattr.pricing_attribute43,
3933 kpattr.pricing_attribute44,
3934 kpattr.pricing_attribute45,
3935 kpattr.pricing_attribute46,
3936 kpattr.pricing_attribute47,
3937 kpattr.pricing_attribute48,
3938 kpattr.pricing_attribute49,
3939 kpattr.pricing_attribute50,
3940 kpattr.pricing_attribute51,
3941 kpattr.pricing_attribute52,
3942 kpattr.pricing_attribute53,
3943 kpattr.pricing_attribute54,
3944 kpattr.pricing_attribute55,
3945 kpattr.pricing_attribute56,
3946 kpattr.pricing_attribute57,
3947 kpattr.pricing_attribute58,
3948 kpattr.pricing_attribute59,
3949 kpattr.pricing_attribute60,
3950 kpattr.pricing_attribute61,
3951 kpattr.pricing_attribute62,
3952 kpattr.pricing_attribute63,
3953 kpattr.pricing_attribute64,
3954 kpattr.pricing_attribute65,
3955 kpattr.pricing_attribute66,
3956 kpattr.pricing_attribute67,
3957 kpattr.pricing_attribute68,
3958 kpattr.pricing_attribute69,
3959 kpattr.pricing_attribute70,
3960 kpattr.pricing_attribute71,
3961 kpattr.pricing_attribute72,
3962 kpattr.pricing_attribute73,
3963 kpattr.pricing_attribute74,
3964 kpattr.pricing_attribute75,
3965 kpattr.pricing_attribute76,
3966 kpattr.pricing_attribute77,
3967 kpattr.pricing_attribute78,
3968 kpattr.pricing_attribute79,
3969 kpattr.pricing_attribute80,
3970 kpattr.pricing_attribute81,
3971 kpattr.pricing_attribute82,
3972 kpattr.pricing_attribute83,
3973 kpattr.pricing_attribute84,
3974 kpattr.pricing_attribute85,
3975 kpattr.pricing_attribute86,
3976 kpattr.pricing_attribute87,
3977 kpattr.pricing_attribute88,
3978 kpattr.pricing_attribute89,
3979 kpattr.pricing_attribute90,
3980 kpattr.pricing_attribute91,
3981 kpattr.pricing_attribute92,
3982 kpattr.pricing_attribute93,
3983 kpattr.pricing_attribute94,
3984 kpattr.pricing_attribute95,
3985 kpattr.pricing_attribute96,
3986 kpattr.pricing_attribute97,
3987 kpattr.pricing_attribute98,
3988 kpattr.pricing_attribute99,
3989 kpattr.pricing_attribute100
3990 FROM
3991 okc_price_att_values kpattr
3992 WHERE
3993 kpattr.chr_id = b_kh_id
3994 AND
3995 ((b_kl_id IS NULL AND kpattr.cle_id IS NULL ) OR
3996 (b_kl_id IS NOT NULL AND kpattr.cle_id = b_kl_id));
3997
3998 -- Variables declaration
3999
4000 l_price_attr_rec ASO_QUOTE_PUB.price_attributes_rec_type;
4001 l_price_attr_tab ASO_QUOTE_PUB.price_attributes_tbl_type;
4002 x Binary_integer;
4003
4004 BEGIN
4005
4006 IF (l_debug = 'Y') THEN
4007 okc_util.print_trace(1,'-------------------------------');
4008 okc_util.print_trace(1,'>> start : Get price attributes ');
4009 okc_util.print_trace(1,'-------------------------------');
4010 END IF;
4011
4012 x_price_attr_tab.DELETE;
4013
4014 l_price_attr_tab.DELETE;
4015
4016
4017 x := l_price_attr_tab.count;
4018
4019 IF x = 0 THEN
4020 x:=x+1;
4021 END IF;
4022
4023 --
4024 -- Fill in the l_price_attr_tab with price attributes to be deleted.
4025 --
4026 FOR l_price_attr IN c_price_attr(p_q_flag,p_qhr_id, p_qle_id,
4027 p_o_flag,p_ohr_id, p_ole_id) LOOP
4028
4029 -- populate l_price_attr_rec with information from l_price_attr
4030
4031 l_price_attr_rec.price_attribute_id := l_price_attr.price_attribute_id;
4032
4033 l_price_attr_rec.operation_code := g_aso_op_code_delete;
4034 l_price_attr_rec.quote_header_id := p_qhr_id;
4035 l_price_attr_rec.quote_line_id := p_qle_id;
4036
4037 l_price_attr_tab(x) := l_price_attr_rec;
4038 x := x + 1;
4039
4040 END LOOP;
4041
4042 --
4043 -- Fill in the l_price_attr_tab with price attributes to be created.
4044 --
4045 FOR price_attr_rec IN c_k_price_attr(p_chr_id, p_cle_id) LOOP
4046
4047 -- poputate l_price_attr_rec with information from price_attr_rec
4048
4049 l_price_attr_rec.flex_title := price_attr_rec.flex_title;
4050 l_price_attr_rec.pricing_context := price_attr_rec.pricing_context;
4051 l_price_attr_rec.pricing_attribute1 := price_attr_rec.pricing_attribute1;
4052 l_price_attr_rec.pricing_attribute2 := price_attr_rec.pricing_attribute2;
4053 l_price_attr_rec.pricing_attribute3 := price_attr_rec.pricing_attribute3;
4054 l_price_attr_rec.pricing_attribute4 := price_attr_rec.pricing_attribute4;
4055 l_price_attr_rec.pricing_attribute5 := price_attr_rec.pricing_attribute5;
4056 l_price_attr_rec.pricing_attribute6 := price_attr_rec.pricing_attribute6;
4057 l_price_attr_rec.pricing_attribute7 := price_attr_rec.pricing_attribute7;
4058 l_price_attr_rec.pricing_attribute8 := price_attr_rec.pricing_attribute8;
4059 l_price_attr_rec.pricing_attribute9 := price_attr_rec.pricing_attribute9;
4060 l_price_attr_rec.pricing_attribute10 := price_attr_rec.pricing_attribute10;
4061 l_price_attr_rec.pricing_attribute11 := price_attr_rec.pricing_attribute11;
4062 l_price_attr_rec.pricing_attribute12 := price_attr_rec.pricing_attribute12;
4063 l_price_attr_rec.pricing_attribute13 := price_attr_rec.pricing_attribute13;
4064 l_price_attr_rec.pricing_attribute14 := price_attr_rec.pricing_attribute14;
4065 l_price_attr_rec.pricing_attribute15 := price_attr_rec.pricing_attribute15;
4066 l_price_attr_rec.pricing_attribute16 := price_attr_rec.pricing_attribute16;
4067 l_price_attr_rec.pricing_attribute17 := price_attr_rec.pricing_attribute17;
4068 l_price_attr_rec.pricing_attribute18 := price_attr_rec.pricing_attribute18;
4069 l_price_attr_rec.pricing_attribute19 := price_attr_rec.pricing_attribute19;
4070 l_price_attr_rec.pricing_attribute20 := price_attr_rec.pricing_attribute20;
4071 l_price_attr_rec.pricing_attribute21 := price_attr_rec.pricing_attribute21;
4072 l_price_attr_rec.pricing_attribute22 := price_attr_rec.pricing_attribute22;
4073 l_price_attr_rec.pricing_attribute23 := price_attr_rec.pricing_attribute23;
4074 l_price_attr_rec.pricing_attribute24 := price_attr_rec.pricing_attribute24;
4075 l_price_attr_rec.pricing_attribute25 := price_attr_rec.pricing_attribute25;
4076 l_price_attr_rec.pricing_attribute26 := price_attr_rec.pricing_attribute26;
4077 l_price_attr_rec.pricing_attribute27 := price_attr_rec.pricing_attribute27;
4078 l_price_attr_rec.pricing_attribute28 := price_attr_rec.pricing_attribute28;
4079 l_price_attr_rec.pricing_attribute29 := price_attr_rec.pricing_attribute29;
4080 l_price_attr_rec.pricing_attribute30 := price_attr_rec.pricing_attribute30;
4081 l_price_attr_rec.pricing_attribute31 := price_attr_rec.pricing_attribute31;
4082 l_price_attr_rec.pricing_attribute32 := price_attr_rec.pricing_attribute32;
4083 l_price_attr_rec.pricing_attribute33 := price_attr_rec.pricing_attribute33;
4084 l_price_attr_rec.pricing_attribute34 := price_attr_rec.pricing_attribute34;
4085 l_price_attr_rec.pricing_attribute35 := price_attr_rec.pricing_attribute35;
4086 l_price_attr_rec.pricing_attribute36 := price_attr_rec.pricing_attribute36;
4087 l_price_attr_rec.pricing_attribute37 := price_attr_rec.pricing_attribute37;
4088 l_price_attr_rec.pricing_attribute38 := price_attr_rec.pricing_attribute38;
4089 l_price_attr_rec.pricing_attribute39 := price_attr_rec.pricing_attribute39;
4090 l_price_attr_rec.pricing_attribute40 := price_attr_rec.pricing_attribute40;
4091 l_price_attr_rec.pricing_attribute41 := price_attr_rec.pricing_attribute41;
4092 l_price_attr_rec.pricing_attribute42 := price_attr_rec.pricing_attribute42;
4093 l_price_attr_rec.pricing_attribute43 := price_attr_rec.pricing_attribute43;
4094 l_price_attr_rec.pricing_attribute44 := price_attr_rec.pricing_attribute44;
4095 l_price_attr_rec.pricing_attribute45 := price_attr_rec.pricing_attribute45;
4096 l_price_attr_rec.pricing_attribute46 := price_attr_rec.pricing_attribute46;
4097 l_price_attr_rec.pricing_attribute47 := price_attr_rec.pricing_attribute47;
4098 l_price_attr_rec.pricing_attribute48 := price_attr_rec.pricing_attribute48;
4099 l_price_attr_rec.pricing_attribute49 := price_attr_rec.pricing_attribute49;
4100 l_price_attr_rec.pricing_attribute50 := price_attr_rec.pricing_attribute50;
4101 l_price_attr_rec.pricing_attribute51 := price_attr_rec.pricing_attribute51;
4102 l_price_attr_rec.pricing_attribute52 := price_attr_rec.pricing_attribute52;
4103 l_price_attr_rec.pricing_attribute53 := price_attr_rec.pricing_attribute53;
4104 l_price_attr_rec.pricing_attribute54 := price_attr_rec.pricing_attribute54;
4105 l_price_attr_rec.pricing_attribute55 := price_attr_rec.pricing_attribute55;
4106 l_price_attr_rec.pricing_attribute56 := price_attr_rec.pricing_attribute56;
4107 l_price_attr_rec.pricing_attribute57 := price_attr_rec.pricing_attribute57;
4108 l_price_attr_rec.pricing_attribute58 := price_attr_rec.pricing_attribute58;
4109 l_price_attr_rec.pricing_attribute59 := price_attr_rec.pricing_attribute59;
4110 l_price_attr_rec.pricing_attribute60 := price_attr_rec.pricing_attribute60;
4111 l_price_attr_rec.pricing_attribute61 := price_attr_rec.pricing_attribute61;
4112 l_price_attr_rec.pricing_attribute62 := price_attr_rec.pricing_attribute62;
4113 l_price_attr_rec.pricing_attribute63 := price_attr_rec.pricing_attribute63;
4114 l_price_attr_rec.pricing_attribute64 := price_attr_rec.pricing_attribute64;
4115 l_price_attr_rec.pricing_attribute65 := price_attr_rec.pricing_attribute65;
4116 l_price_attr_rec.pricing_attribute66 := price_attr_rec.pricing_attribute66;
4117 l_price_attr_rec.pricing_attribute67 := price_attr_rec.pricing_attribute67;
4118 l_price_attr_rec.pricing_attribute68 := price_attr_rec.pricing_attribute68;
4119 l_price_attr_rec.pricing_attribute69 := price_attr_rec.pricing_attribute69;
4120 l_price_attr_rec.pricing_attribute70 := price_attr_rec.pricing_attribute70;
4121 l_price_attr_rec.pricing_attribute71 := price_attr_rec.pricing_attribute71;
4122 l_price_attr_rec.pricing_attribute72 := price_attr_rec.pricing_attribute72;
4123 l_price_attr_rec.pricing_attribute73 := price_attr_rec.pricing_attribute73;
4124 l_price_attr_rec.pricing_attribute74 := price_attr_rec.pricing_attribute74;
4125 l_price_attr_rec.pricing_attribute75 := price_attr_rec.pricing_attribute75;
4126 l_price_attr_rec.pricing_attribute76 := price_attr_rec.pricing_attribute76;
4127 l_price_attr_rec.pricing_attribute77 := price_attr_rec.pricing_attribute77;
4128 l_price_attr_rec.pricing_attribute78 := price_attr_rec.pricing_attribute78;
4129 l_price_attr_rec.pricing_attribute79 := price_attr_rec.pricing_attribute79;
4130 l_price_attr_rec.pricing_attribute80 := price_attr_rec.pricing_attribute80;
4131 l_price_attr_rec.pricing_attribute81 := price_attr_rec.pricing_attribute81;
4132 l_price_attr_rec.pricing_attribute82 := price_attr_rec.pricing_attribute82;
4133 l_price_attr_rec.pricing_attribute83 := price_attr_rec.pricing_attribute83;
4134 l_price_attr_rec.pricing_attribute84 := price_attr_rec.pricing_attribute84;
4135 l_price_attr_rec.pricing_attribute85 := price_attr_rec.pricing_attribute85;
4136 l_price_attr_rec.pricing_attribute86 := price_attr_rec.pricing_attribute86;
4137 l_price_attr_rec.pricing_attribute87 := price_attr_rec.pricing_attribute87;
4138 l_price_attr_rec.pricing_attribute88 := price_attr_rec.pricing_attribute88;
4139 l_price_attr_rec.pricing_attribute89 := price_attr_rec.pricing_attribute89;
4140 l_price_attr_rec.pricing_attribute90 := price_attr_rec.pricing_attribute90;
4141 l_price_attr_rec.pricing_attribute91 := price_attr_rec.pricing_attribute91;
4142 l_price_attr_rec.pricing_attribute92 := price_attr_rec.pricing_attribute92;
4143 l_price_attr_rec.pricing_attribute93 := price_attr_rec.pricing_attribute93;
4144 l_price_attr_rec.pricing_attribute94 := price_attr_rec.pricing_attribute94;
4145 l_price_attr_rec.pricing_attribute95 := price_attr_rec.pricing_attribute95;
4146 l_price_attr_rec.pricing_attribute96 := price_attr_rec.pricing_attribute96;
4147 l_price_attr_rec.pricing_attribute97 := price_attr_rec.pricing_attribute97;
4148 l_price_attr_rec.pricing_attribute98 := price_attr_rec.pricing_attribute98;
4149 l_price_attr_rec.pricing_attribute99 := price_attr_rec.pricing_attribute99;
4150 l_price_attr_rec.pricing_attribute100:= price_attr_rec.pricing_attribute100;
4151
4152 l_price_attr_rec.operation_code := g_aso_op_code_create;
4153 l_price_attr_rec.quote_header_id := p_qhr_id;
4154 l_price_attr_rec.quote_line_id := p_qle_id;
4155
4156 IF p_level = 'L' AND p_qhr_id IS NULL THEN -- related quote line has to be created
4157
4158 l_price_attr_rec.quote_header_id := p_nqhr_id;
4159 l_price_attr_rec.qte_line_index := p_nqle_idx;
4160
4161 END IF;
4162
4163 l_price_attr_tab(x) := l_price_attr_rec;
4164 x := x + 1;
4165
4166 END LOOP;
4167
4168 --
4169 -- print out information of the l_price_attr_variable
4170 --
4171
4172
4173 IF (l_debug = 'Y') THEN
4174 okc_util.print_trace(1,' Count of l_price_attr_tab = '||l_price_attr_tab.COUNT);
4175 END IF;
4176
4177 IF l_price_attr_tab.COUNT > 0 THEN
4178 FOR i IN l_price_attr_tab.FIRST..l_price_attr_tab.LAST LOOP
4179
4180 IF (l_debug = 'Y') THEN
4181 okc_util.print_trace(1,' l_price_attr_tab record '||i);
4182 okc_util.print_trace(1,' ');
4183 okc_util.print_trace(1,'operation code = '||l_price_attr_tab(i).operation_code);
4184 okc_util.print_trace(1,'price attribute id = '||l_price_attr_tab(i).price_attribute_id);
4185 okc_util.print_trace(1,'quote header id = '||l_price_attr_tab(i).quote_header_id);
4186 okc_util.print_trace(1,'quote line id = '||l_price_attr_tab(i).quote_line_id);
4187 okc_util.print_trace(1,'qte line index = '||l_price_attr_tab(i).qte_line_index);
4188 okc_util.print_trace(1,'flex title = '||l_price_attr_tab(i).flex_title);
4189 okc_util.print_trace(1,'Pricing context = '||l_price_attr_tab(i).pricing_context);
4190 END IF;
4191
4192 END LOOP;
4193 END IF;
4194
4195
4196 IF l_price_attr_tab.COUNT > 0 THEN
4197 FOR i IN l_price_attr_tab.FIRST..l_price_attr_tab.LAST LOOP
4198 x_price_attr_tab(x_price_attr_tab.COUNT+1) := l_price_attr_tab(i);
4199 END LOOP;
4200 END IF;
4201
4202 -- x_price_attr_tab := l_price_attr_tab;
4203
4204 IF (l_debug = 'Y') THEN
4205 okc_util.print_trace(1,'-------------------------------');
4206 okc_util.print_trace(1,'>> End : Get price attributes ');
4207 okc_util.print_trace(1,'-------------------------------');
4208 END IF;
4209
4210 EXCEPTION
4211
4212 WHEN OTHERS THEN
4213
4214 IF (l_debug = 'Y') THEN
4215 OKC_UTIL.print_trace(3,SQLERRM);
4216 END IF;
4217 -- Bug#2320635
4218 OKC_API.set_message(G_APP_NAME,
4219 G_UNEXPECTED_ERROR,
4220 G_SQLCODE_TOKEN,
4221 SQLCODE,
4222 G_SQLERRM_TOKEN,
4223 SQLERRM);
4224
4225 IF c_k_price_attr%ISOPEN THEN
4226 CLOSE c_k_price_attr;
4227 END IF;
4228
4229 IF c_price_attr%ISOPEN THEN
4230 CLOSE c_price_attr;
4231 END IF;
4232
4233 RAISE OKC_API.G_EXCEPTION_ERROR;
4234
4235
4236 END; -- get_price_attr
4237
4238
4239 ----------------------------------------------------------------------------
4240 -- PROCEDURE get_price_adj_rltship
4241 --
4242 ----------------------------------------------------------------------------
4243
4244 PROCEDURE get_price_adj_rltship ( p_price_adj_tab IN ASO_QUOTE_PUB.Price_Adj_Tbl_Type,
4245 p_k_price_adj_tab IN k_price_adj_tab_type,
4246 --
4247 p_line_tab IN ASO_QUOTE_PUB.qte_line_tbl_type,
4248 p_kl_rel_tab IN okc_oc_int_config_pvt.line_rel_tab_type,
4249 p_line_shipment_tab IN ASO_QUOTE_PUB.shipment_tbl_type,
4250 --
4251 p_q_flag IN VARCHAR2,
4252 p_o_flag IN VARCHAR2,
4253 p_level IN VARCHAR2,
4254 --
4255 x_price_adj_rltship_tab OUT NOCOPY ASO_QUOTE_PUB.Price_Adj_Rltship_Tbl_Type) IS
4256 --
4257 -- Cursors declaration
4258 --
4259
4260 CURSOR c_k_price_adj_rltship (b_kpat_id NUMBER) IS
4261 SELECT pat_id_from,
4262 cle_id,
4263 pat_id
4264 FROM OKC_PRICE_ADJ_ASSOCS kpadj
4265 WHERE kpadj.pat_id_from = b_kpat_id;
4266
4267 --
4268 --
4269
4270 CURSOR c_price_adj_rltship_rltd(b_q_flag VARCHAR, b_o_flag VARCHAR, b_pat_id NUMBER) IS
4271 SELECT
4272 qpadj.ADJ_RELATIONSHIP_ID, --quote or order price adj rltship ID
4273 qpadj.price_adjustment_id PRICE_ADJUSTMENT_ID,
4274 qpadj.quote_shipment_id SHIPMENT_ID,
4275 qpadj.quote_line_id LINE_ID
4276 FROM OKX_QTE_PRC_ADJ_RLSHP_V qpadj
4277 WHERE b_q_flag = OKC_API.g_true
4278 AND qpadj.rltd_price_adj_id = b_pat_id
4279
4280 UNION
4281
4282 SELECT
4283 opadj.PRICE_ADJ_ASSOC_ID, -- PRICE_ADJ_ASSOC_ID, -- quote price adj rltship ID
4284 opadj.price_adjustment_id,
4285 to_number(NULL), --qpadj.quote_shipment_id
4286 opadj.line_id
4287 FROM OKX_ORD_PRC_ADJ_RLSHP_V opadj
4288 WHERE b_o_flag = OKC_API.g_true
4289 AND opadj.rltd_price_adj_id = b_pat_id;
4290
4291
4292 --
4293 --
4294
4295 CURSOR c_price_adj_rltship(b_q_flag VARCHAR, b_o_flag VARCHAR2, b_pat_id NUMBER, b_ln_id NUMBER) IS
4296 SELECT
4297 qpadj.ADJ_RELATIONSHIP_ID, -- quote or order price adj rltship ID
4298 qpadj.price_adjustment_id PRICE_ADJUSTMENT_ID,
4299 qpadj.quote_shipment_id SHIPMENT_ID,
4300 qpadj.quote_line_id LINE_ID,
4301 qpadj.rltd_price_adj_id
4302 FROM OKX_QTE_PRC_ADJ_RLSHP_V qpadj
4303 WHERE b_q_flag = OKC_API.g_true
4304 AND qpadj.price_adjustment_id = b_pat_id
4305 AND ((b_ln_id IS NOT NULL AND qpadj.quote_line_id = b_ln_id) OR b_ln_id IS NULL)
4306
4307 UNION
4308
4309 SELECT opadj.PRICE_ADJ_ASSOC_ID, -- quote price adj rltship ID
4310 opadj.price_adjustment_id,
4311 to_number(NULL), --qpadj.quote_shipment_id
4312 opadj.line_id,
4313 opadj.rltd_price_adj_id
4314 FROM OKX_ORD_PRC_ADJ_RLSHP_V opadj
4315 WHERE b_o_flag = OKC_API.g_true
4316 AND opadj.price_adjustment_id = b_pat_id
4317 AND ((b_ln_id IS NOT NULL AND opadj.line_id = b_ln_id) OR b_ln_id IS NULL);
4318
4319
4320 --
4321 -- Variables declaration
4322 --
4323 l_ql INTEGER;
4324 l_qs INTEGER;
4325 l_kpat INTEGER;
4326 l_shipment VARCHAR2(1);
4327 l_kl_id okc_k_lines_b.id%TYPE;
4328
4329 v_price_adj_rltship c_price_adj_rltship%ROWTYPE;
4330 l_price_adj_rltship c_price_adj_rltship%ROWTYPE;
4331 l_price_adj_insert VARCHAR2(1) := OKC_API.G_TRUE;
4332
4333 l_price_adj_rltship_rec ASO_QUOTE_PUB.price_adj_rltship_rec_type;
4334 l_price_adj_rltship_tab ASO_QUOTE_PUB.price_adj_rltship_tbl_type;
4335
4336 x binary_integer;
4337
4338 BEGIN
4339
4340 IF (l_debug = 'Y') THEN
4341 okc_util.print_trace(1,'---------------------------------------------');
4342 okc_util.print_trace(1,'>> start : Get price adjustment relationship ');
4343 okc_util.print_trace(1,'---------------------------------------------');
4344 END IF;
4345
4346
4347 IF (l_debug = 'Y') THEN
4348 okc_util.print_trace(1,'p_k_price_adj_tab count '||p_k_price_adj_tab.count);
4349 okc_util.print_trace(1,'p_price_adj_tab count '||p_price_adj_tab.count);
4350 END IF;
4351
4352 x_price_adj_rltship_tab.DELETE;
4353
4354 l_price_adj_rltship_tab.DELETE;
4355
4356 x := l_price_adj_rltship_tab.count;
4357
4358 IF x = 0 THEN
4359 x:=x+1;
4360 END IF;
4361
4362 IF p_k_price_adj_tab.count <> 0 THEN
4363
4364 IF p_k_price_adj_tab IS NOT NULL THEN
4365 FOR i IN p_k_price_adj_tab.first..p_k_price_adj_tab.last LOOP
4366 IF p_k_price_adj_tab(i).level = p_level THEN
4367 --
4368 -- Fill in the l_price_adj_rltship_tab variable with price relationship
4369 -- to be created or updated
4370 --
4371 IF p_price_adj_tab(i).operation_code = g_aso_op_code_create THEN
4372 FOR price_adj_rltship_rec IN c_k_price_adj_rltship(p_k_price_adj_tab(i).id) LOOP
4373 l_price_adj_rltship_rec.operation_code := g_aso_op_code_create;
4374 l_price_adj_rltship_rec.price_adj_index := i;
4375
4376 -- Need to check up on the operation code of each related quote line
4377 -- At this level, there is a relationship between a price adj and a valid contract line id, therefore
4378 -- if the related quote line id cannot be found (quote line id or index), an exception must be raised.
4379
4380
4381 -- Process associated contract line id
4382 IF price_adj_rltship_rec.cle_id IS NOT NULL THEN
4383 l_price_adj_insert := okc_api.g_false;
4384 IF p_kl_rel_tab.FIRST IS NOT NULL THEN
4385 l_ql := 0;
4386 FOR j IN p_kl_rel_tab.FIRST..p_kl_rel_tab.LAST LOOP
4387 IF p_kl_rel_tab(j).k_line_id = price_adj_rltship_rec.cle_id THEN
4388 l_price_adj_insert := okc_api.g_true;
4389 l_ql:= p_kl_rel_tab(j).q_line_idx;
4390 l_kl_id := p_kl_rel_tab(j).k_line_id; -- Bug 2543112
4391 EXIT;
4392 END IF;
4393 END LOOP;
4394 END IF;
4395
4396
4397 IF l_price_adj_insert = okc_api.g_true THEN
4398 IF p_line_tab(l_ql).operation_code = g_aso_op_code_create THEN
4399 l_price_adj_rltship_rec.qte_line_index:=l_ql; -- p_line_tab(l_ql).line_number
4400 ELSIF p_line_tab(l_ql).operation_code = g_aso_op_code_update THEN
4401 l_price_adj_rltship_rec.quote_line_id:= p_line_tab(l_ql).quote_line_id;
4402 ELSIF p_line_tab(l_ql).operation_code = g_aso_op_code_delete THEN
4403
4404 --set a specific error message, print it out and raise an exception
4405
4406 OKC_API.set_message(p_app_name => g_app_name,
4407 p_msg_name => 'OKO_PRC_PADJREL1',
4408 p_token1 => 'KLINEID',
4409 p_token1_value => l_kl_id);
4410 print_error(4);
4411 RAISE e_exit;
4412
4413 END IF;
4414 ELSE
4415
4416 --set a specific error message, print it out and raise an exception
4417
4418 OKC_API.set_message(p_app_name => g_app_name,
4419 p_msg_name => 'OKO_PRC_PADJREL2',
4420 p_token1 => 'PRICEADJID',
4421 p_token1_value => price_adj_rltship_rec.pat_id );
4422 print_error(4);
4423 RAISE e_exit;
4424
4425
4426 END IF;
4427 END IF; --IF price_adj_rltship_rec.cle_id IS NOT NULL THEN
4428
4429 --Process associated price adjustment id
4430 IF price_adj_rltship_rec.pat_id IS NOT NULL THEN
4431 l_price_adj_insert := okc_api.g_false;
4432 l_kpat:=0;
4433 FOR k in p_k_price_adj_tab.first .. p_k_price_adj_tab.last LOOP
4434 IF p_k_price_adj_tab(k).id = price_adj_rltship_rec.pat_id THEN
4435 l_price_adj_insert := okc_api.g_true;
4436 l_kpat:=k;
4437 EXIT;
4438 END IF;
4439 END LOOP;
4440 IF l_price_adj_insert = okc_api.g_true THEN
4441 IF p_price_adj_tab(l_kpat).operation_code = g_aso_op_code_create THEN
4442 l_price_adj_rltship_rec.rltd_price_adj_index:=l_kpat;
4443 ELSIF p_price_adj_tab(l_kpat).operation_code = g_aso_op_code_update THEN
4444 l_price_adj_rltship_rec.rltd_price_adj_id:= p_price_adj_tab(l_kpat).price_adjustment_id;
4445 ELSIF p_line_tab(l_ql).operation_code = g_aso_op_code_delete THEN
4446 --set a specific error message, print it out and raise an exception
4447
4448 OKC_API.set_message(p_app_name => g_app_name,
4449 p_msg_name => 'OKO_PRC_PADJREL3',
4450 p_token1 => 'PRICEADJID',
4451 p_token1_value => price_adj_rltship_rec.pat_id );
4452 print_error(4);
4453 RAISE e_exit;
4454
4455 END IF;
4456 ELSE
4457 --set a specific error message, print it out and raise an exception
4458
4459 OKC_API.set_message(p_app_name => g_app_name,
4460 p_msg_name => 'OKO_PRC_PADJREL4',
4461 p_token1 => 'KLINEID',
4462 p_token1_value => l_kl_id);
4463 print_error(4);
4464 RAISE e_exit;
4465
4466
4467 END IF;
4468 END IF; --IF price_adj_rltship_rec.pat_id IS NOT NULL THEN
4469
4470 l_price_adj_rltship_tab(x) := l_price_adj_rltship_rec;
4471 x := x +1;
4472
4473 END LOOP;
4474 END IF; -- IF p_price_adj_tab(i).operation_code = g_aso_op_code_create THEN
4475
4476
4477 IF p_price_adj_tab(i).operation_code = g_aso_op_code_update THEN
4478 FOR price_adj_rltship_rec IN c_k_price_adj_rltship (p_k_price_adj_tab(i).id) LOOP
4479
4480 -- Need to check up on the operation code of each related quote line
4481 -- At this level, there is a relationship between a price adj and a valid contract line id, therefore
4482 -- if the related quote line id cannot be found (quote line id or index), an exception must be raised.
4483 --
4484 IF price_adj_rltship_rec.cle_id IS NOT NULL THEN
4485 l_price_adj_insert := okc_api.g_false;
4486 IF p_kl_rel_tab.FIRST is NOT NULL THEN
4487 l_ql := 0;
4488 FOR j IN p_kl_rel_tab.FIRST .. p_kl_rel_tab.LAST LOOP
4489 IF p_kl_rel_tab(j).k_line_id = price_adj_rltship_rec.cle_id THEN
4490 l_price_adj_insert := okc_api.g_true;
4491 l_ql:= p_kl_rel_tab(j).q_line_idx;
4492 EXIT;
4493 END IF;
4494 END LOOP;
4495 END IF;
4496
4497 IF l_price_adj_insert = okc_api.g_true THEN
4498
4499 IF p_line_tab(l_ql).operation_code = g_aso_op_code_create THEN
4500 l_price_adj_rltship_rec.operation_code := g_aso_op_code_create;
4501 l_price_adj_rltship_rec.price_adjustment_id := p_price_adj_tab(i).price_adjustment_id;
4502 l_price_adj_rltship_rec.qte_line_index:=l_ql;
4503
4504 ELSIF p_line_tab(l_ql).operation_code = g_aso_op_code_update THEN
4505 --Need to check if the related quote line id is associated to the quote price adj id
4506 OPEN c_price_adj_rltship(p_q_flag, p_o_flag,
4507 p_price_adj_tab(i).price_adjustment_id, p_line_tab(l_ql).quote_line_id);
4508 FETCH c_price_adj_rltship INTO v_price_adj_rltship;
4509 CLOSE c_price_adj_rltship;
4510
4511 IF c_price_adj_rltship%NOTFOUND THEN
4512 l_price_adj_rltship_rec.operation_code := g_aso_op_code_create;
4513 l_price_adj_rltship_rec.price_adjustment_id := p_price_adj_tab(i).price_adjustment_id;
4514 l_price_adj_rltship_rec.quote_line_id:= p_line_tab(l_ql).quote_line_id;
4515 ELSE -- NEED to UPDATE OR NOT with shipment id?
4516 -- Need to check if the quote adj rltship involves a shipment id
4517 IF v_price_adj_rltship.shipment_id IS NOT NULL THEN
4518 -- Need to check if we have a shipment line for the related quote line id
4519 l_shipment := okc_api.g_false;
4520 IF p_line_shipment_tab.FIRST IS NOT NULL THEN
4521 FOR k IN p_line_shipment_tab.FIRST..p_line_shipment_tab.LAST LOOP
4522 IF p_line_shipment_tab(k).quote_header_id = p_line_tab(l_ql).quote_header_id AND
4523 p_line_shipment_tab(k).quote_line_id = p_line_tab(l_ql).quote_line_id THEN
4524 l_shipment:=okc_api.g_true;
4525 l_qs := k;
4526 EXIT;
4527 END IF;
4528 END LOOP;
4529 END IF;
4530
4531 IF l_shipment = okc_api.g_false THEN
4532 -- set an error on the stack, print it out and raise an exception
4533
4534 OKC_API.set_message(p_app_name => g_app_name,
4535 p_msg_name => 'OKO_PRC_PADJREL5',
4536 p_token1 => 'QLINEID',
4537 p_token1_value => p_line_tab(l_ql).quote_line_id);
4538 print_error(4);
4539 RAISE e_exit;
4540
4541 ELSE
4542 IF p_line_shipment_tab(l_qs).operation_code = g_aso_op_code_update THEN
4543 l_price_adj_rltship_rec.operation_code := g_aso_op_code_update;
4544 l_price_adj_rltship_rec.adj_relationship_id := v_price_adj_rltship.adj_relationship_id;
4545 l_price_adj_rltship_rec.price_adjustment_id := p_price_adj_tab(i).price_adjustment_id;
4546 l_price_adj_rltship_rec.quote_line_id:= p_line_tab(l_ql).quote_line_id;
4547 -- And we keep the same shipment id which is planned to be updated
4548 ELSIF p_line_shipment_tab(l_qs).operation_code = g_aso_op_code_delete THEN
4549 l_price_adj_rltship_rec.operation_code := g_aso_op_code_update;
4550 l_price_adj_rltship_rec.adj_relationship_id := v_price_adj_rltship.adj_relationship_id;
4551 l_price_adj_rltship_rec.price_adjustment_id := p_price_adj_tab(i).price_adjustment_id;
4552 l_price_adj_rltship_rec.quote_line_id:= p_line_tab(l_ql).quote_line_id;
4553 l_price_adj_rltship_rec.quote_shipment_id:= NULL;
4554 END IF;
4555 END IF;
4556 ELSE
4557 --Even if we have a contract shipment line id, we cannot decide to attach it to the price adj id
4558 l_price_adj_rltship_rec.operation_code := g_aso_op_code_update;
4559 l_price_adj_rltship_rec.adj_relationship_id := v_price_adj_rltship.adj_relationship_id;
4560 l_price_adj_rltship_rec.price_adjustment_id := p_price_adj_tab(i).price_adjustment_id;
4561 l_price_adj_rltship_rec.quote_line_id:= p_line_tab(l_ql).quote_line_id;
4562 END IF;
4563 END IF;
4564
4565 ELSIF p_line_tab(l_ql).operation_code = g_aso_op_code_delete THEN
4566 --set a specific error message, print it out and raise an exception
4567
4568 OKC_API.set_message(p_app_name => g_app_name,
4569 p_msg_name => 'OKO_PRC_PADJREL6');
4570 print_error(4);
4571 RAISE e_exit;
4572
4573
4574 END IF; -- ELSIF p_line_tab(l_ql).operation_code = g_aso_op_code_update THEN
4575
4576 ELSE
4577 --set a specific error message, print it out and raise an exception
4578
4579 OKC_API.set_message(p_app_name => g_app_name,
4580 p_msg_name => 'OKO_PRC_PADJREL7',
4581 p_token1 => 'KLINEID',
4582 p_token1_value => price_adj_rltship_rec.cle_id);
4583 print_error(4);
4584 RAISE e_exit;
4585
4586
4587 END IF; -- IF l_price_adj_insert = okc_api.g_true THEN
4588 END IF; --IF price_adj_rltship_rec.cle_id IS NOT NULL THEN
4589
4590 --Process associated price adjustement id
4591 IF price_adj_rltship_rec.pat_id IS NOT NULL THEN
4592 l_price_adj_insert := okc_api.g_false;
4593 l_kpat:=0;
4594 FOR k in p_k_price_adj_tab.first .. p_k_price_adj_tab.last LOOP
4595 IF p_k_price_adj_tab(k).id = price_adj_rltship_rec.pat_id THEN
4596 l_price_adj_insert := okc_api.g_true;
4597 l_kpat:=k;
4598 EXIT;
4599 END IF;
4600 END LOOP;
4601 IF l_price_adj_insert = okc_api.g_true THEN
4602 IF p_price_adj_tab(l_kpat).operation_code = g_aso_op_code_create THEN
4603 l_price_adj_rltship_rec.rltd_price_adj_index:=l_kpat;
4604 ELSIF p_price_adj_tab(l_kpat).operation_code = g_aso_op_code_update THEN
4605 l_price_adj_rltship_rec.rltd_price_adj_id:= p_price_adj_tab(l_kpat).price_adjustment_id;
4606 ELSIF p_line_tab(l_ql).operation_code = g_aso_op_code_delete THEN
4607 --set a specific error message, print it out and raise an exception
4608
4609 OKC_API.set_message(p_app_name => g_app_name,
4610 p_msg_name => 'OKO_PRC_PADJREL8',
4611 p_token1 => 'PRICEADJID',
4612 p_token1_value => price_adj_rltship_rec.pat_id );
4613 print_error(4);
4614 RAISE e_exit;
4615
4616 END IF;
4617 ELSE
4618 --set a specific error message, print it out and raise an exception
4619
4620 OKC_API.set_message(p_app_name => g_app_name,
4621 p_msg_name => 'OKO_PRC_PADJREL9',
4622 p_token1 => 'KLINEID',
4623 p_token1_value => price_adj_rltship_rec.cle_id);
4624 print_error(4);
4625 RAISE e_exit;
4626
4627
4628 END IF;
4629 END IF; --IF price_adj_rltship_rec.pat_id IS NOT NULL THEN
4630
4631 l_price_adj_rltship_tab(x) := l_price_adj_rltship_rec;
4632 x := x+1;
4633
4634 END LOOP;
4635 FOR l_price_adj_rltship IN c_price_adj_rltship(p_q_flag, p_o_flag,
4636 p_price_adj_tab(i).price_adjustment_id, NULL) LOOP
4637 l_price_adj_insert:=okc_api.g_true;
4638 -- Need to check if the related quote price adj rltship is not already planned to
4639 -- be updated in the l_price_adj_rltship_tab variable
4640
4641 IF l_price_adj_rltship_tab.first IS NOT NULL THEN
4642 FOR i in l_price_adj_rltship_tab.first..l_price_adj_rltship_tab.last LOOP
4643 IF (l_price_adj_rltship_tab(i).adj_relationship_id = l_price_adj_rltship.adj_relationship_id AND
4644 l_price_adj_rltship_tab(i).price_adjustment_id = l_price_adj_rltship.price_adjustment_id AND
4645 (l_price_adj_rltship_tab(i).quote_line_id= l_price_adj_rltship.line_id OR
4646 (l_price_adj_rltship_tab(i).quote_line_id IS NULL AND l_price_adj_rltship.line_id IS NULL)) AND
4647 (l_price_adj_rltship_tab(i).quote_shipment_id= l_price_adj_rltship.shipment_id OR
4648 (l_price_adj_rltship_tab(i).quote_shipment_id IS NULL AND l_price_adj_rltship.shipment_id IS NULL)) AND
4649 (l_price_adj_rltship_tab(i).rltd_price_adj_id= l_price_adj_rltship.rltd_price_adj_id OR
4650 (l_price_adj_rltship_tab(i).rltd_price_adj_id IS NULL AND l_price_adj_rltship.rltd_price_adj_id IS NULL)))
4651 THEN
4652 l_price_adj_insert:=okc_api.g_false;
4653 EXIT;
4654 END IF;
4655 END LOOP;
4656 END IF;
4657
4658 IF l_price_adj_insert=okc_api.g_true THEN
4659 -- populate l_price_adj_rltship_rec with information from l_price_adj_rltship
4660 ----
4661 l_price_adj_rltship_rec.adj_relationship_id := l_price_adj_rltship.adj_relationship_id;
4662 l_price_adj_rltship_rec.quote_line_id := l_price_adj_rltship.line_id;
4663 l_price_adj_rltship_rec.quote_shipment_id := l_price_adj_rltship.shipment_id;
4664 l_price_adj_rltship_rec.price_adjustment_id := l_price_adj_rltship.price_adjustment_id;
4665
4666 l_price_adj_rltship_rec.operation_code := g_aso_op_code_delete;
4667
4668 l_price_adj_rltship_tab(x) := l_price_adj_rltship_rec;
4669 x := x +1;
4670
4671 END IF;
4672 END LOOP;
4673 END IF; -- IF p_price_adj_tab(i).operation_code = g_aso_op_code_update THEN
4674 END IF; --IF p_k_price_adj_tab(i).level = p_level THEN
4675 END LOOP; -- FOR i IN p_k_price_adj_tab.FIRST
4676 END IF; -- IF p_k_price_adj_tab.FIRST IS NOT NULL
4677
4678 END IF; -- IF p_k_price_adj_tab.count <> 0
4679
4680 --
4681 -- Fill in the l_price_adj_rltship_tab variable with price adj rltship to be deleted
4682 --
4683
4684 IF p_price_adj_tab.first IS NOT NULL THEN
4685
4686 FOR i in p_price_adj_tab.first .. p_price_adj_tab.last LOOP
4687
4688 IF (p_level = 'H' AND
4689 p_price_adj_tab(i).quote_header_id IS NOT NULL AND
4690 p_price_adj_tab(i).quote_line_id IS NULL)
4691 OR
4692 (p_level ='L' AND
4693 p_price_adj_tab(i).quote_header_id IS NOT NULL AND
4694 p_price_adj_tab(i).quote_line_id IS NOT NULL)
4695 THEN
4696
4697
4698 IF p_price_adj_tab(i).operation_code = g_aso_op_code_delete THEN
4699
4700 --Delete all relationships pertaining directly to this quote price adj
4701
4702 IF (l_debug = 'Y') THEN
4703 okc_util.print_trace(1,'p_price_adj_tab - price_adjustment_id '||p_price_adj_tab(i).price_adjustment_id);
4704 END IF;
4705
4706 FOR l_price_adj_rltship IN c_price_adj_rltship (p_q_flag,p_o_flag,
4707 p_price_adj_tab(i).price_adjustment_id,to_number(NULL)) LOOP
4708 ---- populate l_price_adj_rltship_rec with information from l_price_adj_rltship
4709
4710 l_price_adj_rltship_rec.adj_relationship_id := l_price_adj_rltship.adj_relationship_id;
4711 l_price_adj_rltship_rec.quote_line_id := l_price_adj_rltship.line_id;
4712 l_price_adj_rltship_rec.quote_shipment_id := l_price_adj_rltship.shipment_id;
4713 l_price_adj_rltship_rec.price_adjustment_id := l_price_adj_rltship.price_adjustment_id;
4714
4715 l_price_adj_rltship_rec.operation_code := g_aso_op_code_delete;
4716
4717 l_price_adj_rltship_tab(x) := l_price_adj_rltship_rec;
4718 x := x+1;
4719
4720 END LOOP;
4721
4722 -- Update all relationships pertaining indirectly to this quote price adj refered as rltd_price_adj_id
4723 --
4724 FOR l_price_adj_rltship IN c_price_adj_rltship_rltd (p_q_flag, p_o_flag,
4725 p_price_adj_tab(i).price_adjustment_id ) LOOP
4726 ---- populate l_price_adj_rltship_rec with information from l_price_adj_rltship
4727
4728 l_price_adj_rltship_rec.adj_relationship_id := l_price_adj_rltship.adj_relationship_id;
4729 l_price_adj_rltship_rec.quote_line_id := l_price_adj_rltship.line_id;
4730 l_price_adj_rltship_rec.quote_shipment_id := l_price_adj_rltship.shipment_id;
4731 l_price_adj_rltship_rec.price_adjustment_id := l_price_adj_rltship.price_adjustment_id;
4732
4733 l_price_adj_rltship_rec.operation_code := g_aso_op_code_update;
4734 l_price_adj_rltship_rec.rltd_price_adj_id := to_number(NULL);
4735
4736 l_price_adj_rltship_tab(x) := l_price_adj_rltship_rec;
4737 x := x + 1;
4738
4739 END LOOP;
4740 END IF;
4741 END IF;
4742 END LOOP;
4743 END IF;
4744
4745 --
4746 -- Print out the main information of the l_price_adj_rltship_tab variable
4747 --
4748
4749 IF (l_debug = 'Y') THEN
4750 okc_util.print_trace(1,' Count of l_price_adj_rltship_tab = '||l_price_adj_rltship_tab.COUNT);
4751 END IF;
4752
4753 IF l_price_adj_rltship_tab.COUNT > 0 THEN
4754 FOR i IN l_price_adj_rltship_tab.FIRST..l_price_adj_rltship_tab.LAST LOOP
4755
4756 IF (l_debug = 'Y') THEN
4757 okc_util.print_trace(1,' l_price_adj_rltship_tab record '||i);
4758 okc_util.print_trace(1,' ');
4759 okc_util.print_trace(1,'operation code = '||l_price_adj_rltship_tab(i).operation_code);
4760 okc_util.print_trace(1,'price adjustment id = '||l_price_adj_rltship_tab(i).price_adjustment_id);
4761 okc_util.print_trace(1,'quote line id = '||l_price_adj_rltship_tab(i).quote_line_id);
4762 okc_util.print_trace(1,'qte line index = '||l_price_adj_rltship_tab(i).qte_line_index);
4763 okc_util.print_trace(1,'quote shipment id = '||l_price_adj_rltship_tab(i).quote_shipment_id);
4764 okc_util.print_trace(1,'rltd price adj id = '||l_price_adj_rltship_tab(i).rltd_price_adj_id);
4765 END IF;
4766
4767 END LOOP;
4768 END IF;
4769
4770
4771 IF l_price_adj_rltship_tab.COUNT > 0 THEN
4772 FOR i IN l_price_adj_rltship_tab.FIRST..l_price_adj_rltship_tab.LAST LOOP
4773 x_price_adj_rltship_tab(x_price_adj_rltship_tab.COUNT+1):=l_price_adj_rltship_tab(i);
4774 END LOOP;
4775 END IF;
4776
4777 -- x_price_adj_rltship_tab:=l_price_adj_rltship_tab;
4778
4779 IF (l_debug = 'Y') THEN
4780 okc_util.print_trace(1,'---------------------------------------------');
4781 okc_util.print_trace(1,'>> End : Get price adjustment relationship ');
4782 okc_util.print_trace(1,'---------------------------------------------');
4783 END IF;
4784
4785 EXCEPTION
4786
4787 WHEN e_exit THEN
4788 -- Bug#2320635
4789 OKC_API.set_message(G_APP_NAME,
4790 G_UNEXPECTED_ERROR,
4791 G_SQLCODE_TOKEN,
4792 SQLCODE,
4793 G_SQLERRM_TOKEN,
4794 SQLERRM);
4795 IF c_k_price_adj_rltship%ISOPEN THEN
4796 CLOSE c_k_price_adj_rltship;
4797 END IF;
4798
4799 IF c_price_adj_rltship_rltd%ISOPEN THEN
4800 CLOSE c_k_price_adj_rltship;
4801 END IF;
4802
4803 RAISE OKC_API.G_EXCEPTION_ERROR;
4804
4805 END; -- get_price_adj_rltship
4806
4807
4808
4809 -----------------------------------------------------------------------------------------
4810 -- procedure build_pricing_from_k
4811 -----------------------------------------------------------------------------------------
4812
4813 -- Notes for the impact of configuration items on Pricing information
4814 -- for k->Q update and K->O creation.
4815 --
4816 -- Quote Contract
4817 -- ----- --------
4818 -- QL1 <---------------- KL1 Top Model line (Contains sales credit, rule info)
4819 -- |______________ |__KSL1 Top Base line (Contains Price adjustment info)
4820 -- |
4821 -- QL2 <---------------- |- KSL1.1 Config (Contains Price adjustment info)
4822 -- QL3 <---------------- |_ KSL1.2 Config (Contains Price adjustment info)
4823 --
4824 -- The Top model line and the Top base line information is stored into the same
4825 -- quote line as shown above and when the relationship PL/SQL table is constructed
4826 -- the first relationship between KL1 and QL1 is overwritten by the second
4827 -- relationship. Therefore the line id that is against the QL1 is the id
4828 -- of the top base line.So there is no impact of configuration items on
4829 -- Pricing information because all the information is available
4830 --
4831
4832
4833 PROCEDURE build_pricing_from_k(
4834 p_chr_id IN OKC_K_HEADERS_B.id%TYPE,
4835 p_kl_rel_tab IN okc_oc_int_config_pvt.line_rel_tab_type ,
4836 --
4837 p_q_flag IN VARCHAR2 ,
4838 p_qhr_id IN OKX_QUOTE_HEADERS_V.id1%TYPE ,
4839 p_qle_tab IN ASO_QUOTE_PUB.qte_line_tbl_type ,
4840 p_qle_shipment_tab IN ASO_QUOTE_PUB.shipment_tbl_type ,
4841 --
4842 p_o_flag IN VARCHAR2 ,
4843 p_ohr_id IN OKX_ORDER_HEADERS_V.id1%TYPE ,
4844 p_ole_tab IN ASO_QUOTE_PUB.qte_line_tbl_type ,
4845 p_ole_shipment_tab IN ASO_QUOTE_PUB.shipment_tbl_type ,
4846 --
4847 x_hd_price_adj_tab OUT NOCOPY ASO_QUOTE_PUB.price_adj_tbl_type,
4848 x_ln_price_adj_tab OUT NOCOPY ASO_QUOTE_PUB.price_adj_tbl_type,
4849 x_hd_price_adj_attr_tab OUT NOCOPY ASO_QUOTE_PUB.price_adj_attr_tbl_type,
4850 x_ln_price_adj_attr_tab OUT NOCOPY ASO_QUOTE_PUB.price_adj_attr_tbl_type,
4851 x_hd_price_attr_tab OUT NOCOPY ASO_QUOTE_PUB.price_attributes_tbl_type,
4852 x_ln_price_attr_tab OUT NOCOPY ASO_QUOTE_PUB.price_attributes_tbl_type,
4853 x_hd_price_adj_rltship_tab OUT NOCOPY ASO_QUOTE_PUB.price_adj_rltship_tbl_type,
4854 x_ln_price_adj_rltship_tab OUT NOCOPY ASO_QUOTE_PUB.price_adj_rltship_tbl_type,
4855 --
4856 x_return_status OUT NOCOPY VARCHAR2 ) IS
4857
4858 k BINARY_INTEGER;
4859 x_ln_tmp_price_adj_tab ASO_QUOTE_PUB.price_adj_tbl_type;
4860 x_ln_tmp_price_attr_tab ASO_QUOTE_PUB.price_attributes_tbl_type;
4861 l_k_temp_price_adj_tab k_price_adj_tab_type;
4862
4863 BEGIN
4864 --
4865 -- Delete pl/sql tables, so that they are not reused
4866 -- when a connection is used by another client
4867 --
4868 -- housekeeping
4869 --
4870 l_line_tab.DELETE;
4871 l_line_shipment_tab.DELETE;
4872 l_k_price_adj_tab.DELETE;
4873 l_k_temp_price_adj_tab.DELETE;
4874
4875 x_ln_tmp_price_adj_tab.DELETE;
4876 x_ln_tmp_price_attr_tab.DELETE;
4877
4878 x_hd_price_adj_tab.DELETE;
4879 x_ln_price_adj_tab.DELETE;
4880 x_hd_price_adj_attr_tab.DELETE;
4881 x_ln_price_adj_attr_tab.DELETE;
4882 x_hd_price_attr_tab.DELETE;
4883 x_ln_price_attr_tab.DELETE;
4884 x_hd_price_adj_rltship_tab.DELETE;
4885 x_ln_price_adj_rltship_tab.DELETE;
4886
4887 IF p_q_flag = OKC_API.g_true THEN
4888 l_line_tab:=p_qle_tab;
4889 l_line_shipment_tab:=p_qle_shipment_tab;
4890 ELSIF p_o_flag = OKC_API.g_true THEN
4891 l_line_tab:=p_ole_tab;
4892 l_line_shipment_tab:=p_ole_shipment_tab;
4893 END IF;
4894
4895 x_return_status := OKC_API.G_RET_STS_SUCCESS;
4896
4897
4898 ----------------------------------------------------------
4899 -- Select pricing information at the contract header level
4900 ----------------------------------------------------------
4901
4902 --
4903 -- Get the price adjustments into the x_hd_price_adj_tab variable
4904 --
4905 IF (l_debug = 'Y') THEN
4906 okc_util.print_trace(1,'=============================================');
4907 okc_util.print_trace(1,'START : GET PRICE ADJUSTMENTS AT HEADER LEVEL');
4908 okc_util.print_trace(1,'=============================================');
4909 END IF;
4910
4911 get_price_adj(p_chr_id => p_chr_id,
4912 p_cle_id => NULL,
4913 --
4914 p_qhr_id => p_qhr_id,
4915 p_qle_id => NULL,
4916 p_q_flag => p_q_flag,
4917 --
4918 p_ohr_id => p_ohr_id,
4919 p_ole_id => NULL,
4920 p_o_flag => p_o_flag,
4921 --
4922 p_level => 'H',
4923 --
4924 p_nqhr_id => NULL,
4925 p_nqle_idx => NULL,
4926 --
4927 x_k_price_adj_tab => l_k_price_adj_tab,
4928 x_price_adj_tab => x_hd_price_adj_tab );
4929
4930 IF (l_debug = 'Y') THEN
4931 okc_util.print_trace(1,'===========================================');
4932 okc_util.print_trace(1,'END : GET PRICE ADJUSTMENTS AT HEADER LEVEL');
4933 okc_util.print_trace(1,'===========================================');
4934 END IF;
4935
4936 --
4937 -- Get the price adjustment attributes into the x_hd_price_adj_attr_tab variable
4938 --
4939
4940 IF (l_debug = 'Y') THEN
4941 okc_util.print_trace(1,'=======================================================');
4942 okc_util.print_trace(1,'START : GET PRICE ADJUSTMENT ATTRIBUTES AT HEADER LEVEL');
4943 okc_util.print_trace(1,'=======================================================');
4944 END IF;
4945
4946 get_price_adj_attr ( p_price_adj_tab => x_hd_price_adj_tab,
4947 p_k_price_adj_tab => l_k_price_adj_tab,
4948 p_q_flag => p_q_flag,
4949 p_o_flag => p_o_flag,
4950 p_level => 'H',
4951 x_price_adj_attr_tab => x_hd_price_adj_attr_tab );
4952
4953 IF (l_debug = 'Y') THEN
4954 okc_util.print_trace(1,'=======================================================');
4955 okc_util.print_trace(1,' END : GET PRICE ADJUSTMENT ATTRIBUTES AT HEADER LEVEL');
4956 okc_util.print_trace(1,'=======================================================');
4957 END IF;
4958
4959 --
4960 -- Get the price adjustment relationship into the x_hd_price_adj_rltship_tab variable
4961 --
4962
4963 IF (l_debug = 'Y') THEN
4964 okc_util.print_trace(1,'=========================================================');
4965 okc_util.print_trace(1,'START : GET PRICE ADJUSTMENT RELATIONSHIP AT HEADER LEVEL');
4966 okc_util.print_trace(1,'=========================================================');
4967 END IF;
4968
4969 get_price_adj_rltship ( p_price_adj_tab => x_hd_price_adj_tab,
4970 p_k_price_adj_tab => l_k_price_adj_tab,
4971 --
4972 p_line_tab => l_line_tab,
4973 p_kl_rel_tab => p_kl_rel_tab,
4974 p_line_shipment_tab => l_line_shipment_tab,
4975 --
4976 p_q_flag => p_q_flag,
4977 p_o_flag => p_o_flag,
4978 p_level => 'H',
4979 --
4980 x_price_adj_rltship_tab => x_hd_price_adj_rltship_tab );
4981
4982 IF (l_debug = 'Y') THEN
4983 okc_util.print_trace(1,'=========================================================');
4984 okc_util.print_trace(1,' END : GET PRICE ADJUSTMENT RELATIONSHIP AT HEADER LEVEL');
4985 okc_util.print_trace(1,'=========================================================');
4986 END IF;
4987
4988 --
4989 -- Get the price attributes into the x_hd_price_attr_tab variable
4990 --
4991 IF (l_debug = 'Y') THEN
4992 okc_util.print_trace(1,'============================================');
4993 okc_util.print_trace(1,'START : GET PRICE ATTRIBUTES AT HEADER LEVEL');
4994 okc_util.print_trace(1,'============================================');
4995 END IF;
4996
4997 get_price_attr(p_chr_id => p_chr_id,
4998 p_cle_id => NULL,
4999 --
5000 p_qhr_id => p_qhr_id,
5001 p_qle_id => NULL,
5002 p_q_flag => p_q_flag,
5003 --
5004 p_ohr_id => p_ohr_id,
5005 p_ole_id => NULL,
5006 p_o_flag => p_o_flag,
5007 --
5008 p_level => 'H',
5009
5010 p_nqhr_id => NULL,
5011 p_nqle_idx => NULL,
5012 --
5013 x_price_attr_tab => x_hd_price_attr_tab );
5014
5015 IF (l_debug = 'Y') THEN
5016 okc_util.print_trace(1,'============================================');
5017 okc_util.print_trace(1,' END : GET PRICE ATTRIBUTES AT HEADER LEVEL');
5018 okc_util.print_trace(1,'============================================');
5019 END IF;
5020
5021 ----------------------------------------------------------
5022 -- Select pricing information at the contract Line level
5023 ----------------------------------------------------------
5024
5025
5026 --
5027 -- At this point processing the price adjustments at the header level
5028 -- has been completed and the contents of l_k_price_adj_tab can be
5029 -- deleted as it contains data pertaining to header level
5030 --
5031 l_k_price_adj_tab.DELETE;
5032
5033
5034
5035 --
5036 -- Select price adjustments into the x_ln_price_adj_tab variable
5037 --
5038
5039 IF (l_debug = 'Y') THEN
5040 okc_util.print_trace(1,'=============================================');
5041 okc_util.print_trace(1,'START : GET PRICE ADJUSTMENTS AT LINE LEVEL');
5042 okc_util.print_trace(1,'=============================================');
5043 END IF;
5044
5045 IF l_line_tab.FIRST IS NOT NULL THEN
5046 FOR i IN l_line_tab.FIRST..l_line_tab.LAST LOOP
5047
5048 IF l_line_tab(i).operation_code= g_aso_op_code_create THEN
5049
5050 -- okc_util.print_trace(1,'operation code '||l_line_tab(i).operation_code);
5051 --
5052 -- Also the index value i is the same in l_line_tab and p_kl_rel_tab because
5053 -- when the quote line table was populated px_k2q_line_id(l_ql).q_line_idx := l_ql
5054 -- the value of l_ql,q_line_idx are the same
5055 --
5056
5057 get_price_adj(p_chr_id => p_chr_id,
5058 p_cle_id => p_kl_rel_tab(i).k_line_id,
5059 --
5060 p_qhr_id => NULL,
5061 p_qle_id => NULL,
5062 p_q_flag => p_q_flag,
5063 --
5064 p_ohr_id => NULL,
5065 p_ole_id => NULL,
5066 p_o_flag => p_o_flag,
5067 --
5068 p_level =>'L',
5069 --
5070 p_nqhr_id => p_qhr_id,
5071 p_nqle_idx => i,
5072 --
5073 x_k_price_adj_tab => l_k_temp_price_adj_tab,
5074 x_price_adj_tab => x_ln_tmp_price_adj_tab);
5075
5076 ELSIF l_line_tab(i).operation_code= g_aso_op_code_update THEN
5077
5078 -- okc_util.print_trace(1,'operation code '||l_line_tab(i).operation_code);
5079
5080 get_price_adj(p_chr_id => p_chr_id,
5081 p_cle_id => p_kl_rel_tab(i).k_line_id,
5082 --
5083 p_qhr_id => p_qhr_id,
5084 p_qle_id => l_line_tab(i).quote_line_id,
5085 p_q_flag => p_q_flag,
5086 --
5087 p_ohr_id => p_ohr_id,
5088 p_ole_id => l_line_tab(i).quote_line_id,
5089 --Not valid now in case of an Order update from a contract
5090 --Will need to be modified when K -> O for update will be
5091 --required to be developed
5092
5093 p_o_flag => p_o_flag,
5094 --
5095 p_level =>'L',
5096 --
5097 p_nqhr_id => NULL,
5098 p_nqle_idx => NULL,
5099 --
5100 x_k_price_adj_tab => l_k_temp_price_adj_tab,
5101 x_price_adj_tab => x_ln_tmp_price_adj_tab);
5102
5103 ELSIF l_line_tab(i).operation_code= g_aso_op_code_delete THEN
5104
5105 -- okc_util.print_trace(1,'operation code '||l_line_tab(i).operation_code);
5106
5107 get_price_adj(p_chr_id => NULL,
5108 p_cle_id => NULL,
5109 --
5110 p_qhr_id => p_qhr_id,
5111 p_qle_id => l_line_tab(i).quote_line_id,
5112 p_q_flag => p_q_flag,
5113 --
5114 p_ohr_id => p_ohr_id,
5115 p_ole_id => l_line_tab(i).quote_line_id,
5116 --Not valid now in case of an Order update from a contract
5117 --Will need to be modified when K -> O for update will be
5118 --required to be developed
5119
5120 p_o_flag => p_o_flag,
5121 --
5122 p_level =>'L',
5123 --
5124 p_nqhr_id => NULL,
5125 p_nqle_idx => NULL,
5126 --
5127 x_k_price_adj_tab => l_k_temp_price_adj_tab,
5128 x_price_adj_tab => x_ln_tmp_price_adj_tab);
5129 END IF;
5130
5131 --
5132 -- The processing below, is required to ensure that for every line processed
5133 -- the contents of the temp tables are loaded to the main tables, thereby
5134 -- ensuring that the table is not overwritten
5135 --
5136
5137 IF l_k_temp_price_adj_tab.COUNT > 0 THEN
5138 FOR k IN l_k_temp_price_adj_tab.FIRST..l_k_temp_price_adj_tab.LAST LOOP
5139 l_k_price_adj_tab(l_k_price_adj_tab.COUNT+1) := l_k_temp_price_adj_tab(k);
5140 END LOOP;
5141 END IF;
5142
5143 IF x_ln_tmp_price_adj_tab.COUNT > 0 THEN
5144 FOR k IN x_ln_tmp_price_adj_tab.FIRST..x_ln_tmp_price_adj_tab.LAST LOOP
5145 x_ln_price_adj_tab(x_ln_price_adj_tab.COUNT+1) := x_ln_tmp_price_adj_tab(k);
5146 END LOOP;
5147 END IF;
5148
5149
5150 END LOOP;
5151 END IF;
5152
5153 IF (l_debug = 'Y') THEN
5154 okc_util.print_trace(1,'=============================================');
5155 okc_util.print_trace(1,' END : GET PRICE ADJUSTMENTS AT LINE LEVEL');
5156 okc_util.print_trace(1,'=============================================');
5157 END IF;
5158
5159 --
5160 -- Select price adjustment attributes into the x_ln_price_adj_attr_tab variable
5161 --
5162
5163 IF (l_debug = 'Y') THEN
5164 okc_util.print_trace(1,'=======================================================');
5165 okc_util.print_trace(1,'START : GET PRICE ADJUSTMENT ATTRIBUTES AT LINE LEVEL');
5166 okc_util.print_trace(1,'=======================================================');
5167 END IF;
5168
5169 get_price_adj_attr ( p_price_adj_tab => x_ln_price_adj_tab,
5170 p_k_price_adj_tab => l_k_price_adj_tab,
5171 p_q_flag => p_q_flag,
5172 p_o_flag => p_o_flag,
5173 p_level => 'L',
5174 x_price_adj_attr_tab => x_ln_price_adj_attr_tab);
5175
5176 IF (l_debug = 'Y') THEN
5177 okc_util.print_trace(1,'=======================================================');
5178 okc_util.print_trace(1,' END : GET PRICE ADJUSTMENT ATTRIBUTES AT LINE LEVEL');
5179 okc_util.print_trace(1,'=======================================================');
5180 END IF;
5181
5182 --
5183 -- Select price adjustment relationship into the x_ln_price_adj_rltship_tab variable
5184 --
5185
5186 IF (l_debug = 'Y') THEN
5187 okc_util.print_trace(1,'=========================================================');
5188 okc_util.print_trace(1,'START : GET PRICE ADJUSTMENT RELATIONSHIP AT LINE LEVEL');
5189 okc_util.print_trace(1,'=========================================================');
5190 END IF;
5191
5192 get_price_adj_rltship( p_price_adj_tab => x_ln_price_adj_tab,
5193 p_k_price_adj_tab => l_k_price_adj_tab,
5194 --
5195 p_line_tab => l_line_tab,
5196 p_kl_rel_tab => p_kl_rel_tab,
5197 p_line_shipment_tab => l_line_shipment_tab,
5198 --
5199 p_q_flag => p_q_flag,
5200 p_o_flag => p_o_flag,
5201 p_level => 'L',
5202 --
5203 x_price_adj_rltship_tab => x_ln_price_adj_rltship_tab);
5204
5205 IF (l_debug = 'Y') THEN
5206 okc_util.print_trace(1,'=========================================================');
5207 okc_util.print_trace(1,' END : GET PRICE ADJUSTMENT RELATIONSHIP AT LINE LEVEL');
5208 okc_util.print_trace(1,'=========================================================');
5209 END IF;
5210
5211 --
5212 -- Select price attributes into the x_ln_price_attr_tab variable
5213 --
5214
5215 IF (l_debug = 'Y') THEN
5216 okc_util.print_trace(1,'============================================');
5217 okc_util.print_trace(1,'START : GET PRICE ATTRIBUTES AT LINE LEVEL');
5218 okc_util.print_trace(1,'============================================');
5219 END IF;
5220
5221 IF l_line_tab.FIRST IS NOT NULL THEN
5222 FOR i IN l_line_tab.FIRST..l_line_tab.LAST LOOP
5223
5224 IF l_line_tab(i).operation_code= g_aso_op_code_create THEN
5225
5226 -- okc_util.print_trace(1,'operation code '||l_line_tab(i).operation_code);
5227 --
5228 -- Also the index value i is the same in l_line_tab and p_kl_rel_tab because
5229 -- when the quote line table was populated px_k2q_line_id(l_ql).q_line_idx := l_ql
5230 -- the value of l_ql,q_line_idx are the same
5231 --
5232
5233 get_price_attr(p_chr_id => p_chr_id,
5234 p_cle_id => p_kl_rel_tab(i).k_line_id,
5235 --
5236 p_qhr_id => NULL,
5237 p_qle_id => NULL,
5238 p_q_flag => p_q_flag,
5239 --
5240 p_ohr_id => NULL,
5241 p_ole_id => NULL,
5242 p_o_flag => p_o_flag,
5243 --
5244 p_level =>'L',
5245 --
5246 p_nqhr_id => p_qhr_id,
5247 p_nqle_idx => i, -- px_k2q_line_rel_tbl(i).q_line_idx = i
5248 -- px_qte_line_tbl(i).line_number = i
5249 --
5250 x_price_attr_tab => x_ln_tmp_price_attr_tab);
5251
5252 ELSIF l_line_tab(i).operation_code= g_aso_op_code_update THEN
5253
5254 -- okc_util.print_trace(1,'operation code '||l_line_tab(i).operation_code);
5255
5256 get_price_attr(p_chr_id => p_chr_id,
5257 p_cle_id => p_kl_rel_tab(i).k_line_id,
5258 --
5259 p_qhr_id => p_qhr_id,
5260 p_qle_id => l_line_tab(i).quote_line_id,
5261 p_q_flag => p_q_flag,
5262 --
5263 p_ohr_id => p_ohr_id,
5264 p_ole_id => l_line_tab(i).quote_line_id,
5265 --Not valid now in case of an Order update from a contract
5266 --Will need to be modified when K -> O for update will be
5267 --required to be developed
5268
5269 p_o_flag => p_o_flag,
5270 --
5271 p_level =>'L',
5272 --
5273 p_nqhr_id => NULL,
5274 p_nqle_idx => NULL,
5275 --
5276 x_price_attr_tab => x_ln_tmp_price_attr_tab);
5277
5278 ELSIF l_line_tab(i).operation_code= g_aso_op_code_delete THEN
5279
5280 -- okc_util.print_trace(1,'operation code '||l_line_tab(i).operation_code);
5281
5282 get_price_attr(p_chr_id => NULL,
5283 p_cle_id => NULL,
5284 --
5285 p_qhr_id => p_qhr_id,
5286 p_qle_id => l_line_tab(i).quote_line_id,
5287 p_q_flag => p_q_flag,
5288 --
5289 p_ohr_id => p_ohr_id,
5290 p_ole_id => l_line_tab(i).quote_line_id,
5291 --Not valid now in case of an Order update from a contract
5292 --Will need to be modified when K -> O for update will be
5293 --required to be developed
5294
5295 p_o_flag => p_o_flag,
5296 --
5297 p_level =>'L',
5298 --
5299 p_nqhr_id => NULL,
5300 p_nqle_idx => NULL,
5301 --
5302 x_price_attr_tab => x_ln_tmp_price_attr_tab);
5303 END IF;
5304
5305 IF x_ln_tmp_price_attr_tab.COUNT > 0 THEN
5306 FOR k IN x_ln_tmp_price_attr_tab.FIRST..x_ln_tmp_price_attr_tab.LAST LOOP
5307 x_ln_price_attr_tab(x_ln_price_attr_tab.COUNT+1) := x_ln_tmp_price_attr_tab(k);
5308 END LOOP;
5309 END IF;
5310
5311 END LOOP;
5312 END IF;
5313
5314 IF (l_debug = 'Y') THEN
5315 okc_util.print_trace(1,'============================================');
5316 okc_util.print_trace(1,' END : GET PRICE ATTRIBUTES AT LINE LEVEL');
5317 okc_util.print_trace(1,'============================================');
5318 END IF;
5319
5320 EXCEPTION
5321
5322 WHEN e_exit THEN
5323
5324 x_return_status := OKC_API.G_RET_STS_ERROR;
5325
5326
5327 WHEN OTHERS THEN
5328 OKC_API.set_message(G_APP_NAME,
5329 G_UNEXPECTED_ERROR,
5330 G_SQLCODE_TOKEN,
5331 SQLCODE,
5332 G_SQLERRM_TOKEN,
5333 SQLERRM );
5334 print_error(2);
5335
5336 -- notify caller of an unexpected error
5337
5338 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
5339
5340 END build_pricing_from_k;
5341
5342
5343 -- =========================================================================
5344 -- =========================================================================
5345 -- END OF KTQ or KTO PRICING INFORMATION CREATION
5346 -- or UPDATE
5347 -- =========================================================================
5348 -- =========================================================================
5349
5350
5351 END OKC_OC_INT_PRICING_PVT;