PACKAGE BODY: APPS.OPI_EDW_OPI_OPRN_M_SZ

Source

1 PACKAGE BODY opi_edw_opi_oprn_m_sz as
2 /* $Header: OPIOONZB.pls 120.1 2005/06/07 02:32:38 appldev  $*/
3 
4 PROCEDURE cnt_rows(p_from_date DATE,
5                    p_to_date DATE,
6                    p_num_rows OUT NOCOPY NUMBER) IS
7   CURSOR c_cnt_rows IS
8      select sum(cnt)
9        from (select count(*) cnt
10 	     FROM
11 	     BOM_OPERATION_SEQUENCES BOS,
12 	     BOM_OPERATIONAL_ROUTINGS BOR,
13 	     BOM_STANDARD_OPERATIONS bso
14 	     WHERE BOS.ROUTING_SEQUENCE_ID = BOR.ROUTING_SEQUENCE_ID
15 	     AND bos.STANDARD_OPERATION_ID =  BSO.STANDARD_OPERATION_ID (+)
16 	     AND Greatest( bos.last_update_date, bor.last_update_date,
17 			   bso.last_update_date) between p_from_date and p_to_date
18           UNION ALL
19 	     SELECT count(*) cnt
20 	     FROM dual
21 	     );
22 
23 BEGIN
24 
25   OPEN c_cnt_rows;
26        FETCH c_cnt_rows INTO p_num_rows;
27   CLOSE c_cnt_rows;
28 
29 END;  -- procedure cnt_rows.
30 
31 
32 PROCEDURE est_row_len(p_from_date DATE,
33                    p_to_date DATE,
34                    p_est_row_len OUT NOCOPY NUMBER) IS
35 
36   x_date                 number := 7;
37   x_total                number := 0;
38   x_constant             number := 6;
39 
40   CURSOR c_org IS
41      SELECT avg(nvl(Vsize(organization_id), 0)) org_id,
42        avg(nvl(Vsize(organization_code), 0))    org_code
43        FROM mtl_parameters;
44 
45   CURSOR c_instance IS
46      SELECT
47        avg(nvl(vsize(instance_code), 0))
48        FROM	EDW_LOCAL_INSTANCE ;
49 
50   CURSOR c_bos IS
51      SELECT   avg(nvl(vsize(operation_sequence_id), 0)) seq_id,
52        avg(nvl(vsize(DEPARTMENT_ID), 0))      dept_id
53        FROM bom_operation_sequences
54        WHERE last_update_date between p_from_date  and  p_to_date;
55 
56   CURSOR c_bso IS
57      SELECT  avg(nvl(vsize(OPERATION_CODE), 0)) code,
58        avg(nvl(vsize(OPERATION_DESCRIPTION), 0)) des
59        FROM   bom_standard_operations
60        WHERE last_update_date between p_from_date  and  p_to_date;
61 
62 
63    x_instance_fk NUMBER;
64    l_org       c_org%ROWTYPE;
65    l_bos       c_bos%ROWTYPE;
66    l_bso       c_bso%ROWTYPE;
67 
68 
69 BEGIN
70 
71    OPEN c_instance;
72    FETCH c_instance INTO  x_instance_fk;
73    CLOSE c_instance;
74 
75    OPEN c_org;
76    FETCH c_org INTO l_org;
77    CLOSE c_org;
78 
79    OPEN c_bos;
80    FETCH c_bos INTO l_bos;
81    CLOSE c_bos;
82 
83    OPEN c_bso;
84    FETCH c_bso INTO l_bso;
85    CLOSE c_bso;
86 
87    x_total := x_total
88      -- OPRN_PK
89      + Ceil( Nvl(l_bos.seq_id,0) + l_org.org_id + x_instance_fk +5 + 1)
90      -- OPRC_FK
91      + 5
92      -- OPRN_DP
93      + 4
94      --  NAME  OPRN_NAME
95      + 2 * Ceil( Nvl(l_bso.code,0) + 1)
96      -- DESCRIPTION
97      + Ceil(Nvl(l_bso.des,0) + 1)
98      -- ORGN_CODE
99      + Ceil( l_org.org_code + 1)
100      -- DEPARTMENT_ID
101      + Ceil( Nvl(l_bos.dept_id,0) + 1)
102      -- LAST_UPDATE_DATE  CREATION_DATE
103      + 2 * x_date;
104 
105    p_est_row_len := x_total ;
106 
107 
108    --dbms_output.put_line ('******************'||x_total||'******') ;
109 
110 
111 END ;
112 
113 END OPI_EDW_OPI_OPRN_M_SZ ;  -- procedure est_row_len.