[Home] [Help]
PACKAGE BODY: APPS.CN_MASS_ADJUST_UTIL
Source
1 PACKAGE BODY CN_MASS_ADJUST_UTIL AS
2 -- $Header: cnvmutlb.pls 120.10.12000000.3 2007/08/09 15:00:06 apink ship $
3 -- +======================================================================+
4 -- | Copyright (c) 1994 Oracle Corporation |
5 -- | Redwood Shores, California, USA |
6 -- | All rights reserved. |
7 -- +======================================================================+
8 --
9 -- Package Name
10 -- CN_MASS_ADJUST_UTIL
11 -- Purpose
12 -- Package Body for Mass Adjustments Package
13 -- History
14 -- 10/27/03 Hithanki R12 Version
15 --
16 -- Nov 10, 2005 vensrini Added org_id join condition
17 -- to l_header_sql and l_api_sql dynamic sql
18 -- Nov 14, 2005 vensrini Removed commented out portion in
19 -- convert_rec_to_gmiss procedure
20 -- Jun 26, 2006 vensrinin Bug fix 5349170
21 --
22 --
23 --
24
25
26
27 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_MASS_ADJUST_UTIL';
28 G_FILE_NAME CONSTANT VARCHAR2(12) := 'cnvmutlb.pls';
29 g_space VARCHAR2(10) := '&'||'nbsp;';
30
31 --
32 FUNCTION g_track_invoice
33 RETURN VARCHAR2 IS
34 l_track_invoice VARCHAR2(1) := 'N';
35 BEGIN
36 l_track_invoice := NVL(fnd_profile.value('CN_TRACK_INVOICE'),'N');
37 RETURN l_track_invoice;
38 EXCEPTION
39 WHEN OTHERS THEN
40 RETURN l_track_invoice;
41 END;
42 --
43 PROCEDURE find_functional_amount(
44 p_from_currency IN VARCHAR2,
45 p_to_currency IN VARCHAR2,
46 p_conversion_date IN DATE,
47 p_conversion_type IN VARCHAR2,
48 p_from_amount IN NUMBER,
49 x_to_amount OUT NOCOPY NUMBER,
50 x_return_status OUT NOCOPY VARCHAR2) IS
51 -- Local variables
52 l_conversion_date DATE;
53 l_check_max CHAR(1) := 'Y';
54
55 --
56 CURSOR c1 IS
57 SELECT conversion_date
58 FROM gl_daily_rates
59 WHERE from_currency = p_from_currency
60 AND to_currency = p_to_currency
61 AND conversion_type = p_conversion_type
62 AND conversion_date = p_conversion_date
63 AND rownum < 2
64 ORDER BY conversion_date DESC;
65 CURSOR c2 IS
66 SELECT MAX(conversion_date) conversion_date
67 FROM gl_daily_rates
68 WHERE from_currency = p_from_currency
69 AND to_currency = p_to_currency
70 AND conversion_type = p_conversion_type
71 AND conversion_date < p_conversion_date;
72 --
73 BEGIN
74 x_return_status := FND_API.G_RET_STS_SUCCESS;
75 FOR rec IN c1
76 LOOP
77 l_conversion_date := rec.conversion_date;
78 l_check_max := 'N';
79 END LOOP;
80 IF (l_check_max = 'Y') THEN
81 FOR rec IN c2
82 LOOP
83 IF (rec.conversion_date IS NOT NULL) THEN
84 l_conversion_date := rec.conversion_date;
85 l_check_max := 'N';
86 END IF;
87 END LOOP;
88 END IF;
89 IF (l_check_max = 'Y') THEN
90 RAISE NO_DATA_FOUND;
91 ELSE
92 x_to_amount :=
93 gl_currency_api.convert_amount(
94 x_from_currency => p_from_currency,
95 x_to_currency => p_to_currency,
96 x_conversion_date => l_conversion_date,
97 x_conversion_type => p_conversion_type,
98 x_amount => p_from_amount);
99 x_return_status := 'SUCCESS';
100 END IF;
101 EXCEPTION
102 WHEN NO_DATA_FOUND THEN
103 x_to_amount := 0;
104 x_return_status := 'NO DATA';
105 WHEN OTHERS THEN
106 x_to_amount := 0;
107 x_return_status := 'ERROR';
108 END find_functional_amount;
109 --
110 PROCEDURE search_result (
111 p_salesrep_id IN NUMBER := FND_API.G_MISS_NUM,
112 p_pr_date_to IN DATE := FND_API.G_MISS_DATE,
113 p_pr_date_from IN DATE := FND_API.G_MISS_DATE,
114 p_calc_status IN VARCHAR2:= FND_API.G_MISS_CHAR,
115 p_adj_status IN VARCHAR2:= FND_API.G_MISS_CHAR,
116 p_load_status IN VARCHAR2:= FND_API.G_MISS_CHAR,
117 p_invoice_num IN VARCHAR2:= FND_API.G_MISS_CHAR,
118 p_order_num IN NUMBER := FND_API.G_MISS_NUM,
119 p_org_id IN NUMBER := FND_API.G_MISS_NUM,
120 p_srch_attr_rec IN cn_get_tx_data_pub.adj_rec_type,
121 x_return_status OUT NOCOPY VARCHAR2,
122 x_adj_tbl OUT NOCOPY cn_get_tx_data_pub.adj_tbl_type,
123 x_source_counter OUT NOCOPY NUMBER) IS
124 --
125 TYPE api_rec IS RECORD(
126 salesrep_id cn_comm_lines_api.salesrep_id%TYPE,
127 processed_date cn_comm_lines_api.processed_date%TYPE,
128 processed_period_id cn_comm_lines_api.processed_period_id%TYPE,
129 transaction_amount cn_comm_lines_api.transaction_amount%TYPE,
130 trx_type cn_comm_lines_api.trx_type%TYPE,
131 revenue_class_id cn_comm_lines_api.revenue_class_id%TYPE,
132 load_status cn_comm_lines_api.load_status%TYPE,
133 attribute_category cn_comm_lines_api.attribute_category%TYPE,
134 attribute1 cn_comm_lines_api.attribute1%TYPE,
135 attribute2 cn_comm_lines_api.attribute2%TYPE,
136 attribute3 cn_comm_lines_api.attribute3%TYPE,
137 attribute4 cn_comm_lines_api.attribute4%TYPE,
138 attribute5 cn_comm_lines_api.attribute5%TYPE,
139 attribute6 cn_comm_lines_api.attribute6%TYPE,
140 attribute7 cn_comm_lines_api.attribute7%TYPE,
141 attribute8 cn_comm_lines_api.attribute8%TYPE,
142 attribute9 cn_comm_lines_api.attribute9%TYPE,
143 attribute10 cn_comm_lines_api.attribute10%TYPE,
144 attribute11 cn_comm_lines_api.attribute11%TYPE,
145 attribute12 cn_comm_lines_api.attribute12%TYPE,
146 attribute13 cn_comm_lines_api.attribute13%TYPE,
147 attribute14 cn_comm_lines_api.attribute14%TYPE,
148 attribute15 cn_comm_lines_api.attribute15%TYPE,
149 attribute16 cn_comm_lines_api.attribute16%TYPE,
150 attribute17 cn_comm_lines_api.attribute17%TYPE,
151 attribute18 cn_comm_lines_api.attribute18%TYPE,
152 attribute19 cn_comm_lines_api.attribute19%TYPE,
153 attribute20 cn_comm_lines_api.attribute20%TYPE,
154 attribute21 cn_comm_lines_api.attribute21%TYPE,
155 attribute22 cn_comm_lines_api.attribute22%TYPE,
156 attribute23 cn_comm_lines_api.attribute23%TYPE,
157 attribute24 cn_comm_lines_api.attribute24%TYPE,
158 attribute25 cn_comm_lines_api.attribute25%TYPE,
159 attribute26 cn_comm_lines_api.attribute26%TYPE,
160 attribute27 cn_comm_lines_api.attribute27%TYPE,
161 attribute28 cn_comm_lines_api.attribute28%TYPE,
162 attribute29 cn_comm_lines_api.attribute29%TYPE,
163 attribute30 cn_comm_lines_api.attribute30%TYPE,
164 attribute31 cn_comm_lines_api.attribute31%TYPE,
165 attribute32 cn_comm_lines_api.attribute32%TYPE,
166 attribute33 cn_comm_lines_api.attribute33%TYPE,
167 attribute34 cn_comm_lines_api.attribute34%TYPE,
168 attribute35 cn_comm_lines_api.attribute35%TYPE,
169 attribute36 cn_comm_lines_api.attribute36%TYPE,
170 attribute37 cn_comm_lines_api.attribute37%TYPE,
171 attribute38 cn_comm_lines_api.attribute38%TYPE,
172 attribute39 cn_comm_lines_api.attribute39%TYPE,
173 attribute40 cn_comm_lines_api.attribute40%TYPE,
174 attribute41 cn_comm_lines_api.attribute41%TYPE,
175 attribute42 cn_comm_lines_api.attribute42%TYPE,
176 attribute43 cn_comm_lines_api.attribute43%TYPE,
177 attribute44 cn_comm_lines_api.attribute44%TYPE,
178 attribute45 cn_comm_lines_api.attribute45%TYPE,
179 attribute46 cn_comm_lines_api.attribute46%TYPE,
180 attribute47 cn_comm_lines_api.attribute47%TYPE,
181 attribute48 cn_comm_lines_api.attribute48%TYPE,
182 attribute49 cn_comm_lines_api.attribute49%TYPE,
183 attribute50 cn_comm_lines_api.attribute50%TYPE,
184 attribute51 cn_comm_lines_api.attribute51%TYPE,
185 attribute52 cn_comm_lines_api.attribute52%TYPE,
186 attribute53 cn_comm_lines_api.attribute53%TYPE,
187 attribute54 cn_comm_lines_api.attribute54%TYPE,
188 attribute55 cn_comm_lines_api.attribute55%TYPE,
189 attribute56 cn_comm_lines_api.attribute56%TYPE,
190 attribute57 cn_comm_lines_api.attribute57%TYPE,
191 attribute58 cn_comm_lines_api.attribute58%TYPE,
192 attribute59 cn_comm_lines_api.attribute59%TYPE,
193 attribute60 cn_comm_lines_api.attribute60%TYPE,
194 attribute61 cn_comm_lines_api.attribute61%TYPE,
195 attribute62 cn_comm_lines_api.attribute62%TYPE,
196 attribute63 cn_comm_lines_api.attribute63%TYPE,
197 attribute64 cn_comm_lines_api.attribute64%TYPE,
198 attribute65 cn_comm_lines_api.attribute65%TYPE,
199 attribute66 cn_comm_lines_api.attribute66%TYPE,
200 attribute67 cn_comm_lines_api.attribute67%TYPE,
201 attribute68 cn_comm_lines_api.attribute68%TYPE,
202 attribute69 cn_comm_lines_api.attribute69%TYPE,
203 attribute70 cn_comm_lines_api.attribute70%TYPE,
204 attribute71 cn_comm_lines_api.attribute71%TYPE,
205 attribute72 cn_comm_lines_api.attribute72%TYPE,
206 attribute73 cn_comm_lines_api.attribute73%TYPE,
207 attribute74 cn_comm_lines_api.attribute74%TYPE,
208 attribute75 cn_comm_lines_api.attribute75%TYPE,
209 attribute76 cn_comm_lines_api.attribute76%TYPE,
210 attribute77 cn_comm_lines_api.attribute77%TYPE,
211 attribute78 cn_comm_lines_api.attribute78%TYPE,
212 attribute79 cn_comm_lines_api.attribute79%TYPE,
213 attribute80 cn_comm_lines_api.attribute80%TYPE,
214 attribute81 cn_comm_lines_api.attribute81%TYPE,
215 attribute82 cn_comm_lines_api.attribute82%TYPE,
216 attribute83 cn_comm_lines_api.attribute83%TYPE,
217 attribute84 cn_comm_lines_api.attribute84%TYPE,
218 attribute85 cn_comm_lines_api.attribute85%TYPE,
219 attribute86 cn_comm_lines_api.attribute86%TYPE,
220 attribute87 cn_comm_lines_api.attribute87%TYPE,
221 attribute88 cn_comm_lines_api.attribute88%TYPE,
222 attribute89 cn_comm_lines_api.attribute89%TYPE,
223 attribute90 cn_comm_lines_api.attribute90%TYPE,
224 attribute91 cn_comm_lines_api.attribute91%TYPE,
225 attribute92 cn_comm_lines_api.attribute92%TYPE,
226 attribute93 cn_comm_lines_api.attribute93%TYPE,
227 attribute94 cn_comm_lines_api.attribute94%TYPE,
228 attribute95 cn_comm_lines_api.attribute95%TYPE,
229 attribute96 cn_comm_lines_api.attribute96%TYPE,
230 attribute97 cn_comm_lines_api.attribute97%TYPE,
231 attribute98 cn_comm_lines_api.attribute98%TYPE,
232 attribute99 cn_comm_lines_api.attribute99%TYPE,
233 attribute100 cn_comm_lines_api.attribute100%TYPE,
234 comm_lines_api_id cn_comm_lines_api.comm_lines_api_id%TYPE,
235 conc_batch_id cn_comm_lines_api.conc_batch_id%TYPE,
236 process_batch_id cn_comm_lines_api.process_batch_id%TYPE,
237 salesrep_number cn_comm_lines_api.salesrep_number%TYPE,
238 rollup_date cn_comm_lines_api.rollup_date%TYPE,
239 source_doc_id cn_comm_lines_api.source_doc_id%TYPE,
240 source_doc_type cn_comm_lines_api.source_doc_type%TYPE,
241 created_by cn_comm_lines_api.created_by%TYPE,
242 creation_date cn_comm_lines_api.creation_date%TYPE,
243 last_updated_by cn_comm_lines_api.last_updated_by%TYPE,
244 last_update_date cn_comm_lines_api.last_update_date%TYPE,
245 last_update_login cn_comm_lines_api.last_update_login%TYPE,
246 transaction_currency_code cn_comm_lines_api.transaction_currency_code%TYPE,
247 exchange_rate cn_comm_lines_api.exchange_rate%TYPE,
248 acctd_transaction_amount cn_comm_lines_api.acctd_transaction_amount%TYPE,
249 trx_id cn_comm_lines_api.trx_id%TYPE,
250 trx_line_id cn_comm_lines_api.trx_line_id%TYPE,
251 trx_sales_line_id cn_comm_lines_api.trx_sales_line_id%TYPE,
252 org_id cn_comm_lines_api.org_id%TYPE,
253 quantity cn_comm_lines_api.quantity%TYPE,
254 source_trx_number cn_comm_lines_api.source_trx_number%TYPE,
255 discount_percentage cn_comm_lines_api.discount_percentage%TYPE,
256 margin_percentage cn_comm_lines_api.margin_percentage%TYPE,
257 source_trx_id cn_comm_lines_api.source_trx_id%TYPE,
258 source_trx_line_id cn_comm_lines_api.source_trx_line_id%TYPE,
259 source_trx_sales_line_id cn_comm_lines_api.source_trx_sales_line_id%TYPE,
260 negated_flag cn_comm_lines_api.negated_flag%TYPE,
261 customer_id cn_comm_lines_api.customer_id%TYPE,
262 inventory_item_id cn_comm_lines_api.inventory_item_id%TYPE,
263 order_number cn_comm_lines_api.order_number%TYPE,
264 booked_date cn_comm_lines_api.booked_date%TYPE,
265 invoice_number cn_comm_lines_api.invoice_number%TYPE,
266 invoice_date cn_comm_lines_api.invoice_date%TYPE,
267 adjust_date cn_comm_lines_api.adjust_date%TYPE,
268 adjusted_by cn_comm_lines_api.adjusted_by%TYPE,
269 revenue_type cn_comm_lines_api.revenue_type%TYPE,
270 adjust_rollup_flag cn_comm_lines_api.adjust_rollup_flag%TYPE,
271 adjust_comments cn_comm_lines_api.adjust_comments%TYPE,
272 adjust_status cn_comm_lines_api.adjust_status%TYPE,
273 line_number cn_comm_lines_api.line_number%TYPE,
274 bill_to_address_id cn_comm_lines_api.bill_to_address_id%TYPE,
275 ship_to_address_id cn_comm_lines_api.ship_to_address_id%TYPE,
276 bill_to_contact_id cn_comm_lines_api.bill_to_contact_id%TYPE,
277 ship_to_contact_id cn_comm_lines_api.ship_to_contact_id%TYPE,
278 adj_comm_lines_api_id cn_comm_lines_api.adj_comm_lines_api_id%TYPE,
279 pre_defined_rc_flag cn_comm_lines_api.pre_defined_rc_flag%TYPE,
280 rollup_flag cn_comm_lines_api.rollup_flag%TYPE,
281 forecast_id cn_comm_lines_api.forecast_id%TYPE,
282 upside_quantity cn_comm_lines_api.upside_quantity%TYPE,
283 upside_amount cn_comm_lines_api.upside_amount%TYPE,
284 uom_code cn_comm_lines_api.uom_code%TYPE,
285 reason_code cn_comm_lines_api.reason_code%TYPE,
286 type cn_comm_lines_api.type%TYPE,
287 pre_processed_code cn_comm_lines_api.pre_processed_code%TYPE,
288 quota_id cn_comm_lines_api.quota_id%TYPE,
289 srp_plan_assign_id cn_comm_lines_api.srp_plan_assign_id%TYPE,
290 role_id cn_comm_lines_api.role_id%TYPE,
291 comp_group_id cn_comm_lines_api.comp_group_id%TYPE,
292 commission_amount cn_comm_lines_api.commission_amount%TYPE,
293 employee_number cn_comm_lines_api.employee_number%TYPE,
294 reversal_flag cn_comm_lines_api.reversal_flag%TYPE,
295 reversal_header_id cn_comm_lines_api.reversal_header_id%TYPE,
296 sales_channel cn_comm_lines_api.sales_channel%TYPE,
297 object_version_number cn_comm_lines_api.object_version_number%TYPE,
298 split_pct cn_comm_lines_api.split_pct%TYPE,
299 split_status cn_comm_lines_api.split_status%TYPE,
300 direct_salesrep_number cn_salesreps.employee_number%TYPE,
301 direct_salesrep_name cn_salesreps.name%TYPE,
302 period_name cn_period_statuses.period_name%TYPE,
303 trx_type_disp cn_lookups.meaning%TYPE,
304 reason cn_lookups.meaning%TYPE,
308 terr_id cn_comm_lines_api.terr_id%TYPE,
305 revenue_class_name cn_revenue_classes.name%TYPE,
306 revenue_type_disp cn_lookups.meaning%TYPE,
307 adjust_status_disp cn_lookups.meaning%TYPE,
309 preserve_credit_override_flag cn_comm_lines_api.preserve_credit_override_flag%TYPE);
310 l_api_rec api_rec;
311
312 TYPE header_rec IS RECORD(
313 commission_header_id cn_commission_headers.commission_header_id%TYPE,
314 direct_salesrep_id cn_commission_headers.direct_salesrep_id%TYPE,
315 processed_date cn_commission_headers.processed_date%TYPE,
316 processed_period_id cn_commission_headers.processed_period_id%TYPE,
317 rollup_date cn_commission_headers.rollup_date%TYPE,
318 transaction_amount cn_commission_headers.transaction_amount%TYPE,
319 quantity cn_commission_headers.quantity%TYPE,
320 discount_percentage cn_commission_headers.discount_percentage%TYPE,
321 margin_percentage cn_commission_headers.margin_percentage%TYPE,
322 orig_currency_code cn_commission_headers.orig_currency_code%TYPE,
323 transaction_amount_orig cn_commission_headers.transaction_amount_orig%TYPE,
324 trx_type cn_commission_headers.trx_type%TYPE,
325 status cn_commission_headers.status%TYPE,
326 pre_processed_code cn_commission_headers.pre_processed_code%TYPE,
327 comm_lines_api_id cn_commission_headers.comm_lines_api_id%TYPE,
328 source_doc_type cn_commission_headers.source_doc_type%TYPE,
329 source_trx_number cn_commission_headers.source_trx_number%TYPE,
330 quota_id cn_commission_headers.quota_id%TYPE,
331 srp_plan_assign_id cn_commission_headers.srp_plan_assign_id%TYPE,
332 revenue_class_id cn_commission_headers.revenue_class_id%TYPE,
333 role_id cn_commission_headers.role_id%TYPE,
334 comp_group_id cn_commission_headers.comp_group_id%TYPE,
335 commission_amount cn_commission_headers.commission_amount%TYPE,
336 trx_batch_id cn_commission_headers.trx_batch_id%TYPE,
337 reversal_flag cn_commission_headers.reversal_flag%TYPE,
338 reversal_header_id cn_commission_headers.reversal_header_id%TYPE,
339 reason_code cn_commission_headers.reason_code%TYPE,
340 comments cn_commission_headers.comments%TYPE,
341 attribute_category cn_commission_headers.attribute_category%TYPE,
342 attribute1 cn_commission_headers.attribute1%TYPE,
343 attribute2 cn_commission_headers.attribute2%TYPE,
344 attribute3 cn_commission_headers.attribute3%TYPE,
345 attribute4 cn_commission_headers.attribute4%TYPE,
346 attribute5 cn_commission_headers.attribute5%TYPE,
347 attribute6 cn_commission_headers.attribute6%TYPE,
348 attribute7 cn_commission_headers.attribute7%TYPE,
349 attribute8 cn_commission_headers.attribute8%TYPE,
350 attribute9 cn_commission_headers.attribute9%TYPE,
351 attribute10 cn_commission_headers.attribute10%TYPE,
352 attribute11 cn_commission_headers.attribute11%TYPE,
353 attribute12 cn_commission_headers.attribute12%TYPE,
354 attribute13 cn_commission_headers.attribute13%TYPE,
355 attribute14 cn_commission_headers.attribute14%TYPE,
356 attribute15 cn_commission_headers.attribute15%TYPE,
357 attribute16 cn_commission_headers.attribute16%TYPE,
358 attribute17 cn_commission_headers.attribute17%TYPE,
359 attribute18 cn_commission_headers.attribute18%TYPE,
360 attribute19 cn_commission_headers.attribute19%TYPE,
361 attribute20 cn_commission_headers.attribute20%TYPE,
362 attribute21 cn_commission_headers.attribute21%TYPE,
363 attribute22 cn_commission_headers.attribute22%TYPE,
364 attribute23 cn_commission_headers.attribute23%TYPE,
365 attribute24 cn_commission_headers.attribute24%TYPE,
366 attribute25 cn_commission_headers.attribute25%TYPE,
367 attribute26 cn_commission_headers.attribute26%TYPE,
368 attribute27 cn_commission_headers.attribute27%TYPE,
369 attribute28 cn_commission_headers.attribute28%TYPE,
370 attribute29 cn_commission_headers.attribute29%TYPE,
371 attribute30 cn_commission_headers.attribute30%TYPE,
372 attribute31 cn_commission_headers.attribute31%TYPE,
373 attribute32 cn_commission_headers.attribute32%TYPE,
374 attribute33 cn_commission_headers.attribute33%TYPE,
375 attribute34 cn_commission_headers.attribute34%TYPE,
376 attribute35 cn_commission_headers.attribute35%TYPE,
377 attribute36 cn_commission_headers.attribute36%TYPE,
378 attribute37 cn_commission_headers.attribute37%TYPE,
379 attribute38 cn_commission_headers.attribute38%TYPE,
380 attribute39 cn_commission_headers.attribute39%TYPE,
381 attribute40 cn_commission_headers.attribute40%TYPE,
382 attribute41 cn_commission_headers.attribute41%TYPE,
383 attribute42 cn_commission_headers.attribute42%TYPE,
384 attribute43 cn_commission_headers.attribute43%TYPE,
385 attribute44 cn_commission_headers.attribute44%TYPE,
386 attribute45 cn_commission_headers.attribute45%TYPE,
387 attribute46 cn_commission_headers.attribute46%TYPE,
388 attribute47 cn_commission_headers.attribute47%TYPE,
389 attribute48 cn_commission_headers.attribute48%TYPE,
390 attribute49 cn_commission_headers.attribute49%TYPE,
391 attribute50 cn_commission_headers.attribute50%TYPE,
392 attribute51 cn_commission_headers.attribute51%TYPE,
393 attribute52 cn_commission_headers.attribute52%TYPE,
394 attribute53 cn_commission_headers.attribute53%TYPE,
398 attribute57 cn_commission_headers.attribute57%TYPE,
395 attribute54 cn_commission_headers.attribute54%TYPE,
396 attribute55 cn_commission_headers.attribute55%TYPE,
397 attribute56 cn_commission_headers.attribute56%TYPE,
399 attribute58 cn_commission_headers.attribute58%TYPE,
400 attribute59 cn_commission_headers.attribute59%TYPE,
401 attribute60 cn_commission_headers.attribute60%TYPE,
402 attribute61 cn_commission_headers.attribute61%TYPE,
403 attribute62 cn_commission_headers.attribute62%TYPE,
404 attribute63 cn_commission_headers.attribute63%TYPE,
405 attribute64 cn_commission_headers.attribute64%TYPE,
406 attribute65 cn_commission_headers.attribute65%TYPE,
407 attribute66 cn_commission_headers.attribute66%TYPE,
408 attribute67 cn_commission_headers.attribute67%TYPE,
409 attribute68 cn_commission_headers.attribute68%TYPE,
410 attribute69 cn_commission_headers.attribute69%TYPE,
411 attribute70 cn_commission_headers.attribute70%TYPE,
412 attribute71 cn_commission_headers.attribute71%TYPE,
413 attribute72 cn_commission_headers.attribute72%TYPE,
414 attribute73 cn_commission_headers.attribute73%TYPE,
415 attribute74 cn_commission_headers.attribute74%TYPE,
416 attribute75 cn_commission_headers.attribute75%TYPE,
417 attribute76 cn_commission_headers.attribute76%TYPE,
418 attribute77 cn_commission_headers.attribute77%TYPE,
419 attribute78 cn_commission_headers.attribute78%TYPE,
420 attribute79 cn_commission_headers.attribute79%TYPE,
421 attribute80 cn_commission_headers.attribute80%TYPE,
422 attribute81 cn_commission_headers.attribute81%TYPE,
423 attribute82 cn_commission_headers.attribute82%TYPE,
424 attribute83 cn_commission_headers.attribute83%TYPE,
425 attribute84 cn_commission_headers.attribute84%TYPE,
426 attribute85 cn_commission_headers.attribute85%TYPE,
427 attribute86 cn_commission_headers.attribute86%TYPE,
428 attribute87 cn_commission_headers.attribute87%TYPE,
429 attribute88 cn_commission_headers.attribute88%TYPE,
430 attribute89 cn_commission_headers.attribute89%TYPE,
431 attribute90 cn_commission_headers.attribute90%TYPE,
432 attribute91 cn_commission_headers.attribute91%TYPE,
433 attribute92 cn_commission_headers.attribute92%TYPE,
434 attribute93 cn_commission_headers.attribute93%TYPE,
435 attribute94 cn_commission_headers.attribute94%TYPE,
436 attribute95 cn_commission_headers.attribute95%TYPE,
437 attribute96 cn_commission_headers.attribute96%TYPE,
438 attribute97 cn_commission_headers.attribute97%TYPE,
439 attribute98 cn_commission_headers.attribute98%TYPE,
440 attribute99 cn_commission_headers.attribute99%TYPE,
441 attribute100 cn_commission_headers.attribute100%TYPE,
442 last_update_date cn_commission_headers.last_update_date%TYPE,
443 last_updated_by cn_commission_headers.last_updated_by%TYPE,
444 last_update_login cn_commission_headers.last_update_login%TYPE,
445 creation_date cn_commission_headers.creation_date%TYPE,
446 created_by cn_commission_headers.created_by%TYPE,
447 org_id cn_commission_headers.org_id%TYPE,
448 exchange_rate cn_commission_headers.exchange_rate%TYPE,
449 forecast_id cn_commission_headers.forecast_id%TYPE,
450 upside_quantity cn_commission_headers.upside_quantity%TYPE,
451 upside_amount cn_commission_headers.upside_amount%TYPE,
452 uom_code cn_commission_headers.uom_code%TYPE,
453 source_trx_id cn_commission_headers.source_trx_id%TYPE,
454 source_trx_line_id cn_commission_headers.source_trx_line_id%TYPE,
455 source_trx_sales_line_id cn_commission_headers.source_trx_sales_line_id%TYPE,
456 negated_flag cn_commission_headers.negated_flag%TYPE,
457 customer_id cn_commission_headers.customer_id%TYPE,
458 inventory_item_id cn_commission_headers.inventory_item_id%TYPE,
459 order_number cn_commission_headers.order_number%TYPE,
460 booked_date cn_commission_headers.booked_date%TYPE,
461 invoice_number cn_commission_headers.invoice_number%TYPE,
462 invoice_date cn_commission_headers.invoice_date%TYPE,
463 bill_to_address_id cn_commission_headers.bill_to_address_id%TYPE,
464 ship_to_address_id cn_commission_headers.ship_to_address_id%TYPE,
465 bill_to_contact_id cn_commission_headers.bill_to_contact_id%TYPE,
466 ship_to_contact_id cn_commission_headers.ship_to_contact_id%TYPE,
467 adj_comm_lines_api_id cn_commission_headers.adj_comm_lines_api_id%TYPE,
468 adjust_date cn_commission_headers.adjust_date%TYPE,
469 adjusted_by cn_commission_headers.adjusted_by%TYPE,
470 revenue_type cn_commission_headers.revenue_type%TYPE,
471 adjust_rollup_flag cn_commission_headers.adjust_rollup_flag%TYPE,
472 adjust_comments cn_commission_headers.adjust_comments%TYPE,
473 adjust_status cn_commission_headers.adjust_status%TYPE,
474 line_number cn_commission_headers.line_number%TYPE,
475 request_id cn_commission_headers.request_id%TYPE,
476 program_id cn_commission_headers.program_id%TYPE,
477 program_application_id cn_commission_headers.program_application_id%TYPE,
478 program_update_date cn_commission_headers.program_update_date%TYPE,
479 type cn_commission_headers.type%TYPE,
480 sales_channel cn_commission_headers.sales_channel%TYPE,
481 object_version_number cn_commission_headers.object_version_number%TYPE,
485 direct_salesrep_name cn_salesreps.name%TYPE,
482 split_pct cn_commission_headers.split_pct%TYPE,
483 split_status cn_commission_headers.split_status%TYPE,
484 direct_salesrep_number cn_salesreps.employee_number%TYPE,
486 period_name cn_period_statuses.period_name%TYPE,
487 status_disp cn_lookups.meaning%TYPE,
488 trx_type_disp cn_lookups.meaning%TYPE,
489 reason cn_lookups.meaning%TYPE,
490 revenue_class_name cn_revenue_classes.name%TYPE,
491 revenue_type_disp cn_lookups.meaning%TYPE,
492 adjust_status_disp cn_lookups.meaning%TYPE,
493 trx_batch_name cn_trx_batches.trx_batch_name%TYPE,
494 terr_id cn_comm_lines_api.terr_id%TYPE,
495 preserve_credit_override_flag cn_comm_lines_api.preserve_credit_override_flag%TYPE
496 );
497 l_header_rec header_rec;
498 -- Local Variables
499 l_api_sql VARCHAR2(10000);
500 l_header_sql VARCHAR2(10000);
501 l_api_query_flag CHAR(1) := 'Y';
502 l_header_query_flag CHAR(1) := 'Y';
503 l_source_counter NUMBER := 0;
504 l_salesrep_id NUMBER;
505 l_pr_date_from DATE;
506 l_pr_date_to DATE;
507 l_invoice_num VARCHAR2(20);
508 l_order_num NUMBER;
509
510 --Added for Crediting
511 l_terr_id NUMBER;
512 l_keep_flag VARCHAR2(1);
513
514 -- Tables/Records definitions
515 l_adj_tbl cn_get_tx_data_pub.adj_tbl_type;
516 adj cn_get_tx_data_pub.adj_rec_type;
517 l_attribute_tbl cn_get_tx_data_pub.attribute_tbl;
518 -- Defining REF CURSOR
519 TYPE rc IS REF CURSOR;
520 query_cur rc;
521
522 cursor get_inv_details(p_comm_lines_api_id NUMBER)
523 is
524 select trx_id, trx_line_id, trx_sales_line_id
525 from cn_comm_lines_api
526 where comm_lines_api_id = p_comm_lines_api_id;
527
528 BEGIN
529 IF (p_salesrep_id <> FND_API.G_MISS_NUM) THEN
530 l_salesrep_id := p_salesrep_id;
531 ELSE
532 l_salesrep_id := null;
533 END IF;
534 IF (p_pr_date_from <> FND_API.G_MISS_DATE) THEN
535 l_pr_date_from := p_pr_date_from;
536 ELSE
537 l_pr_date_from := null;
538 END IF;
539 IF (p_pr_date_to <> FND_API.G_MISS_DATE) THEN
540 l_pr_date_to := p_pr_date_to;
541 ELSE
542 l_pr_date_to := null;
543 END IF;
544 IF (p_invoice_num <> FND_API.G_MISS_CHAR) THEN
545 l_invoice_num := p_invoice_num;
546 ELSE
547 l_invoice_num := null;
548 END IF;
549 IF (p_order_num <> FND_API.G_MISS_NUM) THEN
550 l_order_num := p_order_num;
551 ELSE
552 l_order_num := null;
553 END IF;
554
555 l_header_sql :=
556 'SELECT '||
557 'CCH.commission_header_id,CCH.direct_salesrep_id,CCH.processed_date, '||
558 'CCH.processed_period_id,CCH.rollup_date,CCH.transaction_amount, '||
559 'CCH.quantity,CCH.discount_percentage,CCH.margin_percentage, '||
560 'CCH.orig_currency_code,CCH.transaction_amount_orig, '||
561 'CCH.trx_type,CCH.status,CCH.pre_processed_code,CCH.comm_lines_api_id, '||
562 'CCH.source_doc_type,CCH.source_trx_number,CCH.quota_id, '||
563 'CCH.srp_plan_assign_id,CCH.revenue_class_id,CCH.role_id, '||
564 'CCH.comp_group_id,CCH.commission_amount,CCH.trx_batch_id, '||
565 'CCH.reversal_flag,CCH.reversal_header_id,CCH.reason_code, '||
566 'CCH.comments,CCH.attribute_category, '||
567 'CCH.attribute1,CCH.attribute2,CCH.attribute3,CCH.attribute4,CCH.attribute5, '||
568 'CCH.attribute6,CCH.attribute7,CCH.attribute8,CCH.attribute9,CCH.attribute10, '||
569 'CCH.attribute11,CCH.attribute12,CCH.attribute13,CCH.attribute14,CCH.attribute15, '||
570 'CCH.attribute16,CCH.attribute17,CCH.attribute18,CCH.attribute19,CCH.attribute20, '||
571 'CCH.attribute21,CCH.attribute22,CCH.attribute23,CCH.attribute24,CCH.attribute25, '||
572 'CCH.attribute26,CCH.attribute27,CCH.attribute28,CCH.attribute29,CCH.attribute30, '||
573 'CCH.attribute31,CCH.attribute32,CCH.attribute33,CCH.attribute34,CCH.attribute35, '||
574 'CCH.attribute36,CCH.attribute37,CCH.attribute38,CCH.attribute39,CCH.attribute40, '||
575 'CCH.attribute41,CCH.attribute42,CCH.attribute43,CCH.attribute44,CCH.attribute45, '||
576 'CCH.attribute46,CCH.attribute47,CCH.attribute48,CCH.attribute49,CCH.attribute50, '||
577 'CCH.attribute51,CCH.attribute52,CCH.attribute53,CCH.attribute54,CCH.attribute55, '||
578 'CCH.attribute56,CCH.attribute57,CCH.attribute58,CCH.attribute59,CCH.attribute60, '||
579 'CCH.attribute61,CCH.attribute62,CCH.attribute63,CCH.attribute64,CCH.attribute65, '||
580 'CCH.attribute66,CCH.attribute67,CCH.attribute68,CCH.attribute69,CCH.attribute70, '||
581 'CCH.attribute71,CCH.attribute72,CCH.attribute73,CCH.attribute74,CCH.attribute75, '||
582 'CCH.attribute76,CCH.attribute77,CCH.attribute78,CCH.attribute79,CCH.attribute80, '||
583 'CCH.attribute81,CCH.attribute82,CCH.attribute83,CCH.attribute84,CCH.attribute85, '||
584 'CCH.attribute86,CCH.attribute87,CCH.attribute88,CCH.attribute89,CCH.attribute90, '||
585 'CCH.attribute91,CCH.attribute92,CCH.attribute93,CCH.attribute94,CCH.attribute95, '||
586 'CCH.attribute96,CCH.attribute97,CCH.attribute98,CCH.attribute99,CCH.attribute100, '||
587 'CCH.last_update_date,CCH.last_updated_by,CCH.last_update_login, '||
588 'CCH.creation_date,CCH.created_by,CCH.org_id,CCH.exchange_rate, '||
589 'CCH.forecast_id,CCH.upside_quantity,CCH.upside_amount, '||
590 'CCH.uom_code,CCH.source_trx_id,CCH.source_trx_line_id, '||
594 'CCH.ship_to_address_id,CCH.bill_to_contact_id,CCH.ship_to_contact_id, '||
591 'CCH.source_trx_sales_line_id,CCH.negated_flag,CCH.customer_id, '||
592 'CCH.inventory_item_id,CCH.order_number,CCH.booked_date, '||
593 'CCH.invoice_number,CCH.invoice_date,CCH.bill_to_address_id, '||
595 'CCH.adj_comm_lines_api_id,CCH.adjust_date,CCH.adjusted_by, '||
596 'CCH.revenue_type,CCH.adjust_rollup_flag,CCH.adjust_comments, '||
597 'CCH.adjust_status,CCH.line_number,CCH.request_id,CCH.program_id, '||
598 'CCH.program_application_id,CCH.program_update_date,CCH.type, '||
599 'CCH.sales_channel,CCH.object_version_number,CCH.split_pct, CCH.split_status, '||
600 'RSD.employee_number direct_salesrep_number, '||
601 'RSD.name direct_salesrep_name, CPSP.period_name, '||
602 'CLS.meaning status_disp ,CLT.meaning trx_type_disp, '||
603 'CLR.meaning reason, CNR.name revenue_class_name, '||
604 'CLRV.meaning revenue_type_disp, CLAD.meaning adjust_status_disp, '||
605 'CTB.trx_batch_name, '||
606 --Added for Crediting bug
607 'API.terr_id, API.preserve_credit_override_flag '||
608 'FROM cn_period_statuses CPSP, cn_salesreps RSD, cn_commission_headers_all CCH, '||
609 'cn_lookups CLT, cn_lookups CLR, cn_lookups CLS, cn_lookups CLRV, '||
610 'cn_lookups CLAD, cn_revenue_classes CNR, cn_quotas CQ, cn_trx_batches CTB, '||
611 -- Modified for Crediting Bug
612 'cn_comm_lines_api API '||
613 'WHERE CCH.direct_salesrep_id = RSD.salesrep_id '||
614 --Modified for Crediting Bug
615 'AND CCH.comm_lines_api_id = API.comm_lines_api_id(+)'||
616 'AND CCH.org_id = API.org_id(+)'||
617 'AND CCH.processed_period_id = CPSP.period_id '||
618 'AND CCH.status = CLS.lookup_code(+) '||
619 'AND CLS.lookup_type (+)= ''TRX_STATUS'' '||
620 'AND CCH.reason_code = CLR.lookup_code(+) '||
621 'AND CLR.lookup_type (+)= ''ADJUSTMENT_REASON'' '||
622 'AND CCH.trx_type = CLT.lookup_code(+) '||
623 'AND CLT.lookup_type (+)= ''TRX TYPES'' '||
624 'AND CCH.revenue_type = CLRV.lookup_code (+) '||
625 'AND CLRV.lookup_type (+) = ''REVENUE_TYPE'' '||
626 'AND CCH.adjust_status = CLAD.lookup_code (+) '||
627 'AND CLAD.lookup_type (+) = ''ADJUST_STATUS'' '||
628 'AND CCH.quota_id = CQ.quota_id(+) '||
629 'AND CCH.revenue_class_id = CNR.revenue_class_id(+) '||
630 'AND CCH.trx_batch_id = CTB.trx_batch_id(+) '||
631 'AND CPSP.ORG_ID = CCH.ORG_ID '||
632 'AND RSD.ORG_ID = CCH.ORG_id '||
633 'AND CNR.ORG_ID(+) = CCH.ORG_id '||
634 'AND CQ.ORG_ID(+) = CCH.ORG_id '||
635 'AND CTB.ORG_ID(+) = CCH.ORG_id ';
636 -- Column Mappings
637 -- RSD.employee_number -> direct_salesrep_number
638 -- RSD.name -> direct_salesrep_name
639 -- CPSP.period_name -> processed_period
640 -- CLT.meaning -> trx_type_disp
641 -- CLT.lookup_code -> trx_type
642 -- CLR.meaning -> reason
643 -- CLR.lookup_code -> reason_code
644 -- CNR.name -> revenue_class_name
645 -- CLRV.meaning -> revenue_type_disp
646 -- CLAD.meaning -> adjust_status_disp
647 l_api_sql :=
648 'SELECT '||
649 'CCLA.salesrep_id,CCLA.processed_date,CCLA.processed_period_id,CCLA.transaction_amount, '||
650 'CCLA.trx_type,CCLA.revenue_class_id,CCLA.load_status,CCLA.attribute_category, '||
651 'CCLA.attribute1,CCLA.attribute2,CCLA.attribute3,CCLA.attribute4,CCLA.attribute5, '||
652 'CCLA.attribute6,CCLA.attribute7,CCLA.attribute8,CCLA.attribute9,CCLA.attribute10, '||
653 'CCLA.attribute11,CCLA.attribute12,CCLA.attribute13,CCLA.attribute14,CCLA.attribute15, '||
654 'CCLA.attribute16,CCLA.attribute17,CCLA.attribute18,CCLA.attribute19,CCLA.attribute20, '||
655 'CCLA.attribute21,CCLA.attribute22,CCLA.attribute23,CCLA.attribute24,CCLA.attribute25, '||
656 'CCLA.attribute26,CCLA.attribute27,CCLA.attribute28,CCLA.attribute29,CCLA.attribute30, '||
657 'CCLA.attribute31,CCLA.attribute32,CCLA.attribute33,CCLA.attribute34,CCLA.attribute35, '||
658 'CCLA.attribute36,CCLA.attribute37,CCLA.attribute38,CCLA.attribute39,CCLA.attribute40, '||
659 'CCLA.attribute41,CCLA.attribute42,CCLA.attribute43,CCLA.attribute44,CCLA.attribute45, '||
660 'CCLA.attribute46,CCLA.attribute47,CCLA.attribute48,CCLA.attribute49,CCLA.attribute50, '||
661 'CCLA.attribute51,CCLA.attribute52,CCLA.attribute53,CCLA.attribute54,CCLA.attribute55, '||
662 'CCLA.attribute56,CCLA.attribute57,CCLA.attribute58,CCLA.attribute59,CCLA.attribute60, '||
663 'CCLA.attribute61,CCLA.attribute62,CCLA.attribute63,CCLA.attribute64,CCLA.attribute65, '||
664 'CCLA.attribute66,CCLA.attribute67,CCLA.attribute68,CCLA.attribute69,CCLA.attribute70, '||
665 'CCLA.attribute71,CCLA.attribute72,CCLA.attribute73,CCLA.attribute74,CCLA.attribute75, '||
666 'CCLA.attribute76,CCLA.attribute77,CCLA.attribute78,CCLA.attribute79,CCLA.attribute80, '||
667 'CCLA.attribute81,CCLA.attribute82,CCLA.attribute83,CCLA.attribute84,CCLA.attribute85, '||
668 'CCLA.attribute86,CCLA.attribute87,CCLA.attribute88,CCLA.attribute89,CCLA.attribute90, '||
669 'CCLA.attribute91,CCLA.attribute92,CCLA.attribute93,CCLA.attribute94,CCLA.attribute95, '||
670 'CCLA.attribute96,CCLA.attribute97,CCLA.attribute98,CCLA.attribute99,CCLA.attribute100, '||
671 'CCLA.comm_lines_api_id,CCLA.conc_batch_id,CCLA.process_batch_id,CCLA.salesrep_number, '||
672 'CCLA.rollup_date,CCLA.source_doc_id,CCLA.source_doc_type,CCLA.created_by, '||
673 'CCLA.creation_date,CCLA.last_updated_by,CCLA.last_update_date,CCLA.last_update_login, '||
674 'CCLA.transaction_currency_code,CCLA.exchange_rate,CCLA.acctd_transaction_amount, '||
678 'CCLA.source_trx_sales_line_id,CCLA.negated_flag,CCLA.customer_id,CCLA.inventory_item_id, '||
675 'CCLA.trx_id,CCLA.trx_line_id,CCLA.trx_sales_line_id,CCLA.org_id,CCLA.quantity, '||
676 'CCLA.source_trx_number, CCLA.discount_percentage,CCLA.margin_percentage, '||
677 'CCLA.source_trx_id,CCLA.source_trx_line_id, '||
679 'CCLA.order_number,CCLA.booked_date,CCLA.invoice_number,CCLA.invoice_date,CCLA.adjust_date, '||
680 'CCLA.adjusted_by,CCLA.revenue_type,CCLA.adjust_rollup_flag,CCLA.adjust_comments, '||
681 'CCLA.adjust_status,CCLA.line_number,CCLA.bill_to_address_id,CCLA.ship_to_address_id, '||
682 'CCLA.bill_to_contact_id,CCLA.ship_to_contact_id,CCLA.adj_comm_lines_api_id, '||
683 'CCLA.pre_defined_rc_flag,CCLA.rollup_flag,CCLA.forecast_id,CCLA.upside_quantity, '||
684 'CCLA.upside_amount,CCLA.uom_code,CCLA.reason_code,CCLA.type,CCLA.pre_processed_code, '||
685 'CCLA.quota_id,CCLA.srp_plan_assign_id,CCLA.role_id,CCLA.comp_group_id, '||
686 'CCLA.commission_amount,CCLA.employee_number,CCLA.reversal_flag,CCLA.reversal_header_id, '||
687 'CCLA.sales_channel,CCLA.object_version_number,CCLA.split_pct,CCLA.split_status, '||
688 'RSD.employee_number, '||
689 'RSD.name, CPSP.period_name, CLT.meaning trx_type_disp, '||
690 'CLR.meaning reason, CNR.name revenue_class_name, '||
691 'CLRV.meaning revenue_type_disp, CLAD.meaning adjust_status_disp, '||
692 --Modified for Crediting Bug
693 'CCLA.terr_id, CCLA.preserve_credit_override_flag '||
694 'FROM cn_comm_lines_api_all CCLA, '||
695 'cn_period_statuses CPSP, cn_salesreps RSD, '||
696 'cn_revenue_classes CNR, cn_lookups CLT, '||
697 'cn_lookups CLRV, cn_lookups CLAD,cn_lookups CLR '||
698 'WHERE RSD.salesrep_id = CCLA.salesrep_id '||
699 'AND CCLA.processed_period_id = CPSP.period_id '||
700 'AND CCLA.revenue_class_id = CNR.revenue_class_id(+) '||
701 'AND CCLA.reason_code = CLR.lookup_code(+) '||
702 'AND CLR.lookup_type (+)= ''ADJUSTMENT_REASON'' '||
703 'AND CCLA.trx_type = CLT.lookup_code '||
704 'AND CLT.lookup_type = ''TRX TYPES'' '||
705 'AND CCLA.revenue_type = CLRV.lookup_code(+) '||
706 'AND CLRV.lookup_type (+)= ''REVENUE_TYPE'' '||
707 'AND CCLA.adjust_status = CLAD.lookup_code(+) '||
708 'AND CLAD.lookup_type (+)= ''ADJUST_STATUS'' '||
709 'AND nvl(CCLA.load_status,''X'') <> ''LOADED'' '||
710 'AND CPSP.ORG_ID = CCLA.ORG_ID '||
711 'AND RSD.ORG_ID = CCLA.ORG_ID '||
712 'AND CNR.ORG_ID(+) = CCLA.ORG_id ';
713
714 IF (p_salesrep_id <> FND_API.G_MISS_NUM) THEN
715 l_header_sql := l_header_sql|| ' AND CCH.direct_salesrep_id = :p_salesrep_id';
716 l_api_sql := l_api_sql || ' AND CCLA.salesrep_id = :p_salesrep_id';
717 ELSE
718 --l_header_sql := l_header_sql|| ' AND :p_salesrep_id = '||FND_API.G_MISS_NUM;
719 --l_api_sql := l_api_sql|| ' AND :p_salesrep_id = '||FND_API.G_MISS_NUM;
720 l_header_sql := l_header_sql|| ' AND :p_salesrep_id IS NULL ';
721 l_api_sql := l_api_sql|| ' AND :p_salesrep_id IS NULL ';
722 END IF;
723
724 IF (p_org_id <> FND_API.G_MISS_NUM) THEN
725 l_header_sql := l_header_sql|| ' AND CCH.org_id = :p_org_id';
726 l_api_sql := l_api_sql || ' AND CCLA.org_id = :p_org_id';
727 ELSE
728 l_header_sql := l_header_sql|| ' AND :p_org_id = '||FND_API.G_MISS_NUM;
729 l_api_sql := l_api_sql|| ' AND :p_org_id = '||FND_API.G_MISS_NUM;
730 END IF;
731
732 --
733
734 --Added for Crediting
735 l_terr_id := p_srch_attr_rec.terr_id;
736 IF (l_terr_id = 0) THEN
737 l_header_sql := l_header_sql|| ' AND API.terr_id is not null';
738 l_api_sql := l_api_sql || ' AND CCLA.terr_id is not null';
739 END IF;
740 IF (l_terr_id = 1) THEN
741 l_header_sql := l_header_sql|| ' AND API.terr_id is null';
742 l_api_sql := l_api_sql || ' AND CCLA.terr_id is null';
743 END IF;
744
745 l_keep_flag := p_srch_attr_rec.preserve_credit_override_flag;
746 IF (l_keep_flag <> FND_API.G_MISS_CHAR AND l_keep_flag IS NOT NULL) THEN
747 IF (l_keep_flag = 'Y')
748 THEN
749 l_header_sql := l_header_sql|| ' AND API.preserve_credit_override_flag = :l_keep_flag';
750 l_api_sql := l_api_sql || ' AND CCLA.preserve_credit_override_flag = :l_keep_flag';
751 END IF;
752 IF (l_keep_flag = 'N')
753 THEN
754 l_header_sql := l_header_sql|| ' AND NVL(API.preserve_credit_override_flag, ''N'') = :l_keep_flag';
755 l_api_sql := l_api_sql || ' AND NVL(CCLA.preserve_credit_override_flag, ''N'') = :l_keep_flag';
756 END IF;
757 ELSE
758 l_header_sql := l_header_sql|| ' AND :l_keep_flag IS NULL';
759 l_api_sql := l_api_sql || ' AND :l_keep_flag IS NULL';
760 END IF;
761
762
763 IF (p_pr_date_from <> FND_API.G_MISS_DATE) THEN
764 l_header_sql := l_header_sql|| ' AND trunc(CCH.processed_date) >= :l_pr_date_from';
765 l_api_sql := l_api_sql || ' AND trunc(CCLA.processed_date) >= :l_pr_date_from';
766 ELSE
767 l_header_sql := l_header_sql|| ' AND :l_pr_date_from IS NULL';
768 l_api_sql := l_api_sql|| ' AND :l_pr_date_from IS NULL';
769 END IF;
770 --
771 IF (p_pr_date_to <> FND_API.G_MISS_DATE) THEN
772 l_header_sql := l_header_sql|| ' AND trunc(CCH.processed_date) <= :l_pr_date_to';
773 l_api_sql := l_api_sql || ' AND trunc(CCLA.processed_date) <= :l_pr_date_to';
774 ELSE
775 l_header_sql := l_header_sql|| ' AND :l_pr_date_to IS NULL';
779 IF (p_invoice_num <> FND_API.G_MISS_CHAR) THEN
776 l_api_sql := l_api_sql|| ' AND :l_pr_date_to IS NULL';
777 END IF;
778 --
780 l_header_sql := l_header_sql|| ' AND CCH.invoice_number LIKE :l_invoice_num';
781 l_api_sql := l_api_sql || ' AND CCLA.invoice_number LIKE :l_invoice_num';
782 ELSE
783 l_header_sql := l_header_sql|| ' AND :l_invoice_num IS NULL';
784 l_api_sql := l_api_sql|| ' AND :l_invoice_num IS NULL';
785 END IF;
786 --
787 IF (p_order_num <> FND_API.G_MISS_NUM) THEN
788 l_header_sql := l_header_sql|| ' AND CCH.order_number = :p_order_num';
789 l_api_sql := l_api_sql || ' AND CCLA.order_number = :p_order_num';
790 ELSE
791 l_header_sql := l_header_sql|| ' AND :p_order_num IS NULL';
792 l_api_sql := l_api_sql|| ' AND :p_order_num IS NULL';
793 END IF;
794 --
795 convert_rec_to_tbl(
796 p_srch_attr_rec => p_srch_attr_rec,
797 x_attribute_tbl => l_attribute_tbl);
798 --
799 IF (l_attribute_tbl.COUNT > 0) THEN
800 FOR i IN l_attribute_tbl.FIRST..l_attribute_tbl.LAST
801 LOOP
802 IF (l_attribute_tbl(i).attribute_name IS NOT NULL) THEN
803 l_header_sql := l_header_sql||' AND CCH.'||
804 l_attribute_tbl(i).attribute_name||' LIKE '''||
805 l_attribute_tbl(i).attribute_value||'''';
806 l_api_sql := l_api_sql||' AND CCLA.'||
807 l_attribute_tbl(i).attribute_name||' LIKE '''||
808 l_attribute_tbl(i).attribute_value||'''';
809 END IF;
810 END LOOP;
811 END IF;
812 --
813
814 IF (p_calc_status <> 'ALL') THEN
815 l_header_sql := l_header_sql|| ' AND CCH.status = :p_calc_status';
816 l_api_query_flag := 'N';
817 ELSE
818 l_header_sql := l_header_sql|| ' and :p_calc_status = ''ALL''';
819 l_api_query_flag := 'Y';
820 END IF;
821 IF (p_adj_status <> 'ALL') THEN
822 l_header_sql := l_header_sql|| ' AND CCH.adjust_status = :p_adj_status';
823 l_api_sql := l_api_sql || ' AND CCLA.adjust_status = :p_adj_status';
824 ELSE
825 l_header_sql := l_header_sql|| ' and :p_adj_status = ''ALL''';
826 l_api_sql := l_api_sql || ' and :p_adj_status = ''ALL''';
827 END IF;
828 IF ((p_load_status <> 'ALL') AND (p_load_status <> 'LOADED')) THEN
829 l_header_query_flag := 'N';
830 l_api_sql := l_api_sql || ' AND CCLA.load_status = :p_load_status';
831 ELSIF ((p_load_status <> 'ALL') AND (p_load_status = 'LOADED')) THEN
832 l_header_query_flag := 'Y';
833 l_api_query_flag := 'N';
834 ELSE
835 l_header_query_flag := 'Y';
836 l_api_sql := l_api_sql || ' and :p_load_status = ''ALL''';
837 END IF;
838 -- Processing Attribute Columns
839 --CN_mydebug.ADD('Header SQL : '||substr(l_header_sql,1,3500));
840 --CN_mydebug.ADD('Header SQL : '||substr(l_header_sql,3500,7000));
841 --CN_mydebug.ADD('Header SQL : '||substr(l_header_sql,7000,1000));
842
843 --CN_mydebug.ADD('API SQL : '||substr(l_api_sql,1,3500));
844 --CN_mydebug.ADD('API SQL : '||substr(l_api_sql,3500,7000));
845 --CN_mydebug.ADD('API SQL : '||substr(l_api_sql,7000,1000));
846
847 IF (l_header_query_flag = 'Y') THEN
848 OPEN query_cur FOR l_header_sql
849 USING
850 l_salesrep_id,
851 p_org_id,
852 l_keep_flag,
853 l_pr_date_from,
854 l_pr_date_to,
855 l_invoice_num,
856 l_order_num,
857 p_calc_status,
858 p_adj_status;
859 LOOP
860 FETCH query_cur INTO l_header_rec;
861 EXIT WHEN query_cur%NOTFOUND;
862 l_source_counter := l_source_counter + 1;
863 l_adj_tbl(l_source_counter).commission_header_id := l_header_rec.commission_header_id;
864 l_adj_tbl(l_source_counter).direct_salesrep_number := l_header_rec.direct_salesrep_number;
865 l_adj_tbl(l_source_counter).direct_salesrep_name := l_header_rec.direct_salesrep_name;
866 l_adj_tbl(l_source_counter).direct_salesrep_id := l_header_rec.direct_salesrep_id;
867 l_adj_tbl(l_source_counter).processed_period_id := l_header_rec.processed_period_id;
868 l_adj_tbl(l_source_counter).processed_period := l_header_rec.period_name;
869 l_adj_tbl(l_source_counter).processed_date := l_header_rec.processed_date;
870 l_adj_tbl(l_source_counter).rollup_date := l_header_rec.rollup_date;
871 /* In this record type transaction_amount corresponds to functional amount
872 and transaction_amount_orig corresponds to foreign amount. In the header table
873 functional amount is stored in the transaction_amount and foreign amount is
874 stored in the transaction_amount_orig column. check the API record for
875 more information */
876 l_adj_tbl(l_source_counter).transaction_amount := l_header_rec.transaction_amount;
877 l_adj_tbl(l_source_counter).transaction_amount_orig := l_header_rec.transaction_amount_orig;
878 l_adj_tbl(l_source_counter).quantity := l_header_rec.quantity;
879 l_adj_tbl(l_source_counter).discount_percentage := l_header_rec.discount_percentage;
880 l_adj_tbl(l_source_counter).margin_percentage := l_header_rec.margin_percentage;
881 l_adj_tbl(l_source_counter).orig_currency_code := l_header_rec.orig_currency_code;
882 l_adj_tbl(l_source_counter).exchange_rate := l_header_rec.exchange_rate;
883 l_adj_tbl(l_source_counter).status_disp := l_header_rec.status_disp;
884 l_adj_tbl(l_source_counter).status := l_header_rec.status;
885 l_adj_tbl(l_source_counter).trx_type_disp := l_header_rec.trx_type_disp;
886 l_adj_tbl(l_source_counter).trx_type := l_header_rec.trx_type;
890 l_adj_tbl(l_source_counter).trx_batch_id := l_header_rec.trx_batch_id;
887 l_adj_tbl(l_source_counter).reason := l_header_rec.reason;
888 l_adj_tbl(l_source_counter).reason_code := l_header_rec.reason_code;
889 l_adj_tbl(l_source_counter).comments := l_header_rec.comments;
891 l_adj_tbl(l_source_counter).created_by := l_header_rec.created_by;
892 l_adj_tbl(l_source_counter).creation_date := l_header_rec.creation_date;
893 l_adj_tbl(l_source_counter).last_updated_by := l_header_rec.last_updated_by;
894 l_adj_tbl(l_source_counter).last_update_login := l_header_rec.last_update_login;
895 l_adj_tbl(l_source_counter).last_update_date := l_header_rec.last_update_date;
896 l_adj_tbl(l_source_counter).attribute_category := l_header_rec.attribute_category;
897 l_adj_tbl(l_source_counter).attribute1 := l_header_rec.attribute1;
898 l_adj_tbl(l_source_counter).attribute2 := l_header_rec.attribute2;
899 l_adj_tbl(l_source_counter).attribute3 := l_header_rec.attribute3;
900 l_adj_tbl(l_source_counter).attribute4 := l_header_rec.attribute4;
901 l_adj_tbl(l_source_counter).attribute5 := l_header_rec.attribute5;
902 l_adj_tbl(l_source_counter).attribute6 := l_header_rec.attribute6;
903 l_adj_tbl(l_source_counter).attribute7 := l_header_rec.attribute7;
904 l_adj_tbl(l_source_counter).attribute8 := l_header_rec.attribute8;
905 l_adj_tbl(l_source_counter).attribute9 := l_header_rec.attribute9;
906 l_adj_tbl(l_source_counter).attribute10 := l_header_rec.attribute10;
907 l_adj_tbl(l_source_counter).attribute11 := l_header_rec.attribute11;
908 l_adj_tbl(l_source_counter).attribute12 := l_header_rec.attribute12;
909 l_adj_tbl(l_source_counter).attribute13 := l_header_rec.attribute13;
910 l_adj_tbl(l_source_counter).attribute14 := l_header_rec.attribute14;
911 l_adj_tbl(l_source_counter).attribute15 := l_header_rec.attribute15;
912 l_adj_tbl(l_source_counter).attribute16 := l_header_rec.attribute16;
913 l_adj_tbl(l_source_counter).attribute17 := l_header_rec.attribute17;
914 l_adj_tbl(l_source_counter).attribute18 := l_header_rec.attribute18;
915 l_adj_tbl(l_source_counter).attribute19 := l_header_rec.attribute19;
916 l_adj_tbl(l_source_counter).attribute20 := l_header_rec.attribute20;
917 l_adj_tbl(l_source_counter).attribute21 := l_header_rec.attribute21;
918 l_adj_tbl(l_source_counter).attribute22 := l_header_rec.attribute22;
919 l_adj_tbl(l_source_counter).attribute23 := l_header_rec.attribute23;
920 l_adj_tbl(l_source_counter).attribute24 := l_header_rec.attribute24;
921 l_adj_tbl(l_source_counter).attribute25 := l_header_rec.attribute25;
922 l_adj_tbl(l_source_counter).attribute26 := l_header_rec.attribute26;
923 l_adj_tbl(l_source_counter).attribute27 := l_header_rec.attribute27;
924 l_adj_tbl(l_source_counter).attribute28 := l_header_rec.attribute28;
925 l_adj_tbl(l_source_counter).attribute29 := l_header_rec.attribute29;
926 l_adj_tbl(l_source_counter).attribute30 := l_header_rec.attribute30;
927 l_adj_tbl(l_source_counter).attribute31 := l_header_rec.attribute31;
928 l_adj_tbl(l_source_counter).attribute32 := l_header_rec.attribute32;
929 l_adj_tbl(l_source_counter).attribute33 := l_header_rec.attribute33;
930 l_adj_tbl(l_source_counter).attribute34 := l_header_rec.attribute34;
931 l_adj_tbl(l_source_counter).attribute35 := l_header_rec.attribute35;
932 l_adj_tbl(l_source_counter).attribute36 := l_header_rec.attribute36;
933 l_adj_tbl(l_source_counter).attribute37 := l_header_rec.attribute37;
934 l_adj_tbl(l_source_counter).attribute38 := l_header_rec.attribute38;
935 l_adj_tbl(l_source_counter).attribute39 := l_header_rec.attribute39;
936 l_adj_tbl(l_source_counter).attribute40 := l_header_rec.attribute40;
937 l_adj_tbl(l_source_counter).attribute41 := l_header_rec.attribute41;
938 l_adj_tbl(l_source_counter).attribute42 := l_header_rec.attribute42;
939 l_adj_tbl(l_source_counter).attribute43 := l_header_rec.attribute43;
940 l_adj_tbl(l_source_counter).attribute44 := l_header_rec.attribute44;
941 l_adj_tbl(l_source_counter).attribute45 := l_header_rec.attribute45;
942 l_adj_tbl(l_source_counter).attribute46 := l_header_rec.attribute46;
943 l_adj_tbl(l_source_counter).attribute47 := l_header_rec.attribute47;
944 l_adj_tbl(l_source_counter).attribute48 := l_header_rec.attribute48;
945 l_adj_tbl(l_source_counter).attribute49 := l_header_rec.attribute49;
946 l_adj_tbl(l_source_counter).attribute50 := l_header_rec.attribute50;
947 l_adj_tbl(l_source_counter).attribute51 := l_header_rec.attribute51;
948 l_adj_tbl(l_source_counter).attribute52 := l_header_rec.attribute52;
949 l_adj_tbl(l_source_counter).attribute53 := l_header_rec.attribute53;
950 l_adj_tbl(l_source_counter).attribute54 := l_header_rec.attribute54;
951 l_adj_tbl(l_source_counter).attribute55 := l_header_rec.attribute55;
952 l_adj_tbl(l_source_counter).attribute56 := l_header_rec.attribute56;
953 l_adj_tbl(l_source_counter).attribute57 := l_header_rec.attribute57;
954 l_adj_tbl(l_source_counter).attribute58 := l_header_rec.attribute58;
955 l_adj_tbl(l_source_counter).attribute59 := l_header_rec.attribute59;
956 l_adj_tbl(l_source_counter).attribute60 := l_header_rec.attribute60;
957 l_adj_tbl(l_source_counter).attribute61 := l_header_rec.attribute61;
958 l_adj_tbl(l_source_counter).attribute62 := l_header_rec.attribute62;
959 l_adj_tbl(l_source_counter).attribute63 := l_header_rec.attribute63;
963 l_adj_tbl(l_source_counter).attribute67 := l_header_rec.attribute67;
960 l_adj_tbl(l_source_counter).attribute64 := l_header_rec.attribute64;
961 l_adj_tbl(l_source_counter).attribute65 := l_header_rec.attribute65;
962 l_adj_tbl(l_source_counter).attribute66 := l_header_rec.attribute66;
964 l_adj_tbl(l_source_counter).attribute68 := l_header_rec.attribute68;
965 l_adj_tbl(l_source_counter).attribute69 := l_header_rec.attribute69;
966 l_adj_tbl(l_source_counter).attribute70 := l_header_rec.attribute70;
967 l_adj_tbl(l_source_counter).attribute71 := l_header_rec.attribute71;
968 l_adj_tbl(l_source_counter).attribute72 := l_header_rec.attribute72;
969 l_adj_tbl(l_source_counter).attribute73 := l_header_rec.attribute73;
970 l_adj_tbl(l_source_counter).attribute74 := l_header_rec.attribute74;
971 l_adj_tbl(l_source_counter).attribute75 := l_header_rec.attribute75;
972 l_adj_tbl(l_source_counter).attribute76 := l_header_rec.attribute76;
973 l_adj_tbl(l_source_counter).attribute77 := l_header_rec.attribute77;
974 l_adj_tbl(l_source_counter).attribute78 := l_header_rec.attribute78;
975 l_adj_tbl(l_source_counter).attribute79 := l_header_rec.attribute79;
976 l_adj_tbl(l_source_counter).attribute80 := l_header_rec.attribute80;
977 l_adj_tbl(l_source_counter).attribute81 := l_header_rec.attribute81;
978 l_adj_tbl(l_source_counter).attribute82 := l_header_rec.attribute82;
979 l_adj_tbl(l_source_counter).attribute83 := l_header_rec.attribute83;
980 l_adj_tbl(l_source_counter).attribute84 := l_header_rec.attribute84;
981 l_adj_tbl(l_source_counter).attribute85 := l_header_rec.attribute85;
982 l_adj_tbl(l_source_counter).attribute86 := l_header_rec.attribute86;
983 l_adj_tbl(l_source_counter).attribute87 := l_header_rec.attribute87;
984 l_adj_tbl(l_source_counter).attribute88 := l_header_rec.attribute88;
985 l_adj_tbl(l_source_counter).attribute89 := l_header_rec.attribute89;
986 l_adj_tbl(l_source_counter).attribute90 := l_header_rec.attribute90;
987 l_adj_tbl(l_source_counter).attribute91 := l_header_rec.attribute91;
988 l_adj_tbl(l_source_counter).attribute92 := l_header_rec.attribute92;
989 l_adj_tbl(l_source_counter).attribute93 := l_header_rec.attribute93;
990 l_adj_tbl(l_source_counter).attribute94 := l_header_rec.attribute94;
991 l_adj_tbl(l_source_counter).attribute95 := l_header_rec.attribute95;
992 l_adj_tbl(l_source_counter).attribute96 := l_header_rec.attribute96;
993 l_adj_tbl(l_source_counter).attribute97 := l_header_rec.attribute97;
994 l_adj_tbl(l_source_counter).attribute98 := l_header_rec.attribute98;
995 l_adj_tbl(l_source_counter).attribute99 := l_header_rec.attribute99;
996 l_adj_tbl(l_source_counter).attribute100 := l_header_rec.attribute100;
997 l_adj_tbl(l_source_counter).quota_id := l_header_rec.quota_id;
998 l_adj_tbl(l_source_counter).revenue_class_id := l_header_rec.revenue_class_id;
999 l_adj_tbl(l_source_counter).revenue_class_name := l_header_rec.revenue_class_name;
1000 l_adj_tbl(l_source_counter).trx_batch_name := l_header_rec.trx_batch_name;
1001 l_adj_tbl(l_source_counter).source_trx_number := l_header_rec.source_trx_number;
1002 l_adj_tbl(l_source_counter).comm_lines_api_id := l_header_rec.comm_lines_api_id;
1003 l_adj_tbl(l_source_counter).source_doc_type := l_header_rec.source_doc_type;
1004 l_adj_tbl(l_source_counter).upside_amount := l_header_rec.upside_amount;
1005 l_adj_tbl(l_source_counter).upside_quantity := l_header_rec.upside_quantity;
1006 l_adj_tbl(l_source_counter).uom_code := l_header_rec.uom_code;
1007 l_adj_tbl(l_source_counter).forecast_id := l_header_rec.forecast_id;
1008 l_adj_tbl(l_source_counter).program_id := l_header_rec.program_id;
1009 l_adj_tbl(l_source_counter).request_id := l_header_rec.request_id;
1010 l_adj_tbl(l_source_counter).program_application_id := l_header_rec.program_application_id;
1011 l_adj_tbl(l_source_counter).program_update_date := l_header_rec.program_update_date;
1012 l_adj_tbl(l_source_counter).adj_comm_lines_api_id := l_header_rec.adj_comm_lines_api_id;
1013 l_adj_tbl(l_source_counter).invoice_number := l_header_rec.invoice_number;
1014 l_adj_tbl(l_source_counter).invoice_date := l_header_rec.invoice_date;
1015 l_adj_tbl(l_source_counter).order_number := l_header_rec.order_number;
1016 l_adj_tbl(l_source_counter).order_date := l_header_rec.booked_date;
1017 l_adj_tbl(l_source_counter).line_number := l_header_rec.line_number;
1018 l_adj_tbl(l_source_counter).customer_id := l_header_rec.customer_id;
1019 l_adj_tbl(l_source_counter).bill_to_address_id := l_header_rec.bill_to_address_id;
1020 l_adj_tbl(l_source_counter).ship_to_address_id := l_header_rec.ship_to_address_id;
1021 l_adj_tbl(l_source_counter).bill_to_contact_id := l_header_rec.bill_to_contact_id;
1022 l_adj_tbl(l_source_counter).ship_to_contact_id := l_header_rec.ship_to_contact_id;
1023 l_adj_tbl(l_source_counter).load_status := 'LOADED';
1024 l_adj_tbl(l_source_counter).revenue_type_disp := l_header_rec.revenue_type_disp;
1025 l_adj_tbl(l_source_counter).revenue_type := l_header_rec.revenue_type;
1026 l_adj_tbl(l_source_counter).adjust_rollup_flag := l_header_rec.adjust_rollup_flag;
1027 l_adj_tbl(l_source_counter).adjust_date := l_header_rec.adjust_date;
1028 l_adj_tbl(l_source_counter).adjusted_by := l_header_rec.adjusted_by;
1029 l_adj_tbl(l_source_counter).adjust_status_disp := l_header_rec.adjust_status_disp;
1030 l_adj_tbl(l_source_counter).adjust_status := l_header_rec.adjust_status;
1031 l_adj_tbl(l_source_counter).adjust_comments := l_header_rec.adjust_comments;
1035 l_adj_tbl(l_source_counter).srp_plan_assign_id := l_header_rec.srp_plan_assign_id;
1032 l_adj_tbl(l_source_counter).type := l_header_rec.type;
1033 l_adj_tbl(l_source_counter).pre_processed_code := l_header_rec.pre_processed_code;
1034 l_adj_tbl(l_source_counter).comp_group_id := l_header_rec.comp_group_id;
1036 l_adj_tbl(l_source_counter).role_id := l_header_rec.role_id;
1037 l_adj_tbl(l_source_counter).sales_channel := l_header_rec.sales_channel;
1038 l_adj_tbl(l_source_counter).object_version_number := l_header_rec.object_version_number;
1039 l_adj_tbl(l_source_counter).split_pct := l_header_rec.split_pct;
1040 l_adj_tbl(l_source_counter).split_status := l_header_rec.split_status;
1041 l_adj_tbl(l_source_counter).inventory_item_id := l_header_rec.inventory_item_id;
1042 l_adj_tbl(l_source_counter).org_id := l_header_rec.org_id;
1043 -- Bug fix 5349170
1044 l_adj_tbl(l_source_counter).source_trx_id := l_header_rec.source_trx_id;
1045 l_adj_tbl(l_source_counter).source_trx_line_id := l_header_rec.source_trx_line_id;
1046 l_adj_tbl(l_source_counter).source_trx_sales_line_id := l_header_rec.source_trx_sales_line_id;
1047 -- Bug fix 5349170
1048 -- Added for Crediting
1049 l_adj_tbl(l_source_counter).terr_id := l_header_rec.terr_id;
1050 l_adj_tbl(l_source_counter).preserve_credit_override_flag := l_header_rec.preserve_credit_override_flag;
1051
1052
1053 -- fix to get trx_id, trx_line_id, trx_sales_line_id when using header query
1054 -- an extra cursor is required as these columns are not available in
1055 -- cn_commission_headers table
1056 FOR inv_rec IN get_inv_details(l_header_rec.comm_lines_api_id) LOOP
1057 l_adj_tbl(l_source_counter).trx_id := inv_rec.trx_id;
1058 l_adj_tbl(l_source_counter).trx_line_id := inv_rec.trx_line_id;
1059 l_adj_tbl(l_source_counter).trx_sales_line_id := inv_rec.trx_sales_line_id;
1060
1061 END LOOP;
1062
1063 END LOOP;
1064 END IF;
1065
1066 IF (l_api_query_flag = 'Y') THEN
1067
1068
1069 --CN_mydebug.ADD('API SQL : '||l_api_sql);
1070
1071
1072 OPEN query_cur FOR l_api_sql
1073 USING
1074 l_salesrep_id,
1075 p_org_id,
1076 l_keep_flag,
1077 l_pr_date_from,
1078 l_pr_date_to,
1079 l_invoice_num,
1080 l_order_num,
1081 p_adj_status,
1082 p_load_status;
1083
1084 LOOP
1085 FETCH query_cur INTO l_api_rec;
1086 EXIT WHEN query_cur%NOTFOUND;
1087
1088 l_source_counter := l_source_counter + 1;
1089 l_adj_tbl(l_source_counter).direct_salesrep_number := l_api_rec.direct_salesrep_number;
1090 l_adj_tbl(l_source_counter).direct_salesrep_name := l_api_rec.direct_salesrep_name;
1091 l_adj_tbl(l_source_counter).direct_salesrep_id := l_api_rec.salesrep_id;
1092 l_adj_tbl(l_source_counter).processed_period_id := l_api_rec.processed_period_id;
1093 l_adj_tbl(l_source_counter).processed_period := l_api_rec.period_name;
1094 l_adj_tbl(l_source_counter).processed_date := l_api_rec.processed_date;
1095 l_adj_tbl(l_source_counter).rollup_date := l_api_rec.rollup_date;
1096 /* In this API table transaction_amount corresponds to foreign amount
1097 and acctd_transaction_amount corresponds to functional amount.*/
1098 l_adj_tbl(l_source_counter).transaction_amount := l_api_rec.acctd_transaction_amount;
1099 l_adj_tbl(l_source_counter).transaction_amount_orig := l_api_rec.transaction_amount;
1100 l_adj_tbl(l_source_counter).quantity := l_api_rec.quantity;
1101 l_adj_tbl(l_source_counter).discount_percentage := l_api_rec.discount_percentage;
1102 l_adj_tbl(l_source_counter).margin_percentage := l_api_rec.margin_percentage;
1103 l_adj_tbl(l_source_counter).orig_currency_code := l_api_rec.transaction_currency_code;
1104 l_adj_tbl(l_source_counter).exchange_rate := l_api_rec.exchange_rate;
1105 l_adj_tbl(l_source_counter).status_disp := g_space;
1106 l_adj_tbl(l_source_counter).status := g_space;
1107 l_adj_tbl(l_source_counter).trx_type_disp := l_api_rec.trx_type_disp;
1108 l_adj_tbl(l_source_counter).trx_type := l_api_rec.trx_type;
1109 l_adj_tbl(l_source_counter).reason := l_api_rec.reason;
1110 l_adj_tbl(l_source_counter).reason_code := l_api_rec.reason_code;
1111 l_adj_tbl(l_source_counter).created_by := l_api_rec.created_by;
1112 l_adj_tbl(l_source_counter).creation_date := l_api_rec.creation_date;
1113 l_adj_tbl(l_source_counter).last_updated_by := l_api_rec.last_updated_by;
1114 l_adj_tbl(l_source_counter).last_update_login := l_api_rec.last_update_login;
1115 l_adj_tbl(l_source_counter).last_update_date := l_api_rec.last_update_date;
1116 l_adj_tbl(l_source_counter).attribute_category := l_api_rec.attribute_category;
1117 l_adj_tbl(l_source_counter).attribute1 := l_api_rec.attribute1;
1118 l_adj_tbl(l_source_counter).attribute2 := l_api_rec.attribute2;
1119 l_adj_tbl(l_source_counter).attribute3 := l_api_rec.attribute3;
1120 l_adj_tbl(l_source_counter).attribute4 := l_api_rec.attribute4;
1121 l_adj_tbl(l_source_counter).attribute5 := l_api_rec.attribute5;
1122 l_adj_tbl(l_source_counter).attribute6 := l_api_rec.attribute6;
1123 l_adj_tbl(l_source_counter).attribute7 := l_api_rec.attribute7;
1124 l_adj_tbl(l_source_counter).attribute8 := l_api_rec.attribute8;
1125 l_adj_tbl(l_source_counter).attribute9 := l_api_rec.attribute9;
1126 l_adj_tbl(l_source_counter).attribute10 := l_api_rec.attribute10;
1127 l_adj_tbl(l_source_counter).attribute11 := l_api_rec.attribute11;
1128 l_adj_tbl(l_source_counter).attribute12 := l_api_rec.attribute12;
1132 l_adj_tbl(l_source_counter).attribute16 := l_api_rec.attribute16;
1129 l_adj_tbl(l_source_counter).attribute13 := l_api_rec.attribute13;
1130 l_adj_tbl(l_source_counter).attribute14 := l_api_rec.attribute14;
1131 l_adj_tbl(l_source_counter).attribute15 := l_api_rec.attribute15;
1133 l_adj_tbl(l_source_counter).attribute17 := l_api_rec.attribute17;
1134 l_adj_tbl(l_source_counter).attribute18 := l_api_rec.attribute18;
1135 l_adj_tbl(l_source_counter).attribute19 := l_api_rec.attribute19;
1136 l_adj_tbl(l_source_counter).attribute20 := l_api_rec.attribute20;
1137 l_adj_tbl(l_source_counter).attribute21 := l_api_rec.attribute21;
1138 l_adj_tbl(l_source_counter).attribute22 := l_api_rec.attribute22;
1139 l_adj_tbl(l_source_counter).attribute23 := l_api_rec.attribute23;
1140 l_adj_tbl(l_source_counter).attribute24 := l_api_rec.attribute24;
1141 l_adj_tbl(l_source_counter).attribute25 := l_api_rec.attribute25;
1142 l_adj_tbl(l_source_counter).attribute26 := l_api_rec.attribute26;
1143 l_adj_tbl(l_source_counter).attribute27 := l_api_rec.attribute27;
1144 l_adj_tbl(l_source_counter).attribute28 := l_api_rec.attribute28;
1145 l_adj_tbl(l_source_counter).attribute29 := l_api_rec.attribute29;
1146 l_adj_tbl(l_source_counter).attribute30 := l_api_rec.attribute30;
1147 l_adj_tbl(l_source_counter).attribute31 := l_api_rec.attribute31;
1148 l_adj_tbl(l_source_counter).attribute32 := l_api_rec.attribute32;
1149 l_adj_tbl(l_source_counter).attribute33 := l_api_rec.attribute33;
1150 l_adj_tbl(l_source_counter).attribute34 := l_api_rec.attribute34;
1151 l_adj_tbl(l_source_counter).attribute35 := l_api_rec.attribute35;
1152 l_adj_tbl(l_source_counter).attribute36 := l_api_rec.attribute36;
1153 l_adj_tbl(l_source_counter).attribute37 := l_api_rec.attribute37;
1154 l_adj_tbl(l_source_counter).attribute38 := l_api_rec.attribute38;
1155 l_adj_tbl(l_source_counter).attribute39 := l_api_rec.attribute39;
1156 l_adj_tbl(l_source_counter).attribute40 := l_api_rec.attribute40;
1157 l_adj_tbl(l_source_counter).attribute41 := l_api_rec.attribute41;
1158 l_adj_tbl(l_source_counter).attribute42 := l_api_rec.attribute42;
1159 l_adj_tbl(l_source_counter).attribute43 := l_api_rec.attribute43;
1160 l_adj_tbl(l_source_counter).attribute44 := l_api_rec.attribute44;
1161 l_adj_tbl(l_source_counter).attribute45 := l_api_rec.attribute45;
1162 l_adj_tbl(l_source_counter).attribute46 := l_api_rec.attribute46;
1163 l_adj_tbl(l_source_counter).attribute47 := l_api_rec.attribute47;
1164 l_adj_tbl(l_source_counter).attribute48 := l_api_rec.attribute48;
1165 l_adj_tbl(l_source_counter).attribute49 := l_api_rec.attribute49;
1166 l_adj_tbl(l_source_counter).attribute50 := l_api_rec.attribute50;
1167 l_adj_tbl(l_source_counter).attribute51 := l_api_rec.attribute51;
1168 l_adj_tbl(l_source_counter).attribute52 := l_api_rec.attribute52;
1169 l_adj_tbl(l_source_counter).attribute53 := l_api_rec.attribute53;
1170 l_adj_tbl(l_source_counter).attribute54 := l_api_rec.attribute54;
1171 l_adj_tbl(l_source_counter).attribute55 := l_api_rec.attribute55;
1172 l_adj_tbl(l_source_counter).attribute56 := l_api_rec.attribute56;
1173 l_adj_tbl(l_source_counter).attribute57 := l_api_rec.attribute57;
1174 l_adj_tbl(l_source_counter).attribute58 := l_api_rec.attribute58;
1175 l_adj_tbl(l_source_counter).attribute59 := l_api_rec.attribute59;
1176 l_adj_tbl(l_source_counter).attribute60 := l_api_rec.attribute60;
1177 l_adj_tbl(l_source_counter).attribute61 := l_api_rec.attribute61;
1178 l_adj_tbl(l_source_counter).attribute62 := l_api_rec.attribute62;
1179 l_adj_tbl(l_source_counter).attribute63 := l_api_rec.attribute63;
1180 l_adj_tbl(l_source_counter).attribute64 := l_api_rec.attribute64;
1181 l_adj_tbl(l_source_counter).attribute65 := l_api_rec.attribute65;
1182 l_adj_tbl(l_source_counter).attribute66 := l_api_rec.attribute66;
1183 l_adj_tbl(l_source_counter).attribute67 := l_api_rec.attribute67;
1184 l_adj_tbl(l_source_counter).attribute68 := l_api_rec.attribute68;
1185 l_adj_tbl(l_source_counter).attribute69 := l_api_rec.attribute69;
1186 l_adj_tbl(l_source_counter).attribute70 := l_api_rec.attribute70;
1187 l_adj_tbl(l_source_counter).attribute71 := l_api_rec.attribute71;
1188 l_adj_tbl(l_source_counter).attribute72 := l_api_rec.attribute72;
1189 l_adj_tbl(l_source_counter).attribute73 := l_api_rec.attribute73;
1190 l_adj_tbl(l_source_counter).attribute74 := l_api_rec.attribute74;
1191 l_adj_tbl(l_source_counter).attribute75 := l_api_rec.attribute75;
1192 l_adj_tbl(l_source_counter).attribute76 := l_api_rec.attribute76;
1193 l_adj_tbl(l_source_counter).attribute77 := l_api_rec.attribute77;
1194 l_adj_tbl(l_source_counter).attribute78 := l_api_rec.attribute78;
1195 l_adj_tbl(l_source_counter).attribute79 := l_api_rec.attribute79;
1196 l_adj_tbl(l_source_counter).attribute80 := l_api_rec.attribute80;
1197 l_adj_tbl(l_source_counter).attribute81 := l_api_rec.attribute81;
1198 l_adj_tbl(l_source_counter).attribute82 := l_api_rec.attribute82;
1199 l_adj_tbl(l_source_counter).attribute83 := l_api_rec.attribute83;
1200 l_adj_tbl(l_source_counter).attribute84 := l_api_rec.attribute84;
1204 l_adj_tbl(l_source_counter).attribute88 := l_api_rec.attribute88;
1201 l_adj_tbl(l_source_counter).attribute85 := l_api_rec.attribute85;
1202 l_adj_tbl(l_source_counter).attribute86 := l_api_rec.attribute86;
1203 l_adj_tbl(l_source_counter).attribute87 := l_api_rec.attribute87;
1205 l_adj_tbl(l_source_counter).attribute89 := l_api_rec.attribute89;
1206 l_adj_tbl(l_source_counter).attribute90 := l_api_rec.attribute90;
1207 l_adj_tbl(l_source_counter).attribute91 := l_api_rec.attribute91;
1208 l_adj_tbl(l_source_counter).attribute92 := l_api_rec.attribute92;
1209 l_adj_tbl(l_source_counter).attribute93 := l_api_rec.attribute93;
1210 l_adj_tbl(l_source_counter).attribute94 := l_api_rec.attribute94;
1211 l_adj_tbl(l_source_counter).attribute95 := l_api_rec.attribute95;
1212 l_adj_tbl(l_source_counter).attribute96 := l_api_rec.attribute96;
1213 l_adj_tbl(l_source_counter).attribute97 := l_api_rec.attribute97;
1214 l_adj_tbl(l_source_counter).attribute98 := l_api_rec.attribute98;
1215 l_adj_tbl(l_source_counter).attribute99 := l_api_rec.attribute99;
1216 l_adj_tbl(l_source_counter).attribute100 := l_api_rec.attribute100;
1217 l_adj_tbl(l_source_counter).quota_id := l_api_rec.quota_id;
1218 l_adj_tbl(l_source_counter).revenue_class_id := l_api_rec.revenue_class_id;
1219 l_adj_tbl(l_source_counter).revenue_class_name := l_api_rec.revenue_class_name;
1220 l_adj_tbl(l_source_counter).source_trx_number := l_api_rec.source_trx_number;
1221 l_adj_tbl(l_source_counter).comm_lines_api_id := l_api_rec.comm_lines_api_id;
1222 l_adj_tbl(l_source_counter).source_doc_type := l_api_rec.source_doc_type;
1223 l_adj_tbl(l_source_counter).upside_amount := l_api_rec.upside_amount;
1224 l_adj_tbl(l_source_counter).upside_quantity := l_api_rec.upside_quantity;
1225 l_adj_tbl(l_source_counter).uom_code := l_api_rec.uom_code;
1226 l_adj_tbl(l_source_counter).forecast_id := l_api_rec.forecast_id;
1227 l_adj_tbl(l_source_counter).adj_comm_lines_api_id := l_api_rec.adj_comm_lines_api_id;
1228 l_adj_tbl(l_source_counter).invoice_number := l_api_rec.invoice_number;
1229 l_adj_tbl(l_source_counter).invoice_date := l_api_rec.invoice_date;
1230 l_adj_tbl(l_source_counter).order_number := l_api_rec.order_number;
1231 l_adj_tbl(l_source_counter).order_date := l_api_rec.booked_date;
1232 l_adj_tbl(l_source_counter).line_number := l_api_rec.line_number;
1233 l_adj_tbl(l_source_counter).customer_id := l_api_rec.customer_id;
1234 l_adj_tbl(l_source_counter).bill_to_address_id := l_api_rec.bill_to_address_id;
1235 l_adj_tbl(l_source_counter).ship_to_address_id := l_api_rec.ship_to_address_id;
1236 l_adj_tbl(l_source_counter).bill_to_contact_id := l_api_rec.bill_to_contact_id;
1237 l_adj_tbl(l_source_counter).ship_to_contact_id := l_api_rec.ship_to_contact_id;
1238 l_adj_tbl(l_source_counter).load_status := l_api_rec.load_status;
1239 l_adj_tbl(l_source_counter).revenue_type_disp := l_api_rec.revenue_type_disp;
1240 l_adj_tbl(l_source_counter).revenue_type := l_api_rec.revenue_type;
1241 l_adj_tbl(l_source_counter).adjust_rollup_flag := l_api_rec.adjust_rollup_flag;
1242 l_adj_tbl(l_source_counter).adjust_date := l_api_rec.adjust_date;
1243 l_adj_tbl(l_source_counter).adjusted_by := l_api_rec.adjusted_by;
1244 l_adj_tbl(l_source_counter).adjust_status_disp := l_api_rec.adjust_status_disp;
1245 l_adj_tbl(l_source_counter).adjust_status := l_api_rec.adjust_status;
1246 l_adj_tbl(l_source_counter).adjust_comments := l_api_rec.adjust_comments;
1247 l_adj_tbl(l_source_counter).type := l_api_rec.type;
1248 l_adj_tbl(l_source_counter).pre_processed_code := l_api_rec.pre_processed_code;
1249 l_adj_tbl(l_source_counter).comp_group_id := l_api_rec.comp_group_id;
1250 l_adj_tbl(l_source_counter).srp_plan_assign_id := l_api_rec.srp_plan_assign_id;
1251 l_adj_tbl(l_source_counter).role_id := l_api_rec.role_id;
1252 l_adj_tbl(l_source_counter).sales_channel := l_api_rec.sales_channel;
1253 l_adj_tbl(l_source_counter).object_version_number := l_api_rec.object_version_number;
1254 l_adj_tbl(l_source_counter).split_pct := l_api_rec.split_pct;
1255 l_adj_tbl(l_source_counter).split_status := l_api_rec.split_status;
1256 l_adj_tbl(l_source_counter).inventory_item_id := l_api_rec.inventory_item_id;
1257 l_adj_tbl(l_source_counter).org_id := l_api_rec.org_id;
1258 -- Bug fix 5349170
1259 l_adj_tbl(l_source_counter).trx_id := l_api_rec.trx_id;
1260 l_adj_tbl(l_source_counter).trx_line_id := l_api_rec.trx_line_id;
1261 l_adj_tbl(l_source_counter).trx_sales_line_id := l_api_rec.trx_sales_line_id;
1262
1263 l_adj_tbl(l_source_counter).source_trx_id := l_api_rec.source_trx_id;
1264 l_adj_tbl(l_source_counter).source_trx_line_id := l_api_rec.source_trx_line_id;
1265 l_adj_tbl(l_source_counter).source_trx_sales_line_id := l_api_rec.source_trx_sales_line_id;
1266 -- Bug fix 5349170
1267
1268 l_adj_tbl(l_source_counter).terr_id := l_api_rec.terr_id;
1269 l_adj_tbl(l_source_counter).preserve_credit_override_flag := l_api_rec.preserve_credit_override_flag;
1270
1271
1272 END LOOP;
1273 END IF;
1274
1275 IF (l_source_counter = 0) THEN
1276 x_return_status := 'NO DATA';
1277 x_source_counter := l_source_counter;
1278 ELSE
1279 x_adj_tbl := l_adj_tbl;
1280 x_source_counter := l_source_counter;
1281 x_return_status := 'SUCCESS';
1282 END IF;
1283 EXCEPTION
1284 WHEN OTHERS THEN
1285 x_return_status := 'ERROR';
1289 --
1286 --cn_mass_adjust_util.my_debug(SQLERRM);
1287 x_source_counter := l_source_counter;
1288 END;
1290 PROCEDURE convert_rec_to_tbl(
1291 p_srch_attr_rec IN cn_get_tx_data_pub.adj_rec_type,
1292 x_attribute_tbl OUT NOCOPY cn_get_tx_data_pub.attribute_tbl) IS
1293 --
1294 l_counter NUMBER := 1;
1295 --
1296 BEGIN
1297 x_attribute_tbl(1).attribute_name := NULL;
1298 x_attribute_tbl(1).attribute_value := NULL;
1299 l_counter := l_counter + 1;
1300 IF (p_srch_attr_rec.attribute1 <> FND_API.G_MISS_CHAR) THEN
1301 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE1';
1302 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute1;
1303 l_counter := l_counter + 1;
1304 END IF;
1305 IF (p_srch_attr_rec.attribute2 <> FND_API.G_MISS_CHAR) THEN
1306 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE2';
1307 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute2;
1308 l_counter := l_counter + 1;
1309 END IF;
1310 IF (p_srch_attr_rec.attribute3 <> FND_API.G_MISS_CHAR) THEN
1311 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE3';
1312 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute3;
1313 l_counter := l_counter + 1;
1314 END IF;
1315 IF (p_srch_attr_rec.attribute4 <> FND_API.G_MISS_CHAR) THEN
1316 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE4';
1317 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute4;
1318 l_counter := l_counter + 1;
1319 END IF;
1320 IF (p_srch_attr_rec.attribute5 <> FND_API.G_MISS_CHAR) THEN
1321 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE5';
1322 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute5;
1323 l_counter := l_counter + 1;
1324 END IF;
1325 IF (p_srch_attr_rec.attribute6 <> FND_API.G_MISS_CHAR) THEN
1326 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE6';
1327 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute6;
1328 l_counter := l_counter + 1;
1329 END IF;
1330 IF (p_srch_attr_rec.attribute7 <> FND_API.G_MISS_CHAR) THEN
1331 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE7';
1332 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute7;
1333 l_counter := l_counter + 1;
1334 END IF;
1335 IF (p_srch_attr_rec.attribute8 <> FND_API.G_MISS_CHAR) THEN
1336 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE8';
1337 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute8;
1338 l_counter := l_counter + 1;
1339 END IF;
1340 IF (p_srch_attr_rec.attribute9 <> FND_API.G_MISS_CHAR) THEN
1341 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE9';
1342 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute9;
1343 l_counter := l_counter + 1;
1344 END IF;
1345 IF (p_srch_attr_rec.attribute10 <> FND_API.G_MISS_CHAR) THEN
1346 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE10';
1347 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute10;
1348 l_counter := l_counter + 1;
1349 END IF;
1350 IF (p_srch_attr_rec.attribute11 <> FND_API.G_MISS_CHAR) THEN
1351 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE11';
1352 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute11;
1353 l_counter := l_counter + 1;
1354 END IF;
1355 IF (p_srch_attr_rec.attribute12 <> FND_API.G_MISS_CHAR) THEN
1356 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE12';
1357 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute12;
1358 l_counter := l_counter + 1;
1359 END IF;
1360 IF (p_srch_attr_rec.attribute13 <> FND_API.G_MISS_CHAR) THEN
1361 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE13';
1362 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute13;
1363 l_counter := l_counter + 1;
1364 END IF;
1365 IF (p_srch_attr_rec.attribute14 <> FND_API.G_MISS_CHAR) THEN
1366 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE14';
1367 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute14;
1368 l_counter := l_counter + 1;
1369 END IF;
1370 IF (p_srch_attr_rec.attribute15 <> FND_API.G_MISS_CHAR) THEN
1371 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE15';
1372 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute15;
1373 l_counter := l_counter + 1;
1374 END IF;
1375 IF (p_srch_attr_rec.attribute16 <> FND_API.G_MISS_CHAR) THEN
1376 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE16';
1377 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute16;
1378 l_counter := l_counter + 1;
1379 END IF;
1380 IF (p_srch_attr_rec.attribute17 <> FND_API.G_MISS_CHAR) THEN
1381 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE17';
1382 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute17;
1383 l_counter := l_counter + 1;
1384 END IF;
1385 IF (p_srch_attr_rec.attribute18 <> FND_API.G_MISS_CHAR) THEN
1386 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE18';
1387 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute18;
1388 l_counter := l_counter + 1;
1389 END IF;
1390 IF (p_srch_attr_rec.attribute19 <> FND_API.G_MISS_CHAR) THEN
1391 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE19';
1392 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute19;
1393 l_counter := l_counter + 1;
1394 END IF;
1398 l_counter := l_counter + 1;
1395 IF (p_srch_attr_rec.attribute20 <> FND_API.G_MISS_CHAR) THEN
1396 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE20';
1397 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute20;
1399 END IF;
1400 IF (p_srch_attr_rec.attribute21 <> FND_API.G_MISS_CHAR) THEN
1401 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE21';
1402 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute21;
1403 l_counter := l_counter + 1;
1404 END IF;
1405 IF (p_srch_attr_rec.attribute22 <> FND_API.G_MISS_CHAR) THEN
1406 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE22';
1407 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute22;
1408 l_counter := l_counter + 1;
1409 END IF;
1410 IF (p_srch_attr_rec.attribute23 <> FND_API.G_MISS_CHAR) THEN
1411 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE23';
1412 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute23;
1413 l_counter := l_counter + 1;
1414 END IF;
1415 IF (p_srch_attr_rec.attribute24 <> FND_API.G_MISS_CHAR) THEN
1416 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE24';
1417 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute24;
1418 l_counter := l_counter + 1;
1419 END IF;
1420 IF (p_srch_attr_rec.attribute25 <> FND_API.G_MISS_CHAR) THEN
1421 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE25';
1422 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute25;
1423 l_counter := l_counter + 1;
1424 END IF;
1425 IF (p_srch_attr_rec.attribute26 <> FND_API.G_MISS_CHAR) THEN
1426 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE26';
1427 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute26;
1428 l_counter := l_counter + 1;
1429 END IF;
1430 IF (p_srch_attr_rec.attribute27 <> FND_API.G_MISS_CHAR) THEN
1431 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE27';
1432 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute27;
1433 l_counter := l_counter + 1;
1434 END IF;
1435 IF (p_srch_attr_rec.attribute28 <> FND_API.G_MISS_CHAR) THEN
1436 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE28';
1437 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute28;
1438 l_counter := l_counter + 1;
1439 END IF;
1440 IF (p_srch_attr_rec.attribute29 <> FND_API.G_MISS_CHAR) THEN
1441 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE29';
1442 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute29;
1443 l_counter := l_counter + 1;
1444 END IF;
1445 IF (p_srch_attr_rec.attribute30 <> FND_API.G_MISS_CHAR) THEN
1446 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE30';
1447 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute30;
1448 l_counter := l_counter + 1;
1449 END IF;
1450 IF (p_srch_attr_rec.attribute31 <> FND_API.G_MISS_CHAR) THEN
1451 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE31';
1452 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute31;
1453 l_counter := l_counter + 1;
1454 END IF;
1455 IF (p_srch_attr_rec.attribute32 <> FND_API.G_MISS_CHAR) THEN
1456 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE32';
1457 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute32;
1458 l_counter := l_counter + 1;
1459 END IF;
1460 IF (p_srch_attr_rec.attribute33 <> FND_API.G_MISS_CHAR) THEN
1461 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE33';
1462 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute33;
1463 l_counter := l_counter + 1;
1464 END IF;
1465 IF (p_srch_attr_rec.attribute34 <> FND_API.G_MISS_CHAR) THEN
1466 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE34';
1467 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute34;
1468 l_counter := l_counter + 1;
1469 END IF;
1470 IF (p_srch_attr_rec.attribute35 <> FND_API.G_MISS_CHAR) THEN
1471 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE35';
1472 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute35;
1473 l_counter := l_counter + 1;
1474 END IF;
1475 IF (p_srch_attr_rec.attribute36 <> FND_API.G_MISS_CHAR) THEN
1476 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE36';
1477 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute36;
1478 l_counter := l_counter + 1;
1479 END IF;
1480 IF (p_srch_attr_rec.attribute37 <> FND_API.G_MISS_CHAR) THEN
1481 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE37';
1482 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute37;
1483 l_counter := l_counter + 1;
1484 END IF;
1485 IF (p_srch_attr_rec.attribute38 <> FND_API.G_MISS_CHAR) THEN
1486 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE38';
1487 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute38;
1488 l_counter := l_counter + 1;
1489 END IF;
1490 IF (p_srch_attr_rec.attribute39 <> FND_API.G_MISS_CHAR) THEN
1491 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE39';
1492 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute39;
1493 l_counter := l_counter + 1;
1494 END IF;
1495 IF (p_srch_attr_rec.attribute40 <> FND_API.G_MISS_CHAR) THEN
1496 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE40';
1497 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute40;
1501 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE41';
1498 l_counter := l_counter + 1;
1499 END IF;
1500 IF (p_srch_attr_rec.attribute41 <> FND_API.G_MISS_CHAR) THEN
1502 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute41;
1503 l_counter := l_counter + 1;
1504 END IF;
1505 IF (p_srch_attr_rec.attribute42 <> FND_API.G_MISS_CHAR) THEN
1506 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE42';
1507 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute42;
1508 l_counter := l_counter + 1;
1509 END IF;
1510 IF (p_srch_attr_rec.attribute43 <> FND_API.G_MISS_CHAR) THEN
1511 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE43';
1512 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute43;
1513 l_counter := l_counter + 1;
1514 END IF;
1515 IF (p_srch_attr_rec.attribute44 <> FND_API.G_MISS_CHAR) THEN
1516 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE44';
1517 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute44;
1518 l_counter := l_counter + 1;
1519 END IF;
1520 IF (p_srch_attr_rec.attribute45 <> FND_API.G_MISS_CHAR) THEN
1521 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE45';
1522 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute45;
1523 l_counter := l_counter + 1;
1524 END IF;
1525 IF (p_srch_attr_rec.attribute46 <> FND_API.G_MISS_CHAR) THEN
1526 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE46';
1527 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute46;
1528 l_counter := l_counter + 1;
1529 END IF;
1530 IF (p_srch_attr_rec.attribute47 <> FND_API.G_MISS_CHAR) THEN
1531 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE47';
1532 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute47;
1533 l_counter := l_counter + 1;
1534 END IF;
1535 IF (p_srch_attr_rec.attribute48 <> FND_API.G_MISS_CHAR) THEN
1536 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE48';
1537 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute48;
1538 l_counter := l_counter + 1;
1539 END IF;
1540 IF (p_srch_attr_rec.attribute49 <> FND_API.G_MISS_CHAR) THEN
1541 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE49';
1542 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute49;
1543 l_counter := l_counter + 1;
1544 END IF;
1545 IF (p_srch_attr_rec.attribute50 <> FND_API.G_MISS_CHAR) THEN
1546 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE50';
1547 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute50;
1548 l_counter := l_counter + 1;
1549 END IF;
1550 IF (p_srch_attr_rec.attribute51 <> FND_API.G_MISS_CHAR) THEN
1551 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE51';
1552 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute51;
1553 l_counter := l_counter + 1;
1554 END IF;
1555 IF (p_srch_attr_rec.attribute52 <> FND_API.G_MISS_CHAR) THEN
1556 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE52';
1557 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute52;
1558 l_counter := l_counter + 1;
1559 END IF;
1560 IF (p_srch_attr_rec.attribute53 <> FND_API.G_MISS_CHAR) THEN
1561 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE53';
1562 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute53;
1563 l_counter := l_counter + 1;
1564 END IF;
1565 IF (p_srch_attr_rec.attribute54 <> FND_API.G_MISS_CHAR) THEN
1566 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE54';
1567 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute54;
1568 l_counter := l_counter + 1;
1569 END IF;
1570 IF (p_srch_attr_rec.attribute55 <> FND_API.G_MISS_CHAR) THEN
1571 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE55';
1572 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute55;
1573 l_counter := l_counter + 1;
1574 END IF;
1575 IF (p_srch_attr_rec.attribute56 <> FND_API.G_MISS_CHAR) THEN
1576 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE56';
1577 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute56;
1578 l_counter := l_counter + 1;
1579 END IF;
1580 IF (p_srch_attr_rec.attribute57 <> FND_API.G_MISS_CHAR) THEN
1581 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE57';
1582 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute57;
1583 l_counter := l_counter + 1;
1584 END IF;
1585 IF (p_srch_attr_rec.attribute58 <> FND_API.G_MISS_CHAR) THEN
1586 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE58';
1587 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute58;
1588 l_counter := l_counter + 1;
1589 END IF;
1590 IF (p_srch_attr_rec.attribute59 <> FND_API.G_MISS_CHAR) THEN
1591 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE59';
1592 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute59;
1593 l_counter := l_counter + 1;
1594 END IF;
1595 IF (p_srch_attr_rec.attribute60 <> FND_API.G_MISS_CHAR) THEN
1596 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE60';
1597 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute60;
1598 l_counter := l_counter + 1;
1599 END IF;
1600 IF (p_srch_attr_rec.attribute61 <> FND_API.G_MISS_CHAR) THEN
1601 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE61';
1605 IF (p_srch_attr_rec.attribute62 <> FND_API.G_MISS_CHAR) THEN
1602 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute61;
1603 l_counter := l_counter + 1;
1604 END IF;
1606 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE62';
1607 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute62;
1608 l_counter := l_counter + 1;
1609 END IF;
1610 IF (p_srch_attr_rec.attribute63 <> FND_API.G_MISS_CHAR) THEN
1611 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE63';
1612 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute63;
1613 l_counter := l_counter + 1;
1614 END IF;
1615 IF (p_srch_attr_rec.attribute64 <> FND_API.G_MISS_CHAR) THEN
1616 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE64';
1617 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute64;
1618 l_counter := l_counter + 1;
1619 END IF;
1620 IF (p_srch_attr_rec.attribute65 <> FND_API.G_MISS_CHAR) THEN
1621 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE65';
1622 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute65;
1623 l_counter := l_counter + 1;
1624 END IF;
1625 IF (p_srch_attr_rec.attribute66 <> FND_API.G_MISS_CHAR) THEN
1626 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE66';
1627 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute66;
1628 l_counter := l_counter + 1;
1629 END IF;
1630 IF (p_srch_attr_rec.attribute67 <> FND_API.G_MISS_CHAR) THEN
1631 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE67';
1632 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute67;
1633 l_counter := l_counter + 1;
1634 END IF;
1635 IF (p_srch_attr_rec.attribute68 <> FND_API.G_MISS_CHAR) THEN
1636 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE68';
1637 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute68;
1638 l_counter := l_counter + 1;
1639 END IF;
1640 IF (p_srch_attr_rec.attribute69 <> FND_API.G_MISS_CHAR) THEN
1641 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE69';
1642 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute69;
1643 l_counter := l_counter + 1;
1644 END IF;
1645 IF (p_srch_attr_rec.attribute70 <> FND_API.G_MISS_CHAR) THEN
1646 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE70';
1647 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute70;
1648 l_counter := l_counter + 1;
1649 END IF;
1650 IF (p_srch_attr_rec.attribute71 <> FND_API.G_MISS_CHAR) THEN
1651 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE71';
1652 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute71;
1653 l_counter := l_counter + 1;
1654 END IF;
1655 IF (p_srch_attr_rec.attribute72 <> FND_API.G_MISS_CHAR) THEN
1656 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE72';
1657 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute72;
1658 l_counter := l_counter + 1;
1659 END IF;
1660 IF (p_srch_attr_rec.attribute73 <> FND_API.G_MISS_CHAR) THEN
1661 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE73';
1662 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute73;
1663 l_counter := l_counter + 1;
1664 END IF;
1665 IF (p_srch_attr_rec.attribute74 <> FND_API.G_MISS_CHAR) THEN
1666 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE74';
1667 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute74;
1668 l_counter := l_counter + 1;
1669 END IF;
1670 IF (p_srch_attr_rec.attribute75 <> FND_API.G_MISS_CHAR) THEN
1671 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE75';
1672 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute75;
1673 l_counter := l_counter + 1;
1674 END IF;
1675 IF (p_srch_attr_rec.attribute76 <> FND_API.G_MISS_CHAR) THEN
1676 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE76';
1677 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute76;
1678 l_counter := l_counter + 1;
1679 END IF;
1680 IF (p_srch_attr_rec.attribute77 <> FND_API.G_MISS_CHAR) THEN
1681 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE77';
1682 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute77;
1683 l_counter := l_counter + 1;
1684 END IF;
1685 IF (p_srch_attr_rec.attribute78 <> FND_API.G_MISS_CHAR) THEN
1686 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE78';
1687 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute78;
1688 l_counter := l_counter + 1;
1689 END IF;
1690 IF (p_srch_attr_rec.attribute79 <> FND_API.G_MISS_CHAR) THEN
1691 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE79';
1692 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute79;
1693 l_counter := l_counter + 1;
1694 END IF;
1695 IF (p_srch_attr_rec.attribute80 <> FND_API.G_MISS_CHAR) THEN
1696 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE80';
1697 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute80;
1698 l_counter := l_counter + 1;
1699 END IF;
1700 IF (p_srch_attr_rec.attribute81 <> FND_API.G_MISS_CHAR) THEN
1701 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE81';
1702 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute81;
1703 l_counter := l_counter + 1;
1704 END IF;
1705 IF (p_srch_attr_rec.attribute82 <> FND_API.G_MISS_CHAR) THEN
1709 END IF;
1706 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE82';
1707 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute82;
1708 l_counter := l_counter + 1;
1710 IF (p_srch_attr_rec.attribute83 <> FND_API.G_MISS_CHAR) THEN
1711 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE83';
1712 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute83;
1713 l_counter := l_counter + 1;
1714 END IF;
1715 IF (p_srch_attr_rec.attribute84 <> FND_API.G_MISS_CHAR) THEN
1716 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE84';
1717 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute84;
1718 l_counter := l_counter + 1;
1719 END IF;
1720 IF (p_srch_attr_rec.attribute85 <> FND_API.G_MISS_CHAR) THEN
1721 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE85';
1722 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute85;
1723 l_counter := l_counter + 1;
1724 END IF;
1725 IF (p_srch_attr_rec.attribute86 <> FND_API.G_MISS_CHAR) THEN
1726 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE86';
1727 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute86;
1728 l_counter := l_counter + 1;
1729 END IF;
1730 IF (p_srch_attr_rec.attribute87 <> FND_API.G_MISS_CHAR) THEN
1731 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE87';
1732 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute87;
1733 l_counter := l_counter + 1;
1734 END IF;
1735 IF (p_srch_attr_rec.attribute88 <> FND_API.G_MISS_CHAR) THEN
1736 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE88';
1737 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute88;
1738 l_counter := l_counter + 1;
1739 END IF;
1740 IF (p_srch_attr_rec.attribute89 <> FND_API.G_MISS_CHAR) THEN
1741 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE89';
1742 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute89;
1743 l_counter := l_counter + 1;
1744 END IF;
1745 IF (p_srch_attr_rec.attribute90 <> FND_API.G_MISS_CHAR) THEN
1746 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE90';
1747 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute90;
1748 l_counter := l_counter + 1;
1749 END IF;
1750 IF (p_srch_attr_rec.attribute91 <> FND_API.G_MISS_CHAR) THEN
1751 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE91';
1752 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute91;
1753 l_counter := l_counter + 1;
1754 END IF;
1755 IF (p_srch_attr_rec.attribute92 <> FND_API.G_MISS_CHAR) THEN
1756 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE92';
1757 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute92;
1758 l_counter := l_counter + 1;
1759 END IF;
1760 IF (p_srch_attr_rec.attribute93 <> FND_API.G_MISS_CHAR) THEN
1761 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE93';
1762 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute93;
1763 l_counter := l_counter + 1;
1764 END IF;
1765 IF (p_srch_attr_rec.attribute94 <> FND_API.G_MISS_CHAR) THEN
1766 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE94';
1767 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute94;
1768 l_counter := l_counter + 1;
1769 END IF;
1770 IF (p_srch_attr_rec.attribute95 <> FND_API.G_MISS_CHAR) THEN
1771 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE95';
1772 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute95;
1773 l_counter := l_counter + 1;
1774 END IF;
1775 IF (p_srch_attr_rec.attribute96 <> FND_API.G_MISS_CHAR) THEN
1776 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE96';
1777 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute96;
1778 l_counter := l_counter + 1;
1779 END IF;
1780 IF (p_srch_attr_rec.attribute97 <> FND_API.G_MISS_CHAR) THEN
1781 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE97';
1782 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute97;
1783 l_counter := l_counter + 1;
1784 END IF;
1785 IF (p_srch_attr_rec.attribute98 <> FND_API.G_MISS_CHAR) THEN
1786 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE98';
1787 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute98;
1788 l_counter := l_counter + 1;
1789 END IF;
1790 IF (p_srch_attr_rec.attribute99 <> FND_API.G_MISS_CHAR) THEN
1791 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE99';
1792 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute99;
1793 l_counter := l_counter + 1;
1794 END IF;
1795 IF (p_srch_attr_rec.attribute100 <> FND_API.G_MISS_CHAR) THEN
1796 x_attribute_tbl(l_counter).attribute_name := 'ATTRIBUTE100';
1797 x_attribute_tbl(l_counter).attribute_value := p_srch_attr_rec.attribute100;
1798 l_counter := l_counter + 1;
1799 END IF;
1800 END;
1801
1802 PROCEDURE convert_rec_to_gmiss(
1803 p_rec IN cn_get_tx_data_pub.adj_rec_type,
1804 x_api_rec OUT NOCOPY cn_get_tx_data_pub.adj_rec_type) IS
1805
1806 BEGIN
1807
1808 SELECT DECODE(p_rec.direct_salesrep_id, null, fnd_api.g_miss_num, p_rec.direct_salesrep_id),
1809 DECODE(p_rec.processed_date, null, fnd_api.g_miss_date, p_rec.processed_date),
1810 DECODE(p_rec.processed_period_id, null, fnd_api.g_miss_num, p_rec.processed_period_id),
1814 DECODE(p_rec.load_status, null, fnd_api.g_miss_char, p_rec.load_status),
1811 DECODE(p_rec.transaction_amount, null, fnd_api.g_miss_num, p_rec.transaction_amount),
1812 DECODE(p_rec.trx_type, null, fnd_api.g_miss_char,p_rec.trx_type),
1813 DECODE(p_rec.revenue_class_id, null, fnd_api.g_miss_num, p_rec.revenue_class_id),
1815 DECODE(p_rec.attribute1,null, fnd_api.g_miss_char, p_rec.attribute1),
1816 DECODE(p_rec.attribute2,null, fnd_api.g_miss_char, p_rec.attribute2),
1817 DECODE(p_rec.attribute3,null, fnd_api.g_miss_char, p_rec.attribute3),
1818 DECODE(p_rec.attribute4,null, fnd_api.g_miss_char, p_rec.attribute4),
1819 DECODE(p_rec.attribute5,null, fnd_api.g_miss_char, p_rec.attribute5),
1820 DECODE(p_rec.attribute6,null, fnd_api.g_miss_char, p_rec.attribute6),
1821 DECODE(p_rec.attribute7,null, fnd_api.g_miss_char, p_rec.attribute7),
1822 DECODE(p_rec.attribute8,null, fnd_api.g_miss_char, p_rec.attribute8),
1823 DECODE(p_rec.attribute9,null, fnd_api.g_miss_char, p_rec.attribute9),
1824 DECODE(p_rec.attribute10,null, fnd_api.g_miss_char, p_rec.attribute10),
1825 DECODE(p_rec.attribute11,null, fnd_api.g_miss_char, p_rec.attribute11),
1826 DECODE(p_rec.attribute12,null, fnd_api.g_miss_char, p_rec.attribute12),
1827 DECODE(p_rec.attribute13,null, fnd_api.g_miss_char, p_rec.attribute13),
1828 DECODE(p_rec.attribute14,null, fnd_api.g_miss_char, p_rec.attribute14),
1829 DECODE(p_rec.attribute15,null, fnd_api.g_miss_char, p_rec.attribute15),
1830 DECODE(p_rec.attribute16,null, fnd_api.g_miss_char, p_rec.attribute16),
1831 DECODE(p_rec.attribute17,null, fnd_api.g_miss_char, p_rec.attribute17),
1832 DECODE(p_rec.attribute18,null, fnd_api.g_miss_char, p_rec.attribute18),
1833 DECODE(p_rec.attribute19,null, fnd_api.g_miss_char, p_rec.attribute19),
1834 DECODE(p_rec.attribute20,null, fnd_api.g_miss_char, p_rec.attribute20),
1835 DECODE(p_rec.attribute21,null, fnd_api.g_miss_char, p_rec.attribute21),
1836 DECODE(p_rec.attribute22,null, fnd_api.g_miss_char, p_rec.attribute22),
1837 DECODE(p_rec.attribute23,null, fnd_api.g_miss_char, p_rec.attribute23),
1838 DECODE(p_rec.attribute24,null, fnd_api.g_miss_char, p_rec.attribute24),
1839 DECODE(p_rec.attribute25,null, fnd_api.g_miss_char, p_rec.attribute25),
1840 DECODE(p_rec.attribute26,null, fnd_api.g_miss_char, p_rec.attribute26),
1841 DECODE(p_rec.attribute27,null, fnd_api.g_miss_char, p_rec.attribute27),
1842 DECODE(p_rec.attribute28,null, fnd_api.g_miss_char, p_rec.attribute28),
1843 DECODE(p_rec.attribute29,null, fnd_api.g_miss_char, p_rec.attribute29),
1844 DECODE(p_rec.attribute30,null, fnd_api.g_miss_char, p_rec.attribute30),
1845 DECODE(p_rec.attribute31,null, fnd_api.g_miss_char, p_rec.attribute31),
1846 DECODE(p_rec.attribute32,null, fnd_api.g_miss_char, p_rec.attribute32),
1847 DECODE(p_rec.attribute33,null, fnd_api.g_miss_char, p_rec.attribute33),
1848 DECODE(p_rec.attribute34,null, fnd_api.g_miss_char, p_rec.attribute34),
1849 DECODE(p_rec.attribute35,null, fnd_api.g_miss_char, p_rec.attribute35),
1850 DECODE(p_rec.attribute36,null, fnd_api.g_miss_char, p_rec.attribute36),
1851 DECODE(p_rec.attribute37,null, fnd_api.g_miss_char, p_rec.attribute37),
1852 DECODE(p_rec.attribute38,null, fnd_api.g_miss_char, p_rec.attribute38),
1853 DECODE(p_rec.attribute39,null, fnd_api.g_miss_char, p_rec.attribute39),
1854 DECODE(p_rec.attribute40,null, fnd_api.g_miss_char, p_rec.attribute40),
1855 DECODE(p_rec.attribute41,null, fnd_api.g_miss_char, p_rec.attribute41),
1856 DECODE(p_rec.attribute42,null, fnd_api.g_miss_char, p_rec.attribute42),
1857 DECODE(p_rec.attribute43,null, fnd_api.g_miss_char, p_rec.attribute43),
1858 DECODE(p_rec.attribute44,null, fnd_api.g_miss_char, p_rec.attribute44),
1859 DECODE(p_rec.attribute45,null, fnd_api.g_miss_char, p_rec.attribute45),
1860 DECODE(p_rec.attribute46,null, fnd_api.g_miss_char, p_rec.attribute46),
1861 DECODE(p_rec.attribute47,null, fnd_api.g_miss_char, p_rec.attribute47),
1862 DECODE(p_rec.attribute48,null, fnd_api.g_miss_char, p_rec.attribute48),
1863 DECODE(p_rec.attribute49,null, fnd_api.g_miss_char, p_rec.attribute49),
1864 DECODE(p_rec.attribute50,null, fnd_api.g_miss_char, p_rec.attribute50),
1865 DECODE(p_rec.attribute51,null, fnd_api.g_miss_char, p_rec.attribute51),
1866 DECODE(p_rec.attribute52,null, fnd_api.g_miss_char, p_rec.attribute52),
1867 DECODE(p_rec.attribute53,null, fnd_api.g_miss_char, p_rec.attribute53),
1868 DECODE(p_rec.attribute54,null, fnd_api.g_miss_char, p_rec.attribute54),
1869 DECODE(p_rec.attribute55,null, fnd_api.g_miss_char, p_rec.attribute55),
1870 DECODE(p_rec.attribute56,null, fnd_api.g_miss_char, p_rec.attribute56),
1871 DECODE(p_rec.attribute57,null, fnd_api.g_miss_char, p_rec.attribute57),
1872 DECODE(p_rec.attribute58,null, fnd_api.g_miss_char, p_rec.attribute58),
1873 DECODE(p_rec.attribute59,null, fnd_api.g_miss_char, p_rec.attribute59),
1874 DECODE(p_rec.attribute60,null, fnd_api.g_miss_char, p_rec.attribute60),
1875 DECODE(p_rec.attribute61,null, fnd_api.g_miss_char, p_rec.attribute61),
1876 DECODE(p_rec.attribute62,null, fnd_api.g_miss_char, p_rec.attribute62),
1877 DECODE(p_rec.attribute63,null, fnd_api.g_miss_char, p_rec.attribute63),
1878 DECODE(p_rec.attribute64,null, fnd_api.g_miss_char, p_rec.attribute64),
1879 DECODE(p_rec.attribute65,null, fnd_api.g_miss_char, p_rec.attribute65),
1883 DECODE(p_rec.attribute69,null, fnd_api.g_miss_char, p_rec.attribute69),
1880 DECODE(p_rec.attribute66,null, fnd_api.g_miss_char, p_rec.attribute66),
1881 DECODE(p_rec.attribute67,null, fnd_api.g_miss_char, p_rec.attribute67),
1882 DECODE(p_rec.attribute68,null, fnd_api.g_miss_char, p_rec.attribute68),
1884 DECODE(p_rec.attribute70,null, fnd_api.g_miss_char, p_rec.attribute70),
1885 DECODE(p_rec.attribute71,null, fnd_api.g_miss_char, p_rec.attribute71),
1886 DECODE(p_rec.attribute72,null, fnd_api.g_miss_char, p_rec.attribute72),
1887 DECODE(p_rec.attribute73,null, fnd_api.g_miss_char, p_rec.attribute73),
1888 DECODE(p_rec.attribute74,null, fnd_api.g_miss_char, p_rec.attribute74),
1889 DECODE(p_rec.attribute75,null, fnd_api.g_miss_char, p_rec.attribute75),
1890 DECODE(p_rec.attribute76,null, fnd_api.g_miss_char, p_rec.attribute76),
1891 DECODE(p_rec.attribute77,null, fnd_api.g_miss_char, p_rec.attribute77),
1892 DECODE(p_rec.attribute78,null, fnd_api.g_miss_char, p_rec.attribute78),
1893 DECODE(p_rec.attribute79,null, fnd_api.g_miss_char, p_rec.attribute79),
1894 DECODE(p_rec.attribute80,null, fnd_api.g_miss_char, p_rec.attribute80),
1895 DECODE(p_rec.attribute81,null, fnd_api.g_miss_char, p_rec.attribute81),
1896 DECODE(p_rec.attribute82,null, fnd_api.g_miss_char, p_rec.attribute82),
1897 DECODE(p_rec.attribute83,null, fnd_api.g_miss_char, p_rec.attribute83),
1898 DECODE(p_rec.attribute84,null, fnd_api.g_miss_char, p_rec.attribute84),
1899 DECODE(p_rec.attribute85,null, fnd_api.g_miss_char, p_rec.attribute85),
1900 DECODE(p_rec.attribute86,null, fnd_api.g_miss_char, p_rec.attribute86),
1901 DECODE(p_rec.attribute87,null, fnd_api.g_miss_char, p_rec.attribute87),
1902 DECODE(p_rec.attribute88,null, fnd_api.g_miss_char, p_rec.attribute88),
1903 DECODE(p_rec.attribute89,null, fnd_api.g_miss_char, p_rec.attribute89),
1904 DECODE(p_rec.attribute90,null, fnd_api.g_miss_char, p_rec.attribute90),
1905 DECODE(p_rec.attribute91,null, fnd_api.g_miss_char, p_rec.attribute91),
1906 DECODE(p_rec.attribute92,null, fnd_api.g_miss_char, p_rec.attribute92),
1907 DECODE(p_rec.attribute93,null, fnd_api.g_miss_char, p_rec.attribute93),
1908 DECODE(p_rec.attribute94,null, fnd_api.g_miss_char, p_rec.attribute94),
1909 DECODE(p_rec.attribute95,null, fnd_api.g_miss_char, p_rec.attribute95),
1910 DECODE(p_rec.attribute96,null, fnd_api.g_miss_char, p_rec.attribute96),
1911 DECODE(p_rec.attribute97,null, fnd_api.g_miss_char, p_rec.attribute97),
1912 DECODE(p_rec.attribute98,null, fnd_api.g_miss_char, p_rec.attribute98),
1913 DECODE(p_rec.attribute99,null, fnd_api.g_miss_char, p_rec.attribute99),
1914 DECODE(p_rec.attribute100,null, fnd_api.g_miss_char, p_rec.attribute100),
1915 DECODE(p_rec.direct_salesrep_number,null,fnd_api.g_miss_char, p_rec.direct_salesrep_number),
1916 DECODE(p_rec.comm_lines_api_id,null,fnd_api.g_miss_num, p_rec.comm_lines_api_id),
1917 DECODE(p_rec.rollup_date,NULL, fnd_api.g_miss_date,
1918 p_rec.rollup_date),
1919 DECODE(p_rec.source_doc_type,NULL, fnd_api.g_miss_char,
1920 p_rec.source_doc_type),
1921 DECODE(p_rec.orig_currency_code,NULL, fnd_api.g_miss_char,
1922 p_rec.orig_currency_code),
1923 DECODE(p_rec.exchange_rate,NULL, fnd_api.g_miss_num,
1924 p_rec.exchange_rate),
1925 DECODE(p_rec.transaction_amount_orig,NULL, fnd_api.g_miss_num,
1926 p_rec.transaction_amount_orig),
1927 DECODE(p_rec.trx_id,NULL, fnd_api.g_miss_num,
1928 p_rec.trx_id),
1929 DECODE(p_rec.trx_line_id,NULL, fnd_api.g_miss_num,
1930 p_rec.trx_line_id),
1931 DECODE(p_rec.trx_sales_line_id,NULL, fnd_api.g_miss_num,
1932 p_rec.trx_sales_line_id),
1933 DECODE(p_rec.quantity,NULL, fnd_api.g_miss_num,
1934 p_rec.quantity),
1935 DECODE(p_rec.source_trx_number,NULL, fnd_api.g_miss_char,
1936 p_rec.source_trx_number),
1937 DECODE(p_rec.discount_percentage,NULL, fnd_api.g_miss_num,
1938 p_rec.discount_percentage),
1939 DECODE(p_rec.margin_percentage,NULL, fnd_api.g_miss_num,
1940 p_rec.margin_percentage),
1941 DECODE(p_rec.forecast_id,NULL, fnd_api.g_miss_num,
1942 p_rec.forecast_id),
1943 DECODE(p_rec.upside_quantity,NULL, fnd_api.g_miss_num,
1944 p_rec.upside_quantity),
1945 DECODE(p_rec.upside_amount,NULL, fnd_api.g_miss_num,
1946 p_rec.upside_amount),
1947 DECODE(p_rec.uom_code,NULL, fnd_api.g_miss_char,
1948 p_rec.uom_code),
1949 DECODE(p_rec.source_trx_id,NULL, fnd_api.g_miss_num,
1950 p_rec.source_trx_id),
1951 DECODE(p_rec.source_trx_line_id,NULL, fnd_api.g_miss_num,
1952 p_rec.source_trx_line_id),
1953 DECODE(p_rec.source_trx_sales_line_id,NULL, fnd_api.g_miss_num,
1954 p_rec.source_trx_sales_line_id),
1955 DECODE(p_rec.customer_id, NULL, fnd_api.g_miss_num, p_rec.customer_id),
1956 DECODE(p_rec.inventory_item_id, NULL, fnd_api.g_miss_num,p_rec.inventory_item_id),
1957 DECODE(p_rec.order_number,NULL, fnd_api.g_miss_num,
1958 p_rec.order_number),
1959 DECODE(p_rec.order_date,NULL, fnd_api.g_miss_date,
1960 p_rec.order_date),
1961 DECODE(p_rec.invoice_number,NULL, fnd_api.g_miss_char,
1962 p_rec.invoice_number),
1963 DECODE(p_rec.invoice_date,NULL, fnd_api.g_miss_date,
1964 p_rec.invoice_date),
1965 DECODE(p_rec.bill_to_address_id,NULL, fnd_api.g_miss_num,
1966 p_rec.bill_to_address_id),
1967 DECODE(p_rec.ship_to_address_id,NULL, fnd_api.g_miss_num,
1968 p_rec.ship_to_address_id),
1969 DECODE(p_rec.bill_to_contact_id,NULL, fnd_api.g_miss_num,
1970 p_rec.bill_to_contact_id),
1971 DECODE(p_rec.ship_to_contact_id,NULL, fnd_api.g_miss_num,
1972 p_rec.ship_to_contact_id),
1973 DECODE(p_rec.adj_comm_lines_api_id,NULL, fnd_api.g_miss_num,
1974 p_rec.adj_comm_lines_api_id),
1975 DECODE(p_rec.adjust_date,NULL, fnd_api.g_miss_date,
1976 p_rec.adjust_date),
1977 DECODE(p_rec.adjusted_by,NULL, fnd_api.g_miss_char,
1978 p_rec.adjusted_by),
1979 DECODE(p_rec.revenue_type,NULL, fnd_api.g_miss_char,
1980 p_rec.revenue_type),
1981 DECODE(p_rec.adjust_rollup_flag,NULL, fnd_api.g_miss_char,
1982 p_rec.adjust_rollup_flag),
1983 DECODE(p_rec.adjust_comments,NULL, fnd_api.g_miss_char,
1984 p_rec.adjust_comments),
1985 DECODE(p_rec.adjust_status,NULL, fnd_api.g_miss_char,
1986 p_rec.adjust_status),
1987 DECODE(p_rec.line_number,NULL, fnd_api.g_miss_num,
1988 p_rec.line_number),
1989 DECODE(p_rec.reason_code,NULL, fnd_api.g_miss_char,
1990 p_rec.reason_code),
1991 DECODE(p_rec.attribute_category,NULL, fnd_api.g_miss_char,
1992 p_rec.attribute_category),
1993 DECODE(p_rec.type,NULL, fnd_api.g_miss_char,
1994 p_rec.type),
1995 DECODE(p_rec.pre_processed_code,NULL, fnd_api.g_miss_char,
1996 p_rec.pre_processed_code),
1997 DECODE(p_rec.quota_id,NULL, fnd_api.g_miss_num,
1998 p_rec.quota_id),
1999 DECODE(p_rec.srp_plan_assign_id,NULL, fnd_api.g_miss_num,
2000 p_rec.srp_plan_assign_id),
2004 p_rec.comp_group_id),
2001 DECODE(p_rec.role_id,NULL, fnd_api.g_miss_num,
2002 p_rec.role_id),
2003 DECODE(p_rec.comp_group_id,NULL, fnd_api.g_miss_num,
2005 DECODE(p_rec.commission_amount,NULL, fnd_api.g_miss_num,
2006 p_rec.commission_amount),
2007 DECODE(p_rec.sales_channel,NULL, fnd_api.g_miss_char,
2008 p_rec.sales_channel),
2009 DECODE(p_rec.split_pct,NULL, fnd_api.g_miss_num,
2010 p_rec.split_pct),
2011 DECODE(p_rec.split_status,NULL, fnd_api.g_miss_char,
2012 p_rec.split_status),
2013 DECODE(p_rec.org_id,NULL, fnd_api.g_miss_num,
2014 p_rec.org_id),
2015 DECODE(p_rec.terr_id,NULL, fnd_api.g_miss_num,
2016 p_rec.terr_id),
2017 DECODE(p_rec.preserve_credit_override_flag,NULL, fnd_api.g_miss_char,
2018 p_rec.preserve_credit_override_flag)
2019 INTO x_api_rec.direct_salesrep_id,x_api_rec.processed_date,
2020 x_api_rec.processed_period_id,x_api_rec.transaction_amount,
2021 x_api_rec.trx_type,x_api_rec.revenue_class_id,
2022 x_api_rec.load_status,
2023 x_api_rec.attribute1,x_api_rec.attribute2,
2024 x_api_rec.attribute3,x_api_rec.attribute4,
2025 x_api_rec.attribute5,x_api_rec.attribute6,
2026 x_api_rec.attribute7,x_api_rec.attribute8,
2027 x_api_rec.attribute9,x_api_rec.attribute10,
2028 x_api_rec.attribute11,x_api_rec.attribute12,
2029 x_api_rec.attribute13,x_api_rec.attribute14,
2030 x_api_rec.attribute15,x_api_rec.attribute16,
2031 x_api_rec.attribute17,x_api_rec.attribute18,
2032 x_api_rec.attribute19,x_api_rec.attribute20,
2033 x_api_rec.attribute21,x_api_rec.attribute22,
2034 x_api_rec.attribute23,x_api_rec.attribute24,
2035 x_api_rec.attribute25,x_api_rec.attribute26,
2036 x_api_rec.attribute27,x_api_rec.attribute28,
2037 x_api_rec.attribute29,x_api_rec.attribute30,
2038 x_api_rec.attribute31,x_api_rec.attribute32,
2039 x_api_rec.attribute33,x_api_rec.attribute34,
2040 x_api_rec.attribute35,x_api_rec.attribute36,
2041 x_api_rec.attribute37,x_api_rec.attribute38,
2042 x_api_rec.attribute39,x_api_rec.attribute40,
2043 x_api_rec.attribute41,x_api_rec.attribute42,
2044 x_api_rec.attribute43,x_api_rec.attribute44,
2045 x_api_rec.attribute45,x_api_rec.attribute46,
2046 x_api_rec.attribute47,x_api_rec.attribute48,
2047 x_api_rec.attribute49,x_api_rec.attribute50,
2048 x_api_rec.attribute51,x_api_rec.attribute52,
2049 x_api_rec.attribute53,x_api_rec.attribute54,
2050 x_api_rec.attribute55,x_api_rec.attribute56,
2051 x_api_rec.attribute57,x_api_rec.attribute58,
2052 x_api_rec.attribute59,x_api_rec.attribute60,
2053 x_api_rec.attribute61,x_api_rec.attribute62,
2054 x_api_rec.attribute63,x_api_rec.attribute64,
2055 x_api_rec.attribute65,x_api_rec.attribute66,
2056 x_api_rec.attribute67,x_api_rec.attribute68,
2057 x_api_rec.attribute69,x_api_rec.attribute70,
2058 x_api_rec.attribute71,x_api_rec.attribute72,
2059 x_api_rec.attribute73,x_api_rec.attribute74,
2060 x_api_rec.attribute75,x_api_rec.attribute76,
2061 x_api_rec.attribute77,x_api_rec.attribute78,
2062 x_api_rec.attribute79,x_api_rec.attribute80,
2063 x_api_rec.attribute81,x_api_rec.attribute82,
2064 x_api_rec.attribute83,x_api_rec.attribute84,
2065 x_api_rec.attribute85,x_api_rec.attribute86,
2066 x_api_rec.attribute87,x_api_rec.attribute88,
2067 x_api_rec.attribute89,x_api_rec.attribute90,
2068 x_api_rec.attribute91,x_api_rec.attribute92,
2069 x_api_rec.attribute93,x_api_rec.attribute94,
2070 x_api_rec.attribute95,x_api_rec.attribute96,
2071 x_api_rec.attribute97,x_api_rec.attribute98,
2072 x_api_rec.attribute99,x_api_rec.attribute100,
2073 x_api_rec.direct_salesrep_number,x_api_rec.comm_lines_api_id,
2074 x_api_rec.rollup_date,
2075 x_api_rec.source_doc_type,
2076 x_api_rec.orig_currency_code,
2077 x_api_rec.exchange_rate,
2078 x_api_rec.transaction_amount_orig,
2079 x_api_rec.trx_id,x_api_rec.trx_line_id,
2080 x_api_rec.trx_sales_line_id,x_api_rec.quantity,
2081 x_api_rec.source_trx_number,
2082 x_api_rec.discount_percentage,
2083 x_api_rec.margin_percentage,
2084 x_api_rec.forecast_id,
2085 x_api_rec.upside_quantity,x_api_rec.upside_amount,
2086 x_api_rec.uom_code,x_api_rec.source_trx_id,
2087 x_api_rec.source_trx_line_id,
2088 x_api_rec.source_trx_sales_line_id,
2089 x_api_rec.customer_id,
2090 x_api_rec.inventory_item_id,x_api_rec.order_number,
2091 x_api_rec.order_date,x_api_rec.invoice_number,
2092 x_api_rec.invoice_date,x_api_rec.bill_to_address_id,
2093 x_api_rec.ship_to_address_id,x_api_rec.bill_to_contact_id,
2094 x_api_rec.ship_to_contact_id,x_api_rec.adj_comm_lines_api_id,
2095 x_api_rec.adjust_date,x_api_rec.adjusted_by,
2096 x_api_rec.revenue_type,x_api_rec.adjust_rollup_flag,
2097 x_api_rec.adjust_comments,x_api_rec.adjust_status,
2098 x_api_rec.line_number,x_api_rec.reason_code,
2099 x_api_rec.attribute_category,x_api_rec.type,
2100 x_api_rec.pre_processed_code,x_api_rec.quota_id,
2101 x_api_rec.srp_plan_assign_id,x_api_rec.role_id,
2102 x_api_rec.comp_group_id,x_api_rec.commission_amount,
2103 x_api_rec.sales_channel,x_api_rec.split_pct,
2104 x_api_rec.split_status,
2105 x_api_rec.org_id,
2106 x_api_rec.terr_id,
2107 x_api_rec.preserve_credit_override_flag
2108 FROM DUAL;
2109 END;
2110 --
2111 END;
2112
2113