[Home] [Help]
PACKAGE BODY: APPS.CN_ADJUSTMENTS_PKG
Source
1 PACKAGE BODY CN_ADJUSTMENTS_PKG AS
2 -- $Header: cntradjb.pls 120.10.12010000.2 2008/11/04 07:41:52 sseshaiy ship $
3 -- +======================================================================+
4 -- | Copyright (c) 1994 Oracle Corporation |
5 -- | Redwood Shores, California, USA |
6 -- | All rights reserved. |
7 -- +======================================================================+
8 --
9 -- Package Name
10 -- cn_adjustments_pkg
11 -- Purpose
12 -- Package spec for notifying orders
13 -- History
14 -- 11/17/98 JPENDYAL Created
15 -- 07/22/04 HITHANKI Modified For Bug Fix : 3784174
16 -- Added Comma In GET_CUST_INFO Queries
17 --
18 -- Jun 26, 2006 vensrini Bug fix 5349170
19 --
20
21
22 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_ADJUSTMENTS_PKG';
23 G_FILE_NAME CONSTANT VARCHAR2(12) := 'cntradjb.pls';
24 --
25 FUNCTION g_track_invoice
26 RETURN VARCHAR2 IS
27 l_track_invoice VARCHAR2(1) := 'N';
28 BEGIN
29 l_track_invoice := NVL(fnd_profile.value('CN_TRACK_INVOICE'),'N');
30 RETURN l_track_invoice;
31 EXCEPTION
32 WHEN OTHERS THEN
33 RETURN l_track_invoice;
34 END;
35 --
36 PROCEDURE mass_adjust_build_query(
37 x_where_clause VARCHAR2) IS
38 --
39 select_cursor NUMBER(15);
40 sql_stmt VARCHAR2(5000);
41 count_rows NUMBER;
42 l_comm_lines_api_id NUMBER;
43 i BINARY_INTEGER := 1;
44 BEGIN
45 select_cursor := DBMS_SQL.open_cursor;
46 sql_stmt := 'SELECT COMM_LINES_API_ID FROM CN_ADJUSTMENTS_V WHERE ';
47 sql_stmt := sql_stmt || x_where_clause;
48 dbms_sql.parse(select_cursor,sql_stmt,DBMS_SQL.NATIVE);
49 DBMS_SQL.define_column (select_cursor,1,l_comm_lines_api_id);
50 count_rows := DBMS_SQL.execute (select_cursor);
51 tab_mass_update_comm.delete;
52 LOOP
53 IF (dbms_sql.fetch_rows(select_cursor) > 0) THEN
54 DBMS_SQL.column_value (select_cursor,1,l_comm_lines_api_id);
55 tab_mass_update_comm(i) := l_comm_lines_api_id;
56 i := i + 1;
57 ELSE
58 EXIT;
59 END IF;
60 END LOOP;
61 DBMS_SQL.close_cursor(select_cursor);
62 END mass_adjust_build_query;
63 --
64
65 -- Negate a transaction (original transaction t1)
66 -- If t1 was in API table (not loaded) then
67 -- 1. adjust_status = 'FROZEN'
68 -- 2. load_status = 'OBSOLETE'
69 -- ELSIF t1 was in HEADER table (loaded) then
70 -- 1. adjust_status = 'FROZEN'
71 -- 2. update reversal_header_id, reversal_flag for t1
72 -- 3. insert REVERSAL trx t1' into API table
73
74 PROCEDURE api_negate_record(
75 x_comm_lines_api_id IN NUMBER,
76 x_adjusted_by IN VARCHAR2,
77 x_adjust_comments IN VARCHAR2,
78 x_salesrep_number IN VARCHAR2 DEFAULT NULL) IS
79
80 l_comm_lines_api_id NUMBER;
81 --Added for Crediting issue
82 l_terr_id NUMBER;
83 l_org_id NUMBER;
84 l_adjusted_by VARCHAR2(100);
85 l_quantity NUMBER;
86 l_transaction_amount NUMBER;
87 l_src_transaction_amount NUMBER;
88 l_acctd_transaction_amount NUMBER;
89 l_negate_flag VARCHAR2(1);
90 l_adjust_status VARCHAR2(20);
91 l_adjust_date DATE := SYSDATE;
92 l_status VARCHAR2(10);
93 l_reversal_header_id NUMBER;
94 l_reversal_flag VARCHAR2(1);
95 l_next_step CHAR(1) := 'Y';
96 l_load_status cn_comm_lines_api.load_status%TYPE;
97 l_trx_type cn_commission_headers.trx_type%TYPE;
98 l_transaction_amount_orig cn_commission_headers.transaction_amount_orig%TYPE;
99 -- PL/SQL tables/columns
100 l_api_rec cn_comm_lines_api_pkg.comm_lines_api_rec_type;
101 -- Added for bug 7524578
102 l_territory_id NUMBER;
103 l_terr_name VARCHAR2(2000);
104 l_presrv_credit_override_flag VARCHAR2(1);
105 --
106 CURSOR api_cur IS
107 SELECT *
108 FROM cn_comm_lines_api
109 WHERE comm_lines_api_id = x_comm_lines_api_id;
110 --
111 CURSOR header_cur(l_commission_header_id NUMBER) IS
112 SELECT a.*,b.employee_number
113 FROM cn_commission_headers a,
114 cn_salesreps b
115 WHERE a.direct_salesrep_id = b.salesrep_id
116 AND commission_header_id = l_commission_header_id;
117
118 -- Added for bug 7524578
119 CURSOR api_cur_terr IS
120 SELECT terr_id,terr_name,preserve_credit_override_flag
121 FROM cn_comm_lines_api
122 WHERE comm_lines_api_id = x_comm_lines_api_id;
123
124 BEGIN
125 -- First check whether the record is available in cn_comm_lines_api table.
126 BEGIN
127 SELECT adjust_status,load_status,quantity,
128 acctd_transaction_amount,transaction_amount
129 INTO l_adjust_status,l_load_status,l_quantity,
130 l_acctd_transaction_amount,l_transaction_amount
131 FROM cn_comm_lines_api
132 WHERE comm_lines_api_id = x_comm_lines_api_id
133 AND load_status <> 'LOADED'
134 AND (adjust_status NOT IN ('FROZEN','REVERSAL') OR
135 adjust_status IS NULL);
136 --
137 UPDATE cn_comm_lines_api api
138 SET load_status = 'OBSOLETE',
139 adjust_status = 'FROZEN',
140 adjust_date = l_adjust_date,
141 adjusted_by = x_adjusted_by,
142 adjust_comments = x_adjust_comments
143 WHERE comm_lines_api_id = x_comm_lines_api_id;
144 --
145 l_next_step := 'N'; -- It need not check in cn_commission_headers table.
146 EXCEPTION
147 WHEN OTHERS THEN
148 NULL;
149 END;
150 --
151
152 FOR each_trx IN api_cur_terr
153 LOOP
154 l_territory_id := each_trx.terr_id;
155 l_terr_name := each_trx.terr_name;
156 l_presrv_credit_override_flag := each_trx.preserve_credit_override_flag;
157
158 END LOOP;
159
160 IF (l_next_step = 'Y') THEN
161 BEGIN
162 SELECT commission_header_id,adjust_status,quantity,trx_type,
163 transaction_amount,transaction_amount_orig
164 INTO l_reversal_header_id,l_adjust_status,l_quantity,l_trx_type,
165 l_transaction_amount,l_transaction_amount_orig
166 FROM cn_commission_headers
167 WHERE comm_lines_api_id = x_comm_lines_api_id
168 AND (adjust_status NOT IN ('FROZEN','REVERSAL') OR
169 adjust_status IS NULL)
170 AND trx_type NOT IN ('ITD','GRP','THR');
171 --
172 l_quantity := -1 * l_quantity;
173 l_src_transaction_amount := -1 * NVL(l_transaction_amount_orig,0);
174 l_acctd_transaction_amount := -1 * NVL(l_transaction_amount,0);
175 l_negate_flag := 'Y';
176 l_adjust_status := 'REVERSAL';
177 l_reversal_flag := 'Y';
178 --
179 UPDATE cn_commission_headers
180 SET adjust_status = 'FROZEN',
181 reversal_header_id = l_reversal_header_id,
182 reversal_flag = l_reversal_flag,
183 adjust_date = l_adjust_date,
184 adjusted_by = x_adjusted_by,
185 adjust_comments = x_adjust_comments,
186 -- clku, update the last updated info
187 last_update_date = sysdate,
188 last_updated_by = fnd_global.user_id,
189 last_update_login = fnd_global.login_id
190 WHERE comm_lines_api_id = x_comm_lines_api_id;
191 FOR api_curs_rec IN header_cur(l_reversal_header_id)
192 LOOP
193 --
194 SELECT cn_comm_lines_api_s.NEXTVAL
195 INTO l_comm_lines_api_id
196 FROM dual;
197 --
198 l_api_rec.salesrep_id := api_curs_rec.direct_salesrep_id;
199 l_api_rec.processed_date := api_curs_rec.processed_date;
200 l_api_rec.processed_period_id := api_curs_rec.processed_period_id;
201 l_api_rec.transaction_amount := l_src_transaction_amount;
202 l_api_rec.trx_type := api_curs_rec.trx_type;
203 l_api_rec.revenue_class_id := api_curs_rec.revenue_class_id;
204 l_api_rec.load_status := 'UNLOADED';
205 l_api_rec.attribute1 := api_curs_rec.attribute1;
206 l_api_rec.attribute2 := api_curs_rec.attribute2;
207 l_api_rec.attribute3 := api_curs_rec.attribute3;
208 l_api_rec.attribute4 := api_curs_rec.attribute4;
209 l_api_rec.attribute5 := api_curs_rec.attribute5;
210 l_api_rec.attribute6 := api_curs_rec.attribute6;
211 l_api_rec.attribute7 := api_curs_rec.attribute7;
212 l_api_rec.attribute8 := api_curs_rec.attribute8;
213 l_api_rec.attribute9 := api_curs_rec.attribute9;
214 l_api_rec.attribute10 := api_curs_rec.attribute10;
215 l_api_rec.attribute11 := api_curs_rec.attribute11;
216 l_api_rec.attribute12 := api_curs_rec.attribute12;
217 l_api_rec.attribute13 := api_curs_rec.attribute13;
218 l_api_rec.attribute14 := api_curs_rec.attribute14;
219 l_api_rec.attribute15 := api_curs_rec.attribute15;
220 l_api_rec.attribute16 := api_curs_rec.attribute16;
221 l_api_rec.attribute17 := api_curs_rec.attribute17;
222 l_api_rec.attribute18 := api_curs_rec.attribute18;
223 l_api_rec.attribute19 := api_curs_rec.attribute19;
224 l_api_rec.attribute20 := api_curs_rec.attribute20;
225 l_api_rec.attribute21 := api_curs_rec.attribute21;
226 l_api_rec.attribute22 := api_curs_rec.attribute22;
227 l_api_rec.attribute23 := api_curs_rec.attribute23;
228 l_api_rec.attribute24 := api_curs_rec.attribute24;
229 l_api_rec.attribute25 := api_curs_rec.attribute25;
230 l_api_rec.attribute26 := api_curs_rec.attribute26;
231 l_api_rec.attribute27 := api_curs_rec.attribute27;
232 l_api_rec.attribute28 := api_curs_rec.attribute28;
233 l_api_rec.attribute29 := api_curs_rec.attribute29;
234 l_api_rec.attribute30 := api_curs_rec.attribute30;
235 l_api_rec.attribute31 := api_curs_rec.attribute31;
236 l_api_rec.attribute32 := api_curs_rec.attribute32;
237 l_api_rec.attribute33 := api_curs_rec.attribute33;
238 l_api_rec.attribute34 := api_curs_rec.attribute34;
239 l_api_rec.attribute35 := api_curs_rec.attribute35;
240 l_api_rec.attribute36 := api_curs_rec.attribute36;
241 l_api_rec.attribute37 := api_curs_rec.attribute37;
242 l_api_rec.attribute38 := api_curs_rec.attribute38;
243 l_api_rec.attribute39 := api_curs_rec.attribute39;
244 l_api_rec.attribute40 := api_curs_rec.attribute40;
245 l_api_rec.attribute41 := api_curs_rec.attribute41;
246 l_api_rec.attribute42 := api_curs_rec.attribute42;
247 l_api_rec.attribute43 := api_curs_rec.attribute43;
248 l_api_rec.attribute44 := api_curs_rec.attribute44;
249 l_api_rec.attribute45 := api_curs_rec.attribute45;
250 l_api_rec.attribute46 := api_curs_rec.attribute46;
251 l_api_rec.attribute47 := api_curs_rec.attribute47;
252 l_api_rec.attribute48 := api_curs_rec.attribute48;
253 l_api_rec.attribute49 := api_curs_rec.attribute49;
254 l_api_rec.attribute50 := api_curs_rec.attribute50;
255 l_api_rec.attribute51 := api_curs_rec.attribute51;
256 l_api_rec.attribute52 := api_curs_rec.attribute52;
257 l_api_rec.attribute53 := api_curs_rec.attribute53;
258 l_api_rec.attribute54 := api_curs_rec.attribute54;
259 l_api_rec.attribute55 := api_curs_rec.attribute55;
260 l_api_rec.attribute56 := api_curs_rec.attribute56;
261 l_api_rec.attribute57 := api_curs_rec.attribute57;
262 l_api_rec.attribute58 := api_curs_rec.attribute58;
263 l_api_rec.attribute59 := api_curs_rec.attribute59;
264 l_api_rec.attribute60 := api_curs_rec.attribute60;
265 l_api_rec.attribute61 := api_curs_rec.attribute61;
266 l_api_rec.attribute62 := api_curs_rec.attribute62;
267 l_api_rec.attribute63 := api_curs_rec.attribute63;
268 l_api_rec.attribute64 := api_curs_rec.attribute64;
269 l_api_rec.attribute65 := api_curs_rec.attribute65;
270 l_api_rec.attribute66 := api_curs_rec.attribute66;
271 l_api_rec.attribute67 := api_curs_rec.attribute67;
272 l_api_rec.attribute68 := api_curs_rec.attribute68;
273 l_api_rec.attribute69 := api_curs_rec.attribute69;
274 l_api_rec.attribute70 := api_curs_rec.attribute70;
275 l_api_rec.attribute71 := api_curs_rec.attribute71;
276 l_api_rec.attribute72 := api_curs_rec.attribute72;
277 l_api_rec.attribute73 := api_curs_rec.attribute73;
278 l_api_rec.attribute74 := api_curs_rec.attribute74;
279 l_api_rec.attribute75 := api_curs_rec.attribute75;
280 l_api_rec.attribute76 := api_curs_rec.attribute76;
281 l_api_rec.attribute77 := api_curs_rec.attribute77;
282 l_api_rec.attribute78 := api_curs_rec.attribute78;
283 l_api_rec.attribute79 := api_curs_rec.attribute79;
284 l_api_rec.attribute80 := api_curs_rec.attribute80;
285 l_api_rec.attribute81 := api_curs_rec.attribute81;
286 l_api_rec.attribute82 := api_curs_rec.attribute82;
287 l_api_rec.attribute83 := api_curs_rec.attribute83;
288 l_api_rec.attribute84 := api_curs_rec.attribute84;
289 l_api_rec.attribute85 := api_curs_rec.attribute85;
290 l_api_rec.attribute86 := api_curs_rec.attribute86;
291 l_api_rec.attribute87 := api_curs_rec.attribute87;
292 l_api_rec.attribute88 := api_curs_rec.attribute88;
293 l_api_rec.attribute89 := api_curs_rec.attribute89;
294 l_api_rec.attribute90 := api_curs_rec.attribute90;
295 l_api_rec.attribute91 := api_curs_rec.attribute91;
296 l_api_rec.attribute92 := api_curs_rec.attribute92;
297 l_api_rec.attribute93 := api_curs_rec.attribute93;
298 l_api_rec.attribute94 := api_curs_rec.attribute94;
299 l_api_rec.attribute95 := api_curs_rec.attribute95;
300 l_api_rec.attribute96 := api_curs_rec.attribute96;
301 l_api_rec.attribute97 := api_curs_rec.attribute97;
302 l_api_rec.attribute98 := api_curs_rec.attribute98;
303 l_api_rec.attribute99 := api_curs_rec.attribute99;
304 l_api_rec.attribute100 := api_curs_rec.attribute100;
305 l_api_rec.employee_number := api_curs_rec.employee_number;
306 l_api_rec.comm_lines_api_id := l_comm_lines_api_id;
307 l_api_rec.conc_batch_id := NULL;
308 l_api_rec.process_batch_id := NULL;
309 -- l_api_rec.salesrep_number
310 -- := api_curs_rec.employee_number;
311 -- obsoleted column bug2131915
312 l_api_rec.salesrep_number := null;
313 l_api_rec.rollup_date := api_curs_rec.rollup_date;
314 --l_api_rec.rollup_period_id := NULL;
315 l_api_rec.source_doc_id := NULL;
316 l_api_rec.source_doc_type := api_curs_rec.source_doc_type;
317 l_api_rec.transaction_currency_code := api_curs_rec.orig_currency_code;
321 l_api_rec.trx_line_id := NULL;
318 l_api_rec.exchange_rate := api_curs_rec.exchange_rate;
319 l_api_rec.acctd_transaction_amount := l_acctd_transaction_amount;
320 l_api_rec.trx_id := NULL;
322 l_api_rec.trx_sales_line_id := NULL;
323 l_api_rec.quantity := l_quantity;
324 l_api_rec.source_trx_number := api_curs_rec.source_trx_number;
325 l_api_rec.discount_percentage := api_curs_rec.discount_percentage;
326 l_api_rec.margin_percentage := api_curs_rec.margin_percentage;
327 l_api_rec.pre_defined_rc_flag := NULL;
328 l_api_rec.rollup_flag := NULL;
329 l_api_rec.forecast_id := api_curs_rec.forecast_id;
330 l_api_rec.upside_quantity := api_curs_rec.upside_quantity;
331 l_api_rec.upside_amount := api_curs_rec.upside_amount;
332 l_api_rec.uom_code := api_curs_rec.uom_code;
333 l_api_rec.source_trx_id := api_curs_rec.source_trx_id;
334 l_api_rec.source_trx_line_id := api_curs_rec.source_trx_line_id;
335 l_api_rec.source_trx_sales_line_id := api_curs_rec.source_trx_sales_line_id;
336 l_api_rec.negated_flag := l_negate_flag;
337 l_api_rec.customer_id := api_curs_rec.customer_id;
338 l_api_rec.inventory_item_id := api_curs_rec.inventory_item_id;
339 l_api_rec.order_number := api_curs_rec.order_number;
340 l_api_rec.booked_date := api_curs_rec.booked_date;
341 l_api_rec.invoice_number := api_curs_rec.invoice_number;
342 l_api_rec.invoice_date := api_curs_rec.invoice_date;
343 l_api_rec.bill_to_address_id := api_curs_rec.bill_to_address_id;
344 l_api_rec.ship_to_address_id := api_curs_rec.ship_to_address_id;
345 l_api_rec.bill_to_contact_id := api_curs_rec.bill_to_contact_id;
346 l_api_rec.ship_to_contact_id := api_curs_rec.ship_to_contact_id;
347 l_api_rec.adj_comm_lines_api_id := api_curs_rec.comm_lines_api_id;
348 l_api_rec.adjust_date := l_adjust_date;
349 l_api_rec.adjusted_by := x_adjusted_by;
350 l_api_rec.revenue_type := api_curs_rec.revenue_type;
351 l_api_rec.adjust_rollup_flag := api_curs_rec.adjust_rollup_flag;
352 l_api_rec.adjust_comments := x_adjust_comments;
353 l_api_rec.adjust_status := l_adjust_status;
354 l_api_rec.line_number := api_curs_rec.line_number;
355 /* codeCheck: Is it correct? */
356 l_api_rec.reason_code := api_curs_rec.reason_code;
357 l_api_rec.attribute_category := api_curs_rec.attribute_category;
358 l_api_rec.type := api_curs_rec.type;
359 l_api_rec.pre_processed_code := api_curs_rec.pre_processed_code;
360 l_api_rec.quota_id := api_curs_rec.quota_id;
361 l_api_rec.srp_plan_assign_id := api_curs_rec.srp_plan_assign_id;
362 l_api_rec.role_id := api_curs_rec.role_id;
363 l_api_rec.comp_group_id := api_curs_rec.comp_group_id;
364 /* codeCheck: Is it correct? */
365 l_api_rec.commission_amount := NULL;
366 l_api_rec.reversal_flag := l_reversal_flag;
367 l_api_rec.reversal_header_id := l_reversal_header_id;
368 l_api_rec.sales_channel := api_curs_rec.sales_channel;
369 l_api_rec.split_pct := api_curs_rec.split_pct;
370 l_api_rec.split_status := api_curs_rec.split_status;
371 l_api_rec.org_id := api_curs_rec.org_id; -- vensrini.
372 l_api_rec.terr_id := l_territory_id;
373 l_api_rec.terr_name := l_terr_name;
374 l_api_rec.preserve_credit_override_flag := l_presrv_credit_override_flag;
375 --
376 cn_comm_lines_api_pkg.insert_row(l_api_rec);
377 --
378 END LOOP;
379 EXCEPTION
380 WHEN OTHERS THEN
381 NULL;
382 END;
383 END IF;
384
385 /* Added for Crediting Bug */
386 BEGIN
387 SELECT COMM_LINES_API_ID, TERR_ID, ORG_ID
388 INTO l_comm_lines_api_id, l_terr_id, l_org_id
389 FROM CN_COMM_LINES_API
390 WHERE COMM_LINES_API_ID = x_comm_lines_api_id;
391
392 update_credit_credentials(
393 l_comm_lines_api_id,
394 l_terr_id,
395 l_org_id,
396 x_adjusted_by
397 );
398 EXCEPTION
399 WHEN OTHERS THEN
400 NULL;
401 END;
402
403 END api_negate_record;
404
405 --
406 PROCEDURE mass_update_values(
407 x_adj_data cn_get_tx_data_pub.adj_tbl_type,
408 x_adj_rec cn_get_tx_data_pub.adj_rec_type,
409 x_mass_adj_type VARCHAR2,
410 x_proc_comp OUT NOCOPY VARCHAR2) IS
411
412 l_api_name CONSTANT VARCHAR2(30) := 'mass_update_values';
413 l_api_version CONSTANT NUMBER := 1.0;
414 l_validation_level VARCHAR2(100) := FND_API.G_VALID_LEVEL_FULL;
415 l_return_status VARCHAR2(2000);
416 l_msg_count NUMBER;
417 l_msg_data VARCHAR2(2000);
418 l_loading_status VARCHAR2(2000);
419 l_max_val NUMBER;
420 l_adjust_status VARCHAR2(20);
424 -- PL/SQL tables/columns
421 l_adjust_date DATE := SYSDATE;
422 l_comm_lines_api_id NUMBER;
423 l_counter NUMBER := 0;
425 l_api_rec cn_comm_lines_api_pkg.comm_lines_api_rec_type;
426 l_existing_data cn_invoice_changes_pvt.invoice_tbl;
427 l_new_data cn_invoice_changes_pvt.invoice_tbl;
428 --
429 BEGIN
430 x_proc_comp := 'N';
431
432 IF (x_adj_data.COUNT>0) THEN
433
434 FOR i in x_adj_data.first .. x_adj_data.last
435 LOOP
436 IF ((x_adj_data(i).adjust_status NOT IN('FROZEN','REVERSAL','SCA_PENDING') OR
437 x_adj_data(i).adjust_status IS null) AND
438 x_adj_data(i).trx_type NOT IN ('ITD','GRP','THR') AND
439 x_adj_data(i).load_status NOT IN ('FILTERED')) THEN
440 IF (x_mass_adj_type = 'M') THEN
441 l_adjust_status := 'MASSADJ';
442 ELSE
443 l_adjust_status := 'MASSASGN';
444 END IF;
445 --
446 SELECT cn_comm_lines_api_s.NEXTVAL
447 INTO l_comm_lines_api_id
448 FROM DUAL;
449 --
450
451
452 SELECT DECODE(x_adj_rec.direct_salesrep_id,fnd_api.g_miss_num,
453 DECODE(x_adj_data(i).direct_salesrep_id,fnd_api.g_miss_num,NULL,
454 x_adj_data(i).direct_salesrep_id),
455 x_adj_rec.direct_salesrep_id),
456 DECODE(x_adj_rec.processed_date,fnd_api.g_miss_date,
457 DECODE(x_adj_data(i).processed_date,fnd_api.g_miss_date,NULL,
458 x_adj_data(i).processed_date),
459 x_adj_rec.processed_date),
460 DECODE(x_adj_rec.processed_period_id,fnd_api.g_miss_num,
461 DECODE(x_adj_data(i).processed_period_id,fnd_api.g_miss_num,NULL,
462 x_adj_data(i).processed_period_id),
463 x_adj_rec.processed_period_id),
464 DECODE(x_adj_rec.transaction_amount,fnd_api.g_miss_num,
465 DECODE(x_adj_data(i).transaction_amount,fnd_api.g_miss_num,NULL,
466 x_adj_data(i).transaction_amount),
467 x_adj_rec.transaction_amount),
468 DECODE(x_adj_rec.trx_type,fnd_api.g_miss_char,
469 DECODE(x_adj_data(i).trx_type,fnd_api.g_miss_char,NULL,
470 x_adj_data(i).trx_type),x_adj_rec.trx_type),
471 DECODE(x_adj_rec.revenue_class_id,fnd_api.g_miss_num,
472 DECODE(x_adj_data(i).revenue_class_id,fnd_api.g_miss_num,NULL,
473 x_adj_data(i).revenue_class_id),
474 x_adj_rec.revenue_class_id),
475 'UNLOADED',
476 DECODE(x_adj_rec.attribute1,fnd_api.g_miss_char,
477 DECODE(x_adj_data(i).attribute1,fnd_api.g_miss_char,NULL,
478 x_adj_data(i).attribute1),x_adj_rec.attribute1),
479 DECODE(x_adj_rec.attribute2,fnd_api.g_miss_char,
480 DECODE(x_adj_data(i).attribute2,fnd_api.g_miss_char,NULL,
481 x_adj_data(i).attribute2),x_adj_rec.attribute2),
482 DECODE(x_adj_rec.attribute3,fnd_api.g_miss_char,
483 DECODE(x_adj_data(i).attribute3,fnd_api.g_miss_char,NULL,
484 x_adj_data(i).attribute3),x_adj_rec.attribute3),
485 DECODE(x_adj_rec.attribute4,fnd_api.g_miss_char,
486 DECODE(x_adj_data(i).attribute4,fnd_api.g_miss_char,NULL,
487 x_adj_data(i).attribute4),x_adj_rec.attribute4),
488 DECODE(x_adj_rec.attribute5,fnd_api.g_miss_char,
489 DECODE(x_adj_data(i).attribute5,fnd_api.g_miss_char,NULL,
490 x_adj_data(i).attribute5),x_adj_rec.attribute5),
491 DECODE(x_adj_rec.attribute6,fnd_api.g_miss_char,
492 DECODE(x_adj_data(i).attribute6,fnd_api.g_miss_char,NULL,
493 x_adj_data(i).attribute6),x_adj_rec.attribute6),
494 DECODE(x_adj_rec.attribute7,fnd_api.g_miss_char,
495 DECODE(x_adj_data(i).attribute7,fnd_api.g_miss_char,NULL,
496 x_adj_data(i).attribute7),x_adj_rec.attribute7),
497 DECODE(x_adj_rec.attribute8,fnd_api.g_miss_char,
498 DECODE(x_adj_data(i).attribute8,fnd_api.g_miss_char,NULL,
499 x_adj_data(i).attribute8),x_adj_rec.attribute8),
500 DECODE(x_adj_rec.attribute9,fnd_api.g_miss_char,
501 DECODE(x_adj_data(i).attribute9,fnd_api.g_miss_char,NULL,
502 x_adj_data(i).attribute9),x_adj_rec.attribute9),
503 DECODE(x_adj_rec.attribute10,fnd_api.g_miss_char,
504 DECODE(x_adj_data(i).attribute10,fnd_api.g_miss_char,NULL,
505 x_adj_data(i).attribute10),x_adj_rec.attribute10),
506 DECODE(x_adj_rec.attribute11,fnd_api.g_miss_char,
507 DECODE(x_adj_data(i).attribute11,fnd_api.g_miss_char,NULL,
508 x_adj_data(i).attribute11),x_adj_rec.attribute11),
509 DECODE(x_adj_rec.attribute12,fnd_api.g_miss_char,
510 DECODE(x_adj_data(i).attribute12,fnd_api.g_miss_char,NULL,
511 x_adj_data(i).attribute12),x_adj_rec.attribute12),
512 DECODE(x_adj_rec.attribute13,fnd_api.g_miss_char,
513 DECODE(x_adj_data(i).attribute13,fnd_api.g_miss_char,NULL,
514 x_adj_data(i).attribute13),x_adj_rec.attribute13),
515 DECODE(x_adj_rec.attribute14,fnd_api.g_miss_char,
516 DECODE(x_adj_data(i).attribute14,fnd_api.g_miss_char,NULL,
517 x_adj_data(i).attribute14),x_adj_rec.attribute14),
518 DECODE(x_adj_rec.attribute15,fnd_api.g_miss_char,
519 DECODE(x_adj_data(i).attribute15,fnd_api.g_miss_char,NULL,
523 x_adj_data(i).attribute16),x_adj_rec.attribute16),
520 x_adj_data(i).attribute15),x_adj_rec.attribute15),
521 DECODE(x_adj_rec.attribute16,fnd_api.g_miss_char,
522 DECODE(x_adj_data(i).attribute16,fnd_api.g_miss_char,NULL,
524 DECODE(x_adj_rec.attribute17,fnd_api.g_miss_char,
525 DECODE(x_adj_data(i).attribute17,fnd_api.g_miss_char,NULL,
526 x_adj_data(i).attribute17),x_adj_rec.attribute17),
527 DECODE(x_adj_rec.attribute18,fnd_api.g_miss_char,
528 DECODE(x_adj_data(i).attribute18,fnd_api.g_miss_char,NULL,
529 x_adj_data(i).attribute18),x_adj_rec.attribute18),
530 DECODE(x_adj_rec.attribute19,fnd_api.g_miss_char,
531 DECODE(x_adj_data(i).attribute19,fnd_api.g_miss_char,NULL,
532 x_adj_data(i).attribute19),x_adj_rec.attribute19),
533 DECODE(x_adj_rec.attribute20,fnd_api.g_miss_char,
534 DECODE(x_adj_data(i).attribute20,fnd_api.g_miss_char,NULL,
535 x_adj_data(i).attribute20),x_adj_rec.attribute20),
536 DECODE(x_adj_rec.attribute21,fnd_api.g_miss_char,
537 DECODE(x_adj_data(i).attribute21,fnd_api.g_miss_char,NULL,
538 x_adj_data(i).attribute21),x_adj_rec.attribute21),
539 DECODE(x_adj_rec.attribute22,fnd_api.g_miss_char,
540 DECODE(x_adj_data(i).attribute22,fnd_api.g_miss_char,NULL,
541 x_adj_data(i).attribute22),x_adj_rec.attribute22),
542 DECODE(x_adj_rec.attribute23,fnd_api.g_miss_char,
543 DECODE(x_adj_data(i).attribute23,fnd_api.g_miss_char,NULL,
544 x_adj_data(i).attribute23),x_adj_rec.attribute23),
545 DECODE(x_adj_rec.attribute24,fnd_api.g_miss_char,
546 DECODE(x_adj_data(i).attribute24,fnd_api.g_miss_char,NULL,
547 x_adj_data(i).attribute24),x_adj_rec.attribute24),
548 DECODE(x_adj_rec.attribute25,fnd_api.g_miss_char,
549 DECODE(x_adj_data(i).attribute25,fnd_api.g_miss_char,NULL,
550 x_adj_data(i).attribute25),x_adj_rec.attribute25),
551 DECODE(x_adj_rec.attribute26,fnd_api.g_miss_char,
552 DECODE(x_adj_data(i).attribute26,fnd_api.g_miss_char,NULL,
553 x_adj_data(i).attribute26),x_adj_rec.attribute26),
554 DECODE(x_adj_rec.attribute27,fnd_api.g_miss_char,
555 DECODE(x_adj_data(i).attribute27,fnd_api.g_miss_char,NULL,
556 x_adj_data(i).attribute27),x_adj_rec.attribute27),
557 DECODE(x_adj_rec.attribute28,fnd_api.g_miss_char,
558 DECODE(x_adj_data(i).attribute28,fnd_api.g_miss_char,NULL,
559 x_adj_data(i).attribute28),x_adj_rec.attribute28),
560 DECODE(x_adj_rec.attribute29,fnd_api.g_miss_char,
561 DECODE(x_adj_data(i).attribute29,fnd_api.g_miss_char,NULL,
562 x_adj_data(i).attribute29),x_adj_rec.attribute29),
563 DECODE(x_adj_rec.attribute30,fnd_api.g_miss_char,
564 DECODE(x_adj_data(i).attribute30,fnd_api.g_miss_char,NULL,
565 x_adj_data(i).attribute30),x_adj_rec.attribute30),
566 DECODE(x_adj_rec.attribute31,fnd_api.g_miss_char,
567 DECODE(x_adj_data(i).attribute31,fnd_api.g_miss_char,NULL,
568 x_adj_data(i).attribute31),x_adj_rec.attribute31),
569 DECODE(x_adj_rec.attribute32,fnd_api.g_miss_char,
570 DECODE(x_adj_data(i).attribute32,fnd_api.g_miss_char,NULL,
571 x_adj_data(i).attribute32),x_adj_rec.attribute32),
572 DECODE(x_adj_rec.attribute33,fnd_api.g_miss_char,
573 DECODE(x_adj_data(i).attribute33,fnd_api.g_miss_char,NULL,
574 x_adj_data(i).attribute33),x_adj_rec.attribute33),
575 DECODE(x_adj_rec.attribute34,fnd_api.g_miss_char,
576 DECODE(x_adj_data(i).attribute34,fnd_api.g_miss_char,NULL,
577 x_adj_data(i).attribute34),x_adj_rec.attribute34),
578 DECODE(x_adj_rec.attribute35,fnd_api.g_miss_char,
579 DECODE(x_adj_data(i).attribute35,fnd_api.g_miss_char,NULL,
580 x_adj_data(i).attribute35),x_adj_rec.attribute35),
581 DECODE(x_adj_rec.attribute36,fnd_api.g_miss_char,
582 DECODE(x_adj_data(i).attribute36,fnd_api.g_miss_char,NULL,
583 x_adj_data(i).attribute36),x_adj_rec.attribute36),
584 DECODE(x_adj_rec.attribute37,fnd_api.g_miss_char,
585 DECODE(x_adj_data(i).attribute37,fnd_api.g_miss_char,NULL,
586 x_adj_data(i).attribute37),x_adj_rec.attribute37),
587 DECODE(x_adj_rec.attribute38,fnd_api.g_miss_char,
588 DECODE(x_adj_data(i).attribute38,fnd_api.g_miss_char,NULL,
589 x_adj_data(i).attribute38),x_adj_rec.attribute38),
590 DECODE(x_adj_rec.attribute39,fnd_api.g_miss_char,
591 DECODE(x_adj_data(i).attribute39,fnd_api.g_miss_char,NULL,
592 x_adj_data(i).attribute39),x_adj_rec.attribute39),
593 DECODE(x_adj_rec.attribute40,fnd_api.g_miss_char,
594 DECODE(x_adj_data(i).attribute40,fnd_api.g_miss_char,NULL,
595 x_adj_data(i).attribute40),x_adj_rec.attribute40),
596 DECODE(x_adj_rec.attribute41,fnd_api.g_miss_char,
597 DECODE(x_adj_data(i).attribute41,fnd_api.g_miss_char,NULL,
598 x_adj_data(i).attribute41),x_adj_rec.attribute41),
599 DECODE(x_adj_rec.attribute42,fnd_api.g_miss_char,
600 DECODE(x_adj_data(i).attribute42,fnd_api.g_miss_char,NULL,
601 x_adj_data(i).attribute42),x_adj_rec.attribute42),
602 DECODE(x_adj_rec.attribute43,fnd_api.g_miss_char,
603 DECODE(x_adj_data(i).attribute43,fnd_api.g_miss_char,NULL,
607 x_adj_data(i).attribute44),x_adj_rec.attribute44),
604 x_adj_data(i).attribute43),x_adj_rec.attribute43),
605 DECODE(x_adj_rec.attribute44,fnd_api.g_miss_char,
606 DECODE(x_adj_data(i).attribute44,fnd_api.g_miss_char,NULL,
608 DECODE(x_adj_rec.attribute45,fnd_api.g_miss_char,
609 DECODE(x_adj_data(i).attribute45,fnd_api.g_miss_char,NULL,
610 x_adj_data(i).attribute45),x_adj_rec.attribute45),
611 DECODE(x_adj_rec.attribute46,fnd_api.g_miss_char,
612 DECODE(x_adj_data(i).attribute46,fnd_api.g_miss_char,NULL,
613 x_adj_data(i).attribute46),x_adj_rec.attribute46),
614 DECODE(x_adj_rec.attribute47,fnd_api.g_miss_char,
615 DECODE(x_adj_data(i).attribute47,fnd_api.g_miss_char,NULL,
616 x_adj_data(i).attribute47),x_adj_rec.attribute47),
617 DECODE(x_adj_rec.attribute48,fnd_api.g_miss_char,
618 DECODE(x_adj_data(i).attribute48,fnd_api.g_miss_char,NULL,
619 x_adj_data(i).attribute48),x_adj_rec.attribute48),
620 DECODE(x_adj_rec.attribute49,fnd_api.g_miss_char,
621 DECODE(x_adj_data(i).attribute49,fnd_api.g_miss_char,NULL,
622 x_adj_data(i).attribute49),x_adj_rec.attribute49),
623 DECODE(x_adj_rec.attribute50,fnd_api.g_miss_char,
624 DECODE(x_adj_data(i).attribute50,fnd_api.g_miss_char,NULL,
625 x_adj_data(i).attribute50),x_adj_rec.attribute50),
626 DECODE(x_adj_rec.attribute51,fnd_api.g_miss_char,
627 DECODE(x_adj_data(i).attribute51,fnd_api.g_miss_char,NULL,
628 x_adj_data(i).attribute51),x_adj_rec.attribute51),
629 DECODE(x_adj_rec.attribute52,fnd_api.g_miss_char,
630 DECODE(x_adj_data(i).attribute52,fnd_api.g_miss_char,NULL,
631 x_adj_data(i).attribute52),x_adj_rec.attribute52),
632 DECODE(x_adj_rec.attribute53,fnd_api.g_miss_char,
633 DECODE(x_adj_data(i).attribute53,fnd_api.g_miss_char,NULL,
634 x_adj_data(i).attribute53),x_adj_rec.attribute53),
635 DECODE(x_adj_rec.attribute54,fnd_api.g_miss_char,
636 DECODE(x_adj_data(i).attribute54,fnd_api.g_miss_char,NULL,
637 x_adj_data(i).attribute54),x_adj_rec.attribute54),
638 DECODE(x_adj_rec.attribute55,fnd_api.g_miss_char,
639 DECODE(x_adj_data(i).attribute55,fnd_api.g_miss_char,NULL,
640 x_adj_data(i).attribute55),x_adj_rec.attribute55),
641 DECODE(x_adj_rec.attribute56,fnd_api.g_miss_char,
642 DECODE(x_adj_data(i).attribute56,fnd_api.g_miss_char,NULL,
643 x_adj_data(i).attribute56),x_adj_rec.attribute56),
644 DECODE(x_adj_rec.attribute57,fnd_api.g_miss_char,
645 DECODE(x_adj_data(i).attribute57,fnd_api.g_miss_char,NULL,
646 x_adj_data(i).attribute57),x_adj_rec.attribute57),
647 DECODE(x_adj_rec.attribute58,fnd_api.g_miss_char,
648 DECODE(x_adj_data(i).attribute58,fnd_api.g_miss_char,NULL,
649 x_adj_data(i).attribute58),x_adj_rec.attribute58),
650 DECODE(x_adj_rec.attribute59,fnd_api.g_miss_char,
651 DECODE(x_adj_data(i).attribute59,fnd_api.g_miss_char,NULL,
652 x_adj_data(i).attribute59),x_adj_rec.attribute59),
653 DECODE(x_adj_rec.attribute60,fnd_api.g_miss_char,
654 DECODE(x_adj_data(i).attribute60,fnd_api.g_miss_char,NULL,
655 x_adj_data(i).attribute60),x_adj_rec.attribute60),
656 DECODE(x_adj_rec.attribute61,fnd_api.g_miss_char,
657 DECODE(x_adj_data(i).attribute61,fnd_api.g_miss_char,NULL,
658 x_adj_data(i).attribute61),x_adj_rec.attribute61),
659 DECODE(x_adj_rec.attribute62,fnd_api.g_miss_char,
660 DECODE(x_adj_data(i).attribute62,fnd_api.g_miss_char,NULL,
661 x_adj_data(i).attribute62),x_adj_rec.attribute62),
662 DECODE(x_adj_rec.attribute63,fnd_api.g_miss_char,
663 DECODE(x_adj_data(i).attribute63,fnd_api.g_miss_char,NULL,
664 x_adj_data(i).attribute63),x_adj_rec.attribute63),
665 DECODE(x_adj_rec.attribute64,fnd_api.g_miss_char,
666 DECODE(x_adj_data(i).attribute64,fnd_api.g_miss_char,NULL,
667 x_adj_data(i).attribute64),x_adj_rec.attribute64),
668 DECODE(x_adj_rec.attribute65,fnd_api.g_miss_char,
669 DECODE(x_adj_data(i).attribute65,fnd_api.g_miss_char,NULL,
670 x_adj_data(i).attribute65),x_adj_rec.attribute65),
671 DECODE(x_adj_rec.attribute66,fnd_api.g_miss_char,
672 DECODE(x_adj_data(i).attribute66,fnd_api.g_miss_char,NULL,
673 x_adj_data(i).attribute66),x_adj_rec.attribute66),
674 DECODE(x_adj_rec.attribute67,fnd_api.g_miss_char,
675 DECODE(x_adj_data(i).attribute67,fnd_api.g_miss_char,NULL,
676 x_adj_data(i).attribute67),x_adj_rec.attribute67),
677 DECODE(x_adj_rec.attribute68,fnd_api.g_miss_char,
678 DECODE(x_adj_data(i).attribute68,fnd_api.g_miss_char,NULL,
679 x_adj_data(i).attribute68),x_adj_rec.attribute68),
680 DECODE(x_adj_rec.attribute69,fnd_api.g_miss_char,
681 DECODE(x_adj_data(i).attribute69,fnd_api.g_miss_char,NULL,
682 x_adj_data(i).attribute69),x_adj_rec.attribute69),
683 DECODE(x_adj_rec.attribute70,fnd_api.g_miss_char,
684 DECODE(x_adj_data(i).attribute70,fnd_api.g_miss_char,NULL,
685 x_adj_data(i).attribute70),x_adj_rec.attribute70),
686 DECODE(x_adj_rec.attribute71,fnd_api.g_miss_char,
687 DECODE(x_adj_data(i).attribute71,fnd_api.g_miss_char,NULL,
691 x_adj_data(i).attribute72),x_adj_rec.attribute72),
688 x_adj_data(i).attribute71),x_adj_rec.attribute71),
689 DECODE(x_adj_rec.attribute72,fnd_api.g_miss_char,
690 DECODE(x_adj_data(i).attribute72,fnd_api.g_miss_char,NULL,
692 DECODE(x_adj_rec.attribute73,fnd_api.g_miss_char,
693 DECODE(x_adj_data(i).attribute73,fnd_api.g_miss_char,NULL,
694 x_adj_data(i).attribute73),x_adj_rec.attribute73),
695 DECODE(x_adj_rec.attribute74,fnd_api.g_miss_char,
696 DECODE(x_adj_data(i).attribute74,fnd_api.g_miss_char,NULL,
697 x_adj_data(i).attribute74),x_adj_rec.attribute74),
698 DECODE(x_adj_rec.attribute75,fnd_api.g_miss_char,
699 DECODE(x_adj_data(i).attribute75,fnd_api.g_miss_char,NULL,
700 x_adj_data(i).attribute75),x_adj_rec.attribute75),
701 DECODE(x_adj_rec.attribute76,fnd_api.g_miss_char,
702 DECODE(x_adj_data(i).attribute76,fnd_api.g_miss_char,NULL,
703 x_adj_data(i).attribute76),x_adj_rec.attribute76),
704 DECODE(x_adj_rec.attribute77,fnd_api.g_miss_char,
705 DECODE(x_adj_data(i).attribute77,fnd_api.g_miss_char,NULL,
706 x_adj_data(i).attribute77),x_adj_rec.attribute77),
707 DECODE(x_adj_rec.attribute78,fnd_api.g_miss_char,
708 DECODE(x_adj_data(i).attribute78,fnd_api.g_miss_char,NULL,
709 x_adj_data(i).attribute78),x_adj_rec.attribute78),
710 DECODE(x_adj_rec.attribute79,fnd_api.g_miss_char,
711 DECODE(x_adj_data(i).attribute79,fnd_api.g_miss_char,NULL,
712 x_adj_data(i).attribute79),x_adj_rec.attribute79),
713 DECODE(x_adj_rec.attribute80,fnd_api.g_miss_char,
714 DECODE(x_adj_data(i).attribute80,fnd_api.g_miss_char,NULL,
715 x_adj_data(i).attribute80),x_adj_rec.attribute80),
716 DECODE(x_adj_rec.attribute81,fnd_api.g_miss_char,
717 DECODE(x_adj_data(i).attribute81,fnd_api.g_miss_char,NULL,
718 x_adj_data(i).attribute81),x_adj_rec.attribute81),
719 DECODE(x_adj_rec.attribute82,fnd_api.g_miss_char,
720 DECODE(x_adj_data(i).attribute82,fnd_api.g_miss_char,NULL,
721 x_adj_data(i).attribute82),x_adj_rec.attribute82),
722 DECODE(x_adj_rec.attribute83,fnd_api.g_miss_char,
723 DECODE(x_adj_data(i).attribute83,fnd_api.g_miss_char,NULL,
724 x_adj_data(i).attribute83),x_adj_rec.attribute83),
725 DECODE(x_adj_rec.attribute84,fnd_api.g_miss_char,
726 DECODE(x_adj_data(i).attribute84,fnd_api.g_miss_char,NULL,
727 x_adj_data(i).attribute84),x_adj_rec.attribute84),
728 DECODE(x_adj_rec.attribute85,fnd_api.g_miss_char,
729 DECODE(x_adj_data(i).attribute85,fnd_api.g_miss_char,NULL,
730 x_adj_data(i).attribute85),x_adj_rec.attribute85),
731 DECODE(x_adj_rec.attribute86,fnd_api.g_miss_char,
732 DECODE(x_adj_data(i).attribute86,fnd_api.g_miss_char,NULL,
733 x_adj_data(i).attribute86),x_adj_rec.attribute86),
734 DECODE(x_adj_rec.attribute87,fnd_api.g_miss_char,
735 DECODE(x_adj_data(i).attribute87,fnd_api.g_miss_char,NULL,
736 x_adj_data(i).attribute87),x_adj_rec.attribute87),
737 DECODE(x_adj_rec.attribute88,fnd_api.g_miss_char,
738 DECODE(x_adj_data(i).attribute88,fnd_api.g_miss_char,NULL,
739 x_adj_data(i).attribute88),x_adj_rec.attribute88),
740 DECODE(x_adj_rec.attribute89,fnd_api.g_miss_char,
741 DECODE(x_adj_data(i).attribute89,fnd_api.g_miss_char,NULL,
742 x_adj_data(i).attribute89),x_adj_rec.attribute89),
743 DECODE(x_adj_rec.attribute90,fnd_api.g_miss_char,
744 DECODE(x_adj_data(i).attribute90,fnd_api.g_miss_char,NULL,
745 x_adj_data(i).attribute90),x_adj_rec.attribute90),
746 DECODE(x_adj_rec.attribute91,fnd_api.g_miss_char,
747 DECODE(x_adj_data(i).attribute91,fnd_api.g_miss_char,NULL,
748 x_adj_data(i).attribute91),x_adj_rec.attribute91),
749 DECODE(x_adj_rec.attribute92,fnd_api.g_miss_char,
750 DECODE(x_adj_data(i).attribute92,fnd_api.g_miss_char,NULL,
751 x_adj_data(i).attribute92),x_adj_rec.attribute92),
752 DECODE(x_adj_rec.attribute93,fnd_api.g_miss_char,
753 DECODE(x_adj_data(i).attribute93,fnd_api.g_miss_char,NULL,
754 x_adj_data(i).attribute93),x_adj_rec.attribute93),
755 DECODE(x_adj_rec.attribute94,fnd_api.g_miss_char,
756 DECODE(x_adj_data(i).attribute94,fnd_api.g_miss_char,NULL,
757 x_adj_data(i).attribute94),x_adj_rec.attribute94),
758 DECODE(x_adj_rec.attribute95,fnd_api.g_miss_char,
759 DECODE(x_adj_data(i).attribute95,fnd_api.g_miss_char,NULL,
760 x_adj_data(i).attribute95),x_adj_rec.attribute95),
761 DECODE(x_adj_rec.attribute96,fnd_api.g_miss_char,
762 DECODE(x_adj_data(i).attribute96,fnd_api.g_miss_char,NULL,
763 x_adj_data(i).attribute96),x_adj_rec.attribute96),
764 DECODE(x_adj_rec.attribute97,fnd_api.g_miss_char,
765 DECODE(x_adj_data(i).attribute97,fnd_api.g_miss_char,NULL,
766 x_adj_data(i).attribute97),x_adj_rec.attribute97),
767 DECODE(x_adj_rec.attribute98,fnd_api.g_miss_char,
768 DECODE(x_adj_data(i).attribute98,fnd_api.g_miss_char,NULL,
769 x_adj_data(i).attribute98),x_adj_rec.attribute98),
770 DECODE(x_adj_rec.attribute99,fnd_api.g_miss_char,
771 DECODE(x_adj_data(i).attribute99,fnd_api.g_miss_char,NULL,
775 x_adj_data(i).attribute100),x_adj_rec.attribute100),
772 x_adj_data(i).attribute99),x_adj_rec.attribute99),
773 DECODE(x_adj_rec.attribute100,fnd_api.g_miss_char,
774 DECODE(x_adj_data(i).attribute100,fnd_api.g_miss_char,NULL,
776 DECODE(x_adj_rec.direct_salesrep_number,fnd_api.g_miss_char,
777 DECODE(x_adj_data(i).direct_salesrep_number,fnd_api.g_miss_char,NULL,
778 x_adj_data(i).direct_salesrep_number),
779 x_adj_rec.direct_salesrep_number),
780 l_comm_lines_api_id,
781 NULL,NULL,NULL,
782 DECODE(x_adj_data(i).rollup_date,fnd_api.g_miss_date,NULL,
783 x_adj_data(i).rollup_date),
784 NULL,
785 DECODE(x_adj_data(i).source_doc_type,fnd_api.g_miss_char,NULL,
786 x_adj_data(i).source_doc_type),
787 DECODE(x_adj_data(i).orig_currency_code,fnd_api.g_miss_char,NULL,
788 x_adj_data(i).orig_currency_code),
789 DECODE(x_adj_data(i).exchange_rate,fnd_api.g_miss_num,NULL,
790 x_adj_data(i).exchange_rate),
791 DECODE(x_adj_data(i).transaction_amount_orig,fnd_api.g_miss_num,NULL,
792 x_adj_data(i).transaction_amount_orig),
793 DECODE(x_adj_data(i).trx_id,fnd_api.g_miss_num,NULL,
794 x_adj_data(i).trx_id),
795 DECODE(x_adj_data(i).trx_line_id,fnd_api.g_miss_num,NULL,
796 x_adj_data(i).trx_line_id),
797 DECODE(x_adj_data(i).trx_sales_line_id,fnd_api.g_miss_num,NULL,
798 x_adj_data(i).trx_sales_line_id),
799 DECODE(x_adj_data(i).quantity,fnd_api.g_miss_num,NULL,
800 x_adj_data(i).quantity),
801 DECODE(x_adj_data(i).source_trx_number,fnd_api.g_miss_char,NULL,
802 x_adj_data(i).source_trx_number),
803 DECODE(x_adj_data(i).discount_percentage,fnd_api.g_miss_num,NULL,
804 x_adj_data(i).discount_percentage),
805 DECODE(x_adj_data(i).margin_percentage,fnd_api.g_miss_num,NULL,
806 x_adj_data(i).margin_percentage),
807 NULL,NULL,
808 DECODE(x_adj_data(i).forecast_id,fnd_api.g_miss_num,NULL,
809 x_adj_data(i).forecast_id),
810 DECODE(x_adj_data(i).upside_quantity,fnd_api.g_miss_num,NULL,
811 x_adj_data(i).upside_quantity),
812 DECODE(x_adj_data(i).upside_amount,fnd_api.g_miss_num,NULL,
813 x_adj_data(i).upside_amount),
814 DECODE(x_adj_data(i).uom_code,fnd_api.g_miss_char,NULL,
815 x_adj_data(i).uom_code),
816 -- Bug fix 5349170
817 DECODE(x_adj_data(i).source_trx_id,fnd_api.g_miss_num,NULL,
818 x_adj_data(i).source_trx_id),
819 DECODE(x_adj_data(i).source_trx_line_id,fnd_api.g_miss_num,NULL,
820 x_adj_data(i).source_trx_line_id),
821 DECODE(x_adj_data(i).source_trx_sales_line_id,fnd_api.g_miss_num,NULL,
822 x_adj_data(i).source_trx_sales_line_id),
823 -- Bug fix 5349170
824 NULL,
825 DECODE(x_adj_rec.customer_id,fnd_api.g_miss_num,
826 DECODE(x_adj_data(i).customer_id,fnd_api.g_miss_num,NULL,
827 x_adj_data(i).customer_id),x_adj_rec.customer_id),
828 DECODE(x_adj_rec.inventory_item_id,fnd_api.g_miss_num,
829 DECODE(x_adj_data(i).inventory_item_id,fnd_api.g_miss_num,NULL,
830 x_adj_data(i).inventory_item_id),x_adj_rec.inventory_item_id),
831 DECODE(x_adj_data(i).order_number,fnd_api.g_miss_num,NULL,
832 x_adj_data(i).order_number),
833 DECODE(x_adj_data(i).order_date,fnd_api.g_miss_date,NULL,
834 x_adj_data(i).order_date),
835 DECODE(x_adj_data(i).invoice_number,fnd_api.g_miss_char,NULL,
836 x_adj_data(i).invoice_number),
837 DECODE(x_adj_data(i).invoice_date,fnd_api.g_miss_date,NULL,
838 x_adj_data(i).invoice_date),
839 DECODE(x_adj_data(i).bill_to_address_id,fnd_api.g_miss_num,NULL,
840 x_adj_data(i).bill_to_address_id),
841 DECODE(x_adj_data(i).ship_to_address_id,fnd_api.g_miss_num,NULL,
842 x_adj_data(i).ship_to_address_id),
843 DECODE(x_adj_data(i).bill_to_contact_id,fnd_api.g_miss_num,NULL,
844 x_adj_data(i).bill_to_contact_id),
845 DECODE(x_adj_data(i).ship_to_contact_id,fnd_api.g_miss_num,NULL,
846 x_adj_data(i).ship_to_contact_id),
847 DECODE(x_adj_data(i).comm_lines_api_id,fnd_api.g_miss_num,NULL,
848 x_adj_data(i).comm_lines_api_id),
849 l_adjust_date,x_adj_rec.adjusted_by,
850 DECODE(x_adj_data(i).revenue_type,fnd_api.g_miss_char,NULL,
851 x_adj_data(i).revenue_type),
852 NULL,
853 x_adj_rec.adjust_comments,l_adjust_status,
854 DECODE(x_adj_data(i).line_number,fnd_api.g_miss_num,NULL,
855 x_adj_data(i).line_number),
856 DECODE(x_adj_data(i).reason_code,fnd_api.g_miss_char,NULL,
857 x_adj_data(i).reason_code),
858 DECODE(x_adj_data(i).attribute_category,fnd_api.g_miss_char,NULL,
859 x_adj_data(i).attribute_category),
860 DECODE(x_adj_data(i).type,fnd_api.g_miss_char,NULL,
861 x_adj_data(i).type),
862 DECODE(x_adj_data(i).pre_processed_code,fnd_api.g_miss_char,NULL,
863 x_adj_data(i).pre_processed_code),
864 DECODE(x_adj_data(i).quota_id,fnd_api.g_miss_num,NULL,
865 x_adj_data(i).quota_id),
866 DECODE(x_adj_data(i).srp_plan_assign_id,fnd_api.g_miss_num,NULL,
867 x_adj_data(i).srp_plan_assign_id),
868 DECODE(x_adj_data(i).role_id,fnd_api.g_miss_num,NULL,
869 x_adj_data(i).role_id),
870 DECODE(x_adj_data(i).comp_group_id,fnd_api.g_miss_num,NULL,
871 x_adj_data(i).comp_group_id),
872 NULL,NULL,NULL,
876 x_adj_data(i).split_pct),
873 DECODE(x_adj_data(i).sales_channel,fnd_api.g_miss_char,NULL,
874 x_adj_data(i).sales_channel),
875 DECODE(x_adj_data(i).split_pct,fnd_api.g_miss_char,NULL,
877 DECODE(x_adj_data(i).split_status,fnd_api.g_miss_char,NULL,
878 x_adj_data(i).split_status),
879 DECODE(x_adj_rec.org_id,fnd_api.g_miss_num,
880 DECODE(x_adj_data(i).org_id,fnd_api.g_miss_num,NULL,
881 x_adj_data(i).org_id),
882 x_adj_rec.org_id),
883 DECODE(x_adj_rec.terr_id,fnd_api.g_miss_num,
884 DECODE(x_adj_data(i).terr_id,fnd_api.g_miss_num,NULL,
885 x_adj_data(i).terr_id),
886 x_adj_rec.terr_id),
887 DECODE(x_adj_rec.preserve_credit_override_flag,fnd_api.g_miss_char,
888 DECODE(x_adj_data(i).preserve_credit_override_flag,fnd_api.g_miss_char,NULL,
889 x_adj_data(i).preserve_credit_override_flag),
890 x_adj_rec.preserve_credit_override_flag)
891 INTO l_api_rec.salesrep_id,l_api_rec.processed_date,
892 l_api_rec.processed_period_id,l_api_rec.acctd_transaction_amount,
893 l_api_rec.trx_type,l_api_rec.revenue_class_id,
894 l_api_rec.load_status,
895 l_api_rec.attribute1,l_api_rec.attribute2,
896 l_api_rec.attribute3,l_api_rec.attribute4,
897 l_api_rec.attribute5,l_api_rec.attribute6,
898 l_api_rec.attribute7,l_api_rec.attribute8,
899 l_api_rec.attribute9,l_api_rec.attribute10,
900 l_api_rec.attribute11,l_api_rec.attribute12,
901 l_api_rec.attribute13,l_api_rec.attribute14,
902 l_api_rec.attribute15,l_api_rec.attribute16,
903 l_api_rec.attribute17,l_api_rec.attribute18,
904 l_api_rec.attribute19,l_api_rec.attribute20,
905 l_api_rec.attribute21,l_api_rec.attribute22,
906 l_api_rec.attribute23,l_api_rec.attribute24,
907 l_api_rec.attribute25,l_api_rec.attribute26,
908 l_api_rec.attribute27,l_api_rec.attribute28,
909 l_api_rec.attribute29,l_api_rec.attribute30,
910 l_api_rec.attribute31,l_api_rec.attribute32,
911 l_api_rec.attribute33,l_api_rec.attribute34,
912 l_api_rec.attribute35,l_api_rec.attribute36,
913 l_api_rec.attribute37,l_api_rec.attribute38,
914 l_api_rec.attribute39,l_api_rec.attribute40,
915 l_api_rec.attribute41,l_api_rec.attribute42,
916 l_api_rec.attribute43,l_api_rec.attribute44,
917 l_api_rec.attribute45,l_api_rec.attribute46,
918 l_api_rec.attribute47,l_api_rec.attribute48,
919 l_api_rec.attribute49,l_api_rec.attribute50,
920 l_api_rec.attribute51,l_api_rec.attribute52,
921 l_api_rec.attribute53,l_api_rec.attribute54,
922 l_api_rec.attribute55,l_api_rec.attribute56,
923 l_api_rec.attribute57,l_api_rec.attribute58,
924 l_api_rec.attribute59,l_api_rec.attribute60,
925 l_api_rec.attribute61,l_api_rec.attribute62,
926 l_api_rec.attribute63,l_api_rec.attribute64,
927 l_api_rec.attribute65,l_api_rec.attribute66,
928 l_api_rec.attribute67,l_api_rec.attribute68,
929 l_api_rec.attribute69,l_api_rec.attribute70,
930 l_api_rec.attribute71,l_api_rec.attribute72,
931 l_api_rec.attribute73,l_api_rec.attribute74,
932 l_api_rec.attribute75,l_api_rec.attribute76,
933 l_api_rec.attribute77,l_api_rec.attribute78,
934 l_api_rec.attribute79,l_api_rec.attribute80,
935 l_api_rec.attribute81,l_api_rec.attribute82,
936 l_api_rec.attribute83,l_api_rec.attribute84,
937 l_api_rec.attribute85,l_api_rec.attribute86,
938 l_api_rec.attribute87,l_api_rec.attribute88,
939 l_api_rec.attribute89,l_api_rec.attribute90,
940 l_api_rec.attribute91,l_api_rec.attribute92,
941 l_api_rec.attribute93,l_api_rec.attribute94,
942 l_api_rec.attribute95,l_api_rec.attribute96,
943 l_api_rec.attribute97,l_api_rec.attribute98,
944 l_api_rec.attribute99,l_api_rec.attribute100,
945 l_api_rec.employee_number,l_api_rec.comm_lines_api_id,
946 l_api_rec.conc_batch_id,l_api_rec.process_batch_id,
947 l_api_rec.salesrep_number,l_api_rec.rollup_date,
948 l_api_rec.source_doc_id,l_api_rec.source_doc_type,
949 l_api_rec.transaction_currency_code,
950 l_api_rec.exchange_rate,
951 l_api_rec.transaction_amount,
952 l_api_rec.trx_id,l_api_rec.trx_line_id,
953 l_api_rec.trx_sales_line_id,l_api_rec.quantity,
954 l_api_rec.source_trx_number,
955 l_api_rec.discount_percentage,
956 l_api_rec.margin_percentage,
957 l_api_rec.pre_defined_rc_flag,l_api_rec.rollup_flag,
958 l_api_rec.forecast_id,
959 l_api_rec.upside_quantity,l_api_rec.upside_amount,
960 l_api_rec.uom_code,l_api_rec.source_trx_id,
961 l_api_rec.source_trx_line_id,
962 l_api_rec.source_trx_sales_line_id,
963 l_api_rec.negated_flag,l_api_rec.customer_id,
964 l_api_rec.inventory_item_id,l_api_rec.order_number,
965 l_api_rec.booked_date,l_api_rec.invoice_number,
966 l_api_rec.invoice_date,l_api_rec.bill_to_address_id,
967 l_api_rec.ship_to_address_id,l_api_rec.bill_to_contact_id,
971 l_api_rec.adjust_comments,l_api_rec.adjust_status,
968 l_api_rec.ship_to_contact_id,l_api_rec.adj_comm_lines_api_id,
969 l_api_rec.adjust_date,l_api_rec.adjusted_by,
970 l_api_rec.revenue_type,l_api_rec.adjust_rollup_flag,
972 l_api_rec.line_number,l_api_rec.reason_code,
973 l_api_rec.attribute_category,l_api_rec.type,
974 l_api_rec.pre_processed_code,l_api_rec.quota_id,
975 l_api_rec.srp_plan_assign_id,l_api_rec.role_id,
976 l_api_rec.comp_group_id,l_api_rec.commission_amount,
977 l_api_rec.reversal_flag,l_api_rec.reversal_header_id,
978 l_api_rec.sales_channel,l_api_rec.split_pct,
979 l_api_rec.split_status,
980 l_api_rec.org_id,
981 l_api_rec.terr_id,
982 l_api_rec.preserve_credit_override_flag
983 FROM DUAL;
984
985 --Added for Crediting
986 IF(x_adj_data(i).terr_id IS NOT NULL)
987 THEN
988 l_api_rec.terr_id := -999;
989 l_api_rec.preserve_credit_override_flag := 'Y';
990 END IF;
991
992 l_api_rec.adj_comm_lines_api_id := x_adj_data(i).comm_lines_api_id;
993 cn_comm_lines_api_pkg.insert_row(l_api_rec);
994
995 --
996 IF ((g_track_invoice = 'Y') AND (l_api_rec.trx_type = 'INV')) THEN
997 l_counter := l_counter + 1;
998 --
999 l_new_data(l_counter).salesrep_id := l_api_rec.salesrep_id;
1000 l_new_data(l_counter).direct_salesrep_number
1001 := l_api_rec.employee_number;
1002 l_new_data(l_counter).invoice_number := l_api_rec.invoice_number;
1003 l_new_data(l_counter).line_number := l_api_rec.line_number;
1004 l_new_data(l_counter).revenue_type := l_api_rec.revenue_type;
1005 l_new_data(l_counter).split_pct := l_api_rec.split_pct;
1006 l_new_data(l_counter).comm_lines_api_id := l_comm_lines_api_id;
1007 --
1008 END IF;
1009 X_proc_comp := 'Y';
1010 END IF;
1011 IF (x_mass_adj_type = 'M') AND
1012 (x_adj_data(i).adjust_status NOT IN('FROZEN','REVERSAL','SCA_PENDING') OR
1013 x_adj_data(i).adjust_status IS null) AND
1014 x_adj_data(i).load_status NOT IN ('FILTERED') THEN
1015
1016 cn_adjustments_pkg.api_negate_record(
1017 x_adj_data(i).comm_lines_api_id,
1018 x_adj_rec.adjusted_by,
1019 x_adj_rec.adjust_comments,
1020 x_adj_data(i).direct_salesrep_number);
1021
1022 --Added for Crediting
1023 /*CN_GET_TX_DATA_PUB.update_credit_credentials(
1024 x_adj_data(i).comm_lines_api_id,
1025 x_adj_data(i).terr_id,
1026 x_adj_data(i).org_id,
1027 x_adj_rec.adjusted_by
1028 );*/
1029
1030 END IF;
1031 END LOOP;
1032 END IF; -- IF (x_adj_data.COUNT>0)
1033 IF ((g_track_invoice = 'Y') AND (l_new_data.COUNT > 0)) THEN
1034 --
1035 l_existing_data(1).salesrep_id := NULL;
1036 l_existing_data(1).direct_salesrep_number := NULL;
1037 l_existing_data(1).invoice_number := NULL;
1038 l_existing_data(1).line_number := NULL;
1039 l_existing_data(1).revenue_type := NULL;
1040 l_existing_data(1).split_pct := NULL;
1041 l_existing_data(1).comm_lines_api_id := NULL;
1042 --
1043 cn_invoice_changes_pvt.update_invoice_changes(
1044 p_api_version => l_api_version,
1045 p_validation_level => l_validation_level,
1046 p_existing_data => l_existing_data,
1047 p_new_data => l_new_data,
1048 p_exist_data_check => 'N',
1049 p_new_data_check => 'Y',
1050 x_return_status => l_return_status,
1051 x_msg_count => l_msg_count,
1052 x_msg_data => l_msg_data,
1053 x_loading_status => l_loading_status);
1054 --
1055 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1056 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
1057 FND_MESSAGE.Set_Name('CN', 'CN_UPDATE_INV_ERROR');
1058 FND_MSG_PUB.Add;
1059 END IF;
1060 l_loading_status := 'CN_UPDATE_INV_ERROR';
1061 x_proc_comp := 'E';
1062 END IF;
1063 --
1064 END IF;
1065 EXCEPTION
1066 WHEN OTHERS THEN
1067 x_proc_comp := 'E';
1068 END mass_update_values;
1069 --
1070 PROCEDURE deal_split(
1071 x_source_salesrep_id NUMBER,
1072 x_salesrep_id NUMBER,
1073 x_split_percent NUMBER,
1074 x_revenue_type VARCHAR2,
1075 x_invoice_number VARCHAR2,
1076 x_order_number NUMBER,
1077 x_adjusted_by VARCHAR2,
1078 x_adjust_comments VARCHAR2) IS
1079 --
1080 deal_select_cursor NUMBER(15);
1081 sql_stmt VARCHAR2(5000);
1082 count_rows NUMBER;
1083 l_comm_lines_api_id NUMBER;
1084 l_ins_comm_lines_api_id NUMBER;
1085 l_transaction_amount NUMBER;
1086 l_acctd_transaction_amount NUMBER;
1087 l_salesrep_number VARCHAR2(30);
1088 l_revenue_type VARCHAR2(15);
1089 l_revenue VARCHAR2(12) := 'REVENUE';
1090 l_adj_tbl cn_get_tx_data_pub.adj_tbl_type;
1091 -- PL/SQL tables/records
1092 l_newtx_rec cn_get_tx_data_pub.adj_rec_type;
1093 o_newtx_rec cn_get_tx_data_pub.adj_rec_type;
1094 l_api_rec cn_comm_lines_api_pkg.comm_lines_api_rec_type;
1095 -- To mask this non-standard API
1096 x_return_status VARCHAR2(1000);
1100 direct_salesrep_id = :X_source_salesrep_id AND revenue_type = :l_revenue';
1097 BEGIN
1098 deal_select_cursor := DBMS_SQL.open_cursor;
1099 sql_stmt := 'SELECT COMM_LINES_API_ID FROM CN_ADJUSTMENTS_V WHERE
1101 --
1102 IF (x_invoice_number IS NOT NULL) THEN
1103 sql_stmt := sql_stmt || ' and invoice_number = :X_invoice_number';
1104 ELSIF(x_order_number IS NOT NULL) THEN
1105 sql_stmt := sql_stmt || ' and order_number = :X_order_number';
1106 END IF;
1107 --
1108 dbms_sql.parse(deal_select_cursor,sql_stmt,DBMS_SQL.NATIVE);
1109 DBMS_SQL.bind_variable(
1110 deal_select_cursor,'x_source_salesrep_id',x_source_salesrep_id);
1111 DBMS_SQL.bind_variable(
1112 deal_select_cursor,'l_revenue',l_revenue);
1113 --
1114 IF (x_invoice_number IS NOT NULL) THEN
1115 DBMS_SQL.bind_variable(
1116 deal_select_cursor,'x_invoice_number',x_invoice_number);
1117 END IF;
1118 --
1119 IF (x_order_number IS NOT NULL) THEN
1120 DBMS_SQL.bind_variable(
1121 deal_select_cursor,'x_order_number',x_order_number);
1122 END IF;
1123 --
1124 DBMS_SQL.define_column (deal_select_cursor,1,l_comm_lines_api_id);
1125 count_rows := DBMS_SQL.execute (deal_select_cursor);
1126 LOOP
1127 IF (dbms_sql.fetch_rows(deal_select_cursor) > 0) THEN
1128 DBMS_SQL.column_value (deal_select_cursor,1,l_comm_lines_api_id);
1129 --
1130 --cn_adjustments_pkg.get_api_data(l_comm_lines_api_id,l_adj_tbl);
1131 cn_get_tx_data_pub.get_api_data(l_comm_lines_api_id,l_adj_tbl);
1132 --
1133 IF (l_adj_tbl.COUNT > 0) THEN
1134 l_transaction_amount := ((NVL(l_adj_tbl(1).transaction_amount,0) *
1135 x_split_percent)/100);
1136 l_acctd_transaction_amount := ((NVL(l_adj_tbl(1).transaction_amount,0) *
1137 x_split_percent)/100);
1138 --
1139 SELECT employee_number
1140 INTO l_salesrep_number
1141 FROM cn_salesreps
1142 WHERE salesrep_id = x_salesrep_id;
1143 --
1144 SELECT cn_comm_lines_api_s.NEXTVAL
1145 INTO l_ins_comm_lines_api_id
1146 FROM dual;
1147 --
1148 l_newtx_rec.direct_salesrep_id := x_salesrep_id;
1149 l_newtx_rec.transaction_amount := l_transaction_amount;
1150 l_newtx_rec.load_status := 'UNLOADED';
1151 l_newtx_rec.comm_lines_api_id := l_ins_comm_lines_api_id;
1152 l_newtx_rec.transaction_amount_orig := l_acctd_transaction_amount;
1153 l_newtx_rec.adjust_date := SYSDATE;
1154 l_newtx_rec.adjusted_by := x_adjusted_by;
1155 l_newtx_rec.revenue_type := x_revenue_type;
1156 l_newtx_rec.adjust_comments := x_adjust_comments;
1157 l_newtx_rec.adjust_status := 'DEALSPLIT';
1158 l_newtx_rec.adj_comm_lines_api_id := l_adj_tbl(1).comm_lines_api_id;
1159 l_newtx_rec.direct_salesrep_number := l_salesrep_number;
1160 --
1161 cn_invoice_changes_pvt.prepare_api_record(
1162 p_newtx_rec => l_newtx_rec,
1163 p_old_adj_tbl => l_adj_tbl,
1164 x_final_trx_rec => o_newtx_rec,
1165 x_return_status => x_return_status);
1166 -- codeCheck: I will explain later about this conversion.
1167 cn_invoice_changes_pvt.convert_adj_to_api(
1168 p_adj_rec => o_newtx_rec,
1169 x_api_rec => l_api_rec);
1170 --
1171 cn_comm_lines_api_pkg.insert_row(l_api_rec);
1172 --
1173 END IF;
1174 ELSE
1175 EXIT;
1176 END IF;
1177 END LOOP;
1178 DBMS_SQL.close_cursor(deal_select_cursor);
1179 END deal_split;
1180 --
1181 PROCEDURE deal_assign(
1182 x_from_salesrep_id NUMBER,
1183 x_to_salesrep_id NUMBER,
1184 x_invoice_number VARCHAR2,
1185 x_order_number NUMBER,
1186 x_adjusted_by VARCHAR2,
1187 x_adjust_comments VARCHAR2) IS
1188 --
1189 CURSOR api_cur(l_comm_lines_api_id NUMBER) IS
1190 SELECT l.comm_lines_api_id,l.adjust_status
1191 FROM cn_comm_lines_api l
1192 WHERE adj_comm_lines_api_id = l_comm_lines_api_id;
1193 --
1194 CURSOR header_cur(l_comm_lines_api_id NUMBER) IS
1195 SELECT h.comm_lines_api_id,h.adjust_status
1196 FROM cn_commission_headers h
1197 WHERE adj_comm_lines_api_id = l_comm_lines_api_id;
1198 --
1199 assign_select_cursor NUMBER(15);
1200 sql_stmt VARCHAR2(5000);
1201 count_rows NUMBER;
1202 l_comm_lines_api_id NUMBER;
1203 l_ins_comm_lines_api_id NUMBER;
1204 l_transaction_amount NUMBER;
1205 l_acctd_transaction_amount NUMBER;
1206 l_salesrep_number VARCHAR2(30);
1207 l_revenue VARCHAR2(12) := 'REVENUE';
1208 l_load_status VARCHAR2(30);
1209 -- PL/SQL tables/records
1210 l_adj_tbl cn_get_tx_data_pub.adj_tbl_type;
1211 l_newtx_rec cn_get_tx_data_pub.adj_rec_type;
1212 o_newtx_rec cn_get_tx_data_pub.adj_rec_type;
1213 l_api_rec cn_comm_lines_api_pkg.comm_lines_api_rec_type;
1214 -- To mask this non-standard API
1215 x_return_status VARCHAR2(1000);
1216 --
1217 BEGIN
1218 assign_select_cursor := DBMS_SQL.open_cursor;
1219 sql_stmt := 'SELECT comm_lines_api_id,load_status FROM cn_adjustments_v WHERE';
1220 sql_stmt := sql_stmt || ' direct_salesrep_id = :x_from_salesrep_id AND revenue_type = :l_revenue';
1221 --
1222 IF (x_invoice_number IS NOT NULL) THEN
1226 END IF;
1223 sql_stmt := sql_stmt || ' AND invoice_number = :x_invoice_number';
1224 ELSIF(x_order_number IS NOT NULL) THEN
1225 sql_stmt := sql_stmt || ' AND order_number = :x_order_number';
1227 --
1228 dbms_sql.parse(assign_select_cursor,sql_stmt,DBMS_SQL.NATIVE);
1229 DBMS_SQL.bind_variable(
1230 assign_select_cursor,'x_from_salesrep_id',x_from_salesrep_id);
1231 DBMS_SQL.bind_variable(
1232 assign_select_cursor,'l_revenue',l_revenue);
1233 --
1234 IF (x_invoice_number IS NOT NULL) THEN
1235 DBMS_SQL.bind_variable(
1236 assign_select_cursor,'x_invoice_number',x_invoice_number);
1237 END IF;
1238 --
1239 IF (x_order_number IS NOT NULL) THEN
1240 DBMS_SQL.bind_variable(
1241 assign_select_cursor,'x_order_number',x_order_number);
1242 END IF;
1243 --
1244 DBMS_SQL.define_column (assign_select_cursor,1,l_comm_lines_api_id);
1245 DBMS_SQL.define_column (assign_select_cursor,2,l_load_status,30);
1246 count_rows := DBMS_SQL.execute (assign_select_cursor);
1247 LOOP
1248 IF (dbms_sql.fetch_rows(assign_select_cursor) > 0) THEN
1249 DBMS_SQL.column_value (assign_select_cursor,1,l_comm_lines_api_id);
1250 DBMS_SQL.column_value (assign_select_cursor,2,l_load_status);
1251 IF (l_load_status = 'LOADED') THEN
1252 FOR rec IN header_cur(l_comm_lines_api_id)
1253 LOOP
1254 IF(nvl(rec.adjust_status,'X') <> 'FROZEN') THEN
1255 cn_adjustments_pkg.api_negate_record(
1256 rec.comm_lines_api_id,
1257 x_adjusted_by,
1258 x_adjust_comments);
1259 END IF;
1260 END LOOP;
1261 ELSIF (l_load_status = 'UNLOADED') THEN
1262 FOR rec IN api_cur(l_comm_lines_api_id)
1263 LOOP
1264 IF(nvl(rec.adjust_status,'X') <> 'FROZEN') THEN
1265 cn_adjustments_pkg.api_negate_record(
1266 rec.comm_lines_api_id,
1267 x_adjusted_by,
1268 x_adjust_comments);
1269 END IF;
1270 END LOOP;
1271 END IF;
1272 --
1273 --cn_adjustments_pkg.get_api_data(l_comm_lines_api_id,l_adj_tbl);
1274 cn_get_tx_data_pub.get_api_data(l_comm_lines_api_id,l_adj_tbl);
1275 --
1276 IF (l_adj_tbl.COUNT > 0) THEN
1277 /* codeCheck: I need to revisit this code */
1278 l_transaction_amount := NVL(l_adj_tbl(1).transaction_amount,0);
1279 l_acctd_transaction_amount
1280 := NVL(l_adj_tbl(1).transaction_amount,0);
1281 --
1282 /*
1283 IF(nvl(l_adj_tbl(1).adjust_status,'X') <> 'FROZEN') THEN
1284 cn_adjustments_pkg.api_negate_record(
1285 l_adj_tbl(1).comm_lines_api_id,
1286 l_adjusted_by,
1287 l_adjust_comments);
1288 END IF; */
1289 --
1290 SELECT employee_number
1291 INTO l_salesrep_number
1292 FROM cn_salesreps
1293 WHERE salesrep_id = x_to_salesrep_id;
1294 --
1295 SELECT cn_comm_lines_api_s.NEXTVAL
1296 INTO l_ins_comm_lines_api_id
1297 FROM dual;
1298 --
1299 l_newtx_rec.direct_salesrep_id := x_to_salesrep_id;
1300 l_newtx_rec.transaction_amount := l_transaction_amount;
1301 l_newtx_rec.load_status := 'UNLOADED';
1302 l_newtx_rec.comm_lines_api_id := l_ins_comm_lines_api_id;
1303 l_newtx_rec.transaction_amount_orig := l_acctd_transaction_amount;
1304 l_newtx_rec.adjust_date := SYSDATE;
1305 l_newtx_rec.adjusted_by := x_adjusted_by;
1306 l_newtx_rec.revenue_type := 'REVENUE';
1307 l_newtx_rec.adjust_comments := x_adjust_comments;
1308 l_newtx_rec.adjust_status := 'DEALASGN';
1309 l_newtx_rec.adj_comm_lines_api_id := l_adj_tbl(1).comm_lines_api_id;
1310 l_newtx_rec.direct_salesrep_number := l_salesrep_number;
1311 --
1312 cn_invoice_changes_pvt.prepare_api_record(
1313 p_newtx_rec => l_newtx_rec,
1314 p_old_adj_tbl => l_adj_tbl,
1315 x_final_trx_rec => o_newtx_rec,
1316 x_return_status => x_return_status);
1317 -- codeCheck: I will explain later about this conversion.
1318 cn_invoice_changes_pvt.convert_adj_to_api(
1319 p_adj_rec => o_newtx_rec,
1320 x_api_rec => l_api_rec);
1321 --
1322 cn_comm_lines_api_pkg.insert_row(l_api_rec);
1323 --
1324 END IF;
1325 ELSE
1326 exit;
1327 END IF;
1328 END LOOP;
1329 DBMS_SQL.close_cursor(assign_select_cursor);
1330 END deal_assign;
1331 --
1332 PROCEDURE get_cust_info(
1333 p_comm_lines_api_id IN NUMBER,
1334 p_load_status IN VARCHAR2,
1335 x_cust_info_rec OUT NOCOPY cust_info_rec) IS
1336 BEGIN
1337 -- First check in header table.
1338 BEGIN
1339 SELECT CCH.customer_id,RAC.customer_number,RAC.customer_name,
1340 CCH.bill_to_address_id,RABA.address1,RABA.address2,
1341 RABA.address3,RABA.address4,RABA.city,RABA.postal_code,
1342 RABA.state,CCH.ship_to_address_id,RASA.address1,
1343 RASA.address2,RASA.address3,RASA.address4,RASA.city,
1344 RASA.postal_code,RASA.state,CCH.bill_to_contact_id,
1345 RABC.person_last_name||', ' ||RABC.person_first_name,
1346 CCH.ship_to_contact_id,
1347 RASC.person_last_name||', '||RASC.person_first_name
1348
1349 INTO x_cust_info_rec.customer_id,
1350 x_cust_info_rec.customer_number,
1351 x_cust_info_rec.customer_name,
1352 x_cust_info_rec.bill_to_address_id,
1356 x_cust_info_rec.bill_to_address4,
1353 x_cust_info_rec.bill_to_address1,
1354 x_cust_info_rec.bill_to_address2,
1355 x_cust_info_rec.bill_to_address3,
1357 x_cust_info_rec.bill_to_city,
1358 x_cust_info_rec.bill_to_postal_code,
1359 x_cust_info_rec.bill_to_state,
1360 x_cust_info_rec.ship_to_address_id,
1361 x_cust_info_rec.ship_to_address1,
1362 x_cust_info_rec.ship_to_address2,
1363 x_cust_info_rec.ship_to_address3,
1364 x_cust_info_rec.ship_to_address4,
1365 x_cust_info_rec.ship_to_city,
1366 x_cust_info_rec.ship_to_postal_code,
1367 x_cust_info_rec.ship_to_state,
1368 x_cust_info_rec.bill_to_contact_id,
1369 x_cust_info_rec.bill_to_contact,
1370 x_cust_info_rec.ship_to_contact_id,
1371 x_cust_info_rec.ship_to_contact
1372
1373 FROM cn_commission_headers cch,
1374 (SELECT CUST_ACCT.CUST_ACCOUNT_ID CUSTOMER_ID, substrb(PARTY.PARTY_NAME,1,50) CUSTOMER_NAME,
1375 CUST_ACCT.ACCOUNT_NUMBER CUSTOMER_NUMBER
1376 FROM HZ_PARTIES PARTY, HZ_CUST_ACCOUNTS CUST_ACCT
1377 WHERE CUST_ACCT.PARTY_ID = PARTY.PARTY_ID) rac,
1378 (SELECT ACCT_SITE.CUST_ACCT_SITE_ID /* ADDRESS_ID */ ,
1379 LOC.ADDRESS1 , LOC.ADDRESS2 , LOC.ADDRESS3 , LOC.ADDRESS4 , LOC.CITY , LOC.POSTAL_CODE ,
1380 LOC.STATE FROM HZ_PARTY_SITES PARTY_SITE, HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
1381 HZ_LOCATIONS LOC, HZ_CUST_ACCT_SITES_ALL ACCT_SITE
1382 WHERE ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
1383 AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
1384 AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
1385 AND NVL(ACCT_SITE.ORG_ID, -99) = NVL(LOC_ASSIGN.ORG_ID, -99)) raba,
1386 (SELECT ACCT_SITE.CUST_ACCT_SITE_ID /* ADDRESS_ID */ ,
1387 LOC.ADDRESS1 , LOC.ADDRESS2 , LOC.ADDRESS3 , LOC.ADDRESS4 , LOC.CITY , LOC.POSTAL_CODE ,
1388 LOC.STATE FROM HZ_PARTY_SITES PARTY_SITE, HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
1389 HZ_LOCATIONS LOC, HZ_CUST_ACCT_SITES_ALL ACCT_SITE
1390 WHERE ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
1391 AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
1392 AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
1393 AND NVL(ACCT_SITE.ORG_ID, -99) = NVL(LOC_ASSIGN.ORG_ID, -99)) rasa,
1394 (SELECT ACCT_ROLE.CUST_ACCOUNT_ROLE_ID /* CONTACT_ID */ ,
1395 substrb(PARTY.PERSON_LAST_NAME,1,50) PERSON_LAST_NAME,
1396 substrb(PARTY.PERSON_FIRST_NAME,1,40) PERSON_FIRST_NAME
1397 FROM HZ_CUST_ACCOUNT_ROLES ACCT_ROLE, HZ_PARTIES PARTY, HZ_RELATIONSHIPS REL,
1398 HZ_ORG_CONTACTS ORG_CONT, HZ_PARTIES REL_PARTY, HZ_CUST_ACCOUNTS ROLE_ACCT
1399 WHERE ACCT_ROLE.PARTY_ID = REL.PARTY_ID
1400 AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
1401 AND ORG_CONT.PARTY_RELATIONSHIP_ID = REL.RELATIONSHIP_ID
1402 AND REL.SUBJECT_ID = PARTY.PARTY_ID
1403 AND REL.PARTY_ID = REL_PARTY.PARTY_ID
1404 AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
1405 AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
1406 AND ACCT_ROLE.CUST_ACCOUNT_ID = ROLE_ACCT.CUST_ACCOUNT_ID
1407 AND ROLE_ACCT.PARTY_ID = REL.OBJECT_ID /* AND REL.DIRECTIONAL_FLAG = 'F' */
1408 ) rabc,
1409 (SELECT ACCT_ROLE.CUST_ACCOUNT_ROLE_ID /* CONTACT_ID */ ,
1410 substrb(PARTY.PERSON_LAST_NAME,1,50) PERSON_LAST_NAME,
1411 substrb(PARTY.PERSON_FIRST_NAME,1,40) PERSON_FIRST_NAME
1412 FROM HZ_CUST_ACCOUNT_ROLES ACCT_ROLE, HZ_PARTIES PARTY, HZ_RELATIONSHIPS REL,
1413 HZ_ORG_CONTACTS ORG_CONT, HZ_PARTIES REL_PARTY, HZ_CUST_ACCOUNTS ROLE_ACCT
1414 WHERE ACCT_ROLE.PARTY_ID = REL.PARTY_ID
1415 AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
1416 AND ORG_CONT.PARTY_RELATIONSHIP_ID = REL.RELATIONSHIP_ID
1417 AND REL.SUBJECT_ID = PARTY.PARTY_ID
1418 AND REL.PARTY_ID = REL_PARTY.PARTY_ID
1419 AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
1420 AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
1421 AND ACCT_ROLE.CUST_ACCOUNT_ID = ROLE_ACCT.CUST_ACCOUNT_ID
1422 AND ROLE_ACCT.PARTY_ID = REL.OBJECT_ID /* AND REL.DIRECTIONAL_FLAG = 'F' */
1423 ) rasc
1424 WHERE CCH.comm_lines_api_id = p_comm_lines_api_id
1425 AND CCH.customer_id = RAC.customer_id (+)
1426 AND CCH.bill_to_address_id = RABA.CUST_ACCT_SITE_ID (+)
1427 AND CCH.ship_to_address_id = RASA.CUST_ACCT_SITE_ID (+)
1428 AND CCH.bill_to_contact_id = RABC.CUST_ACCOUNT_ROLE_ID (+)
1429 AND CCH.ship_to_contact_id = RASC.CUST_ACCOUNT_ROLE_ID (+)
1430 AND ROWNUM < 2;
1431 EXCEPTION
1432 WHEN OTHERS THEN
1433 BEGIN
1434 SELECT CCLA.customer_id,RAC.customer_number,RAC.customer_name,
1435 CCLA.bill_to_address_id,RABA.address1,RABA.address2,
1436 RABA.address3,RABA.address4,RABA.city,RABA.postal_code,
1437 RABA.state,CCLA.ship_to_address_id,RASA.address1,
1438 RASA.address2,RASA.address3,RASA.address4,RASA.city,
1439 RASA.postal_code,RASA.state,CCLA.bill_to_contact_id,
1440 RABC.person_last_name||', ' ||RABC.person_first_name,
1441 CCLA.ship_to_contact_id,
1442 RASC.person_last_name||', '||RASC.person_first_name
1443
1444 INTO x_cust_info_rec.customer_id,
1445 x_cust_info_rec.customer_number,
1446 x_cust_info_rec.customer_name,
1450 x_cust_info_rec.bill_to_address3,
1447 x_cust_info_rec.bill_to_address_id,
1448 x_cust_info_rec.bill_to_address1,
1449 x_cust_info_rec.bill_to_address2,
1451 x_cust_info_rec.bill_to_address4,
1452 x_cust_info_rec.bill_to_city,
1453 x_cust_info_rec.bill_to_postal_code,
1454 x_cust_info_rec.bill_to_state,
1455 x_cust_info_rec.ship_to_address_id,
1456 x_cust_info_rec.ship_to_address1,
1457 x_cust_info_rec.ship_to_address2,
1458 x_cust_info_rec.ship_to_address3,
1459 x_cust_info_rec.ship_to_address4,
1460 x_cust_info_rec.ship_to_city,
1461 x_cust_info_rec.ship_to_postal_code,
1462 x_cust_info_rec.ship_to_state,
1463 x_cust_info_rec.bill_to_contact_id,
1464 x_cust_info_rec.bill_to_contact,
1465 x_cust_info_rec.ship_to_contact_id,
1466 x_cust_info_rec.ship_to_contact
1467
1468 FROM cn_comm_lines_api ccla,
1469 (SELECT CUST_ACCT.CUST_ACCOUNT_ID CUSTOMER_ID, substrb(PARTY.PARTY_NAME,1,50) CUSTOMER_NAME,
1470 CUST_ACCT.ACCOUNT_NUMBER CUSTOMER_NUMBER
1471 FROM HZ_PARTIES PARTY, HZ_CUST_ACCOUNTS CUST_ACCT
1472 WHERE CUST_ACCT.PARTY_ID = PARTY.PARTY_ID) rac,
1473 (SELECT ACCT_SITE.CUST_ACCT_SITE_ID /* ADDRESS_ID */ ,
1474 LOC.ADDRESS1 , LOC.ADDRESS2 , LOC.ADDRESS3 , LOC.ADDRESS4 , LOC.CITY , LOC.POSTAL_CODE ,
1475 LOC.STATE FROM HZ_PARTY_SITES PARTY_SITE, HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
1476 HZ_LOCATIONS LOC, HZ_CUST_ACCT_SITES_ALL ACCT_SITE
1477 WHERE ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
1478 AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
1479 AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
1480 AND NVL(ACCT_SITE.ORG_ID, -99) = NVL(LOC_ASSIGN.ORG_ID, -99)) raba,
1481 (SELECT ACCT_SITE.CUST_ACCT_SITE_ID /* ADDRESS_ID */ ,
1482 LOC.ADDRESS1 , LOC.ADDRESS2 , LOC.ADDRESS3 , LOC.ADDRESS4 , LOC.CITY , LOC.POSTAL_CODE ,
1483 LOC.STATE FROM HZ_PARTY_SITES PARTY_SITE, HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
1484 HZ_LOCATIONS LOC, HZ_CUST_ACCT_SITES_ALL ACCT_SITE
1485 WHERE ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
1486 AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
1487 AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
1488 AND NVL(ACCT_SITE.ORG_ID, -99) = NVL(LOC_ASSIGN.ORG_ID, -99)) rasa,
1489 (SELECT ACCT_ROLE.CUST_ACCOUNT_ROLE_ID /* CONTACT_ID */ ,
1490 substrb(PARTY.PERSON_LAST_NAME,1,50) PERSON_LAST_NAME,
1491 substrb(PARTY.PERSON_FIRST_NAME,1,40) PERSON_FIRST_NAME
1492 FROM HZ_CUST_ACCOUNT_ROLES ACCT_ROLE, HZ_PARTIES PARTY, HZ_RELATIONSHIPS REL,
1493 HZ_ORG_CONTACTS ORG_CONT, HZ_PARTIES REL_PARTY, HZ_CUST_ACCOUNTS ROLE_ACCT
1494 WHERE ACCT_ROLE.PARTY_ID = REL.PARTY_ID
1495 AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
1496 AND ORG_CONT.PARTY_RELATIONSHIP_ID = REL.RELATIONSHIP_ID
1497 AND REL.SUBJECT_ID = PARTY.PARTY_ID
1498 AND REL.PARTY_ID = REL_PARTY.PARTY_ID
1499 AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
1500 AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
1501 AND ACCT_ROLE.CUST_ACCOUNT_ID = ROLE_ACCT.CUST_ACCOUNT_ID
1502 AND ROLE_ACCT.PARTY_ID = REL.OBJECT_ID /* AND REL.DIRECTIONAL_FLAG = 'F' */
1503 ) rabc,
1504 (SELECT ACCT_ROLE.CUST_ACCOUNT_ROLE_ID,
1505 substrb(PARTY.PERSON_LAST_NAME,1,50) PERSON_LAST_NAME,
1506 substrb(PARTY.PERSON_FIRST_NAME,1,40) PERSON_FIRST_NAME
1507 FROM HZ_CUST_ACCOUNT_ROLES ACCT_ROLE, HZ_PARTIES PARTY, HZ_RELATIONSHIPS REL,
1508 HZ_ORG_CONTACTS ORG_CONT, HZ_PARTIES REL_PARTY, HZ_CUST_ACCOUNTS ROLE_ACCT
1509 WHERE ACCT_ROLE.PARTY_ID = REL.PARTY_ID
1510 AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
1511 AND ORG_CONT.PARTY_RELATIONSHIP_ID = REL.RELATIONSHIP_ID
1512 AND REL.SUBJECT_ID = PARTY.PARTY_ID
1513 AND REL.PARTY_ID = REL_PARTY.PARTY_ID
1514 AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
1515 AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
1516 AND ACCT_ROLE.CUST_ACCOUNT_ID = ROLE_ACCT.CUST_ACCOUNT_ID
1517 AND ROLE_ACCT.PARTY_ID = REL.OBJECT_ID /* AND REL.DIRECTIONAL_FLAG = 'F' */
1518 ) rasc
1519 WHERE ccla.comm_lines_api_id = p_comm_lines_api_id
1520 AND ccla.customer_id = RAC.customer_id (+)
1521 AND ccla.bill_to_address_id = RABA.CUST_ACCT_SITE_ID (+)
1522 AND ccla.ship_to_address_id = RASA.CUST_ACCT_SITE_ID (+)
1523 AND ccla.bill_to_contact_id = RABC.CUST_ACCOUNT_ROLE_ID (+)
1524 AND ccla.ship_to_contact_id = RASC.CUST_ACCOUNT_ROLE_ID (+)
1525 AND ROWNUM < 2;
1526 EXCEPTION
1527 WHEN OTHERS THEN
1528 NULL;
1529 END;
1530 END;
1531 EXCEPTION
1532 WHEN OTHERS THEN
1533 NULL;
1534 END;
1535
1536 PROCEDURE update_credit_credentials (
1537 p_comm_lines_api_id IN NUMBER,
1538 p_terr_id IN NUMBER,
1539 p_org_id IN NUMBER,
1540 p_adjusted_by IN VARCHAR2
1541 )
1542 IS
1543 /* Added to fix Crediting bug */
1547 WHERE ORG_ID = l_nbr_org_id
1544 CURSOR l_csr_credited_trx(l_nbr_comm_lines_api_id NUMBER, l_nbr_org_id NUMBER) IS
1545 SELECT COMM_LINES_API_ID, ORG_ID
1546 FROM CN_COMM_LINES_API
1548 AND TERR_ID IS NOT NULL
1549 AND (adjust_status NOT IN ('FROZEN','REVERSAL','SCA PENDING') OR
1550 adjust_status IS NULL)
1551 START WITH COMM_LINES_API_ID = l_nbr_comm_lines_api_id
1552 CONNECT BY PRIOR COMM_LINES_API_ID = ADJ_COMM_LINES_API_ID;
1553
1554
1555 CURSOR l_csr_parent_trx(l_nbr_comm_lines_api_id NUMBER, l_nbr_org_id NUMBER) IS
1556 SELECT COMM_LINES_API_ID, ORG_ID FROM CN_COMM_LINES_API
1557 WHERE ORG_ID = l_nbr_org_id
1558 AND TERR_ID IS NULL
1559 AND (adjust_status NOT IN ('FROZEN','REVERSAL','SCA PENDING') OR
1560 adjust_status IS NULL)
1561 START WITH COMM_LINES_API_ID = l_nbr_comm_lines_api_id
1562 CONNECT BY PRIOR ADJ_COMM_LINES_API_ID = COMM_LINES_API_ID;
1563
1564 l_adj_comm_lines_api_id NUMBER;
1565 BEGIN
1566 /* Code added for Crediting, to obsolete any child records that have gone through
1567 the crediting cycle */
1568 IF p_terr_id IS NULL
1569 THEN
1570 FOR each_trx IN l_csr_credited_trx(p_comm_lines_api_id, p_org_id)
1571 LOOP
1572
1573 cn_adjustments_pkg.api_negate_record(
1574 x_comm_lines_api_id => each_trx.comm_lines_api_id,
1575 x_adjusted_by => p_adjusted_by,
1576 x_adjust_comments => 'Parent transaction modified');
1577 END LOOP;
1578 ELSE IF p_terr_id IS NOT NULL
1579 THEN
1580 FOR parent_trx IN l_csr_parent_trx(p_comm_lines_api_id, p_org_id)
1581 LOOP
1582 UPDATE CN_COMM_LINES_API
1583 SET PRESERVE_CREDIT_OVERRIDE_FLAG = 'Y',
1584 ADJUSTED_BY = p_adjusted_by
1585 WHERE COMM_LINES_API_ID = parent_trx.comm_lines_api_id
1586 AND ORG_ID = parent_trx.org_id;
1587 END LOOP;
1588 END IF;
1589 END IF;
1590
1591 --
1592 END update_credit_credentials;
1593
1594 --
1595 END cn_adjustments_pkg;
1596
1597
1598