1 PACKAGE BODY EGO_COMMON_PVT AS
2 /* $Header: EGOAPCCB.pls 120.13 2007/03/23 10:09:17 dsakalle ship $ */
3
4
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'EGO_COMMON_PVT';
6
7
8 FUNCTION Is_EGO_Installed (
9 p_api_version IN NUMBER
10 ,p_release_version IN VARCHAR2
11 ) RETURN VARCHAR2 IS
12 l_api_version CONSTANT NUMBER := 1.0;
13 l_api_name CONSTANT VARCHAR2(30) := 'Is_APC_Installed';
14
15 l_ego_installed VARCHAR2(1);
16 l_status VARCHAR2(1);
17 BEGIN
18
19 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
20 l_api_name, G_PKG_NAME)
21 THEN
22 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
23 END IF;
24
25 -- Bug 4958641
26 -- Removed the 'EGO_INSTALLED_PVT' package existance check for R12
27 -- R12 release is single APPS delivery
28 -- So all the package objects are expected to be available in the install
29
30 SELECT STATUS
31 INTO l_status
32 FROM FND_PRODUCT_INSTALLATIONS
33 WHERE APPLICATION_ID = '431';
34
35 IF l_status = 'I' THEN
36 RETURN('T');
37 ELSE
38 RETURN('F');
39 END IF;
40
41 EXCEPTION
42 WHEN NO_DATA_FOUND THEN
43 RETURN('F');
44 WHEN OTHERS THEN
45 RETURN('F');
46
47 END Is_EGO_Installed;
48
49
50 --------------------------------------------------------------------------------
51 -- This function takes a product name and returns its associated schema name. --
52 --------------------------------------------------------------------------------
53
54 FUNCTION Get_Prod_Schema (
55 p_prod_name IN VARCHAR2
56 ) RETURN VARCHAR2 IS
57
58 l_installed BOOLEAN;
59 l_status VARCHAR2(1);
60 l_industry VARCHAR2(1);
61 l_schema VARCHAR2(30);
62
63 BEGIN
64
65 l_installed := FND_INSTALLATION.Get_App_Info (p_prod_name, l_status, l_industry, l_schema);
66 RETURN l_schema;
67
68 EXCEPTION
69
70 WHEN OTHERS THEN
71 RETURN NULL;
72
73 END Get_Prod_Schema;
74
75 /*#
76 *------------------------------------------------------------------------*
77 * This procedure updates the profile value EGO_USER_ORGANIZATION_CONTEXT *
78 * for the current user. Returns S is successful. *
79 * If any other profile id is passed, this API returns U. *
80 * @param P_PROFILE_OPTION_ID: Profile Id *
81 * @param P_PROFILE_OPTION_VALUE: Profile Value *
82 * @return X_RETURN_STATUS: Return Status. U- Unsuccessful. S- Success *
83 * @return X_MSG_DATA: Message indicating wat went wrong incase of U *
84 *------------------------------------------------------------------------*
85 */
86 PROCEDURE SAVE_USR_ORG_CTX_PROF_VAL (
87 P_PROFILE_OPTION_ID IN NUMBER
88 ,P_PROFILE_OPTION_VALUE IN VARCHAR2
89 ,P_MODE IN VARCHAR2
90 ,X_RETURN_STATUS OUT NOCOPY VARCHAR2
91 ,X_MSG_DATA OUT NOCOPY VARCHAR2
92 )
93 IS
94 L_PROFILE_OPTION_NAME VARCHAR2(80);
95 L_FND_RETURN_STATUS BOOLEAN;
96 BEGIN
97
98 X_RETURN_STATUS := 'S';
99
100 --first get the profile option name given the profile option id
101 SELECT PROFILE_OPTION_NAME INTO L_PROFILE_OPTION_NAME
102 FROM FND_PROFILE_OPTIONS
103 WHERE PROFILE_OPTION_ID = P_PROFILE_OPTION_ID;
104
105
106 IF(P_MODE = 'delete') THEN
107 X_RETURN_STATUS := 'U';
108 FND_MESSAGE.SET_NAME('EGO', 'EGO_PROF_DELETE_NOT_SUP');
109 X_MSG_DATA := FND_MESSAGE.GET;
110 RETURN;
111 END IF;
112
113 --check if the profile option passed in is EGO_USER_ORGANIZATION_CONTEXT
114 --If not, set return_status to U and message stating update of the passed in
115 --profile option is not allowed.
116 IF(L_PROFILE_OPTION_NAME <> 'EGO_USER_ORGANIZATION_CONTEXT') THEN
117 X_RETURN_STATUS := 'U';
118 IF(P_MODE = 'update') THEN
119 FND_MESSAGE.SET_NAME('EGO', 'EGO_PROF_UPDATE_NOT_SUP');
120 FND_MESSAGE.SET_TOKEN('PROFILE_NAME', L_PROFILE_OPTION_NAME);
121 X_MSG_DATA := FND_MESSAGE.GET;
122 END IF;
123 IF(P_MODE = 'insert') THEN
124 FND_MESSAGE.SET_NAME('EGO', 'EGO_PROF_INSERT_NOT_SUP');
125 FND_MESSAGE.SET_TOKEN('PROFILE_NAME', L_PROFILE_OPTION_NAME);
126 X_MSG_DATA := FND_MESSAGE.GET;
127 END IF;
128 RETURN;
129 END IF;
130
131 --now this is the profile we need to update. so call FND API to do that.
132 L_FND_RETURN_STATUS := FALSE;
133 L_FND_RETURN_STATUS := FND_PROFILE.SAVE_USER( L_PROFILE_OPTION_NAME
134 ,P_PROFILE_OPTION_VALUE
135 );
136 IF(L_FND_RETURN_STATUS) THEN
137 X_RETURN_STATUS := 'S';
138 ELSE
139 X_RETURN_STATUS := 'U';
140 FND_MESSAGE.SET_NAME('EGO', 'EGO_PROFILE_UPDATE_FAILED');
141 FND_MESSAGE.SET_TOKEN('PROFILE_NAME', L_PROFILE_OPTION_NAME);
142 X_MSG_DATA := FND_MESSAGE.GET;
143 END IF;
144
145 END SAVE_USR_ORG_CTX_PROF_VAL;
146
147 /*#
148 *------------------------------------------------------------------------*
149 * This procedure calls Change Management procedure to cancel any NIR *
150 * associated with an item being deleted. It is called from *
151 * BOM_DELETE_GROUPS_API.INVOKE_EVENTS. *
152 * @param P_INVENTORY_ITEM_ID: Id of the item being deleted. *
153 * @param P_ORGANIZATION_ID: Organization Id from which the item is being *
154 * deleted.Profile Value *
155 * @param P_ITEM_NUMBER: Item Number of the item being deleted. *
156 *------------------------------------------------------------------------*
157 */
158 PROCEDURE CANCEL_NIR_FOR_DELETE_ITEM (
159 P_INVENTORY_ITEM_ID IN NUMBER
160 ,P_ORGANIZATION_ID IN NUMBER
161 ,P_ITEM_NUMBER IN VARCHAR2
162 )
163 IS
164 l_nir_cancel_status VARCHAR2(10);
165 l_cancel_comment VARCHAR2(2000);
166 BEGIN
167
168 IF(P_INVENTORY_ITEM_ID IS NOT NULL AND P_INVENTORY_ITEM_ID IS NOT NULL) THEN
169
170 FND_MESSAGE.SET_NAME('EGO', 'EGO_CANCELLED_BY_DELETE');
171 FND_MESSAGE.SET_TOKEN('ITEM_NAME', P_ITEM_NUMBER);
172 l_cancel_comment := FND_MESSAGE.GET;
173
174 EXECUTE IMMEDIATE
175 'BEGIN ' ||
176 'ENG_NIR_UTIL_PKG.CANCEL_NIR_FOR_ITEM ' ||
177 ' ( ' ||
178 ' p_item_id => :P_INVENTORY_ITEM_ID , ' ||
179 ' p_org_id => :P_ORGANIZATION_ID , ' ||
180 ' p_auto_commit => FND_API.G_FALSE , ' ||
181 ' p_wf_user_id => FND_GLOBAL.user_id , ' ||
182 ' p_fnd_user_id => FND_GLOBAL.login_id , ' ||
183 ' p_cancel_comments => :l_cancel_comment, ' ||
184 ' p_check_security => FALSE, ' ||
185 ' x_nir_cancel_status => :l_nir_cancel_status ' ||
186 ' ); ' ||
187 ' END; '
188 USING IN P_INVENTORY_ITEM_ID, IN P_ORGANIZATION_ID, IN l_cancel_comment, OUT l_nir_cancel_status;
189
190 END IF; --IF(P_INVENTORY_ITEM_ID IS NOT NULL AND P_INVENTORY_ITEM_ID IS NOT NULL)
191
192 EXCEPTION
193 WHEN OTHERS THEN
194 NULL;
195
196 END CANCEL_NIR_FOR_DELETE_ITEM;
197
198 /*#
199 *--------------------------------------------------------------------*
200 * R12C new Function for getting the defaulting options. *
201 * This function takes in the option_code as an input and returns the *
202 * correspondin option value. *
203 * @param OPTION_CODE_IN : option code for which the value is seeked *
204 *--------------------------------------------------------------------*
205 */
206 FUNCTION GET_OPTION_VALUE(OPTION_CODE_IN IN VARCHAR2) RETURN VARCHAR2 IS
207
208 return_value VARCHAR2(30);
209
210 BEGIN
211
212 SELECT OPTION_VALUE
213 INTO RETURN_VALUE
214 FROM EGO_DEFAULT_OPTIONS
215 WHERE OPTION_CODE = OPTION_CODE_IN;
216
217 RETURN return_value;
218
219 EXCEPTION
220 WHEN others THEN
221 RETURN null;
222
223 END GET_OPTION_VALUE;
224
225 /*
226 * This procedure is used to write the debug messages into FND_LOG
227 * If this is called from within a concurrent program, the request_id
228 * will be prepended in the message.
229 * @param p_log_level: log level, a constant from FND_LOG.
230 * If passed null, then FND_LOG.LEVEL_STATEMENT
231 * will be used for logging.
232 * @param p_module: Name of the calling module
233 * for eg. EGO_IMPORT_PVT.Resolve_Child_Entities
234 * @param p_message: Message text
235 */
236 PROCEDURE WRITE_DIAGNOSTIC(p_log_level NUMBER DEFAULT NULL,
237 p_module VARCHAR2,
238 p_message VARCHAR2)
239 IS
240 l_request_id NUMBER := FND_GLOBAL.CONC_REQUEST_ID;
241 l_log_level NUMBER := NVL(p_log_level, FND_LOG.LEVEL_STATEMENT);
242 l_message VARCHAR2(32000);
243 BEGIN
244 IF l_request_id IS NOT NULL AND l_request_id > 0 THEN
245 l_message := '[Request ID=' || l_request_id || ']- ' || p_message;
246 ELSE
247 l_message := p_message;
248 END IF;
249
250 IF ( l_log_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
251 FND_LOG.STRING( l_log_level, p_module, l_message );
252 END IF;
253 END WRITE_DIAGNOSTIC;
254
255
256 END EGO_COMMON_PVT;
257