[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.5 2008/12/22 10:39:04 kkorrapo 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 l_manufacturer VARCHAR2(30);
731
732 CURSOR cMfgName is
733 SELECT ASSET_COMP.MANUFACTURER_NAME MANUFACTURER_NAME
734 FROM OKL_ASSET_COMPONENTS_B ASSET_COMP, OKL_LEASE_APPLICATIONS_B LAP,
735 OKL_ASSETS_B ASSET, OKL_LEASE_QUOTES_B QUOTE
736 WHERE ASSET_COMP.ASSET_ID = ASSET.ID
737 AND ASSET_COMP.PRIMARY_COMPONENT = 'YES'
738 AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
739 AND QUOTE.PARENT_OBJECT_ID = LAP.ID
740 AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
741 AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
742 AND QUOTE.PRIMARY_QUOTE = 'Y'
743 AND LAP.ID = l_lease_app_id;
744
745 ln_seq_number NUMBER :=1;
746
747 BEGIN
748 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
749 x_resultout := FND_API.G_RET_STS_SUCCESS;
750 BEGIN
751
752 x_resultout := FND_API.G_RET_STS_SUCCESS;
753
754 FOR cMfgNameRec IN cMfgName LOOP
755 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_id :=
756 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_id;
757 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_parent_data_point_id :=
758 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_parent_data_point_id;
759 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_sequence_number := ln_seq_number;
760 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_value :=
761 cMfgNameRec.manufacturer_name;
762
763 ln_seq_number := ln_seq_number + 1;
764
765 END LOOP;
766 RETURN NULL;
767
768 EXCEPTION
769 WHEN NO_DATA_FOUND THEN
770 l_manufacturer := NULL;
771 WHEN OTHERS THEN
772 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
773 x_errormsg := sqlerrm;
774 END;
775 END manufacturer;
776
777 ---------------------------------------------
778 -- FUNCTION year_of_manufacture
779 ---------------------------------------------
780 FUNCTION year_of_manufacture(x_resultout OUT NOCOPY VARCHAR2,
781 x_errormsg OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
782
783 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
784 l_year_of_manufacture NUMBER;
785
786 CURSOR cYearMfg IS
787 SELECT ASSET_COMP.YEAR_MANUFACTURED YEAR_MANUFACTURED
788 FROM OKL_ASSET_COMPONENTS_B ASSET_COMP, OKL_LEASE_APPLICATIONS_B LAP,
789 OKL_ASSETS_B ASSET, OKL_LEASE_QUOTES_B QUOTE
790 WHERE ASSET_COMP.ASSET_ID = ASSET.ID
791 AND ASSET_COMP.PRIMARY_COMPONENT = 'YES'
792 AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
793 AND QUOTE.PARENT_OBJECT_ID = LAP.ID
794 AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
795 AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
796 AND QUOTE.PRIMARY_QUOTE = 'Y'
797 AND LAP.ID = l_lease_app_id;
798
799 ln_seq_number NUMBER :=1;
800
801 BEGIN
802 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
803 x_resultout := FND_API.G_RET_STS_SUCCESS;
804 BEGIN
805 x_resultout := FND_API.G_RET_STS_SUCCESS;
806
807 FOR cYearMfgRec IN cYearMfg LOOP
808 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_id :=
809 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_id;
810 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_parent_data_point_id :=
811 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_parent_data_point_id;
812 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_sequence_number := ln_seq_number;
813 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_value :=
814 cYearMfgRec.year_manufactured;
815
816 ln_seq_number := ln_seq_number + 1;
817
818 END LOOP;
819 RETURN NULL;
820
821 EXCEPTION
822 WHEN NO_DATA_FOUND THEN
823 l_year_of_manufacture := NULL;
824 WHEN OTHERS THEN
825 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
826 x_errormsg := sqlerrm;
827 END;
828 END year_of_manufacture ;
829
830 ---------------------------------------------
831 -- FUNCTION no_of_units
832 ---------------------------------------------
833 FUNCTION no_of_units(x_resultout OUT NOCOPY VARCHAR2,
834 x_errormsg OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
835
836 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
837 l_no_of_units NUMBER;
838
839 CURSOR cNoOfUnits IS
840 SELECT ASSET_COMP.NUMBER_OF_UNITS NUMBER_OF_UNITS
841 FROM OKL_ASSET_COMPONENTS_B ASSET_COMP, OKL_LEASE_APPLICATIONS_B LAP,
842 OKL_ASSETS_B ASSET, OKL_LEASE_QUOTES_B QUOTE
843 WHERE ASSET_COMP.ASSET_ID = ASSET.ID
844 AND ASSET_COMP.PRIMARY_COMPONENT = 'YES'
845 AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
846 AND QUOTE.PARENT_OBJECT_ID = LAP.ID
847 AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
848 AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
849 AND QUOTE.PRIMARY_QUOTE = 'Y'
850 AND LAP.ID = l_lease_app_id;
851
852 ln_seq_number NUMBER :=1;
853 BEGIN
854 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
855 x_resultout := FND_API.G_RET_STS_SUCCESS;
856 BEGIN
857 x_resultout := FND_API.G_RET_STS_SUCCESS;
858
859 FOR cNoOfUnitsRec IN cNoOfUnits LOOP
860 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_id :=
861 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_id;
862 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_parent_data_point_id :=
863 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_parent_data_point_id;
864 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_sequence_number := ln_seq_number;
865 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_value :=
866 cNoOfUnitsRec.number_of_units;
867
868 ln_seq_number := ln_seq_number + 1;
869
870 END LOOP;
871 RETURN NULL;
872
873 EXCEPTION
874 WHEN NO_DATA_FOUND THEN
875 l_no_of_units := NULL;
876 WHEN OTHERS THEN
877 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
878 x_errormsg := sqlerrm;
879 END;
880 END no_of_units ;
881
882 ---------------------------------------------
883 -- FUNCTION unit_cost
884 ---------------------------------------------
885 FUNCTION unit_cost(x_resultout OUT NOCOPY VARCHAR2,
886 x_errormsg OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
887
888 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
889 l_unit_cost NUMBER;
890
891 CURSOR cUnitCost IS
892 SELECT ASSET_COMP.UNIT_COST UNIT_COST
893 FROM OKL_ASSET_COMPONENTS_B ASSET_COMP, OKL_LEASE_APPLICATIONS_B LAP,
894 OKL_ASSETS_B ASSET, OKL_LEASE_QUOTES_B QUOTE
895 WHERE ASSET_COMP.ASSET_ID = ASSET.ID
896 AND ASSET_COMP.PRIMARY_COMPONENT = 'YES'
897 AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
898 AND QUOTE.PARENT_OBJECT_ID = LAP.ID
899 AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
900 AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
901 AND QUOTE.PRIMARY_QUOTE = 'Y'
902 AND LAP.ID = l_lease_app_id;
903
904 ln_seq_number NUMBER :=1;
905
906
907 BEGIN
908 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
909 x_resultout := FND_API.G_RET_STS_SUCCESS;
910 BEGIN
911 x_resultout := FND_API.G_RET_STS_SUCCESS;
912
913 FOR cUnitCostRec IN cUnitCost LOOP
914 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_id :=
915 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_id;
916 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_parent_data_point_id :=
917 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_parent_data_point_id;
918 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_sequence_number := ln_seq_number;
919 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_value :=
920 cUnitCostRec.unit_cost;
921
922 ln_seq_number := ln_seq_number + 1;
923
924 END LOOP;
925 RETURN NULL;
926
927 EXCEPTION
928 WHEN NO_DATA_FOUND THEN
929 l_unit_cost := NULL;
930 WHEN OTHERS THEN
931 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
932 x_errormsg := sqlerrm;
933 END;
934 END unit_cost ;
935
936 ---------------------------------------------
937 -- FUNCTION install_site
938 ---------------------------------------------
939 FUNCTION install_site(x_resultout OUT NOCOPY VARCHAR2,
940 x_errormsg OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
941
942 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
943 l_install_site VARCHAR2(30);
944
945 CURSOR cInstallSite IS
946 SELECT PARTY_SITE.PARTY_SITE_NUMBER PARTY_SITE_NUMBER
947 FROM HZ_PARTY_SITES PARTY_SITE, OKL_LEASE_APPLICATIONS_B LAP,
948 OKL_ASSETS_B ASSET, OKL_LEASE_QUOTES_B QUOTE
949 WHERE PARTY_SITE.PARTY_SITE_ID = ASSET.INSTALL_SITE_ID
950 AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
951 AND QUOTE.PARENT_OBJECT_ID = LAP.ID
952 AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
953 AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
954 AND QUOTE.PRIMARY_QUOTE = 'Y'
955 AND LAP.ID = l_lease_app_id;
956
957 ln_seq_number NUMBER :=1;
958
959 BEGIN
960 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
961 x_resultout := FND_API.G_RET_STS_SUCCESS;
962 BEGIN
963 x_resultout := FND_API.G_RET_STS_SUCCESS;
964
965 FOR cInstallSiteRec IN cInstallSite LOOP
966 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_id :=
967 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_id;
968 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_parent_data_point_id :=
969 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_parent_data_point_id;
970 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_sequence_number := ln_seq_number;
971 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_value :=
972 cInstallSiteRec.party_site_number;
973
974 ln_seq_number := ln_seq_number + 1;
975
976 END LOOP;
977 RETURN NULL;
978
979 EXCEPTION
980 WHEN NO_DATA_FOUND THEN
981 l_install_site := NULL;
982 WHEN OTHERS THEN
983 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
984 x_errormsg := sqlerrm;
985 END;
986 END install_site ;
987
988 ---------------------------------------------
989 -- FUNCTION usage_of_equipment
990 ---------------------------------------------
991 FUNCTION usage_of_equipment(x_resultout OUT NOCOPY VARCHAR2,
992 x_errormsg OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
993
994 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
995 l_usage_of_equipment VARCHAR2(30);
996
997 BEGIN
998 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
999 x_resultout := FND_API.G_RET_STS_SUCCESS;
1000 BEGIN
1001 -- gboomina Bug 7110500 - Start
1002 SELECT QUOTE.USAGE_CATEGORY --QUOTE.USAGE_INDUSTRY_CLASS
1003 INTO l_usage_of_equipment
1004 FROM OKL_LEASE_APPLICATIONS_B LAP, OKL_LEASE_QUOTES_B QUOTE
1005 WHERE QUOTE.PARENT_OBJECT_ID = LAP.ID
1006 AND QUOTE.PRIMARY_QUOTE = 'Y'
1007 AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
1008 AND LAP.ID = l_lease_app_id;
1009 -- gboomina Bug 7110500 - End
1010
1011
1012 EXCEPTION
1013 WHEN NO_DATA_FOUND THEN
1014 l_usage_of_equipment := NULL;
1015 WHEN OTHERS THEN
1016 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
1017 x_errormsg := sqlerrm;
1018 END;
1019
1020 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_value :=
1021 to_char(l_usage_of_equipment) ;
1022
1023 RETURN to_char(l_usage_of_equipment );
1024 END usage_of_equipment ;
1025
1026 ---------------------------------------------
1027 -- FUNCTION usage_industry
1028 ---------------------------------------------
1029 FUNCTION usage_industry(x_resultout OUT NOCOPY VARCHAR2,
1030 x_errormsg OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
1031
1032 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
1033 l_usage_industry VARCHAR2(1995);
1034
1035 BEGIN
1036 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
1037 x_resultout := FND_API.G_RET_STS_SUCCESS;
1038 BEGIN
1039 -- gboomina Bug 7110500 - Start
1040 /*
1041 SELECT QUOTE.USAGE_INDUSTRY_CODE
1042 INTO l_usage_industry
1043 FROM OKL_LEASE_APPLICATIONS_B LAP, OKL_LEASE_QUOTES_B QUOTE
1044 WHERE QUOTE.PARENT_OBJECT_ID = LAP.ID
1045 AND QUOTE.PRIMARY_QUOTE = 'Y'
1046 AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
1047 AND LAP.ID = l_lease_app_id;
1048 */
1049
1050 SELECT INDC.MEANING ||' - '||IND.MEANING USAGE_INDUSTRY
1051 INTO l_usage_industry
1052 FROM OKL_LEASE_APPLICATIONS_B LAP,AR_LOOKUPS INDC, AR_LOOKUPS IND
1053 WHERE INDC.LOOKUP_TYPE(+)='SIC_CODE_TYPE'
1054 AND INDC.LOOKUP_CODE(+)=LAP.INDUSTRY_CLASS
1055 AND IND.LOOKUP_TYPE(+)=LAP.INDUSTRY_CLASS
1056 AND IND.LOOKUP_CODE(+)=LAP.INDUSTRY_CODE
1057 AND LAP.id= l_lease_app_id;
1058
1059 -- gboomina Bug 7110500 - End
1060
1061 EXCEPTION
1062 WHEN NO_DATA_FOUND THEN
1063 l_usage_industry := NULL;
1064 WHEN OTHERS THEN
1065 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
1066 x_errormsg := sqlerrm;
1067 END;
1068
1069 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_value :=
1070 to_char(l_usage_industry) ;
1071
1072 RETURN to_char(l_usage_industry);
1073 END usage_industry ;
1074
1075 ---------------------------------------------
1076 -- FUNCTION usage_category
1077 ---------------------------------------------
1078 FUNCTION usage_category(x_resultout OUT NOCOPY VARCHAR2,
1079 x_errormsg OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
1080
1081 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
1082 l_usage_category VARCHAR2(30);
1083
1084 BEGIN
1085 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
1086 x_resultout := FND_API.G_RET_STS_SUCCESS;
1087 BEGIN
1088 SELECT QUOTE.USAGE_CATEGORY
1089 INTO l_usage_category
1090 FROM OKL_LEASE_APPLICATIONS_B LAP, OKL_LEASE_QUOTES_B QUOTE
1091 WHERE QUOTE.PARENT_OBJECT_ID = LAP.ID
1092 AND QUOTE.PRIMARY_QUOTE = 'Y'
1093 AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
1094 AND LAP.ID = l_lease_app_id;
1095
1096
1097 EXCEPTION
1098 WHEN NO_DATA_FOUND THEN
1099 l_usage_category := NULL;
1100 WHEN OTHERS THEN
1101 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
1102 x_errormsg := sqlerrm;
1103 END;
1104
1105 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_value :=
1106 to_char(l_usage_category) ;
1107
1108 RETURN to_char(l_usage_category);
1109 END usage_category ;
1110
1111 ---------------------------------------------
1112 -- FUNCTION usage_amount
1113 ---------------------------------------------
1114 FUNCTION usage_amount(x_resultout OUT NOCOPY VARCHAR2,
1115 x_errormsg OUT NOCOPY VARCHAR2) RETURN NUMBER IS
1116
1117 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
1118 l_usage_amount NUMBER;
1119
1120 BEGIN
1121 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
1122 x_resultout := FND_API.G_RET_STS_SUCCESS;
1123 BEGIN
1124 -- gboomina Bug 7110500 - Start
1125 /*
1126 SELECT QUOTE.USAGE_AMOUNT
1127 INTO l_usage_amount
1128 FROM OKL_LEASE_APPLICATIONS_B LAP, OKL_LEASE_QUOTES_B QUOTE
1129 WHERE QUOTE.PARENT_OBJECT_ID = LAP.ID
1130 AND QUOTE.PRIMARY_QUOTE = 'Y'
1131 AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
1132 AND LAP.ID = l_lease_app_id;
1133 */
1134 SELECT LOP.USAGE_AMOUNT
1135 INTO l_usage_amount
1136 FROM OKL_LEASE_APPLICATIONS_B LAP, OKL_LEASE_OPPORTUNITIES_B LOP
1137 WHERE LOP.ID= LAP.LEASE_OPPORTUNITY_ID
1138 AND LAP.ID = l_lease_app_id;
1139
1140 -- gboomina Bug 7110500 - End
1141
1142 EXCEPTION
1143 WHEN NO_DATA_FOUND THEN
1144 l_usage_amount := NULL;
1145 WHEN OTHERS THEN
1146 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
1147 x_errormsg := sqlerrm;
1148 END;
1149
1150 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_value :=
1151 l_usage_amount;
1152
1153 RETURN l_usage_amount;
1154 END usage_amount ;
1155
1156 ---------------------------------------------
1157 -- FUNCTION add_on_item
1158 ---------------------------------------------
1159 FUNCTION add_on_item(x_resultout OUT NOCOPY VARCHAR2,
1160 x_errormsg OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
1161
1162 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
1163 l_asset_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_value_id);
1164
1165 l_add_on_item VARCHAR2(40);
1166
1167 CURSOR cAddOnItem IS
1168 SELECT ITEM.CONCATENATED_SEGMENTS ADD_ON_ITEM
1169 FROM MTL_SYSTEM_ITEMS_KFV ITEM, OKL_ASSET_COMPONENTS_B ASSET_COMP
1170 WHERE ITEM.INVENTORY_ITEM_ID = ASSET_COMP.INV_ITEM_ID
1171 AND ITEM.ORGANIZATION_ID = OKL_SYSTEM_PARAMS_ALL_PUB.get_system_param_value(OKL_SYSTEM_PARAMS_ALL_PUB.G_ITEM_INV_ORG_ID)
1172 AND ASSET_COMP.ASSET_ID = l_asset_id
1173 AND ASSET_COMP.PRIMARY_COMPONENT = 'NO';
1174
1175 ln_seq_number NUMBER :=1;
1176 BEGIN
1177 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
1178 x_resultout := FND_API.G_RET_STS_SUCCESS;
1179 BEGIN
1180 x_resultout := FND_API.G_RET_STS_SUCCESS;
1181
1182 FOR cAddOnItemRec IN cAddOnItem LOOP
1183 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_id :=
1184 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_id;
1185 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_parent_data_point_id := l_asset_id;
1186 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_sequence_number := ln_seq_number;
1187 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_value :=
1188 cAddOnItemRec.add_on_item;
1189
1190 ln_seq_number := ln_seq_number + 1;
1191 END LOOP;
1192
1193 RETURN NULL;
1194
1195 EXCEPTION
1196 WHEN NO_DATA_FOUND THEN
1197 l_add_on_item := NULL;
1198 WHEN OTHERS THEN
1199 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
1200 x_errormsg := sqlerrm;
1201 END;
1202 END add_on_item ;
1203
1204 ---------------------------------------------
1205 -- FUNCTION add_on_item_description
1206 ---------------------------------------------
1207 FUNCTION add_on_item_description(x_resultout OUT NOCOPY VARCHAR2,
1208 x_errormsg OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
1209
1210 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
1211 l_asset_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_value_id);
1212
1213 l_add_on_item_description VARCHAR2(1995);
1214
1215 CURSOR cItemDescription IS
1216 SELECT ITEM.CONCATENATED_SEGMENTS ADD_ON_ITEM
1217 FROM MTL_SYSTEM_ITEMS_KFV ITEM, OKL_ASSET_COMPONENTS_B ASSET_COMP
1218 WHERE ITEM.INVENTORY_ITEM_ID = ASSET_COMP.INV_ITEM_ID
1219 AND ITEM.ORGANIZATION_ID = OKL_SYSTEM_PARAMS_ALL_PUB.get_system_param_value(OKL_SYSTEM_PARAMS_ALL_PUB.G_ITEM_INV_ORG_ID)
1220 AND ASSET_COMP.ASSET_ID = l_asset_id
1221 AND ASSET_COMP.PRIMARY_COMPONENT = 'NO';
1222
1223 ln_seq_number NUMBER :=1;
1224 BEGIN
1225 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
1226 x_resultout := FND_API.G_RET_STS_SUCCESS;
1227 BEGIN
1228
1229 x_resultout := FND_API.G_RET_STS_SUCCESS;
1230
1231 FOR cItemDescriptionRec IN cItemDescription LOOP
1232 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_id :=
1233 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_id;
1234 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_parent_data_point_id :=
1235 l_asset_id;
1236 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_sequence_number := ln_seq_number;
1237 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_value :=
1238 cItemDescriptionRec.add_on_item;
1239
1240 ln_seq_number := ln_seq_number + 1;
1241
1242 END LOOP;
1243 RETURN NULL;
1244
1245 EXCEPTION
1246 WHEN NO_DATA_FOUND THEN
1247 l_add_on_item_description := NULL;
1248 WHEN OTHERS THEN
1249 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
1250 x_errormsg := sqlerrm;
1251 END;
1252 END add_on_item_description ;
1253
1254 ---------------------------------------------
1255 -- FUNCTION add_on_item_supplier
1256 ---------------------------------------------
1257 FUNCTION add_on_item_supplier(x_resultout OUT NOCOPY VARCHAR2,
1258 x_errormsg OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
1259
1260 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
1261 l_asset_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_value_id);
1262
1263 l_add_on_item_supplier VARCHAR2(30);
1264
1265 CURSOR cItemSupplier IS
1266 SELECT VENDOR.VENDOR_NAME ITEM_SUPPLIER
1267 FROM PO_VENDORS VENDOR, OKL_ASSET_COMPONENTS_B ASSET_COMP
1268 WHERE VENDOR.VENDOR_ID = ASSET_COMP.SUPPLIER_ID
1269 AND ASSET_COMP.ASSET_ID = l_asset_id
1270 AND ASSET_COMP.PRIMARY_COMPONENT = 'NO';
1271
1272 ln_seq_number NUMBER :=1;
1273 BEGIN
1274 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
1275 x_resultout := FND_API.G_RET_STS_SUCCESS;
1276 BEGIN
1277 x_resultout := FND_API.G_RET_STS_SUCCESS;
1278
1279 FOR cItemSupplierRec IN cItemSupplier LOOP
1280 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_id :=
1281 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_id;
1282 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_parent_data_point_id :=
1283 l_asset_id;
1284 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_sequence_number := ln_seq_number;
1285 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_value :=
1286 cItemSupplierRec.item_supplier;
1287
1288 ln_seq_number := ln_seq_number + 1;
1289
1290 END LOOP;
1291 RETURN NULL;
1292
1293 EXCEPTION
1294 WHEN NO_DATA_FOUND THEN
1295 l_add_on_item_supplier := NULL;
1296 WHEN OTHERS THEN
1297 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
1298 x_errormsg := sqlerrm;
1299 END;
1300 END add_on_item_supplier ;
1301
1302 ---------------------------------------------
1303 -- FUNCTION add_on_item_model
1304 ---------------------------------------------
1305 FUNCTION add_on_item_model(x_resultout OUT NOCOPY VARCHAR2,
1306 x_errormsg OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
1307
1308 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
1309 l_asset_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_value_id);
1310
1311 l_add_on_item_model VARCHAR2(40);
1312
1313 CURSOR cItemModel IS
1314 SELECT ASSET_COMP.MODEL_NUMBER MODEL_NUMBER
1315 FROM OKL_ASSET_COMPONENTS_B ASSET_COMP
1316 WHERE ASSET_COMP.ASSET_ID = l_asset_id
1317 AND ASSET_COMP.PRIMARY_COMPONENT = 'NO';
1318
1319 ln_seq_number NUMBER :=1;
1320
1321 BEGIN
1322 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
1323 x_resultout := FND_API.G_RET_STS_SUCCESS;
1324 BEGIN
1325
1326 x_resultout := FND_API.G_RET_STS_SUCCESS;
1327
1328 FOR cItemModelRec IN cItemModel LOOP
1329 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_id :=
1330 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_id;
1331 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_parent_data_point_id := l_asset_id;
1332 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_sequence_number := ln_seq_number;
1333 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_value :=
1334 cItemModelRec.model_number;
1335
1336 ln_seq_number := ln_seq_number + 1;
1337
1338 END LOOP;
1339 RETURN NULL;
1340
1341 EXCEPTION
1342 WHEN NO_DATA_FOUND THEN
1343 l_add_on_item_model := NULL;
1344 WHEN OTHERS THEN
1345 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
1346 x_errormsg := sqlerrm;
1347 END;
1348 END add_on_item_model ;
1349
1350 ---------------------------------------------
1351 -- FUNCTION add_on_item_manufacturer
1352 ---------------------------------------------
1353 FUNCTION add_on_item_manufacturer(x_resultout OUT NOCOPY VARCHAR2,
1354 x_errormsg OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
1355
1356 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
1357 l_asset_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_value_id);
1358
1359 l_add_on_item_manufacturer VARCHAR2(30);
1360
1361 CURSOR cItemMfg IS
1362 SELECT ASSET_COMP.MANUFACTURER_NAME MANUFACTURER_NAME
1363 FROM OKL_ASSET_COMPONENTS_B ASSET_COMP
1364 WHERE ASSET_COMP.ASSET_ID = l_asset_id
1365 AND ASSET_COMP.PRIMARY_COMPONENT = 'NO';
1366
1367 ln_seq_number NUMBER :=1;
1368 BEGIN
1369 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
1370 x_resultout := FND_API.G_RET_STS_SUCCESS;
1371 BEGIN
1372
1373 x_resultout := FND_API.G_RET_STS_SUCCESS;
1374
1375 FOR cItemMfgRec IN cItemMfg LOOP
1376 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_id :=
1377 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_id;
1378 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_parent_data_point_id :=
1379 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_parent_data_point_id;
1380 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_sequence_number := ln_seq_number;
1381 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_value :=
1382 cItemMfgRec.manufacturer_name;
1383
1384 ln_seq_number := ln_seq_number + 1;
1385
1386 END LOOP;
1387 RETURN NULL;
1388
1389 EXCEPTION
1390 WHEN NO_DATA_FOUND THEN
1391 l_add_on_item_manufacturer := NULL;
1392 WHEN OTHERS THEN
1393 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
1394 x_errormsg := sqlerrm;
1395 END;
1396 END add_on_item_manufacturer ;
1397
1398 ---------------------------------------------
1399 -- FUNCTION add_on_item_amount
1400 ---------------------------------------------
1401 FUNCTION add_on_item_amount(x_resultout OUT NOCOPY VARCHAR2,
1402 x_errormsg OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
1403
1404 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
1405 l_asset_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_value_id);
1406
1407 l_add_on_item_amount NUMBER;
1408
1409 CURSOR cAddOnItemAmount IS
1410 SELECT ASSET_COMP.UNIT_COST UNIT_COST
1411 FROM OKL_ASSET_COMPONENTS_B ASSET_COMP
1412 WHERE ASSET_COMP.ASSET_ID = l_asset_id
1413 AND ASSET_COMP.PRIMARY_COMPONENT = 'NO';
1414
1415 ln_seq_number NUMBER :=1;
1416 BEGIN
1417 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
1418 x_resultout := FND_API.G_RET_STS_SUCCESS;
1419 BEGIN
1420
1421 x_resultout := FND_API.G_RET_STS_SUCCESS;
1422
1423 FOR cAddOnItemAmountRec IN cAddOnItemAmount LOOP
1424 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_id :=
1425 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_id;
1426 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_parent_data_point_id := l_asset_id;
1427 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_sequence_number := ln_seq_number;
1428 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_value :=
1429 cAddOnItemAmountRec.unit_cost;
1430
1431 ln_seq_number := ln_seq_number + 1;
1432
1433 END LOOP;
1434 RETURN NULL;
1435
1436 EXCEPTION
1437 WHEN NO_DATA_FOUND THEN
1438 l_add_on_item_amount := NULL;
1439 WHEN OTHERS THEN
1440 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
1441 x_errormsg := sqlerrm;
1442 END;
1443 END add_on_item_amount ;
1444
1445 ---------------------------------------------
1446 -- FUNCTION asset_residual_value
1447 ---------------------------------------------
1448 FUNCTION asset_residual_value(x_resultout OUT NOCOPY VARCHAR2,
1449 x_errormsg OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
1450
1451 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
1452 l_add_on_item_residual_values NUMBER;
1453
1454 CURSOR cItemResVal IS
1455 SELECT NVL(ASSET.END_OF_TERM_VALUE,ASSET.END_OF_TERM_VALUE_DEFAULT) END_OF_TERM_VALUE
1456 FROM OKL_LEASE_APPLICATIONS_B LAP, OKL_ASSETS_B ASSET, OKL_LEASE_QUOTES_B QUOTE
1457 WHERE ASSET.PARENT_OBJECT_ID = QUOTE.ID
1458 AND QUOTE.PARENT_OBJECT_ID = LAP.ID
1459 AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
1460 AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
1461 AND QUOTE.PRIMARY_QUOTE = 'Y'
1462 AND LAP.ID = l_lease_app_id;
1463
1464 ln_seq_number NUMBER :=1;
1465 BEGIN
1466 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
1467 x_resultout := FND_API.G_RET_STS_SUCCESS;
1468 BEGIN
1469
1470 x_resultout := FND_API.G_RET_STS_SUCCESS;
1471
1472 FOR cItemResValRec IN cItemResVal LOOP
1473 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_id :=
1474 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_id;
1475 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_parent_data_point_id :=
1476 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_parent_data_point_id;
1477 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_sequence_number := ln_seq_number;
1478 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_value :=
1479 cItemResValRec.END_OF_TERM_VALUE;
1480
1481 ln_seq_number := ln_seq_number + 1;
1482
1483 END LOOP;
1484 RETURN NULL;
1485
1486 EXCEPTION
1487 WHEN NO_DATA_FOUND THEN
1488 l_add_on_item_residual_values := NULL;
1489 WHEN OTHERS THEN
1490 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
1491 x_errormsg := sqlerrm;
1492 END;
1493 END asset_residual_value ;
1494
1495 ---------------------------------------------
1496 -- FUNCTION down_payment_amount
1497 ---------------------------------------------
1498 FUNCTION down_payment_amount(x_resultout OUT NOCOPY VARCHAR2,
1499 x_errormsg OUT NOCOPY VARCHAR2) RETURN NUMBER IS
1500
1501 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
1502 l_down_payment_amount NUMBER;
1503
1504 BEGIN
1505 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
1506 x_resultout := FND_API.G_RET_STS_SUCCESS;
1507 BEGIN
1508 SELECT NVL(SUM(ADJ.VALUE), 0)
1509 INTO l_down_payment_amount
1510 FROM OKL_LEASE_APPLICATIONS_B LAP, OKL_ASSETS_B ASSET,
1511 OKL_COST_ADJUSTMENTS_B ADJ, OKL_LEASE_QUOTES_B QUOTE
1512 WHERE ADJ.ADJUSTMENT_SOURCE_TYPE = 'DOWN_PAYMENT'
1513 AND ADJ.PARENT_OBJECT_CODE = 'ASSET'
1514 AND ADJ.PARENT_OBJECT_ID = ASSET.ID
1515 AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
1516 AND QUOTE.PARENT_OBJECT_ID = LAP.ID
1517 AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
1518 AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
1519 AND QUOTE.PRIMARY_QUOTE = 'Y'
1520 AND LAP.ID = l_lease_app_id;
1521
1522 EXCEPTION
1523 WHEN NO_DATA_FOUND THEN
1524 l_down_payment_amount := NULL;
1525 WHEN OTHERS THEN
1526 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
1527 x_errormsg := sqlerrm;
1528 END;
1529
1530 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_value :=
1531 l_down_payment_amount;
1532 RETURN l_down_payment_amount;
1533 END down_payment_amount ;
1534
1535 ---------------------------------------------
1536 -- FUNCTION subsidy_amount
1537 ---------------------------------------------
1538 FUNCTION subsidy_amount(x_resultout OUT NOCOPY VARCHAR2,
1539 x_errormsg OUT NOCOPY VARCHAR2) RETURN NUMBER IS
1540
1541 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
1542 l_subsidy_amount NUMBER;
1543
1544 BEGIN
1545 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
1546 x_resultout := FND_API.G_RET_STS_SUCCESS;
1547 BEGIN
1548 SELECT SUM(DECODE(ADJ.VALUE, NULL, ADJ.DEFAULT_SUBSIDY_AMOUNT, ADJ.VALUE)) SUBSIDY_AMOUNT
1549 INTO l_subsidy_amount
1550 FROM OKL_LEASE_APPLICATIONS_B LAP, OKL_ASSETS_B ASSET,
1551 OKL_COST_ADJUSTMENTS_B ADJ, OKL_LEASE_QUOTES_B QUOTE
1552 WHERE ADJ.ADJUSTMENT_SOURCE_TYPE = 'SUBSIDY'
1553 AND ADJ.PARENT_OBJECT_CODE = 'ASSET'
1554 AND ADJ.PARENT_OBJECT_ID = ASSET.ID
1555 AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
1556 AND QUOTE.PARENT_OBJECT_ID = LAP.ID
1557 AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
1558 AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
1559 AND QUOTE.PRIMARY_QUOTE = 'Y'
1560 AND LAP.ID = l_lease_app_id;
1561
1562 EXCEPTION
1563 WHEN NO_DATA_FOUND THEN
1564 l_subsidy_amount := NULL;
1565 WHEN OTHERS THEN
1566 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
1567 x_errormsg := sqlerrm;
1568 END;
1569
1570 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_value :=
1571 l_subsidy_amount ;
1572
1573 RETURN l_subsidy_amount;
1574 END subsidy_amount ;
1575
1576 ---------------------------------------------
1577 -- FUNCTION trade_in_amount
1578 ---------------------------------------------
1579 FUNCTION trade_in_amount(x_resultout OUT NOCOPY VARCHAR2,
1580 x_errormsg OUT NOCOPY VARCHAR2) RETURN NUMBER IS
1581
1582 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
1583 l_trade_in_amount NUMBER;
1584
1585 BEGIN
1586 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
1587 x_resultout := FND_API.G_RET_STS_SUCCESS;
1588 BEGIN
1589 SELECT NVL(SUM(ADJ.VALUE), 0)
1590 INTO l_trade_in_amount
1591 FROM OKL_LEASE_APPLICATIONS_B LAP, OKL_ASSETS_B ASSET,
1592 OKL_COST_ADJUSTMENTS_B ADJ, OKL_LEASE_QUOTES_B QUOTE
1593 WHERE ADJ.ADJUSTMENT_SOURCE_TYPE = 'TRADEIN'
1594 AND ADJ.PARENT_OBJECT_CODE = 'ASSET'
1595 AND ADJ.PARENT_OBJECT_ID = ASSET.ID
1596 AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
1597 AND QUOTE.PARENT_OBJECT_ID = LAP.ID
1598 AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
1599 AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
1600 AND QUOTE.PRIMARY_QUOTE = 'Y'
1601 AND LAP.ID = l_lease_app_id;
1602
1603 EXCEPTION
1604 WHEN NO_DATA_FOUND THEN
1605 l_trade_in_amount := NULL;
1606 WHEN OTHERS THEN
1607 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
1608 x_errormsg := sqlerrm;
1609 END;
1610
1611 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_value :=
1612 l_trade_in_amount;
1613
1614 RETURN l_trade_in_amount;
1615 END trade_in_amount ;
1616
1617 ---------------------------------------------
1618 -- FUNCTION trade_in_asset_number
1619 ---------------------------------------------
1620 FUNCTION trade_in_asset_number(x_resultout OUT NOCOPY VARCHAR2,
1621 x_errormsg OUT NOCOPY VARCHAR2) RETURN NUMBER IS
1622
1623 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
1624 l_trade_in_asset_number VARCHAR2(15);
1625
1626 CURSOR cAssetNumber IS
1627 SELECT ASSET.ASSET_NUMBER
1628 FROM OKL_LEASE_APPLICATIONS_B LAP, OKL_ASSETS_B ASSET,
1629 OKL_LEASE_QUOTES_B QUOTE, OKL_COST_ADJUSTMENTS_B ADJ
1630 WHERE ADJ.ADJUSTMENT_SOURCE_TYPE = 'TRADEIN'
1631 AND ADJ.PARENT_OBJECT_CODE = 'ASSET'
1632 AND ADJ.PARENT_OBJECT_ID = ASSET.ID
1633 AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
1634 AND QUOTE.PARENT_OBJECT_ID = LAP.ID
1635 AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
1636 AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
1637 AND QUOTE.PRIMARY_QUOTE = 'Y'
1638 AND LAP.ID = l_lease_app_id;
1639
1640 ln_seq_number NUMBER :=1;
1641 BEGIN
1642 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
1643 x_resultout := FND_API.G_RET_STS_SUCCESS;
1644 BEGIN
1645 x_resultout := FND_API.G_RET_STS_SUCCESS;
1646
1647 FOR cAssetNumberRec IN cAssetNumber
1648 LOOP
1649 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_id :=
1650 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_id;
1651 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_parent_data_point_id :=
1652 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_parent_data_point_id;
1653 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_sequence_number := ln_seq_number;
1654 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_value :=
1655 cAssetNumberRec.ASSET_NUMBER;
1656
1657 ln_seq_number := ln_seq_number + 1;
1658
1659 END LOOP;
1660 RETURN NULL;
1661
1662 EXCEPTION
1663 WHEN NO_DATA_FOUND THEN
1664 l_trade_in_asset_number := NULL;
1665 WHEN OTHERS THEN
1666 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
1667 x_errormsg := sqlerrm;
1668 END;
1669
1670 END trade_in_asset_number ;
1671
1672 ---------------------------------------------
1673 -- FUNCTION pmnt_frequency
1674 ---------------------------------------------
1675 FUNCTION pmnt_frequency(x_resultout OUT NOCOPY VARCHAR2,
1676 x_errormsg OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
1677
1678 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
1679 l_asset_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_value_id);
1680
1681 l_pmnt_frequency VARCHAR2(30);
1682
1683 cursor cPmntFreq is
1684 select FND.MEANING frequency
1685 from OKL_CASH_FLOW_OBJECTS CFO, OKL_CASH_FLOWS CFL,
1686 OKL_CASH_FLOW_LEVELS LVL, FND_LOOKUPS FND
1687 where CFO.OTY_CODE = 'QUOTED_ASSET'
1688 and CFO.ID = CFL.CFO_ID
1689 and CFL.ID = LVL.CAF_ID
1690 and CFO.SOURCE_ID = l_asset_id
1691 and CFO.SOURCE_TABLE = 'OKL_ASSETS_B'
1692 AND LVL.FQY_CODE = FND.LOOKUP_CODE
1693 AND FND.LOOKUP_TYPE = 'OKL_FREQUENCY';
1694
1695 /*UNION
1696 select OKL_CASH_FLOW_LEVELS.FQY_CODE FQY_CODE
1697 from okl_lease_applications_b LAP, OKL_FEES_B FEE, OKL_CASH_FLOW_OBJECTS,
1698 okl_cash_flows, OKL_CASH_FLOW_LEVELS, OKL_LEASE_QUOTES_B QUOTE
1699 where OKL_CASH_FLOW_objects.OTY_CODE = 'QUOTED_FEE'
1700 and OKL_CASH_FLOW_objects.id = okl_cash_flows.CFO_ID
1701 and okl_cash_flows.id = OKL_CASH_FLOW_LEVELS.CAF_ID
1702 and OKL_CASH_FLOW_OBJECTS.source_id = FEE.id
1703 and OKL_CASH_FLOW_OBJECTS.source_table = 'OKL_ASSETS_B'
1704 and FEE.parent_object_id = QUOTE.id
1705 and QUOTE.parent_object_id = LAP.id
1706 and FEE.parent_object_code = 'LEASEQUOTE'
1707 and QUOTE.parent_object_code = 'LEASEAPP'
1708 and QUOTE.primary_quote = 'Y'
1709 and LAP.id = l_lease_app_id
1710 UNION
1711 select OKL_CASH_FLOW_LEVELS.FQY_CODE FQY_CODE
1712 from okl_lease_applications_b LAP, OKL_SERVICES_B SRV, OKL_CASH_FLOW_OBJECTS,
1713 okl_cash_flows, OKL_CASH_FLOW_LEVELS, OKL_LEASE_QUOTES_B QUOTE
1714 where OKL_CASH_FLOW_objects.OTY_CODE = 'QUOTED_SERVICE'
1715 and OKL_CASH_FLOW_objects.id = okl_cash_flows.CFO_ID
1716 and okl_cash_flows.id = OKL_CASH_FLOW_LEVELS.CAF_ID
1717 and OKL_CASH_FLOW_OBJECTS.source_id = SRV.id
1718 and OKL_CASH_FLOW_OBJECTS.source_table = 'OKL_ASSETS_B'
1719 and SRV.parent_object_id = QUOTE.id
1720 and QUOTE.parent_object_id = LAP.id
1721 and SRV.parent_object_code = 'LEASEQUOTE'
1722 and QUOTE.parent_object_code = 'LEASEAPP'
1723 and QUOTE.primary_quote = 'Y'
1724 and LAP.id = l_lease_app_id
1725 UNION
1726 select OKL_CASH_FLOW_LEVELS.FQY_CODE FQY_CODE
1727 from okl_lease_applications_b LAP, OKL_INSURANCE_ESTIMATES_B INS, OKL_CASH_FLOW_OBJECTS,
1728 okl_cash_flows, OKL_CASH_FLOW_LEVELS, OKL_LEASE_QUOTES_B QUOTE
1729 where OKL_CASH_FLOW_objects.OTY_CODE = 'QUOTED_INSURANCE'
1730 and OKL_CASH_FLOW_objects.id = okl_cash_flows.CFO_ID
1731 and okl_cash_flows.id = OKL_CASH_FLOW_LEVELS.CAF_ID
1732 and OKL_CASH_FLOW_OBJECTS.source_id = INS.id
1733 and OKL_CASH_FLOW_OBJECTS.source_table = 'OKL_INSURANCE_ESTIMATES_B'
1734 and INS.LEASE_QUOTE_ID = QUOTE.id
1735 and QUOTE.parent_object_id = LAP.id
1736 and QUOTE.parent_object_code = 'LEASEAPP'
1737 and QUOTE.primary_quote = 'Y'
1738 and LAP.id = l_lease_app_id;*/
1739
1740
1741 ln_seq_number NUMBER :=1;
1742 BEGIN
1743 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
1744 x_resultout := FND_API.G_RET_STS_SUCCESS;
1745 BEGIN
1746 x_resultout := FND_API.G_RET_STS_SUCCESS;
1747
1748 FOR cPmntFreqRec IN cPmntFreq
1749 LOOP
1750 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_id :=
1751 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_id;
1752 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_parent_data_point_id := l_asset_id;
1753 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_sequence_number := ln_seq_number;
1754 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_value :=
1755 cPmntFreqRec.frequency;
1756
1757 ln_seq_number := ln_seq_number + 1;
1758
1759 END LOOP;
1760 RETURN NULL;
1761
1762 EXCEPTION
1763 WHEN NO_DATA_FOUND THEN
1764 l_pmnt_frequency := NULL;
1765 WHEN OTHERS THEN
1766 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
1767 x_errormsg := sqlerrm;
1768 END;
1769 END pmnt_frequency ;
1770
1771 ---------------------------------------------
1772 -- FUNCTION pmnt_arrears_yn
1773 ---------------------------------------------
1774 FUNCTION pmnt_arrears_yn(x_resultout OUT NOCOPY VARCHAR2,
1775 x_errormsg OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
1776
1777 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
1778 l_asset_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_value_id);
1779
1780 l_pmnt_arrears_yn VARCHAR2(3);
1781
1782 cursor cPmntArrYN is
1783 select FND.MEANING Arrears
1784 from OKL_CASH_FLOW_OBJECTS CFO, OKL_CASH_FLOWS CFL, FND_LOOKUPS FND
1785 where CFO.OTY_CODE = 'QUOTED_ASSET'
1786 and CFO.id = CFL.CFO_ID
1787 --Bug 7030452 :Hardcoded value '23' is removed
1788 -- and CFO.source_id = 23
1789 and CFO.source_id = l_asset_id
1790 --Bug 7030452 :End
1791 and CFO.source_table = 'OKL_ASSETS_B'
1792 AND CFL.due_arrears_yn = FND.LOOKUP_CODE
1793 AND FND.LOOKUP_TYPE = 'OKL_YES_NO';
1794
1795 /*union
1796 select OKL_CASH_FLOWS.due_arrears_yn due_arrears_yn
1797 from okl_lease_applications_b LAP, OKL_FEES_B FEE, OKL_CASH_FLOW_OBJECTS,
1798 okl_cash_flows, OKL_LEASE_QUOTES_B QUOTE
1799 where OKL_CASH_FLOW_objects.OTY_CODE = 'QUOTED_FEE'
1800 and OKL_CASH_FLOW_objects.id = okl_cash_flows.CFO_ID
1801 and OKL_CASH_FLOW_OBJECTS.source_id = FEE.id
1802 and OKL_CASH_FLOW_OBJECTS.source_table = 'OKL_FEES_B'
1803 and FEE.parent_object_id = QUOTE.id
1804 and QUOTE.parent_object_id = LAP.id
1805 and FEE.parent_object_code = 'LEASEQUOTE'
1806 and QUOTE.parent_object_code = 'LEASEAPP'
1807 and QUOTE.primary_quote = 'Y'
1808 and LAP.id = l_lease_app_id
1809 union
1810 select OKL_CASH_FLOWS.due_arrears_yn due_arrears_yn
1811 from okl_lease_applications_b LAP, OKL_SERVICES_B SRV, OKL_CASH_FLOW_OBJECTS,
1812 okl_cash_flows, OKL_LEASE_QUOTES_B QUOTE
1813 where OKL_CASH_FLOW_objects.OTY_CODE = 'QUOTED_SERVICE'
1814 and OKL_CASH_FLOW_objects.id = okl_cash_flows.CFO_ID
1815 and OKL_CASH_FLOW_OBJECTS.source_id = SRV.id
1816 and OKL_CASH_FLOW_OBJECTS.source_table = 'OKL_SERVICES_B'
1817 and SRV.parent_object_id = QUOTE.id
1818 and QUOTE.parent_object_id = LAP.id
1819 and SRV.parent_object_code = 'LEASEQUOTE'
1820 and QUOTE.parent_object_code = 'LEASEAPP'
1821 and QUOTE.primary_quote = 'Y'
1822 and LAP.id = l_lease_app_id
1823 union
1824 select OKL_CASH_FLOWS.due_arrears_yn due_arrears_yn
1825 from okl_lease_applications_b LAP, OKL_INSURANCE_ESTIMATES_B INS, OKL_CASH_FLOW_OBJECTS,
1826 okl_cash_flows, OKL_LEASE_QUOTES_B QUOTE
1827 where OKL_CASH_FLOW_objects.OTY_CODE = 'QUOTED_INSURANCE'
1828 and OKL_CASH_FLOW_objects.id = okl_cash_flows.CFO_ID
1829 and OKL_CASH_FLOW_OBJECTS.source_id = INS.id
1830 and OKL_CASH_FLOW_OBJECTS.source_table = 'OKL_INSURANCE_ESTIMATES_B'
1831 and INS.LEASE_QUOTE_ID = QUOTE.id
1832 and QUOTE.parent_object_id = LAP.id
1833 and QUOTE.parent_object_code = 'LEASEAPP'
1834 and QUOTE.primary_quote = 'Y'
1835 and LAP.id = l_lease_app_id;*/
1836
1837 ln_seq_number NUMBER :=1;
1838 BEGIN
1839 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
1840 x_resultout := FND_API.G_RET_STS_SUCCESS;
1841 BEGIN
1842
1843 x_resultout := FND_API.G_RET_STS_SUCCESS;
1844
1845 FOR cPmntArrYNRec IN cPmntArrYN
1846 LOOP
1847 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_id :=
1848 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_id;
1849 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_parent_data_point_id := l_asset_id;
1850 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_sequence_number := ln_seq_number;
1851 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_value :=
1852 cPmntArrYNRec.Arrears;
1853
1854 ln_seq_number := ln_seq_number + 1;
1855
1856 END LOOP;
1857 RETURN NULL;
1858
1859 EXCEPTION
1860 WHEN NO_DATA_FOUND THEN
1861 l_pmnt_arrears_yn := NULL;
1862 WHEN OTHERS THEN
1863 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
1864 x_errormsg := sqlerrm;
1865 END;
1866 END pmnt_arrears_yn ;
1867
1868
1869 ---------------------------------------------
1870 -- FUNCTION pmnt_periods
1871 ---------------------------------------------
1872 FUNCTION pmnt_periods(x_resultout OUT NOCOPY VARCHAR2,
1873 x_errormsg OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
1874
1875 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
1876 l_asset_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_value_id);
1877
1878 l_pmnt_periods NUMBER;
1879
1880 cursor cPmntPeriods is
1881 select LVL.NUMBER_OF_PERIODS
1882 from OKL_CASH_FLOW_OBJECTS CFO, OKL_CASH_FLOWS CFL, OKL_CASH_FLOW_LEVELS LVL
1883 where CFO.OTY_CODE = 'QUOTED_ASSET'
1884 and CFO.ID = CFL.CFO_ID
1885 and CFL.ID = LVL.CAF_ID
1886 and CFO.SOURCE_ID = l_asset_id
1887 and CFO.SOURCE_TABLE = 'OKL_ASSETS_B';
1888
1889 /*union
1890 select OKL_CASH_FLOW_LEVELS.number_of_periods number_of_periods
1891 from okl_lease_applications_b LAP, OKL_FEES_B FEE, OKL_CASH_FLOW_OBJECTS,
1892 okl_cash_flows, OKL_CASH_FLOW_LEVELS, OKL_LEASE_QUOTES_B QUOTE
1893 where OKL_CASH_FLOW_objects.OTY_CODE = 'QUOTED_FEE'
1894 and OKL_CASH_FLOW_objects.id = okl_cash_flows.CFO_ID
1895 and okl_cash_flows.id = OKL_CASH_FLOW_LEVELS.CAF_ID
1896 and OKL_CASH_FLOW_OBJECTS.source_id = FEE.id
1897 and OKL_CASH_FLOW_OBJECTS.source_table = 'OKL_FEES_B'
1898 and FEE.parent_object_id = QUOTE.id
1899 and QUOTE.parent_object_id = LAP.id
1900 and FEE.parent_object_code = 'LEASEQUOTE'
1901 and QUOTE.parent_object_code = 'LEASEAPP'
1902 and QUOTE.primary_quote = 'Y'
1903 and LAP.id = l_lease_app_id
1904 union
1905 select OKL_CASH_FLOW_LEVELS.number_of_periods number_of_periods
1906 from okl_lease_applications_b LAP, OKL_SERVICES_B SRV, OKL_CASH_FLOW_OBJECTS,
1907 okl_cash_flows, OKL_CASH_FLOW_LEVELS, OKL_LEASE_QUOTES_B QUOTE
1908 where OKL_CASH_FLOW_objects.OTY_CODE = 'QUOTED_SERVICE'
1909 and OKL_CASH_FLOW_objects.id = okl_cash_flows.CFO_ID
1910 and okl_cash_flows.id = OKL_CASH_FLOW_LEVELS.CAF_ID
1911 and OKL_CASH_FLOW_OBJECTS.source_id = SRV.id
1912 and OKL_CASH_FLOW_OBJECTS.source_table = 'OKL_SERVICES_B'
1913 and SRV.parent_object_id = QUOTE.id
1914 and QUOTE.parent_object_id = LAP.id
1915 and SRV.parent_object_code = 'LEASEQUOTE'
1916 and QUOTE.parent_object_code = 'LEASEAPP'
1917 and QUOTE.primary_quote = 'Y'
1918 and LAP.id = l_lease_app_id
1919 union
1920 select OKL_CASH_FLOW_LEVELS.number_of_periods number_of_periods
1921 from okl_lease_applications_b LAP, OKL_INSURANCE_ESTIMATES_B INS, OKL_CASH_FLOW_OBJECTS,
1922 okl_cash_flows, OKL_CASH_FLOW_LEVELS, OKL_LEASE_QUOTES_B QUOTE
1923 where OKL_CASH_FLOW_objects.OTY_CODE = 'QUOTED_INSURANCE'
1924 and OKL_CASH_FLOW_objects.id = okl_cash_flows.CFO_ID
1925 and okl_cash_flows.id = OKL_CASH_FLOW_LEVELS.CAF_ID
1926 and OKL_CASH_FLOW_OBJECTS.source_id = INS.id
1927 and OKL_CASH_FLOW_OBJECTS.source_table = 'OKL_INSURANCE_ESTIMATES_B'
1928 and INS.LEASE_QUOTE_ID = QUOTE.id
1929 and QUOTE.parent_object_id = LAP.id
1930 and QUOTE.parent_object_code = 'LEASEAPP'
1931 and QUOTE.primary_quote = 'Y'
1932 and LAP.id = l_lease_app_id;*/
1933
1934 ln_seq_number NUMBER :=1;
1935 BEGIN
1936 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
1937 x_resultout := FND_API.G_RET_STS_SUCCESS;
1938 BEGIN
1939 x_resultout := FND_API.G_RET_STS_SUCCESS;
1940
1941 FOR cPmntPeriodsRec IN cPmntPeriods
1942 LOOP
1943 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_id :=
1944 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_id;
1945 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_parent_data_point_id := l_asset_id;
1946 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_sequence_number := ln_seq_number;
1947 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_value :=
1948 cPmntPeriodsRec.number_of_periods;
1949
1950 ln_seq_number := ln_seq_number + 1;
1951
1952 END LOOP;
1953 RETURN NULL;
1954
1955 EXCEPTION
1956 WHEN NO_DATA_FOUND THEN
1957 l_pmnt_periods := NULL;
1958 WHEN OTHERS THEN
1959 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
1960 x_errormsg := sqlerrm;
1961 END;
1962 END pmnt_periods ;
1963
1964
1965 ---------------------------------------------
1966 -- FUNCTION pmnt_amounts
1967 ---------------------------------------------
1968 FUNCTION pmnt_amounts(x_resultout OUT NOCOPY VARCHAR2,
1969 x_errormsg OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
1970
1971 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
1972 l_asset_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_value_id);
1973
1974 l_pmnt_amounts NUMBER;
1975
1976 cursor cPmntAmount is
1977 select LVL.AMOUNT
1978 from OKL_CASH_FLOW_OBJECTS CFO, OKL_CASH_FLOWS CFL, OKL_CASH_FLOW_LEVELS LVL
1979 where CFO.OTY_CODE = 'QUOTED_ASSET'
1980 and CFO.ID = CFL.CFO_ID
1981 and CFL.ID = LVL.CAF_ID
1982 and CFO.SOURCE_ID = l_asset_id
1983 and CFO.SOURCE_TABLE = 'OKL_ASSETS_B';
1984
1985 /*union
1986 select OKL_CASH_FLOW_LEVELS.amount amount
1987 from okl_lease_applications_b LAP, OKL_FEES_B FEE, OKL_CASH_FLOW_OBJECTS,
1988 okl_cash_flows, OKL_CASH_FLOW_LEVELS, OKL_LEASE_QUOTES_B QUOTE
1989 where OKL_CASH_FLOW_objects.OTY_CODE = 'QUOTED_FEE'
1990 and OKL_CASH_FLOW_objects.id = okl_cash_flows.CFO_ID
1991 and okl_cash_flows.id = OKL_CASH_FLOW_LEVELS.CAF_ID
1992 and OKL_CASH_FLOW_OBJECTS.source_id = FEE.id
1993 and OKL_CASH_FLOW_OBJECTS.source_table = 'OKL_FEES_B'
1994 and FEE.parent_object_id = QUOTE.id
1995 and QUOTE.parent_object_id = LAP.id
1996 and FEE.parent_object_code = 'LEASEQUOTE'
1997 and QUOTE.parent_object_code = 'LEASEAPP'
1998 and QUOTE.primary_quote = 'Y'
1999 and LAP.id = l_lease_app_id
2000 union
2001 select OKL_CASH_FLOW_LEVELS.amount amount
2002 from okl_lease_applications_b LAP, OKL_SERVICES_B SRV, OKL_CASH_FLOW_OBJECTS,
2003 okl_cash_flows, OKL_CASH_FLOW_LEVELS, OKL_LEASE_QUOTES_B QUOTE
2004 where OKL_CASH_FLOW_objects.OTY_CODE = 'QUOTED_SERVICE'
2005 and OKL_CASH_FLOW_objects.id = okl_cash_flows.CFO_ID
2006 and okl_cash_flows.id = OKL_CASH_FLOW_LEVELS.CAF_ID
2007 and OKL_CASH_FLOW_OBJECTS.source_id = SRV.id
2008 and OKL_CASH_FLOW_OBJECTS.source_table = 'OKL_SERVICES_B'
2009 and SRV.parent_object_id = QUOTE.id
2010 and QUOTE.parent_object_id = LAP.id
2011 and SRV.parent_object_code = 'LEASEQUOTE'
2012 and QUOTE.parent_object_code = 'LEASEAPP'
2013 and QUOTE.primary_quote = 'Y'
2014 and LAP.id = l_lease_app_id
2015 union
2016 select OKL_CASH_FLOW_LEVELS.amount amount
2017 from okl_lease_applications_b LAP, OKL_INSURANCE_ESTIMATES_B INS, OKL_CASH_FLOW_OBJECTS,
2018 okl_cash_flows, OKL_CASH_FLOW_LEVELS, OKL_LEASE_QUOTES_B QUOTE
2019 where OKL_CASH_FLOW_objects.OTY_CODE = 'QUOTED_INSURANCE'
2020 and OKL_CASH_FLOW_objects.id = okl_cash_flows.CFO_ID
2021 and okl_cash_flows.id = OKL_CASH_FLOW_LEVELS.CAF_ID
2022 and OKL_CASH_FLOW_OBJECTS.source_id = INS.id
2023 and OKL_CASH_FLOW_OBJECTS.source_table = 'OKL_INSURANCE_ESTIMATES_B'
2024 and INS.LEASE_QUOTE_ID = QUOTE.id
2025 and QUOTE.parent_object_id = LAP.id
2026 and QUOTE.parent_object_code = 'LEASEAPP'
2027 and QUOTE.primary_quote = 'Y'
2028 and LAP.id = l_lease_app_id; */
2029
2030 ln_seq_number NUMBER :=1;
2031 BEGIN
2032 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
2033 x_resultout := FND_API.G_RET_STS_SUCCESS;
2034 BEGIN
2035 x_resultout := FND_API.G_RET_STS_SUCCESS;
2036
2037 FOR cPmntAmountRec IN cPmntAmount
2038 LOOP
2039 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_id :=
2040 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_id;
2041 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_parent_data_point_id := l_asset_id;
2042 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_sequence_number := ln_seq_number;
2043 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_value :=
2044 cPmntAmountRec.amount;
2045
2046 ln_seq_number := ln_seq_number + 1;
2047
2048 END LOOP;
2049 RETURN NULL;
2050
2051
2052 EXCEPTION
2053 WHEN NO_DATA_FOUND THEN
2054 l_pmnt_amounts := NULL;
2055 WHEN OTHERS THEN
2056 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
2057 x_errormsg := sqlerrm;
2058 END;
2059 END pmnt_amounts ;
2060
2061 ---------------------------------------------
2062 -- FUNCTION pmnt_start_date
2063 ---------------------------------------------
2064 FUNCTION pmnt_start_date(x_resultout OUT NOCOPY VARCHAR2,
2065 x_errormsg OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
2066
2067 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
2068 l_asset_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_value_id);
2069
2070 l_pmnt_start_date DATE;
2071
2072 cursor cPmntStartDate is
2073 select LVL.start_date
2074 from OKL_CASH_FLOW_OBJECTS CFO, OKL_CASH_FLOWS CFL, OKL_CASH_FLOW_LEVELS LVL
2075 where CFO.OTY_CODE = 'QUOTED_ASSET'
2076 and CFO.ID = CFL.CFO_ID
2077 and CFL.ID = LVL.CAF_ID
2078 and CFO.SOURCE_ID = l_asset_id
2079 and CFO.SOURCE_TABLE = 'OKL_ASSETS_B';
2080
2081 /*union
2082 select OKL_CASH_FLOW_LEVELS.start_date start_date
2083 from okl_lease_applications_b LAP, OKL_FEES_B FEE, OKL_CASH_FLOW_OBJECTS,
2084 okl_cash_flows, OKL_CASH_FLOW_LEVELS, OKL_LEASE_QUOTES_B QUOTE
2085 where OKL_CASH_FLOW_objects.OTY_CODE = 'QUOTED_FEE'
2086 and OKL_CASH_FLOW_objects.id = okl_cash_flows.CFO_ID
2087 and okl_cash_flows.id = OKL_CASH_FLOW_LEVELS.CAF_ID
2088 and OKL_CASH_FLOW_OBJECTS.source_id = FEE.id
2089 and OKL_CASH_FLOW_OBJECTS.source_table = 'OKL_FEES_B'
2090 and FEE.parent_object_id = QUOTE.id
2091 and QUOTE.parent_object_id = LAP.id
2092 and FEE.parent_object_code = 'LEASEQUOTE'
2093 and QUOTE.parent_object_code = 'LEASEAPP'
2094 and QUOTE.primary_quote = 'Y'
2095 and LAP.id = l_lease_app_id
2096 union
2097 select OKL_CASH_FLOW_LEVELS.start_date start_date
2098 from okl_lease_applications_b LAP, OKL_SERVICES_B SRV, OKL_CASH_FLOW_OBJECTS,
2099 okl_cash_flows, OKL_CASH_FLOW_LEVELS, OKL_LEASE_QUOTES_B QUOTE
2100 where OKL_CASH_FLOW_objects.OTY_CODE = 'QUOTED_SERVICE'
2101 and OKL_CASH_FLOW_objects.id = okl_cash_flows.CFO_ID
2102 and okl_cash_flows.id = OKL_CASH_FLOW_LEVELS.CAF_ID
2103 and OKL_CASH_FLOW_OBJECTS.source_id = SRV.id
2104 and OKL_CASH_FLOW_OBJECTS.source_table = 'OKL_SERVICES_B'
2105 and SRV.parent_object_id = QUOTE.id
2106 and QUOTE.parent_object_id = LAP.id
2107 and SRV.parent_object_code = 'LEASEQUOTE'
2108 and QUOTE.parent_object_code = 'LEASEAPP'
2109 and QUOTE.primary_quote = 'Y'
2110 and LAP.id = l_lease_app_id
2111 union
2112 select OKL_CASH_FLOW_LEVELS.start_date start_date
2113 from okl_lease_applications_b LAP, OKL_INSURANCE_ESTIMATES_B INS, OKL_CASH_FLOW_OBJECTS,
2114 okl_cash_flows, OKL_CASH_FLOW_LEVELS, OKL_LEASE_QUOTES_B QUOTE
2115 where OKL_CASH_FLOW_objects.OTY_CODE = 'QUOTED_INSURANCE'
2116 and OKL_CASH_FLOW_objects.id = okl_cash_flows.CFO_ID
2117 and okl_cash_flows.id = OKL_CASH_FLOW_LEVELS.CAF_ID
2118 and OKL_CASH_FLOW_OBJECTS.source_id = INS.id
2119 and OKL_CASH_FLOW_OBJECTS.source_table = 'OKL_INSURANCE_ESTIMATES_B'
2120 and INS.LEASE_QUOTE_ID = QUOTE.id
2121 and QUOTE.parent_object_id = LAP.id
2122 and QUOTE.parent_object_code = 'LEASEAPP'
2123 and QUOTE.primary_quote = 'Y'
2124 and LAP.id = l_lease_app_id;*/
2125
2126 ln_seq_number NUMBER :=1;
2127 BEGIN
2128 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
2129 x_resultout := FND_API.G_RET_STS_SUCCESS;
2130 BEGIN
2131
2132 x_resultout := FND_API.G_RET_STS_SUCCESS;
2133
2134 FOR cPmntStartDateRec IN cPmntStartDate
2135 LOOP
2136 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_id :=
2137 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_id;
2138 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_parent_data_point_id := l_asset_id;
2139 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_sequence_number := ln_seq_number;
2140 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_value :=
2141 cPmntStartDateRec.start_date;
2142
2143 ln_seq_number := ln_seq_number + 1;
2144
2145 END LOOP;
2146 RETURN NULL;
2147
2148 EXCEPTION
2149 WHEN NO_DATA_FOUND THEN
2150 l_pmnt_start_date := NULL;
2151 WHEN OTHERS THEN
2152 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
2153 x_errormsg := sqlerrm;
2154 END;
2155 END pmnt_start_date ;
2156
2157 ---------------------------------------------
2158 -- FUNCTION payment_structure
2159 ---------------------------------------------
2160 FUNCTION payment_structure(x_resultout OUT NOCOPY VARCHAR2,
2161 x_errormsg OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
2162
2163 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
2164 l_asset_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_value_id);
2165
2166 l_payment_structure VARCHAR2(5) := 'LEVEL';
2167
2168 ln_seq_number NUMBER :=1;
2169 BEGIN
2170 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
2171 x_resultout := FND_API.G_RET_STS_SUCCESS;
2172 BEGIN
2173
2174 x_resultout := FND_API.G_RET_STS_SUCCESS;
2175
2176 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_id :=
2177 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_id;
2178 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_parent_data_point_id := l_asset_id;
2179 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_sequence_number := ln_seq_number;
2180 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_value :=
2181 l_payment_structure;
2182
2183
2184 RETURN NULL;
2185
2186 EXCEPTION
2187 WHEN NO_DATA_FOUND THEN
2188 l_payment_structure := NULL;
2189 WHEN OTHERS THEN
2190 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
2191 x_errormsg := sqlerrm;
2192 END;
2193 END payment_structure ;
2194
2195 -------------------------------------
2196 -- PROCEDURE calculate_level_end_date
2197 -------------------------------------
2198 PROCEDURE calculate_level_end_date (
2199 p_level_start_date IN DATE
2200 ,p_contract_term IN NUMBER
2201 ,p_frequency_code IN VARCHAR2
2202 ,p_cashflow_level_tbl IN OUT NOCOPY OKL_LEASE_QUOTE_CASHFLOW_PVT.cashflow_level_tbl_type
2203 ,x_return_status OUT NOCOPY VARCHAR2) IS
2204
2205 l_program_name CONSTANT VARCHAR2(30) := 'calculate_level_end_date';
2206 l_api_name CONSTANT VARCHAR2(61) := G_APP_NAME||'.'||l_program_name;
2207
2208 l_mpp PLS_INTEGER;
2209
2210 l_contract_end_date DATE;
2211 l_next_start_date DATE;
2212 l_end_date DATE;
2213
2214 BEGIN
2215 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
2216 IF p_frequency_code = 'A' THEN
2217 l_mpp := 12;
2218 ELSIF p_frequency_code = 'S' THEN
2219 l_mpp := 6;
2220 ELSIF p_frequency_code = 'Q' THEN
2221 l_mpp := 3;
2222 ELSIF p_frequency_code = 'M' THEN
2223 l_mpp := 1;
2224 END IF;
2225
2226 l_next_start_date := p_level_start_date;
2227
2228 FOR i IN p_cashflow_level_tbl.FIRST .. p_cashflow_level_tbl.LAST LOOP
2229 IF p_cashflow_level_tbl.EXISTS(i) THEN
2230
2231 IF p_cashflow_level_tbl(i).stub_days IS NOT NULL THEN
2232 l_end_date := l_next_start_date + p_cashflow_level_tbl(i).stub_days - 1;
2233 ELSE
2234 l_end_date := ADD_MONTHS(l_next_start_date, l_mpp*p_cashflow_level_tbl(i).periods) - 1;
2235 END IF;
2236
2237 p_cashflow_level_tbl(i).start_date := l_end_date;
2238 l_next_start_date := l_end_date + 1;
2239
2240 END IF;
2241 END LOOP;
2242
2243 l_contract_end_date := ADD_MONTHS(p_level_start_date, p_contract_term) - 1;
2244
2245 IF l_end_date > l_contract_end_date THEN
2246 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME, p_msg_name => 'OKL_LEVEL_EXTENDS_K_END');
2247 RAISE OKL_API.G_EXCEPTION_ERROR;
2248 END IF;
2249
2250 x_return_status := G_RET_STS_SUCCESS;
2251
2252 EXCEPTION
2253
2254 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2255 x_return_status := G_RET_STS_ERROR;
2256
2257 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2258 x_return_status := G_RET_STS_UNEXP_ERROR;
2259
2260 WHEN OTHERS THEN
2261 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
2262 p_msg_name => G_DB_ERROR,
2263 p_token1 => G_PROG_NAME_TOKEN,
2264 p_token1_value => l_api_name,
2265 p_token2 => G_SQLCODE_TOKEN,
2266 p_token2_value => sqlcode,
2267 p_token3 => G_SQLERRM_TOKEN,
2268 p_token3_value => sqlerrm);
2269
2270 x_return_status := G_RET_STS_UNEXP_ERROR;
2271
2272 END calculate_level_end_date;
2273
2274 ---------------------------------------------
2275 -- FUNCTION pmnt_end_date
2276 ---------------------------------------------
2277 FUNCTION pmnt_end_date(x_resultout OUT NOCOPY VARCHAR2,
2278 x_errormsg OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
2279
2280 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
2281 l_asset_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_value_id);
2282
2283 l_pmnt_end_date DATE;
2284
2285 cursor c_level_data is
2286 select LVL.stub_days, LVL.number_of_periods, LVL.start_date
2287 from OKL_CASH_FLOW_OBJECTS CFO, OKL_CASH_FLOWS CFL, OKL_CASH_FLOW_LEVELS LVL
2288 where CFO.OTY_CODE = 'QUOTED_ASSET'
2289 and CFO.ID = CFL.CFO_ID
2290 and CFL.ID = LVL.CAF_ID
2291 and CFO.SOURCE_ID = l_asset_id
2292 and CFO.SOURCE_TABLE = 'OKL_ASSETS_B';
2293
2294 /*union
2295 select OKL_CASH_FLOW_LEVELS.start_date end_date
2296 from okl_lease_applications_b LAP, OKL_FEES_B FEE, OKL_CASH_FLOW_OBJECTS,
2297 okl_cash_flows, OKL_CASH_FLOW_LEVELS, OKL_LEASE_QUOTES_B QUOTE
2298 where OKL_CASH_FLOW_objects.OTY_CODE = 'QUOTED_FEE'
2299 and OKL_CASH_FLOW_objects.id = okl_cash_flows.CFO_ID
2300 and okl_cash_flows.id = OKL_CASH_FLOW_LEVELS.CAF_ID
2301 and OKL_CASH_FLOW_OBJECTS.source_id = FEE.id
2302 and OKL_CASH_FLOW_OBJECTS.source_table = 'OKL_FEES_B'
2303 and FEE.parent_object_id = QUOTE.id
2304 and QUOTE.parent_object_id = LAP.id
2305 and FEE.parent_object_code = 'LEASEQUOTE'
2306 and QUOTE.parent_object_code = 'LEASEAPP'
2307 and QUOTE.primary_quote = 'Y'
2308 and LAP.id = l_lease_app_id
2309 union
2310 select OKL_CASH_FLOW_LEVELS.start_date end_date
2311 from okl_lease_applications_b LAP, OKL_SERVICES_B SRV, OKL_CASH_FLOW_OBJECTS,
2312 okl_cash_flows, OKL_CASH_FLOW_LEVELS, OKL_LEASE_QUOTES_B QUOTE
2313 where OKL_CASH_FLOW_objects.OTY_CODE = 'QUOTED_SERVICE'
2314 and OKL_CASH_FLOW_objects.id = okl_cash_flows.CFO_ID
2315 and okl_cash_flows.id = OKL_CASH_FLOW_LEVELS.CAF_ID
2316 and OKL_CASH_FLOW_OBJECTS.source_id = SRV.id
2317 and OKL_CASH_FLOW_OBJECTS.source_table = 'OKL_SERVICES_B'
2318 and SRV.parent_object_id = QUOTE.id
2319 and QUOTE.parent_object_id = LAP.id
2320 and SRV.parent_object_code = 'LEASEQUOTE'
2321 and QUOTE.parent_object_code = 'LEASEAPP'
2322 and QUOTE.primary_quote = 'Y'
2323 and LAP.id = l_lease_app_id
2324 union
2325 select OKL_CASH_FLOW_LEVELS.start_date end_date
2326 from okl_lease_applications_b LAP, OKL_INSURANCE_ESTIMATES_B INS, OKL_CASH_FLOW_OBJECTS,
2327 okl_cash_flows, OKL_CASH_FLOW_LEVELS, OKL_LEASE_QUOTES_B QUOTE
2328 where OKL_CASH_FLOW_objects.OTY_CODE = 'QUOTED_INSURANCE'
2329 and OKL_CASH_FLOW_objects.id = okl_cash_flows.CFO_ID
2330 and okl_cash_flows.id = OKL_CASH_FLOW_LEVELS.CAF_ID
2331 and OKL_CASH_FLOW_OBJECTS.source_id = INS.id
2332 and OKL_CASH_FLOW_OBJECTS.source_table = 'OKL_INSURANCE_ESTIMATES_B'
2333 and INS.LEASE_QUOTE_ID = QUOTE.id
2334 and QUOTE.parent_object_id = LAP.id
2335 and QUOTE.parent_object_code = 'LEASEAPP'
2336 and QUOTE.primary_quote = 'Y'
2337 and LAP.id = l_lease_app_id;*/
2338
2339 CURSOR c_get_info IS
2340 SELECT OKL_CASH_FLOW_LEVELS.FQY_CODE FQY_CODE, QUOTE.TERM
2341 FROM OKL_LEASE_APPLICATIONS_B LAP, OKL_ASSETS_B ASSET, OKL_CASH_FLOW_OBJECTS,
2342 OKL_CASH_FLOWS, OKL_CASH_FLOW_LEVELS, OKL_LEASE_QUOTES_B QUOTE
2343 WHERE OKL_CASH_FLOW_OBJECTS.OTY_CODE = 'QUOTED_ASSET'
2344 AND OKL_CASH_FLOW_OBJECTS.ID = OKL_CASH_FLOWS.CFO_ID
2345 AND OKL_CASH_FLOWS.ID = OKL_CASH_FLOW_LEVELS.CAF_ID
2346 AND OKL_CASH_FLOW_OBJECTS.SOURCE_ID = ASSET.ID
2347 AND OKL_CASH_FLOW_OBJECTS.SOURCE_TABLE = 'OKL_ASSETS_B'
2348 AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
2349 AND QUOTE.PARENT_OBJECT_ID = LAP.ID
2350 AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
2351 AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
2352 AND QUOTE.PRIMARY_QUOTE = 'Y'
2353 AND LAP.ID = l_lease_app_id
2354 AND ASSET.ID = l_asset_id
2355 AND ROWNUM = 1;
2356
2357 ln_seq_number NUMBER :=1;
2358 i BINARY_INTEGER := 0;
2359
2360 cf_level_tbl OKL_LEASE_QUOTE_CASHFLOW_PVT.cashflow_level_tbl_type;
2361
2362 l_freq_code OKL_CASH_FLOW_LEVELS.FQY_CODE%TYPE;
2363 l_term NUMBER;
2364 l_return_status VARCHAR2(1);
2365 BEGIN
2366 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
2367 x_resultout := FND_API.G_RET_STS_SUCCESS;
2368 BEGIN
2369 x_resultout := FND_API.G_RET_STS_SUCCESS;
2370
2371 -- Fetch the frequency code, term
2372 OPEN c_get_info;
2373 FETCH c_get_info INTO l_freq_code, l_term;
2374 CLOSE c_get_info;
2375
2376 -- Populate the level table
2377 FOR l_level_data IN c_level_data LOOP
2378 cf_level_tbl(i).stub_days := l_level_data.stub_days;
2379 cf_level_tbl(i).periods := l_level_data.number_of_periods;
2380 cf_level_tbl(i).start_date := l_level_data.start_date;
2381
2382 i := i + 1;
2383 END LOOP;
2384
2385 IF (l_freq_code IS NOT NULL AND l_term IS NOT NULL AND cf_level_tbl.COUNT > 0) THEN
2386
2387 calculate_level_end_date ( p_level_start_date => cf_level_tbl(0).start_date
2388 ,p_contract_term => l_term
2389 ,p_frequency_code => l_freq_code
2390 ,p_cashflow_level_tbl => cf_level_tbl
2391 ,x_return_status => l_return_status);
2392 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
2393 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2394 ELSIF l_return_status = G_RET_STS_ERROR THEN
2395 RAISE OKL_API.G_EXCEPTION_ERROR;
2396 END IF;
2397
2398 FOR j IN cf_level_tbl.FIRST .. cf_level_tbl.LAST LOOP
2399 IF cf_level_tbl.EXISTS(j) THEN
2400 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_id :=
2401 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_id;
2402 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_parent_data_point_id := l_asset_id;
2403 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_sequence_number := ln_seq_number;
2404 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_value := cf_level_tbl(j).start_date;
2405
2406 ln_seq_number := ln_seq_number + 1;
2407 END IF;
2408 END LOOP;
2409
2410 END IF;
2411
2412 RETURN NULL;
2413
2414 EXCEPTION
2415 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2416 l_pmnt_end_date := NULL;
2417
2418 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2419 l_pmnt_end_date := NULL;
2420
2421 WHEN NO_DATA_FOUND THEN
2422 l_pmnt_end_date := NULL;
2423 WHEN OTHERS THEN
2424 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
2425 x_errormsg := sqlerrm;
2426 END;
2427 END pmnt_end_date ;
2428
2429 ---------------------------------------------
2430 -- FUNCTION fee_name
2431 ---------------------------------------------
2432 FUNCTION fee_name (x_resultout OUT NOCOPY VARCHAR2,
2433 x_errormsg OUT NOCOPY VARCHAR2 ) RETURN VARCHAR2 IS
2434
2435 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
2436 l_fee_name VARCHAR2(30);
2437 -- gboomina:Bug 7110500 :Modified cursor to use correct view
2438 CURSOR cFeeName IS
2439 /*
2440 SELECT STRM.STY_NAME FEE_NAME
2441 FROM OKL_LEASE_APPLICATIONS_B LAP, OKL_LEASE_QUOTES_B QUOTE,
2442 OKL_FEES_B FEES, OKL_STRM_TMPT_PRIMARY_UV STRM
2443 WHERE STRM.STY_ID = FEES.STREAM_TYPE_ID
2444 AND FEES.PARENT_OBJECT_CODE = 'LEASEQUOTE'
2445 AND FEES.PARENT_OBJECT_ID = QUOTE.ID
2446 AND QUOTE.PARENT_OBJECT_ID = LAP.ID
2447 AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
2448 AND QUOTE.PRIMARY_QUOTE = 'Y'
2449 AND LAP.ID = l_lease_app_id;
2450 */
2451 SELECT STRM.NAME FEE_NAME
2452 FROM OKL_LEASE_APPLICATIONS_B LAP, OKL_LEASE_QUOTES_B QUOTE,
2453 OKL_FEES_B FEES, OKL_STRM_TYPE_V STRM
2454 WHERE STRM.ID = FEES.STREAM_TYPE_ID
2455 AND FEES.PARENT_OBJECT_CODE = 'LEASEQUOTE'
2456 AND FEES.PARENT_OBJECT_ID = QUOTE.ID
2457 AND QUOTE.PARENT_OBJECT_ID = LAP.ID
2458 AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
2459 AND QUOTE.PRIMARY_QUOTE = 'Y'
2460 AND LAP.ID = l_lease_app_id;
2461
2462 -- gboomina:Bug 7110500 - End
2463
2464 ln_seq_number NUMBER :=1;
2465
2466 BEGIN
2467 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
2468 x_resultout := FND_API.G_RET_STS_SUCCESS;
2469 BEGIN
2470 x_resultout := FND_API.G_RET_STS_SUCCESS;
2471
2472 FOR cFeeNameRec IN cFeeName LOOP
2473 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_id :=
2474 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_id;
2475 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_parent_data_point_id :=
2476 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_parent_data_point_id;
2477 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_sequence_number := ln_seq_number;
2478 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_value :=
2479 cFeeNameRec.fee_name;
2480
2481 ln_seq_number := ln_seq_number + 1;
2482
2483 END LOOP;
2484 RETURN NULL;
2485
2486 EXCEPTION
2487 WHEN NO_DATA_FOUND THEN
2488 l_fee_name := NULL;
2489 WHEN OTHERS THEN
2490 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
2491 x_errormsg := sqlerrm;
2492 END;
2493 END fee_name ;
2494
2495
2496 ---------------------------------------------
2497 -- FUNCTION fee_type
2498 ---------------------------------------------
2499 FUNCTION fee_type(x_resultout OUT NOCOPY VARCHAR2,
2500 x_errormsg OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
2501
2502 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
2503 l_fee_type VARCHAR2(30);
2504
2505 CURSOR cFeeType IS
2506 SELECT FEES.FEE_TYPE FEE_TYPE
2507 FROM OKL_LEASE_APPLICATIONS_B LAP, OKL_LEASE_QUOTES_B QUOTE, OKL_FEES_B FEES
2508 WHERE FEES.PARENT_OBJECT_CODE = 'LEASEQUOTE'
2509 AND FEES.PARENT_OBJECT_ID = QUOTE.ID
2510 AND QUOTE.PARENT_OBJECT_ID = LAP.ID
2511 AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
2512 AND QUOTE.PRIMARY_QUOTE = 'Y'
2513 AND LAP.ID = l_lease_app_id;
2514
2515
2516 ln_seq_number NUMBER :=1;
2517
2518 BEGIN
2519 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
2520 x_resultout := FND_API.G_RET_STS_SUCCESS;
2521 BEGIN
2522 x_resultout := FND_API.G_RET_STS_SUCCESS;
2523
2524 FOR cFeeTypeRec IN cFeeType LOOP
2525 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_id :=
2526 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_id;
2527 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_parent_data_point_id :=
2528 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_parent_data_point_id;
2529 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_sequence_number := ln_seq_number;
2530 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_value :=
2531 cFeeTypeRec.fee_type;
2532
2533 ln_seq_number := ln_seq_number + 1;
2534
2535 END LOOP;
2536 RETURN NULL;
2537
2538 EXCEPTION
2539 WHEN NO_DATA_FOUND THEN
2540 l_fee_type := NULL;
2541 WHEN OTHERS THEN
2542 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
2543 x_errormsg := sqlerrm;
2544 END;
2545 END fee_type ;
2546
2547 ---------------------------------------------
2548 -- FUNCTION fee_amount
2549 ---------------------------------------------
2550 FUNCTION fee_amount(x_resultout OUT NOCOPY VARCHAR2,
2551 x_errormsg OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
2552
2553 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
2554 l_fee_amount NUMBER;
2555
2556 CURSOR cFeeAmount IS
2557 SELECT FEES.FEE_AMOUNT FEE_AMOUNT
2558 FROM OKL_LEASE_APPLICATIONS_B LAP, OKL_LEASE_QUOTES_B QUOTE, OKL_FEES_B FEES
2559 WHERE FEES.PARENT_OBJECT_CODE = 'LEASEQUOTE'
2560 AND FEES.PARENT_OBJECT_ID = QUOTE.ID
2561 AND QUOTE.PARENT_OBJECT_ID = LAP.ID
2562 AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
2563 AND QUOTE.PRIMARY_QUOTE = 'Y'
2564 AND LAP.ID = l_lease_app_id;
2565
2566
2567 ln_seq_number NUMBER :=1;
2568 BEGIN
2569 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
2570 x_resultout := FND_API.G_RET_STS_SUCCESS;
2571 BEGIN
2572 x_resultout := FND_API.G_RET_STS_SUCCESS;
2573
2574 FOR cFeeAmountRec IN cFeeAmount LOOP
2575 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_id :=
2576 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_id;
2577 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_parent_data_point_id :=
2578 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_parent_data_point_id;
2579 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_sequence_number := ln_seq_number;
2580 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_value :=
2581 cFeeAmountRec.fee_amount;
2582
2583 ln_seq_number := ln_seq_number + 1;
2584
2585 END LOOP;
2586 RETURN NULL;
2587
2588 EXCEPTION
2589 WHEN NO_DATA_FOUND THEN
2590 l_fee_amount := NULL;
2591 WHEN OTHERS THEN
2592 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
2593 x_errormsg := sqlerrm;
2594 END;
2595 END fee_amount ;
2596
2597 ---------------------------------------------
2598 -- FUNCTION fee_date
2599 ---------------------------------------------
2600 FUNCTION fee_date(x_resultout OUT NOCOPY VARCHAR2,
2601 x_errormsg OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
2602
2603 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
2604 l_fee_date DATE;
2605
2606 CURSOR cFeeEffFrom IS
2607 SELECT FEES.EFFECTIVE_FROM EFFECTIVE_FROM
2608 FROM OKL_LEASE_APPLICATIONS_B LAP, OKL_LEASE_QUOTES_B QUOTE, OKL_FEES_B FEES
2609 WHERE FEES.PARENT_OBJECT_CODE = 'LEASEQUOTE'
2610 AND FEES.PARENT_OBJECT_ID = QUOTE.ID
2611 AND QUOTE.PARENT_OBJECT_ID = LAP.ID
2612 AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
2613 AND QUOTE.PRIMARY_QUOTE = 'Y'
2614 AND LAP.ID = l_lease_app_id;
2615
2616
2617 ln_seq_number NUMBER :=1;
2618 BEGIN
2619 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
2620 x_resultout := FND_API.G_RET_STS_SUCCESS;
2621 BEGIN
2622 x_resultout := FND_API.G_RET_STS_SUCCESS;
2623
2624 FOR cFeeEffFromRec IN cFeeEffFrom LOOP
2625 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_id :=
2626 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_id;
2627 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_parent_data_point_id :=
2628 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_parent_data_point_id;
2629 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_sequence_number := ln_seq_number;
2630 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_value :=
2631 cFeeEffFromRec.effective_from;
2632
2633 ln_seq_number := ln_seq_number + 1;
2634
2635 END LOOP;
2636 RETURN NULL;
2637
2638 EXCEPTION
2639 WHEN NO_DATA_FOUND THEN
2640 l_fee_date := NULL;
2641 WHEN OTHERS THEN
2642 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
2643 x_errormsg := sqlerrm;
2644 END;
2645
2646 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_value :=
2647 to_char(l_fee_date) ;
2648
2649 RETURN to_char(l_fee_date);
2650 END fee_date ;
2651
2652 ---------------------------------------------
2653 -- FUNCTION amount_requested
2654 ---------------------------------------------
2655 FUNCTION amount_requested(x_resultout OUT NOCOPY VARCHAR2,
2656 x_errormsg OUT NOCOPY VARCHAR2) RETURN NUMBER IS
2657
2658 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
2659
2660 ln_amount_requested NUMBER;
2661 ln_asset_cost NUMBER;
2662 ln_addon_cost NUMBER;
2663 ln_fee_cost NUMBER;
2664
2665 -- added for bug 6596860 --
2666 CURSOR l_adj_sum_csr(p_lease_app_id IN NUMBER) IS
2667 SELECT NVL(SUM(VALUE),0 )
2668 FROM OKL_LEASE_APPLICATIONS_B LAP, OKL_ASSETS_B ASSET,
2669 OKL_COST_ADJUSTMENTS_B ADJ, OKL_LEASE_QUOTES_B QUOTE
2670 WHERE ADJ.PARENT_OBJECT_CODE = 'ASSET'
2671 AND ADJ.PARENT_OBJECT_ID = ASSET.ID
2672 AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
2673 AND QUOTE.PARENT_OBJECT_ID = LAP.ID
2674 AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
2675 AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
2676 AND QUOTE.PRIMARY_QUOTE = 'Y'
2677 AND ADJ.ADJUSTMENT_SOURCE_TYPE IN ('DOWN_PAYMENT', 'TRADEIN')
2678 AND LAP.ID = p_lease_app_id;
2679
2680 l_adj_amount NUMBER; -- added for bug 6596860 --
2681
2682 BEGIN
2683 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
2684 x_resultout := FND_API.G_RET_STS_SUCCESS;
2685
2686 SELECT NVL(SUM(AST.OEC), 0)
2687 INTO ln_asset_cost
2688 FROM OKL_ASSETS_B AST, OKL_LEASE_QUOTES_B QUOTE, OKL_LEASE_APPLICATIONS_B LAP
2689 WHERE AST.PARENT_OBJECT_ID = QUOTE.ID
2690 AND AST.PARENT_OBJECT_CODE = 'LEASEQUOTE'
2691 AND QUOTE.PARENT_OBJECT_ID = LAP.ID
2692 AND QUOTE.PRIMARY_QUOTE = 'Y'
2693 AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
2694 AND LAP.ID = l_lease_app_id;
2695
2696 /*SELECT NVL(SUM(AST_COMP.UNIT_COST * AST_COMP.NUMBER_OF_UNITS), 0)
2697 INTO ln_addon_cost
2698 FROM OKL_ASSETS_B AST, OKL_ASSET_COMPONENTS_B AST_COMP,
2699 OKL_LEASE_QUOTES_B QUOTE, OKL_LEASE_APPLICATIONS_B LAP
2700 WHERE AST_COMP.ASSET_ID = AST.ID
2701 AND AST_COMP.PRIMARY_COMPONENT = 'NO'
2702 AND AST.PARENT_OBJECT_ID = QUOTE.ID
2703 AND AST.PARENT_OBJECT_CODE = 'LEASEQUOTE'
2704 AND QUOTE.PARENT_OBJECT_ID = LAP.ID
2705 AND QUOTE.PRIMARY_QUOTE = 'Y'
2706 AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
2707 AND LAP.ID = l_lease_app_id;*/
2708
2709 SELECT NVL(SUM(FEES.FEE_AMOUNT), 0)
2710 INTO ln_fee_cost
2711 FROM OKL_LEASE_APPLICATIONS_B LAP, OKL_LEASE_QUOTES_B QUOTE, OKL_FEES_B FEES
2712 WHERE FEES.PARENT_OBJECT_CODE = 'LEASEQUOTE'
2713 AND FEES.PARENT_OBJECT_ID = QUOTE.ID
2714 AND FEES.FEE_TYPE IN ('ROLLOVER', 'FINANCED', 'CAPITALIZED') --Bug 6697231 Added capitalized fee
2715 AND QUOTE.PARENT_OBJECT_ID = LAP.ID
2716 AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
2717 AND QUOTE.PRIMARY_QUOTE = 'Y'
2718 AND LAP.ID = l_lease_app_id;
2719
2720 l_adj_amount :=0; -- added for bug 6596860 --
2721 -- added for bug 6596860 --
2722 OPEN l_adj_sum_csr(l_lease_app_id);
2723 FETCH l_adj_sum_csr INTO l_adj_amount;
2724 CLOSE l_adj_sum_csr;
2725
2726 ln_amount_requested := ln_asset_cost + ln_fee_cost - l_adj_amount ; -- added for bug 6596860 --removded addon and added adj_amount
2727
2728 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_value :=
2729 ln_amount_requested;
2730
2731 RETURN ln_amount_requested;
2732 END amount_requested ;
2733
2734 ---------------------------------------------
2735 -- FUNCTION total_financed_amount
2736 ---------------------------------------------
2737 FUNCTION total_financed_amount(x_resultout OUT NOCOPY VARCHAR2,
2738 x_errormsg OUT NOCOPY VARCHAR2) RETURN NUMBER IS
2739
2740 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
2741 ln_financed_amount NUMBER;
2742
2743 BEGIN
2744 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
2745 x_resultout := FND_API.G_RET_STS_SUCCESS;
2746
2747 ln_financed_amount := amount_requested(x_resultout => x_resultout,
2748 x_errormsg => x_errormsg);
2749
2750 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_value :=
2751 ln_financed_amount ;
2752
2753 RETURN ln_financed_amount;
2754 END total_financed_amount ;
2755
2756 ---------------------------------------------
2757 -- FUNCTION total_subsidized_cost
2758 ---------------------------------------------
2759 FUNCTION total_subsidized_cost(x_resultout OUT NOCOPY VARCHAR2,
2760 x_errormsg OUT NOCOPY VARCHAR2) RETURN NUMBER IS
2761
2762 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
2763
2764 ln_subs_cost NUMBER;
2765 ln_down_payment_amount NUMBER;
2766 ln_financed_amount NUMBER;
2767 ln_subsidy_amount NUMBER;
2768
2769 BEGIN
2770 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
2771 SELECT NVL(SUM(ADJ.VALUE), 0)
2772 INTO ln_down_payment_amount
2773 FROM OKL_LEASE_APPLICATIONS_B LAP, OKL_ASSETS_B ASSET,
2774 OKL_COST_ADJUSTMENTS_B ADJ, OKL_LEASE_QUOTES_B QUOTE
2775 WHERE ADJ.ADJUSTMENT_SOURCE_TYPE IN ('DOWN_PAYMENT', 'TRADEIN')
2776 AND ADJ.PARENT_OBJECT_CODE = 'ASSET'
2777 AND ADJ.PARENT_OBJECT_ID = ASSET.ID
2778 AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
2779 AND QUOTE.PARENT_OBJECT_ID = LAP.ID
2780 AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
2781 AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
2782 AND QUOTE.PRIMARY_QUOTE = 'Y'
2783 AND LAP.ID = l_lease_app_id;
2784
2785 x_resultout := FND_API.G_RET_STS_SUCCESS;
2786
2787 ln_financed_amount := amount_requested(x_resultout => x_resultout,
2788 x_errormsg => x_errormsg);
2789
2790 ln_subsidy_amount := subsidy_amount(x_resultout => x_resultout,
2791 x_errormsg => x_errormsg);
2792
2793 ln_subs_cost := ln_financed_amount - ln_down_payment_amount - ln_subsidy_amount;
2794
2795 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_value := ln_subs_cost;
2796
2797 RETURN ln_subs_cost;
2798 END total_subsidized_cost ;
2799
2800 ---------------------------------------------
2801 -- FUNCTION security_deposit
2802 ---------------------------------------------
2803 FUNCTION security_deposit(x_resultout OUT NOCOPY VARCHAR2,
2804 x_errormsg OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
2805
2806 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
2807 l_security_deposit VARCHAR2(120);
2808
2809 CURSOR cSecDeposit IS
2810 SELECT FEES.FEE_AMOUNT FEE_AMOUNT
2811 FROM OKL_LEASE_APPLICATIONS_B LAP, OKL_LEASE_QUOTES_B QUOTE, OKL_FEES_B FEES
2812 WHERE FEES.PARENT_OBJECT_CODE = 'LEASEQUOTE'
2813 AND FEES.PARENT_OBJECT_ID = QUOTE.ID
2814 AND QUOTE.PARENT_OBJECT_ID = LAP.ID
2815 AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
2816 AND QUOTE.PRIMARY_QUOTE = 'Y'
2817 AND FEES.FEE_TYPE = 'SEC_DEPOSIT'
2818 AND LAP.ID = l_lease_app_id;
2819
2820
2821 ln_seq_number NUMBER :=1;
2822
2823 BEGIN
2824 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
2825 x_resultout := FND_API.G_RET_STS_SUCCESS;
2826 BEGIN
2827 x_resultout := FND_API.G_RET_STS_SUCCESS;
2828
2829 FOR cSecDepositRec IN cSecDeposit
2830 LOOP
2831 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_id :=
2832 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_id;
2833 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_parent_data_point_id :=
2834 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_parent_data_point_id;
2835 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_sequence_number := ln_seq_number;
2836 OCM_ADD_DATA_POINTS.pg_ocm_dp_values_tbl(ln_seq_number).p_data_point_value :=
2837 cSecDepositRec.fee_amount;
2838
2839 ln_seq_number := ln_seq_number + 1;
2840
2841 END LOOP;
2842 RETURN NULL;
2843
2844
2845 EXCEPTION
2846 WHEN NO_DATA_FOUND THEN
2847 l_security_deposit := NULL;
2848 WHEN OTHERS THEN
2849 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
2850 x_errormsg := sqlerrm;
2851 END;
2852
2853 END security_deposit ;
2854
2855 ---------------------------------------------
2856 -- FUNCTION billed_tax
2857 ---------------------------------------------
2858 FUNCTION billed_tax(x_resultout OUT NOCOPY VARCHAR2,
2859 x_errormsg OUT NOCOPY VARCHAR2) RETURN NUMBER IS
2860
2861 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
2862 l_billed_tax NUMBER;
2863
2864 BEGIN
2865 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
2866 x_resultout := FND_API.G_RET_STS_SUCCESS;
2867 BEGIN
2868 SELECT SUM(TRXD.TAX_AMT)
2869 INTO l_billed_tax
2870 FROM
2871 OKL_TAX_SOURCES TXS, OKL_TAX_TRX_DETAILS TRXD, OKL_LEASE_QUOTES_B LSQ,
2872 OKL_LEASE_APPLICATIONS_B LAP
2873 WHERE
2874 TRXD.TXS_ID = TXS.ID
2875 AND TRXD.BILLED_YN = 'Y'
2876 AND TXS.TRX_ID = LSQ.ID
2877 AND TXS.TAX_CALL_TYPE_CODE = 'UPFRONT_TAX'
2878 AND TXS.TAX_CALL_TYPE_CODE = TRXD.TAX_CALL_TYPE_CODE
2879 AND TXS.ENTITY_CODE = 'OKL_LEASE_QUOTES_B'
2880 AND TXS.TAX_LINE_STATUS_CODE = 'ACTIVE'
2881 AND LSQ.PARENT_OBJECT_ID = LAP.ID
2882 AND LSQ.PARENT_OBJECT_CODE = 'LEASEAPP'
2883 AND LAP.ID = l_lease_app_id;
2884
2885 EXCEPTION
2886 WHEN NO_DATA_FOUND THEN
2887 l_billed_tax := NULL;
2888 WHEN OTHERS THEN
2889 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
2890 x_errormsg := sqlerrm;
2891 END;
2892
2893 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_value :=
2894 l_billed_tax;
2895
2896 RETURN l_billed_tax;
2897 END billed_tax ;
2898
2899 ---------------------------------------------
2900 -- PROCEDURE fetch_leaseapp_datapoints
2901 ---------------------------------------------
2902 PROCEDURE fetch_leaseapp_datapoints(p_api_version IN NUMBER
2903 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
2904 ,p_leaseapp_id IN NUMBER
2905 ,x_lap_dp_tbl_type OUT NOCOPY lap_dp_tbl_type
2906 ,x_return_status OUT NOCOPY VARCHAR2
2907 ,x_msg_count OUT NOCOPY NUMBER
2908 ,x_msg_data OUT NOCOPY VARCHAR2) IS
2909
2910 l_program_name CONSTANT VARCHAR2(30) := 'fetch_leaseapp_datapoints';
2911 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
2912
2913 CURSOR c_get_leaseapp_template IS
2914 SELECT lat.credit_review_purpose,
2915 lat.cust_credit_classification
2916 FROM
2917 okl_leaseapp_templates lat,
2918 okl_leaseapp_templ_versions_b latv,
2919 okl_lease_applications_b lap
2920 WHERE
2921 lap.leaseapp_template_id = latv.id
2922 AND latv.version_status = 'ACTIVE'
2923 AND latv.valid_from <= lap.valid_from
2924 AND nvl(latv.valid_to, lap.valid_from) >= lap.valid_from
2925 AND latv.leaseapp_template_id = lat.id
2926 AND lap.id = p_leaseapp_id;
2927
2928 lv_credit_rev_purpose VARCHAR2(30);
2929 lv_credit_classification VARCHAR2(30);
2930
2931 x_data_points_tbl OCM_DATA_POINTS_PUB.data_points_tbl;
2932
2933 BEGIN
2934 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
2935 IF p_init_msg_list = G_TRUE THEN
2936 FND_MSG_PUB.initialize;
2937 END IF;
2938
2939 OPEN c_get_leaseapp_template;
2940 FETCH c_get_leaseapp_template INTO lv_credit_rev_purpose, lv_credit_classification;
2941 CLOSE c_get_leaseapp_template;
2942
2943 OCM_DATA_POINTS_PUB.GET_DATA_POINTS(p_api_version => p_api_version,
2944 p_init_msg_list => p_init_msg_list,
2945 p_validation_level => 'F',
2946 p_credit_classification => lv_credit_classification,
2947 p_review_type => lv_credit_rev_purpose,
2948 p_data_point_category => 'ADDITIONAL',
2949 p_data_point_sub_category => 'OKL_LAP_DATAPOINT',
2950 x_return_status => x_return_status,
2951 x_msg_count => x_msg_count,
2952 x_msg_data => x_msg_data,
2953 p_datapoints_tbl => x_data_points_tbl);
2954 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2955 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2956 ELSIF x_return_status = G_RET_STS_ERROR THEN
2957 RAISE OKL_API.G_EXCEPTION_ERROR;
2958 END IF;
2959
2960 IF (x_data_points_tbl.COUNT > 0) THEN
2961 FOR i IN x_data_points_tbl.FIRST .. x_data_points_tbl.LAST LOOP
2962 IF x_data_points_tbl.EXISTS(i) THEN
2963 x_lap_dp_tbl_type(i).data_point_id := x_data_points_tbl(i).data_point_id;
2964 x_lap_dp_tbl_type(i).data_point_category := x_data_points_tbl(i).data_point_sub_category;
2965 x_lap_dp_tbl_type(i).data_point_name := x_data_points_tbl(i).data_point_name;
2966 x_lap_dp_tbl_type(i).description := x_data_points_tbl(i).description;
2967 END IF;
2968 END LOOP;
2969 END IF;
2970
2971 x_return_status := G_RET_STS_SUCCESS;
2972
2973 EXCEPTION
2974 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2975 IF (c_get_leaseapp_template%ISOPEN) THEN
2976 CLOSE c_get_leaseapp_template;
2977 END IF;
2978
2979 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2980 IF (c_get_leaseapp_template%ISOPEN) THEN
2981 CLOSE c_get_leaseapp_template;
2982 END IF;
2983
2984 WHEN OTHERS THEN
2985 IF (c_get_leaseapp_template%ISOPEN) THEN
2986 CLOSE c_get_leaseapp_template;
2987 END IF;
2988
2989 END fetch_leaseapp_datapoints;
2990
2991 ---------------------------------------------
2992 -- PROCEDURE store_leaseapp_datapoints
2993 ---------------------------------------------
2994 PROCEDURE store_leaseapp_datapoints(p_api_version IN NUMBER
2995 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
2996 ,p_lap_dp_tbl IN lap_dp_tbl_type
2997 ,x_return_status OUT NOCOPY VARCHAR2
2998 ,x_msg_count OUT NOCOPY NUMBER
2999 ,x_msg_data OUT NOCOPY VARCHAR2) IS
3000
3001 l_program_name CONSTANT VARCHAR2(30) := 'store_leaseapp_datapoints';
3002 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
3003
3004 j BINARY_INTEGER := 0;
3005
3006 l_lap_dp_tbl lap_dp_tbl_type;
3007 lx_lap_dp_tbl lap_dp_tbl_type;
3008
3009 lp_lap_dp_tbl okl_lad_pvt.ladv_tbl_type;
3010 lpx_lap_dp_tbl okl_lad_pvt.ladv_tbl_type;
3011
3012 ln_index NUMBER;
3013
3014 BEGIN
3015 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
3016 IF p_init_msg_list = G_TRUE THEN
3017 FND_MSG_PUB.initialize;
3018 END IF;
3019
3020 l_lap_dp_tbl := p_lap_dp_tbl;
3021
3022 IF (l_lap_dp_tbl.COUNT > 0) THEN
3023
3024 FOR i IN l_lap_dp_tbl.FIRST .. l_lap_dp_tbl.LAST LOOP
3025 IF l_lap_dp_tbl.EXISTS(i) THEN
3026 lp_lap_dp_tbl(i).id := l_lap_dp_tbl(i).id;
3027 lp_lap_dp_tbl(i).object_version_number := l_lap_dp_tbl(i).object_version_number;
3028 lp_lap_dp_tbl(i).leaseapp_id := l_lap_dp_tbl(i).leaseapp_id;
3029 lp_lap_dp_tbl(i).data_point_id := l_lap_dp_tbl(i).data_point_id;
3030 lp_lap_dp_tbl(i).data_point_category := l_lap_dp_tbl(i).data_point_category;
3031 lp_lap_dp_tbl(i).data_point_value := l_lap_dp_tbl(i).data_point_value;
3032 END IF;
3033 END LOOP;
3034
3035 ln_index := lp_lap_dp_tbl.FIRST;
3036 IF (lp_lap_dp_tbl(ln_index).id is null OR lp_lap_dp_tbl(ln_index).id = OKL_API.G_MISS_NUM) THEN
3037
3038 okl_lad_pvt.insert_row (p_api_version => G_API_VERSION
3039 ,p_init_msg_list => G_FALSE
3040 ,x_return_status => x_return_status
3041 ,x_msg_count => x_msg_count
3042 ,x_msg_data => x_msg_data
3043 ,p_ladv_tbl => lp_lap_dp_tbl
3044 ,x_ladv_tbl => lpx_lap_dp_tbl);
3045 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3046 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3047 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3048 RAISE OKL_API.G_EXCEPTION_ERROR;
3049 END IF;
3050 ELSE
3051 okl_lad_pvt.update_row (p_api_version => G_API_VERSION
3052 ,p_init_msg_list => G_FALSE
3053 ,x_return_status => x_return_status
3054 ,x_msg_count => x_msg_count
3055 ,x_msg_data => x_msg_data
3056 ,p_ladv_tbl => lp_lap_dp_tbl
3057 ,x_ladv_tbl => lpx_lap_dp_tbl);
3058 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3059 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3060 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3061 RAISE OKL_API.G_EXCEPTION_ERROR;
3062 END IF;
3063 END IF;
3064 END IF;
3065
3066 x_return_status := G_RET_STS_SUCCESS;
3067
3068 EXCEPTION
3069 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3070 x_return_status := G_RET_STS_ERROR;
3071 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3072 x_return_status := G_RET_STS_UNEXP_ERROR;
3073 WHEN OTHERS THEN
3074 x_return_status := G_RET_STS_ERROR;
3075 END store_leaseapp_datapoints;
3076
3077 ---------------------------------------------
3078 -- PROCEDURE delete_leaseapp_datapoints
3079 ---------------------------------------------
3080 PROCEDURE delete_leaseapp_datapoints(p_api_version IN NUMBER
3081 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
3082 ,p_leaseapp_id IN NUMBER
3083 ,x_return_status OUT NOCOPY VARCHAR2
3084 ,x_msg_count OUT NOCOPY NUMBER
3085 ,x_msg_data OUT NOCOPY VARCHAR2) IS
3086
3087 l_program_name CONSTANT VARCHAR2(30) := 'delete_leaseapp_datapoints';
3088 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
3089
3090 i BINARY_INTEGER := 0;
3091
3092 lap_dp_tbl okl_lad_pvt.ladv_tbl_type;
3093 l_error_tbl_type OKC_API.ERROR_TBL_TYPE;
3094
3095 CURSOR c_get_leaseapp_datapoints IS
3096 SELECT id
3097 FROM
3098 okl_leaseapp_datapoints
3099 WHERE
3100 leaseapp_id = p_leaseapp_id;
3101
3102 BEGIN
3103 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
3104 IF p_init_msg_list = G_TRUE THEN
3105 FND_MSG_PUB.initialize;
3106 END IF;
3107
3108 FOR l_get_leaseapp_datapoints IN c_get_leaseapp_datapoints LOOP
3109 lap_dp_tbl(i).id := l_get_leaseapp_datapoints.id;
3110 i := i + 1;
3111 END LOOP;
3112
3113 IF lap_dp_tbl.COUNT > 0 THEN
3114 okl_lad_pvt.delete_row (
3115 p_api_version => G_API_VERSION
3116 ,p_init_msg_list => G_FALSE
3117 ,x_return_status => x_return_status
3118 ,x_msg_count => x_msg_count
3119 ,x_msg_data => x_msg_data
3120 ,p_ladv_tbl => lap_dp_tbl
3121 ,px_error_tbl => l_error_tbl_type );
3122
3123 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
3124 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3125 ELSIF x_return_status = G_RET_STS_ERROR THEN
3126 RAISE OKL_API.G_EXCEPTION_ERROR;
3127 END IF;
3128 END IF;
3129
3130 x_return_status := G_RET_STS_SUCCESS;
3131
3132 EXCEPTION
3133 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3134 x_return_status := G_RET_STS_ERROR;
3135 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3136 x_return_status := G_RET_STS_UNEXP_ERROR;
3137 WHEN OTHERS THEN
3138 x_return_status := G_RET_STS_ERROR;
3139 END delete_leaseapp_datapoints;
3140
3141 ---------------------------------------------
3142 -- FUNCTION leaseapp_datapoints_exists
3143 ---------------------------------------------
3144 FUNCTION leaseapp_datapoints_exists(p_leaseapp_id IN NUMBER)
3145 RETURN BOOLEAN IS
3146
3147 ln_dp_count NUMBER;
3148
3149 CURSOR c_get_leaseapp_datapoints IS
3150 SELECT count(*)
3151 FROM
3152 okl_leaseapp_datapoints
3153 WHERE
3154 leaseapp_id = p_leaseapp_id;
3155
3156 BEGIN
3157 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
3158 OPEN c_get_leaseapp_datapoints;
3159 FETCH c_get_leaseapp_datapoints INTO ln_dp_count;
3160 CLOSE c_get_leaseapp_datapoints;
3161
3162 IF (ln_dp_count > 0) THEN
3163 RETURN TRUE;
3164 ELSE
3165 RETURN FALSE;
3166 END IF;
3167 END leaseapp_datapoints_exists ;
3168
3169 FUNCTION fetch_data_point_value(x_resultout OUT NOCOPY VARCHAR2,
3170 x_errormsg OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
3171
3172 l_lease_app_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
3173 l_data_point_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_id);
3174
3175 lv_data_point_value okl_leaseapp_datapoints.data_point_value%TYPE;
3176
3177 BEGIN
3178 mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
3179 x_resultout := FND_API.G_RET_STS_SUCCESS;
3180 BEGIN
3181 SELECT DATA_POINT_VALUE
3182 INTO lv_data_point_value
3183 FROM OKL_LEASEAPP_DATAPOINTS
3184 WHERE DATA_POINT_ID = l_data_point_id
3185 AND LEASEAPP_ID = l_lease_app_id;
3186
3187 EXCEPTION
3188 WHEN NO_DATA_FOUND THEN
3189 lv_data_point_value := NULL;
3190 WHEN OTHERS THEN
3191 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
3192 x_errormsg := sqlerrm;
3193 END;
3194
3195 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_value := lv_data_point_value;
3196
3197 RETURN lv_data_point_value;
3198
3199 END fetch_data_point_value ;
3200
3201 END OKL_CREDIT_DATAPOINTS_PVT;