[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE update_balance
IS
l_id number;
SELECT fund_id,week_date
FROM bim_r_fund_balance a
WHERE exists --week_date not in
( SELECT /*+ index_desc(b,bim_r_fund_balance_n6) */ null
FROM bim_r_fund_balance b
WHERE b.fund_id = a.fund_id
AND b.week_date < a.week_date )
order by fund_id, week_date ;
SELECT b.fund_id fund_id,
c.week_date week_date,
nvl(b.original_budget,0) original,
nvl(b.original_budget,0)+nvl(b.transfer_in,0)-nvl(b.transfer_out,0) available,
nvl(c.commited_amt,0) commited,
nvl(c.commited_amt,0) commited_sum,
nvl(b.original_budget,0)+nvl(b.transfer_in,0)-nvl(b.transfer_out,0)-nvl(c.commited_amt,0) balance,
nvl(c.utilized_amt,0) utilized,
nvl(c.planned,0) planned,
nvl(c.paid,0) paid
FROM bim_r_fund_daily_facts b ,
(SELECT
c1.fund_id fund_id,
c1.week_date week_date,
SUM(nvl(fdsp.commited_amt,0)) commited_amt,
SUM(nvl(fdsp.standard_discount,0)+nvl(fdsp.market_expense,0)+nvl(fdsp.accrual,0)) utilized_amt,
SUM(nvl(fdsp.planned_amt,0)) planned,
SUM(nvl(fdsp.paid_amt,0)) paid
FROM bim_r_fdsp_daily_facts fdsp,
(SELECT fund_id fund_id,
MIN(transaction_create_date) week_date
FROM bim_r_fund_daily_facts
GROUP BY fund_id) c1
WHERE fdsp.fund_id(+) = c1.fund_id
AND fdsp.transaction_create_date(+) = c1.week_date
GROUP BY c1.fund_id, c1.week_date ) c
WHERE b.fund_id = c.fund_id
AND c.week_date = b.transaction_create_date;
fnd_message.set_token('proc_name', 'UPDATE_BALANCE', FALSE);
INSERT into BIM_R_FUND_BALANCE (fund_id, week_date,fis_month, fis_qtr, fis_year)
SELECT fund_id,transaction_create_date,fis_month, fis_qtr, fis_year
FROM bim_r_fund_daily_facts;
select i.index_tablespace into l_index_tablespace
from fnd_product_installations i, fnd_application a
where a.application_short_name = 'BIM'
and a.application_id = i.application_id;
UPDATE BIM_R_FUND_BALANCE a
SET current_available = l.available,
past_available = l.original,
commited =l.commited,
commited_sum=l.commited_sum,
current_balance =l.balance,
utilized=l.utilized,
planned = l.planned,
paid = l.paid
WHERE a.fund_id = l.fund_id
AND a.week_date = l.week_date;
ams_utility_pvt.write_conc_log('error updateing minimum balance'||sqlerrm(sqlcode));
/* Update in loop: for each fund, each date, populate the balance,
commited, utilized, planned, paid */
FOR x in balance_cur LOOP
BEGIN
UPDATE bim_r_fund_balance fb
SET (past_available,current_available,commited,commited_sum,
current_balance, utilized,planned, paid)
=(
select /*+ use_nl(maxdate ba) */
nvl(ba.current_available,0),
nvl(ba.current_available,0)+nvl(a.transfer_in,0) - nvl(a.transfer_out,0),
nvl(fdsp.commited_amt,0),
nvl(ba.commited_sum,0) + nvl(fdsp.commited_amt,0),
nvl(ba.current_available,0)+nvl(a.transfer_in,0) - nvl(a.transfer_out,0) -nvl(ba.commited_sum,0)-nvl(fdsp.commited_amt,0),
nvl(fdsp.utilized_amt,0),
nvl(fdsp.planned, 0),
nvl(fdsp.paid,0)
from (select max(week_date) max_date
from bim_r_fund_balance
where fund_id = x.fund_id
and week_date < x.week_date) maxdate,
bim_r_fund_balance ba,
bim_r_fund_daily_facts a,
(select fund_id fund_id,
SUM(nvl(commited_amt,0)) commited_amt,
SUM(nvl(planned_amt,0)) planned,
SUM(nvl(paid_amt,0)) paid,
SUM(nvl(standard_discount,0)+nvl(market_expense,0)+nvl(accrual,0)) utilized_amt,
transaction_create_date week_date
from bim_r_fdsp_daily_facts
WHERE fund_id = x.fund_id
AND transaction_create_date = x.week_date
group by fund_id, transaction_create_date) fdsp
where a.fund_id =x.fund_id
and a.transaction_create_date = x.week_date
and fdsp.fund_id(+) = a.fund_id
and fdsp.week_date(+)=a.transaction_create_date
and ba.week_date =maxdate.max_date
and ba.fund_id = x.fund_id
and ba.fund_id = a.fund_id
)
where fb.fund_id = x.fund_id
and fb.week_date = x.week_date ;
ams_utility_pvt.write_conc_log('error updateing balance in loop'||sqlerrm(sqlcode));
fnd_message.set_token('proc_name', 'UPDATE_BALANCE', FALSE);
ams_utility_pvt.write_conc_log('error updateing balance'||sqlerrm(sqlcode));
END update_balance;
PROCEDURE update_sub_balance(p_start_date DATE, p_end_date DATE)
IS
l_oldid NUMBER:=0;
SELECT distinct w.fund_id, w.transaction_create_date
FROM bim_r_fund_daily_facts w
WHERE not exists( select 1
from bim_r_fund_balance b
where b.fund_id = w.fund_id
and b.week_date = w.transaction_create_date)
order by w.fund_id, w.transaction_create_date;
SELECT distinct fund_id fund_id, max(week_date) mdate
FROM bim_r_fund_balance
WHERE week_date
fnd_message.set_token('proc_name', 'UPDATE_SUB_BALANCE', FALSE);
SELECT i.index_tablespace into l_index_tablespace
FROM fnd_product_installations i, fnd_application a
WHERE a.application_short_name = 'BIM'
AND a.application_id = i.application_id;
INSERT INTO bim_r_fund_balance(
FUND_ID
, WEEK_DATE
, CURRENT_AVAILABLE
, PAST_AVAILABLE
, COMMITED
, COMMITED_SUM
, CURRENT_BALANCE
, UTILIZED
, PLANNED
, PAID
, FIS_MONTH
, FIS_QTR
, FIS_YEAR)
SELECT x.fund_id,
a.transaction_create_date,
nvl(ba.current_available,0)+nvl(a.transfer_in,0) - nvl(a.transfer_out, 0) ,
nvl(ba.current_available, 0),
nvl(fdsp.commited_amt,0),
nvl(ba.commited_sum,0) + nvl(fdsp.commited_amt, 0),
nvl(ba.current_available,0)+nvl(a.transfer_in,0) - nvl(a.transfer_out,0) -nvl(ba.commited_sum,0)-nvl(fdsp.
commited_amt, 0),
nvl(fdsp.utilized,0),
nvl(fdsp.planned,0),
nvl(fdsp.paid,0),
a.fis_month,
a.fis_qtr,
a.fis_year
FROM bim_r_fund_balance ba,
bim_r_fund_daily_facts a,
(SELECT fund_id fund_id,
SUM(nvl(commited_amt,0)) commited_amt,
SUM(nvl(standard_discount,0)+nvl(accrual,0)+
nvl(market_expense,0)) utilized,
SUM(nvl(planned_amt,0)) planned,
SUM(nvl(paid_amt,0)) paid,
transaction_create_date week_date
FROM bim_r_fdsp_daily_facts
WHERE fund_id = x.fund_id
AND transaction_create_date between p_start_date and p_end_date+0.99999
GROUP BY fund_id, transaction_create_date) fdsp
WHERE a.fund_id =x.fund_id
and a.transaction_create_date between p_start_date and p_end_date+0.99999
and fdsp.fund_id(+) = a.fund_id
and fdsp.week_date(+)=a.transaction_create_date
and ba.week_date =x.mdate
and ba.fund_id = a.fund_id;
FND_FILE.put_line(fnd_file.log,'error updateing delta balance'||sqlerrm(sqlcode));
INSERT INTO bim_r_fund_balance(
FUND_ID
, WEEK_DATE
, CURRENT_AVAILABLE
, PAST_AVAILABLE
, COMMITED
, COMMITED_SUM
, CURRENT_BALANCE
, UTILIZED
, PLANNED
, PAID
, FIS_MONTH
, FIS_QTR
, FIS_YEAR)
SELECT x.fund_id,
x.transaction_create_date,
nvl(a.original_budget,0)+nvl(a.transfer_in,0) - nvl(a.transfer_out,0),
nvl(a.original_budget,0),
nvl(fdsp.commited_amt,0),
nvl(fdsp.commited_amt,0),
nvl(a.original_budget,0)+nvl(a.transfer_in,0)-nvl(a.transfer_out,0)-nvl(fdsp.commited_amt,0),
nvl(fdsp.utilized,0),
nvl(fdsp.planned,0),
nvl(fdsp.paid,0),
a.fis_month,
a.fis_qtr,
a.fis_year
FROM bim_r_fund_daily_facts a,
(SELECT fund_id fund_id,
SUM(nvl(commited_amt,0)) commited_amt,
SUM(nvl(standard_discount,0)+nvl(accrual,0)+
nvl(market_expense,0)) utilized,
SUM(nvl(planned_amt,0)) planned,
SUM(nvl(paid_amt,0)) paid,
transaction_create_date week_date
FROM bim_r_fdsp_daily_facts
WHERE fund_id = x.fund_id
AND transaction_create_date = x.transaction_create_date
GROUP BY fund_id, transaction_create_date) fdsp
WHERE a.fund_id =x.fund_id
AND a.transaction_create_date = x.transaction_create_date
AND fdsp.fund_id(+) = a.fund_id
AND fdsp.week_date(+)=a.transaction_create_date;
INSERT INTO bim_r_fund_balance(
FUND_ID
, WEEK_DATE
, PAST_AVAILABLE
, CURRENT_AVAILABLE
, COMMITED
, COMMITED_SUM
, CURRENT_BALANCE
, UTILIZED
, PLANNED
, PAID
, FIS_MONTH
, FIS_QTR
, FIS_YEAR)
SELECT x.fund_id,
x.transaction_create_date,
nvl(ba.current_available,0),
nvl(ba.current_available,0)+nvl(a.transfer_in,0) - nvl(a.transfer_out,0),
nvl(fdsp.commited_amt,0),
nvl(ba.commited_sum,0) + nvl(fdsp.commited_amt,0),
nvl(ba.current_available,0)+nvl(a.transfer_in,0) - nvl(a.transfer_out,0) - nvl(ba.commited_sum,0)-nvl( fdsp.commited_amt,0),
nvl(fdsp.utilized,0),
nvl(fdsp.planned,0),
nvl(fdsp.paid,0),
a.fis_month,
a.fis_qtr,
a.fis_year
FROM (select max(week_date) max_date
from bim_r_fund_balance
where fund_id = x.fund_id
and week_date < x.transaction_create_date) maxdate,
bim_r_fund_balance ba,
bim_r_fund_daily_facts a,
(select fund_id fund_id,
sum(nvl(commited_amt,0)) commited_amt,
SUM(nvl(standard_discount,0)+nvl(accrual,0)+
nvl(market_expense,0)) utilized,
SUM(nvl(planned_amt,0)) planned,
SUM(nvl(paid_amt,0)) paid,
transaction_create_date week_date
from bim_r_fdsp_daily_facts
where fund_id = x.fund_id
and transaction_create_date = x.transaction_create_date
group by fund_id, transaction_create_date) fdsp
WHERE a.fund_id =x.fund_id
and a.transaction_create_date = x.transaction_create_date
and fdsp.fund_id(+) = a.fund_id
and fdsp.week_date(+)=a.transaction_create_date
and ba.week_date =maxdate.max_date
and ba.fund_id = x.fund_id;
fnd_message.set_token('proc_name', 'UPDATE_SUB_BALANCE', FALSE);
FND_FILE.put_line(fnd_file.log,'error updateing new balance'||sqlerrm(sqlcode));
END update_sub_balance;
l_last_update_date DATE;
CURSOR last_update_history IS
SELECT MAX(end_date)
FROM bim_rep_history
WHERE object = p_object;
SELECT start_date
INTO l_start_date
FROM gl_periods
WHERE start_date <
(select nvl(min(start_date_active),sysdate)
from ozf_funds_all_b
)
AND rownum < 2;
OPEN last_update_history;
FETCH last_update_history INTO l_last_update_date;
CLOSE last_update_history;
IF (l_last_update_date IS NOT NULL AND p_start_date IS NOT NULL) THEN
fnd_message.set_name('BIM','BIM_R_FIRST_LOAD');
fnd_message.set_token('end_date', l_last_update_date, FALSE);
IF l_last_update_date IS NOT NULL THEN
IF (p_end_date <= l_last_update_date) THEN
ams_utility_pvt.write_conc_log('The current end date cannot be less than the last end date ');
FUND_SUB_LOAD(p_start_datel => TRUNC(l_last_update_date + 1)
,p_end_datel => TRUNC(l_end_date)
,p_para_num => p_para_num
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,x_return_status => x_return_status);
INSERT INTO
bim_rep_history
(creation_date,
last_update_date,
created_by,
last_updated_by,
object_last_updated_date,
object,
start_date,
end_date)
VALUES
(sysdate,
sysdate,
l_user_id,
l_user_id,
sysdate,
p_object,
p_start_time,
p_end_time);
FND_MESSAGE.Set_Name ('AMS', 'AMS_REP_INSERT_ERROR');
l_last_update_date DATE;
SELECT i.tablespace, i.index_tablespace, u.oracle_username
FROM fnd_product_installations i, fnd_application a, fnd_oracle_userid u
WHERE a.application_short_name = 'BIM'
AND a.application_id = i.application_id
AND u.oracle_id = i.oracle_id;
SELECT a.owner,a.index_name,b.table_name,b.column_name,pct_free,ini_trans,max_trans
,initial_extent,next_extent,min_extents,
max_extents, pct_increase
FROM all_indexes a, all_ind_columns b
WHERE a.index_name = b.index_name
AND a.owner = l_schema
AND a.owner = b.index_owner
AND (a.index_name like 'BIM_R_FUND%_FACTS%'
OR a.index_name like 'BIM_R_FDSP%_FACTS%')
ORDER BY a.index_name;
/* In order to speed up insertion. We drop all the index before inserting, and
create them back after inserting. But we want to keep the parameters(tablespace,
indexspace etc) they were created before. */
OPEN get_ts_name;
/* First insert: Insert all the transactions: transfer in/out for funds whose active date between p_start_date and p_end_date */
INSERT /*+ append parallel(fdf,p_para_num) */
INTO bim_r_fund_daily_facts fdf(
fund_transaction_id
,creation_date
,last_update_date
,created_by
,last_updated_by
,last_update_login
,fund_id
,parent_fund_id
,fund_number
,start_date
,end_date
,start_period
,end_period
,set_of_books_id
,fund_type
--,region
,country
,org_id
,category_id
,status
,original_budget
,transfer_in
,transfer_out
,holdback_amt
,currency_code_fc
,delete_flag
,transaction_create_date
,load_date
,fis_month
,fis_qtr
,fis_year
,business_unit_id)
SELECT /*+ parallel(inner, p_para_num) */
bim_r_fund_daily_facts_s.nextval,
sysdate,
sysdate,
l_user_id,
l_user_id,
l_user_id,
inner.fund_id,
inner.parent_fund_id,
inner.fund_number,
inner.start_date,
inner.end_date,
inner.start_period,
inner.end_period,
inner.set_of_books_id,
inner.fund_type,
--inner.region,
inner.country,
inner.org_id,
inner.category_id,
inner.status,
inner.original_budget,
inner.transfer_in,
inner.transfer_out,
inner.holdback,
inner.currency_code_fc,
'N',
inner.transaction_create_date,
inner.weekend_date,
BIM_SET_OF_BOOKS.get_fiscal_month(inner.transaction_create_date,204),
BIM_SET_OF_BOOKS.get_fiscal_qtr(inner.transaction_create_date,204),
BIM_SET_OF_BOOKS.get_fiscal_year(inner.transaction_create_date,204),
inner.business_unit_id
FROM (
SELECT fund_id fund_id,
fund_number fund_number,
start_date start_date,
end_date end_date,
start_period start_period,
end_period end_period,
category_id category_id,
status status,
fund_type fund_type,
parent_fund_id parent_fund_id,
country country,
org_id org_id,
business_unit_id business_unit_id,
set_of_books_id set_of_books_id,
currency_code_fc currency_code_fc,
original_budget original_budget,
transaction_create_date transaction_create_date,
weekend_date weekend_date,
SUM(transfer_in) transfer_in,
SUM(transfer_out) transfer_out,
SUM(holdback) holdback
FROM (
SELECT ad.fund_id fund_id,
ad.fund_number fund_number,
ad.start_date_active start_date,
ad.end_date_active end_date,
ad.start_period_name start_period,
ad.end_period_name end_period,
ad.category_id category_id,
ad.status_code status,
ad.fund_type fund_type,
ad.parent_fund_id parent_fund_id,
ad.country_code country,
ad.org_id org_id,
ad.business_unit_id business_unit_id,
ad.set_of_books_id set_of_books_id,
ad.currency_code_fc currency_code_fc,
ad.original_budget original_budget,
ad.tr_date transaction_create_date,
trunc((decode(decode( to_char(ad.tr_date,'MM') , to_char(next_day(ad.tr_date,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM') ,'TRUE','FALSE' )
,'TRUE'
,decode(decode(ad.tr_date , (next_day(ad.tr_date, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))) - 7) , 'TRUE' ,'FALSE')
,'TRUE'
,ad.tr_date
,'FALSE'
,next_day(ad.tr_date, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))))
,'FALSE'
,decode(decode(to_char(ad.tr_date,'MM'),to_char(next_day(ad.tr_date,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM'),'TRUE','FALSE')
,'FALSE'
,last_day(ad.tr_date))))) weekend_date,
nvl(SUM(convert_currency(bu1.approved_in_currency,nvl(bu1.approved_original_amount,0))),0) transfer_in,
0 transfer_out,
0 holdback
FROM (SELECT a.fund_id fund_id,
a.fund_number fund_number,
a.start_date_active start_date_active,
a.end_date_active end_date_active,
a.start_period_name start_period_name,
a.end_period_name end_period_name,
a.category_id category_id,
a.status_code status_code,
a.fund_type fund_type,
a.parent_fund_id parent_fund_id,
a.business_unit_id business_unit_id,
a.country_id country_code,
--b.area2_code area2_code,
a.org_id org_id,
a.set_of_books_id set_of_books_id,
a.currency_code_fc currency_code_fc,
-- decode(trunc(d.trdate),trunc(a.PROGRAM_UPDATE_DATE),a.original_budget,0) original_budget,
a.original_budget original_budget,
trunc(d.trdate) tr_date
FROM ozf_funds_all_b a,
bim_intl_dates d
WHERE a.status_code in ('ACTIVE', 'CANCELLED', 'CLOSED')
AND d.trdate between a.start_date_active and least(nvl(a.end_date_active,sysdate-1),p_end_datel)
AND a.start_date_active between p_start_datel and p_end_datel
GROUP BY a.fund_id,
trunc(d.trdate),
-- trunc(a.PROGRAM_UPDATE_DATE),
a.fund_number,
a.start_date_active,
a.end_date_active,
a.start_period_name,
a.end_period_name,
a.category_id,
a.status_code,
a.fund_type,
a.parent_fund_id,
a.country_id,
a.org_id,
a.business_unit_id,
a.set_of_books_id,
a.currency_code_fc,
a.original_budget
) ad,
ozf_act_budgets BU1
WHERE bu1.approval_date(+) between ad.tr_date and ad.tr_date + 0.99999
AND bu1.transfer_type in ('TRANSFER','REQUEST')
AND bu1.status_code(+) = 'APPROVED'
AND bu1.arc_act_budget_used_by(+) = 'FUND'
AND bu1.act_budget_used_by_id(+) = ad.fund_id
AND bu1.budget_source_type(+) ='FUND'
GROUP BY ad.fund_id,
ad.tr_date ,
ad.fund_number,
ad.start_date_active ,
ad.end_date_active ,
ad.start_period_name ,
ad.end_period_name ,
ad.category_id ,
ad.status_code ,
ad.fund_type ,
ad.parent_fund_id,
ad.country_code,
ad.business_unit_id,
ad.org_id ,
ad.set_of_books_id ,
ad.currency_code_fc ,
ad.original_budget
UNION ALL
SELECT ad.fund_id fund_id,
ad.fund_number fund_number,
ad.start_date_active start_date,
ad.end_date_active end_date,
ad.start_period_name start_period,
ad.end_period_name end_period,
ad.category_id category_id,
ad.status_code status,
ad.fund_type fund_type,
ad.parent_fund_id parent_fund_id,
ad.country_code country,
ad.org_id org_id,
ad.business_unit_id business_unit_id,
ad.set_of_books_id set_of_books_id,
ad.currency_code_fc currency_code_fc,
ad.original_budget original_budget,
ad.tr_date transaction_create_date,
trunc((decode(decode( to_char(ad.tr_date,'MM') , to_char(next_day(ad.tr_date,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM') ,'TRUE','FALSE' )
,'TRUE'
,decode(decode(ad.tr_date , (next_day(ad.tr_date, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))) - 7) , 'TRUE' ,'FALSE')
,'TRUE'
,ad.tr_date
,'FALSE'
,next_day(ad.tr_date, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))))
,'FALSE'
,decode(decode(to_char(ad.tr_date,'MM'),to_char(next_day(ad.tr_date,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM'),'TRUE','FALSE')
,'FALSE'
,last_day(ad.tr_date))))) weekend_date,
0 transfer_in,
nvl(SUM(decode(bu2.transfer_type,'TRANSFER', convert_currency(bu2.approved_in_currency,nvl(bu2.approved_original_amount,0)))),0)
+nvl(SUM(decode(bu2.transfer_type,'REQUEST', convert_currency(bu2.approved_in_currency,nvl(bu2.approved_original_amount,0)))),0) transfer_out,
--nvl(SUM(convert_currency(bu2.approved_in_currency,nvl(bu2.approved_original_amount,0))),0) transfer_out,
nvl(SUM(decode(bu2.transfer_type, 'RESERVE', convert_currency(bu2.approved_in_currency,nvl(bu2.approved_original_amount,0)))),0)-
nvl(SUM(decode(bu2.transfer_type, 'RELEASE', convert_currency(bu2.approved_in_currency,nvl(bu2.approved_original_amount,0)))),0) holdback
FROM (SELECT a.fund_id fund_id,
a.fund_number fund_number,
a.start_date_active start_date_active,
a.end_date_active end_date_active,
a.start_period_name start_period_name,
a.end_period_name end_period_name,
a.category_id category_id,
a.status_code status_code,
a.fund_type fund_type,
a.parent_fund_id parent_fund_id,
a.country_id country_code,
a.org_id org_id,
a.business_unit_id business_unit_id,
a.set_of_books_id set_of_books_id,
a.currency_code_fc currency_code_fc,
-- decode(trunc(d.trdate),trunc(a.PROGRAM_UPDATE_DATE),a.original_budget,0) original_budget,
a.original_budget original_budget,
trunc(d.trdate) tr_date
FROM ozf_funds_all_b a,
bim_intl_dates d
WHERE a.status_code in ('ACTIVE', 'CANCELLED', 'CLOSED')
AND d.trdate between a.start_date_active and least(nvl(a.end_date_active,sysdate-1),p_end_datel)
AND a.start_date_active between p_start_datel and p_end_datel
GROUP BY a.fund_id,
trunc(d.trdate),
-- trunc(a.PROGRAM_UPDATE_DATE),
a.fund_number,
a.start_date_active,
a.end_date_active,
a.start_period_name,
a.end_period_name,
a.category_id,
a.status_code,
a.fund_type,
a.parent_fund_id,
a.country_id,
a.org_id,
a.business_unit_id,
a.set_of_books_id,
a.currency_code_fc,
a.original_budget
) ad,
ozf_act_budgets BU2
WHERE bu2.approval_date(+) between ad.tr_date and ad.tr_date + 0.99999
AND bu2.status_code(+) = 'APPROVED'
AND bu2.arc_act_budget_used_by(+) = 'FUND'
AND bu2.budget_source_type(+) ='FUND'
AND bu2.budget_source_id (+)= ad.fund_id
GROUP BY ad.fund_id,
ad.tr_date ,
ad.fund_number,
ad.start_date_active ,
ad.end_date_active ,
ad.start_period_name ,
ad.end_period_name ,
ad.category_id ,
ad.status_code ,
ad.fund_type ,
ad.parent_fund_id,
ad.country_code,
ad.org_id ,
ad.business_unit_id,
ad.set_of_books_id ,
ad.currency_code_fc ,
ad.original_budget)
GROUP BY
fund_id,
transaction_create_date,
weekend_date,
fund_number,
start_date,
end_date,
start_period,
end_period,
category_id,
status,
fund_type,
parent_fund_id,
country,
org_id,
business_unit_id,
set_of_books_id,
currency_code_fc,
original_budget
)inner;
FND_MESSAGE.Set_Name ('AMS', 'AMS_REP_INSERT_ERROR');
/*Second insert: more records which transact before active date */
BEGIN
-- dbms_output.put_line('inserting extra');
INSERT
INTO bim_r_fund_daily_facts fdf(
fund_transaction_id
,creation_date
,last_update_date
,created_by
,last_updated_by
,last_update_login
,fund_id
,parent_fund_id
,fund_number
,start_date
,end_date
,start_period
,end_period
,set_of_books_id
,fund_type
--,region
,country
,org_id
,category_id
,status
,original_budget
,transfer_in
,transfer_out
,holdback_amt
,currency_code_fc
,delete_flag
,transaction_create_date
,load_date
,fis_month
,fis_qtr
,fis_year
,business_unit_id)
-- ,security_group_id)
SELECT
bim_r_fund_daily_facts_s.nextval,
sysdate,
sysdate,
l_user_id,
l_user_id,
l_user_id,
inner.fund_id,
inner.parent_fund_id,
inner.fund_number,
inner.start_date,
inner.end_date,
inner.start_period,
inner.end_period,
inner.set_of_books_id,
inner.fund_type,
--inner.region,
inner.country,
inner.org_id,
inner.category_id,
inner.status,
inner.original_budget,
inner.transfer_in,
inner.transfer_out,
inner.holdback,
inner.currency_code_fc,
'N',
inner.transaction_create_date,
inner.weekend_date,
BIM_SET_OF_BOOKS.get_fiscal_month(inner.transaction_create_date,204),
BIM_SET_OF_BOOKS.get_fiscal_qtr(inner.transaction_create_date,204),
BIM_SET_OF_BOOKS.get_fiscal_year(inner.transaction_create_date,204),
inner.business_unit_id
FROM (
SELECT transaction_create_date transaction_create_date,
trunc((decode(decode( to_char(transaction_create_date,'MM') , to_char(next_day(transaction_create_date,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM') ,'TRUE','FALSE' )
,'TRUE'
,decode(decode(transaction_create_date , (next_day(transaction_create_date, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))) - 7) , 'TRUE' ,'FALSE')
,'TRUE'
,transaction_create_date
,'FALSE'
,next_day(transaction_create_date, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))))
,'FALSE'
,decode(decode(to_char(transaction_create_date,'MM'),to_char(next_day(transaction_create_date,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM'),'TRUE','FALSE')
,'FALSE'
,last_day(transaction_create_date))))) weekend_date,
SUM(transfer_in) transfer_in,
SUM(transfer_out) transfer_out,
SUM(holdback) holdback,
fund_id ,
fund_number ,
start_date ,
end_date ,
start_period ,
end_period ,
category_id ,
status ,
fund_type ,
parent_fund_id ,
business_unit_id ,
country ,
org_id ,
set_of_books_id ,
currency_code_fc ,
original_budget
FROM (
SELECT trunc(bu1.approval_date) transaction_create_date,
nvl(SUM(convert_currency(bu1.approved_in_currency,nvl(bu1.approved_original_amount,0))),0) transfer_in,
0 transfer_out,
0 holdback,
o.fund_id fund_id,
o.fund_number fund_number,
o.start_date_active start_date,
o.end_date_active end_date,
o.start_period_name start_period,
o.end_period_name end_period,
o.category_id category_id,
o.status_code status,
o.fund_type fund_type,
o.parent_fund_id parent_fund_id,
o.business_unit_id business_unit_id,
o.country_id country,
o.org_id org_id,
o.set_of_books_id set_of_books_id,
o.currency_code_fc currency_code_fc,
o.original_budget original_budget
FROM ozf_funds_all_b o,
ozf_act_budgets BU1
WHERE o.start_date_active between p_start_datel and p_end_datel
AND o.status_code in ('ACTIVE','CANCELLED', 'CLOSED')
AND bu1.transfer_type in ('TRANSFER', 'REQUEST')
AND bu1.approval_date
FND_FILE.put_line(fnd_file.log,'error insert into fund_daily for transactions happened b4 start date'||sqlerrm(sqlcode));
FND_MESSAGE.Set_Name ('AMS', 'AMS_REP_INSERT_ERROR');
/* Start inserting into 'bim_r_fdsp_daily_facts' */
l_table_name :='bim_r_fdsp_daily_facts';
INSERT /*+ append parallel(bfl,p_para_num) */
INTO bim_r_fdsp_load bfl(
spend_transaction_id
,creation_date
,last_update_date
,created_by
,last_updated_by
,last_update_login
,fund_id
,business_unit_id
,util_org_id
,standard_discount
,accrual
,market_expense
,commited_amt
,planned_amt
,paid_amt
,delete_flag
,transaction_create_date
,load_date
,object_id
,object_type)
SELECT /*+ parallel(act_util, p_para_num) */
0,
sysdate,
sysdate,
-1,
-1,
-1,
act_util.fund_id,
0,
0,
act_util.standard_discount,
act_util.accrual,
act_util.market_expense,
act_util.commited_amt,
act_util.planned_amt,
act_util.paid_amt,
'Y',
act_util.creation_date,
sysdate,
act_util.object_id,
act_util.object_type
FROM (SELECT fund_id fund_id,
object_id object_id,
object_type object_type,
creation_date creation_date,
SUM(nvl(planned_amt,0)) planned_amt,
SUM(nvl(commited_amt,0)) commited_amt,
SUM(nvl(standard_discount,0)) standard_discount,
SUM(nvl(accrual,0)) accrual,
SUM(nvl(market_expense,0)) market_expense,
SUM(nvl(paid_amt,0)) paid_amt
FROM (
SELECT budget_source_id fund_id,
act_budget_used_by_id object_id,
arc_act_budget_used_by object_type,
trunc(nvl(request_date,creation_date)) creation_date,
SUM(convert_currency(request_currency, nvl(request_amount,0))) planned_amt,
0 commited_amt,
0 standard_discount,
0 accrual,
0 market_expense,
0 paid_amt
FROM ozf_act_budgets
WHERE budget_source_type ='FUND'
AND status_code ='PENDING'
AND ARC_ACT_BUDGET_USED_BY <> 'FUND'
GROUP BY trunc(nvl(request_date ,creation_date)),
budget_source_id,act_budget_used_by_id,
arc_act_budget_used_by
UNION ALL
SELECT budget_source_id fund_id,
act_budget_used_by_id object_id,
arc_act_budget_used_by object_type,
trunc(nvl(approval_date,last_update_date)) creation_date,
--0-SUM(convert_currency(request_currency, nvl(request_amount,0))) planned_amt,
0 planned_amt,
SUM(convert_currency(approved_in_currency,nvl(approved_original_amount,0))) commited_amt,
0 standard_discount,
0 accrual,
0 market_expense,
0 paid_amt
FROM ozf_act_budgets
WHERE budget_source_type ='FUND'
AND ARC_ACT_BUDGET_USED_BY <> 'FUND'
AND status_code ='APPROVED'
GROUP BY trunc(nvl(approval_date,last_update_date)),
budget_source_id,act_budget_used_by_id,
arc_act_budget_used_by
UNION ALL
SELECT act_budget_used_by_id fund_id,
budget_source_id object_id,
budget_source_type object_type,
trunc(nvl(approval_date,last_update_date)) creation_date,
0 planned_amt,
0-SUM(convert_currency(approved_in_currency,nvl(approved_original_amount,0))) commited_amt,
0 standard_discount,
0 accrual,
0 market_expense,
0 paid_amt
FROM ozf_act_budgets
WHERE arc_act_budget_used_by ='FUND'
AND budget_source_type<>'FUND'
AND status_code ='APPROVED'
GROUP BY trunc(nvl(approval_date,last_update_date)),
act_budget_used_by_id, budget_source_id,
budget_source_type
UNION ALL
SELECT fund_id fund_id,
plan_id object_id,
plan_type object_type,
trunc(creation_date) creation_date,
0 planned_amt,
0 commited_amt,
SUM(decode(component_type,'OFFR',decode(utilization_type, 'UTILIZED',convert_currency(currency_code,nvl(amount,0)), 0),0)) standard_discount,
SUM(decode(component_type,'OFFR', decode(utilization_type, 'ACCRUAL', convert_currency(currency_code,nvl(amount,0)), 0),0) +
decode(component_type,'OFFR', decode(utilization_type, 'ADJUSTMENT', convert_currency(currency_code,nvl(amount,0)), 0),0)) accrual,
SUM(decode(component_type,'OFFR',0, decode(utilization_type, 'UTILIZED', convert_currency(currency_code,nvl(amount,0)), 0))) market_expense,
sum(decode(component_type,'OFFR',0,convert_currency(currency_code,(nvl(amount,0)-NVL(amount_remaining,0))))) paid_amt
FROM ozf_funds_utilized_all_b
WHERE utilization_type in ('UTILIZED','ACCRUAL','ADJUSTMENT')
GROUP BY trunc(creation_date),fund_id,plan_id,plan_type
)
GROUP BY creation_date, fund_id, object_id,object_type
) act_util;
FND_FILE.put_line(fnd_file.log,'error insert fdsp daily'||sqlerrm(sqlcode));
FND_MESSAGE.Set_Name ('AMS', 'AMS_REP_INSERT_ERROR');
END ; --end of insertion into bim_r_fdsp_load.
/* First insert: insert into bim_r_fdsp_daily_facts for the funds whose active
date between p_start_date and p_end_date. Fund transactions are: commited, planned, utilized
etc. The measures are on object level. Object are like campaigns, events, offers, etc. */
BEGIN
INSERT /*+ append parallel(fdf,p_para_num) */
INTO bim_r_fdsp_daily_facts fdf(
spend_transaction_id
,creation_date
,last_update_date
,created_by
,last_updated_by
,last_update_login
,fund_id
,business_unit_id
,util_org_id
,standard_discount
,accrual
,market_expense
,commited_amt
,planned_amt
,paid_amt
,delete_flag
,transaction_create_date
,load_date
,object_id
,object_type
,fis_month
,fis_qtr
,fis_year
)
SELECT /*+ parallel(inner, p_para_num) */
bim_r_fdsp_daily_facts_s.nextval,
sysdate,
sysdate,
l_user_id,
l_user_id,
l_user_id,
inner.fund_id,
inner.business_unit_id,
inner.org_id,
inner.standard_discount,
inner.accrual,
inner.market_expense,
inner.commited_amt,
inner.planned_amt,
inner.paid_amt,
'N',
inner.transaction_create_date,
trunc((decode(decode( to_char(inner.transaction_create_date,'MM') , to_char(next_day(inner.transaction_create_date,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM') ,'TRUE','FALSE' )
,'TRUE'
,decode(decode(inner.transaction_create_date , (next_day(inner.transaction_create_date, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))) - 7) , 'TRUE' ,'FALSE')
,'TRUE'
,inner.transaction_create_date
,'FALSE'
,next_day(inner.transaction_create_date, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))))
,'FALSE'
,decode(decode(to_char(inner.transaction_create_date,'MM'),to_char(next_day(inner.transaction_create_date,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM'),'TRUE','FALSE')
,'FALSE'
,last_day(inner.transaction_create_date))))) weekend_date
,inner.object_id
,inner.object_type
,BIM_SET_OF_BOOKS.get_fiscal_month(inner.transaction_create_date,204)
,BIM_SET_OF_BOOKS.get_fiscal_qtr(inner.transaction_create_date,204)
,BIM_SET_OF_BOOKS.get_fiscal_year(inner.transaction_create_date,204)
FROM (SELECT AD.fund_id fund_id,
U.business_unit_id business_unit_id,
U.org_id org_id,
NVL(AU.standard_discount,0) standard_discount,
NVL(AU.accrual,0) accrual,
NVL(AU.market_expense,0) market_expense,
NVL(AU.commited_amt,0) commited_amt,
NVL(AU.planned_amt,0) planned_amt,
NVL(AU.paid_amt,0) paid_amt,
AU.object_id object_id,
U.object_type object_type,
AD.trdate transaction_create_date
FROM (SELECT A.fund_id fund_id,TRUNC(DA.trdate) trdate
FROM ozf_funds_all_b A,
bim_intl_dates DA
WHERE A.status_code IN ( 'ACTIVE','CANCELLED','CLOSED' )
AND DA.trdate between A.start_date_active
and least(nvl(A.end_date_active,sysdate-1),p_end_datel)
AND A.start_date_active between p_start_datel and p_end_datel
) AD,
bim_r_fdsp_load AU,
(SELECT
D.business_unit_id business_unit_id,D.org_id org_id,
'CAMP' object_type_J,'CAMP' object_type, D.campaign_id object_id
FROM ams_campaigns_all_b D
UNION ALL
SELECT
D.business_unit_id business_unit_id,D.org_id org_id,
'CSCH' object_type_J,'CSCH' object_type,B.SCHEDULE_ID object_id
FROM
ams_campaigns_all_b D,
ams_campaign_schedules_b B
WHERE B.campaign_id = D.campaign_id (+)
UNION ALL
SELECT
D.business_unit_id business_unit_id,D.org_id org_id,
'EVEH' object_type_J,'EVEH' object_type, D.event_header_id object_id
FROM
ams_event_headers_all_b D
UNION ALL
SELECT
D.business_unit_id business_unit_id,D.org_id org_id,
'EVEO' object_type_J, 'EVEO' object_type, D.event_offer_id object_id
FROM
ams_event_offers_all_b D
WHERE event_header_id is not null
UNION ALL
SELECT
D.business_unit_id business_unit_id,D.org_id org_id,
'EONE' object_type_J, 'EONE' object_type, D.event_offer_id object_id
FROM
ams_event_offers_all_b D
WHERE event_header_id is null
UNION ALL
SELECT
BC.business_unit_id business_unit_id,BC.org_id org_id,
'OFFR' object_type_J, 'OFFR' object_type, D.qp_list_header_id object_id
FROM
ams_campaigns_all_b BC,
ams_act_offers D
WHERE
D.arc_act_offer_used_by (+) = 'CAMP' AND D.act_offer_used_by_id =
BC.campaign_id (+) AND BC.show_campaign_flag (+) = 'Y'
UNION ALL
SELECT
BA.business_unit_id business_unit_id,BA.org_id org_id,
'DELV' object_type_J, 'CAMPDELV' object_type, D.using_object_id object_id
FROM
ams_campaigns_all_b BA,
ams_object_associations D
WHERE
D.using_object_type='DELV' AND
D.master_object_type (+) = 'CAMP' AND
D.master_object_id = BA.campaign_id (+)
UNION ALL
SELECT
BA.business_unit_id business_unit_id,BA.org_id org_id,
'DELV' object_type_J, 'CSCHDELV' object_type, D.using_object_id object_id
FROM
ams_campaigns_all_b BA,
ams_campaign_schedules_b E,
ams_object_associations D
WHERE
D.master_object_type (+) = 'CSCH' AND D.master_object_id = E.SCHEDULE_ID
(+) AND E.campaign_id = BA.campaign_id (+)
AND D.using_object_type (+) = 'DELV'
UNION ALL
SELECT
BA.business_unit_id business_unit_id,BA.org_id org_id,
'DELV' object_type_J, 'EVEHDELV' object_type, D.using_object_id object_id
FROM
ams_event_headers_all_b BA,
ams_object_associations D
WHERE D.using_object_type(+) = 'DELV'
AND D.master_object_type(+) = 'EVEH'
AND D.master_object_id = BA.event_header_id (+)
UNION ALL
SELECT
BA.business_unit_id business_unit_id,BA.org_id org_id,
'DELV' object_type_J, 'EVEODELV' object_type, D.using_object_id object_id
FROM
ams_event_offers_all_b BA,
ams_object_associations D
WHERE D.using_object_type(+) = 'DELV'
AND D.master_object_type(+) = 'EVEO'
AND D.master_object_id = BA.event_offer_id (+)
UNION ALL
SELECT
BA.business_unit_id business_unit_id,BA.org_id org_id,
'DELV' object_type_J, 'EONEDELV' object_type, D.using_object_id object_id
FROM
ams_event_offers_all_b BA,
ams_object_associations D
WHERE D.using_object_type(+) = 'DELV'
AND D.master_object_type(+) = 'EONE'
AND D.master_object_id = BA.event_offer_id (+)
) U
WHERE AU.object_type = U.object_type_J (+)
AND AU.object_id = U.object_id (+)
AND AU.fund_id (+) = AD.fund_id
AND AU.transaction_create_date (+) BETWEEN TRUNC(AD.trdate) AND TRUNC(AD.trdate) + 0.99999
) inner;
FND_FILE.put_line(fnd_file.log,'error insert fdsp daily'||sqlerrm(sqlcode));
FND_MESSAGE.Set_Name ('AMS', 'AMS_REP_INSERT_ERROR');
/* Second insert: extra records which happened before start date active */
fnd_message.set_name('BIM','BIM_R_POPULATE_TABLE');
INSERT INTO bim_r_fdsp_daily_facts fdf(
spend_transaction_id
,creation_date
,last_update_date
,created_by
,last_updated_by
,last_update_login
,fund_id
,business_unit_id
,util_org_id
,standard_discount
,accrual
,market_expense
,commited_amt
,planned_amt
,paid_amt
,delete_flag
,transaction_create_date
,load_date
,object_id
,object_type
,fis_month
,fis_qtr
,fis_year
)
SELECT
bim_r_fdsp_daily_facts_s.nextval,
sysdate,
sysdate,
l_user_id,
l_user_id,
l_user_id,
inner.fund_id,
inner.business_unit_id,
inner.org_id,
inner.standard_discount,
inner.accrual,
inner.market_expense,
inner.commited_amt,
inner.planned_amt,
inner.paid_amt,
'N',
inner.transaction_create_date,
trunc((decode(decode( to_char(inner.transaction_create_date,'MM') , to_char(next_day(inner.transaction_create_date,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM') ,'TRUE','FALSE' )
,'TRUE'
,decode(decode(inner.transaction_create_date , (next_day(inner.transaction_create_date, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))) - 7) , 'TRUE' ,'FALSE')
,'TRUE'
,inner.transaction_create_date
,'FALSE'
,next_day(inner.transaction_create_date, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))))
,'FALSE'
,decode(decode(to_char(inner.transaction_create_date,'MM'),to_char(next_day(inner.transaction_create_date,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM'),'TRUE','FALSE')
,'FALSE'
,last_day(inner.transaction_create_date))))) weekend_date
,inner.object_id
,inner.object_type
,BIM_SET_OF_BOOKS.get_fiscal_month(inner.transaction_create_date,204)
,BIM_SET_OF_BOOKS.get_fiscal_qtr(inner.transaction_create_date,204)
,BIM_SET_OF_BOOKS.get_fiscal_year(inner.transaction_create_date,204)
FROM (SELECT AD.fund_id fund_id,
U.business_unit_id business_unit_id,
U.org_id org_id,
NVL(AU.standard_discount,0) standard_discount,
NVL(AU.accrual,0) accrual,
NVL(AU.market_expense,0) market_expense,
NVL(AU.commited_amt,0) commited_amt,
NVL(AU.planned_amt,0) planned_amt,
NVL(AU.paid_amt,0) paid_amt,
AU.object_id object_id,
U.object_type object_type,
AU.transaction_create_date transaction_create_date
FROM ozf_funds_all_b AD,
bim_r_fdsp_load AU,
(SELECT
D.business_unit_id business_unit_id,D.org_id org_id,
'CAMP' object_type_J,'CAMP' object_type, D.campaign_id object_id
FROM ams_campaigns_all_b D
UNION ALL
SELECT
D.business_unit_id business_unit_id,D.org_id org_id,
'CSCH' object_type_J,'CSCH' object_type,B.SCHEDULE_ID object_id
FROM
ams_campaigns_all_b D,
ams_campaign_schedules_b B
WHERE B.campaign_id = D.campaign_id (+)
UNION ALL
SELECT
D.business_unit_id business_unit_id,D.org_id org_id,
'EVEH' object_type_J,'EVEH' object_type, D.event_header_id object_id
FROM
ams_event_headers_all_b D
UNION ALL
SELECT
D.business_unit_id business_unit_id,D.org_id org_id,
'EVEO' object_type_J, 'EVEO' object_type, D.event_offer_id object_id
FROM
ams_event_offers_all_b D
WHERE event_header_id is not null
UNION ALL
SELECT
D.business_unit_id business_unit_id,D.org_id org_id,
'EONE' object_type_J, 'EONE' object_type, D.event_offer_id object_id
FROM
ams_event_offers_all_b D
WHERE event_header_id is null
UNION ALL
SELECT
BC.business_unit_id business_unit_id,BC.org_id org_id,
'OFFR' object_type_J, 'OFFR' object_type, D.qp_list_header_id object_id
FROM
ams_campaigns_all_b BC,
ams_act_offers D
WHERE
D.arc_act_offer_used_by (+) = 'CAMP' AND D.act_offer_used_by_id =
BC.campaign_id (+) AND BC.show_campaign_flag (+) = 'Y'
UNION ALL
SELECT
BA.business_unit_id business_unit_id,BA.org_id org_id,
'DELV' object_type_J, 'CAMPDELV' object_type, D.using_object_id object_id
FROM
ams_campaigns_all_b BA,
ams_object_associations D
WHERE
D.using_object_type='DELV' AND
D.master_object_type (+) = 'CAMP' AND
D.master_object_id = BA.campaign_id (+)
UNION ALL
SELECT
BA.business_unit_id business_unit_id,BA.org_id org_id,
'DELV' object_type_J, 'CSCHDELV' object_type, D.using_object_id object_id
FROM
ams_campaigns_all_b BA,
ams_campaign_schedules_b E,
ams_object_associations D
WHERE
D.master_object_type (+) = 'CSCH' AND D.master_object_id = E.SCHEDULE_ID
(+) AND E.campaign_id = BA.campaign_id (+)
AND D.using_object_type (+) = 'DELV'
UNION ALL
SELECT
BA.business_unit_id business_unit_id,BA.org_id org_id,
'DELV' object_type_J, 'EVEHDELV' object_type, D.using_object_id object_id
FROM
ams_event_headers_all_b BA,
ams_object_associations D
WHERE D.using_object_type(+) = 'DELV'
AND D.master_object_type(+) = 'EVEH'
AND D.master_object_id = BA.event_header_id (+)
UNION ALL
SELECT
BA.business_unit_id business_unit_id,BA.org_id org_id,
'DELV' object_type_J, 'EVEODELV' object_type, D.using_object_id object_id
FROM
ams_event_offers_all_b BA,
ams_object_associations D
WHERE D.using_object_type(+) = 'DELV'
AND D.master_object_type(+) = 'EVEO'
AND D.master_object_id = BA.event_offer_id (+)
UNION ALL
SELECT
BA.business_unit_id business_unit_id,BA.org_id org_id,
'DELV' object_type_J, 'EONEDELV' object_type, D.using_object_id object_id
FROM
ams_event_offers_all_b BA,
ams_object_associations D
WHERE D.using_object_type(+) = 'DELV'
AND D.master_object_type(+) = 'EONE'
AND D.master_object_id = BA.event_offer_id (+)
) U
WHERE AD.start_date_active between p_start_datel and p_end_datel
AND AD.status_code in ('ACTIVE', 'CANCELLED', 'CLOSED')
AND AU.fund_id (+) = AD.fund_id
AND AU.object_type = U.object_type_J (+)
AND AU.object_id = U.object_id (+)
AND AU.transaction_create_date
FND_FILE.put_line(fnd_file.log,'error insert extras into fdsp daily'||sqlerrm(sqlcode));
FND_MESSAGE.Set_Name ('AMS', 'AMS_REP_INSERT_ERROR');
/* Insert into history table */
--IF l_count>0 THEN
LOG_HISTORY(
'FUND',
p_start_datel,
p_end_datel,
x_msg_count,
x_msg_data,
x_return_status) ;
the pre-approvals. So, we want to make them have the same dates by inserting into each
table the dates in one table but not in another table for the same funds.*/
BEGIN
-- dbms_output.put_line('balancing');
/* Insert into bim_r_fund_daily_facts the dates which are in bim_r_fdsp_daily_facts
but not in bim_r_fund_daily_facts. */
fnd_message.set_name('BIM','BIM_R_POPULATE_TABLE');
INSERT into bim_r_fund_daily_facts(
fund_transaction_id
,creation_date
,last_update_date
,created_by
,last_updated_by
,last_update_login
,fund_id
,parent_fund_id
,fund_number
,start_date
,end_date
,start_period
,end_period
,set_of_books_id
,fund_type
--,region
,country
,org_id
,category_id
,status
,original_budget
,transfer_in
,transfer_out
,holdback_amt
,currency_code_fc
,delete_flag
,transaction_create_date
,load_date
,fis_month
,fis_qtr
,fis_year
,business_unit_id)
SELECT
bim_r_fund_daily_facts_s.nextval,
sysdate,
sysdate,
l_user_id,
l_user_id,
l_user_id,
a.fund_id ,
a.parent_fund_id parent_fund_id,
a.fund_number fund_number,
a.start_date_active start_date,
a.end_date_active end_date,
a.start_period_name start_period,
a.end_period_name end_period,
a.set_of_books_id set_of_book_id,
a.fund_type fund_type,
a.country_id country,
a.org_id org_id,
a.category_id fund_category,
a.status_code fund_status,
a.original_budget original_amount,
0,
0,
0,
a.currency_code_fc,
'N',
b2.transaction_create_date,
trunc((decode(decode( to_char(transaction_create_date,'MM') , to_char(next_day(transaction_create_date,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM') ,'TRUE','FALSE' )
,'TRUE'
,decode(decode(transaction_create_date , (next_day(transaction_create_date, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))) - 7) , 'TRUE' ,'FALSE')
,'TRUE'
,transaction_create_date
,'FALSE'
,next_day(transaction_create_date, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))))
,'FALSE'
,decode(decode(to_char(transaction_create_date,'MM'),to_char(next_day(transaction_create_date,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM'),'TRUE','FALSE')
,'FALSE'
,last_day(transaction_create_date))))),
b2.fis_month,
b2.fis_qtr,
b2.fis_year,
a.business_unit_id
FROM ozf_funds_all_b a,
(SELECT distinct(fund_id) fund_id,
fis_month fis_month,
fis_qtr fis_qtr,
fis_year fis_year,
transaction_create_date transaction_create_date
FROM bim_r_fdsp_daily_facts fdsp
WHERE fund_id is not null
AND transaction_create_date is not null
AND (fund_id, transaction_create_date) not in
( SELECT /*+ hash_aj */ fund_id, transaction_create_date
from bim_r_fund_daily_facts b1
)) b2
WHERE b2.fund_id = a.fund_id;
FND_FILE.put_line(fnd_file.log,'error insert fund daily for balancing'||sqlerrm(sqlcode));
FND_MESSAGE.Set_Name ('AMS', 'AMS_REP_INSERT_ERROR');
/* Start of inserting into 'bim_r_fund_weekly_facts'.
Weekly table are summarized on daily tables. */
BEGIN
l_table_name :='bim_r_fund_weekly_facts';
INSERT /*+ append parallel(fwf,p_para_num) */ INTO bim_r_fund_weekly_facts fwf(
fund_transaction_id
,creation_date
,last_update_date
,created_by
,last_updated_by
,last_update_login
,fund_id
,parent_fund_id
,fund_number
,start_date
,end_date
,start_period
,end_period
,set_of_books_id
,fund_type
-- ,region
,country
,org_id
,category_id
,status
,original_budget
,transfer_in
,transfer_out
,holdback_amt
,currency_code_fc
,delete_flag
,transaction_create_date
,load_date
,fis_month
,fis_qtr
,fis_year
,business_unit_id)
SELECT /*+ parallel(inner, p_para_num) */
bim_r_fund_weekly_facts_s.nextval
,sysdate
,sysdate
,l_user_id
,l_user_id
,l_user_id
,inner.fund_id
,inner.parent_fund_id
,inner.fund_number
,inner.start_date
,inner.end_date
,inner.start_period
,inner.end_period
,inner.set_of_books_id
,inner.fund_type
--,inner.region
,inner.country
,inner.org_id
,inner.category_id
,inner.status
,inner.original_budget
,inner.transfer_in
,inner.transfer_out
,inner.holdback_amt
,inner.currency_code_fc
,'N'
,inner.load_date
,inner.load_date
,BIM_SET_OF_BOOKS.get_fiscal_month(inner.load_date,204)
,BIM_SET_OF_BOOKS.get_fiscal_qtr(inner.load_date,204)
,BIM_SET_OF_BOOKS.get_fiscal_year(inner.load_date,204)
,inner.business_unit_id
FROM(SELECT fund_id fund_id
,parent_fund_id parent_fund_id
,fund_number fund_number
,start_date start_date
,end_date end_date
,start_period start_period
,end_period end_period
,set_of_books_id set_of_books_id
,fund_type fund_type
--,region region
,country country
,org_id org_id
,business_unit_id business_unit_id
,category_id category_id
,status status
,original_budget original_budget
,SUM(transfer_in) transfer_in
,SUM(transfer_out) transfer_out
,SUM(holdback_amt) holdback_amt
,currency_code_fc currency_code_fc
,load_date load_date
FROM bim_r_fund_daily_facts
GROUP BY
fund_id
,load_date
,parent_fund_id
,fund_number
,start_date
,end_date
,start_period
,end_period
,set_of_books_id
,fund_type
-- ,region
,country
,org_id
,business_unit_id
,category_id
,status
,original_budget
,currency_code_fc) inner;
FND_FILE.put_line(fnd_file.log,'Error insertg fund weekly:'||sqlerrm(sqlcode));
FND_MESSAGE.Set_Name ('AMS', 'AMS_REP_INSERT_ERROR');
/* Insert into bim_r_fdsp_weekly_facts. */
BEGIN
l_table_name :='bim_r_fdsp_weekly_facts';
INSERT /*+ append parallel(fwf,p_para_num) */
INTO bim_r_fdsp_weekly_facts fwf(
spend_transaction_id
,creation_date
,last_update_date
,created_by
,last_updated_by
,last_update_login
,fund_id
,business_unit_id
,util_org_id
,standard_discount
,accrual
,market_expense
,commited_amt
,planned_amt
,paid_amt
,delete_flag
,transaction_create_date
,load_date
,object_id
,object_type
,fis_month
,fis_qtr
,fis_year
)
SELECT /*+ parallel(inner.p_para_num) */
bim_r_fdsp_weekly_facts_s.nextval
,sysdate
,sysdate
,l_user_id
,l_user_id
,l_user_id
,inner.fund_id
,inner.business_unit_id
,inner.util_org_id
,inner.standard_discount
,inner.accrual
,inner.market_expense
,inner.commited_amt
,inner.planned_amt
,inner.paid_amt
,'N'
,inner.load_date
,inner.load_date
,inner.object_id
,inner.object_type
,BIM_SET_OF_BOOKS.get_fiscal_month(inner.load_date,204)
,BIM_SET_OF_BOOKS.get_fiscal_qtr(inner.load_date,204)
,BIM_SET_OF_BOOKS.get_fiscal_year(inner.load_date,204)
FROM
(SELECT fund_id fund_id
,business_unit_id business_unit_id
,util_org_id util_org_id
,object_id object_id
,object_type object_type
,SUM(standard_discount) standard_discount
,SUM(accrual) accrual
,SUM(market_expense) market_expense
,SUM(commited_amt) commited_amt
,SUM(planned_amt) planned_amt
,SUM(paid_amt) paid_amt
,load_date load_date
FROM bim_r_fdsp_daily_facts
GROUP BY load_date
,business_unit_id
,object_id
,object_type
,fund_id
,util_org_id) inner;
FND_FILE.put_line(fnd_file.log,'error insert fdsp_weekly'||sqlerrm(sqlcode));
FND_MESSAGE.Set_Name ('AMS', 'AMS_REP_INSERT_ERROR');
fnd_message.set_token('proc_name', 'UPDATE_BALANCE', FALSE);
update_balance;
l_last_update_date DATE;
/* First insert: Insert transactions happened between p_start_date and p_end_date. */
INSERT /*+ append parallel(fdf,p_para_num) */
INTO bim_r_fund_daily_facts fdf(
fund_transaction_id
,creation_date
,last_update_date
,created_by
,last_updated_by
,last_update_login
,fund_id
,parent_fund_id
,fund_number
,start_date
,end_date
,start_period
,end_period
,set_of_books_id
,fund_type
--,region
,country
,org_id
,category_id
,status
,original_budget
,transfer_in
,transfer_out
,holdback_amt
,currency_code_fc
,delete_flag
,transaction_create_date
,load_date
,fis_month
,fis_qtr
,fis_year
,business_unit_id)
--,security_group_id)
SELECT /*+ parallel(inner, p_para_num) */
bim_r_fund_daily_facts_s.nextval,
sysdate,
sysdate,
l_user_id,
l_user_id,
l_user_id,
inner.fund_id,
inner.parent_fund_id,
inner.fund_number,
inner.start_date,
inner.end_date,
inner.start_period,
inner.end_period,
inner.set_of_books_id,
inner.fund_type,
--inner.region,
inner.country,
inner.org_id,
inner.category_id,
inner.status,
inner.original_budget,
inner.transfer_in,
inner.transfer_out,
inner.holdback,
inner.currency_code_fc,
'N',
inner.transaction_create_date,
inner.weekend_date,
BIM_SET_OF_BOOKS.get_fiscal_month(inner.transaction_create_date,204),
BIM_SET_OF_BOOKS.get_fiscal_qtr(inner.transaction_create_date,204),
BIM_SET_OF_BOOKS.get_fiscal_year(inner.transaction_create_date,204),
inner.business_unit_id
FROM (
SELECT fund_id fund_id,
fund_number fund_number,
start_date start_date,
end_date end_date,
start_period start_period,
end_period end_period,
category_id category_id,
status status,
fund_type fund_type,
parent_fund_id parent_fund_id,
country country,
org_id org_id,
business_unit_id business_unit_id,
set_of_books_id set_of_books_id,
currency_code_fc currency_code_fc,
original_budget original_budget,
transaction_create_date transaction_create_date,
trunc((decode(decode( to_char(transaction_create_date,'MM') , to_char(next_day(transaction_create_date,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM') ,'TRUE','FALSE' )
,'TRUE'
,decode(decode(transaction_create_date , (next_day(transaction_create_date, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))) - 7) , 'TRUE' ,'FALSE')
,'TRUE'
,transaction_create_date
,'FALSE'
,next_day(transaction_create_date, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))))
,'FALSE'
,decode(decode(to_char(transaction_create_date,'MM'),to_char(next_day(transaction_create_date,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM'),'TRUE','FALSE')
,'FALSE'
,last_day(transaction_create_date))))) weekend_date,
SUM(transfer_in) transfer_in,
SUM(transfer_out) transfer_out,
SUM(holdback) holdback
FROM
(SELECT ad.fund_id fund_id,
ad.fund_number fund_number,
ad.start_date_active start_date,
ad.end_date_active end_date,
ad.start_period_name start_period,
ad.end_period_name end_period,
ad.category_id category_id,
ad.status_code status,
ad.fund_type fund_type,
ad.parent_fund_id parent_fund_id,
ad.country_id country,
ad.org_id org_id,
ad.business_unit_id business_unit_id,
ad.set_of_books_id set_of_books_id,
ad.currency_code_fc currency_code_fc,
ad.original_budget original_budget,
trunc(bu1.approval_date) transaction_create_date,
nvl(SUM(convert_currency(bu1.approved_in_currency,nvl(bu1.approved_original_amount,0))),0) transfer_in,
0 transfer_out,
0 holdback
FROM ozf_funds_all_b ad,
ozf_act_budgets BU1
WHERE bu1.approval_date between p_start_datel and p_end_datel + 0.99999
AND ad.status_code in ('ACTIVE', 'CANCELLED', 'CLOSED')
AND bu1.transfer_type in ('TRANSFER','REQUEST')
AND bu1.status_code(+) = 'APPROVED'
AND bu1.arc_act_budget_used_by(+) = 'FUND'
AND bu1.act_budget_used_by_id(+) = ad.fund_id
AND bu1.budget_source_type(+) ='FUND'
GROUP BY ad.fund_id,
trunc(bu1.approval_date),
ad.fund_number,
ad.start_date_active ,
ad.end_date_active ,
ad.start_period_name ,
ad.end_period_name ,
ad.category_id ,
ad.status_code ,
ad.fund_type ,
ad.parent_fund_id,
ad.country_id,
ad.business_unit_id,
ad.org_id ,
ad.set_of_books_id ,
ad.currency_code_fc ,
ad.original_budget
UNION ALL
SELECT ad.fund_id fund_id,
ad.fund_number fund_number,
ad.start_date_active start_date,
ad.end_date_active end_date,
ad.start_period_name start_period,
ad.end_period_name end_period,
ad.category_id category_id,
ad.status_code status,
ad.fund_type fund_type,
ad.parent_fund_id parent_fund_id,
ad.country_id country,
ad.org_id org_id,
ad.business_unit_id business_unit_id,
ad.set_of_books_id set_of_books_id,
ad.currency_code_fc currency_code_fc,
ad.original_budget original_budget,
TRUNC(bu2.approval_date) transaction_create_date,
0 transfer_in,
nvl(SUM(decode(bu2.transfer_type,'TRANSFER', convert_currency(bu2.approved_in_currency,nvl(bu2.approved_original_amount,0)))),0)
+nvl(SUM(decode(bu2.transfer_type,'REQUEST', convert_currency(bu2.approved_in_currency,nvl(bu2.approved_original_amount,0)))),0) transfer_out,
nvl(SUM(decode(bu2.transfer_type, 'RESERVE', convert_currency(bu2.approved_in_currency,nvl(bu2.approved_original_amount,0)))),0)-
nvl(SUM(decode(bu2.transfer_type, 'RELEASE', convert_currency(bu2.approved_in_currency,nvl(bu2.approved_original_amount,0)))),0) holdback
FROM ozf_funds_all_b ad,
ozf_act_budgets BU2
WHERE bu2.approval_date between p_start_datel and p_end_datel + 0.99999
AND ad.status_code in ('ACTIVE', 'CANCELLED', 'CLOSED')
AND bu2.status_code = 'APPROVED'
AND bu2.arc_act_budget_used_by = 'FUND'
AND bu2.budget_source_type ='FUND'
AND bu2.budget_source_id = ad.fund_id
GROUP BY ad.fund_id,
trunc(bu2.approval_date) ,
ad.fund_number,
ad.start_date_active ,
ad.end_date_active ,
ad.start_period_name ,
ad.end_period_name ,
ad.category_id ,
ad.status_code ,
ad.fund_type ,
ad.parent_fund_id,
ad.country_id,
ad.org_id ,
ad.business_unit_id,
ad.set_of_books_id ,
ad.currency_code_fc ,
ad.original_budget)
GROUP BY
fund_id,
transaction_create_date,
fund_number,
start_date,
end_date,
start_period,
end_period,
category_id,
status,
fund_type,
parent_fund_id,
country,
org_id,
business_unit_id,
set_of_books_id,
currency_code_fc,
original_budget
)inner;
FND_FILE.put_line(fnd_file.log,'error insert into fund_daily'||sqlerrm(sqlcode));
FND_MESSAGE.Set_Name ('AMS', 'AMS_REP_INSERT_ERROR');
/*Second insert: For funds whose active date is between p_start_date and p_end_date, insert
transactions happened before active date */
fnd_message.set_name('BIM','BIM_R_POPULATE_TABLE');
INSERT /*+ append parallel(fdf,p_para_num) */
INTO bim_r_fund_daily_facts fdf(
fund_transaction_id
,creation_date
,last_update_date
,created_by
,last_updated_by
,last_update_login
,fund_id
,parent_fund_id
,fund_number
,start_date
,end_date
,start_period
,end_period
,set_of_books_id
,fund_type
--,region
,country
,org_id
,category_id
,status
,original_budget
,transfer_in
,transfer_out
,holdback_amt
,currency_code_fc
,delete_flag
,transaction_create_date
,load_date
,fis_month
,fis_qtr
,fis_year
,business_unit_id)
--,security_group_id)
SELECT /*+ parallel(inner, p_para_num) */
bim_r_fund_daily_facts_s.nextval,
sysdate,
sysdate,
l_user_id,
l_user_id,
l_user_id,
inner.fund_id,
inner.parent_fund_id,
inner.fund_number,
inner.start_date,
inner.end_date,
inner.start_period,
inner.end_period,
inner.set_of_books_id,
inner.fund_type,
--inner.region,
inner.country,
inner.org_id,
inner.category_id,
inner.status,
inner.original_budget,
inner.transfer_in,
inner.transfer_out,
inner.holdback,
inner.currency_code_fc,
'N',
inner.transaction_create_date,
inner.weekend_date,
BIM_SET_OF_BOOKS.get_fiscal_month(inner.transaction_create_date,204),
BIM_SET_OF_BOOKS.get_fiscal_qtr(inner.transaction_create_date,204),
BIM_SET_OF_BOOKS.get_fiscal_year(inner.transaction_create_date,204),
inner.business_unit_id
FROM (
SELECT fund_id fund_id,
fund_number fund_number,
start_date start_date,
end_date end_date,
start_period start_period,
end_period end_period,
category_id category_id,
status status,
fund_type fund_type,
parent_fund_id parent_fund_id,
country country,
org_id org_id,
business_unit_id business_unit_id,
set_of_books_id set_of_books_id,
currency_code_fc currency_code_fc,
original_budget original_budget,
transaction_create_date transaction_create_date,
trunc((decode(decode( to_char(transaction_create_date,'MM') , to_char(next_day(transaction_create_date,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM') ,'TRUE','FALSE' )
,'TRUE'
,decode(decode(transaction_create_date , (next_day(transaction_create_date, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))) - 7) , 'TRUE' ,'FALSE')
,'TRUE'
,transaction_create_date
,'FALSE'
,next_day(transaction_create_date, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))))
,'FALSE'
,decode(decode(to_char(transaction_create_date,'MM'),to_char(next_day(transaction_create_date,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM'),'TRUE','FALSE')
,'FALSE'
,last_day(transaction_create_date))))) weekend_date,
SUM(transfer_in) transfer_in,
SUM(transfer_out) transfer_out,
SUM(holdback) holdback
FROM
(SELECT ad.fund_id fund_id,
ad.fund_number fund_number,
ad.start_date_active start_date,
ad.end_date_active end_date,
ad.start_period_name start_period,
ad.end_period_name end_period,
ad.category_id category_id,
ad.status_code status,
ad.fund_type fund_type,
ad.parent_fund_id parent_fund_id,
ad.country_id country,
ad.org_id org_id,
ad.business_unit_id business_unit_id,
ad.set_of_books_id set_of_books_id,
ad.currency_code_fc currency_code_fc,
ad.original_budget original_budget,
trunc(bu1.approval_date) transaction_create_date,
nvl(SUM(convert_currency(bu1.approved_in_currency,nvl(bu1.approved_original_amount,0))),0) transfer_in,
0 transfer_out,
0 holdback
FROM ozf_funds_all_b ad,
ozf_act_budgets BU1
WHERE bu1.approval_date < p_start_datel
AND ad.status_code in ('ACTIVE', 'CANCELLED', 'CLOSED')
AND ad.start_date_active between p_start_datel and p_end_datel
AND bu1.transfer_type in ('TRANSFER','REQUEST')
AND bu1.status_code = 'APPROVED'
AND bu1.arc_act_budget_used_by = 'FUND'
AND bu1.act_budget_used_by_id = ad.fund_id
AND bu1.budget_source_type ='FUND'
GROUP BY ad.fund_id,
trunc(bu1.approval_date),
ad.fund_number,
ad.start_date_active ,
ad.end_date_active ,
ad.start_period_name ,
ad.end_period_name ,
ad.category_id ,
ad.status_code ,
ad.fund_type ,
ad.parent_fund_id,
ad.country_id,
ad.business_unit_id,
ad.org_id ,
ad.set_of_books_id ,
ad.currency_code_fc ,
ad.original_budget
UNION ALL
SELECT ad.fund_id fund_id,
ad.fund_number fund_number,
ad.start_date_active start_date,
ad.end_date_active end_date,
ad.start_period_name start_period,
ad.end_period_name end_period,
ad.category_id category_id,
ad.status_code status,
ad.fund_type fund_type,
ad.parent_fund_id parent_fund_id,
ad.country_id country,
ad.org_id org_id,
ad.business_unit_id business_unit_id,
ad.set_of_books_id set_of_books_id,
ad.currency_code_fc currency_code_fc,
ad.original_budget original_budget,
TRUNC(bu2.approval_date) transaction_create_date,
0 transfer_in,
nvl(SUM(decode(bu2.transfer_type,'TRANSFER', convert_currency(bu2.approved_in_currency,nvl(bu2.approved_original_amount,0)))),0)
+nvl(SUM(decode(bu2.transfer_type,'REQUEST', convert_currency(bu2.approved_in_currency,nvl(bu2.approved_original_amount,0)))),0) transfer_out,
nvl(SUM(decode(bu2.transfer_type, 'RESERVE', convert_currency(bu2.approved_in_currency,nvl(bu2.approved_original_amount,0)))),0)-
nvl(SUM(decode(bu2.transfer_type, 'RELEASE', convert_currency(bu2.approved_in_currency,nvl(bu2.approved_original_amount,0)))),0) holdback
FROM ozf_funds_all_b ad,
ozf_act_budgets BU2
WHERE bu2.approval_date < p_start_datel
AND ad.status_code in ('ACTIVE', 'CANCELLED', 'CLOSED')
AND ad.start_date_active between p_start_datel and p_end_datel
AND bu2.status_code = 'APPROVED'
AND bu2.arc_act_budget_used_by = 'FUND'
AND bu2.budget_source_type ='FUND'
AND bu2.budget_source_id = ad.fund_id
GROUP BY ad.fund_id,
trunc(bu2.approval_date) ,
ad.fund_number,
ad.start_date_active ,
ad.end_date_active ,
ad.start_period_name ,
ad.end_period_name ,
ad.category_id ,
ad.status_code ,
ad.fund_type ,
ad.parent_fund_id,
ad.country_id,
ad.org_id ,
ad.business_unit_id,
ad.set_of_books_id ,
ad.currency_code_fc ,
ad.original_budget)
GROUP BY
fund_id,
transaction_create_date,
fund_number,
start_date,
end_date,
start_period,
end_period,
category_id,
status,
fund_type,
parent_fund_id,
country,
org_id,
business_unit_id,
set_of_books_id,
currency_code_fc,
original_budget
)inner;
FND_FILE.put_line(fnd_file.log,'error insert into fund_daily for transactions happened b4 start date'||sqlerrm(sqlcode));
FND_MESSAGE.Set_Name ('AMS', 'AMS_REP_INSERT_ERROR');
/* insert dummy 0s for dates between p_start_date/start_date_active and p_end_date/end_date_active,
but has no transactions.*/
fnd_message.set_name('BIM','BIM_R_POPULATE_TABLE');
INSERT /*+ append parallel(fdf,p_para_num) */
INTO bim_r_fund_daily_facts fdf(
fund_transaction_id
,creation_date
,last_update_date
,created_by
,last_updated_by
,last_update_login
,fund_id
,parent_fund_id
,fund_number
,start_date
,end_date
,start_period
,end_period
,set_of_books_id
,fund_type
--,region
,country
,org_id
,category_id
,status
,original_budget
,transfer_in
,transfer_out
,holdback_amt
,currency_code_fc
,delete_flag
,transaction_create_date
,load_date
,fis_month
,fis_qtr
,fis_year
,business_unit_id)
--,security_group_id)
SELECT /*+ parallel(inner, p_para_num) */
bim_r_fund_daily_facts_s.nextval,
sysdate,
sysdate,
l_user_id,
l_user_id,
l_user_id,
inner.fund_id,
inner.parent_fund_id,
inner.fund_number,
inner.start_date,
inner.end_date,
inner.start_period,
inner.end_period,
inner.set_of_books_id,
inner.fund_type,
--inner.region,
inner.country,
inner.org_id,
inner.category_id,
inner.status,
inner.original_budget,
0,
0,
0,
inner.currency_code_fc,
'N',
inner.trdate,
trunc((decode(decode( to_char(inner.trdate,'MM') , to_char(next_day(inner.trdate,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM') ,'TRUE','FALSE' )
,'TRUE'
,decode(decode(inner.trdate , (next_day(inner.trdate, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))) - 7) , 'TRUE' ,'FALSE')
,'TRUE'
,inner.trdate
,'FALSE'
,next_day(inner.trdate, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))))
,'FALSE'
,decode(decode(to_char(inner.trdate,'MM'),to_char(next_day(inner.trdate,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM'),'TRUE','FALSE')
,'FALSE'
,last_day(inner.trdate))))) ,
BIM_SET_OF_BOOKS.get_fiscal_month(inner.trdate,204),
BIM_SET_OF_BOOKS.get_fiscal_qtr(inner.trdate,204),
BIM_SET_OF_BOOKS.get_fiscal_year(inner.trdate,204),
inner.business_unit_id
FROM (
SELECT distinct a.fund_id fund_id,
a.fund_number fund_number,
a.start_date start_date,
a.end_date end_date,
a.start_period start_period,
a.end_period end_period,
a.category_id category_id,
a.status status,
a.fund_type fund_type,
a.parent_fund_id parent_fund_id,
a.business_unit_id business_unit_id,
a.country country,
a.org_id org_id,
a.set_of_books_id set_of_books_id,
a.currency_code_fc currency_code_fc,
a.original_budget original_budget,
trunc(b.trdate) trdate
FROM bim_r_fund_daily_facts a,
bim_intl_dates b
WHERE b.trdate between p_start_datel and p_end_datel+0.99999
AND b.trdate between a.start_date and nvl(a.end_date,p_end_datel)+0.99999
AND (a.fund_id, trunc(b.trdate)) not in (select c.fund_id, c.transaction_create_date
from bim_r_fund_daily_facts c
where c.fund_id = a.fund_id
and c.transaction_create_date = trunc(b.trdate))) inner;
FND_FILE.put_line(fnd_file.log,'error insert into fund_daily for missing date'||sqlerrm(sqlcode));
FND_MESSAGE.Set_Name ('AMS', 'AMS_REP_INSERT_ERROR');
/* insert dummy 0s for funds created between p_start_datel and p_end_datel,
and in those dates between p_start_date/start_date_active and p_end_date/end_date_active,
but has no transactions.*/
fnd_message.set_name('BIM','BIM_R_POPULATE_TABLE');
INSERT /*+ append parallel(fdf,p_para_num) */
INTO bim_r_fund_daily_facts fdf(
fund_transaction_id
,creation_date
,last_update_date
,created_by
,last_updated_by
,last_update_login
,fund_id
,parent_fund_id
,fund_number
,start_date
,end_date
,start_period
,end_period
,set_of_books_id
,fund_type
--,region
,country
,org_id
,category_id
,status
,original_budget
,transfer_in
,transfer_out
,holdback_amt
,currency_code_fc
,delete_flag
,transaction_create_date
,load_date
,fis_month
,fis_qtr
,fis_year
,business_unit_id)
--,security_group_id)
SELECT /*+ parallel(inner, p_para_num) */
bim_r_fund_daily_facts_s.nextval,
sysdate,
sysdate,
l_user_id,
l_user_id,
l_user_id,
inner.fund_id,
inner.parent_fund_id,
inner.fund_number,
inner.start_date,
inner.end_date,
inner.start_period,
inner.end_period,
inner.set_of_books_id,
inner.fund_type,
--inner.region,
inner.country,
inner.org_id,
inner.category_id,
inner.status,
inner.original_budget,
0,
0,
0,
inner.currency_code_fc,
'N',
inner.trdate,
trunc((decode(decode( to_char(inner.trdate,'MM') , to_char(next_day(inner.trdate,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM') ,'TRUE','FALSE' )
,'TRUE'
,decode(decode(inner.trdate , (next_day(inner.trdate, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))) - 7) , 'TRUE' ,'FALSE')
,'TRUE'
,inner.trdate
,'FALSE'
,next_day(inner.trdate, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))))
,'FALSE'
,decode(decode(to_char(inner.trdate,'MM'),to_char(next_day(inner.trdate,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM'),'TRUE','FALSE')
,'FALSE'
,last_day(inner.trdate))))) ,
BIM_SET_OF_BOOKS.get_fiscal_month(inner.trdate,204),
BIM_SET_OF_BOOKS.get_fiscal_qtr(inner.trdate,204),
BIM_SET_OF_BOOKS.get_fiscal_year(inner.trdate,204),
inner.business_unit_id
FROM (
SELECT ad.fund_id fund_id,
ad.fund_number fund_number,
ad.start_date_active start_date,
ad.end_date_active end_date,
ad.start_period_name start_period,
ad.end_period_name end_period,
ad.category_id category_id,
ad.status_code status,
ad.fund_type fund_type,
ad.parent_fund_id parent_fund_id,
ad.country_id country,
ad.org_id org_id,
ad.business_unit_id business_unit_id,
ad.set_of_books_id set_of_books_id,
ad.currency_code_fc currency_code_fc,
ad.original_budget original_budget,
trunc(b.trdate) trdate,
0 transfer_in,
0 transfer_out,
0 holdback
FROM ozf_funds_all_b ad,
bim_intl_dates b
WHERE ad.status_code in ('ACTIVE', 'CANCELLED', 'CLOSED')
AND ad.start_date_active between p_start_datel and p_end_datel
AND b.trdate between p_start_datel and p_end_datel+0.99999
AND b.trdate between ad.start_date_active and nvl(ad.end_date_active,p_end_datel)+0.99999
AND (ad.fund_id, trunc(b.trdate)) not in (select c.fund_id, c.transaction_create_date
from bim_r_fund_daily_facts c
where c.fund_id = ad.fund_id
and c.transaction_create_date = trunc(b.trdate))) inner;
FND_FILE.put_line(fnd_file.log,'error insert into fund_daily for missing date 2'||sqlerrm(sqlcode));
FND_MESSAGE.Set_Name ('AMS', 'AMS_REP_INSERT_ERROR');
/* Inserting into 'bim_r_fdsp_load' all the objects information */
l_table_name :='bim_r_fdsp_daily_facts';
INSERT /*+ append parallel(bfl,p_para_num) */
INTO bim_r_fdsp_load bfl(
spend_transaction_id
,creation_date
,last_update_date
,created_by
,last_updated_by
,last_update_login
,fund_id
,business_unit_id
,util_org_id
,standard_discount
,accrual
,market_expense
,commited_amt
,planned_amt
,paid_amt
,delete_flag
,transaction_create_date
,load_date
,object_id
,object_type)
SELECT /*+ parallel(act_util, p_para_num) */
0,
sysdate,
sysdate,
-1,
-1,
-1,
act_util.fund_id,
0,
0,
act_util.standard_discount,
act_util.accrual,
act_util.market_expense,
act_util.commited_amt,
act_util.planned_amt,
act_util.paid_amt,
'Y',
act_util.creation_date,
sysdate,
act_util.object_id,
act_util.object_type
FROM (SELECT fund_id fund_id,
object_id object_id,
object_type object_type,
creation_date creation_date,
SUM(nvl(planned_amt,0)) planned_amt,
SUM(nvl(commited_amt,0)) commited_amt,
SUM(nvl(standard_discount,0)) standard_discount,
SUM(nvl(accrual,0)) accrual,
SUM(nvl(market_expense,0)) market_expense,
SUM(nvl(paid_amt,0)) paid_amt
FROM (
SELECT budget_source_id fund_id,
act_budget_used_by_id object_id,
arc_act_budget_used_by object_type,
trunc(nvl(request_date,creation_date)) creation_date,
SUM(convert_currency(request_currency, nvl(request_amount,0))) planned_amt,
0 commited_amt,
0 standard_discount,
0 accrual,
0 market_expense,
0 paid_amt
FROM ozf_act_budgets
WHERE budget_source_type ='FUND'
AND status_code ='PENDING'
AND ARC_ACT_BUDGET_USED_BY <> 'FUND'
GROUP BY trunc(nvl(request_date ,creation_date)),
budget_source_id,act_budget_used_by_id,
arc_act_budget_used_by
UNION ALL
SELECT budget_source_id fund_id,
act_budget_used_by_id object_id,
arc_act_budget_used_by object_type,
trunc(nvl(approval_date,last_update_date)) creation_date,
0-SUM(convert_currency(request_currency, nvl(request_amount,0))) planned_amt,
SUM(convert_currency(approved_in_currency,nvl(approved_original_amount,0))) commited_amt,
0 standard_discount,
0 accrual,
0 market_expense,
0 paid_amt
FROM ozf_act_budgets
WHERE budget_source_type ='FUND'
AND ARC_ACT_BUDGET_USED_BY <> 'FUND'
AND status_code ='APPROVED'
GROUP BY trunc(nvl(approval_date,last_update_date)),
budget_source_id,act_budget_used_by_id,
arc_act_budget_used_by
UNION ALL
SELECT act_budget_used_by_id fund_id,
budget_source_id object_id,
budget_source_type object_type,
trunc(nvl(approval_date,last_update_date)) creation_date,
0 planned_amt,
0-SUM(convert_currency(approved_in_currency,nvl(approved_original_amount,0))) commited_amt,
0 standard_discount,
0 accrual,
0 market_expense,
0 paid_amt
FROM ozf_act_budgets
WHERE arc_act_budget_used_by ='FUND'
AND budget_source_type<>'FUND'
AND status_code ='APPROVED'
GROUP BY trunc(nvl(approval_date,last_update_date)),
act_budget_used_by_id, budget_source_id,
budget_source_type
UNION ALL
SELECT fund_id fund_id,
plan_id object_id,
plan_type object_type,
trunc(creation_date) creation_date,
0 planned_amt,
0 commited_amt,
SUM(decode(component_type,'OFFR',decode(utilization_type, 'UTILIZED',convert_currency(currency_code,nvl(amount,0)), 0),0)) standard_discount,
SUM(decode(component_type,'OFFR', decode(utilization_type, 'ACCRUAL', convert_currency(currency_code,nvl(amount,0)), 0),0) +
decode(component_type,'OFFR', decode(utilization_type, 'ADJUSTMENT', convert_currency(currency_code,nvl(amount,0)), 0),0)) accrual,
SUM(decode(component_type,'OFFR',0, decode(utilization_type, 'UTILIZED', convert_currency(currency_code,nvl(amount,0)), 0))) market_expense,
sum(decode(component_type,'OFFR',0,convert_currency(currency_code,(nvl(amount,0)-NVL(amount_remaining,0))))) paid_amt
FROM ozf_funds_utilized_all_b
WHERE utilization_type in ('UTILIZED','ACCRUAL','ADJUSTMENT')
GROUP BY trunc(creation_date),fund_id,plan_id,plan_type
)
GROUP BY creation_date, fund_id, object_id,object_type
) act_util;
FND_FILE.put_line(fnd_file.log,'error insert fdsp daily'||sqlerrm(sqlcode));
FND_MESSAGE.Set_Name ('AMS', 'AMS_REP_INSERT_ERROR');
END ; --end of insertion into bim_r_fdsp_load.
/* First insert:Insert into 'bim_r_fdsp_daily_facts' the transactions happend between
p_start_date and p_end_date */
fnd_message.set_name('BIM','BIM_R_POPULATE_TABLE');
INSERT /*+ append parallel(fdf,p_para_num) */
INTO bim_r_fdsp_daily_facts fdf(
spend_transaction_id
,creation_date
,last_update_date
,created_by
,last_updated_by
,last_update_login
,fund_id
,business_unit_id
,util_org_id
,standard_discount
,accrual
,market_expense
,commited_amt
,planned_amt
,paid_amt
,delete_flag
,transaction_create_date
,load_date
,object_id
,object_type
,fis_month
,fis_qtr
,fis_year
)
SELECT /*+ parallel(inner, p_para_num) */
bim_r_fdsp_daily_facts_s.nextval,
sysdate,
sysdate,
l_user_id,
l_user_id,
l_user_id,
inner.fund_id,
inner.business_unit_id,
inner.org_id,
inner.standard_discount,
inner.accrual,
inner.market_expense,
inner.commited_amt,
inner.planned_amt,
inner.paid_amt,
'N',
inner.transaction_create_date,
trunc((decode(decode( to_char(inner.transaction_create_date,'MM') , to_char(next_day(inner.transaction_create_date,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM') ,'TRUE','FALSE' )
,'TRUE'
,decode(decode(inner.transaction_create_date , (next_day(inner.transaction_create_date, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))) - 7) , 'TRUE' ,'FALSE')
,'TRUE'
,inner.transaction_create_date
,'FALSE'
,next_day(inner.transaction_create_date, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))))
,'FALSE'
,decode(decode(to_char(inner.transaction_create_date,'MM'),to_char(next_day(inner.transaction_create_date,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM'),'TRUE','FALSE')
,'FALSE'
,last_day(inner.transaction_create_date))))) weekend_date
,inner.object_id
,inner.object_type
,BIM_SET_OF_BOOKS.get_fiscal_month(inner.transaction_create_date,204)
,BIM_SET_OF_BOOKS.get_fiscal_qtr(inner.transaction_create_date,204)
,BIM_SET_OF_BOOKS.get_fiscal_year(inner.transaction_create_date,204)
FROM (SELECT AD.fund_id fund_id,
U.business_unit_id business_unit_id,
U.org_id org_id,
NVL(AU.standard_discount,0) standard_discount,
NVL(AU.accrual,0) accrual,
NVL(AU.market_expense,0) market_expense,
NVL(AU.commited_amt,0) commited_amt,
NVL(AU.planned_amt,0) planned_amt,
NVL(AU.paid_amt,0) paid_amt,
AU.object_id object_id,
U.object_type object_type,
AU.transaction_create_date transaction_create_date
FROM ozf_funds_all_b AD,
bim_r_fdsp_load AU,
(SELECT
D.business_unit_id business_unit_id,D.org_id org_id,
'CAMP' object_type_J,'CAMP' object_type, D.campaign_id object_id
FROM ams_campaigns_all_b D
UNION ALL
SELECT
D.business_unit_id business_unit_id,D.org_id org_id,
'CSCH' object_type_J,'CSCH' object_type,B.SCHEDULE_ID object_id
FROM
ams_campaigns_all_b D,
ams_campaign_schedules_b B
WHERE B.campaign_id = D.campaign_id (+)
UNION ALL
SELECT
D.business_unit_id business_unit_id,D.org_id org_id,
'EVEH' object_type_J,'EVEH' object_type, D.event_header_id object_id
FROM
ams_event_headers_all_b D
UNION ALL
SELECT
D.business_unit_id business_unit_id,D.org_id org_id,
'EVEO' object_type_J, 'EVEO' object_type, D.event_offer_id object_id
FROM
ams_event_offers_all_b D
WHERE event_header_id is not null
UNION ALL
SELECT
D.business_unit_id business_unit_id,D.org_id org_id,
'EONE' object_type_J, 'EONE' object_type, D.event_offer_id object_id
FROM
ams_event_offers_all_b D
WHERE event_header_id is null
UNION ALL
SELECT
BC.business_unit_id business_unit_id,BC.org_id org_id,
'OFFR' object_type_J, 'OFFR' object_type, D.qp_list_header_id object_id
FROM
ams_campaigns_all_b BC,
ams_act_offers D
WHERE
D.arc_act_offer_used_by (+) = 'CAMP' AND D.act_offer_used_by_id =
BC.campaign_id (+) AND BC.show_campaign_flag (+) = 'Y'
UNION ALL
SELECT
BA.business_unit_id business_unit_id,BA.org_id org_id,
'DELV' object_type_J, 'CAMPDELV' object_type, D.using_object_id object_id
FROM
ams_campaigns_all_b BA,
ams_object_associations D
WHERE
D.using_object_type='DELV' AND
D.master_object_type (+) = 'CAMP' AND
D.master_object_id = BA.campaign_id (+)
UNION ALL
SELECT
BA.business_unit_id business_unit_id,BA.org_id org_id,
'DELV' object_type_J, 'CSCHDELV' object_type, D.using_object_id object_id
FROM
ams_campaigns_all_b BA,
ams_campaign_schedules_b E,
ams_object_associations D
WHERE
D.master_object_type (+) = 'CSCH' AND D.master_object_id = E.SCHEDULE_ID
(+) AND E.campaign_id = BA.campaign_id (+)
AND D.using_object_type (+) = 'DELV'
UNION ALL
SELECT
BA.business_unit_id business_unit_id,BA.org_id org_id,
'DELV' object_type_J, 'EVEHDELV' object_type, D.using_object_id object_id
FROM
ams_event_headers_all_b BA,
ams_object_associations D
WHERE D.using_object_type(+) = 'DELV'
AND D.master_object_type(+) = 'EVEH'
AND D.master_object_id = BA.event_header_id (+)
UNION ALL
SELECT
BA.business_unit_id business_unit_id,BA.org_id org_id,
'DELV' object_type_J, 'EVEODELV' object_type, D.using_object_id object_id
FROM
ams_event_offers_all_b BA,
ams_object_associations D
WHERE D.using_object_type(+) = 'DELV'
AND D.master_object_type(+) = 'EVEO'
AND D.master_object_id = BA.event_offer_id (+)
UNION ALL
SELECT
BA.business_unit_id business_unit_id,BA.org_id org_id,
'DELV' object_type_J, 'EONEDELV' object_type, D.using_object_id object_id
FROM
ams_event_offers_all_b BA,
ams_object_associations D
WHERE D.using_object_type(+) = 'DELV'
AND D.master_object_type(+) = 'EONE'
AND D.master_object_id = BA.event_offer_id (+)
) U
WHERE AD.status_code IN ( 'ACTIVE','CANCELLED','CLOSED' )
AND AU.transaction_create_date BETWEEN p_start_datel AND p_end_datel + 0.99999
AND AU.object_type = U.object_type_J (+)
AND AU.object_id = U.object_id (+)
AND AU.fund_id = AD.fund_id
) inner;
FND_FILE.put_line(fnd_file.log,'first insert:error insert fdsp daily'||sqlerrm(sqlcode));
FND_MESSAGE.Set_Name ('AMS', 'AMS_REP_INSERT_ERROR');
/* Second insert: Insert extra records which happened before start date active */
fnd_message.set_name('BIM','BIM_R_POPULATE_TABLE');
INSERT /*+ append parallel(fdf,p_para_num) */
INTO bim_r_fdsp_daily_facts fdf(
spend_transaction_id
,creation_date
,last_update_date
,created_by
,last_updated_by
,last_update_login
,fund_id
,business_unit_id
,util_org_id
,standard_discount
,accrual
,market_expense
,commited_amt
,planned_amt
,paid_amt
,delete_flag
,transaction_create_date
,load_date
,object_id
,object_type
,fis_month
,fis_qtr
,fis_year
)
SELECT /*+ parallel(inner, p_para_num) */
bim_r_fdsp_daily_facts_s.nextval,
sysdate,
sysdate,
l_user_id,
l_user_id,
l_user_id,
inner.fund_id,
inner.business_unit_id,
inner.org_id,
inner.standard_discount,
inner.accrual,
inner.market_expense,
inner.commited_amt,
inner.planned_amt,
inner.paid_amt,
'N',
inner.transaction_create_date,
trunc((decode(decode( to_char(inner.transaction_create_date,'MM') , to_char(next_day(inner.transaction_create_date,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM') ,'TRUE','FALSE' )
,'TRUE'
,decode(decode(inner.transaction_create_date , (next_day(inner.transaction_create_date, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))) - 7) , 'TRUE' ,'FALSE')
,'TRUE'
,inner.transaction_create_date
,'FALSE'
,next_day(inner.transaction_create_date, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))))
,'FALSE'
,decode(decode(to_char(inner.transaction_create_date,'MM'),to_char(next_day(inner.transaction_create_date,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM'),'TRUE','FALSE')
,'FALSE'
,last_day(inner.transaction_create_date))))) weekend_date
,inner.object_id
,inner.object_type
,BIM_SET_OF_BOOKS.get_fiscal_month(inner.transaction_create_date,204)
,BIM_SET_OF_BOOKS.get_fiscal_qtr(inner.transaction_create_date,204)
,BIM_SET_OF_BOOKS.get_fiscal_year(inner.transaction_create_date,204)
FROM (SELECT AD.fund_id fund_id,
U.business_unit_id business_unit_id,
U.org_id org_id,
NVL(AU.standard_discount,0) standard_discount,
NVL(AU.accrual,0) accrual,
NVL(AU.market_expense,0) market_expense,
NVL(AU.commited_amt,0) commited_amt,
NVL(AU.planned_amt,0) planned_amt,
NVL(AU.paid_amt,0) paid_amt,
AU.object_id object_id,
U.object_type object_type,
AU.transaction_create_date transaction_create_date
FROM ozf_funds_all_b AD,
bim_r_fdsp_load AU,
(SELECT
D.business_unit_id business_unit_id,D.org_id org_id,
'CAMP' object_type_J,'CAMP' object_type, D.campaign_id object_id
FROM ams_campaigns_all_b D
UNION ALL
SELECT
D.business_unit_id business_unit_id,D.org_id org_id,
'CSCH' object_type_J,'CSCH' object_type,B.SCHEDULE_ID object_id
FROM
ams_campaigns_all_b D,
ams_campaign_schedules_b B
WHERE B.campaign_id = D.campaign_id (+)
UNION ALL
SELECT
D.business_unit_id business_unit_id,D.org_id org_id,
'EVEH' object_type_J,'EVEH' object_type, D.event_header_id object_id
FROM
ams_event_headers_all_b D
UNION ALL
SELECT
D.business_unit_id business_unit_id,D.org_id org_id,
'EVEO' object_type_J, 'EVEO' object_type, D.event_offer_id object_id
FROM
ams_event_offers_all_b D
WHERE event_header_id is not null
UNION ALL
SELECT
D.business_unit_id business_unit_id,D.org_id org_id,
'EONE' object_type_J, 'EONE' object_type, D.event_offer_id object_id
FROM
ams_event_offers_all_b D
WHERE event_header_id is null
UNION ALL
SELECT
BC.business_unit_id business_unit_id,BC.org_id org_id,
'OFFR' object_type_J, 'OFFR' object_type, D.qp_list_header_id object_id
FROM
ams_campaigns_all_b BC,
ams_act_offers D
WHERE
D.arc_act_offer_used_by (+) = 'CAMP' AND D.act_offer_used_by_id =
BC.campaign_id (+) AND BC.show_campaign_flag (+) = 'Y'
UNION ALL
SELECT
BA.business_unit_id business_unit_id,BA.org_id org_id,
'DELV' object_type_J, 'CAMPDELV' object_type, D.using_object_id object_id
FROM
ams_campaigns_all_b BA,
ams_object_associations D
WHERE
D.using_object_type='DELV' AND
D.master_object_type (+) = 'CAMP' AND
D.master_object_id = BA.campaign_id (+)
UNION ALL
SELECT
BA.business_unit_id business_unit_id,BA.org_id org_id,
'DELV' object_type_J, 'CSCHDELV' object_type, D.using_object_id object_id
FROM
ams_campaigns_all_b BA,
ams_campaign_schedules_b E,
ams_object_associations D
WHERE
D.master_object_type (+) = 'CSCH' AND D.master_object_id = E.SCHEDULE_ID
(+) AND E.campaign_id = BA.campaign_id (+)
AND D.using_object_type (+) = 'DELV'
UNION ALL
SELECT
BA.business_unit_id business_unit_id,BA.org_id org_id,
'DELV' object_type_J, 'EVEHDELV' object_type, D.using_object_id object_id
FROM
ams_event_headers_all_b BA,
ams_object_associations D
WHERE D.using_object_type(+) = 'DELV'
AND D.master_object_type(+) = 'EVEH'
AND D.master_object_id = BA.event_header_id (+)
UNION ALL
SELECT
BA.business_unit_id business_unit_id,BA.org_id org_id,
'DELV' object_type_J, 'EVEODELV' object_type, D.using_object_id object_id
FROM
ams_event_offers_all_b BA,
ams_object_associations D
WHERE D.using_object_type(+) = 'DELV'
AND D.master_object_type(+) = 'EVEO'
AND D.master_object_id = BA.event_offer_id (+)
UNION ALL
SELECT
BA.business_unit_id business_unit_id,BA.org_id org_id,
'DELV' object_type_J, 'EONEDELV' object_type, D.using_object_id object_id
FROM
ams_event_offers_all_b BA,
ams_object_associations D
WHERE D.using_object_type(+) = 'DELV'
AND D.master_object_type(+) = 'EONE'
AND D.master_object_id = BA.event_offer_id (+)
) U
WHERE AD.status_code IN ( 'ACTIVE','CANCELLED','CLOSED' )
AND AD.start_date_active BETWEEN p_start_datel AND p_end_datel + 0.99999
AND AU.transaction_create_date < p_start_datel
AND AU.object_type = U.object_type_J (+)
AND AU.object_id = U.object_id (+)
AND AU.fund_id = AD.fund_id
) inner;
FND_FILE.put_line(fnd_file.log,'error insert extras into fdsp daily'||sqlerrm(sqlcode));
FND_MESSAGE.Set_Name ('AMS', 'AMS_REP_INSERT_ERROR');
/* Insert dummy 0s for the dates between p_start_date/start_date_active and p_end_date/end_date_active,
but has no transactions. */
fnd_message.set_name('BIM','BIM_R_POPULATE_TABLE');
INSERT /*+ append parallel(fdf,p_para_num) */
INTO bim_r_fdsp_daily_facts fdf(
spend_transaction_id
,creation_date
,last_update_date
,created_by
,last_updated_by
,last_update_login
,fund_id
,business_unit_id
,util_org_id
,standard_discount
,accrual
,market_expense
,commited_amt
,planned_amt
,paid_amt
,delete_flag
,transaction_create_date
,load_date
,object_id
,object_type
,fis_month
,fis_qtr
,fis_year
)
SELECT /*+ parallel(inner, p_para_num) */
bim_r_fdsp_daily_facts_s.nextval,
sysdate,
sysdate,
l_user_id,
l_user_id,
l_user_id,
inner.fund_id,
null,
null,
0,
0,
0,
0,
0,
0,
'N',
inner.trdate,
trunc((decode(decode( to_char(inner.trdate,'MM') , to_char(next_day(inner.trdate,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM') ,'TRUE','FALSE' )
,'TRUE'
,decode(decode(inner.trdate , (next_day(inner.trdate, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))) - 7) , 'TRUE' ,'FALSE')
,'TRUE'
,inner.trdate
,'FALSE'
,next_day(inner.trdate, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))))
,'FALSE'
,decode(decode(to_char(inner.trdate,'MM'),to_char(next_day(inner.trdate,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM'),'TRUE','FALSE')
,'FALSE'
,last_day(inner.trdate))))) weekend_date
,null
,null
,BIM_SET_OF_BOOKS.get_fiscal_month(inner.trdate,204)
,BIM_SET_OF_BOOKS.get_fiscal_qtr(inner.trdate,204)
,BIM_SET_OF_BOOKS.get_fiscal_year(inner.trdate,204)
FROM (
SELECT distinct a.fund_id fund_id,
TRUNC(b.trdate) trdate
FROM bim_r_fdsp_daily_facts a,
ozf_funds_all_b f,
bim_intl_dates b
WHERE b.trdate between greatest(p_start_datel, f.start_date_active)
and least(p_end_datel, nvl(f.end_date_active,p_end_datel))
and f.fund_id = a.fund_id
and (a.fund_id, TRUNC(b.trdate)) not in (select c.fund_id, c.transaction_create_date
from bim_r_fdsp_daily_facts c
where c.fund_id = a.fund_id
and c.transaction_create_date = TRUNC(b.trdate)) )inner;
FND_FILE.put_line(fnd_file.log,'error insert fdsp daily for missing dates'||sqlerrm(sqlcode));
FND_MESSAGE.Set_Name ('AMS', 'AMS_REP_INSERT_ERROR');
/* Insert into bim_r_fdsp_daily_facts the dates which are in bim_r_fund_daily_facts
but not in bim_r_fdsp_daily_facts. */
fnd_message.set_name('BIM','BIM_R_POPULATE_TABLE');
BEGIN --insert into fdsp for balancing
l_table_name :='bim_r_fdsp_daily_facts';
INSERT /*+ append parallel(fdf,p_para_num) */
INTO bim_r_fdsp_daily_facts fdf(
spend_transaction_id
,creation_date
,last_update_date
,created_by
,last_updated_by
,last_update_login
,fund_id
,business_unit_id
,util_org_id
,standard_discount
,accrual
,market_expense
,commited_amt
,planned_amt
,paid_amt
,delete_flag
,transaction_create_date
,load_date
,object_id
,object_type
,fis_month
,fis_qtr
,fis_year
)
SELECT /*+ parallel(inner, p_para_num) */
bim_r_fdsp_daily_facts_s.nextval,
sysdate,
sysdate,
l_user_id,
l_user_id,
l_user_id,
b1.fund_id,
b1.business_unit_id,
b1.util_org_id,
0,
0,
0,
0,
0,
0,
'N',
b1.transaction_create_date,
b1.load_date,
b1.object_id,
b1.object_type,
b1.fis_month,
b1.fis_qtr,
b1.fis_year
FROM bim_r_fdsp_daily_facts b1,
(SELECT distinct fund_id fund_id,
transaction_create_date transaction_create_date
FROM bim_r_fund_daily_facts fd
WHERE (fund_id, transaction_create_date) not in
( SELECT /*+ hash_aj */ fund_id, transaction_create_date
from bim_r_fdsp_daily_facts
where fund_id is not null
and transaction_create_date is not null
)) b2
WHERE b1.fund_id = b2.fund_id
AND b1.transaction_create_date = b2.transaction_create_date ;
FND_FILE.put_line(fnd_file.log,'error insert fdsp daily for balancing'||sqlerrm(sqlcode));
FND_MESSAGE.Set_Name ('AMS', 'AMS_REP_INSERT_ERROR');
/* Insert into bim_r_fund_daily_facts dates which are in bim_r_fdsp_daily_facts
but not in bim_r_fund_daily_facts */
BEGIN
l_table_name :='bim_r_fund_daily_facts';
INSERT into bim_r_fund_daily_facts(
fund_transaction_id
,creation_date
,last_update_date
,created_by
,last_updated_by
,last_update_login
,fund_id
,parent_fund_id
,fund_number
,start_date
,end_date
,start_period
,end_period
,set_of_books_id
,fund_type
--,region
,country
,org_id
,category_id
,status
,original_budget
,transfer_in
,transfer_out
,holdback_amt
,currency_code_fc
,delete_flag
,transaction_create_date
,load_date
,fis_month
,fis_qtr
,fis_year
,business_unit_id)
SELECT
bim_r_fund_daily_facts_s.nextval,
sysdate,
sysdate,
l_user_id,
l_user_id,
l_user_id,
a.fund_id ,
a.parent_fund_id parent_fund_id,
a.fund_number fund_number,
a.start_date_active start_date,
a.end_date_active end_date,
a.start_period_name start_period,
a.end_period_name end_period,
a.set_of_books_id set_of_book_id,
a.fund_type fund_type,
a.country_id country,
a.org_id org_id,
a.category_id fund_category,
a.status_code fund_status,
a.original_budget original_amount,
0,
0,
0,
a.currency_code_fc,
'N',
b2.transaction_create_date,
trunc((decode(decode( to_char(transaction_create_date,'MM') , to_char(next_day(transaction_create_date,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM') ,'TRUE','FALSE' )
,'TRUE'
,decode(decode(transaction_create_date , (next_day(transaction_create_date, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))) - 7) , 'TRUE' ,'FALSE')
,'TRUE'
,transaction_create_date
,'FALSE'
,next_day(transaction_create_date, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))))
,'FALSE'
,decode(decode(to_char(transaction_create_date,'MM'),to_char(next_day(transaction_create_date,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM'),'TRUE','FALSE')
,'FALSE'
,last_day(transaction_create_date))))) weekend_date,
b2.fis_month,
b2.fis_qtr,
b2.fis_year,
a.business_unit_id
FROM ozf_funds_all_b a,
(SELECT distinct(fund_id) fund_id,
fis_month fis_month,
fis_qtr fis_qtr,
fis_year fis_year,
transaction_create_date transaction_create_date
FROM bim_r_fdsp_daily_facts fdsp
WHERE fund_id is not null
AND transaction_create_date is not null
AND (fund_id, transaction_create_date) not in
( SELECT /*+ hash_aj */ fund_id, transaction_create_date
from bim_r_fund_daily_facts b1
)) b2
WHERE b2.fund_id = a.fund_id;
FND_FILE.put_line(fnd_file.log,'error insert fund daily for balancing'||sqlerrm(sqlcode));
FND_MESSAGE.Set_Name ('AMS', 'AMS_REP_INSERT_ERROR');
FND_FILE.put_line(fnd_file.log,'Insert into log history');
INSERT /*+ append parallel(fwf,p_para_num) */ INTO bim_r_fund_weekly_facts fwf(
fund_transaction_id
,creation_date
,last_update_date
,created_by
,last_updated_by
,last_update_login
,fund_id
,parent_fund_id
,fund_number
,start_date
,end_date
,start_period
,end_period
,set_of_books_id
,fund_type
-- ,region
,country
,org_id
,category_id
,status
,original_budget
,transfer_in
,transfer_out
,holdback_amt
,currency_code_fc
,delete_flag
,transaction_create_date
,load_date
,fis_month
,fis_qtr
,fis_year
,business_unit_id)
SELECT /*+ parallel(inner, p_para_num) */
bim_r_fund_weekly_facts_s.nextval
,sysdate
,sysdate
,l_user_id
,l_user_id
,l_user_id
,inner.fund_id
,inner.parent_fund_id
,inner.fund_number
,inner.start_date
,inner.end_date
,inner.start_period
,inner.end_period
,inner.set_of_books_id
,inner.fund_type
--,inner.region
,inner.country
,inner.org_id
,inner.category_id
,inner.status
,inner.original_budget
,inner.transfer_in
,inner.transfer_out
,inner.holdback_amt
,inner.currency_code_fc
,'N'
,inner.load_date
,inner.load_date
,BIM_SET_OF_BOOKS.get_fiscal_month(inner.load_date,204)
,BIM_SET_OF_BOOKS.get_fiscal_qtr(inner.load_date,204)
,BIM_SET_OF_BOOKS.get_fiscal_year(inner.load_date,204)
,inner.business_unit_id
FROM(SELECT fund_id fund_id
,parent_fund_id parent_fund_id
,fund_number fund_number
,start_date start_date
,end_date end_date
,start_period start_period
,end_period end_period
,set_of_books_id set_of_books_id
,fund_type fund_type
--,region region
,country country
,org_id org_id
,business_unit_id business_unit_id
,category_id category_id
,status status
,original_budget original_budget
,SUM(transfer_in) transfer_in
,SUM(transfer_out) transfer_out
,SUM(holdback_amt) holdback_amt
,currency_code_fc currency_code_fc
,load_date load_date
FROM bim_r_fund_daily_facts
GROUP BY
fund_id
,load_date
,parent_fund_id
,fund_number
,start_date
,end_date
,start_period
,end_period
,set_of_books_id
,fund_type
-- ,region
,country
,org_id
,business_unit_id
,category_id
,status
,original_budget
,currency_code_fc) inner;
FND_FILE.put_line(fnd_file.log,'Error insertg fund weekly:'||sqlerrm(sqlcode));
FND_MESSAGE.Set_Name ('AMS', 'AMS_REP_INSERT_ERROR');
INSERT /*+ append parallel(fwf,p_para_num) */
INTO bim_r_fdsp_weekly_facts fwf(
spend_transaction_id
,creation_date
,last_update_date
,created_by
,last_updated_by
,last_update_login
,fund_id
,business_unit_id
,util_org_id
,standard_discount
,accrual
,market_expense
,commited_amt
,planned_amt
,paid_amt
,delete_flag
,transaction_create_date
,load_date
,object_id
,object_type
,fis_month
,fis_qtr
,fis_year
)
SELECT /*+ parallel(inner.p_para_num) */
bim_r_fdsp_weekly_facts_s.nextval
,sysdate
,sysdate
,l_user_id
,l_user_id
,l_user_id
,inner.fund_id
,inner.business_unit_id
,inner.util_org_id
,inner.standard_discount
,inner.accrual
,inner.market_expense
,inner.commited_amt
,inner.planned_amt
,inner.paid_amt
,'N'
,inner.load_date
,inner.load_date
,inner.object_id
,inner.object_type
,BIM_SET_OF_BOOKS.get_fiscal_month(inner.load_date,204)
,BIM_SET_OF_BOOKS.get_fiscal_qtr(inner.load_date,204)
,BIM_SET_OF_BOOKS.get_fiscal_year(inner.load_date,204)
FROM
(SELECT fund_id fund_id
,business_unit_id business_unit_id
,util_org_id util_org_id
,object_id object_id
,object_type object_type
,SUM(standard_discount) standard_discount
,SUM(accrual) accrual
,SUM(market_expense) market_expense
,SUM(commited_amt) commited_amt
,SUM(planned_amt) planned_amt
,SUM(paid_amt) paid_amt
,load_date load_date
FROM bim_r_fdsp_daily_facts
GROUP BY load_date
,business_unit_id
,object_id
,object_type
,fund_id
,util_org_id) inner;
FND_FILE.put_line(fnd_file.log,'error insert fdsp_weekly'||sqlerrm(sqlcode));
FND_MESSAGE.Set_Name ('AMS', 'AMS_REP_INSERT_ERROR');
fnd_message.set_token('proc_name', 'UPDATE_SUB_BALANCE', FALSE);
update_sub_balance(p_start_datel, p_end_datel);