[Home] [Help]
PACKAGE BODY: APPS.OKL_CREDIT_DATAPOINTS_PVT
Source
1 PACKAGE BODY OKL_CREDIT_DATAPOINTS_PVT AS
2 /* $Header: OKLRCDPB.pls 120.13.12010000.7 2010/04/28 05:00:25 rpillay ship $ */
3
4 ---------------------------------------------
5 -- FUNCTION credit_line_number
6 ---------------------------------------------
7 FUNCTION credit_line_number(x_resultout OUT NOCOPY VARCHAR2,
8 x_errormsg OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
9
10 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
11 l_credit_line_number VARCHAR2(120);
12
13 BEGIN
14 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
15 x_resultout := FND_API.G_RET_STS_SUCCESS;
16 BEGIN
17 SELECT KHR.CONTRACT_NUMBER
18 INTO l_credit_line_number
19 FROM OKC_K_HEADERS_B KHR, OKL_LEASE_APPLICATIONS_B LAP
20 WHERE KHR.SCS_CODE = 'CREDITLINE_CONTRACT'
21 AND LAP.CREDIT_LINE_ID = KHR.ID
22 AND LAP.ID = l_lease_app_id;
23
24 EXCEPTION
25 WHEN NO_DATA_FOUND THEN
26 l_credit_line_number := NULL;
27 WHEN OTHERS THEN
28 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
29 x_errormsg := sqlerrm;
30 END;
31
32 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_value :=
33 to_char(l_credit_line_number) ;
34
35 RETURN to_char(l_credit_line_number );
36 END credit_line_number ;
37
38 ---------------------------------------------
39 -- FUNCTION credit_line_expiration_date
40 ---------------------------------------------
41 FUNCTION credit_line_expiration_date(x_resultout OUT NOCOPY VARCHAR2,
42 x_errormsg OUT NOCOPY VARCHAR2) RETURN DATE IS
43
44 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
45 l_credit_line_expiration_date DATE;
46
47 BEGIN
48 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
49 x_resultout := FND_API.G_RET_STS_SUCCESS;
50 BEGIN
51 SELECT KHR.END_DATE
52 INTO l_credit_line_expiration_date
53 FROM OKC_K_HEADERS_B KHR,
54 OKL_LEASE_APPLICATIONS_B LAP
55 WHERE KHR.SCS_CODE = 'CREDITLINE_CONTRACT'
56 AND LAP.CREDIT_LINE_ID = KHR.ID
57 AND LAP.ID = l_lease_app_id;
58
59 EXCEPTION
60 WHEN NO_DATA_FOUND THEN
61 l_credit_line_expiration_date := NULL;
62 WHEN OTHERS THEN
63 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
64 x_errormsg := sqlerrm;
65 END;
66
67 l_credit_line_expiration_date := to_date(l_credit_line_expiration_date, 'DD-MM-RRRR');
68
69 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_value :=
70 l_credit_line_expiration_date;
71
72 RETURN l_credit_line_expiration_date;
73 END credit_line_expiration_date ;
74
75 ---------------------------------------------
76 -- FUNCTION currency
77 ---------------------------------------------
78 FUNCTION currency(x_resultout OUT NOCOPY VARCHAR2,
79 x_errormsg OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
80
81 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
82 l_currency VARCHAR2(15);
83
84 BEGIN
85 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
86 x_resultout := FND_API.G_RET_STS_SUCCESS;
87 BEGIN
88 SELECT CURRENCY_CODE
89 INTO l_currency
90 FROM OKL_LEASE_APPLICATIONS_B
91 WHERE ID = l_lease_app_id;
92
93 EXCEPTION
94 WHEN NO_DATA_FOUND THEN
95 l_currency := NULL;
96 WHEN OTHERS THEN
97 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
98 x_errormsg := sqlerrm;
99 END;
100
101 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_value :=
102 to_char(l_currency) ;
103
104 RETURN to_char(l_currency);
105 END currency ;
106
107 ---------------------------------------------
108 -- FUNCTION sales_rep
109 ---------------------------------------------
110 FUNCTION sales_rep(x_resultout OUT NOCOPY VARCHAR2,
111 x_errormsg OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
112
113 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
114 l_sales_rep VARCHAR2(30);
115
116 BEGIN
117 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
118 x_resultout := FND_API.G_RET_STS_SUCCESS;
119 BEGIN
120 SELECT REP.SALESREP_NUMBER
121 INTO l_sales_rep
122 FROM JTF_RS_SALESREPS REP,
123 OKL_LEASE_APPLICATIONS_B LAP
124 WHERE LAP.SALES_REP_ID = REP.SALESREP_ID
125 AND REP.ORG_ID = LAP.ORG_ID
126 AND LAP.ID = l_lease_app_id;
127
128 EXCEPTION
129 WHEN NO_DATA_FOUND THEN
130 l_sales_rep := NULL;
131 WHEN OTHERS THEN
132 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
133 x_errormsg := sqlerrm;
134 END;
135
136 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_value :=
137 to_char(l_sales_rep) ;
138
139 RETURN to_char(l_sales_rep);
140 END sales_rep ;
141
142 ---------------------------------------------
143 -- FUNCTION program_vendor
144 ---------------------------------------------
145 FUNCTION program_vendor(x_resultout OUT NOCOPY VARCHAR2,
146 x_errormsg OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
147
148 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
149 l_program_vendor VARCHAR2(30);
150
151 BEGIN
152 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
153 x_resultout := FND_API.G_RET_STS_SUCCESS;
154 BEGIN
155 SELECT PO.SEGMENT1
156 INTO l_program_vendor
157 FROM OKL_LEASE_APPLICATIONS_B LAP, OKC_K_HEADERS_B CHR,
158 OKC_K_PARTY_ROLES_B CPL, PO_VENDORS PO
159 WHERE LAP.PROGRAM_AGREEMENT_ID = CHR.ID
160 AND CHR.ID = CPL.DNZ_CHR_ID
161 AND CPL.JTOT_OBJECT1_CODE = 'OKL_VENDOR'
162 AND CPL.OBJECT1_ID1 = PO.VENDOR_ID
163 AND LAP.ID = l_lease_app_id;
164
165 EXCEPTION
166 WHEN NO_DATA_FOUND THEN
167 l_program_vendor := NULL;
168 WHEN OTHERS THEN
169 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
170 x_errormsg := sqlerrm;
171 END;
172
173 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_value :=
174 to_char(l_program_vendor) ;
175
176 RETURN to_char(l_program_vendor );
177 END program_vendor ;
178
179 ---------------------------------------------
180 -- FUNCTION program_agreement_number
181 ---------------------------------------------
182 FUNCTION program_agreement_number(x_resultout OUT NOCOPY VARCHAR2,
183 x_errormsg OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
184
185 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
186 l_program_agreement_number VARCHAR2(120);
187
188 BEGIN
189 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
190 x_resultout := FND_API.G_RET_STS_SUCCESS;
191 BEGIN
192 SELECT KHR.CONTRACT_NUMBER
193 INTO l_program_agreement_number
194 FROM OKC_K_HEADERS_B KHR, OKL_LEASE_APPLICATIONS_B LAP
195 WHERE KHR.SCS_CODE = 'PROGRAM'
196 AND LAP.PROGRAM_AGREEMENT_ID = KHR.ID
197 AND LAP.ID = l_lease_app_id;
198
199 EXCEPTION
200 WHEN NO_DATA_FOUND THEN
201 l_program_agreement_number := NULL;
202 WHEN OTHERS THEN
203 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
204 x_errormsg := sqlerrm;
205 END;
206
207 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_value :=
208 to_char(l_program_agreement_number) ;
209
210 RETURN to_char(l_program_agreement_number );
211 END program_agreement_number ;
212
213 ---------------------------------------------
214 -- FUNCTION expected_start_date
215 ---------------------------------------------
216 FUNCTION expected_start_date(x_resultout OUT NOCOPY VARCHAR2,
217 x_errormsg OUT NOCOPY VARCHAR2) RETURN DATE IS
218
219 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
220 l_expected_start_date DATE;
221
222 BEGIN
223 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
224 x_resultout := FND_API.G_RET_STS_SUCCESS;
225 BEGIN
226 SELECT QUOTE.EXPECTED_START_DATE
227 INTO l_expected_start_date
228 FROM OKL_LEASE_QUOTES_B QUOTE,
229 OKL_LEASE_APPLICATIONS_B LAP
230 WHERE QUOTE.PRIMARY_QUOTE = 'Y'
231 AND LAP.ID = QUOTE.PARENT_OBJECT_ID
232 AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
233 AND LAP.ID = l_lease_app_id;
234
235 EXCEPTION
236 WHEN NO_DATA_FOUND THEN
237 l_expected_start_date := NULL;
238 WHEN OTHERS THEN
239 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
240 x_errormsg := sqlerrm;
241 END;
242
243 l_expected_start_date := to_date(l_expected_start_date, 'DD-MM-RRRR');
244
245 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_value :=
246 l_expected_start_date;
247
248 RETURN l_expected_start_date;
249 END expected_start_date ;
250
251 ---------------------------------------------
252 -- FUNCTION expected_delivery_date
253 ---------------------------------------------
254 FUNCTION expected_delivery_date(x_resultout OUT NOCOPY VARCHAR2,
255 x_errormsg OUT NOCOPY VARCHAR2) RETURN DATE IS
256
257 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
258 l_expected_delivery_date DATE;
259
260 BEGIN
261 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
262 x_resultout := FND_API.G_RET_STS_SUCCESS;
263 BEGIN
264 SELECT QUOTE.EXPECTED_DELIVERY_DATE
265 INTO l_expected_delivery_date
266 FROM OKL_LEASE_QUOTES_B QUOTE, OKL_LEASE_APPLICATIONS_B LAP
267 WHERE QUOTE.PRIMARY_QUOTE = 'Y'
268 AND LAP.ID = QUOTE.PARENT_OBJECT_ID
269 AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
270 AND LAP.ID = l_lease_app_id;
271
272 EXCEPTION
273 WHEN NO_DATA_FOUND THEN
274 l_expected_delivery_date := NULL;
275 WHEN OTHERS THEN
276 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
277 x_errormsg := sqlerrm;
278 END;
279
280 l_expected_delivery_date := to_date(l_expected_delivery_date, 'DD-MM-RRRR');
281
282 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_value :=
283 l_expected_delivery_date;
284
285 RETURN l_expected_delivery_date;
286 END expected_delivery_date ;
287
288 ---------------------------------------------
289 -- FUNCTION expected_funding_date
290 ---------------------------------------------
291 FUNCTION expected_funding_date(x_resultout OUT NOCOPY VARCHAR2,
292 x_errormsg OUT NOCOPY VARCHAR2) RETURN DATE IS
293
294 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
295 l_expected_funding_date DATE;
296
297 BEGIN
298 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
299 x_resultout := FND_API.G_RET_STS_SUCCESS;
300 BEGIN
301 SELECT QUOTE.EXPECTED_FUNDING_DATE
302 INTO l_expected_funding_date
303 FROM OKL_LEASE_QUOTES_B QUOTE, OKL_LEASE_APPLICATIONS_B LAP
304 WHERE QUOTE.PRIMARY_QUOTE = 'Y'
305 AND LAP.ID = QUOTE.PARENT_OBJECT_ID
306 AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
307 AND LAP.ID = l_lease_app_id;
308
309 EXCEPTION
310 WHEN NO_DATA_FOUND THEN
311 l_expected_funding_date := NULL;
312 WHEN OTHERS THEN
313 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
314 x_errormsg := sqlerrm;
315 END;
316
317 l_expected_funding_date := to_date(l_expected_funding_date, 'DD-MM-RRRR');
318
319 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_value :=
320 l_expected_funding_date;
321
322 RETURN l_expected_funding_date;
323 END expected_funding_date ;
324
325 ---------------------------------------------
326 -- FUNCTION lease_application_template
327 ---------------------------------------------
328 FUNCTION lease_application_template(x_resultout OUT NOCOPY VARCHAR2,
329 x_errormsg OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
330
331 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
332 l_lease_application_template VARCHAR2(150);
333
334 BEGIN
335 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
336 x_resultout := FND_API.G_RET_STS_SUCCESS;
337 BEGIN
338 SELECT TEMPLATES.NAME
339 INTO l_lease_application_template
340 FROM OKL_LEASEAPP_TEMPLATES TEMPLATES, OKL_LEASE_APPLICATIONS_B LAP
341 WHERE LAP. LEASEAPP_TEMPLATE_ID = TEMPLATES.ID
342 AND LAP.ID = l_lease_app_id;
343
344 EXCEPTION
345 WHEN NO_DATA_FOUND THEN
346 l_lease_application_template := NULL;
347 WHEN OTHERS THEN
348 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
349 x_errormsg := sqlerrm;
350 END;
351
352 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_value :=
353 to_char(l_lease_application_template) ;
354
355 RETURN to_char(l_lease_application_template );
356 END lease_application_template ;
357
358 ---------------------------------------------
359 -- FUNCTION org_unit
360 ---------------------------------------------
361 FUNCTION org_unit(x_resultout OUT NOCOPY VARCHAR2,
362 x_errormsg OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
363
364 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
365 l_org_unit VARCHAR2(240);
366
367 BEGIN
368 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
369 x_resultout := FND_API.G_RET_STS_SUCCESS;
370 BEGIN
371 SELECT ORG.NAME
372 INTO l_org_unit
373 FROM HR_ALL_ORGANIZATION_UNITS ORG, OKL_LEASE_APPLICATIONS_B LAP
374 WHERE ORG.ORGANIZATION_ID = LAP.ORG_ID
375 AND LAP.ID = l_lease_app_id;
376
377 EXCEPTION
378 WHEN NO_DATA_FOUND THEN
379 l_org_unit := NULL;
380 WHEN OTHERS THEN
381 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
382 x_errormsg := sqlerrm;
383 END;
384
385 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_value :=
386 to_char(l_org_unit) ;
387
388 RETURN to_char(l_org_unit );
389 END org_unit ;
390
391 ---------------------------------------------
392 -- FUNCTION prospect_address
393 ---------------------------------------------
394 FUNCTION prospect_address(x_resultout OUT NOCOPY VARCHAR2,
395 x_errormsg OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
396
397 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
398 l_prospect_address VARCHAR2(30);
399
400 BEGIN
401 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
402 x_resultout := FND_API.G_RET_STS_SUCCESS;
403 BEGIN
404 SELECT PARTY_SITES.PARTY_SITE_NUMBER
405 INTO l_prospect_address
406 FROM HZ_PARTY_SITES PARTY_SITES, OKL_LEASE_APPLICATIONS_B LAP
407 WHERE PARTY_SITES.PARTY_SITE_ID = LAP.PROSPECT_ADDRESS_ID
408 AND LAP.ID = l_lease_app_id;
409
410 EXCEPTION
411 WHEN NO_DATA_FOUND THEN
412 l_prospect_address := NULL;
413 WHEN OTHERS THEN
414 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
415 x_errormsg := sqlerrm;
416 END;
417
418 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_value :=
419 to_char(l_prospect_address) ;
420
421 RETURN to_char(l_prospect_address );
422 END prospect_address ;
423
424 ---------------------------------------------
425 -- FUNCTION term_of_deal
426 ---------------------------------------------
427 FUNCTION term_of_deal(x_resultout OUT NOCOPY VARCHAR2,
428 x_errormsg OUT NOCOPY VARCHAR2) RETURN NUMBER IS
429
430 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
431 l_term_of_deal NUMBER;
432
433 BEGIN
434 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
435 x_resultout := FND_API.G_RET_STS_SUCCESS;
436 BEGIN
437 SELECT QUOTE.TERM
438 INTO l_term_of_deal
439 FROM OKL_LEASE_QUOTES_B QUOTE, OKL_LEASE_APPLICATIONS_B LAP
440 WHERE QUOTE.PRIMARY_QUOTE = 'Y'
441 AND QUOTE.PARENT_OBJECT_ID = LAP.ID
442 AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
443 AND LAP.ID = l_lease_app_id;
444
445 EXCEPTION
446 WHEN NO_DATA_FOUND THEN
447 l_term_of_deal := NULL;
448 WHEN OTHERS THEN
449 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
450 x_errormsg := sqlerrm;
451 END;
452
453 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_value :=
454 l_term_of_deal ;
455
456 RETURN l_term_of_deal;
457 END term_of_deal ;
458
459 ---------------------------------------------
460 -- FUNCTION financial_product
461 ---------------------------------------------
462 FUNCTION financial_product(x_resultout OUT NOCOPY VARCHAR2,
463 x_errormsg OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
464
465 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
466 l_financial_product VARCHAR2(150);
467
468 BEGIN
469 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
470 x_resultout := FND_API.G_RET_STS_SUCCESS;
471 BEGIN
472 SELECT PRODUCT.NAME
473 INTO l_financial_product
474 FROM OKL_PRODUCTS PRODUCT, OKL_LEASE_APPLICATIONS_B LAP, OKL_LEASE_QUOTES_B QUOTE
475 WHERE PRODUCT.ID = QUOTE.PRODUCT_ID
476 AND QUOTE.PRIMARY_QUOTE = 'Y'
477 AND QUOTE.PARENT_OBJECT_ID = LAP.ID
478 AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
479 AND LAP.ID = l_lease_app_id;
480
481
482 EXCEPTION
483 WHEN NO_DATA_FOUND THEN
484 l_financial_product := NULL;
485 WHEN OTHERS THEN
486 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
487 x_errormsg := sqlerrm;
488 END;
489
490 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_value :=
491 to_char(l_financial_product) ;
492
493 RETURN to_char(l_financial_product);
494 END financial_product ;
495
496 ---------------------------------------------
497 -- FUNCTION item
498 ---------------------------------------------
499 FUNCTION item(x_resultout OUT NOCOPY VARCHAR2,
500 x_errormsg OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
501
502 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
503 l_item VARCHAR2(40);
504
505 CURSOR citem IS
506 SELECT ITEM.CONCATENATED_SEGMENTS CONCATENATED_SEGMENTS, ASSET.ID
507 FROM MTL_SYSTEM_ITEMS_KFV ITEM, OKL_ASSET_COMPONENTS_B ASSET_COMP,
508 OKL_LEASE_APPLICATIONS_B LAP, OKL_ASSETS_B ASSET,
509 OKL_LEASE_QUOTES_B QUOTE
510 WHERE ITEM.INVENTORY_ITEM_ID = ASSET_COMP.INV_ITEM_ID
511 --Bug 7030452 :use inventory org id in the following condition
512 -- AND ITEM.ORGANIZATION_ID = LAP.ORG_ID -- ssdeshpa Bug # 6689249 added
513 AND ITEM.ORGANIZATION_ID = LAP.INV_ORG_ID
514 --Bug 7030452 :End
515 AND ASSET_COMP.ASSET_ID = ASSET.ID
516 AND ASSET_COMP.PRIMARY_COMPONENT = 'YES'
517 AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
518 AND QUOTE.PARENT_OBJECT_ID = LAP.ID
519 AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
520 AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
521 AND QUOTE.PRIMARY_QUOTE = 'Y'
522 AND LAP.ID = l_lease_app_id;
523
524 ln_seq_number NUMBER :=1;
525 BEGIN
526 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
527 x_resultout := FND_API.G_RET_STS_SUCCESS;
528 BEGIN
529
530 x_resultout := FND_API.G_RET_STS_SUCCESS;
531
532 FOR cItemRec IN cItem LOOP
533 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_id :=
534 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_id;
535 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_parent_data_point_id :=
536 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_parent_data_point_id;
537 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_sequence_number := ln_seq_number;
538 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_value :=
539 cItemRec.CONCATENATED_SEGMENTS;
540 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_value_id :=
541 cItemRec.ID;
542
543 ln_seq_number := ln_seq_number + 1;
544 END LOOP;
545
546 RETURN NULL;
547 EXCEPTION
548 WHEN NO_DATA_FOUND THEN
549 l_item := NULL;
550 WHEN OTHERS THEN
551 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
552 x_errormsg := sqlerrm;
553 END;
554 END item;
555
556 ---------------------------------------------
557 -- FUNCTION item_description
558 ---------------------------------------------
559 FUNCTION item_description(x_resultout OUT NOCOPY VARCHAR2,
560 x_errormsg OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
561
562 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
563 l_item_description VARCHAR2(1995);
564
565 CURSOR citemdesc IS
566 -- ssdeshpa Bug#6689249 start
567 SELECT ASSET_TL.SHORT_DESCRIPTION DESCRIPTION
568 -- ssdeshpa Bug#6689249 end
569 FROM OKL_ASSETS_TL ASSET_TL, OKL_ASSETS_B ASSET,
570 OKL_LEASE_APPLICATIONS_B LAP, OKL_LEASE_QUOTES_B QUOTE
571 WHERE ASSET_TL.ID = ASSET.ID
572 AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
573 AND QUOTE.PARENT_OBJECT_ID = LAP.ID
574 AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
575 AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
576 AND QUOTE.PRIMARY_QUOTE = 'Y'
577 AND ASSET_TL.LANGUAGE = USERENV('LANG')
578 AND LAP.ID = l_lease_app_id;
579
580 ln_seq_number NUMBER :=1;
581
582 BEGIN
583 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
584 x_resultout := FND_API.G_RET_STS_SUCCESS;
585 BEGIN
586
587 x_resultout := FND_API.G_RET_STS_SUCCESS;
588
589 FOR cItemDescRec IN cItemDesc LOOP
590 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_id :=
591 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_id;
592 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_parent_data_point_id :=
593 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_parent_data_point_id;
594 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_sequence_number := ln_seq_number;
595 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_value :=
596 cItemDescRec.description;
597
598 ln_seq_number := ln_seq_number + 1;
599
600 END LOOP;
601 RETURN NULL;
602
603 EXCEPTION
604 WHEN NO_DATA_FOUND THEN
605 l_item_description := NULL;
606 WHEN OTHERS THEN
607 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
608 x_errormsg := sqlerrm;
609 END;
610 END item_description ;
611
612 ---------------------------------------------
613 -- FUNCTION item_supplier
614 ---------------------------------------------
615 FUNCTION item_supplier(x_resultout OUT NOCOPY VARCHAR2,
616 x_errormsg OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
617
618 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
619 l_item_supplier VARCHAR2(120);
620
621 CURSOR cItemSupp IS
622 -- ssdeshpa Bug# 6689249 start
623 SELECT VENDOR.VENDOR_NAME SUPPLIER
624 -- ssdeshpa Bug# 6689249 end
625 FROM PO_VENDORS VENDOR, OKL_ASSET_COMPONENTS_B ASSET_COMP,
626 OKL_LEASE_APPLICATIONS_B LAP, OKL_ASSETS_B ASSET,
627 OKL_LEASE_QUOTES_B QUOTE
628 WHERE VENDOR.VENDOR_ID = ASSET_COMP.SUPPLIER_ID
629 AND ASSET_COMP.ASSET_ID = ASSET.ID
630 AND ASSET_COMP.PRIMARY_COMPONENT = 'YES'
631 AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
632 AND QUOTE.PARENT_OBJECT_ID = LAP.ID
633 AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
634 AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
635 AND QUOTE.PRIMARY_QUOTE = 'Y'
636 AND LAP.ID = l_lease_app_id;
637
638 ln_seq_number NUMBER :=1;
639 BEGIN
640 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
641 x_resultout := FND_API.G_RET_STS_SUCCESS;
642 BEGIN
643
644 x_resultout := FND_API.G_RET_STS_SUCCESS;
645
646 FOR cItemSuppRec IN cItemSupp LOOP
647 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_id :=
648 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_id;
649 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_parent_data_point_id :=
650 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_parent_data_point_id;
651 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_sequence_number := ln_seq_number;
652 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_value :=
653 cItemSuppRec.supplier;
654
655 ln_seq_number := ln_seq_number + 1;
656
657 END LOOP;
658 RETURN NULL;
659
660 EXCEPTION
661 WHEN NO_DATA_FOUND THEN
662 l_item_supplier := NULL;
663 WHEN OTHERS THEN
664 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
665 x_errormsg := sqlerrm;
666 END;
667 END item_supplier ;
668
669 ---------------------------------------------
670 -- FUNCTION model
671 ---------------------------------------------
672 FUNCTION model(x_resultout OUT NOCOPY VARCHAR2,
673 x_errormsg OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
674
675 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
676 l_model VARCHAR2(40);
677
678 CURSOR cModelNum IS
679 SELECT ASSET_COMP.MODEL_NUMBER MODEL_NUMBER
680 FROM OKL_ASSET_COMPONENTS_B ASSET_COMP, OKL_LEASE_APPLICATIONS_B LAP,
681 OKL_ASSETS_B ASSET, OKL_LEASE_QUOTES_B QUOTE
682 WHERE ASSET_COMP.ASSET_ID = ASSET.ID
683 AND ASSET_COMP.PRIMARY_COMPONENT = 'YES'
684 AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
685 AND QUOTE.PARENT_OBJECT_ID = LAP.ID
686 AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
687 AND QUOTE.PRIMARY_QUOTE = 'Y'
688 AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
689 AND LAP.ID = l_lease_app_id;
690
691 ln_seq_number NUMBER :=1;
692
693 BEGIN
694 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
695 x_resultout := FND_API.G_RET_STS_SUCCESS;
696 BEGIN
697
698 x_resultout := FND_API.G_RET_STS_SUCCESS;
699
700 FOR cModelNumRec IN cModelNum LOOP
701 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_id :=
702 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_id;
703 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_parent_data_point_id :=
704 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_parent_data_point_id;
705 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_sequence_number := ln_seq_number;
706 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_value :=
707 cModelNumRec.model_number;
708
709 ln_seq_number := ln_seq_number + 1;
710
711 END LOOP;
712 RETURN NULL;
713
714 EXCEPTION
715 WHEN NO_DATA_FOUND THEN
716 l_model := NULL;
717 WHEN OTHERS THEN
718 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
719 x_errormsg := sqlerrm;
720 END;
721 END model ;
722
723 ---------------------------------------------
724 -- FUNCTION manufacturer
725 ---------------------------------------------
726 FUNCTION manufacturer(x_resultout OUT NOCOPY VARCHAR2,
727 x_errormsg OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
728
729 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
730 --Bug# 9590286
731 l_manufacturer VARCHAR2(360);
732
733 CURSOR cMfgName is
734 SELECT ASSET_COMP.MANUFACTURER_NAME MANUFACTURER_NAME
735 FROM OKL_ASSET_COMPONENTS_B ASSET_COMP, OKL_LEASE_APPLICATIONS_B LAP,
736 OKL_ASSETS_B ASSET, OKL_LEASE_QUOTES_B QUOTE
737 WHERE ASSET_COMP.ASSET_ID = ASSET.ID
738 AND ASSET_COMP.PRIMARY_COMPONENT = 'YES'
739 AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
740 AND QUOTE.PARENT_OBJECT_ID = LAP.ID
741 AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
742 AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
743 AND QUOTE.PRIMARY_QUOTE = 'Y'
744 AND LAP.ID = l_lease_app_id;
745
746 ln_seq_number NUMBER :=1;
747
748 BEGIN
749 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
750 x_resultout := FND_API.G_RET_STS_SUCCESS;
751 BEGIN
752
753 x_resultout := FND_API.G_RET_STS_SUCCESS;
754
755 FOR cMfgNameRec IN cMfgName LOOP
756 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_id :=
757 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_id;
758 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_parent_data_point_id :=
759 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_parent_data_point_id;
760 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_sequence_number := ln_seq_number;
761 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_value :=
762 cMfgNameRec.manufacturer_name;
763
764 ln_seq_number := ln_seq_number + 1;
765
766 END LOOP;
767 RETURN NULL;
768
769 EXCEPTION
770 WHEN NO_DATA_FOUND THEN
771 l_manufacturer := NULL;
772 WHEN OTHERS THEN
773 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
774 x_errormsg := sqlerrm;
775 END;
776 END manufacturer;
777
778 ---------------------------------------------
779 -- FUNCTION year_of_manufacture
780 ---------------------------------------------
781 FUNCTION year_of_manufacture(x_resultout OUT NOCOPY VARCHAR2,
782 x_errormsg OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
783
784 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
785 l_year_of_manufacture NUMBER;
786
787 CURSOR cYearMfg IS
788 SELECT ASSET_COMP.YEAR_MANUFACTURED YEAR_MANUFACTURED
789 FROM OKL_ASSET_COMPONENTS_B ASSET_COMP, OKL_LEASE_APPLICATIONS_B LAP,
790 OKL_ASSETS_B ASSET, OKL_LEASE_QUOTES_B QUOTE
791 WHERE ASSET_COMP.ASSET_ID = ASSET.ID
792 AND ASSET_COMP.PRIMARY_COMPONENT = 'YES'
793 AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
794 AND QUOTE.PARENT_OBJECT_ID = LAP.ID
795 AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
796 AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
797 AND QUOTE.PRIMARY_QUOTE = 'Y'
798 AND LAP.ID = l_lease_app_id;
799
800 ln_seq_number NUMBER :=1;
801
802 BEGIN
803 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
804 x_resultout := FND_API.G_RET_STS_SUCCESS;
805 BEGIN
806 x_resultout := FND_API.G_RET_STS_SUCCESS;
807
808 FOR cYearMfgRec IN cYearMfg LOOP
809 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_id :=
810 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_id;
811 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_parent_data_point_id :=
812 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_parent_data_point_id;
813 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_sequence_number := ln_seq_number;
814 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_value :=
815 cYearMfgRec.year_manufactured;
816
817 ln_seq_number := ln_seq_number + 1;
818
819 END LOOP;
820 RETURN NULL;
821
822 EXCEPTION
823 WHEN NO_DATA_FOUND THEN
824 l_year_of_manufacture := NULL;
825 WHEN OTHERS THEN
826 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
827 x_errormsg := sqlerrm;
828 END;
829 END year_of_manufacture ;
830
831 ---------------------------------------------
832 -- FUNCTION no_of_units
833 ---------------------------------------------
834 FUNCTION no_of_units(x_resultout OUT NOCOPY VARCHAR2,
835 x_errormsg OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
836
837 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
838 l_no_of_units NUMBER;
839
840 CURSOR cNoOfUnits IS
841 SELECT ASSET_COMP.NUMBER_OF_UNITS NUMBER_OF_UNITS
842 FROM OKL_ASSET_COMPONENTS_B ASSET_COMP, OKL_LEASE_APPLICATIONS_B LAP,
843 OKL_ASSETS_B ASSET, OKL_LEASE_QUOTES_B QUOTE
844 WHERE ASSET_COMP.ASSET_ID = ASSET.ID
845 AND ASSET_COMP.PRIMARY_COMPONENT = 'YES'
846 AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
847 AND QUOTE.PARENT_OBJECT_ID = LAP.ID
848 AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
849 AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
850 AND QUOTE.PRIMARY_QUOTE = 'Y'
851 AND LAP.ID = l_lease_app_id;
852
853 ln_seq_number NUMBER :=1;
854 BEGIN
855 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
856 x_resultout := FND_API.G_RET_STS_SUCCESS;
857 BEGIN
858 x_resultout := FND_API.G_RET_STS_SUCCESS;
859
860 FOR cNoOfUnitsRec IN cNoOfUnits LOOP
861 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_id :=
862 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_id;
863 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_parent_data_point_id :=
864 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_parent_data_point_id;
865 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_sequence_number := ln_seq_number;
866 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_value :=
867 cNoOfUnitsRec.number_of_units;
868
869 ln_seq_number := ln_seq_number + 1;
870
871 END LOOP;
872 RETURN NULL;
873
874 EXCEPTION
875 WHEN NO_DATA_FOUND THEN
876 l_no_of_units := NULL;
877 WHEN OTHERS THEN
878 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
879 x_errormsg := sqlerrm;
880 END;
881 END no_of_units ;
882
883 ---------------------------------------------
884 -- FUNCTION unit_cost
885 ---------------------------------------------
886 FUNCTION unit_cost(x_resultout OUT NOCOPY VARCHAR2,
887 x_errormsg OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
888
889 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
890 l_unit_cost NUMBER;
891
892 CURSOR cUnitCost IS
893 SELECT ASSET_COMP.UNIT_COST UNIT_COST
894 FROM OKL_ASSET_COMPONENTS_B ASSET_COMP, OKL_LEASE_APPLICATIONS_B LAP,
895 OKL_ASSETS_B ASSET, OKL_LEASE_QUOTES_B QUOTE
896 WHERE ASSET_COMP.ASSET_ID = ASSET.ID
897 AND ASSET_COMP.PRIMARY_COMPONENT = 'YES'
898 AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
899 AND QUOTE.PARENT_OBJECT_ID = LAP.ID
900 AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
901 AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
902 AND QUOTE.PRIMARY_QUOTE = 'Y'
903 AND LAP.ID = l_lease_app_id;
904
905 ln_seq_number NUMBER :=1;
906
907
908 BEGIN
909 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
910 x_resultout := FND_API.G_RET_STS_SUCCESS;
911 BEGIN
912 x_resultout := FND_API.G_RET_STS_SUCCESS;
913
914 FOR cUnitCostRec IN cUnitCost LOOP
915 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_id :=
916 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_id;
917 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_parent_data_point_id :=
918 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_parent_data_point_id;
919 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_sequence_number := ln_seq_number;
920 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_value :=
921 cUnitCostRec.unit_cost;
922
923 ln_seq_number := ln_seq_number + 1;
924
925 END LOOP;
926 RETURN NULL;
927
928 EXCEPTION
929 WHEN NO_DATA_FOUND THEN
930 l_unit_cost := NULL;
931 WHEN OTHERS THEN
932 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
933 x_errormsg := sqlerrm;
934 END;
935 END unit_cost ;
936
937 ---------------------------------------------
938 -- FUNCTION install_site
939 ---------------------------------------------
940 FUNCTION install_site(x_resultout OUT NOCOPY VARCHAR2,
941 x_errormsg OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
942
943 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
944 l_install_site VARCHAR2(30);
945
946 CURSOR cInstallSite IS
947 SELECT PARTY_SITE.PARTY_SITE_NUMBER PARTY_SITE_NUMBER
948 FROM HZ_PARTY_SITES PARTY_SITE, OKL_LEASE_APPLICATIONS_B LAP,
949 OKL_ASSETS_B ASSET, OKL_LEASE_QUOTES_B QUOTE
950 WHERE PARTY_SITE.PARTY_SITE_ID = ASSET.INSTALL_SITE_ID
951 AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
952 AND QUOTE.PARENT_OBJECT_ID = LAP.ID
953 AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
954 AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
955 AND QUOTE.PRIMARY_QUOTE = 'Y'
956 AND LAP.ID = l_lease_app_id;
957
958 ln_seq_number NUMBER :=1;
959
960 BEGIN
961 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
962 x_resultout := FND_API.G_RET_STS_SUCCESS;
963 BEGIN
964 x_resultout := FND_API.G_RET_STS_SUCCESS;
965
966 FOR cInstallSiteRec IN cInstallSite LOOP
967 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_id :=
968 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_id;
969 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_parent_data_point_id :=
970 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_parent_data_point_id;
971 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_sequence_number := ln_seq_number;
972 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_value :=
973 cInstallSiteRec.party_site_number;
974
975 ln_seq_number := ln_seq_number + 1;
976
977 END LOOP;
978 RETURN NULL;
979
980 EXCEPTION
981 WHEN NO_DATA_FOUND THEN
982 l_install_site := NULL;
983 WHEN OTHERS THEN
984 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
985 x_errormsg := sqlerrm;
986 END;
987 END install_site ;
988
989 ---------------------------------------------
990 -- FUNCTION usage_of_equipment
991 ---------------------------------------------
992 FUNCTION usage_of_equipment(x_resultout OUT NOCOPY VARCHAR2,
993 x_errormsg OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
994
995 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
996 l_usage_of_equipment VARCHAR2(30);
997
998 BEGIN
999 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
1000 x_resultout := FND_API.G_RET_STS_SUCCESS;
1001 BEGIN
1002 -- gboomina Bug 7110500 - Start
1003 SELECT QUOTE.USAGE_CATEGORY --QUOTE.USAGE_INDUSTRY_CLASS
1004 INTO l_usage_of_equipment
1005 FROM OKL_LEASE_APPLICATIONS_B LAP, OKL_LEASE_QUOTES_B QUOTE
1006 WHERE QUOTE.PARENT_OBJECT_ID = LAP.ID
1007 AND QUOTE.PRIMARY_QUOTE = 'Y'
1008 AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
1009 AND LAP.ID = l_lease_app_id;
1010 -- gboomina Bug 7110500 - End
1011
1012
1013 EXCEPTION
1014 WHEN NO_DATA_FOUND THEN
1015 l_usage_of_equipment := NULL;
1016 WHEN OTHERS THEN
1017 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
1018 x_errormsg := sqlerrm;
1019 END;
1020
1021 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_value :=
1022 to_char(l_usage_of_equipment) ;
1023
1024 RETURN to_char(l_usage_of_equipment );
1025 END usage_of_equipment ;
1026
1027 ---------------------------------------------
1028 -- FUNCTION usage_industry
1029 ---------------------------------------------
1030 FUNCTION usage_industry(x_resultout OUT NOCOPY VARCHAR2,
1031 x_errormsg OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
1032
1033 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
1034 l_usage_industry VARCHAR2(1995);
1035
1036 BEGIN
1037 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
1038 x_resultout := FND_API.G_RET_STS_SUCCESS;
1039 BEGIN
1040 -- gboomina Bug 7110500 - Start
1041 /*
1042 SELECT QUOTE.USAGE_INDUSTRY_CODE
1043 INTO l_usage_industry
1044 FROM OKL_LEASE_APPLICATIONS_B LAP, OKL_LEASE_QUOTES_B QUOTE
1045 WHERE QUOTE.PARENT_OBJECT_ID = LAP.ID
1046 AND QUOTE.PRIMARY_QUOTE = 'Y'
1047 AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
1048 AND LAP.ID = l_lease_app_id;
1049 */
1050
1051 SELECT INDC.MEANING ||' - '||IND.MEANING USAGE_INDUSTRY
1052 INTO l_usage_industry
1053 FROM OKL_LEASE_APPLICATIONS_B LAP,AR_LOOKUPS INDC, AR_LOOKUPS IND
1054 WHERE INDC.LOOKUP_TYPE(+)='SIC_CODE_TYPE'
1055 AND INDC.LOOKUP_CODE(+)=LAP.INDUSTRY_CLASS
1056 AND IND.LOOKUP_TYPE(+)=LAP.INDUSTRY_CLASS
1057 AND IND.LOOKUP_CODE(+)=LAP.INDUSTRY_CODE
1058 AND LAP.id= l_lease_app_id;
1059
1060 -- gboomina Bug 7110500 - End
1061
1062 EXCEPTION
1063 WHEN NO_DATA_FOUND THEN
1064 l_usage_industry := NULL;
1065 WHEN OTHERS THEN
1066 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
1067 x_errormsg := sqlerrm;
1068 END;
1069
1070 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_value :=
1071 to_char(l_usage_industry) ;
1072
1073 RETURN to_char(l_usage_industry);
1074 END usage_industry ;
1075
1076 ---------------------------------------------
1077 -- FUNCTION usage_category
1078 ---------------------------------------------
1079 FUNCTION usage_category(x_resultout OUT NOCOPY VARCHAR2,
1080 x_errormsg OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
1081
1082 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
1083 l_usage_category VARCHAR2(30);
1084
1085 BEGIN
1086 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
1087 x_resultout := FND_API.G_RET_STS_SUCCESS;
1088 BEGIN
1089 SELECT QUOTE.USAGE_CATEGORY
1090 INTO l_usage_category
1091 FROM OKL_LEASE_APPLICATIONS_B LAP, OKL_LEASE_QUOTES_B QUOTE
1092 WHERE QUOTE.PARENT_OBJECT_ID = LAP.ID
1093 AND QUOTE.PRIMARY_QUOTE = 'Y'
1094 AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
1095 AND LAP.ID = l_lease_app_id;
1096
1097
1098 EXCEPTION
1099 WHEN NO_DATA_FOUND THEN
1100 l_usage_category := NULL;
1101 WHEN OTHERS THEN
1102 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
1103 x_errormsg := sqlerrm;
1104 END;
1105
1106 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_value :=
1107 to_char(l_usage_category) ;
1108
1109 RETURN to_char(l_usage_category);
1110 END usage_category ;
1111
1112 ---------------------------------------------
1113 -- FUNCTION usage_amount
1114 ---------------------------------------------
1115 FUNCTION usage_amount(x_resultout OUT NOCOPY VARCHAR2,
1116 x_errormsg OUT NOCOPY VARCHAR2) RETURN NUMBER IS
1117
1118 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
1119 l_usage_amount NUMBER;
1120
1121 BEGIN
1122 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
1123 x_resultout := FND_API.G_RET_STS_SUCCESS;
1124 BEGIN
1125 -- gboomina Bug 7110500 - Start
1126 /*
1127 SELECT QUOTE.USAGE_AMOUNT
1128 INTO l_usage_amount
1129 FROM OKL_LEASE_APPLICATIONS_B LAP, OKL_LEASE_QUOTES_B QUOTE
1130 WHERE QUOTE.PARENT_OBJECT_ID = LAP.ID
1131 AND QUOTE.PRIMARY_QUOTE = 'Y'
1132 AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
1133 AND LAP.ID = l_lease_app_id;
1134 */
1135 SELECT LOP.USAGE_AMOUNT
1136 INTO l_usage_amount
1137 FROM OKL_LEASE_APPLICATIONS_B LAP, OKL_LEASE_OPPORTUNITIES_B LOP
1138 WHERE LOP.ID= LAP.LEASE_OPPORTUNITY_ID
1139 AND LAP.ID = l_lease_app_id;
1140
1141 -- gboomina Bug 7110500 - End
1142
1143 EXCEPTION
1144 WHEN NO_DATA_FOUND THEN
1145 l_usage_amount := NULL;
1146 WHEN OTHERS THEN
1147 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
1148 x_errormsg := sqlerrm;
1149 END;
1150
1151 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_value :=
1152 l_usage_amount;
1153
1154 RETURN l_usage_amount;
1155 END usage_amount ;
1156
1157 ---------------------------------------------
1158 -- FUNCTION add_on_item
1159 ---------------------------------------------
1160 FUNCTION add_on_item(x_resultout OUT NOCOPY VARCHAR2,
1161 x_errormsg OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
1162
1163 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
1164 l_asset_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_value_id);
1165
1166 l_add_on_item VARCHAR2(40);
1167
1168 CURSOR cAddOnItem IS
1169 SELECT ITEM.CONCATENATED_SEGMENTS ADD_ON_ITEM
1170 FROM MTL_SYSTEM_ITEMS_KFV ITEM, OKL_ASSET_COMPONENTS_B ASSET_COMP
1171 WHERE ITEM.INVENTORY_ITEM_ID = ASSET_COMP.INV_ITEM_ID
1172 AND ITEM.ORGANIZATION_ID = OKL_SYSTEM_PARAMS_ALL_PUB.get_system_param_value(OKL_SYSTEM_PARAMS_ALL_PUB.G_ITEM_INV_ORG_ID)
1173 AND ASSET_COMP.ASSET_ID = l_asset_id
1174 AND ASSET_COMP.PRIMARY_COMPONENT = 'NO';
1175
1176 ln_seq_number NUMBER :=1;
1177 BEGIN
1178 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
1179 x_resultout := FND_API.G_RET_STS_SUCCESS;
1180 BEGIN
1181 x_resultout := FND_API.G_RET_STS_SUCCESS;
1182
1183 FOR cAddOnItemRec IN cAddOnItem LOOP
1184 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_id :=
1185 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_id;
1186 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_parent_data_point_id := l_asset_id;
1187 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_sequence_number := ln_seq_number;
1188 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_value :=
1189 cAddOnItemRec.add_on_item;
1190
1191 ln_seq_number := ln_seq_number + 1;
1192 END LOOP;
1193
1194 RETURN NULL;
1195
1196 EXCEPTION
1197 WHEN NO_DATA_FOUND THEN
1198 l_add_on_item := NULL;
1199 WHEN OTHERS THEN
1200 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
1201 x_errormsg := sqlerrm;
1202 END;
1203 END add_on_item ;
1204
1205 ---------------------------------------------
1206 -- FUNCTION add_on_item_description
1207 ---------------------------------------------
1208 FUNCTION add_on_item_description(x_resultout OUT NOCOPY VARCHAR2,
1209 x_errormsg OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
1210
1211 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
1212 l_asset_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_value_id);
1213
1214 l_add_on_item_description VARCHAR2(1995);
1215
1216 CURSOR cItemDescription IS
1217 SELECT ITEM.CONCATENATED_SEGMENTS ADD_ON_ITEM
1218 FROM MTL_SYSTEM_ITEMS_KFV ITEM, OKL_ASSET_COMPONENTS_B ASSET_COMP
1219 WHERE ITEM.INVENTORY_ITEM_ID = ASSET_COMP.INV_ITEM_ID
1220 AND ITEM.ORGANIZATION_ID = OKL_SYSTEM_PARAMS_ALL_PUB.get_system_param_value(OKL_SYSTEM_PARAMS_ALL_PUB.G_ITEM_INV_ORG_ID)
1221 AND ASSET_COMP.ASSET_ID = l_asset_id
1222 AND ASSET_COMP.PRIMARY_COMPONENT = 'NO';
1223
1224 ln_seq_number NUMBER :=1;
1225 BEGIN
1226 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
1227 x_resultout := FND_API.G_RET_STS_SUCCESS;
1228 BEGIN
1229
1230 x_resultout := FND_API.G_RET_STS_SUCCESS;
1231
1232 FOR cItemDescriptionRec IN cItemDescription LOOP
1233 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_id :=
1234 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_id;
1235 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_parent_data_point_id :=
1236 l_asset_id;
1237 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_sequence_number := ln_seq_number;
1238 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_value :=
1239 cItemDescriptionRec.add_on_item;
1240
1241 ln_seq_number := ln_seq_number + 1;
1242
1243 END LOOP;
1244 RETURN NULL;
1245
1246 EXCEPTION
1247 WHEN NO_DATA_FOUND THEN
1248 l_add_on_item_description := NULL;
1249 WHEN OTHERS THEN
1250 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
1251 x_errormsg := sqlerrm;
1252 END;
1253 END add_on_item_description ;
1254
1255 ---------------------------------------------
1256 -- FUNCTION add_on_item_supplier
1257 ---------------------------------------------
1258 FUNCTION add_on_item_supplier(x_resultout OUT NOCOPY VARCHAR2,
1259 x_errormsg OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
1260
1261 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
1262 l_asset_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_value_id);
1263
1264 l_add_on_item_supplier VARCHAR2(30);
1265
1266 CURSOR cItemSupplier IS
1267 SELECT VENDOR.VENDOR_NAME ITEM_SUPPLIER
1268 FROM PO_VENDORS VENDOR, OKL_ASSET_COMPONENTS_B ASSET_COMP
1269 WHERE VENDOR.VENDOR_ID = ASSET_COMP.SUPPLIER_ID
1270 AND ASSET_COMP.ASSET_ID = l_asset_id
1271 AND ASSET_COMP.PRIMARY_COMPONENT = 'NO';
1272
1273 ln_seq_number NUMBER :=1;
1274 BEGIN
1275 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
1276 x_resultout := FND_API.G_RET_STS_SUCCESS;
1277 BEGIN
1278 x_resultout := FND_API.G_RET_STS_SUCCESS;
1279
1280 FOR cItemSupplierRec IN cItemSupplier LOOP
1281 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_id :=
1282 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_id;
1283 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_parent_data_point_id :=
1284 l_asset_id;
1285 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_sequence_number := ln_seq_number;
1286 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_value :=
1287 cItemSupplierRec.item_supplier;
1288
1289 ln_seq_number := ln_seq_number + 1;
1290
1291 END LOOP;
1292 RETURN NULL;
1293
1294 EXCEPTION
1295 WHEN NO_DATA_FOUND THEN
1296 l_add_on_item_supplier := NULL;
1297 WHEN OTHERS THEN
1298 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
1299 x_errormsg := sqlerrm;
1300 END;
1301 END add_on_item_supplier ;
1302
1303 ---------------------------------------------
1304 -- FUNCTION add_on_item_model
1305 ---------------------------------------------
1306 FUNCTION add_on_item_model(x_resultout OUT NOCOPY VARCHAR2,
1307 x_errormsg OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
1308
1309 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
1310 l_asset_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_value_id);
1311
1312 l_add_on_item_model VARCHAR2(40);
1313
1314 CURSOR cItemModel IS
1315 SELECT ASSET_COMP.MODEL_NUMBER MODEL_NUMBER
1316 FROM OKL_ASSET_COMPONENTS_B ASSET_COMP
1317 WHERE ASSET_COMP.ASSET_ID = l_asset_id
1318 AND ASSET_COMP.PRIMARY_COMPONENT = 'NO';
1319
1320 ln_seq_number NUMBER :=1;
1321
1322 BEGIN
1323 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
1324 x_resultout := FND_API.G_RET_STS_SUCCESS;
1325 BEGIN
1326
1327 x_resultout := FND_API.G_RET_STS_SUCCESS;
1328
1329 FOR cItemModelRec IN cItemModel LOOP
1330 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_id :=
1331 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_id;
1332 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_parent_data_point_id := l_asset_id;
1333 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_sequence_number := ln_seq_number;
1334 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_value :=
1335 cItemModelRec.model_number;
1336
1337 ln_seq_number := ln_seq_number + 1;
1338
1339 END LOOP;
1340 RETURN NULL;
1341
1342 EXCEPTION
1343 WHEN NO_DATA_FOUND THEN
1344 l_add_on_item_model := NULL;
1345 WHEN OTHERS THEN
1346 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
1347 x_errormsg := sqlerrm;
1348 END;
1349 END add_on_item_model ;
1350
1351 ---------------------------------------------
1352 -- FUNCTION add_on_item_manufacturer
1353 ---------------------------------------------
1354 FUNCTION add_on_item_manufacturer(x_resultout OUT NOCOPY VARCHAR2,
1355 x_errormsg OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
1356
1357 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
1358 l_asset_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_value_id);
1359
1360 --Bug# 9590286
1361 l_add_on_item_manufacturer VARCHAR2(360);
1362
1363 CURSOR cItemMfg IS
1364 SELECT ASSET_COMP.MANUFACTURER_NAME MANUFACTURER_NAME
1365 FROM OKL_ASSET_COMPONENTS_B ASSET_COMP
1366 WHERE ASSET_COMP.ASSET_ID = l_asset_id
1367 AND ASSET_COMP.PRIMARY_COMPONENT = 'NO';
1368
1369 ln_seq_number NUMBER :=1;
1370 BEGIN
1371 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
1372 x_resultout := FND_API.G_RET_STS_SUCCESS;
1373 BEGIN
1374
1375 x_resultout := FND_API.G_RET_STS_SUCCESS;
1376
1377 FOR cItemMfgRec IN cItemMfg LOOP
1378 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_id :=
1379 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_id;
1380 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_parent_data_point_id :=
1381 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_parent_data_point_id;
1382 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_sequence_number := ln_seq_number;
1383 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_value :=
1384 cItemMfgRec.manufacturer_name;
1385
1386 ln_seq_number := ln_seq_number + 1;
1387
1388 END LOOP;
1389 RETURN NULL;
1390
1391 EXCEPTION
1392 WHEN NO_DATA_FOUND THEN
1393 l_add_on_item_manufacturer := NULL;
1394 WHEN OTHERS THEN
1395 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
1396 x_errormsg := sqlerrm;
1397 END;
1398 END add_on_item_manufacturer ;
1399
1400 ---------------------------------------------
1401 -- FUNCTION add_on_item_amount
1402 ---------------------------------------------
1403 FUNCTION add_on_item_amount(x_resultout OUT NOCOPY VARCHAR2,
1404 x_errormsg OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
1405
1406 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
1407 l_asset_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_value_id);
1408
1409 l_add_on_item_amount NUMBER;
1410
1411 CURSOR cAddOnItemAmount IS
1412 SELECT ASSET_COMP.UNIT_COST UNIT_COST
1413 FROM OKL_ASSET_COMPONENTS_B ASSET_COMP
1414 WHERE ASSET_COMP.ASSET_ID = l_asset_id
1415 AND ASSET_COMP.PRIMARY_COMPONENT = 'NO';
1416
1417 ln_seq_number NUMBER :=1;
1418 BEGIN
1419 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
1420 x_resultout := FND_API.G_RET_STS_SUCCESS;
1421 BEGIN
1422
1423 x_resultout := FND_API.G_RET_STS_SUCCESS;
1424
1425 FOR cAddOnItemAmountRec IN cAddOnItemAmount LOOP
1426 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_id :=
1427 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_id;
1428 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_parent_data_point_id := l_asset_id;
1429 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_sequence_number := ln_seq_number;
1430 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_value :=
1431 cAddOnItemAmountRec.unit_cost;
1432
1433 ln_seq_number := ln_seq_number + 1;
1434
1435 END LOOP;
1436 RETURN NULL;
1437
1438 EXCEPTION
1439 WHEN NO_DATA_FOUND THEN
1440 l_add_on_item_amount := NULL;
1441 WHEN OTHERS THEN
1442 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
1443 x_errormsg := sqlerrm;
1444 END;
1445 END add_on_item_amount ;
1446
1447 ---------------------------------------------
1448 -- FUNCTION asset_residual_value
1449 ---------------------------------------------
1450 FUNCTION asset_residual_value(x_resultout OUT NOCOPY VARCHAR2,
1451 x_errormsg OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
1452
1453 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
1454 l_add_on_item_residual_values NUMBER;
1455
1456 CURSOR cItemResVal IS
1457 SELECT NVL(ASSET.END_OF_TERM_VALUE,ASSET.END_OF_TERM_VALUE_DEFAULT) END_OF_TERM_VALUE
1458 FROM OKL_LEASE_APPLICATIONS_B LAP, OKL_ASSETS_B ASSET, OKL_LEASE_QUOTES_B QUOTE
1459 WHERE ASSET.PARENT_OBJECT_ID = QUOTE.ID
1460 AND QUOTE.PARENT_OBJECT_ID = LAP.ID
1461 AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
1462 AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
1463 AND QUOTE.PRIMARY_QUOTE = 'Y'
1464 AND LAP.ID = l_lease_app_id;
1465
1466 ln_seq_number NUMBER :=1;
1467 BEGIN
1468 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
1469 x_resultout := FND_API.G_RET_STS_SUCCESS;
1470 BEGIN
1471
1472 x_resultout := FND_API.G_RET_STS_SUCCESS;
1473
1474 FOR cItemResValRec IN cItemResVal LOOP
1475 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_id :=
1476 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_id;
1477 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_parent_data_point_id :=
1478 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_parent_data_point_id;
1479 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_sequence_number := ln_seq_number;
1480 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_value :=
1481 cItemResValRec.END_OF_TERM_VALUE;
1482
1483 ln_seq_number := ln_seq_number + 1;
1484
1485 END LOOP;
1486 RETURN NULL;
1487
1488 EXCEPTION
1489 WHEN NO_DATA_FOUND THEN
1490 l_add_on_item_residual_values := NULL;
1491 WHEN OTHERS THEN
1492 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
1493 x_errormsg := sqlerrm;
1494 END;
1495 END asset_residual_value ;
1496
1497 ---------------------------------------------
1498 -- FUNCTION down_payment_amount
1499 ---------------------------------------------
1500 FUNCTION down_payment_amount(x_resultout OUT NOCOPY VARCHAR2,
1501 x_errormsg OUT NOCOPY VARCHAR2) RETURN NUMBER IS
1502
1503 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
1504 l_down_payment_amount NUMBER;
1505
1506 BEGIN
1507 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
1508 x_resultout := FND_API.G_RET_STS_SUCCESS;
1509 BEGIN
1510 SELECT NVL(SUM(ADJ.VALUE), 0)
1511 INTO l_down_payment_amount
1512 FROM OKL_LEASE_APPLICATIONS_B LAP, OKL_ASSETS_B ASSET,
1513 OKL_COST_ADJUSTMENTS_B ADJ, OKL_LEASE_QUOTES_B QUOTE
1514 WHERE ADJ.ADJUSTMENT_SOURCE_TYPE = 'DOWN_PAYMENT'
1515 AND ADJ.PARENT_OBJECT_CODE = 'ASSET'
1516 AND ADJ.PARENT_OBJECT_ID = ASSET.ID
1517 AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
1518 AND QUOTE.PARENT_OBJECT_ID = LAP.ID
1519 AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
1520 AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
1521 AND QUOTE.PRIMARY_QUOTE = 'Y'
1522 AND LAP.ID = l_lease_app_id;
1523
1524 EXCEPTION
1525 WHEN NO_DATA_FOUND THEN
1526 l_down_payment_amount := NULL;
1527 WHEN OTHERS THEN
1528 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
1529 x_errormsg := sqlerrm;
1530 END;
1531
1532 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_value :=
1533 l_down_payment_amount;
1534 RETURN l_down_payment_amount;
1535 END down_payment_amount ;
1536
1537 ---------------------------------------------
1538 -- FUNCTION subsidy_amount
1539 ---------------------------------------------
1540 FUNCTION subsidy_amount(x_resultout OUT NOCOPY VARCHAR2,
1541 x_errormsg OUT NOCOPY VARCHAR2) RETURN NUMBER IS
1542
1543 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
1544 l_subsidy_amount NUMBER;
1545
1546 BEGIN
1547 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
1548 x_resultout := FND_API.G_RET_STS_SUCCESS;
1549 BEGIN
1550 SELECT SUM(DECODE(ADJ.VALUE, NULL, ADJ.DEFAULT_SUBSIDY_AMOUNT, ADJ.VALUE)) SUBSIDY_AMOUNT
1551 INTO l_subsidy_amount
1552 FROM OKL_LEASE_APPLICATIONS_B LAP, OKL_ASSETS_B ASSET,
1553 OKL_COST_ADJUSTMENTS_B ADJ, OKL_LEASE_QUOTES_B QUOTE
1554 WHERE ADJ.ADJUSTMENT_SOURCE_TYPE = 'SUBSIDY'
1555 AND ADJ.PARENT_OBJECT_CODE = 'ASSET'
1556 AND ADJ.PARENT_OBJECT_ID = ASSET.ID
1557 AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
1558 AND QUOTE.PARENT_OBJECT_ID = LAP.ID
1559 AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
1560 AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
1561 AND QUOTE.PRIMARY_QUOTE = 'Y'
1562 AND LAP.ID = l_lease_app_id;
1563
1564 EXCEPTION
1565 WHEN NO_DATA_FOUND THEN
1566 l_subsidy_amount := NULL;
1567 WHEN OTHERS THEN
1568 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
1569 x_errormsg := sqlerrm;
1570 END;
1571
1572 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_value :=
1573 l_subsidy_amount ;
1574
1575 RETURN l_subsidy_amount;
1576 END subsidy_amount ;
1577
1578 ---------------------------------------------
1579 -- FUNCTION trade_in_amount
1580 ---------------------------------------------
1581 FUNCTION trade_in_amount(x_resultout OUT NOCOPY VARCHAR2,
1582 x_errormsg OUT NOCOPY VARCHAR2) RETURN NUMBER IS
1583
1584 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
1585 l_trade_in_amount NUMBER;
1586
1587 BEGIN
1588 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
1589 x_resultout := FND_API.G_RET_STS_SUCCESS;
1590 BEGIN
1591 SELECT NVL(SUM(ADJ.VALUE), 0)
1592 INTO l_trade_in_amount
1593 FROM OKL_LEASE_APPLICATIONS_B LAP, OKL_ASSETS_B ASSET,
1594 OKL_COST_ADJUSTMENTS_B ADJ, OKL_LEASE_QUOTES_B QUOTE
1595 WHERE ADJ.ADJUSTMENT_SOURCE_TYPE = 'TRADEIN'
1596 AND ADJ.PARENT_OBJECT_CODE = 'ASSET'
1597 AND ADJ.PARENT_OBJECT_ID = ASSET.ID
1598 AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
1599 AND QUOTE.PARENT_OBJECT_ID = LAP.ID
1600 AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
1601 AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
1602 AND QUOTE.PRIMARY_QUOTE = 'Y'
1603 AND LAP.ID = l_lease_app_id;
1604
1605 EXCEPTION
1606 WHEN NO_DATA_FOUND THEN
1607 l_trade_in_amount := NULL;
1608 WHEN OTHERS THEN
1609 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
1610 x_errormsg := sqlerrm;
1611 END;
1612
1613 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_value :=
1614 l_trade_in_amount;
1615
1616 RETURN l_trade_in_amount;
1617 END trade_in_amount ;
1618
1619 ---------------------------------------------
1620 -- FUNCTION trade_in_asset_number
1621 ---------------------------------------------
1622 FUNCTION trade_in_asset_number(x_resultout OUT NOCOPY VARCHAR2,
1623 x_errormsg OUT NOCOPY VARCHAR2) RETURN NUMBER IS
1624
1625 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
1626 l_trade_in_asset_number VARCHAR2(15);
1627
1628 CURSOR cAssetNumber IS
1629 SELECT ASSET.ASSET_NUMBER
1630 FROM OKL_LEASE_APPLICATIONS_B LAP, OKL_ASSETS_B ASSET,
1631 OKL_LEASE_QUOTES_B QUOTE, OKL_COST_ADJUSTMENTS_B ADJ
1632 WHERE ADJ.ADJUSTMENT_SOURCE_TYPE = 'TRADEIN'
1633 AND ADJ.PARENT_OBJECT_CODE = 'ASSET'
1634 AND ADJ.PARENT_OBJECT_ID = ASSET.ID
1635 AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
1636 AND QUOTE.PARENT_OBJECT_ID = LAP.ID
1637 AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
1638 AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
1639 AND QUOTE.PRIMARY_QUOTE = 'Y'
1640 AND LAP.ID = l_lease_app_id;
1641
1642 ln_seq_number NUMBER :=1;
1643 BEGIN
1644 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
1645 x_resultout := FND_API.G_RET_STS_SUCCESS;
1646 BEGIN
1647 x_resultout := FND_API.G_RET_STS_SUCCESS;
1648
1649 FOR cAssetNumberRec IN cAssetNumber
1650 LOOP
1651 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_id :=
1652 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_id;
1653 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_parent_data_point_id :=
1654 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_parent_data_point_id;
1655 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_sequence_number := ln_seq_number;
1656 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_value :=
1657 cAssetNumberRec.ASSET_NUMBER;
1658
1659 ln_seq_number := ln_seq_number + 1;
1660
1661 END LOOP;
1662 RETURN NULL;
1663
1664 EXCEPTION
1665 WHEN NO_DATA_FOUND THEN
1666 l_trade_in_asset_number := NULL;
1667 WHEN OTHERS THEN
1668 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
1669 x_errormsg := sqlerrm;
1670 END;
1671
1672 END trade_in_asset_number ;
1673
1674 ---------------------------------------------
1675 -- FUNCTION pmnt_frequency
1676 ---------------------------------------------
1677 FUNCTION pmnt_frequency(x_resultout OUT NOCOPY VARCHAR2,
1678 x_errormsg OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
1679
1680 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
1681 l_asset_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_value_id);
1682
1683 l_pmnt_frequency VARCHAR2(30);
1684
1685 cursor cPmntFreq is
1686 select FND.MEANING frequency
1687 from OKL_CASH_FLOW_OBJECTS CFO, OKL_CASH_FLOWS CFL,
1688 OKL_CASH_FLOW_LEVELS LVL, FND_LOOKUPS FND
1689 where CFO.OTY_CODE = 'QUOTED_ASSET'
1690 and CFO.ID = CFL.CFO_ID
1691 and CFL.ID = LVL.CAF_ID
1692 and CFO.SOURCE_ID = l_asset_id
1693 and CFO.SOURCE_TABLE = 'OKL_ASSETS_B'
1694 AND LVL.FQY_CODE = FND.LOOKUP_CODE
1695 AND FND.LOOKUP_TYPE = 'OKL_FREQUENCY';
1696
1697 /*UNION
1698 select OKL_CASH_FLOW_LEVELS.FQY_CODE FQY_CODE
1699 from okl_lease_applications_b LAP, OKL_FEES_B FEE, OKL_CASH_FLOW_OBJECTS,
1700 okl_cash_flows, OKL_CASH_FLOW_LEVELS, OKL_LEASE_QUOTES_B QUOTE
1701 where OKL_CASH_FLOW_objects.OTY_CODE = 'QUOTED_FEE'
1702 and OKL_CASH_FLOW_objects.id = okl_cash_flows.CFO_ID
1703 and okl_cash_flows.id = OKL_CASH_FLOW_LEVELS.CAF_ID
1704 and OKL_CASH_FLOW_OBJECTS.source_id = FEE.id
1705 and OKL_CASH_FLOW_OBJECTS.source_table = 'OKL_ASSETS_B'
1706 and FEE.parent_object_id = QUOTE.id
1707 and QUOTE.parent_object_id = LAP.id
1708 and FEE.parent_object_code = 'LEASEQUOTE'
1709 and QUOTE.parent_object_code = 'LEASEAPP'
1710 and QUOTE.primary_quote = 'Y'
1711 and LAP.id = l_lease_app_id
1712 UNION
1713 select OKL_CASH_FLOW_LEVELS.FQY_CODE FQY_CODE
1714 from okl_lease_applications_b LAP, OKL_SERVICES_B SRV, OKL_CASH_FLOW_OBJECTS,
1715 okl_cash_flows, OKL_CASH_FLOW_LEVELS, OKL_LEASE_QUOTES_B QUOTE
1716 where OKL_CASH_FLOW_objects.OTY_CODE = 'QUOTED_SERVICE'
1717 and OKL_CASH_FLOW_objects.id = okl_cash_flows.CFO_ID
1718 and okl_cash_flows.id = OKL_CASH_FLOW_LEVELS.CAF_ID
1719 and OKL_CASH_FLOW_OBJECTS.source_id = SRV.id
1720 and OKL_CASH_FLOW_OBJECTS.source_table = 'OKL_ASSETS_B'
1721 and SRV.parent_object_id = QUOTE.id
1722 and QUOTE.parent_object_id = LAP.id
1723 and SRV.parent_object_code = 'LEASEQUOTE'
1724 and QUOTE.parent_object_code = 'LEASEAPP'
1725 and QUOTE.primary_quote = 'Y'
1726 and LAP.id = l_lease_app_id
1727 UNION
1728 select OKL_CASH_FLOW_LEVELS.FQY_CODE FQY_CODE
1729 from okl_lease_applications_b LAP, OKL_INSURANCE_ESTIMATES_B INS, OKL_CASH_FLOW_OBJECTS,
1730 okl_cash_flows, OKL_CASH_FLOW_LEVELS, OKL_LEASE_QUOTES_B QUOTE
1731 where OKL_CASH_FLOW_objects.OTY_CODE = 'QUOTED_INSURANCE'
1732 and OKL_CASH_FLOW_objects.id = okl_cash_flows.CFO_ID
1733 and okl_cash_flows.id = OKL_CASH_FLOW_LEVELS.CAF_ID
1734 and OKL_CASH_FLOW_OBJECTS.source_id = INS.id
1735 and OKL_CASH_FLOW_OBJECTS.source_table = 'OKL_INSURANCE_ESTIMATES_B'
1736 and INS.LEASE_QUOTE_ID = QUOTE.id
1737 and QUOTE.parent_object_id = LAP.id
1738 and QUOTE.parent_object_code = 'LEASEAPP'
1739 and QUOTE.primary_quote = 'Y'
1740 and LAP.id = l_lease_app_id;*/
1741
1742
1743 ln_seq_number NUMBER :=1;
1744 BEGIN
1745 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
1746 x_resultout := FND_API.G_RET_STS_SUCCESS;
1747 BEGIN
1748 x_resultout := FND_API.G_RET_STS_SUCCESS;
1749
1750 FOR cPmntFreqRec IN cPmntFreq
1751 LOOP
1752 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_id :=
1753 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_id;
1754 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_parent_data_point_id := l_asset_id;
1755 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_sequence_number := ln_seq_number;
1756 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_value :=
1757 cPmntFreqRec.frequency;
1758
1759 ln_seq_number := ln_seq_number + 1;
1760
1761 END LOOP;
1762 RETURN NULL;
1763
1764 EXCEPTION
1765 WHEN NO_DATA_FOUND THEN
1766 l_pmnt_frequency := NULL;
1767 WHEN OTHERS THEN
1768 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
1769 x_errormsg := sqlerrm;
1770 END;
1771 END pmnt_frequency ;
1772
1773 ---------------------------------------------
1774 -- FUNCTION pmnt_arrears_yn
1775 ---------------------------------------------
1776 FUNCTION pmnt_arrears_yn(x_resultout OUT NOCOPY VARCHAR2,
1777 x_errormsg OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
1778
1779 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
1780 l_asset_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_value_id);
1781
1782 l_pmnt_arrears_yn VARCHAR2(3);
1783
1784 cursor cPmntArrYN is
1785 select FND.MEANING Arrears
1786 from OKL_CASH_FLOW_OBJECTS CFO, OKL_CASH_FLOWS CFL, FND_LOOKUPS FND
1787 where CFO.OTY_CODE = 'QUOTED_ASSET'
1788 and CFO.id = CFL.CFO_ID
1789 --Bug 7030452 :Hardcoded value '23' is removed
1790 -- and CFO.source_id = 23
1791 and CFO.source_id = l_asset_id
1792 --Bug 7030452 :End
1793 and CFO.source_table = 'OKL_ASSETS_B'
1794 AND CFL.due_arrears_yn = FND.LOOKUP_CODE
1795 AND FND.LOOKUP_TYPE = 'OKL_YES_NO';
1796
1797 /*union
1798 select OKL_CASH_FLOWS.due_arrears_yn due_arrears_yn
1799 from okl_lease_applications_b LAP, OKL_FEES_B FEE, OKL_CASH_FLOW_OBJECTS,
1800 okl_cash_flows, OKL_LEASE_QUOTES_B QUOTE
1801 where OKL_CASH_FLOW_objects.OTY_CODE = 'QUOTED_FEE'
1802 and OKL_CASH_FLOW_objects.id = okl_cash_flows.CFO_ID
1803 and OKL_CASH_FLOW_OBJECTS.source_id = FEE.id
1804 and OKL_CASH_FLOW_OBJECTS.source_table = 'OKL_FEES_B'
1805 and FEE.parent_object_id = QUOTE.id
1806 and QUOTE.parent_object_id = LAP.id
1807 and FEE.parent_object_code = 'LEASEQUOTE'
1808 and QUOTE.parent_object_code = 'LEASEAPP'
1809 and QUOTE.primary_quote = 'Y'
1810 and LAP.id = l_lease_app_id
1811 union
1812 select OKL_CASH_FLOWS.due_arrears_yn due_arrears_yn
1813 from okl_lease_applications_b LAP, OKL_SERVICES_B SRV, OKL_CASH_FLOW_OBJECTS,
1814 okl_cash_flows, OKL_LEASE_QUOTES_B QUOTE
1815 where OKL_CASH_FLOW_objects.OTY_CODE = 'QUOTED_SERVICE'
1816 and OKL_CASH_FLOW_objects.id = okl_cash_flows.CFO_ID
1817 and OKL_CASH_FLOW_OBJECTS.source_id = SRV.id
1818 and OKL_CASH_FLOW_OBJECTS.source_table = 'OKL_SERVICES_B'
1819 and SRV.parent_object_id = QUOTE.id
1820 and QUOTE.parent_object_id = LAP.id
1821 and SRV.parent_object_code = 'LEASEQUOTE'
1822 and QUOTE.parent_object_code = 'LEASEAPP'
1823 and QUOTE.primary_quote = 'Y'
1824 and LAP.id = l_lease_app_id
1825 union
1826 select OKL_CASH_FLOWS.due_arrears_yn due_arrears_yn
1827 from okl_lease_applications_b LAP, OKL_INSURANCE_ESTIMATES_B INS, OKL_CASH_FLOW_OBJECTS,
1828 okl_cash_flows, OKL_LEASE_QUOTES_B QUOTE
1829 where OKL_CASH_FLOW_objects.OTY_CODE = 'QUOTED_INSURANCE'
1830 and OKL_CASH_FLOW_objects.id = okl_cash_flows.CFO_ID
1831 and OKL_CASH_FLOW_OBJECTS.source_id = INS.id
1832 and OKL_CASH_FLOW_OBJECTS.source_table = 'OKL_INSURANCE_ESTIMATES_B'
1833 and INS.LEASE_QUOTE_ID = QUOTE.id
1834 and QUOTE.parent_object_id = LAP.id
1835 and QUOTE.parent_object_code = 'LEASEAPP'
1836 and QUOTE.primary_quote = 'Y'
1837 and LAP.id = l_lease_app_id;*/
1838
1839 ln_seq_number NUMBER :=1;
1840 BEGIN
1841 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
1842 x_resultout := FND_API.G_RET_STS_SUCCESS;
1843 BEGIN
1844
1845 x_resultout := FND_API.G_RET_STS_SUCCESS;
1846
1847 FOR cPmntArrYNRec IN cPmntArrYN
1848 LOOP
1849 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_id :=
1850 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_id;
1851 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_parent_data_point_id := l_asset_id;
1852 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_sequence_number := ln_seq_number;
1853 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_value :=
1854 cPmntArrYNRec.Arrears;
1855
1856 ln_seq_number := ln_seq_number + 1;
1857
1858 END LOOP;
1859 RETURN NULL;
1860
1861 EXCEPTION
1862 WHEN NO_DATA_FOUND THEN
1863 l_pmnt_arrears_yn := NULL;
1864 WHEN OTHERS THEN
1865 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
1866 x_errormsg := sqlerrm;
1867 END;
1868 END pmnt_arrears_yn ;
1869
1870
1871 ---------------------------------------------
1872 -- FUNCTION pmnt_periods
1873 ---------------------------------------------
1874 FUNCTION pmnt_periods(x_resultout OUT NOCOPY VARCHAR2,
1875 x_errormsg OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
1876
1877 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
1878 l_asset_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_value_id);
1879
1880 l_pmnt_periods NUMBER;
1881
1882 cursor cPmntPeriods is
1883 select LVL.NUMBER_OF_PERIODS
1884 from OKL_CASH_FLOW_OBJECTS CFO, OKL_CASH_FLOWS CFL, OKL_CASH_FLOW_LEVELS LVL
1885 where CFO.OTY_CODE = 'QUOTED_ASSET'
1886 and CFO.ID = CFL.CFO_ID
1887 and CFL.ID = LVL.CAF_ID
1888 and CFO.SOURCE_ID = l_asset_id
1889 and CFO.SOURCE_TABLE = 'OKL_ASSETS_B';
1890
1891 /*union
1892 select OKL_CASH_FLOW_LEVELS.number_of_periods number_of_periods
1893 from okl_lease_applications_b LAP, OKL_FEES_B FEE, OKL_CASH_FLOW_OBJECTS,
1894 okl_cash_flows, OKL_CASH_FLOW_LEVELS, OKL_LEASE_QUOTES_B QUOTE
1895 where OKL_CASH_FLOW_objects.OTY_CODE = 'QUOTED_FEE'
1896 and OKL_CASH_FLOW_objects.id = okl_cash_flows.CFO_ID
1897 and okl_cash_flows.id = OKL_CASH_FLOW_LEVELS.CAF_ID
1898 and OKL_CASH_FLOW_OBJECTS.source_id = FEE.id
1899 and OKL_CASH_FLOW_OBJECTS.source_table = 'OKL_FEES_B'
1900 and FEE.parent_object_id = QUOTE.id
1901 and QUOTE.parent_object_id = LAP.id
1902 and FEE.parent_object_code = 'LEASEQUOTE'
1903 and QUOTE.parent_object_code = 'LEASEAPP'
1904 and QUOTE.primary_quote = 'Y'
1905 and LAP.id = l_lease_app_id
1906 union
1907 select OKL_CASH_FLOW_LEVELS.number_of_periods number_of_periods
1908 from okl_lease_applications_b LAP, OKL_SERVICES_B SRV, OKL_CASH_FLOW_OBJECTS,
1909 okl_cash_flows, OKL_CASH_FLOW_LEVELS, OKL_LEASE_QUOTES_B QUOTE
1910 where OKL_CASH_FLOW_objects.OTY_CODE = 'QUOTED_SERVICE'
1911 and OKL_CASH_FLOW_objects.id = okl_cash_flows.CFO_ID
1912 and okl_cash_flows.id = OKL_CASH_FLOW_LEVELS.CAF_ID
1913 and OKL_CASH_FLOW_OBJECTS.source_id = SRV.id
1914 and OKL_CASH_FLOW_OBJECTS.source_table = 'OKL_SERVICES_B'
1915 and SRV.parent_object_id = QUOTE.id
1916 and QUOTE.parent_object_id = LAP.id
1917 and SRV.parent_object_code = 'LEASEQUOTE'
1918 and QUOTE.parent_object_code = 'LEASEAPP'
1919 and QUOTE.primary_quote = 'Y'
1920 and LAP.id = l_lease_app_id
1921 union
1922 select OKL_CASH_FLOW_LEVELS.number_of_periods number_of_periods
1923 from okl_lease_applications_b LAP, OKL_INSURANCE_ESTIMATES_B INS, OKL_CASH_FLOW_OBJECTS,
1924 okl_cash_flows, OKL_CASH_FLOW_LEVELS, OKL_LEASE_QUOTES_B QUOTE
1925 where OKL_CASH_FLOW_objects.OTY_CODE = 'QUOTED_INSURANCE'
1926 and OKL_CASH_FLOW_objects.id = okl_cash_flows.CFO_ID
1927 and okl_cash_flows.id = OKL_CASH_FLOW_LEVELS.CAF_ID
1928 and OKL_CASH_FLOW_OBJECTS.source_id = INS.id
1929 and OKL_CASH_FLOW_OBJECTS.source_table = 'OKL_INSURANCE_ESTIMATES_B'
1930 and INS.LEASE_QUOTE_ID = QUOTE.id
1931 and QUOTE.parent_object_id = LAP.id
1932 and QUOTE.parent_object_code = 'LEASEAPP'
1933 and QUOTE.primary_quote = 'Y'
1934 and LAP.id = l_lease_app_id;*/
1935
1936 ln_seq_number NUMBER :=1;
1937 BEGIN
1938 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
1939 x_resultout := FND_API.G_RET_STS_SUCCESS;
1940 BEGIN
1941 x_resultout := FND_API.G_RET_STS_SUCCESS;
1942
1943 FOR cPmntPeriodsRec IN cPmntPeriods
1944 LOOP
1945 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_id :=
1946 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_id;
1947 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_parent_data_point_id := l_asset_id;
1948 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_sequence_number := ln_seq_number;
1949 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_value :=
1950 cPmntPeriodsRec.number_of_periods;
1951
1952 ln_seq_number := ln_seq_number + 1;
1953
1954 END LOOP;
1955 RETURN NULL;
1956
1957 EXCEPTION
1958 WHEN NO_DATA_FOUND THEN
1959 l_pmnt_periods := NULL;
1960 WHEN OTHERS THEN
1961 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
1962 x_errormsg := sqlerrm;
1963 END;
1964 END pmnt_periods ;
1965
1966
1967 ---------------------------------------------
1968 -- FUNCTION pmnt_amounts
1969 ---------------------------------------------
1970 FUNCTION pmnt_amounts(x_resultout OUT NOCOPY VARCHAR2,
1971 x_errormsg OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
1972
1973 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
1974 l_asset_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_value_id);
1975
1976 l_pmnt_amounts NUMBER;
1977
1978 cursor cPmntAmount is
1979 select LVL.AMOUNT
1980 from OKL_CASH_FLOW_OBJECTS CFO, OKL_CASH_FLOWS CFL, OKL_CASH_FLOW_LEVELS LVL
1981 where CFO.OTY_CODE = 'QUOTED_ASSET'
1982 and CFO.ID = CFL.CFO_ID
1983 and CFL.ID = LVL.CAF_ID
1984 and CFO.SOURCE_ID = l_asset_id
1985 and CFO.SOURCE_TABLE = 'OKL_ASSETS_B';
1986
1987 /*union
1988 select OKL_CASH_FLOW_LEVELS.amount amount
1989 from okl_lease_applications_b LAP, OKL_FEES_B FEE, OKL_CASH_FLOW_OBJECTS,
1990 okl_cash_flows, OKL_CASH_FLOW_LEVELS, OKL_LEASE_QUOTES_B QUOTE
1991 where OKL_CASH_FLOW_objects.OTY_CODE = 'QUOTED_FEE'
1992 and OKL_CASH_FLOW_objects.id = okl_cash_flows.CFO_ID
1993 and okl_cash_flows.id = OKL_CASH_FLOW_LEVELS.CAF_ID
1994 and OKL_CASH_FLOW_OBJECTS.source_id = FEE.id
1995 and OKL_CASH_FLOW_OBJECTS.source_table = 'OKL_FEES_B'
1996 and FEE.parent_object_id = QUOTE.id
1997 and QUOTE.parent_object_id = LAP.id
1998 and FEE.parent_object_code = 'LEASEQUOTE'
1999 and QUOTE.parent_object_code = 'LEASEAPP'
2000 and QUOTE.primary_quote = 'Y'
2001 and LAP.id = l_lease_app_id
2002 union
2003 select OKL_CASH_FLOW_LEVELS.amount amount
2004 from okl_lease_applications_b LAP, OKL_SERVICES_B SRV, OKL_CASH_FLOW_OBJECTS,
2005 okl_cash_flows, OKL_CASH_FLOW_LEVELS, OKL_LEASE_QUOTES_B QUOTE
2006 where OKL_CASH_FLOW_objects.OTY_CODE = 'QUOTED_SERVICE'
2007 and OKL_CASH_FLOW_objects.id = okl_cash_flows.CFO_ID
2008 and okl_cash_flows.id = OKL_CASH_FLOW_LEVELS.CAF_ID
2009 and OKL_CASH_FLOW_OBJECTS.source_id = SRV.id
2010 and OKL_CASH_FLOW_OBJECTS.source_table = 'OKL_SERVICES_B'
2011 and SRV.parent_object_id = QUOTE.id
2012 and QUOTE.parent_object_id = LAP.id
2013 and SRV.parent_object_code = 'LEASEQUOTE'
2014 and QUOTE.parent_object_code = 'LEASEAPP'
2015 and QUOTE.primary_quote = 'Y'
2016 and LAP.id = l_lease_app_id
2017 union
2018 select OKL_CASH_FLOW_LEVELS.amount amount
2019 from okl_lease_applications_b LAP, OKL_INSURANCE_ESTIMATES_B INS, OKL_CASH_FLOW_OBJECTS,
2020 okl_cash_flows, OKL_CASH_FLOW_LEVELS, OKL_LEASE_QUOTES_B QUOTE
2021 where OKL_CASH_FLOW_objects.OTY_CODE = 'QUOTED_INSURANCE'
2022 and OKL_CASH_FLOW_objects.id = okl_cash_flows.CFO_ID
2023 and okl_cash_flows.id = OKL_CASH_FLOW_LEVELS.CAF_ID
2024 and OKL_CASH_FLOW_OBJECTS.source_id = INS.id
2025 and OKL_CASH_FLOW_OBJECTS.source_table = 'OKL_INSURANCE_ESTIMATES_B'
2026 and INS.LEASE_QUOTE_ID = QUOTE.id
2027 and QUOTE.parent_object_id = LAP.id
2028 and QUOTE.parent_object_code = 'LEASEAPP'
2029 and QUOTE.primary_quote = 'Y'
2030 and LAP.id = l_lease_app_id; */
2031
2032 ln_seq_number NUMBER :=1;
2033 BEGIN
2034 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
2035 x_resultout := FND_API.G_RET_STS_SUCCESS;
2036 BEGIN
2037 x_resultout := FND_API.G_RET_STS_SUCCESS;
2038
2039 FOR cPmntAmountRec IN cPmntAmount
2040 LOOP
2041 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_id :=
2042 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_id;
2043 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_parent_data_point_id := l_asset_id;
2044 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_sequence_number := ln_seq_number;
2045 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_value :=
2046 cPmntAmountRec.amount;
2047
2048 ln_seq_number := ln_seq_number + 1;
2049
2050 END LOOP;
2051 RETURN NULL;
2052
2053
2054 EXCEPTION
2055 WHEN NO_DATA_FOUND THEN
2056 l_pmnt_amounts := NULL;
2057 WHEN OTHERS THEN
2058 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
2059 x_errormsg := sqlerrm;
2060 END;
2061 END pmnt_amounts ;
2062
2063 ---------------------------------------------
2064 -- FUNCTION pmnt_start_date
2065 ---------------------------------------------
2066 FUNCTION pmnt_start_date(x_resultout OUT NOCOPY VARCHAR2,
2067 x_errormsg OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
2068
2069 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
2070 l_asset_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_value_id);
2071
2072 l_pmnt_start_date DATE;
2073
2074 cursor cPmntStartDate is
2075 select LVL.start_date
2076 from OKL_CASH_FLOW_OBJECTS CFO, OKL_CASH_FLOWS CFL, OKL_CASH_FLOW_LEVELS LVL
2077 where CFO.OTY_CODE = 'QUOTED_ASSET'
2078 and CFO.ID = CFL.CFO_ID
2079 and CFL.ID = LVL.CAF_ID
2080 and CFO.SOURCE_ID = l_asset_id
2081 and CFO.SOURCE_TABLE = 'OKL_ASSETS_B';
2082
2083 /*union
2084 select OKL_CASH_FLOW_LEVELS.start_date start_date
2085 from okl_lease_applications_b LAP, OKL_FEES_B FEE, OKL_CASH_FLOW_OBJECTS,
2086 okl_cash_flows, OKL_CASH_FLOW_LEVELS, OKL_LEASE_QUOTES_B QUOTE
2087 where OKL_CASH_FLOW_objects.OTY_CODE = 'QUOTED_FEE'
2088 and OKL_CASH_FLOW_objects.id = okl_cash_flows.CFO_ID
2089 and okl_cash_flows.id = OKL_CASH_FLOW_LEVELS.CAF_ID
2090 and OKL_CASH_FLOW_OBJECTS.source_id = FEE.id
2091 and OKL_CASH_FLOW_OBJECTS.source_table = 'OKL_FEES_B'
2092 and FEE.parent_object_id = QUOTE.id
2093 and QUOTE.parent_object_id = LAP.id
2094 and FEE.parent_object_code = 'LEASEQUOTE'
2095 and QUOTE.parent_object_code = 'LEASEAPP'
2096 and QUOTE.primary_quote = 'Y'
2097 and LAP.id = l_lease_app_id
2098 union
2099 select OKL_CASH_FLOW_LEVELS.start_date start_date
2100 from okl_lease_applications_b LAP, OKL_SERVICES_B SRV, OKL_CASH_FLOW_OBJECTS,
2101 okl_cash_flows, OKL_CASH_FLOW_LEVELS, OKL_LEASE_QUOTES_B QUOTE
2102 where OKL_CASH_FLOW_objects.OTY_CODE = 'QUOTED_SERVICE'
2103 and OKL_CASH_FLOW_objects.id = okl_cash_flows.CFO_ID
2104 and okl_cash_flows.id = OKL_CASH_FLOW_LEVELS.CAF_ID
2105 and OKL_CASH_FLOW_OBJECTS.source_id = SRV.id
2106 and OKL_CASH_FLOW_OBJECTS.source_table = 'OKL_SERVICES_B'
2107 and SRV.parent_object_id = QUOTE.id
2108 and QUOTE.parent_object_id = LAP.id
2109 and SRV.parent_object_code = 'LEASEQUOTE'
2110 and QUOTE.parent_object_code = 'LEASEAPP'
2111 and QUOTE.primary_quote = 'Y'
2112 and LAP.id = l_lease_app_id
2113 union
2114 select OKL_CASH_FLOW_LEVELS.start_date start_date
2115 from okl_lease_applications_b LAP, OKL_INSURANCE_ESTIMATES_B INS, OKL_CASH_FLOW_OBJECTS,
2116 okl_cash_flows, OKL_CASH_FLOW_LEVELS, OKL_LEASE_QUOTES_B QUOTE
2117 where OKL_CASH_FLOW_objects.OTY_CODE = 'QUOTED_INSURANCE'
2118 and OKL_CASH_FLOW_objects.id = okl_cash_flows.CFO_ID
2119 and okl_cash_flows.id = OKL_CASH_FLOW_LEVELS.CAF_ID
2120 and OKL_CASH_FLOW_OBJECTS.source_id = INS.id
2121 and OKL_CASH_FLOW_OBJECTS.source_table = 'OKL_INSURANCE_ESTIMATES_B'
2122 and INS.LEASE_QUOTE_ID = QUOTE.id
2123 and QUOTE.parent_object_id = LAP.id
2124 and QUOTE.parent_object_code = 'LEASEAPP'
2125 and QUOTE.primary_quote = 'Y'
2126 and LAP.id = l_lease_app_id;*/
2127
2128 ln_seq_number NUMBER :=1;
2129 BEGIN
2130 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
2131 x_resultout := FND_API.G_RET_STS_SUCCESS;
2132 BEGIN
2133
2134 x_resultout := FND_API.G_RET_STS_SUCCESS;
2135
2136 FOR cPmntStartDateRec IN cPmntStartDate
2137 LOOP
2138 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_id :=
2139 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_id;
2140 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_parent_data_point_id := l_asset_id;
2141 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_sequence_number := ln_seq_number;
2142 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_value :=
2143 cPmntStartDateRec.start_date;
2144
2145 ln_seq_number := ln_seq_number + 1;
2146
2147 END LOOP;
2148 RETURN NULL;
2149
2150 EXCEPTION
2151 WHEN NO_DATA_FOUND THEN
2152 l_pmnt_start_date := NULL;
2153 WHEN OTHERS THEN
2154 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
2155 x_errormsg := sqlerrm;
2156 END;
2157 END pmnt_start_date ;
2158
2159 ---------------------------------------------
2160 -- FUNCTION payment_structure
2161 ---------------------------------------------
2162 FUNCTION payment_structure(x_resultout OUT NOCOPY VARCHAR2,
2163 x_errormsg OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
2164
2165 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
2166 l_asset_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_value_id);
2167
2168 l_payment_structure VARCHAR2(5) := 'LEVEL';
2169
2170 ln_seq_number NUMBER :=1;
2171 BEGIN
2172 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
2173 x_resultout := FND_API.G_RET_STS_SUCCESS;
2174 BEGIN
2175
2176 x_resultout := FND_API.G_RET_STS_SUCCESS;
2177
2178 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_id :=
2179 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_id;
2180 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_parent_data_point_id := l_asset_id;
2181 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_sequence_number := ln_seq_number;
2182 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_value :=
2183 l_payment_structure;
2184
2185
2186 RETURN NULL;
2187
2188 EXCEPTION
2189 WHEN NO_DATA_FOUND THEN
2190 l_payment_structure := NULL;
2191 WHEN OTHERS THEN
2192 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
2193 x_errormsg := sqlerrm;
2194 END;
2195 END payment_structure ;
2196
2197 -------------------------------------
2198 -- PROCEDURE calculate_level_end_date
2199 -------------------------------------
2200 PROCEDURE calculate_level_end_date (
2201 p_level_start_date IN DATE
2202 ,p_contract_term IN NUMBER
2203 ,p_frequency_code IN VARCHAR2
2204 ,p_cashflow_level_tbl IN OUT NOCOPY OKL_LEASE_QUOTE_CASHFLOW_PVT.cashflow_level_tbl_type
2205 ,x_return_status OUT NOCOPY VARCHAR2) IS
2206
2207 l_program_name CONSTANT VARCHAR2(30) := 'calculate_level_end_date';
2208 l_api_name CONSTANT VARCHAR2(61) := G_APP_NAME||'.'||l_program_name;
2209
2210 l_mpp PLS_INTEGER;
2211
2212 l_contract_end_date DATE;
2213 l_next_start_date DATE;
2214 l_end_date DATE;
2215
2216 BEGIN
2217 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
2218 IF p_frequency_code = 'A' THEN
2219 l_mpp := 12;
2220 ELSIF p_frequency_code = 'S' THEN
2221 l_mpp := 6;
2222 ELSIF p_frequency_code = 'Q' THEN
2223 l_mpp := 3;
2224 ELSIF p_frequency_code = 'M' THEN
2225 l_mpp := 1;
2226 END IF;
2227
2228 l_next_start_date := p_level_start_date;
2229
2230 FOR i IN p_cashflow_level_tbl.FIRST .. p_cashflow_level_tbl.LAST LOOP
2231 IF p_cashflow_level_tbl.EXISTS(i) THEN
2232
2233 IF p_cashflow_level_tbl(i).stub_days IS NOT NULL THEN
2234 l_end_date := l_next_start_date + p_cashflow_level_tbl(i).stub_days - 1;
2235 ELSE
2236 l_end_date := ADD_MONTHS(l_next_start_date, l_mpp*p_cashflow_level_tbl(i).periods) - 1;
2237 END IF;
2238
2239 p_cashflow_level_tbl(i).start_date := l_end_date;
2240 l_next_start_date := l_end_date + 1;
2241
2242 END IF;
2243 END LOOP;
2244
2245 l_contract_end_date := ADD_MONTHS(p_level_start_date, p_contract_term) - 1;
2246
2247 IF l_end_date > l_contract_end_date THEN
2248 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME, p_msg_name => 'OKL_LEVEL_EXTENDS_K_END');
2249 RAISE OKL_API.G_EXCEPTION_ERROR;
2250 END IF;
2251
2252 x_return_status := G_RET_STS_SUCCESS;
2253
2254 EXCEPTION
2255
2256 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2257 x_return_status := G_RET_STS_ERROR;
2258
2259 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2260 x_return_status := G_RET_STS_UNEXP_ERROR;
2261
2262 WHEN OTHERS THEN
2263 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
2264 p_msg_name => G_DB_ERROR,
2265 p_token1 => G_PROG_NAME_TOKEN,
2266 p_token1_value => l_api_name,
2267 p_token2 => G_SQLCODE_TOKEN,
2268 p_token2_value => sqlcode,
2269 p_token3 => G_SQLERRM_TOKEN,
2270 p_token3_value => sqlerrm);
2271
2272 x_return_status := G_RET_STS_UNEXP_ERROR;
2273
2274 END calculate_level_end_date;
2275
2276 ---------------------------------------------
2277 -- FUNCTION pmnt_end_date
2278 ---------------------------------------------
2279 FUNCTION pmnt_end_date(x_resultout OUT NOCOPY VARCHAR2,
2280 x_errormsg OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
2281
2282 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
2283 l_asset_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_value_id);
2284
2285 l_pmnt_end_date DATE;
2286
2287 cursor c_level_data is
2288 select LVL.stub_days, LVL.number_of_periods, LVL.start_date
2289 from OKL_CASH_FLOW_OBJECTS CFO, OKL_CASH_FLOWS CFL, OKL_CASH_FLOW_LEVELS LVL
2290 where CFO.OTY_CODE = 'QUOTED_ASSET'
2291 and CFO.ID = CFL.CFO_ID
2292 and CFL.ID = LVL.CAF_ID
2293 and CFO.SOURCE_ID = l_asset_id
2294 and CFO.SOURCE_TABLE = 'OKL_ASSETS_B'
2295 order by LVL.start_date; --Bug # 9116306
2296
2297 /*union
2298 select OKL_CASH_FLOW_LEVELS.start_date end_date
2299 from okl_lease_applications_b LAP, OKL_FEES_B FEE, OKL_CASH_FLOW_OBJECTS,
2300 okl_cash_flows, OKL_CASH_FLOW_LEVELS, OKL_LEASE_QUOTES_B QUOTE
2301 where OKL_CASH_FLOW_objects.OTY_CODE = 'QUOTED_FEE'
2302 and OKL_CASH_FLOW_objects.id = okl_cash_flows.CFO_ID
2303 and okl_cash_flows.id = OKL_CASH_FLOW_LEVELS.CAF_ID
2304 and OKL_CASH_FLOW_OBJECTS.source_id = FEE.id
2305 and OKL_CASH_FLOW_OBJECTS.source_table = 'OKL_FEES_B'
2306 and FEE.parent_object_id = QUOTE.id
2307 and QUOTE.parent_object_id = LAP.id
2308 and FEE.parent_object_code = 'LEASEQUOTE'
2309 and QUOTE.parent_object_code = 'LEASEAPP'
2310 and QUOTE.primary_quote = 'Y'
2311 and LAP.id = l_lease_app_id
2312 union
2313 select OKL_CASH_FLOW_LEVELS.start_date end_date
2314 from okl_lease_applications_b LAP, OKL_SERVICES_B SRV, OKL_CASH_FLOW_OBJECTS,
2315 okl_cash_flows, OKL_CASH_FLOW_LEVELS, OKL_LEASE_QUOTES_B QUOTE
2316 where OKL_CASH_FLOW_objects.OTY_CODE = 'QUOTED_SERVICE'
2317 and OKL_CASH_FLOW_objects.id = okl_cash_flows.CFO_ID
2318 and okl_cash_flows.id = OKL_CASH_FLOW_LEVELS.CAF_ID
2319 and OKL_CASH_FLOW_OBJECTS.source_id = SRV.id
2320 and OKL_CASH_FLOW_OBJECTS.source_table = 'OKL_SERVICES_B'
2321 and SRV.parent_object_id = QUOTE.id
2322 and QUOTE.parent_object_id = LAP.id
2323 and SRV.parent_object_code = 'LEASEQUOTE'
2324 and QUOTE.parent_object_code = 'LEASEAPP'
2325 and QUOTE.primary_quote = 'Y'
2326 and LAP.id = l_lease_app_id
2327 union
2328 select OKL_CASH_FLOW_LEVELS.start_date end_date
2329 from okl_lease_applications_b LAP, OKL_INSURANCE_ESTIMATES_B INS, OKL_CASH_FLOW_OBJECTS,
2330 okl_cash_flows, OKL_CASH_FLOW_LEVELS, OKL_LEASE_QUOTES_B QUOTE
2331 where OKL_CASH_FLOW_objects.OTY_CODE = 'QUOTED_INSURANCE'
2332 and OKL_CASH_FLOW_objects.id = okl_cash_flows.CFO_ID
2333 and okl_cash_flows.id = OKL_CASH_FLOW_LEVELS.CAF_ID
2334 and OKL_CASH_FLOW_OBJECTS.source_id = INS.id
2335 and OKL_CASH_FLOW_OBJECTS.source_table = 'OKL_INSURANCE_ESTIMATES_B'
2336 and INS.LEASE_QUOTE_ID = QUOTE.id
2337 and QUOTE.parent_object_id = LAP.id
2338 and QUOTE.parent_object_code = 'LEASEAPP'
2339 and QUOTE.primary_quote = 'Y'
2340 and LAP.id = l_lease_app_id;*/
2341
2342 CURSOR c_get_info IS
2343 SELECT OKL_CASH_FLOW_LEVELS.FQY_CODE FQY_CODE, QUOTE.TERM
2344 FROM OKL_LEASE_APPLICATIONS_B LAP, OKL_ASSETS_B ASSET, OKL_CASH_FLOW_OBJECTS,
2345 OKL_CASH_FLOWS, OKL_CASH_FLOW_LEVELS, OKL_LEASE_QUOTES_B QUOTE
2346 WHERE OKL_CASH_FLOW_OBJECTS.OTY_CODE = 'QUOTED_ASSET'
2347 AND OKL_CASH_FLOW_OBJECTS.ID = OKL_CASH_FLOWS.CFO_ID
2348 AND OKL_CASH_FLOWS.ID = OKL_CASH_FLOW_LEVELS.CAF_ID
2349 AND OKL_CASH_FLOW_OBJECTS.SOURCE_ID = ASSET.ID
2350 AND OKL_CASH_FLOW_OBJECTS.SOURCE_TABLE = 'OKL_ASSETS_B'
2351 AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
2352 AND QUOTE.PARENT_OBJECT_ID = LAP.ID
2353 AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
2354 AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
2355 AND QUOTE.PRIMARY_QUOTE = 'Y'
2356 AND LAP.ID = l_lease_app_id
2357 AND ASSET.ID = l_asset_id
2358 AND ROWNUM = 1;
2359
2360 ln_seq_number NUMBER :=1;
2361 i BINARY_INTEGER := 0;
2362
2363 cf_level_tbl OKL_LEASE_QUOTE_CASHFLOW_PVT.cashflow_level_tbl_type;
2364
2365 l_freq_code OKL_CASH_FLOW_LEVELS.FQY_CODE%TYPE;
2366 l_term NUMBER;
2367 l_return_status VARCHAR2(1);
2368 BEGIN
2369 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
2370 x_resultout := FND_API.G_RET_STS_SUCCESS;
2371 BEGIN
2372 x_resultout := FND_API.G_RET_STS_SUCCESS;
2373
2374 -- Fetch the frequency code, term
2375 OPEN c_get_info;
2376 FETCH c_get_info INTO l_freq_code, l_term;
2377 CLOSE c_get_info;
2378
2379 -- Populate the level table
2380 FOR l_level_data IN c_level_data LOOP
2381 cf_level_tbl(i).stub_days := l_level_data.stub_days;
2382 cf_level_tbl(i).periods := l_level_data.number_of_periods;
2383 cf_level_tbl(i).start_date := l_level_data.start_date;
2384
2385 i := i + 1;
2386 END LOOP;
2387
2388 IF (l_freq_code IS NOT NULL AND l_term IS NOT NULL AND cf_level_tbl.COUNT > 0) THEN
2389
2390 calculate_level_end_date ( p_level_start_date => cf_level_tbl(0).start_date
2391 ,p_contract_term => l_term
2392 ,p_frequency_code => l_freq_code
2393 ,p_cashflow_level_tbl => cf_level_tbl
2394 ,x_return_status => l_return_status);
2395 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
2396 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2397 ELSIF l_return_status = G_RET_STS_ERROR THEN
2398 RAISE OKL_API.G_EXCEPTION_ERROR;
2399 END IF;
2400
2401 FOR j IN cf_level_tbl.FIRST .. cf_level_tbl.LAST LOOP
2402 IF cf_level_tbl.EXISTS(j) THEN
2403 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_id :=
2404 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_id;
2405 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_parent_data_point_id := l_asset_id;
2406 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_sequence_number := ln_seq_number;
2407 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_value := cf_level_tbl(j).start_date;
2408
2409 ln_seq_number := ln_seq_number + 1;
2410 END IF;
2411 END LOOP;
2412
2413 END IF;
2414
2415 RETURN NULL;
2416
2417 EXCEPTION
2418 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2419 l_pmnt_end_date := NULL;
2420
2421 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2422 l_pmnt_end_date := NULL;
2423
2424 WHEN NO_DATA_FOUND THEN
2425 l_pmnt_end_date := NULL;
2426 WHEN OTHERS THEN
2427 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
2428 x_errormsg := sqlerrm;
2429 END;
2430 END pmnt_end_date ;
2431
2432 ---------------------------------------------
2433 -- FUNCTION fee_name
2434 ---------------------------------------------
2435 FUNCTION fee_name (x_resultout OUT NOCOPY VARCHAR2,
2436 x_errormsg OUT NOCOPY VARCHAR2 ) RETURN VARCHAR2 IS
2437
2438 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
2439 l_fee_name VARCHAR2(30);
2440 -- gboomina:Bug 7110500 :Modified cursor to use correct view
2441 CURSOR cFeeName IS
2442 /*
2443 SELECT STRM.STY_NAME FEE_NAME
2444 FROM OKL_LEASE_APPLICATIONS_B LAP, OKL_LEASE_QUOTES_B QUOTE,
2445 OKL_FEES_B FEES, OKL_STRM_TMPT_PRIMARY_UV STRM
2446 WHERE STRM.STY_ID = FEES.STREAM_TYPE_ID
2447 AND FEES.PARENT_OBJECT_CODE = 'LEASEQUOTE'
2448 AND FEES.PARENT_OBJECT_ID = QUOTE.ID
2449 AND QUOTE.PARENT_OBJECT_ID = LAP.ID
2450 AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
2451 AND QUOTE.PRIMARY_QUOTE = 'Y'
2452 AND LAP.ID = l_lease_app_id;
2453 */
2454 SELECT STRM.NAME FEE_NAME
2455 FROM OKL_LEASE_APPLICATIONS_B LAP, OKL_LEASE_QUOTES_B QUOTE,
2456 OKL_FEES_B FEES, OKL_STRM_TYPE_V STRM
2457 WHERE STRM.ID = FEES.STREAM_TYPE_ID
2458 AND FEES.PARENT_OBJECT_CODE = 'LEASEQUOTE'
2459 AND FEES.PARENT_OBJECT_ID = QUOTE.ID
2460 AND QUOTE.PARENT_OBJECT_ID = LAP.ID
2461 AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
2462 AND QUOTE.PRIMARY_QUOTE = 'Y'
2463 AND LAP.ID = l_lease_app_id;
2464
2465 -- gboomina:Bug 7110500 - End
2466
2467 ln_seq_number NUMBER :=1;
2468
2469 BEGIN
2470 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
2471 x_resultout := FND_API.G_RET_STS_SUCCESS;
2472 BEGIN
2473 x_resultout := FND_API.G_RET_STS_SUCCESS;
2474
2475 FOR cFeeNameRec IN cFeeName LOOP
2476 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_id :=
2477 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_id;
2478 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_parent_data_point_id :=
2479 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_parent_data_point_id;
2480 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_sequence_number := ln_seq_number;
2481 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_value :=
2482 cFeeNameRec.fee_name;
2483
2484 ln_seq_number := ln_seq_number + 1;
2485
2486 END LOOP;
2487 RETURN NULL;
2488
2489 EXCEPTION
2490 WHEN NO_DATA_FOUND THEN
2491 l_fee_name := NULL;
2492 WHEN OTHERS THEN
2493 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
2494 x_errormsg := sqlerrm;
2495 END;
2496 END fee_name ;
2497
2498
2499 ---------------------------------------------
2500 -- FUNCTION fee_type
2501 ---------------------------------------------
2502 FUNCTION fee_type(x_resultout OUT NOCOPY VARCHAR2,
2503 x_errormsg OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
2504
2505 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
2506 l_fee_type VARCHAR2(30);
2507
2508 CURSOR cFeeType IS
2509 SELECT FEES.FEE_TYPE FEE_TYPE
2510 FROM OKL_LEASE_APPLICATIONS_B LAP, OKL_LEASE_QUOTES_B QUOTE, OKL_FEES_B FEES
2511 WHERE FEES.PARENT_OBJECT_CODE = 'LEASEQUOTE'
2512 AND FEES.PARENT_OBJECT_ID = QUOTE.ID
2513 AND QUOTE.PARENT_OBJECT_ID = LAP.ID
2514 AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
2515 AND QUOTE.PRIMARY_QUOTE = 'Y'
2516 AND LAP.ID = l_lease_app_id;
2517
2518
2519 ln_seq_number NUMBER :=1;
2520
2521 BEGIN
2522 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
2523 x_resultout := FND_API.G_RET_STS_SUCCESS;
2524 BEGIN
2525 x_resultout := FND_API.G_RET_STS_SUCCESS;
2526
2527 FOR cFeeTypeRec IN cFeeType LOOP
2528 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_id :=
2529 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_id;
2530 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_parent_data_point_id :=
2531 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_parent_data_point_id;
2532 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_sequence_number := ln_seq_number;
2533 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_value :=
2534 cFeeTypeRec.fee_type;
2535
2536 ln_seq_number := ln_seq_number + 1;
2537
2538 END LOOP;
2539 RETURN NULL;
2540
2541 EXCEPTION
2542 WHEN NO_DATA_FOUND THEN
2543 l_fee_type := NULL;
2544 WHEN OTHERS THEN
2545 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
2546 x_errormsg := sqlerrm;
2547 END;
2548 END fee_type ;
2549
2550 ---------------------------------------------
2551 -- FUNCTION fee_amount
2552 ---------------------------------------------
2553 FUNCTION fee_amount(x_resultout OUT NOCOPY VARCHAR2,
2554 x_errormsg OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
2555
2556 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
2557 l_fee_amount NUMBER;
2558
2559 CURSOR cFeeAmount IS
2560 SELECT FEES.FEE_AMOUNT FEE_AMOUNT
2561 FROM OKL_LEASE_APPLICATIONS_B LAP, OKL_LEASE_QUOTES_B QUOTE, OKL_FEES_B FEES
2562 WHERE FEES.PARENT_OBJECT_CODE = 'LEASEQUOTE'
2563 AND FEES.PARENT_OBJECT_ID = QUOTE.ID
2564 AND QUOTE.PARENT_OBJECT_ID = LAP.ID
2565 AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
2566 AND QUOTE.PRIMARY_QUOTE = 'Y'
2567 AND LAP.ID = l_lease_app_id;
2568
2569
2570 ln_seq_number NUMBER :=1;
2571 BEGIN
2572 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
2573 x_resultout := FND_API.G_RET_STS_SUCCESS;
2574 BEGIN
2575 x_resultout := FND_API.G_RET_STS_SUCCESS;
2576
2577 FOR cFeeAmountRec IN cFeeAmount LOOP
2578 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_id :=
2579 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_id;
2580 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_parent_data_point_id :=
2581 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_parent_data_point_id;
2582 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_sequence_number := ln_seq_number;
2583 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_value :=
2584 cFeeAmountRec.fee_amount;
2585
2586 ln_seq_number := ln_seq_number + 1;
2587
2588 END LOOP;
2589 RETURN NULL;
2590
2591 EXCEPTION
2592 WHEN NO_DATA_FOUND THEN
2593 l_fee_amount := NULL;
2594 WHEN OTHERS THEN
2595 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
2596 x_errormsg := sqlerrm;
2597 END;
2598 END fee_amount ;
2599
2600 ---------------------------------------------
2601 -- FUNCTION fee_date
2602 ---------------------------------------------
2603 FUNCTION fee_date(x_resultout OUT NOCOPY VARCHAR2,
2604 x_errormsg OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
2605
2606 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
2607 l_fee_date DATE;
2608
2609 CURSOR cFeeEffFrom IS
2610 SELECT FEES.EFFECTIVE_FROM EFFECTIVE_FROM
2611 FROM OKL_LEASE_APPLICATIONS_B LAP, OKL_LEASE_QUOTES_B QUOTE, OKL_FEES_B FEES
2612 WHERE FEES.PARENT_OBJECT_CODE = 'LEASEQUOTE'
2613 AND FEES.PARENT_OBJECT_ID = QUOTE.ID
2614 AND QUOTE.PARENT_OBJECT_ID = LAP.ID
2615 AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
2616 AND QUOTE.PRIMARY_QUOTE = 'Y'
2617 AND LAP.ID = l_lease_app_id;
2618
2619
2620 ln_seq_number NUMBER :=1;
2621 BEGIN
2622 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
2623 x_resultout := FND_API.G_RET_STS_SUCCESS;
2624 BEGIN
2625 x_resultout := FND_API.G_RET_STS_SUCCESS;
2626
2627 FOR cFeeEffFromRec IN cFeeEffFrom LOOP
2628 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_id :=
2629 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_id;
2630 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_parent_data_point_id :=
2631 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_parent_data_point_id;
2632 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_sequence_number := ln_seq_number;
2633 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_value :=
2634 cFeeEffFromRec.effective_from;
2635
2636 ln_seq_number := ln_seq_number + 1;
2637
2638 END LOOP;
2639 RETURN NULL;
2640
2641 EXCEPTION
2642 WHEN NO_DATA_FOUND THEN
2643 l_fee_date := NULL;
2644 WHEN OTHERS THEN
2645 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
2646 x_errormsg := sqlerrm;
2647 END;
2648
2649 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_value :=
2650 to_char(l_fee_date) ;
2651
2652 RETURN to_char(l_fee_date);
2653 END fee_date ;
2654
2655 ---------------------------------------------
2656 -- FUNCTION amount_requested
2657 ---------------------------------------------
2658 FUNCTION amount_requested(x_resultout OUT NOCOPY VARCHAR2,
2659 x_errormsg OUT NOCOPY VARCHAR2) RETURN NUMBER IS
2660
2661 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
2662
2663 ln_amount_requested NUMBER;
2664 ln_asset_cost NUMBER;
2665 ln_addon_cost NUMBER;
2666 ln_fee_cost NUMBER;
2667
2668 -- added for bug 6596860 --
2669 CURSOR l_adj_sum_csr(p_lease_app_id IN NUMBER) IS
2670 SELECT NVL(SUM(VALUE),0 )
2671 FROM OKL_LEASE_APPLICATIONS_B LAP, OKL_ASSETS_B ASSET,
2672 OKL_COST_ADJUSTMENTS_B ADJ, OKL_LEASE_QUOTES_B QUOTE
2673 WHERE ADJ.PARENT_OBJECT_CODE = 'ASSET'
2674 AND ADJ.PARENT_OBJECT_ID = ASSET.ID
2675 AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
2676 AND QUOTE.PARENT_OBJECT_ID = LAP.ID
2677 AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
2678 AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
2679 AND QUOTE.PRIMARY_QUOTE = 'Y'
2680 AND ADJ.ADJUSTMENT_SOURCE_TYPE IN ('DOWN_PAYMENT', 'TRADEIN')
2681 AND LAP.ID = p_lease_app_id;
2682
2683 l_adj_amount NUMBER; -- added for bug 6596860 --
2684
2685 BEGIN
2686 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
2687 x_resultout := FND_API.G_RET_STS_SUCCESS;
2688
2689 SELECT NVL(SUM(AST.OEC), 0)
2690 INTO ln_asset_cost
2691 FROM OKL_ASSETS_B AST, OKL_LEASE_QUOTES_B QUOTE, OKL_LEASE_APPLICATIONS_B LAP
2692 WHERE AST.PARENT_OBJECT_ID = QUOTE.ID
2693 AND AST.PARENT_OBJECT_CODE = 'LEASEQUOTE'
2694 AND QUOTE.PARENT_OBJECT_ID = LAP.ID
2695 AND QUOTE.PRIMARY_QUOTE = 'Y'
2696 AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
2697 AND LAP.ID = l_lease_app_id;
2698
2699 /*SELECT NVL(SUM(AST_COMP.UNIT_COST * AST_COMP.NUMBER_OF_UNITS), 0)
2700 INTO ln_addon_cost
2701 FROM OKL_ASSETS_B AST, OKL_ASSET_COMPONENTS_B AST_COMP,
2702 OKL_LEASE_QUOTES_B QUOTE, OKL_LEASE_APPLICATIONS_B LAP
2703 WHERE AST_COMP.ASSET_ID = AST.ID
2704 AND AST_COMP.PRIMARY_COMPONENT = 'NO'
2705 AND AST.PARENT_OBJECT_ID = QUOTE.ID
2706 AND AST.PARENT_OBJECT_CODE = 'LEASEQUOTE'
2707 AND QUOTE.PARENT_OBJECT_ID = LAP.ID
2708 AND QUOTE.PRIMARY_QUOTE = 'Y'
2709 AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
2710 AND LAP.ID = l_lease_app_id;*/
2711
2712 SELECT NVL(SUM(FEES.FEE_AMOUNT), 0)
2713 INTO ln_fee_cost
2714 FROM OKL_LEASE_APPLICATIONS_B LAP, OKL_LEASE_QUOTES_B QUOTE, OKL_FEES_B FEES
2715 WHERE FEES.PARENT_OBJECT_CODE = 'LEASEQUOTE'
2716 AND FEES.PARENT_OBJECT_ID = QUOTE.ID
2717 AND FEES.FEE_TYPE IN ('ROLLOVER', 'FINANCED', 'CAPITALIZED') --Bug 6697231 Added capitalized fee
2718 AND QUOTE.PARENT_OBJECT_ID = LAP.ID
2719 AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
2720 AND QUOTE.PRIMARY_QUOTE = 'Y'
2721 AND LAP.ID = l_lease_app_id;
2722
2723 l_adj_amount :=0; -- added for bug 6596860 --
2724 -- added for bug 6596860 --
2725 OPEN l_adj_sum_csr(l_lease_app_id);
2726 FETCH l_adj_sum_csr INTO l_adj_amount;
2727 CLOSE l_adj_sum_csr;
2728
2729 ln_amount_requested := ln_asset_cost + ln_fee_cost - l_adj_amount ; -- added for bug 6596860 --removded addon and added adj_amount
2730
2731 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_value :=
2732 ln_amount_requested;
2733
2734 RETURN ln_amount_requested;
2735 END amount_requested ;
2736
2737 ---------------------------------------------
2738 -- FUNCTION total_financed_amount
2739 ---------------------------------------------
2740 FUNCTION total_financed_amount(x_resultout OUT NOCOPY VARCHAR2,
2741 x_errormsg OUT NOCOPY VARCHAR2) RETURN NUMBER IS
2742
2743 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
2744 ln_financed_amount NUMBER;
2745
2746 BEGIN
2747 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
2748 x_resultout := FND_API.G_RET_STS_SUCCESS;
2749
2750 ln_financed_amount := amount_requested(x_resultout => x_resultout,
2751 x_errormsg => x_errormsg);
2752
2753 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_value :=
2754 ln_financed_amount ;
2755
2756 RETURN ln_financed_amount;
2757 END total_financed_amount ;
2758
2759 ---------------------------------------------
2760 -- FUNCTION total_subsidized_cost
2761 ---------------------------------------------
2762 FUNCTION total_subsidized_cost(x_resultout OUT NOCOPY VARCHAR2,
2763 x_errormsg OUT NOCOPY VARCHAR2) RETURN NUMBER IS
2764
2765 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
2766
2767 ln_subs_cost NUMBER;
2768 ln_down_payment_amount NUMBER;
2769 ln_financed_amount NUMBER;
2770 ln_subsidy_amount NUMBER;
2771
2772 BEGIN
2773 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
2774 SELECT NVL(SUM(ADJ.VALUE), 0)
2775 INTO ln_down_payment_amount
2776 FROM OKL_LEASE_APPLICATIONS_B LAP, OKL_ASSETS_B ASSET,
2777 OKL_COST_ADJUSTMENTS_B ADJ, OKL_LEASE_QUOTES_B QUOTE
2778 WHERE ADJ.ADJUSTMENT_SOURCE_TYPE IN ('DOWN_PAYMENT', 'TRADEIN')
2779 AND ADJ.PARENT_OBJECT_CODE = 'ASSET'
2780 AND ADJ.PARENT_OBJECT_ID = ASSET.ID
2781 AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
2782 AND QUOTE.PARENT_OBJECT_ID = LAP.ID
2783 AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
2784 AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
2785 AND QUOTE.PRIMARY_QUOTE = 'Y'
2786 AND LAP.ID = l_lease_app_id;
2787
2788 x_resultout := FND_API.G_RET_STS_SUCCESS;
2789
2790 ln_financed_amount := amount_requested(x_resultout => x_resultout,
2791 x_errormsg => x_errormsg);
2792
2793 ln_subsidy_amount := subsidy_amount(x_resultout => x_resultout,
2794 x_errormsg => x_errormsg);
2795
2796 ln_subs_cost := ln_financed_amount - ln_down_payment_amount - ln_subsidy_amount;
2797
2798 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_value := ln_subs_cost;
2799
2800 RETURN ln_subs_cost;
2801 END total_subsidized_cost ;
2802
2803 ---------------------------------------------
2804 -- FUNCTION security_deposit
2805 ---------------------------------------------
2806 FUNCTION security_deposit(x_resultout OUT NOCOPY VARCHAR2,
2807 x_errormsg OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
2808
2809 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
2810 l_security_deposit VARCHAR2(120);
2811
2812 CURSOR cSecDeposit IS
2813 SELECT FEES.FEE_AMOUNT FEE_AMOUNT
2814 FROM OKL_LEASE_APPLICATIONS_B LAP, OKL_LEASE_QUOTES_B QUOTE, OKL_FEES_B FEES
2815 WHERE FEES.PARENT_OBJECT_CODE = 'LEASEQUOTE'
2816 AND FEES.PARENT_OBJECT_ID = QUOTE.ID
2817 AND QUOTE.PARENT_OBJECT_ID = LAP.ID
2818 AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
2819 AND QUOTE.PRIMARY_QUOTE = 'Y'
2820 AND FEES.FEE_TYPE = 'SEC_DEPOSIT'
2821 AND LAP.ID = l_lease_app_id;
2822
2823
2824 ln_seq_number NUMBER :=1;
2825
2826 BEGIN
2827 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
2828 x_resultout := FND_API.G_RET_STS_SUCCESS;
2829 BEGIN
2830 x_resultout := FND_API.G_RET_STS_SUCCESS;
2831
2832 FOR cSecDepositRec IN cSecDeposit
2833 LOOP
2834 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_id :=
2835 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_id;
2836 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_parent_data_point_id :=
2837 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_parent_data_point_id;
2838 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_sequence_number := ln_seq_number;
2839 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_value :=
2840 cSecDepositRec.fee_amount;
2841
2842 ln_seq_number := ln_seq_number + 1;
2843
2844 END LOOP;
2845 RETURN NULL;
2846
2847
2848 EXCEPTION
2849 WHEN NO_DATA_FOUND THEN
2850 l_security_deposit := NULL;
2851 WHEN OTHERS THEN
2852 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
2853 x_errormsg := sqlerrm;
2854 END;
2855
2856 END security_deposit ;
2857
2858 ---------------------------------------------
2859 -- FUNCTION billed_tax
2860 ---------------------------------------------
2861 FUNCTION billed_tax(x_resultout OUT NOCOPY VARCHAR2,
2862 x_errormsg OUT NOCOPY VARCHAR2) RETURN NUMBER IS
2863
2864 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
2865 l_billed_tax NUMBER;
2866
2867 BEGIN
2868 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
2869 x_resultout := FND_API.G_RET_STS_SUCCESS;
2870 BEGIN
2871 SELECT SUM(TRXD.TAX_AMT)
2872 INTO l_billed_tax
2873 FROM
2874 OKL_TAX_SOURCES TXS, OKL_TAX_TRX_DETAILS TRXD, OKL_LEASE_QUOTES_B LSQ,
2875 OKL_LEASE_APPLICATIONS_B LAP
2876 WHERE
2877 TRXD.TXS_ID = TXS.ID
2878 AND TRXD.BILLED_YN = 'Y'
2879 AND TXS.TRX_ID = LSQ.ID
2880 AND TXS.TAX_CALL_TYPE_CODE = 'UPFRONT_TAX'
2881 AND TXS.TAX_CALL_TYPE_CODE = TRXD.TAX_CALL_TYPE_CODE
2882 AND TXS.ENTITY_CODE = 'OKL_LEASE_QUOTES_B'
2883 AND TXS.TAX_LINE_STATUS_CODE = 'ACTIVE'
2884 AND LSQ.PARENT_OBJECT_ID = LAP.ID
2885 AND LSQ.PARENT_OBJECT_CODE = 'LEASEAPP'
2886 AND LAP.ID = l_lease_app_id;
2887
2888 EXCEPTION
2889 WHEN NO_DATA_FOUND THEN
2890 l_billed_tax := NULL;
2891 WHEN OTHERS THEN
2892 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
2893 x_errormsg := sqlerrm;
2894 END;
2895
2896 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_value :=
2897 l_billed_tax;
2898
2899 RETURN l_billed_tax;
2900 END billed_tax ;
2901
2902 ---------------------------------------------
2903 -- PROCEDURE fetch_leaseapp_datapoints
2904 ---------------------------------------------
2905 PROCEDURE fetch_leaseapp_datapoints(p_api_version IN NUMBER
2906 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
2907 ,p_leaseapp_id IN NUMBER
2908 ,x_lap_dp_tbl_type OUT NOCOPY lap_dp_tbl_type
2909 ,x_return_status OUT NOCOPY VARCHAR2
2910 ,x_msg_count OUT NOCOPY NUMBER
2911 ,x_msg_data OUT NOCOPY VARCHAR2) IS
2912
2913 l_program_name CONSTANT VARCHAR2(30) := 'fetch_leaseapp_datapoints';
2914 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
2915
2916 CURSOR c_get_leaseapp_template IS
2917 SELECT lat.credit_review_purpose,
2918 lat.cust_credit_classification
2919 FROM
2920 okl_leaseapp_templates lat,
2921 okl_leaseapp_templ_versions_b latv,
2922 okl_lease_applications_b lap
2923 WHERE
2924 lap.leaseapp_template_id = latv.id
2925 AND latv.version_status = 'ACTIVE'
2926 AND latv.valid_from <= lap.valid_from
2927 AND nvl(latv.valid_to, lap.valid_from) >= lap.valid_from
2928 AND latv.leaseapp_template_id = lat.id
2929 AND lap.id = p_leaseapp_id;
2930
2931 lv_credit_rev_purpose VARCHAR2(30);
2932 lv_credit_classification VARCHAR2(30);
2933
2934 x_data_points_tbl OCM_DATA_POINTS_PUB.data_points_tbl;
2935
2936 BEGIN
2937 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
2938 IF p_init_msg_list = G_TRUE THEN
2939 FND_MSG_PUB.initialize;
2940 END IF;
2941
2942 OPEN c_get_leaseapp_template;
2943 FETCH c_get_leaseapp_template INTO lv_credit_rev_purpose, lv_credit_classification;
2944 CLOSE c_get_leaseapp_template;
2945
2946 OCM_DATA_POINTS_PUB.GET_DATA_POINTS(p_api_version => p_api_version,
2947 p_init_msg_list => p_init_msg_list,
2948 p_validation_level => 'F',
2949 p_credit_classification => lv_credit_classification,
2950 p_review_type => lv_credit_rev_purpose,
2951 p_data_point_category => 'ADDITIONAL',
2952 p_data_point_sub_category => 'OKL_LAP_DATAPOINT',
2953 x_return_status => x_return_status,
2954 x_msg_count => x_msg_count,
2955 x_msg_data => x_msg_data,
2956 p_datapoints_tbl => x_data_points_tbl);
2957 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2958 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2959 ELSIF x_return_status = G_RET_STS_ERROR THEN
2960 RAISE OKL_API.G_EXCEPTION_ERROR;
2961 END IF;
2962
2963 IF (x_data_points_tbl.COUNT > 0) THEN
2964 FOR i IN x_data_points_tbl.FIRST .. x_data_points_tbl.LAST LOOP
2965 IF x_data_points_tbl.EXISTS(i) THEN
2966 x_lap_dp_tbl_type(i).data_point_id := x_data_points_tbl(i).data_point_id;
2967 x_lap_dp_tbl_type(i).data_point_category := x_data_points_tbl(i).data_point_sub_category;
2968 x_lap_dp_tbl_type(i).data_point_name := x_data_points_tbl(i).data_point_name;
2969 x_lap_dp_tbl_type(i).description := x_data_points_tbl(i).description;
2970 END IF;
2971 END LOOP;
2972 END IF;
2973
2974 x_return_status := G_RET_STS_SUCCESS;
2975
2976 EXCEPTION
2977 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2978 IF (c_get_leaseapp_template%ISOPEN) THEN
2979 CLOSE c_get_leaseapp_template;
2980 END IF;
2981
2982 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2983 IF (c_get_leaseapp_template%ISOPEN) THEN
2984 CLOSE c_get_leaseapp_template;
2985 END IF;
2986
2987 WHEN OTHERS THEN
2988 IF (c_get_leaseapp_template%ISOPEN) THEN
2989 CLOSE c_get_leaseapp_template;
2990 END IF;
2991
2992 END fetch_leaseapp_datapoints;
2993
2994 ---------------------------------------------
2995 -- PROCEDURE store_leaseapp_datapoints
2996 ---------------------------------------------
2997 PROCEDURE store_leaseapp_datapoints(p_api_version IN NUMBER
2998 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
2999 ,p_lap_dp_tbl IN lap_dp_tbl_type
3000 ,x_return_status OUT NOCOPY VARCHAR2
3001 ,x_msg_count OUT NOCOPY NUMBER
3002 ,x_msg_data OUT NOCOPY VARCHAR2) IS
3003
3004 l_program_name CONSTANT VARCHAR2(30) := 'store_leaseapp_datapoints';
3005 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
3006
3007 j BINARY_INTEGER := 0;
3008
3009 l_lap_dp_tbl lap_dp_tbl_type;
3010 lx_lap_dp_tbl lap_dp_tbl_type;
3011
3012 lp_lap_dp_tbl okl_lad_pvt.ladv_tbl_type;
3013 lpx_lap_dp_tbl okl_lad_pvt.ladv_tbl_type;
3014
3015 ln_index NUMBER;
3016
3017 BEGIN
3018 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
3019 IF p_init_msg_list = G_TRUE THEN
3020 FND_MSG_PUB.initialize;
3021 END IF;
3022
3023 l_lap_dp_tbl := p_lap_dp_tbl;
3024
3025 IF (l_lap_dp_tbl.COUNT > 0) THEN
3026
3027 FOR i IN l_lap_dp_tbl.FIRST .. l_lap_dp_tbl.LAST LOOP
3028 IF l_lap_dp_tbl.EXISTS(i) THEN
3029 lp_lap_dp_tbl(i).id := l_lap_dp_tbl(i).id;
3030 lp_lap_dp_tbl(i).object_version_number := l_lap_dp_tbl(i).object_version_number;
3031 lp_lap_dp_tbl(i).leaseapp_id := l_lap_dp_tbl(i).leaseapp_id;
3032 lp_lap_dp_tbl(i).data_point_id := l_lap_dp_tbl(i).data_point_id;
3033 lp_lap_dp_tbl(i).data_point_category := l_lap_dp_tbl(i).data_point_category;
3034 lp_lap_dp_tbl(i).data_point_value := l_lap_dp_tbl(i).data_point_value;
3035 END IF;
3036 END LOOP;
3037
3038 ln_index := lp_lap_dp_tbl.FIRST;
3039 IF (lp_lap_dp_tbl(ln_index).id is null OR lp_lap_dp_tbl(ln_index).id = OKL_API.G_MISS_NUM) THEN
3040
3041 okl_lad_pvt.insert_row (p_api_version => G_API_VERSION
3042 ,p_init_msg_list => G_FALSE
3043 ,x_return_status => x_return_status
3044 ,x_msg_count => x_msg_count
3045 ,x_msg_data => x_msg_data
3046 ,p_ladv_tbl => lp_lap_dp_tbl
3047 ,x_ladv_tbl => lpx_lap_dp_tbl);
3048 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3049 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3050 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3051 RAISE OKL_API.G_EXCEPTION_ERROR;
3052 END IF;
3053 ELSE
3054 okl_lad_pvt.update_row (p_api_version => G_API_VERSION
3055 ,p_init_msg_list => G_FALSE
3056 ,x_return_status => x_return_status
3057 ,x_msg_count => x_msg_count
3058 ,x_msg_data => x_msg_data
3059 ,p_ladv_tbl => lp_lap_dp_tbl
3060 ,x_ladv_tbl => lpx_lap_dp_tbl);
3061 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3062 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3063 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3064 RAISE OKL_API.G_EXCEPTION_ERROR;
3065 END IF;
3066 END IF;
3067 END IF;
3068
3069 x_return_status := G_RET_STS_SUCCESS;
3070
3071 EXCEPTION
3072 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3073 x_return_status := G_RET_STS_ERROR;
3074 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3075 x_return_status := G_RET_STS_UNEXP_ERROR;
3076 WHEN OTHERS THEN
3077 x_return_status := G_RET_STS_ERROR;
3078 END store_leaseapp_datapoints;
3079
3080 ---------------------------------------------
3081 -- PROCEDURE delete_leaseapp_datapoints
3082 ---------------------------------------------
3083 PROCEDURE delete_leaseapp_datapoints(p_api_version IN NUMBER
3084 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
3085 ,p_leaseapp_id IN NUMBER
3086 ,x_return_status OUT NOCOPY VARCHAR2
3087 ,x_msg_count OUT NOCOPY NUMBER
3088 ,x_msg_data OUT NOCOPY VARCHAR2) IS
3089
3090 l_program_name CONSTANT VARCHAR2(30) := 'delete_leaseapp_datapoints';
3091 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
3092
3093 i BINARY_INTEGER := 0;
3094
3095 lap_dp_tbl okl_lad_pvt.ladv_tbl_type;
3096 l_error_tbl_type OKC_API.ERROR_TBL_TYPE;
3097
3098 CURSOR c_get_leaseapp_datapoints IS
3099 SELECT id
3100 FROM
3101 okl_leaseapp_datapoints
3102 WHERE
3103 leaseapp_id = p_leaseapp_id;
3104
3105 BEGIN
3106 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
3107 IF p_init_msg_list = G_TRUE THEN
3108 FND_MSG_PUB.initialize;
3109 END IF;
3110
3111 FOR l_get_leaseapp_datapoints IN c_get_leaseapp_datapoints LOOP
3112 lap_dp_tbl(i).id := l_get_leaseapp_datapoints.id;
3113 i := i + 1;
3114 END LOOP;
3115
3116 IF lap_dp_tbl.COUNT > 0 THEN
3117 okl_lad_pvt.delete_row (
3118 p_api_version => G_API_VERSION
3119 ,p_init_msg_list => G_FALSE
3120 ,x_return_status => x_return_status
3121 ,x_msg_count => x_msg_count
3122 ,x_msg_data => x_msg_data
3123 ,p_ladv_tbl => lap_dp_tbl
3124 ,px_error_tbl => l_error_tbl_type );
3125
3126 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
3127 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3128 ELSIF x_return_status = G_RET_STS_ERROR THEN
3129 RAISE OKL_API.G_EXCEPTION_ERROR;
3130 END IF;
3131 END IF;
3132
3133 x_return_status := G_RET_STS_SUCCESS;
3134
3135 EXCEPTION
3136 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3137 x_return_status := G_RET_STS_ERROR;
3138 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3139 x_return_status := G_RET_STS_UNEXP_ERROR;
3140 WHEN OTHERS THEN
3141 x_return_status := G_RET_STS_ERROR;
3142 END delete_leaseapp_datapoints;
3143
3144 ---------------------------------------------
3145 -- FUNCTION leaseapp_datapoints_exists
3146 ---------------------------------------------
3147 FUNCTION leaseapp_datapoints_exists(p_leaseapp_id IN NUMBER)
3148 RETURN BOOLEAN IS
3149
3150 ln_dp_count NUMBER;
3151
3152 CURSOR c_get_leaseapp_datapoints IS
3153 SELECT count(*)
3154 FROM
3155 okl_leaseapp_datapoints
3156 WHERE
3157 leaseapp_id = p_leaseapp_id;
3158
3159 BEGIN
3160 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
3161 OPEN c_get_leaseapp_datapoints;
3162 FETCH c_get_leaseapp_datapoints INTO ln_dp_count;
3163 CLOSE c_get_leaseapp_datapoints;
3164
3165 IF (ln_dp_count > 0) THEN
3166 RETURN TRUE;
3167 ELSE
3168 RETURN FALSE;
3169 END IF;
3170 END leaseapp_datapoints_exists ;
3171
3172 FUNCTION fetch_data_point_value(x_resultout OUT NOCOPY VARCHAR2,
3173 x_errormsg OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
3174
3175 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
3176 l_data_point_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_id);
3177
3178 lv_data_point_value okl_leaseapp_datapoints.data_point_value%TYPE;
3179
3180 BEGIN
3181 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
3182 x_resultout := FND_API.G_RET_STS_SUCCESS;
3183 BEGIN
3184 SELECT DATA_POINT_VALUE
3185 INTO lv_data_point_value
3186 FROM OKL_LEASEAPP_DATAPOINTS
3187 WHERE DATA_POINT_ID = l_data_point_id
3188 AND LEASEAPP_ID = l_lease_app_id;
3189
3190 EXCEPTION
3191 WHEN NO_DATA_FOUND THEN
3192 lv_data_point_value := NULL;
3193 WHEN OTHERS THEN
3194 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
3195 x_errormsg := sqlerrm;
3196 END;
3197
3198 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_value := lv_data_point_value;
3199
3200 RETURN lv_data_point_value;
3201
3202 END fetch_data_point_value ;
3203
3204 END OKL_CREDIT_DATAPOINTS_PVT;