[Home] [Help]
PACKAGE BODY: APPS.PN_EXP_TO_AP
Source
1 PACKAGE BODY pn_exp_to_ap AS
2 -- $Header: PNTXPMTB.pls 120.14 2007/10/09 09:11:04 rkartha ship $
3
4 -------------------------------------------------------------------
5 -- For loading PN's Invoice Info into AP's Interface Tables
6 -- ( Run as a Conc Process )
7 -------------------------------------------------------------------
8
9 -- ************************************************************************
10 -- Package level DECLARATIONS go here
11 -- ************************************************************************
12
13 -- variables for ref cursor
14 l_one NUMBER := 1;
15 l_id NUMBER := -1;
16
17 -- the main query string
18 -- this will be used by all procedures
19
20 l_func_curr_code gl_sets_of_books.currency_code%TYPE;
21
22 Q_Payitem VARCHAR2(5000);
23
24 l_Select_Clause VARCHAR2(5000):= '
25 SELECT
26 pi.ORG_ID as org_id,
27 pi.payment_item_id,
28 pi.payment_term_id as payment_term_id,
29 pi.export_currency_amount,
30 pi.export_currency_code,
31 pi.vendor_id,
32 pi.vendor_site_id as vendor_site_id,
33 pt.project_id,
34 pt.task_id,
35 pt.organization_id,
36 pt.expenditure_type,
37 pt.expenditure_item_date,
38 pt.tax_group_id,
39 pt.tax_code_id,
40 pt.tax_classification_code,
41 pt.tax_included,
42 pt.distribution_set_id,
43 le.lease_num,
44 le.lease_id,
45 NVL(pld.send_entries, ''Y'') as send_entries,
46 pi.payment_schedule_id,
47 ps.period_name,
48 PNP_UTIL_FUNC.get_start_date(ps.period_name,pn_mo_cache_utils.get_current_org_id) as gl_date,
49 pt.normalize,
50 pi.due_date,
51 pt.ap_ar_term_id,
52 TRUNC(pi.accounted_date),
53 pi.rate,
54 pi.ap_invoice_num,
55 pt.payment_purpose_code,
56 pt.payment_term_type_code,
57 pn_exp_to_ap.get_liability_acc(pi.payment_term_id,
58 pi.vendor_id,
59 pi.vendor_site_id) as lia_account,
60 pt.legal_entity_id as legal_entity_id,
61 decode(UPPER(PNP_UTIL_FUNC.check_conversion_type('''||l_func_curr_code||''',pn_mo_cache_utils.get_current_org_id))
62 , ''USER'', decode(pi.export_currency_code
63 ,'''||l_func_curr_code||''', 1
64 ,pi.rate)
65 , NULL) as conv_rate,
66 PNP_UTIL_FUNC.check_conversion_type('''||l_func_curr_code||''',pn_mo_cache_utils.get_current_org_id)
67 as conv_rate_type,
68 pi.grouping_rule_id as item_grouping_rule_id,
69 pt.grouping_rule_id as term_grouping_rule_id,
70 pld.grouping_rule_id as lease_grouping_rule_id
71 FROM pn_payment_items pi,
72 pn_payment_schedules_all ps,
73 pn_payment_terms_all pt,
74 pn_leases_all le,
75 pn_lease_details_all pld,
76 fnd_lookups type_lookup,
77 fnd_lookups purpose_lookup,
78 po_vendors vendor,
79 hr_operating_units ou,
80 pn_pay_group_rules ppgr
81 WHERE pi.payment_term_id = pt.payment_term_id
82 AND pi.payment_schedule_id = ps.payment_schedule_id
83 AND nvl(pi.export_to_ap_flag,''N'') = ''Y''
84 AND pi.payment_item_type_lookup_code = ''CASH''
85 AND pt.lease_id = le.lease_id
86 AND pld.lease_id = le.lease_id
87 AND le.parent_lease_id IS NULL
88 AND pi.transferred_to_ap_flag IS NULL
89 AND pi.vendor_id IS NOT NULL
90 AND pi.export_currency_amount <> 0
91 AND pi.grouping_rule_id = ppgr.grouping_rule_id (+)
92 AND type_lookup.lookup_type = ''PN_PAYMENT_TERM_TYPE''
93 AND type_lookup.lookup_code = pt.payment_term_type_code
94 AND purpose_lookup.lookup_type = ''PN_PAYMENT_PURPOSE_TYPE''
95 AND purpose_lookup.lookup_code = pt.payment_purpose_code
96 AND vendor.vendor_id = pi.vendor_id
97 AND ou.organization_id = pi.org_id
98 AND 1 = :l_one ';
99
100 -- where clauses for 3 cases of grouping rule attached to
101 -- 1. item level
102 l_where_clause_item VARCHAR2(2000) := ' AND pi.grouping_rule_id IS NOT NULL
103 AND pi.grouping_rule_id = :l_id ';
104
105 -- 2. term level
106 l_where_clause_term VARCHAR2(2000) := ' AND pi.grouping_rule_id IS NULL
107 AND pt.grouping_rule_id IS NOT NULL
108 AND pt.grouping_rule_id = :l_id ';
109 -- 3. lease level
110 l_where_clause_lease VARCHAR2(2000) := ' AND pi.grouping_rule_id IS NULL
111 AND pt.grouping_rule_id IS NULL
112 AND pld.grouping_rule_id IS NOT NULL
113 AND pld.grouping_rule_id = :l_id ';
114 -- 4. system option level
115 l_where_clause_sysop VARCHAR2(2000) := ' AND pi.grouping_rule_id IS NULL
116 AND pt.grouping_rule_id IS NULL
117 AND pld.grouping_rule_id IS NULL
118 AND -1 = :l_id ';
119
120 -- order by clause to be used for all cases for mandatory attributes
121 --
122 -- mapping is as follows for the mandatory attributes
123 --
124 -- Supplier pi.vendor_id
125 -- Supplier Site pi.vendor_site_id
126 -- Payment Terms pt.ap_ar_term_id
127 -- GL Date gl_date
128 -- Invoice Date pi.due_date
129 -- Currency code pi.export_currency_code
130 -- Exchange rate conv_rate
131 -- Exchange date pi.accounted_date
132 -- Exchange type conv_rate_type
133 -- Legal entity id legal_entity_id
134
135 l_order_by_clause VARCHAR2(2000) := ' pi.vendor_id,
136 pi.vendor_site_id,
137 pt.ap_ar_term_id,
138 gl_date,
139 pi.due_date,
140 pi.export_currency_code,
141 conv_rate,
142 TRUNC(pi.accounted_date),
143 conv_rate_type,
144 legal_entity_id ';
145
146 -- order by clause to be used for cases of grouping rule attached to
147 -- 1. item level
148
149 l_order_by_clause_item VARCHAR2(2000) := ' ORDER BY item_grouping_rule_id, ';
150
151 -- 2. term level
152
153 l_order_by_clause_term VARCHAR2(2000) := ' ORDER BY term_grouping_rule_id, ';
154
155 -- 3. lease level
156
157 l_order_by_clause_lease VARCHAR2(2000) := ' ORDER BY lease_grouping_rule_id, ';
158
159 -- 4. order by for optional attributes
160
161 l_order_by_clause_grpby VARCHAR2(2000) := '';
162
163 -- 5. default
164
165 l_order_by_clause_default VARCHAR2(2000) := ' ORDER BY le.lease_id ';
166
167 -- export to AP items cache for grouping and processing
168
169 exp_ap_cache exp_ap_tbl_typ;
170 exp_ap_lines_cache exp_ap_tbl_typ;
171 exp_ap_dist_cache exp_ap_tbl_typ;
172
173 -- account distribution info
174
175 CURSOR get_acnt_info(p_term_id NUMBER) IS
176 SELECT account_id,
177 account_class,
178 percentage
179 FROM pn_distributions_all
180 WHERE payment_term_id = p_term_id;
181
182 TYPE acnt_type IS TABLE OF get_acnt_info%ROWTYPE
183 INDEX BY BINARY_INTEGER;
184
185 lia_acnt_tab acnt_type;
186 exp_acnt_tab acnt_type;
187 acc_acnt_tab acnt_type;
188
189 l_total_exp_amt NUMBER := 0;
190 l_total_exp_percent NUMBER := 0;
191 l_diff_amt NUMBER := 0;
192 l_amt NUMBER := 0;
193 l_exp_amt NUMBER := 0;
194 l_lia_cnt NUMBER := 0;
195 l_exp_cnt NUMBER := 0;
196 l_acc_cnt NUMBER := 0;
197
198 -- tax name
199
200 CURSOR get_tax_name(p_tax_id NUMBER) IS
201 SELECT name
202 FROM ap_tax_codes_all
203 WHERE tax_id = p_tax_id;
204
205 l_tax_name ap_tax_codes.name%TYPE;
206 l_tax_code_override_flag ap_invoice_lines_interface.tax_code_override_flag%TYPE := NULL;
207
208 -- functional currrency code
209
210 CURSOR get_func_curr_code(p_set_of_books_id IN NUMBER) IS
211 SELECT currency_code
212 FROM gl_sets_of_books
213 WHERE set_of_books_id = p_set_of_books_id;
214
215 -- currency precision
216
217 l_precision NUMBER;
218 l_ext_precision NUMBER;
219 l_min_acct_unit NUMBER;
220
221 -- system level grouping rule ID
222
223 CURSOR get_system_grouping_rule_id(p_org_ID IN NUMBER) IS
224 SELECT grouping_rule_id
225 FROM pn_system_setup_options
226 WHERE org_id = p_org_ID;
227
228 -- group by attributes
229
230 CURSOR get_group_bys(p_grouping_rule_id IN NUMBER) IS
231 SELECT group_by_id,
232 grouping_rule_id,
233 group_by_lookup_code
234 FROM pn_pay_group_bys
235 WHERE grouping_rule_id = p_grouping_rule_id;
236
237 -- group by flags
238
239 l_grpby_INVOICENUM BOOLEAN := FALSE;
240 l_grpby_LEASENUM BOOLEAN := FALSE;
241 l_grpby_PAYPURPOSE BOOLEAN := FALSE;
242 l_grpby_PAYTYPE BOOLEAN := FALSE;
243
244 -- counters for total and error
245
246 l_total_ctr NUMBER := 0;
247 l_error_ctr NUMBER := 0;
248
249 -- for interface
250
251 l_invoice_id ap_invoices_interface.invoice_id%type;
252 l_invoice_num ap_invoices_interface.invoice_num%type;
253 l_invoice_line_id ap_invoice_lines_interface.invoice_line_id%type;
254
255 -- other variables
256
257 l_system_grouping_rule_id NUMBER;
258 l_created_by NUMBER := FND_GLOBAL.USER_ID;
259 l_creation_date DATE := sysdate;
260 l_last_updated_by NUMBER := FND_GLOBAL.USER_ID;
261 l_last_update_login NUMBER := FND_GLOBAL.LOGIN_ID;
262 l_last_update_date DATE := sysdate;
263 l_context VARCHAR2(2000);
264
265 /* EXCEPTIONS */
266 BAD_ITEM_EXCEPTION EXCEPTION;
267 BAD_INVOICE_NUM EXCEPTION;
268 FATAL_ERROR EXCEPTION;
269
270 -- variables for dbms_sql
271 l_cursor INTEGER;
272 l_rows INTEGER;
273 l_count INTEGER;
274 l_cursor_2 INTEGER;
275 Q_Payitem1 VARCHAR2(5000);
276 l_rows_2 INTEGER;
277 l_count_2 INTEGER;
278
279
280
281 /* ******************* END OF DECLARATIONS ******************* */
282
283 /* ************************************************************************
284 PROCEDURES BEGIN HERE
285 ************************************************************************ */
286
287 --------------------------------------------------------------------------------
288 -- NAME : get_liability_acc
289 -- DESCRIPTION : Gets the Liability account for a payment item given the
290 -- Payment Term ID. If a Term has no Liabilty account defined in
291 -- its distributions, the Liability account is defaulted from
292 -- the Vendor Site; in case a Liability account is not defined
293 -- for a Vendor Site, it is defaulted from the Vendor.
294 -- PURPOSE : Gets the Liability account.
295 -- INVOKED FROM : The main query
296 -- ARGUMENTS : p_payment_term_id - Payment Term ID
297 -- p_vendor_id - Vendor ID in PO_VENDORS
298 -- p_vendor_site_id - Vendor Site ID in PO_VENDOR_SITES
299 -- REFERENCE : PN_COMMON.debug()
300 -- HISTORY :
301 --
302 -- 19-DEC-2003 Kiran o Created
303 -- 12-SEP-2005 sdmahesh o Removed NVL from the cursor get_lia_vendor_site
304 -- query
305 --------------------------------------------------------------------------------
306 FUNCTION get_liability_acc(p_payment_term_id NUMBER,
307 p_vendor_id NUMBER,
308 p_vendor_site_id NUMBER)
309 RETURN NUMBER IS
310
311 CURSOR get_lia_acnt(p_term_id IN NUMBER) IS
312 SELECT account_id
313 FROM pn_distributions_all
314 WHERE payment_term_id = p_term_id
315 AND account_class = 'LIA';
316
317 CURSOR get_lia_vendor_site(p_vendor_site IN NUMBER) IS
318 SELECT site.accts_pay_code_combination_id as accts_pay_code_combination_id
319 FROM po_vendor_sites site
320 WHERE site.vendor_site_id = p_vendor_site_id;
321
322 account_id NUMBER := NULL;
323
324 BEGIN
325 -- ** uncomment the following for debugging **
326 -- PNP_DEBUG_PKG.log('pn_exp_to_ap.get_liability_acc (+)');
327 -- PNP_DEBUG_PKG.log('p_payment_term_id: '||p_payment_term_id);
328 -- PNP_DEBUG_PKG.log('p_vendor_id: ' ||p_vendor_id);
329 -- PNP_DEBUG_PKG.log('p_vendor_site_id: ' ||p_vendor_site_id);
330
331 l_context := 'Getting account info for Payment term ID: '
332 ||p_payment_term_id;
333
334 FOR lia_acc IN get_lia_acnt(p_payment_term_id) LOOP
335 account_id := lia_acc.account_id;
336 END LOOP;
337
338 IF account_id IS NULL THEN
339 FOR lia_acc IN get_lia_vendor_site(p_vendor_site_id) LOOP
340 account_id := lia_acc.accts_pay_code_combination_id;
341 END LOOP;
342 END IF;
343
344 -- ** uncomment the following for debugging **
345 -- PNP_DEBUG_PKG.log('account_id: '||account_id);
346 -- PNP_DEBUG_PKG.log('pn_exp_to_ap.get_liability_acc (-)');
347
348 RETURN account_id;
349
350 EXCEPTION
351 WHEN others THEN
352 RAISE;
353
354 END get_liability_acc;
355
356 --------------------------------------------------------------------------------
357 -- NAME : populate_group_by_flags
358 -- DESCRIPTION : Populates group by flags to be used later by
359 -- get_order_by_grpby to create the order by clause and by
360 -- group_and_export_to_AP for flagging groups.
361 -- PURPOSE : Populates group by flags
362 -- INVOKED FROM : exp_to_ap
363 -- ARGUMENTS : p_grouping_rule_id - Grouping Rule ID
364 -- REFERENCE : PN_COMMON.debug()
365 -- HISTORY :
366 --
367 -- 19-DEC-2003 Kiran o Created
368 --------------------------------------------------------------------------------
369 PROCEDURE populate_group_by_flags(p_grouping_rule_id IN NUMBER) IS
370
371 BEGIN
372 PNP_DEBUG_PKG.log('pn_exp_to_ap.populate_group_by_flags (+)');
376 || p_grouping_rule_id;
373 PNP_DEBUG_PKG.log('p_grouping_rule_id: '||p_grouping_rule_id);
374
375 l_context := 'Populating group by flags for Grouping Rule ID: '
377
378 -- init the flags
379
380 l_grpby_INVOICENUM := FALSE;
381 l_grpby_LEASENUM := FALSE;
382 l_grpby_PAYPURPOSE := FALSE;
383 l_grpby_PAYTYPE := FALSE;
384
385 FOR group_bys IN get_group_bys(p_grouping_rule_id) LOOP
386
387 IF group_bys.group_by_lookup_code = 'INVOICENUM' THEN
388 l_grpby_INVOICENUM := TRUE;
389 ELSIF group_bys.group_by_lookup_code = 'LEASENUM' THEN
390 l_grpby_LEASENUM := TRUE;
391 ELSIF group_bys.group_by_lookup_code = 'PAYPURPOSE' THEN
392 l_grpby_PAYPURPOSE := TRUE;
393 ELSIF group_bys.group_by_lookup_code = 'PAYTYPE' THEN
394 l_grpby_PAYTYPE := TRUE;
395 END IF;
396
397 END LOOP;
398
399 PNP_DEBUG_PKG.log('pn_exp_to_ap.populate_group_by_flags (-)');
400
401 EXCEPTION
402 WHEN others THEN
403 RAISE;
404 END populate_group_by_flags;
405
406 --------------------------------------------------------------------------------
407 -- NAME : get_order_by_grpby
408 -- DESCRIPTION : Conditionally creates the order by clause incrementally,
409 -- based on the global grouping flags set by call to
410 -- populate_group_by_flags
411 -- ** NOTE : Call this only after a call to populate_group_by_flags
412 -- PURPOSE : Creates the order by clause for grouping attributes
413 -- INVOKED FROM : exp_to_ap
414 -- ARGUMENTS : none
415 -- REFERENCE : PN_COMMON.debug()
416 -- HISTORY :
417 --
418 -- 19-DEC-2003 Kiran o Created
419 --------------------------------------------------------------------------------
420 PROCEDURE get_order_by_grpby IS
421
422 BEGIN
423 PNP_DEBUG_PKG.log('pn_exp_to_ap.get_order_by_grpby (+)');
424
425 l_context := 'Creating order by clause';
426
427 IF l_grpby_INVOICENUM THEN
428 l_order_by_clause_grpby := l_order_by_clause_grpby || ' , pi.ap_invoice_num ';
429 END IF;
430
431 IF l_grpby_LEASENUM THEN
432 l_order_by_clause_grpby := l_order_by_clause_grpby || ' , le.lease_num ';
433 END IF;
434
435 IF l_grpby_PAYPURPOSE THEN
436 l_order_by_clause_grpby := l_order_by_clause_grpby || ' , pt.payment_purpose_code ';
437 END IF;
438
439 IF l_grpby_PAYTYPE THEN
440 l_order_by_clause_grpby := l_order_by_clause_grpby || ' , pt.payment_term_type_code ';
441 END IF;
442
443 PNP_DEBUG_PKG.log('pn_exp_to_ap.get_order_by_grpby (-)');
444
445 EXCEPTION
446 WHEN others THEN
447 RAISE;
448
449 END get_order_by_grpby;
450
451 --------------------------------------------------------------------------------
452 -- NAME : bind_variables_to_cursor
453 -- PURPOSE : Binding the variables to the cursor passed
454 -- INVOKED FROM : cache_exp_items
455 -- ARGUMENTS : p_lease_num_low
456 -- p_lease_num_high
457 -- p_sch_dt_low
458 -- p_sch_dt_high
459 -- p_due_dt_low
460 -- p_due_dt_high
461 -- p_pay_prps_code
462 -- p_prd_name
463 -- p_amt_low
464 -- p_amt_high
465 -- p_vendor_id
466 -- p_inv_num
467 -- p_grp_param
468 -- p_cursor
469 -- HISTORY :
470 -- 30-NOV-05 Hareesha o Created
471 --------------------------------------------------------------------------------
472 PROCEDURE bind_variables_to_cursor(
473 p_lease_num_low VARCHAR2,
474 p_lease_num_high VARCHAR2,
475 p_sch_dt_low VARCHAR2,
476 p_sch_dt_high VARCHAR2,
477 p_due_dt_low VARCHAR2,
478 p_due_dt_high VARCHAR2,
479 p_pay_prps_code VARCHAR2,
480 p_prd_name VARCHAR2,
481 p_amt_low NUMBER,
482 p_amt_high NUMBER,
483 p_vendor_id NUMBER,
484 p_inv_num VARCHAR2,
485 p_grp_param VARCHAR2,
486 p_cursor INTEGER) IS
487
488 BEGIN
489
490 PNP_DEBUG_PKG.log('pn_exp_to_ap.bind_variables_to_cursor (+)');
491
492 dbms_sql.bind_variable
493 (p_cursor,'l_one',l_one );
494 dbms_sql.bind_variable
495 (p_cursor,'l_id',l_id );
496
497 IF p_grp_param IS NULL THEN
498
499 IF p_lease_num_low IS NOT NULL AND p_lease_num_high IS NOT NULL THEN
500 dbms_sql.bind_variable
501 (p_cursor,'l_lease_num_low',p_lease_num_low );
502 dbms_sql.bind_variable
503 (p_cursor,'l_lease_num_high',p_lease_num_high );
504
505 ELSIF p_lease_num_low IS NULL AND p_lease_num_high IS NOT NULL THEN
506 dbms_sql.bind_variable
510 dbms_sql.bind_variable
507 (p_cursor,'l_lease_num_high',p_lease_num_high );
508
509 ELSIF p_lease_num_low IS NOT NULL AND p_lease_num_high IS NULL THEN
511 (p_cursor,'l_lease_num_low',p_lease_num_low );
512 END IF;
513
514 IF p_sch_dt_low IS NOT NULL AND p_sch_dt_high IS NOT NULL THEN
515 dbms_sql.bind_variable
516 (p_cursor,'l_sch_dt_high',fnd_date.canonical_to_date(p_sch_dt_high) );
517 dbms_sql.bind_variable
518 (p_cursor,'l_sch_dt_low',fnd_date.canonical_to_date(p_sch_dt_low) );
519
520 ELSIF p_sch_dt_low IS NULL AND p_sch_dt_high IS NOT NULL THEN
521 dbms_sql.bind_variable
522 (p_cursor,'l_sch_dt_high',fnd_date.canonical_to_date(p_sch_dt_high) );
523
524 ELSIF p_sch_dt_low IS NOT NULL AND p_sch_dt_high IS NULL THEN
525 dbms_sql.bind_variable
526 (p_cursor,'l_sch_dt_low',fnd_date.canonical_to_date(p_sch_dt_low) );
527 END IF;
528
529 IF p_due_dt_low IS NOT NULL AND p_due_dt_high IS NOT NULL THEN
530 dbms_sql.bind_variable
531 (p_cursor,'l_due_dt_low',fnd_date.canonical_to_date(p_due_dt_low) );
532 dbms_sql.bind_variable
533 (p_cursor,'l_due_dt_high',fnd_date.canonical_to_date(p_due_dt_high) );
534
535 ELSIF p_due_dt_low IS NULL AND p_due_dt_high IS NOT NULL THEN
536 dbms_sql.bind_variable
537 (p_cursor,'l_due_dt_high',fnd_date.canonical_to_date(p_due_dt_high) );
538
539 ELSIF p_due_dt_low IS NOT NULL AND p_due_dt_high IS NULL THEN
540 dbms_sql.bind_variable
541 (p_cursor,'l_due_dt_low',fnd_date.canonical_to_date(p_due_dt_low) );
542 END IF;
543
544 IF p_pay_prps_code IS NOT NULL THEN
545 dbms_sql.bind_variable
546 (p_cursor,'l_pay_prps_code',p_pay_prps_code );
547 END IF;
548
549 IF p_prd_name IS NOT NULL THEN
550 dbms_sql.bind_variable
551 (p_cursor,'l_prd_name',p_prd_name );
552 END IF;
553
554 IF p_amt_low IS NOT NULL AND p_amt_high IS NOT NULL THEN
555 dbms_sql.bind_variable
556 (p_cursor,'l_amt_low',p_amt_low );
557 dbms_sql.bind_variable
558 (p_cursor,'l_amt_high',p_amt_high );
559
560 ELSIF p_amt_low IS NULL AND p_amt_high IS NOT NULL THEN
561 dbms_sql.bind_variable
562 (p_cursor,'l_amt_high',p_amt_high );
563
564 ELSIF p_amt_low IS NOT NULL AND p_amt_high IS NULL THEN
565 dbms_sql.bind_variable
566 (p_cursor,'l_amt_low',p_amt_low );
567 END IF;
568
569 IF p_vendor_id IS NOT NULL THEN
570 dbms_sql.bind_variable
571 (p_cursor,'l_vendor_id',p_vendor_id );
572 END IF;
573
574 IF p_inv_num IS NOT NULL THEN
575 dbms_sql.bind_variable
576 (p_cursor,'l_inv_num',p_inv_num );
577 END IF;
578
579 ELSE
580 dbms_sql.bind_variable
581 (p_cursor,'l_grp_param',p_grp_param );
582 END IF;
583
584 PNP_DEBUG_PKG.log('pn_exp_to_ap.bind_variables_to_cursor (-)');
585
586
587 EXCEPTION
588 WHEN FATAL_ERROR THEN
589 RAISE;
590 WHEN others THEN
591 RAISE;
592
593 END bind_variables_to_cursor;
594
595 --------------------------------------------------------------------------------
596 -- NAME : cache_exp_items
597 -- DESCRIPTION : Uses the query string created in Q_Payitems to query and
598 -- cache the valid payment items. At a given time, the cache is
599 -- designed to contain items with the same grouping rule. The
600 -- items are also ordered in groups, but the groups will need to
601 -- be identified at begin/end and flagged accordingly.
602 -- PURPOSE : Cache items for export to AP
603 -- INVOKED FROM : exp_to_ap
604 -- ARGUMENTS :
605 -- REFERENCE : PN_COMMON.debug()
606 -- HISTORY :
607 --
608 -- 19-DEC-03 Kiran o Created
609 -- 22-NOV-04 Kiran o Fixed validations for terms distributions
610 -- 19-NOV-04 Anand o Bug # 4015081 - invoice number should be unique for a
611 -- a vendor - org_id combination.
612 -- 15-JUN-05 Kiran o Bug # 4303846 Used exceptions to handle errors.
613 -- 26-OCT-05 Hareesha o ATG mandated changes for SQL literals using dbms_sql.
614 -- 30-NOV-05 Hareesha o Code changes for LE uptake.
615 --------------------------------------------------------------------------------
616 PROCEDURE cache_exp_items (p_lease_num_low VARCHAR2,
617 p_lease_num_high VARCHAR2,
618 p_sch_dt_low VARCHAR2,
619 p_sch_dt_high VARCHAR2,
620 p_due_dt_low VARCHAR2,
621 p_due_dt_high VARCHAR2,
622 p_pay_prps_code VARCHAR2,
623 p_prd_name VARCHAR2,
624 p_amt_low NUMBER,
625 p_amt_high NUMBER,
626 p_vendor_id NUMBER,
627 p_inv_num VARCHAR2,
628 p_grp_param VARCHAR2)
629
630 IS
631
632 -- ref cursor
636
633
634 l_index NUMBER := 0;
635 l_lia_acc NUMBER := 0;
637 -- local variables to temporarily hold fetched values
638 v_org_id NUMBER;
639 v_pn_payment_item_id NUMBER;
640 v_pn_payment_term_id NUMBER;
641 v_pn_export_currency_amount NUMBER;
642 v_pn_export_currency_code VARCHAR2(15);
643 v_pn_vendor_id NUMBER;
644 v_pn_vendor_site_id NUMBER;
645 v_pn_project_id NUMBER;
646 v_pn_task_id NUMBER;
647 v_pn_organization_id NUMBER;
648 v_pn_expenditure_type VARCHAR2(30);
649 v_pn_expenditure_item_date DATE;
650 v_pn_tax_group_id NUMBER;
651 v_pn_tax_code_id NUMBER;
652 v_pn_tax_classification_code VARCHAR2(30);
653 v_pn_tax_included VARCHAR2(1);
654 v_pn_legal_entity_id NUMBER;
655 v_pn_distribution_set_id NUMBER;
656 v_pn_lease_num VARCHAR2(30);
657 v_pn_lease_id NUMBER;
658 v_pn_send_entries VARCHAR2(1);
659 v_pn_payment_schedule_id NUMBER;
660 v_pn_period_name VARCHAR2(15);
661 v_gl_date DATE;
662 v_pn_normalize VARCHAR2(1);
663 v_pn_due_date DATE;
664 v_pn_ap_ar_term_id NUMBER;
665 v_pn_accounted_date DATE;
666 v_pn_rate NUMBER;
667 v_pn_ap_invoice_num VARCHAR2(50);
668 v_pn_payment_purpose_code VARCHAR2(30);
669 v_pn_payment_term_type_code VARCHAR2(30);
670 v_pn_lia_account NUMBER;
671 v_conv_rate NUMBER;
672 v_conv_rate_type VARCHAR2(30);
673 v_item_grouping_rule_id NUMBER;
674 v_term_grouping_rule_id NUMBER;
675 v_lease_grouping_rule_id NUMBER;
676
677 CURSOR c_inv_num( p_invoice_num IN VARCHAR2
678 ,p_vendor_id IN NUMBER
679 ,p_org_ID IN NUMBER) IS
680 SELECT 1
681 FROM DUAL
682 WHERE EXISTS(SELECT 1
683 FROM ap_invoices_all
684 WHERE invoice_num = p_invoice_num
685 AND vendor_id = p_vendor_id
686 AND org_ID = p_org_ID);
687
688 CURSOR c_inv_num_itf( p_invoice_num IN VARCHAR2
689 ,p_vendor_id IN NUMBER
690 ,p_org_ID IN NUMBER) IS
691 SELECT 1
692 FROM DUAL
693 WHERE EXISTS(SELECT 1
694 FROM ap_invoices_interface
695 WHERE invoice_num = p_invoice_num
696 AND vendor_id = p_vendor_id
697 AND org_ID = p_org_ID);
698
699 TYPE NUMBER_tbl_typ IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
700
701 term_ID_tbl NUMBER_tbl_typ;
702 LE_tbl NUMBER_tbl_typ;
703
704 l_index_2 NUMBER;
705
706 BEGIN
707 PNP_DEBUG_PKG.log('pn_exp_to_ap.cache_exp_items (+)');
708
709 l_context := 'Caching items for export to AP';
710
711 -- init
712 exp_ap_cache.DELETE;
713 l_index := 0;
714 l_index_2 := 0;
715
716 l_cursor := dbms_sql.open_cursor;
717
718 dbms_sql.parse(l_cursor, Q_Payitem, dbms_sql.native);
719
720 bind_variables_to_cursor(p_lease_num_low,
721 p_lease_num_high,
722 p_sch_dt_low,
723 p_sch_dt_high,
724 p_due_dt_low,
725 p_due_dt_high,
726 p_pay_prps_code,
727 p_prd_name,
728 p_amt_low,
729 p_amt_high,
730 p_vendor_id,
731 p_inv_num,
732 p_grp_param,
733 l_cursor);
734
735 Q_Payitem1 := 'SELECT DISTINCT payment_term_id,
736 vendor_site_id,
737 lia_account,
738 org_id
739 FROM ( '
740 || Q_Payitem ||
741 ' ) WHERE legal_entity_id IS NULL ';
742
743 term_ID_tbl.DELETE;
744 LE_tbl.DELETE;
745
746 l_cursor_2 := dbms_sql.open_cursor;
747 dbms_sql.parse(l_cursor_2, Q_Payitem1, dbms_sql.native);
748
749 pnp_debug_pkg.log('Q_Payitem1:'||Q_Payitem1);
750
751 bind_variables_to_cursor(p_lease_num_low,
752 p_lease_num_high,
753 p_sch_dt_low,
754 p_sch_dt_high,
755 p_due_dt_low,
756 p_due_dt_high,
757 p_pay_prps_code,
758 p_prd_name,
759 p_amt_low,
763 p_grp_param,
760 p_amt_high,
761 p_vendor_id,
762 p_inv_num,
764 l_cursor_2);
765
766 l_rows_2 := dbms_sql.execute(l_cursor_2);
767
768 dbms_sql.define_column (l_cursor_2, 1, v_pn_payment_term_id);
769 dbms_sql.define_column (l_cursor_2, 2, v_pn_vendor_site_id);
770 dbms_sql.define_column (l_cursor_2, 3, v_pn_lia_account);
771 dbms_sql.define_column (l_cursor_2, 4, v_org_id);
772
773
774 LOOP
775
776 l_count_2 := dbms_sql.fetch_rows( l_cursor_2 );
777 EXIT WHEN l_count_2 <> 1;
778
779 l_index_2 := l_index_2 + 1;
780
781 dbms_sql.column_value (l_cursor_2, 1, term_ID_tbl(l_index_2));
782 dbms_sql.column_value (l_cursor_2, 2, v_pn_vendor_site_id);
783 dbms_sql.column_value (l_cursor_2, 3, v_pn_lia_account);
784 dbms_sql.column_value (l_cursor_2, 4, v_org_id);
785
786 LE_tbl(l_index_2) := pn_r12_util_pkg.get_le_for_ap(v_pn_lia_account,v_pn_vendor_site_id,v_org_id);
787
788 END LOOP;
789
790 IF dbms_sql.is_open (l_cursor_2) THEN
791 dbms_sql.close_cursor (l_cursor_2);
792 END IF;
793
794 FORALL i IN term_ID_tbl.FIRST..term_ID_tbl.LAST
795 UPDATE pn_payment_terms_all
796 SET legal_entity_id = LE_tbl(i)
797 WHERE payment_term_id = term_ID_tbl(i);
798
799 l_rows := dbms_sql.execute(l_cursor);
800
801 dbms_sql.define_column (l_cursor, 1, v_org_id);
802 dbms_sql.define_column (l_cursor, 2, v_pn_payment_item_id);
803 dbms_sql.define_column (l_cursor, 3, v_pn_payment_term_id);
804 dbms_sql.define_column (l_cursor, 4, v_pn_export_currency_amount);
805 dbms_sql.define_column (l_cursor, 5, v_pn_export_currency_code,15);
806 dbms_sql.define_column (l_cursor, 6, v_pn_vendor_id);
807 dbms_sql.define_column (l_cursor, 7, v_pn_vendor_site_id);
808 dbms_sql.define_column (l_cursor, 8, v_pn_project_id);
809 dbms_sql.define_column (l_cursor, 9, v_pn_task_id);
810 dbms_sql.define_column (l_cursor, 10,v_pn_organization_id);
811 dbms_sql.define_column (l_cursor, 11,v_pn_expenditure_type,30);
812 dbms_sql.define_column (l_cursor, 12,v_pn_expenditure_item_date);
813 dbms_sql.define_column (l_cursor, 13,v_pn_tax_group_id);
814 dbms_sql.define_column (l_cursor, 14,v_pn_tax_code_id);
815 dbms_sql.define_column (l_cursor, 15,v_pn_tax_classification_code,30);
816 dbms_sql.define_column (l_cursor, 16,v_pn_tax_included,1);
817 dbms_sql.define_column (l_cursor, 17,v_pn_distribution_set_id);
818 dbms_sql.define_column (l_cursor, 18,v_pn_lease_num,30);
819 dbms_sql.define_column (l_cursor, 19,v_pn_lease_id);
820 dbms_sql.define_column (l_cursor, 20,v_pn_send_entries,1);
821 dbms_sql.define_column (l_cursor, 21,v_pn_payment_schedule_id);
822 dbms_sql.define_column (l_cursor, 22,v_pn_period_name,15);
823 dbms_sql.define_column (l_cursor, 23,v_gl_date);
824 dbms_sql.define_column (l_cursor, 24,v_pn_normalize,1);
825 dbms_sql.define_column (l_cursor, 25,v_pn_due_date);
826 dbms_sql.define_column (l_cursor, 26,v_pn_ap_ar_term_id);
827 dbms_sql.define_column (l_cursor, 27,v_pn_accounted_date);
828 dbms_sql.define_column (l_cursor, 28,v_pn_rate);
832 dbms_sql.define_column (l_cursor, 32,v_pn_lia_account);
829 dbms_sql.define_column (l_cursor, 29,v_pn_ap_invoice_num,50);
830 dbms_sql.define_column (l_cursor, 30,v_pn_payment_purpose_code,30);
831 dbms_sql.define_column (l_cursor, 31,v_pn_payment_term_type_code,30);
833 dbms_sql.define_column (l_cursor, 33,v_pn_legal_entity_id);
834 dbms_sql.define_column (l_cursor, 34,v_conv_rate);
835 dbms_sql.define_column (l_cursor, 35,v_conv_rate_type,30);
836 dbms_sql.define_column (l_cursor, 36,v_item_grouping_rule_id);
837 dbms_sql.define_column (l_cursor, 37,v_term_grouping_rule_id);
838 dbms_sql.define_column (l_cursor, 38,v_lease_grouping_rule_id);
839
840 LOOP
841
842 l_index := l_index + 1;
843
844 l_count := dbms_sql.fetch_rows( l_cursor );
845 EXIT WHEN l_count <> 1;
846
847 dbms_sql.column_value (l_cursor, 1, exp_ap_cache(l_index).org_id);
848 dbms_sql.column_value (l_cursor, 2, exp_ap_cache(l_index).pn_payment_item_id);
849 dbms_sql.column_value (l_cursor, 3, exp_ap_cache(l_index).pn_payment_term_id);
850 dbms_sql.column_value (l_cursor, 4, exp_ap_cache(l_index).pn_export_currency_amount);
851 dbms_sql.column_value (l_cursor, 5, exp_ap_cache(l_index).pn_export_currency_code);
852 dbms_sql.column_value (l_cursor, 6, exp_ap_cache(l_index).pn_vendor_id);
853 dbms_sql.column_value (l_cursor, 7, exp_ap_cache(l_index).pn_vendor_site_id);
854 dbms_sql.column_value (l_cursor, 8, exp_ap_cache(l_index).pn_project_id);
855 dbms_sql.column_value (l_cursor, 9, exp_ap_cache(l_index).pn_task_id);
856 dbms_sql.column_value (l_cursor, 10,exp_ap_cache(l_index).pn_organization_id);
857 dbms_sql.column_value (l_cursor, 11,exp_ap_cache(l_index).pn_expenditure_type);
858 dbms_sql.column_value (l_cursor, 12,exp_ap_cache(l_index).pn_expenditure_item_date);
859 dbms_sql.column_value (l_cursor, 13,exp_ap_cache(l_index).pn_tax_group_id);
860 dbms_sql.column_value (l_cursor, 14,exp_ap_cache(l_index).pn_tax_code_id);
861 dbms_sql.column_value (l_cursor, 15,exp_ap_cache(l_index).pn_tax_classification_code);
862 dbms_sql.column_value (l_cursor, 16,exp_ap_cache(l_index).pn_tax_included);
863 dbms_sql.column_value (l_cursor, 17,exp_ap_cache(l_index).pn_distribution_set_id);
864 dbms_sql.column_value (l_cursor, 18,exp_ap_cache(l_index).pn_lease_num);
865 dbms_sql.column_value (l_cursor, 19,exp_ap_cache(l_index).pn_lease_id);
866 dbms_sql.column_value (l_cursor, 20,exp_ap_cache(l_index).pn_send_entries);
867 dbms_sql.column_value (l_cursor, 21,exp_ap_cache(l_index).pn_payment_schedule_id);
868 dbms_sql.column_value (l_cursor, 22,exp_ap_cache(l_index).pn_period_name);
869 dbms_sql.column_value (l_cursor, 23,exp_ap_cache(l_index).gl_date);
870 dbms_sql.column_value (l_cursor, 24,exp_ap_cache(l_index).pn_normalize);
871 dbms_sql.column_value (l_cursor, 25,exp_ap_cache(l_index).pn_due_date);
872 dbms_sql.column_value (l_cursor, 26,exp_ap_cache(l_index).pn_ap_ar_term_id);
873 dbms_sql.column_value (l_cursor, 27,exp_ap_cache(l_index).pn_accounted_date);
874 dbms_sql.column_value (l_cursor, 28,exp_ap_cache(l_index).pn_rate);
875 dbms_sql.column_value (l_cursor, 29,exp_ap_cache(l_index).pn_ap_invoice_num);
876 dbms_sql.column_value (l_cursor, 30,exp_ap_cache(l_index).pn_payment_purpose_code);
877 dbms_sql.column_value (l_cursor, 31,exp_ap_cache(l_index).pn_payment_term_type_code);
878 dbms_sql.column_value (l_cursor, 32,exp_ap_cache(l_index).pn_lia_account);
879 dbms_sql.column_value (l_cursor, 33,exp_ap_cache(l_index).pn_legal_entity_id);
880 dbms_sql.column_value (l_cursor, 34,exp_ap_cache(l_index).conv_rate);
881 dbms_sql.column_value (l_cursor, 35,exp_ap_cache(l_index).conv_rate_type);
882 dbms_sql.column_value (l_cursor, 36,exp_ap_cache(l_index).item_grouping_rule_id);
883 dbms_sql.column_value (l_cursor, 37,exp_ap_cache(l_index).term_grouping_rule_id);
884 dbms_sql.column_value (l_cursor, 38,exp_ap_cache(l_index).lease_grouping_rule_id);
885
886 exp_ap_cache(l_index).processed := 'N';
887
888 -- validate the item
889
890 -- Note: tax_include flag for R12 can have 3 values: 'A', 'N', and 'S'
891 -- 'A': Yes, 'N': No, 'S': Use system default
892 -- Add logic to handle legacy data, where 'Y' : Yes, and 'N' / null = No
893
894 IF pn_r12_util_pkg.is_r12 THEN
895 IF exp_ap_cache(l_index).pn_tax_included = 'Y' THEN
896 exp_ap_cache(l_index).pn_tax_included := 'A';
897 END IF;
898 END IF;
899
900 l_total_exp_amt := 0;
901 l_total_exp_percent := 0;
902
903 IF exp_ap_cache(l_index).pn_distribution_set_id IS NULL
904 AND exp_ap_cache(l_index).pn_project_id IS NULL THEN
905
906 -- validate distributions
907 l_lia_cnt := 0;
908 l_acc_cnt := 0;
909 l_exp_cnt := 0;
910
911 FOR acnt_rec IN get_acnt_info(exp_ap_cache(l_index).pn_payment_term_id) LOOP
912
913 IF acnt_rec.account_class = 'LIA' THEN
914 l_lia_cnt := l_lia_cnt + 1;
915 ELSIF acnt_rec.account_class = 'EXP' THEN
916 l_exp_cnt := l_exp_cnt + 1;
917 ELSIF acnt_rec.account_class = 'ACC' THEN
918 l_acc_cnt := l_acc_cnt + 1;
919 END IF;
920
921 END LOOP; -- for account in
922
923 END IF; -- if dist_set/project is null
924
925 BEGIN
926
930 IF NVL(exp_ap_cache(l_index).pn_normalize,'N') = 'Y' AND
927 IF exp_ap_cache(l_index).pn_distribution_set_id IS NULL AND
928 exp_ap_cache(l_index).pn_project_id IS NULL THEN
929
931 (l_exp_cnt = 0 OR l_acc_cnt = 0) THEN
932
933 fnd_message.set_name ('PN', 'PN_ALL_ACNT_DIST_MSG');
934 PNP_DEBUG_PKG.put_log_msg(fnd_message.get);
935 RAISE BAD_ITEM_EXCEPTION;
936
937 ELSIF NVL(exp_ap_cache(l_index).pn_normalize,'N') = 'N' AND
938 l_exp_cnt = 0 THEN
939
940 fnd_message.set_name ('PN', 'PN_EXP_DIST_MSG');
941 PNP_DEBUG_PKG.put_log_msg(fnd_message.get);
942 RAISE BAD_ITEM_EXCEPTION;
943
944 END IF;
945
946 ELSIF UPPER(exp_ap_cache(l_index).conv_rate_type) = 'USER' AND
947 exp_ap_cache(l_index).conv_rate IS NULL THEN
948
949 fnd_message.set_name ('PN', 'PN_CONV_RATE_REQD');
950 pnp_debug_pkg.put_log_msg(fnd_message.get);
951 RAISE BAD_ITEM_EXCEPTION;
952
953 ELSIF l_grpby_INVOICENUM THEN
954 IF exp_ap_cache(l_index).pn_ap_invoice_num IS NOT NULL THEN
955 FOR i IN c_inv_num(exp_ap_cache(l_index).pn_ap_invoice_num,
956 exp_ap_cache(l_index).pn_vendor_id,
957 exp_ap_cache(l_index).org_id)
958 LOOP
959 fnd_message.set_name ('PN', 'PN_INV_NUM_ALREADY_EXIST');
960 pnp_debug_pkg.put_log_msg(fnd_message.get);
961 RAISE BAD_ITEM_EXCEPTION;
962 END LOOP;
963 FOR i IN c_inv_num_itf(exp_ap_cache(l_index).pn_ap_invoice_num,
964 exp_ap_cache(l_index).pn_vendor_id,
965 exp_ap_cache(l_index).org_id)
966 LOOP
967 fnd_message.set_name ('PN', 'PN_INV_NUM_ALREADY_EXIST');
968 pnp_debug_pkg.put_log_msg(fnd_message.get);
969 RAISE BAD_ITEM_EXCEPTION;
970 END LOOP;
971 END IF;
972 END IF;
973
974 EXCEPTION
975 WHEN BAD_ITEM_EXCEPTION THEN
976 fnd_message.set_name ('PN','PN_EXPAP_ERR');
977 fnd_message.set_token ('ID',exp_ap_cache(l_index).pn_payment_item_id);
978 pnp_debug_pkg.put_log_msg(fnd_message.get);
979
980 l_error_ctr := l_error_ctr + 1;
981
982 exp_ap_cache.DELETE(l_index);
983 l_index := l_index - 1;
984
985 WHEN OTHERS THEN
986 fnd_message.set_name ('PN','PN_EXPAP_ERR');
987 fnd_message.set_token ('ID',exp_ap_cache(l_index).pn_payment_item_id);
988 pnp_debug_pkg.put_log_msg(fnd_message.get);
989
990 pnp_debug_pkg.put_log_msg(SQLERRM);
991 RAISE;
992 END;
993 END LOOP;
994
995 IF dbms_sql.is_open (l_cursor) THEN
996 dbms_sql.close_cursor (l_cursor);
997 END IF;
998
999 l_total_ctr := l_total_ctr + l_index - 1;
1000
1001 PNP_DEBUG_PKG.log('pn_exp_to_ap.cache_exp_items (-)');
1002
1003 EXCEPTION
1004 WHEN others THEN
1005 RAISE;
1006
1007 END cache_exp_items;
1008
1009 -------------------------------------------------------------------------------
1010 -- NAME : group_and_export_items
1011 -- DESCRIPTION : Loop through the PL/SQL table to find the groups of items
1012 -- that can be grouped into a single transcation.
1013 --
1014 -- Once a group has been identified,
1015 -- Insert into AP Invoice interface one record for the
1016 -- Invoice with the Expense account.
1017 -- For each Invoice, insert into the AP Invoice Lines
1018 -- interface table as many records as the Distributions
1019 -- for Liability and Accrued Liability per item.
1020 --
1021 -- PURPOSE : Groups and exports items to AP interface table
1022 -- INVOKED FROM : exp_to_ap
1023 -- ARGUMENTS : errbuf - Error Buffer
1024 -- retcode - Rerurn Code, indicates if the CP should end in
1025 -- success or failure.
1026 -- p_group_id - Export Group ID
1027 -- p_param_where_clause - Where clause from SYSTEM.last_query
1028 -- from the Export to AP form.
1029 -- REFERENCE : PN_COMMON.debug()
1030 -- HISTORY :
1031 -- 19-DEC-03 Kiran o Created
1032 -- 17-Jun-04 Kiran o Bug # 4303846
1033 -- If GRP_BY_INVOICE_NUM and users specifies INVOICE_NUM
1034 -- If some other mandatory attribute stops us from
1035 -- creating a single group for all the items with
1036 -- the same invoice number then,
1037 -- DO NOT PROCESS ANY ITEM with that INVOICE_NUM
1038 -- 30-NOV-05 Hareesha o Code changes for LE uptake.
1039 -- 24-SEP-07 rkartha o Bug # 6392393. Added tax_classification_code in the INSERT
1040 -- statement into 'ap_invoice_lines_interface' table.
1041 --------------------------------------------------------------------------------
1042 PROCEDURE group_and_export_items(errbuf IN OUT NOCOPY VARCHAR2,
1043 retcode IN OUT NOCOPY NUMBER,
1047 l_lineNumber NUMBER := 0;
1044 p_group_id VARCHAR2,
1045 p_param_where_clause VARCHAR2) IS
1046
1048 l_prior_payment_schedule_id NUMBER := -999;
1049 l_start NUMBER := 0;
1050 l_next NUMBER := 0;
1051 l_item_prcsed NUMBER := 0;
1052 l_count NUMBER := 0;
1053 l_header_amount NUMBER;
1054 l_line_amount NUMBER;
1055
1056 CURSOR c_inv_num_itf( p_invoice_num IN VARCHAR2
1057 ,p_vendor_id IN NUMBER
1058 ,p_org_ID IN NUMBER) IS
1059 SELECT invoice_id
1060 ,invoice_num
1061 FROM ap_invoices_interface
1062 WHERE invoice_num = p_invoice_num
1063 AND vendor_id = p_vendor_id
1064 AND org_ID = p_org_ID;
1065
1066 TYPE inv_rec IS RECORD
1067 ( invoice_id ap_invoices_interface.invoice_id%TYPE
1068 ,invoice_num ap_invoices_interface.invoice_num%TYPE
1069 ,items_proc NUMBER);
1070
1071 TYPE inv_tab IS TABLE OF inv_rec INDEX BY BINARY_INTEGER;
1072
1073 inserted_inv_t inv_tab;
1074 bad_inv_t inv_tab;
1075 l_temp_count NUMBER;
1076
1077 /* remove this after SEED bug for message PN_CANNOT_GRP_ON_INV is fixed */
1078 CURSOR exists_msg IS
1079 SELECT message_name
1080 FROM fnd_new_messages
1081 WHERE application_id = 240
1082 AND message_name = 'PN_CANNOT_GRP_ON_INV';
1083 l_msg_exists BOOLEAN;
1084
1085 BEGIN
1086
1087 PNP_DEBUG_PKG.log('pn_exp_to_ap.group_and_export_items (+)');
1088
1089 -- we already have the required items in the cache
1090
1091 l_context := 'Exporting to AP with grouping';
1092
1093 l_start := 1;
1094 l_next := 2;
1095 l_item_prcsed := 0;
1096 l_count := exp_ap_cache.COUNT;
1097
1098 pnp_debug_pkg.log('The number of items to be processed :' || l_count);
1099
1100 IF l_count < 1 THEN
1101 RETURN;
1102 END IF;
1103
1104 l_context := 'Finding the Groups of items';
1105
1106 /* init the tables */
1107 inserted_inv_t.DELETE;
1108 bad_inv_t.DELETE;
1109
1110 WHILE (l_item_prcsed < l_count) LOOP
1111
1112 IF ((l_next <= l_count) AND
1113 -- mandatory attrs
1114 (exp_ap_cache(l_start).pn_vendor_id = exp_ap_cache(l_next).pn_vendor_id) AND
1115 (exp_ap_cache(l_start).pn_vendor_site_id = exp_ap_cache(l_next).pn_vendor_site_id) AND
1116 ((exp_ap_cache(l_start).pn_ap_ar_term_id = exp_ap_cache(l_next).pn_ap_ar_term_id) OR
1117 (exp_ap_cache(l_start).pn_ap_ar_term_id IS NULL AND
1118 exp_ap_cache(l_next).pn_ap_ar_term_id IS NULL)) AND
1119 (exp_ap_cache(l_start).gl_date = exp_ap_cache(l_next).gl_date) AND
1120 (exp_ap_cache(l_start).pn_due_date = exp_ap_cache(l_next).pn_due_date) AND
1121 ((exp_ap_cache(l_start).pn_export_currency_code
1122 = exp_ap_cache(l_next).pn_export_currency_code) OR
1123 (exp_ap_cache(l_start).pn_export_currency_code IS NULL AND
1124 exp_ap_cache(l_next).pn_export_currency_code IS NULL)) AND
1125 ((exp_ap_cache(l_start).conv_rate = exp_ap_cache(l_next).conv_rate) OR
1126 (exp_ap_cache(l_start).conv_rate IS NULL AND
1127 exp_ap_cache(l_next).conv_rate IS NULL)) AND
1128 ((exp_ap_cache(l_start).pn_accounted_date = exp_ap_cache(l_next).pn_accounted_date) OR
1129 (exp_ap_cache(l_start).pn_accounted_date IS NULL AND
1130 exp_ap_cache(l_next).pn_accounted_date IS NULL)) AND
1131 ((exp_ap_cache(l_start).conv_rate_type = exp_ap_cache(l_next).conv_rate_type) OR
1132 (exp_ap_cache(l_start).conv_rate_type IS NULL AND
1133 exp_ap_cache(l_next).conv_rate_type IS NULL)) AND
1134 ((exp_ap_cache(l_start).pn_legal_entity_id = exp_ap_cache(l_next).pn_legal_entity_id) OR
1135 (exp_ap_cache(l_start).pn_legal_entity_id IS NULL AND
1136 exp_ap_cache(l_next).pn_legal_entity_id IS NULL)) AND
1137 -- mandatory attrs
1138
1139 -- optional attrs
1140 ((l_grpby_INVOICENUM AND
1141 ((exp_ap_cache(l_start).pn_ap_invoice_num
1142 = exp_ap_cache(l_next).pn_ap_invoice_num) OR
1143 (exp_ap_cache(l_start).pn_ap_invoice_num IS NULL AND
1144 exp_ap_cache(l_next).pn_ap_invoice_num IS NULL))) OR
1145 (NOT l_grpby_INVOICENUM)) AND
1146 ((l_grpby_LEASENUM AND
1147 (exp_ap_cache(l_start).pn_lease_num = exp_ap_cache(l_next).pn_lease_num)) OR
1148 (NOT l_grpby_LEASENUM)) AND
1149 ((l_grpby_PAYPURPOSE AND
1150 (exp_ap_cache(l_start).pn_payment_purpose_code
1151 = exp_ap_cache(l_next).pn_payment_purpose_code)) OR
1152 (NOT l_grpby_PAYPURPOSE)) AND
1153 ((l_grpby_PAYTYPE AND
1154 (exp_ap_cache(l_start).pn_payment_term_type_code
1155 = exp_ap_cache(l_next).pn_payment_term_type_code)) OR
1156 (NOT l_grpby_PAYTYPE)) AND
1157 (exp_ap_cache(l_start).pn_lia_account = exp_ap_cache(l_next).pn_lia_account))
1158 THEN
1159
1160 -- increment 'next' counter. we are still getting the super group.
1161 l_next := l_next + 1;
1162
1163 ELSE -- we have group
1164
1165 l_context := 'Get the amount for header.';
1166
1167 l_header_amount := 0;
1168
1172 END LOOP;
1169 FOR item IN l_start .. l_next-1 LOOP
1170 l_header_amount
1171 := l_header_amount + exp_ap_cache(item).pn_export_currency_amount;
1173
1174 l_context := 'Insert into the header';
1175
1176 -- Create the header
1177
1178 l_lineNumber := 0;
1179
1180 l_context := 'Inserting into ap_invoices_interface ...';
1181
1182 fnd_currency.get_info(exp_ap_cache(l_start).pn_export_currency_code,
1183 l_precision,
1184 l_ext_precision,
1185 l_min_acct_unit);
1186
1187 IF l_grpby_INVOICENUM THEN
1188 l_invoice_num := exp_ap_cache(l_start).pn_ap_invoice_num;
1189 ELSE
1190 l_invoice_num := NULL;
1191 END IF;
1192
1193 BEGIN
1194 IF l_invoice_num IS NOT NULL THEN
1195 /* if we find the l_invoice_num in the bad invoice num table,
1196 dont bother processing the group */
1197 FOR bad_rec IN 1..bad_inv_t.COUNT LOOP
1198 IF bad_inv_t(bad_rec).invoice_num = l_invoice_num THEN
1199 RAISE BAD_INVOICE_NUM;
1200 END IF;
1201 END LOOP;
1202 /* there is a chance a bad invoice number did not yet make into
1203 the blacklist */
1204 FOR inv_rec IN
1205 c_inv_num_itf
1206 ( p_invoice_num => l_invoice_num
1207 ,p_vendor_id => exp_ap_cache(l_start).pn_vendor_id
1208 ,p_org_ID => exp_ap_cache(l_start).org_id)
1209 LOOP
1210 IF bad_inv_t.LAST IS NULL THEN
1211 l_temp_count := 1;
1212 ELSE
1213 l_temp_count := bad_inv_t.LAST + 1;
1214 END IF;
1215 bad_inv_t(l_temp_count).invoice_id := inv_rec.invoice_id;
1216 bad_inv_t(l_temp_count).invoice_num := inv_rec.invoice_num;
1217 /* delete the data for the bad invoice number */
1218 IF inv_rec.invoice_id = inserted_inv_t(inserted_inv_t.LAST).invoice_id AND
1219 inv_rec.invoice_num = inserted_inv_t(inserted_inv_t.LAST).invoice_num
1220 THEN
1221 l_error_ctr := l_error_ctr + inserted_inv_t(inserted_inv_t.LAST).items_proc;
1222 ROLLBACK TO beforeinsert;
1223 RAISE BAD_INVOICE_NUM;
1224 ELSE
1225 RAISE FATAL_ERROR;
1226 END IF;
1227 END LOOP;
1228 END IF;
1229
1230 SAVEPOINT beforeinsert;
1231
1232 INSERT INTO ap_invoices_interface
1233 (invoice_id
1234 ,invoice_num
1235 ,invoice_amount
1236 ,invoice_currency_code
1237 ,description
1238 ,source
1239 ,vendor_id
1240 ,vendor_site_id
1241 ,accts_pay_code_combination_id
1242 ,last_updated_by
1243 ,last_update_date
1244 ,last_update_login
1245 ,created_by
1246 ,creation_date
1247 ,org_id
1248 ,group_id
1249 ,gl_date
1250 ,terms_date
1251 ,invoice_date
1252 ,invoice_received_date
1253 ,terms_id
1254 ,legal_entity_id
1255 ,exchange_rate
1256 ,exchange_rate_type
1257 ,exchange_date)
1258 VALUES
1259 (AP_INVOICES_INTERFACE_S.nextval
1260 ,NVL(l_invoice_num
1261 ,'PN-'||PN_PAYMENT_ITEMS_NUM_S.nextval)
1262 ,ROUND(l_header_amount,l_precision)
1263 ,exp_ap_cache(l_start).pn_export_currency_code
1264 ,'Lease Number: ' || exp_ap_cache(l_start).pn_lease_num
1265 ,'Oracle Property Manager'
1266 ,exp_ap_cache(l_start).pn_vendor_id
1267 ,exp_ap_cache(l_start).pn_vendor_site_id
1268 ,exp_ap_cache(l_start).pn_lia_account
1269 ,l_last_updated_by
1270 ,l_last_update_date
1271 ,l_last_update_login
1272 ,l_created_by
1273 ,l_creation_date
1274 ,exp_ap_cache(l_start).org_id
1275 ,p_group_id
1276 ,exp_ap_cache(l_start).gl_date -- gl_date
1277 ,exp_ap_cache(l_start).pn_due_date -- terms date
1278 ,exp_ap_cache(l_start).pn_due_date -- invoice date
1279 ,exp_ap_cache(l_start).pn_due_date -- invoice received date
1280 ,exp_ap_cache(l_start).pn_ap_ar_term_id
1281 ,exp_ap_cache(l_start).pn_legal_entity_id
1282 ,exp_ap_cache(l_start).conv_rate
1283 ,exp_ap_cache(l_start).conv_rate_type
1284 ,exp_ap_cache(l_start).pn_accounted_date)
1285 RETURNING invoice_id, invoice_num INTO l_invoice_id, l_invoice_num;
1286
1287 IF inserted_inv_t.LAST IS NULL THEN
1288 l_temp_count := 1;
1289 ELSE
1290 l_temp_count := inserted_inv_t.LAST + 1;
1291 END IF;
1292
1293 inserted_inv_t(l_temp_count).invoice_id := l_invoice_id;
1297 fnd_message.set_name('PN','PN_EXPAP_HEAD_PARAM');
1294 inserted_inv_t(l_temp_count).invoice_num := l_invoice_num;
1295 inserted_inv_t(l_temp_count).items_proc := l_next - l_start;
1296
1298 fnd_message.set_token('INV_ID',l_invoice_id);
1299 fnd_message.set_token('INV_NUM',l_invoice_num);
1300 fnd_message.set_token('AMT',l_header_amount);
1301 pnp_debug_pkg.put_log_msg(fnd_message.get);
1302
1303 PNP_DEBUG_PKG.put_log_msg(' ');
1304 -- now for the distributions
1305
1306 FOR item IN l_start .. l_next-1 LOOP
1307
1308 l_total_exp_amt := 0;
1309 l_total_exp_percent := 0;
1310
1311 IF exp_ap_cache(item).pn_distribution_set_id IS NULL
1312 AND exp_ap_cache(item).pn_project_id IS NULL THEN
1313 -- validate distributions
1314 -- Initailize the tables
1315 lia_acnt_tab.delete;
1316 acc_acnt_tab.delete;
1317 exp_acnt_tab.delete;
1318
1319 l_lia_cnt := 0;
1320 l_acc_cnt := 0;
1321 l_exp_cnt := 0;
1322
1323 FOR acnt_rec IN get_acnt_info(exp_ap_cache(item).pn_payment_term_id) LOOP
1324 IF acnt_rec.account_class = 'LIA' THEN
1325 l_lia_cnt := l_lia_cnt + 1;
1326 lia_acnt_tab(l_lia_cnt) := acnt_rec;
1327
1328 ELSIF acnt_rec.account_class = 'EXP' THEN
1329 l_exp_cnt := l_exp_cnt + 1;
1330 exp_acnt_tab(l_exp_cnt) := acnt_rec;
1331
1332 ELSIF acnt_rec.account_class = 'ACC' THEN
1333 l_acc_cnt := l_acc_cnt + 1;
1334 acc_acnt_tab(l_acc_cnt) := acnt_rec;
1335
1336 END IF;
1337 END LOOP; -- for account in
1338
1339 -- In case of terms that are not normalized, we may not get the liability
1340 -- and/or Accrual A/C
1341
1342 IF l_lia_cnt = 0 THEN
1343 lia_acnt_tab(1) := NULL;
1344 END IF;
1345
1346 IF l_acc_cnt = 0 THEN
1347 acc_acnt_tab(1) := NULL;
1348 END IF;
1349
1350 ELSE -- distribution_set/project info exists
1351
1352 lia_acnt_tab(1) := null;
1353 exp_acnt_tab(1) := null;
1354 acc_acnt_tab(1) := null;
1355
1356 END IF; -- if dist_set/project is null
1357
1358 IF pn_r12_util_pkg.is_r12 THEN
1359 IF exp_ap_cache(item).pn_tax_classification_code IS NOT NULL THEN
1360 l_tax_code_override_flag := 'Y';
1361 END IF;
1362
1363 exp_ap_cache(item).pn_tax_code_id := null;
1364 exp_ap_cache(item).pn_tax_group_id := null;
1365
1366 ELSE
1367
1368 -- alls well and we are ready to insert into the AP ITF
1369 -- Get tax name for the expense account
1370
1371 --
1372 FOR rec IN get_tax_name(nvl(exp_ap_cache(item).pn_tax_code_id,
1373 exp_ap_cache(item).pn_tax_group_id)) LOOP
1374 l_tax_name := rec.name;
1375 END LOOP;
1376
1377 -- If Tax Code Id or Tax Group Id is not null then populate
1378 -- tax_code_override_flag.
1379
1380 IF exp_ap_cache(item).pn_tax_code_id IS NOT NULL OR
1381 exp_ap_cache(item).pn_tax_group_id IS NOT NULL THEN
1382 l_tax_code_override_flag := 'Y';
1383 END IF;
1384
1385 exp_ap_cache(item).pn_tax_classification_code := null;
1386 END IF;
1387
1388 -- Create a line for accrual amount if the term is normalized
1389
1390 IF (nvl(exp_ap_cache(item).pn_normalize,'N') = 'Y' AND
1391 nvl(exp_ap_cache(item).pn_send_entries,'Y') = 'Y') THEN
1392
1393 FOR i IN 1..acc_acnt_tab.COUNT LOOP
1394
1395 l_lineNumber := l_lineNumber + 1;
1396
1397 l_context := 'Inserting into ap_invoice_lines_interface ...';
1398
1399 INSERT INTO ap_invoice_lines_interface
1400 (invoice_id
1401 ,invoice_line_id
1402 ,line_type_lookup_code
1403 ,amount
1404 ,description
1405 ,dist_code_combination_id
1406 ,last_updated_by
1407 ,last_update_date
1408 ,last_update_login
1409 ,created_by
1410 ,creation_date
1411 ,line_number
1412 ,org_id
1413 ,amount_includes_tax_flag -- Tax Inclusive
1414 ,distribution_set_id
1415 ,project_id
1416 ,task_id
1417 ,expenditure_type
1418 ,expenditure_item_date
1419 ,expenditure_organization_id
1420 ,tax_code_id
1424 VALUES
1421 ,tax_code
1422 ,tax_classification_code
1423 ,tax_code_override_flag)
1425 (l_invoice_id
1426 ,AP_INVOICE_LINES_INTERFACE_S.nextval
1427 ,'ITEM'
1428 ,ROUND(((exp_ap_cache(item).pn_export_currency_amount)
1429 * nvl(acc_acnt_tab(i).percentage,100)/100), l_precision)
1430 ,'Lease Number: ' || exp_ap_cache(item).pn_lease_num
1431 ,acc_acnt_tab(i).account_id
1432 ,l_last_updated_by
1433 ,l_last_update_date
1434 ,l_last_update_login
1435 ,l_created_by
1436 ,l_creation_date
1437 ,l_lineNumber
1438 ,exp_ap_cache(item).org_id
1439 ,exp_ap_cache(item).pn_tax_included
1440 ,exp_ap_cache(item).pn_distribution_set_id
1441 ,exp_ap_cache(item).pn_project_id
1442 ,exp_ap_cache(item).pn_task_id
1443 ,exp_ap_cache(item).pn_expenditure_type
1444 ,exp_ap_cache(item).pn_expenditure_item_date
1445 ,exp_ap_cache(item).pn_organization_id
1446 ,nvl(exp_ap_cache(item).pn_tax_code_id,
1447 exp_ap_cache(item).pn_tax_group_id)
1448 ,l_tax_name
1449 ,exp_ap_cache(item).pn_tax_classification_code
1450 ,l_tax_code_override_flag)
1451 RETURNING invoice_line_id, amount INTO l_invoice_line_id, l_line_amount;
1452
1453 fnd_message.set_name('PN','PN_EXPAP_LINE_PARAM');
1454 fnd_message.set_token('INV_ID',l_invoice_id);
1455 fnd_message.set_token('NUM',l_invoice_num);
1456 fnd_message.set_token('ID',l_invoice_line_id);
1457 fnd_message.set_token('AMT',l_line_amount);
1458 fnd_message.set_token('PAY_ID',exp_ap_cache(item).pn_payment_item_id);
1459 pnp_debug_pkg.put_log_msg(fnd_message.get);
1460
1461 END LOOP; -- for accrual_tab
1462
1463 END IF; -- accrual entered if normalized
1464
1465 -- Create a line for expense A/C
1466
1467 IF ((nvl(exp_ap_cache(item).pn_normalize,'N') <> 'Y') OR
1468 ((exp_ap_cache(item).pn_normalize = 'Y') AND
1469 nvl(exp_ap_cache(item).pn_send_entries,'Y') = 'N')) THEN
1470
1471 l_exp_amt := round(exp_ap_cache(item).pn_export_currency_amount,l_precision);
1472
1473 FOR i IN 1..exp_acnt_tab.COUNT LOOP
1474
1475 l_lineNumber := l_lineNumber + 1;
1476 l_context := 'Inserting into ap_invoice_lines_interface ...';
1477
1478 l_amt := ROUND((l_exp_amt * nvl(exp_acnt_tab(i).percentage,100)/100),l_precision);
1479 l_total_exp_amt := l_total_exp_amt + l_amt;
1480 l_total_exp_percent := l_total_exp_percent + nvl(exp_acnt_tab(i).percentage,100);
1481
1482 IF l_total_exp_percent = 100 THEN
1483 l_diff_amt := l_total_exp_amt - l_exp_amt;
1484 l_amt := l_amt - l_diff_amt;
1485 END IF;
1486
1487 INSERT INTO ap_invoice_lines_interface
1488 ( invoice_id
1489 ,invoice_line_id
1490 ,line_type_lookup_code
1491 ,amount
1492 ,description
1493 ,dist_code_combination_id
1494 ,last_updated_by
1495 ,last_update_date
1496 ,last_update_login
1497 ,created_by
1498 ,creation_date
1499 ,line_number
1500 ,org_id
1501 ,amount_includes_tax_flag -- Tax Inclusive
1502 ,distribution_set_id
1503 ,project_id
1504 ,task_id
1505 ,expenditure_type
1506 ,expenditure_item_date
1507 ,expenditure_organization_id
1508 ,tax_code_id
1509 ,tax_code
1510 ,tax_classification_code /*--Bug 6392393--*/
1511 ,tax_code_override_flag)
1512 VALUES
1513 (l_invoice_id
1514 ,AP_INVOICE_LINES_INTERFACE_S.nextval
1515 ,'ITEM'
1516 ,l_amt
1517 ,'Lease Number: ' || exp_ap_cache(item).pn_lease_num
1518 ,exp_acnt_tab(i).account_id
1519 ,l_last_updated_by
1520 ,l_last_update_date
1521 ,l_last_update_login
1522 ,l_created_by
1523 ,l_creation_date
1524 ,l_lineNumber
1525 ,exp_ap_cache(item).org_id
1526 ,exp_ap_cache(item).pn_tax_included
1527 ,exp_ap_cache(item).pn_distribution_set_id
1528 ,exp_ap_cache(item).pn_project_id
1529 ,exp_ap_cache(item).pn_task_id
1533 ,nvl(exp_ap_cache(item).pn_tax_code_id,
1530 ,exp_ap_cache(item).pn_expenditure_type
1531 ,exp_ap_cache(item).pn_expenditure_item_date
1532 ,exp_ap_cache(item).pn_organization_id
1534 exp_ap_cache(item).pn_tax_group_id)
1535 ,l_tax_name
1536 ,exp_ap_cache(item).pn_tax_classification_code /*--Bug 6392393--*/
1537 ,l_tax_code_override_flag)
1538 RETURNING invoice_line_id, amount INTO l_invoice_line_id, l_line_amount;
1539
1540 fnd_message.set_name('PN','PN_EXPAP_LINE_PARAM');
1541 fnd_message.set_token('INV_ID',l_invoice_id);
1542 fnd_message.set_token('NUM',l_invoice_num);
1543 fnd_message.set_token('ID',l_invoice_line_id);
1544 fnd_message.set_token('AMT',l_line_amount);
1545 fnd_message.set_token('PAY_ID',exp_ap_cache(item).pn_payment_item_id);
1546 pnp_debug_pkg.put_log_msg(fnd_message.get);
1547
1548 END LOOP;
1549
1550 END IF; -- expense a/c
1551
1552 ---------------------------------------------------------------
1553 -- Set Transferred Flag to 'Y' for all payment items exported
1554 -- to AP
1555 ---------------------------------------------------------------
1556 UPDATE pn_payment_items_all
1557 SET transferred_to_ap_flag = 'Y' ,
1558 ap_invoice_num = l_invoice_num,
1559 last_updated_by = l_last_updated_by,
1560 last_update_login = l_last_update_login,
1561 last_update_date = l_last_update_date ,
1562 export_group_id = p_group_id
1563 WHERE payment_item_id = exp_ap_cache(item).pn_payment_item_id;
1564
1565 IF (SQL%NOTFOUND) then
1566 fnd_message.set_name('PN', 'PN_TRANSFER_TO_AP_FLAG_NOT_SET');
1567 errbuf := fnd_message.get;
1568 pnp_debug_pkg.put_log_msg(errbuf);
1569 ROLLBACK;
1570 retcode := 2;
1571 RETURN;
1572 END IF;
1573
1574 IF (exp_ap_cache(item).pn_payment_schedule_id
1575 <> l_prior_payment_schedule_id) THEN
1576
1577 l_prior_payment_schedule_id := exp_ap_cache(item).pn_payment_schedule_id;
1578
1579 UPDATE pn_payment_schedules_all
1580 SET transferred_by_user_id = l_last_updated_by,
1581 transfer_date = l_last_update_date,
1582 last_updated_by = l_last_updated_by,
1583 last_update_login = l_last_update_login,
1584 last_update_date = l_last_update_date
1585 WHERE payment_schedule_id = exp_ap_cache(item).pn_payment_schedule_id;
1586
1587 IF (SQL%NOTFOUND) then
1588 fnd_message.set_name('PN', 'PN_TRANSFER_TO_AP_INFO_NOT_SET');
1589 errbuf := fnd_message.get;
1590 pnp_debug_pkg.put_log_msg(errbuf);
1591 ROLLBACK;
1592 retcode := 2;
1593 RETURN;
1594 END IF;
1595
1596 END IF;
1597
1598 END LOOP; -- now for the distributions
1599
1600 EXCEPTION
1601 WHEN BAD_INVOICE_NUM THEN
1602 retcode := 1;
1603 l_error_ctr := l_error_ctr + l_next - l_start;
1604 /* this is for the backport - remove after seed bug is fixed */
1605 l_msg_exists := FALSE;
1606 FOR i IN exists_msg LOOP
1607 l_msg_exists := TRUE;
1608 END LOOP;
1609 IF l_msg_exists THEN
1610 fnd_message.set_name('PN', 'PN_CANNOT_GRP_ON_INV');
1611 fnd_message.set_token('INV_NUM',l_invoice_num);
1612 pnp_debug_pkg.put_log_msg(fnd_message.get);
1613 ELSE
1614 pnp_debug_pkg.put_log_msg
1615 ('The system is unable to group and process items with invoice number '||
1616 l_invoice_num||
1617 ' because some mandatory attributes do not match.');
1618 END IF;
1619
1620 WHEN FATAL_ERROR THEN
1621 RAISE;
1622
1623 WHEN OTHERS THEN
1624 errbuf := SQLERRM;
1625 pnp_debug_pkg.put_log_msg(errbuf);
1626 ROLLBACK;
1627 RAISE;
1628
1629 END;
1630
1631 /* get our counters right now
1632 else we will loop for eternity */
1633 l_item_prcsed := l_next - 1;
1634 l_start := l_next;
1635 l_next := l_next + 1;
1636 END IF;
1637 END LOOP;
1638
1639 PNP_DEBUG_PKG.log('pn_exp_to_ap.group_and_export_items (-)');
1640
1641 EXCEPTION
1642 WHEN FATAL_ERROR THEN
1643 RAISE;
1644 WHEN others THEN
1645 RAISE;
1646
1647 END group_and_export_items;
1648
1649 --------------------------------------------------------------------------------
1650 -- NAME : export_items_nogrp
1654 -- PURPOSE : Export items to AP without grouping.
1651 -- DESCRIPTION : Creates entries in AP Interface for Invoice and Invoice Lines
1652 -- This procedure is the default functionality when Grouping
1653 -- Rule is not defined at any level.
1655 -- INVOKED FROM : exp_to_ap
1656 -- ARGUMENTS : errbuf - Error Buffer
1657 -- retcode - Rerurn Code, indicates if the CP should end in
1658 -- success or failure.
1659 -- p_group_id - Export Group ID
1660 -- p_param_where_clause - Where clause from SYSTEM.last_query
1661 -- from the Export to AP form.
1662 -- REFERENCE : PN_COMMON.debug()
1663 -- HISTORY :
1664 --
1665 -- 19-DEC-03 Kiran o Created
1666 --------------------------------------------------------------------------------
1667 PROCEDURE export_items_nogrp(errbuf IN OUT NOCOPY VARCHAR2,
1668 retcode IN OUT NOCOPY NUMBER,
1669 p_group_id VARCHAR2,
1670 p_param_where_clause VARCHAR2) IS
1671
1672 l_lineNumber NUMBER := 0;
1673 l_prior_payment_schedule_id NUMBER := -999;
1674 l_header_amount NUMBER;
1675 l_line_amount NUMBER;
1676
1677 BEGIN
1678 PNP_DEBUG_PKG.log('pn_exp_to_ap.export_items_nogrp (+)');
1679
1680 /* we already have the required items in the cache */
1681
1682 l_context := 'Exporting to AP with default functionality';
1683
1684 pnp_debug_pkg.log(' Exporting to AP with default functionality exp_ap_cache.COUNT:'||exp_ap_cache.COUNT);
1685
1686 FOR item IN 1..exp_ap_cache.COUNT LOOP
1687
1688 l_total_exp_amt := 0;
1689 l_total_exp_percent := 0;
1690
1691 IF exp_ap_cache(item).pn_distribution_set_id IS NULL AND
1692 exp_ap_cache(item).pn_project_id IS NULL THEN
1693 -- validate distributions
1694 -- Initailize the tables
1695 lia_acnt_tab.delete;
1696 acc_acnt_tab.delete;
1697 exp_acnt_tab.delete;
1698
1699 l_lia_cnt := 0;
1700 l_acc_cnt := 0;
1701 l_exp_cnt := 0;
1702
1703 FOR acnt_rec IN get_acnt_info(exp_ap_cache(item).pn_payment_term_id) LOOP
1704 IF acnt_rec.account_class = 'LIA' THEN
1705 l_lia_cnt := l_lia_cnt + 1;
1706 lia_acnt_tab(l_lia_cnt) := acnt_rec;
1707
1708 ELSIF acnt_rec.account_class = 'EXP' THEN
1709 l_exp_cnt := l_exp_cnt + 1;
1710 exp_acnt_tab(l_exp_cnt) := acnt_rec;
1711
1712 ELSIF acnt_rec.account_class = 'ACC' THEN
1713 l_acc_cnt := l_acc_cnt + 1;
1714 acc_acnt_tab(l_acc_cnt) := acnt_rec;
1715
1716 END IF;
1717 END LOOP; -- for account in
1718
1719 /* In case of terms that are not normalized, we may not get the liability
1720 and/or Accrual A/C */
1721
1722 IF l_lia_cnt = 0 THEN
1723 lia_acnt_tab(1) := NULL;
1724 END IF;
1725
1726 IF l_acc_cnt = 0 THEN
1727 acc_acnt_tab(1) := NULL;
1728 END IF;
1729
1730 ELSE -- distribution_set/project info exists
1731
1732 lia_acnt_tab(1) := null;
1733 exp_acnt_tab(1) := null;
1734 acc_acnt_tab(1) := null;
1735
1736 END IF; -- if dist_set/project is null
1737
1738 IF pn_r12_util_pkg.is_r12 THEN
1739 IF exp_ap_cache(item).pn_tax_classification_code IS NOT NULL THEN
1740 l_tax_code_override_flag := 'Y';
1741 END IF;
1742 exp_ap_cache(item).pn_tax_code_id := null;
1743 exp_ap_cache(item).pn_tax_group_id := null;
1744
1745 ELSE
1746
1747 /* alls well and we are ready to insert into the AP ITF
1748 Get tax name for the expense account */
1749
1750 OPEN get_tax_name(nvl(exp_ap_cache(item).pn_tax_code_id,
1751 exp_ap_cache(item).pn_tax_group_id));
1752 FETCH get_tax_name INTO l_tax_name;
1753 IF get_tax_name%NOTFOUND then
1754 l_tax_name := null;
1755 END IF;
1756 CLOSE get_tax_name;
1757
1758 /* If Tax Code Id or Tax Group Id is not null then populate
1759 tax_code_override_flag. */
1760
1761 IF exp_ap_cache(item).pn_tax_code_id IS NOT NULL OR
1762 exp_ap_cache(item).pn_tax_group_id IS NOT NULL THEN
1763 l_tax_code_override_flag := 'Y';
1764 END IF;
1765
1766 END IF;
1767
1768 /* Create the header */
1769
1770 l_lineNumber := 0;
1771
1772 l_context := 'Inserting into ap_invoices_interface ...';
1773
1774 pnp_debug_pkg.log(' Inserting into ap_invoices_interface ...');
1775
1776 fnd_currency.get_info(exp_ap_cache(item).pn_export_currency_code,
1777 l_precision,
1778 l_ext_precision,
1779 l_min_acct_unit);
1780
1781 INSERT INTO ap_invoices_interface
1782 (invoice_id
1783 ,invoice_num
1784 ,invoice_amount
1785 ,invoice_currency_code
1786 ,description
1787 ,source
1788 ,vendor_id
1789 ,vendor_site_id
1790 ,accts_pay_code_combination_id
1791 ,last_updated_by
1792 ,last_update_date
1796 ,ORG_ID
1793 ,last_update_login
1794 ,created_by
1795 ,creation_date
1797 ,GROUP_ID
1798 ,gl_date
1799 ,terms_date
1800 ,invoice_date
1801 ,invoice_received_date
1802 ,terms_id
1803 ,legal_entity_id
1804 ,exchange_rate
1805 ,exchange_rate_type
1806 ,exchange_date)
1807 VALUES
1808 (AP_INVOICES_INTERFACE_S.nextval
1809 ,'PN-'||PN_PAYMENT_ITEMS_NUM_S.nextval
1810 ,ROUND(exp_ap_cache(item).pn_export_currency_amount,l_precision)
1811 ,exp_ap_cache(item).pn_export_currency_code
1812 ,'Lease Number: ' || exp_ap_cache(item).pn_lease_num
1813 ,'Oracle Property Manager'
1814 ,exp_ap_cache(item).pn_vendor_id
1815 ,exp_ap_cache(item).pn_vendor_site_id
1816 ,lia_acnt_tab(1).account_id
1817 ,l_last_updated_by
1818 ,l_last_update_date
1819 ,l_last_update_login
1820 ,l_created_by
1821 ,l_creation_date
1822 ,exp_ap_cache(item).org_id
1823 ,p_group_id
1824 ,exp_ap_cache(item).gl_date -- gl_date
1825 ,exp_ap_cache(item).pn_due_date -- terms date
1826 ,exp_ap_cache(item).pn_due_date -- invoice date
1827 ,exp_ap_cache(item).pn_due_date -- invoice received date
1828 ,exp_ap_cache(item).pn_ap_ar_term_id
1829 ,exp_ap_cache(item).pn_legal_entity_id
1830 ,exp_ap_cache(item).conv_rate
1831 ,exp_ap_cache(item).conv_rate_type
1832 ,exp_ap_cache(item).pn_accounted_date)
1833 RETURNING invoice_id, invoice_num, invoice_amount
1834 INTO l_invoice_id, l_invoice_num, l_header_amount;
1835
1836 fnd_message.set_name('PN','PN_EXPAP_HEAD_PARAM');
1837 fnd_message.set_token('INV_ID',l_invoice_id);
1838 fnd_message.set_token('INV_NUM',l_invoice_num);
1839 fnd_message.set_token('AMT',l_header_amount);
1840 pnp_debug_pkg.put_log_msg(fnd_message.get);
1841
1842 PNP_DEBUG_PKG.put_log_msg(' ');
1843 /* Create a line for accrual amount if the term is normalized */
1844
1845 IF (nvl(exp_ap_cache(item).pn_normalize,'N') = 'Y' AND
1846 nvl(exp_ap_cache(item).pn_send_entries,'Y') = 'Y') THEN
1847
1848 FOR i IN 1..acc_acnt_tab.COUNT LOOP
1849
1850 l_lineNumber := l_lineNumber + 1;
1851
1852 l_context := 'Inserting into ap_invoice_lines_interface ...';
1853
1854 INSERT INTO ap_invoice_lines_interface
1855 (invoice_id
1856 ,invoice_line_id
1857 ,line_type_lookup_code
1858 ,amount
1859 ,description
1860 ,dist_code_combination_id
1861 ,last_updated_by
1862 ,last_update_date
1863 ,last_update_login
1864 ,created_by
1865 ,creation_date
1866 ,line_number
1867 ,org_id
1868 ,amount_includes_tax_flag -- Tax Inclusive
1869 ,distribution_set_id
1870 ,project_id
1871 ,task_id
1872 ,expenditure_type
1873 ,expenditure_item_date
1874 ,expenditure_organization_id
1875 ,tax_code_id
1876 ,tax_code
1877 ,tax_classification_code
1878 ,tax_code_override_flag)
1879 values
1880 (l_invoice_id
1881 ,AP_INVOICE_LINES_INTERFACE_S.nextval
1882 ,'ITEM'
1883 ,ROUND(((exp_ap_cache(item).pn_export_currency_amount)
1884 * nvl(acc_acnt_tab(i).percentage,100)/100), l_precision)
1885 ,'Lease Number: ' || exp_ap_cache(item).pn_lease_num
1886 ,acc_acnt_tab(i).account_id
1887 ,l_last_updated_by
1888 ,l_last_update_date
1889 ,l_last_update_login
1890 ,l_created_by
1891 ,l_creation_date
1892 ,l_lineNumber
1893 ,exp_ap_cache(item).org_id
1894 ,exp_ap_cache(item).pn_tax_included
1895 ,exp_ap_cache(item).pn_distribution_set_id
1896 ,exp_ap_cache(item).pn_project_id
1897 ,exp_ap_cache(item).pn_task_id
1898 ,exp_ap_cache(item).pn_expenditure_type
1899 ,exp_ap_cache(item).pn_expenditure_item_date
1900 ,exp_ap_cache(item).pn_organization_id
1901 ,nvl(exp_ap_cache(item).pn_tax_code_id,
1902 exp_ap_cache(item).pn_tax_group_id)
1903 ,l_tax_name
1904 ,exp_ap_cache(item).pn_tax_classification_code
1905 ,l_tax_code_override_flag)
1906 RETURNING invoice_line_id, amount
1907 INTO l_invoice_line_id, l_line_amount;
1908
1909 fnd_message.set_name('PN','PN_EXPAP_LINE_PARAM');
1910 fnd_message.set_token('INV_ID',l_invoice_id);
1911 fnd_message.set_token('NUM',l_invoice_num);
1912 fnd_message.set_token('ID',l_invoice_line_id);
1913 fnd_message.set_token('AMT',l_line_amount);
1914 fnd_message.set_token('PAY_ID',exp_ap_cache(item).pn_payment_item_id);
1915 pnp_debug_pkg.put_log_msg(fnd_message.get);
1916
1917 END LOOP; -- for accrual_tab
1918 END IF; -- accrual entered if normalized
1919
1920 -- Create a line for expense A/C
1921
1922 IF ((nvl(exp_ap_cache(item).pn_normalize,'N') <> 'Y') OR
1923 ((exp_ap_cache(item).pn_normalize = 'Y') AND
1927
1924 nvl(exp_ap_cache(item).pn_send_entries,'Y') = 'N')) THEN
1925
1926 l_exp_amt := round(exp_ap_cache(item).pn_export_currency_amount,l_precision);
1928 FOR i IN 1..exp_acnt_tab.COUNT LOOP
1929
1930 l_lineNumber := l_lineNumber + 1;
1931 l_context := 'Inserting into ap_invoice_lines_interface ...';
1932
1933 l_amt := ROUND((l_exp_amt * nvl(exp_acnt_tab(i).percentage,100)/100),l_precision);
1934 l_total_exp_amt := l_total_exp_amt + l_amt;
1935 l_total_exp_percent := l_total_exp_percent + nvl(exp_acnt_tab(i).percentage,100);
1936
1937 IF l_total_exp_percent = 100 THEN
1938 l_diff_amt := l_total_exp_amt - l_exp_amt;
1939 l_amt := l_amt - l_diff_amt;
1940 END IF;
1941
1942 INSERT INTO ap_invoice_lines_interface
1943 (invoice_id
1944 ,invoice_line_id
1945 ,line_type_lookup_code
1946 ,amount
1947 ,description
1948 ,dist_code_combination_id
1949 ,last_updated_by
1950 ,last_update_date
1951 ,last_update_login
1952 ,created_by
1953 ,creation_date
1954 ,line_number
1955 ,org_id
1956 ,amount_includes_tax_flag -- Tax Inclusive
1957 ,distribution_set_id
1958 ,project_id
1959 ,task_id
1960 ,expenditure_type
1961 ,expenditure_item_date
1962 ,expenditure_organization_id
1963 ,tax_code_id
1964 ,tax_code
1965 ,tax_classification_code
1966 ,tax_code_override_flag)
1967 VALUES
1968 (l_invoice_id
1969 ,AP_INVOICE_LINES_INTERFACE_S.nextval
1970 ,'ITEM'
1971 ,l_amt
1972 ,'Lease Number: ' || exp_ap_cache(item).pn_lease_num
1973 ,exp_acnt_tab(i).account_id
1974 ,l_last_updated_by
1975 ,l_last_update_date
1976 ,l_last_update_login
1977 ,l_created_by
1978 ,l_creation_date
1979 ,l_lineNumber
1980 ,exp_ap_cache(item).org_id
1981 ,exp_ap_cache(item).pn_tax_included
1982 ,exp_ap_cache(item).pn_distribution_set_id
1983 ,exp_ap_cache(item).pn_project_id
1984 ,exp_ap_cache(item).pn_task_id
1985 ,exp_ap_cache(item).pn_expenditure_type
1986 ,exp_ap_cache(item).pn_expenditure_item_date
1987 ,exp_ap_cache(item).pn_organization_id
1988 ,nvl(exp_ap_cache(item).pn_tax_code_id,
1989 exp_ap_cache(item).pn_tax_group_id)
1990 ,l_tax_name
1991 ,exp_ap_cache(item).pn_tax_classification_code
1992 ,l_tax_code_override_flag)
1993 RETURNING invoice_line_id, amount
1994 INTO l_invoice_line_id, l_line_amount;
1995
1996 fnd_message.set_name('PN','PN_EXPAP_LINE_PARAM');
1997 fnd_message.set_token('INV_ID',l_invoice_id);
1998 fnd_message.set_token('NUM',l_invoice_num);
1999 fnd_message.set_token('ID',l_invoice_line_id);
2000 fnd_message.set_token('AMT',l_line_amount);
2001 fnd_message.set_token('PAY_ID',exp_ap_cache(item).pn_payment_item_id);
2002 pnp_debug_pkg.put_log_msg(fnd_message.get);
2003
2004 END LOOP;
2005 END IF; -- expense a/c
2006
2007 ---------------------------------------------------------------
2008 -- Set Transferred Flag to 'Y' for all payment items exported
2009 -- to AP
2010 ---------------------------------------------------------------
2011 UPDATE pn_payment_items_all
2012 SET transferred_to_ap_flag = 'Y' ,
2013 ap_invoice_num = l_invoice_num,
2014 last_updated_by = l_last_updated_by,
2015 last_update_login = l_last_update_login,
2016 last_update_date = l_last_update_date ,
2017 export_group_id = p_group_id
2018 WHERE payment_item_id = exp_ap_cache(item).pn_payment_item_id;
2019
2020 IF (SQL%NOTFOUND) then
2021 fnd_message.set_name('PN', 'PN_TRANSFER_TO_AP_FLAG_NOT_SET');
2022 errbuf := fnd_message.get;
2023 rollback;
2024 retcode := 2;
2025 RETURN;
2026 END IF;
2027
2028 IF (exp_ap_cache(item).pn_payment_schedule_id
2029 <> l_prior_payment_schedule_id ) THEN
2030
2031 l_prior_payment_schedule_id := exp_ap_cache(item).pn_payment_schedule_id;
2032
2033 UPDATE pn_payment_schedules_all
2034 SET transferred_by_user_id = l_last_updated_by,
2035 transfer_date = l_last_update_date,
2036 last_updated_by = l_last_updated_by,
2037 last_update_login = l_last_update_login,
2038 last_update_date = l_last_update_date
2039 WHERE payment_schedule_id = exp_ap_cache(item).pn_payment_schedule_id;
2040
2041 IF (SQL%NOTFOUND) then
2042 fnd_message.set_name('PN', 'PN_TRANSFER_TO_AP_INFO_NOT_SET');
2043 errbuf := fnd_message.get;
2044 rollback;
2045 retcode := 2;
2046 return;
2047 END IF;
2048
2049 END IF;
2050 END LOOP; -- for item in
2054 WHEN others THEN
2051
2052 PNP_DEBUG_PKG.log('pn_exp_to_ap.export_items_nogrp (-)');
2053 EXCEPTION
2055 RAISE;
2056
2057 END export_items_nogrp;
2058
2059 --------------------------------------------------------------------------------
2060 -- NAME : exp_to_ap
2061 -- DESCRIPTION : Called from concurrent request
2062 --
2063 -- For levels where grouping rule can be attached Loop
2064 -- For distinct Grouping Rules in a Level Loop
2065 -- o Populate the global Group By FLAGS
2066 -- o Create the Order By clause based on the Group By FLAGS
2067 -- and the Level
2068 -- o Cache the valid items to export
2069 -- o Group the items and export to AP
2070 -- End Loop for distinct Grouping Rules
2071 -- End Loop for levels
2072 --
2073 -- If no Grouping Rules are defined at any level, use the
2074 -- Default functionality.
2075 --
2076 -- PURPOSE : Groups invoices and transferrs to AP
2077 -- INVOKED FROM : Concurrent request
2078 -- ARGUMENTS : errbuf, retcode, p_lease_num_low, p_lease_num_high,
2079 -- p_sch_dt_low, p_sch_dt_high, p_due_dt_low, p_due_dt_high,
2080 -- p_pay_prps_code, p_prd_name, p_amt_low, p_amt_high,
2081 -- p_vendor_id, p_inv_num.
2082 -- REFERENCE : PN_COMMON.debug()
2083 -- HISTORY :
2084 --
2085 -- 19-DEC-03 Kiran o Re written
2086 -- 12-FEB-04 Mrinal Misra o Added parameters to exp_to_ap procedure and
2087 -- code to create l_param_where_clause.
2088 -- 17-FEB-04 Mrinal Misra o Added p_grp_param as parameter to exp_to_ap
2089 -- procedure.
2090 -- 18-FEB-04 Kiran Hegde o Added call to get_order_by_grpby in case of
2091 -- the grouping rule attached at SYSOP level
2092 -- 26-OCT-05 Hareesha o ATG mandated changes for SQL literals using
2093 -- dbms_sql.
2094 -- 24-JUL-06 Hareesha o Bug# 5398654 Consider the lease-no ,sched dt,
2095 -- due dt,amt due ranges while exporting.
2096 --------------------------------------------------------------------------------
2097 PROCEDURE exp_to_ap(errbuf OUT NOCOPY VARCHAR2,
2098 retcode OUT NOCOPY NUMBER,
2099 p_lease_num_low VARCHAR2,
2100 p_lease_num_high VARCHAR2,
2101 p_sch_dt_low VARCHAR2,
2102 p_sch_dt_high VARCHAR2,
2103 p_due_dt_low VARCHAR2,
2104 p_due_dt_high VARCHAR2,
2105 p_pay_prps_code VARCHAR2,
2106 p_prd_name VARCHAR2,
2107 p_amt_low NUMBER,
2108 p_amt_high NUMBER,
2109 p_vendor_id NUMBER,
2110 p_inv_num VARCHAR2,
2111 p_grp_param VARCHAR2)
2112 IS
2113
2114 CURSOR get_grp_rule_item IS
2115 SELECT pi.grouping_rule_id
2116 FROM pn_payment_items pi,
2117 pn_payment_terms_all pt,
2118 pn_leases_all le
2119 WHERE pi.payment_term_id = pt.payment_term_id
2120 AND pt.lease_id = le.lease_id
2121 AND nvl(pi.export_to_ap_flag,'N') = 'Y'
2122 AND pi.payment_item_type_lookup_code ='CASH'
2123 AND le.parent_lease_id is NULL
2124 AND pi.transferred_to_ap_flag is NULL
2125 AND pi.vendor_id is NOT NULL
2126 AND pi.export_currency_amount <> 0
2127 AND pi.grouping_rule_id IS NOT NULL
2128 GROUP BY pi.grouping_rule_id;
2129
2130 CURSOR get_grp_rule_term IS
2131 SELECT pt.grouping_rule_id
2132 FROM pn_payment_items pi,
2133 pn_payment_terms_all pt,
2134 pn_leases_all le
2135 WHERE pi.payment_term_id = pt.payment_term_id
2136 AND pt.lease_id = le.lease_id
2137 AND nvl(pi.export_to_ap_flag,'N') = 'Y'
2138 AND pi.payment_item_type_lookup_code = 'CASH'
2139 AND le.parent_lease_id is NULL
2140 AND pi.transferred_to_ap_flag is NULL
2141 AND pi.vendor_id is NOT NULL
2142 AND pi.export_currency_amount <> 0
2143 AND pi.grouping_rule_id IS NULL
2144 AND pt.grouping_rule_id IS NOT NULL
2145 GROUP BY pt.grouping_rule_id;
2146
2147 CURSOR get_grp_rule_lease IS
2148 SELECT pld.grouping_rule_id
2149 FROM pn_payment_items pi,
2150 pn_payment_terms_all pt,
2151 pn_leases_all le,
2152 pn_lease_details_all pld
2153 WHERE pi.payment_term_id = pt.payment_term_id
2154 AND pt.lease_id = le.lease_id
2155 AND pld.lease_id = le.lease_id
2156 AND nvl(pi.export_to_ap_flag,'N') = 'Y'
2157 AND pi.payment_item_type_lookup_code ='CASH'
2158 AND le.parent_lease_id is NULL
2159 AND pi.transferred_to_ap_flag is NULL
2160 AND pi.vendor_id is NOT NULL
2161 AND pi.export_currency_amount <> 0
2165 GROUP BY pld.grouping_rule_id;
2162 AND pi.grouping_rule_id IS NULL
2163 AND pt.grouping_rule_id IS NULL
2164 AND pld.grouping_rule_id IS NOT NULL
2166
2167 -- counters
2168
2169 l_processing_level_ctr NUMBER := 0;
2170 l_lease_num_where_clause VARCHAR2(4000);
2171 l_sch_date_where_clause VARCHAR2(4000);
2172 l_due_date_where_clause VARCHAR2(4000);
2173 l_pay_prps_where_clause VARCHAR2(4000);
2174 l_prd_name_where_clause VARCHAR2(4000);
2175 l_amt_where_clause VARCHAR2(4000);
2176 l_vendor_where_clause VARCHAR2(4000);
2177 l_inv_num_where_clause VARCHAR2(4000);
2178 l_param_where_clause VARCHAR2(4000);
2179 l_group_id VARCHAR2(10);
2180 l_set_of_books_id NUMBER;
2181
2182 BEGIN
2183
2184 PNP_DEBUG_PKG.log('pn_exp_to_ap.exp_to_ap (+)');
2185
2186 l_context := 'Getting functional currency code';
2187
2188 l_set_of_books_id :=
2189 TO_NUMBER(pn_mo_cache_utils.get_profile_value('PN_SET_OF_BOOKS_ID',
2190 pn_mo_cache_utils.get_current_org_id));
2191
2192 FOR rec IN get_func_curr_code(l_set_of_books_id) LOOP
2193 l_func_curr_code := rec.currency_code;
2194 END LOOP;
2195
2196 IF p_grp_param IS NULL THEN
2197
2198 SELECT TO_CHAR(pn_payments_group_s.NEXTVAL)
2199 INTO l_group_id
2200 FROM dual;
2201
2202 IF p_lease_num_low IS NOT NULL AND
2203 p_lease_num_high IS NOT NULL THEN
2204
2205 l_lease_num_where_clause := ' AND le.lease_num BETWEEN :l_lease_num_low AND :l_lease_num_high ';
2206
2207 ELSIF p_lease_num_low IS NULL AND
2208 p_lease_num_high IS NOT NULL THEN
2209
2210 l_lease_num_where_clause := ' AND le.lease_num <= :l_lease_num_high ';
2211
2212 ELSIF p_lease_num_low IS NOT NULL AND
2213 p_lease_num_high IS NULL THEN
2214
2215 l_lease_num_where_clause := ' AND le.lease_num >= :l_lease_num_low ';
2216
2217 ELSE l_lease_num_where_clause := ' AND 2=2 ';
2218 END IF;
2219
2220 IF p_sch_dt_low IS NOT NULL AND
2221 p_sch_dt_high IS NOT NULL THEN
2222
2223 l_sch_date_where_clause := ' AND ps.schedule_date BETWEEN :l_sch_dt_low AND :l_sch_dt_high ';
2224
2225 ELSIF p_sch_dt_low IS NULL AND
2226 p_sch_dt_high IS NOT NULL THEN
2227
2228 l_sch_date_where_clause := ' AND ps.schedule_date <= :l_sch_dt_high ';
2229
2230 ELSIF p_sch_dt_low IS NOT NULL AND
2231 p_sch_dt_high IS NULL THEN
2232
2233 l_sch_date_where_clause := ' AND ps.schedule_date >= :l_sch_dt_low ';
2234
2235 ELSE l_sch_date_where_clause := ' AND 3=3 ';
2236 END IF;
2237
2238 IF p_due_dt_low IS NOT NULL AND
2239 p_due_dt_high IS NOT NULL THEN
2240
2241 l_due_date_where_clause := ' AND pi.due_date BETWEEN :l_due_dt_low AND :l_due_dt_high ';
2242
2243 ELSIF p_due_dt_low IS NULL AND
2244 p_due_dt_high IS NOT NULL THEN
2245
2246 l_due_date_where_clause := ' AND pi.due_date <= :l_due_dt_high ';
2247
2248 ELSIF p_due_dt_low IS NOT NULL AND
2249 p_due_dt_high IS NULL THEN
2250
2251 l_due_date_where_clause := ' AND pi.due_date >= :l_due_dt_low ';
2252
2253 ELSE l_due_date_where_clause := ' AND 3=3 ';
2254 END IF;
2255
2256 IF p_pay_prps_code IS NOT NULL THEN
2257
2258 l_pay_prps_where_clause := ' AND pt.payment_purpose_code = :l_pay_prps_code ';
2259
2260 ELSE l_pay_prps_where_clause := ' AND 4=4 ';
2261 END IF;
2262
2263 IF p_prd_name IS NOT NULL THEN
2264
2265 l_prd_name_where_clause := ' AND ps.period_name = :l_prd_name ';
2266
2267 ELSE l_prd_name_where_clause := ' AND 5=5';
2268 END IF;
2269
2270 IF p_amt_low IS NOT NULL AND
2271 p_amt_high IS NOT NULL THEN
2272
2273 l_amt_where_clause := ' AND pi.actual_amount BETWEEN :l_amt_low AND :l_amt_high ';
2274
2275 ELSIF p_amt_low IS NULL AND
2276 p_amt_high IS NOT NULL THEN
2277
2278 l_amt_where_clause := ' AND pi.actual_amount <= :l_amt_high ';
2279
2280 ELSIF p_amt_low IS NOT NULL AND
2281 p_amt_high IS NULL THEN
2282
2283 l_amt_where_clause := ' AND pi.actual_amount >= :l_amt_low ';
2284
2285 ELSE l_amt_where_clause := ' AND 6=6 ';
2286 END IF;
2287
2288 IF p_vendor_id IS NOT NULL THEN
2289
2290 l_vendor_where_clause := ' AND pi.vendor_id = :l_vendor_id ';
2291
2292 ELSE l_vendor_where_clause := ' AND 7=7 ';
2293 END IF;
2294
2295 IF p_inv_num IS NOT NULL THEN
2296
2297 l_inv_num_where_clause := ' AND pi.ap_invoice_num = :l_inv_num ';
2298
2299 ELSE l_inv_num_where_clause := ' AND 8=8 ';
2300 END IF;
2301
2302 l_param_where_clause := l_lease_num_where_clause ||
2303 l_sch_date_where_clause ||
2304 l_due_date_where_clause ||
2305 l_pay_prps_where_clause ||
2306 l_prd_name_where_clause ||
2310
2307 l_amt_where_clause ||
2308 l_vendor_where_clause ||
2309 l_inv_num_where_clause;
2311 ELSE
2312
2313 l_param_where_clause := ' AND pi.export_group_id = :l_grp_param ';
2314
2315 l_group_id := p_grp_param;
2316 END IF;
2317
2318
2319 FOR l_processing_level_ctr IN 1..4 LOOP
2320
2321 IF l_processing_level_ctr = 1 THEN
2322 -- item level
2323 l_context := 'Processing items with Grouping Rule at Item level (+)';
2324 PNP_DEBUG_PKG.log(l_context);
2325
2326 FOR grp IN get_grp_rule_item LOOP
2327 -- get group rule id
2328 l_id := grp.grouping_rule_id;
2329 -- populate flags
2330 populate_group_by_flags(l_id);
2331 -- get the order by for group bys
2332 get_order_by_grpby;
2333
2334 Q_Payitem := l_Select_Clause ||
2335 l_param_where_clause ||
2336 l_where_clause_item ||
2337 l_order_by_clause_item ||
2338 l_order_by_clause ||
2339 l_order_by_clause_grpby ||
2340 ' , lia_account';
2341
2342 PNP_DEBUG_PKG.log(' Q_Payitem : '|| Q_Payitem );
2343
2344 -- get items
2345 cache_exp_items (
2346 p_lease_num_low ,
2347 p_lease_num_high,
2348 p_sch_dt_low ,
2349 p_sch_dt_high ,
2350 p_due_dt_low ,
2351 p_due_dt_high ,
2352 p_pay_prps_code ,
2353 p_prd_name ,
2354 p_amt_low ,
2355 p_amt_high ,
2356 p_vendor_id ,
2357 p_inv_num ,
2358 p_grp_param );
2359
2360
2361
2362 -- export items
2363 group_and_export_items(errbuf,
2364 retcode,
2365 l_group_id,
2366 l_param_where_clause);
2367 END LOOP;
2368
2369 l_context := 'Processing items with Grouping Rule at Item level (-)';
2370 PNP_DEBUG_PKG.log(l_context);
2371
2372 ELSIF l_processing_level_ctr = 2 THEN
2373 -- term level
2374 l_context := 'Processing items with Grouping Rule at Term level (+)';
2375 PNP_DEBUG_PKG.log(l_context);
2376
2377 FOR grp IN get_grp_rule_term LOOP
2378 -- get group rule id
2379 l_id := grp.grouping_rule_id;
2380 -- populate flags
2381 populate_group_by_flags(l_id);
2382 -- get the order by for group bys
2383 get_order_by_grpby;
2384
2385 Q_Payitem := l_Select_Clause ||
2386 l_param_where_clause ||
2387 l_where_clause_term ||
2388 l_order_by_clause_term ||
2389 l_order_by_clause ||
2390 l_order_by_clause_grpby||
2391 ' , lia_account';
2392
2393 PNP_DEBUG_PKG.log(' Q_Payitem : '|| Q_Payitem );
2394
2395 cache_exp_items (
2396 p_lease_num_low ,
2397 p_lease_num_high,
2398 p_sch_dt_low ,
2399 p_sch_dt_high ,
2400 p_due_dt_low ,
2401 p_due_dt_high ,
2402 p_pay_prps_code ,
2403 p_prd_name ,
2404 p_amt_low ,
2405 p_amt_high ,
2406 p_vendor_id ,
2407 p_inv_num ,
2408 p_grp_param );
2409
2410
2411 -- export items
2412 group_and_export_items(errbuf,
2413 retcode,
2414 l_group_id,
2415 l_param_where_clause);
2416
2417 END LOOP;
2418
2419 l_context := 'Processing items with Grouping Rule at Term level (-)';
2420 PNP_DEBUG_PKG.log(l_context);
2421
2422 ELSIF l_processing_level_ctr = 3 THEN
2423 -- lease level
2424 l_context := 'Processing items with Grouping Rule at Lease level (+)';
2425 PNP_DEBUG_PKG.log(l_context);
2426
2427 FOR grp IN get_grp_rule_lease LOOP
2428 -- get group rule id
2429 l_id := grp.grouping_rule_id;
2430 -- populate flags
2431 populate_group_by_flags(l_id);
2432 -- get the order by for group bys
2433 get_order_by_grpby;
2434
2435 Q_Payitem := l_Select_Clause ||
2436 l_param_where_clause ||
2437 l_where_clause_lease ||
2438 l_order_by_clause_lease ||
2439 l_order_by_clause||
2440 l_order_by_clause_grpby||
2441 ' , lia_account';
2442
2446 p_lease_num_low ,
2443 PNP_DEBUG_PKG.log(' Q_Payitem : '|| Q_Payitem );
2444
2445 cache_exp_items (
2447 p_lease_num_high,
2448 p_sch_dt_low ,
2449 p_sch_dt_high ,
2450 p_due_dt_low ,
2451 p_due_dt_high ,
2452 p_pay_prps_code ,
2453 p_prd_name ,
2454 p_amt_low ,
2455 p_amt_high ,
2456 p_vendor_id ,
2457 p_inv_num ,
2458 p_grp_param );
2459
2460
2461 -- export items
2462 group_and_export_items(errbuf,
2463 retcode,
2464 l_group_id,
2465 l_param_where_clause);
2466
2467 END LOOP;
2468
2469 l_context := 'Processing items with Grouping Rule at Lease level (-)';
2470 PNP_DEBUG_PKG.log(l_context);
2471
2472 ELSIF l_processing_level_ctr = 4 THEN
2473 -- system option level
2474 l_context := 'Processing items with Grouping Rule at System Option level (+)';
2475 PNP_DEBUG_PKG.log(l_context);
2476
2477 l_id := -1;
2478
2479 FOR rec IN get_system_grouping_rule_id(pn_mo_cache_utils.get_current_org_id) LOOP
2480 l_system_grouping_rule_id := rec.grouping_rule_id;
2481 END LOOP;
2482
2483 IF l_system_grouping_rule_id IS NULL THEN
2484 -- no grouping rule at system level
2485 l_context := 'Default functionality. No grouping rule exists';
2486 PNP_DEBUG_PKG.log(l_context);
2487
2488 l_system_grouping_rule_id := -1;
2489
2490 Q_Payitem := l_Select_Clause ||
2491 l_param_where_clause ||
2492 l_where_clause_sysop ||
2493 l_order_by_clause_default;
2494
2495 PNP_DEBUG_PKG.log(' Q_Payitem : '|| Q_Payitem );
2496
2497 cache_exp_items (
2498 p_lease_num_low ,
2499 p_lease_num_high,
2500 p_sch_dt_low ,
2501 p_sch_dt_high ,
2502 p_due_dt_low ,
2503 p_due_dt_high ,
2504 p_pay_prps_code ,
2505 p_prd_name ,
2506 p_amt_low ,
2507 p_amt_high ,
2508 p_vendor_id ,
2509 p_inv_num ,
2510 p_grp_param );
2511
2512 -- use default functionlity here
2513 export_items_nogrp(errbuf,
2514 retcode,
2515 l_group_id,
2516 l_param_where_clause);
2517
2518 ELSE
2519 -- need to create order by clause sysop here
2520 populate_group_by_flags(l_system_grouping_rule_id);
2521 -- get the order by for group bys
2522 get_order_by_grpby;
2523
2524 Q_Payitem := l_Select_Clause ||
2525 l_param_where_clause ||
2526 l_where_clause_sysop ||
2527 ' ORDER BY ' ||
2528 l_order_by_clause ||
2529 l_order_by_clause_grpby||
2530 ' , lia_account';
2531
2532 PNP_DEBUG_PKG.log(' Q_Payitem : '|| Q_Payitem );
2533
2534 cache_exp_items (
2535 p_lease_num_low ,
2536 p_lease_num_high,
2537 p_sch_dt_low ,
2538 p_sch_dt_high ,
2539 p_due_dt_low ,
2540 p_due_dt_high ,
2541 p_pay_prps_code ,
2542 p_prd_name ,
2543 p_amt_low ,
2544 p_amt_high ,
2545 p_vendor_id ,
2546 p_inv_num ,
2547 p_grp_param );
2548
2549 -- export items
2550 group_and_export_items(errbuf,
2551 retcode,
2552 l_group_id,
2553 l_param_where_clause);
2554
2555
2556 END IF; -- if NOTFOUND
2557
2558 l_context := 'Processing items with Grouping Rule at System Option level (-)';
2559 PNP_DEBUG_PKG.log(l_context);
2560
2561 END IF; -- if l_processing_level_ctr
2562 END LOOP; -- for l_processing_level_ctr
2563
2564 COMMIT;
2565
2566 pnp_debug_pkg.put_log_msg('
2567 ===========================================================================');
2568 fnd_message.set_name ('PN','PN_EXPAP_PAY_PROC_SUC');
2569 fnd_message.set_token ('NUM',(l_total_ctr - l_error_ctr));
2570 pnp_debug_pkg.put_log_msg(fnd_message.get);
2571
2572 fnd_message.set_name ('PN','PN_EXPAP_PAY_PROC_FAIL');
2573 fnd_message.set_token ('NUM',l_error_ctr);
2574 pnp_debug_pkg.put_log_msg(fnd_message.get);
2575
2576 fnd_message.set_name ('PN','PN_EXPAP_PAY_PROC_TOT');
2577 fnd_message.set_token ('NUM',l_total_ctr);
2581 ===========================================================================');
2578 pnp_debug_pkg.put_log_msg(fnd_message.get);
2579
2580 pnp_debug_pkg.put_log_msg('
2582 PNP_DEBUG_PKG.log('pn_exp_to_ap.exp_to_ap (-)');
2583
2584 EXCEPTION
2585 WHEN FATAL_ERROR THEN
2586 /* we should never get here */
2587 pnp_debug_pkg.LOG(SUBSTR(l_context,1,244));
2588 errbuf :=
2589 'A system error occured. A most likely cause is some other process is updating'
2590 ||'the AP interface tables. Please run the export program again';
2591 pnp_debug_pkg.put_log_msg(errbuf);
2592 retcode := 2;
2593 ROLLBACK;
2594
2595 WHEN OTHERS THEN
2596 -- fnd_message.set_name('PN', 'PN_TRANSFER_TO_AP_PROBLEM');
2597 pnp_debug_pkg.LOG(SUBSTR(l_context,1,244));
2598 errbuf := SQLERRM;
2599 retcode := 2;
2600 ROLLBACK;
2601 RAISE;
2602
2603 END EXP_TO_AP;
2604
2605 END PN_EXP_TO_AP;