[Home] [Help]
PACKAGE BODY: APPS.CN_ADJUSTMENTS_PKG
Source
4 -- | Copyright (c) 1994 Oracle Corporation |
1 PACKAGE BODY CN_ADJUSTMENTS_PKG AS
2 -- $Header: cntradjb.pls 120.10.12010000.5 2009/06/30 07:02:08 gmarwah ship $
3 -- +======================================================================+
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);
53 IF (dbms_sql.fetch_rows(select_cursor) > 0) THEN
50 count_rows := DBMS_SQL.execute (select_cursor);
51 tab_mass_update_comm.delete;
52 LOOP
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,NVL(preserve_credit_override_flag,'N') 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 := NVL(each_trx.preserve_credit_override_flag,'N');
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
172 l_quantity := -1 * l_quantity;
169 -- adjust_status IS NULL)
170 AND trx_type NOT IN ('ITD','GRP','THR');
171 --
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;
252 l_api_rec.attribute48 := api_curs_rec.attribute48;
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;
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;
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;
321 l_api_rec.trx_line_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;
341 l_api_rec.invoice_number := api_curs_rec.invoice_number;
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;
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 := NVL(l_adjust_status,'NEW');
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 := NVL(l_presrv_credit_override_flag,'N');
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
391
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;
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);
421 l_adjust_date DATE := SYSDATE;
422 l_comm_lines_api_id NUMBER;
423 l_counter NUMBER := 0;
424 -- PL/SQL tables/columns
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
439 x_adj_data(i).trx_type NOT IN ('ITD','GRP','THR') AND
436 IF ((x_adj_data(i).adjust_status NOT IN('FROZEN','REVERSAL','SCA_PENDING'))-- OR
437 --x_adj_data(i).adjust_status IS null)
438 AND
440 x_adj_data(i).load_status NOT IN ('FILTERED')) THEN
441 IF (x_mass_adj_type = 'M') THEN
442 l_adjust_status := 'MASSADJ';
443 ELSE
444 l_adjust_status := 'MASSASGN';
445 END IF;
446 --
447 SELECT cn_comm_lines_api_s.NEXTVAL
448 INTO l_comm_lines_api_id
449 FROM DUAL;
450 --
451
452
453 SELECT DECODE(x_adj_rec.direct_salesrep_id,fnd_api.g_miss_num,
454 DECODE(x_adj_data(i).direct_salesrep_id,fnd_api.g_miss_num,NULL,
455 x_adj_data(i).direct_salesrep_id),
456 x_adj_rec.direct_salesrep_id),
457 DECODE(x_adj_rec.processed_date,fnd_api.g_miss_date,
458 DECODE(x_adj_data(i).processed_date,fnd_api.g_miss_date,NULL,
459 x_adj_data(i).processed_date),
460 x_adj_rec.processed_date),
461 DECODE(x_adj_rec.processed_period_id,fnd_api.g_miss_num,
462 DECODE(x_adj_data(i).processed_period_id,fnd_api.g_miss_num,NULL,
463 x_adj_data(i).processed_period_id),
464 x_adj_rec.processed_period_id),
465 DECODE(x_adj_rec.transaction_amount,fnd_api.g_miss_num,
466 DECODE(x_adj_data(i).transaction_amount,fnd_api.g_miss_num,NULL,
467 x_adj_data(i).transaction_amount),
468 x_adj_rec.transaction_amount),
469 DECODE(x_adj_rec.trx_type,fnd_api.g_miss_char,
470 DECODE(x_adj_data(i).trx_type,fnd_api.g_miss_char,NULL,
471 x_adj_data(i).trx_type),x_adj_rec.trx_type),
472 DECODE(x_adj_rec.revenue_class_id,fnd_api.g_miss_num,
473 DECODE(x_adj_data(i).revenue_class_id,fnd_api.g_miss_num,NULL,
474 x_adj_data(i).revenue_class_id),
475 x_adj_rec.revenue_class_id),
476 'UNLOADED',
477 DECODE(x_adj_rec.attribute1,fnd_api.g_miss_char,
478 DECODE(x_adj_data(i).attribute1,fnd_api.g_miss_char,NULL,
479 x_adj_data(i).attribute1),x_adj_rec.attribute1),
480 DECODE(x_adj_rec.attribute2,fnd_api.g_miss_char,
481 DECODE(x_adj_data(i).attribute2,fnd_api.g_miss_char,NULL,
482 x_adj_data(i).attribute2),x_adj_rec.attribute2),
483 DECODE(x_adj_rec.attribute3,fnd_api.g_miss_char,
484 DECODE(x_adj_data(i).attribute3,fnd_api.g_miss_char,NULL,
485 x_adj_data(i).attribute3),x_adj_rec.attribute3),
486 DECODE(x_adj_rec.attribute4,fnd_api.g_miss_char,
487 DECODE(x_adj_data(i).attribute4,fnd_api.g_miss_char,NULL,
488 x_adj_data(i).attribute4),x_adj_rec.attribute4),
489 DECODE(x_adj_rec.attribute5,fnd_api.g_miss_char,
490 DECODE(x_adj_data(i).attribute5,fnd_api.g_miss_char,NULL,
491 x_adj_data(i).attribute5),x_adj_rec.attribute5),
492 DECODE(x_adj_rec.attribute6,fnd_api.g_miss_char,
493 DECODE(x_adj_data(i).attribute6,fnd_api.g_miss_char,NULL,
494 x_adj_data(i).attribute6),x_adj_rec.attribute6),
495 DECODE(x_adj_rec.attribute7,fnd_api.g_miss_char,
496 DECODE(x_adj_data(i).attribute7,fnd_api.g_miss_char,NULL,
497 x_adj_data(i).attribute7),x_adj_rec.attribute7),
498 DECODE(x_adj_rec.attribute8,fnd_api.g_miss_char,
499 DECODE(x_adj_data(i).attribute8,fnd_api.g_miss_char,NULL,
500 x_adj_data(i).attribute8),x_adj_rec.attribute8),
501 DECODE(x_adj_rec.attribute9,fnd_api.g_miss_char,
502 DECODE(x_adj_data(i).attribute9,fnd_api.g_miss_char,NULL,
503 x_adj_data(i).attribute9),x_adj_rec.attribute9),
504 DECODE(x_adj_rec.attribute10,fnd_api.g_miss_char,
505 DECODE(x_adj_data(i).attribute10,fnd_api.g_miss_char,NULL,
506 x_adj_data(i).attribute10),x_adj_rec.attribute10),
507 DECODE(x_adj_rec.attribute11,fnd_api.g_miss_char,
508 DECODE(x_adj_data(i).attribute11,fnd_api.g_miss_char,NULL,
509 x_adj_data(i).attribute11),x_adj_rec.attribute11),
510 DECODE(x_adj_rec.attribute12,fnd_api.g_miss_char,
511 DECODE(x_adj_data(i).attribute12,fnd_api.g_miss_char,NULL,
512 x_adj_data(i).attribute12),x_adj_rec.attribute12),
513 DECODE(x_adj_rec.attribute13,fnd_api.g_miss_char,
514 DECODE(x_adj_data(i).attribute13,fnd_api.g_miss_char,NULL,
515 x_adj_data(i).attribute13),x_adj_rec.attribute13),
516 DECODE(x_adj_rec.attribute14,fnd_api.g_miss_char,
517 DECODE(x_adj_data(i).attribute14,fnd_api.g_miss_char,NULL,
518 x_adj_data(i).attribute14),x_adj_rec.attribute14),
519 DECODE(x_adj_rec.attribute15,fnd_api.g_miss_char,
520 DECODE(x_adj_data(i).attribute15,fnd_api.g_miss_char,NULL,
521 x_adj_data(i).attribute15),x_adj_rec.attribute15),
522 DECODE(x_adj_rec.attribute16,fnd_api.g_miss_char,
526 DECODE(x_adj_data(i).attribute17,fnd_api.g_miss_char,NULL,
523 DECODE(x_adj_data(i).attribute16,fnd_api.g_miss_char,NULL,
524 x_adj_data(i).attribute16),x_adj_rec.attribute16),
525 DECODE(x_adj_rec.attribute17,fnd_api.g_miss_char,
527 x_adj_data(i).attribute17),x_adj_rec.attribute17),
528 DECODE(x_adj_rec.attribute18,fnd_api.g_miss_char,
529 DECODE(x_adj_data(i).attribute18,fnd_api.g_miss_char,NULL,
530 x_adj_data(i).attribute18),x_adj_rec.attribute18),
531 DECODE(x_adj_rec.attribute19,fnd_api.g_miss_char,
532 DECODE(x_adj_data(i).attribute19,fnd_api.g_miss_char,NULL,
533 x_adj_data(i).attribute19),x_adj_rec.attribute19),
534 DECODE(x_adj_rec.attribute20,fnd_api.g_miss_char,
535 DECODE(x_adj_data(i).attribute20,fnd_api.g_miss_char,NULL,
536 x_adj_data(i).attribute20),x_adj_rec.attribute20),
537 DECODE(x_adj_rec.attribute21,fnd_api.g_miss_char,
538 DECODE(x_adj_data(i).attribute21,fnd_api.g_miss_char,NULL,
539 x_adj_data(i).attribute21),x_adj_rec.attribute21),
540 DECODE(x_adj_rec.attribute22,fnd_api.g_miss_char,
541 DECODE(x_adj_data(i).attribute22,fnd_api.g_miss_char,NULL,
542 x_adj_data(i).attribute22),x_adj_rec.attribute22),
543 DECODE(x_adj_rec.attribute23,fnd_api.g_miss_char,
544 DECODE(x_adj_data(i).attribute23,fnd_api.g_miss_char,NULL,
545 x_adj_data(i).attribute23),x_adj_rec.attribute23),
546 DECODE(x_adj_rec.attribute24,fnd_api.g_miss_char,
547 DECODE(x_adj_data(i).attribute24,fnd_api.g_miss_char,NULL,
548 x_adj_data(i).attribute24),x_adj_rec.attribute24),
549 DECODE(x_adj_rec.attribute25,fnd_api.g_miss_char,
550 DECODE(x_adj_data(i).attribute25,fnd_api.g_miss_char,NULL,
551 x_adj_data(i).attribute25),x_adj_rec.attribute25),
552 DECODE(x_adj_rec.attribute26,fnd_api.g_miss_char,
553 DECODE(x_adj_data(i).attribute26,fnd_api.g_miss_char,NULL,
554 x_adj_data(i).attribute26),x_adj_rec.attribute26),
555 DECODE(x_adj_rec.attribute27,fnd_api.g_miss_char,
556 DECODE(x_adj_data(i).attribute27,fnd_api.g_miss_char,NULL,
557 x_adj_data(i).attribute27),x_adj_rec.attribute27),
558 DECODE(x_adj_rec.attribute28,fnd_api.g_miss_char,
559 DECODE(x_adj_data(i).attribute28,fnd_api.g_miss_char,NULL,
560 x_adj_data(i).attribute28),x_adj_rec.attribute28),
561 DECODE(x_adj_rec.attribute29,fnd_api.g_miss_char,
562 DECODE(x_adj_data(i).attribute29,fnd_api.g_miss_char,NULL,
563 x_adj_data(i).attribute29),x_adj_rec.attribute29),
564 DECODE(x_adj_rec.attribute30,fnd_api.g_miss_char,
565 DECODE(x_adj_data(i).attribute30,fnd_api.g_miss_char,NULL,
566 x_adj_data(i).attribute30),x_adj_rec.attribute30),
567 DECODE(x_adj_rec.attribute31,fnd_api.g_miss_char,
568 DECODE(x_adj_data(i).attribute31,fnd_api.g_miss_char,NULL,
569 x_adj_data(i).attribute31),x_adj_rec.attribute31),
570 DECODE(x_adj_rec.attribute32,fnd_api.g_miss_char,
571 DECODE(x_adj_data(i).attribute32,fnd_api.g_miss_char,NULL,
572 x_adj_data(i).attribute32),x_adj_rec.attribute32),
573 DECODE(x_adj_rec.attribute33,fnd_api.g_miss_char,
574 DECODE(x_adj_data(i).attribute33,fnd_api.g_miss_char,NULL,
575 x_adj_data(i).attribute33),x_adj_rec.attribute33),
576 DECODE(x_adj_rec.attribute34,fnd_api.g_miss_char,
577 DECODE(x_adj_data(i).attribute34,fnd_api.g_miss_char,NULL,
578 x_adj_data(i).attribute34),x_adj_rec.attribute34),
579 DECODE(x_adj_rec.attribute35,fnd_api.g_miss_char,
580 DECODE(x_adj_data(i).attribute35,fnd_api.g_miss_char,NULL,
581 x_adj_data(i).attribute35),x_adj_rec.attribute35),
582 DECODE(x_adj_rec.attribute36,fnd_api.g_miss_char,
583 DECODE(x_adj_data(i).attribute36,fnd_api.g_miss_char,NULL,
584 x_adj_data(i).attribute36),x_adj_rec.attribute36),
585 DECODE(x_adj_rec.attribute37,fnd_api.g_miss_char,
586 DECODE(x_adj_data(i).attribute37,fnd_api.g_miss_char,NULL,
587 x_adj_data(i).attribute37),x_adj_rec.attribute37),
588 DECODE(x_adj_rec.attribute38,fnd_api.g_miss_char,
589 DECODE(x_adj_data(i).attribute38,fnd_api.g_miss_char,NULL,
590 x_adj_data(i).attribute38),x_adj_rec.attribute38),
591 DECODE(x_adj_rec.attribute39,fnd_api.g_miss_char,
592 DECODE(x_adj_data(i).attribute39,fnd_api.g_miss_char,NULL,
593 x_adj_data(i).attribute39),x_adj_rec.attribute39),
594 DECODE(x_adj_rec.attribute40,fnd_api.g_miss_char,
595 DECODE(x_adj_data(i).attribute40,fnd_api.g_miss_char,NULL,
596 x_adj_data(i).attribute40),x_adj_rec.attribute40),
597 DECODE(x_adj_rec.attribute41,fnd_api.g_miss_char,
598 DECODE(x_adj_data(i).attribute41,fnd_api.g_miss_char,NULL,
599 x_adj_data(i).attribute41),x_adj_rec.attribute41),
600 DECODE(x_adj_rec.attribute42,fnd_api.g_miss_char,
601 DECODE(x_adj_data(i).attribute42,fnd_api.g_miss_char,NULL,
602 x_adj_data(i).attribute42),x_adj_rec.attribute42),
603 DECODE(x_adj_rec.attribute43,fnd_api.g_miss_char,
604 DECODE(x_adj_data(i).attribute43,fnd_api.g_miss_char,NULL,
605 x_adj_data(i).attribute43),x_adj_rec.attribute43),
609 DECODE(x_adj_rec.attribute45,fnd_api.g_miss_char,
606 DECODE(x_adj_rec.attribute44,fnd_api.g_miss_char,
607 DECODE(x_adj_data(i).attribute44,fnd_api.g_miss_char,NULL,
608 x_adj_data(i).attribute44),x_adj_rec.attribute44),
610 DECODE(x_adj_data(i).attribute45,fnd_api.g_miss_char,NULL,
611 x_adj_data(i).attribute45),x_adj_rec.attribute45),
612 DECODE(x_adj_rec.attribute46,fnd_api.g_miss_char,
613 DECODE(x_adj_data(i).attribute46,fnd_api.g_miss_char,NULL,
614 x_adj_data(i).attribute46),x_adj_rec.attribute46),
615 DECODE(x_adj_rec.attribute47,fnd_api.g_miss_char,
616 DECODE(x_adj_data(i).attribute47,fnd_api.g_miss_char,NULL,
617 x_adj_data(i).attribute47),x_adj_rec.attribute47),
618 DECODE(x_adj_rec.attribute48,fnd_api.g_miss_char,
619 DECODE(x_adj_data(i).attribute48,fnd_api.g_miss_char,NULL,
620 x_adj_data(i).attribute48),x_adj_rec.attribute48),
621 DECODE(x_adj_rec.attribute49,fnd_api.g_miss_char,
622 DECODE(x_adj_data(i).attribute49,fnd_api.g_miss_char,NULL,
623 x_adj_data(i).attribute49),x_adj_rec.attribute49),
624 DECODE(x_adj_rec.attribute50,fnd_api.g_miss_char,
625 DECODE(x_adj_data(i).attribute50,fnd_api.g_miss_char,NULL,
626 x_adj_data(i).attribute50),x_adj_rec.attribute50),
627 DECODE(x_adj_rec.attribute51,fnd_api.g_miss_char,
628 DECODE(x_adj_data(i).attribute51,fnd_api.g_miss_char,NULL,
629 x_adj_data(i).attribute51),x_adj_rec.attribute51),
630 DECODE(x_adj_rec.attribute52,fnd_api.g_miss_char,
631 DECODE(x_adj_data(i).attribute52,fnd_api.g_miss_char,NULL,
632 x_adj_data(i).attribute52),x_adj_rec.attribute52),
633 DECODE(x_adj_rec.attribute53,fnd_api.g_miss_char,
634 DECODE(x_adj_data(i).attribute53,fnd_api.g_miss_char,NULL,
635 x_adj_data(i).attribute53),x_adj_rec.attribute53),
636 DECODE(x_adj_rec.attribute54,fnd_api.g_miss_char,
637 DECODE(x_adj_data(i).attribute54,fnd_api.g_miss_char,NULL,
638 x_adj_data(i).attribute54),x_adj_rec.attribute54),
639 DECODE(x_adj_rec.attribute55,fnd_api.g_miss_char,
640 DECODE(x_adj_data(i).attribute55,fnd_api.g_miss_char,NULL,
641 x_adj_data(i).attribute55),x_adj_rec.attribute55),
642 DECODE(x_adj_rec.attribute56,fnd_api.g_miss_char,
643 DECODE(x_adj_data(i).attribute56,fnd_api.g_miss_char,NULL,
644 x_adj_data(i).attribute56),x_adj_rec.attribute56),
645 DECODE(x_adj_rec.attribute57,fnd_api.g_miss_char,
646 DECODE(x_adj_data(i).attribute57,fnd_api.g_miss_char,NULL,
647 x_adj_data(i).attribute57),x_adj_rec.attribute57),
648 DECODE(x_adj_rec.attribute58,fnd_api.g_miss_char,
649 DECODE(x_adj_data(i).attribute58,fnd_api.g_miss_char,NULL,
650 x_adj_data(i).attribute58),x_adj_rec.attribute58),
651 DECODE(x_adj_rec.attribute59,fnd_api.g_miss_char,
652 DECODE(x_adj_data(i).attribute59,fnd_api.g_miss_char,NULL,
653 x_adj_data(i).attribute59),x_adj_rec.attribute59),
654 DECODE(x_adj_rec.attribute60,fnd_api.g_miss_char,
655 DECODE(x_adj_data(i).attribute60,fnd_api.g_miss_char,NULL,
656 x_adj_data(i).attribute60),x_adj_rec.attribute60),
657 DECODE(x_adj_rec.attribute61,fnd_api.g_miss_char,
658 DECODE(x_adj_data(i).attribute61,fnd_api.g_miss_char,NULL,
659 x_adj_data(i).attribute61),x_adj_rec.attribute61),
660 DECODE(x_adj_rec.attribute62,fnd_api.g_miss_char,
661 DECODE(x_adj_data(i).attribute62,fnd_api.g_miss_char,NULL,
662 x_adj_data(i).attribute62),x_adj_rec.attribute62),
663 DECODE(x_adj_rec.attribute63,fnd_api.g_miss_char,
664 DECODE(x_adj_data(i).attribute63,fnd_api.g_miss_char,NULL,
665 x_adj_data(i).attribute63),x_adj_rec.attribute63),
666 DECODE(x_adj_rec.attribute64,fnd_api.g_miss_char,
667 DECODE(x_adj_data(i).attribute64,fnd_api.g_miss_char,NULL,
668 x_adj_data(i).attribute64),x_adj_rec.attribute64),
669 DECODE(x_adj_rec.attribute65,fnd_api.g_miss_char,
670 DECODE(x_adj_data(i).attribute65,fnd_api.g_miss_char,NULL,
671 x_adj_data(i).attribute65),x_adj_rec.attribute65),
672 DECODE(x_adj_rec.attribute66,fnd_api.g_miss_char,
673 DECODE(x_adj_data(i).attribute66,fnd_api.g_miss_char,NULL,
674 x_adj_data(i).attribute66),x_adj_rec.attribute66),
675 DECODE(x_adj_rec.attribute67,fnd_api.g_miss_char,
676 DECODE(x_adj_data(i).attribute67,fnd_api.g_miss_char,NULL,
677 x_adj_data(i).attribute67),x_adj_rec.attribute67),
678 DECODE(x_adj_rec.attribute68,fnd_api.g_miss_char,
679 DECODE(x_adj_data(i).attribute68,fnd_api.g_miss_char,NULL,
680 x_adj_data(i).attribute68),x_adj_rec.attribute68),
681 DECODE(x_adj_rec.attribute69,fnd_api.g_miss_char,
682 DECODE(x_adj_data(i).attribute69,fnd_api.g_miss_char,NULL,
683 x_adj_data(i).attribute69),x_adj_rec.attribute69),
684 DECODE(x_adj_rec.attribute70,fnd_api.g_miss_char,
685 DECODE(x_adj_data(i).attribute70,fnd_api.g_miss_char,NULL,
686 x_adj_data(i).attribute70),x_adj_rec.attribute70),
687 DECODE(x_adj_rec.attribute71,fnd_api.g_miss_char,
688 DECODE(x_adj_data(i).attribute71,fnd_api.g_miss_char,NULL,
689 x_adj_data(i).attribute71),x_adj_rec.attribute71),
690 DECODE(x_adj_rec.attribute72,fnd_api.g_miss_char,
691 DECODE(x_adj_data(i).attribute72,fnd_api.g_miss_char,NULL,
692 x_adj_data(i).attribute72),x_adj_rec.attribute72),
693 DECODE(x_adj_rec.attribute73,fnd_api.g_miss_char,
694 DECODE(x_adj_data(i).attribute73,fnd_api.g_miss_char,NULL,
698 x_adj_data(i).attribute74),x_adj_rec.attribute74),
695 x_adj_data(i).attribute73),x_adj_rec.attribute73),
696 DECODE(x_adj_rec.attribute74,fnd_api.g_miss_char,
697 DECODE(x_adj_data(i).attribute74,fnd_api.g_miss_char,NULL,
699 DECODE(x_adj_rec.attribute75,fnd_api.g_miss_char,
700 DECODE(x_adj_data(i).attribute75,fnd_api.g_miss_char,NULL,
701 x_adj_data(i).attribute75),x_adj_rec.attribute75),
702 DECODE(x_adj_rec.attribute76,fnd_api.g_miss_char,
703 DECODE(x_adj_data(i).attribute76,fnd_api.g_miss_char,NULL,
704 x_adj_data(i).attribute76),x_adj_rec.attribute76),
705 DECODE(x_adj_rec.attribute77,fnd_api.g_miss_char,
706 DECODE(x_adj_data(i).attribute77,fnd_api.g_miss_char,NULL,
707 x_adj_data(i).attribute77),x_adj_rec.attribute77),
708 DECODE(x_adj_rec.attribute78,fnd_api.g_miss_char,
709 DECODE(x_adj_data(i).attribute78,fnd_api.g_miss_char,NULL,
710 x_adj_data(i).attribute78),x_adj_rec.attribute78),
711 DECODE(x_adj_rec.attribute79,fnd_api.g_miss_char,
712 DECODE(x_adj_data(i).attribute79,fnd_api.g_miss_char,NULL,
713 x_adj_data(i).attribute79),x_adj_rec.attribute79),
714 DECODE(x_adj_rec.attribute80,fnd_api.g_miss_char,
715 DECODE(x_adj_data(i).attribute80,fnd_api.g_miss_char,NULL,
716 x_adj_data(i).attribute80),x_adj_rec.attribute80),
717 DECODE(x_adj_rec.attribute81,fnd_api.g_miss_char,
718 DECODE(x_adj_data(i).attribute81,fnd_api.g_miss_char,NULL,
719 x_adj_data(i).attribute81),x_adj_rec.attribute81),
720 DECODE(x_adj_rec.attribute82,fnd_api.g_miss_char,
721 DECODE(x_adj_data(i).attribute82,fnd_api.g_miss_char,NULL,
722 x_adj_data(i).attribute82),x_adj_rec.attribute82),
723 DECODE(x_adj_rec.attribute83,fnd_api.g_miss_char,
724 DECODE(x_adj_data(i).attribute83,fnd_api.g_miss_char,NULL,
725 x_adj_data(i).attribute83),x_adj_rec.attribute83),
726 DECODE(x_adj_rec.attribute84,fnd_api.g_miss_char,
730 DECODE(x_adj_data(i).attribute85,fnd_api.g_miss_char,NULL,
727 DECODE(x_adj_data(i).attribute84,fnd_api.g_miss_char,NULL,
728 x_adj_data(i).attribute84),x_adj_rec.attribute84),
729 DECODE(x_adj_rec.attribute85,fnd_api.g_miss_char,
731 x_adj_data(i).attribute85),x_adj_rec.attribute85),
732 DECODE(x_adj_rec.attribute86,fnd_api.g_miss_char,
733 DECODE(x_adj_data(i).attribute86,fnd_api.g_miss_char,NULL,
734 x_adj_data(i).attribute86),x_adj_rec.attribute86),
735 DECODE(x_adj_rec.attribute87,fnd_api.g_miss_char,
736 DECODE(x_adj_data(i).attribute87,fnd_api.g_miss_char,NULL,
737 x_adj_data(i).attribute87),x_adj_rec.attribute87),
738 DECODE(x_adj_rec.attribute88,fnd_api.g_miss_char,
739 DECODE(x_adj_data(i).attribute88,fnd_api.g_miss_char,NULL,
740 x_adj_data(i).attribute88),x_adj_rec.attribute88),
741 DECODE(x_adj_rec.attribute89,fnd_api.g_miss_char,
742 DECODE(x_adj_data(i).attribute89,fnd_api.g_miss_char,NULL,
743 x_adj_data(i).attribute89),x_adj_rec.attribute89),
744 DECODE(x_adj_rec.attribute90,fnd_api.g_miss_char,
745 DECODE(x_adj_data(i).attribute90,fnd_api.g_miss_char,NULL,
746 x_adj_data(i).attribute90),x_adj_rec.attribute90),
747 DECODE(x_adj_rec.attribute91,fnd_api.g_miss_char,
748 DECODE(x_adj_data(i).attribute91,fnd_api.g_miss_char,NULL,
749 x_adj_data(i).attribute91),x_adj_rec.attribute91),
750 DECODE(x_adj_rec.attribute92,fnd_api.g_miss_char,
751 DECODE(x_adj_data(i).attribute92,fnd_api.g_miss_char,NULL,
752 x_adj_data(i).attribute92),x_adj_rec.attribute92),
753 DECODE(x_adj_rec.attribute93,fnd_api.g_miss_char,
754 DECODE(x_adj_data(i).attribute93,fnd_api.g_miss_char,NULL,
755 x_adj_data(i).attribute93),x_adj_rec.attribute93),
756 DECODE(x_adj_rec.attribute94,fnd_api.g_miss_char,
757 DECODE(x_adj_data(i).attribute94,fnd_api.g_miss_char,NULL,
758 x_adj_data(i).attribute94),x_adj_rec.attribute94),
759 DECODE(x_adj_rec.attribute95,fnd_api.g_miss_char,
760 DECODE(x_adj_data(i).attribute95,fnd_api.g_miss_char,NULL,
761 x_adj_data(i).attribute95),x_adj_rec.attribute95),
762 DECODE(x_adj_rec.attribute96,fnd_api.g_miss_char,
763 DECODE(x_adj_data(i).attribute96,fnd_api.g_miss_char,NULL,
764 x_adj_data(i).attribute96),x_adj_rec.attribute96),
765 DECODE(x_adj_rec.attribute97,fnd_api.g_miss_char,
769 DECODE(x_adj_data(i).attribute98,fnd_api.g_miss_char,NULL,
766 DECODE(x_adj_data(i).attribute97,fnd_api.g_miss_char,NULL,
767 x_adj_data(i).attribute97),x_adj_rec.attribute97),
768 DECODE(x_adj_rec.attribute98,fnd_api.g_miss_char,
770 x_adj_data(i).attribute98),x_adj_rec.attribute98),
771 DECODE(x_adj_rec.attribute99,fnd_api.g_miss_char,
772 DECODE(x_adj_data(i).attribute99,fnd_api.g_miss_char,NULL,
773 x_adj_data(i).attribute99),x_adj_rec.attribute99),
774 DECODE(x_adj_rec.attribute100,fnd_api.g_miss_char,
775 DECODE(x_adj_data(i).attribute100,fnd_api.g_miss_char,NULL,
776 x_adj_data(i).attribute100),x_adj_rec.attribute100),
777 DECODE(x_adj_rec.direct_salesrep_number,fnd_api.g_miss_char,
778 DECODE(x_adj_data(i).direct_salesrep_number,fnd_api.g_miss_char,NULL,
779 x_adj_data(i).direct_salesrep_number),
780 x_adj_rec.direct_salesrep_number),
781 l_comm_lines_api_id,
782 NULL,NULL,NULL,
783 DECODE(x_adj_data(i).rollup_date,fnd_api.g_miss_date,NULL,
784 x_adj_data(i).rollup_date),
785 NULL,
786 DECODE(x_adj_data(i).source_doc_type,fnd_api.g_miss_char,NULL,
787 x_adj_data(i).source_doc_type),
788 DECODE(x_adj_data(i).orig_currency_code,fnd_api.g_miss_char,NULL,
789 x_adj_data(i).orig_currency_code),
790 DECODE(x_adj_data(i).exchange_rate,fnd_api.g_miss_num,NULL,
791 x_adj_data(i).exchange_rate),
792 DECODE(x_adj_data(i).transaction_amount_orig,fnd_api.g_miss_num,NULL,
793 x_adj_data(i).transaction_amount_orig),
794 DECODE(x_adj_data(i).trx_id,fnd_api.g_miss_num,NULL,
795 x_adj_data(i).trx_id),
796 DECODE(x_adj_data(i).trx_line_id,fnd_api.g_miss_num,NULL,
797 x_adj_data(i).trx_line_id),
798 DECODE(x_adj_data(i).trx_sales_line_id,fnd_api.g_miss_num,NULL,
799 x_adj_data(i).trx_sales_line_id),
800 DECODE(x_adj_data(i).quantity,fnd_api.g_miss_num,NULL,
801 x_adj_data(i).quantity),
802 DECODE(x_adj_data(i).source_trx_number,fnd_api.g_miss_char,NULL,
803 x_adj_data(i).source_trx_number),
804 DECODE(x_adj_data(i).discount_percentage,fnd_api.g_miss_num,NULL,
805 x_adj_data(i).discount_percentage),
806 DECODE(x_adj_data(i).margin_percentage,fnd_api.g_miss_num,NULL,
807 x_adj_data(i).margin_percentage),
808 NULL,NULL,
809 DECODE(x_adj_data(i).forecast_id,fnd_api.g_miss_num,NULL,
810 x_adj_data(i).forecast_id),
811 DECODE(x_adj_data(i).upside_quantity,fnd_api.g_miss_num,NULL,
812 x_adj_data(i).upside_quantity),
813 DECODE(x_adj_data(i).upside_amount,fnd_api.g_miss_num,NULL,
814 x_adj_data(i).upside_amount),
815 DECODE(x_adj_data(i).uom_code,fnd_api.g_miss_char,NULL,
816 x_adj_data(i).uom_code),
817 -- Bug fix 5349170
818 DECODE(x_adj_data(i).source_trx_id,fnd_api.g_miss_num,NULL,
819 x_adj_data(i).source_trx_id),
820 DECODE(x_adj_data(i).source_trx_line_id,fnd_api.g_miss_num,NULL,
821 x_adj_data(i).source_trx_line_id),
822 DECODE(x_adj_data(i).source_trx_sales_line_id,fnd_api.g_miss_num,NULL,
823 x_adj_data(i).source_trx_sales_line_id),
824 -- Bug fix 5349170
825 NULL,
826 DECODE(x_adj_rec.customer_id,fnd_api.g_miss_num,
827 DECODE(x_adj_data(i).customer_id,fnd_api.g_miss_num,NULL,
828 x_adj_data(i).customer_id),x_adj_rec.customer_id),
829 DECODE(x_adj_rec.inventory_item_id,fnd_api.g_miss_num,
830 DECODE(x_adj_data(i).inventory_item_id,fnd_api.g_miss_num,NULL,
831 x_adj_data(i).inventory_item_id),x_adj_rec.inventory_item_id),
832 DECODE(x_adj_data(i).order_number,fnd_api.g_miss_num,NULL,
833 x_adj_data(i).order_number),
834 DECODE(x_adj_data(i).order_date,fnd_api.g_miss_date,NULL,
835 x_adj_data(i).order_date),
836 DECODE(x_adj_data(i).invoice_number,fnd_api.g_miss_char,NULL,
837 x_adj_data(i).invoice_number),
838 DECODE(x_adj_data(i).invoice_date,fnd_api.g_miss_date,NULL,
839 x_adj_data(i).invoice_date),
840 DECODE(x_adj_data(i).bill_to_address_id,fnd_api.g_miss_num,NULL,
841 x_adj_data(i).bill_to_address_id),
842 DECODE(x_adj_data(i).ship_to_address_id,fnd_api.g_miss_num,NULL,
843 x_adj_data(i).ship_to_address_id),
844 DECODE(x_adj_data(i).bill_to_contact_id,fnd_api.g_miss_num,NULL,
845 x_adj_data(i).bill_to_contact_id),
846 DECODE(x_adj_data(i).ship_to_contact_id,fnd_api.g_miss_num,NULL,
847 x_adj_data(i).ship_to_contact_id),
848 DECODE(x_adj_data(i).comm_lines_api_id,fnd_api.g_miss_num,NULL,
849 x_adj_data(i).comm_lines_api_id),
850 l_adjust_date,x_adj_rec.adjusted_by,
851 DECODE(x_adj_data(i).revenue_type,fnd_api.g_miss_char,NULL,
852 x_adj_data(i).revenue_type),
853 NULL,
854 x_adj_rec.adjust_comments,NVL(l_adjust_status,'NEW'),
855 DECODE(x_adj_data(i).line_number,fnd_api.g_miss_num,NULL,
856 x_adj_data(i).line_number),
857 DECODE(x_adj_data(i).reason_code,fnd_api.g_miss_char,NULL,
858 x_adj_data(i).reason_code),
859 DECODE(x_adj_data(i).attribute_category,fnd_api.g_miss_char,NULL,
860 x_adj_data(i).attribute_category),
861 DECODE(x_adj_data(i).type,fnd_api.g_miss_char,NULL,
862 x_adj_data(i).type),
863 DECODE(x_adj_data(i).pre_processed_code,fnd_api.g_miss_char,NULL,
864 x_adj_data(i).pre_processed_code),
865 DECODE(x_adj_data(i).quota_id,fnd_api.g_miss_num,NULL,
866 x_adj_data(i).quota_id),
867 DECODE(x_adj_data(i).srp_plan_assign_id,fnd_api.g_miss_num,NULL,
868 x_adj_data(i).srp_plan_assign_id),
869 DECODE(x_adj_data(i).role_id,fnd_api.g_miss_num,NULL,
873 NULL,NULL,NULL,
870 x_adj_data(i).role_id),
871 DECODE(x_adj_data(i).comp_group_id,fnd_api.g_miss_num,NULL,
872 x_adj_data(i).comp_group_id),
874 DECODE(x_adj_data(i).sales_channel,fnd_api.g_miss_char,NULL,
875 x_adj_data(i).sales_channel),
876 DECODE(x_adj_data(i).split_pct,fnd_api.g_miss_char,NULL,
877 x_adj_data(i).split_pct),
878 DECODE(x_adj_data(i).split_status,fnd_api.g_miss_char,NULL,
879 x_adj_data(i).split_status),
880 DECODE(x_adj_rec.org_id,fnd_api.g_miss_num,
881 DECODE(x_adj_data(i).org_id,fnd_api.g_miss_num,NULL,
882 x_adj_data(i).org_id),
883 x_adj_rec.org_id),
884 DECODE(x_adj_rec.terr_id,fnd_api.g_miss_num,
885 DECODE(x_adj_data(i).terr_id,fnd_api.g_miss_num,NULL,
886 x_adj_data(i).terr_id),
887 x_adj_rec.terr_id),
888 DECODE(x_adj_rec.preserve_credit_override_flag,fnd_api.g_miss_char,
889 DECODE(x_adj_data(i).preserve_credit_override_flag,fnd_api.g_miss_char,'N',
890 NVL(x_adj_data(i).preserve_credit_override_flag,'N')),
891 NVL(x_adj_rec.preserve_credit_override_flag,'N'))
892 INTO l_api_rec.salesrep_id,l_api_rec.processed_date,
893 l_api_rec.processed_period_id,l_api_rec.acctd_transaction_amount,
894 l_api_rec.trx_type,l_api_rec.revenue_class_id,
895 l_api_rec.load_status,
896 l_api_rec.attribute1,l_api_rec.attribute2,
897 l_api_rec.attribute3,l_api_rec.attribute4,
898 l_api_rec.attribute5,l_api_rec.attribute6,
899 l_api_rec.attribute7,l_api_rec.attribute8,
900 l_api_rec.attribute9,l_api_rec.attribute10,
901 l_api_rec.attribute11,l_api_rec.attribute12,
902 l_api_rec.attribute13,l_api_rec.attribute14,
903 l_api_rec.attribute15,l_api_rec.attribute16,
904 l_api_rec.attribute17,l_api_rec.attribute18,
905 l_api_rec.attribute19,l_api_rec.attribute20,
906 l_api_rec.attribute21,l_api_rec.attribute22,
907 l_api_rec.attribute23,l_api_rec.attribute24,
908 l_api_rec.attribute25,l_api_rec.attribute26,
909 l_api_rec.attribute27,l_api_rec.attribute28,
910 l_api_rec.attribute29,l_api_rec.attribute30,
911 l_api_rec.attribute31,l_api_rec.attribute32,
912 l_api_rec.attribute33,l_api_rec.attribute34,
913 l_api_rec.attribute35,l_api_rec.attribute36,
914 l_api_rec.attribute37,l_api_rec.attribute38,
915 l_api_rec.attribute39,l_api_rec.attribute40,
916 l_api_rec.attribute41,l_api_rec.attribute42,
917 l_api_rec.attribute43,l_api_rec.attribute44,
918 l_api_rec.attribute45,l_api_rec.attribute46,
919 l_api_rec.attribute47,l_api_rec.attribute48,
920 l_api_rec.attribute49,l_api_rec.attribute50,
921 l_api_rec.attribute51,l_api_rec.attribute52,
922 l_api_rec.attribute53,l_api_rec.attribute54,
923 l_api_rec.attribute55,l_api_rec.attribute56,
924 l_api_rec.attribute57,l_api_rec.attribute58,
925 l_api_rec.attribute59,l_api_rec.attribute60,
926 l_api_rec.attribute61,l_api_rec.attribute62,
927 l_api_rec.attribute63,l_api_rec.attribute64,
928 l_api_rec.attribute65,l_api_rec.attribute66,
929 l_api_rec.attribute67,l_api_rec.attribute68,
930 l_api_rec.attribute69,l_api_rec.attribute70,
931 l_api_rec.attribute71,l_api_rec.attribute72,
932 l_api_rec.attribute73,l_api_rec.attribute74,
933 l_api_rec.attribute75,l_api_rec.attribute76,
934 l_api_rec.attribute77,l_api_rec.attribute78,
935 l_api_rec.attribute79,l_api_rec.attribute80,
936 l_api_rec.attribute81,l_api_rec.attribute82,
937 l_api_rec.attribute83,l_api_rec.attribute84,
938 l_api_rec.attribute85,l_api_rec.attribute86,
939 l_api_rec.attribute87,l_api_rec.attribute88,
940 l_api_rec.attribute89,l_api_rec.attribute90,
941 l_api_rec.attribute91,l_api_rec.attribute92,
942 l_api_rec.attribute93,l_api_rec.attribute94,
943 l_api_rec.attribute95,l_api_rec.attribute96,
944 l_api_rec.attribute97,l_api_rec.attribute98,
945 l_api_rec.attribute99,l_api_rec.attribute100,
946 l_api_rec.employee_number,l_api_rec.comm_lines_api_id,
947 l_api_rec.conc_batch_id,l_api_rec.process_batch_id,
948 l_api_rec.salesrep_number,l_api_rec.rollup_date,
949 l_api_rec.source_doc_id,l_api_rec.source_doc_type,
950 l_api_rec.transaction_currency_code,
951 l_api_rec.exchange_rate,
952 l_api_rec.transaction_amount,
953 l_api_rec.trx_id,l_api_rec.trx_line_id,
954 l_api_rec.trx_sales_line_id,l_api_rec.quantity,
955 l_api_rec.source_trx_number,
956 l_api_rec.discount_percentage,
957 l_api_rec.margin_percentage,
958 l_api_rec.pre_defined_rc_flag,l_api_rec.rollup_flag,
959 l_api_rec.forecast_id,
960 l_api_rec.upside_quantity,l_api_rec.upside_amount,
961 l_api_rec.uom_code,l_api_rec.source_trx_id,
962 l_api_rec.source_trx_line_id,
963 l_api_rec.source_trx_sales_line_id,
964 l_api_rec.negated_flag,l_api_rec.customer_id,
965 l_api_rec.inventory_item_id,l_api_rec.order_number,
966 l_api_rec.booked_date,l_api_rec.invoice_number,
967 l_api_rec.invoice_date,l_api_rec.bill_to_address_id,
968 l_api_rec.ship_to_address_id,l_api_rec.bill_to_contact_id,
969 l_api_rec.ship_to_contact_id,l_api_rec.adj_comm_lines_api_id,
973 l_api_rec.line_number,l_api_rec.reason_code,
970 l_api_rec.adjust_date,l_api_rec.adjusted_by,
971 l_api_rec.revenue_type,l_api_rec.adjust_rollup_flag,
972 l_api_rec.adjust_comments,l_api_rec.adjust_status,
974 l_api_rec.attribute_category,l_api_rec.type,
975 l_api_rec.pre_processed_code,l_api_rec.quota_id,
976 l_api_rec.srp_plan_assign_id,l_api_rec.role_id,
977 l_api_rec.comp_group_id,l_api_rec.commission_amount,
978 l_api_rec.reversal_flag,l_api_rec.reversal_header_id,
979 l_api_rec.sales_channel,l_api_rec.split_pct,
980 l_api_rec.split_status,
981 l_api_rec.org_id,
982 l_api_rec.terr_id,
983 l_api_rec.preserve_credit_override_flag
984 FROM DUAL;
985
986 --Added for Crediting
987 IF(x_adj_data(i).terr_id IS NOT NULL)
988 THEN
989 l_api_rec.terr_id := -999;
990 l_api_rec.preserve_credit_override_flag := 'Y';
991 END IF;
992
993 l_api_rec.adj_comm_lines_api_id := x_adj_data(i).comm_lines_api_id;
994 cn_comm_lines_api_pkg.insert_row(l_api_rec);
995
996 --
997 IF ((g_track_invoice = 'Y') AND (l_api_rec.trx_type = 'INV')) THEN
998 l_counter := l_counter + 1;
999 --
1000 l_new_data(l_counter).salesrep_id := l_api_rec.salesrep_id;
1001 l_new_data(l_counter).direct_salesrep_number
1002 := l_api_rec.employee_number;
1003 l_new_data(l_counter).invoice_number := l_api_rec.invoice_number;
1004 l_new_data(l_counter).line_number := l_api_rec.line_number;
1005 l_new_data(l_counter).revenue_type := l_api_rec.revenue_type;
1006 l_new_data(l_counter).split_pct := l_api_rec.split_pct;
1007 l_new_data(l_counter).comm_lines_api_id := l_comm_lines_api_id;
1008 --
1009 END IF;
1010 X_proc_comp := 'Y';
1011 END IF;
1012 IF (x_mass_adj_type = 'M') AND
1013 (x_adj_data(i).adjust_status NOT IN('FROZEN','REVERSAL','SCA_PENDING')) --OR
1014 --x_adj_data(i).adjust_status IS null)
1015 AND
1016 x_adj_data(i).load_status NOT IN ('FILTERED') THEN
1017
1018 cn_adjustments_pkg.api_negate_record(
1019 x_adj_data(i).comm_lines_api_id,
1020 x_adj_rec.adjusted_by,
1021 x_adj_rec.adjust_comments,
1022 x_adj_data(i).direct_salesrep_number);
1023
1024 --Added for Crediting
1025 /*CN_GET_TX_DATA_PUB.update_credit_credentials(
1026 x_adj_data(i).comm_lines_api_id,
1027 x_adj_data(i).terr_id,
1028 x_adj_data(i).org_id,
1029 x_adj_rec.adjusted_by
1030 );*/
1031
1032 END IF;
1033 END LOOP;
1034 END IF; -- IF (x_adj_data.COUNT>0)
1035 IF ((g_track_invoice = 'Y') AND (l_new_data.COUNT > 0)) THEN
1036 --
1037 l_existing_data(1).salesrep_id := NULL;
1038 l_existing_data(1).direct_salesrep_number := NULL;
1039 l_existing_data(1).invoice_number := NULL;
1040 l_existing_data(1).line_number := NULL;
1041 l_existing_data(1).revenue_type := NULL;
1042 l_existing_data(1).split_pct := NULL;
1043 l_existing_data(1).comm_lines_api_id := NULL;
1044 --
1045 cn_invoice_changes_pvt.update_invoice_changes(
1046 p_api_version => l_api_version,
1047 p_validation_level => l_validation_level,
1048 p_existing_data => l_existing_data,
1049 p_new_data => l_new_data,
1050 p_exist_data_check => 'N',
1051 p_new_data_check => 'Y',
1052 x_return_status => l_return_status,
1053 x_msg_count => l_msg_count,
1054 x_msg_data => l_msg_data,
1055 x_loading_status => l_loading_status);
1056 --
1057 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1058 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
1059 FND_MESSAGE.Set_Name('CN', 'CN_UPDATE_INV_ERROR');
1060 FND_MSG_PUB.Add;
1061 END IF;
1062 l_loading_status := 'CN_UPDATE_INV_ERROR';
1063 x_proc_comp := 'E';
1064 END IF;
1065 --
1066 END IF;
1067 EXCEPTION
1068 WHEN OTHERS THEN
1069 x_proc_comp := 'E';
1070 END mass_update_values;
1071 --
1072 PROCEDURE deal_split(
1073 x_source_salesrep_id NUMBER,
1074 x_salesrep_id NUMBER,
1075 x_split_percent NUMBER,
1076 x_revenue_type VARCHAR2,
1077 x_invoice_number VARCHAR2,
1078 x_order_number NUMBER,
1079 x_adjusted_by VARCHAR2,
1080 x_adjust_comments VARCHAR2) IS
1081 --
1082 deal_select_cursor NUMBER(15);
1083 sql_stmt VARCHAR2(5000);
1084 count_rows NUMBER;
1085 l_comm_lines_api_id NUMBER;
1086 l_ins_comm_lines_api_id NUMBER;
1087 l_transaction_amount NUMBER;
1088 l_acctd_transaction_amount NUMBER;
1089 l_salesrep_number VARCHAR2(30);
1090 l_revenue_type VARCHAR2(15);
1091 l_revenue VARCHAR2(12) := 'REVENUE';
1092 l_adj_tbl cn_get_tx_data_pub.adj_tbl_type;
1093 -- PL/SQL tables/records
1094 l_newtx_rec cn_get_tx_data_pub.adj_rec_type;
1095 o_newtx_rec cn_get_tx_data_pub.adj_rec_type;
1096 l_api_rec cn_comm_lines_api_pkg.comm_lines_api_rec_type;
1097 -- To mask this non-standard API
1098 x_return_status VARCHAR2(1000);
1099 BEGIN
1100 deal_select_cursor := DBMS_SQL.open_cursor;
1101 sql_stmt := 'SELECT COMM_LINES_API_ID FROM CN_ADJUSTMENTS_V WHERE
1105 sql_stmt := sql_stmt || ' and invoice_number = :X_invoice_number';
1102 direct_salesrep_id = :X_source_salesrep_id AND revenue_type = :l_revenue';
1103 --
1104 IF (x_invoice_number IS NOT NULL) THEN
1106 ELSIF(x_order_number IS NOT NULL) THEN
1107 sql_stmt := sql_stmt || ' and order_number = :X_order_number';
1108 END IF;
1109 --
1110 dbms_sql.parse(deal_select_cursor,sql_stmt,DBMS_SQL.NATIVE);
1111 DBMS_SQL.bind_variable(
1112 deal_select_cursor,'x_source_salesrep_id',x_source_salesrep_id);
1113 DBMS_SQL.bind_variable(
1114 deal_select_cursor,'l_revenue',l_revenue);
1115 --
1116 IF (x_invoice_number IS NOT NULL) THEN
1117 DBMS_SQL.bind_variable(
1118 deal_select_cursor,'x_invoice_number',x_invoice_number);
1119 END IF;
1120 --
1121 IF (x_order_number IS NOT NULL) THEN
1122 DBMS_SQL.bind_variable(
1123 deal_select_cursor,'x_order_number',x_order_number);
1124 END IF;
1125 --
1126 DBMS_SQL.define_column (deal_select_cursor,1,l_comm_lines_api_id);
1127 count_rows := DBMS_SQL.execute (deal_select_cursor);
1128 LOOP
1129 IF (dbms_sql.fetch_rows(deal_select_cursor) > 0) THEN
1130 DBMS_SQL.column_value (deal_select_cursor,1,l_comm_lines_api_id);
1131 --
1132 --cn_adjustments_pkg.get_api_data(l_comm_lines_api_id,l_adj_tbl);
1133 cn_get_tx_data_pub.get_api_data(l_comm_lines_api_id,l_adj_tbl);
1134 --
1135 IF (l_adj_tbl.COUNT > 0) THEN
1136 l_transaction_amount := ((NVL(l_adj_tbl(1).transaction_amount,0) *
1137 x_split_percent)/100);
1138 l_acctd_transaction_amount := ((NVL(l_adj_tbl(1).transaction_amount,0) *
1139 x_split_percent)/100);
1140 --
1141 SELECT employee_number
1142 INTO l_salesrep_number
1143 FROM cn_salesreps
1144 WHERE salesrep_id = x_salesrep_id;
1145 --
1146 SELECT cn_comm_lines_api_s.NEXTVAL
1147 INTO l_ins_comm_lines_api_id
1148 FROM dual;
1149 --
1150 l_newtx_rec.direct_salesrep_id := x_salesrep_id;
1151 l_newtx_rec.transaction_amount := l_transaction_amount;
1152 l_newtx_rec.load_status := 'UNLOADED';
1153 l_newtx_rec.comm_lines_api_id := l_ins_comm_lines_api_id;
1154 l_newtx_rec.transaction_amount_orig := l_acctd_transaction_amount;
1155 l_newtx_rec.adjust_date := SYSDATE;
1156 l_newtx_rec.adjusted_by := x_adjusted_by;
1157 l_newtx_rec.revenue_type := x_revenue_type;
1158 l_newtx_rec.adjust_comments := x_adjust_comments;
1159 l_newtx_rec.adjust_status := 'DEALSPLIT';
1160 l_newtx_rec.adj_comm_lines_api_id := l_adj_tbl(1).comm_lines_api_id;
1161 l_newtx_rec.direct_salesrep_number := l_salesrep_number;
1162 --
1163 cn_invoice_changes_pvt.prepare_api_record(
1164 p_newtx_rec => l_newtx_rec,
1165 p_old_adj_tbl => l_adj_tbl,
1166 x_final_trx_rec => o_newtx_rec,
1167 x_return_status => x_return_status);
1168 -- codeCheck: I will explain later about this conversion.
1169 cn_invoice_changes_pvt.convert_adj_to_api(
1170 p_adj_rec => o_newtx_rec,
1171 x_api_rec => l_api_rec);
1172 --
1173 cn_comm_lines_api_pkg.insert_row(l_api_rec);
1174 --
1175 END IF;
1176 ELSE
1177 EXIT;
1178 END IF;
1179 END LOOP;
1180 DBMS_SQL.close_cursor(deal_select_cursor);
1181 END deal_split;
1182 --
1183 PROCEDURE deal_assign(
1184 x_from_salesrep_id NUMBER,
1185 x_to_salesrep_id NUMBER,
1186 x_invoice_number VARCHAR2,
1187 x_order_number NUMBER,
1188 x_adjusted_by VARCHAR2,
1189 x_adjust_comments VARCHAR2) IS
1190 --
1191 CURSOR api_cur(l_comm_lines_api_id NUMBER) IS
1192 SELECT l.comm_lines_api_id,NVL(l.adjust_status,'NEW') adjust_status
1193 FROM cn_comm_lines_api l
1194 WHERE adj_comm_lines_api_id = l_comm_lines_api_id;
1195 --
1196 CURSOR header_cur(l_comm_lines_api_id NUMBER) IS
1197 SELECT h.comm_lines_api_id,NVL(h.adjust_status,'NEW') adjust_status
1198 FROM cn_commission_headers h
1199 WHERE adj_comm_lines_api_id = l_comm_lines_api_id;
1200 --
1201 assign_select_cursor NUMBER(15);
1202 sql_stmt VARCHAR2(5000);
1203 count_rows NUMBER;
1204 l_comm_lines_api_id NUMBER;
1205 l_ins_comm_lines_api_id NUMBER;
1206 l_transaction_amount NUMBER;
1207 l_acctd_transaction_amount NUMBER;
1208 l_salesrep_number VARCHAR2(30);
1209 l_revenue VARCHAR2(12) := 'REVENUE';
1210 l_load_status VARCHAR2(30);
1211 -- PL/SQL tables/records
1212 l_adj_tbl cn_get_tx_data_pub.adj_tbl_type;
1213 l_newtx_rec cn_get_tx_data_pub.adj_rec_type;
1214 o_newtx_rec cn_get_tx_data_pub.adj_rec_type;
1215 l_api_rec cn_comm_lines_api_pkg.comm_lines_api_rec_type;
1216 -- To mask this non-standard API
1217 x_return_status VARCHAR2(1000);
1218 --
1219 BEGIN
1220 assign_select_cursor := DBMS_SQL.open_cursor;
1221 sql_stmt := 'SELECT comm_lines_api_id,load_status FROM cn_adjustments_v WHERE';
1222 sql_stmt := sql_stmt || ' direct_salesrep_id = :x_from_salesrep_id AND revenue_type = :l_revenue';
1223 --
1224 IF (x_invoice_number IS NOT NULL) THEN
1225 sql_stmt := sql_stmt || ' AND invoice_number = :x_invoice_number';
1226 ELSIF(x_order_number IS NOT NULL) THEN
1227 sql_stmt := sql_stmt || ' AND order_number = :x_order_number';
1228 END IF;
1229 --
1230 dbms_sql.parse(assign_select_cursor,sql_stmt,DBMS_SQL.NATIVE);
1231 DBMS_SQL.bind_variable(
1232 assign_select_cursor,'x_from_salesrep_id',x_from_salesrep_id);
1233 DBMS_SQL.bind_variable(
1234 assign_select_cursor,'l_revenue',l_revenue);
1235 --
1239 END IF;
1236 IF (x_invoice_number IS NOT NULL) THEN
1237 DBMS_SQL.bind_variable(
1238 assign_select_cursor,'x_invoice_number',x_invoice_number);
1240 --
1241 IF (x_order_number IS NOT NULL) THEN
1242 DBMS_SQL.bind_variable(
1243 assign_select_cursor,'x_order_number',x_order_number);
1244 END IF;
1245 --
1246 DBMS_SQL.define_column (assign_select_cursor,1,l_comm_lines_api_id);
1247 DBMS_SQL.define_column (assign_select_cursor,2,l_load_status,30);
1248 count_rows := DBMS_SQL.execute (assign_select_cursor);
1249 LOOP
1250 IF (dbms_sql.fetch_rows(assign_select_cursor) > 0) THEN
1251 DBMS_SQL.column_value (assign_select_cursor,1,l_comm_lines_api_id);
1252 DBMS_SQL.column_value (assign_select_cursor,2,l_load_status);
1253 IF (l_load_status = 'LOADED') THEN
1254 FOR rec IN header_cur(l_comm_lines_api_id)
1255 LOOP
1256 IF(nvl(rec.adjust_status,'X') <> 'FROZEN') THEN
1257 cn_adjustments_pkg.api_negate_record(
1258 rec.comm_lines_api_id,
1259 x_adjusted_by,
1260 x_adjust_comments);
1261 END IF;
1262 END LOOP;
1263 ELSIF (l_load_status = 'UNLOADED') THEN
1264 FOR rec IN api_cur(l_comm_lines_api_id)
1265 LOOP
1266 IF(nvl(rec.adjust_status,'X') <> 'FROZEN') THEN
1267 cn_adjustments_pkg.api_negate_record(
1268 rec.comm_lines_api_id,
1269 x_adjusted_by,
1270 x_adjust_comments);
1271 END IF;
1272 END LOOP;
1273 END IF;
1274 --
1275 --cn_adjustments_pkg.get_api_data(l_comm_lines_api_id,l_adj_tbl);
1276 cn_get_tx_data_pub.get_api_data(l_comm_lines_api_id,l_adj_tbl);
1277 --
1278 IF (l_adj_tbl.COUNT > 0) THEN
1279 /* codeCheck: I need to revisit this code */
1280 l_transaction_amount := NVL(l_adj_tbl(1).transaction_amount,0);
1281 l_acctd_transaction_amount
1282 := NVL(l_adj_tbl(1).transaction_amount,0);
1283 --
1284 /*
1285 IF(nvl(l_adj_tbl(1).adjust_status,'X') <> 'FROZEN') THEN
1286 cn_adjustments_pkg.api_negate_record(
1287 l_adj_tbl(1).comm_lines_api_id,
1288 l_adjusted_by,
1289 l_adjust_comments);
1290 END IF; */
1291 --
1292 SELECT employee_number
1293 INTO l_salesrep_number
1294 FROM cn_salesreps
1295 WHERE salesrep_id = x_to_salesrep_id;
1296 --
1297 SELECT cn_comm_lines_api_s.NEXTVAL
1298 INTO l_ins_comm_lines_api_id
1299 FROM dual;
1300 --
1301 l_newtx_rec.direct_salesrep_id := x_to_salesrep_id;
1302 l_newtx_rec.transaction_amount := l_transaction_amount;
1303 l_newtx_rec.load_status := 'UNLOADED';
1304 l_newtx_rec.comm_lines_api_id := l_ins_comm_lines_api_id;
1305 l_newtx_rec.transaction_amount_orig := l_acctd_transaction_amount;
1306 l_newtx_rec.adjust_date := SYSDATE;
1307 l_newtx_rec.adjusted_by := x_adjusted_by;
1308 l_newtx_rec.revenue_type := 'REVENUE';
1309 l_newtx_rec.adjust_comments := x_adjust_comments;
1310 l_newtx_rec.adjust_status := 'DEALASGN';
1311 l_newtx_rec.adj_comm_lines_api_id := l_adj_tbl(1).comm_lines_api_id;
1312 l_newtx_rec.direct_salesrep_number := l_salesrep_number;
1313 --
1314 cn_invoice_changes_pvt.prepare_api_record(
1315 p_newtx_rec => l_newtx_rec,
1316 p_old_adj_tbl => l_adj_tbl,
1317 x_final_trx_rec => o_newtx_rec,
1318 x_return_status => x_return_status);
1319 -- codeCheck: I will explain later about this conversion.
1320 cn_invoice_changes_pvt.convert_adj_to_api(
1321 p_adj_rec => o_newtx_rec,
1322 x_api_rec => l_api_rec);
1323 --
1324 cn_comm_lines_api_pkg.insert_row(l_api_rec);
1325 --
1326 END IF;
1327 ELSE
1328 exit;
1329 END IF;
1330 END LOOP;
1331 DBMS_SQL.close_cursor(assign_select_cursor);
1332 END deal_assign;
1333 --
1334 PROCEDURE get_cust_info(
1335 p_comm_lines_api_id IN NUMBER,
1336 p_load_status IN VARCHAR2,
1337 x_cust_info_rec OUT NOCOPY cust_info_rec) IS
1338 BEGIN
1339 -- First check in header table.
1340 BEGIN
1341 SELECT CCH.customer_id,RAC.customer_number,RAC.customer_name,
1342 CCH.bill_to_address_id,RABA.address1,RABA.address2,
1343 RABA.address3,RABA.address4,RABA.city,RABA.postal_code,
1344 RABA.state,CCH.ship_to_address_id,RASA.address1,
1345 RASA.address2,RASA.address3,RASA.address4,RASA.city,
1346 RASA.postal_code,RASA.state,CCH.bill_to_contact_id,
1347 RABC.person_last_name||', ' ||RABC.person_first_name,
1348 CCH.ship_to_contact_id,
1349 RASC.person_last_name||', '||RASC.person_first_name
1350
1351 INTO x_cust_info_rec.customer_id,
1352 x_cust_info_rec.customer_number,
1353 x_cust_info_rec.customer_name,
1354 x_cust_info_rec.bill_to_address_id,
1355 x_cust_info_rec.bill_to_address1,
1356 x_cust_info_rec.bill_to_address2,
1357 x_cust_info_rec.bill_to_address3,
1358 x_cust_info_rec.bill_to_address4,
1359 x_cust_info_rec.bill_to_city,
1360 x_cust_info_rec.bill_to_postal_code,
1361 x_cust_info_rec.bill_to_state,
1362 x_cust_info_rec.ship_to_address_id,
1363 x_cust_info_rec.ship_to_address1,
1364 x_cust_info_rec.ship_to_address2,
1365 x_cust_info_rec.ship_to_address3,
1366 x_cust_info_rec.ship_to_address4,
1367 x_cust_info_rec.ship_to_city,
1368 x_cust_info_rec.ship_to_postal_code,
1369 x_cust_info_rec.ship_to_state,
1370 x_cust_info_rec.bill_to_contact_id,
1371 x_cust_info_rec.bill_to_contact,
1375 FROM cn_commission_headers cch,
1372 x_cust_info_rec.ship_to_contact_id,
1373 x_cust_info_rec.ship_to_contact
1374
1376 (SELECT CUST_ACCT.CUST_ACCOUNT_ID CUSTOMER_ID, substrb(PARTY.PARTY_NAME,1,50) CUSTOMER_NAME,
1377 CUST_ACCT.ACCOUNT_NUMBER CUSTOMER_NUMBER
1378 FROM HZ_PARTIES PARTY, HZ_CUST_ACCOUNTS CUST_ACCT
1379 WHERE CUST_ACCT.PARTY_ID = PARTY.PARTY_ID) rac,
1380 (SELECT ACCT_SITE.CUST_ACCT_SITE_ID /* ADDRESS_ID */ ,
1381 LOC.ADDRESS1 , LOC.ADDRESS2 , LOC.ADDRESS3 , LOC.ADDRESS4 , LOC.CITY , LOC.POSTAL_CODE ,
1382 LOC.STATE FROM HZ_PARTY_SITES PARTY_SITE, HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
1383 HZ_LOCATIONS LOC, HZ_CUST_ACCT_SITES_ALL ACCT_SITE
1384 WHERE ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
1385 AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
1386 AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
1387 AND NVL(ACCT_SITE.ORG_ID, -99) = NVL(LOC_ASSIGN.ORG_ID, -99)) raba,
1388 (SELECT ACCT_SITE.CUST_ACCT_SITE_ID /* ADDRESS_ID */ ,
1389 LOC.ADDRESS1 , LOC.ADDRESS2 , LOC.ADDRESS3 , LOC.ADDRESS4 , LOC.CITY , LOC.POSTAL_CODE ,
1390 LOC.STATE FROM HZ_PARTY_SITES PARTY_SITE, HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
1391 HZ_LOCATIONS LOC, HZ_CUST_ACCT_SITES_ALL ACCT_SITE
1392 WHERE ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
1393 AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
1394 AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
1395 AND NVL(ACCT_SITE.ORG_ID, -99) = NVL(LOC_ASSIGN.ORG_ID, -99)) rasa,
1396 (SELECT ACCT_ROLE.CUST_ACCOUNT_ROLE_ID /* CONTACT_ID */ ,
1397 substrb(PARTY.PERSON_LAST_NAME,1,50) PERSON_LAST_NAME,
1398 substrb(PARTY.PERSON_FIRST_NAME,1,40) PERSON_FIRST_NAME
1399 FROM HZ_CUST_ACCOUNT_ROLES ACCT_ROLE, HZ_PARTIES PARTY, HZ_RELATIONSHIPS REL,
1400 HZ_ORG_CONTACTS ORG_CONT, HZ_PARTIES REL_PARTY, HZ_CUST_ACCOUNTS ROLE_ACCT
1401 WHERE ACCT_ROLE.PARTY_ID = REL.PARTY_ID
1402 AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
1403 AND ORG_CONT.PARTY_RELATIONSHIP_ID = REL.RELATIONSHIP_ID
1404 AND REL.SUBJECT_ID = PARTY.PARTY_ID
1405 AND REL.PARTY_ID = REL_PARTY.PARTY_ID
1406 AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
1407 AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
1408 AND ACCT_ROLE.CUST_ACCOUNT_ID = ROLE_ACCT.CUST_ACCOUNT_ID
1409 AND ROLE_ACCT.PARTY_ID = REL.OBJECT_ID /* AND REL.DIRECTIONAL_FLAG = 'F' */
1410 ) rabc,
1411 (SELECT ACCT_ROLE.CUST_ACCOUNT_ROLE_ID /* CONTACT_ID */ ,
1412 substrb(PARTY.PERSON_LAST_NAME,1,50) PERSON_LAST_NAME,
1413 substrb(PARTY.PERSON_FIRST_NAME,1,40) PERSON_FIRST_NAME
1414 FROM HZ_CUST_ACCOUNT_ROLES ACCT_ROLE, HZ_PARTIES PARTY, HZ_RELATIONSHIPS REL,
1415 HZ_ORG_CONTACTS ORG_CONT, HZ_PARTIES REL_PARTY, HZ_CUST_ACCOUNTS ROLE_ACCT
1416 WHERE ACCT_ROLE.PARTY_ID = REL.PARTY_ID
1417 AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
1418 AND ORG_CONT.PARTY_RELATIONSHIP_ID = REL.RELATIONSHIP_ID
1419 AND REL.SUBJECT_ID = PARTY.PARTY_ID
1420 AND REL.PARTY_ID = REL_PARTY.PARTY_ID
1421 AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
1422 AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
1423 AND ACCT_ROLE.CUST_ACCOUNT_ID = ROLE_ACCT.CUST_ACCOUNT_ID
1424 AND ROLE_ACCT.PARTY_ID = REL.OBJECT_ID /* AND REL.DIRECTIONAL_FLAG = 'F' */
1425 ) rasc
1426 WHERE CCH.comm_lines_api_id = p_comm_lines_api_id
1427 AND CCH.customer_id = RAC.customer_id (+)
1428 AND CCH.bill_to_address_id = RABA.CUST_ACCT_SITE_ID (+)
1429 AND CCH.ship_to_address_id = RASA.CUST_ACCT_SITE_ID (+)
1430 AND CCH.bill_to_contact_id = RABC.CUST_ACCOUNT_ROLE_ID (+)
1431 AND CCH.ship_to_contact_id = RASC.CUST_ACCOUNT_ROLE_ID (+)
1432 AND ROWNUM < 2;
1433 EXCEPTION
1434 WHEN OTHERS THEN
1435 BEGIN
1436 SELECT CCLA.customer_id,RAC.customer_number,RAC.customer_name,
1437 CCLA.bill_to_address_id,RABA.address1,RABA.address2,
1438 RABA.address3,RABA.address4,RABA.city,RABA.postal_code,
1439 RABA.state,CCLA.ship_to_address_id,RASA.address1,
1440 RASA.address2,RASA.address3,RASA.address4,RASA.city,
1441 RASA.postal_code,RASA.state,CCLA.bill_to_contact_id,
1442 RABC.person_last_name||', ' ||RABC.person_first_name,
1443 CCLA.ship_to_contact_id,
1444 RASC.person_last_name||', '||RASC.person_first_name
1445
1446 INTO x_cust_info_rec.customer_id,
1447 x_cust_info_rec.customer_number,
1448 x_cust_info_rec.customer_name,
1449 x_cust_info_rec.bill_to_address_id,
1450 x_cust_info_rec.bill_to_address1,
1451 x_cust_info_rec.bill_to_address2,
1452 x_cust_info_rec.bill_to_address3,
1453 x_cust_info_rec.bill_to_address4,
1454 x_cust_info_rec.bill_to_city,
1455 x_cust_info_rec.bill_to_postal_code,
1456 x_cust_info_rec.bill_to_state,
1457 x_cust_info_rec.ship_to_address_id,
1458 x_cust_info_rec.ship_to_address1,
1459 x_cust_info_rec.ship_to_address2,
1460 x_cust_info_rec.ship_to_address3,
1461 x_cust_info_rec.ship_to_address4,
1462 x_cust_info_rec.ship_to_city,
1463 x_cust_info_rec.ship_to_postal_code,
1464 x_cust_info_rec.ship_to_state,
1465 x_cust_info_rec.bill_to_contact_id,
1466 x_cust_info_rec.bill_to_contact,
1467 x_cust_info_rec.ship_to_contact_id,
1468 x_cust_info_rec.ship_to_contact
1469
1470 FROM cn_comm_lines_api ccla,
1474 WHERE CUST_ACCT.PARTY_ID = PARTY.PARTY_ID) rac,
1471 (SELECT CUST_ACCT.CUST_ACCOUNT_ID CUSTOMER_ID, substrb(PARTY.PARTY_NAME,1,50) CUSTOMER_NAME,
1472 CUST_ACCT.ACCOUNT_NUMBER CUSTOMER_NUMBER
1473 FROM HZ_PARTIES PARTY, HZ_CUST_ACCOUNTS CUST_ACCT
1475 (SELECT ACCT_SITE.CUST_ACCT_SITE_ID /* ADDRESS_ID */ ,
1476 LOC.ADDRESS1 , LOC.ADDRESS2 , LOC.ADDRESS3 , LOC.ADDRESS4 , LOC.CITY , LOC.POSTAL_CODE ,
1477 LOC.STATE FROM HZ_PARTY_SITES PARTY_SITE, HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
1478 HZ_LOCATIONS LOC, HZ_CUST_ACCT_SITES_ALL ACCT_SITE
1479 WHERE ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
1480 AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
1481 AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
1482 AND NVL(ACCT_SITE.ORG_ID, -99) = NVL(LOC_ASSIGN.ORG_ID, -99)) raba,
1483 (SELECT ACCT_SITE.CUST_ACCT_SITE_ID /* ADDRESS_ID */ ,
1484 LOC.ADDRESS1 , LOC.ADDRESS2 , LOC.ADDRESS3 , LOC.ADDRESS4 , LOC.CITY , LOC.POSTAL_CODE ,
1485 LOC.STATE FROM HZ_PARTY_SITES PARTY_SITE, HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
1486 HZ_LOCATIONS LOC, HZ_CUST_ACCT_SITES_ALL ACCT_SITE
1487 WHERE ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
1488 AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
1489 AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
1490 AND NVL(ACCT_SITE.ORG_ID, -99) = NVL(LOC_ASSIGN.ORG_ID, -99)) rasa,
1491 (SELECT ACCT_ROLE.CUST_ACCOUNT_ROLE_ID /* CONTACT_ID */ ,
1492 substrb(PARTY.PERSON_LAST_NAME,1,50) PERSON_LAST_NAME,
1493 substrb(PARTY.PERSON_FIRST_NAME,1,40) PERSON_FIRST_NAME
1494 FROM HZ_CUST_ACCOUNT_ROLES ACCT_ROLE, HZ_PARTIES PARTY, HZ_RELATIONSHIPS REL,
1495 HZ_ORG_CONTACTS ORG_CONT, HZ_PARTIES REL_PARTY, HZ_CUST_ACCOUNTS ROLE_ACCT
1496 WHERE ACCT_ROLE.PARTY_ID = REL.PARTY_ID
1497 AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
1498 AND ORG_CONT.PARTY_RELATIONSHIP_ID = REL.RELATIONSHIP_ID
1499 AND REL.SUBJECT_ID = PARTY.PARTY_ID
1500 AND REL.PARTY_ID = REL_PARTY.PARTY_ID
1501 AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
1502 AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
1503 AND ACCT_ROLE.CUST_ACCOUNT_ID = ROLE_ACCT.CUST_ACCOUNT_ID
1504 AND ROLE_ACCT.PARTY_ID = REL.OBJECT_ID /* AND REL.DIRECTIONAL_FLAG = 'F' */
1505 ) rabc,
1506 (SELECT ACCT_ROLE.CUST_ACCOUNT_ROLE_ID,
1507 substrb(PARTY.PERSON_LAST_NAME,1,50) PERSON_LAST_NAME,
1508 substrb(PARTY.PERSON_FIRST_NAME,1,40) PERSON_FIRST_NAME
1509 FROM HZ_CUST_ACCOUNT_ROLES ACCT_ROLE, HZ_PARTIES PARTY, HZ_RELATIONSHIPS REL,
1510 HZ_ORG_CONTACTS ORG_CONT, HZ_PARTIES REL_PARTY, HZ_CUST_ACCOUNTS ROLE_ACCT
1511 WHERE ACCT_ROLE.PARTY_ID = REL.PARTY_ID
1512 AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
1513 AND ORG_CONT.PARTY_RELATIONSHIP_ID = REL.RELATIONSHIP_ID
1514 AND REL.SUBJECT_ID = PARTY.PARTY_ID
1515 AND REL.PARTY_ID = REL_PARTY.PARTY_ID
1516 AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
1517 AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
1518 AND ACCT_ROLE.CUST_ACCOUNT_ID = ROLE_ACCT.CUST_ACCOUNT_ID
1519 AND ROLE_ACCT.PARTY_ID = REL.OBJECT_ID /* AND REL.DIRECTIONAL_FLAG = 'F' */
1520 ) rasc
1521 WHERE ccla.comm_lines_api_id = p_comm_lines_api_id
1522 AND ccla.customer_id = RAC.customer_id (+)
1523 AND ccla.bill_to_address_id = RABA.CUST_ACCT_SITE_ID (+)
1524 AND ccla.ship_to_address_id = RASA.CUST_ACCT_SITE_ID (+)
1525 AND ccla.bill_to_contact_id = RABC.CUST_ACCOUNT_ROLE_ID (+)
1526 AND ccla.ship_to_contact_id = RASC.CUST_ACCOUNT_ROLE_ID (+)
1527 AND ROWNUM < 2;
1528 EXCEPTION
1529 WHEN OTHERS THEN
1530 NULL;
1531 END;
1532 END;
1533 EXCEPTION
1534 WHEN OTHERS THEN
1535 NULL;
1536 END;
1537
1538 PROCEDURE update_credit_credentials (
1539 p_comm_lines_api_id IN NUMBER,
1540 p_terr_id IN NUMBER,
1541 p_org_id IN NUMBER,
1542 p_adjusted_by IN VARCHAR2
1543 )
1544 IS
1545 /* Added to fix Crediting bug */
1546 CURSOR l_csr_credited_trx(l_nbr_comm_lines_api_id NUMBER, l_nbr_org_id NUMBER) IS
1547 SELECT COMM_LINES_API_ID, ORG_ID
1548 FROM CN_COMM_LINES_API
1549 WHERE ORG_ID = l_nbr_org_id
1550 AND TERR_ID IS NOT NULL
1551 AND (adjust_status NOT IN ('FROZEN','REVERSAL','SCA PENDING'))-- OR
1552 -- adjust_status IS NULL)
1553 START WITH COMM_LINES_API_ID = l_nbr_comm_lines_api_id
1554 CONNECT BY PRIOR COMM_LINES_API_ID = ADJ_COMM_LINES_API_ID;
1555
1556
1557 CURSOR l_csr_parent_trx(l_nbr_comm_lines_api_id NUMBER, l_nbr_org_id NUMBER) IS
1558 SELECT COMM_LINES_API_ID, ORG_ID FROM CN_COMM_LINES_API
1559 WHERE ORG_ID = l_nbr_org_id
1560 AND TERR_ID IS NULL
1561 AND (adjust_status NOT IN ('FROZEN','REVERSAL','SCA PENDING'))-- OR
1562 -- adjust_status IS NULL)
1563 START WITH COMM_LINES_API_ID = l_nbr_comm_lines_api_id
1564 CONNECT BY PRIOR ADJ_COMM_LINES_API_ID = COMM_LINES_API_ID;
1565
1566 l_adj_comm_lines_api_id NUMBER;
1567 BEGIN
1568 /* Code added for Crediting, to obsolete any child records that have gone through
1569 the crediting cycle */
1570 IF p_terr_id IS NULL
1571 THEN
1572 FOR each_trx IN l_csr_credited_trx(p_comm_lines_api_id, p_org_id)
1573 LOOP
1574
1575 cn_adjustments_pkg.api_negate_record(
1576 x_comm_lines_api_id => each_trx.comm_lines_api_id,
1577 x_adjusted_by => p_adjusted_by,
1578 x_adjust_comments => 'Parent transaction modified');
1579 END LOOP;
1580 ELSE IF p_terr_id IS NOT NULL
1584 UPDATE CN_COMM_LINES_API
1581 THEN
1582 FOR parent_trx IN l_csr_parent_trx(p_comm_lines_api_id, p_org_id)
1583 LOOP
1585 SET PRESERVE_CREDIT_OVERRIDE_FLAG = 'Y',
1586 ADJUSTED_BY = p_adjusted_by
1587 WHERE COMM_LINES_API_ID = parent_trx.comm_lines_api_id
1588 AND ORG_ID = parent_trx.org_id;
1589 END LOOP;
1590 END IF;
1591 END IF;
1592
1593 --
1594 END update_credit_credentials;
1595
1596 --
1597 END cn_adjustments_pkg;
1598
1599
1600