1 PACKAGE hri_bpl_fact_sup_wrkfc_sql AS
2 /* $Header: hribfwrk.pkh 120.1 2006/08/18 06:22:08 rkonduru noship $ */
3
4 /******************************************************************************/
5 /* Column aliases returned: */
6 /* ------------------------ */
7 /* The following columns are always returned whatever parameters are set: */
8 /* vby_id (group by column corresponding to selected view by) */
9 /* direct_ind (whether the row is the direct reports summary row) */
10 /* */
11 /* The column list below is returned if the include_hdc parameter is set: */
12 /* curr_hdc_end */
13 /* curr_hdc_start (if include_start parameter is set) */
14 /* curr_hdc_<bucket> (if bucket_dim parameter is set) */
15 /* comp_hdc_end (if include_comp parameter is set) */
16 /* comp_hdc_start (if include_comp and include_start are set) */
17 /* comp_hdc_<bucket> (if include_comp and bucket_dim are set) */
18 /* */
19 /* In addition to the above list, a further list of columns is added to allow */
20 /* grand totals to be calculated. */
21 /* curr_total_hdc_start (if include_start parameter is set) */
22 /* curr_total_hdc_<bucket> (if bucket_dim parameter is set) */
23 /* comp_total_hdc_end (if include_comp parameter is set) */
24 /* comp_total_hdc_start (if include_comp and include_start are set) */
25 /* comp_total_hdc_<bucket> (if include_comp and bucket_dim are set) */
26 /* */
27 /* An analagous column set is returned for every measure, except for the */
28 /* _start columns which are only added on for the headcount measure. */
29 /* */
30 /* Example */
31 /* ------- */
32 /* For example if: */
33 /* include_comp = 'N' */
34 /* include_start = 'Y' */
35 /* bucket_dim = '' */
36 /* */
37 /* and all other measure parameters are set then the following column list */
38 /* will be returned: */
39 /* curr_hdc_end */
40 /* curr_hdc_start */
41 /* curr_sal_end */
42 /* curr_low_end */
43 /* curr_total_hdc_start */
44 /* */
45 /******************************************************************************/
46
47 TYPE wrkfc_fact_param_type IS RECORD
48 (bind_format VARCHAR2(30),
49 include_comp VARCHAR2(30) DEFAULT 'N',
50 include_start VARCHAR2(30) DEFAULT 'N',
51 include_hdc VARCHAR2(30) DEFAULT 'N',
52 include_sal VARCHAR2(30) DEFAULT 'N',
53 include_low VARCHAR2(30) DEFAULT 'N',
54 include_pasg_cnt VARCHAR2(30) DEFAULT 'N',
55 kpi_mode VARCHAR2(30) DEFAULT 'N',
56 bucket_dim VARCHAR2(100) DEFAULT NULL);
57
58 PROCEDURE set_fact_table
59 (p_parameter_rec IN hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE,
60 p_bucket_dim IN VARCHAR2,
61 p_include_sal IN VARCHAR2,
62 p_parameter_count IN PLS_INTEGER,
63 p_single_param IN VARCHAR2,
64 p_use_snapshot IN OUT NOCOPY BOOLEAN,
65 p_fact_table OUT NOCOPY VARCHAR2);
66
67 FUNCTION get_sql
68 (p_parameter_rec IN hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE,
69 p_bind_tab IN hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE,
70 p_wrkfc_params IN wrkfc_fact_param_type)
71 RETURN VARCHAR2;
72
73 FUNCTION get_sql
74 (p_parameter_rec IN hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE,
75 p_bind_tab IN hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE,
76 p_wrkfc_params IN wrkfc_fact_param_type,
77 p_calling_module IN VARCHAR2)
78 RETURN VARCHAR2;
79
80 END hri_bpl_fact_sup_wrkfc_sql;
[Home] [Help]