[Home] [Help]
PACKAGE BODY: APPS.FII_AR_UTIL
Source
1 PACKAGE BODY FII_AR_UTIL AS
2 /* $Header: FIIARPMV1B.pls 115.1 2004/05/19 02:38:35 ilavenil noship $ */
3
4 -- -------------------------------------------------
5 -- Re-set the globals variables to NULL
6 -- -------------------------------------------------
7 PROCEDURE reset_globals IS
8 BEGIN
9 p_as_of_date := NULL;
10 p_period_type := NULL;
11 p_view_by := NULL;
12 p_sgid := NULL;
13 p_prod_cat := NULL;
14 p_cust := NULL;
15 p_curr := NULL;
16 p_record_type_id := NULL;
17
18
19
20 END reset_globals;
21
22 -- -------------------------------------------------
23 -- Parse thru the parameter talbe and set globals
24 -- -------------------------------------------------
25
26
27 PROCEDURE get_parameters (p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
28
29
30 ) IS
31
32 BEGIN
33 IF (p_page_parameter_tbl.count > 0) THEN
34 FOR i IN p_page_parameter_tbl.first..p_page_parameter_tbl.last LOOP
35 IF p_page_parameter_tbl(i).parameter_name = 'AS_OF_DATE' THEN
36 p_as_of_date := to_date(p_page_parameter_tbl(i).parameter_value, 'DD-MM-YYYY');
37 END IF;
38 IF p_page_parameter_tbl(i).parameter_name = 'PERIOD_TYPE' THEN
39 p_period_type := p_page_parameter_tbl(i).parameter_value;
40 END IF;
41 IF p_page_parameter_tbl(i).parameter_name = 'VIEW_BY' THEN
42 p_view_by := p_page_parameter_tbl(i).parameter_value;
43 END IF;
44 IF(p_page_parameter_tbl(i).parameter_name = 'ORGANIZATION+JTF_ORG_SALES_GROUP') THEN
45 p_sgid := p_page_parameter_tbl(i).parameter_id;
46 END IF;
47
48 IF(p_page_parameter_tbl(i).parameter_name = 'ITEM+ENI_ITEM_VBH_CAT') THEN
49 p_prod_cat := p_page_parameter_tbl(i).parameter_id;
50 END IF;
51
52 IF(p_page_parameter_tbl(i).parameter_name = 'CUSTOMER+FII_CUSTOMERS') THEN
53 p_cust := p_page_parameter_tbl(i).parameter_id;
54 END IF;
55
56 IF (p_page_parameter_tbl(i).parameter_name = 'CURRENCY+FII_CURRENCIES')
57 THEN p_curr := p_page_parameter_tbl(i).parameter_id;
58 END IF;
59
60 END LOOP;
61 END IF;
62 If p_period_type is not null then
63 CASE p_period_type
64 WHEN 'FII_TIME_WEEK' THEN p_record_type_id := 32;
65 WHEN 'FII_TIME_ENT_PERIOD' THEN p_record_type_id := 64;
66 WHEN 'FII_TIME_ENT_QTR' THEN p_record_type_id := 128;
67 WHEN 'FII_TIME_ENT_YEAR' THEN p_record_type_id := 256;
68 END CASE;
69 End if;
70
71
72
73 END get_parameters;
74
75 FUNCTION get_label(sequence IN VARCHAR2) RETURN VARCHAR2 IS
76
77 stmt VARCHAR2(240);
78 l_asof_date DATE := FII_AR_Util.p_as_of_date;
79 l_temp_date DATE := NULL;
80
81 BEGIN
82
83 IF FII_AR_Util.p_period_type = 'FII_TIME_ENT_YEAR' THEN
84
85 CASE sequence
86
87 WHEN '1' THEN
88 l_temp_date := fii_time_api.ent_pqtr_end(fii_time_api.ent_pqtr_end(fii_time_api.ent_pqtr_end(l_asof_date)));
89 WHEN '2' THEN
90 l_temp_date := fii_time_api.ent_pqtr_end(fii_time_api.ent_pqtr_end(l_asof_date));
91 WHEN '3' THEN
92 l_temp_date := fii_time_api.ent_pqtr_end(l_asof_date);
93 WHEN '4' THEN
94 stmt := FND_Message.get_string('FII', 'FII_QTD');
95 RETURN stmt;
96 WHEN '5' THEN
97 stmt := FND_Message.get_string('FII', 'FII_ROLL4_QTS_REV');
98 RETURN stmt;
99 ELSE
100 RETURN NULL;
101
102 END CASE;
103
104 SELECT name INTO stmt
105 FROM fii_time_ent_qtr
106 WHERE l_temp_date = end_date;
107
108
109
110 ELSIF FII_AR_Util.p_period_type = 'FII_TIME_ENT_QTR' THEN
111
112 CASE sequence
113
114 WHEN '1' THEN
115 l_temp_date := fii_time_api.ent_pqtr_end(fii_time_api.ent_pqtr_end(fii_time_api.ent_pqtr_end(l_asof_date)));
116 WHEN '2' THEN
117 l_temp_date := fii_time_api.ent_pper_end(fii_time_api.ent_pper_end(l_asof_date));
118 WHEN '3' THEN
119 l_temp_date := fii_time_api.ent_pper_end(l_asof_date);
120 WHEN '4' THEN
121 stmt := FND_Message.get_string('FII', 'FII_MTD');
122 RETURN stmt;
123 WHEN '5' THEN
124 stmt := FND_Message.get_string('FII', 'FII_ROLL3_MTH_REV');
125 RETURN stmt;
126 ELSE
127 RETURN NULL;
128
129 END CASE;
130
131 SELECT name INTO stmt
132 FROM fii_time_ent_period
133 WHERE l_temp_date = end_date;
134
135 ELSE
136 RETURN NULL;
137
138 END IF;
139
140 RETURN stmt;
141
142 END get_label;
143
144
145 /*public procedure. binding variables is done here.*/
146 PROCEDURE Bind_variable
147 (p_sqlstmt IN Varchar2,
148 p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
149 p_sql_output OUT NOCOPY Varchar2,
150 p_bind_output_table OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL,
151 p_record_type_id IN Number Default Null,
152 p_view_by IN Varchar2 Default Null,
153 p_fiibind1 IN Varchar2 Default null,
154 p_fiibind2 IN Varchar2 Default null
155 ) IS
156 l_bind_rec BIS_QUERY_ATTRIBUTES;
157
158 BEGIN
159 p_bind_output_table := BIS_QUERY_ATTRIBUTES_TBL();
160 l_bind_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
161 p_sql_output := p_sqlstmt;
162
163 p_bind_output_table.EXTEND;
164 l_bind_rec.attribute_name := ':FIIBIND1';
165 l_bind_rec.attribute_value := to_char(p_fiibind1);
166 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
167 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
168 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
169 p_bind_output_table.EXTEND;
170 l_bind_rec.attribute_name := ':FIIBIND2';
171 l_bind_rec.attribute_value := to_char(p_fiibind2);
172 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
173 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
174 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
175 p_bind_output_table.EXTEND;
176 l_bind_rec.attribute_name := ':VIEW_BY';
177 l_bind_rec.attribute_value := to_char(p_view_by);
178 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
179 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
180 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
181 p_bind_output_table.EXTEND;
182 l_bind_rec.attribute_name := ':RECORD_TYPE_ID';
183 l_bind_rec.attribute_value := to_char(p_record_type_id);
184 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
185 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
186 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
187 p_bind_output_table.EXTEND;
188
189
190 END;
191
192 END fii_AR_util;