[Home] [Help]
PACKAGE BODY: APPS.OKC_OC_INT_KTQ_PVT
Source
1 Package Body OKC_OC_INT_KTQ_PVT AS
2 /* $Header: OKCRKTQB.pls 120.3 2006/02/28 14:50:17 smallya noship $ */
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5
6 --
7 -- Copyright (c) 1999 Oracle Corporation, Redwood Shores, CA, USA
8 -- All rights reserved.
9 --
10 -- Created By Matt Connors 01-31-2000
11 -- Modified By Satish Karumuri 02-14-2000
12 -- Modified Quote creation around currency AND effectivity rules.
13 --
14 -- Modified By Eric TRUSZ 04-19-2000
15 --
16 -- 1-Implemented trace mode
17 -- 2-Added conditions on contract validations (Sell AND Issue)
18 -- 3-Added condition on g_rd_billto, g_rd_custacct, g_rd_price and
19 -- g_rd_invrule rules uniqueness
20 -- 4-Added conditions on contract party validations
21 -- (2 parties, 2 roles, 1 party <> operunit)
22 -- 5-Completed exception clauses fro trace mode
23 -- 6-Removed conditions on rule groups in the c_rule cursor
24 -- 7-Included Convertion rule
25 -- 8-Removed SUPPORT as candidate line for selecting covered product lines
26 -- 9-Made mandatory the currency code at the contract header.
27 -- No default value derived from the contract operating unit.
28 -- 10-Added exception clauses in build_qte_hdr AND build_qte_line procedures
29 -- AND completed with error message handling
30 -- 11-Completed global cusrsors with additional columns (currency_code...)
31 -- 12-Reviewed quote line price calculation
32 -- 13-Added exchange information for quote
33 -- 14-Added conditions on org_id AND organization_id when
34 -- using okx_customer_products_v
35 -- 15-Added the organization context initialization
36 -- 16-Ensure that the source of the covered products is OKX_CUSTOMER_PRODUCTS_V
37 -- instead of testing the JTF Object code
38 -- 17-Reviewed quote line creation by disregarding the top lines for which
39 -- an error occurs when calculating the price, AND by printing
40 -- an appropriate message
41 -- 18-Reviewed contract line research by disregarding the service AND ext warr.
42 -- lines having no covered product lines, AND by printing an appropriate
43 -- message
44 -- 19-reevaluated the quote line price as unit price
45 --
46 --
47 -- Modified By Eric TRUSZ 04-19-2000
48 --
49 -- 1-Added the quote NUMBER having been created in the contract comments;
50 -- In case the quote creation failed, a specific error message is added.
51 -- 2-Relplaced all TABLE_NAME_B reference with the related view TABLE_NAME_V
52 -- to be compliant with the standards
53 -- 3-Implemented quote line creation for contract having support top
54 -- AND sub lines
55 -- 4-Removed the creation of a dummy quote detail line when we have no
56 -- subline for a top contract line
57 -- 5-Added trace file name into the contract's comments
58 -- 6-Set an error message before each different API called
59 -- 7-Added update_k_comments_err procedure to update contract in case of quote
60 -- creation error
61 --
62 -- Modified By Eric TRUSZ 07-11-2000
63 --
64 -- 1-Reviewed quote line price calculation (New requirements defined by Mara
65 -- AND Jorg)
66 -- 2-Reviewed contract comments update with specific OKC message.
67 -- 3-Moved trace procedures to the OKC_UTIL package
68 --
69 -- Modified By Eric TRUSZ 07-27-2000
70 --
71 -- 1-Removed use of service_ref_line_number in quote creation, used to
72 -- reference any existing quote line
73 --
74 -- Modified By Eric TRUSZ 08-09-2000
75 --
76 -- 1-Replaced all okc_xxx_v references with okc_xxx_b references
77 --
78 -- Modified By Eric TRUSZ 10-20-2000
79 --
80 -- 1-Modified c_top_cle cursor for contract top line selection
81 -- -All the hierarchy of the top line style is considered to find out
82 -- priced items
83 -- -Each is printed as well as the exception in case it is not
84 -- suitable for a quote/order creation
85 -- -Modified order of contract line processing to ensure that Li. Prod.
86 -- lines will be handled before Support lines
87 --
88 -- 2-Added new features:
89 -- -rules for renewal (H, L)
90 -- -ship_to rule (H)
91 -- -customer_order_enabled_flag
92 -- -Added flexibility by created new procedures:
93 -- -is_line_orderable_s
94 -- -is_line_orderable_i
95 -- -is_line_with_covered_prod
96 -- -build_oc_relationships
97 -- -validate_oc_eligibility
98 --
99 -- Modified By Eric TRUSZ 02-26-2001
100 --
101 -- Modified to provide with service reference values
102 -- in service_ref_line_id instead of service_ref_system_id.
103 --
104 -- ===========================================================================
105 --
106 -- Modified By Vijay Ramalingam 30-Jul-2001
107 --
108 -- - Added/modified the following new procedures
109 -- - added update_quote_from_k
110 -- - added validate_k_eligibility
111 -- - added build_k_structures
112 -- - modified build_qte_hdr
113 -- - modified build_qte_line
114 -- - added aso_quote_pub.update_quote_from_k procedure
115 --
116 --
117 -- - Added the following new functions
118 --
119 -- - is_top_line_style_seeded
120 -- - is_top_line_with_covered_prod
121 -- - is_top_line_orderable
122 -- - is_kl_linked_to_ql
123 --
124 -- Modified By Vijay Ramalingam 13-Aug-2001
125 --
126 -- - Added the sales rep information for the quote header rec
127 -- - Added the sign_by_date for the quote_expiration_date
128 -- - Added Procedure calls to sales credit
129 -- - Modified the c_q_k_rel cursor to pick up the quote id
130 -- - Modified procedures, wherever p_quote_id was passed and
131 -- replace it with a g_quote_id which was selected
132 -- instead of being passed.
133 -- - Fixed a minor bug encountered during unit testing in the
134 -- is_top_line_orderable_i
135 --
136 --
137 -- Modified By Vijay Ramalingam 20-Aug-2001
138 --
139 -- - Added the call to the quote_line relationship procedure in
140 -- the OKC_OC_INT_CONFIG_PVT package to create the relationship
141 -- between the quote lines.
142 -- - Modified the c_top_cle_init cursor to include the configuration
143 -- items.
144 -- - Modified the build_qte_line procedure to handle the configuration
145 -- items, and to populate the item type code
146 --
147 -- Modified By Vijay Ramalingam 28-Aug-2001
148 --
149 -- - Deleted some global constants, variables and cursors that
150 -- were not used in the package
151 --
152 -- Modified By Vijay Ramalingam 30-Aug-2001
153 --
154 -- - Modified the way the px_k2q_line_tbl is populated in the build_qte_line
155 -- procedure.
156 --
157 -- Notes AND limitations:
158 --
159 -- ===========================================================================
160 --
161 -- global constants
162 --
163 -- standard api constants
164 --
165 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKC_CONTRACTS_UNEXP_ERROR';
166 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'SQLCODE';
167 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'SQLERRM';
168 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKC_OC_INT_KTQ_PVT';
169 G_APP_NAME CONSTANT VARCHAR2(3) := OKC_API.G_APP_NAME;
170 G_APP_NAME1 CONSTANT VARCHAR2(3) := 'OKO';
171
172 -- other constants
173 g_okx_system_items_v CONSTANT VARCHAR2(30) := 'OKX_SYSTEM_ITEMS_V';
174 g_okx_product_lines_v CONSTANT VARCHAR2(50) := 'OKX_PRODUCT_LINES_V';
175 g_okx_customer_products_v CONSTANT VARCHAR2(50) := 'OKX_CUSTOMER_PRODUCTS_V';
176 g_okx_operunit CONSTANT VARCHAR2(30) := 'OKX_OPERUNIT';
177 g_okx_legentity CONSTANT VARCHAR2(30) := 'OKX_LEGAL_ENTITY';
178 g_okx_service CONSTANT VARCHAR2(30) := 'OKX_SERVICE';
179
180 g_sts_entered CONSTANT VARCHAR2(30) := 'ENTERED';
181 g_sts_terminated CONSTANT VARCHAR2(30) := 'TERMINATED';
182
183 g_qte_ref_cp CONSTANT VARCHAR2(30) := 'CUSTOMER_PRODUCT';
184 g_qte_ref_order CONSTANT VARCHAR2(30) := 'ORDER';
185
186 g_supplier_ptrol CONSTANT VARCHAR2(30) := 'SUPPLIER';
187 g_salesrep_ctrol CONSTANT VARCHAR2(30) := 'SALESPERSON';
188 g_jtf_okx_salepers CONSTANT VARCHAR2(30) := 'OKX_SALEPERS';
189
190
191 --
192 -- global cursors
193 --
194 -- cursor to get the quote header information
195 --
196 CURSOR c_qhr(b_qhr_id NUMBER) IS
197 SELECT quote_number
198 ,quote_version
199 FROM okx_quote_headers_v
200 WHERE id1 = b_qhr_id;
201
202 --
203 -- cursor for contract header information
204 --
205 CURSOR c_chr (b_chr_id NUMBER) IS SELECT
206 object_version_number
207 ,authoring_org_id
208 ,inv_organization_id
209 ,contract_number
210 ,contract_number_modifier
211 ,currency_code
212 ,estimated_amount
213 ,date_renewed
214 ,scs_code
215 ,total_line_list_price
216 ,price_list_id
217 ,sign_by_date
218 FROM okc_k_headers_b
219 WHERE id = b_chr_id;
220
221 CURSOR c_k_header(b_chr_id NUMBER) IS
222 SELECT
223 kh.ID ,
224 --kh.STS_CODE ,
225 kh.SCS_CODE ,
226 kh.CONTRACT_NUMBER ,
227 kh.CURRENCY_CODE ,
228 kh.CONTRACT_NUMBER_MODIFIER,
229 kh.TEMPLATE_YN ,
230 kh.TEMPLATE_USED ,
231 kh.CHR_TYPE ,
232 kh.DATE_TERMINATED ,
233 --
234 kh.DATE_RENEWED ,
235 kh2.contract_number ren_contract_num,
236 --
237 kh.START_DATE ,
238 kh.END_DATE ,
239 kh.AUTHORING_ORG_ID ,
240 kh.INV_ORGANIZATION_ID ,
241 kh.BUY_OR_SELL ,
242 kh.ISSUE_OR_RECEIVE ,
243 kh.ESTIMATED_AMOUNT ,
244 ks.cls_code ,
245 ks.meaning ,
246 kst.ste_code
247 FROM okc_statuses_b kst,
248 okc_k_headers_b kh,
249 okc_k_headers_b kh2,
250 okc_subclasses_v ks
251 WHERE kh.id = b_chr_id
252 AND ks.code = kh.scs_code
253 AND kst.code = kh.sts_code
254 AND kh2.id(+) = kh.chr_id_renewed_to;
255
256
257 --
258 -- cursor to get covered product information for:
259 -- service lines, ext warranty lines and support lines
260 --
261 CURSOR c_cp (b_chr_id NUMBER,
262 b_line_id NUMBER,
263 b_org_id NUMBER,
264 b_inv_org_id NUMBER) IS
265 SELECT -- For service and ext warranty lines
266 cle.id cle_id
267 ,cle.line_number line_number
268 --,cle.sts_code sts_code
269 ,sts.ste_code ste_code
270 --
271 ,NVL(cim.number_of_items, cpt.quantity) quantity
272 ,DECODE(cim.number_of_items, NULL,cpt.unit_of_measure_code,
273 cim.uom_code) uom_code
274 ,cim.priced_item_yn priced_item_yn
275 ,cle.price_unit price_unit
276 ,cle.price_negotiated price_negotiated
277 ,cle.currency_code currency_code
278 --
279 ,cle.start_date start_date
280 ,cle.end_date end_date
281 --
282 ,lse.lse_type line_style
283 ,lse.lty_code line_type
284 ,lss.jtot_object_code line_source_code
285 ,jot.from_table line_source_table
286 --
287 --,cpt.id1 id1
288 --,cpt.id2 id2
289 ,cim.object1_id1 id1
290 ,cim.object1_id2 id2
291 ,cpt.name prod_name
292 ,cim.jtot_object1_code item_source_code
293 ,jot.from_table item_source_table
294 FROM
295 jtf_objects_b jot
296 ,okc_k_lines_b cle
297 ,okc_k_items cim
298 ,okc_line_styles_b lse
299 ,okc_line_style_sources lss
300 ,okx_customer_products_v cpt
301 ,okc_statuses_b sts
302 WHERE
303 cim.cle_id = cle.id
304 AND cpt.id1 = cim.object1_id1
305 AND cpt.id2 = cim.object1_id2
306 AND cpt.org_id = b_org_id
307 AND cpt.organization_id = b_inv_org_id
308 AND jot.object_code = cim.jtot_object1_code
309 AND rtrim(ltrim(jot.from_table)) like g_okx_customer_products_v||'%'||cim.jtot_object1_code
310 --
311 AND lse.id = cle.lse_id
312 AND lse.lty_code = g_lt_coverprod -- must be a covered product
313 AND lss.lse_id = lse.id
314 AND lss.jtot_object_code = cim.jtot_object1_code
315 --
316 AND sts.code = cle.sts_code
317 AND sts.ste_code <> g_sts_terminated
318 --
319 AND cle.cle_id = b_line_id -- immediate child of top line
320 AND cle.dnz_chr_id = b_chr_id
321
322 UNION
323
324 SELECT -- For support lines
325 cle.id cle_id
326 ,cle.line_number line_number
327 --,cle.sts_code sts_code
328 ,sts.ste_code ste_code
329 --
330 ,NVL(cim.number_of_items, cpt.quantity) quantity
331 ,DECODE(cim.number_of_items, NULL,cpt.unit_of_measure_code,
332 cim.uom_code) uom_code
333 ,cim.priced_item_yn priced_item_yn
334 ,cle.price_unit
335 ,cle.price_negotiated
336 ,cle.currency_code
337 --
338 ,cle.start_date
339 ,cle.end_date
340 --
341 ,lse.lse_type line_style
342 ,lse.lty_code line_type
343 ,lss.jtot_object_code line_source_code
344 ,jot.from_table line_source_table
345 --
346 --,cpt.inventory_item_id
347 --,cpt.organization_id
348 ,cim.object1_id1 id1
349 ,cim.object1_id2 id2
350 ,sit.name prod_name
351 ,cim.jtot_object1_code
352 ,jot.from_table
353 FROM jtf_objects_b jot
354 ,okc_k_lines_b cle
355 ,okc_k_items cim
356 ,okc_line_styles_b lse
357 ,okc_line_style_sources lss
358 ,okx_product_lines_v cpt
359 ,okx_system_items_v sit
360 ,okc_statuses_b sts
361 WHERE cim.cle_id = cle.id
362 AND cpt.id1 = cim.object1_id1
363 AND cpt.id2 = cim.object1_id2
364 AND cpt.dnz_chr_id = cle.dnz_chr_id
365 AND jot.object_code = cim.jtot_object1_code
366 AND rtrim(ltrim(jot.from_table)) like
367 DECODE(lse.lty_code, g_lt_suppline,
368 g_okx_product_lines_v||'%'||cim.jtot_object1_code,
369 g_okx_system_items_v||'%'||cim.jtot_object1_code)
370 --
371 AND sit.organization_id = cpt.organization_id
372 AND sit.inventory_item_id = cpt.inventory_item_id
373 --
374 AND lse.id = cle.lse_id
375 AND lse.lty_code IN (g_lt_suppline, -- must be a support_line line,
376 g_lt_supp) -- or support line
377 AND lss.lse_id = lse.id
378 AND lss.jtot_object_code = cim.jtot_object1_code
379 --
380 AND sts.code = cle.sts_code
381 AND sts.ste_code <> g_sts_terminated
382 --
383 AND cle.cle_id = b_line_id -- immediate child of top line
384 AND cle.dnz_chr_id = b_chr_id;
385
386 --
387 -- cursor to get customer information
388 -- header level customers only
389 -- the customer is the role in the sell contract that is not me
390 -- this assumption will not hold as more roles get added post 11i
391 -- IF for a party, this should be party id, not cust account id
392 --
393
394 CURSOR c_cust (b_chr_id NUMBER) IS SELECT
395 cpr.id
396 ,cpr.jtot_object1_code
397 ,cpr.object1_id1
398 ,cpr.object1_id2
399 ,cpr.rle_code
400 FROM okc_k_party_roles_b cpr
401 ,okc_role_sources rsc
402 WHERE
403 rsc.buy_or_sell = 'S' -- sell contract
404 AND rsc.rle_code = cpr.rle_code -- role
405 AND rsc.start_date <= sysdate
406 AND NVL(rsc.end_date, sysdate) >= sysdate
407 AND cpr.cle_id IS NULL -- parties
408 AND cpr.dnz_chr_id = b_chr_id;
409 -- AND cpr.jot.object1_code <> g_okx_legentity;
410 --and cpr.jot.object1_code <> g_okx_operunit -- not me
411
412
413 --
414 -- cursor to see if contract is governed by another contract
415 --
416 CURSOR c_hdr_subject_to (b_chr_id NUMBER) IS
417 SELECT chr_id_referred
418 FROM okc_governances
419 WHERE dnz_chr_id = b_chr_id;
420
421 --
422 -- cursor to select contract comments to update with quote/order information
423 --
424 CURSOR c_k_header_tl (b_chr_id NUMBER) IS
425 SELECT
426 khtl.ID,
427 khtl.COMMENTS
428 FROM okc_k_headers_v khtl
429 WHERE khtl.ID = b_chr_id;
430 --FOR UPDATE OF khtl.COMMENTS;
431
432 CURSOR c_k_top_line_styles(b_scs_code VARCHAR2) IS
433 select distinct(lse.lty_code) lty_code,
434 lse.priced_yn,
435 lse.item_to_price_yn,
436 lse.price_basis_yn,
437 lse.id,
438 lse.name,
439 jot.object_code,
440 jot.where_clause,
441 jot.from_table
442 -- stl.seeded_flag
443 from okc_subclass_top_line stl,
444 okc_line_styles_v lse,
445 okc_line_style_sources lss,
446 jtf_objects_b jot
447 where
448 jot.object_code = lss.jtot_object_code
449 and lss.lse_id = lse.id
450 and sysdate between lss.start_date and nvl(lss.end_date,sysdate)
451 and lse.id = stl.lse_id
452 and sysdate between stl.start_date and nvl(stl.end_date,sysdate)
453 -- and stl.seeded_flag = 'Y'
454 and stl.scs_code = b_scs_code;
455
456
457 TYPE line_style_rec_type IS RECORD(
458 lty_code okc_line_styles_v.lty_code%type,
459 priced_yn okc_line_styles_v.priced_yn%type,
460 item_to_price_yn okc_line_styles_v.item_to_price_yn%type,
461 price_basis_yn okc_line_styles_v.price_basis_yn%type,
462 lse_id okc_line_styles_v.id%type,
463 lse_name okc_line_styles_v.name%type,
464 object_code jtf_objects_b.object_code%type,
465 where_clause jtf_objects_b.where_clause%type,
466 from_table jtf_objects_b.from_table%type
467 -- seeded_flag okc_subclass_top_line.seeded_flag%TYPE
468 );
469
470 TYPE line_style_tab IS TABLE OF line_style_rec_type INDEX BY BINARY_INTEGER;
471 l_line_style_tab line_style_tab;
472
473
474 --
475 -- cursor to get header rule information related to the customer
476 --
477 CURSOR c_rules(b_chr_id NUMBER,
478 b_cle_id NUMBER,
479 b_cpr_id NUMBER) IS
480 SELECT
481 rgp.chr_id,
482 rgp.cle_id,
483 rul.object1_id1,
484 rul.object1_id2,
485 rul.jtot_object1_code,
486 rul.object2_id1,
487 rul.object2_id2,
488 rul.jtot_object2_code,
489 rul.object3_id1,
490 rul.object3_id2,
491 rul.jtot_object3_code,
492 rul.rule_information_category
493 FROM
494 okc_rule_groups_b rgp
495 ,okc_rules_b rul
496 -- ,okc_rg_party_roles rpr
497 WHERE
498 -- Since only one party is allowed in a contract, the party who is acting
499 -- as the subject or object of a rule group is not handled
500 --rpr.cpl_id = b_cpr_id
501 --AND rpr.rgp_id = rgp.id
502 --AND rpr.dnz_chr_id = rgp.dnz_chr_id
503 rgp.dnz_chr_id = b_chr_id
504 AND ((rgp.cle_id IS NULL AND b_cle_id IS NULL) OR
505 (b_cle_id IS NOT NULL AND rgp.cle_id = b_cle_id))
506 -- AND rgp.rgd_code in (g_rg_billing, g_rg_service, g_rg_pricing)
507 AND rul.rgp_id = rgp.id
508 AND rul.rule_information_category IN (
509 g_rd_billto,
510 g_rd_shipto,
511 g_rd_shipmtd,
512 g_rd_custacct,
513 g_rd_invrule,
514 g_rd_price,
515 g_rd_convert);
516
517 --
518 -- Cursor to select the contract to quote relationship object id.
519 --
520
521 CURSOR c_q_k_rel(b_kh_id NUMBER, b_kl_id NUMBER, b_qh_id NUMBER,
522 b_rlt_code VARCHAR, b_rlt_type VARCHAR) IS
523 SELECT
524 krel.object1_id1
525 FROM
526 okc_k_rel_objs krel
527 WHERE
528 krel.chr_id = b_kh_id
529 AND ((krel.cle_id IS NULL AND b_kl_id IS NULL
530 AND krel.object1_id1 = DECODE(NVL(b_qh_id,OKC_API.G_MISS_NUM),OKC_API.G_MISS_NUM, krel.object1_id1, b_qh_id))
531 OR (b_kl_id IS NOT NULL AND krel.cle_id = b_kl_id))
532 AND krel.rty_code = b_rlt_code
533 AND krel.jtot_object1_code = b_rlt_type;
534
535
536 --
537 -- global type declarations
538 --
539 TYPE line_info_rec_typ IS RECORD (
540 line_id okc_k_lines_v.id%TYPE
541 ,cle_id okc_k_lines_v.cle_id%TYPE
542 ,lse_id okc_k_lines_v.lse_id%TYPE
543 ,line_number okc_k_lines_v.line_number%TYPE
544 ,status_code okc_statuses_b.ste_code%TYPE
545 --
546 ,qty okc_k_items.number_of_items%TYPE
547 ,uom_code okc_k_items.uom_code%TYPE
548 ,customer_order_enabled_flag VARCHAR2(1)
549 ,item_name okc_k_lines_v.name%TYPE
550 --,item_name VARCHAR2(150)
551 ,priced_item_yn okc_k_items.priced_item_yn%TYPE
552 ,price_unit okc_k_lines_v.price_unit%TYPE
553 ,price_negotiated okc_k_lines_v.price_negotiated%TYPE
554 ,line_list_price okc_k_lines_v.line_list_price%TYPE
555 ,price_list_id okc_k_lines_v.price_list_id%TYPE
556 ,price_list_line_id okc_k_lines_v.price_list_line_id%TYPE
557 ,currency_code okc_k_lines_v.currency_code%TYPE
558 --
559 ,config_header_id okc_k_lines_v.config_header_id%TYPE
560 ,config_revision_number okc_k_lines_v.config_revision_number%TYPE
561 ,config_complete_yn okc_k_lines_v.config_complete_yn%TYPE
562 ,config_valid_yn okc_k_lines_v.config_valid_yn%TYPE
563 ,config_item_id okc_k_lines_v.config_item_id%TYPE
564 ,config_item_type okc_k_lines_v.config_item_type%TYPE
565 ,component_code okx_config_items_v.component_code%TYPE
566 --
567 ,start_date okc_k_lines_v.start_date%TYPE
568 ,end_date okc_k_lines_v.end_date%TYPE
569 ,k_item_id okc_k_items.id%TYPE
570 ,object_id1 okc_k_items.object1_id1%TYPE
571 ,object_id2 okc_k_items.object1_id2%TYPE
572 --
573 ,line_style okc_line_styles_b.lse_type%TYPE
574 ,line_type okc_line_styles_b.lty_code%TYPE
575 ,line_source_code okc_line_style_sources.jtot_object_code%TYPE
576 ,line_source_table jtf_objects_b.from_table%TYPE
577 --
578 ,item_source_code okc_k_items.jtot_object1_code%TYPE
579 ,item_source_table jtf_objects_b.from_table%TYPE
580 );
581
582 TYPE line_info_tab_typ IS TABLE OF line_info_rec_typ INDEX BY BINARY_INTEGER;
583 TYPE line_info_ren_typ_dnr IS TABLE OF okc_k_lines_b.id%TYPE INDEX BY BINARY_INTEGER;
584
585
586 TYPE covlvl_info_rec_typ IS RECORD (
587 line_tab_idx binary_integer
588 ,line_id okc_k_lines_b.id%TYPE
589 ,line_number okc_k_lines_b.line_number%TYPE
590 ,status_code okc_statuses_b.ste_code%TYPE
591 --
592 ,qty okc_k_items.number_of_items%TYPE
593 ,uom_code okc_k_items.uom_code%TYPE
594 ,priced_item_yn okc_k_items.priced_item_yn%TYPE
595 ,price_unit okc_k_lines_b.price_unit%TYPE
596 ,price_negotiated okc_k_lines_b.price_negotiated%TYPE
597 ,currency_code okc_k_lines_b.currency_code%TYPE
598 --
599 ,start_date okc_k_lines_b.start_date%TYPE
600 ,end_date okc_k_lines_b.end_date%TYPE
601 --
602 ,line_style okc_line_styles_b.lse_type%TYPE
603 ,line_type okc_line_styles_b.lty_code%TYPE
604 ,line_source_code jtf_objects_b.object_code%TYPE
605 ,line_source_table jtf_objects_b.from_table%TYPE
606 --
607 ,id1 okc_k_items.object1_id1%TYPE
608 ,id2 okc_k_items.object1_id2%TYPE
609 ,prod_name okx_system_items_v.name%TYPE
610 ,item_source_code jtf_objects_b.object_code%TYPE
611 ,item_source_table jtf_objects_b.from_table%TYPE
612 --
613 ,svc_duration okx_quote_line_detail_v.service_duration%TYPE
614 ,svc_period okx_quote_line_detail_v.service_period%TYPE
615 );
616
617 TYPE covlvl_info_tab_typ IS TABLE OF covlvl_info_rec_typ INDEX BY BINARY_INTEGER;
618
619 --
620 -- type declaration for table to hold the list of line types that
621 -- can become lines on a quote or an order with detail lines (covered lines)
622 --
623 TYPE line_with_cover_prod_tab_type IS TABLE OF FND_LOOKUP_VALUES.LOOKUP_CODE%TYPE INDEX BY BINARY_INTEGER;
624
625 --
626 -- table to hold the rules at a contract header level
627 --
628 TYPE rule_rec_typ IS RECORD
629 (
630 chr_id okc_k_headers_b.id%TYPE,
631 cle_id okc_k_lines_b.id%TYPE,
632 object1_id1 okc_rules_b.object1_id1%TYPE
633 ,object1_id2 okc_rules_b.object1_id2%TYPE
634 ,jtot_object1_code okc_rules_b.jtot_object1_code%TYPE
635 ,object2_id1 okc_rules_b.object2_id1%TYPE
636 ,object2_id2 okc_rules_b.object2_id2%TYPE
637 ,jtot_object2_code okc_rules_b.jtot_object2_code%TYPE
638 ,object3_id1 okc_rules_b.object3_id1%TYPE
639 ,object3_id2 okc_rules_b.object3_id2%TYPE
640 ,jtot_object3_code okc_rules_b.jtot_object3_code%TYPE
641 ,rule_information_category okc_rules_b.rule_information_category%TYPE
642 );
643
644 TYPE rule_tbl_typ IS TABLE OF rule_rec_typ INDEX BY BINARY_INTEGER;
645
646 l_kh_rule_tab rule_tbl_typ; -- Renamed from l_rule_tab (header level rules)
647 l_kl_rule_tab rule_tbl_typ; -- added (line level rules)
648
649
650 TYPE bto_sto_rec_typ IS RECORD
651 (
652 chr_id okc_k_headers_b.id%TYPE,
653 cle_id okc_k_lines_b.id%TYPE,
654 --
655 party_site_id okx_cust_site_uses_v.party_site_id%TYPE,
656 cust_acct_id okx_cust_site_uses_v.cust_account_id%TYPE,
657 party_id okx_cust_site_uses_v.party_id%TYPE,
658 address1 okx_cust_site_uses_v.address1%TYPE,
659 address2 okx_cust_site_uses_v.address2%TYPE,
660 address3 okx_cust_site_uses_v.address3%TYPE,
661 address4 okx_cust_site_uses_v.address4%TYPE,
662 city okx_cust_site_uses_v.city%TYPE,
663 postal_code okx_cust_site_uses_v.postal_code%TYPE,
664 state okx_cust_site_uses_v.state%TYPE,
665 province okx_cust_site_uses_v.province%TYPE,
666 county okx_cust_site_uses_v.county%TYPE,
667 country okx_cust_site_uses_v.country%TYPE);
668
669
670 TYPE l_k_bto_sto_data_tab_typ IS TABLE OF bto_sto_rec_typ INDEX BY BINARY_INTEGER;
671
672
673 -- Tables to hold bill to and ship to information at the header level
674
675 l_kh_bto_data_tab l_k_bto_sto_data_tab_typ;
676 l_kh_sto_data_tab l_k_bto_sto_data_tab_typ;
677
678
679 -- Tables to hold bill to and ship to information at the line level
680
681 l_kl_bto_data_tab l_k_bto_sto_data_tab_typ;
682 l_kl_sto_data_tab l_k_bto_sto_data_tab_typ;
683
684 --
685 -- global variables
686 --
687 l_chr c_chr%ROWTYPE;
688 l_k_nbr VARCHAR2(2000); -- contract number plus modifier
689
690 l_qhr c_qhr%ROWTYPE;
691 l_q_nbr VARCHAR2(2000); -- Quote Number with version
692
693 --
694 l_line_with_cover_prod_qc_tab line_with_cover_prod_tab_type;
695 l_line_info_ren_typ_dnr line_info_ren_typ_dnr;
696
697
698 l_line_info_tab line_info_tab_typ;
699 l_covlvl_info_tab covlvl_info_tab_typ;
700
701 l_ktq_flag VARCHAR2(1) ;
702
703 p_rel_code okc_k_rel_objs.rty_code%TYPE ;
704
705 --
706 l_cust c_cust%ROWTYPE;
707 l_customer c_cust%ROWTYPE;
708 l_st_cust_acct_id okx_cust_site_uses_v.cust_account_id%TYPE; -- cust acct id holder
709 l_st_party_site_id okx_cust_site_uses_v.party_site_id%TYPE; -- bill to site id holder
710 l_st_party_id okx_cust_site_uses_v.party_id%TYPE; -- bill to party id holder
711 l_st_address1 okx_cust_site_uses_v.address1%TYPE; -- address
712 l_st_address2 okx_cust_site_uses_v.address2%TYPE; -- address
713 l_st_address3 okx_cust_site_uses_v.address3%TYPE; -- address
714 l_st_address4 okx_cust_site_uses_v.address4%TYPE; -- address
715 l_st_city okx_cust_site_uses_v.city%TYPE; -- city
716 l_st_postal_code okx_cust_site_uses_v.postal_code%TYPE; -- postal_code
717 l_st_state okx_cust_site_uses_v.state%TYPE; -- state
718 l_st_province okx_cust_site_uses_v.province%TYPE; -- province
719 l_st_county okx_cust_site_uses_v.county%TYPE; -- county
720 l_st_country okx_cust_site_uses_v.country%TYPE; -- country
721 l_bt_cust_acct_id okx_cust_site_uses_v.cust_account_id%TYPE; -- cust acct id holder
722 l_bt_party_site_id okx_cust_site_uses_v.party_site_id%TYPE; -- bill to site id holder
723 l_bt_party_id okx_cust_site_uses_v.party_id%TYPE; -- bill to party id holder
724 l_exchange_type okc_conversion_attribs_v.conversion_type%TYPE;
725 l_exchange_rate okc_conversion_attribs_v.conversion_rate%TYPE;
726 l_exchange_date okc_conversion_attribs_v.conversion_date%TYPE;
727
728
729 PROCEDURE create_quote_from_k( p_api_version IN NUMBER
730 ,p_init_msg_list IN VARCHAR2
731 ,x_return_status OUT NOCOPY VARCHAR2
732 ,x_msg_count OUT NOCOPY NUMBER
733 ,x_msg_data OUT NOCOPY VARCHAR2
734 --
735 ,p_contract_id IN okc_k_headers_b.ID%TYPE
736 ,p_rel_type IN okc_k_rel_objs.rty_code%TYPE
737 --
738 ,p_trace_mode IN VARCHAR2
739 ,x_quote_id OUT NOCOPY okx_quote_headers_v.id1%TYPE
740 )
741 IS
742 BEGIN
743
744 NULL;
745
746 END create_quote_from_k;
747
748
749
750 -----------------------------------------------------------------------------
751 -- Procedure: print_error
752 -- Returns:
753 -- Purpose: Print the last error which occured
754 -- In Parameters: pos position on the line to print the message
755 -- Out Parameters:
756
757 PROCEDURE print_error(pos IN NUMBER) IS
758 x_msg_count NUMBER;
759 x_msg_data VARCHAR2(1000);
760 BEGIN
761 IF okc_util.l_trace_flag OR okc_util.l_log_flag THEN
762 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
763 p_data => x_msg_data
764 );
765 IF (l_debug = 'Y') THEN
766 okc_util.print_trace(pos, '==EXCEPTION=================');
767 END IF;
768 x_msg_data := fnd_msg_pub.get( p_msg_index => x_msg_count,
769 p_encoded => 'F'
770 );
771 IF (l_debug = 'Y') THEN
772 okc_util.print_trace(pos, 'Message : '||x_msg_data);
773 okc_util.print_trace(pos, '============================');
774 END IF;
775 END IF;
776 END print_error;
777
778 ------------------------------------------------------------------------------------
779 -- Procedure: build_k_rules
780 -- Purpose: Builds the header and topline rules by ensuring that
781 -- the occurance of any rule doesnot happen more
782 -- than once per top line.
783 --
784 -- In Parameters: p_chr_id Contract_header id
785 -- p_cle_id topline id
786 --
787 -- Out Parameters: x_return_status Return status of the rules check
788 -- x_rule_tab Table of rule info except shipto and billto
789 -- x_bto_data_tab Table of billto rule info
790 -- x_sto_data_tab Table of shipto rule info
791 --
792 PROCEDURE build_k_rules( p_chr_id IN okc_k_headers_b.ID%TYPE,
793 p_cle_id IN okc_k_lines_v.id%TYPE,
794 x_rule_tab OUT NOCOPY rule_tbl_typ,
795 x_bto_data_rec OUT NOCOPY bto_sto_rec_typ,
796 x_sto_data_rec OUT NOCOPY bto_sto_rec_typ,
797 x_return_status OUT NOCOPY VARCHAR2 ) IS
798
799 -- get party site id for a customer account site id
800 --
801 CURSOR c_party_site (b_id1 VARCHAR2, b_id2 VARCHAR2) IS
802 SELECT
803 party_site_id
804 ,cust_account_id
805 ,party_id
806 ,address1
807 ,address2
808 ,address3
809 ,address4
810 ,city
811 ,state
812 ,province
813 ,postal_code
814 ,county
815 ,country
816 FROM okx_cust_site_uses_v
817 WHERE id1 = b_id1
818 AND id2 = b_id2;
819
820 --
821 l_party_site c_party_site%ROWTYPE;
822
823 -- get exchange information
824 --
825 CURSOR c_conv_type (b_id1 VARCHAR2, b_id2 VARCHAR2) IS
826 SELECT conversion_type,
827 conversion_rate,
828 conversion_date
829 FROM okc_conversion_attribs_v
830 WHERE conversion_type = b_id1
831 AND dnz_chr_id = p_chr_id;
832
833 e_exit EXCEPTION;
834 l_rd_nb NUMBER;
835 l_rd_custacct_nb NUMBER;
836 l_rd_price_nb NUMBER;
837 l_rd_invrule_nb NUMBER;
838 l_rd_billto_nb NUMBER;
839 l_rd_shipto_nb NUMBER;
840 l_rd_shipmtd_nb NUMBER;
841 l_rd_convert_nb NUMBER;
842
843 l_lines NUMBER;
844 l_idx INTEGER;
845
846 l_sto_data_rec bto_sto_rec_typ;
847 l_bto_data_rec bto_sto_rec_typ;
848 l_k_rule_tab rule_tbl_typ;
849
850 BEGIN
851
852 IF (l_debug = 'Y') THEN
853 okc_util.print_trace(3, ' ');
854 okc_util.print_trace(3, '------------------- ');
855 okc_util.print_trace(3, 'START BUILD_K_RULES');
856 okc_util.print_trace(3, '------------------- ');
857 okc_util.print_trace(3, ' ');
858 END IF;
859
860 l_sto_data_rec := NULL;
861 l_bto_data_rec := NULL;
862 l_k_rule_tab.delete;
863
864 l_st_party_id := null;
865 l_st_party_site_id := null;
866 l_st_cust_acct_id := null;
867 l_bt_party_id := null;
868 l_bt_party_site_id := null;
869 l_bt_cust_acct_id := null;
870
871
872 IF (l_debug = 'Y') THEN
873 okc_util.print_trace(3, ' ');
874 okc_util.print_trace(3, 'Rules:');
875 okc_util.print_trace(3, '=======');
876 END IF;
877 l_idx := 0;
878 l_rd_nb := 0;
879 l_rd_custacct_nb := 0;
880 l_rd_price_nb := 0;
881 l_rd_invrule_nb := 0;
882 l_rd_billto_nb := 0;
883 l_rd_shipto_nb := 0;
884 l_rd_shipmtd_nb := 0;
885 IF (l_debug = 'Y') THEN
886 okc_util.print_trace(3, '-->Rule selection');
887 END IF;
888
889 FOR r_rule IN c_rules(p_chr_id, p_cle_id, l_cust.id) LOOP
890 IF p_cle_id IS NULL THEN
891 IF r_rule.rule_information_category = g_rd_custacct THEN
892 IF l_rd_custacct_nb = 1 THEN
893 OKC_API.set_message(p_app_name => g_app_name1,
894 p_msg_name => 'OKO_K2Q_RULEOCC',
895 p_token1 => 'RULE',
896 p_token1_value => g_rd_custacct,
897 p_token2 => 'KNUMBER',
898 p_token2_value => l_k_nbr);
899 x_return_status := OKC_API.G_RET_STS_ERROR;
900 print_error(4);
901 RAISE e_exit;
902 ELSE
903 l_rd_custacct_nb := l_rd_custacct_nb + 1;
904 l_rd_nb:=l_rd_nb+1;
905 IF (l_debug = 'Y') THEN
906 okc_util.print_trace(4, '-->Rule selected: '||g_rd_custacct);
907 END IF;
908 END IF;
909 IF (l_debug = 'Y') THEN
910 okc_util.print_trace(5, ' Cust Acct Id: '||r_rule.object1_id1);
911 END IF;
912 END IF;
913 END IF;
914
915 IF r_rule.rule_information_category = g_rd_price THEN
916 IF l_rd_price_nb = 1 THEN
917 OKC_API.set_message(p_app_name => g_app_name1,
918 p_msg_name => 'OKO_K2Q_RULEOCC',
919 p_token1 => 'RULE',
920 p_token1_value => g_rd_price,
921 p_token2 => 'KNUMBER',
922 p_token2_value => l_k_nbr);
923 x_return_status := OKC_API.G_RET_STS_ERROR;
924 print_error(4);
925 RAISE e_exit;
926 ELSE
927 l_rd_price_nb := l_rd_price_nb + 1;
928 l_rd_nb:=l_rd_nb+1;
929 IF (l_debug = 'Y') THEN
930 okc_util.print_trace(4, '-->Rule selected: '||g_rd_price);
931 END IF;
932 END IF;
933 IF (l_debug = 'Y') THEN
934 okc_util.print_trace(5, ' Price List Id: '||r_rule.object1_id1);
935 END IF;
936 END IF;
937
938
939 IF r_rule.rule_information_category = g_rd_invrule THEN
940 IF l_rd_invrule_nb = 1 THEN
941 OKC_API.set_message(p_app_name => g_app_name1,
942 p_msg_name => 'OKO_K2Q_RULEOCC',
943 p_token1 => 'RULE',
944 p_token1_value => g_rd_invrule,
945 p_token2 => 'KNUMBER',
946 p_token2_value => l_k_nbr);
947 x_return_status := OKC_API.G_RET_STS_ERROR;
948 print_error(4);
949 RAISE e_exit;
950 ELSE
951 l_rd_invrule_nb := l_rd_invrule_nb + 1;
952 l_rd_nb:=l_rd_nb+1;
953 IF (l_debug = 'Y') THEN
954 okc_util.print_trace(4, '-->Rule selected: '||g_rd_invrule);
955 END IF;
956 END IF;
957 IF (l_debug = 'Y') THEN
958 okc_util.print_trace(5, ' Inv Rule Id: '||r_rule.object1_id1);
959 END IF;
960 END IF;
961
962 IF p_cle_id IS NULL THEN
963 IF r_rule.rule_information_category = g_rd_convert THEN
964 IF l_rd_convert_nb = 1 THEN
965 OKC_API.set_message(p_app_name => g_app_name1,
966 p_msg_name => 'OKO_K2Q_RULEOCC',
967 p_token1 => 'RULE',
968 p_token1_value => g_rd_convert,
969 p_token2 => 'KNUMBER',
970 p_token2_value => l_k_nbr);
971 x_return_status := OKC_API.G_RET_STS_ERROR;
972 print_error(4);
973 RAISE e_exit;
974 ELSE
975 OPEN c_conv_type(r_rule.object1_id1, r_rule.object1_id2);
976 FETCH c_conv_type INTO l_exchange_type,
977 l_exchange_rate,
978 l_exchange_date;
979 CLOSE c_conv_type;
980
981 IF (l_debug = 'Y') THEN
982 okc_util.print_trace(4, '-->Rule selected: '||g_rd_convert);
983 okc_util.print_trace(5, ' Exchange type code = '||l_exchange_type);
984 okc_util.print_trace(5, ' Exchange rate = '||l_exchange_rate);
985 okc_util.print_trace(5, ' Exchange rate date = '||l_exchange_date);
986 END IF;
987 l_rd_convert_nb := l_rd_convert_nb + 1;
988 l_rd_nb:=l_rd_nb+1;
989 END IF;
990 END IF;
991 END IF;
992
993 IF r_rule.rule_information_category = g_rd_shipto THEN
994 IF l_rd_shipto_nb = 1 THEN
995 OKC_API.set_message(p_app_name => g_app_name1,
996 p_msg_name => 'OKO_K2Q_RULEOCC',
997 p_token1 => 'RULE',
998 p_token1_value => g_rd_shipto,
999 p_token2 => 'KNUMBER',
1000 p_token2_value => l_k_nbr);
1001 x_return_status := OKC_API.G_RET_STS_ERROR;
1002 print_error(4);
1003 RAISE e_exit;
1004 ELSE
1005 OPEN c_party_site(r_rule.object1_id1, r_rule.object1_id2);
1006 FETCH c_party_site INTO l_party_site;
1007
1008 IF c_party_site%FOUND THEN
1009
1010 l_sto_data_rec.chr_id := p_chr_id;
1011 l_sto_data_rec.party_site_id := l_party_site.party_site_id;
1012 l_sto_data_rec.cust_acct_id := l_party_site.cust_account_id;
1013 l_sto_data_rec.party_id := l_party_site.party_id;
1014 l_sto_data_rec.address1 := l_party_site.address1;
1015 l_sto_data_rec.address2 := l_party_site.address2;
1016 l_sto_data_rec.address3 := l_party_site.address3;
1017 l_sto_data_rec.address4 := l_party_site.address4;
1018 l_sto_data_rec.city := l_party_site.city;
1019 l_sto_data_rec.state := l_party_site.state;
1020 l_sto_data_rec.province := l_party_site.province;
1021 l_sto_data_rec.postal_code := l_party_site.postal_code;
1022 l_sto_data_rec.county := l_party_site.county;
1023 l_sto_data_rec.country := l_party_site.country;
1024
1025 l_st_party_site_id := l_party_site.party_site_id;
1026 l_st_cust_acct_id := l_party_site.cust_account_id;
1027 l_st_party_id := l_party_site.party_id;
1028
1029 IF p_cle_id IS NULL THEN -- Header level rule info
1030 l_sto_data_rec.cle_id := null;
1031 ELSE -- Line level rule info
1032 l_sto_data_rec.cle_id := p_cle_id;
1033 END IF;
1034
1035 END IF;
1036
1037 CLOSE c_party_site;
1038
1039 IF (l_debug = 'Y') THEN
1040 okc_util.print_trace(4, '-->Rule selected: '||g_rd_shipto);
1041 okc_util.print_trace(5, ' Party_site_id (STO) = '||l_st_party_site_id);
1042 okc_util.print_trace(5, ' Cust Acct Id (STO) = '||l_st_cust_acct_id);
1043 okc_util.print_trace(5, ' Party Id (STO) = '||l_st_party_id);
1044 END IF;
1045 l_rd_shipto_nb := l_rd_shipto_nb + 1;
1046 l_rd_nb:=l_rd_nb+1;
1047 END IF;
1048 END IF;
1049
1050 IF r_rule.rule_information_category = g_rd_shipmtd THEN
1051 IF l_rd_shipmtd_nb = 1 THEN
1052 OKC_API.set_message(p_app_name => g_app_name1,
1053 p_msg_name => 'OKO_K2Q_RULEOCC',
1054 p_token1 => 'RULE',
1055 p_token1_value => g_rd_shipmtd,
1056 p_token2 => 'KNUMBER',
1057 p_token2_value => l_k_nbr);
1058 x_return_status := OKC_API.G_RET_STS_ERROR;
1059 print_error(4);
1060 RAISE e_exit;
1061 ELSE
1062 l_rd_shipmtd_nb := l_rd_shipmtd_nb + 1;
1063 l_rd_nb:=l_rd_nb+1;
1064 END IF;
1065 END IF;
1066
1067 IF r_rule.rule_information_category = g_rd_billto THEN
1068 IF l_rd_billto_nb = 1 THEN
1069 OKC_API.set_message(p_app_name => g_app_name1,
1070 p_msg_name => 'OKO_K2Q_RULEOCC',
1071 p_token1 => 'RULE',
1072 p_token1_value => g_rd_billto,
1073 p_token2 => 'KNUMBER',
1074 p_token2_value => l_k_nbr);
1075 x_return_status := OKC_API.G_RET_STS_ERROR;
1076 print_error(4);
1077 RAISE e_exit;
1078 ELSE
1079 -- need to fix bill to, since ASO wants the party site, not customer acct site
1080 OPEN c_party_site(r_rule.object1_id1, r_rule.object1_id2);
1081 FETCH c_party_site INTO l_party_site;
1082
1083 IF c_party_site%FOUND THEN
1084 l_bto_data_rec.chr_id := p_chr_id;
1085 l_bto_data_rec.party_site_id := l_party_site.party_site_id;
1086 l_bto_data_rec.cust_acct_id := l_party_site.cust_account_id;
1087 -- l_bto_data_rec.party_id := l_party_site.party_id;
1088
1089 l_bt_party_site_id := l_party_site.party_site_id;
1090 l_bt_cust_acct_id := l_party_site.cust_account_id;
1091 -- l_bt_party_id := l_party_site.party_id;
1092
1093 IF p_cle_id IS NULL THEN -- Header level rule info
1094 l_bto_data_rec.cle_id := null;
1095 ELSE
1096 l_bto_data_rec.cle_id := p_cle_id;
1097 END IF;
1098 END IF;
1099
1100 CLOSE c_party_site;
1101
1102 IF (l_debug = 'Y') THEN
1103 okc_util.print_trace(4, '-->Rule selected: '||g_rd_billto);
1104 okc_util.print_trace(5, ' Party_site_id (BTO) = '||l_bt_party_site_id);
1105 okc_util.print_trace(5, ' Cust Acct Id (BTO) = '||l_bt_cust_acct_id);
1106 okc_util.print_trace(5, ' Party Id (BTO) = '||l_bt_party_id);
1107 END IF;
1108 l_rd_billto_nb := l_rd_billto_nb + 1;
1109 l_rd_nb:=l_rd_nb+1;
1110 END IF;
1111 END IF;
1112
1113 l_idx := l_idx + 1;
1114 l_k_rule_tab(l_idx) := r_rule;
1115
1116 END LOOP;
1117
1118 x_sto_data_rec := l_sto_data_rec;
1119 x_bto_data_rec := l_bto_data_rec;
1120 x_rule_tab := l_k_rule_tab;
1121
1122 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1123
1124 IF (l_debug = 'Y') THEN
1125 okc_util.print_trace(3, '-->Rule selection: '||l_rd_nb||' rule(s) selected');
1126 okc_util.print_trace(3, '-->Return status '||x_return_status );
1127 END IF;
1128
1129 IF (l_debug = 'Y') THEN
1130 okc_util.print_trace(3, ' ');
1131 okc_util.print_trace(3, '------------------- ');
1132 okc_util.print_trace(3, ' END BUILD_K_RULES');
1133 okc_util.print_trace(3, '------------------- ');
1134 okc_util.print_trace(3, ' ');
1135 END IF;
1136 EXCEPTION
1137 WHEN e_exit THEN
1138 IF c_party_site%ISOPEN THEN
1139 CLOSE c_party_site;
1140 END IF;
1141
1142 IF c_conv_type%ISOPEN THEN
1143 CLOSE c_conv_type;
1144 END IF;
1145
1146 END build_k_rules;
1147
1148 -------------------------------------------------------------------------------
1149 -- Function: is_top_line_style_seeded
1150 -- Returns: Boolean
1151 -- Purpose: Determines if the contract top line is seeded
1152 -- To do so, it looks at the l_line_style_tab
1153 -- compares it to the line style id that is passed
1154 -- and returns the boolean value along with the index
1155 -- value of the l_line_style_tab
1156 -- In Parameters: p_lse_id record of line information
1157 -- Out Parameters: x_index Index value of the l_line_style_tab rec that
1158 -- matches the line style id that is passed
1159
1160 FUNCTION is_top_line_style_seeded(p_lse_id IN okc_line_styles_b.id%TYPE,
1161 x_index OUT NOCOPY NUMBER) RETURN VARCHAR IS
1162 l_retval VARCHAR2(1):= OKC_API.G_FALSE;
1163
1164 BEGIN
1165
1166 IF l_line_style_tab.first IS NOT NULL THEN
1167 FOR i in l_line_style_tab.first..l_line_style_tab.last LOOP
1168 IF l_line_style_tab(i).lse_id = p_lse_id THEN
1169 x_index := i;
1170 l_retval := OKC_API.G_TRUE;
1171 EXIT;
1172 END IF;
1173 END LOOP;
1174 END IF;
1175 RETURN l_retval;
1176 END is_top_line_style_seeded;
1177
1178 -------------------------------------------------------------------------------
1179 -- Function: is_top_line_with_covered_prod
1180 -- Returns: Boolean
1181 -- Purpose: Determines IF the contract line can be translated
1182 -- to a quote or an order line with detail lines.
1183 -- To do so it checks the l_line_style_tab having an
1184 -- object_code of OKX_SERVICE
1185 -- In Parameters: p_lse_id
1186 -- Out Parameters: p_index
1187
1188 FUNCTION is_top_line_with_covered_prod(p_lse_id IN okc_line_styles_b.id%TYPE,
1189 p_index IN NUMBER DEFAULT OKC_API.G_MISS_NUM )
1190 RETURN VARCHAR IS
1191
1192 l_retval VARCHAR2(1):= OKC_API.G_FALSE;
1193 BEGIN
1194 -- IF found, set return value to true AND exit loop
1195 -- IF not found, default value of return value is false
1196
1197 IF NVL(p_index,OKC_API.G_MISS_NUM) <> OKC_API.G_MISS_NUM THEN
1198 IF l_line_style_tab(p_index).object_code = g_okx_service THEN
1199 l_retval := OKC_API.G_TRUE;
1200 END IF;
1201 ELSE
1202 IF l_line_style_tab.first IS NOT NULL THEN
1203 FOR i IN l_line_style_tab.first..l_line_style_tab.last LOOP
1204 IF l_line_style_tab(i).lse_id = p_lse_id AND
1205 l_line_style_tab(i).object_code = g_okx_service THEN
1206 l_retval := OKC_API.G_TRUE;
1207 EXIT;
1208 END IF;
1209 END LOOP;
1210 END IF;
1211 END IF;
1212 RETURN l_retval;
1213 END is_top_line_with_covered_prod;
1214
1215 -------------------------------------------------------------------------------
1216 -- Function: is_top_line_orderable_i
1217 -- Returns: Boolean
1218 -- Purpose: Determines if the contract top line can
1219 -- be translated to a quote or an order line,
1220 -- by checking whether it is an orderable item
1221 -- In Parameters: p_cust_ord_enabled_flag
1222 -- p_lse_id
1223 -- p_index
1224 --
1225
1226 FUNCTION is_top_line_orderable_i(p_cust_ord_enabled_flag IN VARCHAR2,
1227 p_lse_id IN okc_line_styles_b.id%TYPE,
1228 p_index IN NUMBER
1229 ) RETURN VARCHAR IS
1230
1231 l_retval VARCHAR2(1) := OKC_API.G_TRUE;
1232 i NUMBER;
1233
1234 BEGIN
1235
1236 i := p_index;
1237
1238
1239 IF NVL(p_index,OKC_API.G_MISS_NUM) <> OKC_API.G_MISS_NUM THEN
1240 IF NOT( p_cust_ord_enabled_flag = 'Y' AND l_line_style_tab(i).priced_yn = 'Y'
1241 AND l_line_style_tab(i).item_to_price_yn = 'Y') THEN
1242
1243 /* OKC_API.set_message(p_app_name => g_app_name1,
1244 p_msg_name => 'OKO_K2Q_TLNOTORDBLITM',
1245 p_token1 => 'CONTRACTNUM',
1246 p_token1_value => l_k_nbr,
1247 p_token2 => 'LINESTYLEID',
1248 p_token2_value => p_lse_id); */
1249 l_retval := OKC_API.G_FALSE;
1250 END IF;
1251 ELSE
1252 FOR i IN l_line_style_tab.first..l_line_style_tab.last LOOP
1253 IF l_line_style_tab(i).lse_id = p_lse_id THEN
1254 IF NOT ( p_cust_ord_enabled_flag = 'Y' AND l_line_style_tab(i).priced_yn = 'Y'
1255 AND l_line_style_tab(i).item_to_price_yn = 'Y') THEN
1256
1257 /* OKC_API.set_message(p_app_name => g_app_name1,
1258 p_msg_name => 'OKO_K2Q_TLNOTORDBLITM',
1259 p_token1 => 'CONTRACTNUM',
1260 p_token1_value => l_k_nbr,
1261 p_token2 => 'LINESTYLEID',
1262 p_token2_value => p_lse_id); */
1263
1264 l_retval := OKC_API.G_FALSE;
1265 EXIT;
1266 END IF;
1267 END IF;
1268 END LOOP;
1269 END IF;
1270 RETURN l_retval;
1271 END is_top_line_orderable_i;
1272
1273 -------------------------------------------------------------------------------
1274 -- Function: is_kl_linked_to_ql
1275 -- Returns: Boolean
1276 -- Purpose: Determines if the contract line is linked to quote line
1277 -- In Parameters: p_chr_id
1278 -- p_cle_id
1279 -- p_qh_id
1280 -- p_rlt_code
1281 -- p_rlt_type
1282 --
1283 --
1284 FUNCTION is_kl_linked_to_ql(p_chr_id IN NUMBER,
1285 p_cle_id IN okc_k_lines_b.id%TYPE,
1286 p_qh_id IN okx_quote_headers_v.id1%TYPE,
1287 p_rlt_code IN VARCHAR2,
1288 p_rlt_type IN VARCHAR2
1289 ) RETURN VARCHAR2 IS
1290
1291 l_retval VARCHAR2(1) := OKC_API.G_TRUE;
1292 l_object_id1 okc_k_rel_objs.object1_id1%TYPE;
1293
1294 BEGIN
1295 OPEN c_q_k_rel(p_chr_id,p_cle_id,p_qh_id,p_rlt_code,p_rlt_type);
1296 FETCH c_q_k_rel INTO l_object_id1;
1297 IF c_q_k_rel%NOTFOUND THEN
1298 l_retval := OKC_API.G_FALSE;
1299 END IF;
1300 CLOSE c_q_k_rel;
1301 RETURN l_retval;
1302 END is_kl_linked_to_ql;
1303
1304 -------------------------------------------------------------------------------
1305 -- Procedure: validate_k_eligibility
1306 -- Purpose: Check up on specific conditions to ensure the contract
1307 -- is elligible for a quote updation
1308 --
1309 -- In Parameters: p_k_header_rec contract information that has contract
1310 -- id and contract category
1311 --
1312 -- Out Parameters: x_return_status standard return status
1313
1314 PROCEDURE validate_k_eligibility( p_k_header_rec IN c_k_header%ROWTYPE
1315 ,p_quote_id IN okx_quote_headers_v.id1%TYPE
1316 ,x_return_status OUT NOCOPY VARCHAR2
1317 ) IS
1318
1319 --
1320 -- Cursor to select the Quote's expiration date
1321 --
1322
1323 CURSOR c_qh_expiration(b_qh_id NUMBER) IS
1324 SELECT quote_expiration_date
1325 FROM okx_quote_headers_v
1326 WHERE id1 = b_qh_id;
1327
1328 e_exit exception;
1329 e_exit2 exception;
1330 l_msg_count NUMBER := 0;
1331 l_msg_data VARCHAR2(1000);
1332 l_object_id1 okc_k_rel_objs.object1_id1%TYPE;
1333 l_quot_exp_date okx_quote_headers_v.quote_expiration_date%TYPE;
1334
1335
1336 BEGIN
1337
1338 IF (l_debug = 'Y') THEN
1339 OKC_UTIL.print_trace(1, ' ');
1340 OKC_UTIL.print_trace(1, '>START - OKC_OC_INT_KTQ_PVT.VALIDATE_K_ELIGIBILITY - Check up on specific contract conditions');
1341 END IF;
1342
1343 IF (l_debug = 'Y') THEN
1344 OKC_UTIL.print_trace(1, ' ');
1345 OKC_UTIL.print_trace(1, 'The input quote id = '||p_quote_id);
1346 OKC_UTIL.print_trace(1, ' ');
1347 OKC_UTIL.print_trace(1, 'First contract validations: common general conditions');
1348 OKC_UTIL.print_trace(1, '--------------------------------------------------------');
1349 OKC_UTIL.print_trace(2, 'Checking on : contract category is KFORQUOTE');
1350 END IF;
1351
1352 IF p_k_header_rec.scs_code = g_k_kfq_subclass THEN
1353 IF (l_debug = 'Y') THEN
1354 OKC_UTIL.print_trace(2,' ');
1355 OKC_UTIL.print_trace(2, 'Checking on : relationship between contract and quote for which the');
1356 OKC_UTIL.print_trace(2, ' code is CONTRACTISTERMSFORQUOTE and type is OKX_QUOTEHEAD');
1357 OKC_UTIL.print_trace(2, ' ');
1358 END IF;
1359
1360 OPEN c_q_k_rel(p_k_header_rec.id, null, p_quote_id, g_rlt_code_ktq, g_rlt_typ_qh);
1361 FETCH c_q_k_rel into l_object_id1;
1362 IF c_q_k_rel%NOTFOUND THEN
1363 CLOSE c_q_k_rel;
1364
1365 IF (l_debug = 'Y') THEN
1366 OKC_UTIL.print_trace(2,' ');
1367 OKC_UTIL.print_trace(2, 'Checking on : relationship between contract and quote for which the');
1368 OKC_UTIL.print_trace(2, ' code is CONTRACTNEGOTIATESQUOTE and type is OKX_QUOTEHEAD');
1369 OKC_UTIL.print_trace(2, ' ');
1370 END IF;
1371
1372 OPEN c_q_k_rel(p_k_header_rec.id, null, p_quote_id, g_rlt_code_knq, g_rlt_typ_qh);
1373 FETCH c_q_k_rel into l_object_id1;
1374 IF c_q_k_rel%NOTFOUND OR l_object_id1 IS NULL THEN
1375
1376 OKC_API.set_message(p_app_name => g_app_name1,
1377 p_msg_name => 'OKO_K2Q_NORELBQK',
1378 p_token1 => 'KNUMBER',
1379 p_token1_value => l_k_nbr,
1380 p_token2 => 'QNUMBER',
1381 p_token2_value => l_q_nbr);
1382
1383 x_return_status := OKC_API.G_RET_STS_ERROR;
1384 IF (l_debug = 'Y') THEN
1385 OKC_UTIL.print_trace(2, 'No relationship exists between the quote and the contract');
1386 END IF;
1387 print_error(3);
1388 RAISE e_exit;
1389 ELSE
1390 g_quote_id := l_object_id1;
1391 IF (l_debug = 'Y') THEN
1392 okc_util.print_trace(2,'The fetched quote id = '||g_quote_id);
1393 END IF;
1394 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1395 p_rel_code := g_rlt_code_knq;
1396 END IF;
1397 CLOSE c_q_k_rel;
1398 ELSE
1399 IF l_object_id1 IS NULL THEN
1400 OKC_API.set_message(p_app_name => g_app_name1,
1401 p_msg_name => 'OKO_K2Q_NORELBQK',
1402 p_token1 => 'KNUMBER',
1403 p_token1_value => l_k_nbr,
1404 p_token2 => 'QNUMBER',
1405 p_token2_value => l_q_nbr);
1406
1407 x_return_status := OKC_API.G_RET_STS_ERROR;
1408 IF (l_debug = 'Y') THEN
1409 OKC_UTIL.print_trace(2, '2.No relationship exists between the quote and the contract');
1410 END IF;
1411 print_error(3);
1412 RAISE e_exit;
1413 END IF;
1414 g_quote_id := l_object_id1;
1415 IF (l_debug = 'Y') THEN
1416 okc_util.print_trace(2,'2.The fetched quote id = '||g_quote_id);
1417 END IF;
1418 l_ktq_flag := OKC_API.G_TRUE;
1419 p_rel_code := g_rlt_code_ktq;
1420 END IF;
1421 ELSE
1422 OKC_API.set_message(p_app_name => g_app_name1,
1423 p_msg_name => 'OKO_K2Q_INVCAT');
1424
1425 x_return_status := OKC_API.G_RET_STS_ERROR;
1426 IF (l_debug = 'Y') THEN
1427 OKC_UTIL.print_trace(2, 'The contract category doesnot belong to KFORQUOTE');
1428 END IF;
1429 print_error(3);
1430 RAISE e_exit;
1431 END IF;
1432
1433
1434 /*
1435 -- Checking for the quote's expiration date
1436
1437 IF (l_debug = 'Y') THEN
1438 OKC_UTIL.print_trace(1, 'Second contract validations: common general conditions');
1439 OKC_UTIL.print_trace(1, '--------------------------------------------------------');
1440 OKC_UTIL.print_trace(2, 'Checking on : Quote''s expiration date');
1441 END IF;
1442
1443 OPEN c_qh_expiration(g_quote_id);
1444 FETCH c_qh_expiration INTO l_quot_exp_date;
1445 IF l_quot_exp_date IS NOT NULL AND TRUNC(SYSDATE) > TRUNC(l_quot_exp_date) THEN
1446
1447 OKC_API.set_message(p_app_name => g_app_name1,
1448 p_msg_name => 'OKO_K2Q_QDATEXP',
1449 p_token1 => 'QNUMBER',
1450 p_token1_value => l_q_nbr,
1451 p_token2 => 'QUOTEXPDATE',
1452 p_token2_value => g_k_kfq_subclass);
1453
1454 x_return_status := OKC_API.G_RET_STS_ERROR;
1455 IF (l_debug = 'Y') THEN
1456 OKC_UTIL.print_trace(2, 'The quote has already expired');
1457 END IF;
1458 print_error(3);
1459 RAISE e_exit;
1460 ELSE
1461 IF (l_debug = 'Y') THEN
1462 OKC_UTIL.print_trace(2, 'checked for the quote''s expr date - the quote is still valid');
1463 END IF;
1464 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1465 END IF;
1466 CLOSE c_qh_expiration;
1467
1468 */
1469
1470 EXCEPTION
1471 WHEN e_exit THEN
1472 IF c_q_k_rel%ISOPEN THEN
1473 CLOSE c_q_k_rel;
1474 END IF;
1475 WHEN OTHERS THEN
1476 IF c_q_k_rel%ISOPEN THEN
1477 CLOSE c_q_k_rel;
1478 END IF;
1479
1480 OKC_API.set_message(G_APP_NAME, -- set the err mesg on the stack to be retrieved
1481 G_UNEXPECTED_ERROR, -- by the calling routine
1482 G_SQLCODE_TOKEN,
1483 SQLCODE,
1484 G_SQLERRM_TOKEN,
1485 SQLERRM);
1486 -- notify caller of an UNEXPECTED error
1487 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1488
1489 END validate_k_eligibility;
1490
1491 -------------------------------------------------------------------------------
1492 -- Procedure: build_k_structures
1493 -- Purpose: Build several records/tables that hold information to be
1494 -- used to pass to ASO APIs
1495 -- In Parameters: p_chr_id contract id
1496 -- Out Parameters: x_return_status standard return status
1497
1498 PROCEDURE build_k_structures (p_chr_id IN okc_k_headers_b.ID%TYPE
1499 -- ,p_quote_id IN OKX_QUOTE_HEADERS_V.ID1%TYPE
1500 ,p_rel_code IN okc_k_rel_objs.rty_code%TYPE
1501 ,p_k_header_rec IN c_k_header%ROWTYPE
1502 ,x_return_status OUT NOCOPY VARCHAR2
1503 ) IS
1504
1505 CURSOR c_top_cle_init(b_chr_id NUMBER) IS
1506 SELECT cle.id line_id
1507 ,cle.line_number line_number
1508 FROM
1509 okc_k_lines_b cle
1510 WHERE EXISTS ( SELECT 1
1511 FROM okc_statuses_b sts
1512 WHERE sts.code = cle.sts_code
1513 AND sts.ste_code <> g_sts_terminated )
1514 AND (
1515 (cle.cle_id IS NULL AND cle.config_item_type NOT IN (g_okc_model_item,g_okc_base_item,g_okc_config_item))
1516 OR ( cle.config_item_type IN (g_okc_model_item,g_okc_base_item,g_okc_config_item))
1517 )
1518 AND cle.dnz_chr_id = b_chr_id
1519 ORDER BY cle.config_item_type DESC, -- To ensure that top model line,top base line
1520 line_id; -- and config are processed in order
1521
1522
1523 CURSOR c_top_cle(b_chr_id NUMBER,
1524 b_line_id NUMBER) IS
1525 SELECT
1526 -- b_line_level lv
1527 cle.id line_id
1528 ,cle.cle_id cle_id
1529 ,cle.lse_id lse_id
1530 ,cle.line_number line_number
1531 ,sts.ste_code
1532 --
1533 ,cim.number_of_items qty
1534 ,cim.uom_code
1535 ,'N' customer_order_enabled_flag
1536 ,cle.name item_name
1537 ,cim.priced_item_yn
1538 ,cle.price_unit
1539 ,cle.price_negotiated
1540 ,cle.line_list_price
1541 ,cle.price_list_id
1542 ,cle.price_list_line_id
1543 ,cle.currency_code
1544 --
1545 --
1546 ,cle.config_header_id
1547 ,cle.config_revision_number
1548 ,cle.config_complete_yn
1549 ,cle.config_valid_yn
1550 ,cle.config_item_id
1551 ,cle.config_item_type
1552 ,cfg.component_code
1553 --
1554 --
1555 ,cle.start_date
1556 ,cle.end_date
1557 ,cim.id cim_id
1558 ,cim.object1_id1
1559 ,cim.object1_id2
1560 --
1561 ,lse.lse_type line_style
1562 ,lse.lty_code line_type
1563 ,lss.jtot_object_code line_source_code
1564 ,jot2.from_table line_source_table
1565 --
1566 ,cim.jtot_object1_code item_source_code
1567 ,jot.from_table item_source_table
1568 FROM
1569 okc_k_lines_v cle,
1570 okc_k_items cim,
1571 okc_line_styles_b lse,
1572 okc_line_style_sources lss,
1573 jtf_objects_b jot,
1574 jtf_objects_b jot2,
1575 okc_statuses_b sts,
1576 -- okx_system_items_v sit,
1577 okx_config_items_v cfg
1578 WHERE
1579 cim.cle_id = cle.id
1580 AND jot.object_code(+) = cim.jtot_object1_code
1581 AND lse.id = cle.lse_id
1582 AND lss.lse_id(+) = lse.id
1583 AND lss.jtot_object_code = jot2.object_code(+)
1584 AND sts.code = cle.sts_code
1585 AND cle.dnz_chr_id = b_chr_id
1586 AND cle.id = b_line_id
1587 AND cfg.config_hdr_id(+) = cle.config_header_id
1588 AND cfg.config_rev_nbr(+) = cle.config_revision_number
1589 AND cfg.config_item_id(+) = cle.config_item_id;
1590
1591
1592 e_exit exception;
1593 l_idx binary_integer; -- generic table index
1594 l_cp_idx binary_integer; -- index for cust prod cov lvl table
1595 l_cp_ctr integer; -- NUMBER of cp lines per service line
1596 l_svc_duration okx_quote_line_detail_v.service_duration%TYPE;
1597 l_svc_period okx_quote_line_detail_v.service_period%TYPE;
1598 l_party NUMBER;
1599 l_lines NUMBER;
1600 l_nb_parties NUMBER;
1601 l_nb_roles NUMBER;
1602 l_legentity NUMBER;
1603 l_prev_rle_code okc_role_sources.rle_code%TYPE;
1604 l_err_nb NUMBER;
1605 l_sql VARCHAR2(2000);
1606 l_item_name VARCHAR2(150);
1607 l_customer_order_enabled_flag VARCHAR2(1);
1608 r_cle line_info_rec_typ;
1609 lx_return_status VARCHAR2(1);
1610 lx_index NUMBER;
1611 x_msg_count NUMBER;
1612 x_msg_data VARCHAR2(1000);
1613
1614
1615 lx_kh_rule_tab rule_tbl_typ;
1616 lx_kl_rule_tab rule_tbl_typ;
1617
1618 lx_kh_bto_data_rec bto_sto_rec_typ;
1619 lx_kh_sto_data_rec bto_sto_rec_typ;
1620
1621 lx_kl_bto_data_rec bto_sto_rec_typ;
1622 lx_kl_sto_data_rec bto_sto_rec_typ;
1623
1624
1625 BEGIN
1626
1627
1628 IF (l_debug = 'Y') THEN
1629 okc_util.print_trace(1, ' ');
1630 okc_util.print_trace(1, '>START - OKC_OC_INT_KTQ_PVT.BUILD_K_STRUCTURES - Get contract information');
1631 END IF;
1632
1633 --
1634 -- get contract header information
1635 -- already selected in STEP 1
1636 --
1637
1638 IF (l_debug = 'Y') THEN
1639 okc_util.print_trace(2, 'Contract Header:');
1640 okc_util.print_trace(2, '================');
1641 okc_util.print_trace(3, 'Org Id = '||l_chr.authoring_org_id);
1642 okc_util.print_trace(3, 'Inv Org Id = '||l_chr.inv_organization_id);
1643 okc_util.print_trace(3, 'Contract NUMBER = '||l_chr.contract_number);
1644 okc_util.print_trace(3, 'Contract modifier = '||l_chr.contract_number_modifier);
1645 okc_util.print_trace(3, 'Currency code = '||l_chr.currency_code);
1646 okc_util.print_trace(3, 'Estimated amount = '||LTRIM(TO_CHAR(l_chr.estimated_amount, '9G999G999G990D00')));
1647 END IF;
1648
1649 IF (l_debug = 'Y') THEN
1650 okc_util.print_trace(3, ' ');
1651 END IF;
1652 --
1653 -- get customer information
1654 --
1655
1656 IF (l_debug = 'Y') THEN
1657 okc_util.print_trace(3, 'Party:');
1658 okc_util.print_trace(3, '=======');
1659 END IF;
1660 l_nb_parties:=0;
1661 l_nb_roles:=0;
1662 l_legentity:=0;
1663 l_party:=0;
1664 l_prev_rle_code:=NULL;
1665 FOR l_cust IN c_cust(p_chr_id) LOOP
1666 l_nb_parties:=l_nb_parties+1;
1667 IF l_cust.rle_code <> l_prev_rle_code OR l_prev_rle_code IS NULL THEN
1668 l_prev_rle_code:=l_cust.rle_code;
1669 l_nb_roles:=l_nb_roles+1;
1670 END IF;
1671 IF l_cust.jtot_object1_code <> g_okx_legentity THEN
1672 l_party:=l_party+1;
1673 l_customer:=l_cust;
1674 IF (l_debug = 'Y') THEN
1675 okc_util.print_trace(3, 'Party code = '||l_cust.jtot_object1_code);
1676 okc_util.print_trace(3, 'Party_id1 = '||l_cust.object1_id1);
1677 okc_util.print_trace(3, 'Party_id2 = '||l_cust.object1_id2);
1678 END IF;
1679 ELSE
1680 l_legentity:=l_legentity+1;
1681 END IF;
1682 END LOOP;
1683
1684 IF l_nb_parties = 0 THEN
1685 OKC_API.set_message(p_app_name => g_app_name1,
1686 p_msg_name => 'OKO_K2Q_NOCUSTDEF',
1687 p_token1 => 'KNUMBER',
1688 p_token1_value => l_k_nbr);
1689 x_return_status := OKC_API.G_RET_STS_ERROR;
1690 print_error(4);
1691 RAISE e_exit;
1692 END IF;
1693 IF l_nb_parties > 2 THEN
1694 OKC_API.set_message(p_app_name => g_app_name1,
1695 p_msg_name => 'OKO_K2Q_PARNUMLIMT',
1696 p_token1 => 'KNUMBER',
1697 p_token1_value => l_k_nbr);
1698 x_return_status := OKC_API.G_RET_STS_ERROR;
1699 print_error(4);
1700 RAISE e_exit;
1701 END IF;
1702 IF l_nb_parties < 2 THEN
1703 OKC_API.set_message(p_app_name => g_app_name1,
1704 p_msg_name => 'OKO_K2Q_PARNUMLIMT2',
1705 p_token1 => 'KNUMBER',
1706 p_token1_value => l_k_nbr);
1707 x_return_status := OKC_API.G_RET_STS_ERROR;
1708 print_error(4);
1709 RAISE e_exit;
1710 END IF;
1711 IF l_nb_roles <> 2 THEN
1712 OKC_API.set_message(p_app_name => g_app_name1,
1713 p_msg_name => 'OKO_K2Q_ROLEDEFN',
1714 p_token1 => 'KNUMBER',
1715 p_token1_value => l_k_nbr);
1716 x_return_status := OKC_API.G_RET_STS_ERROR;
1717 print_error(4);
1718 RAISE e_exit;
1719 END IF;
1720 IF l_party <> 1 OR l_legentity <> 1 THEN
1721 OKC_API.set_message(p_app_name => g_app_name1,
1722 p_msg_name => 'OKO_K2Q_PTYTYPE_MIS',
1723 p_token1 => 'KNUMBER',
1724 p_token1_value => l_k_nbr);
1725 x_return_status := OKC_API.G_RET_STS_ERROR;
1726 print_error(4);
1727 RAISE e_exit;
1728 END IF;
1729 l_cust:=l_customer;
1730
1731 --
1732 -- make sure global variables are clear
1733 --
1734
1735 l_kh_rule_tab.delete;
1736 l_kl_rule_tab.delete;
1737
1738 l_kh_sto_data_tab.delete;
1739 l_kh_bto_data_tab.delete;
1740
1741 l_kl_sto_data_tab.delete;
1742 l_kl_bto_data_tab.delete;
1743
1744 l_line_info_tab.delete;
1745 l_covlvl_info_tab.delete;
1746
1747 --
1748 -- get header level rules related to this customer
1749 --
1750
1751 -- Make a call to the build_k_rules procedure to get
1752 -- the header level rules
1753 --
1754 IF (l_debug = 'Y') THEN
1755 okc_util.print_trace(3,'====================================');
1756 okc_util.print_trace(3,'Retrieving the rules at header level');
1757 okc_util.print_trace(3,'====================================');
1758 END IF;
1759
1760 build_k_rules ( p_chr_id => p_chr_id,
1761 p_cle_id => NULL,
1762 x_rule_tab => lx_kh_rule_tab,
1763 x_bto_data_rec => lx_kh_bto_data_rec,
1764 x_sto_data_rec => lx_kh_sto_data_rec,
1765 x_return_status => lx_return_status );
1766
1767
1768 IF lx_return_status = OKC_API.G_RET_STS_SUCCESS THEN
1769
1770 IF lx_kh_rule_tab.FIRST IS NOT NULL THEN
1771 l_kh_rule_tab := lx_kh_rule_tab;
1772 END IF;
1773
1774 IF lx_kh_bto_data_rec.chr_id IS NOT NULL THEN
1775 l_kh_bto_data_tab(l_kh_bto_data_tab.COUNT+1):= lx_kh_bto_data_rec;
1776 END IF;
1777
1778 IF lx_kh_sto_data_rec.chr_id IS NOT NULL THEN
1779 l_kh_sto_data_tab(l_kh_sto_data_tab.COUNT+1):= lx_kh_sto_data_rec;
1780 END IF;
1781
1782 ELSE
1783 raise e_exit;
1784 END IF;
1785
1786 IF (l_debug = 'Y') THEN
1787 okc_util.print_trace(3,'==============================================');
1788 okc_util.print_trace(3,'Completed retrieving the rules at header level');
1789 okc_util.print_trace(3,'==============================================');
1790 END IF;
1791
1792 --
1793 -- get all the top lines in detail
1794 --
1795
1796 -- Initialize pl/sql table l_line_style_tab with all the valid top line styles
1797
1798 l_line_style_tab.DELETE;
1799 l_idx := 1;
1800
1801 FOR r_line_styles_rec IN c_k_top_line_styles(p_k_header_rec.scs_code) LOOP
1802 l_line_style_tab(l_idx):= r_line_styles_rec;
1803 l_idx := l_idx + 1;
1804 END LOOP;
1805
1806 l_lines := 0;
1807 l_idx := 0;
1808 l_cp_idx := 0;
1809 IF (l_debug = 'Y') THEN
1810 okc_util.print_trace(2, '===================');
1811 okc_util.print_trace(2, 'Contract Top Lines:');
1812 okc_util.print_trace(2, '===================');
1813 END IF;
1814
1815 FOR r_cle_i IN c_top_cle_init(p_chr_id) LOOP
1816 IF (l_debug = 'Y') THEN
1817 okc_util.print_trace(2,'>>Select contract line');
1818 END IF;
1819
1820 OPEN c_top_cle(p_chr_id, r_cle_i.line_id);
1821 FETCH c_top_cle INTO r_cle;
1822
1823 IF c_top_cle%NOTFOUND THEN
1824 OKC_API.set_message(
1825 p_app_name => g_app_name1,
1826 p_msg_name => 'OKO_K2Q_LINENOTORDBL1',
1827 p_token1 => 'LINE_NUM',
1828 p_token1_value => r_cle_i.line_number,
1829 p_token2 => 'KNUMBER',
1830 p_token2_value => l_k_nbr);
1831 print_error(2);
1832 RAISE e_exit;
1833 END IF;
1834 --
1835 -- select item name
1836 -- If no item source code, the item name is provided by the contract line
1837 --
1838 l_item_name :=r_cle.item_name;
1839 l_customer_order_enabled_flag := r_cle.customer_order_enabled_flag;
1840
1841 IF (l_debug = 'Y') THEN
1842 okc_util.print_trace(2, '>>Select contract line product name');
1843 END IF;
1844
1845 IF rtrim(ltrim(r_cle.item_source_code)) IS NOT NULL THEN
1846 IF rtrim(ltrim(r_cle.item_source_table)) NOT LIKE
1847 g_okx_system_items_v||'%'||r_cle.item_source_code THEN
1848 BEGIN
1849 l_sql:= 'SELECT name FROM '||r_cle.item_source_table ||' WHERE id1 = :b AND id2 = :c';
1850 EXECUTE IMMEDIATE l_sql INTO l_item_name USING r_cle.object_id1, r_cle.object_id2;
1851 END;
1852 ELSE
1853 BEGIN
1854 l_sql := 'SELECT name, customer_order_enabled_flag FROM '||r_cle.item_source_table||
1855 ' WHERE id1 = :b AND id2 = :c';
1856 EXECUTE IMMEDIATE l_sql INTO l_item_name, l_customer_order_enabled_flag
1857 USING r_cle.object_id1,r_cle.object_id2;
1858 END;
1859 END IF;
1860 END IF;
1861 --
1862 l_lines:=l_lines+1;
1863 --
1864 --okc_util.print_trace(2,' ');
1865 IF (l_debug = 'Y') THEN
1866 okc_util.print_trace(2,'--------------');
1867 okc_util.print_trace(2,'> Line_id = '||r_cle.line_id);
1868 okc_util.print_trace(2,'Line NUMBER = '||r_cle.line_number);
1869 okc_util.print_trace(2,'--------------');
1870 okc_util.print_trace(2,'Line style = '||r_cle.line_style);
1871 okc_util.print_trace(2,'Line type = '||r_cle.line_type);
1872 okc_util.print_trace(2,'Line source code = '||r_cle.line_source_code);
1873 okc_util.print_trace(2,'Line source table = '||r_cle.line_source_table);
1874 okc_util.print_trace(2,'Item source code = '||r_cle.item_source_code);
1875 okc_util.print_trace(2,'Item source table = '||r_cle.item_source_table);
1876 okc_util.print_trace(2,'Item id1 = '||r_cle.object_id1);
1877 okc_util.print_trace(2,'Item id2 = '||r_cle.object_id2);
1878 END IF;
1879 --okc_util.print_trace(2,'Item name = '||r_cle.item_name);
1880 IF (l_debug = 'Y') THEN
1881 okc_util.print_trace(2,'Item name = '||l_item_name);
1882 END IF;
1883 --okc_util.print_trace(2,'Item Orderable = '||r_cle.customer_order_enabled_flag);
1884 IF (l_debug = 'Y') THEN
1885 okc_util.print_trace(2,'Item Orderable = '||l_customer_order_enabled_flag);
1886 okc_util.print_trace(2,'Item Priced = '||r_cle.priced_item_yn);
1887 okc_util.print_trace(2,'Quantity = '||r_cle.qty);
1888 okc_util.print_trace(2,'UOM = '||r_cle.uom_code);
1889 okc_util.print_trace(2,'Currency code = '||r_cle.currency_code);
1890 END IF;
1891 --okc_util.print_trace(2,'Negot. price = '||r_cle.price);
1892 IF (l_debug = 'Y') THEN
1893 okc_util.print_trace(2,'Unit price = '||LTRIM(TO_CHAR(r_cle.price_unit, '9G999G999G990D00')));
1894 okc_util.print_trace(2,'Start date = '||r_cle.start_date);
1895 okc_util.print_trace(2,'End date = '||r_cle.end_date);
1896 END IF;
1897
1898 IF (l_debug = 'Y') THEN
1899 okc_util.print_trace(2,'Config header id = '||r_cle.config_header_id);
1900 okc_util.print_trace(2,'config rev Num = '||r_cle.config_revision_number);
1901 okc_util.print_trace(2,'Config item id = '||r_cle.config_item_id);
1902 okc_util.print_trace(2,'Config complet yn = '||r_cle.config_complete_yn);
1903 okc_util.print_trace(2,'Config valid yn = '||r_cle.config_valid_yn);
1904 okc_util.print_trace(2,'Component code = '||r_cle.component_code);
1905 okc_util.print_trace(2,'Config item type = '||r_cle.end_date);
1906 END IF;
1907
1908
1909
1910 IF is_top_line_style_seeded(p_lse_id =>r_cle.lse_id,x_index =>lx_index) = OKC_API.G_TRUE THEN
1911
1912 IF is_top_line_with_covered_prod(r_cle.lse_id,lx_index) = OKC_API.G_TRUE THEN
1913
1914 IF is_top_line_orderable_i(l_customer_order_enabled_flag,r_cle.lse_id,lx_index) = OKC_API.G_TRUE THEN
1915
1916 l_idx := l_idx + 1;
1917
1918 l_line_info_tab(l_idx) := r_cle;
1919
1920 --
1921 -- At this point the line is a serviceable line, and no config_item_type is
1922 -- provided from the cursor (i.e the value is NULL ). Hence
1923 -- value of config_item_type is explicitly set to service
1924 --
1925 l_line_info_tab(l_idx).config_item_type := g_okc_service_item; -- 'SRV'
1926
1927 l_line_info_tab(l_idx).item_name := l_item_name;
1928 l_line_info_tab(l_idx).customer_order_enabled_flag := l_customer_order_enabled_flag;
1929
1930
1931
1932 IF (l_debug = 'Y') THEN
1933 okc_util.print_trace(2,'============================');
1934 okc_util.print_trace(2,'Contract line idx = '||l_idx);
1935 okc_util.print_trace(2,'============================');
1936 END IF;
1937
1938
1939 IF (l_debug = 'Y') THEN
1940 okc_util.print_trace(2,'--------------');
1941 okc_util.print_trace(2,'> Line_id = '||l_line_info_tab(l_idx).line_id);
1942 okc_util.print_trace(2,'Line NUMBER = '||l_line_info_tab(l_idx).line_number);
1943 okc_util.print_trace(2,'--------------');
1944 okc_util.print_trace(2,'Line style = '||l_line_info_tab(l_idx).line_style);
1945 okc_util.print_trace(2,'Line type = '||l_line_info_tab(l_idx).line_type);
1946 okc_util.print_trace(2,'Line source code = '||l_line_info_tab(l_idx).line_source_code);
1947 okc_util.print_trace(2,'Line source table = '||l_line_info_tab(l_idx).line_source_table);
1948 okc_util.print_trace(2,'Item source code = '||l_line_info_tab(l_idx).item_source_code);
1949 okc_util.print_trace(2,'Item source table = '||l_line_info_tab(l_idx).item_source_table);
1950 okc_util.print_trace(2,'Item id1 = '||l_line_info_tab(l_idx).object_id1);
1951 okc_util.print_trace(2,'Item id2 = '||l_line_info_tab(l_idx).object_id2);
1952 okc_util.print_trace(2,'Item name = '||l_line_info_tab(l_idx).item_name);
1953 okc_util.print_trace(2,'Item Orderable = '||l_line_info_tab(l_idx).customer_order_enabled_flag);
1954 okc_util.print_trace(2,'Item Priced = '||l_line_info_tab(l_idx).priced_item_yn);
1955 okc_util.print_trace(2,'Quantity = '||l_line_info_tab(l_idx).qty);
1956 okc_util.print_trace(2,'UOM = '||l_line_info_tab(l_idx).uom_code);
1957 okc_util.print_trace(2,'Currency code = '||l_line_info_tab(l_idx).currency_code);
1958 okc_util.print_trace(2,'Unit price = '||LTRIM(TO_CHAR(l_line_info_tab(l_idx).price_unit, '9G999G999G990D00')));
1959 END IF;
1960 --okc_util.print_trace(2,'Negot. price = '||l_line_info_tab(l_idx).price);
1961 IF (l_debug = 'Y') THEN
1962 okc_util.print_trace(2,'Start date = '||l_line_info_tab(l_idx).start_date);
1963 okc_util.print_trace(2,'End date = '||l_line_info_tab(l_idx).end_date);
1964 END IF;
1965
1966 IF (l_debug = 'Y') THEN
1967 okc_util.print_trace(2,'Config header id = '||l_line_info_tab(l_idx).config_header_id);
1968 okc_util.print_trace(2,'config rev Num = '||l_line_info_tab(l_idx).config_revision_number);
1969 okc_util.print_trace(2,'Config item id = '||l_line_info_tab(l_idx).config_item_id);
1970 okc_util.print_trace(2,'Config complet yn = '||l_line_info_tab(l_idx).config_complete_yn);
1971 okc_util.print_trace(2,'Config valid yn = '||l_line_info_tab(l_idx).config_valid_yn);
1972 okc_util.print_trace(2,'Component code = '||l_line_info_tab(l_idx).component_code);
1973 okc_util.print_trace(2,'Config item type = '||l_line_info_tab(l_idx).end_date);
1974 END IF;
1975
1976
1977 IF (l_debug = 'Y') THEN
1978 okc_util.print_trace(2,'============================');
1979 okc_util.print_trace(2,'Contract line idx = '||l_idx);
1980 okc_util.print_trace(2,'============================');
1981 END IF;
1982
1983
1984
1985 --
1986 -- Creating Service Quote Lines with only one detail quote line
1987 -- If the line is an extended warranty line, a service line or a
1988 -- support line that has a customer product coverage level, get the
1989 -- covered products.
1990 --
1991
1992 -- get the duration for the entered start AND end dates
1993 --
1994 okc_time_util_pub.get_duration(r_cle.start_date,r_cle.end_date,
1995 l_svc_duration,l_svc_period,x_return_status);
1996
1997 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
1998 okc_api.set_message(OKC_API.G_APP_NAME,'OKC_GET_DURATION_ERROR');
1999 print_error(3);
2000 RAISE e_exit;
2001 END IF;
2002
2003 -- duration is quantity AND period uom for service line
2004 l_line_info_tab(l_idx).qty := rtrim(ltrim(l_svc_duration));
2005 l_line_info_tab(l_idx).uom_code := rtrim(ltrim(l_svc_period));
2006
2007 IF (l_debug = 'Y') THEN
2008 okc_util.print_trace(3,'Duration quantity AND Period uom:');
2009 okc_util.print_trace(3,'=================================');
2010 okc_util.print_trace(3,'Quantity = '||l_line_info_tab(l_idx).qty);
2011 okc_util.print_trace(3,'UOM = '||l_line_info_tab(l_idx).uom_code);
2012 okc_util.print_trace(3,'Duration = '||l_svc_duration);
2013 okc_util.print_trace(3,'Period = '||l_svc_period);
2014 END IF;
2015
2016
2017 --
2018 -- Check and populate the top lines rules table
2019 --
2020
2021 IF (l_debug = 'Y') THEN
2022 okc_util.print_trace(3,'==================================');
2023 okc_util.print_trace(3,'Retrieving the rules at line level');
2024 END IF;
2025
2026 build_k_rules( p_chr_id => p_chr_id,
2027 p_cle_id => l_line_info_tab(l_idx).line_id,
2028 x_rule_tab => lx_kl_rule_tab,
2029 x_bto_data_rec => lx_kl_bto_data_rec,
2030 x_sto_data_rec => lx_kl_sto_data_rec,
2031 x_return_status => lx_return_status );
2032
2033 IF lx_return_status = OKC_API.G_RET_STS_SUCCESS THEN
2034
2035 IF lx_kl_rule_tab.FIRST IS NOT NULL THEN
2036 FOR i IN lx_kl_rule_tab.FIRST..lx_kl_rule_tab.LAST LOOP
2037 l_kl_rule_tab(l_kl_rule_tab.COUNT+1) := lx_kl_rule_tab(i);
2038 END LOOP;
2039 END IF;
2040
2041 IF lx_kl_bto_data_rec.cle_id IS NOT NULL THEN
2042 l_kl_bto_data_tab(l_kl_bto_data_tab.COUNT+1) := lx_kl_bto_data_rec;
2043 END IF;
2044
2045 IF lx_kl_sto_data_rec.cle_id IS NOT NULL THEN
2046 l_kl_sto_data_tab(l_kl_sto_data_tab.COUNT+1) := lx_kl_sto_data_rec;
2047 END IF;
2048
2049 ELSE
2050 raise e_exit;
2051 END IF;
2052
2053 IF (l_debug = 'Y') THEN
2054 okc_util.print_trace(3,'Completed retrieving the rules at line level');
2055 okc_util.print_trace(3,'==================================');
2056 END IF;
2057
2058 --
2059 -- get covered product information
2060 --
2061
2062 l_cp_ctr := 0; -- counter for customer product (cp) lines per service line
2063 FOR r_cp IN c_cp(p_chr_id,
2064 r_cle.line_id, -- Top line id
2065 l_chr.authoring_org_id,
2066 l_chr.inv_organization_id) LOOP
2067
2068 l_cp_ctr:=l_cp_ctr+1;
2069
2070 IF l_cp_ctr > 1 THEN -- more than one cp line for this service line
2071 OKC_API.set_message( -- Set the error message
2072 p_app_name => g_app_name1,
2073 p_msg_name => 'OKO_K2Q_LINENOTORDBL2',
2074 p_token1 => 'LINE_NUM',
2075 p_token1_value => r_cle.line_number,
2076 p_token2 => 'LINE_STYLE',
2077 p_token2_value => r_cle.line_type,
2078 p_token3 => 'KNUMBER',
2079 p_token3_value => l_k_nbr);
2080
2081 print_error(4);
2082
2083 RAISE e_exit;
2084 END IF;
2085
2086 -- increment cp line index
2087 l_cp_idx := l_cp_idx + 1;
2088 l_covlvl_info_tab(l_cp_idx).line_tab_idx := l_idx; -- Maintain Service line info in covlvl line
2089 --
2090 l_covlvl_info_tab(l_cp_idx).line_id := r_cp.cle_id;
2091 l_covlvl_info_tab(l_cp_idx).line_number := r_cp.line_number;
2092 l_covlvl_info_tab(l_cp_idx).status_code := r_cp.ste_code;
2093 l_covlvl_info_tab(l_cp_idx).qty := r_cp.quantity;
2094 l_covlvl_info_tab(l_cp_idx).uom_code := r_cp.uom_code;
2095 l_covlvl_info_tab(l_cp_idx).priced_item_yn := r_cp.priced_item_yn;
2096 l_covlvl_info_tab(l_cp_idx).price_unit := r_cp.price_unit;
2097 l_covlvl_info_tab(l_cp_idx).currency_code := r_cp.currency_code;
2098 l_covlvl_info_tab(l_cp_idx).start_date := r_cp.start_date;
2099 l_covlvl_info_tab(l_cp_idx).end_date := r_cp.end_date;
2100 l_covlvl_info_tab(l_cp_idx).line_style := r_cp.line_style;
2101 l_covlvl_info_tab(l_cp_idx).line_type := r_cp.line_type;
2102 l_covlvl_info_tab(l_cp_idx).line_source_code := r_cp.line_source_code;
2103 l_covlvl_info_tab(l_cp_idx).line_source_table := r_cp.line_source_table;
2104 l_covlvl_info_tab(l_cp_idx).id1 := r_cp.id1;
2105 l_covlvl_info_tab(l_cp_idx).id2 := r_cp.id2;
2106 l_covlvl_info_tab(l_cp_idx).prod_name := r_cp.prod_name;
2107 l_covlvl_info_tab(l_cp_idx).item_source_code := r_cp.item_source_code;
2108 l_covlvl_info_tab(l_cp_idx).item_source_table := r_cp.item_source_table;
2109 l_covlvl_info_tab(l_cp_idx).svc_duration := rtrim(ltrim(l_svc_duration));
2110 l_covlvl_info_tab(l_cp_idx).svc_period := rtrim(ltrim(l_svc_period));
2111
2112 IF (l_debug = 'Y') THEN
2113 okc_util.print_trace(3,'Contract Covered Lines:');
2114 okc_util.print_trace(3,'=======================');
2115 END IF;
2116
2117 IF (l_debug = 'Y') THEN
2118 okc_util.print_trace(3,'>> Line_id = '||l_covlvl_info_tab(l_cp_idx).line_id);
2119 okc_util.print_trace(3,'Line NUMBER = '||l_covlvl_info_tab(l_cp_idx).line_number);
2120 okc_util.print_trace(3,'--------------');
2121 okc_util.print_trace(3,'Top Line idx = '||l_covlvl_info_tab(l_cp_idx).line_tab_idx);
2122 okc_util.print_trace(3,'--------------');
2123 okc_util.print_trace(3,'Line style = '||l_covlvl_info_tab(l_cp_idx).line_style);
2124 okc_util.print_trace(3,'Line type = '||l_covlvl_info_tab(l_cp_idx).line_type);
2125 okc_util.print_trace(3,'Line source code = '||l_covlvl_info_tab(l_cp_idx).line_source_code);
2126 okc_util.print_trace(3,'Line source table = '||l_covlvl_info_tab(l_cp_idx).line_source_table);
2127 okc_util.print_trace(3,'Prod source code = '||l_covlvl_info_tab(l_cp_idx).item_source_code);
2128 okc_util.print_trace(3,'Prod source table = '||l_covlvl_info_tab(l_cp_idx).item_source_table);
2129 okc_util.print_trace(3,'Cust Prod id1/Line id = '||l_covlvl_info_tab(l_cp_idx).id1);
2130 okc_util.print_trace(3,'Cust Prod id2 = '||l_covlvl_info_tab(l_cp_idx).id2);
2131 okc_util.print_trace(3,'Cust Prod name = '||l_covlvl_info_tab(l_cp_idx).prod_name);
2132 okc_util.print_trace(3,'Prod Priced = '||l_covlvl_info_tab(l_cp_idx).priced_item_yn);
2133 okc_util.print_trace(3,'Quantity = '||l_covlvl_info_tab(l_cp_idx).qty);
2134 okc_util.print_trace(3,'UOM = '||l_covlvl_info_tab(l_cp_idx).uom_code);
2135 okc_util.print_trace(3,'Currency code = '||l_covlvl_info_tab(l_cp_idx).currency_code);
2136 END IF;
2137 --okc_util.print_trace(3,'Negoc. price = '||l_covlvl_info_tab(l_cp_idx).price);
2138 IF (l_debug = 'Y') THEN
2139 okc_util.print_trace(3,'Unit price = '||LTRIM(TO_CHAR(l_covlvl_info_tab(l_cp_idx).price_unit, '9G999G999G990D00')));
2140 okc_util.print_trace(3,'Start date = '||l_covlvl_info_tab(l_cp_idx).start_date);
2141 okc_util.print_trace(3,'End date = '||l_covlvl_info_tab(l_cp_idx).end_date);
2142 okc_util.print_trace(3,'SVC duration = '||l_covlvl_info_tab(l_cp_idx).svc_duration);
2143 okc_util.print_trace(3,'SVC period = '||l_covlvl_info_tab(l_cp_idx).svc_period);
2144 okc_util.print_trace(3,' ');
2145 END IF;
2146 END LOOP;
2147
2148 ELSE -- IF is_top_line_orderable_i(r_cle ...) THEN
2149
2150 FND_MSG_PUB.Count_And_Get (
2151 p_count => x_msg_count,
2152 p_data => x_msg_data);
2153 IF x_msg_count > 0 THEN
2154 FOR i IN 1..x_msg_count LOOP
2155 x_msg_data := fnd_msg_pub.get( p_msg_index => i,
2156 p_encoded => 'F'
2157 );
2158 IF (l_debug = 'Y') THEN
2159 okc_util.print_trace(2, '==EXCEPTION=================');
2160 okc_util.print_trace(2, 'Message : '||x_msg_data);
2161 okc_util.print_trace(2, '============================');
2162 END IF;
2163 END LOOP;
2164 END IF;
2165
2166 --
2167 -- Check if this contract line is related to the quote line, if yes raise exception- cannot update
2168 -- If not, then print error - cannot create and continue with the next topline
2169 --
2170
2171 IF is_kl_linked_to_ql(p_chr_id => p_chr_id,
2172 p_cle_id => r_cle.line_id,
2173 p_qh_id => g_quote_id,
2174 p_rlt_code => p_rel_code,
2175 p_rlt_type => g_rlt_typ_ql) = OKC_API.G_TRUE THEN
2176
2177 OKC_API.set_message( -- Set the error message
2178 p_app_name => g_app_name1,
2179 p_msg_name => 'OKO_K2Q_LINENOTORDBL3',
2180 p_token1 => 'LINE_NUM',
2181 p_token1_value => r_cle.line_number,
2182 p_token2 => 'LINE_STYLE',
2183 p_token2_value => r_cle.line_type,
2184 p_token3 => 'KNUMBER',
2185 p_token3_value => l_k_nbr);
2186
2187 print_error(4);
2188
2189 RAISE e_exit;
2190
2191 ELSE -- Contract line not linked to quote line
2192
2193 OKC_API.set_message( -- Set the error message
2194 p_app_name => g_app_name1,
2195 p_msg_name => 'OKO_K2Q_LINENOTORDBL4',
2196 p_token1 => 'LINE_NUM',
2197 p_token1_value => r_cle.line_number,
2198 p_token2 => 'LINE_STYLE',
2199 p_token2_value => r_cle.line_type,
2200 p_token3 => 'KNUMBER',
2201 p_token3_value => l_k_nbr);
2202
2203 print_error(4);
2204
2205 END IF;
2206 END IF; -- is_top_line_orderable_i
2207
2208 ELSE -- is_top_line_with_covered_prod
2209
2210
2211 IF is_top_line_orderable_i(l_customer_order_enabled_flag,r_cle.lse_id,lx_index) = OKC_API.G_TRUE THEN
2212
2213 l_idx := l_idx + 1;
2214 l_line_info_tab(l_idx) := r_cle;
2215
2216 l_line_info_tab(l_idx).item_name := l_item_name;
2217 l_line_info_tab(l_idx).customer_order_enabled_flag := l_customer_order_enabled_flag;
2218
2219 IF (l_debug = 'Y') THEN
2220 okc_util.print_trace(2,'============================');
2221 okc_util.print_trace(2,'Contract line idx - II = '||l_idx);
2222 okc_util.print_trace(2,'============================');
2223 END IF;
2224
2225 IF (l_debug = 'Y') THEN
2226 okc_util.print_trace(2,'--------------');
2227 okc_util.print_trace(2,'> Line_id = '||l_line_info_tab(l_idx).line_id);
2228 okc_util.print_trace(2,'Line NUMBER = '||l_line_info_tab(l_idx).line_number);
2229 okc_util.print_trace(2,'--------------');
2230 okc_util.print_trace(2,'Line style = '||l_line_info_tab(l_idx).line_style);
2231 okc_util.print_trace(2,'Line type = '||l_line_info_tab(l_idx).line_type);
2232 okc_util.print_trace(2,'Line source code = '||l_line_info_tab(l_idx).line_source_code);
2233 okc_util.print_trace(2,'Line source table = '||l_line_info_tab(l_idx).line_source_table);
2234 okc_util.print_trace(2,'Item source code = '||l_line_info_tab(l_idx).item_source_code);
2235 okc_util.print_trace(2,'Item source table = '||l_line_info_tab(l_idx).item_source_table);
2236 okc_util.print_trace(2,'Item id1 = '||l_line_info_tab(l_idx).object_id1);
2237 okc_util.print_trace(2,'Item id2 = '||l_line_info_tab(l_idx).object_id2);
2238 okc_util.print_trace(2,'Item name = '||l_line_info_tab(l_idx).item_name);
2239 okc_util.print_trace(2,'Item Orderable = '||l_line_info_tab(l_idx).customer_order_enabled_flag);
2240 okc_util.print_trace(2,'Item Priced = '||l_line_info_tab(l_idx).priced_item_yn);
2241 okc_util.print_trace(2,'Quantity = '||l_line_info_tab(l_idx).qty);
2242 okc_util.print_trace(2,'UOM = '||l_line_info_tab(l_idx).uom_code);
2243 okc_util.print_trace(2,'Currency code = '||l_line_info_tab(l_idx).currency_code);
2244 okc_util.print_trace(2,'Unit price = '||LTRIM(TO_CHAR(l_line_info_tab(l_idx).price_unit, '9G999G999G990D00')));
2245 END IF;
2246 --okc_util.print_trace(2,'Negot. price = '||l_line_info_tab(l_idx).price);
2247 IF (l_debug = 'Y') THEN
2248 okc_util.print_trace(2,'Start date = '||l_line_info_tab(l_idx).start_date);
2249 okc_util.print_trace(2,'End date = '||l_line_info_tab(l_idx).end_date);
2250 END IF;
2251
2252 IF (l_debug = 'Y') THEN
2253 okc_util.print_trace(2,'Config header id = '||l_line_info_tab(l_idx).config_header_id);
2254 okc_util.print_trace(2,'config rev Num = '||l_line_info_tab(l_idx).config_revision_number);
2255 okc_util.print_trace(2,'Config item id = '||l_line_info_tab(l_idx).config_item_id);
2256 okc_util.print_trace(2,'Config complet yn = '||l_line_info_tab(l_idx).config_complete_yn);
2257 okc_util.print_trace(2,'Config valid yn = '||l_line_info_tab(l_idx).config_valid_yn);
2258 okc_util.print_trace(2,'Component code = '||l_line_info_tab(l_idx).component_code);
2259 okc_util.print_trace(2,'Config item type = '||l_line_info_tab(l_idx).end_date);
2260 END IF;
2261
2262 IF (l_debug = 'Y') THEN
2263 okc_util.print_trace(2,'============================');
2264 okc_util.print_trace(2,'Contract line idx - II = '||l_idx);
2265 okc_util.print_trace(2,'============================');
2266 END IF;
2267 --
2268 -- Check and populate the top lines rules table
2269 --
2270
2271 IF (l_debug = 'Y') THEN
2272 okc_util.print_trace(3,'==================================');
2273 okc_util.print_trace(3,'Retrieving the rules at line level - II');
2274 okc_util.print_trace(3,'==================================');
2275 END IF;
2276
2277 build_k_rules( p_chr_id => p_chr_id,
2278 p_cle_id => l_line_info_tab(l_idx).line_id,
2279 x_rule_tab => lx_kl_rule_tab,
2280 x_bto_data_rec => lx_kl_bto_data_rec,
2281 x_sto_data_rec => lx_kl_sto_data_rec,
2282 x_return_status => lx_return_status );
2283
2284 IF lx_return_status = OKC_API.G_RET_STS_SUCCESS THEN
2285
2286 IF lx_kl_rule_tab.FIRST IS NOT NULL THEN
2287 FOR i IN lx_kl_rule_tab.FIRST..lx_kl_rule_tab.LAST LOOP
2288 l_kl_rule_tab(l_kl_rule_tab.COUNT+1) := lx_kl_rule_tab(i);
2289 END LOOP;
2290 END IF;
2291
2292 IF lx_kl_bto_data_rec.cle_id IS NOT NULL THEN
2293 l_kl_bto_data_tab(l_kl_bto_data_tab.COUNT+1) := lx_kl_bto_data_rec;
2294 END IF;
2295
2296 IF lx_kl_sto_data_rec.cle_id IS NOT NULL THEN
2297 l_kl_sto_data_tab(l_kl_sto_data_tab.COUNT+1) := lx_kl_sto_data_rec;
2298 END IF;
2299
2300 ELSE
2301 raise e_exit;
2302 END IF;
2303
2304 IF (l_debug = 'Y') THEN
2305 okc_util.print_trace(3,'=================================================');
2306 okc_util.print_trace(3,'Completed retrieving the rules at line level - II');
2307 okc_util.print_trace(3,'=================================================');
2308 END IF;
2309
2310
2311 ELSE -- IF is_top_line_orderable_i(r_cle ...) THEN
2312
2313 FND_MSG_PUB.Count_And_Get (
2314 p_count => x_msg_count,
2315 p_data => x_msg_data);
2316 FOR i IN x_msg_count-1..x_msg_count LOOP
2317 x_msg_data := fnd_msg_pub.get( p_msg_index => i,
2318 p_encoded => 'F'
2319 );
2320 IF (l_debug = 'Y') THEN
2321 okc_util.print_trace(2, '==EXCEPTION=================');
2322 okc_util.print_trace(2, 'Message : '||x_msg_data);
2323 okc_util.print_trace(2, '============================');
2324 END IF;
2325 END LOOP;
2326
2327 --
2328 -- Check if this contract line is related to the quote line, if yes raise exception- cannot update
2329 -- If not, then print error - cannot create and continue with the next topline
2330 --
2331 IF is_kl_linked_to_ql(p_chr_id => p_chr_id,
2332 p_cle_id => r_cle.line_id,
2333 p_qh_id => g_quote_id,
2334 p_rlt_code => p_rel_code,
2335 p_rlt_type => g_rlt_typ_ql) = OKC_API.G_TRUE THEN
2336
2337 OKC_API.set_message( -- Set the error message
2338 p_app_name => g_app_name1,
2339 p_msg_name => 'OKO_K2Q_LINENOTORDBL5',
2340 p_token1 => 'LINE_NUM',
2341 p_token1_value => r_cle.line_number,
2342 p_token2 => 'LINE_STYLE',
2343 p_token2_value => r_cle.line_type,
2344 p_token3 => 'KNUMBER',
2345 p_token3_value => l_k_nbr);
2346
2347 print_error(4);
2348
2349 RAISE e_exit;
2350
2351 ELSE -- Contract line not linked to quote line
2352
2353 OKC_API.set_message( -- Set the error message
2354 p_app_name => g_app_name1,
2355 p_msg_name => 'OKO_K2Q_LINENOTORDBL6',
2356 p_token1 => 'LINE_NUM',
2357 p_token1_value => r_cle.line_number,
2358 p_token2 => 'LINE_STYLE',
2359 p_token2_value => r_cle.line_type,
2360 p_token3 => 'KNUMBER',
2361 p_token3_value => l_k_nbr);
2362
2363 print_error(4);
2364
2365 END IF;
2366 END IF; -- is_top_line_orderable_i
2367
2368 END IF; -- is_top_line_with_covered_prod
2369
2370 ELSE -- is_top_line_style_seeded
2371
2372 --
2373 -- Check if this contract line is related to the quote line, if yes raise exception- cannot update
2374 -- If not, then print error - cannot create and continue with the next topline
2375 --
2376 IF is_kl_linked_to_ql(p_chr_id => p_chr_id,
2377 p_cle_id => r_cle.line_id,
2378 p_qh_id => g_quote_id,
2379 p_rlt_code => p_rel_code,
2380 p_rlt_type => g_rlt_typ_ql) = OKC_API.G_TRUE THEN
2381
2382 OKC_API.set_message( -- Set the error message
2383 p_app_name => g_app_name1,
2384 p_msg_name => 'OKO_K2Q_LINENOTORDBL7',
2385 p_token1 => 'LINE_NUM',
2386 p_token1_value => r_cle.line_number,
2387 p_token2 => 'LINE_STYLE',
2388 p_token2_value => r_cle.line_type,
2389 p_token3 => 'KNUMBER',
2390 p_token3_value => l_k_nbr);
2391
2392 print_error(4);
2393
2394 RAISE e_exit;
2395
2396 ELSE -- Contract line not linked to quote line
2397
2398 OKC_API.set_message( -- Set the error message
2399 p_app_name => g_app_name1,
2400 p_msg_name => 'OKO_K2Q_LINENOTORDBL8',
2401 p_token1 => 'LINE_NUM',
2402 p_token1_value => r_cle.line_number,
2403 p_token2 => 'LINE_STYLE',
2404 p_token2_value => r_cle.line_type,
2405 p_token3 => 'KNUMBER',
2406 p_token3_value => l_k_nbr);
2407
2408 print_error(4);
2409
2410 END IF;
2411 END IF; -- is_top_line_seeded
2412 CLOSE c_top_cle;
2413 END LOOP; -- FOR r_cle_i IN c_top_cle_init(p_chr_id) LOOP
2414
2415 --
2416 -- were there any lines, orderable lines?
2417 -- IF not, set return status to error, as no point in continuing
2418 --
2419
2420 IF l_lines = 0 THEN
2421 IF (l_debug = 'Y') THEN
2422 okc_util.print_trace(3, ' ');
2423 okc_util.print_trace(3, '******************************************************************');
2424 okc_util.print_trace(4, 'NO lines');
2425 okc_util.print_trace(3, '******************************************************************');
2426 END IF;
2427 okc_api.set_message(G_APP_NAME1,
2428 'OKO_K2Q_NOLINESFORUPDT',
2429 'KNUMBER',
2430 l_k_nbr);
2431 x_return_status := OKC_API.G_RET_STS_ERROR;
2432 print_error(4);
2433 RAISE e_exit;
2434 END IF;
2435 IF l_idx = 0 THEN
2436 IF (l_debug = 'Y') THEN
2437 okc_util.print_trace(3, ' ');
2438 okc_util.print_trace(3, '******************************************************************');
2439 okc_util.print_trace(4, 'NO orderable lines');
2440 okc_util.print_trace(3, '******************************************************************');
2441 END IF;
2442 okc_api.set_message(G_APP_NAME1,
2443 'OKO_K2Q_NOORDLNFORUPDT',
2444 'KNUMBER',
2445 l_k_nbr);
2446 x_return_status := OKC_API.G_RET_STS_ERROR;
2447 print_error(4);
2448 RAISE e_exit;
2449 END IF;
2450
2451 x_return_status := OKC_API.G_RET_STS_SUCCESS;
2452 IF (l_debug = 'Y') THEN
2453 okc_util.print_trace(1, '<END - OKC_OC_INT_KTQ_PVT.BUILD_K_STRUCTURES -');
2454 END IF;
2455 EXCEPTION
2456 WHEN e_exit THEN
2457 IF c_chr%ISOPEN THEN
2458 CLOSE c_chr;
2459 END IF;
2460 IF c_cust%ISOPEN THEN
2461 CLOSE c_cust;
2462 END IF;
2463 IF c_rules%ISOPEN THEN
2464 CLOSE c_rules;
2465 END IF;
2466 IF c_top_cle%ISOPEN THEN
2467 CLOSE c_top_cle;
2468 END IF;
2469 IF c_cp%ISOPEN THEN
2470 CLOSE c_cp;
2471 END IF;
2472 WHEN OTHERS THEN
2473 IF c_chr%ISOPEN THEN
2474 CLOSE c_chr;
2475 END IF;
2476 IF c_cust%ISOPEN THEN
2477 CLOSE c_cust;
2478 END IF;
2479 IF c_rules%ISOPEN THEN
2480 CLOSE c_rules;
2481 END IF;
2482 IF c_top_cle%ISOPEN THEN
2483 CLOSE c_top_cle;
2484 END IF;
2485 IF c_cp%ISOPEN THEN
2486 CLOSE c_cp;
2487 END IF;
2488 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
2489 -- notify caller of an UNEXPECTED error
2490 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2491 --RAISE;
2492 END build_k_structures;
2493
2494
2495 -------------------------------------------------------------------------------
2496 -- Procedure: build_qte_hdr
2497 -- Purpose: Build the quote or order header record to pass
2498 -- to the ASO
2499 -- APIs
2500 -- In Parameters:
2501 -- Out Parameters:
2502 -- In/Out Parameters: px_qte_hdr_rec - the record to pass to ASO
2503
2504 PROCEDURE build_qte_hdr(px_qte_hdr_rec IN OUT NOCOPY ASO_QUOTE_PUB.qte_header_rec_type
2505 ,px_hd_shipment_tbl IN OUT NOCOPY ASO_QUOTE_PUB.shipment_tbl_type
2506 ,p_contract_id IN OKC_K_HEADERS_B.ID%TYPE
2507 -- ,p_quote_id IN OKX_QUOTE_HEADERS_V.ID1%TYPE
2508 ,p_rel_code IN OKC_K_REL_OBJS.rty_code%TYPE
2509 ,x_return_status OUT NOCOPY VARCHAR2
2510 ) IS
2511
2512 e_exit exception; -- used to exit processing
2513 l_msg_count NUMBER;
2514 l_msg_data VARCHAR2(1000);
2515 l_k_ship_found VARCHAR2(1) := 'N';
2516 l_value VARCHAR2(1) := 'N';
2517
2518
2519 -- Cursor to select all existing shipment lines at the quote header level
2520 -- and decide on the operation code.
2521 --
2522
2523 CURSOR c_q_shipment(b_qh_id NUMBER, b_ql_id NUMBER) IS
2524 SELECT 'Y'
2525 FROM
2526 okx_qte_shipments_v
2527 WHERE
2528 quote_header_id = b_qh_id
2529 AND (( b_ql_id IS NULL AND quote_line_id IS NULL) OR
2530 (b_ql_id IS NOT NULL AND quote_line_id = b_ql_id));
2531
2532 /*
2533 CURSOR c_qte_hdr(b_qh_id NUMBER) IS
2534 SELECT
2535 id1 quote_header_id,
2536 quote_number
2537 -- status,
2538 -- b_status status_code
2539 FROM
2540 okx_quote_headers_v
2541 WHERE
2542 id1 = b_qh_id;
2543 */
2544
2545 CURSOR c_qte_hdr(b_qh_id NUMBER) IS
2546 SELECT
2547 id1 quote_header_id,
2548 quote_number,
2549 last_update_date
2550 FROM
2551 okx_quote_headers_v
2552 WHERE
2553 id1=b_qh_id;
2554
2555 l_qte_hdr c_qte_hdr%ROWTYPE;
2556
2557
2558 CURSOR c_salesrep IS
2559 SELECT
2560 sr.resource_id
2561 FROM
2562 okc_contacts ct,
2563 okc_k_party_roles_b pt,
2564 okx_salesreps_v sr
2565 WHERE
2566 pt.jtot_object1_code = g_okx_legentity
2567 AND pt.rle_code = g_supplier_ptrol -- g_supplier_ptrol = SUPPLIER
2568 AND ct.cpl_id = pt.id -- party role id
2569 AND ct.dnz_chr_id = p_contract_id
2570 AND ct.contact_sequence = 2
2571 AND ct.cro_code = g_salesrep_ctrol -- g_salesrep_ctrol = SALESPERSON
2572 AND ct.jtot_object1_code = g_jtf_okx_salepers -- g_jtf_okx_salepers = OKX_SALEPERS
2573 AND sr.id1 = ct.object1_id1
2574 AND sr.id2 = ct.object1_id2;
2575
2576 l_salesrep c_salesrep%ROWTYPE;
2577
2578 BEGIN
2579
2580 IF (l_debug = 'Y') THEN
2581 okc_util.print_trace(1, ' ');
2582 okc_util.print_trace(1, '>START - OKC_OC_INT_KTQ_PVT.BUILD_QTE_HDR - Get quote header information');
2583 END IF;
2584
2585 px_qte_hdr_rec.org_id := l_chr.authoring_org_id;
2586 px_qte_hdr_rec.currency_code := l_chr.currency_code; -- add back in when available
2587 px_qte_hdr_rec.quote_version := 1;
2588 px_qte_hdr_rec.party_id := l_cust.object1_id1;
2589 px_qte_hdr_rec.original_system_reference := l_k_nbr;
2590
2591 OPEN c_qte_hdr(g_quote_id);
2592 FETCH c_qte_hdr INTO l_qte_hdr;
2593 IF c_qte_hdr%FOUND THEN
2594
2595 px_qte_hdr_rec.quote_header_id := l_qte_hdr.quote_header_id;
2596 px_qte_hdr_rec.quote_number := l_qte_hdr.quote_number;
2597 -- px_qte_hdr_rec.quote_status := l_qte_hdr.status;
2598 -- px_qte_hdr_rec.quote_status_code:= l_qte_hdr.status_code;
2599 px_qte_hdr_rec.last_update_date := l_qte_hdr.last_update_date;
2600
2601 END IF;
2602 CLOSE c_qte_hdr;
2603
2604
2605 OPEN c_salesrep;
2606 FETCH c_salesrep INTO l_salesrep;
2607 IF c_salesrep%FOUND THEN
2608 px_qte_hdr_rec.resource_id := to_number(l_salesrep.resource_id);
2609 END IF;
2610 CLOSE c_salesrep;
2611
2612
2613 IF l_kh_rule_tab.first IS NOT NULL THEN
2614 FOR i IN l_kh_rule_tab.first..l_kh_rule_tab.last LOOP
2615 -- get rule information for header
2616
2617 IF l_kh_rule_tab(i).rule_information_category = g_rd_custacct THEN
2618 -- customer account
2619 px_qte_hdr_rec.cust_account_id := l_kh_rule_tab(i).object1_id1;
2620
2621 ELSIF l_kh_rule_tab(i).rule_information_category = g_rd_price THEN
2622 -- price list
2623 px_qte_hdr_rec.price_list_id := l_kh_rule_tab(i).object1_id1;
2624
2625 ELSIF l_kh_rule_tab(i).rule_information_category = g_rd_invrule THEN
2626 -- invoice rule
2627 px_qte_hdr_rec.invoicing_rule_id:= l_kh_rule_tab(i).object1_id1;
2628
2629 ELSIF l_kh_rule_tab(i).rule_information_category = g_rd_shipmtd THEN
2630 px_hd_shipment_tbl(i).ship_method_code := l_kh_rule_tab(i).rule_information_category;
2631
2632 -- shipment method
2633 ELSIF l_kh_rule_tab(i).rule_information_category = g_rd_shipto THEN
2634 l_k_ship_found := 'Y';
2635
2636 END IF;
2637 END LOOP;
2638 END IF;
2639
2640
2641 IF l_kh_bto_data_tab.FIRST IS NOT NULL THEN
2642 FOR i IN l_kh_bto_data_tab.FIRST..l_kh_bto_data_tab.LAST LOOP
2643
2644 px_qte_hdr_rec.invoice_to_party_site_id := l_kh_bto_data_tab(i).party_site_id;
2645 -- px_qte_hdr_rec.invoice_to_party_id := NVL(l_kh_bto_data_tab(i).party_id,l_cust.object1_id1);
2646
2647 END LOOP;
2648 END IF;
2649
2650 --
2651 -- Populate the shipment record
2652
2653 IF l_kh_sto_data_tab.FIRST IS NOT NULL THEN
2654 FOR i IN l_kh_sto_data_tab.FIRST..l_kh_sto_data_tab.LAST LOOP
2655
2656 px_hd_shipment_tbl(i).ship_to_party_id := NVL(l_kh_sto_data_tab(i).party_id,l_cust.object1_id1);
2657 px_hd_shipment_tbl(i).ship_to_party_site_id := l_kh_sto_data_tab(i).party_site_id;
2658 px_hd_shipment_tbl(i).ship_to_cust_account_id := l_kh_sto_data_tab(i).cust_acct_id;
2659
2660 px_hd_shipment_tbl(i).ship_to_address1 := l_kh_sto_data_tab(i).address1;
2661 px_hd_shipment_tbl(i).ship_to_address2 := l_kh_sto_data_tab(i).address2;
2662 px_hd_shipment_tbl(i).ship_to_address3 := l_kh_sto_data_tab(i).address3;
2663 px_hd_shipment_tbl(i).ship_to_address4 := l_kh_sto_data_tab(i).address4;
2664 px_hd_shipment_tbl(i).ship_to_city := l_kh_sto_data_tab(i).city;
2665 px_hd_shipment_tbl(i).ship_to_state := l_kh_sto_data_tab(i).state;
2666 px_hd_shipment_tbl(i).ship_to_province := l_kh_sto_data_tab(i).province;
2667 px_hd_shipment_tbl(i).ship_to_postal_code := l_kh_sto_data_tab(i).postal_code;
2668 px_hd_shipment_tbl(i).ship_to_county := l_kh_sto_data_tab(i).county;
2669 px_hd_shipment_tbl(i).ship_to_country := l_kh_sto_data_tab(i).country;
2670
2671
2672 OPEN c_q_shipment(px_qte_hdr_rec.quote_header_id,null);
2673
2674 FETCH c_q_shipment INTO l_value;
2675
2676 IF c_q_shipment%NOTFOUND THEN
2677 IF l_k_ship_found = 'Y' THEN
2678 px_hd_shipment_tbl(i).operation_code := g_aso_op_code_create;
2679 END IF;
2680 ELSE
2681 IF l_k_ship_found = 'Y' THEN
2682 px_hd_shipment_tbl(i).operation_code := g_aso_op_code_update;
2683 ELSE
2684 px_hd_shipment_tbl(i).operation_code := g_aso_op_code_delete;
2685 END IF;
2686 END IF;
2687 CLOSE c_q_shipment;
2688
2689 END LOOP;
2690 END IF;
2691
2692 --
2693 -- set exchange information
2694 --
2695 px_qte_hdr_rec.exchange_type_code := l_exchange_type;
2696 px_qte_hdr_rec.exchange_rate := l_exchange_rate;
2697 px_qte_hdr_rec.exchange_rate_date := l_exchange_date;
2698
2699 --
2700 -- check IF we got customer account, set IF not
2701 --
2702 IF px_qte_hdr_rec.cust_account_id IS NULL
2703 OR px_qte_hdr_rec.cust_account_id = FND_API.G_MISS_NUM THEN
2704
2705 px_qte_hdr_rec.cust_account_id := l_bt_cust_acct_id;
2706
2707 END IF;
2708
2709 OPEN c_chr(p_contract_id);
2710 FETCH c_chr INTO l_chr;
2711 IF c_chr%FOUND THEN
2712
2713 px_qte_hdr_rec.total_list_price := l_chr.total_line_list_price;
2714 px_qte_hdr_rec.total_adjusted_amount := l_chr.total_line_list_price - l_chr.estimated_amount;
2715 px_qte_hdr_rec.price_list_id := l_chr.price_list_id;
2716
2717 px_qte_hdr_rec.quote_expiration_date := l_chr.sign_by_date;
2718
2719 END IF;
2720 CLOSE c_chr;
2721
2722 IF (l_debug = 'Y') THEN
2723 okc_util.print_trace(1, ' ');
2724 okc_util.print_trace(1, 'INPUT RECORD FOR QUOTE UPDATION - Quote Header:');
2725 okc_util.print_trace(1, '===============================================');
2726 okc_util.print_trace(2, 'Org_id = '||px_qte_hdr_rec.org_id);
2727 okc_util.print_trace(2, 'Original syst ref (contract num) = '||px_qte_hdr_rec.original_system_reference);
2728 okc_util.print_trace(2, 'Quote name = '||px_qte_hdr_rec.quote_name);
2729 okc_util.print_trace(2, 'Quote version = '||px_qte_hdr_rec.quote_version);
2730 okc_util.print_trace(2, 'Quote source code = '||px_qte_hdr_rec.quote_source_code);
2731 okc_util.print_trace(2, 'Quote category code = '||ltrim(rtrim(px_qte_hdr_rec.quote_category_code)));
2732 okc_util.print_trace(2, 'Quote expiration date= '||px_qte_hdr_rec.quote_expiration_date);
2733 okc_util.print_trace(2, 'Party_id = '||px_qte_hdr_rec.party_id);
2734 okc_util.print_trace(2, 'Cust Acct Id = '||px_qte_hdr_rec.cust_account_id);
2735 okc_util.print_trace(2, 'Price List Id = '||px_qte_hdr_rec.price_list_id);
2736 okc_util.print_trace(2, 'Inv Rule Id = '||px_qte_hdr_rec.invoicing_rule_id);
2737 okc_util.print_trace(2, 'Inv To Party Id = '||px_qte_hdr_rec.invoice_to_party_id);
2738 okc_util.print_trace(2, 'Inv To Party Site Id = '||px_qte_hdr_rec.invoice_to_party_site_id);
2739 END IF;
2740 --okc_util.print_trace(2, 'Ship To Party Id = '||px_hd_shipment_rec.ship_to_party_id);
2741 --okc_util.print_trace(2, 'Ship To Party Site Id= '||px_hd_shipment_rec.ship_to_party_site_id);
2742 IF (l_debug = 'Y') THEN
2743 okc_util.print_trace(2, 'Currency code = '||px_qte_hdr_rec.currency_code);
2744 END IF;
2745 --okc_util.print_trace(2, 'Total quote price = '||LTRIM(TO_CHAR(px_qte_hdr_rec.total_quote_price, '9G999G999G990D00')));
2746 IF (l_debug = 'Y') THEN
2747 okc_util.print_trace(2, 'Total list price = '||LTRIM(TO_CHAR(px_qte_hdr_rec.total_list_price, '9G999G999G990D00')));
2748 okc_util.print_trace(2, 'Total adjusted amount= '||LTRIM(TO_CHAR(px_qte_hdr_rec.total_adjusted_amount, '9G999G999G990D00')));
2749 okc_util.print_trace(2, 'Total adjusted amount= '||to_number(px_qte_hdr_rec.total_adjusted_amount));
2750 okc_util.print_trace(2, 'Exchange type code = '||px_qte_hdr_rec.exchange_type_code);
2751 okc_util.print_trace(2, 'Exchange rate = '||px_qte_hdr_rec.exchange_rate);
2752 okc_util.print_trace(2, 'Exchange rate date = '||px_qte_hdr_rec.exchange_rate_date);
2753 okc_util.print_trace(2, '---------------------------------------');
2754 okc_util.print_trace(2, 'Quote header Id = '||px_qte_hdr_rec.quote_header_id);
2755 okc_util.print_trace(2, 'Quote NUMBER = '||px_qte_hdr_rec.quote_number);
2756 okc_util.print_trace(2, 'Quote status Id = '||px_qte_hdr_rec.quote_status_id);
2757 okc_util.print_trace(2, 'Quote status code = '||ltrim(rtrim(px_qte_hdr_rec.quote_status_code)));
2758 okc_util.print_trace(2, 'Quote status = '||ltrim(rtrim(px_qte_hdr_rec.quote_status)));
2759 END IF;
2760
2761 x_return_status := OKC_API.G_RET_STS_SUCCESS;
2762 IF (l_debug = 'Y') THEN
2763 okc_util.print_trace(1, '<END - OKC_OC_INT_KTQ_PVT.BUILD_QTE_HDR -');
2764 END IF;
2765 EXCEPTION
2766 WHEN e_exit THEN
2767 -- nothing more to do
2768 null;
2769 WHEN OTHERS THEN
2770 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
2771 -- notify caller of an UNEXPECTED error
2772 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2773 --RAISE;
2774 END build_qte_hdr;
2775
2776 -------------------------------------------------------------------------------
2777 -- Procedure: build_qte_line
2778 -- Purpose: Build the quote line AND quote detail line records. To be used
2779 -- to pass to the ASO quote AND order APIs
2780 -- In Parameters:
2781 -- Out Parameters:
2782 -- In/Out Paramters: px_qte_line_tbl -- table of quote lines
2783 -- px_qte_line_dtl_tbl -- table of quote detail lines
2784 -- px_k2q_line_tbl -- holds relationship between contract line
2785 -- AND the index in the quote line table
2786 -- px_qte_ln_shipment_tbl contains the shipment information
2787 --
2788 --
2789 PROCEDURE build_qte_line (
2790 p_contract_id IN OKC_K_HEADERS_B.ID%TYPE
2791 ,px_qte_hdr_rec IN ASO_QUOTE_PUB.qte_header_rec_type
2792 ,px_qte_line_tbl IN OUT NOCOPY aso_quote_pub.qte_line_tbl_type
2793 ,px_qte_line_dtl_tbl IN OUT NOCOPY aso_quote_pub.qte_line_dtl_tbl_type
2794 ,px_qte_ln_shipment_tbl IN OUT NOCOPY aso_quote_pub.shipment_tbl_type
2795 ,px_k2q_line_tbl IN OUT NOCOPY okc_oc_int_config_pvt.line_rel_tab_type
2796 -- ,x_total_price OUT NUMBER
2797 ,x_line_rltship_tab OUT NOCOPY ASO_QUOTE_PUB.line_rltship_tbl_type
2798 ,x_return_status OUT NOCOPY VARCHAR2
2799 ) IS
2800
2801 l_ql binary_integer; -- table index
2802 i_l_ql binary_integer; -- table index
2803 l_dql binary_integer; -- table index
2804 i_l_dql binary_integer; -- table index
2805 l_cp_found BOOLEAN;
2806 l_lp_found BOOLEAN;
2807 l_line_skipped BOOLEAN;
2808 l_continue BOOLEAN;
2809 l_nb_qte_line_dtl NUMBER;
2810 --
2811 l_top_line_unit_price okc_k_lines_b.price_unit%TYPE;
2812 l_topln_unit_prc_assgnd VARCHAR2(1);
2813 l_unit_price_assigned okc_k_lines_b.price_unit%TYPE;
2814 --
2815 l_previous_line_id okc_k_lines_b.id%TYPE;
2816 l_cur_tl NUMBER;
2817 l_cur_cl NUMBER;
2818 l_id1 okx_quote_line_detail_v.id1%TYPE;
2819 --
2820 l_k_ship_found VARCHAR2(1) := 'N';
2821 l_value VARCHAR2(1) := 'N';
2822 l_quote_line_id okc_k_rel_objs.object1_id1%TYPE;
2823 l_qt_found VARCHAR2(1) := 'N';
2824 l_k2q_found VARCHAR2(1) := 'N';
2825 l_qdl_a_found VARCHAR2(1) := 'N';
2826 l_qdl_b_found VARCHAR2(1) := 'N';
2827
2828 e_exit EXCEPTION;
2829
2830 --
2831 k binary_integer;
2832 r binary_integer;
2833 x binary_integer;
2834 y binary_integer;
2835 z binary_integer;
2836
2837
2838 x_msg_count NUMBER ;
2839 x_msg_data VARCHAR2(1000);
2840
2841 CURSOR c_quot_detl_line (p_qte_line_id IN NUMBER) IS
2842 SELECT ID1
2843 FROM okx_quote_line_detail_v
2844 WHERE QUOTE_LINE_ID = p_qte_line_id;
2845
2846
2847 CURSOR c_ql (g_quote_id IN okx_quote_lines_v.quote_header_id%TYPE ) IS
2848 SELECT id1
2849 FROM okx_quote_lines_v
2850 WHERE quote_header_id = g_quote_id;
2851
2852 CURSOR c_sl (id1 IN okx_quote_lines_v.ID1%TYPE ) IS
2853 SELECT shipment_id
2854 FROM okx_qte_shipments_v
2855 WHERE quote_line_id = id1;
2856
2857
2858 CURSOR c_qdl(id1 IN okx_quote_lines_v.ID1%TYPE ) IS
2859 SELECT id1
2860 FROM okx_quote_line_detail_v
2861 WHERE quote_line_id = id1;
2862
2863 -- Cursor to select all existing shipment lines at the quote header level
2864 -- and decide on the operation code.
2865 --
2866 CURSOR c_q_shipment(b_ql_id NUMBER) IS
2867 SELECT 'Y'
2868 FROM
2869 okx_qte_shipments_v
2870 WHERE quote_line_id = b_ql_id;
2871
2872 BEGIN
2873 IF (l_debug = 'Y') THEN
2874 okc_util.print_trace(1, ' ');
2875 okc_util.print_trace(1, '>START - OKC_OC_INT_KTQ_PVT.BUILD_QTE_LINE - Get quote line AND quote detail line information');
2876 END IF;
2877
2878 --
2879 -- housekeeping
2880 --
2881 x_line_rltship_tab.DELETE;
2882
2883
2884 l_dql := 0;
2885 l_ql := 0;
2886 l_previous_line_id := NULL;
2887 -- x_total_price := 0;
2888
2889 IF l_line_info_tab.first is not NULL THEN
2890 IF (l_debug = 'Y') THEN
2891 okc_util.print_trace(2,'l_line_info_tab.count '||l_line_info_tab.count);
2892 END IF;
2893 FOR c IN 1..2 LOOP
2894 --
2895 -- need to ensure that license product lines are processed before
2896 -- support lines, to have the quote line id available when creating
2897 -- the quote detail line of the quote line for the support line
2898 --
2899 FOR i IN l_line_info_tab.first..l_line_info_tab.last LOOP
2900 l_continue:=TRUE;
2901 IF c=1 AND is_top_line_with_covered_prod(l_line_info_tab(i).lse_id) = OKC_API.G_TRUE THEN
2902 l_continue:=FALSE;
2903 ELSIF c=2 AND NOT is_top_line_with_covered_prod(l_line_info_tab(i).lse_id) = OKC_API.G_TRUE THEN
2904 l_continue:=FALSE;
2905 END IF;
2906
2907 IF l_continue THEN
2908
2909 IF (l_debug = 'Y') THEN
2910 okc_util.print_trace(2, ' ');
2911 okc_util.print_trace(2, '-----------------------------');
2912 okc_util.print_trace(2, 'Contract line idx = '||i);
2913 okc_util.print_trace(2, 'Contract line NUMBER = '||l_line_info_tab(i).line_number);
2914 okc_util.print_trace(2, '-----------------------------');
2915 END IF;
2916
2917 --
2918 -- define one quote line
2919 --
2920 -- l_ql := l_ql + 1;
2921 --
2922
2923 -- Open the c_q_k_rel cursor to check the existence of the related quote line
2924 -- to create the operation code.(Create,Update or Delete ).
2925
2926 IF l_ktq_flag = OKC_API.G_TRUE THEN
2927 OPEN c_q_k_rel(p_contract_id,l_line_info_tab(i).line_id,g_quote_id,g_rlt_code_ktq,g_rlt_typ_ql);
2928 ELSE
2929 OPEN c_q_k_rel(p_contract_id,l_line_info_tab(i).line_id,g_quote_id,g_rlt_code_knq,g_rlt_typ_ql);
2930 END IF;
2931
2932 FETCH c_q_k_rel INTO l_quote_line_id;
2933 IF c_q_k_rel%FOUND THEN -- UPDATE
2934
2935 IF (l_debug = 'Y') THEN
2936 okc_util.print_trace(2,'Quote line: Related quote line found - update ');
2937 END IF;
2938
2939 IF (l_debug = 'Y') THEN
2940 okc_util.print_trace(2,'l_line_info_tab('||i||') - config_item_type = '||l_line_info_tab(i).config_item_type);
2941 END IF;
2942 IF l_line_info_tab(i).config_item_type IN (g_okc_model_item,g_okc_base_item) THEN
2943 --
2944 -- Configurable item
2945 --
2946 -- check againt px_qte_line_tbl to retrieve l_quote_line_id
2947 -- This is necessary to fit the top model line and top base line
2948 -- into one line
2949
2950
2951 IF px_qte_line_tbl.FIRST IS NOT NULL THEN
2952 FOR k IN px_qte_line_tbl.FIRST..px_qte_line_tbl.LAST LOOP
2953 IF px_qte_line_tbl(k).quote_line_id = l_quote_line_id THEN
2954 l_qt_found := 'Y';
2955 l_ql:=k; -- if found reuse the same entry for update
2956 px_qte_line_tbl(l_ql).operation_code := g_aso_op_code_update;
2957 px_qte_line_tbl(l_ql).quote_line_id := l_quote_line_id;
2958 px_qte_line_tbl(l_ql).quote_header_id:= g_quote_id;
2959
2960 IF (l_debug = 'Y') THEN
2961 okc_util.print_trace(2,'1a.Found an entry in px_qte_line_tbl with index = '||l_ql);
2962 okc_util.print_trace(2,'Operation code = '||px_qte_line_tbl(l_ql).operation_code);
2963 okc_util.print_trace(2,'Qte line id = '||px_qte_line_tbl(l_ql).quote_line_id);
2964 okc_util.print_trace(2,'Qte header id = '||px_qte_line_tbl(l_ql).quote_header_id);
2965 END IF;
2966
2967 EXIT;
2968 END IF;
2969 END LOOP; -- FOR k IN px_qte_line_tbl.FIRST
2970 END IF; -- IF px_qte_line_tbl.FIRST IS NOT NULL
2971
2972 IF l_qt_found = 'N' THEN -- Looping thru the px_qte_line_tbl didnot find l_quote_line_id
2973 i_l_ql:=i_l_ql+1;
2974 l_ql:=i_l_ql;
2975 px_qte_line_tbl(l_ql).operation_code := g_aso_op_code_update;
2976 px_qte_line_tbl(l_ql).quote_line_id := l_quote_line_id;
2977 px_qte_line_tbl(l_ql).quote_header_id:= g_quote_id;
2978
2979 IF (l_debug = 'Y') THEN
2980 okc_util.print_trace(2,'1a.Did not find any entry in px_qte_line_tbl');
2981 okc_util.print_trace(2,'Index = '||l_ql);
2982 okc_util.print_trace(2,'Operation code = '||px_qte_line_tbl(l_ql).operation_code);
2983 okc_util.print_trace(2,'Qte line id = '||px_qte_line_tbl(l_ql).quote_line_id);
2984 okc_util.print_trace(2,'Qte header id = '||px_qte_line_tbl(l_ql).quote_header_id);
2985 END IF;
2986 END IF;
2987
2988 ELSE -- l_line_info_tab(i).config_item_type IN
2989
2990 --
2991 -- This is the case of a regular quote line other than
2992 -- the top model line and top base line
2993 -- Related quote line found, but is not a top model or top base item,
2994 -- and a regular update
2995
2996 i_l_ql:=i_l_ql+1;
2997 l_ql:=i_l_ql;
2998 px_qte_line_tbl(l_ql).operation_code := g_aso_op_code_update;
2999 px_qte_line_tbl(l_ql).quote_line_id := l_quote_line_id;
3000 px_qte_line_tbl(l_ql).quote_header_id:= g_quote_id;
3001
3002 IF (l_debug = 'Y') THEN
3003 okc_util.print_trace(2,'1a.case of a non top model or non top base line - update ');
3004 okc_util.print_trace(2,'Index = '||l_ql);
3005 okc_util.print_trace(2,'Operation code = '||px_qte_line_tbl(l_ql).operation_code);
3006 okc_util.print_trace(2,'Qte line id = '||px_qte_line_tbl(l_ql).quote_line_id);
3007 okc_util.print_trace(2,'Qte header id = '||px_qte_line_tbl(l_ql).quote_header_id);
3008 END IF;
3009
3010 END IF; --l_line_info_tab(i).config_item_type IN
3011
3012 ELSE -- c_q_k_rel%FOUND ( i.e. if not found ) -- CREATE
3013 --
3014 -- Check against px_k2q_line_tbl to find if there is an entry for the
3015 -- parent line id, in case of a configurable item
3016 --
3017 IF (l_debug = 'Y') THEN
3018 okc_util.print_trace(2,'Quote line: Related quote line not found - create ');
3019 END IF;
3020
3021 IF (l_debug = 'Y') THEN
3022 okc_util.print_trace(2,'l_line_info_tab('||i||') - config_item_type = '||l_line_info_tab(i).config_item_type);
3023 END IF;
3024 IF l_line_info_tab(i).config_item_type IN (g_okc_model_item,g_okc_base_item) THEN
3025 --
3026 -- Check against px_k2q_line_tbl
3027 --
3028 IF px_k2q_line_tbl.FIRST IS NOT NULL THEN
3029 FOR k IN px_k2q_line_tbl.FIRST..px_k2q_line_tbl.LAST LOOP
3030 IF px_k2q_line_tbl(k).k_line_id = l_line_info_tab(i).cle_id THEN
3031 l_k2q_found := 'Y';
3032 l_ql:=px_k2q_line_tbl(k).q_line_idx; -- q_line_idx should be equal to k
3033 --
3034 -- if found reuse the same entry (with oper.code
3035 -- as CREATE) to be updated
3036 --
3037 px_qte_line_tbl(l_ql).operation_code := g_aso_op_code_create;
3038 px_qte_line_tbl(l_ql).quote_header_id:= g_quote_id;
3039
3040 IF (l_debug = 'Y') THEN
3041 okc_util.print_trace(2,'2a.Found an entry in px_qte_line_tbl with index = '||l_ql);
3042 okc_util.print_trace(2,'Operation code = '||px_qte_line_tbl(l_ql).operation_code);
3043 okc_util.print_trace(2,'Qte header id = '||px_qte_line_tbl(l_ql).quote_header_id);
3044 END IF;
3045
3046 EXIT;
3047 END IF;
3048 END LOOP; -- FOR k IN px_k2q_line_tbl.FIRST..
3049 END IF; -- IF px_k2q_line_tbl.FIRST IS NOT NULL
3050
3051 IF l_k2q_found = 'N' THEN -- Looping thru the px_k2q_line_tbl
3052 -- didnot find l_line_info_tab(i).cle_id
3053 l_ql:=l_ql+1;
3054 px_qte_line_tbl(l_ql).operation_code := g_aso_op_code_create;
3055 px_qte_line_tbl(l_ql).quote_header_id:= g_quote_id;
3056
3057 IF (l_debug = 'Y') THEN
3058 okc_util.print_trace(2,'2a.Didnot find any entry in px_qte_line_tbl ');
3059 okc_util.print_trace(2,'Operation code = '||px_qte_line_tbl(l_ql).operation_code);
3060 okc_util.print_trace(2,'Qte header id = '||px_qte_line_tbl(l_ql).quote_header_id);
3061 END IF;
3062
3063 END IF;
3064 ELSE -- l_line_info_tab(i).config_item_type IN (g_okc_model_item,g_okc_base_item)
3065 l_ql:=l_ql+1;
3066 px_qte_line_tbl(l_ql).operation_code := g_aso_op_code_create;
3067 px_qte_line_tbl(l_ql).quote_header_id:= g_quote_id;
3068
3069 IF (l_debug = 'Y') THEN
3070 okc_util.print_trace(2,'2a.case of a non top model or non top base line - create ');
3071 okc_util.print_trace(2,'Index = '||l_ql);
3072 okc_util.print_trace(2,'Operation code = '||px_qte_line_tbl(l_ql).operation_code);
3073 okc_util.print_trace(2,'Qte header id = '||px_qte_line_tbl(l_ql).quote_header_id);
3074 END IF;
3075
3076 END IF; -- l_line_info_tab(i).config_item_type IN (g_okc_model_item,g_okc_base_item)
3077
3078 END IF; -- c_q_k_rel%FOUND
3079
3080 IF (l_debug = 'Y') THEN
3081 okc_util.print_trace(2, 'Qte line table - operation code '|| px_qte_line_tbl(l_ql).operation_code);
3082 okc_util.print_trace(2, 'Qte line table - qte hdr id '|| px_qte_line_tbl(l_ql).quote_header_id);
3083 okc_util.print_trace(2, 'Qte line table - qte line id '|| px_qte_line_tbl(l_ql).quote_line_id);
3084 END IF;
3085
3086 CLOSE c_q_k_rel;
3087
3088 IF (l_debug = 'Y') THEN
3089 okc_util.print_trace(2, 'Quote line NUMBER = '||l_ql);
3090 okc_util.print_trace(2, '-----------------------------');
3091 END IF;
3092
3093 IF (l_debug = 'Y') THEN
3094 okc_util.print_trace(2, '>> Building quote line structures');
3095 END IF;
3096 px_qte_line_tbl(l_ql).line_number := l_ql;
3097 px_qte_line_tbl(l_ql).org_id := l_chr.authoring_org_id;
3098 px_qte_line_tbl(l_ql).inventory_item_id:= l_line_info_tab(i).object_id1;
3099 px_qte_line_tbl(l_ql).organization_id := l_line_info_tab(i).object_id2;
3100 px_qte_line_tbl(l_ql).quantity := l_line_info_tab(i).qty;
3101 px_qte_line_tbl(l_ql).uom_code := l_line_info_tab(i).uom_code;
3102 px_qte_line_tbl(l_ql).start_date_active:= l_line_info_tab(i).end_date + 1;
3103 px_qte_line_tbl(l_ql).currency_code := l_line_info_tab(i).currency_code;
3104
3105
3106 IF (l_debug = 'Y') THEN
3107 okc_util.print_trace(2,'Qte line table - line number '||px_qte_line_tbl(l_ql).line_number);
3108 okc_util.print_trace(2,'Qte line table - org id '||px_qte_line_tbl(l_ql).org_id);
3109 okc_util.print_trace(2,'Qte line table - item id '||px_qte_line_tbl(l_ql).inventory_item_id);
3110 okc_util.print_trace(2,'Qte line table - organization id '||px_qte_line_tbl(l_ql).organization_id);
3111 okc_util.print_trace(2,'Qte line table - quantity '||px_qte_line_tbl(l_ql).quantity);
3112 okc_util.print_trace(2,'Qte line table - uom_code '||px_qte_line_tbl(l_ql).uom_code);
3113 okc_util.print_trace(2,'Qte line table - start_date_active'||px_qte_line_tbl(l_ql).start_date_active);
3114 okc_util.print_trace(2,'Qte line table - currency_code '||px_qte_line_tbl(l_ql).currency_code);
3115 END IF;
3116
3117
3118 IF (l_debug = 'Y') THEN
3119 okc_util.print_trace(2,'Obtaining the rule(s)');
3120 END IF;
3121 -- Obtain the top line rules
3122
3123 FOR k IN l_kl_rule_tab.FIRST..l_kl_rule_tab.LAST LOOP
3124 IF (l_debug = 'Y') THEN
3125 okc_util.print_trace(2,'rule category '||l_kl_rule_tab(k).rule_information_category);
3126 okc_util.print_trace(2,'rule - rule tab cle_id '||l_kl_rule_tab(k).cle_id);
3127 okc_util.print_trace(2,'rule - line info tab line_id '||l_line_info_tab(i).line_id);
3128 END IF;
3129
3130 IF l_kl_rule_tab(k).cle_id = l_line_info_tab(i).line_id THEN
3131
3132 -- get rule information for line
3133
3134 IF l_kl_rule_tab(k).rule_information_category = g_rd_price THEN
3135 -- price list
3136 px_qte_line_tbl(l_ql).price_list_id := NVL(l_kl_rule_tab(k).object1_id1,px_qte_hdr_rec.price_list_id);
3137 IF (l_debug = 'Y') THEN
3138 okc_util.print_trace(2,'Qte line table - price list id '|| px_qte_line_tbl(l_ql).price_list_id);
3139 END IF;
3140 ELSIF l_kl_rule_tab(k).rule_information_category = g_rd_invrule THEN
3141 -- invoice rule
3142 px_qte_line_tbl(l_ql).invoicing_rule_id := NVL(l_kl_rule_tab(k).object1_id1,px_qte_hdr_rec.invoicing_rule_id);
3143 IF (l_debug = 'Y') THEN
3144 okc_util.print_trace(2,'Qte line table - inv rule id '|| px_qte_line_tbl(l_ql).invoicing_rule_id);
3145 END IF;
3146
3147 ELSIF l_kl_rule_tab(k).rule_information_category = g_rd_shipmtd THEN
3148 px_qte_ln_shipment_tbl(l_ql).ship_method_code := l_kl_rule_tab(k).rule_information_category;
3149 IF (l_debug = 'Y') THEN
3150 okc_util.print_trace(2,'Qte line table - ship_method_code '||px_qte_ln_shipment_tbl(l_ql).ship_method_code);
3151 END IF;
3152 -- shipment method
3153 ELSIF l_kl_rule_tab(k).rule_information_category = g_rd_shipto THEN
3154 l_k_ship_found := 'Y';
3155 END IF;
3156 END IF;
3157 END LOOP;
3158
3159 --
3160 -- obtain the bill to rule
3161 --
3162 IF (l_debug = 'Y') THEN
3163 okc_util.print_trace(2,'Obtaining the Billto rule');
3164 END IF;
3165 IF l_kl_bto_data_tab.FIRST IS NOT NULL THEN
3166 FOR k IN l_kl_bto_data_tab.FIRST..l_kl_bto_data_tab.LAST LOOP
3167 IF l_kl_bto_data_tab(k).cle_id = l_line_info_tab(i).line_id THEN
3168
3169 px_qte_line_tbl(l_ql).invoice_to_party_site_id := NVL(l_kl_bto_data_tab(k).party_site_id,px_qte_hdr_rec.invoice_to_party_site_id);
3170 -- px_qte_line_tbl(l_ql).invoice_to_party_id := NVL(l_kl_bto_data_tab(k).party_id,px_qte_hdr_rec.invoice_to_party_id);
3171
3172 IF (l_debug = 'Y') THEN
3173 okc_util.print_trace(2,'Qte line table - invoice_to_party_site_id '|| px_qte_line_tbl(l_ql).invoice_to_party_site_id);
3174 okc_util.print_trace(2,'Qte line table - invoice_to_party_id '|| px_qte_line_tbl(l_ql).invoice_to_party_id);
3175 END IF;
3176 END IF;
3177 END LOOP;
3178 END IF;
3179
3180 IF (l_debug = 'Y') THEN
3181 okc_util.print_trace(2,'Obtaining the ship to rule and operation code');
3182 END IF;
3183 --
3184 -- obtain the ship to rule and the operation code
3185 --
3186 IF l_kl_sto_data_tab.FIRST IS NOT NULL THEN
3187
3188 FOR k IN l_kl_sto_data_tab.FIRST..l_kl_sto_data_tab.LAST LOOP
3189
3190 IF (l_debug = 'Y') THEN
3191 OKC_UTIL.PRINT_TRACE(2,'ship to rule at line level found');
3192 END IF;
3193
3194 IF l_kl_sto_data_tab(k).cle_id = l_line_info_tab(i).line_id THEN
3195
3196 px_qte_ln_shipment_tbl(l_ql).ship_to_party_site_id := l_kl_sto_data_tab(k).party_site_id;
3197 px_qte_ln_shipment_tbl(l_ql).ship_to_cust_account_id := l_kl_sto_data_tab(k).cust_acct_id;
3198 px_qte_ln_shipment_tbl(l_ql).ship_to_party_id := NVL(l_kl_sto_data_tab(k).party_id,l_cust.object1_id1);
3199 px_qte_ln_shipment_tbl(l_ql).ship_to_address1 := l_kl_sto_data_tab(k).address1;
3200 px_qte_ln_shipment_tbl(l_ql).ship_to_address2 := l_kl_sto_data_tab(k).address2;
3201 px_qte_ln_shipment_tbl(l_ql).ship_to_address3 := l_kl_sto_data_tab(k).address3;
3202 px_qte_ln_shipment_tbl(l_ql).ship_to_address4 := l_kl_sto_data_tab(k).address4;
3203 px_qte_ln_shipment_tbl(l_ql).ship_to_city := l_kl_sto_data_tab(k).city;
3204 px_qte_ln_shipment_tbl(l_ql).ship_to_state := l_kl_sto_data_tab(k).state;
3205 px_qte_ln_shipment_tbl(l_ql).ship_to_province := l_kl_sto_data_tab(k).province;
3206 px_qte_ln_shipment_tbl(l_ql).ship_to_postal_code:= l_kl_sto_data_tab(k).postal_code;
3207 px_qte_ln_shipment_tbl(l_ql).ship_to_county := l_kl_sto_data_tab(k).county;
3208 px_qte_ln_shipment_tbl(l_ql).ship_to_country := l_kl_sto_data_tab(k).country;
3209 END IF;
3210
3211 OPEN c_q_shipment(px_qte_line_tbl(l_ql).quote_line_id);
3212
3213 FETCH c_q_shipment INTO l_value;
3214
3215 IF c_q_shipment%NOTFOUND THEN
3216 IF l_k_ship_found = 'Y' THEN
3217 px_qte_ln_shipment_tbl(l_ql).operation_code := g_aso_op_code_create;
3218 END IF;
3219 ELSE
3220 IF l_k_ship_found = 'Y' THEN
3221 px_qte_ln_shipment_tbl(l_ql).operation_code := g_aso_op_code_update;
3222 ELSE
3223 px_qte_ln_shipment_tbl(l_ql).operation_code := g_aso_op_code_delete;
3224 END IF;
3225 END IF;
3226 CLOSE c_q_shipment;
3227
3228 END LOOP;
3229 END IF;
3230
3231 --
3232 -- get covered item info if available to define the quote detail line
3233 -- get calculated quote line price
3234 --
3235
3236 l_cp_found := FALSE;
3237 l_line_skipped := FALSE;
3238
3239 IF l_covlvl_info_tab.first IS NOT NULL THEN
3240 FOR j IN l_covlvl_info_tab.first..l_covlvl_info_tab.last LOOP
3241 l_cp_found := FALSE;
3242
3243 IF l_covlvl_info_tab(j).line_tab_idx = i THEN
3244 l_cp_found := TRUE;
3245
3246 --
3247 -- define one quote detail line
3248 -- --
3249 l_dql := l_dql + 1;
3250 --
3251 px_qte_line_dtl_tbl(l_dql).qte_line_index := l_ql;
3252 px_qte_line_dtl_tbl(l_dql).service_duration := l_covlvl_info_tab(j).svc_duration;
3253 px_qte_line_dtl_tbl(l_dql).service_period := l_covlvl_info_tab(j).svc_period;
3254
3255 IF px_qte_line_tbl(l_ql).operation_code = g_aso_op_code_create THEN
3256
3257 px_qte_line_dtl_tbl(l_dql).operation_code := g_aso_op_code_create;
3258 -- parent line
3259 -- l_ql = px_qte_line_tbl(l_ql).line_number;
3260 px_qte_line_dtl_tbl(l_dql).qte_line_index := l_ql;
3261
3262 ELSIF
3263
3264 px_qte_line_tbl(l_ql).operation_code = g_aso_op_code_update THEN
3265
3266 OPEN c_quot_detl_line (px_qte_line_tbl(l_ql).quote_line_id);
3267 FETCH c_quot_detl_line INTO l_id1;
3268 IF c_quot_detl_line%FOUND THEN
3269 px_qte_line_dtl_tbl(l_dql).operation_code := g_aso_op_code_update;
3270 px_qte_line_dtl_tbl(l_dql).quote_line_detail_id := l_id1;
3271
3272 px_qte_line_dtl_tbl(l_dql).config_header_id := NULL;
3273 px_qte_line_dtl_tbl(l_dql).config_revision_num := NULL;
3274 px_qte_line_dtl_tbl(l_dql).config_item_id := NULL;
3275 px_qte_line_dtl_tbl(l_dql).complete_configuration_flag := NULL;
3276 px_qte_line_dtl_tbl(l_dql).valid_configuration_flag := NULL;
3277 px_qte_line_dtl_tbl(l_dql).component_code := NULL;
3278
3279 ELSE
3280 px_qte_line_dtl_tbl(l_dql).operation_code := g_aso_op_code_create;
3281 END IF;
3282 -- parent_line
3283 px_qte_line_dtl_tbl(l_dql).quote_line_id := px_qte_line_dtl_tbl(l_dql).quote_line_id;
3284
3285 END IF; -- px_qte_line_tbl(l_ql).operation_code = g_aso_op_code_create
3286
3287 IF l_covlvl_info_tab(i).line_type <> g_lt_suppline THEN
3288 px_qte_line_dtl_tbl(l_dql).service_ref_type_code := g_qte_ref_cp;
3289 --
3290 -- 02/26/01:Logic change in ASO API.
3291 -- service_ref_line_id has to be used instead of
3292 -- service_ref_system_id in case of a service line
3293 -- with a covered product
3294 px_qte_line_dtl_tbl(l_dql).service_ref_line_id := l_covlvl_info_tab(j).id1;
3295 ELSE
3296 px_qte_line_dtl_tbl(l_dql).service_ref_type_code := g_qte_ref_quote;
3297 --
3298 -- We need to retrieve the quote line NUMBER of the
3299 -- related License Product line
3300 --
3301 IF (l_debug = 'Y') THEN
3302 okc_util.print_trace(3, '>Look for Quote line number of the Lic Prod line');
3303 END IF;
3304 l_lp_found := FALSE;
3305 IF px_k2q_line_tbl.first IS NOT NULL THEN
3306
3307 FOR k IN px_k2q_line_tbl.first..px_k2q_line_tbl.last LOOP
3308
3309 IF px_k2q_line_tbl(k).k_line_id = l_covlvl_info_tab(j).id1 THEN
3310
3311 l_lp_found := TRUE;
3312
3313 IF l_lp_found THEN
3314
3315 IF px_qte_line_tbl(k).operation_code = g_aso_op_code_create THEN
3316 px_qte_line_dtl_tbl(l_dql).service_ref_qte_line_index:= px_qte_line_tbl(k).line_number;
3317 ELSE
3318 px_qte_line_dtl_tbl(l_dql).service_ref_line_id := px_qte_line_tbl(k).quote_line_id;
3319 END IF; -- px_qte_line_tbl(k).operation_code
3320
3321 END IF; -- IF l_lp_found
3322
3323 EXIT;
3324 END IF; --IF px_k2q_line_tbl(k).k_line_id
3325
3326 END LOOP; -- FOR k IN px_k2q_line_tbl
3327
3328 END IF; --IF px_k2q_line_tbl.first IS
3329
3330 END IF; -- IF l_covlvl_info_tab(i).line_type<>g_lt_suppline THEN
3331
3332 END IF; -- IF l_covlvl_info_tab(j).line_tab_idx = i THEN
3333
3334 END LOOP; -- FOR j IN l_covlvl_info_tab.first..last LOOP
3335
3336 END IF; -- IF l_covlvl_info_tab.first IS NOT NULL THEN
3337
3338 IF (l_debug = 'Y') THEN
3339 okc_util.print_trace(2,' ');
3340 okc_util.print_trace(2,'QDL ');
3341 okc_util.print_trace(2,'l_line_info_tab('||i||').config_item_type = '||l_line_info_tab(i).config_item_type);
3342 okc_util.print_trace(2,' ');
3343 END IF;
3344
3345
3346 IF NOT l_cp_found AND px_qte_line_tbl(l_ql).operation_code = g_aso_op_code_update AND
3347 l_line_info_tab(i).config_item_type NOT IN (g_okc_model_item,g_okc_base_item,g_okc_config_item) THEN
3348 --
3349 -- This is the case of a Non service, Non configurable item
3350 -- ie a standard item and no sub lines
3351 --
3352 -- Need to ensure that the original quote line had no quote detail line
3353 -- Need to retrieve the quote detail line of related quote parent line
3354 -- pointed by px_qte_line_tbl(l_ql).quote_line_id using a cursor on
3355 -- OKX_QUOTE_LINE_DETAILS_V
3356 --
3357 OPEN c_qdl(px_qte_line_tbl(l_ql).quote_line_id) ;
3358 FETCH c_qdl INTO l_id1;
3359 IF c_qdl%FOUND THEN
3360 l_dql := l_dql + 1;
3361 px_qte_line_dtl_tbl(l_dql).operation_code := g_aso_op_code_delete;
3362 px_qte_line_dtl_tbl(l_dql).quote_line_detail_id := l_id1;
3363 IF (l_debug = 'Y') THEN
3364 okc_util.print_trace(2,'case of a Non service, Non configurable item, deleting a qte dtl line');
3365 END IF;
3366 END IF;
3367 CLOSE c_qdl;
3368 END IF;
3369
3370
3371 IF NOT l_cp_found AND px_qte_line_tbl(l_ql).operation_code = g_aso_op_code_update AND
3372 l_line_info_tab(i).config_item_type IN (g_okc_model_item,g_okc_base_item,g_okc_config_item) THEN
3373 --
3374 -- This is the case of a Non service, Configurable item -- 'UPDATE'
3375 --
3376 -- Need to retrieve if the original quote line has any quote detail line using the c_qdl cursor
3377 -- ie. against the okx_quote_line_details_v
3378 --
3379 OPEN c_qdl(px_qte_line_tbl(l_ql).quote_line_id) ;
3380 FETCH c_qdl INTO l_id1;
3381 IF c_qdl%FOUND THEN -- The Quote detail line needs to be updated.
3382 --
3383 -- Need to check if there is any quote detail line against
3384 -- px_qte_line_dtl_tbl for the quote_line_id
3385 -- ie. check against the px_qte_line_dtl_tbl PL/SQL table
3386 --
3387 IF px_qte_line_dtl_tbl.FIRST IS NOT NULL THEN
3388 FOR k IN px_qte_line_dtl_tbl.FIRST..px_qte_line_dtl_tbl.LAST LOOP
3389 IF px_qte_line_dtl_tbl(k).quote_line_id = px_qte_line_tbl(l_ql).quote_line_id THEN
3390 l_qdl_a_found := 'Y';
3391 l_dql := k;
3392 IF (l_debug = 'Y') THEN
3393 okc_util.print_trace(2,'1a.case of a Non service,Configurable item, updating qte dtl line');
3394 END IF;
3395 EXIT;
3396 END IF;
3397 END LOOP;
3398 END IF;
3399 IF l_qdl_a_found = 'N' THEN -- The previous check did'nt find any entry in the PL/SQLtable
3400 i_l_dql := l_dql + 1;
3401 l_dql := i_l_dql;
3402 IF (l_debug = 'Y') THEN
3403 okc_util.print_trace(2,'1a.case of a Non service,Configurable item, check against qte dtl line PL/SQL table didnot find any QDL');
3404 END IF;
3405 END IF;
3406
3407 px_qte_line_dtl_tbl(l_dql).operation_code := g_aso_op_code_update;
3408 px_qte_line_dtl_tbl(l_dql).quote_line_detail_id := l_id1;
3409 px_qte_line_dtl_tbl(l_dql).quote_line_id := px_qte_line_tbl(l_ql).quote_line_id;
3410
3411 px_qte_line_dtl_tbl(l_dql).config_header_id := l_line_info_tab(i).config_header_id;
3412 px_qte_line_dtl_tbl(l_dql).config_revision_num := l_line_info_tab(i).config_revision_number;
3413 px_qte_line_dtl_tbl(l_dql).config_item_id := l_line_info_tab(i).config_item_id;
3414 px_qte_line_dtl_tbl(l_dql).complete_configuration_flag := l_line_info_tab(i).config_complete_yn;
3415 px_qte_line_dtl_tbl(l_dql).valid_configuration_flag := l_line_info_tab(i).config_valid_yn;
3416 px_qte_line_dtl_tbl(l_dql).component_code := l_line_info_tab(i).component_code;
3417
3418 -- set the rest of the columns to null,since they are not valid anymore
3419
3420
3421 px_qte_line_dtl_tbl(l_dql).service_duration := NULL;
3422 px_qte_line_dtl_tbl(l_dql).service_period := NULL;
3423 px_qte_line_dtl_tbl(l_dql).service_ref_type_code := NULL;
3424 px_qte_line_dtl_tbl(l_dql).service_ref_line_number := NULL;
3425
3426
3427 IF (l_debug = 'Y') THEN
3428 okc_util.print_trace(2, ' ');
3429 okc_util.print_trace(2, 'Quote detail line values');
3430 okc_util.print_trace(2, ' ');
3431 END IF;
3432
3433 IF (l_debug = 'Y') THEN
3434 okc_util.print_trace(2,'operation_code '||px_qte_line_dtl_tbl(l_dql).operation_code);
3435 okc_util.print_trace(2,'qte_line_dtl_id '||px_qte_line_dtl_tbl(l_dql).quote_line_detail_id);
3436 okc_util.print_trace(2,'quote_line_id '||px_qte_line_dtl_tbl(l_dql).quote_line_id);
3437 okc_util.print_trace(2,'config_header_id '||px_qte_line_dtl_tbl(l_dql).config_header_id);
3438 okc_util.print_trace(2,'config_rev_num '||px_qte_line_dtl_tbl(l_dql).config_revision_num);
3439 okc_util.print_trace(2,'config_item_id '||px_qte_line_dtl_tbl(l_dql).config_item_id);
3440 okc_util.print_trace(2,'complete conf flag '||px_qte_line_dtl_tbl(l_dql).complete_configuration_flag);
3441 okc_util.print_trace(2,'valid_conf flag '||px_qte_line_dtl_tbl(l_dql).valid_configuration_flag);
3442 okc_util.print_trace(2,'component_code '||px_qte_line_dtl_tbl(l_dql).component_code);
3443 END IF;
3444
3445
3446 ELSE -- c_qdl%FOUND (i.e. quote detail line not found )
3447
3448 --
3449 -- Need to check if there is any quote detail line against
3450 -- px_qte_line_dtl_tbl for the quote_line_id
3451 -- ie. check against the px_qte_line_dtl_tbl PL/SQL table
3452 --
3453 IF px_qte_line_dtl_tbl.FIRST IS NOT NULL THEN
3454 FOR k IN px_qte_line_dtl_tbl.FIRST..px_qte_line_dtl_tbl.LAST LOOP
3455 IF px_qte_line_dtl_tbl(k).quote_line_id = px_qte_line_tbl(l_ql).quote_line_id THEN
3456 l_qdl_a_found := 'Y';
3457 l_dql := k;
3458 IF (l_debug = 'Y') THEN
3459 okc_util.print_trace(2,'Case of qdl not found in okx_quote_line_detail_v but found in the PL/SQL table');
3460 END IF;
3461 EXIT;
3462 END IF;
3463 END LOOP;
3464 END IF;
3465 IF l_qdl_a_found = 'N' THEN -- The previous check did'nt find any entry in the PL/SQLtable
3466 i_l_dql := l_dql + 1;
3467 l_dql := i_l_dql;
3468 IF (l_debug = 'Y') THEN
3469 okc_util.print_trace(2,'Case of qdl not found in neither okx_quote_line_detail_v nor the PL/SQL table');
3470 END IF;
3471 END IF;
3472
3473 px_qte_line_dtl_tbl(l_dql).operation_code := g_aso_op_code_create;
3474 px_qte_line_dtl_tbl(l_dql).quote_line_id := px_qte_line_tbl(l_ql).quote_line_id;
3475
3476 px_qte_line_dtl_tbl(l_dql).config_header_id := l_line_info_tab(i).config_header_id;
3477 px_qte_line_dtl_tbl(l_dql).config_revision_num := l_line_info_tab(i).config_revision_number;
3478 px_qte_line_dtl_tbl(l_dql).config_item_id := l_line_info_tab(i).config_item_id;
3479 px_qte_line_dtl_tbl(l_dql).complete_configuration_flag := l_line_info_tab(i).config_complete_yn;
3480 px_qte_line_dtl_tbl(l_dql).valid_configuration_flag := l_line_info_tab(i).config_valid_yn;
3481 px_qte_line_dtl_tbl(l_dql).component_code := l_line_info_tab(i).component_code;
3482
3483
3484 IF (l_debug = 'Y') THEN
3485 okc_util.print_trace(2,'operation_code '||px_qte_line_dtl_tbl(l_dql).operation_code);
3486 okc_util.print_trace(2,'quote_line_id '||px_qte_line_dtl_tbl(l_dql).quote_line_id);
3487 okc_util.print_trace(2,'config_header_id '||px_qte_line_dtl_tbl(l_dql).config_header_id);
3488 okc_util.print_trace(2,'config_rev num '||px_qte_line_dtl_tbl(l_dql).config_revision_num);
3489 okc_util.print_trace(2,'config_item_id '||px_qte_line_dtl_tbl(l_dql).config_item_id);
3490 okc_util.print_trace(2,'comp conf flag '||px_qte_line_dtl_tbl(l_dql).complete_configuration_flag);
3491 okc_util.print_trace(2,'valid conf flag '||px_qte_line_dtl_tbl(l_dql).valid_configuration_flag);
3492 okc_util.print_trace(2,'component_code '||px_qte_line_dtl_tbl(l_dql).component_code);
3493 END IF;
3494
3495
3496 END IF; -- IF c_qdl%FOUND THEN
3497
3498 END IF; -- IF NOT l_cp_found AND px_qte_line_tbl(l_ql).operation_code = g_aso_op_code_update
3499 -- l_line_info_tab(i).config_item_type IN (g_okc ....
3500
3501
3502
3503 IF NOT l_cp_found AND px_qte_line_tbl(l_ql).operation_code = g_aso_op_code_create AND
3504 l_line_info_tab(i).config_item_type IN (g_okc_model_item,g_okc_base_item,g_okc_config_item) THEN
3505 --
3506 -- This is the case of a Non service, Configurable item -- 'CREATE'
3507 --
3508 --
3509 -- Need to retrieve if the quote line has any quote detail line against
3510 -- px_qte_line_dtl_tbl for the quote_line_id
3511 -- ie. check against the px_qte_line_dtl_tbl PL/SQL table
3512 -- for the quote line index = l_ql
3513 --
3514 IF px_qte_line_dtl_tbl.FIRST IS NOT NULL THEN
3515 FOR k IN px_qte_line_dtl_tbl.FIRST..px_qte_line_dtl_tbl.LAST LOOP
3516 IF px_qte_line_dtl_tbl(k).qte_line_index = l_ql THEN
3517 l_qdl_b_found := 'Y';
3518 l_dql := k;
3519 IF (l_debug = 'Y') THEN
3520 okc_util.print_trace(2,'Case of Non service, Configurable item, entry found in PL/SQL table - create QDL ');
3521 END IF;
3522 px_qte_line_dtl_tbl(l_dql).operation_code := g_aso_op_code_create;
3523 px_qte_line_dtl_tbl(l_dql).qte_line_index := l_ql;
3524
3525 px_qte_line_dtl_tbl(l_dql).config_header_id := l_line_info_tab(i).config_header_id;
3526 px_qte_line_dtl_tbl(l_dql).config_revision_num := l_line_info_tab(i).config_revision_number;
3527 px_qte_line_dtl_tbl(l_dql).config_item_id := l_line_info_tab(i).config_item_id;
3528 px_qte_line_dtl_tbl(l_dql).complete_configuration_flag := l_line_info_tab(i).config_complete_yn;
3529 px_qte_line_dtl_tbl(l_dql).valid_configuration_flag := l_line_info_tab(i).config_valid_yn;
3530 px_qte_line_dtl_tbl(l_dql).component_code := l_line_info_tab(i).component_code;
3531
3532 IF (l_debug = 'Y') THEN
3533 okc_util.print_trace(2,'operation_code '||px_qte_line_dtl_tbl(l_dql).operation_code);
3534 okc_util.print_trace(2,'quote_line_idx '||px_qte_line_dtl_tbl(l_dql).qte_line_index);
3535 okc_util.print_trace(2,'config_header_id '||px_qte_line_dtl_tbl(l_dql).config_header_id);
3536 okc_util.print_trace(2,'config_rev num '||px_qte_line_dtl_tbl(l_dql).config_revision_num);
3537 okc_util.print_trace(2,'config_item_id '||px_qte_line_dtl_tbl(l_dql).config_item_id);
3538 okc_util.print_trace(2,'comp conf flag '||px_qte_line_dtl_tbl(l_dql).complete_configuration_flag);
3539 okc_util.print_trace(2,'valid conf flag '||px_qte_line_dtl_tbl(l_dql).valid_configuration_flag);
3540 okc_util.print_trace(2,'component_code '||px_qte_line_dtl_tbl(l_dql).component_code);
3541 END IF;
3542
3543 EXIT;
3544 END IF;
3545 END LOOP;
3546 END IF;
3547 IF l_qdl_b_found = 'N' THEN -- The previous check did'nt find any entry in the PL/SQLtable
3548 -- Need to create a quote detail line
3549 i_l_dql := l_dql + 1;
3550 l_dql := i_l_dql;
3551 px_qte_line_dtl_tbl(l_dql).operation_code := g_aso_op_code_create;
3552 px_qte_line_dtl_tbl(l_dql).qte_line_index := l_ql;
3553
3554 IF (l_debug = 'Y') THEN
3555 okc_util.print_trace(2,'Case of Non service, Configurable item,no entry in PL/SQL table - create QDL ');
3556 END IF;
3557
3558 px_qte_line_dtl_tbl(l_dql).config_header_id := l_line_info_tab(i).config_header_id;
3559 px_qte_line_dtl_tbl(l_dql).config_revision_num := l_line_info_tab(i).config_revision_number;
3560 px_qte_line_dtl_tbl(l_dql).config_item_id := l_line_info_tab(i).config_item_id;
3561 px_qte_line_dtl_tbl(l_dql).complete_configuration_flag := l_line_info_tab(i).config_complete_yn;
3562 px_qte_line_dtl_tbl(l_dql).valid_configuration_flag := l_line_info_tab(i).config_valid_yn;
3563 px_qte_line_dtl_tbl(l_dql).component_code := l_line_info_tab(i).component_code;
3564
3565 IF (l_debug = 'Y') THEN
3566 okc_util.print_trace(2,'operation_code '||px_qte_line_dtl_tbl(l_dql).operation_code);
3567 okc_util.print_trace(2,'quote_line_idx '||px_qte_line_dtl_tbl(l_dql).qte_line_index);
3568 okc_util.print_trace(2,'config_header_id '||px_qte_line_dtl_tbl(l_dql).config_header_id);
3569 okc_util.print_trace(2,'config_rev num '||px_qte_line_dtl_tbl(l_dql).config_revision_num);
3570 okc_util.print_trace(2,'config_item_id '||px_qte_line_dtl_tbl(l_dql).config_item_id);
3571 okc_util.print_trace(2,'comp conf flag '||px_qte_line_dtl_tbl(l_dql).complete_configuration_flag);
3572 okc_util.print_trace(2,'valid conf flag '||px_qte_line_dtl_tbl(l_dql).valid_configuration_flag);
3573 okc_util.print_trace(2,'component_code '||px_qte_line_dtl_tbl(l_dql).component_code);
3574 END IF;
3575 END IF;
3576
3577 END IF; -- IF NOT l_cp_found AND px_qte_line_tbl(l_ql).operation_code = g_aso_op_code_update
3578 -- l_line_info_tab(i).config_item_type IN (g_okc ....
3579
3580 --
3581 -- =========================
3582 -- UPDATE CONFIG INFORMATION
3583 -- =========================
3584 --
3585 IF (l_debug = 'Y') THEN
3586 okc_util.print_trace(2,' ');
3587 okc_util.print_trace(2,' UPDATING CONFIG INFORMATION ');
3588 okc_util.print_trace(2,' ');
3589 END IF;
3590
3591 IF l_line_info_tab(i).config_item_type = g_okc_model_item THEN
3592 px_qte_line_tbl(l_ql).item_type_code := g_aso_model_item; -- 'MDL'
3593
3594 ELSIF l_line_info_tab(i).config_item_type = g_okc_base_item THEN
3595 px_qte_line_tbl(l_ql).item_type_code := g_aso_model_item; -- 'MDL'
3596
3597 ELSIF l_line_info_tab(i).config_item_type = g_okc_config_item THEN
3598 px_qte_line_tbl(l_ql).item_type_code := g_aso_config_item; -- 'CFG'
3599
3600 ELSIF l_line_info_tab(i).config_item_type = g_okc_service_item THEN
3601 px_qte_line_tbl(l_ql).item_type_code := g_aso_service_item; -- 'SRV'
3602
3603 END IF;
3604
3605
3606
3607 l_cur_tl:=i;
3608 --
3609 -- calculate a quote line price, from the contract
3610 -- line(index i=l_cur_tl)
3611 --
3612 IF (l_debug = 'Y') THEN
3613 okc_util.print_trace(3, '--Quantity of the contract top line = '||l_line_info_tab(l_cur_tl).qty);
3614 okc_util.print_trace(3, '--Unit of Measure of the contract top line = '||l_line_info_tab(l_cur_tl).uom_code);
3615 END IF;
3616
3617 IF (l_debug = 'Y') THEN
3618 okc_util.print_trace(2,' ');
3619 okc_util.print_trace(2,' Calculating line list price and line adjusted amount ' );
3620 okc_util.print_trace(2,' ');
3621 END IF;
3622
3623 px_qte_line_tbl(l_ql).line_list_price := NVL(l_line_info_tab(l_cur_tl).price_unit,ROUND(l_line_info_tab(l_cur_tl).line_list_price / l_line_info_tab(l_cur_tl).qty, 2));
3624 IF (l_debug = 'Y') THEN
3625 okc_util.print_trace(3,' px_qte_line_tbl(l_ql).line_list_price = '|| px_qte_line_tbl(l_ql).line_list_price);
3626 END IF;
3627
3628 px_qte_line_tbl(l_ql).line_adjusted_amount := px_qte_line_tbl(l_ql).line_list_price-ROUND(l_line_info_tab(l_cur_tl).price_negotiated/l_line_info_tab(l_cur_tl).qty,2);
3629 IF (l_debug = 'Y') THEN
3630 okc_util.print_trace(3,' px_qte_line_tbl(l_ql).line_adjusted_amount = '|| px_qte_line_tbl(l_ql).line_adjusted_amount);
3631 END IF;
3632
3633 px_qte_line_tbl(l_ql).price_list_id := l_line_info_tab(l_cur_tl).price_list_id;
3634 IF (l_debug = 'Y') THEN
3635 okc_util.print_trace(3,' px_qte_line_tbl(l_ql).price_list_id = '|| px_qte_line_tbl(l_ql).price_list_id);
3636 END IF;
3637
3638 px_qte_line_tbl(l_ql).price_list_line_id := l_line_info_tab(l_cur_tl).price_list_line_id;
3639 IF (l_debug = 'Y') THEN
3640 okc_util.print_trace(3,' px_qte_line_tbl(l_ql).price_list_line_id = '||px_qte_line_tbl(l_ql).price_list_line_id);
3641 END IF;
3642
3643
3644 --
3645 -- record relation in the px_k2q_line_tbl PL/SQL table
3646 --
3647 IF (l_debug = 'Y') THEN
3648 okc_util.print_trace(2,' ');
3649 okc_util.print_trace(2,' Creating relation in px_k2q_line_tbl PL/SQL table ');
3650 okc_util.print_trace(2,' ');
3651 END IF;
3652
3653 px_k2q_line_tbl(l_ql).k_line_id := l_line_info_tab(l_cur_tl).line_id;
3654 px_k2q_line_tbl(l_ql).q_line_idx := l_ql;
3655
3656 IF l_line_info_tab(i).config_item_type IN ( g_okc_model_item, g_okc_base_item, g_okc_config_item ) THEN
3657 px_k2q_line_tbl(l_ql).k_parent_line_id := l_line_info_tab(l_cur_tl).cle_id;
3658 px_k2q_line_tbl(l_ql).q_item_type_code := px_qte_line_tbl(l_ql).item_type_code;
3659 END IF;
3660
3661 IF (l_debug = 'Y') THEN
3662 okc_util.print_trace(5, '-----------------------------');
3663 okc_util.print_trace(5, 'Quote line NUMBER = '||px_qte_line_tbl(l_ql).line_number);
3664 okc_util.print_trace(5, '-----------------------------');
3665 okc_util.print_trace(6, 'Quote line quantity = '||px_qte_line_tbl(l_ql).quantity);
3666 okc_util.print_trace(6, 'Quote line uom = '||px_qte_line_tbl(l_ql).uom_code);
3667 END IF;
3668 -- okc_util.print_trace(6, 'Quote line unit price = '||LTRIM(TO_CHAR(px_qte_line_tbl(l_ql).line_quote_price, '9G999G999G990D00')));
3669 -- okc_util.print_trace(6, 'Quote line price = '||LTRIM(TO_CHAR(px_qte_line_tbl(l_ql).line_quote_price*px_qte_line_tbl(l_ql).quantity, '9G999G999G990D00')));
3670
3671
3672 END IF; -- IF l_continue THEN
3673 END LOOP; --qteline
3674 END LOOP; --FOR c IN 1..2 LOOP
3675 END IF;
3676
3677 --
3678 -- Call the OKC_OC_INT_CONFIG_PVT.quote_line_relationship by passing the
3679 -- quote line table,quote line detail table and px_k2q_line_tbl
3680 -- to get the l_line_rltship_tbl, that contains information about
3681 -- relationship between quote lines.
3682 --
3683
3684
3685 IF (l_debug = 'Y') THEN
3686 okc_util.print_trace(2,' ');
3687 okc_util.print_trace(2,' Calling the OKC_OC_INT_CONFIG_PVT.quote_line_relationship procedure ');
3688 okc_util.print_trace(2,' ');
3689 END IF;
3690
3691
3692 OKC_OC_INT_CONFIG_PVT.quote_line_relationship(
3693 px_k2q_line_tbl,
3694 px_qte_line_tbl,
3695 px_qte_line_dtl_tbl,
3696 x_line_rltship_tab,
3697 x_return_status
3698 );
3699
3700
3701 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
3702
3703 print_error(3);
3704
3705 RAISE e_exit;
3706
3707 END IF;
3708
3709 IF (l_debug = 'Y') THEN
3710 okc_util.print_trace(2,' ');
3711 okc_util.print_trace(2,' Success - Calling the OKC_OC_INT_CONFIG_PVT.quote_line_relationship procedure ');
3712 okc_util.print_trace(2,' ');
3713 END IF;
3714
3715 -- Need to identify the quote lines that have to be deleted, for which
3716 -- the contract lines have already been deleted.
3717
3718 x := px_qte_line_tbl.COUNT;
3719 y := px_qte_ln_shipment_tbl.COUNT;
3720 z := px_qte_line_dtl_tbl.COUNT;
3721
3722 FOR c_ql_rec IN c_ql(g_quote_id) LOOP
3723
3724 FOR i IN px_qte_line_tbl.FIRST..px_qte_line_tbl.LAST LOOP
3725 IF px_qte_line_tbl(i).quote_line_id = c_ql_rec.id1 THEN
3726 EXIT;
3727 ELSE
3728
3729 x := x + 1;
3730 px_qte_line_tbl(x).operation_code := g_aso_op_code_delete;
3731 px_qte_line_tbl(x).quote_line_id := c_ql_rec.id1;
3732 px_qte_line_tbl(x).quote_header_id:= g_quote_id;
3733
3734 FOR c_sl_rec IN c_sl(c_ql_rec.id1) LOOP
3735
3736 y := y + 1;
3737 px_qte_ln_shipment_tbl(y).operation_code := g_aso_op_code_delete;
3738 px_qte_ln_shipment_tbl(y).quote_line_id := c_ql_rec.id1;
3739 px_qte_ln_shipment_tbl(y).quote_header_id:= g_quote_id;
3740 px_qte_ln_shipment_tbl(y).shipment_id := c_sl_rec.shipment_id;
3741 END LOOP;
3742
3743 FOR c_qdl_rec IN c_qdl(c_ql_rec.id1) LOOP
3744
3745 z := z + 1;
3746 px_qte_line_dtl_tbl(z).operation_code := g_aso_op_code_delete;
3747 px_qte_line_dtl_tbl(z).quote_line_id := c_ql_rec.id1;
3748 px_qte_line_dtl_tbl(z).quote_line_detail_id := c_ql_rec.id1;
3749
3750 END LOOP;
3751 END IF;
3752 END LOOP;
3753 END LOOP;
3754
3755
3756 IF l_ql = 0 THEN
3757 IF (l_debug = 'Y') THEN
3758 okc_util.print_trace(2, ' ');
3759 okc_util.print_trace(2, '******************************************************************');
3760 okc_util.print_trace(3, 'NO quote lines: All contract top lines have been discarded');
3761 okc_util.print_trace(2, '******************************************************************');
3762 END IF;
3763 okc_api.set_message(G_APP_NAME1,
3764 'OKO_K2Q_NOORDLINES',
3765 'KNUMBER',
3766 l_chr.contract_number);
3767 x_return_status := OKC_API.G_RET_STS_ERROR;
3768 print_error(4);
3769 --RAISE e_exit;
3770 ELSE
3771 x_return_status := OKC_API.G_RET_STS_SUCCESS;
3772 END IF;
3773
3774 --okc_util.print_trace(2, ' ');
3775 --okc_util.print_trace(2, '=====================================');
3776 --okc_util.print_trace(2, 'Total Quote Price = '||LTRIM(TO_CHAR(x_total_price, '9G999G999G990D00')));
3777 --okc_util.print_trace(2, '=====================================');
3778 IF (l_debug = 'Y') THEN
3779 okc_util.print_trace(1, ' ');
3780 okc_util.print_trace(1, 'INPUT RECORD FOR QUOTE CREATION - Quote Lines:');
3781 okc_util.print_trace(1, '==============================================');
3782 END IF;
3783 IF px_qte_line_tbl.first IS NOT NULL THEN
3784 FOR i IN px_qte_line_tbl.first..px_qte_line_tbl.last LOOP
3785 IF (l_debug = 'Y') THEN
3786 okc_util.print_trace(2, '> Org Id = '||px_qte_line_tbl(i).org_id);
3787 okc_util.print_trace(2, 'Quote Line NUMBER = '||px_qte_line_tbl(i).line_number);
3788 okc_util.print_trace(2, 'Quote Line category code = '||ltrim(rtrim(px_qte_line_tbl(i).line_category_code)));
3789 okc_util.print_trace(2, 'Item type code = '||px_qte_line_tbl(i).item_type_code);
3790 okc_util.print_trace(2, 'Start date active = '||px_qte_line_tbl(i).start_date_active);
3791 okc_util.print_trace(2, 'End date active = '||px_qte_line_tbl(i).end_date_active);
3792 okc_util.print_trace(2, 'Price List Id = '||px_qte_line_tbl(i).price_list_id);
3793 okc_util.print_trace(2, 'Inv Rule Id = '||px_qte_line_tbl(i).invoicing_rule_id);
3794 okc_util.print_trace(2, 'Inv To Party Id = '||px_qte_line_tbl(i).invoice_to_party_id);
3795 okc_util.print_trace(2, 'Inv To Party site Id = '||px_qte_line_tbl(i).invoice_to_party_site_id);
3796 okc_util.print_trace(2, 'Inv Item Id = '||px_qte_line_tbl(i).inventory_item_id);
3797 okc_util.print_trace(2, 'Organization Id = '||px_qte_line_tbl(i).organization_id);
3798 okc_util.print_trace(2, 'Quantity = '||px_qte_line_tbl(i).quantity);
3799 okc_util.print_trace(2, 'UOM = '||px_qte_line_tbl(i).uom_code);
3800 okc_util.print_trace(2, 'Currency code = '||px_qte_line_tbl(i).currency_code);
3801 okc_util.print_trace(2, 'Quote line unit price = '||LTRIM(TO_CHAR(px_qte_line_tbl(i).line_quote_price, '9G999G999G990D00')));
3802 okc_util.print_trace(2, 'Quote line price = '||LTRIM(TO_CHAR(px_qte_line_tbl(i).line_quote_price*px_qte_line_tbl(i).quantity, '9G999G999G990D99')));
3803 okc_util.print_trace(2, '---------------------------------------');
3804 okc_util.print_trace(2, 'Quote Header Id = '||px_qte_line_tbl(i).quote_header_id);
3805 okc_util.print_trace(2, 'Quote Line Id = '||px_qte_line_tbl(i).quote_line_id);
3806 END IF;
3807
3808 IF px_qte_line_dtl_tbl.first IS NOT NULL THEN
3809 IF (l_debug = 'Y') THEN
3810 okc_util.print_trace(3, ' ');
3811 okc_util.print_trace(3, 'INPUT RECORD FOR QUOTE CREATION - Quote Detail Lines:');
3812 okc_util.print_trace(3, '===============================================');
3813 END IF;
3814 l_nb_qte_line_dtl:=0;
3815 FOR j IN px_qte_line_dtl_tbl.first..px_qte_line_dtl_tbl.last LOOP
3816
3817 IF px_qte_line_dtl_tbl(j).qte_line_index = px_qte_line_tbl(i).line_number THEN
3818
3819
3820 l_nb_qte_line_dtl:=l_nb_qte_line_dtl + 1;
3821
3822 IF (l_debug = 'Y') THEN
3823 okc_util.print_trace(4, '>> Quote Line NUMBER = '||px_qte_line_dtl_tbl(j).qte_line_index);
3824 okc_util.print_trace(4, 'Service Ref type code = '||px_qte_line_dtl_tbl(j).service_ref_type_code);
3825 okc_util.print_trace(4, 'Service Ref Syst Id = '||px_qte_line_dtl_tbl(j).service_ref_system_id);
3826 okc_util.print_trace(4, 'Service Ref Line Id = '||px_qte_line_dtl_tbl(j).service_ref_line_id);
3827 END IF;
3828 --okc_util.print_trace(4, 'Service Ref Line Num = '||px_qte_line_dtl_tbl(j).service_ref_line_number);
3829 IF (l_debug = 'Y') THEN
3830 okc_util.print_trace(4, 'Service Ref Line Num = '||px_qte_line_dtl_tbl(j).service_ref_qte_line_index);
3831 okc_util.print_trace(4, 'Service Ref Qte Line Idx = '||px_qte_line_dtl_tbl(j).service_ref_qte_line_index);
3832 okc_util.print_trace(4, 'Service Ref Order Num = '||px_qte_line_dtl_tbl(j).service_ref_order_number);
3833 okc_util.print_trace(4, 'Service duration = '||px_qte_line_dtl_tbl(j).service_duration);
3834 okc_util.print_trace(4, 'Service period = '||px_qte_line_dtl_tbl(j).service_period);
3835 END IF;
3836
3837 IF (l_debug = 'Y') THEN
3838 okc_util.print_trace(4, 'config_header_id = '||px_qte_line_dtl_tbl(j).config_header_id);
3839 okc_util.print_trace(4, 'config_rev num = '||px_qte_line_dtl_tbl(j).config_revision_num);
3840 okc_util.print_trace(4, 'config_item_id = '||px_qte_line_dtl_tbl(j).config_item_id);
3841 okc_util.print_trace(4, 'comp conf flag = '||px_qte_line_dtl_tbl(j).complete_configuration_flag);
3842 okc_util.print_trace(4, 'valid conf flag = '||px_qte_line_dtl_tbl(j).valid_configuration_flag);
3843 okc_util.print_trace(4, 'component_code = '||px_qte_line_dtl_tbl(j).component_code);
3844 END IF;
3845
3846
3847 IF (l_debug = 'Y') THEN
3848 okc_util.print_trace(4, '---------------------------------------');
3849 okc_util.print_trace(4, 'Quote Line Id = '||px_qte_line_dtl_tbl(j).quote_line_id);
3850 okc_util.print_trace(4, 'Quote Detail Line Id = '||px_qte_line_dtl_tbl(j).quote_line_detail_id);
3851 END IF;
3852
3853 END IF;
3854 END LOOP;
3855 IF l_nb_qte_line_dtl = 0 THEN
3856 IF (l_debug = 'Y') THEN
3857 okc_util.print_trace(4, 'NO Quote Detail Lines');
3858 END IF;
3859 END IF;
3860 ELSE
3861 IF (l_debug = 'Y') THEN
3862 okc_util.print_trace(3, ' ');
3863 okc_util.print_trace(3, 'INPUT RECORD FOR QUOTE CREATION - Quote Detail Lines:');
3864 okc_util.print_trace(3, '=====================================================');
3865 okc_util.print_trace(4, 'NO Quote Detail Lines');
3866 END IF;
3867 END IF;
3868
3869 IF (l_debug = 'Y') THEN
3870 okc_util.print_trace(5, ' ');
3871 okc_util.print_trace(5, 'INPUT RECORD FOR RELATIONSHIP CREATION - Contract Line-Quote Line Relationship:');
3872 okc_util.print_trace(5, '=====================================================');
3873 END IF;
3874
3875 IF (l_debug = 'Y') THEN
3876 okc_util.print_trace(2,'px_k2q_tab_count '||px_k2q_line_tbl.count);
3877 END IF;
3878
3879 IF px_k2q_line_tbl.EXISTS(i) THEN
3880 IF (l_debug = 'Y') THEN
3881 okc_util.print_trace(6, 'Contract Line Id = '||px_k2q_line_tbl(i).k_line_id);
3882 okc_util.print_trace(6, 'Contract Line parent id = '||px_k2q_line_tbl(i).k_parent_line_id);
3883 okc_util.print_trace(6, 'Quote Line Index = '||px_k2q_line_tbl(i).q_line_idx);
3884 okc_util.print_trace(6, 'Quote Item type code = '||px_k2q_line_tbl(i).q_item_type_code);
3885 END IF;
3886 END IF;
3887 IF (l_debug = 'Y') THEN
3888 okc_util.print_trace(2, ' ');
3889 END IF;
3890
3891 END LOOP;
3892 ELSE
3893 IF (l_debug = 'Y') THEN
3894 okc_util.print_trace(2, 'NO Quote Lines');
3895 END IF;
3896 END IF;
3897 IF (l_debug = 'Y') THEN
3898 okc_util.print_trace(2, ' ');
3899 okc_util.print_trace(1, '<END - OKC_OC_INT_KTQ_PVT.BUILD_QTE_LINE -');
3900 END IF;
3901
3902 x_return_status := OKC_API.G_RET_STS_SUCCESS;
3903
3904 EXCEPTION
3905 WHEN e_exit THEN
3906 -- IF c_price%ISOPEN THEN
3907 -- CLOSE c_price;
3908 -- END IF;
3909 null;
3910 WHEN OTHERS THEN
3911 -- IF c_price%ISOPEN THEN
3912 -- CLOSE c_price;
3913 -- END IF;
3914 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
3915 -- notify caller of an UNEXPECTED error
3916 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
3917 --RAISE;
3918 END build_qte_line;
3919
3920 -------------------------------------------------------------------------------
3921 --
3922 -- public procedures
3923 --
3924 -- Procedure: update_quote_from_k
3925 -- Version: 1.0
3926 -- Purpose: Update a quote from a contract
3927 -- to a master contract
3928 -- Calls aso_quote_pub.CREATE_QUOTE to create the quote
3929 -- In Parameters: p_contract_id Contract for which to create quote
3930 -- p_quote_id Id of quote to be renewed
3931 --
3932 PROCEDURE update_quote_from_k( p_api_version IN NUMBER
3933 ,p_init_msg_list IN VARCHAR2
3934 ,p_quote_id IN OKX_QUOTE_HEADERS_V.ID1%TYPE
3935 ,p_contract_id IN OKC_K_HEADERS_B.ID%TYPE
3936 ,p_trace_mode IN VARCHAR2
3937 ,x_return_status OUT NOCOPY VARCHAR2
3938 ,x_msg_count OUT NOCOPY NUMBER
3939 ,x_msg_data OUT NOCOPY VARCHAR2
3940 )
3941 IS
3942
3943 -- standard api variables
3944 l_api_version CONSTANT NUMBER := 1;
3945 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_Q_FROM_K';
3946 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3947 l_return_status2 VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3948 l_msg_count NUMBER := 0;
3949 l_msg_data VARCHAR2(1000);
3950
3951 -- miscellaneous variables
3952 l_idx BINARY_INTEGER; -- generic table index
3953 m BINARY_INTEGER; -- generic table index
3954 l_qte_creation_message VARCHAR2(1000);
3955 l_nb_qte_line_dtl NUMBER;
3956 l_aso_api_version CONSTANT NUMBER := 1;
3957 l_init_msg_count NUMBER;
3958 --
3959 l_renew_rec okc_renew_pub.renew_in_parameters_rec;
3960 l_k_header_rec c_k_header%ROWTYPE;
3961 l_k_header_tl_rec c_k_header_tl%ROWTYPE;
3962 l_chrv_rec okc_contract_pub.chrv_rec_type;
3963 lx_chrv_rec okc_contract_pub.chrv_rec_type;
3964 l_rel_line_idx BINARY_INTEGER;
3965 l_k2q_line_rel_tab okc_oc_int_config_pvt.line_rel_tab_type; -- keeps track of k line to q line relation
3966 l_hdr_price NUMBER;
3967 x_total_price NUMBER;
3968
3969 x_line_rltship_tab ASO_QUOTE_PUB.line_rltship_tbl_type;
3970
3971 -- variables for calling create_quote
3972 l_control_rec ASO_QUOTE_PUB.control_rec_type;
3973 l_quote_header_rec ASO_QUOTE_PUB.qte_header_rec_type;
3974 l_quote_line_tab ASO_QUOTE_PUB.qte_line_tbl_type;
3975 l_quote_line_dtl_tab ASO_QUOTE_PUB.qte_line_dtl_tbl_type;
3976 l_quote_ln_shipment_tab ASO_QUOTE_PUB.shipment_tbl_type;
3977 l_hd_payment_tbl ASO_QUOTE_PUB.payment_tbl_type;
3978 l_quote_hd_shipment_tab ASO_QUOTE_PUB.shipment_tbl_type;
3979 l_hd_freight_charge_tbl ASO_QUOTE_PUB.freight_charge_tbl_type;
3980 l_hd_tax_detail_tbl ASO_QUOTE_PUB.tax_detail_tbl_type;
3981 l_line_attr_ext_tbl ASO_QUOTE_PUB.line_attribs_ext_tbl_type;
3982 l_line_rltship_tab ASO_QUOTE_PUB.line_rltship_tbl_type;
3983
3984 --
3985 l_quote_price_adj_tab ASO_QUOTE_PUB.price_adj_tbl_type;
3986 l_quote_ln_price_adj_tab ASO_QUOTE_PUB.price_adj_tbl_type;
3987
3988 l_quote_price_adj_attr_tab ASO_QUOTE_PUB.price_adj_attr_tbl_type;
3989 l_quote_ln_price_adj_attr_tab ASO_QUOTE_PUB.price_adj_attr_tbl_type;
3990
3991 l_quote_price_adj_rltship_tab ASO_QUOTE_PUB.price_adj_rltship_tbl_type;
3992 l_qt_ln_price_adj_rltship_tab ASO_QUOTE_PUB.price_adj_rltship_tbl_type;
3993
3994 l_quote_ln_price_attr_tab ASO_QUOTE_PUB.price_attributes_tbl_type;
3995 l_quote_hd_price_attr_tab ASO_QUOTE_PUB.price_attributes_tbl_type;
3996
3997 --
3998
3999 l_ln_payment_tbl ASO_QUOTE_PUB.payment_tbl_type;
4000 l_ln_tax_detail_tbl ASO_QUOTE_PUB.tax_detail_tbl_type;
4001
4002 l_hd_attr_ext_tbl ASO_QUOTE_PUB.line_attribs_ext_tbl_type;
4003 l_quote_hd_sales_credit_tab ASO_QUOTE_PUB.sales_credit_tbl_type;
4004 l_hd_quote_party_tbl ASO_QUOTE_PUB.quote_party_tbl_type;
4005 l_quote_ln_sales_credit_tab ASO_QUOTE_PUB.sales_credit_tbl_type;
4006 l_ln_quote_party_tbl ASO_QUOTE_PUB.quote_party_tbl_type;
4007
4008 lx_hd_attr_ext_tbl ASO_QUOTE_PUB.line_attribs_ext_tbl_type;
4009 lx_hd_sales_credit_tab ASO_QUOTE_PUB.sales_credit_tbl_type;
4010 lx_hd_quote_party_tbl ASO_QUOTE_PUB.quote_party_tbl_type;
4011 lx_ln_sales_credit_tab ASO_QUOTE_PUB.sales_credit_tbl_type;
4012 lx_ln_quote_party_tbl ASO_QUOTE_PUB.quote_party_tbl_type;
4013
4014 lx_qte_header_rec ASO_QUOTE_PUB.qte_header_rec_type;
4015 lx_qte_line_tbl ASO_QUOTE_PUB.qte_line_tbl_type;
4016 lx_qte_line_dtl_tbl ASO_QUOTE_PUB.qte_line_dtl_tbl_type;
4017 lx_hd_price_attributes_tbl ASO_QUOTE_PUB.price_attributes_tbl_type;
4018 lx_hd_payment_tbl ASO_QUOTE_PUB.payment_tbl_type;
4019 lx_hd_shipment_tbl ASO_QUOTE_PUB.shipment_tbl_type;
4020 lx_hd_freight_charge_tbl ASO_QUOTE_PUB.freight_charge_tbl_type;
4021 lx_hd_tax_detail_tbl ASO_QUOTE_PUB.tax_detail_tbl_type;
4022 lx_line_attr_ext_tbl ASO_QUOTE_PUB.line_attribs_ext_tbl_type;
4023 lx_line_rltship_tbl ASO_QUOTE_PUB.line_rltship_tbl_type;
4024 lx_price_adjustment_tbl ASO_QUOTE_PUB.price_adj_tbl_type;
4025 lx_price_adj_attr_tbl ASO_QUOTE_PUB.price_adj_attr_tbl_type;
4026 lx_price_adj_rltship_tbl ASO_QUOTE_PUB.price_adj_rltship_tbl_type;
4027 lx_ln_price_attributes_tbl ASO_QUOTE_PUB.price_attributes_tbl_type;
4028 lx_ln_payment_tbl ASO_QUOTE_PUB.payment_tbl_type;
4029 lx_ln_shipment_tbl ASO_QUOTE_PUB.shipment_tbl_type;
4030 lx_ln_tax_detail_tbl ASO_QUOTE_PUB.tax_detail_tbl_type;
4031 lx_ln_freight_charge_tbl ASO_QUOTE_PUB.freight_charge_tbl_type;
4032
4033
4034
4035 CALCULATE_TAX_FLAG VARCHAR2(1) := 'Y';
4036 CALCULATE_FREIGHT_CHARGE_FLAG VARCHAR2(1) := 'Y';
4037
4038
4039 BEGIN
4040
4041 --
4042 -- housekeeping
4043 --
4044
4045 l_quote_line_tab.DELETE;
4046 l_quote_line_dtl_tab.DELETE;
4047
4048 l_quote_hd_shipment_tab.DELETE;
4049 l_quote_ln_shipment_tab.DELETE;
4050
4051 l_k2q_line_rel_tab.DELETE;
4052 l_line_rltship_tab.DELETE;
4053 x_line_rltship_tab.DELETE;
4054
4055 l_quote_hd_sales_credit_tab.DELETE;
4056 l_quote_ln_sales_credit_tab.DELETE;
4057
4058 l_quote_price_adj_tab.DELETE;
4059 l_quote_ln_price_adj_tab.DELETE;
4060
4061 l_quote_price_adj_attr_tab.DELETE;
4062 l_quote_ln_price_adj_attr_tab.DELETE;
4063
4064 l_quote_price_adj_rltship_tab.DELETE;
4065 l_qt_ln_price_adj_rltship_tab.DELETE;
4066
4067 l_quote_ln_price_attr_tab.DELETE;
4068 l_quote_hd_price_attr_tab.DELETE;
4069
4070
4071 IF (l_debug = 'Y') THEN
4072 okc_util.print_trace(0, ' ');
4073 okc_util.print_trace(0, '>START - OKC_OC_INT_KTQ_PVT.CREATE_QUOTE_FROM_K -');
4074 okc_util.print_trace(0, ' ');
4075 okc_util.print_trace(0, ' ');
4076 okc_util.print_trace(0, '================================================');
4077 okc_util.print_trace(0, 'STEP 1 : FETCH AND LOCK CONTRACT, AND INITIALIZE CONTEXT');
4078 okc_util.print_trace(0, '================================================');
4079 okc_util.print_trace(0, ' ');
4080 END IF;
4081
4082 --
4083 -- housekeeping
4084 --
4085
4086 IF (l_debug = 'Y') THEN
4087 okc_util.print_trace(1, 'Initialize the message list');
4088 END IF;
4089 okc_api.init_msg_list(p_init_msg_list => p_init_msg_list);
4090 l_init_msg_count:=fnd_msg_pub.count_msg;
4091
4092 --
4093 -- fetch the contract
4094 --
4095 IF (l_debug = 'Y') THEN
4096 okc_util.print_trace(1,'The Input contract id is '||p_contract_id);
4097 END IF;
4098
4099 IF (l_debug = 'Y') THEN
4100 okc_util.print_trace(1, 'Fetch the contract');
4101 END IF;
4102 OPEN c_chr(p_contract_id);
4103 FETCH c_chr INTO l_chr;
4104 IF c_chr%NOTFOUND THEN
4105 -- no contract header is a fatal error
4106 okc_api.set_message(G_APP_NAME1,'OKO_K2Q_NOKHDRUPDT');
4107 CLOSE c_chr;
4108 x_return_status := OKC_API.G_RET_STS_ERROR;
4109 print_error(2);
4110 CLOSE c_chr;
4111 RAISE OKC_API.G_EXCEPTION_ERROR;
4112 END IF;
4113 CLOSE c_chr;
4114
4115 -- need this for error messages
4116 IF l_chr.contract_number_modifier IS NOT NULL THEN
4117 l_k_nbr := l_chr.contract_number||'-'||l_chr.contract_number_modifier;
4118 ELSE
4119 l_k_nbr := l_chr.contract_number;
4120 END IF;
4121
4122 IF (l_debug = 'Y') THEN
4123 okc_util.print_trace(1,'The contract number for the given contract id is '||l_k_nbr);
4124 END IF;
4125
4126 --
4127 -- lock the contract
4128 -- - to avoid a concurrent access to the contract for update, renewal...
4129 -- - to update contract comments
4130 --
4131
4132 IF (l_debug = 'Y') THEN
4133 okc_util.print_trace(1, 'Lock the contract');
4134 END IF;
4135 l_chrv_rec.id := p_contract_id;
4136 l_chrv_rec.object_version_number := l_chr.object_version_number;
4137 okc_contract_pub.lock_contract_header (
4138 p_api_version => 1,
4139 p_init_msg_list => OKC_API.G_FALSE,
4140 x_return_status => l_return_status,
4141 x_msg_count => l_msg_count,
4142 x_msg_data => l_msg_data,
4143 p_chrv_rec => l_chrv_rec);
4144
4145 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR OR
4146 l_return_status = OKC_API.G_RET_STS_ERROR THEN
4147 OKC_API.set_message(p_app_name => g_app_name,
4148 p_msg_name => 'OKC_K2Q_KLOCKED',
4149 p_token1 => 'NUMBER',
4150 p_token1_value => l_k_nbr);
4151 print_error(2);
4152 END IF; -- IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR OR
4153
4154 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4155 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4156 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4157 RAISE OKC_API.G_EXCEPTION_ERROR;
4158 END IF;
4159
4160 --
4161 -- set organization context
4162 --
4163
4164 IF (l_debug = 'Y') THEN
4165 okc_util.print_trace(1, 'Set the contract context');
4166 END IF;
4167 IF p_contract_id IS NULL THEN
4168 OKC_API.set_message(p_app_name => g_app_name,
4169 p_msg_name => 'OKC_K2Q_KIDISNULL');
4170 print_error(2);
4171 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4172 END IF;
4173 okc_context.set_okc_org_context(p_chr_id => p_contract_id);
4174 IF p_trace_mode = okc_api.g_true AND okc_util.l_trace_flag THEN
4175 okc_util.l_complete_trace_file_name2 := '- Trace file = '|| okc_util.l_complete_trace_file_name;
4176 ELSE
4177 okc_util.l_complete_trace_file_name2 := '- Request Id = '|| okc_util.l_request_id;
4178 END IF;
4179 IF p_trace_mode = okc_api.g_true AND okc_util.l_trace_flag THEN
4180 OKC_API.set_message(p_app_name => g_app_name,
4181 p_msg_name => 'OKC_K2Q_TRACEFILE',
4182 p_token1 => 'TRACEFILE',
4183 p_token1_value => okc_util.l_complete_trace_file_name2);
4184 END IF;
4185 --
4186 -- fetch the contract
4187 --
4188
4189 IF (l_debug = 'Y') THEN
4190 okc_util.print_trace(1, 'Fetch the contract');
4191 END IF;
4192 OPEN c_k_header(p_contract_id);
4193 FETCH c_k_header INTO l_k_header_rec;
4194 CLOSE c_k_header;
4195
4196 --
4197 -- Check up on contract eligibility for a quote creation
4198 --
4199
4200 IF (l_debug = 'Y') THEN
4201 okc_util.print_trace(0, ' ');
4202 okc_util.print_trace(0, ' ');
4203 okc_util.print_trace(0, '==========================================================');
4204 okc_util.print_trace(0, 'STEP 2 : CHECK CONTRACT ELIGIBILITY FOR THE QUOTE UPDATION');
4205 okc_util.print_trace(0, '==========================================================');
4206 okc_util.print_trace(0, ' ');
4207 END IF;
4208 validate_k_eligibility( l_k_header_rec,
4209 p_quote_id,
4210 l_return_status
4211 );
4212
4213 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4214 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4215 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4216 RAISE OKC_API.G_EXCEPTION_ERROR;
4217 END IF;
4218
4219
4220 --
4221 -- fetch the Quote
4222 --
4223 IF (l_debug = 'Y') THEN
4224 okc_util.print_trace(1,' ');
4225 okc_util.print_trace(1,'The Input quote id is '||g_quote_id);
4226 END IF;
4227
4228 IF (l_debug = 'Y') THEN
4229 okc_util.print_trace(1, 'Fetch the Quote');
4230 END IF;
4231 OPEN c_qhr(g_quote_id);
4232 FETCH c_qhr INTO l_qhr;
4233
4234 IF c_qhr%NOTFOUND THEN
4235 -- no quote header is a fatal error
4236 okc_api.set_message(G_APP_NAME1,'OKO_K2Q_NOQHDRUPDT');
4237 CLOSE c_qhr;
4238 x_return_status := OKC_API.G_RET_STS_ERROR;
4239 print_error(2);
4240 CLOSE c_qhr;
4241 RAISE OKC_API.G_EXCEPTION_ERROR;
4242 END IF;
4243 CLOSE c_qhr;
4244
4245 -- need this for error messages
4246 IF l_qhr.quote_version IS NOT NULL THEN
4247 l_q_nbr := l_qhr.quote_number||'-'||l_qhr.quote_version;
4248 ELSE
4249 l_q_nbr := l_qhr.quote_number;
4250 END IF;
4251
4252 IF (l_debug = 'Y') THEN
4253 okc_util.print_trace(1,'The quote number for the given quote id is '||l_q_nbr);
4254 END IF;
4255
4256
4257 --
4258 -- get the contract information
4259 --
4260
4261 IF (l_debug = 'Y') THEN
4262 okc_util.print_trace(0, ' ');
4263 okc_util.print_trace(0, ' ');
4264 okc_util.print_trace(0, '================================================');
4265 okc_util.print_trace(0, 'STEP 3 : BUILD CONTRACT STRUCTURES');
4266 okc_util.print_trace(0, '================================================');
4267 okc_util.print_trace(0, ' ');
4268 okc_util.print_trace(0, 'The Input contract is '||p_contract_id);
4269 okc_util.print_trace(0, 'The Input quote is '||g_quote_id);
4270 okc_util.print_trace(0, 'The Input relationship is '||p_rel_code);
4271 okc_util.print_trace(0, 'The contract category is '||l_k_header_rec.scs_code);
4272 okc_util.print_trace(0, ' ');
4273 END IF;
4274
4275 build_k_structures(p_contract_id,
4276 -- p_quote_id,
4277 p_rel_code,
4278 l_k_header_rec,
4279 l_return_status );
4280
4281 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4282 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4283 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4284 RAISE OKC_API.G_EXCEPTION_ERROR;
4285 END IF;
4286
4287 --
4288 -- populate quote header record
4289 --
4290
4291 IF (l_debug = 'Y') THEN
4292 okc_util.print_trace(0, ' ');
4293 okc_util.print_trace(0, ' ');
4294 okc_util.print_trace(0, '================================================');
4295 okc_util.print_trace(0, 'STEP 4 : BUILD QUOTE HEADER STRUCTURES');
4296 okc_util.print_trace(0, '================================================');
4297 okc_util.print_trace(0, ' ');
4298 END IF;
4299 build_qte_hdr(l_quote_header_rec,
4300 l_quote_hd_shipment_tab,
4301 p_contract_id,
4302 -- p_quote_id,
4303 p_rel_code,
4304 l_return_status);
4305
4306 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4307 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4308 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4309 RAISE OKC_API.G_EXCEPTION_ERROR;
4310 END IF;
4311
4312
4313 --
4314 -- populate quote lines table, line details
4315 --
4316
4317 IF (l_debug = 'Y') THEN
4318 okc_util.print_trace(0, ' ');
4319 okc_util.print_trace(0, ' ');
4320 okc_util.print_trace(0, '================================================');
4321 okc_util.print_trace(0, 'STEP 5 : BUILD QUOTE LINE AND QUOTE DETAIL LINE STRUCTURES');
4322 okc_util.print_trace(0, '================================================');
4323 okc_util.print_trace(0, ' ');
4324 END IF;
4325
4326 build_qte_line(
4327 p_contract_id
4328 ,l_quote_header_rec
4329 ,l_quote_line_tab
4330 ,l_quote_line_dtl_tab
4331 ,l_quote_ln_shipment_tab
4332 ,l_k2q_line_rel_tab
4333 -- ,x_total_price
4334 ,x_line_rltship_tab
4335 ,l_return_status);
4336
4337
4338 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4339 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4340 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4341 RAISE OKC_API.G_EXCEPTION_ERROR;
4342 END IF;
4343
4344 -- l_quote_header_rec.total_quote_price := x_total_price;
4345
4346 l_line_rltship_tab := x_line_rltship_tab;
4347
4348 --
4349 -- populate pricing information fom the pricing API
4350 --
4351
4352 IF (l_debug = 'Y') THEN
4353 okc_util.print_trace(0, ' ');
4354 okc_util.print_trace(0, ' ');
4355 okc_util.print_trace(0, '=============================================================');
4356 okc_util.print_trace(0, 'STEP 6 : BUILD PRICING INFORMATION FOR QUOTE HEADER AND LINES');
4357 okc_util.print_trace(0, '==============================================================');
4358 okc_util.print_trace(0, ' ');
4359 END IF;
4360
4361
4362 -- Displaying the values of relation table, quote line table and
4363 -- quote line shipment table
4364
4365
4366 IF l_k2q_line_rel_tab.FIRST IS NOT NULL THEN
4367 FOR m IN l_k2q_line_rel_tab.FIRST..l_k2q_line_rel_tab.LAST LOOP
4368 IF (l_debug = 'Y') THEN
4369 okc_util.print_trace(2,'contract line id '||l_k2q_line_rel_tab(m).k_line_id);
4370 okc_util.print_trace(2,'K parent line id '||l_k2q_line_rel_tab(m).k_parent_line_id);
4371 okc_util.print_trace(2,'Quote line index '||l_k2q_line_rel_tab(m).q_line_idx);
4372 okc_util.print_trace(2,'Quote itm typ cod'||l_k2q_line_rel_tab(m).q_item_type_code);
4373 END IF;
4374 END LOOP;
4375 END IF;
4376
4377 IF (l_debug = 'Y') THEN
4378 okc_util.print_trace(2, '================================================');
4379 END IF;
4380
4381 IF l_quote_line_tab.FIRST IS NOT NULL THEN
4382 FOR m IN l_quote_line_tab.FIRST..l_quote_line_tab.LAST LOOP
4383 IF (l_debug = 'Y') THEN
4384 okc_util.print_trace(2,'quote line id '||l_quote_line_tab(m).quote_line_id);
4385 okc_util.print_trace(2,'quote hdr id '||l_quote_line_tab(m).quote_header_id);
4386 okc_util.print_trace(2,'op code '||l_quote_line_tab(m).operation_code);
4387 okc_util.print_trace(2,'quote line # '||l_quote_line_tab(m).line_number);
4388 okc_util.print_trace(2,'Inv item id '||l_quote_line_tab(m).inventory_item_id);
4389 okc_util.print_trace(2,'quantity '||l_quote_line_tab(m).quantity);
4390 okc_util.print_trace(2,'UOM code '||l_quote_line_tab(m).uom_code);
4391 END IF;
4392 END LOOP;
4393 END IF;
4394
4395 OKC_OC_INT_PRICING_PVT.build_pricing_from_k(p_chr_id =>p_contract_id,
4396 p_kl_rel_tab =>l_k2q_line_rel_tab,
4397 --
4398 p_q_flag =>OKC_API.G_TRUE,
4399 p_qhr_id =>g_quote_id,
4400 p_qle_tab =>l_quote_line_tab,
4401 p_qle_shipment_tab =>l_quote_ln_shipment_tab,
4402 --
4403 x_hd_price_adj_tab =>l_quote_price_adj_tab,
4404 x_ln_price_adj_tab =>l_quote_ln_price_adj_tab,
4405 --
4406 x_hd_price_adj_attr_tab =>l_quote_price_adj_attr_tab,
4407 x_ln_price_adj_attr_tab =>l_quote_ln_price_adj_attr_tab,
4408 --
4409 x_hd_price_attr_tab =>l_quote_hd_price_attr_tab,
4410 x_ln_price_attr_tab =>l_quote_ln_price_attr_tab,
4411 --
4412 x_hd_price_adj_rltship_tab =>l_quote_price_adj_rltship_tab,
4413 x_ln_price_adj_rltship_tab =>l_qt_ln_price_adj_rltship_tab,
4414 --
4415 x_return_status => l_return_status );
4416
4417 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4418 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4419 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4420 RAISE OKC_API.G_EXCEPTION_ERROR;
4421 END IF;
4422
4423 IF (l_debug = 'Y') THEN
4424 okc_util.print_trace(0, 'count of l_quote_price_adj_tab = '||l_quote_price_adj_tab.count);
4425 okc_util.print_trace(0, 'count of l_quote_ln_price_adj_tab = '||l_quote_ln_price_adj_tab.count);
4426 okc_util.print_trace(0, ' ');
4427 okc_util.print_trace(0, 'count of l_quote_price_adj_attr_tab = '||l_quote_price_adj_attr_tab.count);
4428 okc_util.print_trace(0, 'count of l_quote_ln_price_adj_attr_tab = '||l_quote_ln_price_adj_attr_tab.count);
4429 okc_util.print_trace(0, ' ');
4430 okc_util.print_trace(0, 'count of l_quote_price_adj_rltship_tab = '||l_quote_price_adj_rltship_tab.count);
4431 okc_util.print_trace(0, 'count of l_quote_ln_price_adj_rltship_tab = '||l_qt_ln_price_adj_rltship_tab.count);
4432 okc_util.print_trace(0, ' ');
4433 okc_util.print_trace(0, 'count of l_quote_head_price_attr_tab = '||l_quote_hd_price_attr_tab.count);
4434 okc_util.print_trace(0, 'count of l_quote_line_price_attr_tab = '||l_quote_ln_price_attr_tab.count);
4435 END IF;
4436
4437 IF l_quote_ln_price_adj_tab.count > 0 THEN
4438 FOR i IN l_quote_ln_price_adj_tab.FIRST..l_quote_ln_price_adj_tab.LAST LOOP
4439 l_quote_price_adj_tab(l_quote_price_adj_tab.COUNT+1) := l_quote_ln_price_adj_tab(i);
4440 END LOOP;
4441 END IF;
4442
4443 IF l_quote_ln_price_adj_attr_tab.count > 0 THEN
4444 FOR i IN l_quote_ln_price_adj_attr_tab.FIRST..l_quote_ln_price_adj_attr_tab.LAST LOOP
4445 l_quote_price_adj_attr_tab(l_quote_price_adj_attr_tab.COUNT+1) := l_quote_ln_price_adj_attr_tab(i);
4446 END LOOP;
4447 END IF;
4448
4449 IF l_qt_ln_price_adj_rltship_tab.count > 0 THEN
4450 FOR i IN l_qt_ln_price_adj_rltship_tab.FIRST..l_qt_ln_price_adj_rltship_tab.LAST LOOP
4451 l_quote_price_adj_rltship_tab(l_quote_price_adj_rltship_tab.COUNT+1) := l_qt_ln_price_adj_rltship_tab(i);
4452 END LOOP;
4453 END IF;
4454
4455 IF (l_debug = 'Y') THEN
4456 okc_util.print_trace(0,'===========================================');
4457 okc_util.print_trace(0, 'count of l_quote_price_adj_tab = '||l_quote_price_adj_tab.count);
4458 okc_util.print_trace(0, ' ');
4459 okc_util.print_trace(0, 'count of l_quote_price_adj_attr_tab = '||l_quote_price_adj_attr_tab.count);
4460 okc_util.print_trace(0, ' ');
4461 okc_util.print_trace(0, 'count of l_quote_price_adj_rltship_tab = '||l_quote_price_adj_rltship_tab.count);
4462 okc_util.print_trace(0, ' ');
4463 okc_util.print_trace(0, 'count of l_quote_head_price_attr_tab = '||l_quote_hd_price_attr_tab.count);
4464 okc_util.print_trace(0, 'count of l_quote_line_price_attr_tab = '||l_quote_ln_price_attr_tab.count);
4465 okc_util.print_trace(0,'===========================================');
4466 END IF;
4467
4468 IF (l_debug = 'Y') THEN
4469 okc_util.print_trace(1,'====================================================================');
4470 okc_util.print_trace(1,'DISPLAYING THE PRICING PL/SQL TABLE INFO BEFORE CALLING UPDATE QUOTE');
4471 okc_util.print_trace(1,'====================================================================');
4472 okc_util.print_trace(1,' ');
4473 END IF;
4474
4475 IF (l_debug = 'Y') THEN
4476 okc_util.print_trace(1,'VALUES FROM l_quote_price_adj_tab');
4477 okc_util.print_trace(1,' ');
4478 END IF;
4479
4480 IF l_quote_price_adj_tab.count > 0 THEN
4481 FOR i IN l_quote_price_adj_tab.FIRST..l_quote_price_adj_tab.LAST LOOP
4482 IF (l_debug = 'Y') THEN
4483 okc_util.print_trace(1,'index value = '||i);
4484 okc_util.print_trace(1,'oper code = '||l_quote_price_adj_tab(i).operation_code);
4485 okc_util.print_trace(1,'Price adj id= '||l_quote_price_adj_tab(i).price_adjustment_id);
4486 okc_util.print_trace(1,'qte hdr id = '||l_quote_price_adj_tab(i).quote_header_id);
4487 okc_util.print_trace(1,'qte line id = '||l_quote_price_adj_tab(i).quote_line_id);
4488 okc_util.print_trace(1,'--------------------------------------------');
4489 END IF;
4490 END LOOP;
4491 END IF;
4492
4493 IF (l_debug = 'Y') THEN
4494 okc_util.print_trace(1,' ');
4495 okc_util.print_trace(1,'VALUES FROM l_quote_price_adj_attr_tab');
4496 okc_util.print_trace(1,' ');
4497 END IF;
4498
4499 IF l_quote_price_adj_attr_tab.count > 0 THEN
4500 FOR i IN l_quote_price_adj_attr_tab.FIRST..l_quote_price_adj_attr_tab.LAST LOOP
4501 IF (l_debug = 'Y') THEN
4502 okc_util.print_trace(1,'index value = '||i);
4503 okc_util.print_trace(1,'oper code = '||l_quote_price_adj_attr_tab(i).operation_code);
4504 okc_util.print_trace(1,'Price adj id= '||l_quote_price_adj_attr_tab(i).price_adjustment_id);
4505 okc_util.print_trace(1,'--------------------------------------------');
4506 END IF;
4507 END LOOP;
4508 END IF;
4509
4510
4511
4512 IF (l_debug = 'Y') THEN
4513 okc_util.print_trace(0, ' ');
4514 okc_util.print_trace(0, ' ');
4515 okc_util.print_trace(0, '==================================================================');
4516 okc_util.print_trace(0, 'STEP 7 : BUILD SALES CREDIT INFORMATION FOR QUOTE HEADER AND LINES');
4517 okc_util.print_trace(0, '==================================================================');
4518 okc_util.print_trace(0, ' ');
4519 okc_util.print_trace(0, ' ');
4520 END IF;
4521
4522 OKC_OC_INT_SALESCDT_PVT.build_sales_credit_from_k(p_chr_id =>p_contract_id,
4523 p_kl_rel_tab =>l_k2q_line_rel_tab,
4524 --
4525 p_q_flag =>OKC_API.G_TRUE,
4526 p_qhr_id =>g_quote_id,
4527 p_qle_tab =>l_quote_line_tab,
4528 --
4529 x_hd_sales_credit_tab =>l_quote_hd_sales_credit_tab,
4530 x_ln_sales_credit_tab =>l_quote_ln_sales_credit_tab,
4531 --
4532 x_return_status => l_return_status );
4533
4534 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4535 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4536 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4537 RAISE OKC_API.G_EXCEPTION_ERROR;
4538 END IF;
4539
4540 --
4541 -- set control record, need to set additional attributes
4542 --
4543
4544 IF (l_debug = 'Y') THEN
4545 okc_util.print_trace(0, ' ');
4546 okc_util.print_trace(0, 'Initialize control record');
4547 okc_util.print_trace(1, '--------------------------------------------------------');
4548 END IF;
4549 l_control_rec.last_update_date := sysdate;
4550
4551 IF (l_debug = 'Y') THEN
4552 okc_util.print_trace(1, '>START - ******* aso_quote_pub.UPDATE_QUOTE -');
4553 END IF;
4554
4555 aso_quote_pub.update_quote(p_api_version_number => l_aso_api_version
4556 ,p_init_msg_list => FND_API.G_FALSE
4557 ,p_commit => FND_API.G_FALSE
4558 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
4559 ,p_control_rec => l_control_rec
4560 --
4561 ,p_qte_header_rec => l_quote_header_rec
4562 --
4563 ,p_hd_price_attributes_tbl => l_quote_hd_price_attr_tab
4564 ,p_hd_payment_tbl => l_hd_payment_tbl
4565 ,p_hd_shipment_tbl => l_quote_hd_shipment_tab
4566 ,p_hd_freight_charge_tbl => l_hd_freight_charge_tbl
4567 ,p_hd_tax_detail_tbl => l_hd_tax_detail_tbl
4568 --
4569 ,p_hd_attr_ext_tbl => l_hd_attr_ext_tbl
4570 ,p_hd_sales_credit_tbl => l_quote_hd_sales_credit_tab
4571 ,p_hd_quote_party_tbl => l_hd_quote_party_tbl
4572 --
4573 ,p_qte_line_tbl => l_quote_line_tab
4574 ,p_qte_line_dtl_tbl => l_quote_line_dtl_tab
4575 --
4576 ,p_line_attr_ext_tbl => l_line_attr_ext_tbl
4577 ,p_line_rltship_tbl => l_line_rltship_tab
4578 --
4579 ,p_price_adjustment_tbl => l_quote_price_adj_tab
4580 ,p_price_adj_attr_tbl => l_quote_price_adj_attr_tab
4581 ,p_price_adj_rltship_tbl => l_quote_price_adj_rltship_tab
4582 ,p_ln_price_attributes_tbl => l_quote_ln_price_attr_tab
4583 --
4584 ,p_ln_payment_tbl => l_ln_payment_tbl
4585 ,p_ln_shipment_tbl => l_quote_ln_shipment_tab
4586 ,p_ln_freight_charge_tbl => l_hd_freight_charge_tbl
4587 ,p_ln_tax_detail_tbl => l_ln_tax_detail_tbl
4588 --
4589 ,p_ln_sales_credit_tbl => l_quote_ln_sales_credit_tab
4590 ,p_ln_quote_party_tbl => l_ln_quote_party_tbl
4591 --
4592 ,x_qte_header_rec => lx_qte_header_rec
4593 ,x_qte_line_tbl => lx_qte_line_tbl
4594 ,x_qte_line_dtl_tbl => lx_qte_line_dtl_tbl
4595 --
4596 ,x_hd_price_attributes_tbl => lx_hd_price_attributes_tbl
4597 ,x_hd_payment_tbl => lx_hd_payment_tbl
4598 ,x_hd_shipment_tbl => lx_hd_shipment_tbl
4599 ,x_hd_freight_charge_tbl => lx_hd_freight_charge_tbl
4600 ,x_hd_tax_detail_tbl => lx_hd_tax_detail_tbl
4601 --
4602 ,x_hd_attr_ext_tbl => lx_hd_attr_ext_tbl
4603 ,x_hd_sales_credit_tbl => lx_hd_sales_credit_tab
4604 ,x_hd_quote_party_tbl => lx_hd_quote_party_tbl
4605 --
4606 ,x_line_attr_ext_tbl => lx_line_attr_ext_tbl
4607 ,x_line_rltship_tbl => lx_line_rltship_tbl
4608 --
4609 ,x_price_adjustment_tbl => lx_price_adjustment_tbl
4610 ,x_price_adj_attr_tbl => lx_price_adj_attr_tbl
4611 ,x_price_adj_rltship_tbl => lx_price_adj_rltship_tbl
4612 --
4613 ,x_ln_price_attributes_tbl => lx_ln_price_attributes_tbl
4614 --
4615 ,x_ln_payment_tbl => lx_ln_payment_tbl
4616 ,x_ln_shipment_tbl => lx_ln_shipment_tbl
4617 ,x_ln_freight_charge_tbl => lx_ln_freight_charge_tbl
4618 ,x_ln_tax_detail_tbl => lx_ln_tax_detail_tbl
4619 --
4620 ,x_ln_sales_credit_tbl => lx_ln_sales_credit_tab
4621 ,x_ln_quote_party_tbl => lx_ln_quote_party_tbl
4622 --
4623 ,x_return_status => l_return_status
4624 ,x_msg_count => l_msg_count
4625 ,x_msg_data => l_msg_data
4626 );
4627
4628 ------------------------------------------------------------------------------------------------
4629 IF (l_debug = 'Y') THEN
4630 okc_util.print_trace(1, '*******************');
4631 END IF;
4632
4633 IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
4634
4635 FND_MSG_PUB.Count_And_Get (
4636 p_count => x_msg_count,
4637 p_data => x_msg_data);
4638
4639 FOR k in 1..x_msg_count LOOP
4640 x_msg_data := fnd_msg_pub.get( p_msg_index => k,
4641 p_encoded => 'F'
4642 );
4643 IF x_msg_data IS NOT NULL THEN
4644 IF (l_debug = 'Y') THEN
4645 okc_util.print_trace(0, 'Message : '||x_msg_data);
4646 okc_util.print_trace(0, ' ');
4647 END IF;
4648 END IF;
4649 END LOOP;
4650 END IF;
4651
4652 IF (l_debug = 'Y') THEN
4653 okc_util.print_trace(1, '*******************');
4654 END IF;
4655
4656 ------------------------------------------------------------------------------------------------
4657 IF (l_debug = 'Y') THEN
4658 okc_util.print_trace(1, '<END - ****** aso_quote_pub.UPDATE_QUOTE -');
4659 okc_util.print_trace(1, '--------------------------------------------------------');
4660 okc_util.print_trace(1, ' ');
4661 okc_util.print_trace(1, 'OUTPUT RECORD - Completion status:');
4662 okc_util.print_trace(1, '==================================');
4663 okc_util.print_trace(2, 'Return status = '||l_return_status);
4664 okc_util.print_trace(1, ' ');
4665 okc_util.print_trace(1, 'OUTPUT RECORD - Quote Header:');
4666 okc_util.print_trace(1, '=============================');
4667 okc_util.print_trace(2, 'Org_id = '||lx_qte_header_rec.org_id);
4668 okc_util.print_trace(2, 'Original syst ref (contract num) = '||ltrim(rtrim(lx_qte_header_rec.original_system_reference)));
4669 okc_util.print_trace(2, 'Quote name = '||ltrim(rtrim(lx_qte_header_rec.quote_name)));
4670 okc_util.print_trace(2, 'Quote version = '||lx_qte_header_rec.quote_version);
4671 okc_util.print_trace(2, 'Quote source code = '||ltrim(rtrim(lx_qte_header_rec.quote_source_code)));
4672 okc_util.print_trace(2, 'Quote category code = '||ltrim(rtrim(lx_qte_header_rec.quote_category_code)));
4673 okc_util.print_trace(2, 'Quote creation date= '||lx_qte_header_rec.creation_date);
4674 okc_util.print_trace(2, 'Quote expiration date= '||lx_qte_header_rec.quote_expiration_date);
4675 okc_util.print_trace(2, 'Party_id = '||lx_qte_header_rec.party_id);
4676 okc_util.print_trace(2, 'Cust Acct Id = '||lx_qte_header_rec.cust_account_id);
4677 okc_util.print_trace(2, 'Price List Id = '||lx_qte_header_rec.price_list_id);
4678 okc_util.print_trace(2, 'Inv Rule Id = '||lx_qte_header_rec.invoicing_rule_id);
4679 okc_util.print_trace(2, 'Inv To Party Id = '||lx_qte_header_rec.invoice_to_party_id);
4680 okc_util.print_trace(2, 'Inv To Party site Id = '||lx_qte_header_rec.invoice_to_party_site_id);
4681 okc_util.print_trace(2, 'Currency code = '||ltrim(rtrim(lx_qte_header_rec.currency_code)));
4682 okc_util.print_trace(2, 'Total quote price = '||LTRIM(TO_CHAR(lx_qte_header_rec.total_quote_price, '9G999G999G990D00')));
4683 okc_util.print_trace(2, 'Exchange type code = '||ltrim(rtrim(lx_qte_header_rec.exchange_type_code)));
4684 okc_util.print_trace(2, 'Exchange rate = '||lx_qte_header_rec.exchange_rate);
4685 okc_util.print_trace(2, 'Exchange rate date = '||lx_qte_header_rec.exchange_rate_date);
4686 okc_util.print_trace(2, '---------------------------------------');
4687 okc_util.print_trace(2, 'Quote header Id = '||lx_qte_header_rec.quote_header_id);
4688 okc_util.print_trace(2, 'Quote NUMBER = '||lx_qte_header_rec.quote_number);
4689 okc_util.print_trace(2, 'Quote status Id = '||lx_qte_header_rec.quote_status_id);
4690 okc_util.print_trace(2, 'Quote status code = '||ltrim(rtrim(lx_qte_header_rec.quote_status_code)));
4691 okc_util.print_trace(2, 'Quote status = '||ltrim(rtrim(lx_qte_header_rec.quote_status)));
4692 END IF;
4693
4694 IF (l_debug = 'Y') THEN
4695 okc_util.print_trace(1, ' ');
4696 okc_util.print_trace(1, 'OUTPUT RECORD - Quote Lines: '||lx_qte_line_tbl.count||' line(s)');
4697 okc_util.print_trace(1, '============================');
4698 END IF;
4699 IF lx_qte_line_tbl.first IS NOT NULL THEN
4700 FOR i IN lx_qte_line_tbl.first..lx_qte_line_tbl.last LOOP
4701 IF (l_debug = 'Y') THEN
4702 okc_util.print_trace(2, '> Org Id = '||lx_qte_line_tbl(i).org_id);
4703 okc_util.print_trace(2, 'Quote Line NUMBER = '||lx_qte_line_tbl(i).line_number);
4704 okc_util.print_trace(2, 'Quote Line category code = '||ltrim(rtrim(lx_qte_line_tbl(i).line_category_code)));
4705 okc_util.print_trace(2, 'Start date active = '||lx_qte_line_tbl(i).start_date_active);
4706 okc_util.print_trace(2, 'End date active = '||lx_qte_line_tbl(i).end_date_active);
4707 END IF;
4708 --
4709 IF (l_debug = 'Y') THEN
4710 okc_util.print_trace(2, 'Price List Id = '||lx_qte_line_tbl(i).price_list_id);
4711 okc_util.print_trace(2, 'Inv Rule Id = '||lx_qte_line_tbl(i).invoicing_rule_id);
4712 okc_util.print_trace(2, 'Inv To Party Id = '||lx_qte_line_tbl(i).invoice_to_party_id);
4713 okc_util.print_trace(2, 'Inv To Party site Id = '||lx_qte_line_tbl(i).invoice_to_party_site_id);
4714 END IF;
4715 --
4716 IF (l_debug = 'Y') THEN
4717 okc_util.print_trace(2, 'Inv Item Id = '||lx_qte_line_tbl(i).inventory_item_id);
4718 okc_util.print_trace(2, 'Organization Id = '||lx_qte_line_tbl(i).organization_id);
4719 okc_util.print_trace(2, 'Quantity = '||lx_qte_line_tbl(i).quantity);
4720 okc_util.print_trace(2, 'UOM = '||lx_qte_line_tbl(i).uom_code);
4721 okc_util.print_trace(2, 'Currency code = '||lx_qte_line_tbl(i).currency_code);
4722 okc_util.print_trace(2, 'Quote line unit price = '||LTRIM(TO_CHAR(lx_qte_line_tbl(i).line_quote_price, '9G999G999G990D00')));
4723 okc_util.print_trace(2, 'Quote line price = '||LTRIM(TO_CHAR(lx_qte_line_tbl(i).line_quote_price*lx_qte_line_tbl(i).quantity, '9G999G999G990D00')));
4724 okc_util.print_trace(2, '---------------------------------------');
4725 okc_util.print_trace(2, 'Quote Header Id = '||lx_qte_line_tbl(i).quote_header_id);
4726 okc_util.print_trace(2, 'Quote Line Id = '||lx_qte_line_tbl(i).quote_line_id);
4727 END IF;
4728
4729 IF lx_qte_line_dtl_tbl.first IS NOT NULL THEN
4730 IF (l_debug = 'Y') THEN
4731 okc_util.print_trace(1, ' ');
4732 okc_util.print_trace(3, 'OUTPUT RECORD - Quote Detail Lines:');
4733 okc_util.print_trace(3, '===================================');
4734 END IF;
4735 l_nb_qte_line_dtl:=0;
4736 FOR j IN lx_qte_line_dtl_tbl.first..lx_qte_line_dtl_tbl.last LOOP
4737 IF lx_qte_line_dtl_tbl(j).qte_line_index = lx_qte_line_tbl(i).line_number THEN
4738 l_nb_qte_line_dtl:=l_nb_qte_line_dtl+1;
4739 IF (l_debug = 'Y') THEN
4740 okc_util.print_trace(4, '>> Quote Line NUMBER = '||lx_qte_line_dtl_tbl(j).qte_line_index);
4741 okc_util.print_trace(4, 'Service Ref type code = '||lx_qte_line_dtl_tbl(j).service_ref_type_code);
4742 okc_util.print_trace(4, 'Service Ref Syst Id = '||lx_qte_line_dtl_tbl(j).service_ref_system_id);
4743 okc_util.print_trace(4, 'Service Ref Line Id = '||lx_qte_line_dtl_tbl(j).service_ref_line_id);
4744 END IF;
4745 --okc_util.print_trace(4, 'Service Ref Line Num = '||lx_qte_line_dtl_tbl(j).service_ref_line_number);
4746 IF (l_debug = 'Y') THEN
4747 okc_util.print_trace(4, 'Service Ref Line Num = '||lx_qte_line_dtl_tbl(j).service_ref_qte_line_index);
4748 okc_util.print_trace(4, 'Service Ref Qte Line Idx = '||lx_qte_line_dtl_tbl(j).service_ref_qte_line_index);
4749 okc_util.print_trace(4, 'Service Ref Order Num = '||lx_qte_line_dtl_tbl(j).service_ref_order_number);
4750 okc_util.print_trace(4, 'Service duration = '||lx_qte_line_dtl_tbl(j).service_duration);
4751 okc_util.print_trace(4, 'Service period = '||lx_qte_line_dtl_tbl(j).service_period);
4752 END IF;
4753
4754 IF (l_debug = 'Y') THEN
4755 okc_util.print_trace(4, 'config_header_id = '||lx_qte_line_dtl_tbl(j).config_header_id);
4756 okc_util.print_trace(4, 'config_rev num = '||lx_qte_line_dtl_tbl(j).config_revision_num);
4757 okc_util.print_trace(4, 'config_item_id = '||lx_qte_line_dtl_tbl(j).config_item_id);
4758 okc_util.print_trace(4, 'comp conf flag = '||lx_qte_line_dtl_tbl(j).complete_configuration_flag);
4759 okc_util.print_trace(4, 'valid conf flag = '||lx_qte_line_dtl_tbl(j).valid_configuration_flag);
4760 okc_util.print_trace(4, 'component_code = '||lx_qte_line_dtl_tbl(j).component_code);
4761 END IF;
4762
4763 IF (l_debug = 'Y') THEN
4764 okc_util.print_trace(4, '---------------------------------------');
4765 okc_util.print_trace(4, 'Quote Line Id = '||lx_qte_line_dtl_tbl(j).quote_line_id);
4766 okc_util.print_trace(4, 'Quote Detail Line Id = '||lx_qte_line_dtl_tbl(j).quote_line_detail_id);
4767 okc_util.print_trace(4, ' ');
4768 END IF;
4769 END IF;
4770 END LOOP;
4771 IF l_nb_qte_line_dtl=0 THEN
4772 IF (l_debug = 'Y') THEN
4773 okc_util.print_trace(4, 'NO Quote Detail Lines');
4774 END IF;
4775 END IF;
4776 ELSE
4777 IF (l_debug = 'Y') THEN
4778 okc_util.print_trace(3, ' ');
4779 okc_util.print_trace(3, 'OUTPUT RECORD - Quote Detail Lines:');
4780 okc_util.print_trace(3, '===================================');
4781 okc_util.print_trace(4, 'NO Quote Detail Lines');
4782 END IF;
4783 END IF;
4784 IF (l_debug = 'Y') THEN
4785 okc_util.print_trace(2, ' ');
4786 END IF;
4787 END LOOP qteline;
4788 ELSE
4789 IF (l_debug = 'Y') THEN
4790 okc_util.print_trace(2, 'NO Quote Lines');
4791 END IF;
4792 END IF;
4793 IF (l_debug = 'Y') THEN
4794 okc_util.print_trace(2, ' ');
4795 END IF;
4796
4797 --
4798 -- Contract updating with quote information waiting for
4799 -- a specific notification creation
4800 --
4801
4802 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR OR
4803 l_return_status = OKC_API.G_RET_STS_ERROR THEN
4804 --l_qte_updation_message :=
4805 -- 'An Error occurred while updating a quote' || ' ';
4806 --IF p_trace_mode = okc_api.g_true THEN
4807 -- l_qte_updation_message := l_qte_updation_message ||
4808 -- '- Trace file = '|| okc_util.l_complete_trace_file_name;
4809 --ELSE
4810 -- l_qte_updation_message := l_qte_updation_message ||
4811 -- '- Please try again with trace mode active';
4812 --END IF;
4813 lx_qte_header_rec.creation_date:=SYSDATE;
4814 ELSE
4815 --SELECT DECODE(lx_qte_header_rec.creation_date, fnd_api.g_miss_date,
4816 -- TRUNC(l_control_rec.last_update_date),
4817 -- lx_qte_header_rec.creation_date)
4818 --INTO lx_qte_header_rec.creation_date
4819 --FROM DUAL;
4820
4821 OKC_API.set_message(p_app_name => g_app_name,
4822 p_msg_name => 'OKC_K2Q_K2QCOMMENTS',
4823 p_token1 => 'CRDATE',
4824 p_token1_value => lx_qte_header_rec.creation_date,
4825 p_token2 => 'NUMBER',
4826 p_token2_value => lx_qte_header_rec.quote_number,
4827 p_token3 => 'VERSION',
4828 p_token3_value => lx_qte_header_rec.quote_version,
4829 p_token4 => 'EXDATE',
4830 p_token4_value => lx_qte_header_rec.quote_expiration_date,
4831 p_token5 => 'TRACEFILE',
4832 p_token5_value => okc_util.l_complete_trace_file_name2
4833 );
4834 FND_MSG_PUB.Count_And_Get (
4835 p_count => x_msg_count,
4836 p_data => x_msg_data);
4837 x_msg_data := fnd_msg_pub.get(
4838 p_msg_index => x_msg_count,
4839 p_encoded => 'F');
4840
4841 l_qte_creation_message := x_msg_data;
4842 FND_MSG_PUB.Delete_Msg ( p_msg_index => x_msg_count);
4843
4844 END IF;
4845 EXCEPTION
4846 WHEN OKC_API.G_EXCEPTION_ERROR THEN
4847 --update_k_comments_err;
4848 IF c_k_header%ISOPEN THEN
4849 CLOSE c_k_header;
4850 END IF;
4851 IF c_k_header_tl%ISOPEN THEN
4852 CLOSE c_k_header_tl;
4853 END IF;
4854 IF c_hdr_subject_to%ISOPEN THEN
4855 CLOSE c_hdr_subject_to;
4856 END IF;
4857 x_return_status := OKC_API.G_RET_STS_ERROR;
4858 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
4859 fnd_msg_pub.add_exc_msg(p_pkg_name => g_pkg_name
4860 ,p_procedure_name => l_api_name
4861 ,p_error_text => 'Encountered error condition'
4862 );
4863 END IF;
4864
4865 --Error messages for the trace file
4866 IF (l_debug = 'Y') THEN
4867 okc_util.print_trace(4, ' ');
4868 okc_util.print_trace(4, '==================================');
4869 okc_util.print_trace(5, 'Error while creating quote:');
4870 okc_util.print_trace(5, 'Return status: '||x_return_status);
4871 okc_util.print_trace(4, '==================================');
4872 END IF;
4873 --Error messages for the output file
4874 IF okc_util.l_output_flag THEN
4875 OKC_API.set_message(p_app_name => g_app_name,
4876 p_msg_name => 'OKC_K2Q_K2QOUTEMSG',
4877 p_token1 => 'CRDATE',
4878 p_token1_value => lx_qte_header_rec.creation_date,
4879 p_token2 => 'KNUMBER',
4880 p_token2_value => l_chr.contract_number,
4881 p_token3 => 'KMODIFIER',
4882 p_token3_value => NVL(l_chr.contract_number_modifier, ' ')
4883 );
4884 FND_MSG_PUB.Count_And_Get (
4885 p_count => x_msg_count,
4886 p_data => x_msg_data);
4887 x_msg_data := fnd_msg_pub.get(
4888 p_msg_index => x_msg_count,
4889 p_encoded => 'F');
4890
4891 l_qte_creation_message := x_msg_data;
4892 FND_MSG_PUB.Delete_Msg ( p_msg_index => x_msg_count);
4893
4894 okc_util.print_output(0, l_qte_creation_message);
4895 END IF;
4896 FND_MSG_PUB.Count_And_Get (
4897 p_count => x_msg_count,
4898 p_data => x_msg_data);
4899 FOR k in l_init_msg_count..x_msg_count LOOP
4900 x_msg_data := fnd_msg_pub.get( p_msg_index => k,
4901 p_encoded => 'F'
4902 );
4903 IF x_msg_data IS NOT NULL THEN
4904 IF (l_debug = 'Y') THEN
4905 okc_util.print_trace(5, 'Message : '||x_msg_data);
4906 okc_util.print_trace(5, ' ');
4907 END IF;
4908 IF okc_util.l_output_flag THEN
4909 okc_util.print_output(0, 'Message : '||x_msg_data);
4910 okc_util.print_output(0, ' ');
4911 END IF;
4912 END IF;
4913 END LOOP;
4914 IF (l_debug = 'Y') THEN
4915 okc_util.print_trace(4, '==================================');
4916 END IF;
4917 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4918 IF c_k_header%ISOPEN THEN
4919 CLOSE c_k_header;
4920 END IF;
4921 IF c_k_header_tl%ISOPEN THEN
4922 CLOSE c_k_header_tl;
4923 END IF;
4924 IF c_hdr_subject_to%ISOPEN THEN
4925 CLOSE c_hdr_subject_to;
4926 END IF;
4927 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
4928 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
4929 fnd_msg_pub.add_exc_msg(p_pkg_name => g_pkg_name
4930 ,p_procedure_name => l_api_name
4931 ,p_error_text => 'Encountered unexpected error'
4932 );
4933 END IF;
4934
4935 --Error messages for the trace file
4936 IF (l_debug = 'Y') THEN
4937 okc_util.print_trace(4, ' ');
4938 okc_util.print_trace(4, '==================================');
4939 okc_util.print_trace(5, 'Error while updating quote:');
4940 okc_util.print_trace(5, 'Return status: '||x_return_status);
4941 okc_util.print_trace(4, '==================================');
4942 END IF;
4943 --Error messages for the output file
4944 IF okc_util.l_output_flag THEN
4945 OKC_API.set_message(p_app_name => g_app_name,
4946 p_msg_name => 'OKC_K2Q_K2QOUTEMSG',
4947 p_token1 => 'CRDATE',
4948 p_token1_value => lx_qte_header_rec.creation_date,
4949 p_token2 => 'KNUMBER',
4950 p_token2_value => l_chr.contract_number,
4951 p_token3 => 'KMODIFIER',
4952 p_token3_value => NVL(l_chr.contract_number_modifier, ' ')
4953 );
4954 FND_MSG_PUB.Count_And_Get (
4955 p_count => x_msg_count,
4956 p_data => x_msg_data);
4957 x_msg_data := fnd_msg_pub.get(
4958 p_msg_index => x_msg_count,
4959 p_encoded => 'F');
4960
4961 l_qte_creation_message := x_msg_data;
4962 FND_MSG_PUB.Delete_Msg ( p_msg_index => x_msg_count);
4963
4964 okc_util.print_output(0, l_qte_creation_message);
4965 END IF;
4966 FND_MSG_PUB.Count_And_Get (
4967 p_count => x_msg_count,
4968 p_data => x_msg_data);
4969 FOR k in l_init_msg_count..x_msg_count LOOP
4970 x_msg_data := fnd_msg_pub.get( p_msg_index => k,
4971 p_encoded => 'F'
4972 );
4973 IF x_msg_data IS NOT NULL THEN
4974 IF (l_debug = 'Y') THEN
4975 okc_util.print_trace(5, 'Message : '||x_msg_data);
4976 okc_util.print_trace(5, ' ');
4977 END IF;
4978 IF okc_util.l_output_flag THEN
4979 okc_util.print_output(0, 'Message : '||x_msg_data);
4980 okc_util.print_output(0, ' ');
4981 END IF;
4982 END IF;
4983 END LOOP;
4984 IF (l_debug = 'Y') THEN
4985 okc_util.print_trace(4, '==================================');
4986 END IF;
4987 WHEN OTHERS THEN
4988 --update_k_comments_err;
4989 IF c_k_header%ISOPEN THEN
4990 CLOSE c_k_header;
4991 END IF;
4992 IF c_k_header_tl%ISOPEN THEN
4993 CLOSE c_k_header_tl;
4994 END IF;
4995 IF c_hdr_subject_to%ISOPEN THEN
4996 CLOSE c_hdr_subject_to;
4997 END IF;
4998 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
4999 OKC_API.set_message(G_APP_NAME
5000 ,G_UNEXPECTED_ERROR
5001 ,G_SQLCODE_TOKEN
5002 ,SQLCODE
5003 ,G_SQLERRM_TOKEN
5004 ,SQLERRM);
5005
5006 --Error messages for the trace file
5007 IF (l_debug = 'Y') THEN
5008 okc_util.print_trace(4, ' ');
5009 okc_util.print_trace(4, '==================================');
5010 okc_util.print_trace(5, 'Error while updating quote:');
5011 okc_util.print_trace(5, 'Return status: '||x_return_status);
5012 okc_util.print_trace(4, '==================================');
5013 END IF;
5014 --Error messages for the output file
5015 IF okc_util.l_output_flag THEN
5016 OKC_API.set_message(p_app_name => g_app_name,
5017 p_msg_name => 'OKC_K2Q_K2QOUTEMSG',
5018 p_token1 => 'CRDATE',
5019 p_token1_value => lx_qte_header_rec.creation_date,
5020 p_token2 => 'KNUMBER',
5021 p_token2_value => l_chr.contract_number,
5022 p_token3 => 'KMODIFIER',
5023 p_token3_value => NVL(l_chr.contract_number_modifier, ' ')
5024 );
5025 FND_MSG_PUB.Count_And_Get (
5026 p_count => x_msg_count,
5027 p_data => x_msg_data);
5028 x_msg_data := fnd_msg_pub.get(
5029 p_msg_index => x_msg_count,
5030 p_encoded => 'F');
5031
5032 l_qte_creation_message := x_msg_data;
5033 FND_MSG_PUB.Delete_Msg ( p_msg_index => x_msg_count);
5034
5035 okc_util.print_output(0, l_qte_creation_message);
5036 END IF;
5037 FND_MSG_PUB.Count_And_Get (
5038 p_count => x_msg_count,
5039 p_data => x_msg_data);
5040 FOR k in l_init_msg_count..x_msg_count LOOP
5041 x_msg_data := fnd_msg_pub.get( p_msg_index => k,
5042 p_encoded => 'F'
5043 );
5044 IF x_msg_data IS NOT NULL THEN
5045 IF (l_debug = 'Y') THEN
5046 okc_util.print_trace(5, 'Message : '||x_msg_data);
5047 okc_util.print_trace(5, ' ');
5048 END IF;
5049 IF okc_util.l_output_flag THEN
5050 okc_util.print_output(0, 'Message : '||x_msg_data);
5051 okc_util.print_output(0, ' ');
5052 END IF;
5053 END IF;
5054 END LOOP;
5055 IF (l_debug = 'Y') THEN
5056 okc_util.print_trace(4, '==================================');
5057 END IF;
5058
5059 END update_quote_from_k;
5060 --
5061 -- initialization section
5062 --
5063
5064 BEGIN
5065 --
5066 IF (l_debug = 'Y') THEN
5067 okc_util.print_trace(0,'Starting OKC_OC_INT_KTQ_PVT Initialization');
5068 okc_util.print_trace(0,'==========================================');
5069 END IF;
5070
5071 -- load the table with the line styles that need to be processed
5072 -- in a specific manner while creating a quote or an order
5073 --
5074 l_line_with_cover_prod_qc_tab(1) := g_lt_ext_warr;
5075 l_line_with_cover_prod_qc_tab(2) := g_lt_service;
5076 l_line_with_cover_prod_qc_tab(3) := g_lt_support;
5077
5078 END OKC_OC_INT_KTQ_PVT;