[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| PUBLIC PROCEDURE SELECT_WF_PROCESS
|
| DESCRIPTION
| This process selects the process to run.
|
| PSEUDO CODE/LOGIC
|
| PARAMETERS
| P_PARAM1 IN Standard in parameter
| X_PARAM2 OUT NOCOPY Standard out parameter
|
| KNOWN ISSUES
| None
|
| NOTES
|
| MODIFICATION HISTORY
| Date Author Description of Changes
| 17-Jan-2006 GBELLARY Created
| 17-Apr-2007 MBOLLI Modified- Bug#5923205
|
*=======================================================================*/
/*=======================================================================+
| Package Global Constants
+=======================================================================*/
G_PKG_NAME CONSTANT VARCHAR2(30):= 'LNS_LOAN_PUB';
SELECT loan_type.loan_type_id loan_type_id
,loan_type.loan_class_code loan_class_code
,loan_type.loan_type_name loan_type_name
,loan_type.multiple_funding_flag multiple_funding_flag
,loan_type.open_to_term_flag open_to_term_flag
,loan_type.credit_review_flag credit_review_flag
,loan_product.loan_product_id loan_product_id
,loan_product.loan_product_name loan_product_name
,loan_product.loan_term loan_term
,loan_product.loan_term_period loan_term_period
,loan_product.max_loan_term max_loan_term
,loan_product.max_loan_term_period max_loan_term_period
,loan_product.loan_currency loan_currency
,loan_product.requested_amount requested_amount
,loan_product.max_requested_amount max_requested_amount
,loan_product.index_rate_id index_rate_id
,loan_product.rate_type rate_type
,loan_product.spread spread
,loan_product.floor_rate floor_rate
,loan_product.ceiling_rate ceiling_rate
,loan_product.interest_compounding_freq interest_compounding_freq
,loan_product.loan_payment_frequency loan_payment_frequency
,loan_product.loan_subtype loan_subtype
,loan_product.collateral_percent collateral_percent
,loan_product.allow_interest_only_flag allow_interest_only_flag
,loan_product.reamortize_over_payment reamortize_over_payment
,loan_product.org_id org_id
,loan_product.legal_entity_id legal_entity_id
,loan_product.rate_change_frequency rate_change_frequency
,loan_type.payment_application_order payment_application_order
,loan_type.pmt_appl_order_scope pmt_appl_order_scope
,loan_product.open_floor_rate open_floor_rate
,loan_product.open_ceiling_rate open_ceiling_rate
,loan_product.reamortize_under_payment reamortize_under_payment
,loan_product.percent_increase percent_increase
,loan_product.percent_increase_life percent_increase_life
,loan_product.open_percent_increase open_percent_increase
,loan_product.open_percent_increase_life open_percent_increase_life
,loan_product.open_spread open_spread
,loan_product.credit_review_type credit_review_type
,loan_product.guarantor_review_type guarantor_review_type
,loan_product.party_type party_type
,loan_product.open_loan_term open_loan_term
,loan_product.open_loan_term_period open_loan_term_period
,loan_product.open_max_loan_term open_max_loan_term
,loan_product.open_max_loan_term_period open_max_loan_term_period
,loan_product.interest_calculation_method CALCULATION_METHOD
,loan_product.day_count_method day_count_method
,loan_product.FORGIVENESS_FLAG
,loan_product.FORGIVENESS_PERCENT
,loan_product.PAYMENT_CALC_METHOD
,loan_product.PENAL_INT_RATE
,loan_product.PENAL_INT_GRACE_DAYS
,loan_product.CALC_ADD_INT_UNPAID_PRIN
,loan_product.CALC_ADD_INT_UNPAID_INT
,loan_product.PRINCIPAL_PAYMENT_FREQUENCY
,loan_product.REAMORTIZE_ON_FUNDING
,loan_product.ATTRIBUTE_CATEGORY
,loan_product.ATTRIBUTE1
,loan_product.ATTRIBUTE2
,loan_product.ATTRIBUTE3
,loan_product.ATTRIBUTE4
,loan_product.ATTRIBUTE5
,loan_product.ATTRIBUTE6
,loan_product.ATTRIBUTE7
,loan_product.ATTRIBUTE8
,loan_product.ATTRIBUTE9
,loan_product.ATTRIBUTE10
,loan_product.ATTRIBUTE11
,loan_product.ATTRIBUTE12
,loan_product.ATTRIBUTE13
,loan_product.ATTRIBUTE14
,loan_product.ATTRIBUTE15
,loan_product.ATTRIBUTE16
,loan_product.ATTRIBUTE17
,loan_product.ATTRIBUTE18
,loan_product.ATTRIBUTE19
,loan_product.ATTRIBUTE20
,loan_product.CUSTOM_SCHED_DATA
,loan_product.CUSTOM_CALC_METHOD
FROM lns_loan_types loan_type
,lns_loan_products loan_product
WHERE loan_product.loan_product_id = p_product_id
AND loan_type.loan_type_id = loan_product.loan_type_id
AND loan_type.status = 'COMPLETE'
AND loan_type.start_date_active <= sysdate
AND (loan_type.end_date_active is null OR
loan_type.end_date_active >= sysdate)
AND loan_product.status = 'COMPLETE'
AND loan_product.start_date_active <= sysdate
AND (loan_product.end_date_active is null OR
loan_product.end_date_active >= sysdate);
SELECT LEGAL_ENTITY_ID_TBL.flag LEGAL_ENTITY_ID_OVR
,LOAN_TERM_TBL.flag LOAN_TERM_OVR
,LOAN_TERM_PERIOD_TBL.flag LOAN_TERM_PERIOD_OVR
,INDEX_RATE_ID_TBL.flag INDEX_RATE_ID_OVR
,RATE_TYPE_TBL.flag RATE_TYPE_OVR
,INTEREST_COMPOUNDING_FREQ_TBL.flag INTEREST_COMPOUNDING_FREQ_OVR
,OPEN_SPREAD_TBL.flag OPEN_SPREAD_OVR
,OPEN_FLOOR_RATE_TBL.flag OPEN_FLOOR_RATE_OVR
,OPEN_CEILING_RATE_TBL.flag OPEN_CEILING_RATE_OVR
,OPEN_PERCENT_INCREASE_TBL.flag OPEN_PERCENT_INCREASE_OVR
,OPEN_PERCENT_INCREASE_LIFE_TBL.flag OPEN_PERCENT_INCREASE_LIFE_OVR
,SPREAD_TBL.flag SPREAD_OVR
,FLOOR_RATE_TBL.flag FLOOR_RATE_OVR
,CEILING_RATE_TBL.flag CEILING_RATE_OVR
,PERCENT_INCREASE_TBL.flag PERCENT_INCREASE_OVR
,PERCENT_INCREASE_LIFE_TBL.flag PERCENT_INCREASE_LIFE_OVR
,LOAN_PAYMENT_FREQUENCY_TBL.flag LOAN_PAYMENT_FREQ_OVR
,LOAN_SUBTYPE_TBL.flag LOAN_SUBTYPE_OVR
,REAMORTIZE_OVER_PAYMENT_TBL.flag REAMORTIZE_OVER_PAYMENT_OVR
,DAY_COUNT_METHOD_TBL.flag DAY_COUNT_METHOD_OVR
,CALCULATION_METHOD_TBL.flag CALCULATION_METHOD_OVR
,RATE_CHANGE_FREQUENCY_TBL.flag RATE_CHANGE_FREQUENCY_OVR
,COLLATERAL_PERCENT_TBL.flag COLLATERAL_PERCENT_OVR
,FORGIVENESS_FLAG_TBL.flag FORGIVENESS_FLAG_OVR
,FORGIVENESS_PERCENT_TBL.flag FORGIVENESS_PERCENT_OVR
,PAYMENT_CALC_METHOD_TBL.flag PAYMENT_CALC_METHOD_OVR
,PRIN_PAYMENT_FREQUENCY_TBL.flag PRIN_PAYMENT_FREQUENCY_OVR
,PENAL_INT_RATE_TBL.flag PENAL_INT_RATE_OVR
,PENAL_INT_GRACE_DAYS_TBL.flag PENAL_INT_GRACE_DAYS_OVR
,CALC_ADD_INT_UNPAID_PRIN_TBL.flag CALC_ADD_INT_UNPAID_PRIN_OVR
,CALC_ADD_INT_UNPAID_INT_TBL.flag CALC_ADD_INT_UNPAID_INT_OVR
,REAMORTIZE_ON_FUNDING_TBL.flag REAMORTIZE_ON_FUNDING_OVR
,CUSTOM_CALC_METHOD_TBL.flag CUSTOM_CALC_METHOD_OVR
FROM (SELECT DECODE(COUNT(*),0,'Y','N') flag
FROM LNS_LOAN_PRODUCT_FLAGS
WHERE loan_product_id = p_product_id
AND READONLY_COLUMN_NAME = 'LEGAL_ENTITY_ID'
) LEGAL_ENTITY_ID_TBL,
(SELECT DECODE(COUNT(*),0,'Y','N') flag
FROM LNS_LOAN_PRODUCT_FLAGS
WHERE loan_product_id = p_product_id
AND READONLY_COLUMN_NAME = 'LOAN_TERM'
) LOAN_TERM_TBL,
(SELECT DECODE(COUNT(*),0,'Y','N') flag
FROM LNS_LOAN_PRODUCT_FLAGS
WHERE loan_product_id = p_product_id
AND READONLY_COLUMN_NAME = 'LOAN_TERM_PERIOD'
) LOAN_TERM_PERIOD_TBL,
(SELECT DECODE(COUNT(*),0,'Y','N') flag
FROM LNS_LOAN_PRODUCT_FLAGS
WHERE loan_product_id = p_product_id
AND READONLY_COLUMN_NAME = 'INDEX_RATE_ID'
) INDEX_RATE_ID_TBL,
(SELECT DECODE(COUNT(*),0,'Y','N') flag
FROM LNS_LOAN_PRODUCT_FLAGS
WHERE loan_product_id = p_product_id
AND READONLY_COLUMN_NAME = 'RATE_TYPE'
) RATE_TYPE_TBL,
(SELECT DECODE(COUNT(*),0,'Y','N') flag
FROM LNS_LOAN_PRODUCT_FLAGS
WHERE loan_product_id = p_product_id
AND READONLY_COLUMN_NAME = 'INTEREST_COMPOUNDING_FREQ'
) INTEREST_COMPOUNDING_FREQ_TBL,
(SELECT DECODE(COUNT(*),0,'Y','N') flag
FROM LNS_LOAN_PRODUCT_FLAGS
WHERE loan_product_id = p_product_id
AND READONLY_COLUMN_NAME = 'OPEN_SPREAD'
) OPEN_SPREAD_TBL,
(SELECT DECODE(COUNT(*),0,'Y','N') flag
FROM LNS_LOAN_PRODUCT_FLAGS
WHERE loan_product_id = p_product_id
AND READONLY_COLUMN_NAME = 'OPEN_FLOOR_RATE'
) OPEN_FLOOR_RATE_TBL,
(SELECT DECODE(COUNT(*),0,'Y','N') flag
FROM LNS_LOAN_PRODUCT_FLAGS
WHERE loan_product_id = p_product_id
AND READONLY_COLUMN_NAME = 'OPEN_CEILING_RATE'
) OPEN_CEILING_RATE_TBL,
(SELECT DECODE(COUNT(*),0,'Y','N') flag
FROM LNS_LOAN_PRODUCT_FLAGS
WHERE loan_product_id = p_product_id
AND READONLY_COLUMN_NAME = 'OPEN_PERCENT_INCREASE'
) OPEN_PERCENT_INCREASE_TBL,
(SELECT DECODE(COUNT(*),0,'Y','N') flag
FROM LNS_LOAN_PRODUCT_FLAGS
WHERE loan_product_id = p_product_id
AND READONLY_COLUMN_NAME = 'OPEN_PERCENT_INCREASE_LIFE'
) OPEN_PERCENT_INCREASE_LIFE_TBL,
(SELECT DECODE(COUNT(*),0,'Y','N') flag
FROM LNS_LOAN_PRODUCT_FLAGS
WHERE loan_product_id = p_product_id
AND READONLY_COLUMN_NAME = 'SPREAD'
) SPREAD_TBL,
(SELECT DECODE(COUNT(*),0,'Y','N') flag
FROM LNS_LOAN_PRODUCT_FLAGS
WHERE loan_product_id = p_product_id
AND READONLY_COLUMN_NAME = 'FLOOR_RATE'
) FLOOR_RATE_TBL,
(SELECT DECODE(COUNT(*),0,'Y','N') flag
FROM LNS_LOAN_PRODUCT_FLAGS
WHERE loan_product_id = p_product_id
AND READONLY_COLUMN_NAME = 'CEILING_RATE'
) CEILING_RATE_TBL,
(SELECT DECODE(COUNT(*),0,'Y','N') flag
FROM LNS_LOAN_PRODUCT_FLAGS
WHERE loan_product_id = p_product_id
AND READONLY_COLUMN_NAME = 'PERCENT_INCREASE'
) PERCENT_INCREASE_TBL,
(SELECT DECODE(COUNT(*),0,'Y','N') flag
FROM LNS_LOAN_PRODUCT_FLAGS
WHERE loan_product_id = p_product_id
AND READONLY_COLUMN_NAME = 'PERCENT_INCREASE_LIFE'
) PERCENT_INCREASE_LIFE_TBL,
(SELECT DECODE(COUNT(*),0,'Y','N') flag
FROM LNS_LOAN_PRODUCT_FLAGS
WHERE loan_product_id = p_product_id
AND READONLY_COLUMN_NAME = 'LOAN_PAYMENT_FREQUENCY'
) LOAN_PAYMENT_FREQUENCY_TBL,
(SELECT DECODE(COUNT(*),0,'Y','N') flag
FROM LNS_LOAN_PRODUCT_FLAGS
WHERE loan_product_id = p_product_id
AND READONLY_COLUMN_NAME = 'LOAN_SUBTYPE'
) LOAN_SUBTYPE_TBL,
(SELECT DECODE(COUNT(*),0,'Y','N') flag
FROM LNS_LOAN_PRODUCT_FLAGS
WHERE loan_product_id = p_product_id
AND READONLY_COLUMN_NAME = 'REAMORTIZE_OVER_PAYMENT'
) REAMORTIZE_OVER_PAYMENT_TBL,
(SELECT DECODE(COUNT(*),0,'Y','N') flag
FROM LNS_LOAN_PRODUCT_FLAGS
WHERE loan_product_id = p_product_id
AND READONLY_COLUMN_NAME = 'DAY_COUNT_METHOD'
) DAY_COUNT_METHOD_TBL,
(SELECT DECODE(COUNT(*),0,'Y','N') flag
FROM LNS_LOAN_PRODUCT_FLAGS
WHERE loan_product_id = p_product_id
AND READONLY_COLUMN_NAME = 'INTEREST_CALCULATION_METHOD'
) CALCULATION_METHOD_TBL,
(SELECT DECODE(COUNT(*),0,'Y','N') flag
FROM LNS_LOAN_PRODUCT_FLAGS
WHERE loan_product_id = p_product_id
AND READONLY_COLUMN_NAME = 'RATE_CHANGE_FREQUENCY'
) RATE_CHANGE_FREQUENCY_TBL,
(SELECT DECODE(COUNT(*),0,'Y','N') flag
FROM LNS_LOAN_PRODUCT_FLAGS
WHERE loan_product_id = p_product_id
AND READONLY_COLUMN_NAME = 'COLLATERAL_PERCENT'
) COLLATERAL_PERCENT_TBL,
(SELECT DECODE(COUNT(*),0,'Y','N') flag
FROM LNS_LOAN_PRODUCT_FLAGS
WHERE loan_product_id = p_product_id
AND READONLY_COLUMN_NAME = 'FORGIVENESS_FLAG'
) FORGIVENESS_FLAG_TBL,
(SELECT DECODE(COUNT(*),0,'Y','N') flag
FROM LNS_LOAN_PRODUCT_FLAGS
WHERE loan_product_id = p_product_id
AND READONLY_COLUMN_NAME = 'FORGIVENESS_PERCENT'
) FORGIVENESS_PERCENT_TBL,
(SELECT DECODE(COUNT(*),0,'Y','N') flag
FROM LNS_LOAN_PRODUCT_FLAGS
WHERE loan_product_id = p_product_id
AND READONLY_COLUMN_NAME = 'PAYMENT_CALC_METHOD'
) PAYMENT_CALC_METHOD_TBL,
(SELECT DECODE(COUNT(*),0,'Y','N') flag
FROM LNS_LOAN_PRODUCT_FLAGS
WHERE loan_product_id = p_product_id
AND READONLY_COLUMN_NAME = 'PRINCIPAL_PAYMENT_FREQUENCY'
) PRIN_PAYMENT_FREQUENCY_TBL,
(SELECT DECODE(COUNT(*),0,'Y','N') flag
FROM LNS_LOAN_PRODUCT_FLAGS
WHERE loan_product_id = p_product_id
AND READONLY_COLUMN_NAME = 'PENAL_INT_RATE'
) PENAL_INT_RATE_TBL,
(SELECT DECODE(COUNT(*),0,'Y','N') flag
FROM LNS_LOAN_PRODUCT_FLAGS
WHERE loan_product_id = p_product_id
AND READONLY_COLUMN_NAME = 'PENAL_INT_GRACE_DAYS'
) PENAL_INT_GRACE_DAYS_TBL,
(SELECT DECODE(COUNT(*),0,'Y','N') flag
FROM LNS_LOAN_PRODUCT_FLAGS
WHERE loan_product_id = p_product_id
AND READONLY_COLUMN_NAME = 'CALC_ADD_INT_UNPAID_PRIN'
) CALC_ADD_INT_UNPAID_PRIN_TBL,
(SELECT DECODE(COUNT(*),0,'Y','N') flag
FROM LNS_LOAN_PRODUCT_FLAGS
WHERE loan_product_id = p_product_id
AND READONLY_COLUMN_NAME = 'CALC_ADD_INT_UNPAID_INT'
) CALC_ADD_INT_UNPAID_INT_TBL,
(SELECT DECODE(COUNT(*),0,'Y','N') flag
FROM LNS_LOAN_PRODUCT_FLAGS
WHERE loan_product_id = p_product_id
AND READONLY_COLUMN_NAME = 'REAMORTIZE_ON_FUNDING'
) REAMORTIZE_ON_FUNDING_TBL,
(SELECT DECODE(COUNT(*),0,'Y','N') flag
FROM LNS_LOAN_PRODUCT_FLAGS
WHERE loan_product_id = p_product_id
AND READONLY_COLUMN_NAME = 'CUSTOM_CALC_METHOD'
) CUSTOM_CALC_METHOD_TBL;
SELECT 'Y'
INTO l_dummy
FROM jtf_rs_resource_extns res
WHERE res.resource_id = P_Loan_Details_Rec.loan_assigned_to
AND res.category = 'EMPLOYEE'
AND res.start_date_active <= SYSDATE
AND (res.end_date_active is null or res.end_date_active >= SYSDATE);
SELECT 'Y'
INTO l_dummy
FROM xle_entity_profiles xep
WHERE xep.legal_entity_id = P_Loan_Details_Rec.legal_entity_id;
SELECT 'Y'
INTO l_dummy
FROM lns_lookups llk
WHERE llk.lookup_code = P_Loan_Details_Rec.loan_purpose_code
AND llk.lookup_type = 'LOAN_PURPOSE';
SELECT glsob.currency_code
INTO l_functional_currency
FROM gl_sets_of_books glsob,
lns_system_options_all lso
WHERE glsob.set_of_books_id = lso.set_of_books_id
AND lso.org_id = l_product_rec.org_id;
SELECT 'Y'
INTO l_dummy
FROM gl_daily_conversion_types gdct
WHERE gdct.conversion_type = P_Loan_Details_Rec.exchange_rate_type;
SELECT 'Y'
INTO l_dummy
FROM ar_lookups alk
,RA_CUST_TRX_TYPES_ALL rtyp
WHERE rtyp.CUST_TRX_TYPE_ID = P_Loan_Details_Rec.trx_type_id
AND alk.lookup_code = rtyp.type
AND alk.lookup_type = 'INV/CM';
SELECT 'Y'
INTO l_dummy
FROM lns_lookups llk
WHERE llk.lookup_code = P_Loan_Details_Rec.loan_subtype
AND llk.lookup_type = 'LOAN_SUBTYPE';
SELECT 'Y'
INTO l_dummy
FROM lns_lookups llk
WHERE llk.lookup_code = P_Loan_Details_Rec.loan_term_period
AND llk.lookup_type = 'PERIOD';
SELECT 'Y'
INTO l_dummy
FROM lns_lookups llk
WHERE llk.lookup_code = P_Loan_Details_Rec.balloon_payment_type
AND llk.lookup_type = 'BALLOON_PAYMENT_TYPE';
SELECT 'Y'
INTO l_dummy
FROM hz_parties hzp
WHERE hzp.party_id = P_Loan_Details_Rec.primary_borrower_party_id
AND hzp.party_type = l_product_rec.party_type
AND hzp.status = 'A';
SELECT 'Y'
INTO l_dummy
FROM hz_cust_accounts_all hzca
WHERE hzca.cust_account_id = P_Loan_Details_Rec.cust_account_id
AND hzca.party_id = P_Loan_Details_Rec.primary_borrower_party_id
AND hzca.status = 'A';
SELECT 'Y'
INTO l_dummy
FROM hz_cust_accounts_all account,
hz_cust_site_uses acc_site_use,
hz_cust_acct_sites_all acct_site
WHERE account.cust_account_id = acct_site.cust_account_id
AND acc_site_use.cust_acct_site_id = acct_site.cust_acct_site_id
AND acc_site_use.site_use_code = 'BILL_TO'
AND acct_site.cust_acct_site_id = P_Loan_Details_Rec.bill_to_acct_site_id
AND acct_site.cust_account_id = P_Loan_Details_Rec.cust_account_id
AND acc_site_use.status = 'A';
SELECT
p.party_id INTO l_contact_pers_party_id
FROM
hz_relationships rel,
hz_parties p,
hz_parties o,
hz_parties rel_party,
ar_lookups lkup,
hz_relationship_types rel_type,
hz_contact_points con_phone
WHERE o.party_type = 'ORGANIZATION'
AND p.party_type = 'PERSON'
AND rel.subject_id = p.party_id
AND rel.object_id = o.party_id
AND rel.relationship_code = rel_type.forward_rel_code
AND rel_type.create_party_flag = 'Y'
AND rel_type.subject_type = 'PERSON'
AND rel_type.object_type = 'ORGANIZATION'
AND rel.relationship_type = rel_type.relationship_type
AND rel_type.role = lkup.lookup_code
AND lkup.lookup_type = 'HZ_RELATIONSHIP_ROLE'
AND rel.party_id = rel_party.party_id
AND rel.status = 'A'
AND rel_party.status = 'A'
AND p.status = 'A'
AND con_phone.owner_table_name(+) = 'HZ_PARTIES'
AND con_phone.owner_table_id(+) = rel.party_id
AND con_phone.primary_flag(+) = 'Y'
AND con_phone.status(+) = 'A'
AND con_phone.contact_point_type(+) = 'PHONE'
AND rel.party_id = P_Loan_Details_Rec.contact_rel_party_id;
SELECT
rel.party_id INTO l_contact_rel_party_id
FROM
hz_relationships rel,
hz_parties p,
hz_parties o,
hz_parties rel_party,
ar_lookups lkup,
hz_relationship_types rel_type,
hz_contact_points con_phone
WHERE o.party_type = 'ORGANIZATION'
AND p.party_type = 'PERSON'
AND rel.subject_id = p.party_id
AND rel.object_id = o.party_id
AND rel.relationship_code = rel_type.forward_rel_code
AND rel_type.create_party_flag = 'Y'
AND rel_type.subject_type = 'PERSON'
AND rel_type.object_type = 'ORGANIZATION'
AND rel.relationship_type = rel_type.relationship_type
AND rel_type.role = lkup.lookup_code
AND lkup.lookup_type = 'HZ_RELATIONSHIP_ROLE'
AND rel.party_id = rel_party.party_id
AND rel.status = 'A'
AND rel_party.status = 'A'
AND p.status = 'A'
AND con_phone.owner_table_name(+) = 'HZ_PARTIES'
AND con_phone.owner_table_id(+) = rel.party_id
AND con_phone.primary_flag(+) = 'Y'
AND con_phone.status(+) = 'A'
AND con_phone.contact_point_type(+) = 'PHONE'
AND p.party_id = P_Loan_Details_Rec.contact_pers_party_id;
SELECT 'Y'
INTO l_dummy
FROM lns_lookups llk
WHERE llk.lookup_code = P_Loan_Details_Rec.RATE_TYPE
AND llk.lookup_type = 'RATE_TYPE'
AND enabled_flag = 'Y';
SELECT 'Y'
INTO l_dummy
FROM lns_int_rate_headers lirh
WHERE lirh.interest_rate_id = P_Loan_Details_Rec.index_rate_id;
SELECT 'Y'
INTO l_dummy
FROM lns_lookups llk
WHERE llk.lookup_code = P_Loan_Details_Rec.loan_payment_frequency
AND llk.lookup_type = 'FREQUENCY';
SELECT 'Y'
INTO l_dummy
FROM lns_lookups llk
WHERE llk.lookup_code = P_Loan_Details_Rec.day_count_method
AND llk.lookup_type = 'DAY_COUNT_METHOD'
AND enabled_flag = 'Y';
SELECT 'Y'
INTO l_dummy
FROM lns_lookups llk
WHERE llk.lookup_code = P_Loan_Details_Rec.PAYMENT_CALC_METHOD
AND llk.lookup_type = 'PAYMENT_CALCULATION_METHOD';
SELECT 'Y'
INTO l_dummy
FROM lns_lookups llk
WHERE llk.lookup_code = P_Loan_Details_Rec.CALCULATION_METHOD
AND llk.lookup_type = 'INTEREST_CALCULATION_METHOD';
SELECT 'Y'
INTO l_dummy
FROM lns_lookups llk
WHERE llk.lookup_code = P_Loan_Details_Rec.INTEREST_COMPOUNDING_FREQ
AND llk.lookup_type = 'FREQUENCY';
SELECT 'Y'
INTO l_dummy
FROM lns_lookups llk
WHERE llk.lookup_code = P_Loan_Details_Rec.CUSTOM_CALC_METHOD
AND llk.lookup_type = 'CUSTOM_CALCULATION_METHOD';
SELECT 'Y'
INTO l_dummy
FROM lns_lookups llk
WHERE llk.lookup_code = P_Loan_Details_Rec.ORIG_PAY_CALC_METHOD
AND llk.lookup_type = 'PAYMENT_CALCULATION_METHOD';
SELECT 'Y'
INTO l_dummy
FROM lns_lookups llk
WHERE llk.lookup_code = P_Loan_Details_Rec.open_loan_term_period
AND llk.lookup_type = 'PERIOD';
SELECT 'Y'
INTO l_dummy
FROM lns_lookups llk
WHERE llk.lookup_code = P_Loan_Details_Rec.open_to_term_event
AND llk.lookup_type = 'OPEN_TO_TERM_EVENT';
SELECT 'Y'
INTO l_dummy
FROM lns_lookups llk
WHERE llk.lookup_code = P_Loan_Details_Rec.PRIN_PAYMENT_FREQUENCY
AND llk.lookup_type = 'FREQUENCY';
SELECT 'Y'
INTO l_dummy
FROM lns_lookups llk
WHERE llk.lookup_code = P_Loan_Details_Rec.REAMORTIZE_ON_FUNDING
AND llk.lookup_type = 'REAMORTIZE_ON_FUNDING_OPTIONS';
SELECT 'Y'
INTO l_dummy
FROM lns_lookups llk
WHERE llk.lookup_code = p_loan_part_tbl(l_count).loan_participant_type
AND llk.lookup_type = 'LNS_PARTICIPANT_TYPE'
AND enabled_flag = 'Y'
AND lookup_code <> 'PRIMARY_BORROWER';
SELECT 'Y'
INTO l_dummy
FROM hz_parties hzp
WHERE hzp.party_id = p_loan_part_tbl(l_count).hz_party_id
AND hzp.status = 'A';
SELECT 'Y'
INTO l_dummy
FROM hz_cust_accounts_all hzca
WHERE hzca.cust_account_id = p_loan_part_tbl(l_count).cust_account_id
AND hzca.party_id = p_loan_part_tbl(l_count).hz_party_id
AND hzca.status = 'A';
SELECT 'Y'
INTO l_dummy
FROM hz_cust_accounts_all account,
hz_cust_site_uses acc_site_use,
hz_cust_acct_sites_all acct_site
WHERE account.cust_account_id = acct_site.cust_account_id
AND acc_site_use.cust_acct_site_id = acct_site.cust_acct_site_id
AND acc_site_use.site_use_code = 'BILL_TO'
AND acct_site.cust_acct_site_id = p_loan_part_tbl(l_count).bill_to_acct_site_id
AND acct_site.cust_account_id = p_loan_part_tbl(l_count).cust_account_id
AND acc_site_use.status = 'A';
SELECT
p.party_id INTO l_contact_pers_party_id
FROM
hz_relationships rel,
hz_parties p,
hz_parties o,
hz_parties rel_party,
ar_lookups lkup,
hz_relationship_types rel_type,
hz_contact_points con_phone
WHERE o.party_type = 'ORGANIZATION'
AND p.party_type = 'PERSON'
AND rel.subject_id = p.party_id
AND rel.object_id = o.party_id
AND rel.relationship_code = rel_type.forward_rel_code
AND rel_type.create_party_flag = 'Y'
AND rel_type.subject_type = 'PERSON'
AND rel_type.object_type = 'ORGANIZATION'
AND rel.relationship_type = rel_type.relationship_type
AND rel_type.role = lkup.lookup_code
AND lkup.lookup_type = 'HZ_RELATIONSHIP_ROLE'
AND rel.party_id = rel_party.party_id
AND rel.status = 'A'
AND rel_party.status = 'A'
AND p.status = 'A'
AND con_phone.owner_table_name(+) = 'HZ_PARTIES'
AND con_phone.owner_table_id(+) = rel.party_id
AND con_phone.primary_flag(+) = 'Y'
AND con_phone.status(+) = 'A'
AND con_phone.contact_point_type(+) = 'PHONE'
AND rel.party_id = P_LOAN_PART_TBL(l_count).contact_rel_party_id;
SELECT
rel.party_id INTO l_contact_rel_party_id
FROM
hz_relationships rel,
hz_parties p,
hz_parties o,
hz_parties rel_party,
ar_lookups lkup,
hz_relationship_types rel_type,
hz_contact_points con_phone
WHERE o.party_type = 'ORGANIZATION'
AND p.party_type = 'PERSON'
AND rel.subject_id = p.party_id
AND rel.object_id = o.party_id
AND rel.relationship_code = rel_type.forward_rel_code
AND rel_type.create_party_flag = 'Y'
AND rel_type.subject_type = 'PERSON'
AND rel_type.object_type = 'ORGANIZATION'
AND rel.relationship_type = rel_type.relationship_type
AND rel_type.role = lkup.lookup_code
AND lkup.lookup_type = 'HZ_RELATIONSHIP_ROLE'
AND rel.party_id = rel_party.party_id
AND rel.status = 'A'
AND rel_party.status = 'A'
AND p.status = 'A'
AND con_phone.owner_table_name(+) = 'HZ_PARTIES'
AND con_phone.owner_table_id(+) = rel.party_id
AND con_phone.primary_flag(+) = 'Y'
AND con_phone.status(+) = 'A'
AND con_phone.contact_point_type(+) = 'PHONE'
AND p.party_id = P_LOAN_PART_TBL(l_count).contact_pers_party_id;
select pmt_sch.customer_trx_id
,pmt_sch.trx_number
,pmt_sch.amount_due_remaining
,pmt_sch.terms_sequence_number
INTO p_loan_lines_tbl(l_count).customer_trx_id
,p_loan_lines_tbl(l_count).REFERENCE_NUMBER
,p_loan_lines_tbl(l_count).remaining_balance
,p_loan_lines_tbl(l_count).installment_number
FROM
ar_payment_schedules_all pmt_sch,
hz_cust_accounts account,
RA_CUST_TRX_TYPES trx_type,
ar_lookups trx_type_lkup,
ar_lookups trx_class_lkup
WHERE
pmt_sch.class in ('INV','DM') and
pmt_sch.status = 'OP' and
pmt_sch.amount_due_remaining > 0 and
pmt_sch.cust_trx_type_id = trx_type.CUST_TRX_TYPE_ID and
trx_type_lkup.lookup_type = 'INV/CM' and
trx_type_lkup.lookup_code = trx_type.type and
trx_class_lkup.lookup_type = 'INV/CM' and
trx_class_lkup.lookup_code = pmt_sch.class and
pmt_sch.customer_id = account.cust_account_id and
account.party_id = P_Loan_Details_Rec.primary_borrower_party_id and
pmt_sch.INVOICE_CURRENCY_CODE = l_product_rec.loan_currency and
pmt_sch.payment_schedule_id = p_loan_lines_tbl(l_count).payment_schedule_id;
SELECT line.interest_rate
INTO l_num_temp
FROM lns_int_rate_headers hdr,
lns_int_rate_lines line
WHERE hdr.interest_rate_id = line.interest_rate_id
AND(p_loan_rates_tbl(l_count).index_date BETWEEN line.start_date_active
AND line.end_date_active)
AND hdr.interest_rate_id = P_Loan_Details_Rec.INDEX_RATE_ID;
l_rate_tbl.delete(i);
select LNS_FEE_ASSIGNMENTS_S.NEXTVAL FEE_ASSIGNMENT_ID,
LnsLoanHeaders.LOAN_ID,
LnsFees.FEE_ID,
--decode(LnsFees.RATE_TYPE,'VARIABLE', lns_fee_engine.calculateFee(LnsFees.FEE_ID,LnsLoanHeaders.LOAN_ID) ,LnsFees.FEE) FEE,
LnsFees.FEE,
LnsFees.FEE_TYPE,
LnsFees.FEE_BASIS,
LnsFees.NUMBER_GRACE_DAYS,
LnsFees.COLLECTED_THIRD_PARTY_FLAG,
LnsFees.RATE_TYPE,
decode(LnsFees.BILLING_OPTION,'ORIGINATION',0,
'SUBMIT_FOR_APPROVAL',0,
'TERM_CONVERSION',0,
'BILL_WITH_INSTALLMENT',1,
(decode(LNS_BILLING_UTIL_PUB.LAST_PAYMENT_NUMBER_EXT(LnsLoanHeaders.LOAN_ID) ,
-1 , 0 , LNS_BILLING_UTIL_PUB.LAST_PAYMENT_NUMBER_EXT(LnsLoanHeaders.LOAN_ID)) + 1 )
) BEGIN_INSTALLMENT_NUMBER,
decode(LnsFees.BILLING_OPTION,'ORIGINATION',0,
'SUBMIT_FOR_APPROVAL',0,
'TERM_CONVERSION',0,
'BILL_WITH_INSTALLMENT',1,
lns_fin_utils.getnumberinstallments(LnsLoanHeaders.LOAN_ID)) END_INSTALLMENT_NUMBER,
NULL NUMBER_OF_PAYMENTS,
LnsFees.BILLING_OPTION,
NULL CREATED_BY,
NULL CREATION_DATE,
NULL LAST_UPDATED_BY,
NULL LAST_UPDATE_DATE,
NULL LAST_UPDATE_LOGIN,
1 OBJECT_VERSION_NUMBER,
sysdate START_DATE_ACTIVE,
NULL END_DATE_ACTIVE,
NULL DISB_HEADER_ID,
LnsLoanProductLines.MANDATORY_FLAG,
NULL OPEN_PHASE_FLAG,
NULL PHASE,
LnsFees.CUSTOM_PROCEDURE
FROM LNS_FEES LnsFees ,
LNS_LOAN_HEADERS LnsLoanHeaders ,
LNS_LOAN_PRODUCT_LINES LnsLoanProductLines
WHERE LnsLoanHeaders.LOAN_ID = c_loan_id
AND LnsLoanHeaders.PRODUCT_ID = LnsLoanProductLines.LOAN_PRODUCT_ID
AND LnsLoanProductLines.LOAN_PRODUCT_LINE_TYPE = 'FEE'
AND LnsLoanProductLines.LINE_REFERENCE_ID = LnsFees.FEE_ID ;
SELECT LINE_TYPE, ACCOUNT_NAME, CODE_COMBINATION_ID, ACCOUNT_TYPE, DISTRIBUTION_PERCENT, DISTRIBUTION_TYPE
FROM LNS_DEFAULT_DISTRIBS
WHERE ACCOUNT_NAME = 'FEE_RECEIVABLE' OR FEE_ID = c_fee_id ;
SELECT LOAN_STATUS , CURRENT_PHASE
FROM LNS_LOAN_HEADERS LnsLoanHeaders
WHERE LnsLoanHeaders.LOAN_ID = c_loan_id ;
SELECT 'Y'
FROM DUAL
WHERE
EXISTS
(SELECT NULL FROM LNS_FEE_ASSIGNMENTS LnsFeeAssignments
WHERE LnsFeeAssignments.LOAN_ID = c_loan_id)
OR EXISTS
(SELECT NULL FROM LNS_LOAN_HISTORIES_H
WHERE TABLE_NAME = 'LNS_FEE_ASSIGNMENTS' AND LOAN_ID = c_loan_id) ;
logmessage(fnd_log.level_statement, 'Inserting into lns_distributions...');
Insert into lns_distributions
(DISTRIBUTION_ID
,LOAN_ID
,LINE_TYPE
,ACCOUNT_NAME
,CODE_COMBINATION_ID
,ACCOUNT_TYPE
,DISTRIBUTION_PERCENT
,DISTRIBUTION_TYPE
,FEE_ID
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,OBJECT_VERSION_NUMBER )
values
(LNS_DISTRIBUTIONS_S.nextval
,p_loan_id
,l_line_type
,l_account_name
,l_code_combination_id
,l_account_type
,l_distribution_percent
,l_distribution_type
,l_fee_assignment_rec.fee_id
,lns_utility_pub.creation_date
,lns_utility_pub.created_by
,lns_utility_pub.last_update_date
,lns_utility_pub.last_updated_by
,1) ;
SELECT 'Y'
INTO l_dummy
FROM hz_parties hzp
WHERE hzp.party_id = P_DISB_TBL(i).PAYEE_PARTY_ID
AND hzp.status = 'A';
error_while_insert EXCEPTION;
l_header_insert_success VARCHAR2(1);
l_part_insert_success VARCHAR2(1);
l_term_insert_success VARCHAR2(1);
l_term_update_success VARCHAR2(1);
g_errors_rec.delete;
select LNS_LOAN_HEADERS_S.nextval
into l_loan_rec.loan_id
from dual;
select 'L' || LNS_LOAN_NUMBER_S.nextval
into P_Loan_Details_Rec.loan_number
from dual;
select LNS_TERMS_S.nextval
into l_term_rec.term_id
from dual;
,x_return_status => l_header_insert_success
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
IF l_header_insert_success <> 'S' THEN
LogErrors(p_message_name=>'LNS_LCREATE_INS_ERR_HEADER'
,p_token1 => l_msg_data);
,x_return_status => l_part_insert_success
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
IF l_part_insert_success <> 'S' THEN
LogErrors(p_message_name=>'LNS_LCREATE_INS_ERR_PART'
,p_token1 =>l_msg_data);
,x_return_status => l_term_insert_success
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
IF l_term_insert_success <> 'S' THEN
LogErrors(p_message_name=>'LNS_LCREATE_INS_ERR_TERM'
,p_token1 =>l_msg_data);
LogMessage(FND_LOG.LEVEL_STATEMENT, 'Inserting into LNS_FREQ_SCHEDULES...');
insert into LNS_FREQ_SCHEDULES
(FREQ_SCHEDULE_ID,
LOAN_ID,
PHASE,
COMPONENT,
FREQUENCY_BEGIN_DATE,
FREQUENCY,
OBJECT_VERSION_NUMBER,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN)
values
(LNS_FREQ_SCHEDULES_S.nextval,
l_loan_rec.loan_id,
'OPEN',
'PRIN_INT',
l_open_freq_schedule_tbl(1).PERIOD_BEGIN_DATE,
l_open_freq_schedule_tbl(1).FREQUENCY,
1,
sysdate,
lns_utility_pub.created_by,
sysdate,
lns_utility_pub.last_updated_by,
lns_utility_pub.LAST_UPDATE_LOGIN);
logmessage(fnd_log.LEVEL_UNEXPECTED, 'Inserted Open phase record');
insert into LNS_FREQ_SCHEDULES
(FREQ_SCHEDULE_ID,
LOAN_ID,
PHASE,
COMPONENT,
FREQUENCY_BEGIN_DATE,
FREQUENCY,
OBJECT_VERSION_NUMBER,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN)
values
(LNS_FREQ_SCHEDULES_S.nextval,
l_loan_rec.loan_id,
'TERM',
'INT',
l_int_freq_schedule_tbl(1).PERIOD_BEGIN_DATE,
l_int_freq_schedule_tbl(1).FREQUENCY,
1,
sysdate,
lns_utility_pub.created_by,
sysdate,
lns_utility_pub.last_updated_by,
lns_utility_pub.LAST_UPDATE_LOGIN);
logmessage(fnd_log.LEVEL_UNEXPECTED, 'Inserted INT record');
insert into LNS_FREQ_SCHEDULES
(FREQ_SCHEDULE_ID,
LOAN_ID,
PHASE,
COMPONENT,
FREQUENCY_BEGIN_DATE,
FREQUENCY,
OBJECT_VERSION_NUMBER,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN)
values
(LNS_FREQ_SCHEDULES_S.nextval,
l_loan_id,
'TERM',
'PRIN',
l_prin_freq_schedule_tbl(1).PERIOD_BEGIN_DATE,
l_prin_freq_schedule_tbl(1).FREQUENCY,
1,
sysdate,
lns_utility_pub.created_by,
sysdate,
lns_utility_pub.last_updated_by,
lns_utility_pub.LAST_UPDATE_LOGIN);
logmessage(fnd_log.LEVEL_UNEXPECTED, 'Inserted PRIN record');
insert into LNS_FREQ_SCHEDULES
(FREQ_SCHEDULE_ID,
LOAN_ID,
PHASE,
COMPONENT,
FREQUENCY_BEGIN_DATE,
FREQUENCY,
OBJECT_VERSION_NUMBER,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN)
values
(LNS_FREQ_SCHEDULES_S.nextval,
l_loan_id,
'TERM',
'PRIN_INT',
l_freq_schedule_tbl(1).PERIOD_BEGIN_DATE,
l_freq_schedule_tbl(1).FREQUENCY,
1,
sysdate,
lns_utility_pub.created_by,
sysdate,
lns_utility_pub.last_updated_by,
lns_utility_pub.LAST_UPDATE_LOGIN);
logmessage(fnd_log.LEVEL_UNEXPECTED, 'Inserted PRIN_INT record');
insert into LNS_RATE_SCHEDULES
(RATE_ID
,TERM_ID
,INDEX_RATE
,SPREAD
,CURRENT_INTEREST_RATE
,START_DATE_ACTIVE
,END_DATE_ACTIVE
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,OBJECT_VERSION_NUMBER
,INDEX_DATE
,BEGIN_INSTALLMENT_NUMBER
,END_INSTALLMENT_NUMBER
,INTEREST_ONLY_FLAG
,PHASE
,FLOATING_FLAG)
VALUES
(LNS_RATE_SCHEDULES_S.nextval
,l_term_rec.term_id
,P_TERM_RATES_TBL(l_Count).INDEX_RATE
,nvl(P_TERM_RATES_TBL(l_Count).SPREAD, 0)
,(P_TERM_RATES_TBL(l_Count).INDEX_RATE + nvl(P_TERM_RATES_TBL(l_Count).SPREAD, 0))
,sysdate
,null
,lns_utility_pub.created_by
,sysdate
,lns_utility_pub.last_updated_by
,sysdate
,lns_utility_pub.LAST_UPDATE_LOGIN
,1
,P_TERM_RATES_TBL(l_Count).INDEX_DATE
,P_TERM_RATES_TBL(l_Count).BEGIN_INSTALLMENT_NUMBER
,P_TERM_RATES_TBL(l_Count).END_INSTALLMENT_NUMBER
,P_TERM_RATES_TBL(l_Count).INTEREST_ONLY_FLAG
,'TERM'
,null);
insert into LNS_RATE_SCHEDULES
(RATE_ID
,TERM_ID
,INDEX_RATE
,SPREAD
,CURRENT_INTEREST_RATE
,START_DATE_ACTIVE
,END_DATE_ACTIVE
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,OBJECT_VERSION_NUMBER
,INDEX_DATE
,BEGIN_INSTALLMENT_NUMBER
,END_INSTALLMENT_NUMBER
,INTEREST_ONLY_FLAG
,PHASE
,FLOATING_FLAG)
VALUES
(LNS_RATE_SCHEDULES_S.nextval
,l_term_rec.term_id
,P_OPEN_RATES_TBL(l_Count).INDEX_RATE
,nvl(P_OPEN_RATES_TBL(l_Count).SPREAD, 0)
,(P_OPEN_RATES_TBL(l_Count).INDEX_RATE + nvl(P_OPEN_RATES_TBL(l_Count).SPREAD, 0))
,sysdate
,null
,lns_utility_pub.created_by
,sysdate
,lns_utility_pub.last_updated_by
,sysdate
,lns_utility_pub.LAST_UPDATE_LOGIN
,1
,P_OPEN_RATES_TBL(l_Count).INDEX_DATE
,P_OPEN_RATES_TBL(l_Count).BEGIN_INSTALLMENT_NUMBER
,P_OPEN_RATES_TBL(l_Count).END_INSTALLMENT_NUMBER
,P_OPEN_RATES_TBL(l_Count).INTEREST_ONLY_FLAG
,'OPEN'
,null);
LogMessage(FND_LOG.LEVEL_STATEMENT, 'Inserting records into LNS_LOAN_LINES...');
INSERT INTO LNS_LOAN_LINES
(
LOAN_LINE_ID
,LOAN_ID
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,OBJECT_VERSION_NUMBER
,REFERENCE_TYPE
,REFERENCE_ID
,REFERENCE_NUMBER
,REFERENCE_DESCRIPTION
,REFERENCE_AMOUNT
,REQUESTED_AMOUNT
,PAYMENT_SCHEDULE_ID
,INSTALLMENT_NUMBER
)
VALUES
(
LNS_LOAN_LINE_S.nextval
,l_loan_rec.loan_id
,sysdate
,lns_utility_pub.created_by
,sysdate
,lns_utility_pub.created_by
,1
,'RECEIVABLE'
,p_loan_lines_tbl(l_count).customer_trx_id --v_customer_trx_id(i)
,p_loan_lines_tbl(l_count).REFERENCE_NUMBER --v_reference_number(i)
,p_loan_lines_tbl(l_count).REFERENCE_DESCRIPTION --v_DESCRIPTION(i)
,p_loan_lines_tbl(l_count).remaining_balance --v_remaining_balance(i)
,p_loan_lines_tbl(l_count).requested_amount --v_requested_amount(i)
,p_loan_lines_tbl(l_count).payment_schedule_id --v_PAYMENT_SCHEDULE_ID(i)
,p_loan_lines_tbl(l_count).installment_number --v_installment_number(i)
);
UPDATE lns_loan_headers_all
SET requested_amount = l_ers_requested_amount
,object_version_number = 2
WHERE loan_id = l_loan_rec.loan_id;
select lns_disb_headers_s.NEXTVAL into l_DISB_HEADER_REC.DISB_HEADER_ID from dual;
LogMessage(FND_LOG.LEVEL_STATEMENT, 'Calling INSERT_DISB_HEADER...');
LNS_FUNDING_PUB.INSERT_DISB_HEADER(
P_API_VERSION => 1.0,
P_INIT_MSG_LIST => FND_API.G_TRUE,
P_COMMIT => FND_API.G_FALSE,
P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
P_DISB_HEADER_REC => l_DISB_HEADER_REC,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data);
logmessage(fnd_log.level_unexpected, 'Call to INSERT_DISB_HEADER failed');
select lns_disb_lines_s.NEXTVAL into l_DISB_LINE_REC.DISB_LINE_ID from dual;
LogMessage(FND_LOG.LEVEL_STATEMENT, 'Calling INSERT_DISB_LINE...');
LNS_FUNDING_PUB.INSERT_DISB_LINE(
P_API_VERSION => 1.0,
P_INIT_MSG_LIST => FND_API.G_TRUE,
P_COMMIT => FND_API.G_FALSE,
P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
P_DISB_LINE_REC => l_DISB_LINE_REC,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data);
logmessage(fnd_log.level_unexpected, 'Call to INSERT_DISB_LINE failed');
l_custom_tbl.delete;
p_loan_cust_sched_tbl.delete;
LNS_INDEX_RATES_PUB.UPDATE_LOAN_FLOATING_RATE(
P_API_VERSION => 1.0,
P_INIT_MSG_LIST => FND_API.G_TRUE,
P_COMMIT => FND_API.G_FALSE,
P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
P_LOAN_ID => l_loan_rec.loan_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
logmessage(fnd_log.LEVEL_UNEXPECTED, 'Floating rate schedule updated successfully');
,x_return_status => l_term_update_success
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
IF l_term_update_success <> 'S' THEN
LogErrors(p_message_name=>'LNS_LCREATE_UPD_ERR_TERM'
,p_token1 =>l_msg_data);
logmessage(fnd_log.LEVEL_UNEXPECTED, 'Default delinquency amount updated successfully');
WHEN error_while_insert THEN
ROLLBACK TO CREATE_LOAN;