APPS.FII_USER_SEC_OPTIMIZER SQL Statements

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

Line: 49

    	FII_UTIL.Write_Log ('Inserted ' || SQL%ROWCOUNT || ' rows into fii_cost_center_grants.');

Line: 72

    g_phase := 'Selecting top nodes for company and cost center dimensions from fii_financial_dimensions.';

Line: 74

    select dbi_hier_top_node_id into l_company_top_node_id from fii_financial_dimensions where dimension_short_name='FII_COMPANIES';

Line: 75

	select dbi_hier_top_node_id into l_cost_ctr_top_node_id from fii_financial_dimensions where dimension_short_name='HRI_CL_ORGCC';

Line: 82

    g_phase := 'Inserting into fii_company_grants from bis_grants_v.';

Line: 85

	INSERT INTO fii_company_grants
	(user_id,
	 report_region_code,
	 company_id,
	 aggregated_flag,
	 last_update_date, last_updated_by,
	 creation_date, created_by, last_update_login)
	(SELECT DISTINCT u.user_id,
			s.report_region_code,
			decode(s.granted_for,
				   -999, l_company_top_node_id,
				   s.granted_for),
            h.aggregated_flag,
	        sysdate, g_fii_user_id,
			sysdate, g_fii_user_id, g_fii_login_id
 	 FROM bis_grants_v s, --user_security_initial2 s,
          fii_com_pmv_agrt_nodes h,
          fnd_user u
 	 WHERE decode(s.granted_for,
				   -999, l_company_top_node_id,
				   s.granted_for) = h.company_id
     AND s.delegation_parameter='FII_COMPANIES'
	 AND (sysdate BETWEEN TRUNC(s.start_date) AND nvl(TRUNC(s.end_date),to_date('12-31-9999','MM-DD-YYYY')))
     AND s.granted_to = u.employee_id);

Line: 111

    	FII_UTIL.Write_Log ('Inserted ' || SQL%ROWCOUNT || ' rows into fii_company_grants.');

Line: 113

	--DBMS_OUTPUT.PUT_LINE('Inserted ' || SQL%ROWCOUNT || ' rows into fii_company_grants.');

Line: 115

    g_phase := 'Inserting into fii_cost_center_grants from bis_grants_v.';

Line: 117

	INSERT INTO fii_cost_center_grants
	(user_id,
     report_region_code,
     cost_center_id,
     aggregated_flag,
	 last_update_date, last_updated_by,
	 creation_date, created_by, last_update_login)
	(SELECT DISTINCT u.user_id,
            s.report_region_code,
            decode(s.granted_for,
				   -999, l_cost_ctr_top_node_id,
				   s.granted_for),
            h.aggregated_flag,
	        sysdate, g_fii_user_id,
			sysdate, g_fii_user_id, g_fii_login_id
 	 FROM bis_grants_v s,  --user_security_initial2 s,
          fii_cc_pmv_agrt_nodes h,
          fnd_user u
 	 WHERE decode(s.granted_for,
				   -999, l_cost_ctr_top_node_id,
				   s.granted_for) = h.cost_center_id
     AND s.delegation_parameter='HRI_CL_ORGCC'
	 AND (sysdate BETWEEN TRUNC(s.start_date) AND nvl(TRUNC(s.end_date),to_date('12-31-9999','MM-DD-YYYY')))
     AND s.granted_to = u.employee_id);

Line: 143

    	FII_UTIL.Write_Log ('Inserted ' || SQL%ROWCOUNT || ' rows into fii_cost_center_grants.');

Line: 145

	--DBMS_OUTPUT.PUT_LINE('Inserted ' || SQL%ROWCOUNT || ' rows into fii_cost_center_grants.');

Line: 149

	DELETE FROM fii_company_grants
	WHERE (user_id, report_region_code, company_id) IN
	(SELECT s.user_id, s.report_region_code, s.company_id FROM fii_company_grants s, fii_company_hierarchies h
	 WHERE s.company_id = h.child_company_id
 	   AND h.parent_company_id IN (SELECT company_id from fii_company_grants where user_id=s.user_id and report_region_code=s.report_region_code)
 	   AND h.parent_company_id <> h.child_company_id);

Line: 157

    	FII_UTIL.Write_Log ('Deleted ' || SQL%ROWCOUNT || ' rows from fii_company_grants.');

Line: 159

	--DBMS_OUTPUT.PUT_LINE('Deleted ' || SQL%ROWCOUNT || ' rows from fii_company_grants.');

Line: 163

	DELETE FROM fii_cost_center_grants
	WHERE (user_id, report_region_code, cost_center_id) IN
	(SELECT s.user_id, s.report_region_code, s.cost_center_id FROM fii_cost_center_grants s, fii_cost_ctr_hierarchies h
	 WHERE s.cost_center_id = h.child_cc_id
	   AND h.parent_cc_id IN (SELECT cost_center_id from fii_cost_center_grants where user_id=s.user_id and report_region_code=s.report_region_code)
 	   AND h.parent_cc_id <> h.child_cc_id);

Line: 171

    	FII_UTIL.Write_Log ('Deleted ' || SQL%ROWCOUNT || ' rows from fii_cost_center_grants.');

Line: 173

	--DBMS_OUTPUT.PUT_LINE('Deleted ' || SQL%ROWCOUNT || ' rows from fii_cost_center_grants.');