[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT NVL(tax_upfront_yn,'N')
FROM OKL_SYSTEM_PARAMS;
SELECT MEANING
FROM FND_LOOKUPS FL
WHERE FL.LOOKUP_TYPE = p_lookup_type
AND FL.LOOKUP_CODE = p_lookup_code;
SELECT RCM.CREDIT_RECOMMENDATION
, RCM.STATUS
FROM AR_CMGT_CF_RECOMMENDS RCM
, AR_CMGT_CREDIT_REQUESTS CCR
WHERE RCM.CREDIT_REQUEST_ID = CCR.CREDIT_REQUEST_ID
AND RCM.CREDIT_RECOMMENDATION IN ('REJECT', 'APPROVE')
AND CCR.SOURCE_COLUMN3 = 'LEASEAPP'
AND CCR.SOURCE_COLUMN1 = cp_lap_id;
SELECT MEANING
FROM FND_LOOKUPS FL
WHERE FL.LOOKUP_TYPE = 'OKL_LEASE_APP_STATUS'
AND FL.LOOKUP_CODE = p_decision_code;
SELECT COUNT(*)
FROM AR_CMGT_CF_RECOMMENDS RCM
, AR_CMGT_CREDIT_REQUESTS CCR
WHERE RCM.CREDIT_REQUEST_ID = CCR.CREDIT_REQUEST_ID
AND RCM.CREDIT_RECOMMENDATION IN ('APPROVE', 'EXPIRATION_DATE')
AND CCR.SOURCE_COLUMN3 = 'LEASEAPP'
AND CCR.SOURCE_COLUMN1 = cp_lap_id;
SELECT LAP.CR_EXP_DAYS + TRUNC(LAST_UPDATED) APPR_EXP_DATE
FROM AR_CMGT_CASE_FOLDERS CCF
, AR_CMGT_CREDIT_REQUESTS CCR
, OKL_LEASE_APPLICATIONS_B LAP
WHERE CCR.CREDIT_REQUEST_ID = CCF.CREDIT_REQUEST_ID
AND CCR.SOURCE_COLUMN3 = 'LEASEAPP'
AND CCR.SOURCE_COLUMN1 = LAP.ID
AND CCF.STATUS = 'CLOSED'
AND LAP.ID = p_lap_id;
SELECT RCM.CREDIT_RECOMMENDATION
, RCM.APPEALED_FLAG
FROM AR_CMGT_CF_RECOMMENDS RCM
, AR_CMGT_CREDIT_REQUESTS CCR
WHERE RCM.CREDIT_REQUEST_ID = CCR.CREDIT_REQUEST_ID
AND RCM.CREDIT_RECOMMENDATION IN ('REJECT', 'APPROVE')
AND CCR.SOURCE_COLUMN3 = 'LEASEAPP'
AND CCR.SOURCE_COLUMN1 = cp_lap_id;
SELECT COUNT(*)
FROM AR_CMGT_CF_RECOMMENDS RCM
, AR_CMGT_CREDIT_REQUESTS CCR
WHERE RCM.CREDIT_REQUEST_ID = CCR.CREDIT_REQUEST_ID
AND RCM.CREDIT_RECOMMENDATION IN ('APPROVE', 'EXPIRATION_DATE')
AND CCR.SOURCE_COLUMN3 = 'LEASEAPP'
AND CCR.SOURCE_COLUMN1 = cp_lap_id;
SELECT RCM.APPEALED_FLAG
FROM AR_CMGT_CF_RECOMMENDS RCM
, AR_CMGT_CREDIT_REQUESTS CCR
WHERE RCM.CREDIT_REQUEST_ID = CCR.CREDIT_REQUEST_ID
AND RCM.CREDIT_RECOMMENDATION = 'EXPIRATION_DATE'
AND CCR.SOURCE_COLUMN3 = 'LEASEAPP'
AND CCR.SOURCE_COLUMN1 = p_lap_id;
l_seq_stmt := 'SELECT ' || p_seq_name || '.NEXTVAL FROM DUAL';
l_query_stmt := 'SELECT ' ||
p_col_name ||
' FROM ' ||
p_table_name ||
' WHERE '||
p_col_name || ' = :1 ';
SELECT DECODE(c_table_name,'OKL_LEASE_QUOTES_B',LSEQTE_SEQ_PREFIX_TXT,'OKL_QUICK_QUOTES_B',QCKQTE_SEQ_PREFIX_TXT,'OKL_LEASE_OPPORTUNITIES_B',LSEOPP_SEQ_PREFIX_TXT,'OKL_LEASE_APPLICATIONS_B',LSEAPP_SEQ_PREFIX_TXT)
FROM okl_system_params;
l_seq_stmt := 'SELECT ' || p_seq_name || '.NEXTVAL FROM DUAL';
l_query_stmt := 'SELECT ' ||
p_col_name ||
' FROM ' ||
p_table_name ||
' WHERE '||
p_col_name || ' = :1 ';
SELECT VALID_FROM VALID_FROM
, PROSPECT_ID PROSPECT_ID
, CUST_ACCT_ID CUST_ACCT_ID
, CURRENCY_CODE CURRENCY_CODE
FROM OKL_LEASE_APPLICATIONS_B LAB
WHERE LAB.ID = cp_lse_app_id;
SELECT LSQ.ID LSQ_ID
, LSQ.TERM
FROM OKL_LEASE_QUOTES_B LSQ
WHERE LSQ.PARENT_OBJECT_CODE = 'LEASEAPP'
AND LSQ.PRIMARY_QUOTE= 'Y'
AND LSQ.PARENT_OBJECT_ID = p_lap_id;
SELECT INV_ITEM_ID
FROM OKL_ASSET_COMPONENTS_B ASTC
, OKL_ASSETS_B AST
WHERE ASTC.ASSET_ID = AST.ID
AND AST.PARENT_OBJECT_CODE = 'LEASEQUOTE'
AND AST.PARENT_OBJECT_ID = p_lsq_id;
SELECT SUM(ADJ.VALUE)
FROM OKL_ASSETS_B AST
, OKL_COST_ADJUSTMENTS_B ADJ
WHERE AST.PARENT_OBJECT_CODE = 'LEASEQUOTE'
AND ADJ.PARENT_OBJECT_ID = AST.ID
AND AST.PARENT_OBJECT_ID = p_lsq_id
AND ADJ.ADJUSTMENT_SOURCE_TYPE = cp_adj_src_type;
SELECT NVL(SUM(ASS.OEC), 0) ASSET_AMOUNT
FROM OKL_ASSETS_B ASS
WHERE ASS.PARENT_OBJECT_CODE = 'LEASEQUOTE'
AND ASS.PARENT_OBJECT_ID = p_lsq_id;
SELECT NVL(SUM(FEE.FEE_AMOUNT), 0) FEE_AMOUNT
FROM OKL_FEES_B FEE
WHERE FEE.FEE_TYPE = p_fee_type
AND FEE.PARENT_OBJECT_CODE = 'LEASEQUOTE'
AND FEE.PARENT_OBJECT_ID = p_lsq_id;
SELECT NVL(SUM(VALUE), 0 )
FROM OKL_ASSETS_B AST
, OKL_COST_ADJUSTMENTS_B ADJ
WHERE AST.PARENT_OBJECT_CODE = 'LEASEQUOTE'
AND ADJ.PARENT_OBJECT_ID = AST.ID
AND ADJ.ADJUSTMENT_SOURCE_TYPE IN ('DOWN_PAYMENT', 'TRADEIN')
AND AST.PARENT_OBJECT_ID = p_lsq_id;
SELECT LAB.ID ID
, LAB.REFERENCE_NUMBER LEASE_APPL_NUMBER
, LAB.APPLICATION_STATUS LEASE_APPL_STATUS_CODE
, LAB.VALID_FROM VALID_FROM
, LAB.VALID_TO VALID_TO
, LAB.PROSPECT_ID PROSPECT_ID
, LAB.PROSPECT_ADDRESS_ID PROSPECT_ADDRESS_ID
, LAB.CUST_ACCT_ID CUST_ACCT_ID
, LAB.CURRENCY_CODE CURRENCY_CODE
, LAB.CURRENCY_CONVERSION_TYPE CONVERSION_TYPE
, LAB.CURRENCY_CONVERSION_RATE CONVERSION_RATE
, LAB.CURRENCY_CONVERSION_DATE CONVERSION_DATE
, LAB.LEASEAPP_TEMPLATE_ID LEASEAPP_TEMPLATE_ID
, LAB.LEASE_OPPORTUNITY_ID LEASE_OPPORTUNITY_ID
, LAB.CREDIT_LINE_ID CREDIT_LINE_ID
, LAB.PROGRAM_AGREEMENT_ID PROGRAM_AGREEMENT_ID
, LAB.MASTER_LEASE_ID MASTER_LEASE_ID
, LAB.SALES_REP_ID SALES_REP_ID
, LAB.SALES_TERRITORY_ID SALES_TERRITORY_ID
, LAB.INDUSTRY_CODE INDUSTRY_CODE
, LAB.INDUSTRY_CLASS INDUSTRY_CLASS
, LAT.SHORT_DESCRIPTION DESCRIPTION
, LAB.ORG_ID ORG_ID
, LAB.OBJECT_VERSION_NUMBER OBJECT_VERSION_NUMBER
, LAB.CREATED_BY CREATED_BY
, LAB.CREATION_DATE CREATION_DATE
, LAB.PARENT_LEASEAPP_ID PARENT_LEASEAPP_ID
, LAB.ACTION ACTION
, LAB.ORIG_STATUS ORIG_STATUS
, LQ.ID LSE_QTE_ID
, LQ.REFERENCE_NUMBER QUOTE_NUMBER
, LQ.STATUS STATUS_CODE
, LQ.VALID_FROM LQ_VALID_FROM
, LQ.VALID_TO LQ_VALID_TO
, LQ.PRICING_METHOD PRICING_METHOD_CODE
, LQ.TERM TERM
, LQ.PRODUCT_ID PRODUCT_ID
, LQ.END_OF_TERM_OPTION_ID END_OF_TERM_OPTION_ID
, LQ.PROPERTY_TAX_APPLICABLE PROPERTY_TAX_APPLICABLE_CODE
, LQ.PROPERTY_TAX_BILLING_TYPE PROPERTY_TAX_BILLING_TYPE_CODE
, LQ.UPFRONT_TAX_TREATMENT UPFRONT_TAX_TREATMENT_CODE
, LQ.PURCHASE_OF_LEASE PURCHASE_OF_LEASE_CODE
, LQ.SALE_AND_LEASE_BACK SALE_AND_LEASE_BACK_CODE
, LQ.INTEREST_DISCLOSED INTEREST_DISCLOSED_CODE
, LQ.TRANSFER_OF_TITLE TRANSFER_OF_TITLE_CODE
, LQ.USAGE_CATEGORY USAGE_CATEGORY
, LQ.AGE_OF_EQUIPMENT AGE_OF_EQUIPMENT
, LQ.UPFRONT_TAX_STREAM_TYPE UPFRONT_TAX_STY_ID
, LQ.PARENT_OBJECT_CODE PARENT_OBJECT_CODE
, LQ.PARENT_OBJECT_ID PARENT_OBJECT_ID
, LQ.EXPECTED_START_DATE LQ_EXPECTED_START_DATE
, LQ.OBJECT_VERSION_NUMBER LQ_OBJECT_VERSION_NUMBER
--asawanka bug 4721141 start
, LQ.EXPECTED_FUNDING_DATE LQ_EXPECTED_FUNDING_DATE
, LQ.EXPECTED_DELIVERY_DATE LQ_EXPECTED_DELIVERY_DATE
--asawanka bug 4721141 end
--Added Bug 5647107 ssdeshpa start
, LQ.LEGAL_ENTITY_ID LEGAL_ENTITY_ID
--Added Bug 5647107 ssdeshpa start
FROM OKL_LEASE_APPLICATIONS_B LAB
, OKL_LEASE_APPLICATIONS_TL LAT
, OKL_LEASE_QUOTES_B LQ
WHERE LAB.ID = LAT.ID
AND LAT.LANGUAGE = USERENV('LANG')
AND LQ.PARENT_OBJECT_ID = LAB.ID
AND LQ.PARENT_OBJECT_CODE = 'LEASEAPP'
AND LQ.PRIMARY_QUOTE = 'Y'
AND LAB.ID = cp_lap_id;
SELECT CLD.ID ID
, CLD.OBJECT_VERSION_NUMBER OBJECT_VERSION_NUMBER
, CLD.CKL_ID CKL_ID
, CLD.TODO_ITEM_CODE TODO_ITEM_CODE
, CLD.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
, CLD.ATTRIBUTE1 ATTRIBUTE1
, CLD.ATTRIBUTE2 ATTRIBUTE2
, CLD.ATTRIBUTE3 ATTRIBUTE3
, CLD.ATTRIBUTE4 ATTRIBUTE4
, CLD.ATTRIBUTE5 ATTRIBUTE5
, CLD.ATTRIBUTE6 ATTRIBUTE6
, CLD.ATTRIBUTE7 ATTRIBUTE7
, CLD.ATTRIBUTE8 ATTRIBUTE8
, CLD.ATTRIBUTE9 ATTRIBUTE9
, CLD.ATTRIBUTE10 ATTRIBUTE10
, CLD.ATTRIBUTE11 ATTRIBUTE11
, CLD.ATTRIBUTE12 ATTRIBUTE12
, CLD.ATTRIBUTE13 ATTRIBUTE13
, CLD.ATTRIBUTE14 ATTRIBUTE14
, CLD.ATTRIBUTE15 ATTRIBUTE15
, CLD.ORG_ID ORG_ID
, CLD.MANDATORY_FLAG MANDATORY_FLAG
, CLD.USER_COMPLETE_FLAG USER_COMPLETE_FLAG
, CLD.ADMIN_NOTE ADMIN_NOTE
, CLD.USER_NOTE USER_NOTE
, CLD.FUNCTION_VALIDATE_RSTS FUNCTION_VALIDATE_RSTS
, CLD.FUNCTION_VALIDATE_MSG FUNCTION_VALIDATE_MSG
, CLD.DNZ_CHECKLIST_OBJ_ID DNZ_CHECKLIST_OBJ_ID
, CLD.FUNCTION_ID FUNCTION_ID
, CLD.INST_CHECKLIST_TYPE INST_CHECKLIST_TYPE
, CLD.APPEAL_FLAG APPEAL_FLAG
FROM OKL_CHECKLIST_DETAILS CLD
WHERE CLD.ID = cp_cld_id;
,'begin debug call OKL_LAP_PVT.UPDATE_ROW');
OKL_LAP_PVT.UPDATE_ROW(
p_api_version => p_api_version
,p_init_msg_list => OKL_API.G_FALSE
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_lapv_rec => l_lapv_rec
,x_lapv_rec => x_lapv_rec);
,'end debug call OKL_LAP_PVT.UPDATE_ROW');
,L_MODULE || ' Result of OKL_LAP_PVT.UPDATE_ROW'
,'l_return_status ' || l_return_status);
SELECT PLAB.ID PARENT_LSE_APP_ID
, PLAB.APPLICATION_STATUS PARENT_LSE_APP_STS
FROM OKL_LEASE_APPLICATIONS_B LAB
, OKL_LEASE_APPLICATIONS_B PLAB
WHERE LAB.ID = cp_lse_app_id
AND PLAB.ID = LAB.PARENT_LEASEAPP_ID;
,'begin debug call OKL_LAP_PVT.UPDATE_ROW');
OKL_LAP_PVT.UPDATE_ROW(
p_api_version => p_api_version
,p_init_msg_list => OKL_API.G_FALSE
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_lapv_rec => l_lapv_rec
,x_lapv_rec => x_lapv_rec);
,'end debug call OKL_LAP_PVT.UPDATE_ROW');
,L_MODULE || ' Result of OKL_LAP_PVT.UPDATE_ROW'
,'l_return_status ' || l_return_status);
SELECT LQ.REFERENCE_NUMBER REFERENCE_NUMBER
FROM OKL_LEASE_QUOTES_B LQ
WHERE LQ.ID = cp_lsq_id
-- AND NVL(LQ.PRICING_METHOD, OKL_API.G_MISS_CHAR) = NVL(cp_lsqv_rec.PRICING_METHOD, OKL_API.G_MISS_CHAR)
AND NVL(LQ.EXPECTED_START_DATE, OKL_API.G_MISS_DATE) = NVL(cp_lsqv_rec.EXPECTED_START_DATE, OKL_API.G_MISS_DATE)
AND NVL(LQ.VALID_FROM, OKL_API.G_MISS_DATE) = NVL(cp_lsqv_rec.VALID_FROM, OKL_API.G_MISS_DATE)
AND NVL(LQ.VALID_TO, OKL_API.G_MISS_DATE) = NVL(cp_lsqv_rec.VALID_TO, OKL_API.G_MISS_DATE)
AND NVL(LQ.TERM, OKL_API.G_MISS_NUM) = NVL(cp_lsqv_rec.TERM, OKL_API.G_MISS_NUM)
AND NVL(LQ.PRODUCT_ID, OKL_API.G_MISS_NUM) = NVL(cp_lsqv_rec.PRODUCT_ID, OKL_API.G_MISS_NUM)
AND NVL(LQ.END_OF_TERM_OPTION_ID, OKL_API.G_MISS_NUM) = NVL(cp_lsqv_rec.END_OF_TERM_OPTION_ID, OKL_API.G_MISS_NUM)
AND NVL(LQ.PROPERTY_TAX_APPLICABLE, OKL_API.G_MISS_CHAR) = NVL(cp_lsqv_rec.PROPERTY_TAX_APPLICABLE, OKL_API.G_MISS_CHAR)
AND NVL(LQ.PROPERTY_TAX_BILLING_TYPE, OKL_API.G_MISS_CHAR) = NVL(cp_lsqv_rec.PROPERTY_TAX_BILLING_TYPE, OKL_API.G_MISS_CHAR)
AND NVL(LQ.USAGE_CATEGORY, OKL_API.G_MISS_CHAR) = NVL(cp_lsqv_rec.USAGE_CATEGORY, OKL_API.G_MISS_CHAR)
AND NVL(LQ.UPFRONT_TAX_TREATMENT, OKL_API.G_MISS_CHAR) = NVL(cp_lsqv_rec.UPFRONT_TAX_TREATMENT, OKL_API.G_MISS_CHAR)
AND NVL(LQ.UPFRONT_TAX_STREAM_TYPE, OKL_API.G_MISS_NUM) = NVL(cp_lsqv_rec.UPFRONT_TAX_STREAM_TYPE, OKL_API.G_MISS_NUM)
AND NVL(LQ.TRANSFER_OF_TITLE, OKL_API.G_MISS_CHAR) = NVL(cp_lsqv_rec.TRANSFER_OF_TITLE, OKL_API.G_MISS_CHAR)
AND NVL(LQ.AGE_OF_EQUIPMENT, OKL_API.G_MISS_NUM) = NVL(cp_lsqv_rec.AGE_OF_EQUIPMENT, OKL_API.G_MISS_NUM)
AND NVL(LQ.PURCHASE_OF_LEASE, OKL_API.G_MISS_CHAR) = NVL(cp_lsqv_rec.PURCHASE_OF_LEASE, OKL_API.G_MISS_CHAR)
AND NVL(LQ.SALE_AND_LEASE_BACK, OKL_API.G_MISS_CHAR) = NVL(cp_lsqv_rec.SALE_AND_LEASE_BACK, OKL_API.G_MISS_CHAR)
AND NVL(LQ.INTEREST_DISCLOSED, OKL_API.G_MISS_CHAR) = NVL(cp_lsqv_rec.INTEREST_DISCLOSED, OKL_API.G_MISS_CHAR)
--Added Bug # 5647107 start
AND NVL(LQ.LEGAL_ENTITY_ID, OKL_API.G_MISS_NUM) = NVL(cp_lsqv_rec.LEGAL_ENTITY_ID, OKL_API.G_MISS_NUM)
--Added Bug # 5647107 end
-- Bug 5908845. eBTax Enhancement Project
AND NVL(LQ.LINE_INTENDED_USE, OKL_API.G_MISS_CHAR) = NVL(cp_lsqv_rec.LINE_INTENDED_USE, OKL_API.G_MISS_CHAR);
SELECT LAP.REFERENCE_NUMBER REFERENCE_NUMBER
FROM OKL_LEASE_APPLICATIONS_B LAP
WHERE LAP.ID = cp_lap_id
AND NVL(LAP.PROSPECT_ADDRESS_ID, OKL_API.G_MISS_NUM) = NVL(cp_lapv_rec.prospect_address_id, OKL_API.G_MISS_NUM)
AND NVL(LAP.CUST_ACCT_ID, OKL_API.G_MISS_NUM) = NVL(cp_lapv_rec.cust_acct_id, OKL_API.G_MISS_NUM)
AND NVL(LAP.MASTER_LEASE_ID, OKL_API.G_MISS_NUM) = NVL(cp_lapv_rec.master_lease_id, OKL_API.G_MISS_NUM)
AND NVL(LAP.CURRENCY_CODE, OKL_API.G_MISS_CHAR) = NVL(cp_lapv_rec.currency_code, OKL_API.G_MISS_CHAR)
AND NVL(LAP.CURRENCY_CONVERSION_TYPE, OKL_API.G_MISS_CHAR) = NVL(cp_lapv_rec.currency_conversion_type, OKL_API.G_MISS_CHAR)
AND NVL(LAP.CURRENCY_CONVERSION_RATE, OKL_API.G_MISS_NUM) = NVL(cp_lapv_rec.currency_conversion_rate, OKL_API.G_MISS_NUM)
AND NVL(LAP.CURRENCY_CONVERSION_DATE, OKL_API.G_MISS_DATE) = NVL(cp_lapv_rec.currency_conversion_date, OKL_API.G_MISS_DATE);
SELECT LOP.REFERENCE_NUMBER REFERENCE_NUMBER
FROM OKL_LEASE_OPPORTUNITIES_B LOP
WHERE LOP.ID = cp_lop_id
AND NVL(LOP.PROSPECT_ADDRESS_ID, OKL_API.G_MISS_NUM) = NVL(cp_lapv_rec.prospect_address_id, OKL_API.G_MISS_NUM)
AND NVL(LOP.CUST_ACCT_ID, OKL_API.G_MISS_NUM) = NVL(cp_lapv_rec.cust_acct_id, OKL_API.G_MISS_NUM)
AND NVL(LOP.MASTER_LEASE_ID, OKL_API.G_MISS_NUM) = NVL(cp_lapv_rec.master_lease_id, OKL_API.G_MISS_NUM)
AND NVL(LOP.CURRENCY_CODE, OKL_API.G_MISS_CHAR) = NVL(cp_lapv_rec.currency_code, OKL_API.G_MISS_CHAR)
AND NVL(LOP.CURRENCY_CONVERSION_TYPE, OKL_API.G_MISS_CHAR) = NVL(cp_lapv_rec.currency_conversion_type, OKL_API.G_MISS_CHAR)
AND NVL(LOP.CURRENCY_CONVERSION_RATE, OKL_API.G_MISS_NUM) = NVL(cp_lapv_rec.currency_conversion_rate, OKL_API.G_MISS_NUM)
AND NVL(LOP.CURRENCY_CONVERSION_DATE, OKL_API.G_MISS_DATE) = NVL(cp_lapv_rec.currency_conversion_date, OKL_API.G_MISS_DATE);
SELECT LQ.PARENT_OBJECT_CODE PARENT_OBJECT_CODE
, LQ.PARENT_OBJECT_ID PARENT_OBJECT_ID
FROM OKL_LEASE_QUOTES_B LQ
WHERE LQ.ID = cp_lsq_id;
SELECT okl_wf_item_s.nextval
FROM dual;
l_parameter_list.DELETE;
SELECT LAB.REFERENCE_NUMBER REFERENCE_NUMBER
,LSQ.EXPECTED_START_DATE EXPECTED_START_DATE
,LAT.CREDIT_REVIEW_PURPOSE REVIEW_TYPE
,LAT.CUST_CREDIT_CLASSIFICATION CREDIT_CLASSIFICATION
,LAB.CURRENCY_CODE CURRENCY_CODE
,LSQ.TERM TERM
,LAB.PROSPECT_ID PROSPECT_ID
,LAB.CUST_ACCT_ID CUST_ACCT_ID
,LAB.ORG_ID ORG_ID
,LAB.CREDIT_LINE_ID CREDIT_LINE_ID
,LSQ.ID LSQ_ID
FROM OKL_LEASE_APPLICATIONS_B LAB
,OKL_LEASEAPP_TEMPLATES LAT
,OKL_LEASE_QUOTES_B LSQ
,OKL_LEASEAPP_TEMPL_VERSIONS_V LATV
WHERE LATV.ID = LAB.LEASEAPP_TEMPLATE_ID
AND LAT.ID = LATV.LEASEAPP_TEMPLATE_ID
AND LSQ.PARENT_OBJECT_CODE = 'LEASEAPP'
AND LSQ.PARENT_OBJECT_ID = LAB.ID
AND LSQ.PRIMARY_QUOTE = 'Y'
AND LAB.ID = cp_lse_app_id;
SELECT LAP.ORG_ID ORG_ID
, LAP.VALID_FROM VALID_FROM
, LAP.VALID_TO VALID_TO
FROM OKL_LEASE_APPLICATIONS_B LAP
WHERE LAP.ID = cp_lap_id;
SELECT CHK.ID ID
,CHK.START_DATE START_DATE
,CHK.END_DATE END_DATE
,CHK.DESCRIPTION
,CHK.SHORT_DESCRIPTION
FROM OKL_CHECKLISTS CHK
WHERE CHK.ID = cp_chk_id;
SELECT CHKD.TODO_ITEM_CODE ITEM_CODE
, CHK.CHECKLIST_TYPE CHECKLIST_TYPE
, CHKD.FUNCTION_ID FUNCTION_ID
FROM OKL_CHECKLIST_DETAILS CHKD
, OKL_CHECKLISTS CHK
WHERE CHKD.CKL_ID = CHK.ID
--removing the condition for bug 5167776
-- AND CHK.CHECKLIST_PURPOSE_CODE <> 'CHECKLIST_INSTANCE'
AND (CHK.ID = cp_chk_id OR CHK.CKL_ID = cp_chk_id );
SELECT CHK.ID ID
,CHK.START_DATE START_DATE
,CHK.END_DATE END_DATE
,CHK.DESCRIPTION DESCRIPTION
,CHK.SHORT_DESCRIPTION SHORT_DESCRIPTION
,CHK.OBJECT_VERSION_NUMBER OBJECT_VERSION_NUMBER
FROM OKL_CHECKLISTS CHK
WHERE CHK.CHECKLIST_OBJ_TYPE_CODE = 'LEASE_APPL'
AND CHK.CHECKLIST_OBJ_ID = cp_lap_id;
,'begin debug call OKL_CHECKLIST_PVT.UPDATE_CHECKLIST_INST_HDR');
OKL_CHECKLIST_PVT.UPDATE_CHECKLIST_INST_HDR(
p_api_version => p_api_version
,p_init_msg_list => OKL_API.G_FALSE
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_clhv_rec => l_clhv_rec
,x_clhv_rec => x_clhv_rec);
,'end debug call OKL_CHECKLIST_PVT.UPDATE_CHECKLIST_INST_HDR');
,L_MODULE || ' Result of OKL_CHECKLIST_PVT.UPDATE_CHECKLIST_INST_HDR'
,'l_return_status ' || l_return_status);
SELECT LATV.CHECKLIST_ID
FROM OKL_LEASE_APPLICATIONS_B LAP
,OKL_LEASEAPP_TEMPL_VERSIONS_V LATV
WHERE LAP.LEASEAPP_TEMPLATE_ID = LATV.ID
AND LAP.ID = cp_lap_id;
SELECT LSQ.ID LSQ_ID
, LOP.ID LOP_ID
FROM OKL_LEASE_OPPORTUNITIES_B LOP
, OKL_LEASE_QUOTES_B LSQ
WHERE
LSQ.PARENT_OBJECT_ID = LOP.ID
AND LSQ.PARENT_OBJECT_CODE = 'LEASEOPP'
AND LSQ.STATUS = 'CT-ACCEPTED'
AND LOP.ID = cp_lap_id;
,'begin debug call OKL_LAP_PVT.INSERT_ROW');
OKL_LAP_PVT.INSERT_ROW(
p_api_version => p_api_version
,p_init_msg_list => OKL_API.G_FALSE
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_lapv_rec => l_lapv_rec
,x_lapv_rec => x_lapv_rec);
,'end debug call OKL_LAP_PVT.INSERT_ROW');
,L_MODULE || ' Result of OKL_LAP_PVT.INSERT_ROW'
,'l_lapv_rec.reference_number ' || to_char(l_lapv_rec.reference_number) ||
' l_lapv_rec.id '|| l_lapv_rec.id ||
' result status ' || l_return_status ||
' x_msg_data ' || x_msg_data);
,'begin debug call OKL_LEASE_QUOTE_PVT.UPDATE_LEASE_QTE');
OKL_LEASE_QUOTE_PVT.UPDATE_LEASE_QTE(
p_api_version => p_api_version
,p_init_msg_list => OKL_API.G_FALSE
,p_transaction_control => OKL_API.G_TRUE
,p_lease_qte_rec => l_lsqv_rec
,x_lease_qte_rec => x_lsqv_rec
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
,'end debug call OKL_LEASE_QUOTE_PVT.UPDATE_LEASE_QTE');
,L_MODULE || ' Result of OKL_LEASE_QUOTE_PVT.UPDATE_LEASE_QTE'
,'l_lsqv_rec.reference_number ' || to_char(l_lsqv_rec.reference_number) ||
' l_lsqv_rec.id '|| l_lsqv_rec.id ||
' result status ' || l_return_status ||
' x_msg_data ' || x_msg_data);
l_update_quote NUMBER;
l_credit_request_rec OCM_CREDIT_REQUEST_UPDATE_PUB.credit_request_rec;
SELECT LSQ.ID LSQ_ID
, LSQ.STATUS STATUS
FROM OKL_LEASE_QUOTES_B LSQ
WHERE LSQ.PARENT_OBJECT_CODE = 'LEASEAPP'
AND LSQ.PRIMARY_QUOTE= 'Y'
AND LSQ.PARENT_OBJECT_ID = p_lap_id;
SELECT leaseapp_template_id
FROM okl_lease_applications_b LSQ
WHERE ID = p_lap_id;
SELECT LATV.CHECKLIST_ID
,LAT.CREDIT_REVIEW_PURPOSE
,LAT.CUST_CREDIT_CLASSIFICATION
FROM OKL_LEASEAPP_TEMPL_VERSIONS_V LATV
,OKL_LEASEAPP_TEMPLATES LAT
WHERE LATV.LEASEAPP_TEMPLATE_ID = LAT.ID
AND LATV.ID = cp_latv_id;
SELECT CREDIT_REQUEST_ID
,APPLICATION_NUMBER
,TRX_AMOUNT
,TRX_CURRENCY
,STATUS
,RECOMMENDATION_NAME
,SOURCE_COLUMN1
,SOURCE_COLUMN2
,REVIEW_TYPE
,CREDIT_CLASSIFICATION
,CUST_ACCOUNT_ID
FROM AR_CMGT_CREDIT_REQUESTS
WHERE SOURCE_COLUMN3 = 'LEASEAPP'
AND SOURCE_COLUMN1 = cp_lap_id;
l_update_quote := 0;
l_update_quote := 1;
,'begin debug call OKL_LAP_PVT.UPDATE_ROW');
OKL_LAP_PVT.UPDATE_ROW(
p_api_version => p_api_version
,p_init_msg_list => OKL_API.G_FALSE
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_lapv_rec => l_lapv_rec
,x_lapv_rec => x_lapv_rec);
,'end debug call OKL_LAP_PVT.UPDATE_ROW');
,L_MODULE || ' Result of OKL_LAP_PVT.UPDATE_ROW'
,'l_lapv_rec.reference_number ' || to_char(l_lapv_rec.reference_number) ||
' expiring lease application with status ' || l_return_status ||
' x_msg_data ' || x_msg_data);
IF(l_update_quote = 1)
THEN
l_lsqv_rec.status := 'PR-INCOMPLETE';
,'begin debug call OKL_LEASE_QUOTE_PVT.UPDATE_LEASE_QTE');
OKL_LEASE_QUOTE_PVT.UPDATE_LEASE_QTE(
p_api_version => p_api_version
,p_init_msg_list => OKL_API.G_FALSE
,p_transaction_control => OKL_API.G_TRUE
,p_lease_qte_rec => l_lsqv_rec
,x_lease_qte_rec => x_lsqv_rec
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
,'end debug call OKL_LEASE_QUOTE_PVT.UPDATE_LEASE_QTE');
,L_MODULE || ' Result of OKL_LEASE_QUOTE_PVT.UPDATE_LEASE_QTE'
,'l_lsqv_rec.reference_number ' || to_char(l_lsqv_rec.reference_number) ||
' l_lsqv_rec.id '|| l_lsqv_rec.id ||
' result status ' || l_return_status ||
' x_msg_data ' || x_msg_data);
,'begin debug call OCM_CREDIT_REQUEST_UPDATE_PUB.GET_CREDIT_REQUEST_REC');
OCM_CREDIT_REQUEST_UPDATE_PUB.GET_CREDIT_REQUEST_REC(
p_credit_request_id => get_crd_app_data_rec.credit_request_id
,p_return_status => l_return_status
,p_error_msg => x_msg_data
,p_credit_request_rec => l_credit_request_rec);
,'end debug call OCM_CREDIT_REQUEST_UPDATE_PUB.GET_CREDIT_REQUEST_REC');
,L_MODULE || ' Result of OCM_CREDIT_REQUEST_UPDATE_PUB.GET_CREDIT_REQUEST_REC'
,' result status ' || l_return_status);
,'begin debug call OCM_CREDIT_REQUEST_UPDATE_PUB.UPDATE_CREDIT_REQUEST');
OCM_CREDIT_REQUEST_UPDATE_PUB.UPDATE_CREDIT_REQUEST(
p_api_version => l_api_version,
p_init_msg_list => OKL_API.G_FALSE,
p_commit => NULL,
p_validation_level => NULL,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_credit_request_rec => l_credit_request_rec);
,'end debug call OCM_CREDIT_REQUEST_UPDATE_PUB.UPDATE_CREDIT_REQUEST');
,L_MODULE || ' Result of OCM_CREDIT_REQUEST_UPDATE_PUB.UPDATE_CREDIT_REQUEST'
,' result status ' || l_return_status);
SELECT LAT.NAME LEASEAPP_TMPL_NAME
FROM OKL_LEASEAPP_TEMPLATES LAT
,OKL_LEASEAPP_TEMPL_VERSIONS_V LATV
WHERE (LATV.VERSION_STATUS <> 'ACTIVE' OR
LATV.VALID_FROM > cp_valid_from OR
NVL(LATV.VALID_TO, TO_DATE('31-12-9999', 'dd-mm-yyyy')) < cp_valid_from)
AND LATV.ID = cp_tmpl_id
AND LATV.LEASEAPP_TEMPLATE_ID = LAT.ID;
SELECT CHR.ID
, CHR.CONTRACT_NUMBER
, CHR.STS_CODE
, PSTS.STE_CODE
, CHR.START_DATE START_DATE
, CHR.END_DATE END_DATE
FROM OKC_K_HEADERS_B CHR
, OKC_STATUSES_V PSTS
WHERE CHR.SCS_CODE = 'PROGRAM'
AND CHR.STS_CODE = PSTS.CODE
AND CHR.ID = cp_pgm_agr_id;
SELECT LSQ.ID
, LSQ.REFERENCE_NUMBER
, LSQ.STATUS
FROM OKL_LEASE_QUOTES_B LSQ
, OKL_LEASE_OPPORTUNITIES_B LOP
WHERE LOP.ID = cp_lop_id
AND LSQ.PARENT_OBJECT_CODE = 'LEASEOPP'
AND LSQ.PARENT_OBJECT_ID = LOP.ID
AND LSQ.STATUS = 'CT-ACCEPTED';
SELECT 'X'
FROM OKC_K_HEADERS_B PGM
, OKL_VP_ASSOCIATIONS_V VPA
, OKL_LEASEAPP_TEMPLATES LAT
, OKL_LEASEAPP_TEMPL_VERSIONS_B LATV
WHERE VPA.CHR_ID = PGM.ID
AND VPA.CRS_ID IS NULL
AND VPA.ASSOC_OBJECT_TYPE_CODE = 'LA_TEMPLATE'
AND PGM.SCS_CODE = 'PROGRAM'
AND VPA.ASSOC_OBJECT_ID = LAT.ID
AND VPA.ASSOC_OBJECT_VERSION = LATV.VERSION_NUMBER
AND LATV.LEASEAPP_TEMPLATE_ID = LAT.ID
AND PGM.ID = cp_pgm_agr_id;
SELECT 'X'
FROM OKC_K_HEADERS_B PGM
, OKL_VP_ASSOCIATIONS_V VPA
, OKL_LEASEAPP_TEMPLATES LAT
, OKL_LEASEAPP_TEMPL_VERSIONS_B LATV
WHERE VPA.CHR_ID = PGM.ID
AND VPA.CRS_ID IS NULL
AND VPA.ASSOC_OBJECT_TYPE_CODE = 'LA_TEMPLATE'
AND PGM.SCS_CODE = 'PROGRAM'
AND VPA.ASSOC_OBJECT_ID = LAT.ID
AND VPA.ASSOC_OBJECT_VERSION = LATV.VERSION_NUMBER
AND LATV.LEASEAPP_TEMPLATE_ID = LAT.ID
AND PGM.ID = cp_pgm_agr_id
AND LATV.ID = cp_latv_id;
SELECT REFERENCE_NUMBER
FROM OKL_LEASE_APPLICATIONS_B
--asawanka bug 4936130 changes start
WHERE APPLICATION_STATUS NOT IN ('CR-APPROVED'
,'CR-REJECTED'
,'RECOM_NOT_APPROVED'
,'CONV-K'
,'WITHDRAWN'
,'CANCELED'
,'APPEALINPROG'
,'RESUBMITINPROG')
--asawanka bug 4936130 changes end
AND LEASE_OPPORTUNITY_ID = cp_lop_id
AND ID <> NVL(p_lap_id, -1);
SELECT CHECKLIST_NUMBER TEMPLATE_NUMBER
FROM OKL_CHECKLISTS CHK
, OKL_LEASEAPP_TEMPL_VERSIONS_V LATV
, OKL_LEASEAPP_TEMPLATES LAT
WHERE LATV.CHECKLIST_ID = CHK.ID
AND LATV.LEASEAPP_TEMPLATE_ID = LAT.ID
AND ( (p_valid_from NOT BETWEEN NVL(CHK.START_DATE, p_valid_from)
AND NVL(CHK.END_DATE, p_valid_from))
OR CHK.STATUS_CODE <> 'ACTIVE'
OR CHK.CHECKLIST_PURPOSE_CODE NOT IN ('CHECKLIST_TEMPLATE', 'CHECKLIST_TEMPLATE_GROUP')
OR CHK.ORG_ID <> LAT.ORG_ID)
AND LATV.ID = p_latv_id;
SELECT CHR.CONTRACT_NUMBER TEMPLATE_NUMBER
FROM OKC_K_HEADERS_B CHR
, OKL_LEASEAPP_TEMPL_VERSIONS_V LATV
, OKL_LEASEAPP_TEMPLATES LAT
, OKL_K_HEADERS KHR
, OKC_STATUSES_V STS
WHERE LATV.CONTRACT_TEMPLATE_ID = CHR.ID
AND LATV.LEASEAPP_TEMPLATE_ID = LAT.ID
AND CHR.ID = KHR.ID
AND CHR.STS_CODE = STS.CODE
/* Bug#6850094 : Include contract template with any status
AND (CHR.TEMPLATE_YN <> 'Y'
OR STS.STE_CODE <> 'ACTIVE'
OR NVL(KHR.TEMPLATE_TYPE_CODE, 'X') <> 'LEASEAPP'
OR CHR.AUTHORING_ORG_ID <> LAT.ORG_ID)
*/
AND (CHR.TEMPLATE_YN <> 'Y'
OR (NVL(KHR.TEMPLATE_TYPE_CODE,'X') = 'LEASEAPP' AND STS.STE_CODE <> 'ACTIVE')
OR NVL(KHR.TEMPLATE_TYPE_CODE, 'X') NOT IN ('LEASEAPP','CONTRACT')
OR CHR.AUTHORING_ORG_ID <> LAT.ORG_ID)
--Bug# 6850094 :End
AND LATV.ID = p_latv_id;
,'begin debug call OKL_LEASE_QUOTE_PVT.UPDATE_LEASE_QTE');
OKL_LEASE_QUOTE_PVT.UPDATE_LEASE_QTE(
p_api_version => p_api_version
,p_init_msg_list => OKL_API.G_FALSE
,p_transaction_control => OKL_API.G_TRUE
,p_lease_qte_rec => l_lsqv_rec
,x_lease_qte_rec => x_lsqv_rec
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
,'end debug call OKL_LEASE_QUOTE_PVT.UPDATE_LEASE_QTE');
,L_MODULE || ' Result of OKL_LEASE_QUOTE_PVT.UPDATE_LEASE_QTE'
,'l_lsqv_rec.reference_number ' || to_char(l_lsqv_rec.reference_number) ||
' l_lsqv_rec.id '|| l_lsqv_rec.id ||
' result status ' || l_return_status ||
' x_msg_data ' || x_msg_data);
,'begin debug call OKL_LAP_PVT.UPDATE_ROW');
OKL_LAP_PVT.UPDATE_ROW(
p_api_version => p_api_version
,p_init_msg_list => OKL_API.G_FALSE
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_lapv_rec => l_lapv_rec
,x_lapv_rec => x_lapv_rec);
,'end debug call OKL_LAP_PVT.UPDATE_ROW');
,L_MODULE || ' Result of OKL_LAP_PVT.UPDATE_ROW'
,'l_return_status ' || l_return_status);
SELECT
EXPECTED_FUNDING_DATE,
EXPECTED_DELIVERY_DATE
FROM okl_lease_quotes_v
WHERE id = p_source_quote_id;
SELECT LATV.CHECKLIST_ID
FROM OKL_LEASE_APPLICATIONS_B LAP
,OKL_LEASEAPP_TEMPL_VERSIONS_V LATV
WHERE LAP.LEASEAPP_TEMPLATE_ID = LATV.ID
AND LAP.ID = cp_lap_id;
SELECT LSQ.ID LSQ_ID
FROM OKL_LEASE_QUOTES_B LSQ
, OKL_LEASE_APPLICATIONS_B LAP
WHERE LSQ.PARENT_OBJECT_ID = LAP.ID
AND LSQ.PARENT_OBJECT_CODE = 'LEASEAPP'
AND LSQ.PRIMARY_QUOTE = 'Y'
AND LAP.ID = cp_lap_id;
SELECT LSQ.ID LSQ_ID
, LOP.ID LOP_ID
FROM OKL_LEASE_OPPORTUNITIES_B LOP
, OKL_LEASE_QUOTES_B LSQ
WHERE
LSQ.PARENT_OBJECT_ID = LOP.ID
AND LSQ.PARENT_OBJECT_CODE = 'LEASEOPP'
AND LSQ.STATUS = 'CT-ACCEPTED'
AND LOP.ID = cp_lap_id;
,'begin debug call OKL_LAP_PVT.INSERT_ROW');
OKL_LAP_PVT.INSERT_ROW(
p_api_version => p_api_version
,p_init_msg_list => OKL_API.G_FALSE
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_lapv_rec => l_lapv_rec
,x_lapv_rec => x_lapv_rec);
,'end debug call OKL_LAP_PVT.INSERT_ROW');
,L_MODULE || ' Result of OKL_LAP_PVT.INSERT_ROW'
,'l_lapv_rec.reference_number ' || to_char(l_lapv_rec.reference_number) ||
' l_lapv_rec.id '|| l_lapv_rec.id ||
' result status ' || l_return_status ||
' x_msg_data ' || x_msg_data);
,'begin debug call OKL_LEASE_QUOTE_PVT.UPDATE_LEASE_QTE');
OKL_LEASE_QUOTE_PVT.UPDATE_LEASE_QTE(
p_api_version => p_api_version
,p_init_msg_list => OKL_API.G_FALSE
,p_transaction_control => OKL_API.G_TRUE
,p_lease_qte_rec => l_lsqv_rec
,x_lease_qte_rec => x_lsqv_rec
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
,'end debug call OKL_LEASE_QUOTE_PVT.UPDATE_LEASE_QTE');
,L_MODULE || ' Result of OKL_LEASE_QUOTE_PVT.UPDATE_LEASE_QTE'
,'l_lsqv_rec.reference_number ' || to_char(l_lsqv_rec.reference_number) ||
' l_lsqv_rec.id '|| l_lsqv_rec.id ||
' result status ' || l_return_status ||
' x_msg_data ' || x_msg_data);
SELECT LSQ.ID LSQ_ID
, LSQ.STATUS STATUS
FROM OKL_LEASE_QUOTES_B LSQ
WHERE LSQ.PARENT_OBJECT_CODE = 'LEASEAPP'
AND LSQ.PRIMARY_QUOTE= 'Y'
AND LSQ.PARENT_OBJECT_ID = p_lap_id;
l_crd_req_rec OCM_CREDIT_REQUEST_UPDATE_PUB.credit_request_rec;
SELECT CREDIT_REQUEST_ID CREDIT_REQUEST_ID
FROM AR_CMGT_CREDIT_REQUESTS ACR
WHERE ACR.SOURCE_COLUMN3 = 'LEASEAPP' -- Fix for Bug 4749255
AND ACR.SOURCE_COLUMN1 = cp_lap_id;
SELECT LSQ.ID LSQ_ID
, LSQ.REFERENCE_NUMBER REFERENCE_NUMBER
FROM OKL_LEASE_QUOTES_B LSQ
WHERE LSQ.PARENT_OBJECT_CODE = 'LEASEAPP'
AND LSQ.PRIMARY_QUOTE= 'Y'
AND LSQ.PARENT_OBJECT_ID = p_lap_id;
SELECT COUNT(*)
FROM AR_CMGT_CF_RECOMMENDS RCM
, AR_CMGT_CREDIT_REQUESTS CCR
WHERE RCM.CREDIT_REQUEST_ID = CCR.CREDIT_REQUEST_ID
AND CCR.SOURCE_COLUMN3 = 'LEASEAPP'
AND RCM.CREDIT_RECOMMENDATION = NVL(p_recomm, RCM.CREDIT_RECOMMENDATION)
AND NVL(RCM.APPEALED_FLAG, 'N') = NVL(p_appeal, NVL(RCM.APPEALED_FLAG, 'N'))
AND CCR.SOURCE_COLUMN1 = p_lap_id;
SELECT RCM.RECOMMENDATION_VALUE1 + TRUNC(LAST_UPDATED) APPEAL_EXP_DATE
FROM AR_CMGT_CASE_FOLDERS CCF
, AR_CMGT_CREDIT_REQUESTS CCR
, AR_CMGT_CF_RECOMMENDS RCM
WHERE CCR.CREDIT_REQUEST_ID = CCF.CREDIT_REQUEST_ID
AND CCR.SOURCE_COLUMN3 = 'LEASEAPP'
AND CCF.STATUS = 'CLOSED'
AND RCM.CREDIT_REQUEST_ID = CCR.CREDIT_REQUEST_ID
AND RCM.CREDIT_RECOMMENDATION = 'AUTHORIZE_APPEAL'
AND CCR.SOURCE_COLUMN1 = p_lap_id;
SELECT LAB.REFERENCE_NUMBER
FROM OKL_LEASE_APPLICATIONS_B LAB
WHERE LAB.ID = p_lap_id;
,'begin debug call OCM_CREDIT_REQUEST_UPDATE_PUB.GET_CREDIT_REQUEST_REC');
OCM_CREDIT_REQUEST_UPDATE_PUB.GET_CREDIT_REQUEST_REC(
p_credit_request_id => acr_dtls_rec.credit_request_id
,p_return_status => l_return_status
,p_error_msg => x_msg_data
,p_credit_request_rec => l_crd_req_rec);
,'end debug call OCM_CREDIT_REQUEST_UPDATE_PUB.GET_CREDIT_REQUEST_REC');
,L_MODULE || ' Result of OCM_CREDIT_REQUEST_UPDATE_PUB.GET_CREDIT_REQUEST_REC'
,' result status ' || l_return_status);
,'begin debug call OCM_CREDIT_REQUEST_UPDATE_PUB.UPDATE_CREDIT_REQUEST');
OCM_CREDIT_REQUEST_UPDATE_PUB.UPDATE_CREDIT_REQUEST(
p_api_version => l_api_version,
p_init_msg_list => OKL_API.G_FALSE,
p_commit => NULL,
p_validation_level => NULL,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_credit_request_rec => l_crd_req_rec);
,'end debug call OCM_CREDIT_REQUEST_UPDATE_PUB.UPDATE_CREDIT_REQUEST');
,L_MODULE || ' Result of OCM_CREDIT_REQUEST_UPDATE_PUB.UPDATE_CREDIT_REQUEST'
,' result status ' || l_return_status);
/* UPDATE AR_CMGT_CREDIT_REQUESTS
SET status = 'SUBMIT'
WHERE credit_request_id= acr_dtls_rec.credit_request_id;
,'begin debug call OCM_CREDIT_REQUEST_UPDATE_PUB.UPDATE_CREDIT_REQUEST_STATUS');
OCM_CREDIT_REQUEST_UPDATE_PUB.UPDATE_CREDIT_REQUEST_STATUS(
p_api_version => l_api_version
,p_init_msg_list => OKL_API.G_FALSE
,p_commit => OKL_API.G_FALSE
,p_validation_level => FND_API.G_VALID_LEVEL_FULL
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_credit_request_id => acr_dtls_rec.credit_request_id
,p_credit_request_status => 'SUBMIT');
,'end debug call OCM_CREDIT_REQUEST_UPDATE_PUB.UPDATE_CREDIT_REQUEST_STATUS');
,L_MODULE || ' Result of OCM_CREDIT_REQUEST_UPDATE_PUB.UPDATE_CREDIT_REQUEST_STATUS'
,'l_return_status ' || l_return_status);
SELECT CONTRACT_NUMBER PROGRAM_AGREEMENT
FROM OKC_K_HEADERS_B CHR
WHERE CHR.ID = cp_pgm_agr_id
AND CHR.SCS_CODE = 'PROGRAM';
SELECT LAT.NAME LAT_NUMBER
FROM OKL_LEASEAPP_TEMPLATES LAT
,OKL_LEASEAPP_TEMPL_VERSIONS_V LATV
WHERE LATV.LEASEAPP_TEMPLATE_ID = LAT.ID
AND LATV.ID = cp_lat_id;
select a.site_use_id site_use_id
Into l_site_use_id
from Hz_cust_site_uses a,
Hz_cust_acct_sites b
Where a.CUST_ACCT_SITE_ID=B.CUST_ACCT_SITE_ID
AND A.SITE_USE_CODE='BILL_TO'
AND B.PARTY_SITE_ID=p_site_use_id;
SELECT DEFAULT_CREDIT_CLASSIFICATION
INTO l_cr_class
FROM AR_CMGT_SETUP_OPTIONS;
SELECT LSQ.ID LSQ_ID
, LAP.APPLICATION_STATUS LAP_STATUS
, LAP.REFERENCE_NUMBER LAP_NUMBER
FROM OKL_LEASE_QUOTES_B LSQ
, OKL_LEASE_APPLICATIONS_B LAP
WHERE LSQ.PARENT_OBJECT_ID = LAP.ID
AND LSQ.PARENT_OBJECT_CODE = 'LEASEAPP'
AND LSQ.PRIMARY_QUOTE = 'Y'
AND LAP.ID = cp_lap_id;
--Update the in play quote
l_lsqv_rec.id := in_play_qte_dtls_rec.lsq_id;
,'begin debug call OKL_LEASE_QUOTE_PVT.UPDATE_LEASE_QTE');
OKL_LEASE_QUOTE_PVT.UPDATE_LEASE_QTE(
p_api_version => p_api_version
,p_init_msg_list => OKL_API.G_FALSE
,p_transaction_control => OKL_API.G_TRUE
,p_lease_qte_rec => l_lsqv_rec
,x_lease_qte_rec => x_lsqv_rec
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
,'end debug call OKL_LEASE_QUOTE_PVT.UPDATE_LEASE_QTE');
,L_MODULE || ' Result of OKL_LEASE_QUOTE_PVT.UPDATE_LEASE_QTE'
,'l_lsqv_rec.reference_number ' || to_char(l_lsqv_rec.reference_number) ||
' l_lsqv_rec.id '|| l_lsqv_rec.id ||
' result status ' || l_return_status ||
' x_msg_data ' || x_msg_data);
,'begin debug call OKL_LEASE_QUOTE_PVT.UPDATE_LEASE_QTE');
OKL_LEASE_QUOTE_PVT.UPDATE_LEASE_QTE(
p_api_version => p_api_version
,p_init_msg_list => OKL_API.G_FALSE
,p_transaction_control => OKL_API.G_TRUE
,p_lease_qte_rec => l_lsqv_rec
,x_lease_qte_rec => x_lsqv_rec
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
,'end debug call OKL_LEASE_QUOTE_PVT.UPDATE_LEASE_QTE');
,L_MODULE || ' Result of OKL_LEASE_QUOTE_PVT.UPDATE_LEASE_QTE'
,'l_lsqv_rec.reference_number ' || to_char(l_lsqv_rec.reference_number) ||
' l_lsqv_rec.id '|| l_lsqv_rec.id ||
' result status ' || l_return_status ||
' x_msg_data ' || x_msg_data);
SELECT LAB.REFERENCE_NUMBER LEASE_APPL_NUMBER
, LAB.APPLICATION_STATUS APPLICATION_STATUS
, LAB.OBJECT_VERSION_NUMBER OBJECT_VERSION_NUMBER
FROM OKL_LEASE_APPLICATIONS_B LAB
WHERE LAB.ID = cp_lap_id;
SELECT COUNT(*)
FROM AR_CMGT_CF_RECOMMENDS RCM
, AR_CMGT_CREDIT_REQUESTS CCR
WHERE RCM.CREDIT_REQUEST_ID = CCR.CREDIT_REQUEST_ID
AND RCM.CREDIT_RECOMMENDATION = p_recom
AND CCR.SOURCE_COLUMN3 = 'LEASEAPP'
AND CCR.SOURCE_COLUMN1 = cp_lap_id;
SELECT ID
, REFERENCE_NUMBER
, STATUS
FROM OKL_LEASE_QUOTES_B LSQ
WHERE PARENT_OBJECT_CODE = 'LEASEAPP'
AND STATUS IN ('CR-RECOMMENDATION', 'CR-INCOMPLETE')
AND PARENT_OBJECT_ID = cp_lap_id;
SELECT 'X'
FROM AR_CMGT_CF_RECOMMENDS CCRM
, AR_CMGT_CREDIT_REQUESTS CCR
WHERE CCRM.CREDIT_REQUEST_ID = CCR.CREDIT_REQUEST_ID
AND CCR.SOURCE_COLUMN3 = 'LEASEAPP'
AND CCRM.CREDIT_RECOMMENDATION = 'COUNTER_OFFER'
AND CCR.SOURCE_COLUMN1 = cp_lap_id
AND CCRM.RECOMMENDATION_VALUE1 = cp_co_id;
SELECT OBJECT_VERSION_NUMBER INTO l_lsqv_rec.object_version_number
FROM OKL_LEASE_QUOTES_B
WHERE ID = okl_counter_offer_rec.id;
,'begin debug call OKL_LSQ_PVT.UPDATE_ROW');
OKL_LSQ_PVT.UPDATE_ROW(
p_api_version => p_api_version
,p_init_msg_list => OKL_API.G_FALSE
,p_lsqv_rec => l_lsqv_rec
,x_lsqv_rec => x_lsqv_rec
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
,'end debug call OKL_LSQ_PVT.UPDATE_ROW');
,L_MODULE || ' Result of OKL_LSQ_PVT.UPDATE_ROW'
,'l_lsqv_rec.reference_number ' || to_char(l_lsqv_rec.reference_number) ||
' result status ' || l_return_status);
SELECT LATV.CHECKLIST_ID
FROM OKL_LEASE_APPLICATIONS_B LAP
,OKL_LEASEAPP_TEMPL_VERSIONS_V LATV
WHERE LAP.LEASEAPP_TEMPLATE_ID = LATV.ID
AND LAP.ID = cp_lap_id;
SELECT LSQ.ID LSQ_ID
, CRR.CREDIT_REQUEST_ID
FROM OKL_LEASE_QUOTES_B LSQ
, OKL_LEASE_APPLICATIONS_B LAP
, AR_CMGT_CREDIT_REQUESTS CRR
WHERE LSQ.PARENT_OBJECT_ID = LAP.ID
AND LSQ.PARENT_OBJECT_CODE = 'LEASEAPP'
AND LSQ.PRIMARY_QUOTE = 'Y'
AND CRR.SOURCE_COLUMN1 = LAP.ID
AND CRR.SOURCE_COLUMN3 = 'LEASEAPP'
AND LAP.ID = cp_lap_id;
,'begin debug call OKL_LAP_PVT.INSERT_ROW');
OKL_LAP_PVT.INSERT_ROW(
p_api_version => p_api_version
,p_init_msg_list => OKL_API.G_FALSE
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_lapv_rec => l_lapv_rec
,x_lapv_rec => x_lapv_rec);
,'end debug call OKL_LAP_PVT.INSERT_ROW');
,L_MODULE || ' Result of OKL_LAP_PVT.INSERT_ROW'
,'l_lapv_rec.reference_number ' || to_char(l_lapv_rec.reference_number) ||
' l_lapv_rec.id '|| l_lapv_rec.id ||
' result status ' || l_return_status ||
' x_msg_data ' || x_msg_data);
SELECT LAB.REFERENCE_NUMBER LEASE_APPL_NUMBER
, LAB.APPLICATION_STATUS APPLICATION_STATUS
FROM OKL_LEASE_APPLICATIONS_B LAB
WHERE LAB.ID = cp_lap_id;
SELECT CHK.ID CHECKLIST_ID
FROM OKL_LEASE_APPLICATIONS_B LAP
, OKL_CHECKLISTS CHK
WHERE CHK.CHECKLIST_OBJ_TYPE_CODE = 'LEASE_APPL'
AND CHK.CHECKLIST_OBJ_ID = LAP.ID
AND LAP.ID = cp_lap_id;
SELECT LSQ.ID LSQ_ID
, CRR.CREDIT_REQUEST_ID
, LAP.APPLICATION_STATUS
, LAP.APPLICATION_STATUS APPEAL_SCOPE
FROM OKL_LEASE_QUOTES_B LSQ
, OKL_LEASE_APPLICATIONS_B LAP
, AR_CMGT_CREDIT_REQUESTS CRR
WHERE LSQ.PARENT_OBJECT_ID = LAP.ID
AND LSQ.PARENT_OBJECT_CODE = 'LEASEAPP'
AND LSQ.PRIMARY_QUOTE = 'Y'
AND CRR.SOURCE_COLUMN1 = LAP.ID
AND CRR.SOURCE_COLUMN3 = 'LEASEAPP'
AND LAP.ID = cp_lap_id;
,'begin debug call OKL_LAP_PVT.INSERT_ROW');
OKL_LAP_PVT.INSERT_ROW(
p_api_version => p_api_version
,p_init_msg_list => OKL_API.G_FALSE
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_lapv_rec => l_lapv_rec
,x_lapv_rec => x_lapv_rec);
,'end debug call OKL_LAP_PVT.INSERT_ROW');
,L_MODULE || ' Result of OKL_LAP_PVT.INSERT_ROW'
,'l_lapv_rec.reference_number ' || to_char(l_lapv_rec.reference_number) ||
' l_lapv_rec.id '|| l_lapv_rec.id ||
' result status ' || l_return_status ||
' x_msg_data ' || x_msg_data);
SELECT ID
, REFERENCE_NUMBER
, APPLICATION_STATUS
FROM OKL_LEASE_APPLICATIONS_B
WHERE ID <> cp_lap_id
CONNECT BY PARENT_LEASEAPP_ID = PRIOR ID
START WITH ID = cp_lap_id;
SELECT ID
, REFERENCE_NUMBER
, APPLICATION_STATUS
FROM OKL_LEASE_APPLICATIONS_B
WHERE ID <> cp_lap_id
CONNECT BY PRIOR PARENT_LEASEAPP_ID = ID
START WITH ID = cp_lap_id;
SELECT COUNT(*)
FROM AR_CMGT_CF_RECOMMENDS RCM
, AR_CMGT_CREDIT_REQUESTS CCR
WHERE RCM.CREDIT_REQUEST_ID = CCR.CREDIT_REQUEST_ID
AND CCR.SOURCE_COLUMN3 = 'LEASEAPP'
AND RCM.CREDIT_RECOMMENDATION = p_recomm
AND CCR.SOURCE_COLUMN1 = p_lap_id;
SELECT RCM.RECOMMENDATION_VALUE1 + TRUNC(LAST_UPDATED) APPEAL_EXP_DATE
FROM AR_CMGT_CASE_FOLDERS CCF
, AR_CMGT_CREDIT_REQUESTS CCR
, AR_CMGT_CF_RECOMMENDS RCM
WHERE CCR.CREDIT_REQUEST_ID = CCF.CREDIT_REQUEST_ID
AND CCR.SOURCE_COLUMN3 = 'LEASEAPP'
AND CCF.STATUS = 'CLOSED'
AND RCM.CREDIT_REQUEST_ID = CCR.CREDIT_REQUEST_ID
AND RCM.CREDIT_RECOMMENDATION = 'AUTHORIZE_APPEAL'
AND CCR.SOURCE_COLUMN1 = p_lap_id;
SELECT CHR.CONTRACT_NUMBER CONTRACT_NUMBER
FROM OKC_K_HEADERS_B CHR
, OKC_STATUSES_V CSTS
WHERE CHR.ORIG_SYSTEM_SOURCE_CODE = 'OKL_LEASE_APP'
AND CHR.STS_CODE = CSTS.CODE
AND CSTS.STE_CODE <> 'CANCELLED'
AND CHR.ORIG_SYSTEM_ID1 = p_lap_id;
SELECT LAB.REFERENCE_NUMBER
FROM OKL_LEASE_APPLICATIONS_B LAB
WHERE LAB.ID = p_lap_id;
SELECT LSQ.ID LSQ_ID
, LSQ.STATUS STATUS
FROM OKL_LEASE_QUOTES_B LSQ
WHERE LSQ.PARENT_OBJECT_CODE = 'LEASEAPP'
AND LSQ.PRIMARY_QUOTE= 'Y'
AND LSQ.PARENT_OBJECT_ID = p_lap_id;
,'begin debug call OKL_LAP_PVT.UPDATE_ROW');
OKL_LAP_PVT.UPDATE_ROW(
p_api_version => p_api_version
,p_init_msg_list => OKL_API.G_FALSE
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_lapv_rec => l_lapv_rec
,x_lapv_rec => x_lapv_rec);
,'end debug call OKL_LAP_PVT.UPDATE_ROW');
,L_MODULE || ' Result of OKL_LAP_PVT.UPDATE_ROW'
,'l_return_status ' || l_return_status);
,'begin debug call OKL_LAP_PVT.UPDATE_ROW');
OKL_LAP_PVT.UPDATE_ROW(
p_api_version => p_api_version
,p_init_msg_list => OKL_API.G_FALSE
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_lapv_rec => l_lapv_rec
,x_lapv_rec => x_lapv_rec);
,'end debug call OKL_LAP_PVT.UPDATE_ROW');
,L_MODULE || ' Result of OKL_LAP_PVT.UPDATE_ROW'
,'l_return_status ' || l_return_status);
SELECT OKL_ACCOUNTING_UTIL.GET_FUNC_CURR_CODE INTO l_func_curr_code
FROM DUAL;
SELECT USER_CONVERSION_TYPE
INTO l_meaning
FROM GL_DAILY_CONVERSION_TYPES
WHERE CONVERSION_TYPE = p_curr_type;
SELECT REFERENCE_NUMBER
FROM OKL_LEASE_APPLICATIONS_B LAB
WHERE LAB.ID = p_lse_app_id;
SELECT LAB.REFERENCE_NUMBER REFERENCE_NUMBER
, LAB.LEASE_OPPORTUNITY_ID LEASE_OPPORTUNITY_ID
FROM OKL_LEASE_APPLICATIONS_B LAB
WHERE LAB.ID = p_lap_id;
SELECT RCM.RECOMMENDATION_ID
, RCM.CASE_FOLDER_ID
, RCM.CREDIT_REQUEST_ID
, RCM.CREDIT_RECOMMENDATION
, RCM.RECOMMENDATION_NAME
, RCM.APPEALED_FLAG
FROM AR_CMGT_CF_RECOMMENDS RCM
, AR_CMGT_CREDIT_REQUESTS CCR
WHERE RCM.CREDIT_REQUEST_ID = CCR.CREDIT_REQUEST_ID
AND RCM.CREDIT_RECOMMENDATION = NVL(cp_cr_recom, RCM.CREDIT_RECOMMENDATION)
AND RCM.RECOMMENDATION_ID = NVL(cp_recom_id, RCM.RECOMMENDATION_ID)
AND CCR.SOURCE_COLUMN3 = 'LEASEAPP'
AND CCR.SOURCE_COLUMN1 = cp_lap_id;
SELECT CCR.CREDIT_REQUEST_ID
, CCR.APPLICATION_NUMBER
, CCR.TRX_AMOUNT
, CCR.TRX_CURRENCY
, CCR.STATUS
, CCR.RECOMMENDATION_NAME
, CCR.SOURCE_COLUMN1
, CCR.SOURCE_COLUMN2
, CCR.REVIEW_TYPE
, CCR.CREDIT_CLASSIFICATION
, CCR.CUST_ACCOUNT_ID
, LAB.PARENT_LEASEAPP_ID PARENT_LEASEAPP_ID
FROM AR_CMGT_CREDIT_REQUESTS CCR
, OKL_LEASE_APPLICATIONS_B LAB
WHERE SOURCE_COLUMN3 = 'LEASEAPP'
AND SOURCE_COLUMN1 = LAB.PARENT_LEASEAPP_ID
AND LAB.ID = cp_lap_id;
SELECT LAB.REFERENCE_NUMBER REFERENCE_NUMBER
, LAB.LEASE_OPPORTUNITY_ID LEASE_OPPORTUNITY_ID
FROM OKL_LEASE_APPLICATIONS_B LAB
WHERE LAB.ID = p_lap_id;
SELECT LAB.REFERENCE_NUMBER LSE_APP
, LOP.REFERENCE_NUMBER LSE_OPP
, CHR.CONTRACT_NUMBER CONTRACT_NUMBER
FROM OKL_LEASE_APPLICATIONS_B LAB
, OKL_LEASE_APPLICATIONS_B CLAB
, OKL_LEASE_OPPORTUNITIES_B LOP
, OKC_K_HEADERS_B CHR
, OKC_STATUSES_V CSTS
WHERE LAB.LEASE_OPPORTUNITY_ID = CLAB.LEASE_OPPORTUNITY_ID
AND LOP.ID = LAB.LEASE_OPPORTUNITY_ID
AND LAB.ID <> CLAB.ID
AND LAB.APPLICATION_STATUS = 'CONV-K'
AND LAB.ID = CHR.ORIG_SYSTEM_ID1
AND CHR.ORIG_SYSTEM_SOURCE_CODE = 'OKL_LEASE_APP'
AND CHR.STS_CODE = CSTS.CODE
AND CSTS.STE_CODE <> 'CANCELLED'
AND CLAB.ID = p_lap_id;
SELECT LSQ.REFERENCE_NUMBER LSE_QTE
, LOP.REFERENCE_NUMBER LSE_OPP
, CHR.CONTRACT_NUMBER CONTRACT_NUMBER
FROM OKL_LEASE_QUOTES_B LSQ
, OKL_LEASE_APPLICATIONS_B CLAB
, OKL_LEASE_OPPORTUNITIES_B LOP
, OKC_K_HEADERS_B CHR
, OKC_STATUSES_V CSTS
WHERE LOP.ID = CLAB.LEASE_OPPORTUNITY_ID
AND CHR.ORIG_SYSTEM_ID1 = LOP.ID
AND CHR.ORIG_SYSTEM_SOURCE_CODE = 'OKL_QUOTE'
AND CSTS.CODE = CHR.STS_CODE
AND CSTS.STE_CODE <> 'CANCELLED'
AND LSQ.PARENT_OBJECT_CODE = 'LEASEOPP'
AND LSQ.PARENT_OBJECT_ID = LOP.ID
AND LSQ.STATUS = 'CT-ACCEPTED'
AND CLAB.ID = p_lap_id;