PACKAGE BODY: APPS.OPI_EDW_OPI_PRDL_M_SZ

Source

1 PACKAGE BODY opi_edw_opi_prdl_m_sz as
2 /* $Header: OPIOPLZB.pls 120.1 2005/06/07 02:36:09 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 count(*) cnt
9 	     FROM WIP_LINES WL,
10 		MTL_PARAMETERS MP
11 	     where WL.ORGANIZATION_ID = MP.ORGANIZATION_ID
12 		and wl.last_update_date between p_from_date and p_to_date;
13 
14 BEGIN
15 
16   OPEN c_cnt_rows;
17        FETCH c_cnt_rows INTO p_num_rows;
18   CLOSE c_cnt_rows;
19 
20 END;  -- procedure cnt_rows.
21 
22 
23 PROCEDURE est_row_len(p_from_date DATE,
24                    p_to_date DATE,
25                    p_est_row_len OUT NOCOPY NUMBER) IS
26 
27   x_date                 number := 7;
28   x_total                number := 0;
29   x_constant             number := 6;
30 
31   cursor c_wl is
32     select avg(nvl(vsize(wl.line_id),0)) line_id,
33 	avg(nvl(vsize(wl.line_code),0))  code,
34 	avg(nvl(vsize(wl.description),0)) des
35 	from wip_lines wl
36 	where wl.last_update_date between p_from_date and p_to_date;
37 
38   CURSOR c_org IS
39      SELECT avg(nvl(Vsize(organization_id), 0)) org_id,
40        avg(nvl(Vsize(organization_code), 0))    org_code
41        FROM mtl_parameters;
42 
43   CURSOR c_instance IS
44      SELECT
45        avg(nvl(vsize(instance_code), 0))
46        FROM	EDW_LOCAL_INSTANCE ;
47 
48    x_instance_fk NUMBER;
49    l_org       c_org%ROWTYPE;
50    l_wl        c_wl%rowtype;
51 
52 BEGIN
53 
54    OPEN c_instance;
55    FETCH c_instance INTO  x_instance_fk;
56    CLOSE c_instance;
57 
58    OPEN c_org;
59    FETCH c_org INTO l_org;
60    CLOSE c_org;
61 
62    open c_wl;
63    fetch c_wl into l_wl;
64    close c_wl;
65 
66    x_total := x_total
67      -- PRDL_PK
68      + Ceil( l_wl.line_id +x_instance_fk +l_org.org_id +l_org.org_code +6 +1)
69      -- PRDL_DP  NAME  PRDL_NAME
70      + 3* ceil( l_wl.code + 1)
71      -- ALL_FK
72      + 3
73      -- DESCRIPTION
74      + ceil( l_wl.des + 1)
75      -- ORGN_CODE
76      + Ceil( l_org.org_code + 1)
77      -- LAST_UPDATE_DATE  CREATION_DATE
78      + 2 * x_date;
79 
80    p_est_row_len := x_total ;
81 
82 END ;
83 
84 END opi_edw_opi_prdl_m_sz;