1 PACKAGE hri_bpl_fact_sup_wcnt_chg_sql AS
2 /* $Header: hribfwch.pkh 120.0 2005/05/29 07:03:05 appldev 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_hire parameter is set: */
12 /* */
13 /* curr_hire_hdc */
14 /* curr_hire_hdc_<bucket> (if bucket_dim parameter is set) */
15 /* comp_hire_hdc (if include_comp parameter is set) */
16 /* comp_hire_hdc_<bucket> (if include_comp and bucket_dim are set) */
17 /* */
18 /* An analagous column set is returned for every measure, except for the */
19 /* length of service parameter include_low. If this is set then there are */
20 /* two sets of columns added for the length of service in months and days. */
21 /* */
22 /* Example */
23 /* ------- */
24 /* For example if: */
25 /* include_comp = 'N' */
26 /* bucket_dim = '' */
27 /* */
28 /* and all other measure parameters are set then the following column list */
29 /* will be returned: */
30 /* curr_hire_hdc */
31 /* curr_transfer_in_hdc */
32 /* curr_transfer_out_hdc */
33 /* curr_termination_hdc */
34 /* curr_term_vol_hdc */
35 /* curr_term_inv_hdc */
36 /* curr_low_months */
37 /* curr_low_days */
38 /* */
39 /******************************************************************************/
40
41 TYPE wcnt_chg_fact_param_type IS RECORD
42 (bind_format VARCHAR2(30),
43 include_comp VARCHAR2(30) DEFAULT 'N',
44 include_hire VARCHAR2(30) DEFAULT 'N',
45 include_trin VARCHAR2(30) DEFAULT 'N',
46 include_trout VARCHAR2(30) DEFAULT 'N',
47 include_term VARCHAR2(30) DEFAULT 'N',
48 include_sep VARCHAR2(30) DEFAULT 'N',
49 include_sep_inv VARCHAR2(30) DEFAULT 'N',
50 include_sep_vol VARCHAR2(30) DEFAULT 'N',
51 include_low VARCHAR2(30) DEFAULT 'N',
52 kpi_mode VARCHAR2(30) DEFAULT 'N',
53 bucket_dim VARCHAR2(100) DEFAULT NULL);
54
55 PROCEDURE set_fact_table
56 (p_parameter_rec IN hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE,
57 p_bucket_dim IN VARCHAR2,
58 p_include_hire IN VARCHAR2,
59 p_include_trin IN VARCHAR2,
60 p_include_trout IN VARCHAR2,
61 p_include_term IN VARCHAR2,
62 p_include_low IN VARCHAR2,
63 p_parameter_count IN PLS_INTEGER,
64 p_single_param IN VARCHAR2,
65 p_use_snapshot IN OUT NOCOPY BOOLEAN,
66 p_fact_table OUT NOCOPY VARCHAR2);
67
68 FUNCTION get_sql
69 (p_parameter_rec IN hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE,
70 p_bind_tab IN hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE,
71 p_wcnt_chg_params IN wcnt_chg_fact_param_type)
72 RETURN VARCHAR2;
73
74 FUNCTION get_sql
75 (p_parameter_rec IN hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE,
76 p_bind_tab IN hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE,
77 p_wcnt_chg_params IN wcnt_chg_fact_param_type,
78 p_calling_module IN VARCHAR2)
79 RETURN VARCHAR2;
80
81 END hri_bpl_fact_sup_wcnt_chg_sql;