[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| 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-2005 GBELLARY Created
|
*=======================================================================*/
/*=======================================================================+
| Package Global Constants
+=======================================================================*/
G_PKG_NAME CONSTANT VARCHAR2(30):= 'LNS_REP_UTILS';
'select ' ||
'lh.LOAN_ID, ' ||
'lh.loan_number ' ||
',hp.party_name customer ' ||
',llk.meaning || decode(lh.secondary_status,null,'''','': '') || llks.meaning loan_status_meaning ' ||
',lh.loan_status || decode(lh.secondary_status,null,'''','': '') || lh.secondary_status loan_status ' ||
',lh.loan_status primary_loan_status ' ||
',lh.secondary_status secondary_loan_status ' ||
',pay.TOTAL_PRINCIPAL_BALANCE loan_amount ' ||
',(CASE
WHEN
(
lh.LOAN_STATUS = ''ACTIVE''
OR lh.LOAN_STATUS = ''DEFAULT''
OR lh.LOAN_STATUS = ''DELINQUENT''
OR lh.LOAN_STATUS = ''PAIDOFF''
)
THEN
pay.TOTAL_PRINCIPAL_BALANCE
ELSE
lh.REQUESTED_AMOUNT
END
) loan_req_bal_amount' ||
',lh.loan_description ' ||
',to_char(lh.loan_maturity_date, ''YYYY-MM-DD'') loan_maturity_date ' ||
',lh.loan_term || '' '' || llktt.meaning loan_length ' ||
',to_char(lh.LOAN_APPLICATION_DATE, ''YYYY-MM-DD'') LOAN_APPLICATION_DATE ' ||
',lh.ORG_ID ' ||
',lh.LOAN_TYPE ' ||
',lh.LOAN_CLASS_CODE ' ||
',lh.LOAN_CURRENCY ' ||
',LNS_FINANCIALS.getActiveRate(lh.LOAN_ID) current_interest_rate ' ||
', llkrt.meaning interest_type ' ||
',lh.LOAN_SUBTYPE ' ||
',(select max(last_update_date) from LNS_APPROVAL_ACTIONS ' ||
'where loan_id = lh.LOAN_ID ' ||
'and ACTION_TYPE = ''SUBMIT_FOR_APPR'') submit_for_approval_date ' ||
',lh.LAST_BILLED_DATE ' ||
',(lh.REQUESTED_AMOUNT + nvl(lh.ADD_REQUESTED_AMOUNT, 0)) original_requested_amount ' ||
',lh.LOAN_APPROVAL_DATE ' ||
',pay.TOTAL_PRIN_PAID_TODATE principal_paid ' ||
',pay.INTEREST_PAID_TODATE interest_paid ' ||
',pay.FEE_PAID_TODATE fees_paid ' ||
',pay.NEXT_PAYMENT_DUE_DATE next_payment_due_date ' ||
',pay.NEXT_PAYMENT_PRINCIPAL_DUE next_principal_due ' ||
',pay.NEXT_PAYMENT_INTEREST_DUE next_interest_due ' ||
',pay.NEXT_PAYMENT_FEE_DUE next_fees_due ' ||
',pay.NEXT_PAYMENT_TOTAL_DUE next_payment_due ' ||
',account.ACCOUNT_NUMBER ' ||
', CURSOR (select ' ||
'REQUESTED_AMOUNT loan_requested_amount, ' ||
'REFERENCE_AMOUNT original_rec_balance, ' ||
'REFERENCE_NUMBER original_rec_desc ' ||
'from LNS_LOAN_LINES ' ||
'where loan_id = lh.LOAN_ID ' ||
'and end_date is null) AS ORIGINAL_RECEIVABLES ' ||
'from ' ||
'lns_loan_headers_all_vl lh, ' ||
'hz_parties hp, ' ||
'lns_terms t, ' ||
'lns_lookups llk, ' ||
'LNS_PAY_SUM_V pay, ' ||
'hz_cust_accounts_all account, ' ||
'lns_lookups llkrt, ' ||
'lns_lookups llktt, ' ||
'lns_lookups llks ' ||
'where ' ||
'lh.primary_borrower_id = hp.party_id and ' ||
'lh.loan_id = pay.loan_id and ' ||
'lh.loan_id = t.loan_id and ' ||
'llk.lookup_code = lh.loan_status and ' ||
'llk.lookup_type = ''LOAN_STATUS'' and ' ||
'llktt.lookup_code = lh.loan_term_period and ' ||
'llktt.lookup_type = ''PERIOD'' and ' ||
'llkrt.lookup_code = t.rate_type and ' ||
'llkrt.lookup_type = ''RATE_TYPE'' and ' ||
'llks.lookup_code(+) = lh.secondary_status and ' ||
'llks.lookup_type(+) = ''SECONDARY_STATUS'' and ' ||
'lh.loan_status <> ''DELETED'' and ' ||
'lh.CUST_ACCOUNT_ID = account.CUST_ACCOUNT_ID and ' ||
'lh.loan_class_code = :LOAN_CLASS and ' ||
'lh.loan_type_id = :LOAN_TYPE_ID and ' ||
'lh.loan_currency = :CURRENCY_CODE and ' ||
'lh.org_id = :ORG_ID';
SELECT loan_type_desc
into l_loan_type_desc
from lns_loan_types_vl
where loan_type_id = l_loan_type_id;
SELECT name
INTO l_org_name
FROM HR_ALL_ORGANIZATION_UNITS_TL
WHERE organization_id = l_org_id
AND language = userenv('LANG');
SELECT PARTY_NAME
INTO l_borrower_name
FROM hz_parties
WHERE party_id = borrower_id;
SELECT account_number
into l_account_number
FROM hz_cust_accounts
WHERE cust_account_id = l_cust_account_id;
SELECT meaning
into l_loan_status1_desc
from lns_lookups
where lookup_type = 'LOAN_STATUS'
and lookup_code = loan_status1;
SELECT meaning
into l_loan_status2_desc
from lns_lookups
where lookup_type = 'LOAN_STATUS'
and lookup_code = loan_status2;
SELECT NAME
INTO l_legal_entity_name
FROM xle_entity_profiles
WHERE legal_entity_id = l_legal_entity_id;
SELECT loan_product_name
INTO l_loan_product_name
FROM lns_loan_products_all_vl
WHERE loan_product_id = product_id;
SELECT source_name
INTO l_loan_officer
FROM jtf_rs_resource_extns
WHERE resource_id = loan_assigned_to;
'select ' ||
'lh.LOAN_ID, ' ||
'lh.loan_number ' ||
',hp.party_name customer ' ||
',llk.meaning loan_status_meaning ' ||
',lh.loan_status ' ||
',pay.TOTAL_PRINCIPAL_BALANCE loan_balance ' ||
',lh.loan_description ' ||
',account.ACCOUNT_NUMBER ' ||
',lh.FUNDED_AMOUNT original_loan_amount ' ||
',to_char(lh.loan_maturity_date, ''YYYY-MM-DD'') loan_maturity_date ' ||
',lh.ORG_ID ' ||
',lot.loan_type_name loan_type ' ||
',lh.LOAN_CLASS_CODE ' ||
',lh.LOAN_CURRENCY ' ||
',(loc.address1 || '' '' || loc.city || '' '' || loc.state || '' '' || ' ||
'loc.postal_code || '' '' || terr.TERRITORY_SHORT_NAME) Address ' ||
',ou.name operating_unit ' ||
',lh.LEGAL_ENTITY_ID ' ||
',le.NAME LEGAL_ENTITY_NAME ' ||
',lh.product_id ' ||
',product.loan_product_name loan_product ' ||
',nvl((select sum(line.line_amount) ' ||
' from lns_disb_lines line, ' ||
' lns_disb_headers head ' ||
' where head.loan_id = lh.LOAN_ID and ' ||
' head.disb_header_id = line.disb_header_id and ' ||
' line.status = ''FULLY_FUNDED''), 0) disbursed_amount ' ||
',nvl(lh.CURRENT_PHASE, ''TERM'') current_phase ' ||
', CURSOR (select ' ||
' LOAN_ID loan_id, ' ||
' REFERENCE_NUMBER original_rec_number, ' ||
' REFERENCE_AMOUNT original_rec_balance, ' ||
' REQUESTED_AMOUNT loan_requested_amount, ' ||
' REFERENCE_NUMBER original_rec_desc ' ||
' from LNS_LOAN_LINES ' ||
' where loan_id = lh.LOAN_ID ' ||
' and end_date is null ' ||
') AS ORIGINAL_RECEIVABLES ' ||
', CURSOR ( ' ||
' select ' ||
' max(amv.loan_id) loan_id, ' ||
' sum(amv.PRINCIPAL_AMOUNT) prin_amortization_amount, ' ||
' sum(amv.PRIN_CASH) prin_receipt_payments, ' ||
' sum(amv.PRIN_NET_CREDIT) prin_credit_netting, ' ||
' sum(amv.PRIN_LOAN_PMT_CREDIT) prin_credit_payments, ' ||
' sum(amv.PRIN_OTHER_CREDIT) prin_credit_non_payments, ' ||
' sum(amv.PRIN_ADJ) prin_adjustments, ' ||
' sum(amv.PRINCIPAL_REMAINING) prin_remaining_amount, ' ||
' sum(amv.INTEREST_AMOUNT) int_amortization_amount, ' ||
' sum(amv.INT_CASH) int_receipt_payments, ' ||
' sum(amv.INT_NET_CREDIT) int_credit_netting, ' ||
' sum(amv.INT_LOAN_PMT_CREDIT) int_credit_payments, ' ||
' sum(amv.INT_OTHER_CREDIT) int_credit_non_payments, ' ||
' sum(amv.INT_ADJ) int_adjustments, ' ||
' sum(amv.INTEREST_REMAINING) int_remaining_amount, ' ||
' sum(amv.FEE_AMOUNT) fee_amortization_amount, ' ||
' sum(amv.FEE_CASH) fee_receipt_payments, ' ||
' sum(amv.FEE_NET_CREDIT) fee_credit_netting, ' ||
' sum(amv.FEE_LOAN_PMT_CREDIT) fee_credit_payments, ' ||
' sum(amv.FEE_OTHER_CREDIT) fee_credit_non_payments, ' ||
' sum(amv.FEE_ADJ) fee_adjustments, ' ||
' sum(amv.FEE_REMAINING) fee_remaining_amount ' ||
' from ' ||
' LNS_AM_SCHEDS_DTL_V amv ' ||
' where amv.loan_id = lh.LOAN_ID and ' ||
' amv.REVERSED_CODE = ''N'' and ' ||
' trunc(amv.DUE_DATE) between dateparameters.from_dt and dateparameters.to_dt ' ||
') AS BILL_PAY_SUMMARY ' ||
', CURSOR ( ' ||
' select ' ||
' am.PAYMENT_NUMBER, ' ||
' lok.MEANING purpose, ' ||
' decode(trx.customer_trx_id, ' ||
' am.PRINCIPAL_TRX_ID, am.PRINCIPAL_AMOUNT, ' ||
' am.INTEREST_TRX_ID, am.INTEREST_AMOUNT, ' ||
' am.FEE_TRX_ID, am.FEE_AMOUNT) amortization_amount, ' ||
' psa.AMOUNT_DUE_ORIGINAL ar_trx_amount, ' ||
' (decode(trx.customer_trx_id, ' ||
' am.PRINCIPAL_TRX_ID, am.PRINCIPAL_AMOUNT, ' ||
' am.INTEREST_TRX_ID, am.INTEREST_AMOUNT, ' ||
' am.FEE_TRX_ID, am.FEE_AMOUNT) ' ||
' - psa.AMOUNT_DUE_ORIGINAL) difference, ' ||
' trx.INTERFACE_HEADER_ATTRIBUTE1 ar_trx_reference ' ||
' from ' ||
' lns_amortization_scheds am, ' ||
' ar_payment_schedules_all psa, ' ||
' RA_CUSTOMER_TRX_ALL trx, ' ||
' LNS_LOOKUPS lok ' ||
' where am.loan_id = lh.LOAN_ID and ' ||
' (am.REVERSED_FLAG is null or am.REVERSED_FLAG = ''N'') and ' ||
' trunc(am.DUE_DATE) between dateparameters.from_dt and dateparameters.to_dt and ' ||
' (trx.customer_trx_id = am.PRINCIPAL_TRX_ID or ' ||
' trx.customer_trx_id = am.INTEREST_TRX_ID or ' ||
' trx.customer_trx_id = am.FEE_TRX_ID) and ' ||
' psa.customer_trx_id = trx.customer_trx_id and ' ||
' lok.lookup_type = ''PAYMENT_APPLICATION_TYPE'' and ' ||
' lok.lookup_code = decode(trx.customer_trx_id, am.PRINCIPAL_TRX_ID, ' ||
' ''PRIN'', am.INTEREST_TRX_ID, ''INT'', am.FEE_TRX_ID, ''FEES'') and ' ||
' (decode(trx.customer_trx_id, ' ||
' am.PRINCIPAL_TRX_ID, am.PRINCIPAL_AMOUNT, ' ||
' am.INTEREST_TRX_ID, am.INTEREST_AMOUNT, ' ||
' am.FEE_TRX_ID, am.FEE_AMOUNT) <> psa.AMOUNT_DUE_ORIGINAL) ' ||
') AS EXCEPTION_FLAGS , ' ||
'CURSOR ( ' ||
' select ' ||
' amv.PAYMENT_NUMBER, ' ||
' amv.BILL_TYPE_DESC meaning, ' ||
' amv.PRINCIPAL_AMOUNT billed_principal_amount, ' ||
' amv.PRIN_CASH prin_receipt_payments, ' ||
' amv.PRIN_NET_CREDIT prin_credit_netting, ' ||
' amv.PRIN_LOAN_PMT_CREDIT prin_credit_payments, ' ||
' amv.PRIN_OTHER_CREDIT prin_credit_non_payments, ' ||
' amv.PRIN_ADJ prin_adjustments, ' ||
' amv.PRINCIPAL_REMAINING prin_remaining_amount, ' ||
' amv.INTEREST_AMOUNT billed_int_amount, ' ||
' amv.INT_CASH int_receipt_payments, ' ||
' amv.INT_NET_CREDIT int_credit_netting, ' ||
' amv.INT_LOAN_PMT_CREDIT int_credit_payments, ' ||
' amv.INT_OTHER_CREDIT int_credit_non_payments, ' ||
' amv.INT_ADJ int_adjustments, ' ||
' amv.INTEREST_REMAINING int_remaining_amount, ' ||
' amv.FEE_AMOUNT billed_fee_amount, ' ||
' amv.FEE_CASH fee_receipt_payments, ' ||
' amv.FEE_NET_CREDIT fee_credit_netting, ' ||
' amv.FEE_LOAN_PMT_CREDIT fee_credit_payments, ' ||
' amv.FEE_OTHER_CREDIT fee_credit_non_payments, ' ||
' amv.FEE_ADJ fee_adjustments, ' ||
' amv.FEE_REMAINING fee_remaining_amount, ' ||
' to_char(amv.DUE_DATE,''YYYY-MM-DD'') due_date, ' ||
' amv.PHASE, ' ||
' CURSOR ( ' ||
' select ' ||
' rec.PAYMENT_NUMBER, ' ||
' rec.line_type_desc, ' ||
' rec.line_desc, ' ||
' rec.activity_desc, ' ||
' rec.activity_amount, ' ||
' rec.activity_number, ' ||
' to_char(rec.activity_date,''YYYY-MM-DD'') activity_date, ' ||
' rec.trx_currency, ' ||
' rec.receipt_amt_applied_from, ' ||
' rec.receipt_currency, ' ||
' rec.trx_to_receipt_rate ' ||
' from ' ||
' LNS_REC_ACT_CASH_CM_V rec ' ||
' where ' ||
' amv.loan_id = rec.loan_id and ' ||
' amv.AMORTIZATION_SCHEDULE_ID = rec.LOAN_AMORTIZATION_ID ' ||
' ) AS PAYMENT_ACTIVITY_CASH_CM, ' ||
' CURSOR ( ' ||
' select ' ||
' rec.PAYMENT_NUMBER, ' ||
' rec.line_type_desc, ' ||
' rec.line_desc, ' ||
' rec.activity_desc, ' ||
' rec.activity_amount, ' ||
' rec.activity_number, ' ||
' to_char(rec.activity_date,''YYYY-MM-DD'') activity_date, ' ||
' rec.trx_currency, ' ||
' rec.receipt_amt_applied_from, ' ||
' rec.receipt_currency, ' ||
' rec.trx_to_receipt_rate ' ||
' from ' ||
' LNS_REC_ACT_ADJ_V rec ' ||
' where ' ||
' amv.loan_id = rec.loan_id and ' ||
' amv.AMORTIZATION_SCHEDULE_ID = rec.LOAN_AMORTIZATION_ID ' ||
' ) AS PAYMENT_ACTIVITY_ADJ ' ||
' from ' ||
' LNS_AM_SCHEDS_DTL_V amv ' ||
' where amv.loan_id = lh.LOAN_ID and ' ||
' amv.REVERSED_CODE = ''N'' and ' ||
' trunc(amv.DUE_DATE) between dateparameters.from_dt and dateparameters.to_dt ' ||
') AS BILL_PAY_DTL_BY_PAY_NUM, ' ||
'CURSOR( ' ||
' select head.DISB_HEADER_ID, ' ||
' head.DISBURSEMENT_NUMBER, ' ||
' to_char(head.TARGET_DATE, ''MM/DD/YYYY'') target_date, ' ||
' to_char(head.PAYMENT_REQUEST_DATE, ''MM/DD/YYYY'') payment_request_date, ' ||
' head.HEADER_PERCENT, ' ||
' head.HEADER_AMOUNT, ' ||
' fund_status.meaning status, ' ||
' fund_act.meaning activity_name, ' ||
' (select to_char(max(DISBURSEMENT_DATE),''YYYY-MM-DD'') from lns_disb_lines where DISB_HEADER_ID = head.DISB_HEADER_ID) DISBURSEMENT_DATE ' ||
' from lns_disb_headers head, ' ||
' lns_lookups fund_status, ' ||
' lns_lookups fund_act ' ||
' where head.loan_id = lh.loan_id and ' ||
' fund_status.lookup_type(+) = ''FUNDING_STATUS'' and ' ||
' fund_status.lookup_code(+) = head.STATUS and ' ||
' fund_act.lookup_type(+) = ''DISB_ACTIVITY'' and ' ||
' fund_act.lookup_code(+) = head.ACTIVITY_CODE) ' ||
'AS Disbursement_Schedule ' ||
'from ' ||
'lns_loan_headers_all_vl lh, ' ||
'(select lns_rep_utils.get_bill_due_date_from() from_dt, ' ||
' lns_rep_utils.get_bill_due_date_to() to_dt ' ||
' from dual) dateparameters, ' ||
'hz_parties hp, ' ||
'lns_lookups llk, ' ||
'LNS_PAY_SUM_V pay, ' ||
'hz_cust_accounts_all account, ' ||
'hz_locations loc, ' ||
'fnd_territories_vl terr, ' ||
'hz_party_sites site, ' ||
'hz_cust_acct_sites_all acct_site, ' ||
'lns_loan_products_vl product, ' ||
'xle_entity_profiles le, ' ||
'hr_operating_units ou, ' ||
'lns_loan_types_vl lot ' ||
'where ' ||
'lh.primary_borrower_id = hp.party_id and ' ||
'lh.loan_id = pay.loan_id and ' ||
'llk.lookup_code = lh.loan_status and ' ||
'llk.lookup_type = ''LOAN_STATUS'' and ' ||
'lh.loan_status <> ''DELETED'' and ' ||
'lh.CUST_ACCOUNT_ID = account.CUST_ACCOUNT_ID and ' ||
'acct_site.cust_acct_site_id = lh.bill_to_acct_site_id and ' ||
'acct_site.org_id = lh.org_id and ' ||
'site.party_site_id = acct_site.party_site_id and ' ||
'site.location_id = loc.location_id and ' ||
'loc.country = terr.TERRITORY_CODE and ' ||
'lh.loan_class_code = :LOAN_CLASS and ' ||
'lh.loan_type_id = :LOAN_TYPE_ID and ' ||
'lh.loan_currency = :CURRENCY_CODE and ' ||
'lh.product_id = product.LOAN_PRODUCT_ID(+) and ' ||
'le.LEGAL_ENTITY_ID = lh.LEGAL_ENTITY_ID and ' ||
'ou.organization_id = lh.org_id and ' ||
'lh.org_id = :ORG_ID and ' ||
'lot.loan_type_id = lh.loan_type_id and ' ||
'EXISTS ' ||
' (select loan_id ' ||
' from lns_amortization_scheds am ' ||
' where am.loan_id = lh.loan_id and ' ||
' (REVERSED_FLAG is null or REVERSED_FLAG = ''N'') and ' ||
' trunc(am.DUE_DATE) between lns_rep_utils.get_bill_due_date_from() ' ||
' and lns_rep_utils.get_bill_due_date_to())';
SELECT loan_type_desc
into l_loan_type_desc
from lns_loan_types_vl
where loan_type_id = l_loan_type_id;
SELECT name
INTO l_org_name
FROM HR_ALL_ORGANIZATION_UNITS_TL
WHERE organization_id = l_org_id
AND language = userenv('LANG');
SELECT PARTY_NAME
INTO l_borrower_name
FROM hz_parties
WHERE party_id = borrower_id;
SELECT account_number
into l_account_number
FROM hz_cust_accounts
WHERE cust_account_id = l_cust_account_id;
SELECT NAME
INTO l_legal_entity_name
FROM xle_entity_profiles
WHERE legal_entity_id = l_legal_entity_id;
SELECT source_name
INTO l_loan_officer
FROM jtf_rs_resource_extns
WHERE resource_id = loan_assigned_to;
select pay.TOTAL_PRINCIPAL_BALANCE loan_amount
from LNS_PAY_SUM_V pay
where pay.loan_id = pLoanId;
select sys_XMLGen(
XMLELEMENT("ROW", XMLATTRIBUTES (1 AS "num"),
XMLFOREST (
lh.LOAN_ID,
lh.loan_number
,lh.multiple_funding_flag
,xle.name lending_org_name
,hp.party_name borrower_name
,(lh.REQUESTED_AMOUNT + nvl(lh.ADD_REQUESTED_AMOUNT, 0)) loan_requested_amount
,lh.LOAN_CURRENCY
,llklc.meaning LOAN_CLASS
,llt.loan_type_name LOAN_TYPE
,to_char(lh.LOAN_APPLICATION_DATE, 'YYYY-MM-DD') LOAN_APPLICATION_DATE
,to_char(lh.LOAN_START_DATE, 'YYYY-MM-DD') LOAN_START_DATE
,lh.loan_term || ' ' || llktt.meaning loan_term
,ratesch.current_interest_rate initial_interest_rate
,to_char(t.first_payment_date, 'YYYY-MM-DD') payment_start_date
,t.loan_payment_frequency payment_frequency_code
,llkfq.meaning payment_frequency
, llkrt.meaning interest_type
,res.source_name loan_officer
,llkst.meaning loan_subtype
,nvl(lh.collateral_percent,0) COLLATERAL_PERCENTAGE
,llkp.meaning loan_purpose
,intrt.interest_rate_name index_name
,llkdc.meaning day_count_method
,llkic.meaning interest_calculation_method
,t.calculation_method int_calc_method_code
,t.day_count_method day_count_method_code
,t.delinquency_threshold_amount delinquency_overdue_amount
,nvl(t.reamortize_over_payment,'N') reamortize_over_payment
,t.ceiling_rate
,t.floor_rate
,to_char(t.lock_in_date,'YYYY-MM-DD') lock_in_date
,to_char(t.lock_to_date,'YYYY-MM-DD') lock_expiration_date
,llkfqf.meaning floating_frequency
,llkfqf.meaning open_payment_frequency
,to_char(t.open_first_payment_date,'YYYY-MM-DD') open_first_payment_date
,to_char(ldh.target_date,'YYYY-MM-DD') first_disbursement_date
,ldh.header_percent first_disbursement_percent
,ldh.header_amount first_disbursement_amount
,lh.open_to_term_flag
,decode(nvl(lh.open_to_term_flag,'N'),'Y','with','without') open_to_term_str
,nvl(lh.funded_amount,lh.requested_amount) * nvl(lh.collateral_percent,0)
/ 100 collateral_required
,lh.collateral_percent loan_to_value_ratio
,lh.loan_status
--,pay.TOTAL_PRINCIPAL_BALANCE loan_amount --Bug5262505
,pTotalPrincipalBalance loan_amount
,lh.loan_description
,to_char(lh.loan_maturity_date, 'YYYY-MM-DD') loan_maturity_date
,lh.ORG_ID
,lh.LOAN_TYPE LOAN_TYPE_CODE
,lh.LOAN_CLASS_CODE
,LNS_FINANCIALS.getActiveRate(lh.LOAN_ID) current_interest_rate
,lh.LOAN_SUBTYPE
,lh.LAST_BILLED_DATE
,to_char(lh.LOAN_APPROVAL_DATE, 'YYYY-MM-DD') LOAN_APPROVAL_DATE
,loc.address1 || ' ' || loc.city || ' ' || loc.state || ' ' ||
loc.postal_code || ' ' || terr.TERRITORY_SHORT_NAME primary_borrower_addr
,xle.ADDRESS_LINE_1 || ' ' || xle.ADDRESS_LINE_2 || ' ' || xle.ADDRESS_LINE_3 || ' ' ||
xle.POSTAL_CODE || ' ' ||xle.COUNTRY Lender_address
,to_char(lh.loan_approval_date, 'YYYY-MM-DD') loan_approval_date
,lh.exchange_rate_type
,lh.exchange_rate
,to_char(lh.exchange_date, 'YYYY-MM-DD') exchange_date
,product.loan_product_name loan_product
),
-- Loan_Participants
(select
XMLELEMENT("LOAN_PARTICIPANTS",
XMLAGG(
XMLELEMENT("LOAN_PARTICIPANTS_ROW", XMLATTRIBUTES (rownum AS "num"),
XMLFOREST(
party.party_name participant_name,
party.JGZZ_FISCAL_CODE participant_tax_id,
lkup.meaning participant_type,
party.party_type participant_type_code,
party.party_number participant_number,
lnslkup.meaning participant_role,
lp.LOAN_PARTICIPANT_TYPE participant_role_code,
party.address1 || ' ' || party.address2 || ' '
|| party.address3 || ' ' || party.address4 || ' '
|| party.city || ' ' || party.state || ' ' ||
party.postal_code || ' ' || party.county || ' ' ||
ter.territory_short_name participant_address,
party.email_address participant_email_address,
party.URL participant_url,
cp.raw_phone_number primary_phone_number,
contact_person.party_name contact_person_name,
contact_party.email_address contact_email_address,
con_phone.raw_phone_number contact_phone_number
)
)
)
)
FROM LNS_PARTICIPANTS lp, HZ_PARTIES party, AR_LOOKUPS lkup, LNS_LOOKUPS lnslkup,
FND_TERRITORIES_TL ter, HZ_CONTACT_POINTS cp, HZ_PARTIES contact_person,
HZ_PARTIES contact_party, HZ_CONTACT_POINTS con_phone
WHERE party.party_id =lp.HZ_PARTY_ID
AND party.party_type = lkup.lookup_code
AND lkup.lookup_type = 'PARTY_TYPE'
AND lp.LOAN_PARTICIPANT_TYPE = lnslkup.lookup_code
AND lnslkup.lookup_type = 'LNS_PARTICIPANT_TYPE'
AND party.country = ter.TERRITORY_CODE(+)
AND ter.LANGUAGE(+) = userenv('LANG')
AND party.party_id = cp.owner_table_id(+)
AND cp.owner_table_name(+) = 'HZ_PARTIES'
AND cp.contact_point_type(+) = 'PHONE'
AND cp.primary_flag(+) = 'Y'
AND contact_person.party_id(+) =lp.contact_pers_party_id
AND contact_party.party_id(+) =lp.contact_rel_party_id
AND con_phone.owner_table_name(+) = 'HZ_PARTIES'
AND con_phone.owner_table_id(+) =lp.contact_rel_party_id
AND con_phone.primary_flag(+) = 'Y'
AND con_phone.status(+) = 'A'
AND con_phone.contact_point_type(+) = 'PHONE'
AND LOAN_ID = lh.loan_id
), -- end of LOAN_PARTICIPANTS
-- DISB_RATE_SCHEDULE
(select
XMLELEMENT("DISB_RATE_SCHEDULE",
XMLAGG(
XMLELEMENT("DISB_RATE_SCHEDULE_ROW", XMLATTRIBUTES (rownum AS "num"),
XMLFOREST(
BEGIN_INSTALLMENT_NUMBER installment_from
,END_INSTALLMENT_NUMBER installment_to
,to_char(index_date, 'YYYY-MM-DD') index_date
,current_interest_rate interest_rate
)
)
)
)
from lns_rate_schedules rsh
where rsh.term_id = t.term_id
and rsh.end_date_active is null
and nvl(rsh.phase,'TERM') = 'OPEN'
), -- end of DISB_RATE_SCHEDULE
-- RATE_SCHEDULE
(select
XMLELEMENT("RATE_SCHEDULE",
XMLAGG(
XMLELEMENT("RATE_SCHEDULE_ROW", XMLATTRIBUTES (rownum AS "num"),
XMLFOREST(
BEGIN_INSTALLMENT_NUMBER installment_from
,END_INSTALLMENT_NUMBER installment_to
,to_char(index_date, 'YYYY-MM-DD') index_date
,current_interest_rate interest_rate
)
)
)
)
from lns_rate_schedules rsh
where rsh.term_id = t.term_id
and rsh.end_date_active is null
and nvl(rsh.phase,'TERM') = 'TERM'
), -- end of RATE_SCHEDULE
-- COLLATERAL
(select
XMLELEMENT("COLLATERAL",
XMLAGG(
XMLELEMENT("COLLATERAL_ROW", XMLATTRIBUTES (rownum AS "num"),
XMLFOREST(
assetassign.PLEDGED_AMOUNT,
assetassign.START_DATE_ACTIVE,
assetassign.END_DATE_ACTIVE,
lkps1.meaning participant_role,
party.party_name participant_name,
lkps2.meaning asset_class,
lkps3.meaning asset_type,
asset.quantity || ' ' || lkps4.meaning asset_quantity,
lkps5.meaning || ': ' || asset.reference_name reference,
asset.appraiser_name,
asset.next_evaluation_date,
lkps6.meaning valuation_method,
asset.lien_amount,
asset.description, asset.currency_code,
asset.valuation, asset.start_date_active acquired_date
)
)
)
)
FROM LNS_ASSET_ASSIGNMENTS assetassign,
LNS_ASSETS asset,
LNS_LOOKUPS lkps1,
LNS_LOOKUPS lkps2,
LNS_LOOKUPS lkps3,
LNS_LOOKUPS lkps4,
LNS_LOOKUPS lkps5,
LNS_LOOKUPS lkps6,
HZ_PARTIES party,
LNS_PARTICIPANTS par
WHERE assetassign.asset_id = asset.asset_id and
asset.asset_owner_id = par.hz_party_id and
party.party_id = par.hz_party_id and
assetassign.loan_id = par.loan_id and
par.loan_participant_type = lkps1.lookup_code and
lkps1.lookup_type = 'LNS_PARTICIPANT_TYPE' and
asset.asset_class_code = lkps2.lookup_code and
lkps2.lookup_type = 'ASSET_CLASSES' and
asset.asset_type_code = lkps3.lookup_code and
lkps3.lookup_type = asset.asset_class_code and
asset.uom_code = lkps4.lookup_code and
lkps4.lookup_type = 'ASSET_QNT_'||asset.asset_class_code and
asset.reference_type = lkps5.lookup_code and
lkps5.lookup_type = 'ASSET_REF_'||asset.asset_class_code and
asset.valuation_method_code = lkps6.lookup_code and
lkps6.lookup_type = 'VALUATION_METHOD' and
assetassign.loan_id = lh.loan_id
), -- end of COLLATERAL
-- FEES
(select
XMLELEMENT("FEES",
XMLAGG(
XMLELEMENT("FEES_ROW", XMLATTRIBUTES (rownum AS "num"),
XMLFOREST(
lfa.FEE, lfa.FEE_TYPE, lfa.FEE_BASIS, lfa.RATE_TYPE,
lfa.BILLING_OPTION BILLING_OPTION_CODE,
llkbo.meaning BILLING_OPTION,
lf.FEE_NAME,
lf.FEE_CATEGORY FEE_CATEGORY_CODE,
llkfc.meaning FEE_CATEGORY,
lf.rate_type method_code,
llkrt.meaning Fee_method,
fl.meaning update_allowed,
lf.FEE_DESCRIPTION,
decode(lf.RATE_TYPE, 'FIXED',
to_char(lf.FEE,FND_CURRENCY.SAFE_GET_FORMAT_MASK
(nvl(lf.CURRENCY_CODE,'USD'),25))
, to_char(lf.FEE) || '%' ||
decode(lf.FEE_BASIS, null, '', ', ')
|| lkps1.meaning) FEE_VAR_AMOUNT_PERCENT
)
)
)
)
FROM LNS_FEE_ASSIGNMENTS lfa, LNS_FEES_ALL lf, LNS_LOOKUPS lkps1,
LNS_LOOKUPS llkbo,
LNS_LOOKUPS llkrt,
LNS_LOOKUPS llkfc,
fnd_lookups fl
WHERE lfa.FEE_ID = lf.FEE_ID AND
lfa.loan_id = lh.loan_id and
llkfc.lookup_type = 'FEE_CATEGORY' and
llkfc.lookup_code = lf.fee_category and
lkps1.lookup_code(+) = lf.FEE_BASIS AND lkps1.lookup_type(+) = 'FEE_BASIS' and
llkbo.lookup_code(+) = lf.BILLING_OPTION AND
llkbo.lookup_type(+) = 'FEE_BILLING_OPTIONS' AND
llkrt.lookup_code(+) = lf.RATE_TYPE AND llkrt.lookup_type(+) = 'RATE_TYPE' AND
fl.lookup_code(+) = lf.FEE_EDITABLE_FLAG AND fl.lookup_type(+) = 'YES_NO'
), -- end of FEES
-- CONDITIONS
(select
XMLELEMENT("CONDITIONS",
XMLAGG(
XMLELEMENT("CONDITIONS_ROW", XMLATTRIBUTES (rownum AS "num"),
XMLFOREST(
lc.CONDITION_NAME,
lca.CONDITION_DESCRIPTION,
lc.CONDITION_TYPE CONDITION_TYPE_CODE,
lkps1.meaning CONDITION_TYPE, fl.meaning MANDATORY_FLAG
)
)
)
)
FROM LNS_COND_ASSIGNMENTS_VL lca, LNS_CONDITIONS_VL lc,
LNS_LOOKUPS lkps1 , fnd_lookups fl
WHERE lca.CONDITION_ID = lc.CONDITION_ID
AND lkps1.lookup_type = 'CONDITION_TYPE'
AND lkps1.lookup_code = lc.CONDITION_TYPE
AND fl.lookup_type = 'YES_NO'
AND fl.lookup_code = lca.MANDATORY_FLAG
AND nvl(lca.start_date_active, sysdate) <= sysdate
AND nvl(lca.end_date_active, sysdate) >= sysdate
AND lca.LOAN_ID = lh.loan_id
), -- end of CONDITIONS
-- ORIGINAL_RECEIVABLES
(select
XMLELEMENT("ORIGINAL_RECEIVABLES",
XMLAGG(
XMLELEMENT("ORIGINAL_RECEIVABLES_ROW", XMLATTRIBUTES (rownum AS "num"),
XMLFOREST(
lll.REFERENCE_NUMBER original_rec_number,
lll.REFERENCE_AMOUNT original_rec_balance,
lll.REQUESTED_AMOUNT loan_requested_amount,
lll.REFERENCE_NUMBER original_rec_desc,
to_char(cust_trx.term_due_date,'YYYY-MM-DD')
INVOICE_DUE_DATE,
trx_type.name || ' - ' || trx_type_lkup.meaning
inv_trx_type
)
)
)
)
from LNS_LOAN_LINES lll,
RA_CUSTOMER_TRX_ALL cust_trx,
RA_CUST_TRX_TYPES_ALL trx_type,
ar_lookups trx_type_lkup
where lll.loan_id = lh.LOAN_ID and
lll.end_date is null and
cust_trx.cust_trx_type_id = trx_type.CUST_TRX_TYPE_ID and
trx_type.org_id = lh.org_id and
trx_type_lkup.lookup_type = 'INV/CM' and
trx_type_lkup.lookup_code = trx_type.type and
cust_trx.customer_trx_id = lll.reference_id
), -- end of ORIGINAL_RECEIVABLES
-- DISBURSEMENTS
(select
XMLELEMENT("DISBURSEMENTS",
XMLAGG(
XMLELEMENT("DISBURSEMENTS_ROW", XMLATTRIBUTES (rownum AS "num"),
XMLFOREST(
disbursement_number
,llkac.meaning disbursement_activity
,to_char(ldih.target_date,'YYYY-MM-DD') target_date
,ldih.header_percent disbursement_percent
,ldih.header_amount amount_of_disbursement
),
-- PAYEES
(select
XMLELEMENT("PAYEES",
XMLAGG(
XMLELEMENT("PAYEES_ROW", XMLATTRIBUTES (rownum AS "num"),
XMLFOREST(
party.party_name payee_name
,party.ADDRESS1 || ' ' || party.ADDRESS2 || ' ' ||
party.ADDRESS3 || ' ' || party.STATE || ' ' ||
party.POSTAL_CODE || ' ' ||party.COUNTRY payee_address
,ibypm.payment_method_name
,line_percent payee_percent
,line_amount payee_amount
)
)
)
)
from lns_disb_lines ldl
,hz_parties party
,IBY_PAYMENT_METHODS_TL ibypm
where ldl.disb_header_id = ldih.disb_header_id
and party.party_id = ldl.payee_party_id and
ibypm.payment_method_code (+) = ldl.payment_method_code and
ibypm.LANGUAGE (+) = userenv('LANG')
),-- end of PAYEES
-- DISBFEES
(select
XMLELEMENT("DISBFEES",
XMLAGG(
XMLELEMENT("DISBFEES_ROW", XMLATTRIBUTES (rownum AS "num"),
XMLFOREST(
lfa.FEE, lfa.FEE_TYPE,
lfa.FEE_BASIS,
lfa.RATE_TYPE,
lfa.BILLING_OPTION BILLING_OPTION_CODE,
llkbo.meaning BILLING_OPTION,
lf.FEE_NAME,
lf.FEE_CATEGORY FEE_CATEGORY_CODE,
llkfc.meaning FEE_CATEGORY,
lf.rate_type method_code,
llkrt.meaning Fee_method,
fl.meaning update_allowed,
lf.FEE_DESCRIPTION,
decode(lf.RATE_TYPE, 'FIXED', to_char(lf.FEE,FND_CURRENCY.SAFE_GET_FORMAT_MASK
(nvl(lf.CURRENCY_CODE,'USD'),25)), to_char(lf.FEE) || '%' ||
decode(lf.FEE_BASIS, null, '', ', ') || lkps1.meaning)
FEE_VAR_AMOUNT_PERCENT
)
)
)
)
FROM LNS_FEE_ASSIGNMENTS lfa,
LNS_FEES_ALL lf,
LNS_LOOKUPS lkps1,
LNS_LOOKUPS llkbo,
LNS_LOOKUPS llkrt,
LNS_LOOKUPS llkfc,
fnd_lookups fl
WHERE lfa.FEE_ID = lf.FEE_ID AND
lfa.disb_header_id = ldih.disb_header_id and
llkfc.lookup_type = 'FEE_CATEGORY' and
llkfc.lookup_code = lf.fee_category and
lkps1.lookup_code(+) = lf.FEE_BASIS AND lkps1.lookup_type(+) = 'FEE_BASIS' and
llkbo.lookup_code(+) = lf.BILLING_OPTION AND
llkbo.lookup_type(+) = 'FEE_BILLING_OPTIONS' AND
llkrt.lookup_code(+) = lf.RATE_TYPE AND llkrt.lookup_type(+) = 'RATE_TYPE' AND
fl.lookup_code(+) = lf.FEE_EDITABLE_FLAG AND fl.lookup_type(+) = 'YES_NO'
), -- end of DISBFEES
-- DISBCOND
(select
XMLELEMENT("DISBCOND",
XMLAGG(
XMLELEMENT("DISBCOND_ROW", XMLATTRIBUTES (rownum AS "num"),
XMLFOREST(
lc.CONDITION_NAME,
lca.CONDITION_DESCRIPTION,
lc.CONDITION_TYPE CONDITION_TYPE_CODE,
lkps1.meaning CONDITION_TYPE, fl.meaning MANDATORY_FLAG
)
)
)
)
FROM LNS_COND_ASSIGNMENTS_VL lca, LNS_CONDITIONS_VL lc,
LNS_LOOKUPS lkps1 , fnd_lookups fl
WHERE lca.CONDITION_ID = lc.CONDITION_ID
AND lkps1.lookup_type = 'CONDITION_TYPE'
AND lkps1.lookup_code = lc.CONDITION_TYPE
AND fl.lookup_type = 'YES_NO'
AND fl.lookup_code = lca.MANDATORY_FLAG
AND nvl(lca.start_date_active, sysdate) <= sysdate
AND nvl(lca.end_date_active, sysdate) >= sysdate
AND lca.disb_header_id = ldih.disb_header_id
) -- end of DISBCOND
)
)
)
FROM lns_disb_headers ldih
,lns_lookups llkac
WHERE ldih.loan_id = lh.loan_id and
llkac.lookup_code = ldih.activity_code
), -- end of DISBURSEMENTS
-- AMORTIZATION
(select
XMLELEMENT("AMORTIZATION",
XMLAGG(
XMLELEMENT("AMORTIZATION_ROW", XMLATTRIBUTES (rownum AS "num"),
XMLFOREST(
INSTALLMENT_NUMBER PAYMENT_NUMBER
,to_char(DUE_DATE, 'YYYY-MM-DD') DUE_DATE
,PRINCIPAL_AMOUNT PAYMENT_PRINCIPAL
,INTEREST_AMOUNT PAYMENT_INTEREST
,FEE_AMOUNT PAYMENT_FEES
,(PRINCIPAL_AMOUNT+INTEREST_AMOUNT+FEE_AMOUNT) PAYMENT_TOTAL
,OTHER_AMOUNT
,BEGIN_BALANCE BEGINNING_BALANCE
,END_BALANCE ENDING_BALANCE
,INTEREST_CUMULATIVE
,PRINCIPAL_CUMULATIVE
,FEES_CUMULATIVE
,OTHER_CUMULATIVE
,SOURCE
,NORMAL_INT_AMOUNT
,DISBURSEMENT_AMOUNT
,PREV_DEFERRED_INT_AMOUNT
,DEFERRED_INT_AMOUNT
,PREV_CAP_INT_AMOUNT
,CURR_CAP_INT_AMOUNT
,CAP_INT_AMOUNT
,NORMAL_INT_DETAILS
,DEFERRED_INT_DETAILS
,CAP_INT_DETAILS
)
)
)
)
FROM lns_amort_scheds_gt agt
where agt.loan_id = lh.loan_id
) -- end of AMORTIZATION
), XMLFormat.createformat('ROWSET')).getClobVal()
from
lns_loan_headers_all_vl lh,
hz_parties hp,
lns_terms t,
lns_int_rate_headers_vl intrt,
lns_rate_schedules ratesch,
hz_locations loc,
fnd_territories_tl terr,
hz_party_sites site,
hz_cust_acct_sites_all acct_site,
--LNS_PAY_SUM_V pay, --Bug5262505
xle_firstparty_information_v xle,
lns_lookups llkrt,
lns_lookups llktt,
lns_loan_types_vl llt,
lns_lookups llklc,
lns_lookups llkp,
lns_lookups llkst,
lns_lookups llkdc,
lns_lookups llkfq,
lns_lookups llkfqf,
lns_lookups llkic,
jtf_rs_resource_extns res,
lns_disb_headers ldh,
lns_loan_products_all_vl product
where
lh.primary_borrower_id = hp.party_id and
--lh.loan_id = pay.loan_id and --Bug5262505
lh.loan_id = t.loan_id and
ldh.loan_id(+) = lh.loan_id and
ldh.disbursement_number(+) = 1 and
product.loan_product_id(+) = lh.product_id and
t.term_id = ratesch.term_id and
ratesch.begin_installment_number = 1 and
ratesch.end_date_active is null and
((lh.multiple_funding_flag = 'Y' and lh.open_to_term_flag = 'N' and
ratesch.phase = 'OPEN') OR ( ratesch.phase = 'TERM')) and
intrt.interest_rate_id = t.index_rate_id and
xle.legal_entity_id = lh.legal_entity_id and
llktt.lookup_code = lh.loan_term_period and
llktt.lookup_type = 'PERIOD' and
llkrt.lookup_code = t.rate_type and
llkrt.lookup_type = 'RATE_TYPE' and
llkic.lookup_code = t.calculation_method and
llkic.lookup_type = 'INTEREST_CALCULATION_METHOD' and
llklc.lookup_code = lh.loan_class_code and
llklc.lookup_type = 'LOAN_CLASS' and
llt.loan_type_id = lh.loan_type_id and
llkdc.lookup_code = t.day_count_method and
llkdc.lookup_type = 'DAY_COUNT_METHOD' and
llkp.lookup_code (+) = lh.loan_purpose_code and
llkp.lookup_type (+) = 'LOAN_PURPOSE' and
llkst.lookup_code (+) = lh.loan_subtype and
llkst.lookup_type (+) = 'LOAN_SUBTYPE' and
lh.loan_assigned_to = res.resource_id and
llkfq.lookup_code (+) = t.loan_payment_frequency and
llkfq.lookup_type (+) = 'FREQUENCY' and
llkfqf.lookup_code (+) = t.open_payment_frequency and
llkfqf.lookup_type (+) = 'FREQUENCY' and
res.category = 'EMPLOYEE' and
lh.loan_status <> 'DELETED' and
acct_site.cust_acct_site_id = lh.bill_to_acct_site_id and
acct_site.org_id = lh.org_id and
site.party_site_id = acct_site.party_site_id and
site.location_id = loc.location_id and
loc.country = terr.TERRITORY_CODE and
terr.language = userenv('LANG') and
lh.loan_id = X_Loan_Id and
lh.org_id = lh.org_id;
LogMessage(FND_LOG.LEVEL_STATEMENT, 'Inserting into lns_amort_scheds_gt...');
insert into lns_amort_scheds_gt
(
LOAN_ID
,INSTALLMENT_NUMBER
,DUE_DATE
,PRINCIPAL_AMOUNT
,INTEREST_AMOUNT
,FEE_AMOUNT
,OTHER_AMOUNT
,BEGIN_BALANCE
,END_BALANCE
,TOTAL
,INTEREST_CUMULATIVE
,PRINCIPAL_CUMULATIVE
,FEES_CUMULATIVE
,OTHER_CUMULATIVE
,RATE_ID
,SOURCE
,GRAND_TOTAL_FLAG
,NORMAL_INT_AMOUNT
,DISBURSEMENT_AMOUNT
,PREV_DEFERRED_INT_AMOUNT
,DEFERRED_INT_AMOUNT
,PREV_CAP_INT_AMOUNT
,CURR_CAP_INT_AMOUNT
,CAP_INT_AMOUNT
,NORMAL_INT_DETAILS
,DEFERRED_INT_DETAILS
,CAP_INT_DETAILS
)
VALUES (
p_loan_id
,v_INSTALLMENT_NUMBER(j)
,v_DUE_DATE(j)
,v_PRINCIPAL_AMOUNT(j)
,v_INTEREST_AMOUNT(j)
,v_FEE_AMOUNT(j)
,v_OTHER_AMOUNT(j)
,v_BEGIN_BALANCE(j)
,v_END_BALANCE(j)
,v_TOTAL(j)
,v_INTEREST_CUMULATIVE(j)
,v_PRINCIPAL_CUMULATIVE(j)
,v_FEES_CUMULATIVE(j)
,v_OTHER_CUMULATIVE(j)
,v_RATE_ID(j)
,v_SOURCE(j)
,v_GRAND_TOTAL_FLAG(j)
,v_NORMAL_INT_AMOUNT(j)
,v_DISBURSEMENT_AMOUNT(j)
,v_PREV_DEFERRED_INT_AMOUNT(j)
,v_DEFERRED_INT_AMOUNT(j)
,v_PREV_CAP_INT_AMOUNT(j)
,v_CURR_CAP_INT_AMOUNT(j)
,v_CAP_INT_AMOUNT(j)
,v_NORMAL_INT_DETAILS(j)
,v_DEFERRED_INT_DETAILS(j)
,v_CAP_INT_DETAILS(j)
);
SELECT hou.name, gsb.currency_code
INTO l_org_name, l_sob_currency_code
FROM hr_operating_units hou,
gl_sets_of_books gsb,
lns_loan_headers_all loan
WHERE hou.organization_id = loan.org_id
AND gsb.set_of_books_id = hou.set_of_books_id
AND loan.loan_id = p_loan_id;
select lh.loan_status into l_loan_status
from lns_loan_headers_all lh
where lh.loan_id = l_loan_id;
IF (l_loan_status <> 'DELETED' AND l_loan_status <> 'PAIDOFF' AND l_loan_status <> 'REJECTED') THEN
select max(version) into l_version
from lns_loan_documents
where source_table = 'LNS_LOAN_HEADERS_ALL'
and source_id = l_loan_id
and document_type = 'LOAN_AGREEMENT';
FND_MSG_PUB.DELETE_MSG(FND_MSG_PUB.COUNT_MSG);
select count(*) into l_count
from lns_loan_documents
where source_id = l_loan_id;
LogMessage(FND_LOG.LEVEL_STATEMENT,'For incomplete loan, inserting loan agreement for first time');
LNS_LOAN_DOCUMENTS_PKG.Insert_Row(X_DOCUMENT_ID => l_document_id
,P_SOURCE_ID => l_loan_id
,P_SOURCE_TABLE =>'LNS_LOAN_HEADERS_ALL'
,P_DOCUMENT_TYPE => 'LOAN_AGREEMENT'
,P_VERSION => l_version
,P_DOCUMENT_XML => l_result_xml
,P_OBJECT_VERSION_NUMBER => l_object_version_number
,P_REASON => l_reason);
LNS_LOAN_DOCUMENTS_PKG.Update_Row(X_DOCUMENT_ID => l_document_id
,P_SOURCE_ID => l_loan_id
,P_SOURCE_TABLE =>'LNS_LOAN_HEADERS_ALL'
,P_DOCUMENT_TYPE => 'LOAN_AGREEMENT'
,P_VERSION => l_version
,P_DOCUMENT_XML => l_result_xml
,P_OBJECT_VERSION_NUMBER => l_object_version_number
,P_REASON => l_reason);
LNS_LOAN_DOCUMENTS_PKG.Insert_Row(X_DOCUMENT_ID => l_document_id
,P_SOURCE_ID => l_loan_id
,P_SOURCE_TABLE =>'LNS_LOAN_HEADERS_ALL'
,P_DOCUMENT_TYPE => 'LOAN_AGREEMENT'
,P_VERSION => l_version
,P_DOCUMENT_XML => l_result_xml
,P_OBJECT_VERSION_NUMBER => l_object_version_number
,P_REASON => l_reason);
select document_xml
from lns_loan_documents
where source_table = 'LNS_LOAN_HEADERS_ALL'
and source_id = p_loan_id
and version = 1
and document_type = 'LOAN_AGREEMENT';
select lh.loan_status into l_loan_status
from lns_loan_headers_all lh
where lh.loan_id = P_LOAN_ID;
FND_MSG_PUB.DELETE_MSG(FND_MSG_PUB.COUNT_MSG);
LogMessage(FND_LOG.LEVEL_STATEMENT,'Inserting loan agreement for first time...');
LNS_LOAN_DOCUMENTS_PKG.Insert_Row(X_DOCUMENT_ID => l_document_id
,P_SOURCE_ID => p_loan_id
,P_SOURCE_TABLE =>'LNS_LOAN_HEADERS_ALL'
,P_DOCUMENT_TYPE => 'LOAN_AGREEMENT'
,P_VERSION => 1
,P_DOCUMENT_XML => l_result_xml
,P_OBJECT_VERSION_NUMBER => 1
,P_REASON => l_reason);
LNS_LOAN_DOCUMENTS_PKG.Update_Row(X_DOCUMENT_ID => l_document_id
,P_SOURCE_ID => p_loan_id
,P_SOURCE_TABLE =>'LNS_LOAN_HEADERS_ALL'
,P_DOCUMENT_TYPE => 'LOAN_AGREEMENT'
,P_VERSION => l_version
,P_DOCUMENT_XML => l_result_xml
,P_OBJECT_VERSION_NUMBER => l_object_version_number
,P_REASON => l_reason);
select org_id from lns_loan_headers_all where loan_id = P_LOAN_ID;
SELECT
lower(iso_language),iso_territory
INTO
l_iso_language,l_iso_territory
FROM
FND_LANGUAGES
WHERE
language_code = USERENV('LANG');
select
head.OBJECT_VERSION_NUMBER,
head.loan_id,
head.loan_number
from
LNS_LOAN_HEADERS_ALL head
where
head.loan_assigned_to = p_from_loan_officer;
LNS_LOAN_HEADER_PUB.UPDATE_LOAN(P_OBJECT_VERSION_NUMBER => l_version_number,
P_LOAN_HEADER_REC => l_loan_header_rec,
P_INIT_MSG_LIST => FND_API.G_FALSE,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data);
/* query all participants whose access levels need to be updated */
CURSOR part_to_be_updated_cur (P_ORG_ID NUMBER, P_BORROWER_ID NUMBER, P_PRODUCT_ID NUMBER, P_LOAN_PARTICIPANT_TYPE VARCHAR2, P_ACCESS_LEVEL VARCHAR2) IS
select parts.PARTICIPANT_ID,
parts.LOAN_ID,
loans.LOAN_NUMBER,
parts.OBJECT_VERSION_NUMBER,
parts.ACCESS_LEVEL
from LNS_PARTICIPANTS parts,
LNS_LOAN_HEADERS loans
where loans.loan_id = parts.loan_id
and loans.ORG_ID = nvl(P_ORG_ID, loans.ORG_ID)
and loans.PRIMARY_BORROWER_ID = nvl(P_BORROWER_ID, loans.PRIMARY_BORROWER_ID)
and loans.PRODUCT_ID = nvl(P_PRODUCT_ID, loans.PRODUCT_ID)
and parts.LOAN_PARTICIPANT_TYPE = P_LOAN_PARTICIPANT_TYPE
and nvl(parts.ACCESS_LEVEL, 'GLOBAL') <> decode(P_ACCESS_LEVEL, 'GLOBAL', 'GLOBAL', P_ACCESS_LEVEL)
order by parts.LOAN_ID, parts.PARTICIPANT_ID;
open part_to_be_updated_cur(ORG_ID, BORROWER_ID, PRODUCT_ID, 'PRIMARY_BORROWER', BORR_ACCESS_LEVEL);
fetch part_to_be_updated_cur into
l_participant_rec.PARTICIPANT_ID,
l_loan_id,
l_loan_number,
l_OBJECT_VERSION_NUMBER,
l_ACCESS_LEVEL;
exit when part_to_be_updated_cur%NOTFOUND;
LogMessage(FND_LOG.LEVEL_STATEMENT, 'Calling LNS_PARTICIPANTS_PUB.UPDATEPARTICIPANT...');
LNS_PARTICIPANTS_PUB.UPDATEPARTICIPANT (
p_init_msg_list => FND_API.G_TRUE
,p_validation_level => FND_API.G_VALID_LEVEL_FULL
,p_loan_participant_rec => l_participant_rec
,X_OBJECT_VERSION_NUMBER => l_OBJECT_VERSION_NUMBER
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
LogMessage(FND_LOG.LEVEL_STATEMENT, 'Participant updated successfully');
close part_to_be_updated_cur;
open part_to_be_updated_cur(ORG_ID, BORROWER_ID, PRODUCT_ID, 'COBORROWER', COBORR_ACCESS_LEVEL);
fetch part_to_be_updated_cur into
l_participant_rec.PARTICIPANT_ID,
l_loan_id,
l_loan_number,
l_OBJECT_VERSION_NUMBER,
l_ACCESS_LEVEL;
exit when part_to_be_updated_cur%NOTFOUND;
LogMessage(FND_LOG.LEVEL_STATEMENT, 'Calling LNS_PARTICIPANTS_PUB.UPDATEPARTICIPANT...');
LNS_PARTICIPANTS_PUB.UPDATEPARTICIPANT (
p_init_msg_list => FND_API.G_TRUE
,p_validation_level => FND_API.G_VALID_LEVEL_FULL
,p_loan_participant_rec => l_participant_rec
,X_OBJECT_VERSION_NUMBER => l_OBJECT_VERSION_NUMBER
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
LogMessage(FND_LOG.LEVEL_STATEMENT, 'Participant updated successfully');
close part_to_be_updated_cur;
open part_to_be_updated_cur(ORG_ID, BORROWER_ID, PRODUCT_ID, 'GUARANTOR', GUAR_ACCESS_LEVEL);
fetch part_to_be_updated_cur into
l_participant_rec.PARTICIPANT_ID,
l_loan_id,
l_loan_number,
l_OBJECT_VERSION_NUMBER,
l_ACCESS_LEVEL;
exit when part_to_be_updated_cur%NOTFOUND;
LogMessage(FND_LOG.LEVEL_STATEMENT, 'Calling LNS_PARTICIPANTS_PUB.UPDATEPARTICIPANT...');
LNS_PARTICIPANTS_PUB.UPDATEPARTICIPANT (
p_init_msg_list => FND_API.G_TRUE
,p_validation_level => FND_API.G_VALID_LEVEL_FULL
,p_loan_participant_rec => l_participant_rec
,X_OBJECT_VERSION_NUMBER => l_OBJECT_VERSION_NUMBER
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
LogMessage(FND_LOG.LEVEL_STATEMENT, 'Participant updated successfully');
close part_to_be_updated_cur;
'select LOAN_ID, ' ||
'LOAN_NUMBER, ' ||
'LOAN_DESCRIPTION, ' ||
'PRIMARY_BORROWER_ID, ' ||
'(REQUESTED_AMOUNT + NVL(ADD_REQUESTED_AMOUNT, 0)), ' ||
'FUNDED_AMOUNT, ' ||
'LOAN_CURRENCY, ' ||
'ORG_ID, ' ||
'LEGAL_ENTITY_ID, ' ||
'LOAN_STATUS ' ||
'from LNS_LOAN_HEADERS_ALL_VL';
delete from lns_am_scheds_exports
where loan_id = l_LOAN_ID
and SCHEDULE_TYPE = l_BASED_ON_TERMS;
delete from lns_am_scheds_exports
where loan_id = l_LOAN_ID
and SCHEDULE_TYPE = 'ORIGINAL';
l_amort_tbl.delete;
v_INSTALLMENT_NUMBER.delete;
v_DUE_DATE.delete;
v_PRINCIPAL_AMOUNT.delete;
v_INTEREST_AMOUNT.delete;
v_FEE_AMOUNT.delete;
v_OTHER_AMOUNT.delete;
v_BEGIN_BALANCE.delete;
v_END_BALANCE.delete;
v_TOTAL.delete;
v_INTEREST_CUMULATIVE.delete;
v_PRINCIPAL_CUMULATIVE.delete;
v_FEES_CUMULATIVE.delete;
v_OTHER_CUMULATIVE.delete;
v_UNPAID_PRIN.delete;
v_UNPAID_INT.delete;
v_INTEREST_RATE.delete;
v_PERIOD.delete;
v_DEFERRED_INT_AMOUNT.delete;
LogMessage(FND_LOG.LEVEL_STATEMENT, 'Inserting records...');
insert into lns_am_scheds_exports
(
LOAN_ID
,SCHEDULE_TYPE
,CREATION_DATE
,INSTALLMENT_NUMBER
,DUE_DATE
,PRINCIPAL_AMOUNT
,INTEREST_AMOUNT
,FEE_AMOUNT
,OTHER_AMOUNT
,BEGIN_BALANCE
,END_BALANCE
,TOTAL
,INTEREST_CUMULATIVE
,PRINCIPAL_CUMULATIVE
,FEES_CUMULATIVE
,OTHER_CUMULATIVE
,UNPAID_PRIN
,UNPAID_INT
,INTEREST_RATE
,PERIOD
,DEFERRED_INT_AMOUNT
,REQUEST_ID
)
VALUES
(
l_loan_id
,l_BASED_ON_TERMS
,sysdate
,v_INSTALLMENT_NUMBER(i)
,v_DUE_DATE(i)
,v_PRINCIPAL_AMOUNT(i)
,v_INTEREST_AMOUNT(i)
,v_FEE_AMOUNT(i)
,v_OTHER_AMOUNT(i)
,v_BEGIN_BALANCE(i)
,v_END_BALANCE(i)
,v_TOTAL(i)
,v_INTEREST_CUMULATIVE(i)
,v_PRINCIPAL_CUMULATIVE(i)
,v_FEES_CUMULATIVE(i)
,v_OTHER_CUMULATIVE(i)
,v_UNPAID_PRIN(i)
,v_UNPAID_INT(i)
,v_INTEREST_RATE(i)
,v_PERIOD(i)
,v_DEFERRED_INT_AMOUNT(i)
,FND_GLOBAL.Conc_Request_Id
);
l_amort_tbl.delete;
v_INSTALLMENT_NUMBER.delete;
v_DUE_DATE.delete;
v_PRINCIPAL_AMOUNT.delete;
v_INTEREST_AMOUNT.delete;
v_FEE_AMOUNT.delete;
v_OTHER_AMOUNT.delete;
v_BEGIN_BALANCE.delete;
v_END_BALANCE.delete;
v_TOTAL.delete;
v_INTEREST_CUMULATIVE.delete;
v_PRINCIPAL_CUMULATIVE.delete;
v_FEES_CUMULATIVE.delete;
v_OTHER_CUMULATIVE.delete;
v_UNPAID_PRIN.delete;
v_UNPAID_INT.delete;
v_INTEREST_RATE.delete;
v_PERIOD.delete;
v_DEFERRED_INT_AMOUNT.delete;
LogMessage(FND_LOG.LEVEL_STATEMENT, 'Inserting records...');
insert into lns_am_scheds_exports
(
LOAN_ID
,SCHEDULE_TYPE
,CREATION_DATE
,INSTALLMENT_NUMBER
,DUE_DATE
,PRINCIPAL_AMOUNT
,INTEREST_AMOUNT
,FEE_AMOUNT
,OTHER_AMOUNT
,BEGIN_BALANCE
,END_BALANCE
,TOTAL
,INTEREST_CUMULATIVE
,PRINCIPAL_CUMULATIVE
,FEES_CUMULATIVE
,OTHER_CUMULATIVE
,UNPAID_PRIN
,UNPAID_INT
,INTEREST_RATE
,PERIOD
,DEFERRED_INT_AMOUNT
,REQUEST_ID
)
VALUES
(
l_loan_id
,'ORIGINAL'
,sysdate
,v_INSTALLMENT_NUMBER(i)
,v_DUE_DATE(i)
,v_PRINCIPAL_AMOUNT(i)
,v_INTEREST_AMOUNT(i)
,v_FEE_AMOUNT(i)
,v_OTHER_AMOUNT(i)
,v_BEGIN_BALANCE(i)
,v_END_BALANCE(i)
,v_TOTAL(i)
,v_INTEREST_CUMULATIVE(i)
,v_PRINCIPAL_CUMULATIVE(i)
,v_FEES_CUMULATIVE(i)
,v_OTHER_CUMULATIVE(i)
,v_UNPAID_PRIN(i)
,v_UNPAID_INT(i)
,v_INTEREST_RATE(i)
,v_PERIOD(i)
,v_DEFERRED_INT_AMOUNT(i)
,FND_GLOBAL.Conc_Request_Id
);