APPS.FII_GL_TOP_SPENDERS_PKG SQL Statements

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

Line: 23

    SELECT 1 INTO l_ap_row_cnt
    FROM FII_AP_INV_B
    WHERE rownum = 1;

Line: 86

g_phase := 'Insert month slices into fii_top_spenders_stg table';

Line: 88

INSERT /*+ append parallel(stg) */ INTO FII_TOP_SPENDERS_STG (
	period_id,
	slice_type_flag,
	qtr_id,
	year_id,
	person_id,
	ccc_org_id,
	prim_amount_g,
	sec_amount_g,
	no_of_exp_rpts,
	last_update_date,
	last_updated_by,
	creation_date,
	created_by,
	last_update_login)
SELECT /*+ ordered use_hash(b,m) parallel(b) parallel(m) */
	t.ENT_PERIOD_ID,
	'M',
	t.ENT_QTR_ID,
	t.ENT_YEAR_ID,
	b.employee_id,
	m.COMPANY_COST_CENTER_ORG_ID,
	sum(b.PRIM_AMOUNT_G),
	sum(b.SEC_AMOUNT_G) ,
	count(distinct b.INVOICE_ID),
	sysdate,
	g_fii_user_id,
	sysdate,
    g_fii_user_id,
    g_fii_login_id
FROM fii_time_day t, FII_AP_INV_B b,  FII_COM_CC_MAPPINGS m
WHERE b.discretionary_expense_flag = 'Y'
      and b.account_Date = t.report_date
      and b.COMPANY_ID = m.COMPANY_ID
      and b.COST_CENTER_ID = m.COST_CENTER_ID
GROUP BY m.COMPANY_COST_CENTER_ORG_ID, b.employee_id, t.ENT_YEAR_ID,  t.ENT_QTR_ID, t.ENT_PERIOD_ID;

Line: 126

   fii_util.write_log('Inserted '||SQL%ROWCOUNT||' rows into FII_TOP_SPENDERS_STG');

Line: 153

g_phase := 'Insert quarter slices into fii_top_spenders_stg table';

Line: 155

INSERT /*+ append parallel(stg) */ INTO FII_TOP_SPENDERS_STG (
	period_id,
	slice_type_flag,
	qtr_id,
	year_id,
	person_id,
	ccc_org_id,
	prim_amount_g,
	sec_amount_g,
	no_of_exp_rpts,
	last_update_date,
	last_updated_by,
	creation_date,
	created_by,
	last_update_login)

SELECT /*+ parallel(t) */
	t.PERIOD_ID,
	'Q',
	t.QTR_ID,
	t.YEAR_ID,
	t.person_id,
	t.CCC_ORG_ID,
	SUM(t.PRIM_AMOUNT_G) OVER (PARTITION BY t.QTR_ID, t.YEAR_ID, t.CCC_ORG_ID, t.person_id  ORDER BY t.period_id ROWS UNBOUNDED PRECEDING) AS PRIM_AMOUNT_G,
	SUM(t.SEC_AMOUNT_G) OVER (PARTITION BY t.QTR_ID, t.YEAR_ID, t.CCC_ORG_ID, t.person_id  ORDER BY t.period_id ROWS UNBOUNDED PRECEDING) AS SEC_AMOUNT_G,
	SUM(t.no_of_exp_rpts) OVER (PARTITION BY t.QTR_ID, t.YEAR_ID, t.CCC_ORG_ID, t.person_id  ORDER BY t.period_id ROWS UNBOUNDED PRECEDING) AS no_of_exp_rpts,
	sysdate,
	g_fii_user_id,
	sysdate,
        g_fii_user_id,
        g_fii_login_id
FROM fii_top_spenders_stg t;

Line: 189

   fii_util.write_log('Inserted '||SQL%ROWCOUNT||' rows into FII_TOP_SPENDERS_STG');

Line: 217

g_phase := 'Insert year slices into fii_top_spenders_stg table';

Line: 219

INSERT /*+ append parallel(stg) */  INTO FII_TOP_SPENDERS_STG (
	period_id,
	slice_type_flag,
	qtr_id,
	year_id,
	person_id,
	ccc_org_id,
	prim_amount_g,
	sec_amount_g,
	no_of_exp_rpts,
	last_update_date,
	last_updated_by,
	creation_date,
	created_by,
	last_update_login)
SELECT /*+ append parallel(b) */
	b.PERIOD_ID,
	'Y',
	b.QTR_ID,
	b.YEAR_ID,
	b.person_id,
	b.CCC_ORG_ID,
	SUM(B.PRIM_AMOUNT_G) OVER (PARTITION BY B.YEAR_ID, b.CCC_ORG_ID, b.person_id  ORDER BY b.period_id ROWS UNBOUNDED PRECEDING) AS PRIM_AMOUNT_G,
	SUM(B.SEC_AMOUNT_G) OVER (PARTITION BY B.YEAR_ID, b.CCC_ORG_ID, b.person_id  ORDER BY b.period_id ROWS UNBOUNDED PRECEDING) AS SEC_AMOUNT_G,
	SUM(B.no_of_exp_rpts) OVER (PARTITION BY B.YEAR_ID, b.CCC_ORG_ID, b.person_id  ORDER BY b.period_id ROWS UNBOUNDED PRECEDING) AS no_of_exp_rpts,
	sysdate,
	g_fii_user_id,
	sysdate,
        g_fii_user_id,
        g_fii_login_id
FROM fii_top_spenders_stg b
WHERE b.slice_type_flag = 'M';

Line: 253

   fii_util.write_log('Inserted '||SQL%ROWCOUNT||' rows into FII_TOP_SPENDERS_STG');

Line: 280

g_phase := 'Insert rows into fii_top_spndr_sum_b table';

Line: 282

INSERT /*+ append parallel(b) */ INTO   fii_top_spndr_sum_b (
	person_id,
	period_id,
	slice_type_flag,
	manager_id,
	rank_within_manager_ptd,
	prim_ptd_g,
	sec_ptd_g,
	no_exp_reports_ptd,
	last_update_date,
	last_updated_by,
	creation_date,
	created_by,
	last_update_login)
SELECT
	person_id,
	period_id,
	slice_type_flag,
	manager_id,
	RANK_WITHIN_MANAGER_ptd,
	prim_ptd_g,
	sec_ptd_g,
	no_of_exp_rpts_ptd,
	sysdate,
	g_fii_user_id,
	sysdate,
        g_fii_user_id,
        g_fii_login_id
FROM (SELECT /*+ ordered use_hash(stg) parallel(stg) */
         stg.person_id person_id,
	     stg.period_id period_id,
	     stg.slice_type_flag,
	     help.manager_id manager_id,
	     RANK() OVER (PARTITION BY stg.period_id, stg.slice_type_flag, help.manager_id
	     ORDER BY sum(stg.prim_amount_g) DESC) AS RANK_WITHIN_MANAGER_ptd ,
	     sum(stg.prim_amount_g) prim_ptd_g,
	     sum(stg.sec_amount_g) sec_ptd_g,
	     sum(stg.no_of_exp_rpts) no_of_exp_rpts_ptd
	FROM  fii_org_mgr_mappings help, fii_top_spenders_stg stg
	WHERE help.ccc_org_id = stg.ccc_org_id
	GROUP BY stg.person_id ,
		 stg.period_id ,
		 stg.slice_type_flag,
		 help.manager_id ) x
WHERE x.RANK_WITHIN_MANAGER_ptd <= 10;

Line: 329

   fii_util.write_log('Inserted '||SQL%ROWCOUNT||' rows into FII_TOP_SPNDR_SUM_B');