[Home] [Help]
PACKAGE BODY: APPS.JL_AR_AUTOINV_PKG
Source
1 package body JL_AR_AUTOINV_PKG as
2 /* $Header: jlarranb.pls 120.5.12010000.2 2008/12/15 18:49:06 vspuli ship $ */
3
4 PROCEDURE UPDATE_BATCH_SOURCE(p_invoice_date_from IN DATE,
5 p_invoice_date_to IN DATE,
6 p_gl_date_from IN DATE,
7 p_gl_date_to IN DATE,
8 p_ship_date_from IN DATE,
9 p_ship_date_to IN DATE,
10 p_default_date IN DATE) IS
11 l_contributor_class_code VARCHAR2(150);
12 l_organization_class_code VARCHAR2(150);
13 l_tax_category_id NUMBER;
14 l_org_attribute_name VARCHAR2(30);
15 l_org_attribute_value VARCHAR2(30);
16 l_cust_attribute_name VARCHAR2(30);
17 l_cust_attribute_value VARCHAR2(30);
18 l_document_letter VARCHAR2(1);
19 l_new_batch_source_name VARCHAR2(50);
20 l_so_org_id NUMBER;
21 l_batch_source_id NUMBER;
22 l_cus_cls_flag VARCHAR2(1);
23 error_condition EXCEPTION;
24 l_org_id NUMBER;
25 CURSOR trx_lines (p_invoice_date_from DATE,
26 p_invoice_date_to DATE,
27 p_gl_date_from DATE,
28 p_gl_date_to DATE,
29 p_ship_date_from DATE,
30 p_ship_date_to DATE) IS
31 SELECT rowid,
32 interface_line_attribute1,
33 cust_trx_type_id,
34 nvl(orig_system_ship_address_id,
35 orig_system_bill_address_id) orig_system_address_id,
36 batch_source_name,
37 trx_date,
38 org_id
39 FROM ra_interface_lines
40 WHERE nvl(interface_status, '~') <> 'P'
41 AND ((nvl(trx_date,sysdate-1) BETWEEN
42 nvl(p_invoice_date_from,nvl(trx_date,sysdate)) AND
43 nvl(p_invoice_date_to, nvl(trx_date,sysdate)))
44 OR (p_invoice_date_from is null and p_invoice_date_to is null
45 and trx_date is null))
46 AND ((nvl(gl_date,sysdate-1) BETWEEN
47 nvl(p_gl_date_from, nvl(gl_date,sysdate)) AND
48 nvl(p_gl_date_to, nvl(gl_date,sysdate)))
49 OR (p_gl_date_from is null and p_gl_date_to is null
50 and gl_date is null))
51 AND ((nvl(ship_date_actual,sysdate-1) BETWEEN
52 nvl(p_ship_date_from,nvl(ship_date_actual,sysdate)) AND
53 nvl(p_ship_date_to,nvl(ship_date_actual,sysdate)))
54 OR (p_ship_date_from is null and p_ship_date_to is null
55 and ship_date_actual is null));
56 BEGIN
57 arp_file.write_log('inside update_batch_source',0);
58 FOR trx_lines_rec IN
59 trx_lines(p_invoice_date_from, p_invoice_date_to,
60 p_gl_date_from, p_gl_date_to,
61 p_ship_date_from, p_ship_date_to)
62 LOOP
63
64 arp_file.write_log('Processing interface line '||
65 trx_lines_rec.interface_line_attribute1,0);
66
67 l_contributor_class_code := null;
68 l_organization_class_code := null;
69 l_tax_category_id := null;
70 l_org_attribute_name := null;
71 l_org_attribute_value := null;
72 l_cust_attribute_name := null;
73 l_cust_attribute_value := null;
74 l_document_letter := null;
75 l_batch_source_id := null;
76 l_so_org_id := null;
77 l_new_batch_source_name := null;
78
79 BEGIN
80 SELECT ra.global_attribute8
81 INTO l_contributor_class_code
82 FROM hz_cust_acct_sites ra
83 WHERE ra.cust_acct_site_id = trx_lines_rec.orig_system_address_id;
84 arp_file.write_log('Contributor class code '||l_contributor_class_code);
85
86 EXCEPTION WHEN NO_DATA_FOUND THEN
87 arp_file.write_log('Address Id invalid '||
88 trx_lines_rec.orig_system_address_id,0);
89 IF NOT JG_ZZ_AUTO_INVOICE.put_error_message ('JL',
90 'JL_ZZ_AR_INVALID_ADDRESS',
91 trx_lines_rec.interface_line_attribute1,
92 'JLERRUBS') THEN
93 RAISE error_condition;
94 END IF;
95 END;
96 IF l_contributor_class_code is null then
97 IF NOT JG_ZZ_AUTO_INVOICE.put_error_message ('JL',
98 'JL_AR_AR_AI_CONT_CLS_NOT_DEF',
99 trx_lines_rec.interface_line_attribute1,
100 'JLERRUBS') THEN
101 RAISE error_condition;
102 END IF;
103 END IF;
104
105 -- Get Organization class code
106 l_org_id := mo_global.get_current_org_id;
107 l_so_org_id := oe_sys_parameters.value('MASTER_ORGANIZATION_ID',l_org_id);
108
109 BEGIN
110 SELECT hrl.global_attribute1
111 INTO l_organization_class_code
112 FROM hr_locations hrl,
113 hr_organization_units hrou
114 WHERE hrou.organization_id = l_so_org_id
115 AND hrl.location_id = hrou.location_id;
116
117 arp_file.write_log('Organization class code '||l_organization_class_code,0);
118
119 EXCEPTION WHEN NO_DATA_FOUND THEN
120 arp_file.write_log('Inv Org Id invalid '|| l_so_org_id,0);
121 IF NOT JG_ZZ_AUTO_INVOICE.put_error_message ('JL',
122 'JL_AR_AR_INVALID_ORGANIZATION',
123 trx_lines_rec.interface_line_attribute1,
124 'JLERRUBS') THEN
125 RAISE error_condition;
126 END IF;
127 END;
128
129 IF l_organization_class_code IS NULL THEN
130 IF NOT JG_ZZ_AUTO_INVOICE.put_error_message ('JL',
131 'JL_AR_AR_AI_ORG_CLS_NOT_DEF',
132 trx_lines_rec.interface_line_attribute1,
133 'JLERRUBS') THEN
134 RAISE error_condition;
135 END IF;
136 END IF;
137
138 -- Get VAT tax category from document letter table
139 BEGIN
140 SELECT distinct tax_category_id
141 INTO l_tax_category_id
142 FROM jl_ar_ar_doc_letter;
143
144 EXCEPTION WHEN NO_DATA_FOUND THEN
145 arp_file.write_log('Document letter not set up',0);
146 IF NOT JG_ZZ_AUTO_INVOICE.put_error_message ('JL',
147 'JL_AR_AR_DOC_LET_NOT_FOUND ',
148 trx_lines_rec.interface_line_attribute1,
149 'JLERRUBS') THEN
150 RAISE error_condition;
151 END IF;
152 END;
153
154 -- Get condition and value for organization class code and tax category
155 IF l_organization_class_code IS NOT NULL AND
156 l_tax_category_id IS NOT NULL THEN
157 BEGIN
158 SELECT tax_attribute_name, tax_attribute_value
159 INTO l_org_attribute_name, l_org_attribute_value
160 FROM jl_zz_ar_tx_att_cls cls, jl_zz_ar_tx_categ_all cat
161 WHERE cls.tax_attr_class_type = 'ORGANIZATION_CLASS'
162 AND cls.tax_attr_class_code = l_organization_class_code
163 AND cls.tax_category_id = l_tax_category_id
164 AND cls.tax_category_id = cat.tax_category_id
165 AND cls.tax_attribute_name = cat.org_tax_attribute;
166
167 arp_file.write_log('Organization condition '||l_org_attribute_name,0);
168 arp_file.write_log('Organization condition value '||l_org_attribute_value,0);
169
170 EXCEPTION WHEN NO_DATA_FOUND THEN
171 arp_file.write_log('Organization attributes not found',0);
172 IF NOT JG_ZZ_AUTO_INVOICE.put_error_message ('JL',
173 'JL_AR_AR_AI_ATT_CLS_NOT_DEF',
174 trx_lines_rec.interface_line_attribute1,
175 'JLERRUBS') THEN
176 RAISE error_condition;
177 END IF;
178
179 END;
180 END IF;
181
182 -- Get condition and value for contributor class code and tax category
183 IF l_contributor_class_code IS NOT NULL AND
184 l_tax_category_id IS NOT NULL THEN
185 SELECT nvl(cas.global_attribute9,'N')
186 INTO l_cus_cls_flag
187 FROM hz_cust_acct_sites cas
188 WHERE cas.cust_acct_site_id = trx_lines_rec.orig_system_address_id;
189 IF l_cus_cls_flag = 'Y' THEN
190 BEGIN
191 SELECT tax_attribute_name, tax_attribute_value
192 INTO l_cust_attribute_name, l_cust_attribute_value
193 FROM jl_zz_ar_tx_cus_cls cus, jl_zz_ar_tx_categ_all cat
194 WHERE cus.tax_attr_class_code = l_contributor_class_code
195 AND cus.address_id = trx_lines_rec.orig_system_address_id
196 AND cus.tax_category_id = l_tax_category_id
197 AND cus.tax_category_id = cat.tax_category_id
198 AND cus.tax_attribute_name = cat.cus_tax_attribute;
199
200 arp_file.write_log('Contributor condition '||l_cust_attribute_name,0);
201 arp_file.write_log('Contributor condition value '||l_cust_attribute_value,0);
202
203 EXCEPTION WHEN NO_DATA_FOUND THEN
204 arp_file.write_log('Contributor condition not found ',0);
205 IF NOT JG_ZZ_AUTO_INVOICE.put_error_message ('JL',
206 'JL_AR_AR_AI_CU_SIT_PRO_NOT_DEF',
207 trx_lines_rec.interface_line_attribute1,
208 'JLERRUBS') THEN
209 RAISE error_condition;
210 END IF;
211 END;
212 ELSE
213 BEGIN
214 SELECT tax_attribute_name, tax_attribute_value
215 INTO l_cust_attribute_name, l_cust_attribute_value
216 FROM jl_zz_ar_tx_att_cls att, jl_zz_ar_tx_categ_all cat
217 WHERE att.tax_attr_class_type = 'CONTRIBUTOR_CLASS'
218 AND att.tax_attr_class_code = l_contributor_class_code
219 AND att.tax_category_id = l_tax_category_id
220 AND att.tax_attribute_name = cat.cus_tax_attribute
221 AND att.tax_category_id = cat.tax_category_id;
222
223 arp_file.write_log('Contributor condition '||l_cust_attribute_name,0);
224 arp_file.write_log('Contributor condition value '||l_cust_attribute_value,0);
225
226 EXCEPTION WHEN NO_DATA_FOUND THEN
227 arp_file.write_log('Contributor condition not found ',0);
228 IF NOT JG_ZZ_AUTO_INVOICE.put_error_message ('JL',
229 'JL_AR_AR_AI_CU_SIT_PRO_NOT_DEF',
230 trx_lines_rec.interface_line_attribute1,
231 'JLERRUBS') THEN
232 RAISE error_condition;
233 END IF;
234 END;
235
236 END IF;
237 END IF;
238
239 -- Get document letter for Organization and contributor conditions
240 -- and values and tax category
241 IF l_org_attribute_name IS NOT NULL AND l_org_attribute_value IS NOT NULL AND
242 l_cust_attribute_name IS NOT NULL AND l_cust_attribute_value IS NOT NULL THEN
243 BEGIN
244 SELECT document_letter
245 INTO l_document_letter
246 FROM jl_ar_ar_doc_letter
247 WHERE tax_category_id = l_tax_category_id
248 AND org_tax_attribute_name = l_org_attribute_name
249 AND org_tax_attribute_value = l_org_attribute_value
250 AND con_tax_attribute_name = l_cust_attribute_name
251 AND con_tax_attribute_value = l_cust_attribute_value
252 AND nvl(trx_lines_rec.trx_date, p_default_date) BETWEEN start_date_active AND end_date_active;
253 arp_file.write_log('Document letter is '||l_document_letter,0);
254
255 EXCEPTION WHEN NO_DATA_FOUND THEN
256 arp_file.write_log('Document letter not found ',0);
257 IF NOT JG_ZZ_AUTO_INVOICE.put_error_message ('JL',
258 'JL_AR_AR_DOC_LET_NOT_FOUND',
259 trx_lines_rec.interface_line_attribute1,
260 'JLERRUBS') THEN
261 RAISE error_condition;
262 END IF;
263 END;
264 END IF;
265
266 -- Get batch source using letter and transaction type
267 IF l_document_letter IS NOT NULL THEN
268 BEGIN
269 SELECT ty.batch_source_id
270 INTO l_batch_source_id
271 FROM jg_zz_ar_src_trx_ty ty, ra_batch_sources_all src
272 WHERE ty.cust_trx_type_id = trx_lines_rec.cust_trx_type_id
273 AND ty.batch_source_id = src.batch_source_id
274 AND src.global_attribute3 = l_document_letter
275 AND ty.enable_flag = 'Y';
276
277 arp_file.write_log('Correct Batch source id is '||l_batch_source_id,0);
278
279 EXCEPTION WHEN NO_DATA_FOUND THEN
280 arp_file.write_log('Batch source not found',0);
281 IF NOT JG_ZZ_AUTO_INVOICE.put_error_message ('JL',
282 'JL_AR_AR_BT_SRC_NOT_FOUND',
283 trx_lines_rec.interface_line_attribute1,
284 'JLERRUBS') THEN
285 RAISE error_condition;
286 END IF;
287 WHEN TOO_MANY_ROWS THEN
288 arp_file.write_log('More than one batch source found',0);
289 IF NOT JG_ZZ_AUTO_INVOICE.put_error_message ('JL',
290 'JL_AR_AR_BT_SRC_MORE_FOUND',
291 trx_lines_rec.interface_line_attribute1,
292 'JLERRUBS') THEN
293 RAISE error_condition;
294 END IF;
295 END;
296 END IF;
297
298 BEGIN
299 IF l_batch_source_id IS NOT NULL
300 THEN
301 SELECT name
302 INTO l_new_batch_source_name
303 FROM ra_batch_sources
304 WHERE batch_source_id = l_batch_source_id;
305 END IF;
306
307 END;
308
309 IF nvl(l_new_batch_source_name,trx_lines_rec.batch_source_name) <> trx_lines_rec.batch_source_name THEN
310 UPDATE ra_interface_lines
311 SET batch_source_name = l_new_batch_source_name
312 WHERE rowid = trx_lines_rec.rowid;
313 INSERT INTO RA_INTERFACE_ERRORS(INTERFACE_LINE_ID, MESSAGE_TEXT, INVALID_VALUE, ORG_ID)
314 VALUES(trx_lines_rec.interface_line_attribute1,
315 'Original batch source : '||trx_lines_rec.batch_source_name||
316 'is updated with new batch source :'||l_new_batch_source_name,
317 'JLUPDUBS',
318 trx_lines_rec.org_id);
319 arp_file.write_log('Updated old batch source '||trx_lines_rec.batch_source_name ||
320 'to '|| l_new_batch_source_name,0);
321 END IF;
322
323 END LOOP;
324 COMMIT;
325
326 EXCEPTION
327 WHEN OTHERS THEN
328 arp_file.write_log(sqlerrm,0);
329 END;
330
331 PROCEDURE JL_AR_AR_UPDATE_BATCH_SOURCE (
332 errbuf OUT NOCOPY varchar2,
333 retcode OUT NOCOPY number,
334 p_low_gl_date IN VARCHAR2 ,
335 p_high_gl_date IN VARCHAR2 ,
336 p_low_ship_date IN VARCHAR2 ,
337 p_high_ship_date IN VARCHAR2,
338 p_low_invoice_date IN VARCHAR2 ,
339 p_high_invoice_date IN VARCHAR2,
340 p_default_date IN VARCHAR2) IS
341 X_req_id NUMBER(38);
342 p_low_gl_dt DATE;
343 p_high_gl_dt DATE;
344 p_low_ship_dt DATE;
345 p_high_ship_dt DATE;
346 p_low_invoice_dt DATE;
347 p_high_invoice_dt DATE;
348 p_default_dt DATE;
349 BEGIN
350 arp_file.write_log('Calling Update Batch source',0);
351
352 p_low_gl_dt := fnd_date.canonical_to_date(p_low_gl_date);
353 p_high_gl_dt := fnd_date.canonical_to_date(p_high_gl_date);
357 p_high_invoice_dt := fnd_date.canonical_to_date(p_high_invoice_date);
354 p_low_ship_dt := fnd_date.canonical_to_date(p_low_ship_date);
355 p_high_ship_dt := fnd_date.canonical_to_date(p_high_ship_date);
356 p_low_invoice_dt := fnd_date.canonical_to_date(p_low_invoice_date);
358 p_default_dt := fnd_date.canonical_to_date(p_default_date);
359
360 arp_file.write_log('low gl'||p_low_gl_date,0);
361 arp_file.write_log('high gl'||p_high_gl_date,0);
362 arp_file.write_log('l ship'||p_low_ship_date,0);
363 arp_file.write_log('h ship'||p_high_ship_date,0);
364 arp_file.write_log('l invoice'||p_low_invoice_date,0);
365 arp_file.write_log('h invoice'||p_high_invoice_date,0);
366 arp_file.write_log('default'||p_default_date,0);
367
368 UPDATE_BATCH_SOURCE(p_low_invoice_dt, p_high_invoice_dt,
369 p_low_gl_dt, p_high_gl_dt,
370 p_low_ship_dt, p_high_ship_dt, p_default_dt);
371
372 arp_file.write_log('After update batch source');
373 -- Call to the Batch Source Update error report
374 X_req_id := FND_REQUEST.SUBMIT_REQUEST(
375 'JL' ,
376 'JLARRERR',
377 'Argentine Autoinvoice Batch Source Update Error Report',
378 SYSDATE,
379 FALSE);
380 EXCEPTION
381 WHEN OTHERS THEN
382 arp_file.write_log(sqlerrm,0);
383
384 END JL_AR_AR_UPDATE_BATCH_SOURCE;
385
386
387 PROCEDURE submit_request (
388 errbuf OUT NOCOPY varchar2,
389 retcode OUT NOCOPY number,
390 p_parallel_module_name IN varchar2,
391 p_running_mode IN varchar2,
392 p_batch_source_id IN ra_batch_sources.batch_source_id%TYPE,
393 p_batch_source_name IN varchar2,
394 p_default_date IN varchar2,
395 p_trans_flexfield IN varchar2,
396 p_trans_type IN ra_cust_trx_types.name%TYPE,
397 p_low_bill_to_cust_num IN hz_cust_accounts.account_number%TYPE ,
398 p_high_bill_to_cust_num IN hz_cust_accounts.account_number%TYPE ,
399 p_low_bill_to_cust_name IN hz_parties.party_name%TYPE ,
400 p_high_bill_to_cust_name IN hz_parties.party_name%TYPE ,
401 p_low_gl_date IN VARCHAR2 ,
402 p_high_gl_date IN VARCHAR2 ,
403 p_low_ship_date IN VARCHAR2,
404 p_high_ship_date IN VARCHAR2,
405 p_low_trans_number IN ra_interface_lines.trx_number%TYPE,
406 p_high_trans_number IN ra_interface_lines.trx_number%TYPE ,
407 p_low_sales_order_num IN ra_interface_lines.sales_order%TYPE ,
408 p_high_sales_order_num IN ra_interface_lines.sales_order%TYPE,
409 p_low_invoice_date IN VARCHAR2 ,
410 p_high_invoice_date IN VARCHAR2 ,
411 p_low_ship_to_cust_num IN hz_cust_accounts.account_number%TYPE ,
412 p_high_ship_to_cust_num IN hz_cust_accounts.account_number%TYPE ,
413 p_low_ship_to_cust_name IN hz_parties.party_name%TYPE ,
414 p_high_ship_to_cust_name IN hz_parties.party_name%TYPE,
415 p_call_from_master_flag IN varchar2 ,
416 p_base_due_date_on_trx_date IN fnd_lookups.meaning%TYPE ,
417 p_due_date_adj_days IN number ) IS
418
419 X_req_id NUMBER(38);
420 call_status BOOLEAN;
421 rphase VARCHAR2(30);
422 rstatus VARCHAR2(30);
423 dphase VARCHAR2(30);
424 dstatus VARCHAR2(30);
425 message VARCHAR2(240);
426 l_org_id NUMBER;
427
428 BEGIN
429 arp_file.write_log('JL Submitting Autoinvoice',0);
430
431 -- Bug#7642995 Start
432 l_org_id := mo_global.get_current_org_id;
433
434 arp_file.write_log(l_org_id,0);
435
436 fnd_request.set_org_id(l_org_id);
437
438 -- Bug#7642995 End
439
440 X_req_id := FND_REQUEST.SUBMIT_REQUEST(
441 'AR' ,
442 'RAXTRX',
443 'Autoinvoice Import Program',
444 SYSDATE,
445 FALSE,
446 p_parallel_module_name,
447 p_running_mode,
448 p_batch_source_id,
449 p_batch_source_name,
450 p_default_date,
451 p_trans_flexfield,
452 p_trans_type,
453 p_low_bill_to_cust_num,
454 p_high_bill_to_cust_num,
455 p_low_bill_to_cust_name,
456 p_high_bill_to_cust_name,
457 p_low_gl_date,
458 p_high_gl_date,
459 p_low_ship_date,
460 p_high_ship_date,
461 p_low_trans_number,
462 p_high_trans_number,
463 p_low_sales_order_num,
464 p_high_sales_order_num,
465 p_low_invoice_date,
466 p_high_invoice_date,
467 p_low_ship_to_cust_num,
468 p_high_ship_to_cust_num,
469 p_low_ship_to_cust_name,
470 p_high_ship_to_cust_name,
471 p_call_from_master_flag,
472 p_base_due_date_on_trx_date,
473 p_due_date_adj_days,
474 l_org_id);
475
476 END SUBMIT_REQUEST;
477
478 END JL_AR_AUTOINV_PKG;