APPS.BIM_BUDGETS_DENORM_PKG SQL Statements

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

Line: 165

		BIS_COLLECTION_UTILITIES.deleteLogForObject('BIM_BUDGET_DENORM');

Line: 169

  	          DELETE  bim_i_budgets_denorm
      		  WHERE	  object_id IN
                              	(SELECT  fund_id
                              	FROM	 ozf_funds_all_b a
                              	WHERE    last_update_date > l_temp_start_date
		                AND NOT EXISTS
                                (SELECT	1
                                 FROM	bim_i_budgets_denorm b
                                 WHERE	b.object_id = a.fund_id
				 AND    b.object_type='BUDGET'
                                 AND    b.parent_object_id = a.parent_fund_id)
			         );

Line: 182

  	          DELETE  bim_i_budgets_denorm
      		  WHERE	  object_id IN
                              	(SELECT  category_id
                              	FROM	 ams_categories_b a
                              	WHERE   arc_category_created_for='FUND'
                                AND     last_update_date > l_temp_start_date
		                AND NOT EXISTS
                                (SELECT	1
                                 FROM	bim_i_budgets_denorm b
                                 WHERE  b.object_id = a.category_id
				 AND    b.object_type='CATEGORY'
                                 AND    b.parent_object_id = a.parent_category_id)
			         );

Line: 207

      INSERT INTO  bim_i_budgets_denorm
      (object_id
      ,child_denorm_id
      ,object_type
      ,child_denorm_type
      ,object_level
      ,immediate_child_flag
      ,parent_object_id
      ,creation_date
      ,last_update_date
      ,created_by
      ,last_updated_by
      ,last_update_login
      ,object_sub_type
      ,object_sub_cat
      ,leaf_node_flag)
       SELECT
       fund_id object_id
       ,TO_NUMBER(NVL(SUBSTR(SYS_CONNECT_BY_PATH(fund_id,'/'),2,
		INSTR(SYS_CONNECT_BY_PATH(fund_id,'/'),'/',2) -2),fund_id)) AS child_denorm_id
      ,'BUDGET'
      ,'BUDGET'
      ,LEVEL
      ,decode(level,2,'Y',decode(parent_fund_id,NULL,'N','Y')) immediate_child_flag
      ,parent_fund_id
      ,sysdate
      ,sysdate
      ,-1
      ,-1
      ,-1
      ,fund_type
      ,category_id
      ,'N'
       FROM   ozf_funds_all_b a
       WHERE
       NOT EXISTS
              (SELECT 1
                 FROM bim_i_budgets_denorm b
                WHERE b.object_id = a.fund_id
                  AND b.object_type ='BUDGET'
                  AND nvl(b.parent_object_id,1) = nvl(a.parent_fund_id,1))
      CONNECT BY PRIOR parent_fund_id = fund_id
      UNION ALL
      SELECT   category_id
      	,TO_NUMBER(NVL(SUBSTR(SYS_CONNECT_BY_PATH(category_id,'/'),2,
		INSTR(SYS_CONNECT_BY_PATH(category_id,'/'),'/',2) -2),category_id)) AS child_denorm_id
      ,'CATEGORY'
      ,'CATEGORY'
      ,LEVEL
      ,decode(level,2,'Y',decode(parent_category_id,NULL,'N','Y')) immediate_child_flag
      ,parent_category_id
      	,sysdate
      	,sysdate
      	,-1
      	,-1
      	,-1
	,'CATEGORY'
	,0
      ,'N'
       FROM   ams_categories_b a
       WHERE  arc_category_created_for='FUND'
       AND    NOT EXISTS
             (SELECT 1
               FROM bim_i_budgets_denorm b
               WHERE b.object_id = a.category_id
                 AND b.object_type ='CATEGORY'
                 AND nvl(b.parent_object_id,1) = nvl(a.parent_category_id,1))
      connect by prior parent_category_id = category_id ;

Line: 276

 update bim_i_budgets_denorm bd
 set leaf_node_flag='Y'
 where (object_id,object_type) in(
 select object_id, object_type from bim_i_budgets_denorm a
 where   child_denorm_id =object_id
 and not exists (select 1
 from bim_i_budgets_denorm b
  where a.object_id = b.parent_object_id
  and a.object_type=b.object_type
  ));