1 PACKAGE pa_org_utils as
2 /* $Header: PAXORUTS.pls 120.3 2007/10/29 10:11:25 jjgeorge ship $ */
3
4 FUNCTION get_org_version_id(x_usage in varchar2) RETURN NUMBER;
5 pragma RESTRICT_REFERENCES ( get_org_version_id, WNDS, WNPS );
6
7 /* 1333116 Added this function to return the org hierarchy version
8 and to handle burdening hierarchy */
9 FUNCTION get_org_version_id2(x_usage in varchar2) RETURN NUMBER;
10 pragma RESTRICT_REFERENCES ( get_org_version_id, WNDS, WNPS );
11
12 FUNCTION get_start_org_id(x_usage in varchar2) RETURN NUMBER;
13 pragma RESTRICT_REFERENCES ( get_start_org_id, WNDS, WNPS );
14
15 /* 1333116 Added this function to return the org hierarchy
16 start organization and to handle burdening hierarchy */
17 FUNCTION get_start_org_id2(x_usage in varchar2) RETURN NUMBER;
18 pragma RESTRICT_REFERENCES ( get_start_org_id, WNDS, WNPS );
19
20 -- Start CC Change
21 FUNCTION get_org_level(
22 p_org_version_id in number,
23 p_child_parent_org_id in number,
24 p_start_org_id in number
25 )
26 RETURN NUMBER;
27 pragma RESTRICT_REFERENCES ( get_org_level, WNDS, WNPS );
28
29 FUNCTION get_start_org_id_sch(
30 p_org_version_id in number
31 )
32 RETURN NUMBER;
33 pragma RESTRICT_REFERENCES ( get_start_org_id, WNDS, WNPS );
34
35 FUNCTION get_max_org_level(
36 p_org_version_id in number,
37 p_start_org_id in number
38 )
39 RETURN NUMBER;
40 pragma RESTRICT_REFERENCES ( get_max_org_level, WNDS, WNPS );
41
42 -- End CC Change
43
44
45 -- Start CC Change
46
47 PROCEDURE Create_org_hier_denorm_levels(p_parent_organization_id in number,
48 p_child_organization_id in number,
49 p_org_hierarchy_version_id in number,
50 p_pa_org_use_type in varchar2,
51 p_parent_level in number,
52 p_child_level in number,
53 x_err_code in out NOCOPY number, --File.Sql.39 bug 4440895
54 x_err_stage in out NOCOPY varchar2, --File.Sql.39 bug 4440895
55 x_err_stack in out NOCOPY varchar2); --File.Sql.39 bug 4440895
56
57
58 Procedure populate_hier_denorm_sch ( p_org_version_id in number,
59 x_err_code in out NOCOPY number, --File.Sql.39 bug 4440895
60 x_err_stage in out NOCOPY varchar2, --File.Sql.39 bug 4440895
61 x_err_stack in out NOCOPY varchar2); --File.Sql.39 bug 4440895
62
63 -- End CC Change
64
65 Procedure maintain_org_hist_imp(x_org_id in number,
66 x_old_proj_org_version_id in number,
67 x_new_proj_org_version_id in number,
68 x_old_exp_org_version_id in number,
69 x_new_exp_org_version_id in number,
70 x_old_org_structure_version_id in number,
71 x_new_org_structure_version_id in number,
72 x_old_proj_start_org_id in number,
73 x_new_proj_start_org_id in number,
74 x_old_exp_start_org_id in number,
75 x_new_exp_start_org_id in number,
76 x_old_start_organization_id in number,
77 x_new_start_organization_id in number,
78 x_err_code in out NOCOPY number, --File.Sql.39 bug 4440895
79 x_err_stage in out NOCOPY varchar2, --File.Sql.39 bug 4440895
80 x_err_stack in out NOCOPY varchar2); --File.Sql.39 bug 4440895
81
82 Procedure maintain_org_hist_bri(x_org_version_id in number,
83 x_organization_id_child in number,
84 x_organization_id_parent in number,
85 x_err_code in out NOCOPY number, --File.Sql.39 bug 4440895
86 x_err_stage in out NOCOPY varchar2, --File.Sql.39 bug 4440895
87 x_err_stack in out NOCOPY varchar2) ; --File.Sql.39 bug 4440895
88
89 Procedure maintain_org_hist_brd(x_org_version_id in number,
90 x_organization_id_child in number,
91 x_err_code in out NOCOPY number, --File.Sql.39 bug 4440895
92 x_err_stage in out NOCOPY varchar2, --File.Sql.39 bug 4440895
93 x_err_stack in out NOCOPY varchar2) ; --File.Sql.39 bug 4440895
94
95 Procedure Start_Org_Changed (x_old_org_version_id in number,
96 x_new_org_version_id in number,
97 x_old_start_org_id in number,
98 x_new_start_org_id in number,
99 x_org_use_type in varchar2,
100 x_org_id in number,
101 x_err_code in out NOCOPY number, --File.Sql.39 bug 4440895
102 x_err_stage in out NOCOPY varchar2, --File.Sql.39 bug 4440895
103 x_err_stack in out NOCOPY varchar2) ; --File.Sql.39 bug 4440895
104 procedure maintain_org_info_hist_bri
105 (x_organization_id in number ,
106 x_org_information1 in varchar2,
107 x_org_information_context in varchar2,
108 x_org_information2 in varchar2,
109 x_err_code in out NOCOPY number, --File.Sql.39 bug 4440895
110 x_err_stage in out NOCOPY varchar2, --File.Sql.39 bug 4440895
111 x_err_stack in out NOCOPY varchar2 ); --File.Sql.39 bug 4440895
112
113 PROCEDURE Create_org_hierarchy_denorm(p_parent_organization_id in number,
114 p_child_organization_id in number,
115 p_org_hierarchy_version_id in number,
116 p_pa_org_use_type in varchar2,
117 x_err_code in out NOCOPY number, --File.Sql.39 bug 4440895
118 x_err_stage in out NOCOPY varchar2, --File.Sql.39 bug 4440895
119 x_err_stack in out NOCOPY varchar2); --File.Sql.39 bug 4440895
120
121 --
122 -- Procedure
123 -- Create by Ranga Iyengar
124 -- Dated : 02-NOV-2000
125 -- This procedure populates data in pa_org_hierarchy_denorm
126 -- for reporting type of organizations and stores
127 -- parent level and child levels
128 --
129 --
130 PROCEDURE populate_hierarchy_denorm
131 ( p_org_version_id IN NUMBER
132 ,p_organization_id_parent IN NUMBER
133 ,p_organization_id_child IN NUMBER
134 ,x_err_code IN OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
135 ,x_err_stage IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
136 ,x_err_stack IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
137 );
138
139 PROCEDURE populate_hierarchy_denorm2
140 ( p_org_version_id IN NUMBER
141 ,p_organization_id_parent IN NUMBER
142 ,p_organization_id_child IN NUMBER
143 ,p_org_id IN NUMBER
144 ,x_err_code IN OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
145 ,x_err_stage IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
146 ,x_err_stack IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
147 );
148 PROCEDURE Check_Org_In_OrgHierarchy
149 (
150 p_organization_id IN PA_ORG_HIERARCHY_DENORM.parent_organization_id%TYPE,
151 p_org_structure_version_id IN PA_ORG_HIERARCHY_DENORM.org_hierarchy_version_id%TYPE,
152 p_org_structure_type IN PA_ORG_HIERARCHY_DENORM.pa_org_use_type%TYPE,
153 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
154 x_error_message_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
155 );
156
157 PROCEDURE Check_OrgHierarchy_Type(
158 p_org_structure_version_id IN PA_ORG_HIERARCHY_DENORM.org_hierarchy_version_id%TYPE,
159 p_org_structure_type IN PA_ORG_HIERARCHY_DENORM.pa_org_use_type%TYPE,
160 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
161 x_error_message_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
162 );
163
164 PROCEDURE Check_Org_Type(
165 p_organization_id IN PA_ORG_HIERARCHY_DENORM.parent_organization_id%TYPE,
166 p_org_structure_type IN PA_ORG_HIERARCHY_DENORM.pa_org_use_type%TYPE,
167 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
168 x_error_message_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
169 );
170
171 --
172 -- Procedure : Populate_Org_Hier_Denorm
173 -- This procedure populates data in pa_org_hierarchy_denorm
174 -- for reporting type of organizations and stores
175 -- parent level and child levels
176 -- This procedure is called by the concurrent process
177 -- "Maintain Project Resources"
178 PROCEDURE Populate_Org_Hier_Denorm(
179 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
180 x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
181
182 /* Bug#2643047 - This procedure is added so as to populate organizations for REPORTING pa_org_use_type
183 when a new organization is added in the hierarchy. That is added in per_org_structure_elements table.
184 The call to this procedure will be made from maintain_org_hist_bri
185 The newly added organization is x_organiation_id_child which is added under x_organization_id_parent*/
186
187 Procedure populate_reporting_orgs(
188 x_org_version_id in number,
189 x_organization_id_child in number,
190 x_organization_id_parent in number,
191 x_err_code in out NOCOPY number, --File.Sql.39 bug 4440895
192 x_err_stage in out NOCOPY varchar2, --File.Sql.39 bug 4440895
193 x_err_stack in out NOCOPY varchar2 --File.Sql.39 bug 4440895
194 );
195 /* Bug#2643047 - This procedure is added to restructure the levels in the table
196 pa_og_hierarchy_denorm for REPORTING pa_org_use_type when a organization is deleted from the hierarchy.
197 The call to this procedure will be made from maintain_org_hist_brd.
198 The deleted organization is x_organiation_id_child */
199
200 procedure restructure_rpt_orgs_denorm(
201 x_org_version_id in number,
202 x_err_code in out NOCOPY number, --File.Sql.39 bug 4440895
203 x_err_stage in out NOCOPY varchar2, --File.Sql.39 bug 4440895
204 x_err_stack in out NOCOPY varchar2 --File.Sql.39 bug 4440895
205 );
206
207 /* Bug 3649799 Procedure for update of denorm table, called in the update trigger */
208
209 procedure maintain_org_hist_update(x_err_code in out NOCOPY number, --File.Sql.39 bug 4440895
210 x_err_stage in out NOCOPY varchar2, --File.Sql.39 bug 4440895
211 x_err_stack in out NOCOPY varchar2); --File.Sql.39 bug 4440895
212
213 /* Bug 3649799 - added rowid plsql table for storing the rowids of records updated*/
214
215 type ridArray is table of rowid index by binary_integer;
216
217 newRows ridArray;
218 empty ridArray;
219
220 procedure maintain_projexp_org_update(p_version_id in number,
221 p_org_use_type in varchar2,
222 x_err_code in out NOCOPY number, --File.Sql.39 bug 4440895
223 x_err_stage in out NOCOPY varchar2, --File.Sql.39 bug 4440895
224 x_err_stack in out NOCOPY varchar2); --File.Sql.39 bug 4440895
225
226
227 /* Added for bug 5633304*/
228 Procedure maintain_pa_all_org(x_org_version_id in number,
229 x_err_code in out NOCOPY number,
230 x_err_stage in out NOCOPY varchar2,
231 x_err_stack in out NOCOPY varchar2);
232
233
234 END pa_org_utils;
[Home] [Help]