1 PACKAGE BODY okc_cfg_pub as
2 /* $Header: OKCPCFGB.pls 120.1 2005/06/28 12:10:32 smallya noship $ */
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5
6 -- -------------------------------------------------------------------------
7 -- GLOBAL VARIABLES
8 -- -------------------------------------------------------------------------
9 --l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
10 G_APP_NAME CONSTANT VARCHAR2(3) := OKC_API.G_APP_NAME;
11 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKC_CONTRACTS_UNEXPECTED_ERROR';
12 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'SQLERRM';
13 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'SQLCODE';
14 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OKC_CFG_PUB';
15 G_API_TYPE CONSTANT VARCHAR2(4) := '_PUB';
16 G_FILE_NAME CONSTANT VARCHAR2(12) := 'OKCPCFGB.pls';
17 G_QA_SUCCESS CONSTANT VARCHAR2(200) := 'OKC_CONTRACTS_QA_SUCCESS';
18 G_EXCEPTION_HALT_VALIDATION EXCEPTION;
19
20 -- ---------------------------------------------------------------------------
21 -- FUNCTION : cle_config_check
22 -- PURPOSE : To check whether the contract configuration exists
23 -- in the okc_k_lines_v Returns Y or N.
24 -- Default return value = N
25 -- ---------------------------------------------------------------------------
26 FUNCTION cle_config_check ( p_line_id IN NUMBER,
27 p_config_hdr_id IN NUMBER,
28 p_config_rev_nbr IN NUMBER)
29 RETURN VARCHAR2
30 IS
31 CURSOR cur_cle_config_check ( l_line_id NUMBER,
32 l_config_hdr_id NUMBER,
33 l_config_rev_nbr NUMBER )
34 IS
35 SELECT 'Y'
36 FROM okc_k_lines_v clev
37 WHERE clev.id = l_line_id
38 AND clev.config_item_type = 'TOP_MODEL_LINE'
39 AND clev.config_header_id = l_config_hdr_id
40 AND clev.config_revision_number = l_config_rev_nbr;
41
42 l_dummy VARCHAR2(1) := 'N';
43 BEGIN
44 OPEN cur_cle_config_check (p_line_id,
45 p_config_hdr_id,
46 p_config_rev_nbr);
47 FETCH cur_cle_config_check INTO l_dummy;
48 CLOSE cur_cle_config_check;
49
50 RETURN l_dummy;
51
52 END cle_config_check;
53
54 -- ---------------------------------------------------------------------------
55 -- PROCEDURE : build_cle_from_cz
56 -- PURPOSE : Create/Update/Delete Contract Lines based on
57 -- the termination of the configuration call.
58 -- gets the configuration details from the
59 -- CZ schema tables join to OKC lines table.
60 -- ---------------------------------------------------------------------------
61 PROCEDURE build_cle_from_cz (
62 p_api_version_number IN NUMBER ,
63 p_init_msg_list IN VARCHAR2,
64 p_commit IN VARCHAR2,
65 p_config_complete_yn IN VARCHAR2,
66 p_config_valid_yn IN VARCHAR2,
67 p_au_line_rec IN OKC_CFG_PUB.au_rec_type,
68 p_config_rec IN OKC_CFG_PUB.config_rec_type,
69 x_cfg_list_price OUT NOCOPY NUMBER,
70 x_cfg_net_price OUT NOCOPY NUMBER,
71 x_return_status OUT NOCOPY VARCHAR2,
72 x_msg_count OUT NOCOPY NUMBER,
73 x_msg_data OUT NOCOPY VARCHAR2 )
74 IS
75 -- -------------------------------------------------------------------------
76 -- To handle new configured rows insertion into the contract lines.
77 -- -------------------------------------------------------------------------
78 CURSOR cur_cle_ins ( l_dnz_chr_id NUMBER,
79 l_config_hdr_id NUMBER,
80 l_config_rev_nbr NUMBER ) IS
81 SELECT config_hdr_id,
82 config_rev_nbr ,
83 config_item_id ,
84 parent_config_item_id ,
85 inventory_item_id ,
86 organization_id ,
87 component_code ,
88 quantity ,
89 uom_code ,
90 'INSERT' -- operation
91 FROM okx_config_details_v czdv
92 WHERE czdv.config_hdr_id = l_config_hdr_id
93 AND czdv.config_rev_nbr = l_config_rev_nbr
94 AND NOT EXISTS (
95 SELECT 'x'
96 FROM okc_k_lines_v clev,
97 okc_k_items_v cimv
98 WHERE clev.id = cimv.cle_id
99 AND clev.dnz_chr_id = l_dnz_chr_id
100 AND clev.config_header_id = czdv.config_hdr_id
101 AND clev.config_revision_number = czdv.config_rev_nbr
102 AND cimv.object1_id1 = czdv.inventory_item_id)
103 ORDER BY bom_sort_order; -- Bug 2654009
104
105 -- --------------------------------------------------------------------
106 -- To Delete the earlier configuration revision lines to be replaced
107 -- with new config revision that is under process.
108 -- --------------------------------------------------------------------
109 CURSOR cur_old_config (l_config_hdr_id NUMBER,
110 l_config_rev_nbr NUMBER,
111 l_top_model_line_id NUMBER,
112 l_dnz_chr_id NUMBER) IS
113 SELECT cleb.id
114 FROM okc_k_lines_b cleb
115 WHERE cleb.dnz_chr_id = l_dnz_chr_id
116 AND cleb.id <> l_top_model_line_id
117 AND cleb.config_header_id = l_config_hdr_id
118 AND cleb.config_revision_number = l_config_rev_nbr;
119
120 -- Bug: 2627343 : Changed the reference from the view, to use the
121 -- base table instead, for perfomance.
122
123 -- -------------------------------------------------------------------------
124 -- To prepare pricing call related info
125 -- -------------------------------------------------------------------------
126 CURSOR cur_price_cle ( l_dnz_chr_id NUMBER,
127 l_config_hdr_id NUMBER,
128 l_config_rev_nbr NUMBER) IS
129
130 SELECT cleb.id id,
131 'P' pi_bpi,
132 cim.number_of_items qty,
133 cim.uom_code uom_code,
134 cleb.currency_code currency_code,
135 cim.jtot_object1_code object_code,
136 cim.object1_id1 id1,
137 cim.object1_id2 id2,
138 cleb.price_list_id price_list_id,
139 cleb.dnz_chr_id dnz_chr_id,
140 cleb.pricing_date pricing_date -- Added for Bug 2393302
141 FROM okc_k_lines_b cleb,
142 okc_k_items cim
143 WHERE cleb.dnz_chr_id = l_dnz_chr_id
144 AND cleb.config_header_id = l_config_hdr_id
145 AND cleb.config_revision_number = l_config_rev_nbr
146 AND cleb.id = cim.cle_id
147 AND cleb.config_item_type <> 'TOP_MODEL_LINE';
148
149 -- Bug: 2627343 : Changed the reference from the view, to use the
150 -- base table instead, for perfomance.
151
152 -- -------------------------------------------------------------------------
153 -- To get top-model-id. This is the top-base-line in contract lines.
154 -- The top-model-line is just an extra line to store rollup price info.
155 -- -------------------------------------------------------------------------
156 CURSOR cur_get_model_id ( l_dnz_chr_id NUMBER,
157 l_config_hdr_id NUMBER,
158 l_config_rev_nbr NUMBER) IS
159 SELECT cleb.id top_model_id,
160 cleb.config_top_model_line_id top_model_line_id
161 FROM okc_k_lines_b cleb
162 WHERE cleb.dnz_chr_id = l_dnz_chr_id
163 AND cleb.config_header_id = l_config_hdr_id
164 AND cleb.config_revision_number = l_config_rev_nbr
165 AND cleb.config_item_type = 'TOP_MODEL_LINE';
166
167 -- Bug: 2627343 : Changed the reference from the view, to use the
168 -- base table instead, for perfomance.
169
170
171 -- -------------------------------------------------------------------------
172 -- To get cle_id. Required to update the pricing info for related lines.
173 -- -------------------------------------------------------------------------
174 CURSOR cur_line_info ( l_line_id NUMBER )
175 IS
176 SELECT cleb.cle_id,
177 cleb.chr_id,
178 cleb.line_number,
179 cleb.display_sequence
180 FROM okc_k_lines_b cleb
181 WHERE cleb.id = l_line_id;
182
183 -- -------------------------------------------------------------------------
184 -- To get pricing date from OKC_K_HEADERS_B -- Added for Bug 2393302
185 -- -------------------------------------------------------------------------
186 CURSOR cur_hdr_pr_date ( l_dnz_chr_id NUMBER )
187 IS
188 SELECT chrv.pricing_date
189 FROM okc_k_headers_b chrv
190 WHERE chrv.id = l_dnz_chr_id;
191
192 -- -------------------------------------------------------------------------
193 -- To get price list id. Required to default value for config lines.
194 -- Modified for Bug 2393302 , getting pricing date also
195 -- -------------------------------------------------------------------------
196 CURSOR cur_pr_list ( l_line_id NUMBER )
197 IS
198 SELECT clev.price_list_id,clev.pricing_date
199 FROM okc_k_lines_b clev
200 WHERE clev.id = l_line_id;
201
202 /* Commented for Bug 2393302
203 -- -------------------------------------------------------------------------
204 -- To get price list id. Required to default value for config lines.
205 -- -------------------------------------------------------------------------
206 CURSOR cur_pr_list ( l_line_id NUMBER )
207 IS
208 SELECT clev.price_list_id
209 FROM okc_k_lines_b clev
210 WHERE clev.id = l_line_id;
211 */
212
213 -- -------------------------------------------------------------------------
214 -- build_cle_from_cz variable declarations:
215 -- -------------------------------------------------------------------------
216 l_api_name CONSTANT VARCHAR2(30) := 'build_cle_from_cz';
217 l_api_version_number CONSTANT NUMBER := 1.0;
218 l_init_msg_list VARCHAR2(1) := OKC_API.G_FALSE;
219 l_index BINARY_INTEGER := 0;
220 l_complete_configuration_flag VARCHAR2(3);
221 l_valid_configuration_flag VARCHAR2(3);
222 l_config_exists VARCHAR2(1) := 'N';
223 l_cle_id NUMBER;
224 l_line_id NUMBER;
225 l_quantity NUMBER;
226 l_inventory_item_id NUMBER;
227 l_uom_code VARCHAR2(3);
228 l_org_id NUMBER;
229 l_top_model_line_id NUMBER;
230 l_top_base_line_id NUMBER;
231 l_config_line_id NUMBER;
232 i NUMBER := 1;
233 l_chr_id NUMBER := 0;
234 l_cur_chr_id NUMBER := -999999999;
235 l_cur_cle_id NUMBER := -999999999;
236 l_cur_line_nbr NUMBER := 0;
237 l_display_sequence NUMBER := 0;
238 l_top_model_id NUMBER;
239 l_tot_negotiated NUMBER := 0;
240 l_tot_list_price NUMBER := 0;
241 l_item_name VARCHAR2(240);
242 l_price_list_id NUMBER;
243 l_hdr_pricing_date DATE; -- Added for Bug 2393302
244 l_line_pricing_date DATE; -- Added for Bug 2393302
245 l_pricing_date DATE; -- Added for Bug 2393302
246 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
247
248 -- ---------------------------------------------------------------
249 -- build_cle_from_cz Record type declarations:
250 -- ---------------------------------------------------------------
251 l_config_rec OKC_CFG_PUB.config_rec_type;
252 cz_track_tbl OKC_CFG_PUB.cz_track_tbl_type;
253 l_clev_rec OKC_CFG_PUB.clev_rec_type;
254 l_clev_rec_init OKC_CFG_PUB.clev_rec_type;
255 l_cimv_rec OKC_CFG_PUB.cimv_rec_type;
256 l_cimv_rec_init OKC_CFG_PUB.cimv_rec_type;
257 x_clev_rec OKC_CFG_PUB.clev_rec_type;
258 x_cimv_rec OKC_CFG_PUB.cimv_rec_type;
259
260 -- ---------------------------------------------------------------
261 -- build_cle_from_cz: Pricing related table of records
262 -- ---------------------------------------------------------------
263 l_control_rec OKC_PRICE_PVT.okc_control_rec_type;
264 l_CLE_PRICE_TBL OKC_PRICE_PVT.cle_price_tbl_type;
265 l_line_tbl OKC_PRICE_PVT.line_tbl_type;
266 l_req_line_tbl QP_PREQ_GRP.line_tbl_type;
267 l_req_line_qual_tbl QP_PREQ_GRP.qual_tbl_type;
268 l_req_line_attr_tbl QP_PREQ_GRP.line_attr_tbl_type;
269 l_req_line_detail_tbl QP_PREQ_GRP.line_detail_tbl_type;
270 l_req_line_detail_qual_tbl QP_PREQ_GRP.line_detail_qual_tbl_type;
271 l_req_line_detail_attr_tbl QP_PREQ_GRP.line_detail_attr_tbl_type;
272 l_req_related_line_tbl QP_PREQ_GRP.related_lines_tbl_type;
273 l_pricing_contexts_tbl QP_PREQ_GRP.line_attr_tbl_type;
274 l_qual_contexts_tbl QP_PREQ_GRP.qual_tbl_type;
275
276 BEGIN
277 SAVEPOINT build_cle_from_cz;
278
279 IF (l_debug = 'Y') THEN
280 OKC_DEBUG.set_indentation(l_api_name);
281 OKC_DEBUG.log('10: Entered build_cle_from_cz', 2);
282 END IF;
283 --FND_MSG_PUB.initialize;
284 okc_api.init_msg_list(p_init_msg_list);
285
286 -- ------------------------------------------------------------
287 -- Call start_activity to create savepoint, check compatibility
288 -- and initialize message list
289 -- ------------------------------------------------------------
290 l_return_status := OKC_API.START_ACTIVITY(
291 p_api_name => l_api_name,
292 p_pkg_name => G_PKG_NAME,
293 p_init_msg_list => p_init_msg_list,
294 l_api_version => l_api_version_number,
295 p_api_version => p_api_version_number,
296 p_api_type => G_API_TYPE,
297 x_return_status => x_return_status);
298
299 -- Check if activity started successfully
300 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR)
301 THEN
302 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
303 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR)
304 THEN
305 RAISE OKC_API.G_EXCEPTION_ERROR;
306 END IF;
307
308 -- -------------------------------------------------------------------
309 -- STEP.1: DEBUG: 100 series
310 -- Check whether the configuration already exists in okc_k_lines_v
311 -- for a given config_header_id and config_revision_number
312 -- -------------------------------------------------------------------
313 l_config_exists := cle_config_check(p_au_line_rec.id,
314 p_config_rec.config_hdr_id,
315 p_config_rec.config_rev_nbr);
316
317 IF l_config_exists = 'Y'
318 THEN
319 IF (l_debug = 'Y') THEN
320 OKC_DEBUG.log('11: Config Exists already in OKC: '||p_au_line_rec.id);
321 END IF;
322 RAISE OKC_API.G_EXCEPTION_ERROR;
323 END IF;
324
325 -- --------------------------------------------------------------------
326 -- STEP.1.1: DEBUG: 100 series
330 IF ( p_au_line_rec.config_hdr_id <> OKC_API.G_MISS_NUM AND
327 -- Delete the earlier configuration revision lines to be replaced
328 -- with new config revision that is under process.
329 -- --------------------------------------------------------------------
331 p_au_line_rec.config_rev_nbr <> OKC_API.G_MISS_NUM )
332 THEN
333 OPEN cur_old_config (p_au_line_rec.config_hdr_id,
334 p_au_line_rec.config_rev_nbr,
335 p_au_line_rec.id,
336 p_au_line_rec.dnz_chr_id);
337 LOOP
338 FETCH cur_old_config INTO l_line_id;
339
340 EXIT WHEN cur_old_config%NOTFOUND;
341
342 l_clev_rec.id := l_line_id;
343
344 IF (l_debug = 'Y') THEN
345 OKC_DEBUG.log('30: Before delete old revision....');
346 END IF;
347
348 OKC_CONTRACT_PUB.delete_contract_line (
349 p_api_version => l_api_version_number,
350 p_init_msg_list => l_init_msg_list,
351 x_return_status => x_return_status,
352 x_msg_count => x_msg_count,
353 x_msg_data => x_msg_data,
354 p_line_id => l_line_id );
355
356 IF (l_debug = 'Y') THEN
357 OKC_DEBUG.log('40: After delete old revision...'||x_return_status);
358 END IF;
359
360 FND_MSG_PUB.count_and_get(p_count => x_msg_count,
361 p_data => x_msg_data);
362
363 -- DEBUG: Print messages from stack
364 IF NVL(x_msg_count,0) > 0
365 THEN
366 FOR i IN 1..x_msg_count
367 LOOP
368 x_msg_data := FND_MSG_PUB.Get(p_msg_index => i,
369 p_encoded => 'F');
370 IF (l_debug = 'Y') THEN
371 OKC_DEBUG.log('41: x_msg_data '||x_msg_data);
372 END IF;
373 END LOOP; -- end of message count loop
374 END IF;
375
376 -- Check Return Status
377 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR
378 THEN
379 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
380 ELSIF x_return_status = OKC_API.G_RET_STS_ERROR
381 THEN
382 RAISE OKC_API.G_EXCEPTION_ERROR;
383 END IF;
384
385 END LOOP; -- old config
386 CLOSE cur_old_config;
387
388 END IF; -- earlier config check.
389
390 -- -------------------------------------------------------------------
391 -- STEP.2: DEBUG: 100 series
392 -- Handle the inserts for the lines that are not existing in OKC.
393 -- -------------------------------------------------------------------
394 IF (l_debug = 'Y') THEN
395 OKC_DEBUG.log('130: Config Hdr Id : '||p_config_rec.config_hdr_id);
396 OKC_DEBUG.log('140: Config Rev Nbr: '||p_config_rec.config_rev_nbr);
397 OKC_DEBUG.log('150: Line Id : '||p_au_line_rec.id);
398 OKC_DEBUG.log('160: Inv Item Id : '||p_au_line_rec.inventory_item_id);
399 END IF;
400
401 -- Initialize clev record for further process.
402 l_clev_rec := l_clev_rec_init;
403
404 OPEN cur_cle_ins ( p_au_line_rec.dnz_chr_id,
405 p_config_rec.config_hdr_id,
406 p_config_rec.config_rev_nbr);
407 LOOP
408 FETCH cur_cle_ins INTO l_config_rec;
409
410 EXIT WHEN cur_cle_ins%NOTFOUND;
411 l_index := l_index + 1;
412
413 IF (l_debug = 'Y') THEN
414 OKC_DEBUG.log('161: l_index = '||l_index);
415 END IF;
416
417 -- ------------------------------------------------------------
418 -- Added for Bug 2393302.
419 -- Getting Pricing_date from Header
420 -- ------------------------------------------------------------
421 OPEN cur_hdr_pr_date ( p_au_line_rec.dnz_chr_id );
422 FETCH cur_hdr_pr_date INTO l_hdr_pricing_date;
423 CLOSE cur_hdr_pr_date;
424
425 -- ------------------------------------------------------------
426 -- Determine the price list to be used for config lines.
427 -- If the user has selected a pricelist at model line level,
428 -- this pricelist will be defaulted to config lines, otherwise
429 -- header level pricelist will be defaulted to config lines.
430 -- ------------------------------------------------------------
431 OPEN cur_pr_list ( p_au_line_rec.id );
432 FETCH cur_pr_list INTO l_price_list_id,l_line_pricing_date;
433 -- Getting l_line_pricing_date as per Bug 2393302
434 CLOSE cur_pr_list;
435
436 IF (l_debug = 'Y') THEN
437 OKC_DEBUG.log('165: Model Line Pricelist :'||l_price_list_id);
438 OKC_DEBUG.log('166: Header Level Pricelist :'||p_au_line_rec.hdr_price_list_id);
439 END IF;
440
441 l_price_list_id := NVL(l_price_list_id,
442 p_au_line_rec.hdr_price_list_id);
443
444 IF (l_debug = 'Y') THEN
448 -- Added for Bug 2393302
445 OKC_DEBUG.log('166: Defaulted Pricelist :'||l_price_list_id);
446 END IF;
447
449 IF (l_debug = 'Y') THEN
450 OKC_DEBUG.log('165.A: Header Pricing Date :'||l_hdr_pricing_date);
451 OKC_DEBUG.log('165.A: Line Pricing Date :'||l_line_pricing_date);
452 END IF;
453
454 l_pricing_date := NVL(l_line_pricing_date,
455 l_hdr_pricing_date);
456
457 IF (l_debug = 'Y') THEN
458 OKC_DEBUG.log('166.A: Defaulted PricingDate :'||l_pricing_date);
459 END IF;
460
461 -- ------------------------------------------------------------
462
463 IF TO_CHAR(l_config_rec.inventory_item_id)=l_config_rec.component_code
464 THEN
465 -- ------------------------------------------------------------
466 -- This is a model item.
467 -- Create top model, top base line and top base item.
468 -- ------------------------------------------------------------
469 IF (l_debug = 'Y') THEN
470 OKC_DEBUG.log('175: Inside Create Model contract lines...'||l_config_rec.inventory_item_id);
471 END IF;
472
473 -- -----------------------------------------------------------------
474 -- Assign available values to create top model line record
475 -- -----------------------------------------------------------------
476 l_clev_rec.config_header_id := p_config_rec.config_hdr_id;
477 l_clev_rec.config_revision_number := p_config_rec.config_rev_nbr;
478 l_clev_rec.id := p_au_line_rec.id;
479 l_clev_rec.dnz_chr_id := p_au_line_rec.dnz_chr_id;
480 l_clev_rec.start_date := p_au_line_rec.start_date;
481 l_clev_rec.end_date := p_au_line_rec.end_date;
482 l_clev_rec.currency_code := p_au_line_rec.currency_code;
483 l_clev_rec.exception_yn := 'N';
484 l_clev_rec.sts_code := 'ENTERED';
485 l_clev_rec.sfwt_flag := 'N';
486 l_clev_rec.lse_id := p_au_line_rec.lse_id;
487 l_clev_rec.config_item_type := 'TOP_MODEL_LINE';
488 l_clev_rec.config_complete_yn := p_config_complete_yn;
489 l_clev_rec.config_valid_yn := p_config_valid_yn;
490 l_clev_rec.config_top_model_line_id := p_au_line_rec.id;
491 l_clev_rec.price_level_ind := 'Y';
492 l_clev_rec.item_to_price_yn := 'Y';
493 l_clev_rec.price_list_id := l_price_list_id;
494 l_clev_rec.pricing_date := l_pricing_date; -- Added for Bug 2393302
495 -- -----------------------------------------------------------------
496
497 -- -----------------------------------------------------------------
498 -- STEP.2.1: DEBUG: 200 series
499 -- CREATE TOP MODEL LINE
500 -- To update the user entered row and make it a top model line.
501 -- Calls okc_contract_pub.update_contract_line
502 -- with the above in-record init values.
503 -- No need to create contract item line, as this is update
504 -- for the Authoring form row. Form will commit the item row.
505 -- -----------------------------------------------------------------
506 IF (l_debug = 'Y') THEN
507 OKC_DEBUG.log('200: Before update contract top model line...');
508 END IF;
509
510 OKC_CONTRACT_PUB.update_contract_line
511 ( p_api_version => l_api_version_number,
512 p_init_msg_list => l_init_msg_list,
513 x_msg_count => x_msg_count,
514 x_msg_data => x_msg_data,
515 x_return_status => x_return_status,
516 p_clev_rec => l_clev_rec,
517 x_clev_rec => x_clev_rec);
518
519 IF (l_debug = 'Y') THEN
520 OKC_DEBUG.log('210: Updated contract Top Model Line..'||x_return_status);
521 END IF;
522
523 IF (l_debug = 'Y') THEN
524 OKC_DEBUG.log('220: Top Model Line Id : '||x_clev_rec.id);
525 END IF;
526
527 FND_MSG_PUB.count_and_get(p_count => x_msg_count,
528 p_data => x_msg_data);
529
530 -- DEBUG: Print messages from stack
531 IF NVL(x_msg_count,0) > 0
532 THEN
533 FOR i IN 1..x_msg_count
534 LOOP
535 x_msg_data := FND_MSG_PUB.Get(p_msg_index => i,
536 p_encoded => 'F');
537 IF (l_debug = 'Y') THEN
538 OKC_DEBUG.log('221: x_msg_data '||x_msg_data);
539 END IF;
540 END LOOP; -- end of message count loop
541 END IF;
542
543 -- Check Return Status
544 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR
545 THEN
546 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
547 ELSIF x_return_status = OKC_API.G_RET_STS_ERROR
548 THEN
552 l_top_model_line_id := x_clev_rec.id;
549 RAISE OKC_API.G_EXCEPTION_ERROR;
550 END IF;
551
553
554 -- -----------------------------------------------------------------
555 -- STEP.2.2: DEBUG: 300 series
556 -- CREATE TOP BASE LINE
557 -- Re-Assign above values to create top base line row.
558 -- This is the top line of the model hierarchy.
559 -- This will contain the same model related information.
560 -- Rows starting from here and down,are Query only on the form.
561 -- The changes compared to top-model-line are:
562 -- - The new line-id is generated by system. g_miss_num now.
563 -- - The column config_item_type will have 'TOP_BASE_LINE'
564 -- - The display sequence are l_index based for now.
565 -- - Config Item Id is populated only for base line and its
566 -- children. This is part of the unique key in CZ schema.
567 -- -----------------------------------------------------------------
568 l_clev_rec.id := OKC_API.G_MISS_NUM; -- init
569 l_clev_rec.cle_id := l_top_model_line_id;
570 l_clev_rec.chr_id := OKC_API.G_MISS_NUM; -- init
571 l_clev_rec.config_item_type := 'TOP_BASE_LINE'; -- flag
572 l_clev_rec.display_sequence := l_index + 1;
573 l_clev_rec.config_item_id := l_config_rec.config_item_id;
574
575 IF (l_debug = 'Y') THEN
576 OKC_DEBUG.log('300: Before create top base model line...');
577 END IF;
578
579 OKC_CONTRACT_PUB.create_contract_line
580 ( p_api_version => l_api_version_number,
581 p_init_msg_list => l_init_msg_list,
582 x_msg_count => x_msg_count,
583 x_msg_data => x_msg_data,
584 x_return_status => x_return_status,
585 p_clev_rec => l_clev_rec,
586 x_clev_rec => x_clev_rec);
587
588 IF (l_debug = 'Y') THEN
589 OKC_DEBUG.log('310: Created Top Base Model Line...'||x_return_status);
590 END IF;
591
592 IF (l_debug = 'Y') THEN
593 OKC_DEBUG.log('320:Top Base LineId :'||x_clev_rec.id);
594 OKC_DEBUG.log('330:Config Item Type:'||x_clev_rec.config_item_type);
595 END IF;
596
597 FND_MSG_PUB.count_and_get(p_count => x_msg_count,
598 p_data => x_msg_data);
599
600 -- DEBUG: Print messages from stack
601 IF NVL(x_msg_count,0) > 0
602 THEN
603 FOR i IN 1..x_msg_count
604 LOOP
605 x_msg_data := FND_MSG_PUB.Get(p_msg_index => i,
606 p_encoded => 'F');
607 IF (l_debug = 'Y') THEN
608 OKC_DEBUG.log('391: x_msg_data '||x_msg_data);
609 END IF;
610 END LOOP; -- end of message count loop
611 END IF;
612
613 -- Check Return Status
614 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR
615 THEN
616 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
617 ELSIF x_return_status = OKC_API.G_RET_STS_ERROR
618 THEN
619 RAISE OKC_API.G_EXCEPTION_ERROR;
620 END IF;
621
622 l_top_base_line_id := x_clev_rec.id;
623
624 -- -------------------------------------------------------
625 -- CZLE: Register top model base details in cz_track_rec.
626 -- -------------------------------------------------------
627 cz_track_tbl(l_config_rec.config_item_id).config_item_id :=
628 l_config_rec.config_item_id;
629 cz_track_tbl(l_config_rec.config_item_id).parent_config_item_id :=
630 l_config_rec.parent_config_item_id;
631 cz_track_tbl(l_config_rec.config_item_id).inventory_item_id :=
632 l_config_rec.inventory_item_id;
633 cz_track_tbl(l_config_rec.config_item_id).id :=
634 l_top_base_line_id;
635 cz_track_tbl(l_config_rec.config_item_id).chr_id :=
636 OKC_API.G_MISS_NUM;
637 cz_track_tbl(l_config_rec.config_item_id).dnz_chr_id :=
638 p_au_line_rec.dnz_chr_id;
639 cz_track_tbl(l_config_rec.config_item_id).cle_id :=
640 l_top_model_line_id;
641 cz_track_tbl(l_config_rec.config_item_id).top_model_line_id :=
642 l_top_model_line_id;
643 cz_track_tbl(l_config_rec.config_item_id).component_code :=
644 l_config_rec.component_code;
645 cz_track_tbl(l_config_rec.config_item_id).config_hdr_id :=
646 l_config_rec.config_hdr_id;
647 cz_track_tbl(l_config_rec.config_item_id).config_rev_nbr:=
648 l_config_rec.config_rev_nbr;
649 cz_track_tbl(l_config_rec.config_item_id).line_index := l_index;
653 p_data => x_msg_data);
650 -- -------------------------------------------------------
651
652 FND_MSG_PUB.count_and_get(p_count => x_msg_count,
654
655 -- DEBUG: Print messages from stack
656 IF NVL(x_msg_count,0) > 0
657 THEN
658 FOR i IN 1..x_msg_count
659 LOOP
660 x_msg_data := FND_MSG_PUB.Get(p_msg_index => i,
661 p_encoded => 'F');
662 IF (l_debug = 'Y') THEN
663 OKC_DEBUG.log('400: x_msg_data '||x_msg_data);
664 END IF;
665 END LOOP; -- end of message count loop
666 END IF;
667
668 -- Check Return Status
669 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR
670 THEN
671 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
672 ELSIF x_return_status = OKC_API.G_RET_STS_ERROR
673 THEN
674 RAISE OKC_API.G_EXCEPTION_ERROR;
675 END IF;
676
677 -- -----------------------------------------------------------------
678 -- STEP.2.3: DEBUG: 500 series
679 -- CREATE TOP BASE LINE ITEM
680 -- Assign available values to create corresponding item line record
681 -- required for the call to okc_contract_pub.create_item_line
682 -- -----------------------------------------------------------------
683 l_cimv_rec.cle_id := l_top_base_line_id;
684 l_cimv_rec.dnz_chr_id := p_au_line_rec.dnz_chr_id;
685 l_cimv_rec.object1_id1 := l_config_rec.inventory_item_id;
686 l_cimv_rec.object1_id2 := p_au_line_rec.item_object1_id2;
687 l_cimv_rec.jtot_object1_code := p_au_line_rec.item_jtot_obj_code;
688 l_cimv_rec.number_of_items := l_config_rec.quantity;
689 l_cimv_rec.uom_code := l_config_rec.uom_code;
690 l_cimv_rec.exception_yn := 'N';
691 l_cimv_rec.priced_item_yn := 'Y';
692 -- -----------------------------------------------------------------
693 IF (l_debug = 'Y') THEN
694 OKC_DEBUG.log('500: Before base contract item create ...');
695 END IF;
696
697 OKC_CONTRACT_ITEM_PUB.create_contract_item
698 ( p_api_version => 1,
699 p_init_msg_list => OKC_API.G_FALSE,
700 x_return_status => x_return_status,
701 x_msg_count => x_msg_count,
702 x_msg_data => x_msg_data,
703 p_cimv_rec => l_cimv_rec,
704 x_cimv_rec => x_cimv_rec);
705
706 IF (l_debug = 'Y') THEN
707 OKC_DEBUG.log('510: Created contract Base model Item ...'||x_return_status);
708 END IF;
709
710 FND_MSG_PUB.count_and_get(p_count => x_msg_count,
711 p_data => x_msg_data);
712
713 -- DEBUG: Print messages from stack
714 IF NVL(x_msg_count,0) > 0
715 THEN
716 FOR i IN 1..x_msg_count
717 LOOP
718 x_msg_data := FND_MSG_PUB.Get(p_msg_index => i,
719 p_encoded => 'F');
720 IF (l_debug = 'Y') THEN
721 OKC_DEBUG.log('511: x_msg_data '||x_msg_data);
722 END IF;
723 END LOOP; -- end of message count loop
724 END IF;
725
726 -- Check Return Status
727 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR
728 THEN
729 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
730 ELSIF x_return_status = OKC_API.G_RET_STS_ERROR
731 THEN
732 RAISE OKC_API.G_EXCEPTION_ERROR;
733 END IF;
734
735 ELSE
736 -- -------------------------------------------------------
737 -- STEP.2.4: DEBUG: 5000 series
738 -- This row is a config options line. Child row of a model.
739 -- Create sublines and subline item.
740 -- -------------------------------------------------------
741 IF (l_debug = 'Y') THEN
742 OKC_DEBUG.log('5000: Creating new config line for item ...'||l_config_rec.inventory_item_id);
743 END IF;
744
745 -- ----------------------------------------------------------------
746 -- STEP.2.4.1 DEBUG: 5010 - 5080
747 -- Check the validity of the item existance in the Linestyle source
748 -- For the linestyle that the user selected, to configure a model,
749 -- the same linestyle source should contain the model option items.
750 -- The linestyle source should contain the configured system items.
751 -- i.e. check for the inventory item is part of the model source.
752 -- ----------------------------------------------------------------
753 IF (l_debug = 'Y') THEN
757 END IF;
754 OKC_DEBUG.log('5010: JTOT Object Code: '||p_au_line_rec.item_jtot_obj_code);
755 OKC_DEBUG.log('5020: Object1 Id1-Item: '||l_config_rec.inventory_item_id);
756 OKC_DEBUG.log('5030: Object1 Id2-Org : '||p_au_line_rec.item_object1_id2);
758
759 -- ---------------------------------------------------------------------------
760 -- Call to fetch the item name, with these values
761 -- ---------------------------------------------------------------------------
762 l_item_name := OKC_UTIL.get_name_from_jtfv
763 ( p_object_code => p_au_line_rec.item_jtot_obj_code,
764 p_id1 => l_config_rec.inventory_item_id,
765 p_id2 => p_au_line_rec.item_object1_id2);
766
767 IF (l_debug = 'Y') THEN
768 OKC_DEBUG.log('5080: Item Name : '||l_item_name);
769 END IF;
770
771 IF l_item_name IS NULL
772 THEN
773 -- Item name cannot be found in the selected linestyle source
774 -- Stop processing further, return control to authoring form.
775 ROLLBACK TO SAVEPOINT build_cle_from_cz;
776
777 OKC_API.SET_MESSAGE( p_app_name => 'OKC',
778 p_msg_name => 'OKC_CONFIG_ERROR',
779 p_token1 => 'PROCEDURE',
780 p_token1_value => 'validating linestyle based source item.');
781
782 -- notify caller of an error
783 x_return_status := OKC_API.G_RET_STS_ERROR;
784
785 RAISE OKC_API.G_EXCEPTION_ERROR;
786 END IF;
787 -- ----------------------------------------------------------------
788
789 -- -----------------------------------------------------------------
790 -- STEP.2.4.2 DEBUG: 5100 series
791 -- CREATE NEW CONFIG LINE
792 -- This is the child line of the model hierarchy.
793 -- This will contain the option class and options information.
794 -- All the model and option rows are Query only on the form.
795 -- -----------------------------------------------------------------
796 -- Initialize new contract line input record.
797 l_clev_rec := l_clev_rec_init;
798 IF (l_debug = 'Y') THEN
799 OKC_DEBUG.log('5100: New config contract line inited...');
800 END IF;
801
802 -- -----------------------------------------------------------------
803 -- Assign available values to create config line record
804 -- -----------------------------------------------------------------
805 l_clev_rec.config_header_id := p_config_rec.config_hdr_id;
806 l_clev_rec.config_revision_number := p_config_rec.config_rev_nbr;
807 l_clev_rec.config_item_id := l_config_rec.config_item_id;
808 l_clev_rec.cle_id :=
809 cz_track_tbl(l_config_rec.parent_config_item_id).id;
810 l_clev_rec.dnz_chr_id := p_au_line_rec.dnz_chr_id;
811 l_clev_rec.start_date := p_au_line_rec.start_date;
812 l_clev_rec.end_date := p_au_line_rec.end_date;
813 l_clev_rec.currency_code := p_au_line_rec.currency_code;
814 l_clev_rec.exception_yn := 'N';
815 l_clev_rec.sts_code := 'ENTERED';
816 l_clev_rec.sfwt_flag := 'N';
817 l_clev_rec.lse_id := p_au_line_rec.lse_id;
818 l_clev_rec.config_item_type := 'CONFIG'; -- flag
819 l_clev_rec.config_complete_yn := p_config_complete_yn;
820 l_clev_rec.config_valid_yn := p_config_valid_yn;
821 l_clev_rec.config_top_model_line_id := p_au_line_rec.id;
822 l_clev_rec.display_sequence := l_index + 1;
823 l_clev_rec.price_level_ind := 'Y';
824 l_clev_rec.item_to_price_yn := 'Y';
825 l_clev_rec.price_list_id := l_price_list_id;
826 l_clev_rec.pricing_date := l_pricing_date; -- Added for Bug 2393302
827 -- -----------------------------------------------------------------
828
829 IF (l_debug = 'Y') THEN
830 OKC_DEBUG.log('5101: Before create config line...');
831 OKC_DEBUG.log('5102: Parent for this line :'||l_clev_rec.cle_id);
832 END IF;
833
834 OKC_CONTRACT_PUB.create_contract_line
835 ( p_api_version => l_api_version_number,
836 p_init_msg_list => l_init_msg_list,
837 x_msg_count => x_msg_count,
838 x_msg_data => x_msg_data,
839 x_return_status => x_return_status,
840 p_clev_rec => l_clev_rec,
841 x_clev_rec => x_clev_rec);
842
843 IF (l_debug = 'Y') THEN
844 OKC_DEBUG.log('5110: Created new Config Line...'|| x_return_status);
845 END IF;
846
847 IF (l_debug = 'Y') THEN
848 OKC_DEBUG.log('5200: New Cfg line :'||x_clev_rec.cle_id);
852 FND_MSG_PUB.count_and_get(p_count => x_msg_count,
849 OKC_DEBUG.log('5210: Cfg Item Type:'||x_clev_rec.config_item_type);
850 END IF;
851
853 p_data => x_msg_data);
854
855 -- DEBUG: Print messages from stack
856 IF NVL(x_msg_count,0) > 0
857 THEN
858 FOR i IN 1..x_msg_count
859 LOOP
860 x_msg_data := FND_MSG_PUB.Get(p_msg_index => i,
861 p_encoded => 'F');
862 IF (l_debug = 'Y') THEN
863 OKC_DEBUG.log('5290: x_msg_data '||x_msg_data);
864 END IF;
865 END LOOP; -- end of message count loop
866 END IF;
867
868 -- Check Return Status
869 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR
870 THEN
871 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
872 ELSIF x_return_status = OKC_API.G_RET_STS_ERROR
873 THEN
874 RAISE OKC_API.G_EXCEPTION_ERROR;
875 END IF;
876
877 l_config_line_id := x_clev_rec.id;
878
879
880 -- ---------------------------------------------------------------
881 -- CZLE: Refresh cz_track_rec details....
882 -- ---------------------------------------------------------------
883 cz_track_tbl(l_config_rec.config_item_id).config_item_id :=
884 l_config_rec.config_item_id;
885 cz_track_tbl(l_config_rec.config_item_id).parent_config_item_id :=
886 l_config_rec.parent_config_item_id;
887 cz_track_tbl(l_config_rec.config_item_id).inventory_item_id :=
888 l_config_rec.inventory_item_id;
889 cz_track_tbl(l_config_rec.config_item_id).id :=
890 l_config_line_id;
891 cz_track_tbl(l_config_rec.config_item_id).chr_id :=
892 OKC_API.G_MISS_NUM;
893 cz_track_tbl(l_config_rec.config_item_id).cle_id :=
894 cz_track_tbl(l_config_rec.parent_config_item_id).id;
895 cz_track_tbl(l_config_rec.config_item_id).top_model_line_id :=
896 l_top_model_line_id;
897 cz_track_tbl(l_config_rec.config_item_id).component_code :=
898 l_config_rec.component_code;
899 cz_track_tbl(l_config_rec.config_item_id).config_hdr_id :=
900 l_config_rec.config_hdr_id;
901 cz_track_tbl(l_config_rec.config_item_id).config_rev_nbr:=
902 l_config_rec.config_rev_nbr;
903 cz_track_tbl(l_config_rec.config_item_id).line_index := l_index;
904 -- ---------------------------------------------------------------
905
906 -- -----------------------------------------------------------------
907 -- STEP.2.4.3 DEBUG: 5300 series
908 -- CREATE CONFIG LINE ITEM
909 -- Assign available values to create corresponding item line record
910 -- required for the call to okc_contract_pub.create_item_line
911 -- -----------------------------------------------------------------
912 l_cimv_rec.cle_id := l_config_line_id;
913 l_cimv_rec.dnz_chr_id := p_au_line_rec.dnz_chr_id;
914 l_cimv_rec.object1_id1 := l_config_rec.inventory_item_id;
915 l_cimv_rec.object1_id2 := p_au_line_rec.item_object1_id2;
916 l_cimv_rec.jtot_object1_code := p_au_line_rec.item_jtot_obj_code;
917 l_cimv_rec.number_of_items := l_config_rec.quantity;
918 l_cimv_rec.uom_code := l_config_rec.uom_code;
919 l_cimv_rec.exception_yn := 'N';
920 l_cimv_rec.priced_item_yn := 'Y';
921 -- -----------------------------------------------------------------
922
923 IF (l_debug = 'Y') THEN
924 OKC_DEBUG.log('5300: Before config contract item create ...');
925 END IF;
926
927 OKC_CONTRACT_ITEM_PUB.create_contract_item
928 ( p_api_version => 1,
929 p_init_msg_list => OKC_API.G_FALSE,
930 x_return_status => x_return_status,
931 x_msg_count => x_msg_count,
932 x_msg_data => x_msg_data,
933 p_cimv_rec => l_cimv_rec,
934 x_cimv_rec => x_cimv_rec);
935
936 IF (l_debug = 'Y') THEN
937 OKC_DEBUG.log('5310: Created corresponding Contract Item...'||x_return_status);
938 END IF;
939
940 FND_MSG_PUB.count_and_get(p_count => x_msg_count,
941 p_data => x_msg_data);
942
943 -- DEBUG: Print messages from stack
944 IF NVL(x_msg_count,0) > 0
945 THEN
946 FOR i IN 1..x_msg_count
947 LOOP
948 x_msg_data := FND_MSG_PUB.Get(p_msg_index => i,
949 p_encoded => 'F');
950 IF (l_debug = 'Y') THEN
951 OKC_DEBUG.log('5311: x_msg_data '||x_msg_data);
952 END IF;
953 END LOOP; -- end of message count loop
957 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR
954 END IF;
955
956 -- Check Return Status
958 THEN
959 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
960 ELSIF x_return_status = OKC_API.G_RET_STS_ERROR
961 THEN
962 RAISE OKC_API.G_EXCEPTION_ERROR;
963 END IF;
964
965 END IF;
966 END LOOP;
967 CLOSE cur_cle_ins;
968
969 -- -------------------------------------------------------------------
970 -- STEP.5: DEBUG: 5600 series
971 -- Handle the pricing call and update the line.
972 -- -------------------------------------------------------------------
973 IF (l_debug = 'Y') THEN
974 OKC_DEBUG.log('5600: Entering Pricing Section ...', 2);
975 END IF;
976
977 -- ----------------------------------------------------------
978 -- STEP.5.1:
979 -- Prepare the contract lines for pricing
980 -- ----------------------------------------------------------
981 OPEN cur_price_cle ( p_au_line_rec.dnz_chr_id,
982 p_config_rec.config_hdr_id,
983 p_config_rec.config_rev_nbr);
984 LOOP
985 FETCH cur_price_cle
986 INTO l_cle_price_tbl(i).id,
987 l_cle_price_tbl(i).pi_bpi,
988 l_cle_price_tbl(i).qty,
989 l_cle_price_tbl(i).uom_code,
990 l_cle_price_tbl(i).currency,
991 l_cle_price_tbl(i).object_code,
992 l_cle_price_tbl(i).id1,
993 l_cle_price_tbl(i).id2,
994 l_cle_price_tbl(i).pricelist_id,
995 l_chr_id,
996 l_cle_price_tbl(i).pricing_date; -- Added for Bug 2393302
997
998 IF (l_debug = 'Y') THEN
999 OKC_DEBUG.log('<------------ INPUT LINE - '||i||' ------->');
1000 OKC_DEBUG.log('5611: Id : '||l_cle_price_tbl(i).id);
1001 OKC_DEBUG.log('5612: Inv Item: '||l_cle_price_tbl(i).id1);
1002 OKC_DEBUG.log('5613: Quantity: '||l_cle_price_tbl(i).qty);
1003 OKC_DEBUG.log('5614: UOM Code: '||l_cle_price_tbl(i).uom_code);
1004 OKC_DEBUG.log('5615: Currency: '||l_cle_price_tbl(i).currency);
1005 OKC_DEBUG.log('5616: Org Id : '||l_cle_price_tbl(i).id2);
1006 OKC_DEBUG.log('5617: Jtot Obj: '||l_cle_price_tbl(i).object_code);
1007 OKC_DEBUG.log('5618: Pr List : '||l_cle_price_tbl(i).pricelist_id);
1008 OKC_DEBUG.log('5619: Chr Id : '||l_chr_id);
1009 OKC_DEBUG.log('5620: Pricing Date : '||l_cle_price_tbl(i).pricing_date); -- Added for Bug 2393302
1010 END IF;
1011
1012 EXIT WHEN cur_price_cle%NOTFOUND;
1013 i := i + 1;
1014 END LOOP;
1015 CLOSE cur_price_cle;
1016
1017 -- delete last blank row
1018 l_cle_price_tbl.DELETE(i);
1019
1020 -- Fetch Top Model Id for pricing
1021 OPEN cur_get_model_id ( p_au_line_rec.dnz_chr_id,
1022 p_config_rec.config_hdr_id,
1023 p_config_rec.config_rev_nbr);
1024 FETCH cur_get_model_id INTO l_top_model_id,
1025 l_top_model_line_id;
1026 CLOSE cur_get_model_id;
1027
1028 IF (l_debug = 'Y') THEN
1029 OKC_DEBUG.log('5630: Top Model Id for pricing : '||l_top_model_id);
1030 OKC_DEBUG.log('5635: Total Input Price rows : '||l_cle_price_tbl.count);
1031 END IF;
1032
1033 -- ------------------------------------------------------------
1034 -- STEP.5.2:
1035 -- Pricing Control Record init values:
1036 -- ------------------------------------------------------------
1037 l_control_rec.p_top_model_id := l_top_model_id;
1038 l_control_rec.p_config_yn :='S';
1039 l_control_rec.qp_control_rec.pricing_event :='BATCH';
1040
1041 IF (l_debug = 'Y') THEN
1042 OKC_DEBUG.log('5640: Pr Ctl.Top-Model :'||l_control_rec.p_top_model_id);
1043 OKC_DEBUG.log('5641: Pr Ctl.Config YN :'||l_control_rec.p_config_yn);
1044 OKC_DEBUG.log('5642: Pr Ctl.Pr Event :'||'BATCH');
1045 END IF;
1046
1047 -- --------------------------------------------------------------
1048 -- STEP.5.3:
1049 -- OKC Specific Pricing call to calculate list or sell price.
1050 -- --------------------------------------------------------------
1051 IF (l_debug = 'Y') THEN
1052 OKC_DEBUG.log('5650: Before Calculate Price call...');
1053 END IF;
1054
1055 OKC_PRICE_PVT.CALCULATE_PRICE(
1056 p_api_version => 1,
1057 p_init_msg_list => OKC_API.G_FALSE,
1058 p_chr_id => l_chr_id,
1059 p_Control_Rec => l_control_rec,
1060 px_req_line_tbl => l_req_line_tbl,
1061 px_Req_qual_tbl => l_req_line_qual_tbl,
1062 px_Req_line_attr_tbl => l_req_line_attr_tbl,
1063 px_Req_LINE_DETAIL_tbl => l_req_line_detail_tbl,
1064 px_Req_LINE_DETAIL_qual_tbl => l_req_line_detail_qual_tbl,
1065 px_Req_LINE_DETAIL_attr_tbl => l_req_line_detail_attr_tbl,
1069 x_msg_count => x_msg_count,
1066 px_Req_RELATED_LINE_TBL => l_req_related_line_tbl,
1067 px_CLE_PRICE_TBL => l_cle_price_tbl,
1068 x_return_status => x_return_status,
1070 x_msg_data => x_msg_data);
1071
1072 IF (l_debug = 'Y') THEN
1073 OKC_DEBUG.log('5655: Exiting Calculate Price... '||x_return_status);
1074 END IF;
1075
1076 -- DEBUG: Print messages from stack
1077 IF NVL(x_msg_count,0) > 0
1078 THEN
1079 FOR i IN 1..x_msg_count
1080 LOOP
1081 x_msg_data := FND_MSG_PUB.Get(p_msg_index => i,
1082 p_encoded => 'F');
1083 IF (l_debug = 'Y') THEN
1084 OKC_DEBUG.log('5660: x_msg_data '||x_msg_data);
1085 END IF;
1086 END LOOP; -- end of message count loop
1087 END IF;
1088
1089 -- Check Return Status
1090 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR
1091 THEN
1092 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1093 ELSIF x_return_status = OKC_API.G_RET_STS_ERROR
1094 THEN
1095 RAISE OKC_API.G_EXCEPTION_ERROR;
1096 END IF; -- end of ret status validation
1097
1098 l_tot_negotiated := 0;
1099 l_tot_list_price := 0;
1100 IF l_cle_price_tbl.count > 0
1101 THEN
1102 FOR i in l_cle_price_tbl.FIRST..l_cle_price_tbl.LAST
1103 LOOP
1104 -- Dump all priced config lines in debug mode
1105 IF (l_debug = 'Y') THEN
1106 OKC_DEBUG.log('5671: <--- OUTPUT LINE - '||(to_number(i))||' ------->');
1107 OKC_DEBUG.log('5672: List Price : '||l_CLE_PRICE_TBL(i).list_price);
1108 OKC_DEBUG.log('5673: Nego Price : '||l_CLE_PRICE_TBL(i).negotiated_amt);
1109 OKC_DEBUG.log('5674: Pr List Id : '||l_CLE_PRICE_TBL(i).pricelist_id);
1110 OKC_DEBUG.log('5675: Line Id : '||l_CLE_PRICE_TBL(i).id);
1111 OKC_DEBUG.log('5676: Pi Bpi : '||l_CLE_PRICE_TBL(i).pi_bpi);
1112 OKC_DEBUG.log('5677: Quantity : '||l_CLE_PRICE_TBL(i).qty);
1113 OKC_DEBUG.log('5678: Uom Code : '||l_CLE_PRICE_TBL(i).uom_code);
1114 OKC_DEBUG.log('5679: Currency : '||l_CLE_PRICE_TBL(i).currency);
1115 OKC_DEBUG.log('5680: Obj Code : '||l_CLE_PRICE_TBL(i).object_code);
1116 OKC_DEBUG.log('5681: Obj Id1 : '||l_CLE_PRICE_TBL(i).id1);
1117 OKC_DEBUG.log('5682: Obj Id2 : '||l_CLE_PRICE_TBL(i).id2);
1118 OKC_DEBUG.log('5690: <------------------------------------------------>');
1119 END IF;
1120
1121 -- --------------------------------------------------------------
1122 -- Calculate the rollup price to be shown for top model line.
1123 -- --------------------------------------------------------------
1124 l_tot_negotiated := l_tot_negotiated + l_cle_price_tbl(i).negotiated_amt;
1125 l_tot_list_price := l_tot_list_price + l_cle_price_tbl(i).list_price;
1126
1127 -- Initialize clev record for further process.
1128 l_clev_rec := l_clev_rec_init;
1129
1130 OPEN cur_line_info (l_CLE_PRICE_TBL(i).id);
1131 FETCH cur_line_info INTO l_cur_cle_id,
1132 l_cur_chr_id,
1133 l_cur_line_nbr, --> Used to sync-up disp seq.
1134 l_display_sequence; --> Not used in this loop.
1135 CLOSE cur_line_info;
1136
1137 l_clev_rec.id := l_CLE_PRICE_TBL(i).id;
1138 l_clev_rec.chr_id := l_cur_chr_id;
1139 l_clev_rec.cle_id := l_cur_cle_id;
1140 l_clev_rec.display_sequence := l_cur_line_nbr; -- sync up with line
1141 l_clev_rec.line_list_price := l_CLE_PRICE_TBL(i).list_price;
1142 l_clev_rec.price_unit := l_CLE_PRICE_TBL(i).unit_price;
1143 l_clev_rec.price_negotiated := l_CLE_PRICE_TBL(i).negotiated_amt;
1144 l_clev_rec.price_list_id := l_CLE_PRICE_TBL(i).pricelist_id;
1145 l_clev_rec.pricing_date := l_CLE_PRICE_TBL(i).pricing_date;
1146 l_clev_rec.price_list_line_id := l_CLE_PRICE_TBL(i).list_line_id;
1147
1148 IF (l_debug = 'Y') THEN
1149 OKC_DEBUG.log('5700: Before Update Pricing Info ....');
1150 END IF;
1151
1152 IF (l_debug = 'Y') THEN
1153 OKC_DEBUG.log('5710: Priced Line Id : '||l_clev_rec.id);
1154 OKC_DEBUG.log('5711: Cle Id : '||l_clev_rec.cle_id);
1155 OKC_DEBUG.log('5712: Chr Id : '||l_clev_rec.chr_id);
1156 OKC_DEBUG.log('5720: List Price : '||l_clev_rec.line_list_price);
1157 OKC_DEBUG.log('5730: Unit Price : '||l_clev_rec.price_unit);
1158 OKC_DEBUG.log('5740: Negotiated Price: '||l_clev_rec.price_negotiated);
1159 OKC_DEBUG.log('5750: Price List Id : '||l_clev_rec.price_list_id);
1160 OKC_DEBUG.log('5760: Pricing Date : '||l_clev_rec.pricing_date);
1161 OKC_DEBUG.log('5780: Pr List Line Id : '||l_clev_rec.price_list_line_id);
1162 OKC_DEBUG.log('5785: Config Item Type: '||l_clev_rec.config_item_type);
1163 END IF;
1164
1165 OKC_CONTRACT_PUB.update_contract_line
1166 ( p_api_version => l_api_version_number,
1167 p_init_msg_list => l_init_msg_list,
1168 x_msg_count => x_msg_count,
1169 x_msg_data => x_msg_data,
1173
1170 x_return_status => x_return_status,
1171 p_clev_rec => l_clev_rec,
1172 x_clev_rec => x_clev_rec);
1174 IF (l_debug = 'Y') THEN
1175 OKC_DEBUG.log('5800: Updated Pricing Info...'||x_return_status);
1176 END IF;
1177
1178 IF (l_debug = 'Y') THEN
1179 OKC_DEBUG.log('5810: Priced Line Id : '||x_clev_rec.id);
1180 OKC_DEBUG.log('5811: Cle Id : '||x_clev_rec.cle_id);
1181 OKC_DEBUG.log('5812: Chr Id : '||x_clev_rec.chr_id);
1182 OKC_DEBUG.log('5820: List Price : '||x_clev_rec.line_list_price);
1183 OKC_DEBUG.log('5830: Unit Price : '||x_clev_rec.price_unit);
1184 OKC_DEBUG.log('5840: Negotiated Price: '||x_clev_rec.price_negotiated);
1185 OKC_DEBUG.log('5850: Price List Id : '||x_clev_rec.price_list_id);
1186 OKC_DEBUG.log('5860: Pricing Date : '||x_clev_rec.pricing_date);
1187 OKC_DEBUG.log('5880: Pr List Line Id : '||x_clev_rec.price_list_line_id);
1188 OKC_DEBUG.log('5885: Config Item Type: '||x_clev_rec.config_item_type);
1189 END IF;
1190
1191 FND_MSG_PUB.count_and_get(p_count => x_msg_count,
1192 p_data => x_msg_data);
1193
1194 -- DEBUG: Print messages from stack
1195 IF NVL(x_msg_count,0) > 0
1196 THEN
1197 FOR i IN 1..x_msg_count
1198 LOOP
1199 x_msg_data := FND_MSG_PUB.Get(p_msg_index => i,
1200 p_encoded => 'F');
1201 IF (l_debug = 'Y') THEN
1202 OKC_DEBUG.log('5890: x_msg_data '||x_msg_data);
1203 END IF;
1204 END LOOP; -- end of message count loop
1205 END IF;
1206
1207 -- Check Return Status
1208 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR
1209 THEN
1210 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1211 ELSIF x_return_status = OKC_API.G_RET_STS_ERROR
1212 THEN
1213 RAISE OKC_API.G_EXCEPTION_ERROR;
1214 END IF; -- return status
1215
1216 END LOOP; -- l_cle_price_table rows
1217 END IF;
1218
1219 -- --------------------------------------------------------------
1220 -- Update the rollup price to be shown for top model line.
1221 -- --------------------------------------------------------------
1222 -- Initialize clev record for further process.
1223 l_clev_rec := l_clev_rec_init;
1224
1225 IF (l_debug = 'Y') THEN
1226 OKC_DEBUG.log('5898: Total List Price : '||l_tot_list_price);
1227 OKC_DEBUG.log('5899: Total Price : '||l_tot_negotiated);
1228 OKC_DEBUG.log('5900: Top Model Line : '||l_top_model_line_id);
1229 END IF;
1230
1231 OPEN cur_line_info (l_top_model_line_id);
1232 FETCH cur_line_info INTO l_cur_cle_id,
1233 l_cur_chr_id,
1234 l_cur_line_nbr, --> not used in this loop.
1235 l_display_sequence; --> gets user entered value.
1236 CLOSE cur_line_info;
1237
1238 l_clev_rec.id := l_top_model_line_id;
1239 l_clev_rec.chr_id := l_cur_chr_id;
1240 l_clev_rec.cle_id := l_cur_cle_id;
1241 l_clev_rec.display_sequence := l_display_sequence; -- User value
1242 l_clev_rec.price_unit := NULL; -- Top Model Line
1243 l_clev_rec.line_list_price := l_tot_list_price; -- Rollup Value
1244 l_clev_rec.price_negotiated := l_tot_negotiated; -- Rollup Value
1245 l_clev_rec.price_list_id := l_CLE_PRICE_TBL(1).pricelist_id;
1246 l_clev_rec.pricing_date := l_CLE_PRICE_TBL(1).pricing_date;
1247 l_clev_rec.price_list_line_id := l_CLE_PRICE_TBL(1).list_line_id;
1248 l_clev_rec.config_top_model_line_id := l_top_model_line_id;
1249 l_clev_rec.config_complete_yn := p_config_complete_yn;
1250 l_clev_rec.config_valid_yn := p_config_valid_yn;
1251 l_clev_rec.item_to_price_yn := 'Y';
1252
1253 IF (l_debug = 'Y') THEN
1254 OKC_DEBUG.log('5910: Before Update Top Model Pricing Info ....');
1255 END IF;
1256
1257 IF (l_debug = 'Y') THEN
1258 OKC_DEBUG.log('5911: Priced Line Id : '||l_clev_rec.id);
1259 OKC_DEBUG.log('5912: Chr Id : '||l_clev_rec.chr_id);
1260 OKC_DEBUG.log('5913: Cle Id : '||l_clev_rec.cle_id);
1261 OKC_DEBUG.log('5914: List Price : '||l_clev_rec.line_list_price);
1262 OKC_DEBUG.log('5915: Unit Price : '||l_clev_rec.price_unit);
1263 OKC_DEBUG.log('5916: Negotiated Price: '||l_clev_rec.price_negotiated);
1264 OKC_DEBUG.log('5917: Price List Id : '||l_clev_rec.price_list_id);
1265 OKC_DEBUG.log('5918: Pricing Date : '||l_clev_rec.pricing_date);
1266 OKC_DEBUG.log('5919: Pr List Line Id : '||l_clev_rec.price_list_line_id);
1267 END IF;
1268
1269 OKC_CONTRACT_PUB.update_contract_line
1270 ( p_api_version => l_api_version_number,
1271 p_init_msg_list => l_init_msg_list,
1272 x_msg_count => x_msg_count,
1273 x_msg_data => x_msg_data,
1274 x_return_status => x_return_status,
1275 p_clev_rec => l_clev_rec,
1276 x_clev_rec => x_clev_rec);
1277
1278 IF (l_debug = 'Y') THEN
1282 IF (l_debug = 'Y') THEN
1279 OKC_DEBUG.log('5920: Updated Top Model Pricing Info...'||x_return_status);
1280 END IF;
1281
1283 OKC_DEBUG.log('5921: Priced Line Id : '||x_clev_rec.id);
1284 OKC_DEBUG.log('5922: Display Sequence: '||x_clev_rec.display_sequence);
1285 OKC_DEBUG.log('5923: Chr Id : '||l_clev_rec.chr_id);
1286 OKC_DEBUG.log('5924: List Price : '||x_clev_rec.line_list_price);
1287 OKC_DEBUG.log('5925: Unit Price : '||x_clev_rec.price_unit);
1288 OKC_DEBUG.log('5926: Negotiated Price: '||x_clev_rec.price_negotiated);
1289 OKC_DEBUG.log('5927: Price List Id : '||x_clev_rec.price_list_id);
1290 OKC_DEBUG.log('5928: Pricing Date : '||x_clev_rec.pricing_date);
1291 OKC_DEBUG.log('5929: Pr List Line Id : '||x_clev_rec.price_list_line_id);
1292 OKC_DEBUG.log('5930: Cle Id : '||x_clev_rec.cle_id);
1293 OKC_DEBUG.log('5931: Config Item Type: '||x_clev_rec.config_item_type);
1294 END IF;
1295
1296 FND_MSG_PUB.count_and_get(p_count => x_msg_count,
1297 p_data => x_msg_data);
1298
1299 -- DEBUG: Print messages from stack
1300 IF NVL(x_msg_count,0) > 0
1301 THEN
1302 FOR i IN 1..x_msg_count
1303 LOOP
1304 x_msg_data := FND_MSG_PUB.Get(p_msg_index => i,
1305 p_encoded => 'F');
1306 IF (l_debug = 'Y') THEN
1307 OKC_DEBUG.log('5990: x_msg_data '||x_msg_data);
1308 END IF;
1309 END LOOP; -- end of message count loop
1310 END IF;
1311
1312 -- Check Return Status
1313 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR
1314 THEN
1315 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1316 ELSIF x_return_status = OKC_API.G_RET_STS_ERROR
1317 THEN
1318 RAISE OKC_API.G_EXCEPTION_ERROR;
1319 END IF; -- return status
1320
1321 -- ----------------------------------------------------------
1322 -- Populate the Configured model final rollup totals
1323 -- to be displayed in the Net-Price column on the Authoring
1324 -- form header level information. This value will get added
1325 -- to the value existing before the configurator call.
1326 -- This is done by the update-price-line for regular lines.
1327 -- ----------------------------------------------------------
1328 x_cfg_list_price := l_tot_list_price;
1329 x_cfg_net_price := l_tot_negotiated;
1330
1331 IF (l_debug = 'Y') THEN
1332 OKC_DEBUG.log('5990: Exiting Pricing Section ...', 2);
1333 END IF;
1334 -- -------------------------------------------------------------------
1335
1336 -- end activity
1337 OKC_API.END_ACTIVITY( x_msg_count => x_msg_count,
1338 x_msg_data => x_msg_data);
1339
1340 IF (l_debug = 'Y') THEN
1341 OKC_DEBUG.log('5999: Exiting build_cle_from_cz...', 2);
1342 OKC_DEBUG.Reset_Indentation;
1343 END IF;
1344
1345 EXCEPTION
1346 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1347 IF (l_debug = 'Y') THEN
1348 OKC_DEBUG.log('5999:Exception Error in Build Cle...', 2);
1349 OKC_DEBUG.Reset_Indentation;
1350 END IF;
1351
1352 IF cur_cle_ins%ISOPEN
1353 THEN
1354 CLOSE cur_cle_ins;
1355 END IF;
1356
1357 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1358 p_api_name => l_api_name,
1359 p_pkg_name => G_PKG_NAME,
1360 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
1361 x_msg_count => x_msg_count,
1362 x_msg_data => x_msg_data,
1363 p_api_type => G_API_TYPE);
1364
1365 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1366 IF (l_debug = 'Y') THEN
1367 OKC_DEBUG.log('5999:Unexpected Error in Build Cle...', 2);
1368 OKC_DEBUG.Reset_Indentation;
1369 END IF;
1370
1371 IF cur_cle_ins%ISOPEN
1372 THEN
1373 CLOSE cur_cle_ins;
1374 END IF;
1375
1376 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1377 p_api_name => l_api_name,
1378 p_pkg_name => G_PKG_NAME,
1379 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
1380 x_msg_count => x_msg_count,
1381 x_msg_data => x_msg_data,
1382 p_api_type => G_API_TYPE);
1383 WHEN OTHERS THEN
1384 IF (l_debug = 'Y') THEN
1385 OKC_DEBUG.log('5999:Other Exception in Build Cle...', 2);
1386 OKC_DEBUG.Reset_Indentation;
1387 END IF;
1388
1389 IF cur_cle_ins%ISOPEN
1390 THEN
1391 CLOSE cur_cle_ins;
1392 END IF;
1393
1394 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1395 p_api_name => l_api_name,
1396 p_pkg_name => G_PKG_NAME,
1397 p_exc_name => 'OTHERS',
1398 x_msg_count => x_msg_count,
1399 x_msg_data => x_msg_data,
1400 p_api_type => G_API_TYPE);
1401 END build_cle_from_cz;
1402
1406 -- to check whether the contract configuration
1403 -- ---------------------------------------------------------------------------
1404 -- PROCEDURE : config_qa_check
1405 -- PURPOSE : To call from QA check on a contract.
1407 -- is compelte and also valid.
1408 -- INPUT : Contract Header Id.
1409 -- OUTPUT : Contract Valid Y/N flag.
1410 -- DEBUG : 6000 Series
1411 -- ---------------------------------------------------------------------------
1412 PROCEDURE config_qa_check ( p_chr_id IN NUMBER,
1413 x_return_status OUT NOCOPY VARCHAR2 )
1414 IS
1415 l_line_id NUMBER;
1416 l_count NUMBER;
1417 l_msg_token VARCHAR2(300);
1418 l_line_index VARCHAR2(200);
1419 l_return_status VARCHAR2(1);
1420
1421 l_app_id OKC_K_HEADERS_B.application_id%TYPE;
1422 l_buy_or_sell OKC_K_HEADERS_B.buy_or_sell%TYPE;
1423 l_valid OKC_K_LINES_B.config_valid_yn%TYPE;
1424 l_complete OKC_K_LINES_B.config_complete_yn%TYPE;
1425
1426 -- -----------------------------------------------------
1427 -- Get application Ids to activate config qa check.
1428 -- -----------------------------------------------------
1429 CURSOR get_k_app_id
1430 IS
1431 SELECT application_id,
1432 buy_or_sell
1433 FROM okc_k_headers_b
1434 WHERE id = p_chr_id;
1435
1436 -- -----------------------------------------------------
1437 -- Get configured models count from the contract.
1438 -- dnz_chr_id is used, as the config can be in subline.
1439 -- The null values for the flags are interpreted as Y
1440 -- As for the regular contract lines have NULL value
1441 -- for both config_valid_yn and config_complete_yn.
1442 -- -----------------------------------------------------
1443 CURSOR get_cfg_count
1444 IS
1445 SELECT count(clev.id)
1446 FROM okc_k_lines_b clev
1447 WHERE clev.dnz_chr_id = p_chr_id
1448 AND clev.config_item_type = 'TOP_MODEL_LINE'
1449 AND ( NVL(clev.config_valid_yn,'Y') = 'N'
1450 OR NVL(clev.config_complete_yn,'Y') = 'N');
1451
1452 -- -----------------------------------------------------
1453 -- Get configured top model lines from the contract.
1454 -- dnz_chr_id is used, as the config can be in subline.
1455 -- The null values for the flags are interpreted as Y
1456 -- As for the regular contract lines have NULL value
1457 -- -----------------------------------------------------
1458 CURSOR get_cfg_line_info
1459 IS
1460 SELECT clev.id,
1461 clev.config_valid_yn,
1462 clev.config_complete_yn
1463 FROM okc_k_lines_b clev
1464 WHERE clev.dnz_chr_id = p_chr_id
1465 AND clev.config_item_type = 'TOP_MODEL_LINE'
1466 AND ( NVL(clev.config_valid_yn,'Y') = 'N'
1467 OR NVL(clev.config_complete_yn,'Y') = 'N');
1468
1469 BEGIN
1470 -- ----------------------------------------------------
1471 -- Call from QA check, to flag the contract as invalid,
1472 -- with error status, for the following conditions.
1473 -- config_compelte_yn = N (or) config_valid_yn = N
1474 -- ----------------------------------------------------
1475
1476 IF (l_debug = 'Y') THEN
1477 OKC_DEBUG.set_indentation('Config_qa_check');
1478 OKC_DEBUG.log('6000: Entering configuration_qa_check...', 2);
1479 END IF;
1480
1481 -- initialize return status.
1482 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1483
1484 -- ----------------------------------------------------
1485 -- Config Validation is to be done only for OKC and OKO.
1486 -- ----------------------------------------------------
1487 IF (l_debug = 'Y') THEN
1488 OKC_DEBUG.log('6010: Getting Application Id: ');
1489 END IF;
1490
1491 OPEN get_k_app_id;
1492 FETCH get_k_app_id INTO l_app_id, l_buy_or_sell;
1493 CLOSE get_k_app_id;
1494
1495 IF (l_debug = 'Y') THEN
1496 OKC_DEBUG.log('6020: Application Id : '||TO_CHAR(l_app_id));
1497 END IF;
1498 IF (l_app_id NOT IN (510, 871) OR
1499 l_buy_or_sell = 'B')
1500 THEN
1501 -- No need to set the return status here
1502 RAISE G_EXCEPTION_HALT_VALIDATION;
1503 END IF;
1504
1505 -- ------------------------------------------------------
1506 -- Get the invalid or incomplete configured models count.
1507 -- ------------------------------------------------------
1508 OPEN get_cfg_count;
1509 FETCH get_cfg_count INTO l_count;
1510 CLOSE get_cfg_count;
1511
1512 IF (l_count > 0)
1513 THEN
1514 -- Process the QA error rows further
1515 OPEN get_cfg_line_info;
1516 LOOP
1517 FETCH get_cfg_line_info INTO l_line_id,
1518 l_valid,
1519 l_complete;
1520
1521 EXIT WHEN get_cfg_line_info%NOTFOUND;
1522
1523 IF (l_debug = 'Y') THEN
1524 OKC_DEBUG.log('6030: Line Id : '||l_line_id);
1525 OKC_DEBUG.log('6040: Config Valid YN : '||l_valid);
1526 OKC_DEBUG.log('6050: Config Complete YN : '||l_complete);
1527 END IF;
1528
1529 -- initialize the msg variable
1530 l_msg_token := NULL;
1531
1532 -- Prepare the message token string
1533 l_line_index := OKC_CONTRACT_PUB.get_concat_line_no
1534 (l_line_id,
1535 l_return_status);
1539 l_line_index := 'Unknown';
1536
1537 IF l_return_status <> OKC_API.G_RET_STS_SUCCESS
1538 THEN
1540 END IF;
1541
1542 IF (l_debug = 'Y') THEN
1543 OKC_DEBUG.log('6060: Line Index : '||l_line_index);
1544 END IF;
1545
1546 -- prefix the config string with above line index value.
1547 l_msg_token := RTRIM(l_line_index);
1548
1549 IF (l_debug = 'Y') THEN
1550 OKC_DEBUG.log('6070: QA Msg Token: '||l_msg_token);
1551 END IF;
1552
1553 -- ----------------------------------------------------------
1554 -- To prepare message string with appropriate error text.
1555 -- Handle NN,NY,YN,YY conditions for the two flags.
1556 -- The case of YY does not come into this loop at all.
1557 -- This string gets sufixed to the line-index of the token.
1558 -- ----------------------------------------------------------
1559 IF ( NVL(l_valid,'Y') = 'N' ) AND
1560 ( NVL(l_complete,'Y') = 'N' )
1561 THEN
1562 -- Flag this configured model as invalid and incomplete
1563 IF (l_debug = 'Y') THEN
1564 OKC_DEBUG.log('6080: Config Invalid and Incomplete');
1565 END IF;
1566 OKC_API.set_message(
1567 p_app_name => G_APP_NAME,
1568 p_msg_name => 'OKC_CONFIG_INV_INC',
1569 p_token1 => 'CFG_INFO',
1570 p_token1_value => l_msg_token);
1571 ELSIF ( NVL(l_valid,'Y') = 'N' ) AND
1572 ( NVL(l_complete,'Y') = 'Y' )
1573 THEN
1574 -- Flag this configured model as invalid
1575 IF (l_debug = 'Y') THEN
1576 OKC_DEBUG.log('6090: Config Invalid ');
1577 END IF;
1578 OKC_API.set_message(
1579 p_app_name => G_APP_NAME,
1580 p_msg_name => 'OKC_CONFIG_INVALID',
1581 p_token1 => 'CFG_INFO',
1582 p_token1_value => l_msg_token);
1583 ELSIF ( NVL(l_complete,'Y') = 'N' )
1584 THEN
1585 -- Flag this configured model as incomplete
1586 IF (l_debug = 'Y') THEN
1587 OKC_DEBUG.log('6100: Config Incomplete ');
1588 END IF;
1589 OKC_API.set_message(
1590 p_app_name => G_APP_NAME,
1591 p_msg_name => 'OKC_CONFIG_INCOMPLETE',
1592 p_token1 => 'CFG_INFO',
1593 p_token1_value => l_msg_token);
1594 END IF;
1595 END LOOP;
1596 CLOSE get_cfg_line_info;
1597
1598 -- Set the error return status
1599 x_return_status := OKC_API.G_RET_STS_ERROR;
1600 RAISE G_EXCEPTION_HALT_VALIDATION;
1601
1602 ELSE
1603 -- This is a normal and valid contract.
1604 -- notify caller of qa success
1605 OKC_API.set_message(
1606 p_app_name => G_APP_NAME,
1607 p_msg_name => G_QA_SUCCESS);
1608 END IF;
1609
1610 IF (l_debug = 'Y') THEN
1611 OKC_DEBUG.log('6200: Config QA Ret Status : '||x_return_status);
1612 OKC_DEBUG.log('6300: Exiting config_qa_check...', 2);
1613 OKC_DEBUG.reset_indentation;
1614 END IF;
1615
1616 EXCEPTION
1617 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1618 IF (l_debug = 'Y') THEN
1619 OKC_DEBUG.log('6400: Exiting Config_qa_check', 2);
1620 OKC_DEBUG.Reset_Indentation;
1621 END IF;
1622
1623 WHEN OTHERS THEN
1624 IF (l_debug = 'Y') THEN
1625 OKC_DEBUG.log('6500: Exiting Config_qa_check', 2);
1626 OKC_DEBUG.Reset_Indentation;
1627 END IF;
1628
1629 -- notify caller of an error as UNEXPETED error
1630 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1631
1632 OKC_API.set_message(
1633 p_app_name => G_APP_NAME,
1634 p_msg_name => G_UNEXPECTED_ERROR,
1635 p_token1 => G_SQLCODE_TOKEN,
1636 p_token1_value => SQLCODE,
1637 p_token2 => G_SQLERRM_TOKEN,
1638 p_token2_value => SQLERRM);
1639
1640 END config_qa_check;
1641
1642 -- ---------------------------------------------------------------------------
1643 -- PROCEDURE : okc_pricing_callback
1644 -- PURPOSE : To calculate LIST or SELL price of the config
1645 -- items. Callback from the Configurator window.
1646 -- The price details are for display only.
1647 -- Configurator is called from the authoring form
1648 -- OKCAUDET.fmx
1649 -- -> OKCAUPRC.fmx
1650 -- -> OKCAUCFG.plx
1651 -- -> Oracle Configurator Window
1652 -- -> Pricing_Callback
1653 -- DEBUG : 7000 Series
1654 --
1655 -- ------------------------------------------------------------------
1656 -- Pricing Callback scenarios for the Configured Lines:
1657 -- ------------------------------------------------------------------
1658 -- Oracle Configurator makes the Pricing callback calls using
1659 -- our code in OKC_CFG_PUB.okc_pricing_callback. This happens in
1663 --
1660 -- two cases. Once the CZ calls for only list_price of all model bom.
1661 -- Secondly, when the user preses the Price button. Cases 2,3 mentioned
1662 -- below will have the provision to just call or call and save values.
1664 -- 1. List price only
1665 -- p_control_rec.qp_control_rec.priceing_event='PRICE'
1666 -- p_control_rec.p_config_yn='Y'
1667 -- p_control_rec.p_top_model_id = my_top_model_line_id;
1668 -- px_cle_price_tbl contains all config lines.
1669 -- uom, currency, pricelist
1670 -- Search flag = Yes for event 'List price',
1671 -- object1_id1(inventory item_id),
1672 -- quantity,
1673 -- ID- this could be a unique sequence number
1674 --
1675 -- 2. Adjusted price but do not save adjustment data in
1676 -- database same as above except the following :
1677 -- p_control_rec.qp_control_rec.priceing_event='BATCH
1678 --
1679 -- 3. Adjusted price and save price adjustment data in database
1680 -- same as 2. except the following
1681 -- ID - this should be some valid cle_id.
1682 -- p_control_rec.p_config_yn='S'
1683 --
1684 -- OUTCOME: You will get back all the request data sent out by the
1685 -- price request. Also the sent in table px_cle_price_tbl
1686 -- will have the various prices and the return status.
1687 -- ---------------------------------------------------------------------------
1688 PROCEDURE okc_pricing_callback( p_config_session_key IN VARCHAR2,
1689 p_price_type IN VARCHAR2,
1690 x_total_price OUT NOCOPY NUMBER )
1691 IS
1692 CURSOR cur_cz_pricing_structures
1693 IS
1694 SELECT TO_NUMBER(SUBSTR(p_config_session_key,1,
1695 INSTR( p_config_session_key, '-' ) - 1 )) id,
1696 CZ_ATP_CALLBACK_UTIL.inv_item_id_from_item_key(item_key) item_id,
1697 quantity quantity,
1698 uom_code uom_code,
1699 SUBSTR(item_key, 1,INSTR( item_key, ':' ,1)-1) comp_code
1700 FROM okx_config_pricing_v
1701 WHERE configurator_session_key = p_config_session_key
1702 AND list_price IS NULL
1703 AND selling_price IS NULL;
1704
1705 CURSOR cur_line_details (p_top_model_line_id NUMBER)
1706 IS
1707 SELECT dnz_chr_id,
1708 price_list_id
1709 FROM okc_k_lines_b
1710 WHERE id = p_top_model_line_id;
1711
1712 CURSOR cur_get_hdr_det (p_chr_id NUMBER)
1713 IS
1714 SELECT currency_code,
1715 authoring_org_id,
1716 price_list_id -- If user already selects one
1717 FROM okc_k_headers_v
1718 WHERE id = p_chr_id;
1719
1720 l_control_rec OKC_PRICE_PVT.okc_control_rec_type;
1721 l_CLE_PRICE_TBL OKC_PRICE_PVT.cle_price_tbl_type;
1722 l_line_tbl OKC_PRICE_PVT.line_tbl_type;
1723 l_req_line_tbl QP_PREQ_GRP.line_tbl_type;
1724 l_req_line_qual_tbl QP_PREQ_GRP.qual_tbl_type;
1725 l_req_line_attr_tbl QP_PREQ_GRP.line_attr_tbl_type;
1726 l_req_line_detail_tbl QP_PREQ_GRP.line_detail_tbl_type;
1727 l_req_line_detail_qual_tbl QP_PREQ_GRP.line_detail_qual_tbl_type;
1728 l_req_line_detail_attr_tbl QP_PREQ_GRP.line_detail_attr_tbl_type;
1729 l_req_related_line_tbl QP_PREQ_GRP.related_lines_tbl_type;
1730 l_pricing_contexts_tbl QP_PREQ_GRP.line_attr_tbl_type;
1731 l_qual_contexts_tbl QP_PREQ_GRP.qual_tbl_type;
1732 i NUMBER := 1;
1733 l_chr_id NUMBER := 0;
1734 l_currency VARCHAR2(15);
1735 l_org_id NUMBER;
1736 l_price_list_id NUMBER;
1737 l_hdr_price_list_id NUMBER;
1738 l_top_model_line_id NUMBER;
1739 l_item_id NUMBER;
1740 l_quantity NUMBER;
1741 l_uom_code VARCHAR2(3);
1742 l_comp_code VARCHAR2(1200);
1743 x_return_status VARCHAR2(1);
1744 x_msg_count NUMBER;
1745 x_msg_data VARCHAR2(240);
1746 l_total_price NUMBER := 0;
1747 l_api_name CONSTANT VARCHAR2(30) := 'okc_pricing_callback';
1748 l_calc_price_error VARCHAR2(2000);
1749 l_unit_sell_price NUMBER;
1750 BEGIN
1751
1752 -- --------------------------------------------------------------------------
1753 -- Set the profile values for logging
1754 -- different sessions debug messages.
1755 -- --------------------------------------------------------------------------
1756 IF (l_debug = 'Y') THEN
1757 OKC_DEBUG.g_session_id := SYS_CONTEXT('USERENV','SESSIONID');
1758 END IF;
1759 FND_PROFILE.PUT('AFLOG_ENABLED','Y'); -- Enable the log
1760 FND_PROFILE.PUT('AFLOG_LEVEL',1); -- Set the debug level
1761 FND_PROFILE.PUT('AFLOG_MODULE','OKC'); -- Set the module name
1762
1763 -- --------------------------------------------------------------------------
1764 -- Pricing callback from the Oracle Configurator occurs from a new session
1765 -- in which configurator applet window operates. This is a non-apps mode.
1766 -- In the Non-apps mode the value of g_profile_log_level in the okc_debug API
1767 -- is set to 0. However in the subsequent call to the Fnd_Log.test procedure
1768 -- the value of G_CURRENT_RUNTIME_LEVEL is used.Hence need to set its value
1772 FND_LOG_REPOSITORY.init(OKC_DEBUG.g_session_id,fnd_global.user_id);
1769 -- to 1 by executing the fnd_log_repository.init procedure call.
1770 -- --------------------------------------------------------------------------
1771 -- Initialize the current runtime level
1773
1774 l_debug := 'Y';
1775
1776 IF (l_debug = 'Y') THEN
1777 OKC_DEBUG.set_indentation(l_api_name);
1778 OKC_DEBUG.log('7000: Entered okc_pricing_callback...', 2);
1779 OKC_DEBUG.log('7001: Config Session Key : '||p_config_session_key);
1780 OKC_DEBUG.log('7002: Pricing Type : '||p_price_type);
1781 END IF;
1782
1783 -- ----------------------------------------------------------
1784 -- Prepare the cz temporary table rows for pricing
1785 -- ----------------------------------------------------------
1786 OPEN cur_cz_pricing_structures;
1787 LOOP
1788 FETCH cur_cz_pricing_structures
1789 INTO l_top_model_line_id,
1790 l_item_id,
1791 l_quantity,
1792 l_uom_code,
1793 l_comp_code;
1794
1795 IF (l_debug = 'Y') THEN
1796 OKC_DEBUG.log('7010: Top Model Line Id : '||l_top_model_line_id);
1797 OKC_DEBUG.log('7011: Inventory Item Id : '||l_item_id);
1798 OKC_DEBUG.log('7012: Quantity : '||l_quantity);
1799 OKC_DEBUG.log('7013: UOM Code : '||l_uom_code);
1800 OKC_DEBUG.log('7014: Component Code : '||l_comp_code);
1801 END IF;
1802
1803 -- ----------------------------------------------------------
1804 -- Get corresponding Contract Header Id from top model line.
1805 -- ----------------------------------------------------------
1806 OPEN cur_line_details (l_top_model_line_id);
1807 FETCH cur_line_details INTO l_chr_id,l_price_list_id;
1808 CLOSE cur_line_details;
1809
1810 IF (l_debug = 'Y') THEN
1811 OKC_DEBUG.log('7015: Contract Hdr Id : '||l_chr_id);
1812 OKC_DEBUG.log('7016: Line Price List Id : '||l_price_list_id);
1813 END IF;
1814
1815 -- ----------------------------------------------------------
1816 -- Get corresponding Contract Header details from header row.
1817 -- ----------------------------------------------------------
1818 OPEN cur_get_hdr_det (l_chr_id);
1819 FETCH cur_get_hdr_det
1820 INTO l_currency,
1821 l_org_id,
1822 l_hdr_price_list_id;
1823 CLOSE cur_get_hdr_det;
1824
1825 IF (l_debug = 'Y') THEN
1826 OKC_DEBUG.log('7020: Currency : '||l_currency);
1827 OKC_DEBUG.log('7021: Auth Org Id : '||l_org_id);
1828 OKC_DEBUG.log('7022: Hdr Price List Id : '||l_hdr_price_list_id);
1829 END IF;
1830
1831 -- -------------------------------------------------------------
1832 -- If line level pricelist is null, pick header level price list
1833 -- -------------------------------------------------------------
1834 l_price_list_id := NVL(l_price_list_id,l_hdr_price_list_id);
1835
1836 IF (l_debug = 'Y') THEN
1837 OKC_DEBUG.log('7023: Final Price List Id : '||l_price_list_id);
1838 END IF;
1839
1840 -- ----------------------------------------------------------
1841 -- Prepare init values for the pricing call
1842 -- ----------------------------------------------------------
1843 l_cle_price_tbl(i).id := i; -- sequence number
1844 l_cle_price_tbl(i).qty := l_quantity;
1845 l_cle_price_tbl(i).uom_code := l_uom_code;
1846 l_cle_price_tbl(i).currency := l_currency;
1847 l_cle_price_tbl(i).id1 := l_item_id;
1848 l_cle_price_tbl(i).id2 := l_org_id;
1849 l_cle_price_tbl(i).pricelist_id := l_price_list_id;
1850
1851 EXIT WHEN cur_cz_pricing_structures%NOTFOUND;
1852 i := i + 1;
1853 END LOOP;
1854 CLOSE cur_cz_pricing_structures;
1855
1856 -- delete last blank row
1857 l_cle_price_tbl.DELETE(i);
1858
1859 IF (l_debug = 'Y') THEN
1860 OKC_DEBUG.log('7050: Total input Pr-Cb rows : '||l_cle_price_tbl.count);
1861 END IF;
1862
1863 -- ---------------------------------------------------------------------
1864 -- Pricing Call init values:
1865 -- ---------------------------------------------------------------------
1866 l_control_rec.p_top_model_id := l_top_model_line_id;
1867 l_control_rec.p_config_yn := 'Y';
1868 l_control_rec.qp_control_rec.pricing_event := 'PRICE'; -- For ListPrice
1869
1870 -- --------------------------------------------------------------
1871 -- STEP:1
1872 -- OKC Specific Pricing call to calculate list or sell price.
1873 -- --------------------------------------------------------------
1874 IF (l_debug = 'Y') THEN
1875 OKC_DEBUG.log('7700: Before Calculate Price call...');
1876 END IF;
1877
1878 OKC_PRICE_PVT.CALCULATE_PRICE(
1879 p_api_version => 1,
1880 p_init_msg_list => OKC_API.G_FALSE,
1881 p_chr_id => l_chr_id,
1882 p_Control_Rec => l_control_rec,
1883 px_req_line_tbl => l_req_line_tbl,
1884 px_Req_qual_tbl => l_req_line_qual_tbl,
1888 px_Req_LINE_DETAIL_attr_tbl => l_req_line_detail_attr_tbl,
1885 px_Req_line_attr_tbl => l_req_line_attr_tbl,
1886 px_Req_LINE_DETAIL_tbl => l_req_line_detail_tbl,
1887 px_Req_LINE_DETAIL_qual_tbl => l_req_line_detail_qual_tbl,
1889 px_Req_RELATED_LINE_TBL => l_req_related_line_tbl,
1890 px_CLE_PRICE_TBL => l_cle_price_tbl,
1891 x_return_status => x_return_status,
1892 x_msg_count => x_msg_count,
1893 x_msg_data => x_msg_data);
1894
1895 IF (l_debug = 'Y') THEN
1896 OKC_DEBUG.log('7750: Exiting Calculate Price... '||x_return_status);
1897 END IF;
1898 l_calc_price_error := 'Error in Calculate Price..';
1899
1900 -- DEBUG: Print messages from stack
1901 IF NVL(x_msg_count,0) > 0
1902 THEN
1903 FOR i IN 1..x_msg_count
1904 LOOP
1905 x_msg_data := FND_MSG_PUB.Get(p_msg_index => i,
1906 p_encoded => 'F');
1907 IF (l_debug = 'Y') THEN
1908 OKC_DEBUG.log('7755: x_msg_data '||x_msg_data);
1909 END IF;
1910 -- Limit size for Error message to Configurator
1911 IF LENGTH(l_calc_price_error||x_msg_data) < 2000
1912 THEN
1913 l_calc_price_error := l_calc_price_error ||
1914 ', '||x_msg_data;
1915 END IF;
1916 END LOOP; -- end of message count loop
1917 END IF;
1918
1919 -- Check Return Status
1920 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR
1921 THEN
1922 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1923 ELSIF x_return_status = OKC_API.G_RET_STS_ERROR
1924 THEN
1925 RAISE OKC_API.G_EXCEPTION_ERROR;
1926 END IF; -- end of ret status validation
1927
1928 l_total_price := 0;
1929 IF l_cle_price_tbl.count > 0
1930 THEN
1931 FOR i in l_cle_price_tbl.FIRST..l_cle_price_tbl.LAST
1932 LOOP
1933 -- Dump all priced config lines in debug mode
1934 IF (l_debug = 'Y') THEN
1935 OKC_DEBUG.log('7810: <--- OUTPUT LINE - '||(to_number(i)+1)||' ------->');
1936 OKC_DEBUG.log('7811: List Price : '||l_CLE_PRICE_TBL(i).list_price);
1937 OKC_DEBUG.log('7812: Nego Price : '||l_CLE_PRICE_TBL(i).negotiated_amt);
1938 OKC_DEBUG.log('7813: Pr List Id : '||l_CLE_PRICE_TBL(i).pricelist_id);
1939 OKC_DEBUG.log('7814: Line Id : '||l_CLE_PRICE_TBL(i).id);
1940 OKC_DEBUG.log('7815: Pi Bpi : '||l_CLE_PRICE_TBL(i).pi_bpi);
1941 OKC_DEBUG.log('7816: Quantity : '||l_CLE_PRICE_TBL(i).qty);
1942 OKC_DEBUG.log('7817: Uom Code : '||l_CLE_PRICE_TBL(i).uom_code);
1943 OKC_DEBUG.log('7818: Currency : '||l_CLE_PRICE_TBL(i).currency);
1944 OKC_DEBUG.log('7819: Obj Code : '||l_CLE_PRICE_TBL(i).object_code);
1945 OKC_DEBUG.log('7820: Obj Id1 : '||l_CLE_PRICE_TBL(i).id1);
1946 OKC_DEBUG.log('7821: Obj Id2 : '||l_CLE_PRICE_TBL(i).id2);
1947 END IF;
1948
1949 -- --------------------------------------------------------------
1950 -- Calculate the rollup price to be shown for top model line.
1951 -- --------------------------------------------------------------
1952 l_total_price := l_total_price + l_cle_price_tbl(i).negotiated_amt;
1953
1954 -- --------------------------------------------------------------
1955 -- Calculate the unit sell price. The Configurator window
1956 -- has two columns, one for Sell Price and other Extended Price.
1957 -- Configurator multiplies sell price with quantity to get
1958 -- Extended price. Our Pricing API call returns sell price
1959 -- multiplied by quantity in the column negotiated_amt value.
1960 -- --------------------------------------------------------------
1961 IF l_cle_price_tbl(i).qty > 0
1962 THEN
1963 l_unit_sell_price := ( l_cle_price_tbl(i).negotiated_amt /
1964 l_cle_price_tbl(i).qty);
1965 ELSE
1966 l_unit_sell_price := NVL(l_cle_price_tbl(i).negotiated_amt,0);
1967 END IF;
1968
1969 -- --------------------------------------------------------------
1970 -- Update the CZ temporary table with the pricing info.
1971 -- --------------------------------------------------------------
1972 UPDATE cz_pricing_structures
1973 SET selling_price = l_unit_sell_price,
1974 list_price = l_cle_price_tbl(i).list_price
1975 WHERE configurator_session_key = p_config_session_key
1976 AND CZ_ATP_CALLBACK_UTIL.inv_item_id_from_item_key(item_key) =
1977 l_cle_price_tbl(i).id1 ;
1978 END LOOP; -- l_cle_price_tbl rows
1979 END IF;
1980
1981 -- ----------------------------------------------------------
1982 -- The total price should reflect the quantity change in the
1983 -- configurator window. The sum sellprice will only produce
1984 -- the sum of unit-sell-prices of all the items configured.
1985 -- The following line replaces the select statement below it.
1986 -- ---------------------------------------------------------
1987 x_total_price := l_total_price;
1988
1989 -- BEGIN
1990 -- SELECT SUM( selling_price )
1991 -- INTO x_total_price
1995 -- WHEN OTHERS THEN
1992 -- FROM okx_config_pricing_v
1993 -- WHERE configurator_session_key = p_config_session_key ;
1994 -- EXCEPTION
1996 -- RAISE OKC_API.G_EXCEPTION_ERROR;
1997 -- END;
1998 -- -------------------------------------------------------------
1999
2000 IF (l_debug = 'Y') THEN
2001 OKC_DEBUG.log('7900: Total Sell Price : '||x_total_price);
2002 OKC_DEBUG.log('7999: Leaving okc_pricing_callback...', 2);
2003 END IF;
2004
2005 IF (l_debug = 'Y') THEN
2006 OKC_DEBUG.Reset_Indentation;
2007 END IF;
2008
2009 EXCEPTION
2010 WHEN OTHERS THEN
2011
2012 IF (l_debug = 'Y') THEN
2013 OKC_DEBUG.log('7995: Sending Pricing Callback Error Message to Configurator...');
2014 OKC_DEBUG.log('7996: Error Message: '||l_calc_price_error);
2015 END IF;
2016
2017 -- Set the error message
2018 UPDATE okx_config_pricing_v
2019 SET msg_data = l_calc_price_error
2020 WHERE configurator_session_key = p_config_session_key;
2021
2022 IF (l_debug = 'Y') THEN
2023 OKC_DEBUG.log('7997: Pricing Callback Error Message sent to Configurator...');
2024 OKC_DEBUG.Reset_Indentation;
2025 END IF;
2026
2027 END okc_pricing_callback;
2028
2029
2030 -- ---------------------------------------------------------------------------
2031 -- PROCEDURE : copy_config
2032 -- PURPOSE : Creates new configuration header and revision while
2033 -- copying a contract. The newly copied contract will point
2034 -- to the newly created config header and revisions.
2035 -- This procedure is called from the contract COPY APIs.
2036 -- Procedure will handle all configured models in a contract.
2037 -- ---------------------------------------------------------------------------
2038 PROCEDURE copy_config ( p_dnz_chr_id IN NUMBER,
2039 x_return_status OUT NOCOPY VARCHAR2)
2040 AS
2041 -- -------------------------------------------------------
2042 -- Get all the top models in a copied target contract.
2043 -- There can be more than one configuration in a contract.
2044 -- -------------------------------------------------------
2045 CURSOR cur_get_top_models
2046 IS
2047 SELECT clev.id,
2048 clev.config_header_id,
2049 clev.config_revision_number
2050 FROM okc_k_lines_v clev
2051 WHERE clev.dnz_chr_id = p_dnz_chr_id
2052 AND clev.config_item_type = 'TOP_MODEL_LINE';
2053
2054 l_top_model_line_id NUMBER;
2055 l_cfg_hdr_id NUMBER;
2056 l_cfg_rev_nbr NUMBER;
2057
2058 -- l_new_config_flag VARCHAR2(1) := '1';
2059 l_copy_mode VARCHAR2(1) := CZ_API_PUB.G_NEW_HEADER_COPY_MODE;
2060 l_api_version NUMBER := 1;
2061 i BINARY_INTEGER;
2062 x_msg_count NUMBER;
2063
2064 x_err_msg VARCHAR2(200);
2065 x_ret_status VARCHAR2(1);
2066 x_cfg_hdr_id NUMBER;
2067 x_cfg_rev_nbr NUMBER;
2068
2069 x_orig_item_id_tbl CZ_API_PUB.NUMBER_TBL_TYPE;
2070 x_new_item_id_tbl CZ_API_PUB.NUMBER_TBL_TYPE;
2071
2072
2073 BEGIN
2074 SAVEPOINT okc_copy_config;
2075 IF (l_debug = 'Y') THEN
2076 OKC_DEBUG.set_indentation('copy_config');
2077 END IF;
2078 --FND_MSG_PUB.initialize;
2079
2080 IF (l_debug = 'Y') THEN
2081 OKC_DEBUG.log('9000: Entering OKC Copy Configuration...');
2082 END IF;
2083 x_return_status := FND_API.G_RET_STS_SUCCESS;
2084
2085 FOR cur_get_top_models_rec in cur_get_top_models
2086 LOOP
2087 l_top_model_line_id := cur_get_top_models_rec.id;
2088 l_cfg_hdr_id := cur_get_top_models_rec.config_header_id;
2089 l_cfg_rev_nbr := cur_get_top_models_rec.config_revision_number;
2090
2091 IF (l_debug = 'Y') THEN
2092 OKC_DEBUG.log('9100: Config Top Model Id : '||l_top_model_line_id);
2093 OKC_DEBUG.log('9110: Old config header id : '||l_cfg_hdr_id);
2094 OKC_DEBUG.log('9120: Old config Revision : '||l_cfg_rev_nbr);
2095 END IF;
2096
2097 IF l_cfg_hdr_id IS NOT NULL AND
2098 l_cfg_rev_nbr IS NOT NULL
2099 THEN
2100 IF (l_debug = 'Y') THEN
2101 OKC_DEBUG.log('9130: Calling CZ Copy Config API...');
2102 END IF;
2103
2104 -- Bug 2614950.
2105 -- The old copy_configuration call has been commented out below.
2106 -- New signature of copy configuration has been provided in the
2107 -- CZ_CONFIG_API_PUB package.The new signature returns two more OUT tables,
2108 -- one having the old config item ids and the other having the corresponding
2109 -- new config item ids(if there is any change in the config item ids).The
2110 -- config item ids of the contract lines should also be updated using these
2111 -- pl/sql out tables.
2112
2113
2114 /* CZ_CF_API.copy_configuration
2115 ( config_hdr_id => l_cfg_hdr_id,
2116 config_rev_nbr => l_cfg_rev_nbr,
2117 new_config_flag => l_new_config_flag,
2118 out_config_hdr_id => x_cfg_hdr_id,
2119 out_config_rev_nbr => x_cfg_rev_nbr,
2120 error_message => x_err_msg,
2121 return_value => x_ret_value); */
2122
2126 p_config_rev_nbr => l_cfg_rev_nbr,
2123 CZ_CONFIG_API_PUB.copy_configuration
2124 ( p_api_version => l_api_version,
2125 p_config_hdr_id => l_cfg_hdr_id,
2127 p_copy_mode => l_copy_mode,
2128 x_config_hdr_id => x_cfg_hdr_id,
2129 x_config_rev_nbr => x_cfg_rev_nbr,
2130 x_orig_item_id_tbl => x_orig_item_id_tbl,
2131 x_new_item_id_tbl => x_new_item_id_tbl,
2132 x_return_status => x_ret_status,
2133 x_msg_count => x_msg_count,
2134 x_msg_data => x_err_msg );
2135
2136 END IF;
2137
2138
2139 IF x_ret_status <> OKC_API.G_RET_STS_SUCCESS
2140 THEN
2141 -- This is an error condition for copy configuration
2142 IF (l_debug = 'Y') THEN
2143 OKC_DEBUG.log('9250: Error in Copy Configuration...'||x_err_msg);
2144 END IF;
2145 RAISE OKC_API.G_EXCEPTION_ERROR;
2146 ELSE
2147 -- --------------------------------------------------------
2148 -- CZ Copy config returned success.
2149 -- Update contract lines for this config with new pointers
2150 -- for the columns config_top_model_line_id,
2151 -- config_header_id,config_revision_number.
2152 -- --------------------------------------------------------
2153
2154 IF (l_debug = 'Y') THEN -- Display all the returned info in debug mode
2155
2156 OKC_DEBUG.LOG('9200: New config header id : '||x_cfg_hdr_id);
2157 OKC_DEBUG.LOG('9210: New config Revision : '||x_cfg_rev_nbr);
2158 OKC_DEBUG.LOG('9220: Error Message : '||x_err_msg);
2159 OKC_DEBUG.LOG('9221: Error Message Count : '||x_msg_count);
2160 OKC_DEBUG.LOG('9230: Return Status : '||x_ret_status);
2161 OKC_DEBUG.LOG(' ');
2162 OKC_DEBUG.LOG('9231: The original item ids from the output table');
2163
2164 IF x_orig_item_id_tbl.count > 0 THEN
2165 FOR i in x_orig_item_id_tbl.FIRST..x_orig_item_id_tbl.LAST
2166 LOOP
2167 OKC_DEBUG.LOG(' ');
2168 OKC_DEBUG.LOG('Original config item id '||i||' = '||x_orig_item_id_tbl(i));
2169 END LOOP;
2170 END IF;
2171
2172 OKC_DEBUG.LOG(' ');
2173 OKC_DEBUG.LOG('9232: The new item ids from the output table');
2174
2175 IF x_new_item_id_tbl.count > 0 THEN
2176 FOR i in x_new_item_id_tbl.FIRST..x_new_item_id_tbl.LAST
2177 LOOP
2178 OKC_DEBUG.LOG(' ');
2179 OKC_DEBUG.LOG('New config item id '||i||' = '||x_new_item_id_tbl(i));
2180 END LOOP;
2181 END IF;
2182 END IF; -- End display all the returned info in debug mode
2183
2184
2185 UPDATE okc_k_lines_b
2186 SET config_top_model_line_id = l_top_model_line_id,
2187 config_header_id = x_cfg_hdr_id,
2188 config_revision_number = x_cfg_rev_nbr
2189 WHERE dnz_chr_id = p_dnz_chr_id
2190 AND config_header_id = l_cfg_hdr_id
2191 AND config_revision_number = l_cfg_rev_nbr ;
2192
2193 END IF;
2194
2195 IF (l_debug = 'Y') THEN
2196 OKC_DEBUG.log('9500: Created new Configuration for : '||l_cfg_hdr_id, 2);
2197 END IF;
2198
2199 -- ----------------------------------------------------------------------------
2200 -- Update the contract lines for this configuration with the new config_item_id
2201 -- with the new config_item_id.
2202 -- ----------------------------------------------------------------------------
2203
2204 IF (l_debug = 'Y') THEN
2205 OKC_DEBUG.log('9501: Updating the CONFIG ITEM IDs : ');
2206 END IF;
2207
2208 IF x_orig_item_id_tbl.COUNT > 0 THEN
2209 FOR i IN x_orig_item_id_tbl.FIRST..x_orig_item_id_tbl.LAST LOOP
2210 UPDATE okc_k_lines_b
2211 SET config_item_id = x_new_item_id_tbl(i)
2212 WHERE
2213 dnz_chr_id = p_dnz_chr_id
2214 AND config_header_id = x_cfg_hdr_id
2215 AND config_revision_number = x_cfg_rev_nbr
2216 AND config_item_id = x_orig_item_id_tbl(i)
2217 AND config_item_type IN ('CONFIG','TOP_BASE_LINE');
2218 END Loop;
2219 x_return_status := FND_API.G_RET_STS_SUCCESS;
2220 END IF;
2221
2222 END LOOP;
2223
2224 IF (l_debug = 'Y') THEN
2225 OKC_DEBUG.log('9999: Exiting Copy Configuration...', 2);
2226 OKC_DEBUG.Reset_Indentation;
2227 END IF;
2228
2229 EXCEPTION
2230 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2231 IF (l_debug = 'Y') THEN
2232 OKC_DEBUG.log('5999:Exception Error in Copy Config...', 2);
2233 OKC_DEBUG.Reset_Indentation;
2234 END IF;
2235
2236 IF cur_get_top_models%ISOPEN
2237 THEN
2238 CLOSE cur_get_top_models;
2239 END IF;
2240
2241 x_return_status := FND_API.G_RET_STS_ERROR;
2242
2243 WHEN OTHERS THEN
2244 IF (l_debug = 'Y') THEN
2245 OKC_DEBUG.log('5999:Other Exception in Copy Config...', 2);
2246 OKC_DEBUG.Reset_Indentation;
2247 END IF;
2248
2249 IF cur_get_top_models%ISOPEN
2250 THEN
2251 CLOSE cur_get_top_models;
2252 END IF;
2253
2254 OKC_API.set_message(
2255 p_app_name => G_APP_NAME,
2256 p_msg_name => G_UNEXPECTED_ERROR,
2257 p_token1 => G_SQLCODE_TOKEN,
2258 p_token1_value => SQLCODE,
2259 p_token2 => G_SQLERRM_TOKEN,
2260 p_token2_value => SQLERRM);
2261
2262 x_return_status := FND_API.G_RET_STS_ERROR;
2263 END copy_config;
2264
2265 END okc_cfg_pub;
[Home] [Help]