MATERIALIZED VIEW: APPS.POA_API_BS_J_MV

Source

SELECT /* 12.0: bug#4526784 */
1 umarker ,
f.rowid frid,
pof.rowid pofrid,
com.rowid comrid,
cal.rowid crid,
map.rowid mrid,
f.org_id,
f.supplier_id,
f.supplier_site_id,
f.inv_dist_created_by inv_d_created_by,
nvl(com.commodity_id,-1) commodity_id,
pof.category_id,
pof.po_item_id,
pof.buyer_id,
pof.ship_to_org_id,
pof.base_uom,
map.parent_company_dim_id company_id,
map.parent_cost_center_dim_id cost_center_id,
cal.ent_year_id,
cal.ent_qtr_id,
cal.ent_period_id,
cal.week_id,
cal.report_date_julian day_id,
decode(pof.shipment_type,'PREPAYMENT',0,f.amount_b) amount_b,
decode(pof.shipment_type,'PREPAYMENT',0,f.prim_amount_g) amount_g,
decode(pof.shipment_type,'PREPAYMENT',0,f.sec_amount_g) amount_sg,
(pof.amt_financed * pof.func_cur_conv_rate) amount_financed_b,
decode(pof.global_cur_conv_rate,0,
       pof.amt_financed,
       pof.amt_financed * pof.func_cur_conv_rate *
       pof.global_cur_conv_rate) amount_financed_g,
decode(pof.sglobal_cur_conv_rate,0,
       pof.amt_financed,
       pof.amt_financed * pof.func_cur_conv_rate *
       pof.sglobal_cur_conv_rate) amount_financed_sg,
(pof.amt_recouped * pof.func_cur_conv_rate) amount_recouped_b,
decode(pof.global_cur_conv_rate,0,
       pof.amt_recouped,
       pof.amt_recouped * pof.func_cur_conv_rate *
       pof.global_cur_conv_rate) amount_recouped_g,
decode(pof.sglobal_cur_conv_rate,0,
       pof.amt_recouped,
       pof.amt_recouped * pof.func_cur_conv_rate *
       pof.sglobal_cur_conv_rate) amount_recouped_sg,
f.approved_flag,
f.invoice_type,
f.line_type_lookup_code
FROM
 FII.FII_AP_INV_B f,
 POA.POA_DBI_POD_F pof,
 FII.FII_TIME_DAY cal,
 PO.PO_COMMODITY_CATEGORIES com,
 FII.FII_COM_CC_DIM_MAPS map
where    f.po_distribution_id = pof.po_distribution_id
AND      f.account_date = cal.report_date
AND      pof.category_id = com.category_id(+)
and      f.company_id = map.child_company_id(+)
and      f.cost_center_id = map.child_cost_center_id(+)
UNION ALL
SELECT /* 12.0: bug#4526784 */
2 umarker,
f.rowid frid,
pof.rowid pofrid,
com.rowid comrid,
cal.rowid crid,
map.rowid mrid,
f.org_id,
f.supplier_id,
f.supplier_site_id,
f.inv_dist_created_by inv_d_created_by,
nvl(com.commodity_id,-1) commodity_id,
pof.category_id,
pof.po_item_id,
pof.buyer_id,
pof.ship_to_org_id,
pof.base_uom,
map.parent_company_dim_id company_id,
map.parent_cost_center_dim_id cost_center_id,
cal.ent_year_id,
cal.ent_qtr_id,
cal.ent_period_id,
cal.week_id,
cal.report_date_julian day_id,
decode(pof.shipment_type,'PREPAYMENT',0,f.amount_b) amount_b,
decode(pof.shipment_type,'PREPAYMENT',0,f.prim_amount_g) amount_g,
decode(pof.shipment_type,'PREPAYMENT',0,f.sec_amount_g) amount_sg,
(pof.amt_financed * pof.func_cur_conv_rate) amount_financed_b,
decode(pof.global_cur_conv_rate,0,
       pof.amt_financed,
       pof.amt_financed * pof.func_cur_conv_rate *
       pof.global_cur_conv_rate) amount_financed_g,
decode(pof.sglobal_cur_conv_rate,0,
       pof.amt_financed,
       pof.amt_financed * pof.func_cur_conv_rate *
       pof.sglobal_cur_conv_rate) amount_financed_sg,
(pof.amt_recouped * pof.func_cur_conv_rate) amount_recouped_b,
decode(pof.global_cur_conv_rate,0,
       pof.amt_recouped,
       pof.amt_recouped * pof.func_cur_conv_rate *
       pof.global_cur_conv_rate) amount_recouped_g,
decode(pof.sglobal_cur_conv_rate,0,
       pof.amt_recouped,
       pof.amt_recouped * pof.func_cur_conv_rate *
       pof.sglobal_cur_conv_rate) amount_recouped_sg,
f.approved_flag,
f.invoice_type,
f.line_type_lookup_code
FROM
 FII.FII_AP_INV_DIST_F f,
 POA.POA_DBI_POD_F pof,
 FII.FII_TIME_DAY cal,
 PO.PO_COMMODITY_CATEGORIES com,
 FII.FII_COM_CC_DIM_MAPS map
where    f.po_distribution_id = pof.po_distribution_id
AND      f.account_date = cal.report_date
AND      pof.category_id = com.category_id(+)
and      f.company_id = map.child_company_id(+)
and      f.cost_center_id = map.child_cost_center_id(+)