[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select to_char(the_date, 'MM' ) - 3 into m from dual;
select to_char(the_date, 'YYYY' ) into y from dual;
select to_char(the_date, 'DD' ) into d from dual;
select min(trunc(billable_date))
into l_billing_date
from ar_cons_bill_cycles_b cy,
ar_cons_bill_cycle_dates cyd
where cy.billing_cycle_id = p_billing_cycle_id
and cy.billing_cycle_id = cyd.billing_cycle_id
and cyd.billable_date
between trunc(greatest(nvl(p_billing_date,sysdate), nvl(cy.START_DATE, sysdate)))
and (trunc(greatest(nvl(p_billing_date,sysdate), nvl(cy.START_DATE, sysdate))) + decode(cy.cycle_frequency,
'DAILY', 1*nvl(cy.REPEAT_DAILY,0),
'WEEKLY', 7*nvl(cy.REPEAT_WEEKLY,0),
'MONTHLY', 31*nvl(cy.REPEAT_MONTHLY,0),
0));
select max(billable_date)
into l_billing_date
from ar_cons_bill_cycle_dates
where billing_cycle_id = p_billing_cycle_id
and billable_date between trunc(p_last_bill_date) and trunc(p_billing_date);
select max(billable_date)
into l_billing_date
from ar_cons_bill_cycle_dates
where billing_cycle_id = p_billing_cycle_id
and billable_date between trunc(p_billing_date) and trunc(p_last_bill_date);
select due_days,
due_day_of_month,
due_months_forward
into dued,
duedom,
duemf
from ra_terms_lines
where term_id = p_payment_term_id;
select billing_cycle_id
into bill_cycle_id
from ra_terms
where term_id = p_payment_term_id;
select billing_cycle_id
into bill_cycle_id
from ra_terms
where name = p_payment_term_name;
select decode(cp.cons_bill_level,'ACCOUNT','A','SITE','S','N')
into bfb_level
from hz_customer_profiles cp
where cp.cust_account_id = p_cust_account_id
and cp.site_use_id IS NULL;
select decode(cp.cons_bill_level,'ACCOUNT','A','SITE','S','N')
into bfb_level
from hz_customer_profiles cp
where cp.cust_account_id = p_cust_account_id
and cp.site_use_id IS NULL;
select billing_cycle_id
into bill_cycle_id
from ra_terms
where term_id = p_payment_term_id;
select bill_cycle_type
into cycle_type
from ar_cons_bill_cycles_b
where billing_cycle_id = p_bill_cycle_id;
select nvl(accounting_affect_flag,'Y')
into open_rec
from ra_cust_trx_types
where cust_trx_type_id = p_cust_trx_type_id;
select nvl(su.payment_term_id,
decode(spt.billing_cycle_id,
-- if cycle is NULL
NULL, nvl(sp.standard_terms,
decode(apt.billing_cycle_id,
-- if cycle is NULL
NULL, nvl(nvl(ap.standard_terms, tt.default_term) , -94) ,
-- if cycle is NOT NULL
-92)),
-- if cycle is NOT NULL
decode(ap.cons_bill_level,
-- if bill level = Account
'ACCOUNT', nvl(ap.standard_terms, -93),
-- if bill level = Site
'SITE', nvl(nvl(sp.standard_terms, ap.standard_terms), -95)
-- if bill level is not set
, -91)))
into l_default_term
from ra_cust_trx_types tt,
hz_customer_profiles ap,
hz_cust_site_uses su,
ra_terms_b apt,
ra_terms_b spt,
( select /*+ leading(su2) */ cp.override_terms,cp.standard_terms,cp.cust_account_id,
su2.site_use_id profile_bill_to_site_use_id
from hz_customer_profiles cp,
hz_cust_site_uses su1,
hz_cust_site_uses su2
where cp.site_use_id = su1.site_use_id
and cp.status ='A'
and su1.cust_acct_site_id =su2.cust_acct_site_id
--bug11698651 start--
--add a condition to make sure the return result is only one row
and su1.site_use_id = su2.site_use_id
--bug11698651 end--
and su2.site_use_code = 'BILL_TO'
) sp
where p_trx_type_id = tt.cust_trx_type_id
and p_org_id = tt.org_id
and p_bill_to_site = su.site_use_id
and p_bill_to_customer = ap.cust_account_id
and ap.site_use_id is null
and p_bill_to_customer = sp.cust_account_id (+)
and su.site_use_id = sp.profile_bill_to_site_use_id (+)
and ap.standard_terms = apt.term_id (+)
and sysdate between nvl(apt.start_date_active, sysdate) and
nvl(apt.end_date_active, sysdate)
and sp.standard_terms = spt.term_id (+)
and sysdate between nvl(spt.start_date_active, sysdate) and
nvl(spt.end_date_active, sysdate);
/* Procedure that bulk updates the term_ids on imported transactions
using the predefined algorithm for BFB/ECBI. Takes a request
ID in and processes all invoices in that request batch. Also
inserts errors into ra_interface_errors for those situations
where the term is in conflict with the setups */
PROCEDURE validate_and_default_term( p_request_id IN NUMBER,
p_error_count IN OUT NOCOPY NUMBER)
IS
CURSOR c_terms(p_request_id NUMBER) IS
select
decode(invt.billing_cycle_id,
NULL, decode(decode(ap.cons_bill_level,'SITE',sp.override_terms,ap.override_terms),
'Y', trx.term_id,
nvl(su.payment_term_id,
decode(spt.billing_cycle_id,
NULL, nvl(sp.standard_terms,
decode(apt.billing_cycle_id,
NULL, nvl(nvl(ap.standard_terms, tt.default_term) , -94),
-92)),
decode(ap.cons_bill_level,
'ACCOUNT', nvl(ap.standard_terms, -93),
'SITE', nvl(nvl(sp.standard_terms, ap.standard_terms), -95),
-91)))),
nvl(su.payment_term_id,
decode(spt.billing_cycle_id,
NULL, DECODE(APT.BILLING_CYCLE_ID,
NULL, nvl(sp.standard_terms, nvl(nvl(ap.standard_terms, tt.default_term), -94)),
DECODE(AP.CONS_BILL_LEVEL,
'ACCOUNT', NVL(AP.STANDARD_TERMS, -93),
'SITE', NVL(AP.STANDARD_TERMS, -93),
-91)),
decode(ap.cons_bill_level,
'ACCOUNT', nvl(ap.standard_terms, -93),
'SITE', nvl(nvl(sp.standard_terms, ap.standard_terms), -95)
, -91)))) new_term_id, trx.customer_trx_id
from ra_customer_trx trx,
ra_cust_trx_types tt,
hz_customer_profiles ap,
hz_cust_site_uses su,
ra_terms_b sut,
ra_terms_b apt,
ra_terms_b spt,
ra_terms_b ttt,
ra_terms_b invt,
( select /*+ leading(su2) */ cp.override_terms,cp.standard_terms,cp.cust_account_id,
su2.site_use_id profile_bill_to_site_use_id
from hz_customer_profiles cp,
hz_cust_site_uses su1,
hz_cust_site_uses su2
where cp.site_use_id = su1.site_use_id
and cp.status ='A'
and su1.cust_acct_site_id =su2.cust_acct_site_id
and su2.site_use_code = 'BILL_TO'
) sp
where trx.request_id = p_request_id
and trx.previous_customer_trx_id IS NULL -- invoices only
and trx.term_id = invt.term_id
and trx.cust_trx_type_id = tt.cust_trx_type_id
and trx.org_id = tt.org_id
and tt.default_term = ttt.term_id (+)
and trx.trx_date between nvl(ttt.start_date_active, trx.trx_date) and
nvl(ttt.end_date_active, trx.trx_date)
and trx.bill_to_site_use_id = su.site_use_id
and su.payment_term_id = sut.term_id (+)
and trx.trx_date between nvl(sut.start_date_active, trx.trx_date) and
nvl(sut.end_date_active, trx.trx_date)
and trx.bill_to_customer_id = ap.cust_account_id
and ap.site_use_id is null
and NVL(ap.cons_inv_flag, 'N') = 'Y' -- 7575555
and trx.bill_to_customer_id = sp.cust_account_id (+)
and trx.bill_to_site_use_id = sp.profile_bill_to_site_use_id (+)
and ap.standard_terms = apt.term_id (+)
and trx.trx_date between nvl(apt.start_date_active, trx.trx_date) and
nvl(apt.end_date_active, trx.trx_date)
and sp.standard_terms = spt.term_id (+)
and trx.trx_date between nvl(spt.start_date_active, trx.trx_date) and
nvl(spt.end_date_active, trx.trx_date);
l_rows_selected number;
l_rows_updated number;
l_rows_selected := c_terms%ROWCOUNT;
IF l_rows_selected > 0
THEN
/* Process what we've got */
IF PG_DEBUG in ('Y', 'C') THEN
arp_standard.debug(' rows selected = ' || l_rows_selected);
/* Bulk update of transaction terms
NOTE: This excludes those in error or null */
FORALL i IN t_trx_id.FIRST..t_trx_id.LAST
UPDATE ra_customer_trx trx
SET term_id = t_term_id(i)
WHERE trx.customer_trx_id = t_trx_id(i)
AND NVL(t_term_id(i), -99) > 0;
were in error, we need to insert the correct error
message into RA_INTERFACE_ERRORS */
IF NVL(l_rows_processed,0) < l_rows_selected
THEN
/* Get error messages for bulk insert */
fnd_message.set_name('AR', 'AR_BFB_TERM_BILL_LEVEL_NULL');
INSERT into RA_INTERFACE_ERRORS
(interface_line_id,
message_text,
org_id)
SELECT line.customer_trx_line_id,
DECODE(t_term_id(err),
-91,l_msg_91,
-92,l_msg_92,
-93,l_msg_93,
-94,l_msg_94,
-95,l_msg_95),
line.org_id
FROM RA_CUSTOMER_TRX_LINES line
WHERE line.customer_trx_id = t_trx_id(err)
AND t_term_id(err) < 0;
arp_standard.debug(' rows processed = ' || l_rows_updated);
l_last_day_month_insert_flag varchar2(1); -- Added for Bug 7476810
select cycle_frequency ,
decode(cycle_frequency, 'MONTHLY', repeat_monthly ,
'WEEKLY', repeat_weekly,
'DAILY', repeat_daily ) repeat_frequency,
skip_weekends,
day_type,
trunc(start_date)
into l_cycle_frequency ,
l_repeat_frequency , l_skip_weekends, l_day_type,l_start_date
from ar_cons_bill_cycles_b
where billing_cycle_id = p_billing_cycle_id;
INSERT INTO AR_CONS_BILL_CYCLE_DATES (
BILLING_CYCLE_ID ,
BILLABLE_DATE ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY )
values
( p_billing_cycle_id,
l_next_billing_date,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id);
select day_monday, day_tuesday, day_wednesday, day_thursday, day_friday, day_saturday, day_sunday
into week_tab(2), week_tab(3), week_tab(4), week_tab(5), week_tab(6),week_tab(7),week_tab(1)
from AR_CONS_BILL_CYCLES_B
where billing_cycle_id = p_billing_cycle_id;
INSERT INTO AR_CONS_BILL_CYCLE_DATES (
BILLING_CYCLE_ID ,
BILLABLE_DATE ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY )
values
( p_billing_cycle_id,
l_next_billing_date,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id);
/* Bug 7476810. Initializing the variable l_last_day_month_insert_flag each
time to 'N' at the start of the new month */
l_last_day_month_insert_flag := 'N';
select day_1, day_2, day_3, day_4, day_5,
day_6, day_7, day_8, day_9, day_10,
day_11, day_12, day_13, day_14, day_15,
day_16, day_17, day_18, day_19, day_20,
day_21, day_22, day_23, day_24, day_25,
day_26, day_27, day_28, day_29, day_30, day_31, last_day
into daytab(1), daytab(2), daytab(3), daytab(4), daytab(5),
daytab(6), daytab(7), daytab(8), daytab(9), daytab(10),
daytab(11), daytab(12), daytab(13), daytab(14), daytab(15),
daytab(16), daytab(17), daytab(18), daytab(19), daytab(20),
daytab(21), daytab(22), daytab(23), daytab(24), daytab(25),
daytab(26), daytab(27), daytab(28), daytab(29), daytab(30), daytab(31),
l_last_day
from ar_cons_bill_cycles_b
where billing_cycle_id = p_billing_cycle_id;
/* Bug 7476810. If the last day of the month is inserted in the table as a
billling date, then dont insert the last date again. */
if daytab(i) = 'Y' and l_last_day_month_insert_flag = 'N' then
if i >= l_month_days then
l_last_day_month_insert_flag := 'Y';
would not be inserted in the table AR_CONS_BILL_CYCLE_DATES */
if l_day_type = 'W' and ar_day_of_week(l_next_billing_date) in (6,7) then
if ar_day_of_week(l_next_billing_date) = 6 then
-- If the billable day is 'Saturday', make the coming monday as billable day and skip Saturday.
daytab(i+2) := 'Y';
INSERT INTO AR_CONS_BILL_CYCLE_DATES (
BILLING_CYCLE_ID ,
BILLABLE_DATE ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY )
values
( p_billing_cycle_id,
l_next_billing_date,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id);