[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, 'Y')
,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
where distribution_type = 'ORIGINATION'
and loan_id = p_loan_id
and event_id is not null
and disb_header_id is null;
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 = p_loan_id
and xle.budgetary_control_flag = 'Y'
and xll.event_id =xle.event_id;
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, 'Y')
,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 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';
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;
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)
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);
end do_insert_distributions;
select h.loan_class_code
,t.loan_type_id
,h.funded_amount
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 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 ' ||
'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" "IN_FUNDING"
|| ,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
where disb_header_id = p_acc_event_tbl(k).disb_header_id
and loan_id = p_acc_event_tbl(k).loan_id;
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
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
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
from lns_distributions
where distribution_id = x_distribution_id
and distribution_percent > 0;
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
from lns_loan_headers_all
where loan_id = p_loan_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 = p_loan_id
and distribution_type = 'ORIGINATION'
and disb_header_id = p_disb_header_id;
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
(subsidy_rate/100)
FROM
lns_loan_headers_all
WHERE
loan_id = p_loan_id;
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(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 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
,h.funded_amount
,h.legal_entity_id
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))));
/* 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 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')
and event_id is null;
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);
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);
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));
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;
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 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');
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';
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');
logMessage(FND_LOG.level_statement, G_PKG_NAME, 'Calling do_insert_distributions...');
lns_distributions_pub.do_insert_distributions(
p_distributions_tbl => l_distributionsCatch
,p_loan_id => p_loan_id);