[Home] [Help]
PACKAGE BODY: APPS.JERX_TO
Source
1 PACKAGE BODY JERX_TO AS
2 /* $Header: jegrtob.pls 120.9 2008/05/29 12:32:44 pakumare ship $ */
3
4
5 PROCEDURE JE_AP_TURNOVER_EXTRACT(
6 errbuf OUT NOCOPY VARCHAR2,
7 retcode OUT NOCOPY NUMBER,
8 p_app_short_name in varchar2,
9 p_set_of_books_id in varchar2,
10 p_period_start_date in varchar2,
11 p_period_end_date in varchar2,
12 p_range_type in varchar2,
13 p_cs_name_from in varchar2,
14 p_cs_name_to in varchar2,
15 p_cs_number_from in varchar2,
16 p_cs_number_to in varchar2,
17 p_currency_code in varchar2,
18 p_rule_id in varchar2,
19 p_inv_amount_limit in varchar2,
20 p_balance_type in varchar2,
21 p_request_id in number,
22 p_legal_entity_id in number)
23
24 IS
25
26
27 /* This cursor selects all the records needed to populate the interface table. */
28
29 CURSOR c_vendor_turnover IS
30 SELECT
31 p_request_id ap_request_id,
32 substr(pv.vendor_name,1,80) vendor_name,
33 pv.segment1 segment1,
34 nvl(papf.national_identifier,nvl(pv.individual_1099,pv.num_1099)) num_1099,
35 pv.vat_registration_num vat_registration_number,
36 pvs.vendor_site_code vendor_site_code,
37 pv.standard_industry_class standard_industry_class,
38 pvs.address_line1 address_line1,
39 pvs.address_line2 address_line2,
40 pvs.address_line3 address_line3,
41 pvs.city city,
42 pvs.state state,
43 pvs.zip zip,
44 pvs.province province,
45 pvs.country country,
46 ai.invoice_num invoice_num,
47 ai.invoice_id invoice_id,
48 ai.invoice_date invoice_date,
49 ai.invoice_currency_code invoice_currency_code,
50 sum(nvl(aid.amount,0)) distribution_total,
51 ai.invoice_type_lookup_code invoice_type_lookup_code,
52 sum(nvl(aid.base_amount,0)) distribution_base_total,
53 decode(nvl(pv.global_attribute1,'N'),'Y','PUBLIC SECTOR COMPANIES',
54 pv.vendor_type_lookup_code) vendor_type_lookup_code,
55 0 created_by,
56 sysdate creation_date,
57 sysdate last_update_date,
58 0 last_updated_by,
59 NULL last_update_login
60 FROM
61 po_vendors pv,
62 (SELECT distinct person_id
63 ,national_identifier
64 FROM PER_ALL_PEOPLE_F) papf,
65 po_vendor_sites_all pvs,
66 ap_invoices_all ai,
67 ap_invoice_distributions_all aid
68 WHERE
69 pv.vendor_id = ai.vendor_id
70 and nvl(pv.employee_id, -99) = papf.person_id (+)
71 and ai.vendor_site_id = pvs.vendor_site_id(+) and
72 ((p_range_type = 'NAME' and (pv.vendor_name between
73 nvl(p_cs_name_from, pv.vendor_name) and
74 nvl(p_cs_name_to,pv.vendor_name))) OR
75 (p_range_type = 'NUMBER' and (pv.segment1 between
76 nvl(p_cs_number_from, pv.segment1) and
77 nvl(p_cs_number_to,pv.segment1)))) and
78 pvs.pay_site_flag = 'Y' and
79 ai.invoice_currency_code = nvl(p_currency_code, ai.invoice_currency_code) and
80 ai.set_of_books_id = TO_NUMBER(p_set_of_books_id) and
81 ai.legal_entity_id = p_legal_entity_id and
82 ai.invoice_id = aid.invoice_id and
83 aid.accounting_date >= TO_DATE(p_period_start_date,'DD/MM/YYYY HH24:MI:SS') and
84 aid.accounting_date <= TO_DATE(p_period_end_date,'DD/MM/YYYY HH24:MI:SS') and
85 (aid.posted_flag = 'Y' or aid.ACCRUAL_POSTED_FLAG = 'Y' or
86 aid.CASH_POSTED_FLAG = 'Y') and
87 (nvl(pv.vendor_type_lookup_code,'X') not in (select lookup_code
88 from je_gr_trnovr_rule_lines irl,
89 je_gr_trnovr_rules ir
90 where ir.trnovr_rule_id = p_rule_id
91 and irl.trnovr_rule_id = ir.trnovr_rule_id
92 and irl.lookup_type = 'VENDOR TYPE'
93 and irl.exclude_flag = 'Y' )) AND
94 (ai.invoice_type_lookup_code not in (select lookup_code
95 from je_gr_trnovr_rule_lines irl,
96 je_gr_trnovr_rules ir
97 where ir.trnovr_rule_id = p_rule_id
98 and irl.trnovr_rule_id = ir.trnovr_rule_id
99 and irl.lookup_type = 'INVOICE TYPE'
100 and irl.exclude_flag = 'Y')) AND
101 (aid.line_type_lookup_code not in (select lookup_code
102 from je_gr_trnovr_rule_lines irl,
103 je_gr_trnovr_rules ir
104 where ir.trnovr_rule_id = p_rule_id
105 and irl.trnovr_rule_id = ir.trnovr_rule_id
106 and irl.lookup_type = 'INVOICE DISTRIBUTION TYPE'
107 and irl.exclude_flag = 'Y'))
108 GROUP BY
109 pv.vendor_name,
110 pv.vendor_type_lookup_code,
111 pv.standard_industry_class,
112 pvs.country,
113 pvs.state,
114 pvs.province,
115 pvs.city,
116 pvs.address_line1,
117 pvs.address_line2,
118 pvs.address_line3,
119 pvs.zip,
120 ai.invoice_num,
121 ai.invoice_id,
122 ai.invoice_type_lookup_code,
123 ai.invoice_date,
124 ai.invoice_currency_code,
125 pv.segment1,
126 nvl(papf.national_identifier,nvl(pv.individual_1099,pv.num_1099)),
127 pv.vat_registration_num,
128 pv.global_attribute1,
129 pvs.vendor_site_code
130 HAVING
131 (decode(sign(SUM(nvl(aid.base_amount,NVL(aid.amount,0)))),to_number(p_balance_type),
132 abs(SUM(nvl(aid.base_amount,NVL(aid.amount,0)))),p_inv_amount_limit)) > p_inv_amount_limit;
133
134 ctr NUMBER := 0;
135
136 BEGIN
137
138 /* The following loop navigates through each and every record of the cursor
139 and calls the generic procedure to insert the data in the interface table. */
140
141
142
143
144
145 FOR rec in c_vendor_turnover
146 LOOP
147 ctr := ctr + 1;
148 GENERIC_INSERT_TO_ITF(
149 errbuf,
150 retcode,
151 rec.ap_request_id,
152 rec.vendor_name,
153 rec.segment1,
154 rec.num_1099,
155 rec.vat_registration_number,
156 rec.vendor_site_code,
157 rec.standard_industry_class,
158 rec.address_line1,
159 rec.address_line2,
160 rec.address_line3,
161 rec.city,
162 rec.state,
163 rec.zip,
164 rec.province,
165 rec.country,
166 rec.invoice_num,
167 rec.invoice_id,
168 rec.invoice_date,
169 rec.invoice_currency_code,
170 rec.distribution_total,
171 rec.invoice_type_lookup_code,
172 rec.distribution_base_total,
173 rec.vendor_type_lookup_code,
174 rec.created_by,
175 rec.creation_date,
176 rec.last_update_date,
177 rec.last_updated_by,
178 rec.last_update_login);
179 END LOOP;
180
181 IF ctr = 0 THEN
182 fnd_file.put_line(FND_FILE.OUTPUT,'******* NO DATA FOUND ******');
183 ELSE
184 COMMIT;
185 END IF;
186
187 fnd_file.put_line(FND_FILE.OUTPUT,'Concurrent Request Processed Successfully...');
188 retcode := 0;
189
190 EXCEPTION
191 WHEN OTHERS THEN
192 retcode := 2;
193 fnd_file.put_line(FND_FILE.LOG,'Error occurred during extract process...');
194 ROLLBACK;
195
196 END JE_AP_TURNOVER_EXTRACT;
197
198
199 -- This procedure extracts the data for the AR Turnover Report.
200
201 PROCEDURE JE_AR_TURNOVER_EXTRACT(
202 errbuf OUT NOCOPY VARCHAR2,
203 retcode OUT NOCOPY NUMBER,
204 p_app_short_name in varchar2,
205 p_set_of_books_id in varchar2,
206 p_period_start_date in varchar2,
207 p_period_end_date in varchar2,
208 p_range_type in varchar2,
209 p_cs_name_from in varchar2,
210 p_cs_name_to in varchar2,
211 p_cs_number_from in varchar2,
212 p_cs_number_to in varchar2,
213 p_currency_code in varchar2,
214 p_rule_id in varchar2,
215 p_inv_amount_limit in varchar2,
216 p_balance_type in varchar2,
217 p_request_id in number,
218 p_legal_entity_id in number)
219
220 IS
221
222
223 /* This cursor selects all the records needed to populate the interface table. */
224
225 CURSOR c_customer_turnover IS
226
227 SELECT
228 p_request_id ar_request_id,
229 party.party_name customer_name,
230 CUST_ACCT.ACCOUNT_NUMBER customer_number,
231 PARTY.TAX_REFERENCE tax_reference,
232 NULL vat_number,
233 PARTY.SIC_CODE standard_industry_class,
234 loc.address1 address_line1,
235 loc.address2 address_line2,
236 loc.address3 address_line3,
237 loc.city city,
238 loc.state state,
239 loc.postal_code zip,
240 loc.province province,
241 loc.country country,
242 ctt.type invoice_type_lookup_code,
243 ctx.trx_number invoice_num,
244 ctx.customer_trx_id invoice_id,
245 ctx.trx_date invoice_date,
246 ctx.invoice_currency_code invoice_currency_code,
247 sum(cgld.amount) distribution_total,
248 sum(cgld.acctd_amount) distribution_base_total,
249 -- Bug 3554792
250 decode(nvl(cusT_ACCT.global_attribute1,'N'),'Y','PUBLIC SECTOR COMPANIES',
251 CUST_ACCT.CUSTOMER_CLASS_CODE) customer_type_lookup_code,
252 0 created_by,
253 sysdate creation_date,
254 sysdate last_update_date,
255 0 last_updated_by,
256 NULL last_update_login
257 FROM ra_cust_trx_types_all ctt,
258 ra_cust_trx_line_gl_dist_all cgld,
259 ra_customer_trx_lines_all ctl,
260 ra_customer_trx_all ctx,
261
262 --ra_site_uses rsu, -- obsolete R12
263 --ra_addresses ad, -- obsolete R12
264 HZ_LOCATIONS loc,
265 HZ_CUST_SITE_USES_ALL site_uses,
266 hz_cust_acct_sites_all acct_site,
267 hz_party_sites party_site,
268
269 HZ_PARTIES party,
270 HZ_CUST_ACCOUNTS cust_acct
271 WHERE
272 CUST_ACCT.CUST_ACCOUNT_ID = ctx.bill_to_customer_id
273
274 and acct_site.cust_acct_site_id = site_uses.cust_acct_site_id
275 and acct_site.party_site_id = party_site.party_site_id
276 and loc.location_id = party_site.location_id
277
278 and ctx.customer_trx_id = ctl.customer_trx_id
279 and ctx.set_of_books_id = TO_NUMBER(p_set_of_books_id)
280 and ctx.legal_entity_id = p_legal_entity_id
281 and ctx.legal_entity_id = ctt.legal_entity_id
282 and CUST_ACCT.CUST_ACCOUNT_ID = acct_site.cust_account_id --customer_id
283 and ((p_range_type = 'NAME' and (PARTY.PARTY_NAME between
284 -- Bug 3554792
285 nvl(p_cs_name_from,PARTY.PARTY_NAME) and
286 nvl(p_cs_name_to,PARTY.PARTY_NAME))) OR
287 (p_range_type = 'NUMBER' and (CUST_ACCT.ACCOUNT_NUMBER between
288 nvl(p_cs_number_from, CUST_ACCT.ACCOUNT_NUMBER ) and
289 nvl(p_cs_number_to,CUST_ACCT.ACCOUNT_NUMBER ))))
290 -- and loc.address_id = site_uses.address_id
291 and site_uses.site_use_id = ctx.bill_to_site_use_id
292 and ctx.cust_trx_type_id = ctt.cust_trx_type_id
293 and ctx.invoice_currency_code = nvl(p_currency_code,ctx.invoice_currency_code)
294 and cgld.customer_trx_line_id = ctl.customer_trx_line_id
295 and cgld.gl_date >= TO_DATE(p_period_start_date,'DD/MM/YYYY HH24:MI:SS')
296 and cgld.gl_date <= TO_DATE(p_period_end_date,'DD/MM/YYYY HH24:MI:SS')
297 and cgld.gl_posted_date is not null
298 and (nvl(CUST_ACCT.CUSTOMER_CLASS_CODE ,'X') not in ( select lookup_code
299 from je_gr_trnovr_rule_lines irl,
300 je_gr_trnovr_rules ir
301 where ir.trnovr_rule_id = p_rule_id
302 and irl.trnovr_rule_id = ir.trnovr_rule_id
303 and irl.lookup_type = 'CUSTOMER CLASS'
304 and irl.exclude_flag = 'Y'))
305 and (ctt.type not in ( select lookup_code
306 from je_gr_trnovr_rule_lines irl,
307 je_gr_trnovr_rules ir
308 where ir.trnovr_rule_id = p_rule_id
309 and irl.trnovr_rule_id = ir.trnovr_rule_id
310 and irl.lookup_type = 'INV/CM'
311 and irl.exclude_flag = 'Y'))
312 and (ctl.line_type not in ( select lookup_code
313 from je_gr_trnovr_rule_lines irl,
314 je_gr_trnovr_rules ir
315 where ir.trnovr_rule_id = p_rule_id
316 and irl.trnovr_rule_id = ir.trnovr_rule_id
317 and irl.lookup_type = 'STD_LINE_TYPE'
318 and irl.exclude_flag = 'Y'))
319 AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
320 GROUP BY
321 PARTY.PARTY_NAME,
322 CUST_ACCT.ACCOUNT_NUMBER,
323 CUST_ACCT.CUSTOMER_CLASS_CODE ,
324 PARTY.TAX_REFERENCE,
325 PARTY.SIC_CODE,
326 CUST_ACCT.GLOBAL_ATTRIBUTE1,
327 loc.city,
328 loc.state,
329 loc.province,
330 loc.country,
331 loc.address1,
332 loc.address2,
333 loc.address3,
334 loc.postal_code,
335 ctt.type,
336 ctx.trx_number,
337 ctx.customer_trx_id,
338 cgld.gl_date,
339 ctx.customer_trx_id,
340 ctx.trx_date,
341 ctx.invoice_currency_code
342 HAVING
343 (decode(sign(sum(nvl(cgld.acctd_amount,nvl(cgld.amount,0)))),to_number(p_balance_type),
344 abs(sum(nvl(cgld.acctd_amount,nvl(cgld.amount,0)))),p_inv_amount_limit)) > p_inv_amount_limit;
345
346 ctr NUMBER := 0;
347
348 BEGIN
349
350 /* The following loop navigates through each and every record of the cursor and calls the generic
351 procedure to insert the data in the interface table. */
352
353
354 FOR rec in c_customer_turnover
355
356 LOOP
357
358 GENERIC_INSERT_TO_ITF( errbuf,retcode,rec.ar_request_id,
359 rec.customer_name,
360 rec.customer_number,
361 rec.tax_reference,
362 rec.vat_number,
363 NULL,
364 rec.standard_industry_class,
365 rec.address_line1,
366 rec.address_line2,
367 rec.address_line3,
368 rec.city,
369 rec.state,
370 rec.zip,
371 rec.province,
372 rec.country,
373 rec.invoice_num,
374 rec.invoice_id,
375 rec.invoice_date,
376 rec.invoice_currency_code,
377 rec.distribution_total,
378 rec.invoice_type_lookup_code,
379 rec.distribution_base_total,
380 rec.customer_type_lookup_code,
381 rec.created_by,
382 rec.creation_date,
383 rec.last_update_date,
384 rec.last_updated_by,
385 rec.last_update_login);
386
387 ctr := ctr + 1;
388
389 END LOOP;
390
391 IF ctr = 0 THEN
392 fnd_file.put_line(FND_FILE.OUTPUT,'******* NO DATA FOUND ******');
393 retcode := 2;
394 ELSE
395 COMMIT;
396 END IF;
397
398 fnd_file.put_line(FND_FILE.OUTPUT,'Concurrent Request Processed Successfully...');
399 retcode := 0;
400
401 EXCEPTION
402
403 WHEN OTHERS THEN
404 retcode := 2;
405 fnd_file.put_line(FND_FILE.LOG,'Error occurred during extract process...');
406 ROLLBACK;
407
408 END JE_AR_TURNOVER_EXTRACT;
409
410
411 PROCEDURE GENERIC_INSERT_TO_ITF( errbuf OUT NOCOPY VARCHAR2,
412 retcode OUT NOCOPY NUMBER,
416 p_tax_payer_id in varchar2,
413 p_request_id in number,
414 p_cust_sup_name in varchar2,
415 p_cust_sup_number in varchar2,
417 p_vat_registration_number in varchar2,
418 p_supplier_site_code in varchar2,
419 p_profession in varchar2,
420 p_address_line1 in varchar2,
421 p_address_line2 in varchar2,
422 p_address_line3 in varchar2,
423 p_city in varchar2,
424 p_state in varchar2,
425 p_zip in varchar2,
426 p_province in varchar2,
427 p_country in varchar2,
428 p_inv_trx_number in varchar2,
429 p_inv_trx_id in number,
430 p_inv_trx_date in date,
431 p_inv_trx_currency_code in varchar2,
432 p_inv_trx_amount in number,
433 p_inv_trx_type in varchar2,
434 p_acctd_inv_trx_amount in number,
435 p_cust_sup_type_code in varchar2,
436 p_created_by in number,
437 p_creation_date in date,
438 p_last_update_date in date,
439 p_last_updated_by in number,
440 p_last_update_login in number)
441 IS
442
443 BEGIN
444
445
446 INSERT INTO JE_GR_AR_AP_TRNOVR_ITF (
447 REQUEST_ID
448 ,CUST_SUP_TYPE_CODE
449 ,CUST_SUP_NAME
450 ,CUST_SUP_NUMBER
451 ,TAX_PAYER_ID
452 ,VAT_REGISTRATION_NUMBER
453 ,SUPPLIER_SITE_CODE
454 ,PROFESSION
455 ,ADDRESS_LINE1
456 ,ADDRESS_LINE2
457 ,ADDRESS_LINE3
458 ,CITY
459 ,STATE
460 ,ZIP
461 ,PROVINCE
462 ,COUNTRY
463 ,INV_TRX_TYPE
464 ,INV_TRX_NUMBER
465 ,INV_TRX_ID
466 ,INV_TRX_DATE
467 ,INV_TRX_CURRENCY_CODE
468 ,INV_TRX_AMOUNT
469 ,ACCTD_INV_TRX_AMOUNT
470 ,CREATED_BY
471 ,CREATION_DATE
472 ,LAST_UPDATE_DATE
473 ,LAST_UPDATED_BY
474 ,LAST_UPDATE_LOGIN
475 )
476 values(
477 p_request_id,
478 p_cust_sup_type_code,
479 p_cust_sup_name,
480 p_cust_sup_number,
481 p_tax_payer_id,
482 p_vat_registration_number,
483 p_supplier_site_code,
484 p_profession,
485 p_address_line1,
486 p_address_line2,
487 p_address_line3,
488 p_city,
489 p_state,
490 p_zip,
491 p_province,
492 p_country,
493 p_inv_trx_type,
494 p_inv_trx_number,
495 p_inv_trx_id,
496 p_inv_trx_date,
497 p_inv_trx_currency_code,
498 p_inv_trx_amount,
499 p_acctd_inv_trx_amount,
500 p_created_by,
501 p_creation_date,
502 p_last_update_date,
503 p_last_updated_by,
504 p_last_update_login);
505 EXCEPTION
506
507 WHEN OTHERS THEN
508 fnd_file.put_line(FND_FILE.log,'While Inserting the Record into Interface Table');
509 retcode := 2;
510
511 END GENERIC_INSERT_TO_ITF;
512
513
514 END JERX_TO;