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