MATERIALIZED VIEW: APPS.BIM_I_LP_ITEM_MV

Source

SELECT /* Dummy Comment */
		    1 umark
		    ,fact.rowid frowid
		    ,star.rowid srowid
		    ,lead_id
		    ,lead_line_id
		    ,lead_creation_date
		    ,lead_touched_date
		    ,lead_converted_date
		    ,lead_dead_date
		    ,lead_closed_date
		    ,transaction_create_date
		    ,group_id
		    ,resource_id
		    ,channel_code
		    ,cust_category
		    ,star.vbh_category_id product_category_id
		    ,DECODE(star.master_id,NULL,star.id,star.master_id) item_id
		    ,fact.organization_id organization_id
		    ,lead_rank_id
		    ,lead_source
		    ,lead_status
		    ,lead_region
		    ,lead_country
		    ,source_code_id
		    ,object_type
		    ,object_id
		    ,child_object_type
		    ,child_object_id
		    ,object_region
		    ,object_country
		    ,child_object_region
		    ,child_object_country
		    ,business_unit_id
		   ,lead_amount
		   ,qualified_flag
		   ,customer_id
                   ,lead_name
		   ,close_reason
		from
		    ENI.ENI_OLTP_ITEM_STAR star,
		    BIM.BIM_I_LEAD_FACTS fact
		where
		    fact.item_id = star.inventory_item_id
		    AND fact.organization_id = star.organization_id
		    AND fact.item_id <> -1
		UNION ALL
		SELECT
		    2 umark
		    ,fact.rowid frowid
		    ,null srowid
		    ,lead_id
		    ,lead_line_id
		    ,lead_creation_date
		    ,lead_touched_date
		    ,lead_converted_date
		    ,lead_dead_date
		    ,lead_closed_date
		    ,transaction_create_date
		    ,group_id
		    ,resource_id
		    ,channel_code
		    ,cust_category
		    ,product_category_id
		    ,'-1' item_id
		    ,organization_id
		    ,lead_rank_id
		    ,lead_source
		    ,lead_status
		    ,lead_region
		    ,lead_country
		    ,source_code_id
		    ,object_type
		    ,object_id
		    ,child_object_type
		    ,child_object_id
		    ,object_region
		    ,object_country
		    ,child_object_region
		    ,child_object_country
		    ,business_unit_id
   		    ,lead_amount
   		   ,qualified_flag
		   ,customer_id
                   ,lead_name
		   ,close_reason
		from
		    BIM.BIM_I_LEAD_FACTS fact
		where
		    fact.item_id = -1