[Home] [Help]
PACKAGE BODY: APPS.OKS_EXTWAR_UTIL_PUB
Source
1 PACKAGE BODY OKS_EXTWAR_UTIL_PUB AS
2 /* $Header: OKSPUTLB.pls 120.4 2006/02/20 10:21:58 hmnair noship $ */
3
4 l_conc_program VARCHAR2(200) := 'Y';
5
6
7 Procedure Get_Warranty_Info
8 (
9 p_api_version IN Number,
10 p_init_msg_list IN Varchar2,
11 p_Org_id IN Number,
12 p_prod_item_id IN Number,
13 p_date IN Date,
14 x_return_status OUT NOCOPY Varchar2,
15 x_msg_count OUT NOCOPY Number,
16 x_msg_data OUT NOCOPY Varchar2,
17 x_warranty_tbl OUT NOCOPY War_tbl
18 )
19 IS
20
21 l_return_status Varchar2(1) := OKC_API.G_RET_STS_SUCCESS;
22 l_api_name CONSTANT VARCHAR2(30) := 'Get_Warranty_Info';
23 BEGIN
24
25 l_return_status := OKC_API.START_ACTIVITY(l_api_name
26 ,p_init_msg_list
27 ,'_PUB'
28 ,x_return_status
29 );
30 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
31 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
32 ELSIF l_return_status = OKC_API.G_RET_STS_ERROR THEN
33 RAISE OKC_API.G_EXCEPTION_ERROR;
34 END IF;
35
36
37 OKS_EXTWAR_UTIL_PVT.Get_Warranty_Info
38 (p_Org_id => p_Org_id
39 ,p_prod_item_id => p_prod_item_id
40 ,p_date => p_date
41 ,x_return_status => l_return_status
42 ,x_warranty_tbl => x_warranty_tbl);
43
44 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
45 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
46 ELSIF l_return_status = OKC_API.G_RET_STS_ERROR THEN
47 RAISE OKC_API.G_EXCEPTION_ERROR;
48 END IF;
49
50 OKC_API.END_ACTIVITY(x_msg_count,x_msg_data);
51
52 x_return_status := l_return_status;
53
54 EXCEPTION
55 WHEN OKC_API.G_EXCEPTION_ERROR THEN
56 x_return_status := OKC_API.HANDLE_EXCEPTIONS
57 (l_api_name,
58 G_PKG_NAME,
59 'OKC_API.G_RET_STS_ERROR',
60 x_msg_count,
61 x_msg_data,
62 '_PUB');
63 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
64 x_return_status := OKC_API.HANDLE_EXCEPTIONS
65 (l_api_name,
66 G_PKG_NAME,
67 'OKC_API.G_RET_STS_UNEXP_ERROR',
68 x_msg_count,
69 x_msg_data,
70 '_PUB');
71 WHEN OTHERS THEN
72 x_return_status := OKC_API.HANDLE_EXCEPTIONS
73 (l_api_name,
74 G_PKG_NAME,
75 'OTHERS',
76 x_msg_count,
77 x_msg_data,
78 '_PUB');
79
80 END Get_Warranty_Info;
81
82
83 Procedure Update_Hdr_Amount
84 (
85 p_api_version IN Number,
86 p_init_msg_list IN Varchar2,
87 p_chr_id IN Number,
88 x_return_status OUT NOCOPY Varchar2,
89 x_msg_count OUT NOCOPY Number,
90 x_msg_data OUT NOCOPY Varchar2
91 )
92 IS
93
94 l_return_status Varchar2(1) := OKC_API.G_RET_STS_SUCCESS;
95 l_api_name CONSTANT VARCHAR2(30) := 'Update_Hdr_Amount';
96 l_api_version Number := 1.0;
97
98 --Contract Header
99 l_chrv_tbl_in okc_contract_pub.chrv_tbl_type;
100 l_chrv_tbl_out okc_contract_pub.chrv_tbl_type;
101
102
103 Cursor l_line_csr Is Select Sum(Nvl(PRICE_NEGOTIATED,0))
104 From OKC_K_LINES_B
105 Where dnz_chr_id = p_chr_id And
106 lse_id in (7,8,9,10,11,35,25);
107
108 l_hdr_amount Number;
109
110 BEGIN
111
112 l_return_status := OKC_API.START_ACTIVITY(l_api_name
113 ,p_init_msg_list
114 ,'_PUB'
115 ,x_return_status
116 );
117 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
118 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
119 ELSIF l_return_status = OKC_API.G_RET_STS_ERROR THEN
120 RAISE OKC_API.G_EXCEPTION_ERROR;
121 END IF;
122
123 Open l_line_csr;
124 Fetch l_line_csr into l_hdr_amount;
125 Close l_line_csr;
126
127
128 l_chrv_tbl_in(1).id := p_chr_id;
129 l_chrv_tbl_in(1).estimated_amount := l_hdr_amount;
130
131 okc_contract_pub.update_contract_header
132 (
133 p_api_version => l_api_version,
134 p_init_msg_list => p_init_msg_list,
135 x_return_status => x_return_status,
136 x_msg_count => x_msg_count,
137 x_msg_data => x_msg_data,
138 p_chrv_tbl => l_chrv_tbl_in,
139 x_chrv_tbl => l_chrv_tbl_out
140 );
141
142 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
143 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
144 ELSIF l_return_status = OKC_API.G_RET_STS_ERROR THEN
145 RAISE OKC_API.G_EXCEPTION_ERROR;
146 END IF;
147
148 OKC_API.END_ACTIVITY(x_msg_count,x_msg_data);
149
150 x_return_status := l_return_status;
151
152 EXCEPTION
153 WHEN OKC_API.G_EXCEPTION_ERROR THEN
154 x_return_status := OKC_API.HANDLE_EXCEPTIONS
155 (l_api_name,
156 G_PKG_NAME,
157 'OKC_API.G_RET_STS_ERROR',
158 x_msg_count,
159 x_msg_data,
160 '_PUB');
161 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
162 x_return_status := OKC_API.HANDLE_EXCEPTIONS
163 (l_api_name,
164 G_PKG_NAME,
165 'OKC_API.G_RET_STS_UNEXP_ERROR',
166 x_msg_count,
167 x_msg_data,
168 '_PUB');
169 WHEN OTHERS THEN
170 x_return_status := OKC_API.HANDLE_EXCEPTIONS
171 (l_api_name,
172 G_PKG_NAME,
173 'OTHERS',
174 x_msg_count,
175 x_msg_data,
176 '_PUB');
177
178 END Update_Hdr_Amount;
179
180
181
182 FUNCTION Create_Timevalue (p_chr_id IN NUMBER,p_start_date IN DATE) RETURN NUMBER Is
183 l_p_tavv_tbl OKC_TIME_PUB.TAVV_TBL_TYPE;
184 l_x_tavv_tbl OKC_TIME_PUB.TAVV_TBL_TYPE;
185 l_api_version Number := 1.0;
186 l_init_msg_list Varchar2(1) := 'F';
187 l_return_status varchar2(200);
188 l_msg_count NUMBER;
189 l_msg_data VARCHAR2(2000);
190 Begin
191 l_p_tavv_tbl(1).id := NULL;
192 l_p_tavv_tbl(1).object_version_number := NULL;
193 l_p_tavv_tbl(1).sfwt_flag := 'N';
194 l_p_tavv_tbl(1).spn_id := NULL;
195 l_p_tavv_tbl(1).tve_id_generated_by := NULL;
196 l_p_tavv_tbl(1).dnz_chr_id := NULL;
197 l_p_tavv_tbl(1).tze_id := NULL;
198 l_p_tavv_tbl(1).tve_id_limited := NULL;
199 l_p_tavv_tbl(1).description := '';
200 l_p_tavv_tbl(1).short_description := '';
201 l_p_tavv_tbl(1).comments := '';
202 l_p_tavv_tbl(1).datetime := null;
203 l_p_tavv_tbl(1).attribute_category := '';
204 l_p_tavv_tbl(1).attribute1 := '';
205 l_p_tavv_tbl(1).attribute2 := '';
206 l_p_tavv_tbl(1).attribute3 := '';
207 l_p_tavv_tbl(1).attribute4 := '';
208 l_p_tavv_tbl(1).attribute5 := '';
209 l_p_tavv_tbl(1).attribute6 := '';
210 l_p_tavv_tbl(1).attribute7 := '';
211 l_p_tavv_tbl(1).attribute8 := '';
212 l_p_tavv_tbl(1).attribute9 := '';
213 l_p_tavv_tbl(1).attribute10 := '';
214 l_p_tavv_tbl(1).attribute11 := '';
215 l_p_tavv_tbl(1).attribute12 := '';
216 l_p_tavv_tbl(1).attribute13 := '';
217 l_p_tavv_tbl(1).attribute14 := '';
218 l_p_tavv_tbl(1).attribute15 := '';
219 l_p_tavv_tbl(1).created_by := NULL;
220 l_p_tavv_tbl(1).creation_date := NULL;
221 l_p_tavv_tbl(1).last_updated_by := NULL;
222 l_p_tavv_tbl(1).last_update_date := NULL;
223 l_p_tavv_tbl(1).last_update_login := NULL;
224
225 l_p_tavv_tbl(1).datetime := p_start_date;
226 l_p_tavv_tbl(1).dnz_chr_id := p_chr_id;
227
228 okc_time_pub.create_tpa_value
229 (p_api_version => l_api_version,
230 p_init_msg_list => l_init_msg_list,
231 x_return_status => l_return_status,
232 x_msg_count => l_msg_count,
233 x_msg_data => l_msg_data,
234 p_tavv_tbl => l_p_tavv_tbl,
235 x_tavv_tbl => l_x_tavv_tbl) ;
236
237 If l_return_status <> 'S' then
238 null;
239 --action
240 End If;
241
242 RETURN(l_x_tavv_tbl(1).id);
243
244 End Create_Timevalue;
245
246 -- This procedure should no longer be used.
247 -- Please use OKS_RENEW_PVT.GET_OKS_RESOURCE
248 PROCEDURE GET_OKS_RESOURCE (
249 p_party_id IN NUMBER,
250 x_return_status OUT NOCOPY Varchar2,
251 x_msg_count OUT NOCOPY Number,
252 x_msg_data OUT NOCOPY Varchar2,
253 x_winning_res_id OUT NOCOPY NUMBER, --l_salesrep_id,
254 x_winning_user_id OUT NOCOPY NUMBER
255 ) IS
256 l_terrkren_rec jtf_territory_pub.jtf_kren_rec_type;
257 l_resource_type varchar2(100) := to_char(null);
258 l_role varchar2(100) := to_char(null);
259 l_return_status varchar2(1);
260 l_msg_count NUMBER;
261 l_msg_data varchar2(2000);
262 l_terrresource_tbl jtf_territory_pub.winningterrmember_tbl_type;
263 l_user_id fnd_user.user_id%TYPE;
264
265 CURSOR resource_details(p_resource_id number) IS
266 SELECT fu.user_id
267 FROM jtf_rs_resource_extns jrd,
268 fnd_user fu
269 WHERE jrd.resource_id=p_resource_id
270 AND fu.user_id = jrd.user_id;
271
272
273 CURSOR l_party_name_csr Is select party_Name from hz_parties where party_id = p_party_id;
274 l_party_name Varchar2(2000);
275
276
277 BEGIN
278
279 fnd_msg_pub.initialize;
280
281 IF (p_party_id = OKC_API.G_MISS_NUM OR p_party_id IS NULL) THEN
282 null;
283 -- Handle missing party ID
284 END IF;
285
286 Open l_party_name_csr;
287 Fetch l_party_name_csr Into l_party_name;
288 close l_party_name_csr;
289
290 l_terrkren_rec.PARTY_ID := p_party_id;
291 l_terrkren_rec.COMP_NAME_RANGE := l_party_name;
292
293
294 jtf_terr_oks_pub.get_winningterrmembers(
295 p_api_version_number => 1.0,
296 p_terrkren_rec => l_terrkren_rec,
297 p_resource_type => l_resource_type,
298 p_role => l_role,
299 x_return_status => l_return_status, -- OUT
300 x_msg_count => l_msg_count, -- OUT
301 x_msg_data => l_msg_data, -- OUT
302 x_terrresource_tbl => l_terrresource_tbl); -- OUT
303
304 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
305 -- Handle error from territory API
306 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
307 null;
308 END IF;
309
310 ----dbms_output.put_line('count res: '||l_terrresource_tbl.count);
311 ----dbms_output.put_line('v_return_status: '||l_return_status);
312 ----dbms_output.put_line('v_msg_count: '||l_msg_count);
313 ----dbms_output.put_line('v_msg_data: '||l_msg_data);
314
315 IF l_terrresource_tbl.count = 0 THEN
316
317 OKC_API.set_message
318 (
319 G_APP_NAME,
320 G_UNEXPECTED_ERROR,
321 G_SQLCODE_TOKEN,
322 SQLCODE,
323 G_SQLERRM_TOKEN,
324 'No resource found'
325 );
326 l_return_status := 'E';
327 null;
328 -- Handle no resource returned
329
330 ELSIF l_terrresource_tbl.count > 1 THEN
331 OKC_API.set_message
332 (
333 G_APP_NAME,
334 G_UNEXPECTED_ERROR,
335 G_SQLCODE_TOKEN,
336 SQLCODE,
337 G_SQLERRM_TOKEN,
338 'Found more than one jtf resource'
339 );
340 l_return_status := 'E';
341 null;
342 -- Handle >1 resource returned
343
344 ELSE -- i.e. l_terrresource_tbl.count=1
345
346 l_user_id := to_number(null);
347 OPEN resource_details(p_resource_id => l_terrresource_tbl(0).resource_id);
348 FETCH resource_details INTO l_user_id;
349 IF resource_details%NOTFOUND THEN
350 null;
351 -- Handle no user_id found;
352 END IF;
353 CLOSE resource_details;
354
355 ----dbms_output.put_line('l_user_id: '||l_user_id);
356
357 -- Set OUT parameters
358 x_winning_res_id := l_terrresource_tbl(0).resource_id;
359 x_winning_user_id := l_user_id;
360 l_return_status := OKC_API.G_RET_STS_SUCCESS;
361
362 END IF;
363 x_return_status := l_return_status;
364
365 EXCEPTION
366
367 WHEN OTHERS THEN
368
369 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
370 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
371 p_data => x_msg_data);
372 END GET_OKS_RESOURCE;
373
374 FUNCTION GET_PARTY_ID (p_contract_id IN NUMBER) RETURN NUMBER IS
375 CURSOR cur_get_party_id IS
376 select object1_id1
377 from okc_k_party_roles_b
378 where dnz_chr_id = p_contract_id
379 and cle_id is null
380 and RLE_CODE = 'CUSTOMER';
381 l_party_id NUMBER;
382
383 BEGIN
384
385 FOR c_cur_get_party_id in cur_get_party_id LOOP
386 l_party_id := c_cur_get_party_id.object1_id1;
387 END LOOP;
388 return(l_party_id);
389
390 END GET_PARTY_ID;
391
392
393 FUNCTION GET_SALESREP_ID (p_resource_id Number,p_org_id Number ) RETURN NUMBER IS
394 Cursor l_salesrep_csr IS
395 SELECT salesrep_id
396 From jtf_rs_salesreps
397 Where resource_id = p_resource_id and org_id = p_org_id;
398 l_salesrep_id NUMBER;
399 BEGIN
400 FOR c_l_salesrep_csr in l_salesrep_csr LOOP
401 l_salesrep_id := c_l_salesrep_csr.salesrep_id;
402 END LOOP;
403 return(l_salesrep_id);
404 END GET_SALESREP_ID;
405
406
407
408 FUNCTION GET_RESOURCE_NAME (p_resource_id Number) RETURN VARCHAR2 IS
409 CURSOR resource_name IS
410 select resource_name from jtf_rs_resource_extns_tl
411 where resource_id = p_resource_id
412 and language = userenv('LANG');
413
414 l_resource_name VARCHAR2(200);
415 BEGIN
416 FOR c_resource_name IN resource_name LOOP
417 l_resource_name := c_resource_name.resource_name;
418 END LOOP;
419 return(l_resource_name);
420 END GET_RESOURCE_NAME;
421
422
423 FUNCTION GET_PARTY_NAME (p_party_id Number) RETURN VARCHAR2 IS
424 CURSOR cur_party_name IS
425 select party_Name from hz_parties
426 where party_id = p_party_id;
427 l_party_Name VARCHAR2(200);
428 BEGIN
429 FOR c_cur_party_name IN cur_party_name LOOP
430 l_party_Name := c_cur_party_name.party_Name;
431 END LOOP;
432 return(l_party_Name);
433 END GET_PARTY_NAME;
434
435
436 PROCEDURE REASSIGNCONTACT (
437 p_api_version IN NUMBER,
438 p_init_msg_list IN VARCHAR2,
439 x_return_status OUT NOCOPY VARCHAR2,
440 x_msg_count OUT NOCOPY NUMBER,
441 x_msg_data OUT NOCOPY VARCHAR2,
442 p_contract_header_id IN NUMBER,
443 p_contract_number IN VARCHAR2,
444 p_contract_number_modifier IN VARCHAR2,
445 p_cro_code IN VARCHAR2,
446 p_salesrep_id IN NUMBER,
447 p_user_id IN NUMBER,
448 p_sales_group_id IN NUMBER
449 ) IS
450 l_api_version CONSTANT NUMBER := 1.0;
451 l_init_msg_list VARCHAR2(2000) := OKC_API.G_FALSE;
452 l_return_status VARCHAR2(1);
453 l_msg_count NUMBER;
454 l_msg_data VARCHAR2(2000);
455 l_msg_index_out NUMBER;
456
457 CURSOR GET_CONTACT IS
458 SELECT object1_id1, cro_code, id
459 FROM OKC_CONTACTS
460 WHERE CRO_CODE = p_cro_code
461 AND dnz_chr_id = p_contract_header_id;
462
463 CURSOR GET_CPL_ID IS
464 select id from okc_k_party_roles_b
465 where dnz_chr_id = p_contract_header_id
466 and cle_id is null
467 and RLE_CODE = 'VENDOR';
468
469 no_of_matches NUMBER := 0;
470 l_cpl_id NUMBER;
471
472 BEGIN
473
474 l_return_status := OKC_API.G_RET_STS_SUCCESS;
475
476 FOR C_GET_CPL_ID in GET_CPL_ID LOOP
477 l_cpl_id := C_GET_CPL_ID.id;
478 END LOOP;
479
480 FOR C_GET_CONTACT IN GET_CONTACT LOOP
481 IF (C_GET_CONTACT.object1_id1 = p_salesrep_id) THEN
482 no_of_matches := no_of_matches + 1;
483 ELSE
484
485 DELETE_CONTACT (
486 x_return_status => l_return_status,
487 p_contact_id => C_GET_CONTACT.id
488 );
489 END IF;
490 END LOOP;
491 log_messages('Inside REASSIGNCONTACT no_of_matches = ' || no_of_matches);
492
493 IF no_of_matches = 0 THEN
494 CREATE_CONTACT(
495 x_return_status => l_return_status,
496 p_cpl_id => l_cpl_id,
497 p_dnz_chr_id => p_contract_header_id,
498 p_cro_code => p_cro_code,
499 p_jtot_object1_code => 'OKX_SALEPERS',
500 p_object1_id1 => p_salesrep_id,
501 p_sales_group_id => p_sales_group_id
502 );
503
504 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
505 NOTIFY_SALESREP
506 (
507 p_user_id,
508 p_contract_header_id,
509 p_contract_number,
510 p_contract_number_modifier,
511 'You are assigned to contract '||p_contract_number || '-' || p_contract_number_modifier
512 );
513 ELSE
514
515 --Assemble Data for errorneous condition
516
517 NOTIFY_CONTRACT_ADMIN
518 (
519 p_contract_header_id,
520 p_contract_number,
521 p_contract_number_modifier,
522 GET_FND_MESSAGE
523 );
524 END IF;
525 END IF;
526
527 x_return_status := l_return_status;
528
529 END REASSIGNCONTACT;
530
531
532 PROCEDURE DELETE_CONTACT (
533 x_return_status OUT NOCOPY VARCHAR2,
534 p_contact_id IN NUMBER
535 ) IS
536 -- Contact Details
537 cursor contact_det is
538 select a.dnz_chr_id,a.object1_id1, a.object1_id2, a.jtot_object1_code
539 from okc_contacts a
540 where a.id = p_contact_id;
541
542 /*
543 -- Rule group
544 cursor rgp_cur(p_chr_id NUMBER) is
545 select id
546 from okc_rule_groups_b
547 where dnz_chr_id = p_chr_id
548 and cle_id is null;
549 */
550
551 -- Temporary variables
552 l_rgp_id NUMBER;
553 l_chr_id NUMBER;
554 l_obj_id1 Varchar2(2000);
555 l_obj_id2 Varchar2(2000);
556 l_jtot_cd Varchar2(2000);
557
558 l_api_version CONSTANT NUMBER := 1.0;
559 l_init_msg_list VARCHAR2(2000) := OKC_API.G_FALSE;
560 l_return_status VARCHAR2(1);
561 l_msg_count NUMBER;
562 l_msg_data VARCHAR2(2000);
563 l_msg_index_out NUMBER;
564
565 -- Local PL/SQL tables
566 l_rgpv_tbl_in okc_rule_pub.rgpv_tbl_type;
567 l_rgpv_tbl_out okc_rule_pub.rgpv_tbl_type;
568 l_rulv_tbl_in okc_rule_pub.rulv_tbl_type;
569 l_rulv_tbl_out okc_rule_pub.rulv_tbl_type;
570 l_ctcv_tbl_in okc_contract_party_pub.ctcv_tbl_type;
571 l_ctcv_tbl_out okc_contract_party_pub.ctcv_tbl_type;
572
573 BEGIN
574
575 -- Get the contact details (if any)
576 Open contact_det;
577 Fetch contact_det into l_chr_id,l_obj_id1,l_obj_id2,l_jtot_cd;
578 Close contact_det;
579
580 /*
581 --
582 -- Create a rule group if one doesn't exist
583 --
584 Open rgp_cur(l_chr_id);
585 Fetch rgp_cur into l_rgp_id;
586 Close rgp_cur;
587 If l_rgp_id is null Then
588 l_rgpv_tbl_in(1).chr_id := l_chr_id;
589 l_rgpv_tbl_in(1).sfwt_flag := 'N';
590 l_rgpv_tbl_in(1).rgd_code := 'SVC_K';
591 l_rgpv_tbl_in(1).dnz_chr_id := l_chr_id;
592 l_rgpv_tbl_in(1).rgp_type := 'KRG';
593 okc_rule_pub.create_rule_group
594 (p_api_version => l_api_version,
595 p_init_msg_list => l_init_msg_list,
596 x_return_status => l_return_status,
597 x_msg_count => l_msg_count,
598 x_msg_data => l_msg_data,
599 p_rgpv_tbl => l_rgpv_tbl_in,
600 x_rgpv_tbl => l_rgpv_tbl_out
601 );
602 if x_return_status = 'S' then
603 l_rgp_id := l_rgpv_tbl_out(1).id;
604 else
605 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
606 LOG_MESSAGES('okc_rule_pub.create_rule_group - EAB rule l_msg_data = ' || l_msg_data);
607 -- RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
608 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
609 LOG_MESSAGES('okc_rule_pub.create_rule_group - EAB rule l_msg_data = ' || l_msg_data);
610 -- RAISE OKC_API.G_EXCEPTION_ERROR;
611 END IF;
612
613 end if;
614 End If;
615
616 --
617 -- Now, create EAB Rule (Assuming that one doesn't exist already)
618 --
619 l_rulv_tbl_in(1).rgp_id := l_rgp_id;
620 l_rulv_tbl_in(1).rule_information_category := 'EAB';
621 l_rulv_tbl_in(1).rule_information2 := l_jtot_cd;
622 l_rulv_tbl_in(1).rule_information3 := l_obj_id1;
623 l_rulv_tbl_in(1).rule_information4 := l_obj_id2;
624 l_rulv_tbl_in(1).dnz_chr_id := l_chr_id;
625 okc_rule_pub.create_rule
626 (p_api_version => l_api_version,
627 p_init_msg_list => l_init_msg_list,
628 x_return_status => l_return_status,
629 x_msg_count => l_msg_count,
630 x_msg_data => l_msg_data,
631 p_rulv_tbl => l_rulv_tbl_in,
632 x_rulv_tbl => l_rulv_tbl_out
633 );
634
635 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
636 LOG_MESSAGES('okc_rule_pub.create_rule - EAB rule l_msg_data = ' || l_msg_data);
637 -- RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
638 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
639 LOG_MESSAGES('okc_rule_pub.create_rule - EAB rule l_msg_data = ' || l_msg_data);
640 -- RAISE OKC_API.G_EXCEPTION_ERROR;
641 END IF;
642
643 */
644 l_ctcv_tbl_in(1).id := p_contact_id;
645
646 okc_contract_party_pub.delete_contact (
647 p_api_version => l_api_version,
648 p_init_msg_list => l_init_msg_list,
649 x_return_status => l_return_status,
650 x_msg_count => l_msg_count,
651 x_msg_data => l_msg_data,
652 p_ctcv_tbl => l_ctcv_tbl_in
653 );
654 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
655 LOG_MESSAGES('okc_contract_party_pub.delete_contact l_msg_data = ' || l_msg_data);
656 -- RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
657 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
658 LOG_MESSAGES('okc_contract_party_pub.delete_contact l_msg_data = ' || l_msg_data);
659 -- RAISE OKC_API.G_EXCEPTION_ERROR;
660 END IF;
661 x_return_status := l_return_status;
662 END DELETE_CONTACT;
663
664 PROCEDURE CREATE_CONTACT (x_return_status OUT NOCOPY VARCHAR2,
665 p_cpl_id IN NUMBER,
666 p_dnz_chr_id IN NUMBER,
667 p_cro_code IN VARCHAR2,
668 p_jtot_object1_code IN VARCHAR2,
669 p_object1_id1 IN NUMBER,
670 p_sales_group_id IN NUMBER
671 ) IS
672 l_api_version CONSTANT NUMBER := 1.0;
673 l_init_msg_list VARCHAR2(2000) := OKC_API.G_FALSE;
674 l_return_status VARCHAR2(1);
675 l_msg_count NUMBER;
676 l_msg_data VARCHAR2(2000);
677 l_msg_index_out NUMBER;
678 l_ctcv_tbl_in okc_contract_party_pub.ctcv_tbl_type;
679 l_ctcv_tbl_out okc_contract_party_pub.ctcv_tbl_type;
680
681 BEGIN
682 null;
683 l_ctcv_tbl_in(1).cpl_id := p_cpl_id;
684 l_ctcv_tbl_in(1).cro_code := p_cro_code;
685 l_ctcv_tbl_in(1).dnz_chr_id := p_dnz_chr_id;
686 l_ctcv_tbl_in(1).OBJECT1_ID1 := p_object1_id1;
687 l_ctcv_tbl_in(1).object1_id2 := '#';
688 l_ctcv_tbl_in(1).JTOT_OBJECT1_CODE := p_jtot_object1_code;
689 l_ctcv_tbl_in(1).object_version_number := OKC_API.G_MISS_NUM;
690 l_ctcv_tbl_in(1).created_by := OKC_API.G_MISS_NUM;
691 l_ctcv_tbl_in(1).creation_date := SYSDATE;
692 l_ctcv_tbl_in(1).last_updated_by := OKC_API.G_MISS_NUM;
693 l_ctcv_tbl_in(1).last_update_date := SYSDATE;
694 l_ctcv_tbl_in(1).last_update_login := OKC_API.G_MISS_NUM;
695 l_ctcv_tbl_in(1).sales_group_id := p_sales_group_id;
696
697 okc_contract_party_pub.create_contact (
698 p_api_version => l_api_version,
699 p_init_msg_list => l_init_msg_list,
700 x_return_status => l_return_status,
701 x_msg_count => l_msg_count,
702 x_msg_data => l_msg_data,
703 p_ctcv_tbl => l_ctcv_tbl_in,
704 x_ctcv_tbl => l_ctcv_tbl_out
705 );
706
707 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
708 LOG_MESSAGES('okc_contract_party_pub.delete_contact l_msg_data = ' || l_msg_data);
709 -- RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
710 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
711 LOG_MESSAGES('okc_contract_party_pub.delete_contact l_msg_data = ' || l_msg_data);
712 -- RAISE OKC_API.G_EXCEPTION_ERROR;
713 END IF;
714
715 x_return_status := l_return_status;
716 END CREATE_CONTACT;
717
718 FUNCTION GET_FND_MESSAGE RETURN VARCHAR2 IS
719 i NUMBER := 0;
720 l_return_status VARCHAR2(1);
721 l_msg_count NUMBER;
722 l_msg_data VARCHAR2(2000);
723 l_msg_index_out NUMBER;
724 l_mesg VARCHAR2(2000) := NULL;
725 BEGIN
726 FOR i in 1..fnd_msg_pub.count_msg Loop
727 fnd_msg_pub.get
728 (
729 p_msg_index => i,
730 p_encoded => 'F',
731 p_data => l_msg_data,
732 p_msg_index_out => l_msg_index_out
733 );
734 l_mesg := l_mesg || ':' || i || ':' || l_msg_data;
735 END LOOP;
736 FND_MESSAGE.CLEAR;
737 return(l_mesg);
738 END GET_FND_MESSAGE;
739
740 PROCEDURE NOTIFY_SETUP_ADMIN IS
741 l_setup_admin VARCHAR2(200) := FND_PROFILE.VALUE('OKS_SETUP_ADMIN_ID');
742 BEGIN
743 null;
744 IF l_setup_admin IS NOT NULL THEN
745 OKS_EXTWAR_UTIL_PUB.NOTIFY('NSA',l_setup_admin,NULL,NULL,NULL,'Profile Vendor Contact Role Not Set Up');
746 LOG_MESSAGES('Profile Vendor Contact Role Not Set Up Notification Sent to ' || l_setup_admin);
747 ELSE
748 LOG_MESSAGES('OKS: Notify Setup Admin is NULL');
749 END IF;
750 END NOTIFY_SETUP_ADMIN;
751
752 PROCEDURE NOTIFY_TERRITORY_ADMIN (p_chr_id IN Number,p_contract_number IN VARCHAR2, p_contract_number_modifier IN VARCHAR2, p_mesg IN VARCHAR2) IS
753 CURSOR l_fnd_csr(p_user_id NUMBER) IS
754 SELECT user_name
755 FROM fnd_user
756 WHERE user_id = p_user_id ;
757
758
759 l_msg_count NUMBER;
760 l_msg_data VARCHAR2(2000);
761 l_return_status VARCHAR2(3);
762 l_terr_admin_id NUMBER;
763 l_contract_admin_id NUMBER;
764 l_contract_approver_id NUMBER;
765 l_subj VARCHAR2(2000);
766 l_msg VARCHAR2(2000);
767 l_user_name VARCHAR2(100);
768 l_con_num_prompt VARCHAR2(100);
769
770
771 BEGIN
772
773 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
774 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT ||'.SUBMIT_CONTACT_CREATION',
775 'Inside NOTIFY_TERRITORY_ADMIN');
776 END IF;
777
778 l_terr_admin_id := FND_PROFILE.VALUE('OKS_TERR_ADMIN_ID');
779 l_subj := FND_MESSAGE.get_string('OKS','OKS_TERR_SETUP_ERR_SUB');
780 IF (l_terr_admin_id IS NULL) THEN
781 l_contract_admin_id := FND_PROFILE.VALUE('OKS_CONTRACT_ADMIN_ID');
782 END IF;
783 IF l_terr_admin_id IS NOT NULL THEN
784 OPEN l_fnd_csr(l_terr_admin_id);
785 FETCH l_fnd_csr INTO l_user_name;
786 CLOSE l_fnd_csr;
787 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
788 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT ||'.SUBMIT_CONTACT_CREATION',
789 'Territory Admin is not null - ' || l_user_name);
790 END IF;
791 ELSIF l_contract_admin_id IS NOT NULL THEN
792 OPEN l_fnd_csr(l_contract_admin_id);
793 FETCH l_fnd_csr INTO l_user_name;
794 CLOSE l_fnd_csr;
795 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
796 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT ||'.SUBMIT_CONTACT_CREATION',
797 'Contract Admin is not null - ' || l_user_name);
798 END IF;
799 ELSE
800 l_user_name := FND_PROFILE.VALUE('OKC_K_APPROVER');
801 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
802 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT ||'.SUBMIT_CONTACT_CREATION',
803 'Contract Approver is not null - ' || l_user_name);
804 END IF;
805
806 END IF;
807
808 l_con_num_prompt := FND_MESSAGE.get_string('OKS','OKS_CONTRACT_NUMBER');
809 l_subj := l_subj || ' ' || l_con_num_prompt || ' - ' || p_contract_number ||' '||p_contract_number_modifier;
810
811 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
812 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT ||'.SUBMIT_CONTACT_CREATION',
813 'p_recipient is ' || l_user_name ||
814 'p_contract_id is ' || p_chr_id );
815 END IF;
816 OKC_ASYNC_PUB.msg_call(p_api_version => 1,
817 x_return_status => l_return_status,
818 x_msg_count => l_msg_count,
819 x_msg_data => l_msg_data,
820 p_recipient => l_user_name,
821 p_msg_body => p_mesg,
822 p_msg_subj => l_subj,
823 p_contract_id => p_chr_id );
824 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
825 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT ||'.SUBMIT_CONTACT_CREATION',
826 'Exiting NOTIFY_TERR_ADMIN' || l_return_status);
827 END IF;
828 END NOTIFY_TERRITORY_ADMIN;
829
830 PROCEDURE NOTIFY_CONTRACT_ADMIN (p_chr_id IN Number, p_contract_number IN VARCHAR2, p_contract_number_modifier IN VARCHAR2, p_mesg IN VARCHAR2) IS
831 l_contract_admin VARCHAR2(200) := FND_PROFILE.VALUE('OKS_CONTRACT_ADMIN_ID');
832 BEGIN
833
834 IF l_contract_admin IS NOT NULL THEN
835 OKS_EXTWAR_UTIL_PUB.NOTIFY('NCA',l_contract_admin,p_chr_id,p_contract_number,p_contract_number_modifier,p_mesg);
836 --LOG_MESSAGES(p_mesg || ':' || p_contract_number || ':' || p_contract_number_modifier || ':' || l_contract_admin);
837 END IF;
838 END NOTIFY_CONTRACT_ADMIN;
839
840 PROCEDURE NOTIFY_SALESREP (p_user_id IN NUMBER, p_chr_id IN Number, p_contract_number IN VARCHAR2, p_contract_number_modifier IN VARCHAR2, p_mesg IN VARCHAR2) IS
841 --l_salesperson_id VARCHAR2(200) := FND_PROFILE.VALUE('OKS_SALESPERSON_ID');
842 BEGIN
843
844 OKS_EXTWAR_UTIL_PUB.NOTIFY('NSR',p_user_id,p_chr_id,p_contract_number,p_contract_number_modifier,p_mesg);
845 -- LOG_MESSAGES(p_mesg || ':' || p_contract_number || ':' || p_contract_number_modifier || ':' || p_user_id);
846 END NOTIFY_SALESREP;
847
848 PROCEDURE SET_MSG (x_return_Status OUT Nocopy Varchar2, p_msg Varchar2)
849 Is
850 Begin
851 x_return_status := OKC_API.G_RET_STS_SUCCESS;
852
853 OKC_API.set_message(p_app_name => g_app_name,
854 p_msg_name => 'CONTRACTS RE-ASSIGNED'
855 );
856
857 OKC_API.set_message(p_app_name => g_app_name,
858 p_msg_name => p_msg
859 );
860
861
862 End;
863
864
865 PROCEDURE NOTIFY
866 (
867 p_type IN VARCHAR2,
868 p_notify_id IN Number,
869 p_chr_id IN Number,
870 p_contract_number IN VARCHAR2,
871 p_contract_number_modifier IN VARCHAR2,
872 p_mesg IN VARCHAR2) Is
873
874 l_proc Varchar2(4000);
875 l_return_status Varchar2(2000);
876 l_msg_data Varchar2(4000);
877 l_msg_count Number;
878 l_user_name Varchar2(2000);
879 l_subj Varchar2(4000) := Null;
880
881 cursor l_fnd_csr is select user_name from fnd_user where user_id = p_notify_id;
882
883 Begin
884
885 if p_type = 'NSA' Then
886 l_subj := 'Setup Error';
887 elsif p_type = 'NTA' Then
888 l_subj := 'Territory Setup Error';
889 elsif p_type = 'NCA' Then
890 l_subj := 'Vendor Contact Creation Error';
891 elsif p_type = 'NSR' Then
892 l_subj := FND_MESSAGE.get_string('OKS','OKS_VENDOR_REASSIGNED');
893 end if;
894
895 open l_fnd_csr;
896 fetch l_fnd_csr into l_user_name;
897 close l_fnd_csr;
898 IF l_user_name IS NOT NULL THEN
899 OKC_ASYNC_PUB.msg_call(
900 p_api_version => 1,
901 x_return_status => l_return_status,
902 x_msg_count => l_msg_count,
903 x_msg_data => l_msg_data,
904 p_recipient => l_user_name,
905 p_msg_body => p_mesg,
906 p_msg_subj => l_subj,
907 p_contract_id => p_chr_id
908 );
909 END IF;
910
911 End NOTIFY;
912
913
914
915 PROCEDURE LOG_MESSAGES(p_mesg IN VARCHAR2) IS
916 BEGIN
917 IF l_conc_program = 'N' THEN
918 -- errorout_ad(p_mesg);
919 null;
920 ELSE
921 fnd_file.put_line(FND_FILE.LOG, p_mesg);
922 END IF;
923 END LOG_MESSAGES;
924
925
926 FUNCTION def_sts_code(p_ste_code VARCHAR2) RETURN VARCHAR2 IS
927 CURSOR get_def_sts_code_csr IS
928 SELECT code
929 FROM okc_statuses_b
930 WHERE ste_code = p_ste_code
931 AND default_yn = 'Y';
932 get_def_sts_code_rec get_def_sts_code_csr%ROWTYPE;
933 BEGIN
934
935 OPEN get_def_sts_code_csr;
936 FETCH get_def_sts_code_csr INTO get_def_sts_code_rec;
937 CLOSE get_def_sts_code_csr;
938 RETURN (get_def_sts_code_rec.code);
939
940 END def_sts_code;
941
942 FUNCTION get_ste_code(p_sts_code VARCHAR2) RETURN VARCHAR2 IS
943 CURSOR get_ste_code_csr IS
944 SELECT ste_code
945 FROM okc_statuses_b
946 WHERE code = p_sts_code;
947 get_ste_code_rec get_ste_code_csr%ROWTYPE;
948 BEGIN
949
950 OPEN get_ste_code_csr;
951 FETCH get_ste_code_csr INTO get_ste_code_rec;
952 CLOSE get_ste_code_csr;
953 RETURN (get_ste_code_rec.ste_code);
954
955 END get_ste_code;
956
957
958 Procedure get_duration( p_line_start_date IN DATE,
959 p_line_end_date IN DATE,
960 x_line_duration OUT NOCOPY NUMBER,
961 x_line_timeunit OUT NOCOPY VARCHAR2,
962 x_return_status OUT NOCOPY VARCHAR2,
963 p_init_msg_list IN VARCHAR2)
964 IS
965
966
967 Begin
968 OKC_API.init_msg_list(p_init_msg_list);
969 OKC_TIME_UTIL_PUB.get_duration(p_start_date => p_line_start_date,
970 p_end_date => p_line_end_date,
971 x_duration => x_line_duration,
972 x_timeunit => x_line_timeunit,
973 x_return_status => x_return_status);
974
975 END get_duration;
976
977 FUNCTION GET_CHR_SALESREP_ID (p_resource_id Number,p_chr_id Number ) RETURN NUMBER IS
978 Cursor l_salesrep_csr IS
979 SELECT a.salesrep_id
980 From jtf_rs_salesreps a,
981 okc_k_headers_b b
982 Where b.id = p_chr_id
983 and a.resource_id = p_resource_id
984 and a.org_id = b.authoring_org_id;
985 l_salesrep_id NUMBER;
986 BEGIN
987 FOR c_l_salesrep_csr in l_salesrep_csr LOOP
988 l_salesrep_id := c_l_salesrep_csr.salesrep_id;
989 END LOOP;
990 return(l_salesrep_id);
991 END GET_chr_SALESREP_ID;
992
993
994 /******* New concurrent Program ******************/
995 PROCEDURE SUBMIT_CONTACT_CREATION(ERRBUF OUT NOCOPY VARCHAR2,
996 RETCODE OUT NOCOPY NUMBER,
997 p_contract_hdr_id IN NUMBER,
998 p_status_code IN VARCHAR2,
999 p_org_id IN NUMBER,
1000 p_salesrep_id IN NUMBER ) IS
1001
1002 l_api_version CONSTANT NUMBER := 1.0;
1003 l_init_msg_list VARCHAR2(2000) := OKC_API.G_FALSE;
1004 l_return_status VARCHAR2(1);
1005 l_msg_count NUMBER;
1006 l_msg_data VARCHAR2(2000);
1007 l_gen_bulk_Rec JTF_TERR_ASSIGN_PUB.bulk_trans_rec_type;
1008 l_gen_return_Rec JTF_TERR_ASSIGN_PUB.bulk_winners_rec_type;
1009 l_use_type VARCHAR2(30);
1010 l_contract_id NUMBER;
1011 l_contract_number VARCHAR2(120);
1012 l_cro_code VARCHAR2(200);
1013 l_org_id NUMBER;
1014 l_resource_id NUMBER;
1015 l_user_id NUMBER;
1016 l_salesrep_id NUMBER;
1017 l_party_id NUMBER;
1018
1019 idx1 NUMBER:=1;
1020 idx2 NUMBER:=1;
1021 idx3 NUMBER;
1022 idx4 NUMBER;
1023 idx5 NUMBER;
1024 idx6 NUMBER;
1025
1026
1027 l_count NUMBER:=0;
1028 l_counter NUMBER;
1029 l_cpl_id NUMBER;
1030 l_temp_salesrep NUMBER;
1031 l_temp_org_id NUMBER;
1032 l_request_id NUMBER:=0;
1033 l_terr_admin_msg VARCHAR2(1000);
1034 l_admin_msg VARCHAR2(1000);
1035 l_salesrep_msg VARCHAR2(1000);
1036 l_cvmv_rec OKC_CVM_PVT.cvmv_rec_type ;
1037 l_cvmv_upd_rec OKC_CVM_PVT.cvmv_rec_type ;
1038 l_cvmv_out_rec OKC_CVM_PVT.cvmv_rec_type ;
1039 l_cvmv_upd_out_rec OKC_CVM_PVT.cvmv_rec_type ;
1040 l_chrv_rec OKC_CONTRACT_PUB.chrv_rec_type;
1041 l_chrv_upd_rec OKC_CONTRACT_PUB.chrv_rec_type;
1042 l_chrv_out_rec OKC_CONTRACT_PUB.chrv_rec_type;
1043 l_chrv_upd_out_rec OKC_CONTRACT_PUB.chrv_rec_type;
1044 l_e_contract_number VARCHAR2(120);
1045 l_e_contract_number_mod VARCHAR2(120);
1046 l_f_chr_id NUMBER;
1047 l_terr_res VARCHAR2(3);
1048
1049 Type winning_rec_type is record (chr_id number,
1050 resource_id number,
1051 user_id NUMBER);
1052 TYPE winning_tbl IS TABLE OF winning_rec_type INDEX BY BINARY_INTEGER;
1053 Type l_num IS TABLE OF number index by binary_integer;
1054 Type l_varchar IS TABLE OF varchar2(2000) index by binary_integer;
1055 l_winning_tbl winning_tbl;
1056 l2_winning_tbl winning_tbl;
1057 l_resource_id_tbl l_num;
1058 l_user_id_tbl l_num;
1059 lb_id l_num;
1060 lb_contract_number l_varchar ;
1061 lb_contract_number_modifier l_varchar ;
1062 lb_contact_start_date l_varchar;
1063 lb_authoring_org_id l_num;
1064 lb_inv_organization_id l_num;
1065 lb_party_id l_varchar;
1066 lb_cpl_id l_num;
1067 lb_party_name l_varchar ;
1068 lb_country_code l_varchar ;
1069 lb_state_code l_varchar ;
1070 lb_contact_id l_num;
1071 lb_salesrep_id l_varchar;
1072 lb_status l_varchar;
1073 lj_id l_num;
1074 lr_cpl_id l_num;
1075 lr_del_cpl_id l_num;
1076 lr_del_cpl_org_id l_num;
1077 lr_upd_cpl_org_id l_num;
1078 lj_chr_id l_num;
1079 lj_cpl_id l_num;
1080 lj_salesrep_id l_varchar;
1081 lj_org_id l_num;
1082 lj_resource_id l_num;
1083 lj_user_id l_num;
1084
1085 l_current_salesrep_id NUMBER := -9999;
1086 l_current_salesgrp_id NUMBER := -1;
1087
1088
1089 -- Sepearte static SQLs are defined due to performance reason.
1090 -- Contract Number not null , Status not null, Salesrep not null
1091 CURSOR GET_ALL_CONTRACTS_1 (p_cro_code in VARCHAR2, p_org_id NUMBER)is
1092 SELECT /*+ PARALLEL(hdr) */ hdr.id,hdr.contract_number
1093 ,hdr.contract_number_modifier
1094 ,hdr.authoring_org_id,hdr.inv_organization_id, party1.object1_id1
1095 ,Party2.id,hz.party_name, hzl.country, hzl.state
1096 ,cont.id,cont.object1_id1,cont.start_date,hdr.sts_code
1097 FROM OKC_K_HEADERS_B hdr,
1098 okc_k_party_roles_b party1,
1099 okc_k_party_roles_b party2,
1100 okc_contacts cont,
1101 hz_parties hz,
1102 hz_party_sites hzs,
1103 hz_locations hzl
1104 WHERE hdr.id = p_contract_hdr_id
1105 AND hdr.authoring_org_id=nvl(p_org_id,hdr.authoring_org_id)
1106 AND hdr.sts_code = p_status_code
1107 AND hdr.scs_code IN ('SERVICE','WARRANTY','SUBSCRIPTION')
1108 AND hdr.template_yn = 'N'
1109 AND party1.dnz_chr_id = hdr.id
1110 AND party1.cle_id is null
1111 AND party1.rle_code IN ('CUSTOMER','SUBSCRIBER')
1112 AND hz.party_id = party1.object1_id1
1113 AND hzs.party_id = hz.party_id
1114 AND hzs.identifying_address_flag ='Y'
1115 AND hzl.location_id = hzs.location_id
1116 AND party2.dnz_chr_id = party1.dnz_chr_id
1117 AND party2.chr_id = party1.dnz_chr_id
1118 AND party2.cle_id is null
1119 AND party2.rle_code IN ('VENDOR','MERCHANT')
1120 AND cont.cpl_id (+) = party2.id
1121 AND cont.object1_id1(+) = p_salesrep_id
1122 AND cont.cro_code(+) = p_cro_code
1123 AND (TRUNC(NVL(cont.end_date(+),SYSDATE)) >= TRUNC(SYSDATE));
1124
1125 -- Contract Number is not null , Status is not null
1126 CURSOR GET_ALL_CONTRACTS_2 (p_cro_code in VARCHAR2, p_org_id NUMBER)is
1127 SELECT /*+ PARALLEL(hdr) */ hdr.id,hdr.contract_number
1128 ,hdr.contract_number_modifier
1129 ,hdr.authoring_org_id,hdr.inv_organization_id, party1.object1_id1
1130 ,Party2.id,hz.party_name, hzl.country, hzl.state
1131 ,cont.id,cont.object1_id1,cont.start_date,hdr.sts_code
1132 FROM OKC_K_HEADERS_B hdr,
1133 okc_k_party_roles_b party1,
1134 okc_k_party_roles_b party2,
1135 okc_contacts cont,
1136 hz_parties hz,
1137 hz_party_sites hzs,
1138 hz_locations hzl
1139 WHERE hdr.id = p_contract_hdr_id
1140 AND hdr.authoring_org_id=nvl(p_org_id,hdr.authoring_org_id)
1141 AND hdr.sts_code = p_status_code
1142 AND hdr.scs_code IN ('SERVICE','WARRANTY','SUBSCRIPTION')
1143 AND hdr.template_yn = 'N'
1144 AND party1.dnz_chr_id = hdr.id
1145 AND party1.cle_id is null
1146 AND party1.rle_code IN ('CUSTOMER','SUBSCRIBER')
1147 AND hz.party_id = party1.object1_id1
1148 AND hzs.party_id = hz.party_id
1149 AND hzs.identifying_address_flag ='Y'
1150 AND hzl.location_id = hzs.location_id
1151 AND party2.dnz_chr_id = party1.dnz_chr_id
1152 AND party2.chr_id = party1.dnz_chr_id
1153 AND party2.cle_id is null
1154 AND party2.rle_code IN ('VENDOR','MERCHANT')
1155 AND cont.cpl_id (+) = party2.id
1156 AND cont.cro_code(+) = p_cro_code
1157 AND (TRUNC(NVL(cont.end_date(+),SYSDATE)) >= TRUNC(SYSDATE));
1158
1159 -- Contract number is not null, salesrep is not null
1160 CURSOR GET_ALL_CONTRACTS_3 (p_cro_code in VARCHAR2, p_org_id NUMBER)is
1161 SELECT /*+ PARALLEL(hdr) */ hdr.id,hdr.contract_number
1162 ,hdr.contract_number_modifier
1163 ,hdr.authoring_org_id,hdr.inv_organization_id, party1.object1_id1
1164 ,Party2.id,hz.party_name, hzl.country, hzl.state
1165 ,cont.id,cont.object1_id1,cont.start_date,hdr.sts_code
1166 FROM OKC_K_HEADERS_B hdr,
1167 okc_statuses_b stat,
1168 okc_k_party_roles_b party1,
1169 okc_k_party_roles_b party2,
1170 okc_contacts cont,
1171 hz_parties hz,
1172 hz_party_sites hzs,
1173 hz_locations hzl
1174 WHERE hdr.id = p_contract_hdr_id
1175 AND hdr.authoring_org_id=nvl(p_org_id,hdr.authoring_org_id)
1176 AND stat.STE_CODE IN ('ENTERED','ACTIVE','SIGNED','HOLD')
1177 AND hdr.sts_code = stat.CODE
1178 AND hdr.scs_code IN ('SERVICE','WARRANTY','SUBSCRIPTION')
1179 AND hdr.template_yn = 'N'
1180 AND party1.dnz_chr_id = hdr.id
1181 AND party1.cle_id is null
1182 AND party1.rle_code IN ('CUSTOMER','SUBSCRIBER')
1183 AND hz.party_id = party1.object1_id1
1184 AND hzs.party_id = hz.party_id
1185 AND hzs.identifying_address_flag ='Y'
1186 AND hzl.location_id = hzs.location_id
1187 AND party2.dnz_chr_id = party1.dnz_chr_id
1188 AND party2.chr_id = party1.dnz_chr_id
1189 AND party2.cle_id is null
1190 AND party2.rle_code IN ('VENDOR','MERCHANT')
1191 AND cont.cpl_id (+) = party2.id
1192 AND cont.object1_id1(+) = p_salesrep_id
1193 AND cont.cro_code(+) = p_cro_code
1194 AND (TRUNC(NVL(cont.end_date(+),SYSDATE)) >= TRUNC(SYSDATE));
1195
1196 -- Status is not null and Salesrep is not null
1197 CURSOR GET_ALL_CONTRACTS_4 (p_cro_code in VARCHAR2, p_org_id NUMBER)is
1198 SELECT /*+ PARALLEL(hdr) */ hdr.id,hdr.contract_number
1199 ,hdr.contract_number_modifier
1200 ,hdr.authoring_org_id,hdr.inv_organization_id, party1.object1_id1
1201 ,Party2.id,hz.party_name, hzl.country, hzl.state
1202 ,cont.id,cont.object1_id1,cont.start_date,hdr.sts_code
1203 FROM OKC_K_HEADERS_B hdr,
1204 okc_k_party_roles_b party1,
1205 okc_k_party_roles_b party2,
1206 okc_contacts cont,
1207 hz_parties hz,
1208 hz_party_sites hzs,
1209 hz_locations hzl
1210 WHERE hdr.authoring_org_id=nvl(p_org_id,hdr.authoring_org_id)
1211 AND hdr.sts_code = p_status_code
1212 AND hdr.scs_code IN ('SERVICE','WARRANTY','SUBSCRIPTION')
1213 AND hdr.template_yn = 'N'
1214 AND party1.dnz_chr_id = hdr.id
1215 AND party1.cle_id is null
1216 AND party1.rle_code IN ('CUSTOMER','SUBSCRIBER')
1217 AND hz.party_id = party1.object1_id1
1218 AND hzs.party_id = hz.party_id
1219 AND hzs.identifying_address_flag ='Y'
1220 AND hzl.location_id = hzs.location_id
1221 AND party2.dnz_chr_id = party1.dnz_chr_id
1222 AND party2.chr_id = party1.dnz_chr_id
1223 AND party2.cle_id is null
1224 AND party2.rle_code IN ('VENDOR','MERCHANT')
1225 AND cont.cpl_id (+) = party2.id
1226 AND cont.object1_id1(+) = p_salesrep_id
1227 AND cont.cro_code(+) = p_cro_code
1228 AND (TRUNC(NVL(cont.end_date(+),SYSDATE)) >= TRUNC(SYSDATE));
1229
1230 -- Contract Number is not null
1231 CURSOR GET_ALL_CONTRACTS_5 (p_cro_code in VARCHAR2, p_org_id NUMBER)is
1232 SELECT /*+ PARALLEL(hdr) */ hdr.id,hdr.contract_number
1233 ,hdr.contract_number_modifier
1234 ,hdr.authoring_org_id,hdr.inv_organization_id, party1.object1_id1
1235 ,Party2.id,hz.party_name, hzl.country, hzl.state
1236 ,cont.id,cont.object1_id1,cont.start_date,hdr.sts_code
1237 FROM OKC_K_HEADERS_B hdr,
1238 okc_statuses_b stat,
1239 okc_k_party_roles_b party1,
1240 okc_k_party_roles_b party2,
1241 okc_contacts cont,
1242 hz_parties hz,
1243 hz_party_sites hzs,
1244 hz_locations hzl
1245 WHERE hdr.id = p_contract_hdr_id
1246 AND hdr.authoring_org_id=nvl(p_org_id,hdr.authoring_org_id)
1247 AND stat.STE_CODE IN ('ENTERED','ACTIVE','SIGNED','HOLD')
1248 AND hdr.sts_code = stat.CODE
1249 AND hdr.scs_code IN ('SERVICE','WARRANTY','SUBSCRIPTION')
1250 AND hdr.template_yn = 'N'
1251 AND party1.dnz_chr_id = hdr.id
1252 AND party1.cle_id is null
1253 AND party1.rle_code IN ('CUSTOMER','SUBSCRIBER')
1254 AND hz.party_id = party1.object1_id1
1255 AND hzs.party_id = hz.party_id
1256 AND hzs.identifying_address_flag ='Y'
1257 AND hzl.location_id = hzs.location_id
1258 AND party2.dnz_chr_id = party1.dnz_chr_id
1259 AND party2.chr_id = party1.dnz_chr_id
1260 AND party2.cle_id is null
1261 AND party2.rle_code IN ('VENDOR','MERCHANT')
1262 AND cont.cpl_id (+) = party2.id
1263 AND cont.cro_code(+) = p_cro_code
1264 AND (TRUNC(NVL(cont.end_date(+),SYSDATE)) >= TRUNC(SYSDATE)) ;
1265
1266 -- Status is not null
1267 CURSOR GET_ALL_CONTRACTS_6 (p_cro_code in VARCHAR2, p_org_id NUMBER)is
1268 SELECT /*+ PARALLEL(hdr) */ hdr.id,hdr.contract_number
1269 ,hdr.contract_number_modifier
1270 ,hdr.authoring_org_id,hdr.inv_organization_id, party1.object1_id1
1271 ,Party2.id,hz.party_name, hzl.country, hzl.state
1272 ,cont.id,cont.object1_id1,cont.start_date,hdr.sts_code
1273 FROM OKC_K_HEADERS_B hdr,
1274 okc_k_party_roles_b party1,
1275 okc_k_party_roles_b party2,
1276 okc_contacts cont,
1277 hz_parties hz,
1278 hz_party_sites hzs,
1279 hz_locations hzl
1280 WHERE hdr.authoring_org_id=nvl(p_org_id,hdr.authoring_org_id)
1281 AND hdr.sts_code = p_status_code
1282 AND hdr.scs_code IN ('SERVICE','WARRANTY','SUBSCRIPTION')
1283 AND hdr.template_yn = 'N'
1284 AND party1.dnz_chr_id = hdr.id
1285 AND party1.cle_id is null
1286 AND party1.rle_code IN ('CUSTOMER','SUBSCRIBER')
1287 AND hz.party_id = party1.object1_id1
1288 AND hzs.party_id = hz.party_id
1289 AND hzs.identifying_address_flag ='Y'
1290 AND hzl.location_id = hzs.location_id
1291 AND party2.dnz_chr_id = party1.dnz_chr_id
1292 AND party2.chr_id = party1.dnz_chr_id
1293 AND party2.cle_id is null
1294 AND party2.rle_code IN ('VENDOR','MERCHANT')
1295 AND cont.cpl_id (+) = party2.id
1296 AND cont.cro_code(+) = p_cro_code
1297 AND (TRUNC(NVL(cont.end_date(+),SYSDATE)) >= TRUNC(SYSDATE));
1298
1299 -- Sales rep is not null
1300 CURSOR GET_ALL_CONTRACTS_7 (p_cro_code in VARCHAR2, p_org_id NUMBER)is
1301 SELECT /*+ PARALLEL(hdr) */ hdr.id,hdr.contract_number
1302 ,hdr.contract_number_modifier
1303 ,hdr.authoring_org_id,hdr.inv_organization_id, party1.object1_id1
1304 ,Party2.id,hz.party_name, hzl.country, hzl.state
1305 ,cont.id,cont.object1_id1,cont.start_date,hdr.sts_code
1306 FROM OKC_K_HEADERS_B hdr,
1307 okc_statuses_b stat,
1308 okc_k_party_roles_b party1,
1309 okc_k_party_roles_b party2,
1310 okc_contacts cont,
1311 hz_parties hz,
1312 hz_party_sites hzs,
1313 hz_locations hzl
1314 WHERE hdr.authoring_org_id=nvl(p_org_id,hdr.authoring_org_id)
1315 AND stat.STE_CODE IN ('ENTERED','ACTIVE','SIGNED','HOLD')
1316 AND hdr.sts_code = stat.CODE
1317 AND hdr.scs_code IN ('SERVICE','WARRANTY','SUBSCRIPTION')
1318 AND hdr.template_yn = 'N'
1319 AND party1.dnz_chr_id = hdr.id
1320 AND party1.cle_id is null
1321 AND party1.rle_code IN ('CUSTOMER','SUBSCRIBER')
1322 AND hz.party_id = party1.object1_id1
1323 AND hzs.party_id = hz.party_id
1324 AND hzs.identifying_address_flag ='Y'
1325 AND hzl.location_id = hzs.location_id
1326 AND party2.dnz_chr_id = party1.dnz_chr_id
1327 AND party2.chr_id = party1.dnz_chr_id
1328 AND party2.cle_id is null
1329 AND party2.rle_code IN ('VENDOR','MERCHANT')
1330 AND cont.cpl_id = party2.id
1331 AND cont.object1_id1 = p_salesrep_id
1332 AND cont.end_date is null
1333 AND cont.cro_code = p_cro_code
1334 AND (TRUNC(NVL(cont.end_date,SYSDATE)) >= TRUNC(SYSDATE));
1335
1336 -- All params are null
1337 CURSOR GET_ALL_CONTRACTS_8 (p_cro_code in VARCHAR2, p_org_id NUMBER)is
1338 SELECT /*+ PARALLEL(hdr) */ hdr.id,hdr.contract_number
1339 ,hdr.contract_number_modifier
1340 ,hdr.authoring_org_id,hdr.inv_organization_id, party1.object1_id1
1341 ,Party2.id,hz.party_name, hzl.country, hzl.state
1342 ,cont.id,cont.object1_id1,cont.start_date,hdr.sts_code
1343 FROM OKC_K_HEADERS_B hdr,
1344 okc_statuses_b stat,
1345 okc_k_party_roles_b party1,
1346 okc_k_party_roles_b party2,
1347 okc_contacts cont,
1348 hz_parties hz,
1349 hz_party_sites hzs,
1350 hz_locations hzl
1351 WHERE hdr.authoring_org_id=nvl(p_org_id,hdr.authoring_org_id)
1352 AND stat.STE_CODE IN ('ENTERED','ACTIVE','SIGNED','HOLD')
1353 AND hdr.sts_code = stat.CODE
1354 AND hdr.scs_code IN ('SERVICE','WARRANTY','SUBSCRIPTION')
1355 AND hdr.template_yn = 'N'
1356 AND party1.dnz_chr_id = hdr.id
1357 AND party1.cle_id is null
1358 AND party1.rle_code IN ('CUSTOMER','SUBSCRIBER')
1359 AND hz.party_id = party1.object1_id1
1360 AND hzs.party_id = hz.party_id
1361 AND hzs.identifying_address_flag ='Y'
1362 AND hzl.location_id = hzs.location_id
1363 AND party2.dnz_chr_id = party1.dnz_chr_id
1364 AND party2.chr_id = party1.dnz_chr_id
1365 AND party2.cle_id is null
1366 AND party2.rle_code IN ('VENDOR','MERCHANT')
1367 AND cont.cpl_id (+) = party2.id
1368 AND cont.cro_code(+)= p_cro_code
1369 AND (TRUNC(NVL(cont.end_date(+),SYSDATE)) >= TRUNC(SYSDATE));
1370
1371 ---------------------------------------------------------------------
1372 -- Sepearte static SQLs are defined due to performance reason.
1373 -- Contract Number not null , Status not null, Salesrep not null
1374
1375 CURSOR GET_ALL_CONTRACTS_9 (p_cro_code in VARCHAR2, p_org_id NUMBER)is
1376 SELECT /*+ PARALLEL(HDR) */
1377 hdr.ID,
1378 hdr.contract_number,
1379 hdr.contract_number_modifier,
1380 hdr.authoring_org_id,
1381 hdr.inv_organization_id,
1382 party1.object1_id1,
1383 party2.ID,
1384 hz.party_name,
1385 c.country,
1386 c.region_2 state,
1387 cont.ID,
1388 cont.object1_id1,
1389 cont.start_date,
1390 hdr.sts_code
1391 FROM okc_k_headers_b hdr,
1392 okc_k_party_roles_b party1,
1393 okc_k_party_roles_b party2,
1394 okc_contacts cont,
1395 hz_parties hz,
1396 hr_all_organization_units b,
1397 hr_locations_all c
1398 WHERE hdr.id = p_contract_hdr_id
1399 AND hdr.authoring_org_id = NVL( p_org_id, hdr.authoring_org_id )
1400 AND hdr.sts_code = p_status_code
1401 AND hdr.scs_code IN( 'SERVICE', 'WARRANTY', 'SUBSCRIPTION' )
1402 AND hdr.template_yn = 'N'
1403 AND party1.dnz_chr_id = hdr.ID
1404 AND party1.cle_id IS NULL
1405 AND party1.rle_code IN ('CUSTOMER','SUBSCRIBER')
1406 AND party1.object1_id1 = hz.party_id
1407 AND party2.dnz_chr_id = party1.dnz_chr_id
1408 AND party2.chr_id = party1.dnz_chr_id
1409 AND party2.cle_id IS NULL
1410 AND party2.rle_code IN ('VENDOR','MERCHANT')
1411 AND cont.cpl_id = party2.id
1412 AND cont.object1_id1 = p_salesrep_id
1413 AND cont.cro_code = p_cro_code
1414 AND (TRUNC(NVL(cont.end_date,SYSDATE)) >= TRUNC(SYSDATE))
1415 AND party2.object1_id1 = b.organization_id
1416 AND b.location_id = c.location_id;
1417
1418
1419 -- Contract Number is not null , Status is not null
1420 CURSOR GET_ALL_CONTRACTS_10 (p_cro_code in VARCHAR2, p_org_id NUMBER)is
1421 SELECT /*+ PARALLEL(HDR) */
1422 hdr.ID,
1423 hdr.contract_number,
1424 hdr.contract_number_modifier,
1425 hdr.authoring_org_id,
1426 hdr.inv_organization_id,
1427 party1.object1_id1,
1428 party2.ID,
1429 hz.party_name,
1430 c.country,
1431 c.region_2 state,
1432 cont.ID,
1433 cont.object1_id1,
1434 cont.start_date,
1435 hdr.sts_code
1436 FROM okc_k_headers_b hdr,
1437 okc_k_party_roles_b party1,
1438 okc_k_party_roles_b party2,
1439 okc_contacts cont,
1440 hz_parties hz,
1441 hr_all_organization_units b,
1442 hr_locations_all c
1443 WHERE hdr.id = p_contract_hdr_id
1444 AND hdr.authoring_org_id = NVL( p_org_id, hdr.authoring_org_id )
1445 AND hdr.sts_code = p_status_code
1446 AND hdr.scs_code IN( 'SERVICE', 'WARRANTY', 'SUBSCRIPTION' )
1447 AND hdr.template_yn = 'N'
1448 AND party1.dnz_chr_id = hdr.ID
1449 AND party1.cle_id IS NULL
1450 AND party1.rle_code IN ('CUSTOMER','SUBSCRIBER')
1451 AND party1.object1_id1 = hz.party_id
1452 AND party2.dnz_chr_id = party1.dnz_chr_id
1453 AND party2.chr_id = party1.dnz_chr_id
1454 AND party2.cle_id IS NULL
1455 AND party2.rle_code IN ('VENDOR','MERCHANT')
1456 AND cont.cpl_id(+) = party2.id
1457 AND cont.cro_code(+) = p_cro_code
1458 AND (TRUNC(NVL(cont.end_date(+),SYSDATE)) >= TRUNC(SYSDATE))
1459 AND party2.object1_id1 = b.organization_id
1460 AND b.location_id = c.location_id;
1461
1462
1463 -- Contract number is not null, salesrep is not null
1464 CURSOR GET_ALL_CONTRACTS_11 (p_cro_code in VARCHAR2, p_org_id NUMBER)is
1465 SELECT /*+ PARALLEL(HDR) */
1466 hdr.ID,
1467 hdr.contract_number,
1468 hdr.contract_number_modifier,
1469 hdr.authoring_org_id,
1470 hdr.inv_organization_id,
1471 party1.object1_id1,
1472 party2.ID,
1473 hz.party_name,
1474 c.country,
1475 c.region_2 state,
1476 cont.ID,
1477 cont.object1_id1,
1478 cont.start_date,
1479 hdr.sts_code
1480 FROM okc_k_headers_b hdr,
1481 okc_statuses_b stat,
1482 okc_k_party_roles_b party1,
1483 okc_k_party_roles_b party2,
1484 okc_contacts cont,
1485 hz_parties hz,
1486 hr_all_organization_units b,
1487 hr_locations_all c
1488 WHERE hdr.id = p_contract_hdr_id
1489 AND hdr.authoring_org_id = NVL( p_org_id, hdr.authoring_org_id )
1490 AND hdr.sts_code = stat.code
1491 AND stat.ste_code IN ('ENTERED','ACTIVE','SIGNED','HOLD')
1492 AND hdr.scs_code IN( 'SERVICE', 'WARRANTY', 'SUBSCRIPTION' )
1493 AND hdr.template_yn = 'N'
1494 AND (TRUNC(NVL(cont.end_date(+),SYSDATE)) >= TRUNC(SYSDATE))
1495 AND party1.dnz_chr_id = hdr.ID
1496 AND party1.cle_id IS NULL
1497 AND party1.rle_code IN ('CUSTOMER','SUBSCRIBER')
1498 AND party1.object1_id1 = hz.party_id
1499 AND party2.dnz_chr_id = party1.dnz_chr_id
1500 AND party2.chr_id = party1.dnz_chr_id
1501 AND party2.cle_id IS NULL
1502 AND party2.rle_code IN ('VENDOR','MERCHANT')
1503 AND cont.cpl_id = party2.id
1504 AND cont.object1_id1 = p_salesrep_id
1505 AND cont.cro_code = p_cro_code
1506 AND party2.object1_id1 = b.organization_id
1507 AND b.location_id = c.location_id;
1508
1509
1510 -- Status is not null and Salesrep is not null
1511 CURSOR GET_ALL_CONTRACTS_12 (p_cro_code in VARCHAR2, p_org_id NUMBER)is
1512 SELECT /*+ PARALLEL(HDR) */
1513 hdr.ID,
1514 hdr.contract_number,
1515 hdr.contract_number_modifier,
1516 hdr.authoring_org_id,
1517 hdr.inv_organization_id,
1518 party1.object1_id1,
1519 party2.ID,
1520 hz.party_name,
1521 c.country,
1522 c.region_2 state,
1523 cont.ID,
1524 cont.object1_id1,
1525 cont.start_date,
1526 hdr.sts_code
1527 FROM okc_k_headers_b hdr,
1528 okc_k_party_roles_b party1,
1529 okc_k_party_roles_b party2,
1530 okc_contacts cont,
1531 hz_parties hz,
1532 hr_all_organization_units b,
1533 hr_locations_all c
1534 WHERE hdr.authoring_org_id = NVL( p_org_id, hdr.authoring_org_id )
1535 AND hdr.sts_code = p_status_code
1536 AND hdr.scs_code IN( 'SERVICE', 'WARRANTY', 'SUBSCRIPTION' )
1537 AND hdr.template_yn = 'N'
1538 AND party1.dnz_chr_id = hdr.ID
1539 AND party1.cle_id IS NULL
1540 AND party1.rle_code IN ('CUSTOMER','SUBSCRIBER')
1541 AND party1.object1_id1 = hz.party_id
1542 AND party2.dnz_chr_id = party1.dnz_chr_id
1543 AND party2.chr_id = party1.dnz_chr_id
1544 AND party2.cle_id IS NULL
1545 AND party2.rle_code IN ('VENDOR','MERCHANT')
1546 AND cont.cpl_id = party2.id
1547 AND cont.object1_id1 = p_salesrep_id
1548 AND cont.cro_code = p_cro_code
1549 AND (TRUNC(NVL(cont.end_date,SYSDATE)) >= TRUNC(SYSDATE))
1550 AND party2.object1_id1 = b.organization_id
1551 AND b.location_id = c.location_id;
1552
1553 -- Contract Number is not null
1554 CURSOR GET_ALL_CONTRACTS_13 (p_cro_code in VARCHAR2, p_org_id NUMBER)is
1555 SELECT /*+ PARALLEL(HDR) */
1556 hdr.ID,
1557 hdr.contract_number,
1558 hdr.contract_number_modifier,
1559 hdr.authoring_org_id,
1560 hdr.inv_organization_id,
1561 party1.object1_id1,
1562 party2.ID,
1563 hz.party_name,
1564 c.country,
1565 c.region_2 state,
1566 cont.ID,
1567 cont.object1_id1,
1568 cont.start_date,
1569 hdr.sts_code
1570 FROM okc_k_headers_b hdr,
1571 okc_statuses_b stat,
1572 okc_k_party_roles_b party1,
1573 okc_k_party_roles_b party2,
1574 okc_contacts cont,
1575 hz_parties hz,
1576 hr_all_organization_units b,
1577 hr_locations_all c
1578 WHERE hdr.id = p_contract_hdr_id
1579 AND hdr.authoring_org_id = NVL( p_org_id, hdr.authoring_org_id )
1580 AND hdr.sts_code = stat.code
1581 AND stat.ste_code IN ('ENTERED','ACTIVE','SIGNED','HOLD')
1582 AND hdr.scs_code IN( 'SERVICE', 'WARRANTY', 'SUBSCRIPTION' )
1583 AND hdr.template_yn = 'N'
1584 AND party1.dnz_chr_id = hdr.ID
1585 AND party1.cle_id IS NULL
1586 AND party1.rle_code IN ('CUSTOMER','SUBSCRIBER')
1587 AND party1.object1_id1 = hz.party_id
1588 AND party2.dnz_chr_id = party1.dnz_chr_id
1589 AND party2.chr_id = party1.dnz_chr_id
1590 AND party2.cle_id IS NULL
1591 AND party2.rle_code IN ('VENDOR','MERCHANT')
1592 AND cont.cpl_id(+) = party2.id
1593 AND cont.cro_code(+) = p_cro_code
1594 AND (TRUNC(NVL(cont.end_date(+),SYSDATE)) >= TRUNC(SYSDATE))
1595 AND party2.object1_id1 = b.organization_id
1596 AND b.location_id = c.location_id;
1597
1598
1599 -- Status is not null
1600 CURSOR GET_ALL_CONTRACTS_14 (p_cro_code in VARCHAR2, p_org_id NUMBER)is
1601 SELECT /*+ PARALLEL(HDR) */
1602 hdr.ID,
1603 hdr.contract_number,
1604 hdr.contract_number_modifier,
1605 hdr.authoring_org_id,
1606 hdr.inv_organization_id,
1607 party1.object1_id1,
1608 party2.ID,
1609 hz.party_name,
1610 c.country,
1611 c.region_2 state,
1612 cont.ID,
1613 cont.object1_id1,
1614 cont.start_date,
1615 hdr.sts_code
1616 FROM okc_k_headers_b hdr,
1617 okc_k_party_roles_b party1,
1618 okc_k_party_roles_b party2,
1619 okc_contacts cont,
1620 hz_parties hz,
1621 hr_all_organization_units b,
1622 hr_locations_all c
1623 WHERE hdr.authoring_org_id = NVL( p_org_id, hdr.authoring_org_id )
1624 AND hdr.sts_code = p_status_code
1625 AND hdr.scs_code IN( 'SERVICE', 'WARRANTY', 'SUBSCRIPTION' )
1626 AND hdr.template_yn = 'N'
1627 AND party1.dnz_chr_id = hdr.ID
1628 AND party1.cle_id IS NULL
1629 AND party1.rle_code IN ('CUSTOMER','SUBSCRIBER')
1630 AND party1.object1_id1 = hz.party_id
1631 AND party2.dnz_chr_id = party1.dnz_chr_id
1632 AND party2.chr_id = party1.dnz_chr_id
1633 AND party2.cle_id IS NULL
1634 AND party2.rle_code IN ('VENDOR','MERCHANT')
1635 AND cont.cpl_id(+) = party2.id
1636 AND cont.cro_code(+) = p_cro_code
1637 AND (TRUNC(NVL(cont.end_date(+),SYSDATE)) >= TRUNC(SYSDATE))
1638 AND party2.object1_id1 = b.organization_id
1639 AND b.location_id = c.location_id;
1640
1641 -- Sales rep is not null
1642 CURSOR GET_ALL_CONTRACTS_15 (p_cro_code in VARCHAR2, p_org_id NUMBER)is
1643 SELECT /*+ PARALLEL(HDR) */
1644 hdr.ID,
1645 hdr.contract_number,
1646 hdr.contract_number_modifier,
1647 hdr.authoring_org_id,
1648 hdr.inv_organization_id,
1649 party1.object1_id1,
1650 party2.ID,
1651 hz.party_name,
1652 c.country,
1653 c.region_2 state,
1654 cont.ID,
1655 cont.object1_id1,
1656 cont.start_date,
1657 hdr.sts_code
1658 FROM okc_k_headers_b hdr,
1659 okc_statuses_b stat,
1660 okc_k_party_roles_b party1,
1661 okc_k_party_roles_b party2,
1662 okc_contacts cont,
1663 hz_parties hz,
1664 hr_all_organization_units b,
1665 hr_locations_all c
1666 WHERE hdr.authoring_org_id = NVL( p_org_id, hdr.authoring_org_id )
1667 AND hdr.sts_code = stat.code
1668 AND stat.ste_code IN ('ENTERED','ACTIVE','SIGNED','HOLD')
1669 AND hdr.scs_code IN( 'SERVICE', 'WARRANTY', 'SUBSCRIPTION' )
1670 AND hdr.template_yn = 'N'
1671 AND party1.dnz_chr_id = hdr.ID
1672 AND party1.cle_id IS NULL
1673 AND party1.rle_code IN ('CUSTOMER','SUBSCRIBER')
1674 AND party1.object1_id1 = hz.party_id
1675 AND party2.dnz_chr_id = party1.dnz_chr_id
1676 AND party2.chr_id = party1.dnz_chr_id
1677 AND party2.cle_id IS NULL
1678 AND party2.rle_code IN ('VENDOR','MERCHANT')
1679 AND cont.cpl_id = party2.id
1680 AND cont.object1_id1 = p_salesrep_id
1681 AND cont.cro_code = p_cro_code
1682 AND (TRUNC(NVL(cont.end_date,SYSDATE)) >= TRUNC(SYSDATE))
1683 AND party2.object1_id1 = b.organization_id
1684 AND b.location_id = c.location_id;
1685
1686 -- All params are null
1687 CURSOR GET_ALL_CONTRACTS_16 (p_cro_code in VARCHAR2, p_org_id NUMBER)is
1688 SELECT /*+ PARALLEL(HDR) */
1689 hdr.ID,
1690 hdr.contract_number,
1691 hdr.contract_number_modifier,
1692 hdr.authoring_org_id,
1693 hdr.inv_organization_id,
1694 party1.object1_id1,
1695 party2.ID,
1696 hz.party_name,
1697 c.country,
1698 c.region_2 state,
1699 cont.ID,
1700 cont.object1_id1,
1701 cont.start_date,
1702 hdr.sts_code
1703 FROM okc_k_headers_b hdr,
1704 okc_statuses_b stat,
1705 okc_k_party_roles_b party1,
1706 okc_k_party_roles_b party2,
1707 okc_contacts cont,
1708 hz_parties hz,
1709 hr_all_organization_units b,
1710 hr_locations_all c
1711 WHERE hdr.authoring_org_id = NVL( p_org_id, hdr.authoring_org_id )
1712 AND hdr.sts_code = stat.code
1713 AND stat.ste_code IN ('ENTERED','ACTIVE','SIGNED','HOLD')
1714 AND hdr.scs_code IN( 'SERVICE', 'WARRANTY', 'SUBSCRIPTION' )
1715 AND hdr.template_yn = 'N'
1716 AND party1.dnz_chr_id = hdr.ID
1717 AND party1.cle_id IS NULL
1718 AND party1.rle_code IN ('CUSTOMER','SUBSCRIBER')
1719 AND party1.object1_id1 = hz.party_id
1720 AND party2.dnz_chr_id = party1.dnz_chr_id
1721 AND party2.chr_id = party1.dnz_chr_id
1722 AND party2.cle_id IS NULL
1723 AND party2.rle_code IN ('VENDOR','MERCHANT')
1724 AND cont.cpl_id(+) = party2.id
1725 AND cont.cro_code(+) = p_cro_code
1726 AND (TRUNC(NVL(cont.end_date(+),SYSDATE)) >= TRUNC(SYSDATE))
1727 AND party2.object1_id1 = b.organization_id
1728 AND b.location_id = c.location_id;
1729
1730
1731 ---------------------------------------------------------------------
1732
1733 CURSOR resource_details(p_resource_id number) IS
1734 SELECT fu.user_id
1735 FROM jtf_rs_resource_extns jrd,fnd_user fu
1736 WHERE jrd.resource_id=p_resource_id
1737 AND fu.user_id = jrd.user_id;
1738
1739 CURSOR create_contact_resource IS
1740 SELECT chr_id,resource_id,user_id,
1741 salesrep_id,org_id
1742 FROM oks_jtf_res_temp;
1743
1744 CURSOR update_contact_resource IS
1745 SELECT contact_id,authoring_org_id
1746 FROM oks_k_res_temp
1747 WHERE status IN
1748 (SELECT code
1749 FROM okc_statuses_v
1750 WHERE ste_code IN('ACTIVE','SIGNED','HOLD'))
1751 AND contact_id IS NOT NULL;
1752
1753 CURSOR delete_contact_resource IS
1754 SELECT contact_id,authoring_org_id
1755 FROM oks_k_res_temp
1756 WHERE status IN
1757 (SELECT code
1758 FROM okc_statuses_v
1759 WHERE ste_code = 'ENTERED')
1760 AND contact_id IS NOT NULL;
1761
1762 CURSOR contact_resource_in_future IS
1763 SELECT id,contract_number,contract_number_modifier
1764 FROM oks_k_res_temp
1765 WHERE status IN
1766 (SELECT code
1767 FROM okc_statuses_v
1768 WHERE ste_code IN('ACTIVE','SIGNED','HOLD'))
1769 AND contact_start_date >= trunc(sysdate) ;
1770
1771 CURSOR contract_noresource IS
1772 SELECT id,contract_number, contract_number_modifier,party_name
1773 FROM OKS_K_RES_TEMP
1774 WHERE id not in (SELECT chr_id FROM OKS_JTF_RES_TEMP);
1775
1776 CURSOR get_contract_num_mod(p_chr_id NUMBER) IS
1777 SELECT contract_number, contract_number_modifier
1778 FROM okc_k_headers_b
1779 WHERE id = p_chr_id;
1780
1781 l_ctcv_tbl_in okc_contract_party_pub.ctcv_tbl_type;
1782 l_ctcv_tbl_in_del okc_contract_party_pub.ctcv_tbl_type;
1783 l_ctcv_tbl_in_upd okc_contract_party_pub.ctcv_tbl_type;
1784
1785 l_ctcv_tbl_out okc_contract_party_pub.ctcv_tbl_type;
1786 l_ctcv_tbl_out_upd okc_contract_party_pub.ctcv_tbl_type;
1787 l_ctcv_tbl_out_del okc_contract_party_pub.ctcv_tbl_type;
1788
1789 l_ctcv_rec_out_upd okc_contract_party_pub.ctcv_rec_type;
1790 l_ctcv_rec_out_ins okc_contract_party_pub.ctcv_rec_type;
1791 l_ctcv_rec_out_del okc_contract_party_pub.ctcv_rec_type;
1792
1793
1794
1795
1796 FUNCTION GET_CONTRACT_NUMBER (p_chr_id Number ) RETURN VARCHAR2 IS
1797 CURSOR l_contract_csr IS
1798 SELECT contract_number
1799 FROM okc_k_headers_b
1800 WHERE id = p_chr_id;
1801 l_contract_number VARCHAR2(120);
1802 BEGIN
1803 FOR c_l_contract_csr in l_contract_csr LOOP
1804 l_contract_number := c_l_contract_csr.contract_number;
1805 EXIT;
1806 END LOOP;
1807 return(l_contract_number);
1808 END GET_CONTRACT_NUMBER;
1809
1810 FUNCTION GET_CPL_ID (p_chr_id Number ) RETURN NUMBER IS
1811
1812 CURSOR get_cpl_id_csr(p_chr_id number) IS
1813 SELECT id
1814 FROM okc_k_party_roles_b
1815 WHERE dnz_chr_id = p_chr_id
1816 AND rle_code IN ('VENDOR','MERCHANT')
1817 AND cle_id is null;
1818
1819 l_cpl_id NUMBER;
1820 BEGIN
1821 FOR c_l_salesrep_csr in get_cpl_id_csr(P_CHR_ID) LOOP
1822 l_cpl_id := c_l_salesrep_csr.id;
1823 EXIT;
1824 END LOOP;
1825 return(l_cpl_id);
1826 END GET_CPL_ID;
1827
1828 FUNCTION GET_ORG_ID (p_chr_id Number ) RETURN NUMBER IS
1829
1830 CURSOR get_org_id(p_chr_id number) IS
1831 SELECT authoring_org_id
1832 FROM okc_k_headers_b
1833 WHERE id = p_chr_id;
1834
1835 l_org_id NUMBER;
1836 BEGIN
1837 OPEN get_org_id(p_chr_id);
1838 FETCH get_org_id INTO l_org_id;
1839 CLOSE get_org_id;
1840
1841 return(l_org_id);
1842 END GET_ORG_ID;
1843
1844 BEGIN
1845
1846 fnd_file.put_line(FND_FILE.LOG,'Start Time ' || to_char(sysdate,'HH:MI:SS'));
1847 -- l_request_id := FND_GLOBAL.CONC_REQUEST_ID;
1848
1849 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1850 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT ||'.SUBMIT_CONTACT_CREATION',
1851 'Reassign: start time= '
1852 ||to_char(sysdate,'HH:MI:SS') ||' '
1853 || ' p_org_id : ' || p_org_id
1854 || ' p_salesrep_id: ' || p_salesrep_id
1855 || ' p_contract_number: ' || p_contract_hdr_id
1856 || ' p_contract_status: ' || p_status_code);
1857 END IF;
1858
1859
1860 l_org_id := p_org_id;
1861
1862 IF (FND_PROFILE.VALUE('OKS_USE_JTF') = 'YES') THEN
1863 fnd_file.put_line(FND_FILE.LOG,'OKS: Use Territories to Default Sales Person: Yes');
1864 l_cro_code := FND_PROFILE.VALUE('OKS_VENDOR_CONTACT_ROLE');
1865
1866 -- set org context when the org parameter is not null
1867 IF l_org_id IS NOT NULL THEN
1868 OKC_CONTEXT.set_okc_org_context(l_org_id,null);
1869 END IF;
1870
1871 IF l_cro_code IS NULL THEN
1872 fnd_file.put_line(FND_FILE.LOG,'Invalid CRO Code');
1873 NOTIFY_SETUP_ADMIN;
1874 ELSE
1875 IF NVL(fnd_profile.value('OKS_SRC_TERR_QUALFIERS'),'V')='V' THEN
1876 l_terr_res := 'V';
1877 ELSE
1878 l_terr_res := 'C';
1879 END IF;
1880
1881 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1882 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT ||'.SUBMIT_CONTACT_CREATION',
1883 'Profile Value for OKS_SRC_TERR_QUALFIERS is '|| l_terr_res);
1884 END IF;
1885
1886 IF l_terr_res = 'V' THEN
1887
1888 IF (p_contract_hdr_id IS NOT NULL AND p_status_code IS NOT NULL AND p_salesrep_id IS NOT NULL ) THEN
1889 OPEN GET_ALL_CONTRACTS_9(l_cro_code,l_org_id);
1890 ELSIF (p_contract_hdr_id IS NOT NULL AND p_status_code IS NOT NULL) THEN
1891 OPEN GET_ALL_CONTRACTS_10(l_cro_code,l_org_id);
1892 ELSIF (p_contract_hdr_id IS NOT NULL AND p_salesrep_id IS NOT NULL) THEN
1893 OPEN GET_ALL_CONTRACTS_11(l_cro_code,l_org_id);
1894 ELSIF (p_status_code IS NOT NULL AND p_salesrep_id IS NOT NULL) THEN
1895 OPEN GET_ALL_CONTRACTS_12(l_cro_code,l_org_id) ;
1896 ELSIF (p_contract_hdr_id IS NOT NULL) THEN
1897 OPEN GET_ALL_CONTRACTS_13(l_cro_code,l_org_id) ;
1898 ELSIF (p_status_code IS NOT NULL) THEN
1899 OPEN GET_ALL_CONTRACTS_14(l_cro_code,l_org_id);
1900 ELSIF (p_salesrep_id IS NOT NULL) THEN
1901 OPEN GET_ALL_CONTRACTS_15(l_cro_code,l_org_id);
1902 ELSE
1903 OPEN GET_ALL_CONTRACTS_16(l_cro_code,l_org_id);
1904 END IF;
1905 ELSE
1906 -- OPEN THE CURSOR BASED ON THE INPUT PARAMETERS
1907 IF (p_contract_hdr_id IS NOT NULL AND p_status_code IS NOT NULL AND p_salesrep_id IS NOT NULL ) THEN
1908 OPEN GET_ALL_CONTRACTS_1(l_cro_code,l_org_id);
1909 ELSIF (p_contract_hdr_id IS NOT NULL AND p_status_code IS NOT NULL) THEN
1910 OPEN GET_ALL_CONTRACTS_2(l_cro_code,l_org_id);
1911 ELSIF (p_contract_hdr_id IS NOT NULL AND p_salesrep_id IS NOT NULL) THEN
1912 OPEN GET_ALL_CONTRACTS_3(l_cro_code,l_org_id);
1913 ELSIF (p_status_code IS NOT NULL AND p_salesrep_id IS NOT NULL) THEN
1914 OPEN GET_ALL_CONTRACTS_4(l_cro_code,l_org_id) ;
1915 ELSIF (p_contract_hdr_id IS NOT NULL) THEN
1916 OPEN GET_ALL_CONTRACTS_5(l_cro_code,l_org_id) ;
1917 ELSIF (p_status_code IS NOT NULL) THEN
1918 OPEN GET_ALL_CONTRACTS_6(l_cro_code,l_org_id);
1919 ELSIF (p_salesrep_id IS NOT NULL) THEN
1920 OPEN GET_ALL_CONTRACTS_7(l_cro_code,l_org_id);
1921 ELSE
1922 OPEN GET_ALL_CONTRACTS_8(l_cro_code,l_org_id);
1923 END IF;
1924 END IF;
1925 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1926 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT ||'.SUBMIT_CONTACT_CREATION',
1927 'Before Cursor Fetch');
1928 END IF;
1929 LOOP
1930 IF GET_ALL_CONTRACTS_1%ISOPEN THEN
1931 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1932 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT ||'.SUBMIT_CONTACT_CREATION',
1933 ' GET_ALL_CONTRACTS_1%ISOPEN - ');
1934 END IF;
1935 FETCH GET_ALL_CONTRACTS_1 BULK COLLECT INTO lb_id,lb_contract_number,
1936 lb_contract_number_modifier,
1937 lb_authoring_org_id,
1938 lb_inv_organization_id,
1939 lb_party_id,lb_cpl_id,
1940 lb_party_name,lb_country_code,
1941 lb_state_code,lb_contact_id,
1942 lb_salesrep_id,
1943 lb_contact_start_date,
1944 lb_status limit 1000;
1945 ELSIF GET_ALL_CONTRACTS_2%ISOPEN THEN
1946 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1947 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT ||'.SUBMIT_CONTACT_CREATION',
1948 ' GET_ALL_CONTRACTS_2%ISOPEN - ');
1949 END IF;
1950 FETCH GET_ALL_CONTRACTS_2 BULK COLLECT INTO lb_id,lb_contract_number,
1951 lb_contract_number_modifier,
1952 lb_authoring_org_id,
1953 lb_inv_organization_id,
1954 lb_party_id,lb_cpl_id,
1955 lb_party_name,lb_country_code,
1956 lb_state_code,lb_contact_id,
1957 lb_salesrep_id,
1958 lb_contact_start_date,
1959 lb_status limit 1000;
1960 ELSIF GET_ALL_CONTRACTS_3%ISOPEN THEN
1961 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1962 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT ||'.SUBMIT_CONTACT_CREATION',
1963 ' GET_ALL_CONTRACTS_3%ISOPEN - ');
1964 END IF;
1965 FETCH GET_ALL_CONTRACTS_3 BULK COLLECT INTO lb_id,lb_contract_number,
1966 lb_contract_number_modifier,
1967 lb_authoring_org_id,
1968 lb_inv_organization_id,
1969 lb_party_id,lb_cpl_id,
1970 lb_party_name,lb_country_code,
1971 lb_state_code,lb_contact_id,
1972 lb_salesrep_id,
1973 lb_contact_start_date,
1974 lb_status limit 1000;
1975 ELSIF GET_ALL_CONTRACTS_4%ISOPEN THEN
1976 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1977 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT ||'.SUBMIT_CONTACT_CREATION',
1978 ' GET_ALL_CONTRACTS_4%ISOPEN - ');
1979 END IF;
1980 FETCH GET_ALL_CONTRACTS_4 BULK COLLECT INTO lb_id,lb_contract_number,
1981 lb_contract_number_modifier,
1982 lb_authoring_org_id,
1983 lb_inv_organization_id,
1984 lb_party_id,lb_cpl_id,
1985 lb_party_name,lb_country_code,
1986 lb_state_code,lb_contact_id,
1987 lb_salesrep_id,
1988 lb_contact_start_date,
1989 lb_status limit 1000;
1990
1991 ELSIF GET_ALL_CONTRACTS_5%ISOPEN THEN
1992 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1993 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT ||'.SUBMIT_CONTACT_CREATION',
1994 ' GET_ALL_CONTRACTS_5%ISOPEN - ');
1995 END IF;
1996 FETCH GET_ALL_CONTRACTS_5 BULK COLLECT INTO lb_id,lb_contract_number,
1997 lb_contract_number_modifier,
1998 lb_authoring_org_id,
1999 lb_inv_organization_id,
2000 lb_party_id,lb_cpl_id,
2001 lb_party_name,lb_country_code,
2002 lb_state_code,lb_contact_id,
2003 lb_salesrep_id ,
2004 lb_contact_start_date,
2005 lb_status limit 1000;
2006 ELSIF GET_ALL_CONTRACTS_6%ISOPEN THEN
2007 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2008 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT ||'.SUBMIT_CONTACT_CREATION',
2009 ' GET_ALL_CONTRACTS_6%ISOPEN - ');
2010 END IF;
2011 FETCH GET_ALL_CONTRACTS_6 BULK COLLECT INTO lb_id,lb_contract_number,
2012 lb_contract_number_modifier,
2013 lb_authoring_org_id,
2014 lb_inv_organization_id,
2015 lb_party_id,lb_cpl_id,
2016 lb_party_name,lb_country_code,
2017 lb_state_code,lb_contact_id,
2018 lb_salesrep_id ,
2019 lb_contact_start_date,
2020 lb_status limit 1000;
2021 ELSIF GET_ALL_CONTRACTS_7%ISOPEN THEN
2022 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2023 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT ||'.SUBMIT_CONTACT_CREATION',
2024 ' GET_ALL_CONTRACTS_7%ISOPEN - ');
2025 END IF;
2026 FETCH GET_ALL_CONTRACTS_7 BULK COLLECT INTO lb_id,lb_contract_number,
2027 lb_contract_number_modifier,
2028 lb_authoring_org_id,
2029 lb_inv_organization_id,
2030 lb_party_id,lb_cpl_id,
2031 lb_party_name,lb_country_code,
2032 lb_state_code,lb_contact_id,
2033 lb_salesrep_id ,
2034 lb_contact_start_date,
2035 lb_status limit 1000;
2036 ELSIF GET_ALL_CONTRACTS_8%ISOPEN THEN
2037 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2038 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT ||'.SUBMIT_CONTACT_CREATION',
2039 ' GET_ALL_CONTRACTS_8%ISOPEN - ');
2040 END IF;
2041 FETCH GET_ALL_CONTRACTS_8 BULK COLLECT INTO lb_id,lb_contract_number,
2042 lb_contract_number_modifier,
2043 lb_authoring_org_id,
2044 lb_inv_organization_id,
2045 lb_party_id,lb_cpl_id,
2046 lb_party_name,lb_country_code,
2047 lb_state_code,lb_contact_id,
2048 lb_salesrep_id,
2049 lb_contact_start_date,
2050 lb_status limit 1000;
2051
2052 ELSIF GET_ALL_CONTRACTS_9%ISOPEN THEN
2053 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2054 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT ||'.SUBMIT_CONTACT_CREATION',
2055 ' GET_ALL_CONTRACTS_9%ISOPEN - ');
2056 END IF;
2057 FETCH GET_ALL_CONTRACTS_9 BULK COLLECT INTO lb_id,lb_contract_number,
2058 lb_contract_number_modifier,
2059 lb_authoring_org_id,
2060 lb_inv_organization_id,
2061 lb_party_id,lb_cpl_id,
2062 lb_party_name,lb_country_code,
2063 lb_state_code,lb_contact_id,
2064 lb_salesrep_id,
2065 lb_contact_start_date,
2066 lb_status limit 1000;
2067 ELSIF GET_ALL_CONTRACTS_10%ISOPEN THEN
2068 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2069 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT ||'.SUBMIT_CONTACT_CREATION',
2070 ' GET_ALL_CONTRACTS_10%ISOPEN - ');
2071 END IF;
2072 FETCH GET_ALL_CONTRACTS_10 BULK COLLECT INTO lb_id,lb_contract_number,
2073 lb_contract_number_modifier,
2074 lb_authoring_org_id,
2075 lb_inv_organization_id,
2076 lb_party_id,lb_cpl_id,
2077 lb_party_name,lb_country_code,
2078 lb_state_code,lb_contact_id,
2079 lb_salesrep_id,
2080 lb_contact_start_date,
2081 lb_status limit 1000;
2082 ELSIF GET_ALL_CONTRACTS_11%ISOPEN THEN
2083 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2084 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT ||'.SUBMIT_CONTACT_CREATION',
2085 ' GET_ALL_CONTRACTS_11%ISOPEN - ');
2086 END IF;
2087 FETCH GET_ALL_CONTRACTS_11 BULK COLLECT INTO lb_id,lb_contract_number,
2088 lb_contract_number_modifier,
2089 lb_authoring_org_id,
2090 lb_inv_organization_id,
2091 lb_party_id,lb_cpl_id,
2092 lb_party_name,lb_country_code,
2093 lb_state_code,lb_contact_id,
2094 lb_salesrep_id,
2095 lb_contact_start_date,
2096 lb_status limit 1000;
2097 ELSIF GET_ALL_CONTRACTS_12%ISOPEN THEN
2098 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2099 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT ||'.SUBMIT_CONTACT_CREATION',
2100 ' GET_ALL_CONTRACTS_12%ISOPEN - ');
2101 END IF;
2102 FETCH GET_ALL_CONTRACTS_12 BULK COLLECT INTO lb_id,lb_contract_number,
2103 lb_contract_number_modifier,
2104 lb_authoring_org_id,
2105 lb_inv_organization_id,
2106 lb_party_id,lb_cpl_id,
2107 lb_party_name,lb_country_code,
2108 lb_state_code,lb_contact_id,
2109 lb_salesrep_id,
2110 lb_contact_start_date,
2111 lb_status limit 1000;
2112
2113 ELSIF GET_ALL_CONTRACTS_13%ISOPEN THEN
2114 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2115 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT ||'.SUBMIT_CONTACT_CREATION',
2116 ' GET_ALL_CONTRACTS_13%ISOPEN - ');
2117 END IF;
2118 FETCH GET_ALL_CONTRACTS_13 BULK COLLECT INTO lb_id,lb_contract_number,
2119 lb_contract_number_modifier,
2120 lb_authoring_org_id,
2121 lb_inv_organization_id,
2122 lb_party_id,lb_cpl_id,
2123 lb_party_name,lb_country_code,
2124 lb_state_code,lb_contact_id,
2125 lb_salesrep_id ,
2126 lb_contact_start_date,
2127 lb_status limit 1000;
2128 ELSIF GET_ALL_CONTRACTS_14%ISOPEN THEN
2129 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2130 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT ||'.SUBMIT_CONTACT_CREATION',
2131 ' GET_ALL_CONTRACTS_14%ISOPEN - ');
2132 END IF;
2133 FETCH GET_ALL_CONTRACTS_14 BULK COLLECT INTO lb_id,lb_contract_number,
2134 lb_contract_number_modifier,
2135 lb_authoring_org_id,
2136 lb_inv_organization_id,
2137 lb_party_id,lb_cpl_id,
2138 lb_party_name,lb_country_code,
2139 lb_state_code,lb_contact_id,
2140 lb_salesrep_id ,
2141 lb_contact_start_date,
2142 lb_status limit 1000;
2143 ELSIF GET_ALL_CONTRACTS_15%ISOPEN THEN
2144 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2145 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT ||'.SUBMIT_CONTACT_CREATION',
2146 ' GET_ALL_CONTRACTS_15%ISOPEN - ');
2147 END IF;
2148 FETCH GET_ALL_CONTRACTS_15 BULK COLLECT INTO lb_id,lb_contract_number,
2149 lb_contract_number_modifier,
2150 lb_authoring_org_id,
2151 lb_inv_organization_id,
2152 lb_party_id,lb_cpl_id,
2153 lb_party_name,lb_country_code,
2154 lb_state_code,lb_contact_id,
2155 lb_salesrep_id ,
2156 lb_contact_start_date,
2157 lb_status limit 1000;
2158 ELSIF GET_ALL_CONTRACTS_16%ISOPEN THEN
2159 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2160 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT ||'.SUBMIT_CONTACT_CREATION',
2161 ' GET_ALL_CONTRACTS_16%ISOPEN - ');
2162 END IF;
2163 FETCH GET_ALL_CONTRACTS_16 BULK COLLECT INTO lb_id,lb_contract_number,
2164 lb_contract_number_modifier,
2165 lb_authoring_org_id,
2166 lb_inv_organization_id,
2167 lb_party_id,lb_cpl_id,
2168 lb_party_name,lb_country_code,
2169 lb_state_code,lb_contact_id,
2170 lb_salesrep_id,
2171 lb_contact_start_date,
2172 lb_status limit 1000;
2173
2174 END IF;
2175
2176 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2177 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT ||'.SUBMIT_CONTACT_CREATION',
2178 ' Main cursor count : ' || lb_id.count ||
2179 ' After Main Cursor Fetch time: ' || to_char(sysdate,'HH:MI:SS'));
2180 END IF;
2181
2182 l_gen_return_Rec.trans_object_id.delete;
2183 l_gen_return_Rec.resource_id.delete;
2184 l_gen_bulk_rec.trans_object_id.delete;
2185 l_gen_bulk_rec.trans_detail_object_id.delete;
2186 l_gen_bulk_rec.SQUAL_CHAR01.delete;
2187 l_gen_bulk_rec.SQUAL_CHAR04.delete;
2188 l_gen_bulk_rec.SQUAL_CHAR07.delete;
2189 l_gen_bulk_rec.SQUAL_NUM01.delete;
2190
2191
2192
2193 IF lb_id.count > 0 THEN
2194 FOR i in lb_id.first..lb_id.last
2195 LOOP
2196 l_gen_bulk_rec.trans_object_id.EXTEND;
2197 l_gen_bulk_rec.trans_detail_object_id.EXTEND;
2198 l_gen_bulk_rec.SQUAL_CHAR01.EXTEND;
2199 l_gen_bulk_rec.SQUAL_CHAR04.EXTEND;
2200 l_gen_bulk_rec.SQUAL_CHAR07.EXTEND;
2201 l_gen_bulk_rec.SQUAL_NUM01.EXTEND;
2202 l_gen_bulk_rec.trans_object_id(i) := lb_id(i);
2203 l_gen_bulk_rec.trans_detail_object_id(i) := lb_id(i);
2204 l_gen_bulk_rec.SQUAL_CHAR01(i) := lb_party_name(i);
2205 l_gen_bulk_rec.SQUAL_CHAR04(i) := lb_state_code(i);
2206 l_gen_bulk_rec.SQUAL_CHAR07(i) := lb_country_code(i);
2207 l_gen_bulk_rec.SQUAL_NUM01(i) := lb_party_id(i);
2208 l_use_type := 'RESOURCE';
2209
2210 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2211 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT ||'.SUBMIT_CONTACT_CREATION',
2212 ' Parameters Passed to Territory: ' ||
2213 ' Party Name ' || lb_party_name(i) ||
2214 ' State ' || lb_state_code(i) ||
2215 ' Country ' || lb_country_code(i)||
2216 ' Party Id ' || lb_party_id(i) );
2217 END IF;
2218 INSERT INTO OKS_K_RES_TEMP (id,contract_number,contract_number_modifier,status,
2219 authoring_org_id,inv_organization_id,party_id,cpl_id,
2220 party_name,country_code,state_code,contact_id,salesrep_id,
2221 contact_start_date,contact_end_date,contract_start_date,contract_end_date)
2222 values (lb_id(i)
2223 ,lb_contract_number(i)
2224 ,lb_contract_number_modifier(i)
2225 ,lb_status(i)
2226 ,lb_authoring_org_id(i)
2227 ,lb_inv_organization_id(i)
2228 ,lb_party_id(i)
2229 ,lb_cpl_id(i)
2230 ,lb_party_name(i)
2231 ,lb_country_code(i)
2232 ,lb_state_code(i)
2233 ,lb_contact_id(i)
2234 ,lb_salesrep_id(i)
2235 ,lb_contact_start_date(i)
2236 ,null
2237 ,null
2238 ,null
2239 );
2240
2241 END LOOP;
2242
2243 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2244 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT ||'.SUBMIT_CONTACT_CREATION',
2245 'INSERTED INTO oks_reassign_resource_TMP : Successful'
2246 || 'Nmber of Recs passedto JTF: '
2247 || l_gen_bulk_rec.trans_object_id.count
2248 || 'JTF_TERR_ASSIGN_PUB.get_winners start :'
2249 || to_char(sysdate,'HH:MI:SS'));
2250 END IF;
2251
2252 -- Call JTT API to get the winners
2253 JTF_TERR_ASSIGN_PUB.get_winners
2254 ( p_api_version_number => 1.0,
2255 p_init_msg_list => OKC_API.G_FALSE,
2256 p_use_type => l_use_type,
2257 p_source_id => -1500,
2258 p_trans_id => -1501,
2259 p_trans_rec => l_gen_bulk_rec,
2260 p_resource_type => FND_API.G_MISS_CHAR,
2261 p_role => FND_API.G_MISS_CHAR,
2262 p_top_level_terr_id => FND_API.G_MISS_NUM,
2263 p_num_winners => FND_API.G_MISS_NUM,
2264 x_return_status => l_return_status,
2265 x_msg_count => l_msg_count,
2266 x_msg_data => l_msg_data,
2267 x_winners_rec => l_gen_return_rec
2268 );
2269
2270 fnd_file.put_line(FND_FILE.LOG,'After JTF API Call : ' || l_return_status ) ;
2271 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2272 -- Handle error from territory API
2273 fnd_file.put_line(FND_FILE.LOG,'Exception in JTF Territory call');
2274 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2275 ELSE
2276 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2277 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT ||'.SUBMIT_CONTACT_CREATION',
2278 'JTF_TERR_ASSIGN_PUB.get_winners end :'
2279 || to_char(sysdate,'HH:MI:SS') );
2280 END IF;
2281
2282 END IF;
2283
2284 l_winning_tbl.delete;
2285 l_counter := l_gen_return_Rec.trans_object_id.FIRST;
2286 l2_winning_tbl(1).chr_id:=0;
2287 WHILE (l_counter <= l_gen_return_Rec.trans_object_id.LAST)
2288 LOOP
2289 IF l2_winning_tbl(1).chr_id<> l_gen_return_rec.trans_object_id(l_counter) THEN
2290 l_user_id := to_number(null);
2291 -- Set OUT parameters
2292 l_winning_tbl(l_counter).resource_id :=l_gen_return_Rec.RESOURCE_ID(l_counter);
2293 l_winning_tbl(l_counter).user_id := l_user_id;
2294 l_winning_tbl(l_counter).chr_id := l_gen_return_rec.trans_object_id(l_counter);
2295 l2_winning_tbl(1).chr_id:= l_gen_return_rec.trans_object_id(l_counter);
2296
2297 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2298 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT ||'.SUBMIT_CONTACT_CREATION',
2299 ' Resource Returned from JTT API for Contract : ' || l_gen_return_rec.trans_object_id(l_counter)
2300 || ' is ' || l_gen_return_Rec.RESOURCE_ID(l_counter));
2301 END IF;
2302 END IF;
2303 l_counter := l_counter + 1;
2304 END LOOP;
2305
2306
2307 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2308 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT ||'.SUBMIT_CONTACT_CREATION',
2309 'l_winning_tbl count is : '|| l_winning_tbl.count ||
2310 ' Completed time '|| to_char(sysdate,'HH:MI:SS'));
2311 END IF;
2312
2313 IF l_winning_tbl.count>0 THEN
2314 idx4:= l_winning_tbl.FIRST;
2315 LOOP
2316 IF l_winning_tbl(idx4).resource_id IS NOT NULL THEN
2317 l_temp_salesrep := GET_CHR_SALESREP_ID(l_winning_tbl(idx4).resource_id,
2318 l_winning_tbl(idx4).chr_id);
2319 l_temp_org_id := GET_ORG_ID(l_winning_tbl(idx4).chr_id);
2320 IF l_temp_salesrep IS NOT NULL THEN
2321 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2322 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT ||'.SUBMIT_CONTACT_CREATION',
2323 ' Contract selected to insert into JTF_RES_TEMP : ' || l_winning_tbl(idx4).chr_id);
2324 END IF;
2325
2326 INSERT INTO OKS_JTF_RES_TEMP (chr_id,resource_id,user_id,salesrep_id
2327 ,org_id,inv_organization_id,contract_start_date,contract_end_date)
2328 values(l_winning_tbl(idx4).chr_id
2329 ,l_winning_tbl(idx4).resource_id
2330 ,l_winning_tbl(idx4).user_id
2331 ,l_temp_salesrep
2332 ,l_temp_org_id
2333 ,null
2334 ,null
2335 ,null);
2336 ELSE
2337 l_admin_msg := FND_MESSAGE.GET_STRING('OKS','OKS_INVALID_SALES_PERSON');
2338 OPEN get_contract_num_mod(l_winning_tbl(idx4).chr_id);
2339 FETCH get_contract_num_mod into l_e_contract_number,l_e_contract_number_mod;
2340 CLOSE get_contract_num_mod;
2341
2342 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2343 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT ||'.SUBMIT_CONTACT_CREATION',
2344 ' Invalid salesrep returned for this Contract : ' || l_e_contract_number || ' ' || l_e_contract_number_mod);
2345 END IF;
2346
2347 --Delete this contract
2348 DELETE FROM oks_k_res_temp
2349 WHERE id = l_winning_tbl(idx4).chr_id;
2350
2351 NOTIFY_TERRITORY_ADMIN
2352 ( 1,
2353 l_e_contract_number,
2354 l_e_contract_number_mod,
2355 l_admin_msg);
2356 fnd_file.put_line(FND_FILE.LOG,l_admin_msg || ' Contract Number - ' || l_e_contract_number);
2357 END IF;
2358 EXIT WHEN idx4 =l_winning_tbl.LAST;
2359 idx4 := l_winning_tbl.NEXT(idx4);
2360 END IF;
2361 END LOOP;
2362 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2363 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT ||'.SUBMIT_CONTACT_CREATION',
2364 'oks_jtf_resource_TMP Table population completed');
2365 END IF;
2366 END IF;
2367
2368
2369 -- Delete all contracts which has a salesrep (not end dated),which is same as the resource setup.
2370 DELETE FROM oks_k_res_temp a
2371 WHERE exists
2372 ( SELECT null
2373 FROM oks_k_res_temp b,
2374 oks_jtf_res_temp c
2375 WHERE b.id = a.id
2376 AND b.salesrep_id = c.salesrep_id
2377 AND c.chr_id = a.id
2378 );
2379
2380 -- Send notification for those contracts with no resource setup
2381 FOR contract_noresource_rec in contract_noresource
2382 LOOP
2383 l_terr_admin_msg := FND_MESSAGE.GET_STRING('OKS','OKS_NO_TERR_RESOURCES');
2384 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2385 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT ||'.SUBMIT_CONTACT_CREATION',
2386 'Contract with no resource setup : ' || contract_noresource_rec.Contract_number || ' '
2387 || contract_noresource_rec.Contract_number_modifier);
2388 end if;
2389 NOTIFY_TERRITORY_ADMIN
2390 ( contract_noresource_rec.id,
2391 contract_noresource_rec.Contract_number,
2392 contract_noresource_rec.Contract_number_modifier,l_terr_admin_msg);
2393 DELETE FROM oks_k_res_temp
2394 WHERE id = contract_noresource_rec.id;
2395 fnd_file.put_line(FND_FILE.LOG,'There is no resource setup. Contract Number - '|| contract_noresource_rec.Contract_number);
2396 END LOOP;
2397
2398 -- Send notification for those contracts which has a vendor contact starting in future.
2399 l_f_chr_id := 0;
2400 FOR contact_resource_inf_rec IN contact_resource_in_future
2401 LOOP
2402 l_terr_admin_msg := FND_MESSAGE.GET_STRING('OKS','OKS_VENDOR_STAMP_ERROR');
2403 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2404 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT ||'.SUBMIT_CONTACT_CREATION',
2405 'Contract with vendor contact starting in future: ' || contact_resource_inf_rec.Contract_number || ' '
2406 || contact_resource_inf_rec.Contract_number_modifier);
2407 end if;
2408 IF ( l_f_chr_id <> contact_resource_inf_rec.id) THEN
2409 NOTIFY_TERRITORY_ADMIN
2410 ( contact_resource_inf_rec.id,
2411 contact_resource_inf_rec.Contract_number,
2412 contact_resource_inf_rec.Contract_number_modifier,l_terr_admin_msg);
2413 fnd_file.put_line(FND_FILE.LOG,'Unable to terminate current vendor contact. Contract Number - ' || contact_resource_inf_rec.Contract_number);
2414 END IF;
2415 l_f_chr_id := contact_resource_inf_rec.id;
2416 END LOOP;
2417
2418 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2419 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT ||'.SUBMIT_CONTACT_CREATION',
2420 'Notifications sends for no resources and resources in future date');
2421 END IF;
2422
2423
2424 -- Delete all contracts which has a salesrep with start date in future.
2425 DELETE FROM oks_k_res_temp a
2426 WHERE exists
2427 ( SELECT null
2428 FROM oks_k_res_temp b
2429 WHERE b.id = a.id
2430 AND b.contact_start_date >= trunc(sysdate)
2431 AND b.status IN (SELECT code
2432 FROM okc_statuses_v
2433 WHERE ste_code IN('ACTIVE','SIGNED','HOLD'))
2434 );
2435
2436 -- Now delete those contracts from oks_jtf_res_temp which are deleted from oks_k_res_temp
2437 DELETE FROM oks_jtf_res_temp a
2438 WHERE not exists
2439 ( SELECT null
2440 FROM oks_k_res_temp b
2441 WHERE a.chr_id = b.id
2442 );
2443
2444
2445 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2446 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT ||'.SUBMIT_CONTACT_CREATION',
2447 'Before fetching create_contact_resource cursor');
2448 END IF;
2449
2450 -- Get all contracts to be stamped with new salesrep.
2451 OPEN create_contact_resource;
2452 LOOP
2453 FETCH create_contact_resource BULK COLLECT INTO
2454 lj_chr_id
2455 ,lj_resource_id
2456 ,lj_user_id
2457 ,lj_salesrep_id
2458 ,lj_org_id limit 1000;
2459 IF lj_chr_id.count > 0 THEN
2460 FOR i2 in lj_chr_id.first..lj_chr_id.last
2461 LOOP
2462 l_cpl_id := get_cpl_id(lj_chr_id(i2));
2463 l_contract_number := get_contract_number(lj_chr_id(i2));
2464 l_ctcv_tbl_in(idx1).cpl_id := l_cpl_id;
2465 l_ctcv_tbl_in(idx1).cro_code := l_cro_code;
2466 l_ctcv_tbl_in(idx1).dnz_chr_id := lj_chr_id(i2);
2467 l_ctcv_tbl_in(idx1).OBJECT1_ID1 := lj_salesrep_id(i2);
2468 l_ctcv_tbl_in(idx1).object1_id2 := '#';
2469 l_ctcv_tbl_in(idx1).JTOT_OBJECT1_CODE := 'OKX_SALEPERS';
2470 l_ctcv_tbl_in(idx1).attribute1 := lj_user_id(i2);
2471 l_ctcv_tbl_in(idx1).attribute2 := l_contract_number;
2472 l_ctcv_tbl_in(idx1).object_version_number := OKC_API.G_MISS_NUM;
2473 l_ctcv_tbl_in(idx1).created_by := OKC_API.G_MISS_NUM;
2474 l_ctcv_tbl_in(idx1).creation_date := SYSDATE;
2475 l_ctcv_tbl_in(idx1).last_updated_by := OKC_API.G_MISS_NUM;
2476 l_ctcv_tbl_in(idx1).last_update_date := SYSDATE;
2477 l_ctcv_tbl_in(idx1).last_update_login := OKC_API.G_MISS_NUM;
2478 l_ctcv_tbl_in(idx1).start_date := SYSDATE;
2479 l_ctcv_tbl_in(idx1).attribute3 := lj_org_id(i2);
2480 -- Bug Fix 4749200
2481 IF l_current_salesrep_id <> lj_salesrep_id(i2) THEN
2482 l_current_salesgrp_id := jtf_rs_integration_pub.get_default_sales_group( p_salesrep_id => lj_salesrep_id(i2) ,
2483 p_org_id => lj_org_id(i2),
2484 p_date => SYSDATE );
2485 l_ctcv_tbl_in(idx1).sales_group_id := l_current_salesgrp_id ;
2486 l_current_salesrep_id := lj_salesrep_id(i2) ;
2487 ELSE
2488 l_ctcv_tbl_in(idx1).sales_group_id := l_current_salesgrp_id ;
2489 END IF;
2490
2491 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2492 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT ||'.SUBMIT_CONTACT_CREATION',
2493 'contract selected to create new contact : ' || l_contract_number
2494 || ' ' || 'ID ' || lj_chr_id(i2) );
2495 END IF;
2496 idx1 := idx1 +1;
2497 END LOOP ;
2498 END IF;
2499 IF create_contact_resource%ISOPEN THEN
2500 EXIT WHEN create_contact_resource%NOTFOUND;
2501 END IF;
2502 END LOOP;
2503 IF create_contact_resource%ISOPEN THEN
2504 CLOSE create_contact_resource;
2505 END IF;
2506
2507
2508 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2509 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT ||'.SUBMIT_CONTACT_CREATION',
2510 'Populating l_ctcv_tbl_in completed');
2511 END IF;
2512
2513 -- Get all contacts that has to be updated.
2514 OPEN update_contact_resource;
2515 LOOP
2516 FETCH update_contact_resource BULK COLLECT INTO
2517 lr_cpl_id,lr_upd_cpl_org_id limit 1000;
2518 IF (lr_cpl_id.count > 0 ) THEN
2519 FOR i3 in lr_cpl_id.first .. lr_cpl_id.last
2520 LOOP
2521 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2522 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT ||'.SUBMIT_CONTACT_CREATION',
2523 'Contacts selected for update ' || lr_cpl_id(i3) );
2524 END IF;
2525 l_ctcv_tbl_in_upd(idx2).id := lr_cpl_id(i3);
2526 l_ctcv_tbl_in_upd(idx2).end_date := sysdate-1;
2527 l_ctcv_tbl_in_upd(idx2).attribute1 := lr_upd_cpl_org_id(i3);
2528 idx2 := idx2 +1;
2529 END LOOP;
2530 END IF;
2531 IF update_contact_resource%ISOPEN THEN
2532 EXIT WHEN update_contact_resource%NOTFOUND;
2533 END IF;
2534 END LOOP;
2535 IF update_contact_resource%ISOPEN THEN
2536 CLOSE update_contact_resource;
2537 END IF;
2538 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2539 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT ||'.SUBMIT_CONTACT_CREATION',
2540 'Populating l_ctcv_tbl_in_upd completed');
2541 END IF;
2542
2543 -- Get all contacts that has to be deleted.
2544 OPEN delete_contact_resource;
2545 LOOP
2546 FETCH delete_contact_resource BULK COLLECT INTO
2547 lr_del_cpl_id,lr_del_cpl_org_id limit 1000;
2548 IF (lr_del_cpl_id.count >0 ) THEN
2549 FOR i4 in lr_del_cpl_id.first .. lr_del_cpl_id.last
2550 LOOP
2551 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2552 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT ||'.SUBMIT_CONTACT_CREATION',
2553 'Contacts selected for delete ' || lr_del_cpl_id(i4));
2554 END IF;
2555 l_ctcv_tbl_in_del(i4).id := lr_del_cpl_id(i4);
2556 l_ctcv_tbl_in_del(i4).attribute1 := lr_del_cpl_org_id(i4);
2557 END LOOP;
2558 END IF;
2559 IF delete_contact_resource%ISOPEN THEN
2560 EXIT WHEN delete_contact_resource%NOTFOUND;
2561 END IF;
2562 END LOOP;
2563
2564 IF delete_contact_resource%ISOPEN THEN
2565 CLOSE delete_contact_resource;
2566 END IF;
2567
2568 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2569 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT ||'.SUBMIT_CONTACT_CREATION',
2570 'Completed populating PL/SQL tables for create, update and delete');
2571 END IF;
2572
2573 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2574 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT ||'.SUBMIT_CONTACT_CREATION',
2575 ' Number of records selected for update ' || l_ctcv_tbl_in_upd.count || ' ' ||
2576 ' Number of records selected for delete ' || l_ctcv_tbl_in_del.count || ' ' ||
2577 ' Number of records selected for create ' || l_ctcv_tbl_in.count) ;
2578 END IF;
2579
2580 IF (l_ctcv_tbl_in_upd.count > 0 ) THEN
2581 idx5 := l_ctcv_tbl_in_upd.FIRST;
2582 LOOP
2583 -- If org id is null, need to set org context for each contract.
2584 IF l_org_id IS NULL THEN
2585 OKC_CONTEXT.set_okc_org_context(l_ctcv_tbl_in_upd(idx5).attribute1,null);
2586 END IF;
2587 l_ctcv_tbl_in_upd(idx5).attribute1 := NULL;
2588 okc_contract_party_pub.update_contact ( p_api_version => l_api_version,
2589 p_init_msg_list => l_init_msg_list,
2590 x_return_status => l_return_status,
2591 x_msg_count => l_msg_count,
2592 x_msg_data => l_msg_data,
2593 p_ctcv_rec => l_ctcv_tbl_in_upd(idx5),
2594 x_ctcv_rec => l_ctcv_rec_out_upd );
2595 EXIT WHEN idx5 = l_ctcv_tbl_in_upd.LAST;
2596 idx5 := l_ctcv_tbl_in_upd.NEXT(idx5);
2597 END LOOP;
2598 END IF;
2599
2600 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2601 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2602 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT ||'.SUBMIT_CONTACT_CREATION',
2603 'Exception in update contact ');
2604 END IF;
2605 fnd_file.put_line(FND_FILE.LOG,'Exception in update contact');
2606 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2607 END IF;
2608
2609 IF (l_ctcv_tbl_in_del.count > 0 ) THEN
2610 idx6 := l_ctcv_tbl_in_del.FIRST;
2611 LOOP
2612 -- If org id is null, need to set org context for each contract.
2613 IF l_org_id IS NULL THEN
2614 OKC_CONTEXT.set_okc_org_context(l_ctcv_tbl_in_del(idx6).attribute1,null);
2615 END IF;
2616 l_ctcv_tbl_in_del(idx6).attribute1 := NULL;
2617 okc_contract_party_pub.delete_contact ( p_api_version => l_api_version,
2618 p_init_msg_list => l_init_msg_list,
2619 x_return_status => l_return_status,
2620 x_msg_count => l_msg_count,
2621 x_msg_data => l_msg_data,
2622 p_ctcv_rec => l_ctcv_tbl_in_del(idx6));
2623
2624 EXIT WHEN idx6 = l_ctcv_tbl_in_del.LAST;
2625 idx6 := l_ctcv_tbl_in_del.NEXT(idx6);
2626 END LOOP;
2627 END IF;
2628
2629 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2630 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2631 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT ||'.SUBMIT_CONTACT_CREATION',
2632 'Exception in delete contact ');
2633 END IF;
2634 fnd_file.put_line(FND_FILE.LOG,'Exception in delete contact');
2635 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2636 END IF;
2637
2638 IF (l_ctcv_tbl_in.count > 0 ) THEN
2639 idx3 := l_ctcv_tbl_in.FIRST;
2640 LOOP
2641 IF l_org_id IS NULL THEN
2642 OKC_CONTEXT.set_okc_org_context(l_ctcv_tbl_in(idx3).attribute3,null);
2643 END IF;
2644 l_ctcv_tbl_in(idx3).attribute3 := NULL;
2645 okc_contract_party_pub.create_contact (p_api_version => l_api_version,
2646 p_init_msg_list => l_init_msg_list,
2647 x_return_status => l_return_status,
2648 x_msg_count => l_msg_count,
2649 x_msg_data => l_msg_data,
2650 p_ctcv_rec => l_ctcv_tbl_in(idx3),
2651 x_ctcv_rec => l_ctcv_rec_out_ins);
2652
2653 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS ) THEN
2654 okc_cvm_pvt.g_trans_id := 'XXX';
2655 l_cvmv_rec.chr_id := l_ctcv_rec_out_ins.dnz_chr_id;
2656 OKC_CVM_PVT.update_contract_version(p_api_version => l_api_version,
2657 p_init_msg_list => l_init_msg_list,
2658 x_return_status => l_return_status,
2659 x_msg_count => l_msg_count,
2660 x_msg_data => l_msg_data,
2661 p_cvmv_rec => l_cvmv_rec,
2662 x_cvmv_rec => l_cvmv_out_rec);
2663
2664
2665 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2666 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT ||'.SUBMIT_CONTACT_CREATION',
2667 'Contract ID: ' || l_cvmv_rec.chr_id
2668 ||' Update contract version return status '
2669 || l_return_status );
2670 END IF;
2671 l_chrv_rec.id := l_ctcv_rec_out_ins.dnz_chr_id;
2672 l_chrv_rec.last_update_date := sysdate;
2673 OKC_CONTRACT_PUB.update_contract_header(p_api_version => l_api_version,
2674 p_init_msg_list => OKC_API.G_TRUE,
2675 x_return_status => l_return_status,
2676 x_msg_count => l_msg_count,
2677 x_msg_data => l_msg_data,
2678 p_restricted_update => OKC_API.G_TRUE,
2679 p_chrv_rec => l_chrv_rec,
2680 x_chrv_rec => l_chrv_out_rec);
2681
2682 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2683 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT ||'.SUBMIT_CONTACT_CREATION',
2684 'Update contract header return status '
2685 || l_return_status );
2686 END IF;
2687 FND_MESSAGE.SET_NAME('OKS','OKS_NOTIFY_SALESREP');
2688 FND_MESSAGE.SET_TOKEN(token => 'CONTRACTNUM',
2689 Value => l_ctcv_rec_out_ins.attribute2);
2690 l_salesrep_msg := FND_MESSAGE.GET;
2691 NOTIFY_SALESREP(to_number(l_ctcv_rec_out_ins.attribute1),
2692 l_ctcv_rec_out_ins.dnz_chr_id,
2693 l_ctcv_rec_out_ins.attribute2,
2694 NULL,
2695 l_salesrep_msg);
2696
2697
2698 ELSE
2699
2700 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2701 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT ||'.SUBMIT_CONTACT_CREATION',
2702 'This contract could not create vendor contact : '
2703 || l_ctcv_rec_out_ins.attribute1 || ' '
2704 || l_ctcv_rec_out_ins.attribute2 );
2705 END IF;
2706 --Assemble Data for errorneous condition
2707 NOTIFY_CONTRACT_ADMIN(l_ctcv_rec_out_ins.dnz_chr_id,
2708 NULL,
2709 NULL,
2710 GET_FND_MESSAGE );
2711 END IF;
2712 EXIT WHEN idx3 = l_ctcv_tbl_in.LAST;
2713 idx3 := l_ctcv_tbl_in.NEXT(idx3);
2714 END LOOP;
2715 END IF;
2716
2717 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2718 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT ||'.SUBMIT_CONTACT_CREATION',
2719 'Start upadate and create contact process : No of contacts : '
2720 ||l_ctcv_tbl_in.count );
2721 END IF;
2722
2723 END IF;--b_id.count > 0
2724
2725 -- Now cleanup the temp tables for next steup of records.
2726 l_ctcv_tbl_in.delete;
2727 l_ctcv_tbl_in_del.delete;
2728 l_ctcv_tbl_in_upd.delete;
2729
2730 DELETE FROM oks_k_res_temp;
2731 DELETE FROM oks_jtf_res_temp;
2732
2733 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2734 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT ||'.SUBMIT_CONTACT_CREATION',
2735 'Completed Processing *****');
2736 END IF;
2737
2738 IF GET_ALL_CONTRACTS_1%ISOPEN THEN
2739 EXIT WHEN GET_ALL_CONTRACTS_1%NOTFOUND;
2740 ELSIF GET_ALL_CONTRACTS_2%ISOPEN THEN
2741 EXIT WHEN GET_ALL_CONTRACTS_2%NOTFOUND;
2742 ELSIF GET_ALL_CONTRACTS_3%ISOPEN THEN
2743 EXIT WHEN GET_ALL_CONTRACTS_3%NOTFOUND;
2744 ELSIF GET_ALL_CONTRACTS_4%ISOPEN THEN
2745 EXIT WHEN GET_ALL_CONTRACTS_4%NOTFOUND;
2746 ELSIF GET_ALL_CONTRACTS_5%ISOPEN THEN
2747 EXIT WHEN GET_ALL_CONTRACTS_5%NOTFOUND;
2748 ELSIF GET_ALL_CONTRACTS_6%ISOPEN THEN
2749 EXIT WHEN GET_ALL_CONTRACTS_6%NOTFOUND;
2750 ELSIF GET_ALL_CONTRACTS_7%ISOPEN THEN
2751 EXIT WHEN GET_ALL_CONTRACTS_7%NOTFOUND;
2752 ELSIF GET_ALL_CONTRACTS_8%ISOPEN THEN
2753 EXIT WHEN GET_ALL_CONTRACTS_8%NOTFOUND;
2754 ELSIF GET_ALL_CONTRACTS_9%ISOPEN THEN
2755 EXIT WHEN GET_ALL_CONTRACTS_9%NOTFOUND;
2756 ELSIF GET_ALL_CONTRACTS_10%ISOPEN THEN
2757 EXIT WHEN GET_ALL_CONTRACTS_10%NOTFOUND;
2758 ELSIF GET_ALL_CONTRACTS_11%ISOPEN THEN
2759 EXIT WHEN GET_ALL_CONTRACTS_11%NOTFOUND;
2760 ELSIF GET_ALL_CONTRACTS_12%ISOPEN THEN
2761 EXIT WHEN GET_ALL_CONTRACTS_12%NOTFOUND;
2762 ELSIF GET_ALL_CONTRACTS_13%ISOPEN THEN
2763 EXIT WHEN GET_ALL_CONTRACTS_13%NOTFOUND;
2764 ELSIF GET_ALL_CONTRACTS_14%ISOPEN THEN
2765 EXIT WHEN GET_ALL_CONTRACTS_14%NOTFOUND;
2766 ELSIF GET_ALL_CONTRACTS_15%ISOPEN THEN
2767 EXIT WHEN GET_ALL_CONTRACTS_15%NOTFOUND;
2768 ELSIF GET_ALL_CONTRACTS_16%ISOPEN THEN
2769 EXIT WHEN GET_ALL_CONTRACTS_16%NOTFOUND;
2770
2771 END IF;
2772 END LOOP;
2773
2774 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2775 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT ||'.SUBMIT_CONTACT_CREATION',
2776 'Out of Main Loop');
2777 END IF;
2778
2779 IF GET_ALL_CONTRACTS_1%ISOPEN THEN
2780 CLOSE GET_ALL_CONTRACTS_1;
2781 ELSIF GET_ALL_CONTRACTS_2%ISOPEN THEN
2782 CLOSE GET_ALL_CONTRACTS_2;
2783 ELSIF GET_ALL_CONTRACTS_3%ISOPEN THEN
2784 CLOSE GET_ALL_CONTRACTS_3;
2785 ELSIF GET_ALL_CONTRACTS_4%ISOPEN THEN
2786 CLOSE GET_ALL_CONTRACTS_4;
2787 ELSIF GET_ALL_CONTRACTS_5%ISOPEN THEN
2788 CLOSE GET_ALL_CONTRACTS_5;
2789 ELSIF GET_ALL_CONTRACTS_6%ISOPEN THEN
2790 CLOSE GET_ALL_CONTRACTS_6;
2791 ELSIF GET_ALL_CONTRACTS_7%ISOPEN THEN
2792 CLOSE GET_ALL_CONTRACTS_7;
2793 ELSIF GET_ALL_CONTRACTS_8%ISOPEN THEN
2794 CLOSE GET_ALL_CONTRACTS_8;
2795 ELSIF GET_ALL_CONTRACTS_9%ISOPEN THEN
2796 CLOSE GET_ALL_CONTRACTS_9;
2797 ELSIF GET_ALL_CONTRACTS_10%ISOPEN THEN
2798 CLOSE GET_ALL_CONTRACTS_10;
2799 ELSIF GET_ALL_CONTRACTS_11%ISOPEN THEN
2800 CLOSE GET_ALL_CONTRACTS_11;
2801 ELSIF GET_ALL_CONTRACTS_12%ISOPEN THEN
2802 CLOSE GET_ALL_CONTRACTS_12;
2803 ELSIF GET_ALL_CONTRACTS_13%ISOPEN THEN
2804 CLOSE GET_ALL_CONTRACTS_13;
2805 ELSIF GET_ALL_CONTRACTS_14%ISOPEN THEN
2806 CLOSE GET_ALL_CONTRACTS_14;
2807 ELSIF GET_ALL_CONTRACTS_15%ISOPEN THEN
2808 CLOSE GET_ALL_CONTRACTS_15;
2809 ELSIF GET_ALL_CONTRACTS_16%ISOPEN THEN
2810 CLOSE GET_ALL_CONTRACTS_16;
2811 END IF;
2812
2813 END IF; --CRO_CODE
2814 END IF; --Use JTF
2815 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2816 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT ||'.SUBMIT_CONTACT_CREATION',
2817 'Reassugn End Time ' ||
2818 to_char(sysdate,'HH:MI:SS') );
2819 END IF;
2820 fnd_file.put_line(FND_FILE.LOG,'Program completed successfully ' || to_char(sysdate,'HH:MI:SS'));
2821 EXCEPTION
2822
2823 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2824 fnd_file.put_line(FND_FILE.LOG,'Exception occured');
2825 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2826 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT ||'.SUBMIT_CONTACT_CREATION',
2827 'Error occured : ' || get_fnd_message() );
2828 END IF;
2829 rollback;
2830 RAISE_APPLICATION_ERROR(-20001, 'Exception occured');
2831 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2832 fnd_file.put_line(FND_FILE.LOG,'Exception occured');
2833 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2834 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT ||'.SUBMIT_CONTACT_CREATION',
2835 'Unexpected Error : ' || get_fnd_message() );
2836 END IF;
2837 rollback;
2838 RAISE_APPLICATION_ERROR(-20001, 'Exception occured');
2839 WHEN OTHERS THEN
2840 fnd_file.put_line(FND_FILE.LOG,'Exception occured');
2841 OKC_API.set_message(p_app_name => g_app_name,
2842 p_msg_name => g_unexpected_error,
2843 p_token1 => g_sqlcode_token,
2844 p_token1_value => sqlcode,
2845 p_token2 => g_sqlerrm_token,
2846 p_token2_value => sqlerrm);
2847
2848 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2849 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT ||'.SUBMIT_CONTACT_CREATION',
2850 'Other Exception occured ' || sqlerrm);
2851 END IF;
2852 rollback;
2853 RAISE_APPLICATION_ERROR(-20001, 'Exception occured: other exception');
2854
2855 END SUBMIT_CONTACT_CREATION;
2856
2857
2858 END OKS_EXTWAR_UTIL_PUB;
2859