[Home] [Help]
PACKAGE BODY: APPS.CN_ADJ_DISP_PUB
Source
1 PACKAGE BODY cn_adj_disp_pub AS
2 --$Header: cnpadjb.pls 120.2 2005/09/26 07:36:03 chanthon noship $
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_ADJ_DISP_PUB';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'cnpadjb.pls';
6 G_LAST_UPDATE_DATE DATE := sysdate;
7 G_LAST_UPDATED_BY NUMBER := fnd_global.user_id;
8 G_CREATION_DATE DATE := sysdate;
9 G_CREATED_BY NUMBER := fnd_global.user_id;
10 G_LAST_UPDATE_LOGIN NUMBER := fnd_global.login_id;
11
12
13 PROCEDURE get_adj
14 (
15 p_api_version IN NUMBER,
16 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
17 p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL,
18 x_return_status OUT NOCOPY VARCHAR2,
19 x_msg_count OUT NOCOPY NUMBER,
20 x_msg_data OUT NOCOPY VARCHAR2,
21 x_loading_status OUT NOCOPY VARCHAR2,
22 p_salesrep_id IN NUMBER ,
23 p_pr_date_from IN DATE ,
24 p_pr_date_to IN DATE ,
25 p_invoice_num IN VARCHAR2,
26 p_order_num IN NUMBER,
27 p_calc_status IN VARCHAR2,
28 p_adjust_status IN VARCHAR2,
29 p_adjust_date IN DATE,
30 p_trx_type IN VARCHAR2,
31 p_date_pattern IN DATE,
32 p_start_record IN NUMBER := 1,
33 p_increment_count IN NUMBER,
34 p_curr_code IN VARCHAR2,
35 x_adj_tbl OUT NOCOPY adj_tbl_type,
36 x_adj_count OUT NOCOPY NUMBER,
37 x_total_sales_credit OUT NOCOPY NUMBER,
38 x_total_commission OUT NOCOPY NUMBER,
39 x_conv_status OUT NOCOPY VARCHAR2
40 )
41
42
43 IS
44
45 l_api_name CONSTANT VARCHAR2(30) := 'get_adj';
46 l_api_version CONSTANT NUMBER := 1.0;
47 l_flag NUMBER := 0;
48 l_column_value NUMBER;
49 l_pr_date_from DATE ;
50 l_pr_date_to DATE ;
51 l_adjust_date DATE ;
52 l_customer_name VARCHAR2(50);
53 l_customer_number VARCHAR2(30);
54 l_quota_name VARCHAR2(80);
55 l_revenue_class_name VARCHAR2(30);
56 l_credit_type_id NUMBER;
57 l_temp_sts VARCHAR2(30);
58 l_conv_sc NUMBER;
59 l_conv_ca NUMBER;
60
61 adj adj_rec_type;
62
63 --Added l_func_curr to get functional currency to pass it to CN_API
64 l_func_curr varchar2(8);
65 --Added l_org_id as this procedure does not have org_id as I/P param
66 l_org_id Number := mo_global.get_current_org_id();
67
68 TYPE rc IS ref cursor;
69 query_cur rc;
70
71 -- get credit type ID from the quota_id
72 cursor get_credit_type_id (l_quota_id in number) is
73 select nvl(credit_type_id, -1000)
74 from cn_quotas
75 where quota_id = l_quota_id;
76
77 query VARCHAR2(20000) := '
78 SELECT
79 ctrx.invoice_number invoice_number,
80 ctrx.invoice_date invoice_date,
81 ctrx.order_number order_number,
82 ctrx.booked_date order_date,
83 ctrx.creation_date creation_date,
84 ctrx.processed_date processed_date,
85 ctrx.trx_type_disp trx_type_disp,
86 ctrx.adjust_status_disp adjust_status_disp,
87 ctrx.adjusted_by adjusted_by,
88 ctrx.adjust_date adjust_date,
89 ctrx.status_disp calc_status_disp,
90 ctrx.orig_currency_code currency_code,
91 ctrx.transaction_amount sales_credit,
92 ctrx.commission_amount commission,
93 ctrx.attribute1 attribute1,
94 ctrx.attribute2 attribute2,
95 ctrx.attribute3 attribute3,
96 ctrx.attribute4 attribute4,
97 ctrx.attribute5 attribute5,
98 ctrx.attribute6 attribute6,
99 ctrx.attribute7 attribute7,
100 ctrx.attribute8 attribute8,
101 ctrx.attribute9 attribute9,
102 ctrx.attribute10 attribute10,
103 ctrx.attribute11 attribute11,
104 ctrx.attribute12 attribute12,
105 ctrx.attribute13 attribute13,
106 ctrx.attribute14 attribute14,
107 ctrx.attribute15 attribute15,
108 ctrx.attribute16 attribute16,
109 ctrx.attribute17 attribute17,
110 ctrx.attribute18 attribute18,
111 ctrx.attribute19 attribute19,
112 ctrx.attribute20 attribute20,
113 ctrx.attribute21 attribute21,
114 ctrx.attribute22 attribute22,
115 ctrx.attribute23 attribute23,
116 ctrx.attribute24 attribute24,
117 ctrx.attribute25 attribute25,
118 ctrx.attribute26 attribute26,
119 ctrx.attribute27 attribute27,
120 ctrx.attribute28 attribute28,
121 ctrx.attribute29 attribute29,
122 ctrx.attribute30 attribute30,
123 ctrx.attribute31 attribute31,
124 ctrx.attribute32 attribute32,
125 ctrx.attribute33 attribute33,
126 ctrx.attribute34 attribute34,
127 ctrx.attribute35 attribute35,
128 ctrx.attribute36 attribute36,
129 ctrx.attribute37 attribute37,
130 ctrx.attribute38 attribute38,
131 ctrx.attribute39 attribute39,
132 ctrx.attribute40 attribute40,
133 ctrx.attribute41 attribute41,
134 ctrx.attribute42 attribute42,
135 ctrx.attribute43 attribute43,
136 ctrx.attribute44 attribute44,
137 ctrx.attribute45 attribute45,
138 ctrx.attribute46 attribute46,
139 ctrx.attribute47 attribute47,
140 ctrx.attribute48 attribute48,
141 ctrx.attribute49 attribute49,
142 ctrx.attribute50 attribute50,
143 ctrx.attribute51 attribute51,
144 ctrx.attribute52 attribute52,
145 ctrx.attribute53 attribute53,
146 ctrx.attribute54 attribute54,
147 ctrx.attribute55 attribute55,
148 ctrx.attribute56 attribute56,
149 ctrx.attribute57 attribute57,
150 ctrx.attribute58 attribute58,
151 ctrx.attribute59 attribute59,
152 ctrx.attribute60 attribute60,
153 ctrx.attribute61 attribute61,
154 ctrx.attribute62 attribute62,
155 ctrx.attribute63 attribute63,
156 ctrx.attribute64 attribute64,
157 ctrx.attribute65 attribute65,
158 ctrx.attribute66 attribute66,
159 ctrx.attribute67 attribute67,
160 ctrx.attribute68 attribute68,
161 ctrx.attribute69 attribute69,
162 ctrx.attribute70 attribute70,
163 ctrx.attribute71 attribute71,
164 ctrx.attribute72 attribute72,
165 ctrx.attribute73 attribute73,
166 ctrx.attribute74 attribute74,
167 ctrx.attribute75 attribute75,
168 ctrx.attribute76 attribute76,
169 ctrx.attribute77 attribute77,
170 ctrx.attribute78 attribute78,
171 ctrx.attribute79 attribute79,
172 ctrx.attribute80 attribute80,
173 ctrx.attribute81 attribute81,
174 ctrx.attribute82 attribute82,
175 ctrx.attribute83 attribute83,
176 ctrx.attribute84 attribute84,
177 ctrx.attribute85 attribute85,
178 ctrx.attribute86 attribute86,
179 ctrx.attribute87 attribute87,
180 ctrx.attribute88 attribute88,
181 ctrx.attribute89 attribute89,
182 ctrx.attribute90 attribute90,
183 ctrx.attribute91 attribute91,
184 ctrx.attribute92 attribute92,
185 ctrx.attribute93 attribute93,
186 ctrx.attribute94 attribute94,
187 ctrx.attribute95 attribute95,
188 ctrx.attribute96 attribute96,
189 ctrx.attribute97 attribute97,
190 ctrx.attribute98 attribute98,
191 ctrx.attribute99 attribute99,
192 ctrx.attribute100 attribute100,
193 ctrx.customer_id customer_id,
194 NULL customer_name,
195 NULL customer_number,
196 ctrx.bill_to_address_id bill_to_address_id,
197 ctrx.ship_to_address_id ship_to_address_id,
198 ctrx.bill_to_contact_id bill_to_contact_id,
199 ctrx.ship_to_contact_id ship_to_contact_id,
200 ctrx.rollup_date rollup_date,
201 ctrx.comments comments,
202 ctrx.reason_code reason_code,
203 ctrx.reason reason,
204 ctrx.quota_id quota_id,
205 NULL quota_name,
206 ctrx.revenue_class_id revenue_class_id,
207 NULL revenue_class_name
208
209 FROM
210 cn_trx_details ctrx';
211
212
213 BEGIN
214
215 --+
216 --+ Standard call to check for call compatibility.
217 --+
218 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
219 p_api_version ,
220 l_api_name,
221 G_PKG_NAME )
222 THEN
223 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
224 END IF;
225
226 --+
227 --+ Initialize message list if p_init_msg_list is set to TRUE.
228 --+
229 IF FND_API.to_Boolean( p_init_msg_list ) THEN
230 FND_MSG_PUB.initialize;
231 END IF;
232
233 --+
234 --+ Initialize API return status to success
235 --+
236 x_return_status := FND_API.G_RET_STS_SUCCESS;
237 x_loading_status := 'CN_INSERTED';
238
239 --+
240 --+ API body
241 --+
242
243
244 x_adj_count := 0;
245 x_total_sales_credit := 0;
246 x_total_commission := 0;
247
248 IF (p_date_pattern IS NOT NULL) THEN
249 IF (p_adjust_date <> p_date_pattern) THEN
250 l_adjust_date := p_adjust_date;
251 END IF;
252 IF (p_pr_date_from <> p_date_pattern) THEN
253 l_pr_date_from := p_pr_date_from;
254 END IF;
255 IF (p_pr_date_to <> p_date_pattern) THEN
256 l_pr_date_to := p_pr_date_to;
257 END IF;
258 END IF;
259
260 --+ FOR adj IN adj_cur( p_salesrep_id,l_pr_date_from,l_pr_date_to,
261 --+ p_invoice_num, p_order_num, p_calc_status, p_adjust_status,
262 --+ l_adjust_date, p_trx_type)
263
264 query := query || ' WHERE ctrx.credited_salesrep_id = :1 ';
265
266 IF (l_pr_date_from IS NOT NULL) THEN
267 --query := query || ' and trunc(ctrx.processed_date) >= :2 ';
268 query := query || ' and ctrx.processed_date >= trunc(:2) ';
269 ELSE
270 query := query || ' and :2 is null ';
271 END IF;
272
273 IF (l_pr_date_to IS NOT NULL) THEN
274 --query := query || ' and trunc(ctrx.processed_date) <= :3 ';
275 query := query || ' and ctrx.processed_date <= trunc(:3)+0.99999 ';
276 ELSE
277 query := query || ' and :3 is null ';
278 END IF;
279
280 IF (p_invoice_num <> 'ALL') THEN
281 query := query || ' and ctrx.invoice_number = :4 ';
282 ELSE
283 query := query || ' and :4 = ''ALL''';
284 END IF;
285
286 IF (p_order_num <> -99999) THEN
287 query := query || ' and ctrx.order_number = :5 ';
288 ELSE
289 query := query || ' and :5 = -99999';
290 END IF;
291
292 IF (p_calc_status <> 'ALL') THEN
293 query := query || ' and ctrx.status = :6 ';
294 ELSE
295 query := query || ' and :6 = ''ALL''';
296 END IF;
297
298 IF (p_adjust_status <> 'ALL') THEN
299 query := query || ' and ctrx.adjust_status = :7 ';
300 ELSE
301 query := query || ' and :7 = ''ALL''';
302 END IF;
303
304 IF (l_adjust_date IS NOT NULL) THEN
305 query := query || ' and trunc(ctrx.adjust_date) = :8 ';
306 ELSE
307 query := query || ' and :8 is null ';
308 END IF;
309
310 IF (p_trx_type <> 'ALL') THEN
311 query := query || ' and ctrx.trx_type = :9 ';
312 ELSE
313 query := query || ' and :9 = ''ALL''';
314 END IF;
315
316 /*
317 dbms_output.put_line('p_salesrep_id'||p_salesrep_id);
318 dbms_output.put_line('l_pr_date_from'||l_pr_date_from);
319 dbms_output.put_line('l_pr_date_to'||l_pr_date_to);
320 dbms_output.put_line('p_invoice_num'||p_invoice_num);
321 dbms_output.put_line('p_order_num'||p_order_num);
322 dbms_output.put_line('p_calc_status'||p_calc_status);
323 dbms_output.put_line('p_adjust_status'||p_adjust_status);
324 dbms_output.put_line('l_adjust_date'||l_adjust_date);
325 dbms_output.put_line('p_trx_type'||p_trx_type);
326 */
327
328 OPEN query_cur FOR query
329 using
330 p_salesrep_id, -- :1
331 l_pr_date_from, -- :2
332 l_pr_date_to, -- :3
333 p_invoice_num, -- :4
334 p_order_num, -- :5
335 p_calc_status, -- :6
336 p_adjust_status, -- :7
337 l_adjust_date, -- :8
338 p_trx_type; -- :9
339
340 LOOP
341
342 FETCH query_cur INTO adj;
343 exit when query_cur%notfound;
344
345 --dbms_output.put_line('adj.sales_credit = ' || adj.sales_credit);
346 --dbms_output.put_line('adj.commission = ' || adj.commission);
347
348 x_adj_count := x_adj_count + 1;
349 l_credit_type_id := -1000;
350 open get_credit_type_id(adj.quota_id);
351 fetch get_credit_type_id into l_credit_type_id;
352 close get_credit_type_id;
353
354 /* cn_ytd_balances_pvt.Currency_Convert
355 (nvl(adj.sales_credit,0), l_credit_type_id, p_curr_code,
356 l_conv_sc, l_temp_sts);
357 cn_ytd_balances_pvt.Currency_Convert
358 (nvl(adj.commission,0), l_credit_type_id, p_curr_code,
359 l_conv_ca, l_temp_sts); */
360
361 select s.currency_code into l_func_curr
362 from gl_sets_of_books s, cn_repositories r
363 where r.set_of_books_id = s.set_of_books_id
364 and r.application_id = 283 and r.org_id=l_org_id;
365 l_conv_sc := cn_api.convert_to_repcurr(nvl(adj.sales_credit,0),
366 sysdate,
367 nvl(cn_system_parameters.value('CN_CONVERSION_TYPE',l_org_id),'Corporate'),
368 l_credit_type_id,
369 l_func_curr,
370 p_curr_code,
371 l_org_id
372 );
373 l_conv_ca := cn_api.convert_to_repcurr(nvl(adj.commission,0),
374 sysdate,
375 nvl(cn_system_parameters.value('CN_CONVERSION_TYPE',l_org_id),'Corporate'),
376 l_credit_type_id,
377 l_func_curr,
378 p_curr_code,
379 l_org_id
380 );
381
382
386
383 if (l_temp_sts <> 'SUCCESS' AND x_conv_status is null) then
384 x_conv_status := fnd_message.get_string('CN', l_temp_sts);
385 end if;
387 x_total_sales_credit := x_total_sales_credit + l_conv_sc;
388 x_total_commission := x_total_commission + l_conv_ca;
389
390 IF (( p_increment_count = -9999) OR
391 (x_adj_count BETWEEN p_start_record
392 AND (p_start_record + p_increment_count -1)))
393 THEN
394 x_adj_tbl(x_adj_count).invoice_number := adj.invoice_number;
395 x_adj_tbl(x_adj_count).invoice_date := adj.invoice_date;
396 x_adj_tbl(x_adj_count).order_number := adj.order_number;
397 x_adj_tbl(x_adj_count).order_date := adj.order_date;
398 x_adj_tbl(x_adj_count).creation_date := adj.creation_date;
399 x_adj_tbl(x_adj_count).processed_date := adj.processed_date;
400 x_adj_tbl(x_adj_count).trx_type_disp := adj.trx_type_disp;
401 x_adj_tbl(x_adj_count).adjust_status_disp := adj.adjust_status_disp;
402 x_adj_tbl(x_adj_count).adjusted_by := adj.adjusted_by;
403 x_adj_tbl(x_adj_count).adjust_date := adj.adjust_date;
404 x_adj_tbl(x_adj_count).calc_status_disp := adj.calc_status_disp;
405 x_adj_tbl(x_adj_count).currency_code := adj.currency_code;
406 x_adj_tbl(x_adj_count).sales_credit := l_conv_sc;
407 x_adj_tbl(x_adj_count).commission := l_conv_ca;
408
409 x_adj_tbl(x_adj_count).attribute1 :=
410 adj.attribute1;
411 x_adj_tbl(x_adj_count).attribute2 :=
412 adj.attribute2;
413 x_adj_tbl(x_adj_count).attribute3 :=
414 adj.attribute3;
415 x_adj_tbl(x_adj_count).attribute4 :=
416 adj.attribute4;
417 x_adj_tbl(x_adj_count).attribute5 :=
418 adj.attribute5;
419 x_adj_tbl(x_adj_count).attribute6 :=
420 adj.attribute6;
421 x_adj_tbl(x_adj_count).attribute7 :=
422 adj.attribute7;
423 x_adj_tbl(x_adj_count).attribute8 :=
424 adj.attribute8;
425 x_adj_tbl(x_adj_count).attribute9 :=
426 adj.attribute9;
427 x_adj_tbl(x_adj_count).attribute10 :=
428 adj.attribute10;
429 x_adj_tbl(x_adj_count).attribute11 :=
430 adj.attribute11;
431 x_adj_tbl(x_adj_count).attribute12 :=
432 adj.attribute12;
433 x_adj_tbl(x_adj_count).attribute13 :=
434 adj.attribute13;
435 x_adj_tbl(x_adj_count).attribute14 :=
436 adj.attribute14;
437 x_adj_tbl(x_adj_count).attribute15 :=
438 adj.attribute15;
439 x_adj_tbl(x_adj_count).attribute16 :=
440 adj.attribute16;
441 x_adj_tbl(x_adj_count).attribute17 :=
442 adj.attribute17;
443 x_adj_tbl(x_adj_count).attribute18 :=
444 adj.attribute18;
445 x_adj_tbl(x_adj_count).attribute19 :=
446 adj.attribute19;
447 x_adj_tbl(x_adj_count).attribute20 :=
448 adj.attribute20;
449 x_adj_tbl(x_adj_count).attribute21 :=
450 adj.attribute21;
451 x_adj_tbl(x_adj_count).attribute22 :=
452 adj.attribute22;
453 x_adj_tbl(x_adj_count).attribute23 :=
454 adj.attribute23;
455 x_adj_tbl(x_adj_count).attribute24 :=
456 adj.attribute24;
457 x_adj_tbl(x_adj_count).attribute25 :=
458 adj.attribute25;
459 x_adj_tbl(x_adj_count).attribute26 :=
460 adj.attribute26;
461 x_adj_tbl(x_adj_count).attribute27 :=
462 adj.attribute27;
463 x_adj_tbl(x_adj_count).attribute28 :=
464 adj.attribute28;
465 x_adj_tbl(x_adj_count).attribute29 :=
466 adj.attribute29;
467 x_adj_tbl(x_adj_count).attribute30 :=
468 adj.attribute30;
469 x_adj_tbl(x_adj_count).attribute31 :=
470 adj.attribute31;
471 x_adj_tbl(x_adj_count).attribute32 :=
472 adj.attribute32;
473 x_adj_tbl(x_adj_count).attribute33 :=
474 adj.attribute33;
475 x_adj_tbl(x_adj_count).attribute34 :=
476 adj.attribute34;
477 x_adj_tbl(x_adj_count).attribute35 :=
478 adj.attribute35;
479 x_adj_tbl(x_adj_count).attribute36 :=
480 adj.attribute36;
481 x_adj_tbl(x_adj_count).attribute37 :=
482 adj.attribute37;
483 x_adj_tbl(x_adj_count).attribute38 :=
484 adj.attribute38;
485 x_adj_tbl(x_adj_count).attribute39 :=
486 adj.attribute39;
487 x_adj_tbl(x_adj_count).attribute40 :=
488 adj.attribute40;
489 x_adj_tbl(x_adj_count).attribute41 :=
490 adj.attribute41;
491 x_adj_tbl(x_adj_count).attribute42 :=
492 adj.attribute42;
493 x_adj_tbl(x_adj_count).attribute43 :=
494 adj.attribute43;
495 x_adj_tbl(x_adj_count).attribute44 :=
496 adj.attribute44;
497 x_adj_tbl(x_adj_count).attribute45 :=
498 adj.attribute45;
499 x_adj_tbl(x_adj_count).attribute46 :=
500 adj.attribute46;
501 x_adj_tbl(x_adj_count).attribute47 :=
502 adj.attribute47;
503 x_adj_tbl(x_adj_count).attribute48 :=
504 adj.attribute48;
505 x_adj_tbl(x_adj_count).attribute49 :=
506 adj.attribute49;
507 x_adj_tbl(x_adj_count).attribute50 :=
508 adj.attribute50;
509 x_adj_tbl(x_adj_count).attribute51 :=
510 adj.attribute51;
511 x_adj_tbl(x_adj_count).attribute52 :=
512 adj.attribute52;
513 x_adj_tbl(x_adj_count).attribute53 :=
517 x_adj_tbl(x_adj_count).attribute55 :=
514 adj.attribute53;
515 x_adj_tbl(x_adj_count).attribute54 :=
516 adj.attribute54;
518 adj.attribute55;
519 x_adj_tbl(x_adj_count).attribute56 :=
520 adj.attribute56;
521 x_adj_tbl(x_adj_count).attribute57 :=
522 adj.attribute57;
523 x_adj_tbl(x_adj_count).attribute58 :=
524 adj.attribute58;
525 x_adj_tbl(x_adj_count).attribute59 :=
526 adj.attribute59;
527 x_adj_tbl(x_adj_count).attribute60 :=
528 adj.attribute60;
529 x_adj_tbl(x_adj_count).attribute61 :=
530 adj.attribute61;
531 x_adj_tbl(x_adj_count).attribute62 :=
532 adj.attribute62;
533 x_adj_tbl(x_adj_count).attribute63 :=
534 adj.attribute63;
535 x_adj_tbl(x_adj_count).attribute64 :=
536 adj.attribute64;
537 x_adj_tbl(x_adj_count).attribute65 :=
538 adj.attribute65;
539 x_adj_tbl(x_adj_count).attribute66 :=
540 adj.attribute66;
541 x_adj_tbl(x_adj_count).attribute67 :=
542 adj.attribute67;
543 x_adj_tbl(x_adj_count).attribute68 :=
544 adj.attribute68;
545 x_adj_tbl(x_adj_count).attribute69 :=
546 adj.attribute69;
547 x_adj_tbl(x_adj_count).attribute70 :=
548 adj.attribute70;
549 x_adj_tbl(x_adj_count).attribute71 :=
550 adj.attribute71;
551 x_adj_tbl(x_adj_count).attribute72 :=
552 adj.attribute72;
553 x_adj_tbl(x_adj_count).attribute73 :=
554 adj.attribute73;
555 x_adj_tbl(x_adj_count).attribute74 :=
556 adj.attribute74;
557 x_adj_tbl(x_adj_count).attribute75 :=
558 adj.attribute75;
559 x_adj_tbl(x_adj_count).attribute76 :=
560 adj.attribute76;
561 x_adj_tbl(x_adj_count).attribute77 :=
562 adj.attribute77;
563 x_adj_tbl(x_adj_count).attribute78 :=
564 adj.attribute78;
565 x_adj_tbl(x_adj_count).attribute79 :=
566 adj.attribute79;
567 x_adj_tbl(x_adj_count).attribute80 :=
568 adj.attribute80;
569 x_adj_tbl(x_adj_count).attribute81 :=
570 adj.attribute81;
571 x_adj_tbl(x_adj_count).attribute82 :=
572 adj.attribute82;
573 x_adj_tbl(x_adj_count).attribute83 :=
574 adj.attribute83;
575 x_adj_tbl(x_adj_count).attribute84 :=
576 adj.attribute84;
577 x_adj_tbl(x_adj_count).attribute85 :=
578 adj.attribute85;
579 x_adj_tbl(x_adj_count).attribute86 :=
580 adj.attribute86;
581 x_adj_tbl(x_adj_count).attribute87 :=
582 adj.attribute87;
583 x_adj_tbl(x_adj_count).attribute88 :=
584 adj.attribute88;
585 x_adj_tbl(x_adj_count).attribute89 :=
586 adj.attribute89;
587 x_adj_tbl(x_adj_count).attribute90 :=
588 adj.attribute90;
589 x_adj_tbl(x_adj_count).attribute91 :=
590 adj.attribute91;
591 x_adj_tbl(x_adj_count).attribute92 :=
592 adj.attribute92;
593 x_adj_tbl(x_adj_count).attribute93 :=
594 adj.attribute93;
595 x_adj_tbl(x_adj_count).attribute94 :=
596 adj.attribute94;
597 x_adj_tbl(x_adj_count).attribute95 :=
598 adj.attribute95;
599 x_adj_tbl(x_adj_count).attribute96 :=
600 adj.attribute96;
601 x_adj_tbl(x_adj_count).attribute97 :=
602 adj.attribute97;
603 x_adj_tbl(x_adj_count).attribute98 :=
604 adj.attribute98;
605 x_adj_tbl(x_adj_count).attribute99 :=
606 adj.attribute99;
607 x_adj_tbl(x_adj_count).attribute100 :=
608 adj.attribute100;
609
610
611 x_adj_tbl(x_adj_count).customer_id := adj.customer_id;
612 IF adj.customer_id IS NOT NULL THEN
613 BEGIN
614 SELECT substrb(PARTY.PARTY_NAME,1,50), CUST_ACCT.ACCOUNT_NUMBER
615 INTO l_customer_name, l_customer_number
616 FROM HZ_PARTIES PARTY, HZ_CUST_ACCOUNTS CUST_ACCT
617 WHERE CUST_ACCT.CUST_ACCOUNT_ID = adj.customer_id
618 AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID;
619 x_adj_tbl(x_adj_count).customer_name := l_customer_name;
620 x_adj_tbl(x_adj_count).customer_number := l_customer_number;
621 EXCEPTION
622 WHEN NO_DATA_FOUND THEN
623 x_adj_tbl(x_adj_count).customer_name := NULL;
624 x_adj_tbl(x_adj_count).customer_number := NULL;
625 END;
626 ELSE
627 x_adj_tbl(x_adj_count).customer_name := NULL;
628 x_adj_tbl(x_adj_count).customer_number := NULL;
629 END IF;
630
631 x_adj_tbl(x_adj_count).bill_to_address_id := adj.bill_to_address_id;
632 x_adj_tbl(x_adj_count).ship_to_address_id := adj.ship_to_address_id;
633 x_adj_tbl(x_adj_count).bill_to_contact_id := adj.bill_to_contact_id;
634 x_adj_tbl(x_adj_count).ship_to_contact_id := adj.ship_to_contact_id;
635 x_adj_tbl(x_adj_count).rollup_date := adj.rollup_date;
636 x_adj_tbl(x_adj_count).comments := adj.comments;
637 x_adj_tbl(x_adj_count).reason_code := adj.reason_code;
638 x_adj_tbl(x_adj_count).reason := adj.reason;
639
640 x_adj_tbl(x_adj_count).quota_id := adj.quota_id;
641 IF adj.quota_id IS NOT NULL THEN
642 BEGIN
643 SELECT name INTO l_quota_name
644 FROM cn_quotas WHERE quota_id = adj.quota_id;
648 x_adj_tbl(x_adj_count).quota_name := NULL;
645 x_adj_tbl(x_adj_count).quota_name := l_quota_name;
646 EXCEPTION
647 WHEN no_data_found THEN
649 END;
650 ELSE
651 x_adj_tbl(x_adj_count).quota_name := NULL;
652 END IF;
653
654 x_adj_tbl(x_adj_count).revenue_class_id := adj.revenue_class_id;
655 IF adj.revenue_class_id IS NOT NULL THEN
656 BEGIN
657 SELECT name INTO l_revenue_class_name
658 FROM cn_revenue_classes
659 WHERE revenue_class_id = adj.revenue_class_id;
660 x_adj_tbl(x_adj_count).revenue_class_name :=
661 l_revenue_class_name;
662 EXCEPTION
663 WHEN no_data_found THEN
664 x_adj_tbl(x_adj_count).revenue_class_name := NULL;
665 END;
666 ELSE
667 x_adj_tbl(x_adj_count).revenue_class_name := NULL;
668 END IF;
669
670
671 END IF;
672
673 END LOOP;
674
675
676
677 EXCEPTION
678 WHEN FND_API.G_EXC_ERROR THEN
679 x_return_status := FND_API.G_RET_STS_ERROR ;
680 FND_MSG_PUB.Count_And_Get
681 (
682 p_count => x_msg_count ,
683 p_data => x_msg_data ,
684 p_encoded => FND_API.G_FALSE
685 );
686 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
687 x_loading_status := 'UNEXPECTED_ERR';
688 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
689 FND_MSG_PUB.Count_And_Get
690 (
691 p_count => x_msg_count ,
692 p_data => x_msg_data ,
693 p_encoded => FND_API.G_FALSE
694 );
695 WHEN OTHERS THEN
696 x_loading_status := 'UNEXPECTED_ERR';
697 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
698 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
699 THEN
700 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
701 END IF;
702 FND_MSG_PUB.Count_And_Get
703 (
704 p_count => x_msg_count ,
705 p_data => x_msg_data ,
706 p_encoded => FND_API.G_FALSE
707 );
708
709 END;
710 END cn_adj_disp_pub;