[Home] [Help]
PACKAGE BODY: APPS.FV_1099_TRANSACTION
Source
1 PACKAGE BODY fv_1099_transaction AS
2 --$Header: FVR1099B.pls 120.16 2006/08/10 08:47:58 ckappaga ship $
3 -- l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('FV_DEBUG_FLAG'),'N');
4 g_module_name VARCHAR2(100) ;
5
6
7 PROCEDURE fvr1099p
8 (errbuf OUT NOCOPY varchar2,
9 retcode OUT NOCOPY number,
10 v_creditors_tin IN varchar2,
11 v_year IN number,
12 v_rec_activity IN number,
13 v_include_charges IN varchar2)
14 IS
15 l_module_name VARCHAR2(200) ;
16 v_cust_name hz_parties.party_name%TYPE;
17 v_tax_ref hz_parties.tax_reference%TYPE;
18 v_address1 hz_locations.address1%TYPE;
19 v_address2 hz_locations.address2%TYPE;
20 v_address3 hz_locations.address3%TYPE;
21 v_address4 hz_locations.address4%TYPE;
22 v_city hz_locations.city%TYPE;
23 v_state hz_locations.state%TYPE;
24 v_postal_code hz_locations.postal_code%TYPE;
25 v_province hz_locations.province%TYPE;
26 v_country hz_locations.country%TYPE;
27 v_trx_number ra_customer_trx.trx_number%TYPE;
28 v_process_inv_id ra_customer_trx.customer_trx_id%TYPE;
29 v_customer_id ra_customer_trx.bill_to_customer_id%TYPE;
30 v_apply_date ar_adjustments.apply_date%TYPE;
31 v_debit_memo_sum number;
32 v_begin_date date;
33 v_end_date date;
34 v_org_id number;
35 v_user_id number;
36 v_login_id number;
37 v_sob_id number;
38 v_sob_name varchar2(30);
39
40 CURSOR debit_memos_c IS
41 select nvl(sum(nvl(amount,0)),0)
42 from ar_adjustments
43 where customer_trx_id in (select customer_trx_id
44 from ra_customer_trx,
45 fv_finance_charge_controls fcc
46 where related_customer_trx_id = v_process_inv_id
47 and cust_trx_type_id in (select cust_trx_type_id
48 from ra_cust_trx_types
49 where type = 'DM')
50 and interface_header_attribute3 = fcc.charge_type
51 and fcc.set_of_books_id = v_sob_id)
52 and set_of_books_id = v_sob_id
53 and status = 'A'
54 and receivables_trx_id = v_rec_activity
55 and apply_date between v_begin_date and v_end_date;
56
57 CURSOR all_adjustments_c(p_sob_id number) IS
58 select nvl(sum(nvl(amount,0)),0) sum_adjustments,
59 nvl(related_customer_trx_id, aa.customer_trx_id) id,
60 bill_to_customer_id
61 from ar_adjustments aa,
62 ra_customer_trx rct
63 where (aa.customer_trx_id = rct.customer_trx_id
64 or aa.customer_trx_id in (select customer_trx_id
65 from ra_customer_trx
66 where related_customer_trx_id = aa.customer_trx_id))
67 and aa.set_of_books_id = p_sob_id
68 and aa.status = 'A'
69 and aa.receivables_trx_id = v_rec_activity
70 and aa.apply_date between v_begin_date and v_end_date
71 group by nvl(related_customer_trx_id, aa.customer_trx_id),
72 bill_to_customer_id;
73
74 BEGIN
75
76 l_module_name := g_module_name || 'fvr1099p';
77
78 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
79 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'BEGIN');
80 END IF;
81 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
82 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'BEGIN');
83 END IF;
84
85 /* Multi org changes */
86 --- v_org_id := to_number(fnd_profile.value('ORG_ID'));
87 v_org_id := mo_global.get_current_org_id;
88 mo_utils.Get_Ledger_Info(v_org_id,v_sob_id,v_sob_name);
89
90 DELETE from fv_1099c
91 WHERE set_of_books_id = v_sob_id;
92
93
94 -- v_begin_date := to_date('01-JAN-'||substr(v_year,3,2));
95 -- v_end_date := to_date('31-DEC-'||substr(v_year,3,2));
96
97 SELECT to_date('01/01/'|| v_year, 'MM/DD/YYYY')
98 INTO v_begin_date
99 FROM DUAL;
100
101 -- v_begin_date := to_date(l_begin_date, 'DD-MON-YYYY') ;
102
103 SELECT to_date('12/31/'|| v_year, 'MM/DD/YYYY')
104 INTO v_end_date
105 FROM DUAL;
106
107 -- v_end_date := to_date(l_end_date, 'DD-MON-YYYY') ;
108
109
110
111 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
112 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'BEGIN_DATE ='||TO_CHAR(V_BEGIN_DATE, 'DD-MON-YYYY'));
113 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'BEGIN_DATE ='||TO_CHAR(V_END_DATE, 'DD-MON-YYYY'));
114 END IF;
115 v_user_id := FND_GLOBAL.USER_ID;
116 v_login_id := FND_GLOBAL.LOGIN_ID;
117
118 FOR v_adjustment_rec IN all_adjustments_c(v_sob_id) LOOP
119
120 -- sum all adjustments for the type specified including
121 -- all finance charges
122
123 -- reassign variables.
124 v_process_inv_id := v_adjustment_rec.id;
125 v_customer_id := v_adjustment_rec.bill_to_customer_id;
126
127 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
128 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'INVOICE_ID ='||V_PROCESS_INV_ID);
129 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'CUSTOMER_ID = '||V_CUSTOMER_ID);
130 END IF;
131
132 -- sum all finance charge debit memos for an invoice and specified type.
133 OPEN debit_memos_c;
134 FETCH debit_memos_c into v_debit_memo_sum;
135
136 IF debit_memos_c%NOTFOUND THEN
137 v_debit_memo_sum := 0;
138 END IF;
139
140 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
141 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'DEBIT_MEMO_SUM ='||V_DEBIT_MEMO_SUM);
142 END IF;
143 CLOSE debit_memos_c;
144
145 BEGIN
146 SELECT hzp.party_name,hzp.tax_reference
147 INTO v_cust_name, v_tax_ref
148 FROM hz_parties hzp, hz_cust_accounts hzca
149 WHERE hzca.cust_account_id = v_customer_id
150 AND hzca.party_id = hzp.party_id;
151
152 EXCEPTION
153 when others then
154 retcode := 2;
155 errbuf := 'A-'||sqlerrm;
156 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error1', errbuf) ;
157 /* Bug No: 1979347
158 Bug Desc: 1099-C SET-UP PROCESS FAILS WITH ERROR*/
159 --CLOSE all_adjustments_c;
160 --rollback;
161
162 END;
163
164 BEGIN
165
166 SELECT address1, address2, address3, address4, city, state,
167 postal_code, country, province
168 INTO v_address1, v_address2, v_address3, v_address4, v_city,
169 v_state, v_postal_code, v_country, v_province
170 FROM hz_locations hzl, hz_cust_acct_sites hzcas, hz_party_sites hzps
171 WHERE hzcas.cust_account_id = v_customer_id
172 AND hzcas.party_site_id = hzps.party_site_id
173 AND hzps.location_id = hzl.location_id;
174
175
176 EXCEPTION
177 when others then
178 retcode := 2;
179 errbuf := 'B-'||sqlerrm;
180 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error2', errbuf) ;
181 /* Bug No: 1979347
182 Bug Desc: 1099-C SET-UP PROCESS FAILS WITH ERROR*/
183 --CLOSE all_adjustments_c;
184 --rollback;
185
186 END;
187
188 BEGIN
189 SELECT trx_number
190 INTO v_trx_number
191 FROM ra_customer_trx
192 WHERE customer_trx_id = v_process_inv_id;
193 EXCEPTION
194 when others then
195 retcode := 2;
196 errbuf := 'C-'||sqlerrm;
197 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error3', errbuf) ;
198 /* Bug No: 1979347
199 Bug Desc: 1099-C SET-UP PROCESS FAILS WITH ERROR*/
200 --CLOSE all_adjustments_c;
201 --rollback;
202 END;
203
204 BEGIN
205
206 SELECT max(apply_date)
207 INTO v_apply_date
208 FROM ar_adjustments
209 WHERE customer_trx_id = v_process_inv_id;
210
211 IF v_apply_date is null THEN
212 -- there are only adjustments for a finance charge(s), find
213 -- the max date for the fc(s). (the main cursor will return
214 -- trx_number so the case when only fc are written off there
215 -- will not be a record in the adjustment table for the
216 -- invoice only the fc record.)
217 BEGIN
218 SELECT max(apply_date)
219 INTO v_apply_date
220 FROM ar_adjustments
221 WHERE customer_trx_id in (select customer_trx_id
222 from ra_customer_trx
223 where related_customer_trx_id =
224 v_process_inv_id)
225 AND set_of_books_id = v_sob_id
226 AND apply_date between v_begin_date and v_end_date
227 AND receivables_trx_id = v_rec_activity;
228 IF v_apply_date is null THEN
229 retcode := 2;
230 errbuf := 'D-Apply Date is null for Invoice Number '
231 ||v_trx_number;
232 ROLLBACK;
233 RETURN;
234 END IF;
235 EXCEPTION
236 WHEN others THEN
237 retcode := 2;
238 errbuf := 'D-'||sqlerrm;
239 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error4', errbuf) ;
240 /* Bug No: 1979347
241 Bug Desc: 1099-C SET-UP PROCESS FAILS WITH ERROR*/
242 --CLOSE all_adjustments_c;
243 --rollback;
244 END;
245 END IF;
246 EXCEPTION
247 when others THEN
248 retcode := 2;
249 errbuf := 'E-'||sqlerrm;
250 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error5', errbuf) ;
251 /* Bug No: 1979347
252 Bug Desc: 1099-C SET-UP PROCESS FAILS WITH ERROR*/
253 --CLOSE all_adjustments_c;
254 END;
255
256 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
257 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'APPLY_DATE = '||V_APPLY_DATE);
258 END IF;
259 INSERT INTO fv_1099c
260 (set_of_books_id,
261 creditors_tin,
262 customer_id,
263 date_canceled,
264 customer_name,
265 tax_id,
266 trx_number,
267 amount,
268 finance_charge_amount,
269 address1,
270 address2,
271 address3,
272 address4,
273 city,
274 state,
275 postal_code,
276 province,
277 country,
278 reportable_flag,
279 org_id,
280 last_update_date,
281 last_updated_by,
282 creation_date,
283 created_by,
284 last_update_login )
285 values
286 (v_sob_id,
287 v_creditors_tin,
288 v_customer_id,
289 v_apply_date,
290 v_cust_name,
291 v_tax_ref,
292 v_trx_number,
293 decode(v_include_charges,'Y',(v_adjustment_rec.sum_adjustments*-1),
294 ((v_adjustment_rec.sum_adjustments - v_debit_memo_sum)*-1)),
295 decode(v_include_charges,'Y',(v_debit_memo_sum*-1),0),
296 v_address1,
297 v_address2,
298 v_address3,
299 v_address4,
300 v_city,
301 v_state,
302 v_postal_code,
303 v_province,
304 v_country,
305 'Y',
306 v_org_id,
307 SYSDATE,
308 v_user_id,
309 SYSDATE,
310 v_user_id,
311 v_login_id );
312 END LOOP;
313 commit;
314 retcode := 0;
315 errbuf := null;
316 EXCEPTION
317 WHEN others then
318 retcode := 2;
319 errbuf := sqlerrm;
320 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception', errbuf) ;
321 rollback;
322 END;
323 BEGIN
324
325 g_module_name := 'fv.plsql.fv_1099_transaction.';
326
327
328 END fv_1099_transaction;