[Home] [Help]
PACKAGE BODY: APPS.INV_3PL_BILLING_UNITS_PVT
Source
1 PACKAGE BODY INV_3PL_BILLING_UNITS_PVT AS
2 /* $Header: INVVBLUB.pls 120.1.12020000.2 2012/09/25 08:54:44 gjyoti ship $ */
3
4
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'INV_3PL_BILLING_UNITS_PVT';
6 g_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
7
8 v_client_id mtl_billing_rule_lines_v.client_id%type;
9 v_client_code mtl_billing_rule_lines_v.client_code%type;
10 v_client_name mtl_billing_rule_lines_v.client_name%type;
11 v_client_number mtl_billing_rule_lines_v.client_number%type;
12 v_contract_id mtl_billing_rule_lines_v.contract_id%type;
13 v_contract_number mtl_billing_rule_lines_v.contract_number%type;
14 v_counter_item_id mtl_billing_rule_lines_v.counter_item_id%type;
15 v_last_computation_Date mtl_billing_rule_lines_v.last_computation_Date%type;
16 v_net_Reading mtl_billing_rule_lines_v.net_Reading%type;
17 v_last_reading mtl_billing_rule_lines_v.last_reading%type;
18 v_billing_uom mtl_billing_rule_lines_v.billing_uom%type;
19 v_service_item_org_id mtl_billing_rule_lines_v.service_item_org_id%type;
20 v_billing_source_id mtl_billing_rule_lines_v.billing_source_id%type;
21 v_billing_source_name mtl_billing_rule_lines_v.billing_source_name%type;
22 v_service_line_start_date mtl_billing_rule_lines_v.service_line_start_date%type;
23 v_service_line_end_date mtl_billing_rule_lines_v.service_line_end_date%type;
24
25
26 PROCEDURE debug(
27 p_message IN VARCHAR2
28 ) IS
29 BEGIN
30 inv_log_util.trace(p_message, G_PKG_NAME , 10 );
31 EXCEPTION
32 WHEN OTHERS THEN
33 NULL;
34 END debug;
35
36
37
38 PROCEDURE calculate_billing_units
39 (
40 ERRBUF OUT NOCOPY VARCHAR2 ,
41 RETCODE OUT NOCOPY NUMBER ,
42 p_OU_id IN NUMBER,
43 p_client_id IN NUMBER,
44 p_rule_ID IN NUMBER,
45 p_contract_id IN NUMBER,
46 p_item_id IN NUMBER,
47 p_source_to_date IN VARCHAR2
48 )
49
50 IS
51
52 CURSOR cur_invoice_interface_details (p_contract_id NUMBER)
53 IS
54 SELECT invoice_date, interface_date, date_start, date_end
55 FROM
56 (SELECT b.date_transaction invoice_date,
57 b.date_to_interface interface_date,
58 date_start, date_end
59 FROM mtl_agreement_details_v a, oks_level_elements b
60 WHERE a.cle_id = b.cle_id
61 AND a.dnz_chr_id = b.dnz_chr_id
62 AND b.date_completed IS NOT NULL
63 AND a.dnz_chr_id = p_contract_id
64 ORDER BY b.id DESC) invoice_interface_det
65 WHERE ROWNUM <2;
66
67 CURSOR sel_eligible_transactions (p_end_date DATE, p_client_code VARCHAR2)
68 IS
69 SELECT /*+ parallel(MMT) */ MMT.locator_id
70 , MMT.organization_id
71 , WMS_DEPLOY.GET_CLIENT_CODE( inventory_item_id ) CLIENT_CODE
72 , MMT.transaction_action_id
73 , MMT.primary_quantity
74 , MMT.creation_date
75 FROM MTL_MATERIAL_TRANSACTIONS MMT
76 , MTL_3PL_LOCATOR_OCCUPANCY MLC
77 WHERE MMT.organization_id = MLC.organization_id
78 AND WMS_DEPLOY.GET_CLIENT_CODE( MMT.inventory_item_id ) = nvl(p_client_code, MLC.client_code)
79 AND MMT.locator_id = MLC.locator_id
80 AND MMT.creation_date between MLC.last_invoiced_date and p_end_date
81 AND MMT.transaction_action_id not in (5,6,24,30,26,7,11,17,10,9,13,14,56,57)
82 AND EXISTS (SELECT 1 FROM mtl_parameters mp
83 WHERE wms_enabled_flag = 'Y'
84 AND mp.organization_id = mmt.organization_id)
85 ORDER BY MMT.inventory_item_id, MMT.locator_id, MMT.creation_date;
86
87
88 CURSOR sel_new_transactions (p_start_date DATE, p_end_date DATE, p_client_code VARCHAR2)
89 IS
90
91 SELECT /*+ parallel(MMT) */ MMT.locator_id
92 , MMT.organization_id
93 , WMS_DEPLOY.GET_CLIENT_CODE( inventory_item_id ) CLIENT_CODE
94 , MMT.transaction_action_id
95 , MMT.primary_quantity
96 , MMT.creation_date
97 FROM MTL_MATERIAL_TRANSACTIONS MMT
98 WHERE
99 WMS_DEPLOY.GET_CLIENT_CODE( MMT.inventory_item_id ) IS NOT NULL
100 AND NOT EXISTS (SELECT 1
101 FROM MTL_3PL_LOCATOR_OCCUPANCY MLC
102 WHERE MMT.organization_id = MLC.organization_id
103 AND WMS_DEPLOY.GET_CLIENT_CODE( MMT.inventory_item_id ) = MLC.client_code
104 AND MMT.locator_id = MLC.locator_id)
105 AND MMT.creation_date BETWEEN p_start_date AND p_end_date
106 AND MMT.transaction_action_id not in (5,6,24,30,26,7,11,17,10,9,13,14,56,57)
107 AND EXISTS (SELECT 1 FROM mtl_parameters mp
108 WHERE wms_enabled_flag = 'Y'
109 AND mp.organization_id = mmt.organization_id)
110 ORDER BY MMT.inventory_item_id, MMT.locator_id, MMT.creation_date;
111
112 CURSOR sel_ct_new_transactions (p_start_date DATE, p_end_date DATE, p_client_code VARCHAR2)
113 IS
114
115 SELECT /*+ parallel(MMT) */ MMT.locator_id
116 , MMT.organization_id
117 , WMS_DEPLOY.GET_CLIENT_CODE( inventory_item_id ) CLIENT_CODE
118 , MMT.transaction_action_id
119 , MMT.primary_quantity
120 , MMT.creation_date
121 FROM MTL_MATERIAL_TRANSACTIONS MMT
122 WHERE
123 WMS_DEPLOY.GET_CLIENT_CODE( MMT.inventory_item_id ) IS NOT NULL
124 AND WMS_DEPLOY.GET_CLIENT_CODE( MMT.inventory_item_id ) = p_client_code
125 AND NOT EXISTS (SELECT 1
126 FROM MTL_3PL_LOCATOR_OCCUPANCY MLC
127 WHERE MMT.organization_id = MLC.organization_id
128 AND WMS_DEPLOY.GET_CLIENT_CODE( MMT.inventory_item_id ) = p_client_code
129 AND MMT.locator_id = MLC.locator_id)
130 AND MMT.creation_date BETWEEN p_start_date AND p_end_date
131 AND MMT.transaction_action_id not in (5,6,24,30,26,7,11,17,10,9,13,14,56,57)
132 AND EXISTS (SELECT 1 FROM mtl_parameters mp
133 WHERE wms_enabled_flag = 'Y'
134 AND mp.organization_id = mmt.organization_id)
135 ORDER BY MMT.inventory_item_id, MMT.locator_id, MMT.creation_date;
136
137 l_progress NUMBER(2):= 0;
138 l_last_invoice_date DATE;
139 l_last_interface_date DATE;
140 l_custom_reading NUMBER:= 0;
141 x_return_Status VARCHAR2(1);
142 l_ret BOOLEAN;
143 l_meaning VARCHAR2(80);
144 l_plsql_block VARCHAR2(4000);
145 l_source_to_date VARCHAR2(100);
146 l_src_to_date DATE;
147 l_ctr_value_id NUMBER;
148 l_success BOOLEAN := FALSE;
149 l_rec_processed NUMBER(10) := 0;
150 l_rec_failed NUMBER(10) := 0;
151 l_client_code VARCHAR2(10) := NULL;
152 l_upgrade_date DATE := NULL;
153 l_space_seeded_src_used VARCHAR2(1) := 'N';
154 x_msg_count NUMBER;
155 x_msg_data VARCHAR2(4000);
156 l_counter_item_id okc_k_items.object1_id1%TYPE;
157 l_billing_source_rec INV_3PL_BILLING_PUB.source_rec_type;
158
159 ERROR_IN_PROGRAM EXCEPTION;
160 l_start_date DATE;
161 l_profile_creation_date DATE:= NULL;
162 l_plsql_blk_failed VARCHAR2(1):='Y';
163 l_plsql_msg_data VARCHAR2(4000);
164 l_printed_in_outfile VARCHAR2(1):='N';
165 d_sql_p INTEGER := NULL;
166 d_sql_rows_processed INTEGER := NULL;
167 d_sql_stmt VARCHAR2(32700) := NULL;
168 d_space_seeded_src_used NUMBER := 0;
169 lc_client_id mtl_client_parameters.client_id%TYPE;
170 lc_client_code mtl_client_parameters.client_code%TYPE;
171 lc_client_name hz_parties.party_name%TYPE;
172 lc_client_number mtl_client_parameters.client_number%TYPE;
173 lc_contract_id okc_k_headers_all_b.id%TYPE;
174 lc_contract_number okc_k_headers_all_b.contract_number%TYPE;
175 lc_counter_item_id mtl_system_items.inventory_item_id%TYPE;
176 lc_net_Reading csi_counter_readings.net_Reading%TYPE;
177 lc_last_reading csi_counter_readings.net_Reading%TYPE;
178 lc_billing_uom mtl_system_items.primary_uom_code%TYPE;
179 lc_service_item_org_id mtl_billing_rule_lines_v.service_item_org_id%TYPE;
180 lc_billing_source_id mtl_billing_sources_b.billing_source_id%TYPE;
181 lc_billing_source_name mtl_billing_sources_tl.name%TYPE;
182 lc_last_computation_Date DATE;
183 lc_service_line_start_date DATE;
184 lc_service_line_end_date DATE;
185 l_transaction_id NUMBER; /* Added for bug 9657044 */
186
187 BEGIN
188 l_source_to_date := fnd_date.date_to_canonical(nvl(to_date(p_source_to_date, 'YYYY/MM/DD HH24:MI:SS'), sysdate));
189 l_src_to_date:= to_Date(l_source_to_date, 'YYYY/MM/DD HH24:MI:SS');
190
191 IF l_src_to_date > SYSDATE THEN
192 IF g_debug = 1 THEN
193 debug('Source to date can not be a future date ');
194 END IF;
195 RAISE ERROR_IN_PROGRAM;
196 END IF;
197
198 BEGIN
199 d_sql_p := DBMS_SQL.open_cursor;
200 d_sql_stmt :=
201 'SELECT count(*) cnt '
202 || 'FROM mtl_billing_rule_lines rule_lines, '
203 || 'mtl_billing_rule_headers_b rule_headers, '
204 || 'okc_k_headers_all_b contract_headers, '
205 || 'mtl_client_parameters mcp '
206 || 'WHERE rule_headers.billing_rule_header_id = rule_lines.billing_rule_header_id '
207 || 'AND contract_headers.authoring_org_id = :OU_Id '
208 || 'AND rule_headers.service_agreement_id = contract_headers.id '
209 || 'AND mcp.client_code = rule_lines.client_code ';
210
211 d_sql_stmt := d_sql_stmt || 'AND EXISTS ('
212 || 'SELECT 1 '
213 || 'FROM mfg_lookups lookup, mtl_billing_sources_b blsrc '
214 || 'WHERE blsrc.billing_source_code = ''S'' '
215 || 'AND rule_lines.billing_source_id = blsrc.billing_source_id '
216 || 'AND lookup.lookup_type = ''MTL_3PL_SEEDED_SOURCE'' '
217 || 'AND lookup.lookup_code IN (7, 8) '
218 || 'AND blsrc.procedure_code = lookup.lookup_code)';
219 IF (p_client_id is NOT NULL) THEN
220 d_sql_stmt := d_sql_stmt || ' AND mcp.client_id = :client_id';
221 END IF;
222 IF (p_rule_id is NOT NULL) THEN
223 d_sql_stmt := d_sql_stmt || ' AND rule_headers.billing_rule_header_id = :rule_id';
224 END IF;
225 IF (p_contract_id is NOT NULL) THEN
226 d_sql_stmt := d_sql_stmt || ' AND rule_headers.service_agreement_id = :contract_id';
227 END IF;
228 IF (p_item_id is NOT NULL) THEN
229 d_sql_stmt := d_sql_stmt || ' AND rule_lines.inventory_item_id = :item_id';
230 END IF;
231 IF g_debug = 1 THEN
232 debug('Prepared the statment ');
233 END IF;
234 DBMS_SQL.parse(d_sql_p, d_sql_stmt, DBMS_SQL.native);
235 DBMS_SQL.define_column(d_sql_p, 1, d_space_seeded_src_used);
236 IF g_debug = 1 THEN
237 debug('Binding the variables ');
238 END IF;
239
240 DBMS_SQL.bind_variable(d_sql_p, 'OU_Id', p_OU_Id);
241 IF (p_client_id is NOT NULL) THEN
242 DBMS_SQL.bind_variable(d_sql_p, 'client_id', p_client_id);
243 END IF;
244 IF (p_rule_id is NOT NULL) THEN
245 DBMS_SQL.bind_variable(d_sql_p, 'rule_id', p_rule_id);
246 END IF;
247 IF (p_contract_id is NOT NULL) THEN
248 DBMS_SQL.bind_variable(d_sql_p, 'contract_id', p_contract_id);
249 END IF;
250 IF (p_item_id is NOT NULL) THEN
251 DBMS_SQL.bind_variable(d_sql_p, 'item_id', p_item_id);
252 END IF;
253
254 d_sql_rows_processed := DBMS_SQL.EXECUTE(d_sql_p);
255
256 LOOP
257 BEGIN
258 IF (DBMS_SQL.fetch_rows(d_sql_p) > 0) THEN
259 DBMS_SQL.column_value(d_sql_p, 1, d_space_seeded_src_used);
260 IF g_debug = 1 THEN
261 debug('After fetch , d_space_seeded_src_used-> '||d_space_seeded_src_used);
262 END IF;
263 EXIT;
264 ELSE
265 d_space_seeded_src_used := 0;
266 IF g_debug = 1 THEN
267 debug('Seeded Space source not used -> '||d_space_seeded_src_used);
268 END IF;
269 DBMS_SQL.close_cursor(d_sql_p);
270 EXIT;
271 END IF;
272 EXCEPTION
273 WHEN OTHERS THEN
274 IF g_debug = 1 THEN
275 debug('Exception while finding Seeded Space source used -> '||sqlerrm);
276 END IF;
277 EXIT;
278 END;
279 END LOOP;
280 IF DBMS_SQL.is_open(d_sql_p) THEN
281 DBMS_SQL.close_cursor(d_sql_p);
282 END IF;
283 EXCEPTION
284 WHEN OTHERS THEN
285 d_space_seeded_src_used := 0;
286 IF g_debug = 1 THEN
287 debug('Exception in dynamic sql for seeded space source -> '||sqlerrm);
288 END IF;
289 IF DBMS_SQL.is_open(d_sql_p) THEN
290 DBMS_SQL.close_cursor(d_sql_p);
291 END IF;
292 END;
293
294 IF (d_space_seeded_src_used > 0) THEN
295 IF g_debug = 1 THEN
296 debug('p_client_id not null ? -> '||p_client_id);
297 END IF;
298 IF p_client_id IS NOT NULL
299 THEN
300 BEGIN
301 debug('Get l_client_code');
302 SELECT client_code
303 INTO l_client_code
304 FROM mtl_client_parameters
305 WHERE client_id = p_client_id;
306
307 IF g_debug = 1 THEN
308 debug('l_client_code -> '||l_client_code);
309 END IF;
310 EXCEPTION
311 WHEN OTHERS THEN
312 debug(' l_client_code exception -> '||sqlerrm);
313 l_client_code := NULL;
314 END;
315 END IF;
316
317 BEGIN
318 SELECT upgrade_date
319 INTO l_upgrade_date
320 FROM mtl_3pl_locator_occupancy
321 WHERE upgrade_date IS NOT NULL
322 AND ROWNUM <2;
323
324 EXCEPTION
325 WHEN NO_DATA_FOUND THEN
326 BEGIN
327 SELECT creation_date
328 INTO l_profile_creation_date
329 FROM fnd_profile_options
330 WHERE profile_option_name = 'WMS_DEPLOYMENT_MODE'
331 AND application_id = 385;
332 EXCEPTION
333 WHEN OTHERS THEN
334 l_profile_creation_date:= NULL;
335 END;
336 WHEN OTHERS THEN
337 l_upgrade_date:= NULL;
338 END;
339
340 IF g_debug = 1 THEN
341 debug('Get eligible Transactions ');
342 debug('Going to insert data in locator table ');
343 END IF;
344
345 BEGIN
346 FOR sel_eligible_rec IN sel_eligible_transactions(l_src_to_date, l_client_code)
347 LOOP
348 IF g_debug = 1 THEN
349 debug('In Select sel_eligible_rec for date, code -> '||l_src_to_date||', '||l_client_code);
350 END IF;
351 inv_3pl_loc_pvt.update_locator_capacity(
352 x_return_status => x_return_status
353 , x_msg_count => x_msg_count
354 , x_msg_data => x_msg_data
355 , p_inventory_location_id => sel_eligible_rec.locator_id
356 , p_organization_id => sel_eligible_rec.organization_id
357 , p_client_code => sel_eligible_rec.client_code
358 , p_transaction_action_id => sel_eligible_rec.transaction_action_id
359 , p_quantity => sel_eligible_rec.primary_quantity
360 , p_transaction_date => sel_eligible_rec.creation_date
361 );
362 IF x_return_status <> fnd_api.g_ret_sts_success THEN
363 x_msg_data := fnd_message.get;
364 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Error => '||x_msg_data);
365 IF g_debug = 1 THEN
366 debug('Error from update_locator_capacity - '||x_msg_data);
367 END IF;
368 l_success := FALSE;
369 RAISE ERROR_IN_PROGRAM;
370 END IF;
371
372 END LOOP;
373
374 EXCEPTION
375 WHEN OTHERS THEN
376 IF g_debug = 1 THEN
377 debug(l_progress ||' : Got error while selecting eligible transactions');
378 debug(l_progress ||' : Error: '||SQLERRM);
379 END IF;
380 l_success := FALSE;
381 raise ERROR_IN_PROGRAM;
382 END;
383
384
385 IF g_debug = 1 THEN
386 debug('Get New Transactions ');
387 END IF;
388
389 IF l_upgrade_date IS NOT NULL THEN
390 l_start_date := l_upgrade_date;
391 ELSE
392 l_start_date := l_profile_creation_date ;
393 END IF;
394
395 IF l_client_code IS NULL THEN
396 BEGIN
397 FOR sel_new_rec IN sel_new_transactions(l_start_date, l_src_to_date, l_client_code)
398 LOOP
399 IF g_debug = 1 THEN
400 debug('In NEW sel_new_transactions for l_start_date, l_src_to_date, l_client_code -> '||l_start_date||', '||l_src_to_date||', '||l_client_code);
401 END IF;
402
403 inv_3pl_loc_pvt.update_locator_capacity(
404 x_return_status => x_return_status
405 , x_msg_count => x_msg_count
406 , x_msg_data => x_msg_data
407 , p_inventory_location_id => sel_new_rec.locator_id
408 , p_organization_id => sel_new_rec.organization_id
409 , p_client_code => sel_new_rec.client_code
410 , p_transaction_action_id => sel_new_rec.transaction_action_id
411 , p_quantity => sel_new_rec.primary_quantity
412 , p_transaction_date => sel_new_rec.creation_date
413 );
414 IF x_return_status <> fnd_api.g_ret_sts_success THEN
415 x_msg_data := fnd_message.get;
416 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Error => '||x_msg_data);
417
418 IF g_debug = 1 THEN
419 debug('Error from update_locator_capacity - '||x_msg_data);
420 END IF;
421 l_success := FALSE;
422 RAISE ERROR_IN_PROGRAM;
423 END IF;
424
425 END LOOP;
426
427 EXCEPTION
428 WHEN OTHERS THEN
429 IF g_debug = 1 THEN
430 debug(l_progress ||' : Got error while selecting new transactions');
431 debug(l_progress ||' : Error: '||SQLERRM);
432 END IF;
433 l_success := FALSE;
434 RAISE ERROR_IN_PROGRAM;
435 END;
436 ELSIF l_client_code IS NOT NULL THEN
437 BEGIN
438 FOR sel_new_rec IN sel_ct_new_transactions(l_start_date, l_src_to_date, l_client_code)
439 LOOP
440 IF g_debug = 1 THEN
441 debug('In sel_ct_new_transactions for l_start_date, l_src_to_date, l_client_code -> '||l_start_date||', '||l_src_to_date||', '||l_client_code);
442 END IF;
443
444 inv_3pl_loc_pvt.update_locator_capacity(
445 x_return_status => x_return_status
446 , x_msg_count => x_msg_count
447 , x_msg_data => x_msg_data
448 , p_inventory_location_id => sel_new_rec.locator_id
449 , p_organization_id => sel_new_rec.organization_id
450 , p_client_code => sel_new_rec.client_code
451 , p_transaction_action_id => sel_new_rec.transaction_action_id
452 , p_quantity => sel_new_rec.primary_quantity
453 , p_transaction_date => sel_new_rec.creation_date
454 );
455 IF x_return_status <> fnd_api.g_ret_sts_success THEN
456 x_msg_data := fnd_message.get;
457 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Error => '||x_msg_data);
458
459 IF g_debug = 1 THEN
460 debug('Error from update_locator_capacity - '||x_msg_data);
461 END IF;
462 l_success := FALSE;
463 RAISE ERROR_IN_PROGRAM;
464 END IF;
465
466 END LOOP;
467
468 EXCEPTION
469 WHEN OTHERS THEN
470 IF g_debug = 1 THEN
471 debug(l_progress ||' : Got error while selecting new transactions for Ct.');
472 debug(l_progress ||' : Error: '||SQLERRM);
473 END IF;
474 l_success := FALSE;
475 RAISE ERROR_IN_PROGRAM;
476 END;
477 END IF; /* if l_client_code is null */
478 END IF; /* d_space_seeded_src_used > 0 */
479
480
481 IF g_debug = 1 THEN
482 debug('Get eligible Contracts ');
483 END IF;
484 IF p_OU_id IS NOT NULL THEN
485 IF g_debug = 1 THEN
486 debug('Set correct OU context');
487 END IF;
488 OKC_CONTEXT.set_okc_org_context( p_OU_id, NULL);
489 END IF;
490
491 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ');
492 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '--------------------------------------------------------------------------------------------------------------------');
493 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' Output Summary ');
494 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '--------------------------------------------------------------------------------------------------------------------');
495
496
497 l_success := TRUE;
498 d_sql_stmt := NULL;
499
500 debug ('p_rule_id -> '|| p_rule_id);
501 debug ('p_contract_id -> '|| p_contract_id);
502 debug ('p_client_id -> '|| p_client_id);
503 debug ('p_item_id -> '|| p_item_id);
504 debug ('p_OU_id -> '|| p_OU_id);
505
506 d_sql_p := DBMS_SQL.open_cursor;
507 d_sql_stmt :=
508 'SELECT client_id, client_code, client_name, client_number, '
509 || 'contract_id, contract_number, counter_item_id, '
510 || 'last_computation_Date, net_Reading, last_reading, '
511 || 'billing_uom, service_item_org_id, billing_source_id, '
512 || 'billing_source_name, service_line_start_date, '
513 || 'service_line_end_date '
514 || 'FROM mtl_billing_rule_lines_v rules '
515 || 'WHERE authoring_org_id IN ( SELECT organization_id '
516 || 'FROM hr_operating_units hr '
517 || 'WHERE '
518 || 'mo_global.check_access(hr.organization_id)=''Y'') '
519 || 'AND EXISTS (SELECT 1 '
520 || 'FROM mtl_service_contracts_v active_contracts '
521 || 'WHERE active_contracts.id = rules.contract_id) '
522 || 'AND :OU_id IN ( SELECT organization_id '
523 || 'FROM hr_operating_units hr '
524 || 'WHERE '
525 || 'mo_global.check_access(hr.organization_id) = ''Y'') ';
526
527 IF (p_rule_id is NOT NULL) THEN
528 d_sql_stmt := d_sql_stmt || 'AND rules.billing_rule_header_id = :rule_id ';
529 END IF;
530 IF (p_contract_id is NOT NULL) THEN
531 d_sql_stmt := d_sql_stmt || 'AND rules.contract_id = :contract_id ';
532 END IF;
533 IF (p_client_id is NOT NULL) THEN
534 d_sql_stmt := d_sql_stmt || 'AND client_id = :client_id ';
535 END IF;
536 IF (p_item_id is NOT NULL) THEN
537 d_sql_stmt := d_sql_stmt || 'AND inventory_item_id =:item_id ';
538 END IF;
539
540 IF g_debug = 1 THEN
541 debug('Prepared statements for Contracts ');
542 END IF;
543
544 DBMS_SQL.parse(d_sql_p, d_sql_stmt, DBMS_SQL.native);
545 DBMS_SQL.define_column(d_sql_p, 1, lc_client_id);
546 DBMS_SQL.define_column(d_sql_p, 2, lc_client_code, 10);
547 DBMS_SQL.define_column(d_sql_p, 3, lc_client_name, 360);
548 DBMS_SQL.define_column(d_sql_p, 4, lc_client_number, 30);
549 DBMS_SQL.define_column(d_sql_p, 5, lc_contract_id);
550 DBMS_SQL.define_column(d_sql_p, 6, lc_contract_number, 120);
551 DBMS_SQL.define_column(d_sql_p, 7, lc_counter_item_id);
552 DBMS_SQL.define_column(d_sql_p, 8, lc_last_computation_Date);
553 DBMS_SQL.define_column(d_sql_p, 9, lc_net_Reading);
554 DBMS_SQL.define_column(d_sql_p, 10, lc_last_reading);
555 DBMS_SQL.define_column(d_sql_p, 11, lc_billing_uom, 3);
556 DBMS_SQL.define_column(d_sql_p, 12, lc_service_item_org_id, 200);
557 DBMS_SQL.define_column(d_sql_p, 13, lc_billing_source_id);
558 DBMS_SQL.define_column(d_sql_p, 14, lc_billing_source_name, 80);
559 DBMS_SQL.define_column(d_sql_p, 15, lc_service_line_start_date);
560 DBMS_SQL.define_column(d_sql_p, 16, lc_service_line_end_date);
561
562 IF g_debug = 1 THEN
563 debug('Bind the variables');
564 END IF;
565
566 DBMS_SQL.bind_variable(d_sql_p, 'OU_Id', p_OU_Id);
567 IF (p_client_id is NOT NULL) THEN
568 DBMS_SQL.bind_variable(d_sql_p, 'client_id', p_client_id);
569 END IF;
570 IF (p_rule_id is NOT NULL) THEN
571 DBMS_SQL.bind_variable(d_sql_p, 'rule_id', p_rule_id);
572 END IF;
573 IF (p_contract_id is NOT NULL) THEN
574 DBMS_SQL.bind_variable(d_sql_p, 'contract_id', p_contract_id);
575 END IF;
576 IF (p_item_id is NOT NULL) THEN
577 DBMS_SQL.bind_variable(d_sql_p, 'item_id', p_item_id);
578 END IF;
579 d_sql_rows_processed := DBMS_SQL.EXECUTE(d_sql_p);
580
581 IF g_debug = 1 THEN
582 debug('Fetched ref cursor');
583 END IF;
584
585 LOOP
586 IF dbms_sql.fetch_rows(d_sql_p) = 0 THEN
587 EXIT;
588 END IF;
589
590 dbms_sql.column_value(d_sql_p,1,v_client_id);
591 dbms_sql.column_value(d_sql_p,2,v_client_code);
592 dbms_sql.column_value(d_sql_p,3,v_client_name);
593 dbms_sql.column_value(d_sql_p,4,v_client_number);
594 dbms_sql.column_value(d_sql_p,5,v_contract_id);
595 dbms_sql.column_value(d_sql_p,6,v_contract_number);
596 dbms_sql.column_value(d_sql_p,7,v_counter_item_id);
597 dbms_sql.column_value(d_sql_p,8,v_last_computation_Date);
598 dbms_sql.column_value(d_sql_p,9,v_net_Reading);
599 dbms_sql.column_value(d_sql_p,10,v_last_reading);
600 dbms_sql.column_value(d_sql_p,11,v_billing_uom);
601 dbms_sql.column_value(d_sql_p,12,v_service_item_org_id);
602 dbms_sql.column_value(d_sql_p,13,v_billing_source_id);
603 dbms_sql.column_value(d_sql_p,14,v_billing_source_name);
604 dbms_sql.column_value(d_sql_p,15,v_service_line_start_date);
605 dbms_sql.column_value(d_sql_p,16,v_service_line_end_date);
606
607 BEGIN
608 SAVEPOINT process_client;
609 fnd_message.clear;
610
611 /* Derive Client code, last invoice date, last interface date, last computation date, last updated counter value, last billed counter value, source to date, Billing UOM */
612 l_progress := 10;
613 -- OKC_CONTEXT.set_okc_org_context( v_service_item_org_id, NULL);
614 /* Added following for bug 14617279 */
615 OKC_CONTEXT.set_okc_org_context( p_OU_Id, v_service_item_org_id);
616
617 IF g_debug = 1 THEN
618 debug('***************************************************************************');
619 debug(' Processing for Client code => '|| v_client_code );
620 debug('***************************************************************************');
621 debug(l_progress ||' : Client id : '|| v_client_id);
622 debug(l_progress ||' : Client number : '|| v_client_number);
623 debug(l_progress ||' : Client name : '|| v_client_name);
624 debug(l_progress ||' : Contract id : '|| v_contract_id);
625 debug(l_progress ||' : Contract number : '|| v_contract_number);
626 END IF;
627 l_printed_in_outfile := 'N';
628 BEGIN
629 l_last_invoice_date := NULL;
630 l_last_interface_date := NULL;
631 FOR invoice_rec IN cur_invoice_interface_details(v_contract_id)
632 LOOP
633 l_last_invoice_date := invoice_rec.invoice_date;
634 l_last_interface_date := invoice_rec.interface_date;
635 END LOOP;
636 EXCEPTION
637 WHEN OTHERS THEN
638 IF g_debug = 1 THEN
639 debug(l_progress ||' : Got error while fetching last invoice date, interface date');
640 debug(l_progress ||' : Error: '||SQLERRM);
641 END IF;
642 l_success := FALSE;
643 l_rec_failed := l_rec_failed +1;
644 RAISE ERROR_IN_PROGRAM;
645 END;
646
647 l_progress := 20;
648
649 IF g_debug = 1 THEN
650 debug(l_progress ||' : Fetched last invoice date, interface date ');
651 debug(l_progress ||' : l_last_invoice_date : '|| l_last_invoice_date);
652 debug(l_progress ||' : l_last_interface_date : '|| l_last_interface_date);
653 debug(l_progress ||' : v_last_computation_Date : '|| v_last_computation_Date);
654 debug(l_progress ||' : v_billing_source_id : '||v_billing_source_id);
655 END IF;
656
657 IF ( ( p_source_to_date IS NOT NULL)
658 AND (( SYSDATE > v_service_line_end_date) AND ( to_date(p_source_to_date, 'YYYY/MM/DD HH24:MI:SS') > v_service_line_end_date)) ) THEN
659 -- when run for date > service line end date
660 l_src_to_date := v_service_line_end_date;
661 END IF;
662
663 debug( 'l_src_to_date to be updated in PUB pl/sql-> '|| l_src_to_date);
664
665 l_counter_item_id := v_counter_item_id;
666 l_meaning := NULL;
667
668 IF v_billing_source_id IS NOT NULL THEN
669 BEGIN
670 SELECT meaning
671 INTO l_meaning
672 FROM mtl_billing_sources_b blsrc, mfg_lookups lookup
673 WHERE billing_source_id = v_billing_source_id
674 AND
675 ( ( decode(blsrc.billing_source_code, 'C', lookup.lookup_type, NULL) = 'MTL_3PL_CUSTOM_SOURCE')
676 OR ( decode(blsrc.billing_source_code, 'S', lookup.lookup_type, NULL) = 'MTL_3PL_SEEDED_SOURCE')
677 )
678 AND blsrc.procedure_code = lookup.lookup_code
679 AND lookup.lookup_type IN ('MTL_3PL_CUSTOM_SOURCE', 'MTL_3PL_SEEDED_SOURCE');
680
681 EXCEPTION
682 WHEN OTHERS THEN
683 l_success := FALSE;
684 l_rec_failed := l_rec_failed +1;
685 IF g_debug = 1 THEN
686 debug('Could not get custom/seed procedure name due to error -> '||sqlerrm);
687 END IF;
688 ROLLBACK TO process_client;
689 GOTO next_contract_line;
690 END;
691 ELSE /* v_billing_source_id is not NULL */
692 -- no source attached to this line. No calculation.
693 l_success := TRUE;
694 l_printed_in_outfile := 'Y';
695 ROLLBACK TO process_client;
696 GOTO next_contract_line;
697 END IF;
698
699 IF g_debug = 1 THEN
700 debug(l_progress ||' : Procedure name : '|| l_meaning );
701 debug(l_progress ||' : l_counter_item_id : '||l_counter_item_id);
702 debug(l_progress ||' : last_computation_Date : '||v_last_computation_date);
703 debug(l_progress ||' : last_counter_reading : '||v_last_reading);
704 debug(l_progress ||' : l_counter_net_reading : '||v_net_Reading);
705 debug(l_progress ||' : l_service_line_start_date : '||v_service_line_start_date);
706 END IF;
707
708
709 l_progress := 30;
710 IF g_debug = 1 THEN
711 debug(l_progress ||' : Populate Global structure ');
712 END IF;
713
714 l_billing_source_rec.client_code := v_client_code;
715 l_billing_source_rec.client_id := v_client_id;
716 l_billing_source_rec.client_number := v_client_number;
717 l_billing_source_rec.client_name := v_client_name;
718 /* Added to_number for bug 9657044 */
719 l_billing_source_rec.operating_unit := to_number(v_service_item_org_id);
720 l_billing_source_rec.last_invoice_date := l_last_invoice_date;
721 l_billing_source_rec.last_interface_date := l_last_interface_date;
722 l_billing_source_rec.billing_uom := v_billing_uom;
723 l_billing_source_rec.last_reading := v_net_Reading;
724 l_billing_source_rec.last_computation_Date := v_last_computation_date;
725 l_billing_source_rec.service_line_start_date := v_service_line_start_date;
726 l_billing_source_rec.source_to_date := l_src_to_date;
727
728 /* populating global structure */
729 IF INV_3PL_BILLING_PUB.set_billing_source_rec(l_billing_source_rec) THEN
730 l_progress := 40;
731 IF g_debug = 1 THEN
732 debug(l_progress ||' : Came back to Calculate Billing Units - Global structure returned true');
733 END IF;
734 END IF;
735 l_progress := 50;
736
737 IF g_debug = 1 THEN
738 debug(l_progress ||' : Get counter value to be updated for billing from custom procedure ');
739 -- GET THE READING (i.e. Number of units from custom or Seeded source)
740 debug('The PL/SQL to be executed '||l_meaning);
741 END IF;
742
743 BEGIN
744 BEGIN
745 debug('In internal BEGIN -> '||l_meaning);
746 l_custom_reading := 0;
747 l_plsql_block := 'BEGIN '||l_meaning||'(:a, :b ); END;';
748 l_plsql_blk_failed := 'N';
749 EXECUTE IMMEDIATE l_plsql_block USING IN OUT l_custom_reading, IN OUT x_return_Status; --, IN OUT x_msg_count, IN OUT x_msg_data;
750 EXCEPTION
751 WHEN OTHERS THEN
752 l_plsql_msg_data := sqlcode;
753 IF SQLCODE = -6550 THEN
754 l_plsql_msg_data := 'PL/SQL procedure -> ' || l_meaning ||' has some errors. Re-compile the procedure';
755 END IF;
756 l_success := FALSE;
757 x_return_Status:= fnd_api.g_ret_sts_error;
758 l_plsql_blk_failed := 'Y';
759 END;
760
761 IF g_debug = 1 THEN
762 debug('x_return_Status -> ' ||x_return_Status);
763 END IF;
764
765 IF x_return_Status = fnd_api.g_ret_sts_success THEN
766 IF g_debug = 1 THEN
767 debug(l_progress ||' : l_custom_reading from custom procedure => '||l_custom_reading );
768 END IF;
769 ELSIF x_return_Status IN (fnd_api.g_ret_sts_error, fnd_api.g_ret_sts_unexp_error) THEN
770 l_success := FALSE;
771 l_rec_failed := l_rec_failed +1;
772 -- get the messages returned from seeded/custom source
773 l_plsql_blk_failed := 'Y';
774 IF x_return_status <> fnd_api.g_ret_sts_success THEN
775 x_msg_data := fnd_message.get;
776 if x_msg_data IS NOT NULL THEN
777 l_plsql_msg_data := x_msg_data;
778 end if;
779 IF g_debug = 1 THEN
780 debug('Error - '||l_plsql_msg_data);
781 END IF;
782 END IF;
783
784 ROLLBACK TO process_client;
785 GOTO next_contract_line;
786 END IF;
787 END;
788 l_progress := 60;
789
790 IF g_debug = 1 THEN
791 debug(l_progress ||' : l_custom_reading => '||l_custom_reading );
792 debug(l_progress ||' : Now call IB api to update counter ');
793 END IF;
794 BEGIN
795 IF g_debug = 1 THEN
796 debug(l_progress ||' : Calculate Cumulative Counter reading ');
797 END IF;
798
799 l_custom_reading := nvl(l_custom_reading,0) + nvl(v_last_reading, 0);
800 IF g_debug = 1 THEN
801 debug(l_progress ||' : Cumulative Counter reading => '|| l_custom_reading);
802 debug(l_progress ||' : Counter reading > 0, updating ....... ');
803 END IF;
804
805 /* Added for bug 9657044 */
806 SELECT csi_transactions_s.NEXTVAL
807 INTO l_transaction_id
808 FROM dual;
809
810 inv_3pl_billing_counter_pvt.inv_insert_readings_using_api(
811 p_counter_id => l_counter_item_id,
812 p_count_date => l_src_to_date , p_new_reading=> l_custom_reading ,
813 p_net_reading => v_net_Reading,
814 p_transaction_id=> l_transaction_id);
815 /* Added l_transaction_id for bug 9657044 */
816
817 -- No need to print in outfile for successful record
818 l_printed_in_outfile := 'Y';
819
820 EXCEPTION
821 WHEN OTHERS THEN
822 l_success := FALSE;
823 l_rec_failed := l_rec_failed +1;
824 ROLLBACK TO process_client;
825 GOTO next_contract_line;
826 END;
827 l_progress := 70;
828
829 <<next_contract_line>>
830 l_progress := 80;
831
832 if l_printed_in_outfile = 'N' THEN
833 IF NOT (l_success) THEN
834 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '--------------------------------------------------------------------------------------------------------------------');
835 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Could not update counter reading for the following combination ');
836 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '--------------------------------------------------------------------------------------------------------------------');
837 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' Client id : '|| v_client_id);
838 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' Client number : '|| v_client_number);
839 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' Client name : '|| v_client_name);
840 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' Contract id : '|| v_contract_id);
841 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' Contract number : '|| v_contract_number);
842 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' Billing source name : '|| v_billing_source_name);
843
844 IF (l_plsql_blk_failed = 'Y') AND
845 (x_return_status <> fnd_api.g_ret_sts_success) THEN
846 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' Error from custom/seeded source => '||l_plsql_msg_data);
847 END IF;
848 l_printed_in_outfile := 'Y';
849
850 IF g_debug = 1 THEN
851 debug(' ----------------------------------------------------------------------');
852 debug('Could not process current record, fetching next if any ');
853 END IF;
854 ELSE
855 IF g_debug = 1 THEN
856 debug('Processed record, fetching next');
857 END IF;
858 END IF;
859 ELSE
860 IF g_debug = 1 THEN
861 debug('Processed record, fetching next');
862 END IF;
863 END IF;
864 END;
865 l_progress := 90;
866 IF g_debug = 1 THEN
867 debug('Old number of l_rec_processed => '||l_rec_processed);
868 END IF;
869 l_rec_processed := l_rec_processed + 1;
870 IF g_debug = 1 THEN
871 debug('Records processed => '||l_rec_processed);
872 END IF;
873 COMMIT;
874 IF g_debug = 1 THEN
875 debug('Committed the current record');
876 END IF;
877 END LOOP; /* Main Contract Cursor */
878
879 IF DBMS_SQL.is_open(d_sql_p) THEN
880 DBMS_SQL.close_cursor(d_sql_p);
881 END IF;
882 RETCODE := 1;
883 IF NOT (l_success) THEN
884
885 l_ret := FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING',ERRBUF);
886 ELSE
887
888 l_ret := FND_CONCURRENT.SET_COMPLETION_STATUS('NORMAL',ERRBUF);
889 END IF;
890
891 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ');
892 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '--------------------------------------------------------------------------------------------------------------------');
893 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' SUMMARY OF PROCESSING ');
894 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '--------------------------------------------------------------------------------------------------------------------');
895 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' Total Records processed -> '|| l_rec_processed);
896 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' Number of records failed -> '|| l_rec_failed);
897
898 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' Number of records processed successfully -> '|| to_char(l_rec_processed - l_rec_failed));
899
900 debug(l_progress ||' : Completed Execution ');
901 RETURN;
902
903 EXCEPTION
904 WHEN ERROR_IN_PROGRAM THEN
905 IF DBMS_SQL.is_open(d_sql_p) THEN
906 DBMS_SQL.close_cursor(d_sql_p);
907 END IF;
908 RETCODE := 2;
909 IF g_debug = 1 THEN
910 debug('Error occurred - '||SQLERRM);
911 END IF;
912 l_ret := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',ERRBUF);
913 ROLLBACK;
914 RETURN;
915 WHEN OTHERS THEN
916 IF DBMS_SQL.is_open(d_sql_p) THEN
917 DBMS_SQL.close_cursor(d_sql_p);
918 END IF;
919 RETCODE := 2;
920 IF g_debug = 1 THEN
921 debug(' Unexpected error occurred => '||SQLERRM);
922 END IF;
923 l_ret := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',ERRBUF);
924 ROLLBACK;
925 RETURN;
926
927 END CALCULATE_BILLING_UNITS;
928
929 END INV_3PL_BILLING_UNITS_PVT;
930