[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT KHR.CONTRACT_NUMBER
INTO l_credit_line_number
FROM OKC_K_HEADERS_B KHR, OKL_LEASE_APPLICATIONS_B LAP
WHERE KHR.SCS_CODE = 'CREDITLINE_CONTRACT'
AND LAP.CREDIT_LINE_ID = KHR.ID
AND LAP.ID = l_lease_app_id;
SELECT KHR.END_DATE
INTO l_credit_line_expiration_date
FROM OKC_K_HEADERS_B KHR,
OKL_LEASE_APPLICATIONS_B LAP
WHERE KHR.SCS_CODE = 'CREDITLINE_CONTRACT'
AND LAP.CREDIT_LINE_ID = KHR.ID
AND LAP.ID = l_lease_app_id;
SELECT CURRENCY_CODE
INTO l_currency
FROM OKL_LEASE_APPLICATIONS_B
WHERE ID = l_lease_app_id;
SELECT REP.SALESREP_NUMBER
INTO l_sales_rep
FROM JTF_RS_SALESREPS REP,
OKL_LEASE_APPLICATIONS_B LAP
WHERE LAP.SALES_REP_ID = REP.SALESREP_ID
AND REP.ORG_ID = LAP.ORG_ID
AND LAP.ID = l_lease_app_id;
SELECT PO.SEGMENT1
INTO l_program_vendor
FROM OKL_LEASE_APPLICATIONS_B LAP, OKC_K_HEADERS_B CHR,
OKC_K_PARTY_ROLES_B CPL, PO_VENDORS PO
WHERE LAP.PROGRAM_AGREEMENT_ID = CHR.ID
AND CHR.ID = CPL.DNZ_CHR_ID
AND CPL.JTOT_OBJECT1_CODE = 'OKL_VENDOR'
AND CPL.OBJECT1_ID1 = PO.VENDOR_ID
AND LAP.ID = l_lease_app_id;
SELECT KHR.CONTRACT_NUMBER
INTO l_program_agreement_number
FROM OKC_K_HEADERS_B KHR, OKL_LEASE_APPLICATIONS_B LAP
WHERE KHR.SCS_CODE = 'PROGRAM'
AND LAP.PROGRAM_AGREEMENT_ID = KHR.ID
AND LAP.ID = l_lease_app_id;
SELECT QUOTE.EXPECTED_START_DATE
INTO l_expected_start_date
FROM OKL_LEASE_QUOTES_B QUOTE,
OKL_LEASE_APPLICATIONS_B LAP
WHERE QUOTE.PRIMARY_QUOTE = 'Y'
AND LAP.ID = QUOTE.PARENT_OBJECT_ID
AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
AND LAP.ID = l_lease_app_id;
SELECT QUOTE.EXPECTED_DELIVERY_DATE
INTO l_expected_delivery_date
FROM OKL_LEASE_QUOTES_B QUOTE, OKL_LEASE_APPLICATIONS_B LAP
WHERE QUOTE.PRIMARY_QUOTE = 'Y'
AND LAP.ID = QUOTE.PARENT_OBJECT_ID
AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
AND LAP.ID = l_lease_app_id;
SELECT QUOTE.EXPECTED_FUNDING_DATE
INTO l_expected_funding_date
FROM OKL_LEASE_QUOTES_B QUOTE, OKL_LEASE_APPLICATIONS_B LAP
WHERE QUOTE.PRIMARY_QUOTE = 'Y'
AND LAP.ID = QUOTE.PARENT_OBJECT_ID
AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
AND LAP.ID = l_lease_app_id;
SELECT TEMPLATES.NAME
INTO l_lease_application_template
FROM OKL_LEASEAPP_TEMPLATES TEMPLATES, OKL_LEASE_APPLICATIONS_B LAP
WHERE LAP. LEASEAPP_TEMPLATE_ID = TEMPLATES.ID
AND LAP.ID = l_lease_app_id;
SELECT ORG.NAME
INTO l_org_unit
FROM HR_ALL_ORGANIZATION_UNITS ORG, OKL_LEASE_APPLICATIONS_B LAP
WHERE ORG.ORGANIZATION_ID = LAP.ORG_ID
AND LAP.ID = l_lease_app_id;
SELECT PARTY_SITES.PARTY_SITE_NUMBER
INTO l_prospect_address
FROM HZ_PARTY_SITES PARTY_SITES, OKL_LEASE_APPLICATIONS_B LAP
WHERE PARTY_SITES.PARTY_SITE_ID = LAP.PROSPECT_ADDRESS_ID
AND LAP.ID = l_lease_app_id;
SELECT QUOTE.TERM
INTO l_term_of_deal
FROM OKL_LEASE_QUOTES_B QUOTE, OKL_LEASE_APPLICATIONS_B LAP
WHERE QUOTE.PRIMARY_QUOTE = 'Y'
AND QUOTE.PARENT_OBJECT_ID = LAP.ID
AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
AND LAP.ID = l_lease_app_id;
SELECT PRODUCT.NAME
INTO l_financial_product
FROM OKL_PRODUCTS PRODUCT, OKL_LEASE_APPLICATIONS_B LAP, OKL_LEASE_QUOTES_B QUOTE
WHERE PRODUCT.ID = QUOTE.PRODUCT_ID
AND QUOTE.PRIMARY_QUOTE = 'Y'
AND QUOTE.PARENT_OBJECT_ID = LAP.ID
AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
AND LAP.ID = l_lease_app_id;
SELECT ITEM.CONCATENATED_SEGMENTS CONCATENATED_SEGMENTS, ASSET.ID
FROM MTL_SYSTEM_ITEMS_KFV ITEM, OKL_ASSET_COMPONENTS_B ASSET_COMP,
OKL_LEASE_APPLICATIONS_B LAP, OKL_ASSETS_B ASSET,
OKL_LEASE_QUOTES_B QUOTE
WHERE ITEM.INVENTORY_ITEM_ID = ASSET_COMP.INV_ITEM_ID
--Bug 7030452 :use inventory org id in the following condition
-- AND ITEM.ORGANIZATION_ID = LAP.ORG_ID -- ssdeshpa Bug # 6689249 added
AND ITEM.ORGANIZATION_ID = LAP.INV_ORG_ID
--Bug 7030452 :End
AND ASSET_COMP.ASSET_ID = ASSET.ID
AND ASSET_COMP.PRIMARY_COMPONENT = 'YES'
AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
AND QUOTE.PARENT_OBJECT_ID = LAP.ID
AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
AND QUOTE.PRIMARY_QUOTE = 'Y'
AND LAP.ID = l_lease_app_id;
SELECT ASSET_TL.SHORT_DESCRIPTION DESCRIPTION
-- ssdeshpa Bug#6689249 end
FROM OKL_ASSETS_TL ASSET_TL, OKL_ASSETS_B ASSET,
OKL_LEASE_APPLICATIONS_B LAP, OKL_LEASE_QUOTES_B QUOTE
WHERE ASSET_TL.ID = ASSET.ID
AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
AND QUOTE.PARENT_OBJECT_ID = LAP.ID
AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
AND QUOTE.PRIMARY_QUOTE = 'Y'
AND ASSET_TL.LANGUAGE = USERENV('LANG')
AND LAP.ID = l_lease_app_id;
SELECT VENDOR.VENDOR_NAME SUPPLIER
-- ssdeshpa Bug# 6689249 end
FROM PO_VENDORS VENDOR, OKL_ASSET_COMPONENTS_B ASSET_COMP,
OKL_LEASE_APPLICATIONS_B LAP, OKL_ASSETS_B ASSET,
OKL_LEASE_QUOTES_B QUOTE
WHERE VENDOR.VENDOR_ID = ASSET_COMP.SUPPLIER_ID
AND ASSET_COMP.ASSET_ID = ASSET.ID
AND ASSET_COMP.PRIMARY_COMPONENT = 'YES'
AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
AND QUOTE.PARENT_OBJECT_ID = LAP.ID
AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
AND QUOTE.PRIMARY_QUOTE = 'Y'
AND LAP.ID = l_lease_app_id;
SELECT ASSET_COMP.MODEL_NUMBER MODEL_NUMBER
FROM OKL_ASSET_COMPONENTS_B ASSET_COMP, OKL_LEASE_APPLICATIONS_B LAP,
OKL_ASSETS_B ASSET, OKL_LEASE_QUOTES_B QUOTE
WHERE ASSET_COMP.ASSET_ID = ASSET.ID
AND ASSET_COMP.PRIMARY_COMPONENT = 'YES'
AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
AND QUOTE.PARENT_OBJECT_ID = LAP.ID
AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
AND QUOTE.PRIMARY_QUOTE = 'Y'
AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
AND LAP.ID = l_lease_app_id;
SELECT ASSET_COMP.MANUFACTURER_NAME MANUFACTURER_NAME
FROM OKL_ASSET_COMPONENTS_B ASSET_COMP, OKL_LEASE_APPLICATIONS_B LAP,
OKL_ASSETS_B ASSET, OKL_LEASE_QUOTES_B QUOTE
WHERE ASSET_COMP.ASSET_ID = ASSET.ID
AND ASSET_COMP.PRIMARY_COMPONENT = 'YES'
AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
AND QUOTE.PARENT_OBJECT_ID = LAP.ID
AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
AND QUOTE.PRIMARY_QUOTE = 'Y'
AND LAP.ID = l_lease_app_id;
SELECT ASSET_COMP.YEAR_MANUFACTURED YEAR_MANUFACTURED
FROM OKL_ASSET_COMPONENTS_B ASSET_COMP, OKL_LEASE_APPLICATIONS_B LAP,
OKL_ASSETS_B ASSET, OKL_LEASE_QUOTES_B QUOTE
WHERE ASSET_COMP.ASSET_ID = ASSET.ID
AND ASSET_COMP.PRIMARY_COMPONENT = 'YES'
AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
AND QUOTE.PARENT_OBJECT_ID = LAP.ID
AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
AND QUOTE.PRIMARY_QUOTE = 'Y'
AND LAP.ID = l_lease_app_id;
SELECT ASSET_COMP.NUMBER_OF_UNITS NUMBER_OF_UNITS
FROM OKL_ASSET_COMPONENTS_B ASSET_COMP, OKL_LEASE_APPLICATIONS_B LAP,
OKL_ASSETS_B ASSET, OKL_LEASE_QUOTES_B QUOTE
WHERE ASSET_COMP.ASSET_ID = ASSET.ID
AND ASSET_COMP.PRIMARY_COMPONENT = 'YES'
AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
AND QUOTE.PARENT_OBJECT_ID = LAP.ID
AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
AND QUOTE.PRIMARY_QUOTE = 'Y'
AND LAP.ID = l_lease_app_id;
SELECT ASSET_COMP.UNIT_COST UNIT_COST
FROM OKL_ASSET_COMPONENTS_B ASSET_COMP, OKL_LEASE_APPLICATIONS_B LAP,
OKL_ASSETS_B ASSET, OKL_LEASE_QUOTES_B QUOTE
WHERE ASSET_COMP.ASSET_ID = ASSET.ID
AND ASSET_COMP.PRIMARY_COMPONENT = 'YES'
AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
AND QUOTE.PARENT_OBJECT_ID = LAP.ID
AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
AND QUOTE.PRIMARY_QUOTE = 'Y'
AND LAP.ID = l_lease_app_id;
SELECT PARTY_SITE.PARTY_SITE_NUMBER PARTY_SITE_NUMBER
FROM HZ_PARTY_SITES PARTY_SITE, OKL_LEASE_APPLICATIONS_B LAP,
OKL_ASSETS_B ASSET, OKL_LEASE_QUOTES_B QUOTE
WHERE PARTY_SITE.PARTY_SITE_ID = ASSET.INSTALL_SITE_ID
AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
AND QUOTE.PARENT_OBJECT_ID = LAP.ID
AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
AND QUOTE.PRIMARY_QUOTE = 'Y'
AND LAP.ID = l_lease_app_id;
SELECT QUOTE.USAGE_CATEGORY --QUOTE.USAGE_INDUSTRY_CLASS
INTO l_usage_of_equipment
FROM OKL_LEASE_APPLICATIONS_B LAP, OKL_LEASE_QUOTES_B QUOTE
WHERE QUOTE.PARENT_OBJECT_ID = LAP.ID
AND QUOTE.PRIMARY_QUOTE = 'Y'
AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
AND LAP.ID = l_lease_app_id;
SELECT QUOTE.USAGE_INDUSTRY_CODE
INTO l_usage_industry
FROM OKL_LEASE_APPLICATIONS_B LAP, OKL_LEASE_QUOTES_B QUOTE
WHERE QUOTE.PARENT_OBJECT_ID = LAP.ID
AND QUOTE.PRIMARY_QUOTE = 'Y'
AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
AND LAP.ID = l_lease_app_id;
SELECT INDC.MEANING ||' - '||IND.MEANING USAGE_INDUSTRY
INTO l_usage_industry
FROM OKL_LEASE_APPLICATIONS_B LAP,AR_LOOKUPS INDC, AR_LOOKUPS IND
WHERE INDC.LOOKUP_TYPE(+)='SIC_CODE_TYPE'
AND INDC.LOOKUP_CODE(+)=LAP.INDUSTRY_CLASS
AND IND.LOOKUP_TYPE(+)=LAP.INDUSTRY_CLASS
AND IND.LOOKUP_CODE(+)=LAP.INDUSTRY_CODE
AND LAP.id= l_lease_app_id;
SELECT QUOTE.USAGE_CATEGORY
INTO l_usage_category
FROM OKL_LEASE_APPLICATIONS_B LAP, OKL_LEASE_QUOTES_B QUOTE
WHERE QUOTE.PARENT_OBJECT_ID = LAP.ID
AND QUOTE.PRIMARY_QUOTE = 'Y'
AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
AND LAP.ID = l_lease_app_id;
SELECT QUOTE.USAGE_AMOUNT
INTO l_usage_amount
FROM OKL_LEASE_APPLICATIONS_B LAP, OKL_LEASE_QUOTES_B QUOTE
WHERE QUOTE.PARENT_OBJECT_ID = LAP.ID
AND QUOTE.PRIMARY_QUOTE = 'Y'
AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
AND LAP.ID = l_lease_app_id;
SELECT LOP.USAGE_AMOUNT
INTO l_usage_amount
FROM OKL_LEASE_APPLICATIONS_B LAP, OKL_LEASE_OPPORTUNITIES_B LOP
WHERE LOP.ID= LAP.LEASE_OPPORTUNITY_ID
AND LAP.ID = l_lease_app_id;
SELECT ITEM.CONCATENATED_SEGMENTS ADD_ON_ITEM
FROM MTL_SYSTEM_ITEMS_KFV ITEM, OKL_ASSET_COMPONENTS_B ASSET_COMP
WHERE ITEM.INVENTORY_ITEM_ID = ASSET_COMP.INV_ITEM_ID
AND ITEM.ORGANIZATION_ID = OKL_SYSTEM_PARAMS_ALL_PUB.get_system_param_value(OKL_SYSTEM_PARAMS_ALL_PUB.G_ITEM_INV_ORG_ID)
AND ASSET_COMP.ASSET_ID = l_asset_id
AND ASSET_COMP.PRIMARY_COMPONENT = 'NO';
SELECT ITEM.CONCATENATED_SEGMENTS ADD_ON_ITEM
FROM MTL_SYSTEM_ITEMS_KFV ITEM, OKL_ASSET_COMPONENTS_B ASSET_COMP
WHERE ITEM.INVENTORY_ITEM_ID = ASSET_COMP.INV_ITEM_ID
AND ITEM.ORGANIZATION_ID = OKL_SYSTEM_PARAMS_ALL_PUB.get_system_param_value(OKL_SYSTEM_PARAMS_ALL_PUB.G_ITEM_INV_ORG_ID)
AND ASSET_COMP.ASSET_ID = l_asset_id
AND ASSET_COMP.PRIMARY_COMPONENT = 'NO';
SELECT VENDOR.VENDOR_NAME ITEM_SUPPLIER
FROM PO_VENDORS VENDOR, OKL_ASSET_COMPONENTS_B ASSET_COMP
WHERE VENDOR.VENDOR_ID = ASSET_COMP.SUPPLIER_ID
AND ASSET_COMP.ASSET_ID = l_asset_id
AND ASSET_COMP.PRIMARY_COMPONENT = 'NO';
SELECT ASSET_COMP.MODEL_NUMBER MODEL_NUMBER
FROM OKL_ASSET_COMPONENTS_B ASSET_COMP
WHERE ASSET_COMP.ASSET_ID = l_asset_id
AND ASSET_COMP.PRIMARY_COMPONENT = 'NO';
SELECT ASSET_COMP.MANUFACTURER_NAME MANUFACTURER_NAME
FROM OKL_ASSET_COMPONENTS_B ASSET_COMP
WHERE ASSET_COMP.ASSET_ID = l_asset_id
AND ASSET_COMP.PRIMARY_COMPONENT = 'NO';
SELECT ASSET_COMP.UNIT_COST UNIT_COST
FROM OKL_ASSET_COMPONENTS_B ASSET_COMP
WHERE ASSET_COMP.ASSET_ID = l_asset_id
AND ASSET_COMP.PRIMARY_COMPONENT = 'NO';
SELECT NVL(ASSET.END_OF_TERM_VALUE,ASSET.END_OF_TERM_VALUE_DEFAULT) END_OF_TERM_VALUE
FROM OKL_LEASE_APPLICATIONS_B LAP, OKL_ASSETS_B ASSET, OKL_LEASE_QUOTES_B QUOTE
WHERE ASSET.PARENT_OBJECT_ID = QUOTE.ID
AND QUOTE.PARENT_OBJECT_ID = LAP.ID
AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
AND QUOTE.PRIMARY_QUOTE = 'Y'
AND LAP.ID = l_lease_app_id;
SELECT NVL(SUM(ADJ.VALUE), 0)
INTO l_down_payment_amount
FROM OKL_LEASE_APPLICATIONS_B LAP, OKL_ASSETS_B ASSET,
OKL_COST_ADJUSTMENTS_B ADJ, OKL_LEASE_QUOTES_B QUOTE
WHERE ADJ.ADJUSTMENT_SOURCE_TYPE = 'DOWN_PAYMENT'
AND ADJ.PARENT_OBJECT_CODE = 'ASSET'
AND ADJ.PARENT_OBJECT_ID = ASSET.ID
AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
AND QUOTE.PARENT_OBJECT_ID = LAP.ID
AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
AND QUOTE.PRIMARY_QUOTE = 'Y'
AND LAP.ID = l_lease_app_id;
SELECT SUM(DECODE(ADJ.VALUE, NULL, ADJ.DEFAULT_SUBSIDY_AMOUNT, ADJ.VALUE)) SUBSIDY_AMOUNT
INTO l_subsidy_amount
FROM OKL_LEASE_APPLICATIONS_B LAP, OKL_ASSETS_B ASSET,
OKL_COST_ADJUSTMENTS_B ADJ, OKL_LEASE_QUOTES_B QUOTE
WHERE ADJ.ADJUSTMENT_SOURCE_TYPE = 'SUBSIDY'
AND ADJ.PARENT_OBJECT_CODE = 'ASSET'
AND ADJ.PARENT_OBJECT_ID = ASSET.ID
AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
AND QUOTE.PARENT_OBJECT_ID = LAP.ID
AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
AND QUOTE.PRIMARY_QUOTE = 'Y'
AND LAP.ID = l_lease_app_id;
SELECT NVL(SUM(ADJ.VALUE), 0)
INTO l_trade_in_amount
FROM OKL_LEASE_APPLICATIONS_B LAP, OKL_ASSETS_B ASSET,
OKL_COST_ADJUSTMENTS_B ADJ, OKL_LEASE_QUOTES_B QUOTE
WHERE ADJ.ADJUSTMENT_SOURCE_TYPE = 'TRADEIN'
AND ADJ.PARENT_OBJECT_CODE = 'ASSET'
AND ADJ.PARENT_OBJECT_ID = ASSET.ID
AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
AND QUOTE.PARENT_OBJECT_ID = LAP.ID
AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
AND QUOTE.PRIMARY_QUOTE = 'Y'
AND LAP.ID = l_lease_app_id;
SELECT ASSET.ASSET_NUMBER
FROM OKL_LEASE_APPLICATIONS_B LAP, OKL_ASSETS_B ASSET,
OKL_LEASE_QUOTES_B QUOTE, OKL_COST_ADJUSTMENTS_B ADJ
WHERE ADJ.ADJUSTMENT_SOURCE_TYPE = 'TRADEIN'
AND ADJ.PARENT_OBJECT_CODE = 'ASSET'
AND ADJ.PARENT_OBJECT_ID = ASSET.ID
AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
AND QUOTE.PARENT_OBJECT_ID = LAP.ID
AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
AND QUOTE.PRIMARY_QUOTE = 'Y'
AND LAP.ID = l_lease_app_id;
select FND.MEANING frequency
from OKL_CASH_FLOW_OBJECTS CFO, OKL_CASH_FLOWS CFL,
OKL_CASH_FLOW_LEVELS LVL, FND_LOOKUPS FND
where CFO.OTY_CODE = 'QUOTED_ASSET'
and CFO.ID = CFL.CFO_ID
and CFL.ID = LVL.CAF_ID
and CFO.SOURCE_ID = l_asset_id
and CFO.SOURCE_TABLE = 'OKL_ASSETS_B'
AND LVL.FQY_CODE = FND.LOOKUP_CODE
AND FND.LOOKUP_TYPE = 'OKL_FREQUENCY';
select OKL_CASH_FLOW_LEVELS.FQY_CODE FQY_CODE
from okl_lease_applications_b LAP, OKL_FEES_B FEE, OKL_CASH_FLOW_OBJECTS,
okl_cash_flows, OKL_CASH_FLOW_LEVELS, OKL_LEASE_QUOTES_B QUOTE
where OKL_CASH_FLOW_objects.OTY_CODE = 'QUOTED_FEE'
and OKL_CASH_FLOW_objects.id = okl_cash_flows.CFO_ID
and okl_cash_flows.id = OKL_CASH_FLOW_LEVELS.CAF_ID
and OKL_CASH_FLOW_OBJECTS.source_id = FEE.id
and OKL_CASH_FLOW_OBJECTS.source_table = 'OKL_ASSETS_B'
and FEE.parent_object_id = QUOTE.id
and QUOTE.parent_object_id = LAP.id
and FEE.parent_object_code = 'LEASEQUOTE'
and QUOTE.parent_object_code = 'LEASEAPP'
and QUOTE.primary_quote = 'Y'
and LAP.id = l_lease_app_id
UNION
select OKL_CASH_FLOW_LEVELS.FQY_CODE FQY_CODE
from okl_lease_applications_b LAP, OKL_SERVICES_B SRV, OKL_CASH_FLOW_OBJECTS,
okl_cash_flows, OKL_CASH_FLOW_LEVELS, OKL_LEASE_QUOTES_B QUOTE
where OKL_CASH_FLOW_objects.OTY_CODE = 'QUOTED_SERVICE'
and OKL_CASH_FLOW_objects.id = okl_cash_flows.CFO_ID
and okl_cash_flows.id = OKL_CASH_FLOW_LEVELS.CAF_ID
and OKL_CASH_FLOW_OBJECTS.source_id = SRV.id
and OKL_CASH_FLOW_OBJECTS.source_table = 'OKL_ASSETS_B'
and SRV.parent_object_id = QUOTE.id
and QUOTE.parent_object_id = LAP.id
and SRV.parent_object_code = 'LEASEQUOTE'
and QUOTE.parent_object_code = 'LEASEAPP'
and QUOTE.primary_quote = 'Y'
and LAP.id = l_lease_app_id
UNION
select OKL_CASH_FLOW_LEVELS.FQY_CODE FQY_CODE
from okl_lease_applications_b LAP, OKL_INSURANCE_ESTIMATES_B INS, OKL_CASH_FLOW_OBJECTS,
okl_cash_flows, OKL_CASH_FLOW_LEVELS, OKL_LEASE_QUOTES_B QUOTE
where OKL_CASH_FLOW_objects.OTY_CODE = 'QUOTED_INSURANCE'
and OKL_CASH_FLOW_objects.id = okl_cash_flows.CFO_ID
and okl_cash_flows.id = OKL_CASH_FLOW_LEVELS.CAF_ID
and OKL_CASH_FLOW_OBJECTS.source_id = INS.id
and OKL_CASH_FLOW_OBJECTS.source_table = 'OKL_INSURANCE_ESTIMATES_B'
and INS.LEASE_QUOTE_ID = QUOTE.id
and QUOTE.parent_object_id = LAP.id
and QUOTE.parent_object_code = 'LEASEAPP'
and QUOTE.primary_quote = 'Y'
and LAP.id = l_lease_app_id;*/
select FND.MEANING Arrears
from OKL_CASH_FLOW_OBJECTS CFO, OKL_CASH_FLOWS CFL, FND_LOOKUPS FND
where CFO.OTY_CODE = 'QUOTED_ASSET'
and CFO.id = CFL.CFO_ID
--Bug 7030452 :Hardcoded value '23' is removed
-- and CFO.source_id = 23
and CFO.source_id = l_asset_id
--Bug 7030452 :End
and CFO.source_table = 'OKL_ASSETS_B'
AND CFL.due_arrears_yn = FND.LOOKUP_CODE
AND FND.LOOKUP_TYPE = 'OKL_YES_NO';
select OKL_CASH_FLOWS.due_arrears_yn due_arrears_yn
from okl_lease_applications_b LAP, OKL_FEES_B FEE, OKL_CASH_FLOW_OBJECTS,
okl_cash_flows, OKL_LEASE_QUOTES_B QUOTE
where OKL_CASH_FLOW_objects.OTY_CODE = 'QUOTED_FEE'
and OKL_CASH_FLOW_objects.id = okl_cash_flows.CFO_ID
and OKL_CASH_FLOW_OBJECTS.source_id = FEE.id
and OKL_CASH_FLOW_OBJECTS.source_table = 'OKL_FEES_B'
and FEE.parent_object_id = QUOTE.id
and QUOTE.parent_object_id = LAP.id
and FEE.parent_object_code = 'LEASEQUOTE'
and QUOTE.parent_object_code = 'LEASEAPP'
and QUOTE.primary_quote = 'Y'
and LAP.id = l_lease_app_id
union
select OKL_CASH_FLOWS.due_arrears_yn due_arrears_yn
from okl_lease_applications_b LAP, OKL_SERVICES_B SRV, OKL_CASH_FLOW_OBJECTS,
okl_cash_flows, OKL_LEASE_QUOTES_B QUOTE
where OKL_CASH_FLOW_objects.OTY_CODE = 'QUOTED_SERVICE'
and OKL_CASH_FLOW_objects.id = okl_cash_flows.CFO_ID
and OKL_CASH_FLOW_OBJECTS.source_id = SRV.id
and OKL_CASH_FLOW_OBJECTS.source_table = 'OKL_SERVICES_B'
and SRV.parent_object_id = QUOTE.id
and QUOTE.parent_object_id = LAP.id
and SRV.parent_object_code = 'LEASEQUOTE'
and QUOTE.parent_object_code = 'LEASEAPP'
and QUOTE.primary_quote = 'Y'
and LAP.id = l_lease_app_id
union
select OKL_CASH_FLOWS.due_arrears_yn due_arrears_yn
from okl_lease_applications_b LAP, OKL_INSURANCE_ESTIMATES_B INS, OKL_CASH_FLOW_OBJECTS,
okl_cash_flows, OKL_LEASE_QUOTES_B QUOTE
where OKL_CASH_FLOW_objects.OTY_CODE = 'QUOTED_INSURANCE'
and OKL_CASH_FLOW_objects.id = okl_cash_flows.CFO_ID
and OKL_CASH_FLOW_OBJECTS.source_id = INS.id
and OKL_CASH_FLOW_OBJECTS.source_table = 'OKL_INSURANCE_ESTIMATES_B'
and INS.LEASE_QUOTE_ID = QUOTE.id
and QUOTE.parent_object_id = LAP.id
and QUOTE.parent_object_code = 'LEASEAPP'
and QUOTE.primary_quote = 'Y'
and LAP.id = l_lease_app_id;*/
select LVL.NUMBER_OF_PERIODS
from OKL_CASH_FLOW_OBJECTS CFO, OKL_CASH_FLOWS CFL, OKL_CASH_FLOW_LEVELS LVL
where CFO.OTY_CODE = 'QUOTED_ASSET'
and CFO.ID = CFL.CFO_ID
and CFL.ID = LVL.CAF_ID
and CFO.SOURCE_ID = l_asset_id
and CFO.SOURCE_TABLE = 'OKL_ASSETS_B';
select OKL_CASH_FLOW_LEVELS.number_of_periods number_of_periods
from okl_lease_applications_b LAP, OKL_FEES_B FEE, OKL_CASH_FLOW_OBJECTS,
okl_cash_flows, OKL_CASH_FLOW_LEVELS, OKL_LEASE_QUOTES_B QUOTE
where OKL_CASH_FLOW_objects.OTY_CODE = 'QUOTED_FEE'
and OKL_CASH_FLOW_objects.id = okl_cash_flows.CFO_ID
and okl_cash_flows.id = OKL_CASH_FLOW_LEVELS.CAF_ID
and OKL_CASH_FLOW_OBJECTS.source_id = FEE.id
and OKL_CASH_FLOW_OBJECTS.source_table = 'OKL_FEES_B'
and FEE.parent_object_id = QUOTE.id
and QUOTE.parent_object_id = LAP.id
and FEE.parent_object_code = 'LEASEQUOTE'
and QUOTE.parent_object_code = 'LEASEAPP'
and QUOTE.primary_quote = 'Y'
and LAP.id = l_lease_app_id
union
select OKL_CASH_FLOW_LEVELS.number_of_periods number_of_periods
from okl_lease_applications_b LAP, OKL_SERVICES_B SRV, OKL_CASH_FLOW_OBJECTS,
okl_cash_flows, OKL_CASH_FLOW_LEVELS, OKL_LEASE_QUOTES_B QUOTE
where OKL_CASH_FLOW_objects.OTY_CODE = 'QUOTED_SERVICE'
and OKL_CASH_FLOW_objects.id = okl_cash_flows.CFO_ID
and okl_cash_flows.id = OKL_CASH_FLOW_LEVELS.CAF_ID
and OKL_CASH_FLOW_OBJECTS.source_id = SRV.id
and OKL_CASH_FLOW_OBJECTS.source_table = 'OKL_SERVICES_B'
and SRV.parent_object_id = QUOTE.id
and QUOTE.parent_object_id = LAP.id
and SRV.parent_object_code = 'LEASEQUOTE'
and QUOTE.parent_object_code = 'LEASEAPP'
and QUOTE.primary_quote = 'Y'
and LAP.id = l_lease_app_id
union
select OKL_CASH_FLOW_LEVELS.number_of_periods number_of_periods
from okl_lease_applications_b LAP, OKL_INSURANCE_ESTIMATES_B INS, OKL_CASH_FLOW_OBJECTS,
okl_cash_flows, OKL_CASH_FLOW_LEVELS, OKL_LEASE_QUOTES_B QUOTE
where OKL_CASH_FLOW_objects.OTY_CODE = 'QUOTED_INSURANCE'
and OKL_CASH_FLOW_objects.id = okl_cash_flows.CFO_ID
and okl_cash_flows.id = OKL_CASH_FLOW_LEVELS.CAF_ID
and OKL_CASH_FLOW_OBJECTS.source_id = INS.id
and OKL_CASH_FLOW_OBJECTS.source_table = 'OKL_INSURANCE_ESTIMATES_B'
and INS.LEASE_QUOTE_ID = QUOTE.id
and QUOTE.parent_object_id = LAP.id
and QUOTE.parent_object_code = 'LEASEAPP'
and QUOTE.primary_quote = 'Y'
and LAP.id = l_lease_app_id;*/
select LVL.AMOUNT
from OKL_CASH_FLOW_OBJECTS CFO, OKL_CASH_FLOWS CFL, OKL_CASH_FLOW_LEVELS LVL
where CFO.OTY_CODE = 'QUOTED_ASSET'
and CFO.ID = CFL.CFO_ID
and CFL.ID = LVL.CAF_ID
and CFO.SOURCE_ID = l_asset_id
and CFO.SOURCE_TABLE = 'OKL_ASSETS_B';
select OKL_CASH_FLOW_LEVELS.amount amount
from okl_lease_applications_b LAP, OKL_FEES_B FEE, OKL_CASH_FLOW_OBJECTS,
okl_cash_flows, OKL_CASH_FLOW_LEVELS, OKL_LEASE_QUOTES_B QUOTE
where OKL_CASH_FLOW_objects.OTY_CODE = 'QUOTED_FEE'
and OKL_CASH_FLOW_objects.id = okl_cash_flows.CFO_ID
and okl_cash_flows.id = OKL_CASH_FLOW_LEVELS.CAF_ID
and OKL_CASH_FLOW_OBJECTS.source_id = FEE.id
and OKL_CASH_FLOW_OBJECTS.source_table = 'OKL_FEES_B'
and FEE.parent_object_id = QUOTE.id
and QUOTE.parent_object_id = LAP.id
and FEE.parent_object_code = 'LEASEQUOTE'
and QUOTE.parent_object_code = 'LEASEAPP'
and QUOTE.primary_quote = 'Y'
and LAP.id = l_lease_app_id
union
select OKL_CASH_FLOW_LEVELS.amount amount
from okl_lease_applications_b LAP, OKL_SERVICES_B SRV, OKL_CASH_FLOW_OBJECTS,
okl_cash_flows, OKL_CASH_FLOW_LEVELS, OKL_LEASE_QUOTES_B QUOTE
where OKL_CASH_FLOW_objects.OTY_CODE = 'QUOTED_SERVICE'
and OKL_CASH_FLOW_objects.id = okl_cash_flows.CFO_ID
and okl_cash_flows.id = OKL_CASH_FLOW_LEVELS.CAF_ID
and OKL_CASH_FLOW_OBJECTS.source_id = SRV.id
and OKL_CASH_FLOW_OBJECTS.source_table = 'OKL_SERVICES_B'
and SRV.parent_object_id = QUOTE.id
and QUOTE.parent_object_id = LAP.id
and SRV.parent_object_code = 'LEASEQUOTE'
and QUOTE.parent_object_code = 'LEASEAPP'
and QUOTE.primary_quote = 'Y'
and LAP.id = l_lease_app_id
union
select OKL_CASH_FLOW_LEVELS.amount amount
from okl_lease_applications_b LAP, OKL_INSURANCE_ESTIMATES_B INS, OKL_CASH_FLOW_OBJECTS,
okl_cash_flows, OKL_CASH_FLOW_LEVELS, OKL_LEASE_QUOTES_B QUOTE
where OKL_CASH_FLOW_objects.OTY_CODE = 'QUOTED_INSURANCE'
and OKL_CASH_FLOW_objects.id = okl_cash_flows.CFO_ID
and okl_cash_flows.id = OKL_CASH_FLOW_LEVELS.CAF_ID
and OKL_CASH_FLOW_OBJECTS.source_id = INS.id
and OKL_CASH_FLOW_OBJECTS.source_table = 'OKL_INSURANCE_ESTIMATES_B'
and INS.LEASE_QUOTE_ID = QUOTE.id
and QUOTE.parent_object_id = LAP.id
and QUOTE.parent_object_code = 'LEASEAPP'
and QUOTE.primary_quote = 'Y'
and LAP.id = l_lease_app_id; */
select LVL.start_date
from OKL_CASH_FLOW_OBJECTS CFO, OKL_CASH_FLOWS CFL, OKL_CASH_FLOW_LEVELS LVL
where CFO.OTY_CODE = 'QUOTED_ASSET'
and CFO.ID = CFL.CFO_ID
and CFL.ID = LVL.CAF_ID
and CFO.SOURCE_ID = l_asset_id
and CFO.SOURCE_TABLE = 'OKL_ASSETS_B';
select OKL_CASH_FLOW_LEVELS.start_date start_date
from okl_lease_applications_b LAP, OKL_FEES_B FEE, OKL_CASH_FLOW_OBJECTS,
okl_cash_flows, OKL_CASH_FLOW_LEVELS, OKL_LEASE_QUOTES_B QUOTE
where OKL_CASH_FLOW_objects.OTY_CODE = 'QUOTED_FEE'
and OKL_CASH_FLOW_objects.id = okl_cash_flows.CFO_ID
and okl_cash_flows.id = OKL_CASH_FLOW_LEVELS.CAF_ID
and OKL_CASH_FLOW_OBJECTS.source_id = FEE.id
and OKL_CASH_FLOW_OBJECTS.source_table = 'OKL_FEES_B'
and FEE.parent_object_id = QUOTE.id
and QUOTE.parent_object_id = LAP.id
and FEE.parent_object_code = 'LEASEQUOTE'
and QUOTE.parent_object_code = 'LEASEAPP'
and QUOTE.primary_quote = 'Y'
and LAP.id = l_lease_app_id
union
select OKL_CASH_FLOW_LEVELS.start_date start_date
from okl_lease_applications_b LAP, OKL_SERVICES_B SRV, OKL_CASH_FLOW_OBJECTS,
okl_cash_flows, OKL_CASH_FLOW_LEVELS, OKL_LEASE_QUOTES_B QUOTE
where OKL_CASH_FLOW_objects.OTY_CODE = 'QUOTED_SERVICE'
and OKL_CASH_FLOW_objects.id = okl_cash_flows.CFO_ID
and okl_cash_flows.id = OKL_CASH_FLOW_LEVELS.CAF_ID
and OKL_CASH_FLOW_OBJECTS.source_id = SRV.id
and OKL_CASH_FLOW_OBJECTS.source_table = 'OKL_SERVICES_B'
and SRV.parent_object_id = QUOTE.id
and QUOTE.parent_object_id = LAP.id
and SRV.parent_object_code = 'LEASEQUOTE'
and QUOTE.parent_object_code = 'LEASEAPP'
and QUOTE.primary_quote = 'Y'
and LAP.id = l_lease_app_id
union
select OKL_CASH_FLOW_LEVELS.start_date start_date
from okl_lease_applications_b LAP, OKL_INSURANCE_ESTIMATES_B INS, OKL_CASH_FLOW_OBJECTS,
okl_cash_flows, OKL_CASH_FLOW_LEVELS, OKL_LEASE_QUOTES_B QUOTE
where OKL_CASH_FLOW_objects.OTY_CODE = 'QUOTED_INSURANCE'
and OKL_CASH_FLOW_objects.id = okl_cash_flows.CFO_ID
and okl_cash_flows.id = OKL_CASH_FLOW_LEVELS.CAF_ID
and OKL_CASH_FLOW_OBJECTS.source_id = INS.id
and OKL_CASH_FLOW_OBJECTS.source_table = 'OKL_INSURANCE_ESTIMATES_B'
and INS.LEASE_QUOTE_ID = QUOTE.id
and QUOTE.parent_object_id = LAP.id
and QUOTE.parent_object_code = 'LEASEAPP'
and QUOTE.primary_quote = 'Y'
and LAP.id = l_lease_app_id;*/
select LVL.stub_days, LVL.number_of_periods, LVL.start_date
from OKL_CASH_FLOW_OBJECTS CFO, OKL_CASH_FLOWS CFL, OKL_CASH_FLOW_LEVELS LVL
where CFO.OTY_CODE = 'QUOTED_ASSET'
and CFO.ID = CFL.CFO_ID
and CFL.ID = LVL.CAF_ID
and CFO.SOURCE_ID = l_asset_id
and CFO.SOURCE_TABLE = 'OKL_ASSETS_B';
select OKL_CASH_FLOW_LEVELS.start_date end_date
from okl_lease_applications_b LAP, OKL_FEES_B FEE, OKL_CASH_FLOW_OBJECTS,
okl_cash_flows, OKL_CASH_FLOW_LEVELS, OKL_LEASE_QUOTES_B QUOTE
where OKL_CASH_FLOW_objects.OTY_CODE = 'QUOTED_FEE'
and OKL_CASH_FLOW_objects.id = okl_cash_flows.CFO_ID
and okl_cash_flows.id = OKL_CASH_FLOW_LEVELS.CAF_ID
and OKL_CASH_FLOW_OBJECTS.source_id = FEE.id
and OKL_CASH_FLOW_OBJECTS.source_table = 'OKL_FEES_B'
and FEE.parent_object_id = QUOTE.id
and QUOTE.parent_object_id = LAP.id
and FEE.parent_object_code = 'LEASEQUOTE'
and QUOTE.parent_object_code = 'LEASEAPP'
and QUOTE.primary_quote = 'Y'
and LAP.id = l_lease_app_id
union
select OKL_CASH_FLOW_LEVELS.start_date end_date
from okl_lease_applications_b LAP, OKL_SERVICES_B SRV, OKL_CASH_FLOW_OBJECTS,
okl_cash_flows, OKL_CASH_FLOW_LEVELS, OKL_LEASE_QUOTES_B QUOTE
where OKL_CASH_FLOW_objects.OTY_CODE = 'QUOTED_SERVICE'
and OKL_CASH_FLOW_objects.id = okl_cash_flows.CFO_ID
and okl_cash_flows.id = OKL_CASH_FLOW_LEVELS.CAF_ID
and OKL_CASH_FLOW_OBJECTS.source_id = SRV.id
and OKL_CASH_FLOW_OBJECTS.source_table = 'OKL_SERVICES_B'
and SRV.parent_object_id = QUOTE.id
and QUOTE.parent_object_id = LAP.id
and SRV.parent_object_code = 'LEASEQUOTE'
and QUOTE.parent_object_code = 'LEASEAPP'
and QUOTE.primary_quote = 'Y'
and LAP.id = l_lease_app_id
union
select OKL_CASH_FLOW_LEVELS.start_date end_date
from okl_lease_applications_b LAP, OKL_INSURANCE_ESTIMATES_B INS, OKL_CASH_FLOW_OBJECTS,
okl_cash_flows, OKL_CASH_FLOW_LEVELS, OKL_LEASE_QUOTES_B QUOTE
where OKL_CASH_FLOW_objects.OTY_CODE = 'QUOTED_INSURANCE'
and OKL_CASH_FLOW_objects.id = okl_cash_flows.CFO_ID
and okl_cash_flows.id = OKL_CASH_FLOW_LEVELS.CAF_ID
and OKL_CASH_FLOW_OBJECTS.source_id = INS.id
and OKL_CASH_FLOW_OBJECTS.source_table = 'OKL_INSURANCE_ESTIMATES_B'
and INS.LEASE_QUOTE_ID = QUOTE.id
and QUOTE.parent_object_id = LAP.id
and QUOTE.parent_object_code = 'LEASEAPP'
and QUOTE.primary_quote = 'Y'
and LAP.id = l_lease_app_id;*/
SELECT OKL_CASH_FLOW_LEVELS.FQY_CODE FQY_CODE, QUOTE.TERM
FROM OKL_LEASE_APPLICATIONS_B LAP, OKL_ASSETS_B ASSET, OKL_CASH_FLOW_OBJECTS,
OKL_CASH_FLOWS, OKL_CASH_FLOW_LEVELS, OKL_LEASE_QUOTES_B QUOTE
WHERE OKL_CASH_FLOW_OBJECTS.OTY_CODE = 'QUOTED_ASSET'
AND OKL_CASH_FLOW_OBJECTS.ID = OKL_CASH_FLOWS.CFO_ID
AND OKL_CASH_FLOWS.ID = OKL_CASH_FLOW_LEVELS.CAF_ID
AND OKL_CASH_FLOW_OBJECTS.SOURCE_ID = ASSET.ID
AND OKL_CASH_FLOW_OBJECTS.SOURCE_TABLE = 'OKL_ASSETS_B'
AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
AND QUOTE.PARENT_OBJECT_ID = LAP.ID
AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
AND QUOTE.PRIMARY_QUOTE = 'Y'
AND LAP.ID = l_lease_app_id
AND ASSET.ID = l_asset_id
AND ROWNUM = 1;
SELECT STRM.STY_NAME FEE_NAME
FROM OKL_LEASE_APPLICATIONS_B LAP, OKL_LEASE_QUOTES_B QUOTE,
OKL_FEES_B FEES, OKL_STRM_TMPT_PRIMARY_UV STRM
WHERE STRM.STY_ID = FEES.STREAM_TYPE_ID
AND FEES.PARENT_OBJECT_CODE = 'LEASEQUOTE'
AND FEES.PARENT_OBJECT_ID = QUOTE.ID
AND QUOTE.PARENT_OBJECT_ID = LAP.ID
AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
AND QUOTE.PRIMARY_QUOTE = 'Y'
AND LAP.ID = l_lease_app_id;
SELECT STRM.NAME FEE_NAME
FROM OKL_LEASE_APPLICATIONS_B LAP, OKL_LEASE_QUOTES_B QUOTE,
OKL_FEES_B FEES, OKL_STRM_TYPE_V STRM
WHERE STRM.ID = FEES.STREAM_TYPE_ID
AND FEES.PARENT_OBJECT_CODE = 'LEASEQUOTE'
AND FEES.PARENT_OBJECT_ID = QUOTE.ID
AND QUOTE.PARENT_OBJECT_ID = LAP.ID
AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
AND QUOTE.PRIMARY_QUOTE = 'Y'
AND LAP.ID = l_lease_app_id;
SELECT FEES.FEE_TYPE FEE_TYPE
FROM OKL_LEASE_APPLICATIONS_B LAP, OKL_LEASE_QUOTES_B QUOTE, OKL_FEES_B FEES
WHERE FEES.PARENT_OBJECT_CODE = 'LEASEQUOTE'
AND FEES.PARENT_OBJECT_ID = QUOTE.ID
AND QUOTE.PARENT_OBJECT_ID = LAP.ID
AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
AND QUOTE.PRIMARY_QUOTE = 'Y'
AND LAP.ID = l_lease_app_id;
SELECT FEES.FEE_AMOUNT FEE_AMOUNT
FROM OKL_LEASE_APPLICATIONS_B LAP, OKL_LEASE_QUOTES_B QUOTE, OKL_FEES_B FEES
WHERE FEES.PARENT_OBJECT_CODE = 'LEASEQUOTE'
AND FEES.PARENT_OBJECT_ID = QUOTE.ID
AND QUOTE.PARENT_OBJECT_ID = LAP.ID
AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
AND QUOTE.PRIMARY_QUOTE = 'Y'
AND LAP.ID = l_lease_app_id;
SELECT FEES.EFFECTIVE_FROM EFFECTIVE_FROM
FROM OKL_LEASE_APPLICATIONS_B LAP, OKL_LEASE_QUOTES_B QUOTE, OKL_FEES_B FEES
WHERE FEES.PARENT_OBJECT_CODE = 'LEASEQUOTE'
AND FEES.PARENT_OBJECT_ID = QUOTE.ID
AND QUOTE.PARENT_OBJECT_ID = LAP.ID
AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
AND QUOTE.PRIMARY_QUOTE = 'Y'
AND LAP.ID = l_lease_app_id;
SELECT NVL(SUM(VALUE),0 )
FROM OKL_LEASE_APPLICATIONS_B LAP, OKL_ASSETS_B ASSET,
OKL_COST_ADJUSTMENTS_B ADJ, OKL_LEASE_QUOTES_B QUOTE
WHERE ADJ.PARENT_OBJECT_CODE = 'ASSET'
AND ADJ.PARENT_OBJECT_ID = ASSET.ID
AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
AND QUOTE.PARENT_OBJECT_ID = LAP.ID
AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
AND QUOTE.PRIMARY_QUOTE = 'Y'
AND ADJ.ADJUSTMENT_SOURCE_TYPE IN ('DOWN_PAYMENT', 'TRADEIN')
AND LAP.ID = p_lease_app_id;
SELECT NVL(SUM(AST.OEC), 0)
INTO ln_asset_cost
FROM OKL_ASSETS_B AST, OKL_LEASE_QUOTES_B QUOTE, OKL_LEASE_APPLICATIONS_B LAP
WHERE AST.PARENT_OBJECT_ID = QUOTE.ID
AND AST.PARENT_OBJECT_CODE = 'LEASEQUOTE'
AND QUOTE.PARENT_OBJECT_ID = LAP.ID
AND QUOTE.PRIMARY_QUOTE = 'Y'
AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
AND LAP.ID = l_lease_app_id;
/*SELECT NVL(SUM(AST_COMP.UNIT_COST * AST_COMP.NUMBER_OF_UNITS), 0)
INTO ln_addon_cost
FROM OKL_ASSETS_B AST, OKL_ASSET_COMPONENTS_B AST_COMP,
OKL_LEASE_QUOTES_B QUOTE, OKL_LEASE_APPLICATIONS_B LAP
WHERE AST_COMP.ASSET_ID = AST.ID
AND AST_COMP.PRIMARY_COMPONENT = 'NO'
AND AST.PARENT_OBJECT_ID = QUOTE.ID
AND AST.PARENT_OBJECT_CODE = 'LEASEQUOTE'
AND QUOTE.PARENT_OBJECT_ID = LAP.ID
AND QUOTE.PRIMARY_QUOTE = 'Y'
AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
AND LAP.ID = l_lease_app_id;*/
SELECT NVL(SUM(FEES.FEE_AMOUNT), 0)
INTO ln_fee_cost
FROM OKL_LEASE_APPLICATIONS_B LAP, OKL_LEASE_QUOTES_B QUOTE, OKL_FEES_B FEES
WHERE FEES.PARENT_OBJECT_CODE = 'LEASEQUOTE'
AND FEES.PARENT_OBJECT_ID = QUOTE.ID
AND FEES.FEE_TYPE IN ('ROLLOVER', 'FINANCED', 'CAPITALIZED') --Bug 6697231 Added capitalized fee
AND QUOTE.PARENT_OBJECT_ID = LAP.ID
AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
AND QUOTE.PRIMARY_QUOTE = 'Y'
AND LAP.ID = l_lease_app_id;
SELECT NVL(SUM(ADJ.VALUE), 0)
INTO ln_down_payment_amount
FROM OKL_LEASE_APPLICATIONS_B LAP, OKL_ASSETS_B ASSET,
OKL_COST_ADJUSTMENTS_B ADJ, OKL_LEASE_QUOTES_B QUOTE
WHERE ADJ.ADJUSTMENT_SOURCE_TYPE IN ('DOWN_PAYMENT', 'TRADEIN')
AND ADJ.PARENT_OBJECT_CODE = 'ASSET'
AND ADJ.PARENT_OBJECT_ID = ASSET.ID
AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
AND QUOTE.PARENT_OBJECT_ID = LAP.ID
AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
AND QUOTE.PRIMARY_QUOTE = 'Y'
AND LAP.ID = l_lease_app_id;
SELECT FEES.FEE_AMOUNT FEE_AMOUNT
FROM OKL_LEASE_APPLICATIONS_B LAP, OKL_LEASE_QUOTES_B QUOTE, OKL_FEES_B FEES
WHERE FEES.PARENT_OBJECT_CODE = 'LEASEQUOTE'
AND FEES.PARENT_OBJECT_ID = QUOTE.ID
AND QUOTE.PARENT_OBJECT_ID = LAP.ID
AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
AND QUOTE.PRIMARY_QUOTE = 'Y'
AND FEES.FEE_TYPE = 'SEC_DEPOSIT'
AND LAP.ID = l_lease_app_id;
SELECT SUM(TRXD.TAX_AMT)
INTO l_billed_tax
FROM
OKL_TAX_SOURCES TXS, OKL_TAX_TRX_DETAILS TRXD, OKL_LEASE_QUOTES_B LSQ,
OKL_LEASE_APPLICATIONS_B LAP
WHERE
TRXD.TXS_ID = TXS.ID
AND TRXD.BILLED_YN = 'Y'
AND TXS.TRX_ID = LSQ.ID
AND TXS.TAX_CALL_TYPE_CODE = 'UPFRONT_TAX'
AND TXS.TAX_CALL_TYPE_CODE = TRXD.TAX_CALL_TYPE_CODE
AND TXS.ENTITY_CODE = 'OKL_LEASE_QUOTES_B'
AND TXS.TAX_LINE_STATUS_CODE = 'ACTIVE'
AND LSQ.PARENT_OBJECT_ID = LAP.ID
AND LSQ.PARENT_OBJECT_CODE = 'LEASEAPP'
AND LAP.ID = l_lease_app_id;
SELECT lat.credit_review_purpose,
lat.cust_credit_classification
FROM
okl_leaseapp_templates lat,
okl_leaseapp_templ_versions_b latv,
okl_lease_applications_b lap
WHERE
lap.leaseapp_template_id = latv.id
AND latv.version_status = 'ACTIVE'
AND latv.valid_from <= lap.valid_from
AND nvl(latv.valid_to, lap.valid_from) >= lap.valid_from
AND latv.leaseapp_template_id = lat.id
AND lap.id = p_leaseapp_id;
okl_lad_pvt.insert_row (p_api_version => G_API_VERSION
,p_init_msg_list => G_FALSE
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_ladv_tbl => lp_lap_dp_tbl
,x_ladv_tbl => lpx_lap_dp_tbl);
okl_lad_pvt.update_row (p_api_version => G_API_VERSION
,p_init_msg_list => G_FALSE
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_ladv_tbl => lp_lap_dp_tbl
,x_ladv_tbl => lpx_lap_dp_tbl);
PROCEDURE delete_leaseapp_datapoints(p_api_version IN NUMBER
,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
,p_leaseapp_id IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2) IS
l_program_name CONSTANT VARCHAR2(30) := 'delete_leaseapp_datapoints';
SELECT id
FROM
okl_leaseapp_datapoints
WHERE
leaseapp_id = p_leaseapp_id;
okl_lad_pvt.delete_row (
p_api_version => G_API_VERSION
,p_init_msg_list => G_FALSE
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_ladv_tbl => lap_dp_tbl
,px_error_tbl => l_error_tbl_type );
END delete_leaseapp_datapoints;
SELECT count(*)
FROM
okl_leaseapp_datapoints
WHERE
leaseapp_id = p_leaseapp_id;
SELECT DATA_POINT_VALUE
INTO lv_data_point_value
FROM OKL_LEASEAPP_DATAPOINTS
WHERE DATA_POINT_ID = l_data_point_id
AND LEASEAPP_ID = l_lease_app_id;