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.

APPS.OZF_REFRESH_VIEW_PVT SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 104


	  SELECT 'ALTER INDEX '||owner||'.'||index_name ||' UNUSABLE' sqlstmt
	    FROM all_indexes
	   WHERE table_owner = (select u.oracle_username
				from fnd_product_installations i,
				fnd_application a, fnd_oracle_userid u
				where a.application_short_name = 'OZF'
				  and a.application_id = i.application_id
				  and u.oracle_id = i.oracle_id)
		AND table_name = p_table_name;

Line: 115


	  SELECT 'ALTER INDEX '||owner||'.'||index_name ||' REBUILD ONLINE' sqlstmt
	    FROM all_indexes
	   WHERE table_owner = (select u.oracle_username
				from fnd_product_installations i,
				fnd_application a, fnd_oracle_userid u
				where a.application_short_name = 'OZF'
				  and a.application_id = i.application_id
				  and u.oracle_id = i.oracle_id)
	     AND table_name = p_table_name;

Line: 132


	                      select u.oracle_username
			      into ls_owner
			      from fnd_product_installations i,
				   fnd_application a,
				   fnd_oracle_userid u
			     where a.application_short_name = 'OZF'
			       and a.application_id = i.application_id
			       and u.oracle_id = i.oracle_id;

Line: 145


	--Insert data in temp table

     IF p_table_name = 'OZF_ORDER_SALES_SUMRY_MV' THEN

    	INSERT INTO OZF_ORDER_SALES_SUMRY_MV_TEMP
	(TIME_ID,
	PERIOD_TYPE_ID,
	SOLD_TO_CUST_ACCOUNT_ID,
	SHIP_TO_SITE_USE_ID,
	BILL_TO_SITE_USE_ID,
	INVENTORY_ITEM_ID,
	SALES_QTY,
	SALES_AMT
	)
	SELECT TIME_ID,
	PERIOD_TYPE_ID,
	SOLD_TO_CUST_ACCOUNT_ID,
	SHIP_TO_SITE_USE_ID,
	BILL_TO_SITE_USE_ID,
	INVENTORY_ITEM_ID,
	SALES_QTY,
	SALES_AMT
	 FROM
	(
	select b.report_date_julian time_id,
	       1   period_type_id,
		   a.sold_to_cust_account_id,
		   a.ship_to_site_use_id ,
		   a.bill_to_site_use_id ,
		   a.inventory_item_id,
		   SUM( DECODE(a.transfer_type, 'IN', a.common_quantity ,-a.common_quantity) ) sales_qty,
	           SUM( DECODE(a.transfer_type, 'IN', a.common_amount   ,-a.common_amount) ) sales_amt
	from ozf_sales_transactions_all a,
	     ozf_time_day b
	where  a.transaction_date = b.report_date
	and a.error_flag = 'N'
	and a.source_code = 'OM'
	group by
	       b.report_date_julian,
		   1,
		   a.sold_to_cust_account_id,
		   a.ship_to_site_use_id,
		   a.bill_to_site_use_id,
		   a.inventory_item_id
	union all
	select b.week_id time_id,
	       16			   period_type_id,
		   a.sold_to_cust_account_id,
		   a.ship_to_site_use_id,
		   a.bill_to_site_use_id,
		   a.inventory_item_id,
		   SUM( DECODE(a.transfer_type, 'IN', a.common_quantity , -a.common_quantity) ) sales_qty,
	           SUM( DECODE(a.transfer_type, 'IN', a.common_amount, -a.common_amount) ) sales_amt
	from ozf_sales_transactions_all a,
	     ozf_time_day b
	where  a.transaction_date = b.report_date
	and a.error_flag = 'N'
	and a.source_code = 'OM'
	group by
	       b.week_id,
		   16,
		   a.sold_to_cust_account_id,
		   a.ship_to_site_use_id,
		   a.bill_to_site_use_id,
		   a.inventory_item_id
	union all
	select b.ent_period_id time_id,
	           32  period_type_id,
		   a.sold_to_cust_account_id,
		   a.ship_to_site_use_id,
		   a.bill_to_site_use_id,
		   a.inventory_item_id,
		   SUM( DECODE(a.transfer_type, 'IN', a.common_quantity, -a.common_quantity) ) sales_qty,
	           SUM( DECODE(a.transfer_type, 'IN', a.common_amount, -a.common_amount) ) sales_amt
	from ozf_sales_transactions_all a,
	     ozf_time_day b
	where  a.transaction_date = b.report_date
	and a.error_flag = 'N'
	and a.source_code = 'OM'
	group by
	       b.ent_period_id,
		   32,
		   a.sold_to_cust_account_id,
		   a.ship_to_site_use_id,
		   a.bill_to_site_use_id,
		   a.inventory_item_id
	union all
	select b.ent_qtr_id time_id,
	       64		   period_type_id,
		   a.sold_to_cust_account_id,
		   a.ship_to_site_use_id,
		   a.bill_to_site_use_id,
		   a.inventory_item_id,
		   SUM( DECODE(a.transfer_type, 'IN',a.common_quantity,-a.common_quantity) ) sales_qty,
	           SUM( DECODE(a.transfer_type, 'IN',a.common_amount, -a.common_amount) ) sales_amt
	from ozf_sales_transactions_all a,
	     ozf_time_day b
	where  a.transaction_date = b.report_date
	and a.error_flag = 'N'
	and a.source_code = 'OM'
	group by
	       b.ent_qtr_id,
		   64,
		   a.sold_to_cust_account_id,
		   a.ship_to_site_use_id,
		   a.bill_to_site_use_id,
		   a.inventory_item_id
	union all
	select b.ent_year_id time_id,
	       128		     period_type_id,
		   a.sold_to_cust_account_id,
		   a.ship_to_site_use_id,
		   a.bill_to_site_use_id,
		   a.inventory_item_id,
		   SUM( DECODE(a.transfer_type, 'IN', a.common_quantity, -a.common_quantity) ) sales_qty,
	           SUM( DECODE(a.transfer_type, 'IN', a.common_amount , -a.common_amount) ) sales_amt
	from ozf_sales_transactions_all a,
	     ozf_time_day b
	where  a.transaction_date = b.report_date
	and a.error_flag = 'N'
	and a.source_code = 'OM'
	group by
	       b.ent_year_id,
		   128,
		   a.sold_to_cust_account_id,
		   a.ship_to_site_use_id,
		   a.bill_to_site_use_id,
		   a.inventory_item_id)  ;

Line: 276


   INSERT INTO OZF_INVENTORY_SUMMARY_MV_TEMP
	(TIME_ID,
	PERIOD_TYPE_ID,
	PARTY_ID,
	INVENTORY_ITEM_ID,
	PRIMARY_UOM,
	COMMON_QUANTITY_IN,
	COMMON_QUANTITY_OUT,
	COMMON_QUANTITY_MANUAL,
	PRIMARY_QUANTITY
	)
	SELECT TIME_ID,
	PERIOD_TYPE_ID,
	PARTY_ID,
	INVENTORY_ITEM_ID,
	PRIMARY_UOM,
	COMMON_QUANTITY_IN,
	COMMON_QUANTITY_OUT,
	COMMON_QUANTITY_MANUAL,
	PRIMARY_QUANTITY
	FROM
	(select b.report_date_julian time_id,
       1   period_type_id,
       a.sold_to_party_id party_id,
       a.inventory_item_id inventory_item_id,
       a.primary_uom_code primary_uom,
       sum(decode(a.transfer_type, 'IN', nvl(a.common_quantity,0), 0)) common_quantity_in,
       sum(decode(a.transfer_type, 'OUT', nvl(a.common_quantity,0), 0)) common_quantity_out,
       0 common_quantity_manual,
       SUM(decode(a.transfer_type, 'IN', 1, 'OUT', -1, 0) * nvl(a.primary_quantity,0)) primary_quantity
from ozf_sales_transactions_all a,
     ozf_time_day b
where  a.transaction_date = b.report_date
and a.error_flag = 'N'
and source_code = 'OM'
group by
       b.report_date_julian,
       1,
       a.sold_to_party_id,
       a.inventory_item_id,
       a.primary_uom_code
union all
select b.week_id time_id,
       16   period_type_id,
       a.sold_to_party_id party_id,
       a.inventory_item_id inventory_item_id,
       a.primary_uom_code primary_uom,
       sum(decode(a.transfer_type, 'IN', nvl(a.common_quantity,0), 0)) common_quantity_in,
       sum(decode(a.transfer_type, 'OUT', nvl(a.common_quantity,0), 0)) common_quantity_out,
       0 common_quantity_manual,
       SUM(decode(a.transfer_type, 'IN', 1, 'OUT', -1, 0) * nvl(a.primary_quantity,0)) primary_quantity
from ozf_sales_transactions_all a,
     ozf_time_day b
where  a.transaction_date = b.report_date
and a.error_flag = 'N'
and source_code = 'OM'
group by
       b.week_id,
       16,
       a.sold_to_party_id,
       a.inventory_item_id,
       a.primary_uom_code
union all
select b.ent_period_id time_id,
       32   period_type_id,
       a.sold_to_party_id party_id,
       a.inventory_item_id inventory_item_id,
       a.primary_uom_code primary_uom,
       sum(decode(a.transfer_type, 'IN', nvl(a.common_quantity,0), 0)) common_quantity_in,
       sum(decode(a.transfer_type, 'OUT', nvl(a.common_quantity,0), 0)) common_quantity_out,
       0 common_quantity_manual,
       SUM(decode(a.transfer_type, 'IN', 1, 'OUT', -1, 0) * nvl(a.primary_quantity,0)) primary_quantity
from ozf_sales_transactions_all a,
     ozf_time_day b
where  a.transaction_date = b.report_date
and a.error_flag = 'N'
and source_code = 'OM'
group by
       b.ent_period_id,
       32,
       a.sold_to_party_id,
       a.inventory_item_id,
       a.primary_uom_code
union all
select b.ent_qtr_id time_id,
       64   period_type_id,
       a.sold_to_party_id party_id,
       a.inventory_item_id inventory_item_id,
       a.primary_uom_code primary_uom,
       sum(decode(a.transfer_type, 'IN', nvl(a.common_quantity,0), 0)) common_quantity_in,
       sum(decode(a.transfer_type, 'OUT', nvl(a.common_quantity,0), 0)) common_quantity_out,
       0 common_quantity_manual,
       SUM(decode(a.transfer_type, 'IN', 1, 'OUT', -1, 0) * nvl(a.primary_quantity,0)) primary_quantity
from ozf_sales_transactions_all a,
     ozf_time_day b
where  a.transaction_date = b.report_date
and a.error_flag = 'N'
and source_code = 'OM'
group by
       b.ent_qtr_id,
       64,
       a.sold_to_party_id,
       a.inventory_item_id,
       a.primary_uom_code
union all
select b.ent_year_id time_id,
       128   period_type_id,
       a.sold_to_party_id party_id,
       a.inventory_item_id inventory_item_id,
       a.primary_uom_code primary_uom,
       sum(decode(a.transfer_type, 'IN', nvl(a.common_quantity,0), 0)) common_quantity_in,
       sum(decode(a.transfer_type, 'OUT', nvl(a.common_quantity,0), 0)) common_quantity_out,
       0 common_quantity_manual,
       SUM(decode(a.transfer_type, 'IN', 1, 'OUT', -1, 0) * nvl(a.primary_quantity,0)) primary_quantity
from ozf_sales_transactions_all a,
     ozf_time_day b
where  a.transaction_date = b.report_date
and a.error_flag = 'N'
and source_code = 'OM'
group by
       b.ent_year_id,
       128,
       a.sold_to_party_id,
       a.inventory_item_id,
       a.primary_uom_code
union all
select b.report_date_julian time_id,  -- 'IS data'
       1   period_type_id,
       a.sold_from_party_id party_id,
       a.inventory_item_id inventory_item_id,
       a.primary_uom_code primary_uom,
       sum(decode(a.transfer_type, 'IN', nvl(a.common_quantity,0), 0)) common_quantity_in,
       sum(decode(a.transfer_type, 'OUT', nvl(a.common_quantity,0), 0)) common_quantity_out,
       0 common_quantity_manual,
       SUM(decode(a.transfer_type, 'IN', 1, 'OUT', -1, 0) * nvl(a.primary_quantity,0)) primary_quantity
from ozf_sales_transactions_all a,
     ozf_time_day b
where  a.transaction_date = b.report_date
and a.error_flag = 'N'
and source_code = 'IS'
group by
       b.report_date_julian,
       1,
       a.sold_from_party_id,
       a.inventory_item_id,
       a.primary_uom_code
union all
select b.week_id time_id,
       16   period_type_id,
       a.sold_from_party_id party_id,
       a.inventory_item_id inventory_item_id,
       a.primary_uom_code primary_uom,
       sum(decode(a.transfer_type, 'IN', nvl(a.common_quantity,0), 0)) common_quantity_in,
       sum(decode(a.transfer_type, 'OUT', nvl(a.common_quantity,0), 0)) common_quantity_out,
       0 common_quantity_manual,
       SUM(decode(a.transfer_type, 'IN', 1, 'OUT', -1, 0) * nvl(a.primary_quantity,0)) primary_quantity
from ozf_sales_transactions_all a,
     ozf_time_day b
where  a.transaction_date = b.report_date
and a.error_flag = 'N'
and source_code = 'IS'
group by
       b.week_id,
       16,
       a.sold_from_party_id,
       a.inventory_item_id,
       a.primary_uom_code
union all
select b.ent_period_id time_id,
       32   period_type_id,
       a.sold_from_party_id party_id,
       a.inventory_item_id inventory_item_id,
       a.primary_uom_code primary_uom,
       sum(decode(a.transfer_type, 'IN', nvl(a.common_quantity,0), 0)) common_quantity_in,
       sum(decode(a.transfer_type, 'OUT', nvl(a.common_quantity,0), 0)) common_quantity_out,
       0 common_quantity_manual,
       SUM(decode(a.transfer_type, 'IN', 1, 'OUT', -1, 0) * nvl(a.primary_quantity,0)) primary_quantity
from ozf_sales_transactions_all a,
     ozf_time_day b
where  a.transaction_date = b.report_date
and a.error_flag = 'N'
and source_code = 'IS'
group by
       b.ent_period_id,
       32,
       a.sold_from_party_id,
       a.inventory_item_id,
       a.primary_uom_code
union all
select b.ent_qtr_id time_id,
       64   period_type_id,
       a.sold_from_party_id party_id,
       a.inventory_item_id inventory_item_id,
       a.primary_uom_code primary_uom,
       sum(decode(a.transfer_type, 'IN', nvl(a.common_quantity,0), 0)) common_quantity_in,
       sum(decode(a.transfer_type, 'OUT', nvl(a.common_quantity,0), 0)) common_quantity_out,
       0 common_quantity_manual,
       SUM(decode(a.transfer_type, 'IN', 1, 'OUT', -1, 0) * nvl(a.primary_quantity,0)) primary_quantity
from ozf_sales_transactions_all a,
     ozf_time_day b
where  a.transaction_date = b.report_date
and a.error_flag = 'N'
and source_code = 'IS'
group by
       b.ent_qtr_id,
       64,
       a.sold_from_party_id,
       a.inventory_item_id,
       a.primary_uom_code
union all
select b.ent_year_id time_id,
       128   period_type_id,
       a.sold_from_party_id party_id,
       a.inventory_item_id inventory_item_id,
       a.primary_uom_code primary_uom,
       sum(decode(a.transfer_type, 'IN', nvl(a.common_quantity,0), 0)) common_quantity_in,
       sum(decode(a.transfer_type, 'OUT', nvl(a.common_quantity,0), 0)) common_quantity_out,
       0 common_quantity_manual,
       SUM(decode(a.transfer_type, 'IN', 1, 'OUT', -1, 0) * nvl(a.primary_quantity,0)) primary_quantity
from ozf_sales_transactions_all a,
     ozf_time_day b
where  a.transaction_date = b.report_date
and a.error_flag = 'N'
and source_code = 'IS'
group by
       b.ent_year_id,
       128,
       a.sold_from_party_id,
       a.inventory_item_id,
       a.primary_uom_code
union all
select b.report_date_julian time_id,  -- 'MA data'
       1   period_type_id,
       a.sold_to_party_id party_id,
       a.inventory_item_id inventory_item_id,
       a.primary_uom_code primary_uom,
       0 common_quantity_in,
       0 common_quantity_out,
       sum(decode(a.transfer_type, 'IN', 1, 'OUT', -1, 0) * nvl(a.common_quantity, 0)) common_quantity_manual,
       SUM(decode(a.transfer_type, 'IN', 1, 'OUT', -1, 0) * nvl(a.primary_quantity,0)) primary_quantity
from ozf_sales_transactions_all a,
     ozf_time_day b
where  a.transaction_date = b.report_date
and a.error_flag = 'N'
and source_code = 'MA'
group by
       b.report_date_julian,
       1,
       a.sold_to_party_id,
       a.inventory_item_id,
       a.primary_uom_code
union all
select b.week_id time_id,
       16   period_type_id,
       a.sold_to_party_id party_id,
       a.inventory_item_id inventory_item_id,
       a.primary_uom_code primary_uom,
       0 common_quantity_in,
       0 common_quantity_out,
       sum(decode(a.transfer_type, 'IN', 1, 'OUT', -1, 0) * nvl(a.common_quantity, 0)) common_quantity_manual,
       SUM(decode(a.transfer_type, 'IN', 1, 'OUT', -1, 0) * nvl(a.primary_quantity,0)) primary_quantity
from ozf_sales_transactions_all a,
     ozf_time_day b
where  a.transaction_date = b.report_date
and a.error_flag = 'N'
and source_code = 'MA'
group by
       b.week_id,
       16,
       a.sold_to_party_id,
       a.inventory_item_id,
       a.primary_uom_code
union all
select b.ent_period_id time_id,
       32   period_type_id,
       a.sold_to_party_id party_id,
       a.inventory_item_id inventory_item_id,
       a.primary_uom_code primary_uom,
       0 common_quantity_in,
       0 common_quantity_out,
       sum(decode(a.transfer_type, 'IN', 1, 'OUT', -1, 0) * nvl(a.common_quantity, 0)) common_quantity_manual,
       SUM(decode(a.transfer_type, 'IN', 1, 'OUT', -1, 0) * nvl(a.primary_quantity,0)) primary_quantity
from ozf_sales_transactions_all a,
     ozf_time_day b
where  a.transaction_date = b.report_date
and a.error_flag = 'N'
and source_code = 'MA'
group by
       b.ent_period_id,
       32,
       a.sold_to_party_id,
       a.inventory_item_id,
       a.primary_uom_code
union all
select b.ent_qtr_id time_id,
       64   period_type_id,
       a.sold_to_party_id party_id,
       a.inventory_item_id inventory_item_id,
       a.primary_uom_code primary_uom,
       0 common_quantity_in,
       0 common_quantity_out,
       sum(decode(a.transfer_type, 'IN', 1, 'OUT', -1, 0) * nvl(a.common_quantity, 0)) common_quantity_manual,
       SUM(decode(a.transfer_type, 'IN', 1, 'OUT', -1, 0) * nvl(a.primary_quantity,0)) primary_quantity
from ozf_sales_transactions_all a,
     ozf_time_day b
where  a.transaction_date = b.report_date
and a.error_flag = 'N'
and source_code = 'MA'
group by
       b.ent_qtr_id,
       64,
       a.sold_to_party_id,
       a.inventory_item_id,
       a.primary_uom_code
union all
select b.ent_year_id time_id,
       128   period_type_id,
       a.sold_to_party_id party_id,
       a.inventory_item_id inventory_item_id,
       a.primary_uom_code primary_uom,
       0 common_quantity_in,
       0 common_quantity_out,
       sum(decode(a.transfer_type, 'IN', 1, 'OUT', -1, 0) * nvl(a.common_quantity, 0)) common_quantity_manual,
       SUM(decode(a.transfer_type, 'IN', 1, 'OUT', -1, 0) * nvl(a.primary_quantity,0)) primary_quantity
from ozf_sales_transactions_all a,
     ozf_time_day b
where  a.transaction_date = b.report_date
and a.error_flag = 'N'
and source_code = 'MA'
group by
       b.ent_year_id,
       128,
       a.sold_to_party_id,
       a.inventory_item_id,
       a.primary_uom_code);

Line: 614


   INSERT INTO OZF_BASELINE_SALES_MV_TEMP
	(DATA_SOURCE,
	MARKET_TYPE,
	MARKET_ID,
	ITEM_LEVEL,
	ITEM_ID,
	TIME_ID,
	PERIOD_TYPE_ID,
	BASELINE_QTY,
	BASELINE_AMT
	)
	SELECT DATA_SOURCE,
	MARKET_TYPE,
	MARKET_ID,
	ITEM_LEVEL,
	ITEM_ID,
	TIME_ID,
	PERIOD_TYPE_ID,
	BASELINE_QTY,
	BASELINE_AMT
	FROM
	(
	SELECT
  x.DATA_SOURCE,
  x.MARKET_TYPE,
  x.MARKET_ID,
  x.ITEM_LEVEL,
  x.ITEM_ID,
  (CASE
      WHEN GROUPING(b.report_date_julian)=0 THEN b.report_date_julian
      WHEN GROUPING(b.week_id)=0            THEN b.week_id
      WHEN GROUPING(b.ent_period_id)=0      THEN b.ent_period_id
      WHEN GROUPING(b.ent_qtr_id)=0         THEN b.ent_qtr_id
      ELSE b.ent_year_id
      END
   ) time_id,
  (CASE
      WHEN GROUPING(b.report_date_julian)=0 THEN 1
      WHEN GROUPING(b.week_id)=0            THEN 16
      WHEN GROUPING(b.ent_period_id)=0      THEN 32
      WHEN GROUPING(b.ent_qtr_id)=0         THEN 64
      ELSE 128
      END
   ) period_type_id,
  SUM(CASE WHEN COM_BASELINE_QTY > 0 THEN COM_BASELINE_QTY ELSE 0 END) BASELINE_QTY,
  SUM(CASE WHEN COM_BASELINE_AMT > 0 THEN COM_BASELINE_AMT ELSE 0 END) BASELINE_AMT
FROM
 OZF_BASELINE_SALES_FACTS x,
 OZF_TIME_DAY b
WHERE x.status_flag IS NULL
  AND x.TRANSACTION_DATE = b.report_date
GROUP BY
  x.DATA_SOURCE                 ,
  x.MARKET_TYPE,
  x.MARKET_ID                 ,
  x.ITEM_LEVEL                  ,
  x.ITEM_ID                     ,
  GROUPING SETS (b.report_date_julian,b.week_id,b.ent_period_id,b.ent_qtr_id,b.ent_year_id)
 ) ;