1 PACKAGE BODY CN_RAM_ADJUSTMENTS_PKG AS
2 -- $Header: cnvramb.pls 120.12 2008/03/18 19:35:47 fmburu ship $
3
4
5 PROCEDURE identify (
6 x_start_period cn_periods.period_id%TYPE,
7 x_end_period cn_periods.period_id%TYPE,
8 debug_pipe VARCHAR2 DEFAULT NULL,
9 debug_level NUMBER DEFAULT NULL,
10 x_org_id NUMBER ) IS
11
12
13 -- Join cn_not_trx and ar_revenue_adjustments to get the customer_trx_id
14 -- of the transactions (header level) which have been RAM-adjusted after
15 -- last collection run within the interested period.
16
17 -- Note : the application_date and last_collected_date used in the where
18 -- clause contain timestamp. Do not use 'trunc' so that the same adjustment
19 -- won't be handled again when run RAM Collection again on the same day.
20
21 -- Note : the "within interested period" condition has been changed to use
22 -- the period when the adjustments was made, not the original transaction
23 -- processed date (GL date). This is the requirement changed on 05/08/2002
24
25 CURSOR adj_cursor(p_start_date DATE, p_end_date DATE) IS
26 select distinct
27 cnt.not_trx_id,
28 ara.customer_trx_id,
29 ara.from_cust_trx_line_id,
30 ara.from_inventory_item_id,
31 ara.from_category_id,
32 ara.line_selection_mode
33 from cn_not_trx cnt, ar_revenue_adjustments ara
34 where
35 nvl(cnt.last_collected_date, cnt.notified_date) <= ara.application_date and
36 cnt.collected_flag = 'Y' and -- has been collected before
37 cnt.source_doc_type = 'AR' and -- AR collection
38 cnt.event_id = cn_global.inv_event_id and -- INV/CM/DM collection
39 cnt.source_trx_id = ara.customer_trx_id
40 -- cnt.processed_date between p_start_date and p_end_date -- within interested period
41 AND cnt.org_id = x_org_id -- MOAC Changes made by Ashley
42 AND ara.org_id = cnt.org_id -- MOAC Changes made by Ashley
43 AND trunc(ara.application_date) between p_start_date and p_end_date -- within interested period
44 order by ara.customer_trx_id, line_selection_mode;
45
46 x_trx_identified_count NUMBER := 0;
47 x_proc_audit_id NUMBER;
48 x_start_date DATE;
49 x_end_date DATE;
50 x_rowid ROWID;
51 x_last_A_customer_trx_id NUMBER;
52 l_sys_batch_size NUMBER;
53
54 CURSOR batch_size IS SELECT system_batch_size FROM cn_repositories WHERE org_id = x_org_id;
55
56
57 BEGIN
58 IF (debug_pipe IS NOT NULL) THEN
59 cn_debug.init_pipe(debug_pipe, debug_level);
60 END IF;
61 cn_debug.print_msg('>>identify RAM adjustments', 1);
62 cn_message_pkg.debug('identify RAM adjustments>>');
63 fnd_file.put_line(fnd_file.Log, 'identify RAM adjustments>>');
64
65 x_proc_audit_id := NULL; -- Will get a value in the call below
66 cn_process_audits_pkg.insert_row(x_rowid, x_proc_audit_id, NULL,'RAMADJ', 'Identify RAM adjustments', NULL, NULL, NULL, NULL, NULL, SYSDATE, NULL, x_org_id);
67
68 cn_periods_api.set_dates(x_start_period,
69 x_end_period,
70 x_org_id,
71 x_start_date,
72 x_end_date);
73
74 cn_message_pkg.debug('identify RAM adjustments: identifing adjustments for transactions from '||x_start_date ||' to '||x_end_date ||'.');
75 fnd_file.put_line(fnd_file.Log, 'identify RAM adjustments: identifing adjustments for transactions from '||x_start_date ||' to '||x_end_date ||'.');
76
77 OPEN batch_size;
78 FETCH batch_size INTO l_sys_batch_size;
79 CLOSE batch_size;
80
81
82 -- For each adjustments (distinct on Not_trx_id, Header_id, Line_id, Item_id,
83 -- Category_id, and mode), which has been identified in ar_revenue_adjustments
84 -- table, figure out the trx_line_id of the corresponding transaction lines
85 -- that got adjusted and set the flags.
86
87 FOR adj IN adj_cursor(x_start_date, x_end_date) LOOP
88
89 x_trx_identified_count := x_trx_identified_count + 1;
90
91 -- line_selection_mode = ('A','C','I','S')
92
93 -- line_selection_mode = 'A' (All lines)
94 -- Adjustment was applied to all lines of the transactions.
95 if (adj.line_selection_mode = 'A') then
96
97 update cn_trx_lines
98 set adjusted_flag = 'Y',
99 negated_flag = 'N',
100 collected_flag = 'N',
101 adj_batch_id = FLOOR(x_trx_identified_count/l_sys_batch_size)
102 where trx_line_id in (
103 select ctl.trx_line_id
104 from cn_not_trx cnt, cn_trx ct, cn_trx_lines ctl
105 where cnt.not_trx_id = adj.not_trx_id and
106 ct.not_trx_id = cnt.not_trx_id and
107 ctl.trx_id = ct.trx_id and
108 cnt.source_trx_id = adj.customer_trx_id and
109 ct.source_trx_id = cnt.source_trx_id
110 AND cnt.org_id = x_org_id -- MOAC Changes made by Ashley
111 AND ct.org_id = cnt.org_id -- MOAC Changes made by Ashley
112 AND ctl.org_id = ct.org_id -- MOAC Changes made by Ashley
113 )
114 AND org_id = x_org_id; -- MOAC Changes made by Ashley
115
116 x_last_A_customer_trx_id := adj.customer_trx_id;
117
118 else -- line_select_mode = 'C', 'I', or 'S'
119
120 if (adj.customer_trx_id = x_last_A_customer_trx_id) then
121 -- no need to handle S I C cases if A (all lines)
122 -- for the same customer_trx_id has been done before.
123 null;
124 else
125
126 -- line_selection_mode = 'S' (Specific line)
127 -- Adjustment was applied to a specific line only.
128 if (adj.line_selection_mode = 'S') then
129
130 update cn_trx_lines
131 set adjusted_flag = 'Y',
132 negated_flag = 'N',
133 collected_flag = 'N',
134 adj_batch_id = FLOOR(x_trx_identified_count/l_sys_batch_size)
135 where trx_line_id in (
136 select ctl.trx_line_id
137 from cn_not_trx cnt, cn_trx ct, cn_trx_lines ctl
138 where cnt.not_trx_id = adj.not_trx_id and
139 ct.not_trx_id = cnt.not_trx_id and
140 ctl.trx_id = ct.trx_id and
141 cnt.source_trx_id = adj.customer_trx_id and
142 ct.source_trx_id = cnt.source_trx_id and
143 ctl.source_trx_line_id = adj.from_cust_trx_line_id
144 AND cnt.org_id = x_org_id -- MOAC Changes made by Ashley
145 AND ct.org_id = cnt.org_id -- MOAC Changes made by Ashley
146 AND ctl.org_id = ct.org_id -- MOAC Changes made by Ashley
147
148 )
149 AND org_id = x_org_id; -- MOAC Changes made by Ashley
150 end if;
151
152
153 -- line_selection_mode = 'I' (Inventory item)
154 -- Adjustment was applied to all lines with a specific inventory item.
155 if (adj.line_selection_mode = 'I') then
156
157 update cn_trx_lines
158 set adjusted_flag = 'Y',
159 negated_flag = 'N',
160 collected_flag = 'N',
161 adj_batch_id = FLOOR(x_trx_identified_count/l_sys_batch_size)
162 where trx_line_id in (
163 select ctl.trx_line_id
164 from cn_not_trx cnt, cn_trx ct, cn_trx_lines ctl
165 where cnt.not_trx_id = adj.not_trx_id and
166 ct.not_trx_id = cnt.not_trx_id and
167 ctl.trx_id = ct.trx_id and
168 cnt.source_trx_id = adj.customer_trx_id and
169 ct.source_trx_id = cnt.source_trx_id and
170 ctl.inventory_id = adj.from_inventory_item_id
171 AND cnt.org_id = x_org_id -- MOAC Changes made by Ashley
172 AND ct.org_id = cnt.org_id -- MOAC Changes made by Ashley
173 AND ctl.org_id = ct.org_id -- MOAC Changes made by Ashley
174
175 )
176 AND org_id = x_org_id; -- MOAC Changes made by Ashley
177 end if;
178
179
180
181
182 -- line_selection_mode = 'C' (item Category)
183 -- Adjustment was applied to all lines with items that belong to a certain category.
184 if (adj.line_selection_mode = 'C') THEN
185 --
186 -- rewrite the update statement for performance issue
187 --
188 --old statement
189 update cn_trx_lines
190 set adjusted_flag = 'Y',
191 negated_flag = 'N',
192 collected_flag = 'N',
193 adj_batch_id = FLOOR(x_trx_identified_count/l_sys_batch_size)
194 where trx_line_id in (
195 select ctl.trx_line_id
196 from cn_not_trx cnt, cn_trx ct, cn_trx_lines ctl,
197 (select rctl.customer_trx_line_id
198 from ra_customer_trx_lines rctl
199 where rctl.customer_trx_id = adj.customer_trx_id and
200 exists
201 (select 1
202 from mtl_item_categories mic
203 where mic.category_id = adj.from_category_id and
204 mic.inventory_item_id = rctl.inventory_item_id)
205 ) r
206 where cnt.not_trx_id = adj.not_trx_id and
207 ct.not_trx_id = cnt.not_trx_id and
208 ctl.trx_id = ct.trx_id and
209 cnt.source_trx_id = adj.customer_trx_id and
210 ct.source_trx_id = adj.customer_trx_id and
211 ctl.source_trx_line_id = r.customer_trx_line_id
212 AND cnt.org_id = x_org_id -- MOAC Changes made by Ashley
213 AND ct.org_id = cnt.org_id -- MOAC Changes made by Ashley
214 AND ctl.org_id = ct.org_id -- MOAC Changes made by Ashley
215 )
216 AND org_id = x_org_id;
217
218 /* -- new statement
219 update cn_trx_lines
220 set adjusted_flag = 'Y',
221 negated_flag = 'N',
222 collected_flag = 'N',
223 adj_batch_id = FLOOR(x_trx_identified_count/l_sys_batch_size)
224 where trx_line_id in (
225 select ctl.trx_line_id
226 from cn_not_trx cnt, cn_trx ct, cn_trx_lines ctl,
227 mtl_item_categories mic
228 where cnt.not_trx_id = adj.not_trx_id and
229 ct.not_trx_id = cnt.not_trx_id and
230 ctl.trx_id = ct.trx_id and
231 cnt.source_trx_id = adj.customer_trx_id and
232 ct.source_trx_id = cnt.source_trx_id and
233 ctl.inventory_id = mic.inventory_item_id and
234 nvl(ctl.org_id,-99) = nvl(mic.organization_id,-99) and
235 mic.category_id = adj.from_category_id
236 AND cnt.org_id = x_org_id -- MOAC Changes made by Ashley
237 AND ct.org_id = cnt.org_id -- MOAC Changes made by Ashley
238 AND ctl.org_id = ct.org_id -- MOAC Changes made by Ashley
239 )
240 AND org_id = x_org_id; */ -- MOAC Changes made by Ashley
241 end if;
242
243 end if; -- end if (adj.customer_trx_id = x_last_A_customer_trx_id)
244 end if; -- end if adj.line_selection_mode = 'A'
245
246
247 -- Update cn_not_trx.last_collected_date
248 update cn_not_trx
249 set last_collected_date = SYSDATE
250 where not_trx_id = adj.not_trx_id
251 AND org_id = x_org_id; --MOAC Changes made by Ashley
252
253 END LOOP; -- end FOR adj IN adj_cursor LOOP
254
255 cn_process_audits_pkg.update_row(x_proc_audit_id, NULL, SYSDATE, 0,
256 'identify RAM adjustments: completed. ' || x_trx_identified_count || ' transactions identified.');
257
258 IF ( x_trx_identified_count = 0 ) THEN
259 cn_message_pkg.debug('identify RAM adjustments: no RAM adjustments was found.');
260 fnd_file.put_line(fnd_file.Log, 'identify RAM adjustments: no RAM adjustments was found.');
261 END IF;
262
263 COMMIT; -- commit after identify process
264
265 cn_message_pkg.debug('identify RAM adjustments: identify process completed. ' || x_trx_identified_count || ' transactions identified.');
266 fnd_file.put_line(fnd_file.Log, 'identify RAM adjustments: identify process completed. ' || x_trx_identified_count || ' transactions identified.');
267
268 cn_debug.print_msg('<<identify RAM adjustments', 1);
269 cn_message_pkg.debug('identify RAM adjustments<<');
270 fnd_file.put_line(fnd_file.Log, 'identify RAM adjustments<<');
271
272
273
274 EXCEPTION
275 WHEN OTHERS THEN ROLLBACK;
276
277 cn_message_pkg.debug('identify RAM adjustments: in exception handler');
278 fnd_file.put_line(fnd_file.Log, 'identify RAM adjustments: in exception handler');
279
280 cn_message_pkg.debug(SQLCODE||' '||SQLERRM);
281 fnd_file.put_line(fnd_file.Log, SQLCODE||' '||SQLERRM);
282
283 cn_debug.print_msg('identify RAM adjustments: in exception handler', 1);
284 cn_process_audits_pkg.update_row(X_proc_audit_id, NULL, SYSDATE, SQLCODE,
285 SQLERRM);
286
287 app_exception.raise_exception;
288
289 END identify;
290
291
292
293
294
295 PROCEDURE negate (
296 debug_pipe VARCHAR2 DEFAULT NULL,
297 debug_level NUMBER DEFAULT NULL ,
298 x_org_id NUMBER ) IS
299
300 -- Get affected trx_line_id
301 CURSOR neg_trx_line_cursor IS
302 select ctl.trx_line_id
303 from cn_trx_lines ctl
304 where ctl.adjusted_flag = 'Y' and
305 ctl.negated_flag = 'N' and
306 ctl.collected_flag = 'N' and
307 ctl.event_id = cn_global.inv_event_id
308 AND org_id = x_org_id;
309
310 -- Get affected comm_lines_api_id
311 CURSOR neg_api_cursor(p_trx_line_id number) IS
312 select api.comm_lines_api_id
313 from cn_comm_lines_api api
314 where api.trx_line_id = p_trx_line_id and
315 api.source_doc_type = 'AR'
316 AND org_id = x_org_id;
317
318 x_api_negated_count NUMBER := 0;
319 x_proc_audit_id NUMBER;
320 x_rowid ROWID;
321 l_adjusted_by NUMBER := fnd_global.user_id;
322 l_adjust_comments VARCHAR2(2000) := 'Negated by Revenue Adjustments Collection - Request ID = ' || fnd_global.conc_request_id;
323
324 BEGIN
325 IF (debug_pipe IS NOT NULL) THEN
326 cn_debug.init_pipe(debug_pipe, debug_level);
327 END IF;
328 cn_debug.print_msg('>>negate process', 1);
329 cn_message_pkg.debug('negate process>>');
330 fnd_file.put_line(fnd_file.Log, 'negate process>>');
331
332 x_proc_audit_id := NULL; -- Will get a value in the call below
333 cn_process_audits_pkg.insert_row(x_rowid, x_proc_audit_id, NULL,'RAMADJ', 'negate process', NULL, NULL, NULL, NULL, NULL, SYSDATE, NULL, x_org_id);
334
335 FOR nt IN neg_trx_line_cursor LOOP
336
337 FOR na IN neg_api_cursor(nt.trx_line_id) LOOP
338
339 x_api_negated_count := x_api_negated_count + 1;
340
341 -- Negate records in API or Header table
342 cn_adjustments_pkg.api_negate_record(
343 x_comm_lines_api_id => na.comm_lines_api_id,
344 x_adjusted_by => l_adjusted_by,
345 x_adjust_comments => l_adjust_comments);
346
347 END LOOP;
348
349 -- Set flags in cn_trx_lines
350 update cn_trx_lines
351 set negated_flag = 'Y'
352 where trx_line_id = nt.trx_line_id
353 AND org_id = x_org_id;
354
355 -- Flag status now should be
356 -- adjusted_flag = 'Y'
357 -- negated_flag = 'Y'
358 -- collected_flag = 'N'
359
360 END LOOP;
361
362 cn_process_audits_pkg.update_row(x_proc_audit_id, NULL, SYSDATE, 0,
363 'negate process: completed. ' || x_api_negated_count || ' OIC transactions negated.');
364
365 IF ( x_api_negated_count = 0 ) THEN
366 cn_message_pkg.debug('negate process: No OIC transaction was negated.');
367 fnd_file.put_line(fnd_file.Log, 'negate process: No OIC transaction was negated.');
368 END IF;
369
370 -- COMMIT; commit will be called when the re-collection is done.
371
372 cn_message_pkg.debug('negate process: negate process completed. ' || x_api_negated_count || ' OIC transactions negated.');
373 fnd_file.put_line(fnd_file.Log, 'negate process: negate process completed. ' || x_api_negated_count || ' OIC transactions negated.');
374
375 cn_debug.print_msg('<<negate process', 1);
376 cn_message_pkg.debug('negate process<<');
377 fnd_file.put_line(fnd_file.Log, 'negate process<<');
378
379
380 EXCEPTION
381 WHEN OTHERS THEN ROLLBACK;
382
383 cn_message_pkg.debug('negate process: in exception handler');
384 fnd_file.put_line(fnd_file.Log, 'negate process: in exception handler');
385
386 cn_message_pkg.debug(SQLCODE||' '||SQLERRM);
387 fnd_file.put_line(fnd_file.Log, SQLCODE||' '||SQLERRM);
388
389 cn_debug.print_msg('negate process: in exception handler', 1);
390 cn_process_audits_pkg.update_row(X_proc_audit_id, NULL, SYSDATE, SQLCODE,
391 SQLERRM);
392
393 app_exception.raise_exception;
394
395 END negate;
396
397
398 END cn_ram_adjustments_pkg;
[Home] [Help]