[Home] [Help]
1: PACKAGE BODY PSA_AR_GL_INTERFACE AS
2: /* $Header: PSAARTCB.pls 120.12 2006/09/13 11:47:25 agovil ship $ */
3:
4: --===========================FND_LOG.START=====================================
5: g_state_level NUMBER := FND_LOG.LEVEL_STATEMENT;
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,
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 ===========================
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
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
94: WHERE cr.cash_receipt_id = TO_NUMBER(SUBSTR(gl.reference22, 1, INSTR(gl.reference22, 'C')-1)))
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: --
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)))
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
133: ( SELECT 'Receipt Applied In This Posting Run'
134: FROM gl_interface ar
135: WHERE ar.user_je_source_name = 'Receivables'
136: AND ar.set_of_books_id = l_sob_id
137: AND ar.group_id = l_group_id
138: AND ar.reference29 = 'TRADE_CASH'
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:
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
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;
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:
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';
278: RETURN 'N';
279:
280: END is_mfar_transaction;
281:
282: END PSA_AR_GL_INTERFACE;