[Home] [Help]
PACKAGE BODY: APPS.JERX_TO
Source
1 PACKAGE BODY JERX_TO AS
2 /* $Header: jegrtob.pls 120.9.12010000.3 2010/02/18 06:52:47 gkumares 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 WHERE trunc(sysdate) BETWEEN effective_start_date AND effective_end_date) 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
258 ra_cust_trx_types_all ctt,
259 ra_cust_trx_line_gl_dist_all cgld,
260 ra_customer_trx_lines_all ctl,
261 ra_customer_trx_all ctx,
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 HZ_PARTIES party,
269 HZ_CUST_ACCOUNTS cust_acct
270 WHERE
271 CUST_ACCT.CUST_ACCOUNT_ID = ctx.bill_to_customer_id
272 and acct_site.cust_acct_site_id = site_uses.cust_acct_site_id
273 and acct_site.party_site_id = party_site.party_site_id
274 and loc.location_id = party_site.location_id
275 and ctx.customer_trx_id = ctl.customer_trx_id
276 and ctx.set_of_books_id = TO_NUMBER(p_set_of_books_id)
277 and ctx.legal_entity_id = p_legal_entity_id
278 -- and ctx.legal_entity_id = ctt.legal_entity_id
279 and ctx.org_id = ctt.org_id --Bug 6389667
280 and CUST_ACCT.CUST_ACCOUNT_ID = acct_site.cust_account_id --customer_id
281 and ((p_range_type = 'NAME' and (PARTY.PARTY_NAME between -- Bug 3554792
282 nvl(p_cs_name_from,PARTY.PARTY_NAME) and nvl(p_cs_name_to,PARTY.PARTY_NAME)))
283 OR
284 (p_range_type = 'NUMBER' and (CUST_ACCT.ACCOUNT_NUMBER between
285 nvl(p_cs_number_from, CUST_ACCT.ACCOUNT_NUMBER ) and nvl(p_cs_number_to,CUST_ACCT.ACCOUNT_NUMBER ))))
286 -- and loc.address_id = site_uses.address_id
287 and site_uses.site_use_id = ctx.bill_to_site_use_id
288 and ctx.cust_trx_type_id = ctt.cust_trx_type_id
289 and ctx.invoice_currency_code = nvl(p_currency_code,ctx.invoice_currency_code)
290 and cgld.customer_trx_line_id = ctl.customer_trx_line_id
291 and cgld.gl_date >= TO_DATE(p_period_start_date,'DD/MM/YYYY HH24:MI:SS')
292 and cgld.gl_date <= TO_DATE(p_period_end_date,'DD/MM/YYYY HH24:MI:SS')
293 and cgld.gl_posted_date is not null
294 and CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
295 and (nvl(CUST_ACCT.CUSTOMER_CLASS_CODE ,'X') not in ( select lookup_code
296 from
297 je_gr_trnovr_rule_lines irl,
298 je_gr_trnovr_rules ir
299 where
300 ir.trnovr_rule_id = p_rule_id
301 and irl.trnovr_rule_id = ir.trnovr_rule_id
302 and irl.lookup_type = 'CUSTOMER CLASS'
303 and irl.exclude_flag = 'Y'))
304 and (ctt.type not in ( select lookup_code
305 from
306 je_gr_trnovr_rule_lines irl,
307 je_gr_trnovr_rules ir
308 where
309 ir.trnovr_rule_id = p_rule_id
310 and irl.trnovr_rule_id = ir.trnovr_rule_id
311 and irl.lookup_type = 'INV/CM'
312 and irl.exclude_flag = 'Y'))
313 and (ctl.line_type not in ( select lookup_code
314 from
315 je_gr_trnovr_rule_lines irl,
316 je_gr_trnovr_rules ir
317 where
318 ir.trnovr_rule_id = p_rule_id
319 and irl.trnovr_rule_id = ir.trnovr_rule_id
320 and irl.lookup_type = 'STD_LINE_TYPE'
321 and irl.exclude_flag = 'Y'))
322
323 GROUP BY
324 PARTY.PARTY_NAME,
325 CUST_ACCT.ACCOUNT_NUMBER,
326 CUST_ACCT.CUSTOMER_CLASS_CODE ,
327 PARTY.TAX_REFERENCE,
328 PARTY.SIC_CODE,
329 CUST_ACCT.GLOBAL_ATTRIBUTE1,
330 loc.city,
331 loc.state,
332 loc.province,
333 loc.country,
334 loc.address1,
335 loc.address2,
336 loc.address3,
337 loc.postal_code,
338 ctt.type,
339 ctx.trx_number,
340 ctx.customer_trx_id,
341 cgld.gl_date,
342 ctx.customer_trx_id,
343 ctx.trx_date,
344 ctx.invoice_currency_code
345 HAVING
346 (decode(sign(sum(nvl(cgld.acctd_amount,nvl(cgld.amount,0)))),to_number(p_balance_type),
347 abs(sum(nvl(cgld.acctd_amount,nvl(cgld.amount,0)))),p_inv_amount_limit)) > p_inv_amount_limit;
348
349 ctr NUMBER := 0;
350
351 BEGIN
352
353 /* The following loop navigates through each and every record of the cursor and calls the generic
354 procedure to insert the data in the interface table. */
355
356
357 FOR rec in c_customer_turnover
358
359 LOOP
360
361 GENERIC_INSERT_TO_ITF( errbuf,retcode,rec.ar_request_id,
362 rec.customer_name,
363 rec.customer_number,
364 rec.tax_reference,
365 rec.vat_number,
366 NULL,
367 rec.standard_industry_class,
368 rec.address_line1,
369 rec.address_line2,
370 rec.address_line3,
371 rec.city,
372 rec.state,
373 rec.zip,
374 rec.province,
375 rec.country,
376 rec.invoice_num,
377 rec.invoice_id,
378 rec.invoice_date,
379 rec.invoice_currency_code,
380 rec.distribution_total,
381 rec.invoice_type_lookup_code,
382 rec.distribution_base_total,
383 rec.customer_type_lookup_code,
384 rec.created_by,
385 rec.creation_date,
386 rec.last_update_date,
387 rec.last_updated_by,
388 rec.last_update_login);
389
390 ctr := ctr + 1;
391
392 END LOOP;
393
394 IF ctr = 0 THEN
395 fnd_file.put_line(FND_FILE.OUTPUT,'******* NO DATA FOUND ******');
396 retcode := 2;
397 ELSE
398 COMMIT;
399 END IF;
400
401 fnd_file.put_line(FND_FILE.OUTPUT,'Concurrent Request Processed Successfully...');
402 retcode := 0;
403
404 EXCEPTION
405
406 WHEN OTHERS THEN
407 retcode := 2;
408 fnd_file.put_line(FND_FILE.LOG,'Error occurred during extract process...');
409 ROLLBACK;
410
411 END JE_AR_TURNOVER_EXTRACT;
412
413
414 PROCEDURE GENERIC_INSERT_TO_ITF( errbuf OUT NOCOPY VARCHAR2,
415 retcode OUT NOCOPY NUMBER,
416 p_request_id in number,
417 p_cust_sup_name in varchar2,
418 p_cust_sup_number in varchar2,
419 p_tax_payer_id in varchar2,
420 p_vat_registration_number in varchar2,
421 p_supplier_site_code in varchar2,
422 p_profession in varchar2,
423 p_address_line1 in varchar2,
424 p_address_line2 in varchar2,
425 p_address_line3 in varchar2,
426 p_city in varchar2,
427 p_state in varchar2,
428 p_zip in varchar2,
429 p_province in varchar2,
430 p_country in varchar2,
431 p_inv_trx_number in varchar2,
432 p_inv_trx_id in number,
433 p_inv_trx_date in date,
434 p_inv_trx_currency_code in varchar2,
435 p_inv_trx_amount in number,
436 p_inv_trx_type in varchar2,
437 p_acctd_inv_trx_amount in number,
438 p_cust_sup_type_code in varchar2,
439 p_created_by in number,
440 p_creation_date in date,
441 p_last_update_date in date,
442 p_last_updated_by in number,
443 p_last_update_login in number)
444 IS
445
446 BEGIN
447
448
449 INSERT INTO JE_GR_AR_AP_TRNOVR_ITF (
450 REQUEST_ID
451 ,CUST_SUP_TYPE_CODE
452 ,CUST_SUP_NAME
453 ,CUST_SUP_NUMBER
454 ,TAX_PAYER_ID
455 ,VAT_REGISTRATION_NUMBER
456 ,SUPPLIER_SITE_CODE
457 ,PROFESSION
458 ,ADDRESS_LINE1
459 ,ADDRESS_LINE2
460 ,ADDRESS_LINE3
461 ,CITY
462 ,STATE
463 ,ZIP
464 ,PROVINCE
465 ,COUNTRY
466 ,INV_TRX_TYPE
467 ,INV_TRX_NUMBER
468 ,INV_TRX_ID
469 ,INV_TRX_DATE
470 ,INV_TRX_CURRENCY_CODE
471 ,INV_TRX_AMOUNT
472 ,ACCTD_INV_TRX_AMOUNT
473 ,CREATED_BY
474 ,CREATION_DATE
475 ,LAST_UPDATE_DATE
476 ,LAST_UPDATED_BY
477 ,LAST_UPDATE_LOGIN
478 )
479 values(
480 p_request_id,
481 p_cust_sup_type_code,
482 p_cust_sup_name,
483 p_cust_sup_number,
484 p_tax_payer_id,
485 p_vat_registration_number,
486 p_supplier_site_code,
487 p_profession,
488 p_address_line1,
489 p_address_line2,
490 p_address_line3,
491 p_city,
492 p_state,
493 p_zip,
494 p_province,
495 p_country,
496 p_inv_trx_type,
497 p_inv_trx_number,
498 p_inv_trx_id,
499 p_inv_trx_date,
500 p_inv_trx_currency_code,
501 p_inv_trx_amount,
502 p_acctd_inv_trx_amount,
503 p_created_by,
504 p_creation_date,
505 p_last_update_date,
506 p_last_updated_by,
507 p_last_update_login);
508 EXCEPTION
509
510 WHEN OTHERS THEN
511 fnd_file.put_line(FND_FILE.log,'While Inserting the Record into Interface Table');
512 retcode := 2;
513
514 END GENERIC_INSERT_TO_ITF;
515
516
517 END JERX_TO;