[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select event_id
from xla_transaction_entities xlee
,xla_events xle
where xle.application_id = 206
and xle.entity_id = xlee.entity_id
and xlee.source_id_int_1 = p_loan_id
and xle.budgetary_control_flag = 'Y'
and xle.event_type_code = 'FUTURE_DISBURSEMENT_CANCELLED'
and xle.process_status_code <> 'P';
select nvl(p.BDGT_REQ_FOR_APPR_FLAG, 'N')
,nvl(h.funds_reserved_flag, 'N')
,nvl(h.gl_date, sysdate)
from lns_loan_headers h,
lns_loan_products p
where p.loan_product_id = h.product_id
and h.loan_id = p_loan_id;
select object_version_number
from lns_loan_headers
where loan_id = p_loan_id;
select disb_header_id
from lns_disb_headers
where loan_id = p_loan_id
and disbursement_number = 1;
update lns_distributions
set event_id = l_event_id
,last_update_date = sysdate
,object_Version_number = object_version_number + 1
where distribution_type = 'ORIGINATION'
and loan_id = p_loan_id
and activity = 'LNS_REMAIN_DISB_CANCEL';
logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Updated event_id '||l_event_id||' succesfully for '||SQL%ROWCOUNT||' rows');
logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'inserting into PSA_BC_XLA_EVENTS_GT - event => ' || l_event_id);
insert into PSA_BC_XLA_EVENTS_GT (event_id, result_code)
values (l_event_id, 'FAIL');
select xle.event_id, xll.source_distribution_id_num_1, ledger_id
from xla_transaction_entities xlee
,xla_events xle
,xla_distribution_links xll
where xle.application_id = 206
and xle.entity_id = xlee.entity_id
and xlee.source_id_int_1 = c_loan_id
-- and nvl(xlee.source_id_int_3, -1) = nvl(c_loan_amount_adj_id, -1)
and xle.budgetary_control_flag = 'Y'
and xll.event_id =xle.event_id
order by event_id desc;
select max(ladj.loan_amount_adj_id)
from lns_loan_amount_adjs ladj
,xla_transaction_entities xlee
,xla_events xle
where ladj.loan_id = c_loan_id
and xlee.entity_id = xle.entity_id
and xle.event_type_code in ('DIRECT_LOAN_ADJ_APPROVED', 'DIRECT_LOAN_ADJ_REVERSED')
and xlee.source_id_int_1 = ladj.loan_id
and xlee.source_id_int_3 = ladj.loan_amount_adj_id
and ladj.status in ('PENDING', 'APPROVED')
order by ladj.loan_amount_adj_id desc;
DELETE FROM PSA_BC_REPORT_EVENTS_GT;
logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'inserting into psa table...');
INSERT INTO PSA_BC_REPORT_EVENTS_GT
(event_id
,SOURCE_DISTRIBUTION_ID_NUM_1
,SOURCE_DISTRIBUTION_ID_NUM_2)
values(l_event_id
,l_distribution_id
,null);
select PSA_BC_XML_REPORT_S.nextval
into l_sequence_id
from dual;
SELECT count(*) INTO l_count
FROM PSA_BC_REPORT_EVENTS_GT;
select nvl(p.BDGT_REQ_FOR_APPR_FLAG, 'N')
,nvl(h.funds_reserved_flag, 'N')
,h.loan_start_date -- fix for bug 16068385
from lns_loan_headers h,
lns_loan_products p
where p.loan_product_id = h.product_id
and h.loan_id = p_loan_id;
select event_id
from xla_transaction_entities xlee
,xla_events xle
where xle.application_id = 206
and xle.entity_id = xlee.entity_id
and xlee.source_id_int_1 = p_loan_id
and xle.budgetary_control_flag = 'Y'
and xle.event_type_code = 'DIRECT_LOAN_APPROVED'
and xle.process_status_code <> 'P'
order by event_id desc;
select disb_header_id
from lns_disb_headers
where loan_id = p_loan_id
and disbursement_number = 1;
select object_version_number
from lns_loan_headers
where loan_id = p_loan_id;
select count(1)
from xla_transaction_entities xlee
,xla_events xle
where xle.application_id = 206
and xle.entity_id = xlee.entity_id
and xlee.source_id_int_1 = p_loan_id
-- and xlee.source_id_int_2 = p_disb_header_id
and xle.budgetary_control_flag = 'Y';
update lns_distributions
set event_id = x_event_id
,last_update_date = sysdate
where distribution_type = 'ORIGINATION'
and loan_id = p_loan_id
-- and loan_amount_adj_id IS NULL;
logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Updated event_id succesfully for '||SQL%ROWCOUNT||' rows');
-- Bug#9328437, First time, when we do fundsCheck, the event_id creates and updates in lns_distributions table.
-- However if we do fundsCheck/fundsReserver later, existed distribtuions are deleted and again
-- defaulted, which has event_id as NULL. So, update the event_id if it is already created whose event_id is NULL
logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'stamping eventID on lns_distributions whose eventID is NULL');
update lns_distributions
set event_id = l_event_id
,last_update_date = sysdate
where distribution_type = 'ORIGINATION'
and event_id IS NULL
and loan_id = p_loan_id;
logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Updated event_id succesfully for '||SQL%ROWCOUNT||' rows');
logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'inserting into PSA_BC_XLA_EVENTS_GT ');
insert
into PSA_BC_XLA_EVENTS_GT (event_id, result_code)
values (l_event_id, 'FAIL');
LNS_LOAN_HEADER_PUB.UPDATE_LOAN(P_OBJECT_VERSION_NUMBER => l_version
,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);
logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'update loan status = ' || l_return_status);
|| PRIVATE PROCEDURE do_insert_distributions
||
|| DESCRIPTION
|| this procedure insert records into lns_distributions table
||
|| PARAMETERS p_distributions_tbl => table -f distribution records
||
|| Return value: NA
||
|| Source Tables:
||
|| Target Tables: LNS_DISTRIBUTIONS
||
|| KNOWN ISSUES
||
|| NOTES
||
|| MODIFICATION HISTORY
|| Date Author Description of Changes
|| 04-20-2005 raverma Created
*=======================================================================*/
procedure do_insert_distributions(p_distributions_tbl in lns_distributions_pub.distribution_tbl
,p_loan_id in number)
is
l_total_distributions number;
l_api_name := 'do_insert_distributions';
logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Inserting row : ' || k);
Insert into lns_distributions
(DISTRIBUTION_ID
,LOAN_ID
,LINE_TYPE
,ACCOUNT_NAME
,CODE_COMBINATION_ID
,ACCOUNT_TYPE
,DISTRIBUTION_PERCENT
,DISTRIBUTION_AMOUNT
,DISTRIBUTION_TYPE
,EVENT_ID
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,OBJECT_VERSION_NUMBER
,DISB_HEADER_ID
,LOAN_AMOUNT_ADJ_ID
,LOAN_LINE_ID
,ACTIVITY
,ACCRUAL_PERIOD
,ACCRUAL_DATE
,ACCRUAL_COMPLETED_FLAG
,PROJECT_ID
,AWARD_ID
,TASK_ID
,EXPENDITURE_TYPE
,EXPENDITURE_ITEM_DATE)
values
(LNS_DISTRIBUTIONS_S.nextval
,p_loan_id
,p_distributions_tbl(k).line_type
,p_distributions_tbl(k).account_name
,p_distributions_tbl(k).code_combination_id
,p_distributions_tbl(k).account_type
,p_distributions_tbl(k).distribution_percent
,p_distributions_tbl(k).distribution_amount
,p_distributions_tbl(k).distribution_type
,p_distributions_tbl(k).event_id
,lns_utility_pub.creation_date
,lns_utility_pub.created_by
,lns_utility_pub.last_update_date
,lns_utility_pub.last_updated_by
,1
,p_distributions_tbl(k).disb_header_id
,p_distributions_tbl(k).loan_amount_adj_id
,p_distributions_tbl(k).loan_line_id
,p_distributions_tbl(k).activity
,p_distributions_tbl(k).accrual_period
,p_distributions_tbl(k).accrual_date
,p_distributions_tbl(k).accrual_completed_flag
,p_distributions_tbl(k).project_id
,p_distributions_tbl(k).award_id
,p_distributions_tbl(k).task_id
,p_distributions_tbl(k).expenditure_type
,p_distributions_tbl(k).EXPENDITURE_ITEM_DATE);
end do_insert_distributions;
select h.loan_class_code
,t.loan_type_id
-- ,h.funded_amount
,h.requested_amount -- Bug#9755933
from lns_loan_headers_all h
,lns_loan_types t
where h.loan_id = p_loan_id
and h.loan_type_id = t.loan_type_id;
select h.loan_class_code
,t.loan_type_id
,d.header_amount
from lns_loan_headers_all h
,lns_loan_types t
,lns_disb_headers d
where h.loan_id = p_loan_id
and h.loan_type_id = t.loan_type_id
and h.loan_id = d.loan_id
and d.disb_header_id = p_disb_header_id;
select h.loan_class_code
,t.loan_type_id
,ladj.adjustment_amount
from lns_loan_headers_all h
,lns_loan_types t
,LNS_LOAN_AMOUNT_ADJS ladj
where h.loan_id = p_loan_id
and h.loan_type_id = t.loan_type_id
and h.loan_id = ladj.loan_id
and ladj.status = 'PENDING'
and ladj.loan_amount_adj_id = c_loan_amount_adj_id;
select count(1)
from lns_default_distribs
where loan_class = p_loan_class
AND loan_type_id = p_loan_type_id
and account_name = 'LOAN_RECEIVABLE'
and distribution_type = 'ORIGINATION'
and account_type = 'DR';
select count(1)
from lns_default_distribs
where loan_class = p_loan_class
AND loan_type_id = p_loan_type_id
and account_name = 'LOAN_PAYABLE'
and distribution_type = 'FUNDING'
and account_type = 'DR';
vPLSQL := 'SELECT d.line_type ' ||
' ,d.account_name ' ||
' ,d.code_combination_id ' ||
' ,d.account_type ' ||
' ,d.distribution_percent ' ||
' ,d.distribution_type ' ||
' ,d.project_id ' ||
' ,d.award_id ' ||
' ,d.task_id ' ||
' ,d.expenditure_type ' ||
' ,d.expenditure_item_date ' ||
'FROM lns_default_distribs d ' ||
'WHERE ((d.loan_class = :p_loan_class_code AND d.loan_type_id = :p_loan_type_id) ) ' ||
' AND account_name IN (''PRINCIPAL_RECEIVABLE'', ''INTEREST_RECEIVABLE'', ''INTEREST_INCOME'' ';
|| Overview: will write to xla_events table and update lns_distributions
|| this can handle a set of accounting event records
||
|| PSEUDO CODE/LOGIC
||
|| PARAMETERS
|| Parameter: p_acc_event_tbl => table of accounting records
|| ,p_event_type_code => seeded code for loans "APPROVED", "DISBURSEMENT_FUNDED",
|| "FUTURE_DISBURSEMENT_CANCELLED", "DIRECT_LOAN_ADJ_APPROVED",
|| "DIRECT_LOAN_ADJ_REVERSED", "APPROVED", "ERS_LOAN_ADD_REC_APPROVED"
|| ,p_event_date => most likely GL_DATE
|| ,p_event_status => event Status
|| CONSTANT = 'U'; -- event status:unprocessed
select h.loan_class_code
,h.loan_type_id
from lns_loan_headers_all h
where h.loan_id = p_loan_id;
update lns_distributions
set event_id = l_event_id
,last_update_date = sysdate
,object_version_number = object_version_number + 1
where loan_id = p_acc_event_tbl(k).loan_id
and activity = l_activity
and nvl(disb_header_id, -1) = nvl(p_acc_event_tbl(k).disb_header_id,-1)
and nvl(loan_amount_adj_id, -1) = nvl(p_acc_event_tbl(k).loan_amount_adj_id, -1);
select MFAR_NATURAL_ACCOUNT_REC
from lns_default_distribs_all d
,lns_loan_headers_all h
where account_name = 'MFAR_FUND_ACCOUNT_CHANGE'
and h.loan_id = p_loan_id
and h.loan_class_code = d.loan_class
and h.loan_type_id = d.loan_type_id
and h.org_id = d.org_id;
select MFAR_NATURAL_ACCOUNT_REC
from lns_default_distribs d
,lns_loan_headers h
where account_name = 'MFAR_FUND_ACCOUNT_CHANGE'
and h.loan_id = p_loan_id
and h.loan_class_code = d.loan_class
and h.loan_type_id = d.loan_type_id;
select loan_class
,loan_type_id
,line_type
,account_name
,code_combination_id
,distribution_percent
,distribution_type
,FEE_ID
,ORG_ID
,MFAR_NATURAL_ACCOUNT_REC
,PROJECT_ID
,TASK_ID
,AWARD_ID
,EXPENDITURE_TYPE
,EXPENDITURE_ITEM_DATE
from lns_default_distribs
where loan_class = p_loan_class
and loan_type_id = p_loan_type_id
and account_type = p_acct_type
and account_name = p_acct_name
and line_type = p_line_type
and distribution_type = p_distribution_type
and distribution_percent > 0
order by code_combination_id;
select d.distribution_id
,d.loan_id
,d.line_type
,d.account_name
,d.code_combination_id
,d.distribution_percent
,d.distribution_amount
,d.distribution_type
,d.event_id
,d.project_id
,d.task_id
,d.award_id
,d.expenditure_type
,d.expenditure_item_date
from lns_distributions d
where d.loan_id = x_loan_id
and d.account_type = x_acct_type
and d.account_name = x_acct_name
and d.line_type = x_line_type
and d.distribution_type = x_distribution_type
and d.distribution_percent >= 0
order by d.code_combination_id;
x_distribution_tbl.delete;
select distribution_id
,loan_id
,line_type
,account_name
,code_combination_id
,distribution_percent
,distribution_amount
,distribution_type
,account_type
,account_name
,event_id
,project_id
,task_id
,award_id
,expenditure_type
,expenditure_item_date
from lns_distributions
where distribution_id = x_distribution_id
and distribution_percent >= 0;
select d.distribution_id
,d.loan_id
,d.line_type
,d.account_name
,d.account_type
,d.code_combination_id
,d.distribution_percent
,d.distribution_amount
,d.distribution_type
,d.event_id
,d.project_id
,d.task_id
,d.award_id
,d.expenditure_type
,d.expenditure_item_date
from lns_distributions d
where d.loan_id = x_loan_id
and d.loan_line_id = x_loan_line_id
and d.account_type = x_acct_type
and d.account_name = x_acct_name
and d.line_type = x_line_type
and d.distribution_type = x_distribution_type
and d.distribution_percent > 0
order by d.code_combination_id;
x_distribution_tbl.delete;
SELECT so.set_of_books_id
,sb.name
,sb.short_name
,sb.chart_of_accounts_id
,sb.period_set_name
,sb.currency_code
,fndc.precision
FROM lns_system_options so,
gl_ledgers sb,
fnd_currencies fndc
WHERE sb.ledger_id = so.set_of_books_id
and sb.currency_code = fndc.currency_code;
select round(lnh.funded_amount * lnd.distribution_percent / 100, curr.precision)
from lns_distributions lnd
,lns_loan_headers lnh
,fnd_currencies curr
where lnh.loan_id = lnd.loan_id
and curr.currency_code = lnh.loan_currency
and lnd.distribution_id = p_distribution_id;
select max(distribution_id)
from lns_distributions lnd
,lns_loan_headers lnh
where lnh.loan_id = lnd.loan_id
and lnd.distribution_type = p_distribution_type
and lnd.account_type = p_account_type
and lnh.loan_id = p_loan_id;
select
lnh.funded_amount -
(round(lnh.funded_amount *
(select sum(distribution_percent) / 100
from lns_distributions
where distribution_id <> p_distribution_id
and distribution_type = p_distribution_type
and account_type = p_account_type
and loan_id = p_loan_id), curr.precision))
from lns_distributions lnd
,lns_loan_headers lnh
,fnd_currencies curr
where lnh.loan_id = lnd.loan_id
and lnh.loan_id = p_loan_id
and curr.currency_code = lnh.loan_currency;
select lnh.exchange_rate_type
,lnh.exchange_rate
,lnh.exchange_date
,lnh.loan_currency
from lns_loan_headers lnh
where loan_id = p_loan_id;
select round(lnh.funded_amount * lnd.distribution_percent / 100, curr.precision)
from lns_distributions lnd
,lns_loan_headers lnh
,fnd_currencies curr
where lnh.loan_id = lnd.loan_id
and curr.currency_code = lnh.loan_currency
and lnd.distribution_id = p_distribution_id;
select
lnh.funded_amount -
(round(lnh.funded_amount *
(select sum(distribution_percent) / 100
from lns_distributions
where distribution_id <> p_distribution_id
and distribution_type = p_distribution_type
and account_type = p_account_type
and loan_id = p_loan_id), curr.precision))
from lns_distributions lnd
,lns_loan_headers lnh
,fnd_currencies curr
where lnh.loan_id = lnd.loan_id
and lnh.loan_id = p_loan_id
and curr.currency_code = lnh.loan_currency;
select lnh.loan_currency
from lns_loan_headers lnh
,lns_distributions lnd
where lnh.loan_id = lnd.loan_id
and lnd.distribution_id = p_distribution_id;
SELECT sb.currency_code
FROM lns_system_options so,
gl_sets_of_books sb
WHERE sb.set_of_books_id = so.set_of_books_id;
SELECT s.segment_num
FROM fnd_id_flex_segments s
, fnd_segment_attribute_values sav
, fnd_segment_attribute_types sat
, lns_system_options lso
, gl_ledgers gl
WHERE s.application_id = p_application_id
and lso.set_of_books_id = gl.ledger_id
AND s.id_flex_code = p_flex_code
AND s.id_flex_num = gl.chart_of_accounts_id
AND s.enabled_flag = 'Y'
AND s.application_column_name = sav.application_column_name
AND sav.application_id = p_application_id
AND sav.id_flex_code = p_flex_code
AND sav.id_flex_num = gl.chart_of_accounts_id
AND sav.attribute_value = 'Y'
AND sav.segment_attribute_type = sat.segment_attribute_type
AND sat.application_id = p_application_id
AND sat.id_flex_code = p_flex_code
AND sat.unique_flag = 'Y'
and sat.segment_attribute_type = p_segment_attribute_type;
SELECT s.flex_value_set_id
FROM fnd_id_flex_segments s
,fnd_segment_attribute_values sav
,fnd_segment_attribute_types sat
,lns_system_options lso
,gl_ledgers gl
WHERE s.application_id = 101
and lso.set_of_books_id = gl.ledger_id
AND s.id_flex_code = 'GL#'
AND s.id_flex_num = gl.chart_of_accounts_id
AND s.enabled_flag = 'Y'
AND s.application_column_name = sav.application_column_name
AND sav.application_id = 101
AND sav.id_flex_code = 'GL#'
AND sav.id_flex_num = gl.chart_of_accounts_id
AND sav.attribute_value = 'Y'
AND sav.segment_attribute_type = sat.segment_attribute_type
AND sat.application_id = 101
AND sat.id_flex_code = 'GL#'
AND sat.unique_flag = 'Y'
AND sat.segment_attribute_type = p_segment_attribute_type;
select loan_class_code, loan_status
from lns_loan_headers_all
where loan_id = p_loan_id;
SELECT dist.distribution_id
,dist.line_type
,dist.account_name
,dist.account_type
,dist.code_combination_id
,dist.distribution_percent
,dist.distribution_amount
,dist.distribution_type
,dist.event_id
from lns_distributions dist
where dist.loan_id = C_LOAN_ID
and dist.account_type = 'CR'
and dist.account_name = 'LOAN_RECEIVABLE'
and dist.line_type = 'PRIN'
and dist.distribution_type = 'BILLING'
and dist.distribution_percent > 0
and nvl(dist.loan_line_id, -1) =
nvl((select max(loan_line_id)
from lns_loan_lines
where status = 'APPROVED'
and LOAN_ID = C_LOAN_ID
and original_flag = 'N'
and trunc(adjustment_date) <= trunc(sysdate)), -1)
order by dist.distribution_id;
select count(1)
from lns_loan_lines
where loan_id = p_loan_id
and reference_type = 'RECEIVABLE'
and end_date is null;
select ra.cust_trx_type_id, nvl(psa.psa_trx_type_id,-1)
from lns_loan_lines lines
,ra_customer_trx ra
,psa_trx_types_all psa
where ra.customer_trx_id = lines.reference_id
and psa.psa_trx_type_id (+)= ra.cust_trx_type_id
and lines.reference_type = 'RECEIVABLE'
and lines.end_date is null
and lines.loan_id = p_loan_id
group by ra.cust_trx_type_id, psa.psa_trx_type_id;
select nvl(1,0)
from ra_customer_trx ra
,psa_trx_types_all psa
,lns_loan_lines lines
where ra.CUST_TRX_TYPE_ID = psa.psa_trx_type_id
and ra.customer_trx_id = lines.reference_id
and lines.end_date is null
and lines.reference_type = 'RECEIVABLE'
and lines.loan_id = p_loan_id
group by lines.loan_id;
SELECT count(1)
FROM lns_distributions
WHERE loan_id = c_loan_id
AND nvl(disb_header_id, -1) = nvl(c_disb_header_id, -1)
AND nvl(loan_amount_adj_id, -1) = nvl(c_loan_amount_adj_id, -1)
AND activity = c_activity
AND distribution_type = 'ORIGINATION';
select disb_header_id
from lns_disb_headers
where loan_id = p_loan_id;
select count(1)
from lns_distributions
where loan_id = p_loan_id
and line_type = 'SUBSIDY'
and distribution_type = 'ORIGINATION'
--and event_id is not null;
SELECT
(nvl(subsidy_rate, 0)/100)
FROM
lns_loan_headers_all
WHERE
loan_id = p_loan_id;
SELECT LOAN_AMOUNT_ADJ_ID
FROM LNS_LOAN_AMOUNT_ADJS
WHERE loan_id = c_loan_id
AND status = 'PENDING';
-- insert into the distributions table
logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Calling defaultDistributionsCatch...');
logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Calling do_insert_distributions...');
do_insert_distributions(l_distributions, p_loan_id);
|| 07-26-2004 raverma delete rows before each call to accounting
|| 12-18-2004 raverma look at lns_loan_lines
|| 12-18-2004 raverma need to get loan class code
|| 04-19-2005 raverma establish loan clearing as per bug #4313925
*=======================================================================*/
procedure defaultDistributions(p_api_version IN NUMBER
,p_init_msg_list IN VARCHAR2
,p_commit IN VARCHAR2
,p_loan_id IN NUMBER
,p_loan_class_code IN VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2)
is
/*------------------------------------------------------------------------+
| Local Variable Declarations and initializations |
+-----------------------------------------------------------------------*/
l_api_name varchar2(30);
select sum(lline.requested_amount)
,ael.code_combination_id
from ra_customer_trx_all inv
,xla_transaction_entities ent
,xla_ae_headers aeh
,xla_ae_lines ael
,lns_loan_lines lline
where ent.application_id = 222
and lline.reference_id = ent.source_id_int_1
and lline.loan_id = p_loan_id
-- and lline.status = 'APPROVED'
and (lline.end_date is null or trunc(lline.end_date) > trunc(sysdate))
and inv.customer_trx_id = ent.source_id_int_1
and ent.entity_code = 'TRANSACTIONS'
and ent.entity_id = aeh.entity_id
and ent.ledger_id = aeh.ledger_id
and aeh.ae_header_id = ael.ae_header_id
and aeh.accounting_entry_status_code = 'F'
and ael.accounting_class_code IN
(select xaa.accounting_class_code
from XLA_ACCT_CLASS_ASSGNS xaa
,XLA_ASSIGNMENT_DEFNS_B xad
,XLA_POST_ACCT_PROGS_B xpa
where xaa.program_code = 'GET_RECEIVABLE_CCID'
and xpa.program_code = xaa.program_code
and xaa.program_code = xad.program_code
and xad.assignment_code = xaa.assignment_code
and xad.enabled_flag = 'Y')
and inv.customer_trx_id in
(select reference_id
from lns_loan_lines lines
where reference_type = 'RECEIVABLE'
and end_date is null
and loan_id = p_loan_id)
group by ael.code_combination_id;
select sum(ael.entered_dr)
,ael.code_combination_id
from ar_adjustments_all adj
,xla_transaction_entities ent
,xla_ae_headers aeh
,xla_ae_lines ael
where ent.application_id = 222
and adj.adjustment_id = ent.source_id_int_1
and ent.entity_code = 'ADJUSTMENTS'
and ent.entity_id = aeh.entity_id
and ent.ledger_id = aeh.ledger_id
and aeh.ae_header_id = ael.ae_header_id
and aeh.accounting_entry_status_code = 'F'
and ael.accounting_class_code in
(select xaa.accounting_class_code
from XLA_ACCT_CLASS_ASSGNS xaa
,XLA_ASSIGNMENT_DEFNS_B xad
,XLA_POST_ACCT_PROGS_B xpa
where xaa.program_code = 'LNS_ADJUSTMENT_DEBIT' -- Bug#8231149
and xpa.program_code = xaa.program_code
and xaa.program_code = xad.program_code
and xad.assignment_code = xaa.assignment_code
and xad.enabled_flag = 'Y')
and adj.adjustment_id in
(select rec_adjustment_id
from lns_loan_lines lines
where reference_type = 'RECEIVABLE'
and end_date is null
and loan_id = p_loan_id)
group by ael.code_combination_id;
select h.loan_class_code
,t.loan_type_id
,decode(h.loan_status,'INCOMPLETE', h.requested_amount
,'PENDING', h.requested_amount
,h.funded_amount)
,h.legal_entity_id
,h.LOAN_STATUS
from lns_loan_headers_all h
,lns_loan_types t
where h.loan_id = p_loan_id
and h.loan_type_id = t.loan_type_id;
select adj.adjustment_id
,adj.adjustment_number
from ar_adjustments adj
,lns_loan_lines lines
where lines.rec_adjustment_number = adj.adjustment_number
and lines.end_date is null
and lines.reference_type = 'RECEIVABLE'
and lines.loan_id = p_loan_id;
select lines.reference_id
,lines.reference_number
from lns_loan_lines lines
where lines.end_date is null
and lines.reference_type = 'RECEIVABLE'
and lines.loan_id = p_loan_id;
select object_version_number
from lns_loan_headers
where loan_id = p_loan_id;
select lines.reference_id, trx.trx_number
from lns_loan_lines lines
,ra_customer_trx trx
where lines.reference_type = 'RECEIVABLE'
and lines.end_date is null
and lines.loan_id = p_loan_id
and lines.reference_id = trx.customer_trx_id;
select xlt.transaction_number, xlt.entity_code, err.encoded_msg
from xla_accounting_errors err
,xla_transaction_entities xlt
where xlt.application_id = 222
--and err.accounting_batch_id = nvl(p_accounting_batch_id, null)
and xlt.entity_id = err.entity_id
and xlt.entity_id in (select entity_id from xla_transaction_entities
where application_id = 222
and entity_code IN ('TRANSACTIONS', 'ADJUSTMENTS')
and ((source_id_int_1 in (select reference_id from lns_loan_lines where end_date is null and reference_type = 'RECEIVABLE' and loan_id = p_loan_id))
OR (source_id_int_1 in (select rec_adjustment_id from lns_loan_lines where end_date is null and reference_type = 'RECEIVABLE' and loan_id = p_loan_id))));
select entity_id, entity_code, source_id_int_1, transaction_number
from xla_transaction_entities
where application_id = 222
and entity_code IN ('TRANSACTIONS', 'ADJUSTMENTS')
and ((source_id_int_1 in (select reference_id from lns_loan_lines where end_date is null and reference_type = 'RECEIVABLE' and loan_id = p_loan_id)
OR (source_id_int_1 in (select rec_adjustment_id from lns_loan_lines where end_date is null and reference_type = 'RECEIVABLE' and loan_id = p_loan_id))));
SELECT count(1)
FROM lns_distributions
WHERE loan_id = p_loan_id
AND account_name IN ('PRINCIPAL_RECEIVABLE', 'INTEREST_RECEIVABLE', 'INTEREST_INCOME');
SELECT rec.CODE_COMBINATION_ID
FROM lns_loan_headers lhdr,
lns_system_options sysop,
ar_receivables_trx rec
WHERE lhdr.loan_id = c_loan_id
AND sysop.org_id = lhdr.org_id
AND sysop.receivables_trx_id = rec.receivables_trx_id;
/* delete any rows for this loan before inheritance do not delete FEE_RECEIVABLE or FEE_INCOME rows*/
/* Commented Bug#7406404 - Defaulting the laon distributions happens only at Loan Creation time and later only updation at loan level is changed.
logMessage(FND_LOG.level_statement, G_PKG_NAME, 'Deleting any existing accounting rows except FEE_RECEIVABLE or FEE_INCOME...');
delete from lns_distributions
where loan_id = p_loan_id
and account_name in ('PRINCIPAL_RECEIVABLE', 'INTEREST_RECEIVABLE', 'INTEREST_INCOME', 'LOAN_RECEIVABLE', 'LOAN_CLEARING', 'LOAN_LIABILITY', 'LOAN_PAYABLE');
LNS_LOAN_HEADER_PUB.UPDATE_LOAN(P_OBJECT_VERSION_NUMBER => l_version
,P_LOAN_HEADER_REC => l_loan_header_rec
,P_INIT_MSG_LIST => p_init_msg_list
,X_RETURN_STATUS => l_return_status
,X_MSG_COUNT => l_msg_count
,X_MSG_DATA => l_msg_data);
logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' update loan status = ' || l_return_status);
delete from lns_distributions
where loan_id = p_loan_id
and account_name in ('LOAN_RECEIVABLE', 'LOAN_CLEARING');
logMessage(FND_LOG.level_statement, G_PKG_NAME, 'Deleted '||SQL%ROWCOUNT||' rows');
insert into XLA_ACCT_PROG_DOCS_GT (entity_id) VALUES (l_entity_id);
logMessage(FND_LOG.level_statement, G_PKG_NAME, 'Inserted into XLA_ACCT_PROG_DOCS_GT');
select count(1) into l_transactions_count
from XLA_ACCT_PROG_DOCS_GT;
logMessage(FND_LOG.level_statement, G_PKG_NAME, 'Inserted transaction_entities = ' || l_transactions_count);
delete from lns_distributions
where loan_id = p_loan_id
and account_name IN ('PRINCIPAL_RECEIVABLE', 'INTEREST_RECEIVABLE', 'INTEREST_INCOME');
logMessage(FND_LOG.level_statement, G_PKG_NAME, 'Billing rows - deleted ||'||SQL%ROWCOUNT||' rows');
logMessage(FND_LOG.level_statement, G_PKG_NAME, 'Calling do_insert_distributions...');
do_insert_distributions(p_distributions_tbl => l_distributionsALL
,p_loan_id => p_loan_id);
select h.legal_entity_id
,h.loan_class_code
from lns_loan_headers h
where h.loan_id = p_loan_id;
select xlt.transaction_number, xlt.entity_code, err.encoded_msg
from xla_accounting_errors err
,xla_Transaction_entities xlt
where xlt.application_id = 206
and err.accounting_batch_id = p_accounting_batch_id
and err.entity_id = xlt.entity_id
and xlt.entity_id in (select entity_id from xla_transaction_entities
where application_id = 206
and entity_code = 'LOANS'
and source_id_int_1 = p_loan_id
and source_id_int_2 IN
(select disb.disb_header_id
from lns_disb_headers dh
,lns_distributions disb
where disb.loan_id = p_loan_id
and disb.disb_header_id = dh.disb_header_id
and disb.account_name = 'LOAN_RECEIVABLE'
and dh.status = 'FULLY_FUNDED'
and not exists
(select 'X'
from xla_events xle
,XLA_TRANSACTION_ENTITIES XLEE
,xla_ae_headers aeh
where XLE.application_id = 206
and XLE.event_id = disb.event_id
and XLE.entity_id = xlee.entity_id
and XLEE.source_id_int_1 = dh.loan_id
and XLEE.source_id_int_2 = dh.disb_header_id
and xlee.entity_id = aeh.entity_id
and xlee.ledger_id = aeh.ledger_id
and aeh.accounting_entry_status_code = 'F'
and xlee.entity_code = 'LOANS')
or source_id_int_2 = -1)
and source_id_int_3 IN
(select disb.loan_amount_adj_id
from lns_loan_amount_adjs ladj
,lns_distributions disb
where disb.loan_id = p_loan_id
and disb.loan_amount_adj_id = ladj.loan_amount_adj_id
and disb.account_name = 'LOAN_RECEIVABLE'
and ladj.status = 'APPROVED'
and not exists
(select 'X'
from xla_events xle
,XLA_TRANSACTION_ENTITIES XLEE
,xla_ae_headers aeh
where XLE.application_id = 206
and XLE.event_id = disb.event_id
and XLE.entity_id = xlee.entity_id
and XLEE.source_id_int_1 = ladj.loan_id
and XLEE.source_id_int_3 = ladj.loan_amount_adj_id
and xlee.entity_id = aeh.entity_id
and xlee.ledger_id = aeh.ledger_id
and aeh.accounting_entry_status_code = 'F'
and xlee.entity_code = 'LOANS')
or source_id_int_3 = -1
or source_id_int_3 IS NULL)
);
insert into XLA_ACCT_PROG_DOCS_GT
(entity_id)
select entity_id from xla_transaction_entities
where application_id = 206
and entity_code = 'LOANS'
and source_id_int_1 = p_loan_id
and source_id_int_2 = -1
and nvl(source_id_int_3, -1) = -1;
insert into XLA_ACCT_PROG_DOCS_GT
(entity_id)
select entity_id from xla_transaction_entities
where application_id = 206
and entity_code = 'LOANS'
and source_id_int_1 = p_loan_id
and (source_id_int_2 = -1
OR source_id_int_2 in (select disb.disb_header_id
from lns_disb_headers dh
,lns_distributions disb
where disb.loan_id = p_loan_id
and disb.disb_header_id = dh.disb_header_id
and disb.account_name = 'LOAN_RECEIVABLE'
and ((disb.activity = 'LNS_SUBMIT_DISBURSEMENT') OR (disb.activity = 'DISBURSEMENT' AND dh.status = 'FULLY_FUNDED'))
)
)
and (source_id_int_3 IS NULL -- Before introducing MD loanAdjustment, source_id_int_3 values are NULL
OR source_id_int_3 = -1
OR source_id_int_3 in (select disb.loan_amount_adj_id
from lns_loan_amount_adjs ladj
,lns_distributions disb
where disb.loan_id = p_loan_id
and disb.loan_amount_adj_id = ladj.loan_amount_adj_id
and disb.account_name = 'LOAN_RECEIVABLE'
and ladj.status = 'APPROVED')
);
select count(1) into l_transactions_count
from XLA_ACCT_PROG_DOCS_GT;
logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'inserted transaction_entities ' || l_transactions_count);
select loan_class_code
,gl_date
,OBJECT_VERSION_NUMBER
,org_id
from lns_loan_headers
where loan_id = c_loan_id;
select nvl(count(1),0)
from lns_fee_assignments
where begin_installment_number = 0
and end_installment_number = 0
and end_date_active is null
and billing_option = 'ORIGINATION'
and loan_id = C_LOAN_ID;
update lns_distributions
set event_id = x_event_id
where loan_id = P_LOAN_ID
and account_name in ('LOAN_RECEIVABLE', 'LOAN_CLEARING')
and distribution_type = 'ORIGINATION'
and activity = 'LNS_APPROVAL'
and loan_line_id IS NULL;
logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Before call to LNS_LOAN_HEADER_PUB.update_loan');
LNS_LOAN_HEADER_PUB.update_loan(p_init_msg_list => FND_API.G_FALSE
,p_loan_header_rec => l_loan_header_rec
,p_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);
SELECT
lower(iso_language),iso_territory
INTO
l_iso_language,l_iso_territory
FROM
FND_LANGUAGES
WHERE
language_code = USERENV('LANG');
select gl_date
from lns_loan_headers
where loan_id = c_loan_id;
delete from lns_distributions
where loan_id = p_loan_id;
do_insert_distributions(p_distributions_tbl => x_distribution_tbl
,p_loan_id => p_loan_id);
update lns_distributions
set event_id = l_event_id
where loan_id = P_LOAN_ID
and account_name in ('LOAN_RECEIVABLE', 'LOAN_CLEARING')
and distribution_type = 'ORIGINATION'
and activity = 'LNS_APPROVAL'
and loan_line_id IS NULL;
SELECT LOAN_ID, STATUS, ADJUSTMENT_AMOUNT
FROM LNS_LOAN_AMOUNT_ADJS
WHERE loan_amount_adj_id = c_loan_amount_adj_id;
SELECT LOAN_AMOUNT_ADJ_ID, STATUS, ADJUSTMENT_AMOUNT
FROM LNS_LOAN_AMOUNT_ADJS
WHERE loan_id = c_loan_id
AND status = 'PENDING';
IF l_adj_status in ('APPROVED', 'REJECTED', 'DELETED') THEN
logMessage(FND_LOG.level_statement, G_PKG_NAME, 'The distributions of adjustment with status '||l_adj_status||' cant be deleted');
/* delete any rows for this loan before inheritance do not delete FEE_RECEIVABLE or FEE_INCOME rows*/
logMessage(FND_LOG.level_statement, G_PKG_NAME, 'Deleting any existing adjustment accounting rows for '||l_loan_id||'-'||l_loan_amount_adj_id);
delete from lns_distributions
where loan_id = l_loan_id
and loan_amount_adj_id = l_loan_amount_adj_id
and account_name in ('LOAN_RECEIVABLE', 'LOAN_PAYABLE');
logMessage(FND_LOG.level_statement, G_PKG_NAME, 'Deleted '||SQL%ROWCOUNT||' rows succesfully');
select nvl(p.BDGT_REQ_FOR_APPR_FLAG, 'N')
,nvl(ladj.funds_reserved_flag, 'N')
,ladj.EFFECTIVE_DATE -- fix for bug 16068385
from lns_loan_headers h,
lns_loan_products p,
lns_loan_amount_adjs ladj
where p.loan_product_id = h.product_id
and ladj.loan_id = h.loan_id
and ladj.loan_amount_adj_id = c_loan_amount_adj_id;
select event_id
from xla_transaction_entities xlee
,xla_events xle
where xle.application_id = 206
and xle.entity_id = xlee.entity_id
and xlee.source_id_int_3 = c_loan_amount_adj_id
and xle.event_type_code = c_event_type
and xle.budgetary_control_flag = 'Y'
and xle.process_status_code <> 'P'
order by event_id desc;
SELECT LOAN_ID, STATUS, ADJUSTMENT_AMOUNT
FROM LNS_LOAN_AMOUNT_ADJS
WHERE loan_amount_adj_id = c_loan_amount_adj_id;
SELECT LOAN_AMOUNT_ADJ_ID, STATUS, ADJUSTMENT_AMOUNT
FROM LNS_LOAN_AMOUNT_ADJS
WHERE loan_id = c_loan_id
AND status = 'PENDING';
select object_version_number
from lns_loan_headers
where loan_id = p_loan_id;
select object_version_number
from lns_loan_amount_adjs
where loan_amount_adj_id = c_loan_amount_adj_id;
select count(1)
from xla_transaction_entities xlee
,xla_events xle
where xle.application_id = 206
and xle.entity_id = xlee.entity_id
and xlee.source_id_int_3 = c_loan_adj_id
and xle.event_type_code = c_event_type
and xle.budgetary_control_flag = 'Y'
and xle.process_status_code <> 'P';
update lns_distributions
set event_id = l_event_id
,last_update_date = sysdate
where distribution_type = 'ORIGINATION'
and loan_id = p_loan_id
and loan_amount_adj_id = l_loan_amount_adj_id
and activity = 'LOAN_AMOUNT_ADJUSTMENT';
logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Updated event_id succesfully for '||SQL%ROWCOUNT||' rows');
logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'inserting into PSA_BC_XLA_EVENTS_GT ');
INSERT INTO PSA_BC_XLA_EVENTS_GT (event_id, result_code)
values (l_event_id, 'FAIL');
LNS_LOAN_HEADER_PUB.UPDATE_LOAN(P_OBJECT_VERSION_NUMBER => l_version
,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);
logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'update loan status = ' || l_return_status);
select lines.reference_id, trx.trx_number
from lns_loan_lines lines
,ra_customer_trx trx
where lines.reference_type = 'RECEIVABLE'
and lines.end_date is null
and lines.loan_line_id = p_loan_line_id
and lines.reference_id = trx.customer_trx_id;
select entity_id, entity_code, source_id_int_1, transaction_number
from xla_transaction_entities
where application_id = 222
and entity_code IN ('TRANSACTIONS', 'ADJUSTMENTS')
and ((source_id_int_1 in (select reference_id from lns_loan_lines where end_date is null and reference_type = 'RECEIVABLE' and loan_line_id = p_loan_line_id)
OR (source_id_int_1 in (select rec_adjustment_id from lns_loan_lines where end_date is null and reference_type = 'RECEIVABLE' and loan_line_id = p_loan_line_id))));
select xlt.transaction_number, xlt.entity_code, err.encoded_msg
from xla_accounting_errors err
,xla_transaction_entities xlt
where xlt.application_id = 222
--and err.accounting_batch_id = nvl(p_accounting_batch_id, null)
and xlt.entity_id = err.entity_id
and xlt.entity_id in (select entity_id from xla_transaction_entities
where application_id = 222
and entity_code IN ('TRANSACTIONS', 'ADJUSTMENTS')
and ((source_id_int_1 in (select reference_id from lns_loan_lines where end_date is null and reference_type = 'RECEIVABLE' and loan_line_id = p_loan_line_id))
OR (source_id_int_1 in (select rec_adjustment_id from lns_loan_lines where end_date is null and reference_type = 'RECEIVABLE' and loan_line_id = p_loan_line_id))));
select sum(ael.entered_dr)
,ael.code_combination_id
from ar_adjustments_all adj
,xla_transaction_entities ent
,xla_ae_headers aeh
,xla_ae_lines ael
where ent.application_id = 222
and adj.adjustment_id = ent.source_id_int_1
and ent.entity_code = 'ADJUSTMENTS'
and ent.entity_id = aeh.entity_id
and ent.ledger_id = aeh.ledger_id
and aeh.ae_header_id = ael.ae_header_id
and aeh.accounting_entry_status_code = 'F'
and ael.accounting_class_code in
(select xaa.accounting_class_code
from XLA_ACCT_CLASS_ASSGNS xaa
,XLA_ASSIGNMENT_DEFNS_B xad
,XLA_POST_ACCT_PROGS_B xpa
where xaa.program_code = 'LNS_ADJUSTMENT_DEBIT' -- Bug#8231149
and xpa.program_code = xaa.program_code
and xaa.program_code = xad.program_code
and xad.assignment_code = xaa.assignment_code
and xad.enabled_flag = 'Y')
and adj.adjustment_id in
(select rec_adjustment_id
from lns_loan_lines
where reference_type = 'RECEIVABLE'
and end_date is null
and loan_line_id = p_loan_line_id)
group by ael.code_combination_id;
select sum(ael.entered_dr)
,ael.code_combination_id
from ra_customer_trx_all inv
,xla_transaction_entities ent
,xla_ae_headers aeh
,xla_ae_lines ael
where ent.application_id = 222
and inv.customer_trx_id = ent.source_id_int_1
and ent.entity_code = 'TRANSACTIONS'
and ent.entity_id = aeh.entity_id
and ent.ledger_id = aeh.ledger_id
and aeh.ae_header_id = ael.ae_header_id
and aeh.accounting_entry_status_code = 'F'
and ael.accounting_class_code IN
(select xaa.accounting_class_code
from XLA_ACCT_CLASS_ASSGNS xaa
,XLA_ASSIGNMENT_DEFNS_B xad
,XLA_POST_ACCT_PROGS_B xpa
where xaa.program_code = 'GET_RECEIVABLE_CCID'
and xpa.program_code = xaa.program_code
and xaa.program_code = xad.program_code
and xad.assignment_code = xaa.assignment_code
and xad.enabled_flag = 'Y')
and inv.customer_trx_id in
(select reference_id
from lns_loan_lines
where reference_type = 'RECEIVABLE'
and end_date is null
and loan_line_id = p_loan_line_id)
group by ael.code_combination_id;
select nvl(sum(dist.distribution_amount), 0)
from lns_distributions dist,
lns_loan_lines lines
where dist.distribution_type = 'ORIGINATION'
and dist.line_type = 'ORIG'
and dist.account_name = 'LOAN_RECEIVABLE'
and dist.account_type = 'DR'
and dist.loan_id = p_loan_id
and dist.code_combination_id = p_code_combination_id
and dist.LOAN_LINE_ID = lines.LOAN_LINE_ID(+)
and lines.loan_id(+) = dist.loan_id
and trunc(lines.ADJUSTMENT_DATE(+)) <= trunc(p_adj_date)
and lines.status(+) = 'APPROVED';
select nvl(sum(dist.amount), 0)
from lns_amortization_lines lines,
lns_amortization_scheds am,
RA_CUST_TRX_LINE_GL_DIST_ALL dist
where am.loan_id = p_loan_id
and (am.REVERSED_FLAG is null or am.REVERSED_FLAG = 'N')
and am.REAMORTIZATION_AMOUNT is null
and am.loan_id = lines.loan_id
and am.amortization_schedule_id = lines.amortization_schedule_id
and dist.CUSTOMER_TRX_ID = lines.cust_trx_id
and dist.CUSTOMER_TRX_LINE_ID = lines.cust_trx_line_id
and dist.code_combination_id = p_code_combination_id
and trunc(am.due_date) <= trunc(p_adj_date);
select ADJUSTMENT_DATE, adj.gl_date
from lns_loan_lines lines, ar_adjustments_all adj
where lines.LOAN_LINE_ID = p_loan_line_id
and lines.REC_ADJUSTMENT_ID = adj.ADJUSTMENT_ID;
select dist.distribution_id
,dist.loan_id
,dist.line_type
,dist.account_name
,dist.account_type
,dist.code_combination_id
,dist.distribution_percent
,dist.distribution_amount
,dist.distribution_type
from lns_distributions dist
where dist.loan_id = C_LOAN_ID
and dist.account_type = 'CR'
and dist.account_name = 'LOAN_RECEIVABLE'
and dist.line_type = 'PRIN'
and dist.distribution_type = 'BILLING'
and dist.distribution_percent > 0
and nvl(dist.loan_line_id, -1) =
nvl((select max(loan_line_id)
from lns_loan_lines
where status = 'APPROVED'
and LOAN_ID = C_LOAN_ID
-- Added below one line condition b'coz we want the last loan_line_id distributions
-- but here this code executes after the current loan_line_id is approved so it returns
-- the current loan_line_id , which is incorrect.
and loan_line_id NOT IN (C_LOAN_LINE_ID)
and original_flag = 'N'
and trunc(adjustment_date) <= trunc(C_ADJ_DATE)), -1)
order by dist.distribution_id;
insert into XLA_ACCT_PROG_DOCS_GT (entity_id) VALUES (l_entity_id);
logMessage(FND_LOG.level_statement, G_PKG_NAME, 'Inserted into XLA_ACCT_PROG_DOCS_GT');
select count(1) into l_transactions_count
from XLA_ACCT_PROG_DOCS_GT;
logMessage(FND_LOG.level_statement, G_PKG_NAME, 'Inserted transaction_entities = ' || l_transactions_count);
logMessage(FND_LOG.level_statement, G_PKG_NAME, 'Calling do_insert_distributions...');
do_insert_distributions(p_distributions_tbl => l_distributionsALL
,p_loan_id => p_loan_id);
update lns_distributions
set event_id = l_event_id
where loan_id = P_LOAN_ID
and loan_line_id = p_loan_line_id
and account_name in ('LOAN_RECEIVABLE', 'LOAN_CLEARING')
and distribution_type = 'ORIGINATION'
and activity = 'ERS_ADD_REC';
select d.line_type
,d.account_name
,d.code_combination_id
,d.account_type
,d.distribution_percent
,d.distribution_type
from lns_distributions d
where d.loan_id = c_loan_id
and d.distribution_type = 'ORIGINATION'
and d.distribution_percent > 0
and d.disb_header_id IS NULL
and d.loan_amount_adj_id IS NULL
and d.loan_line_id IS NULL
order by d.code_combination_id;
select h.loan_class_code, h.requested_amount, h.funded_amount, (nvl(subsidy_rate, 0)/100)
from lns_loan_headers_all h
where h.loan_id = c_loan_id;
select count(1)
from lns_distributions
where loan_id = c_loan_id
and account_name = 'LOAN_RECEIVABLE'
and distribution_type = 'ORIGINATION'
and account_type = 'DR'
and line_type <> 'SUBSIDY';
select count(1)
from lns_distributions
where loan_id = c_loan_id
and account_name in ('LOAN_PAYABLE', 'LOAN_CLEARING')
and distribution_type = 'ORIGINATION'
and account_type = 'CR'
and line_type <> 'SUBSIDY';
-- 1b. Calculate and update the amount
open c_num_receivables(p_loan_id);
logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Calling do_insert_distributions...');
do_insert_distributions(l_distributions, p_loan_id);
select event_id
from xla_transaction_entities xlee
,xla_events xle
where xle.application_id = 206
and xle.entity_id = xlee.entity_id
and xlee.source_id_int_1 = p_loan_id
and xle.budgetary_control_flag = 'Y'
and xle.event_type_code = 'FUTURE_DISBURSEMENT_CANCELLED'
and xle.process_status_code <> 'P';
select nvl(p.BDGT_REQ_FOR_APPR_FLAG, 'N')
,nvl(h.funds_reserved_flag, 'N')
,nvl(h.gl_date, sysdate)
from lns_loan_headers h,
lns_loan_products p
where p.loan_product_id = h.product_id
and h.loan_id = p_loan_id;
select object_version_number
from lns_loan_headers
where loan_id = p_loan_id;
select disb_header_id
from lns_disb_headers
where loan_id = p_loan_id
and disbursement_number = 1;
select PAYMENT_REQUEST_DATE
from lns_disb_headers
where disb_header_id = p_disb_header_id;
update lns_distributions
set event_id = l_event_id
,last_update_date = sysdate
,object_Version_number = object_version_number + 1
where distribution_type = 'ORIGINATION'
and loan_id = p_loan_id
and disb_header_id = p_disb_header_id
and activity = p_activity;
logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Updated event_id '||l_event_id||' succesfully for '||SQL%ROWCOUNT||' rows');
logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'inserting into PSA_BC_XLA_EVENTS_GT - event => ' || l_event_id);
insert into PSA_BC_XLA_EVENTS_GT (event_id, result_code)
values (l_event_id, 'FAIL');
select party_name from hz_parties party where party_id = P_BORROWER_ID;
select loan_number from lns_loan_headers where loan_id = P_LOAN_ID;
select name
from hr_all_organization_units_tl
where ORGANIZATION_ID = C_ORG_ID and
language(+) = userenv('LANG');
select name
from gl_ledgers
where ledger_id = C_LEDGER_ID;
SELECT
party.party_name
,loan.loan_number
,loan.LOAN_CURRENCY
,to_char(C_INT_ACCRUED, FND_CURRENCY.SAFE_GET_FORMAT_MASK(loan.LOAN_CURRENCY,50))
,loan.last_payment_number
,decode(loan.current_phase, 'OPEN', to_char(loan.open_loan_start_date,'YYYY-MM-DD'), to_char(loan.loan_start_date,'YYYY-MM-DD'))
FROM
lns_loan_headers loan,
hz_parties party
WHERE party.party_id = loan.PRIMARY_BORROWER_ID
AND loan.loan_id = C_LOAN_ID;
SELECT
max(ldist.accrual_date)
FROM
lns_distributions ldist
WHERE ldist.loan_id = c_loan_id
AND ldist.distribution_type = 'ACCRUAL'
AND ldist. activity = 'INTEREST_ACCRUAL';
SELECT
to_char(max(amort.due_date),'YYYY-MM-DD')
FROM
LNS_AMORTIZATION_SCHEDS amort
WHERE amort.loan_id = c_loan_id
AND amort.reversed_flag = 'N';
SELECT
loan.loan_number
FROM
lns_loan_headers loan
WHERE
loan.loan_id = C_LOAN_ID;
SELECT count(1)
FROM lns_distributions ldist
WHERE ldist.loan_id = c_loan_id
AND ldist.accrual_date >= c_accrual_date
AND ldist.distribution_type = 'ACCRUAL'
AND ldist.accrual_completed_flag = 'Y';
select ldist.line_type
,ldist.account_name
,ldist.code_combination_id
,ldist.account_type
,ldist.distribution_percent
,ldist.distribution_type
from lns_distributions ldist
where ldist.loan_id = c_loan_id
and ldist.line_type = 'INT'
and ldist.distribution_type = 'BILLING'
and ldist.distribution_percent >= 0
order by ldist.account_type, ldist.code_combination_id;
select count(1)
from lns_distributions ldist
where ldist.loan_id = c_loan_id
and ldist.line_type = 'INT'
and ldist.distribution_type = 'BILLING'
and ldist.distribution_percent >= 0
and ldist.account_type = 'DR'
and ldist.account_name = 'INTEREST_RECEIVABLE';
select count(1)
from lns_distributions ldist
where ldist.loan_id = c_loan_id
and ldist.line_type = 'INT'
and ldist.distribution_type = 'BILLING'
and ldist.distribution_percent >= 0
and ldist.account_type = 'CR'
and ldist.account_name = 'INTEREST_INCOME';
logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Calling do_insert_distributions...');
do_insert_distributions(l_distributions, p_loan_id);
SELECT trunc(decode(c_last_inst_no,
0,
decode(lnh.current_phase, 'TERM', lnh.loan_start_date, 'OPEN', lnh.open_loan_start_date),
-1,
decode(lnh.current_phase, 'TERM', lnh.loan_start_date, 'OPEN', lnh.open_loan_start_date),
(select max(due_date)
from lns_amortization_scheds
where reversed_flag = 'N'
and loan_id = lnh.loan_id
and phase = lnh.current_phase)))
FROM lns_loan_headers lnh
WHERE lnh.loan_id = c_loan_id;
SELECT nvl(h.current_phase, 'TERM')
FROM lns_loan_headers h
WHERE h.loan_id = c_loan_id;
SELECT
loan.loan_number, nvl(loan.disable_int_accrual_flag, 'N'), ou.name, gl.name
FROM
lns_loan_headers_all loan, lns_system_options_all lsysopt, hr_operating_units ou, gl_ledgers gl
WHERE loan.loan_id = C_LOAN_ID
AND loan.org_id = lsysopt.org_id
AND lsysopt.set_of_books_id = gl.ledger_id
AND loan.org_id = ou.organization_id;
SELECT
max(ldist.accrual_date)
FROM
lns_distributions ldist
WHERE ldist.loan_id = c_loan_id
AND ldist.distribution_type = 'ACCRUAL'
AND ldist. activity = 'INTEREST_ACCRUAL'
AND ldist.accrual_completed_flag = 'Y';
SELECT
account_name
,code_combination_id
,account_type
,distribution_percent
,distribution_amount
,distribution_type
FROM
lns_distributions ldist
WHERE ldist.loan_id = c_loan_id
AND ldist.accrual_date = c_accrual_date
AND ldist.distribution_type = 'ACCRUAL'
AND ldist. activity = 'INTEREST_ACCRUAL'
ORDER BY ldist.account_type;
SELECT xlt.transaction_number, xlt.entity_code, err.encoded_msg
FROM xla_accounting_errors err
,xla_transaction_entities xlt
WHERE xlt.application_id = 206
AND xlt.entity_id = err.entity_id
AND err.event_id = C_EVENT_ID;
SELECT sb.chart_of_accounts_id
FROM lns_loan_headers lhdr,
lns_system_options so,
gl_ledgers sb,
fnd_currencies fndc
WHERE lhdr.loan_id = c_loan_id
AND lhdr.org_id = so.org_id
AND sb.ledger_id = so.set_of_books_id
AND sb.currency_code = fndc.currency_code;
SELECT
glps.closing_status
FROM
GL_PERIOD_STATUSES glps, LNS_SYSTEM_OPTIONS sysopt, LNS_LOAN_HEADERS lhdr
WHERE lhdr.loan_id = C_LOAN_ID
AND lhdr.org_id = sysopt.org_id
AND sysopt.set_of_books_id = glps.ledger_id
AND glps.application_id = 101
AND glps.period_name = C_PERIOD_NAME;
SELECT distinct ldist.event_id, evnt.event_status_code
FROM lns_distributions ldist, xla_events evnt
WHERE ldist.event_id = evnt.event_id
AND ldist.loan_id = C_LOAN_ID
AND ldist.activity = 'INTEREST_ACCRUAL'
AND ldist.distribution_type = 'ACCRUAL'
AND ldist.accrual_completed_flag <> 'Y';
logMessage(FND_LOG.level_statement, G_PKG_NAME, 'Delete existed accrual rows on this accrual date if they are not Final accrued ');
LNS_XLA_EVENTS.delete_event(p_loan_id => P_loan_id
,p_disb_header_id => -1
,p_loan_amount_adj_id => -1
,p_loan_line_id => -1
,p_event_id => l_exist_event_id
,p_init_msg_list => 'F'
,p_commit => 'F'
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
-- So delete all the above distributions above that date
DELETE FROM lns_distributions ldist
WHERE ldist.loan_id = P_LOAN_ID
AND ldist.activity = 'INTEREST_ACCRUAL'
AND ldist.distribution_type = 'ACCRUAL'
AND ldist.accrual_completed_flag <> 'Y'
AND NOT EXISTS
(SELECT 'X' FROM xla_events evnt
WHERE evnt.event_id = ldist.event_id
AND evnt.event_status_code in ('P', 'R'));
logMessage(FND_LOG.level_statement, G_PKG_NAME, 'Deleted '||SQL%ROWCOUNT||' accrual rows succesfully');
update lns_distributions
set event_id = l_event_id
,last_update_date = sysdate
where distribution_type = 'ACCRUAL'
and loan_id = p_loan_id
and accrual_date = p_accrual_date
--and accrual_period = NVL(p_period_name, accrual_period)
and activity = 'INTEREST_ACCRUAL';
logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Updated event_id succesfully for '||SQL%ROWCOUNT||' rows');
SELECT
head.LOAN_ID
FROM
LNS_LOAN_HEADERS head, LNS_SYSTEM_OPTIONS sysopt
WHERE
head.org_id = sysopt.org_id
AND sysopt.set_of_books_id = nvl(C_LEDGER_ID, sysopt.set_of_books_id)
AND c_accrual_date >= NVL((select max(due_date) from LNS_AMORTIZATION_SCHEDS aschd
where aschd.loan_id = head.loan_id and aschd.reversed_flag = 'N')
,decode(head.current_phase, 'OPEN',head.open_loan_start_date, head.loan_start_date))
AND head.loan_status in ('ACTIVE', 'DEFAULT', 'DELINQUENT')
AND head.loan_id = NVL(C_LOAN_ID, head.loan_id)
AND head.PRIMARY_BORROWER_ID = nvl(C_BORROWER_ID, head.PRIMARY_BORROWER_ID)
AND head.org_id = nvl(C_ORG_ID, head.org_id)
AND NVL(head.current_phase, 'TERM') = 'TERM'
ORDER BY head.LOAN_ID;
SELECT
glps.period_name, glps.closing_status
FROM
GL_PERIOD_STATUSES glps
WHERE glps.start_date <= C_ACCRUAL_DATE
AND glps.end_date >= C_ACCRUAL_DATE
AND glps.ledger_id = C_LEDGER_ID
AND glps.application_id = 101;
SELECT
to_char(glpstat.end_date,'YYYY-MM-DD')
FROM
gl_period_statuses glpstat
WHERE
glpstat.application_id = 101
AND glpstat.set_of_books_id = c_ledger_id
AND glpstat.period_name = c_period_name
AND glpstat.adjustment_period_flag = 'N';
/* Accrue all the selected loans */
FOR l_Count1 IN 1..l_loans_to_accrue_tbl.COUNT LOOP
LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Processing loan #' || l_Count1||' - '||l_loans_to_accrue_tbl(l_Count1));
select FND_PROFILE.VALUE('ICX_DATE_FORMAT_MASK') into l_date_format from dual;
select TO_DATE(ACCRUAL_DATE, l_date_format) into l_accrual_date from dual;
select d.header_amount
from lns_disb_headers d
where d.loan_id = p_loan_id
and d.disb_header_id = p_disb_header_id;
select ladj.adjustment_amount
from LNS_LOAN_AMOUNT_ADJS ladj
where ladj.loan_id = p_loan_id
and ladj.status = 'PENDING'
and ladj.loan_amount_adj_id = c_loan_amount_adj_id;
select count(1)
from lns_distributions
where loan_id = c_loan_id
and activity = c_activity
and account_type = 'DR';
select count(1)
from lns_distributions
where loan_id = c_loan_id
and activity = c_activity
and account_type = 'CR';
select d.distribution_id
,d.account_type
,d.line_type
,d.account_name
,d.code_combination_id
,d.distribution_percent
,d.distribution_amount
,d.distribution_type
from lns_distributions d
where d.loan_id = c_loan_id
and d.activity = 'LNS_APPROVAL'
order by d.account_type, d.code_combination_id;
|| p_project_id = Project ID corresponding to the Project selected
|| p_task_id = Task ID corresponding to the Task selected
|| p_award_id = Award ID corresponding to the Award selected
|| p_expenditure_type = expenditure Type
|| p_expenditure_date = expenditure date
||
|| Return value: CCID corresponding to the Project, Task, Award, Expenditure Type combination
|| Concatenated Segment value
|| Source Tables: lns_distributions
||
|| Target Tables:
||
|| KNOWN ISSUES
||
|| NOTES
||
||
|| MODIFICATION HISTORY
|| Date Author Description of Changes
|| 02-12-2011 gparuchu Created
||
*=======================================================================*/
procedure GENERATE_CCID_FOR_PROJECTS(p_loan_id in number
,p_distribution_id in number
,p_project_id in number
,p_task_id in number
,p_award_id in number
,p_expenditure_type in varchar2
,p_expenditure_date date
,x_ccid OUT NOCOPY NUMBER
,x_conc_segment_values OUT NOCOPY VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2)
IS
/*------------------------------------------------------------------------+
| Local Variable Declarations and initializations |
+-----------------------------------------------------------------------*/
l_api_name varchar2(50);
select chart_of_accounts_id
from gl_sets_of_books gl_books
where gl_books.set_of_books_id = (select set_of_books_id from lns_system_options);
select org_id
from lns_loan_headers lh
where lh.loan_id = p_loan_id;