[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT tc_code from_currency,
decode(prim_conversion_rate,-3,to_date('01/01/1999','MM/DD/RRRR'),trx_date) transaction_create_date
FROM BIM_I_BGT_RATES
WHERE prim_conversion_rate < 0
AND tc_code is not null
AND trx_date >= p_start_date
ORDER BY tc_code,
trx_date ;
SELECT tc_code from_currency,
decode(sec_conversion_rate,-3,to_date('01/01/1999','MM/DD/RRRR'),trx_date) transaction_create_date
FROM BIM_I_BGT_RATES
WHERE sec_conversion_rate < 0
AND tc_code is not null
AND trx_date >= p_start_date
ORDER BY tc_code,
trx_date ;
SELECT COUNT(*) INTO l_cnt_miss_rate1 FROM BIM_I_BGT_RATES
WHERE
prim_conversion_rate < 0
AND tc_code is not null
AND trx_date >= p_start_date;
SELECT COUNT(*) INTO l_cnt_miss_rate2 FROM BIM_I_BGT_RATES
WHERE
sec_conversion_rate <0
AND tc_code is not null
AND trx_date >= p_start_date;
l_last_update_date DATE;
l_last_update_date DATE;
BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS:Drop index before inserting.');
BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS:First insert into table BIM_I_BUDGET_FACTS_STG');
INSERT /*+ append parallel */
INTO BIM_I_BUDGET_FACTS_STG CDF(
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
,business_unit_id
,from_currency
,conversion_rate
,planned
,committed
,utilized
,paid
,metric_type
,accrual
,conversion_rate_s
,original_budget_s
,transfer_in_s
,transfer_out_s
,holdback_amt_s
,planned_s
,committed_s
,utilized_s
,accrual_s
,paid_s)
SELECT /*+ parallel */
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.transaction_create_date,
inner.business_unit_id,
inner.from_currency,
inner.conversion_rate,
inner.planned,
inner.committed,
inner.utilized,
inner.paid,
inner.metric_type,
inner.accrual,
inner.conversion_rate_s,
inner.original_budget_s,
inner.transfer_in_s,
inner.transfer_out_s,
inner.holdback_amt_s,
inner.planned_s,
inner.committed_s,
inner.utilized_s,
inner.accrual_s,
inner.paid_s
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,
SUM(transfer_in) transfer_in,
SUM(transfer_out) transfer_out,
SUM(holdback_amt) holdback_amt,
from_currency,
conversion_rate,
SUM(planned) planned,
SUM(committed) committed,
SUM(utilized) utilized,
SUM(paid) paid,
metric_type metric_type,
SUM(accrual) accrual,
conversion_rate_s,
SUM(original_budget_s) original_budget_s,
SUM(transfer_in_s) transfer_in_s,
SUM(transfer_out_s) transfer_out_s,
SUM(holdback_amt_s) holdback_amt_s,
SUM(planned_s) planned_s,
SUM(committed_s) committed_s,
SUM(utilized_s) utilized_s,
SUM(accrual_s) accrual_s,
SUM(paid_s) paid_s
FROM (
--original budget
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,
DECODE(ad.fund_type,'FIXED',ad.original_budget,'FULLY_ACCRUED',0) original_budget,
trunc(ad.start_date_active) transaction_create_date,
0 transfer_in,
0 transfer_out,
0 holdback_amt,
nvl(ad.currency_code_tc,'USD') from_currency,
0 conversion_rate,
0 planned,
0 committed,
0 utilized,
0 paid,
'ORIGINAL_BUDGET' metric_type,
0 accrual,
0 conversion_rate_s,
0 original_budget_s,
0 transfer_in_s,
0 transfer_out_s,
0 holdback_amt_s,
0 planned_s,
0 committed_s,
0 utilized_s,
0 accrual_s,
0 paid_s
FROM ozf_funds_all_b ad
WHERE nvl(ad.end_date_active,sysdate) >=p_start_date
AND ad.start_date_active <=p_end_date
AND ad.status_code in ('ACTIVE','CLOSED','CANCELLED')
AND ad.parent_fund_id is null
UNION ALL --transfer_in
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,
0 original_budget,
trunc(bu1.approval_date) transaction_create_date,
SUM(nvl(bu1.approved_amount,0)) transfer_in,
0 transfer_out,
0 holdback_amt,
nvl(bu1.request_currency,'USD') from_currency,
0 conversion_rate,
0 planned,
0 committed,
0 utilized,
0 paid,
'TRANSFER_IN' metric_type,
0 accrual,
0 conversion_rate_s,
0 original_budget_s,
0 transfer_in_s,
0 transfer_out_s,
0 holdback_amt_s,
0 planned_s,
0 committed_s,
0 utilized_s,
0 accrual_s,
0 paid_s
FROM ozf_funds_all_b ad,
ozf_act_budgets BU1
WHERE nvl(ad.end_date_active,sysdate) >p_start_date
AND bu1.approval_date <= p_end_date
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 ,
nvl(bu1.request_currency,'USD')
UNION ALL --transfer_out
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,
0 original_budget,
trunc(bu2.approval_date) transaction_create_date,
0 transfer_in,
SUM(decode(bu2.transfer_type,'TRANSFER', nvl(bu2.approved_amount,0),0))+
SUM(decode(bu2.transfer_type,'REQUEST', nvl(bu2.approved_amount,0),0)) transfer_out,
SUM(decode(bu2.transfer_type, 'RESERVE', nvl(bu2.approved_amount,0),0))-
SUM(decode(bu2.transfer_type, 'RELEASE', nvl(bu2.approved_amount,0),0)) holdback_amt,
nvl(bu2.request_currency,'USD') from_currency,
0 conversion_rate,
0 planned,
0 committed,
0 utilized,
0 paid,
'TRANSFER_OUT' metric_type,
0 accrual,
0 conversion_rate_s,
0 original_budget_s,
0 transfer_in_s,
0 transfer_out_s,
0 holdback_amt_s,
0 planned_s,
0 committed_s,
0 utilized_s,
0 accrual_s,
0 paid_s
FROM ozf_funds_all_b ad,
ozf_act_budgets BU2
WHERE nvl(ad.end_date_active,sysdate) >p_start_date
AND bu2.approval_date<=p_end_date
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,
nvl(bu2.request_currency,'USD')
UNION ALL--planned
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,
0 original_budget,
trunc(nvl(bu2.request_date,bu2.creation_date)) transaction_create_date,
0 transfer_in,
0 transfer_out,
0 holdback_amt,
nvl(bu2.request_currency,'USD') from_currency,
0 conversion_rate,
SUM(nvl(bu2.request_amount,0)) planned,
0 committed,
0 utilized,
0 paid,
'PLANNED' metric_type,
0 accrual,
0 conversion_rate_s,
0 original_budget_s,
0 transfer_in_s,
0 transfer_out_s,
0 holdback_amt_s,
0 planned_s,
0 committed_s,
0 utilized_s,
0 accrual_s,
0 paid_s
FROM ozf_funds_all_b ad,
ozf_act_budgets BU2
WHERE nvl(ad.end_date_active,sysdate) >p_start_date
AND bu2.budget_source_type ='FUND'
AND bu2.ARC_ACT_BUDGET_USED_BY <> 'FUND'
AND nvl(bu2.request_date,bu2.creation_date) <=p_end_date
AND bu2.budget_source_id = ad.fund_id
GROUP BY ad.fund_id,
trunc(nvl(bu2.request_date,bu2.creation_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,
nvl(bu2.request_currency,'USD')
UNION ALL--PLANNED 2
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,
0 original_budget,
trunc(bu2.approval_date) transaction_create_date,
0 transfer_in,
0 transfer_out,
0 holdback_amt,
nvl(bu2.request_currency,'USD') from_currency,
0 conversion_rate,
0-SUM(nvl(bu2.approved_amount,0)) planned,
0 committed,
0 utilized,
0 paid,
'PLANNED' metric_type,
0 accrual,
0 conversion_rate_s,
0 original_budget_s,
0 transfer_in_s,
0 transfer_out_s,
0 holdback_amt_s,
0 planned_s,
0 committed_s,
0 utilized_s,
0 accrual_s,
0 paid_s
FROM ozf_funds_all_b ad,
ozf_act_budgets BU2
WHERE nvl(ad.end_date_active,sysdate) >p_start_date
AND bu2.arc_act_budget_used_by ='FUND'
AND bu2.budget_source_type<>'FUND'
AND bu2.status_code ='APPROVED'
AND bu2.approval_date <=p_end_date
AND bu2.act_budget_used_by_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,
nvl(bu2.request_currency,'USD')
UNION ALL--committed 1
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,
0 original_budget,
trunc(bu2.approval_date) transaction_create_date,
0 transfer_in,
0 transfer_out,
0 holdback_amt,
nvl(bu2.request_currency,'USD') from_currency,
0 conversion_rate,
0 planned,
SUM(nvl(bu2.approved_amount,0)) committed,
0 utilized,
0 paid,
'COMMITTED' metric_type,
0 accrual,
0 conversion_rate_s,
0 original_budget_s,
0 transfer_in_s,
0 transfer_out_s,
0 holdback_amt_s,
0 planned_s,
0 committed_s,
0 utilized_s,
0 accrual_s,
0 paid_s
FROM ozf_funds_all_b ad,
ozf_act_budgets BU2
WHERE nvl(ad.end_date_active,sysdate) >p_start_date
AND bu2.budget_source_type ='FUND'
AND bu2.ARC_ACT_BUDGET_USED_BY <> 'FUND'
AND bu2.approval_date <=p_end_date
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,
nvl(bu2.request_currency,'USD')
UNION ALL--committed 2
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,
0 original_budget,
trunc(bu2.approval_date) transaction_create_date,
0 transfer_in,
0 transfer_out,
0 holdback_amt,
nvl(bu2.request_currency,'USD') from_currency,
0 conversion_rate,
0 planned,
0-SUM(nvl(bu2.approved_amount,0)) committed,
0 utilized,
0 paid,
'COMMITTED' metric_type,
0 accrual,
0 conversion_rate_s,
0 original_budget_s,
0 transfer_in_s,
0 transfer_out_s,
0 holdback_amt_s,
0 planned_s,
0 committed_s,
0 utilized_s,
0 accrual_s,
0 paid_s
FROM ozf_funds_all_b ad,
ozf_act_budgets BU2
WHERE nvl(ad.end_date_active,sysdate) >p_start_date
AND bu2.arc_act_budget_used_by ='FUND'
AND bu2.budget_source_type<>'FUND'
AND bu2.status_code ='APPROVED'
AND bu2.approval_date <=p_end_date
AND bu2.act_budget_used_by_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,
nvl(bu2.request_currency,'USD')
UNION ALL --utilized
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,
0 original_budget,
trunc(u2.creation_date) transaction_create_date,
0 transfer_in,
0 transfer_out,
0 holdback_amt,
nvl(u2.currency_code,'USD') from_currency,
0 conversion_rate,
0 planned,
0 committed,
SUM(nvl(u2.amount,0)) utilized,
0 paid,
'UTILIZED' metric_type,
0 accrual,
0 conversion_rate_s,
0 original_budget_s,
0 transfer_in_s,
0 transfer_out_s,
0 holdback_amt_s,
0 planned_s,
0 committed_s,
0 utilized_s,
0 accrual_s,
0 paid_s
FROM ozf_funds_all_b ad,
ozf_funds_utilized_all_b u2
WHERE nvl(ad.end_date_active,sysdate) >p_start_date
AND ad.fund_id =u2.fund_id
AND u2.creation_date <=p_end_date
AND u2.utilization_type in ('UTILIZED','ACCRUAL','ADJUSTMENT')
GROUP BY ad.fund_id,
trunc(u2.creation_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,
nvl(u2.currency_code,'USD')
union all --utilized 2
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,
0 original_budget,
trunc(u2.creation_date) transaction_create_date,
0 transfer_in,
0 transfer_out,
0 holdback_amt,
nvl(u2.currency_code,'USD') from_currency,
0 conversion_rate,
0 planned,
0 committed,
0-SUM(nvl(u2.amount,0)) utilized,
0 paid,
'UTILIZED' metric_type,
0 accrual,
0 conversion_rate_s,
0 original_budget_s,
0 transfer_in_s,
0 transfer_out_s,
0 holdback_amt_s,
0 planned_s,
0 committed_s,
0 utilized_s,
0 accrual_s,
0 paid_s
FROM ozf_funds_all_b ad,
ozf_funds_utilized_all_b u2
WHERE nvl(ad.end_date_active,sysdate) >p_start_date
AND ad.fund_id =u2.fund_id
AND ad.fund_type='FULLY_ACCRUED'
AND ad.liability_flag='N'
AND ad.accrual_basis='CUSTOMER'
AND u2.creation_date <=p_end_date
AND ad.plan_id=u2.component_id
AND u2.component_type='OFFR'
AND u2.utilization_type ='ACCRUAL'
GROUP BY ad.fund_id,
trunc(u2.creation_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,
nvl(u2.currency_code,'USD')
UNION ALL --accrual
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,
0 original_budget,
trunc(u2.creation_date) transaction_create_date,
0 transfer_in,
0 transfer_out,
0 holdback_amt,
nvl(u2.currency_code,'USD') from_currency,
0 conversion_rate,
0 planned,
0 committed,
0 utilized,
0 paid,
'ACCRUAL' metric_type,
SUM(nvl(u2.amount,0)) accrual,
0 conversion_rate_s,
0 original_budget_s,
0 transfer_in_s,
0 transfer_out_s,
0 holdback_amt_s,
0 planned_s,
0 committed_s,
0 utilized_s,
0 accrual_s,
0 paid_s
FROM ozf_funds_all_b ad,
ozf_funds_utilized_all_b u2
WHERE nvl(ad.end_date_active,sysdate) >p_start_date
AND ad.fund_id =u2.fund_id
AND ad.fund_type='FULLY_ACCRUED'
AND ad.liability_flag='N'
AND ad.accrual_basis='CUSTOMER'
AND u2.creation_date <=p_end_date
AND ad.plan_id=u2.component_id
AND u2.component_type='OFFR'
AND u2.utilization_type ='ACCRUAL'
GROUP BY ad.fund_id,
trunc(u2.creation_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,
nvl(u2.currency_code,'USD')
union all --accrual 2
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,
0 original_budget,
trunc(u2.creation_date) transaction_create_date,
0 transfer_in,
0 transfer_out,
0 holdback_amt,
nvl(u2.currency_code,'USD') from_currency,
0 conversion_rate,
0 planned,
0 committed,
0 utilized,
0 paid,
'ACCRUAL' metric_type,
SUM(nvl(u2.amount,0)) accrual,
0 conversion_rate_s,
0 original_budget_s,
0 transfer_in_s,
0 transfer_out_s,
0 holdback_amt_s,
0 planned_s,
0 committed_s,
0 utilized_s,
0 accrual_s,
0 paid_s
FROM ozf_funds_all_b ad,
ozf_funds_utilized_all_b u2
WHERE nvl(ad.end_date_active,sysdate) >p_start_date
AND ad.fund_id =u2.fund_id
AND ad.fund_type='FULLY_ACCRUED'
AND ad.accrual_basis='SALES'
AND u2.creation_date <=p_end_date
AND ad.plan_id=u2.component_id
AND u2.component_type='OFFR'
AND u2.utilization_type ='SALES_ACCRUAL'
GROUP BY ad.fund_id,
trunc(u2.creation_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,
nvl(u2.currency_code,'USD')
UNION ALL--paid 1
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,
0 original_budget,
trunc(u2.creation_date) transaction_create_date,
0 transfer_in,
0 transfer_out,
0 holdback_amt,
nvl(u2.currency_code,'USD') from_currency,
0 conversion_rate,
0 planned,
0 committed,
0 utilized,
SUM(nvl(u2.amount,0)) paid,
'PAID' metric_type,
0 accrual,
0 conversion_rate_s,
0 original_budget_s,
0 transfer_in_s,
0 transfer_out_s,
0 holdback_amt_s,
0 planned_s,
0 committed_s,
0 utilized_s,
0 accrual_s,
0 paid_s
FROM ozf_funds_all_b ad,
ozf_funds_utilized_all_b u2
WHERE nvl(ad.end_date_active,sysdate) >p_start_date
AND ad.fund_id =u2.fund_id
AND u2.creation_date <=p_end_date
AND u2.utilization_type ='UTILIZED'
GROUP BY ad.fund_id,
trunc(u2.creation_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,
nvl(u2.currency_code,'USD')
UNION ALL--paid 2, based on 11.5.9
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,
0 original_budget,
trunc(cla.claim_date) transaction_create_date,
0 transfer_in,
0 transfer_out,
0 holdback_amt,
nvl(cuti.currency_code,'USD') from_currency,
0 conversion_rate,
0 planned,
0 committed,
0 utilized,
SUM(nvl(cuti.amount,0)) paid,
'PAID' metric_type,
0 accrual,
0 conversion_rate_s,
0 original_budget_s,
0 transfer_in_s,
0 transfer_out_s,
0 holdback_amt_s,
0 planned_s,
0 committed_s,
0 utilized_s,
0 accrual_s,
0 paid_s
FROM ozf_funds_all_b ad,
ozf_funds_utilized_all_b u2,
ozf_claim_lines_util_all cuti,
ozf_claim_lines_all cln,
ozf_claims_all cla
WHERE nvl(ad.end_date_active,sysdate) >p_start_date
AND ad.fund_id =u2.fund_id
AND cla.claim_date <=p_end_date
AND u2.utilization_id= cuti.utilization_id
AND u2.utilization_type IN ('ACCRUAL','SALES_ACCRUAL','ADJUSTMENT')
AND cuti.claim_line_id= cln.claim_line_id
AND cln.claim_id = cla.claim_id
AND cla.status_code = 'CLOSED'
GROUP BY ad.fund_id,
trunc(cla.claim_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,
nvl(cuti.currency_code,'USD')
)
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,
from_currency,
conversion_rate,
metric_type,
conversion_rate_s
)inner;
BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS:Inserting into BIM_I_BGT_RATES');
INSERT /*+ append parallel */
INTO BIM_I_BGT_RATES BRT(tc_code,
trx_date,
prim_conversion_rate,
sec_conversion_rate)
SELECT from_currency,
transaction_create_date,
FII_CURRENCY.get_rate(from_currency,l_global_currency_code,transaction_create_date,l_pgc_rate_type),
FII_CURRENCY.get_rate(from_currency,l_secondary_currency_code,transaction_create_date,l_sgc_rate_type)
FROM (select distinct from_currency from_currency,
transaction_create_date transaction_create_date
from bim_i_budget_facts_stg);
BIS_COLLECTION_UTILITIES.deleteLogForObject('BUDGET_FACTS');
BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS:Insert into BIM_I_BUDGET_FACTS');
INSERT /*+ append parallel */
INTO BIM_I_BUDGET_FACTS CDF(
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
,business_unit_id
,from_currency
,conversion_rate
,planned
,committed
,utilized
,paid
,metric_type
,accrual
,conversion_rate_s
,original_budget_s
,transfer_in_s
,transfer_out_s
,holdback_amt_s
,planned_s
,committed_s
,utilized_s
,accrual_s
,paid_s)
SELECT /*+ parallel */
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*prim_conversion_rate,
inner.transfer_in*prim_conversion_rate,
inner.transfer_out*prim_conversion_rate,
inner.holdback_amt*prim_conversion_rate,
inner.currency_code_fc,
'N',
inner.transaction_create_date,
inner.business_unit_id,
inner.from_currency,
inner.conversion_rate,
inner.planned*prim_conversion_rate,
inner.committed*prim_conversion_rate,
inner.utilized*prim_conversion_rate,
inner.paid*prim_conversion_rate,
inner.metric_type,
inner.accrual*prim_conversion_rate,
inner.conversion_rate_s,
inner.original_budget*sec_conversion_rate,
inner.transfer_in*sec_conversion_rate,
inner.transfer_out*sec_conversion_rate,
inner.holdback_amt*sec_conversion_rate,
inner.planned*sec_conversion_rate,
inner.committed*sec_conversion_rate,
inner.utilized*sec_conversion_rate,
inner.accrual*sec_conversion_rate,
inner.paid*sec_conversion_rate
FROM bim_i_budget_facts_stg inner, bim_i_bgt_rates rt
where inner.from_currency = rt.tc_code
and inner.transaction_create_date= rt.trx_date;
l_last_update_date DATE;
DELETE from bim_i_budget_facts where transaction_create_date>= p_start_date and metric_type is not null;
INSERT INTO BIM_I_BUDGET_FACTS_STG CDF(
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
,business_unit_id
,from_currency
,conversion_rate
,planned
,committed
,utilized
,paid
,metric_type
,accrual
,conversion_rate_s
,original_budget_s
,transfer_in_s
,transfer_out_s
,holdback_amt_s
,planned_s
,committed_s
,utilized_s
,accrual_s
,paid_s)
SELECT
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.transaction_create_date,
inner.business_unit_id,
inner.from_currency,
inner.conversion_rate,
inner.planned,
inner.committed,
inner.utilized,
inner.paid,
inner.metric_type,
inner.accrual,
inner.conversion_rate_s,
inner.original_budget_s,
inner.transfer_in_s,
inner.transfer_out_s,
inner.holdback_amt_s,
inner.planned_s,
inner.committed_s,
inner.utilized_s,
inner.accrual_s,
inner.paid_s
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,
SUM(transfer_in) transfer_in,
SUM(transfer_out) transfer_out,
SUM(holdback_amt) holdback_amt,
from_currency,
conversion_rate,
SUM(planned) planned,
SUM(committed) committed,
SUM(utilized) utilized,
SUM(paid) paid,
metric_type metric_type,
SUM(accrual) accrual,
conversion_rate_s,
SUM(original_budget_s) original_budget_s,
SUM(transfer_in_s) transfer_in_s,
SUM(transfer_out_s) transfer_out_s,
SUM(holdback_amt_s) holdback_amt_s,
SUM(planned_s) planned_s,
SUM(committed_s) committed_s,
SUM(utilized_s) utilized_s,
SUM(accrual_s) accrual_s,
SUM(paid_s) paid_s
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,
DECODE(ad.fund_type,'FIXED',ad.original_budget,'FULLY_ACCRUED',0) original_budget,
trunc(ad.start_date_active) transaction_create_date,
0 transfer_in,
0 transfer_out,
0 holdback_amt,
nvl(ad.currency_code_tc,'USD') from_currency,
0 conversion_rate,
0 planned,
0 committed,
0 utilized,
0 paid,
'ORIGINAL_BUDGET' metric_type,
0 accrual,
0 conversion_rate_s,
0 original_budget_s,
0 transfer_in_s,
0 transfer_out_s,
0 holdback_amt_s,
0 planned_s,
0 committed_s,
0 utilized_s,
0 accrual_s,
0 paid_s
FROM ozf_funds_all_b ad
WHERE ( ( ad.status_date between p_start_date and p_end_date
AND ad.start_date_active <=p_end_date
)
or ( ad.start_date_active between p_start_date and p_end_date
AND ad.status_date 'FUND'
AND nvl(bu2.request_date,bu2.creation_date) <=p_end_date
AND bu2.budget_source_id = ad.fund_id
GROUP BY ad.fund_id,
trunc(nvl(bu2.request_date,bu2.creation_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,
nvl(bu2.request_currency,'USD')
UNION ALL--planned 2
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,
0 original_budget,
trunc(bu2.approval_date) transaction_create_date,
0 transfer_in,
0 transfer_out,
0 holdback_amt,
nvl(bu2.request_currency,'USD') from_currency,
0 conversion_rate,
0-SUM(nvl(bu2.approved_amount,0)) planned,
0 committed,
0 utilized,
0 paid,
'PLANNED' metric_type,
0 accrual,
0 conversion_rate_s,
0 original_budget_s,
0 transfer_in_s,
0 transfer_out_s,
0 holdback_amt_s,
0 planned_s,
0 committed_s,
0 utilized_s,
0 accrual_s,
0 paid_s
FROM ozf_funds_all_b ad,
ozf_act_budgets BU2
WHERE bu2.approval_date between p_start_date and p_end_date
AND bu2.arc_act_budget_used_by ='FUND'
AND bu2.budget_source_type<>'FUND'
AND bu2.status_code ='APPROVED'
AND bu2.act_budget_used_by_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,
nvl(bu2.request_currency,'USD')
UNION ALL--committed 1
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,
0 original_budget,
trunc(bu2.approval_date) transaction_create_date,
0 transfer_in,
0 transfer_out,
0 holdback_amt,
nvl(bu2.request_currency,'USD') from_currency,
0 conversion_rate,
0 planned,
SUM(nvl(bu2.approved_amount,0)) committed,
0 utilized,
0 paid,
'COMMITTED' metric_type,
0 accrual,
0 conversion_rate_s,
0 original_budget_s,
0 transfer_in_s,
0 transfer_out_s,
0 holdback_amt_s,
0 planned_s,
0 committed_s,
0 utilized_s,
0 accrual_s,
0 paid_s
FROM ozf_funds_all_b ad,
ozf_act_budgets BU2
WHERE bu2.approval_date between p_start_date and p_end_date
AND bu2.budget_source_type ='FUND'
AND bu2.ARC_ACT_BUDGET_USED_BY <> '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,
nvl(bu2.request_currency,'USD')
UNION ALL--committed 2
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,
0 original_budget,
trunc(bu2.approval_date) transaction_create_date,
0 transfer_in,
0 transfer_out,
0 holdback_amt,
nvl(bu2.request_currency,'USD') from_currency,
0 conversion_rate,
0 planned,
0-SUM(nvl(bu2.approved_amount,0)) committed,
0 utilized,
0 paid,
'COMMITTED' metric_type,
0 accrual,
0 conversion_rate_s,
0 original_budget_s,
0 transfer_in_s,
0 transfer_out_s,
0 holdback_amt_s,
0 planned_s,
0 committed_s,
0 utilized_s,
0 accrual_s,
0 paid_s
FROM ozf_funds_all_b ad,
ozf_act_budgets BU2
WHERE bu2.approval_date between p_start_date and p_end_date
AND bu2.arc_act_budget_used_by ='FUND'
AND bu2.budget_source_type<>'FUND'
AND bu2.status_code ='APPROVED'
AND bu2.act_budget_used_by_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,
nvl(bu2.request_currency,'USD')
UNION ALL --utilized
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,
0 original_budget,
trunc(u2.creation_date) transaction_create_date,
0 transfer_in,
0 transfer_out,
0 holdback_amt,
nvl(u2.currency_code,'USD') from_currency,
0 conversion_rate,
0 planned,
0 committed,
SUM(nvl(u2.amount,0)) utilized,
0 paid,
'UTILIZED' metric_type,
0 accrual,
0 conversion_rate_s,
0 original_budget_s,
0 transfer_in_s,
0 transfer_out_s,
0 holdback_amt_s,
0 planned_s,
0 committed_s,
0 utilized_s,
0 accrual_s,
0 paid_s
FROM ozf_funds_all_b ad,
ozf_funds_utilized_all_b u2
WHERE u2.creation_date between p_start_date and p_end_date
AND ad.fund_id =u2.fund_id
AND u2.utilization_type in ('UTILIZED','ACCRUAL','ADJUSTMENT')
GROUP BY ad.fund_id,
trunc(u2.creation_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,
nvl(u2.currency_code,'USD')
UNION ALL --utilized 2
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,
0 original_budget,
trunc(u2.creation_date) transaction_create_date,
0 transfer_in,
0 transfer_out,
0 holdback_amt,
nvl(u2.currency_code,'USD') from_currency,
0 conversion_rate,
0 planned,
0 committed,
0-SUM(nvl(u2.amount,0)) utilized,
0 paid,
'UTILIZED' metric_type,
0 accrual,
0 conversion_rate_s,
0 original_budget_s,
0 transfer_in_s,
0 transfer_out_s,
0 holdback_amt_s,
0 planned_s,
0 committed_s,
0 utilized_s,
0 accrual_s,
0 paid_s
FROM ozf_funds_all_b ad,
ozf_funds_utilized_all_b u2
WHERE u2.creation_date between p_start_date and p_end_date
AND ad.fund_id =u2.fund_id
AND ad.fund_type='FULLY_ACCRUED'
AND ad.accrual_basis ='CUSTOMER'
AND ad.liability_flag='N'
AND ad.plan_id=u2.component_id
AND u2.component_type='OFFR'
AND u2.utilization_type='ACCRUAL'
GROUP BY ad.fund_id,
trunc(u2.creation_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,
nvl(u2.currency_code,'USD')
UNION ALL --accrual 1
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,
0 original_budget,
trunc(u2.creation_date) transaction_create_date,
0 transfer_in,
0 transfer_out,
0 holdback_amt,
nvl(u2.currency_code,'USD') from_currency,
0 conversion_rate,
0 planned,
0 committed,
0 utilized,
0 paid,
'ACCRUAL' metric_type,
SUM(nvl(u2.amount,0)) accrual,
0 conversion_rate_s,
0 original_budget_s,
0 transfer_in_s,
0 transfer_out_s,
0 holdback_amt_s,
0 planned_s,
0 committed_s,
0 utilized_s,
0 accrual_s,
0 paid_s
FROM ozf_funds_all_b ad,
ozf_funds_utilized_all_b u2
WHERE u2.creation_date between p_start_date and p_end_date
AND ad.fund_id =u2.fund_id
AND ad.fund_type='FULLY_ACCRUED'
AND ad.accrual_basis ='SALES'
AND ad.plan_id=u2.component_id
AND u2.component_type='OFFR'
AND u2.utilization_type='SALES_ACCRUAL'
GROUP BY ad.fund_id,
trunc(u2.creation_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,
nvl(u2.currency_code,'USD')
UNION ALL --accrual 2
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,
0 original_budget,
trunc(u2.creation_date) transaction_create_date,
0 transfer_in,
0 transfer_out,
0 holdback_amt,
nvl(u2.currency_code,'USD') from_currency,
0 conversion_rate,
0 planned,
0 committed,
0 utilized,
0 paid,
'ACCRUAL' metric_type,
SUM(nvl(u2.amount,0)) accrual,
0 conversion_rate_s,
0 original_budget_s,
0 transfer_in_s,
0 transfer_out_s,
0 holdback_amt_s,
0 planned_s,
0 committed_s,
0 utilized_s,
0 accrual_s,
0 paid_s
FROM ozf_funds_all_b ad,
ozf_funds_utilized_all_b u2
WHERE u2.creation_date between p_start_date and p_end_date
AND ad.fund_id =u2.fund_id
AND ad.fund_type='FULLY_ACCRUED'
AND ad.accrual_basis ='CUSTOMER'
AND ad.liability_flag='N'
AND ad.plan_id=u2.component_id
AND u2.component_type='OFFR'
AND u2.utilization_type ='ACCRUAL'
GROUP BY ad.fund_id,
trunc(u2.creation_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,
nvl(u2.currency_code,'USD')
UNION ALL--paid 1
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,
0 original_budget,
trunc(u2.creation_date) transaction_create_date,
0 transfer_in,
0 transfer_out,
0 holdback_amt,
nvl(u2.currency_code,'USD') from_currency,
0 conversion_rate,
0 planned,
0 committed,
0 utilized,
SUM(nvl(u2.amount,0)) paid,
'PAID' metric_type,
0 accrual,
0 conversion_rate_s,
0 original_budget_s,
0 transfer_in_s,
0 transfer_out_s,
0 holdback_amt_s,
0 planned_s,
0 committed_s,
0 utilized_s,
0 accrual_s,
0 paid_s
FROM ozf_funds_all_b ad,
ozf_funds_utilized_all_b u2
WHERE u2.creation_date between p_start_date and p_end_date
AND ad.fund_id =u2.fund_id
AND u2.utilization_type ='UTILIZED'
GROUP BY ad.fund_id,
trunc(u2.creation_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,
nvl(u2.currency_code,'USD')
UNION ALL--paid 2, based on 11.5.9
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,
0 original_budget,
trunc(cla.claim_date) transaction_create_date,
0 transfer_in,
0 transfer_out,
0 holdback_amt,
nvl(cuti.currency_code,'USD') from_currency,
0 conversion_rate,
0 planned,
0 committed,
0 utilized,
SUM(nvl(cuti.amount,0)) paid,
'PAID' metric_type,
0 accrual,
0 conversion_rate_s,
0 original_budget_s,
0 transfer_in_s,
0 transfer_out_s,
0 holdback_amt_s,
0 planned_s,
0 committed_s,
0 utilized_s,
0 accrual_s,
0 paid_s
FROM ozf_funds_all_b ad,
ozf_funds_utilized_all_b u2,
ozf_claim_lines_util_all cuti,
ozf_claim_lines_all cln,
ozf_claims_all cla
WHERE cla.claim_date between p_start_date and p_end_date
AND ad.fund_id =u2.fund_id
AND u2.utilization_id= cuti.utilization_id
AND u2.utilization_type IN ('ACCRUAL','SALES_ACCRUAL','ADJUSTMENT')
AND cuti.claim_line_id= cln.claim_line_id
AND cln.claim_id = cla.claim_id
AND cla.status_code = 'CLOSED'
GROUP BY ad.fund_id,
trunc(cla.claim_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,
nvl(cuti.currency_code,'USD')
)
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,
from_currency,
conversion_rate,
metric_type,
conversion_rate_s
)inner;
BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS:Inserting into BIM_I_BGT_RATES');
INSERT
INTO BIM_I_BGT_RATES BRT(tc_code,
trx_date,
prim_conversion_rate,
sec_conversion_rate)
SELECT from_currency,
transaction_create_date,
FII_CURRENCY.get_rate(from_currency,l_global_currency_code,transaction_create_date,l_pgc_rate_type),
FII_CURRENCY.get_rate(from_currency,l_secondary_currency_code,transaction_create_date,l_sgc_rate_type)
FROM (select distinct from_currency from_currency,
transaction_create_date transaction_create_date
from bim_i_budget_facts_stg);
DELETE from BIM_I_BUDGET_FACTS_stg where transaction_create_date>= p_start_date;
INSERT /*+ append parallel */
INTO BIM_I_BUDGET_FACTS CDF(
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
,business_unit_id
,from_currency
,conversion_rate
,planned
,committed
,utilized
,paid
,metric_type
,accrual
,conversion_rate_s
,original_budget_s
,transfer_in_s
,transfer_out_s
,holdback_amt_s
,planned_s
,committed_s
,utilized_s
,accrual_s
,paid_s)
SELECT /*+ parallel */
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*prim_conversion_rate,
inner.transfer_in*prim_conversion_rate,
inner.transfer_out*prim_conversion_rate,
inner.holdback_amt*prim_conversion_rate,
inner.currency_code_fc,
'N',
inner.transaction_create_date,
inner.business_unit_id,
inner.from_currency,
inner.conversion_rate,
inner.planned*prim_conversion_rate,
inner.committed*prim_conversion_rate,
inner.utilized*prim_conversion_rate,
inner.paid*prim_conversion_rate,
inner.metric_type,
inner.accrual*prim_conversion_rate,
inner.conversion_rate_s,
inner.original_budget*sec_conversion_rate,
inner.transfer_in*sec_conversion_rate,
inner.transfer_out*sec_conversion_rate,
inner.holdback_amt*sec_conversion_rate,
inner.planned*sec_conversion_rate,
inner.committed*sec_conversion_rate,
inner.utilized*sec_conversion_rate,
inner.accrual*sec_conversion_rate,
inner.paid*sec_conversion_rate
FROM bim_i_budget_facts_stg inner, bim_i_bgt_rates rt
where inner.from_currency = rt.tc_code
and inner.transaction_create_date= rt.trx_date;
BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS:Before Insert into log.');
BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS:After Insert into log.');