4 PROCEDURE UPDATE_BATCH_SOURCE(p_invoice_date_from IN DATE,
1 package body JL_AR_AUTOINV_PKG as
2 /* $Header: jlarranb.pls 120.8.12020000.3 2013/03/11 16:23:04 abuissa ship $ */
3
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,
36 batch_source_name,
33 cust_trx_type_id,
34 nvl(orig_system_ship_address_id,
35 orig_system_bill_address_id) orig_system_address_id,
37 trx_date,
38 nvl(org_id, -99) org_id --bug 16471013
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_message1 ('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_message1 ('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 -- Bug 16471013 - Start
110 arp_file.write_log('Master Org='||l_so_org_id,0);
111 arp_file.write_log('Current Org='||l_org_id,0);
112 arp_file.write_log('Interface Org='||trx_lines_rec.org_id,0);
113 arp_file.write_log('Searching for Org Class Code in Org_ID='||trx_lines_rec.org_id,0);
114
115
116 BEGIN
117 SELECT hrl.global_attribute1
118 INTO l_organization_class_code
119 FROM hr_locations hrl,
120 hr_organization_units hrou
121 WHERE hrou.organization_id = trx_lines_rec.org_id
122 AND hrl.location_id = hrou.location_id;
123
124 arp_file.write_log('Organization class code '||l_organization_class_code,0);
125
126 EXCEPTION WHEN NO_DATA_FOUND THEN
127 arp_file.write_log('No Org Class Code for ORG_ID '|| trx_lines_rec.org_id,0);
128 END;
129
130 IF l_organization_class_code IS NULL THEN
131 arp_file.write_log('Searching for Org Class Code in Org_ID='||l_so_org_id,0);
132 BEGIN
133 SELECT hrl.global_attribute1
134 INTO l_organization_class_code
135 FROM hr_locations hrl,
136 hr_organization_units hrou
137 WHERE hrou.organization_id = l_so_org_id
138 AND hrl.location_id = hrou.location_id;
139
140 arp_file.write_log('Organization class code '||l_organization_class_code,0);
141
142 EXCEPTION WHEN NO_DATA_FOUND THEN
143 arp_file.write_log('No Org Class Code for SO_ORGANIZATION_ID '|| l_so_org_id,0);
144 IF NOT JG_ZZ_AUTO_INVOICE.put_error_message1 ('JL',
145 'JL_AR_AR_INVALID_ORGANIZATION',
146 trx_lines_rec.interface_line_attribute1,
147 'JLERRUBS') THEN
148 RAISE error_condition;
149 END IF;
150 END;
151 END IF;
152 -- Bug 16471013 - End
153
154 IF l_organization_class_code IS NULL THEN
155 IF NOT JG_ZZ_AUTO_INVOICE.put_error_message1 ('JL',
156 'JL_AR_AR_AI_ORG_CLS_NOT_DEF',
157 trx_lines_rec.interface_line_attribute1,
158 'JLERRUBS') THEN
162
159 RAISE error_condition;
160 END IF;
161 END IF;
163 -- Get VAT tax category from document letter table
164 BEGIN
165 SELECT distinct tax_category_id
166 INTO l_tax_category_id
167 FROM jl_ar_ar_doc_letter;
168
169 EXCEPTION WHEN NO_DATA_FOUND THEN
170 arp_file.write_log('Document letter not set up',0);
171 IF NOT JG_ZZ_AUTO_INVOICE.put_error_message1 ('JL',
172 'JL_AR_AR_DOC_LET_NOT_FOUND ',
173 trx_lines_rec.interface_line_attribute1,
174 'JLERRUBS') THEN
175 RAISE error_condition;
176 END IF;
177 END;
178
179 -- Get condition and value for organization class code and tax category
180 IF l_organization_class_code IS NOT NULL AND
181 l_tax_category_id IS NOT NULL THEN
182 BEGIN
183 SELECT tax_attribute_name, tax_attribute_value
184 INTO l_org_attribute_name, l_org_attribute_value
185 FROM jl_zz_ar_tx_att_cls cls, jl_zz_ar_tx_categ cat
186 WHERE cls.tax_attr_class_type = 'ORGANIZATION_CLASS'
187 AND cls.tax_attr_class_code = l_organization_class_code
188 AND cls.tax_category_id = l_tax_category_id
189 AND cls.tax_category_id = cat.tax_category_id
190 AND cls.tax_attribute_name = cat.org_tax_attribute;
191
192 arp_file.write_log('Organization condition '||l_org_attribute_name,0);
193 arp_file.write_log('Organization condition value '||l_org_attribute_value,0);
194
195 EXCEPTION WHEN NO_DATA_FOUND THEN
196 arp_file.write_log('Organization attributes not found',0);
197 IF NOT JG_ZZ_AUTO_INVOICE.put_error_message1 ('JL',
198 'JL_AR_AR_AI_ATT_CLS_NOT_DEF',
199 trx_lines_rec.interface_line_attribute1,
200 'JLERRUBS') THEN
201 RAISE error_condition;
202 END IF;
203
204 END;
205 END IF;
206
207 -- Get condition and value for contributor class code and tax category
208 IF l_contributor_class_code IS NOT NULL AND
209 l_tax_category_id IS NOT NULL THEN
210 SELECT nvl(cas.global_attribute9,'N')
211 INTO l_cus_cls_flag
212 FROM hz_cust_acct_sites cas
213 WHERE cas.cust_acct_site_id = trx_lines_rec.orig_system_address_id;
214 IF l_cus_cls_flag = 'Y' THEN
215 BEGIN
216 SELECT tax_attribute_name, tax_attribute_value
217 INTO l_cust_attribute_name, l_cust_attribute_value
218 FROM jl_zz_ar_tx_cus_cls cus, jl_zz_ar_tx_categ cat
219 WHERE cus.tax_attr_class_code = l_contributor_class_code
220 AND cus.address_id = trx_lines_rec.orig_system_address_id
221 AND cus.tax_category_id = l_tax_category_id
222 AND cus.tax_category_id = cat.tax_category_id
223 AND cus.tax_attribute_name = cat.cus_tax_attribute;
224
225 arp_file.write_log('Contributor condition '||l_cust_attribute_name,0);
226 arp_file.write_log('Contributor condition value '||l_cust_attribute_value,0);
227
228 EXCEPTION WHEN NO_DATA_FOUND THEN
229 arp_file.write_log('Contributor condition not found ',0);
230 IF NOT JG_ZZ_AUTO_INVOICE.put_error_message1 ('JL',
231 'JL_AR_AR_AI_CU_SIT_PRO_NOT_DEF',
232 trx_lines_rec.interface_line_attribute1,
233 'JLERRUBS') THEN
234 RAISE error_condition;
235 END IF;
236 END;
237 ELSE
238 BEGIN
239 SELECT tax_attribute_name, tax_attribute_value
240 INTO l_cust_attribute_name, l_cust_attribute_value
241 FROM jl_zz_ar_tx_att_cls att, jl_zz_ar_tx_categ cat
242 WHERE att.tax_attr_class_type = 'CONTRIBUTOR_CLASS'
243 AND att.tax_attr_class_code = l_contributor_class_code
244 AND att.tax_category_id = l_tax_category_id
245 AND att.tax_attribute_name = cat.cus_tax_attribute
246 AND att.tax_category_id = cat.tax_category_id;
247
248 arp_file.write_log('Contributor condition '||l_cust_attribute_name,0);
249 arp_file.write_log('Contributor condition value '||l_cust_attribute_value,0);
250
251 EXCEPTION WHEN NO_DATA_FOUND THEN
252 arp_file.write_log('Contributor condition not found ',0);
253 IF NOT JG_ZZ_AUTO_INVOICE.put_error_message1 ('JL',
254 'JL_AR_AR_AI_CU_SIT_PRO_NOT_DEF',
255 trx_lines_rec.interface_line_attribute1,
256 'JLERRUBS') THEN
257 RAISE error_condition;
258 END IF;
259 END;
260
261 END IF;
262 END IF;
263
264 -- Get document letter for Organization and contributor conditions
265 -- and values and tax category
266 IF l_org_attribute_name IS NOT NULL AND l_org_attribute_value IS NOT NULL AND
267 l_cust_attribute_name IS NOT NULL AND l_cust_attribute_value IS NOT NULL THEN
268 BEGIN
269 SELECT document_letter
270 INTO l_document_letter
271 FROM jl_ar_ar_doc_letter
272 WHERE tax_category_id = l_tax_category_id
273 AND org_tax_attribute_name = l_org_attribute_name
274 AND org_tax_attribute_value = l_org_attribute_value
275 AND con_tax_attribute_name = l_cust_attribute_name
279
276 AND con_tax_attribute_value = l_cust_attribute_value
277 AND nvl(trx_lines_rec.trx_date, p_default_date) BETWEEN start_date_active AND end_date_active;
278 arp_file.write_log('Document letter is '||l_document_letter,0);
280 EXCEPTION WHEN NO_DATA_FOUND THEN
281 arp_file.write_log('Document letter not found ',0);
282 IF NOT JG_ZZ_AUTO_INVOICE.put_error_message1 ('JL',
283 'JL_AR_AR_DOC_LET_NOT_FOUND',
284 trx_lines_rec.interface_line_attribute1,
285 'JLERRUBS') THEN
286 RAISE error_condition;
287 END IF;
288 END;
289 END IF;
290
291 -- Get batch source using letter and transaction type
292 IF l_document_letter IS NOT NULL THEN
293 BEGIN
294 SELECT ty.batch_source_id
295 INTO l_batch_source_id
296 FROM jg_zz_ar_src_trx_ty ty, ra_batch_sources src
297 WHERE ty.cust_trx_type_id = trx_lines_rec.cust_trx_type_id
298 AND ty.batch_source_id = src.batch_source_id
299 AND src.global_attribute3 = l_document_letter
300 AND ty.enable_flag = 'Y';
301
302 arp_file.write_log('Correct Batch source id is '||l_batch_source_id,0);
303
304 EXCEPTION WHEN NO_DATA_FOUND THEN
305 arp_file.write_log('Batch source not found',0);
306 IF NOT JG_ZZ_AUTO_INVOICE.put_error_message1 ('JL',
307 'JL_AR_AR_BT_SRC_NOT_FOUND',
308 trx_lines_rec.interface_line_attribute1,
309 'JLERRUBS') THEN
310 RAISE error_condition;
311 END IF;
312 WHEN TOO_MANY_ROWS THEN
313 arp_file.write_log('More than one batch source found',0);
314 IF NOT JG_ZZ_AUTO_INVOICE.put_error_message1 ('JL',
315 'JL_AR_AR_BT_SRC_MORE_FOUND',
316 trx_lines_rec.interface_line_attribute1,
317 'JLERRUBS') THEN
318 RAISE error_condition;
319 END IF;
320 END;
321 END IF;
322
323 BEGIN
324 IF l_batch_source_id IS NOT NULL
325 THEN
326 SELECT name
327 INTO l_new_batch_source_name
328 FROM ra_batch_sources
329 WHERE batch_source_id = l_batch_source_id;
330 END IF;
331
332 END;
333
334 IF nvl(l_new_batch_source_name,trx_lines_rec.batch_source_name) <> trx_lines_rec.batch_source_name THEN
335 UPDATE ra_interface_lines
336 SET batch_source_name = l_new_batch_source_name
337 WHERE rowid = trx_lines_rec.rowid;
338 INSERT INTO JL_AUTOINV_INT_LINES(INTERFACE_LINE_REF, MESSAGE_TEXT, INVALID_VALUE, ORG_ID)
339 VALUES(trx_lines_rec.interface_line_attribute1,
340 'Original batch source : '||trx_lines_rec.batch_source_name||
341 'is updated with new batch source :'||l_new_batch_source_name,
342 'JLUPDUBS',
343 trx_lines_rec.org_id);
344 arp_file.write_log('Updated old batch source '||trx_lines_rec.batch_source_name ||
345 'to '|| l_new_batch_source_name,0);
346 END IF;
347
348 END LOOP;
349 COMMIT;
350
351 EXCEPTION
352 WHEN OTHERS THEN
353 arp_file.write_log(sqlerrm,0);
354 END;
355
356 PROCEDURE JL_AR_AR_UPDATE_BATCH_SOURCE (
357 errbuf OUT NOCOPY varchar2,
358 retcode OUT NOCOPY number,
359 p_low_gl_date IN VARCHAR2 ,
360 p_high_gl_date IN VARCHAR2 ,
361 p_low_ship_date IN VARCHAR2 ,
362 p_high_ship_date IN VARCHAR2,
363 p_low_invoice_date IN VARCHAR2 ,
364 p_high_invoice_date IN VARCHAR2,
365 p_default_date IN VARCHAR2) IS
366 X_req_id NUMBER(38);
367 p_low_gl_dt DATE;
368 p_high_gl_dt DATE;
369 p_low_ship_dt DATE;
370 p_high_ship_dt DATE;
371 p_low_invoice_dt DATE;
372 p_high_invoice_dt DATE;
373 p_default_dt DATE;
374 BEGIN
375 arp_file.write_log('Calling Update Batch source',0);
376
377 p_low_gl_dt := fnd_date.canonical_to_date(p_low_gl_date);
378 p_high_gl_dt := fnd_date.canonical_to_date(p_high_gl_date);
379 p_low_ship_dt := fnd_date.canonical_to_date(p_low_ship_date);
380 p_high_ship_dt := fnd_date.canonical_to_date(p_high_ship_date);
381 p_low_invoice_dt := fnd_date.canonical_to_date(p_low_invoice_date);
382 p_high_invoice_dt := fnd_date.canonical_to_date(p_high_invoice_date);
383 p_default_dt := fnd_date.canonical_to_date(p_default_date);
384
385 arp_file.write_log('low gl'||p_low_gl_date,0);
386 arp_file.write_log('high gl'||p_high_gl_date,0);
387 arp_file.write_log('l ship'||p_low_ship_date,0);
388 arp_file.write_log('h ship'||p_high_ship_date,0);
389 arp_file.write_log('l invoice'||p_low_invoice_date,0);
390 arp_file.write_log('h invoice'||p_high_invoice_date,0);
391 arp_file.write_log('default'||p_default_date,0);
392
393 UPDATE_BATCH_SOURCE(p_low_invoice_dt, p_high_invoice_dt,
394 p_low_gl_dt, p_high_gl_dt,
395 p_low_ship_dt, p_high_ship_dt, p_default_dt);
396
397 arp_file.write_log('After update batch source');
398 -- Call to the Batch Source Update error report
399 X_req_id := FND_REQUEST.SUBMIT_REQUEST(
400 'JL' ,
401 'JLARRERR',
402 'Argentine Autoinvoice Batch Source Update Error Report',
403 SYSDATE,
404 FALSE);
405 EXCEPTION
406 WHEN OTHERS THEN
410
407 arp_file.write_log(sqlerrm,0);
408
409 END JL_AR_AR_UPDATE_BATCH_SOURCE;
411
412 PROCEDURE submit_request (
413 errbuf OUT NOCOPY varchar2,
414 retcode OUT NOCOPY number,
415 p_parallel_module_name IN varchar2,
416 p_running_mode IN varchar2,
417 p_batch_source_id IN ra_batch_sources.batch_source_id%TYPE,
418 p_batch_source_name IN varchar2,
419 p_default_date IN varchar2,
420 p_trans_flexfield IN varchar2,
421 p_trans_type IN ra_cust_trx_types.name%TYPE,
422 p_low_bill_to_cust_num IN hz_cust_accounts.account_number%TYPE ,
423 p_high_bill_to_cust_num IN hz_cust_accounts.account_number%TYPE ,
424 p_low_bill_to_cust_name IN hz_parties.party_name%TYPE ,
425 p_high_bill_to_cust_name IN hz_parties.party_name%TYPE ,
426 p_low_gl_date IN VARCHAR2 ,
427 p_high_gl_date IN VARCHAR2 ,
428 p_low_ship_date IN VARCHAR2,
429 p_high_ship_date IN VARCHAR2,
430 p_low_trans_number IN ra_interface_lines.trx_number%TYPE,
431 p_high_trans_number IN ra_interface_lines.trx_number%TYPE ,
432 p_low_sales_order_num IN ra_interface_lines.sales_order%TYPE ,
433 p_high_sales_order_num IN ra_interface_lines.sales_order%TYPE,
434 p_low_invoice_date IN VARCHAR2 ,
435 p_high_invoice_date IN VARCHAR2 ,
436 p_low_ship_to_cust_num IN hz_cust_accounts.account_number%TYPE ,
437 p_high_ship_to_cust_num IN hz_cust_accounts.account_number%TYPE ,
438 p_low_ship_to_cust_name IN hz_parties.party_name%TYPE ,
439 p_high_ship_to_cust_name IN hz_parties.party_name%TYPE,
440 p_call_from_master_flag IN varchar2 ,
441 p_base_due_date_on_trx_date IN fnd_lookups.meaning%TYPE ,
442 p_due_date_adj_days IN number ) IS
443
444 X_req_id NUMBER(38);
445 call_status BOOLEAN;
446 rphase VARCHAR2(30);
447 rstatus VARCHAR2(30);
448 dphase VARCHAR2(30);
449 dstatus VARCHAR2(30);
450 message VARCHAR2(240);
451 l_org_id NUMBER;
452
453 BEGIN
454 arp_file.write_log('JL Submitting Autoinvoice',0);
455
456 -- Bug#7642995 Start
457 l_org_id := mo_global.get_current_org_id;
458
459 arp_file.write_log(l_org_id,0);
460
461 fnd_request.set_org_id(l_org_id);
462
463 -- Bug#7642995 End
464
465 X_req_id := FND_REQUEST.SUBMIT_REQUEST(
466 'AR' ,
467 'RAXTRX',
468 'Autoinvoice Import Program',
469 SYSDATE,
470 FALSE,
471 p_parallel_module_name,
472 p_running_mode,
473 p_batch_source_id,
474 p_batch_source_name,
475 p_default_date,
476 p_trans_flexfield,
477 p_trans_type,
478 p_low_bill_to_cust_num,
479 p_high_bill_to_cust_num,
480 p_low_bill_to_cust_name,
481 p_high_bill_to_cust_name,
482 p_low_gl_date,
483 p_high_gl_date,
484 p_low_ship_date,
485 p_high_ship_date,
486 p_low_trans_number,
487 p_high_trans_number,
488 p_low_sales_order_num,
489 p_high_sales_order_num,
490 p_low_invoice_date,
491 p_high_invoice_date,
492 p_low_ship_to_cust_num,
493 p_high_ship_to_cust_num,
494 p_low_ship_to_cust_name,
495 p_high_ship_to_cust_name,
496 p_call_from_master_flag,
497 p_base_due_date_on_trx_date,
498 p_due_date_adj_days,
499 l_org_id);
500
501 END SUBMIT_REQUEST;
502
503 END JL_AR_AUTOINV_PKG;
[Home] [Help]