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.POA_RTX_BS_MV

Source


SELECT	/* 12.0: bug#4526784 */ grouping_id(commodity_id, category_id,
		    po_item_id, org_id, supplier_id,
		    supplier_site_id, receiving_org_id,
		    buyer_id, reason_id, base_uom, ent_year_id,
		    ent_qtr_id,ent_period_id,week_id,day_id) grp_id,
	 decode(grouping_id(ent_year_id,ent_qtr_id,ent_period_id,week_id,day_id),0,day_id,1,week_id,3,ent_period_id,7,ent_qtr_id,15,ent_year_id) time_id,
	 decode(
           grouping_id(ent_year_id,ent_qtr_id, ent_period_id,week_id,day_id),
           0,1,
           1,16,
           3,32,
           7,64,
           15,128) period_type_id,
	 grouping_id(supplier_id, category_id, po_item_id) aggregation_level,
	 f.commodity_id,
	 f.category_id,
	 f.po_item_id,
	 f.org_id,
	 f.supplier_id,
	 f.supplier_site_id,
	 f.receiving_org_id,
	 f.buyer_id,
	 f.reason_id,
	 f.base_uom,
         f.ent_year_id,
	 f.ent_qtr_id,
	 f.ent_period_id,
	 f.week_id,
	 f.day_id,
	 sum(case when transaction_flag = 'RECEIVE' then amount_b end) amt_receipt_and_dropship_b,
	 sum(case when transaction_flag = 'RECEIVE' then amount_g end) amt_receipt_and_dropship_g,
	 sum(case when transaction_flag = 'RECEIVE' then amount_sg end) amt_receipt_and_dropship_sg,
	 sum(case when transaction_flag = 'RECEIVE' and dropship_type_code=0 then amount_b end) amt_receipt_b,
	 sum(case when transaction_flag = 'RECEIVE' and dropship_type_code=0 then amount_g end) amt_receipt_g,
	 sum(case when transaction_flag = 'RECEIVE' and dropship_type_code=0 then amount_sg end) amt_receipt_sg,
	 sum(case when (mx_id = 2 and transaction_flag = 'RETURN TO VENDOR') then amount_b end) amt_return_b,
	 sum(case when (mx_id = 2 and transaction_flag = 'RETURN TO VENDOR') then amount_g end) amt_return_g,
	 sum(case when (mx_id = 2 and transaction_flag = 'RETURN TO VENDOR') then amount_sg end) amt_return_sg,
	 sum(case when (mx_id = 1 and transaction_flag = 'RETURN TO VENDOR') then amount_b end) amt_receipt_return_b,
	 sum(case when (mx_id = 1 and transaction_flag = 'RETURN TO VENDOR') then amount_g end) amt_receipt_return_g,
	 sum(case when (mx_id = 1 and transaction_flag = 'RETURN TO VENDOR') then amount_sg end) amt_receipt_return_sg,
	 sum(case when transaction_flag = 'RECEIVE' then quantity end) qty_receipt_and_dropship,
	 sum(case when transaction_flag = 'RECEIVE' and dropship_type_code=0 then quantity end) qty_receipt,
	 sum(case when (mx_id = 2 and transaction_flag = 'RETURN TO VENDOR') then quantity end) qty_return,
	 sum(case when (mx_id = 2 and transaction_flag = 'REJECT') then quantity end) qty_reject,
	 sum(case when dropship_type_code=0 then amt_afterdue_b end) amt_afterdue_b,
	 sum(case when dropship_type_code=0 then amt_afterdue_g end) amt_afterdue_g,
	 sum(case when dropship_type_code=0 then amt_afterdue_sg end) amt_afterdue_sg,
	 sum(case when dropship_type_code=0 then amt_beforedue_b end) amt_beforedue_b,
	 sum(case when dropship_type_code=0 then amt_beforedue_g end) amt_beforedue_g,
	 sum(case when dropship_type_code=0 then amt_beforedue_sg end) amt_beforedue_sg,
	 sum(case when dropship_type_code=0 then amt_expt_b end) amt_expt_b,
	 sum(case when dropship_type_code=0 then amt_expt_g end) amt_expt_g,
	 sum(case when dropship_type_code=0 then amt_expt_sg end) amt_expt_sg,
	 sum(case when dropship_type_code=0 then qty_intol end) qty_intol,
	 sum(case when dropship_type_code=0 then qty_afterdue end) qty_afterdue,
	 sum(case when dropship_type_code=0 then qty_beforedue end) qty_beforedue,
	 sum(case
	 when (dropship_type_code=0 and (transaction_type in ('RECEIVE', 'MATCH') and num_days_early > 0)) then num_days_early end) num_days_early,
	 sum(case
	 when (dropship_type_code=0 and (transaction_type in ('RECEIVE', 'MATCH') and num_days_late > 0)) then num_days_late end) num_days_late,
	 sum(case when (mx_id = 2 and transaction_flag = 'REJECT') then amount_b end) amt_reject_b,
	 sum(case when (mx_id = 2 and transaction_flag = 'REJECT') then amount_g end) amt_reject_g,
	 sum(case when (mx_id = 2 and transaction_flag = 'REJECT') then amount_sg end) amt_reject_sg,
	 sum(case when (mx_id = 2 and transaction_flag in ('ACCEPT', 'REJECT')) then amount_b end) amt_inspected_b,
	 sum(case when (mx_id = 2 and transaction_flag in ('ACCEPT', 'REJECT')) then amount_g end) amt_inspected_g,
	 sum(case when (mx_id = 2 and transaction_flag in ('ACCEPT', 'REJECT')) then amount_sg end) amt_inspected_sg,
	 sum(case when (mx_id = 1 and transaction_flag = 'REJECT') then amount_b end) amt_receipt_reject_b,
	 sum(case when (mx_id = 1 and transaction_flag = 'REJECT') then amount_g end) amt_receipt_reject_g,
	 sum(case when (mx_id = 1 and transaction_flag = 'REJECT') then amount_sg end) amt_receipt_reject_sg,
	 count(case when transaction_flag = 'RECEIVE' then amount_b end) c_amt_receipt_and_dropship_b,
	 count(case when transaction_flag = 'RECEIVE' then amount_g end) c_amt_receipt_and_dropship_g,
	 count(case when transaction_flag = 'RECEIVE' then amount_sg end) c_amt_receipt_and_dropship_sg,
	 count(case when transaction_flag = 'RECEIVE' and dropship_type_code=0 then amount_b end) c_amt_receipt_b,
	 count(case when transaction_flag = 'RECEIVE' and dropship_type_code=0 then amount_g end) c_amt_receipt_g,
	 count(case when transaction_flag = 'RECEIVE' and dropship_type_code=0 then amount_sg end) c_amt_receipt_sg,
	 count(case when (mx_id = 2 and transaction_flag = 'RETURN TO VENDOR') then amount_b end) c_amt_return_b,
	 count(case when (mx_id = 2 and transaction_flag = 'RETURN TO VENDOR') then amount_g end) c_amt_return_g,
	 count(case when (mx_id = 2 and transaction_flag = 'RETURN TO VENDOR') then amount_sg end) c_amt_return_sg,
	 count(case when (mx_id = 1 and transaction_flag = 'RETURN TO VENDOR') then amount_b end) c_amt_receipt_return_b,
	 count(case when (mx_id = 1 and transaction_flag = 'RETURN TO VENDOR') then amount_g end) c_amt_receipt_return_g,
	 count(case when (mx_id = 1 and transaction_flag = 'RETURN TO VENDOR') then amount_sg end) c_amt_receipt_return_sg,
	 count(case when transaction_flag = 'RECEIVE' then quantity end) c_qty_receipt_and_dropship,
	 count(case when transaction_flag = 'RECEIVE' and dropship_type_code=0 then quantity end) c_qty_receipt,
	 count(case when (mx_id = 2 and transaction_flag = 'RETURN TO VENDOR') then quantity end) c_qty_return,
	 count(case when (mx_id = 2 and transaction_flag = 'REJECT') then quantity end) c_qty_reject,
	 count(case when dropship_type_code=0 then amt_afterdue_b end) c_amt_afterdue_b,
	 count(case when dropship_type_code=0 then amt_afterdue_g end) c_amt_afterdue_g,
	 count(case when dropship_type_code=0 then amt_afterdue_sg end) c_amt_afterdue_sg,
	 count(case when dropship_type_code=0 then amt_beforedue_b end) c_amt_beforedue_b,
	 count(case when dropship_type_code=0 then amt_beforedue_g end) c_amt_beforedue_g,
	 count(case when dropship_type_code=0 then amt_beforedue_sg end) c_amt_beforedue_sg,
	 count(case when dropship_type_code=0 then amt_expt_b end) c_amt_expt_b,
	 count(case when dropship_type_code=0 then amt_expt_g end) c_amt_expt_g,
	 count(case when dropship_type_code=0 then amt_expt_sg end) c_amt_expt_sg,
	 count(case when dropship_type_code=0 then qty_intol end) c_qty_intol,
	 count(case when dropship_type_code=0 then qty_afterdue end) c_qty_afterdue,
	 count(case when dropship_type_code=0 then qty_beforedue end) c_qty_beforedue,
	 count(case when (dropship_type_code=0 and (transaction_type in ('RECEIVE', 'MATCH') and num_days_early > 0)) then num_days_early end) num_txns_early_cnt,
	 count(case when (dropship_type_code=0 and (transaction_type in ('RECEIVE', 'MATCH') and num_days_late > 0)) then num_days_late end) num_txns_late_cnt,
	 count(case when (mx_id = 2 and transaction_flag = 'REJECT') then amount_b end) c_amt_reject_b,
	 count(case when (mx_id = 2 and transaction_flag = 'REJECT') then amount_g end) c_amt_reject_g,
	 count(case when (mx_id = 2 and transaction_flag = 'REJECT') then amount_sg end) c_amt_reject_sg,
	 count(case when (mx_id = 2 and transaction_flag in ('ACCEPT', 'REJECT')) then amount_b end) c_amt_inspected_b,
	 count(case when (mx_id = 2 and transaction_flag in ('ACCEPT', 'REJECT')) then amount_g end) c_amt_inspected_g,
	 count(case when (mx_id = 2 and transaction_flag in ('ACCEPT', 'REJECT')) then amount_sg end) c_amt_inspected_sg,
	 count(case when (mx_id = 1 and transaction_flag = 'REJECT') then amount_b end) c_amt_receipt_reject_b,
	 count(case when (mx_id = 1 and transaction_flag = 'REJECT') then amount_g end) c_amt_receipt_reject_g,
	 count(case when (mx_id = 1 and transaction_flag = 'REJECT') then amount_sg end) c_amt_receipt_reject_sg,
	 count(*) c_total,
	 count(case when (dropship_type_code=0 and (transaction_type in ('RECEIVE', 'MATCH'))) then amount_b end) num_txns_receipt_cnt,
	 count(case when (mx_id = 2 and transaction_type = 'RETURN TO VENDOR') then amount_b end) num_txns_return_cnt,
         count(case when (mx_id = 1 and transaction_type = 'RETURN TO VENDOR') then amount_b end) num_txns_receipt_return_cnt
FROM	 poa_rtx_bs_j_mv f
group by
org_id,
commodity_id,
rollup(supplier_id, category_id, (po_item_id,
		     supplier_site_id,
		     receiving_org_id, buyer_id, reason_id,
		     base_uom)),
rollup(ent_qtr_id,ent_period_id, week_id, day_id),
ent_year_id