[Home] [Help]
PACKAGE BODY: APPS.OKC_PRICE_PUB
Source
1 PACKAGE BODY OKC_PRICE_PUB AS
2 /* $Header: OKCPPREB.pls 120.0 2005/05/26 09:41:21 appldev noship $ */
3 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
4
5 G_NO_PRICING_RULE Varchar2(1) := 'N'; -- No Pricing Rule attached
6 G_NO_PRICE_LIST Varchar2(1) := 'E'; -- Pricing Rule attached but no Price List
7 G_NO_LOV_PRICE_LIST Varchar2(1) := 'V'; -- Pricing Rule attached but NO LOV Price
8 -- List found, possible for Buy Contracts
9 G_OK_PRICE_LIST Varchar2(1) := 'S'; -- Price List Found
10
11 ---smhanda added ---------------------------------------------------------------------
12 --------------------------------------------------------------------
13 --FUNCTION - GET_LSE_SOURCE_VALUE
14 -- This function is used in mapping of attributes between QP and OKC
15 -- The calls to this function will be made by QP Engine to get values for
16 --various Qualifiers and Pricing Attributes
17 -- p_lse_tbl - Global Table holding various OKX_SOURCES and their values for lse
18 -- p_registered_source - The source for which value should be returned
19 -- Returns the value for the p_registered_source
20 ----------------------------------------------------------------------------
21 FUNCTION Get_LSE_SOURCE_VALUE (
22 p_lse_tbl IN global_lse_tbl_type,
23 p_registered_source IN VARCHAR2)
24 RETURN VARCHAR2 IS
25 BEGIN
26 return OKC_PRICE_PVT.Get_LSE_SOURCE_VALUE (
27 p_lse_tbl => p_lse_tbl,
28 p_registered_source => p_registered_source);
29 END Get_LSE_SOURCE_VALUE;
30
31 -----------------------------------------------------------------------------
32 --FUNCTION - GET_RUL_SOURCE_VALUE
33 -- This function is used in mapping of attributes between QP and OKC
34 -- The calls to this function will be made by QP Engine to get values for
35 --various Qualifiers and Pricing Attributes
36 -- p_rul_tbl - Global Table holding various OKX_SOURCES and their values for rules
37 -- p_registered_code - The rule code for which value should be returned
38 -- p_registered_source - The source for which value should be returned
39 -- Returns the value for the p_registered_source+p_registered_code
40 ----------------------------------------------------------------------------
41 FUNCTION Get_RUL_SOURCE_VALUE (
42 p_rul_tbl IN global_rprle_tbl_type,
43 p_registered_code IN varchar2,
44 p_registered_source IN VARCHAR2)
45 RETURN VARCHAR2 IS
46 BEGIN
47 return OKC_PRICE_PVT.Get_RUL_SOURCE_VALUE (
48 p_rul_tbl => p_rul_tbl,
49 p_registered_code => p_registered_code,
50 p_registered_source => p_registered_source);
51 END Get_RUL_SOURCE_VALUE;
52
53
54 ------------------------------------------------------------------------------
55 --FUNCTION - GET_PRLE_SOURCE_VALUE
56 -- This function is used in mapping of attributes between QP and OKC
57 -- The calls to this function will be made by QP Engine to get values for
58 --various Qualifiers and Pricing Attributes
59 -- p_prle_tbl - Global Table holding various OKX_SOURCES and their values for rules
60 -- p_registered_role - The role code for which value should be returned
61 -- p_registered_source - The source for which value should be returned
62 -- Returns the value for the p_registered_source+p_registered_role
63 ----------------------------------------------------------------------------
64 FUNCTION Get_PRLE_SOURCE_VALUE (
65 p_prle_tbl IN global_rprle_tbl_type,
66 p_registered_code IN varchar2,
67 p_registered_source IN VARCHAR2)
68 RETURN VARCHAR2 IS
69 BEGIN
70 return OKC_PRICE_PVT.Get_PRLE_SOURCE_VALUE(
71 p_prle_tbl => p_prle_tbl,
72 p_registered_code => p_registered_code,
73 p_registered_source => p_registered_source);
74 END Get_PRLE_SOURCE_VALUE;
75
76 ------------------------------------------------------------------------------
77 --FUNCTION - ROUND_PRICE
78 -- This function is used to round the price (parameter p_price) according to rules
79 -- of currency (p_cur_code - currency code)
80 ----------------------------------------------------------------------------
81 FUNCTION ROUND_PRICE(p_price NUMBER, p_cur_code VARCHAR2) RETURN NUMBER IS
82 l_price NUMBER := p_price;
83 Cursor fnd_cur IS
84 SELECT Minimum_Accountable_Unit,
85 Precision,
86 Extended_Precision
87 FROM FND_CURRENCIES
88 WHERE Currency_Code = p_cur_code ;
89 l_mau FND_CURRENCIES.MINIMUM_ACCOUNTABLE_UNIT%TYPE;
90 l_sp FND_CURRENCIES.PRECISION%TYPE;
91 l_ep FND_CURRENCIES.EXTENDED_PRECISION%TYPE;
92 BEGIN
93 open fnd_cur;
94 fetch fnd_cur into l_mau,l_sp,l_ep;
95 close fnd_cur;
96
97 If (l_mau is not null) Then
98 If (l_mau < 0.00001) Then
99 return ( round(l_price,5) );
100 Else
101 return ( round(l_price/l_mau) * l_mau );
102 End If;
103 Elsif l_sp is not null then
104 If (l_sp > 5) Then
105 return ( round(l_price,5) );
106 Else
107 return ( round(l_price, l_sp) );
108 End If;
109 Else
110 return ( round(l_price,5) );
111 End If;
112 EXCEPTION
113 WHEN NO_DATA_FOUND THEN
114 return l_price;
115 WHEN OTHERS THEN
116 return l_price;
117 END ROUND_PRICE;
118
119
120 ----------------------------------------------------------------------------
121 -- CALCULATE_PRICE
122 -- This procedure will calculate the price for the sent in line/header
123 -- px_cle_price_tbl returns the priced line ids and thier prices
124 -- p_level tells whether line level or header level
125 -- possible value 'L','H','QA' DEFAULT 'L'
126 --p_calc_flag 'B'(Both -calculate and search),'C'(Calculate Only), 'S' (Search only)
127 ----------------------------------------------------------------------------
128 PROCEDURE CALCULATE_price(
129 p_api_version IN NUMBER ,
130 p_init_msg_list IN VARCHAR2 ,
131 p_CHR_ID IN NUMBER,
132 p_Control_Rec IN OKC_CONTROL_REC_TYPE,
133 px_req_line_tbl IN OUT NOCOPY QP_PREQ_GRP.LINE_TBL_TYPE,
134 px_Req_qual_tbl IN OUT NOCOPY QP_PREQ_GRP.QUAL_TBL_TYPE,
135 px_Req_line_attr_tbl IN OUT NOCOPY QP_PREQ_GRP.LINE_ATTR_TBL_TYPE,
136 px_Req_LINE_DETAIL_tbl IN OUT NOCOPY QP_PREQ_GRP.LINE_DETAIL_TBL_TYPE,
137 px_Req_LINE_DETAIL_qual_tbl IN OUT NOCOPY QP_PREQ_GRP.LINE_DETAIL_QUAL_TBL_TYPE,
138 px_Req_LINE_DETAIL_attr_tbl IN OUT NOCOPY QP_PREQ_GRP.LINE_DETAIL_ATTR_TBL_TYPE,
139 px_Req_RELATED_LINE_TBL IN OUT NOCOPY QP_PREQ_GRP.RELATED_LINES_TBL_TYPE,
140 px_CLE_PRICE_TBL IN OUT NOCOPY CLE_PRICE_TBL_TYPE,
141 x_return_status OUT NOCOPY VARCHAR2,
142 x_msg_count OUT NOCOPY NUMBER,
143 x_msg_data OUT NOCOPY VARCHAR2) IS
144 BEGIN
145 OKC_PRICE_PVT.CALCULATE_price(
146 p_api_version => p_api_version,
147 p_init_msg_list => p_init_msg_list ,
148 p_CHR_ID => p_CHR_ID,
149 p_Control_Rec => p_Control_Rec,
150 px_req_line_tbl => px_req_line_tbl,
151 px_Req_qual_tbl => px_Req_qual_tbl,
152 px_Req_line_attr_tbl => px_Req_line_attr_tbl,
153 px_Req_LINE_DETAIL_tbl => px_Req_LINE_DETAIL_tbl,
154 px_Req_LINE_DETAIL_qual_tbl => px_Req_LINE_DETAIL_qual_tbl,
155 px_Req_LINE_DETAIL_attr_tbl => px_Req_LINE_DETAIL_attr_tbl,
156 px_Req_RELATED_LINE_TBL => px_Req_RELATED_LINE_TBL,
157 px_CLE_PRICE_TBL => px_CLE_PRICE_TBL,
158 x_return_status => x_return_status,
159 x_msg_count => x_msg_count,
160 x_msg_data => x_msg_data);
161 END CALCULATE_PRICE;
162
163 --------------------------------------------------------------------------
164 -- Update_Contract_price
165 -- This procedure will calculate the price for all the Priced lines in a contract
166 -- while calculating whether header level adjustments are to be considrerd
167 -- or not will be taken care of by px_control_rec.p_level (possible values 'L','H','QA')
168 -- 'L' line only, 'H' Header and all the lines ,'QA' QA only
169 -- p_chr_id - id of the header
170 -- x_chr_net_price - estimated amount on header
171 ----------------------------------------------------------------------------
172 PROCEDURE Update_CONTRACT_price(
173 p_api_version IN NUMBER,
174 p_init_msg_list IN VARCHAR2 ,
175 p_commit IN VARCHAR2 ,
176 p_CHR_ID IN NUMBER,
177 px_Control_Rec IN OUT NOCOPY PRICE_CONTROL_REC_TYPE,
178 x_CLE_PRICE_TBL OUT NOCOPY CLE_PRICE_TBL_TYPE,
179 x_chr_net_price OUT NOCOPY NUMBER,
180 x_return_status OUT NOCOPY VARCHAR2,
181 x_msg_count OUT NOCOPY NUMBER,
182 x_msg_data OUT NOCOPY VARCHAR2) IS
183
184 l_control_rec OKC_CONTROL_REC_TYPE;
185 BEGIN
186
187 l_control_rec.p_Request_Type_Code := px_Control_Rec.p_Request_Type_Code;
188 l_control_rec.p_negotiated_changed := px_Control_Rec.p_negotiated_changed;
189 l_control_rec.p_level := px_Control_Rec.p_level ;
190 l_control_rec.p_calc_flag := px_Control_Rec.p_calc_flag ;
191 l_control_rec.p_config_yn := px_Control_Rec.p_config_yn;
192 l_control_rec.p_top_model_id := px_Control_Rec.p_top_model_id;
193
194 OKC_PRICE_PVT.Update_CONTRACT_price(
195 p_api_version => p_api_version,
196 p_init_msg_list => p_init_msg_list,
197 p_commit => p_commit,
198 p_CHR_ID => p_CHR_ID,
199 px_Control_Rec => l_Control_Rec,
200 x_CLE_PRICE_TBL => x_CLE_PRICE_TBL,
201 x_chr_net_price => x_chr_net_price,
202 x_return_status => x_return_status,
203 x_msg_count => x_msg_count,
204 x_msg_data => x_msg_data);
205 END UPDATE_CONTRACT_PRICE;
206
207 ----------------------------------------------------------------------------
208 -- Update_Line_price
209 -- This procedure will calculate the price for all the Priced lines below sent in line
210 -- Called when a line is updated in the form
211 -- p_cle_id - id of the line updated
212 -- p_chr_id - id of the header
213 -- p_lowest_level Possible values 0(p_cle_id not null and this line is subline),
214 -- 1(p_cle_id not null and this line is upper line),
215 -- -1(update all lines)
216 -- -2(update all lines and header)
217 -- DEFAULT -2
218 --
219 --px_chr_list_price IN OUT -holds the total line list price, for right value pass in the existing value,
220 --px_chr_net_price IN OUT -holds the total line net price, for right value pass in the existing value
221
222
223 ----------------------------------------------------------------------------
224 PROCEDURE Update_LINE_price(
225 p_api_version IN NUMBER,
226 p_init_msg_list IN VARCHAR2 ,
227 p_commit IN VARCHAR2 ,
228 p_CHR_ID IN NUMBER,
229 p_cle_id IN NUMBER ,
230 p_lowest_level IN NUMBER ,
231 px_Control_Rec IN OUT NOCOPY PRICE_CONTROL_REC_TYPE,
232 px_chr_list_price IN OUT NOCOPY NUMBER,
233 px_chr_net_price IN OUT NOCOPY NUMBER,
234 x_CLE_PRICE_TBL OUT NOCOPY CLE_PRICE_TBL_TYPE,
235 px_cle_amt IN OUT NOCOPY NUMBER,
236 x_return_status OUT NOCOPY VARCHAR2,
237 x_msg_count OUT NOCOPY NUMBER,
238 x_msg_data OUT NOCOPY VARCHAR2)IS
239
240 l_control_rec OKC_CONTROL_REC_TYPE;
241 BEGIN
242
243 l_control_rec.p_Request_Type_Code := px_Control_Rec.p_Request_Type_Code;
244 l_control_rec.p_negotiated_changed := px_Control_Rec.p_negotiated_changed;
245 l_control_rec.p_level := px_Control_Rec.p_level ;
246 l_control_rec.p_calc_flag := px_Control_Rec.p_calc_flag ;
247 l_control_rec.p_config_yn := px_Control_Rec.p_config_yn;
248 l_control_rec.p_top_model_id := px_Control_Rec.p_top_model_id;
249
250 OKC_PRICE_PVT.Update_LINE_price(
251 p_api_version => p_api_version,
252 p_init_msg_list => p_init_msg_list,
253 p_commit => p_commit,
254 p_CHR_ID => p_CHR_ID,
255 p_cle_id => p_cle_id,
256 p_lowest_level => p_lowest_level,
257 px_Control_Rec => l_Control_Rec,
258 px_chr_list_price => px_chr_list_price,
259 px_chr_net_price => px_chr_net_price,
260 x_CLE_PRICE_TBL => x_CLE_PRICE_TBL,
261 px_cle_amt => px_cle_amt,
262 x_return_status => x_return_status,
263 x_msg_count => x_msg_count,
264 x_msg_data => x_msg_data);
265 END UPDATE_LINE_PRICE;
266
267 ----------------------------------------------------------------------------
268 -- GET_MANUAL_ADJUSTMENTS
269 -- This procedure will return all the manual adjustments that qualify for the
270 -- sent in lines and header
271 -- To get adjustments for a line pass p_cle_id and p_control_rec.p_level='L'
272 -- To get adjustments for a Header pass p_cle_id as null and p_control_rec.p_level='H'
273 ----------------------------------------------------------------------------
274 PROCEDURE get_manual_adjustments(
275 p_api_version IN NUMBER,
276 p_init_msg_list IN VARCHAR2 ,
277 p_CHR_ID IN NUMBER,
278 p_cle_id IN number ,
279 p_Control_Rec IN PRICE_CONTROL_REC_TYPE,
280 x_ADJ_tbl OUT NOCOPY MANUAL_Adj_Tbl_Type,
281 x_return_status OUT NOCOPY VARCHAR2,
282 x_msg_count OUT NOCOPY NUMBER,
283 x_msg_data OUT NOCOPY VARCHAR2) IS
284
285 l_control_rec OKC_CONTROL_REC_TYPE;
286 BEGIN
287
288 l_control_rec.p_Request_Type_Code := p_Control_Rec.p_Request_Type_Code;
289 l_control_rec.p_negotiated_changed := p_Control_Rec.p_negotiated_changed;
290 l_control_rec.p_level := p_Control_Rec.p_level ;
291 l_control_rec.p_calc_flag := p_Control_Rec.p_calc_flag ;
292 l_control_rec.p_config_yn := p_Control_Rec.p_config_yn;
293 l_control_rec.p_top_model_id := p_Control_Rec.p_top_model_id;
294
295 OKC_PRICE_PVT.get_manual_adjustments(
296 p_api_version => p_api_version,
297 p_init_msg_list => p_init_msg_list,
298 p_CHR_ID => p_CHR_ID,
299 p_cle_id => p_cle_id,
300 p_Control_Rec => l_Control_Rec,
301 x_ADJ_tbl => x_ADJ_tbl,
302 x_return_status => x_return_status,
303 x_msg_count => x_msg_count,
304 x_msg_data => x_msg_data);
305 END GET_MANUAL_ADJUSTMENTS;
306 ---end smhanda added------------------------------------------------------------------
307
308 Function Get_Line_Rule(p_cle_id Number,
309 p_category Varchar2,
310 p_object_code Varchar2)
311 Return Varchar2 IS
312 cursor c1 Is
313 select rul.object1_id1
314 from okc_rules_b rul,
315 okc_rule_groups_b rgp
316 where rul.rgp_id = rgp.id
317 and rul.rule_information_category = p_category
318 and rul.jtot_object1_code = p_object_code
319 and rgp.cle_id = p_cle_id;
320 l_object_id okc_rules_b.object1_id1%TYPE;
321 BEGIN
322 -- dbms_output.put_line('Get_Line_Rule');
323 Open c1;
324 Fetch c1 Into l_object_id;
325 If c1%notfound Then
326 l_object_id := Null;
327 End If;
328 Close c1;
329 return(l_object_id);
330 END Get_Line_Rule;
331
332 Function Get_Line_Pricing_Rule(p_cle_id Number,
333 p_category Varchar2,
334 x_return_status OUT NOCOPY Varchar2)
335 Return Varchar2 IS
336 cursor c1 Is
337 select rul.object1_id1,
338 rul.jtot_object1_code
339 from okc_rules_b rul,
340 okc_rule_groups_b rgp
341 where rul.rgp_id = rgp.id
342 and rul.rule_information_category = p_category
343 and rgp.cle_id = p_cle_id;
344 l_object_id okc_rules_b.object1_id1%TYPE;
345 l_jtot_object okc_rules_b.jtot_object1_code%TYPE;
346 l_row_notfound Boolean;
347 BEGIN
348 -- dbms_output.put_line('Get_Line_Pricing_Rule');
349 Open c1;
350 Fetch c1 Into l_object_id, l_jtot_object;
351 l_row_notfound := c1%NotFound;
352 Close c1;
353 If l_row_notfound Then
354 x_return_status := G_NO_PRICING_RULE;
355 Elsif l_jtot_object = G_JTF_NOLOV Then
356 x_return_status := G_NO_LOV_PRICE_LIST;
357 Elsif l_object_id Is Null Then
358 x_return_status := G_NO_PRICE_LIST;
359 Else
360 x_return_status := G_OK_PRICE_LIST;
361 End If;
362 return(l_object_id);
363 END Get_Line_Pricing_Rule;
364
365 Function Get_Hdr_Rule(p_chr_id NUMBER,
366 p_category VARCHAR2,
367 p_object_code VARCHAR2)
368 Return Varchar2 Is
369 cursor c1 Is
370 select rul.object1_id1
371 from okc_rules_b rul,
372 okc_rule_groups_b rgp
373 where rul.rgp_id = rgp.id
374 and rul.rule_information_category = p_category
375 and rul.jtot_object1_code = p_object_code
376 and rgp.chr_id = p_chr_id;
377 l_object_id okc_rules_b.object1_id1%TYPE;
378 l_jtot_object okc_rules_b.jtot_object1_code%TYPE;
379 BEGIN
380 -- dbms_output.put_line('Get_Hdr_Rule');
381 Open c1;
382 Fetch c1 Into l_object_id;
383 If c1%notfound Then
384 l_object_id := Null;
385 End If;
386 Close c1;
387 return(l_object_id);
388 END Get_Hdr_Rule;
389
390 Function Get_Hdr_Pricing_Rule(p_chr_id NUMBER,
391 p_category VARCHAR2,
392 x_return_status OUT NOCOPY Varchar2)
393 Return Varchar2 Is
394 cursor c1 Is
395 select rul.object1_id1,
396 rul.jtot_object1_code
397 from okc_rules_b rul,
398 okc_rule_groups_b rgp
399 where rul.rgp_id = rgp.id
400 and rul.rule_information_category = p_category
401 and rgp.chr_id = p_chr_id;
402 l_object_id okc_rules_b.object1_id1%TYPE;
403 l_jtot_object okc_rules_b.jtot_object1_code%TYPE;
404 l_row_notfound Boolean;
405 BEGIN
406 -- dbms_output.put_line('Get_Hdr_Pricing_Rule');
407 Open c1;
408 Fetch c1 Into l_object_id, l_jtot_object;
409 l_row_notfound := c1%NotFound;
410 Close c1;
411 If l_row_notfound Then
412 x_return_status := G_NO_PRICING_RULE;
413 Elsif l_jtot_object = G_JTF_NOLOV Then
414 x_return_status := G_NO_LOV_PRICE_LIST;
415 Elsif l_object_id Is Null Then
416 x_return_status := G_NO_PRICE_LIST;
417 Else
418 x_return_status := G_OK_PRICE_LIST;
419 End If;
420 return(l_object_id);
421 END Get_Hdr_Pricing_Rule;
422
423 --2782972
424 ---Modified cursor c1 to check for shared mode,Full install not required to call pricing engine
425
426 Function Product_Installed(p_product_name Varchar2)
427 Return Boolean IS
428 l_row_notfound Boolean := True;
429 l_dummy Varchar2(1);
430 l_return_status Boolean := True;
431 cursor c1(p_status Varchar2,p_status1 Varchar2) is
432 select 'x'
433 from fnd_application app,
434 fnd_product_installations prd
435 where app.application_short_name = p_product_name
436 and app.application_id = prd.application_id
437 and prd.status in(p_status,p_status1);
438
439 Begin
440 -- dbms_output.put_line('Product_Installed');
441 Open c1('I','S');
442 Fetch c1 Into l_dummy;
443 l_row_notfound := c1%NotFound;
444 Close c1;
445 If l_row_notfound Then
446 OKC_API.set_message(G_APP_NAME, 'OKC_QP_NOT_INSTALLED');
447 l_return_status := False;
448 End If;
449 Return (l_return_status);
450 End Product_Installed;
451 --
452 Procedure Get_Price_List(p_clev_tbl OKC_CONTRACT_PUB.clev_tbl_type,
453 x_price_list OUT NOCOPY Varchar2,
454 x_return_status OUT NOCOPY Varchar2) Is
455 l_row_notfound Boolean := True;
456 l_price_list Varchar2(30);
457 l_cle_id Number;
458 l_cle_id_ascendant Number;
459 l_chr_id Number;
460 l_return_status Varchar2(1);
461 Ok_Price_List Exception;
462 No_Price_List Exception;
463 No_Lov_Price_List Exception;
464 No_Pricing_Rule Exception;
465 invalid_line Exception;
466 cursor c1(p_cle_id Number) is
467 select cle_id_ascendant
468 from okc_ancestrys_v
469 where cle_id = p_cle_id
470 order by level_sequence desc;
471 cursor c2(p_cle_id Number) is
472 select dnz_chr_id
473 from okc_k_lines_b
474 where id = p_cle_id;
475 Begin
476 -- dbms_output.put_line('Get_Price_List');
477 x_return_status := OKC_API.G_RET_STS_SUCCESS;
478 If p_clev_tbl(1).id Is Null Or
479 p_clev_tbl(1).id = OKC_API.G_MISS_NUM Then
480 If p_clev_tbl(1).cle_id Is Null Or
481 p_clev_tbl(1).cle_id = OKC_API.G_MISS_NUM Then
482 l_cle_id := Null;
483 Else
484 l_cle_id := p_clev_tbl(1).cle_id;
485 End If;
486 Else
487 l_cle_id := p_clev_tbl(1).id;
488 End If;
489 If l_cle_id Is Not Null Then
490 l_price_list := Get_Line_Pricing_Rule(l_cle_id, G_PRE_RULE, l_return_status);
491 -- If Price List found in the Pricing Rule, return with success
492 -- If NO LOV Price List found, return with success
493 -- If No Price List found in the attached rule, return with Error
494 -- If No Pricing Rule found, search at the higher level; If found none, return with Success
495 If l_return_status = G_OK_PRICE_LIST Then
496 Raise Ok_Price_List;
497 Elsif l_return_status = G_NO_LOV_PRICE_LIST Then
498 Raise No_Lov_Price_List;
499 Elsif l_return_status = G_NO_PRICE_LIST Then
500 Raise No_Price_List;
501 End If;
502 Open c1(l_cle_id);
503 Loop
504 Fetch c1 Into l_cle_id_ascendant;
505 Exit When c1%NotFound;
506 l_price_list := Get_Line_Pricing_Rule(l_cle_id_ascendant, G_PRE_RULE, l_return_status);
507 If l_return_status = G_OK_PRICE_LIST Then
508 Raise Ok_Price_List;
509 Elsif l_return_status = G_NO_LOV_PRICE_LIST Then
510 Raise No_Lov_Price_List;
511 Elsif l_return_status = G_NO_PRICE_LIST Then
512 Raise No_Price_List;
513 End If;
514 End Loop;
515 Close c1;
516 End If;
517 If p_clev_tbl(1).dnz_chr_id Is Null Or
518 p_clev_tbl(1).dnz_chr_id = OKC_API.G_MISS_NUM Then
519 If l_cle_id Is Not Null Then
520 Open c2(l_cle_id);
521 Fetch c2 Into l_chr_id;
522 l_row_notfound := c2%NotFound;
523 Close c2;
524 If l_row_notfound Then
525 Raise Invalid_Line;
526 End If;
527 Else
528 Raise Invalid_Line;
529 End If;
530 Else
531 l_chr_id := p_clev_tbl(1).dnz_chr_id;
532 End If;
533 l_price_list := Get_Hdr_Pricing_Rule(l_chr_id, G_PRE_RULE, l_return_status);
534 If l_return_status = G_OK_PRICE_LIST Then
535 Raise Ok_Price_List;
536 Elsif l_return_status = G_NO_LOV_PRICE_LIST Then
537 Raise No_Lov_Price_List;
538 Elsif l_return_status = G_NO_PRICE_LIST Then
539 Raise No_Price_List;
540 End If;
541 Raise No_Pricing_Rule;
542 Exception
543 When No_Pricing_Rule Then
544 x_price_list := Null;
545 When No_Lov_Price_List Then
546 x_price_list := Null;
547 When No_Price_List Then
548 OKC_API.set_message(G_APP_NAME, 'OKC_PRICE_LIST_NOT_FOUND');
549 x_return_status := OKC_API.G_RET_STS_ERROR;
550 When OK_Price_List Then
551 If c1%IsOpen Then
552 Close c1;
553 End If;
554 x_price_list := l_price_list;
555 When Invalid_Line Then
556 OKC_API.set_message(G_APP_NAME, 'OKC_CONTRACT_NOT_FOUND');
557 x_return_status := OKC_API.G_RET_STS_ERROR;
558 WHEN OTHERS THEN
559 If c1%IsOpen Then
560 Close c1;
561 End If;
562 If c2%IsOpen Then
563 Close c2;
564 End If;
565 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR, G_SQLCODE_TOKEN,
566 SQLCODE, G_SQLERRM_TOKEN, SQLERRM);
567 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
568 End Get_Price_List;
569 --
570 Procedure Get_Inventory_Item(p_clev_tbl OKC_CONTRACT_PUB.clev_tbl_type,
571 p_cimv_tbl OKC_CONTRACT_ITEM_PUB.cimv_tbl_type,
572 x_inventory_item_id OUT NOCOPY Number,
573 x_uom_code OUT NOCOPY Varchar2,
574 x_qty OUT NOCOPY Number,
575 x_return_status OUT NOCOPY Varchar2) Is
576 cursor c1(p_cle_id Number) is
577 select object1_id1,
578 uom_code,
579 number_of_items
580 from okc_k_items_v
581 where cle_id = p_cle_id
582 and priced_item_yn = 'Y';
583 -- and jtot_object1_code in (G_JTF_usage,G_JTF_service);
584 l_row_notfound Boolean := True;
585 Inv_Item_Not_Found Exception;
586 Item_Uom_Qty_Null Exception;
587 BEGIN
588 -- dbms_output.put_line('Get_Inventory_Item');
589 x_return_status := OKC_API.G_RET_STS_SUCCESS;
590 If Nvl(p_cimv_tbl(1).priced_item_yn, 'N') = 'Y' Then
591 x_inventory_item_id := p_cimv_tbl(1).object1_id1;
592 x_uom_code := p_cimv_tbl(1).uom_code;
593 x_qty := p_cimv_tbl(1).number_of_items;
594 Elsif p_clev_tbl(1).id Is Null Or
595 p_clev_tbl(1).id = OKC_API.G_MISS_NUM Then
596 Raise Inv_Item_Not_Found;
597 Else
598 Open c1(p_clev_tbl(1).id);
599 Fetch c1
600 Into x_inventory_item_id,
601 x_uom_code,
602 x_qty;
603 l_row_notfound := c1%NotFound;
604 Close c1;
605 If l_row_notfound Then
606 Raise Inv_Item_Not_Found;
607 End If;
608 End If;
609 If x_inventory_item_id Is Null Or
610 x_uom_code Is Null Or
611 x_qty Is Null Then
612 Raise Item_Uom_Qty_Null;
613 End If;
614 EXCEPTION
615 When Inv_Item_Not_Found Then
616 x_return_status := OKC_API.G_RET_STS_ERROR;
617 OKC_API.set_message(G_APP_NAME, 'OKC_INV_ITEM_NOT_FOUND');
618 When Item_Uom_Qty_Null Then
619 x_return_status := OKC_API.G_RET_STS_ERROR;
620 OKC_API.set_message(G_APP_NAME, 'OKC_ITEM_UOM_QTY_NULL');
621 WHEN OTHERS THEN
622 If c1%IsOpen Then
623 Close c1;
624 End If;
625 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR, G_SQLCODE_TOKEN,
626 SQLCODE, G_SQLERRM_TOKEN, SQLERRM);
627 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
628 END Get_Inventory_Item;
629 --
630 Procedure Get_Currency_Code(p_clev_tbl OKC_CONTRACT_PUB.clev_tbl_type,
631 x_cur_code OUT NOCOPY Varchar2,
632 x_return_status OUT NOCOPY Varchar2) Is
633 cursor c1(p_chr_id Number) is
634 select currency_code
635 from okc_k_headers_b
636 where id = p_chr_id;
637 cursor c2(p_cle_id Number) is
638 select k.currency_code
639 from okc_k_headers_b k,
640 okc_k_lines_b b
641 where b.id = p_cle_id
642 and k.id = b.dnz_chr_id;
643 l_cle_id Number;
644 l_row_notfound Boolean := True;
645 Contract_Not_Found Exception;
646 BEGIN
647 -- dbms_output.put_line('Get_Currency_Code');
648 x_return_status := OKC_API.G_RET_STS_SUCCESS;
649 If p_clev_tbl(1).currency_code Is Null Or
650 p_clev_tbl(1).currency_code = OKC_API.G_MISS_CHAR Then
651 If p_clev_tbl(1).dnz_chr_id Is Null Or
652 p_clev_tbl(1).dnz_chr_id = OKC_API.G_MISS_NUM Then
653 If p_clev_tbl(1).id Is Null Or
654 p_clev_tbl(1).id = OKC_API.G_MISS_NUM Then
655 If p_clev_tbl(1).cle_id Is Null Or
656 p_clev_tbl(1).cle_id = OKC_API.G_MISS_NUM Then
657 Raise Contract_Not_Found;
658 Else
659 l_cle_id := p_clev_tbl(1).cle_id;
660 End If;
661 Else
662 l_cle_id := p_clev_tbl(1).id;
663 End If;
664 Open c2(l_cle_id);
665 Fetch c2
666 Into x_cur_code;
667 l_row_notfound := c1%NotFound;
668 Close c2;
669 If l_row_notfound Then
670 Raise Contract_Not_Found;
671 End If;
672 Else
673 Open c1(p_clev_tbl(1).dnz_chr_id);
674 Fetch c1
675 Into x_cur_code;
676 l_row_notfound := c1%NotFound;
677 Close c1;
678 If l_row_notfound Then
679 Raise Contract_Not_Found;
680 End If;
681 End If;
682 Else
683 x_cur_code := p_clev_tbl(1).currency_code;
684 End If;
685 EXCEPTION
686 When Contract_Not_Found Then
687 x_return_status := OKC_API.G_RET_STS_ERROR;
688 OKC_API.set_message(G_APP_NAME, 'OKC_CONTRACT_NOT_FOUND');
689 WHEN OTHERS THEN
690 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
691 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR, G_SQLCODE_TOKEN,
692 SQLCODE, G_SQLERRM_TOKEN, SQLERRM);
693 END Get_Currency_Code;
694 --
695 Procedure BUILD_OKC_KLINE_REC
696 (
697 p_contract_line_id IN NUMBER,
698 x_contract_line_rec OUT NOCOPY OKC_PRICE_PUB.G_LINE_REC_TYPE,
699 x_return_status OUT NOCOPY VARCHAR2
700 )
701 Is
702 Cursor Get_Line_det_Csr Is
703 Select dnz_chr_id
704 ,Start_date
705 ,End_Date
706 ,sts_code
707 From OKC_K_LINES_B
708 Where Id = p_contract_line_id;
709
710 Cursor Get_Hdr_det_Csr (p_hdr_id Number) Is
711 Select Currency_code
712 ,Cust_po_number
713 ,scs_code
714 From OKC_K_HEADERS_B
715 Where id = p_hdr_id;
716
717 Cursor Get_agreement_Csr(p_id Number) Is
718 Select Isa_Agreement_id
719 From OKC_GOVERNANCES_V
720 Where dnz_chr_id = p_id
721 AND cle_id IS NULL;
722 -- Changed Because it gives full table scan as it dont have a Index on Chr_Id.--Jomy
723 -- Where chr_id = p_id;
724
725 /*** Item Csr - may not work for OKC Use Get_inventory_Item ***/
726 Cursor Get_Item_Csr Is
727 Select Object1_id1
728 From OKC_K_ITEMS_V
729 Where cle_id = p_contract_line_id
730 And Jtot_object1_code in(G_JTF_usage,G_JTF_service);
731
732 /*** Get it in the above API for inv item ***/
733 Cursor Get_usage_flag_Csr(l_id Number) Is
734 Select Usage_item_flag
735 From OKX_SYSTEM_ITEMS_V
736 Where id1 = l_id ;
737
738 /**** Party Csr - Assumes a header party, we might have line too ***/
739 Cursor Get_party_Csr (p_Kid Number) Is
740 Select object1_id1
741 From OKC_K_PARTY_ROLES_B
742 Where dnz_chr_id = p_kid
743 And Jtot_Object1_code = G_JTF_PARTY;
744
745 /*** Rule Csr - Assumes line level rule ?? ***/
746 Cursor Get_rule_Csr(l_cat varchar2) Is
747 Select OBJECT1_ID1,
748 RULE_INFORMATION1,
749 RULE_INFORMATION2,
750 RULE_INFORMATION3,
751 RULE_INFORMATION4
752 From OKC_RULE_GROUPS_B RG
753 ,OKC_RULES_B RL
754 Where RG.CLE_ID = p_contract_line_id
755 AND RG.ID = RL.RGP_ID
756 AND RULE_INFORMATION_CATEGORY = l_cat;
757
758 Cursor Get_Cust_Csr(p_bill_to_id Number, p_party_id number) Is
759 Select c.id1
760 from OKX_CUST_SITE_USES_V a, okx_customer_accounts_v c
761 where a.party_id = p_party_id and
762 c.id1 = a.CUST_ACCOUNT_ID and
763 a.Id1 = p_bill_to_id And a.Name = 'BILL_TO';
764
765
766 P_name Varchar2(40);
767 l_chrid Number;
768 l_service_period Varchar2(10);
769 l_Service_duration Number;
770 l_return_status Varchar2(10) := OKC_API.G_RET_STS_SUCCESS;
771 l_agreement_id Number;
772 l_Cust_Acct_id Number;
773 l_item_id Varchar2(40);
774 l_party_id Number;
775 l_bill_id Varchar2(240);
776 l_bill_interval Varchar2(240);
777 lId Number;
778
779 l_rule_rec GET_RULE_CSR%ROWTYPE;
780
781 BEGIN
782 x_return_status := OKC_API.G_RET_STS_SUCCESS;
783
784 X_Contract_line_rec.line_id:= p_contract_line_id;
785 OPEN Get_line_det_csr;
786 FETCH Get_Line_det_csr INTO
787 x_Contract_line_rec.hdr_id,
788 x_Contract_line_rec.start_date,
789 x_Contract_line_rec.end_date,
790 x_Contract_line_rec.status_code;
791 CLOSE get_line_det_csr;
792
793 OPEN Get_agreement_Csr(X_Contract_line_rec.hdr_id);
794 FETCH Get_agreement_Csr INTO x_contract_line_rec.agreement_id;
795 CLOSE Get_agreement_Csr;
796
797 /*** Item csr not needed ****/
798 OPEN Get_item_Csr;
799 FETCH Get_item_Csr INTO x_contract_line_rec.inventory_item_id;
800 CLOSE Get_item_Csr;
801
802 /*** We can get this info from the get_inventory_item api ***/
803 OPEN Get_usage_flag_Csr(x_contract_line_rec.inventory_item_id);
804 FETCH Get_usage_flag_csr INTO x_Contract_line_rec.usage_item_flag;
805 CLOSE Get_usage_flag_Csr;
806
807 /*** Do we need this ?? ***/
808 OPEN Get_Party_Csr(X_Contract_line_rec.hdr_id );
809 FETCH Get_Party_Csr INTO x_contract_line_rec.party_id;
810 CLOSE Get_Party_Csr;
811
812 /*** For the rules use the RULE API coded above for Price List ***/
813 OPEN Get_Rule_Csr('BTO');
814 FETCH Get_Rule_Csr INTO l_rule_rec;
815 Close Get_Rule_Csr;
816
817 x_contract_line_rec.bill_to_id := l_rule_rec.object1_id1;
818
819 Open Get_Rule_Csr('SBG');
820 Fetch Get_rule_Csr INTO l_rule_rec;
821 Close Get_Rule_Csr;
822
823 x_contract_line_rec.bill_interval := l_rule_rec.rule_information1;
824
825
826 For l_chr_rec in Get_hdr_det_csr(x_Contract_line_rec.hdr_id)
827 Loop
828 x_Contract_line_rec.currency_code:= l_chr_rec.currency_code;
829 x_Contract_line_rec.customer_po_number:= l_chr_rec.cust_po_number;
830 x_Contract_line_rec.class := 'SERVICE';
831 x_Contract_line_rec.sub_class := l_chr_rec.scs_code;
832 End Loop;
833
834 x_Contract_line_rec.Accounting_rule_id := get_hdr_rule(X_Contract_line_rec.hdr_id,'ARL',G_JTF_Acctrule);
835 x_Contract_line_rec.Invoice_rule_id := get_hdr_rule(X_Contract_line_rec.hdr_id,'IRE',G_JTF_Invrule);
836 x_Contract_line_rec.Payment_terms_id := get_hdr_rule(X_Contract_line_rec.hdr_id,'PTR',G_JTF_Payterm);
837 -- Beware!! Price_List_id is declared Number, function returns Varchar2
838 x_Contract_line_rec.Price_list_id := get_hdr_rule(X_Contract_line_rec.hdr_id,'PRE',G_JTF_Price);
839 x_Contract_line_rec.Bill_to_id := get_line_rule(p_contract_line_id,'BTO',G_JTF_Billto);
840 x_Contract_line_rec.Ship_to_id := get_line_rule(p_contract_line_id,'STO',G_JTF_Shipto);
841
842 /*** Do we need this ?? Probably not ***/
843 OKC_TIME_UTIL_PUB.GET_DURATION (
844 p_start_date => x_Contract_line_rec.start_date ,
845 p_end_date => x_Contract_line_rec.end_date ,
846 x_duration => x_contract_line_rec.item_qty,
847 x_timeunit => x_contract_line_rec.item_uom_code,
848 x_return_status => l_return_status
849 );
850
851 OPEN Get_Cust_Csr(X_Contract_line_rec.Bill_to_id, x_contract_line_rec.party_id);
852 FETCH Get_Cust_Csr INTO x_contract_line_rec.customer_acct_id;
853 CLOSE Get_Cust_Csr;
854
855
856 EXCEPTION
857 WHEN G_EXCEPTION_HALT_VALIDATION Then
858 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
859 OKC_API.set_message(G_APP_NAME,G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE, G_SQLERRM_TOKEN,SQLERRM);
860 WHEN OTHERS Then
861 ----Dbms_Output.Put_Line('OTHERS IN BUILD ' || sqlerrm);
862 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
863 OKC_API.set_message(G_APP_NAME,G_UNEXPECTED_ERROR, G_SQLCODE_TOKEN, SQLCODE,G_SQLERRM_TOKEN, SQLERRM);
864 END;
865
866
867 --- This Procedure loads the pricing attribute table and is called to load
868 --- the sourced as well as user defined pricing attributes
869
870 Procedure Load_Pattr_Tbl(p_line_index IN NUMBER,
871 p_pricing_context IN VARCHAR2,
872 p_pricing_attribute IN VARCHAR2,
873 p_pricing_attr_value IN VARCHAR2,
874 x_pattr_tbl IN OUT NOCOPY QP_PREQ_GRP.LINE_ATTR_TBL_TYPE) IS
875 i NUMBER;
876 BEGIN
877 i := nvl(x_pattr_tbl.last,0) +1;
878 x_pattr_tbl(i).Line_Index := p_Line_Index;
879 x_pattr_tbl(i).Validated_Flag := 'Y';
880 x_pattr_tbl(i).pricing_context := p_pricing_context;
881 x_pattr_tbl(i).Pricing_Attribute := p_pricing_attribute;
882 x_pattr_tbl(i).Pricing_Attr_Value_From :=p_pricing_attr_value;
883 END Load_Pattr_Tbl;
884
885 Procedure Load_Qual_Tbl(p_line_index IN NUMBER,
886 p_qualifier_context IN VARCHAR2,
887 p_qualifier_attribute IN VARCHAR2,
888 p_qualifier_attr_value IN VARCHAR2,
889 x_qual_tbl IN OUT NOCOPY QP_PREQ_GRP.QUAL_TBL_TYPE) IS
890 i NUMBER;
891 BEGIN
892 i := nvl(x_qual_tbl.last,0) +1;
893 x_qual_tbl(i).Line_Index := p_Line_Index;
894 x_qual_tbl(i).Validated_Flag := 'Y';
895 x_qual_tbl(i).qualifier_context := p_qualifier_context;
896 x_qual_tbl(i).qualifier_Attribute := p_qualifier_attribute;
897 x_qual_tbl(i).qualifier_Attr_Value_From :=p_qualifier_attr_value;
898 x_qual_tbl(i).qualifier_Attr_Value_To :=p_qualifier_attr_value;
899 x_qual_tbl(i).comparison_operator_code := '=';
900 END Load_Qual_Tbl;
901
902 -- This Procedure will return the attributes sourced using QP dimension mapping
903
904 Procedure Load_Sourced_Pattrs(p_service_line_index IN NUMBER,
905 p_pricing_contexts_tbl IN QP_Attr_Mapping_PUB.Contexts_Result_Tbl_Type,
906 px_Req_line_attr_tbl IN OUT nocopy QP_PREQ_GRP.LINE_ATTR_TBL_TYPE) IS
907
908 BEGIN
909 --- Load the pricing attributes of service item
910 If p_pricing_contexts_tbl.exists(1)Then
911 For i in p_pricing_contexts_Tbl.first .. p_pricing_contexts_Tbl.last
912 Loop
913 Load_Pattr_Tbl(p_line_index => p_service_line_index,
914 p_pricing_context => p_pricing_contexts_tbl(i).context_name,
915 p_pricing_attribute => p_pricing_contexts_tbl(i).attribute_name,
916 p_pricing_attr_value => p_pricing_contexts_tbl(i).attribute_value,
917 x_pattr_tbl => px_Req_line_attr_tbl);
918 End Loop;
919 End If;
920 END Load_Sourced_Pattrs;
921
922 -- This Procedure will return the user enterable pricing attributes in contracts
923
924 Procedure Load_User_Defined_Pattrs(p_contract_line_id NUMBER,
925 p_service_line_index NUMBER,
926 px_Req_line_attr_tbl IN OUT NOCOPY QP_PREQ_GRP.LINE_ATTR_TBL_TYPE) IS
927 cursor okc_pattr_cur is
928 select pricing_context,
929 pricing_attribute1, pricing_attribute2, pricing_attribute3, pricing_attribute4,
930 pricing_attribute5, pricing_attribute6, pricing_attribute7, pricing_attribute8,
931 pricing_attribute9, pricing_attribute10, pricing_attribute11, pricing_attribute12,
932 pricing_attribute13, pricing_attribute14, pricing_attribute15, pricing_attribute16,
933 pricing_attribute17, pricing_attribute18, pricing_attribute19, pricing_attribute20,
934 pricing_attribute21, pricing_attribute22, pricing_attribute23, pricing_attribute24,
935 pricing_attribute25, pricing_attribute26, pricing_attribute27, pricing_attribute28,
936 pricing_attribute29, pricing_attribute30, pricing_attribute31, pricing_attribute32,
937 pricing_attribute33, pricing_attribute34, pricing_attribute35, pricing_attribute36,
938 pricing_attribute37, pricing_attribute38, pricing_attribute39, pricing_attribute40,
939 pricing_attribute41, pricing_attribute42, pricing_attribute43, pricing_attribute44,
940 pricing_attribute45, pricing_attribute46, pricing_attribute47, pricing_attribute48,
941 pricing_attribute49, pricing_attribute50, pricing_attribute51, pricing_attribute52,
942 pricing_attribute53, pricing_attribute54, pricing_attribute55, pricing_attribute56,
943 pricing_attribute57, pricing_attribute58, pricing_attribute59, pricing_attribute60,
944 pricing_attribute61, pricing_attribute62, pricing_attribute63, pricing_attribute64,
945 pricing_attribute65, pricing_attribute66, pricing_attribute67, pricing_attribute68,
946 pricing_attribute69, pricing_attribute70, pricing_attribute71, pricing_attribute72,
947 pricing_attribute73, pricing_attribute74, pricing_attribute75, pricing_attribute76,
948 pricing_attribute77, pricing_attribute78, pricing_attribute79, pricing_attribute80,
949 pricing_attribute81, pricing_attribute82, pricing_attribute83, pricing_attribute84,
950 pricing_attribute85, pricing_attribute86, pricing_attribute87, pricing_attribute88,
951 pricing_attribute89, pricing_attribute90, pricing_attribute91, pricing_attribute92,
952 pricing_attribute93, pricing_attribute94, pricing_attribute95, pricing_attribute96,
953 pricing_attribute97, pricing_attribute98, pricing_attribute99, pricing_attribute100
954 from okc_price_att_values_v
955 where cle_id = p_contract_line_id;
956 Procedure Load_Tbl(p_prc_context Varchar2,
957 p_prc_attr Varchar2,
958 p_prc_attr_value Varchar2) Is
959 Begin
960 If p_prc_attr_value Is Not Null Then
961 LOAD_PATTR_TBL(p_line_index => p_service_line_index,
962 p_pricing_context => p_prc_context,
963 p_pricing_attribute => p_prc_attr,
964 p_pricing_attr_value => p_prc_attr_value,
965 x_pattr_tbl => px_req_line_attr_tbl);
966 End If;
967 End;
968 BEGIN
969 For OKC_pattr_rec in OKC_pattr_cur Loop
970 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE1', OKC_pattr_rec.PRICING_ATTRIBUTE1);
971 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE2', OKC_pattr_rec.PRICING_ATTRIBUTE2);
972 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE3', OKC_pattr_rec.PRICING_ATTRIBUTE3);
973 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE4', OKC_pattr_rec.PRICING_ATTRIBUTE4);
974 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE5', OKC_pattr_rec.PRICING_ATTRIBUTE5);
975 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE6', OKC_pattr_rec.PRICING_ATTRIBUTE6);
976 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE7', OKC_pattr_rec.PRICING_ATTRIBUTE7);
977 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE8', OKC_pattr_rec.PRICING_ATTRIBUTE8);
978 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE9', OKC_pattr_rec.PRICING_ATTRIBUTE9);
979 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE10', OKC_pattr_rec.PRICING_ATTRIBUTE10);
980 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE11', OKC_pattr_rec.PRICING_ATTRIBUTE11);
981 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE12', OKC_pattr_rec.PRICING_ATTRIBUTE12);
982 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE13', OKC_pattr_rec.PRICING_ATTRIBUTE13);
983 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE14', OKC_pattr_rec.PRICING_ATTRIBUTE14);
984 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE15', OKC_pattr_rec.PRICING_ATTRIBUTE15);
985 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE16', OKC_pattr_rec.PRICING_ATTRIBUTE16);
986 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE17', OKC_pattr_rec.PRICING_ATTRIBUTE17);
987 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE18', OKC_pattr_rec.PRICING_ATTRIBUTE18);
988 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE19', OKC_pattr_rec.PRICING_ATTRIBUTE19);
989 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE20', OKC_pattr_rec.PRICING_ATTRIBUTE20);
990 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE21', OKC_pattr_rec.PRICING_ATTRIBUTE21);
991 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE22', OKC_pattr_rec.PRICING_ATTRIBUTE22);
992 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE23', OKC_pattr_rec.PRICING_ATTRIBUTE23);
993 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE24', OKC_pattr_rec.PRICING_ATTRIBUTE24);
994 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE25', OKC_pattr_rec.PRICING_ATTRIBUTE25);
995 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE26', OKC_pattr_rec.PRICING_ATTRIBUTE26);
996 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE27', OKC_pattr_rec.PRICING_ATTRIBUTE27);
997 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE28', OKC_pattr_rec.PRICING_ATTRIBUTE28);
998 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE29', OKC_pattr_rec.PRICING_ATTRIBUTE29);
999 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE30', OKC_pattr_rec.PRICING_ATTRIBUTE30);
1000 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE31', OKC_pattr_rec.PRICING_ATTRIBUTE31);
1001 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE32', OKC_pattr_rec.PRICING_ATTRIBUTE32);
1002 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE33', OKC_pattr_rec.PRICING_ATTRIBUTE33);
1003 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE34', OKC_pattr_rec.PRICING_ATTRIBUTE34);
1004 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE35', OKC_pattr_rec.PRICING_ATTRIBUTE35);
1005 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE36', OKC_pattr_rec.PRICING_ATTRIBUTE36);
1006 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE37', OKC_pattr_rec.PRICING_ATTRIBUTE37);
1007 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE38', OKC_pattr_rec.PRICING_ATTRIBUTE38);
1008 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE39', OKC_pattr_rec.PRICING_ATTRIBUTE39);
1009 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE40', OKC_pattr_rec.PRICING_ATTRIBUTE40);
1010 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE41', OKC_pattr_rec.PRICING_ATTRIBUTE41);
1011 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE42', OKC_pattr_rec.PRICING_ATTRIBUTE42);
1012 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE43', OKC_pattr_rec.PRICING_ATTRIBUTE43);
1013 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE44', OKC_pattr_rec.PRICING_ATTRIBUTE44);
1014 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE45', OKC_pattr_rec.PRICING_ATTRIBUTE45);
1015 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE46', OKC_pattr_rec.PRICING_ATTRIBUTE46);
1016 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE47', OKC_pattr_rec.PRICING_ATTRIBUTE47);
1017 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE48', OKC_pattr_rec.PRICING_ATTRIBUTE48);
1018 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE49', OKC_pattr_rec.PRICING_ATTRIBUTE49);
1019 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE50', OKC_pattr_rec.PRICING_ATTRIBUTE50);
1020 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE51', OKC_pattr_rec.PRICING_ATTRIBUTE51);
1021 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE52', OKC_pattr_rec.PRICING_ATTRIBUTE52);
1022 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE53', OKC_pattr_rec.PRICING_ATTRIBUTE53);
1023 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE54', OKC_pattr_rec.PRICING_ATTRIBUTE54);
1024 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE55', OKC_pattr_rec.PRICING_ATTRIBUTE55);
1025 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE56', OKC_pattr_rec.PRICING_ATTRIBUTE56);
1026 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE57', OKC_pattr_rec.PRICING_ATTRIBUTE57);
1027 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE58', OKC_pattr_rec.PRICING_ATTRIBUTE58);
1028 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE59', OKC_pattr_rec.PRICING_ATTRIBUTE59);
1029 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE60', OKC_pattr_rec.PRICING_ATTRIBUTE60);
1030 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE61', OKC_pattr_rec.PRICING_ATTRIBUTE61);
1031 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE62', OKC_pattr_rec.PRICING_ATTRIBUTE62);
1032 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE63', OKC_pattr_rec.PRICING_ATTRIBUTE63);
1033 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE64', OKC_pattr_rec.PRICING_ATTRIBUTE64);
1034 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE65', OKC_pattr_rec.PRICING_ATTRIBUTE65);
1035 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE66', OKC_pattr_rec.PRICING_ATTRIBUTE66);
1036 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE67', OKC_pattr_rec.PRICING_ATTRIBUTE67);
1037 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE68', OKC_pattr_rec.PRICING_ATTRIBUTE68);
1038 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE69', OKC_pattr_rec.PRICING_ATTRIBUTE69);
1039 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE70', OKC_pattr_rec.PRICING_ATTRIBUTE70);
1040 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE71', OKC_pattr_rec.PRICING_ATTRIBUTE71);
1041 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE72', OKC_pattr_rec.PRICING_ATTRIBUTE72);
1042 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE73', OKC_pattr_rec.PRICING_ATTRIBUTE73);
1043 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE74', OKC_pattr_rec.PRICING_ATTRIBUTE74);
1044 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE75', OKC_pattr_rec.PRICING_ATTRIBUTE75);
1045 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE76', OKC_pattr_rec.PRICING_ATTRIBUTE76);
1046 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE77', OKC_pattr_rec.PRICING_ATTRIBUTE77);
1047 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE78', OKC_pattr_rec.PRICING_ATTRIBUTE78);
1048 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE79', OKC_pattr_rec.PRICING_ATTRIBUTE79);
1049 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE80', OKC_pattr_rec.PRICING_ATTRIBUTE80);
1050 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE81', OKC_pattr_rec.PRICING_ATTRIBUTE81);
1051 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE82', OKC_pattr_rec.PRICING_ATTRIBUTE82);
1052 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE83', OKC_pattr_rec.PRICING_ATTRIBUTE83);
1053 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE84', OKC_pattr_rec.PRICING_ATTRIBUTE84);
1054 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE85', OKC_pattr_rec.PRICING_ATTRIBUTE85);
1055 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE86', OKC_pattr_rec.PRICING_ATTRIBUTE86);
1056 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE87', OKC_pattr_rec.PRICING_ATTRIBUTE87);
1057 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE88', OKC_pattr_rec.PRICING_ATTRIBUTE88);
1058 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE89', OKC_pattr_rec.PRICING_ATTRIBUTE89);
1059 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE90', OKC_pattr_rec.PRICING_ATTRIBUTE90);
1060 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE91', OKC_pattr_rec.PRICING_ATTRIBUTE91);
1061 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE92', OKC_pattr_rec.PRICING_ATTRIBUTE92);
1062 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE93', OKC_pattr_rec.PRICING_ATTRIBUTE93);
1063 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE94', OKC_pattr_rec.PRICING_ATTRIBUTE94);
1064 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE95', OKC_pattr_rec.PRICING_ATTRIBUTE95);
1065 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE96', OKC_pattr_rec.PRICING_ATTRIBUTE96);
1066 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE97', OKC_pattr_rec.PRICING_ATTRIBUTE97);
1067 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE98', OKC_pattr_rec.PRICING_ATTRIBUTE98);
1068 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE99', OKC_pattr_rec.PRICING_ATTRIBUTE99);
1069 LOAD_TBL(OKC_pattr_rec.pricing_context, 'PRICING_ATTRIBUTE100', OKC_pattr_rec.PRICING_ATTRIBUTE100);
1070
1071 /* If OKC_pattr_rec.PRICING_ATTRIBUTE100 is not null Then
1072 LOAD_PATTR_TBL(p_line_index => p_service_line_index,
1073 p_pricing_context => OKC_pattr_rec.pricing_context,
1074 p_pricing_attribute => 'PRICING_ATTRIBUTE100',
1075 p_pricing_attr_value => OKC_pattr_rec.PRICING_ATTRIBUTE100,
1076 x_pattr_tbl => px_req_line_attr_tbl);
1077 End If; */
1078 End Loop;
1079 END Load_User_Defined_Pattrs;
1080
1081 /**** Load Lines is used for PB_Tbl_flag = N and usage item = N
1082 For this condition the Req_Line-TBL gets two records - one from Kline and from CP_Tbl
1083 Also Req_Line_Attr_Tbl gets two similar records
1084 Req_Qual_Tbl gets two price list record and one modifier record
1085 Might want to check this out nocopy ***/
1086
1087 Procedure Load_Lines(p_line_rec IN OKC_PRICE_PUB.G_LINE_REC_TYPE,
1088 p_cp_Line_tbl IN OKC_PRICE_PUB.G_SLINE_TBL_TYPE,
1089 p_get_pb_tbl_flag IN Varchar2,
1090 p_price_list_id IN Number,
1091 p_modifier_list_id IN Number,
1092 p_pricing_contexts_tbl IN QP_ATTR_MAPPING_PUB.CONTEXTS_RESULT_TBL_TYPE,
1093 px_req_line_tbl IN OUT NOCOPY QP_PREQ_GRP.LINE_TBL_TYPE,
1094 px_Req_line_attr_tbl IN OUT NOCOPY QP_PREQ_GRP.LINE_ATTR_TBL_TYPE,
1095 px_req_related_lines_tbl IN OUT NOCOPY QP_PREQ_GRP.RELATED_LINES_TBL_TYPE,
1096 px_req_qual_tbl IN OUT NOCOPY QP_PREQ_GRP.QUAL_TBL_TYPE) IS
1097 l_line_index NUMBER := 0;
1098 l_related_lines_Index NUMBER := 0;
1099 l_attr_index NUMBER := 0;
1100 l_qual_attr_index NUMBER := 0;
1101 BEGIN
1102 If p_get_pb_tbl_flag = 'Y' AND p_line_rec.usage_item_flag = 'Y' Then
1103 --- Get only price break information. Used only for Service Contracts
1104 l_line_index := l_line_index+1;
1105 px_req_line_tbl(l_line_index).LINE_INDEX := l_line_index;
1106 px_req_line_tbl(l_line_index).LINE_TYPE_CODE := 'LINE';
1107 px_req_line_tbl(l_line_index).REQUEST_TYPE_CODE := OKC_PRICE_PUB.G_REQUEST_TYPE_CODE;
1108 px_req_line_tbl(l_line_index).CURRENCY_CODE := p_Line_rec.currency_code;
1109 px_req_line_tbl(l_line_index).PRICING_EFFECTIVE_DATE := sysdate;
1110 px_req_line_tbl(l_line_index).ACTIVE_DATE_FIRST :=
1111 px_req_line_tbl(l_line_index).PRICING_EFFECTIVE_DATE;
1112 px_req_line_tbl(l_line_index).ACTIVE_DATE_FIRST_TYPE := 'NO TYPE';
1113 px_req_line_tbl(l_line_index).LINE_QUANTITY := p_line_rec.item_qty;
1114 px_req_line_tbl(l_line_index).LINE_UOM_CODE := p_line_rec.item_uom_code;
1115 px_req_line_tbl(l_line_index).PRICE_FLAG := 'Y';
1116
1117 --- Load the pricing attribute For this item
1118 Load_Pattr_Tbl(p_line_index => l_line_index,
1119 p_pricing_context => OKC_PRICE_PUB.G_ITEM_CONTEXT,
1120 p_pricing_attribute => OKC_PRICE_PUB.G_ITEM_ATTR,
1121 p_pricing_attr_value => p_line_rec.inventory_item_id,
1122 x_pattr_tbl => px_Req_line_attr_tbl);
1123
1124 Load_Pattr_Tbl(p_line_index => l_line_index,
1125 p_pricing_context => OKC_PRICE_PUB.G_VOLUME_CONTEXT,
1126 p_pricing_attribute => OKC_PRICE_PUB.G_VOLUME_ATTR,
1127 p_pricing_attr_value => p_line_rec.item_qty,
1128 x_pattr_tbl => px_Req_line_attr_tbl);
1129
1130 --- Load the Price list as qualifier For serviceable item since we know which price list to use
1131 --- We do not use modifier list For serviceable items
1132 Load_Qual_Tbl(p_line_index => l_line_index,
1133 p_qualifier_context => OKC_PRICE_PUB.G_LIST_CONTEXT,
1134 p_qualifier_attribute => OKC_PRICE_PUB.G_LIST_PRICE_ATTR,
1135 p_qualifier_attr_value => p_price_list_id,
1136 x_qual_tbl => px_Req_qual_tbl);
1137 Else
1138 --- Calculate price
1139 --- Load Serviceable Item records into request. This must be done prior to service item according to QP
1140 For i in p_cp_line_tbl.first .. p_cp_line_tbl.last
1141 Loop
1142 --- Load the serviceable item first
1143 l_line_index := l_line_index+1;
1144 px_req_line_tbl(l_line_index).LINE_INDEX := l_line_index;
1145 px_req_line_tbl(l_line_index).LINE_TYPE_CODE := 'LINE';
1146 px_req_line_tbl(l_line_index).REQUEST_TYPE_CODE := OKC_PRICE_PUB.G_REQUEST_TYPE_CODE;
1147 px_req_line_tbl(l_line_index).CURRENCY_CODE := p_line_rec.currency_code;
1148 px_req_line_tbl(l_line_index).PRICING_EFFECTIVE_DATE := sysdate;
1149 px_req_line_tbl(l_line_index).ACTIVE_DATE_FIRST :=
1150 px_req_line_tbl(l_line_index).PRICING_EFFECTIVE_DATE;
1151 px_req_line_tbl(l_line_index).ACTIVE_DATE_FIRST_TYPE := 'NO TYPE';
1152 px_req_line_tbl(l_line_index).LINE_QUANTITY := p_cp_line_tbl(i).item_qty;
1153 px_req_line_tbl(l_line_index).LINE_UOM_CODE := p_cp_line_tbl(i).item_uom_code;
1154 px_req_line_tbl(l_line_index).PRICE_FLAG := 'Y';
1155
1156 --- Load the pricing attribute For this item
1157
1158 If nvl(p_line_rec.usage_item_flag, 'N') = 'N' Then
1159 Load_Pattr_Tbl(p_line_index => l_line_index,
1160 p_pricing_context => OKC_PRICE_PUB.G_ITEM_CONTEXT,
1161 p_pricing_attribute => OKC_PRICE_PUB.G_ITEM_ATTR,
1162 p_pricing_attr_value => p_cp_line_tbl(i).inventory_item_id,
1163 x_pattr_tbl => px_Req_line_attr_tbl);
1164 Else
1165 Load_Pattr_Tbl(p_line_index => l_line_index,
1166 p_pricing_context => OKC_PRICE_PUB.G_ITEM_CONTEXT,
1167 p_pricing_attribute => OKC_PRICE_PUB.G_ITEM_ATTR,
1168 p_pricing_attr_value => p_line_rec.inventory_item_id,
1169 x_pattr_tbl => px_Req_line_attr_tbl);
1170
1171 --- For usage items, QP requires quantity as pricing attr rather than item qty
1172 Load_Pattr_Tbl(p_line_index => l_line_index,
1173 p_pricing_context => OKC_PRICE_PUB.G_VOLUME_CONTEXT,
1174 p_pricing_attribute => OKC_PRICE_PUB.G_VOLUME_ATTR,
1175 p_pricing_attr_value => p_cp_line_tbl(i).item_qty,
1176 x_pattr_tbl => px_Req_line_attr_tbl);
1177 End If;
1178
1179 --- Load the Price list as qualifier For serviceable item since we know which price list to use
1180 --- We do not use modifier list For serviceable items
1181
1182 Load_Qual_Tbl(p_line_index => l_line_index,
1183 p_qualifier_context => OKC_PRICE_PUB.G_LIST_CONTEXT,
1184 p_qualifier_attribute => OKC_PRICE_PUB.G_LIST_PRICE_ATTR,
1185 p_qualifier_attr_value => p_price_list_id,
1186 x_qual_tbl => px_Req_qual_tbl);
1187 -- Commented out the following since core contracts will be passing exactly one
1188 -- item and not multiple like the service contracts
1189 /* If nvl(p_line_rec.usage_item_flag, 'N') = 'N' Then
1190 --- Load the service item
1191 l_line_index := l_line_index+1;
1192 px_req_line_tbl(l_line_index).LINE_INDEX := l_line_index;
1193 px_req_line_tbl(l_line_index).LINE_TYPE_CODE := 'LINE';
1194 px_req_line_tbl(l_line_index).REQUEST_TYPE_CODE := OKC_PRICE_PUB.G_REQUEST_TYPE_CODE;
1195 px_req_line_tbl(l_line_index).CURRENCY_CODE := p_Line_rec.currency_code;
1196 px_req_line_tbl(l_line_index).PRICING_EFFECTIVE_DATE := sysdate;
1197 px_req_line_tbl(l_line_index).ACTIVE_DATE_FIRST :=
1198 px_req_line_tbl(l_line_index).PRICING_EFFECTIVE_DATE;
1199 px_req_line_tbl(l_line_index).ACTIVE_DATE_FIRST_TYPE := 'NO TYPE';
1200 px_req_line_tbl(l_line_index).LINE_QUANTITY := p_Line_rec.item_qty;
1201 px_req_line_tbl(l_line_index).LINE_UOM_CODE := p_Line_rec.item_uom_code;
1202 px_req_line_tbl(l_line_index).PRICE_FLAG := 'Y';
1203
1204 --- Load the pricing attribute For service/usage item
1205 LOAD_PATTR_TBL(p_line_index => l_line_index,
1206 p_pricing_context => OKC_PRICE_PUB.G_ITEM_CONTEXT,
1207 p_pricing_attribute => OKC_PRICE_PUB.G_ITEM_ATTR,
1208 p_pricing_attr_value => p_line_rec.inventory_item_id,
1209 x_pattr_tbl => px_Req_line_attr_tbl);
1210
1211 --- Load the Price list list as qualifier For service/usage item
1212 Load_Qual_Tbl(p_line_index => l_line_index,
1213 p_qualifier_context => OKC_PRICE_PUB.G_LIST_CONTEXT,
1214 p_qualifier_attribute => OKC_PRICE_PUB.G_LIST_PRICE_ATTR,
1215 p_qualifier_attr_value => p_price_list_id,
1216 x_qual_tbl => px_Req_qual_tbl);
1217
1218 --- Load the Modifier list list as qualifier For service item
1219 If p_modifier_list_id IS NOT NULL Then
1220 Load_Qual_Tbl(p_line_index => l_line_index,
1221 p_qualifier_context => OKC_PRICE_PUB.G_LIST_CONTEXT,
1222 p_qualifier_attribute => OKC_PRICE_PUB.G_LIST_MODIFIER_ATTR,
1223 p_qualifier_attr_value => p_modifier_list_id,
1224 x_qual_tbl => px_Req_qual_tbl);
1225 End If;
1226
1227 --- Set the relationship between service/serviceable items
1228 --- Last line in the request line table is the service item other lines are serviceable items
1229
1230 l_related_lines_Index := nvl(px_Req_related_lines_tbl.last,0) + 1;
1231 px_Req_related_lines_tbl(l_related_lines_Index).Line_Index := l_line_index;
1232 px_Req_related_lines_tbl(l_related_lines_Index).Line_Detail_Index := 0;
1233 px_Req_related_lines_tbl(l_related_lines_Index).Related_Line_Index := l_line_index - 1;
1234 px_Req_related_lines_tbl(l_related_lines_Index).Related_Line_Detail_Index := 0;
1235 px_Req_related_lines_tbl(l_related_lines_Index).Relationship_Type_Code := QP_PREQ_GRP.G_SERVICE_LINE;
1236 --Dbms_Output.Put_Line('RELATED LINE INDEX ' || l_line_index);
1237 --Dbms_Output.Put_Line('RELATED DETAIL LINE INDEX ' || i);
1238 --Dbms_Output.Put_Line('G_SERVICE LINE ' || QP_PREQ_GRP.G_SERVICE_LINE);
1239
1240 End If; */
1241 End Loop;
1242 End If;
1243 END Load_LineS;
1244
1245 Procedure Call_QP(p_contract_line_rec IN OKC_PRICE_PUB.G_LINE_REC_TYPE,
1246 p_contract_cp_tbl IN OUT NOCOPY OKC_PRICE_PUB.G_SLINE_TBL_TYPE,
1247 p_get_pb_tbl_flag IN VARCHAR2,
1248 x_req_line_tbl OUT NOCOPY QP_PREQ_GRP.LINE_TBL_TYPE,
1249 x_Req_qual_tbl OUT NOCOPY QP_PREQ_GRP.QUAL_TBL_TYPE,
1250 x_Req_line_attr_tbl OUT NOCOPY QP_PREQ_GRP.LINE_ATTR_TBL_TYPE,
1251 x_Req_LINE_DETAIL_tbl OUT NOCOPY QP_PREQ_GRP.LINE_DETAIL_TBL_TYPE,
1252 x_Req_LINE_DETAIL_qual_tbl OUT NOCOPY QP_PREQ_GRP.LINE_DETAIL_QUAL_TBL_TYPE,
1253 x_Req_LINE_DETAIL_attr_tbl OUT NOCOPY QP_PREQ_GRP.LINE_DETAIL_ATTR_TBL_TYPE,
1254 x_Req_related_lines_tbl OUT NOCOPY QP_PREQ_GRP.RELATED_LINES_TBL_TYPE,
1255 x_return_status OUT NOCOPY Varchar2,
1256 x_return_status_text OUT NOCOPY Varchar2) IS
1257 l_return_status Varchar2(1) := OKC_API.G_RET_STS_SUCCESS;
1258 l_return_status_text Varchar2(240);
1259 l_Req_line_tbl QP_PREQ_GRP.LINE_TBL_TYPE;
1260 l_Req_qual_tbl QP_PREQ_GRP.QUAL_TBL_TYPE;
1261 l_Req_line_attr_tbl QP_PREQ_GRP.LINE_ATTR_TBL_TYPE;
1262 l_Req_LINE_DETAIL_tbl QP_PREQ_GRP.LINE_DETAIL_TBL_TYPE;
1263 l_Req_LINE_DETAIL_qual_tbl QP_PREQ_GRP.LINE_DETAIL_QUAL_TBL_TYPE;
1264 l_Req_LINE_DETAIL_attr_tbl QP_PREQ_GRP.LINE_DETAIL_ATTR_TBL_TYPE;
1265 l_Req_related_lines_tbl QP_PREQ_GRP.RELATED_LINES_TBL_TYPE;
1266 l_pricing_contexts_Tbl QP_Attr_Mapping_PUB.Contexts_Result_Tbl_Type;
1267 l_qualifier_contexts_Tbl QP_Attr_Mapping_PUB.Contexts_Result_Tbl_Type;
1268 i Number;
1269
1270 Begin
1271 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1272 G_CONTROL_REC.pricing_event := OKC_PRICE_PUB.G_PRICING_EVENT;
1273 G_CONTROL_REC.calculate_flag := 'Y';
1274 G_CONTROL_REC.simulation_flag := 'N';
1275
1276 QP_Attr_Mapping_PUB.Build_Contexts(p_request_type_code => OKC_PRICE_PUB.G_REQUEST_TYPE_CODE,
1277 p_pricing_type => 'L',
1278 x_price_contexts_result_tbl => l_pricing_contexts_Tbl,
1279 x_qual_contexts_result_tbl => l_qualifier_Contexts_Tbl);
1280
1281 -- Build line Request including price/modifier list as qualifiers and related lines
1282 Load_Lines(p_line_rec => p_contract_line_rec,
1283 p_cp_Line_tbl => p_contract_cp_tbl,
1284 p_get_pb_tbl_flag => p_get_pb_tbl_flag,
1285 p_price_list_id => p_contract_line_rec.price_list_id,
1286 p_modifier_list_id => p_contract_line_rec.modifier_list_id,
1287 p_pricing_contexts_tbl => l_pricing_contexts_Tbl,
1288 px_req_line_tbl => l_req_line_tbl,
1289 px_req_line_attr_tbl => l_req_line_attr_tbl,
1290 px_req_related_lines_tbl => l_req_related_lines_tbl,
1291 px_req_qual_tbl => l_req_qual_tbl);
1292
1293 Load_Sourced_Pattrs(p_service_line_index => l_req_line_tbl.last,
1294 p_pricing_contexts_tbl => l_pricing_contexts_tbl,
1295 px_req_line_attr_tbl => l_req_line_attr_tbl);
1296
1297 Load_User_Defined_Pattrs(p_contract_line_id => p_contract_line_rec.line_id,
1298 p_service_line_index => l_req_line_tbl.last,
1299 px_req_line_attr_tbl => l_req_line_attr_tbl);
1300
1301 /* If Nvl(l_req_line_tbl.count,0 ) > 0 Then
1302 Dbms_Output.Put_Line('-------------Line Information-------------------');
1303 For i in l_req_line_tbl.first .. l_req_line_tbl.last
1304 Loop
1305 Dbms_Output.Put_Line('line_index: '||l_req_line_tbl(I).line_index);
1306 Dbms_Output.Put_Line(' line id: '||l_req_line_tbl(I).line_id);
1307 Dbms_Output.Put_Line(' Unit_price: '||l_req_line_tbl(I).unit_price);
1308 Dbms_Output.Put_Line(' Percent price: '||l_req_line_tbl(I).percent_price);
1309 Dbms_Output.Put_Line(' Line quantity: '||l_req_line_tbl(I).line_quantity);
1310 Dbms_Output.Put_Line(' Parent price: '||l_req_line_tbl(I).parent_price);
1311 Dbms_Output.Put_Line(' Parent quant: '||l_req_line_tbl(I).parent_quantity);
1312 Dbms_Output.Put_Line(' Adjusted unit price: '||l_req_line_tbl(I).adjusted_unit_price);
1313 Dbms_Output.Put_Line(' Line UOM Code : '||l_req_line_tbl(I).line_uom_code);
1314 Dbms_Output.Put_Line(' UOM Quantity : '||l_req_line_tbl(I).uom_quantity);
1315 Dbms_Output.Put_Line(' Currency Code : '||l_req_line_tbl(I).currency_code);
1316 Dbms_Output.Put_Line(' Pricing status code: '||l_req_line_tbl(I).status_code);
1317 Dbms_Output.Put_Line(' Pricing quantity: '||l_req_line_tbl(I).priced_quantity);
1318 Dbms_Output.Put_Line(' Pricing uom code code: '||l_req_line_tbl(I).priced_uom_code);
1319 Dbms_Output.Put_Line(' Pricing status text: '||l_req_line_tbl(I).status_text);
1320 End Loop;
1321 End If;
1322
1323 If nvl(l_req_qual_tbl.count,0 ) > 0 Then
1324 Dbms_Output.Put_Line('-------------Qualifier Information-------------------');
1325 For i in l_req_qual_tbl.first .. l_req_qual_tbl.last
1326 Loop
1327 Dbms_Output.Put_Line('line_index: '||l_req_qual_tbl(I).line_index);
1328 Dbms_Output.Put_Line(' qual context: '||l_req_qual_tbl(I).qualifier_context);
1329 Dbms_Output.Put_Line(' qual attr: '||l_req_qual_tbl(I).qualifier_attribute);
1330 Dbms_Output.Put_Line(' qual attr value from: '||l_req_qual_tbl(I).qualifier_attr_value_from);
1331 Dbms_Output.Put_Line(' qual attr value to: '||l_req_qual_tbl(I).qualifier_attr_value_to);
1332 Dbms_Output.Put_Line(' comp opn code: '||l_req_qual_tbl(I).comparison_operator_code);
1333 Dbms_Output.Put_Line(' validated flag: '||l_req_qual_tbl(I).validated_flag);
1334 Dbms_Output.Put_Line(' status code: '||l_req_qual_tbl(I).status_code);
1335 Dbms_Output.Put_Line(' status text: '||l_req_qual_tbl(I).status_text);
1336 End Loop;
1337 End If;
1338
1339 If nvl(l_Req_line_attr_tbl.count,0 ) > 0 Then
1340 Dbms_Output.Put_Line('-------------Line Attr Information-------------------');
1341 For i in l_Req_line_attr_tbl.first .. l_Req_line_attr_tbl.last
1342 Loop
1343 Dbms_Output.Put_Line('line_index: '||l_Req_line_attr_tbl(I).line_index);
1344 Dbms_Output.Put_Line(' Prc context: '||l_Req_line_attr_tbl(I).pricing_context);
1345 Dbms_Output.Put_Line(' Prc attr: '||l_Req_line_attr_tbl(I).pricing_attribute);
1346 Dbms_Output.Put_Line(' Prc attr value from: '||l_Req_line_attr_tbl(I).pricing_attr_value_from);
1347 Dbms_Output.Put_Line(' Prc attr value to: '||l_Req_line_attr_tbl(I).pricing_attr_value_to);
1348 Dbms_Output.Put_Line(' validated flag: '||l_Req_line_attr_tbl(I).validated_flag);
1349 Dbms_Output.Put_Line(' status code: '||l_Req_line_attr_tbl(I).status_code);
1350 Dbms_Output.Put_Line(' status text: '||l_Req_line_attr_tbl(I).status_text);
1351 End Loop;
1352 End If;
1353
1354 If nvl(l_req_line_detail_tbl.count,0) > 0 Then
1355 Dbms_Output.Put_Line('------------Line Detail Information------------ ');
1356 For I in l_req_line_detail_tbl.first .. l_req_line_detail_tbl.last
1357 Loop
1358 Dbms_Output.Put_Line(' I ' || I || 'Count ' || l_req_line_detail_tbl.count);
1359 Dbms_Output.Put_Line('line_detail_index: '||l_req_line_detail_tbl(I).line_detail_index);
1360 Dbms_Output.Put_Line(' line detail type:'||l_req_line_detail_tbl(I).line_detail_type_code);
1361 Dbms_Output.Put_Line(' line_index: '||l_req_line_detail_tbl(I).line_index);
1362 Dbms_Output.Put_Line(' list_header_id: '||l_req_line_detail_tbl(I).list_header_id);
1363 Dbms_Output.Put_Line(' list_line_id: '||l_req_line_detail_tbl(I).list_line_id);
1364 Dbms_Output.Put_Line(' list_line_type_code: '||l_req_line_detail_tbl(I).list_line_type_code);
1365 Dbms_Output.Put_Line(' Adjustment Amount : '||l_req_line_detail_tbl(I).adjustment_amount);
1366 Dbms_Output.Put_Line(' Line Quantity : '||l_req_line_detail_tbl(I).line_quantity);
1367 Dbms_Output.Put_Line(' List Price : '||l_req_line_detail_tbl(I).list_price);
1368 Dbms_Output.Put_Line(' Operand_calculation_code: '||l_req_line_detail_tbl(I).Operand_calculation_code);
1369 Dbms_Output.Put_Line(' Operand value: '||l_req_line_detail_tbl(I).operand_value);
1370 Dbms_Output.Put_Line(' Automatic Flag: '||l_req_line_detail_tbl(I).automatic_flag);
1371 Dbms_Output.Put_Line(' Overide Flag: '||l_req_line_detail_tbl(I).override_flag);
1372 Dbms_Output.Put_Line(' status_code: '||l_req_line_detail_tbl(I).status_code);
1373 Dbms_Output.Put_Line(' status text: '||l_req_line_detail_tbl(I).status_text);
1374 Dbms_Output.Put_Line('-------------------------------------------');
1375 End Loop;
1376 End If;
1377
1378 If nvl(l_req_related_lines_tbl.count,0) > 0 Then
1379 Dbms_Output.Put_Line('--------------Related Lines Information---------------');
1380 For i in l_req_related_lines_tbl.first .. l_req_related_lines_tbl.last
1381 Loop
1382 Dbms_Output.Put_Line('Line Index :'||l_req_related_lines_tbl(I).line_index);
1383 Dbms_Output.Put_Line('Line detail index: '||l_req_related_lines_tbl(I).LINE_DETAIL_INDEX);
1384 Dbms_Output.Put_Line(' Relationship type code: '||l_req_related_lines_tbl(I).relationship_type_code);
1385 Dbms_Output.Put_Line(' Related Line Index: '||l_req_related_lines_tbl(I).RELATED_LINE_INDEX);
1386 Dbms_Output.Put_Line(' Related line detail index: '||l_req_related_lines_tbl(I).related_line_detail_index);
1387 Dbms_Output.Put_Line(' Status Code: '|| l_req_related_lines_tbl(I).STATUS_CODE);
1388 End Loop;
1389 End If;
1390
1391 If nvl(l_req_line_detail_attr_tbl.count,0) > 0 Then
1392 Dbms_Output.Put_Line('-----------Attributes Information-------------');
1393 For i in l_req_line_detail_attr_tbl.first .. l_req_line_detail_attr_tbl.last
1394 Loop
1395 Dbms_Output.Put_Line('Line detail INDEX '||l_req_line_detail_attr_tbl(I).line_detail_index);
1396 Dbms_Output.Put_Line(' Pricing Context '||l_req_line_detail_attr_tbl(I).pricing_context);
1397 Dbms_Output.Put_Line(' Pricing attribute '||l_req_line_detail_attr_tbl(I).pricing_attribute);
1398 Dbms_Output.Put_Line(' Pricing attr value from '||l_req_line_detail_attr_tbl(I).pricing_attr_value_from);
1399 Dbms_Output.Put_Line(' Pircing attr value to '||l_req_line_detail_attr_tbl(I).pricing_attr_value_to);
1400 Dbms_Output.Put_Line(' Status Code '||l_req_line_detail_attr_tbl(I).status_code);
1401 End Loop;
1402 End If;
1403
1404 If nvl(l_req_line_detail_qual_tbl.count,0) > 0 Then
1405 Dbms_Output.Put_Line('-----------Qualifier Attributes Information-------------');
1406 For i in l_req_line_detail_qual_tbl.first .. l_req_line_detail_qual_tbl.last
1407 Loop
1408 Dbms_Output.Put_Line('Line detail INDEX '||l_req_line_detail_qual_tbl(I).line_detail_index);
1409 Dbms_Output.Put_Line(' Qualifier Context '||l_req_line_detail_qual_tbl(I).qualifier_context);
1410 Dbms_Output.Put_Line(' Qualifier attribute '||l_req_line_detail_qual_tbl(I).qualifier_attribute);
1411 Dbms_Output.Put_Line(' Qualifier attr value from '||l_req_line_detail_qual_tbl(I).qualifier_attr_value_from);
1412 Dbms_Output.Put_Line(' Qualifier attr value to '||l_req_line_detail_qual_tbl(I).qualifier_attr_value_to);
1413 Dbms_Output.Put_Line(' Status Code '||l_req_line_detail_qual_tbl(I).status_code);
1414 Dbms_Output.Put_Line('---------------------------------------------------');
1415 End Loop;
1416 End If; */
1417 QP_PREQ_GRP.PRICE_REQUEST(p_control_rec => G_CONTROL_REC,
1418 p_line_tbl => l_Req_line_tbl,
1419 p_qual_tbl => l_Req_qual_tbl,
1420 p_line_attr_tbl => l_Req_line_attr_tbl,
1421 p_line_detail_tbl => l_req_line_detail_tbl,
1422 p_line_detail_qual_tbl => l_req_line_detail_qual_tbl,
1423 p_line_detail_attr_tbl => l_req_line_detail_attr_tbl,
1424 p_related_lines_tbl => l_req_related_lines_tbl,
1425 x_line_tbl => x_req_line_tbl,
1426 x_line_qual => x_Req_qual_tbl,
1427 x_line_attr_tbl => x_Req_line_attr_tbl,
1428 x_line_detail_tbl => x_req_line_detail_tbl,
1429 x_line_detail_qual_tbl => x_req_line_detail_qual_tbl,
1430 x_line_detail_attr_tbl => x_req_line_detail_attr_tbl,
1431 x_related_lines_tbl => x_req_related_lines_tbl,
1432 x_return_status => l_return_status,
1433 x_return_status_text => l_return_status_text);
1434
1435 x_return_status := l_return_status;
1436 x_return_status_text := l_return_status_text;
1437
1438 /* Dbms_Output.Put_Line('Error is '|| sqlerrm);
1439 Dbms_Output.Put_Line('After QP : Return Status text : '|| l_return_status || ' : ' || l_return_status_text);
1440 Dbms_Output.Put_Line('+---------Information returned to caller:---------------------+ ');
1441
1442 If nvl(x_req_line_tbl.count,0 ) > 0 Then
1443 Dbms_Output.Put_Line('-------------Line Information-------------------');
1444 For i in x_req_line_tbl.first .. x_req_line_tbl.last
1445 Loop
1446 Dbms_Output.Put_Line('Row index: '||i);
1447 Dbms_Output.Put_Line('line_index: '||x_req_line_tbl(I).line_index);
1448 Dbms_Output.Put_Line(' line id: '||x_req_line_tbl(I).line_id);
1449 Dbms_Output.Put_Line(' Unit_price: '||x_req_line_tbl(I).unit_price);
1450 Dbms_Output.Put_Line(' Percent price: '||x_req_line_tbl(I).percent_price);
1451 Dbms_Output.Put_Line(' Line quantity: '||x_req_line_tbl(I).line_quantity);
1452 Dbms_Output.Put_Line(' Parent price: '||x_req_line_tbl(I).parent_price);
1453 Dbms_Output.Put_Line(' Parent quant: '||x_req_line_tbl(I).parent_quantity);
1454 Dbms_Output.Put_Line(' Adjusted unit price: '||x_req_line_tbl(I).adjusted_unit_price);
1455 Dbms_Output.Put_Line(' Line UOM Code : '||x_req_line_tbl(I).line_uom_code);
1456 Dbms_Output.Put_Line(' UOM Quantity : '||x_req_line_tbl(I).uom_quantity);
1457 Dbms_Output.Put_Line(' Pricing status code: '||x_req_line_tbl(I).status_code);
1458 Dbms_Output.Put_Line(' Pricing quantity: '||x_req_line_tbl(I).priced_quantity);
1459 Dbms_Output.Put_Line(' Pricing uom code code: '||x_req_line_tbl(I).priced_uom_code);
1460 Dbms_Output.Put_Line(' Pricing status text: '||x_req_line_tbl(I).status_text);
1461 End Loop;
1462 End If;
1463
1464 If nvl(x_req_qual_tbl.count,0 ) > 0 Then
1465 Dbms_Output.Put_Line('-------------Qualifier Information-------------------');
1466 For i in x_req_qual_tbl.first .. x_req_qual_tbl.last
1467 Loop
1468 Dbms_Output.Put_Line('Row index: '||i);
1469 Dbms_Output.Put_Line('line_index: '||x_req_qual_tbl(I).line_index);
1470 Dbms_Output.Put_Line(' qual context: '||x_req_qual_tbl(I).qualifier_context);
1471 Dbms_Output.Put_Line(' qual attr: '||x_req_qual_tbl(I).qualifier_attribute);
1472 Dbms_Output.Put_Line(' qual attr value from: '||x_req_qual_tbl(I).qualifier_attr_value_from);
1473 Dbms_Output.Put_Line(' qual attr value to: '||x_req_qual_tbl(I).qualifier_attr_value_to);
1474 Dbms_Output.Put_Line(' comp opn code: '||x_req_qual_tbl(I).comparison_operator_code);
1475 Dbms_Output.Put_Line(' validated flag: '||x_req_qual_tbl(I).validated_flag);
1476 Dbms_Output.Put_Line(' status code: '||x_req_qual_tbl(I).status_code);
1477 Dbms_Output.Put_Line(' status text: '||x_req_qual_tbl(I).status_text);
1478 End Loop;
1479 End If;
1480
1481 If nvl(x_Req_line_attr_tbl.count,0 ) > 0 Then
1482 Dbms_Output.Put_Line('-------------Line Attr Information-------------------');
1483 For i in x_Req_line_attr_tbl.first .. x_Req_line_attr_tbl.last
1484 Loop
1485 Dbms_Output.Put_Line('Row index: '||i);
1486 Dbms_Output.Put_Line('line_index: '||x_Req_line_attr_tbl(I).line_index);
1487 Dbms_Output.Put_Line(' Prc context: '||x_Req_line_attr_tbl(I).pricing_context);
1488 Dbms_Output.Put_Line(' Prc attr: '||x_Req_line_attr_tbl(I).pricing_attribute);
1489 Dbms_Output.Put_Line(' Prc attr value from: '||x_Req_line_attr_tbl(I).pricing_attr_value_from);
1490 Dbms_Output.Put_Line(' Prc attr value to: '||x_Req_line_attr_tbl(I).pricing_attr_value_to);
1491 Dbms_Output.Put_Line(' validated flag: '||x_Req_line_attr_tbl(I).validated_flag);
1492 Dbms_Output.Put_Line(' status code: '||x_Req_line_attr_tbl(I).status_code);
1493 Dbms_Output.Put_Line(' status text: '||x_Req_line_attr_tbl(I).status_text);
1494 End Loop;
1495 End If;
1496
1497 If nvl(x_req_line_detail_tbl.count,0) > 0 Then
1498 Dbms_Output.Put_Line('------------Line Detail Information------------ ');
1499 For I in x_req_line_detail_tbl.first .. x_req_line_detail_tbl.last
1500 Loop
1501 Dbms_Output.Put_Line('Row index: '||i);
1502 Dbms_Output.Put_Line('line_detail_index: '||x_req_line_detail_tbl(I).line_detail_index);
1503 Dbms_Output.Put_Line(' line detail type:'||x_req_line_detail_tbl(I).line_detail_type_code);
1504 Dbms_Output.Put_Line(' line_index: '||x_req_line_detail_tbl(I).line_index);
1505 Dbms_Output.Put_Line(' list_header_id: '||x_req_line_detail_tbl(I).list_header_id);
1506 Dbms_Output.Put_Line(' list_line_id: '||x_req_line_detail_tbl(I).list_line_id);
1507 Dbms_Output.Put_Line(' list_line_type_code: '||x_req_line_detail_tbl(I).list_line_type_code);
1508 Dbms_Output.Put_Line(' Adjustment Amount : '||x_req_line_detail_tbl(I).adjustment_amount);
1509 Dbms_Output.Put_Line(' Line Quantity : '||x_req_line_detail_tbl(I).line_quantity);
1510 Dbms_Output.Put_Line(' List Price : '||x_req_line_detail_tbl(I).list_price);
1511 Dbms_Output.Put_Line(' Operand_calculation_code: '||x_req_line_detail_tbl(I).Operand_calculation_code);
1512 Dbms_Output.Put_Line(' Operand value: '||x_req_line_detail_tbl(I).operand_value);
1513 Dbms_Output.Put_Line(' Automatic Flag: '||x_req_line_detail_tbl(I).automatic_flag);
1514 Dbms_Output.Put_Line(' Overide Flag: '||x_req_line_detail_tbl(I).override_flag);
1515 Dbms_Output.Put_Line(' status_code: '||x_req_line_detail_tbl(I).status_code);
1516 Dbms_Output.Put_Line(' status text: '||x_req_line_detail_tbl(I).status_text);
1517 Dbms_Output.Put_Line('-------------------------------------------');
1518 End Loop;
1519 End If;
1520
1521 If nvl(x_req_related_lines_tbl.count,0) > 0 Then
1522 Dbms_Output.Put_Line('--------------Related Lines Information---------------');
1523 For i in x_req_related_lines_tbl.first .. x_req_related_lines_tbl.last
1524 Loop
1525 Dbms_Output.Put_Line('Row index: '||i);
1526 Dbms_Output.Put_Line('Line Index :'||x_req_related_lines_tbl(I).line_index);
1527 Dbms_Output.Put_Line('Line detail index: '||x_req_related_lines_tbl(I).LINE_DETAIL_INDEX);
1528 Dbms_Output.Put_Line(' Relationship type code: '||x_req_related_lines_tbl(I).relationship_type_code);
1529 Dbms_Output.Put_Line(' Related Line Index: '||x_req_related_lines_tbl(I).RELATED_LINE_INDEX);
1530 Dbms_Output.Put_Line(' Related line detail index: '||x_req_related_lines_tbl(I).related_line_detail_index);
1531 Dbms_Output.Put_Line(' Status Code: '|| x_req_related_lines_tbl(I).STATUS_CODE);
1532 End Loop;
1533 End If;
1534
1535 If nvl(x_req_line_detail_attr_tbl.count,0) > 0 Then
1536 Dbms_Output.Put_Line('-----------Attributes Information-------------');
1537 For i in x_req_line_detail_attr_tbl.first .. x_req_line_detail_attr_tbl.last
1538 Loop
1539 Dbms_Output.Put_Line('Row index: '||i);
1540 Dbms_Output.Put_Line('Line detail INDEX '||x_req_line_detail_attr_tbl(I).line_detail_index);
1541 Dbms_Output.Put_Line(' Pricing Context '||x_req_line_detail_attr_tbl(I).pricing_context);
1542 Dbms_Output.Put_Line(' Pricing attribute '||x_req_line_detail_attr_tbl(I).pricing_attribute);
1543 Dbms_Output.Put_Line(' Pricing attr value from '||x_req_line_detail_attr_tbl(I).pricing_attr_value_from);
1544 Dbms_Output.Put_Line(' Pircing attr value to '||x_req_line_detail_attr_tbl(I).pricing_attr_value_to);
1545 Dbms_Output.Put_Line(' Status Code '||x_req_line_detail_attr_tbl(I).status_code);
1546 End Loop;
1547 End If;
1548
1549 If nvl(x_req_line_detail_qual_tbl.count,0) > 0 Then
1550 Dbms_Output.Put_Line('-----------Qualifier Attributes Information-------------');
1551 For i in x_req_line_detail_qual_tbl.first .. x_req_line_detail_qual_tbl.last
1552 Loop
1553 Dbms_Output.Put_Line('Row index: '||i);
1554 Dbms_Output.Put_Line('Line detail INDEX '||x_req_line_detail_qual_tbl(I).line_detail_index);
1555 Dbms_Output.Put_Line(' Qualifier Context '||x_req_line_detail_qual_tbl(I).qualifier_context);
1556 Dbms_Output.Put_Line(' Qualifier attribute '||x_req_line_detail_qual_tbl(I).qualifier_attribute);
1557 Dbms_Output.Put_Line(' Qualifier attr value from '||x_req_line_detail_qual_tbl(I).qualifier_attr_value_from);
1558 Dbms_Output.Put_Line(' Qualifier attr value to '||x_req_line_detail_qual_tbl(I).qualifier_attr_value_to);
1559 Dbms_Output.Put_Line(' Status Code '||x_req_line_detail_qual_tbl(I).status_code);
1560 Dbms_Output.Put_Line('---------------------------------------------------');
1561 End Loop;
1562 End If;
1563 Dbms_Output.Put_Line('+--------------------------------------------------------------+'); */
1564
1565 EXCEPTION
1566 WHEN FND_API.G_EXC_ERROR Then
1567 x_return_status := OKC_API.G_RET_STS_ERROR;
1568 WHEN FND_API.G_EXC_UNEXPECTED_ERROR Then
1569 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1570 WHEN OTHERS Then
1571 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1572 -- Dbms_Output.Put_Line('Error is '|| sqlerrm);
1573 If FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) Then
1574 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME ,
1575 'OKC_PRICE_PUB.CALL_PRICE_ENGINE',
1576 sqlerrm);
1577 End If;
1578 END Call_QP;
1579 --
1580 /**** For calculate_Price do we need the caller to fill up the contract_cp_tbl too?
1581 Do we need the contract_cp_tbl?? It should have the same info as the line_rec ??
1582 === This api is called from ANOTHER calculat_Prcie API which sets the reqd info
1583 properly =====***/
1584
1585 Procedure Calculate_Price(p_contract_line_rec IN OKC_PRICE_PUB.G_LINE_REC_TYPE,
1586 px_contract_cp_tbl IN OUT NOCOPY OKC_PRICE_PUB.G_SLINE_TBL_TYPE,
1587 px_message OUT NOCOPY VARCHAR2,
1588 x_return_status OUT NOCOPY VARCHAR2,
1589 x_msg_count OUT NOCOPY NUMBER,
1590 x_msg_data OUT NOCOPY VARCHAR2) IS
1591 l_return_status Varchar2(1) := OKC_API.G_RET_STS_SUCCESS;
1592 l_return_status_text Varchar2(240) := NULL;
1593 lx_req_line_tbl QP_PREQ_GRP.LINE_TBL_TYPE;
1594 lx_Req_qual_tbl QP_PREQ_GRP.QUAL_TBL_TYPE;
1595 lx_Req_line_attr_tbl QP_PREQ_GRP.LINE_ATTR_TBL_TYPE;
1596 lx_Req_LINE_DETAIL_tbl QP_PREQ_GRP.LINE_DETAIL_TBL_TYPE;
1597 lx_Req_LINE_DETAIL_qual_tbl QP_PREQ_GRP.LINE_DETAIL_QUAL_TBL_TYPE;
1598 lx_Req_LINE_DETAIL_attr_tbl QP_PREQ_GRP.LINE_DETAIL_ATTR_TBL_TYPE;
1599 lx_Req_related_lines_tbl QP_PREQ_GRP.RELATED_LINES_TBL_TYPE;
1600 l_idx NUMBER := 0;
1601 l_contract_line_rec OKC_PRICE_PUB.G_LINE_REC_TYPE := p_contract_line_rec;
1602 Begin
1603 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1604 If nvl(p_contract_line_rec.record_built_flag, 'Y') = 'N' Then
1605 --- Build the contract line record so that it can be used in sourcing pricing attributes
1606 Build_Okc_Kline_Rec(p_Contract_Line_Id => p_contract_line_rec.line_id,
1607 x_CONTRACT_Line_rec => l_contract_line_rec,
1608 x_return_status => l_return_status);
1609 If l_return_status <> OKC_API.G_RET_STS_SUCCESS Then
1610 Raise G_BUILD_RECORD_FAILED;
1611 End If;
1612 Else
1613 l_contract_line_rec := p_contract_line_rec;
1614 End If;
1615
1616 If l_contract_line_rec.price_list_id is null Or
1617 l_contract_line_rec.inventory_item_id is null Or
1618 l_contract_line_rec.item_qty is null Or
1619 l_contract_line_rec.item_uom_code is null Or
1620 -- l_contract_line_rec.start_date is null Or
1621 l_contract_line_rec.currency_code is null Then
1622 Raise G_REQUIRED_ATTR_FAILED;
1623 End If;
1624 -- Dbms_Output.Put_Line('Before Call_Qp');
1625 Call_QP(p_contract_line_rec => l_contract_line_rec,
1626 p_contract_cp_tbl => px_contract_cp_tbl,
1627 p_get_pb_tbl_flag => 'N',
1628 x_Req_line_tbl => lx_req_line_tbl,
1629 x_Req_qual_tbl => lx_Req_qual_tbl,
1630 x_Req_line_attr_tbl => lx_Req_line_attr_tbl,
1631 x_Req_line_detail_tbl => lx_req_line_detail_tbl,
1632 x_Req_line_detail_qual_tbl => lx_req_line_detail_qual_tbl,
1633 x_Req_line_detail_attr_tbl => lx_req_line_detail_attr_tbl,
1634 x_Req_related_lines_tbl => lx_req_related_lines_tbl,
1635 x_return_status => l_return_status,
1636 x_return_status_text => l_return_status_text);
1637 -- Dbms_Output.Put_Line('After Call_Qp');
1638 -- Dbms_Output.Put_Line('Return Status text : '|| l_return_status || ' : ' || l_return_status_text);
1639
1640 If l_return_status <> OKC_API.G_RET_STS_SUCCESS Then
1641 -- Dbms_Output.Put_Line('Qp Engine Failed !!');
1642 Raise G_CALL_QP_FAILED;
1643 End If;
1644 -- Dbms_Output.Put_Line('Qp Engine Success !!');
1645
1646 --- Change is needed For priced qty/uom when qp is ready with uom conversion
1647 If nvl(l_contract_line_rec.usage_item_flag, 'N') = 'N' Then
1648 For i In px_contract_cp_tbl.first .. px_contract_cp_tbl.last
1649 Loop
1650 l_idx := i;
1651 -- l_idx := i * 2;
1652 px_contract_cp_tbl(i).priced_quantity := lx_req_line_tbl(l_idx).priced_quantity;
1653 px_contract_cp_tbl(i).priced_uom_code := lx_req_line_tbl(l_idx).priced_uom_code;
1654 -- px_contract_cp_tbl(i).priced_quantity := lx_req_line_tbl(l_idx).priced_quantity;
1655 -- px_contract_cp_tbl(i).priced_uom_code := lx_req_line_tbl(l_idx).priced_uom_code;
1656 px_contract_cp_tbl(i).currency_code := lx_req_line_tbl(l_idx).currency_code;
1657 -- px_contract_cp_tbl(i).unit_price := lx_req_line_tbl(l_idx).unit_price;
1658 px_contract_cp_tbl(i).unit_price := lx_req_line_tbl(l_idx).unit_price *
1659 (lx_req_line_tbl(l_idx).priced_quantity /
1660 px_contract_cp_tbl(i).item_qty);
1661 px_contract_cp_tbl(i).adjusted_unit_price := lx_req_line_tbl(l_idx).adjusted_unit_price;
1662 px_contract_cp_tbl(i).cp_unit_price := lx_req_line_tbl(l_idx).parent_price;
1663
1664 -- Not yet ready for OKC. percent_price will not be used.
1665 If nvl(lx_req_line_tbl(l_idx).percent_price,0) > 0 Then
1666 px_contract_cp_tbl(i).unit_percent := lx_req_line_tbl(l_idx).percent_price;
1667 px_contract_cp_tbl(i).extended_amount := (lx_req_line_tbl(l_idx).percent_price *
1668 lx_req_line_tbl(l_idx).parent_price) / 100.0 *
1669 lx_req_line_tbl(l_idx).line_quantity *
1670 lx_req_line_tbl(l_idx).priced_quantity;
1671 -- px_contract_cp_tbl(i).item_qty;
1672 Else
1673 -- Commented out the following, used mainly for Service contracts
1674 /* px_contract_cp_tbl(i).extended_amount := lx_req_line_tbl(l_idx).unit_price *
1675 lx_req_line_tbl(l_idx).line_quantity *
1676 px_contract_cp_tbl(i).item_qty; */
1677 px_contract_cp_tbl(i).extended_amount := lx_req_line_tbl(l_idx).unit_price *
1678 lx_req_line_tbl(l_idx).priced_quantity;
1679 -- px_contract_cp_tbl(i).item_qty;
1680 End If;
1681 px_message := lx_req_line_tbl(l_idx).status_text;
1682 End Loop;
1683 Else
1684 -- Used for Service contracts
1685 For i In px_contract_cp_tbl.first .. px_contract_cp_tbl.last
1686 Loop
1687 --- this part needs to be worked out
1688 px_contract_cp_tbl(i).unit_price := lx_req_line_tbl(i).unit_price;
1689 px_contract_cp_tbl(i).unit_percent := lx_req_line_tbl(i).percent_price;
1690 px_contract_cp_tbl(i).priced_quantity := lx_req_line_tbl(i).priced_quantity;
1691 px_contract_cp_tbl(i).priced_uom_code := lx_req_line_tbl(i).priced_uom_code;
1692 px_contract_cp_tbl(i).currency_code := lx_req_line_tbl(i).currency_code;
1693 px_contract_cp_tbl(i).adjusted_unit_price := lx_req_line_tbl(i).adjusted_unit_price;
1694 px_contract_cp_tbl(i).extended_amount := px_contract_cp_tbl(i).adjusted_unit_price *
1695 px_contract_cp_tbl(i).item_qty;
1696 End Loop;
1697 End If;
1698 EXCEPTION
1699 When G_BUILD_RECORD_FAILED Then
1700 x_return_status := OKC_API.G_RET_STS_ERROR;
1701 If FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) Then
1702 FND_MESSAGE.SET_NAME('OKC','OKC_DATA_NOT_POSTED');
1703 FND_MSG_PUB.Add;
1704 End If;
1705 FND_MSG_PUB.COUNT_AND_GET(p_count => x_msg_count,
1706 p_data => x_msg_data,
1707 p_encoded => FND_API.G_FALSE);
1708 When G_REQUIRED_ATTR_FAILED Then
1709 x_return_status := OKC_API.G_RET_STS_ERROR;
1710 If FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) Then
1711 FND_MESSAGE.SET_NAME('OKC','OKC_ITEM_UOM_QTY_NUMM');
1712 FND_MSG_PUB.Add;
1713 End If;
1714 FND_MSG_PUB.COUNT_AND_GET(p_count => x_msg_count,
1715 p_data => x_msg_data,
1716 p_encoded => FND_API.G_FALSE);
1717 When G_CALL_QP_FAILED Then
1718 x_return_status := OKC_API.G_RET_STS_ERROR;
1719 If FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) Then
1720 FND_MESSAGE.SET_NAME('OKC','OKC_QP_FAILED');
1721 FND_MSG_PUB.Add;
1722 End If;
1723 FND_MSG_PUB.COUNT_AND_GET(p_count => x_msg_count,
1724 p_data => x_msg_data,
1725 p_encoded => FND_API.G_FALSE);
1726
1727 When G_EXCEPTION_HALT_VALIDATION Then
1728 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1729 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR, G_SQLCODE_TOKEN,
1730 SQLCODE, G_SQLERRM_TOKEN,SQLERRM);
1731 When FND_API.G_EXC_ERROR Then
1732 x_return_status := OKC_API.G_RET_STS_ERROR;
1733 When FND_API.G_EXC_UNEXPECTED_ERROR Then
1734 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1735 When OTHERS Then
1736 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1737 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR, G_SQLCODE_TOKEN,
1738 SQLCODE, G_SQLERRM_TOKEN, SQLERRM);
1739 End Calculate_Price;
1740 --
1741
1742 /*** For OKC we need to call this API..... ***/
1743
1744 Procedure Calculate_Price(p_clev_tbl OKC_CONTRACT_PUB.clev_tbl_type,
1745 p_cimv_tbl OKC_CONTRACT_ITEM_PUB.cimv_tbl_type,
1746 px_message OUT NOCOPY VARCHAR2,
1747 px_contract_cp_tbl IN OUT NOCOPY OKC_PRICE_PUB.G_SLINE_TBL_TYPE,
1748 x_return_status OUT NOCOPY VARCHAR2,
1749 x_msg_count OUT NOCOPY NUMBER,
1750 x_msg_data OUT NOCOPY VARCHAR2) IS
1751 l_contract_line_rec OKC_PRICE_PUB.G_LINE_REC_TYPE;
1752 l_return_status Varchar2(1) := OKC_API.G_RET_STS_SUCCESS;
1753 l_price_list_id Number;
1754 l_inventory_item_id Number;
1755 l_uom_code Varchar2(3);
1756 l_qty Number;
1757 l_cur_code Varchar2(15);
1758 QP_Not_Installed Exception;
1759 Mandatory_Data_Missing Exception;
1760 Begin
1761 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1762 --Bug 2752224--Moving after price list check to avoid error
1763 -- when user intends to enter price manually
1764 /*If Not Product_Installed('QP') Then
1765 Raise QP_Not_Installed;
1766 End If;
1767 */
1768 Get_Price_List(p_clev_tbl,
1769 l_price_list_id,
1770 l_return_status);
1771 If l_return_status <> OKC_API.G_RET_STS_SUCCESS Then
1772 Raise Mandatory_Data_Missing;
1773 Else
1774 if l_price_list_id Is Null Then
1775 -- It is success. Either there is no Pricing Rule attached or
1776 -- NOLOV Price List is attached to the Rule. These 2 should
1777 -- not be reported as error.
1778 Raise Mandatory_Data_Missing;
1779 End If;
1780 End If;
1781
1782 --Check for QP installation after checking for price list
1783 If Not Product_Installed('QP') Then
1784 Raise QP_Not_Installed;
1785 End If;
1786
1787 Get_Inventory_Item(p_clev_tbl,
1788 p_cimv_tbl,
1789 l_inventory_item_id,
1790 l_uom_code,
1791 l_qty,
1792 l_return_status);
1793 If l_return_status <> OKC_API.G_RET_STS_SUCCESS Then
1794 Raise Mandatory_Data_Missing;
1795 End If;
1796 Get_Currency_Code(p_clev_tbl,
1797 l_cur_code,
1798 l_return_status);
1799 If l_return_status <> OKC_API.G_RET_STS_SUCCESS Then
1800 Raise Mandatory_Data_Missing;
1801 End If;
1802 /* dbms_output.put_line('line_id ' || p_cle_id);
1803 dbms_output.put_line('price_list_id ' || l_price_list_id);
1804 dbms_output.put_line('inventory_item_id ' || l_inventory_item_id);
1805 dbms_output.put_line('item_qty ' || l_qty);
1806 dbms_output.put_line('item_uom_code ' || l_uom_code);
1807 dbms_output.put_line('currency_code ' || l_cur_code); */
1808 If p_clev_tbl(1).id Is Not Null Then
1809 l_contract_line_rec.line_id := p_clev_tbl(1).id;
1810 End If;
1811 l_contract_line_rec.price_list_id := l_price_list_id;
1812 l_contract_line_rec.inventory_item_id := l_inventory_item_id;
1813 l_contract_line_rec.item_qty := l_qty;
1814 l_contract_line_rec.item_uom_code := l_uom_code;
1815 l_contract_line_rec.currency_code := l_cur_code;
1816 l_contract_line_rec.usage_item_flag := 'N';
1817 l_contract_line_rec.record_built_flag := 'Y';
1818
1819 px_contract_cp_tbl(1).inventory_item_id := l_inventory_item_id;
1820 px_contract_cp_tbl(1).item_qty := l_qty;
1821 px_contract_cp_tbl(1).item_uom_code := l_uom_code;
1822 px_contract_cp_tbl(1).currency_code := l_cur_code;
1823
1824 /*** The line_rec and contract_cp_tbl contain the same info ***/
1825 Calculate_Price(l_contract_line_rec,
1826 px_contract_cp_tbl,
1827 px_message,
1828 x_return_status,
1829 x_msg_count,
1830 x_msg_data);
1831 EXCEPTION
1832 When QP_Not_Installed Then
1833 -- dbms_output.put_line('QP_NOT_INSTALLED');
1834 x_return_status := OKC_API.G_RET_STS_ERROR;
1835 When Mandatory_Data_Missing Then
1836 -- dbms_output.put_line('Mandatory_Data_Missing');
1837 x_return_status := l_return_status;
1838 When OTHERS Then
1839 -- dbms_output.put_line('Unexpected Errors');
1840 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1841 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR, G_SQLCODE_TOKEN,
1842 SQLCODE, G_SQLERRM_TOKEN, SQLERRM);
1843 End Calculate_Price;
1844 --
1845 Procedure Calculate_Price(p_clev_tbl OKC_CONTRACT_PUB.clev_tbl_type,
1846 p_cimv_tbl OKC_CONTRACT_ITEM_PUB.cimv_tbl_type,
1847 px_unit_price OUT NOCOPY Number,
1848 px_extended_amount OUT NOCOPY Number,
1849 px_message OUT NOCOPY Varchar2,
1850 x_return_status OUT NOCOPY VARCHAR2,
1851 x_msg_count OUT NOCOPY NUMBER,
1852 x_msg_data OUT NOCOPY VARCHAR2) IS
1853 l_return_status Varchar2(1) := OKC_API.G_RET_STS_SUCCESS;
1854 l_contract_cp_tbl G_SLINE_TBL_TYPE;
1855 Data_Unposted Exception;
1856 Begin
1857 OKC_API.init_msg_list(OKC_API.G_FALSE);
1858 If (p_clev_tbl.COUNT = 0) Or
1859 (p_cimv_tbl.COUNT = 0) Then
1860 Raise Data_Unposted;
1861 End If;
1862 Calculate_Price(p_clev_tbl,
1863 p_cimv_tbl,
1864 px_message,
1865 l_contract_cp_tbl,
1866 l_return_status,
1867 x_msg_count,
1868 x_msg_data);
1869 x_return_status := l_return_status;
1870 If l_return_status = OKC_API.G_RET_STS_SUCCESS Then
1871 If l_contract_cp_tbl.count > 0 Then
1872 px_unit_price := l_contract_cp_tbl(1).unit_price;
1873 px_extended_amount := l_contract_cp_tbl(1).extended_amount;
1874 End If;
1875 End If;
1876 EXCEPTION
1877 When Data_Unposted Then
1878 x_return_status := OKC_API.G_RET_STS_ERROR;
1879 OKC_API.set_message(G_APP_NAME, 'OKC_DATA_NOT_POSTED');
1880 When OTHERS Then
1881 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1882 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR, G_SQLCODE_TOKEN,
1883 SQLCODE, G_SQLERRM_TOKEN, SQLERRM);
1884 End Calculate_Price;
1885 --
1886 -- ifilimon: added to support unit price retrieving by main item attributes
1887 FUNCTION Get_Unit_Price(
1888 p_price_list_id Number,
1889 p_inventory_item_id Number,
1890 p_uom_code Varchar2,
1891 p_cur_code Varchar2,
1892 p_qty NUMBER := 1
1893 ) RETURN NUMBER IS
1894 l_contract_cp_tbl OKC_PRICE_PUB.G_SLINE_TBL_TYPE;
1895 l_contract_line_rec OKC_PRICE_PUB.G_LINE_REC_TYPE;
1896 x_return_status Varchar2(1) := OKC_API.G_RET_STS_SUCCESS;
1897 x_message VARCHAR2(1000);
1898 x_msg_count NUMBER;
1899 x_msg_data VARCHAR2(2000);
1900 BEGIN
1901 l_contract_line_rec.price_list_id := p_price_list_id;
1902 l_contract_line_rec.inventory_item_id := p_inventory_item_id;
1903 l_contract_line_rec.item_uom_code := p_uom_code;
1904 l_contract_line_rec.currency_code := p_cur_code;
1905
1906 l_contract_line_rec.item_qty := p_qty;
1907 l_contract_line_rec.usage_item_flag := 'N';
1908 l_contract_line_rec.record_built_flag := 'Y';
1909
1910 l_contract_cp_tbl(1).inventory_item_id := l_contract_line_rec.inventory_item_id;
1911 l_contract_cp_tbl(1).item_qty := l_contract_line_rec.item_qty;
1912 l_contract_cp_tbl(1).item_uom_code := l_contract_line_rec.item_uom_code;
1913 l_contract_cp_tbl(1).currency_code := l_contract_line_rec.currency_code;
1914
1915 /*** The line_rec and contract_cp_tbl contain the same info ***/
1916 Calculate_Price(l_contract_line_rec,
1917 l_contract_cp_tbl,
1918 x_message,
1919 x_return_status,
1920 x_msg_count,
1921 x_msg_data);
1922 IF (x_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
1923 return l_contract_cp_tbl(1).unit_price;
1924 ELSE
1925 return NULL;
1926 END IF;
1927 EXCEPTION
1928 WHEN OTHERS THEN
1929 return NULL;
1930 END;
1931 --
1932 Procedure GET_PRICE_BREAK(
1933 p_contract_line_rec IN OKC_PRICE_PUB.G_LINE_REC_TYPE,
1934 x_price_break_tbl OUT NOCOPY OKC_PRICE_PUB.G_PRICE_BREAK_TBL_TYPE,
1935 x_return_status OUT NOCOPY VARCHAR2,
1936 x_msg_count OUT NOCOPY NUMBER,
1937 x_msg_data OUT NOCOPY VARCHAR2)
1938 IS
1939
1940 l_contract_line_rec OKC_PRICE_PUB.G_LINE_REC_TYPE;
1941
1942 ---- dummy table
1943 l_contract_cp_tbl OKC_PRICE_PUB.G_SLINE_TBL_TYPE;
1944 l_return_status varchar2(1) := OKC_API.G_RET_STS_SUCCESS;
1945 l_return_status_text varchar2(240) := NULL;
1946
1947 lx_req_line_tbl QP_PREQ_GRP.LINE_TBL_TYPE;
1948 lx_Req_qual_tbl QP_PREQ_GRP.QUAL_TBL_TYPE;
1949 lx_Req_line_attr_tbl QP_PREQ_GRP.LINE_ATTR_TBL_TYPE;
1950 lx_Req_LINE_DETAIL_tbl QP_PREQ_GRP.LINE_DETAIL_TBL_TYPE;
1951 lx_Req_LINE_DETAIL_qual_tbl QP_PREQ_GRP.LINE_DETAIL_QUAL_TBL_TYPE;
1952 lx_Req_LINE_DETAIL_attr_tbl QP_PREQ_GRP.LINE_DETAIL_ATTR_TBL_TYPE;
1953 lx_Req_related_lines_tbl QP_PREQ_GRP.RELATED_LINES_TBL_TYPE;
1954 l_price_break_tbl OKC_PRICE_PUB.G_PRICE_BREAK_TBL_TYPE;
1955 l_rel_index NUMBER;
1956
1957 CURSOR puom_cur(p_inv_id NUMBER) IS
1958 select primary_uom_code from OKX_SYSTEM_ITEMS_V
1959 WHERE ID1 = p_inv_id;
1960 BEGIN
1961 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1962
1963 If nvl(p_contract_line_rec.record_built_flag, 'Y') = 'N' Then
1964 --- Build the contract line record so that it can be used in sourcing pricing attributes
1965 BUILD_OKC_KLINE_REC(
1966 p_Contract_Line_Id => p_contract_line_rec.line_id,
1967 x_CONTRACT_Line_rec => l_contract_line_rec,
1968 x_return_status => l_return_status);
1969 ----Dbms_Output.Put_Line('RETURN STATUS ' || l_return_status);
1970 If l_return_status <> OKC_API.G_RET_STS_SUCCESS Then
1971 RAISE G_BUILD_RECORD_FAILED;
1972 End If;
1973 Else
1974 l_contract_line_rec := p_contract_line_rec;
1975 End If;
1976
1977 -- Just to get price break, hard-code quantity to 1 and UOM to primary uom of the item
1978 l_contract_line_rec.item_qty := 1;
1979
1980 OPEN puom_cur(l_contract_line_rec.inventory_item_id);
1981 FETCH puom_cur INTO l_contract_line_rec.item_uom_code;
1982 CLOSE puom_cur;
1983
1984 If l_contract_line_rec.price_list_id is null Or
1985 l_contract_line_rec.inventory_item_id is null Or
1986 l_contract_line_rec.item_qty is null Or
1987 l_contract_line_rec.item_uom_code is null Or
1988 l_contract_line_rec.start_date is null Or
1989 l_contract_line_rec.currency_code is null Then
1990 RAISE G_REQUIRED_ATTR_FAILED;
1991 End If;
1992 ----Dbms_Output.Put_Line ('Before Calling QP');
1993 CALL_QP(
1994 p_contract_line_rec => l_contract_line_rec,
1995 p_contract_cp_tbl => l_contract_cp_tbl,
1996 p_get_pb_tbl_flag => 'Y',
1997 x_Req_line_tbl => lx_req_line_tbl,
1998 x_Req_qual_tbl => lx_Req_qual_tbl,
1999 x_Req_line_attr_tbl => lx_Req_line_attr_tbl,
2000 x_Req_line_detail_tbl => lx_req_line_detail_tbl,
2001 x_Req_line_detail_qual_tbl => lx_req_line_detail_qual_tbl,
2002 x_Req_line_detail_attr_tbl => lx_req_line_detail_attr_tbl,
2003 x_Req_related_lines_tbl => lx_req_related_lines_tbl,
2004 x_return_status => l_return_status,
2005 x_return_status_text => l_return_status_text);
2006
2007 If l_return_status <> OKC_API.G_RET_STS_SUCCESS Then
2008 RAISE G_CALL_QP_FAILED;
2009 End If;
2010
2011 For i in lx_req_related_lines_tbl.first .. lx_req_related_lines_tbl.last
2012 Loop
2013 If lx_req_related_lines_tbl(i).relationship_type_code = QP_PREQ_GRP.G_PBH_LINE Then
2014 l_rel_index := lx_req_related_lines_tbl(i).related_line_detail_index;
2015 ---Dbms_Output.Put_Line('REL INDEX ' || l_rel_index);
2016 For j in lx_req_line_detail_attr_tbl.first .. lx_req_line_detail_attr_tbl.last
2017 Loop
2018 If lx_req_line_detail_attr_tbl(j).line_detail_index = l_rel_index Then
2019 ---Dbms_Output.Put_Line('LINE DETAIL INDEX ' || lx_req_line_detail_attr_tbl(j).line_detail_index);
2020 l_price_break_tbl(i).quantity_from :=
2021 lx_req_line_detail_attr_tbl(j).PRICING_ATTR_VALUE_FROM ;
2022 l_price_break_tbl(i).quantity_to :=
2023 lx_req_line_detail_attr_tbl(j).PRICING_ATTR_VALUE_TO ;
2024 End If;
2025 End Loop;
2026 For y in lx_req_line_detail_tbl.first .. lx_req_line_detail_tbl.last
2027 Loop
2028 If lx_req_line_detail_tbl(y).line_detail_index = l_rel_index Then
2029 l_price_break_tbl(i).list_price :=
2030 lx_req_line_detail_tbl(y).list_price;
2031 l_price_break_tbl(i).break_method :=
2032 lx_req_line_detail_tbl(y).price_break_type_code;
2033 End If;
2034 End Loop;
2035
2036 --Dbms_Output.Put_Line(l_price_break_tbl(i).quantity_from || ' - ' ||
2037 --l_price_break_tbl(i).quantity_to || ' - ' ||
2038 --l_price_break_tbl(i).list_price || ' - ' ||
2039 --l_price_break_tbl(i).break_method);
2040 End If;
2041 End Loop;
2042 x_price_break_tbl := l_price_break_tbl;
2043 EXCEPTION
2044 WHEN G_BUILD_RECORD_FAILED Then
2045 x_return_status := OKC_API.G_RET_STS_ERROR;
2046 If FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2047 Then
2048 FND_MESSAGE.SET_NAME('OKC','OKC_DATA_NOT_POSTED');
2049 FND_MSG_PUB.Add;
2050 End If;
2051
2052 FND_MSG_PUB.COUNT_AND_GET(
2053 p_count => x_msg_count,
2054 p_data => x_msg_data,
2055 p_encoded => FND_API.G_FALSE);
2056
2057 WHEN G_REQUIRED_ATTR_FAILED Then
2058 x_return_status := OKC_API.G_RET_STS_ERROR;
2059 If FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2060 Then
2061 FND_MESSAGE.SET_NAME('OKC','OKC_ITEM_UOM_QTY_NULL');
2062 FND_MSG_PUB.Add;
2063 End If;
2064
2065 FND_MSG_PUB.COUNT_AND_GET(
2066 p_count => x_msg_count,
2067 p_data => x_msg_data,
2068 p_encoded => FND_API.G_FALSE);
2069
2070 WHEN G_CALL_QP_FAILED Then
2071 x_return_status := OKC_API.G_RET_STS_ERROR;
2072 If FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2073 Then
2074 FND_MESSAGE.SET_NAME('OKC','OKC_QP_FAILED');
2075 FND_MSG_PUB.Add;
2076 End If;
2077
2078 FND_MSG_PUB.COUNT_AND_GET(
2079 p_count => x_msg_count,
2080 p_data => x_msg_data,
2081 p_encoded => FND_API.G_FALSE);
2082
2083 WHEN G_EXCEPTION_HALT_VALIDATION Then
2084 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2085 OKC_API.set_message(G_APP_NAME,G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE, G_SQLERRM_TOKEN,SQLERRM);
2086 WHEN FND_API.G_EXC_ERROR Then
2087 x_return_status := OKC_API.G_RET_STS_ERROR;
2088 WHEN FND_API.G_EXC_UNEXPECTED_ERROR Then
2089 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2090 WHEN OTHERS Then
2091 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2092 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
2093
2094 END GET_PRICE_BREAK;
2095
2096 /*--------Begin Added by Jomy--------*/
2097 Procedure Get_Customer_Info(p_cust_id NUMBER)
2098 IS
2099 v_count NUMBER := 1;
2100 l_temp_tbl QP_Attr_Mapping_PUB.t_MultiRecord;
2101 i pls_integer;
2102 BEGIN
2103 G_Customer_Info.customer_id := p_cust_id;
2104 -- Getting info from OKX_CUSTOMER_ACCOUNTS_V
2105 BEGIN
2106 SELECT customer_class_code,
2107 sales_channel_code,
2108 gsa_indicator
2109 INTO G_Customer_Info.customer_class_code,
2110 G_Customer_Info.sales_channel_code,
2111 G_Customer_Info.gsa_indicator
2112 ---FROM ra_customers
2113 FROM OKX_CUSTOMER_ACCOUNTS_V
2114 WHERE ID1 = p_cust_id;
2115 --- WHERE CUST_ACCOUNT_ID = p_cust_id;
2116 ---FROM RA_customers
2117 --- WHERE customer_id = p_cust_id;
2118 EXCEPTION
2119 WHEN no_data_found THEN
2120 G_Customer_Info.customer_class_code := null;
2121 G_Customer_Info.sales_channel_code := null;
2122 G_Customer_Info.gsa_indicator := null;
2123 END;
2124 -- Getting Account Types
2125 Begin
2126 --------SELECT distinct customer_profile_class_id
2127 SELECT distinct PROFILE_CLASS_ID
2128 BULK COLLECT INTO l_temp_tbl
2129 FROM OKX_CUSTOMER_PROFILES_V
2130 WHERE cust_account_id = p_cust_id;
2131 --------FROM AR_CUSTOMER_PROFILES
2132 --------WHERE customer_id = p_cust_id;
2133
2134 i:= l_temp_tbl.first;
2135 While i is not null
2136 LOOP
2137 G_Customer_Info.account_types(v_count) := l_temp_tbl(i);
2138 v_count := v_count + 1;
2139 i:=l_temp_tbl.next(i);
2140 END LOOP;
2141 EXCEPTION
2142 When others then
2143 null;
2144 END;
2145 -- Get Customer Relationships
2146 l_temp_tbl.delete;
2147 v_count := 1;
2148 BEGIN
2149 -- SELECT RELATED_CUSTOMER_ID
2150 SELECT ID1
2151 BULK COLLECT INTO l_temp_tbl
2152 FROM OKX_CUST_ACCT_RELATE_V
2153 WHERE customer_id = p_cust_id;
2154 ---FROM RA_CUSTOMER_RELATIONSHIPS
2155 ---WHERE customer_id = p_cust_id;
2156 i:= l_temp_tbl.first;
2157 While i is not null
2158 LOOP
2159 G_Customer_Info.customer_relationships(v_count) := l_temp_tbl(i);
2160 v_count := v_count + 1;
2161 i:=l_temp_tbl.next(i);
2162 END LOOP;
2163 EXCEPTION
2164 When others then
2165 null;
2166 END;
2167 END Get_Customer_Info;
2168
2169 FUNCTION Get_Item_Category
2170 (
2171 p_inventory_item_id IN NUMBER,
2172 p_org_id IN NUMBER
2173 )
2174 RETURN QP_Attr_Mapping_PUB.t_MultiRecord
2175 IS
2176
2177 x_category_ids QP_Attr_Mapping_PUB.t_MultiRecord;
2178
2179 BEGIN
2180 -- Bug 2304008 skekkar
2181 SELECT ic.id1
2182 BULK COLLECT
2183 INTO x_category_ids
2184 FROM OKX_ITEM_CATEGORIES_V ic,
2185 MTL_CATEGORIES_B c,
2186 FND_ID_FLEX_STRUCTURES fs
2187 WHERE c.category_id = ic.category_id
2188 AND c.structure_id = fs.id_flex_num
2189 AND fs.id_flex_structure_code = 'ITEM_CATEGORIES'
2190 AND ic.inventory_item_id = p_inventory_item_id
2191 AND ic.organization_id = p_org_id;
2192
2193 /*
2194 SELECT ID1
2195 BULK COLLECT
2196 INTO x_category_ids
2197 FROM OKX_ITEM_CATEGORIES_V
2198 WHERE inventory_item_id = p_inventory_item_id
2199 AND organization_id = p_org_id;
2200 */
2201 /********************************************************
2202 --SELECT category_id BULK COLLECT INTO x_category_ids
2203 FROM mtl_item_categories
2204 --WHERE inventory_item_id = p_inventory_item_id
2205 --AND organization_id = p_org_id;
2206 *********************************************************/
2207 RETURN x_category_ids;
2208
2209 END Get_Item_Category;
2210 ---******************************
2211 FUNCTION Get_Customer_Class(p_cust_id IN NUMBER) RETURN VARCHAR2
2212 IS
2213
2214 BEGIN
2215 IF G_Customer_Info.customer_id = p_cust_id then
2216 return G_Customer_Info.customer_class_code;
2217 ELSE
2218 Get_Customer_Info(p_cust_id);
2219 return G_Customer_Info.customer_class_code;
2220 END IF;
2221 END Get_Customer_Class;
2222 ---***-- END Get_Customer_Class ---************************
2223 FUNCTION Get_Account_Type (p_cust_id IN NUMBER) RETURN QP_Attr_Mapping_PUB.t_MultiRecord
2224 IS
2225 BEGIN
2226 IF p_cust_id = G_Customer_Info.customer_id THEN
2227 RETURN G_Customer_Info.account_types;
2228 ELSE
2229 Get_Customer_Info(p_cust_id);
2230 RETURN G_Customer_Info.account_types;
2231 END IF;
2232 END Get_Account_Type;
2233 ---***-- END Get_Account_Type ---************************
2234
2235 FUNCTION Get_Sales_Channel (p_cust_id IN NUMBER) RETURN VARCHAR2
2236 IS
2237 BEGIN
2238 IF G_Customer_Info.customer_id = p_cust_id then
2239 return G_Customer_Info.sales_channel_code;
2240 ELSE
2241 Get_Customer_Info(p_cust_id);
2242 return G_Customer_Info.sales_channel_code;
2243 END IF;
2244 END Get_Sales_Channel;
2245 ---***-- END Get_Sales_Channel ---************************
2246 FUNCTION Get_GSA (p_cust_id NUMBER) RETURN VARCHAR2
2247 IS
2248 BEGIN
2249 IF G_Customer_Info.customer_id = p_cust_id then
2250 return G_Customer_Info.gsa_indicator;
2251 ELSE
2252 Get_Customer_Info(p_cust_id);
2253 return G_Customer_Info.gsa_indicator;
2254 END IF;
2255 END Get_GSA;
2256 ---***-- END Get_GSA ---************************
2257 PROCEDURE Get_Item_Segments_All(p_inventory_item_id IN NUMBER, p_org_id IN NUMBER
2258 )
2259 IS
2260 BEGIN
2261 G_Item_Segments.inventory_item_id := p_inventory_item_id;
2262
2263 SELECT segment1,segment2,segment3,segment4,segment5,
2264 segment6,segment7,segment8,segment9,segment10,
2265 segment11,segment12,segment13,segment14,segment15,
2266 segment16,segment17,segment18,segment19,segment20
2267 INTO
2268 G_Item_Segments.segment1,G_Item_Segments.segment2,G_Item_Segments.segment3,
2269 G_Item_Segments.segment4,G_Item_Segments.segment5,G_Item_Segments.segment6,
2270 G_Item_Segments.segment7,G_Item_Segments.segment8,G_Item_Segments.segment9,
2271 G_Item_Segments.segment10,G_Item_Segments.segment11,G_Item_Segments.segment12,
2272 G_Item_Segments.segment13,G_Item_Segments.segment14,G_Item_Segments.segment15,
2273 G_Item_Segments.segment16,G_Item_Segments.segment17,G_Item_Segments.segment18,
2274 G_Item_Segments.segment19,G_Item_Segments.segment20
2275 FROM okx_system_items_v
2276 WHERE inventory_item_id = p_inventory_item_id
2277 AND organization_id = p_org_id;
2278 ---FROM mtl_system_items
2279 --- WHERE inventory_item_id = p_inventory_item_id
2280 --- AND organization_id = p_org_id;
2281 END Get_Item_Segments_All;
2282 ---******************************
2283 FUNCTION Get_Item_Segment
2284 ( p_inventory_item_id IN NUMBER,
2285 p_org_id IN NUMBER,
2286 p_seg_num NUMBER
2287 )
2288 RETURN VARCHAR2
2289 IS
2290 l_segment_name VARCHAR2(30);
2291 BEGIN
2292 IF p_inventory_item_id <> G_Item_Segments.inventory_item_id THEN
2293 Get_Item_Segments_All(p_inventory_item_id,p_org_id);
2294 END IF;
2295 IF p_seg_num = 1 THEN
2296 RETURN G_Item_Segments.segment1;
2297 ELSIF p_seg_num = 2 THEN
2298 RETURN G_Item_Segments.segment2;
2299 ELSIF p_seg_num = 3 THEN
2300 RETURN G_Item_Segments.segment3;
2301 ELSIF p_seg_num = 4 THEN
2302 RETURN G_Item_Segments.segment4;
2303 ELSIF p_seg_num = 5 THEN
2304 RETURN G_Item_Segments.segment5;
2305 ELSIF p_seg_num = 6 THEN
2306 RETURN G_Item_Segments.segment6;
2307 ELSIF p_seg_num = 7 THEN
2308 RETURN G_Item_Segments.segment7;
2309 ELSIF p_seg_num = 8 THEN
2310 RETURN G_Item_Segments.segment8;
2311 ELSIF p_seg_num = 9 THEN
2312 RETURN G_Item_Segments.segment9;
2313 ELSIF p_seg_num = 10 THEN
2314 RETURN G_Item_Segments.segment10;
2315 ELSIF p_seg_num = 11 THEN
2316 RETURN G_Item_Segments.segment11;
2317 ELSIF p_seg_num = 12 THEN
2318 RETURN G_Item_Segments.segment12;
2319 ELSIF p_seg_num = 13 THEN
2320 RETURN G_Item_Segments.segment13;
2321 ELSIF p_seg_num = 14 THEN
2322 RETURN G_Item_Segments.segment14;
2323 ELSIF p_seg_num = 15 THEN
2324 RETURN G_Item_Segments.segment15;
2325 ELSIF p_seg_num = 16 THEN
2326 RETURN G_Item_Segments.segment16;
2327 ELSIF p_seg_num = 17 THEN
2328 RETURN G_Item_Segments.segment17;
2329 ELSIF p_seg_num = 18 THEN
2330 RETURN G_Item_Segments.segment18;
2331 ELSIF p_seg_num = 19 THEN
2332 RETURN G_Item_Segments.segment19;
2333 ELSIF p_seg_num = 20 THEN
2334 RETURN G_Item_Segments.segment20;
2335 END IF;
2336 END Get_Item_Segment;
2337 ---******************************
2338 /*End Added by Jomy*/
2339 -----attribute mapping functions added by smhanda-------------------------------
2340
2341 FUNCTION Get_Site_Use (p_rul_tbl IN GLOBAL_RPRLE_TBL_TYPE) RETURN QP_Attr_Mapping_PUB.t_MultiRecord
2342 IS
2343
2344 x_site_use_info QP_Attr_Mapping_PUB.t_MultiRecord;
2345 l_ship_to_org_id number :=null;
2346 l_invoice_to_org_id number :=null;
2347 i pls_integer :=0;
2348 BEGIN
2349 i:=p_rul_tbl.first;
2350 While i is not null LOOP
2351 If l_ship_to_org_id is not null and l_invoice_to_org_id is not null then
2352 exit;
2353 End If;
2354 If l_ship_to_org_id is null and p_rul_tbl(i).code = 'STO' and p_rul_tbl(i).current_source = 'OKX_SHIPTO' then
2355 l_ship_to_org_id := p_rul_tbl(i).source_value;
2356 END IF;
2357 If l_invoice_to_org_id is null and p_rul_tbl(i).code = 'BTO' and p_rul_tbl(i).current_source = 'OKX_BILLTO' then
2358 l_invoice_to_org_id := p_rul_tbl(i).source_value;
2359 END IF;
2360 i:= p_rul_tbl.next(i);
2361 End loop;
2362 IF l_ship_to_org_id is not null THEN
2363 x_site_use_info(1) := l_ship_to_org_id;
2364 IF l_invoice_to_org_id is not null and l_ship_to_org_id <> l_invoice_to_org_id THEN
2365 x_site_use_info(2) := l_invoice_to_org_id;
2366 END IF;
2367 ELSE IF l_invoice_to_org_id is not null THEN
2368 x_site_use_info(1) := l_invoice_to_org_id;
2369 END IF;
2370 END IF;
2371 RETURN x_site_use_info;
2372
2373 END Get_Site_Use;
2374
2375
2376 FUNCTION GET_INVOICE_TO_ORG_ID (p_rul_tbl IN GLOBAL_RPRLE_TBL_TYPE,p_rle_tbl IN GLOBAL_RPRLE_TBL_TYPE)
2377 RETURN NUMBER
2378 IS
2379
2380 l_invoice_to_org_id number;
2381 i pls_integer :=0;
2382 BEGIN
2383 i:=p_rul_tbl.first;
2384 While i is not null LOOP
2385 If p_rul_tbl(i).code = 'BTO' and p_rul_tbl(i).current_source = 'OKX_CUSTACCT' then
2386 l_invoice_to_org_id := p_rul_tbl(i).source_value;
2387 exit;
2388 END IF;
2389 i:= p_rul_tbl.next(i);
2390 End loop;
2391 IF l_invoice_to_org_id is null then
2392 i:=p_rle_tbl.first;
2393 While i is not null LOOP
2394 If p_rle_tbl(i).code = 'BILL_TO' and p_rle_tbl(i).current_source = 'OKX_CUSTACCT' then
2395 l_invoice_to_org_id := p_rle_tbl(i).source_value;
2396 exit;
2397 END IF;
2398 i:= p_rle_tbl.next(i);
2399 End loop;
2400 END IF;
2401
2402 return l_invoice_to_org_id;
2403 END GET_INVOICE_TO_ORG_ID;
2404
2405 --???see if the vaue here can be cached in
2406 FUNCTION GET_PARTY_ID (p_sold_to_org_id IN NUMBER) RETURN NUMBER IS
2407
2408 l_party_id NUMBER;
2409
2410 CURSOR get_party_id_cur(l_sold_to_org_id NUMBER) IS
2411 SELECT party_id
2412 FROM okx_customer_accounts_v
2413 WHERE id1 = l_sold_to_org_id;
2414
2415 BEGIN
2416 OPEN get_party_id_cur(p_sold_to_org_id);
2417 FETCH get_party_id_cur INTO l_party_id;
2418 CLOSE get_party_id_cur;
2419 RETURN l_party_id;
2420
2421
2422 EXCEPTION
2423 WHEN OTHERS THEN
2424 RETURN NULL;
2425 END GET_PARTY_ID;
2426
2427 FUNCTION GET_SHIP_TO_PARTY_SITE_ID(p_rul_tbl IN GLOBAL_RPRLE_TBL_TYPE) RETURN NUMBER IS
2428
2429 l_ship_to_party_site_id NUMBER;
2430 l_ship_id number;
2431 i pls_integer :=0;
2432 CURSOR get_ship_to_site_id_cur (l_ship_to_org_id NUMBER) IS
2433 SELECT a.party_site_id
2434 FROM okx_cust_sites_v a,
2435 okx_cust_site_uses_v b
2436 WHERE a.cust_acct_site_id = b.cust_acct_site_id
2437 AND b.id1 = l_ship_to_org_id
2438 AND b.site_use_code = 'SHIP_TO';
2439
2440 BEGIN
2441 i:=p_rul_tbl.first;
2442 While i is not null LOOP
2443 If p_rul_tbl(i).code = 'STO' and p_rul_tbl(i).current_source = 'OKX_SHIPTO' then
2444 l_ship_id := p_rul_tbl(i).source_value;
2445 exit;
2446 END IF;
2447 i:= p_rul_tbl.next(i);
2448 End loop;
2449 If l_ship_id is not null then
2450 OPEN get_ship_to_site_id_cur (l_ship_id);
2451 FETCH get_ship_to_site_id_cur INTO l_ship_to_party_site_id;
2452 CLOSE get_ship_to_site_id_cur;
2453 End If;
2454 RETURN l_ship_to_party_site_id;
2455
2456 EXCEPTION
2457 WHEN OTHERS THEN
2458 RETURN NULL;
2459 END GET_SHIP_TO_PARTY_SITE_ID;
2460
2461 FUNCTION GET_INVOICE_TO_PARTY_SITE_ID(p_rul_tbl IN GLOBAL_RPRLE_TBL_TYPE) RETURN NUMBER IS
2462
2463 l_bill_to_party_site_id NUMBER;
2464 l_bill_id number;
2465 i pls_integer :=0;
2466 CURSOR get_bill_to_site_id_cur (l_bill_to_org_id NUMBER) IS
2467 SELECT a.party_site_id
2468 FROM okx_cust_sites_v a,
2469 okx_cust_site_uses_v b
2470 WHERE a.cust_acct_site_id = b.cust_acct_site_id
2471 AND b.id1 = l_bill_to_org_id
2472 AND b.site_use_code = 'BILL_TO';
2473
2474 BEGIN
2475 i:=p_rul_tbl.first;
2476 While i is not null LOOP
2477 If p_rul_tbl(i).code = 'BTO' and p_rul_tbl(i).current_source = 'OKX_BILLTO' then
2478 l_bill_id := p_rul_tbl(i).source_value;
2479 exit;
2480 END IF;
2481 i:= p_rul_tbl.next(i);
2482 End loop;
2483 If l_bill_id is not null then
2484 OPEN get_bill_to_site_id_cur (l_bill_id);
2485 FETCH get_bill_to_site_id_cur INTO l_bill_to_party_site_id;
2486 CLOSE get_bill_to_site_id_cur;
2487 End If;
2488 RETURN l_bill_to_party_site_id;
2489
2490 EXCEPTION
2491 WHEN OTHERS THEN
2492 RETURN NULL;
2493 END GET_INVOICE_TO_PARTY_SITE_ID;
2494
2495 ---end added by smhanda----------------------------
2496
2497 END OKC_PRICE_PUB;