[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_state := 'Insert into FII_AP_TRANS_BACKLOG_SUMMARY';
l_stmt := 'insert into '||g_fii_schema||'.FII_AP_TRANS_BACKLOG_SUMMARY (
operating_unit_pk_key,
operating_unit_name,
trading_partner_pk_key,
trading_partner_name,
sch_payment_id,
open_payment_amount,
invoice_pk_key,
invoice_num,
invoice_amount,
payment_due_date,
invoice_date,
days_outstanding)
select org.oper_operating_unit_pk_key OPERATING_UNIT_PK_KEY,
org.oper_name OPERATING_UNIT_NAME,
partner.tprt_trade_partner_pk_key TRADING_PARTNER_PK_KEY,
partner.tprt_name TRADING_PARTNER_NAME,
schp.sch_payment_pk_key SCH_PAYMENT_ID,
nvl(schp.REMAINING_INV_AMT_AT_RISK_G,0) OPEN_PAYMENT_AMOUNT,
schp.inv_fk_key INVOICE_PK_KEY,
schp.invoice_num INVOICE_NUM,
decode(schp.inv_amt_having_disc_g,0, schp.inv_amt_not_having_disc_g,
schp.inv_amt_having_disc_g) INVOICE_AMOUNT,
schp.due_date PAYMENT_DUE_DATE,
time.calendar_date INVOICE_DATE,
round(to_date(''' || to_char(g_today,'DD-MM-YYYY') || ''',''DD-MM-YYYY'') - schp.due_date) days_outstanding
from fii_ap_sch_paymts_f schp,
edw_organization_m org,
edw_time_cal_day_ltc time,
edw_trd_partner_m partner
WHERE schp.REMAINING_INV_AMT_AT_RISK_G > 0
AND schp.payment_status_flag <> ''Y''
AND schp.due_date <= to_date('''||to_char(g_today,'DD-MM-YYYY')||''',''DD-MM-YYYY'')
AND schp.inv_date_fk_key = time.CAL_DAY_PK_KEY
AND schp.org_fk_key = org.ORGA_ORGANIZATION_PK_KEY
AND schp.org_fk_key > 0
AND schp.supplier_fk_key = partner.tplo_tpartner_loc_pk_key
AND schp.supplier_fk_key > 0
AND schp.inv_fk_key > 0';
edw_log.put_line('Inserted ' || sql%rowcount || ' rows into FII_AP_TRANS_BACKLOG_SUMMARY');
SELECT tablespace_name
INTO g_tablespace
FROM all_tables
WHERE table_name = 'FII_AP_TRANS_BACKLOG_SUMMARY'
AND owner = g_fii_schema;