APPS.FII_AR_UTIL_PKG SQL Statements

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

Line: 169

/* If user views in primary global, use 1st view which SELECTs the primary amount.  For secondary global
currency, use 2nd view which SELECTs secondary amount for Functional currency, use 3rd view which
SELECTs functional amount. */

IF g_currency = 'FII_GLOBAL1' THEN
      g_curr_suffix := '_p_v';

Line: 203

SELECT nvl(min(start_date), trunc(sysdate)) INTO g_min_start_date
FROM	 fii_time_ent_period;

Line: 206

SELECT nvl(min(start_date), g_min_start_date) INTO g_curr_month_start
FROM	 fii_time_ent_period
WHERE  g_as_of_date between start_date and END_date;

Line: 226

      SELECT NVL(fii_time_api.pwk_end(g_as_of_date-84),g_min_start_date) INTO g_sd_prior FROM DUAL;

Line: 227

      SELECT NVL(fii_time_api.ent_sd_lysper_end(fii_time_api.ent_sd_lysper_end(g_as_of_date)),g_min_start_date)  INTO g_sd_prior_prior FROM DUAL;

Line: 228

      SELECT NVL(fii_time_api.cwk_start(g_sd_prior), g_min_start_date) INTO g_sd_curr_sdate FROM DUAL;

Line: 230

      SELECT NVL(fii_time_api.cwk_start(g_as_of_date), g_min_start_date) INTO g_curr_per_start FROM DUAL;

Line: 231

      SELECT NVL(fii_time_api.cwk_end(g_as_of_date), g_min_start_date) INTO g_curr_per_end FROM DUAL;

Line: 232

      SELECT NVL(fii_time_api.pwk_start(g_previous_asof_date), g_min_start_date) INTO g_prior_per_start FROM DUAL;

Line: 233

      SELECT NVL(fii_time_api.pwk_end(g_previous_asof_date), g_min_start_date) INTO g_prior_per_end FROM DUAL;

Line: 239

                SELECT NVL(fii_time_api.ent_sd_pper_end(g_as_of_date),g_min_start_date) INTO g_previous_asof_date FROM dual;

Line: 241

                SELECT NVL(fii_time_api.ent_sd_lysper_END(g_as_of_date),g_min_start_date) INTO g_previous_asof_date FROM dual;

Line: 244

     SELECT NVL(fii_time_api.ent_sd_lysper_end(g_as_of_date), g_min_start_date) INTO g_sd_prior FROM DUAL;

Line: 245

     SELECT NVL(fii_time_api.ent_sd_lysper_end(g_sd_prior), g_min_start_date) INTO g_sd_prior_prior FROM DUAL;

Line: 246

     SELECT fii_time_api.ent_cper_end(g_sd_prior)+1 INTO g_sd_curr_sdate  FROM DUAL;

Line: 249

      SELECT NVL(fii_time_api.ent_cper_start(g_as_of_date), g_min_start_date) INTO g_curr_per_start FROM DUAL;

Line: 250

      SELECT NVL(fii_time_api.ent_cper_end(g_as_of_date), g_min_start_date) INTO g_curr_per_end FROM DUAL;

Line: 251

      SELECT NVL(fii_time_api.ent_cper_start(g_previous_asof_date), g_min_start_date) INTO g_prior_per_start FROM DUAL;

Line: 252

      SELECT NVL(fii_time_api.ent_cper_end(g_previous_asof_date), g_min_start_date) INTO g_prior_per_end FROM DUAL;

Line: 259

                SELECT NVL(fii_time_api.ent_sd_pqtr_end(g_as_of_date),g_min_start_date) INTO g_previous_asof_date FROM dual;

Line: 261

                SELECT NVL(fii_time_api.ent_sd_lysqtr_end(g_as_of_date),g_min_start_date) INTO g_previous_asof_date FROM dual;

Line: 264

      SELECT NVL(fii_time_api.ent_sd_lysqtr_end(g_as_of_date),g_min_start_date) INTO g_sd_prior FROM dual;

Line: 265

      SELECT NVL(fii_time_api.ent_sd_lysqtr_end(g_sd_prior),g_min_start_date) INTO g_sd_prior_prior FROM dual;

Line: 266

      SELECT fii_time_api.ent_cqtr_end(g_sd_prior)+1 INTO g_sd_curr_sdate FROM dual;

Line: 268

      SELECT NVL(fii_time_api.ent_cqtr_start(g_as_of_date), g_min_start_date) INTO g_curr_per_start FROM DUAL;

Line: 269

      SELECT NVL(fii_time_api.ent_cqtr_end(g_as_of_date), g_min_start_date) INTO g_curr_per_end FROM DUAL;

Line: 270

      SELECT NVL(fii_time_api.ent_cqtr_start(g_previous_asof_date), g_min_start_date) INTO g_prior_per_start FROM DUAL;

Line: 271

      SELECT NVL(fii_time_api.ent_cqtr_end(g_previous_asof_date), g_min_start_date) INTO g_prior_per_end FROM DUAL;

Line: 276

      SELECT NVL(fii_time_api.ent_pyr_end(fii_time_api.ent_pyr_end(fii_time_api.ent_pyr_end(fii_time_api.ent_pyr_end(g_as_of_date)))), g_min_start_date) INTO g_sd_prior FROM DUAL;

Line: 277

      SELECT NVL(fii_time_api.ent_pyr_end(g_sd_prior), g_min_start_date) INTO g_sd_prior_prior  FROM DUAL;

Line: 279

      SELECT NVL(fii_time_api.ent_cyr_start(g_as_of_date), g_min_start_date) INTO g_curr_per_start FROM DUAL;

Line: 280

      SELECT NVL(fii_time_api.ent_cyr_end(g_as_of_date), g_min_start_date) INTO g_curr_per_end FROM DUAL;

Line: 281

      SELECT NVL(fii_time_api.ent_cyr_start(g_previous_asof_date), g_min_start_date) INTO g_prior_per_start FROM DUAL;

Line: 282

      SELECT NVL(fii_time_api.ent_cyr_end(g_previous_asof_date), g_min_start_date) INTO g_prior_per_end FROM DUAL;

Line: 288

  SELECT NVL(fii_time_api.ent_sd_lysper_end(g_as_of_date), g_min_start_date) INTO g_sd_prior FROM DUAL;

Line: 289

  SELECT NVL(fii_time_api.ent_sd_lysper_end(g_sd_prior), g_min_start_date) INTO g_sd_prior_prior FROM DUAL;

Line: 290

  SELECT fii_time_api.ent_cper_end(g_sd_prior)+1 INTO g_sd_curr_sdate  FROM DUAL;

Line: 311

SELECT fii_ar_util_pkg.get_dso_period_profile
INTO g_dso_period
FROM dual;

Line: 623

  SELECT dso_value INTO l_flag FROM FII_AR_DSO_SETUP WHERE dso_type = p_category;

Line: 632

	l_dso_sql :=	' SELECT dso_type,dso_value FROM FII_AR_DSO_SETUP ';

Line: 648

		SELECT view_all_organizations_flag, business_group_id
		INTO l_all_org_flag, l_business_group_id
		FROM per_security_profiles
		WHERE security_profile_id = g_security_profile_id;

Line: 675

	SELECT business_group_id
	INTO l_business_group_id
	FROM per_security_profiles
	WHERE security_profile_id = g_security_profile_id;

Line: 689

FUNCTION get_display_currency(p_selected_operating_unit      IN VARCHAR2) RETURN VARCHAR2 IS
l_org_id NUMBER;

Line: 700

    IF(p_selected_operating_unit <> 'ALL') then
        IF (g_operating_unit is null or g_operating_unit <> p_selected_operating_unit) THEN
           g_operating_unit := p_selected_operating_unit;

Line: 704

  	   select currency_code
           into g_functional_currency_code
           from ar_system_parameters_all fsp,
                gl_sets_of_books gsob
           where fsp.org_id = p_selected_operating_unit
           and fsp.set_of_books_id = gsob.set_of_books_id;

Line: 725

	 select distinct currency_code
           into g_common_functional_currency
           from ar_system_parameters_all fsp,
             	gl_sets_of_books gsob
           where fsp.set_of_books_id = gsob.set_of_books_id
		and  fsp.org_id = fsp.org_id ;

Line: 733

	select distinct currency_code
           into g_common_functional_currency
           from ar_system_parameters_all fsp,
             gl_sets_of_books gsob
           where fsp.set_of_books_id = gsob.set_of_books_id
	 	AND fsp.org_id in (SELECT organization_id
				FROM hr_operating_units
				WHERE business_group_id = fii_ar_util_pkg.g_business_group_id) ;

Line: 743

	select distinct currency_code
           into g_common_functional_currency
           from ar_system_parameters_all fsp,
             gl_sets_of_books gsob
           where fsp.set_of_books_id = gsob.set_of_books_id
		AND fsp.org_id in (SELECT organization_id
				FROM per_organization_list
				WHERE security_profile_id = g_security_profile_id) ;

Line: 754

	select distinct currency_code
           into g_common_functional_currency
           from ar_system_parameters_all fsp,
             gl_sets_of_books gsob
           where fsp.set_of_books_id = gsob.set_of_books_id
		AND fsp.org_id = l_org_id ;

Line: 764

 select distinct currency_code
           into g_common_functional_currency
           from ar_system_parameters_all fsp,
             gl_sets_of_books_v gsob
           where fsp.set_of_books_id = gsob.set_of_books_id
        AND (
                (
                        g_det_ou_lov=1 AND fsp.org_id = fsp.org_id
                )
                OR (
                        g_det_ou_lov=2
                        AND fsp.org_id in (
                                SELECT organization_id
                                FROM hr_operating_units
                                WHERE business_group_id = fii_ar_util_pkg.g_business_group_id
                        )
                )
                OR (
                        g_det_ou_lov=3
                        AND fsp.org_id in (
                                SELECT organization_id
                                FROM per_organization_list
                                WHERE security_profile_id = g_security_profile_id
                        )
                )
                OR(
                        g_det_ou_lov=4 AND fsp.org_id = nvl(fnd_profile.value('ORG_ID'), -1)
                )
        );

Line: 820

  SELECT id INTO stmt FROM fii_currencies_v WHERE id = 'FII_GLOBAL1';

Line: 835

l_party_select		VARCHAR2(500):=NULL;

Line: 841

l_org_select		VARCHAR2(240):=NULL;

Line: 842

l_select		VARCHAR2(1500):=NULL;

Line: 843

l_select2		VARCHAR2(1500):=NULL;

Line: 844

l_collector_select	VARCHAR2(100):=NULL;

Line: 848

l_parent_select 	VARCHAR2(500):=NULL;

Line: 852

l_industry_select	VARCHAR2(500):=NULL;

Line: 885

/* This dynamic select in the case of customer dimension is hierarchical checks if the
party chosen is leaf node. If True it gets the immediate parent_party_id  from
fii_customer_hierarches.*/

IF  	g_is_hierarchical_flag  = 'Y'
	AND (g_view_by = 'CUSTOMER+FII_CUSTOMERS' or g_view_by = 'CUSTOMER+FII_CUSTOMER_ACCOUNTS')
	AND (g_count_parent_party_id  = 1  AND g_party_id <> '-111' ) THEN

	l_parent_select := ' f.next_level_is_leaf_flag,(SELECT decode (x.next_level_is_leaf_flag,
     ''Y'', x.parent_party_id, f.child_party_id)
     FROM fii_customer_hierarchies x
     WHERE x.next_level_party_id = f.child_party_id
     AND x.child_party_id = f.child_party_id
     AND x.child_party_id <> x.parent_party_id) parent_party_id ';

Line: 906

	l_parent_select := ' f.next_level_is_leaf_flag,(SELECT decode (x.next_level_is_leaf_flag,
     ''Y'', x.parent_party_id, f.child_party_id)
     FROM fii_customer_hierarchies x
     WHERE x.next_level_party_id = f.child_party_id
     AND x.child_party_id = f.child_party_id
     AND x.child_party_id <> x.parent_party_id) parent_party_id ';

Line: 919

	l_parent_select := ' f.next_level_is_leaf_flag,(SELECT decode (x.next_level_is_leaf_flag,
     ''Y'', x.parent_party_id, f.child_party_id)
     FROM fii_customer_hierarchies x
     WHERE x.next_level_party_id = f.child_party_id
     AND x.child_party_id = f.child_party_id
     AND x.child_party_id <> x.parent_party_id) parent_party_id ';

Line: 928

	--l_parent_select := ' f.next_level_is_leaf_flag, f.parent_party_id ';

Line: 934

	l_parent_select := 'f.next_level_is_leaf_flag, f.parent_party_id  parent_party_id  ' ;

Line: 938

	l_parent_select := 'NULL is_leaf_flag, NULL  parent_party_id ' ;

Line: 946

party selected. In the case when only 1 party is chosen, the where clause is built to
show both parent self record and all its child parties.*/


IF  g_is_hierarchical_flag  = 'Y' AND g_count_parent_party_id  > 1 THEN

  IF  g_view_by = 'ORGANIZATION+FII_OPERATING_UNITS' THEN

	l_party_select := ' ,f.child_party_id child_party_id ';

Line: 963

	l_party_select := ' ,f.child_party_id child_party_id ';

Line: 972

	l_party_select := ' ,f.child_party_id child_party_id ';

Line: 983

	l_party_select := ' ,f.child_party_id child_party_id ';

Line: 999

	l_party_select := ' ,NULL child_party_id ';

Line: 1005

	l_party_select := ' ,f.child_party_id child_party_id ';

Line: 1019

     l_party_select := ' ,f.child_party_id child_party_id ';

Line: 1035

	l_party_select := ' ,NULL child_party_id ';

Line: 1040

	l_party_select := ' ,f.child_party_id child_party_id ';

Line: 1052

	l_party_select := ' ,NULL child_party_id ';

Line: 1058

	l_party_select := ' ,f.child_party_id child_party_id ';

Line: 1070

/* Handles Org related SELECT, FROM and WHERE clause */

	g_security_profile_id := fii_ar_util_pkg.get_sec_profile;

Line: 1080

    	SELECT view_all_organizations_flag, business_group_id
    	INTO l_all_org_flag, l_business_group_id
    	FROM per_security_profiles
    	WHERE security_profile_id = g_security_profile_id;

Line: 1094

   	l_org_select	:=	' p.organization_id, ';

Line: 1099

        		l_org_from := ' ( SELECT per.organization_id,hr.name
    			FROM hr_operating_units per, ar_system_parameters_all ar, hr_all_organization_units hr
    			WHERE per.business_group_id = '||l_business_group_id ||'
    			AND per.organization_id = ar.org_id
    			AND per.organization_id = hr.organization_id '||l_org_specific_where||') p ';

Line: 1106

	       		l_org_from := ' (SELECT per.organization_id
			FROM hr_operating_units per, ar_system_parameters_all ar
			WHERE per.business_group_id = 	'||l_business_group_id ||'
			AND per.organization_id = ar.org_id '||l_org_specific_where||') p ';

Line: 1114

   	l_org_select	:=	' p.organization_id, ';

Line: 1119

        		l_org_from := ' ( SELECT per.organization_id,hr.name
    			FROM hr_operating_units per, hr_all_organization_units hr
    			WHERE per.organization_id = hr.organization_id '||l_org_specific_where||') p ';

Line: 1124

	       		l_org_from := ' (SELECT per.organization_id
			FROM hr_operating_units per
			WHERE 1=1 '||l_org_specific_where||') p ';

Line: 1131

	l_org_select	:=	' p.organization_id, ';

Line: 1136

        		l_org_from := ' ( SELECT per.organization_id,hr.name
    			FROM per_organization_list per, ar_system_parameters_all ar, hr_all_organization_units hr
    			WHERE per.security_profile_id = '||g_security_profile_id||'
    			AND per.organization_id = ar.org_id
    			AND per.organization_id = hr.organization_id '||l_org_specific_where||') p ';

Line: 1143

        		l_org_from := ' (SELECT organization_id
		   	FROM per_organization_list per, ar_system_parameters_all ar
			WHERE per.security_profile_id = '||g_security_profile_id ||'
			AND per.organization_id = ar.org_id '||l_org_specific_where||') p ';

Line: 1155

		l_org_select	:=	g_security_org_id||' organization_id, ';

Line: 1157

		l_org_select	:=	'-1 organization_id, ';

Line: 1161

		l_org_from := ' (SELECT organization_id, name
		FROM hr_all_organization_units
		WHERE organization_id ='||g_security_org_id||') p ';

Line: 1169

	l_org_select	:=	'-1 organization_id, ';

Line: 1173

		l_org_from :=' (SELECT organization_id, name
			FROM hr_all_organization_units
			WHERE organization_id =-1) p ';

Line: 1182

/* Handles the select clause for Collectors */

IF g_view_by = 'FII_COLLECTOR+FII_COLLECTOR' THEN
	l_collector_select := ' c.collector_id, ';

Line: 1189

           l_collector_where := ' exists (select ''x''
                                       from fii_collectors hz
                                       where hz.collector_id = c.collector_id
                                       and hz.party_id = f.child_party_id) ';

Line: 1196

        l_collector_where := ' exists (select ''x''
                                       from fii_collectors hz
                                       where hz.collector_id = c.collector_id '||
                               case when g_party_id <> '-111' then 'AND hz.party_id = f.child_party_id' else '' end||') ';

Line: 1210

	l_collector_select := NULL;

Line: 1214

		l_collector_select := ' NULL collector_id, ';

Line: 1216

		l_collector_select := g_collector_id||' collector_id, ';

Line: 1224

        l_industry_select := ' hz.class_code class_code, hz.class_category class_category, ';

Line: 1231

        l_industry_select := ' c.class_code class_code, c.class_category class_category, ';

Line: 1235

           l_industry_where := ' exists (select ''x''
                                       from fii_party_mkt_class hz
                                       where hz.class_code = c.class_code
                                       and hz.party_id = f.child_party_id) ';

Line: 1257

	l_industry_select := ' NULL class_code, NULL class_category, ';

Line: 1264

		l_industry_select := ' '||g_industry_id||', '''||g_industry_class_type||''', ';

Line: 1270

		l_industry_select := ' '||g_industry_id||' class_code, '||''''||g_industry_class_type||''''||' class_category,';

Line: 1278

/* Code for appending comma, WHERE, AND, GROUP BY clauses to the dynamic SELECT clause */

	IF l_party_from IS NOT NULL AND l_org_from IS NOT NULL THEN
		l_org_from := ','||l_org_from;

Line: 1327

l_select := ' 	SELECT '||l_parent_select||l_party_select||', '
		||l_org_select||l_collector_select||l_industry_select||
		' hz.party_name view_by FROM '||l_party_from||l_org_from||l_party_where||
		l_party_group_by||l_parent_group_by||l_org_group_by;

Line: 1333

l_select := 'SELECT '||l_parent_select||l_party_select||', '
		||l_org_select||l_collector_select||l_industry_select||
		' case when f.parent_party_id = hz.party_id
 				 and f.next_level_is_leaf_flag <> ''Y''
			   then hz.party_name||'' '||g_self_msg||
		 	 ''' else hz.party_name end view_by,
		hz.party_id viewby_code,
		case when f.parent_party_id = hz.party_id
 				 and f.next_level_is_leaf_flag <> ''Y''
			   then ''Y''
		 	 else ''N'' end is_self_flag FROM '||l_party_from||l_org_from||l_industry_from||l_party_where||l_industry_where||
		l_party_group_by||l_parent_group_by||l_org_group_by||l_industry_group_by;

Line: 1353

l_select := ' 	SELECT	'||l_parent_select||l_party_select||', '||l_org_select||l_collector_select||l_industry_select||' c.class_name view_by,
		c.class_code viewby_code, null is_self_flag FROM '||l_party_from||l_org_from||l_industry_from||l_party_where||l_industry_where||
		l_party_group_by||l_org_group_by||l_parent_group_by;

Line: 1364

l_select := ' 	SELECT '||l_parent_select||l_party_select||', '||l_org_select||l_collector_select||l_industry_select||
			' p.name viewby, p.organization_id viewby_code, null is_self_flag FROM '||l_party_from||l_org_from||l_party_where||l_party_group_by||l_parent_group_by;

Line: 1374

l_select := ' 	SELECT 	'||l_parent_select||l_party_select||', '
			||l_org_select||l_collector_select||l_industry_select||' c.name     view_by,
		c.collector_id viewby_code, null is_self_flag FROM '||l_party_from||l_org_from||l_party_where||l_collector_where||
		l_party_group_by||l_org_group_by||l_parent_group_by;

Line: 1383

	SELECT	COUNT(*) INTO l_table_count
	FROM	all_tables
	WHERE	table_name = 'FII_AR_DEBUG_STATEMENTS'
	and owner = l_schema_name;

Line: 1402

		EXECUTE IMMEDIATE 'DELETE FROM '||l_schema_name||'.FII_AR_DEBUG_STATEMENTS WHERE REGION_CODE = '''||g_region_code||''' AND SESSION_ID = '||g_session_id;

Line: 1406

        EXECUTE IMMEDIATE 'INSERT INTO '||l_schema_name||'.FII_AR_DEBUG_STATEMENTS (package,
						                procedure,
								session_id,
								region_code,
								sql_statement,
								value)
				VALUES	       (''FII_AR_UTIL_PKG'',
						''POPULATE_SUMMARY_GT_TABLES'',
						'||g_session_id||','''||g_region_code||''' ,
                                                ''l_select'',
                                                '''||REPLACE(l_select, '''', '''''')||''')';

Line: 1422

EXECUTE IMMEDIATE l_select BULK COLLECT INTO tbl_is_leaf_flag, tbl_parent_party_id, tbl_party_id,
tbl_org_id, tbl_collector_id, tbl_class_code, tbl_class_category, tbl_view_by, tbl_viewby_code, tbl_is_self_flag;

Line: 1426

   INSERT INTO  fii_ar_summary_gt (parent_party_id, party_id, org_id, collector_id, viewby, viewby_code, is_leaf_flag, class_code, class_category, is_self_flag)
   VALUES
   (tbl_parent_party_id(i), tbl_party_id(i), tbl_org_id(i), tbl_collector_id(i),
tbl_view_by(i), tbl_viewby_code(i), tbl_is_leaf_flag(i), tbl_class_code(i), tbl_class_category(i), tbl_is_self_flag(i));

Line: 1432

EXECUTE IMMEDIATE 'INSERT INTO FII_AR_SUMMARY_GT
            (is_leaf_flag, parent_party_id, party_id, org_id, collector_id, class_code, class_category, viewby, viewby_code, is_self_flag) '
             || l_select;

Line: 1447

		l_select2 := 'SELECT ''Y'' is_leaf_flag, -999 parent_party_id, -2 party_id, '
				||l_org_select||l_collector_select||' -1 class_code, '''||g_industry_class_type||''', '||
				' '''||l_unid_message||''' view_by,
				 -2 viewby_code,
				 ''N'' is_self_flag FROM '|| LTRIM(l_org_from, ',');

Line: 1454

		l_select2 := 'SELECT ''Y'' is_leaf_flag, -999 parent_party_id, -2 party_id, '
				||l_org_select||l_collector_select||' -1 class_code, '''||g_industry_class_type||''', '||
				' '''||l_unid_message||''' view_by,
				 -2 viewby_code,
				 ''N'' is_self_flag FROM dual';

Line: 1462

	EXECUTE IMMEDIATE l_select2 BULK COLLECT INTO tbl_is_leaf_flag, tbl_parent_party_id, tbl_party_id,
	tbl_org_id, tbl_collector_id, tbl_class_code, tbl_class_category, tbl_view_by, tbl_viewby_code, tbl_is_self_flag;

Line: 1467

	   INSERT INTO  fii_ar_summary_gt (parent_party_id, party_id, org_id, collector_id, class_code, class_category, viewby, viewby_code, is_leaf_flag, is_self_flag)
	   VALUES
	   (tbl_parent_party_id(i), tbl_party_id(i), tbl_org_id(i), tbl_collector_id(i), tbl_class_code(i), tbl_class_category(i),
	    tbl_view_by(i), tbl_viewby_code(i), tbl_is_leaf_flag(i), tbl_is_self_flag(i));

Line: 1472

        EXECUTE IMMEDIATE 'INSERT INTO FII_AR_SUMMARY_GT
            (is_leaf_flag, parent_party_id, party_id, org_id, collector_id, class_code, class_category, viewby, viewby_code, is_self_flag) '
             || l_select2;

Line: 1482

		l_select2 := 'SELECT null is_leaf_flag, null parent_party_id, '||case when g_party_id = '-2' then '-2' else 'null' end||' party_id, '
				||l_org_select||' -1 collector_id, '||l_industry_select||
				' '''||l_unid_message||''' view_by,
				 -1 viewby_code,
				 null is_self_flag FROM '|| LTRIM(l_org_from, ',');

Line: 1489

		l_select2 := 'SELECT null is_leaf_flag, null parent_party_id, '||case when g_party_id = '-2' then '-2' else 'null' end||' party_id, '
				||l_org_select||' -1 collector_id, '||l_industry_select||
				' '''||l_unid_message||''' view_by,
				 -1 viewby_code,
				 null is_self_flag FROM dual';

Line: 1498

	EXECUTE IMMEDIATE l_select2 BULK COLLECT INTO tbl_is_leaf_flag, tbl_parent_party_id, tbl_party_id,
	tbl_org_id, tbl_collector_id, tbl_class_code, tbl_class_category, tbl_view_by, tbl_viewby_code, tbl_is_self_flag;

Line: 1503

	   INSERT INTO  fii_ar_summary_gt (parent_party_id, party_id, org_id, collector_id, class_code, class_category, viewby, viewby_code, is_leaf_flag, is_self_flag)
	   VALUES
	   (tbl_parent_party_id(i), tbl_party_id(i), tbl_org_id(i), tbl_collector_id(i), tbl_class_code(i), tbl_class_category(i),
	    tbl_view_by(i), tbl_viewby_code(i), tbl_is_leaf_flag(i), tbl_is_self_flag(i));

Line: 1508

        EXECUTE IMMEDIATE 'INSERT INTO FII_AR_SUMMARY_GT
            (is_leaf_flag, parent_party_id, party_id, org_id, collector_id, class_code, class_category, viewby, viewby_code, is_self_flag) '
             || l_select2;

Line: 1518

		l_select2 := 'SELECT ''Y'' is_leaf_flag, -999 parent_party_id, -2 party_id, '
				||l_org_select||l_collector_select||' -1 class_code, '''||g_industry_class_type||''', '||
				' '''||l_unid_message||''' view_by,
				 -2 viewby_code,
				 ''N'' is_self_flag FROM '|| LTRIM(l_org_from, ',');

Line: 1525

		l_select2 := 'SELECT ''Y'' is_leaf_flag, -999 parent_party_id, -2 party_id, '
				||l_org_select||l_collector_select||' -1 class_code, '''||g_industry_class_type||''', '||
				' '''||l_unid_message||''' view_by,
				 -2 viewby_code,
				 ''N'' is_self_flag FROM dual';

Line: 1533

	EXECUTE IMMEDIATE l_select2 BULK COLLECT INTO tbl_is_leaf_flag, tbl_parent_party_id, tbl_party_id,
	tbl_org_id, tbl_collector_id, tbl_class_code, tbl_class_category, tbl_view_by, tbl_viewby_code, tbl_is_self_flag;

Line: 1538

	   INSERT INTO  fii_ar_summary_gt (parent_party_id, party_id, org_id, collector_id, class_code, class_category, viewby, viewby_code, is_leaf_flag, is_self_flag)
	   VALUES
	   (tbl_parent_party_id(i), tbl_party_id(i), tbl_org_id(i), tbl_collector_id(i), tbl_class_code(i), tbl_class_category(i),
	    tbl_view_by(i), tbl_viewby_code(i), tbl_is_leaf_flag(i), tbl_is_self_flag(i));

Line: 1543

        EXECUTE IMMEDIATE 'INSERT INTO FII_AR_SUMMARY_GT
            (is_leaf_flag, parent_party_id, party_id, org_id, collector_id, class_code, class_category, viewby, viewby_code, is_self_flag) '
             || l_select2;

Line: 1551

	insert_into_debug_table;

Line: 1553

        EXECUTE IMMEDIATE 'INSERT INTO '||l_schema_name||'.FII_AR_DEBUG_STATEMENTS (package,
						                procedure,
								session_id,
								region_code,
								sql_statement,
								value)
				VALUES	       (''FII_AR_UTIL_PKG'',
						''POPULATE_SUMMARY_GT_TABLES'',
						'||g_session_id||','''||g_region_code||''' ,
                                                ''l_select2'',
                                                '''||REPLACE(l_select2, '''', '''''')||''')';

Line: 1573

PROCEDURE insert_into_debug_table IS
/* logic for this api...
1. We first search for existence of debug table. If it doesn't exist, we create it else we delete the records inserted for the same session_id and region code.
2. We then insert all columns of corresponding gt table + session_id and report_region_code into debug tables.
*/

l_table_count NUMBER := 0;

Line: 1584

	SELECT	COUNT(*) INTO l_table_count
	FROM	all_tables
	WHERE	table_name = 'FII_AR_DEBUG_SUMMARY'
	and owner = l_schema_name;

Line: 1609

		EXECUTE IMMEDIATE 'DELETE FROM '||l_schema_name||'.FII_AR_DEBUG_SUMMARY WHERE REGION_CODE = '''||g_region_code||''' AND SESSION_ID = '||g_session_id;

Line: 1613

		EXECUTE IMMEDIATE 'INSERT INTO '||l_schema_name||'.FII_AR_DEBUG_SUMMARY (PARENT_PARTY_ID,
						        PARTY_ID,
								ORG_ID,
								COLLECTOR_ID,
								IS_LEAF_FLAG,
								IS_SELF_FLAG,
								CLASS_CODE,
								CLASS_CATEGORY,
								VIEWBY,
								VIEWBY_CODE,
								SESSION_ID,
								REGION_CODE)
				SELECT		gt.PARENT_PARTY_ID,
						    gt.PARTY_ID,
							gt.ORG_ID,
							gt.COLLECTOR_ID,
							gt.IS_LEAF_FLAG,
							gt.IS_SELF_FLAG,
							gt.CLASS_CODE,
							gt.CLASS_CATEGORY,
							gt.VIEWBY,
							gt.VIEWBY_CODE,
							'||g_session_id||','''||g_region_code||'''
				FROM		fii_ar_summary_gt gt';

Line: 1638

END insert_into_debug_table;

Line: 1653

  l_select		VARCHAR2(10000);

Line: 1670

	SELECT INSTR(g_party_id,',', 1, l_occur)  into l_position FROM DUAL;

Line: 1672

    		SELECT LENGTH(g_party_id) INTO l_str_length  FROM DUAL;

Line: 1677

	SELECT SUBSTR(g_party_id, l_substr_start, l_substr_val)  into l_tmp_party_id FROM DUAL;

Line: 1694

l_select := ' SELECT parent_party_id FROM fii_customer_hierarchies p
		WHERE p.parent_party_id=p.next_level_party_id
		AND p.next_level_party_id = p.child_party_id
		AND p.child_party_id IN ('||l_parse_party_id||')
		AND NOT EXISTS (SELECT  c.child_party_id FROM fii_customer_hierarchies c
			WHERE c.child_party_id IN ('||l_parse_party_id||')
			AND   c.parent_party_id IN ('||l_parse_party_id||')
			AND c.parent_party_id <> c.next_level_party_id
			and c.child_party_id = p.child_party_id) ';

Line: 1704

EXECUTE IMMEDIATE l_select BULK COLLECT INTO tbl_parent_party_id;

Line: 1910

select nvl(last_refresh_date,sysdate) INTO g_page_refresh_date
from bis_obj_properties
where object_name = 'FII_AR_STATUS_DASHBOARD'
and object_type = 'PAGE';