[Home] [Help]
PACKAGE BODY: APPS.OPI_DBI_INV_CCA_OPM_PKG
Source
1 PACKAGE BODY opi_dbi_inv_cca_opm_pkg AS
2 /*$Header: OPIDEICCAPB.pls 120.1 2005/08/02 04:55:28 visgupta noship $ */
3
4 /**************************************************
5 * Global Varaiables
6 **************************************************/
7
8 g_ERROR CONSTANT NUMBER := -1; -- concurrent manager error code
9 g_WARNING CONSTANT NUMBER := 1; -- concurrent manager warning code
10 g_OK CONSTANT NUMBER := 0; -- concurrent manager success code
11 g_CY_ZERO_DATE DATE := to_date(fnd_profile.value('SY$ZERODATE'),'yyyy/mm/dd');
12
13 /**************************************************
14 * User Defined Exceptions
15 **************************************************/
16
17 GLOBAL_START_DATE_NULL EXCEPTION;
18 PRAGMA EXCEPTION_INIT (GLOBAL_START_DATE_NULL, -20900);
19 GLOBAL_START_DATE_NULL_MESG CONSTANT VARCHAR2(200) := 'The global start date seems null. Please set up the global start date correctly.';
20
21 GLOBAL_SETUP_MISSING EXCEPTION;
22 PRAGMA EXCEPTION_INIT (GLOBAL_SETUP_MISSING, -20901);
23 GLOBAL_SETUP_MISSING_MESG CONSTANT VARCHAR2(200) := 'Unable to verify setup of global start date and global currency code.';
24
25 MISSING_DATES EXCEPTION;
26 PRAGMA EXCEPTION_INIT (MISSING_DATES, -20902);
27 MISSING_DATES_MESG CONSTANT VARCHAR2(200) := 'Missing Date.';
28
29 INITIALIZATION_ERROR EXCEPTION;
30 PRAGMA EXCEPTION_INIT (INITIALIZATION_ERROR, -20903);
31
32 SETUP_LOG_INIT_ERROR EXCEPTION;
33 PRAGMA EXCEPTION_INIT (SETUP_LOG_INIT_ERROR, -20904);
34
35 EXTRACT_ADJ_INIT_ERROR EXCEPTION;
36 PRAGMA EXCEPTION_INIT (EXTRACT_ADJ_INIT_ERROR, -20906);
37
38 EXTRACT_MATCHES_INIT_ERROR EXCEPTION;
39 PRAGMA EXCEPTION_INIT (EXTRACT_MATCHES_INIT_ERROR, -20907);
40
41 OPM_EXTRACTION_ERROR EXCEPTION;
42 PRAGMA EXCEPTION_INIT (OPM_EXTRACTION_ERROR, -20910);
43 OPM_EXTRACTION_ERROR_MESG CONSTANT VARCHAR2(200) := 'Error Occured during OPM Extraction.';
44
45
46 /**************************************************
47 * File scope variables
48 **************************************************/
49
50 s_user_id NUMBER := nvl(fnd_global.user_id, -1);
51 s_login_id NUMBER := nvl(fnd_global.login_id, -1);
52
53 s_global_start_date DATE := NULL;
54
55 /**************************************************
56 * Common Procedures (to initial and incremental load)
57 *
58 * File scope functions (not in spec)
59 **************************************************/
60
61 /*FUNCTION check_global_setup
62 RETURN BOOLEAN;
63 */
64 FUNCTION err_mesg (p_mesg IN VARCHAR2,
65 p_proc_name IN VARCHAR2 DEFAULT NULL,
66 p_stmt_id IN NUMBER DEFAULT -1)
67 RETURN VARCHAR2;
68
69 PROCEDURE setup_load;
70
71
72 /**************************************************
73 * Initial Load Procedures
74 *
75 * File scope functions (not in spec)
76 **************************************************/
77
78 --PROCEDURE setup_log_init;
79 PROCEDURE extract_adjustments_init;
80 PROCEDURE extract_exact_matches_init;
81
82 /**************************************************
83 * Incremental Load Procedures
84 *
85 * File scope functions (not in spec)
86 **************************************************/
87
88 -- No incremental load procedures
89
90 /**************************************************
91 * Common Procedures Definitions
92 **************************************************/
93
94 /* setup_load
95
96 Gets the GSD.
97
98 History:
99 Date Author Action
100 07/04/04 Vedhanarayanan G Defined procedure.
101
102 */
103
104 PROCEDURE setup_load
105 IS
106 l_proc_name VARCHAR2 (40);
107 l_stmt_id NUMBER;
108 l_from_date DATE;
109 l_to_date DATE;
110 l_missing_day_flag BOOLEAN;
111 l_min_miss_date DATE;
112 l_max_miss_date DATE;
113
114 BEGIN
115
116 -- Initialization
117 l_proc_name := 'setup_load';
118 l_stmt_id := 0;
119 l_missing_day_flag := FALSE;
120
121 l_stmt_id := 10;
122 -- Check for the primary currency code and global start date setup.
123 -- These two parameters must be set up prior to any DBI load.
124 -- done in Discrete package.
125 /* IF (NOT (check_global_setup ())) THEN
126 RAISE GLOBAL_SETUP_MISSING;
127 END IF;
128 */
129 -- Get the global start date
130 l_stmt_id := 20;
131 s_global_start_date := trunc (bis_common_parameters.get_global_start_date);
132 IF (s_global_start_date IS NULL) THEN
133 RAISE GLOBAL_START_DATE_NULL;
134 END IF;
135
136
137 l_stmt_id :=30;
138 l_from_date := s_global_start_date;
139 l_to_date := sysdate;
140
141 fii_time_api.check_missing_date( P_FROM_DATE => l_from_date,
142 P_TO_DATE => l_to_date,
143 P_HAS_MISSING_DATE => l_missing_day_flag,
144 P_MIN_MISSING_DATE => l_min_miss_date,
145 P_MAX_MISSING_DATE => l_max_miss_date);
146
147 IF (l_missing_day_flag) THEN
148 BIS_COLLECTION_UTILITIES.PUT_LINE ('Missing Dates Range from ' || l_min_miss_date ||
149 'to' || l_max_miss_date);
150 RAISE MISSING_DATES;
151 END IF;
152
153
154 EXCEPTION
155
156 WHEN GLOBAL_SETUP_MISSING THEN
157
158 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (GLOBAL_SETUP_MISSING_MESG,
159 l_proc_name, l_stmt_id));
160 RAISE INITIALIZATION_ERROR;
161
162 WHEN GLOBAL_START_DATE_NULL THEN
163
164 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (GLOBAL_START_DATE_NULL_MESG,
165 l_proc_name, l_stmt_id));
166 RAISE INITIALIZATION_ERROR;
167
168 WHEN MISSING_DATES THEN
169
170 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (MISSING_DATES_MESG,
171 l_proc_name, l_stmt_id));
172 RAISE INITIALIZATION_ERROR;
173
174 WHEN OTHERS THEN
175
176 RAISE INITIALIZATION_ERROR;
177
178 END setup_load;
179
180
181 /* check_global_setup
182
183 Checks to see if basic global parameters are set up.
184 Currently these include the:
185 1. Global start date
186 2. Global currency code
187
188 Parameters: None
189
190 History:
191 Date Author Action
192 07/04/04 Vedhanarayanan G Defined procedure.
193 */
194 /*
195 FUNCTION check_global_setup
196 RETURN BOOLEAN
197 IS
198 l_proc_name VARCHAR2 (40);
199 l_stmt_id NUMBER;
200 l_setup_good BOOLEAN;
201 l_list dbms_sql.varchar2_table;
202
203 BEGIN
204
205 -- Initialization
206 l_proc_name := 'check_global_setup';
207 l_stmt_id := 0;
208 l_setup_good := false;
209
210 -- Parameters we want to check for
211 l_list(1) := 'BIS_PRIMARY_CURRENCY_CODE';
212 l_list(2) := 'BIS_GLOBAL_START_DATE';
213 l_stmt_id := 10;
214 l_setup_good := bis_common_parameters.check_global_parameters(l_list);
215 return l_setup_good;
216
217 EXCEPTION
218
219 WHEN OTHERS THEN
220 rollback;
221 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name,
222 l_stmt_id));
223 l_setup_good := false;
224 return l_setup_good;
225
226 END check_global_setup;
227
228
229 */
230 /* err_mesg
231
232 History:
233 Date Author Action
234 07/04/04 Vedhanarayanan G Defined procedure.
235 */
236
237 FUNCTION err_mesg (p_mesg IN VARCHAR2,
238 p_proc_name IN VARCHAR2 DEFAULT NULL,
239 p_stmt_id IN NUMBER DEFAULT -1)
240 RETURN VARCHAR2
241 IS
242
243 l_proc_name VARCHAR2 (60);
244 l_stmt_id NUMBER;
245
246 -- The variable declaration cannot take C_ERRBUF_SIZE (a defined constant)
247 -- as the size of the declaration. I have to put 300 here.
248 l_formatted_message VARCHAR2 (300) := NULL;
249
250 BEGIN
251
252 l_proc_name := 'err_mesg';
253 l_stmt_id := 0;
254
255 l_stmt_id := 10;
256 l_formatted_message := substr ((C_PKG_NAME || '.' || p_proc_name || ' #' ||
257 to_char (p_stmt_id) || ': ' || p_mesg),
258 1, C_ERRBUF_SIZE);
259
260 commit;
261
262 return l_formatted_message;
263
264 EXCEPTION
265
266 WHEN OTHERS THEN
267 -- the exception happened in the exception reporting function !!
268 -- return with ERROR.
269 l_formatted_message := substr ((C_PKG_NAME || '.' || l_proc_name ||
270 ' #' ||
271 to_char (l_stmt_id) || ': ' ||
272 SQLERRM),
273 1, C_ERRBUF_SIZE);
274
275 l_formatted_message := 'Error in error reporting.';
276 return l_formatted_message;
277
278 END err_mesg;
279
280
281 /**************************************************
282 * Initial Load Procedure Definitions
283 **************************************************/
284
285 /* run_initial_load
286
287 Wrapper routine for the initial load of the cycle count accuracy for OPM ETL.
288
289 Parameters:
290 retcode - 0 on successful completion, -1 on error and 1 for warning.
291 errbuf - empty on successful completion, message on error or warning
292
293 History:
294 Date Author Action
295 04/03/04 Vedhanarayanan G Defined Procedure.
296
297 */
298
299 PROCEDURE run_initial_load_opm(errbuf in out NOCOPY VARCHAR2,
300 retcode in out NOCOPY NUMBER)
301
302 IS
303
304 BEGIN
305
306 setup_load();
307
308 commit;
309
310 extract_adjustments_init();
311
312 commit;
313
314 extract_exact_matches_init();
315
316 commit;
317
318 retcode := g_OK;
319
320
321 EXCEPTION
322
323 WHEN OTHERS THEN
324
325 retcode := g_ERROR;
326 BIS_COLLECTION_UTILITIES.PUT_LINE (OPM_EXTRACTION_ERROR_MESG);
327 RAISE OPM_EXTRACTION_ERROR;
328
329
330 END run_initial_load_opm;
331
332
333 /* extract_adjustments_init
334
335 Extracts adjustments from the test and the permanent subledger. All transactions of type
336 PICY and transaction date >= the GSD are extracted from the test subledger and from the
337 permanent subledger transactions with transaction date >= from transaction date and
338 < transaction date from the log.
339
340 R12 Changes
341 ----------
342 The adjustment collection will be from the permanent subledger only. As before migrating to R12,
343 all the draft transactions will be posted to permanent. Also the upper bounds will
344 be removed. Hence all the cycle count adjustments which are on or after GSD will be collected.
345 This procedure will be called only when R12 migration date will be greater than GSD.
346
347 History:
348 Date Author Action
349 07/04/04 Vedhanarayanan G Defined procedure.
350 07/04/05 Vishal Gupta Refer R12 Changes.
351
352 */
353
354 PROCEDURE extract_adjustments_init
355 IS
356
357 l_proc_name VARCHAR2 (40);
358 l_stmt_id NUMBER;
359 l_sysdate DATE;
360
361
362 BEGIN
363
364 l_stmt_id := 0;
365 l_proc_name := 'extract_adjustments_init';
366
367
368 BIS_COLLECTION_UTILITIES.PUT_LINE('Extracting Adjustments Initial Load from - ' ||
369 TO_CHAR(s_global_start_date,'mm-dd-yyyy hh24:mi:ss'));
370 BIS_COLLECTION_UTILITIES.PUT_LINE('Extracting Adjustemnts Initail Load Start Time - ' ||
371 TO_CHAR(SYSDATE, 'hh24:mi:ss'));
372
373 l_stmt_id :=20;
374
375 INSERT /*+ append parallel(OPI_DBI_INV_CCA_STG) */
376 INTO opi_dbi_inv_cca_stg (
377 organization_id,
378 inventory_item_id,
379 cycle_count_header_id,
380 abc_class_id,
381 subinventory_code,
382 cycle_count_entry_id,
383 source,
384 approval_date,
385 uom_code,
386 system_inventory_qty,
387 positive_adjustment_qty,
388 negative_adjustment_qty,
389 item_unit_cost,
390 item_adj_unit_cost,
391 hit_miss_pos,
392 hit_miss_neg,
393 exact_match)
394 SELECT/*+ full(ich) use_hash(ica,ich,irm,iwi,icd,icmb,iwm,msi,gl_join)
395 parallel(ica) parallel(ich) parallel(irm) parallel(iwi) parallel(icd)
396 parallel(icmb) parallel(iwm) parallel(msi) parallel(gl_join) */
397 iwm.mtl_organization_id,
398 msi.inventory_item_id,
399 -1,
400 nvl(iwi.whse_abccode,to_char(-1)),
401 to_char(-1),
402 ica.cycle_id||'-'||ica.seq_no,
403 C_PRER12_SOURCE,
404 trunc(ich.last_update_date) approval_date,
405 msi.primary_uom_code,
406 ica.frozen_qty1,
407 CASE WHEN ica.var_qty1 > 0 THEN
408 ica.var_qty1
409 ELSE 0
410 END,
411 CASE WHEN ica.var_qty1 < 0 THEN
412 -1*ica.var_qty1
413 ELSE 0
414 END,
415 opi_dbi_inv_cca_opm_pkg.get_unit_cost(ica.item_id,ica.whse_code,iwm.orgn_code,trunc(icd.creation_date)),
416 abs(gl_join.amount_base/ica.var_qty1),
417 irm.percent_warn,
418 irm.percent_warn,
419 decode(ica.var_qty1,0,1,0)
420 FROM
421 ic_cycl_adt ica,
422 ic_cycl_hdr ich,
423 ic_rank_mst irm,
424 ic_whse_inv iwi,
425 ic_cycl_dtl icd,
426 ic_item_mst_b icmb,
427 ic_whse_mst iwm,
428 mtl_system_items_b msi,
429 (SELECT /*+ use_hash(gsl,itc) parallel(gsl) parallel(itc) */
430 gsl.amount_base amount_base,
431 itc.whse_code whse_code,
432 itc. item_id item_id,
433 itc.lot_id lot_id,
434 itc.location location,
435 itc.doc_id doc_id,
436 itc.line_id line_id
437 FROM
438 gl_subr_led gsl,
439 ic_tran_cmp itc
440 WHERE
441 gsl.line_id = itc.line_id and
442 gsl.doc_id = itc.doc_id and
443 gsl.doc_type = 'PICY' and
444 gsl.acct_ttl_type = 1500 and
445 gsl.creation_date >= s_global_start_date and
446 itc.doc_type = gsl.doc_type and
447 itc.gl_posted_ind = 1 and
448 itc.trans_qty <> 0
449 ) gl_join
450 WHERE
451 gl_join.item_id = ica.item_id and
452 gl_join.whse_code = ica.whse_code and
453 gl_join.lot_id = ica.lot_id and
454 gl_join.location = ica.location and
455 gl_join.doc_id = ica.cycle_id and
456 ica.cycle_id = ich.cycle_id and
457 ica.cycle_id = icd.cycle_id and
458 ica.whse_code = icd.whse_code and
459 ica.item_id = icd.item_id and
460 ica.lot_id = icd.lot_id and
461 ica.location = icd.location and
462 ica.count_no = icd.count_no and
463 ica.item_id = iwi.item_id and
464 ica.whse_code = iwi.whse_code and
465 ica.whse_code = iwm.whse_code and
466 ica.item_id = icmb.item_id and
467 irm.whse_code(+) = iwi.whse_code and
468 irm.abc_code(+) = iwi.whse_abccode and
469 ich.last_update_date >= s_global_start_date and
470 ich.delete_mark = 1 and
471 msi.segment1 = icmb.item_no and
472 msi.organization_id = iwm.mtl_organization_id;
473
474
475 BIS_COLLECTION_UTILITIES.PUT_LINE('Extracting Pre R12 Adjustments Initial Load End Time - ' ||
476 TO_CHAR(SYSDATE, 'hh24:mi:ss'));
477
478
479 EXCEPTION
480
481 WHEN OTHERS THEN
482
483 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name,
484 l_stmt_id));
485
486 RAISE EXTRACT_ADJ_INIT_ERROR;
487
488 END extract_adjustments_init;
489
490
491 /* extract_exact_matches_init
492
493 Extracts all exact match entries where the approval date is between the GSD and
494 the sysdate inserted in the log. Item Count date is checked for > '1-Jan-1970'
495 to eliminate all cycle count info for items for which no count qty was entered in
496 the cycle count form and the cycle count posted.
497
498 R12 Changes
499 ----------
500 The upper bounds will be removed.
501
502 History:
503 Date Author Action
504 07/04/04 Vedhanarayanan G Defined procedure.
505 07/04/05 Vishal Gupta Refer R12 Changes above.
506 */
507
508 PROCEDURE extract_exact_matches_init
509 IS
510
511 l_proc_name VARCHAR2 (40);
512 l_stmt_id NUMBER;
513 l_sysdate DATE;
514
515 BEGIN
516
517 l_stmt_id := 0;
518 l_proc_name := 'extract_exact_matches_init';
519
520 l_stmt_id := 10;
521
522 BIS_COLLECTION_UTILITIES.PUT_LINE('Extracting Exact Matches Inital Load from - ' ||
523 TO_CHAR(s_global_start_date,'mm-dd-yyyy hh24:mi:ss'));
524 BIS_COLLECTION_UTILITIES.PUT_LINE('Extracting Exact Matches Initail Load Start Time - ' ||
525 TO_CHAR(SYSDATE, 'hh24:mi:ss'));
526
527 l_stmt_id := 20;
528
529 INSERT /*+ append parallel(OPI_DBI_INV_CCA_STG) */
530 INTO opi_dbi_inv_cca_stg (
531 organization_id,
532 inventory_item_id,
533 cycle_count_header_id,
534 abc_class_id,
535 subinventory_code,
536 cycle_count_entry_id,
537 source,
538 approval_date,
539 uom_code,
540 system_inventory_qty,
541 positive_adjustment_qty,
542 negative_adjustment_qty,
543 item_unit_cost,
544 item_adj_unit_cost,
545 hit_miss_pos,
546 hit_miss_neg,
547 exact_match)
548 SELECT /*+ full(ich) use_hash(ica, ich, irm, iwi, icd, icmb, iwm, msi)
549 parallel(ica) parallel(ich) parallel(irm) parallel(iwi) parallel(icd)
550 parallel(icmb) parallel(iwm) parallel(msi) */
551 iwm.mtl_organization_id,
552 msi.inventory_item_id,
553 -1,
554 nvl(iwi.whse_abccode,to_char(-1)),
555 to_char(-1),
556 ica.cycle_id||'-'||ica.seq_no,
557 C_PRER12_SOURCE,
558 trunc(ich.last_update_date) approval_date,
559 msi.primary_uom_code,
560 ica.frozen_qty1,
561 0,
562 0,
563 opi_dbi_inv_cca_opm_pkg.get_unit_cost(ica.item_id,ica.whse_code,iwm.orgn_code,
564 trunc(icd.creation_date)),
565 0,
566 NULL,
567 NULL,
568 1
569 FROM
570 ic_cycl_adt ica,
571 ic_cycl_hdr ich,
572 ic_rank_mst irm,
573 ic_whse_inv iwi,
574 ic_whse_mst iwm,
575 ic_cycl_dtl icd,
576 ic_item_mst_b icmb,
577 mtl_system_items_b msi
578 WHERE
579 ica.cycle_id = ich.cycle_id and
580 ica.cycle_id = icd.cycle_id and
581 ica.item_id = icd.item_id and
582 ica.whse_code = icd.whse_code and
583 ica.location = icd.location and
584 ica.count_no = icd.count_no and
585 ica.lot_id = icd.lot_id and
586 ica.item_id = iwi.item_id and
587 ica.whse_code = iwi.whse_code and
588 ica.whse_code = iwm.whse_code and
589 ica.item_id = icmb.item_id and
590 ica.var_qty1 = 0 and
591 ica.item_count_dt > g_CY_ZERO_DATE and
592 ich.last_update_date >= s_global_start_date and
593 irm.whse_code(+) = iwi.whse_code and
594 irm.abc_code(+) = iwi.whse_abccode and
595 ich.delete_mark = 1 and
596 msi.segment1 = icmb.item_no and
597 msi.organization_id = iwm.mtl_organization_id;
598
599 BIS_COLLECTION_UTILITIES.PUT_LINE('Extracted Exact Matches Initail Load End Time - ' ||
600 TO_CHAR(SYSDATE, 'hh24:mi:ss'));
601
602
603 EXCEPTION
604
605 WHEN OTHERS THEN
606
607 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name,
608 l_stmt_id));
609
610 RAISE EXTRACT_MATCHES_INIT_ERROR;
611
612 END extract_exact_matches_init;
613
614 /****************************************************
615 * Public Functions *
616 ****************************************************/
617 /* get_unit_cost
618 *
619 * History:
620 * Date Author Action
621 * 03/06/04 Vedhanarayanan G Defined procedure.
622 *
623 */
624 FUNCTION get_unit_cost(p_item_id IN NUMBER,
625 p_whse_code IN VARCHAR2,
626 p_orgn_code IN VARCHAR2,
627 p_creation_date IN DATE)
628 RETURN NUMBER
629 PARALLEL_ENABLE
630 IS
631 BEGIN
632 return
633 gmf_cmcommon.unit_cost(p_item_id,p_whse_code,p_orgn_code,p_creation_date);
634 END get_unit_cost;
635
636 END opi_dbi_inv_cca_opm_pkg;