1 PACKAGE BODY FII_BUDGET_INTERFACE_PKG AS
2 /* $Header: FIIBUINB.pls 120.10 2005/06/02 19:25:48 ilavenil noship $ */
3
4 -- Purpose
5 -- This is the routine called by Web ADI for uploading records
6 -- into FII_BUDGET_INTERFACE
7 -- History
8 -- 07-22-02 S Kung Created
9 -- Arguments
10 -- All columns in the FII_BUDGET_INTERFACE table
11 -- Example
12 -- FII_BUDGET_INTERFACE.Web_Adi_Upload;
13 -- Notes
14 --
15 FUNCTION Web_Adi_Upload
16 ( X_RowID IN VARCHAR2 DEFAULT NULL,
17 X_Plan_Type IN VARCHAR2,
18 X_Version_Date IN VARCHAR2 DEFAULT NULL,
19 X_Time_Period IN VARCHAR2 DEFAULT NULL,
20 X_Date IN DATE DEFAULT NULL,
21 X_Company IN VARCHAR2 DEFAULT NULL,
22 X_Cost_Center IN VARCHAR2 DEFAULT NULL,
23 X_CCC IN NUMBER DEFAULT NULL,
24 X_LOB IN VARCHAR2 DEFAULT NULL,
25 X_Acct IN VARCHAR2 DEFAULT NULL,
26 X_Fin_Item IN VARCHAR2 DEFAULT NULL,
27 X_Product IN NUMBER DEFAULT NULL,
28 X_User_Defined_Dim IN VARCHAR2 DEFAULT NULL,
29 X_Prim_Amt IN NUMBER,
30 X_Rate IN NUMBER DEFAULT NULL,
31 X_Sec_Amt IN NUMBER DEFAULT NULL,
32 X_Ledger IN NUMBER DEFAULT NULL
33 ) return VARCHAR2 IS
34
35 invalid_company EXCEPTION;
36 invalid_cost_center EXCEPTION;
37 invalid_account EXCEPTION;
38 invalid_user_defined EXCEPTION;
39 invalid_sys_profile EXCEPTION;
40 l_sec_amt NUMBER;
41 l_company NUMBER;
42 l_cost_center NUMBEr;
43 l_account NUMBER;
44 l_user_defined_dim NUMBER;
45 l_err_msg VARCHAR2(150);
46 BEGIN
47
48 --check for system profile FII_BUDGET_SOURCE = WEBADI
49 IF (fnd_profile.value('FII_BUDGET_SOURCE') = 'WEBADI') THEN
50 -- If RowID is null, THEN new record to be inserted, else update.
51 IF (X_RowID is NULL) THEN
52 --if rate is a non zero value, THEN we compute secondary currency amount and overwrite the user
53 --entered secondary currency amount.
54 IF nvl(X_Rate,0) <> 0 THEN
55 l_sec_amt := X_Prim_Amt * X_Rate;
56 ELSE
57 l_sec_amt := X_Sec_Amt;
58 END IF;
59
60 BEGIN
61 SELECT c.flex_value_id
62 INTO l_company
63 FROM FND_ID_FLEX_SEGMENTS a, FND_SEGMENT_ATTRIBUTE_VALUES b, fnd_flex_values c,
64 fnd_flex_values_tl d
65 WHERE a.ID_FLEX_CODE = 'GL#'
66 AND a.APPLICATION_ID = 101
67 AND a.ID_FLEX_NUM in (select chart_of_accounts_id from
68 gl_ledgers_public_v where
69 ledger_id = X_Ledger)
70 AND a.application_id = b.application_id
71 AND a.id_flex_code = b.id_flex_code
72 AND a.id_flex_num = b.id_flex_num
73 AND a.application_column_name = b.application_column_name
74 AND b.attribute_value = 'Y'
75 AND b.segment_attribute_type = ('GL_BALANCING')
76 AND a.flex_value_set_id = c.flex_value_set_id
77 AND c.flex_value_id = d.flex_value_id
78 AND d.language = userenv('LANG')
79 AND c.summary_flag = 'N'
80 AND c.flex_value = X_Company;
81 EXCEPTION
82 WHEN others THEN
83 RAISE invalid_company;
84 END;
85
86 BEGIN
87 SELECT c.flex_value_id
88 INTO l_cost_center
89 FROM FND_ID_FLEX_SEGMENTS a, FND_SEGMENT_ATTRIBUTE_VALUES b, fnd_flex_values c,
90 fnd_flex_values_tl d
91 WHERE a.ID_FLEX_CODE = 'GL#'
92 AND a.APPLICATION_ID = 101
93 AND a.ID_FLEX_NUM in (select chart_of_accounts_id from
94 gl_ledgers_public_v where
95 ledger_id = X_Ledger)
96 AND a.application_id = b.application_id
97 AND a.id_flex_code = b.id_flex_code
98 AND a.id_flex_num = b.id_flex_num
99 AND a.application_column_name = b.application_column_name
100 AND b.attribute_value = 'Y'
101 AND b.segment_attribute_type = ('FA_COST_CTR')
102 AND a.flex_value_set_id = c.flex_value_set_id
103 AND c.flex_value_id = d.flex_value_id
104 AND d.language = userenv('LANG')
105 AND c.summary_flag = 'N'
106 AND c.flex_value = X_Cost_Center;
107 EXCEPTION
108 WHEN others THEN
109 RAISE invalid_cost_center;
110 END;
111
112 BEGIN
113 SELECT c.flex_value_id
114 INTO l_account
115 FROM FND_ID_FLEX_SEGMENTS a, FND_SEGMENT_ATTRIBUTE_VALUES b, fnd_flex_values c,
116 fnd_flex_values_tl d
117 WHERE a.ID_FLEX_CODE = 'GL#'
118 AND a.APPLICATION_ID = 101
119 AND a.ID_FLEX_NUM in (select chart_of_accounts_id from
120 gl_ledgers_public_v where
121 ledger_id = X_Ledger)
122 AND a.application_id = b.application_id
123 AND a.id_flex_code = b.id_flex_code
124 AND a.id_flex_num = b.id_flex_num
125 AND a.application_column_name = b.application_column_name
126 AND b.attribute_value = 'Y'
127 AND b.segment_attribute_type = ('GL_ACCOUNT')
128 AND a.flex_value_set_id = c.flex_value_set_id
129 AND c.flex_value_id = d.flex_value_id
130 AND d.language = userenv('LANG')
131 AND c.flex_value = X_Fin_Item;
132 EXCEPTION
133 WHEN others THEN
134 RAISE invalid_account;
135 END;
136
137 IF X_User_Defined_Dim is not null THEN
138 BEGIN
139 SELECT f.flex_value_id
140 INTO l_user_defined_dim
141 FROM fnd_flex_values f, fnd_flex_values_tl t, fii_financial_dimensions ffd
142 WHERE flex_value_set_id in
143 (SELECT MASTER_VALUE_SET_ID id FROM fii_financial_dimensions
144 WHERE dimension_short_name = 'FII_USER_DEFINED_1'
145 UNION
146 SELECT map.flex_value_set_id1 id
147 FROM fii_dim_mapping_rules map, fii_slg_assignments sts, fii_source_ledger_groups slg,
148 gl_ledgers_public_v sob
149 WHERE map.dimension_short_name = 'FII_USER_DEFINED_1'
150 AND map.chart_of_accounts_id = sts.chart_of_accounts_id
151 AND sts.source_ledger_group_id = slg.source_ledger_group_id
152 AND slg.usage_code = 'DBI'
153 AND sts.ledger_id = sob.ledger_id
154 AND sob.ledger_id = X_Ledger)
155 AND f.flex_value_id = t.flex_value_id
156 AND t.language = userenv('LANG')
157 AND ffd.dimension_short_name = 'FII_USER_DEFINED_1'
158 AND ffd.dbi_enabled_flag = 'Y'
159 AND f.flex_value = X_User_Defined_Dim;
160 EXCEPTION
161 WHEN others THEN
162 RAISE invalid_user_defined;
163 END;
164 END IF;
165
166 INSERT INTO FII_BUDGET_INTERFACE
167 (plan_type_code, prim_amount_g, report_time_period, report_date, version_date, sec_amount_g, conversion_rate,
168 fin_item, fin_category_id, prod_category_id, company, company_id,
169 cost_center, cost_center_id, user_dim1, user_dim1_id, ledger_id)
170 VALUES
171 (X_Plan_Type, X_Prim_Amt, X_Time_Period, X_Date, to_date(X_Version_Date, 'DD-MM-YYYY'), l_sec_amt, X_Rate,
172 X_Fin_Item, l_account, X_Product, X_Company, l_Company,
173 X_Cost_Center, l_Cost_Center, X_User_Defined_Dim, l_User_Defined_Dim, X_Ledger);
174 ELSE
175 UPDATE FII_BUDGET_INTERFACE
176 SET
177 plan_type_code = X_Plan_Type,
178 prim_amount_g = X_Prim_Amt,
179 report_time_period = X_Time_Period,
180 report_date = X_Date,
181 fin_item = X_Fin_Item,
182 fin_category_id = l_account,
183 prod_category_id = X_Product,
184 conversion_rate = X_Rate,
185 sec_amount_g = l_sec_amt,
186 company = X_Company,
187 company_id = l_Company,
188 cost_center = X_Cost_Center,
189 cost_center_id = l_Cost_Center,
190 version_date = to_date(X_Version_Date, 'DD-MM-YYYY'),
191 User_Dim1 = X_User_Defined_Dim,
192 user_dim1_id = l_User_Defined_Dim,
193 ledger_id = X_Ledger,
194 status_code = null
195 WHERE
196 rowid = chartorowid(X_RowID);
197
198 --once user fixes the errored record and re-uploads, THEN it is essential that
199 --all other residing VALIDATED records are to be set as status_code = null.
200 --thus upload into base table program will process all of the records existing in
201 --interface table and not just the error-fixed record.
202 UPDATE FII_BUDGET_INTERFACE
203 SET status_code = NULL;
204
205 END IF;
206 ELSE
207 RAISE invalid_sys_profile;
208 END IF;
209 return null;
210
211 EXCEPTION
212 WHEN invalid_company THEN
213 l_err_msg := fnd_message.get_string('FII','FII_EA_INVALID_COMPANY');
214 return l_err_msg;
215
216 WHEN invalid_cost_center THEN
217 l_err_msg := fnd_message.get_string('FII','FII_EA_INVALID_COST_CENTER');
218 return l_err_msg;
219
220 WHEN invalid_account THEN
221 l_err_msg := fnd_message.get_string('FII','FII_EA_INVALID_ACCOUNT');
222 return l_err_msg;
223
224 WHEN invalid_user_defined THEN
225 l_err_msg := fnd_message.get_string('FII','FII_EA_INVALID_USR_DEFINED_DIM');
226 return l_err_msg;
227
228 WHEN invalid_sys_profile THEN
229 l_err_msg := fnd_message.get_string('FII','FII_EA_INVALID_SYS_PROFILE');
230 return l_err_msg;
231
232 WHEN OTHERS THEN
233 l_err_msg := fnd_message.get_string('FII', 'FII_EA_VALIDATOR_ERR');
234 return l_err_msg;
235 END Web_Adi_Upload;
236
237 END FII_BUDGET_INTERFACE_PKG;
238
[Home] [Help]