[Home] [Help]
PACKAGE BODY: APPS.FII_GL_TOP_SPENDERS_PKG2
Source
1 PACKAGE BODY FII_GL_TOP_SPENDERS_PKG2 AS
2 /* $Header: FIIGLC4B.pls 120.4 2006/05/05 10:39:21 hpoddar noship $ */
3
4 PROCEDURE get_top_spenders (p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL,
5 top_spenders_sql out NOCOPY VARCHAR2, top_spenders_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
6
7 l_global_curr_view VARCHAR2(1);
8 sqlstmt VARCHAR2(15000);
9 l_exists NUMBER := 0;
10 l_exists2 NUMBER := 0;
11 l_url VARCHAR2(200) := NULL;
12 l_stmt VARCHAR2(200) ;
13
14 BEGIN
15
16 fii_gl_util_pkg.reset_globals;
17 fii_gl_util_pkg.get_parameters(p_page_parameter_tbl);
18
19 l_global_curr_view := fii_gl_util_pkg.g_global_curr_view;
20
21 /* Hardcoded 'sys' schema name to use sys.all_tables (instead of all_tables) in order to make the code gscc compliant */
22
23 BEGIN
24
25 SELECT 1 INTO l_exists
26 FROM dba_tables
27 WHERE table_name = 'PER_EMPDIR_PEOPLE'
28 AND owner = fii_util.get_schema_name('PER');
29
30 EXCEPTION
31 WHEN NO_DATA_FOUND THEN
32 l_exists := 0;
33 END;
34
35 IF l_exists > 0 THEN
36 l_stmt := 'SELECT count(*) FROM per_empdir_people WHERE orig_system = ''PER'' AND rownum <= 1';
37 EXECUTE IMMEDIATE(l_stmt) INTO l_exists2;
38 END IF;
39
40 IF fii_gl_util_pkg.g_mgr_id = -99999 THEN
41
42 sqlstmt := '
43 SELECT NULL FII_MEASURE1,
44 NULL FII_MEASURE2,
45 NULL FII_MEASURE3,
46 NULL FII_MEASURE4,
47 NULL FII_MEASURE7,
48 NULL FII_MEASURE5,
49 NULL FII_MEASURE8,
50 NULL FII_MEASURE9
51 FROM DUAL
52 WHERE 1=2';
53
54 ELSE
55
56 sqlstmt := '
57 SELECT ppl1.value FII_MEASURE1,
58 ppl2.value FII_MEASURE2,
59 p.FII_MEASURE3 FII_MEASURE3,
60 p.FII_MEASURE4 FII_MEASURE4,
61 p.FII_MEASURE7 FII_MEASURE7,
62 p.RANK_WITHIN_MANAGER_PTD FII_MEASURE5,
63 p.person_id FII_MEASURE8,
64 DECODE('||l_exists2||', 1, ''pFunctionName=HR_EMPDIR_EMPDTL_PROXY_SS&pId=FII_MEASURE8&OAPB=FII_HR_BRAND_TEXT'', '''') FII_MEASURE9
65 FROM hri_dbi_cl_per_n_v ppl1,
66 hri_dbi_cl_per_n_v ppl2,
67
68 (SELECT
69 b.PERSON_ID PERSON_ID,
70 h.SUP_PERSON_ID DIRECT_MGR_ID,
71 MAX(amount_g) FII_MEASURE3,
72 MAX(NO_EXP_REPORTS_PTD) FII_MEASURE4,
73 b.manager_id FII_MEASURE7,
74 RANK() OVER (PARTITION BY b.MANAGER_ID ORDER BY MAX(amount_g) DESC) AS RANK_WITHIN_MANAGER_ptd
75 FROM fii_top_spenders_v'||l_global_curr_view||' b,
76 hri_cs_suph h
77 WHERE b.PERIOD_ID BETWEEN :START_ID AND :END_ID
78 AND b.MANAGER_ID = &HRI_PERSON+HRI_PER_USRDR_H
79 AND b.slice_type_flag = :SLICE_TYPE_FLAG
80 AND h.sub_person_id (+)= b.person_id
81 AND h.sub_relative_level = 1
82 AND sysdate BETWEEN h.effective_start_date AND h.effective_end_date
83 GROUP BY person_id, manager_id, h.SUP_PERSON_ID) p
84
85 WHERE sysdate BETWEEN ppl1.effective_start_date (+) AND ppl1.effective_end_date (+)
86 AND sysdate BETWEEN ppl2.effective_start_date (+) AND ppl2.effective_end_date (+)
87 AND p.person_id = ppl1.id (+)
88 AND p.direct_mgr_id = ppl2.id (+)
89 AND p.rank_within_manager_ptd <= 10
90 ORDER BY p.FII_MEASURE3 desc';
91
92 END IF;
93
94 fii_gl_util_pkg.bind_variable(sqlstmt, p_page_parameter_tbl, top_spenders_sql, top_spenders_output);
95
96 END get_top_spenders;
97
98
99 PROCEDURE get_top_spenders_drilldown
100 (p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL,top_spenders_drilldown_sql out NOCOPY VARCHAR2,
101 top_spenders_drilldown_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
102
103 i NUMBER;
104 sqlstmt VARCHAR2(15000);
105 l_currency VARCHAR2(50);
106 l_currency_type VARCHAR2(20);
107
108
109 BEGIN
110
111 fii_gl_util_pkg.reset_globals;
112 fii_gl_util_pkg.get_parameters(p_page_parameter_tbl);
113 fii_gl_util_pkg.get_bitmasks;
114 l_currency := fii_gl_util_pkg.g_currency;
115
116
117 IF (p_page_parameter_tbl.count > 0) THEN
118 FOR i IN p_page_parameter_tbl.first..p_page_parameter_tbl.last LOOP
119 IF p_page_parameter_tbl(i).parameter_name = 'FII_MEASURE8' THEN
120 fii_gl_util_pkg.g_prev_mgr_id := p_page_parameter_tbl(i).parameter_value;
121 END IF;
122 IF p_page_parameter_tbl(i).parameter_name = 'FII_MEASURE9' THEN
123 fii_gl_util_pkg.g_emp_id := p_page_parameter_tbl(i).parameter_value;
124 END IF;
125 END LOOP;
126 END IF;
127
128
129 IF l_currency = 'FII_GLOBAL1' THEN
130 l_currency_type := 'prim_amount_g';
131 ELSIF l_currency = 'FII_GLOBAL2' THEN
132 l_currency_type := 'sec_amount_g';
133 ELSE l_currency_type := 'prim_amount_g';
134 END IF;
135
136 sqlstmt := 'SELECT
137 x.invoice_num FII_MEASURE10,
138 headers.report_header_id FII_MEASURE1,
139 x.inv_currency_code FII_MEASURE2,
140 headers.total FII_MEASURE3,
141 SUM(x.amount_g) FII_MEASURE4,
142 tl.name FII_MEASURE5,
143 x.account_date FII_MEASURE6,
144 headers.description FII_MEASURE7,
145 SUM(SUM(x.amount_g)) OVER() FII_ATTRIBUTE1
146 FROM ap_expense_report_headers_all headers,
147 hr_all_organization_units_tl tl,
148 (
149 SELECT ap.invoice_num invoice_num,
150 cc.ccc_org_id cost_center,
151 ap.inv_currency_code inv_currency_code,
152 ap.account_date account_date,
153 SUM(ap.'||l_currency_type||') amount_g,
154 ap.employee_id employee_id
155
156 FROM fii_ap_inv_b ap,
157 fii_org_mgr_mappings cc,
158 fii_com_cc_mappings m
159 WHERE ap.company_id = m.company_id
160 AND ap.cost_center_id = m.cost_center_id
161 AND cc.ccc_org_id = m.company_cost_center_org_id
162 AND cc.manager_id = :PREV_MGR_ID
163 AND ap.employee_id = :EMP_ID
164 AND ap.account_date BETWEEN to_date(:P_TOP_SPEND_START, ''DD-MM-YYYY'') AND to_date(:P_TOP_SPEND_END, ''DD-MM-YYYY'')
165 AND ap.discretionary_expense_flag = ''Y''
166 GROUP BY ap.invoice_num, cc.ccc_org_id, ap.account_date, ap.inv_currency_code, ap.employee_id
167 ) x
168 WHERE headers.invoice_num = x.invoice_num
169 AND NVL(headers.employee_id, x.employee_id) = x.employee_id
170 AND tl.organization_id = x.cost_center
171 AND tl.language = userenv(''LANG'')
172 GROUP BY x.invoice_num, headers.report_header_id, tl.name, headers.description, x.inv_currency_code, x.account_date, headers.total
173 &ORDER_BY_CLAUSE';
174
175 fii_gl_util_pkg.bind_variable(sqlstmt, p_page_parameter_tbl, top_spenders_drilldown_sql, top_spenders_drilldown_output);
176
177 END get_top_spenders_drilldown;
178
179 END fii_gl_top_spenders_pkg2;
180