[Home] [Help]
PACKAGE BODY: APPS.EDW_ORGANIZATION_PKG
Source
1 PACKAGE BODY EDW_ORGANIZATION_PKG AS
2 /* $Header: hriekorg.pkb 120.1 2005/06/07 05:37:30 anmajumd noship $ */
3 -- ----------------------------------------------------------
4 -- Function to return the foreign key values for bottom-level
5 -- organizations in the EDW staging tables.
6 --
7 -- Format is: Organization Id || '-' || Instance Code
8 --
9 -- If p_id is null, return 'NA_EDW'
10 -- p_id = org_id
11 -- ----------------------------------------------------------
12 --
13 Function INT_ORGANIZATION_FK(
14 p_id IN NUMBER := NULL,
15 p_instance_code IN VARCHAR2 := NULL
16 ) return VARCHAR2 IS
17 --
18 l_instance_code edw_local_instance.instance_code%type;
19 --
20 l_org_fk VARCHAR2(240) := 'NA_EDW';
21 --
22 cursor csr_instance is
23 select instance_code
24 from edw_local_instance;
25 BEGIN
26 --
27 if p_id is null then
28 --
29 return('NA_EDW');
30 end if;
31 --
32 if p_instance_code is null then
33 --
34 OPEN csr_instance;
35 --
36 FETCH csr_instance into l_instance_code;
37 --
38 CLOSE csr_instance;
39 --
40 l_org_fk := to_char(p_id) || '-' || l_instance_code;
41 --
42 else
43 --
44 l_org_fk := to_char(p_id) || '-' || p_instance_code;
45 --
46 end if;
47 --
48 return(l_org_fk);
49 --
50 EXCEPTION
51 WHEN OTHERS THEN
52 --
53 IF csr_instance%ISOPEN THEN
54 --
55 CLOSE csr_instance;
56 --
57 END IF;
58 --
59 RETURN('NA_EDW');
60 --
61 END INT_ORGANIZATION_FK;
62 -- ----------------------------------------------------------
63 -- Function to return the foreign key values for Operating
64 -- Unit organizations in the EDW staging tables.
65 --
66 -- Format is: Operating Unit Id || '-' || Instance Code
67 --
68 -- If p_id is null, return 'NA_EDW'
69 -- If p_id is not an Operating Unit, return 'NA_EDW'
70 -- p_id = org_id
71 -- ----------------------------------------------------------
72 --
73 Function operating_unit_fk(
74 p_id IN NUMBER := NULL,
75 p_instance_code IN VARCHAR2 := NULL
76 ) return VARCHAR2 IS
77 --
78 l_check_flag VARCHAR2(1) := 'N';
79 l_instance_code edw_local_instance.instance_code%type;
80 l_ou_fk VARCHAR2(240) := 'NA_EDW';
81 --
82 cursor csr_check_ou is
83 select 'Y'
84 from hr_organization_information oi
85 where oi.org_information_context = 'CLASS'
86 and oi.org_information1 = 'OPERATING_UNIT'
87 and oi.org_information2 = 'Y'
88 and oi.organization_id = p_id;
89 --
90 cursor csr_instance is
91 select instance_code
92 from edw_local_instance;
93 --
94 BEGIN
95 --
96 if p_id is null then
97 --
98 return('NA_EDW');
99 --
100 end if;
101 -- Check whether it's a valid Operating Unit
102 OPEN csr_check_ou;
103 FETCH csr_check_ou into l_check_flag;
104 CLOSE csr_check_ou;
105 --
106 if l_check_flag = 'Y' then
107 --
108 if p_instance_code is null then
109 --
110 OPEN csr_instance;
111 FETCH csr_instance into l_instance_code;
112 CLOSE csr_instance;
113 --
114 l_ou_fk := to_char(p_id) || '-' || l_instance_code;
115 --
116 else
117 --
118 l_ou_fk := to_char(p_id) || '-' || p_instance_code;
119 --
120 end if;
121 --
122 return( l_ou_fk );
123 --
124 else
125 --
126 return('NA_EDW');
127 --
128 end if;
129 --
130 EXCEPTION
131 WHEN OTHERS THEN
132 --
133 IF csr_check_ou%ISOPEN THEN
134 CLOSE csr_check_ou;
135 END IF;
136 IF csr_instance%ISOPEN THEN
137 CLOSE csr_instance;
138 END IF;
139 RETURN('NA_EDW');
140 --
141 END operating_unit_fk;
142 --
143 END;