[Home] [Help]
PACKAGE BODY: APPS.JL_BR_AP_BALANCE_MAINTENANCE
Source
1 package body JL_BR_AP_BALANCE_MAINTENANCE as
2 /* $Header: jlbrpbmb.pls 115.10 2002/09/23 14:21:19 kpvs ship $ */
3
4 /**************************************************************************
5 * *
6 * Name : JL_BR_MESSAGE *
7 * Purpose : This procedure will put the message given in the log file *
8 * *
9 * *
10 * *
11 **************************************************************************/
12
13 PROCEDURE jl_br_message( p_message_code VARCHAR2,
14 p_token_1 VARCHAR2 DEFAULT NULL,
15 p_token_1_value VARCHAR2 DEFAULT NULL,
16 p_token_2 VARCHAR2 DEFAULT NULL,
17 p_token_2_value VARCHAR2 DEFAULT NULL,
18 p_token_3 VARCHAR2 DEFAULT NULL,
19 p_token_3_value VARCHAR2 DEFAULT NULL,
20 p_token_4 VARCHAR2 DEFAULT NULL,
21 p_token_4_value VARCHAR2 DEFAULT NULL
22 ) IS
23 BEGIN
24
25 FND_MESSAGE.SET_NAME('JL',p_message_code);
26 IF p_token_1 IS NOT NULL THEN
27 fnd_message.set_token(p_token_1, p_token_1_value);
28 END IF;
29
30 IF p_token_2 IS NOT NULL THEN
31 fnd_message.set_token(p_token_2, p_token_2_value);
32 END IF;
33
34 IF p_token_3 IS NOT NULL THEN
35 fnd_message.set_token(p_token_3, p_token_3_value);
36 END IF;
37
38 IF p_token_4 IS NOT NULL THEN
39 fnd_message.set_token(p_token_4, p_token_4_value);
40 END IF;
41
42 fnd_file.put_line(fnd_file.Log,fnd_message.get);
43
44
45 END jl_br_message;
46
47
48 /*----------------------------------------------------------------------------*
49 | PUBLIC FUNCTIONS/PROCEDURES |
50 *----------------------------------------------------------------------------*/
51
52 /*----------------------------------------------------------------------------*
53 | PROCEDURE |
54 | JL_BR_AP_BAL_MAINTENANCE |
55 | |
56 | DESCRIPTION |
57 | XLA process calls this routine several times depending on a commit cycle |
58 | which basically for each iteration has a different start date and end date|
59 | GL transfer run id and request id remains the same for the entire commit |
60 | cycle. |
61 | SOB cycle creates a different GL transfer run ID. |
62 | COMMIT OR ROLLBACK control is in xla package. |
63 | XLA process transactions in two cycles, the outer is for different SOBs |
64 | and the inner cycle is for different range of dates. |
65 | |
66 | Note: This process is considered for Accrual Basis Method and only |
67 | accounts for Liability and Gain and Loss lines. |
68 | Changes must be made for some other case outside this scope. |
69 | |
70 | PARAMETERS |
71 | INPUT |
72 | p_request_id contains the concurrent program request id |
73 | p_transfer_run_id contains the Transfer Run ID for a batch |
74 | p_start_date contains the start date of current commit cycle iteration. |
75 | p_end_date contains the end date of current commit cycle iteration. |
76 | |
77 | |
78 | OUTPUT |
79 | |
80 | |
81 | HISTORY |
82 | 11-AUG-99 Rafael Guerrero Created. |
83 *----------------------------------------------------------------------------*/
84
85 /*----------------------------------------------------------------------------*/
86 /*<<<<< JL_BR_AP_BAL_MAINTENANCE >>>>>*/
87 /*----------------------------------------------------------------------------*/
88
89 PROCEDURE JL_BR_AP_BAL_MAINTENANCE (p_request_id NUMBER,
90 p_transfer_run_id NUMBER,
91 p_start_date DATE,
92 p_end_date DATE)
93 IS
94
95 l_user_id NUMBER;
96 l_curr_calling_sequence VARCHAR2(240);
97 l_debug_info VARCHAR2(1000);
98 l_parameters VARCHAR2(1000);
99
100 cursor c_bmb is
101 -- Extract Liability credit lines
102 Select /*+ ORDERED */
103 aeh.set_of_books_id sob,
104 aeh.period_name per,
105 gp.period_year pyear,
106 gp.period_num pnum,
107 gs.period_set_name perset,
108 ael.code_combination_id ccid,
109 ael.third_party_id ven,
110 ael.third_party_sub_id site,
111 ael.currency_code cur,
112 aeh.accounting_date accd,
113 ai.invoice_num num,
114 ael.source_id invid, -- invoice_id
115 ai.invoice_date idat,
116 'Entrada/Estorno Docto' hist,
117 ael.ae_line_id inst,
118 decode(nvl(ael.accounted_Cr,0),0,decode(nvl(ael.entered_Cr,0),0,'D','C'),'C') isign,
119 decode(nvl(ael.accounted_Cr,0),0,decode(nvl(ael.entered_Cr,0),0,decode(nvl(ael.accounted_Dr,0),0,ael.entered_Dr,ael.accounted_Dr),ael.entered_Cr),ael.accounted_Cr) ival,
120 ab.batch_name bat,
121 ab.batch_id batid,
122 ai.org_id
123 From ap_ae_headers aeh,
124 ap_ae_lines ael,
125 ap_invoices ai,
126 gl_periods gp,
127 gl_sets_of_books gs,
128 ap_batches ab
129 Where
130 -- Validate Data Conditions
131 aeh.ae_category = 'Purchase Invoices'
132 and aeh.gl_transfer_Run_id = p_transfer_run_id -- create journals entries for those invoices being transfered.
133 and aeh.accounting_date between p_start_date and p_end_date
134 and ael.ae_line_type_code ='LIABILITY'
135 -- Join Conditions
136 and aeh.ae_header_id = ael.ae_header_id
137 and ael.source_id = ai.invoice_id
138 and aeh.set_of_books_id = gs.set_of_books_id
139 and gs.period_set_name = gp.period_set_name
140 and gp.period_name = aeh.period_name
141 and ai.batch_id = ab.batch_id(+)
142 UNION ALL
143 -- Extract Liability debit lines
144 Select /*+ ORDERED */
145 aeh.set_of_books_id sob,
146 aeh.period_name per,
147 gp.period_year pyear,
148 gp.period_num pnum,
149 gs.period_set_name perset,
150 ael.code_combination_id ccid,
151 ael.third_party_id ven,
152 ael.third_party_sub_id site,
153 ael.currency_code cur,
154 aeh.accounting_date accd,
155 ai.invoice_num num,
156 ael.source_id invid,
157 ai.invoice_date idat,
158 'Pagto/Estorno Docto' hist,
159 ael.ae_line_id inst,
160 decode(nvl(accounted_Cr,0),0,decode(nvl(entered_Cr,0),0,'D','C'),'C') isign,
161 decode(nvl(accounted_Cr,0),0,decode(nvl(entered_Cr,0),0,decode(nvl(accounted_Dr,0),0,entered_Dr,accounted_Dr),entered_Cr),accounted_Cr) ival,
162 ac.checkrun_name bat,
163 0 batid,
164 ai.org_id
165 From ap_ae_headers aeh,
166 ap_ae_lines ael,
167 ap_invoice_payments aip,
168 ap_invoices ai,
169 ap_checks ac,
170 gl_periods gp,
171 gl_sets_of_books gs
172 WHERE
173 -- Validate Data Conditions
174 aeh.ae_category = 'Payments'
175 and aeh.gl_transfer_Run_id = p_transfer_run_id -- create journals entries for those invoices being transfered.
176 and aeh.accounting_date between p_start_date and p_end_date
177 and ael.ae_line_type_code in ('LIABILITY','GAIN','LOSS') -- gain and loss are related to payment
178 and ( nvl(ac.payment_method_lookup_code, 'OLD') not in ('FUTURE DATED', 'MANUAL FUTURE DATED')
179 OR ( nvl(ac.payment_method_lookup_code, 'OLD') in ('FUTURE DATED', 'MANUAL FUTURE DATED')
180 AND nvl(aip.future_pay_posted_flag, 'N') = 'N') )
181 -- Join Conditons
182 and aeh.ae_header_id = ael.ae_header_id
183 and ael.source_id = aip.invoice_payment_id
184 and aip.invoice_id = ai.invoice_id
185 and aip.check_id = ac.check_id
186 and aeh.set_of_books_id = gs.set_of_books_id
187 and gs.period_set_name = gp.period_set_name
188 and gp.period_name = aeh.period_name;
189
190 BEGIN
191 -- fix for bug # 2587958
192 if fnd_profile.value('JGZZ_PRODUCT_CODE') <> 'JL' or fnd_profile.value('JGZZ_COUNTRY_CODE') <> 'BR' then
193 return;
194 end if;
195
196 l_curr_calling_sequence:='JL_BR_AP_BALANCE_MAINTENANCE.jl_br_ap_bal_maintenance';
197 l_parameters:=' p_request_id =' || to_char(p_request_id) || ' p_transfer_run_id= ' || TO_CHAR(p_transfer_run_id) ||
198 ' p_start_date= ' || TO_CHAR(p_start_date) || ' p_end_date= ' || TO_CHAR(p_end_date);
199
200 jl_br_message('JL_BR_ZZ_CREATE_JOURNALS');
201 l_user_id := FND_GLOBAL.user_id;
202 l_debug_info:='Inserting records into jl_br_journals table...';
203
204 FOR r_bmb in c_bmb LOOP
205
206 INSERT INTO JL_BR_JOURNALS (
207 APPLICATION_ID ,
208 SET_OF_BOOKS_ID ,
209 PERIOD_SET_NAME ,
210 PERIOD_NAME ,
211 CODE_COMBINATION_ID ,
212 PERSONNEL_ID ,
213 TRANS_CURRENCY_CODE ,
214 BATCH_ID ,
215 BATCH_NAME ,
216 ACCOUNTING_DATE ,
217 TRANS_ID ,
218 TRANS_NUM ,
219 TRANS_DATE ,
220 TRANS_DESCRIPTION ,
221 INSTALLMENT ,
222 TRANS_VALUE_SIGN ,
223 TRANS_VALUE ,
224 JOURNAL_BALANCE_FLAG,
225 LAST_UPDATE_DATE ,
226 LAST_UPDATED_BY ,
227 LAST_UPDATE_LOGIN ,
228 CREATION_DATE ,
229 CREATED_BY,
230 ORG_ID )
231 VALUES (
232 200,
233 r_bmb.sob,
234 r_bmb.perset,
235 r_bmb.per,
236 r_bmb.ccid,
237 r_bmb.ven,
238 r_bmb.cur,
239 r_bmb.batid,
240 r_bmb.bat,
241 r_bmb.accd,
242 r_bmb.invid,
243 r_bmb.num,
244 r_bmb.idat,
245 r_bmb.hist,
246 r_bmb.inst,
247 r_bmb.isign,
248 r_bmb.ival,
249 'N',
250 sysdate,
251 l_user_id,
252 '',
253 sysdate,
254 l_user_id,
255 r_bmb.org_id);
256
257 END LOOP;
258
259 EXCEPTION
260 WHEN OTHERS THEN
261 jl_br_message('JL_ZZ_AP_DEBUG','ERROR',SQLERRM,'CALLING_SEQUENCE',l_curr_calling_sequence,
262 'PARAMETERS', l_parameters,'DEBUG_INFO',l_debug_info);
263 APP_EXCEPTION.RAISE_EXCEPTION;
264
265 END JL_BR_AP_BAL_MAINTENANCE;
266
267 END JL_BR_AP_BALANCE_MAINTENANCE;