1 PACKAGE BODY OKC_OPPORTUNITY_PVT AS
2 /* $Header: OKCROPPB.pls 120.0 2005/05/25 19:31:13 appldev noship $ */
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5
6 g_sales_rep Number;
7 g_rty_code okc_k_rel_objs.rty_code%TYPE;
8 g_opp_h_created Boolean;
9 --
10 PROCEDURE CREATE_OPPORTUNITY(p_api_version IN NUMBER,
11 p_context IN VARCHAR2,
12 p_contract_id IN NUMBER,
13 p_win_probability IN NUMBER,
14 p_expected_close_days IN NUMBER,
15 x_lead_id OUT NOCOPY NUMBER,
16 p_init_msg_list IN VARCHAR2,
17 x_msg_data OUT NOCOPY VARCHAR2,
18 x_msg_count OUT NOCOPY NUMBER,
19 x_return_status OUT NOCOPY VARCHAR2) IS
20 l_return_status Varchar2(1);
21 l_lead_id Number;
22 l_msg_count NUMBER;
23 l_msg_data VARCHAR2(2000);
24
25 Begin
26 IF (l_debug = 'Y') THEN
27 okc_debug.Set_Indentation('Create_Opportunity');
28 okc_debug.log('1000: Entering okc_opportunity_pvt.create_opportunity', 2);
29 END IF;
30 x_return_status := okc_api.g_ret_sts_success;
31 --
32 IF (l_debug = 'Y') THEN
33 okc_debug.log('1010: Before is_opp_creation_allowed');
34 END IF;
35 Is_Opp_Creation_Allowed(p_context,
36 p_contract_id,
37 l_return_status);
38 If l_return_status <> okc_api.g_ret_sts_success Then
39 Raise g_exception_halt_validation;
40 End If;
41 --
42 IF (l_debug = 'Y') THEN
43 okc_debug.log('1015: Setting Contract Context');
44 END IF;
45 okc_context.set_okc_org_context(p_chr_id => p_contract_id);
46 IF (l_debug = 'Y') THEN
47 okc_debug.log('1020: Before create_opp_header');
48 END IF;
49 Create_Opp_Header(p_api_version,
50 p_context,
51 p_contract_id,
52 p_win_probability,
53 p_expected_close_days,
54 l_lead_id,
55 p_init_msg_list,
56 l_msg_data,
57 l_msg_count,
58 l_return_status);
59 If l_return_status <> okc_api.g_ret_sts_success Then
60 IF (l_debug = 'Y') THEN
61 okc_debug.log('1030: Opp Header Creation Return Status - ' || l_return_status);
62 END IF;
63 Raise g_exception_halt_validation;
64 End If;
65 IF (l_debug = 'Y') THEN
66 okc_debug.log('1040: Opportunity Lead Id - ' || to_char(l_lead_id));
67 END IF;
68 --
69 IF (l_debug = 'Y') THEN
70 okc_debug.log('1050: Before create_opp_lines');
71 END IF;
72 Create_Opp_Lines(p_api_version,
73 p_context,
74 p_contract_id,
75 l_lead_id,
76 p_init_msg_list,
77 l_msg_data,
78 l_msg_count,
79 l_return_status);
80 IF (l_debug = 'Y') THEN
81 okc_debug.log('1060: After create_opp_lines');
82 END IF;
83 If l_return_status <> okc_api.g_ret_sts_success Then
84 IF (l_debug = 'Y') THEN
85 okc_debug.log('1060: Opp Lines Creation Return Status - ' || l_return_status);
86 END IF;
87 Raise g_exception_halt_validation;
88 End If;
89 --
90
91 x_lead_id := l_lead_id;
92 IF (l_debug = 'Y') THEN
93 okc_debug.log('2000: Exiting okc_opportunity_pvt.create_opportunity', 2);
94 okc_debug.Reset_Indentation;
95 END IF;
96 Exception
97 When g_exception_halt_validation Then
98 x_return_status := l_return_status;
99 IF (l_debug = 'Y') THEN
100 okc_debug.log('1970: Exiting okc_opportunity_pvt.create_opportunity', 2);
101 okc_debug.Reset_Indentation;
102 END IF;
103 When Others Then
104 okc_api.Set_Message(p_app_name => g_app_name,
105 p_msg_name => g_unexpected_error,
106 p_token1 => g_sqlcode_token,
107 p_token1_value => sqlcode,
108 p_token2 => g_sqlerrm_token,
109 p_token2_value => sqlerrm);
110 x_return_status := okc_api.g_ret_sts_unexp_error;
111 IF (l_debug = 'Y') THEN
112 okc_debug.log('1990: Exiting okc_opportunity_pvt.create_opportunity', 2);
113 okc_debug.Reset_Indentation;
114 END IF;
115 End Create_Opportunity;
116
117 PROCEDURE CREATE_OPP_HEADER(p_api_version IN NUMBER,
118 p_context IN VARCHAR2,
119 p_contract_id IN NUMBER,
120 p_win_probability IN NUMBER,
121 p_expected_close_days IN NUMBER,
122 x_lead_id OUT NOCOPY NUMBER,
123 p_init_msg_list IN VARCHAR2,
124 x_msg_data OUT NOCOPY VARCHAR2,
125 x_msg_count OUT NOCOPY NUMBER,
126 x_return_status OUT NOCOPY VARCHAR2) IS
127 cursor c1 is
128 select h.contract_number,
129 h.contract_number_modifier,
130 h.description,
131 h.estimated_amount,
132 h.estimated_amount_renewed,
133 h.currency_code,
134 h.authoring_org_id,
135 h.orig_system_source_code,
136 h.orig_system_id1,
137 rel.object1_id1 lead_id
138 from okc_k_headers_v h,
139 okc_k_rel_objs rel
140 where h.id = p_contract_id
141 and rel.chr_id(+) = h.orig_system_id1
142 and rel.rty_code(+) = 'OPPEXPSCONTRACT'
143 and rel.jtot_object1_code(+) = 'OKX_OPPHEAD';
144 --
145 cursor c2(p_rle_code okc_k_party_roles_b.rle_code%TYPE) is
146 select object1_id1
147 from okc_k_party_roles_b
148 where dnz_chr_id = p_contract_id
149 and cle_id is null
150 and rle_code = p_rle_code;
151 --
152 cursor c3 (p_object_code in okc_contacts.jtot_object1_code%TYPE) is
153 select resource_id
154 from jtf_rs_salesreps
155 where salesrep_id in (select object1_id1
156 from okc_contacts
157 where dnz_chr_id = p_contract_id
158 and jtot_object1_code = p_object_code);
159 --
160 /* cursor c4 is
161 select sales_group_id
162 from as_fc_salesforce_v
163 where salesforce_id = (select resource_id
164 from jtf_rs_salesreps
165 where salesrep_id = g_sales_rep); */
166 --
167 cursor c5(p_rule_information_category IN
168 okc_rules_b.rule_information_category%TYPE) is
169 select rule_information1,
170 rule_information2,
171 rule_information3,
172 rule_information4,
173 rule_information5,
174 jtot_object1_code,
175 object1_id1
176 from okc_rules_b
177 where dnz_chr_id = p_contract_id
178 and rule_information_category = p_rule_information_category;
179 --
180
181 cursor c6 (b_id1 NUMBER, p_use_code VARCHAR2) IS
182 SELECT party_site_id
183 FROM okx_cust_site_uses_v
184 WHERE id1 = b_id1
185 AND site_use_code = p_use_code -- ship..to_party_site_id
186 AND status = 'A' -- Active Status
187 AND nvl(ORG_ID,-99) = SYS_CONTEXT('OKC_CONTEXT', 'ORG_ID');
188
189
190 cursor c7 (b_win_probability NUMBER) is
191 select sales_stage_id
192 ---name, min_win_probability, max_win_probability,
193 ---min_win_probability || ' - ' || max_win_probability probability_range,
194 ---description
195 from OKX_OPP_SALES_STAGES_V ---as_sales_stages_all_vl
196 where enabled_flag = 'Y' and
197 ( ( (sysdate > start_date_active) and (end_date_active is null) ) or
198 (sysdate between start_date_active and end_date_active) )
199 and b_win_probability between min_win_probability and max_win_probability;
200 ---order by min_win_probability, max_win_probability;
201
202 c1_rec c1%ROWTYPE;
203 c5_rec c5%ROWTYPE;
204 c51_rec c5%ROWTYPE;
205 c6_rec c6%ROWTYPE;
206
207 c7_rec c7%ROWTYPE;
208
209 l_header_rec as_opportunity_pub.header_rec_type;
210 l_in_crjv_tbl okc_k_rel_objs_pub.crjv_tbl_type;
211 l_out_crjv_tbl okc_k_rel_objs_pub.crjv_tbl_type;
212 l_return_status Varchar2(1);
213 l_msg_count Number;
214 l_msg_data Varchar2(255);
215 l_lead_id Number;
216 l_win_probability Number := p_win_probability;
217 l_expected_close_days Number := p_expected_close_days;
218 l_customer_id Number;
219 l_group_id Number;
220
221 l_sales_stage_id NUMBER;
222 l_party_site_id NUMBER;
223
224 l_note_id Number;
225 l_cr_note VARCHAR2(30) := 'OKC_OPP_CREATED_FROM_K';
226 Note_Message VARCHAR2(2000); -- Bug : 2589898 ENHANCED TO VARCHAR2(2000) FROM VARCHAR2(300)
227
228 Begin
229 IF (l_debug = 'Y') THEN
230 okc_debug.Set_Indentation('Create_Opp_Header');
231 okc_debug.log('3000: Entering okc_opportunity_pvt.create_opp_header', 2);
232 END IF;
233 x_return_status := okc_api.g_ret_sts_success;
234 --
235 IF (l_debug = 'Y') THEN
236 okc_debug.log('3010: Before Get_Opp_Defaults');
237 END IF;
238 Get_Opp_Defaults(p_context,
239 p_contract_id,
240 l_win_probability,
241 l_expected_close_days,
242 l_return_status);
243 If l_return_status <> okc_api.g_ret_sts_success Then
244 IF (l_debug = 'Y') THEN
245 okc_debug.log('3019: Get_Opp_Defaults Return Status - ' || l_return_status);
246 END IF;
247 Raise g_exception_halt_validation;
248 End If;
249 -- Get Contract's details
250 Open c1;
251 Fetch c1 into c1_rec;
252 Close c1;
253
254 -- If contract is renewed and there's EXPIRE opportunity for pre-renewed
255 -- contract: we should just link the new contract to the opportunity
256 IF p_context = 'RENEW' and c1_rec.lead_id IS NOT NULL and c1_rec.orig_system_source_code='OKC_HDR' THEN
257 IF (l_debug = 'Y') THEN
258 okc_debug.log('3020: The contract is renewed from contract id #' || c1_rec.orig_system_id1 );
259 okc_debug.log('3021: There is opportunity #' || c1_rec.lead_id || ' for pre-renewed contract' );
260 okc_debug.log('3022: It will be reused (linked) for the new (renewed) contract' );
261 END IF;
262 l_lead_id := c1_rec.lead_id ;
263 g_opp_h_created := FALSE; -- is used to prevent old header removing
264 l_cr_note := 'OKC_OPP_LINKED_TO_K';
265 ELSE
266 g_opp_h_created := TRUE;
267
268 -- Get Customer ID
269 Open c2('CUSTOMER');
270 Fetch c2 Into l_customer_id;
271 Close c2;
272 IF (l_debug = 'Y') THEN
273 okc_debug.log('3030: Customer ID - ' || To_Char(l_customer_id));
274 END IF;
275 -- Get Sales Rep ID, store it in a global so that it can be used
276 -- later for opp lines creation
277 g_sales_rep := Null;
278 Open c3('OKX_SALEPERS');
279 Fetch c3 Into g_sales_rep;
280 Close c3;
281 IF (l_debug = 'Y') THEN
282 okc_debug.log('3040: Sales Rep ID - ' || To_Char(g_sales_rep));
283 END IF;
284 --
285 /* Open c4;
286 Fetch c4 Into l_group_id;
287 Close c4; */
288 -- Get the price list ID
289 Open c5('PRE');
290 Fetch c5 Into c5_rec;
291 Close c5;
292
293
294 --TEMP okc_util.init_trace();
295 IF nvl(fnd_profile.value('AS_OPP_ADDRESS_REQUIRED'), okc_api.g_miss_char) = 'Y' THEN
296
297 Open c5('STO'); --get the site_use_id of the ship_to_party_site_id
298 Fetch c5 Into c51_rec;
299 IF c5%NOTFOUND OR c51_rec.object1_id1 IS NULL THEN
300 okc_api.Set_Message(p_app_name => g_app_name,
301 p_msg_name => 'OKC_OPP_NO_SHIP_ADDRESS'
302 );
303 l_return_status := OKC_API.G_RET_STS_ERROR;
304 raise g_exception_halt_validation;
305 END IF;
306 Open c6(c51_rec.object1_id1,'SHIP_TO'); --get the party_site_id
307 Fetch c6 Into c6_rec;
308 IF c6%NOTFOUND OR c6_rec.party_site_id IS NULL THEN
309 okc_api.Set_Message(p_app_name => g_app_name,
310 p_msg_name => 'OKC_OPP_NO_PARTY_SITE_ID',
311 p_token1 => 'SITE_USE_ID',
312 p_token1_value => c51_rec.object1_id1
313 );
314 l_return_status := OKC_API.G_RET_STS_ERROR;
315 raise g_exception_halt_validation;
316 END IF;
317 l_party_site_id := nvl(to_number(c6_rec.party_site_id), okc_api.g_miss_num);
318 Close c5;
319 Close c6;
320
321 END IF;
322
323
324 -- Continue preparing the opp header record for the api call
325
326 IF c1_rec.contract_number_modifier IS NULL THEN -- ???
327 l_header_rec.description := c1_rec.contract_number;
328 ELSE
329 l_header_rec.description := c1_rec.contract_number || ' ' ||
330 c1_rec.contract_number_modifier;
331 END IF;
332
333 IF p_context = 'RENEW' THEN
334 --Bug 2033933 KTST1156: OPPORTUNITIES FROM K RENEWALS DON'T HAVE AN AMOUNT
335 l_header_rec.total_amount := c1_rec.estimated_amount_renewed;
336 ELSE
337 --proceed as usual
338 l_header_rec.total_amount := c1_rec.estimated_amount;
339 END IF;
340 --NOTE! Bug 2050044: We do not now consider the estimated amount as the total_amount because
341 -- the total_amount is updated with the sum of the negotiated amounts of the
342 -- contract lines later. See history entry for 23-OCT-2001
343 -- (see l_header_rec.total_amount := l_updt_hdr_tot_amt; in CREATE_OPP_LINES)
344
345
346
347 l_header_rec.currency_code := c1_rec.currency_code;
348 l_header_rec.org_id := c1_rec.authoring_org_id;
349
350 IF p_context = 'EXPIRE' THEN
351 --Bug 2034318
352 --Action assembler defaults the win probability parameter to 50 when calling
353 --opportunity. This value may not be valid so we are defaulting it from profile option
354 l_header_rec.win_probability := NVL(to_number(Fnd_Profile.Value('AS_OPP_WIN_PROBABILITY')), OKC_API.G_MISS_NUM);
355 ELSE
356 --proceed as usual
357 l_header_rec.win_probability := l_win_probability;
358 END IF;
359
360
361 l_header_rec.decision_date := sysdate + nvl(l_expected_close_days, 0);
362 l_header_rec.customer_id := l_customer_id;
363 l_header_rec.price_list_id := c5_rec.object1_id1;
364
365 --TEMP okc_util.print_trace(9,'Opportunity name i.e. description: ' || l_header_rec.description);
366 IF (l_debug = 'Y') THEN
367 okc_debug.log('Opportunity name i.e. description: ' || l_header_rec.description);
368 END IF;
369
370 begin --get sales_stage_id
371 OPEN c7(l_win_probability); --get sales_stage_id
372 FETCH c7 INTO l_sales_stage_id;
373 CLOSE c7;
374 l_header_rec.sales_stage_id := NVL(l_sales_stage_id, OKC_API.G_MISS_NUM);
375 --TEMP okc_util.print_trace(9,'l_sales_stage_id : ' || l_sales_stage_id);
376 IF (l_debug = 'Y') THEN
377 okc_debug.log('l_sales_stage_id : ' || TO_CHAR(l_sales_stage_id));
378 END IF;
379 exception
380 when others then
381 null;
382 end;
383
384 --get value for AMS_P_SOURCE_CODES_V.SOURCE_CODE_ID
385 IF nvl(TO_NUMBER(fnd_profile.value('OKC_DEFAULT_OPP_CODE')), okc_api.g_miss_num) = OKC_API.G_MISS_NUM
386 AND
387 nvl(fnd_profile.value('AS_OPP_SOURCE_CODE_REQUIRED'), okc_api.g_miss_char) = 'Y'
388 THEN
389 okc_api.Set_Message(p_app_name => g_app_name,
390 p_msg_name => 'OKC_OPP_NO_DEFLT_OPP_CODE'
391 );
392 l_return_status := OKC_API.G_RET_STS_ERROR;
393 raise g_exception_halt_validation;
394 END IF;
395 l_header_rec.source_promotion_id := nvl(TO_NUMBER(fnd_profile.value('OKC_DEFAULT_OPP_CODE')), okc_api.g_miss_num);
396 --TEMP okc_util.print_trace(9,'l_header_rec.source_promotion_id: ' || l_header_rec.source_promotion_id);
397 IF (l_debug = 'Y') THEN
398 okc_debug.log('l_header_rec.source_promotion_id: ' || TO_CHAR(l_header_rec.source_promotion_id));
399 END IF;
400
401 l_header_rec.address_id := nvl(to_number(l_party_site_id), okc_api.g_miss_num);
402 --TEMP okc_util.print_trace(9,'l_header_rec.address_id: ' || l_header_rec.address_id);
403 IF (l_debug = 'Y') THEN
404 okc_debug.log('l_header_rec.address_id: ' || TO_CHAR(l_header_rec.address_id));
405 END IF;
406
407 IF (l_debug = 'Y') THEN
408 okc_debug.log('3500: Before as_opportunity_pub.create_opp_header');
409 END IF;
410 -- Finally call the opp header api
411 AS_OPPORTUNITY_PUB.Create_Opp_Header(
412 p_api_version_number => 2.0, --p_api_version,---2.0,
413 p_init_msg_list => p_init_msg_list, --fnd_api.g_false,
414 p_commit => fnd_api.g_false,
415 p_validation_level => fnd_api.g_valid_level_full,
416 p_header_rec => l_header_rec,
417 p_check_access_flag => 'Y',
418 p_admin_flag => 'N',
419 p_admin_group_id => Null,
420 p_identity_salesforce_id => g_sales_rep,
421 -- p_salesgroup_id => l_group_id,
422 p_salesgroup_id => Null,
423 p_partner_cont_party_id => Null,
424 p_profile_tbl => as_utility_pub.g_miss_profile_tbl,
425 x_return_status => l_return_status,
426 x_msg_count => l_msg_count,
427 x_msg_data => l_msg_data,
428 x_lead_id => l_lead_id);
429 --
430 --Note: final commit is done in OKC_OPPORUNTITY_PUB.create_opportunity!
431 IF (l_debug = 'Y') THEN
432 okc_debug.log('3510: After as_opportunity_pub.create_opp_header');
433 END IF;
434 --TEMP okc_util.print_trace(9, 'x_lead_id returned: ' || l_lead_id);
435 IF (l_debug = 'Y') THEN
436 okc_debug.log('x_lead_id returned: ' || TO_CHAR(l_lead_id));
437 END IF;
438 If l_return_status <> okc_api.g_ret_sts_success Then
439 IF (l_debug = 'Y') THEN
440 okc_debug.log('3520: Return Status from Opp header Creation - ' || l_return_status);
441 END IF;
442 Raise g_exception_halt_validation;
443 End If;
444
445 END IF; -- IF p_context = 'RENEW' and c1_rec.lead_id IS NOT NULL and c1_rec.orig_system_source_code='OKC_HDR' THEN
446
447 -- Populate the rel object table to maintain the relationship
448 -- between the contract and the opportunity header just created
449 l_in_crjv_tbl(1).object_version_number := 1;
450 l_in_crjv_tbl(1).chr_id := p_contract_id;
451 l_in_crjv_tbl(1).jtot_object1_code := 'OKX_OPPHEAD';
452 l_in_crjv_tbl(1).object1_id1 := l_lead_id;
453 l_in_crjv_tbl(1).object1_id2 := '#';
454 l_in_crjv_tbl(1).rty_code := g_rty_code;
455 IF (l_debug = 'Y') THEN
456 okc_debug.log('3530: Before creating relation objects');
457 END IF;
458 -- Call the rel object api
459 okc_k_rel_objs_pub.Create_Row(
460 p_api_version => 1.0, --p_api_version,--1.0,
461 p_init_msg_list => p_init_msg_list, --okc_api.g_false,
462 x_return_status => l_return_status,
463 x_msg_count => l_msg_count,
464 x_msg_data => l_msg_data,
465 p_crjv_tbl => l_in_crjv_tbl,
466 x_crjv_tbl => l_out_crjv_tbl);
467 IF (l_debug = 'Y') THEN
468 okc_debug.log('3540: After creating relation objects');
469 END IF;
470 If l_return_status <> okc_api.g_ret_sts_success Then
471 IF (l_debug = 'Y') THEN
472 okc_debug.log('3550: Return Status from Rel Objects Creation - ' || l_return_status);
473 END IF;
474 Raise g_exception_halt_validation;
475 End If;
476
477 FND_MESSAGE.Set_Name('OKC', l_cr_note);
478 FND_MESSAGE.Set_Token('KNUMBER', c1_rec.contract_number , FALSE);
479 FND_MESSAGE.Set_Token('KMODIFIER', c1_rec.contract_number_modifier, FALSE);
480 FND_MESSAGE.Set_Token('CONTEXT', p_context, FALSE);
481 Note_Message := FND_MESSAGE.Get;
482
483 IF (l_debug = 'Y') THEN
484 okc_debug.log('3560: note creation:'|| Note_Message);
485 END IF;
486
487 JTF_NOTES_PUB.Create_note (
488 p_api_version => 1.0,
489 p_init_msg_list => FND_API.G_FALSE,
490 p_commit => FND_API.G_FALSE,
491 x_return_status => l_return_status,
492 x_msg_count => l_msg_count,
493 x_msg_data => l_msg_data,
494 p_source_object_id => l_lead_id,
495 p_source_object_code => 'OPPORTUNITY',
496 p_notes => Note_Message,
497 p_note_status => 'E',
498 p_note_type => 'AS_SYSTEM',
499 p_entered_by => FND_GLOBAL.USER_ID,
500 p_entered_date => SYSDATE,
501 x_jtf_note_id => l_note_id,
502 p_last_update_date => SYSDATE,
503 p_last_updated_by => FND_GLOBAL.USER_ID,
504 p_creation_date => SYSDATE,
505 p_created_by => FND_GLOBAL.USER_ID,
506 p_last_update_login => FND_GLOBAL.LOGIN_ID
507 );
508
509 IF (l_debug = 'Y') THEN
510 okc_debug.log('3570: After creating note');
511 END IF;
512 If l_return_status <> okc_api.g_ret_sts_success Then
513 IF (l_debug = 'Y') THEN
514 okc_debug.log('3580: Return Status from Note Creation - ' || l_return_status);
515 END IF;
516 Raise g_exception_halt_validation;
517 End If;
518
519 -- Pass the header opp id back to the caller
520 x_lead_id := l_lead_id;
521
522 x_msg_data := l_msg_data;
523 x_msg_count := l_msg_count;
524
525 IF (l_debug = 'Y') THEN
526 okc_debug.log('4000: Exiting okc_opportunity_pvt.create_opp_header', 2);
527 okc_debug.Reset_Indentation;
528 END IF;
529 Exception
530 When g_exception_halt_validation Then
531 x_return_status := l_return_status;
532 IF (l_debug = 'Y') THEN
533 okc_debug.log('3980: Exiting okc_opportunity_pvt.create_opp_header', 2);
534 okc_debug.Reset_Indentation;
535 END IF;
536 When Others Then
537 okc_api.Set_Message(p_app_name => g_app_name,
538 p_msg_name => g_unexpected_error,
539 p_token1 => g_sqlcode_token,
540 p_token1_value => sqlcode,
541 p_token2 => g_sqlerrm_token,
542 p_token2_value => sqlerrm);
543 x_return_status := okc_api.g_ret_sts_unexp_error;
544 IF (l_debug = 'Y') THEN
545 okc_debug.log('3990: Exiting okc_opportunity_pvt.create_opp_header', 2);
546 okc_debug.Reset_Indentation;
547 END IF;
548 End Create_Opp_Header;
549 --
550 PROCEDURE CREATE_OPP_LINES(p_api_version IN NUMBER,
551 p_context IN VARCHAR2,
552 p_contract_id IN NUMBER,
553 p_lead_id IN NUMBER,
554 p_init_msg_list IN VARCHAR2,
555 x_msg_data OUT NOCOPY VARCHAR2,
556 x_msg_count OUT NOCOPY NUMBER,
557 x_return_status OUT NOCOPY VARCHAR2) IS
558 -- pl/sql tables for bulk fetch
559 Type id_tbl Is Table of okc_k_lines_b.id%Type
560 Index By Binary_Integer;
561 /* Type price_unit_tbl Is Table of okc_k_lines_b.price_unit%Type
562 Index By Binary_Integer;
563 Type price_negotiated_tbl Is Table of okc_k_lines_b.price_negotiated%Type
564 Index By Binary_Integer;
565 Type currency_code_tbl Is Table of okc_k_lines_b.currency_code%Type
566 Index By Binary_Integer;*/
567 Type Lead_Line_Id_tbl Is Table of NUMBER
568 Index By Binary_Integer;
569 --
570 /* l_id_tbl id_tbl;
571 l_cle_id_tbl id_tbl;
572 l_price_unit_tbl price_unit_tbl;
573 l_price_negotiated_tbl price_negotiated_tbl;
574 l_currency_code_tbl currency_code_tbl;*/
575 l_object1_id1 okc_k_items.object1_id1%Type;
576 l_object1_id2 okc_k_items.object1_id2%Type;
577 l_uom_code okc_k_items.uom_code%Type;
578 l_number_of_items okc_k_items.number_of_items%Type;
579 l_rel_id_tbl id_tbl;
580 l_rel_lead_line_id_tbl Lead_Line_Id_tbl;
581 --
582 l_header_rec as_opportunity_pub.header_rec_type;
583 l_line_tbl as_opportunity_pub.line_tbl_type;
584 l_line_out_tbl as_opportunity_pub.line_out_tbl_type;
585 l_in_crjv_tbl okc_k_rel_objs_pub.crjv_tbl_type;
586 l_out_crjv_tbl okc_k_rel_objs_pub.crjv_tbl_type;
587 --
588 l_org_id Number;
589 l_count Number;
590 l_row_notfound Boolean;
591 l_return_status Varchar2(1);
592 l_msg_count Number;
593 l_msg_data Varchar2(255);
594 l_index Number;
595 l_rel_index Number;
596 -- next lines added as bug#2205445 fix
597 l_interest_type_id Number;
598 l_primary_interest_code_id Number;
599 l_secondary_interest_code_id Number;
600 --
601
602
603 l_updt_hdr_tot_amt Number;
604 l_lead_id Number;
605 /*
606 cursor line_csr_sum_amt is
607 select SUM(DECODE(p_context, 'RENEW', cle.price_negotiated_renewed, cle.price_negotiated))
608 from okc_k_lines_b cle
609 where level = 1
610 start with cle.id in (select cle2.id
611 from okc_k_lines_b cle2,
612 okc_k_items itm,
613 okc_statuses_b sts,
614 jtf_object_usages jou
615 where cle2.dnz_chr_id = p_contract_id
616 and cle2.date_renewed is null
617 and cle2.sts_code = sts.code
618 and itm.cle_id = cle.id
619 -- and itm.jtot_object1_code = 'OKX_LICPROD'
620 and itm.jtot_object1_code = jou.object_code
621 and jou.object_user_code = 'OKX_MTL_SYSTEM_ITEM'
622 and sts.ste_code <> 'TERMINATED'
623 and ((p_context = 'EXPIRE'
624 and sts.ste_code in ('ACTIVE', 'SIGNED'))
625 or (p_context in ('AUTHORING', 'RENEW')
626 and sts.ste_code = 'ENTERED'))
627 and not exists (select 'x'
628 from okc_k_rel_objs rel
629 where (rel.cle_id = cle2.id
630 or rel.cle_id = cle2.cle_id)
631 and rel.rty_code = g_rty_code))
632 connect by prior cle.id = cle.cle_id;
633 */
634 -- The new cursor to select top lines which should be creates
635 cursor line_csr is
636 select cle.id,
637 cle.cle_id,
638 cle.price_unit,
639 cle.price_negotiated,
640 cle.currency_code,
641 cle.orig_system_source_code,
642 cle.orig_system_id1,
643 itm.object1_id1,
644 itm.object1_id2,
645 itm.uom_code,
646 itm.number_of_items
647 from okc_k_lines_b cle,
648 okc_k_items itm,
649 okc_statuses_b sts,
650 jtf_object_usages jou
651 where cle.chr_id = p_contract_id
652 and cle.date_renewed is null
653 and cle.sts_code = sts.code
654 and itm.cle_id = cle.id
655 -- and itm.jtot_object1_code = 'OKX_LICPROD'
656 and itm.jtot_object1_code = jou.object_code
657 and jou.object_user_code = 'OKX_MTL_SYSTEM_ITEM'
658 and sts.ste_code <> 'TERMINATED'
659 and ((p_context = 'EXPIRE' and sts.ste_code in ('ACTIVE', 'SIGNED'))
660 or (p_context in ('AUTHORING', 'RENEW') and sts.ste_code = 'ENTERED'))
661 and not exists (select 'x' from okc_k_rel_objs rel
662 where (rel.cle_id = cle.id
663 or rel.cle_id = cle.cle_id)
664 and rel.rty_code = g_rty_code)
665 ;
666 -- The old one
667 /* cursor line_csr is
668 select cle.id,
669 cle.cle_id,
670 cle.price_unit,
671 cle.price_negotiated,
672 cle.currency_code
673 from okc_k_lines_b cle
674 where level = 1
675 start with cle.id in (select cle2.id
676 from okc_k_lines_b cle2,
677 okc_k_items itm,
678 okc_statuses_b sts,
679 jtf_object_usages jou
680 where cle2.dnz_chr_id = p_contract_id
681 and cle2.date_renewed is null
682 and cle2.sts_code = sts.code
683 and itm.cle_id = cle.id
684 -- and itm.jtot_object1_code = 'OKX_LICPROD'
685 and itm.jtot_object1_code = jou.object_code
686 and jou.object_user_code = 'OKX_MTL_SYSTEM_ITEM'
687 and sts.ste_code <> 'TERMINATED'
688 and ((p_context = 'EXPIRE'
689 and sts.ste_code in ('ACTIVE', 'SIGNED'))
690 or (p_context in ('AUTHORING', 'RENEW')
691 and sts.ste_code = 'ENTERED'))
692 and not exists (select 'x'
693 from okc_k_rel_objs rel
694 where (rel.cle_id = cle2.id
695 or rel.cle_id = cle2.cle_id)
696 and rel.rty_code = g_rty_code))
697 connect by prior cle.id = cle.cle_id;
698 */
699 --
700 cursor exp_rel_csr (p_cle_id okc_k_items.cle_id%TYPE)is
701 select object1_id1, object1_id2
702 from okc_k_rel_objs rel
703 where rel.cle_id = p_cle_id
704 and rel.rty_code = 'OPPEXPSCONTRACT';
705 --
706 /* cursor item_csr (p_cle_id okc_k_items.cle_id%TYPE)is
707 select itm.object1_id1,
708 itm.object1_id2,
709 itm.uom_code,
710 itm.number_of_items
711 from okc_k_items itm,
712 jtf_object_usages jou
713 where itm.cle_id = p_cle_id
714 -- and itm.jtot_object1_code = 'OKX_LICPROD'
715 -- and itm.jtot_object1_code = 'OKX_MTL_SYSTEM_ITEMS'
716 and itm.jtot_object1_code = jou.object_code
717 and jou.object_user_code = 'OKX_MTL_SYSTEM_ITEM'
718 and rownum = 1; */
719 --
720 -- Cursor selects interest type (Marketing Category) for the inventory item
721 --
722 cursor item_interest_csr (p_organization_id NUMBER, p_inv_item_id NUMBER)is
723 SELECT interest_type_id,
724 primary_interest_code_id,
725 secondary_interest_code_id
726 -- FROM AST_INV_ITEM_LOV_V
727 FROM (
728 SELECT
729 mic.organization_id,
730 mic.inventory_item_id,
731 mc.segment1 interest_type_id,
732 mc.segment2 primary_interest_code_id,
733 mc.segment3 secondary_interest_code_id
734 FROM fnd_id_flex_structures fifs,
735 mtl_item_categories mic,
736 MTL_CATEGORIES_B MC
737 WHERE fifs.id_flex_code = 'MCAT' AND fifs.application_id = 401
738 AND fifs.id_flex_structure_code = 'SALES_CATEGORIES'
739 AND mc.structure_id = fifs.id_flex_num
740 and mc.SEGMENT1 < 'A' AND mic.category_set_id = 5
741 AND mic.category_id = mc.category_id
742 )
743 WHERE organization_id = p_organization_id
744 and inventory_item_id=p_inv_item_id;
745 /* --
746 Function Parent_Inv_Item_Exists(p_cle_id Number) Return Boolean IS
747 l_ret Boolean := False;
748 Begin
749 For i in l_id_tbl.FIRST..l_id_tbl.LAST
750 Loop
751 If p_cle_id = l_id_tbl(i) Then
752 l_ret := True;
753 Exit;
754 End If;
755 End Loop;
756 Return l_ret;
757 End Parent_Inv_Item_Exists;
758 */ --
759 --
760 --
761 Begin
762 IF (l_debug = 'Y') THEN
763 okc_debug.Set_Indentation('Create_Opp_Lines');
764 okc_debug.log('5000: Entering okc_opportunity_pvt.create_opp_lines', 2);
765 okc_debug.log(' with next parameters:', 2);
766 okc_debug.log(' p_context = '||p_context);
767 okc_debug.log(' p_contract_id = '||p_contract_id);
768 okc_debug.log(' p_lead_id = '||p_lead_id);
769 okc_debug.log(' g_rty_code = '||g_rty_code);
770 END IF;
771 x_return_status := okc_api.g_ret_sts_success;
772 -- Set the org_id for the curernt contract
773 l_org_id := okc_context.get_okc_org_id;
774 -- set the opp header id
775 l_header_rec.lead_id := p_lead_id;
776 /*
777 -- added to update header amt
778 -- Bug 2050044
779 OPEN line_csr_sum_amt;
780 FETCH line_csr_sum_amt INTO l_updt_hdr_tot_amt;
781 CLOSE line_csr_sum_amt;
782
783 l_header_rec.total_amount := l_updt_hdr_tot_amt;
784
785 IF (l_debug = 'Y') THEN
786 okc_debug.log('5005: Updating total header amount to '||l_updt_hdr_tot_amt, 2);
787 END IF;
788 -- update header now
789 AS_OPPORTUNITY_PUB.Update_Opp_Header
790 ( p_api_version_number => 2.0,
791 p_init_msg_list => p_init_msg_list,
792 p_commit => fnd_api.g_false,
793 p_validation_level => fnd_api.g_valid_level_full,
794 p_header_rec => l_header_rec,
795 p_check_access_flag => 'Y',
796 p_admin_flag => 'N',
797 p_admin_group_id => Null,
798 p_identity_salesforce_id => g_sales_rep,
799 p_partner_cont_party_id => Null,
800 p_profile_tbl => as_utility_pub.g_miss_profile_tbl,
801 x_return_status => l_return_status,
802 x_msg_count => l_msg_count,
803 x_msg_data => l_msg_data,
804 x_lead_id => l_lead_id );
805 */
806 -- end added
807
808
809 --
810 IF (l_debug = 'Y') THEN
811 okc_debug.log('5010: Before opening cursor line_csr');
812 END IF;
813 /* -- GF
814 -- Get all the eligible contract lines for opp lines creation
815 Open line_csr;
816 Fetch line_csr Bulk Collect
817 Into l_id_tbl,
818 l_cle_id_tbl,
819 l_price_unit_tbl,
820 l_price_negotiated_tbl,
821 l_currency_code_tbl;
822 Close line_csr;
823 --
824 IF (l_debug = 'Y') THEN
825 okc_debug.log('5020: After Closing cursor line_csr');
826 END IF;
827 l_count := l_id_tbl.count;
828 IF (l_debug = 'Y') THEN
829 okc_debug.log('5030: Number of lines for Opp creation - ' || to_char(l_count));
830 END IF;
831 */
832 -- Process further only if there are some eligible lines.
833 -- This should not arise since is_opp_creation_allowed will return Error
834 -- in this case
835 l_updt_hdr_tot_amt := 0; --??
836 -- If l_count > 0 Then
837 l_index := 1;
838 l_rel_index := 1;
839 FOR line_csr_rec IN line_csr LOOP
840 l_object1_id1 := NULL;
841 l_object1_id2 := NULL;
842 IF p_context='RENEW' and line_csr_rec.orig_system_source_code='OKC_LINE'
843 AND line_csr_rec.orig_system_id1 IS NOT NULL
844 THEN
845 Open exp_rel_csr( line_csr_rec.orig_system_id1 );
846 Fetch exp_rel_csr Into l_object1_id1, l_object1_id2;
847 Close exp_rel_csr;
848 END IF;
849 -- Prepare a separate table for lines id for later use
850 -- for rel objects creation. We cannot use the same l_id_tbl
851 -- here since there might be some mismatch between this table
852 -- and the output lines_out_tbl from the api call. This could
853 -- happen if opportunity was created as for expired contract
854 -- for original line_csr(before it was renewed). In this case
855 -- we should create link to the opportunity for the new (renewed) line.
856 l_rel_id_tbl(l_rel_index) := line_csr_rec.id;
857 -- IF l_object1_id1 is NULL it'll be populated after opportunity line is created
858 l_rel_lead_line_id_tbl(l_rel_index) := l_object1_id1;
859 l_rel_index := l_rel_index + 1;
860 IF l_object1_id1 IS NOT NULL THEN
861 IF (l_debug = 'Y') THEN
862 okc_debug.log('5031.1: Opportunity line #'||l_object1_id1||' has already been created as expired for renewed line ');
863 END IF;
864 else
865 -- Make sure the parent is not already processed
866 -- If Not Parent_Inv_Item_Exists(l_cle_id_tbl(i)) Then
867 IF (l_debug = 'Y') THEN
868 okc_debug.log('5031.2: Contract line '||line_csr_rec.id||' is going to be included into opportunity');
869 END IF;
870 /*
871 -- Get the item details
872 l_object1_id1 := NULL;
873 l_object1_id2 := NULL;
874 l_uom_code := NULL;
875 l_number_of_items := NULL;
876 Open item_csr(l_id_tbl(i));
877 Fetch item_csr
878 Into l_object1_id1,
879 l_object1_id2,
880 l_uom_code,
881 l_number_of_items;
882 Close item_csr;
883 */
884 -- Get Interest codes for opportunity lines
885 l_interest_type_id := NULL;
886 l_primary_interest_code_id := NULL;
887 l_secondary_interest_code_id := NULL;
888 Open item_interest_csr( line_csr_rec.object1_id2, line_csr_rec.object1_id1 );
889 Fetch item_interest_csr
890 Into l_interest_type_id,
891 l_primary_interest_code_id,
892 l_secondary_interest_code_id;
893 Close item_interest_csr;
894 -- Prepare the opp lines table for api call
895 l_line_tbl(l_index).lead_id := p_lead_id;
896 IF (l_debug = 'Y') THEN
897 okc_debug.log('5032: inventory_item_id='||line_csr_rec.object1_id1);
898 END IF;
899 l_line_tbl(l_index).inventory_item_id := line_csr_rec.object1_id1;
900 l_line_tbl(l_index).organization_id := line_csr_rec.object1_id2;
901 IF (l_debug = 'Y') THEN
902 okc_debug.log('5033: uom_code='||line_csr_rec.uom_code);
903 END IF;
904 l_line_tbl(l_index).uom_code := line_csr_rec.uom_code;
905 IF (l_debug = 'Y') THEN
906 okc_debug.log('5034: quantity='||line_csr_rec.number_of_items);
907 END IF;
908 l_line_tbl(l_index).quantity := line_csr_rec.number_of_items;
909 IF (l_debug = 'Y') THEN
910 okc_debug.log('5035: total_amount='||line_csr_rec.price_negotiated);
911 END IF;
912 l_line_tbl(l_index).total_amount := line_csr_rec.price_negotiated;
913 l_line_tbl(l_index).unit_price := line_csr_rec.price_unit;
914 l_line_tbl(l_index).price := line_csr_rec.price_negotiated;
915 l_line_tbl(l_index).currency_code := line_csr_rec.currency_code;
916 l_line_tbl(l_index).org_id := l_org_id;
917 IF (l_debug = 'Y') THEN
918 okc_debug.log('5035: interest_type_id='||l_interest_type_id);
919 END IF;
920 l_line_tbl(l_index).interest_type_id := l_interest_type_id;
921 l_line_tbl(l_index).primary_interest_code_id := l_primary_interest_code_id;
922 l_line_tbl(l_index).secondary_interest_code_id := l_secondary_interest_code_id;
923 l_updt_hdr_tot_amt := l_updt_hdr_tot_amt + l_line_tbl(l_index).total_amount;
924 l_index := l_index + 1;
925 End If;
926 End Loop;
927 --
928 l_header_rec.total_amount := l_updt_hdr_tot_amt; --??
929 IF (l_debug = 'Y') THEN
930 okc_debug.log('5040: '||l_line_tbl.count ||' lines are requested to be created on total amount:'||l_updt_hdr_tot_amt);
931 END IF;
932 -- Call the opp lines api
933 IF l_line_tbl.count>0 THEN
934 IF (l_debug = 'Y') THEN
935 okc_debug.log('5043: Before calling as_opportunity_pub.create_opp_lines');
936 END IF;
937 AS_OPPORTUNITY_PUB.Create_Opp_Lines(
938 p_api_version_number => 2.0, --p_api_version,--2.0,
939 p_init_msg_list => p_init_msg_list, --fnd_api.g_false,
940 p_commit => fnd_api.g_false,
941 p_validation_level => fnd_api.g_valid_level_full,
942 p_line_tbl => l_line_tbl,
943 p_header_rec => l_header_rec,
944 p_check_access_flag => 'Y',
945 p_admin_flag => 'N',
946 p_admin_group_id => Null,
947 p_identity_salesforce_id => g_sales_rep,
948 -- p_salesgroup_id => l_group_id,
949 p_salesgroup_id => Null,
950 p_partner_cont_party_id => Null,
951 p_profile_tbl => as_utility_pub.g_miss_profile_tbl,
952 x_line_out_tbl => l_line_out_tbl,
953 x_return_status => l_return_status,
954 x_msg_count => l_msg_count,
955 x_msg_data => l_msg_data);
956 --
957 IF (l_debug = 'Y') THEN
958 okc_debug.log('5048: After calling as_opportunity_pub.create_opp_lines');
959 END IF;
960 If l_return_status <> okc_api.g_ret_sts_success Then
961 IF (l_debug = 'Y') THEN
962 okc_debug.log('5049: Opp Lines Return Status - ' || l_return_status);
963 END IF;
964 Raise g_exception_halt_validation;
965 End If;
966 --
967 l_count := l_line_out_tbl.count;
968 ELSE
969 IF (l_debug = 'Y') THEN
970 okc_debug.log('5060: There are not lines to be inserted into Oppurtunity');
971 END IF;
972 IF g_opp_h_created THEN
973 IF (l_debug = 'Y') THEN
974 okc_debug.log('5061: We should remove Opportunity Header because we''ve created it by mistake');
975 okc_debug.log('5063: Before calling as_opportunity_pub.Delete_Opp_Header');
976 END IF;
977 AS_OPPORTUNITY_PUB.Delete_Opp_Header(
978 p_api_version_number => 2.0, --p_api_version,--2.0,
979 p_init_msg_list => p_init_msg_list, --fnd_api.g_false,
980 p_commit => fnd_api.g_false,
981 p_validation_level => fnd_api.g_valid_level_full,
982 p_header_rec => l_header_rec,
983 p_check_access_flag => 'Y',
984 p_admin_flag => 'N',
985 p_admin_group_id => Null,
986 p_identity_salesforce_id => g_sales_rep,
987 p_partner_cont_party_id => Null,
988 p_profile_tbl => as_utility_pub.g_miss_profile_tbl,
989 x_return_status => l_return_status,
990 x_msg_count => l_msg_count,
991 x_msg_data => l_msg_data,
992 x_lead_id => l_lead_id
993 );
994 --
995 IF (l_debug = 'Y') THEN
996 okc_debug.log('5068: After calling as_opportunity_pub.Delete_Opp_Header');
997 END IF;
998 If l_return_status <> okc_api.g_ret_sts_success Then
999 IF (l_debug = 'Y') THEN
1000 okc_debug.log('5069: Opp Lines Return Status - ' || l_return_status);
1001 END IF;
1002 Raise g_exception_halt_validation;
1003 End If;
1004 End If; -- IF g_opp_h_created THEN
1005 --
1006 l_count := 0;
1007 END IF;
1008 IF (l_debug = 'Y') THEN
1009 okc_debug.log('5070: Number of lines processed - ' || To_Char(l_count));
1010 END IF;
1011
1012 -- Now we need to populate the rel objects table
1013 If l_rel_id_tbl.count > 0 Then
1014 l_index := 1; -- point onto rec in l_line_out_tbl
1015 l_rel_index := 1; -- point onto rec in l_in_crjv_tbl
1016 For i in l_rel_id_tbl.FIRST..l_rel_id_tbl.LAST -- point onto rec in l_rel_id_tbl
1017 Loop
1018 IF (l_debug = 'Y') THEN
1019 okc_debug.log('5075: Contract Line ID - ' ||l_rel_id_tbl(i));
1020 END IF;
1021 If l_rel_lead_line_id_tbl(i) IS NULL Then
1022 IF (l_debug = 'Y') THEN
1023 okc_debug.log('5080: Processed Line Status - ' || l_line_out_tbl(l_index).return_status);
1024 END IF;
1025 If l_line_out_tbl(l_index).return_status = okc_api.g_ret_sts_success Then
1026 l_rel_lead_line_id_tbl(i) := l_line_out_tbl(l_index).lead_line_id;
1027 IF (l_debug = 'Y') THEN
1028 okc_debug.log('5081: Opportunity for the line was created successfully');
1029 END IF;
1030 else
1031 IF (l_debug = 'Y') THEN
1032 okc_debug.log('5082: Opportunity for the line was not created');
1033 END IF;
1034 END IF;
1035 l_index := l_index + 1;
1036 ELSE
1037 IF (l_debug = 'Y') THEN
1038 okc_debug.log('5085: Opportunity for the line exists');
1039 END IF;
1040 END IF;
1041 If l_rel_lead_line_id_tbl(i) IS NOT NULL Then
1042 IF (l_debug = 'Y') THEN
1043 okc_debug.log('5090: Opportunity Line ID - ' ||l_rel_lead_line_id_tbl(i));
1044 END IF;
1045 -- prepare the rel obj table for api call
1046 l_in_crjv_tbl(l_rel_index).object_version_number := 1;
1047 l_in_crjv_tbl(l_rel_index).cle_id := l_rel_id_tbl(i);
1048 l_in_crjv_tbl(l_rel_index).chr_id := p_contract_id; -- ??? new
1049 l_in_crjv_tbl(l_rel_index).jtot_object1_code := 'OKX_OPPLINES';
1050 l_in_crjv_tbl(l_rel_index).object1_id1 := l_rel_lead_line_id_tbl(i);
1051 l_in_crjv_tbl(l_rel_index).object1_id2 := '#';
1052 l_in_crjv_tbl(l_rel_index).rty_code := g_rty_code;
1053 l_rel_index := l_rel_index + 1;
1054 End If;
1055 IF (l_debug = 'Y') THEN
1056 okc_debug.log(' ------------ ' );
1057 END IF;
1058 End Loop;
1059 If l_in_crjv_tbl.count > 0 Then
1060 IF (l_debug = 'Y') THEN
1061 okc_debug.log('5091: Before creating relation objects');
1062 okc_debug.log('5092: '||l_in_crjv_tbl.count||' relation lines to be created ');
1063 END IF;
1064 -- Call the api here
1065 okc_k_rel_objs_pub.Create_Row(
1066 p_api_version => 1.0, --p_api_version
1067 p_init_msg_list => p_init_msg_list,
1068 x_return_status => l_return_status,
1069 x_msg_count => l_msg_count,
1070 x_msg_data => l_msg_data,
1071 p_crjv_tbl => l_in_crjv_tbl,
1072 x_crjv_tbl => l_out_crjv_tbl);
1073 IF (l_debug = 'Y') THEN
1074 okc_debug.log('5095: After creating relation objects');
1075 okc_debug.log('5096: '||l_out_crjv_tbl.count||' relation lines were created ');
1076 END IF;
1077 If l_return_status <> okc_api.g_ret_sts_success Then
1078 IF (l_debug = 'Y') THEN
1079 okc_debug.log('5099: Return Status from Rel Objects Creation - ' || l_return_status);
1080 END IF;
1081 Raise g_exception_halt_validation;
1082 End If;
1083 End If;
1084 End If; -- If l_rel_id_tbl.count > 0 Then
1085 /* Else -- If l_count > 0 Then -- 1
1086 -- this should never happen since this will be trapped by the itm_csr,
1087 -- line_csr and rel_csr cursor in is_opp_creation_allowed itself.
1088 IF (l_debug = 'Y') THEN
1089 okc_debug.log('5100: No lines selected for opp creation');
1090 END IF;
1091 l_return_status := okc_api.g_ret_sts_error;
1092 Raise g_exception_halt_validation;
1093 End If;*/
1094 --
1095
1096 x_msg_data := l_msg_data;
1097 x_msg_count := l_msg_count;
1098
1099 IF (l_debug = 'Y') THEN
1100 okc_debug.log('6000: Exiting okc_opportunity_pvt.create_opp_lines', 2);
1101 okc_debug.Reset_Indentation;
1102 END IF;
1103 Exception
1104 When g_exception_halt_validation Then
1105 x_return_status := l_return_status;
1106 IF (l_debug = 'Y') THEN
1107 okc_debug.log('5980: Exiting okc_opportunity_pvt.create_opp_lines', 2);
1108 okc_debug.Reset_Indentation;
1109 END IF;
1110 When Others Then
1111 okc_api.Set_Message(p_app_name => g_app_name,
1112 p_msg_name => g_unexpected_error,
1113 p_token1 => g_sqlcode_token,
1114 p_token1_value => sqlcode,
1115 p_token2 => g_sqlerrm_token,
1116 p_token2_value => sqlerrm);
1117 x_return_status := okc_api.g_ret_sts_unexp_error;
1118 IF (l_debug = 'Y') THEN
1119 okc_debug.log('5990: Exiting okc_opportunity_pvt.create_opp_lines', 2);
1120 okc_debug.Reset_Indentation;
1121 END IF;
1122 End Create_Opp_Lines;
1123
1124 PROCEDURE IS_OPP_CREATION_ALLOWED(p_context IN VARCHAR2,
1125 p_contract_id IN NUMBER,
1126 x_return_status OUT NOCOPY VARCHAR2) IS
1127 --
1128 cursor k_csr is
1129 select chrb.buy_or_sell,
1130 chrb.template_yn,
1131 scs.create_opp_yn,
1132 sts.ste_code
1133 from okc_k_headers_b chrb,
1134 okc_subclasses_b scs,
1135 okc_statuses_b sts
1136 where chrb.id = p_contract_id
1137 and scs.code = chrb.scs_code
1138 and sts.code = chrb.sts_code;
1139 k_rec k_csr%ROWTYPE;
1140 --
1141 cursor cpl_csr (p_rle_code in okc_k_party_roles_b.rle_code%TYPE) is
1142 select 'x'
1143 from okc_k_party_roles_b
1144 where dnz_chr_id = p_contract_id
1145 and cle_id is null
1146 and rle_code = p_rle_code;
1147 --
1148 cursor ctc_csr (p_object_code in okc_contacts.jtot_object1_code%TYPE) is
1149 select 'x'
1150 from okc_contacts
1151 where dnz_chr_id = p_contract_id
1152 and jtot_object1_code = p_object_code
1153
1154 and object1_id1 is not null; --bug 2071104
1155 --
1156 -- item should be of usage OKX_MTL_SYSTEM_ITEM
1157 cursor itm_csr is
1158 select 'x'
1159 from okc_k_items itm, jtf_object_usages jou
1160 where itm.dnz_chr_id = p_contract_id
1161 and itm.jtot_object1_code = jou.object_code
1162 and jou.object_user_code = 'OKX_MTL_SYSTEM_ITEM';
1163 /*
1164 cursor itm_csr is
1165 select 'x'
1166 from okc_k_items
1167 where dnz_chr_id = p_contract_id
1168 -- and jtot_object1_code = 'OKX_LICPROD';
1169 and jtot_object1_code = 'OKX_MTL_SYSTEM_ITEMS';
1170 */
1171 --
1172 cursor line_csr is
1173 select 'x'
1174 from okc_k_lines_b cle,
1175 okc_statuses_b sts
1176 -- where cle.dnz_chr_id = p_contract_id
1177 where cle.chr_id = p_contract_id
1178 and cle.sts_code = sts.code
1179 and sts.ste_code in ('ACTIVE', 'SIGNED');
1180 --
1181 cursor rel_csr is
1182 select 'x'
1183 from okc_k_lines_b cle
1184 -- where cle.dnz_chr_id = p_contract_id
1185 where cle.chr_id = p_contract_id
1186 and cle.date_renewed is null
1187 and not exists (select 'x'
1188 from okc_k_rel_objs rel
1189 where (rel.cle_id = cle.id
1190 or rel.cle_id = cle.cle_id)
1191 and rel.rty_code = g_rty_code);
1192 --
1193 cursor rul_csr(p_rule okc_rules_b.rule_information_category%TYPE,
1194 p_renewal_type okc_rules_b.rule_information1%TYPE) is
1195 select 'x'
1196 from okc_rules_b
1197 where dnz_chr_id = p_contract_id
1198 and rule_information_category = p_rule
1199 and rule_information1 = p_renewal_type;
1200 --
1201 l_dummy Varchar2(1);
1202 l_row_found Boolean;
1203 l_row_notfound Boolean;
1204 --
1205 Begin
1206 IF (l_debug = 'Y') THEN
1207 okc_debug.Set_Indentation('Is_Opp_Creation_Allowed');
1208 okc_debug.log('7000: Entering okc_opportunity_pvt.is_opp_creation_allowed', 2);
1209 END IF;
1210 x_return_status := okc_api.g_ret_sts_success;
1211 -- Get contract's details
1212 Open k_csr;
1213 Fetch k_csr Into k_rec;
1214 l_row_notfound := k_csr%NOTFOUND;
1215 Close k_csr;
1216 -- In all the checks below, the mesasegs are to be shown only if
1217 -- this api has been called from Authoring form.
1218 -- Error out if it is not a valid contract.
1219 -- ****NOTE**** we are now showing the error messages i.e. putting
1220 -- them on the stack, regardless of the context.
1221 -- Please refer to Bug 2074526
1222 -- ************
1223 If l_row_notfound Then
1224 ----If p_context = 'AUTHORING' Then
1225 okc_api.Set_Message(G_APP_NAME, 'OKC_OPP_INVALID_CHR');
1226 ----End If;
1227 Raise g_exception_halt_validation;
1228 End If;
1229 -- Contract's category must allow the opp creation
1230 If Nvl(k_rec.create_opp_yn, '*') <> 'Y' Then
1231 If p_context = 'AUTHORING' Then --we need to check context here because we don't want this
1232 --check for RENEW and EXPIRE processes where we may not want to
1233 --create opportunities
1234 okc_api.Set_Message(G_APP_NAME, 'OKC_OPP_INVALID_CATEGORY');
1235 End If;
1236 Raise g_exception_halt_validation;
1237 End If;
1238 -- Templates cannot be created into opportunities
1239 If k_rec.template_yn = 'Y' Then
1240 ----If p_context = 'AUTHORING' Then
1241 okc_api.Set_Message(G_APP_NAME, 'OKC_OPP_TEMPLATE_CHR');
1242 ----End If;
1243 Raise g_exception_halt_validation;
1244 End If;
1245 -- Opportunites can be created only for Sell contracts
1246 If Nvl(k_rec.buy_or_sell, '*') <> 'S' Then
1247 ----If p_context = 'AUTHORING' Then
1248 okc_api.Set_Message(G_APP_NAME, 'OKC_OPP_NO_SELL_CHR');
1249 ----End If;
1250 Raise g_exception_halt_validation;
1251 End If;
1252 -- Not allowed if it is already terminated
1253 If k_rec.ste_code In ('TERMINATED') Then
1254 ----If p_context = 'AUTHORING' Then
1255 okc_api.Set_Message(G_APP_NAME, 'OKC_OPP_CHR_TERMINATED');
1256 ----End If;
1257 Raise g_exception_halt_validation;
1258 End If;
1259
1260 -- In case this api called from expiry of lines, the contract must
1261 -- be active. For other cases, it should be in entered status.
1262 /* next condition changed 'cause it's not correct. It allows any
1263 If (p_context in ('AUTHORING', 'RENEW') And
1264 k_rec.ste_code <> 'ENTERED') Or
1265 (p_context = 'EXPIRE' And
1266 k_rec.ste_code Not In ('SIGNED', 'ACTIVE')) Then
1267 */
1268 If NOT(( p_context in ('AUTHORING', 'RENEW') And k_rec.ste_code = 'ENTERED')
1269 OR (p_context = 'EXPIRE' And k_rec.ste_code In ('SIGNED', 'ACTIVE') ))
1270 Then
1271 IF (l_debug = 'Y') THEN
1272 okc_debug.log('7300: Invalid contract status for the context' );
1273 okc_debug.log('7310: p_context='||p_context||', ste_code='||k_rec.ste_code );
1274 END IF;
1275 ----If p_context = 'AUTHORING' Then
1276 okc_api.Set_Message(G_APP_NAME, 'OKC_OPP_INVALID_STATUS');
1277 ----End If;
1278 Raise g_exception_halt_validation;
1279 End If;
1280 -- Contract must have a Customer assigned to it
1281 Open cpl_csr('CUSTOMER');
1282 Fetch cpl_csr into l_dummy;
1283 l_row_notfound := cpl_csr%NOTFOUND;
1284 Close cpl_csr;
1285 If l_row_notfound Then
1286 ----If p_context = 'AUTHORING' Then
1287 okc_api.Set_Message(G_APP_NAME, 'OKC_OPP_NO_CUSTOMER');
1288 ----End If;
1289 Raise g_exception_halt_validation;
1290 End If;
1291 -- Also make sure there is a Salesrep
1292 Open ctc_csr('OKX_SALEPERS');
1293 Fetch ctc_csr Into l_dummy;
1294 l_row_notfound := ctc_csr%NOTFOUND;
1295 Close ctc_csr;
1296 If l_row_notfound Then
1297 ----If p_context = 'AUTHORING' Then
1298 okc_api.Set_Message(G_APP_NAME, 'OKC_OPP_NO_SALESREP');
1299 ----End If;
1300 Raise g_exception_halt_validation;
1301 End If;
1302 -- There must be at least one inventory line item
1303 Open itm_csr;
1304 Fetch itm_csr Into l_dummy;
1305 l_row_notfound := itm_csr%NOTFOUND;
1306 Close itm_csr;
1307 If l_row_notfound Then
1308 ----If p_context = 'AUTHORING' Then
1309 okc_api.Set_Message(G_APP_NAME, 'OKC_OPP_NO_INV_ITEM');
1310 ----End If;
1311 Raise g_exception_halt_validation;
1312 End If;
1313 -- If opp creation is done from auth or renewal, it should pick
1314 -- up only those lines that have not already been renewed. In
1315 -- case of expiry, if the lines are not already renewed, they
1316 -- are a candidate for opp creation even though an opp was
1317 -- created for them in the contract's entered status. However
1318 -- the lines that were created into an opp by an earlier expiry
1319 -- process should not be picked up again by the next expiry process.
1320 If p_context in ('AUTHORING', 'RENEW') Then
1321 g_rty_code := 'OPPREPSCONTRACT';
1322 Else
1323 g_rty_code := 'OPPEXPSCONTRACT';
1324 -- For expiry process, make sure there is one Active/Signed line
1325 Open line_csr;
1326 Fetch line_csr Into l_dummy;
1327 l_row_notfound := line_csr%NOTFOUND;
1328 Close line_csr;
1329 If l_row_notfound Then
1330 ----If p_context = 'AUTHORING' Then
1331 okc_api.Set_Message(G_APP_NAME, 'OKC_OPP_NO_ACTIVE_LINES');
1332 ----End If;
1333 Raise g_exception_halt_validation;
1334 End If;
1335 End If;
1336 --
1337 Open rel_csr;
1338 Fetch rel_csr Into l_dummy;
1339 l_row_notfound := rel_csr%NOTFOUND;
1340 Close rel_csr;
1341 If l_row_notfound Then
1342 ----If p_context = 'AUTHORING' Then
1343 okc_api.Set_Message(G_APP_NAME, 'OKC_OPP_NO_LINES');
1344 ----End If;
1345 Raise g_exception_halt_validation;
1346 End If;
1347 -- If the contract is evergreen, no opp can be created
1348 Open rul_csr('REN', 'EVN');
1349 Fetch rul_csr Into l_dummy;
1350 l_row_found := rul_csr%FOUND;
1351 Close rul_csr;
1352 If l_row_found Then
1353 ----If p_context = 'AUTHORING' Then
1354 okc_api.Set_Message(G_APP_NAME, 'OKC_OPP_EVERGREEN_CONTRACT');
1355 ----End If;
1356 Raise g_exception_halt_validation;
1357 End If;
1358 --
1359 IF (l_debug = 'Y') THEN
1360 okc_debug.log('7500: Opportunity Creation is allowed');
1361 okc_debug.log('8000: Exiting okc_opportunity_pvt.is_opp_creation_allowed', 2);
1362 okc_debug.Reset_Indentation;
1363 END IF;
1364 Exception
1365 When g_exception_halt_validation Then
1366 x_return_status := okc_api.g_ret_sts_error;
1367 IF (l_debug = 'Y') THEN
1368 okc_debug.log('7600: Opportunity Creation is not allowed');
1369 okc_debug.log('7980: Exiting okc_opportunity_pvt.is_opp_creation_allowed', 2);
1370 okc_debug.Reset_Indentation;
1371 END IF;
1372 When Others Then
1373 okc_api.Set_Message(p_app_name => g_app_name,
1374 p_msg_name => g_unexpected_error,
1375 p_token1 => g_sqlcode_token,
1376 p_token1_value => sqlcode,
1377 p_token2 => g_sqlerrm_token,
1378 p_token2_value => sqlerrm);
1379 x_return_status := okc_api.g_ret_sts_unexp_error;
1380 IF (l_debug = 'Y') THEN
1381 okc_debug.log('7990: Exiting okc_opportunity_pvt.is_opp_creation_allowed', 2);
1382 okc_debug.Reset_Indentation;
1383 END IF;
1384 End Is_Opp_Creation_Allowed;
1385
1386 PROCEDURE GET_OPP_DEFAULTS(p_context IN VARCHAR2,
1387 p_contract_id IN NUMBER,
1388 x_win_probability IN OUT NOCOPY NUMBER,
1389 x_closing_date_days IN OUT NOCOPY NUMBER,
1390 x_return_status OUT NOCOPY VARCHAR2) IS
1391 cursor c1(p_rule_information_category IN
1392 okc_rules_b.rule_information_category%TYPE) is
1393 select rule_information1,
1394 rule_information2,
1395 rule_information3,
1396 rule_information4,
1397 rule_information5
1398 from okc_rules_b
1399 where dnz_chr_id = p_contract_id
1400 and rule_information_category = p_rule_information_category;
1401 c1_rec c1%ROWTYPE;
1402 l_row_found Boolean;
1403 --
1404 Begin
1405 IF (l_debug = 'Y') THEN
1406 okc_debug.Set_Indentation('Get_Opp_Defaults');
1407 okc_debug.log('9000: Entering okc_opportunity_pvt.get_opp_defaults', 2);
1408 okc_debug.log('9010: Calling Mode - ' || p_context);
1409 END IF;
1410 x_return_status := okc_api.g_ret_sts_success;
1411 --
1412 If p_context = 'RENEW' Then
1413 Open c1('REN');
1414 Fetch c1 Into c1_rec;
1415 l_row_found := c1%FOUND;
1416 Close c1;
1417 If l_row_found Then
1418 x_win_probability := c1_rec.rule_information4;
1419 x_closing_date_days := c1_rec.rule_information5;
1420 End If;
1421 Elsif p_context In ('AUTHORING', 'EXPIRE') Then
1422 Open c1('RVE');
1423 Fetch c1 Into c1_rec;
1424 l_row_found := c1%FOUND;
1425 Close c1;
1426 If l_row_found Then
1427 x_win_probability := c1_rec.rule_information1;
1428 x_closing_date_days := c1_rec.rule_information2;
1429 End If;
1430 End If;
1431 --
1432 If x_win_probability Is Null Then
1433 IF (l_debug = 'Y') THEN
1434 okc_debug.log('9050: Fetching win_probability from profile');
1435 END IF;
1436 x_win_probability := Fnd_Profile.Value('AS_OPP_WIN_PROBABILITY');
1437 End If;
1438 If x_closing_date_days Is Null Then
1439 IF (l_debug = 'Y') THEN
1440 okc_debug.log('9060: Fetching closing_date_days from profile');
1441 END IF;
1442 x_closing_date_days := Fnd_Profile.Value('AS_OPP_CLOSING_DATE_DAYS');
1443 End If;
1444 --
1445 IF (l_debug = 'Y') THEN
1446 okc_debug.log('9070: x_win_probability - ' || x_win_probability);
1447 okc_debug.log('9080: x_closing_date_days - ' || x_closing_date_days);
1448 okc_debug.log('10000: Exiting okc_opportunity_pvt.get_opp_defaults', 2);
1449 okc_debug.Reset_Indentation;
1450 END IF;
1451 Exception
1452 When Others Then
1453 okc_api.Set_Message(p_app_name => g_app_name,
1454 p_msg_name => g_unexpected_error,
1455 p_token1 => g_sqlcode_token,
1456 p_token1_value => sqlcode,
1457 p_token2 => g_sqlerrm_token,
1458 p_token2_value => sqlerrm);
1459 x_return_status := okc_api.g_ret_sts_unexp_error;
1460 IF (l_debug = 'Y') THEN
1461 okc_debug.log('9990: Exiting okc_opportunity_pvt.get_opp_defaults', 2);
1462 okc_debug.Reset_Indentation;
1463 END IF;
1464 End Get_Opp_Defaults;
1465 END OKC_OPPORTUNITY_PVT;
[Home] [Help]