[Home] [Help]
PACKAGE BODY: APPS.CN_UN_PROC_PUB
Source
1 PACKAGE BODY cn_un_proc_pub AS
2 --$Header: cnunprob.pls 115.10 2002/11/21 21:11:19 hlchen ship $
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_UN_PROC_PUB';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'cnunprob.pls';
6 G_LAST_UPDATE_DATE DATE := sysdate;
7 G_LAST_UPDATED_BY NUMBER := fnd_global.user_id;
8 G_CREATION_DATE DATE := sysdate;
9 G_CREATED_BY NUMBER := fnd_global.user_id;
10 G_LAST_UPDATE_LOGIN NUMBER := fnd_global.login_id;
11
12 PROCEDURE get_adj(
13 p_api_version IN NUMBER,
14 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
15 p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL,
16 x_return_status OUT NOCOPY VARCHAR2,
17 x_msg_count OUT NOCOPY NUMBER,
18 x_msg_data OUT NOCOPY VARCHAR2,
19 x_loading_status OUT NOCOPY VARCHAR2,
20 p_salesrep_id IN NUMBER,
21 p_pr_date_from IN DATE,
22 p_pr_date_to IN DATE,
23 p_invoice_num IN VARCHAR2,
24 p_order_num IN NUMBER,
25 p_adjust_status IN VARCHAR2,
26 p_adjust_date IN DATE,
27 p_trx_type IN VARCHAR2,
28 p_calc_status IN VARCHAR2,
29 p_load_status IN VARCHAR2,
30 p_date_pattern IN DATE,
31 p_start_record IN NUMBER := 1,
32 p_increment_count IN NUMBER,
33 x_adj_tbl OUT NOCOPY adj_tbl_type,
34 x_adj_count OUT NOCOPY NUMBER,
35 x_total_sales_credit OUT NOCOPY NUMBER,
36 x_total_commission OUT NOCOPY NUMBER) IS
37
38 l_api_name CONSTANT VARCHAR2(30) := 'get_adj';
39 l_api_version CONSTANT NUMBER := 1.0;
40 l_flag NUMBER := 0;
41 l_column_value NUMBER;
42 l_pr_date_from DATE;
43 l_pr_date_to DATE;
44 l_adjust_date DATE;
45
46 adj cn_un_proc_pub.unproc_rec_type;
47
48 TYPE rc IS ref cursor;
49 query_cur rc;
50
51 query VARCHAR2(10000);
52 l_select VARCHAR2(10000);
53 l_from VARCHAR2(10000);
54 l_where VARCHAR2(10000);
55 l_insert VARCHAR2(10000);
56
57 BEGIN
58 --
59 -- Standard call to check for call compatibility.
60 --
61 IF NOT FND_API.Compatible_API_Call(
62 l_api_version,
63 p_api_version,
64 l_api_name,
65 G_PKG_NAME )
66 THEN
67 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
68 END IF;
69 --
70 -- Initialize message list if p_init_msg_list is set to TRUE.
71 --
72 IF FND_API.to_Boolean( p_init_msg_list ) THEN
73 FND_MSG_PUB.initialize;
74 END IF;
75 --
76 -- Initialize API return status to success
77 --
78 x_return_status := FND_API.G_RET_STS_SUCCESS;
79 x_loading_status := 'CN_INSERTED';
80 --
81 -- API body
82 --
83 x_adj_count := 0;
84 x_total_sales_credit := 0;
85 x_total_commission := 0;
86
87 IF (p_date_pattern IS NOT NULL) THEN
88 IF (p_adjust_date <> p_date_pattern) THEN
89 l_adjust_date := p_adjust_date;
90 END IF;
91 IF (p_pr_date_from <> p_date_pattern) THEN
92 l_pr_date_from := p_pr_date_from;
93 END IF;
94 IF (p_pr_date_to <> p_date_pattern) THEN
95 l_pr_date_to := p_pr_date_to;
96 END IF;
97 END IF;
98
99 /* Trying to contruct a cursor based on P_LOAD_STATUS Flag
100 If the P_LOAD_STATUS is 'Unloaded' get the data from
101 cn_comm_lines_api table. Otherwise get the data from
102 cn_commission_headers table. */
103
104 IF (p_load_status = 'Unloaded') THEN
105 l_select := '
106 SELECT CTRX.invoice_number invoice_number,
107 CTRX.invoice_date invoice_date,
108 CTRX.order_number order_number,
109 CTRX.booked_date order_date,
110 CTRX.creation_date creation_date,
111 CTRX.processed_date processed_date,
112 CL1.meaning trx_type_disp,
113 CL2.meaning adjust_status_disp,
114 CTRX.adjusted_by adjusted_by,
115 CTRX.load_status load_status,
116 '''' calc_status_disp,
117 CTRX.transaction_amount sales_credit,
118 CTRX.commission_amount commission,
119 CTRX.adjust_date adjust_date, ';
120 ELSE
121 l_select := '
122 SELECT ctrx.invoice_number invoice_number,
123 ctrx.invoice_date invoice_date,
124 ctrx.order_number order_number,
125 ctrx.booked_date order_date,
126 ctrx.creation_date creation_date,
127 ctrx.processed_date processed_date,
128 clt.meaning trx_type_disp,
129 clad.meaning adjust_status_disp,
130 ctrx.adjusted_by adjusted_by,
131 '''' load_status,
132 cls.meaning calc_status_disp,
133 ctrx.transaction_amount sales_credit,
134 ctrx.commission_amount commission,
135 ctrx.adjust_date adjust_date, ';
136 END IF;
137 l_select := l_select||'
138 ctrx.attribute1,ctrx.attribute2,ctrx.attribute3,
139 ctrx.attribute4, ctrx.attribute5, ctrx.attribute6,
140 ctrx.attribute7, ctrx.attribute8, ctrx.attribute9,
141 ctrx.attribute10, ctrx.attribute11, ctrx.attribute12,
142 ctrx.attribute13, ctrx.attribute14, ctrx.attribute15,
143 ctrx.attribute16, ctrx.attribute17, ctrx.attribute18,
144 ctrx.attribute19, ctrx.attribute20, ctrx.attribute21,
145 ctrx.attribute22, ctrx.attribute23, ctrx.attribute24,
146 ctrx.attribute25, ctrx.attribute26, ctrx.attribute27,
147 ctrx.attribute28, ctrx.attribute29, ctrx.attribute30,
148 ctrx.attribute31, ctrx.attribute32, ctrx.attribute33,
149 ctrx.attribute34, ctrx.attribute35, ctrx.attribute36,
150 ctrx.attribute37, ctrx.attribute38, ctrx.attribute39,
151 ctrx.attribute40, ctrx.attribute41, ctrx.attribute42,
152 ctrx.attribute43, ctrx.attribute44, ctrx.attribute45,
153 ctrx.attribute46, ctrx.attribute47, ctrx.attribute48,
154 ctrx.attribute49, ctrx.attribute50, ctrx.attribute51,
155 ctrx.attribute52, ctrx.attribute53, ctrx.attribute54,
156 ctrx.attribute55, ctrx.attribute56, ctrx.attribute57,
157 ctrx.attribute58, ctrx.attribute59, ctrx.attribute60,
158 ctrx.attribute61, ctrx.attribute62, ctrx.attribute63,
159 ctrx.attribute64, ctrx.attribute65, ctrx.attribute66,
160 ctrx.attribute67, ctrx.attribute68, ctrx.attribute69,
161 ctrx.attribute70, ctrx.attribute71, ctrx.attribute72,
162 ctrx.attribute73, ctrx.attribute74, ctrx.attribute75,
163 ctrx.attribute76, ctrx.attribute77, ctrx.attribute78,
164 ctrx.attribute79, ctrx.attribute80, ctrx.attribute81,
165 ctrx.attribute82, ctrx.attribute83, ctrx.attribute84,
166 ctrx.attribute85, ctrx.attribute86, ctrx.attribute87,
167 ctrx.attribute88, ctrx.attribute89, ctrx.attribute90,
168 ctrx.attribute91, ctrx.attribute92, ctrx.attribute93,
169 ctrx.attribute94, ctrx.attribute95, ctrx.attribute96,
170 ctrx.attribute97, ctrx.attribute98, ctrx.attribute99,
171 ctrx.attribute100 ';
172 --
173 IF (p_load_status = 'Unloaded') THEN
174 l_from := '
175 FROM cn_comm_lines_api ctrx,
176 cn_lookups cl1,
177 cn_lookups cl2 ';
178 ELSE
179 l_from := '
180 FROM cn_commission_headers ctrx,
181 cn_lookups clt,
182 cn_lookups cls,
183 cn_lookups clad ';
184
185 END IF;
186 --
187 IF (p_load_status = 'Unloaded') THEN
188 l_where := '
189 WHERE ctrx.trx_type = cl1.lookup_code(+)
190 AND cl1.lookup_type(+) = ''TRX TYPES''
191 AND ctrx.adjust_status = cl2.lookup_code(+)
192 AND cl2.lookup_type(+) = ''ADJUST_STATUS''
193 AND ctrx.load_status <> ''LOADED''
194 AND ctrx.salesrep_id = :1 ';
195 ELSE
196 l_where := '
197 WHERE ctrx.trx_type = clt.lookup_code(+)
198 AND clt.lookup_type (+)= ''TRX TYPES''
199 AND ctrx.status = cls.lookup_code(+)
200 AND cls.lookup_type (+)= ''TRX_STATUS''
201 AND ctrx.adjust_status = clad.lookup_code (+)
202 AND clad.lookup_type (+)= ''ADJUST_STATUS''
203 AND ctrx.direct_salesrep_id = :1 ';
204 END IF;
205 --
206 IF (l_pr_date_from IS NOT NULL) THEN
207 l_where := l_where || ' and ctrx.processed_date >= :2 ';
208 ELSE
209 l_where := l_where || ' and :2 is null ';
210 END IF;
211 --
212 IF (l_pr_date_to IS NOT NULL) THEN
213 l_where := l_where || ' and ctrx.processed_date <= :3 ';
214 ELSE
215 l_where := l_where || ' and :3 is null ';
216 END IF;
217 --
218 IF (p_invoice_num <> 'ALL') THEN
219 l_where := l_where || ' and ctrx.invoice_number = :4 ';
220 ELSE
221 l_where := l_where || ' and :4 = ''ALL''';
222 END IF;
223 --
224 IF (p_order_num <> -99999) THEN
225 l_where := l_where || ' and ctrx.order_number = :5 ';
226 ELSE
227 l_where := l_where || ' and :5 = -99999';
228 END IF;
229 --
230 IF (p_adjust_status <> 'ALL') THEN
231 l_where := l_where || ' and ctrx.adjust_status = :6 ';
232 ELSE
233 l_where := l_where || ' and :6 = ''ALL''';
234 END IF;
235 --
236 IF (l_adjust_date IS NOT NULL) THEN
237 l_where := l_where || ' and trunc(ctrx.adjust_date) = trunc(:7) ';
238 ELSE
239 l_where := l_where || ' and :7 is null ';
240 END IF;
241 --
242 IF (p_trx_type <> 'ALL') THEN
243 l_where := l_where || ' and ctrx.trx_type = :8 ';
244 ELSE
245 l_where := l_where || ' and :8 = ''ALL''';
246 END IF;
247 --
248 IF (p_load_status = 'Loaded') THEN
249 IF (p_calc_status <> 'ALL') THEN
250 l_where := l_where || ' and ctrx.status = :9 ';
251 ELSE
252 l_where := l_where || ' and :9 = ''ALL''';
253 END IF;
254 END IF;
255 --
256 query := l_select||' '||l_from||' '||l_where;
257 --
258 IF (p_load_status = 'Unloaded') THEN
259 OPEN query_cur FOR query USING
260 p_salesrep_id, -- :1
261 trunc(l_pr_date_from), -- :2
262 trunc(l_pr_date_to)+.99999, -- :3
263 p_invoice_num, -- :4
264 p_order_num, -- :5
265 p_adjust_status, -- :6
266 l_adjust_date, -- :7
267 p_trx_type; -- :8
268 ELSE
269 OPEN query_cur FOR query USING
270 p_salesrep_id, -- :1
271 trunc(l_pr_date_from), -- :2
272 trunc(l_pr_date_to)+.99999, -- :3
273 p_invoice_num, -- :4
274 p_order_num, -- :5
275 p_adjust_status, -- :6
276 l_adjust_date, -- :7
277 p_trx_type, -- :8
278 p_calc_status; -- :9
279 END IF;
280 LOOP
281 FETCH query_cur INTO adj;
282 EXIT WHEN query_cur%NOTFOUND;
283 x_adj_count := x_adj_count + 1;
284 x_total_sales_credit := x_total_sales_credit + Nvl(adj.sales_credit,0);
285 x_total_commission := x_total_commission + Nvl(adj.commission,0);
286
287 IF (( p_increment_count = -9999) OR
288 (x_adj_count BETWEEN p_start_record AND
289 (p_start_record + p_increment_count -1))) THEN
290 x_adj_tbl(x_adj_count).invoice_number := adj.invoice_number;
291 x_adj_tbl(x_adj_count).invoice_date := adj.invoice_date;
292 x_adj_tbl(x_adj_count).order_number := adj.order_number;
293 x_adj_tbl(x_adj_count).order_date := adj.order_date;
294 x_adj_tbl(x_adj_count).creation_date := adj.creation_date;
295 x_adj_tbl(x_adj_count).processed_date := adj.processed_date;
296 x_adj_tbl(x_adj_count).trx_type_disp := adj.trx_type_disp;
297 x_adj_tbl(x_adj_count).adjust_status_disp := adj.adjust_status_disp;
298 x_adj_tbl(x_adj_count).adjusted_by := adj.adjusted_by;
299 x_adj_tbl(x_adj_count).sales_credit := Nvl(adj.sales_credit,0);
300 x_adj_tbl(x_adj_count).commission := Nvl(adj.commission,0);
301 x_adj_tbl(x_adj_count).adjust_date := adj.adjust_date;
302 x_adj_tbl(x_adj_count).attribute1 := adj.attribute1;
303 x_adj_tbl(x_adj_count).attribute2 := adj.attribute2;
304 x_adj_tbl(x_adj_count).attribute3 := adj.attribute3;
305 x_adj_tbl(x_adj_count).attribute4 := adj.attribute4;
306 x_adj_tbl(x_adj_count).attribute5 := adj.attribute5;
307 x_adj_tbl(x_adj_count).attribute6 := adj.attribute6;
308 x_adj_tbl(x_adj_count).attribute7 := adj.attribute7;
309 x_adj_tbl(x_adj_count).attribute8 := adj.attribute8;
310 x_adj_tbl(x_adj_count).attribute9 := adj.attribute9;
311 x_adj_tbl(x_adj_count).attribute10 := adj.attribute10;
312 x_adj_tbl(x_adj_count).attribute11 := adj.attribute11;
313 x_adj_tbl(x_adj_count).attribute12 := adj.attribute12;
314 x_adj_tbl(x_adj_count).attribute13 := adj.attribute13;
315 x_adj_tbl(x_adj_count).attribute14 := adj.attribute14;
316 x_adj_tbl(x_adj_count).attribute15 := adj.attribute15;
317 x_adj_tbl(x_adj_count).attribute16 := adj.attribute16;
318 x_adj_tbl(x_adj_count).attribute17 := adj.attribute17;
319 x_adj_tbl(x_adj_count).attribute18 := adj.attribute18;
320 x_adj_tbl(x_adj_count).attribute19 := adj.attribute19;
321 x_adj_tbl(x_adj_count).attribute20 := adj.attribute20;
322 x_adj_tbl(x_adj_count).attribute21 := adj.attribute21;
323 x_adj_tbl(x_adj_count).attribute22 := adj.attribute22;
324 x_adj_tbl(x_adj_count).attribute23 := adj.attribute23;
325 x_adj_tbl(x_adj_count).attribute24 := adj.attribute24;
326 x_adj_tbl(x_adj_count).attribute25 := adj.attribute25;
327 x_adj_tbl(x_adj_count).attribute26 := adj.attribute26;
328 x_adj_tbl(x_adj_count).attribute27 := adj.attribute27;
329 x_adj_tbl(x_adj_count).attribute28 := adj.attribute28;
330 x_adj_tbl(x_adj_count).attribute29 := adj.attribute29;
331 x_adj_tbl(x_adj_count).attribute30 := adj.attribute30;
332 x_adj_tbl(x_adj_count).attribute31 := adj.attribute31;
333 x_adj_tbl(x_adj_count).attribute32 := adj.attribute32;
334 x_adj_tbl(x_adj_count).attribute33 := adj.attribute33;
335 x_adj_tbl(x_adj_count).attribute34 := adj.attribute34;
336 x_adj_tbl(x_adj_count).attribute35 := adj.attribute35;
337 x_adj_tbl(x_adj_count).attribute36 := adj.attribute36;
338 x_adj_tbl(x_adj_count).attribute37 := adj.attribute37;
339 x_adj_tbl(x_adj_count).attribute38 := adj.attribute38;
340 x_adj_tbl(x_adj_count).attribute39 := adj.attribute39;
341 x_adj_tbl(x_adj_count).attribute40 := adj.attribute40;
342 x_adj_tbl(x_adj_count).attribute41 := adj.attribute41;
343 x_adj_tbl(x_adj_count).attribute42 := adj.attribute42;
344 x_adj_tbl(x_adj_count).attribute43 := adj.attribute43;
345 x_adj_tbl(x_adj_count).attribute44 := adj.attribute44;
346 x_adj_tbl(x_adj_count).attribute45 := adj.attribute45;
347 x_adj_tbl(x_adj_count).attribute46 := adj.attribute46;
348 x_adj_tbl(x_adj_count).attribute47 := adj.attribute47;
349 x_adj_tbl(x_adj_count).attribute48 := adj.attribute48;
350 x_adj_tbl(x_adj_count).attribute49 := adj.attribute49;
351 x_adj_tbl(x_adj_count).attribute50 := adj.attribute50;
352 x_adj_tbl(x_adj_count).attribute51 := adj.attribute51;
353 x_adj_tbl(x_adj_count).attribute52 := adj.attribute52;
357 x_adj_tbl(x_adj_count).attribute56 := adj.attribute56;
354 x_adj_tbl(x_adj_count).attribute53 := adj.attribute53;
355 x_adj_tbl(x_adj_count).attribute54 := adj.attribute54;
356 x_adj_tbl(x_adj_count).attribute55 := adj.attribute55;
358 x_adj_tbl(x_adj_count).attribute57 := adj.attribute57;
359 x_adj_tbl(x_adj_count).attribute58 := adj.attribute58;
360 x_adj_tbl(x_adj_count).attribute59 := adj.attribute59;
361 x_adj_tbl(x_adj_count).attribute60 := adj.attribute60;
362 x_adj_tbl(x_adj_count).attribute61 := adj.attribute61;
363 x_adj_tbl(x_adj_count).attribute62 := adj.attribute62;
364 x_adj_tbl(x_adj_count).attribute63 := adj.attribute63;
365 x_adj_tbl(x_adj_count).attribute64 := adj.attribute64;
366 x_adj_tbl(x_adj_count).attribute65 := adj.attribute65;
367 x_adj_tbl(x_adj_count).attribute66 := adj.attribute66;
368 x_adj_tbl(x_adj_count).attribute67 := adj.attribute67;
369 x_adj_tbl(x_adj_count).attribute68 := adj.attribute68;
370 x_adj_tbl(x_adj_count).attribute69 := adj.attribute69;
371 x_adj_tbl(x_adj_count).attribute70 := adj.attribute70;
372 x_adj_tbl(x_adj_count).attribute71 := adj.attribute71;
373 x_adj_tbl(x_adj_count).attribute72 := adj.attribute72;
374 x_adj_tbl(x_adj_count).attribute73 := adj.attribute73;
375 x_adj_tbl(x_adj_count).attribute74 := adj.attribute74;
376 x_adj_tbl(x_adj_count).attribute75 := adj.attribute75;
377 x_adj_tbl(x_adj_count).attribute76 := adj.attribute76;
378 x_adj_tbl(x_adj_count).attribute77 := adj.attribute77;
379 x_adj_tbl(x_adj_count).attribute78 := adj.attribute78;
380 x_adj_tbl(x_adj_count).attribute79 := adj.attribute79;
381 x_adj_tbl(x_adj_count).attribute80 := adj.attribute80;
382 x_adj_tbl(x_adj_count).attribute81 := adj.attribute81;
383 x_adj_tbl(x_adj_count).attribute82 := adj.attribute82;
384 x_adj_tbl(x_adj_count).attribute83 := adj.attribute83;
385 x_adj_tbl(x_adj_count).attribute84 := adj.attribute84;
386 x_adj_tbl(x_adj_count).attribute85 := adj.attribute85;
387 x_adj_tbl(x_adj_count).attribute86 := adj.attribute86;
388 x_adj_tbl(x_adj_count).attribute87 := adj.attribute87;
389 x_adj_tbl(x_adj_count).attribute88 := adj.attribute88;
390 x_adj_tbl(x_adj_count).attribute89 := adj.attribute89;
391 x_adj_tbl(x_adj_count).attribute90 := adj.attribute90;
392 x_adj_tbl(x_adj_count).attribute91 := adj.attribute91;
393 x_adj_tbl(x_adj_count).attribute92 := adj.attribute92;
394 x_adj_tbl(x_adj_count).attribute93 := adj.attribute93;
395 x_adj_tbl(x_adj_count).attribute94 := adj.attribute94;
396 x_adj_tbl(x_adj_count).attribute95 := adj.attribute95;
397 x_adj_tbl(x_adj_count).attribute96 := adj.attribute96;
398 x_adj_tbl(x_adj_count).attribute97 := adj.attribute97;
399 x_adj_tbl(x_adj_count).attribute98 := adj.attribute98;
400 x_adj_tbl(x_adj_count).attribute99 := adj.attribute99;
401 x_adj_tbl(x_adj_count).attribute100 := adj.attribute100;
402 IF (p_load_status = 'Unloaded') THEN
403 x_adj_tbl(x_adj_count).load_status := adj.load_status;
404 ELSE
405 x_adj_tbl(x_adj_count).calc_status_disp := adj.calc_status_disp;
406 END IF;
407 END IF;
408 END LOOP;
409 EXCEPTION
410 WHEN FND_API.G_EXC_ERROR THEN
411 x_return_status := FND_API.G_RET_STS_ERROR ;
412 FND_MSG_PUB.Count_And_Get (
413 p_count => x_msg_count ,
414 p_data => x_msg_data ,
415 p_encoded => FND_API.G_FALSE);
416 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
417 x_loading_status := 'UNEXPECTED_ERR';
418 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
419 FND_MSG_PUB.Count_And_Get (
420 p_count => x_msg_count ,
421 p_data => x_msg_data ,
422 p_encoded => FND_API.G_FALSE);
423 WHEN OTHERS THEN
424 x_loading_status := 'UNEXPECTED_ERR';
425 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
426 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
427 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
428 END IF;
429 FND_MSG_PUB.Count_And_Get (
430 p_count => x_msg_count ,
431 p_data => x_msg_data ,
432 p_encoded => FND_API.G_FALSE);
433
434 END;
435 --
436 END cn_un_proc_pub;