[Home] [Help]
PACKAGE BODY: APPS.CN_INVOICE_CHANGES_PVT
Source
1 PACKAGE BODY CN_INVOICE_CHANGES_PVT AS
2 --$Header: cnvinvb.pls 120.7.12000000.2 2007/08/07 15:05:20 apink ship $
3 -- +======================================================================+
4 -- | Copyright (c) 1994 Oracle Corporation |
5 -- | Redwood Shores, California, USA |
6 -- | All rights reserved. |
7 -- +======================================================================+
8 --
9 -- Package Name
10 -- cn_invoice_changes_pvt
11 -- Purpose
12 -- Package Body for Procedures related to cn_invoice_changes table changes.
13 -- History
14 -- 08/07/01 Rao.Chenna Created
15 --
16 -- Nov 22, 2005 vensrini Bug Fix 4202682. Changed cursor c1 in
17 -- capture_deal_invoice proc to exclude transactions
18 -- with load status FILTERED
19 --
20 -- Added org_id join to cursor c2 in capture_deal_invoice
21 -- proc
22 --
23 -- Jun 26, 2006 vensrini Bug fix 5220393
24 --
25 -- Jul 5, 2006 vensrini Bug fix 5349170
26
27
28 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_INVOICE_CHANGES_PVT';
29 G_FILE_NAME CONSTANT VARCHAR2(12) := 'cnvinvb.pls';
30 --
31 FUNCTION g_track_invoice
32 RETURN VARCHAR2 IS
33 l_track_invoice VARCHAR2(1) := 'N';
34 BEGIN
35 l_track_invoice := NVL(fnd_profile.value('CN_TRACK_INVOICE'),'N');
36 RETURN l_track_invoice;
37 EXCEPTION
38 WHEN OTHERS THEN
39 RETURN l_track_invoice;
40 END;
41 --
42 FUNCTION get_adjusted_by
43 RETURN VARCHAR2 IS
44 l_adjusted_by VARCHAR2(100) := '0';
45 BEGIN
46 SELECT user_name
47 INTO l_adjusted_by
48 FROM fnd_user
49 WHERE user_id = fnd_profile.value('USER_ID');
50 RETURN l_adjusted_by;
51 EXCEPTION
52 WHEN OTHERS THEN
53 RETURN l_adjusted_by;
54 END;
55 --
56 PROCEDURE convert_adj_to_api(
57 p_adj_rec IN cn_get_tx_data_pub.adj_rec_type,
58 x_api_rec OUT NOCOPY cn_comm_lines_api_pkg.comm_lines_api_rec_type) IS
59 BEGIN
60 x_api_rec.salesrep_id := p_adj_rec.direct_salesrep_id;
61 x_api_rec.processed_date := p_adj_rec.processed_date;
62 x_api_rec.processed_period_id := p_adj_rec.processed_period_id;
63 x_api_rec.transaction_amount := p_adj_rec.transaction_amount_orig;
64 x_api_rec.trx_type := p_adj_rec.trx_type;
65 x_api_rec.revenue_class_id := p_adj_rec.revenue_class_id;
66 x_api_rec.load_status := p_adj_rec.load_status;
67 x_api_rec.attribute_category := p_adj_rec.attribute_category;
68 x_api_rec.attribute1 := p_adj_rec.attribute1;
69 x_api_rec.attribute2 := p_adj_rec.attribute2;
70 x_api_rec.attribute3 := p_adj_rec.attribute3;
71 x_api_rec.attribute4 := p_adj_rec.attribute4;
72 x_api_rec.attribute5 := p_adj_rec.attribute5;
73 x_api_rec.attribute6 := p_adj_rec.attribute6;
74 x_api_rec.attribute7 := p_adj_rec.attribute7;
75 x_api_rec.attribute8 := p_adj_rec.attribute8;
76 x_api_rec.attribute9 := p_adj_rec.attribute9;
77 x_api_rec.attribute10 := p_adj_rec.attribute10;
78 x_api_rec.attribute11 := p_adj_rec.attribute11;
79 x_api_rec.attribute12 := p_adj_rec.attribute12;
80 x_api_rec.attribute13 := p_adj_rec.attribute13;
81 x_api_rec.attribute14 := p_adj_rec.attribute14;
82 x_api_rec.attribute15 := p_adj_rec.attribute15;
83 x_api_rec.attribute16 := p_adj_rec.attribute16;
84 x_api_rec.attribute17 := p_adj_rec.attribute17;
85 x_api_rec.attribute18 := p_adj_rec.attribute18;
86 x_api_rec.attribute19 := p_adj_rec.attribute19;
87 x_api_rec.attribute20 := p_adj_rec.attribute20;
88 x_api_rec.attribute21 := p_adj_rec.attribute21;
89 x_api_rec.attribute22 := p_adj_rec.attribute22;
90 x_api_rec.attribute23 := p_adj_rec.attribute23;
91 x_api_rec.attribute24 := p_adj_rec.attribute24;
92 x_api_rec.attribute25 := p_adj_rec.attribute25;
93 x_api_rec.attribute26 := p_adj_rec.attribute26;
94 x_api_rec.attribute27 := p_adj_rec.attribute27;
95 x_api_rec.attribute28 := p_adj_rec.attribute28;
96 x_api_rec.attribute29 := p_adj_rec.attribute29;
97 x_api_rec.attribute30 := p_adj_rec.attribute30;
98 x_api_rec.attribute31 := p_adj_rec.attribute31;
99 x_api_rec.attribute32 := p_adj_rec.attribute32;
100 x_api_rec.attribute33 := p_adj_rec.attribute33;
101 x_api_rec.attribute34 := p_adj_rec.attribute34;
102 x_api_rec.attribute35 := p_adj_rec.attribute35;
103 x_api_rec.attribute36 := p_adj_rec.attribute36;
104 x_api_rec.attribute37 := p_adj_rec.attribute37;
105 x_api_rec.attribute38 := p_adj_rec.attribute38;
106 x_api_rec.attribute39 := p_adj_rec.attribute39;
107 x_api_rec.attribute40 := p_adj_rec.attribute40;
108 x_api_rec.attribute41 := p_adj_rec.attribute41;
109 x_api_rec.attribute42 := p_adj_rec.attribute42;
110 x_api_rec.attribute43 := p_adj_rec.attribute43;
111 x_api_rec.attribute44 := p_adj_rec.attribute44;
112 x_api_rec.attribute45 := p_adj_rec.attribute45;
113 x_api_rec.attribute46 := p_adj_rec.attribute46;
114 x_api_rec.attribute47 := p_adj_rec.attribute47;
115 x_api_rec.attribute48 := p_adj_rec.attribute48;
116 x_api_rec.attribute49 := p_adj_rec.attribute49;
117 x_api_rec.attribute50 := p_adj_rec.attribute50;
118 x_api_rec.attribute51 := p_adj_rec.attribute51;
119 x_api_rec.attribute52 := p_adj_rec.attribute52;
120 x_api_rec.attribute53 := p_adj_rec.attribute53;
121 x_api_rec.attribute54 := p_adj_rec.attribute54;
122 x_api_rec.attribute55 := p_adj_rec.attribute55;
123 x_api_rec.attribute56 := p_adj_rec.attribute56;
124 x_api_rec.attribute57 := p_adj_rec.attribute57;
125 x_api_rec.attribute58 := p_adj_rec.attribute58;
126 x_api_rec.attribute59 := p_adj_rec.attribute59;
127 x_api_rec.attribute60 := p_adj_rec.attribute60;
128 x_api_rec.attribute61 := p_adj_rec.attribute61;
129 x_api_rec.attribute62 := p_adj_rec.attribute62;
130 x_api_rec.attribute63 := p_adj_rec.attribute63;
131 x_api_rec.attribute64 := p_adj_rec.attribute64;
132 x_api_rec.attribute65 := p_adj_rec.attribute65;
133 x_api_rec.attribute66 := p_adj_rec.attribute66;
134 x_api_rec.attribute67 := p_adj_rec.attribute67;
135 x_api_rec.attribute68 := p_adj_rec.attribute68;
136 x_api_rec.attribute69 := p_adj_rec.attribute69;
137 x_api_rec.attribute70 := p_adj_rec.attribute70;
138 x_api_rec.attribute71 := p_adj_rec.attribute71;
139 x_api_rec.attribute72 := p_adj_rec.attribute72;
140 x_api_rec.attribute73 := p_adj_rec.attribute73;
141 x_api_rec.attribute74 := p_adj_rec.attribute74;
142 x_api_rec.attribute75 := p_adj_rec.attribute75;
143 x_api_rec.attribute76 := p_adj_rec.attribute76;
144 x_api_rec.attribute77 := p_adj_rec.attribute77;
145 x_api_rec.attribute78 := p_adj_rec.attribute78;
146 x_api_rec.attribute79 := p_adj_rec.attribute79;
147 x_api_rec.attribute80 := p_adj_rec.attribute80;
148 x_api_rec.attribute81 := p_adj_rec.attribute81;
149 x_api_rec.attribute82 := p_adj_rec.attribute82;
150 x_api_rec.attribute83 := p_adj_rec.attribute83;
151 x_api_rec.attribute84 := p_adj_rec.attribute84;
152 x_api_rec.attribute85 := p_adj_rec.attribute85;
153 x_api_rec.attribute86 := p_adj_rec.attribute86;
154 x_api_rec.attribute87 := p_adj_rec.attribute87;
155 x_api_rec.attribute88 := p_adj_rec.attribute88;
156 x_api_rec.attribute89 := p_adj_rec.attribute89;
157 x_api_rec.attribute90 := p_adj_rec.attribute90;
158 x_api_rec.attribute91 := p_adj_rec.attribute91;
159 x_api_rec.attribute92 := p_adj_rec.attribute92;
160 x_api_rec.attribute93 := p_adj_rec.attribute93;
161 x_api_rec.attribute94 := p_adj_rec.attribute94;
162 x_api_rec.attribute95 := p_adj_rec.attribute95;
163 x_api_rec.attribute96 := p_adj_rec.attribute96;
164 x_api_rec.attribute97 := p_adj_rec.attribute97;
165 x_api_rec.attribute98 := p_adj_rec.attribute98;
166 x_api_rec.attribute99 := p_adj_rec.attribute99;
167 x_api_rec.attribute100 := p_adj_rec.attribute100;
168 x_api_rec.employee_number := p_adj_rec.direct_salesrep_number;
169 x_api_rec.comm_lines_api_id := p_adj_rec.comm_lines_api_id;
170 x_api_rec.conc_batch_id := NULL;
171 x_api_rec.process_batch_id := NULL;
172 x_api_rec.salesrep_number := NULL;
173 x_api_rec.rollup_date := p_adj_rec.rollup_date;
174 x_api_rec.source_doc_id := NULL;
175 x_api_rec.source_doc_type := p_adj_rec.source_doc_type;
176 x_api_rec.created_by := NULL;
177 x_api_rec.creation_date := NULL;
178 x_api_rec.last_updated_by := NULL;
179 x_api_rec.last_update_date := NULL;
180 x_api_rec.last_update_login := NULL;
181 x_api_rec.transaction_currency_code := p_adj_rec.orig_currency_code;
182 x_api_rec.exchange_rate := p_adj_rec.exchange_rate;
183 x_api_rec.acctd_transaction_amount := p_adj_rec.transaction_amount;
184 x_api_rec.trx_id := p_adj_rec.trx_id;
185 x_api_rec.trx_line_id := p_adj_rec.trx_line_id;
186 x_api_rec.trx_sales_line_id := p_adj_rec.trx_sales_line_id;
187 x_api_rec.org_id := NULL;
188 x_api_rec.quantity := p_adj_rec.quantity;
189 x_api_rec.source_trx_number := p_adj_rec.source_trx_number;
190 x_api_rec.discount_percentage := p_adj_rec.discount_percentage;
191 x_api_rec.margin_percentage := p_adj_rec.margin_percentage;
192 x_api_rec.pre_defined_rc_flag := NULL;
193 x_api_rec.rollup_flag := NULL;
194 x_api_rec.forecast_id := p_adj_rec.forecast_id;
195 x_api_rec.upside_quantity := p_adj_rec.upside_quantity;
196 x_api_rec.upside_amount := p_adj_rec.upside_amount;
197 x_api_rec.uom_code := p_adj_rec.uom_code;
198 x_api_rec.source_trx_id := p_adj_rec.source_trx_id;
199 x_api_rec.source_trx_line_id := p_adj_rec.source_trx_line_id;
200 x_api_rec.source_trx_sales_line_id := p_adj_rec.source_trx_sales_line_id;
201 x_api_rec.negated_flag := NULL;
202 x_api_rec.customer_id := p_adj_rec.customer_id;
203 x_api_rec.inventory_item_id := p_adj_rec.inventory_item_id;
204 x_api_rec.order_number := p_adj_rec.order_number;
205 x_api_rec.booked_date := p_adj_rec.order_date;
206 x_api_rec.invoice_number := p_adj_rec.invoice_number;
207 x_api_rec.invoice_date := p_adj_rec.invoice_date;
208 x_api_rec.bill_to_address_id := p_adj_rec.bill_to_address_id;
209 x_api_rec.ship_to_address_id := p_adj_rec.ship_to_address_id;
210 x_api_rec.bill_to_contact_id := p_adj_rec.bill_to_contact_id;
211 x_api_rec.ship_to_contact_id := p_adj_rec.ship_to_contact_id;
212 x_api_rec.adj_comm_lines_api_id := p_adj_rec.adj_comm_lines_api_id;
213 x_api_rec.adjust_date := SYSDATE;
214 x_api_rec.adjusted_by := get_adjusted_by;
215 x_api_rec.revenue_type := p_adj_rec.revenue_type;
216 x_api_rec.adjust_rollup_flag := p_adj_rec.adjust_rollup_flag;
217 x_api_rec.adjust_comments := p_adj_rec.adjust_comments;
218 x_api_rec.adjust_status := p_adj_rec.adjust_status;
219 x_api_rec.line_number := p_adj_rec.line_number;
220 x_api_rec.reason_code := p_adj_rec.reason_code;
221 x_api_rec.type := p_adj_rec.type;
222 x_api_rec.pre_processed_code := p_adj_rec.pre_processed_code;
223 x_api_rec.quota_id := p_adj_rec.quota_id;
224 x_api_rec.srp_plan_assign_id := p_adj_rec.srp_plan_assign_id; -- NULL
225 x_api_rec.role_id := p_adj_rec.role_id; -- NULL
226 x_api_rec.comp_group_id := p_adj_rec.comp_group_id; -- NULL
227 x_api_rec.commission_amount := p_adj_rec.commission_amount;
228 x_api_rec.reversal_flag := NULL;
229 x_api_rec.reversal_header_id := NULL;
230 x_api_rec.sales_channel := p_adj_rec.sales_channel;
231 x_api_rec.object_version_number := p_adj_rec.object_version_number;
232 x_api_rec.split_pct := p_adj_rec.split_pct;
233 x_api_rec.split_status := p_adj_rec.split_status;
234 x_api_rec.org_id := p_adj_rec.org_id;
235 /* Added for Crediting Bug */
236 x_api_rec.terr_id := p_adj_rec.terr_id;
237 x_api_rec.terr_name := p_adj_rec.terr_name;
238 x_api_rec.preserve_credit_override_flag := p_adj_rec.preserve_credit_override_flag;
239 EXCEPTION
240 WHEN OTHERS THEN
241 NULL;
242 END;
243
244 -- Assumptions:
245 -- PL/SQL Table contains atleast on record.
246 PROCEDURE prepare_api_record(
247 p_newtx_rec IN cn_get_tx_data_pub.adj_rec_type,
248 p_old_adj_tbl IN cn_get_tx_data_pub.adj_tbl_type,
249 x_final_trx_rec OUT NOCOPY cn_get_tx_data_pub.adj_rec_type,
250 x_return_status OUT NOCOPY VARCHAR2) IS
251 --
252 l_attribute1 VARCHAR2(240);
253 l_attribute100 VARCHAR2(240);
254 l_exchange_rate cn_comm_lines_api.exchange_rate%type;
255 --
256 BEGIN
257
258 IF (p_newtx_rec.exchange_rate <> FND_API.G_MISS_NUM) THEN
259 l_exchange_rate := p_newtx_rec.exchange_rate;
260 ELSE
261 IF ((p_newtx_rec.orig_currency_code = FND_API.G_MISS_CHAR) OR
262 (p_newtx_rec.orig_currency_code = p_old_adj_tbl(1).orig_currency_code))
263 THEN
264 l_exchange_rate := p_old_adj_tbl(1).exchange_rate;
265 ELSE
266 l_exchange_rate := null;
267 END IF;
268 END IF;
269
270 SELECT
271
272 DECODE(p_newtx_rec.direct_salesrep_id,FND_API.G_MISS_NUM,
273 p_old_adj_tbl(1).direct_salesrep_id,p_newtx_rec.direct_salesrep_id),
274 DECODE(p_newtx_rec.inventory_item_id,FND_API.G_MISS_NUM,
275 p_old_adj_tbl(1).inventory_item_id,p_newtx_rec.inventory_item_id),
276 -- Bug fix 5349170
277 DECODE(nvl(p_newtx_rec.source_trx_id,FND_API.G_MISS_num), FND_API.G_MISS_NUM,
278 p_old_adj_tbl(1).source_trx_id,p_newtx_rec.source_trx_id),
279 DECODE(nvl(p_newtx_rec.source_trx_line_id,FND_API.G_MISS_num), FND_API.G_MISS_NUM,
280 p_old_adj_tbl(1).source_trx_line_id,p_newtx_rec.source_trx_line_id),
281 DECODE(nvl(p_newtx_rec.source_trx_sales_line_id,FND_API.G_MISS_num), FND_API.G_MISS_NUM,
282 p_old_adj_tbl(1).source_trx_sales_line_id,p_newtx_rec.source_trx_sales_line_id),
283 -- Bug fix 5349170
284 DECODE(p_newtx_rec.processed_date,FND_API.G_MISS_DATE,
285 p_old_adj_tbl(1).processed_date,p_newtx_rec.processed_date),
286 DECODE(p_newtx_rec.transaction_amount,FND_API.G_MISS_NUM,
287 p_old_adj_tbl(1).transaction_amount,p_newtx_rec.transaction_amount),
288 DECODE(p_newtx_rec.trx_type,FND_API.G_MISS_CHAR,
289 p_old_adj_tbl(1).trx_type,p_newtx_rec.trx_type),
290 DECODE(p_newtx_rec.revenue_class_id,FND_API.G_MISS_NUM,
291 p_old_adj_tbl(1).revenue_class_id,p_newtx_rec.revenue_class_id),
292 'UNLOADED',
293 DECODE(p_newtx_rec.attribute_category,FND_API.G_MISS_CHAR,
294 p_old_adj_tbl(1).attribute_category,p_newtx_rec.attribute_category),
295 DECODE(p_newtx_rec.attribute1,FND_API.G_MISS_CHAR,
296 p_old_adj_tbl(1).attribute1,p_newtx_rec.attribute1),
297 DECODE(p_newtx_rec.attribute2,FND_API.G_MISS_CHAR,
298 p_old_adj_tbl(1).attribute2,p_newtx_rec.attribute2),
299 DECODE(p_newtx_rec.attribute3,FND_API.G_MISS_CHAR,
300 p_old_adj_tbl(1).attribute3,p_newtx_rec.attribute3),
301 DECODE(p_newtx_rec.attribute4,FND_API.G_MISS_CHAR,
302 p_old_adj_tbl(1).attribute4,p_newtx_rec.attribute4),
303 DECODE(p_newtx_rec.attribute5,FND_API.G_MISS_CHAR,
304 p_old_adj_tbl(1).attribute5,p_newtx_rec.attribute5),
305 DECODE(p_newtx_rec.attribute6,FND_API.G_MISS_CHAR,
306 p_old_adj_tbl(1).attribute6,p_newtx_rec.attribute6),
307 DECODE(p_newtx_rec.attribute7,FND_API.G_MISS_CHAR,
308 p_old_adj_tbl(1).attribute7,p_newtx_rec.attribute7),
309 DECODE(p_newtx_rec.attribute8,FND_API.G_MISS_CHAR,
310 p_old_adj_tbl(1).attribute8,p_newtx_rec.attribute8),
311 DECODE(p_newtx_rec.attribute9,FND_API.G_MISS_CHAR,
312 p_old_adj_tbl(1).attribute9,p_newtx_rec.attribute9),
313 DECODE(p_newtx_rec.attribute10,FND_API.G_MISS_CHAR,
314 p_old_adj_tbl(1).attribute10,p_newtx_rec.attribute10),
315 DECODE(p_newtx_rec.attribute11,FND_API.G_MISS_CHAR,
316 p_old_adj_tbl(1).attribute11,p_newtx_rec.attribute11),
317 DECODE(p_newtx_rec.attribute12,FND_API.G_MISS_CHAR,
318 p_old_adj_tbl(1).attribute12,p_newtx_rec.attribute12),
319 DECODE(p_newtx_rec.attribute13,FND_API.G_MISS_CHAR,
320 p_old_adj_tbl(1).attribute13,p_newtx_rec.attribute13),
321 DECODE(p_newtx_rec.attribute14,FND_API.G_MISS_CHAR,
322 p_old_adj_tbl(1).attribute14,p_newtx_rec.attribute14),
323 DECODE(p_newtx_rec.attribute15,FND_API.G_MISS_CHAR,
324 p_old_adj_tbl(1).attribute15,p_newtx_rec.attribute15),
325 DECODE(p_newtx_rec.attribute16,FND_API.G_MISS_CHAR,
326 p_old_adj_tbl(1).attribute16,p_newtx_rec.attribute16),
327 DECODE(p_newtx_rec.attribute17,FND_API.G_MISS_CHAR,
328 p_old_adj_tbl(1).attribute17,p_newtx_rec.attribute17),
329 DECODE(p_newtx_rec.attribute18,FND_API.G_MISS_CHAR,
330 p_old_adj_tbl(1).attribute18,p_newtx_rec.attribute18),
331 DECODE(p_newtx_rec.attribute19,FND_API.G_MISS_CHAR,
332 p_old_adj_tbl(1).attribute19,p_newtx_rec.attribute19),
333 DECODE(p_newtx_rec.attribute20,FND_API.G_MISS_CHAR,
334 p_old_adj_tbl(1).attribute20,p_newtx_rec.attribute20),
335 DECODE(p_newtx_rec.attribute21,FND_API.G_MISS_CHAR,
336 p_old_adj_tbl(1).attribute21,p_newtx_rec.attribute21),
337 DECODE(p_newtx_rec.attribute22,FND_API.G_MISS_CHAR,
338 p_old_adj_tbl(1).attribute22,p_newtx_rec.attribute22),
339 DECODE(p_newtx_rec.attribute23,FND_API.G_MISS_CHAR,
340 p_old_adj_tbl(1).attribute23,p_newtx_rec.attribute23),
341 DECODE(p_newtx_rec.attribute24,FND_API.G_MISS_CHAR,
342 p_old_adj_tbl(1).attribute24,p_newtx_rec.attribute24),
343 DECODE(p_newtx_rec.attribute25,FND_API.G_MISS_CHAR,
344 p_old_adj_tbl(1).attribute25,p_newtx_rec.attribute25),
345 DECODE(p_newtx_rec.attribute26,FND_API.G_MISS_CHAR,
346 p_old_adj_tbl(1).attribute26,p_newtx_rec.attribute26),
347 DECODE(p_newtx_rec.attribute27,FND_API.G_MISS_CHAR,
348 p_old_adj_tbl(1).attribute27,p_newtx_rec.attribute27),
349 DECODE(p_newtx_rec.attribute28,FND_API.G_MISS_CHAR,
350 p_old_adj_tbl(1).attribute28,p_newtx_rec.attribute28),
351 DECODE(p_newtx_rec.attribute29,FND_API.G_MISS_CHAR,
352 p_old_adj_tbl(1).attribute29,p_newtx_rec.attribute29),
353 DECODE(p_newtx_rec.attribute30,FND_API.G_MISS_CHAR,
354 p_old_adj_tbl(1).attribute30,p_newtx_rec.attribute30),
355 DECODE(p_newtx_rec.attribute31,FND_API.G_MISS_CHAR,
356 p_old_adj_tbl(1).attribute31,p_newtx_rec.attribute31),
357 DECODE(p_newtx_rec.attribute32,FND_API.G_MISS_CHAR,
358 p_old_adj_tbl(1).attribute32,p_newtx_rec.attribute32),
359 DECODE(p_newtx_rec.attribute33,FND_API.G_MISS_CHAR,
360 p_old_adj_tbl(1).attribute33,p_newtx_rec.attribute33),
361 DECODE(p_newtx_rec.attribute34,FND_API.G_MISS_CHAR,
362 p_old_adj_tbl(1).attribute34,p_newtx_rec.attribute34),
363 DECODE(p_newtx_rec.attribute35,FND_API.G_MISS_CHAR,
364 p_old_adj_tbl(1).attribute35,p_newtx_rec.attribute35),
365 DECODE(p_newtx_rec.attribute36,FND_API.G_MISS_CHAR,
366 p_old_adj_tbl(1).attribute36,p_newtx_rec.attribute36),
367 DECODE(p_newtx_rec.attribute37,FND_API.G_MISS_CHAR,
368 p_old_adj_tbl(1).attribute37,p_newtx_rec.attribute37),
369 DECODE(p_newtx_rec.attribute38,FND_API.G_MISS_CHAR,
370 p_old_adj_tbl(1).attribute38,p_newtx_rec.attribute38),
371 DECODE(p_newtx_rec.attribute39,FND_API.G_MISS_CHAR,
372 p_old_adj_tbl(1).attribute39,p_newtx_rec.attribute39),
373 DECODE(p_newtx_rec.attribute40,FND_API.G_MISS_CHAR,
374 p_old_adj_tbl(1).attribute40,p_newtx_rec.attribute40),
375 DECODE(p_newtx_rec.attribute41,FND_API.G_MISS_CHAR,
376 p_old_adj_tbl(1).attribute41,p_newtx_rec.attribute41),
377 DECODE(p_newtx_rec.attribute42,FND_API.G_MISS_CHAR,
378 p_old_adj_tbl(1).attribute42,p_newtx_rec.attribute42),
379 DECODE(p_newtx_rec.attribute43,FND_API.G_MISS_CHAR,
380 p_old_adj_tbl(1).attribute43,p_newtx_rec.attribute43),
381 DECODE(p_newtx_rec.attribute44,FND_API.G_MISS_CHAR,
382 p_old_adj_tbl(1).attribute44,p_newtx_rec.attribute44),
383 DECODE(p_newtx_rec.attribute45,FND_API.G_MISS_CHAR,
384 p_old_adj_tbl(1).attribute45,p_newtx_rec.attribute45),
385 DECODE(p_newtx_rec.attribute46,FND_API.G_MISS_CHAR,
386 p_old_adj_tbl(1).attribute46,p_newtx_rec.attribute46),
387 DECODE(p_newtx_rec.attribute47,FND_API.G_MISS_CHAR,
388 p_old_adj_tbl(1).attribute47,p_newtx_rec.attribute47),
389 DECODE(p_newtx_rec.attribute48,FND_API.G_MISS_CHAR,
390 p_old_adj_tbl(1).attribute48,p_newtx_rec.attribute48),
391 DECODE(p_newtx_rec.attribute49,FND_API.G_MISS_CHAR,
392 p_old_adj_tbl(1).attribute49,p_newtx_rec.attribute49),
393 DECODE(p_newtx_rec.attribute50,FND_API.G_MISS_CHAR,
394 p_old_adj_tbl(1).attribute50,p_newtx_rec.attribute50),
395 DECODE(p_newtx_rec.attribute51,FND_API.G_MISS_CHAR,
396 p_old_adj_tbl(1).attribute51,p_newtx_rec.attribute51),
397 DECODE(p_newtx_rec.attribute52,FND_API.G_MISS_CHAR,
398 p_old_adj_tbl(1).attribute52,p_newtx_rec.attribute52),
399 DECODE(p_newtx_rec.attribute53,FND_API.G_MISS_CHAR,
400 p_old_adj_tbl(1).attribute53,p_newtx_rec.attribute53),
401 DECODE(p_newtx_rec.attribute54,FND_API.G_MISS_CHAR,
402 p_old_adj_tbl(1).attribute54,p_newtx_rec.attribute54),
403 DECODE(p_newtx_rec.attribute55,FND_API.G_MISS_CHAR,
404 p_old_adj_tbl(1).attribute55,p_newtx_rec.attribute55),
405 DECODE(p_newtx_rec.attribute56,FND_API.G_MISS_CHAR,
406 p_old_adj_tbl(1).attribute56,p_newtx_rec.attribute56),
407 DECODE(p_newtx_rec.attribute57,FND_API.G_MISS_CHAR,
408 p_old_adj_tbl(1).attribute57,p_newtx_rec.attribute57),
409 DECODE(p_newtx_rec.attribute58,FND_API.G_MISS_CHAR,
410 p_old_adj_tbl(1).attribute58,p_newtx_rec.attribute58),
411 DECODE(p_newtx_rec.attribute59,FND_API.G_MISS_CHAR,
412 p_old_adj_tbl(1).attribute59,p_newtx_rec.attribute59),
413 DECODE(p_newtx_rec.attribute60,FND_API.G_MISS_CHAR,
414 p_old_adj_tbl(1).attribute60,p_newtx_rec.attribute60),
415 DECODE(p_newtx_rec.attribute61,FND_API.G_MISS_CHAR,
416 p_old_adj_tbl(1).attribute61,p_newtx_rec.attribute61),
417 DECODE(p_newtx_rec.attribute62,FND_API.G_MISS_CHAR,
418 p_old_adj_tbl(1).attribute62,p_newtx_rec.attribute62),
419 DECODE(p_newtx_rec.attribute63,FND_API.G_MISS_CHAR,
420 p_old_adj_tbl(1).attribute63,p_newtx_rec.attribute63),
421 DECODE(p_newtx_rec.attribute64,FND_API.G_MISS_CHAR,
422 p_old_adj_tbl(1).attribute64,p_newtx_rec.attribute64),
423 DECODE(p_newtx_rec.attribute65,FND_API.G_MISS_CHAR,
424 p_old_adj_tbl(1).attribute65,p_newtx_rec.attribute65),
425 DECODE(p_newtx_rec.attribute66,FND_API.G_MISS_CHAR,
426 p_old_adj_tbl(1).attribute66,p_newtx_rec.attribute66),
427 DECODE(p_newtx_rec.attribute67,FND_API.G_MISS_CHAR,
428 p_old_adj_tbl(1).attribute67,p_newtx_rec.attribute67),
429 DECODE(p_newtx_rec.attribute68,FND_API.G_MISS_CHAR,
430 p_old_adj_tbl(1).attribute68,p_newtx_rec.attribute68),
431 DECODE(p_newtx_rec.attribute69,FND_API.G_MISS_CHAR,
432 p_old_adj_tbl(1).attribute69,p_newtx_rec.attribute69),
433 DECODE(p_newtx_rec.attribute70,FND_API.G_MISS_CHAR,
434 p_old_adj_tbl(1).attribute70,p_newtx_rec.attribute70),
435 DECODE(p_newtx_rec.attribute71,FND_API.G_MISS_CHAR,
436 p_old_adj_tbl(1).attribute71,p_newtx_rec.attribute71),
437 DECODE(p_newtx_rec.attribute72,FND_API.G_MISS_CHAR,
438 p_old_adj_tbl(1).attribute72,p_newtx_rec.attribute72),
439 DECODE(p_newtx_rec.attribute73,FND_API.G_MISS_CHAR,
440 p_old_adj_tbl(1).attribute73,p_newtx_rec.attribute73),
441 DECODE(p_newtx_rec.attribute74,FND_API.G_MISS_CHAR,
442 p_old_adj_tbl(1).attribute74,p_newtx_rec.attribute74),
443 DECODE(p_newtx_rec.attribute75,FND_API.G_MISS_CHAR,
444 p_old_adj_tbl(1).attribute75,p_newtx_rec.attribute75),
445 DECODE(p_newtx_rec.attribute76,FND_API.G_MISS_CHAR,
446 p_old_adj_tbl(1).attribute76,p_newtx_rec.attribute76),
447 DECODE(p_newtx_rec.attribute77,FND_API.G_MISS_CHAR,
448 p_old_adj_tbl(1).attribute77,p_newtx_rec.attribute77),
449 DECODE(p_newtx_rec.attribute78,FND_API.G_MISS_CHAR,
450 p_old_adj_tbl(1).attribute78,p_newtx_rec.attribute78),
451 DECODE(p_newtx_rec.attribute79,FND_API.G_MISS_CHAR,
452 p_old_adj_tbl(1).attribute79,p_newtx_rec.attribute79),
453 DECODE(p_newtx_rec.attribute80,FND_API.G_MISS_CHAR,
454 p_old_adj_tbl(1).attribute80,p_newtx_rec.attribute80),
455 DECODE(p_newtx_rec.attribute81,FND_API.G_MISS_CHAR,
456 p_old_adj_tbl(1).attribute81,p_newtx_rec.attribute81),
457 DECODE(p_newtx_rec.attribute82,FND_API.G_MISS_CHAR,
458 p_old_adj_tbl(1).attribute82,p_newtx_rec.attribute82),
459 DECODE(p_newtx_rec.attribute83,FND_API.G_MISS_CHAR,
460 p_old_adj_tbl(1).attribute83,p_newtx_rec.attribute83),
461 DECODE(p_newtx_rec.attribute84,FND_API.G_MISS_CHAR,
462 p_old_adj_tbl(1).attribute84,p_newtx_rec.attribute84),
463 DECODE(p_newtx_rec.attribute85,FND_API.G_MISS_CHAR,
464 p_old_adj_tbl(1).attribute85,p_newtx_rec.attribute85),
465 DECODE(p_newtx_rec.attribute86,FND_API.G_MISS_CHAR,
466 p_old_adj_tbl(1).attribute86,p_newtx_rec.attribute86),
467 DECODE(p_newtx_rec.attribute87,FND_API.G_MISS_CHAR,
468 p_old_adj_tbl(1).attribute87,p_newtx_rec.attribute87),
469 DECODE(p_newtx_rec.attribute88,FND_API.G_MISS_CHAR,
470 p_old_adj_tbl(1).attribute88,p_newtx_rec.attribute88),
471 DECODE(p_newtx_rec.attribute89,FND_API.G_MISS_CHAR,
472 p_old_adj_tbl(1).attribute89,p_newtx_rec.attribute89),
473 DECODE(p_newtx_rec.attribute90,FND_API.G_MISS_CHAR,
474 p_old_adj_tbl(1).attribute90,p_newtx_rec.attribute90),
475 DECODE(p_newtx_rec.attribute91,FND_API.G_MISS_CHAR,
476 p_old_adj_tbl(1).attribute91,p_newtx_rec.attribute91),
477 DECODE(p_newtx_rec.attribute92,FND_API.G_MISS_CHAR,
478 p_old_adj_tbl(1).attribute92,p_newtx_rec.attribute92),
479 DECODE(p_newtx_rec.attribute93,FND_API.G_MISS_CHAR,
480 p_old_adj_tbl(1).attribute93,p_newtx_rec.attribute93),
481 DECODE(p_newtx_rec.attribute94,FND_API.G_MISS_CHAR,
482 p_old_adj_tbl(1).attribute94,p_newtx_rec.attribute94),
483 DECODE(p_newtx_rec.attribute95,FND_API.G_MISS_CHAR,
484 p_old_adj_tbl(1).attribute95,p_newtx_rec.attribute95),
485 DECODE(p_newtx_rec.attribute96,FND_API.G_MISS_CHAR,
486 p_old_adj_tbl(1).attribute96,p_newtx_rec.attribute96),
487 DECODE(p_newtx_rec.attribute97,FND_API.G_MISS_CHAR,
488 p_old_adj_tbl(1).attribute97,p_newtx_rec.attribute97),
489 DECODE(p_newtx_rec.attribute98,FND_API.G_MISS_CHAR,
490 p_old_adj_tbl(1).attribute98,p_newtx_rec.attribute98),
491 DECODE(p_newtx_rec.attribute99,FND_API.G_MISS_CHAR,
492 p_old_adj_tbl(1).attribute99,p_newtx_rec.attribute99),
493 DECODE(p_newtx_rec.attribute100,FND_API.G_MISS_CHAR,
494 p_old_adj_tbl(1).attribute100,p_newtx_rec.attribute100),
495 DECODE(p_newtx_rec.rollup_date,FND_API.G_MISS_DATE,
496 p_old_adj_tbl(1).rollup_date,p_newtx_rec.rollup_date),
497 DECODE(p_newtx_rec.source_doc_type,FND_API.G_MISS_CHAR,
498 p_old_adj_tbl(1).source_doc_type,p_newtx_rec.source_doc_type),
499 DECODE(p_newtx_rec.orig_currency_code,FND_API.G_MISS_CHAR,
500 p_old_adj_tbl(1).orig_currency_code,
501 p_newtx_rec.orig_currency_code),
502
503 l_exchange_rate,
504
505 DECODE(p_newtx_rec.transaction_amount_orig,FND_API.G_MISS_NUM,
506 p_old_adj_tbl(1).transaction_amount_orig,
507 p_newtx_rec.transaction_amount_orig),
508 -- Bug fix 5220393. Changed decode statement to just null value check.
509 decode(nvl(p_newtx_rec.trx_id, FND_API.G_MISS_NUM),FND_API.G_MISS_NUM,
510 p_old_adj_tbl(1).trx_id, p_newtx_rec.trx_id),
511 decode(nvl(p_newtx_rec.trx_line_id, FND_API.G_MISS_NUM),FND_API.G_MISS_NUM,
512 p_old_adj_tbl(1).trx_line_id, p_newtx_rec.trx_line_id),
513 decode(nvl(p_newtx_rec.trx_sales_line_id, FND_API.G_MISS_NUM),FND_API.G_MISS_NUM,
514 p_old_adj_tbl(1).trx_sales_line_id, p_newtx_rec.trx_sales_line_id),
515 -- Bug fix 5220393. Changed decode statement to just null value check.
516 DECODE(p_newtx_rec.quantity,FND_API.G_MISS_NUM,
517 p_old_adj_tbl(1).quantity,p_newtx_rec.quantity),
518 DECODE(p_newtx_rec.source_trx_number,FND_API.G_MISS_CHAR,
519 p_old_adj_tbl(1).source_trx_number,p_newtx_rec.source_trx_number),
520 DECODE(p_newtx_rec.discount_percentage,FND_API.G_MISS_NUM,
521 p_old_adj_tbl(1).discount_percentage,p_newtx_rec.discount_percentage),
522 DECODE(p_newtx_rec.margin_percentage,FND_API.G_MISS_NUM,
523 p_old_adj_tbl(1).margin_percentage,p_newtx_rec.margin_percentage),
524 DECODE(p_newtx_rec.customer_id,FND_API.G_MISS_NUM,
525 p_old_adj_tbl(1).customer_id,p_newtx_rec.customer_id),
526 DECODE(p_newtx_rec.order_number,FND_API.G_MISS_NUM,
527 p_old_adj_tbl(1).order_number,p_newtx_rec.order_number),
528 p_newtx_rec.order_date,
529 DECODE(p_newtx_rec.invoice_number,FND_API.G_MISS_CHAR,
530 p_old_adj_tbl(1).invoice_number,p_newtx_rec.invoice_number),
531 p_newtx_rec.invoice_date,
532 SYSDATE,
533 DECODE(p_newtx_rec.revenue_type,FND_API.G_MISS_CHAR,
534 p_old_adj_tbl(1).revenue_type,p_newtx_rec.revenue_type),
535 DECODE(p_newtx_rec.adjust_comments,FND_API.G_MISS_CHAR,
536 p_old_adj_tbl(1).adjust_comments,p_newtx_rec.adjust_comments),
537 DECODE(p_newtx_rec.adjust_status,FND_API.G_MISS_CHAR,
538 p_old_adj_tbl(1).adjust_status,p_newtx_rec.adjust_status),
539 DECODE(p_newtx_rec.line_number,FND_API.G_MISS_NUM,
540 p_old_adj_tbl(1).line_number,p_newtx_rec.line_number),
541 DECODE(p_newtx_rec.bill_to_address_id,FND_API.G_MISS_NUM,
542 p_old_adj_tbl(1).bill_to_address_id,p_newtx_rec.bill_to_address_id),
543 DECODE(p_newtx_rec.ship_to_address_id,FND_API.G_MISS_NUM,
544 p_old_adj_tbl(1).ship_to_address_id,p_newtx_rec.ship_to_address_id),
545 DECODE(p_newtx_rec.bill_to_contact_id,FND_API.G_MISS_NUM,
546 p_old_adj_tbl(1).bill_to_contact_id,p_newtx_rec.bill_to_contact_id),
547 DECODE(p_newtx_rec.ship_to_contact_id,FND_API.G_MISS_NUM,
548 p_old_adj_tbl(1).ship_to_contact_id,p_newtx_rec.ship_to_contact_id),
549 DECODE(p_newtx_rec.reason_code,FND_API.G_MISS_CHAR,
550 p_old_adj_tbl(1).reason_code,p_newtx_rec.reason_code),
551 DECODE(p_newtx_rec.quota_id,FND_API.G_MISS_NUM,
552 p_old_adj_tbl(1).quota_id,p_newtx_rec.quota_id),
553 p_newtx_rec.comp_group_id,
554 DECODE(p_newtx_rec.direct_salesrep_number,FND_API.G_MISS_CHAR,
555 p_old_adj_tbl(1).direct_salesrep_number,
556 p_newtx_rec.direct_salesrep_number),
557 DECODE(p_newtx_rec.sales_channel,FND_API.G_MISS_CHAR,
558 p_old_adj_tbl(1).sales_channel,p_newtx_rec.sales_channel),
559 DECODE(p_newtx_rec.split_pct,FND_API.G_MISS_NUM,
560 p_old_adj_tbl(1).split_pct,p_newtx_rec.split_pct),
561 DECODE(p_newtx_rec.split_status,FND_API.G_MISS_CHAR,
562 p_old_adj_tbl(1).split_status,p_newtx_rec.split_status),
563 DECODE(p_newtx_rec.commission_amount,FND_API.G_MISS_CHAR,
564 p_old_adj_tbl(1).commission_amount,p_newtx_rec.commission_amount),
565 DECODE(p_newtx_rec.role_id,FND_API.G_MISS_NUM,
566 p_old_adj_tbl(1).role_id,p_newtx_rec.role_id),
567 DECODE(p_newtx_rec.pre_processed_code,FND_API.G_MISS_CHAR,
568 p_old_adj_tbl(1).pre_processed_code,p_newtx_rec.pre_processed_code),
569 DECODE(p_newtx_rec.org_id,FND_API.G_MISS_NUM,
570 p_old_adj_tbl(1).org_id,p_newtx_rec.org_id),
571 /* Added for Crediting Bug */
572 DECODE(p_newtx_rec.terr_id,FND_API.G_MISS_NUM,
573 p_old_adj_tbl(1).terr_id,p_newtx_rec.terr_id),
574 DECODE(p_newtx_rec.terr_name,FND_API.G_MISS_CHAR,
575 p_old_adj_tbl(1).terr_name,p_newtx_rec.terr_name) ,
576 DECODE(p_newtx_rec.preserve_credit_override_flag,FND_API.G_MISS_CHAR,
577 p_old_adj_tbl(1).preserve_credit_override_flag,p_newtx_rec.preserve_credit_override_flag)
578 INTO x_final_trx_rec.direct_salesrep_id, x_final_trx_rec.inventory_item_id,
579 x_final_trx_rec.source_trx_id, x_final_trx_rec.source_trx_line_id,
580 x_final_trx_rec.source_trx_sales_line_id ,
581 x_final_trx_rec.processed_date,
582 x_final_trx_rec.transaction_amount, x_final_trx_rec.trx_type,
583 x_final_trx_rec.revenue_class_id, x_final_trx_rec.load_status,
584 x_final_trx_rec.attribute_category,
585 x_final_trx_rec.attribute1, x_final_trx_rec.attribute2,
586 x_final_trx_rec.attribute3, x_final_trx_rec.attribute4,
587 x_final_trx_rec.attribute5, x_final_trx_rec.attribute6,
588 x_final_trx_rec.attribute7, x_final_trx_rec.attribute8,
589 x_final_trx_rec.attribute9, x_final_trx_rec.attribute10,
590 x_final_trx_rec.attribute11, x_final_trx_rec.attribute12,
591 x_final_trx_rec.attribute13, x_final_trx_rec.attribute14,
592 x_final_trx_rec.attribute15, x_final_trx_rec.attribute16,
593 x_final_trx_rec.attribute17, x_final_trx_rec.attribute18,
594 x_final_trx_rec.attribute19, x_final_trx_rec.attribute20,
595 x_final_trx_rec.attribute21, x_final_trx_rec.attribute22,
596 x_final_trx_rec.attribute23, x_final_trx_rec.attribute24,
597 x_final_trx_rec.attribute25, x_final_trx_rec.attribute26,
598 x_final_trx_rec.attribute27, x_final_trx_rec.attribute28,
599 x_final_trx_rec.attribute29, x_final_trx_rec.attribute30,
600 x_final_trx_rec.attribute31, x_final_trx_rec.attribute32,
601 x_final_trx_rec.attribute33, x_final_trx_rec.attribute34,
602 x_final_trx_rec.attribute35, x_final_trx_rec.attribute36,
603 x_final_trx_rec.attribute37, x_final_trx_rec.attribute38,
604 x_final_trx_rec.attribute39, x_final_trx_rec.attribute40,
605 x_final_trx_rec.attribute41, x_final_trx_rec.attribute42,
606 x_final_trx_rec.attribute43, x_final_trx_rec.attribute44,
607 x_final_trx_rec.attribute45, x_final_trx_rec.attribute46,
608 x_final_trx_rec.attribute47, x_final_trx_rec.attribute48,
609 x_final_trx_rec.attribute49, x_final_trx_rec.attribute50,
610 x_final_trx_rec.attribute51, x_final_trx_rec.attribute52,
611 x_final_trx_rec.attribute53, x_final_trx_rec.attribute54,
612 x_final_trx_rec.attribute55, x_final_trx_rec.attribute56,
613 x_final_trx_rec.attribute57, x_final_trx_rec.attribute58,
614 x_final_trx_rec.attribute59, x_final_trx_rec.attribute60,
615 x_final_trx_rec.attribute61, x_final_trx_rec.attribute62,
616 x_final_trx_rec.attribute63, x_final_trx_rec.attribute64,
617 x_final_trx_rec.attribute65, x_final_trx_rec.attribute66,
618 x_final_trx_rec.attribute67, x_final_trx_rec.attribute68,
619 x_final_trx_rec.attribute69, x_final_trx_rec.attribute70,
620 x_final_trx_rec.attribute71, x_final_trx_rec.attribute72,
621 x_final_trx_rec.attribute73, x_final_trx_rec.attribute74,
622 x_final_trx_rec.attribute75, x_final_trx_rec.attribute76,
623 x_final_trx_rec.attribute77, x_final_trx_rec.attribute78,
624 x_final_trx_rec.attribute79, x_final_trx_rec.attribute80,
625 x_final_trx_rec.attribute81, x_final_trx_rec.attribute82,
626 x_final_trx_rec.attribute83, x_final_trx_rec.attribute84,
627 x_final_trx_rec.attribute85, x_final_trx_rec.attribute86,
628 x_final_trx_rec.attribute87, x_final_trx_rec.attribute88,
629 x_final_trx_rec.attribute89, x_final_trx_rec.attribute90,
630 x_final_trx_rec.attribute91, x_final_trx_rec.attribute92,
631 x_final_trx_rec.attribute93, x_final_trx_rec.attribute94,
632 x_final_trx_rec.attribute95, x_final_trx_rec.attribute96,
633 x_final_trx_rec.attribute97, x_final_trx_rec.attribute98,
634 x_final_trx_rec.attribute99, x_final_trx_rec.attribute100,
635 x_final_trx_rec.rollup_date, x_final_trx_rec.source_doc_type,
636 x_final_trx_rec.orig_currency_code, x_final_trx_rec.exchange_rate,
637 x_final_trx_rec.transaction_amount_orig, x_final_trx_rec.trx_id,
638 x_final_trx_rec.trx_line_id, x_final_trx_rec.trx_sales_line_id,
639 x_final_trx_rec.quantity, x_final_trx_rec.source_trx_number,
640 x_final_trx_rec.discount_percentage, x_final_trx_rec.margin_percentage,
641 x_final_trx_rec.customer_id, x_final_trx_rec.order_number,
642 x_final_trx_rec.order_date, x_final_trx_rec.invoice_number,
643 x_final_trx_rec.invoice_date, x_final_trx_rec.adjust_date,
644 x_final_trx_rec.revenue_type, x_final_trx_rec.adjust_comments,
645 x_final_trx_rec.adjust_status,
646 x_final_trx_rec.line_number, x_final_trx_rec.bill_to_address_id,
647 x_final_trx_rec.ship_to_address_id, x_final_trx_rec.bill_to_contact_id,
648 x_final_trx_rec.ship_to_contact_id, x_final_trx_rec.reason_code ,
649 x_final_trx_rec.quota_id, x_final_trx_rec.comp_group_id,
650 x_final_trx_rec.direct_salesrep_number, x_final_trx_rec.sales_channel,
651 x_final_trx_rec.split_pct, x_final_trx_rec.split_status,
652 x_final_trx_rec.commission_amount,x_final_trx_rec.role_id,
653 x_final_trx_rec.pre_processed_code, x_final_trx_rec.org_id,
654 x_final_trx_rec.terr_id, x_final_trx_rec.terr_name,
655 x_final_trx_rec.preserve_credit_override_flag
656 FROM dual;
657 x_return_status := FND_API.G_RET_STS_SUCCESS;
658 EXCEPTION
659 WHEN OTHERS THEN
660 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
661 END;
662 --
663 PROCEDURE update_invoice_changes(
664 p_api_version IN NUMBER,
665 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
666 p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL,
667 p_existing_data IN invoice_tbl,
668 p_new_data IN invoice_tbl,
669 p_exist_data_check IN VARCHAR2 DEFAULT NULL,
670 p_new_data_check IN VARCHAR2 DEFAULT NULL,
671 x_return_status OUT NOCOPY VARCHAR2,
672 x_msg_count OUT NOCOPY NUMBER,
673 x_msg_data OUT NOCOPY VARCHAR2,
674 x_loading_status OUT NOCOPY VARCHAR2) IS
675 --
676 l_api_name CONSTANT VARCHAR2(30) := 'update_invoice_changes';
677 l_api_version CONSTANT NUMBER := 1.0;
678 --
679 l_invoice_change_id NUMBER;
680 -- PL/SQL tables and records
681 l_insert_rec cn_invoice_changes_pkg.invoice_changes_all_rec_type;
682 --
683 CURSOR c1(
684 l_comm_lines_api_id NUMBER) IS
685 SELECT invoice_change_id
686 FROM cn_invoice_changes
687 WHERE comm_lines_api_id = l_comm_lines_api_id;
688 --
689 BEGIN
690 -- Standard Start of API savepoint
691 SAVEPOINT update_invoice_changes;
692 -- Standard call to check for call compatibility.
693 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
694 p_api_version ,
695 l_api_name,
696 G_PKG_NAME ) THEN
697 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
698 END IF;
699 -- Initialize message list if p_init_msg_list is set to TRUE.
700 IF FND_API.to_Boolean( p_init_msg_list ) THEN
701 FND_MSG_PUB.initialize;
702 END IF;
703 -- Initialize API return status to success
704 x_return_status := FND_API.G_RET_STS_SUCCESS;
705 x_loading_status := 'CN_INSERTED';
706 -- API body
707 -- Delete the existing records in cn_invoice_changes table.
708 -- apiId: I need to change here. update_api is OK with api_id.
709 -- apiId: trx split uses this and it is OK with api_id.
710 -- apiId: Move credits also uses and it is OK with api_id.
711 IF (NVL(p_exist_data_check,'Y') = 'Y') THEN
712 FOR i IN p_existing_data.FIRST..p_existing_data.LAST
713 LOOP
714 FOR c1_rec IN c1(p_existing_data(i).comm_lines_api_id) LOOP
715 cn_invoice_changes_pkg.delete_row(c1_rec.invoice_change_id);
716 END LOOP;
717 END LOOP;
718 END IF;
719 -- Create new records in the same table.
720 IF (NVL(p_new_data_check,'Y') = 'Y') THEN
721 FOR i IN p_new_data.FIRST..p_new_data.LAST
722 LOOP
723 --
724 SELECT cn_invoice_change_s.NEXTVAL
725 INTO l_invoice_change_id
726 FROM dual;
727 --
728 l_insert_rec.invoice_change_id := l_invoice_change_id;
729 l_insert_rec.salesrep_id := p_new_data(i).salesrep_id;
730 l_insert_rec.invoice_number := p_new_data(i).invoice_number;
731 l_insert_rec.line_number := p_new_data(i).line_number;
732 l_insert_rec.revenue_type := p_new_data(i).revenue_type;
733 l_insert_rec.split_pct := p_new_data(i).split_pct;
734 l_insert_rec.comm_lines_api_id := p_new_data(i).comm_lines_api_id;
735 --
736 cn_invoice_changes_pkg.insert_row(
737 p_invoice_changes_all_rec => l_insert_rec);
738 --
739 END LOOP;
740 END IF;
741 --
742 EXCEPTION
743 WHEN FND_API.G_EXC_ERROR THEN
744 ROLLBACK TO update_invoice_changes;
745 x_return_status := FND_API.G_RET_STS_ERROR ;
746 FND_MSG_PUB.Count_And_Get(
747 p_count => x_msg_count ,
748 p_data => x_msg_data ,
749 p_encoded => FND_API.G_FALSE);
750 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
751 ROLLBACK TO update_invoice_changes;
752 x_loading_status := 'UNEXPECTED_ERR';
753 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
754 FND_MSG_PUB.Count_And_Get(
755 p_count => x_msg_count ,
756 p_data => x_msg_data ,
757 p_encoded => FND_API.G_FALSE);
758 WHEN OTHERS THEN
759 ROLLBACK TO update_invoice_changes;
760 x_loading_status := 'UNEXPECTED_ERR';
761 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
762 IF FND_MSG_PUB.Check_Msg_Level(
763 FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
764 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
765 END IF;
766 FND_MSG_PUB.Count_And_Get(
767 p_count => x_msg_count ,
768 p_data => x_msg_data ,
769 p_encoded => FND_API.G_FALSE);
770 END;
771 --
772 /*-----------------------------------------------------------------------------
773 update_credit_memo logic:
774 Step 1: Create a cursor api_cur based on old split % data.
775 Step 2: Create a cursor header_cur based on old split % data.
776 Step 3: Open existing old split % PL/SQL table.
777 Step 4: For each record in the Step 3, open api_cur.
778 Step 5: Get the comm_lines_api from Step 4 and negate the record.
779 Step 6: Take the record info from api_cur and construct a adj_rec record type
780 Step 7: Open new split % PL/SQL.
781 Step 8: Complete constructing adj_rec type based on Step 6 and 7.
782 Step 9: Call cn_get_tx_data_pub.insert_api_record to create a new record.
783 -----------------------------------------------------------------------------*/
784 PROCEDURE update_credit_memo(
785 p_api_version IN NUMBER,
786 p_init_msg_list IN VARCHAR2:= FND_API.G_TRUE,
787 p_validation_level IN VARCHAR2:= FND_API.G_VALID_LEVEL_FULL,
788 p_existing_data IN invoice_tbl,
789 p_new_data IN invoice_tbl,
790 p_to_salesrep_id IN NUMBER := FND_API.G_MISS_NUM,
791 p_to_salesrep_number IN VARCHAR2:= FND_API.G_MISS_CHAR,
792 p_called_from IN VARCHAR2,
793 p_adjust_status IN VARCHAR2,
794 x_return_status OUT NOCOPY VARCHAR2,
795 x_msg_count OUT NOCOPY NUMBER,
796 x_msg_data OUT NOCOPY VARCHAR2,
797 x_loading_status OUT NOCOPY VARCHAR2) IS
798 --
799 l_api_name CONSTANT VARCHAR2(30) := 'update_credit_memo';
800 l_api_version CONSTANT NUMBER := 1.0;
801 --
802 l_last_update_date DATE := sysdate;
803 l_last_updated_by NUMBER := fnd_global.user_id;
804 l_creation_date DATE := sysdate;
805 l_created_by NUMBER := fnd_global.user_id;
806 l_last_update_login NUMBER := fnd_global.login_id;
807 l_comm_lines_api_id NUMBER;
808 --
809 l_invoice_change_id NUMBER;
810 --
811 l_newtx_rec cn_get_tx_data_pub.adj_rec_type;
812 --
813 CURSOR api_cur(
814 l_salesrep_id NUMBER,
815 l_invoice_number VARCHAR2,
816 l_line_number NUMBER,
817 l_revenue_type VARCHAR2,
818 l_split_pct NUMBER) IS
819 SELECT l.*
820 FROM cn_comm_lines_api l
821 WHERE l.salesrep_id = l_salesrep_id
822 AND l.invoice_number = l_invoice_number
823 AND l.line_number = l_line_number
824 AND l.revenue_type = l_revenue_type
825 AND l.split_pct = l_split_pct
826 AND l.trx_type IN ('CM','PMT')
827 AND l.load_status <> 'LOADED'
828 AND ((l.adjust_status NOT IN ('FROZEN','REVERSAL','SCA_PENDING')) OR
829 (l.adjust_status IS NULL))
830 AND ((l.split_status <> 'DELINKED') OR
831 (l.split_status IS NULL));
832 --
833 CURSOR header_cur(
834 l_salesrep_id NUMBER,
835 l_invoice_number VARCHAR2,
836 l_line_number NUMBER,
837 l_revenue_type VARCHAR2,
838 l_split_pct NUMBER) IS
839 SELECT h.*, api.terr_id, api.terr_name, api.preserve_credit_override_flag
840 FROM cn_commission_headers h,
841 cn_comm_lines_api api
842 WHERE h.direct_salesrep_id = l_salesrep_id
843 AND h.invoice_number = l_invoice_number
844 AND h.line_number = l_line_number
845 AND h.revenue_type = l_revenue_type
846 AND h.split_pct = l_split_pct
847 AND h.trx_type IN ('CM','PMT')
848 AND ((h.adjust_status NOT IN ('FROZEN','REVERSAL')) OR
849 (h.adjust_status IS NULL))
850 AND ((h.split_status <> 'DELINKED') OR
851 (h.split_status IS NULL))
852 AND api.comm_lines_api_id = h.comm_lines_api_id
853 AND api.org_id = h.org_id;
854 BEGIN
855 -- Standard Start of API savepoint
856 SAVEPOINT update_credit_memo;
857 -- Standard call to check for call compatibility.
858 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
859 p_api_version ,
860 l_api_name,
861 G_PKG_NAME ) THEN
862 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
863 END IF;
864 -- Initialize message list if p_init_msg_list is set to TRUE.
865 IF FND_API.to_Boolean( p_init_msg_list ) THEN
866 FND_MSG_PUB.initialize;
867 END IF;
868 -- Initialize API return status to success
869 x_return_status := FND_API.G_RET_STS_SUCCESS;
870 x_loading_status := 'CN_INSERTED';
871 -- API body
872 -- Based on the input criteria get all the records
873 FOR i IN p_existing_data.FIRST..p_existing_data.LAST
874 LOOP
875 FOR rec IN api_cur(p_existing_data(i).salesrep_id,
876 p_existing_data(i).invoice_number,
877 p_existing_data(i).line_number,
878 p_existing_data(i).revenue_type,
879 p_existing_data(i).split_pct)
880 LOOP
881 /* codeCheck: I should be able to pass original invoice transaction
882 adjust_comments */
883 -- Then negate the transaction
884 cn_adjustments_pkg.api_negate_record(
885 x_comm_lines_api_id => rec.comm_lines_api_id,
886 x_adjusted_by => get_adjusted_by,
887 x_adjust_comments => rec.adjust_comments);
888 -- Then create a transaction based on new split information.
889 l_newtx_rec.processed_period_id := rec.processed_period_id;
890 l_newtx_rec.processed_date := rec.processed_date;
891 l_newtx_rec.rollup_date := rec.rollup_date;
892 l_newtx_rec.transaction_amount := NULL;
893 l_newtx_rec.transaction_amount_orig := rec.transaction_amount;
894 l_newtx_rec.trx_type := rec.trx_type;
895 l_newtx_rec.quantity := rec.quantity;
896 l_newtx_rec.discount_percentage := rec.discount_percentage;
897 l_newtx_rec.margin_percentage := rec.margin_percentage;
898 l_newtx_rec.orig_currency_code := rec.transaction_currency_code;
899 l_newtx_rec.exchange_rate := rec.exchange_rate;
900 l_newtx_rec.reason_code := rec.reason_code;
901 l_newtx_rec.comments := NULL;
902 l_newtx_rec.attribute_category := rec.attribute_category;
903 l_newtx_rec.attribute1 := rec.attribute1;
904 l_newtx_rec.attribute2 := rec.attribute2;
905 l_newtx_rec.attribute3 := rec.attribute3;
906 l_newtx_rec.attribute4 := rec.attribute4;
907 l_newtx_rec.attribute5 := rec.attribute5;
908 l_newtx_rec.attribute6 := rec.attribute6;
909 l_newtx_rec.attribute7 := rec.attribute7;
910 l_newtx_rec.attribute8 := rec.attribute8;
911 l_newtx_rec.attribute9 := rec.attribute9;
912 l_newtx_rec.attribute10 := rec.attribute10;
913 l_newtx_rec.attribute11 := rec.attribute11;
914 l_newtx_rec.attribute12 := rec.attribute12;
915 l_newtx_rec.attribute13 := rec.attribute13;
916 l_newtx_rec.attribute14 := rec.attribute14;
917 l_newtx_rec.attribute15 := rec.attribute15;
918 l_newtx_rec.attribute16 := rec.attribute16;
919 l_newtx_rec.attribute17 := rec.attribute17;
920 l_newtx_rec.attribute18 := rec.attribute18;
921 l_newtx_rec.attribute19 := rec.attribute19;
922 l_newtx_rec.attribute20 := rec.attribute20;
923 l_newtx_rec.attribute21 := rec.attribute21;
924 l_newtx_rec.attribute22 := rec.attribute22;
925 l_newtx_rec.attribute23 := rec.attribute23;
926 l_newtx_rec.attribute24 := rec.attribute24;
927 l_newtx_rec.attribute25 := rec.attribute25;
928 l_newtx_rec.attribute26 := rec.attribute26;
929 l_newtx_rec.attribute27 := rec.attribute27;
930 l_newtx_rec.attribute28 := rec.attribute28;
931 l_newtx_rec.attribute29 := rec.attribute29;
932 l_newtx_rec.attribute30 := rec.attribute30;
933 l_newtx_rec.attribute31 := rec.attribute31;
934 l_newtx_rec.attribute32 := rec.attribute32;
935 l_newtx_rec.attribute33 := rec.attribute33;
936 l_newtx_rec.attribute34 := rec.attribute34;
937 l_newtx_rec.attribute35 := rec.attribute35;
938 l_newtx_rec.attribute36 := rec.attribute36;
939 l_newtx_rec.attribute37 := rec.attribute37;
940 l_newtx_rec.attribute38 := rec.attribute38;
941 l_newtx_rec.attribute39 := rec.attribute39;
942 l_newtx_rec.attribute40 := rec.attribute40;
943 l_newtx_rec.attribute41 := rec.attribute41;
944 l_newtx_rec.attribute42 := rec.attribute42;
945 l_newtx_rec.attribute43 := rec.attribute43;
946 l_newtx_rec.attribute44 := rec.attribute44;
947 l_newtx_rec.attribute45 := rec.attribute45;
948 l_newtx_rec.attribute46 := rec.attribute46;
949 l_newtx_rec.attribute47 := rec.attribute47;
950 l_newtx_rec.attribute48 := rec.attribute48;
951 l_newtx_rec.attribute49 := rec.attribute49;
952 l_newtx_rec.attribute50 := rec.attribute50;
953 l_newtx_rec.attribute51 := rec.attribute51;
954 l_newtx_rec.attribute52 := rec.attribute52;
955 l_newtx_rec.attribute53 := rec.attribute53;
956 l_newtx_rec.attribute54 := rec.attribute54;
957 l_newtx_rec.attribute55 := rec.attribute55;
958 l_newtx_rec.attribute56 := rec.attribute56;
959 l_newtx_rec.attribute57 := rec.attribute57;
960 l_newtx_rec.attribute58 := rec.attribute58;
961 l_newtx_rec.attribute59 := rec.attribute59;
962 l_newtx_rec.attribute60 := rec.attribute60;
963 l_newtx_rec.attribute61 := rec.attribute61;
964 l_newtx_rec.attribute62 := rec.attribute62;
965 l_newtx_rec.attribute63 := rec.attribute63;
966 l_newtx_rec.attribute64 := rec.attribute64;
967 l_newtx_rec.attribute65 := rec.attribute65;
968 l_newtx_rec.attribute66 := rec.attribute66;
969 l_newtx_rec.attribute67 := rec.attribute67;
970 l_newtx_rec.attribute68 := rec.attribute68;
971 l_newtx_rec.attribute69 := rec.attribute69;
972 l_newtx_rec.attribute70 := rec.attribute70;
973 l_newtx_rec.attribute71 := rec.attribute71;
974 l_newtx_rec.attribute72 := rec.attribute72;
975 l_newtx_rec.attribute73 := rec.attribute73;
976 l_newtx_rec.attribute74 := rec.attribute74;
977 l_newtx_rec.attribute75 := rec.attribute75;
978 l_newtx_rec.attribute76 := rec.attribute76;
979 l_newtx_rec.attribute77 := rec.attribute77;
980 l_newtx_rec.attribute78 := rec.attribute78;
981 l_newtx_rec.attribute79 := rec.attribute79;
982 l_newtx_rec.attribute80 := rec.attribute80;
983 l_newtx_rec.attribute81 := rec.attribute81;
984 l_newtx_rec.attribute82 := rec.attribute82;
985 l_newtx_rec.attribute83 := rec.attribute83;
986 l_newtx_rec.attribute84 := rec.attribute84;
987 l_newtx_rec.attribute85 := rec.attribute85;
988 l_newtx_rec.attribute86 := rec.attribute86;
989 l_newtx_rec.attribute87 := rec.attribute87;
990 l_newtx_rec.attribute88 := rec.attribute88;
991 l_newtx_rec.attribute89 := rec.attribute89;
992 l_newtx_rec.attribute90 := rec.attribute90;
993 l_newtx_rec.attribute91 := rec.attribute91;
994 l_newtx_rec.attribute92 := rec.attribute92;
995 l_newtx_rec.attribute93 := rec.attribute93;
996 l_newtx_rec.attribute94 := rec.attribute94;
997 l_newtx_rec.attribute95 := rec.attribute95;
998 l_newtx_rec.attribute96 := rec.attribute96;
999 l_newtx_rec.attribute97 := rec.attribute97;
1000 l_newtx_rec.attribute98 := rec.attribute98;
1001 l_newtx_rec.attribute99 := rec.attribute99;
1002 l_newtx_rec.attribute100 := rec.attribute100;
1003 l_newtx_rec.source_doc_type := rec.source_doc_type;
1004 l_newtx_rec.source_trx_number := rec.source_trx_number;
1005 l_newtx_rec.trx_sales_line_id := rec.trx_sales_line_id;
1006 l_newtx_rec.trx_line_id := rec.trx_line_id;
1007 l_newtx_rec.trx_id := rec.trx_id;
1008 l_newtx_rec.upside_amount := rec.upside_amount;
1009 l_newtx_rec.upside_quantity := rec.upside_quantity;
1010 l_newtx_rec.uom_code := rec.uom_code;
1011 l_newtx_rec.forecast_id := rec.forecast_id;
1012 l_newtx_rec.adj_comm_lines_api_id := rec.comm_lines_api_id;
1013 l_newtx_rec.invoice_number := rec.invoice_number;
1014 l_newtx_rec.invoice_date := rec.invoice_date;
1015 l_newtx_rec.order_number := rec.order_number;
1016 l_newtx_rec.order_date := rec.booked_date;
1017 l_newtx_rec.line_number := rec.line_number;
1018 l_newtx_rec.customer_id := rec.customer_id;
1019 l_newtx_rec.bill_to_address_id := rec.bill_to_address_id;
1020 l_newtx_rec.ship_to_address_id := rec.ship_to_address_id;
1021 l_newtx_rec.bill_to_contact_id := rec.bill_to_contact_id;
1022 l_newtx_rec.ship_to_contact_id := rec.ship_to_contact_id;
1023 l_newtx_rec.load_status := 'UNLOADED';
1024 l_newtx_rec.revenue_type := rec.revenue_type;
1025 l_newtx_rec.adjust_rollup_flag := rec.adjust_rollup_flag;
1026 l_newtx_rec.adjust_date := rec.adjust_date;
1027 l_newtx_rec.adjusted_by := rec.adjusted_by;
1028 l_newtx_rec.adjust_status := p_adjust_status;
1029 l_newtx_rec.adjust_comments := rec.adjust_comments;
1030 l_newtx_rec.type := rec.type;
1031 l_newtx_rec.pre_processed_code := rec.pre_processed_code;
1032 l_newtx_rec.comp_group_id := rec.comp_group_id;
1033 l_newtx_rec.srp_plan_assign_id := rec.srp_plan_assign_id;
1034 l_newtx_rec.role_id := rec.role_id;
1035 l_newtx_rec.sales_channel := rec.sales_channel;
1036 l_newtx_rec.split_pct := rec.split_pct;
1037 l_newtx_rec.split_status := 'LINKED';
1038 l_newtx_rec.commission_amount := rec.commission_amount;
1039
1040 /* Added for crediting bug*/
1041 l_newtx_rec.terr_id := rec.terr_id;
1042 l_newtx_rec.terr_name := rec.terr_name;
1043 l_newtx_rec.preserve_credit_override_flag := rec.preserve_credit_override_flag;
1044
1045 --Added for Crediting bug
1046 IF(rec.terr_id IS NOT NULL)
1047 THEN
1048 l_newtx_rec.preserve_credit_override_flag := 'Y';
1049 l_newtx_rec.terr_id := -999;
1050 END IF;
1051
1052
1053 --
1054 -- Update this record with new split information.
1055 IF (p_called_from = 'MASS') THEN
1056 l_newtx_rec.direct_salesrep_number := p_to_salesrep_number;
1057 l_newtx_rec.direct_salesrep_id := p_to_salesrep_id;
1058 l_newtx_rec.invoice_number := rec.invoice_number;
1059 l_newtx_rec.line_number := rec.line_number;
1060 l_newtx_rec.revenue_type := rec.revenue_type;
1061 l_newtx_rec.split_pct := rec.split_pct;
1062 --
1063 cn_get_tx_data_pub.insert_api_record(
1064 p_api_version => p_api_version,
1065 p_init_msg_list => p_init_msg_list,
1066 p_validation_level => p_validation_level,
1067 p_action => 'UPDATE',
1068 p_newtx_rec => l_newtx_rec,
1069 x_api_id => l_comm_lines_api_id,
1070 x_return_status => x_return_status,
1071 x_msg_count => x_msg_count,
1072 x_msg_data => x_msg_data,
1073 x_loading_status => x_loading_status);
1074 -- codeCheck: I need to handle the return_status
1075 ELSE
1076 FOR i IN p_new_data.FIRST..p_new_data.LAST
1077 LOOP
1078 l_newtx_rec.direct_salesrep_number := p_new_data(i).direct_salesrep_number;
1079 l_newtx_rec.direct_salesrep_id := p_new_data(i).salesrep_id;
1080 l_newtx_rec.invoice_number := p_new_data(i).invoice_number;
1081 l_newtx_rec.line_number := p_new_data(i).line_number;
1082 l_newtx_rec.revenue_type := p_new_data(i).revenue_type;
1083 l_newtx_rec.split_pct := p_new_data(i).split_pct;
1084 IF (p_called_from = 'SPLIT') THEN
1085 l_newtx_rec.transaction_amount_orig
1086 := ROUND((rec.transaction_amount*
1087 p_new_data(i).split_pct)/100,2);
1088 END IF;
1089 -- Create a record in the cn_comm_lines_api table using this record.
1090 cn_get_tx_data_pub.insert_api_record(
1091 p_api_version => p_api_version,
1092 p_init_msg_list => p_init_msg_list,
1093 p_validation_level => p_validation_level,
1094 p_action => 'UPDATE',
1095 p_newtx_rec => l_newtx_rec,
1096 x_api_id => l_comm_lines_api_id,
1097 x_return_status => x_return_status,
1098 x_msg_count => x_msg_count,
1099 x_msg_data => x_msg_data,
1100 x_loading_status => x_loading_status);
1101 --
1102 END LOOP;
1103 END IF;
1104 --
1105
1106 /* Added for Crediting Bug */
1107
1108 /*cn_get_tx_data_pub.update_credit_credentials(
1109 rec.comm_lines_api_id,
1110 rec.terr_id,
1111 rec.org_id,
1112 rec.adjusted_by
1113 );*/
1114
1115 END LOOP;
1116 --
1117 FOR rec IN header_cur(
1118 p_existing_data(i).salesrep_id,
1119 p_existing_data(i).invoice_number,
1120 p_existing_data(i).line_number,
1121 p_existing_data(i).revenue_type,
1122 p_existing_data(i).split_pct)
1123 LOOP
1124 /* codeCheck: I should be able to pass original invoice transaction
1125 adjust_comments */
1126 -- Then negate the transaction
1127 cn_adjustments_pkg.api_negate_record(
1128 x_comm_lines_api_id => rec.comm_lines_api_id,
1129 x_adjusted_by => get_adjusted_by,
1130 x_adjust_comments => rec.adjust_comments);
1131 -- Then create a transaction based on new split information.
1132 l_newtx_rec.processed_period_id := rec.processed_period_id;
1133 l_newtx_rec.processed_date := rec.processed_date;
1134 l_newtx_rec.rollup_date := rec.rollup_date;
1135 l_newtx_rec.transaction_amount := rec.transaction_amount;
1136 l_newtx_rec.transaction_amount_orig := NULL;
1137 l_newtx_rec.trx_type := rec.trx_type;
1138 l_newtx_rec.quantity := rec.quantity;
1139 l_newtx_rec.discount_percentage := rec.discount_percentage;
1140 l_newtx_rec.margin_percentage := rec.margin_percentage;
1141 l_newtx_rec.orig_currency_code := rec.orig_currency_code;
1142 l_newtx_rec.exchange_rate := rec.exchange_rate;
1143 l_newtx_rec.reason_code := rec.reason_code;
1144 l_newtx_rec.comments := rec.comments;
1145 l_newtx_rec.attribute_category := rec.attribute_category;
1146 l_newtx_rec.attribute1 := rec.attribute1;
1147 l_newtx_rec.attribute2 := rec.attribute2;
1148 l_newtx_rec.attribute3 := rec.attribute3;
1149 l_newtx_rec.attribute4 := rec.attribute4;
1150 l_newtx_rec.attribute5 := rec.attribute5;
1151 l_newtx_rec.attribute6 := rec.attribute6;
1152 l_newtx_rec.attribute7 := rec.attribute7;
1153 l_newtx_rec.attribute8 := rec.attribute8;
1154 l_newtx_rec.attribute9 := rec.attribute9;
1155 l_newtx_rec.attribute10 := rec.attribute10;
1156 l_newtx_rec.attribute11 := rec.attribute11;
1157 l_newtx_rec.attribute12 := rec.attribute12;
1158 l_newtx_rec.attribute13 := rec.attribute13;
1159 l_newtx_rec.attribute14 := rec.attribute14;
1160 l_newtx_rec.attribute15 := rec.attribute15;
1161 l_newtx_rec.attribute16 := rec.attribute16;
1162 l_newtx_rec.attribute17 := rec.attribute17;
1163 l_newtx_rec.attribute18 := rec.attribute18;
1164 l_newtx_rec.attribute19 := rec.attribute19;
1165 l_newtx_rec.attribute20 := rec.attribute20;
1166 l_newtx_rec.attribute21 := rec.attribute21;
1167 l_newtx_rec.attribute22 := rec.attribute22;
1168 l_newtx_rec.attribute23 := rec.attribute23;
1169 l_newtx_rec.attribute24 := rec.attribute24;
1170 l_newtx_rec.attribute25 := rec.attribute25;
1171 l_newtx_rec.attribute26 := rec.attribute26;
1172 l_newtx_rec.attribute27 := rec.attribute27;
1173 l_newtx_rec.attribute28 := rec.attribute28;
1174 l_newtx_rec.attribute29 := rec.attribute29;
1175 l_newtx_rec.attribute30 := rec.attribute30;
1176 l_newtx_rec.attribute31 := rec.attribute31;
1177 l_newtx_rec.attribute32 := rec.attribute32;
1178 l_newtx_rec.attribute33 := rec.attribute33;
1179 l_newtx_rec.attribute34 := rec.attribute34;
1180 l_newtx_rec.attribute35 := rec.attribute35;
1181 l_newtx_rec.attribute36 := rec.attribute36;
1182 l_newtx_rec.attribute37 := rec.attribute37;
1183 l_newtx_rec.attribute38 := rec.attribute38;
1184 l_newtx_rec.attribute39 := rec.attribute39;
1185 l_newtx_rec.attribute40 := rec.attribute40;
1186 l_newtx_rec.attribute41 := rec.attribute41;
1187 l_newtx_rec.attribute42 := rec.attribute42;
1188 l_newtx_rec.attribute43 := rec.attribute43;
1189 l_newtx_rec.attribute44 := rec.attribute44;
1190 l_newtx_rec.attribute45 := rec.attribute45;
1191 l_newtx_rec.attribute46 := rec.attribute46;
1192 l_newtx_rec.attribute47 := rec.attribute47;
1193 l_newtx_rec.attribute48 := rec.attribute48;
1194 l_newtx_rec.attribute49 := rec.attribute49;
1195 l_newtx_rec.attribute50 := rec.attribute50;
1196 l_newtx_rec.attribute51 := rec.attribute51;
1197 l_newtx_rec.attribute52 := rec.attribute52;
1198 l_newtx_rec.attribute53 := rec.attribute53;
1199 l_newtx_rec.attribute54 := rec.attribute54;
1200 l_newtx_rec.attribute55 := rec.attribute55;
1201 l_newtx_rec.attribute56 := rec.attribute56;
1202 l_newtx_rec.attribute57 := rec.attribute57;
1203 l_newtx_rec.attribute58 := rec.attribute58;
1204 l_newtx_rec.attribute59 := rec.attribute59;
1205 l_newtx_rec.attribute60 := rec.attribute60;
1206 l_newtx_rec.attribute61 := rec.attribute61;
1207 l_newtx_rec.attribute62 := rec.attribute62;
1208 l_newtx_rec.attribute63 := rec.attribute63;
1209 l_newtx_rec.attribute64 := rec.attribute64;
1210 l_newtx_rec.attribute65 := rec.attribute65;
1211 l_newtx_rec.attribute66 := rec.attribute66;
1212 l_newtx_rec.attribute67 := rec.attribute67;
1213 l_newtx_rec.attribute68 := rec.attribute68;
1214 l_newtx_rec.attribute69 := rec.attribute69;
1215 l_newtx_rec.attribute70 := rec.attribute70;
1216 l_newtx_rec.attribute71 := rec.attribute71;
1217 l_newtx_rec.attribute72 := rec.attribute72;
1218 l_newtx_rec.attribute73 := rec.attribute73;
1219 l_newtx_rec.attribute74 := rec.attribute74;
1220 l_newtx_rec.attribute75 := rec.attribute75;
1221 l_newtx_rec.attribute76 := rec.attribute76;
1222 l_newtx_rec.attribute77 := rec.attribute77;
1223 l_newtx_rec.attribute78 := rec.attribute78;
1224 l_newtx_rec.attribute79 := rec.attribute79;
1225 l_newtx_rec.attribute80 := rec.attribute80;
1226 l_newtx_rec.attribute81 := rec.attribute81;
1227 l_newtx_rec.attribute82 := rec.attribute82;
1228 l_newtx_rec.attribute83 := rec.attribute83;
1229 l_newtx_rec.attribute84 := rec.attribute84;
1230 l_newtx_rec.attribute85 := rec.attribute85;
1231 l_newtx_rec.attribute86 := rec.attribute86;
1232 l_newtx_rec.attribute87 := rec.attribute87;
1233 l_newtx_rec.attribute88 := rec.attribute88;
1234 l_newtx_rec.attribute89 := rec.attribute89;
1235 l_newtx_rec.attribute90 := rec.attribute90;
1236 l_newtx_rec.attribute91 := rec.attribute91;
1237 l_newtx_rec.attribute92 := rec.attribute92;
1238 l_newtx_rec.attribute93 := rec.attribute93;
1239 l_newtx_rec.attribute94 := rec.attribute94;
1240 l_newtx_rec.attribute95 := rec.attribute95;
1241 l_newtx_rec.attribute96 := rec.attribute96;
1242 l_newtx_rec.attribute97 := rec.attribute97;
1243 l_newtx_rec.attribute98 := rec.attribute98;
1244 l_newtx_rec.attribute99 := rec.attribute99;
1245 l_newtx_rec.attribute100 := rec.attribute100;
1246 l_newtx_rec.source_doc_type := rec.source_doc_type;
1247 l_newtx_rec.source_trx_number := rec.source_trx_number;
1248 l_newtx_rec.upside_amount := rec.upside_amount;
1249 l_newtx_rec.upside_quantity := rec.upside_quantity;
1250 l_newtx_rec.uom_code := rec.uom_code;
1251 l_newtx_rec.forecast_id := rec.forecast_id;
1252 l_newtx_rec.adj_comm_lines_api_id := rec.comm_lines_api_id;
1253 l_newtx_rec.invoice_number := rec.invoice_number;
1254 l_newtx_rec.invoice_date := rec.invoice_date;
1255 l_newtx_rec.order_number := rec.order_number;
1256 l_newtx_rec.order_date := rec.booked_date;
1257 l_newtx_rec.line_number := rec.line_number;
1258 l_newtx_rec.customer_id := rec.customer_id;
1259 l_newtx_rec.bill_to_address_id := rec.bill_to_address_id;
1260 l_newtx_rec.ship_to_address_id := rec.ship_to_address_id;
1261 l_newtx_rec.bill_to_contact_id := rec.bill_to_contact_id;
1262 l_newtx_rec.ship_to_contact_id := rec.ship_to_contact_id;
1263 l_newtx_rec.load_status := 'UNLOADED';
1264 l_newtx_rec.revenue_type := rec.revenue_type;
1265 l_newtx_rec.adjust_rollup_flag := rec.adjust_rollup_flag;
1266 l_newtx_rec.adjust_date := rec.adjust_date;
1267 l_newtx_rec.adjusted_by := rec.adjusted_by;
1268 l_newtx_rec.adjust_status := p_adjust_status;
1269 l_newtx_rec.adjust_comments := rec.adjust_comments;
1270 l_newtx_rec.type := rec.type;
1271 l_newtx_rec.pre_processed_code := rec.pre_processed_code;
1272 l_newtx_rec.comp_group_id := rec.comp_group_id;
1273 l_newtx_rec.srp_plan_assign_id := rec.srp_plan_assign_id;
1274 l_newtx_rec.role_id := rec.role_id;
1275 l_newtx_rec.sales_channel := rec.sales_channel;
1276 l_newtx_rec.split_pct := rec.split_pct;
1277 l_newtx_rec.split_status := rec.split_status;
1278 l_newtx_rec.commission_amount := rec.commission_amount;
1279 --
1280
1281 /* Added for crediting bug*/
1282 l_newtx_rec.terr_id := rec.terr_id;
1283 l_newtx_rec.terr_name := rec.terr_name;
1284 l_newtx_rec.preserve_credit_override_flag := rec.preserve_credit_override_flag;
1285
1286 --Added for Crediting bug
1287 IF(rec.terr_id IS NOT NULL)
1288 THEN
1289 l_newtx_rec.preserve_credit_override_flag := 'Y';
1290 l_newtx_rec.terr_id := -999;
1291 END IF;
1292
1293
1294 -- Update this record with new split information.
1295 IF (p_called_from = 'MASS') THEN
1296 l_newtx_rec.direct_salesrep_number := p_to_salesrep_number;
1297 l_newtx_rec.direct_salesrep_id := p_to_salesrep_id;
1298 l_newtx_rec.invoice_number := rec.invoice_number;
1299 l_newtx_rec.line_number := rec.line_number;
1300 l_newtx_rec.revenue_type := rec.revenue_type;
1301 l_newtx_rec.split_pct := rec.split_pct;
1302 --
1303 cn_get_tx_data_pub.insert_api_record(
1304 p_api_version => p_api_version,
1305 p_init_msg_list => p_init_msg_list,
1306 p_validation_level => p_validation_level,
1307 p_action => 'UPDATE',
1308 p_newtx_rec => l_newtx_rec,
1309 x_api_id => l_comm_lines_api_id,
1310 x_return_status => x_return_status,
1311 x_msg_count => x_msg_count,
1312 x_msg_data => x_msg_data,
1313 x_loading_status => x_loading_status);
1314 -- codeCheck: I need to handle the return_status
1315 ELSE
1316 FOR i IN p_new_data.FIRST..p_new_data.LAST
1317 LOOP
1318 l_newtx_rec.direct_salesrep_number := p_new_data(i).direct_salesrep_number;
1319 l_newtx_rec.direct_salesrep_id := p_new_data(i).salesrep_id;
1320 l_newtx_rec.invoice_number := p_new_data(i).invoice_number;
1321 l_newtx_rec.line_number := p_new_data(i).line_number;
1322 l_newtx_rec.revenue_type := p_new_data(i).revenue_type;
1323 l_newtx_rec.split_pct := p_new_data(i).split_pct;
1324 -- Create a record in the cn_comm_lines_api table using this record.
1325 IF (p_called_from = 'SPLIT') THEN
1326 l_newtx_rec.transaction_amount := ROUND((rec.transaction_amount*
1327 p_new_data(i).split_pct)/100,2);
1328 END IF;
1329 cn_get_tx_data_pub.insert_api_record(
1330 p_api_version => p_api_version,
1331 p_init_msg_list => p_init_msg_list,
1332 p_validation_level => p_validation_level,
1333 p_action => 'UPDATE',
1334 p_newtx_rec => l_newtx_rec,
1335 x_api_id => l_comm_lines_api_id,
1336 x_return_status => x_return_status,
1337 x_msg_count => x_msg_count,
1338 x_msg_data => x_msg_data,
1339 x_loading_status => x_loading_status);
1340 --
1341 END LOOP;
1342 END IF;
1343
1344 /* Added for Crediting Bug */
1345
1346 /*cn_get_tx_data_pub.update_credit_credentials(
1347 rec.comm_lines_api_id,
1348 rec.terr_id,
1349 rec.org_id,
1350 rec.adjusted_by
1351 );*/
1352
1353 --
1354 END LOOP;
1355 --
1356 END LOOP;
1357 -- Create new records in the same table.
1358 EXCEPTION
1359 WHEN FND_API.G_EXC_ERROR THEN
1360 ROLLBACK TO update_credit_memo;
1361 x_return_status := FND_API.G_RET_STS_ERROR ;
1362 FND_MSG_PUB.Count_And_Get(
1363 p_count => x_msg_count ,
1364 p_data => x_msg_data ,
1365 p_encoded => FND_API.G_FALSE);
1366 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1367 ROLLBACK TO update_credit_memo;
1368 x_loading_status := 'UNEXPECTED_ERR';
1369 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1370 FND_MSG_PUB.Count_And_Get(
1371 p_count => x_msg_count ,
1372 p_data => x_msg_data ,
1373 p_encoded => FND_API.G_FALSE);
1374 WHEN OTHERS THEN
1375 ROLLBACK TO update_credit_memo;
1376 x_loading_status := 'UNEXPECTED_ERR';
1377 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1378 IF FND_MSG_PUB.Check_Msg_Level(
1379 FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1380 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
1381 END IF;
1382 FND_MSG_PUB.Count_And_Get(
1383 p_count => x_msg_count ,
1384 p_data => x_msg_data ,
1385 p_encoded => FND_API.G_FALSE);
1386 END;
1387 --
1388 PROCEDURE update_mass_invoices (
1389 p_api_version IN NUMBER,
1390 p_init_msg_list IN VARCHAR2:= FND_API.G_TRUE,
1391 p_validation_level IN VARCHAR2:= FND_API.G_VALID_LEVEL_FULL,
1392 p_salesrep_id IN NUMBER := FND_API.G_MISS_NUM,
1393 p_pr_date_to IN DATE := FND_API.G_MISS_DATE,
1394 p_pr_date_from IN DATE := FND_API.G_MISS_DATE,
1395 p_calc_status IN VARCHAR2:= FND_API.G_MISS_CHAR,
1396 p_invoice_num IN VARCHAR2:= FND_API.G_MISS_CHAR,
1397 p_order_num IN NUMBER := FND_API.G_MISS_NUM,
1398 p_srch_attr_rec IN cn_get_tx_data_pub.adj_rec_type,
1399 p_to_salesrep_id IN NUMBER := FND_API.G_MISS_NUM,
1400 p_to_salesrep_number IN VARCHAR2:= FND_API.G_MISS_CHAR,
1401 x_return_status OUT NOCOPY VARCHAR2,
1402 x_msg_count OUT NOCOPY NUMBER,
1403 x_msg_data OUT NOCOPY VARCHAR2,
1404 x_loading_status OUT NOCOPY VARCHAR2,
1405 x_existing_data OUT NOCOPY invoice_tbl) IS
1406 --
1407 -- Local variables
1408 --
1409 l_api_name CONSTANT VARCHAR2(30) := 'update_mass_invoices';
1410 l_api_version CONSTANT NUMBER := 1.0;
1411 l_api_query_flag CHAR(1) := 'Y';
1412 l_sql VARCHAR2(10000);
1413 l_handle INTEGER;
1414 l_return INTEGER;
1415 l_counter NUMBER := 0;
1416 l_direct_salesrep_id NUMBER;
1417 l_invoice_number VARCHAR2(20);
1418 l_line_number NUMBER;
1419 l_revenue_type VARCHAR2(15);
1420 l_split_pct NUMBER;
1421 l_salesrep_number VARCHAR2(30);
1422 l_comm_lines_api_id NUMBER;
1423
1424 --Added for Crediting
1425 l_terr_id NUMBER;
1426 l_keep_flag VARCHAR2(1);
1427 -- PL/SQL tables and records
1428 l_existing_data invoice_tbl;
1429 l_new_data invoice_tbl;
1430 --l_invoice_tbl invoice_tbl;
1431
1432 BEGIN
1433 -- Standard Start of API savepoint
1434 SAVEPOINT update_mass_invoices;
1435 -- Standard call to check for call compatibility.
1436 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
1437 p_api_version ,
1438 l_api_name,
1439 G_PKG_NAME ) THEN
1440 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1441 END IF;
1442 -- Initialize message list if p_init_msg_list is set to TRUE.
1443 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1444 FND_MSG_PUB.initialize;
1445 END IF;
1446 -- Initialize API return status to success
1447 x_return_status := FND_API.G_RET_STS_SUCCESS;
1448 x_loading_status := 'CN_INSERTED';
1449 -- API body
1450 l_handle := DBMS_SQL.open_cursor;
1451 l_sql :=
1452 'SELECT '||
1453 'CCH.direct_salesrep_id,CCH.invoice_number,CCH.line_number, '||
1454 'CCH.revenue_type,CCH.split_pct,RSD.employee_number, CCH.comm_lines_api_id, '||
1455 --Modified for Crediting Bug
1456 'API.terr_id, API.preserve_credit_override_flag '||
1457 'FROM cn_period_statuses CPSP, cn_salesreps RSD, cn_commission_headers CCH, '||
1458 'cn_lookups CLT, cn_lookups CLR, cn_lookups CLS, cn_lookups CLRV, '||
1459 'cn_lookups CLAD, cn_revenue_classes CNR, cn_quotas CQ, cn_trx_batches CTB, '||
1460 -- Modified for Crediting Bug
1461 'cn_comm_lines_api API '||
1462 'WHERE CCH.direct_salesrep_id = RSD.salesrep_id '||
1463 --Modified for Crediting Bug
1464 'AND CCH.comm_lines_api_id = API.comm_lines_api_id(+)'||
1465 'AND CCH.org_id = API.org_id(+)'||
1466 'AND CCH.processed_period_id = CPSP.period_id '||
1467 'AND CCH.status = CLS.lookup_code(+) '||
1468 'AND CLS.lookup_type (+)= ''TRX_STATUS'' '||
1469 'AND CCH.reason_code = CLR.lookup_code(+) '||
1470 'AND CLR.lookup_type (+)= ''ADJUSTMENT_REASON'' '||
1471 'AND CCH.trx_type = CLT.lookup_code(+) '||
1472 'AND CLT.lookup_type (+)= ''TRX TYPES'' '||
1473 'AND CCH.revenue_type = CLRV.lookup_code (+) '||
1474 'AND CLRV.lookup_type (+) = ''REVENUE_TYPE'' '||
1475 'AND CCH.adjust_status = CLAD.lookup_code (+) '||
1476 'AND CLAD.lookup_type (+) = ''ADJUST_STATUS'' '||
1477 'AND CCH.quota_id = CQ.quota_id(+) '||
1478 'AND CCH.revenue_class_id = CNR.revenue_class_id(+) '||
1479 'AND CCH.trx_batch_id = CTB.trx_batch_id(+) '||
1480 'AND CCH.trx_type = ''INV'' '||
1481 'AND ((CCH.adjust_status NOT IN (''REVERSAL'',''FROZEN'')) '||
1482 'OR (CCH.adjust_status IS NULL)) ';
1483 IF (p_salesrep_id <> FND_API.G_MISS_NUM) THEN
1484 l_sql := l_sql|| ' AND CCH.direct_salesrep_id = :p_salesrep_id';
1485 END IF;
1486 IF (p_pr_date_from <> FND_API.G_MISS_DATE) THEN
1487 l_sql := l_sql|| ' AND CCH.processed_date >= :p_pr_date_from';
1488 END IF;
1489 IF (p_pr_date_to <> FND_API.G_MISS_DATE) THEN
1490 l_sql := l_sql|| ' AND CCH.processed_date <= :p_pr_date_to';
1491 END IF;
1492 IF (p_invoice_num <> FND_API.G_MISS_CHAR) THEN
1493 l_sql := l_sql|| ' AND CCH.invoice_number LIKE :p_invoice_num';
1494 END IF;
1495 IF (p_order_num <> FND_API.G_MISS_NUM AND p_order_num <> 0) THEN
1496 l_sql := l_sql|| ' AND CCH.order_number = :p_order_num';
1497 END IF;
1498 IF (p_calc_status <> 'ALL') THEN
1499 l_sql := l_sql|| ' AND CCH.status = :p_calc_status';
1500 l_api_query_flag := 'N';
1501 END IF;
1502
1503 --Added for Crediting
1504 l_terr_id := p_srch_attr_rec.terr_id;
1505 IF (l_terr_id = 0) THEN
1506 l_sql := l_sql|| ' AND API.terr_id IS NOT NULL';
1507 END IF;
1508 IF (l_terr_id = 1) THEN
1509 l_sql := l_sql|| ' AND API.terr_id IS NULL';
1510 END IF;
1511
1512 l_keep_flag := p_srch_attr_rec.preserve_credit_override_flag;
1513 IF (l_keep_flag <> FND_API.G_MISS_CHAR AND l_keep_flag IS NOT NULL) THEN
1514 l_sql := l_sql|| ' AND API.preserve_credit_override_flag = :l_keep_flag';
1515 END IF;
1516
1517 l_sql := l_sql||' GROUP BY CCH.direct_salesrep_id, '||
1518 'CCH.invoice_number,CCH.line_number, '||
1519 'CCH.revenue_type,CCH.split_pct,RSD.employee_number, '||
1520 'CCH.comm_lines_api_id ';
1521 IF (l_api_query_flag = 'Y') THEN
1522 l_sql := l_sql||' UNION ALL '||
1523 'SELECT CCLA.salesrep_id,CCLA.invoice_number,CCLA.line_number, '||
1524 'CCLA.revenue_type,CCLA.split_pct,RSD.employee_number, CCLA.comm_lines_api_id, '||
1525 --Modified for Crediting Bug
1526 'CCLA.terr_id, CCLA.preserve_credit_override_flag '||
1527 'FROM cn_comm_lines_api CCLA, '||
1528 'cn_period_statuses CPSP, cn_salesreps RSD, '||
1529 'cn_revenue_classes CNR, cn_lookups CLT, '||
1530 'cn_lookups CLRV, cn_lookups CLAD,cn_lookups CLR '||
1531 'WHERE RSD.salesrep_id = CCLA.salesrep_id '||
1532 'AND CCLA.processed_period_id = CPSP.period_id '||
1533 'AND CCLA.revenue_class_id = CNR.revenue_class_id(+) '||
1534 'AND CCLA.reason_code = CLR.lookup_code(+) '||
1535 'AND CLR.lookup_type (+)= ''ADJUSTMENT_REASON'' '||
1536 'AND CCLA.trx_type = CLT.lookup_code '||
1537 'AND CLT.lookup_type = ''TRX TYPES'' '||
1538 'AND CCLA.revenue_type = CLRV.lookup_code(+) '||
1539 'AND CLRV.lookup_type (+)= ''REVENUE_TYPE'' '||
1540 'AND CCLA.adjust_status = CLAD.lookup_code(+) '||
1541 'AND CLAD.lookup_type (+)= ''ADJUST_STATUS'' '||
1542 'AND nvl(CCLA.load_status,''X'') <> ''LOADED'' '||
1543 'AND CCLA.trx_type = ''INV'' '||
1544 'AND ((CCLA.adjust_status NOT IN (''REVERSAL'',''FROZEN'',''SCA_PENDING'')) '||
1545 'OR (CCLA.adjust_status IS NULL)) ';
1546 IF (p_salesrep_id <> FND_API.G_MISS_NUM) THEN
1547 l_sql := l_sql|| ' AND CCLA.salesrep_id = :p_salesrep_id';
1548 END IF;
1549 IF (p_pr_date_from <> FND_API.G_MISS_DATE) THEN
1550 l_sql := l_sql|| ' AND CCLA.processed_date >= :p_pr_date_from';
1551 END IF;
1552 IF (p_pr_date_to <> FND_API.G_MISS_DATE) THEN
1553 l_sql := l_sql|| ' AND CCLA.processed_date <= :p_pr_date_to';
1554 END IF;
1555 IF (p_invoice_num <> FND_API.G_MISS_CHAR) THEN
1556 l_sql := l_sql|| ' AND CCLA.invoice_number LIKE :p_invoice_num';
1557 END IF;
1558 IF (p_order_num <> FND_API.G_MISS_NUM AND p_order_num <> 0) THEN
1559 l_sql := l_sql|| ' AND CCLA.order_number = :p_order_num';
1560 END IF;
1561
1562 --Added for Crediting
1563 l_terr_id := p_srch_attr_rec.terr_id;
1564 IF (l_terr_id = 0) THEN
1565 l_sql := l_sql|| ' AND CCLA.terr_id IS NOT NULL';
1566 END IF;
1567 IF (l_terr_id = 1) THEN
1568 l_sql := l_sql|| ' AND CCLA.terr_id IS NULL';
1569 END IF;
1570
1571 l_keep_flag := p_srch_attr_rec.preserve_credit_override_flag;
1572 IF (l_keep_flag <> FND_API.G_MISS_CHAR AND l_keep_flag IS NOT NULL) THEN
1573 l_sql := l_sql|| ' AND CCLA.preserve_credit_override_flag = :l_keep_flag';
1574 END IF;
1575
1576 l_sql := l_sql||' GROUP BY CCLA.salesrep_id, '||
1577 'CCLA.invoice_number,CCLA.line_number, '||
1578 'CCLA.revenue_type,CCLA.split_pct,RSD.employee_number, '||
1579 'CCLA.comm_lines_api_id ';
1580 END IF;
1581 --insert into rao_debug values(l_sql);
1582 --commit;
1583 DBMS_SQL.PARSE(l_handle,l_sql,DBMS_SQL.NATIVE);
1584 IF (p_salesrep_id <> FND_API.G_MISS_NUM) THEN
1585 DBMS_SQL.BIND_VARIABLE(l_handle,'p_salesrep_id',p_salesrep_id);
1586 END IF;
1587 IF (p_pr_date_from <> FND_API.G_MISS_DATE) THEN
1588 DBMS_SQL.BIND_VARIABLE(l_handle,'p_pr_date_from',p_pr_date_from);
1589 END IF;
1590 IF (p_pr_date_to <> FND_API.G_MISS_DATE) THEN
1591 DBMS_SQL.BIND_VARIABLE(l_handle,'p_pr_date_to',p_pr_date_to);
1592 END IF;
1593 IF (p_invoice_num <> FND_API.G_MISS_CHAR) THEN
1594 DBMS_SQL.BIND_VARIABLE(l_handle,'p_invoice_num',p_invoice_num);
1595 END IF;
1596 IF (p_order_num <> FND_API.G_MISS_NUM AND p_order_num <> 0) THEN
1597 DBMS_SQL.BIND_VARIABLE(l_handle,'p_order_num',p_order_num);
1598 END IF;
1599 IF (p_calc_status <> 'ALL') THEN
1600 DBMS_SQL.BIND_VARIABLE(l_handle,'p_calc_status',p_calc_status);
1601 END IF;
1602
1603 -- Added for Crediting
1604 IF (l_keep_flag <> FND_API.G_MISS_CHAR AND l_keep_flag <> NULL) THEN
1605 DBMS_SQL.BIND_VARIABLE(l_handle,'l_keep_flag',l_keep_flag);
1606 END IF;
1607
1608
1609 DBMS_SQL.DEFINE_COLUMN (l_handle,1,l_direct_salesrep_id);
1610 DBMS_SQL.DEFINE_COLUMN (l_handle,2,l_invoice_number,20);
1611 DBMS_SQL.DEFINE_COLUMN (l_handle,3,l_line_number);
1612 DBMS_SQL.DEFINE_COLUMN (l_handle,4,l_revenue_type,15);
1613 DBMS_SQL.DEFINE_COLUMN (l_handle,5,l_split_pct);
1614 DBMS_SQL.DEFINE_COLUMN (l_handle,6,l_salesrep_number,30);
1615 DBMS_SQL.DEFINE_COLUMN (l_handle,7,l_comm_lines_api_id);
1616 l_return := DBMS_SQL.execute (l_handle);
1617 LOOP
1618 IF (dbms_sql.fetch_rows(l_handle) > 0) THEN
1619 l_counter := l_counter + 1;
1620 DBMS_SQL.COLUMN_VALUE (l_handle,1,l_direct_salesrep_id);
1621 DBMS_SQL.COLUMN_VALUE (l_handle,2,l_invoice_number);
1622 DBMS_SQL.COLUMN_VALUE (l_handle,3,l_line_number);
1623 DBMS_SQL.COLUMN_VALUE (l_handle,4,l_revenue_type);
1624 DBMS_SQL.COLUMN_VALUE (l_handle,5,l_split_pct);
1625 DBMS_SQL.COLUMN_VALUE (l_handle,6,l_salesrep_number);
1626 DBMS_SQL.COLUMN_VALUE (l_handle,7,l_comm_lines_api_id);
1627 -- Creating a table of to-be-deleted records.
1628 l_existing_data(l_counter).salesrep_id := l_direct_salesrep_id;
1629 l_existing_data(l_counter).invoice_number := l_invoice_number;
1630 l_existing_data(l_counter).line_number := l_line_number;
1631 l_existing_data(l_counter).revenue_type := l_revenue_type;
1632 l_existing_data(l_counter).split_pct := l_split_pct;
1633 l_existing_data(l_counter).direct_salesrep_number
1634 := l_salesrep_number;
1635 l_existing_data(l_counter).comm_lines_api_id := l_comm_lines_api_id;
1636 ELSE
1637 EXIT;
1638 END IF;
1639 END LOOP;
1640 DBMS_SQL.close_cursor(l_handle);
1641 -- A dummy PL/SQL table need to be created with NULL values to make a
1642 -- call to update_invoice_changes procedure.
1643 l_new_data(1).salesrep_id := NULL;
1644 l_new_data(1).invoice_number := NULL;
1645 l_new_data(1).line_number := NULL;
1646 l_new_data(1).revenue_type := NULL;
1647 l_new_data(1).split_pct := NULL;
1648 l_new_data(1).direct_salesrep_number := NULL;
1649 IF ((l_existing_data.COUNT <> 0) AND (l_new_data.COUNT <> 0)) THEN
1650 cn_invoice_changes_pvt.update_invoice_changes(
1651 p_api_version => l_api_version,
1652 p_validation_level => p_validation_level,
1653 p_existing_data => l_existing_data,
1654 p_new_data => l_new_data,
1655 p_exist_data_check => 'Y',
1656 p_new_data_check => 'N',
1657 x_return_status => x_return_status,
1658 x_msg_count => x_msg_count,
1659 x_msg_data => x_msg_data,
1660 x_loading_status => x_loading_status);
1661 END IF;
1662 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1663 FND_MESSAGE.Set_Name('CN', 'CN_UPD_INV_CHANGES');
1664 FND_MSG_PUB.Add;
1665 x_loading_status := 'CN_UPD_INV_CHANGES';
1666 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1667 END IF;
1668 x_existing_data := l_existing_data;
1669 EXCEPTION
1670 WHEN FND_API.G_EXC_ERROR THEN
1671 ROLLBACK TO update_mass_invoices;
1672 x_return_status := FND_API.G_RET_STS_ERROR ;
1673 FND_MSG_PUB.Count_And_Get(
1674 p_count => x_msg_count ,
1675 p_data => x_msg_data ,
1676 p_encoded => FND_API.G_FALSE);
1677 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1678 ROLLBACK TO update_mass_invoices;
1679 x_loading_status := 'UNEXPECTED_ERR';
1680 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1681 FND_MSG_PUB.Count_And_Get(
1682 p_count => x_msg_count ,
1683 p_data => x_msg_data ,
1684 p_encoded => FND_API.G_FALSE);
1685 WHEN OTHERS THEN
1686 ROLLBACK TO update_mass_invoices;
1687 x_loading_status := 'UNEXPECTED_ERR';
1688 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1689 IF FND_MSG_PUB.Check_Msg_Level(
1690 FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1691 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
1692 END IF;
1693 FND_MSG_PUB.Count_And_Get(
1694 p_count => x_msg_count ,
1695 p_data => x_msg_data ,
1696 p_encoded => FND_API.G_FALSE);
1697 END;
1698 --
1699 PROCEDURE capture_deal_invoice(
1700 p_api_version IN NUMBER,
1701 p_init_msg_list IN VARCHAR2:= FND_API.G_TRUE,
1702 p_validation_level IN VARCHAR2:= FND_API.G_VALID_LEVEL_FULL,
1703 p_trx_type IN VARCHAR2,
1704 p_split_nonrevenue_line IN VARCHAR2,
1705 p_invoice_number IN VARCHAR2,
1706 p_org_id IN NUMBER,
1707 p_split_data_tbl IN cn_get_tx_data_pub.split_data_tbl,
1708 x_deal_data_tbl OUT NOCOPY cn_invoice_changes_pvt.deal_data_tbl,
1709 x_return_status OUT NOCOPY VARCHAR2,
1710 x_msg_count OUT NOCOPY NUMBER,
1711 x_msg_data OUT NOCOPY VARCHAR2,
1712 x_loading_status OUT NOCOPY VARCHAR2) IS
1713 CURSOR c1 IS
1714 SELECT comm_lines_api_id,
1715 invoice_number,
1716 line_number,
1717 revenue_type
1718 FROM cn_comm_lines_api_all api
1719 WHERE api.invoice_number = p_invoice_number
1720 AND api.trx_type = p_trx_type
1721 AND api.org_id = p_org_id
1722 AND api.load_status NOT IN ( 'LOADED', 'FILTERED') -- vensrini Buf fix 4202682
1723 AND (api.adjust_status NOT IN ('FROZEN','REVERSAL','SCA_PENDING') OR
1724 api.adjust_status IS NULL)
1725 UNION ALL
1726 SELECT comm_lines_api_id,
1727 invoice_number,
1728 line_number,
1729 revenue_type
1730 FROM cn_commission_headers_all ch
1731 WHERE ch.invoice_number = p_invoice_number
1732 AND ch.trx_type = p_trx_type
1733 AND ch.org_id = p_org_id
1734 AND (ch.adjust_status NOT IN ('FROZEN','REVERSAL') OR
1735 ch.adjust_status IS NULL);
1736 CURSOR c2 IS
1737 SELECT invoice_change_id, revenue_type
1738 FROM cn_invoice_changes
1739 WHERE invoice_number = p_invoice_number
1740 AND org_id = p_org_id; -- vensrini
1741
1742 --
1743 -- Local variables
1744 l_api_name CONSTANT VARCHAR2(30) := 'capture_deal_invoice';
1745 l_api_version CONSTANT NUMBER := 1.0;
1746 l_invoice_change_id NUMBER;
1747 l_counter NUMBER := 0;
1748 -- PL/SQL tables and records
1749 l_insert_rec cn_invoice_changes_pkg.invoice_changes_all_rec_type;
1750 --
1751 BEGIN
1752 -- Standard Start of API savepoint
1753 SAVEPOINT capture_deal_invoice;
1754 -- Standard call to check for call compatibility.
1755 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
1756 p_api_version ,
1757 l_api_name,
1758 G_PKG_NAME ) THEN
1759 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1760 END IF;
1761 -- Initialize message list if p_init_msg_list is set to TRUE.
1762 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1763 FND_MSG_PUB.initialize;
1764 END IF;
1765 -- Initialize API return status to success
1766 x_return_status := FND_API.G_RET_STS_SUCCESS;
1767 x_loading_status := 'CN_INSERTED';
1768 -- API body
1769 IF ((g_track_invoice = 'Y') AND (p_trx_type = 'INV'))THEN
1770 FOR c2_rec IN c2
1771 LOOP
1772 IF ((c2_rec.revenue_type = 'NONREVENUE') AND
1773 (p_split_nonrevenue_line = 'N'))THEN
1774 NULL;
1775 ELSE
1776 cn_invoice_changes_pkg.delete_row(c2_rec.invoice_change_id);
1777 END IF;
1778 END LOOP;
1779 END IF;
1780 FOR c1_rec IN c1
1781 LOOP
1782 IF ((c1_rec.revenue_type = 'NONREVENUE') AND
1783 (p_split_nonrevenue_line = 'N'))THEN
1784 NULL;
1785 ELSE
1786 l_counter := l_counter + 1;
1787 x_deal_data_tbl(l_counter).comm_lines_api_id := c1_rec.comm_lines_api_id;
1788 x_deal_data_tbl(l_counter).invoice_number := c1_rec.invoice_number;
1789 x_deal_data_tbl(l_counter).line_number := c1_rec.line_number;
1790 END IF;
1791
1792 END LOOP;
1793 EXCEPTION
1794 WHEN FND_API.G_EXC_ERROR THEN
1795 ROLLBACK TO capture_deal_invoice;
1796 x_return_status := FND_API.G_RET_STS_ERROR ;
1797 FND_MSG_PUB.Count_And_Get(
1798 p_count => x_msg_count ,
1799 p_data => x_msg_data ,
1800 p_encoded => FND_API.G_FALSE);
1801 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1802 ROLLBACK TO capture_deal_invoice;
1803 x_loading_status := 'UNEXPECTED_ERR';
1804 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1805 FND_MSG_PUB.Count_And_Get(
1806 p_count => x_msg_count ,
1807 p_data => x_msg_data ,
1808 p_encoded => FND_API.G_FALSE);
1809 WHEN OTHERS THEN
1810 ROLLBACK TO capture_deal_invoice;
1811 x_loading_status := 'UNEXPECTED_ERR';
1812 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1813 IF FND_MSG_PUB.Check_Msg_Level(
1814 FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1815 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
1816 END IF;
1817 FND_MSG_PUB.Count_And_Get(
1818 p_count => x_msg_count ,
1819 p_data => x_msg_data ,
1820 p_encoded => FND_API.G_FALSE);
1821 END;
1822 --
1823 /*-----------------------------------------------------------------------------
1824 Batch Program Logic:
1825 o Collect the new invoices split information after collections get the data
1826 into cn_comm_lines_api table using cursor c1
1827 o Collect the new CMs matching with the stored invoice split data based on
1828 invoice/line/revenue type(group by) using cursor c2
1829 o Based on c2, collect the individual transactions from cn_comm_lines_api
1830 table (using api_cur) and NEGATE them.
1831 o Based on c2, collect the invoice split data from cn_invoice_changes table
1832 and recreate the new records in the cn_comm_lines_api table.
1833 -----------------------------------------------------------------------------*/
1834 PROCEDURE invoice_split_batch(
1835 x_errbuf OUT NOCOPY VARCHAR2,
1836 x_retcode OUT NOCOPY NUMBER) IS
1837 --
1838 CURSOR c1 IS
1839 SELECT api.comm_lines_api_id,
1840 api.salesrep_id,
1841 api.invoice_number,
1842 api.line_number,
1843 api.revenue_type,
1844 api.split_pct
1845 FROM cn_comm_lines_api api
1846 WHERE trx_type = 'INV'
1847 AND load_status = 'UNLOADED'
1848 AND adjust_status IS NULL;
1849 -- AND api.processed_date between SYSDATE-1 AND SYSDATE;
1850 --
1851 CURSOR c2(
1852 l_trx_type VARCHAR2) IS
1853 SELECT api.invoice_number,
1854 api.line_number,
1855 api.revenue_type,
1856 sum(api.transaction_amount) transaction_amount
1857 FROM cn_comm_lines_api api
1858 WHERE EXISTS (
1859 SELECT 1
1860 FROM cn_invoice_changes inv
1861 WHERE api.invoice_number = inv.invoice_number
1862 AND api.line_number = inv.line_number
1863 AND api.revenue_type = inv.revenue_type)
1864 AND (api.split_status NOT IN ('LINKED','DELINKED') OR
1865 api.split_status IS NULL)
1866 AND trx_type = l_trx_type
1867 AND adjust_status IS NULL
1868 GROUP BY api.invoice_number,
1869 api.line_number,
1870 api.revenue_type;
1871 --
1872 CURSOR api_cur(
1873 l_invoice_number VARCHAR2,
1874 l_line_number NUMBER,
1875 l_revenue_type VARCHAR2,
1876 l_trx_type VARCHAR2) IS
1877 SELECT api.*
1878 FROM cn_comm_lines_api api
1879 WHERE api.invoice_number = l_invoice_number
1880 AND api.line_number = l_line_number
1881 AND api.revenue_type = l_revenue_type
1882 AND (api.split_status NOT IN ('LINKED','DELINKED') OR
1883 api.split_status IS NULL)
1884 AND trx_type = l_trx_type;
1885 --
1886 CURSOR inv_cur(
1887 l_invoice_number VARCHAR2,
1888 l_line_number NUMBER,
1889 l_revenue_type VARCHAR2) IS
1890 SELECT inv.*,rep.employee_number
1891 FROM cn_invoice_changes inv,
1892 cn_salesreps rep
1893 WHERE inv.salesrep_id = rep.salesrep_id
1894 AND inv.invoice_number = l_invoice_number
1895 AND inv.line_number = l_line_number
1896 AND inv.revenue_type = l_revenue_type;
1897 --
1898 -- Local variables.
1899 l_invoice_change_id NUMBER;
1900 l_counter NUMBER := 0;
1901 l_negate_counter NUMBER := 0;
1902 l_transaction_amount NUMBER;
1903 l_create VARCHAR(1) := 'N';
1904 l_comm_lines_api_id NUMBER;
1905 l_adjust_comments VARCHAR2(300);
1906 l_process_audit_id cn_process_audits.process_audit_id%TYPE;
1907 l_trx_type VARCHAR2(30);
1908 -- PL/SQL tables and records
1909 l_insert_rec cn_invoice_changes_pkg.invoice_changes_all_rec_type;
1910 l_invoice_tbl invoice_tbl;
1911 l_api_rec cn_comm_lines_api_pkg.comm_lines_api_rec_type;
1912 l_adj_tbl cn_get_tx_data_pub.adj_tbl_type;
1913 --
1914 BEGIN
1915 --
1916 cn_message_pkg.begin_batch(
1917 x_parent_proc_audit_id => NULL,
1918 x_process_audit_id => l_process_audit_id,
1919 x_request_id => fnd_global.conc_request_id,
1920 x_process_type => 'INVLOAD',
1921 p_org_id => 204
1922 );
1923 --
1924 cn_message_pkg.write(
1925 p_message_text => 'Starting Invoice Capture Batch Program',
1926 p_message_type => 'MILESTONE');
1927 --
1928 l_adjust_comments := 'Negated during Invoice Split Batch Program Execution';
1929 x_errbuf := '';
1930 x_retcode := 0;
1931 --
1932 cn_message_pkg.write(
1933 p_message_text => 'Capturing New Invoices Cursor Start',
1934 p_message_type => 'MILESTONE');
1935 --
1936 IF (g_track_invoice = 'Y') THEN
1937 FOR c1_rec in c1
1938 LOOP
1939 --
1940 l_counter := l_counter + 1;
1941 --
1942 cn_message_pkg.write(
1943 p_message_text => 'Record-'||l_counter||' '||
1944 'api_id:'||c1_rec.comm_lines_api_id||'; '||
1945 'rep:'||c1_rec.salesrep_id||'; '||
1946 'invoice:'||c1_rec.invoice_number||'; '||
1947 'line:'||c1_rec.line_number,
1948 p_message_type => 'MILESTONE');
1949 --
1950 SELECT cn_invoice_change_s.NEXTVAL
1951 INTO l_invoice_change_id
1952 FROM dual;
1953 --
1954 l_insert_rec.invoice_change_id := l_invoice_change_id;
1955 l_insert_rec.salesrep_id := c1_rec.salesrep_id;
1956 l_insert_rec.invoice_number := c1_rec.invoice_number;
1957 l_insert_rec.line_number := c1_rec.line_number;
1958 l_insert_rec.revenue_type := c1_rec.revenue_type;
1959 l_insert_rec.split_pct := c1_rec.split_pct;
1960 l_insert_rec.comm_lines_api_id := c1_rec.comm_lines_api_id;
1961 --
1962 cn_invoice_changes_pkg.insert_row(
1963 p_invoice_changes_all_rec => l_insert_rec);
1964 --
1965 UPDATE cn_comm_lines_api
1966 SET adjust_status = 'INVLOAD'
1967 WHERE comm_lines_api_id = c1_rec.comm_lines_api_id;
1968 --
1969 END LOOP;
1970 --
1971 cn_message_pkg.write(
1972 p_message_text => 'Total Number Of New Invoices Captured: '||l_counter,
1973 p_message_type => 'MILESTONE');
1974 --
1975 FOR l_cm_pmt_count IN 1..2
1976 LOOP
1977 IF (l_cm_pmt_count = 1) THEN
1978 l_trx_type := 'CM';
1979 ELSE
1980 l_trx_type := 'PMT';
1981 END IF;
1982 l_counter := 0;
1983 l_negate_counter := 0;
1984 --
1985 cn_message_pkg.write(
1986 p_message_text => 'Capturing '||l_trx_type||' Cursor Start',
1987 p_message_type => 'MILESTONE');
1988 --
1989 FOR c2_rec IN c2(l_trx_type)
1990 LOOP
1991 FOR api_rec IN api_cur(c2_rec.invoice_number,
1992 c2_rec.line_number,
1993 c2_rec.revenue_type,
1994 l_trx_type)
1995 LOOP
1996 --
1997 l_negate_counter := l_negate_counter + 1;
1998 --
1999 cn_message_pkg.write(
2000 p_message_text =>
2001 'NEGATING '||l_trx_type||' : Record-'||l_negate_counter||' '||
2002 'api_id:'||api_rec.comm_lines_api_id||'; '||
2003 'rep:'||api_rec.salesrep_id||'; '||
2004 'invoice:'||api_rec.invoice_number||'; '||
2005 'line:'||api_rec.line_number,
2006 p_message_type => 'MILESTONE');
2007 --
2008 cn_adjustments_pkg.api_negate_record(
2009 x_comm_lines_api_id => api_rec.comm_lines_api_id,
2010 x_adjusted_by => get_adjusted_by,
2011 x_adjust_comments => l_adjust_comments);
2012 END LOOP;
2013 FOR inv_rec IN inv_cur(c2_rec.invoice_number,
2014 c2_rec.line_number,
2015 c2_rec.revenue_type)
2016 LOOP
2017 --
2018 l_counter := l_counter + 1;
2019 --
2020 cn_get_tx_data_pub.get_api_data(
2021 p_comm_lines_api_id => inv_rec.comm_lines_api_id,
2022 x_adj_tbl => l_adj_tbl);
2023 --
2024 IF (l_adj_tbl.COUNT > 0) THEN
2025 --
2026 SELECT cn_comm_lines_api_s.NEXTVAL
2027 INTO l_comm_lines_api_id
2028 FROM sys.dual;
2029 --
2030 l_api_rec.comm_lines_api_id := l_comm_lines_api_id;
2031 l_api_rec.salesrep_id := inv_rec.salesrep_id;
2032 l_api_rec.invoice_number := inv_rec.invoice_number;
2033 l_api_rec.line_number := inv_rec.line_number;
2034 l_api_rec.revenue_type := inv_rec.revenue_type;
2035 l_api_rec.split_pct := inv_rec.split_pct;
2036 l_api_rec.employee_number := inv_rec.employee_number;
2037 l_api_rec.split_status := 'LINKED';
2038 l_api_rec.transaction_amount := c2_rec.transaction_amount*
2039 (NVL(inv_rec.split_pct,0)/100);
2040 l_api_rec.adjust_status := 'SPLIT';
2041 l_api_rec.load_status := 'UNLOADED';
2042 l_api_rec.adj_comm_lines_api_id := NULL;
2043 l_api_rec.processed_date := l_adj_tbl(1).processed_date;
2044 l_api_rec.processed_period_id := l_adj_tbl(1).processed_period_id;
2045 l_api_rec.trx_type := l_trx_type;
2046 l_api_rec.revenue_class_id := l_adj_tbl(1).revenue_class_id;
2047 l_api_rec.attribute_category := l_adj_tbl(1).attribute_category;
2048 l_api_rec.attribute1 := l_adj_tbl(1).attribute1;
2049 l_api_rec.attribute2 := l_adj_tbl(1).attribute2;
2050 l_api_rec.attribute3 := l_adj_tbl(1).attribute3;
2051 l_api_rec.attribute4 := l_adj_tbl(1).attribute4;
2052 l_api_rec.attribute5 := l_adj_tbl(1).attribute5;
2053 l_api_rec.attribute6 := l_adj_tbl(1).attribute6;
2054 l_api_rec.attribute7 := l_adj_tbl(1).attribute7;
2055 l_api_rec.attribute8 := l_adj_tbl(1).attribute8;
2056 l_api_rec.attribute9 := l_adj_tbl(1).attribute9;
2057 l_api_rec.attribute10 := l_adj_tbl(1).attribute10;
2058 l_api_rec.attribute11 := l_adj_tbl(1).attribute11;
2059 l_api_rec.attribute12 := l_adj_tbl(1).attribute12;
2060 l_api_rec.attribute13 := l_adj_tbl(1).attribute13;
2061 l_api_rec.attribute14 := l_adj_tbl(1).attribute14;
2062 l_api_rec.attribute15 := l_adj_tbl(1).attribute15;
2063 l_api_rec.attribute16 := l_adj_tbl(1).attribute16;
2064 l_api_rec.attribute17 := l_adj_tbl(1).attribute17;
2065 l_api_rec.attribute18 := l_adj_tbl(1).attribute18;
2066 l_api_rec.attribute19 := l_adj_tbl(1).attribute19;
2067 l_api_rec.attribute20 := l_adj_tbl(1).attribute20;
2068 l_api_rec.attribute21 := l_adj_tbl(1).attribute21;
2069 l_api_rec.attribute22 := l_adj_tbl(1).attribute22;
2070 l_api_rec.attribute23 := l_adj_tbl(1).attribute23;
2071 l_api_rec.attribute24 := l_adj_tbl(1).attribute24;
2072 l_api_rec.attribute25 := l_adj_tbl(1).attribute25;
2073 l_api_rec.attribute26 := l_adj_tbl(1).attribute26;
2074 l_api_rec.attribute27 := l_adj_tbl(1).attribute27;
2075 l_api_rec.attribute28 := l_adj_tbl(1).attribute28;
2076 l_api_rec.attribute29 := l_adj_tbl(1).attribute29;
2077 l_api_rec.attribute30 := l_adj_tbl(1).attribute30;
2078 l_api_rec.attribute31 := l_adj_tbl(1).attribute31;
2079 l_api_rec.attribute32 := l_adj_tbl(1).attribute32;
2080 l_api_rec.attribute33 := l_adj_tbl(1).attribute33;
2081 l_api_rec.attribute34 := l_adj_tbl(1).attribute34;
2082 l_api_rec.attribute35 := l_adj_tbl(1).attribute35;
2083 l_api_rec.attribute36 := l_adj_tbl(1).attribute36;
2084 l_api_rec.attribute37 := l_adj_tbl(1).attribute37;
2085 l_api_rec.attribute38 := l_adj_tbl(1).attribute38;
2086 l_api_rec.attribute39 := l_adj_tbl(1).attribute39;
2087 l_api_rec.attribute40 := l_adj_tbl(1).attribute40;
2088 l_api_rec.attribute41 := l_adj_tbl(1).attribute41;
2089 l_api_rec.attribute42 := l_adj_tbl(1).attribute42;
2090 l_api_rec.attribute43 := l_adj_tbl(1).attribute43;
2091 l_api_rec.attribute44 := l_adj_tbl(1).attribute44;
2092 l_api_rec.attribute45 := l_adj_tbl(1).attribute45;
2093 l_api_rec.attribute46 := l_adj_tbl(1).attribute46;
2094 l_api_rec.attribute47 := l_adj_tbl(1).attribute47;
2095 l_api_rec.attribute48 := l_adj_tbl(1).attribute48;
2096 l_api_rec.attribute49 := l_adj_tbl(1).attribute49;
2097 l_api_rec.attribute50 := l_adj_tbl(1).attribute50;
2098 l_api_rec.attribute51 := l_adj_tbl(1).attribute51;
2099 l_api_rec.attribute52 := l_adj_tbl(1).attribute52;
2100 l_api_rec.attribute53 := l_adj_tbl(1).attribute53;
2101 l_api_rec.attribute54 := l_adj_tbl(1).attribute54;
2102 l_api_rec.attribute55 := l_adj_tbl(1).attribute55;
2103 l_api_rec.attribute56 := l_adj_tbl(1).attribute56;
2104 l_api_rec.attribute57 := l_adj_tbl(1).attribute57;
2105 l_api_rec.attribute58 := l_adj_tbl(1).attribute58;
2106 l_api_rec.attribute59 := l_adj_tbl(1).attribute59;
2107 l_api_rec.attribute60 := l_adj_tbl(1).attribute60;
2108 l_api_rec.attribute61 := l_adj_tbl(1).attribute61;
2109 l_api_rec.attribute62 := l_adj_tbl(1).attribute62;
2110 l_api_rec.attribute63 := l_adj_tbl(1).attribute63;
2111 l_api_rec.attribute64 := l_adj_tbl(1).attribute64;
2112 l_api_rec.attribute65 := l_adj_tbl(1).attribute65;
2113 l_api_rec.attribute66 := l_adj_tbl(1).attribute66;
2114 l_api_rec.attribute67 := l_adj_tbl(1).attribute67;
2115 l_api_rec.attribute68 := l_adj_tbl(1).attribute68;
2116 l_api_rec.attribute69 := l_adj_tbl(1).attribute69;
2117 l_api_rec.attribute70 := l_adj_tbl(1).attribute70;
2118 l_api_rec.attribute71 := l_adj_tbl(1).attribute71;
2119 l_api_rec.attribute72 := l_adj_tbl(1).attribute72;
2120 l_api_rec.attribute73 := l_adj_tbl(1).attribute73;
2121 l_api_rec.attribute74 := l_adj_tbl(1).attribute74;
2122 l_api_rec.attribute75 := l_adj_tbl(1).attribute75;
2123 l_api_rec.attribute76 := l_adj_tbl(1).attribute76;
2124 l_api_rec.attribute77 := l_adj_tbl(1).attribute77;
2125 l_api_rec.attribute78 := l_adj_tbl(1).attribute78;
2126 l_api_rec.attribute79 := l_adj_tbl(1).attribute79;
2127 l_api_rec.attribute80 := l_adj_tbl(1).attribute80;
2128 l_api_rec.attribute81 := l_adj_tbl(1).attribute81;
2129 l_api_rec.attribute82 := l_adj_tbl(1).attribute82;
2130 l_api_rec.attribute83 := l_adj_tbl(1).attribute83;
2131 l_api_rec.attribute84 := l_adj_tbl(1).attribute84;
2132 l_api_rec.attribute85 := l_adj_tbl(1).attribute85;
2133 l_api_rec.attribute86 := l_adj_tbl(1).attribute86;
2134 l_api_rec.attribute87 := l_adj_tbl(1).attribute87;
2135 l_api_rec.attribute88 := l_adj_tbl(1).attribute88;
2136 l_api_rec.attribute89 := l_adj_tbl(1).attribute89;
2137 l_api_rec.attribute90 := l_adj_tbl(1).attribute90;
2138 l_api_rec.attribute91 := l_adj_tbl(1).attribute91;
2139 l_api_rec.attribute92 := l_adj_tbl(1).attribute92;
2140 l_api_rec.attribute93 := l_adj_tbl(1).attribute93;
2141 l_api_rec.attribute94 := l_adj_tbl(1).attribute94;
2142 l_api_rec.attribute95 := l_adj_tbl(1).attribute95;
2143 l_api_rec.attribute96 := l_adj_tbl(1).attribute96;
2144 l_api_rec.attribute97 := l_adj_tbl(1).attribute97;
2145 l_api_rec.attribute98 := l_adj_tbl(1).attribute98;
2146 l_api_rec.attribute99 := l_adj_tbl(1).attribute99;
2147 l_api_rec.attribute100 := l_adj_tbl(1).attribute100;
2148 l_api_rec.rollup_date := l_adj_tbl(1).rollup_date;
2149 l_api_rec.source_doc_type := l_adj_tbl(1).source_doc_type;
2150 l_api_rec.transaction_currency_code := l_adj_tbl(1).orig_currency_code;
2151 l_api_rec.exchange_rate := l_adj_tbl(1).exchange_rate;
2152 l_api_rec.trx_id := l_adj_tbl(1).trx_id;
2153 l_api_rec.trx_line_id := l_adj_tbl(1).trx_line_id;
2154 l_api_rec.trx_sales_line_id := l_adj_tbl(1).trx_sales_line_id;
2155 l_api_rec.quantity := l_adj_tbl(1).quantity;
2156 l_api_rec.source_trx_number := l_adj_tbl(1).source_trx_number;
2157 l_api_rec.discount_percentage := l_adj_tbl(1).discount_percentage;
2158 l_api_rec.margin_percentage := l_adj_tbl(1).margin_percentage;
2159 l_api_rec.customer_id := l_adj_tbl(1).customer_id;
2160 l_api_rec.order_number := l_adj_tbl(1).order_number;
2161 l_api_rec.booked_date := l_adj_tbl(1).order_date;
2162 l_api_rec.invoice_date := l_adj_tbl(1).invoice_date;
2163 l_api_rec.adjust_date := SYSDATE;
2164 l_api_rec.adjusted_by := get_adjusted_by;
2165 l_api_rec.adjust_rollup_flag := l_adj_tbl(1).adjust_rollup_flag;
2166 l_api_rec.adjust_comments := l_adj_tbl(1).adjust_comments;
2167 l_api_rec.bill_to_address_id := l_adj_tbl(1).bill_to_address_id;
2168 l_api_rec.ship_to_address_id := l_adj_tbl(1).ship_to_address_id;
2169 l_api_rec.bill_to_contact_id := l_adj_tbl(1).bill_to_contact_id;
2170 l_api_rec.ship_to_contact_id := l_adj_tbl(1).ship_to_contact_id;
2171 l_api_rec.forecast_id := l_adj_tbl(1).forecast_id;
2172 l_api_rec.upside_quantity := l_adj_tbl(1).upside_quantity;
2173 l_api_rec.upside_amount := l_adj_tbl(1).upside_amount;
2174 l_api_rec.uom_code := l_adj_tbl(1).uom_code;
2175 l_api_rec.reason_code := l_adj_tbl(1).reason_code;
2176 l_api_rec.type := l_adj_tbl(1).type;
2177 l_api_rec.pre_processed_code := l_adj_tbl(1).pre_processed_code;
2178 l_api_rec.quota_id := l_adj_tbl(1).quota_id;
2179 l_api_rec.srp_plan_assign_id := l_adj_tbl(1).srp_plan_assign_id;
2180 l_api_rec.role_id := l_adj_tbl(1).role_id;
2181 l_api_rec.comp_group_id := l_adj_tbl(1).comp_group_id;
2182 l_api_rec.commission_amount := l_adj_tbl(1).commission_amount;
2183 l_api_rec.sales_channel := l_adj_tbl(1).sales_channel;
2184 --
2185 cn_comm_lines_api_pkg.insert_row(l_api_rec);
2186 --
2187 cn_message_pkg.write(
2188 p_message_text =>
2189 'Creating '||l_trx_type||' : Record-'||l_counter||' '||
2190 'api_id:'||l_comm_lines_api_id,
2191 p_message_type => 'MILESTONE');
2192 END IF;
2193 END LOOP;
2194 END LOOP;
2195 --
2196 cn_message_pkg.write(
2197 p_message_text => 'Total Records Negated for '||
2198 l_trx_type ||':'||l_negate_counter,
2199 p_message_type => 'MILESTONE');
2200 --
2201 cn_message_pkg.write(
2202 p_message_text => 'Total Number Of New '||l_trx_type||
2203 ' Created:'||l_counter,
2204 p_message_type => 'MILESTONE');
2205 --
2206 END LOOP;
2207 END IF;
2208 --
2209 cn_message_pkg.write(
2210 p_message_text => 'Ending Invoice Capture Batch Program',
2211 p_message_type => 'MILESTONE');
2212 --
2213 cn_message_pkg.end_batch(
2214 x_process_audit_id => l_process_audit_id);
2215 --
2216 EXCEPTION
2217 WHEN OTHERS THEN
2218 --
2219 ROLLBACK;
2220 --
2221 cn_message_pkg.write(
2222 p_message_text => 'Error Occured In The Batch Process',
2223 p_message_type => 'ERROR');
2224 --
2225 cn_message_pkg.write(
2226 p_message_text => SQLERRM,
2227 p_message_type => 'ERROR');
2228 --
2229 x_errbuf := 'ERROR';
2230 x_retcode := 1;
2231 --
2232 cn_message_pkg.end_batch(
2233 x_process_audit_id => l_process_audit_id);
2234 --
2235 END;
2236 --
2237 END;
2238