The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'Y'
FROM okl_quote_subpool_usage
WHERE source_object_id = p_object_id
AND source_type_code = p_obj_code
AND subsidy_pool_id = p_subsidy_pool_id;
Select Count(1)
Into L_count
From OKL_LEASE_APPLICATIONS_B
WHERE LEASE_OPPORTUNITY_ID IS NOT NULL
AND ID = p_lease_app_id;
Select PARENT_LEASEAPP_ID
From OKL_LEASE_APPLICATIONS_B
WHERE ID = p_lease_app_id;
SELECT 'Y'
FROM OKL_LEASE_APPLICATIONS_B
WHERE parent_leaseapp_id = p_lease_app_id;
okl_qul_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_qulv_tbl => l_quote_sp_usage_tbl
,x_qulv_tbl => lx_quote_sp_usage_tbl);
PROCEDURE delete_quote_subpool_usage(p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_subsidy_pool_id IN NUMBER,
p_source_object_id IN NUMBER) IS
l_program_name CONSTANT VARCHAR2(30) := 'delete_quote_subpool_usage';
SELECT ID
FROM OKL_QUOTE_SUBPOOL_USAGE
WHERE SUBSIDY_POOL_ID = p_subsidy_pool_id
AND SOURCE_OBJECT_ID = p_source_object_id;
okl_qul_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_qulv_tbl => l_quote_sp_usage_tbl);
END delete_quote_subpool_usage;
SELECT SOURCE_TYPE_CODE,
SOURCE_OBJECT_ID,
ASSET_NUMBER,
ASSET_START_DATE,
SUBSIDY_POOL_ID,
SUBSIDY_POOL_AMOUNT,
SUBSIDY_POOL_CURRENCY_CODE,
SUBSIDY_ID,
SUBSIDY_AMOUNT,
SUBSIDY_CURRENCY_CODE,
VENDOR_ID,
CONVERSION_RATE
FROM OKL_QUOTE_SUBPOOL_USAGE
WHERE SUBSIDY_POOL_ID = p_subsidy_pool_id
AND SOURCE_OBJECT_ID = p_quote_id;
SELECT SUB.SUBSIDY_POOL_ID SUBSIDY_POOL_ID,
SUM(DECODE(ADJ.VALUE, NULL, ADJ.DEFAULT_SUBSIDY_AMOUNT, ADJ.VALUE)) AMOUNT,
QUOTE.ID QUOTE_ID
FROM OKL_COST_ADJUSTMENTS_B ADJ,
OKL_SUBSIDIES_B SUB,
OKL_SUBSIDY_POOLS_B SUB_POOL,
OKL_ASSETS_B ASSET,
OKL_LEASE_QUOTES_B QUOTE
WHERE ADJ.ADJUSTMENT_SOURCE_TYPE = 'SUBSIDY'
AND ADJ.PARENT_OBJECT_CODE = 'ASSET'
AND ADJ.ADJUSTMENT_SOURCE_ID = SUB.ID
AND SUB.SUBSIDY_POOL_ID = SUB_POOL.ID
AND ADJ.PARENT_OBJECT_ID = ASSET.ID
AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
AND QUOTE.STATUS = 'PR-APPROVED'
AND QUOTE.PARENT_OBJECT_ID = p_lop_id
AND QUOTE.ID <> p_quote_id
AND SUB_POOL.ID = p_subsidy_pool_id
group by SUB.SUBSIDY_POOL_ID ,QUOTE.ID;
SELECT ID
, REFERENCE_NUMBER
, APPLICATION_STATUS
FROM OKL_LEASE_APPLICATIONS_B
WHERE application_Status NOT IN ('CANCELED','WITHDRAWN')
CONNECT BY PARENT_LEASEAPP_ID = PRIOR ID
START WITH ID = p_lap_id
UNION
SELECT ID
, REFERENCE_NUMBER
, APPLICATION_STATUS
FROM OKL_LEASE_APPLICATIONS_B
WHERE ID <> p_lap_id
AND application_Status NOT IN ('CANCELED','WITHDRAWN')
CONNECT BY PRIOR PARENT_LEASEAPP_ID = ID
START WITH ID = p_lap_id;
SELECT QUOTE.ID QUOTE_ID
FROM OKL_LEASE_QUOTES_V QUOTE
WHERE QUOTE.STATUS IN ( 'PR-APPROVED','CT-ACCEPTED','CR-RECOMMENDATION')
AND QUOTE.ID <> p_current_qte_id
AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
AND QUOTE.PARENT_OBJECT_ID = p_lease_app_id;
SELECT SUB.SUBSIDY_POOL_ID SUBSIDY_POOL_ID,
SUM(DECODE(ADJ.VALUE, NULL, ADJ.DEFAULT_SUBSIDY_AMOUNT, ADJ.VALUE)) AMOUNT,
QUOTE.ID QUOTE_ID
FROM OKL_COST_ADJUSTMENTS_B ADJ,
OKL_SUBSIDIES_B SUB,
OKL_SUBSIDY_POOLS_B SUB_POOL,
OKL_ASSETS_B ASSET,
OKL_LEASE_QUOTES_V QUOTE,
OKL_LEASE_APPLICATIONS_B LAP
WHERE ADJ.ADJUSTMENT_SOURCE_TYPE = 'SUBSIDY'
AND ADJ.PARENT_OBJECT_CODE = 'ASSET'
AND ADJ.ADJUSTMENT_SOURCE_ID = SUB.ID
AND SUB.SUBSIDY_POOL_ID = SUB_POOL.ID
AND ADJ.PARENT_OBJECT_ID = ASSET.ID
AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
AND QUOTE.STATUS IN ( 'PR-APPROVED','CT-ACCEPTED','CR-RECOMMENDATION')
AND QUOTE.ID <> p_current_qte_id
AND QUOTE.ID = p_qte_id
AND QUOTE.PARENT_OBJECT_ID = LAP.ID
AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
AND SUB_POOL.ID = p_subsidy_pool_id
group by SUB.SUBSIDY_POOL_ID ,QUOTE.ID;
SELECT QUOTE.ID QUOTE_ID
FROM OKL_LEASE_QUOTES_V QUOTE
WHERE QUOTE.STATUS IN ( 'PR-APPROVED','CT-ACCEPTED','CR-RECOMMENDATION')
AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
AND QUOTE.PARENT_OBJECT_ID = p_lease_app_id;
SELECT SUB.SUBSIDY_POOL_ID SUBSIDY_POOL_ID,
SUM(DECODE(ADJ.VALUE, NULL, ADJ.DEFAULT_SUBSIDY_AMOUNT, ADJ.VALUE)) AMOUNT,
QUOTE.ID QUOTE_ID
FROM OKL_COST_ADJUSTMENTS_B ADJ,
OKL_SUBSIDIES_B SUB,
OKL_SUBSIDY_POOLS_B SUB_POOL,
OKL_ASSETS_B ASSET,
OKL_LEASE_QUOTES_V QUOTE,
OKL_LEASE_APPLICATIONS_B LAP
WHERE ADJ.ADJUSTMENT_SOURCE_TYPE = 'SUBSIDY'
AND ADJ.PARENT_OBJECT_CODE = 'ASSET'
AND ADJ.ADJUSTMENT_SOURCE_ID = SUB.ID
AND SUB.SUBSIDY_POOL_ID = SUB_POOL.ID
AND ADJ.PARENT_OBJECT_ID = ASSET.ID
AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
AND QUOTE.STATUS IN ( 'PR-APPROVED','CT-ACCEPTED','CR-RECOMMENDATION')
AND QUOTE.ID = p_qte_id
AND QUOTE.PARENT_OBJECT_ID = LAP.ID
AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
AND SUB_POOL.ID = p_subsidy_pool_id
group by SUB.SUBSIDY_POOL_ID ,QUOTE.ID;
SELECT ADJ.ADJUSTMENT_SOURCE_ID SUBSIDY_ID,
DECODE(ADJ.VALUE, NULL, ADJ.DEFAULT_SUBSIDY_AMOUNT, ADJ.VALUE) VALUE,
ADJ.SUPPLIER_ID,
ASSET.ASSET_NUMBER,
QUOTE.EXPECTED_START_DATE,
SUB.CURRENCY_CODE SUB_CURRENCY_CODE,
SUB_POOL.CURRENCY_CODE SUBPOOL_CURRENCY_CODE,
-- SUB_POOL.CURRENCY_CONVERSION_TYPE,
SUB_POOL.TOTAL_SUBSIDY_AMOUNT
FROM OKL_COST_ADJUSTMENTS_B ADJ,
OKL_SUBSIDIES_B SUB,
OKL_SUBSIDY_POOLS_B SUB_POOL,
OKL_ASSETS_B ASSET,
OKL_LEASE_QUOTES_B QUOTE
WHERE ADJ.ADJUSTMENT_SOURCE_TYPE = 'SUBSIDY'
AND ADJ.PARENT_OBJECT_CODE = 'ASSET'
AND ADJ.ADJUSTMENT_SOURCE_ID = SUB.ID
AND SUB.SUBSIDY_POOL_ID = SUB_POOL.ID
AND ADJ.PARENT_OBJECT_ID = ASSET.ID
AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
AND QUOTE.ID = p_quote_id
AND SUB_POOL.ID = p_subsidy_pool_id;
SELECT QUOTE_ID,
SUBSIDY_POOL_ID,
MAX(AMOUNT) AMOUNT
FROM
(SELECT SUB.SUBSIDY_POOL_ID SUBSIDY_POOL_ID,
SUM(DECODE(ADJ.VALUE, NULL, ADJ.DEFAULT_SUBSIDY_AMOUNT, ADJ.VALUE)) AMOUNT,
QUOTE.ID QUOTE_ID
FROM OKL_COST_ADJUSTMENTS_B ADJ,
OKL_SUBSIDIES_B SUB,
OKL_SUBSIDY_POOLS_B SUB_POOL,
OKL_ASSETS_B ASSET,
OKL_LEASE_QUOTES_B QUOTE
WHERE ADJ.ADJUSTMENT_SOURCE_TYPE = 'SUBSIDY'
AND ADJ.PARENT_OBJECT_CODE = 'ASSET'
AND ADJ.ADJUSTMENT_SOURCE_ID = SUB.ID
AND SUB.SUBSIDY_POOL_ID = SUB_POOL.ID
AND ADJ.PARENT_OBJECT_ID = ASSET.ID
AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
AND QUOTE.STATUS = 'PR-APPROVED'
AND QUOTE.PARENT_OBJECT_ID = p_parent_object_id
AND QUOTE.ID <> p_quote_id
AND SUB_POOL.ID = p_subsidy_pool_id
GROUP BY SUB.SUBSIDY_POOL_ID, QUOTE.ID)
WHERE (SUBSIDY_POOL_ID, AMOUNT)
IN
(SELECT SUBSIDY_POOL_ID,
MAX(AMOUNT) AMOUNT
FROM
(SELECT SUB.SUBSIDY_POOL_ID SUBSIDY_POOL_ID,
SUM(DECODE(ADJ.VALUE, NULL, ADJ.DEFAULT_SUBSIDY_AMOUNT, ADJ.VALUE)) AMOUNT,
QUOTE.ID QUOTE_ID
FROM OKL_COST_ADJUSTMENTS_B ADJ,
OKL_SUBSIDIES_B SUB,
OKL_SUBSIDY_POOLS_B SUB_POOL,
OKL_ASSETS_B ASSET,
OKL_LEASE_QUOTES_B QUOTE
WHERE ADJ.ADJUSTMENT_SOURCE_TYPE = 'SUBSIDY'
AND ADJ.PARENT_OBJECT_CODE = 'ASSET'
AND ADJ.ADJUSTMENT_SOURCE_ID = SUB.ID
AND SUB.SUBSIDY_POOL_ID = SUB_POOL.ID
AND ADJ.PARENT_OBJECT_ID = ASSET.ID
AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
AND QUOTE.STATUS = 'PR-APPROVED'
AND QUOTE.PARENT_OBJECT_ID = p_parent_object_id
AND QUOTE.ID <> p_quote_id
AND SUB_POOL.ID = p_subsidy_pool_id
GROUP BY SUB.SUBSIDY_POOL_ID, QUOTE.ID)
GROUP BY SUBSIDY_POOL_ID)
GROUP BY SUBSIDY_POOL_ID, QUOTE_ID;
SELECT SUB.SUBSIDY_POOL_ID SUBSIDY_POOL_ID,
SUM(DECODE(ADJ.VALUE, NULL, ADJ.DEFAULT_SUBSIDY_AMOUNT, ADJ.VALUE)) AMOUNT
FROM OKL_COST_ADJUSTMENTS_B ADJ,
OKL_SUBSIDIES_B SUB,
OKL_SUBSIDY_POOLS_B SUB_POOL,
OKL_ASSETS_B ASSET,
OKL_LEASE_QUOTES_B QUOTE
WHERE ADJ.ADJUSTMENT_SOURCE_TYPE = 'SUBSIDY'
AND ADJ.PARENT_OBJECT_CODE = 'ASSET'
AND ADJ.ADJUSTMENT_SOURCE_ID = SUB.ID
AND SUB.SUBSIDY_POOL_ID = SUB_POOL.ID
AND ADJ.PARENT_OBJECT_ID = ASSET.ID
AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
AND QUOTE.STATUS = 'PR-APPROVED'
AND QUOTE.ID = p_quote_id
GROUP BY SUB.SUBSIDY_POOL_ID, QUOTE.ID;
l_sub_pool_tbl.delete;
delete_quote_subpool_usage(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_subsidy_pool_id => l_get_quote_pool_values.subsidy_pool_id,
p_source_object_id => ln_populated_quote_id);
SELECT SUB.SUBSIDY_POOL_ID SUBSIDY_POOL_ID,
QUOTE.ID QUOTE_ID
FROM OKL_COST_ADJUSTMENTS_B ADJ,
OKL_SUBSIDIES_B SUB,
OKL_SUBSIDY_POOLS_B SUB_POOL,
OKL_ASSETS_B ASSET,
OKL_LEASE_QUOTES_B QUOTE
WHERE ADJ.ADJUSTMENT_SOURCE_TYPE = 'SUBSIDY'
AND ADJ.PARENT_OBJECT_CODE = 'ASSET'
AND ADJ.ADJUSTMENT_SOURCE_ID = SUB.ID
AND SUB.SUBSIDY_POOL_ID = SUB_POOL.ID
AND ADJ.PARENT_OBJECT_ID = ASSET.ID
AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
AND QUOTE.STATUS = 'PR-APPROVED'
AND QUOTE.PARENT_OBJECT_ID = p_parent_object_id
GROUP BY SUB.SUBSIDY_POOL_ID, QUOTE.ID;
SELECT COUNT(*)
INTO ln_count
FROM OKL_QUOTE_SUBPOOL_USAGE
WHERE SUBSIDY_POOL_ID = l_get_leaseopp_pool_values.subsidy_pool_id
AND SOURCE_OBJECT_ID = l_get_leaseopp_pool_values.quote_id;
l_sub_pool_tbl.delete;
delete_quote_subpool_usage(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_subsidy_pool_id => l_get_leaseopp_pool_values.subsidy_pool_id,
p_source_object_id => l_get_leaseopp_pool_values.quote_id);
SELECT SUB.SUBSIDY_POOL_ID SUBSIDY_POOL_ID,
QUOTE.ID QUOTE_ID
FROM OKL_COST_ADJUSTMENTS_B ADJ,
OKL_SUBSIDIES_B SUB,
OKL_SUBSIDY_POOLS_B SUB_POOL,
OKL_ASSETS_B ASSET,
OKL_LEASE_QUOTES_B QUOTE
WHERE ADJ.ADJUSTMENT_SOURCE_TYPE = 'SUBSIDY'
AND ADJ.PARENT_OBJECT_CODE = 'ASSET'
AND ADJ.ADJUSTMENT_SOURCE_ID = SUB.ID
AND SUB.SUBSIDY_POOL_ID = SUB_POOL.ID
AND ADJ.PARENT_OBJECT_ID = ASSET.ID
AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
AND QUOTE.STATUS = 'PR-APPROVED'
AND QUOTE.PARENT_OBJECT_ID = (SELECT PARENT_OBJECT_ID
FROM OKL_LEASE_QUOTES_B
WHERE ID = p_quote_id)
GROUP BY SUB.SUBSIDY_POOL_ID, QUOTE.ID;
SELECT COUNT(*)
INTO ln_count
FROM OKL_QUOTE_SUBPOOL_USAGE
WHERE SUBSIDY_POOL_ID = l_get_leaseopp_pool_values.subsidy_pool_id
AND SOURCE_OBJECT_ID = l_get_leaseopp_pool_values.quote_id;
delete_quote_subpool_usage(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_subsidy_pool_id => l_get_leaseopp_pool_values.subsidy_pool_id,
p_source_object_id => l_get_leaseopp_pool_values.quote_id);
SELECT SUB.SUBSIDY_POOL_ID SUBSIDY_POOL_ID,
QUOTE.ID QUOTE_ID
FROM OKL_COST_ADJUSTMENTS_B ADJ,
OKL_SUBSIDIES_B SUB,
OKL_SUBSIDY_POOLS_B SUB_POOL,
OKL_ASSETS_B ASSET,
OKL_LEASE_QUOTES_B QUOTE
WHERE ADJ.ADJUSTMENT_SOURCE_TYPE = 'SUBSIDY'
AND ADJ.PARENT_OBJECT_CODE = 'ASSET'
AND ADJ.ADJUSTMENT_SOURCE_ID = SUB.ID
AND SUB.SUBSIDY_POOL_ID = SUB_POOL.ID
AND ADJ.PARENT_OBJECT_ID = ASSET.ID
AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
AND QUOTE.PARENT_OBJECT_ID = p_leaseapp_id
AND QUOTE.PRIMARY_QUOTE = 'Y'
GROUP BY SUB.SUBSIDY_POOL_ID, QUOTE.ID;
SELECT QUOTE.ID
FROM OKL_LEASE_QUOTES_B QUOTE,
OKL_LEASE_OPPORTUNITIES_B LEASEOPP
WHERE QUOTE.PARENT_OBJECT_ID = LEASEOPP.ID
AND QUOTE.PARENT_OBJECT_CODE = 'LEASEOPP'
AND QUOTE.STATUS = 'CT-ACCEPTED'
AND LEASEOPP.ID = (SELECT LEASE_OPPORTUNITY_ID
FROM OKL_LEASE_APPLICATIONS_B
WHERE ID = p_leaseapp_id);
SELECT COUNT(*)
INTO ln_count
FROM OKL_QUOTE_SUBPOOL_USAGE
WHERE SUBSIDY_POOL_ID = l_get_leaseapp_pool_values.subsidy_pool_id
AND SOURCE_OBJECT_ID = p_leaseapp_id;
delete_quote_subpool_usage(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_subsidy_pool_id => l_get_leaseapp_pool_values.subsidy_pool_id,
p_source_object_id => l_get_leaseapp_pool_values.quote_id);
SELECT ORIG_SYSTEM_ID1,
ORIG_SYSTEM_SOURCE_CODE
INTO ln_source_object_id, lv_source_object_code
FROM OKC_K_HEADERS_B
WHERE ID = p_contract_id;
SELECT SUB.SUBSIDY_POOL_ID SUBSIDY_POOL_ID,
QUOTE.ID QUOTE_ID,
SUM(DECODE(ADJ.VALUE, NULL, ADJ.DEFAULT_SUBSIDY_AMOUNT, ADJ.VALUE)) AMOUNT
FROM OKL_COST_ADJUSTMENTS_B ADJ,
OKL_SUBSIDIES_B SUB,
OKL_SUBSIDY_POOLS_B SUB_POOL,
OKL_ASSETS_B ASSET,
OKL_LEASE_QUOTES_B QUOTE
WHERE ADJ.ADJUSTMENT_SOURCE_TYPE = 'SUBSIDY'
AND ADJ.PARENT_OBJECT_CODE = 'ASSET'
AND ADJ.ADJUSTMENT_SOURCE_ID = SUB.ID
AND SUB.SUBSIDY_POOL_ID = SUB_POOL.ID
AND ADJ.PARENT_OBJECT_ID = ASSET.ID
AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
AND QUOTE.ID = p_quote_id
GROUP BY SUB.SUBSIDY_POOL_ID, QUOTE.ID;
SELECT LEASE_OPPORTUNITY_ID
INTO lv_linked_lop_id
FROM OKL_LEASE_APPLICATIONS_B
WHERE ID =p_parent_object_id;
l_sub_pool_tbl.delete;
delete_quote_subpool_usage(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_subsidy_pool_id => l_get_leaseapp_pool_values.subsidy_pool_id,
p_source_object_id => ln_rollback_quote);
PROCEDURE handle_leaseapp_update (p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_transaction_reason IN VARCHAR2,
p_parent_object_id IN NUMBER,
p_parent_object_code IN VARCHAR2,
p_quote_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) := 'handle_leaseapp_update';
SELECT SUB.SUBSIDY_POOL_ID SUBSIDY_POOL_ID,
QUOTE.ID QUOTE_ID
FROM OKL_COST_ADJUSTMENTS_B ADJ,
OKL_SUBSIDIES_B SUB,
OKL_SUBSIDY_POOLS_B SUB_POOL,
OKL_ASSETS_B ASSET,
OKL_LEASE_QUOTES_B QUOTE
WHERE ADJ.ADJUSTMENT_SOURCE_TYPE = 'SUBSIDY'
AND ADJ.PARENT_OBJECT_CODE = 'ASSET'
AND ADJ.ADJUSTMENT_SOURCE_ID = SUB.ID
AND SUB.SUBSIDY_POOL_ID = SUB_POOL.ID
AND ADJ.PARENT_OBJECT_ID = ASSET.ID
AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
AND QUOTE.ID = p_quote_id
GROUP BY SUB.SUBSIDY_POOL_ID, QUOTE.ID;
SELECT SOURCE_TYPE_CODE,
SOURCE_OBJECT_ID,
ASSET_NUMBER,
ASSET_START_DATE,
SUBSIDY_POOL_ID,
SUBSIDY_POOL_AMOUNT,
SUBSIDY_POOL_CURRENCY_CODE,
SUBSIDY_ID,
SUBSIDY_AMOUNT,
SUBSIDY_CURRENCY_CODE,
VENDOR_ID,
CONVERSION_RATE
FROM OKL_QUOTE_SUBPOOL_USAGE
WHERE SUBSIDY_POOL_ID = l_subsidy_pool_id
AND SOURCE_OBJECT_ID = l_qte_id;
l_sub_pool_tbl.delete;
delete_quote_subpool_usage(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_subsidy_pool_id => l_get_leaseapp_pool_values.subsidy_pool_id,
p_source_object_id => l_quote_id);
END handle_leaseapp_update;
SELECT SUB.SUBSIDY_POOL_ID SUBSIDY_POOL_ID,
QUOTE.ID QUOTE_ID
FROM OKL_COST_ADJUSTMENTS_B ADJ,
OKL_SUBSIDIES_B SUB,
OKL_SUBSIDY_POOLS_B SUB_POOL,
OKL_ASSETS_B ASSET,
OKL_LEASE_QUOTES_B QUOTE
WHERE ADJ.ADJUSTMENT_SOURCE_TYPE = 'SUBSIDY'
AND ADJ.PARENT_OBJECT_CODE = 'ASSET'
AND ADJ.ADJUSTMENT_SOURCE_ID = SUB.ID
AND SUB.SUBSIDY_POOL_ID = SUB_POOL.ID
AND ADJ.PARENT_OBJECT_ID = ASSET.ID
AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
AND QUOTE.PARENT_OBJECT_ID = p_parent_object_id
GROUP BY SUB.SUBSIDY_POOL_ID, QUOTE.ID;
delete_quote_subpool_usage(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_subsidy_pool_id => l_get_leaseapp_pool_values.subsidy_pool_id,
p_source_object_id => l_get_leaseapp_pool_values.quote_id);
SELECT QUOTE.ID QUOTE_ID
FROM OKL_LEASE_QUOTES_V QUOTE
WHERE QUOTE.STATUS IN ( 'PR-APPROVED','CT-ACCEPTED','CR-RECOMMENDATION')
AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
AND QUOTE.PARENT_OBJECT_ID = p_lease_app_id;
SELECT SUB.SUBSIDY_POOL_ID SUBSIDY_POOL_ID,
QUOTE.ID QUOTE_ID
FROM OKL_COST_ADJUSTMENTS_B ADJ,
OKL_SUBSIDIES_B SUB,
OKL_SUBSIDY_POOLS_B SUB_POOL,
OKL_ASSETS_B ASSET,
OKL_LEASE_QUOTES_B QUOTE
WHERE ADJ.ADJUSTMENT_SOURCE_TYPE = 'SUBSIDY'
AND ADJ.PARENT_OBJECT_CODE = 'ASSET'
AND ADJ.ADJUSTMENT_SOURCE_ID = SUB.ID
AND SUB.SUBSIDY_POOL_ID = SUB_POOL.ID
AND ADJ.PARENT_OBJECT_ID = ASSET.ID
AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
AND QUOTE.ID = p_lap_quote_id
GROUP BY SUB.SUBSIDY_POOL_ID, QUOTE.ID;
SELECT SOURCE_TYPE_CODE,
SOURCE_OBJECT_ID,
ASSET_NUMBER,
ASSET_START_DATE,
SUBSIDY_POOL_ID,
SUBSIDY_POOL_AMOUNT,
SUBSIDY_POOL_CURRENCY_CODE,
SUBSIDY_ID,
SUBSIDY_AMOUNT,
SUBSIDY_CURRENCY_CODE,
VENDOR_ID,
CONVERSION_RATE
FROM OKL_QUOTE_SUBPOOL_USAGE
WHERE SUBSIDY_POOL_ID = l_subsidy_pool_id
AND SOURCE_OBJECT_ID = l_qte_id;
l_sub_pool_tbl.delete;
delete_quote_subpool_usage(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_subsidy_pool_id => l_get_leaseapp_pool_values.subsidy_pool_id,
p_source_object_id => l_get_leaseapp_pool_values.quote_id);
PROCEDURE handle_approved_quote_update (p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_quote_id IN NUMBER,
p_transaction_reason IN VARCHAR2,
p_parent_object_id IN NUMBER,
p_parent_object_code IN VARCHAR2,
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) := 'handle_approved_quote_update';
SELECT QUOTE_ID,
SUBSIDY_POOL_ID,
MAX(AMOUNT) AMOUNT
FROM
(SELECT SUB.SUBSIDY_POOL_ID SUBSIDY_POOL_ID,
SUM(DECODE(ADJ.VALUE, NULL, ADJ.DEFAULT_SUBSIDY_AMOUNT, ADJ.VALUE)) AMOUNT,
QUOTE.ID QUOTE_ID
FROM OKL_COST_ADJUSTMENTS_B ADJ,
OKL_SUBSIDIES_B SUB,
OKL_SUBSIDY_POOLS_B SUB_POOL,
OKL_ASSETS_B ASSET,
OKL_LEASE_QUOTES_B QUOTE
WHERE ADJ.ADJUSTMENT_SOURCE_TYPE = 'SUBSIDY'
AND ADJ.PARENT_OBJECT_CODE = 'ASSET'
AND ADJ.ADJUSTMENT_SOURCE_ID = SUB.ID
AND SUB.SUBSIDY_POOL_ID = SUB_POOL.ID
AND ADJ.PARENT_OBJECT_ID = ASSET.ID
AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
AND QUOTE.STATUS = 'PR-APPROVED'
AND QUOTE.PARENT_OBJECT_ID = p_top_object_id
AND QUOTE.ID <> p_quote_id
AND SUB_POOL.ID = p_subsidy_pool_id
GROUP BY SUB.SUBSIDY_POOL_ID, QUOTE.ID)
WHERE (SUBSIDY_POOL_ID, AMOUNT)
IN
(SELECT SUBSIDY_POOL_ID,
MAX(AMOUNT) AMOUNT
FROM
(SELECT SUB.SUBSIDY_POOL_ID SUBSIDY_POOL_ID,
SUM(DECODE(ADJ.VALUE, NULL, ADJ.DEFAULT_SUBSIDY_AMOUNT, ADJ.VALUE)) AMOUNT,
QUOTE.ID QUOTE_ID
FROM OKL_COST_ADJUSTMENTS_B ADJ,
OKL_SUBSIDIES_B SUB,
OKL_SUBSIDY_POOLS_B SUB_POOL,
OKL_ASSETS_B ASSET,
OKL_LEASE_QUOTES_B QUOTE
WHERE ADJ.ADJUSTMENT_SOURCE_TYPE = 'SUBSIDY'
AND ADJ.PARENT_OBJECT_CODE = 'ASSET'
AND ADJ.ADJUSTMENT_SOURCE_ID = SUB.ID
AND SUB.SUBSIDY_POOL_ID = SUB_POOL.ID
AND ADJ.PARENT_OBJECT_ID = ASSET.ID
AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
AND QUOTE.STATUS = 'PR-APPROVED'
AND QUOTE.PARENT_OBJECT_ID = p_top_object_id
AND QUOTE.ID <> p_quote_id
AND SUB_POOL.ID = p_subsidy_pool_id
GROUP BY SUB.SUBSIDY_POOL_ID, QUOTE.ID)
GROUP BY SUBSIDY_POOL_ID)
GROUP BY SUBSIDY_POOL_ID, QUOTE_ID;
SELECT SUB.SUBSIDY_POOL_ID SUBSIDY_POOL_ID,
SUM(DECODE(ADJ.VALUE, NULL, ADJ.DEFAULT_SUBSIDY_AMOUNT, ADJ.VALUE)) AMOUNT
FROM OKL_COST_ADJUSTMENTS_B ADJ,
OKL_SUBSIDIES_B SUB,
OKL_SUBSIDY_POOLS_B SUB_POOL,
OKL_ASSETS_B ASSET,
OKL_LEASE_QUOTES_B QUOTE
WHERE ADJ.ADJUSTMENT_SOURCE_TYPE = 'SUBSIDY'
AND ADJ.PARENT_OBJECT_CODE = 'ASSET'
AND ADJ.ADJUSTMENT_SOURCE_ID = SUB.ID
AND SUB.SUBSIDY_POOL_ID = SUB_POOL.ID
AND ADJ.PARENT_OBJECT_ID = ASSET.ID
AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
AND QUOTE.STATUS = 'PR-INCOMPLETE'
AND QUOTE.ID = p_quote_id
GROUP BY SUB.SUBSIDY_POOL_ID, QUOTE.ID;
SELECT SOURCE_TYPE_CODE,
SOURCE_OBJECT_ID,
ASSET_NUMBER,
ASSET_START_DATE,
SUBSIDY_POOL_ID,
SUBSIDY_POOL_AMOUNT,
SUBSIDY_POOL_CURRENCY_CODE,
SUBSIDY_ID,
SUBSIDY_AMOUNT,
SUBSIDY_CURRENCY_CODE,
VENDOR_ID,
CONVERSION_RATE
FROM OKL_QUOTE_SUBPOOL_USAGE
WHERE SUBSIDY_POOL_ID = l_subsidy_pool_id
AND SOURCE_OBJECT_ID = p_quote_id;
SELECT REFERENCE_NUMBER, STATUS
INTO lv_reference_number, lv_status_code
FROM OKL_LEASE_QUOTES_B
WHERE ID = p_quote_id;
SELECT COUNT(*) -- Check if the Subsidy pool exists in the usage table
INTO ln_count
FROM OKL_QUOTE_SUBPOOL_USAGE
WHERE SUBSIDY_POOL_ID = l_get_quote_pool_values.subsidy_pool_id
AND SOURCE_OBJECT_ID = p_quote_id;
l_sub_pool_tbl.delete;
delete_quote_subpool_usage(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_subsidy_pool_id => l_get_quote_pool_values.subsidy_pool_id,
p_source_object_id => p_quote_id);
END handle_approved_quote_update;
SELECT parent_object_id, parent_object_code
FROM okl_lease_quotes_b
WHERE id = p_quote_id;
ELSIF (p_transaction_reason = 'UPDATE_APPROVED_QUOTE') THEN -- OR p_transaction_reason = 'EXPIRE_QUOTE')
-- Transaction --> 'UPDATE_APPROVED_QUOTE', 'EXPIRE_QUOTE'
--debug_proc('UPDATE_APPROVED_QUOTE p_quote_id ='||p_quote_id);
handle_approved_quote_update (p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_quote_id => p_quote_id,
p_transaction_reason => p_transaction_reason,
p_parent_object_id => ln_parent_object_id,
p_parent_object_code => lv_parent_object_code,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
ELSIF (p_transaction_reason = 'UPDATE_LEASE_APP') THEN -- Transaction --> 'UPDATE_LEASE_APP'
handle_leaseapp_update (p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_transaction_reason => p_transaction_reason,
p_parent_object_id => p_leaseapp_id,
p_parent_object_code => 'LEASEAPP',
p_quote_id => p_quote_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);