[Home] [Help]
PACKAGE BODY: APPS.OKS_OC_INT_KTO_PVT
Source
1 PACKAGE BODY OKS_OC_INT_KTO_PVT AS
2 /* $Header: OKSRKTOB.pls 120.4 2007/12/24 07:25:32 rriyer ship $ */
3
4 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKC_CONTRACTS_UNEXP_ERROR';
5 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'SQLCODE';
6 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'SQLERRM';
7 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKS_OC_INT_KTO_PVT';
8 G_APP_NAME CONSTANT VARCHAR2(3) := OKC_API.G_APP_NAME;
9 G_APP_NAME1 CONSTANT VARCHAR2(3) := 'OKS';
10 G_EXCEPTION_HALT_VALIDATION EXCEPTION;
11 G_API_TYPE CONSTANT VARCHAR2(30) := '_PROCESS';
12 G_SCOPE CONSTANT VARCHAR2(4) := '_PVT';
13
14 -- other constants
15
16 g_okx_parties_v CONSTANT VARCHAR2(30):= 'OKX_PARTIES_V';
17 g_rd_price CONSTANT VARCHAR2(30) := 'PRE';
18 g_rd_shipmtd CONSTANT VARCHAR2(30) := 'SMD';
19 --
20 g_qte_source_c CONSTANT VARCHAR2(30) := 'CTRT_COPY';
21
22 --
23 -- global cursors
24 --
25 -- cursor for contract header information
26 --
27 CURSOR c_chr (p_chr_id NUMBER) IS
28 SELECT
29 object_version_number
30 ,authoring_org_id
31 ,inv_organization_id
32 ,contract_number
33 ,contract_number_modifier
34 ,currency_code
35 ,estimated_amount
36 ,date_renewed
37 ,scs_code
38 ,total_line_list_price
39 ,price_list_id
40 FROM okc_k_headers_b
41 WHERE id = p_chr_id;
42
43 -- 4915691 --
44 -- Cursor for contract line information
45 --
46 CURSOR c_cle(p_cle_id NUMBER) IS SELECT
47 object_version_number
48 FROM okc_k_lines_b
49 WHERE id = p_cle_id;
50 -- 4915691 --
51
52
53 CURSOR c_k_header(p_chr_id NUMBER) IS
54 SELECT
55 kh.ID ,
56 kh.SCS_CODE ,
57 kh.CONTRACT_NUMBER ,
58 kh.CURRENCY_CODE ,
59 kh.CONTRACT_NUMBER_MODIFIER,
60 kh.TEMPLATE_YN ,
61 kh.TEMPLATE_USED ,
62 kh.CHR_TYPE ,
63 kh.DATE_TERMINATED ,
64 kh.DATE_RENEWED ,
65 kh.START_DATE ,
66 kh.END_DATE ,
67 kh.AUTHORING_ORG_ID ,
68 kh.INV_ORGANIZATION_ID ,
69 kh.BUY_OR_SELL ,
70 kh.ISSUE_OR_RECEIVE ,
71 kh.ESTIMATED_AMOUNT ,
72 ks.cls_code ,
73 ks.meaning ,
74 kst.ste_code
75 FROM okc_statuses_b kst,
76 okc_k_headers_b kh,
77 okc_subclasses_v ks
78 WHERE kh.id = p_chr_id
79 AND ks.code = kh.scs_code
80 AND kst.code = kh.sts_code;
81
82 --
83 -- cursor to get customer information
84 -- header level customers only
85 -- the customer is the role in the sell contract that is not me
86 -- this assumption will not hold as more roles get added post 11i
87 -- IF for a party, this should be party id, not cust account id
88 --
89
90 CURSOR c_cust (b_chr_id NUMBER) IS
91 SELECT
92 cpr.id
93 ,cpr.jtot_object1_code
94 ,cpr.object1_id1
95 ,cpr.object1_id2
96 ,cpr.rle_code
97 FROM okc_k_party_roles_b cpr
98 ,okc_role_sources rsc
99 WHERE
100 rsc.buy_or_sell = 'S' -- sell contract
101 AND rsc.rle_code = cpr.rle_code -- role
102 AND rsc.start_date <= sysdate
103 AND NVL(rsc.end_date, sysdate) >= sysdate
104 AND cpr.cle_id IS NULL -- parties
105 AND cpr.dnz_chr_id = b_chr_id;
106
107
108 TYPE line_info_rec_typ IS RECORD (
109 line_id okc_k_lines_v.id%TYPE
110 ,cle_id okc_k_lines_v.cle_id%TYPE
111 ,lse_id okc_k_lines_v.lse_id%TYPE
112 ,line_number okc_k_lines_v.line_number%TYPE
113 ,status_code okc_statuses_b.ste_code%TYPE
114 ,qty okc_k_items.number_of_items%TYPE
115 ,uom_code okc_k_items.uom_code%TYPE
116 ,customer_order_enabled_flag VARCHAR2(1)
117 ,item_name okc_k_lines_v.name%TYPE
118 ,priced_item_yn okc_k_items.priced_item_yn%TYPE
119 ,price_unit okc_k_lines_v.price_unit%TYPE
120 ,price_negotiated okc_k_lines_v.price_negotiated%TYPE
121 ,line_list_price okc_k_lines_v.line_list_price%TYPE
122 ,price_list_id okc_k_lines_v.price_list_id%TYPE
123 ,price_list_line_id okc_k_lines_v.price_list_line_id%TYPE
124 ,currency_code okc_k_lines_v.currency_code%TYPE
125 ,start_date okc_k_lines_v.start_date%TYPE
126 ,end_date okc_k_lines_v.end_date%TYPE
127 ,k_item_id okc_k_items.id%TYPE
128 ,object_id1 okc_k_items.object1_id1%TYPE
129 ,object_id2 okc_k_items.object1_id2%TYPE
130 ,line_style okc_line_styles_b.lse_type%TYPE
131 ,line_type okc_line_styles_b.lty_code%TYPE
132 );
133
134 TYPE line_info_tab_typ IS TABLE OF line_info_rec_typ INDEX BY BINARY_INTEGER;
135
136 TYPE kh_attr_rec_type IS RECORD
137 (
138 chr_id okc_k_headers_b.id%TYPE
139 ,CUST_ACCT_ID okc_k_headers_b.CUST_ACCT_ID%TYPE
140 ,BILL_TO_SITE_USE_ID okc_k_headers_b.BILL_TO_SITE_USE_ID%TYPE
141 ,INV_RULE_ID okc_k_headers_b.INV_RULE_ID%TYPE
142 ,SHIP_TO_SITE_USE_ID okc_k_headers_b.SHIP_TO_SITE_USE_ID%TYPE
143 ,CONVERSION_TYPE okc_k_headers_b.CONVERSION_TYPE%TYPE
144 ,CONVERSION_RATE okc_k_headers_b.CONVERSION_RATE%TYPE
145 ,CONVERSION_RATE_DATE okc_k_headers_b.CONVERSION_RATE_DATE%TYPE
146 ,CONVERSION_EURO_RATE okc_k_headers_b.CONVERSION_EURO_RATE%TYPE
147 );
148
149 TYPE kh_attr_tbl_type IS TABLE OF kh_attr_rec_type INDEX BY BINARY_INTEGER;
150 l_kh_attr_tab kh_attr_tbl_type;
151
152
153 TYPE kl_attr_rec_type IS RECORD
154 (
155 CLE_ID okc_k_lines_b.cle_id%TYPE
156 ,CUST_ACCT_ID okc_k_lines_b.CUST_ACCT_ID%TYPE
157 ,BILL_TO_SITE_USE_ID okc_k_lines_b.BILL_TO_SITE_USE_ID%TYPE
158 ,INV_RULE_ID okc_k_lines_b.INV_RULE_ID%TYPE
159 ,SHIP_TO_SITE_USE_ID okc_k_lines_b.SHIP_TO_SITE_USE_ID%TYPE
160 ,PRICE_LIST_ID okc_k_lines_b.SHIP_TO_SITE_USE_ID%TYPE
161 );
162
163 TYPE kl_attr_tbl_type IS TABLE OF kl_attr_rec_type INDEX BY BINARY_INTEGER;
164 l_kl_attr_tab kl_attr_tbl_type;
165
166 TYPE bto_sto_rec_typ IS RECORD
167 (
168 chr_id okc_k_headers_b.id%TYPE,
169 cle_id okc_k_lines_b.id%TYPE,
170 party_site_id okx_cust_site_uses_v.party_site_id%TYPE,
171 cust_acct_id okx_cust_site_uses_v.cust_account_id%TYPE,
172 party_id okx_cust_site_uses_v.party_id%TYPE,
173 address1 HZ_LOCATIONS.address1%TYPE,
174 address2 HZ_LOCATIONS.address2%TYPE,
175 address3 HZ_LOCATIONS.address3%TYPE,
176 address4 HZ_LOCATIONS.address4%TYPE,
177 city HZ_LOCATIONS.city%TYPE,
178 postal_code HZ_LOCATIONS.postal_code%TYPE,
179 state HZ_LOCATIONS.state%TYPE,
180 province HZ_LOCATIONS.province%TYPE,
181 county HZ_LOCATIONS.county%TYPE,
182 country HZ_LOCATIONS.country%TYPE);
183
184 TYPE l_k_bto_sto_data_tab_typ IS TABLE OF bto_sto_rec_typ INDEX BY BINARY_INTEGER;
185
186 --
187 -- Tables to hold bill to and ship to information at the header level
188 --
189
190 l_kh_bto_data_tab l_k_bto_sto_data_tab_typ;
191 l_kh_sto_data_tab l_k_bto_sto_data_tab_typ;
192
193 --
194 -- Tables to hold bill to and ship to information at the line level
195 --
196 l_kl_bto_data_tab l_k_bto_sto_data_tab_typ;
197 l_kl_sto_data_tab l_k_bto_sto_data_tab_typ;
198
199 --
200 -- global variables
201 --
202 l_chr c_chr%ROWTYPE;
203 -- Bug 4915691 --
204 l_cle c_cle%ROWTYPE;
205 -- Bug 4915691 --
206 l_k_nbr VARCHAR2(2000);
207 l_line_info_tab line_info_tab_typ;
208 l_order_type_id varchar2(240):= nvl(fnd_profile.value('OKS_ORDER_TYPE_ID'), okc_api.g_miss_char);
209 l_cust c_cust%ROWTYPE;
210 l_customer c_cust%ROWTYPE;
211
212 l_st_cust_acct_id okx_cust_site_uses_v.cust_account_id%TYPE;
213 l_st_party_site_id okx_cust_site_uses_v.party_site_id%TYPE;
214 l_st_party_id okx_cust_site_uses_v.party_id%TYPE;
215 l_bt_cust_acct_id okx_cust_site_uses_v.cust_account_id%TYPE;
216 l_bt_party_site_id okx_cust_site_uses_v.party_site_id%TYPE;
217 l_bt_party_id okx_cust_site_uses_v.party_id%TYPE;
218
219 l_exchange_type okc_conversion_attribs_v.conversion_type%TYPE;
220 l_exchange_rate okc_conversion_attribs_v.conversion_rate%TYPE;
221 l_exchange_date okc_conversion_attribs_v.conversion_date%TYPE;
222
223
224 --
225 -- private procedures
226 --
227
228 -------------------------------------------------------------------------------
229 -- Procedure: print_error
230 -- Returns:
231 -- Purpose: Print the last error which occured
232 -- In Parameters: pos position on the line to print the message
233 -- Out Parameters:
234
235 PROCEDURE print_error(pos IN NUMBER) IS
236 x_msg_count NUMBER;
237 x_msg_data VARCHAR2(1000);
238 BEGIN
239 IF okc_util.l_trace_flag OR okc_util.l_log_flag THEN
240 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
241 p_data => x_msg_data
242 );
243 FND_FILE.PUT_LINE( FND_FILE.LOG, '==EXCEPTION=================');
244 x_msg_data := fnd_msg_pub.get( p_msg_index => x_msg_count,
245 p_encoded => 'F'
246 );
247 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Message : '||x_msg_data);
248 FND_FILE.PUT_LINE( FND_FILE.LOG, '============================');
249 END IF;
250 END print_error;
251
252 Function is_jtf_source_table( p_object_code jtf_objects_b.object_code%type,
253 p_from_table JTF_OBJECTS_B.from_table%type
254 )
255
256 return boolean is
257 cursor c_get_jtf_source_table(b_object_code varchar2,b_from_table varchar2) is
258 select 'x' from jtf_objects_b
259 where object_code = b_object_code
260 and from_table like b_from_table||'%';
261
262 l_found varchar2(1);
263
264 begin
265 open c_get_jtf_source_table(p_object_code,p_from_table);
266 fetch c_get_jtf_source_table into l_found;
267 If c_get_jtf_source_table%found then
268 return true;
269 else
270 return false;
271 end if;
272 close c_get_jtf_source_table;
273
274 exception
275 when others then
276 if c_get_jtf_source_table%isopen then
277 close c_get_jtf_source_table;
278 return false;
279 end if;
280
281 end;
282
283 ----------------------------------------------------------------------
284 -- Procedure: build_k_attributes
285 -- Purpose: building header and line level rules/attributes
286 -- and bill to and ship to information
287 -- In Parameters: p_chr_id the contract id
288 -- p_cle_id line id
289 -- p_renew_rec contract information for renewal
290 -- Out Parameters: x_return_status standard return status
291 -- x_hdr_attr_tab header level attributes
292 -- x_line_attr_tab line level attributes
293 -- x_bto_data_rec BTO details
294 -- x_sto_data_rec STO details
295
296 ---------------------------------------------------------------------
297
298 PROCEDURE build_k_attributes( p_chr_id IN okc_k_headers_b.ID%TYPE,
299 p_cle_id IN okc_k_lines_v.id%TYPE,
300 x_hdr_attr_tab OUT NOCOPY kh_attr_tbl_type,
301 x_line_attr_tab OUT NOCOPY kl_attr_tbl_type,
302 x_bto_data_rec OUT NOCOPY bto_sto_rec_typ,
303 x_sto_data_rec OUT NOCOPY bto_sto_rec_typ,
304 x_return_status OUT NOCOPY VARCHAR2 ) IS
305
306 --cursor for getting header rules/attribute
307
308 CURSOR c_header_attr(p_chr_id in NUMBER) IS
309 SELECT ID
310 ,CUST_ACCT_ID
311 ,BILL_TO_SITE_USE_ID
312 ,INV_RULE_ID
313 ,SHIP_TO_SITE_USE_ID
314 ,CONVERSION_TYPE
315 ,CONVERSION_RATE
316 ,CONVERSION_RATE_DATE
317 ,CONVERSION_EURO_RATE
318 FROM OKC_K_HEADERS_B
319 WHERE id = p_chr_id;
320
321 --cursor for getting line rules/attributes
322
323 CURSOR c_lines_attr(p_chr_id in NUMBER, p_cle_id in NUMBER) IS
324 SELECT
325 CLE_ID
326 ,CUST_ACCT_ID
327 ,BILL_TO_SITE_USE_ID
328 ,INV_RULE_ID
329 ,SHIP_TO_SITE_USE_ID
330 ,PRICE_LIST_ID
331 FROM OKC_K_LINES_B
332 WHERE dnz_chr_id = p_chr_id
333 AND id = p_cle_id;
334
335 -- get party site id for a customer account site id
336 --
337
338 CURSOR c_party_site (b_id1 NUMBER) IS
339 SELECT
340 party_site_id
341 ,cust_account_id
342 ,party_id
343 ,address1
344 ,address2
345 ,address3
346 ,address4
347 ,city
348 ,state
349 ,province
350 ,postal_code
351 ,county
352 ,country
353 FROM okx_cust_site_uses_v
354 WHERE id1 = b_id1;
355 --
356 l_party_site c_party_site%ROWTYPE;
357 e_exit EXCEPTION;
358 l_lines NUMBER;
359 l_idx INTEGER;
360 l_sto_data_rec bto_sto_rec_typ;
361 l_bto_data_rec bto_sto_rec_typ;
362 l_k_attr_tab_h kh_attr_tbl_type;
363 l_k_attr_tab_l kl_attr_tbl_type;
364
365 BEGIN
366
367 l_sto_data_rec := NULL;
368 l_bto_data_rec := NULL;
369 l_k_attr_tab_h.delete;
370 l_k_attr_tab_l.delete;
371
372 l_st_party_id := null;
373 l_st_party_site_id := null;
374 l_bt_party_id := null;
375 l_bt_party_site_id := null;
376 --l_bt_cust_acct_id := null;
377
378 l_idx := 0;
379
380 --retrieving header data
381
382 IF P_CLE_ID IS NULL Then
383 FOR header_attr_rec IN c_header_attr(p_chr_id)
384 LOOP
385
386 IF header_attr_rec.SHIP_TO_SITE_USE_ID is NOT NULL THEN
387
388 OPEN c_party_site(header_attr_rec.SHIP_TO_SITE_USE_ID);
389 FETCH c_party_site INTO l_party_site;
390
391 IF c_party_site%FOUND THEN
392
393 l_sto_data_rec.chr_id := p_chr_id;
394 l_sto_data_rec.party_site_id := l_party_site.party_site_id;
395 l_sto_data_rec.cust_acct_id := l_party_site.cust_account_id;
396 l_sto_data_rec.party_id := l_party_site.party_id;
397 l_sto_data_rec.address1 := l_party_site.address1;
398 l_sto_data_rec.address2 := l_party_site.address2;
399 l_sto_data_rec.address3 := l_party_site.address3;
400 l_sto_data_rec.address4 := l_party_site.address4;
401 l_sto_data_rec.city := l_party_site.city;
402 l_sto_data_rec.state := l_party_site.state;
403 l_sto_data_rec.province := l_party_site.province;
404 l_sto_data_rec.postal_code := l_party_site.postal_code;
405 l_sto_data_rec.county := l_party_site.county;
406 l_sto_data_rec.country := l_party_site.country;
407 l_st_party_site_id := l_party_site.party_site_id;
408 l_st_cust_acct_id := l_party_site.cust_account_id;
409 l_st_party_id := l_party_site.party_id;
410
411
412
413 l_sto_data_rec.cle_id := p_cle_id;
414 END IF;
415 CLOSE c_party_site;
416 END IF;
417
418 IF header_attr_rec.BILL_TO_SITE_USE_ID is NOT NULL THEN
419
420 OPEN c_party_site(header_attr_rec.BILL_TO_SITE_USE_ID);
421 FETCH c_party_site INTO l_party_site;
422
423 IF c_party_site%FOUND THEN
424 l_bto_data_rec.chr_id := p_chr_id;
425 l_bto_data_rec.party_site_id := l_party_site.party_site_id;
426 l_bto_data_rec.cust_acct_id := l_party_site.cust_account_id;
427 l_bto_data_rec.party_id := l_party_site.party_id;
428 l_bt_party_site_id := l_party_site.party_site_id;
429 l_bt_cust_acct_id := l_party_site.cust_account_id;
430 l_bt_party_id := l_party_site.party_id;
431
432 l_bto_data_rec.cle_id := p_cle_id;
433
434
435 END IF;
436
437 CLOSE c_party_site;
438
439 END IF;
440
441 l_idx := l_idx + 1;
442 l_k_attr_tab_h(l_idx) := header_attr_rec;
443 END LOOP;
444
445 x_sto_data_rec := l_sto_data_rec;
446 x_bto_data_rec := l_bto_data_rec;
447 x_hdr_attr_tab :=l_k_attr_tab_h;
448
449 x_return_status := OKC_API.G_RET_STS_SUCCESS;
450
451 ELSE
452
453 --retrieving lines attributes
454
455 FOR lines_attr_rec IN c_lines_attr(p_chr_id, p_cle_id)
456 LOOP
457
458 IF lines_attr_rec.SHIP_TO_SITE_USE_ID is NOT NULL THEN
459
460 OPEN c_party_site(lines_attr_rec.SHIP_TO_SITE_USE_ID);
461 FETCH c_party_site INTO l_party_site;
462
463 IF c_party_site%FOUND THEN
464
465 l_sto_data_rec.chr_id := p_chr_id;
466 l_sto_data_rec.party_site_id:= l_party_site.party_site_id;
467 l_sto_data_rec.cust_acct_id := l_party_site.cust_account_id;
468 l_sto_data_rec.party_id := l_party_site.party_id;
469 l_sto_data_rec.address1 := l_party_site.address1;
470 l_sto_data_rec.address2 := l_party_site.address2;
471 l_sto_data_rec.address3 := l_party_site.address3;
472 l_sto_data_rec.address4 := l_party_site.address4;
473 l_sto_data_rec.city := l_party_site.city;
474 l_sto_data_rec.state := l_party_site.state;
475 l_sto_data_rec.province := l_party_site.province;
476 l_sto_data_rec.postal_code := l_party_site.postal_code;
477 l_sto_data_rec.county := l_party_site.county;
478 l_sto_data_rec.country := l_party_site.country;
479 l_st_party_site_id := l_party_site.party_site_id;
480 l_st_cust_acct_id := l_party_site.cust_account_id;
481 l_st_party_id := l_party_site.party_id;
482
483
484 l_sto_data_rec.cle_id := p_cle_id;
485
486 END IF;
487 CLOSE c_party_site;
488 END IF;
489
490 IF lines_attr_rec.BILL_TO_SITE_USE_ID IS NOT NULL THEN
491
492 -- need to fix bill to, since ASO wants the party site, not customer acct site
493 OPEN c_party_site(lines_attr_rec.BILL_TO_SITE_USE_ID);
494 FETCH c_party_site INTO l_party_site;
495
496 IF c_party_site%FOUND THEN
497 l_bto_data_rec.chr_id := p_chr_id;
498 l_bto_data_rec.party_site_id := l_party_site.party_site_id;
499 l_bto_data_rec.cust_acct_id := l_party_site.cust_account_id;
500 l_bto_data_rec.party_id := l_party_site.party_id;
501 l_bt_party_site_id := l_party_site.party_site_id;
502 l_bt_cust_acct_id := l_party_site.cust_account_id;
503 l_bt_party_id := l_party_site.party_id;
504 l_bto_data_rec.cle_id := p_cle_id;
505
506
507 END IF;
508
509 CLOSE c_party_site;
510
511 END IF;
512
513 l_idx := l_idx + 1;
514 --l_k_rule_tab(l_idx) := r_rule;
515 l_k_attr_tab_l(l_idx) := lines_attr_rec;
516 END LOOP;
517
518 x_sto_data_rec := l_sto_data_rec;
519 x_bto_data_rec := l_bto_data_rec;
520 -- x_rule_tab := l_k_rule_tab;
521 x_line_attr_tab :=l_k_attr_tab_l;
522 x_return_status := OKC_API.G_RET_STS_SUCCESS;
523
524 END IF;---------p_CLE_ID IS NULL
525
526 EXCEPTION
527 WHEN e_exit THEN
528 IF c_party_site%ISOPEN THEN
529 CLOSE c_party_site;
530 END IF;
531
532
533
534 END build_k_attributes;
535 ----------------------------------------------------------------------
536 ----------------------------------------------------------------------
537 -- Procedure: validate_k_eligibility
538 -- Purpose: Check up on specific conditions to ensure the contract
539 -- is elligible for a order creation
540 -- In Parameters: p_chr_id the contract id
541 -- p_k_header_rec contract information
542 -- p_renew_rec contract information for renewal
543 -- Out Parameters: x_return_status standard return status
544 ---------------------------------------------------------------------
545
546 PROCEDURE validate_k_eligibility(
547 p_chr_id IN okc_k_headers_b.ID%TYPE
548 ,p_k_header_rec IN c_k_header%ROWTYPE
549 ,x_return_status OUT NOCOPY VARCHAR2
550 ) IS
551
552 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
553 l_msg_count NUMBER := 0;
554 l_msg_data VARCHAR2(1000);
555 l_ord_num NUMBER:= OKC_API.G_MISS_NUM;
556 e_exit EXCEPTION;
557
558 BEGIN
559
560 -- To Check If this contract has been created from Order.If yes then return an Error
561
562 --l_order_type_id:=nvl(fnd_profile.value('OKS_ORDER_TYPE_ID'), okc_api.g_miss_char);
563 IF l_order_type_id = okc_api.g_miss_char THEN
564 OKC_API.set_message(p_app_name => g_app_name,
565 p_msg_name => 'OKS_K2O_ORDTYP',
566 p_token1 => 'PROFOPT',
567 p_token1_value => 'OKS:Default Order Type for Subscriptions',
568 p_token2 => 'NUMBER',
569 p_token2_value => p_k_header_rec.contract_number);
570 x_return_status := okc_api.g_ret_sts_error;
571 print_error(3);
572 RAISE e_exit;
573 END IF;
574
575 x_return_status := OKC_API.G_RET_STS_SUCCESS;
576
577 EXCEPTION
578 WHEN e_exit THEN
579
580 OKC_API.set_message(G_APP_NAME,
581 G_UNEXPECTED_ERROR,
582 G_SQLCODE_TOKEN,
583 SQLCODE,
584 G_SQLERRM_TOKEN,
585 SQLERRM);
586
587 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
588 END validate_k_eligibility;
589
590 -------------------------------------------------------------------------------
591 -- Procedure: build_k_structures
592 -- Purpose: Build several records/tables that hold information to be
593 -- used to pass to OC APIs
594 -- In Parameters: p_chr_id the contract id
595 -- Out Parameters: x_return_status standard return status
596 -----------------------------------------------------------------------------
597
598 PROCEDURE build_k_structures (p_chr_id IN okc_k_headers_b.ID%TYPE
599 ,p_cle_id IN okc_k_lines_b.id%TYPE
600 ,p_k_header_rec IN c_k_header%ROWTYPE
601 ,x_return_status OUT NOCOPY VARCHAR2
602 ) IS
603
604 --cursor for getting line level details
605
606 CURSOR c_top_cle(b_chr_id NUMBER,
607 b_line_id NUMBER) IS
608 SELECT
609 cle.id line_id
610 ,cle.cle_id cle_id
611 ,cle.lse_id lse_id
612 ,cle.line_number line_number
613 ,sts.ste_code
614 ,cim.number_of_items qty
615 ,cim.uom_code
616 ,'Y' customer_order_enabled_flag
617 ,cle.name item_name
618 ,cim.priced_item_yn
619 ,cle.price_unit
620 ,'0' price_negotiated
621 ,cle.line_list_price
622 ,cle.price_list_id
623 ,cle.price_list_line_id
624 ,cle.currency_code
625 ,cle.start_date
626 ,cle.end_date
627 ,cim.id cim_id
628 ,cim.object1_id1 object1_id1
629 ,cim.object1_id2 object1_id2
630 ,lse.lse_type line_style
631 ,lse.lty_code line_type
632 FROM
633 okc_k_lines_v cle,
634 okc_k_items cim,
635 okc_line_styles_b lse,
636 okc_statuses_b sts
637 WHERE
638 cim.cle_id = cle.id
639 AND lse.id = cle.lse_id
640 AND sts.code = cle.sts_code
641 AND cle.dnz_chr_id = b_chr_id
642 AND cle.id = b_line_id;
643
644 e_exit exception;
645 l_idx binary_integer;
646 l_svc_duration NUMBER;
647 l_svc_period Varchar2(100);
648 l_party NUMBER;
649 l_lines NUMBER;
650 l_item_name VARCHAR2(150);
651 l_customer_order_enabled_flag VARCHAR2(1);
652 r_cle line_info_rec_typ;
653 lx_return_status VARCHAR2(1);
654 lx_index NUMBER;
655 x_msg_count NUMBER;
656 x_msg_data VARCHAR2(1000);
657 l_customer_account_id NUMBER := NULL;
658 l_count NUMBER;
659 l_party_id NUMBER;
660 l_inventory_item_id NUMBER;
661 l_organization_id NUMBEr;
662
663 lx_kh_attr_tab kh_attr_tbl_type;
664 lx_kl_attr_tab kl_attr_tbl_type;
665
666 lx_kh_bto_data_rec bto_sto_rec_typ;
667 lx_kh_sto_data_rec bto_sto_rec_typ;
668
669 lx_kl_bto_data_rec bto_sto_rec_typ;
670 lx_kl_sto_data_rec bto_sto_rec_typ;
671
672
673
674 BEGIN
675
676 l_line_info_tab.delete;
677
678 l_party:=0;
679
680 l_kh_attr_tab.delete ;
681 l_kl_attr_tab.delete ;
682
683 l_kh_sto_data_tab.delete;
684 l_kh_bto_data_tab.delete;
685
686 l_kl_sto_data_tab.delete;
687 l_kl_bto_data_tab.delete;
688
689 l_line_info_tab.delete;
690
691 FOR l_cust IN c_cust(p_chr_id)
692 LOOP
693 IF is_jtf_source_table(l_cust.jtot_object1_code,g_okx_parties_v) THEN
694 l_party:=l_party+1;
695 l_customer:=l_cust;
696 END IF;
697 END LOOP;
698
699
700 /************ get header level rules************************/
701
702
703 build_k_attributes(p_chr_id => p_chr_id,
704 p_cle_id => NULL,
705 x_hdr_attr_tab => lx_kh_attr_tab,
706 x_line_attr_tab => lx_kl_attr_tab,
707 x_bto_data_rec => lx_kh_bto_data_rec,
708 x_sto_data_rec => lx_kh_sto_data_rec,
709 x_return_status => lx_return_status );
710
711 IF lx_return_status = OKC_API.G_RET_STS_SUCCESS THEN
712
713
714 IF lx_kh_attr_tab.FIRST IS NOT NULL THEN
715 l_kh_attr_tab := lx_kh_attr_tab;
716 END IF;
717
718 IF lx_kh_bto_data_rec.chr_id IS NOT NULL THEN
719 l_kh_bto_data_tab(l_kh_bto_data_tab.COUNT+1):=lx_kh_bto_data_rec;
720 END IF;
721
722 IF lx_kh_sto_data_rec.chr_id IS NOT NULL THEN
723 l_kh_sto_data_tab(l_kh_sto_data_tab.COUNT+1):=lx_kh_sto_data_rec;
724 END IF;
725
726 ELSE
727 raise e_exit;
728 END IF;
729
730 /**************Get all the top lines in detail****************/
731
732
733 l_lines := 0;
734 l_idx := 0;
735
736 OPEN c_top_cle(p_chr_id, p_cle_id);
737 FETCH c_top_cle INTO r_cle;
738
739 IF c_top_cle%NOTFOUND THEN
740 OKC_API.set_message(
741 p_app_name => g_app_name1,
742 p_msg_name => 'OKS_K2O_LINENOTORDBL5',
743 p_token1 => 'LINE_NUM',
744 p_token1_value => r_cle.line_number,
745 p_token2 => 'NUMBER',
746 p_token2_value => l_k_nbr);
747 print_error(2);
748 RAISE e_exit;
749 END IF;
750
751 l_item_name :=r_cle.item_name;
752 l_customer_order_enabled_flag := r_cle.customer_order_enabled_flag;
753
754 l_lines:=l_lines+1;
755
756 l_idx := l_idx + 1;
757
758 l_line_info_tab(l_idx) := r_cle;
759 /*************************************************************/
760
761 FND_FILE.PUT_LINE( FND_FILE.LOG,'inventory_item_id ='|| r_cle.object_id1);
762
763 /*************************************************************/
764 select description into l_item_name
765 from okx_system_items_v
766 where id1=r_cle.object_id1
767 and id2 = r_cle.object_id2;
768
769 l_line_info_tab(l_idx).object_id1:=r_cle.object_id1;
770 ----- l_line_info_tab(l_idx).object_id2:=l_organization_id ;
771
772 l_line_info_tab(l_idx).item_name := l_item_name;
773 l_line_info_tab(l_idx).customer_order_enabled_flag := l_customer_order_enabled_flag;
774 l_svc_duration := r_cle.qty;
775 l_svc_period := r_cle.uom_code;
776
777 -- duration is quantity AND period uom for service line
778 l_line_info_tab(l_idx).qty := rtrim(ltrim(l_svc_duration));
779 l_line_info_tab(l_idx).uom_code := rtrim(ltrim(l_svc_period));
780
781 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Order Details for Top Line No = '||r_cle.line_number ||' '||'Item Name='||l_item_name);
782 FND_FILE.PUT_LINE( FND_FILE.OUTPUT, 'Order Details for Top Line No = '||r_cle.line_number||' '||'Item Name='||l_item_name);
783
784
785 --
786 -- Check and populate the top lines rules table
787 --
788
789 build_k_attributes( p_chr_id => p_chr_id,
790 p_cle_id => l_line_info_tab(l_idx).line_id,
791 x_hdr_attr_tab => lx_kh_attr_tab,
792 x_line_attr_tab => lx_kl_attr_tab,
793 x_bto_data_rec => lx_kl_bto_data_rec,
794 x_sto_data_rec => lx_kl_sto_data_rec,
795 x_return_status => lx_return_status );
796 FND_FILE.PUT_LINE( FND_FILE.LOG, 'After top lines build rules return status = '||lx_return_status);
797
798 IF lx_return_status = OKC_API.G_RET_STS_SUCCESS THEN
799 IF lx_kl_attr_tab.FIRST IS NOT NULL THEN
800 FOR i IN lx_kl_attr_tab.FIRST..lx_kl_attr_tab.LAST LOOP
801 l_kl_attr_tab(l_kl_attr_tab.COUNT+1) := lx_kl_attr_tab(i);
802 END LOOP;
803 END IF;
804
805
806 IF lx_kl_bto_data_rec.cle_id IS NOT NULL THEN
807 l_kl_bto_data_tab(l_kl_bto_data_tab.COUNT+1) := lx_kl_bto_data_rec;
808 END IF;
809
810 IF lx_kl_sto_data_rec.cle_id IS NOT NULL THEN
811 l_kl_sto_data_tab(l_kl_sto_data_tab.COUNT+1) := lx_kl_sto_data_rec;
812 END IF;
813
814 ELSE
815 raise e_exit;
816 END IF;
817
818 CLOSE c_top_cle;
819
820 IF l_lines = 0 THEN
821 FND_FILE.PUT_LINE( FND_FILE.LOG, 'NO LINES');
822 okc_api.set_message(OKC_API.G_APP_NAME,
823 'OKS_K2O_NOLINES',
824 'KNUMBER',
825 l_k_nbr);
826 x_return_status := OKC_API.G_RET_STS_ERROR;
827 print_error(4);
828 RAISE e_exit;
829 END IF;
830 IF l_idx = 0 THEN
831 FND_FILE.PUT_LINE( FND_FILE.LOG, 'NO ORDERABLE LINES');
832
833 FND_FILE.PUT_LINE( FND_FILE.LOG, 'NO orderable lines');
834 okc_api.set_message(OKC_API.G_APP_NAME,
835 'OKS_K2O_NOORDLINES',
836 'KNUMBER',
837 l_k_nbr);
838 x_return_status := OKC_API.G_RET_STS_ERROR;
839 print_error(4);
840 RAISE e_exit;
841 END IF;
842
843 x_return_status := OKC_API.G_RET_STS_SUCCESS;
844 EXCEPTION
845 WHEN e_exit THEN
846
847 IF c_cust%ISOPEN THEN
848 CLOSE c_cust;
849 END IF;
850
851 IF c_top_cle%ISOPEN THEN
852 CLOSE c_top_cle;
853 END IF;
854 WHEN OTHERS THEN
855
856 OKC_API.set_message
857 (G_APP_NAME,
858 G_UNEXPECTED_ERROR,
859 G_SQLCODE_TOKEN,
860 SQLCODE,
861 G_SQLERRM_TOKEN,
862 SQLERRM);
863
864 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
865
866 END build_k_structures;
867
868 -------------------------------------------------------------------------------
869 -- Procedure: build_order_hdr
870 -- Purpose: Build the order header record to pass to the ASO/OM APIs
871 -- In Parameters:
872 -- Out Parameters:
873 -- In/Out Parameters: px_qte_hdr_rec - the record to pass to ASO
874 ------------------------------------------------------------------------------
875
876 PROCEDURE build_order_hdr
877 (px_qte_hdr_rec IN OUT NOCOPY aso_quote_pub.qte_header_rec_type
878 ,p_contract_id IN NUMBER
879 ,px_hd_shipment_tbl IN OUT NOCOPY aso_quote_pub.shipment_tbl_type
880 ,x_return_status OUT NOCOPY VARCHAR2
881 ) IS
882
883 e_exit exception; -- used to exit processing
884 l_msg_count NUMBER;
885 l_msg_data VARCHAR2(1000);
886
887
888 BEGIN
889
890 px_qte_hdr_rec.org_id := l_chr.authoring_org_id;
891 px_qte_hdr_rec.currency_code := l_chr.currency_code;
892 px_qte_hdr_rec.quote_source_code := g_qte_source_c;
893 px_qte_hdr_rec.order_type_id := l_order_type_id;
894
895 okc_api.set_message(OKC_API.G_APP_NAME, 'OKC_K2Q_KCOPY', 'NUMBER', l_k_nbr);
896
897 FND_MSG_PUB.Count_And_Get (
898 p_count => l_msg_count,
899 p_data => l_msg_data);
900
901 px_qte_hdr_rec.quote_name := Substr( fnd_msg_pub.get( p_msg_index =>
902 l_msg_count, p_encoded => 'F'),1,50);
903
904 FND_MSG_PUB.Delete_Msg ( p_msg_index => l_msg_count);
905
906
907 px_qte_hdr_rec.quote_version := 1;
908 px_qte_hdr_rec.party_id := l_cust.object1_id1;
909 px_qte_hdr_rec.original_system_reference := l_k_nbr;
910
911
912 FND_FILE.PUT_LINE( FND_FILE.LOG, 'After order header salescredit');
913
914 IF l_kh_attr_tab.first IS NOT NULL THEN
915 FOR i IN l_kh_attr_tab.first..l_kh_attr_tab.last LOOP
916 IF l_kh_attr_tab(i).CUST_ACCT_ID IS NOT NULL THEN
917
918 px_qte_hdr_rec.cust_account_id := l_kh_attr_tab(i).CUST_ACCT_ID;
919 /* ELSIF l_kh_attr_tab(i).rule_information_category = g_rd_price THEN
920
921 px_qte_hdr_rec.price_list_id := l_kh_rule_tab(i).object1_id1;*/
922
923 ELSIF l_kh_attr_tab(i).INV_RULE_ID IS NOT NULL THEN
924
925 px_qte_hdr_rec.invoicing_rule_id := l_kh_attr_tab(i).INV_RULE_ID;
926
927 END IF;
928 END LOOP;
929 END IF;
930
931 IF l_kh_bto_data_tab.FIRST IS NOT NULL THEN
932 FOR i IN l_kh_bto_data_tab.FIRST..l_kh_bto_data_tab.LAST LOOP
933
934 px_qte_hdr_rec.invoice_to_party_site_id := l_kh_bto_data_tab(i).party_site_id;
935 px_qte_hdr_rec.invoice_to_party_id := NVL(l_kh_bto_data_tab(i).party_id,l_cust.object1_id1);
936 -- Bug 4915691 --
937 px_qte_hdr_rec.INVOICE_TO_CUST_ACCOUNT_ID := l_kh_bto_data_tab(i).cust_acct_id;
938 px_qte_hdr_rec.cust_account_id := l_kh_bto_data_tab(i).cust_acct_id;
939 -- Bug 4915691 --
940
941 END LOOP;
942 END IF;
943
944 -- Populate the shipment record
945 --
946 FND_FILE.PUT_LINE( FND_FILE.LOG, 'before populating order header shipmentrecords = ');
947 IF l_kh_sto_data_tab.FIRST IS NOT NULL THEN
948 FOR i IN l_kh_sto_data_tab.FIRST..l_kh_sto_data_tab.LAST LOOP
949
950 px_hd_shipment_tbl(i).ship_to_party_id := NVL(l_kh_sto_data_tab(i).party_id,l_cust.object1_id1);
951 px_hd_shipment_tbl(i).ship_to_party_site_id := l_kh_sto_data_tab(i).party_site_id;
952 px_hd_shipment_tbl(i).ship_to_cust_account_id := l_kh_sto_data_tab(i).cust_acct_id;
953 px_hd_shipment_tbl(i).ship_to_address1 := l_kh_sto_data_tab(i).address1;
954 px_hd_shipment_tbl(i).ship_to_address2 := l_kh_sto_data_tab(i).address2;
955 px_hd_shipment_tbl(i).ship_to_address3 := l_kh_sto_data_tab(i).address3;
956 px_hd_shipment_tbl(i).ship_to_address4 := l_kh_sto_data_tab(i).address4;
957 px_hd_shipment_tbl(i).ship_to_city := l_kh_sto_data_tab(i).city;
958 px_hd_shipment_tbl(i).ship_to_state := l_kh_sto_data_tab(i).state;
959 px_hd_shipment_tbl(i).ship_to_province := l_kh_sto_data_tab(i).province;
960 px_hd_shipment_tbl(i).ship_to_postal_code := l_kh_sto_data_tab(i).postal_code;
961 px_hd_shipment_tbl(i).ship_to_county := l_kh_sto_data_tab(i).county;
962 px_hd_shipment_tbl(i).ship_to_country := l_kh_sto_data_tab(i).country;
963
964 /* FOR j IN l_kh_rule_tab.FIRST..l_kh_rule_tab.LAST LOOP
965 IF l_kh_rule_tab(j).rule_information_category = g_rd_shipmtd THEN
966 px_hd_shipment_tbl(i).ship_method_code := l_kh_rule_tab(j).rule_information1;
967 END IF;
968 END LOOP;
969 */
970 END LOOP;
971 END IF;
972
973 --
974 -- set exchange information
975 --
976 px_qte_hdr_rec.exchange_type_code := l_exchange_type;
977 px_qte_hdr_rec.exchange_rate := l_exchange_rate;
978 px_qte_hdr_rec.exchange_rate_date := l_exchange_date;
979
980 --
981 -- check IF we got customer account, set IF not
982 --
983 IF px_qte_hdr_rec.cust_account_id IS NULL
984 OR px_qte_hdr_rec.cust_account_id = okc_api.g_miss_num THEN
985
986 px_qte_hdr_rec.cust_account_id := l_bt_cust_acct_id;
987 --px_qte_hdr_rec.cust_account_id := 3347;
988 END IF;
989
990 OPEN c_chr(p_contract_id);
991 FETCH c_chr INTO l_chr;
992 IF c_chr%FOUND THEN
993
994 px_qte_hdr_rec.total_list_price := l_chr.total_line_list_price;
995 px_qte_hdr_rec.total_adjusted_amount := l_chr.total_line_list_price - l_chr.estimated_amount;
996
997 px_qte_hdr_rec.price_list_id := l_chr.price_list_id;
998
999 END IF;
1000 CLOSE c_chr;
1001
1002 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1003 EXCEPTION
1004 WHEN e_exit THEN
1005 null;
1006 WHEN OTHERS THEN
1007 OKC_API.set_message(
1008 G_APP_NAME,
1009 G_UNEXPECTED_ERROR,
1010 G_SQLCODE_TOKEN,
1011 SQLCODE,
1012 G_SQLERRM_TOKEN,
1013 SQLERRM);
1014
1015 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1016
1017 END build_ORDER_HDR;
1018
1019 -------------------------------------------------------------------------------
1020 -- Procedure: build_order_line
1021 -- Purpose: Build the order line record to pass to the ASO/OM APIs
1022 -- In Parameters:
1023 -- Out Parameters:
1024 -- In/Out Parameters: px_qte_hdr_rec - the record to pass to ASO
1025 ------------------------------------------------------------------------------
1026
1027
1028 PROCEDURE build_order_line
1029 (
1030 px_qte_line_tbl IN OUT NOCOPY aso_quote_pub.qte_line_tbl_type
1031 ,px_qte_hdr_rec IN aso_quote_pub.qte_header_rec_type
1032 ,px_qte_ln_shipment_tbl IN OUT NOCOPY aso_quote_pub.shipment_tbl_type
1033 ,px_qte_line_dtl_tbl IN OUT NOCOPY aso_quote_pub.qte_line_dtl_tbl_type
1034 ,px_k2q_line_tbl IN OUT NOCOPY okc_oc_int_config_pvt.line_rel_tab_type
1035 ,x_return_status OUT NOCOPY VARCHAR2
1036 ) IS
1037
1038 l_ql binary_integer;
1039 i binary_integer;
1040 j binary_integer;
1041 k binary_integer;
1042 l_nb_qte_line_dtl NUMBER;
1043 l_cur_tl NUMBER;
1044 l_k2q_found VARCHAR2(1) := 'N';
1045 e_exit EXCEPTION;
1046 l_return_status VARCHAR2(1);
1047
1048
1049 BEGIN
1050
1051 l_ql := 0;
1052
1053
1054 IF l_line_info_tab.first is not NULL THEN
1055
1056 FND_FILE.PUT_LINE( FND_FILE.LOG, 'in build order lines ');
1057 FOR i IN l_line_info_tab.first..l_line_info_tab.last LOOP
1058
1059 l_ql:=l_ql+1;
1060 px_qte_line_tbl(l_ql).operation_code := g_aso_op_code_create;
1061 px_qte_line_tbl(l_ql).quote_header_id:= px_qte_hdr_rec.quote_header_id;
1062 px_qte_line_tbl(l_ql).line_number := l_ql;
1063
1064
1065 --Order Management constraint:
1066 --Need to populate p_line_shipment_tbl in addition of p_qte_line_tbl
1067 --to create order lines
1068 --Order line = (Quote line, Shipment line)
1069 --
1070
1071 px_qte_ln_shipment_tbl(l_ql).qte_line_index := l_ql;
1072 px_qte_ln_shipment_tbl(l_ql).quantity := l_line_info_tab(i).qty;
1073 px_qte_line_tbl(l_ql).org_id := l_chr.authoring_org_id;
1074 px_qte_line_tbl(l_ql).inventory_item_id:= to_number(l_line_info_tab(i).object_id1);
1075 -- px_qte_line_tbl(l_ql).organization_id := to_number(l_line_info_tab(i).object_id2);
1076 px_qte_line_tbl(l_ql).organization_id := l_chr.authoring_org_id;
1077 px_qte_line_tbl(l_ql).quantity := l_line_info_tab(i).qty;
1078 px_qte_line_tbl(l_ql).uom_code := l_line_info_tab(i).uom_code;
1079 px_qte_line_tbl(l_ql).start_date_active:= l_line_info_tab(i).end_date + 1;
1080 px_qte_line_tbl(l_ql).currency_code := l_line_info_tab(i).currency_code;
1081
1082
1083 -- Obtain the top line rules
1084 IF l_kl_attr_tab.COUNT > 0 then
1085 FOR k IN l_kl_attr_tab.FIRST..l_kl_attr_tab.LAST LOOP
1086
1087 IF l_kl_attr_tab(k).cle_id = l_line_info_tab(i).line_id THEN
1088
1089 IF l_kl_attr_tab(k).price_list_id IS NOT NULL THEN
1090 px_qte_line_tbl(l_ql).price_list_id := NVL(l_kl_attr_tab(k).price_list_id,px_qte_hdr_rec.price_list_id);
1091 ELSIF l_kl_attr_tab(k).inv_rule_id IS NOT NULL THEN
1092
1093 px_qte_line_tbl(l_ql).invoicing_rule_id := NVL(l_kl_attr_tab(k).inv_rule_id,px_qte_hdr_rec.invoicing_rule_id);
1094
1095 /* ELSIF l_kl_attr_tab(k).rule_information_category = g_rd_shipmtd THEN
1096 px_qte_ln_shipment_tbl(l_ql).ship_method_code := l_kl_rule_tab(k).rule_information1;
1097 */
1098 END IF;
1099
1100 px_qte_line_tbl(l_ql).price_list_id := px_qte_hdr_rec.price_list_id;
1101
1102 END IF;
1103 END LOOP;
1104 ELSE
1105 px_qte_line_tbl(l_ql).price_list_id := px_qte_hdr_rec.price_list_id;
1106 px_qte_line_tbl(l_ql).invoicing_rule_id := px_qte_hdr_rec.invoicing_rule_id;
1107
1108 END IF;
1109
1110 --
1111 -- obtain the bill to rule
1112 --
1113 IF l_kl_bto_data_tab.FIRST IS NOT NULL THEN
1114 FOR k IN l_kl_bto_data_tab.FIRST..l_kl_bto_data_tab.LAST LOOP
1115 IF l_kl_bto_data_tab(k).cle_id = l_line_info_tab(i).line_id THEN
1116
1117 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);
1118 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);
1119 -- Bug 4915691 --
1120 px_qte_line_tbl(l_ql).INVOICE_TO_CUST_ACCOUNT_ID := NVL(l_kl_bto_data_tab(k).cust_acct_id,px_qte_hdr_rec.invoice_to_party_id);
1121 -- Bug 4915691 --
1122 END IF;
1123 END LOOP;
1124 END IF;
1125
1126 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Before order line shipment = ');
1127 --
1128 -- obtain the ship to rule and the operation code
1129 --
1130 IF l_kl_sto_data_tab.COUNT > 0 THEN
1131 FOR k IN l_kl_sto_data_tab.FIRST..l_kl_sto_data_tab.LAST LOOP
1132 IF l_kl_sto_data_tab(k).cle_id = l_line_info_tab(i).line_id THEN
1133
1134 px_qte_ln_shipment_tbl(l_ql).ship_to_party_site_id := l_kl_sto_data_tab(k).party_site_id;
1135 px_qte_ln_shipment_tbl(l_ql).ship_to_cust_account_id := l_kl_sto_data_tab(k).cust_acct_id;
1136 px_qte_ln_shipment_tbl(l_ql).ship_to_party_id := NVL(l_kl_sto_data_tab(k).party_id,l_cust.object1_id1);
1137 px_qte_ln_shipment_tbl(l_ql).ship_to_address1 := l_kl_sto_data_tab(k).address1;
1138 px_qte_ln_shipment_tbl(l_ql).ship_to_address2 := l_kl_sto_data_tab(k).address2;
1139 px_qte_ln_shipment_tbl(l_ql).ship_to_address3 := l_kl_sto_data_tab(k).address3;
1140 px_qte_ln_shipment_tbl(l_ql).ship_to_address4 := l_kl_sto_data_tab(k).address4;
1141 px_qte_ln_shipment_tbl(l_ql).ship_to_city := l_kl_sto_data_tab(k).city;
1142 px_qte_ln_shipment_tbl(l_ql).ship_to_state := l_kl_sto_data_tab(k).state;
1143 px_qte_ln_shipment_tbl(l_ql).ship_to_province := l_kl_sto_data_tab(k).province;
1144 px_qte_ln_shipment_tbl(l_ql).ship_to_postal_code:= l_kl_sto_data_tab(k).postal_code;
1145 px_qte_ln_shipment_tbl(l_ql).ship_to_county := l_kl_sto_data_tab(k).county;
1146 px_qte_ln_shipment_tbl(l_ql).ship_to_country := l_kl_sto_data_tab(k).country;
1147 END IF;
1148 END LOOP;
1149 END IF;
1150
1151 l_cur_tl:=i;
1152
1153
1154 px_qte_line_tbl(l_ql).line_list_price := 0;
1155 px_qte_line_tbl(l_ql).line_quote_price := ROUND(l_line_info_tab(l_cur_tl).price_negotiated / l_line_info_tab(l_cur_tl).qty, 2);
1156
1157
1158 /* px_qte_line_tbl(l_ql).price_list_id := l_line_info_tab(l_cur_tl).price_list_id;
1159 px_qte_line_tbl(l_ql).price_list_line_id := l_line_info_tab(l_cur_tl).price_list_line_id;
1160 */
1161 --
1162 -- record relation in the px_k2q_line_tbl PL/SQL table
1163 --
1164
1165 px_k2q_line_tbl(l_ql).k_line_id := l_line_info_tab(l_cur_tl).line_id;
1166 px_k2q_line_tbl(l_ql).q_line_idx := l_ql;
1167
1168
1169 END LOOP;
1170 END IF;
1171
1172
1173 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
1174
1175 print_error(3);
1176 RAISE e_exit;
1177
1178 END IF;
1179
1180
1181 IF l_ql = 0 THEN
1182 okc_api.set_message(OKC_API.G_APP_NAME,
1183 'OKS_K2O_NOORDLINES2',
1184 'NUMBER',
1185 l_chr.contract_number);
1186 x_return_status := OKC_API.G_RET_STS_ERROR;
1187 print_error(4);
1188
1189 ELSE
1190 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1191 END IF;
1192
1193 IF px_qte_line_tbl.first IS NOT NULL THEN
1194 FOR i IN px_qte_line_tbl.first..px_qte_line_tbl.last LOOP
1195
1196 IF px_qte_line_dtl_tbl.first IS NOT NULL THEN
1197 l_nb_qte_line_dtl:=0;
1198 FOR j IN px_qte_line_dtl_tbl.first..px_qte_line_dtl_tbl.last LOOP
1199 IF px_qte_line_dtl_tbl(j).qte_line_index = px_qte_line_tbl(i).line_number THEN
1200 l_nb_qte_line_dtl:=l_nb_qte_line_dtl + 1;
1201
1202 END IF;
1203 END LOOP;
1204 IF l_nb_qte_line_dtl = 0 THEN
1205 FND_FILE.PUT_LINE( FND_FILE.LOG, 'NO Order Detail Lines');
1206 END IF;
1207 ELSE
1208 null;
1209 END IF;
1210
1211
1212 IF px_k2q_line_tbl.EXISTS(i) THEN
1213 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Order Item type code = '||px_k2q_line_tbl(i).q_item_type_code);
1214 END IF;
1215 FND_FILE.PUT_LINE( FND_FILE.LOG, ' ');
1216
1217 END LOOP; -- qteline
1218 ELSE
1219 FND_FILE.PUT_LINE( FND_FILE.LOG, 'NO Order Lines');
1220 END IF;
1221 EXCEPTION
1222 WHEN e_exit THEN
1223 null;
1224 WHEN OTHERS THEN
1225 OKC_API.set_message
1226 (G_APP_NAME,
1227 G_UNEXPECTED_ERROR,
1228 G_SQLCODE_TOKEN,
1229 SQLCODE,
1230 G_SQLERRM_TOKEN,
1231 SQLERRM);
1232 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1233 END build_order_line;
1234
1235
1236 --
1237 -- public procedures
1238 --
1239 -------------------------------------------------------------------------------
1240 -- Procedure: create_order_from_k
1241 -- Version: 1.0
1242 -- Purpose: Create an order from a contract by populating quote
1243 -- input records from a contract as the initial
1244 -- stage.
1245 -- Create Relationships from ordering contract to order
1246 -- May also create subject-to relationship from order
1247 -- to master contract if ordering contract is subject
1248 -- to a master contract
1249 -- Calls ASO_ORDER_PUB.CREATE_ORDER to create the order
1250 -- In Parameters: p_contract_id Contract for which to create order
1251 -- Out Parameters: x_order_id Id of created order
1252 -----------------------------------------------------------------------------
1253 PROCEDURE create_order_from_k(
1254 p_api_version IN NUMBER
1255 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
1256 ,x_return_status OUT NOCOPY VARCHAR2
1257 ,x_msg_count OUT NOCOPY NUMBER
1258 ,x_msg_data OUT NOCOPY VARCHAR2
1259 ,p_contract_id IN okc_k_headers_b.ID%TYPE
1260 ,p_default_date IN DATE DEFAULT OKC_API.G_MISS_DATE
1261 ,P_Customer_id IN NUMBER
1262 ,P_Grp_id IN NUMBER
1263 ,P_org_id IN NUMBER
1264 ,P_contract_hdr_id_lo in NUMBER
1265 ,P_contract_hdr_id_hi in NUMBER
1266 -- Bug 4915691 --
1267 ,P_contract_line_id_lo in NUMBER
1268 ,P_contract_line_id_hi in NUMBER
1269 -- Bug 4915691 --
1270 ,x_order_id OUT NOCOPY okx_order_headers_v.id1%TYPE
1271 )
1272 IS
1273
1274 -- Standard api variables
1275
1276
1277 l_api_version CONSTANT NUMBER := 1;
1278 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_O_FROM_K';
1279 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1280 l_msg_count NUMBER := 0;
1281 l_msg_data VARCHAR2(1000);
1282
1283 -- Miscellaneous variables
1284 l_idx BINARY_INTEGER; -- generic table index
1285 l_ord_creation_message VARCHAR2(1000);
1286 l_aso_api_versiOn CONSTANT NUMBER := 1;
1287 l_init_msg_count NUMBER;
1288 k NUMBER;
1289 l_create_order_flag VARCHAR2(10);
1290 --
1291 l_k_header_rec c_k_header%ROWTYPE;
1292 l_chrv_rec okc_contract_pub.chrv_rec_type;
1293 -- Bug 4915691 --
1294 l_clev_rec okc_contract_pub.clev_rec_type;
1295
1296 -- Bug 4915691 --
1297 l_k2q_line_rel_tab okc_oc_int_config_pvt.line_rel_tab_type; -- keeps track of k line to q line relation
1298
1299 -- Variables for calling create_order
1300 l_qte_header_rec ASO_QUOTE_PUB.Qte_Header_Rec_Type;
1301 l_Header_Payment_Tbl ASO_QUOTE_PUB.Payment_Tbl_Type;
1302 l_quote_hd_Price_Adj_Tab ASO_QUOTE_PUB.Price_Adj_Tbl_Type;
1303 l_quote_hd_price_attr_tab ASO_QUOTE_PUB.Price_Attributes_Tbl_Type;
1304 l_qte_hd_price_adj_rltship_tab ASO_QUOTE_PUB.Price_Adj_Rltship_Tbl_Type;
1305 l_quote_hd_price_adj_attr_tab ASO_QUOTE_PUB.Price_Adj_Attr_Tbl_Type;
1306 l_quote_hd_shipment_tab ASO_QUOTE_PUB.shipment_tbl_type;
1307 l_Header_Shipment_Tbl ASO_QUOTE_PUB.Shipment_Tbl_Type;
1308 l_Header_TAX_DETAIL_Tbl ASO_QUOTE_PUB.TAX_DETAIL_Tbl_Type;
1309 l_Header_FREIGHT_CHARGE_Tbl ASO_QUOTE_PUB.FREIGHT_CHARGE_Tbl_Type;
1310 l_quote_line_tab ASO_QUOTE_PUB.Qte_Line_Tbl_Type;
1311 l_qte_line_dtl_tab ASO_QUOTE_PUB.Qte_Line_Dtl_Tbl_Type;
1312 l_Line_Payment_Tbl ASO_QUOTE_PUB.Payment_Tbl_Type;
1313 l_quote_ln_price_adj_tab ASO_QUOTE_PUB.Price_Adj_Tbl_Type;
1314 l_quote_Ln_Price_Attr_Tab ASO_QUOTE_PUB.Price_Attributes_Tbl_Type;
1315 l_qte_ln_price_adj_rltship_tab ASO_QUOTE_PUB.Price_Adj_Rltship_Tbl_Type;
1316 l_quote_ln_price_adj_attr_tab ASO_QUOTE_PUB.Price_Adj_Attr_Tbl_Type;
1317 l_quote_ln_Shipment_Tab ASO_QUOTE_PUB.Shipment_Tbl_Type;
1318 l_Line_TAX_DETAIL_Tbl ASO_QUOTE_PUB.TAX_DETAIL_Tbl_Type;
1319 l_Line_FREIGHT_CHARGE_Tbl ASO_QUOTE_PUB.FREIGHT_CHARGE_Tbl_Type;
1320 l_Line_ATTRIBS_EXT_Tbl ASO_QUOTE_PUB.Line_Attribs_Ext_Tbl_Type;
1321 l_Line_Rltship_Tbl ASO_QUOTE_PUB.Line_Rltship_Tbl_Type;
1322 l_line_rltship_tab ASO_QUOTE_PUB.line_rltship_tbl_type;
1323 x_line_rltship_tab ASO_QUOTE_PUB.line_rltship_tbl_type;
1324 --
1325 l_Control_Rec ASO_ORDER_INT.Control_Rec_Type ;
1326 l_Line_sales_credit_TBL ASO_ORDER_INT.Sales_credit_tbl_type;
1327 l_Lot_Serial_Tbl ASO_QUOTE_PUB.Lot_Serial_Tbl_Type;
1328 l_header_attribs_ext_tbl ASO_QUOTE_PUB.line_attribs_ext_tbl_type;
1329 l_header_quote_party_tbl ASO_QUOTE_PUB.quote_party_tbl_type;
1330 l_line_quote_party_tbl ASO_QUOTE_PUB.quote_party_tbl_type;
1331 lx_order_header_rec ASO_ORDER_INT.Order_Header_Rec_Type;
1332 lx_order_line_tbl ASO_ORDER_INT.Order_Line_Tbl_type;
1333 l_header_sales_credit_TBL ASO_ORDER_INT.Sales_credit_tbl_type;
1334 lx_clev_tbl okc_cle_pvt.clev_tbl_type;
1335
1336 l_order_hd_sales_credit_tab ASO_QUOTE_PUB.sales_credit_tbl_type;
1337 l_order_ln_sales_credit_tab ASO_QUOTE_PUB.sales_credit_tbl_type;
1338
1339 --
1340 l_ord_creation_date oe_order_headers_all.creation_date%TYPE;
1341 l_ord_num oe_order_headers_all.order_number%TYPE;
1342 l_ord_version_number oe_order_headers_all.version_number%TYPE;
1343 l_ord_expiration_date oe_order_headers_all.expiration_date%TYPE;
1344 l_kto_found varchar2(1);
1345 l_elements_found varchar2(1):='N';
1346
1347 ---cursor for getting the subscription details
1348 cursor get_header_id(p_contract_id IN NUMBEr) is
1349 select
1350 hdr.id,
1351 hdr.contract_number
1352 from
1353 okc_k_headers_b hdr,
1354 okc_k_party_roles_b pr
1355 where hdr.id = nvl(p_contract_id ,hdr.id)
1356 and hdr.id between nvl(nvl(p_contract_id,p_contract_hdr_id_lo),hdr.id)
1357 and nvl(nvl(p_contract_id,p_contract_hdr_id_hi),hdr.id)
1358 And pr.chr_id = hdr.id
1359 And pr.rle_code = 'SUBSCRIBER'
1360 And pr.object1_id1 = nvl(p_customer_id,pr.object1_id1)
1361 And Hdr.authoring_org_id = NVL(p_org_id, Hdr.authoring_org_id)
1362 AND hdr.sts_code <> 'QA_HOLD'
1363 And exists (Select 'x' from OKC_K_GRPINGS okg
1364 Where okg.included_chr_id = hdr.id
1365 And okg.cgp_parent_id = nvl(p_grp_id,okg.cgp_parent_id) );
1366
1367 /* Commented by sjanakir for Bug# 5568285 (FP Bug for 5442268) */
1368 /* cursor get_elements(p_contract_id IN NUMBEr) is
1369 select sub.id,
1370 sub.dnz_chr_id,
1371 sub.dnz_cle_id,
1372 sub.start_date,
1373 sub.end_date,
1374 sub.om_interface_date
1375 from oks_subscr_elements sub ,
1376 okc_k_lines_b line,
1377 okc_statuses_b sts
1378 where sub.dnz_chr_id = nvl(p_contract_id ,sub.dnz_chr_id)
1379 and sub.dnz_chr_id between nvl(nvl(p_contract_id,p_contract_hdr_id_lo),sub.dnz_chr_id)
1380 and nvl(nvl(p_contract_id,p_contract_hdr_id_hi),sub.dnz_chr_id)
1381 and sub.order_header_id is null
1382 and sub.om_interface_date <= nvl(p_default_date,sysdate)
1383 and sub.dnz_cle_id = line.id
1384 and line.lse_id = 46
1385 and line.sts_code = sts.code
1386 and sts.ste_code IN('ACTIVE','SIGNED','EXPIRED','TERMINATED')
1387 -- Bug 4915691 --
1388 and line.id between nvl(p_contract_line_id_lo,line.id)
1389 and nvl(p_contract_line_id_hi,line.id);
1390 -- Bug 4915691 -- */
1391 /* Modified by sjanakir for Bug# 5568285 (FP Bug for 5442268) */
1392 cursor get_elements is
1393 select sub.id,
1394 sub.dnz_chr_id,
1395 sub.dnz_cle_id,
1396 sub.start_date,
1397 sub.end_date,
1398 sub.om_interface_date,
1399 hdr.contract_number
1400 from oks_subscr_elements sub,
1401 okc_k_headers_b hdr,
1402 okc_k_party_roles_b pr,
1403 okc_statuses_b st
1404 where sub.dnz_chr_id = nvl(p_contract_id ,sub.dnz_chr_id)
1405 --== and sub.dnz_chr_id between nvl(nvl(p_contract_id,p_contract_hdr_id_lo),sub.dnz_chr_id)
1406 --== and nvl(nvl(p_contract_id,p_contract_hdr_id_hi),sub.dnz_chr_id)
1407 and hdr.id = sub.dnz_chr_id
1408 and pr.chr_id = hdr.id
1409 and pr.rle_code = 'SUBSCRIBER'
1410 and st.code = hdr.sts_code
1411 And pr.object1_id1 = nvl(p_customer_id,pr.object1_id1)
1412 And Hdr.authoring_org_id = NVL(p_org_id, Hdr.authoring_org_id)
1413 And exists (Select 'x' from OKC_K_GRPINGS okg
1414 Where okg.included_chr_id = hdr.id
1415 And okg.cgp_parent_id = nvl(p_grp_id,okg.cgp_parent_id) )
1416 and sub.order_header_id is null
1417 and sub.om_interface_date <= nvl(p_default_date,sysdate)
1418 and st.ste_code in ('ACTIVE','SIGNED')
1419 -- Bug 4915674 --
1420 and sub.dnz_cle_id between nvl(p_contract_line_id_lo,sub.dnz_cle_id)
1421 and nvl(p_contract_line_id_hi,sub.dnz_cle_id);
1422 -- Bug 4915674 --
1423 /* Modification Ends */
1424
1425 get_elements_rec get_elements%rowtype;
1426 get_header_id_rec get_header_id%rowtype;
1427
1428 BEGIN
1429
1430 l_quote_line_tab.DELETE;
1431 l_qte_line_dtl_tab.DELETE;
1432
1433 l_quote_hd_shipment_tab.DELETE;
1434 l_quote_ln_shipment_tab.DELETE;
1435
1436 l_k2q_line_rel_tab.DELETE;
1437 l_line_rltship_tab.DELETE;
1438 x_line_rltship_tab.DELETE;
1439
1440 l_order_hd_sales_credit_tab.DELETE;
1441 l_order_ln_sales_credit_tab.DELETE;
1442
1443 l_header_sales_credit_tbl.DELETE;
1444 l_line_sales_credit_tbl.DELETE;
1445
1446 l_quote_hd_price_adj_tab.DELETE;
1447 l_quote_ln_price_adj_tab.DELETE;
1448
1449 l_quote_hd_price_adj_attr_tab.DELETE;
1450 l_quote_ln_price_adj_attr_tab.DELETE;
1451
1452 l_qte_hd_price_adj_rltship_tab.DELETE;
1453 l_qte_ln_price_adj_rltship_tab.DELETE;
1454
1455 l_quote_hd_price_attr_tab.DELETE;
1456 l_quote_ln_price_attr_tab.DELETE;
1457
1458
1459 l_init_msg_count:=fnd_msg_pub.count_msg;
1460 --FND_FILE.PUT_LINE( FND_FILE.LOG, 'Contract Number = '||l_chr.contract_number);
1461 FND_FILE.PUT_LINE( FND_FILE.OUTPUT, '***************Subscription Contract to Order Creation****************** ');
1462
1463 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Contract id = '||p_contract_id);
1464 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Contract id lo = '||p_contract_hdr_id_lo);
1465 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Contract id high= '||p_contract_hdr_id_hi);
1466 -- Bug 4915691 --
1467 -- FND_FILE.PUT_LINE( FND_FILE.LOG, 'Contract Lines Details ');
1468 -- FND_FILE.PUT_LINE( FND_FILE.LOG, 'Contract Line id lo = '||p_contract_line_id_lo);
1469 -- FND_FILE.PUT_LINE( FND_FILE.LOG, 'Contract Line id high = '||p_contract_line_id_hi);
1470 -- Bug 4915691 --
1471
1472
1473 --
1474 -- fetch the contract
1475 --
1476 /* Commented by sjanakir for Bug# 5568285 (FP Bug for 5442268) */
1477 /* FOR get_header_id_rec IN get_header_id(p_contract_id)
1478 LOOP
1479 FOR get_elements_rec IN get_elements(get_header_id_rec.id) */
1480 /* Modified by sjanakir for Bug# 5568285 (FP Bug for 5442268) */
1481 FOR get_elements_rec IN get_elements
1482 LOOP
1483 l_elements_found:='Y';
1484
1485 OPEN c_chr(get_elements_rec.dnz_chr_id);
1486 FETCH c_chr INTO l_chr;
1487 FND_FILE.PUT_LINE( FND_FILE.OUTPUT, '************************ ');
1488 FND_FILE.PUT_LINE( FND_FILE.LOG, '************************ ');
1489 FND_FILE.PUT_LINE( FND_FILE.OUTPUT, 'Contract Number = '||l_chr.contract_number);
1490 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Contract Number = '||l_chr.contract_number);
1491 IF c_chr%NOTFOUND THEN
1492 okc_api.set_message(OKC_API.G_APP_NAME,'OKS_K2O_NOKHDR');
1493 CLOSE c_chr;
1494 x_return_status := OKC_API.G_RET_STS_ERROR;
1495 print_error(2);
1496 CLOSE c_chr;
1497 RAISE OKC_API.G_EXCEPTION_ERROR;
1498 END IF;
1499 CLOSE c_chr;
1500
1501 -- might need this for error messages
1502 IF l_chr.contract_number_modifier IS NOT NULL THEN
1503 l_k_nbr := l_chr.contract_number||'-'||l_chr.contract_number_modifier;
1504 ELSE
1505 l_k_nbr := l_chr.contract_number;
1506 END IF;
1507
1508 -- Bug 4915691 --
1509 -- lock the contract
1510 -- - to avoid a concurrent access to the contract for update, renewal...
1511 -- - to update contract comments
1512 --
1513
1514 l_chrv_rec.id :=get_elements_rec.dnz_chr_id;
1515 l_chrv_rec.object_version_number := l_chr.object_version_number;
1516
1517 OPEN c_cle(get_elements_rec.dnz_cle_id);
1518 FETCH c_cle INTO l_cle;
1519 close c_cle;
1520 l_clev_rec.id := get_elements_rec.dnz_cle_id;
1521 l_clev_rec.object_version_number := l_cle.object_version_number;
1522
1523 /*
1524 okc_contract_pub.lock_contract_header (
1525 p_api_version => 1,
1526 p_init_msg_list => OKC_API.G_FALSE,
1527 x_return_status => l_return_status,
1528 x_msg_count => l_msg_count,
1529 x_msg_data => l_msg_data,
1530 p_chrv_rec => l_chrv_rec);
1531
1532 */
1533 okc_contract_pub.lock_contract_line (
1534 p_api_version => 1,
1535 p_init_msg_list => OKC_API.G_FALSE,
1536 x_return_status => l_return_status,
1537 x_msg_count => l_msg_count,
1538 x_msg_data => l_msg_data,
1539 p_clev_rec => l_clev_rec);
1540
1541 -- Bug 4915691 --
1542
1543 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR OR
1544 l_return_status = OKC_API.G_RET_STS_ERROR THEN
1545 OKC_API.set_message(p_app_name => g_app_name,
1546 p_msg_name => 'OKS_K2O_KLOCKED',
1547 p_token1 => 'NUMBER',
1548 p_token1_value => l_k_nbr);
1549 print_error(2);
1550 END IF; -- IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR OR
1551
1552 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1553 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1554 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1555 RAISE OKC_API.G_EXCEPTION_ERROR;
1556 END IF;
1557
1558
1559 /*************** set organization context***************************/
1560 /*
1561 IF p_contract_id IS NULL THEN
1562 OKC_API.set_message(p_app_name => g_app_name,
1563 p_msg_name => 'OKS_K2O_KIDISNULL');
1564 print_error(2);
1565 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1566 END IF;
1567 */
1568 okc_context.set_okc_org_context(p_chr_id => get_elements_rec.dnz_chr_id);
1569 /******************************************************************/
1570
1571 FND_FILE.PUT_LINE( FND_FILE.LOG, 'before header cursor');
1572 OPEN c_k_header(get_elements_rec.dnz_chr_id);
1573 FETCH c_k_header INTO l_k_header_rec;
1574 CLOSE c_k_header;
1575
1576
1577 FND_FILE.PUT_LINE( FND_FILE.LOG, 'before validate_k_eligibility');
1578
1579 validate_k_eligibility(get_elements_rec.dnz_chr_id,
1580 -- p_rel_type,
1581 l_k_header_rec,
1582 l_return_status );
1583
1584
1585 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1586 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1587 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1588 RAISE OKC_API.G_EXCEPTION_ERROR;
1589 END IF;
1590 /******************************************************************/
1591 --
1592 -- get the contract information
1593 --
1594 FND_FILE.PUT_LINE( FND_FILE.LOG, 'before build_k_structures');
1595
1596 build_k_structures(p_chr_id => get_elements_rec.dnz_chr_id,
1597 p_cle_id => get_elements_rec.dnz_cle_id,
1598 p_k_header_rec => l_k_header_rec,
1599 x_return_status => l_return_status );
1600
1601 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1602 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1603 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1604 RAISE OKC_API.G_EXCEPTION_ERROR;
1605 END IF;
1606
1607 /******************************************************************/
1608
1609 --
1610 -- populate Order header record
1611 --
1612 FND_FILE.PUT_LINE( FND_FILE.LOG, 'before build_order_hdr');
1613 build_order_hdr(px_qte_hdr_rec => l_qte_header_rec,
1614 p_contract_id => get_elements_rec.dnz_chr_id,
1615 px_hd_shipment_tbl => l_quote_hd_shipment_tab,
1616 x_return_status => l_return_status);
1617
1618 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1619 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1620 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1621 RAISE OKC_API.G_EXCEPTION_ERROR;
1622 END IF;
1623
1624 /******************************************************************/
1625 --
1626 -- populate Order lines table, line details
1627 --
1628
1629 FND_FILE.PUT_LINE( FND_FILE.LOG, 'before build_order_line');
1630 build_order_line(px_qte_line_tbl => l_quote_line_tab
1631 ,px_qte_hdr_rec => l_qte_header_rec
1632 ,px_qte_ln_shipment_tbl => l_quote_ln_shipment_tab
1633 ,px_qte_line_dtl_tbl => l_qte_line_dtl_tab
1634 ,px_k2q_line_tbl => l_k2q_line_rel_tab
1635 ,x_return_status => l_return_status );
1636
1637
1638 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1639 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1640 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1641 RAISE OKC_API.G_EXCEPTION_ERROR;
1642 END IF;
1643
1644 l_line_rltship_tab := x_line_rltship_tab;
1645 /******************************************************************/
1646 --
1647 -- set control record, need to set additional attributes
1648 --
1649
1650 l_control_rec.book_flag := FND_API.G_FALSE;
1651
1652
1653 fnd_profile.put('UNIQUE:SEQ_NUMBERS','A');
1654 FND_FILE.PUT_LINE( FND_FILE.LOG, 'before create order');
1655
1656 ASO_ORDER_INT.Create_order(
1657 P_Api_Version => l_aso_api_version,
1658 P_Init_Msg_List => P_Init_Msg_List,
1659 p_Control_Rec => l_control_rec,
1660 P_Commit => FND_API.G_FALSE,
1661 p_Qte_Rec => l_Qte_Header_Rec,
1662 p_Header_Payment_Tbl => l_Header_Payment_Tbl,
1663 p_Header_Price_Adj_Tbl => l_quote_hd_price_adj_tab,
1664 p_Header_Price_Attributes_Tbl => l_quote_hd_price_attr_tab,
1665 p_Header_Price_Adj_rltship_Tbl => l_qte_hd_Price_Adj_rltship_Tab,
1666 p_Header_Price_Adj_Attr_Tbl => l_quote_hd_Price_Adj_Attr_Tab,
1667 p_Header_Shipment_Tbl => l_quote_hd_Shipment_Tab,
1668 p_Header_TAX_DETAIL_Tbl => l_Header_TAX_DETAIL_Tbl,
1669 p_Header_FREIGHT_CHARGE_Tbl => l_Header_FREIGHT_CHARGE_Tbl,
1670 p_Header_sales_credit_TBL => l_order_hd_sales_credit_tab,
1671 P_Header_ATTRIBS_EXT_Tbl => l_header_attribs_ext_tbl,
1672 P_Header_Quote_Party_Tbl => l_header_quote_party_tbl,
1673 p_Qte_Line_Tbl => l_quote_line_tab,
1674 p_Qte_Line_Dtl_Tbl => l_qte_line_dtl_tab,
1675 p_Line_Payment_Tbl => l_Line_Payment_Tbl,
1676 p_Line_Price_Adj_Tbl => l_quote_ln_Price_Adj_Tab,
1677 p_Line_Price_Attributes_Tbl => l_quote_ln_Price_Attr_Tab,
1678 p_Line_Price_Adj_rltship_Tbl => l_qte_ln_Price_Adj_rltship_Tab,
1679 p_Line_Price_Adj_Attr_Tbl => l_quote_ln_Price_Adj_Attr_Tab,
1680 p_Line_Shipment_Tbl => l_quote_ln_Shipment_Tab,
1681 p_Line_TAX_DETAIL_Tbl => l_Line_TAX_DETAIL_Tbl,
1682 p_Line_FREIGHT_CHARGE_Tbl => l_Line_FREIGHT_CHARGE_Tbl,
1683 P_LINE_ATTRIBS_EXT_TBL => l_Line_ATTRIBS_EXT_Tbl,
1684 p_Line_Rltship_Tbl => l_Line_Rltship_Tab,
1685 P_Line_sales_credit_TBL => l_order_ln_sales_credit_tab,
1686 P_Line_Quote_Party_Tbl => l_line_quote_party_tbl,
1687 P_Lot_Serial_Tbl => l_Lot_Serial_Tbl,
1688 X_Order_Header_Rec => lx_order_header_rec,
1689 X_Order_Line_Tbl => lx_order_line_tbl,
1690 X_Return_Status => l_return_status,
1691 X_Msg_Count => l_msg_count,
1692 X_Msg_Data => l_msg_data);
1693
1694 IF l_return_status<>'S' Then
1695 l_create_order_flag:='N';
1696 END IF;
1697
1698 FND_FILE.PUT_LINE( FND_FILE.LOG, '****************************************** '); FND_FILE.PUT_LINE( FND_FILE.LOG, '********************Order Number = '||lx_order_header_rec.order_number);
1699 FND_FILE.PUT_LINE( FND_FILE.LOG,'order create return status = '||l_return_status);
1700 FND_FILE.PUT_LINE( FND_FILE.LOG, '****************************************** ');
1701
1702 IF lx_order_line_tbl.first IS NOT NULL THEN
1703 FOR i IN lx_order_line_tbl.first..lx_order_line_tbl.last LOOP
1704
1705 update oks_subscr_elements
1706 set order_header_id = lx_order_line_tbl(i).order_header_id
1707 ,order_line_id = lx_order_line_tbl(i).order_line_id
1708 where id= get_elements_rec.id;
1709
1710 fnd_file.put_line(FND_FILE.LOG, 'Order Header Id = '||lx_order_line_tbl(i).order_header_id);
1711 fnd_file.put_line(FND_FILE.LOG, 'Order Line Id = '||lx_order_line_tbl(i).order_line_id);
1712 fnd_file.put_line(FND_FILE.LOG, 'Order line status = '||ltrim(rtrim(lx_order_line_tbl(i).status)));
1713
1714 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Before calling OE_ORDER_BOOK_UTIL ');
1715
1716 OE_Order_Book_Util.Complete_Book_Eligible
1717 ( p_api_version_number => 1.0
1718 , p_init_msg_list => okc_api.g_false
1719 , p_header_id => lx_order_line_tbl(i).order_header_id
1720 , x_return_status => l_return_status
1721 , x_msg_count => l_msg_count
1722 , x_msg_data => l_msg_data);
1723 IF l_return_status ='S' THen
1724 FND_FILE.PUT_LINE( FND_FILE.LOG,'Schedule Start Date = '||get_elements_rec.start_date||' '||'Schedule End Date = '||get_elements_rec.end_date);
1725 FND_FILE.PUT_LINE( FND_FILE.OUTPUT,'Schedule Start Date = '||get_elements_rec.start_date||' '||'Schedule End Date = '||get_elements_rec.end_date||' '|| 'OM Interface Date = '||get_elements_rec.om_interface_date);
1726 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Order Number = '||lx_order_header_rec.order_number);
1727 FND_FILE.PUT_LINE( FND_FILE.OUTPUT, 'Order Number = '||lx_order_header_rec.order_number);
1728 FND_FILE.PUT_LINE( FND_FILE.LOG,'order book return status = '||l_return_status);
1729 FND_FILE.PUT_LINE( FND_FILE.OUTPUT,'order book return status = '||l_return_status);
1730 FND_FILE.PUT_LINE( FND_FILE.LOG, '****************************************** ');FND_FILE.PUT_LINE( FND_FILE.OUTPUT, '***************************************** ');
1731 ELSE
1732
1733 FND_FILE.PUT_LINE( FND_FILE.LOG,'order book return status = '||l_return_status);
1734 FND_FILE.PUT_LINE( FND_FILE.OUTPUT, '***************************************** ');
1735 FND_FILE.PUT_LINE( FND_FILE.OUTPUT, 'Unable to CREATE and BOOK the order, please check the log files for error details');
1736 FND_FILE.PUT_LINE( FND_FILE.OUTPUT, '***************************************** ');
1737 END IF;
1738 /*
1739 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1740 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1741 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1742 RAISE OKC_API.G_EXCEPTION_ERROR;
1743 END IF;
1744 */
1745
1746 END LOOP ;
1747
1748 ELSE
1749 FND_FILE.PUT_LINE( FND_FILE.LOG, 'NO Order Lines');
1750 END IF;
1751
1752 --
1753 -- Contract updating with order information waiting for
1754 -- a specific notification creation
1755 --
1756
1757 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR OR
1758 l_return_status = OKC_API.G_RET_STS_ERROR THEN
1759 FND_FILE.PUT_LINE( FND_FILE.LOG,l_msg_data);
1760 l_ord_creation_date:=SYSDATE;
1761 ELSE
1762
1763 SELECT creation_date,
1764 order_number,
1765 version_number,
1766 NVL(expiration_date, OKC_API.G_MISS_DATE)
1767 INTO l_ord_creation_date,
1768 l_ord_num,
1769 l_ord_version_number,
1770 l_ord_expiration_date
1771 FROM oe_order_headers_all
1772 WHERE header_id = lx_order_header_rec.order_header_id;
1773
1774 OKC_API.set_message(p_app_name => g_app_name,
1775 p_msg_name => 'OKS_K2O_K2OCOMMENTS',
1776 p_token1 => 'CRDATE',
1777 p_token1_value => l_ord_creation_date,
1778 p_token2 => 'NUMBER',
1779 p_token2_value => l_ord_num,
1780 p_token3 => 'VERSION',
1781 p_token3_value => l_ord_version_number,
1782 p_token4 => 'EXDATE',
1783 p_token4_value => l_ord_expiration_date,
1784 p_token5 => 'TRACEFILE',
1785 p_token5_value => okc_util.l_complete_trace_file_name2
1786 );
1787 FND_MSG_PUB.Count_And_Get (
1788 p_count => x_msg_count,
1789 p_data => x_msg_data);
1790 x_msg_data := fnd_msg_pub.get(
1791 p_msg_index => x_msg_count,
1792 p_encoded => 'F');
1793
1794 l_ord_creation_message := x_msg_data;
1795 FND_MSG_PUB.Delete_Msg ( p_msg_index => x_msg_count);
1796
1797 END IF; -- IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR OR
1798
1799 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1800 -- RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1801 --RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1802 IF c_k_header%ISOPEN THEN
1803 CLOSE c_k_header;
1804 END IF;
1805
1806 x_return_status := OKC_API.G_RET_STS_ERROR;
1807 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
1808 fnd_msg_pub.add_exc_msg(p_pkg_name => g_pkg_name
1809 ,p_procedure_name => l_api_name
1810 ,p_error_text => 'Encountered error condition'
1811 );
1812 END IF;
1813
1814 --Error messages for the trace file
1815 --Error messages for the output file
1816 IF okc_util.l_output_flag THEN
1817 OKC_API.set_message(p_app_name => g_app_name,
1818 p_msg_name => 'OKS_K2O_K2OOUTEMSG',
1819 p_token1 => 'CRDATE',
1820 p_token1_value => l_ord_creation_date,
1821 p_token2 => 'KNUMBER',
1822 p_token2_value => l_chr.contract_number,
1823 p_token3 => 'KMODIFIER',
1824 p_token3_value => NVL(l_chr.contract_number_modifier, ' ')
1825 );
1826 FND_MSG_PUB.Count_And_Get (
1827 p_count => x_msg_count,
1828 p_data => x_msg_data);
1829 x_msg_data := fnd_msg_pub.get(
1830 p_msg_index => x_msg_count,
1831 p_encoded => 'F');
1832
1833 l_ord_creation_message := x_msg_data;
1834 FND_MSG_PUB.Delete_Msg ( p_msg_index => x_msg_count);
1835
1836 okc_util.print_output(0, l_ord_creation_message);
1837 END IF;
1838 FND_MSG_PUB.Count_And_Get (
1839 p_count => x_msg_count,
1840 p_data => x_msg_data);
1841 FOR k in l_init_msg_count..x_msg_count LOOP
1842 x_msg_data := fnd_msg_pub.get( p_msg_index => k,
1843 p_encoded => 'F'
1844 );
1845 IF x_msg_data IS NOT NULL THEN
1846 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Message : '||x_msg_data);
1847 FND_FILE.PUT_LINE( FND_FILE.LOG, ' ');
1848 IF okc_util.l_output_flag THEN
1849 okc_util.print_output(0, 'Message : '||x_msg_data);
1850 okc_util.print_output(0, ' ');
1851 END IF;
1852 END IF;
1853 END LOOP;
1854 FND_FILE.PUT_LINE( FND_FILE.LOG, '==================================');
1855
1856
1857 -- IF l_create_order_flag='N' THEN
1858 EXIT;
1859 -- END IF;
1860
1861
1862
1863 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1864 --RAISE OKC_API.G_EXCEPTION_ERROR;
1865 --RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1866 IF c_k_header%ISOPEN THEN
1867 CLOSE c_k_header;
1868 END IF;
1869
1870 x_return_status := OKC_API.G_RET_STS_ERROR;
1871 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
1872 fnd_msg_pub.add_exc_msg(p_pkg_name => g_pkg_name
1873 ,p_procedure_name => l_api_name
1874 ,p_error_text => 'Encountered error condition'
1875 );
1876 END IF;
1877
1878 --Error messages for the trace file
1879 --Error messages for the output file
1880 IF okc_util.l_output_flag THEN
1881 OKC_API.set_message(p_app_name => g_app_name,
1882 p_msg_name => 'OKS_K2O_K2OOUTEMSG',
1883 p_token1 => 'CRDATE',
1884 p_token1_value => l_ord_creation_date,
1885 p_token2 => 'KNUMBER',
1886 p_token2_value => l_chr.contract_number,
1887 p_token3 => 'KMODIFIER',
1888 p_token3_value => NVL(l_chr.contract_number_modifier, ' ')
1889 );
1890 FND_MSG_PUB.Count_And_Get (
1891 p_count => x_msg_count,
1892 p_data => x_msg_data);
1893 x_msg_data := fnd_msg_pub.get(
1894 p_msg_index => x_msg_count,
1895 p_encoded => 'F');
1896
1897 l_ord_creation_message := x_msg_data;
1898 FND_MSG_PUB.Delete_Msg ( p_msg_index => x_msg_count);
1899
1900 okc_util.print_output(0, l_ord_creation_message);
1901 END IF;
1902 FND_MSG_PUB.Count_And_Get (
1903 p_count => x_msg_count,
1904 p_data => x_msg_data);
1905 FOR k in l_init_msg_count..x_msg_count LOOP
1906 x_msg_data := fnd_msg_pub.get( p_msg_index => k,
1907 p_encoded => 'F'
1908 );
1909 IF x_msg_data IS NOT NULL THEN
1910 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Message : '||x_msg_data);
1911 FND_FILE.PUT_LINE( FND_FILE.LOG, ' ');
1912 IF okc_util.l_output_flag THEN
1913 okc_util.print_output(0, 'Message : '||x_msg_data);
1914 okc_util.print_output(0, ' ');
1915 END IF;
1916 END IF;
1917 END LOOP;
1918 FND_FILE.PUT_LINE( FND_FILE.LOG, '==================================');
1919
1920
1921 -- IF l_create_order_flag='N' THEN
1922 EXIT;
1923 -- END IF;
1924
1925
1926
1927 END IF;
1928
1929 --
1930 -- capture the order id for the return
1931 --
1932
1933 x_order_id := lx_order_header_rec.order_header_id;
1934
1935 --
1936 -- we're done
1937 --
1938
1939 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1940
1941
1942 -- In the output file (If conc. prog.)
1943 IF okc_util.l_output_flag THEN
1944 OKC_API.set_message(p_app_name => g_app_name,
1945 p_msg_name => 'OKS_K2O_K2OOUTSMSG',
1946 p_token1 => 'CRDATE',
1947 p_token1_value => l_ord_creation_date,
1948 p_token2 => 'ONUMBER',
1949 p_token2_value => l_ord_num,
1950 p_token3 => 'VERSION',
1951 p_token3_value => l_ord_version_number,
1952 p_token4 => 'EXDATE',
1953 p_token4_value => l_ord_expiration_date,
1954 p_token5 => 'KNUMBER',
1955 p_token5_value => l_chr.contract_number,
1956 p_token6 => 'KMODIFIER',
1957 p_token6_value => NVL(l_chr.contract_number_modifier, ' ')
1958 );
1959 FND_MSG_PUB.Count_And_Get (
1960 p_count => x_msg_count,
1961 p_data => x_msg_data);
1962
1963 x_msg_data := fnd_msg_pub.get(
1964 p_msg_index => x_msg_count,
1965 p_encoded => 'F');
1966
1967 l_ord_creation_message := x_msg_data;
1968 FND_MSG_PUB.Delete_Msg ( p_msg_index => x_msg_count);
1969
1970 okc_util.print_output(0, l_ord_creation_message);
1971 FND_MSG_PUB.Count_And_Get (
1972 p_count => x_msg_count,
1973 p_data => x_msg_data);
1974 FOR k in l_init_msg_count..x_msg_count LOOP
1975 x_msg_data := fnd_msg_pub.get( p_msg_index => k,
1976 p_encoded => 'F'
1977 );
1978 IF x_msg_data IS NOT NULL THEN
1979 okc_util.print_output(0, 'Message : '||x_msg_data);
1980 okc_util.print_output(0, ' ');
1981 END IF;
1982 END LOOP;
1983 END IF;
1984
1985 l_contract_number:=l_chr.contract_number;
1986 l_contract_number_modifier:=l_chr.contract_number_modifier;
1987 l_order_number:=lx_order_header_rec.order_number;
1988
1989 END LOOP; -----------get_elements cursor
1990 IF
1991 l_elements_found='N' Then
1992 /* Commented by sjanakir for Bug# 5568285 (FP Bug for 5442268) */
1993 /* fnd_file.put_line(FND_FILE.LOG, 'No subscription elements to be interfaced for the given Contract number'||' '||get_header_id_rec.contract_number);
1994 fnd_file.put_line(FND_FILE.OUTPUT, ' ');
1995 fnd_file.put_line(FND_FILE.OUTPUT, ' ');
1996 fnd_file.put_line(FND_FILE.OUTPUT, 'No subscription elements to be interfaced for the given Contract number'||' '||get_header_id_rec.contract_number); */
1997
1998 /* Modified by sjanakir for Bug# 5568285 (FP Bug for 5442268) */
1999 fnd_file.put_line(FND_FILE.LOG, 'No subscription elements to be interfaced ');
2000 fnd_file.put_line(FND_FILE.OUTPUT, ' ');
2001 fnd_file.put_line(FND_FILE.OUTPUT, ' ');
2002 fnd_file.put_line(FND_FILE.OUTPUT, 'No subscription elements to be interfaced ');
2003
2004 END IF;
2005 /* Commented by sjanakir for Bug# 5568285 (FP Bug for 5442268) */
2006 /* END LOOP; -----------get_header_id cursor */
2007 /* Comment Ends */
2008 /*
2009 iF
2010 l_elements_found='N' Then
2011 fnd_file.put_line(FND_FILE.LOG, 'No subscription elements for the given Contract number');
2012 fnd_file.put_line(FND_FILE.OUTPUT, ' ');
2013 fnd_file.put_line(FND_FILE.OUTPUT, ' ');
2014 fnd_file.put_line(FND_FILE.OUTPUT, 'No subscription elements for the given Contract number');
2015
2016 END IF;
2017 */
2018 EXCEPTION
2019 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2020 --update_k_comments_err;
2021 IF c_k_header%ISOPEN THEN
2022 CLOSE c_k_header;
2023 END IF;
2024
2025 x_return_status := OKC_API.G_RET_STS_ERROR;
2026 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2027 fnd_msg_pub.add_exc_msg(p_pkg_name => g_pkg_name
2028 ,p_procedure_name => l_api_name
2029 ,p_error_text => 'Encountered error condition'
2030 );
2031 END IF;
2032
2033 --Error messages for the trace file
2034 --Error messages for the output file
2035 IF okc_util.l_output_flag THEN
2036 OKC_API.set_message(p_app_name => g_app_name,
2037 p_msg_name => 'OKS_K2O_K2OOUTEMSG',
2038 p_token1 => 'CRDATE',
2039 p_token1_value => l_ord_creation_date,
2040 p_token2 => 'KNUMBER',
2041 p_token2_value => l_chr.contract_number,
2042 p_token3 => 'KMODIFIER',
2043 p_token3_value => NVL(l_chr.contract_number_modifier, ' ')
2044 );
2045 FND_MSG_PUB.Count_And_Get (
2046 p_count => x_msg_count,
2047 p_data => x_msg_data);
2048 x_msg_data := fnd_msg_pub.get(
2049 p_msg_index => x_msg_count,
2050 p_encoded => 'F');
2051
2052 l_ord_creation_message := x_msg_data;
2053 FND_MSG_PUB.Delete_Msg ( p_msg_index => x_msg_count);
2054
2055 okc_util.print_output(0, l_ord_creation_message);
2056 END IF;
2057 FND_MSG_PUB.Count_And_Get (
2058 p_count => x_msg_count,
2059 p_data => x_msg_data);
2060 FOR k in l_init_msg_count..x_msg_count LOOP
2061 x_msg_data := fnd_msg_pub.get( p_msg_index => k,
2062 p_encoded => 'F'
2063 );
2064 IF x_msg_data IS NOT NULL THEN
2065 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Message : '||x_msg_data);
2066 FND_FILE.PUT_LINE( FND_FILE.LOG, ' ');
2067 IF okc_util.l_output_flag THEN
2068 okc_util.print_output(0, 'Message : '||x_msg_data);
2069 okc_util.print_output(0, ' ');
2070 END IF;
2071 END IF;
2072 END LOOP;
2073 FND_FILE.PUT_LINE( FND_FILE.LOG, '==================================');
2074 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2075 IF c_k_header%ISOPEN THEN
2076 CLOSE c_k_header;
2077 END IF;
2078
2079 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2080 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2081 fnd_msg_pub.add_exc_msg(p_pkg_name => g_pkg_name
2082 ,p_procedure_name => l_api_name
2083 ,p_error_text => 'Encountered unexpected error'
2084 );
2085 END IF;
2086
2087 --Error messages for the trace file
2088 FND_FILE.PUT_LINE( FND_FILE.LOG, ' ');
2089 FND_FILE.PUT_LINE( FND_FILE.LOG, '==================================');
2090 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Error while creating order:');
2091 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Return status: '||x_return_status);
2092 FND_FILE.PUT_LINE( FND_FILE.LOG, '==================================');
2093 --Error messages for the output file
2094 IF okc_util.l_output_flag THEN
2095 OKC_API.set_message(p_app_name => g_app_name,
2096 p_msg_name => 'OKS_K2O_K2OOUTEMSG',
2097 p_token1 => 'CRDATE',
2098 p_token1_value => l_ord_creation_date,
2099 p_token2 => 'KNUMBER',
2100 p_token2_value => l_chr.contract_number,
2101 p_token3 => 'KMODIFIER',
2102 p_token3_value => NVL(l_chr.contract_number_modifier, ' ')
2103 );
2104 FND_MSG_PUB.Count_And_Get (
2105 p_count => x_msg_count,
2106 p_data => x_msg_data);
2107 x_msg_data := fnd_msg_pub.get(
2108 p_msg_index => x_msg_count,
2109 p_encoded => 'F');
2110
2111 l_ord_creation_message := x_msg_data;
2112 FND_MSG_PUB.Delete_Msg ( p_msg_index => x_msg_count);
2113
2114 okc_util.print_output(0, l_ord_creation_message);
2115 END IF;
2116 FND_MSG_PUB.Count_And_Get (
2117 p_count => x_msg_count,
2118 p_data => x_msg_data);
2119 FOR k in l_init_msg_count..x_msg_count LOOP
2120 x_msg_data := fnd_msg_pub.get( p_msg_index => k,
2121 p_encoded => 'F'
2122 );
2123 IF x_msg_data IS NOT NULL THEN
2124 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Message : '||x_msg_data);
2125 FND_FILE.PUT_LINE( FND_FILE.LOG, ' ');
2126 IF okc_util.l_output_flag THEN
2127 okc_util.print_output(0, 'Message : '||x_msg_data);
2128 okc_util.print_output(0, ' ');
2129 END IF;
2130 END IF;
2131 END LOOP;
2132
2133 WHEN OTHERS THEN
2134 --update_k_comments_err;
2135 IF c_k_header%ISOPEN THEN
2136 CLOSE c_k_header;
2137 END IF;
2138
2139 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2140 OKC_API.set_message(G_APP_NAME
2141 ,G_UNEXPECTED_ERROR
2142 ,G_SQLCODE_TOKEN
2143 ,SQLCODE
2144 ,G_SQLERRM_TOKEN
2145 ,SQLERRM);
2146
2147 --Error messages for the trace file
2148 FND_FILE.PUT_LINE( FND_FILE.LOG, ' ');
2149 FND_FILE.PUT_LINE( FND_FILE.LOG, '==================================');
2150 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Error while creating order:');
2151 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Return status: '||x_return_status);
2152 FND_FILE.PUT_LINE( FND_FILE.LOG, '==================================');
2153 --Error messages for the output file
2154 IF okc_util.l_output_flag THEN
2155 OKC_API.set_message(p_app_name => g_app_name,
2156 p_msg_name => 'OKS_K2O_K2OOUTEMSG',
2157 p_token1 => 'CRDATE',
2158 p_token1_value => l_ord_creation_date,
2159 p_token2 => 'KNUMBER',
2160 p_token2_value => l_chr.contract_number,
2161 p_token3 => 'KMODIFIER',
2162 p_token3_value => NVL(l_chr.contract_number_modifier, ' ')
2163 );
2164 FND_MSG_PUB.Count_And_Get (
2165 p_count => x_msg_count,
2166 p_data => x_msg_data);
2167 x_msg_data := fnd_msg_pub.get(
2168 p_msg_index => x_msg_count,
2169 p_encoded => 'F');
2170
2171 l_ord_creation_message := x_msg_data;
2172 FND_MSG_PUB.Delete_Msg ( p_msg_index => x_msg_count);
2173
2174 okc_util.print_output(0, l_ord_creation_message);
2175 END IF;
2176 FND_MSG_PUB.Count_And_Get (
2177 p_count => x_msg_count,
2178 p_data => x_msg_data);
2179 FOR k in l_init_msg_count..x_msg_count LOOP
2180 x_msg_data := fnd_msg_pub.get( p_msg_index => k,
2181 p_encoded => 'F'
2182 );
2183 IF x_msg_data IS NOT NULL THEN
2184 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Message : '||x_msg_data);
2185 FND_FILE.PUT_LINE( FND_FILE.LOG, ' ');
2186 IF okc_util.l_output_flag THEN
2187 okc_util.print_output(0, 'Message : '||x_msg_data);
2188 okc_util.print_output(0, ' ');
2189 END IF;
2190 END IF;
2191 END LOOP;
2192
2193 END create_order_from_k;
2194
2195
2196 END OKS_OC_INT_KTO_PVT;