[Home] [Help]
PACKAGE: APPS.BIX_PMV_DBI_UTL_PKG
Source
1 PACKAGE BIX_PMV_DBI_utl_pkg AS
2 /*$Header: bixdutls.pls 120.1 2006/03/28 22:49:04 pubalasu noship $ */
3
4 g_email_accnt_dim CONSTANT VARCHAR2 (100) := 'EMAIL ACCOUNT+EMAIL ACCOUNT';
5 g_email_class_dim CONSTANT VARCHAR2 (100) := 'EMAIL CLASSIFICATION+EMAIL CLASSIFICATION';
6 g_ai_ccntr_dim CONSTANT VARCHAR2 (100) := 'BIX_TELEPHONY+BIX_CALL_CENTER';
7 g_ai_class_dim CONSTANT VARCHAR2 (100) := 'BIX_TELEPHONY+BIX_CALL_CLASSIFICATION';
8 g_ai_dnis_dim CONSTANT VARCHAR2 (100) := 'BIX_TELEPHONY+BIX_DNIS';
9 g_ai_custm_dim CONSTANT VARCHAR2(100) := 'CUSTOMER';
10
11 g_agent_group_dim CONSTANT VARCHAR2 (100) := 'ORGANIZATION+JTF_ORG_SUPPORT_GROUP';
12
13
14 g_c_period_start_date CONSTANT VARCHAR2 (60) := '&BIS_CURRENT_EFFECTIVE_START_DATE';
15 g_p_period_start_date CONSTANT VARCHAR2 (60) := '&BIS_PREVIOUS_EFFECTIVE_START_DATE';
16 g_c_as_of_date CONSTANT VARCHAR2 (60) := '&BIS_CURRENT_ASOF_DATE';
17 g_p_as_of_date CONSTANT VARCHAR2 (60) := '&BIS_PREVIOUS_ASOF_DATE';
18 g_pp_date CONSTANT VARCHAR2 (60) := '&PREV_PREV_DATE';
19 g_c_period_end_date CONSTANT VARCHAR2 (60) := '&BIS_CURRENT_EFFECTIVE_END_DATE';
20 g_p_period_end_date CONSTANT VARCHAR2 (60) := '&BIS_PREVIOUS_EFFECTIVE_END_DATE';
21
22 /* Two bitmap variables used for the inlist generation */
23 g_inlist_xed CONSTANT NUMBER := 1; -- Bit 0
24 g_inlist_xtd CONSTANT NUMBER := 2; -- Bit 1
25 g_inlist_ytd CONSTANT NUMBER := 4; -- Bit 2
26
27 /* for balance */
28 g_c_as_of_date_balance constant varchar2(60) := 'least(&BIS_CURRENT_EFFECTIVE_END_DATE,&LAST_COLLECTION)';
29 g_p_as_of_date_balance constant varchar2(60) := 'least(&BIS_PREVIOUS_EFFECTIVE_END_DATE,&LAST_COLLECTION)';
30 g_c_as_of_date_o_balance constant varchar2(70) := 'least((&BIS_CURRENT_EFFECTIVE_START_DATE -1),&LAST_COLLECTION)';
31
32 /* for rolling and balance */
33 g_inlist_rlx constant number := 8; -- Bit 3
34 g_inlist_bal constant number := 16; -- Bit 4
35
36
37 PROCEDURE process_parameters (
38 p_param IN bis_pmv_page_parameter_tbl
39 , p_trend IN VARCHAR2
40 , p_func_area IN VARCHAR2
41 , p_version IN VARCHAR2
42 , p_mv_set IN VARCHAR2 /*-- ITM/OTM/ECM --*/
43 , p_where_clause OUT NOCOPY VARCHAR2
44 , p_mv OUT NOCOPY VARCHAR2
45 , p_join_tbl OUT NOCOPY poa_dbi_util_pkg.poa_dbi_join_tbl
46 , p_comp_type OUT NOCOPY VARCHAR2
47 , p_xtd OUT NOCOPY VARCHAR2
48 , p_view_by_select OUT NOCOPY VARCHAR2
49 , p_view_by OUT NOCOPY VARCHAR2
50 );
51
52
53 PROCEDURE get_bind_vars (
54 x_custom_output IN OUT NOCOPY bis_query_attributes_tbl
55 , p_func_area IN VARCHAR2);
56
57 FUNCTION get_orr_views RETURN VARCHAR2;
58
59 FUNCTION status_sql_daylevel (
60 p_fact_name IN VARCHAR2
61 , p_row_type_where_clause IN VARCHAR2
62 , p_col_name IN poa_dbi_util_pkg.poa_dbi_col_tbl
63 , p_join_tables IN poa_dbi_util_pkg.poa_dbi_join_tbl
64 , p_time_type IN VARCHAR2 default 'ESD'
65 , p_union IN VARCHAR2 default 'ALL'
66 )
67 RETURN VARCHAR2;
68
69 FUNCTION GET_DIVIDED_MEASURE (
70 p_percentage VARCHAR2
71 , p_num VARCHAR2
72 , p_denom VARCHAR2
73 , p_measurecol VARCHAR2
74 , p_pnum VARCHAR2:=NULL
75 , p_pdenom VARCHAR2:=NULL
76 , p_totalcol VARCHAR2:=NULL
77 , p_changecol VARCHAR2:=NULL
78 , p_changetotalcol VARCHAR2:=NULL
79 , p_convunitfordenom VARCHAR2:=NULL
80 ) RETURN VARCHAR2;
81
82 FUNCTION GET_PERTOTAL_MEASURE (
83 p_num VARCHAR2,
84 p_measurecol VARCHAR2
85 ) RETURN VARCHAR2;
86
87 FUNCTION GET_DEVAVG_MEASURE (
88 p_percentage VARCHAR2,
89 p_num VARCHAR2,
90 p_denom VARCHAR2,
91 p_col VARCHAR2,
92 p_convunitfordenom VARCHAR2:=NULL
93 ) RETURN VARCHAR2;
94
95 FUNCTION GET_SIMPLE_MEASURE (
96 p_curr VARCHAR2
97 , p_measurecol VARCHAR2
98 , p_prev VARCHAR2:=NULL
99 , p_totalcol VARCHAR2:=NULL
100 , p_changecol VARCHAR2:=NULL
101 , p_changetotalcol VARCHAR2:=NULL
102 , p_convertunit VARCHAR2:=NULL
103 ) RETURN VARCHAR2;
104
105 FUNCTION TREND_SQL (
106 p_xtd IN VARCHAR2
107 , p_comparison_type IN VARCHAR2
108 , p_fact_name IN VARCHAR2
109 , p_where_clause IN VARCHAR2
110 , p_col_name IN poa_dbi_util_pkg.poa_dbi_col_tbl
111 , p_use_grpid IN VARCHAR2 := 'Y'
112 , p_in_join_tables IN poa_dbi_util_pkg.poa_dbi_join_tbl
113 , p_fact_hint IN VARCHAR2 := null
114 , p_union_clause IN VARCHAR2 DEFAULT NULL)
115 RETURN VARCHAR2;
116
117 FUNCTION GET_CONTINUED_MEASURES(
118 p_bix_col_tab IN OUT NOCOPY poa_dbi_util_pkg.poa_dbi_col_tbl,
119 p_where_clause IN OUT NOCOPY VARCHAR2,
120 p_xtd IN VARCHAR2,
121 p_comparison_type IN VARCHAR2,
122 p_mv_set IN VARCHAR2
123 ) RETURN VARCHAR2;
124
125 PROCEDURE get_emc_page_params (
126 p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
127 , l_as_of_date OUT NOCOPY DATE
128 , l_period_type OUT NOCOPY VARCHAR2
129 , l_record_type_id OUT NOCOPY NUMBER
130 , l_comp_type OUT NOCOPY VARCHAR2
131 , l_account OUT NOCOPY VARCHAR2
132 , l_classification OUT NOCOPY VARCHAR2
133 , l_view_by OUT NOCOPY VARCHAR2);
134
135
136 FUNCTION period_start_date(
137 l_as_of_date IN DATE
138 , l_period_type IN VARCHAR2 ) RETURN DATE;
139
140 FUNCTION GET_DEFAULT_PARAMS RETURN VARCHAR2;
141
142 PROCEDURE get_ai_page_params (
143 p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
144 , l_as_of_date OUT NOCOPY DATE
145 , l_period_type OUT NOCOPY VARCHAR2
146 , l_record_type_id OUT NOCOPY NUMBER
147 , l_comp_type OUT NOCOPY VARCHAR2
148 , l_call_center OUT NOCOPY VARCHAR2
149 , l_classification OUT NOCOPY VARCHAR2
150 , l_dnis OUT NOCOPY VARCHAR2
151 , l_view_by OUT NOCOPY VARCHAR2);
152
153 FUNCTION GET_AI_DEFAULT_PAGE_PARAMS RETURN VARCHAR2;
154
155
156 PROCEDURE GET_AO_PAGE_PARAMS (
157 p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
158 , l_as_of_date OUT NOCOPY DATE
159 , l_period_type OUT NOCOPY VARCHAR2
160 , l_record_type_id OUT NOCOPY NUMBER
161 , l_comp_type OUT NOCOPY VARCHAR2
162 , l_call_center OUT NOCOPY VARCHAR2
163 , l_campaign_id OUT NOCOPY VARCHAR2
164 , l_schedule_id OUT NOCOPY VARCHAR2
165 , l_source_code_id OUT NOCOPY VARCHAR2
166 , l_agent_group OUT NOCOPY VARCHAR2
167 , l_view_by OUT NOCOPY VARCHAR2) ;
168
169
170
171 FUNCTION GET_AO_DEFAULT_PAGE_PARAMS RETURN VARCHAR2;
172
173 END BIX_PMV_DBI_utl_pkg;