eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
MATERIALIZED VIEW: APPS.FII_AP_PMT_SCHEDULES_MV

Source


SELECT
decode(grouping_id(time.ent_year_id, time.ent_qtr_id, time.ent_period_id, time.week_id, time.report_date_julian),
        0, time.report_date_julian,
        1, time.week_id,
        3, time.ent_period_id,
        7, time.ent_qtr_id,
        15, time.ent_year_id) time_id,
decode(grouping_id(time.ent_year_id, time.ent_qtr_id, time.ent_period_id, time.week_id, time.report_date_julian),
       0, 1,
       1, 16,
       3, 32,
       7, 64,
       15, 128) period_type_id,
time.ent_year_id,
time.ent_qtr_id,
time.ent_period_id,
time.week_id,
time.report_date_julian,
sched.ORG_ID ORG_ID,
sched.SUPPLIER_ID SUPPLIER_ID,
sched.INVOICE_ID INVOICE_ID,
sched.PAYMENT_NUM PAYMENT_NUM,
sched.BASE_CURRENCY_CODE BASE_CURRENCY_CODE,
sched.DUE_DATE DUE_DATE,
inv.invoice_currency_code invoice_currency_code,
SUM(decode(sched.ACTION,'CREATION',sched.AMOUNT_REMAINING,0)) ORIGINAL_AMT,
SUM(decode(sched.ACTION,'CREATION',sched.AMOUNT_REMAINING_B,0)) ORIGINAL_AMT_B,
SUM(decode(sched.ACTION,'CREATION',sched.PRIM_AMOUNT_REMAINING,0)) PRIM_ORIGINAL_AMT,
SUM(decode(sched.ACTION,'CREATION',sched.SEC_AMOUNT_REMAINING,0)) SEC_ORIGINAL_AMT,
SUM(PAST_DUE_AMOUNT) PAST_DUE_AMOUNT,
SUM(PAST_DUE_AMOUNT_B) PAST_DUE_AMOUNT_B,
SUM(PRIM_PAST_DUE_AMOUNT) PRIM_PAST_DUE_AMOUNT,
SUM(SEC_PAST_DUE_AMOUNT) SEC_PAST_DUE_AMOUNT,
SUM(sched.AMOUNT_REMAINING) AMOUNT_REMAINING,
SUM(sched.AMOUNT_REMAINING_B) AMOUNT_REMAINING_B ,
SUM(sched.PRIM_AMOUNT_REMAINING) PRIM_AMOUNT_REMAINING,
SUM(sched.SEC_AMOUNT_REMAINING) SEC_AMOUNT_REMAINING,
SUM(sched.DISCOUNT_AVAILABLE) DISCOUNT_AVAILABLE,
SUM(sched.DISCOUNT_AVAILABLE_B) DISCOUNT_AVAILABLE_B,
SUM(sched.PRIM_DISCOUNT_AVAILABLE) PRIM_DISCOUNT_AVAILABLE,
SUM(sched.SEC_DISCOUNT_AVAILABLE) SEC_DISCOUNT_AVAILABLE,
SUM(sched.DISCOUNT_LOST) DISCOUNT_LOST,
SUM(sched.DISCOUNT_LOST_B) DISCOUNT_LOST_B,
SUM(sched.PRIM_DISCOUNT_LOST) PRIM_DISCOUNT_LOST,
SUM(sched.SEC_DISCOUNT_LOST ) SEC_DISCOUNT_LOST,
SUM(sched.DISCOUNT_TAKEN) DISCOUNT_TAKEN,
SUM(sched.DISCOUNT_TAKEN_B) DISCOUNT_TAKEN_B,
SUM(sched.PRIM_DISCOUNT_TAKEN) PRIM_DISCOUNT_TAKEN ,
SUM(sched.SEC_DISCOUNT_TAKEN) SEC_DISCOUNT_TAKEN,
SUM(decode(INV.INVOICE_TYPE,'PREPAYMENT',sched.AMOUNT_REMAINING,0)) AMOUNT_REMAINING_PP,
SUM(decode(INV.INVOICE_TYPE,'PREPAYMENT',sched.AMOUNT_REMAINING_B,0)) AMOUNT_REMAINING_B_PP,
SUM(decode(INV.INVOICE_TYPE,'PREPAYMENT',sched.PRIM_AMOUNT_REMAINING,0)) PRIM_AMOUNT_REMAINING_PP,
SUM(decode(INV.INVOICE_TYPE,'PREPAYMENT',sched.SEC_AMOUNT_REMAINING,0)) SEC_AMOUNT_REMAINING_PP,
SUM(decode(INV.INVOICE_TYPE,'PREPAYMENT',0,sched.AMOUNT_REMAINING)) AMOUNT_REMAINING_PMT,
SUM(decode(INV.INVOICE_TYPE,'PREPAYMENT',0,sched.AMOUNT_REMAINING_B)) AMOUNT_REMAINING_B_PMT,
SUM(decode(INV.INVOICE_TYPE,'PREPAYMENT',0,sched.PRIM_AMOUNT_REMAINING)) PRIM_AMOUNT_REMAINING_PMT,
SUM(decode(INV.INVOICE_TYPE,'PREPAYMENT',0,sched.SEC_AMOUNT_REMAINING)) SEC_AMOUNT_REMAINING_PMT,
grouping_id(sched.ORG_ID,
sched.SUPPLIER_ID,
sched.INVOICE_ID,
sched.PAYMENT_NUM,
sched.BASE_CURRENCY_CODE,
inv.invoice_currency_code,
SCHED.DUE_DATE,
time.ent_year_id, time.ent_qtr_id, time.ent_period_id, time.week_id, time.report_date_julian) GID,
COUNT(decode(sched.ACTION,'CREATION',sched.AMOUNT_REMAINING,0)) ORIGINAL_AMT_CT,
COUNT(decode(sched.ACTION,'CREATION',sched.AMOUNT_REMAINING_B,0)) ORIGINAL_AMT_B_CT,
COUNT(decode(sched.ACTION,'CREATION',sched.PRIM_AMOUNT_REMAINING,0)) PRIM_ORIGINAL_AMT_CT,
COUNT(decode(sched.ACTION,'CREATION',sched.SEC_AMOUNT_REMAINING,0)) SEC_ORIGINAL_AMT_CT,
COUNT(PAST_DUE_AMOUNT) PAST_DUE_AMOUNT_CT,
COUNT(PAST_DUE_AMOUNT_B) PAST_DUE_AMOUNT_B_CT,
COUNT(PRIM_PAST_DUE_AMOUNT) PRIM_PAST_DUE_AMOUNT_CT,
COUNT(SEC_PAST_DUE_AMOUNT) SEC_PAST_DUE_AMOUNT_CT,
COUNT(sched.AMOUNT_REMAINING) AMOUNT_REMAINING_CT,
COUNT(sched.AMOUNT_REMAINING_B) AMOUNT_REMAINING_B_CT ,
COUNT(sched.PRIM_AMOUNT_REMAINING) PRIM_AMOUNT_REMAINING_CT,
COUNT(sched.SEC_AMOUNT_REMAINING) SEC_AMOUNT_REMAINING_CT,
COUNT(sched.DISCOUNT_AVAILABLE) DISCOUNT_AVAILABLE_CT,
COUNT(sched.DISCOUNT_AVAILABLE_B) DISCOUNT_AVAILABLE_B_CT,
COUNT(sched.PRIM_DISCOUNT_AVAILABLE) PRIM_DISCOUNT_AVAILABLE_CT,
COUNT(sched.SEC_DISCOUNT_AVAILABLE) SEC_DISCOUNT_AVAILABLE_CT,
COUNT(sched.DISCOUNT_LOST) DISCOUNT_LOST_CT,
COUNT(sched.DISCOUNT_LOST_B) DISCOUNT_LOST_B_CT,
COUNT(sched.PRIM_DISCOUNT_LOST) PRIM_DISCOUNT_LOST_CT,
COUNT(sched.SEC_DISCOUNT_LOST ) SEC_DISCOUNT_LOST_CT,
COUNT(sched.DISCOUNT_TAKEN) DISCOUNT_TAKEN_CT,
COUNT(sched.DISCOUNT_TAKEN_B) DISCOUNT_TAKEN_B_CT,
COUNT(sched.PRIM_DISCOUNT_TAKEN) PRIM_DISCOUNT_TAKEN_CT,
COUNT(sched.SEC_DISCOUNT_TAKEN) SEC_DISCOUNT_TAKEN_CT,
COUNT(decode(INV.INVOICE_TYPE,'PREPAYMENT',sched.AMOUNT_REMAINING,0)) AMOUNT_REMAINING_PP_CT,
COUNT(decode(INV.INVOICE_TYPE,'PREPAYMENT',sched.AMOUNT_REMAINING_B,0)) AMOUNT_REMAINING_B_PP_CT,
COUNT(decode(INV.INVOICE_TYPE,'PREPAYMENT',sched.PRIM_AMOUNT_REMAINING,0)) PRIM_AMOUNT_REMAINING_PP_CT,
COUNT(decode(INV.INVOICE_TYPE,'PREPAYMENT',sched.SEC_AMOUNT_REMAINING,0)) SEC_AMOUNT_REMAINING_PP_CT,
COUNT(decode(INV.INVOICE_TYPE,'PREPAYMENT',0,sched.AMOUNT_REMAINING)) AMOUNT_REMAINING_PMT_CT,
COUNT(decode(INV.INVOICE_TYPE,'PREPAYMENT',0,sched.AMOUNT_REMAINING_B)) AMOUNT_REMAINING_B_PMT_CT,
COUNT(decode(INV.INVOICE_TYPE,'PREPAYMENT',0,sched.PRIM_AMOUNT_REMAINING)) PRIM_AMOUNT_REMAINING_PMT_CT,
COUNT(decode(INV.INVOICE_TYPE,'PREPAYMENT',0,sched.SEC_AMOUNT_REMAINING)) SEC_AMOUNT_REMAINING_PMT_CT,
COUNT(*) ct
FROM FII.FII_AP_PAY_SCHED_B sched, FII.FII_TIME_DAY time,
FII.FII_AP_INVOICE_B inv
where sched.period_type_id = 1
and sched.action_date = time.report_date
and sched.action in ('CREATION','DISCOUNT','PREPAYMENT','PAYMENT','WITHHOLDING','TAX','DUE')
and sched.invoice_id=inv.invoice_id
GROUP BY sched.ORG_ID,
sched.SUPPLIER_ID,
sched.INVOICE_ID,
sched.PAYMENT_NUM,
sched.BASE_CURRENCY_CODE,
SCHED.DUE_DATE,
inv.invoice_currency_code,
rollup (time.ent_year_id, time.ent_qtr_id, time.ent_period_id, time.week_id, time.report_date_julian)