[Home] [Help]
PACKAGE BODY: APPS.FA_FLEX_UPG_PKG
Source
1 PACKAGE BODY FA_FLEX_UPG_PKG AS
2 /* $Header: faflxupb.pls 120.4 2005/07/25 10:03:18 yyoon ship $ */
3
4
5
6 PROCEDURE CALL_UPGRADED_FLEX(itemtype in varchar2,
7 itemkey in varchar2,
8 actid in number,
9 funcmode in varchar2,
10 result out nocopy varchar2,
11 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
12 IS
13 h_ret_ccid number;
14 h_ret_val boolean;
15 h_cat_id number;
16 h_comment varchar2(30);
17 h_debug_flag varchar2(3);
18 h_flex_id number :=0;
19 h_flex_data varchar2(30):=' ';
20 -- fix for 1287708
21 h_flex_seg varchar2(2000):=NULL;
22 h_error_msg varchar2(80):=NULL;
23 -- end fix for 1287708
24 h_error_msg2 varchar2(30):=' ';
25 c_package_name varchar2(30);
26 h_func varchar2(40);
27 h_stmt varchar2(300);
28 h_flex_set number := -1;
29 h_dinsert varchar2(1) := 'N';
30 h_partial_ok varchar2(1) := 'N';
31 h_flex_num number;
32 h_acct_ccid_c varchar2(10);
33 h_acct_ccid number;
34 h_def_ccid_c varchar2(10);
35 h_def_ccid number;
36 h_acct_seg varchar2(30);
37 h_dist_ccid_c varchar2(10);
38 h_dist_ccid number;
39 h_book_type_code varchar2(16);
40 h_book_type_code_out varchar2(16);
41 h_account_type varchar2(30);
42
43 begin
44 if (funcmode = 'RUN') THEN
45
46 h_book_type_code := wf_engine.GetItemAttrText(itemtype,itemkey,'BOOK_TYPE_CODE');
47 h_book_type_code_out := replace(h_book_type_code,' ','_');
48 h_book_type_code_out := replace(h_book_type_code_out,'(','_');
49 h_book_type_code_out := replace(h_book_type_code_out,')','_');
50 h_book_type_code_out := replace(h_book_type_code_out,'.','_');
51 h_book_type_code_out := replace(h_book_type_code_out,'-','_');
52 h_account_type := wf_engine.GetItemAttrText(itemtype,itemkey,'ACCOUNT_TYPE');
53 h_func := h_book_type_code_out || '_' || h_account_type || '.build';
54 h_flex_num := wf_engine.GetItemAttrNumber(itemtype,itemkey,'CHART_OF_ACCOUNTS_ID');
55
56 h_dist_ccid := wf_engine.GetItemAttrNumber(itemtype,itemkey,'DISTRIBUTION_CCID');
57 h_acct_ccid := wf_engine.GetItemAttrNumber(itemtype,itemkey,'ACCOUNT_CCID');
58
59 h_def_ccid := wf_engine.GetItemAttrNumber(itemtype,itemkey,'DEFAULT_CCID');
60
61 h_acct_seg := wf_engine.GetItemAttrText(itemtype,itemkey,'ACCT_SEG_VAL');
62
63 IF (h_account_type in ('ASSET_COST','ASSET_CLEARING',
64 'CIP_CLEARING','CIP_COST',
65 'DEPRN_RSV','REV_AMORT','REV_RSV')
66 )
67 THEN /* Category level Accounts */
68 h_ret_val := CATE_LEVEL_ACCT (X_flex_num=>h_flex_num,
69 X_func=>h_func,
70 X_acct_ccid=>to_char(h_acct_ccid),
71 X_acct_seg=>h_acct_seg,
72 X_def_ccid=>to_char(h_def_ccid),
73 X_dist_ccid=>to_char(h_dist_ccid),
74 X_flex_seg=>h_flex_seg,
75 X_error_msg=>h_error_msg);
76 ELSIF (h_account_type = 'DEPRN_EXP')
77 THEN /* Asset Level */
78 h_ret_val := ASSET_LEVEL_ACCT (X_flex_num=>h_flex_num,
79 X_func=>h_func,
80 X_acct_seg=>h_acct_seg,
81 X_dist_ccid=>to_char(h_dist_ccid),
82 X_flex_seg=>h_flex_seg,
83 X_error_msg=>h_error_msg);
84 ELSE /* Book Level */
85 h_ret_val := BOOK_LEVEL_ACCT (X_flex_num=>h_flex_num,
86 X_func=>h_func,
87 X_acct_seg=>h_acct_seg,
88 X_def_ccid=>to_char(h_def_ccid),
89 X_dist_ccid=>to_char(h_dist_ccid),
90 X_flex_seg=>h_flex_seg,
91 X_error_msg=>h_error_msg);
92 END IF;
93 if (h_ret_val)
94 then
95 FND_FLEX_WORKFLOW.LOAD_CONCATENATED_SEGMENTS('FAFLEXWF',
96 itemkey,
97 h_flex_seg);
98 result := 'COMPLETE:SUCCESS';
99 if (p_log_level_rec.statement_level)
100 then
101 FA_DEBUG_PKG.ADD(
102 fname => 'FA_FLEX_UPG_PKG.CALL_UPGRADED_FLEX',
103 element => 'After Concanetated segments ',
104 value => 1,
105 p_log_level_rec => p_log_level_rec);
106 end if;
107 RETURN;
108 else
109 wf_engine.SetItemAttrText(itemtype=>'FALFEXWF',
110 itemkey=>itemkey,
111 aname=>'ERROR_MESSAGE',
112 avalue=>h_error_msg);
113 result := 'COMPLETE:FAILURE';
114 RETURN;
115 end if;
116 ELSIF (funcmode = 'CANCEL') THEN
117 result := 'COMPLETE:';
118 RETURN;
119 ELSE
120 result := '';
121 RETURN;
122 END IF;
123
124 exception
125 when others then
126 if (p_log_level_rec.statement_level)
127 then
128 FA_DEBUG_PKG.ADD (
129 fname => 'FA_FLEX_UPG_PKG',
130 element=>'Errored',
131 value=>1,
132 p_log_level_rec => p_log_level_rec);
133 end if;
134 end; /* CALL_FLEX_FUNCTION */
135
136 FUNCTION BOOK_LEVEL_ACCT (X_flex_num in number,
137 X_func in varchar2,
138 X_acct_seg in varchar2,
139 X_def_ccid in varchar2,
140 X_dist_ccid in varchar2,
141 X_flex_seg in out nocopy varchar2,
142 X_error_msg in out nocopy varchar2,
143 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
144 RETURN BOOLEAN
145 IS
146 h_cursor_executed integer;
147 cursor_handle integer;
148 h_val number :=1;
149 h_flex_seg varchar2(2000):=NULL;
150 h_error_msg varchar2(80):=NULL;
151
152 BEGIN<<BOOK_LEVEL_ACCT>>
153
154 cursor_handle := DBMS_SQL.OPEN_CURSOR;
155 DBMS_SQL.PARSE(cursor_handle,'BEGIN IF ( ' || X_func ||
156 '(:flex_num,
157 :acct_seg,
158 :def_ccid,
159 :dist_ccid,
160 :flex_seg,
161 :error_msg)) THEN :x1:=1;ELSE :x1:=0;END IF; END;',DBMS_SQL.V7);
162 DBMS_SQL.BIND_VARIABLE(cursor_handle,':x1',h_val);
163 DBMS_SQL.BIND_VARIABLE(cursor_handle,':flex_num',X_flex_num);
164 DBMS_SQL.BIND_VARIABLE(cursor_handle,':acct_seg',X_acct_seg);
165 DBMS_SQL.BIND_VARIABLE(cursor_handle,':def_ccid',X_def_ccid);
166 DBMS_SQL.BIND_VARIABLE(cursor_handle,':dist_ccid',X_dist_ccid);
167 DBMS_SQL.BIND_VARIABLE(cursor_handle,':flex_seg',h_flex_seg,2000);
168 DBMS_SQL.BIND_VARIABLE(cursor_handle,':error_msg',h_error_msg,80);
169 h_cursor_executed := DBMS_SQL.EXECUTE (cursor_handle);
170
171 DBMS_SQL.VARIABLE_VALUE(cursor_handle,':x1',h_val);
172 DBMS_SQL.VARIABLE_VALUE(cursor_handle,':flex_seg',h_flex_seg);
173 DBMS_SQL.VARIABLE_VALUE(cursor_handle,':error_msg',h_error_msg);
174 X_flex_seg := h_flex_seg;
175 X_error_msg := h_error_msg;
176 DBMS_SQL.CLOSE_CURSOR(cursor_handle);
177 IF (h_val=1)
178 THEN
179 RETURN TRUE;
180 ELSE
181 RETURN FALSE;
182 END IF;
183 exception
184 when others then
185 DBMS_SQL.CLOSE_CURSOR(cursor_handle);
186 END ; /* BOOK LEVEL ACCT */
187
188 FUNCTION CATE_LEVEL_ACCT (X_flex_num in number,
189 X_func in varchar2,
190 X_acct_ccid in varchar2,
191 X_acct_seg in varchar2,
192 X_def_ccid in varchar2,
193 X_dist_ccid in varchar2,
194 X_flex_seg in out nocopy varchar2,
195 X_error_msg in out nocopy varchar2,
196 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
197 RETURN BOOLEAN
198 IS
199 h_cursor_executed integer;
200 cursor_handle integer;
201 h_val number:=1;
202 h_flex_seg varchar2(2000):=NULL;
203 h_error_msg varchar2(80):=NULL;
204
205 BEGIN<<CATE_LEVEL_ACCT>>
206
207 cursor_handle := DBMS_SQL.OPEN_CURSOR;
208 DBMS_SQL.PARSE(cursor_handle,'BEGIN IF (' || X_func ||
209 '(:flex_num,
210 :acct_ccid,
211 :acct_seg,
212 :def_ccid,
213 :dist_ccid,
214 :flex_seg,
215 :error_msg)) THEN :x1:=1;ELSE :x1:=0;END IF; END;',DBMS_SQL.V7);
216 DBMS_SQL.BIND_VARIABLE(cursor_handle,':x1',h_val);
217 DBMS_SQL.BIND_VARIABLE(cursor_handle,':flex_num',X_flex_num);
218 DBMS_SQL.BIND_VARIABLE(cursor_handle,':acct_ccid',X_acct_ccid);
219 DBMS_SQL.BIND_VARIABLE(cursor_handle,':acct_seg',X_acct_seg);
220 DBMS_SQL.BIND_VARIABLE(cursor_handle,':def_ccid',X_def_ccid);
221 DBMS_SQL.BIND_VARIABLE(cursor_handle,':dist_ccid',X_dist_ccid);
222 DBMS_SQL.BIND_VARIABLE(cursor_handle,':flex_seg',h_flex_seg,2000);
223 DBMS_SQL.BIND_VARIABLE(cursor_handle,':error_msg',h_error_msg,80);
224 h_cursor_executed := DBMS_SQL.EXECUTE (cursor_handle);
225
226 DBMS_SQL.VARIABLE_VALUE(cursor_handle,':x1',h_val);
227 DBMS_SQL.VARIABLE_VALUE(cursor_handle,':flex_seg',h_flex_seg);
228 DBMS_SQL.VARIABLE_VALUE(cursor_handle,':error_msg',h_error_msg);
229 X_flex_seg := h_flex_seg;
230 X_error_msg := h_error_msg;
231 DBMS_SQL.CLOSE_CURSOR(cursor_handle);
232 IF (h_val = 1)
233 THEN
234 RETURN TRUE;
235 ELSE
236 RETURN FALSE;
237 END IF;
238 exception
239 when others then
240 DBMS_SQL.CLOSE_CURSOR(cursor_handle);
241 END ; /* CATE LEVEL ACCT */
242
243 FUNCTION ASSET_LEVEL_ACCT (X_flex_num in number,
244 X_func in varchar2,
245 X_acct_seg in varchar2,
246 X_dist_ccid in varchar2,
247 X_flex_seg in out nocopy varchar2,
248 X_error_msg in out nocopy varchar2,
249 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
250 RETURN BOOLEAN
251 IS
252 h_cursor_executed integer;
253 cursor_handle integer;
254 h_val number:=1;
255 h_flex_seg varchar2(2000):=NULL;
256 h_error_msg varchar2(80):=NULL;
257
258 BEGIN<<ASSET_LEVEL_ACCT>>
259
260 cursor_handle := DBMS_SQL.OPEN_CURSOR;
261 DBMS_SQL.PARSE(cursor_handle,'BEGIN IF (' || X_func ||
262 '(:flex_num,
263 :acct_seg,
264 :dist_ccid,
265 :flex_seg,
266 :error_msg)) THEN :x1:=1;ELSE :x1:=0;END IF; END;',DBMS_SQL.V7);
267 DBMS_SQL.BIND_VARIABLE(cursor_handle,':x1',h_val);
268 DBMS_SQL.BIND_VARIABLE(cursor_handle,':flex_num',X_flex_num);
269 DBMS_SQL.BIND_VARIABLE(cursor_handle,':acct_seg',X_acct_seg);
270 DBMS_SQL.BIND_VARIABLE(cursor_handle,':dist_ccid',X_dist_ccid);
271 DBMS_SQL.BIND_VARIABLE(cursor_handle,':flex_seg',h_flex_seg,2000);
272 DBMS_SQL.BIND_VARIABLE(cursor_handle,':error_msg',h_error_msg,80);
273 h_cursor_executed := DBMS_SQL.EXECUTE (cursor_handle);
274
275 DBMS_SQL.VARIABLE_VALUE(cursor_handle,':x1',h_val);
276 DBMS_SQL.VARIABLE_VALUE(cursor_handle,':flex_seg',h_flex_seg);
277 DBMS_SQL.VARIABLE_VALUE(cursor_handle,':error_msg',h_error_msg);
278 X_flex_seg := h_flex_seg;
279 X_error_msg := h_error_msg;
280 DBMS_SQL.CLOSE_CURSOR(cursor_handle);
281 IF (h_val = 1)
282 THEN
283 RETURN TRUE;
284 ELSE
285 RETURN FALSE;
286 END IF;
287 exception
288 when others then
289 DBMS_SQL.CLOSE_CURSOR(cursor_handle);
290 END ; /* ASSET LEVEL ACCT */
291
292 END FA_FLEX_UPG_PKG;