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_HATY_XB_MV

Source


select /* 12.0: bug#4526784 */ org_id,
       supplier_id,
       time_id,
       period_type_id,
       grouping_id(org_id, supplier_id, time_id, period_type_id) gid,
       sum(inv_on_hold_amt_wtd_b) inv_on_hold_amt_wtd_b,
       sum(inv_on_hold_amt_mtd_b) inv_on_hold_amt_mtd_b,
       sum(inv_on_hold_amt_qtd_b) inv_on_hold_amt_qtd_b,
       sum(inv_on_hold_amt_ytd_b) inv_on_hold_amt_ytd_b,
       sum(inv_on_hold_amt_wtd_prim_g) inv_on_hold_amt_wtd_prim_g,
       sum(inv_on_hold_amt_mtd_prim_g) inv_on_hold_amt_mtd_prim_g,
       sum(inv_on_hold_amt_qtd_prim_g) inv_on_hold_amt_qtd_prim_g,
       sum(inv_on_hold_amt_ytd_prim_g) inv_on_hold_amt_ytd_prim_g,
       sum(inv_on_hold_amt_wtd_sec_g) inv_on_hold_amt_wtd_sec_g,
       sum(inv_on_hold_amt_mtd_sec_g) inv_on_hold_amt_mtd_sec_g,
       sum(inv_on_hold_amt_qtd_sec_g) inv_on_hold_amt_qtd_sec_g,
       sum(inv_on_hold_amt_ytd_sec_g) inv_on_hold_amt_ytd_sec_g,
       sum(inv_on_hold_count_wtd) inv_on_hold_count_wtd,
       sum(inv_on_hold_count_mtd) inv_on_hold_count_mtd,
       sum(inv_on_hold_count_qtd) inv_on_hold_count_qtd,
       sum(inv_on_hold_count_ytd) inv_on_hold_count_ytd,
       sum(days_on_hold_wtd) days_on_hold_wtd,
       sum(days_on_hold_mtd) days_on_hold_mtd,
       sum(days_on_hold_qtd) days_on_hold_qtd,
       sum(days_on_hold_ytd) days_on_hold_ytd,
       sum(hold_count) no_of_holds_placed
from
(select f.org_id,
        f.supplier_id,
        decode(grouping_id(fday.ent_year_id, fday.ent_qtr_id, fday.ent_period_id, fday.week_id, fday.report_date_julian),
        0, fday.report_date_julian, 1, fday.week_id, 3, fday.ent_period_id, 7, fday.ent_qtr_id, 15, fday.ent_year_id) time_id,
        decode(grouping_id(fday.ent_year_id, fday.ent_qtr_id, fday.ent_period_id, fday.week_id, fday.report_date_julian),
        0, 1, 1, 16, 3, 32, 7, 64, 15, 128) period_type_id,
        sum(case when f.wtd_rank=1 then f.base_amount else 0 end) inv_on_hold_amt_wtd_b,
        sum(case when f.mtd_rank=1 then f.base_amount else 0 end) inv_on_hold_amt_mtd_b,
        sum(case when f.qtd_rank=1 then f.base_amount else 0 end) inv_on_hold_amt_qtd_b,
        sum(case when f.ytd_rank=1 then f.base_amount else 0 end) inv_on_hold_amt_ytd_b,
        sum(case when f.wtd_rank=1 then f.prim_amount else 0 end) inv_on_hold_amt_wtd_prim_g,
        sum(case when f.mtd_rank=1 then f.prim_amount else 0 end) inv_on_hold_amt_mtd_prim_g,
        sum(case when f.qtd_rank=1 then f.prim_amount else 0 end) inv_on_hold_amt_qtd_prim_g,
        sum(case when f.ytd_rank=1 then f.prim_amount else 0 end) inv_on_hold_amt_ytd_prim_g,
        sum(case when f.wtd_rank=1 then f.sec_amount else 0 end) inv_on_hold_amt_wtd_sec_g,
        sum(case when f.mtd_rank=1 then f.sec_amount else 0 end) inv_on_hold_amt_mtd_sec_g,
        sum(case when f.qtd_rank=1 then f.sec_amount else 0 end) inv_on_hold_amt_qtd_sec_g,
        sum(case when f.ytd_rank=1 then f.sec_amount else 0 end) inv_on_hold_amt_ytd_sec_g,
        (case when f.wtd_rank=1 then count(distinct f.invoice_id) else 0 end) inv_on_hold_count_wtd,
        (case when f.mtd_rank=1 then count(distinct f.invoice_id) else 0 end) inv_on_hold_count_mtd,
        (case when f.qtd_rank=1 then count(distinct f.invoice_id) else 0 end) inv_on_hold_count_qtd,
        (case when f.ytd_rank=1 then count(distinct f.invoice_id) else 0 end) inv_on_hold_count_ytd,
        (case when f.wtd_rank=1 then hold.days_on_hold else 0 end) days_on_hold_wtd,
        (case when f.mtd_rank=1 then hold.days_on_hold else 0 end) days_on_hold_mtd,
        (case when f.qtd_rank=1 then hold.days_on_hold else 0 end) days_on_hold_qtd,
        (case when f.ytd_rank=1 then hold.days_on_hold else 0 end) days_on_hold_ytd,
        0 hold_count
 from
 (select f.org_id org_id,
         f.supplier_id supplier_id,
         f.invoice_id,
         f.time_id time_id,
         rank() over (partition by f.invoice_id, time.week_id order by f.hold_date) wtd_rank,
         rank() over (partition by f.invoice_id, time.ent_period_id order by f.hold_date) mtd_rank,
         rank() over (partition by f.invoice_id, time.ent_qtr_id order by f.hold_date) qtd_rank,
         rank() over (partition by f.invoice_id, time.ent_year_id order by f.hold_date) ytd_rank,
         inv.base_amount base_amount,
         inv.prim_amount prim_amount,
         inv.sec_amount sec_amount
  from (select distinct invoice_id, hold_date, org_id, supplier_id, time_id from FII.FII_AP_INV_HOLDS_B) f, FII.FII_AP_INVOICE_B inv, FII.FII_TIME_DAY time
  where inv.entered_date <= f.hold_date
  and f.invoice_id = inv.invoice_id
  and f.hold_date = time.report_date) f,
  (select invoice_id, sum(days_on_hold) days_on_hold from FII_AP_HHIST_IB_MV group by invoice_id) hold,
  FII.FII_TIME_DAY fday
 where f.time_id = fday.report_date_julian
 and hold.invoice_id = f.invoice_id
 group by f.org_id, f.supplier_id, f.invoice_id, f.wtd_rank, f.mtd_rank, f.qtd_rank, f.ytd_rank, hold.days_on_hold,
          rollup (fday.ent_year_id, fday.ent_qtr_id, fday.ent_period_id, fday.week_id, fday.report_date_julian)
 union all
 select f.org_id org_id,
        f.supplier_id supplier_id,
        decode(grouping_id(fday.ent_year_id, fday.ent_qtr_id, fday.ent_period_id, fday.week_id, fday.report_date_julian),
        0, fday.report_date_julian, 1, fday.week_id, 3, fday.ent_period_id, 7, fday.ent_qtr_id, 15, fday.ent_year_id) time_id,
        decode(grouping_id(fday.ent_year_id, fday.ent_qtr_id, fday.ent_period_id, fday.week_id, fday.report_date_julian),
        0, 1, 1, 16, 3, 32, 7, 64, 15, 128) period_type_id,
        0 inv_on_hold_amt_wtd_b,
        0 inv_on_hold_amt_mtd_b,
        0 inv_on_hold_amt_qtd_b,
        0 inv_on_hold_amt_ytd_b,
        0 inv_on_hold_amt_wtd_prim_g,
        0 inv_on_hold_amt_mtd_prim_g,
        0 inv_on_hold_amt_qtd_prim_g,
        0 inv_on_hold_amt_ytd_prim_g,
        0 inv_on_hold_amt_wtd_sec_g,
        0 inv_on_hold_amt_mtd_sec_g,
        0 inv_on_hold_amt_qtd_sec_g,
        0 inv_on_hold_amt_ytd_sec_g,
        0 inv_on_hold_count_wtd,
        0 inv_on_hold_count_mtd,
        0 inv_on_hold_count_qtd,
        0 inv_on_hold_count_ytd,
        0 days_on_hold_wtd,
        0 days_on_hold_mtd,
        0 days_on_hold_qtd,
        0 days_on_hold_ytd,
        sum(1) hold_count
 from FII.FII_AP_INV_HOLDS_B f, FII.FII_TIME_DAY fday
 where f.time_id = fday.report_date_julian
 group by f.org_id, f.supplier_id,
          rollup (fday.ent_year_id, fday.ent_qtr_id, fday.ent_period_id, fday.week_id, fday.report_date_julian))
 group by grouping sets((org_id, supplier_id, time_id, period_type_id),
                       (org_id, time_id, period_type_id))