[Home] [Help]
PACKAGE BODY: APPS.GME_ERES_PKG
Source
1 Package BODY GME_ERES_PKG AS
2 /* $Header: GMEVERSB.pls 120.3.12000000.2 2007/03/06 21:31:31 adeshmuk ship $ */
3
4 /****************************************************************************************
5 *** Following procedure is used to insert ERES event in GME_ERES_GTMP table for ERES
6 *** processing from form
7 ****************************************************************************************/
8
9 PROCEDURE INSERT_EVENT(P_EVENT_NAME VARCHAR2,
10 P_EVENT_KEY VARCHAR2,
11 P_USER_KEY_LABEL VARCHAR2,
12 P_USER_KEY_VALUE VARCHAR2,
13 P_POST_OP_API VARCHAR2,
14 P_PARENT_EVENT VARCHAR2,
15 P_PARENT_EVENT_KEY VARCHAR2,
16 P_PARENT_ERECORD_ID NUMBER,
17 X_STATUS OUT NOCOPY VARCHAR2) IS
18 CURSOR check_event_already_exisits IS
19 select count(*)
20 from GME_ERES_GTMP
21 where event_name = p_event_name
22 and event_key = p_event_key;
23 l_count number:=0;
24 l_ins_flag VARCHAR2(5);
25 l_XML_GEN_API VARCHAR2(400);
26 BEGIN
27
28 OPEN check_event_already_exisits;
29 FETCH check_event_already_exisits into l_count;
30 CLOSE check_event_already_exisits;
31 IF l_count = 0 THEN
32 l_ins_flag := 'Y';
33 -- Meaning event data is not present in ERES Processing Table
34 IF p_event_name in ( gme_common_pvt.G_RESOURCE_ADDED
35 ,gme_common_pvt.G_RESOURCE_REMOVED
36 ,gme_common_pvt.G_RESOURCE_UPDATE)
37 THEN
38 select count(*) into l_count
39 from GME_ERES_GTMP
40 where (event_name = gme_common_pvt.G_BATCHSTEP_ADDED
41 and event_key = substrb(P_EVENT_KEY,1,instrb(P_EVENT_KEY,'-',1,2)-1)) OR
42 (event_name = gme_common_pvt.G_ACTIVITY_ADDED
43 and event_key = substrb(P_EVENT_KEY,1,instrb(P_EVENT_KEY,'-',1,3)-1));
44 IF l_count = 0 then
45 select count(*) into l_count
46 from GME_ERES_GTMP
47 where event_name in (gme_common_pvt.G_RESOURCE_ADDED )
48 and event_key = p_event_key;
49 IF (l_count > 0)
50 THEN
51 IF p_event_name = gme_common_pvt.G_RESOURCE_REMOVED THEN
52 delete GME_ERES_GTMP
53 where event_name in (gme_common_pvt.G_RESOURCE_ADDED )
54 and event_key = p_event_key;
55 end if;
56 l_ins_flag := 'N';
57 ELSE
58 if p_event_name = gme_common_pvt.G_RESOURCE_REMOVED
59 then
60 delete GME_ERES_GTMP
61 where event_name in (gme_common_pvt.G_RESOURCE_UPDATE)
62 and event_key = p_event_key;
63 end if;
64 l_ins_flag := 'Y';
65 END IF;
66 ELSE
67 l_ins_flag := 'N';
68 END IF;
69 ELSIF p_event_name in (gme_common_pvt.G_ACTIVITY_ADDED
70 ,gme_common_pvt.G_ACTIVITY_REMOVED
71 ,gme_common_pvt.G_ACTIVITY_UPDATED)
72 THEN
73 select count(*) into l_count
74 from GME_ERES_GTMP
75 where (event_name = gme_common_pvt.G_BATCHSTEP_ADDED
76 and event_key = substrb(P_EVENT_KEY,1,instrb(P_EVENT_KEY,'-',1,2)-1));
77 IF l_count = 0 then
78 select count(*) into l_count
79 from GME_ERES_GTMP
80 where event_name in (gme_common_pvt.G_ACTIVITY_ADDED )
81 and event_key = p_event_key;
82 IF (l_count > 0)
83 THEN
84 IF p_event_name = gme_common_pvt.G_ACTIVITY_REMOVED THEN
85 delete GME_ERES_GTMP
86 where event_name in (gme_common_pvt.G_ACTIVITY_ADDED )
87 and event_key = p_event_key;
88 end if;
89 l_ins_flag := 'N';
90 ELSE
91 if p_event_name = gme_common_pvt.G_ACTIVITY_REMOVED
92 then
93 delete GME_ERES_GTMP
94 where (event_name in (gme_common_pvt.G_ACTIVITY_UPDATED)
95 and event_key = p_event_key) OR
96 (event_name in (gme_common_pvt.G_RESOURCE_ADDED
97 ,gme_common_pvt.G_RESOURCE_REMOVED
98 ,gme_common_pvt.G_RESOURCE_UPDATE)
99 and substrb(EVENT_KEY,1,instrb(EVENT_KEY,'-',1,3)-1) = p_event_key);
100 end if;
101 l_ins_flag := 'Y';
102 END IF;
103 ELSE
104 l_ins_flag := 'N';
105 END IF;
106 ELSIF p_event_name in (gme_common_pvt.G_BATCHSTEP_ADDED ,
107 gme_common_pvt.G_BATCHSTEP_REMOVED ,
108 gme_common_pvt.G_BATCHSTEP_UPDATE)
109 THEN
110 select count(*) into l_count
111 from GME_ERES_GTMP
112 where event_name = gme_common_pvt.G_BATCHSTEP_ADDED
113 and event_key = p_event_key;
114 IF (l_count > 0)
115 THEN
116 IF p_event_name = gme_common_pvt.G_BATCHSTEP_REMOVED THEN
117 delete GME_ERES_GTMP
118 where event_name in (gme_common_pvt.G_BATCHSTEP_ADDED )
119 and event_key = p_event_key;
120 end if;
121 l_ins_flag := 'N';
122 ELSE
123 if p_event_name = gme_common_pvt.G_BATCHSTEP_REMOVED
124 then
125 delete GME_ERES_GTMP
126 where (event_name in (gme_common_pvt.G_BATCHSTEP_UPDATE)
127 and event_key = p_event_key) OR
128 (event_name in (gme_common_pvt.G_RESOURCE_ADDED
129 ,gme_common_pvt.G_RESOURCE_REMOVED
130 ,gme_common_pvt.G_RESOURCE_UPDATE
131 ,gme_common_pvt.G_ACTIVITY_ADDED
132 ,gme_common_pvt.G_ACTIVITY_REMOVED
133 ,gme_common_pvt.G_ACTIVITY_UPDATED)
134 and substrb(EVENT_KEY,1,instrb(EVENT_KEY,'-',1,2)-1) = p_event_key);
135 end if;
136 l_ins_flag := 'Y';
137 END IF;
138 END IF;
139 IF p_event_name in ( gme_common_pvt.G_BATCHMTL_ADDED
140 ,gme_common_pvt.G_BATCHMTL_REMOVED
141 ,gme_common_pvt.G_BATCHMTL_UPDATED)
142 THEN
143
144 select count(*) into l_count
145 from GME_ERES_GTMP
146 where event_name = gme_common_pvt.G_BATCHMTL_ADDED
147 and event_key = p_event_key;
148 IF (l_count > 0)
149 THEN
150 IF p_event_name = gme_common_pvt.G_BATCHMTL_REMOVED THEN
151 delete GME_ERES_GTMP
152 where event_name in (gme_common_pvt.G_BATCHMTL_ADDED)
153 and event_key = p_event_key;
154 end if;
155 l_ins_flag := 'N';
156 ELSE
157 if p_event_name = gme_common_pvt.G_BATCHMTL_REMOVED
158 then
159 delete GME_ERES_GTMP
160 where (event_name in (gme_common_pvt.G_BATCHMTL_UPDATED)
161 and event_key = p_event_key);
162 end if;
163 l_ins_flag := 'Y';
164 END IF;
165 END IF;
166 ELSE
167 l_ins_flag := 'N';
168 END IF;
169 IF l_ins_flag = 'Y' THEN
170 IF p_event_name in ( gme_common_pvt.G_RESOURCE_REMOVED
171 ,gme_common_pvt.G_BATCHMTL_REMOVED
172 ,gme_common_pvt.G_ACTIVITY_REMOVED
173 ,gme_common_pvt.G_BATCHSTEP_REMOVED )
174 THEN
175 l_XML_GEN_API := ' GME_ERES_PKG.GET_EVENT_XML('||''''||p_event_name||''','''||P_EVENT_KEY||''''||')';
176 ELSE
177 l_XML_GEN_API := null;
178 END IF;
179 INSERT INTO GME_ERES_GTMP ( Event_name
180 ,Event_key
181 ,Task
182 ,Action_code
183 ,User_KEY_LABEL
184 ,USER_KEY_VALUE
185 ,POST_OP_API
186 ,PARENT_EVENT
187 ,PARENT_EVENT_KEY
188 ,PARENT_ERECORD_ID
189 ,XML_GENERATION_API )
190 VALUES (P_EVENT_NAME,
191 P_EVENT_KEY,
192 null,
193 null,
194 P_USER_KEY_LABEL,
195 P_USER_KEY_VALUE,
196 P_POST_OP_API,
197 P_PARENT_EVENT,
198 P_PARENT_EVENT_KEY,
199 P_PARENT_ERECORD_ID,
200 l_XML_GEN_API);
201 END IF;
202
203 END INSERT_EVENT;
204 /****************************************************************************************
205 *** Following procedure is used to retrieve Item concatenated segments
206 *** using org_id and item_id
207 ****************************************************************************************/
208 FUNCTION GET_ITEM_NUMBER(P_ORGANIZATION_ID NUMBER,
209 P_INVENTORY_ITEM_ID NUMBER) RETURN VARCHAR2 IS
210 L_ITEM_NUMBER varchar2(240);
211 CURSOR GET_ITEM_NUM IS
212 SELECT CONCATENATED_SEGMENTS
213 FROM MTL_SYSTEM_ITEMS_B_KFV
214 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
215 AND INVENTORY_ITEM_ID = P_INVENTORY_ITEM_ID;
216 BEGIN
217 OPEN GET_ITEM_NUM;
218 FETCH GET_ITEM_NUM INTO L_ITEM_NUMBER;
219 CLOSE GET_ITEM_NUM;
220 RETURN L_ITEM_NUMBER;
221 END GET_ITEM_NUMBER;
222
223 /****************************************************************************************
224 *** Following procedure is used to retrieve operation number
225 ***
226 ****************************************************************************************/
227
228 FUNCTION GET_OPRN_NO(P_OPRN_ID NUMBER) RETURN VARCHAR2 is
229 L_OPRN_NO varchar2(80);
230 CURSOR GET_OPERATION_NO IS
231 SELECT OPRN_NO
232 FROM GMD_OPERATIONS_B
233 WHERE OPRN_ID = P_OPRN_ID;
234 BEGIN
235 OPEN GET_OPERATION_NO;
236 FETCH GET_OPERATION_NO INTO L_OPRN_NO;
237 CLOSE GET_OPERATION_NO;
238 RETURN L_OPRN_NO;
239 END GET_OPRN_NO;
240
241 FUNCTION GET_EVENT_XML (P_EVENT_NAME VARCHAR2,P_EVENT_KEY VARCHAR2) RETURN CLOB IS
242 PRAGMA AUTONOMOUS_TRANSACTION;
243 l_xml CLOB;
244 l_error_code NUMBER;
245 l_error_msg VARCHAR2(4000);
246 l_log_file VARCHAR2(4000);
247 l_map_code VARCHAR2(50);
248 l_CNT NUMBER;
249 CURSOR GET_MAP_CODE IS
250 SELECT DISTINCT b.STATUS,
251 EDR_INDEXED_XML_UTIL.GET_WF_PARAMS('EDR_XML_MAP_CODE',b.GUID) map_code
252 FROM wf_events_vl a,
253 wf_event_subscriptions b
254 WHERE a.guid=b.EVENT_FILTER_GUID
255 -- Namit S. Bug#4917171 Added the following 2 clauses.
256 -- Table wf_event_subscriptions has index on EVENT_FILTER_GUID, source_type, system_guid.
257 -- Adding the 2 clauses removes Full Table Scan of wf_event_subscriptions.
258 AND b.source_type = 'LOCAL'
259 AND b.system_guid = HEXTORAW(wf_core.TRANSLATE('WF_SYSTEM_GUID'))
260 AND b.RULE_FUNCTION ='EDR_PSIG_RULE.PSIG_RULE'
261 AND a.name = p_event_name
262 AND b.status = 'ENABLED'
263 ORDER BY b.STATUS DESC;
264 BEGIN
265 SELECT COUNT(*) INTO l_cnt
266 FROM wf_events_vl a,
267 wf_event_subscriptions b
268 WHERE a.guid=b.EVENT_FILTER_GUID
269 -- Namit S. Bug#4917171 Added the following 2 clauses.
270 AND b.source_type = 'LOCAL'
271 AND b.system_guid = HEXTORAW(wf_core.TRANSLATE('WF_SYSTEM_GUID'))
272 AND b.RULE_FUNCTION ='EDR_PSIG_RULE.PSIG_RULE'
273 AND b.status = 'ENABLED'
274 AND a.name = p_event_name;
275 IF l_cnt > 1 THEN
276 ROLLBACK;
277 return null;
278 ELSE
279 l_cnt := 0;
280 FOR GET_MAP_CODE_REC in GET_MAP_CODE
281 LOOP
282 l_map_code := GET_MAP_CODE_REC.map_code;
283 l_cnt := l_cnt + 1;
284 END LOOP;
285 IF L_CNT = 1
286 THEN
287 edr_utilities.generate_xml(P_MAP_CODE => nvl(l_map_code,P_EVENT_NAME)
288 ,P_DOCUMENT_ID => P_EVENT_KEY
289 ,p_xml => l_xml
290 ,p_error_code => l_error_code
291 ,p_error_msg => l_error_msg
292 ,p_log_file => l_log_file);
293
294 ROLLBACK;
295 RETURN l_xml;
296 ELSE
297 ROLLBACK;
298 RETURN NULL;
299 END IF;
300 END IF;
301 ROLLBACK;
302 RETURN l_xml;
303 END;
304
305 END;