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_J_MV

Source


select /* 12.0: bug#4526784 */
rtx.rowid rrid,
pll.rowid prid,
cal.rowid crid,
mx.rowid mxrowid,
pll.commodity_id,
pll.category_id,
pll.po_item_id,
rtx.creation_operating_unit_id org_id,
rtx.supplier_id,
rtx.supplier_site_id,
pll.buyer_id,
rtx.receiving_org_id,
rtx.reason_id,
pll.base_uom base_uom,
rtx.transaction_type,
cal.ent_year_id,
cal.ent_qtr_id,
cal.ent_period_id,
cal.week_id,
cal.report_date_julian day_id,
mx.id mx_id,
(case when pll.order_type = 'AMOUNT'
                 then null
                 else Decode(pll.item_id, NULL, rtx.source_doc_quantity, rtx.quantity)
            end) * pll.base_uom_conv_rate quantity,
rtx.source_doc_quantity * pll.price_override * nvl(rtx.currency_conversion_rate,1) amount_b,
decode(
  rtx.global_cur_conv_rate,0,
  rtx.source_doc_quantity * pll.price_override,
  rtx.source_doc_quantity * pll.price_override * nvl(rtx.currency_conversion_rate,1) * rtx.global_cur_conv_rate
) amount_g,
decode(
  rtx.sglobal_cur_conv_rate,0,
  rtx.source_doc_quantity * pll.price_override,
  rtx.source_doc_quantity * pll.price_override * nvl(rtx.currency_conversion_rate,1) * rtx.sglobal_cur_conv_rate
) amount_sg,
decode(rtx.receipt_exists, 'Y', CASE
                  WHEN rtx.shipping_control='BUYER' then 0
    WHEN (rtx.grp_txn_date > pll.expected_date + pll.days_late_receipt_allowed)
    THEN  rtx.source_doc_quantity * pll.price_override  ELSE 0 END, null ) * nvl(rtx.currency_conversion_rate,1) amt_afterdue_b,
decode(
  rtx.global_cur_conv_rate,0,
  decode(rtx.receipt_exists, 'Y', CASE
                  WHEN rtx.shipping_control='BUYER' then 0
    WHEN (rtx.grp_txn_date > pll.expected_date + pll.days_late_receipt_allowed)
    THEN  rtx.source_doc_quantity * pll.price_override  ELSE 0 END, null ) ,
  decode(rtx.receipt_exists, 'Y', CASE
                  WHEN rtx.shipping_control='BUYER' then 0
    WHEN (rtx.grp_txn_date > pll.expected_date + pll.days_late_receipt_allowed)
    THEN  rtx.source_doc_quantity * pll.price_override  ELSE 0 END, null )* nvl(rtx.currency_conversion_rate,1) * rtx.global_cur_conv_rate
) amt_afterdue_g,
decode(
  rtx.sglobal_cur_conv_rate,0,
  decode(rtx.receipt_exists, 'Y', CASE
                  WHEN rtx.shipping_control='BUYER' then 0
    WHEN (rtx.grp_txn_date > pll.expected_date + pll.days_late_receipt_allowed)
    THEN  rtx.source_doc_quantity * pll.price_override  ELSE 0 END, null ) ,
  decode(rtx.receipt_exists, 'Y', CASE
                  WHEN rtx.shipping_control='BUYER' then 0
    WHEN (rtx.grp_txn_date > pll.expected_date + pll.days_late_receipt_allowed)
    THEN  rtx.source_doc_quantity * pll.price_override  ELSE 0 END, null ) * nvl(rtx.currency_conversion_rate,1) * rtx.sglobal_cur_conv_rate
) amt_afterdue_sg,
decode(rtx.receipt_exists, 'Y', CASE
                  WHEN rtx.shipping_control='BUYER' then 0
    WHEN (rtx.grp_txn_date < pll.expected_date - pll.days_early_receipt_allowed)
    THEN  rtx.source_doc_quantity * pll.price_override ELSE 0 END, null ) * nvl(rtx.currency_conversion_rate,1) amt_beforedue_b,
decode(
  rtx.global_cur_conv_rate,0,
  decode(rtx.receipt_exists, 'Y', CASE
                  WHEN rtx.shipping_control='BUYER' then 0
    WHEN (rtx.grp_txn_date < pll.expected_date - pll.days_early_receipt_allowed)
    THEN  rtx.source_doc_quantity * pll.price_override ELSE 0 END, null ),
  decode(rtx.receipt_exists, 'Y', CASE
                  WHEN rtx.shipping_control='BUYER' then 0
    WHEN (rtx.grp_txn_date < pll.expected_date - pll.days_early_receipt_allowed)
    THEN  rtx.source_doc_quantity * pll.price_override ELSE 0 END, null )* nvl(rtx.currency_conversion_rate,1) * rtx.global_cur_conv_rate
) amt_beforedue_g,
decode(
  rtx.sglobal_cur_conv_rate,0,
  decode(rtx.receipt_exists, 'Y', CASE
                  WHEN rtx.shipping_control='BUYER' then 0
    WHEN (rtx.grp_txn_date < pll.expected_date - pll.days_early_receipt_allowed)
    THEN  rtx.source_doc_quantity * pll.price_override ELSE 0 END, null ) ,
  decode(rtx.receipt_exists, 'Y', CASE
                  WHEN rtx.shipping_control='BUYER' then 0
    WHEN (rtx.grp_txn_date < pll.expected_date - pll.days_early_receipt_allowed)
    THEN  rtx.source_doc_quantity * pll.price_override ELSE 0 END, null )  * nvl(rtx.currency_conversion_rate,1)* rtx.sglobal_cur_conv_rate
) amt_beforedue_sg,
( decode(rtx.receipt_exists, 'Y', CASE
                  WHEN rtx.shipping_control='BUYER' then 0
    WHEN (rtx.grp_txn_date > pll.expected_date + pll.days_late_receipt_allowed)
    THEN  rtx.source_doc_quantity * pll.price_override  ELSE 0 END, null )+ decode(rtx.receipt_exists, 'Y', CASE
                  WHEN rtx.shipping_control='BUYER' then 0
    WHEN (rtx.grp_txn_date < pll.expected_date - pll.days_early_receipt_allowed)
    THEN  rtx.source_doc_quantity * pll.price_override ELSE 0 END, null )) * nvl(rtx.currency_conversion_rate,1) amt_expt_b,
decode(
  rtx.global_cur_conv_rate,0,
  ( decode(rtx.receipt_exists, 'Y', CASE
                  WHEN rtx.shipping_control='BUYER' then 0
    WHEN (rtx.grp_txn_date > pll.expected_date + pll.days_late_receipt_allowed)
    THEN  rtx.source_doc_quantity * pll.price_override  ELSE 0 END, null ) + decode(rtx.receipt_exists, 'Y', CASE
                  WHEN rtx.shipping_control='BUYER' then 0
    WHEN (rtx.grp_txn_date < pll.expected_date - pll.days_early_receipt_allowed)
    THEN  rtx.source_doc_quantity * pll.price_override ELSE 0 END, null )),
  ( decode(rtx.receipt_exists, 'Y', CASE
                  WHEN rtx.shipping_control='BUYER' then 0
    WHEN (rtx.grp_txn_date > pll.expected_date + pll.days_late_receipt_allowed)
    THEN  rtx.source_doc_quantity * pll.price_override  ELSE 0 END, null ) + decode(rtx.receipt_exists, 'Y', CASE
                  WHEN rtx.shipping_control='BUYER' then 0
    WHEN (rtx.grp_txn_date < pll.expected_date - pll.days_early_receipt_allowed)
    THEN  rtx.source_doc_quantity * pll.price_override ELSE 0 END, null )) * nvl(rtx.currency_conversion_rate,1) * rtx.global_cur_conv_rate
) amt_expt_g,
decode(
  rtx.sglobal_cur_conv_rate,0,
  (decode(rtx.receipt_exists, 'Y', CASE
                  WHEN rtx.shipping_control='BUYER' then 0
    WHEN (rtx.grp_txn_date > pll.expected_date + pll.days_late_receipt_allowed)
    THEN  rtx.source_doc_quantity * pll.price_override  ELSE 0 END, null ) + decode(rtx.receipt_exists, 'Y', CASE
                  WHEN rtx.shipping_control='BUYER' then 0
    WHEN (rtx.grp_txn_date < pll.expected_date - pll.days_early_receipt_allowed)
    THEN  rtx.source_doc_quantity * pll.price_override ELSE 0 END, null )),
  (decode(rtx.receipt_exists, 'Y', CASE
                  WHEN rtx.shipping_control='BUYER' then 0
    WHEN (rtx.grp_txn_date > pll.expected_date + pll.days_late_receipt_allowed)
    THEN  rtx.source_doc_quantity * pll.price_override  ELSE 0 END, null ) + decode(rtx.receipt_exists, 'Y', CASE
                  WHEN rtx.shipping_control='BUYER' then 0
    WHEN (rtx.grp_txn_date < pll.expected_date - pll.days_early_receipt_allowed)
    THEN  rtx.source_doc_quantity * pll.price_override ELSE 0 END, null )) * nvl(rtx.currency_conversion_rate,1) * rtx.sglobal_cur_conv_rate
) amt_expt_sg,
(case when pll.order_type = 'AMOUNT' then null
else
Decode(rtx.receipt_exists, 'Y', CASE
          WHEN rtx.shipping_control='BUYER' then Decode(pll.item_id, NULL, rtx.source_doc_quantity, rtx.quantity)
		  WHEN (rtx.grp_txn_date between pll.expected_date - pll.days_early_receipt_allowed
            AND pll.expected_date + pll.days_late_receipt_allowed
            OR pll.expected_date IS null)
		  THEN Decode(pll.item_id, NULL, rtx.source_doc_quantity, rtx.quantity)
          ELSE decode(Decode(pll.item_id, NULL, rtx.source_doc_quantity, rtx.quantity)
            ,null,to_number(null),0)
          END
          , null ) end)* pll.base_uom_conv_rate qty_intol,
(case when pll.order_type = 'AMOUNT' then null
else
Decode(rtx.receipt_exists, 'Y', CASE
             WHEN rtx.shipping_control='BUYER'
                  then decode(Decode(pll.item_id, NULL, rtx.source_doc_quantity, rtx.quantity),null,to_number(null),0)
             WHEN (rtx.grp_txn_date > pll.expected_date + pll.days_late_receipt_allowed)
                  THEN Decode(pll.item_id, NULL, rtx.source_doc_quantity, rtx.quantity)
             ELSE decode(Decode(pll.item_id, NULL, rtx.source_doc_quantity, rtx.quantity),null,to_number(null),0)
                    END, null )
        end) * pll.base_uom_conv_rate qty_afterdue,
(case when pll.order_type = 'AMOUNT'   then null
else
decode(rtx.receipt_exists, 'Y', CASE
             WHEN rtx.shipping_control='BUYER'
                  then decode(Decode(pll.item_id, NULL, rtx.source_doc_quantity, rtx.quantity),null,to_number(null),0)
             WHEN (rtx.grp_txn_date < pll.expected_date - pll.days_early_receipt_allowed)
                  THEN  Decode(pll.item_id, NULL, rtx.source_doc_quantity, rtx.quantity)
            ELSE decode(Decode(pll.item_id, NULL, rtx.source_doc_quantity, rtx.quantity),null,to_number(null),0)
                   END, null )
           end) * pll.base_uom_conv_rate qty_beforedue,
decode(rtx.receipt_exists, 'Y', CASE
                  WHEN rtx.shipping_control='BUYER' then 0
    WHEN (rtx.grp_txn_date > pll.expected_date + pll.days_late_receipt_allowed)
    THEN rtx.grp_txn_date - (pll.expected_date + pll.days_late_receipt_allowed)  ELSE 0 END, null ) num_days_late,
decode(rtx.receipt_exists, 'Y', CASE
                 WHEN rtx.shipping_control='BUYER' then 0
   WHEN (rtx.grp_txn_date < pll.expected_date - pll.days_early_receipt_allowed)
   THEN (pll.expected_date - pll.days_early_receipt_allowed) - rtx.grp_txn_date  ELSE 0 END, null ) num_days_early,
(case when (rtx.transaction_type in ('RECEIVE', 'MATCH')
   or (rtx.transaction_type in ('CORRECT') and rtx.parent_transaction_type in ('RECEIVE', 'MATCH'))) then 'RECEIVE'
   when (rtx.transaction_type in ('RETURN TO VENDOR')
   OR (rtx.transaction_type in ('CORRECT') and rtx.parent_transaction_type in ('RETURN TO VENDOR'))) then 'RETURN TO VENDOR'
   when (rtx.transaction_type in ('REJECT')
   OR (rtx.transaction_type in ('CORRECT') and rtx.parent_transaction_type in ('REJECT'))) then 'REJECT'
   when (rtx.transaction_type in ('ACCEPT')
   OR (rtx.transaction_type in ('CORRECT') and rtx.parent_transaction_type in ('ACCEPT'))) then 'ACCEPT'
 else 'N' end) as transaction_flag,
 nvl(rtx.dropship_type_code,0) dropship_type_code
from
POA.POA_DBI_RTX_F rtx,
poa_dbi_pll_mv pll,
FII.FII_TIME_DAY cal,
OKI.OKI_DBI_MULTIPLEXER_B mx
where    decode(mx.id, 1, trunc(rtx.receive_txn_date),trunc(rtx.grp_txn_date))  = cal.report_date
and      mx.id between 1 and 2
and      (((rtx.transaction_type in ('RECEIVE', 'MATCH')
   OR (rtx.transaction_type in ('CORRECT') and rtx.parent_transaction_type in ('RECEIVE', 'MATCH')))  and mx.id = 1)
   OR
  (rtx.transaction_type in ('RETURN TO VENDOR')
    OR (rtx.transaction_type in ('CORRECT') and rtx.parent_transaction_type in ('RETURN TO VENDOR')))
           OR
          (rtx.transaction_type in ('REJECT')
            OR (rtx.transaction_type in ('CORRECT') and rtx.parent_transaction_type in ('REJECT')))
           OR
          ((rtx.transaction_type in ('ACCEPT')
            OR (rtx.transaction_type in ('CORRECT') and rtx.parent_transaction_type in ('ACCEPT'))) and mx.id = 2)
)
and rtx.line_location_id = pll.line_location_id
and rtx.source_document_code = 'PO'
and pll.matching_basis = 'QUANTITY'
and rtx.transaction_type in ('RECEIVE','MATCH','CORRECT','REJECT','ACCEPT','RETURN TO VENDOR')
and pll.complex_work_flag = 'N'