[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select instance_code,
warehouse_to_instance_link
from edw_source_instances;
SELECT tablespace_name
INTO g_tablespace
FROM all_tables
WHERE table_name = 'FII_AR_OPERATIONS_SUMMARY'
AND owner = g_fii_schema;
select name
into l_db_name1
from v$database;
l_stmt := 'select sob_id from fii_ar_oltp_cash_v@'||
g_rec(i).db_link||' where rownum < 2';
l_stmt := 'select name '||
'from v$database@'||g_rec(i).db_link;
l_stmt := 'insert into '||g_fii_schema||'.FII_AR_OLTP_CASH T
(instance_code,
org_id,
sob_id,
customer_id,
period_set,
period_type,
calendar_day,
functional_currency,
cash_b,
cash_g,
receipt_cnt)
select /*+ DRIVING_SITE(CASH) */
instance_code,
org_id,
sob_id,
customer_id,
period_set,
period_type,
trunc(calendar_day),
functional_currency,
sum(cash_b),
sum(cash_g),
count(distinct(cash_receipt_id))
from
fii_ar_oltp_cash_v';
l_stmt := 'insert into '||g_fii_schema||'.FII_AR_EDW_REV T (
set_of_books_fk_key,
operating_unit_fk_key,
org_fk_key,
customer_fk_key,
functional_currency,
calendar_day_fk_key,
calendar_date,
amt_invoiced_ar_b,
amt_invoiced_ar_g,
amt_rev_earned_b,
amt_rev_earned_g,
inv_created_cnt,
inv_revrec_cnt)
select
f.set_of_books_fk_key,
org.oper_operating_unit_pk_key,
f.organization_fk_key,
cust.tprt_trade_partner_pk_key,
curr.crnc_currency,
f.gl_date_fk_key,
trunc(f.gl_date),
sum(decode(f.account_class, ''REC'', f.amt_b, 0)),
sum(decode(f.account_class, ''REC'', f.amt_g, 0)),
sum(decode(f.account_class, ''REV'', f.amt_b, 0)),
sum(decode(f.account_class, ''REV'', f.amt_g, 0)),
count(distinct(decode(f.account_class, ''REC'', invoice_id, to_number(null)))),
count(distinct(decode(f.account_class, ''REV'', invoice_id, to_number(null))))
from fii_ar_trx_dist_f f,
edw_organization_m org,
edw_trd_partner_m cust,
edw_currency_m curr
where f.gl_date >= to_date('''||to_char(g_start_date,'DD-MM-YYYY')||''', ''DD-MM-YYYY'')
and f.gl_date < to_date('''||to_char(g_end_date,'DD-MM-YYYY')||''', ''DD-MM-YYYY'')
and f.account_class in (''REV'', ''REC'')
and curr.crnc_currency_pk_key = f.functional_currency_fk_key
and cust.tplo_tpartner_loc_pk_key = f.bill_to_customer_fk_key
and org.orga_organization_pk_key = f.organization_fk_key
group by
f.set_of_books_fk_key,
org.oper_operating_unit_pk_key,
f.organization_fk_key,
cust.tprt_trade_partner_pk_key,
curr.crnc_currency,
f.gl_date_fk_key,
trunc(f.gl_date)';
l_stmt := 'insert into '||g_fii_schema||'.FII_AR_EDW_REV T (
set_of_books_fk_key,
operating_unit_fk_key,
org_fk_key,
customer_fk_key,
functional_currency,
calendar_day_fk_key,
calendar_date,
amt_invoiced_ar_b,
amt_invoiced_ar_g,
amt_rev_earned_b,
amt_rev_earned_g,
inv_created_cnt,
inv_revrec_cnt )
select
f.gl_set_of_books_fk_key,
org.oper_operating_unit_pk_key,
f.organization_fk_key,
cust.tprt_trade_partner_pk_key,
curr.crnc_currency,
f.gl_period_fk_key,
t.cday_calendar_date,
0,
0,
sum(f.amt_b),
sum(f.amt_g),
0,
count(distinct(f.ar_doc_num_fk_key))
from fii_e_revenue_f f,
edw_time_m t,
edw_currency_m curr,
edw_rev_source_m src,
edw_organization_m org,
edw_gl_acct3_m acct,
edw_trd_partner_m cust
where t.cday_cal_day_pk_key = f.gl_period_fk_key
and src.source_rev_src_pk_key = f.revenue_source_fk_key
and src.source_rev_src_pk in (''AR ADJ'', ''AR REV'')
and acct.l1_pk_key = f.gl_acct3_fk_key
and acct.l1_type = ''Revenue''
and t.cday_calendar_date >= to_date('''||to_char(g_start_date,'DD-MM-YYYY')||''', ''DD-MM-YYYY'')
and t.cday_calendar_date < to_date('''||to_char(g_end_date,'DD-MM-YYYY')||''', ''DD-MM-YYYY'')
and f.base_currency_fk_key = curr.crnc_currency_pk_key
and org.orga_organization_pk_key = f.organization_fk_key
and cust.tplo_tpartner_loc_pk_key = f.bill_to_customer_fk_key
group by
f.gl_set_of_books_fk_key,
org.oper_operating_unit_pk_key,
f.organization_fk_key,
cust.tprt_trade_partner_pk_key,
curr.crnc_currency,
f.gl_period_fk_key,
t.cday_calendar_date';
l_stmt := 'insert into '||g_fii_schema||'.FII_AR_OLTP_INV T (
instance_code,
org_id,
sob_id,
customer_id,
period_set,
period_type,
calendar_day,
functional_currency,
inv_b,
inv_g,
inv_created_cnt)
select /*+ DRIVING_SITE(INV) */
instance_code,
org_id,
sob_id,
customer_trx_id,
period_set,
period_type,
trunc(calendar_day),
functional_currency,
sum(inv_b),
sum(inv_g),
count(distinct(customer_trx_id))
from
fii_ar_oltp_inv_v';
l_stmt := 'insert into fii_ar_oper_cust_summary_f (
set_of_books_fk_key,
operating_unit_fk_key,
org_fk_key,
customer_fk_key,
functional_currency,
calendar_day_fk_key,
calendar_date,
amt_invoiced_ar_b,
amt_invoiced_ar_g,
amt_rev_earned_b,
amt_rev_earned_g,
amt_cash_received_b,
amt_cash_received_g,
inv_created_cnt,
inv_revrec_cnt,
receipt_cnt,
last_update_date,
creation_date)
select a.set_of_books_fk_key,
a.operating_unit_fk_key,
a.org_fk_key,
a.customer_fk_key,
a.functional_currency,
a.calendar_day_fk_key,
a.calendar_date,
sum(a.amt_invoiced_ar_b) amt_invoiced_ar_b,
sum(a.amt_invoiced_ar_g) amt_invoiced_ar_g,
sum(a.amt_rev_earned_b) amt_rev_earned_b,
sum(a.amt_rev_earned_g) amt_rev_earned_g,
sum(a.amt_cash_received_b) amt_cash_received_b,
sum(a.amt_cash_received_g) amt_cash_received_g,
sum(a.inv_created_cnt) inv_created_cnt,
sum(a.inv_revrec_cnt) inv_revrec_cnt,
sum(a.receipt_cnt) receipt_cnt,
sysdate, sysdate
from ( select set_of_books_fk_key,
operating_unit_fk_key,
org_fk_key,
customer_fk_key,
functional_currency,
calendar_day_fk_key,
calendar_date,
amt_invoiced_ar_b,
amt_invoiced_ar_g,
amt_rev_earned_b,
amt_rev_earned_g,
0 amt_cash_received_b,
0 amt_cash_received_g,
inv_created_cnt,
inv_revrec_cnt,
0 receipt_cnt
from '||g_fii_schema||'.fii_ar_edw_rev
where set_of_books_fk_key > 0
and operating_unit_fk_key > 0
and org_fk_key > 0
and customer_fk_key > 0
and calendar_day_fk_key > 0
union all';
select sob.fabk_fa_book_pk_key,
org.oper_operating_unit_pk_key,
org.orga_organization_pk_key,
cust.tprt_trade_partner_pk_key,
f.functional_currency,
t.cday_cal_day_pk_key,
f.calendar_day,
0 amt_invoiced_ar_b,
0 amt_invoiced_ar_g,
0 amt_rev_earned_b,
0 amt_rev_earned_g,
f.cash_b amt_cash_received_b,
f.cash_g amt_cash_received_g,
0 inv_created_cnt,
0 inv_revrec_cnt,
f.receipt_cnt
from '||g_fii_schema||'.fii_ar_oltp_cash f,
edw_time_m t,
edw_gl_book_m sob,
edw_organization_m org,
edw_trd_partner_m cust
where sob.fabk_fa_book_pk = to_char(f.sob_id)||''-''||f.instance_code
and sob.fabk_fa_book_pk_key > 0
and org.oper_operating_unit_pk_key > 0
and org.orga_organization_pk_key > 0
and cust.tprt_trade_partner_pk_key >= 0 /* bug 3290436 */
and t.cday_cal_day_pk_key > 0
and t.cday_cal_day_pk = to_char(f.calendar_day,''DD-MM-YYYY'')||''-''||
f.period_set||''-''||period_type||''-''||f.instance_code||''-CD''
and cust.tplo_tpartner_loc_pk = decode(f.customer_id, -1, ''NA_EDW'',
to_char(f.customer_id)||''-''||f.instance_code||''-CUST_ACCT-TPRT'' )
and org.orga_organization_pk = to_char(f.org_id)||''-''||f.instance_code';
select sob.fabk_fa_book_pk_key,
org.oper_operating_unit_pk_key,
org.orga_organization_pk_key,
cust.tprt_trade_partner_pk_key,
f.functional_currency,
t.cday_cal_day_pk_key,
f.calendar_day,
0 amt_invoiced_ar_b,
0 amt_invoiced_ar_g,
0 amt_rev_earned_b,
0 amt_rev_earned_g,
f.cash_b amt_cash_received_b,
f.cash_g amt_cash_received_g,
0 inv_created_cnt,
0 inv_revrec_cnt,
f.receipt_cnt
from '||g_fii_schema||'.fii_ar_oltp_cash f,
edw_time_m t,
edw_gl_book_m sob,
edw_organization_m org,
edw_trd_partner_m cust
where sob.fabk_fa_book_pk = to_char(f.sob_id)||''-''||f.instance_code
and sob.fabk_fa_book_pk_key > 0
and org.oper_operating_unit_pk_key > 0
and org.orga_organization_pk_key > 0
and cust.tprt_trade_partner_pk_key >= 0 /* bug 3290436 */
and t.cday_cal_day_pk_key > 0
and t.cday_cal_day_pk = to_char(f.calendar_day,''DD-MM-YYYY'')||''-''||
f.period_set||''-''||period_type||''-''||f.instance_code||''-CD''
and cust.tplo_tpartner_loc_pk = decode(f.customer_id, -1, ''NA_EDW'',
to_char(f.customer_id)||''-''||f.instance_code||''-CUST_ACCT-TPRT'' )
and org.orga_organization_pk = to_char(f.org_id)||''-''||f.instance_code';
select sob.fabk_fa_book_pk_key,
org.oper_operating_unit_pk_key,
org.orga_organization_pk_key,
cust.tprt_trade_partner_pk_key,
f.functional_currency,
t.cday_cal_day_pk_key,
f.calendar_day,
f.inv_b amt_invoiced_ar_b,
f.inv_g amt_invoiced_ar_g,
0 amt_rev_earned_b,
0 amt_rev_earned_g,
0 amt_cash_received_b,
0 amt_cash_received_g,
f.inv_created_cnt,
0 inv_revrec_cnt,
0 receipt_count
from '||g_fii_schema||'.fii_ar_oltp_inv f,
edw_time_m t,
edw_gl_book_m sob,
edw_organization_m org,
edw_trd_partner_m cust
where sob.fabk_fa_book_pk = to_char(f.sob_id)||''-''||f.instance_code
and sob.fabk_fa_book_pk_key > 0
and org.oper_operating_unit_pk_key > 0
and org.orga_organization_pk_key > 0
and cust.tprt_trade_partner_pk_key > 0
and t.cday_cal_day_pk_key > 0
and t.cday_cal_day_pk = to_char(f.calendar_day,''DD-MM-YYYY'')||''-''||
f.period_set||''-''||period_type||''-''||f.instance_code||''-CD''
and cust.tplo_tpartner_loc_pk = decode(f.customer_id, -1, ''NA_EDW'',
to_char(f.customer_id)||''-''||f.instance_code||''-CUST_ACCT-TPRT'' )
and org.orga_organization_pk = to_char(f.org_id)||''-''||f.instance_code) a
group by a.set_of_books_fk_key,
a.operating_unit_fk_key,
a.org_fk_key,
a.customer_fk_key,
a.functional_currency,
a.calendar_day_fk_key,
a.calendar_date
order by a.set_of_books_fk_key, a.operating_unit_fk_key, a.calendar_date';
select a.set_of_books_fk_key,
a.operating_unit_fk_key,
a.functional_currency,
a.calendar_day_fk_key,
a.calendar_date,
sum(a.amt_booked_b) amt_booked_b,
sum(a.amt_booked_g) amt_booked_g,
sum(a.amt_shipped_b) amt_shipped_b,
sum(a.amt_shipped_g) amt_shipped_g,
sum(a.amt_invoiced_oe_b) amt_invoiced_oe_b,
sum(a.amt_invoiced_oe_g) amt_invoiced_oe_g,
sum(a.amt_invoiced_ar_b) amt_invoiced_ar_b,
sum(a.amt_invoiced_ar_g) amt_invoiced_ar_g,
sum(a.amt_rev_earned_b) amt_rev_earned_b,
sum(a.amt_rev_earned_g) amt_rev_earned_g,
sum(a.amt_cash_received_b) amt_cash_received_b,
sum(a.amt_cash_received_g) amt_cash_received_g,
sum(a.order_count) order_count
from ( select set_of_books_fk_key set_of_books_fk_key,
operating_unit_fk_key operating_unit_fk_key,
functional_currency functional_currency,
calendar_day_fk_key calendar_day_fk_key,
calendar_date calendar_date,
amt_booked_b amt_booked_b,
amt_booked_g amt_booked_g,
amt_shipped_b amt_shipped_b,
amt_shipped_g amt_shipped_g,
amt_invoiced_oe_b amt_invoiced_oe_b,
amt_invoiced_oe_g amt_invoiced_oe_g,
0 amt_invoiced_ar_b,
0 amt_invoiced_ar_g,
0 amt_rev_earned_b,
0 amt_rev_earned_g,
0 amt_cash_received_b,
0 amt_cash_received_g,
order_count order_count
from fii_ar_operations_summary
union all
select set_of_books_fk_key,
org_fk_key,
functional_currency,
calendar_day_fk_key,
calendar_date,
0 amt_booked_b,
0 amt_booked_g,
0 amt_shipped_b,
0 amt_shipped_g,
0 amt_invoiced_oe_b,
0 amt_invoiced_oe_g,
amt_invoiced_ar_b,
amt_invoiced_ar_g,
amt_rev_earned_b,
amt_rev_earned_g,
amt_cash_received_b,
amt_cash_received_g,
0 order_count
from fii_ar_oper_cust_summary_f) a
group by a.set_of_books_fk_key,
a.operating_unit_fk_key,
a.functional_currency,
a.calendar_day_fk_key,
a.calendar_date
order by a.set_of_books_fk_key,
a.operating_unit_fk_key,
a.calendar_date';
insert into fii_ar_operations_summary T (
set_of_books_fk_key,
operating_unit_fk_key,
calendar_day_fk_key,
calendar_date,
functional_currency,
creation_date,
last_update_date,
amt_booked_b,
amt_booked_g,
amt_shipped_b,
amt_shipped_g,
amt_invoiced_oe_b,
amt_invoiced_oe_g,
amt_invoiced_ar_b,
amt_invoiced_ar_g,
amt_rev_earned_b,
amt_rev_earned_g,
amt_cash_received_b,
amt_cash_received_g,
order_count)
select
set_of_books_fk_key,
operating_unit_fk_key,
calendar_day_fk_key,
calendar_date,
functional_currency,
sysdate,
sysdate,
amt_booked_b,
amt_booked_g,
amt_shipped_b,
amt_shipped_g,
amt_invoiced_oe_b,
amt_invoiced_oe_g,
amt_invoiced_ar_b,
amt_invoiced_ar_g,
amt_rev_earned_b,
amt_rev_earned_g,
amt_cash_received_b,
amt_cash_received_g,
order_count
from '||g_fii_schema||'.fii_ar_operations_summary_new S
where set_of_books_fk_key > 0
and operating_unit_fk_key > 0
and calendar_day_fk_key > 0';