[Home] [Help]
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:
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:
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
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:
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'
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:
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;
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;
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;
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
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
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
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;
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'
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
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'
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'
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
731: l_manufacturer VARCHAR2(360);
732:
733: CURSOR cMfgName is
734: SELECT ASSET_COMP.MANUFACTURER_NAME MANUFACTURER_NAME
735: FROM OKL_ASSET_COMPONENTS_B ASSET_COMP, OKL_LEASE_APPLICATIONS_B LAP,
736: OKL_ASSETS_B ASSET, OKL_LEASE_QUOTES_B QUOTE
737: WHERE ASSET_COMP.ASSET_ID = ASSET.ID
738: AND ASSET_COMP.PRIMARY_COMPONENT = 'YES'
739: AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
785: l_year_of_manufacture NUMBER;
786:
787: CURSOR cYearMfg IS
788: SELECT ASSET_COMP.YEAR_MANUFACTURED YEAR_MANUFACTURED
789: FROM OKL_ASSET_COMPONENTS_B ASSET_COMP, OKL_LEASE_APPLICATIONS_B LAP,
790: OKL_ASSETS_B ASSET, OKL_LEASE_QUOTES_B QUOTE
791: WHERE ASSET_COMP.ASSET_ID = ASSET.ID
792: AND ASSET_COMP.PRIMARY_COMPONENT = 'YES'
793: AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
838: l_no_of_units NUMBER;
839:
840: CURSOR cNoOfUnits IS
841: SELECT ASSET_COMP.NUMBER_OF_UNITS NUMBER_OF_UNITS
842: FROM OKL_ASSET_COMPONENTS_B ASSET_COMP, OKL_LEASE_APPLICATIONS_B LAP,
843: OKL_ASSETS_B ASSET, OKL_LEASE_QUOTES_B QUOTE
844: WHERE ASSET_COMP.ASSET_ID = ASSET.ID
845: AND ASSET_COMP.PRIMARY_COMPONENT = 'YES'
846: AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
890: l_unit_cost NUMBER;
891:
892: CURSOR cUnitCost IS
893: SELECT ASSET_COMP.UNIT_COST UNIT_COST
894: FROM OKL_ASSET_COMPONENTS_B ASSET_COMP, OKL_LEASE_APPLICATIONS_B LAP,
895: OKL_ASSETS_B ASSET, OKL_LEASE_QUOTES_B QUOTE
896: WHERE ASSET_COMP.ASSET_ID = ASSET.ID
897: AND ASSET_COMP.PRIMARY_COMPONENT = 'YES'
898: AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
944: l_install_site VARCHAR2(30);
945:
946: CURSOR cInstallSite IS
947: SELECT PARTY_SITE.PARTY_SITE_NUMBER PARTY_SITE_NUMBER
948: FROM HZ_PARTY_SITES PARTY_SITE, OKL_LEASE_APPLICATIONS_B LAP,
949: OKL_ASSETS_B ASSET, OKL_LEASE_QUOTES_B QUOTE
950: WHERE PARTY_SITE.PARTY_SITE_ID = ASSET.INSTALL_SITE_ID
951: AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
952: AND QUOTE.PARENT_OBJECT_ID = LAP.ID
1001: BEGIN
1002: -- gboomina Bug 7110500 - Start
1003: SELECT QUOTE.USAGE_CATEGORY --QUOTE.USAGE_INDUSTRY_CLASS
1004: INTO l_usage_of_equipment
1005: FROM OKL_LEASE_APPLICATIONS_B LAP, OKL_LEASE_QUOTES_B QUOTE
1006: WHERE QUOTE.PARENT_OBJECT_ID = LAP.ID
1007: AND QUOTE.PRIMARY_QUOTE = 'Y'
1008: AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
1009: AND LAP.ID = l_lease_app_id;
1040: -- gboomina Bug 7110500 - Start
1041: /*
1042: SELECT QUOTE.USAGE_INDUSTRY_CODE
1043: INTO l_usage_industry
1044: FROM OKL_LEASE_APPLICATIONS_B LAP, OKL_LEASE_QUOTES_B QUOTE
1045: WHERE QUOTE.PARENT_OBJECT_ID = LAP.ID
1046: AND QUOTE.PRIMARY_QUOTE = 'Y'
1047: AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
1048: AND LAP.ID = l_lease_app_id;
1049: */
1050:
1051: SELECT INDC.MEANING ||' - '||IND.MEANING USAGE_INDUSTRY
1052: INTO l_usage_industry
1053: FROM OKL_LEASE_APPLICATIONS_B LAP,AR_LOOKUPS INDC, AR_LOOKUPS IND
1054: WHERE INDC.LOOKUP_TYPE(+)='SIC_CODE_TYPE'
1055: AND INDC.LOOKUP_CODE(+)=LAP.INDUSTRY_CLASS
1056: AND IND.LOOKUP_TYPE(+)=LAP.INDUSTRY_CLASS
1057: AND IND.LOOKUP_CODE(+)=LAP.INDUSTRY_CODE
1087: x_resultout := FND_API.G_RET_STS_SUCCESS;
1088: BEGIN
1089: SELECT QUOTE.USAGE_CATEGORY
1090: INTO l_usage_category
1091: FROM OKL_LEASE_APPLICATIONS_B LAP, OKL_LEASE_QUOTES_B QUOTE
1092: WHERE QUOTE.PARENT_OBJECT_ID = LAP.ID
1093: AND QUOTE.PRIMARY_QUOTE = 'Y'
1094: AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
1095: AND LAP.ID = l_lease_app_id;
1125: -- gboomina Bug 7110500 - Start
1126: /*
1127: SELECT QUOTE.USAGE_AMOUNT
1128: INTO l_usage_amount
1129: FROM OKL_LEASE_APPLICATIONS_B LAP, OKL_LEASE_QUOTES_B QUOTE
1130: WHERE QUOTE.PARENT_OBJECT_ID = LAP.ID
1131: AND QUOTE.PRIMARY_QUOTE = 'Y'
1132: AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
1133: AND LAP.ID = l_lease_app_id;
1133: AND LAP.ID = l_lease_app_id;
1134: */
1135: SELECT LOP.USAGE_AMOUNT
1136: INTO l_usage_amount
1137: FROM OKL_LEASE_APPLICATIONS_B LAP, OKL_LEASE_OPPORTUNITIES_B LOP
1138: WHERE LOP.ID= LAP.LEASE_OPPORTUNITY_ID
1139: AND LAP.ID = l_lease_app_id;
1140:
1141: -- gboomina Bug 7110500 - End
1454: l_add_on_item_residual_values NUMBER;
1455:
1456: CURSOR cItemResVal IS
1457: SELECT NVL(ASSET.END_OF_TERM_VALUE,ASSET.END_OF_TERM_VALUE_DEFAULT) END_OF_TERM_VALUE
1458: FROM OKL_LEASE_APPLICATIONS_B LAP, OKL_ASSETS_B ASSET, OKL_LEASE_QUOTES_B QUOTE
1459: WHERE ASSET.PARENT_OBJECT_ID = QUOTE.ID
1460: AND QUOTE.PARENT_OBJECT_ID = LAP.ID
1461: AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
1462: AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
1508: x_resultout := FND_API.G_RET_STS_SUCCESS;
1509: BEGIN
1510: SELECT NVL(SUM(ADJ.VALUE), 0)
1511: INTO l_down_payment_amount
1512: FROM OKL_LEASE_APPLICATIONS_B LAP, OKL_ASSETS_B ASSET,
1513: OKL_COST_ADJUSTMENTS_B ADJ, OKL_LEASE_QUOTES_B QUOTE
1514: WHERE ADJ.ADJUSTMENT_SOURCE_TYPE = 'DOWN_PAYMENT'
1515: AND ADJ.PARENT_OBJECT_CODE = 'ASSET'
1516: AND ADJ.PARENT_OBJECT_ID = ASSET.ID
1548: x_resultout := FND_API.G_RET_STS_SUCCESS;
1549: BEGIN
1550: SELECT SUM(DECODE(ADJ.VALUE, NULL, ADJ.DEFAULT_SUBSIDY_AMOUNT, ADJ.VALUE)) SUBSIDY_AMOUNT
1551: INTO l_subsidy_amount
1552: FROM OKL_LEASE_APPLICATIONS_B LAP, OKL_ASSETS_B ASSET,
1553: OKL_COST_ADJUSTMENTS_B ADJ, OKL_LEASE_QUOTES_B QUOTE
1554: WHERE ADJ.ADJUSTMENT_SOURCE_TYPE = 'SUBSIDY'
1555: AND ADJ.PARENT_OBJECT_CODE = 'ASSET'
1556: AND ADJ.PARENT_OBJECT_ID = ASSET.ID
1589: x_resultout := FND_API.G_RET_STS_SUCCESS;
1590: BEGIN
1591: SELECT NVL(SUM(ADJ.VALUE), 0)
1592: INTO l_trade_in_amount
1593: FROM OKL_LEASE_APPLICATIONS_B LAP, OKL_ASSETS_B ASSET,
1594: OKL_COST_ADJUSTMENTS_B ADJ, OKL_LEASE_QUOTES_B QUOTE
1595: WHERE ADJ.ADJUSTMENT_SOURCE_TYPE = 'TRADEIN'
1596: AND ADJ.PARENT_OBJECT_CODE = 'ASSET'
1597: AND ADJ.PARENT_OBJECT_ID = ASSET.ID
1626: l_trade_in_asset_number VARCHAR2(15);
1627:
1628: CURSOR cAssetNumber IS
1629: SELECT ASSET.ASSET_NUMBER
1630: FROM OKL_LEASE_APPLICATIONS_B LAP, OKL_ASSETS_B ASSET,
1631: OKL_LEASE_QUOTES_B QUOTE, OKL_COST_ADJUSTMENTS_B ADJ
1632: WHERE ADJ.ADJUSTMENT_SOURCE_TYPE = 'TRADEIN'
1633: AND ADJ.PARENT_OBJECT_CODE = 'ASSET'
1634: AND ADJ.PARENT_OBJECT_ID = ASSET.ID
1695: AND FND.LOOKUP_TYPE = 'OKL_FREQUENCY';
1696:
1697: /*UNION
1698: select OKL_CASH_FLOW_LEVELS.FQY_CODE FQY_CODE
1699: from okl_lease_applications_b LAP, OKL_FEES_B FEE, OKL_CASH_FLOW_OBJECTS,
1700: okl_cash_flows, OKL_CASH_FLOW_LEVELS, OKL_LEASE_QUOTES_B QUOTE
1701: where OKL_CASH_FLOW_objects.OTY_CODE = 'QUOTED_FEE'
1702: and OKL_CASH_FLOW_objects.id = okl_cash_flows.CFO_ID
1703: and okl_cash_flows.id = OKL_CASH_FLOW_LEVELS.CAF_ID
1710: and QUOTE.primary_quote = 'Y'
1711: and LAP.id = l_lease_app_id
1712: UNION
1713: select OKL_CASH_FLOW_LEVELS.FQY_CODE FQY_CODE
1714: from okl_lease_applications_b LAP, OKL_SERVICES_B SRV, OKL_CASH_FLOW_OBJECTS,
1715: okl_cash_flows, OKL_CASH_FLOW_LEVELS, OKL_LEASE_QUOTES_B QUOTE
1716: where OKL_CASH_FLOW_objects.OTY_CODE = 'QUOTED_SERVICE'
1717: and OKL_CASH_FLOW_objects.id = okl_cash_flows.CFO_ID
1718: and okl_cash_flows.id = OKL_CASH_FLOW_LEVELS.CAF_ID
1725: and QUOTE.primary_quote = 'Y'
1726: and LAP.id = l_lease_app_id
1727: UNION
1728: select OKL_CASH_FLOW_LEVELS.FQY_CODE FQY_CODE
1729: from okl_lease_applications_b LAP, OKL_INSURANCE_ESTIMATES_B INS, OKL_CASH_FLOW_OBJECTS,
1730: okl_cash_flows, OKL_CASH_FLOW_LEVELS, OKL_LEASE_QUOTES_B QUOTE
1731: where OKL_CASH_FLOW_objects.OTY_CODE = 'QUOTED_INSURANCE'
1732: and OKL_CASH_FLOW_objects.id = okl_cash_flows.CFO_ID
1733: and okl_cash_flows.id = OKL_CASH_FLOW_LEVELS.CAF_ID
1795: AND FND.LOOKUP_TYPE = 'OKL_YES_NO';
1796:
1797: /*union
1798: select OKL_CASH_FLOWS.due_arrears_yn due_arrears_yn
1799: from okl_lease_applications_b LAP, OKL_FEES_B FEE, OKL_CASH_FLOW_OBJECTS,
1800: okl_cash_flows, OKL_LEASE_QUOTES_B QUOTE
1801: where OKL_CASH_FLOW_objects.OTY_CODE = 'QUOTED_FEE'
1802: and OKL_CASH_FLOW_objects.id = okl_cash_flows.CFO_ID
1803: and OKL_CASH_FLOW_OBJECTS.source_id = FEE.id
1809: and QUOTE.primary_quote = 'Y'
1810: and LAP.id = l_lease_app_id
1811: union
1812: select OKL_CASH_FLOWS.due_arrears_yn due_arrears_yn
1813: from okl_lease_applications_b LAP, OKL_SERVICES_B SRV, OKL_CASH_FLOW_OBJECTS,
1814: okl_cash_flows, OKL_LEASE_QUOTES_B QUOTE
1815: where OKL_CASH_FLOW_objects.OTY_CODE = 'QUOTED_SERVICE'
1816: and OKL_CASH_FLOW_objects.id = okl_cash_flows.CFO_ID
1817: and OKL_CASH_FLOW_OBJECTS.source_id = SRV.id
1823: and QUOTE.primary_quote = 'Y'
1824: and LAP.id = l_lease_app_id
1825: union
1826: select OKL_CASH_FLOWS.due_arrears_yn due_arrears_yn
1827: from okl_lease_applications_b LAP, OKL_INSURANCE_ESTIMATES_B INS, OKL_CASH_FLOW_OBJECTS,
1828: okl_cash_flows, OKL_LEASE_QUOTES_B QUOTE
1829: where OKL_CASH_FLOW_objects.OTY_CODE = 'QUOTED_INSURANCE'
1830: and OKL_CASH_FLOW_objects.id = okl_cash_flows.CFO_ID
1831: and OKL_CASH_FLOW_OBJECTS.source_id = INS.id
1889: and CFO.SOURCE_TABLE = 'OKL_ASSETS_B';
1890:
1891: /*union
1892: select OKL_CASH_FLOW_LEVELS.number_of_periods number_of_periods
1893: from okl_lease_applications_b LAP, OKL_FEES_B FEE, OKL_CASH_FLOW_OBJECTS,
1894: okl_cash_flows, OKL_CASH_FLOW_LEVELS, OKL_LEASE_QUOTES_B QUOTE
1895: where OKL_CASH_FLOW_objects.OTY_CODE = 'QUOTED_FEE'
1896: and OKL_CASH_FLOW_objects.id = okl_cash_flows.CFO_ID
1897: and okl_cash_flows.id = OKL_CASH_FLOW_LEVELS.CAF_ID
1904: and QUOTE.primary_quote = 'Y'
1905: and LAP.id = l_lease_app_id
1906: union
1907: select OKL_CASH_FLOW_LEVELS.number_of_periods number_of_periods
1908: from okl_lease_applications_b LAP, OKL_SERVICES_B SRV, OKL_CASH_FLOW_OBJECTS,
1909: okl_cash_flows, OKL_CASH_FLOW_LEVELS, OKL_LEASE_QUOTES_B QUOTE
1910: where OKL_CASH_FLOW_objects.OTY_CODE = 'QUOTED_SERVICE'
1911: and OKL_CASH_FLOW_objects.id = okl_cash_flows.CFO_ID
1912: and okl_cash_flows.id = OKL_CASH_FLOW_LEVELS.CAF_ID
1919: and QUOTE.primary_quote = 'Y'
1920: and LAP.id = l_lease_app_id
1921: union
1922: select OKL_CASH_FLOW_LEVELS.number_of_periods number_of_periods
1923: from okl_lease_applications_b LAP, OKL_INSURANCE_ESTIMATES_B INS, OKL_CASH_FLOW_OBJECTS,
1924: okl_cash_flows, OKL_CASH_FLOW_LEVELS, OKL_LEASE_QUOTES_B QUOTE
1925: where OKL_CASH_FLOW_objects.OTY_CODE = 'QUOTED_INSURANCE'
1926: and OKL_CASH_FLOW_objects.id = okl_cash_flows.CFO_ID
1927: and okl_cash_flows.id = OKL_CASH_FLOW_LEVELS.CAF_ID
1985: and CFO.SOURCE_TABLE = 'OKL_ASSETS_B';
1986:
1987: /*union
1988: select OKL_CASH_FLOW_LEVELS.amount amount
1989: from okl_lease_applications_b LAP, OKL_FEES_B FEE, OKL_CASH_FLOW_OBJECTS,
1990: okl_cash_flows, OKL_CASH_FLOW_LEVELS, OKL_LEASE_QUOTES_B QUOTE
1991: where OKL_CASH_FLOW_objects.OTY_CODE = 'QUOTED_FEE'
1992: and OKL_CASH_FLOW_objects.id = okl_cash_flows.CFO_ID
1993: and okl_cash_flows.id = OKL_CASH_FLOW_LEVELS.CAF_ID
2000: and QUOTE.primary_quote = 'Y'
2001: and LAP.id = l_lease_app_id
2002: union
2003: select OKL_CASH_FLOW_LEVELS.amount amount
2004: from okl_lease_applications_b LAP, OKL_SERVICES_B SRV, OKL_CASH_FLOW_OBJECTS,
2005: okl_cash_flows, OKL_CASH_FLOW_LEVELS, OKL_LEASE_QUOTES_B QUOTE
2006: where OKL_CASH_FLOW_objects.OTY_CODE = 'QUOTED_SERVICE'
2007: and OKL_CASH_FLOW_objects.id = okl_cash_flows.CFO_ID
2008: and okl_cash_flows.id = OKL_CASH_FLOW_LEVELS.CAF_ID
2015: and QUOTE.primary_quote = 'Y'
2016: and LAP.id = l_lease_app_id
2017: union
2018: select OKL_CASH_FLOW_LEVELS.amount amount
2019: from okl_lease_applications_b LAP, OKL_INSURANCE_ESTIMATES_B INS, OKL_CASH_FLOW_OBJECTS,
2020: okl_cash_flows, OKL_CASH_FLOW_LEVELS, OKL_LEASE_QUOTES_B QUOTE
2021: where OKL_CASH_FLOW_objects.OTY_CODE = 'QUOTED_INSURANCE'
2022: and OKL_CASH_FLOW_objects.id = okl_cash_flows.CFO_ID
2023: and okl_cash_flows.id = OKL_CASH_FLOW_LEVELS.CAF_ID
2081: and CFO.SOURCE_TABLE = 'OKL_ASSETS_B';
2082:
2083: /*union
2084: select OKL_CASH_FLOW_LEVELS.start_date start_date
2085: from okl_lease_applications_b LAP, OKL_FEES_B FEE, OKL_CASH_FLOW_OBJECTS,
2086: okl_cash_flows, OKL_CASH_FLOW_LEVELS, OKL_LEASE_QUOTES_B QUOTE
2087: where OKL_CASH_FLOW_objects.OTY_CODE = 'QUOTED_FEE'
2088: and OKL_CASH_FLOW_objects.id = okl_cash_flows.CFO_ID
2089: and okl_cash_flows.id = OKL_CASH_FLOW_LEVELS.CAF_ID
2096: and QUOTE.primary_quote = 'Y'
2097: and LAP.id = l_lease_app_id
2098: union
2099: select OKL_CASH_FLOW_LEVELS.start_date start_date
2100: from okl_lease_applications_b LAP, OKL_SERVICES_B SRV, OKL_CASH_FLOW_OBJECTS,
2101: okl_cash_flows, OKL_CASH_FLOW_LEVELS, OKL_LEASE_QUOTES_B QUOTE
2102: where OKL_CASH_FLOW_objects.OTY_CODE = 'QUOTED_SERVICE'
2103: and OKL_CASH_FLOW_objects.id = okl_cash_flows.CFO_ID
2104: and okl_cash_flows.id = OKL_CASH_FLOW_LEVELS.CAF_ID
2111: and QUOTE.primary_quote = 'Y'
2112: and LAP.id = l_lease_app_id
2113: union
2114: select OKL_CASH_FLOW_LEVELS.start_date start_date
2115: from okl_lease_applications_b LAP, OKL_INSURANCE_ESTIMATES_B INS, OKL_CASH_FLOW_OBJECTS,
2116: okl_cash_flows, OKL_CASH_FLOW_LEVELS, OKL_LEASE_QUOTES_B QUOTE
2117: where OKL_CASH_FLOW_objects.OTY_CODE = 'QUOTED_INSURANCE'
2118: and OKL_CASH_FLOW_objects.id = okl_cash_flows.CFO_ID
2119: and okl_cash_flows.id = OKL_CASH_FLOW_LEVELS.CAF_ID
2295: order by LVL.start_date; --Bug # 9116306
2296:
2297: /*union
2298: select OKL_CASH_FLOW_LEVELS.start_date end_date
2299: from okl_lease_applications_b LAP, OKL_FEES_B FEE, OKL_CASH_FLOW_OBJECTS,
2300: okl_cash_flows, OKL_CASH_FLOW_LEVELS, OKL_LEASE_QUOTES_B QUOTE
2301: where OKL_CASH_FLOW_objects.OTY_CODE = 'QUOTED_FEE'
2302: and OKL_CASH_FLOW_objects.id = okl_cash_flows.CFO_ID
2303: and okl_cash_flows.id = OKL_CASH_FLOW_LEVELS.CAF_ID
2310: and QUOTE.primary_quote = 'Y'
2311: and LAP.id = l_lease_app_id
2312: union
2313: select OKL_CASH_FLOW_LEVELS.start_date end_date
2314: from okl_lease_applications_b LAP, OKL_SERVICES_B SRV, OKL_CASH_FLOW_OBJECTS,
2315: okl_cash_flows, OKL_CASH_FLOW_LEVELS, OKL_LEASE_QUOTES_B QUOTE
2316: where OKL_CASH_FLOW_objects.OTY_CODE = 'QUOTED_SERVICE'
2317: and OKL_CASH_FLOW_objects.id = okl_cash_flows.CFO_ID
2318: and okl_cash_flows.id = OKL_CASH_FLOW_LEVELS.CAF_ID
2325: and QUOTE.primary_quote = 'Y'
2326: and LAP.id = l_lease_app_id
2327: union
2328: select OKL_CASH_FLOW_LEVELS.start_date end_date
2329: from okl_lease_applications_b LAP, OKL_INSURANCE_ESTIMATES_B INS, OKL_CASH_FLOW_OBJECTS,
2330: okl_cash_flows, OKL_CASH_FLOW_LEVELS, OKL_LEASE_QUOTES_B QUOTE
2331: where OKL_CASH_FLOW_objects.OTY_CODE = 'QUOTED_INSURANCE'
2332: and OKL_CASH_FLOW_objects.id = okl_cash_flows.CFO_ID
2333: and okl_cash_flows.id = OKL_CASH_FLOW_LEVELS.CAF_ID
2340: and LAP.id = l_lease_app_id;*/
2341:
2342: CURSOR c_get_info IS
2343: SELECT OKL_CASH_FLOW_LEVELS.FQY_CODE FQY_CODE, QUOTE.TERM
2344: FROM OKL_LEASE_APPLICATIONS_B LAP, OKL_ASSETS_B ASSET, OKL_CASH_FLOW_OBJECTS,
2345: OKL_CASH_FLOWS, OKL_CASH_FLOW_LEVELS, OKL_LEASE_QUOTES_B QUOTE
2346: WHERE OKL_CASH_FLOW_OBJECTS.OTY_CODE = 'QUOTED_ASSET'
2347: AND OKL_CASH_FLOW_OBJECTS.ID = OKL_CASH_FLOWS.CFO_ID
2348: AND OKL_CASH_FLOWS.ID = OKL_CASH_FLOW_LEVELS.CAF_ID
2440: -- gboomina:Bug 7110500 :Modified cursor to use correct view
2441: CURSOR cFeeName IS
2442: /*
2443: SELECT STRM.STY_NAME FEE_NAME
2444: FROM OKL_LEASE_APPLICATIONS_B LAP, OKL_LEASE_QUOTES_B QUOTE,
2445: OKL_FEES_B FEES, OKL_STRM_TMPT_PRIMARY_UV STRM
2446: WHERE STRM.STY_ID = FEES.STREAM_TYPE_ID
2447: AND FEES.PARENT_OBJECT_CODE = 'LEASEQUOTE'
2448: AND FEES.PARENT_OBJECT_ID = QUOTE.ID
2451: AND QUOTE.PRIMARY_QUOTE = 'Y'
2452: AND LAP.ID = l_lease_app_id;
2453: */
2454: SELECT STRM.NAME FEE_NAME
2455: FROM OKL_LEASE_APPLICATIONS_B LAP, OKL_LEASE_QUOTES_B QUOTE,
2456: OKL_FEES_B FEES, OKL_STRM_TYPE_V STRM
2457: WHERE STRM.ID = FEES.STREAM_TYPE_ID
2458: AND FEES.PARENT_OBJECT_CODE = 'LEASEQUOTE'
2459: AND FEES.PARENT_OBJECT_ID = QUOTE.ID
2506: l_fee_type VARCHAR2(30);
2507:
2508: CURSOR cFeeType IS
2509: SELECT FEES.FEE_TYPE FEE_TYPE
2510: FROM OKL_LEASE_APPLICATIONS_B LAP, OKL_LEASE_QUOTES_B QUOTE, OKL_FEES_B FEES
2511: WHERE FEES.PARENT_OBJECT_CODE = 'LEASEQUOTE'
2512: AND FEES.PARENT_OBJECT_ID = QUOTE.ID
2513: AND QUOTE.PARENT_OBJECT_ID = LAP.ID
2514: AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
2557: l_fee_amount NUMBER;
2558:
2559: CURSOR cFeeAmount IS
2560: SELECT FEES.FEE_AMOUNT FEE_AMOUNT
2561: FROM OKL_LEASE_APPLICATIONS_B LAP, OKL_LEASE_QUOTES_B QUOTE, OKL_FEES_B FEES
2562: WHERE FEES.PARENT_OBJECT_CODE = 'LEASEQUOTE'
2563: AND FEES.PARENT_OBJECT_ID = QUOTE.ID
2564: AND QUOTE.PARENT_OBJECT_ID = LAP.ID
2565: AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
2607: l_fee_date DATE;
2608:
2609: CURSOR cFeeEffFrom IS
2610: SELECT FEES.EFFECTIVE_FROM EFFECTIVE_FROM
2611: FROM OKL_LEASE_APPLICATIONS_B LAP, OKL_LEASE_QUOTES_B QUOTE, OKL_FEES_B FEES
2612: WHERE FEES.PARENT_OBJECT_CODE = 'LEASEQUOTE'
2613: AND FEES.PARENT_OBJECT_ID = QUOTE.ID
2614: AND QUOTE.PARENT_OBJECT_ID = LAP.ID
2615: AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
2667:
2668: -- added for bug 6596860 --
2669: CURSOR l_adj_sum_csr(p_lease_app_id IN NUMBER) IS
2670: SELECT NVL(SUM(VALUE),0 )
2671: FROM OKL_LEASE_APPLICATIONS_B LAP, OKL_ASSETS_B ASSET,
2672: OKL_COST_ADJUSTMENTS_B ADJ, OKL_LEASE_QUOTES_B QUOTE
2673: WHERE ADJ.PARENT_OBJECT_CODE = 'ASSET'
2674: AND ADJ.PARENT_OBJECT_ID = ASSET.ID
2675: AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
2687: x_resultout := FND_API.G_RET_STS_SUCCESS;
2688:
2689: SELECT NVL(SUM(AST.OEC), 0)
2690: INTO ln_asset_cost
2691: FROM OKL_ASSETS_B AST, OKL_LEASE_QUOTES_B QUOTE, OKL_LEASE_APPLICATIONS_B LAP
2692: WHERE AST.PARENT_OBJECT_ID = QUOTE.ID
2693: AND AST.PARENT_OBJECT_CODE = 'LEASEQUOTE'
2694: AND QUOTE.PARENT_OBJECT_ID = LAP.ID
2695: AND QUOTE.PRIMARY_QUOTE = 'Y'
2698:
2699: /*SELECT NVL(SUM(AST_COMP.UNIT_COST * AST_COMP.NUMBER_OF_UNITS), 0)
2700: INTO ln_addon_cost
2701: FROM OKL_ASSETS_B AST, OKL_ASSET_COMPONENTS_B AST_COMP,
2702: OKL_LEASE_QUOTES_B QUOTE, OKL_LEASE_APPLICATIONS_B LAP
2703: WHERE AST_COMP.ASSET_ID = AST.ID
2704: AND AST_COMP.PRIMARY_COMPONENT = 'NO'
2705: AND AST.PARENT_OBJECT_ID = QUOTE.ID
2706: AND AST.PARENT_OBJECT_CODE = 'LEASEQUOTE'
2710: AND LAP.ID = l_lease_app_id;*/
2711:
2712: SELECT NVL(SUM(FEES.FEE_AMOUNT), 0)
2713: INTO ln_fee_cost
2714: FROM OKL_LEASE_APPLICATIONS_B LAP, OKL_LEASE_QUOTES_B QUOTE, OKL_FEES_B FEES
2715: WHERE FEES.PARENT_OBJECT_CODE = 'LEASEQUOTE'
2716: AND FEES.PARENT_OBJECT_ID = QUOTE.ID
2717: AND FEES.FEE_TYPE IN ('ROLLOVER', 'FINANCED', 'CAPITALIZED') --Bug 6697231 Added capitalized fee
2718: AND QUOTE.PARENT_OBJECT_ID = LAP.ID
2772: BEGIN
2773: mo_global.set_policy_context('S',OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_org_id); --Bug#7030452
2774: SELECT NVL(SUM(ADJ.VALUE), 0)
2775: INTO ln_down_payment_amount
2776: FROM OKL_LEASE_APPLICATIONS_B LAP, OKL_ASSETS_B ASSET,
2777: OKL_COST_ADJUSTMENTS_B ADJ, OKL_LEASE_QUOTES_B QUOTE
2778: WHERE ADJ.ADJUSTMENT_SOURCE_TYPE IN ('DOWN_PAYMENT', 'TRADEIN')
2779: AND ADJ.PARENT_OBJECT_CODE = 'ASSET'
2780: AND ADJ.PARENT_OBJECT_ID = ASSET.ID
2810: l_security_deposit VARCHAR2(120);
2811:
2812: CURSOR cSecDeposit IS
2813: SELECT FEES.FEE_AMOUNT FEE_AMOUNT
2814: FROM OKL_LEASE_APPLICATIONS_B LAP, OKL_LEASE_QUOTES_B QUOTE, OKL_FEES_B FEES
2815: WHERE FEES.PARENT_OBJECT_CODE = 'LEASEQUOTE'
2816: AND FEES.PARENT_OBJECT_ID = QUOTE.ID
2817: AND QUOTE.PARENT_OBJECT_ID = LAP.ID
2818: AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
2871: SELECT SUM(TRXD.TAX_AMT)
2872: INTO l_billed_tax
2873: FROM
2874: OKL_TAX_SOURCES TXS, OKL_TAX_TRX_DETAILS TRXD, OKL_LEASE_QUOTES_B LSQ,
2875: OKL_LEASE_APPLICATIONS_B LAP
2876: WHERE
2877: TRXD.TXS_ID = TXS.ID
2878: AND TRXD.BILLED_YN = 'Y'
2879: AND TXS.TRX_ID = LSQ.ID
2918: lat.cust_credit_classification
2919: FROM
2920: okl_leaseapp_templates lat,
2921: okl_leaseapp_templ_versions_b latv,
2922: okl_lease_applications_b lap
2923: WHERE
2924: lap.leaseapp_template_id = latv.id
2925: AND latv.version_status = 'ACTIVE'
2926: AND latv.valid_from <= lap.valid_from