[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT SUBSTR(cp.user_concurrent_program_name, 1, 80)
INTO l_report_name
FROM FND_CONCURRENT_PROGRAMS_VL cp,
FND_CONCURRENT_REQUESTS cr
WHERE cr.request_id = P_CONC_REQUEST_ID
AND cp.application_id = cr.program_application_id
AND cp.concurrent_program_id = cr.concurrent_program_id;
SELECT
TO_DATE(ARGUMENT3,'yyyy/mm/dd hh24:mi:ss'),TO_DATE(ARGUMENT4,'yyyy/mm/dd hh24:mi:ss') ,
TO_DATE(ARGUMENT5,'yyyy/mm/dd hh24:mi:ss'),TO_DATE(ARGUMENT6,'yyyy/mm/dd hh24:mi:ss')
INTO P_START_DATE,P_END_DATE,P_PRIOR_START_DATE,P_PRIOR_END_DATE
FROM FND_CONCURRENT_REQUESTS
WHERE REQUEST_ID = P_CONC_REQUEST_ID;
select count(*)
into v_customers_t
from hz_cust_accounts
where creation_date between p_start_date
and p_end_date + (86399/86400);
select count(*)
into prior_v_customers_t
from hz_cust_accounts
where creation_date between p_prior_start_date
and p_prior_end_date + (86399/86400);
select count(*),
NVL(sum(decode(status,'I',1,0)),0)
into cust_total_number_t,
inactive_count_total_number_t
from hz_cust_accounts;
select NVL(sum(decode(status,'I',1,0)),0)
into prior_v_inactive_count_t
from hz_cust_accounts
where last_update_date between
p_prior_start_date
and p_prior_end_date + (86399/86400);
select NVL(sum(decode(status,'I',1,0)),0)
into v_inactive_count_t
from hz_cust_accounts
where last_update_date between p_start_date
and p_end_date + (86399/86400);
select count(*)
into v_addresses_t
from hz_cust_acct_sites acct_site,
hz_party_sites party_site,
hz_loc_assignments loc_assign
where acct_site.party_site_id = party_site.party_site_id
and party_site.location_id = loc_assign.location_id
and nvl(acct_site.org_id, -99) = nvl(loc_assign.org_id,-99)
and acct_site.creation_date between p_start_date
and p_end_date + (86399/86400);
select count(*)
into prior_v_addresses_t
from hz_cust_acct_sites acct_site,
hz_party_sites party_site,
hz_loc_assignments loc_assign
where acct_site.party_site_id = party_site.party_site_id
and party_site.location_id = loc_assign.location_id
and nvl(acct_site.org_id, -99) = nvl(loc_assign.org_id,-99)
and acct_site.creation_date between p_prior_start_date
and p_prior_end_date + (86399/86400);
select count(*)
into address_total_number_t
from hz_cust_acct_sites acct_site,
hz_party_sites party_site,
hz_loc_assignments loc_assign
where acct_site.party_site_id = party_site.party_site_id
and party_site.location_id = loc_assign.location_id
and nvl(acct_site.org_id, -99) = nvl(loc_assign.org_id,-99);
select sum(nvl(b.acctd_amount,0)),
NVL( count( distinct ( a.customer_trx_id )), 0 )
into current_inv_sum_t, current_inv_period_t
from ra_cust_trx_line_gl_dist b,
ra_customer_trx a,
ra_cust_trx_types c
where complete_flag = 'Y'
and c.type in ('INV','DM','DEP','CB')
and a.cust_trx_type_id = inv_type
and a.customer_trx_id = b.customer_trx_id
and b.account_class = 'REC'
and b.latest_rec_flag = 'Y'
and a.cust_trx_type_id = c.cust_trx_type_id
and nvl(b.gl_date,a.trx_date) between p_start_date
and p_end_date + (86399/86400)
-- and a.invoice_currency_code = invoice_currency_code;
select sum(nvl(b.acctd_amount,0)) amount,
nvl(count(distinct(a.customer_trx_id)),0) trx_id
into current_inv_sum1, current_inv_period1
from ra_cust_trx_line_gl_dist b,
ra_customer_trx a,
ra_cust_trx_types c
where complete_flag = 'Y'
and c.type in ('INV','DM','DEP','CB')
and a.cust_trx_type_id = inv_type
and a.customer_trx_id = b.customer_trx_id
and b.account_class = 'REC'
and b.latest_rec_flag = 'Y'
and a.cust_trx_type_id = c.cust_trx_type_id
and b.gl_date is not null
and b.gl_date between p_start_date
and p_end_date + (86399/86400)
--and a.invoice_currency_code = invoice_currency_code ;
select sum(nvl(b.acctd_amount,0)) amount,
nvl(count(distinct(a.customer_trx_id)),0) trx_id
into current_inv_sum2, current_inv_period2
from ra_cust_trx_line_gl_dist b,
ra_customer_trx a,
ra_cust_trx_types c
where complete_flag = 'Y'
and c.type in ('INV','DM','DEP','CB')
and a.cust_trx_type_id = inv_type
and a.customer_trx_id = b.customer_trx_id
and b.account_class = 'REC'
and b.latest_rec_flag = 'Y'
and a.cust_trx_type_id = c.cust_trx_type_id
and b.gl_date is null
and a.trx_date between p_start_date
and p_end_date + (86399/86400)
--and a.invoice_currency_code = invoice_currency_code ;
select sum(nvl(b.acctd_amount,0)),
NVL( count( distinct ( a.customer_trx_id )), 0 )
into prior_inv_sum_t, prior_inv_period_t
from ra_cust_trx_line_gl_dist b,
ra_customer_trx a,
ra_cust_trx_types c
where complete_flag = 'Y'
and c.type in ('INV','DM','DEP','CB')
and a.cust_trx_type_id = inv_type
and a.customer_trx_id = b.customer_trx_id
and b.account_class = 'REC'
and b.latest_rec_flag = 'Y'
and a.cust_trx_type_id = c.cust_trx_type_id
and nvl(b.gl_date,a.trx_date) between p_prior_start_date
and p_prior_end_date + (86399/86400)
--and a.invoice_currency_code = invoice_currency_code;
select sum(nvl(b.acctd_amount,0)) amount,
nvl(count(distinct(a.customer_trx_id)),0) trx_id
into prior_inv_sum1, prior_inv_period1
from ra_cust_trx_line_gl_dist b,
ra_customer_trx a,
ra_cust_trx_types c
where complete_flag = 'Y'
and c.type in ('INV','DM','DEP','CB')
and a.cust_trx_type_id = inv_type
and a.customer_trx_id = b.customer_trx_id
and b.account_class = 'REC'
and b.latest_rec_flag = 'Y'
and a.cust_trx_type_id = c.cust_trx_type_id
and b.gl_date is not null
and b.gl_date between p_prior_start_date
and p_prior_end_date + (86399/86400)
--and a.invoice_currency_code = invoice_currency_code ;
select sum(nvl(b.acctd_amount,0)) amount,
nvl(count(distinct(a.customer_trx_id)),0) trx_id
into prior_inv_sum2, prior_inv_period2
from ra_cust_trx_line_gl_dist b,
ra_customer_trx a,
ra_cust_trx_types c
where complete_flag = 'Y'
and c.type in ('INV','DM','DEP','CB')
and a.cust_trx_type_id = inv_type
and a.customer_trx_id = b.customer_trx_id
and b.account_class = 'REC'
and b.latest_rec_flag = 'Y'
and a.cust_trx_type_id = c.cust_trx_type_id
and b.gl_date is null
and a.trx_date between p_prior_start_date
and p_prior_end_date + (86399/86400)
--and a.invoice_currency_code = invoice_currency_code ;
select NVL( count( distinct( a.customer_trx_id )), 0),
sum(gld.acctd_amount)
--into current_period, current_sum
into current_period_t, current_sum_t
from ra_cust_trx_line_gl_dist gld,
ra_customer_trx a,
ra_cust_trx_types c
where complete_flag = 'Y'
and c.type = 'CM'
and nvl(a.reason_code,'0') = reason
and a.customer_trx_id = gld.customer_trx_id
and gld.account_class = 'REC'
and gld.latest_rec_flag = 'Y'
and a.cust_trx_type_id = c.cust_trx_type_id
and nvl(gld.gl_date,a.trx_date) between p_start_date
and p_end_date + (86399/86400)
--and a.invoice_currency_code = invoice_currency_code;
select nvl(count( distinct (a.customer_trx_id)),0) cust_trx_id,
sum(nvl(gld.acctd_amount,0)) amount
into current_period1, current_sum1
from ra_cust_trx_line_gl_dist gld,
ra_customer_trx a,
ra_cust_trx_types c
where complete_flag = 'Y'
and c.type = 'CM'
and nvl(a.reason_code,'0') = reason
and a.customer_trx_id = gld.customer_trx_id
and gld.account_class = 'REC'
and gld.latest_rec_flag = 'Y'
and a.cust_trx_type_id = c.cust_trx_type_id
and gld.gl_date is not null
and gld.gl_date between p_start_date
and p_end_date + (86399/86400)
--and a.invoice_currency_code = invoice_currency_code ;
select nvl(count( distinct (a.customer_trx_id)),0) cust_trx_id,
sum(nvl(gld.acctd_amount,0)) amount
into current_period2, current_sum2
from ra_cust_trx_line_gl_dist gld,
ra_customer_trx a,
ra_cust_trx_types c
where complete_flag = 'Y'
and c.type = 'CM'
and nvl(a.reason_code,'0') = reason
and a.customer_trx_id = gld.customer_trx_id
and gld.account_class = 'REC'
and gld.latest_rec_flag = 'Y'
and a.cust_trx_type_id = c.cust_trx_type_id
and gld.gl_date is null
and a.trx_date between p_start_date
and p_end_date + (86399/86400)
--and a.invoice_currency_code = invoice_currency_code ;
select NVL( count( distinct( a.customer_trx_id )), 0),
sum(gld.acctd_amount)
--into prior_period, prior_sum
into prior_period_t, prior_sum_t
from ra_cust_trx_line_gl_dist gld,
ra_customer_trx a,
ra_cust_trx_types c
where complete_flag = 'Y'
and c.type = 'CM'
and nvl(a.reason_code,'0') = reason
and a.customer_trx_id = gld.customer_trx_id
and gld.account_class = 'REC'
and gld.latest_rec_flag = 'Y'
and a.cust_trx_type_id = c.cust_trx_type_id
and nvl(gld.gl_date,a.trx_date) between p_prior_start_date
and p_prior_end_date + (86399/86400)
--and a.invoice_currency_code = invoice_currency_code;
select nvl(count(distinct(a.customer_trx_id)),0) cust_trx_id,
sum(nvl(gld.acctd_amount ,0)) amount
into prior_period1, prior_sum1
from ra_cust_trx_line_gl_dist gld,
ra_customer_trx a,
ra_cust_trx_types c
where complete_flag = 'Y'
and c.type = 'CM'
and nvl(a.reason_code,'0') = reason
and a.customer_trx_id = gld.customer_trx_id
and gld.account_class = 'REC'
and gld.latest_rec_flag = 'Y'
and a.cust_trx_type_id = c.cust_trx_type_id
and gld.gl_date is not null
and gld.gl_date between p_prior_start_date
and p_prior_end_date + (86399/86400)
--and a.invoice_currency_code = invoice_currency_code ;
select nvl(count(distinct(a.customer_trx_id)),0) cust_trx_id,
sum(nvl(gld.acctd_amount,0)) amount
into prior_period2, prior_sum2
from ra_cust_trx_line_gl_dist gld,
ra_customer_trx a,
ra_cust_trx_types c
where complete_flag = 'Y'
and c.type = 'CM'
and nvl(a.reason_code,'0') = reason
and a.customer_trx_id = gld.customer_trx_id
and gld.account_class = 'REC'
and gld.latest_rec_flag = 'Y'
and a.cust_trx_type_id = c.cust_trx_type_id
and gld.gl_date is null
and a.trx_date between p_prior_start_date
and p_prior_end_date + (86399/86400)
--and a.invoice_currency_code = invoice_currency_code ;
select meaning
into no_reason
from ar_lookups
where lookup_code = 'NO REASON'
and lookup_type = 'CREDIT_MEMO_REASON';