[Home] [Help]
PACKAGE BODY: APPS.PN_EXP_TO_AP
Source
4 -------------------------------------------------------------------
1 PACKAGE BODY pn_exp_to_ap AS
2 -- $Header: PNTXPMTB.pls 120.16.12020000.2 2012/06/29 08:52:03 ppenumar ship $
3
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 (+)
95 AND purpose_lookup.lookup_code = pt.payment_purpose_code
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''
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
233 group_by_lookup_code
230 CURSOR get_group_bys(p_grouping_rule_id IN NUMBER) IS
231 SELECT group_by_id,
232 grouping_rule_id,
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(15000);--Bug14250364
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
371 BEGIN
368 --------------------------------------------------------------------------------
369 PROCEDURE populate_group_by_flags(p_grouping_rule_id IN NUMBER) IS
370
372 PNP_DEBUG_PKG.log('pn_exp_to_ap.populate_group_by_flags (+)');
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: '
376 || p_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
633
634 l_index NUMBER := 0;
635 l_lia_acc NUMBER := 0;
636
637 -- local variables to temporarily hold fetched values
638 v_org_id NUMBER;
639 v_pn_payment_item_id NUMBER;
643 v_pn_vendor_id NUMBER;
640 v_pn_payment_term_id NUMBER;
641 v_pn_export_currency_amount NUMBER;
642 v_pn_export_currency_code VARCHAR2(15);
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,
760 p_amt_high,
761 p_vendor_id,
762 p_inv_num,
763 p_grp_param,
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);
773
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
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);
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);
832 dbms_sql.define_column (l_cursor, 32,v_pn_lia_account);
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);
877 dbms_sql.column_value (l_cursor, 31,exp_ap_cache(l_index).pn_payment_term_type_code);
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);
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
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
930 IF NVL(exp_ap_cache(l_index).pn_normalize,'N') = 'Y' AND
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
1008
1005 RAISE;
1006
1007 END cache_exp_items;
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,
1044 p_group_id VARCHAR2,
1045 p_param_where_clause VARCHAR2) IS
1046
1047 l_lineNumber NUMBER := 0;
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
1132 (exp_ap_cache(l_start).conv_rate_type IS NULL AND
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
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
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;
1172 END LOOP;
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 ,CALC_TAX_DURING_IMPORT_FLAG
1259 ,ADD_TAX_TO_INV_AMT_FLAG)--For Bug 9068811
1263 ,'PN-'||PN_PAYMENT_ITEMS_NUM_S.nextval)
1260 VALUES
1261 (AP_INVOICES_INTERFACE_S.nextval
1262 ,NVL(l_invoice_num
1264 ,ROUND(l_header_amount,l_precision)
1265 ,exp_ap_cache(l_start).pn_export_currency_code
1266 ,'Lease Number: ' || exp_ap_cache(l_start).pn_lease_num
1267 ,'Oracle Property Manager'
1268 ,exp_ap_cache(l_start).pn_vendor_id
1269 ,exp_ap_cache(l_start).pn_vendor_site_id
1270 ,exp_ap_cache(l_start).pn_lia_account
1271 ,l_last_updated_by
1272 ,l_last_update_date
1273 ,l_last_update_login
1274 ,l_created_by
1275 ,l_creation_date
1276 ,exp_ap_cache(l_start).org_id
1277 ,p_group_id
1278 ,exp_ap_cache(l_start).gl_date -- gl_date
1279 ,exp_ap_cache(l_start).pn_due_date -- terms date
1280 ,exp_ap_cache(l_start).pn_due_date -- invoice date
1281 ,exp_ap_cache(l_start).pn_due_date -- invoice received date
1282 ,exp_ap_cache(l_start).pn_ap_ar_term_id
1283 ,exp_ap_cache(l_start).pn_legal_entity_id
1284 ,exp_ap_cache(l_start).conv_rate
1285 ,exp_ap_cache(l_start).conv_rate_type
1286 ,exp_ap_cache(l_start).pn_accounted_date
1287 ,'Y'
1288 ,'Y')--For Bug 9068811
1289 RETURNING invoice_id, invoice_num INTO l_invoice_id, l_invoice_num;
1290
1291 IF inserted_inv_t.LAST IS NULL THEN
1292 l_temp_count := 1;
1293 ELSE
1294 l_temp_count := inserted_inv_t.LAST + 1;
1295 END IF;
1296
1297 inserted_inv_t(l_temp_count).invoice_id := l_invoice_id;
1298 inserted_inv_t(l_temp_count).invoice_num := l_invoice_num;
1299 inserted_inv_t(l_temp_count).items_proc := l_next - l_start;
1300
1301 fnd_message.set_name('PN','PN_EXPAP_HEAD_PARAM');
1302 fnd_message.set_token('INV_ID',l_invoice_id);
1303 fnd_message.set_token('INV_NUM',l_invoice_num);
1304 fnd_message.set_token('AMT',l_header_amount);
1305 pnp_debug_pkg.put_log_msg(fnd_message.get);
1306
1307 PNP_DEBUG_PKG.put_log_msg(' ');
1308 -- now for the distributions
1309
1310 FOR item IN l_start .. l_next-1 LOOP
1311
1312 l_total_exp_amt := 0;
1313 l_total_exp_percent := 0;
1314
1315 IF exp_ap_cache(item).pn_distribution_set_id IS NULL
1316 AND exp_ap_cache(item).pn_project_id IS NULL THEN
1317 -- validate distributions
1318 -- Initailize the tables
1319 lia_acnt_tab.delete;
1320 acc_acnt_tab.delete;
1321 exp_acnt_tab.delete;
1322
1323 l_lia_cnt := 0;
1324 l_acc_cnt := 0;
1325 l_exp_cnt := 0;
1326
1327 FOR acnt_rec IN get_acnt_info(exp_ap_cache(item).pn_payment_term_id) LOOP
1328 IF acnt_rec.account_class = 'LIA' THEN
1329 l_lia_cnt := l_lia_cnt + 1;
1330 lia_acnt_tab(l_lia_cnt) := acnt_rec;
1331
1332 ELSIF acnt_rec.account_class = 'EXP' THEN
1333 l_exp_cnt := l_exp_cnt + 1;
1334 exp_acnt_tab(l_exp_cnt) := acnt_rec;
1335
1336 ELSIF acnt_rec.account_class = 'ACC' THEN
1337 l_acc_cnt := l_acc_cnt + 1;
1338 acc_acnt_tab(l_acc_cnt) := acnt_rec;
1339
1340 END IF;
1341 END LOOP; -- for account in
1342
1343 -- In case of terms that are not normalized, we may not get the liability
1344 -- and/or Accrual A/C
1345
1346 IF l_lia_cnt = 0 THEN
1347 lia_acnt_tab(1) := NULL;
1348 END IF;
1349
1350 IF l_acc_cnt = 0 THEN
1351 acc_acnt_tab(1) := NULL;
1352 END IF;
1353
1354 ELSE -- distribution_set/project info exists
1355
1356 lia_acnt_tab(1) := null;
1357 exp_acnt_tab(1) := null;
1358 acc_acnt_tab(1) := null;
1359
1360 END IF; -- if dist_set/project is null
1361
1362 IF pn_r12_util_pkg.is_r12 THEN
1363 IF exp_ap_cache(item).pn_tax_classification_code IS NOT NULL THEN
1364 l_tax_code_override_flag := 'Y';
1365 END IF;
1366
1367 exp_ap_cache(item).pn_tax_code_id := null;
1368 exp_ap_cache(item).pn_tax_group_id := null;
1369
1370 ELSE
1371
1372 -- alls well and we are ready to insert into the AP ITF
1373 -- Get tax name for the expense account
1374
1375 --
1376 FOR rec IN get_tax_name(nvl(exp_ap_cache(item).pn_tax_code_id,
1377 exp_ap_cache(item).pn_tax_group_id)) LOOP
1378 l_tax_name := rec.name;
1379 END LOOP;
1380
1381 -- If Tax Code Id or Tax Group Id is not null then populate
1382 -- tax_code_override_flag.
1383
1384 IF exp_ap_cache(item).pn_tax_code_id IS NOT NULL OR
1385 exp_ap_cache(item).pn_tax_group_id IS NOT NULL THEN
1386 l_tax_code_override_flag := 'Y';
1387 END IF;
1388
1389 exp_ap_cache(item).pn_tax_classification_code := null;
1393
1390 END IF;
1391
1392 -- Create a line for accrual amount if the term is normalized
1394 IF (nvl(exp_ap_cache(item).pn_normalize,'N') = 'Y' AND
1395 nvl(exp_ap_cache(item).pn_send_entries,'Y') = 'Y') THEN
1396
1397 FOR i IN 1..acc_acnt_tab.COUNT LOOP
1398
1399 l_lineNumber := l_lineNumber + 1;
1400
1401 l_context := 'Inserting into ap_invoice_lines_interface ...';
1402
1403 INSERT INTO ap_invoice_lines_interface
1404 (invoice_id
1405 ,invoice_line_id
1406 ,line_type_lookup_code
1407 ,amount
1408 ,description
1409 ,dist_code_combination_id
1410 ,DEFAULT_DIST_CCID
1411 ,last_updated_by
1412 ,last_update_date
1413 ,last_update_login
1414 ,created_by
1415 ,creation_date
1416 ,line_number
1417 ,org_id
1418 ,amount_includes_tax_flag -- Tax Inclusive
1419 ,distribution_set_id
1420 ,project_id
1421 ,task_id
1422 ,expenditure_type
1423 ,expenditure_item_date
1424 ,expenditure_organization_id
1425 ,tax_code_id
1426 ,tax_code
1427 ,tax_classification_code
1428 ,tax_code_override_flag)
1429 VALUES
1430 (l_invoice_id
1431 ,AP_INVOICE_LINES_INTERFACE_S.nextval
1432 ,'ITEM'
1433 ,ROUND(((exp_ap_cache(item).pn_export_currency_amount)
1434 * nvl(acc_acnt_tab(i).percentage,100)/100), l_precision)
1435 ,'Lease Number: ' || exp_ap_cache(item).pn_lease_num
1436 ,acc_acnt_tab(i).account_id
1437 ,acc_acnt_tab(i).account_id
1438 ,l_last_updated_by
1439 ,l_last_update_date
1440 ,l_last_update_login
1441 ,l_created_by
1442 ,l_creation_date
1443 ,l_lineNumber
1444 ,exp_ap_cache(item).org_id
1445 ,exp_ap_cache(item).pn_tax_included
1446 ,exp_ap_cache(item).pn_distribution_set_id
1447 ,exp_ap_cache(item).pn_project_id
1448 ,exp_ap_cache(item).pn_task_id
1449 ,exp_ap_cache(item).pn_expenditure_type
1450 ,exp_ap_cache(item).pn_expenditure_item_date
1451 ,exp_ap_cache(item).pn_organization_id
1452 ,nvl(exp_ap_cache(item).pn_tax_code_id,
1453 exp_ap_cache(item).pn_tax_group_id)
1454 ,l_tax_name
1455 ,exp_ap_cache(item).pn_tax_classification_code
1456 ,l_tax_code_override_flag)
1457 RETURNING invoice_line_id, amount INTO l_invoice_line_id, l_line_amount;
1458
1459 fnd_message.set_name('PN','PN_EXPAP_LINE_PARAM');
1460 fnd_message.set_token('INV_ID',l_invoice_id);
1461 fnd_message.set_token('NUM',l_invoice_num);
1462 fnd_message.set_token('ID',l_invoice_line_id);
1463 fnd_message.set_token('AMT',l_line_amount);
1464 fnd_message.set_token('PAY_ID',exp_ap_cache(item).pn_payment_item_id);
1465 pnp_debug_pkg.put_log_msg(fnd_message.get);
1466
1467 END LOOP; -- for accrual_tab
1468
1469 END IF; -- accrual entered if normalized
1470
1471 -- Create a line for expense A/C
1472
1473 IF ((nvl(exp_ap_cache(item).pn_normalize,'N') <> 'Y') OR
1474 ((exp_ap_cache(item).pn_normalize = 'Y') AND
1475 nvl(exp_ap_cache(item).pn_send_entries,'Y') = 'N')) THEN
1476
1477 l_exp_amt := round(exp_ap_cache(item).pn_export_currency_amount,l_precision);
1478
1479 FOR i IN 1..exp_acnt_tab.COUNT LOOP
1480
1481 l_lineNumber := l_lineNumber + 1;
1482 l_context := 'Inserting into ap_invoice_lines_interface ...';
1483
1484 l_amt := ROUND((l_exp_amt * nvl(exp_acnt_tab(i).percentage,100)/100),l_precision);
1485 l_total_exp_amt := l_total_exp_amt + l_amt;
1486 l_total_exp_percent := l_total_exp_percent + nvl(exp_acnt_tab(i).percentage,100);
1487
1488 IF l_total_exp_percent = 100 THEN
1489 l_diff_amt := l_total_exp_amt - l_exp_amt;
1490 l_amt := l_amt - l_diff_amt;
1491 END IF;
1492
1493 INSERT INTO ap_invoice_lines_interface
1494 ( invoice_id
1495 ,invoice_line_id
1496 ,line_type_lookup_code
1497 ,amount
1498 ,description
1499 ,dist_code_combination_id
1500 ,DEFAULT_DIST_CCID
1501 ,last_updated_by
1502 ,last_update_date
1503 ,last_update_login
1504 ,created_by
1505 ,creation_date
1506 ,line_number
1507 ,org_id
1508 ,amount_includes_tax_flag -- Tax Inclusive
1509 ,distribution_set_id
1513 ,expenditure_item_date
1510 ,project_id
1511 ,task_id
1512 ,expenditure_type
1514 ,expenditure_organization_id
1515 ,tax_code_id
1516 ,tax_code
1517 ,tax_classification_code /*--Bug 6392393--*/
1518 ,tax_code_override_flag)
1519 VALUES
1520 (l_invoice_id
1521 ,AP_INVOICE_LINES_INTERFACE_S.nextval
1522 ,'ITEM'
1523 ,l_amt
1524 ,'Lease Number: ' || exp_ap_cache(item).pn_lease_num
1525 ,exp_acnt_tab(i).account_id
1526 ,exp_acnt_tab(i).account_id
1527 ,l_last_updated_by
1528 ,l_last_update_date
1529 ,l_last_update_login
1530 ,l_created_by
1531 ,l_creation_date
1532 ,l_lineNumber
1533 ,exp_ap_cache(item).org_id
1534 ,exp_ap_cache(item).pn_tax_included
1535 ,exp_ap_cache(item).pn_distribution_set_id
1536 ,exp_ap_cache(item).pn_project_id
1537 ,exp_ap_cache(item).pn_task_id
1538 ,exp_ap_cache(item).pn_expenditure_type
1539 ,exp_ap_cache(item).pn_expenditure_item_date
1540 ,exp_ap_cache(item).pn_organization_id
1541 ,nvl(exp_ap_cache(item).pn_tax_code_id,
1542 exp_ap_cache(item).pn_tax_group_id)
1543 ,l_tax_name
1544 ,exp_ap_cache(item).pn_tax_classification_code /*--Bug 6392393--*/
1545 ,l_tax_code_override_flag)
1546 RETURNING invoice_line_id, amount INTO l_invoice_line_id, l_line_amount;
1547
1548 fnd_message.set_name('PN','PN_EXPAP_LINE_PARAM');
1549 fnd_message.set_token('INV_ID',l_invoice_id);
1550 fnd_message.set_token('NUM',l_invoice_num);
1551 fnd_message.set_token('ID',l_invoice_line_id);
1552 fnd_message.set_token('AMT',l_line_amount);
1553 fnd_message.set_token('PAY_ID',exp_ap_cache(item).pn_payment_item_id);
1554 pnp_debug_pkg.put_log_msg(fnd_message.get);
1555
1556 END LOOP;
1557
1558 END IF; -- expense a/c
1559
1560 ---------------------------------------------------------------
1561 -- Set Transferred Flag to 'Y' for all payment items exported
1562 -- to AP
1563 ---------------------------------------------------------------
1564 UPDATE pn_payment_items_all
1565 SET transferred_to_ap_flag = 'Y' ,
1566 ap_invoice_num = l_invoice_num,
1567 last_updated_by = l_last_updated_by,
1568 last_update_login = l_last_update_login,
1569 last_update_date = l_last_update_date ,
1570 export_group_id = p_group_id
1571 WHERE payment_item_id = exp_ap_cache(item).pn_payment_item_id;
1572
1573 IF (SQL%NOTFOUND) then
1574 fnd_message.set_name('PN', 'PN_TRANSFER_TO_AP_FLAG_NOT_SET');
1575 errbuf := fnd_message.get;
1576 pnp_debug_pkg.put_log_msg(errbuf);
1577 ROLLBACK;
1578 retcode := 2;
1579 RETURN;
1580 END IF;
1581
1582 IF (exp_ap_cache(item).pn_payment_schedule_id
1583 <> l_prior_payment_schedule_id) THEN
1584
1585 l_prior_payment_schedule_id := exp_ap_cache(item).pn_payment_schedule_id;
1586
1587 UPDATE pn_payment_schedules_all
1588 SET transferred_by_user_id = l_last_updated_by,
1589 transfer_date = l_last_update_date,
1590 last_updated_by = l_last_updated_by,
1591 last_update_login = l_last_update_login,
1592 last_update_date = l_last_update_date
1593 WHERE payment_schedule_id = exp_ap_cache(item).pn_payment_schedule_id;
1594
1595 IF (SQL%NOTFOUND) then
1596 fnd_message.set_name('PN', 'PN_TRANSFER_TO_AP_INFO_NOT_SET');
1597 errbuf := fnd_message.get;
1598 pnp_debug_pkg.put_log_msg(errbuf);
1599 ROLLBACK;
1600 retcode := 2;
1601 RETURN;
1602 END IF;
1603
1604 END IF;
1605
1606 END LOOP; -- now for the distributions
1607
1608 EXCEPTION
1609 WHEN BAD_INVOICE_NUM THEN
1610 retcode := 1;
1611 l_error_ctr := l_error_ctr + l_next - l_start;
1612 /* this is for the backport - remove after seed bug is fixed */
1613 l_msg_exists := FALSE;
1614 FOR i IN exists_msg LOOP
1615 l_msg_exists := TRUE;
1616 END LOOP;
1617 IF l_msg_exists THEN
1618 fnd_message.set_name('PN', 'PN_CANNOT_GRP_ON_INV');
1619 fnd_message.set_token('INV_NUM',l_invoice_num);
1620 pnp_debug_pkg.put_log_msg(fnd_message.get);
1621 ELSE
1622 pnp_debug_pkg.put_log_msg
1623 ('The system is unable to group and process items with invoice number '||
1624 l_invoice_num||
1628 WHEN FATAL_ERROR THEN
1625 ' because some mandatory attributes do not match.');
1626 END IF;
1627
1629 RAISE;
1630
1631 WHEN OTHERS THEN
1632 errbuf := SQLERRM;
1633 pnp_debug_pkg.put_log_msg(errbuf);
1634 ROLLBACK;
1635 RAISE;
1636
1637 END;
1638
1639 /* get our counters right now
1640 else we will loop for eternity */
1641 l_item_prcsed := l_next - 1;
1642 l_start := l_next;
1643 l_next := l_next + 1;
1644 END IF;
1645 END LOOP;
1646
1647 PNP_DEBUG_PKG.log('pn_exp_to_ap.group_and_export_items (-)');
1648
1649 EXCEPTION
1650 WHEN FATAL_ERROR THEN
1651 RAISE;
1652 WHEN others THEN
1653 RAISE;
1654
1655 END group_and_export_items;
1656
1657 --------------------------------------------------------------------------------
1658 -- NAME : export_items_nogrp
1659 -- DESCRIPTION : Creates entries in AP Interface for Invoice and Invoice Lines
1660 -- This procedure is the default functionality when Grouping
1661 -- Rule is not defined at any level.
1662 -- PURPOSE : Export items to AP without grouping.
1663 -- INVOKED FROM : exp_to_ap
1664 -- ARGUMENTS : errbuf - Error Buffer
1665 -- retcode - Rerurn Code, indicates if the CP should end in
1666 -- success or failure.
1667 -- p_group_id - Export Group ID
1668 -- p_param_where_clause - Where clause from SYSTEM.last_query
1669 -- from the Export to AP form.
1670 -- REFERENCE : PN_COMMON.debug()
1671 -- HISTORY :
1672 --
1673 -- 19-DEC-03 Kiran o Created
1674 --------------------------------------------------------------------------------
1675 PROCEDURE export_items_nogrp(errbuf IN OUT NOCOPY VARCHAR2,
1676 retcode IN OUT NOCOPY NUMBER,
1677 p_group_id VARCHAR2,
1678 p_param_where_clause VARCHAR2) IS
1679
1680 l_lineNumber NUMBER := 0;
1681 l_prior_payment_schedule_id NUMBER := -999;
1682 l_header_amount NUMBER;
1683 l_line_amount NUMBER;
1684
1685 BEGIN
1686 PNP_DEBUG_PKG.log('pn_exp_to_ap.export_items_nogrp (+)');
1687
1688 /* we already have the required items in the cache */
1689
1690 l_context := 'Exporting to AP with default functionality';
1691
1692 pnp_debug_pkg.log(' Exporting to AP with default functionality exp_ap_cache.COUNT:'||exp_ap_cache.COUNT);
1693
1694 FOR item IN 1..exp_ap_cache.COUNT LOOP
1695
1696 l_total_exp_amt := 0;
1697 l_total_exp_percent := 0;
1698
1699 IF exp_ap_cache(item).pn_distribution_set_id IS NULL AND
1700 exp_ap_cache(item).pn_project_id IS NULL THEN
1701 -- validate distributions
1702 -- Initailize the tables
1703 lia_acnt_tab.delete;
1704 acc_acnt_tab.delete;
1705 exp_acnt_tab.delete;
1706
1707 l_lia_cnt := 0;
1708 l_acc_cnt := 0;
1709 l_exp_cnt := 0;
1710
1711 FOR acnt_rec IN get_acnt_info(exp_ap_cache(item).pn_payment_term_id) LOOP
1712 IF acnt_rec.account_class = 'LIA' THEN
1713 l_lia_cnt := l_lia_cnt + 1;
1714 lia_acnt_tab(l_lia_cnt) := acnt_rec;
1715
1716 ELSIF acnt_rec.account_class = 'EXP' THEN
1717 l_exp_cnt := l_exp_cnt + 1;
1718 exp_acnt_tab(l_exp_cnt) := acnt_rec;
1719
1720 ELSIF acnt_rec.account_class = 'ACC' THEN
1721 l_acc_cnt := l_acc_cnt + 1;
1722 acc_acnt_tab(l_acc_cnt) := acnt_rec;
1723
1724 END IF;
1725 END LOOP; -- for account in
1726
1727 /* In case of terms that are not normalized, we may not get the liability
1728 and/or Accrual A/C */
1729
1730 IF l_lia_cnt = 0 THEN
1731 lia_acnt_tab(1) := NULL;
1732 END IF;
1733
1734 IF l_acc_cnt = 0 THEN
1735 acc_acnt_tab(1) := NULL;
1736 END IF;
1737
1738 ELSE -- distribution_set/project info exists
1739
1740 lia_acnt_tab(1) := null;
1741 exp_acnt_tab(1) := null;
1742 acc_acnt_tab(1) := null;
1743
1744 END IF; -- if dist_set/project is null
1745
1746 IF pn_r12_util_pkg.is_r12 THEN
1747 IF exp_ap_cache(item).pn_tax_classification_code IS NOT NULL THEN
1748 l_tax_code_override_flag := 'Y';
1749 END IF;
1750 exp_ap_cache(item).pn_tax_code_id := null;
1751 exp_ap_cache(item).pn_tax_group_id := null;
1752
1753 ELSE
1754
1755 /* alls well and we are ready to insert into the AP ITF
1756 Get tax name for the expense account */
1757
1758 OPEN get_tax_name(nvl(exp_ap_cache(item).pn_tax_code_id,
1759 exp_ap_cache(item).pn_tax_group_id));
1760 FETCH get_tax_name INTO l_tax_name;
1761 IF get_tax_name%NOTFOUND then
1762 l_tax_name := null;
1763 END IF;
1764 CLOSE get_tax_name;
1765
1766 /* If Tax Code Id or Tax Group Id is not null then populate
1767 tax_code_override_flag. */
1768
1769 IF exp_ap_cache(item).pn_tax_code_id IS NOT NULL OR
1770 exp_ap_cache(item).pn_tax_group_id IS NOT NULL THEN
1771 l_tax_code_override_flag := 'Y';
1775
1772 END IF;
1773
1774 END IF;
1776 /* Create the header */
1777
1778 l_lineNumber := 0;
1779
1780 l_context := 'Inserting into ap_invoices_interface ...';
1781
1782 pnp_debug_pkg.log(' Inserting into ap_invoices_interface ...');
1783
1784 fnd_currency.get_info(exp_ap_cache(item).pn_export_currency_code,
1785 l_precision,
1786 l_ext_precision,
1787 l_min_acct_unit);
1788
1789 INSERT INTO ap_invoices_interface
1790 (invoice_id
1791 ,invoice_num
1792 ,invoice_amount
1793 ,invoice_currency_code
1794 ,description
1795 ,source
1796 ,vendor_id
1797 ,vendor_site_id
1798 ,accts_pay_code_combination_id
1799 ,last_updated_by
1800 ,last_update_date
1801 ,last_update_login
1802 ,created_by
1803 ,creation_date
1804 ,ORG_ID
1805 ,GROUP_ID
1806 ,gl_date
1807 ,terms_date
1808 ,invoice_date
1809 ,invoice_received_date
1810 ,terms_id
1811 ,legal_entity_id
1812 ,exchange_rate
1813 ,exchange_rate_type
1814 ,exchange_date
1815 ,CALC_TAX_DURING_IMPORT_FLAG
1816 ,ADD_TAX_TO_INV_AMT_FLAG)--For Bug 9068811
1817 VALUES
1818 (AP_INVOICES_INTERFACE_S.nextval
1819 ,'PN-'||PN_PAYMENT_ITEMS_NUM_S.nextval
1820 ,ROUND(exp_ap_cache(item).pn_export_currency_amount,l_precision)
1821 ,exp_ap_cache(item).pn_export_currency_code
1822 ,'Lease Number: ' || exp_ap_cache(item).pn_lease_num
1823 ,'Oracle Property Manager'
1824 ,exp_ap_cache(item).pn_vendor_id
1825 ,exp_ap_cache(item).pn_vendor_site_id
1826 ,lia_acnt_tab(1).account_id
1827 ,l_last_updated_by
1828 ,l_last_update_date
1829 ,l_last_update_login
1830 ,l_created_by
1831 ,l_creation_date
1832 ,exp_ap_cache(item).org_id
1833 ,p_group_id
1834 ,exp_ap_cache(item).gl_date -- gl_date
1835 ,exp_ap_cache(item).pn_due_date -- terms date
1836 ,exp_ap_cache(item).pn_due_date -- invoice date
1837 ,exp_ap_cache(item).pn_due_date -- invoice received date
1838 ,exp_ap_cache(item).pn_ap_ar_term_id
1839 ,exp_ap_cache(item).pn_legal_entity_id
1840 ,exp_ap_cache(item).conv_rate
1841 ,exp_ap_cache(item).conv_rate_type
1842 ,exp_ap_cache(item).pn_accounted_date
1843 ,'Y'
1844 ,'Y')--For Bug 9068811
1845 RETURNING invoice_id, invoice_num, invoice_amount
1846 INTO l_invoice_id, l_invoice_num, l_header_amount;
1847
1848 fnd_message.set_name('PN','PN_EXPAP_HEAD_PARAM');
1849 fnd_message.set_token('INV_ID',l_invoice_id);
1850 fnd_message.set_token('INV_NUM',l_invoice_num);
1851 fnd_message.set_token('AMT',l_header_amount);
1852 pnp_debug_pkg.put_log_msg(fnd_message.get);
1853
1854 PNP_DEBUG_PKG.put_log_msg(' ');
1855 /* Create a line for accrual amount if the term is normalized */
1856
1857 IF (nvl(exp_ap_cache(item).pn_normalize,'N') = 'Y' AND
1858 nvl(exp_ap_cache(item).pn_send_entries,'Y') = 'Y') THEN
1859
1860 FOR i IN 1..acc_acnt_tab.COUNT LOOP
1861
1862 l_lineNumber := l_lineNumber + 1;
1863
1864 l_context := 'Inserting into ap_invoice_lines_interface ...';
1865
1866 INSERT INTO ap_invoice_lines_interface
1867 (invoice_id
1868 ,invoice_line_id
1869 ,line_type_lookup_code
1870 ,amount
1871 ,description
1872 ,dist_code_combination_id
1873 ,DEFAULT_DIST_CCID
1874 ,last_updated_by
1875 ,last_update_date
1876 ,last_update_login
1877 ,created_by
1878 ,creation_date
1879 ,line_number
1880 ,org_id
1881 ,amount_includes_tax_flag -- Tax Inclusive
1882 ,distribution_set_id
1883 ,project_id
1884 ,task_id
1885 ,expenditure_type
1886 ,expenditure_item_date
1887 ,expenditure_organization_id
1888 ,tax_code_id
1889 ,tax_code
1890 ,tax_classification_code
1891 ,tax_code_override_flag)
1892 values
1893 (l_invoice_id
1894 ,AP_INVOICE_LINES_INTERFACE_S.nextval
1895 ,'ITEM'
1896 ,ROUND(((exp_ap_cache(item).pn_export_currency_amount)
1897 * nvl(acc_acnt_tab(i).percentage,100)/100), l_precision)
1898 ,'Lease Number: ' || exp_ap_cache(item).pn_lease_num
1899 ,acc_acnt_tab(i).account_id
1900 ,acc_acnt_tab(i).account_id
1901 ,l_last_updated_by
1902 ,l_last_update_date
1903 ,l_last_update_login
1904 ,l_created_by
1905 ,l_creation_date
1906 ,l_lineNumber
1907 ,exp_ap_cache(item).org_id
1908 ,exp_ap_cache(item).pn_tax_included
1909 ,exp_ap_cache(item).pn_distribution_set_id
1910 ,exp_ap_cache(item).pn_project_id
1911 ,exp_ap_cache(item).pn_task_id
1912 ,exp_ap_cache(item).pn_expenditure_type
1913 ,exp_ap_cache(item).pn_expenditure_item_date
1914 ,exp_ap_cache(item).pn_organization_id
1915 ,nvl(exp_ap_cache(item).pn_tax_code_id,
1916 exp_ap_cache(item).pn_tax_group_id)
1917 ,l_tax_name
1918 ,exp_ap_cache(item).pn_tax_classification_code
1922
1919 ,l_tax_code_override_flag)
1920 RETURNING invoice_line_id, amount
1921 INTO l_invoice_line_id, l_line_amount;
1923 fnd_message.set_name('PN','PN_EXPAP_LINE_PARAM');
1924 fnd_message.set_token('INV_ID',l_invoice_id);
1925 fnd_message.set_token('NUM',l_invoice_num);
1926 fnd_message.set_token('ID',l_invoice_line_id);
1927 fnd_message.set_token('AMT',l_line_amount);
1928 fnd_message.set_token('PAY_ID',exp_ap_cache(item).pn_payment_item_id);
1929 pnp_debug_pkg.put_log_msg(fnd_message.get);
1930
1931 END LOOP; -- for accrual_tab
1932 END IF; -- accrual entered if normalized
1933
1934 -- Create a line for expense A/C
1935
1936 IF ((nvl(exp_ap_cache(item).pn_normalize,'N') <> 'Y') OR
1937 ((exp_ap_cache(item).pn_normalize = 'Y') AND
1938 nvl(exp_ap_cache(item).pn_send_entries,'Y') = 'N')) THEN
1939
1940 l_exp_amt := round(exp_ap_cache(item).pn_export_currency_amount,l_precision);
1941
1942 FOR i IN 1..exp_acnt_tab.COUNT LOOP
1943
1944 l_lineNumber := l_lineNumber + 1;
1945 l_context := 'Inserting into ap_invoice_lines_interface ...';
1946
1947 l_amt := ROUND((l_exp_amt * nvl(exp_acnt_tab(i).percentage,100)/100),l_precision);
1948 l_total_exp_amt := l_total_exp_amt + l_amt;
1949 l_total_exp_percent := l_total_exp_percent + nvl(exp_acnt_tab(i).percentage,100);
1950
1951 IF l_total_exp_percent = 100 THEN
1952 l_diff_amt := l_total_exp_amt - l_exp_amt;
1953 l_amt := l_amt - l_diff_amt;
1954 END IF;
1955
1956 INSERT INTO ap_invoice_lines_interface
1957 (invoice_id
1958 ,invoice_line_id
1959 ,line_type_lookup_code
1960 ,amount
1961 ,description
1962 ,dist_code_combination_id
1963 ,DEFAULT_DIST_CCID
1964 ,last_updated_by
1965 ,last_update_date
1966 ,last_update_login
1967 ,created_by
1968 ,creation_date
1969 ,line_number
1970 ,org_id
1971 ,amount_includes_tax_flag -- Tax Inclusive
1972 ,distribution_set_id
1973 ,project_id
1974 ,task_id
1975 ,expenditure_type
1976 ,expenditure_item_date
1977 ,expenditure_organization_id
1978 ,tax_code_id
1979 ,tax_code
1980 ,tax_classification_code
1981 ,tax_code_override_flag)
1982 VALUES
1983 (l_invoice_id
1984 ,AP_INVOICE_LINES_INTERFACE_S.nextval
1985 ,'ITEM'
1986 ,l_amt
1987 ,'Lease Number: ' || exp_ap_cache(item).pn_lease_num
1988 ,exp_acnt_tab(i).account_id
1989 ,exp_acnt_tab(i).account_id
1990 ,l_last_updated_by
1991 ,l_last_update_date
1992 ,l_last_update_login
1993 ,l_created_by
1994 ,l_creation_date
1995 ,l_lineNumber
1996 ,exp_ap_cache(item).org_id
1997 ,exp_ap_cache(item).pn_tax_included
1998 ,exp_ap_cache(item).pn_distribution_set_id
1999 ,exp_ap_cache(item).pn_project_id
2000 ,exp_ap_cache(item).pn_task_id
2001 ,exp_ap_cache(item).pn_expenditure_type
2002 ,exp_ap_cache(item).pn_expenditure_item_date
2003 ,exp_ap_cache(item).pn_organization_id
2004 ,nvl(exp_ap_cache(item).pn_tax_code_id,
2005 exp_ap_cache(item).pn_tax_group_id)
2006 ,l_tax_name
2007 ,exp_ap_cache(item).pn_tax_classification_code
2008 ,l_tax_code_override_flag)
2009 RETURNING invoice_line_id, amount
2010 INTO l_invoice_line_id, l_line_amount;
2011
2012 fnd_message.set_name('PN','PN_EXPAP_LINE_PARAM');
2013 fnd_message.set_token('INV_ID',l_invoice_id);
2014 fnd_message.set_token('NUM',l_invoice_num);
2015 fnd_message.set_token('ID',l_invoice_line_id);
2016 fnd_message.set_token('AMT',l_line_amount);
2017 fnd_message.set_token('PAY_ID',exp_ap_cache(item).pn_payment_item_id);
2018 pnp_debug_pkg.put_log_msg(fnd_message.get);
2019
2020 END LOOP;
2021 END IF; -- expense a/c
2022
2023 ---------------------------------------------------------------
2024 -- Set Transferred Flag to 'Y' for all payment items exported
2025 -- to AP
2026 ---------------------------------------------------------------
2027 UPDATE pn_payment_items_all
2028 SET transferred_to_ap_flag = 'Y' ,
2029 ap_invoice_num = l_invoice_num,
2030 last_updated_by = l_last_updated_by,
2031 last_update_login = l_last_update_login,
2032 last_update_date = l_last_update_date ,
2033 export_group_id = p_group_id
2034 WHERE payment_item_id = exp_ap_cache(item).pn_payment_item_id;
2035
2036 IF (SQL%NOTFOUND) then
2037 fnd_message.set_name('PN', 'PN_TRANSFER_TO_AP_FLAG_NOT_SET');
2038 errbuf := fnd_message.get;
2039 rollback;
2040 retcode := 2;
2041 RETURN;
2042 END IF;
2043
2044 IF (exp_ap_cache(item).pn_payment_schedule_id
2045 <> l_prior_payment_schedule_id ) THEN
2046
2047 l_prior_payment_schedule_id := exp_ap_cache(item).pn_payment_schedule_id;
2048
2049 UPDATE pn_payment_schedules_all
2050 SET transferred_by_user_id = l_last_updated_by,
2054 last_update_date = l_last_update_date
2051 transfer_date = l_last_update_date,
2052 last_updated_by = l_last_updated_by,
2053 last_update_login = l_last_update_login,
2055 WHERE payment_schedule_id = exp_ap_cache(item).pn_payment_schedule_id;
2056
2057 IF (SQL%NOTFOUND) then
2058 fnd_message.set_name('PN', 'PN_TRANSFER_TO_AP_INFO_NOT_SET');
2059 errbuf := fnd_message.get;
2060 rollback;
2061 retcode := 2;
2062 return;
2063 END IF;
2064
2065 END IF;
2066 END LOOP; -- for item in
2067
2068 PNP_DEBUG_PKG.log('pn_exp_to_ap.export_items_nogrp (-)');
2069 EXCEPTION
2070 WHEN others THEN
2071 RAISE;
2072
2073 END export_items_nogrp;
2074
2075 --------------------------------------------------------------------------------
2076 -- NAME : exp_to_ap
2077 -- DESCRIPTION : Called from concurrent request
2078 --
2079 -- For levels where grouping rule can be attached Loop
2080 -- For distinct Grouping Rules in a Level Loop
2081 -- o Populate the global Group By FLAGS
2082 -- o Create the Order By clause based on the Group By FLAGS
2083 -- and the Level
2084 -- o Cache the valid items to export
2085 -- o Group the items and export to AP
2086 -- End Loop for distinct Grouping Rules
2087 -- End Loop for levels
2088 --
2089 -- If no Grouping Rules are defined at any level, use the
2090 -- Default functionality.
2091 --
2092 -- PURPOSE : Groups invoices and transferrs to AP
2093 -- INVOKED FROM : Concurrent request
2094 -- ARGUMENTS : errbuf, retcode, p_lease_num_low, p_lease_num_high,
2095 -- p_sch_dt_low, p_sch_dt_high, p_due_dt_low, p_due_dt_high,
2096 -- p_pay_prps_code, p_prd_name, p_amt_low, p_amt_high,
2097 -- p_vendor_id, p_inv_num.
2098 -- REFERENCE : PN_COMMON.debug()
2099 -- HISTORY :
2100 --
2101 -- 19-DEC-03 Kiran o Re written
2102 -- 12-FEB-04 Mrinal Misra o Added parameters to exp_to_ap procedure and
2103 -- code to create l_param_where_clause.
2104 -- 17-FEB-04 Mrinal Misra o Added p_grp_param as parameter to exp_to_ap
2105 -- procedure.
2106 -- 18-FEB-04 Kiran Hegde o Added call to get_order_by_grpby in case of
2107 -- the grouping rule attached at SYSOP level
2108 -- 26-OCT-05 Hareesha o ATG mandated changes for SQL literals using
2109 -- dbms_sql.
2110 -- 24-JUL-06 Hareesha o Bug# 5398654 Consider the lease-no ,sched dt,
2111 -- due dt,amt due ranges while exporting.
2112 --------------------------------------------------------------------------------
2113 PROCEDURE exp_to_ap(errbuf OUT NOCOPY VARCHAR2,
2114 retcode OUT NOCOPY NUMBER,
2115 p_lease_num_low VARCHAR2,
2116 p_lease_num_high VARCHAR2,
2117 p_sch_dt_low VARCHAR2,
2118 p_sch_dt_high VARCHAR2,
2119 p_due_dt_low VARCHAR2,
2120 p_due_dt_high VARCHAR2,
2121 p_pay_prps_code VARCHAR2,
2122 p_prd_name VARCHAR2,
2123 p_amt_low NUMBER,
2124 p_amt_high NUMBER,
2125 p_vendor_id NUMBER,
2126 p_inv_num VARCHAR2,
2127 p_grp_param VARCHAR2)
2128 IS
2129
2130 CURSOR get_grp_rule_item IS
2131 SELECT pi.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 NOT NULL
2144 GROUP BY pi.grouping_rule_id;
2145
2146 CURSOR get_grp_rule_term IS
2147 SELECT pt.grouping_rule_id
2148 FROM pn_payment_items pi,
2149 pn_payment_terms_all pt,
2150 pn_leases_all le
2151 WHERE pi.payment_term_id = pt.payment_term_id
2152 AND pt.lease_id = le.lease_id
2153 AND nvl(pi.export_to_ap_flag,'N') = 'Y'
2154 AND pi.payment_item_type_lookup_code = 'CASH'
2155 AND le.parent_lease_id is NULL
2156 AND pi.transferred_to_ap_flag is NULL
2157 AND pi.vendor_id is NOT NULL
2158 AND pi.export_currency_amount <> 0
2159 AND pi.grouping_rule_id IS NULL
2160 AND pt.grouping_rule_id IS NOT NULL
2161 GROUP BY pt.grouping_rule_id;
2162
2163 CURSOR get_grp_rule_lease IS
2164 SELECT pld.grouping_rule_id
2165 FROM pn_payment_items pi,
2166 pn_payment_terms_all pt,
2167 pn_leases_all le,
2168 pn_lease_details_all pld
2169 WHERE pi.payment_term_id = pt.payment_term_id
2170 AND pt.lease_id = le.lease_id
2174 AND le.parent_lease_id is NULL
2171 AND pld.lease_id = le.lease_id
2172 AND nvl(pi.export_to_ap_flag,'N') = 'Y'
2173 AND pi.payment_item_type_lookup_code ='CASH'
2175 AND pi.transferred_to_ap_flag is NULL
2176 AND pi.vendor_id is NOT NULL
2177 AND pi.export_currency_amount <> 0
2178 AND pi.grouping_rule_id IS NULL
2179 AND pt.grouping_rule_id IS NULL
2180 AND pld.grouping_rule_id IS NOT NULL
2181 GROUP BY pld.grouping_rule_id;
2182
2183 -- counters
2184
2185 l_processing_level_ctr NUMBER := 0;
2186 l_lease_num_where_clause VARCHAR2(4000);
2187 l_sch_date_where_clause VARCHAR2(4000);
2188 l_due_date_where_clause VARCHAR2(4000);
2189 l_pay_prps_where_clause VARCHAR2(4000);
2190 l_prd_name_where_clause VARCHAR2(4000);
2191 l_amt_where_clause VARCHAR2(4000);
2192 l_vendor_where_clause VARCHAR2(4000);
2193 l_inv_num_where_clause VARCHAR2(4000);
2194 l_param_where_clause VARCHAR2(4000);
2195 l_group_id VARCHAR2(10);
2196 l_set_of_books_id NUMBER;
2197
2198 BEGIN
2199
2200 PNP_DEBUG_PKG.log('pn_exp_to_ap.exp_to_ap (+)');
2201
2202 l_context := 'Getting functional currency code';
2203
2204 l_set_of_books_id :=
2205 TO_NUMBER(pn_mo_cache_utils.get_profile_value('PN_SET_OF_BOOKS_ID',
2206 pn_mo_cache_utils.get_current_org_id));
2207
2208 FOR rec IN get_func_curr_code(l_set_of_books_id) LOOP
2209 l_func_curr_code := rec.currency_code;
2210 END LOOP;
2211
2212 IF p_grp_param IS NULL THEN
2213
2214 SELECT TO_CHAR(pn_payments_group_s.NEXTVAL)
2215 INTO l_group_id
2216 FROM dual;
2217
2218 IF p_lease_num_low IS NOT NULL AND
2219 p_lease_num_high IS NOT NULL THEN
2220
2221 l_lease_num_where_clause := ' AND le.lease_num BETWEEN :l_lease_num_low AND :l_lease_num_high ';
2222
2223 ELSIF p_lease_num_low IS NULL AND
2224 p_lease_num_high IS NOT NULL THEN
2225
2226 l_lease_num_where_clause := ' AND le.lease_num <= :l_lease_num_high ';
2227
2228 ELSIF p_lease_num_low IS NOT NULL AND
2229 p_lease_num_high IS NULL THEN
2230
2231 l_lease_num_where_clause := ' AND le.lease_num >= :l_lease_num_low ';
2232
2233 ELSE l_lease_num_where_clause := ' AND 2=2 ';
2234 END IF;
2235
2236 IF p_sch_dt_low IS NOT NULL AND
2237 p_sch_dt_high IS NOT NULL THEN
2238
2239 l_sch_date_where_clause := ' AND ps.schedule_date BETWEEN :l_sch_dt_low AND :l_sch_dt_high ';
2240
2241 ELSIF p_sch_dt_low IS NULL AND
2242 p_sch_dt_high IS NOT NULL THEN
2243
2244 l_sch_date_where_clause := ' AND ps.schedule_date <= :l_sch_dt_high ';
2245
2246 ELSIF p_sch_dt_low IS NOT NULL AND
2247 p_sch_dt_high IS NULL THEN
2248
2249 l_sch_date_where_clause := ' AND ps.schedule_date >= :l_sch_dt_low ';
2250
2251 ELSE l_sch_date_where_clause := ' AND 3=3 ';
2252 END IF;
2253
2254 IF p_due_dt_low IS NOT NULL AND
2255 p_due_dt_high IS NOT NULL THEN
2256
2257 l_due_date_where_clause := ' AND pi.due_date BETWEEN :l_due_dt_low AND :l_due_dt_high ';
2258
2259 ELSIF p_due_dt_low IS NULL AND
2260 p_due_dt_high IS NOT NULL THEN
2261
2262 l_due_date_where_clause := ' AND pi.due_date <= :l_due_dt_high ';
2263
2264 ELSIF p_due_dt_low IS NOT NULL AND
2265 p_due_dt_high IS NULL THEN
2266
2267 l_due_date_where_clause := ' AND pi.due_date >= :l_due_dt_low ';
2268
2269 ELSE l_due_date_where_clause := ' AND 3=3 ';
2270 END IF;
2271
2272 IF p_pay_prps_code IS NOT NULL THEN
2273
2274 l_pay_prps_where_clause := ' AND pt.payment_purpose_code = :l_pay_prps_code ';
2275
2276 ELSE l_pay_prps_where_clause := ' AND 4=4 ';
2277 END IF;
2278
2279 IF p_prd_name IS NOT NULL THEN
2280
2281 l_prd_name_where_clause := ' AND ps.period_name = :l_prd_name ';
2282
2283 ELSE l_prd_name_where_clause := ' AND 5=5';
2284 END IF;
2285
2286 IF p_amt_low IS NOT NULL AND
2287 p_amt_high IS NOT NULL THEN
2288
2289 l_amt_where_clause := ' AND pi.actual_amount BETWEEN :l_amt_low AND :l_amt_high ';
2290
2291 ELSIF p_amt_low IS NULL AND
2292 p_amt_high IS NOT NULL THEN
2293
2294 l_amt_where_clause := ' AND pi.actual_amount <= :l_amt_high ';
2295
2296 ELSIF p_amt_low IS NOT NULL AND
2297 p_amt_high IS NULL THEN
2298
2299 l_amt_where_clause := ' AND pi.actual_amount >= :l_amt_low ';
2300
2301 ELSE l_amt_where_clause := ' AND 6=6 ';
2302 END IF;
2303
2304 IF p_vendor_id IS NOT NULL THEN
2305
2306 l_vendor_where_clause := ' AND pi.vendor_id = :l_vendor_id ';
2307
2308 ELSE l_vendor_where_clause := ' AND 7=7 ';
2309 END IF;
2310
2311 IF p_inv_num IS NOT NULL THEN
2312
2313 l_inv_num_where_clause := ' AND pi.ap_invoice_num = :l_inv_num ';
2314
2315 ELSE l_inv_num_where_clause := ' AND 8=8 ';
2316 END IF;
2317
2318 l_param_where_clause := l_lease_num_where_clause ||
2319 l_sch_date_where_clause ||
2323 l_amt_where_clause ||
2320 l_due_date_where_clause ||
2321 l_pay_prps_where_clause ||
2322 l_prd_name_where_clause ||
2324 l_vendor_where_clause ||
2325 l_inv_num_where_clause;
2326
2327 ELSE
2328
2329 l_param_where_clause := ' AND pi.export_group_id = :l_grp_param ';
2330
2331 l_group_id := p_grp_param;
2332 END IF;
2333
2334
2335 FOR l_processing_level_ctr IN 1..4 LOOP
2336
2337 IF l_processing_level_ctr = 1 THEN
2338 -- item level
2339 l_context := 'Processing items with Grouping Rule at Item level (+)';
2340 PNP_DEBUG_PKG.log(l_context);
2341
2342 FOR grp IN get_grp_rule_item LOOP
2343 -- get group rule id
2344 l_id := grp.grouping_rule_id;
2345 -- populate flags
2346 populate_group_by_flags(l_id);
2347 -- get the order by for group bys
2348 get_order_by_grpby;
2349
2350 Q_Payitem := l_Select_Clause ||
2351 l_param_where_clause ||
2352 l_where_clause_item ||
2353 l_order_by_clause_item ||
2354 l_order_by_clause ||
2355 l_order_by_clause_grpby ||
2356 ' , lia_account';
2357
2358 PNP_DEBUG_PKG.log(' Q_Payitem : '|| Q_Payitem );
2359
2360 -- get items
2361 cache_exp_items (
2362 p_lease_num_low ,
2363 p_lease_num_high,
2364 p_sch_dt_low ,
2365 p_sch_dt_high ,
2366 p_due_dt_low ,
2367 p_due_dt_high ,
2368 p_pay_prps_code ,
2369 p_prd_name ,
2370 p_amt_low ,
2371 p_amt_high ,
2372 p_vendor_id ,
2373 p_inv_num ,
2374 p_grp_param );
2375
2376
2377
2378 -- export items
2379 group_and_export_items(errbuf,
2380 retcode,
2381 l_group_id,
2382 l_param_where_clause);
2383 END LOOP;
2384
2385 l_context := 'Processing items with Grouping Rule at Item level (-)';
2386 PNP_DEBUG_PKG.log(l_context);
2387
2388 ELSIF l_processing_level_ctr = 2 THEN
2389 -- term level
2390 l_context := 'Processing items with Grouping Rule at Term level (+)';
2391 PNP_DEBUG_PKG.log(l_context);
2392
2393 FOR grp IN get_grp_rule_term LOOP
2394 -- get group rule id
2395 l_id := grp.grouping_rule_id;
2396 -- populate flags
2397 populate_group_by_flags(l_id);
2398 -- get the order by for group bys
2399 get_order_by_grpby;
2400
2401 Q_Payitem := l_Select_Clause ||
2402 l_param_where_clause ||
2403 l_where_clause_term ||
2404 l_order_by_clause_term ||
2405 l_order_by_clause ||
2406 l_order_by_clause_grpby||
2407 ' , lia_account';
2408
2409 PNP_DEBUG_PKG.log(' Q_Payitem : '|| Q_Payitem );
2410
2411 cache_exp_items (
2412 p_lease_num_low ,
2413 p_lease_num_high,
2414 p_sch_dt_low ,
2415 p_sch_dt_high ,
2416 p_due_dt_low ,
2417 p_due_dt_high ,
2418 p_pay_prps_code ,
2419 p_prd_name ,
2420 p_amt_low ,
2421 p_amt_high ,
2422 p_vendor_id ,
2423 p_inv_num ,
2424 p_grp_param );
2425
2426
2427 -- export items
2428 group_and_export_items(errbuf,
2429 retcode,
2430 l_group_id,
2431 l_param_where_clause);
2432
2433 END LOOP;
2434
2435 l_context := 'Processing items with Grouping Rule at Term level (-)';
2436 PNP_DEBUG_PKG.log(l_context);
2437
2438 ELSIF l_processing_level_ctr = 3 THEN
2439 -- lease level
2440 l_context := 'Processing items with Grouping Rule at Lease level (+)';
2441 PNP_DEBUG_PKG.log(l_context);
2442
2443 FOR grp IN get_grp_rule_lease LOOP
2444 -- get group rule id
2445 l_id := grp.grouping_rule_id;
2446 -- populate flags
2447 populate_group_by_flags(l_id);
2448 -- get the order by for group bys
2449 get_order_by_grpby;
2450
2451 Q_Payitem := l_Select_Clause ||
2452 l_param_where_clause ||
2453 l_where_clause_lease ||
2454 l_order_by_clause_lease ||
2455 l_order_by_clause||
2456 l_order_by_clause_grpby||
2457 ' , lia_account';
2458
2459 PNP_DEBUG_PKG.log(' Q_Payitem : '|| Q_Payitem );
2463 p_lease_num_high,
2460
2461 cache_exp_items (
2462 p_lease_num_low ,
2464 p_sch_dt_low ,
2465 p_sch_dt_high ,
2466 p_due_dt_low ,
2467 p_due_dt_high ,
2468 p_pay_prps_code ,
2469 p_prd_name ,
2470 p_amt_low ,
2471 p_amt_high ,
2472 p_vendor_id ,
2473 p_inv_num ,
2474 p_grp_param );
2475
2476
2477 -- export items
2478 group_and_export_items(errbuf,
2479 retcode,
2480 l_group_id,
2481 l_param_where_clause);
2482
2483 END LOOP;
2484
2485 l_context := 'Processing items with Grouping Rule at Lease level (-)';
2486 PNP_DEBUG_PKG.log(l_context);
2487
2488 ELSIF l_processing_level_ctr = 4 THEN
2489 -- system option level
2490 l_context := 'Processing items with Grouping Rule at System Option level (+)';
2491 PNP_DEBUG_PKG.log(l_context);
2492
2493 l_id := -1;
2494
2495 FOR rec IN get_system_grouping_rule_id(pn_mo_cache_utils.get_current_org_id) LOOP
2496 l_system_grouping_rule_id := rec.grouping_rule_id;
2497 END LOOP;
2498
2499 IF l_system_grouping_rule_id IS NULL THEN
2500 -- no grouping rule at system level
2501 l_context := 'Default functionality. No grouping rule exists';
2502 PNP_DEBUG_PKG.log(l_context);
2503
2504 l_system_grouping_rule_id := -1;
2505
2506 Q_Payitem := l_Select_Clause ||
2507 l_param_where_clause ||
2508 l_where_clause_sysop ||
2509 l_order_by_clause_default;
2510
2511 PNP_DEBUG_PKG.log(' Q_Payitem : '|| Q_Payitem );
2512
2513 cache_exp_items (
2514 p_lease_num_low ,
2515 p_lease_num_high,
2516 p_sch_dt_low ,
2517 p_sch_dt_high ,
2518 p_due_dt_low ,
2519 p_due_dt_high ,
2520 p_pay_prps_code ,
2521 p_prd_name ,
2522 p_amt_low ,
2523 p_amt_high ,
2524 p_vendor_id ,
2525 p_inv_num ,
2526 p_grp_param );
2527
2528 -- use default functionlity here
2529 export_items_nogrp(errbuf,
2530 retcode,
2531 l_group_id,
2532 l_param_where_clause);
2533
2534 ELSE
2535 -- need to create order by clause sysop here
2536 populate_group_by_flags(l_system_grouping_rule_id);
2537 -- get the order by for group bys
2538 get_order_by_grpby;
2539
2540 Q_Payitem := l_Select_Clause ||
2541 l_param_where_clause ||
2542 l_where_clause_sysop ||
2543 ' ORDER BY ' ||
2544 l_order_by_clause ||
2545 l_order_by_clause_grpby||
2546 ' , lia_account';
2547
2548 PNP_DEBUG_PKG.log(' Q_Payitem : '|| Q_Payitem );
2549
2550 cache_exp_items (
2551 p_lease_num_low ,
2552 p_lease_num_high,
2553 p_sch_dt_low ,
2554 p_sch_dt_high ,
2555 p_due_dt_low ,
2556 p_due_dt_high ,
2557 p_pay_prps_code ,
2558 p_prd_name ,
2559 p_amt_low ,
2560 p_amt_high ,
2561 p_vendor_id ,
2562 p_inv_num ,
2563 p_grp_param );
2564
2565 -- export items
2566 group_and_export_items(errbuf,
2567 retcode,
2568 l_group_id,
2569 l_param_where_clause);
2570
2571
2572 END IF; -- if NOTFOUND
2573
2574 l_context := 'Processing items with Grouping Rule at System Option level (-)';
2575 PNP_DEBUG_PKG.log(l_context);
2576
2577 END IF; -- if l_processing_level_ctr
2578 END LOOP; -- for l_processing_level_ctr
2579
2580 COMMIT;
2581
2582 pnp_debug_pkg.put_log_msg('
2583 ===========================================================================');
2584 fnd_message.set_name ('PN','PN_EXPAP_PAY_PROC_SUC');
2585 fnd_message.set_token ('NUM',(l_total_ctr - l_error_ctr));
2586 pnp_debug_pkg.put_log_msg(fnd_message.get);
2587
2588 fnd_message.set_name ('PN','PN_EXPAP_PAY_PROC_FAIL');
2589 fnd_message.set_token ('NUM',l_error_ctr);
2590 pnp_debug_pkg.put_log_msg(fnd_message.get);
2591
2592 fnd_message.set_name ('PN','PN_EXPAP_PAY_PROC_TOT');
2593 fnd_message.set_token ('NUM',l_total_ctr);
2594 pnp_debug_pkg.put_log_msg(fnd_message.get);
2595
2596 pnp_debug_pkg.put_log_msg('
2597 ===========================================================================');
2598 PNP_DEBUG_PKG.log('pn_exp_to_ap.exp_to_ap (-)');
2599
2600 EXCEPTION
2601 WHEN FATAL_ERROR THEN
2602 /* we should never get here */
2603 pnp_debug_pkg.LOG(SUBSTR(l_context,1,244));
2604 errbuf :=
2605 'A system error occured. A most likely cause is some other process is updating'
2606 ||'the AP interface tables. Please run the export program again';
2607 pnp_debug_pkg.put_log_msg(errbuf);
2608 retcode := 2;
2609 ROLLBACK;
2610
2611 WHEN OTHERS THEN
2612 -- fnd_message.set_name('PN', 'PN_TRANSFER_TO_AP_PROBLEM');
2613 pnp_debug_pkg.LOG(SUBSTR(l_context,1,244));
2614 errbuf := SQLERRM;
2615 retcode := 2;
2616 ROLLBACK;
2617 RAISE;
2618
2619 END EXP_TO_AP;
2620
2621 END PN_EXP_TO_AP;