APPS.FII_GL_TOP_SPENDERS_PKG2 SQL Statements

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

Line: 25

	SELECT	1 INTO l_exists
	FROM	dba_tables
	WHERE	table_name = 'PER_EMPDIR_PEOPLE'
		AND owner = fii_util.get_schema_name('PER');

Line: 36

	l_stmt := 'SELECT count(*) FROM per_empdir_people WHERE orig_system = ''PER'' AND rownum <= 1';

Line: 43

		SELECT NULL	FII_MEASURE1,
		       NULL	FII_MEASURE2,
		       NULL	FII_MEASURE3,
		       NULL	FII_MEASURE4,
		       NULL	FII_MEASURE7,
		       NULL	FII_MEASURE5,
		       NULL	FII_MEASURE8,
		       NULL	FII_MEASURE9
		FROM   DUAL
		WHERE  1=2';

Line: 57

		SELECT ppl1.value FII_MEASURE1,
		       ppl2.value FII_MEASURE2,
		       p.FII_MEASURE3 FII_MEASURE3,
		       p.FII_MEASURE4 FII_MEASURE4,
		       p.FII_MEASURE7 FII_MEASURE7,
		       p.RANK_WITHIN_MANAGER_PTD FII_MEASURE5,
		       p.person_id     FII_MEASURE8,
		       DECODE('||l_exists2||', 1, ''pFunctionName=HR_EMPDIR_EMPDTL_PROXY_SS&pId=FII_MEASURE8&OAPB=FII_HR_BRAND_TEXT'', '''') FII_MEASURE9
		FROM   hri_dbi_cl_per_n_v 	ppl1,
		       hri_dbi_cl_per_n_v ppl2,

		(SELECT
                        b.PERSON_ID     PERSON_ID,
                        h.SUP_PERSON_ID DIRECT_MGR_ID,
                        MAX(amount_g)      FII_MEASURE3,
                        MAX(NO_EXP_REPORTS_PTD)    FII_MEASURE4,
                        b.manager_id    FII_MEASURE7,
                        RANK() OVER (PARTITION BY b.MANAGER_ID  ORDER BY MAX(amount_g) DESC) AS RANK_WITHIN_MANAGER_ptd
                   FROM fii_top_spenders_v'||l_global_curr_view||'  b,
                        hri_cs_suph	h
                   WHERE b.PERIOD_ID BETWEEN :START_ID AND :END_ID
                        AND b.MANAGER_ID = &HRI_PERSON+HRI_PER_USRDR_H
                        AND b.slice_type_flag = :SLICE_TYPE_FLAG
                        AND h.sub_person_id (+)= b.person_id
			AND h.sub_relative_level = 1
			AND sysdate BETWEEN h.effective_start_date AND h.effective_end_date
                   GROUP BY person_id, manager_id, h.SUP_PERSON_ID) p

		WHERE sysdate BETWEEN ppl1.effective_start_date (+) AND ppl1.effective_end_date (+)
		      AND sysdate BETWEEN ppl2.effective_start_date (+) AND ppl2.effective_end_date (+)
		      AND p.person_id = ppl1.id (+)
		      AND p.direct_mgr_id = ppl2.id (+)
		      AND p.rank_within_manager_ptd <= 10
		ORDER BY p.FII_MEASURE3 desc';

Line: 136

sqlstmt := 'SELECT
		   x.invoice_num	FII_MEASURE10,
                   headers.report_header_id FII_MEASURE1,
                   x.inv_currency_code FII_MEASURE2,
		   headers.total FII_MEASURE3,
		   SUM(x.amount_g) FII_MEASURE4,
		   tl.name	 FII_MEASURE5,
                   x.account_date FII_MEASURE6,
		   headers.description FII_MEASURE7,
		   SUM(SUM(x.amount_g)) OVER()  FII_ATTRIBUTE1
FROM ap_expense_report_headers_all headers,
     hr_all_organization_units_tl  tl,
	(
	SELECT ap.invoice_num invoice_num,
        	cc.ccc_org_id cost_center,
        	ap.inv_currency_code inv_currency_code,
        	ap.account_date account_date,
        	SUM(ap.'||l_currency_type||') amount_g,
		ap.employee_id employee_id

	FROM fii_ap_inv_b ap,
		fii_org_mgr_mappings cc,
		fii_com_cc_mappings m
	WHERE ap.company_id = m.company_id
		AND ap.cost_center_id = m.cost_center_id
		AND cc.ccc_org_id = m.company_cost_center_org_id
		AND cc.manager_id = :PREV_MGR_ID
		AND   ap.employee_id = :EMP_ID
		AND   ap.account_date BETWEEN to_date(:P_TOP_SPEND_START, ''DD-MM-YYYY'') AND to_date(:P_TOP_SPEND_END, ''DD-MM-YYYY'')
                AND ap.discretionary_expense_flag = ''Y''
	GROUP BY ap.invoice_num, cc.ccc_org_id, ap.account_date, ap.inv_currency_code, ap.employee_id
	) x
    WHERE 	headers.invoice_num = x.invoice_num
    AND NVL(headers.employee_id, x.employee_id) = x.employee_id
	AND tl.organization_id = x.cost_center
	AND tl.language = userenv(''LANG'')
	GROUP BY x.invoice_num, headers.report_header_id, tl.name, headers.description, x.inv_currency_code, x.account_date, headers.total
	&ORDER_BY_CLAUSE';