[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_RISK_INDICATOR_F'
AND owner = g_fii_schema;
select name
into l_db_name1
from v$database;
l_stmt := 'select sob_id from fii_ar_oltp_open_inv_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_OPEN_INV T
(instance_code,
org_id,
sob_id,
customer_site_id,
receivable_g,
receivable_b,
receivable_t,
unapp_receipt_g,
unapp_receipt_b,
unapp_receipt_t,
functional_currency,
invoice_currency,
invoice_number,
installment_number,
invoice_date,
due_date,
type)
select /*+ DRIVING_SITE(INV) */
instance_code,
org_id,
sob_id,
customer_site_id,
receivable_g,
receivable_b,
receivable_t,
unapp_receipt_g,
unapp_receipt_b,
unapp_receipt_t,
functional_currency,
invoice_currency,
invoice_number,
installment_number,
invoice_date,
due_date,
type
from
fii_ar_oltp_open_inv_v';
l_stmt := 'insert into fii_ar_open_installment_f (
functional_currency_fk_key,
set_of_books_fk_key,
operating_unit_fk_key,
customer_fk_key,
receivable_g,
receivable_b,
receivable_t,
unapp_receipt_g,
unapp_receipt_b,
unapp_receipt_t,
functional_currency,
invoice_currency,
customer_name,
invoice_number,
installment_number,
invoice_date,
due_date,
date_of_snapshot,
age_bucket,
type,
creation_date,
last_update_date)
select curr.crnc_currency_pk_key,
sob.fabk_fa_book_pk_key,
org.oper_operating_unit_pk_key,
cust.tprt_trade_partner_pk_key,
f.receivable_g,
f.receivable_b,
f.receivable_t,
f.unapp_receipt_g,
f.unapp_receipt_b,
f.unapp_receipt_t,
f.functional_currency,
f.invoice_currency,
cust.tprt_name,
f.invoice_number,
f.installment_number,
f.invoice_date,
f.due_date,
trunc(sysdate),
case when (f.due_date >= trunc(sysdate)) then 1
when (f.due_date between trunc(sysdate)-30 and trunc(sysdate)-1) then 2
when (f.due_date between trunc(sysdate)-60 and trunc(sysdate)-31) then 3
when (f.due_date between trunc(sysdate)-90 and trunc(sysdate)-61) then 4
else 5 end,
f.type,
trunc(sysdate),
trunc(sysdate)
from '||g_fii_schema||'.fii_ar_oltp_open_inv f,
edw_gl_book_m sob,
edw_currency_m curr,
edw_organization_m org,
edw_trd_partner_m cust
where sob.fabk_fa_book_pk = to_char(f.sob_id)||''-''||f.instance_code
and curr.crnc_currency_pk_key > 0
and sob.fabk_fa_book_pk_key > 0
and org.oper_operating_unit_pk_key > 0
and cust.tprt_trade_partner_pk_key > 0
and cust.tplo_tpartner_loc_pk = decode(f.customer_site_id, -1, ''NA_EDW'',
to_char(f.customer_site_id)||''-''||f.instance_code||''-CUST_SITE_USE'' )
and org.orga_organization_pk = to_char(f.org_id)||''-''||f.instance_code
and curr.crnc_currency_pk = f.functional_currency' ;
insert into fii_ar_risk_indicator_f (
functional_currency_fk_key,
set_of_books_fk_key,
operating_unit_fk_key,
operating_unit_name,
ship_bklg_amt_b,
ship_bklg_amt_g,
dlqt_bklg_amt_b,
dlqt_bklg_amt_g,
open_rec_amt_b,
open_rec_amt_g,
pastdue_rec_amt_b,
pastdue_rec_amt_g,
date_of_snapshot,
creation_date,
last_update_date)
select
a.functional_currency_fk_key,
a.set_of_books_fk_key,
a.operating_unit_fk_key,
a.operating_unit_name,
sum(a.ship_bklg_amt_b),
sum(a.ship_bklg_amt_g),
sum(a.dlqt_bklg_amt_b),
sum(a.dlqt_bklg_amt_g),
sum(open_rec_amt_b),
sum(open_rec_amt_g),
sum(pastdue_rec_amt_b),
sum(pastdue_rec_amt_g),
trunc(sysdate),
trunc(sysdate),
trunc(sysdate)
from ( select a.functional_currency_fk_key,
a.set_of_books_fk_key,
a.operating_unit_fk_key,
b.name operating_unit_name,
0 ship_bklg_amt_b,
0 ship_bklg_amt_g,
0 dlqt_bklg_amt_b,
0 dlqt_bklg_amt_g,
a.receivable_b open_rec_amt_b,
a.receivable_g open_rec_amt_g,
decode(a.age_bucket, 1, 0, a.receivable_b) pastdue_rec_amt_b,
decode(a.age_bucket, 1, 0, a.receivable_g) pastdue_rec_amt_g
from fii_ar_open_installment_f a,
edw_orga_oper_unit_ltc b
where b.operating_unit_pk_key = a.operating_unit_fk_key
and a.functional_currency_fk_key > 0
and a.operating_unit_fk_key > 0
and a.set_of_books_fk_key > 0
union all
select functional_currency_fk_key,
set_of_books_fk_key,
operating_unit_fk_key,
operating_unit_name,
ship_bklg_amt_b,
ship_bklg_amt_g,
dlqt_bklg_amt_b,
dlqt_bklg_amt_g,
0 open_rec_amt_b,
0 open_rec_amt_g,
0 pastdue_rec_amt_b,
0 pastdue_rec_amt_g
from isc_edw_backlog_sum1_f
where functional_currency_fk_key > 0
and set_of_books_fk_key > 0
and operating_unit_fk_key > 0) a
group by a.functional_currency_fk_key,
a.set_of_books_fk_key,
a.operating_unit_fk_key,
a.operating_unit_name
order by a.set_of_books_fk_key,
a.operating_unit_fk_key';