[Home] [Help]
PACKAGE BODY: APPS.PSA_AR_GL_INTERFACE
Source
4 --===========================FND_LOG.START=====================================
1 PACKAGE BODY PSA_AR_GL_INTERFACE AS
2 /* $Header: PSAARTCB.pls 120.12 2006/09/13 11:47:25 agovil ship $ */
3
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) := 'PSA.PLSQL.PSAARTCB.PSA_AR_GL_INTERFACE.';
12 --===========================FND_LOG.END=======================================
13
14 PROCEDURE reset_transaction_codes
15 (err_buf OUT NOCOPY VARCHAR2,
16 ret_code OUT NOCOPY VARCHAR2,
17 p_pstctrl_id IN VARCHAR2)
18 IS
19
20 Cursor c_mfar_enabled (c_org_id IN NUMBER) IS
21 Select status
22 From psa_implementation_all
23 Where org_id = c_org_id;
24
25 l_org_id NUMBER:=arp_global.sysparam.org_id;
26 l_sob_id NUMBER:=arp_global.sysparam.set_of_books_id;
27 l_enabled psa_implementation_all.status%type;
28 l_group_id gl_interface.group_id%type;
29
30 -- ========================= FND LOG ===========================
31 l_full_path VARCHAR2(100) := g_path || 'Reset_transaction_codes';
32 -- ========================= FND LOG ===========================
33
34 BEGIN
35
36 -- ========================= FND LOG ===========================
37 psa_utils.debug_other_string(g_state_level,l_full_path,
38 ' ########################## ');
39 psa_utils.debug_other_string(g_state_level,l_full_path,
40 ' ## Reset Transaction Codes START ## ');
41 psa_utils.debug_other_string(g_state_level,l_full_path,
42 ' ########################## ');
43 psa_utils.debug_other_string(g_state_level,l_full_path, ' '
44 || to_char (sysdate, 'DD/MM/YYYY HH:MI:SS'));
45 psa_utils.debug_other_string(g_state_level,l_full_path,' ');
46 psa_utils.debug_other_string(g_state_level,l_full_path,' PARAMETERS :');
47 psa_utils.debug_other_string(g_state_level,l_full_path,' ============');
48 psa_utils.debug_other_string(g_state_level,l_full_path,' p_pstctrl_id -->' || p_pstctrl_id );
49 psa_utils.debug_other_string(g_state_level,l_full_path,' Setting save point PSA_PSAARTCB' );
50 -- ========================= FND LOG ===========================
51
52 SAVEPOINT PSA_PSAARTCB;
53 ret_code := 'S';
54 l_group_id := p_pstctrl_id;
55
56 -- ========================= FND LOG ===========================
57 psa_utils.debug_other_string(g_state_level,l_full_path,' l_group_id -->' || l_group_id );
58 psa_utils.debug_other_string(g_state_level,l_full_path,' ');
59 psa_utils.debug_other_string(g_state_level,l_full_path,' PROCESS : ');
60 psa_utils.debug_other_string(g_state_level,l_full_path,' ========= ');
61 psa_utils.debug_other_string(g_state_level,l_full_path,' l_sob_id -->' || l_sob_id );
62 psa_utils.debug_other_string(g_state_level,l_full_path,' l_org_id -->' || l_org_id );
63 psa_utils.debug_other_string(g_state_level,l_full_path,' ');
64 -- ========================= FND LOG ===========================
65
66 UPDATE GL_INTERFACE
67 SET ussgl_transaction_code = NULL
68 WHERE user_je_source_name = 'Receivables'
69 AND set_of_books_id = l_sob_id
70 AND group_id = l_group_id
71 AND ussgl_transaction_code IS NOT NULL
72 AND ( (reference29 = 'MISC_CASH' AND reference30 = 'AR_CASH_RECEIPT_HISTORY')
73 OR
74 (reference29 IN ('INV_REC','CB_REC','CM_REC','DM_REC','TRADE_CASH','TRADE_UNAPP','ADJ_REC'))
75 OR
76 ( arp_global.sysparam.accounting_method = 'CASH' AND
77 reference29 = 'TRADE_APP' AND
78 reference30 = 'AR_RECEIVABLE_APPLICATIONS' ) );
79
80 -- ========================= FND LOG ===========================
81 psa_utils.debug_other_string(g_state_level,l_full_path,' UPDATE GL_INTERFACE ## 1 -->'
82 || SQL%ROWCOUNT );
83 -- ========================= FND LOG ===========================
84
85 --
86 -- Bug 2805101: Update TC from cash receipt header on TRADE_UNAPP
87 -- rows for unapplied receipts.
88 --
89
90 UPDATE GL_INTERFACE gl
91 SET gl.ussgl_transaction_code =
92 ( SELECT ussgl_transaction_code
93 FROM ar_cash_receipts cr
97 AND gl.group_id = l_group_id
94 WHERE cr.cash_receipt_id = TO_NUMBER(SUBSTR(gl.reference22, 1, INSTR(gl.reference22, 'C')-1)))
95 WHERE gl.user_je_source_name = 'Receivables'
96 AND gl.set_of_books_id = l_sob_id
98 AND gl.reference29 = 'TRADE_UNAPP'
99 AND gl.reference30 = 'AR_RECEIVABLE_APPLICATIONS'
100 AND EXISTS
101 ( SELECT 'Cash Receipt Unapplied'
102 FROM ar_cash_receipts ar
103 WHERE ar.cash_receipt_id = TO_NUMBER(SUBSTR(gl.reference22, 1, INSTR(gl.reference22, 'C')-1))
104 AND status = 'UNAPP' );
105
106 -- ========================= FND LOG ===========================
107 psa_utils.debug_other_string(g_state_level,l_full_path,' UPDATE GL_INTERFACE ## 2 -->'
108 || SQL%ROWCOUNT );
109 -- ========================= FND LOG ===========================
110
111 --
112 -- Bug 2805101: Update TC from cash receipt header on TRADE_UNAPP
113 -- rows if receipt is applied after the unapplied receipt
114 -- has been transferred previously.
115 --
116
117 UPDATE GL_INTERFACE gl
118 SET gl.ussgl_transaction_code =
119 ( SELECT ussgl_transaction_code
120 FROM ar_cash_receipts cr
121 WHERE cr.cash_receipt_id = TO_NUMBER(SUBSTR(gl.reference22, 1, INSTR(gl.reference22, 'C')-1)))
122 WHERE gl.user_je_source_name = 'Receivables'
123 AND gl.set_of_books_id = l_sob_id
124 AND gl.group_id = l_group_id
125 AND gl.reference29 = 'TRADE_UNAPP'
126 AND gl.reference30 = 'AR_RECEIVABLE_APPLICATIONS'
127 AND NOT EXISTS
128 ( SELECT 'Cash Receipt Unapplied'
129 FROM ar_cash_receipts ar
130 WHERE ar.cash_receipt_id = TO_NUMBER(SUBSTR(gl.reference22, 1, INSTR(gl.reference22, 'C')-1))
131 AND status = 'UNAPP' )
132 AND NOT EXISTS
136 AND ar.set_of_books_id = l_sob_id
133 ( SELECT 'Receipt Applied In This Posting Run'
134 FROM gl_interface ar
135 WHERE ar.user_je_source_name = 'Receivables'
137 AND ar.group_id = l_group_id
138 AND ar.reference29 = 'TRADE_CASH'
139 AND ar.reference30 = 'AR_CASH_RECEIPT_HISTORY'
140 AND SUBSTR(ar.reference22, 1, INSTR(ar.reference22, 'C')-1) =
141 SUBSTR(gl.reference22, 1, INSTR(gl.reference22, 'C')-1) );
142
143
144 -- ========================= FND LOG ===========================
145 psa_utils.debug_other_string(g_state_level,l_full_path,' UPDATE GL_INTERFACE ## 3 -->'
146 || SQL%ROWCOUNT );
147 -- ========================= FND LOG ===========================
148
149
150 IF l_org_id IS NOT NULL THEN
151
152 OPEN c_mfar_enabled (l_org_id);
153 FETCH c_mfar_enabled INTO l_enabled;
154 CLOSE c_mfar_enabled;
155
156 -- ========================= FND LOG ===========================
157 psa_utils.debug_other_string(g_state_level,l_full_path,' l_enabled -->' || l_enabled );
158 -- ========================= FND LOG ===========================
159
160 IF l_enabled = 'Y' THEN
161
162 UPDATE GL_INTERFACE
163 SET USSGL_TRANSACTION_CODE = NULL
164 WHERE user_je_source_name = 'Receivables'
165 AND set_of_books_id = l_sob_id
166 AND group_id = l_group_id
167 AND ussgl_transaction_code IS NOT NULL
168 AND reference29 = 'TRADE_REC'
169 AND reference30 = 'AR_RECEIVABLE_APPLICATIONS'
170 AND is_mfar_transaction
171 (TO_NUMBER(SUBSTR(reference22, INSTR(reference22, 'C')+1)), l_sob_id) = 'Y';
172
173 -- ========================= FND LOG ===========================
174 psa_utils.debug_other_string(g_state_level,l_full_path,' UPDATE GL_INTERFACE ## 4 -->'
175 || SQL%ROWCOUNT );
176 -- ========================= FND LOG ===========================
177
178 END IF;
179 END IF;
180
181 -- ========================= FND LOG ===========================
182 psa_utils.debug_other_string(g_state_level,l_full_path,
183 ' ########################## ');
187 ' ########################## ');
184 psa_utils.debug_other_string(g_state_level,l_full_path,
185 ' ## Reset Transaction Codes END ## ');
186 psa_utils.debug_other_string(g_state_level,l_full_path,
188 psa_utils.debug_other_string(g_state_level,l_full_path, ' '
189 || to_char (sysdate, 'DD/MM/YYYY HH:MI:SS'));
190 psa_utils.debug_other_string(g_state_level,l_full_path,' ');
191 -- ========================= FND LOG ===========================
192
193 EXCEPTION
194 WHEN OTHERS THEN
195 -- ========================= FND LOG ===========================
196 psa_utils.debug_other_string(g_excep_level,l_full_path,
197 ' --> EXCEPTION - OTHERS raised during PSA_AR_GL_INTERFACE.reset_transaction_codes ');
198 psa_utils.debug_other_string(g_excep_level,l_full_path, sqlcode || sqlerrm);
199 psa_utils.debug_unexpected_msg(l_full_path);
200 -- ========================= FND LOG ===========================
201
202 BEGIN
203 -- ========================= FND LOG ===========================
204 psa_utils.debug_other_string(g_excep_level,l_full_path,'Rolling back');
205 -- ========================= FND LOG ===========================
206 ROLLBACK TO PSA_PSAARTCB;
207 EXCEPTION
208 WHEN OTHERS THEN
209 -- ========================= FND LOG ===========================
210 psa_utils.debug_other_string(g_excep_level,l_full_path,
211 'EXCEPTION - OTHERS : SAVEPOINT ERASED.');
212 -- ========================= FND LOG ===========================
213 END;
214
215 err_buf := 2;
216 ret_code := 'F';
217
218 END reset_transaction_codes;
219
220 /* ################################ IS_MFAR_TRANSACTION ###################################### */
221
222 FUNCTION is_mfar_transaction (p_doc_id NUMBER, p_sob_id NUMBER) RETURN VARCHAR2
223 IS
224
225 Cursor c_trx_id (c_doc_id IN NUMBER)
226 IS
227 Select applied_customer_trx_id
228 From ar_receivable_applications
229 Where receivable_application_id = c_doc_id;
230
231 l_cust_trx_id NUMBER;
232 l_mfar_type VARCHAR2(1);
233
234
235 -- ========================= FND LOG ===========================
236 l_full_path VARCHAR2(100) := g_path || 'is_mfar_transaction';
237 -- ========================= FND LOG ===========================
238
239 BEGIN
240
241 -- ========================= FND LOG ===========================
242 psa_utils.debug_other_string(g_state_level,l_full_path,
243 ' ## is_mfar_transaction START ## ');
244 psa_utils.debug_other_string(g_state_level,l_full_path,' ');
245 psa_utils.debug_other_string(g_state_level,l_full_path,' PARAMETERS :');
246 psa_utils.debug_other_string(g_state_level,l_full_path,' ============');
247 psa_utils.debug_other_string(g_state_level,l_full_path,' p_doc_id -->' || p_doc_id );
248 psa_utils.debug_other_string(g_state_level,l_full_path,' p_sob_id -->' || p_sob_id );
249 -- ========================= FND LOG ===========================
250
251 OPEN c_trx_id(p_doc_id);
252 FETCH c_trx_id INTO l_cust_trx_id;
253 CLOSE c_trx_id;
254
255 -- ========================= FND LOG ===========================
256 psa_utils.debug_other_string(g_state_level,l_full_path,' l_cust_trx_id -->' || l_cust_trx_id );
257 -- ========================= FND LOG ===========================
258
259 IF PSA_MFAR_VAL_PKG.AR_MFAR_VALIDATE_CHECK (l_cust_trx_id, 'TRX', p_sob_id) = 'Y' THEN
260 -- ========================= FND LOG ===========================
261 psa_utils.debug_other_string(g_state_level,l_full_path,' RETURN Y' );
262 -- ========================= FND LOG ===========================
263 RETURN 'Y';
264 END IF;
265 -- ========================= FND LOG ===========================
266 psa_utils.debug_other_string(g_state_level,l_full_path,' RETURN N ' );
267 -- ========================= FND LOG ===========================
268 RETURN 'N';
269
270 EXCEPTION
271 WHEN OTHERS THEN
272 -- ========================= FND LOG ===========================
273 psa_utils.debug_other_string(g_excep_level,l_full_path,
274 ' --> EXCEPTION - OTHERS raised during PSA_AR_GL_INTERFACE.is_mfar_transaction ');
275 psa_utils.debug_other_string(g_excep_level,l_full_path, sqlcode || sqlerrm);
276 psa_utils.debug_unexpected_msg(l_full_path);
277 -- ========================= FND LOG ===========================
278 RETURN 'N';
279
280 END is_mfar_transaction;
281
282 END PSA_AR_GL_INTERFACE;