[Home] [Help]
PACKAGE BODY: APPS.PSA_MFAR_UTILS
Source
1 PACKAGE BODY PSA_MFAR_UTILS AS
2 /* $Header: PSAMFUTB.pls 120.12 2006/09/13 14:06:36 agovil ship $ */
3
4
5 --
6 -- Private procedures/functions/variables
7 --
8
9 --===========================FND_LOG.START=====================================
10 g_state_level NUMBER := FND_LOG.LEVEL_STATEMENT;
11 g_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
12 g_event_level NUMBER := FND_LOG.LEVEL_EVENT;
13 g_excep_level NUMBER := FND_LOG.LEVEL_EXCEPTION;
14 g_error_level NUMBER := FND_LOG.LEVEL_ERROR;
15 g_unexp_level NUMBER := FND_LOG.LEVEL_UNEXPECTED;
16 g_path VARCHAR2(50) := 'PSA.PLSQL.PSAMFUTB.PSA_MFAR_UTILS.';
17 --===========================FND_LOG.END=======================================
18
19 FUNCTION GET_MAPPED_ACCOUNT (p_transaction_type IN VARCHAR2,
20 p_natural_account IN VARCHAR2,
21 p_set_of_books_id IN NUMBER,
22 p_chart_of_accounts_id IN NUMBER )
23 RETURN VARCHAR2;
24
25 l_org_id NUMBER;
26
27
28 --
29 -- ## This procedure will called when ccid details have to be
30 -- ## inserted into a PL/SQL Table.
31 --
32
33 PROCEDURE insert_ccid (p_ccid IN NUMBER,
34 p_segment_info IN FND_FLEX_EXT.SEGMENTARRAY,
35 p_num_segments IN NUMBER)
36 IS
37 l_count number := 0;
38 l_segment_info FND_FLEX_EXT.SEGMENTARRAY;
39
40 -- ========================= FND LOG ===========================
41 l_full_path VARCHAR2(1000) := g_path || 'insert_ccid.';
42 -- ========================= FND LOG ===========================
43
44 BEGIN
45 l_count := nvl(ccid_info.count,0) + 1;
46 l_segment_info := p_segment_info;
47
48 -- ========================= FND LOG ===========================
49 psa_utils.debug_other_string(g_state_level,l_full_path,' Inside insert_ccid ');
50 psa_utils.debug_other_string(g_state_level,l_full_path,' PARAMETERS: ');
51 psa_utils.debug_other_string(g_state_level,l_full_path,' =========== ');
52 psa_utils.debug_other_string(g_state_level,l_full_path,' p_ccid --> ' || p_ccid);
53 psa_utils.debug_other_string(g_state_level,l_full_path,' p_num_segments --> ' || p_num_segments);
54 FOR i IN (p_num_segments+1)..30
55 LOOP
56 l_segment_info(i) := NULL;
57 END LOOP;
58
59 ccid_info(l_count).segment1 := l_segment_info(1);
60 ccid_info(l_count).segment2 := l_segment_info(2);
61 ccid_info(l_count).segment3 := l_segment_info(3);
62 ccid_info(l_count).segment4 := l_segment_info(4);
63 ccid_info(l_count).segment5 := l_segment_info(5);
64 ccid_info(l_count).segment6 := l_segment_info(6);
65 ccid_info(l_count).segment7 := l_segment_info(7);
66 ccid_info(l_count).segment8 := l_segment_info(8);
67 ccid_info(l_count).segment9 := l_segment_info(9);
68 ccid_info(l_count).segment10 := l_segment_info(10);
69 ccid_info(l_count).segment11 := l_segment_info(11);
70 ccid_info(l_count).segment12 := l_segment_info(12);
71 ccid_info(l_count).segment13 := l_segment_info(13);
72 ccid_info(l_count).segment14 := l_segment_info(14);
73 ccid_info(l_count).segment15 := l_segment_info(15);
74 ccid_info(l_count).segment16 := l_segment_info(16);
75 ccid_info(l_count).segment17 := l_segment_info(17);
76 ccid_info(l_count).segment18 := l_segment_info(18);
77 ccid_info(l_count).segment19 := l_segment_info(19);
81 ccid_info(l_count).segment23 := l_segment_info(23);
78 ccid_info(l_count).segment20 := l_segment_info(20);
79 ccid_info(l_count).segment21 := l_segment_info(21);
80 ccid_info(l_count).segment22 := l_segment_info(22);
82 ccid_info(l_count).segment24 := l_segment_info(24);
83 ccid_info(l_count).segment25 := l_segment_info(25);
84 ccid_info(l_count).segment26 := l_segment_info(26);
85 ccid_info(l_count).segment27 := l_segment_info(27);
86 ccid_info(l_count).segment28 := l_segment_info(28);
87 ccid_info(l_count).segment29 := l_segment_info(29);
88 ccid_info(l_count).segment30 := l_segment_info(30);
89 FOR i IN 1..30 LOOP
90 psa_utils.debug_other_string(g_state_level,l_full_path,' segment'||i||' --> ' ||l_segment_info(i) );
91 END LOOP;
92
93 psa_utils.debug_other_string(g_state_level,l_full_path,' p_num_segments --> ' || p_num_segments );
94 -- ========================= FND LOG ===========================
95
96 -- l_count := nvl(ccid_info.count,0) + 1;
97
98 -- ========================= FND LOG ===========================
99 psa_utils.debug_other_string(g_state_level,l_full_path,' l_count -> ' || l_count);
100 -- ========================= FND LOG ===========================
101
102 ccid_info(l_count).ccid := p_ccid;
103 ccid_info(l_count).number_of_segments := p_num_segments;
104
105 -- ========================= FND LOG ===========================
106 psa_utils.debug_other_string(g_state_level,l_full_path,' END - insert ccid ');
107 -- ========================= FND LOG ===========================
108
109 EXCEPTION
110 WHEN OTHERS THEN
111 -- ========================= FND LOG ===========================
112 psa_utils.debug_other_string(g_excep_level,l_full_path,'EXCEPTION - OTHERS : ERROR IN PSA_MFAR_UTILS.insert_ccid');
113 psa_utils.debug_other_string(g_excep_level,l_full_path, sqlcode || sqlerrm);
114 psa_utils.debug_unexpected_msg(l_full_path);
115 -- ========================= FND LOG ===========================
116 APP_EXCEPTION.RAISE_EXCEPTION;
117
118 End insert_ccid;
119
120 /* ================================ IS_CCID_EXISTS ============================= */
121
122 --
123 -- ## This function will check whether the ccid exists in the PL/SQL table
124 -- ## if it exists then this will return TRUE, Otherwise FALSE
125 -- ## It also return ccid and the segment details if it exist in the table
126 --
127
128 Function is_ccid_exists(x_ccid IN OUT NOCOPY NUMBER,
129 x_segment_info IN OUT NOCOPY FND_FLEX_EXT.SEGMENTARRAY,
130 x_number_of_segments OUT NOCOPY NUMBER) return BOOLEAN
131 IS
132
133 l_count NUMBER;
134 l_no_match NUMBER;
135 l_segment_info FND_FLEX_EXT.SEGMENTARRAY;
136
137 -- ========================= FND LOG ===========================
138 l_full_path VARCHAR2(100) := g_path || 'is_ccid_exists.';
139 -- ========================= FND LOG ===========================
140
141 BEGIN
142
143 -- ========================= FND LOG ===========================
144 psa_utils.debug_other_string(g_state_level,l_full_path,' Inside is_ccid_exists ');
145 psa_utils.debug_other_string(g_state_level,l_full_path,' PARAMETERS: ');
146 psa_utils.debug_other_string(g_state_level,l_full_path,' =========== ');
147 psa_utils.debug_other_string(g_state_level,l_full_path,' x_ccid --> ' || x_ccid);
148 FOR I IN 1..x_segment_info.count
149 LOOP
150 psa_utils.debug_other_string(g_state_level,l_full_path,' x_segment_info --> ' || x_segment_info(I) );
151 END LOOP;
152 psa_utils.debug_other_string(g_state_level,l_full_path,' x_number_of_segments --> ' || x_number_of_segments );
153 -- ========================= FND LOG ===========================
154
155 l_count := ccid_info.count;
156
157 -- ========================= FND LOG ===========================
158 psa_utils.debug_other_string(g_state_level,l_full_path,' l_count -> ' || l_count);
159 -- ========================= FND LOG ===========================
160
161 IF x_ccid IS NOT NULL THEN
162
163 -- ========================= FND LOG ===========================
164 psa_utils.debug_other_string(g_state_level,l_full_path,' x_ccid IS NOT NULL ');
165 -- ========================= FND LOG ===========================
166
167 FOR I IN 1..l_count
168 LOOP
169
170 -- ========================= FND LOG ===========================
171 psa_utils.debug_other_string(g_state_level,l_full_path, nvl(ccid_info(I).ccid,-1) || '=' || x_ccid);
172 -- ========================= FND LOG ===========================
173
174 IF nvl(ccid_info(I).ccid,-1) = x_ccid THEN
175 IF ccid_info(I).segment1 IS NOT NULL THEN x_segment_info(1) := ccid_info(I).segment1; END IF;
176 IF ccid_info(I).segment2 IS NOT NULL THEN x_segment_info(2) := ccid_info(I).segment2; END IF;
177 IF ccid_info(I).segment3 IS NOT NULL THEN x_segment_info(3) := ccid_info(I).segment3; END IF;
178 IF ccid_info(I).segment4 IS NOT NULL THEN x_segment_info(4) := ccid_info(I).segment4; END IF;
179 IF ccid_info(I).segment5 IS NOT NULL THEN x_segment_info(5) := ccid_info(I).segment5; END IF;
180 IF ccid_info(I).segment6 IS NOT NULL THEN x_segment_info(6) := ccid_info(I).segment6; END IF;
181 IF ccid_info(I).segment7 IS NOT NULL THEN x_segment_info(7) := ccid_info(I).segment7; END IF;
182 IF ccid_info(I).segment8 IS NOT NULL THEN x_segment_info(8) := ccid_info(I).segment8; END IF;
186 IF ccid_info(I).segment12 IS NOT NULL THEN x_segment_info(12) := ccid_info(I).segment12; END IF;
183 IF ccid_info(I).segment9 IS NOT NULL THEN x_segment_info(9) := ccid_info(I).segment9; END IF;
184 IF ccid_info(I).segment10 IS NOT NULL THEN x_segment_info(10) := ccid_info(I).segment10; END IF;
185 IF ccid_info(I).segment11 IS NOT NULL THEN x_segment_info(11) := ccid_info(I).segment11; END IF;
187 IF ccid_info(I).segment13 IS NOT NULL THEN x_segment_info(13) := ccid_info(I).segment13; END IF;
188 IF ccid_info(I).segment14 IS NOT NULL THEN x_segment_info(14) := ccid_info(I).segment14; END IF;
189 IF ccid_info(I).segment15 IS NOT NULL THEN x_segment_info(15) := ccid_info(I).segment15; END IF;
190 IF ccid_info(I).segment16 IS NOT NULL THEN x_segment_info(16) := ccid_info(I).segment16; END IF;
191 IF ccid_info(I).segment17 IS NOT NULL THEN x_segment_info(17) := ccid_info(I).segment17; END IF;
192 IF ccid_info(I).segment18 IS NOT NULL THEN x_segment_info(18) := ccid_info(I).segment18; END IF;
193 IF ccid_info(I).segment19 IS NOT NULL THEN x_segment_info(19) := ccid_info(I).segment19; END IF;
194 IF ccid_info(I).segment20 IS NOT NULL THEN x_segment_info(20) := ccid_info(I).segment20; END IF;
195 IF ccid_info(I).segment21 IS NOT NULL THEN x_segment_info(21) := ccid_info(I).segment21; END IF;
196 IF ccid_info(I).segment22 IS NOT NULL THEN x_segment_info(22) := ccid_info(I).segment22; END IF;
197 IF ccid_info(I).segment23 IS NOT NULL THEN x_segment_info(23) := ccid_info(I).segment23; END IF;
198 IF ccid_info(I).segment24 IS NOT NULL THEN x_segment_info(24) := ccid_info(I).segment24; END IF;
199 IF ccid_info(I).segment25 IS NOT NULL THEN x_segment_info(25) := ccid_info(I).segment25; END IF;
200 IF ccid_info(I).segment26 IS NOT NULL THEN x_segment_info(26) := ccid_info(I).segment26; END IF;
201 IF ccid_info(I).segment27 IS NOT NULL THEN x_segment_info(27) := ccid_info(I).segment27; END IF;
202 IF ccid_info(I).segment28 IS NOT NULL THEN x_segment_info(28) := ccid_info(I).segment28; END IF;
203 IF ccid_info(I).segment29 IS NOT NULL THEN x_segment_info(29) := ccid_info(I).segment29; END IF;
204 IF ccid_info(I).segment30 IS NOT NULL THEN x_segment_info(30) := ccid_info(I).segment30; END IF;
205
206 x_number_of_segments := ccid_info(I).number_of_segments;
207
208 -- ========================= FND LOG ===========================
209 psa_utils.debug_other_string(g_state_level,l_full_path,' x_number_of_segments -> ' || x_number_of_segments);
210 FOR I IN 1..x_number_of_segments
211 LOOP
212 psa_utils.debug_other_string(g_state_level,l_full_path,' x_segment_info --> ' || x_segment_info(I) );
213 END LOOP;
214 psa_utils.debug_other_string(g_state_level,l_full_path,'RETURN -> TRUE');
215 -- ========================= FND LOG ===========================
216
217 RETURN TRUE;
218
219 END IF;
220
221 END LOOP;
222
223 -- ========================= FND LOG ===========================
224 psa_utils.debug_other_string(g_state_level,l_full_path,' RETURN -> FALSE');
225 -- ========================= FND LOG ===========================
226 RETURN FALSE;
227
228 ELSE
229
230 -- ========================= FND LOG ===========================
231 psa_utils.debug_other_string(g_state_level,l_full_path,' x_ccid IS NULL ');
232 -- ========================= FND LOG ===========================
233
234 l_segment_info := x_segment_info;
235
236 -- ========================= FND LOG ===========================
237 psa_utils.debug_other_string(g_state_level,l_full_path,' Number of segments : '||l_segment_info.count);
238 -- ========================= FND LOG ===========================
239
240 FOR J IN (x_segment_info.count + 1)..30
241 LOOP
242 l_segment_info(J) := NULL;
243 END LOOP;
244
245 FOR I IN 1..ccid_info.count
246 LOOP
247 l_no_match := 0;
248
249 IF ((l_segment_info(1) IS NOT NULL AND ccid_info(I).segment1 <> l_segment_info(1))
250 OR (l_segment_info(2) IS NOT NULL AND ccid_info(I).segment2 <> l_segment_info(2))
251 OR (l_segment_info(3) IS NOT NULL AND ccid_info(I).segment3 <> l_segment_info(3))
252 OR (l_segment_info(4) IS NOT NULL AND ccid_info(I).segment4 <> l_segment_info(4))
253 OR (l_segment_info(5) IS NOT NULL AND ccid_info(I).segment5 <> l_segment_info(5))
254 OR (l_segment_info(6) IS NOT NULL AND ccid_info(I).segment6 <> l_segment_info(6))
255 OR (l_segment_info(7) IS NOT NULL AND ccid_info(I).segment7 <> l_segment_info(7))
256 OR (l_segment_info(8) IS NOT NULL AND ccid_info(I).segment8 <> l_segment_info(8))
257 OR (l_segment_info(9) IS NOT NULL AND ccid_info(I).segment9 <> l_segment_info(9))
258 OR (l_segment_info(10) IS NOT NULL AND ccid_info(I).segment10 <> l_segment_info(10))
259 OR (l_segment_info(11) IS NOT NULL AND ccid_info(I).segment11 <> l_segment_info(11))
260 OR (l_segment_info(12) IS NOT NULL AND ccid_info(I).segment12 <> l_segment_info(12))
261 OR (l_segment_info(13) IS NOT NULL AND ccid_info(I).segment13 <> l_segment_info(13))
262 OR (l_segment_info(14) IS NOT NULL AND ccid_info(I).segment14 <> l_segment_info(14))
263 OR (l_segment_info(15) IS NOT NULL AND ccid_info(I).segment15 <> l_segment_info(15))
264 OR (l_segment_info(16) IS NOT NULL AND ccid_info(I).segment16 <> l_segment_info(16))
265 OR (l_segment_info(17) IS NOT NULL AND ccid_info(I).segment17 <> l_segment_info(17))
266 OR (l_segment_info(18) IS NOT NULL AND ccid_info(I).segment18 <> l_segment_info(18))
267 OR (l_segment_info(19) IS NOT NULL AND ccid_info(I).segment19 <> l_segment_info(19))
268 OR (l_segment_info(20) IS NOT NULL AND ccid_info(I).segment20 <> l_segment_info(20))
272 OR (l_segment_info(24) IS NOT NULL AND ccid_info(I).segment24 <> l_segment_info(24))
269 OR (l_segment_info(21) IS NOT NULL AND ccid_info(I).segment21 <> l_segment_info(21))
270 OR (l_segment_info(22) IS NOT NULL AND ccid_info(I).segment22 <> l_segment_info(22))
271 OR (l_segment_info(23) IS NOT NULL AND ccid_info(I).segment23 <> l_segment_info(23))
273 OR (l_segment_info(25) IS NOT NULL AND ccid_info(I).segment25 <> l_segment_info(25))
274 OR (l_segment_info(26) IS NOT NULL AND ccid_info(I).segment26 <> l_segment_info(26))
275 OR (l_segment_info(27) IS NOT NULL AND ccid_info(I).segment27 <> l_segment_info(27))
276 OR (l_segment_info(28) IS NOT NULL AND ccid_info(I).segment28 <> l_segment_info(28))
277 OR (l_segment_info(29) IS NOT NULL AND ccid_info(I).segment29 <> l_segment_info(29))
278 OR (l_segment_info(30) IS NOT NULL AND ccid_info(I).segment30 <> l_segment_info(30))) THEN
279 l_no_match := 1;
280 END IF;
281
282 IF l_no_match = 0 THEN
283 x_ccid := ccid_info(I).ccid;
284
285 -- ========================= FND LOG ===========================
286 psa_utils.debug_other_string(g_state_level,l_full_path, ' x_ccid -> ' || x_ccid);
287 psa_utils.debug_other_string(g_state_level,l_full_path, ' RETURN -> TRUE');
288 -- ========================= FND LOG ===========================
289
290 RETURN TRUE;
291
292 END IF;
293
294 End Loop;
295
296 -- ========================= FND LOG ===========================
297 psa_utils.debug_other_string(g_state_level,l_full_path, ' RETURN -> FALSE');
298 -- ========================= FND LOG ===========================
299
300 RETURN FALSE;
301
302 END IF;
303
304 RETURN FALSE;
305
306 EXCEPTION
307 WHEN OTHERS THEN
308 -- ========================= FND LOG ===========================
309 psa_utils.debug_other_string(g_excep_level,l_full_path,'EXCEPTION - OTHERS : ERROR IN PSA_MFAR_UTILS.is_ccid_exists');
310 psa_utils.debug_other_string(g_excep_level,l_full_path,'RETURN -> FALSE');
311 psa_utils.debug_other_string(g_excep_level,l_full_path, sqlcode || sqlerrm);
312 psa_utils.debug_unexpected_msg(l_full_path);
313 -- ========================= FND LOG ===========================
314 RETURN FALSE;
315
316 END is_ccid_exists;
317
318 /* ================================== OVERRIDE_SEGMENTS ================================= */
319
320 FUNCTION override_segments
321 (p_primary_ccid IN NUMBER,
322 p_override_ccid IN NUMBER,
323 p_set_of_books_id IN NUMBER,
324 p_trx_type IN VARCHAR2,
325 P_ccid OUT NOCOPY NUMBER)
326 RETURN BOOLEAN IS
327
328 l_primary_segments FND_FLEX_EXT.SEGMENTARRAY;
329 l_override_segments FND_FLEX_EXT.SEGMENTARRAY;
330 l_segments FND_FLEX_EXT.SEGMENTARRAY;
331 l_chart_of_accounts_id NUMBER;
332 l_num_segments NUMBER;
333 l_natural_account GL_CODE_COMBINATIONS.SEGMENT1%TYPE;
334 l_mapped_account GL_CODE_COMBINATIONS.SEGMENT1%TYPE;
335 l_primary_account GL_CODE_COMBINATIONS.SEGMENT1%TYPE;
336 l_fndflex_message VARCHAR2(3000);
337 l_ccid NUMBER;
338 l_conc_segments VARCHAR2(800);
339 l_combination_exists BOOLEAN;
340
341 GET_SEGMENTS_EXCEP EXCEPTION;
342 GET_QUALIFIER_SEGNUM_EXCEP EXCEPTION;
343 GET_COMBINATION_ID_EXCEP EXCEPTION;
344
345 -- ========================= FND LOG ===========================
346 l_full_path VARCHAR2(100) := g_path || 'override_segments';
347 -- ========================= FND LOG ===========================
348
349 BEGIN
350
351 -- ========================= FND LOG ===========================
352 psa_utils.debug_other_string(g_state_level,l_full_path,' Inside override_segments ');
353 psa_utils.debug_other_string(g_state_level,l_full_path,' PARAMETERS: ');
354 psa_utils.debug_other_string(g_state_level,l_full_path,' =========== ');
355 psa_utils.debug_other_string(g_state_level,l_full_path,' p_primary_ccid --> ' || p_primary_ccid);
356 psa_utils.debug_other_string(g_state_level,l_full_path,' p_override_ccid --> ' || p_override_ccid );
357 psa_utils.debug_other_string(g_state_level,l_full_path,' p_set_of_books_id --> ' || p_set_of_books_id );
358 psa_utils.debug_other_string(g_state_level,l_full_path,' p_trx_type --> ' || p_trx_type);
359 psa_utils.debug_other_string(g_state_level,l_full_path,' g_chart_of_accounts_id --> ' || g_chart_of_accounts_id);
360
361 -- ========================= FND LOG ===========================
362
363 -- Get Chart of Accounts ID
364 IF g_chart_of_accounts_id IS NULL THEN
365
366 SELECT chart_of_accounts_id
367 INTO g_chart_of_accounts_id
368 FROM gl_sets_of_books
369 WHERE set_of_books_id = p_set_of_books_id;
370
371 -- ========================= FND LOG ===========================
372 psa_utils.debug_other_string(g_state_level,l_full_path,' g_chart_of_accounts_id --> ' || g_chart_of_accounts_id);
373 psa_utils.debug_other_string(g_state_level,l_full_path,' Getting org details ');
374 -- ========================= FND LOG ===========================
375
376 PSA_MF_ORG_DETAILS (g_org_details);
377
378 -- ========================= FND LOG ===========================
379 psa_utils.debug_other_string(g_state_level,l_full_path,' Calling FND_FLEX_APIS.GET_QUALIFIER_SEGNUM - balancing segment');
380
381
382 -- ========================= FND LOG ===========================
383
384 -- Get balancing segment number
385 IF (NOT FND_FLEX_APIS.GET_QUALIFIER_SEGNUM(
389 FLEX_QUAL_NAME => 'GL_BALANCING',
386 APPL_ID => 101,
387 KEY_FLEX_CODE => 'GL#',
388 STRUCTURE_NUMBER => g_chart_of_accounts_id,
390 SEGMENT_NUMBER => g_bal_acct_seg_num)) -- OUT
391 THEN
392 -- ========================= FND LOG ===========================
393 psa_utils.debug_other_string(g_state_level,l_full_path,' g_bal_acct_seg_num --> ' || g_bal_acct_seg_num );
394 psa_utils.debug_other_string(g_state_level,l_full_path,' Raising GET_QUALIFIER_SEGNUM_EXCEP ');
395 -- ========================= FND LOG ===========================
396 RAISE GET_QUALIFIER_SEGNUM_EXCEP;
397 ELSE
398 -- ========================= FND LOG ===========================
399 psa_utils.debug_other_string(g_state_level,l_full_path,' g_bal_acct_seg_num --> ' || g_bal_acct_seg_num );
400 -- ========================= FND LOG ===========================
401 END IF;
402
403 -- ========================= FND LOG ===========================
404 psa_utils.debug_other_string(g_state_level,l_full_path,' Calling FND_FLEX_APIS.GET_QUALIFIER_SEGNUM - natural account ');
405 -- ========================= FND LOG ===========================
406
407 -- Get natural account segment number
408 IF (NOT FND_FLEX_APIS.GET_QUALIFIER_SEGNUM( APPL_ID => 101,
409 KEY_FLEX_CODE => 'GL#',
410 STRUCTURE_NUMBER => g_chart_of_accounts_id,
411 FLEX_QUAL_NAME => 'GL_ACCOUNT',
412 SEGMENT_NUMBER => g_nat_acct_seg_num)) THEN -- OUT
413 -- ========================= FND LOG ===========================
414 psa_utils.debug_other_string(g_state_level,l_full_path,' g_nat_acct_seg_num --> ' || g_nat_acct_seg_num );
415 psa_utils.debug_other_string(g_state_level,l_full_path,' Raising GET_QUALIFIER_SEGNUM_EXCEP ');
416 -- ========================= FND LOG ===========================
417 RAISE GET_QUALIFIER_SEGNUM_EXCEP;
418 ELSE
419 -- ========================= FND LOG ===========================
420 psa_utils.debug_other_string(g_state_level,l_full_path,' g_nat_acct_seg_num --> ' || g_nat_acct_seg_num );
421 -- ========================= FND LOG ===========================
422 END IF;
423
424 END IF; -- end if for chart of accounts id is null
425
426 -- ========================= FND LOG ===========================
427 psa_utils.debug_other_string(g_state_level,l_full_path,' Calling FND_FLEX_EXT.GET_SEGMENTS - primary segment array');
428 -- ========================= FND LOG ===========================
429
430 -- Get Primary segment array
431 l_ccid := P_primary_ccid;
432
433 -- ========================= FND LOG ===========================
434 psa_utils.debug_other_string(g_state_level,l_full_path,' l_ccid -> ' || l_ccid);
435 -- ========================= FND LOG ===========================
436
437 IF NOT (is_ccid_exists (l_ccid, l_primary_segments, l_num_segments)) THEN
438
439 -- ========================= FND LOG ===========================
440 psa_utils.debug_other_string(g_state_level,l_full_path,' is_ccid_exists -> FALSE' );
441 -- ========================= FND LOG ===========================
442
443 IF (NOT FND_FLEX_EXT.GET_SEGMENTS(
444 APPLICATION_SHORT_NAME => 'SQLGL',
445 KEY_FLEX_CODE => 'GL#',
446 STRUCTURE_NUMBER => g_chart_of_accounts_id,
447 COMBINATION_ID => P_primary_ccid,
448 N_SEGMENTS => l_num_segments, -- OUT
449 SEGMENTS => l_primary_segments)) Then -- OUT
450
451 -- ========================= FND LOG ===========================
452 psa_utils.debug_other_string(g_state_level,l_full_path,' l_num_segments --> ' || l_num_segments);
453 psa_utils.debug_other_string(g_state_level,l_full_path,' Raising GET_SEGMENTS_EXCEP ');
454 -- ========================= FND LOG ===========================
455 RAISE GET_SEGMENTS_EXCEP;
456 ELSE
457 -- ========================= FND LOG ===========================
458 psa_utils.debug_other_string(g_state_level,l_full_path,' l_num_segments --> ' || l_num_segments);
459 FOR i IN 1..l_num_segments LOOP
460 psa_utils.debug_other_string(g_state_level,l_full_path,' l_primary_segments(i) --> ' || l_primary_segments(i));
461 END LOOP;
462 -- ========================= FND LOG ===========================
463 insert_ccid(l_ccid, l_primary_segments, l_num_segments);
464
465 END IF;
466
467 ELSE
468 -- ========================= FND LOG ===========================
469 psa_utils.debug_other_string(g_state_level,l_full_path,' is_ccid_exists -> TRUE' );
470 -- ========================= FND LOG ===========================
471 END IF;
472
473 -- ========================= FND LOG ===========================
474 psa_utils.debug_other_string(g_state_level,l_full_path,' Calling FND_FLEX_EXT.GET_SEGMENTS - override segment array ');
475 -- ========================= FND LOG ===========================
476
480 -- ========================= FND LOG ===========================
477 -- Get Override segment array
478 l_ccid := P_override_ccid ;
479
481 psa_utils.debug_other_string(g_state_level,l_full_path,' l_ccid -> ' || l_ccid );
482 -- ========================= FND LOG ===========================
483
484 IF NOT (is_ccid_exists (l_ccid, l_override_segments, l_num_segments)) THEN
485
486 -- ========================= FND LOG ===========================
487 psa_utils.debug_other_string(g_state_level,l_full_path,' is_ccid_exists -> FALSE' );
488 -- ========================= FND LOG ===========================
489
490 IF (NOT FND_FLEX_EXT.GET_SEGMENTS(
491 APPLICATION_SHORT_NAME => 'SQLGL',
492 KEY_FLEX_CODE => 'GL#',
493 STRUCTURE_NUMBER => g_chart_of_accounts_id,
494 COMBINATION_ID => P_override_ccid,
495 N_SEGMENTS => l_num_segments, -- OUT
496 SEGMENTS => l_override_segments)) Then -- OUT
497
498 -- ========================= FND LOG ===========================
499 psa_utils.debug_other_string(g_state_level,l_full_path,' l_num_segments --> ' || l_num_segments);
500 psa_utils.debug_other_string(g_state_level,l_full_path,' Raising GET_SEGMENTS_EXCEP ');
501 -- ========================= FND LOG ===========================
502 RAISE GET_SEGMENTS_EXCEP;
503 ELSE
504 -- ========================= FND LOG ===========================
505 psa_utils.debug_other_string(g_state_level,l_full_path,' l_num_segments --> ' || l_num_segments);
506 FOR i IN 1..l_num_segments LOOP
507 psa_utils.debug_other_string(g_state_level,l_full_path,' l_override_segments(i) --> '
508 || l_override_segments(i));
509 END LOOP;
510 -- ========================= FND LOG ===========================
511 insert_ccid(l_ccid, l_override_segments, l_num_segments);
512 END IF;
513
514 ELSE
515 -- ========================= FND LOG ===========================
516 psa_utils.debug_other_string(g_state_level,l_full_path,' is_ccid_exists -> TRUE' );
517 -- ========================= FND LOG ===========================
518 END IF;
519
520
521 -- ========================= FND LOG ===========================
522 psa_utils.debug_other_string(g_state_level,l_full_path,' g_org_details.allocation_method --> '
523 || g_org_details.allocation_method );
524 -- ========================= FND LOG ===========================
525
526 IF g_org_details.allocation_method = 'BAL' THEN
527
528 -- Override balancing segment
529 FOR i IN 1..l_num_segments LOOP
530 IF (i = g_bal_acct_seg_num) THEN
531 l_segments(i) := l_override_segments(i);
532 ELSE
533 l_segments(i) := l_primary_segments(i);
534 END IF;
535 -- ========================= FND LOG ===========================
536 psa_utils.debug_other_string(g_state_level,l_full_path,' l_segments(i) --> ' || l_segments(i) );
537 -- ========================= FND LOG ===========================
538 END LOOP;
539
540 ELSIF g_org_details.allocation_method = 'ACC' THEN
541
542 -- Override natural account segment
543 l_segments := l_override_segments;
544 l_primary_account := l_primary_segments (g_nat_acct_seg_num);
545 l_segments (g_nat_acct_seg_num) := l_primary_account;
546 -- ========================= FND LOG ===========================
547 psa_utils.debug_other_string(g_state_level,l_full_path,' l_primary_account --> ' || l_primary_account);
548 psa_utils.debug_other_string(g_state_level,l_full_path,' l_segments (l_nat_acct_seg_num) --> '
549 || l_segments (g_nat_acct_seg_num) );
550 -- ========================= FND LOG ===========================
551 END IF;
552
553 --
554 -- Check account mapping
555 --
556
557 -- ========================= FND LOG ===========================
558 psa_utils.debug_other_string(g_state_level,l_full_path,' p_trx_type --> ' || p_trx_type );
559 -- ========================= FND LOG ===========================
560
561 IF p_trx_type IN ('TRX', 'RCT', 'ADJ', 'MISC') THEN
562
563 -- ========================= FND LOG ===========================
564 psa_utils.debug_other_string(g_state_level,l_full_path,' g_org_details.mapping_required --> '
565 || g_org_details.mapping_required );
566 -- ========================= FND LOG ===========================
567
568 IF g_org_details.mapping_required = 'Y' THEN
569
570 --
571 -- Get account FROM mapping table
572 --
573 l_natural_account := l_override_segments (g_nat_acct_seg_num);
574 l_mapped_account := GET_MAPPED_ACCOUNT (p_trx_type, l_natural_account, p_set_of_books_id, g_chart_of_accounts_id);
575
576 -- ========================= FND LOG ===========================
577 psa_utils.debug_other_string(g_state_level,l_full_path,' l_natural_account --> ' || l_natural_account );
578 psa_utils.debug_other_string(g_state_level,l_full_path,' l_mapped_account --> ' || l_mapped_account );
579 -- ========================= FND LOG ===========================
580
581 --
582 -- Override natural account using mapped account
586 l_segments (g_nat_acct_seg_num) := l_mapped_account;
583 --
584
585 IF l_natural_account <> l_mapped_account THEN
587 -- ========================= FND LOG ===========================
588 psa_utils.debug_other_string(g_state_level,l_full_path,' l_segments (g_nat_acct_seg_num) --> '
589 || l_segments (g_nat_acct_seg_num) );
590 -- ========================= FND LOG ===========================
591 END IF;
592 END IF;
593 END IF;
594
595 -- ========================= FND LOG ===========================
596 psa_utils.debug_other_string(g_state_level,l_full_path,' Calling FND_FLEX_EXT.GET_COMBINATION_ID - overridden segments' );
597 -- ========================= FND LOG ===========================
598
599 --
600 -- Get ccid for overridden segments
601 --
602
603 IF NOT (is_ccid_exists (p_ccid, l_segments, l_num_segments)) THEN
604
605 -- ========================= FND LOG ===========================
606 psa_utils.debug_other_string(g_state_level,l_full_path,' is_ccid_exists -> FALSE' );
607
608 FOR I IN 1..l_segments.count
609 LOOP
610 psa_utils.debug_other_string(g_state_level,l_full_path,' l_segment_info --> ' || l_segments(I) );
611
612 END LOOP;
613 psa_utils.debug_other_string(g_state_level,l_full_path,'l_chart_of_acI :'||g_chart_of_acCOUNTS_ID);
614
615 psa_utils.debug_other_string(g_state_level,l_full_path,'L_NUM_SEG'||l_segments.count);
616
617
618 IF (NOT FND_FLEX_EXT.GET_COMBINATION_ID(
619 APPLICATION_SHORT_NAME => 'SQLGL',
620 KEY_FLEX_CODE => 'GL#',
621 STRUCTURE_NUMBER => g_chart_of_accounts_id,
622 VALIDATION_DATE => SYSDATE,
623 N_SEGMENTS => l_segments.count,
624 SEGMENTS => l_segments,
625 COMBINATION_ID => P_ccid)) Then -- OUT
626
627 -- ========================= FND LOG ===========================
628 psa_utils.debug_other_string(g_state_level,l_full_path,' P_ccid --> ' || P_ccid );
629 psa_utils.debug_other_string(g_state_level,l_full_path,' Raising GET_COMBINATION_ID_EXCEP ');
630 -- ========================= FND LOG ===========================
631 RAISE GET_COMBINATION_ID_EXCEP;
632 ELSE
633 -- ========================= FND LOG ===========================
634 psa_utils.debug_other_string(g_state_level,l_full_path,' P_ccid --> ' || P_ccid );
635 -- ========================= FND LOG ===========================
636 insert_ccid(p_ccid, l_segments, l_segments.count);
637 END IF;
638 ELSE
639 -- ========================= FND LOG ===========================
640 psa_utils.debug_other_string(g_state_level,l_full_path,' is_ccid_exists -> TRUE' );
641 -- ========================= FND LOG ===========================
642 END IF;
643
644 -- ========================= FND LOG ===========================
645 psa_utils.debug_other_string(g_state_level,l_full_path,' RETURNING TRUE ');
646 -- ========================= FND LOG ===========================
647
648 RETURN TRUE;
649
650 EXCEPTION
651
652 WHEN GET_SEGMENTS_EXCEP THEN
653 -- ========================= FND LOG ===========================
654 psa_utils.debug_other_string(g_state_level,l_full_path,' EXCEPTION - GET_SEGMENTS_EXCEP in override_segments');
655 -- ========================= FND LOG ===========================
656 RETURN FALSE;
657
658 WHEN GET_QUALIFIER_SEGNUM_EXCEP THEN
659 -- ========================= FND LOG ===========================
660 psa_utils.debug_other_string(g_state_level,l_full_path,' EXCEPTION - GET_QUALIFIER_SEGNUM_EXCEP in override_segments');
661 -- ========================= FND LOG ===========================
662 RETURN FALSE;
663
664 WHEN GET_COMBINATION_ID_EXCEP THEN
665 -- ========================= FND LOG ===========================
666 psa_utils.debug_other_string(g_state_level,l_full_path,' EXCEPTION- GET_COMBINATION_ID_EXCEP in override_segments');
667 -- ========================= FND LOG ===========================
668 BEGIN
669 IF g_segment_delimiter IS NULL THEN
670 g_segment_delimiter := fnd_flex_apis.get_segment_delimiter(
671 x_application_id => 101,
672 x_id_flex_code => 'GL#',
673 x_id_flex_num => g_chart_of_accounts_id);
674 END IF;
675
676 FOR i IN 1..l_segments.count LOOP
677 IF (i = l_segments.count) THEN
678 l_conc_segments := l_conc_segments || l_segments(i);
679 ELSE
680 l_conc_segments := l_conc_segments || l_segments(i) || g_segment_delimiter;
681 END IF;
682 END LOOP;
683
684 l_combination_exists := FALSE;
685 IF g_invalid_combinations.count > 0 THEN
686 FOR i IN 1..g_invalid_combinations.count LOOP
687 IF (g_invalid_combinations(i).combination = l_conc_segments) THEN
688 l_combination_exists := TRUE;
689 EXIT;
690 END IF;
691 END LOOP;
692 END IF;
693
694 IF NOT l_combination_exists THEN
695 g_invalid_index := g_invalid_combinations.count + 1;
699 /* Next 2 lines of code is required to continue the processing */
696 g_invalid_combinations(g_invalid_index).combination := l_conc_segments;
697 g_invalid_combinations(g_invalid_index).error_message := fnd_message.get;
698 END IF;
700 p_ccid := p_primary_ccid;
701 RETURN TRUE;
702 EXCEPTION
703 WHEN OTHERS THEN
704 -- ========================= FND LOG ===========================
705 psa_utils.debug_other_string(g_state_level,l_full_path,' EXCEPTION in GET_COMBINATION_ID_EXCEP in override_segments');
706 -- ========================= FND LOG ===========================
707 RETURN FALSE;
708 END;
709
710 RETURN FALSE;
711
712 WHEN OTHERS THEN
713 -- ========================= FND LOG ===========================
714 psa_utils.debug_other_string(g_excep_level,l_full_path,'EXCEPTION - OTHERS : ERROR IN PSA_MFAR_UTILS.override_segments');
715 psa_utils.debug_other_string(g_excep_level,l_full_path, sqlcode || sqlerrm);
716 psa_utils.debug_unexpected_msg(l_full_path);
717 -- ========================= FND LOG ===========================
718
719 APP_EXCEPTION.RAISE_EXCEPTION;
720
721 END override_segments;
722
723 /* ========================== INSERT_DISTRIBUTIONS_LOG ===================== */
724
725 PROCEDURE INSERT_DISTRIBUTIONS_LOG (p_error_id IN NUMBER,
726 p_activity IN VARCHAR2,
727 p_customer_trx_id IN NUMBER,
728 p_activity_id IN NUMBER,
729 p_error_message IN VARCHAR2) AS
730
731 PRAGMA AUTONOMOUS_TRANSACTION;
732
733 l_last_updated_by PSA_MF_DISTRIBUTIONS_LOG.LAST_UPDATED_BY%TYPE;
734 l_last_update_login PSA_MF_DISTRIBUTIONS_LOG.LAST_UPDATE_LOGIN%TYPE;
735
736 BEGIN
737
738 l_last_updated_by := FND_GLOBAL.USER_ID;
739
740 IF l_last_updated_by IS NULL THEN
741 l_last_updated_by := -1;
742 END IF;
743
744 l_last_update_login := FND_GLOBAL.LOGIN_ID;
745
746 IF l_last_update_login IS NULL THEN
747 l_last_update_login := -1;
748 END IF;
749
750 INSERT INTO PSA_MF_DISTRIBUTIONS_LOG ( ERROR_ID,
751 ACTIVITY,
752 CUSTOMER_TRX_ID,
753 LAST_UPDATE_DATE,
754 LAST_UPDATED_BY,
755 LAST_UPDATE_LOGIN,
756 CREATED_BY,
757 CREATED_DATE,
758 ACTIVITY_ID,
759 ERROR_MESSAGE)
760 VALUES (p_error_id,
761 p_activity,
762 p_customer_trx_id,
763 SYSDATE,
764 l_last_updated_by,
765 l_last_update_login,
766 l_last_update_login,
767 SYSDATE,
768 p_activity_id,
769 p_error_message);
770
771 COMMIT;
772
773 END INSERT_DISTRIBUTIONS_LOG;
774
775 /* ========================== GET_MAPPED_ACCOUNT ===================== */
776
777 FUNCTION GET_MAPPED_ACCOUNT (p_transaction_type IN VARCHAR2,
778 p_natural_account IN VARCHAR2,
779 p_set_of_books_id IN NUMBER,
780 p_chart_of_accounts_id IN NUMBER )
781 RETURN VARCHAR2 IS
782
783
784 CURSOR c_account_code IS
785 SELECT SUBSTR(v.compiled_value_attributes,5,1) account_code
786 FROM gl_sets_of_books b,
787 fnd_flex_values v,
788 fnd_id_flex_segments s
789 WHERE v.flex_value_set_id = s.flex_value_set_id
790 AND v.flex_value = p_natural_account
791 AND b.set_of_books_id = p_set_of_books_id
792 AND b.chart_of_accounts_id = p_chart_of_accounts_id
793 AND (s.application_id, s.id_flex_code, s.id_flex_num, s.application_column_name) =
794 (SELECT application_id, id_flex_code, id_flex_num, application_column_name
795 FROM fnd_segment_attribute_values a
796 WHERE id_flex_code = 'GL#'
797 AND segment_attribute_type = 'GL_ACCOUNT'
798 AND attribute_value = 'Y'
799 AND id_flex_num = b.chart_of_accounts_id
800 AND application_id =
801 (SELECT application_id
802 FROM fnd_application
803 WHERE application_short_name = 'SQLGL'));
804
805
806 CURSOR c_mapped_account (c_lookup_code IN VARCHAR2,
807 c_source_account IN VARCHAR2) IS
808 SELECT b.target_account mapped_account
809 FROM PSA_MF_ACCT_MAP_HEADER_ALL A,
810 PSA_MF_ACCOUNT_MAPPING_ALL B
811 WHERE a.psa_acct_mapping_id = b.psa_acct_mapping_id
812 AND a.org_id = l_org_id
813 AND a.document_code = p_transaction_type
814 AND a.lookup_code = c_lookup_code
815 AND b.source_account = c_source_account
816 AND trunc(sysdate) >= trunc(b.start_date_active)
817 AND trunc(sysdate) <= trunc(nvl(b.end_date_active,sysdate));
818
819 l_account_code fnd_flex_values.compiled_value_attributes%TYPE;
820 l_mapped_account PSA_MF_ACCOUNT_MAPPING_ALL.target_account%TYPE;
821
822 -- ========================= FND LOG ===========================
823 l_full_path VARCHAR2(100) := g_path || 'get_mapped_account';
824 -- ========================= FND LOG ===========================
825
826 BEGIN
827
828 -- ========================= FND LOG ===========================
829 psa_utils.debug_other_string(g_state_level,l_full_path,' Inside get_mapped_account');
830 psa_utils.debug_other_string(g_state_level,l_full_path,' PARAMETERS: ');
834 psa_utils.debug_other_string(g_state_level,l_full_path,' p_set_of_books_id --> ' || p_set_of_books_id );
831 psa_utils.debug_other_string(g_state_level,l_full_path,' =========== ');
832 psa_utils.debug_other_string(g_state_level,l_full_path,' p_transaction_type --> ' || p_transaction_type);
833 psa_utils.debug_other_string(g_state_level,l_full_path,' p_natural_account --> ' || p_natural_account);
835 psa_utils.debug_other_string(g_state_level,l_full_path,' p_chart_of_accounts_id --> ' || p_chart_of_accounts_id );
836 -- ========================= FND LOG ===========================
837
838 OPEN c_account_code;
839 FETCH c_account_code INTO l_account_code;
840 CLOSE c_account_code;
841
842 -- ========================= FND LOG ===========================
843 psa_utils.debug_other_string(g_state_level,l_full_path,' l_account_code --> ' || l_account_code);
844 -- ========================= FND LOG ===========================
845
846 OPEN c_mapped_account (l_account_code,
847 p_natural_account);
848 FETCH c_mapped_account INTO l_mapped_account;
849 CLOSE c_mapped_account;
850
851 -- ========================= FND LOG ===========================
852 psa_utils.debug_other_string(g_state_level,l_full_path,' l_mapped_account --> ' || l_mapped_account);
853 psa_utils.debug_other_string(g_state_level,l_full_path,' returning --> ' || nvl(l_mapped_account,p_natural_account));
854 -- ========================= FND LOG ===========================
855
856 RETURN nvl(l_mapped_account,p_natural_account);
857
858 END GET_MAPPED_ACCOUNT;
859
860
861 /* =========================== PSA_MF_ORG_DETAILS ========================== */
862
863 PROCEDURE psa_mf_org_details (l_org_details OUT NOCOPY psa_implementation_all%rowtype)
864 IS
865
866 CURSOR c_org_details (c_org_id NUMBER)
867 IS
868 SELECT *
869 FROM psa_implementation_all
870 WHERE org_id = c_org_id;
871
872 -- ========================= FND LOG ===========================
873 l_full_path VARCHAR2(100) := g_path || 'psa_mf_org_details';
874 -- ========================= FND LOG ===========================
875
876 BEGIN
877
878 -- ========================= FND LOG ===========================
879 psa_utils.debug_other_string(g_state_level,l_full_path,' Inside PSA_MF_ORG_DETAILS ');
880 -- ========================= FND LOG ===========================
881
882 FND_PROFILE.GET ('ORG_ID', l_org_id);
883
884 -- ========================= FND LOG ===========================
885 psa_utils.debug_other_string(g_state_level,l_full_path,' l_org_id --> ' || l_org_id);
886 -- ========================= FND LOG ===========================
887
888 OPEN c_org_details (l_org_id);
889 FETCH c_org_details INTO l_org_details;
890 CLOSE c_org_details;
891
892 -- ========================= FND LOG ===========================
893 psa_utils.debug_other_string(g_state_level,l_full_path,' Out of PSA_MF_ORG_DETAILS ');
894 -- ========================= FND LOG ===========================
895
896 END psa_mf_org_details ;
897
898
899 /* =========================== get_ar_sob_id ========================== */
900
901 FUNCTION get_ar_sob_id RETURN number
902 IS
903 l_ar_sob_id ar_system_parameters.set_of_books_id%TYPE;
904
905 -- ========================= FND LOG ===========================
906 l_full_path VARCHAR2(100) := g_path || 'get_ar_sob_id';
907 -- ========================= FND LOG ===========================
908
909 BEGIN
910
911 -- ========================= FND LOG ===========================
912 psa_utils.debug_other_string(g_state_level,l_full_path,' Inside get_ar_sob_id ');
913 -- ========================= FND LOG ===========================
914
915 SELECT set_of_books_id
916 INTO l_ar_sob_id
917 FROM ar_system_parameters;
918
919 -- ========================= FND LOG ===========================
920 psa_utils.debug_other_string(g_state_level,l_full_path,' l_ar_sob_id --> ' || l_ar_sob_id);
921 psa_utils.debug_other_string(g_state_level,l_full_path,' Out of get_ar_sob_id ');
922 -- ========================= FND LOG ===========================
923
924 RETURN l_ar_sob_id;
925
926 END get_ar_sob_id ;
927
928 /* =========================== get_rec_ccid ========================== */
929
930 FUNCTION get_rec_ccid (p_applied_trx_id IN NUMBER,
931 p_trx_id IN NUMBER)
932 RETURN NUMBER
933 is
934 CURSOR c_prev_trx_id
935 IS
936 SELECT previous_customer_trx_id
937 FROM ra_customer_trx_all
938 WHERE customer_trx_id = p_trx_id;
939
940 l_ret_code_combination NUMBER(15);
941 l_prev_trx_id NUMBER(15);
942 l_customer_trx_id NUMBER(15);
943
944 -- ========================= FND LOG ===========================
945 l_full_path VARCHAR2(100) := g_path || 'get_rec_ccid';
946 -- ========================= FND LOG ===========================
947
948 BEGIN
949
950 -- ========================= FND LOG ===========================
951 psa_utils.debug_other_string(g_state_level,l_full_path,' Inside get_rec_ccid ');
952 psa_utils.debug_other_string(g_state_level,l_full_path,' PARAMETERS: ');
953 psa_utils.debug_other_string(g_state_level,l_full_path,' =========== ');
954 psa_utils.debug_other_string(g_state_level,l_full_path,' p_applied_trx_id --> ' || p_applied_trx_id );
955 psa_utils.debug_other_string(g_state_level,l_full_path,' p_trx_id --> ' || p_trx_id );
959
956 -- ========================= FND LOG ===========================
957
958 IF p_applied_trx_id IS NOT NULL THEN
960 OPEN c_prev_trx_id;
961 FETCH c_prev_trx_id INTO l_prev_trx_id;
962 CLOSE c_prev_trx_id;
963
964 -- ========================= FND LOG ===========================
965 psa_utils.debug_other_string(g_state_level,l_full_path,' l_prev_trx_id --> ' || l_prev_trx_id);
966 -- ========================= FND LOG ===========================
967
968 IF l_prev_trx_id IS NULL THEN -- on account credit memo. RETURN trx_id's A/c
969 l_customer_trx_id := p_trx_id;
970 ELSE
971 l_customer_trx_id := p_applied_trx_id;
972 END IF;
973
974 -- ========================= FND LOG ===========================
975 psa_utils.debug_other_string(g_state_level,l_full_path,' l_customer_trx_id --> ' || l_customer_trx_id );
976 -- ========================= FND LOG ===========================
977
978 ELSE
979 l_customer_trx_id := p_trx_id;
980 -- ========================= FND LOG ===========================
981 psa_utils.debug_other_string(g_state_level,l_full_path,' ELSE l_customer_trx_id --> ' || l_customer_trx_id );
982 -- ========================= FND LOG ===========================
983 END IF;
984
985
986 SELECT code_combination_id
987 INTO l_ret_code_combination
988 FROM ra_cust_trx_line_gl_dist_all
989 WHERE customer_trx_id = l_customer_trx_id
990 AND account_class = 'REC'
991 AND account_set_flag = 'N';
992
993 -- ========================= FND LOG ===========================
994 psa_utils.debug_other_string(g_state_level,l_full_path,' l_ret_code_combination --> ' || l_ret_code_combination);
995 -- ========================= FND LOG ===========================
996
997 RETURN l_ret_code_combination;
998
999 END get_rec_ccid;
1000
1001 /* =========================== get_rec_ccid ========================== */
1002
1003 FUNCTION get_coa (sob_id in number)
1004 RETURN number
1005 IS
1006 l_ret_coa_id NUMBER(15);
1007
1008 -- ========================= FND LOG ===========================
1009 l_full_path VARCHAR2(100) := g_path || 'get_coa';
1010 -- ========================= FND LOG ===========================
1011
1012 BEGIN
1013
1014 -- ========================= FND LOG ===========================
1015 psa_utils.debug_other_string(g_state_level,l_full_path,' Inside get_coa ');
1016 psa_utils.debug_other_string(g_state_level,l_full_path,' PARAMETERS: ');
1017 psa_utils.debug_other_string(g_state_level,l_full_path,' =========== ');
1018 psa_utils.debug_other_string(g_state_level,l_full_path,' sob_id --> ' || sob_id );
1019 -- ========================= FND LOG ===========================
1020
1021 SELECT chart_of_accounts_id
1022 INTO l_ret_coa_id
1023 FROM gl_sets_of_books
1024 WHERE set_of_books_id = sob_id;
1025
1026 -- ========================= FND LOG ===========================
1027 psa_utils.debug_other_string(g_state_level,l_full_path,' RETURN l_ret_coa_id --> ' || l_ret_coa_id);
1028 -- ========================= FND LOG ===========================
1029
1030 RETURN l_ret_coa_id;
1031
1032 END;
1033
1034 /* =========================== get_rec_ccid ========================== */
1035
1036 FUNCTION get_user_category_name (cat_name IN VARCHAR2)
1037 RETURN VARCHAR2
1038 IS
1039
1040 l_user_cat_name VARCHAR2(25);
1041 -- ========================= FND LOG ===========================
1042 l_full_path VARCHAR2(100) := g_path || 'get_user_category_name';
1043 -- ========================= FND LOG ===========================
1044
1045 BEGIN
1046
1047 -- ========================= FND LOG ===========================
1048 psa_utils.debug_other_string(g_state_level,l_full_path,' Inside get_user_category_name ');
1049 psa_utils.debug_other_string(g_state_level,l_full_path,' PARAMETERS: ');
1050 psa_utils.debug_other_string(g_state_level,l_full_path,' =========== ');
1051 psa_utils.debug_other_string(g_state_level,l_full_path,' cat_name --> ' || cat_name );
1052 -- ========================= FND LOG ===========================
1053
1054 SELECT user_je_category_name
1055 INTO l_user_cat_name
1056 FROM gl_je_categories
1057 WHERE je_category_name = cat_name;
1058
1059 -- ========================= FND LOG ===========================
1060 psa_utils.debug_other_string(g_state_level,l_full_path,' RETURN l_user_cat_name --> ' || l_user_cat_name);
1061 -- ========================= FND LOG ===========================
1062
1063 RETURN l_user_cat_name;
1064
1065 END get_user_category_name;
1066
1067 FUNCTION accounting_method
1068 RETURN VARCHAR2
1069 IS
1070 -- ========================= FND LOG ===========================
1071 l_full_path VARCHAR2(100) := g_path || 'accounting_method';
1072 -- ========================= FND LOG ===========================
1073
1074 BEGIN
1075
1076 -- ========================= FND LOG ===========================
1077 psa_utils.debug_other_string(g_state_level,l_full_path,' RETURN accounting_method --> ' || arp_global.sysparam.accounting_method);
1078 -- ========================= FND LOG ===========================
1079 RETURN arp_global.sysparam.accounting_method;
1080
1081 END accounting_method;
1082
1083 END PSA_MFAR_UTILS;