[Home] [Help]
PACKAGE BODY: APPS.PSA_AR_GL_POST_PKG
Source
1 PACKAGE BODY psa_ar_gl_post_pkg AS
2 /* $Header: PSAMFG1B.pls 120.2 2006/09/13 12:46:50 agovil noship $ */
3
4 --===========================FND_LOG.START=====================================
5 g_state_level NUMBER := FND_LOG.LEVEL_STATEMENT;
6 g_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
7 g_event_level NUMBER := FND_LOG.LEVEL_EVENT;
8 g_excep_level NUMBER := FND_LOG.LEVEL_EXCEPTION;
9 g_error_level NUMBER := FND_LOG.LEVEL_ERROR;
10 g_unexp_level NUMBER := FND_LOG.LEVEL_UNEXPECTED;
11 g_path VARCHAR2(50);
12 --===========================FND_LOG.END=======================================
13
14 PROCEDURE transfer_to_gl(
15 p_start_date IN DATE,
16 p_post_thru_date IN DATE,
17 p_parent_req_id IN NUMBER,
18 p_posting_control_id IN NUMBER,
19 p_summary_flag IN VARCHAR2,
20 p_status_code OUT NOCOPY VARCHAR2
21 ) IS
22
23 -- Bug 3871686, replaced APPS with a subquery
24 -- to find the exact schema name
25 CURSOR c_fv_pkg_chk
26 IS
27 SELECT DISTINCT 'Y' fv_package
28 FROM ALL_OBJECTS
29 WHERE object_name = 'FV_AR_PKG'
30 AND object_type = 'PACKAGE'
31 AND owner = ( SELECT oracle_username
32 FROM fnd_oracle_userid
33 WHERE read_only_flag = 'U')
34 AND status = 'VALID';
35
36 l_req_id NUMBER;
37 l_org_id psa_implementation_all.org_id%TYPE;
38 l_psa_feature psa_implementation_all.psa_feature%TYPE;
39 l_enabled_flag psa_implementation_all.status%TYPE;
40 l_set_of_books_id gl_sets_of_books.set_of_books_id%TYPE;
41 l_start_date VARCHAR2(20);
42 l_post_thru_date VARCHAR2(20);
43 l_message VARCHAR2(2000);
44
45 -- ## FV
46 fv_ar_stmt VARCHAR2(4000);
47 fv_package VARCHAR2(1);
48 l_fv_pkg_chk c_fv_pkg_chk%rowtype;
49 l_fv_profile_defined BOOLEAN;
50 l_post_det_acct_flag VARCHAR2(1);
51 l_status NUMBER;
52
53 l_user_id fnd_user.user_id%type;
54 l_resp_appl_id fnd_application.application_id%TYPE;
55 l_user_resp_id fnd_responsibility.responsibility_id%TYPE;
56
57 -- ## TC
58 l_ussgl_option VARCHAR2(3);
59
60 l_errbuf VARCHAR2(2000);
61 l_retcode VARCHAR2(1);
62
63 PRODUCT_NOT_INSTALLED EXCEPTION;
64 FV_AR_EXCEPTION EXCEPTION;
65 PSA_MFAR_EXCEPTION EXCEPTION;
66 PSA_RESET_TC_EXCEPTION EXCEPTION;
67
68 -- ========================= FND LOG ===========================
69 l_full_path VARCHAR2(100) ;
70 -- ========================= FND LOG ===========================
71
72 BEGIN
73
74 -- ## Default assignments done here due to GSCC standards.
75 g_path := 'PSA.PLSQL.PSAMFG1B.psa_ar_gl_post_pkg.';
76 l_full_path := g_path || 'Transfer_to_gl';
77 l_post_det_acct_flag := 'Y';
78
79
80 -- ## MFAR Process
81 BEGIN
82
83 -- ========================= FND LOG ===========================
84 psa_utils.debug_other_string(g_state_level,l_full_path,' ');
85 psa_utils.debug_other_string(g_state_level,l_full_path,' Starting PSA Code hook PSAMFG1B for MFAR');
86 psa_utils.debug_other_string(g_state_level,l_full_path,' ');
87 psa_utils.debug_other_string(g_state_level,l_full_path,' PARAMETERS :');
88 psa_utils.debug_other_string(g_state_level,l_full_path,' ============');
89 psa_utils.debug_other_string(g_state_level,l_full_path,' p_start_date -->' || p_start_date );
90 psa_utils.debug_other_string(g_state_level,l_full_path,' p_post_thru_date -->' || p_post_thru_date);
91 psa_utils.debug_other_string(g_state_level,l_full_path,' p_parent_req_id -->' || p_parent_req_id);
92 psa_utils.debug_other_string(g_state_level,l_full_path,' p_posting_control_id -->' || p_posting_control_id);
93 psa_utils.debug_other_string(g_state_level,l_full_path,' p_summary_flag -->' || p_summary_flag);
94 -- ========================= FND LOG ===========================
95
96 p_status_code := 'F';
97 l_set_of_books_id := psa_mfar_utils.get_ar_sob_id;
98 l_psa_feature := 'MFAR';
99 l_start_date := TO_CHAR (p_start_date, 'YYYY/MM/DD');
100 l_post_thru_date := TO_CHAR (p_post_thru_date,'YYYY/MM/DD');
101 fnd_profile.get ('ORG_ID', l_org_id);
102
103 -- ========================= FND LOG ===========================
104 psa_utils.debug_other_string(g_state_level,l_full_path,' ');
105 psa_utils.debug_other_string(g_state_level,l_full_path,' OTHER DEFAULTED VARIABLES ');
106 psa_utils.debug_other_string(g_state_level,l_full_path,' ========================= ');
107 psa_utils.debug_other_string(g_state_level,l_full_path,' l_org_id --> ' || l_org_id );
108 psa_utils.debug_other_string(g_state_level,l_full_path,' l_set_of_books_id --> ' || l_set_of_books_id);
109 psa_utils.debug_other_string(g_state_level,l_full_path,' l_start_date --> ' || l_start_date);
110 psa_utils.debug_other_string(g_state_level,l_full_path,' l_post_thru_date --> ' || l_post_thru_date);
111 psa_utils.debug_other_string(g_state_level,l_full_path,' ');
112 psa_utils.debug_other_string(g_state_level,l_full_path,' PROCESS : ');
113 psa_utils.debug_other_string(g_state_level,l_full_path,' ========= ');
114 psa_utils.debug_other_string(g_state_level,l_full_path,' ');
115 psa_utils.debug_other_string(g_state_level,l_full_path,' TRANSFER_TO_GL ##> Checking MFAR is installed ');
116 -- ========================= FND LOG ===========================
117
118
119 -- Ensure that the FV_AR_PKG package is installed and valid
120 OPEN c_fv_pkg_chk;
121 FETCH c_fv_pkg_chk INTO l_fv_pkg_chk;
122 CLOSE c_fv_pkg_chk;
123
124 -- ========================= FND LOG ===========================
125 psa_utils.debug_other_string(g_state_level,l_full_path,
126 ' l_fv_pkg_chk.fv_package --> ' || l_fv_pkg_chk.fv_package);
127 -- ========================= FND LOG ===========================
128
129 IF l_fv_pkg_chk.fv_package = 'Y' THEN
130
131 --
132 -- Fetch profile option value for FV: Post Detailed Receipt Accounting
133 --
134 l_user_id := FND_GLOBAL.user_id;
135 l_resp_appl_id := FND_GLOBAL.resp_appl_id;
136 l_user_resp_id := FND_GLOBAL.RESP_ID;
137
138 FND_PROFILE.GET_SPECIFIC('FV_POST_DETAIL_REC_ACCOUNTING',
139 l_user_id,
140 l_user_resp_id,
141 l_resp_appl_id,
142 l_post_det_acct_flag,
143 l_fv_profile_defined);
144
145 -- ========================= FND LOG ===========================
146 psa_utils.debug_other_string(g_state_level,l_full_path,
147 ' l_post_det_acct_flag --> ' || l_post_det_acct_flag);
148 -- ========================= FND LOG ===========================
149
150 IF l_post_det_acct_flag = 'N' THEN
151 -- ========================= FND LOG ===========================
152 psa_utils.debug_other_string(g_state_level,l_full_path,' ');
153 psa_utils.debug_other_string(g_state_level,l_full_path,' TRANSFER_TO_GL ##> Calling FV_AR_PKG ');
154 -- ========================= FND LOG ===========================
155
156 fv_ar_stmt :=
157 'BEGIN FV_AR_PKG.delete_offsetting_unapp(:p_posting_control_id, :p_set_of_books_id, :p_status); END;';
158
159 EXECUTE IMMEDIATE fv_ar_stmt
160 USING IN p_posting_control_id, IN l_set_of_books_id, OUT l_status;
161
162 -- 0 (zero)/Null is success
163 -- 1 is failure
164
165 IF l_status = 1 THEN
166 RAISE fv_ar_exception;
167 END IF;
168
169 -- ========================= FND LOG ===========================
170 psa_utils.debug_other_string(g_state_level,l_full_path,'TRANSFER_TO_GL##> END of FV Processing ');
171 psa_utils.debug_other_string(g_state_level,l_full_path,' ');
172 -- ========================= FND LOG ===========================
173
174 END IF;
175 END IF;
176
177 -- ## checking whether PSA is installed
178 IF (psa_implementation.get (P_ORG_ID => l_org_id,
179 P_PSA_FEATURE => l_psa_feature,
180 P_ENABLED_FLAG => l_enabled_flag)) THEN
181
182 /*
183 ## If the IF LOOP is true that means there is a record for this org id
184 ## in psa_implementation_v but it does not mean the product is enabled.
185 ## The p_enabled_flag will show whether the product is installed or not
186 */
187
188 IF NVL(l_enabled_flag,'N') = 'N' THEN
189 RAISE product_not_installed;
190 END IF;
191
192 ELSE
193 RAISE product_not_installed;
194 END IF;
195
196 -- ========================= FND LOG ===========================
197 psa_utils.debug_other_string(g_state_level,l_full_path,' TRANSFER_TO_GL ##> MFAR available');
198 psa_utils.debug_other_string(g_state_level, l_full_path,
199 ' TRANSFER_TO_GL ##> Calling MFAR Transfer to GL --> PSA_xfr_to_gl_pkg.Transfer_to_gl');
200 psa_utils.debug_other_string(g_state_level,l_full_path,' ');
201 -- ========================= FND LOG ===========================
202
203 PSA_xfr_to_gl_pkg.Transfer_to_gl( errbuf => l_errbuf,
204 retcode => l_retcode,
205 p_set_of_books_id => l_set_of_books_id,
206 p_gl_date_from => l_start_date,
207 p_gl_date_to => l_post_thru_date,
208 p_gl_posted_date => trunc(sysdate),
209 p_parent_req_id => p_parent_req_id,
210 p_summary_flag => p_summary_flag,
211 p_pst_ctrl_id => p_posting_control_id);
212
213 -- ========================= FND LOG ===========================
214 psa_utils.debug_other_string(g_state_level,l_full_path,' l_retcode --> ' || l_retcode);
215 -- ========================= FND LOG ===========================
216
217 IF l_retcode = 'F' THEN
218 RAISE PSA_MFAR_EXCEPTION;
219 END IF;
220
221 p_status_code := 'S';
222
223 EXCEPTION
224 WHEN product_not_installed THEN
225 p_status_code := 'S';
226 l_message := 'EXCEPTION - PRODUCT_NOT_INSTALLED PACKAGE - PSA_AR_GL_POST_PKG.TRANSFER_TO_GL - ';
227 -- ========================= FND LOG ===========================
228 psa_utils.debug_other_string(g_excep_level,l_full_path,' ');
229 psa_utils.debug_other_string(g_excep_level,l_full_path,l_message);
230 psa_utils.debug_other_string(g_excep_level,l_full_path,' ');
231 -- ========================= FND LOG ===========================
232
233 WHEN fv_ar_exception THEN
234
235 p_status_code := 'F';
236 fnd_message.set_name ('AR', 'GENERIC_MESSAGE');
237 fnd_message.set_token ('GENERIC_TEXT', 'EXCEPTION - FV_AR_EXCEPTION PACKAGE - PSA_AR_GL_POST_PKG.TRANSFER_TO_GL - '|| sqlerrm);
238 -- ========================= FND LOG ===========================
239 psa_utils.debug_other_msg(g_excep_level,l_full_path,FALSE);
240 -- ========================= FND LOG ===========================
241 l_message := fnd_message.get;
242 -- ========================= FND LOG ===========================
243 psa_utils.debug_other_string(g_excep_level,l_full_path,' ');
244 psa_utils.debug_other_string(g_excep_level,l_full_path,l_message);
245 psa_utils.debug_other_string(g_excep_level,l_full_path,' ');
246 -- ========================= FND LOG ===========================
247
248 WHEN PSA_MFAR_EXCEPTION THEN
249 p_status_code := 'F';
250 l_message := 'EXCEPTION - PSA_MFAR_EXCEPTION PACKAGE - PSA_AR_GL_POST_PKG.TRANSFER_TO_GL - ';
251
252 -- ========================= FND LOG ===========================
253 psa_utils.debug_other_string(g_excep_level,l_full_path,' ');
254 psa_utils.debug_other_string(g_excep_level,l_full_path,l_message);
255 psa_utils.debug_other_string(g_excep_level,l_full_path,' ');
256 -- ========================= FND LOG ===========================
257
258 WHEN OTHERS THEN
259
260 p_status_code := 'F';
261 fnd_message.set_name ('AR', 'GENERIC_MESSAGE');
262 fnd_message.set_token ('GENERIC_TEXT', 'EXCEPTION - OTHERS PACKAGE - PSA_AR_GL_POST_PKG.TRANSFER_TO_GL - '|| sqlerrm);
263 -- ========================= FND LOG ===========================
264 psa_utils.debug_other_msg(g_unexp_level,l_full_path,FALSE);
265 -- ========================= FND LOG ===========================
266 l_message := fnd_message.get;
267 -- ========================= FND LOG ===========================
268 psa_utils.debug_other_string(g_excep_level,l_full_path,' ');
269 psa_utils.debug_other_string(g_excep_level,l_full_path,l_message);
270 psa_utils.debug_other_string(g_excep_level,l_full_path,' ');
271 -- ========================= FND LOG ===========================
272
273 END; -- ## End MFAR process.
274
275 /* ###################### RESET TRANSACTION CODES ########################## */
276
277 BEGIN -- ## Reset Transaction Codes.
278
279 /*
280 ## IF MFAR process gets completed successfully then
281 ## proceed with reset TC, because based on the
282 ## return status, ARGLTP will commit or roll
283 */
284
285 IF p_status_code = 'S' THEN
286
287 p_status_code := 'F';
288 fnd_profile.get ('USSGL_OPTION', l_ussgl_option);
289
290 -- ========================= FND LOG ===========================
291 psa_utils.debug_other_string(g_state_level,l_full_path,' ');
292 psa_utils.debug_other_string(g_state_level,l_full_path,
293 ' TRANSFER_TO_GL ##> START RESET TRANSACTION CODES ');
294 psa_utils.debug_other_string(g_state_level,l_full_path,' ');
295 psa_utils.debug_other_string(g_state_level,l_full_path,' l_ussgl_option --> ' || l_ussgl_option);
296 -- ========================= FND LOG ===========================
297
298 IF l_ussgl_option = 'Y' THEN
299
300 -- ========================= FND LOG ===========================
301 psa_utils.debug_other_string(g_state_level,l_full_path,
302 ' TRANSFER_TO_GL ##> Calling PSA_AR_GL_INTERFACE.reset_transaction_codes ');
303 -- ========================= FND LOG ===========================
304
305 PSA_AR_GL_INTERFACE.reset_transaction_codes (err_buf => l_errbuf,
306 ret_code => l_retcode,
307 p_pstctrl_id => p_posting_control_id);
308
309 -- ========================= FND LOG ===========================
310 psa_utils.debug_other_string(g_state_level,l_full_path,' l_retcode --> ' || l_retcode);
311 -- ========================= FND LOG ===========================
312
313 IF l_retcode = 'F' THEN
314 RAISE PSA_RESET_TC_EXCEPTION;
315 END IF;
316
317 END IF;
318 p_status_code := 'S';
319
320 END IF;
321
322 EXCEPTION
323 WHEN PSA_RESET_TC_EXCEPTION THEN
324
325 p_status_code := 'F';
326 l_message := 'EXCEPTION - PSA_RESET_TC_EXCEPTION PACKAGE - PSA_AR_GL_POST_PKG.TRANSFER_TO_GL - ' || sqlerrm;
327
328 -- ========================= FND LOG ===========================
329 psa_utils.debug_other_string(g_excep_level,l_full_path,' ');
330 psa_utils.debug_other_string(g_excep_level,l_full_path,l_message);
331 psa_utils.debug_other_string(g_excep_level,l_full_path,' ');
332 -- ========================= FND LOG ===========================
333
334 WHEN OTHERS THEN
335
336 p_status_code := 'F';
337 fnd_message.set_name ('AR', 'GENERIC_MESSAGE');
338 fnd_message.set_token ('GENERIC_TEXT', 'EXCEPTION - OTHERS PACKAGE - PSA_AR_GL_POST_PKG.TRANSFER_TO_GL - '|| sqlerrm);
339 -- ========================= FND LOG ===========================
340 psa_utils.debug_other_msg(g_unexp_level,l_full_path,FALSE);
341 -- ========================= FND LOG ===========================
342 l_message := fnd_message.get;
343 -- ========================= FND LOG ===========================
344 psa_utils.debug_other_string(g_excep_level,l_full_path,' ');
345 psa_utils.debug_other_string(g_excep_level,l_full_path,l_message);
346 psa_utils.debug_other_string(g_excep_level,l_full_path,' ');
347 -- ========================= FND LOG ===========================
348 END; -- ## End Reset Transaction Codes.
349
350 END transfer_to_gl;
351 END psa_ar_gl_post_pkg;