[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
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
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
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
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
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
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;
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
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;
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;
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
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'
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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'
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'
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'
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
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'
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'
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
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
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'
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
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