1 PACKAGE ZPB_AW AS
2 /* $Header: zpbaw.pls 120.5 2007/12/04 14:43:43 mbhat ship $ */
3
4 -------------------------------------------------------------------------------
5 -- EXECUTE
6 --
7 -- Function to call dbms_aw.execute
8 --
9 -- IN: p_cmd (varchar2) - The AW command to execute
10 --
11 -------------------------------------------------------------------------------
12 procedure EXECUTE (p_cmd in varchar2);
13
14 -------------------------------------------------------------------------------
15 -- INTERP <--DEPRECATED! Use EVAL_TEXT and EVAL_NUMBER instead -->
16 --
17 -- Wrapper around the call to the AW, which will parse the output. If no
18 -- output is expected, you may just run zpb_aw.execute() instead.
19 --
20 -- IN: p_cmd (varchar2) - The AW command to execute
21 -- OUT: varchar2 - The output of the the AW command
22 --
23 -------------------------------------------------------------------------------
24 function INTERP (p_cmd in varchar2)
25 return varchar2;
26
27 -------------------------------------------------------------------------------
28 -- INTERPBOOL
29 --
30 -- Wrapper around the call the AW with boolean (yes/no) output expected.
31 -- Will handle conversion within the NLS_LANGUAGE setting (Bug 4058390).
32 --
33 -- IN: p_cmd (varchar2) - The AW boolean command to execute
34 -- OUT: boolean - The output of the the AW command
35 --
36 -------------------------------------------------------------------------------
37 function INTERPBOOL (p_cmd in varchar2)
38 return boolean;
39
40 -------------------------------------------------------------------------------
41 -- EVAL_TEXT
42 --
43 -- Improved version of INTERP, which avoids many OLAP bugs. No need to
44 -- use "show" in front of command. Returns text-based queries, null if NA.
45 -- -
46 -- IN: p_cmd (varchar2) - The AW command to execute
47 -- OUT: varchar2 - The output of the the AW command
48 --
49 -------------------------------------------------------------------------------
50 function EVAL_TEXT (p_cmd in VARCHAR2)
51 return VARCHAR2;
52
53 -------------------------------------------------------------------------------
54 -- EVAL_NUMBER
55 --
56 -- Improved version of INTERP, which avoids many OLAP bugs. No need to
57 -- use "show" in front of command. Returns numeric queries, null if NA.
58 -- -
59 -- IN: p_cmd (varchar2) - The AW command to execute
60 -- OUT: varchar2 - The output of the the AW command
61 --
62 -------------------------------------------------------------------------------
63 function EVAL_NUMBER (p_cmd in VARCHAR2)
64 return NUMBER;
65
66 ------------------------------------------------------------------------------
67 -- DETACH_ALL
68 --
69 -- Detaches all AW's on the session
70 ------------------------------------------------------------------------------
71 procedure DETACH_ALL;
72
73 -------------------------------------------------------------------------------
74 -- GET_ANNOTATION_AW
75 --
76 -- Returns the un-qualified (schema not prepended) annotation aw name for
77 -- the business area.
78 --
79 -- IN: p_business_area_id (number) - The Business Area ID. If null, then
80 -- uses the Business Area in context, or
81 -- the business area currently logged in as
82 -------------------------------------------------------------------------------
83 function GET_ANNOTATION_AW
84 (p_business_area_id IN ZPB_BUSINESS_AREAS.BUSINESS_AREA_ID%type := null)
85 return ZPB_BUSINESS_AREAS.ANNOTATION_AW%type;
86
87 -------------------------------------------------------------------------------
88 -- GET_CODE_AW
89 --
90 -- Returns the un-qualified (schema not prepended) code aw name for
91 -- this user.
92 --
93 -- IN: p_user (varchar2) - The FND_USER USER_ID
94 -------------------------------------------------------------------------------
95 function GET_CODE_AW (p_user in varchar2 := null)
96 return varchar2;
97
98 -------------------------------------------------------------------------------
99 -- GET_PERSONAL_AW
100 --
101 -- Returns the un-qualified (schema not prepended) personal aw name for
102 -- this user.
103 --
104 -- IN: p_user (varchar2) - The FND_USER USER_ID
105 -------------------------------------------------------------------------------
106 function GET_PERSONAL_AW (p_user in varchar2 := null,
107 p_business_area_id in ZPB_BUSINESS_AREAS.BUSINESS_AREA_ID%type
108 := null)
109 return ZPB_USERS.PERSONAL_AW%type;
110
111 -------------------------------------------------------------------------------
112 -- GET_SHARED_AW
113 --
114 -- Returns the un-qualified (schema not prepended) shared aw name for
115 -- the business area.
116 --
117 -- IN: p_business_area_id (number) - The Business Area ID. If null, then
118 -- uses the Business Area in context, or
119 -- the business area currently logged in as
120 -------------------------------------------------------------------------------
121 function GET_SHARED_AW
122 (p_business_area_id IN ZPB_BUSINESS_AREAS.BUSINESS_AREA_ID%type := null)
123 return ZPB_BUSINESS_AREAS.DATA_AW%type;
124
125 -------------------------------------------------------------------------------
126 -- GET_SCHEMA
127 --
128 -- Returns the schema where the aw's reside
129 -------------------------------------------------------------------------------
130 function GET_SCHEMA
131 return varchar2;
132
133 -------------------------------------------------------------------------------
134 -- GET_AW_SHORT_NAME
135 --
136 -- Procedure to get the AW short name, used in CWM and view names. If a
137 -- developer AW is passed in, will use the username. Otherwise, is the same as
138 -- the AW actual name
139 --
140 -- IN: p_aw (varchar2) - The actual name of the AW
141 -- OUT: varchar2 - The short name of the AW
142 --
143 -------------------------------------------------------------------------------
144 function GET_AW_SHORT_NAME (p_aw in varchar2) return varchar2;
145
146 -------------------------------------------------------------------------------
147 -- GET_AW_TINY_NAME
148 --
149 -- Procedure to get ZPB followed by business area id from ZPB.ZPBDATAXXX
150 -- Used in CWM and view names. If a personal AW is passed in,
151 -- its name will not be changed other than the stripping of schema prefix.
152 --
153 -- IN: p_aw (varchar2) - The actual name of the AW
154 -- OUT: varchar2 - ZPB + BA_ID
155 --
156 -------------------------------------------------------------------------------
157 function GET_AW_TINY_NAME (p_aw in varchar2) return varchar2;
158
159 -------------------------------------------------------------------------------
160 -- INITIALIZE
161 --
162 -- Initializes the AW session by attaching code, annotation and shared AW for
163 -- the Business Area specified, and setting context and session-wide
164 -- parameters
165 --
166 -- No commit is done by this procedure
167 --
168 -- IN: p_business_area_id NUMBER - The Business Area ID to work under
169 -- p_shadow_id NUMBER - The shadow user ID, if different than
170 -- FND_GLOBAL.USER_ID
171 -- p_shared_rw VARCHAR2 - Whether to attach shared AW r/w. Should
172 -- only be true in a conc. req.
173 -- p_annot_rw VARCHAR2 - Whether to attach shared AW r/w. Annot AW
174 -- should only be r/w for as small a period
175 -- of time as possible
176 --
177 -------------------------------------------------------------------------------
178 PROCEDURE initialize(p_api_version IN NUMBER,
179 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
180 p_validation_level IN NUMBER
181 := FND_API.G_VALID_LEVEL_FULL,
182 x_return_status OUT NOCOPY varchar2,
183 x_msg_count OUT NOCOPY number,
184 x_msg_data OUT NOCOPY varchar2,
185 p_business_area_id IN NUMBER,
186 p_shadow_id IN NUMBER := FND_GLOBAL.USER_ID,
187 p_shared_rw IN VARCHAR2 := FND_API.G_FALSE,
188 p_annot_rw IN VARCHAR2 := FND_API.G_FALSE,
189 p_detach_all IN VARCHAR2 := FND_API.G_TRUE);
190
191 -------------------------------------------------------------------------------
192 -- INITIALIZE_FOR_AC
193 --
194 -- Initializes the AW session by attaching code, annotation and shared AW for
195 -- the business process specified, and setting context and session-wide
196 -- parameters
197 --
198 -- No commit is done by this procedure
199 --
200 -- IN: p_analysis_cycle_id NUMBER - The Analysis Cycle to initialize against
201 -------------------------------------------------------------------------------
202 PROCEDURE initialize_for_ac(p_api_version IN NUMBER,
203 p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE,
204 p_validation_level IN NUMBER
205 := FND_API.G_VALID_LEVEL_FULL,
206 x_return_status OUT NOCOPY varchar2,
207 x_msg_count OUT NOCOPY number,
208 x_msg_data OUT NOCOPY varchar2,
209 p_analysis_cycle_id IN NUMBER,
210 p_shared_rw IN VARCHAR2:=FND_API.G_FALSE,
211 p_annot_rw IN VARCHAR2:=FND_API.G_FALSE);
212
213 -------------------------------------------------------------------------------
214 -- INITIALIZE_USER
215 --
216 -- Initializes the AW session by attaching the personal AW for
217 -- the user specified. Will initialize the shared AW's
218 --
219 -- No commit is done by this procedure
220 --
221 -- IN: p_user NUMBER - The User ID to initialize against
222 -- p_business_area_id NUMBER - The Business Area ID to work under. Defaults
223 -- to the current context, which is set if
224 -- INITIALIZE(_FOR_AC) has already been called.
225 -- Will error if not set or passed in.
226 -- p_attach_readwrite VARCHAR2 - Whether to attach personal rw or ro
227 -- p_sync_shared VARCHAR2 - Whether to synch the shared with the
228 -- personal. Should be done before any
229 -- processing happens.
230 -------------------------------------------------------------------------------
231 PROCEDURE INITIALIZE_USER(p_api_version IN NUMBER,
232 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
233 p_validation_level IN NUMBER
234 := FND_API.G_VALID_LEVEL_FULL,
235 x_return_status OUT NOCOPY varchar2,
236 x_msg_count OUT NOCOPY number,
237 x_msg_data OUT NOCOPY varchar2,
238 p_user IN FND_USER.USER_ID%type,
239 p_business_area_id IN NUMBER
240 := sys_context('ZPB_CONTEXT', 'business_area_id'),
241 p_attach_readwrite IN VARCHAR2 := FND_API.G_FALSE,
242 p_sync_shared IN VARCHAR2 := FND_API.G_TRUE,
243 p_detach_all IN VARCHAR2 := FND_API.G_FALSE);
244
245 -------------------------------------------------------------------------------
246 -- clean_workspace
247 --
248 -- Procedure detaches the code and shared AWs and resets the ZPB context.
249 -- Designed to be called by backend programs that initiate an
250 -- OLAP workspace with full data access.
251 --
252 -- No commit is done by this procedure
253 --
254 -------------------------------------------------------------------------------
255 PROCEDURE clean_workspace ( p_api_version IN NUMBER,
256 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
257 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
258 x_return_status OUT NOCOPY varchar2,
259 x_msg_count OUT NOCOPY number,
260 x_msg_data OUT NOCOPY varchar2);
261
262
263 end ZPB_AW;