[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select
party.party_name BORROWER_NAME,
party.JGZZ_FISCAL_CODE,
ass.CLASS_CODE,
party.SIC_CODE,
party.YEAR_ESTABLISHED,
null PRIMARY_CONTACT_NAME,
party.PRIMARY_PHONE_COUNTRY_CODE,
party.PRIMARY_PHONE_AREA_CODE,
party.PRIMARY_PHONE_NUMBER,
party.PRIMARY_PHONE_EXTENSION,
party.CURR_FY_POTENTIAL_REVENUE
from hz_parties party,
HZ_CODE_ASSIGNMENTS ass
where party.party_id = P_PARTY_ID and
ass.OWNER_TABLE_NAME(+) = 'HZ_PARTIES' and
ass.OWNER_TABLE_ID(+) = party.party_id and
ass.CLASS_CATEGORY(+) = 'CUSTOMER_CATEGORY'and
ass.PRIMARY_FLAG(+) = 'Y' and
ass.START_DATE_ACTIVE(+) <= sysdate and
nvl(ass.END_DATE_ACTIVE(+), sysdate) >= sysdate;
select
loc.COUNTRY,
loc.address1,
loc.address2,
loc.address3,
loc.address4,
loc.city,
loc.postal_code,
loc.state
from hz_party_sites site,
hz_locations loc,
HZ_PARTY_SITE_USES use
where site.party_id = P_PARTY_ID and
site.PARTY_SITE_ID = use.PARTY_SITE_ID(+) and
use.SITE_USE_TYPE(+) = 'BILL_TO' and
use.PRIMARY_PER_TYPE(+) = 'Y' and
use.status(+) = 'A' and
site.location_id = loc.location_id
order by use.PRIMARY_PER_TYPE;
select min(account_established_date)
from hz_cust_accounts_all
where
account_established_date is not null and
party_id = P_PARTY_ID;
select
nvl(count(head.loan_id), 0),
nvl(sum(decode(head.LOAN_CURRENCY, P_CURRENCY, head.REQUESTED_AMOUNT, (head.REQUESTED_AMOUNT*head.EXCHANGE_RATE))), 0),
nvl(sum(decode(head.LOAN_CURRENCY, P_CURRENCY, head.FUNDED_AMOUNT, (head.FUNDED_AMOUNT*head.EXCHANGE_RATE))), 0)
from
lns_participants par,
lns_loan_headers_all head
where
par.hz_party_id = P_PARTY_ID and
par.loan_id = head.loan_id and
par.loan_participant_type in ('PRIMARY_BORROWER', 'COBORROWER') and
head.loan_status in ('ACTIVE', 'APPROVED', 'DEFAULT', 'DELINQUENT', 'IN_FUNDING', 'PAIDOFF', 'PENDING_CANCELLATION') and
head.org_id = P_ORG_ID;
select books.currency_code
from lns_system_options_all sys,
gl_sets_of_books books
where sys.org_id = P_ORG_ID and
sys.set_of_books_id = books.set_of_books_id;
select
nvl(sum(rec.ACCTD_AMOUNT_APPLIED_TO), 0)
from
lns_loan_headers_ALL loan,
LNS_AMORTIZATION_SCHEDS am,
ar_receivable_applications_ALL rec,
lns_participants par
where
par.hz_party_id = P_PARTY_ID and
par.loan_participant_type in ('PRIMARY_BORROWER', 'COBORROWER') and
par.loan_id = loan.loan_id and
loan.loan_status in ('ACTIVE', 'APPROVED', 'DEFAULT', 'DELINQUENT', 'IN_FUNDING', 'PAIDOFF', 'PENDING_CANCELLATION') and
loan.loan_id = am.loan_id and
loan.org_id = P_ORG_ID and
rec.org_id = loan.org_id and
rec.APPLIED_CUSTOMER_TRX_ID =
decode(P_LINE_TYPE, 'PRIN', am.principal_trx_id, 'INT', am.interest_trx_id, 'FEE', am.fee_trx_id) and
rec.application_type = 'CASH' and
trunc(rec.APPLY_DATE) >= decode(P_TIME_FLAG, 'YTD', trunc(sysdate, 'YYYY'), trunc(rec.APPLY_DATE)) and
trunc(rec.APPLY_DATE) <= decode(P_TIME_FLAG, 'YTD', trunc(add_months(sysdate, 12), 'YYYY')-1, trunc(rec.APPLY_DATE));
select
CREDIT_CLASSIFICATION,
REVIEW_CYCLE,
LAST_CREDIT_REVIEW_DATE,
CREDIT_RATING,
CREDIT_HOLD,
CREDIT_CHECKING,
TOLERANCE
from HZ_CUSTOMER_PROFILES
where party_id = P_PARTY_ID and
cust_account_id = -1 and
site_use_id is null;
select org.ORGANIZATION_ID,
org.name,
null,
null
from lns_system_options_all sys,
hr_operating_units org
where sys.ORG_ID = org.ORGANIZATION_ID;
delete from LNS_BORROWER_SUMMARIES
where BORROWER_PARTY_ID = P_BORROWER_PARTY_ID;
insert into LNS_BORROWER_SUMMARIES(
BORROWER_PARTY_ID,
BORROWER_NAME,
tax_payer_code,
CUSTOMER_CLASSIFICATION,
INDUSTRIAL_CLASSIFICATION,
YEAR_ESTABLISHED,
COUNTRY,
ADDRESS1,
ADDRESS2,
ADDRESS3,
ADDRESS4,
CITY,
POSTAL_CODE,
STATE,
PRIMARY_CONTACT_NAME,
PRIMARY_PHONE_COUNTRY_CODE,
PRIMARY_PHONE_AREA_CODE,
PRIMARY_PHONE_NUMBER,
PRIMARY_PHONE_EXTENSION,
ANNUAL_REVENUE,
CUSTOMER_SINCE,
NUM_ACTIVE_LOANS,
TOTAL_REMAINING_PRINCIPAL,
TOTAL_PRINCIPAL_PAID_YTD,
TOTAL_INTEREST_PAID_YTD,
TOTAL_FEE_PAID_YTD,
TOTAL_REQUESTED_LOAN_AMOUNT,
TOTAL_APPROVED_LOAN_AMOUNT,
PLEDGED_COLL_AMOUNT,
LAST_COLL_VALUATION_DATE,
CREDIT_CLASSIFICATION,
REVIEW_CYCLE,
LAST_CREDIT_REVIEW_DATE,
CREDIT_RATING,
CREDIT_HOLD,
CREDIT_CHECKING,
TOLERANCE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_UPDATE_DATE,
OBJECT_VERSION_NUMBER,
ORG_ID,
ORG_NAME,
LEGAL_ENTITY_ID,
LEGAL_ENTITY_NAME,
FUNCTIONAL_CURRENCY)
values(
l_BORROWER_TBL(l_Count).BORROWER_PARTY_ID,
l_BORROWER_TBL(l_Count).BORROWER_NAME,
l_BORROWER_TBL(l_Count).tax_payer_code,
l_BORROWER_TBL(l_Count).CUSTOMER_CLASSIFICATION,
l_BORROWER_TBL(l_Count).INDUSTRIAL_CLASSIFICATION,
l_BORROWER_TBL(l_Count).YEAR_ESTABLISHED,
l_BORROWER_TBL(l_Count).COUNTRY,
l_BORROWER_TBL(l_Count).ADDRESS1,
l_BORROWER_TBL(l_Count).ADDRESS2,
l_BORROWER_TBL(l_Count).ADDRESS3,
l_BORROWER_TBL(l_Count).ADDRESS4,
l_BORROWER_TBL(l_Count).CITY,
l_BORROWER_TBL(l_Count).POSTAL_CODE,
l_BORROWER_TBL(l_Count).STATE,
l_BORROWER_TBL(l_Count).PRIMARY_CONTACT_NAME,
l_BORROWER_TBL(l_Count).PRIMARY_PHONE_COUNTRY_CODE,
l_BORROWER_TBL(l_Count).PRIMARY_PHONE_AREA_CODE,
l_BORROWER_TBL(l_Count).PRIMARY_PHONE_NUMBER,
l_BORROWER_TBL(l_Count).PRIMARY_PHONE_EXTENSION,
l_BORROWER_TBL(l_Count).ANNUAL_REVENUE,
l_BORROWER_TBL(l_Count).CUSTOMER_SINCE,
l_BORROWER_TBL(l_Count).NUM_ACTIVE_LOANS,
l_BORROWER_TBL(l_Count).TOTAL_REMAINING_PRINCIPAL,
l_BORROWER_TBL(l_Count).TOTAL_PRINCIPAL_PAID_YTD,
l_BORROWER_TBL(l_Count).TOTAL_INTEREST_PAID_YTD,
l_BORROWER_TBL(l_Count).TOTAL_FEE_PAID_YTD,
l_BORROWER_TBL(l_Count).TOTAL_REQUESTED_LOAN_AMOUNT,
l_BORROWER_TBL(l_Count).TOTAL_APPROVED_LOAN_AMOUNT,
l_BORROWER_TBL(l_Count).PLEDGED_COLL_AMOUNT,
l_BORROWER_TBL(l_Count).LAST_COLL_VALUATION_DATE,
l_BORROWER_TBL(l_Count).CREDIT_CLASSIFICATION,
l_BORROWER_TBL(l_Count).REVIEW_CYCLE,
l_BORROWER_TBL(l_Count).LAST_CREDIT_REVIEW_DATE,
l_BORROWER_TBL(l_Count).CREDIT_RATING,
l_BORROWER_TBL(l_Count).CREDIT_HOLD,
l_BORROWER_TBL(l_Count).CREDIT_CHECKING,
l_BORROWER_TBL(l_Count).TOLERANCE,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.Conc_Request_Id,
FND_GLOBAL.PROG_APPL_ID,
sysdate,
1,
l_BORROWER_TBL(l_Count).ORG_ID,
l_BORROWER_TBL(l_Count).ORG_NAME,
l_BORROWER_TBL(l_Count).LEGAL_ENTITY_ID,
l_BORROWER_TBL(l_Count).LEGAL_ENTITY_NAME,
l_BORROWER_TBL(l_Count).FUNCTIONAL_CURRENCY);
select
distinct party.party_id,
party.PARTY_NUMBER,
party.party_name
from
lns_participants par,
hz_parties party,
lns_loan_headers_all head
where
party.party_id = nvl(P_BORROWER_ID, party.party_id) and
party.party_id = par.hz_party_id and
par.loan_id = head.loan_id and
par.loan_participant_type in ('PRIMARY_BORROWER', 'COBORROWER');
/* bill all selected loans */
FOR l_Count1 IN 1..l_Count LOOP
LogMessage(FND_LOG.LEVEL_STATEMENT, ' ');