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