[Home] [Help]
PACKAGE BODY: APPS.PN_EXP_TO_AR
Source
1 package body PN_EXP_TO_AR as
2 -- $Header: PNTXBILB.pls 120.11.12020000.3 2012/12/28 13:09:44 pcheruvu ship $
3
4 /* All Cursor declarations go here */
5 CURSOR get_dist(p_term_id NUMBER) IS
6 SELECT account_id,
7 account_class,
8 percentage
9 FROM pn_distributions_all
10 WHERE payment_term_id = p_term_id;
11
12 CURSOR get_desc (p_lookup_code varchar2) is
13 SELECT meaning
14 FROM fnd_lookups
15 WHERE lookup_type = 'PN_PAYMENT_PURPOSE_TYPE'
16 AND lookup_code = p_lookup_code;
17
18 CURSOR get_rule_name (p_rule_id number) is
19 SELECT name,
20 type,
21 frequency
22 FROM ra_rules
23 WHERE rule_id = p_rule_id;
24
25 CURSOR get_receipt_name (p_receipt_method_id number) is
26 SELECT name
27 FROM ar_receipt_methods
28 WHERE receipt_method_id = p_receipt_method_id;
29
30 CURSOR get_salesrep_number (p_salesrep_id number, p_org_id NUMBER) is
31 SELECT SALESREP_NUMBER,SALES_CREDIT_TYPE_ID
32 FROM ra_salesreps
33 WHERE salesrep_id = p_salesrep_id
34 AND org_id = p_org_id;
35
36 CURSOR get_cust_trx_name (p_cust_trx_type_id number) is
37 SELECT name
38 FROM ra_cust_trx_types
39 WHERE cust_trx_type_id = p_cust_trx_type_id;
40
41 CURSOR get_term_name (p_term_id number) is
42 SELECT name
43 FROM ra_terms
44 WHERE term_id = p_term_id;
45
46 CURSOR get_loc_code(p_location_id number) is
47 SELECT pl.location_code
48 FROM pn_locations_all pl
49 WHERE pl.location_id = p_location_id;
50
51 CURSOR get_batch_source_name is
52 SELECT name,
53 rev_acc_allocation_rule,
54 allow_sales_credit_flag,
55 derive_date_flag
56 FROM ra_batch_sources
57 WHERE batch_source_id = 24;
58
59 CURSOR get_tax_code (p_tax_code_id number) is
60 SELECT tax_code
61 FROM ar_vat_tax
62 WHERE vat_tax_id = p_tax_code_id;
63
64 CURSOR acnt_cls_cur(p_term_id NUMBER) IS
65 SELECT account_class
66 FROM pn_distributions_all
67 WHERE payment_term_id = p_term_id;
68
69 CURSOR gl_segment_check IS
70 SELECT 'Y'
71 FROM ra_account_defaults def,
72 ra_account_default_segments seg
73 WHERE seg.table_name = 'RA_SALESREPS'
74 AND def.gl_default_id = seg.gl_default_id
75 AND def.type = 'REV';
76
77 CURSOR sys_param_check IS
78 SELECT 'Y'
79 FROM ar_system_parameters
80 WHERE salesrep_required_flag = 'Y';
81
82 CURSOR get_func_curr_code(p_set_of_books_id IN NUMBER) IS
83 SELECT currency_code
84 FROM gl_sets_of_books
85 WHERE set_of_books_id = p_set_of_books_id;
86
87 CURSOR get_send_flag(p_lease_id NUMBER) IS
88 SELECT nvl(send_entries,'Y')
89 FROM pn_lease_details_all
90 WHERE lease_id = p_lease_id;
91
92 CURSOR get_opt_attr IS
93 SELECT gb.COLUMN_ID
94 FROM RA_GROUP_BYS gb,
95 RA_GROUPING_TRX_TYPES gt,
96 RA_GROUPING_RULES gr,
97 RA_BATCH_SOURCES bs
98 WHERE gb.GROUPING_TRX_TYPE_ID = gt.GROUPING_TRX_TYPE_ID
99 AND gt.GROUPING_RULE_ID = gr.GROUPING_RULE_ID
100 AND gr.GROUPING_RULE_ID = bs.GROUPING_RULE_ID
101 AND bs.BATCH_SOURCE_ID = 24
102 AND NVL(bs.org_id,-99) = NVL(pn_mo_cache_utils.get_current_org_id,-99); --Bug#6319026
103
104 CURSOR get_post_to_gl(p_trx_type_id NUMBER, p_org_id NUMBER) IS
105 SELECT post_to_gl
106 FROM ra_cust_trx_types_all
107 WHERE cust_trx_type_id = p_trx_type_id
108 AND org_id = p_org_id;
109
110 /* Global Flags */
111 g_invalid_group_flag BOOLEAN := FALSE;
112 g_no_group_flag BOOLEAN := FALSE;
113 g_grp_by_purpose_flag BOOLEAN := FALSE;
114 g_grp_by_type_flag BOOLEAN := FALSE;
115 g_grp_by_lenum_flag BOOLEAN := FALSE;
116
117 l_func_curr_code GL_SETS_OF_BOOKS.currency_code%TYPE;
118 Q_Billitem_grp VARCHAR2(30000);
119 Q_Billitem_nogrp VARCHAR2(30000);
120 l_ord_clause VARCHAR(1000) :=
121 ' order by
122 TRUNC(pi.accounted_date),conv_rate, conv_rate_type,
123 pi.export_currency_code, pt.cust_trx_type_id, gl_date,
124 pt.inv_rule_id, pt.salesrep_id, pt.cust_po_number,
125 pt.receipt_method_id, pt.ap_ar_term_id, pi.due_date,
126 pi.customer_id, hzc.cust_acct_site_id, hzc1.cust_acct_site_id,
127 legal_entity_id';
128
129 g_cursor_grp INTEGER;
130 g_cursor_nogrp INTEGER;
131 g_cursor_select_grp INTEGER;
132 g_cursor_select_nogrp INTEGER;
133 Q_select_grp VARCHAR2(32000);
134 Q_select_nogrp VARCHAR2(32000);
135
136 /* exceptions */
137 GENERIC_EXPORT_EXCEPTION EXCEPTION;
138 --------------------------------------------------------------------------------
139 -- For setting PN's Invoice Info
140 -- ( Run as a Conc Process )
141 -- 17-FEB-04 Satish o Fixed for BUG# 2938185. Added 11 parameters
142 -- for this procedure to be called from SRS screen.
143 -- When invoced from SRS screen will will call with
144 -- 11 params. Form will call this with with only
145 -- group_id after all items are updated with same
146 -- export_group_id in the form.
147 -- 29-APR-04 Anand o Added another condition before calling proc
148 -- PN_EXP_TO_AR.EXP_TO_AR_GRP. This condition is needed
149 -- when grouping rule name has no optional attr attched.
150 -- In this case, we need to group by ONLY mandatory attr
151 -- Bug#3586774
152 -- 28-OCT-05 sdmahesh o ATG mandated changes for SQL literals
153 -- 24-JUL-06 Hareesha o Bug# 5398654 Consider the lease-no ,sched dt,
154 -- due dt,amt due ranges while exporting.
155 -- 07-AUG-06 Hareesha o Bug #5405883 Modified Q_Billitem_grp and
156 -- Q_Billitem_nogrp to select schedule_date.
157 --------------------------------------------------------------------------------
158 PROCEDURE Exp_To_AR (
159 errbuf OUT NOCOPY VARCHAR2
160 ,retcode OUT NOCOPY VARCHAR2
161 ,p_lease_num_low VARCHAR2
162 ,p_lease_num_high VARCHAR2
163 ,p_sch_dt_low VARCHAR2
164 ,p_sch_dt_high VARCHAR2
165 ,p_due_dt_low VARCHAR2
166 ,p_due_dt_high VARCHAR2
167 ,p_pay_prps_code VARCHAR2
168 ,p_prd_name VARCHAR2
169 ,p_amt_low NUMBER
170 ,p_amt_high NUMBER
171 ,p_customer_id NUMBER
172 ,p_grp_param VARCHAR2
173 )
174 IS
175
176 INVALID_GROUP_RULE EXCEPTION;
177 err_msg VARCHAR2(2000);
178 l_lease_num_where_clause VARCHAR2(4000);
179 l_sch_date_where_clause VARCHAR2(4000);
180 l_due_date_where_clause VARCHAR2(4000);
181 l_pay_prps_where_clause VARCHAR2(4000);
182 l_prd_name_where_clause VARCHAR2(4000);
183 l_amt_where_clause VARCHAR2(4000);
184 l_customer_where_clause VARCHAR2(4000);
185 l_param_where_clause VARCHAR2(30000);
186 l_groupId VARCHAR2(10);
187 l_set_of_books_id NUMBER := to_number(pn_mo_cache_utils.get_profile_value('PN_SET_OF_BOOKS_ID',
188 pn_mo_cache_utils.get_current_org_id));
189
190
191 BEGIN
192 pnp_debug_pkg.log('-------- PN_EXP_TO_AR.EXP_TO_AR --------- (+)');
193
194 OPEN get_func_curr_code(l_set_of_books_id);
195 FETCH get_func_curr_code INTO l_func_curr_code;
196 CLOSE get_func_curr_code;
197
198 Q_Billitem_grp :=
199 'SELECT pi.payment_item_id, pi.payment_term_id,
200 pi.export_currency_code, pi.export_currency_amount,
201 pi.customer_id AS customer_id, hzc.cust_acct_site_id,
202 hzc1.cust_acct_site_id, pt.tax_code_id,
203 pt.tax_classification_code, pt.legal_entity_id AS legal_entity_id,
204 pt.inv_rule_id, pt.account_rule_id,
205 pt.ap_ar_term_id, pt.cust_trx_type_id AS cust_trx_type_id,
206 pt.receipt_method_id, pt.cust_po_number,
207 pt.tax_included, pt.salesrep_id,
208 pt.project_attribute_category,pt.project_attribute3,
209 pt.project_attribute4, pt.project_attribute5,
210 pt.project_attribute6, pt.project_attribute7,
211 pi.org_id AS org_id, le.lease_num,
212 pi.payment_schedule_id, ps.period_name,
213 pt.payment_purpose_code, le.lease_id,
214 pi.due_date, pt.normalize,
215 TRUNC(pi.accounted_date), pi.rate,
216 pt.location_id, NVL(pld.send_entries, ''Y''),
217 pd.account_id as rec_account,
218 TO_DATE(DECODE(pt.inv_rule_id||pt.account_rule_id||cust_trx.post_to_gl
219 ,''Y'', to_char(PNP_UTIL_FUNC.Get_Start_Date(ps.period_name,
220 pn_mo_cache_utils.get_current_org_id)
221 , ''MM/DD/YYYY'')
222 ,NULL)
223 ,''MM/DD/YYYY'') as gl_date,
224 DECODE(UPPER('''||l_func_curr_code||''')
225 ,UPPER(pi.export_currency_code),1
226 ,DECODE(UPPER(PNP_UTIL_FUNC.check_conversion_type('''||l_func_curr_code||''',
227 pn_mo_cache_utils.get_current_org_id))
228 ,''USER'',pi.rate
229 ,NULL)) as conv_rate,
230 DECODE(UPPER('''||l_func_curr_code||''')
231 ,UPPER(pi.export_currency_code),''User''
232 ,PNP_UTIL_FUNC.check_conversion_type('''||l_func_curr_code||''',
233 pn_mo_cache_utils.get_current_org_id)
234 ) as conv_rate_type,
235 pt.payment_purpose_code as payment_purpose,
236 pt.payment_term_type_code as payment_type,
237 TO_DATE(DECODE(rr.type||rr.frequency ,''ASPECIFIC'',NULL,
238 to_char(PNP_UTIL_FUNC.Get_Start_Date(ps.period_name,
239 pn_mo_cache_utils.get_current_org_id)
240 , ''MM/DD/YYYY''))
241 ,''MM/DD/YYYY'') as rule_gl_date,
242 ps.schedule_date as schedule_date
243 FROM PN_PAYMENT_ITEMS pi, PN_PAYMENT_SCHEDULES_ALL ps,
244 PN_PAYMENT_TERMS_ALL pt, PN_LEASES_ALL le,
245 PN_LEASE_DETAILS_ALL pld, HZ_CUST_SITE_USES_ALL hzc,
246 HZ_CUST_SITE_USES_ALL hzc1, HZ_PARTIES party,
247 HZ_CUST_ACCOUNTS_ALL cust_acc, FND_LOOKUPS type_lookup,
248 FND_LOOKUPS purpose_lookup, HR_OPERATING_UNITS ou,
249 PN_DISTRIBUTIONS_ALL pd, RA_CUST_TRX_TYPES_ALL cust_trx,
250 RA_RULES rr
251 WHERE pi.payment_term_id = pt.payment_term_id
252 AND pi.payment_schedule_id = ps.payment_schedule_id
253 AND pi.export_to_ar_flag = ''Y''
254 and ps.payment_status_lookup_code =''APPROVED''
255 AND pi.payment_item_type_lookup_code = ''CASH''
256 AND pt.lease_id = le.lease_id
257 AND pld.lease_id = le.lease_id
258 AND le.lease_class_code <> ''DIRECT''
259 AND hzc.site_use_id = pi.customer_site_use_id
260 AND hzc1.site_use_id (+) = pi.cust_ship_site_id
261 AND NVL(pi.transferred_to_ar_flag, ''N'') = ''N''
262 AND type_lookup.lookup_type = ''PN_PAYMENT_TERM_TYPE''
263 AND type_lookup.lookup_code = pt.payment_term_type_code
264 AND purpose_lookup.lookup_type = ''PN_PAYMENT_PURPOSE_TYPE''
265 AND purpose_lookup.lookup_code = pt.payment_purpose_code
266 AND party.party_id = cust_acc.party_id
267 AND cust_acc.cust_account_id = pi.customer_id
268 AND ou.organization_id = pi.org_id
269 AND pi.export_currency_amount <> 0
270 AND pd.payment_term_id = pt.payment_term_id
271 AND pd.account_class = ''REC''
272 AND pt.cust_trx_type_id = cust_trx.cust_trx_type_id
273 AND NVL(cust_trx.org_id,-99) = NVL(pt.org_id,NVL(cust_trx.org_id,-99))
274 AND rr.rule_id(+) = pt.account_rule_id
275 ';
276 Q_Billitem_nogrp :=
277 ' SELECT pi.payment_item_id, pi.payment_term_id,
278 pi.export_currency_code, pi.export_currency_amount,
279 pi.customer_id AS customer_id, hzc.cust_acct_site_id,
280 hzc1.cust_acct_site_id, pt.tax_code_id,
281 pt.tax_classification_code, pt.legal_entity_id AS legal_entity_id,
282 pt.inv_rule_id, pt.account_rule_id,
283 pt.ap_ar_term_id, pt.cust_trx_type_id AS cust_trx_type_id,
284 pt.receipt_method_id, pt.cust_po_number,
285 pt.tax_included, pt.salesrep_id,
286 pt.project_attribute_category, pt.project_attribute3,
287 pt.project_attribute4, pt.project_attribute5,
288 pt.project_attribute6, pt.project_attribute7,
289 pi.org_id AS org_id,le.lease_num, pi.payment_schedule_id,
290 ps.period_name, pt.payment_purpose_code,
291 le.lease_id, pi.due_date,
292 pt.normalize, TRUNC(pi.accounted_date),pi.rate,
293 PT.Location_id,
294 pt.payment_purpose_code as payment_purpose,
295 pt.payment_term_type_code as payment_type,
296 ps.schedule_date as schedule_date
297 FROM PN_PAYMENT_ITEMS pi, PN_PAYMENT_SCHEDULES_ALL ps,
298 PN_PAYMENT_TERMS_ALL pt, PN_LEASES_ALL le,
299 HZ_CUST_SITE_USES_ALL hzc, HZ_CUST_SITE_USES_ALL hzc1,
300 hz_parties party, hz_cust_accounts_ALL cust_acc,
301 fnd_lookups type_lookup, fnd_lookups purpose_lookup,
302 hr_operating_units ou
303 WHERE pi.payment_term_id = pt.payment_term_id
304 AND pi.payment_schedule_id = ps.payment_schedule_id
305 AND pi.export_to_ar_flag = ''Y''
306 and ps.payment_status_lookup_code =''APPROVED''
307 AND pi.payment_item_type_lookup_code = ''CASH''
308 AND pt.lease_id = le.lease_id
309 AND le.lease_class_code <> ''DIRECT''
310 AND hzc.site_use_id = pi.customer_site_use_id
311 AND hzc1.site_use_id (+) = pi.cust_ship_site_id
312 AND NVL(pi.transferred_to_ar_flag, ''N'') = ''N''
313 AND type_lookup.lookup_type = ''PN_PAYMENT_TERM_TYPE''
314 AND type_lookup.lookup_code = pt.payment_term_type_code
315 AND purpose_lookup.lookup_type = ''PN_PAYMENT_PURPOSE_TYPE''
316 AND purpose_lookup.lookup_code = pt.payment_purpose_code
317 AND party.party_id = cust_acc.party_id
318 AND cust_acc.cust_account_id = pi.customer_id
319 AND ou.organization_id = pi.org_id
320 AND pi.export_currency_amount <> 0 ' ;
321
322 g_invalid_group_flag := FALSE;
323 g_no_group_flag := FALSE;
324 g_grp_by_purpose_flag := FALSE;
325 g_grp_by_type_flag := FALSE;
326 g_grp_by_lenum_flag := FALSE;
327
328 FOR opt_attr IN get_opt_attr
329 LOOP
330 IF opt_attr.COLUMN_ID = 27 THEN
331 g_no_group_flag := TRUE;
332 EXIT;
333 ELSIF opt_attr.COLUMN_ID = 34 THEN
334 g_grp_by_purpose_flag := TRUE;
335 ELSIF opt_attr.COLUMN_ID = 88 THEN
336 g_grp_by_type_flag := TRUE;
337 ELSIF opt_attr.COLUMN_ID = 89 THEN
338 g_grp_by_lenum_flag := TRUE;
339 ELSE
340 g_invalid_group_flag := TRUE;
341 END IF;
342 END LOOP;
343
344 IF p_grp_param IS NULL THEN
345
346 SELECT TO_CHAR(pn_payments_group_s.NEXTVAL)
347 INTO l_groupId
348 FROM DUAL;
349
350 IF p_lease_num_low IS NOT NULL AND
351 p_lease_num_high IS NOT NULL THEN
352 l_lease_num_where_clause := ' AND le.lease_num BETWEEN :l_lease_num_low AND :l_lease_num_high';
353
354 ELSIF p_lease_num_low IS NULL AND
355 p_lease_num_high IS NOT NULL THEN
356 l_lease_num_where_clause := ' AND le.lease_num <= :l_lease_num_high';
357
358 ELSIF p_lease_num_low IS NOT NULL AND
359 p_lease_num_high IS NULL THEN
360 l_lease_num_where_clause := ' AND le.lease_num >= :l_lease_num_low';
361
362 ELSE
363 l_lease_num_where_clause := ' AND 2=2 ';
364 END IF;
365
366 IF p_sch_dt_low IS NOT NULL AND
367 p_sch_dt_high IS NOT NULL THEN
368 l_sch_date_where_clause := ' AND ps.schedule_date BETWEEN :l_sch_dt_low AND :l_sch_dt_high';
369
370 ELSIF p_sch_dt_low IS NULL AND
371 p_sch_dt_high IS NOT NULL THEN
372 l_sch_date_where_clause := ' AND ps.schedule_date <= :l_sch_dt_high';
373
374 ELSIF p_sch_dt_low IS NOT NULL AND
375 p_sch_dt_high IS NULL THEN
376 l_sch_date_where_clause := ' AND ps.schedule_date >= :l_sch_dt_low';
377
378 ELSE
379 l_sch_date_where_clause := ' AND 3=3 ';
380 END IF;
381
382 IF p_due_dt_low IS NOT NULL AND
383 p_due_dt_high IS NOT NULL THEN
384 l_due_date_where_clause := ' AND pi.due_date BETWEEN :l_due_dt_low AND :l_due_dt_high';
385
386 ELSIF p_due_dt_low IS NULL AND
387 p_due_dt_high IS NOT NULL THEN
388 l_due_date_where_clause := ' AND pi.due_date <= :l_due_dt_high';
389
390 ELSIF p_due_dt_low IS NOT NULL AND
391 p_due_dt_high IS NULL THEN
392 l_due_date_where_clause := ' AND pi.due_date >= :l_due_dt_low';
393
394 ELSE
395 l_due_date_where_clause := ' AND 3=3 ';
396 END IF;
397
398 IF p_pay_prps_code IS NOT NULL THEN
399 l_pay_prps_where_clause := ' AND pt.payment_purpose_code = :l_pay_prps_code';
400
401 ELSE
402 l_pay_prps_where_clause := ' AND 4=4 ';
403 END IF;
404
405 IF p_prd_name IS NOT NULL THEN
406 l_prd_name_where_clause := ' AND ps.period_name = :l_prd_name';
407
408 ELSE
409 l_prd_name_where_clause := ' AND 5=5';
410 END IF;
411
412 IF p_amt_low IS NOT NULL AND
413 p_amt_high IS NOT NULL THEN
414 l_amt_where_clause := ' AND pi.actual_amount BETWEEN :l_amt_low AND :l_amt_high';
415
416 ELSIF p_amt_low IS NULL AND
417 p_amt_high IS NOT NULL THEN
418 l_amt_where_clause := ' AND pi.actual_amount <= :l_amt_high';
419
420 ELSIF p_amt_low IS NOT NULL AND
421 p_amt_high IS NULL THEN
422 l_amt_where_clause := ' AND pi.actual_amount >= :l_amt_low';
423
424 ELSE
425 l_amt_where_clause := ' AND 6=6 ';
426 END IF;
427
428 IF p_customer_id IS NOT NULL THEN
429 l_customer_where_clause := ' AND pi.customer_id = :l_customer_id';
430 ELSE
431 l_customer_where_clause := ' AND 7=7 ';
432 END IF;
433 l_param_where_clause := l_lease_num_where_clause ||
434 l_sch_date_where_clause ||
435 l_due_date_where_clause ||
436 l_pay_prps_where_clause ||
437 l_prd_name_where_clause ||
438 l_amt_where_clause ||
439 l_customer_where_clause;
440 ELSE
441 l_param_where_clause := ' AND pi.export_group_id = :l_grp_param';
442 l_groupId := p_grp_param;
443 END IF;
444
445 IF g_no_group_flag THEN
446
447 Q_Billitem_nogrp := Q_Billitem_nogrp ||l_param_where_clause;
448 PN_EXP_TO_AR.EXP_TO_AR_NO_GRP(errbuf
449 ,retcode
450 ,l_groupId
451 ,p_lease_num_low
452 ,p_lease_num_high
453 ,p_sch_dt_low
454 ,p_sch_dt_high
455 ,p_due_dt_low
456 ,p_due_dt_high
457 ,p_pay_prps_code
458 ,p_prd_name
459 ,p_amt_low
460 ,p_amt_high
461 ,p_customer_id
462 ,p_grp_param
463 );
464 ELSIF g_invalid_group_flag AND NOT(g_no_group_flag) THEN
465 RAISE INVALID_GROUP_RULE;
466
467 ELSIF g_grp_by_purpose_flag OR
468 g_grp_by_type_flag OR
469 g_grp_by_lenum_flag OR
470 (NOT(g_invalid_group_flag) AND NOT(g_no_group_flag)) THEN
471
472 /* Form the Order by clause of optional attributes and print the optional attributes */
473 IF g_grp_by_purpose_flag THEN
474 fnd_message.set_name ('PN','PN_EXPAR_PMT_PUR');
475 pnp_debug_pkg.put_log_msg(fnd_message.get);
476 l_ord_clause := l_ord_clause || ' , payment_purpose';
477 END IF;
478 IF g_grp_by_type_flag THEN
479 fnd_message.set_name ('PN','PN_EXPAR_PMT_TYP');
480 pnp_debug_pkg.put_log_msg(fnd_message.get);
481 l_ord_clause := l_ord_clause || ' , payment_type';
482 END IF;
483 IF g_grp_by_lenum_flag THEN
484 fnd_message.set_name ('PN','PN_EXPAR_LSNO');
485 pnp_debug_pkg.put_log_msg(fnd_message.get);
486 l_ord_clause := l_ord_clause || ' , le.lease_num';
487 END IF;
488 Q_Billitem_grp := Q_Billitem_grp ||l_param_where_clause || l_ord_clause;
489 PN_EXP_TO_AR.EXP_TO_AR_GRP(errbuf
490 ,retcode
491 ,l_groupId
492 ,p_lease_num_low
493 ,p_lease_num_high
494 ,p_sch_dt_low
495 ,p_sch_dt_high
496 ,p_due_dt_low
497 ,p_due_dt_high
498 ,p_pay_prps_code
499 ,p_prd_name
500 ,p_amt_low
501 ,p_amt_high
502 ,p_customer_id
503 ,p_grp_param
504 );
505 END IF;
506
507 pnp_debug_pkg.log('-------- PN_EXP_TO_AR.EXP_TO_AR --------- (-)');
508
509 EXCEPTION
510
511 WHEN INVALID_GROUP_RULE THEN
512 fnd_message.set_name ('PN', 'PN_INVALID_GROUP_RULE_ATTACHED');
513 err_msg := fnd_message.get;
514 pnp_debug_pkg.put_log_msg(err_msg);
515 errbuf := err_msg;
516 retcode := 2;
517 WHEN OTHERS THEN
518 RAISE;
519
520 END EXP_TO_AR;
521
522 /*-----------------------------------------------------------------------------
523 Description:
524 Call this procedure if a Grouping Rule is specified such that
525 Items can be grouped into one invoice
526
527 HISTORY:
528 -- 03-DEC-03 atuppad o Created
529 -- 20-AUG-04 kkhegde o Bug 3836127 - truncated location code to 30 characters
530 before inserting into interface_line_attribute2
531 -- 22-NOV-04 kkhegde o Bug 3751438 - fixed the validation for distributions
532 -- 22-DEC-04 Kiran o Fix for 3751438 - corrected it for bug # 4083036
533 -- 10-MAR-05 piagrawa o Bug #4231051 - Truncated the attribute values to 30
534 -- characters before inserting into ra_interface_lines,
535 -- ra_interface_salescredits and
536 -- ra_interface_distributions tables
537 -- 15-JUL-05 hareesha o Bug 4284035 - Replaced RA_INTERFACE_DISTRIBUTIONS_ALL
538 -- with _ALL table.
539 -- 11-OCT-05 pikhar o Bug 4652946 - Added trunc to pi.accounted_date in
540 -- Q_Billitem, l_ord_clause
541 -- 28-OCT-05 sdmahesh o ATG mandated changes for SQL literals
542 -- 24-MAR-06 Hareesha o Bug 5116270 Modified get_salesrep_number to pass
543 -- org_id as parameter.
544 -- 07-AUG-06 Hareesha o Bug #5405883 Inserted schedule_date as rule_start_date
545 -- into ra_interface_lines_all instead of rule_gl_date.
546 -- 29-Nov-12 ppenumar o Bug #14762515 Used the plsql table 'err_ar_tble' to
547 -- display the details of the failed billing items in the log.
548 -----------------------------------------------------------------------------*/
549 Procedure EXP_TO_AR_GRP (
550 errbuf IN OUT NOCOPY VARCHAR2
551 ,retcode IN OUT NOCOPY VARCHAR2
552 ,p_groupId VARCHAR2
553 ,p_lease_num_low VARCHAR2
554 ,p_lease_num_high VARCHAR2
555 ,p_sch_dt_low VARCHAR2
556 ,p_sch_dt_high VARCHAR2
557 ,p_due_dt_low VARCHAR2
558 ,p_due_dt_high VARCHAR2
559 ,p_pay_prps_code VARCHAR2
560 ,p_prd_name VARCHAR2
561 ,p_amt_low NUMBER
562 ,p_amt_high NUMBER
563 ,p_customer_id NUMBER
564 ,p_grp_param VARCHAR2
565 )
566 IS
567
568 l_acnt_cls PN_DISTRIBUTIONS.account_class%TYPE;
569 l_percent PN_DISTRIBUTIONS.percentage%TYPE;
570 l_location_code PN_LOCATIONS.LOCATION_CODE%TYPE;
571 l_inv_rule_name RA_RULES.NAME%TYPE;
572 l_inv_rule_type RA_RULES.TYPE%TYPE;
573 l_inv_rule_freq RA_RULES.FREQUENCY%TYPE;
574 l_acc_rule_name RA_RULES.NAME%TYPE;
575 l_acc_rule_type RA_RULES.TYPE%TYPE;
576 l_acc_rule_freq RA_RULES.FREQUENCY%TYPE;
577 l_desc RA_INTERFACE_LINES.description%TYPE;
578 l_salesrep_number RA_SALESREPS.SALESREP_NUMBER%TYPE;
579 l_sales_credit_id RA_SALESREPS.SALES_CREDIT_TYPE_ID%TYPE;
580 l_cust_trx_name RA_CUST_TRX_TYPES.NAME%TYPE;
581 l_term_name RA_TERMS.NAME%TYPE;
582 l_pay_method_name AR_RECEIPT_METHODS.NAME%TYPE;
583 l_amt NUMBER;
584 l_prior_payment_schedule_id NUMBER := -999;
585 l_last_updated_by NUMBER := FND_GLOBAL.USER_ID;
586 l_last_update_login NUMBER := FND_GLOBAL.LOGIN_ID;
587 l_last_update_date DATE := sysdate;
588 l_context VARCHAR2(200);
589 l_batch_name RA_BATCH_SOURCES.NAME%TYPE;
590 l_precision NUMBER;
591 l_ext_precision NUMBER;
592 l_min_acct_unit NUMBER;
593 t_count NUMBER := 0;
594 e_count NUMBER := 0;
595 s_count NUMBER := 0;
596 l_tax_code AR_VAT_TAX.tax_code%TYPE;
597 l_tax_classification_code pn_payment_terms.tax_classification_code%TYPE;
598 l_rev_acc_alloc_rule RA_BATCH_SOURCES.rev_acc_allocation_rule%TYPE;
599 l_rev_flag VARCHAR2(1);
600 l_rec_flag VARCHAR2(1);
601 l_ast_flag VARCHAR2(1);
602 l_rec_cnt NUMBER;
603 l_prof_optn VARCHAR2(30);
604 l_err_msg1 VARCHAR2(2000);
605 l_err_msg2 VARCHAR2(2000);
606 l_err_msg3 VARCHAR2(2000);
607 l_err_msg4 VARCHAR2(2000);
608 l_sys_para VARCHAR2(1);
609 l_gl_seg VARCHAR2(1);
610 l_sal_cred VARCHAR2(1);
611 l_total_rev_amt NUMBER := 0;
612 l_total_rev_percent NUMBER := 0;
613 l_diff_amt NUMBER := 0;
614 l_set_of_books_id NUMBER := to_number(pn_mo_cache_utils.get_profile_value('PN_SET_OF_BOOKS_ID',
615 pn_mo_cache_utils.get_current_org_id));
616 l_func_curr_code GL_SETS_OF_BOOKS.currency_code%TYPE;
617 exp_ar_tbl exp_ar_tbl_type;
618 err_ar_tbl err_ar_tbl_type;-- Bug 14762515
619 l_total_items NUMBER := 0;
620 l_index NUMBER := 1;
621 l_index_2 NUMBER := 1; -- Bug 14762515
622 l_start NUMBER;
623 l_next NUMBER;
624 l_count NUMBER;
625 l_item_prcsed NUMBER := 0;
626 l_rec_insert_flag BOOLEAN := TRUE;
627 l_valid_rec_accs BOOLEAN := TRUE;
628 l_grp NUMBER;
629 l_post_to_gl RA_CUST_TRX_TYPES_ALL.POST_TO_GL%TYPE;
630 l_derive_date_flag RA_BATCH_SOURCES.derive_date_flag%TYPE;
631 l_rule_start_date RA_INTERFACE_LINES.RULE_START_DATE%TYPE := NULL;
632 l_count_grp INTEGER;
633 l_rows_grp INTEGER;
634 v_pn_payment_item_id PN_PAYMENT_ITEMS.payment_item_id%TYPE;
635 v_pn_payment_term_id PN_PAYMENT_ITEMS.payment_term_id%TYPE;
636 v_pn_export_currency_code PN_PAYMENT_ITEMS.export_currency_code%TYPE;
637 v_pn_export_currency_amount PN_PAYMENT_ITEMS.export_currency_amount%TYPE;
638 v_pn_customer_id PN_PAYMENT_ITEMS.customer_id%TYPE;
639 v_pn_customer_site_use_id PN_PAYMENT_ITEMS.customer_site_use_id%TYPE;
640 v_pn_cust_ship_site_id PN_PAYMENT_TERMS.cust_ship_site_id%TYPE;
641 v_pn_tax_code_id PN_PAYMENT_TERMS.tax_code_id%TYPE;
642 v_pn_tax_classification_code PN_PAYMENT_TERMS.tax_classification_code%TYPE;
643 v_pn_legal_entity_id PN_PAYMENT_TERMS.legal_entity_id%TYPE;
644 v_pn_inv_rule_id PN_PAYMENT_TERMS.inv_rule_id%TYPE;
645 v_pn_account_rule_id PN_PAYMENT_TERMS.account_rule_id%TYPE;
646 v_pn_term_id PN_PAYMENT_TERMS.ap_ar_term_id%TYPE;
647 v_pn_trx_type_id PN_PAYMENT_TERMS.cust_trx_type_id%TYPE;
648 v_pn_pay_method_id PN_PAYMENT_TERMS.receipt_method_id%TYPE;
649 v_pn_po_number PN_PAYMENT_TERMS.cust_po_number%TYPE;
650 v_pn_tax_included PN_PAYMENT_TERMS.tax_included%TYPE;
651 v_pn_salesrep_id PN_PAYMENT_TERMS.salesrep_id%TYPE;
652 v_pn_proj_attr_catg PN_PAYMENT_TERMS.project_attribute_category%TYPE;
653 v_pn_proj_attr3 PN_PAYMENT_TERMS.project_attribute3%TYPE;
654 v_pn_proj_attr4 PN_PAYMENT_TERMS.project_attribute4%TYPE;
655 v_pn_proj_attr5 PN_PAYMENT_TERMS.project_attribute5%TYPE;
656 v_pn_proj_attr6 PN_PAYMENT_TERMS.project_attribute6%TYPE;
657 v_pn_proj_attr7 PN_PAYMENT_TERMS.project_attribute7%TYPE;
658 v_pn_org_id PN_PAYMENT_TERMS.org_id%TYPE;
659 v_pn_lease_num PN_LEASES.lease_num%TYPE;
660 v_pn_payment_schedule_id PN_PAYMENT_ITEMS.payment_schedule_id%TYPE;
661 v_pn_period_name PN_PAYMENT_SCHEDULES.period_name%TYPE;
662 v_pn_description PN_PAYMENT_TERMS.payment_purpose_code%TYPE;
663 v_pn_lease_id PN_LEASES.lease_id%TYPE;
664 v_transaction_date PN_PAYMENT_ITEMS.due_date%TYPE;
665 v_normalize PN_PAYMENT_TERMS.normalize%TYPE;
666 v_pn_accounted_date PN_PAYMENT_ITEMS.accounted_date%TYPE;
667 v_pn_rate PN_PAYMENT_ITEMS.rate%TYPE;
668 v_location_id PN_LOCATIONS.LOCATION_ID%TYPE;
669 v_send_entries PN_LEASE_DETAILS.send_entries%TYPE;
670 v_rec_account PN_DISTRIBUTIONS.account_id%TYPE;
671 v_gl_date RA_CUST_TRX_LINE_GL_DIST.gl_date%TYPE;
672 v_conv_rate_type PN_CURRENCIES.conversion_type%TYPE;
673 v_conv_rate PN_PAYMENT_ITEMS.rate%TYPE;
674 v_payment_purpose PN_PAYMENT_TERMS.payment_purpose_code%TYPE;
675 v_payment_type PN_PAYMENT_TERMS.payment_term_type_code%TYPE;
676 v_rule_gl_date RA_CUST_TRX_LINE_GL_DIST.gl_date%TYPE;
677 v_schedule_date PN_PAYMENT_SCHEDULES.schedule_date%TYPE;
678 v_pn_payment_term_id1 PN_PAYMENT_ITEMS.payment_term_id%TYPE;
679 v_pn_le_id1 PN_PAYMENT_TERMS.legal_entity_id%TYPE;
680 v_pn_customer_id1 PN_PAYMENT_ITEMS.customer_id%TYPE;
681 v_pn_trx_type_id1 PN_PAYMENT_TERMS.cust_trx_type_id%TYPE;
682 v_pn_org_id1 PN_PAYMENT_TERMS.org_id%TYPE;
683 l_rows_select_grp NUMBER;
684 l_count_select_grp NUMBER;
685 TYPE le_ar_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
686 TYPE term_ar_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
687 le_ar_tbl le_ar_tbl_type;
688 term_ar_tbl term_ar_tbl_type;
689 l_actual_amount NUMBER; -- Bug 14762515
690 l_message varchar2(100); -- Bug 14762515
691
692
693
694 BEGIN
695
696 /* Get the Optional Attributes of the Grouping Rule mentioned in Batch Source */
697 l_context := 'Getting the Optional Attributes of the Grouping Rule mentioned in Batch Source';
698
699 fnd_message.set_name ('PN','PN_EXPAR_OPT');
700 pnp_debug_pkg.put_log_msg(fnd_message.get);
701 l_context := 'Getting the Batch Source Name';
702 pnp_debug_pkg.log(l_context);
703 /* get the batch source name */
704 OPEN get_batch_source_name;
705 FETCH get_batch_source_name into l_batch_name, l_rev_acc_alloc_rule,
706 l_sal_cred, l_derive_date_flag;
707 CLOSE get_batch_source_name;
708
709 l_index := 1;
710 le_ar_tbl.delete;
711 term_ar_tbl.delete;
712
713 Q_select_grp := 'SELECT payment_term_id,
714 customer_id,
715 cust_trx_type_id,
716 org_id
717 FROM ('||Q_Billitem_grp||')
718 WHERE legal_entity_id IS NULL';
719 g_cursor_select_grp := dbms_sql.open_cursor;
720 dbms_sql.parse(g_cursor_select_grp,Q_select_grp,dbms_sql.native);
721 do_binding (g_cursor_select_grp
722 ,p_lease_num_low
723 ,p_lease_num_high
724 ,p_sch_dt_low
725 ,p_sch_dt_high
726 ,p_due_dt_low
727 ,p_due_dt_high
728 ,p_pay_prps_code
729 ,p_prd_name
730 ,p_amt_low
731 ,p_amt_high
732 ,p_customer_id
733 ,p_grp_param
734 );
735
736 dbms_sql.define_column (g_cursor_select_grp,1,v_pn_payment_term_id1);
737 dbms_sql.define_column (g_cursor_select_grp,2,v_pn_customer_id1);
738 dbms_sql.define_column (g_cursor_select_grp,3,v_pn_trx_type_id1);
739 dbms_sql.define_column (g_cursor_select_grp,4,v_pn_org_id1);
740 l_rows_select_grp := dbms_sql.execute(g_cursor_select_grp);
741
742 LOOP
743 BEGIN
744 l_count_select_grp := dbms_sql.fetch_rows(g_cursor_select_grp);
745 EXIT WHEN l_count_select_grp <> 1;
746 dbms_sql.column_value (g_cursor_select_grp, 1,term_ar_tbl(l_index));
747 dbms_sql.column_value (g_cursor_select_grp, 2,v_pn_customer_id1);
748 dbms_sql.column_value (g_cursor_select_grp,3,v_pn_trx_type_id1);
749 dbms_sql.column_value (g_cursor_select_grp,4,v_pn_org_id1);
750 le_ar_tbl(l_index) := pn_r12_util_pkg.get_le_for_ar(v_pn_customer_id1,
751 v_pn_trx_type_id1,
752 v_pn_org_id1);
753
754 l_index := l_index+1;
755 END;
756 END LOOP;
757
758 FORALL i IN term_ar_tbl.FIRST..term_ar_tbl.LAST
759 UPDATE pn_payment_terms_all
760 SET legal_entity_id = le_ar_tbl(i)
761 WHERE payment_term_id = term_ar_tbl(i);
762
763 IF dbms_sql.is_open (g_cursor_select_grp) THEN
764 dbms_sql.close_cursor (g_cursor_select_grp);
765 END IF;
766
767 l_context := 'Opening the cursor';
768 g_cursor_grp := dbms_sql.open_cursor;
769 dbms_sql.parse(g_cursor_grp, Q_Billitem_grp, dbms_sql.native);
770 do_binding (g_cursor_grp
771 ,p_lease_num_low
772 ,p_lease_num_high
773 ,p_sch_dt_low
774 ,p_sch_dt_high
775 ,p_due_dt_low
776 ,p_due_dt_high
777 ,p_pay_prps_code
778 ,p_prd_name
779 ,p_amt_low
780 ,p_amt_high
781 ,p_customer_id
782 ,p_grp_param
783 );
784
785
786 /* === LOGIC ===
787 o open the ref cursor using the query that we dynamically
788 constructed.
789 o fetch all the valid items into the PL/SQL table of
790 records.
791 === LOGIC === */
792
793 /* Initialize the table and loop index */
794 l_index := 1;
795 exp_ar_tbl.DELETE;
796 err_ar_tbl.DELETE; --Bug 14762515
797 dbms_sql.define_column (g_cursor_grp, 1, v_pn_payment_item_id);
798 dbms_sql.define_column (g_cursor_grp, 2, v_pn_payment_term_id);
799 dbms_sql.define_column (g_cursor_grp, 3, v_pn_export_currency_code,15);
800 dbms_sql.define_column (g_cursor_grp, 4, v_pn_export_currency_amount);
801 dbms_sql.define_column (g_cursor_grp, 5, v_pn_customer_id );
802 dbms_sql.define_column (g_cursor_grp, 6, v_pn_customer_site_use_id );
803 dbms_sql.define_column (g_cursor_grp, 7, v_pn_cust_ship_site_id);
804 dbms_sql.define_column (g_cursor_grp, 8, v_pn_tax_code_id);
805 dbms_sql.define_column (g_cursor_grp, 9, v_pn_tax_classification_code,30);
806 dbms_sql.define_column (g_cursor_grp, 10,v_pn_legal_entity_id);
807 dbms_sql.define_column (g_cursor_grp, 11,v_pn_inv_rule_id );
808 dbms_sql.define_column (g_cursor_grp, 12,v_pn_account_rule_id);
809 dbms_sql.define_column (g_cursor_grp, 13,v_pn_term_id);
810 dbms_sql.define_column (g_cursor_grp, 14,v_pn_trx_type_id);
811 dbms_sql.define_column (g_cursor_grp, 15,v_pn_pay_method_id);
812 dbms_sql.define_column (g_cursor_grp, 16,v_pn_po_number,50);
813 dbms_sql.define_column (g_cursor_grp, 17,v_pn_tax_included,1);
814 dbms_sql.define_column (g_cursor_grp, 18,v_pn_salesrep_id);
815 dbms_sql.define_column (g_cursor_grp, 19,v_pn_proj_attr_catg,30);
816 dbms_sql.define_column (g_cursor_grp, 20,v_pn_proj_attr3,150);
817 dbms_sql.define_column (g_cursor_grp, 21,v_pn_proj_attr4,150);
818 dbms_sql.define_column (g_cursor_grp, 22,v_pn_proj_attr5,150);
819 dbms_sql.define_column (g_cursor_grp, 23,v_pn_proj_attr6,150);
820 dbms_sql.define_column (g_cursor_grp, 24,v_pn_proj_attr7,150);
821 dbms_sql.define_column (g_cursor_grp, 25,v_pn_org_id);
822 dbms_sql.define_column (g_cursor_grp, 26,v_pn_lease_num,30);
823 dbms_sql.define_column (g_cursor_grp, 27,v_pn_payment_schedule_id);
824 dbms_sql.define_column (g_cursor_grp, 28,v_pn_period_name,15);
825 dbms_sql.define_column (g_cursor_grp, 29,v_pn_description,30);
826 dbms_sql.define_column (g_cursor_grp, 30,v_pn_lease_id);
827 dbms_sql.define_column (g_cursor_grp, 31,v_transaction_date);
828 dbms_sql.define_column (g_cursor_grp, 32,v_normalize,1);
829 dbms_sql.define_column (g_cursor_grp, 33,v_pn_accounted_date);
830 dbms_sql.define_column (g_cursor_grp, 34,v_pn_rate);
831 dbms_sql.define_column (g_cursor_grp, 35,v_location_id);
832 dbms_sql.define_column (g_cursor_grp, 36,v_send_entries,1);
833 dbms_sql.define_column (g_cursor_grp, 37,v_rec_account);
834 dbms_sql.define_column (g_cursor_grp, 38,v_gl_date);
835 dbms_sql.define_column (g_cursor_grp, 39,v_conv_rate);
836 dbms_sql.define_column (g_cursor_grp, 40,v_conv_rate_type,30);
837 dbms_sql.define_column (g_cursor_grp, 41,v_payment_purpose,30);
838 dbms_sql.define_column (g_cursor_grp, 42,v_payment_type,30);
839 dbms_sql.define_column (g_cursor_grp, 43,v_rule_gl_date);
840 dbms_sql.define_column (g_cursor_grp, 44,v_schedule_date);
841
842 l_rows_grp := dbms_sql.execute(g_cursor_grp);
843 LOOP
844 BEGIN
845 l_context := 'Fetching from the cursor';
846 l_count_grp := dbms_sql.fetch_rows( g_cursor_grp);
847 EXIT WHEN l_count_grp <> 1;
848 dbms_sql.column_value (g_cursor_grp, 1, exp_ar_tbl(l_index).pn_payment_item_id);
849 dbms_sql.column_value (g_cursor_grp, 2, exp_ar_tbl(l_index).pn_payment_term_id);
850 dbms_sql.column_value (g_cursor_grp, 3, exp_ar_tbl(l_index).pn_export_currency_code);
851 dbms_sql.column_value (g_cursor_grp, 4, exp_ar_tbl(l_index).pn_export_currency_amount);
852 dbms_sql.column_value (g_cursor_grp, 5, exp_ar_tbl(l_index).pn_customer_id);
853 dbms_sql.column_value (g_cursor_grp, 6, exp_ar_tbl(l_index).pn_customer_site_use_id);
854 dbms_sql.column_value (g_cursor_grp, 7, exp_ar_tbl(l_index).pn_cust_ship_site_id);
855 dbms_sql.column_value (g_cursor_grp, 8, exp_ar_tbl(l_index).pn_tax_code_id);
856 dbms_sql.column_value (g_cursor_grp, 9, exp_ar_tbl(l_index).pn_tax_classification_code);
857 dbms_sql.column_value (g_cursor_grp, 10,exp_ar_tbl(l_index).pn_legal_entity_id);
858 dbms_sql.column_value (g_cursor_grp, 11,exp_ar_tbl(l_index).pn_inv_rule_id);
859 dbms_sql.column_value (g_cursor_grp, 12,exp_ar_tbl(l_index).pn_account_rule_id);
860 dbms_sql.column_value (g_cursor_grp, 13,exp_ar_tbl(l_index).pn_term_id);
861 dbms_sql.column_value (g_cursor_grp, 14,exp_ar_tbl(l_index).pn_trx_type_id);
862 dbms_sql.column_value (g_cursor_grp, 15,exp_ar_tbl(l_index).pn_pay_method_id);
863 dbms_sql.column_value (g_cursor_grp, 16,exp_ar_tbl(l_index).pn_po_number);
864 dbms_sql.column_value (g_cursor_grp, 17,exp_ar_tbl(l_index).pn_tax_included);
865 dbms_sql.column_value (g_cursor_grp, 18,exp_ar_tbl(l_index).pn_salesrep_id);
866 dbms_sql.column_value (g_cursor_grp, 19,exp_ar_tbl(l_index).pn_proj_attr_catg);
867 dbms_sql.column_value (g_cursor_grp, 20,exp_ar_tbl(l_index).pn_proj_attr3);
868 dbms_sql.column_value (g_cursor_grp, 21,exp_ar_tbl(l_index).pn_proj_attr4);
869 dbms_sql.column_value (g_cursor_grp, 22,exp_ar_tbl(l_index).pn_proj_attr5);
870 dbms_sql.column_value (g_cursor_grp, 23,exp_ar_tbl(l_index).pn_proj_attr6);
871 dbms_sql.column_value (g_cursor_grp, 24,exp_ar_tbl(l_index).pn_proj_attr7);
872 dbms_sql.column_value (g_cursor_grp, 25,exp_ar_tbl(l_index).pn_org_id);
873 dbms_sql.column_value (g_cursor_grp, 26,exp_ar_tbl(l_index).pn_lease_num);
874 dbms_sql.column_value (g_cursor_grp, 27,exp_ar_tbl(l_index).pn_payment_schedule_id);
875 dbms_sql.column_value (g_cursor_grp, 28,exp_ar_tbl(l_index).pn_period_name);
876 dbms_sql.column_value (g_cursor_grp, 29,exp_ar_tbl(l_index).pn_description);
877 dbms_sql.column_value (g_cursor_grp, 30,exp_ar_tbl(l_index).pn_lease_id);
878 dbms_sql.column_value (g_cursor_grp, 31,exp_ar_tbl(l_index).transaction_date);
879 dbms_sql.column_value (g_cursor_grp, 32,exp_ar_tbl(l_index).normalize);
880 dbms_sql.column_value (g_cursor_grp, 33,exp_ar_tbl(l_index).pn_accounted_date);
881 dbms_sql.column_value (g_cursor_grp, 34,exp_ar_tbl(l_index).pn_rate);
882 dbms_sql.column_value (g_cursor_grp, 35,exp_ar_tbl(l_index).location_id);
883 dbms_sql.column_value (g_cursor_grp, 36,exp_ar_tbl(l_index).send_entries);
884 dbms_sql.column_value (g_cursor_grp, 37,exp_ar_tbl(l_index).rec_account);
885 dbms_sql.column_value (g_cursor_grp, 38,exp_ar_tbl(l_index).gl_date);
886 dbms_sql.column_value (g_cursor_grp, 39,exp_ar_tbl(l_index).conv_rate);
887 dbms_sql.column_value (g_cursor_grp, 40,exp_ar_tbl(l_index).conv_rate_type);
888 dbms_sql.column_value (g_cursor_grp, 41,exp_ar_tbl(l_index).payment_purpose);
889 dbms_sql.column_value (g_cursor_grp, 42,exp_ar_tbl(l_index).payment_type);
890 dbms_sql.column_value (g_cursor_grp, 43,exp_ar_tbl(l_index).rule_gl_date);
891 dbms_sql.column_value (g_cursor_grp, 44,exp_ar_tbl(l_index).schedule_date);
892
893 l_rev_flag := 'N';
894 l_rec_flag := 'N';
895 l_ast_flag := 'N';
896 l_rec_cnt := 0;
897 l_prof_optn := pn_mo_cache_utils.get_profile_value('PN_ACCOUNTING_OPTION',
898 pn_mo_cache_utils.get_current_org_id);
899
900 FOR dist_rec IN acnt_cls_cur(exp_ar_tbl(l_index).pn_payment_term_id)
901 LOOP
902
903 IF dist_rec.account_class IN ('REV') THEN
904 l_rev_flag := 'Y';
905 ELSIF dist_rec.account_class IN ('REC') THEN
906 l_rec_flag := 'Y';
907 ELSIF dist_rec.account_class IN ('UNEARN') THEN
908 l_ast_flag := 'Y';
909 END IF;
910
911 l_rec_cnt := l_rec_cnt + 1;
912
913 END LOOP;
914
915 t_count := t_count + 1;
916
917 /* Check for Invalid Items here */
918 IF UPPER(exp_ar_tbl(l_index).conv_rate_type) = 'USER'
919 AND exp_ar_tbl(l_index).conv_rate IS NULL THEN
920
921 fnd_message.set_name ('PN', 'PN_CONV_RATE_REQD');
922 l_err_msg4 := fnd_message.get;
923 pnp_debug_pkg.put_log_msg(l_err_msg4);
924
925 RAISE GENERIC_EXPORT_EXCEPTION;
926
927 END IF;
928
929 IF exp_ar_tbl(l_index).pn_term_id IS NULL
930 OR exp_ar_tbl(l_index).pn_trx_type_id IS NULL THEN
931
932 fnd_message.set_name ('PN', 'PN_PTRM_TRX_REQD_MSG');
933 l_err_msg3 := fnd_message.get;
934 pnp_debug_pkg.put_log_msg(l_err_msg3);
935
936 RAISE GENERIC_EXPORT_EXCEPTION;
937
938 END IF;
939
940 IF NVL(exp_ar_tbl(l_index).normalize,'N') = 'Y' THEN
941
942 IF (l_rev_flag <> 'Y' OR l_rec_flag <> 'Y' OR l_ast_flag <> 'Y') THEN
943
944 fnd_message.set_name ('PN', 'PN_ALL_ACNT_DIST_MSG');
945 l_err_msg1 := fnd_message.get;
946 pnp_debug_pkg.put_log_msg(l_err_msg1);
947
948 RAISE GENERIC_EXPORT_EXCEPTION;
949
950 END IF;
951
952 ELSIF NVL(exp_ar_tbl(l_index).normalize,'N') = 'N' THEN
953
954 IF (l_prof_optn = 'Y' AND (l_rev_flag <> 'Y' OR l_rec_flag <> 'Y')) OR
955 (l_prof_optn IN ('M', 'N') AND ((l_rev_flag = 'Y' AND l_rec_flag <> 'Y') OR
956 (l_rev_flag <> 'Y' AND l_rec_flag = 'Y')))
957 THEN
958
959 fnd_message.set_name ('PN', 'PN_REVREC_DIST_MSG');
960 l_err_msg2 := fnd_message.get;
961 pnp_debug_pkg.put_log_msg(l_err_msg2);
962
963 RAISE GENERIC_EXPORT_EXCEPTION;
964
965 END IF;
966
967 END IF;
968
969 exp_ar_tbl(l_index).set_of_books_id
970 := to_number(pn_mo_cache_utils.get_profile_value('PN_SET_OF_BOOKS_ID',
971 pn_mo_cache_utils.get_current_org_id));
972 -- increase the index only if the item needs to be inserted into interface tables
973 l_index := l_index+1;
974
975 EXCEPTION
976
977 WHEN GENERIC_EXPORT_EXCEPTION THEN
978 e_count := e_count + 1;
979 err_ar_tbl(l_index_2).pn_payment_item_id :=exp_ar_tbl(l_index).pn_payment_item_id;--Bug 14762515
980 err_ar_tbl(l_index_2).pn_payment_term_id :=exp_ar_tbl(l_index).pn_payment_term_id;--Bug 14762515
981 err_ar_tbl(l_index_2).pn_lease_num :=exp_ar_tbl(l_index).pn_lease_num; --Bug 14762515
982 err_ar_tbl(l_index_2).location_id :=exp_ar_tbl(l_index).location_id; --Bug 14762515
983 l_index_2 := l_index_2+1;
984 /* The below condition takes care that if the last record
985 is invalid, it is not part of PL/SQL table */
986 IF t_count = l_total_items THEN
987 exp_ar_tbl.DELETE(l_index);
988 END IF;
989
990 WHEN OTHERS THEN
991 RAISE;
992
993 END;
994
995 END LOOP; /* loop for c_billitem */
996 IF dbms_sql.is_open (g_cursor_grp) THEN
997 dbms_sql.close_cursor (g_cursor_grp);
998 END IF;
999
1000
1001 /*CLOSE c_billitem;*/
1002 /* we have now fetched all valid items into the table */
1003
1004 /* === LOGIC ===
1005 o loop through the table to identify the groups.
1006 o the items are already ordered appropriately.
1007 o once we identify a group,
1008 if the REC account for a group is not same then
1009 reject the whole group
1010 elsif the group is valid then
1011 insert into AR interface tables appropriately
1012 for all lines belonging to one group,
1013 insert only one distribution for REC with 0 amount
1014 end if
1015 === LOGIC === */
1016
1017 /* Initialize the counters */
1018 l_start := 1;
1019 l_next := 2;
1020 l_item_prcsed := 0;
1021 l_count := exp_ar_tbl.count;
1022
1023 fnd_message.set_name ('PN','PN_EXPAR_ITM_PROC');
1024 fnd_message.set_token ('NUM',l_count);
1025 pnp_debug_pkg.put_log_msg(fnd_message.get);
1026
1027 l_context := 'Finding the Groups of items';
1028 pnp_debug_pkg.log(l_context);
1029
1030 -- start loopin thru the table
1031 WHILE (l_item_prcsed < l_count)
1032 LOOP
1033
1034 IF ( (l_next <= l_count)
1035 AND ((exp_ar_tbl(l_start).gl_date = exp_ar_tbl(l_next).gl_date)
1036 OR (exp_ar_tbl(l_start).gl_date IS NULL AND exp_ar_tbl(l_next).gl_date IS NULL))
1037 AND ((exp_ar_tbl(l_start).pn_inv_rule_id = exp_ar_tbl(l_next).pn_inv_rule_id)
1038 OR (exp_ar_tbl(l_start).pn_inv_rule_id IS NULL AND exp_ar_tbl(l_next).pn_inv_rule_id IS NULL))
1039 AND ((exp_ar_tbl(l_start).pn_pay_method_id = exp_ar_tbl(l_next).pn_pay_method_id)
1040 OR (exp_ar_tbl(l_start).pn_pay_method_id IS NULL AND exp_ar_tbl(l_next).pn_pay_method_id IS NULL))
1041 AND ((exp_ar_tbl(l_start).pn_salesrep_id = exp_ar_tbl(l_next).pn_salesrep_id)
1042 OR (exp_ar_tbl(l_start).pn_salesrep_id IS NULL AND exp_ar_tbl(l_next).pn_salesrep_id IS NULL))
1043 AND ((exp_ar_tbl(l_start).pn_po_number = exp_ar_tbl(l_next).pn_po_number)
1044 OR (exp_ar_tbl(l_start).pn_po_number IS NULL AND exp_ar_tbl(l_next).pn_po_number IS NULL))
1045 AND ((exp_ar_tbl(l_start).set_of_books_id = exp_ar_tbl(l_next).set_of_books_id)
1046 OR (exp_ar_tbl(l_start).set_of_books_id IS NULL AND exp_ar_tbl(l_next).set_of_books_id IS NULL))
1047 AND ((exp_ar_tbl(l_start).pn_export_currency_code = exp_ar_tbl(l_next).pn_export_currency_code)
1048 OR (exp_ar_tbl(l_start).pn_export_currency_code IS NULL AND exp_ar_tbl(l_next).pn_export_currency_code IS NULL))
1049 AND ((exp_ar_tbl(l_start).pn_trx_type_id = exp_ar_tbl(l_next).pn_trx_type_id)
1050 OR (exp_ar_tbl(l_start).pn_trx_type_id IS NULL AND exp_ar_tbl(l_next).pn_trx_type_id IS NULL))
1051 AND ((exp_ar_tbl(l_start).pn_term_id = exp_ar_tbl(l_next).pn_term_id)
1052 OR (exp_ar_tbl(l_start).pn_term_id IS NULL AND exp_ar_tbl(l_next).pn_term_id IS NULL))
1053 AND ((exp_ar_tbl(l_start).conv_rate_type = exp_ar_tbl(l_next).conv_rate_type)
1054 OR (exp_ar_tbl(l_start).conv_rate_type IS NULL AND exp_ar_tbl(l_next).conv_rate_type IS NULL))
1055 AND ((exp_ar_tbl(l_start).conv_rate = exp_ar_tbl(l_next).conv_rate)
1056 OR (exp_ar_tbl(l_start).conv_rate IS NULL AND exp_ar_tbl(l_next).conv_rate IS NULL))
1057 AND ((exp_ar_tbl(l_start).pn_accounted_date = exp_ar_tbl(l_next).pn_accounted_date)
1058 OR (exp_ar_tbl(l_start).pn_accounted_date IS NULL AND exp_ar_tbl(l_next).pn_accounted_date IS NULL))
1059 AND ((exp_ar_tbl(l_start).pn_customer_id = exp_ar_tbl(l_next).pn_customer_id)
1060 OR (exp_ar_tbl(l_start).pn_customer_id IS NULL AND exp_ar_tbl(l_next).pn_customer_id IS NULL))
1061 AND ((exp_ar_tbl(l_start).pn_customer_site_use_id = exp_ar_tbl(l_next).pn_customer_site_use_id)
1062 OR (exp_ar_tbl(l_start).pn_customer_site_use_id IS NULL AND exp_ar_tbl(l_next).pn_customer_site_use_id IS NULL))
1063 AND ((exp_ar_tbl(l_start).pn_cust_ship_site_id = exp_ar_tbl(l_next).pn_cust_ship_site_id)
1064 OR (exp_ar_tbl(l_start).pn_cust_ship_site_id IS NULL AND exp_ar_tbl(l_next).pn_cust_ship_site_id IS NULL))
1065 AND ((exp_ar_tbl(l_start).transaction_date = exp_ar_tbl(l_next).transaction_date)
1066 OR (exp_ar_tbl(l_start).transaction_date IS NULL AND exp_ar_tbl(l_next).transaction_date IS NULL))
1067 AND ((exp_ar_tbl(l_start).pn_legal_entity_id = exp_ar_tbl(l_next).pn_legal_entity_id)
1068 OR (exp_ar_tbl(l_start).pn_legal_entity_id IS NULL AND exp_ar_tbl(l_next).pn_legal_entity_id IS NULL))
1069 AND ((g_grp_by_purpose_flag AND exp_ar_tbl(l_start).payment_purpose = exp_ar_tbl(l_next).payment_purpose)
1070 OR (NOT g_grp_by_purpose_flag))
1071 AND ((g_grp_by_type_flag AND exp_ar_tbl(l_start).payment_type = exp_ar_tbl(l_next).payment_type)
1072 OR (NOT g_grp_by_type_flag))
1073 AND ((g_grp_by_lenum_flag AND exp_ar_tbl(l_start).pn_lease_num = exp_ar_tbl(l_next).pn_lease_num)
1074 OR (NOT g_grp_by_lenum_flag))
1075 AND ((NVL(l_derive_date_flag,'N') = 'Y' AND
1076 exp_ar_tbl(l_start).rule_gl_date = exp_ar_tbl(l_next).rule_gl_date) OR
1077 (exp_ar_tbl(l_start).rule_gl_date IS NULL AND exp_ar_tbl(l_next).rule_gl_date IS NULL))
1078 ) THEN
1079
1080 -- increment 'next' counter. we are still getting the group.
1081 l_next := l_next + 1;
1082
1083 ELSE -- we have a group!
1084
1085 l_context := 'Group found. Checking REC account';
1086 pnp_debug_pkg.log(l_context);
1087
1088 -- validate the group for REC account.
1089 l_valid_rec_accs := TRUE;
1090
1091 FOR l_grp IN l_start+1 .. l_next-1 LOOP
1092
1093 IF (exp_ar_tbl(l_start).rec_account
1094 <> exp_ar_tbl(l_grp).rec_account) THEN
1095
1096 l_item_prcsed := l_next-1;
1097 e_count := e_count + l_next - l_start;
1098 l_start := l_next;
1099 l_next := l_next + 1;
1100 l_valid_rec_accs := FALSE;
1101 fnd_message.set_name ('PN', 'PN_UNMATCHING_REC_ACCNT');
1102 pnp_debug_pkg.put_log_msg(fnd_message.get);
1103 EXIT;
1104
1105 END IF;
1106
1107 END LOOP;
1108
1109 -- if REC account is valid, continue!
1110 IF l_valid_rec_accs THEN
1111
1112 l_context := 'Group Valid. Processing the grouped items';
1113 pnp_debug_pkg.log(l_context);
1114
1115 fnd_message.set_name ('PN','PN_EXPAR_ITM_QLFY');
1116 fnd_message.set_token ('NUM',(l_next-l_start));
1117 pnp_debug_pkg.put_log_msg(fnd_message.get);
1118
1119 l_rec_insert_flag := TRUE;
1120 l_last_updated_by := FND_GLOBAL.USER_ID;
1121 l_last_update_login := FND_GLOBAL.LOGIN_ID;
1122 l_last_update_date := sysdate;
1123
1124 /* if we reached here, we have a group worth inserting
1125 loop through the PL/SQL table and insert
1126 o one record per item in group into ra_interface_lines
1127 o distributions into ra_interface_distributions for REV
1128 and UNEARN based on pn_distributions for each line
1129 o ONLY one record in ra_interface_distributions for REC
1130 for ALL items
1131 */
1132
1133 FOR l_grp IN l_start .. l_next-1 LOOP
1134
1135 /* Default the precision to 2 */
1136 l_precision := 2;
1137
1138 /* Get the correct precision for the currency so that the amount can be rounded off */
1139 fnd_currency.get_info(exp_ar_tbl(l_grp).pn_export_currency_code,
1140 l_precision, l_ext_precision, l_min_acct_unit);
1141 pnp_debug_pkg.put_log_msg('
1142 ================================================================================');
1143 fnd_message.set_name ('PN','PN_EXPAR_PMT_PRM');
1144 fnd_message.set_token ('ITM_ID',exp_ar_tbl(l_grp).pn_payment_item_id);
1145 fnd_message.set_token ('CUST_ID',TO_CHAR(exp_ar_tbl(l_grp).pn_customer_id));
1146 fnd_message.set_token ('REC_AMT',0);
1147 fnd_message.set_token ('DATE',exp_ar_tbl(l_grp).gl_date);
1148 pnp_debug_pkg.put_log_msg('
1149 ================================================================================');
1150
1151
1152 /* Print the Conversion Rate and Type */
1153 fnd_message.set_name ('PN','PN_CRACC_CV_RATE');
1154 fnd_message.set_token ('CR',exp_ar_tbl(l_grp).conv_rate);
1155 pnp_debug_pkg.put_log_msg(fnd_message.get);
1156
1157 fnd_message.set_name ('PN','PN_CRACC_CV_TYPE');
1158 fnd_message.set_token ('CT',exp_ar_tbl(l_grp).conv_rate_type);
1159 pnp_debug_pkg.put_log_msg(fnd_message.get);
1160
1161 /* Print send entries flag for the lease */
1162 fnd_message.set_name ('PN','PN_EXPAR_PMT_LS');
1163 fnd_message.set_token ('ID',exp_ar_tbl(l_grp).pn_lease_id);
1164 fnd_message.set_token ('SEND',exp_ar_tbl(l_grp).send_entries);
1165 pnp_debug_pkg.put_log_msg(fnd_message.get);
1166
1167
1168 /* Initialize the variables */
1169 l_desc := NULL;
1170 l_inv_rule_name := NULL;
1171 l_inv_rule_type := NULL;
1172 l_inv_rule_freq := NULL;
1173 l_acc_rule_name := NULL;
1174 l_acc_rule_type := NULL;
1175 l_acc_rule_freq := NULL;
1176 l_pay_method_name := NULL;
1177 l_salesrep_number := NULL;
1178 l_sales_credit_id := NULL;
1179 l_cust_trx_name := NULL;
1180 l_term_name := NULL;
1181 l_location_code := NULL;
1182 l_gl_seg := NULL;
1183 l_sys_para := NULL;
1184 l_post_to_gl := NULL;
1185 l_tax_code := NULL;
1186
1187 /* get the description */
1188 OPEN get_desc(exp_ar_tbl(l_grp).PN_DESCRIPTION);
1189 FETCH get_desc into l_desc;
1190 CLOSE get_desc;
1191
1192 /* get the invoicing rule name */
1193 OPEN get_rule_name(exp_ar_tbl(l_grp).pn_inv_rule_id);
1194 FETCH get_rule_name into l_inv_rule_name, l_inv_rule_type, l_inv_rule_freq;
1195 CLOSE get_rule_name;
1196
1197 fnd_message.set_name ('PN','PN_EXPAR_INV_RULE');
1198 fnd_message.set_token ('NAME',l_inv_rule_name);
1199 pnp_debug_pkg.put_log_msg(fnd_message.get);
1200
1201 /* get the accounting rule name */
1202 OPEN get_rule_name(exp_ar_tbl(l_grp).pn_account_rule_id);
1203 FETCH get_rule_name into l_acc_rule_name,l_acc_rule_type, l_acc_rule_freq;
1204 CLOSE get_rule_name;
1205
1206 fnd_message.set_name ('PN','PN_EXPAR_ACC_RUL_NAME');
1207 fnd_message.set_token ('NAME',l_acc_rule_name);
1208 pnp_debug_pkg.put_log_msg(fnd_message.get);
1209
1210 fnd_message.set_name ('PN','PN_EXPAR_ACC_RUL_TYPE');
1211 fnd_message.set_token ('TYPE',l_acc_rule_type);
1212 pnp_debug_pkg.put_log_msg(fnd_message.get);
1213
1214 fnd_message.set_name ('PN','PN_EXPAR_GL_RUL_FREQ');
1215 fnd_message.set_token ('FREQ',l_acc_rule_freq);
1216 pnp_debug_pkg.put_log_msg(fnd_message.get);
1217
1218 IF exp_ar_tbl(l_grp).pn_account_rule_id IS NOT NULL AND
1219 (l_acc_rule_type <> 'A' OR
1220 l_acc_rule_freq <> 'SPECIFIC') AND
1221 NVL(l_derive_date_flag,'N') = 'Y' THEN
1222
1223 l_rule_start_date := exp_ar_tbl(l_grp).schedule_date;
1224 ELSE
1225 l_rule_start_date := NULL;
1226 END IF;
1227
1228 fnd_message.set_name ('PN','PN_EXPAR_RUL_ST_DT');
1229 fnd_message.set_token ('DATE',l_rule_start_date);
1230 pnp_debug_pkg.put_log_msg(fnd_message.get);
1231
1232 /* get the payment method name */
1233 OPEN get_receipt_name(exp_ar_tbl(l_grp).pn_pay_method_id);
1234 FETCH get_receipt_name into l_pay_method_name;
1235 CLOSE get_receipt_name;
1236
1237 /* get the payment method name */
1238 fnd_message.set_name ('PN','PN_EXPAR_PMT_MTHD');
1239 fnd_message.set_token ('METHOD',l_pay_method_name);
1240 pnp_debug_pkg.put_log_msg(fnd_message.get);
1241
1242 /* get the salesrep number */
1243 OPEN get_salesrep_number(exp_ar_tbl(l_grp).pn_salesrep_id,
1244 exp_ar_tbl(l_grp).pn_org_id);
1245 FETCH get_salesrep_number into l_salesrep_number,l_sales_credit_id;
1246 CLOSE get_salesrep_number;
1247
1248 fnd_message.set_name ('PN','PN_EXPAR_SALES_REP');
1249 fnd_message.set_token ('NAME',l_salesrep_number);
1250 pnp_debug_pkg.put_log_msg(fnd_message.get);
1251
1252 /* get the cust transaction type name */
1253 OPEN get_cust_trx_name(exp_ar_tbl(l_grp).pn_trx_type_id);
1254 FETCH get_cust_trx_name into l_cust_trx_name;
1255 CLOSE get_cust_trx_name;
1256
1257 fnd_message.set_name ('PN','PN_EXPAR_TRNX_TYPE');
1258 fnd_message.set_token ('TYPE',l_cust_trx_name);
1259 pnp_debug_pkg.put_log_msg(fnd_message.get);
1260
1261 /* Get Post To GL value for the transcation type */
1262 OPEN get_post_to_gl(exp_ar_tbl(l_grp).pn_trx_type_id,exp_ar_tbl(l_grp).pn_org_id);
1263 FETCH get_post_to_gl INTO l_post_to_gl;
1264 CLOSE get_post_to_gl;
1265
1266 fnd_message.set_name ('PN','PN_EXPAR_POST');
1267 fnd_message.set_token ('TOK',l_post_to_gl);
1268 pnp_debug_pkg.put_log_msg(fnd_message.get);
1269
1270 /* get the term name */
1271 OPEN get_term_name(exp_ar_tbl(l_grp).pn_term_id);
1272 FETCH get_term_name into l_term_name;
1273 CLOSE get_term_name;
1274
1275 fnd_message.set_name ('PN','PN_EXPAR_PMT_TERM');
1276 fnd_message.set_token ('NUM',l_term_name);
1277 pnp_debug_pkg.put_log_msg(fnd_message.get);
1278
1279 /* get the primary location code */
1280 OPEN get_loc_code(exp_ar_tbl(l_grp).location_id) ;
1281 FETCH get_loc_code into l_location_code;
1282 if get_loc_code%notfound then
1283 l_location_code:= null;
1284 end if;
1285 CLOSE get_loc_code;
1286
1287 fnd_message.set_name ('PN','PN_XPEAM_LOC');
1288 fnd_message.set_token ('LOC_CODE',l_location_code);
1289 pnp_debug_pkg.put_log_msg(fnd_message.get);
1290
1291
1292 /* get the vat tax code */
1293
1294 IF NOT pn_r12_util_pkg.is_r12 THEN
1295 OPEN get_tax_code(exp_ar_tbl(l_grp).pn_tax_code_id);
1296 FETCH get_tax_code into l_tax_code;
1297 CLOSE get_tax_code;
1298 ELSE
1299 l_tax_code := exp_ar_tbl(l_grp).pn_tax_classification_code;
1300 END IF;
1301
1302 /* check for salesrep in GL Segments */
1303 OPEN gl_segment_check;
1304 FETCH gl_segment_check INTO l_gl_seg;
1305 CLOSE gl_segment_check;
1306
1307 fnd_message.set_name ('PN','PN_EXPAR_GL_SALES');
1308 fnd_message.set_token ('TOK',l_gl_seg);
1309 pnp_debug_pkg.put_log_msg(fnd_message.get);
1310
1311 /* Check for System Parameters in AR System Options */
1312 OPEN sys_param_check;
1313 FETCH sys_param_check INTO l_sys_para;
1314 CLOSE sys_param_check;
1315
1316 fnd_message.set_name ('PN','PN_EXPAR_AR_SALES');
1317 fnd_message.set_token ('TOK',l_sys_para);
1318 pnp_debug_pkg.put_log_msg(fnd_message.get);
1319
1320
1321 l_context := 'Inserting into interface lines';
1322
1323 INSERT INTO ra_interface_lines_all
1324
1325 (amount_includes_tax_flag -- tax inclusive flag
1326 ,tax_code -- tax code
1327 ,legal_entity_id -- legal entity
1328 ,org_id -- org id
1329 ,gl_date -- gl date
1330 ,uom_code -- uom
1331 ,invoicing_rule_id -- invoicing rule id
1332 ,invoicing_rule_name -- invoicing rule name
1333 ,accounting_rule_id -- accounting rule id
1334 ,accounting_rule_name -- accounting rule name
1335 ,receipt_method_id -- payment method id
1336 ,receipt_method_name -- payment method name
1337 ,quantity -- quantity invoiced
1338 ,unit_selling_price -- unit selling price
1339 ,primary_salesrep_id -- primary sales person id
1340 ,primary_salesrep_number -- primary sales rep number
1341 ,purchase_order -- purchase order
1342 ,batch_source_name -- Batch source name
1343 ,set_of_books_id -- set of books id
1344 ,line_type -- line type
1345 ,description -- description
1346 ,currency_code -- currency code
1347 ,amount -- amount
1348 ,cust_trx_type_id -- transaction type id
1349 ,cust_trx_type_name -- transaction type name
1350 ,term_id -- payment term id
1351 ,term_name -- payment term name
1352 ,conversion_type
1353 ,conversion_rate
1354 ,conversion_date
1355 ,interface_line_context
1356 ,interface_line_attribute1
1357 ,interface_line_attribute2
1358 ,interface_line_attribute3
1359 ,interface_line_attribute4
1360 ,interface_line_attribute5
1361 ,interface_line_attribute6
1362 ,interface_line_attribute7
1363 ,interface_line_attribute8
1364 ,interface_line_attribute9
1365 ,interface_line_attribute10
1366 ,orig_system_bill_customer_id -- bill to customer id
1367 ,orig_system_bill_address_id -- bill to customer site address
1368 ,orig_system_ship_customer_id -- ship to customer id
1369 ,orig_system_ship_address_id -- ship to customer site address
1370 ,trx_date -- transaction date
1371 ,rule_start_date
1372 )
1373 VALUES
1374 (exp_ar_tbl(l_grp).pn_tax_included
1375 ,l_tax_code
1376 ,exp_ar_tbl(l_grp).pn_legal_entity_id
1377 ,exp_ar_tbl(l_grp).pn_org_id
1378 ,exp_ar_tbl(l_grp).gl_date
1379 ,'EA'
1380 ,exp_ar_tbl(l_grp).pn_inv_rule_id
1381 ,l_inv_rule_name
1382 ,exp_ar_tbl(l_grp).pn_account_rule_id
1383 ,l_acc_rule_name
1384 ,exp_ar_tbl(l_grp).pn_pay_method_id
1385 ,l_pay_method_name
1386 ,1
1387 ,round(exp_ar_tbl(l_grp).pn_export_currency_amount,l_precision)
1388 ,exp_ar_tbl(l_grp).pn_salesrep_id
1389 ,l_salesrep_number
1390 ,exp_ar_tbl(l_grp).pn_po_number
1391 ,l_batch_name
1392 ,pn_mo_cache_utils.get_profile_value('PN_SET_OF_BOOKS_ID',
1393 pn_mo_cache_utils.get_current_org_id)
1394 ,'LINE'
1395 ,l_desc
1396 ,exp_ar_tbl(l_grp).pn_export_currency_code
1397 ,round(exp_ar_tbl(l_grp).pn_export_currency_amount,l_precision)
1398 ,exp_ar_tbl(l_grp).pn_trx_type_id
1399 ,l_cust_trx_name
1400 ,exp_ar_tbl(l_grp).pn_term_id
1401 ,l_term_name
1402 ,exp_ar_tbl(l_grp).conv_rate_type
1403 ,exp_ar_tbl(l_grp).conv_rate
1404 ,exp_ar_tbl(l_grp).pn_accounted_date
1405 ,'Property-Projects'
1406 ,SUBSTRB(exp_ar_tbl(l_grp).pn_lease_num
1407 , 1
1408 , 150 - LENGTHB(' - ' ||to_char(exp_ar_tbl(l_grp).pn_payment_item_id)))
1409 || ' - ' ||to_char(exp_ar_tbl(l_grp).pn_payment_item_id)
1410 ,nvl(SUBSTRB(l_location_code,1,150),'N/A')
1411 ,nvl(exp_ar_tbl(l_grp).pn_proj_attr3,'N/A')
1412 ,nvl(exp_ar_tbl(l_grp).pn_proj_attr4,'N/A')
1413 ,nvl(exp_ar_tbl(l_grp).pn_proj_attr5,'N/A')
1414 ,nvl(exp_ar_tbl(l_grp).pn_proj_attr6,'N/A')
1415 ,nvl(exp_ar_tbl(l_grp).pn_proj_attr7,'N/A')
1416 ,nvl(SUBSTRB(exp_ar_tbl(l_grp).payment_purpose, 1,150),'N/A')
1417 ,nvl(SUBSTRB(exp_ar_tbl(l_grp).payment_type, 1, 150),'N/A')
1418 ,nvl(SUBSTRB(exp_ar_tbl(l_grp).pn_lease_num, 1, 150),'N/A')
1419 ,exp_ar_tbl(l_grp).pn_customer_id
1420 ,exp_ar_tbl(l_grp).pn_customer_site_use_id
1421 ,exp_ar_tbl(l_grp).pn_customer_id
1422 ,exp_ar_tbl(l_grp).pn_cust_ship_site_id
1423 ,exp_ar_tbl(l_grp).transaction_date
1424 ,l_rule_start_date
1425 );
1426
1427 /* Inserting data in RA_INTERFACE_SALESCREDITS */
1428 IF exp_ar_tbl(l_grp).pn_salesrep_id IS NOT NULL
1429 AND (l_gl_seg = 'Y'
1430 OR l_sys_para = 'Y'
1431 OR l_sal_cred = 'Y' ) THEN
1432
1433 INSERT INTO RA_INTERFACE_SALESCREDITS_ALL
1434 (
1435 salesrep_id
1436 ,salesrep_number
1437 ,sales_credit_type_id
1438 ,sales_credit_percent_split
1439 ,org_id
1440 ,interface_line_context
1441 ,interface_line_attribute1
1442 ,interface_line_attribute2
1443 ,interface_line_attribute3
1444 ,interface_line_attribute4
1445 ,interface_line_attribute5
1446 ,interface_line_attribute6
1447 ,interface_line_attribute7
1448 ,interface_line_attribute8
1449 ,interface_line_attribute9
1450 ,interface_line_attribute10
1451 ,created_by
1452 ,creation_date
1453 ,last_updated_by
1454 ,last_update_date
1455 ,last_update_login
1456 )
1457 VALUES
1458 (
1459 exp_ar_tbl(l_grp).pn_salesrep_id
1460 ,l_salesrep_number
1461 ,l_sales_credit_id
1462 ,100
1463 ,exp_ar_tbl(l_grp).pn_org_id
1464 ,'Property-Projects'
1465 ,SUBSTRB(exp_ar_tbl(l_grp).pn_lease_num
1466 , 1
1467 , 150 - LENGTHB(' - ' ||to_char(exp_ar_tbl(l_grp).pn_payment_item_id)))
1468 || ' - ' ||to_char(exp_ar_tbl(l_grp).pn_payment_item_id)
1469 ,nvl(SUBSTRB(l_location_code,1,150),'N/A')
1470 ,nvl(exp_ar_tbl(l_grp).pn_proj_attr3,'N/A')
1471 ,nvl(exp_ar_tbl(l_grp).pn_proj_attr4,'N/A')
1472 ,nvl(exp_ar_tbl(l_grp).pn_proj_attr5,'N/A')
1473 ,nvl(exp_ar_tbl(l_grp).pn_proj_attr6,'N/A')
1474 ,nvl(exp_ar_tbl(l_grp).pn_proj_attr7,'N/A')
1475 ,nvl(SUBSTRB(exp_ar_tbl(l_grp).payment_purpose,1,150),'N/A')
1476 ,nvl(SUBSTRB(exp_ar_tbl(l_grp).payment_type,1,150),'N/A')
1477 ,nvl(SUBSTRB(exp_ar_tbl(l_grp).pn_lease_num,1,150),'N/A')
1478 ,l_last_updated_by
1479 ,sysdate
1480 ,l_last_updated_by
1481 ,sysdate
1482 ,l_last_update_login
1483 );
1484
1485
1486 END IF;
1487
1488 /* Insert into Distributions for REC acount */
1489 /* This has to be done only once for the grouped items */
1490 IF l_rec_insert_flag THEN
1491
1492 l_context := 'Inserting into Distributions for REC acount for this group';
1493 pnp_debug_pkg.log(l_context ||' : '|| exp_ar_tbl(l_grp).rec_account);
1494
1495 INSERT INTO ra_interface_distributions_all
1496 (account_class
1497 ,percent
1498 ,amount
1499 ,code_combination_id
1500 ,created_by
1501 ,creation_date
1502 ,last_updated_by
1503 ,last_update_date
1504 ,last_update_login
1505 ,org_id
1506 ,interface_line_context
1507 ,interface_line_attribute1
1508 ,interface_line_attribute2
1509 ,interface_line_attribute3
1510 ,interface_line_attribute4
1511 ,interface_line_attribute5
1512 ,interface_line_attribute6
1513 ,interface_line_attribute7
1514 ,interface_line_attribute8
1515 ,interface_line_attribute9
1516 ,interface_line_attribute10
1517 )
1518 VALUES
1519 ('REC'
1520 ,100
1521 ,0
1522 ,exp_ar_tbl(l_grp).rec_account
1523 ,l_last_updated_by
1524 ,sysdate
1525 ,l_last_updated_by
1526 ,sysdate
1527 ,l_last_update_login
1528 ,exp_ar_tbl(l_grp).pn_org_id
1529 ,'Property-Projects'
1530 ,SUBSTRB(exp_ar_tbl(l_grp).pn_lease_num
1531 , 1
1532 , 150 - LENGTHB(' - ' ||to_char(exp_ar_tbl(l_grp).pn_payment_item_id)))
1533 || ' - ' ||to_char(exp_ar_tbl(l_grp).pn_payment_item_id)
1534 ,nvl(SUBSTRB(l_location_code,1,150),'N/A')
1535 ,nvl(exp_ar_tbl(l_grp).pn_proj_attr3,'N/A')
1536 ,nvl(exp_ar_tbl(l_grp).pn_proj_attr4,'N/A')
1537 ,nvl(exp_ar_tbl(l_grp).pn_proj_attr5,'N/A')
1538 ,nvl(exp_ar_tbl(l_grp).pn_proj_attr6,'N/A')
1539 ,nvl(exp_ar_tbl(l_grp).pn_proj_attr7,'N/A')
1540 ,nvl(SUBSTRB(exp_ar_tbl(l_grp).payment_purpose,1,150),'N/A')
1541 ,nvl(SUBSTRB(exp_ar_tbl(l_grp).payment_type,1,150),'N/A')
1542 ,nvl(SUBSTRB(exp_ar_tbl(l_grp).pn_lease_num,1,150),'N/A')
1543 );
1544 l_context := 'Inserted into Distributions for REC acount';
1545 pnp_debug_pkg.log(l_context);
1546 l_rec_insert_flag := FALSE;
1547
1548 END IF; -- if REC has not been inserted yet
1549
1550 l_context := 'Getting Revenue amt.';
1551 pnp_debug_pkg.log('Getting Revenue Amount');
1552
1553 fnd_message.set_name ('PN','PN_EXPAR_REV_AMT');
1554 fnd_message.set_token ('AMT',to_char(round(exp_ar_tbl(l_grp).pn_export_currency_amount,l_precision)));
1555 pnp_debug_pkg.put_log_msg(fnd_message.get);
1556
1557 l_total_rev_amt := 0;
1558 l_total_rev_percent := 0;
1559
1560 FOR acnt_rec IN get_dist(exp_ar_tbl(l_grp).pn_payment_term_id)
1561 LOOP
1562
1563 IF acnt_rec.account_class = 'REV' THEN
1564
1565 l_acnt_cls := 'REV';
1566
1567 IF (nvl(exp_ar_tbl(l_grp).normalize,'N') = 'N' and
1568 (exp_ar_tbl(l_grp).pn_inv_rule_id IS NOT NULL OR exp_ar_tbl(l_grp).pn_account_rule_id IS NOT NULL
1569 OR l_rev_acc_alloc_rule = 'Percent')) THEN
1570
1571 l_percent := acnt_rec.percentage;
1572 l_amt := null;
1573
1574 ELSE
1575
1576 l_percent := null;
1577 l_amt := round((exp_ar_tbl(l_grp).pn_export_currency_amount * acnt_rec.percentage)/100,l_precision);
1578 l_total_rev_amt := l_total_rev_amt + l_amt;
1579 l_total_rev_percent := l_total_rev_percent + acnt_rec.percentage;
1580
1581 IF l_total_rev_percent = 100 then
1582
1583 l_diff_amt := l_total_rev_amt - exp_ar_tbl(l_grp).pn_export_currency_amount;
1584 l_amt := l_amt - l_diff_amt;
1585
1586 END IF;
1587
1588 END IF;
1589
1590 fnd_message.set_name ('PN','PN_EXPAR_REV_AMT_DIST');
1591 fnd_message.set_token ('NUM',to_char(round(l_amt,l_precision)));
1592 pnp_debug_pkg.put_log_msg(fnd_message.get);
1593
1594 fnd_message.set_name ('PN','PN_EXPAR_REV_PCT_DIST');
1595 fnd_message.set_token ('PCT',to_char(round(l_percent,2)));
1596 pnp_debug_pkg.put_log_msg(fnd_message.get);
1597
1598 ELSIF acnt_rec.account_class = 'UNEARN' THEN
1599
1600 l_acnt_cls := 'REV';
1601 IF exp_ar_tbl(l_grp).pn_inv_rule_id IS NOT NULL OR exp_ar_tbl(l_grp).pn_account_rule_id IS NOT NULL
1602 OR l_rev_acc_alloc_rule = 'Percent' THEN
1603
1604 l_percent := acnt_rec.percentage;
1605 l_amt := null;
1606
1607 ELSIF nvl(exp_ar_tbl(l_grp).normalize,'N') = 'Y' then
1608 l_percent := 100;
1609 l_amt := round(exp_ar_tbl(l_grp).PN_EXPORT_CURRENCY_AMOUNT,l_precision);
1610
1611 ELSE
1612 l_percent := null;
1613 l_amt := round((exp_ar_tbl(l_grp).PN_EXPORT_CURRENCY_AMOUNT),l_precision);
1614
1615 END IF;
1616
1617 fnd_message.set_name ('PN','PN_EXPAR_ACR_AMT_DIST');
1618 fnd_message.set_token ('NUM',to_char(round(l_amt,l_precision)));
1619 pnp_debug_pkg.put_log_msg(fnd_message.get);
1620
1621 fnd_message.set_name ('PN','PN_EXPAR_ACR_PCT_DIST');
1622 fnd_message.set_token ('PCT',to_char(round(l_percent,2)));
1623 pnp_debug_pkg.put_log_msg(fnd_message.get);
1624
1625 END IF;
1626
1627 IF (acnt_rec.account_class = 'UNEARN' and
1628 NVL(exp_ar_tbl(l_grp).NORMALIZE,'N') = 'Y' and
1629 NVL(exp_ar_tbl(l_grp).send_entries,'Y') = 'Y') OR
1630 (acnt_rec.account_class = 'REV' AND
1631 (NVL(exp_ar_tbl(l_grp).NORMALIZE,'N') <> 'Y' OR
1632 (NVL(exp_ar_tbl(l_grp).NORMALIZE,'N') = 'Y'
1633 AND nvl(exp_ar_tbl(l_grp).send_entries,'Y') = 'N'))) THEN
1634
1635 pnp_debug_pkg.log('Inserting into distributions for account types of REV and UNEARN');
1636 l_context := 'Inserting into Distributions for account types of REV and UNEARN';
1637
1638 INSERT INTO ra_interface_distributions_all
1639 (account_class
1640 ,percent
1641 ,amount
1642 ,code_combination_id
1643 ,created_by
1644 ,creation_date
1645 ,last_updated_by
1646 ,last_update_date
1647 ,last_update_login
1648 ,org_id
1649 ,interface_line_context
1650 ,interface_line_attribute1
1651 ,interface_line_attribute2
1652 ,interface_line_attribute3
1653 ,interface_line_attribute4
1654 ,interface_line_attribute5
1655 ,interface_line_attribute6
1656 ,interface_line_attribute7
1657 ,interface_line_attribute8
1658 ,interface_line_attribute9
1659 ,interface_line_attribute10
1660 )
1661 VALUES
1662 (l_acnt_cls
1663 ,l_percent
1664 ,round(l_amt,l_precision)
1665 ,acnt_rec.account_id
1666 ,l_last_updated_by
1667 ,sysdate
1668 ,l_last_updated_by
1669 ,sysdate
1670 ,l_last_update_login
1671 ,exp_ar_tbl(l_grp).pn_org_id
1672 ,'Property-Projects'
1673 ,SUBSTRB(exp_ar_tbl(l_grp).pn_lease_num
1674 , 1
1675 , 150 - LENGTHB(' - ' ||to_char(exp_ar_tbl(l_grp).pn_payment_item_id)))
1676 || ' - ' ||to_char(exp_ar_tbl(l_grp).pn_payment_item_id)
1677 ,nvl(SUBSTRB(l_location_code,1,150),'N/A')
1678 ,nvl(exp_ar_tbl(l_grp).pn_proj_attr3,'N/A')
1679 ,nvl(exp_ar_tbl(l_grp).pn_proj_attr4,'N/A')
1680 ,nvl(exp_ar_tbl(l_grp).pn_proj_attr5,'N/A')
1681 ,nvl(exp_ar_tbl(l_grp).pn_proj_attr6,'N/A')
1682 ,nvl(exp_ar_tbl(l_grp).pn_proj_attr7,'N/A')
1683 ,nvl(SUBSTRB(exp_ar_tbl(l_grp).payment_purpose,1,150),'N/A')
1684 ,nvl(SUBSTRB(exp_ar_tbl(l_grp).payment_type,1,150),'N/A')
1685 ,nvl(SUBSTRB(exp_ar_tbl(l_grp).pn_lease_num,1,150),'N/A')
1686 );
1687
1688 pnp_debug_pkg.log('Inserted into distributions for account types of REV and UNEARN');
1689 END IF;
1690
1691 END LOOP;
1692
1693 l_context := 'Updating Payment Items';
1694 pnp_debug_pkg.log('Updating payment items for payment item id : ' ||
1695 to_char(exp_ar_tbl(l_grp).pn_payment_item_id) );
1696
1697 UPDATE PN_PAYMENT_ITEMS_ALL
1698 SET transferred_to_ar_flag = 'Y' ,
1699 ar_ref_code = exp_ar_tbl(l_grp).pn_payment_item_id,
1700 last_updated_by = l_last_updated_by,
1701 last_update_login = l_last_update_login,
1702 last_update_date = l_last_update_date ,
1703 export_group_id = p_groupId
1704 WHERE payment_item_id = exp_ar_tbl(l_grp).pn_payment_item_id;
1705
1706 IF (SQL%NOTFOUND) then
1707 pnp_debug_pkg.log('Could not update row for Payment_Item_Id = ' ||
1708 exp_ar_tbl(l_grp).Pn_Payment_Item_Id) ;
1709 fnd_message.set_name('PN', 'PN_TRANSFER_TO_AR_FLAG_NOT_SET');
1710 errbuf := fnd_message.get;
1711 rollback;
1712 retcode := 2;
1713 return;
1714 END IF;
1715
1716
1717 IF ( exp_ar_tbl(l_grp).PN_Payment_Schedule_Id <> l_Prior_Payment_Schedule_Id ) THEN
1718
1719 l_Prior_Payment_Schedule_Id := exp_ar_tbl(l_grp).PN_Payment_Schedule_Id;
1720 l_context := 'Updating Payment Schedules';
1721
1722 pnp_debug_pkg.log('Updating payment schedules for payment sch id : ' ||
1723 to_char(exp_ar_tbl(l_grp).PN_Payment_Schedule_Id) );
1724
1725
1726 UPDATE PN_PAYMENT_SCHEDULES_ALL
1727 SET Transferred_By_User_Id = l_last_updated_by,
1728 Transfer_Date = l_last_update_date
1729 WHERE Payment_Schedule_Id = exp_ar_tbl(l_grp).PN_Payment_Schedule_Id;
1730
1731 IF (SQL%NOTFOUND) then
1732 pnp_debug_pkg.log('Could not update row for Payment_Schedule_Id = '
1733 || exp_ar_tbl(l_grp).PN_Payment_Schedule_Id) ;
1734 fnd_message.set_name('PN', 'PN_TRANSFER_TO_AR_INFO_NOT_SET');
1735 errbuf := fnd_message.get;
1736 rollback;
1737 retcode := 2;
1738 return;
1739 END IF;
1740 END IF;
1741
1742 s_count := s_count + 1;
1743
1744 END LOOP; -- Finished inserting a Group
1745
1746 /* Set the l_start and l_next accordingly */
1747 l_item_prcsed := l_next - 1;
1748 l_start := l_next;
1749 l_next := l_next + 1;
1750
1751 END IF; -- proceed if REC accounts are valid
1752
1753 END IF; -- Group processed!!
1754
1755 END LOOP; -- End loop for main WHILE
1756
1757 exp_ar_tbl.DELETE;
1758 COMMIT;
1759
1760 pnp_debug_pkg.put_log_msg('
1761 ================================================================================');
1762
1763 fnd_message.set_name ('PN','PN_EXPAR_PROC_SUC');
1764 fnd_message.set_token ('NUM',S_Count);
1765 pnp_debug_pkg.put_log_msg(fnd_message.get);
1766
1767 fnd_message.set_name ('PN','PN_EXPAR_PROC_FAIL');
1768 fnd_message.set_token ('NUM',E_Count);
1769 pnp_debug_pkg.put_log_msg(fnd_message.get);
1770
1771 fnd_message.set_name ('PN','PN_EXPAR_PROC_TOT');
1772 fnd_message.set_token ('NUM',T_Count);
1773 pnp_debug_pkg.put_log_msg(fnd_message.get);
1774
1775 pnp_debug_pkg.put_log_msg('
1776 ================================================================================');
1777 IF(E_Count >0) THEN --Bug 14762515
1778
1779 fnd_message.set_name ('PN','PN_EXPAR_PROC_FAIL');
1780 fnd_message.set_token ('NUM',E_Count);
1781 pnp_debug_pkg.put_log_msg(fnd_message.get);
1782
1783 pnp_debug_pkg.put_log_msg('');
1784
1785 FOR i IN 1..l_index_2-1
1786 LOOP
1787 select actual_amount into l_actual_amount
1788 from pn_payment_items_all
1789 where payment_item_id=err_ar_tbl(i).pn_payment_item_id;
1790
1791 fnd_message.set_name ('PN','PN_LEASE_NUMBER');
1792 l_message := fnd_message.get || ' ' || err_ar_tbl(i).pn_lease_num || ' ';
1793 fnd_message.set_name ('PN','PN_AMOUNT');
1794 l_message := l_message || fnd_message.get;
1795 l_message := l_message || ' ' || l_actual_amount;
1796 pnp_debug_pkg.put_log_msg(l_message);
1797
1798
1799 END LOOP;
1800
1801 END IF;-- Bug 14762515
1802
1803 pnp_debug_pkg.put_log_msg('
1804 ================================================================================');
1805
1806
1807 EXCEPTION
1808
1809 WHEN NO_DATA_FOUND THEN
1810 pnp_debug_pkg.log('NO_DATA_FOUND: ' || l_context);
1811 raise;
1812
1813 WHEN OTHERS THEN
1814 pnp_debug_pkg.log(substrb('OTHERS: ' || l_context,1,244));
1815 fnd_message.set_name('PN', 'PN_TRANSFER_TO_AR_PROBLEM');
1816 Errbuf := substrb(SQLERRM,1,244);
1817 Retcode := 2;
1818 rollback;
1819 raise;
1820
1821 END EXP_TO_AR_GRP;
1822
1823 /*-----------------------------------------------------------------------------
1824 Description:
1825 Call this Procedure if the Default Grouping is specified
1826 at the Batch Source Name level.
1827 This means that we need to have the same old default
1828 behaviour when the grouping rule is default one
1829
1830 HISTORY:
1831 -- 03-DEC-03 atuppad o Created
1832 -- 20-AUG-04 kkhegde o Bug 3836127 - truncated location code to 30 characters
1833 before inserting into interface_line_attribute2
1834 -- 22-NOV-04 kkhegde o Bug 3751438 - fixed the validation for distributions
1835 -- 22-DEC-04 Kiran o Fix for 3751438 - corrected it for bug # 4083036
1836 -- 10-MAR-05 piagrawa o Bug #4231051 - Truncated the attribute values to 30
1837 -- characters before inserting into ra_interface_lines,
1838 -- ra_interface_salescredits and
1839 -- ra_interface_distributions tables
1840 -- 12-SEP-05 Parag o Bug #4284035 Modified insert statement to include org_id
1841 -- 11-OCT-05 pikhar o Bug 4652946 - Added trunc to pi.accounted_date in
1842 -- Q_Billitem
1843 -- 28-OCT-05 sdmahesh o ATG mandated changes for SQL literals
1844 -- 28-NOV-05 sdmahesh o Passed org_id to GET_START_DATE,check_conversion_type
1845 -- 24-MAR-06 Hareesha o Bug 5116270 Modified get_salesrep_number to pass
1846 -- org_id as parameter.
1847 -- 07-AUG-06 Hareesha o Bug #5405883 Inserted schedule_date as rule_start_date
1848 -- into ra_interface_lines_all instead of rule_gl_date.
1849 -- 29-Nov-12 ppenumar o Bug #14762515 Used the plsql table 'err_ar_tble' to
1850 -- display the details of the failed billing items in the log.
1851 -----------------------------------------------------------------------------*/
1852
1853 Procedure EXP_TO_AR_NO_GRP (
1854 errbuf IN OUT NOCOPY VARCHAR2
1855 ,retcode IN OUT NOCOPY VARCHAR2
1856 ,p_groupId VARCHAR2
1857 ,p_lease_num_low VARCHAR2
1858 ,p_lease_num_high VARCHAR2
1859 ,p_sch_dt_low VARCHAR2
1860 ,p_sch_dt_high VARCHAR2
1861 ,p_due_dt_low VARCHAR2
1862 ,p_due_dt_high VARCHAR2
1863 ,p_pay_prps_code VARCHAR2
1864 ,p_prd_name VARCHAR2
1865 ,p_amt_low NUMBER
1866 ,p_amt_high NUMBER
1867 ,p_customer_id NUMBER
1868 ,p_grp_param VARCHAR2
1869 )
1870 IS
1871 v_pn_lease_num PN_LEASES.lease_num%TYPE;
1872 v_pn_lease_id PN_LEASES.lease_id%TYPE;
1873 v_pn_period_name PN_PAYMENT_SCHEDULES.period_name%TYPE;
1874 v_pn_code_combination_id PN_PAYMENT_TERMS.code_combination_id%TYPE;
1875 v_pn_cust_ship_site_id PN_PAYMENT_TERMS.cust_ship_site_id%TYPE;
1876 v_pn_tax_code_id PN_PAYMENT_TERMS.tax_code_id%TYPE;
1877 v_pn_tcc PN_PAYMENT_TERMS.tax_classification_code%TYPE;
1878 v_pn_le PN_PAYMENT_TERMS.legal_entity_id%TYPE;
1879 v_pn_inv_rule_id PN_PAYMENT_TERMS.inv_rule_id%TYPE;
1880 v_pn_account_rule_id PN_PAYMENT_TERMS.account_rule_id%TYPE;
1881 v_pn_term_id PN_PAYMENT_TERMS.ap_ar_term_id%TYPE;
1882 v_pn_trx_type_id PN_PAYMENT_TERMS.cust_trx_type_id%TYPE;
1883 v_pn_pay_method_id PN_PAYMENT_TERMS.receipt_method_id%TYPE;
1884 v_pn_po_number PN_PAYMENT_TERMS.cust_po_number%TYPE;
1885 v_pn_tax_included PN_PAYMENT_TERMS.tax_included%TYPE;
1886 v_pn_salesrep_id PN_PAYMENT_TERMS.salesrep_id%TYPE;
1887 v_pn_proj_attr_catg PN_PAYMENT_TERMS.project_attribute_category%TYPE;
1888 v_pn_proj_attr1 PN_PAYMENT_TERMS.project_attribute1%TYPE;
1889 v_pn_proj_attr2 PN_PAYMENT_TERMS.project_attribute2%TYPE;
1890 v_pn_proj_attr3 PN_PAYMENT_TERMS.project_attribute3%TYPE;
1891 v_pn_proj_attr4 PN_PAYMENT_TERMS.project_attribute4%TYPE;
1892 v_pn_proj_attr5 PN_PAYMENT_TERMS.project_attribute5%TYPE;
1893 v_pn_proj_attr6 PN_PAYMENT_TERMS.project_attribute6%TYPE;
1894 v_pn_proj_attr7 PN_PAYMENT_TERMS.project_attribute7%TYPE;
1895 v_pn_org_id PN_PAYMENT_TERMS.org_id%TYPE;
1896 v_pn_description PN_PAYMENT_TERMS.payment_purpose_code%TYPE;
1897 v_transaction_date PN_PAYMENT_ITEMS.due_date%TYPE;
1898 v_normalize PN_PAYMENT_TERMS.normalize%TYPE;
1899 v_pn_payment_item_id PN_PAYMENT_ITEMS.payment_item_id%TYPE;
1900 v_pn_payment_term_id PN_PAYMENT_ITEMS.payment_term_id%TYPE;
1901 v_pn_export_currency_code PN_PAYMENT_ITEMS.export_currency_code%TYPE;
1902 v_pn_export_currency_amount PN_PAYMENT_ITEMS.export_currency_amount%TYPE;
1903 v_pn_customer_id PN_PAYMENT_ITEMS.customer_id%TYPE ;
1904 v_pn_customer_site_use_id PN_PAYMENT_ITEMS.customer_site_use_id%TYPE;
1905 v_pn_payment_schedule_id PN_PAYMENT_ITEMS.payment_schedule_id%TYPE;
1906 v_pn_accounted_date PN_PAYMENT_ITEMS.accounted_date%TYPE;
1907 v_pn_rate PN_PAYMENT_ITEMS.rate%TYPE;
1908 l_acnt_cls PN_DISTRIBUTIONS.account_class%TYPE;
1909 l_percent PN_DISTRIBUTIONS.percentage%TYPE;
1910 l_location_code PN_LOCATIONS.location_code%TYPE;
1911 l_inv_rule_name RA_RULES.name%TYPE;
1912 l_inv_rule_type RA_RULES.type%TYPE;
1913 l_inv_rule_freq RA_RULES.frequency%TYPE;
1914 l_acc_rule_name RA_RULES.name%TYPE;
1915 l_acc_rule_type RA_RULES.type%TYPE;
1916 l_acc_rule_freq RA_RULES.frequency%TYPE;
1917 l_desc RA_INTERFACE_LINES.description%TYPE;
1918 l_salesrep_number RA_SALESREPS.salesrep_number%TYPE;
1919 l_sales_credit_id RA_SALESREPS.sales_credit_type_id%TYPE;
1920 l_cust_trx_name RA_CUST_TRX_TYPES.name%TYPE;
1921 l_term_name RA_TERMS.name%TYPE;
1922 l_pay_method_name AR_RECEIPT_METHODS.name%TYPE;
1923 l_amt NUMBER;
1924 l_prior_payment_schedule_id NUMBER := -999;
1925 l_last_updated_by NUMBER := FND_GLOBAL.USER_ID;
1926 l_last_update_login NUMBER := FND_GLOBAL.LOGIN_ID;
1927 l_last_update_date DATE := sysdate;
1928 l_start_date RA_CUST_TRX_LINE_GL_DIST.gl_date%TYPE ;
1929 l_context VARCHAR2(2000);
1930 l_batch_name RA_BATCH_SOURCES.name%TYPE;
1931 l_precision NUMBER;
1932 l_ext_precision NUMBER;
1933 l_min_acct_unit NUMBER;
1934 t_count NUMBER := 0;
1935 e_count NUMBER := 0;
1936 s_count NUMBER := 0;
1937 l_tax_code AR_VAT_TAX.tax_code%TYPE;
1938 l_rev_acc_alloc_rule RA_BATCH_SOURCES.rev_acc_allocation_rule%TYPE;
1939 l_rev_flag VARCHAR2(1);
1940 l_rec_flag VARCHAR2(1);
1941 l_ast_flag VARCHAR2(1);
1942 l_rec_cnt NUMBER;
1943 l_prof_optn VARCHAR2(30);
1944 l_err_msg1 VARCHAR2(2000);
1945 l_err_msg2 VARCHAR2(2000);
1946 l_err_msg3 VARCHAR2(2000);
1947 l_err_msg4 VARCHAR2(2000);
1948 l_sys_para VARCHAR2(1);
1949 l_gl_seg VARCHAR2(1);
1950 l_sal_cred VARCHAR2(1);
1951 l_total_rev_amt NUMBER := 0;
1952 l_total_rev_percent NUMBER := 0;
1953 l_diff_amt NUMBER := 0;
1954 l_set_of_books_id NUMBER := to_number(pn_mo_cache_utils.get_profile_value('PN_SET_OF_BOOKS_ID',
1955 pn_mo_cache_utils.get_current_org_id));
1956 l_func_curr_code GL_SETS_OF_BOOKS.currency_code%TYPE;
1957 l_conv_rate_type PN_CURRENCIES.conversion_type%TYPE;
1958 l_conv_rate PN_PAYMENT_ITEMS.rate%TYPE;
1959 v_location_id PN_LOCATIONS.location_id%TYPE;
1960 l_send_flag PN_LEASE_DETAILS_ALL.send_entries%TYPE := 'Y';
1961 l_lease_id NUMBER := 0;
1962 v_pur_code PN_PAYMENT_TERMS.payment_purpose_code%TYPE;
1963 v_pur_type_code PN_PAYMENT_TERMS.payment_term_type_code%TYPE;
1964 l_post_to_gl RA_CUST_TRX_TYPES_ALL.post_to_gl%TYPE;
1965 l_derive_date_flag RA_BATCH_SOURCES.derive_date_flag%TYPE;
1966 l_rule_start_date RA_INTERFACE_LINES.rule_start_date%TYPE := NULL;
1967 l_rows_nogrp INTEGER;
1968 l_count_nogrp INTEGER;
1969 v_pn_payment_term_id1 PN_PAYMENT_ITEMS_ALL.payment_term_id%TYPE;
1970 v_pn_le_id1 PN_PAYMENT_TERMS_ALL.legal_entity_id%TYPE;
1971 v_pn_customer_id1 PN_PAYMENT_ITEMS_ALL.customer_id%TYPE;
1972 v_pn_trx_type_id1 PN_PAYMENT_TERMS.cust_trx_type_id%TYPE;
1973 v_pn_org_id1 PN_PAYMENT_TERMS_ALL.org_id%TYPE;
1974 v_schedule_date PN_PAYMENT_SCHEDULES_ALL.schedule_date%TYPE;
1975
1976 l_rows_select_nogrp NUMBER;
1977 l_count_select_nogrp NUMBER;
1978 TYPE le_ar_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1979 TYPE term_ar_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1980 le_ar_tbl le_ar_tbl_type;
1981 term_ar_tbl term_ar_tbl_type;
1982 l_index NUMBER ;
1983 l_index_2 NUMBER :=1; -- Bug 14762515
1984 err_ar_tbl err_ar_tbl_type;-- Bug 14762515
1985 l_actual_amount NUMBER; -- Bug 14762515
1986 l_message varchar2(100); -- Bug 14762515
1987
1988
1989 BEGIN
1990
1991 fnd_message.set_name ('PN','PN_EXPAR_PMT_MSG');
1992 pnp_debug_pkg.put_log_msg(fnd_message.get);
1993
1994 l_context := 'Forming the query statement';
1995 pnp_debug_pkg.log('Forming the query statement');
1996
1997
1998 l_context := 'Getting the Batch Source Name';
1999 pnp_debug_pkg.log('Getting the Batch Source Name');
2000
2001 /* get the batch source name */
2002 OPEN get_batch_source_name;
2003 FETCH get_batch_source_name into l_batch_name, l_rev_acc_alloc_rule,
2004 l_sal_cred, l_derive_date_flag;
2005 CLOSE get_batch_source_name;
2006
2007 fnd_message.set_name ('PN','PN_EXPAR_BTCH_SRC_NAME');
2008 fnd_message.set_token ('NAME',l_batch_name);
2009 pnp_debug_pkg.put_log_msg(fnd_message.get);
2010
2011 Q_select_nogrp := 'SELECT payment_term_id,
2012 customer_id,
2013 cust_trx_type_id,
2014 org_id
2015 FROM ('||Q_Billitem_nogrp||')
2016 WHERE legal_entity_id IS NULL';
2017 g_cursor_select_nogrp := dbms_sql.open_cursor;
2018 dbms_sql.parse(g_cursor_select_nogrp,Q_select_nogrp,dbms_sql.native);
2019 do_binding (g_cursor_select_nogrp
2020 ,p_lease_num_low
2021 ,p_lease_num_high
2022 ,p_sch_dt_low
2023 ,p_sch_dt_high
2024 ,p_due_dt_low
2025 ,p_due_dt_high
2026 ,p_pay_prps_code
2027 ,p_prd_name
2028 ,p_amt_low
2029 ,p_amt_high
2030 ,p_customer_id
2031 ,p_grp_param
2032 );
2033
2034
2035 l_index := 1;
2036 le_ar_tbl.delete;
2037 term_ar_tbl.delete;
2038 err_ar_tbl.DELETE; --Bug 14762515
2039 dbms_sql.define_column (g_cursor_select_nogrp,1,v_pn_payment_term_id1);
2040 dbms_sql.define_column (g_cursor_select_nogrp,2,v_pn_customer_id1);
2041 dbms_sql.define_column (g_cursor_select_nogrp,3,v_pn_trx_type_id1);
2042 dbms_sql.define_column (g_cursor_select_nogrp,4,v_pn_org_id1);
2043 l_rows_select_nogrp := dbms_sql.execute(g_cursor_select_nogrp);
2044
2045 LOOP
2046 BEGIN
2047 l_count_select_nogrp := dbms_sql.fetch_rows(g_cursor_select_nogrp);
2048 EXIT WHEN l_count_select_nogrp <> 1;
2049 dbms_sql.column_value (g_cursor_select_nogrp,1,term_ar_tbl(l_index));
2050 dbms_sql.column_value (g_cursor_select_nogrp,2,v_pn_customer_id1);
2051 dbms_sql.column_value (g_cursor_select_nogrp,3,v_pn_trx_type_id1);
2052 dbms_sql.column_value (g_cursor_select_nogrp,4,v_pn_org_id1);
2053 le_ar_tbl(l_index) := pn_r12_util_pkg.get_le_for_ar(v_pn_customer_id1,
2054 v_pn_trx_type_id1,
2055 v_pn_org_id1);
2056 l_index := l_index+1;
2057 END;
2058 END LOOP;
2059
2060 FORALL i IN term_ar_tbl.FIRST..term_ar_tbl.LAST
2061 UPDATE pn_payment_terms_all
2062 SET legal_entity_id = le_ar_tbl(i)
2063 WHERE payment_term_id = term_ar_tbl(i);
2064
2065 IF dbms_sql.is_open (g_cursor_select_nogrp) THEN
2066 dbms_sql.close_cursor (g_cursor_select_nogrp);
2067 END IF;
2068
2069 g_cursor_nogrp := dbms_sql.open_cursor;
2070 dbms_sql.parse(g_cursor_nogrp, Q_Billitem_nogrp, dbms_sql.native);
2071 do_binding (g_cursor_nogrp
2072 ,p_lease_num_low
2073 ,p_lease_num_high
2074 ,p_sch_dt_low
2075 ,p_sch_dt_high
2076 ,p_due_dt_low
2077 ,p_due_dt_high
2078 ,p_pay_prps_code
2079 ,p_prd_name
2080 ,p_amt_low
2081 ,p_amt_high
2082 ,p_customer_id
2083 ,p_grp_param
2084 );
2085 l_context := 'Opening the cursor';
2086
2087 /*OPEN c_billitem for q_billitem ;*/
2088 dbms_sql.define_column (g_cursor_nogrp, 1, v_pn_payment_item_id);
2089 dbms_sql.define_column (g_cursor_nogrp, 2, v_pn_payment_term_id);
2090 dbms_sql.define_column (g_cursor_nogrp, 3, v_pn_export_currency_code,15);
2091 dbms_sql.define_column (g_cursor_nogrp, 4, v_pn_export_currency_amount);
2092 dbms_sql.define_column (g_cursor_nogrp, 5, v_pn_customer_id);
2093 dbms_sql.define_column (g_cursor_nogrp, 6, v_pn_customer_site_use_id);
2094 dbms_sql.define_column (g_cursor_nogrp, 7, v_pn_cust_ship_site_id);
2095 dbms_sql.define_column (g_cursor_nogrp, 8, v_pn_tax_code_id);
2096 dbms_sql.define_column (g_cursor_nogrp, 9, v_pn_tcc,30);
2097 dbms_sql.define_column (g_cursor_nogrp, 10,v_pn_le);
2098 dbms_sql.define_column (g_cursor_nogrp, 11,v_pn_inv_rule_id);
2099 dbms_sql.define_column (g_cursor_nogrp, 12,v_pn_account_rule_id);
2100 dbms_sql.define_column (g_cursor_nogrp, 13,v_pn_term_id);
2101 dbms_sql.define_column (g_cursor_nogrp, 14,v_pn_trx_type_id);
2102 dbms_sql.define_column (g_cursor_nogrp, 15,v_pn_pay_method_id);
2103 dbms_sql.define_column (g_cursor_nogrp, 16,v_pn_po_number,50);
2104 dbms_sql.define_column (g_cursor_nogrp, 17,v_pn_tax_included,1);
2105 dbms_sql.define_column (g_cursor_nogrp, 18,v_pn_salesrep_id);
2106 dbms_sql.define_column (g_cursor_nogrp, 19,v_pn_proj_attr_catg,30);
2107 dbms_sql.define_column (g_cursor_nogrp, 20,v_pn_proj_attr3,150);
2108 dbms_sql.define_column (g_cursor_nogrp, 21,v_pn_proj_attr4,150);
2109 dbms_sql.define_column (g_cursor_nogrp, 22,v_pn_proj_attr5,150);
2110 dbms_sql.define_column (g_cursor_nogrp, 23,v_pn_proj_attr6,150);
2111 dbms_sql.define_column (g_cursor_nogrp, 24,v_pn_proj_attr7,150);
2112 dbms_sql.define_column (g_cursor_nogrp, 25,v_pn_org_id);
2113 dbms_sql.define_column (g_cursor_nogrp, 26,v_pn_lease_num,30);
2114 dbms_sql.define_column (g_cursor_nogrp, 27,v_pn_payment_schedule_id);
2115 dbms_sql.define_column (g_cursor_nogrp, 28,v_pn_period_name,15);
2116 dbms_sql.define_column (g_cursor_nogrp, 29,v_pn_description,30);
2117 dbms_sql.define_column (g_cursor_nogrp, 30,v_pn_lease_id);
2118 dbms_sql.define_column (g_cursor_nogrp, 31,v_transaction_date);
2119 dbms_sql.define_column (g_cursor_nogrp, 32,v_normalize,1);
2120 dbms_sql.define_column (g_cursor_nogrp, 33,v_pn_accounted_date);
2121 dbms_sql.define_column (g_cursor_nogrp, 34,v_pn_rate);
2122 dbms_sql.define_column (g_cursor_nogrp, 35,v_location_id);
2123 dbms_sql.define_column (g_cursor_nogrp, 36,v_pur_code,30);
2124 dbms_sql.define_column (g_cursor_nogrp, 37,v_pur_type_code,30);
2125 dbms_sql.define_column (g_cursor_nogrp, 38, v_schedule_date);
2126
2127
2128 l_rows_nogrp := dbms_sql.execute(g_cursor_nogrp);
2129
2130 LOOP /* looping for c_billitem */
2131
2132 BEGIN
2133
2134 l_context := 'Fetching from the cursor';
2135
2136 l_count_nogrp := dbms_sql.fetch_rows(g_cursor_nogrp);
2137 EXIT WHEN l_count_nogrp <> 1;
2138 dbms_sql.column_value (g_cursor_nogrp, 1, v_pn_payment_item_id);
2139 dbms_sql.column_value (g_cursor_nogrp, 2, v_pn_payment_term_id);
2140 dbms_sql.column_value (g_cursor_nogrp, 3, v_pn_export_currency_code);
2141 dbms_sql.column_value (g_cursor_nogrp, 4, v_pn_export_currency_amount);
2142 dbms_sql.column_value (g_cursor_nogrp, 5, v_pn_customer_id);
2143 dbms_sql.column_value (g_cursor_nogrp, 6, v_pn_customer_site_use_id);
2144 dbms_sql.column_value (g_cursor_nogrp, 7, v_pn_cust_ship_site_id);
2145 dbms_sql.column_value (g_cursor_nogrp, 8, v_pn_tax_code_id);
2146 dbms_sql.column_value (g_cursor_nogrp, 9, v_pn_tcc);
2147 dbms_sql.column_value (g_cursor_nogrp, 10,v_pn_le);
2148 dbms_sql.column_value (g_cursor_nogrp, 11,v_pn_inv_rule_id);
2149 dbms_sql.column_value (g_cursor_nogrp, 12,v_pn_account_rule_id);
2150 dbms_sql.column_value (g_cursor_nogrp, 13,v_pn_term_id);
2151 dbms_sql.column_value (g_cursor_nogrp, 14,v_pn_trx_type_id);
2152 dbms_sql.column_value (g_cursor_nogrp, 15,v_pn_pay_method_id);
2153 dbms_sql.column_value (g_cursor_nogrp, 16,v_pn_po_number);
2154 dbms_sql.column_value (g_cursor_nogrp, 17,v_pn_tax_included);
2155 dbms_sql.column_value (g_cursor_nogrp, 18,v_pn_salesrep_id);
2156 dbms_sql.column_value (g_cursor_nogrp, 19,v_pn_proj_attr_catg);
2157 dbms_sql.column_value (g_cursor_nogrp, 20,v_pn_proj_attr3);
2158 dbms_sql.column_value (g_cursor_nogrp, 21,v_pn_proj_attr4);
2159 dbms_sql.column_value (g_cursor_nogrp, 22,v_pn_proj_attr5);
2160 dbms_sql.column_value (g_cursor_nogrp, 23,v_pn_proj_attr6);
2161 dbms_sql.column_value (g_cursor_nogrp, 24,v_pn_proj_attr7);
2162 dbms_sql.column_value (g_cursor_nogrp, 25,v_pn_org_id);
2163 dbms_sql.column_value (g_cursor_nogrp, 26,v_pn_lease_num);
2164 dbms_sql.column_value (g_cursor_nogrp, 27,v_pn_payment_schedule_id);
2165 dbms_sql.column_value (g_cursor_nogrp, 28,v_pn_period_name);
2166 dbms_sql.column_value (g_cursor_nogrp, 29,v_pn_description);
2167 dbms_sql.column_value (g_cursor_nogrp, 30,v_pn_lease_id);
2168 dbms_sql.column_value (g_cursor_nogrp, 31,v_transaction_date);
2169 dbms_sql.column_value (g_cursor_nogrp, 32,v_normalize);
2170 dbms_sql.column_value (g_cursor_nogrp, 33,v_pn_accounted_date);
2171 dbms_sql.column_value (g_cursor_nogrp, 34,v_pn_rate);
2172 dbms_sql.column_value (g_cursor_nogrp, 35,v_location_id);
2173 dbms_sql.column_value (g_cursor_nogrp, 36,v_pur_code);
2174 dbms_sql.column_value (g_cursor_nogrp, 37,v_pur_type_code);
2175 dbms_sql.column_value (g_cursor_nogrp, 38,v_schedule_date);
2176
2177 /* Check for Conversion Type and Conversion Rate for Currency Code */
2178 OPEN get_func_curr_code(l_set_of_books_id);
2179 FETCH get_func_curr_code INTO l_func_curr_code;
2180 CLOSE get_func_curr_code;
2181
2182 IF UPPER(l_func_curr_code) = UPPER(v_pn_export_currency_code) THEN
2183 l_conv_rate := 1;
2184 l_conv_rate_type := 'User';
2185
2186 ELSE
2187 l_conv_rate_type := PNP_UTIL_FUNC.check_conversion_type(l_func_curr_code,
2188 pn_mo_cache_utils.get_current_org_id);
2189 IF UPPER(l_conv_rate_type) = 'USER' THEN
2190 l_conv_rate := v_pn_rate;
2191 ELSE
2192 l_conv_rate := NULL;
2193 END IF;
2194 END IF;
2195
2196 fnd_message.set_name ('PN','PN_CRACC_CV_RATE');
2197 fnd_message.set_token ('CR',l_conv_rate);
2198 pnp_debug_pkg.put_log_msg(fnd_message.get);
2199
2200 fnd_message.set_name ('PN','PN_CRACC_CV_TYPE');
2201 fnd_message.set_token ('CT',l_conv_rate_type);
2202 pnp_debug_pkg.put_log_msg(fnd_message.get);
2203
2204
2205 /* Get send entries flag for the lease */
2206 IF l_lease_id <> v_pn_lease_id THEN
2207 OPEN get_send_flag(v_pn_lease_id);
2208 FETCH get_send_flag INTO l_send_flag;
2209 CLOSE get_send_flag;
2210 l_lease_id := v_pn_lease_id;
2211 fnd_message.set_name ('PN','PN_EXPAR_PMT_LS');
2212 fnd_message.set_token ('ID',l_lease_id);
2213 fnd_message.set_token ('SEND',l_send_flag);
2214 pnp_debug_pkg.put_log_msg(fnd_message.get);
2215
2216 END IF;
2217
2218 l_rev_flag := 'N';
2219 l_rec_flag := 'N';
2220 l_ast_flag := 'N';
2221 l_rec_cnt := 0;
2222 l_total_rev_amt := 0;
2223 l_total_rev_percent := 0;
2224 l_prof_optn := pn_mo_cache_utils.get_profile_value('PN_ACCOUNTING_OPTION',
2225 pn_mo_cache_utils.get_current_org_id);
2226
2227 FOR dist_rec IN acnt_cls_cur(v_pn_payment_term_id)
2228 LOOP
2229
2230 IF dist_rec.account_class IN ('REV') THEN
2231 l_rev_flag := 'Y';
2232 ELSIF dist_rec.account_class IN ('REC') THEN
2233 l_rec_flag := 'Y';
2234 ELSIF dist_rec.account_class IN ('UNEARN') THEN
2235 l_ast_flag := 'Y';
2236 END IF;
2237
2238 l_rec_cnt := l_rec_cnt + 1;
2239
2240 END LOOP;
2241
2242 t_count := t_count + 1;
2243
2244 IF UPPER(l_conv_rate_type) = 'USER' AND
2245 l_conv_rate IS NULL THEN
2246
2247 fnd_message.set_name ('PN', 'PN_CONV_RATE_REQD');
2248 l_err_msg4 := fnd_message.get;
2249 pnp_debug_pkg.put_log_msg(l_err_msg4);
2250
2251 RAISE GENERIC_EXPORT_EXCEPTION;
2252
2253 END IF;
2254
2255 IF v_pn_term_id IS NULL OR v_pn_trx_type_id IS NULL THEN
2256
2257 fnd_message.set_name ('PN', 'PN_PTRM_TRX_REQD_MSG');
2258 l_err_msg3 := fnd_message.get;
2259 pnp_debug_pkg.put_log_msg(l_err_msg3);
2260
2261 RAISE GENERIC_EXPORT_EXCEPTION;
2262
2263 END IF;
2264
2265 IF NVL(v_normalize,'N') = 'Y' THEN
2266
2267 IF (l_rev_flag <> 'Y' OR l_rec_flag <> 'Y' OR l_ast_flag <> 'Y') THEN
2268
2269 fnd_message.set_name ('PN', 'PN_ALL_ACNT_DIST_MSG');
2270 l_err_msg1 := fnd_message.get;
2271 pnp_debug_pkg.put_log_msg(l_err_msg1);
2272
2273 RAISE GENERIC_EXPORT_EXCEPTION;
2274 END IF;
2275
2276 ELSIF NVL(v_normalize,'N') = 'N' THEN
2277
2278 IF (l_prof_optn = 'Y' AND (l_rev_flag <> 'Y' OR l_rec_flag <> 'Y')) OR
2279 (l_prof_optn IN ('M', 'N') AND ((l_rev_flag = 'Y' AND l_rec_flag <> 'Y') OR
2280 (l_rev_flag <> 'Y' AND l_rec_flag = 'Y')))
2281 THEN
2282
2283 fnd_message.set_name ('PN', 'PN_REVREC_DIST_MSG');
2284 l_err_msg2 := fnd_message.get;
2285 pnp_debug_pkg.put_log_msg(l_err_msg2);
2286
2287 RAISE GENERIC_EXPORT_EXCEPTION;
2288
2289 END IF;
2290
2291 END IF;
2292
2293 /* Default the precision to 2 */
2294 l_precision := 2;
2295
2296 /* Get the correct precision for the currency so that the amount can be rounded off */
2297 fnd_currency.get_info(v_pn_export_currency_code, l_precision, l_ext_precision, l_min_acct_unit);
2298
2299 /* if post to Gl is N, then do not populate gl_date in interface table */
2300 OPEN get_post_to_gl(v_pn_trx_type_id,v_pn_org_id);
2301 FETCH get_post_to_gl INTO l_post_to_gl;
2302 CLOSE get_post_to_gl;
2303
2304 IF v_pn_inv_rule_id IS NOT NULL OR v_pn_account_rule_id IS NOT NULL
2305 OR NVL(l_post_to_gl,'Y') = 'N' THEN
2306
2307 l_start_date := null;
2308 ELSE
2309
2310 l_start_date := PNP_UTIL_FUNC.Get_Start_Date(v_pn_period_name,
2311 pn_mo_cache_utils.get_current_org_id);
2312 END IF;
2313
2314 pnp_debug_pkg.put_log_msg('
2315 ================================================================================');
2316 fnd_message.set_name ('PN','PN_EXPAR_PMT_PRM');
2317 fnd_message.set_token ('ITM_ID',v_pn_payment_item_id);
2318 fnd_message.set_token ('CUST_ID',TO_CHAR(v_pn_customer_id));
2319 fnd_message.set_token ('REC_AMT',TO_CHAR(ROUND(v_pn_export_currency_amount,l_precision)));
2320 fnd_message.set_token ('DATE',l_start_date);
2321 pnp_debug_pkg.put_log_msg('
2322 ================================================================================');
2323
2324 /* Initialize the variables */
2325 l_desc := NULL;
2326 l_inv_rule_name := NULL;
2327 l_inv_rule_type := NULL;
2328 l_inv_rule_freq := NULL;
2329 l_acc_rule_name := NULL;
2330 l_acc_rule_type := NULL;
2331 l_acc_rule_freq := NULL;
2332 l_pay_method_name := NULL;
2333 l_salesrep_number := NULL;
2334 l_sales_credit_id := NULL;
2335 l_cust_trx_name := NULL;
2336 l_term_name := NULL;
2337 l_location_code := NULL;
2338 l_gl_seg := NULL;
2339 l_sys_para := NULL;
2340 l_tax_code := NULL;
2341
2342 /* get the description */
2343 OPEN get_desc(V_PN_DESCRIPTION);
2344 FETCH get_desc into l_desc;
2345 CLOSE get_desc;
2346
2347 /* get the invoicing rule name */
2348 OPEN get_rule_name(v_pn_inv_rule_id);
2349 FETCH get_rule_name into l_inv_rule_name, l_inv_rule_type, l_inv_rule_freq;
2350 CLOSE get_rule_name;
2351
2352 fnd_message.set_name ('PN','PN_EXPAR_INV_RULE');
2353 fnd_message.set_token ('NAME',l_inv_rule_name);
2354 pnp_debug_pkg.put_log_msg(fnd_message.get);
2355
2356 /* get the accounting rule name */
2357 OPEN get_rule_name(v_pn_account_rule_id);
2358 FETCH get_rule_name into l_acc_rule_name, l_acc_rule_type, l_acc_rule_freq;
2359 CLOSE get_rule_name;
2360
2361 fnd_message.set_name ('PN','PN_EXPAR_ACC_RUL_NAME');
2362 fnd_message.set_token ('NAME',l_acc_rule_name);
2363 pnp_debug_pkg.put_log_msg(fnd_message.get);
2364
2365 fnd_message.set_name ('PN','PN_EXPAR_ACC_RUL_TYPE');
2366 fnd_message.set_token ('TYPE',l_acc_rule_type);
2367 pnp_debug_pkg.put_log_msg(fnd_message.get);
2368
2369 fnd_message.set_name ('PN','PN_EXPAR_GL_RUL_FREQ');
2370 fnd_message.set_token ('FREQ',l_acc_rule_freq);
2371 pnp_debug_pkg.put_log_msg(fnd_message.get);
2372
2373 IF v_pn_account_rule_id IS NOT NULL AND
2374 (l_acc_rule_type <> 'A' OR
2375 l_acc_rule_freq <> 'SPECIFIC') AND
2376 NVL(l_derive_date_flag,'N') = 'Y' THEN
2377
2378 l_rule_start_date := v_schedule_date;
2379 ELSE
2380 l_rule_start_date := NULL;
2381 END IF;
2382
2383 fnd_message.set_name ('PN','PN_EXPAR_RUL_ST_DT');
2384 fnd_message.set_token ('DATE',l_rule_start_date);
2385 pnp_debug_pkg.put_log_msg(fnd_message.get);
2386
2387 /* get the payment method name */
2388 OPEN get_receipt_name(v_pn_pay_method_id);
2389 FETCH get_receipt_name into l_pay_method_name;
2390 CLOSE get_receipt_name;
2391
2392 fnd_message.set_name ('PN','PN_EXPAR_PMT_MTHD');
2393 fnd_message.set_token ('METHOD',l_pay_method_name);
2394 pnp_debug_pkg.put_log_msg(fnd_message.get);
2395
2396 /* get the salesrep number */
2397 OPEN get_salesrep_number(v_pn_salesrep_id,v_pn_org_id);
2398 FETCH get_salesrep_number into l_salesrep_number,l_sales_credit_id;
2399 CLOSE get_salesrep_number;
2400
2401 fnd_message.set_name ('PN','PN_EXPAR_SALES_REP');
2402 fnd_message.set_token ('NAME',l_salesrep_number);
2403 pnp_debug_pkg.put_log_msg(fnd_message.get);
2404
2405 /* get the cust transaction type name */
2406 OPEN get_cust_trx_name(v_pn_trx_type_id);
2407 FETCH get_cust_trx_name into l_cust_trx_name;
2408 CLOSE get_cust_trx_name;
2409
2410 fnd_message.set_name ('PN','PN_EXPAR_TRNX_TYPE');
2411 fnd_message.set_token ('TYPE',l_cust_trx_name);
2412 pnp_debug_pkg.put_log_msg(fnd_message.get);
2413
2414 fnd_message.set_name ('PN','PN_EXPAR_POST');
2415 fnd_message.set_token ('TOK',l_post_to_gl);
2416 pnp_debug_pkg.put_log_msg(fnd_message.get);
2417
2418 /* get the term name */
2419 OPEN get_term_name(v_pn_term_id);
2420 FETCH get_term_name into l_term_name;
2421 CLOSE get_term_name;
2422
2423 fnd_message.set_name ('PN','PN_EXPAR_PMT_TERM');
2424 fnd_message.set_token ('NUM',l_term_name);
2425 pnp_debug_pkg.put_log_msg(fnd_message.get);
2426
2427 /* get the primary location code */
2428 OPEN get_loc_code(v_location_id) ;
2429 FETCH get_loc_code into l_location_code;
2430 IF get_loc_code%NOTFOUND THEN
2431 l_location_code:= NULL;
2432 END IF;
2433 CLOSE get_loc_code;
2434
2435 fnd_message.set_name ('PN','PN_EXPAR_LOC_CODE');
2436 fnd_message.set_token ('LOC_CODE',l_location_code);
2437 pnp_debug_pkg.put_log_msg(fnd_message.get);
2438
2439 /* get the vat tax code */
2440
2441 IF NOT pn_r12_util_pkg.is_r12 THEN
2442 OPEN get_tax_code(v_pn_tax_code_id);
2443 FETCH get_tax_code into l_tax_code;
2444 CLOSE get_tax_code;
2445 ELSE
2446 l_tax_code := v_pn_tcc;
2447 END IF;
2448
2449 /* check for salesrep in GL Segments */
2450 OPEN gl_segment_check;
2451 FETCH gl_segment_check INTO l_gl_seg;
2452 CLOSE gl_segment_check;
2453
2454 fnd_message.set_name ('PN','PN_EXPAR_GL_SALES');
2455 fnd_message.set_token ('TOK',l_gl_seg);
2456 pnp_debug_pkg.put_log_msg(fnd_message.get);
2457
2458 /* Check for System Parameters in AR System Options */
2459 OPEN sys_param_check;
2460 FETCH sys_param_check INTO l_sys_para;
2461 CLOSE sys_param_check;
2462
2463 fnd_message.set_name ('PN','PN_EXPAR_AR_SALES');
2464 fnd_message.set_token ('TOK',l_sys_para);
2465 pnp_debug_pkg.put_log_msg(fnd_message.get);
2466
2467 l_context := 'Inserting into interface lines';
2468
2469 INSERT INTO ra_interface_lines_all
2470 ( amount_includes_tax_flag -- tax inclusive flag
2471 ,tax_code -- tax code
2472 ,legal_entity_id -- legal entity id
2473 ,org_id -- org id
2474 ,gl_date -- gl date
2475 ,uom_code -- uom
2476 ,invoicing_rule_id -- invoicing rule id
2477 ,invoicing_rule_name -- invoicing rule name
2478 ,accounting_rule_id -- accounting rule id
2479 ,accounting_rule_name -- accounting rule name
2480 ,receipt_method_id -- payment method id
2481 ,receipt_method_name -- payment method name
2482 ,quantity -- quantity invoiced
2483 ,unit_selling_price -- unit selling price
2484 ,primary_salesrep_id -- primary sales person id
2485 ,primary_salesrep_number -- primary sales rep number
2486 ,purchase_order -- purchase order
2487 ,batch_source_name -- Batch source name
2488 ,set_of_books_id -- set of books id
2489 ,line_type -- line type
2490 ,description -- description
2491 ,currency_code -- currency code
2492 ,amount -- amount
2493 ,cust_trx_type_id -- transaction type id
2494 ,cust_trx_type_name -- transaction type name
2495 ,term_id -- payment term id
2496 ,term_name -- payment term name
2497 ,conversion_type
2498 ,conversion_rate
2499 ,conversion_date
2500 ,interface_line_context
2501 ,interface_line_attribute1
2502 ,interface_line_attribute2
2503 ,interface_line_attribute3
2504 ,interface_line_attribute4
2505 ,interface_line_attribute5
2506 ,interface_line_attribute6
2507 ,interface_line_attribute7
2508 ,interface_line_attribute8
2509 ,interface_line_attribute9
2510 ,interface_line_attribute10
2511 ,orig_system_bill_customer_id -- bill to customer id
2512 ,orig_system_bill_address_id -- bill to customer site address
2513 ,orig_system_ship_customer_id -- ship to customer id
2514 ,orig_system_ship_address_id -- ship to customer site address
2515 ,trx_date -- transaction date
2516 ,rule_start_date
2517 )
2518 VALUES
2519 ( v_pn_tax_included
2520 ,l_tax_code
2521 ,v_pn_le
2522 ,v_pn_org_id
2523 ,l_start_date
2524 ,'EA'
2525 ,v_pn_inv_rule_id
2526 ,l_inv_rule_name
2527 ,v_pn_account_rule_id
2528 ,l_acc_rule_name
2529 ,v_pn_pay_method_id
2530 ,l_pay_method_name
2531 ,1
2532 ,round(v_pn_export_currency_amount,l_precision)
2533 ,v_pn_salesrep_id
2534 ,l_salesrep_number
2535 ,v_pn_po_number
2536 ,l_batch_name
2537 ,pn_mo_cache_utils.get_profile_value('PN_SET_OF_BOOKS_ID',
2538 pn_mo_cache_utils.get_current_org_id)
2539 ,'LINE'
2540 ,l_desc
2541 ,v_pn_export_currency_code
2542 ,round(v_pn_export_currency_amount,l_precision)
2543 ,v_pn_trx_type_id
2544 , l_cust_trx_name
2545 ,v_pn_term_id
2546 ,l_term_name
2547 ,l_conv_rate_type
2548 ,l_conv_rate
2549 ,v_pn_accounted_date
2550 ,'Property-Projects'
2551 ,SUBSTRB(v_pn_lease_num
2552 , 1
2553 , 150 - LENGTHB( ' - ' ||to_char(v_pn_payment_item_id)))
2554 || ' - ' ||to_char(v_pn_payment_item_id)
2555 ,nvl(SUBSTRB(l_location_code,1,150),'N/A')
2556 ,nvl(v_pn_proj_attr3,'N/A')
2557 ,nvl(v_pn_proj_attr4,'N/A')
2558 ,nvl(v_pn_proj_attr5,'N/A')
2559 ,nvl(v_pn_proj_attr6,'N/A')
2560 ,nvl(v_pn_proj_attr7,'N/A')
2561 ,nvl(SUBSTRB(v_pur_code,1,150),'N/A')
2562 ,nvl(SUBSTRB(v_pur_type_code,1,150),'N/A')
2563 ,nvl(SUBSTRB(v_pn_lease_num,1,150),'N/A')
2564 ,v_pn_customer_id
2565 ,v_pn_customer_site_use_id
2566 ,v_pn_customer_id
2567 ,v_pn_cust_ship_site_id
2568 ,v_transaction_date
2569 ,l_rule_start_date
2570 );
2571
2572 /* Inserting data in RA_INTERFACE_SALESCREDITS */
2573
2574 IF v_pn_salesrep_id IS NOT NULL
2575 AND (l_gl_seg = 'Y'
2576 OR l_sys_para = 'Y'
2577 OR l_sal_cred = 'Y' ) THEN
2578
2579 INSERT INTO RA_INTERFACE_SALESCREDITS_ALL
2580 (
2581 salesrep_id
2582 ,salesrep_number
2583 ,sales_credit_type_id
2584 ,sales_credit_percent_split
2585 ,interface_line_context
2586 ,interface_line_attribute1
2587 ,interface_line_attribute2
2588 ,interface_line_attribute3
2589 ,interface_line_attribute4
2590 ,interface_line_attribute5
2591 ,interface_line_attribute6
2592 ,interface_line_attribute7
2593 ,interface_line_attribute8
2594 ,interface_line_attribute9
2595 ,interface_line_attribute10
2596 ,created_by
2597 ,creation_date
2598 ,last_updated_by
2599 ,last_update_date
2600 ,last_update_login
2601 ,org_id
2602 )
2603 VALUES
2604 (
2605 v_pn_salesrep_id
2606 ,l_salesrep_number
2607 ,l_sales_credit_id
2608 ,100
2609 ,'Property-Projects'
2610 , SUBSTRB(v_pn_lease_num
2611 , 1
2612 , 150 - LENGTHB(' - ' ||to_char(v_pn_payment_item_id)))
2613 || ' - ' ||to_char(v_pn_payment_item_id)
2614 ,NVL(SUBSTRB(l_location_code,1,150),'N/A')
2615 ,nvl(v_pn_proj_attr3,'N/A')
2616 ,nvl(v_pn_proj_attr4,'N/A')
2617 ,nvl(v_pn_proj_attr5,'N/A')
2618 ,nvl(v_pn_proj_attr6,'N/A')
2619 ,nvl(v_pn_proj_attr7,'N/A')
2620 ,NVL(SUBSTRB(v_pur_code,1,150),'N/A')
2621 ,NVL(SUBSTRB(v_pur_type_code,1,150),'N/A')
2622 ,NVL(SUBSTRB(v_pn_lease_num,1,150),'N/A')
2623 ,l_last_updated_by
2624 ,sysdate
2625 ,l_last_updated_by
2626 ,sysdate
2627 ,l_last_update_login
2628 ,v_pn_org_id
2629 );
2630
2631 END IF;
2632
2633 l_context := 'Getting Revenue amt.';
2634 pnp_debug_pkg.log('Getting Revenue Amount');
2635
2636 fnd_message.set_name ('PN','PN_EXPAR_REV_AMT');
2637 fnd_message.set_token ('AMT',to_char(round(v_pn_export_currency_amount,l_precision)));
2638 pnp_debug_pkg.put_log_msg(fnd_message.get);
2639
2640
2641 FOR acnt_rec IN get_dist(v_pn_payment_term_id)
2642 LOOP
2643
2644 IF acnt_rec.account_class = 'REC' THEN
2645
2646 l_acnt_cls := 'REC';
2647 l_percent := 100;
2648 l_amt := V_PN_EXPORT_CURRENCY_AMOUNT;
2649
2650 fnd_message.set_name ('PN','PN_EXPAR_BTCH_RCV_AMT');
2651 fnd_message.set_token ('NUM',to_char(l_amt));
2652 pnp_debug_pkg.put_log_msg(fnd_message.get);
2653
2654 ELSIF acnt_rec.account_class = 'REV' THEN
2655
2656 l_acnt_cls := 'REV';
2657
2658 IF (nvl(v_normalize,'N') = 'N' and
2659 (v_pn_inv_rule_id IS NOT NULL OR v_pn_account_rule_id IS NOT NULL
2660 OR l_rev_acc_alloc_rule = 'Percent')) THEN
2661
2662 l_percent := acnt_rec.percentage;
2663 l_amt := null;
2664
2665 ELSE
2666
2667 l_percent := null;
2668
2669 l_amt := round((v_pn_export_currency_amount * acnt_rec.percentage)/100,l_precision);
2670
2671
2672 l_total_rev_amt := l_total_rev_amt + l_amt;
2673 l_total_rev_percent := l_total_rev_percent + acnt_rec.percentage;
2674
2675 if l_total_rev_percent = 100 then
2676
2677 l_diff_amt := l_total_rev_amt - v_pn_export_currency_amount;
2678 l_amt := l_amt - l_diff_amt;
2679
2680 end if;
2681
2682 END IF;
2683
2684 fnd_message.set_name ('PN','PN_EXPAR_REV_AMT_DIST');
2685 fnd_message.set_token ('NUM',to_char(round(l_amt,l_precision)));
2686 pnp_debug_pkg.put_log_msg(fnd_message.get);
2687
2688 fnd_message.set_name ('PN','PN_EXPAR_REV_PCT_DIST');
2689 fnd_message.set_token ('PCT',to_char(round(l_percent,2)));
2690 pnp_debug_pkg.put_log_msg(fnd_message.get);
2691
2692 ELSIF acnt_rec.account_class = 'UNEARN' THEN
2693
2694 l_acnt_cls := 'REV';
2695
2696 IF v_pn_inv_rule_id IS NOT NULL OR v_pn_account_rule_id IS NOT NULL
2697 OR l_rev_acc_alloc_rule = 'Percent' THEN
2698
2699 l_percent := acnt_rec.percentage;
2700 l_amt := null;
2701
2702 ELSIF nvl(v_normalize,'N') = 'Y' then
2703 l_percent := 100;
2704 l_amt := round(V_PN_EXPORT_CURRENCY_AMOUNT,l_precision);
2705
2706
2707 ELSE
2708
2709 l_percent := null;
2710 l_amt := round((V_PN_EXPORT_CURRENCY_AMOUNT),l_precision);
2711
2712 END IF;
2713
2714 fnd_message.set_name ('PN','PN_EXPAR_ACR_AMT_DIST');
2715 fnd_message.set_token ('NUM',to_char(round(l_amt,l_precision)));
2716 pnp_debug_pkg.put_log_msg(fnd_message.get);
2717
2718 fnd_message.set_name ('PN','PN_EXPAR_ACR_PCT_DIST');
2719 fnd_message.set_token ('PCT',to_char(round(l_percent,2)));
2720 pnp_debug_pkg.put_log_msg(fnd_message.get);
2721
2722 END IF;
2723
2724 l_last_updated_by := FND_GLOBAL.USER_ID;
2725 l_last_update_login := FND_GLOBAL.LOGIN_ID;
2726 l_last_update_date := sysdate;
2727
2728 pnp_debug_pkg.log('Inserting into distributions');
2729 l_context := 'Inserting into Distributions';
2730
2731 IF (acnt_rec.account_class = 'UNEARN' AND
2732 NVL(V_NORMALIZE,'N') = 'Y' AND
2733 NVL(l_send_flag,'Y') = 'Y') OR
2734 (acnt_rec.account_class = 'REC') OR
2735 (acnt_rec.account_class = 'REV' AND
2736 (NVL(V_NORMALIZE,'N') <> 'Y' OR
2737 (NVL(V_NORMALIZE,'N') = 'Y' AND NVL(l_send_flag,'Y') = 'N'))) THEN
2738
2739
2740 INSERT INTO ra_interface_distributions_all
2741 ( account_class
2742 ,percent
2743 ,amount
2744 ,code_combination_id
2745 ,created_by
2746 ,creation_date
2747 ,last_updated_by
2748 ,last_update_date
2749 ,last_update_login
2750 ,org_id
2751 ,interface_line_context
2752 ,interface_line_attribute1
2753 ,interface_line_attribute2
2754 ,interface_line_attribute3
2755 ,interface_line_attribute4
2756 ,interface_line_attribute5
2757 ,interface_line_attribute6
2758 ,interface_line_attribute7
2759 ,interface_line_attribute8
2760 ,interface_line_attribute9
2761 ,interface_line_attribute10
2762 )
2763 VALUES
2764 ( l_acnt_cls
2765 ,l_percent
2766 ,ROUND(l_amt,l_precision)
2767 ,acnt_rec.account_id
2768 ,l_last_updated_by
2769 ,SYSDATE
2770 ,l_last_updated_by
2771 ,SYSDATE
2772 ,l_last_update_login
2773 ,v_pn_org_id
2774 ,'Property-Projects'
2775 , SUBSTRB(v_pn_lease_num
2776 , 1
2777 , 150 - LENGTHB(' - ' ||to_char(v_pn_payment_item_id)))
2778 || ' - ' ||to_char(v_pn_payment_item_id)
2779 ,NVL(SUBSTRB(l_location_code,1,150),'N/A')
2780 ,nvl(v_pn_proj_attr3,'N/A')
2781 ,nvl(v_pn_proj_attr4,'N/A')
2782 ,nvl(v_pn_proj_attr5,'N/A')
2783 ,nvl(v_pn_proj_attr6,'N/A')
2784 ,nvl(v_pn_proj_attr7,'N/A')
2785 ,NVL(SUBSTRB(v_pur_code,1,150),'N/A')
2786 ,NVL(SUBSTRB(v_pur_type_code,1,150),'N/A')
2787 ,NVL(SUBSTRB(v_pn_lease_num,1,150),'N/A')
2788 );
2789
2790 END IF;
2791 pnp_debug_pkg.log('Inserted into distributions');
2792
2793 END LOOP;
2794
2795 l_context := 'Updating Payment Items';
2796 pnp_debug_pkg.log('Updating payment items for payment item id : ' ||
2797 to_char(v_pn_payment_item_id) );
2798
2799 UPDATE PN_PAYMENT_ITEMS_ALL
2800 SET transferred_to_ar_flag = 'Y' ,
2801 ar_ref_code = v_pn_payment_item_id,
2802 last_updated_by = l_last_updated_by,
2803 last_update_login = l_last_update_login,
2804 last_update_date = l_last_update_date ,
2805 export_group_id = p_groupId
2806 WHERE payment_item_id = v_pn_payment_item_id;
2807
2808 IF (SQL%NOTFOUND) then
2809 pnp_debug_pkg.log('Could not update row for Payment_Item_Id = ' ||
2810 V_PN_Payment_Item_Id) ;
2811 fnd_message.set_name('PN', 'PN_TRANSFER_TO_AR_FLAG_NOT_SET');
2812 errbuf := fnd_message.get;
2813 rollback;
2814 retcode := 2;
2815 return;
2816 END IF;
2817
2818
2819 IF ( V_PN_Payment_Schedule_Id <> l_Prior_Payment_Schedule_Id ) THEN
2820
2821 l_Prior_Payment_Schedule_Id := V_PN_Payment_Schedule_Id;
2822
2823 l_context := 'Updating Payment Schedules';
2824
2825 pnp_debug_pkg.log('Updating payment schedules for payment sch id : ' ||
2826 to_char(V_PN_Payment_Schedule_Id) );
2827
2828 UPDATE PN_PAYMENT_SCHEDULES_ALL
2829 SET Transferred_By_User_Id = l_last_updated_by,
2830 Transfer_Date = l_last_update_date
2831 WHERE Payment_Schedule_Id = V_PN_Payment_Schedule_Id;
2832
2833 IF (SQL%NOTFOUND) then
2834 pnp_debug_pkg.log('Could not update row for Payment_Schedule_Id = '
2835 || V_PN_Payment_Schedule_Id) ;
2836 fnd_message.set_name('PN', 'PN_TRANSFER_TO_AR_INFO_NOT_SET');
2837 errbuf := fnd_message.get;
2838 rollback;
2839 retcode := 2;
2840 return;
2841 END IF;
2842 END IF;
2843
2844 s_count := s_count + 1;
2845
2846 EXCEPTION
2847
2848 WHEN GENERIC_EXPORT_EXCEPTION THEN
2849 e_count := e_count + 1;
2850 err_ar_tbl(l_index_2).pn_payment_item_id :=v_pn_payment_item_id;--Bug 14762515
2851 err_ar_tbl(l_index_2).pn_payment_term_id :=v_pn_payment_term_id;--Bug 14762515
2852 err_ar_tbl(l_index_2).pn_lease_num :=v_pn_lease_num; --Bug 14762515
2853 err_ar_tbl(l_index_2).location_id :=v_location_id; --Bug 14762515
2854 l_index_2 := l_index_2+1;--Bug 14762515
2855 WHEN OTHERS THEN
2856 RAISE;
2857
2858 END;
2859
2860 END LOOP; /* looping for c_billitem */
2861
2862 IF dbms_sql.is_open (g_cursor_nogrp) THEN
2863 dbms_sql.close_cursor (g_cursor_nogrp);
2864 END IF;
2865
2866
2867 COMMIT;
2868
2869 /*CLOSE c_billitem;*/
2870
2871 pnp_debug_pkg.put_log_msg('
2872 ================================================================================');
2873
2874
2875 fnd_message.set_name ('PN','PN_EXPAR_PROC_SUC');
2876 fnd_message.set_token ('NUM',S_Count);
2877 pnp_debug_pkg.put_log_msg(fnd_message.get);
2878
2879 fnd_message.set_name ('PN','PN_EXPAR_PROC_FAIL');
2880 fnd_message.set_token ('NUM',E_Count);
2881 pnp_debug_pkg.put_log_msg(fnd_message.get);
2882
2883 fnd_message.set_name ('PN','PN_EXPAR_PROC_TOT');
2884 fnd_message.set_token ('NUM',T_Count);
2885 pnp_debug_pkg.put_log_msg(fnd_message.get);
2886
2887 pnp_debug_pkg.put_log_msg('
2888 ================================================================================');
2889
2890 IF(E_Count >0) THEN --Bug 14762515
2891
2892 fnd_message.set_name ('PN','PN_EXPAR_PROC_FAIL');
2893 fnd_message.set_token ('NUM',E_Count);
2894 pnp_debug_pkg.put_log_msg(fnd_message.get);
2895 pnp_debug_pkg.put_log_msg('');
2896
2897 FOR i IN 1..l_index_2-1
2898 LOOP
2899 select actual_amount into l_actual_amount
2900 from pn_payment_items_all
2901 where payment_item_id=err_ar_tbl(i).pn_payment_item_id;
2902
2903 fnd_message.set_name ('PN','PN_LEASE_NUMBER');
2904 l_message := fnd_message.get || ' ' || err_ar_tbl(i).pn_lease_num || ' ';
2905 fnd_message.set_name ('PN','PN_AMOUNT');
2906 l_message := l_message || fnd_message.get;
2907 l_message := l_message || ' ' || l_actual_amount;
2908 pnp_debug_pkg.put_log_msg(l_message);
2909
2910
2911 END LOOP;
2912
2913 END IF;-- Bug 14762515
2914
2915 pnp_debug_pkg.put_log_msg('
2916 ================================================================================');
2917
2918
2919 EXCEPTION
2920
2921 WHEN NO_DATA_FOUND THEN
2922 pnp_debug_pkg.log('NO_DATA_FOUND: ' || l_context);
2923 raise;
2924
2925 WHEN OTHERS THEN
2926 pnp_debug_pkg.log(substrb('OTHERS: ' || l_context,1,244));
2927 fnd_message.set_name('PN', 'PN_TRANSFER_TO_AR_PROBLEM');
2928 Errbuf := substrb(SQLERRM,1,244);
2929 Retcode := 2;
2930 rollback;
2931 raise;
2932
2933 END EXP_TO_AR_NO_GRP;
2934
2935 PROCEDURE do_binding (p_cursor NUMBER
2936 ,p_lease_num_low VARCHAR2
2937 ,p_lease_num_high VARCHAR2
2938 ,p_sch_dt_low VARCHAR2
2939 ,p_sch_dt_high VARCHAR2
2940 ,p_due_dt_low VARCHAR2
2941 ,p_due_dt_high VARCHAR2
2942 ,p_pay_prps_code VARCHAR2
2943 ,p_prd_name VARCHAR2
2944 ,p_amt_low NUMBER
2945 ,p_amt_high NUMBER
2946 ,p_customer_id NUMBER
2947 ,p_grp_param VARCHAR2
2948 ) IS
2949 BEGIN
2950 PNP_DEBUG_PKG.log('pn_exp_to_ar.do_binding (+)');
2951 IF p_grp_param IS NULL THEN
2952 IF p_lease_num_low IS NOT NULL AND
2953 p_lease_num_high IS NOT NULL THEN
2954 dbms_sql.bind_variable(p_cursor,'l_lease_num_low',p_lease_num_low);
2955 dbms_sql.bind_variable(p_cursor,'l_lease_num_high',p_lease_num_high);
2956 ELSIF p_lease_num_low IS NULL AND
2957 p_lease_num_high IS NOT NULL THEN
2958 dbms_sql.bind_variable(p_cursor,'l_lease_num_high',p_lease_num_high);
2959 ELSIF p_lease_num_low IS NOT NULL AND
2960 p_lease_num_high IS NULL THEN
2961 dbms_sql.bind_variable(p_cursor,'l_lease_num_low',p_lease_num_low);
2962 END IF;
2963 IF p_sch_dt_low IS NOT NULL AND
2964 p_sch_dt_high IS NOT NULL THEN
2965 dbms_sql.bind_variable(p_cursor,'l_sch_dt_low',fnd_date.canonical_to_date(p_sch_dt_low));
2966 dbms_sql.bind_variable(p_cursor,'l_sch_dt_high',fnd_date.canonical_to_date(p_sch_dt_high));
2967 ELSIF p_sch_dt_low IS NULL AND
2968 p_sch_dt_high IS NOT NULL THEN
2969 dbms_sql.bind_variable(p_cursor,'l_sch_dt_high',fnd_date.canonical_to_date(p_sch_dt_high));
2970 ELSIF p_sch_dt_low IS NOT NULL AND
2971 p_sch_dt_high IS NULL THEN
2972 dbms_sql.bind_variable(p_cursor,'l_sch_dt_low',fnd_date.canonical_to_date(p_sch_dt_low));
2973 END IF;
2974 IF p_due_dt_low IS NOT NULL AND
2975 p_due_dt_high IS NOT NULL THEN
2976 dbms_sql.bind_variable(p_cursor,'l_due_dt_low',fnd_date.canonical_to_date(p_due_dt_low));
2977 dbms_sql.bind_variable(p_cursor,'l_due_dt_high',fnd_date.canonical_to_date(p_due_dt_high));
2978 ELSIF p_due_dt_low IS NULL AND
2979 p_due_dt_high IS NOT NULL THEN
2980 dbms_sql.bind_variable(p_cursor,'l_due_dt_high',fnd_date.canonical_to_date(p_due_dt_high));
2981 ELSIF p_due_dt_low IS NOT NULL AND
2982 p_due_dt_high IS NULL THEN
2983 dbms_sql.bind_variable(p_cursor,'l_due_dt_low',fnd_date.canonical_to_date(p_due_dt_low));
2984 END IF;
2985 IF p_pay_prps_code IS NOT NULL THEN
2986 dbms_sql.bind_variable(p_cursor,'l_pay_prps_code',p_pay_prps_code);
2987 END IF;
2988 IF p_prd_name IS NOT NULL THEN
2989 dbms_sql.bind_variable(p_cursor,'l_prd_name',p_prd_name);
2990 END IF;
2991 IF p_amt_low IS NOT NULL AND
2992 p_amt_high IS NOT NULL THEN
2993 dbms_sql.bind_variable(p_cursor,'l_amt_low',p_amt_low);
2994 dbms_sql.bind_variable(p_cursor,'l_amt_high',p_amt_high);
2995 ELSIF p_amt_low IS NULL AND
2996 p_amt_high IS NOT NULL THEN
2997 dbms_sql.bind_variable(p_cursor,'l_amt_high',p_amt_high);
2998 ELSIF p_amt_low IS NOT NULL AND
2999 p_amt_high IS NULL THEN
3000 dbms_sql.bind_variable(p_cursor,'l_amt_low',p_amt_low);
3001 END IF;
3002 IF p_customer_id IS NOT NULL THEN
3003 dbms_sql.bind_variable(p_cursor,'l_customer_id',p_customer_id);
3004 END IF;
3005 ELSE
3006 dbms_sql.bind_variable(p_cursor,'l_grp_param',p_grp_param);
3007 END IF;
3008 PNP_DEBUG_PKG.log('pn_exp_to_ar.do_binding (-)');
3009 END do_binding;
3010
3011
3012
3013 ------------------------------
3014 -- End of Package
3015 ------------------------------
3016 END PN_EXP_TO_AR;