[Home] [Help]
PACKAGE BODY: APPS.CN_GET_TX_DATA_PUB
Source
1 PACKAGE BODY CN_GET_TX_DATA_PUB AS
2 --$Header: cnpxadjb.pls 120.21 2009/09/23 23:19:39 mguo ship $
3 -- +======================================================================+
4 -- | Copyright (c) 1994 Oracle Corporation |
5 -- | Redwood Shores, California, USA |
6 -- | All rights reserved. |
7 -- +======================================================================+
8 --
9 -- Package Name
10 -- cn_get_tx_data_pub
11 -- Purpose
12 -- Package Body for Mass Adjustments Package
13 -- History
14 -- 08/08/2005 Hithanki R12 Version
15 --
16 --
17 -- Nov 14, 2005 vensrini Commented out call to
18 -- convert_rec_to_gmiss in
19 -- update_api_rec procedure
20 --
21 -- Nov 22, 2005 vensrini Bug fix 4202682. Changed order_cur cursor
22 -- in call_split proc to exclude transactions with
23 -- load status as FILTERED
24 --
25 -- Jan 30, 2006 vensrini Added org id join to the cursor that checks whether
26 -- transaction processed date is in an open acc period
27 -- in insert_api_record procedure
28 --
29 -- Mar 27, 2006 vensrini Bug fix 5116954
30 --
31 -- Aug 2, 2006 vensrini Bug fix 5438265
32
33
34 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_GET_TX_DATA_PUB';
35 G_FILE_NAME CONSTANT VARCHAR2(12) := 'cnpxadjb.pls';
36 g_space VARCHAR2(10) := '&'||'nbsp;';
37 --
38 FUNCTION g_track_invoice
39 RETURN VARCHAR2 IS
40 l_track_invoice VARCHAR2(1) := 'N';
41 BEGIN
42 l_track_invoice := NVL(fnd_profile.value('CN_TRACK_INVOICE'),'N');
43 RETURN l_track_invoice;
44 EXCEPTION
45 WHEN OTHERS THEN
46 RETURN l_track_invoice;
47 END;
48 --
49
50
51 PROCEDURE get_api_data(
52 p_comm_lines_api_id IN NUMBER,
53 x_adj_tbl OUT NOCOPY adj_tbl_type) IS
54 --
55 CURSOR api_cur IS
56 SELECT l.*, s.name
57 FROM cn_comm_lines_api_all l,
58 cn_salesreps s
59 WHERE comm_lines_api_id = p_comm_lines_api_id
60 AND l.salesrep_id = s.salesrep_id;
61 --
62 CURSOR header_cur IS
63 SELECT h.*,s.employee_number,s.name
64 FROM cn_commission_headers_all h,
65 cn_salesreps s
66 WHERE comm_lines_api_id = p_comm_lines_api_id
67 AND h.direct_salesrep_id = s.salesrep_id;
68 --
69 l_tbl_count NUMBER := 1;
70 --
71 BEGIN
72 FOR adj IN api_cur
73 LOOP
74 x_adj_tbl(l_tbl_count).direct_salesrep_number := adj.employee_number;
75 x_adj_tbl(l_tbl_count).direct_salesrep_name := adj.name;
76 x_adj_tbl(l_tbl_count).direct_salesrep_id := adj.salesrep_id;
77 x_adj_tbl(l_tbl_count).processed_period_id := adj.processed_period_id;
78 x_adj_tbl(l_tbl_count).processed_date := adj.processed_date;
79 x_adj_tbl(l_tbl_count).rollup_date := adj.rollup_date;
80 x_adj_tbl(l_tbl_count).transaction_amount := adj.acctd_transaction_amount;
81 x_adj_tbl(l_tbl_count).transaction_amount_orig := adj.transaction_amount;
82 x_adj_tbl(l_tbl_count).trx_type := adj.trx_type;
83 x_adj_tbl(l_tbl_count).quantity := adj.quantity;
84 x_adj_tbl(l_tbl_count).discount_percentage := adj.discount_percentage;
85 x_adj_tbl(l_tbl_count).margin_percentage := adj.margin_percentage;
86 x_adj_tbl(l_tbl_count).orig_currency_code := adj.transaction_currency_code;
87 x_adj_tbl(l_tbl_count).exchange_rate := adj.exchange_rate;
88 x_adj_tbl(l_tbl_count).reason_code := adj.reason_code;
89 x_adj_tbl(l_tbl_count).comments := NULL;
90 x_adj_tbl(l_tbl_count).attribute_category := adj.attribute_category;
91 x_adj_tbl(l_tbl_count).attribute1 := adj.attribute1;
92 x_adj_tbl(l_tbl_count).attribute2 := adj.attribute2;
93 x_adj_tbl(l_tbl_count).attribute3 := adj.attribute3;
94 x_adj_tbl(l_tbl_count).attribute4 := adj.attribute4;
95 x_adj_tbl(l_tbl_count).attribute5 := adj.attribute5;
96 x_adj_tbl(l_tbl_count).attribute6 := adj.attribute6;
97 x_adj_tbl(l_tbl_count).attribute7 := adj.attribute7;
98 x_adj_tbl(l_tbl_count).attribute8 := adj.attribute8;
99 x_adj_tbl(l_tbl_count).attribute9 := adj.attribute9;
100 x_adj_tbl(l_tbl_count).attribute10 := adj.attribute10;
101 x_adj_tbl(l_tbl_count).attribute11 := adj.attribute11;
102 x_adj_tbl(l_tbl_count).attribute12 := adj.attribute12;
103 x_adj_tbl(l_tbl_count).attribute13 := adj.attribute13;
104 x_adj_tbl(l_tbl_count).attribute14 := adj.attribute14;
105 x_adj_tbl(l_tbl_count).attribute15 := adj.attribute15;
106 x_adj_tbl(l_tbl_count).attribute16 := adj.attribute16;
107 x_adj_tbl(l_tbl_count).attribute17 := adj.attribute17;
108 x_adj_tbl(l_tbl_count).attribute18 := adj.attribute18;
109 x_adj_tbl(l_tbl_count).attribute19 := adj.attribute19;
110 x_adj_tbl(l_tbl_count).attribute20 := adj.attribute20;
111 x_adj_tbl(l_tbl_count).attribute21 := adj.attribute21;
112 x_adj_tbl(l_tbl_count).attribute22 := adj.attribute22;
113 x_adj_tbl(l_tbl_count).attribute23 := adj.attribute23;
114 x_adj_tbl(l_tbl_count).attribute24 := adj.attribute24;
115 x_adj_tbl(l_tbl_count).attribute25 := adj.attribute25;
116 x_adj_tbl(l_tbl_count).attribute26 := adj.attribute26;
117 x_adj_tbl(l_tbl_count).attribute27 := adj.attribute27;
118 x_adj_tbl(l_tbl_count).attribute28 := adj.attribute28;
119 x_adj_tbl(l_tbl_count).attribute29 := adj.attribute29;
120 x_adj_tbl(l_tbl_count).attribute30 := adj.attribute30;
121 x_adj_tbl(l_tbl_count).attribute31 := adj.attribute31;
122 x_adj_tbl(l_tbl_count).attribute32 := adj.attribute32;
123 x_adj_tbl(l_tbl_count).attribute33 := adj.attribute33;
124 x_adj_tbl(l_tbl_count).attribute34 := adj.attribute34;
125 x_adj_tbl(l_tbl_count).attribute35 := adj.attribute35;
126 x_adj_tbl(l_tbl_count).attribute36 := adj.attribute36;
127 x_adj_tbl(l_tbl_count).attribute37 := adj.attribute37;
128 x_adj_tbl(l_tbl_count).attribute38 := adj.attribute38;
129 x_adj_tbl(l_tbl_count).attribute39 := adj.attribute39;
130 x_adj_tbl(l_tbl_count).attribute40 := adj.attribute40;
131 x_adj_tbl(l_tbl_count).attribute41 := adj.attribute41;
132 x_adj_tbl(l_tbl_count).attribute42 := adj.attribute42;
133 x_adj_tbl(l_tbl_count).attribute43 := adj.attribute43;
134 x_adj_tbl(l_tbl_count).attribute44 := adj.attribute44;
135 x_adj_tbl(l_tbl_count).attribute45 := adj.attribute45;
136 x_adj_tbl(l_tbl_count).attribute46 := adj.attribute46;
137 x_adj_tbl(l_tbl_count).attribute47 := adj.attribute47;
138 x_adj_tbl(l_tbl_count).attribute48 := adj.attribute48;
139 x_adj_tbl(l_tbl_count).attribute49 := adj.attribute49;
140 x_adj_tbl(l_tbl_count).attribute50 := adj.attribute50;
141 x_adj_tbl(l_tbl_count).attribute51 := adj.attribute51;
142 x_adj_tbl(l_tbl_count).attribute52 := adj.attribute52;
143 x_adj_tbl(l_tbl_count).attribute53 := adj.attribute53;
144 x_adj_tbl(l_tbl_count).attribute54 := adj.attribute54;
145 x_adj_tbl(l_tbl_count).attribute55 := adj.attribute55;
146 x_adj_tbl(l_tbl_count).attribute56 := adj.attribute56;
147 x_adj_tbl(l_tbl_count).attribute57 := adj.attribute57;
148 x_adj_tbl(l_tbl_count).attribute58 := adj.attribute58;
149 x_adj_tbl(l_tbl_count).attribute59 := adj.attribute59;
150 x_adj_tbl(l_tbl_count).attribute60 := adj.attribute60;
151 x_adj_tbl(l_tbl_count).attribute61 := adj.attribute61;
152 x_adj_tbl(l_tbl_count).attribute62 := adj.attribute62;
153 x_adj_tbl(l_tbl_count).attribute63 := adj.attribute63;
154 x_adj_tbl(l_tbl_count).attribute64 := adj.attribute64;
155 x_adj_tbl(l_tbl_count).attribute65 := adj.attribute65;
156 x_adj_tbl(l_tbl_count).attribute66 := adj.attribute66;
157 x_adj_tbl(l_tbl_count).attribute67 := adj.attribute67;
158 x_adj_tbl(l_tbl_count).attribute68 := adj.attribute68;
159 x_adj_tbl(l_tbl_count).attribute69 := adj.attribute69;
160 x_adj_tbl(l_tbl_count).attribute70 := adj.attribute70;
161 x_adj_tbl(l_tbl_count).attribute71 := adj.attribute71;
162 x_adj_tbl(l_tbl_count).attribute72 := adj.attribute72;
163 x_adj_tbl(l_tbl_count).attribute73 := adj.attribute73;
164 x_adj_tbl(l_tbl_count).attribute74 := adj.attribute74;
165 x_adj_tbl(l_tbl_count).attribute75 := adj.attribute75;
166 x_adj_tbl(l_tbl_count).attribute76 := adj.attribute76;
167 x_adj_tbl(l_tbl_count).attribute77 := adj.attribute77;
168 x_adj_tbl(l_tbl_count).attribute78 := adj.attribute78;
169 x_adj_tbl(l_tbl_count).attribute79 := adj.attribute79;
170 x_adj_tbl(l_tbl_count).attribute80 := adj.attribute80;
171 x_adj_tbl(l_tbl_count).attribute81 := adj.attribute81;
172 x_adj_tbl(l_tbl_count).attribute82 := adj.attribute82;
173 x_adj_tbl(l_tbl_count).attribute83 := adj.attribute83;
174 x_adj_tbl(l_tbl_count).attribute84 := adj.attribute84;
175 x_adj_tbl(l_tbl_count).attribute85 := adj.attribute85;
176 x_adj_tbl(l_tbl_count).attribute86 := adj.attribute86;
177 x_adj_tbl(l_tbl_count).attribute87 := adj.attribute87;
178 x_adj_tbl(l_tbl_count).attribute88 := adj.attribute88;
179 x_adj_tbl(l_tbl_count).attribute89 := adj.attribute89;
180 x_adj_tbl(l_tbl_count).attribute90 := adj.attribute90;
181 x_adj_tbl(l_tbl_count).attribute91 := adj.attribute91;
182 x_adj_tbl(l_tbl_count).attribute92 := adj.attribute92;
183 x_adj_tbl(l_tbl_count).attribute93 := adj.attribute93;
184 x_adj_tbl(l_tbl_count).attribute94 := adj.attribute94;
185 x_adj_tbl(l_tbl_count).attribute95 := adj.attribute95;
186 x_adj_tbl(l_tbl_count).attribute96 := adj.attribute96;
187 x_adj_tbl(l_tbl_count).attribute97 := adj.attribute97;
188 x_adj_tbl(l_tbl_count).attribute98 := adj.attribute98;
189 x_adj_tbl(l_tbl_count).attribute99 := adj.attribute99;
190 x_adj_tbl(l_tbl_count).attribute100 := adj.attribute100;
191 x_adj_tbl(l_tbl_count).comm_lines_api_id := adj.comm_lines_api_id;
192 x_adj_tbl(l_tbl_count).source_doc_type := adj.source_doc_type;
193 x_adj_tbl(l_tbl_count).source_trx_number := adj.source_trx_number;
194 x_adj_tbl(l_tbl_count).trx_sales_line_id := adj.trx_sales_line_id;
195 x_adj_tbl(l_tbl_count).trx_line_id := adj.trx_line_id;
196 x_adj_tbl(l_tbl_count).trx_id := adj.trx_id;
197 x_adj_tbl(l_tbl_count).upside_amount := adj.upside_amount;
198 x_adj_tbl(l_tbl_count).upside_quantity := adj.upside_quantity;
199 x_adj_tbl(l_tbl_count).uom_code := adj.uom_code;
200 x_adj_tbl(l_tbl_count).forecast_id := adj.forecast_id;
201 x_adj_tbl(l_tbl_count).invoice_number := adj.invoice_number;
202 x_adj_tbl(l_tbl_count).invoice_date := adj.invoice_date;
203 x_adj_tbl(l_tbl_count).order_number := adj.order_number;
204 x_adj_tbl(l_tbl_count).order_date := adj.booked_date;
205 x_adj_tbl(l_tbl_count).line_number := adj.line_number;
206 x_adj_tbl(l_tbl_count).customer_id := adj.customer_id;
207 x_adj_tbl(l_tbl_count).bill_to_address_id := adj.bill_to_address_id;
208 x_adj_tbl(l_tbl_count).ship_to_address_id := adj.ship_to_address_id;
209 x_adj_tbl(l_tbl_count).bill_to_contact_id := adj.bill_to_contact_id;
210 x_adj_tbl(l_tbl_count).ship_to_contact_id := adj.ship_to_contact_id;
211 x_adj_tbl(l_tbl_count).load_status := adj.load_status;
212 x_adj_tbl(l_tbl_count).revenue_type := adj.revenue_type;
213 x_adj_tbl(l_tbl_count).adjust_rollup_flag := adj.adjust_rollup_flag;
214 x_adj_tbl(l_tbl_count).adjust_date := adj.adjust_date;
215 x_adj_tbl(l_tbl_count).adjusted_by := adj.adjusted_by;
216 x_adj_tbl(l_tbl_count).adjust_status := NVL(adj.adjust_status,'NEW');
217 x_adj_tbl(l_tbl_count).adjust_comments := adj.adjust_comments;
218 x_adj_tbl(l_tbl_count).type := adj.type;
219 x_adj_tbl(l_tbl_count).pre_processed_code := adj.pre_processed_code;
220 x_adj_tbl(l_tbl_count).comp_group_id := adj.comp_group_id;
221 x_adj_tbl(l_tbl_count).srp_plan_assign_id := adj.srp_plan_assign_id;
222 x_adj_tbl(l_tbl_count).role_id := adj.role_id;
223 x_adj_tbl(l_tbl_count).sales_channel := adj.sales_channel;
224 x_adj_tbl(l_tbl_count).split_pct := adj.split_pct;
225 x_adj_tbl(l_tbl_count).split_status := adj.split_status;
226 x_adj_tbl(l_tbl_count).source_trx_id := adj.source_trx_id;
227 x_adj_tbl(l_tbl_count).source_trx_line_id := adj.source_trx_line_id;
228 x_adj_tbl(l_tbl_count).source_trx_sales_line_id := adj.source_trx_sales_line_id;
229 x_adj_tbl(l_tbl_count).org_id := adj.org_id;
230 x_adj_tbl(l_tbl_count).inventory_item_id := adj.inventory_item_id; -- Bug fix 5116954
231 /*Fix for Crediting bug*/
232 x_adj_tbl(l_tbl_count).terr_id := adj.terr_id;
233 x_adj_tbl(l_tbl_count).terr_name := adj.terr_name;
234 x_adj_tbl(l_tbl_count).preserve_credit_override_flag := NVL(adj.preserve_credit_override_flag,'N');
235
236 l_tbl_count := l_tbl_count + 1;
237 END LOOP;
238 IF (x_adj_tbl.COUNT = 0) THEN
239 FOR adj IN header_cur
240 LOOP
241 x_adj_tbl(l_tbl_count).direct_salesrep_number := adj.employee_number;
242 x_adj_tbl(l_tbl_count).direct_salesrep_name := adj.name;
243 x_adj_tbl(l_tbl_count).direct_salesrep_id := adj.direct_salesrep_id;
244 x_adj_tbl(l_tbl_count).processed_period_id := adj.processed_period_id;
245 x_adj_tbl(l_tbl_count).processed_date := adj.processed_date;
246 x_adj_tbl(l_tbl_count).rollup_date := adj.rollup_date;
247 x_adj_tbl(l_tbl_count).transaction_amount := adj.transaction_amount;
248 x_adj_tbl(l_tbl_count).transaction_amount_orig := adj.transaction_amount_orig;
249 x_adj_tbl(l_tbl_count).trx_type := adj.trx_type;
250 x_adj_tbl(l_tbl_count).quantity := adj.quantity;
251 x_adj_tbl(l_tbl_count).discount_percentage := adj.discount_percentage;
252 x_adj_tbl(l_tbl_count).margin_percentage := adj.margin_percentage;
253 x_adj_tbl(l_tbl_count).orig_currency_code := adj.orig_currency_code;
254 x_adj_tbl(l_tbl_count).exchange_rate := adj.exchange_rate;
255 x_adj_tbl(l_tbl_count).reason_code := adj.reason_code;
256 x_adj_tbl(l_tbl_count).comments := adj.comments;
257 x_adj_tbl(l_tbl_count).attribute_category := adj.attribute_category;
258 x_adj_tbl(l_tbl_count).attribute1 := adj.attribute1;
259 x_adj_tbl(l_tbl_count).attribute2 := adj.attribute2;
260 x_adj_tbl(l_tbl_count).attribute3 := adj.attribute3;
261 x_adj_tbl(l_tbl_count).attribute4 := adj.attribute4;
262 x_adj_tbl(l_tbl_count).attribute5 := adj.attribute5;
263 x_adj_tbl(l_tbl_count).attribute6 := adj.attribute6;
264 x_adj_tbl(l_tbl_count).attribute7 := adj.attribute7;
265 x_adj_tbl(l_tbl_count).attribute8 := adj.attribute8;
266 x_adj_tbl(l_tbl_count).attribute9 := adj.attribute9;
267 x_adj_tbl(l_tbl_count).attribute10 := adj.attribute10;
268 x_adj_tbl(l_tbl_count).attribute11 := adj.attribute11;
269 x_adj_tbl(l_tbl_count).attribute12 := adj.attribute12;
270 x_adj_tbl(l_tbl_count).attribute13 := adj.attribute13;
271 x_adj_tbl(l_tbl_count).attribute14 := adj.attribute14;
272 x_adj_tbl(l_tbl_count).attribute15 := adj.attribute15;
273 x_adj_tbl(l_tbl_count).attribute16 := adj.attribute16;
274 x_adj_tbl(l_tbl_count).attribute17 := adj.attribute17;
275 x_adj_tbl(l_tbl_count).attribute18 := adj.attribute18;
276 x_adj_tbl(l_tbl_count).attribute19 := adj.attribute19;
277 x_adj_tbl(l_tbl_count).attribute20 := adj.attribute20;
278 x_adj_tbl(l_tbl_count).attribute21 := adj.attribute21;
279 x_adj_tbl(l_tbl_count).attribute22 := adj.attribute22;
280 x_adj_tbl(l_tbl_count).attribute23 := adj.attribute23;
281 x_adj_tbl(l_tbl_count).attribute24 := adj.attribute24;
282 x_adj_tbl(l_tbl_count).attribute25 := adj.attribute25;
283 x_adj_tbl(l_tbl_count).attribute26 := adj.attribute26;
284 x_adj_tbl(l_tbl_count).attribute27 := adj.attribute27;
285 x_adj_tbl(l_tbl_count).attribute28 := adj.attribute28;
286 x_adj_tbl(l_tbl_count).attribute29 := adj.attribute29;
287 x_adj_tbl(l_tbl_count).attribute30 := adj.attribute30;
288 x_adj_tbl(l_tbl_count).attribute31 := adj.attribute31;
289 x_adj_tbl(l_tbl_count).attribute32 := adj.attribute32;
290 x_adj_tbl(l_tbl_count).attribute33 := adj.attribute33;
291 x_adj_tbl(l_tbl_count).attribute34 := adj.attribute34;
292 x_adj_tbl(l_tbl_count).attribute35 := adj.attribute35;
293 x_adj_tbl(l_tbl_count).attribute36 := adj.attribute36;
294 x_adj_tbl(l_tbl_count).attribute37 := adj.attribute37;
295 x_adj_tbl(l_tbl_count).attribute38 := adj.attribute38;
296 x_adj_tbl(l_tbl_count).attribute39 := adj.attribute39;
297 x_adj_tbl(l_tbl_count).attribute40 := adj.attribute40;
298 x_adj_tbl(l_tbl_count).attribute41 := adj.attribute41;
299 x_adj_tbl(l_tbl_count).attribute42 := adj.attribute42;
300 x_adj_tbl(l_tbl_count).attribute43 := adj.attribute43;
301 x_adj_tbl(l_tbl_count).attribute44 := adj.attribute44;
302 x_adj_tbl(l_tbl_count).attribute45 := adj.attribute45;
303 x_adj_tbl(l_tbl_count).attribute46 := adj.attribute46;
304 x_adj_tbl(l_tbl_count).attribute47 := adj.attribute47;
305 x_adj_tbl(l_tbl_count).attribute48 := adj.attribute48;
306 x_adj_tbl(l_tbl_count).attribute49 := adj.attribute49;
307 x_adj_tbl(l_tbl_count).attribute50 := adj.attribute50;
308 x_adj_tbl(l_tbl_count).attribute51 := adj.attribute51;
309 x_adj_tbl(l_tbl_count).attribute52 := adj.attribute52;
310 x_adj_tbl(l_tbl_count).attribute53 := adj.attribute53;
311 x_adj_tbl(l_tbl_count).attribute54 := adj.attribute54;
312 x_adj_tbl(l_tbl_count).attribute55 := adj.attribute55;
313 x_adj_tbl(l_tbl_count).attribute56 := adj.attribute56;
314 x_adj_tbl(l_tbl_count).attribute57 := adj.attribute57;
315 x_adj_tbl(l_tbl_count).attribute58 := adj.attribute58;
316 x_adj_tbl(l_tbl_count).attribute59 := adj.attribute59;
317 x_adj_tbl(l_tbl_count).attribute60 := adj.attribute60;
318 x_adj_tbl(l_tbl_count).attribute61 := adj.attribute61;
319 x_adj_tbl(l_tbl_count).attribute62 := adj.attribute62;
320 x_adj_tbl(l_tbl_count).attribute63 := adj.attribute63;
321 x_adj_tbl(l_tbl_count).attribute64 := adj.attribute64;
322 x_adj_tbl(l_tbl_count).attribute65 := adj.attribute65;
323 x_adj_tbl(l_tbl_count).attribute66 := adj.attribute66;
324 x_adj_tbl(l_tbl_count).attribute67 := adj.attribute67;
325 x_adj_tbl(l_tbl_count).attribute68 := adj.attribute68;
326 x_adj_tbl(l_tbl_count).attribute69 := adj.attribute69;
327 x_adj_tbl(l_tbl_count).attribute70 := adj.attribute70;
328 x_adj_tbl(l_tbl_count).attribute71 := adj.attribute71;
329 x_adj_tbl(l_tbl_count).attribute72 := adj.attribute72;
330 x_adj_tbl(l_tbl_count).attribute73 := adj.attribute73;
331 x_adj_tbl(l_tbl_count).attribute74 := adj.attribute74;
332 x_adj_tbl(l_tbl_count).attribute75 := adj.attribute75;
333 x_adj_tbl(l_tbl_count).attribute76 := adj.attribute76;
334 x_adj_tbl(l_tbl_count).attribute77 := adj.attribute77;
335 x_adj_tbl(l_tbl_count).attribute78 := adj.attribute78;
336 x_adj_tbl(l_tbl_count).attribute79 := adj.attribute79;
337 x_adj_tbl(l_tbl_count).attribute80 := adj.attribute80;
338 x_adj_tbl(l_tbl_count).attribute81 := adj.attribute81;
339 x_adj_tbl(l_tbl_count).attribute82 := adj.attribute82;
340 x_adj_tbl(l_tbl_count).attribute83 := adj.attribute83;
341 x_adj_tbl(l_tbl_count).attribute84 := adj.attribute84;
342 x_adj_tbl(l_tbl_count).attribute85 := adj.attribute85;
343 x_adj_tbl(l_tbl_count).attribute86 := adj.attribute86;
344 x_adj_tbl(l_tbl_count).attribute87 := adj.attribute87;
345 x_adj_tbl(l_tbl_count).attribute88 := adj.attribute88;
346 x_adj_tbl(l_tbl_count).attribute89 := adj.attribute89;
347 x_adj_tbl(l_tbl_count).attribute90 := adj.attribute90;
348 x_adj_tbl(l_tbl_count).attribute91 := adj.attribute91;
349 x_adj_tbl(l_tbl_count).attribute92 := adj.attribute92;
350 x_adj_tbl(l_tbl_count).attribute93 := adj.attribute93;
351 x_adj_tbl(l_tbl_count).attribute94 := adj.attribute94;
352 x_adj_tbl(l_tbl_count).attribute95 := adj.attribute95;
353 x_adj_tbl(l_tbl_count).attribute96 := adj.attribute96;
354 x_adj_tbl(l_tbl_count).attribute97 := adj.attribute97;
355 x_adj_tbl(l_tbl_count).attribute98 := adj.attribute98;
356 x_adj_tbl(l_tbl_count).attribute99 := adj.attribute99;
357 x_adj_tbl(l_tbl_count).attribute100 := adj.attribute100;
358 x_adj_tbl(l_tbl_count).comm_lines_api_id := adj.comm_lines_api_id;
359 x_adj_tbl(l_tbl_count).source_doc_type := adj.source_doc_type;
360 x_adj_tbl(l_tbl_count).source_trx_number := adj.source_trx_number;
361 x_adj_tbl(l_tbl_count).upside_amount := adj.upside_amount;
362 x_adj_tbl(l_tbl_count).upside_quantity := adj.upside_quantity;
363 x_adj_tbl(l_tbl_count).uom_code := adj.uom_code;
364 x_adj_tbl(l_tbl_count).forecast_id := adj.forecast_id;
365 x_adj_tbl(l_tbl_count).invoice_number := adj.invoice_number;
366 x_adj_tbl(l_tbl_count).invoice_date := adj.invoice_date;
367 x_adj_tbl(l_tbl_count).order_number := adj.order_number;
368 x_adj_tbl(l_tbl_count).order_date := adj.booked_date;
369 x_adj_tbl(l_tbl_count).line_number := adj.line_number;
370 x_adj_tbl(l_tbl_count).customer_id := adj.customer_id;
371 x_adj_tbl(l_tbl_count).bill_to_address_id := adj.bill_to_address_id;
372 x_adj_tbl(l_tbl_count).ship_to_address_id := adj.ship_to_address_id;
373 x_adj_tbl(l_tbl_count).bill_to_contact_id := adj.bill_to_contact_id;
374 x_adj_tbl(l_tbl_count).ship_to_contact_id := adj.ship_to_contact_id;
375 x_adj_tbl(l_tbl_count).load_status := NULL;
376 x_adj_tbl(l_tbl_count).revenue_type := adj.revenue_type;
377 x_adj_tbl(l_tbl_count).adjust_rollup_flag := adj.adjust_rollup_flag;
378 x_adj_tbl(l_tbl_count).adjust_date := adj.adjust_date;
379 x_adj_tbl(l_tbl_count).adjusted_by := adj.adjusted_by;
380 x_adj_tbl(l_tbl_count).adjust_status := NVL(adj.adjust_status,'NEW');
381 x_adj_tbl(l_tbl_count).adjust_comments := adj.adjust_comments;
382 x_adj_tbl(l_tbl_count).type := adj.type;
383 x_adj_tbl(l_tbl_count).pre_processed_code := adj.pre_processed_code;
384 x_adj_tbl(l_tbl_count).comp_group_id := adj.comp_group_id;
385 x_adj_tbl(l_tbl_count).srp_plan_assign_id := adj.srp_plan_assign_id;
386 x_adj_tbl(l_tbl_count).role_id := adj.role_id;
387 x_adj_tbl(l_tbl_count).sales_channel := adj.sales_channel;
388 x_adj_tbl(l_tbl_count).split_pct := adj.split_pct;
389 x_adj_tbl(l_tbl_count).split_status := adj.split_status;
390 x_adj_tbl(l_tbl_count).source_trx_id := adj.source_trx_id;
391 x_adj_tbl(l_tbl_count).source_trx_line_id := adj.source_trx_line_id;
392 x_adj_tbl(l_tbl_count).source_trx_sales_line_id := adj.source_trx_sales_line_id;
393 x_adj_tbl(l_tbl_count).inventory_item_id := adj.inventory_item_id; -- Bug fix 5116954
394 x_adj_tbl(l_tbl_count).org_id := adj.org_id;
395 l_tbl_count := l_tbl_count + 1;
396 END LOOP;
397 END IF;
398 END;
399 --
400 --
401 FUNCTION get_adjusted_by
402 RETURN VARCHAR2 IS
403 l_adjusted_by VARCHAR2(100) := '0';
404 BEGIN
405 SELECT user_name
406 INTO l_adjusted_by
407 FROM fnd_user
408 WHERE user_id = fnd_profile.value('USER_ID');
409 RETURN l_adjusted_by;
410 EXCEPTION
411 WHEN OTHERS THEN
412 RETURN l_adjusted_by;
413 END;
414 --
415
416
417
418
419 --
420
421 PROCEDURE get_adj (
422 p_api_version IN NUMBER,
423 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
424 p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL,
425 p_org_id IN NUMBER := FND_API.G_MISS_NUM,
426 p_salesrep_id IN NUMBER := FND_API.G_MISS_NUM,
427 p_pr_date_to IN DATE := FND_API.G_MISS_DATE,
428 p_pr_date_from IN DATE := FND_API.G_MISS_DATE,
429 p_calc_status IN VARCHAR2 := FND_API.G_MISS_CHAR,
430 p_adj_status IN VARCHAR2 := FND_API.G_MISS_CHAR,
431 p_load_status IN VARCHAR2 := FND_API.G_MISS_CHAR,
432 p_invoice_num IN VARCHAR2 := FND_API.G_MISS_CHAR,
433 p_order_num IN NUMBER := FND_API.G_MISS_NUM,
434 p_srch_attr_rec IN adj_rec_type,
435 p_first IN NUMBER,
436 p_last IN NUMBER,
437 x_return_status OUT NOCOPY VARCHAR2,
438 x_msg_count OUT NOCOPY NUMBER,
439 x_msg_data OUT NOCOPY VARCHAR2,
440 x_loading_status OUT NOCOPY VARCHAR2,
441 x_adj_tbl OUT NOCOPY adj_tbl_type,
442 x_adj_count OUT NOCOPY NUMBER,
443 x_valid_trx_count OUT NOCOPY NUMBER) IS
444
445 l_api_name CONSTANT VARCHAR2(30) := 'get_adj';
446 l_api_version CONSTANT NUMBER := 1.0;
447 l_tbl_count NUMBER;
448 l_api_sql VARCHAR2(10000);
449 l_header_sql VARCHAR2(10000);
450 l_total_rows NUMBER := 0;
451 l_adjusted_by VARCHAR2(100) := '0';
452 l_api_query_flag CHAR(1) := 'Y';
453 l_source_counter NUMBER := 0;
454 l_valid_trx_counter NUMBER := 0;
455 l_return_status VARCHAR2(30);
456 -- Tables/Records definitions
457 l_adj_tbl adj_tbl_type;
458 adj adj_rec_type;
459
460 -- Defining REF CURSOR
461 TYPE rc IS REF CURSOR;
462 query_cur rc;
463 BEGIN
464 -- Standard Start of API savepoint
465 SAVEPOINT get_adj;
466 -- Standard call to check for call compatibility.
467 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
468 p_api_version ,
469 l_api_name,
470 G_PKG_NAME ) THEN
471 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
472 END IF;
473 -- Initialize message list if p_init_msg_list is set to TRUE.
474 IF FND_API.to_Boolean( p_init_msg_list ) THEN
475 FND_MSG_PUB.initialize;
476 END IF;
477 -- Initialize API return status to success
478 x_return_status := FND_API.G_RET_STS_SUCCESS;
479 x_loading_status := 'CN_INSERTED';
480 -- API body
481 /* This API take the parameters and construct the SQL. Based on the SQL
482 get the data from cn_comm_lines_api and cn_commission_headers tables. */
483 cn_mass_adjust_util.search_result(
484 p_salesrep_id => p_salesrep_id,
485 p_pr_date_to => p_pr_date_to,
486 p_pr_date_from => p_pr_date_from,
487 p_calc_status => p_calc_status,
488 p_adj_status => p_adj_status,
489 p_load_status => p_load_status,
490 p_invoice_num => p_invoice_num,
491 p_order_num => p_order_num,
492 p_org_id => p_org_id,
493 p_srch_attr_rec => p_srch_attr_rec,
494 x_return_status => l_return_status,
495 x_adj_tbl => l_adj_tbl,
496 x_source_counter => l_source_counter);
497 IF (l_source_counter > 0) THEN
498 l_tbl_count := 0;
499 FOR i IN 1..l_source_counter
500 LOOP
501 IF ((l_adj_tbl(i).adjust_status NOT IN('FROZEN','REVERSAL','SCA_PENDING')) --OR
502 --l_adj_tbl(i).adjust_status IS null)
503 AND
504 l_adj_tbl(i).trx_type NOT IN ('ITD','GRP','THR') AND
505 l_adj_tbl(i).load_status NOT IN ('FILTERED')) THEN
506 l_valid_trx_counter := l_valid_trx_counter + 1;
507 END IF;
508 l_total_rows := l_total_rows + 1;
509 IF (l_total_rows BETWEEN p_first AND p_last) THEN
510 l_tbl_count := l_tbl_count + 1;
511 x_adj_tbl(l_tbl_count).commission_header_id := NVL(l_adj_tbl(i).commission_header_id,0);
512 x_adj_tbl(l_tbl_count).direct_salesrep_number:= NVL(l_adj_tbl(i).direct_salesrep_number,g_space);
513 x_adj_tbl(l_tbl_count).direct_salesrep_name := NVL(l_adj_tbl(i).direct_salesrep_name,g_space);
514 x_adj_tbl(l_tbl_count).direct_salesrep_id := NVL(l_adj_tbl(i).direct_salesrep_id,0);
515 x_adj_tbl(l_tbl_count).processed_period_id := NVL(l_adj_tbl(i).processed_period_id,0);
516 x_adj_tbl(l_tbl_count).processed_period := NVL(l_adj_tbl(i).processed_period,g_space);
517 x_adj_tbl(l_tbl_count).processed_date := l_adj_tbl(i).processed_date;
518 x_adj_tbl(l_tbl_count).rollup_date := l_adj_tbl(i).rollup_date;
519 x_adj_tbl(l_tbl_count).transaction_amount := NVL(l_adj_tbl(i).transaction_amount,0);
520 x_adj_tbl(l_tbl_count).transaction_amount_orig:= NVL(l_adj_tbl(i).transaction_amount_orig,0);
521 x_adj_tbl(l_tbl_count).quantity := NVL(l_adj_tbl(i).quantity,0);
522 x_adj_tbl(l_tbl_count).discount_percentage := NVL(l_adj_tbl(i).discount_percentage,0);
523 x_adj_tbl(l_tbl_count).margin_percentage := NVL(l_adj_tbl(i).margin_percentage,0);
524 x_adj_tbl(l_tbl_count).orig_currency_code := NVL(l_adj_tbl(i).orig_currency_code,g_space);
525 x_adj_tbl(l_tbl_count).exchange_rate := NVL(l_adj_tbl(i).exchange_rate,0);
526 x_adj_tbl(l_tbl_count).status_disp := NVL(l_adj_tbl(i).status_disp,g_space);
527 x_adj_tbl(l_tbl_count).status := NVL(l_adj_tbl(i).status,g_space);
528 x_adj_tbl(l_tbl_count).trx_type_disp := NVL(l_adj_tbl(i).trx_type_disp,g_space);
529 x_adj_tbl(l_tbl_count).trx_type := NVL(l_adj_tbl(i).trx_type,g_space);
530 x_adj_tbl(l_tbl_count).reason := NVL(l_adj_tbl(i).reason,g_space);
531 x_adj_tbl(l_tbl_count).reason_code := NVL(l_adj_tbl(i).reason_code,g_space);
532 x_adj_tbl(l_tbl_count).comments := NVL(l_adj_tbl(i).comments,g_space);
533 x_adj_tbl(l_tbl_count).trx_batch_id := NVL(l_adj_tbl(i).trx_batch_id,0);
534 x_adj_tbl(l_tbl_count).created_by := NVL(l_adj_tbl(i).created_by,0);
535 x_adj_tbl(l_tbl_count).creation_date := l_adj_tbl(i).creation_date;
536 x_adj_tbl(l_tbl_count).last_updated_by := NVL(l_adj_tbl(i).last_updated_by,0);
537 x_adj_tbl(l_tbl_count).last_update_login := NVL(l_adj_tbl(i).last_update_login,0);
538 x_adj_tbl(l_tbl_count).last_update_date := l_adj_tbl(i).last_update_date;
539 x_adj_tbl(l_tbl_count).attribute_category := l_adj_tbl(i).attribute_category;
540 x_adj_tbl(l_tbl_count).attribute1 := NVL(l_adj_tbl(i).attribute1,g_space);
541 x_adj_tbl(l_tbl_count).attribute2 := NVL(l_adj_tbl(i).attribute2,g_space);
542 x_adj_tbl(l_tbl_count).attribute3 := NVL(l_adj_tbl(i).attribute3,g_space);
543 x_adj_tbl(l_tbl_count).attribute4 := NVL(l_adj_tbl(i).attribute4,g_space);
544 x_adj_tbl(l_tbl_count).attribute5 := NVL(l_adj_tbl(i).attribute5,g_space);
545 x_adj_tbl(l_tbl_count).attribute6 := NVL(l_adj_tbl(i).attribute6,g_space);
546 x_adj_tbl(l_tbl_count).attribute7 := NVL(l_adj_tbl(i).attribute7,g_space);
547 x_adj_tbl(l_tbl_count).attribute8 := NVL(l_adj_tbl(i).attribute8,g_space);
548 x_adj_tbl(l_tbl_count).attribute9 := NVL(l_adj_tbl(i).attribute9,g_space);
549 x_adj_tbl(l_tbl_count).attribute10 := NVL(l_adj_tbl(i).attribute10,g_space);
550 x_adj_tbl(l_tbl_count).attribute11 := NVL(l_adj_tbl(i).attribute11,g_space);
551 x_adj_tbl(l_tbl_count).attribute12 := NVL(l_adj_tbl(i).attribute12,g_space);
552 x_adj_tbl(l_tbl_count).attribute13 := NVL(l_adj_tbl(i).attribute13,g_space);
553 x_adj_tbl(l_tbl_count).attribute14 := NVL(l_adj_tbl(i).attribute14,g_space);
554 x_adj_tbl(l_tbl_count).attribute15 := NVL(l_adj_tbl(i).attribute15,g_space);
555 x_adj_tbl(l_tbl_count).attribute16 := NVL(l_adj_tbl(i).attribute16,g_space);
556 x_adj_tbl(l_tbl_count).attribute17 := NVL(l_adj_tbl(i).attribute17,g_space);
557 x_adj_tbl(l_tbl_count).attribute18 := NVL(l_adj_tbl(i).attribute18,g_space);
558 x_adj_tbl(l_tbl_count).attribute19 := NVL(l_adj_tbl(i).attribute19,g_space);
559 x_adj_tbl(l_tbl_count).attribute20 := NVL(l_adj_tbl(i).attribute20,g_space);
560 x_adj_tbl(l_tbl_count).attribute21 := NVL(l_adj_tbl(i).attribute21,g_space);
561 x_adj_tbl(l_tbl_count).attribute22 := NVL(l_adj_tbl(i).attribute22,g_space);
562 x_adj_tbl(l_tbl_count).attribute23 := NVL(l_adj_tbl(i).attribute23,g_space);
563 x_adj_tbl(l_tbl_count).attribute24 := NVL(l_adj_tbl(i).attribute24,g_space);
564 x_adj_tbl(l_tbl_count).attribute25 := NVL(l_adj_tbl(i).attribute25,g_space);
565 x_adj_tbl(l_tbl_count).attribute26 := NVL(l_adj_tbl(i).attribute26,g_space);
566 x_adj_tbl(l_tbl_count).attribute27 := NVL(l_adj_tbl(i).attribute27,g_space);
567 x_adj_tbl(l_tbl_count).attribute28 := NVL(l_adj_tbl(i).attribute28,g_space);
568 x_adj_tbl(l_tbl_count).attribute29 := NVL(l_adj_tbl(i).attribute29,g_space);
569 x_adj_tbl(l_tbl_count).attribute30 := NVL(l_adj_tbl(i).attribute30,g_space);
570 x_adj_tbl(l_tbl_count).attribute31 := NVL(l_adj_tbl(i).attribute31,g_space);
571 x_adj_tbl(l_tbl_count).attribute32 := NVL(l_adj_tbl(i).attribute32,g_space);
572 x_adj_tbl(l_tbl_count).attribute33 := NVL(l_adj_tbl(i).attribute33,g_space);
573 x_adj_tbl(l_tbl_count).attribute34 := NVL(l_adj_tbl(i).attribute34,g_space);
574 x_adj_tbl(l_tbl_count).attribute35 := NVL(l_adj_tbl(i).attribute35,g_space);
575 x_adj_tbl(l_tbl_count).attribute36 := NVL(l_adj_tbl(i).attribute36,g_space);
576 x_adj_tbl(l_tbl_count).attribute37 := NVL(l_adj_tbl(i).attribute37,g_space);
577 x_adj_tbl(l_tbl_count).attribute38 := NVL(l_adj_tbl(i).attribute38,g_space);
578 x_adj_tbl(l_tbl_count).attribute39 := NVL(l_adj_tbl(i).attribute39,g_space);
579 x_adj_tbl(l_tbl_count).attribute40 := NVL(l_adj_tbl(i).attribute40,g_space);
580 x_adj_tbl(l_tbl_count).attribute41 := NVL(l_adj_tbl(i).attribute41,g_space);
581 x_adj_tbl(l_tbl_count).attribute42 := NVL(l_adj_tbl(i).attribute42,g_space);
582 x_adj_tbl(l_tbl_count).attribute43 := NVL(l_adj_tbl(i).attribute43,g_space);
583 x_adj_tbl(l_tbl_count).attribute44 := NVL(l_adj_tbl(i).attribute44,g_space);
584 x_adj_tbl(l_tbl_count).attribute45 := NVL(l_adj_tbl(i).attribute45,g_space);
585 x_adj_tbl(l_tbl_count).attribute46 := NVL(l_adj_tbl(i).attribute46,g_space);
586 x_adj_tbl(l_tbl_count).attribute47 := NVL(l_adj_tbl(i).attribute47,g_space);
587 x_adj_tbl(l_tbl_count).attribute48 := NVL(l_adj_tbl(i).attribute48,g_space);
588 x_adj_tbl(l_tbl_count).attribute49 := NVL(l_adj_tbl(i).attribute49,g_space);
589 x_adj_tbl(l_tbl_count).attribute50 := NVL(l_adj_tbl(i).attribute50,g_space);
590 x_adj_tbl(l_tbl_count).attribute51 := NVL(l_adj_tbl(i).attribute51,g_space);
591 x_adj_tbl(l_tbl_count).attribute52 := NVL(l_adj_tbl(i).attribute52,g_space);
592 x_adj_tbl(l_tbl_count).attribute53 := NVL(l_adj_tbl(i).attribute53,g_space);
593 x_adj_tbl(l_tbl_count).attribute54 := NVL(l_adj_tbl(i).attribute54,g_space);
594 x_adj_tbl(l_tbl_count).attribute55 := NVL(l_adj_tbl(i).attribute55,g_space);
595 x_adj_tbl(l_tbl_count).attribute56 := NVL(l_adj_tbl(i).attribute56,g_space);
596 x_adj_tbl(l_tbl_count).attribute57 := NVL(l_adj_tbl(i).attribute57,g_space);
597 x_adj_tbl(l_tbl_count).attribute58 := NVL(l_adj_tbl(i).attribute58,g_space);
598 x_adj_tbl(l_tbl_count).attribute59 := NVL(l_adj_tbl(i).attribute59,g_space);
599 x_adj_tbl(l_tbl_count).attribute60 := NVL(l_adj_tbl(i).attribute60,g_space);
600 x_adj_tbl(l_tbl_count).attribute61 := NVL(l_adj_tbl(i).attribute61,g_space);
601 x_adj_tbl(l_tbl_count).attribute62 := NVL(l_adj_tbl(i).attribute62,g_space);
602 x_adj_tbl(l_tbl_count).attribute63 := NVL(l_adj_tbl(i).attribute63,g_space);
603 x_adj_tbl(l_tbl_count).attribute64 := NVL(l_adj_tbl(i).attribute64,g_space);
604 x_adj_tbl(l_tbl_count).attribute65 := NVL(l_adj_tbl(i).attribute65,g_space);
605 x_adj_tbl(l_tbl_count).attribute66 := NVL(l_adj_tbl(i).attribute66,g_space);
606 x_adj_tbl(l_tbl_count).attribute67 := NVL(l_adj_tbl(i).attribute67,g_space);
607 x_adj_tbl(l_tbl_count).attribute68 := NVL(l_adj_tbl(i).attribute68,g_space);
608 x_adj_tbl(l_tbl_count).attribute69 := NVL(l_adj_tbl(i).attribute69,g_space);
609 x_adj_tbl(l_tbl_count).attribute70 := NVL(l_adj_tbl(i).attribute70,g_space);
610 x_adj_tbl(l_tbl_count).attribute71 := NVL(l_adj_tbl(i).attribute71,g_space);
611 x_adj_tbl(l_tbl_count).attribute72 := NVL(l_adj_tbl(i).attribute72,g_space);
612 x_adj_tbl(l_tbl_count).attribute73 := NVL(l_adj_tbl(i).attribute73,g_space);
613 x_adj_tbl(l_tbl_count).attribute74 := NVL(l_adj_tbl(i).attribute74,g_space);
614 x_adj_tbl(l_tbl_count).attribute75 := NVL(l_adj_tbl(i).attribute75,g_space);
615 x_adj_tbl(l_tbl_count).attribute76 := NVL(l_adj_tbl(i).attribute76,g_space);
616 x_adj_tbl(l_tbl_count).attribute77 := NVL(l_adj_tbl(i).attribute77,g_space);
617 x_adj_tbl(l_tbl_count).attribute78 := NVL(l_adj_tbl(i).attribute78,g_space);
618 x_adj_tbl(l_tbl_count).attribute79 := NVL(l_adj_tbl(i).attribute79,g_space);
619 x_adj_tbl(l_tbl_count).attribute80 := NVL(l_adj_tbl(i).attribute80,g_space);
620 x_adj_tbl(l_tbl_count).attribute81 := NVL(l_adj_tbl(i).attribute81,g_space);
621 x_adj_tbl(l_tbl_count).attribute82 := NVL(l_adj_tbl(i).attribute82,g_space);
622 x_adj_tbl(l_tbl_count).attribute83 := NVL(l_adj_tbl(i).attribute83,g_space);
623 x_adj_tbl(l_tbl_count).attribute84 := NVL(l_adj_tbl(i).attribute84,g_space);
624 x_adj_tbl(l_tbl_count).attribute85 := NVL(l_adj_tbl(i).attribute85,g_space);
625 x_adj_tbl(l_tbl_count).attribute86 := NVL(l_adj_tbl(i).attribute86,g_space);
626 x_adj_tbl(l_tbl_count).attribute87 := NVL(l_adj_tbl(i).attribute87,g_space);
627 x_adj_tbl(l_tbl_count).attribute88 := NVL(l_adj_tbl(i).attribute88,g_space);
628 x_adj_tbl(l_tbl_count).attribute89 := NVL(l_adj_tbl(i).attribute89,g_space);
629 x_adj_tbl(l_tbl_count).attribute90 := NVL(l_adj_tbl(i).attribute90,g_space);
630 x_adj_tbl(l_tbl_count).attribute91 := NVL(l_adj_tbl(i).attribute91,g_space);
631 x_adj_tbl(l_tbl_count).attribute92 := NVL(l_adj_tbl(i).attribute92,g_space);
632 x_adj_tbl(l_tbl_count).attribute93 := NVL(l_adj_tbl(i).attribute93,g_space);
633 x_adj_tbl(l_tbl_count).attribute94 := NVL(l_adj_tbl(i).attribute94,g_space);
634 x_adj_tbl(l_tbl_count).attribute95 := NVL(l_adj_tbl(i).attribute95,g_space);
635 x_adj_tbl(l_tbl_count).attribute96 := NVL(l_adj_tbl(i).attribute96,g_space);
636 x_adj_tbl(l_tbl_count).attribute97 := NVL(l_adj_tbl(i).attribute97,g_space);
637 x_adj_tbl(l_tbl_count).attribute98 := NVL(l_adj_tbl(i).attribute98,g_space);
638 x_adj_tbl(l_tbl_count).attribute99 := NVL(l_adj_tbl(i).attribute99,g_space);
639 x_adj_tbl(l_tbl_count).attribute100 := NVL(l_adj_tbl(i).attribute100,g_space);
640 x_adj_tbl(l_tbl_count).quota_id := NVL(l_adj_tbl(i).quota_id,0);
641 x_adj_tbl(l_tbl_count).quota_name := NVL(l_adj_tbl(i).quota_name,g_space);
642 x_adj_tbl(l_tbl_count).revenue_class_id := NVL(l_adj_tbl(i).revenue_class_id,0);
643 x_adj_tbl(l_tbl_count).revenue_class_name := NVL(l_adj_tbl(i).revenue_class_name,g_space);
644 x_adj_tbl(l_tbl_count).trx_batch_name := NVL(l_adj_tbl(i).trx_batch_name,g_space);
645 x_adj_tbl(l_tbl_count).source_trx_number := NVL(l_adj_tbl(i).source_trx_number,g_space);
646 x_adj_tbl(l_tbl_count).trx_sales_line_id := NVL(l_adj_tbl(i).trx_sales_line_id,0);
647 x_adj_tbl(l_tbl_count).trx_line_id := NVL(l_adj_tbl(i).trx_line_id,0);
648 x_adj_tbl(l_tbl_count).trx_id := NVL(l_adj_tbl(i).trx_id,0);
649 x_adj_tbl(l_tbl_count).comm_lines_api_id := NVL(l_adj_tbl(i).comm_lines_api_id,0);
650 x_adj_tbl(l_tbl_count).source_doc_type := NVL(l_adj_tbl(i).source_doc_type,g_space);
651 x_adj_tbl(l_tbl_count).upside_amount := NVL(l_adj_tbl(i).upside_amount,0);
652 x_adj_tbl(l_tbl_count).upside_quantity := NVL(l_adj_tbl(i).upside_quantity,0);
653 x_adj_tbl(l_tbl_count).uom_code := NVL(l_adj_tbl(i).uom_code,'N/A');
654 x_adj_tbl(l_tbl_count).forecast_id := NVL(l_adj_tbl(i).forecast_id,0);
655 x_adj_tbl(l_tbl_count).program_id := NVL(l_adj_tbl(i).program_id,0);
656 x_adj_tbl(l_tbl_count).request_id := NVL(l_adj_tbl(i).request_id,0);
657 x_adj_tbl(l_tbl_count).program_application_id := NVL(l_adj_tbl(i).program_application_id,0);
658 x_adj_tbl(l_tbl_count).program_update_date := l_adj_tbl(i).program_update_date;
659 x_adj_tbl(l_tbl_count).adj_comm_lines_api_id:= NVL(l_adj_tbl(i).adj_comm_lines_api_id,0);
660 x_adj_tbl(l_tbl_count).invoice_number := NVL(l_adj_tbl(i).invoice_number,g_space);
661 x_adj_tbl(l_tbl_count).invoice_date := l_adj_tbl(i).invoice_date;
662 x_adj_tbl(l_tbl_count).order_number := NVL(l_adj_tbl(i).order_number,0);
663 x_adj_tbl(l_tbl_count).order_date := l_adj_tbl(i).order_date;
664 x_adj_tbl(l_tbl_count).line_number := NVL(l_adj_tbl(i).line_number,0);
665 x_adj_tbl(l_tbl_count).customer_id := NVL(l_adj_tbl(i).customer_id,0);
666 x_adj_tbl(l_tbl_count).bill_to_address_id := l_adj_tbl(i).bill_to_address_id;
667 x_adj_tbl(l_tbl_count).ship_to_address_id := l_adj_tbl(i).ship_to_address_id;
668 x_adj_tbl(l_tbl_count).bill_to_contact_id := l_adj_tbl(i).bill_to_contact_id;
669 x_adj_tbl(l_tbl_count).ship_to_contact_id := l_adj_tbl(i).ship_to_contact_id;
670 x_adj_tbl(l_tbl_count).load_status := NVL(l_adj_tbl(i).load_status,g_space);
671 x_adj_tbl(l_tbl_count).revenue_type_disp := NVL(l_adj_tbl(i).revenue_type_disp,g_space);
672 x_adj_tbl(l_tbl_count).revenue_type := NVL(l_adj_tbl(i).revenue_type,g_space);
673 x_adj_tbl(l_tbl_count).adjust_rollup_flag := l_adj_tbl(i).adjust_rollup_flag;
674 x_adj_tbl(l_tbl_count).adjust_date := l_adj_tbl(i).adjust_date;
675 x_adj_tbl(l_tbl_count).adjusted_by := l_adj_tbl(i).adjusted_by;
676 x_adj_tbl(l_tbl_count).adjust_status_disp := NVL(l_adj_tbl(i).adjust_status_disp,g_space);
677 x_adj_tbl(l_tbl_count).adjust_status := NVL(l_adj_tbl(i).adjust_status,g_space);
678 x_adj_tbl(l_tbl_count).adjust_comments := l_adj_tbl(i).adjust_comments;
679 x_adj_tbl(l_tbl_count).type := l_adj_tbl(i).type;
680 x_adj_tbl(l_tbl_count).pre_processed_code := l_adj_tbl(i).pre_processed_code;
681 x_adj_tbl(l_tbl_count).comp_group_id := l_adj_tbl(i).comp_group_id;
682 x_adj_tbl(l_tbl_count).srp_plan_assign_id := l_adj_tbl(i).srp_plan_assign_id;
683 x_adj_tbl(l_tbl_count).role_id := l_adj_tbl(i).role_id;
684 x_adj_tbl(l_tbl_count).sales_channel := l_adj_tbl(i).sales_channel;
685 x_adj_tbl(l_tbl_count).object_version_number:= l_adj_tbl(i).object_version_number;
686 x_adj_tbl(l_tbl_count).split_pct := NVL(l_adj_tbl(i).split_pct,0);
687 x_adj_tbl(l_tbl_count).split_status := NVL(l_adj_tbl(i).split_status,g_space);
688 END IF;
689 END LOOP;
690 END IF;
691 x_adj_count := l_total_rows;
692 x_valid_trx_count := l_valid_trx_counter;
693 EXCEPTION
694 WHEN FND_API.G_EXC_ERROR THEN
695 ROLLBACK TO get_adj;
696 x_return_status := FND_API.G_RET_STS_ERROR ;
697 FND_MSG_PUB.Count_And_Get(
698 p_count => x_msg_count ,
699 p_data => x_msg_data ,
700 p_encoded => FND_API.G_FALSE);
701 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
702 ROLLBACK TO get_adj;
703 x_loading_status := 'UNEXPECTED_ERR';
704 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
705 FND_MSG_PUB.Count_And_Get(
706 p_count => x_msg_count ,
707 p_data => x_msg_data ,
708 p_encoded => FND_API.G_FALSE);
709 WHEN OTHERS THEN
710 ROLLBACK TO get_adj;
711 x_loading_status := 'UNEXPECTED_ERR';
712 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
713 IF FND_MSG_PUB.Check_Msg_Level(
714 FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
715 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
716 END IF;
717 FND_MSG_PUB.Count_And_Get(
718 p_count => x_msg_count ,
719 p_data => x_msg_data ,
720 p_encoded => FND_API.G_FALSE);
721 END;
722 --
723 PROCEDURE get_split_data(
724 p_api_version IN NUMBER,
725 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
726 p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL,
727 p_comm_lines_api_id IN NUMBER DEFAULT NULL,
728 p_load_status IN VARCHAR2 DEFAULT NULL,
729 x_return_status OUT NOCOPY VARCHAR2,
730 x_msg_count OUT NOCOPY NUMBER,
731 x_msg_data OUT NOCOPY VARCHAR2,
732 x_loading_status OUT NOCOPY VARCHAR2,
733 x_adj_tbl OUT NOCOPY adj_tbl_type,
734 x_adj_count OUT NOCOPY NUMBER) IS
735 CURSOR api_cur IS
736 SELECT l.*,s.employee_number srp_employee_number,s.name,
737 clad.meaning adjust_status_disp,
738 clt.meaning trx_type_disp
739 FROM cn_comm_lines_api l,
740 cn_salesreps s,
741 cn_lookups clad,
742 cn_lookups clt
743 WHERE l.trx_type = clt.lookup_code(+)
744 AND clt.lookup_type (+)= 'TRX TYPES'
745 AND l.adjust_status = clad.lookup_code(+)
746 AND clad.lookup_type (+)= 'ADJUST_STATUS'
747 AND l.comm_lines_api_id = p_comm_lines_api_id
748 AND l.salesrep_id = s.salesrep_id
749 AND (adjust_status NOT IN ('FROZEN','REVERSAL') )--OR
750 -- adjust_status IS NULL)
751 AND trx_type NOT IN ('ITD','GRP','THR');
752 --
753 CURSOR header_cur IS
754 SELECT h.*,s.employee_number,s.name,
755 clad.meaning adjust_status_disp,
756 clt.meaning trx_type_disp
757 FROM cn_commission_headers h,
758 cn_salesreps s,
759 cn_lookups clad,
760 cn_lookups clt
761 WHERE h.trx_type = clt.lookup_code(+)
762 AND clt.lookup_type (+)= 'TRX TYPES'
763 AND h.adjust_status = clad.lookup_code(+)
764 AND clad.lookup_type (+)= 'ADJUST_STATUS'
765 AND h.comm_lines_api_id = p_comm_lines_api_id
766 AND h.direct_salesrep_id = s.salesrep_id
767 AND (adjust_status NOT IN ('FROZEN','REVERSAL') )--OR
768 -- adjust_status IS NULL)
769 AND trx_type NOT IN ('ITD','GRP','THR');
770 --
771 l_api_name CONSTANT VARCHAR2(30) := 'get_split_data';
772 l_api_version CONSTANT NUMBER := 1.0;
773 l_tbl_count NUMBER := 1;
774 l_cg_flag CHAR(1) := 'N';
775 l_rc_flag CHAR(1) := 'N';
776 l_quota_flag CHAR(1) := 'N';
777 l_role_flag CHAR(1) := 'N';
778 l_cust_flag CHAR(1) := 'N';
779 l_cg_id NUMBER;
780 l_rc_id NUMBER;
781 l_quota_id NUMBER;
782 l_role_id NUMBER;
783 l_cust_id NUMBER;
784 l_space VARCHAR2(10) := '&'||'nbsp;';
785 --
786 BEGIN
787 -- Standard Start of API savepoint
788 SAVEPOINT get_split_data;
789 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
790 p_api_version ,
791 l_api_name,
792 G_PKG_NAME ) THEN
793 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
794 END IF;
795 -- Initialize message list if p_init_msg_list is set to TRUE.
796 IF FND_API.to_Boolean( p_init_msg_list ) THEN
797 FND_MSG_PUB.initialize;
798 END IF;
799 -- Initialize API return status to success
800 x_return_status := FND_API.G_RET_STS_SUCCESS;
801 x_loading_status := 'CN_INSERTED';
802
803 -- API Body Begin
804 IF (p_load_status <> 'LOADED') THEN
805 FOR adj IN api_cur
806 LOOP
807 x_adj_tbl(l_tbl_count).direct_salesrep_number := NVL(adj.srp_employee_number,l_space);
808 x_adj_tbl(l_tbl_count).direct_salesrep_name := NVL(adj.name,l_space);
809 x_adj_tbl(l_tbl_count).direct_salesrep_id := NVL(adj.salesrep_id,0);
810 x_adj_tbl(l_tbl_count).processed_period_id := NVL(adj.processed_period_id,0);
811 x_adj_tbl(l_tbl_count).processed_date := adj.processed_date;
812 x_adj_tbl(l_tbl_count).rollup_date := adj.rollup_date;
813 x_adj_tbl(l_tbl_count).transaction_amount := adj.acctd_transaction_amount;
814 x_adj_tbl(l_tbl_count).transaction_amount_orig := adj.transaction_amount;
815 x_adj_tbl(l_tbl_count).quantity := adj.quantity;
816 x_adj_tbl(l_tbl_count).discount_percentage := adj.discount_percentage;
817 x_adj_tbl(l_tbl_count).margin_percentage := adj.margin_percentage;
818 x_adj_tbl(l_tbl_count).orig_currency_code := adj.transaction_currency_code;
819 x_adj_tbl(l_tbl_count).exchange_rate := adj.exchange_rate;
820 x_adj_tbl(l_tbl_count).reason_code := NVL(adj.reason_code,l_space);
821 x_adj_tbl(l_tbl_count).comments := l_space;
822 x_adj_tbl(l_tbl_count).attribute_category := NVL(adj.attribute_category,l_space);
823 x_adj_tbl(l_tbl_count).attribute1 := NVL(adj.attribute1,l_space);
824 x_adj_tbl(l_tbl_count).attribute2 := NVL(adj.attribute2,l_space);
825 x_adj_tbl(l_tbl_count).attribute3 := NVL(adj.attribute3,l_space);
826 x_adj_tbl(l_tbl_count).attribute4 := NVL(adj.attribute4,l_space);
827 x_adj_tbl(l_tbl_count).attribute5 := NVL(adj.attribute5,l_space);
828 x_adj_tbl(l_tbl_count).attribute6 := NVL(adj.attribute6,l_space);
829 x_adj_tbl(l_tbl_count).attribute7 := NVL(adj.attribute7,l_space);
830 x_adj_tbl(l_tbl_count).attribute8 := NVL(adj.attribute8,l_space);
831 x_adj_tbl(l_tbl_count).attribute9 := NVL(adj.attribute9,l_space);
832 x_adj_tbl(l_tbl_count).attribute10 := NVL(adj.attribute10,l_space);
833 x_adj_tbl(l_tbl_count).attribute11 := NVL(adj.attribute11,l_space);
834 x_adj_tbl(l_tbl_count).attribute12 := NVL(adj.attribute12,l_space);
835 x_adj_tbl(l_tbl_count).attribute13 := NVL(adj.attribute13,l_space);
836 x_adj_tbl(l_tbl_count).attribute14 := NVL(adj.attribute14,l_space);
837 x_adj_tbl(l_tbl_count).attribute15 := NVL(adj.attribute15,l_space);
838 x_adj_tbl(l_tbl_count).attribute16 := NVL(adj.attribute16,l_space);
839 x_adj_tbl(l_tbl_count).attribute17 := NVL(adj.attribute17,l_space);
840 x_adj_tbl(l_tbl_count).attribute18 := NVL(adj.attribute18,l_space);
841 x_adj_tbl(l_tbl_count).attribute19 := NVL(adj.attribute19,l_space);
842 x_adj_tbl(l_tbl_count).attribute20 := NVL(adj.attribute20,l_space);
843 x_adj_tbl(l_tbl_count).attribute21 := NVL(adj.attribute21,l_space);
844 x_adj_tbl(l_tbl_count).attribute22 := NVL(adj.attribute22,l_space);
845 x_adj_tbl(l_tbl_count).attribute23 := NVL(adj.attribute23,l_space);
846 x_adj_tbl(l_tbl_count).attribute24 := NVL(adj.attribute24,l_space);
847 x_adj_tbl(l_tbl_count).attribute25 := NVL(adj.attribute25,l_space);
848 x_adj_tbl(l_tbl_count).attribute26 := NVL(adj.attribute26,l_space);
849 x_adj_tbl(l_tbl_count).attribute27 := NVL(adj.attribute27,l_space);
850 x_adj_tbl(l_tbl_count).attribute28 := NVL(adj.attribute28,l_space);
851 x_adj_tbl(l_tbl_count).attribute29 := NVL(adj.attribute29,l_space);
852 x_adj_tbl(l_tbl_count).attribute30 := NVL(adj.attribute30,l_space);
853 x_adj_tbl(l_tbl_count).attribute31 := NVL(adj.attribute31,l_space);
854 x_adj_tbl(l_tbl_count).attribute32 := NVL(adj.attribute32,l_space);
855 x_adj_tbl(l_tbl_count).attribute33 := NVL(adj.attribute33,l_space);
856 x_adj_tbl(l_tbl_count).attribute34 := NVL(adj.attribute34,l_space);
857 x_adj_tbl(l_tbl_count).attribute35 := NVL(adj.attribute35,l_space);
858 x_adj_tbl(l_tbl_count).attribute36 := NVL(adj.attribute36,l_space);
859 x_adj_tbl(l_tbl_count).attribute37 := NVL(adj.attribute37,l_space);
860 x_adj_tbl(l_tbl_count).attribute38 := NVL(adj.attribute38,l_space);
861 x_adj_tbl(l_tbl_count).attribute39 := NVL(adj.attribute39,l_space);
862 x_adj_tbl(l_tbl_count).attribute40 := NVL(adj.attribute40,l_space);
863 x_adj_tbl(l_tbl_count).attribute41 := NVL(adj.attribute41,l_space);
864 x_adj_tbl(l_tbl_count).attribute42 := NVL(adj.attribute42,l_space);
865 x_adj_tbl(l_tbl_count).attribute43 := NVL(adj.attribute43,l_space);
866 x_adj_tbl(l_tbl_count).attribute44 := NVL(adj.attribute44,l_space);
867 x_adj_tbl(l_tbl_count).attribute45 := NVL(adj.attribute45,l_space);
868 x_adj_tbl(l_tbl_count).attribute46 := NVL(adj.attribute46,l_space);
869 x_adj_tbl(l_tbl_count).attribute47 := NVL(adj.attribute47,l_space);
870 x_adj_tbl(l_tbl_count).attribute48 := NVL(adj.attribute48,l_space);
871 x_adj_tbl(l_tbl_count).attribute49 := NVL(adj.attribute49,l_space);
872 x_adj_tbl(l_tbl_count).attribute50 := NVL(adj.attribute50,l_space);
873 x_adj_tbl(l_tbl_count).attribute51 := NVL(adj.attribute51,l_space);
874 x_adj_tbl(l_tbl_count).attribute52 := NVL(adj.attribute52,l_space);
875 x_adj_tbl(l_tbl_count).attribute53 := NVL(adj.attribute53,l_space);
876 x_adj_tbl(l_tbl_count).attribute54 := NVL(adj.attribute54,l_space);
877 x_adj_tbl(l_tbl_count).attribute55 := NVL(adj.attribute55,l_space);
878 x_adj_tbl(l_tbl_count).attribute56 := NVL(adj.attribute56,l_space);
879 x_adj_tbl(l_tbl_count).attribute57 := NVL(adj.attribute57,l_space);
880 x_adj_tbl(l_tbl_count).attribute58 := NVL(adj.attribute58,l_space);
881 x_adj_tbl(l_tbl_count).attribute59 := NVL(adj.attribute59,l_space);
882 x_adj_tbl(l_tbl_count).attribute60 := NVL(adj.attribute60,l_space);
883 x_adj_tbl(l_tbl_count).attribute61 := NVL(adj.attribute61,l_space);
884 x_adj_tbl(l_tbl_count).attribute62 := NVL(adj.attribute62,l_space);
885 x_adj_tbl(l_tbl_count).attribute63 := NVL(adj.attribute63,l_space);
886 x_adj_tbl(l_tbl_count).attribute64 := NVL(adj.attribute64,l_space);
887 x_adj_tbl(l_tbl_count).attribute65 := NVL(adj.attribute65,l_space);
888 x_adj_tbl(l_tbl_count).attribute66 := NVL(adj.attribute66,l_space);
889 x_adj_tbl(l_tbl_count).attribute67 := NVL(adj.attribute67,l_space);
890 x_adj_tbl(l_tbl_count).attribute68 := NVL(adj.attribute68,l_space);
891 x_adj_tbl(l_tbl_count).attribute69 := NVL(adj.attribute69,l_space);
892 x_adj_tbl(l_tbl_count).attribute70 := NVL(adj.attribute70,l_space);
893 x_adj_tbl(l_tbl_count).attribute71 := NVL(adj.attribute71,l_space);
894 x_adj_tbl(l_tbl_count).attribute72 := NVL(adj.attribute72,l_space);
895 x_adj_tbl(l_tbl_count).attribute73 := NVL(adj.attribute73,l_space);
896 x_adj_tbl(l_tbl_count).attribute74 := NVL(adj.attribute74,l_space);
897 x_adj_tbl(l_tbl_count).attribute75 := NVL(adj.attribute75,l_space);
898 x_adj_tbl(l_tbl_count).attribute76 := NVL(adj.attribute76,l_space);
899 x_adj_tbl(l_tbl_count).attribute77 := NVL(adj.attribute77,l_space);
900 x_adj_tbl(l_tbl_count).attribute78 := NVL(adj.attribute78,l_space);
901 x_adj_tbl(l_tbl_count).attribute79 := NVL(adj.attribute79,l_space);
902 x_adj_tbl(l_tbl_count).attribute80 := NVL(adj.attribute80,l_space);
903 x_adj_tbl(l_tbl_count).attribute81 := NVL(adj.attribute81,l_space);
904 x_adj_tbl(l_tbl_count).attribute82 := NVL(adj.attribute82,l_space);
905 x_adj_tbl(l_tbl_count).attribute83 := NVL(adj.attribute83,l_space);
906 x_adj_tbl(l_tbl_count).attribute84 := NVL(adj.attribute84,l_space);
907 x_adj_tbl(l_tbl_count).attribute85 := NVL(adj.attribute85,l_space);
908 x_adj_tbl(l_tbl_count).attribute86 := NVL(adj.attribute86,l_space);
909 x_adj_tbl(l_tbl_count).attribute87 := NVL(adj.attribute87,l_space);
910 x_adj_tbl(l_tbl_count).attribute88 := NVL(adj.attribute88,l_space);
911 x_adj_tbl(l_tbl_count).attribute89 := NVL(adj.attribute89,l_space);
912 x_adj_tbl(l_tbl_count).attribute90 := NVL(adj.attribute90,l_space);
913 x_adj_tbl(l_tbl_count).attribute91 := NVL(adj.attribute91,l_space);
914 x_adj_tbl(l_tbl_count).attribute92 := NVL(adj.attribute92,l_space);
915 x_adj_tbl(l_tbl_count).attribute93 := NVL(adj.attribute93,l_space);
916 x_adj_tbl(l_tbl_count).attribute94 := NVL(adj.attribute94,l_space);
917 x_adj_tbl(l_tbl_count).attribute95 := NVL(adj.attribute95,l_space);
918 x_adj_tbl(l_tbl_count).attribute96 := NVL(adj.attribute96,l_space);
919 x_adj_tbl(l_tbl_count).attribute97 := NVL(adj.attribute97,l_space);
920 x_adj_tbl(l_tbl_count).attribute98 := NVL(adj.attribute98,l_space);
921 x_adj_tbl(l_tbl_count).attribute99 := NVL(adj.attribute99,l_space);
922 x_adj_tbl(l_tbl_count).attribute100 := NVL(adj.attribute100,l_space);
923 x_adj_tbl(l_tbl_count).quota_id := NVL(adj.quota_id,0);
924 x_adj_tbl(l_tbl_count).comm_lines_api_id := NVL(adj.comm_lines_api_id,0);
925 x_adj_tbl(l_tbl_count).source_doc_type := adj.source_doc_type;
926 x_adj_tbl(l_tbl_count).upside_amount := NVL(adj.upside_amount,0);
927 x_adj_tbl(l_tbl_count).upside_quantity := NVL(adj.upside_quantity,0);
928 x_adj_tbl(l_tbl_count).uom_code := NVL(adj.uom_code,'N/A');
929 x_adj_tbl(l_tbl_count).forecast_id := NVL(adj.forecast_id,0);
930 x_adj_tbl(l_tbl_count).invoice_number := adj.invoice_number;
931 x_adj_tbl(l_tbl_count).invoice_date := adj.invoice_date;
932 x_adj_tbl(l_tbl_count).order_number := adj.order_number;
933 x_adj_tbl(l_tbl_count).order_date := adj.booked_date;
934 x_adj_tbl(l_tbl_count).line_number := adj.line_number;
935 x_adj_tbl(l_tbl_count).load_status := NVL(adj.load_status,l_space);
936 x_adj_tbl(l_tbl_count).revenue_type := NVL(adj.revenue_type,l_space);
937 x_adj_tbl(l_tbl_count).adjust_rollup_flag := adj.adjust_rollup_flag;
938 x_adj_tbl(l_tbl_count).adjust_date := adj.adjust_date;
939 x_adj_tbl(l_tbl_count).adjusted_by := adj.adjusted_by;
940 x_adj_tbl(l_tbl_count).adjust_status := NVL(adj.adjust_status,l_space);
941 x_adj_tbl(l_tbl_count).adjust_status_disp := NVL(adj.adjust_status_disp,l_space);
942 x_adj_tbl(l_tbl_count).adjust_comments := adj.adjust_comments;
943 x_adj_tbl(l_tbl_count).type := adj.type;
944 x_adj_tbl(l_tbl_count).pre_processed_code := adj.pre_processed_code;
945 x_adj_tbl(l_tbl_count).comp_group_id := adj.comp_group_id;
946 x_adj_tbl(l_tbl_count).srp_plan_assign_id := adj.srp_plan_assign_id;
947 x_adj_tbl(l_tbl_count).role_id := adj.role_id;
948 x_adj_tbl(l_tbl_count).sales_channel := adj.sales_channel;
949 x_adj_tbl(l_tbl_count).object_version_number := adj.object_version_number;
950 x_adj_tbl(l_tbl_count).split_pct := adj.split_pct;
951 x_adj_tbl(l_tbl_count).split_status := adj.split_status;
952 x_adj_tbl(l_tbl_count).commission_amount := adj.commission_amount;
953 x_adj_tbl(l_tbl_count).revenue_class_id := adj.revenue_class_id;
954 x_adj_tbl(l_tbl_count).trx_type_disp := adj.trx_type_disp;
955 x_adj_tbl(l_tbl_count).inventory_item_id := adj.inventory_item_id;
956 x_adj_tbl(l_tbl_count).source_trx_id := adj.source_trx_id;
957 x_adj_tbl(l_tbl_count).source_trx_line_id := adj.source_trx_line_id;
958 x_adj_tbl(l_tbl_count).source_trx_sales_line_id := adj.source_trx_sales_line_id;
959
960 IF (adj.comp_group_id IS NOT NULL) THEN
961 l_cg_flag := 'Y';
962 l_cg_id := adj.comp_group_id;
963 END IF;
964 IF (adj.revenue_class_id IS NOT NULL) THEN
965 l_rc_flag := 'Y';
966 l_rc_id := adj.revenue_class_id;
967 END IF;
968 IF (adj.quota_id IS NOT NULL) THEN
969 l_quota_flag := 'Y';
970 l_quota_id := adj.quota_id;
971 END IF;
972 IF (adj.role_id IS NOT NULL) THEN
973 l_role_flag := 'Y';
974 l_role_id := adj.role_id;
975 END IF;
976 IF (adj.customer_id IS NOT NULL) THEN
977 l_cust_flag := 'Y';
978 l_cust_id := adj.customer_id;
979 END IF;
980 END LOOP;
981 ELSIF (p_load_status = 'LOADED') THEN
982 FOR adj IN header_cur
983 LOOP
984 x_adj_tbl(l_tbl_count).direct_salesrep_number := NVL(adj.employee_number,l_space);
985 x_adj_tbl(l_tbl_count).direct_salesrep_name := NVL(adj.name,l_space);
986 x_adj_tbl(l_tbl_count).direct_salesrep_id := NVL(adj.direct_salesrep_id,0);
987 x_adj_tbl(l_tbl_count).processed_period_id := NVL(adj.processed_period_id,0);
988 x_adj_tbl(l_tbl_count).processed_date := adj.processed_date;
989 x_adj_tbl(l_tbl_count).rollup_date := adj.rollup_date;
990 x_adj_tbl(l_tbl_count).transaction_amount := adj.transaction_amount;
991 x_adj_tbl(l_tbl_count).transaction_amount_orig := adj.transaction_amount_orig;
992 x_adj_tbl(l_tbl_count).quantity := adj.quantity;
993 x_adj_tbl(l_tbl_count).discount_percentage := adj.discount_percentage;
994 x_adj_tbl(l_tbl_count).margin_percentage := adj.margin_percentage;
995 x_adj_tbl(l_tbl_count).orig_currency_code := adj.orig_currency_code;
996 x_adj_tbl(l_tbl_count).exchange_rate := adj.exchange_rate;
997 x_adj_tbl(l_tbl_count).reason_code := NVL(adj.reason_code,l_space);
998 x_adj_tbl(l_tbl_count).comments := NVL(adj.comments,l_space);
999 x_adj_tbl(l_tbl_count).attribute_category := NVL(adj.attribute_category,l_space);
1000 x_adj_tbl(l_tbl_count).attribute1 := NVL(adj.attribute1,l_space);
1001 x_adj_tbl(l_tbl_count).attribute2 := NVL(adj.attribute2,l_space);
1002 x_adj_tbl(l_tbl_count).attribute3 := NVL(adj.attribute3,l_space);
1003 x_adj_tbl(l_tbl_count).attribute4 := NVL(adj.attribute4,l_space);
1004 x_adj_tbl(l_tbl_count).attribute5 := NVL(adj.attribute5,l_space);
1005 x_adj_tbl(l_tbl_count).attribute6 := NVL(adj.attribute6,l_space);
1006 x_adj_tbl(l_tbl_count).attribute7 := NVL(adj.attribute7,l_space);
1007 x_adj_tbl(l_tbl_count).attribute8 := NVL(adj.attribute8,l_space);
1008 x_adj_tbl(l_tbl_count).attribute9 := NVL(adj.attribute9,l_space);
1009 x_adj_tbl(l_tbl_count).attribute10 := NVL(adj.attribute10,l_space);
1010 x_adj_tbl(l_tbl_count).attribute11 := NVL(adj.attribute11,l_space);
1011 x_adj_tbl(l_tbl_count).attribute12 := NVL(adj.attribute12,l_space);
1012 x_adj_tbl(l_tbl_count).attribute13 := NVL(adj.attribute13,l_space);
1013 x_adj_tbl(l_tbl_count).attribute14 := NVL(adj.attribute14,l_space);
1014 x_adj_tbl(l_tbl_count).attribute15 := NVL(adj.attribute15,l_space);
1015 x_adj_tbl(l_tbl_count).attribute16 := NVL(adj.attribute16,l_space);
1016 x_adj_tbl(l_tbl_count).attribute17 := NVL(adj.attribute17,l_space);
1017 x_adj_tbl(l_tbl_count).attribute18 := NVL(adj.attribute18,l_space);
1018 x_adj_tbl(l_tbl_count).attribute19 := NVL(adj.attribute19,l_space);
1019 x_adj_tbl(l_tbl_count).attribute20 := NVL(adj.attribute20,l_space);
1020 x_adj_tbl(l_tbl_count).attribute21 := NVL(adj.attribute21,l_space);
1021 x_adj_tbl(l_tbl_count).attribute22 := NVL(adj.attribute22,l_space);
1022 x_adj_tbl(l_tbl_count).attribute23 := NVL(adj.attribute23,l_space);
1023 x_adj_tbl(l_tbl_count).attribute24 := NVL(adj.attribute24,l_space);
1024 x_adj_tbl(l_tbl_count).attribute25 := NVL(adj.attribute25,l_space);
1025 x_adj_tbl(l_tbl_count).attribute26 := NVL(adj.attribute26,l_space);
1026 x_adj_tbl(l_tbl_count).attribute27 := NVL(adj.attribute27,l_space);
1027 x_adj_tbl(l_tbl_count).attribute28 := NVL(adj.attribute28,l_space);
1028 x_adj_tbl(l_tbl_count).attribute29 := NVL(adj.attribute29,l_space);
1029 x_adj_tbl(l_tbl_count).attribute30 := NVL(adj.attribute30,l_space);
1030 x_adj_tbl(l_tbl_count).attribute31 := NVL(adj.attribute31,l_space);
1031 x_adj_tbl(l_tbl_count).attribute32 := NVL(adj.attribute32,l_space);
1032 x_adj_tbl(l_tbl_count).attribute33 := NVL(adj.attribute33,l_space);
1033 x_adj_tbl(l_tbl_count).attribute34 := NVL(adj.attribute34,l_space);
1034 x_adj_tbl(l_tbl_count).attribute35 := NVL(adj.attribute35,l_space);
1035 x_adj_tbl(l_tbl_count).attribute36 := NVL(adj.attribute36,l_space);
1036 x_adj_tbl(l_tbl_count).attribute37 := NVL(adj.attribute37,l_space);
1037 x_adj_tbl(l_tbl_count).attribute38 := NVL(adj.attribute38,l_space);
1038 x_adj_tbl(l_tbl_count).attribute39 := NVL(adj.attribute39,l_space);
1039 x_adj_tbl(l_tbl_count).attribute40 := NVL(adj.attribute40,l_space);
1040 x_adj_tbl(l_tbl_count).attribute41 := NVL(adj.attribute41,l_space);
1041 x_adj_tbl(l_tbl_count).attribute42 := NVL(adj.attribute42,l_space);
1042 x_adj_tbl(l_tbl_count).attribute43 := NVL(adj.attribute43,l_space);
1043 x_adj_tbl(l_tbl_count).attribute44 := NVL(adj.attribute44,l_space);
1044 x_adj_tbl(l_tbl_count).attribute45 := NVL(adj.attribute45,l_space);
1045 x_adj_tbl(l_tbl_count).attribute46 := NVL(adj.attribute46,l_space);
1046 x_adj_tbl(l_tbl_count).attribute47 := NVL(adj.attribute47,l_space);
1047 x_adj_tbl(l_tbl_count).attribute48 := NVL(adj.attribute48,l_space);
1048 x_adj_tbl(l_tbl_count).attribute49 := NVL(adj.attribute49,l_space);
1049 x_adj_tbl(l_tbl_count).attribute50 := NVL(adj.attribute50,l_space);
1050 x_adj_tbl(l_tbl_count).attribute51 := NVL(adj.attribute51,l_space);
1051 x_adj_tbl(l_tbl_count).attribute52 := NVL(adj.attribute52,l_space);
1052 x_adj_tbl(l_tbl_count).attribute53 := NVL(adj.attribute53,l_space);
1053 x_adj_tbl(l_tbl_count).attribute54 := NVL(adj.attribute54,l_space);
1054 x_adj_tbl(l_tbl_count).attribute55 := NVL(adj.attribute55,l_space);
1055 x_adj_tbl(l_tbl_count).attribute56 := NVL(adj.attribute56,l_space);
1056 x_adj_tbl(l_tbl_count).attribute57 := NVL(adj.attribute57,l_space);
1057 x_adj_tbl(l_tbl_count).attribute58 := NVL(adj.attribute58,l_space);
1058 x_adj_tbl(l_tbl_count).attribute59 := NVL(adj.attribute59,l_space);
1059 x_adj_tbl(l_tbl_count).attribute60 := NVL(adj.attribute60,l_space);
1060 x_adj_tbl(l_tbl_count).attribute61 := NVL(adj.attribute61,l_space);
1061 x_adj_tbl(l_tbl_count).attribute62 := NVL(adj.attribute62,l_space);
1062 x_adj_tbl(l_tbl_count).attribute63 := NVL(adj.attribute63,l_space);
1063 x_adj_tbl(l_tbl_count).attribute64 := NVL(adj.attribute64,l_space);
1064 x_adj_tbl(l_tbl_count).attribute65 := NVL(adj.attribute65,l_space);
1065 x_adj_tbl(l_tbl_count).attribute66 := NVL(adj.attribute66,l_space);
1066 x_adj_tbl(l_tbl_count).attribute67 := NVL(adj.attribute67,l_space);
1067 x_adj_tbl(l_tbl_count).attribute68 := NVL(adj.attribute68,l_space);
1068 x_adj_tbl(l_tbl_count).attribute69 := NVL(adj.attribute69,l_space);
1069 x_adj_tbl(l_tbl_count).attribute70 := NVL(adj.attribute70,l_space);
1070 x_adj_tbl(l_tbl_count).attribute71 := NVL(adj.attribute71,l_space);
1071 x_adj_tbl(l_tbl_count).attribute72 := NVL(adj.attribute72,l_space);
1072 x_adj_tbl(l_tbl_count).attribute73 := NVL(adj.attribute73,l_space);
1073 x_adj_tbl(l_tbl_count).attribute74 := NVL(adj.attribute74,l_space);
1074 x_adj_tbl(l_tbl_count).attribute75 := NVL(adj.attribute75,l_space);
1075 x_adj_tbl(l_tbl_count).attribute76 := NVL(adj.attribute76,l_space);
1076 x_adj_tbl(l_tbl_count).attribute77 := NVL(adj.attribute77,l_space);
1077 x_adj_tbl(l_tbl_count).attribute78 := NVL(adj.attribute78,l_space);
1078 x_adj_tbl(l_tbl_count).attribute79 := NVL(adj.attribute79,l_space);
1079 x_adj_tbl(l_tbl_count).attribute80 := NVL(adj.attribute80,l_space);
1080 x_adj_tbl(l_tbl_count).attribute81 := NVL(adj.attribute81,l_space);
1081 x_adj_tbl(l_tbl_count).attribute82 := NVL(adj.attribute82,l_space);
1082 x_adj_tbl(l_tbl_count).attribute83 := NVL(adj.attribute83,l_space);
1083 x_adj_tbl(l_tbl_count).attribute84 := NVL(adj.attribute84,l_space);
1084 x_adj_tbl(l_tbl_count).attribute85 := NVL(adj.attribute85,l_space);
1085 x_adj_tbl(l_tbl_count).attribute86 := NVL(adj.attribute86,l_space);
1086 x_adj_tbl(l_tbl_count).attribute87 := NVL(adj.attribute87,l_space);
1087 x_adj_tbl(l_tbl_count).attribute88 := NVL(adj.attribute88,l_space);
1088 x_adj_tbl(l_tbl_count).attribute89 := NVL(adj.attribute89,l_space);
1089 x_adj_tbl(l_tbl_count).attribute90 := NVL(adj.attribute90,l_space);
1090 x_adj_tbl(l_tbl_count).attribute91 := NVL(adj.attribute91,l_space);
1091 x_adj_tbl(l_tbl_count).attribute92 := NVL(adj.attribute92,l_space);
1092 x_adj_tbl(l_tbl_count).attribute93 := NVL(adj.attribute93,l_space);
1093 x_adj_tbl(l_tbl_count).attribute94 := NVL(adj.attribute94,l_space);
1094 x_adj_tbl(l_tbl_count).attribute95 := NVL(adj.attribute95,l_space);
1095 x_adj_tbl(l_tbl_count).attribute96 := NVL(adj.attribute96,l_space);
1096 x_adj_tbl(l_tbl_count).attribute97 := NVL(adj.attribute97,l_space);
1097 x_adj_tbl(l_tbl_count).attribute98 := NVL(adj.attribute98,l_space);
1098 x_adj_tbl(l_tbl_count).attribute99 := NVL(adj.attribute99,l_space);
1099 x_adj_tbl(l_tbl_count).attribute100 := NVL(adj.attribute100,l_space);
1100 x_adj_tbl(l_tbl_count).quota_id := NVL(adj.quota_id,0);
1101 x_adj_tbl(l_tbl_count).comm_lines_api_id := NVL(adj.comm_lines_api_id,0);
1102 x_adj_tbl(l_tbl_count).source_doc_type := adj.source_doc_type;
1103 x_adj_tbl(l_tbl_count).upside_amount := NVL(adj.upside_amount,0);
1104 x_adj_tbl(l_tbl_count).upside_quantity := NVL(adj.upside_quantity,0);
1105 x_adj_tbl(l_tbl_count).uom_code := NVL(adj.uom_code,'N/A');
1106 x_adj_tbl(l_tbl_count).forecast_id := NVL(adj.forecast_id,0);
1107 x_adj_tbl(l_tbl_count).invoice_number := adj.invoice_number;
1108 x_adj_tbl(l_tbl_count).invoice_date := adj.invoice_date;
1109 x_adj_tbl(l_tbl_count).order_number := adj.order_number;
1110 x_adj_tbl(l_tbl_count).order_date := adj.booked_date;
1111 x_adj_tbl(l_tbl_count).line_number := adj.line_number;
1112 x_adj_tbl(l_tbl_count).load_status := 'LOADED';
1113 x_adj_tbl(l_tbl_count).revenue_type := adj.revenue_type;
1114 x_adj_tbl(l_tbl_count).adjust_rollup_flag := adj.adjust_rollup_flag;
1115 x_adj_tbl(l_tbl_count).adjust_date := adj.adjust_date;
1116 x_adj_tbl(l_tbl_count).adjusted_by := adj.adjusted_by;
1117 x_adj_tbl(l_tbl_count).adjust_status := NVL(adj.adjust_status,l_space);
1118 x_adj_tbl(l_tbl_count).adjust_status_disp := NVL(adj.adjust_status_disp,l_space);
1119 x_adj_tbl(l_tbl_count).adjust_comments := adj.adjust_comments;
1120 x_adj_tbl(l_tbl_count).type := adj.type;
1121 x_adj_tbl(l_tbl_count).pre_processed_code := adj.pre_processed_code;
1122 x_adj_tbl(l_tbl_count).comp_group_id := adj.comp_group_id;
1123 x_adj_tbl(l_tbl_count).srp_plan_assign_id := adj.srp_plan_assign_id;
1124 x_adj_tbl(l_tbl_count).role_id := adj.role_id;
1125 x_adj_tbl(l_tbl_count).sales_channel := adj.sales_channel;
1126 x_adj_tbl(l_tbl_count).object_version_number := adj.object_version_number;
1127 x_adj_tbl(l_tbl_count).split_pct := adj.split_pct;
1128 x_adj_tbl(l_tbl_count).split_status := adj.split_status;
1129 x_adj_tbl(l_tbl_count).commission_amount := adj.commission_amount;
1130 x_adj_tbl(l_tbl_count).revenue_class_id := adj.revenue_class_id;
1131 x_adj_tbl(l_tbl_count).trx_type_disp := adj.trx_type_disp;
1132 x_adj_tbl(l_tbl_count).inventory_item_id := adj.inventory_item_id;
1133 x_adj_tbl(l_tbl_count).source_trx_id := adj.source_trx_id;
1134 x_adj_tbl(l_tbl_count).source_trx_line_id := adj.source_trx_line_id;
1135 x_adj_tbl(l_tbl_count).source_trx_sales_line_id := adj.source_trx_sales_line_id;
1136
1137 IF (adj.comp_group_id IS NOT NULL) THEN
1138 l_cg_flag := 'Y';
1139 l_cg_id := adj.comp_group_id;
1140 END IF;
1141 IF (adj.revenue_class_id IS NOT NULL) THEN
1142 l_rc_flag := 'Y';
1143 l_rc_id := adj.revenue_class_id;
1144 END IF;
1145 IF (adj.quota_id IS NOT NULL) THEN
1146 l_quota_flag := 'Y';
1147 l_quota_id := adj.quota_id;
1148 END IF;
1149 IF (adj.role_id IS NOT NULL) THEN
1150 l_role_flag := 'Y';
1151 l_role_id := adj.role_id;
1152 END IF;
1153 IF (adj.customer_id IS NOT NULL) THEN
1154 l_cust_flag := 'Y';
1155 l_cust_id := adj.customer_id;
1156 END IF;
1157 END LOOP;
1158 END IF;
1159 --
1160 x_adj_count := x_adj_tbl.COUNT;
1161 --
1162 /* To improve the performance, these tables are not added into the main query */
1163 IF (l_cg_flag = 'Y') THEN
1164 BEGIN
1165 SELECT name
1166 INTO x_adj_tbl(1).comp_group_name
1167 FROM cn_comp_groups
1168 WHERE comp_group_id = l_cg_id
1169 AND rownum < 2;
1170 EXCEPTION
1171 WHEN OTHERS THEN
1172 NULL;
1173 END;
1174 END IF;
1175 --
1176 IF (l_rc_flag = 'Y') THEN
1177 BEGIN
1178 SELECT name
1179 INTO x_adj_tbl(1).revenue_class_name
1180 FROM cn_revenue_classes
1181 WHERE revenue_class_id = l_rc_id
1182 AND rownum < 2;
1183 EXCEPTION
1184 WHEN OTHERS THEN
1185 NULL;
1186 END;
1187 END IF;
1188 --
1189 IF (l_quota_flag = 'Y') THEN
1190 BEGIN
1191 SELECT name
1192 INTO x_adj_tbl(1).quota_name
1193 FROM cn_quotas
1194 WHERE quota_id = l_quota_id
1195 AND rownum < 2;
1196 EXCEPTION
1197 WHEN OTHERS THEN
1198 NULL;
1199 END;
1200 END IF;
1201 --
1202 IF (l_role_flag = 'Y') THEN
1203 BEGIN
1204 SELECT name
1205 INTO x_adj_tbl(1).role_name
1206 FROM cn_roles
1207 WHERE role_id = l_role_id
1208 AND rownum < 2;
1209 EXCEPTION
1210 WHEN OTHERS THEN
1211 NULL;
1212 END;
1213 END IF;
1214 --
1215 IF (l_cust_flag = 'Y') THEN
1216 BEGIN
1217 SELECT substrb(PARTY.PARTY_NAME,1,50),
1218 CUST_ACCT.ACCOUNT_NUMBER
1219 INTO x_adj_tbl(1).customer_name,
1220 x_adj_tbl(1).customer_number
1221 FROM HZ_PARTIES PARTY, HZ_CUST_ACCOUNTS CUST_ACCT
1222 WHERE CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
1223 AND CUST_ACCT.CUST_ACCOUNT_ID = l_cust_id
1224 AND rownum < 2;
1225 EXCEPTION
1226 WHEN OTHERS THEN
1227 NULL;
1228 END;
1229 END IF;
1230 --
1231 EXCEPTION
1232 WHEN FND_API.G_EXC_ERROR THEN
1233 ROLLBACK TO get_split_data;
1234 x_return_status := FND_API.G_RET_STS_ERROR ;
1235 FND_MSG_PUB.Count_And_Get(
1236 p_count => x_msg_count ,
1237 p_data => x_msg_data ,
1238 p_encoded => FND_API.G_FALSE);
1239 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1240 ROLLBACK TO get_split_data;
1241 x_loading_status := 'UNEXPECTED_ERR';
1242 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1243 FND_MSG_PUB.Count_And_Get(
1244 p_count => x_msg_count ,
1245 p_data => x_msg_data ,
1246 p_encoded => FND_API.G_FALSE);
1247 WHEN OTHERS THEN
1248 ROLLBACK TO get_split_data;
1249 x_loading_status := 'UNEXPECTED_ERR';
1250 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1251 IF FND_MSG_PUB.Check_Msg_Level(
1252 FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1253 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
1254 END IF;
1255 FND_MSG_PUB.Count_And_Get(
1256 p_count => x_msg_count ,
1257 p_data => x_msg_data ,
1258 p_encoded => FND_API.G_FALSE);
1259 END;
1260 --
1261 PROCEDURE insert_api_record(
1262 p_api_version IN NUMBER,
1263 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
1264 p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL,
1265 p_action IN VARCHAR2 DEFAULT NULL,
1266 p_newtx_rec IN adj_rec_type,
1267 x_api_id OUT NOCOPY NUMBER,
1268 x_return_status OUT NOCOPY VARCHAR2,
1269 x_msg_count OUT NOCOPY NUMBER,
1270 x_msg_data OUT NOCOPY VARCHAR2,
1271 x_loading_status OUT NOCOPY VARCHAR2) IS
1272 --
1273 l_api_name CONSTANT VARCHAR2(30) := 'insert_api_record';
1274 l_api_version CONSTANT NUMBER := 1.0;
1275 l_comm_lines_api_id NUMBER;
1276 l_functional_amt NUMBER;
1277 l_period_count NUMBER;
1278 l_processed_period_id NUMBER(15);
1279 l_return_status VARCHAR2(30);
1280 -- Who columns
1281 l_last_update_date DATE := sysdate;
1282 l_last_updated_by NUMBER := fnd_global.user_id;
1283 l_creation_date DATE := sysdate;
1284 l_created_by NUMBER := fnd_global.user_id;
1285 l_last_update_login NUMBER := fnd_global.login_id;
1286 -- PL/SQL tables/records
1287 l_newtx_rec adj_rec_type;
1288 l_api_rec cn_comm_lines_api_pkg.comm_lines_api_rec_type;
1289
1290 ------+
1291 -- Bug#2969534 Start
1292 ------+
1293
1294 l_comp_group_id cn_comp_groups.comp_group_id%TYPE;
1295 l_comp_group_name cn_comp_groups.name%TYPE;
1296
1297 l_rev_class_name cn_revenue_classes.name%TYPE;
1298 l_rev_class_id cn_revenue_classes.revenue_class_id%TYPE;
1299
1300 l_cust_id HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID%TYPE;
1301 l_cust_name HZ_PARTIES.PARTY_NAME%TYPE;
1302 l_cust_num HZ_CUST_ACCOUNTS.ACCOUNT_NUMBER%TYPE;
1303
1304 l_role_name cn_roles.name%TYPE;
1305 l_role_id cn_roles.role_id%TYPE;
1306
1307 l_pe_id cn_quotas.quota_id%TYPE;
1308 l_pe_name cn_quotas.name%TYPE;
1309
1310 ------+
1311 -- Bug#2969534 End
1312 ------+
1313
1314 BEGIN
1315 -- Standard Start of API savepoint
1316 SAVEPOINT insert_api_record;
1317 -- Standard call to check for call compatibility.
1318 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
1319 p_api_version ,
1320 l_api_name,
1321 G_PKG_NAME ) THEN
1322 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1323 END IF;
1324 -- Initialize message list if p_init_msg_list is set to TRUE.
1325 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1326 FND_MSG_PUB.initialize;
1327 END IF;
1328 -- Initialize API return status to success
1329 x_return_status := FND_API.G_RET_STS_SUCCESS;
1330 x_loading_status := 'CN_INSERTED';
1331 x_api_id := fnd_api.g_miss_num;
1332 -- API Body Begin
1333 -- Check for the open periods
1334 SELECT count(1)
1335 INTO l_period_count
1336 FROM cn_acc_period_statuses_v
1337 WHERE trunc(p_newtx_rec.processed_date)
1338 BETWEEN start_date AND end_date
1339 AND period_status IN ('O','F')
1340 AND org_id = p_newtx_rec.org_id;
1341 --
1342 IF (l_period_count = 0) THEN
1343 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
1344 FND_MESSAGE.Set_Name('CN', 'NOT_WITHIN_OPEN_PERIODS');
1345 FND_MSG_PUB.Add;
1346 END IF;
1347 x_loading_status := 'NOT_WITHIN_OPEN_PERIODS';
1348 RAISE FND_API.G_EXC_ERROR ;
1349 ELSE
1350 --
1351 BEGIN
1352 SELECT period_id
1353 INTO l_processed_period_id
1354 FROM cn_acc_period_statuses_v
1355 WHERE trunc(p_newtx_rec.processed_date)
1356 BETWEEN start_date AND end_date
1357 AND period_status IN ('O','F')
1358 AND org_id = p_newtx_rec.org_id;
1359 EXCEPTION
1360 WHEN OTHERS THEN
1361 NULL; -- I need to check once again to avoid NULL.
1362 END;
1363 --
1364 SELECT cn_comm_lines_api_s.NEXTVAL
1365 INTO l_comm_lines_api_id
1366 FROM SYS.DUAL;
1367 --
1368 l_newtx_rec := p_newtx_rec;
1369 l_newtx_rec.processed_period_id := l_processed_period_id;
1370 l_newtx_rec.comm_lines_api_id := l_comm_lines_api_id;
1371 /*---------------------------------------------------------------
1372 Functional/Foreign amount Logic
1373 Rule: Irrespective of API data or Header data foreign amount
1374 is always stored in adj_rec_type.transaction_amount_orig
1375 column and functional amount is stored in
1376 adj_rec_type.transaction_amount
1377 -----------------------------------------------------------------*/
1378 IF (l_newtx_rec.orig_currency_code IS NULL) THEN
1379 l_newtx_rec.orig_currency_code := cn_global_var.get_currency_code(p_newtx_rec.org_id);
1380 l_newtx_rec.exchange_rate := 1;
1381 IF (l_newtx_rec.transaction_amount_orig IS NOT NULL) THEN
1382 l_newtx_rec.transaction_amount := l_newtx_rec.transaction_amount_orig;
1383 ELSE
1384 l_newtx_rec.transaction_amount_orig
1385 := l_newtx_rec.transaction_amount;
1386 END IF;
1387 ELSE
1388 -- Foreign Amount to Functional Amount
1389 IF (l_newtx_rec.transaction_amount_orig IS NOT NULL) THEN
1390 IF ((l_newtx_rec.orig_currency_code = cn_global_var.get_currency_code(p_newtx_rec.org_id)) OR
1391 (l_newtx_rec.orig_currency_code = 'FUNC_CURR')) THEN
1392 l_newtx_rec.transaction_amount
1393 := l_newtx_rec.transaction_amount_orig;
1394 l_newtx_rec.exchange_rate := 1;
1395 ELSE
1396 IF (l_newtx_rec.exchange_rate IS NOT NULL) THEN
1397 l_newtx_rec.transaction_amount
1398 := (l_newtx_rec.transaction_amount_orig)*
1399 (l_newtx_rec.exchange_rate);
1400 ELSE
1401
1402 cn_mass_adjust_util.find_functional_amount(
1403 p_from_currency => l_newtx_rec.orig_currency_code,
1404 p_to_currency => cn_global_var.get_currency_code(p_newtx_rec.org_id),
1405 p_conversion_date => l_newtx_rec.processed_date,
1406 p_from_amount => l_newtx_rec.transaction_amount_orig,
1407 x_to_amount => l_newtx_rec.transaction_amount,
1408 x_return_status => l_return_status,
1409 p_conversion_type => CN_SYSTEM_PARAMETERS.VALUE('CN_CONVERSION_TYPE', p_newtx_rec.org_id));
1410 IF (l_return_status = 'NO DATA') THEN
1411 FND_MESSAGE.SET_NAME('CN','CN_ADJ_NO_CONVERSION');
1412 FND_MSG_PUB.Add;
1413 x_loading_status := 'CN_ADJ_NO_CONVERSION';
1414 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1415 ELSIF (l_return_status = 'ERROR') THEN
1416 FND_MESSAGE.SET_NAME('CN','CN_ADJ_CONV_ERROR');
1417 FND_MSG_PUB.Add;
1418 x_loading_status := 'CN_ADJ_NO_CONVERSION';
1419 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1420 END IF;
1421 END IF;
1422 END IF;
1423 ELSE
1424 -- Functional Amount to Foreign Amount
1425 IF ((l_newtx_rec.orig_currency_code = cn_global_var.get_currency_code(p_newtx_rec.org_id)) OR
1426 (l_newtx_rec.orig_currency_code = 'FUNC_CURR')) THEN
1427 l_newtx_rec.transaction_amount_orig
1428 := l_newtx_rec.transaction_amount;
1429 l_newtx_rec.exchange_rate := 1;
1430 ELSE
1431 -- In this case some times exchange rate will remain NULL only.
1432 cn_mass_adjust_util.find_functional_amount(
1433 p_from_currency => cn_global_var.get_currency_code(p_newtx_rec.org_id),
1434 p_to_currency => l_newtx_rec.orig_currency_code,
1435 p_conversion_date => l_newtx_rec.processed_date,
1436 p_from_amount => l_newtx_rec.transaction_amount,
1437 x_to_amount => l_newtx_rec.transaction_amount_orig,
1438 x_return_status => l_return_status,
1439 p_conversion_type => CN_SYSTEM_PARAMETERS.VALUE('CN_CONVERSION_TYPE', p_newtx_rec.org_id));
1440 IF (l_return_status = 'NO DATA') THEN
1441 FND_MESSAGE.SET_NAME('CN','CN_ADJ_NO_CONVERSION');
1442 FND_MSG_PUB.Add;
1443 x_loading_status := 'CN_ADJ_NO_CONVERSION';
1444 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1445 ELSIF (l_return_status = 'ERROR') THEN
1446 FND_MESSAGE.SET_NAME('CN','CN_ADJ_CONV_ERROR');
1447 FND_MSG_PUB.Add;
1448 x_loading_status := 'CN_ADJ_NO_CONVERSION';
1449 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1450 END IF;
1451 END IF;
1452 END IF;
1453 END IF;
1454
1455 /* most of the code for this module is based on cn_adjustment_v view
1456 and adj_rec_type is a record corresponding to this view. So
1457 we have to convert this record type to table handler record type
1458 before we call the table handler. */
1459 cn_invoice_changes_pvt.convert_adj_to_api(
1460 p_adj_rec => l_newtx_rec,
1461 x_api_rec => l_api_rec);
1462 --
1463 cn_comm_lines_api_pkg.insert_row(l_api_rec);
1464 --
1465 x_api_id := l_comm_lines_api_id;
1466 --
1467
1468 END IF;
1469 EXCEPTION
1470 WHEN FND_API.G_EXC_ERROR THEN
1471 ROLLBACK TO insert_api_record;
1472 x_return_status := FND_API.G_RET_STS_ERROR ;
1473 FND_MSG_PUB.Count_And_Get(
1474 p_count => x_msg_count ,
1475 p_data => x_msg_data ,
1476 p_encoded => FND_API.G_FALSE);
1477 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1478 ROLLBACK TO insert_api_record;
1479 x_loading_status := 'UNEXPECTED_ERR';
1480 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1481 FND_MSG_PUB.Count_And_Get(
1482 p_count => x_msg_count ,
1483 p_data => x_msg_data ,
1484 p_encoded => FND_API.G_FALSE);
1485 WHEN OTHERS THEN
1486 ROLLBACK TO insert_api_record;
1487 x_loading_status := 'UNEXPECTED_ERR';
1488 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1489 IF FND_MSG_PUB.Check_Msg_Level(
1490 FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1491 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
1492 END IF;
1493 FND_MSG_PUB.Count_And_Get(
1494 p_count => x_msg_count ,
1495 p_data => x_msg_data ,
1496 p_encoded => FND_API.G_FALSE);
1497 END;
1498 --
1499 PROCEDURE call_mass_update (
1500 p_api_version IN NUMBER,
1501 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
1502 p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL,
1503 p_org_id IN NUMBER := FND_API.G_MISS_NUM,
1504 p_salesrep_id IN NUMBER := FND_API.G_MISS_NUM,
1505 p_pr_date_to IN DATE := FND_API.G_MISS_DATE,
1506 p_pr_date_from IN DATE := FND_API.G_MISS_DATE,
1507 p_calc_status IN VARCHAR2 := FND_API.G_MISS_CHAR,
1508 p_adj_status IN VARCHAR2 := FND_API.G_MISS_CHAR,
1509 p_load_status IN VARCHAR2 := FND_API.G_MISS_CHAR,
1510 p_invoice_num IN VARCHAR2 := FND_API.G_MISS_CHAR,
1511 p_order_num IN NUMBER := FND_API.G_MISS_NUM,
1512 p_srch_attr_rec IN adj_rec_type,
1513 p_mass_adj_type IN VARCHAR2 DEFAULT NULL,
1514 p_adj_rec IN adj_rec_type,
1515 x_return_status OUT NOCOPY VARCHAR2,
1516 x_msg_count OUT NOCOPY NUMBER,
1517 x_msg_data OUT NOCOPY VARCHAR2,
1518 x_loading_status OUT NOCOPY VARCHAR2) IS
1519 -- Local Variables
1520 l_api_name CONSTANT VARCHAR2(30) := 'call_mass_update';
1521 l_api_version CONSTANT NUMBER := 1.0;
1522 l_tbl_count NUMBER := 0;
1523 l_adj_tbl adj_tbl_type;
1524 l_proc_comp VARCHAR2(10);
1525 l_api_query_flag CHAR(1) := 'Y';
1526 l_source_counter NUMBER := 0;
1527 l_return_status VARCHAR2(30);
1528 -- PL/SQL Tables and Records
1529 l_existing_data cn_invoice_changes_pvt.invoice_tbl;
1530 l_new_data cn_invoice_changes_pvt.invoice_tbl;
1531 l_adj_rec adj_rec_type;
1532 --
1533 BEGIN
1534 --cn_mydebug.delete;
1535 -- Standard Start of API savepoint
1536 SAVEPOINT call_mass_update;
1537 -- Standard call to check for call compatibility.
1538 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
1539 p_api_version ,
1540 l_api_name,
1541 G_PKG_NAME ) THEN
1542 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1543 END IF;
1544 -- Initialize message list if p_init_msg_list is set to TRUE.
1545 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1546 FND_MSG_PUB.initialize;
1547 END IF;
1548 -- Initialize API return status to success
1549 x_return_status := FND_API.G_RET_STS_SUCCESS;
1550 x_loading_status := 'CN_INSERTED';
1551 -- API body
1552 IF (g_track_invoice = 'Y') THEN
1553 /* First identify the unique invoices based on the search criteria
1554 and delete the records from cn_invoice_changes table based on
1555 these invoices */
1556 cn_invoice_changes_pvt.update_mass_invoices(
1557 p_api_version => l_api_version,
1558 p_salesrep_id => p_salesrep_id,
1559 p_pr_date_to => p_pr_date_to,
1560 p_pr_date_from => p_pr_date_from,
1561 p_calc_status => p_calc_status,
1562 p_invoice_num => p_invoice_num,
1563 p_order_num => p_order_num,
1564 p_srch_attr_rec => p_srch_attr_rec,
1565 p_to_salesrep_id => p_adj_rec.direct_salesrep_id,
1566 p_to_salesrep_number => p_adj_rec.direct_salesrep_number,
1567 x_return_status => x_return_status,
1568 x_msg_count => x_msg_count,
1569 x_msg_data => x_msg_data,
1570 x_loading_status => x_loading_status,
1571 x_existing_data => l_existing_data);
1572 --
1573 END IF;
1574
1575 --CN_mydebug.ADD('Counter value before Search : '||l_source_counter);
1576 --CN_mydebug.ADD('Adjusted table length before search : '||l_adj_tbl.count);
1577
1578
1579 /* This API take the parameters and construct the SQL. Based on the SQL
1580 get the data from cn_comm_lines_api and cn_commission_headers tables. */
1581 cn_mass_adjust_util.search_result(
1582 p_salesrep_id => p_salesrep_id,
1583 p_pr_date_to => p_pr_date_to,
1584 p_pr_date_from => p_pr_date_from,
1585 p_calc_status => p_calc_status,
1586 p_adj_status => p_adj_status,
1587 p_load_status => p_load_status,
1588 p_invoice_num => p_invoice_num,
1589 p_order_num => p_order_num,
1590 p_org_id => p_org_id,
1591 p_srch_attr_rec => p_srch_attr_rec,
1592 x_return_status => l_return_status,
1593 x_adj_tbl => l_adj_tbl,
1594 x_source_counter => l_source_counter);
1595 --
1596
1597 --CN_mydebug.ADD('Counter value after Search Result : '||l_source_counter);
1598 --CN_mydebug.ADD('Adjusted table length : '||l_adj_tbl.count);
1599
1600 cn_mass_adjust_util.convert_rec_to_gmiss(
1601 p_rec => p_adj_rec,
1602 x_api_rec => l_adj_rec);
1603
1604 l_adj_rec.adjusted_by := get_adjusted_by;
1605 /* This API negate the original transctions and create new
1606 transactions based on the l_adj_tbl we got from the above API */
1607 cn_adjustments_pkg.mass_update_values(
1608 x_adj_data => l_adj_tbl,
1609 x_adj_rec => l_adj_rec,
1610 X_mass_adj_type => p_mass_adj_type,
1611 X_proc_comp => l_proc_comp);
1612 --
1613 IF (l_proc_comp = 'E') THEN
1614 RAISE FND_API.G_EXC_ERROR;
1615 END IF;
1616 --
1617 IF (g_track_invoice = 'Y') THEN
1618 IF (l_existing_data.COUNT > 0) THEN
1619 -- Update the corresponding credit memos.
1620 cn_invoice_changes_pvt.update_credit_memo(
1621 p_api_version => l_api_version,
1622 p_existing_data => l_existing_data,
1623 p_new_data => l_new_data,
1624 p_to_salesrep_id => p_adj_rec.direct_salesrep_id,
1625 p_to_salesrep_number => p_adj_rec.direct_salesrep_number,
1626 p_called_from => 'MASS',
1627 p_adjust_status => 'MASSADJ',
1628 x_return_status => x_return_status,
1629 x_msg_count => x_msg_count,
1630 x_msg_data => x_msg_data,
1631 x_loading_status => x_loading_status);
1632 END IF;
1633 END IF;
1634 --
1635 EXCEPTION
1636 WHEN FND_API.G_EXC_ERROR THEN
1637 ROLLBACK TO call_mass_update;
1638 x_return_status := FND_API.G_RET_STS_ERROR ;
1639 FND_MSG_PUB.Count_And_Get(
1640 p_count => x_msg_count ,
1641 p_data => x_msg_data ,
1642 p_encoded => FND_API.G_FALSE);
1643 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1644 ROLLBACK TO call_mass_update;
1645 x_loading_status := 'UNEXPECTED_ERR';
1646 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1647 FND_MSG_PUB.Count_And_Get(
1648 p_count => x_msg_count ,
1649 p_data => x_msg_data ,
1650 p_encoded => FND_API.G_FALSE);
1651 WHEN OTHERS THEN
1652 ROLLBACK TO call_mass_update;
1653 x_loading_status := 'UNEXPECTED_ERR';
1654 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1655 IF FND_MSG_PUB.Check_Msg_Level(
1656 FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1657 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
1658 END IF;
1659 FND_MSG_PUB.Count_And_Get(
1660 p_count => x_msg_count ,
1661 p_data => x_msg_data ,
1662 p_encoded => FND_API.G_FALSE);
1663 END;
1664 -- This functionality is obsoleted.
1665 PROCEDURE call_deal_assign(
1666 p_api_version IN NUMBER,
1667 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
1668 p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL,
1669 p_from_salesrep_id IN NUMBER := FND_API.G_MISS_NUM,
1670 p_to_salesrep_id IN NUMBER := FND_API.G_MISS_NUM,
1671 p_invoice_number IN VARCHAR2 := FND_API.G_MISS_CHAR,
1672 p_order_number IN NUMBER := FND_API.G_MISS_NUM,
1673 p_adjusted_by IN VARCHAR2 := FND_GLOBAL.USER_NAME,
1674 p_adjust_comments IN VARCHAR2 := FND_API.G_MISS_CHAR,
1675 x_return_status OUT NOCOPY VARCHAR2,
1676 x_msg_count OUT NOCOPY NUMBER,
1677 x_msg_data OUT NOCOPY VARCHAR2,
1678 x_loading_status OUT NOCOPY VARCHAR2) IS
1679 --
1680 l_api_name CONSTANT VARCHAR2(30) := 'call_deal_assign';
1681 l_api_version CONSTANT NUMBER := 1.0;
1682 l_invoice_number cn_comm_lines_api.invoice_number%TYPE;
1683 l_order_number cn_comm_lines_api.order_number%TYPE;
1684 l_adjusted_by cn_comm_lines_api.adjusted_by%TYPE;
1685 l_adjust_comments cn_comm_lines_api.adjust_comments%TYPE;
1686 --
1687
1688 BEGIN
1689 -- Standard Start of API savepoint
1690 SAVEPOINT call_deal_assign;
1691
1692 -- Standard call to check for call compatibility.
1693 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
1694 p_api_version ,
1695 l_api_name,
1696 G_PKG_NAME ) THEN
1697 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1698 END IF;
1699 -- Initialize message list if p_init_msg_list is set to TRUE.
1700 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1701 FND_MSG_PUB.initialize;
1702 END IF;
1703 -- Initialize API return status to success
1704 x_return_status := FND_API.G_RET_STS_SUCCESS;
1705 x_loading_status := 'CN_INSERTED';
1706 -- API body
1707 IF (p_invoice_number = FND_API.G_MISS_CHAR) THEN
1708 l_invoice_number := NULL;
1709 END IF;
1710 IF (p_order_number = FND_API.G_MISS_NUM) THEN
1711 l_order_number := NULL;
1712 END IF;
1713 IF (p_adjust_comments = FND_API.G_MISS_CHAR) THEN
1714 l_adjust_comments := NULL;
1715 END IF;
1716 --
1717 cn_adjustments_pkg.deal_assign(
1718 x_from_salesrep_id => p_from_salesrep_id,
1719 x_to_salesrep_id => p_to_salesrep_id,
1720 x_invoice_number => l_invoice_number,
1721 x_order_number => l_order_number,
1722 x_adjusted_by => get_adjusted_by, -- Function
1723 x_adjust_comments => l_adjust_comments);
1724 --
1725 EXCEPTION
1726 WHEN FND_API.G_EXC_ERROR THEN
1727 ROLLBACK TO call_deal_assign;
1728 x_return_status := FND_API.G_RET_STS_ERROR ;
1729 FND_MSG_PUB.Count_And_Get(
1730 p_count => x_msg_count ,
1731 p_data => x_msg_data ,
1732 p_encoded => FND_API.G_FALSE);
1733 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1734 ROLLBACK TO call_deal_assign;
1735 x_loading_status := 'UNEXPECTED_ERR';
1736 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1737 FND_MSG_PUB.Count_And_Get(
1738 p_count => x_msg_count ,
1739 p_data => x_msg_data ,
1740 p_encoded => FND_API.G_FALSE);
1741 WHEN OTHERS THEN
1742 ROLLBACK TO call_deal_assign;
1743 x_loading_status := 'UNEXPECTED_ERR';
1744 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1745 IF FND_MSG_PUB.Check_Msg_Level(
1746 FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1747 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
1748 END IF;
1749 FND_MSG_PUB.Count_And_Get(
1750 p_count => x_msg_count ,
1751 p_data => x_msg_data ,
1752 p_encoded => FND_API.G_FALSE);
1753 END;
1754 --
1755 PROCEDURE call_split(
1756 p_api_version IN NUMBER,
1757 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
1758 p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL,
1759 p_split_type IN VARCHAR2,
1760 p_from_salesrep_id IN NUMBER := FND_API.G_MISS_NUM,
1761 p_split_data_tbl IN split_data_tbl,
1762 p_comm_lines_api_id IN NUMBER := FND_API.G_MISS_NUM,
1763 p_invoice_number IN VARCHAR2 := FND_API.G_MISS_CHAR,
1764 p_order_number IN NUMBER := FND_API.G_MISS_NUM,
1765 p_transaction_amount IN NUMBER,
1766 p_adjusted_by IN VARCHAR2 := FND_GLOBAL.USER_NAME,
1767 p_adjust_comments IN VARCHAR2 := FND_API.G_MISS_CHAR,
1768 p_org_id IN NUMBER := FND_API.G_MISS_NUM,
1769 x_return_status OUT NOCOPY VARCHAR2,
1770 x_msg_count OUT NOCOPY NUMBER,
1771 x_msg_data OUT NOCOPY VARCHAR2,
1772 x_loading_status OUT NOCOPY VARCHAR2) IS
1773 --
1774 l_api_name CONSTANT VARCHAR2(30) := 'call_split';
1775 l_api_version CONSTANT NUMBER := 1.0;
1776 l_split_percent NUMBER := 0;
1777 l_split_amount NUMBER := 0;
1778 l_comm_lines_api_id NUMBER;
1779 l_order_number NUMBER;
1780 l_org_id NUMBER;
1781 l_counter NUMBER := 0;
1782 l_id_counter NUMBER := 0;
1783 l_deal_count NUMBER := 0;
1784 l_deal_type VARCHAR2(30);
1785 l_split_to_all_nonrevenue_type
1786 VARCHAR2(1);
1787 l_split_nonrevenue_line
1788 VARCHAR2(1);
1789 l_trx_type VARCHAR2(30);
1790 l_data_exist VARCHAR2(1) := 'N';
1791 --
1792 --Added for Crediting Bug
1793 l_terr_id NUMBER;
1794 l_terr_name VARCHAR2(2000);
1795
1796 -- PL/SQL tables/records
1797 l_newtx_rec adj_rec_type;
1798 l_adj_tbl adj_tbl_type;
1799 l_existing_data cn_invoice_changes_pvt.invoice_tbl;
1800 l_new_data cn_invoice_changes_pvt.invoice_tbl;
1801 l_api_rec cn_comm_lines_api_pkg.comm_lines_api_rec_type;
1802 o_newtx_rec adj_rec_type;
1803 l_deal_data_tbl cn_invoice_changes_pvt.deal_data_tbl;
1804 CURSOR order_cur IS
1805 SELECT comm_lines_api_id
1806 FROM cn_comm_lines_api_all api
1807 WHERE api.order_number = l_order_number
1808 AND api.org_id = l_org_id
1809 AND api.trx_type = 'ORD'
1810 AND api.load_status NOT IN ('LOADED', 'FILTERED') -- vensrini Bug fix 4202682
1811 AND (api.adjust_status NOT IN ('FROZEN','REVERSAL','SCA_PENDING') )--OR
1812 -- api.adjust_status IS NULL)
1813 UNION ALL
1814 SELECT comm_lines_api_id
1815 FROM cn_commission_headers_all ch
1816 WHERE ch.order_number = l_order_number
1817 AND ch.org_id = l_org_id
1818 AND ch.trx_type = 'ORD'
1819 AND (ch.adjust_status NOT IN ('FROZEN','REVERSAL') );--OR
1820 -- ch.adjust_status IS NULL);
1821
1822 BEGIN
1823 -- Standard Start of API savepoint
1824 SAVEPOINT call_split;
1825 -- Standard call to check for call compatibility.
1826 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
1827 p_api_version ,
1828 l_api_name,
1829 G_PKG_NAME ) THEN
1830 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1831 END IF;
1832 -- Initialize message list if p_init_msg_list is set to TRUE.
1833 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1834 FND_MSG_PUB.initialize;
1835 END IF;
1836 -- Initialize API return status to success
1837 x_return_status := FND_API.G_RET_STS_SUCCESS;
1838 x_loading_status := 'CN_INSERTED';
1839
1840
1841
1842 -- API body
1843 /* Check whether a resource name is available or not to split the trx. */
1844 FOR i IN p_split_data_tbl.FIRST..p_split_data_tbl.LAST
1845 LOOP
1846 IF (p_split_data_tbl(i).salesrep_id IS NOT NULL) THEN
1847 l_id_counter := l_id_counter + 1;
1848 END IF;
1849 END LOOP;
1850 IF (l_id_counter = 0) THEN
1851 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
1852 FND_MESSAGE.Set_Name('CN', 'CN_NO_SPLIT_RESOURCE');
1853 FND_MSG_PUB.Add;
1854 END IF;
1855 x_loading_status := 'CN_NO_SPLIT_RESOURCE';
1856 RAISE FND_API.G_EXC_ERROR;
1857 END IF;
1858 --
1859 /* Get the original data for the comm_lines_api_id */
1860 get_api_data(
1861 p_comm_lines_api_id => p_comm_lines_api_id,
1862 x_adj_tbl => l_adj_tbl);
1863 --
1864 IF (p_split_type = 'TRX') THEN -- 1
1865 -- Check for split amount/percentages
1866 FOR i IN p_split_data_tbl.FIRST..p_split_data_tbl.LAST
1867 LOOP
1868 IF (p_split_data_tbl(i).revenue_type = 'REVENUE') THEN
1869 l_split_percent := l_split_percent + NVL(p_split_data_tbl(i).split_pct,0);
1870 l_split_amount := l_split_amount + NVL(p_split_data_tbl(i).split_amount,0);
1871 END IF;
1872 END LOOP;
1873 IF ((l_adj_tbl(1).revenue_type = 'REVENUE') AND
1874 (l_adj_tbl(1).split_pct <> 0)) THEN -- 2
1875
1876 -- bug 2118574
1877 -- IF (l_split_amount <> p_transaction_amount) THEN
1878 -- IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
1879 -- FND_MESSAGE.Set_Name('CN', 'CN_ADJ_SPLIT_AMOUNT');
1880 -- FND_MSG_PUB.Add;
1881 -- END IF;
1882 -- x_loading_status := 'CN_ADJ_SPLIT_AMOUNT';
1883 -- RAISE FND_API.G_EXC_ERROR;
1884 -- END IF;
1885
1886
1887 IF (l_split_percent <> l_adj_tbl(1).split_pct) THEN
1888 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
1889 FND_MESSAGE.Set_Name('CN', 'CN_ADJ_SPLIT_PERCENT');
1890 FND_MSG_PUB.Add;
1891 END IF;
1892 x_loading_status := 'CN_ADJ_SPLIT_PERCENT';
1893 RAISE FND_API.G_EXC_ERROR;
1894 END IF;
1895 END IF; --2 end
1896 --
1897 IF (l_adj_tbl.COUNT > 0)THEN -- 3
1898 IF ((g_track_invoice = 'Y') AND
1899 (l_adj_tbl(1).trx_type = 'INV')) THEN -- 4
1900 --
1901 l_existing_data(1).salesrep_id := l_adj_tbl(1).direct_salesrep_id;
1902 l_existing_data(1).direct_salesrep_number
1903 := l_adj_tbl(1).direct_salesrep_number;
1904 l_existing_data(1).invoice_number := l_adj_tbl(1).invoice_number;
1905 l_existing_data(1).line_number := l_adj_tbl(1).line_number;
1906 l_existing_data(1).revenue_type := l_adj_tbl(1).revenue_type;
1907 l_existing_data(1).split_pct := l_adj_tbl(1).split_pct;
1908 l_existing_data(1).comm_lines_api_id:= l_adj_tbl(1).comm_lines_api_id;
1909 --
1910 l_new_data(1).salesrep_id := NULL;
1911 l_new_data(1).direct_salesrep_number:= NULL;
1912 l_new_data(1).invoice_number := NULL;
1913 l_new_data(1).line_number := NULL;
1914 l_new_data(1).revenue_type := NULL;
1915 l_new_data(1).split_pct := NULL;
1916 l_new_data(1).comm_lines_api_id := NULL;
1917 --
1918 cn_invoice_changes_pvt.update_invoice_changes(
1919 p_api_version => l_api_version,
1920 p_init_msg_list => p_init_msg_list,
1921 p_validation_level => p_validation_level,
1922 p_existing_data => l_existing_data,
1923 p_new_data => l_new_data,
1924 p_exist_data_check => 'Y',
1925 p_new_data_check => 'N',
1926 x_return_status => x_return_status,
1927 x_msg_count => x_msg_count,
1928 x_msg_data => x_msg_data,
1929 x_loading_status => x_loading_status);
1930 --
1931 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1932 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
1933 FND_MESSAGE.Set_Name('CN', 'CN_UPDATE_INV_ERROR');
1934 FND_MSG_PUB.Add;
1935 END IF;
1936 x_loading_status := 'CN_UPDATE_INV_ERROR';
1937 RAISE FND_API.G_EXC_ERROR;
1938 END IF;
1939 --
1940 END IF; -- 4 end
1941 END IF; -- 3 end
1942 --
1943 cn_adjustments_pkg.api_negate_record(
1944 x_comm_lines_api_id => p_comm_lines_api_id,
1945 x_adjusted_by => p_adjusted_by,
1946 x_adjust_comments => p_adjust_comments);
1947
1948 /* Added for Crediting Bug */
1949
1950 /*update_credit_credentials(
1951 p_comm_lines_api_id,
1952 l_terr_id,
1953 p_org_id,
1954 p_adjusted_by
1955 );*/
1956
1957
1958 /* Added for Crediting Bug */
1959 l_terr_id := NULL;
1960 l_terr_name := NULL;
1961 BEGIN
1962 SELECT TERR_ID, TERR_NAME INTO l_terr_id, l_terr_name
1963 FROM CN_COMM_LINES_API
1964 WHERE
1965 COMM_LINES_API_ID = p_comm_lines_api_id
1966 AND ORG_ID = p_org_id;
1967 EXCEPTION
1968 WHEN OTHERS THEN
1969 NULL;
1970 END;
1971
1972 IF (l_adj_tbl.COUNT = 1) THEN -- 5
1973 FOR i IN p_split_data_tbl.FIRST..p_split_data_tbl.LAST
1974 LOOP
1975 BEGIN
1976 IF (l_adj_tbl(1).load_status = 'LOADED') THEN
1977 l_newtx_rec.transaction_amount := p_split_data_tbl(i).split_amount;
1978 l_newtx_rec.transaction_amount_orig := NULL;
1979 ELSE
1980 l_newtx_rec.transaction_amount_orig := p_split_data_tbl(i).split_amount;
1981 l_newtx_rec.transaction_amount := NULL;
1982 END IF;
1983 --
1984 IF ((l_adj_tbl(1).trx_type IN ('CM','PMT')) AND
1985 (g_track_invoice = 'Y'))THEN
1986 l_newtx_rec.split_status := 'DELINKED';
1987 END IF;
1988 --
1989 l_newtx_rec.direct_salesrep_id := p_split_data_tbl(i).salesrep_id;
1990 l_newtx_rec.revenue_type := p_split_data_tbl(i).revenue_type;
1991 l_newtx_rec.adjust_comments := p_adjust_comments;
1992 l_newtx_rec.adjust_status := 'SPLIT';
1993 l_newtx_rec.load_status := 'UNLOADED';
1994 l_newtx_rec.adj_comm_lines_api_id := p_comm_lines_api_id;
1995 l_newtx_rec.direct_salesrep_number := p_split_data_tbl(i).salesrep_number;
1996 l_newtx_rec.split_pct := NVL(p_split_data_tbl(i).split_pct,0);
1997 l_newtx_rec.invoice_date := l_adj_tbl(1).invoice_date;
1998 l_newtx_rec.order_date := l_adj_tbl(1).order_date;
1999 l_newtx_rec.org_id := l_adj_tbl(1).org_id;
2000 l_newtx_rec.inventory_item_id := l_adj_tbl(1).inventory_item_id;
2001 l_newtx_rec.terr_id := l_terr_id;
2002 l_newtx_rec.terr_name := l_terr_name;
2003
2004 /* Added for Crediting Bug */
2005 IF(l_terr_id IS NOT NULL)
2006 THEN
2007 l_newtx_rec.preserve_credit_override_flag := 'Y';
2008 l_newtx_rec.terr_id := -999;
2009 END IF;
2010
2011 --
2012 cn_invoice_changes_pvt.prepare_api_record(
2013 p_newtx_rec => l_newtx_rec,
2014 p_old_adj_tbl => l_adj_tbl,
2015 x_final_trx_rec => o_newtx_rec,
2016 x_return_status => x_return_status);
2017 --
2018 o_newtx_rec.adj_comm_lines_api_id := p_comm_lines_api_id;
2019 --
2020 cn_get_tx_data_pub.insert_api_record(
2021 p_api_version => p_api_version,
2022 p_init_msg_list => p_init_msg_list,
2023 p_validation_level => p_validation_level,
2024 p_action => 'UPDATE',
2025 p_newtx_rec => o_newtx_rec,
2026 x_api_id => l_comm_lines_api_id,
2027 x_return_status => x_return_status,
2028 x_msg_count => x_msg_count,
2029 x_msg_data => x_msg_data,
2030 x_loading_status => x_loading_status);
2031 --
2032 IF ((g_track_invoice = 'Y') AND (l_adj_tbl(1).trx_type = 'INV'))THEN
2033 l_counter := l_counter + 1;
2034 l_new_data(l_counter).salesrep_id := o_newtx_rec.direct_salesrep_id;
2035 l_new_data(l_counter).direct_salesrep_number
2036 := o_newtx_rec.direct_salesrep_number;
2037 l_new_data(l_counter).invoice_number := o_newtx_rec.invoice_number;
2038 l_new_data(l_counter).line_number := o_newtx_rec.line_number;
2039 l_new_data(l_counter).revenue_type := o_newtx_rec.revenue_type;
2040 l_new_data(l_counter).split_pct := o_newtx_rec.split_pct;
2041 l_new_data(l_counter).comm_lines_api_id
2042 := l_comm_lines_api_id;
2043 END IF;
2044
2045 --
2046 EXCEPTION
2047 WHEN OTHERS THEN
2048 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
2049 FND_MESSAGE.Set_Name('CN', 'CN_SPLIT_TRX_ERROR');
2050 FND_MSG_PUB.Add;
2051 END IF;
2052 x_loading_status := 'CN_SPLIT_TRX_ERROR';
2053 RAISE FND_API.G_EXC_ERROR;
2054 END;
2055 END LOOP;
2056
2057 END IF; -- 5 end
2058 --
2059 IF ((l_adj_tbl(1).trx_type = 'INV') AND
2060 (g_track_invoice = 'Y') AND
2061 (l_new_data.COUNT > 0)) THEN -- 6
2062 --
2063 cn_invoice_changes_pvt.update_invoice_changes(
2064 p_api_version => p_api_version,
2065 p_existing_data => l_existing_data,
2066 p_new_data => l_new_data,
2067 p_exist_data_check => 'N',
2068 p_new_data_check => 'Y',
2069 x_return_status => x_return_status,
2070 x_msg_count => x_msg_count,
2071 x_msg_data => x_msg_data,
2072 x_loading_status => x_loading_status);
2073 --
2074 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2075 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
2076 FND_MESSAGE.Set_Name('CN', 'CN_UPDATE_INV_ERROR');
2077 FND_MSG_PUB.Add;
2078 END IF;
2079 x_loading_status := 'CN_UPDATE_INV_ERROR';
2080 RAISE FND_API.G_EXC_ERROR;
2081 END IF;
2082 --
2083 END IF; -- 6 end
2084 --
2085 IF (g_track_invoice = 'Y') THEN -- 7
2086 IF (l_adj_tbl(1).trx_type = 'INV') THEN
2087 cn_invoice_changes_pvt.update_credit_memo(
2088 p_api_version => p_api_version,
2089 p_existing_data => l_existing_data,
2090 p_new_data => l_new_data,
2091 p_called_from => 'SPLIT',
2092 p_adjust_status => 'SPLIT',
2093 x_return_status => x_return_status,
2094 x_msg_count => x_msg_count,
2095 x_msg_data => x_msg_data,
2096 x_loading_status => x_loading_status);
2097 END IF;
2098 --
2099 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2100 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
2101 FND_MESSAGE.Set_Name('CN', 'CN_UPDATE_CM_ERROR');
2102 FND_MSG_PUB.Add;
2103 END IF;
2104 x_loading_status := 'CN_UPDATE_CM_ERROR';
2105 RAISE FND_API.G_EXC_ERROR;
2106 END IF;
2107 END IF; -- 7 end
2108 --
2109 ELSIF (p_split_type = 'DEAL') THEN -- 1 elsif
2110 -- Currently order_number is coming as G_MISS_NUM.
2111 -- Convert that into NULL
2112 IF (p_order_number = FND_API.G_MISS_NUM) THEN
2113 l_order_number := NULL;
2114 ELSE
2115 l_order_number := p_order_number;
2116 END IF;
2117
2118 l_org_id := p_org_id;
2119
2120 -- Check whether deal is a rev or nonrev deal (based on origial deal)
2121 IF (l_order_number IS NOT NULL) THEN -- 8
2122 BEGIN
2123 SELECT count(order_number)
2124 INTO l_deal_count
2125 FROM cn_commission_headers_all
2126 WHERE order_number = l_order_number
2127 AND revenue_type = 'REVENUE'
2128 AND org_id = l_org_id;
2129 IF (l_deal_count > 0) THEN
2130 l_deal_type := 'REVENUE';
2131 ELSE
2132 SELECT count(order_number)
2133 INTO l_deal_count
2134 FROM cn_comm_lines_api_all
2135 WHERE order_number = l_order_number
2136 AND revenue_type = 'REVENUE'
2137 AND org_id = l_org_id;
2138 IF (l_deal_count > 0) THEN
2139 l_deal_type := 'REVENUE';
2140 ELSE
2141 l_deal_type := 'NONREVENUE';
2142 END IF;
2143 END IF;
2144 EXCEPTION
2145 WHEN OTHERS THEN
2146 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
2147 FND_MESSAGE.Set_Name('CN', 'CN_DEAL_REV_ERROR');
2148 FND_MSG_PUB.Add;
2149 END IF;
2150 x_loading_status := 'CN_DEAL_REV_ERROR';
2151 RAISE FND_API.G_EXC_ERROR;
2152 END;
2153 ELSIF (p_invoice_number IS NOT NULL) THEN
2154 BEGIN
2155 SELECT count(invoice_number)
2156 INTO l_deal_count
2157 FROM cn_commission_headers_all
2158 WHERE invoice_number = p_invoice_number
2159 AND revenue_type = 'REVENUE'
2160 AND org_id = l_org_id;
2161 IF (l_deal_count > 0) THEN
2162 l_deal_type := 'REVENUE';
2163 ELSE
2164 SELECT count(invoice_number)
2165 INTO l_deal_count
2166 FROM cn_comm_lines_api_all
2167 WHERE invoice_number = p_invoice_number
2168 AND revenue_type = 'REVENUE'
2169 AND org_id = l_org_id;
2170 IF (l_deal_count > 0) THEN
2171 l_deal_type := 'REVENUE';
2172 ELSE
2173 l_deal_type := 'NONREVENUE';
2174 END IF;
2175 END IF;
2176 EXCEPTION
2177 WHEN OTHERS THEN
2178 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
2179 FND_MESSAGE.Set_Name('CN', 'CN_DEAL_REV_ERROR');
2180 FND_MSG_PUB.Add;
2181 END IF;
2182 x_loading_status := 'CN_DEAL_REV_ERROR';
2183 RAISE FND_API.G_EXC_ERROR;
2184 END;
2185 END IF; -- 8 end
2186 -- Check for split amount/percentages
2187 FOR i IN p_split_data_tbl.FIRST..p_split_data_tbl.LAST
2188 LOOP
2189 IF (p_split_data_tbl(i).revenue_type = 'REVENUE') THEN
2190 l_split_percent := l_split_percent + NVL(p_split_data_tbl(i).split_pct,0);
2191 END IF;
2192 END LOOP;
2193 IF ((l_split_percent <> 100) AND (l_deal_type = 'REVENUE')) THEN
2194 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
2195 FND_MESSAGE.Set_Name('CN', 'CN_ADJ_SPLIT_PERCENT');
2196 FND_MSG_PUB.Add;
2197 END IF;
2198 x_loading_status := 'CN_ADJ_SPLIT_PERCENT';
2199 RAISE FND_API.G_EXC_ERROR;
2200 END IF;
2201
2202 -- Check if split to all nonrevenue type or not / bug 2130062
2203 l_split_to_all_nonrevenue_type := 'Y';
2204 FOR i IN p_split_data_tbl.FIRST..p_split_data_tbl.last -- 8.5
2205 LOOP
2206 IF (p_split_data_tbl(i).revenue_type = 'REVENUE') THEN
2207 l_split_to_all_nonrevenue_type := 'N';
2208 END IF;
2209 END LOOP;
2210
2211 IF ((l_deal_type = 'NONREVENUE') AND (l_split_to_all_nonrevenue_type = 'Y')) THEN
2212 -- we need to split nonrevenue line
2213 l_split_nonrevenue_line := 'Y';
2214 ELSE
2215 -- we do not split nonrevenue line
2216 l_split_nonrevenue_line := 'N';
2217 END IF; -- end of 8.5
2218
2219 --
2220 -- Processing DEAL SPLIT for order number
2221 IF (l_order_number IS NOT NULL) THEN -- 9
2222
2223 l_data_exist := 'N';
2224
2225 FOR order_rec IN order_cur
2226 LOOP
2227
2228 l_data_exist := 'Y';
2229
2230 get_api_data(
2231 p_comm_lines_api_id => order_rec.comm_lines_api_id,
2232 x_adj_tbl => l_adj_tbl);
2233
2234 l_terr_id := NULL;
2235 l_terr_name := NULL;
2236 /* Added for Crediting Bug */
2237 BEGIN
2238 SELECT TERR_ID, TERR_NAME INTO l_terr_id, l_terr_name
2239 FROM CN_COMM_LINES_API
2240 WHERE
2241 COMM_LINES_API_ID = order_rec.comm_lines_api_id
2242 AND ORG_ID = p_org_id;
2243
2244 EXCEPTION
2245 WHEN OTHERS THEN
2246 NULL;
2247 END;
2248
2249 --
2250 -- bug 2130062 do not split NONREVENUE line
2251 --
2252 IF ((l_adj_tbl(1).revenue_type = 'NONREVENUE') AND
2253 (l_split_nonrevenue_line = 'N'))THEN
2254 NULL;
2255 ELSE
2256 cn_adjustments_pkg.api_negate_record(
2257 x_comm_lines_api_id => order_rec.comm_lines_api_id,
2258 x_adjusted_by => p_adjusted_by,
2259 x_adjust_comments => p_adjust_comments);
2260
2261 /* Added for Crediting Bug */
2262
2263 /*update_credit_credentials(
2264 order_rec.comm_lines_api_id,
2265 l_terr_id,
2266 p_org_id,
2267 p_adjusted_by
2268 );*/
2269
2270
2271 FOR i IN p_split_data_tbl.FIRST..p_split_data_tbl.LAST
2272 LOOP
2273
2274
2275 IF (l_adj_tbl(1).load_status = 'LOADED') THEN
2276 l_newtx_rec.transaction_amount := l_adj_tbl(1).transaction_amount*
2277 NVL(p_split_data_tbl(i).split_pct,0)/100;
2278 l_newtx_rec.transaction_amount_orig := NULL;
2279 ELSE
2280 l_newtx_rec.transaction_amount := NULL;
2281 l_newtx_rec.transaction_amount_orig := l_adj_tbl(1).transaction_amount_orig*
2282 NVL(p_split_data_tbl(i).split_pct,0)/100;
2283 END IF;
2284 l_newtx_rec.direct_salesrep_id := p_split_data_tbl(i).salesrep_id;
2285 l_newtx_rec.revenue_type := p_split_data_tbl(i).revenue_type;
2286 l_newtx_rec.adjust_comments := p_adjust_comments;
2287 l_newtx_rec.adjust_status := 'DEALSPLIT';
2288 l_newtx_rec.load_status := 'UNLOADED';
2289 l_newtx_rec.adj_comm_lines_api_id := order_rec.comm_lines_api_id;
2290 l_newtx_rec.direct_salesrep_number := p_split_data_tbl(i).salesrep_number;
2291 l_newtx_rec.split_pct := NVL(p_split_data_tbl(i).split_pct,0);
2292 l_newtx_rec.invoice_date := l_adj_tbl(1).invoice_date;
2293 l_newtx_rec.order_date := l_adj_tbl(1).order_date;
2294 l_newtx_rec.org_id := l_adj_tbl(1).org_id;
2295 l_newtx_rec.inventory_item_id := l_adj_tbl(1).inventory_item_id;
2296 l_newtx_rec.terr_id := l_terr_id;
2297 l_newtx_rec.terr_name := l_terr_name;
2298
2299
2300 /* Added for Crediting Bug */
2301
2302 IF(l_terr_id IS NOT NULL)
2303 THEN
2304 l_newtx_rec.preserve_credit_override_flag := 'Y';
2305 l_newtx_rec.terr_id := -999;
2306 END IF;
2307
2308 --
2309 cn_invoice_changes_pvt.prepare_api_record(
2310 p_newtx_rec => l_newtx_rec,
2311 p_old_adj_tbl => l_adj_tbl,
2312 x_final_trx_rec => o_newtx_rec,
2313 x_return_status => x_return_status);
2314 --
2315 o_newtx_rec.adj_comm_lines_api_id := order_rec.comm_lines_api_id;
2316 --
2317 cn_get_tx_data_pub.insert_api_record(
2318 p_api_version => p_api_version,
2319 p_init_msg_list => p_init_msg_list,
2320 p_validation_level => p_validation_level,
2321 p_action => 'UPDATE',
2322 p_newtx_rec => o_newtx_rec,
2323 x_api_id => l_comm_lines_api_id,
2324 x_return_status => x_return_status,
2325 x_msg_count => x_msg_count,
2326 x_msg_data => x_msg_data,
2327 x_loading_status => x_loading_status);
2328 --
2329 END LOOP;
2330
2331 END IF; -- 2130062 if l_adj_tbl(1).revenue_type = 'NONREVENUE' THEN
2332 END LOOP;
2333
2334 --
2335 IF (l_data_exist = 'N') THEN
2336 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
2337 FND_MESSAGE.Set_Name('CN', 'CN_NO_REC_DEAL');
2338 FND_MSG_PUB.Add;
2339 END IF;
2340 x_loading_status := 'CN_NO_REC_DEAL';
2341 RAISE FND_API.G_EXC_ERROR;
2342 END IF;
2343
2344 END IF; -- 9 end
2345
2346 IF (p_invoice_number <> FND_API.G_MISS_CHAR) THEN -- 10
2347 cn_invoice_changes_pvt.capture_deal_invoice(
2348 p_api_version => p_api_version,
2349 p_trx_type => 'INV',
2350 p_split_nonrevenue_line => l_split_nonrevenue_line,
2351 p_invoice_number => p_invoice_number,
2352 p_org_id => l_org_id,
2353 p_split_data_tbl => p_split_data_tbl,
2354 x_deal_data_tbl => l_deal_data_tbl,
2355 x_return_status => x_return_status,
2356 x_msg_count => x_msg_count,
2357 x_msg_data => x_msg_data,
2358 x_loading_status => x_loading_status);
2359 IF (l_deal_data_tbl.COUNT > 0) THEN -- 11
2360 --
2361 FOR i IN l_deal_data_tbl.FIRST..l_deal_data_tbl.LAST
2362 LOOP
2363 cn_adjustments_pkg.api_negate_record(
2364 x_comm_lines_api_id => l_deal_data_tbl(i).comm_lines_api_id,
2365 x_adjusted_by => p_adjusted_by,
2366 x_adjust_comments => p_adjust_comments);
2367 /* Added for Crediting Bug */
2368
2369 /*update_credit_credentials(
2370 l_deal_data_tbl(i).comm_lines_api_id,
2371 l_terr_id,
2372 p_org_id,
2373 p_adjusted_by
2374 );*/
2375
2376 END LOOP;
2377 --
2378 FOR j IN l_deal_data_tbl.FIRST..l_deal_data_tbl.LAST
2379 LOOP
2380 --
2381 get_api_data(
2382 p_comm_lines_api_id => l_deal_data_tbl(j).comm_lines_api_id,
2383 x_adj_tbl => l_adj_tbl);
2384 --
2385
2386 /* Added for Crediting Bug */
2387 l_terr_id := NULL;
2388 l_terr_name := NULL;
2389 BEGIN
2390 SELECT TERR_ID, TERR_NAME INTO l_terr_id, l_terr_name
2391 FROM CN_COMM_LINES_API
2392 WHERE
2393 COMM_LINES_API_ID = l_deal_data_tbl(j).comm_lines_api_id
2394 AND ORG_ID = p_org_id;
2395
2396 EXCEPTION
2397 WHEN OTHERS THEN
2398 NULL;
2399 END;
2400
2401 FOR i IN p_split_data_tbl.FIRST..p_split_data_tbl.LAST
2402 LOOP
2403 IF (l_adj_tbl(1).load_status = 'LOADED') THEN
2404 l_newtx_rec.transaction_amount := l_adj_tbl(1).transaction_amount*
2405 NVL(p_split_data_tbl(i).split_pct,0)/100;
2406 l_newtx_rec.transaction_amount_orig:= NULL;
2407 ELSE
2408 l_newtx_rec.transaction_amount := NULL;
2409 l_newtx_rec.transaction_amount_orig:= l_adj_tbl(1).transaction_amount_orig*
2410 NVL(p_split_data_tbl(i).split_pct,0)/100;
2411 END IF;
2412 l_newtx_rec.direct_salesrep_id := p_split_data_tbl(i).salesrep_id;
2413 l_newtx_rec.revenue_type := p_split_data_tbl(i).revenue_type;
2414 l_newtx_rec.adjust_comments := p_adjust_comments;
2415 l_newtx_rec.adjust_status := 'DEALSPLIT';
2416 l_newtx_rec.load_status := 'UNLOADED';
2417 l_newtx_rec.adj_comm_lines_api_id := l_deal_data_tbl(j).comm_lines_api_id;
2418 l_newtx_rec.direct_salesrep_number := p_split_data_tbl(i).salesrep_number;
2419 l_newtx_rec.split_pct := NVL(p_split_data_tbl(i).split_pct,0);
2420 l_newtx_rec.invoice_number := l_deal_data_tbl(j).invoice_number;
2421 l_newtx_rec.line_number := l_deal_data_tbl(j).line_number;
2422 l_newtx_rec.invoice_date := l_adj_tbl(1).invoice_date;
2423 l_newtx_rec.order_date := l_adj_tbl(1).order_date;
2424 l_newtx_rec.org_id := l_adj_tbl(1).org_id;
2425 l_newtx_rec.inventory_item_id := l_adj_tbl(1).inventory_item_id;
2426 l_newtx_rec.terr_id := l_terr_id;
2427 l_newtx_rec.terr_name := l_terr_name;
2428 --
2429
2430 /* Added for Crediting Bug */
2431
2432 IF(l_terr_id IS NOT NULL)
2433 THEN
2434 l_newtx_rec.preserve_credit_override_flag := 'Y';
2435 l_newtx_rec.terr_id := -999;
2436 END IF;
2437
2438
2439 cn_invoice_changes_pvt.prepare_api_record(
2440 p_newtx_rec => l_newtx_rec,
2441 p_old_adj_tbl => l_adj_tbl,
2442 x_final_trx_rec => o_newtx_rec,
2443 x_return_status => x_return_status);
2444 --
2445 o_newtx_rec.adj_comm_lines_api_id := l_deal_data_tbl(j).comm_lines_api_id;
2446 --
2447 cn_get_tx_data_pub.insert_api_record(
2448 p_api_version => p_api_version,
2449 p_init_msg_list => p_init_msg_list,
2450 p_validation_level => p_validation_level,
2451 p_action => 'UPDATE',
2452 p_newtx_rec => o_newtx_rec,
2453 x_api_id => l_comm_lines_api_id,
2454 x_return_status => x_return_status,
2455 x_msg_count => x_msg_count,
2456 x_msg_data => x_msg_data,
2457 x_loading_status => x_loading_status);
2458 --
2459 IF (g_track_invoice = 'Y') THEN
2460 l_counter := l_counter + 1;
2461 --
2462 l_new_data(l_counter).salesrep_id := o_newtx_rec.direct_salesrep_id;
2463 l_new_data(l_counter).direct_salesrep_number
2464 := o_newtx_rec.direct_salesrep_number;
2465 l_new_data(l_counter).invoice_number
2466 := o_newtx_rec.invoice_number;
2467 l_new_data(l_counter).line_number := o_newtx_rec.line_number;
2468 l_new_data(l_counter).revenue_type := o_newtx_rec.revenue_type;
2469 l_new_data(l_counter).split_pct := o_newtx_rec.split_pct;
2470 l_new_data(l_counter).comm_lines_api_id
2471 := l_comm_lines_api_id;
2472 --
2473 END IF;
2474 END LOOP;
2475 END LOOP;
2476 ELSE
2477 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
2478 FND_MESSAGE.Set_Name('CN', 'CN_NO_REC_DEAL');
2479 FND_MSG_PUB.Add;
2480 END IF;
2481 x_loading_status := 'CN_NO_REC_DEAL';
2482 RAISE FND_API.G_EXC_ERROR;
2483 END IF; -- 11 end
2484 --
2485 IF ((g_track_invoice = 'Y') AND (l_new_data.COUNT > 0)) THEN -- 12
2486 --
2487 l_existing_data(1).salesrep_id := NULL;
2488 l_existing_data(1).direct_salesrep_number
2489 := NULL;
2490 l_existing_data(1).invoice_number := NULL;
2491 l_existing_data(1).line_number := NULL;
2492 l_existing_data(1).revenue_type := NULL;
2493 l_existing_data(1).split_pct := NULL;
2494 l_existing_data(1).comm_lines_api_id:= NULL;
2495 --
2496 cn_invoice_changes_pvt.update_invoice_changes(
2497 p_api_version => p_api_version,
2498 p_existing_data => l_existing_data,
2499 p_new_data => l_new_data,
2500 p_exist_data_check => 'N',
2501 p_new_data_check => 'Y',
2502 x_return_status => x_return_status,
2503 x_msg_count => x_msg_count,
2504 x_msg_data => x_msg_data,
2505 x_loading_status => x_loading_status);
2506 --
2507 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2508 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
2509 FND_MESSAGE.Set_Name('CN', 'CN_UPDATE_INV_ERROR');
2510 FND_MSG_PUB.Add;
2511 END IF;
2512 x_loading_status := 'CN_UPDATE_INV_ERROR';
2513 RAISE FND_API.G_EXC_ERROR;
2514 END IF;
2515 END IF; -- 12 end
2516 --
2517 IF ((g_track_invoice = 'Y') AND (l_deal_data_tbl.COUNT > 0)) THEN -- 13
2518 FOR l_cm_pmt_count IN 1..2
2519 LOOP
2520 IF (l_cm_pmt_count = 1) THEN
2521 l_trx_type := 'CM';
2522 ELSE
2523 l_trx_type := 'PMT';
2524 END IF;
2525 --
2526 l_deal_data_tbl.DELETE;
2527 --
2528 cn_invoice_changes_pvt.capture_deal_invoice
2529 (
2530 p_api_version => p_api_version,
2531 p_trx_type => l_trx_type,
2532 p_split_nonrevenue_line => l_split_nonrevenue_line,
2533 p_invoice_number => p_invoice_number,
2534 p_org_id => l_org_id,
2535 p_split_data_tbl => p_split_data_tbl,
2536 x_deal_data_tbl => l_deal_data_tbl,
2537 x_return_status => x_return_status,
2538 x_msg_count => x_msg_count,
2539 x_msg_data => x_msg_data,
2540 x_loading_status => x_loading_status);
2541 --
2542 IF (l_deal_data_tbl.COUNT > 0) THEN -- 14
2543 --
2544 FOR i IN l_deal_data_tbl.FIRST..l_deal_data_tbl.LAST
2545 LOOP
2546 cn_adjustments_pkg.api_negate_record(
2547 x_comm_lines_api_id => l_deal_data_tbl(i).comm_lines_api_id,
2548 x_adjusted_by => p_adjusted_by,
2549 x_adjust_comments => p_adjust_comments);
2550 END LOOP;
2551 --
2552 FOR j IN l_deal_data_tbl.FIRST..l_deal_data_tbl.LAST
2553 LOOP
2554
2555
2556 /* Added for Crediting Bug */
2557 l_terr_id := NULL;
2558 l_terr_name := NULL;
2559 BEGIN
2560 SELECT TERR_ID, TERR_NAME INTO l_terr_id, l_terr_name
2561 FROM CN_COMM_LINES_API
2562 WHERE
2563 COMM_LINES_API_ID = l_deal_data_tbl(j).comm_lines_api_id
2564 AND ORG_ID = p_org_id;
2565
2566 EXCEPTION
2567 WHEN OTHERS THEN
2568 NULL;
2569 END;
2570
2571
2572 FOR i IN p_split_data_tbl.FIRST..p_split_data_tbl.LAST
2573 LOOP
2574 --
2575 get_api_data(
2576 p_comm_lines_api_id => l_deal_data_tbl(j).comm_lines_api_id,
2577 x_adj_tbl => l_adj_tbl);
2578 --
2579 IF (l_adj_tbl(1).load_status = 'LOADED') THEN
2580 l_newtx_rec.transaction_amount
2581 := l_adj_tbl(1).transaction_amount*
2582 NVL(p_split_data_tbl(i).split_pct,0)/100;
2583 l_newtx_rec.transaction_amount_orig := NULL;
2584 ELSE
2585 l_newtx_rec.transaction_amount := NULL;
2586 l_newtx_rec.transaction_amount_orig
2587 := l_adj_tbl(1).transaction_amount_orig*
2588 NVL(p_split_data_tbl(i).split_pct,0)/100;
2589 END IF;
2590 l_newtx_rec.direct_salesrep_id
2591 := p_split_data_tbl(i).salesrep_id;
2592 l_newtx_rec.revenue_type
2593 := p_split_data_tbl(i).revenue_type;
2594 l_newtx_rec.adjust_comments
2595 := p_adjust_comments;
2596 l_newtx_rec.adjust_status := 'DEALSPLIT';
2597 l_newtx_rec.load_status := 'UNLOADED';
2598 l_newtx_rec.split_status := 'LINKED';
2599 l_newtx_rec.adj_comm_lines_api_id
2600 := l_deal_data_tbl(j).comm_lines_api_id;
2601 l_newtx_rec.direct_salesrep_number
2602 := p_split_data_tbl(i).salesrep_number;
2603 l_newtx_rec.split_pct
2604 := NVL(p_split_data_tbl(i).split_pct,0);
2605 l_newtx_rec.invoice_number
2606 := l_deal_data_tbl(j).invoice_number;
2607 l_newtx_rec.line_number
2608 := l_deal_data_tbl(j).line_number;
2609
2610 l_newtx_rec.invoice_date := l_adj_tbl(1).invoice_date;
2611 l_newtx_rec.order_date := l_adj_tbl(1).order_date;
2612 l_newtx_rec.org_id := l_adj_tbl(1).org_id;
2613 l_newtx_rec.inventory_item_id := l_adj_tbl(1).inventory_item_id;
2614 l_newtx_rec.terr_id := l_terr_id;
2615 l_newtx_rec.terr_name := l_terr_name;
2616
2617 IF(l_terr_id IS NOT NULL)
2618 THEN
2619 l_newtx_rec.preserve_credit_override_flag := 'Y';
2620 l_newtx_rec.terr_id := -999;
2621 END IF;
2622
2623
2624 --
2625 cn_invoice_changes_pvt.prepare_api_record(
2626 p_newtx_rec => l_newtx_rec,
2627 p_old_adj_tbl => l_adj_tbl,
2628 x_final_trx_rec => o_newtx_rec,
2629 x_return_status => x_return_status);
2630 --
2631 o_newtx_rec.adj_comm_lines_api_id := l_deal_data_tbl(j).comm_lines_api_id;
2632 --
2633 cn_get_tx_data_pub.insert_api_record(
2634 p_api_version => p_api_version,
2635 p_init_msg_list => p_init_msg_list,
2636 p_validation_level => p_validation_level,
2637 p_action => 'UPDATE',
2638 p_newtx_rec => o_newtx_rec,
2639 x_api_id => l_comm_lines_api_id,
2640 x_return_status => x_return_status,
2641 x_msg_count => x_msg_count,
2642 x_msg_data => x_msg_data,
2643 x_loading_status => x_loading_status);
2644 END LOOP;
2645
2646 /* Added for Crediting Bug */
2647
2648 /*update_credit_credentials(
2649 l_deal_data_tbl(j).comm_lines_api_id,
2650 l_terr_id,
2651 p_org_id,
2652 p_adjusted_by
2653 );*/
2654
2655 END LOOP;
2656 END IF; -- 14 end
2657 END LOOP;
2658 END IF; -- 13 end
2659 END IF; -- 10 end
2660 END IF; -- 1 end
2661
2662
2663
2664 EXCEPTION
2665 WHEN FND_API.G_EXC_ERROR THEN
2666 ROLLBACK TO call_split;
2667 x_return_status := FND_API.G_RET_STS_ERROR ;
2668 FND_MSG_PUB.Count_And_Get(
2669 p_count => x_msg_count ,
2670 p_data => x_msg_data ,
2671 p_encoded => FND_API.G_FALSE);
2672 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2673 ROLLBACK TO call_split;
2674 x_loading_status := 'UNEXPECTED_ERR';
2675 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2676 FND_MSG_PUB.Count_And_Get(
2677 p_count => x_msg_count ,
2678 p_data => x_msg_data ,
2679 p_encoded => FND_API.G_FALSE);
2680 WHEN OTHERS THEN
2681 ROLLBACK TO call_split;
2682 x_loading_status := 'UNEXPECTED_ERR';
2683 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2684 IF FND_MSG_PUB.Check_Msg_Level(
2685 FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2686 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
2687 END IF;
2688 FND_MSG_PUB.Count_And_Get(
2689 p_count => x_msg_count ,
2690 p_data => x_msg_data ,
2691 p_encoded => FND_API.G_FALSE);
2692 END;
2693 --
2694 PROCEDURE get_trx_lines(
2695 p_api_version IN NUMBER,
2696 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
2697 p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL,
2698 p_header_id IN NUMBER := FND_API.G_MISS_NUM,
2699 x_return_status OUT NOCOPY VARCHAR2,
2700 x_msg_count OUT NOCOPY NUMBER,
2701 x_msg_data OUT NOCOPY VARCHAR2,
2702 x_loading_status OUT NOCOPY VARCHAR2,
2703 x_trx_line_tbl OUT NOCOPY trx_line_tbl,
2704 x_tbl_count OUT NOCOPY NUMBER) IS
2705 CURSOR line_cur IS
2706 SELECT *
2707 FROM cn_adj_detail_lines_v
2708 WHERE commission_header_id = p_header_id;
2709 --
2710 l_api_name CONSTANT VARCHAR2(30) := 'get_trx_lines';
2711 l_api_version CONSTANT NUMBER := 1.0;
2712 l_tbl_count NUMBER := 0;
2713 --
2714 BEGIN
2715 -- Standard Start of API savepoint
2716 SAVEPOINT get_trx_lines;
2717 -- Standard call to check for call compatibility.
2718 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
2719 p_api_version ,
2720 l_api_name,
2721 G_PKG_NAME ) THEN
2722 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2723 END IF;
2724 -- Initialize message list if p_init_msg_list is set to TRUE.
2725 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2726 FND_MSG_PUB.initialize;
2727 END IF;
2728 -- Initialize API return status to success
2729 x_return_status := FND_API.G_RET_STS_SUCCESS;
2730 x_loading_status := 'CN_INSERTED';
2731 -- API body
2732 FOR line_rec IN line_cur
2733 LOOP
2734 l_tbl_count := l_tbl_count + 1;
2735 x_trx_line_tbl(l_tbl_count).commission_line_id := line_rec.commission_line_id;
2736 x_trx_line_tbl(l_tbl_count).commission_header_id := line_rec.commission_header_id;
2737 x_trx_line_tbl(l_tbl_count).credited_salesrep_id := line_rec.credited_salesrep_id;
2738 x_trx_line_tbl(l_tbl_count).credited_salesrep_name := line_rec.credited_salesrep_name;
2739 x_trx_line_tbl(l_tbl_count).credited_salesrep_number := line_rec.credited_salesrep_number;
2740 x_trx_line_tbl(l_tbl_count).processed_period_id := line_rec.processed_period_id;
2741 x_trx_line_tbl(l_tbl_count).processed_date := line_rec.processed_date;
2742 x_trx_line_tbl(l_tbl_count).plan_element := line_rec.plan_element;
2743 x_trx_line_tbl(l_tbl_count).payment_uplift := line_rec.payment_uplift;
2744 x_trx_line_tbl(l_tbl_count).quota_uplift := line_rec.quota_uplift;
2745 x_trx_line_tbl(l_tbl_count).commission_amount := line_rec.commission_amount;
2746 x_trx_line_tbl(l_tbl_count).commission_rate := line_rec.commission_rate;
2747 x_trx_line_tbl(l_tbl_count).created_during := line_rec.created_during;
2748 x_trx_line_tbl(l_tbl_count).pay_period := line_rec.pay_period;
2749 x_trx_line_tbl(l_tbl_count).accumulation_period := line_rec.accumulation_period;
2750 x_trx_line_tbl(l_tbl_count).perf_achieved := line_rec.perf_achieved;
2751 x_trx_line_tbl(l_tbl_count).posting_status := line_rec.posting_status;
2752 x_trx_line_tbl(l_tbl_count).pending_status := line_rec.pending_status;
2753 x_trx_line_tbl(l_tbl_count).trx_status := line_rec.trx_status;
2754 x_trx_line_tbl(l_tbl_count).payee := line_rec.payee;
2755 END LOOP;
2756 x_tbl_count := x_trx_line_tbl.COUNT;
2757 EXCEPTION
2758 WHEN FND_API.G_EXC_ERROR THEN
2759 ROLLBACK TO get_trx_lines;
2760 x_return_status := FND_API.G_RET_STS_ERROR ;
2761 FND_MSG_PUB.Count_And_Get(
2762 p_count => x_msg_count ,
2763 p_data => x_msg_data ,
2764 p_encoded => FND_API.G_FALSE);
2765 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2766 ROLLBACK TO get_trx_lines;
2767 x_loading_status := 'UNEXPECTED_ERR';
2768 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2769 FND_MSG_PUB.Count_And_Get(
2770 p_count => x_msg_count ,
2771 p_data => x_msg_data ,
2772 p_encoded => FND_API.G_FALSE);
2773 WHEN OTHERS THEN
2774 ROLLBACK TO get_trx_lines;
2775 x_loading_status := 'UNEXPECTED_ERR';
2776 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2777 IF FND_MSG_PUB.Check_Msg_Level(
2778 FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2779 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
2780 END IF;
2781 FND_MSG_PUB.Count_And_Get(
2782 p_count => x_msg_count ,
2783 p_data => x_msg_data ,
2784 p_encoded => FND_API.G_FALSE);
2785 END;
2786 --
2787 PROCEDURE get_trx_history(
2788 p_api_version IN NUMBER,
2789 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
2790 p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL,
2791 p_adj_comm_lines_api_id IN NUMBER := FND_API.G_MISS_NUM,
2792 x_return_status OUT NOCOPY VARCHAR2,
2793 x_msg_count OUT NOCOPY NUMBER,
2794 x_msg_data OUT NOCOPY VARCHAR2,
2795 x_loading_status OUT NOCOPY VARCHAR2,
2796 x_adj_tbl OUT NOCOPY adj_tbl_type,
2797 x_adj_count OUT NOCOPY NUMBER) IS
2798 --
2799 CURSOR header_hist_cur IS
2800 SELECT cch.adj_comm_lines_api_id,re.resource_name name,
2801 s.salesrep_number employee_number,
2802 cch.processed_date,clt.meaning,cch.order_number,cch.booked_date,
2803 cch.invoice_number,cch.invoice_date,cch.quantity,
2804 cch.transaction_amount,cch.transaction_amount_orig
2805 FROM cn_commission_headers cch,
2806 jtf_rs_resource_extns_vl re,
2807 jtf_rs_salesreps s,
2808 cn_lookups clt,
2809 cn_period_statuses cpsp
2810 WHERE cch.direct_salesrep_id = s.salesrep_id
2811 AND s.resource_id = re.resource_id
2812 AND cch.processed_period_id = cpsp.period_id
2813 AND cch.trx_type = clt.lookup_code(+)
2814 AND clt.lookup_type (+)= 'TRX TYPES'
2815 AND cch.comm_lines_api_id = p_adj_comm_lines_api_id;
2816 --
2817 CURSOR api_hist_cur IS
2818 SELECT ccla.adj_comm_lines_api_id,re.resource_name name,
2819 s.salesrep_number employee_number,
2820 ccla.processed_date,clt.meaning,ccla.order_number,ccla.booked_date,
2821 ccla.invoice_number,ccla.invoice_date,ccla.quantity,
2822 ccla.acctd_transaction_amount,ccla.transaction_amount
2823 FROM cn_comm_lines_api ccla,
2824 jtf_rs_resource_extns_vl re,
2825 jtf_rs_salesreps s,
2826 cn_lookups clt,
2827 cn_period_statuses cpsp
2828 WHERE ccla.salesrep_id = s.salesrep_id
2829 AND s.resource_id = re.resource_id
2830 AND ccla.processed_period_id = cpsp.period_id
2831 AND ccla.trx_type = clt.lookup_code(+)
2832 AND clt.lookup_type (+)= 'TRX TYPES'
2833 AND nvl(CCLA.load_status,'X') <> 'LOADED'
2834 AND ccla.comm_lines_api_id = p_adj_comm_lines_api_id;
2835 --
2836 l_api_name CONSTANT VARCHAR2(30) := 'get_trx_history';
2837 l_api_version CONSTANT NUMBER := 1.0;
2838 l_tbl_count NUMBER := 1;
2839 --
2840 BEGIN
2841 -- Standard Start of API savepoint
2842 SAVEPOINT get_trx_history;
2843
2844 -- Standard call to check for call compatibility.
2845 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
2846 p_api_version ,
2847 l_api_name,
2848 G_PKG_NAME ) THEN
2849 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2850 END IF;
2851 -- Initialize message list if p_init_msg_list is set to TRUE.
2852 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2853 FND_MSG_PUB.initialize;
2854 END IF;
2855 -- Initialize API return status to success
2856 x_return_status := FND_API.G_RET_STS_SUCCESS;
2857 x_loading_status := 'CN_INSERTED';
2858 -- API body
2859 FOR adj IN header_hist_cur
2860 LOOP
2861 x_adj_tbl(l_tbl_count).direct_salesrep_name := adj.name;
2862 x_adj_tbl(l_tbl_count).direct_salesrep_number := adj.employee_number;
2863 x_adj_tbl(l_tbl_count).processed_date := adj.processed_date;
2864 x_adj_tbl(l_tbl_count).trx_type_disp := adj.meaning;
2865 x_adj_tbl(l_tbl_count).order_number := adj.order_number;
2866 x_adj_tbl(l_tbl_count).order_date := adj.booked_date;
2867 x_adj_tbl(l_tbl_count).invoice_number := adj.invoice_number;
2868 x_adj_tbl(l_tbl_count).invoice_date := adj.invoice_date;
2869 x_adj_tbl(l_tbl_count).quantity := adj.quantity;
2870 x_adj_tbl(l_tbl_count).transaction_amount := adj.transaction_amount;
2871 x_adj_tbl(l_tbl_count).transaction_amount_orig := adj.transaction_amount_orig;
2872 l_tbl_count := l_tbl_count + 1;
2873 END LOOP;
2874 FOR adj IN api_hist_cur
2875 LOOP
2876 x_adj_tbl(l_tbl_count).direct_salesrep_name := adj.name;
2877 x_adj_tbl(l_tbl_count).direct_salesrep_number := adj.employee_number;
2878 x_adj_tbl(l_tbl_count).processed_date := adj.processed_date;
2879 x_adj_tbl(l_tbl_count).trx_type_disp := adj.meaning;
2880 x_adj_tbl(l_tbl_count).order_number := adj.order_number;
2881 x_adj_tbl(l_tbl_count).order_date := adj.booked_date;
2882 x_adj_tbl(l_tbl_count).invoice_number := adj.invoice_number;
2883 x_adj_tbl(l_tbl_count).invoice_date := adj.invoice_date;
2884 x_adj_tbl(l_tbl_count).quantity := adj.quantity;
2885 x_adj_tbl(l_tbl_count).transaction_amount := adj.acctd_transaction_amount;
2886 x_adj_tbl(l_tbl_count).transaction_amount_orig := adj.transaction_amount;
2887 l_tbl_count := l_tbl_count + 1;
2888 END LOOP;
2889 x_adj_count := x_adj_tbl.COUNT;
2890 EXCEPTION
2891 WHEN FND_API.G_EXC_ERROR THEN
2892 x_return_status := FND_API.G_RET_STS_ERROR ;
2893 FND_MSG_PUB.Count_And_Get(
2894 p_count => x_msg_count ,
2895 p_data => x_msg_data ,
2896 p_encoded => FND_API.G_FALSE);
2897 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2898 x_loading_status := 'UNEXPECTED_ERR';
2899 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2900 FND_MSG_PUB.Count_And_Get(
2901 p_count => x_msg_count ,
2902 p_data => x_msg_data ,
2903 p_encoded => FND_API.G_FALSE);
2904 WHEN OTHERS THEN
2905 x_loading_status := 'UNEXPECTED_ERR';
2906 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2907 IF FND_MSG_PUB.Check_Msg_Level(
2908 FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2909 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
2910 END IF;
2911 FND_MSG_PUB.Count_And_Get(
2912 p_count => x_msg_count ,
2913 p_data => x_msg_data ,
2914 p_encoded => FND_API.G_FALSE);
2915 END;
2916 --
2917 PROCEDURE get_cust_info(
2918 p_api_version IN NUMBER,
2919 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
2920 p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL,
2921 p_comm_lines_api_id IN NUMBER,
2922 p_load_status IN VARCHAR2,
2923 x_return_status OUT NOCOPY VARCHAR2,
2924 x_msg_count OUT NOCOPY NUMBER,
2925 x_msg_data OUT NOCOPY VARCHAR2,
2926 x_loading_status OUT NOCOPY VARCHAR2,
2927 x_cust_info_rec OUT NOCOPY cust_info_rec) IS
2928 --
2929 l_api_name CONSTANT VARCHAR2(30) := 'get_cust_info';
2930 l_api_version CONSTANT NUMBER := 1.0;
2931 l_tbl_count NUMBER := 1;
2932 l_cust_info_rec cn_adjustments_pkg.cust_info_rec;
2933 --
2934 BEGIN
2935 -- Standard Start of API savepoint
2936 SAVEPOINT get_cust_info;
2937
2938 -- Standard call to check for call compatibility.
2939 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
2940 p_api_version ,
2941 l_api_name,
2942 G_PKG_NAME ) THEN
2943 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2944 END IF;
2945 -- Initialize message list if p_init_msg_list is set to TRUE.
2946 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2947 FND_MSG_PUB.initialize;
2948 END IF;
2949 -- Initialize API return status to success
2950 x_return_status := FND_API.G_RET_STS_SUCCESS;
2951 x_loading_status := 'CN_INSERTED';
2952 -- API body
2953 cn_adjustments_pkg.get_cust_info(
2954 p_comm_lines_api_id => p_comm_lines_api_id,
2955 p_load_status => p_load_status,
2956 x_cust_info_rec => l_cust_info_rec);
2957 x_cust_info_rec.customer_id := l_cust_info_rec.customer_id;
2958 x_cust_info_rec.customer_number := l_cust_info_rec.customer_number;
2959 x_cust_info_rec.customer_name := l_cust_info_rec.customer_name;
2960 x_cust_info_rec.bill_to_address_id := l_cust_info_rec.bill_to_address_id;
2961 x_cust_info_rec.bill_to_address1 := l_cust_info_rec.bill_to_address1;
2962 x_cust_info_rec.bill_to_address2 := l_cust_info_rec.bill_to_address2;
2963 x_cust_info_rec.bill_to_address3 := l_cust_info_rec.bill_to_address3;
2964 x_cust_info_rec.bill_to_address4 := l_cust_info_rec.bill_to_address4;
2965 x_cust_info_rec.bill_to_city := l_cust_info_rec.bill_to_city;
2966 x_cust_info_rec.bill_to_postal_code := l_cust_info_rec.bill_to_postal_code;
2967 x_cust_info_rec.bill_to_state := l_cust_info_rec.bill_to_state;
2968 x_cust_info_rec.ship_to_address_id := l_cust_info_rec.ship_to_address_id;
2969 x_cust_info_rec.ship_to_address1 := l_cust_info_rec.ship_to_address1;
2970 x_cust_info_rec.ship_to_address2 := l_cust_info_rec.ship_to_address2;
2971 x_cust_info_rec.ship_to_address3 := l_cust_info_rec.ship_to_address3;
2972 x_cust_info_rec.ship_to_address4 := l_cust_info_rec.ship_to_address4;
2973 x_cust_info_rec.ship_to_city := l_cust_info_rec.ship_to_city;
2974 x_cust_info_rec.ship_to_postal_code := l_cust_info_rec.ship_to_postal_code;
2975 x_cust_info_rec.ship_to_state := l_cust_info_rec.ship_to_state;
2976 x_cust_info_rec.bill_to_contact_id := l_cust_info_rec.bill_to_contact_id;
2977 x_cust_info_rec.bill_to_contact := l_cust_info_rec.bill_to_contact;
2978 x_cust_info_rec.ship_to_contact_id := l_cust_info_rec.ship_to_contact_id;
2979 x_cust_info_rec.ship_to_contact := l_cust_info_rec.ship_to_contact;
2980 EXCEPTION
2981 WHEN FND_API.G_EXC_ERROR THEN
2982 x_return_status := FND_API.G_RET_STS_ERROR ;
2983 FND_MSG_PUB.Count_And_Get(
2984 p_count => x_msg_count ,
2985 p_data => x_msg_data ,
2986 p_encoded => FND_API.G_FALSE);
2987 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2988 x_loading_status := 'UNEXPECTED_ERR';
2989 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2990 FND_MSG_PUB.Count_And_Get(
2991 p_count => x_msg_count ,
2992 p_data => x_msg_data ,
2993 p_encoded => FND_API.G_FALSE);
2994 WHEN OTHERS THEN
2995 x_loading_status := 'UNEXPECTED_ERR';
2996 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2997 IF FND_MSG_PUB.Check_Msg_Level(
2998 FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2999 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
3000 END IF;
3001 FND_MSG_PUB.Count_And_Get(
3002 p_count => x_msg_count ,
3003 p_data => x_msg_data ,
3004 p_encoded => FND_API.G_FALSE);
3005 END;
3006 --
3007 PROCEDURE update_api_record(
3008 p_api_version IN NUMBER,
3009 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
3010 p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL,
3011 p_newtx_rec IN adj_rec_type,
3012 x_api_id OUT NOCOPY NUMBER,
3013 x_return_status OUT NOCOPY VARCHAR2,
3014 x_msg_count OUT NOCOPY NUMBER,
3015 x_msg_data OUT NOCOPY VARCHAR2,
3016 x_loading_status OUT NOCOPY VARCHAR2) IS
3017 --
3018 l_api_name CONSTANT VARCHAR2(30) := 'update_api_record';
3019 l_api_version CONSTANT NUMBER := 1.0;
3020 l_comm_lines_api_id NUMBER;
3021 -- PL/SQL tables/records
3022 l_old_adj_tbl adj_tbl_type;
3023 l_existing_data cn_invoice_changes_pvt.invoice_tbl;
3024 l_new_data cn_invoice_changes_pvt.invoice_tbl;
3025 l_newtx_rec adj_rec_type;
3026 o_newtx_rec adj_rec_type;
3027 --
3028 ---- Bug 8882352 ----
3029 l_comm_line_count NUMBER;
3030
3031
3032 BEGIN
3033 -- Standard Start of API savepoint
3034 SAVEPOINT update_api_record;
3035 -- Standard call to check for call compatibility.
3036 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
3037 p_api_version ,
3038 l_api_name,
3039 G_PKG_NAME ) THEN
3040 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3041 END IF;
3042 -- Initialize message list if p_init_msg_list is set to TRUE.
3043 IF FND_API.to_Boolean( p_init_msg_list ) THEN
3044 FND_MSG_PUB.initialize;
3045 END IF;
3046 -- Initialize API return status to success
3047 x_return_status := FND_API.G_RET_STS_SUCCESS;
3048 x_loading_status := 'CN_INSERTED';
3049 x_api_id := fnd_api.g_miss_num;
3050
3051 ---- Bug 8882352 ----
3052 SELECT count(a.comm_lines_api_id)
3053 INTO l_comm_line_count
3054 FROM cn_comm_lines_api a, cn_commission_headers h
3055 WHERE a.comm_lines_api_id = p_newtx_rec.comm_lines_api_id
3056 AND a.comm_lines_api_id = h.comm_lines_api_id (+)
3057 AND (a.load_status IN ('FILTERED') OR
3058 a.adjust_status IN ('FROZEN','REVERSAL', 'SCA_PENDING') OR h.adjust_status IN ('FROZEN', 'REVERSAL') OR
3059 a.trx_type IN ('ITD', 'GRP', 'THR','BONUS') OR h.trx_type IN ('ITD', 'GRP', 'THR','BONUS') OR
3060 a.terr_id IS NOT NULL);
3061
3062 IF l_comm_line_count > 0 THEN
3063 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
3064 FND_MESSAGE.Set_Name('CN', 'CN_ADJ_ADJUST_OBSOLETE');
3065 FND_MSG_PUB.Add;
3066 END IF;
3067 x_loading_status := 'CN_ADJUST_TX_ERROR';
3068 RAISE FND_API.G_EXC_ERROR;
3069 END IF;
3070 --- End Bug 8882352 fix---
3071
3072
3073 -- Get the existing data for the 'to be updated' comm_lines_api_id
3074 get_api_data(
3075 p_comm_lines_api_id => p_newtx_rec.comm_lines_api_id,
3076 x_adj_tbl => l_old_adj_tbl);
3077 --
3078 IF ((g_track_invoice = 'Y') AND
3079 (l_old_adj_tbl.COUNT > 0) AND
3080 (l_old_adj_tbl(1).trx_type = 'INV')) THEN
3081 --
3082 l_existing_data(1).salesrep_id := l_old_adj_tbl(1).direct_salesrep_id;
3083 l_existing_data(1).direct_salesrep_number
3084 := l_old_adj_tbl(1).direct_salesrep_number;
3085 l_existing_data(1).invoice_number := l_old_adj_tbl(1).invoice_number;
3086 l_existing_data(1).line_number := l_old_adj_tbl(1).line_number;
3087 l_existing_data(1).revenue_type := l_old_adj_tbl(1).revenue_type;
3088 l_existing_data(1).split_pct := l_old_adj_tbl(1).split_pct;
3089 l_existing_data(1).comm_lines_api_id
3090 := l_old_adj_tbl(1).comm_lines_api_id;
3091 --
3092 l_new_data(1).salesrep_id := NULL;
3093 l_new_data(1).direct_salesrep_number := NULL;
3094 l_new_data(1).invoice_number := NULL;
3095 l_new_data(1).line_number := NULL;
3096 l_new_data(1).revenue_type := NULL;
3097 l_new_data(1).split_pct := NULL;
3098 l_new_data(1).comm_lines_api_id := NULL;
3099 --
3100 cn_invoice_changes_pvt.update_invoice_changes(
3101 p_api_version => l_api_version,
3102 p_init_msg_list => p_init_msg_list,
3103 p_validation_level => p_validation_level,
3104 p_existing_data => l_existing_data,
3105 p_new_data => l_new_data,
3106 p_exist_data_check => 'Y',
3107 p_new_data_check => 'N',
3108 x_return_status => x_return_status,
3109 x_msg_count => x_msg_count,
3110 x_msg_data => x_msg_data,
3111 x_loading_status => x_loading_status);
3112 --
3113 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3114 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
3115 FND_MESSAGE.Set_Name('CN', 'CN_UPDATE_INV_ERROR');
3116 FND_MSG_PUB.Add;
3117 END IF;
3118 x_loading_status := 'CN_UPDATE_INV_ERROR';
3119 RAISE FND_API.G_EXC_ERROR;
3120 END IF;
3121 --
3122 END IF;
3123 --
3124 cn_adjustments_pkg.api_negate_record(
3125 x_comm_lines_api_id => p_newtx_rec.comm_lines_api_id,
3126 x_adjusted_by => get_adjusted_by,
3127 x_adjust_comments => p_newtx_rec.adjust_comments);
3128 --
3129
3130 /* Added for the Crediting Bug */
3131 /*update_credit_credentials(
3132 p_newtx_rec.comm_lines_api_id,
3133 p_newtx_rec.terr_id,
3134 p_newtx_rec.org_id,
3135 get_adjusted_by
3136 );*/
3137
3138
3139 -- vensrini Nov 14, 2005
3140 -- cn_mass_adjust_util.convert_rec_to_gmiss(
3141 -- p_rec => p_newtx_rec,
3142 -- x_api_rec => l_newtx_rec);
3143 -- vensrini Nov 14, 2005
3144
3145 l_newtx_rec := p_newtx_rec;
3146
3147 --
3148 IF ((l_old_adj_tbl(1).trx_type IN ('CM','PMT')) AND
3149 (g_track_invoice = 'Y')) THEN
3150 l_newtx_rec.split_status := 'DELINKED';
3151 END IF;
3152 --
3153 cn_invoice_changes_pvt.prepare_api_record(
3154 p_newtx_rec => l_newtx_rec,
3155 p_old_adj_tbl => l_old_adj_tbl,
3156 x_final_trx_rec => o_newtx_rec,
3157 x_return_status => x_return_status);
3158 --
3159
3160 o_newtx_rec.adj_comm_lines_api_id := l_old_adj_tbl(1).comm_lines_api_id;
3161
3162 cn_get_tx_data_pub.insert_api_record(
3163 p_api_version => p_api_version,
3164 p_init_msg_list => p_init_msg_list,
3165 p_validation_level => p_validation_level,
3166 p_action => 'UPDATE',
3167 p_newtx_rec => o_newtx_rec,
3168 x_api_id => x_api_id,
3169 x_return_status => x_return_status,
3170 x_msg_count => x_msg_count,
3171 x_msg_data => x_msg_data,
3172 x_loading_status => x_loading_status);
3173 --
3174 IF ((g_track_invoice = 'Y') AND
3175 (l_old_adj_tbl.COUNT > 0) AND
3176 (l_old_adj_tbl(1).trx_type = 'INV')) THEN
3177 -- A dummy PL/SQL table need to be created with NULL values to make a
3178 -- call to update_invoice_changes procedure.
3179 l_new_data(1).salesrep_id := o_newtx_rec.direct_salesrep_id;
3180 l_new_data(1).invoice_number := o_newtx_rec.invoice_number;
3181 l_new_data(1).line_number := o_newtx_rec.line_number;
3182 l_new_data(1).revenue_type := o_newtx_rec.revenue_type;
3183 l_new_data(1).split_pct := o_newtx_rec.split_pct;
3184 l_new_data(1).direct_salesrep_number
3185 := o_newtx_rec.direct_salesrep_number;
3186 l_new_data(1).comm_lines_api_id := x_api_id;
3187 --
3188 cn_invoice_changes_pvt.update_invoice_changes(
3189 p_api_version => p_api_version,
3190 p_validation_level => p_validation_level,
3191 p_existing_data => l_existing_data,
3192 p_new_data => l_new_data,
3193 p_exist_data_check => 'N',
3194 p_new_data_check => 'Y',
3195 x_return_status => x_return_status,
3196 x_msg_count => x_msg_count,
3197 x_msg_data => x_msg_data,
3198 x_loading_status => x_loading_status);
3199 --
3200 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3201 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
3202 FND_MESSAGE.Set_Name('CN', 'CN_UPDATE_INV_ERROR');
3203 FND_MSG_PUB.Add;
3204 END IF;
3205 x_loading_status := 'CN_UPDATE_INV_ERROR';
3206 RAISE FND_API.G_EXC_ERROR;
3207 END IF;
3208 --
3209 END IF;
3210 --
3211 IF ((g_track_invoice = 'Y') AND
3212 (l_old_adj_tbl.COUNT > 0) AND
3213 (l_old_adj_tbl(1).trx_type = 'INV')) THEN
3214 cn_invoice_changes_pvt.update_credit_memo(
3215 p_api_version => p_api_version,
3216 p_init_msg_list => p_init_msg_list,
3217 p_validation_level => p_validation_level,
3218 p_existing_data => l_existing_data,
3219 p_new_data => l_new_data,
3220 p_called_from => 'UPDATE',
3221 p_adjust_status => 'MANUAL',
3222 x_return_status => x_return_status,
3223 x_msg_count => x_msg_count,
3224 x_msg_data => x_msg_data,
3225 x_loading_status => x_loading_status);
3226 END IF;
3227 --
3228
3229 EXCEPTION
3230 WHEN FND_API.G_EXC_ERROR THEN
3231 x_return_status := FND_API.G_RET_STS_ERROR ;
3232 FND_MSG_PUB.Count_And_Get(
3233 p_count => x_msg_count ,
3234 p_data => x_msg_data ,
3235 p_encoded => FND_API.G_FALSE);
3236 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3237 x_loading_status := 'UNEXPECTED_ERR';
3238 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3239 FND_MSG_PUB.Count_And_Get(
3240 p_count => x_msg_count ,
3241 p_data => x_msg_data ,
3242 p_encoded => FND_API.G_FALSE);
3243 WHEN OTHERS THEN
3244 x_loading_status := 'UNEXPECTED_ERR';
3245 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3246 IF FND_MSG_PUB.Check_Msg_Level(
3247 FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3248 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
3249 END IF;
3250 FND_MSG_PUB.Count_And_Get(
3251 p_count => x_msg_count ,
3252 p_data => x_msg_data ,
3253 p_encoded => FND_API.G_FALSE);
3254 END;
3255 --
3256 PROCEDURE call_load(
3257 p_api_version IN NUMBER,
3258 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
3259 p_commit IN VARCHAR2 := FND_API.G_FALSE,
3260 p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL,
3261 p_salesrep_id IN NUMBER := FND_API.G_MISS_NUM,
3262 p_pr_date_from IN DATE,
3263 p_pr_date_to IN DATE,
3264 p_cls_rol_flag IN CHAR,
3265 p_load_method IN VARCHAR2,
3266 p_org_id IN NUMBER,
3267 x_return_status OUT NOCOPY VARCHAR2,
3268 x_msg_count OUT NOCOPY NUMBER,
3269 x_msg_data OUT NOCOPY VARCHAR2,
3270 x_loading_status OUT NOCOPY VARCHAR2,
3271 x_process_audit_id OUT NOCOPY NUMBER) IS
3272 --
3273 l_api_name CONSTANT VARCHAR2(30) := 'call_load';
3274 l_api_version CONSTANT NUMBER := 1.0;
3275 l_salesrep_id NUMBER;
3276 l_cls_rol_flag CHAR(1) := 'Y';
3277 --
3278 BEGIN
3279 -- Standard Start of API savepoint
3280 SAVEPOINT call_load;
3281 -- Standard call to check for call compatibility.
3282 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
3283 p_api_version ,
3284 l_api_name,
3285 G_PKG_NAME ) THEN
3286 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3287 END IF;
3288 -- Initialize message list if p_init_msg_list is set to TRUE.
3289 IF FND_API.to_Boolean( p_init_msg_list ) THEN
3290 FND_MSG_PUB.initialize;
3291 END IF;
3292 -- Initialize API return status to success
3293 x_return_status := FND_API.G_RET_STS_SUCCESS;
3294 x_loading_status := 'CN_INSERTED';
3295 IF (p_salesrep_id = FND_API.G_MISS_NUM) THEN
3296 l_salesrep_id := NULL;
3297 ELSE
3298 l_salesrep_id := p_salesrep_id;
3299 END IF;
3300 IF (p_cls_rol_flag <> 'Y') THEN
3301 l_cls_rol_flag := 'N';
3302 END IF;
3303 IF (p_load_method = 'CONC') THEN
3304
3305 x_process_audit_id :=
3306 FND_REQUEST.SUBMIT_REQUEST(
3307 application => 'CN',
3308 program => 'CN_TRX_INTERFACE',
3309 argument1 => TO_CHAR(l_salesrep_id),
3310 argument2 => TO_CHAR(p_pr_date_from,'YYYY/MM/DD HH24:MI:SS'),
3311 argument3 => TO_CHAR(p_pr_date_to,'YYYY/MM/DD HH24:MI:SS'),
3312 argument4 => l_cls_rol_flag);
3313 ELSE
3314 cn_transaction_load_pub.Load
3315 (p_api_version => p_api_version,
3316 p_init_msg_list => p_init_msg_list,
3317 p_commit => p_commit,
3318 p_validation_level => p_validation_level,
3319 p_salesrep_id => l_salesrep_id,
3320 p_start_date => p_pr_date_from,
3321 p_end_date => p_pr_date_to,
3322 p_cls_rol_flag => p_cls_rol_flag,
3323 p_org_id => p_org_id,
3324 x_return_status => x_return_status,
3325 x_msg_count => x_msg_count,
3326 x_msg_data => x_msg_data,
3327 x_loading_status => x_loading_status,
3328 x_process_audit_id => x_process_audit_id);
3329 END IF;
3330 EXCEPTION
3331 WHEN FND_API.G_EXC_ERROR THEN
3332 x_return_status := FND_API.G_RET_STS_ERROR ;
3333 FND_MSG_PUB.Count_And_Get(
3334 p_count => x_msg_count ,
3335 p_data => x_msg_data ,
3336 p_encoded => FND_API.G_FALSE);
3337 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3338 x_loading_status := 'UNEXPECTED_ERR';
3339 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3340 FND_MSG_PUB.Count_And_Get(
3341 p_count => x_msg_count ,
3342 p_data => x_msg_data ,
3343 p_encoded => FND_API.G_FALSE);
3344 WHEN OTHERS THEN
3345 x_loading_status := 'UNEXPECTED_ERR';
3346 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3347 IF FND_MSG_PUB.Check_Msg_Level(
3348 FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3349 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
3350 END IF;
3351 FND_MSG_PUB.Count_And_Get(
3352 p_count => x_msg_count ,
3353 p_data => x_msg_data ,
3354 p_encoded => FND_API.G_FALSE);
3355 END;
3356 --
3357 END cn_get_tx_data_pub;
3358
3359