[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_state := 'Insert into FII_AP_DISCOUNTS_SUMMARY';
l_stmt := 'insert into '||g_fii_schema||'.FII_AP_DISCOUNTS_SUMMARY (
OPERATING_UNIT_PK_KEY,
OPERATING_UNIT_NAME,
TRADING_PARTNER_PK_KEY,
TRADING_PARTNER_NAME,
RECORD_ID,
INVOICE_NUM,
INVOICE_DATE,
INVOICE_UNIQUE_IDENTIFIER,
INVOICE_AMOUNT,
DISCOUNT_AVAILABLE,
DISCOUNT_LOST,
DISCOUNT_DATE,
RECORD_TYPE)
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,
invp.inv_payment_pk RECORD_ID,
invp.inv_num INVOICE_NUM,
to_date(NULL) INVOICE_DATE,
invp.inv_fk_key INVOICE_UNIQUE_IDENTIFIER,
NVL(invp.payment_amt_g,0) INVOICE_AMOUNT,
0 DISCOUNT_AVAILABLE,
NVL(invp.disc_amt_lost_g,0) DISCOUNT_LOST,
to_date(NULL) DISCOUNT_DATE,
''L'' RECORD_TYPE
from fii_ap_inv_paymts_f invp,
edw_organization_m org,
edw_trd_partner_m partner
WHERE invp.check_date between to_date('''
||to_char(g_cur_qtr_start_date, 'DD-MM-YYYY')
||''', ''DD-MM-YYYY'') and to_date('''
|| to_char(g_today, 'DD-MM-YYYY') || ''', ''DD-MM-YYYY'')
AND invp.org_fk_key = org.orga_organization_pk_key
AND invp.org_fk_key > 0
AND invp.supplier_fk_key = partner.tplo_tpartner_loc_pk_key
AND invp.supplier_fk_key > 0
AND NVL(invp.disc_amt_lost_g,0) > 0
UNION ALL
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 RECORD_ID,
schp.invoice_num INVOICE_NUM,
time.calendar_date INVOICE_DATE,
schp.inv_fk_key INVOICE_UNIQUE_IDENTIFIER,
decode(schp.inv_amt_having_disc_g,0, schp.inv_amt_not_having_disc_g,
schp.inv_amt_having_disc_g) INVOICE_AMOUNT,
NVL(schp.remaining_disc_amt_at_risk_g,0) DISCOUNT_AVAILABLE,
0 DISCOUNT_LOST,
DECODE(sign(first_disc_date-to_date('''||to_char(g_today,'DD-MM-YYYY')||''',''DD-MM-YYYY'')),1,first_disc_date,
0,first_disc_date,
(DECODE(sign(second_disc_date-to_date('''||to_char(g_today,'DD-MM-YYYY')||''',''DD-MM-YYYY'')),1,second_disc_date,
0, second_disc_date,third_disc_date))) DISCOUNT_DATE,
''R'' RECORD_TYPE
from fii_ap_sch_paymts_f schp,
edw_organization_m org,
edw_time_cal_day_ltc time,
edw_trd_partner_m partner
WHERE 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 NVL(schp.remaining_disc_amt_at_risk_g,0) > 0
AND schp.inv_date_fk_key = time.CAL_DAY_PK_KEY
AND (schp.first_disc_date between to_date(''' ||to_char(g_today,'DD-MM-YYYY')||''',''DD-MM-YYYY'')
and to_date('''||to_char(g_cur_qtr_end_date,'DD-MM-YYYY')||''',''DD-MM-YYYY'') OR
schp.second_disc_date between to_date(''' ||to_char(g_today,'DD-MM-YYYY')||''',''DD-MM-YYYY'')
and to_date('''||to_char(g_cur_qtr_end_date,'DD-MM-YYYY')||''',''DD-MM-YYYY'') OR
schp.third_disc_date between to_date(''' ||to_char(g_today,'DD-MM-YYYY')|| ''',''DD-MM-YYYY'')
and to_date('''||to_char(g_cur_qtr_end_date,'DD-MM-YYYY')||''',''DD-MM-YYYY''))';
edw_log.put_line('Inserted ' || sql%rowcount || ' rows into FII_AP_DISCOUNTS_SUMMARY');
SELECT tablespace_name
INTO g_tablespace
FROM all_tables
WHERE table_name = 'FII_AP_DISCOUNTS_SUMMARY'
AND owner = g_fii_schema;