[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select DAYS_TOGL_AFTER_DUE_DATE
into g_day_togl_after_dd
FROM LNS_SYSTEM_OPTIONS
WHERE ORG_ID = g_org_id;
select party_name from hz_parties party where party_id = P_BORROWER_ID;
select LOAN_PRODUCT_NAME from lns_loan_products_vl where loan_product_id = P_PRODUCT_ID;
select loan_number from lns_loan_headers where loan_id = P_LOAN_ID;
select meaning from fnd_lookups where lookup_type = 'YES_NO' and lookup_code = P_UNAPPLY_FLAG;
select receipt_number from ar_cash_receipts where cash_receipt_id = P_RECEIPT_ID;
select meaning from FND_LOOKUP_VALUES where lookup_type = 'LNS_RECEIPT_MATCH_CRITERIA' and lookup_code = P_RECEIPT_MATCH_CRITERIA;
select name
from hr_all_organization_units_tl
where ORGANIZATION_ID = P_ORG_ID and
language(+) = userenv('LANG');
select
ra.RECEIVABLE_APPLICATION_ID,
ps.CASH_RECEIPT_ID,
ps.trx_number,
ps.trx_date,
abs(ps.amount_due_remaining),
ps.invoice_currency_code,
party.party_name,
ra.AMOUNT_APPLIED,
nvl(ra.amount_applied_from, ra.AMOUNT_APPLIED)
from ar_receivable_applications ra,
ar_payment_schedules ps,
hz_cust_accounts cust,
hz_parties party
where
ra.APPLIED_PAYMENT_SCHEDULE_ID = P_PAYMENT_SCHEDULE_ID and
ra.application_type = 'CASH' and
ra.display = 'Y' and
ra.cash_receipt_id = ps.cash_receipt_id and
ps.class = 'PMT' and
ps.customer_id = cust.cust_account_id and
cust.status = 'A' and
cust.party_id = party.party_id
order by ra.APPLY_DATE desc;
l_query := 'select
ps.cash_receipt_id,
ps.trx_number,
ps.trx_date,
abs(ps.amount_due_remaining),
ps.invoice_currency_code,
party.party_name
from
lns_participants par,
lns_loan_headers loan,
hz_cust_accounts cust,
ar_payment_schedules ps,
lns_system_options sys,
gl_sets_of_books books,
hz_parties party,
AR_SYSTEM_PARAMETERS arsys,
ar_cash_receipts cr
where
loan.loan_id = :1 and
loan.loan_id = par.loan_id and
(par.loan_participant_type = ''PRIMARY_BORROWER'' or
par.loan_participant_type = decode(arsys.pay_unrelated_invoices_flag, ''N'', ''PRIMARY_BORROWER'', ''Y'', ''COBORROWER'') or
par.loan_participant_type = decode(arsys.pay_unrelated_invoices_flag, ''N'', ''PRIMARY_BORROWER'', ''Y'', ''GUARANTOR'')) and
cust.party_id = par.hz_party_id and
cust.status = ''A'' and
ps.customer_id = cust.cust_account_id and
ps.class = ''PMT'' and
ps.status = ''OP'' and
ps.amount_due_remaining <> 0 and
ps.org_id = loan.org_id and
trunc(ps.trx_date) >= trunc(nvl(:2, ps.trx_date)) and
trunc(ps.trx_date) <= trunc(nvl(:3, ps.trx_date)) and
ps.cash_receipt_id = nvl(:4, ps.cash_receipt_id) and
(ps.invoice_currency_code = loan.loan_currency or
ps.invoice_currency_code = books.currency_code) and
sys.set_of_books_id = books.set_of_books_id and
par.hz_party_id = party.party_id and
ps.cash_receipt_id = cr.cash_receipt_id';
select PAYMENT_APPLICATION_ORDER,
nvl(PMT_APPL_ORDER_SCOPE, 'ACROSS_INSTALLMENTS')
from lns_terms
where loan_id = P_LOAN_ID;
select amortization_schedule_id
from lns_amortization_scheds
where loan_id = P_LOAN_ID and
reversed_flag = 'N'
order by phase, payment_number, amortization_schedule_id;
select
decode(P_INVOICE_TYPE, 'INT', am.INTEREST_TRX_ID, 'PRIN', am.principal_trx_id, 'FEE', am.FEE_TRX_ID),
ps.PAYMENT_SCHEDULE_ID,
ps.trx_number,
loan.primary_borrower_id,
party.party_name,
loan.product_id,
product.loan_product_name,
loan.loan_id,
loan.loan_number,
am.amortization_schedule_id,
am.payment_number,
am.due_date,
am.creation_date,
am.phase,
P_INVOICE_TYPE,
look.meaning,
ps.amount_due_remaining,
ps.invoice_currency_code,
loan.EXCHANGE_RATE
from
lns_amortization_scheds am,
lns_loan_headers loan,
ar_payment_schedules ps,
hz_parties party,
lns_loan_products_vl product,
lns_lookups look
where
am.loan_id = P_LOAN_ID and
loan.loan_id = am.loan_id and
am.reversed_flag = 'N' and
ps.customer_trx_id = decode(P_INVOICE_TYPE, 'INT', am.INTEREST_TRX_ID, 'PRIN', am.principal_trx_id, 'FEE', am.FEE_TRX_ID) and
ps.amount_due_remaining > decode(P_FOR_ACTION, 'APPLY', 0, 'UNAPPLY', 0) and
ps.status = decode(P_FOR_ACTION, 'APPLY', 'OP', 'UNAPPLY', 'OP') and
(am.INTEREST_TRX_ID is not null or am.principal_trx_id is not null or am.FEE_TRX_ID is not null) and
party.party_id = loan.primary_borrower_id and
product.loan_product_id = loan.product_id and
look.lookup_type = 'PAYMENT_APPLICATION_TYPE' and
look.lookup_code = P_INVOICE_TYPE
order by am.phase, am.payment_number, am.amortization_schedule_id;
select
decode(P_INVOICE_TYPE, 'INT', am.INTEREST_TRX_ID, 'PRIN', am.principal_trx_id, 'FEE', am.FEE_TRX_ID),
ps.PAYMENT_SCHEDULE_ID,
ps.trx_number,
loan.primary_borrower_id,
party.party_name,
loan.product_id,
product.loan_product_name,
loan.loan_id,
loan.loan_number,
am.amortization_schedule_id,
am.payment_number,
am.due_date,
am.creation_date,
am.phase,
P_INVOICE_TYPE,
look.meaning,
ps.amount_due_remaining,
ps.invoice_currency_code,
loan.EXCHANGE_RATE
from
lns_amortization_scheds am,
lns_loan_headers loan,
ar_payment_schedules ps,
hz_parties party,
lns_loan_products_vl product,
lns_lookups look
where
am.loan_id = P_LOAN_ID and
am.amortization_schedule_id = P_AMORTIZATION_SCHED_ID and
loan.loan_id = am.loan_id and
am.reversed_flag = 'N' and
ps.customer_trx_id = decode(P_INVOICE_TYPE, 'INT', am.INTEREST_TRX_ID, 'PRIN', am.principal_trx_id, 'FEE', am.FEE_TRX_ID) and
ps.amount_due_remaining > decode(P_FOR_ACTION, 'APPLY', 0, 'UNAPPLY', 0) and
ps.status = decode(P_FOR_ACTION, 'APPLY', 'OP', 'UNAPPLY', 'OP') and
(am.INTEREST_TRX_ID is not null or am.principal_trx_id is not null or am.FEE_TRX_ID is not null) and
party.party_id = loan.primary_borrower_id and
product.loan_product_id = loan.product_id and
look.lookup_type = 'PAYMENT_APPLICATION_TYPE' and
look.lookup_code = P_INVOICE_TYPE;
select loan.loan_id,
loan.loan_number,
loan.primary_borrower_id,
party.party_name,
loan.product_id,
product.loan_product_name,
loan.loan_currency
from lns_loan_headers loan,
hz_parties party,
lns_loan_products_vl product
where
loan.primary_borrower_id = nvl(P_BORROWER_ID, loan.primary_borrower_id) and
loan.product_id = nvl(P_PRODUCT_ID, loan.product_id) and
loan.loan_id = nvl(P_LOAN_ID, loan.loan_id) and
party.party_id = loan.primary_borrower_id and
product.loan_product_id = loan.product_id and
((select count(1)
from
lns_amortization_scheds am,
ar_payment_schedules ps
where
am.loan_id = loan.loan_id and
(am.reversed_flag is null or am.reversed_flag = 'N') and
ps.customer_trx_id in (am.principal_trx_id, am.interest_trx_id, am.fee_trx_id) and
ps.amount_due_remaining > decode(P_UNAPPLY_FLAG, 'Y', -1, 'N', 0) and
ps.status = decode(P_UNAPPLY_FLAG, 'Y', ps.status, 'N', 'OP')) > 0)
order by trunc(nvl(loan.open_loan_start_date, loan.loan_start_date)), loan.loan_id;
l_LOAN_INVOICES_TBL.delete;
l_LOAN_INVOICES_TBL.delete;
l_RECEIPTS_TBL.delete;