[Home] [Help]
PACKAGE BODY: APPS.DPP_BUSINESSEVENTS_PVT
Source
4 -- Package name : DPP_BUSINESSEVENTS_PVT
1 PACKAGE BODY DPP_BUSINESSEVENTS_PVT AS
2 /* $Header: dppvbevb.pls 120.54 2011/09/13 09:12:12 rvkondur noship $ */
3
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10 PROCEDURE Raise_Workflow_Event(
11 x_return_status OUT NOCOPY VARCHAR2
12 ,x_msg_count OUT NOCOPY NUMBER
13 ,x_msg_data OUT NOCOPY VARCHAR2
14 ,p_txn_hdr_id IN NUMBER
15 ,p_process_code IN VARCHAR2
16 ,p_input_xml IN CLOB
17 ,p_row_count IN NUMBER
18 ,p_exe_dtl_id IN NUMBER
19 );
20
21 FUNCTION IS_NOTIF_EXECUTED(P_TXN_HDR_ID IN NUMBER, P_PROC_CODE IN VARCHAR2) RETURN BOOLEAN;
22
23 G_PKG_NAME CONSTANT VARCHAR2(30) := 'DPP_BUSINESSEVENTS_PVT';
24 G_DEBUG BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_high);
25 G_FILE_NAME CONSTANT VARCHAR2(12) := 'dppvbevb.pls';
26
27
28 FUNCTION IS_NOTIF_EXECUTED(P_TXN_HDR_ID IN NUMBER, P_PROC_CODE IN VARCHAR2)
29 RETURN BOOLEAN IS
30 L_EXECUTED BOOLEAN DEFAULT FALSE;
31 L_EXEC_COUNT NUMBER;
32 BEGIN
33 --Get the number of times the notification process was run without an error for this transaction, from the dpp execution details table.
34 SELECT COUNT(1)
35 INTO L_EXEC_COUNT
36 FROM DPP_EXECUTION_DETAILS
37 WHERE TRANSACTION_HEADER_ID=P_TXN_HDR_ID
38 AND PROCESS_CODE=P_PROC_CODE
39 AND EXECUTION_STATUS <> 'ERROR';
40
41 --return true if the process was not run.
42 IF L_EXEC_COUNT > 0
43 THEN
44 L_EXECUTED := TRUE;
45 END IF;
46
47 RETURN L_EXECUTED;
48 END IS_NOTIF_EXECUTED;
49
50
51
52 PROCEDURE SEND_CANCEL_NOTIFICATIONS(
53 P_API_VERSION IN NUMBER,
57 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
54 P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
55 P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
56 P_VALIDATION_LEVEL IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
58 X_MSG_COUNT OUT NOCOPY NUMBER,
59 X_MSG_DATA OUT NOCOPY VARCHAR2,
60
61 P_TXN_HDR_ID IN NUMBER
62 )
63 IS
64 L_API_VERSION CONSTANT NUMBER := 1.0;
65 L_API_NAME CONSTANT VARCHAR2(30) := 'SEND_CANCEL_NOTIFICATIONS';
66 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| L_API_NAME;
67 l_module CONSTANT VARCHAR2(100) := 'dpp.plsql.DPP_BUSINESSEVENTS_PVT.SEND_CANCEL_NOTIFICATIONS';
68
69 BEGIN
70
71 SAVEPOINT SEND_CANCEL_NOTIF;
72
73 -- Standard call to check for call compatibility.
74 IF NOT FND_API.COMPATIBLE_API_CALL (L_API_VERSION, P_API_VERSION, L_API_NAME, G_PKG_NAME)
75 THEN
76 DPP_UTILITY_PVT.DEBUG_MESSAGE(FND_LOG.LEVEL_STATEMENT, l_module, L_API_NAME || ' : Not a compatible API call.');
77 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
78 END IF;
79
80 DPP_UTILITY_PVT.DEBUG_MESSAGE(FND_LOG.LEVEL_STATEMENT, l_module, L_API_NAME||': START');
81
82 --Initialize message list if p_init_msg_list is TRUE.
83 IF FND_API.TO_BOOLEAN (P_INIT_MSG_LIST) THEN
84 FND_MSG_PUB.INITIALIZE;
85 END IF;
86
87 -- Initialize API return status to sucess
88 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
89
90 IF IS_NOTIF_EXECUTED(P_TXN_HDR_ID, 'INPL')
91 THEN
92 --Call the RAISE_BUSINESS_EVENT procedure with CNCL_INPL as the process code.
93 RAISE_BUSINESS_EVT_FOR_PROCESS (
94 P_API_VERSION => L_API_VERSION, P_INIT_MSG_LIST => P_INIT_MSG_LIST, P_COMMIT => P_COMMIT, P_VALIDATION_LEVEL => P_VALIDATION_LEVEL,
95 X_RETURN_STATUS => X_RETURN_STATUS, X_MSG_COUNT => X_MSG_COUNT, X_MSG_DATA => X_MSG_DATA,
96 P_TXN_HDR_ID => P_TXN_HDR_ID, P_PROCESS_CODE => 'CNCL_INPL'
97 );
98 END IF;
99
100 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
101 RAISE FND_API.G_EXC_ERROR;
102 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
103 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
104 END IF;
105
106 --If an outbound price list notification was sent earlier, then
107 IF IS_NOTIF_EXECUTED(P_TXN_HDR_ID, 'OUTPL')
108 THEN
109 --Call the RAISE_BUSINESS_EVENT procedure with CNCL_OUTPL as the process code.
110 RAISE_BUSINESS_EVT_FOR_PROCESS (
111 P_API_VERSION => L_API_VERSION, P_INIT_MSG_LIST => P_INIT_MSG_LIST, P_COMMIT => P_COMMIT, P_VALIDATION_LEVEL => P_VALIDATION_LEVEL,
112 X_RETURN_STATUS => X_RETURN_STATUS, X_MSG_COUNT => X_MSG_COUNT, X_MSG_DATA => X_MSG_DATA,
113 P_TXN_HDR_ID => P_TXN_HDR_ID, P_PROCESS_CODE => 'CNCL_OUTPL'
114 );
115 END IF;
116
117 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
118 RAISE FND_API.G_EXC_ERROR;
119 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
120 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
121 END IF;
122
123 --If a notification for offers and promotions was sent earlier, then
124 IF IS_NOTIF_EXECUTED(P_TXN_HDR_ID, 'PROMO')
125 THEN
126 --Call the RAISE_BUSINESS_EVENT procedure with CNCL_PROMO as the process code.
127 RAISE_BUSINESS_EVT_FOR_PROCESS (
128 P_API_VERSION => L_API_VERSION, P_INIT_MSG_LIST => P_INIT_MSG_LIST, P_COMMIT => P_COMMIT, P_VALIDATION_LEVEL => P_VALIDATION_LEVEL,
129 X_RETURN_STATUS => X_RETURN_STATUS, X_MSG_COUNT => X_MSG_COUNT, X_MSG_DATA => X_MSG_DATA,
130 P_TXN_HDR_ID => P_TXN_HDR_ID, P_PROCESS_CODE => 'CNCL_PROMO'
131 );
132 END IF;
133
134 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
135 RAISE FND_API.G_EXC_ERROR;
136 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
137 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
138 END IF;
139
140 --If a notification for purchase orders was sent earlier, then
141 IF IS_NOTIF_EXECUTED(P_TXN_HDR_ID, 'NTFYPO')
142 THEN
143 --Call the RAISE_BUSINESS_EVENT procedure with CNCL_NTFYPO as the process code.
144 RAISE_BUSINESS_EVT_FOR_PROCESS (
145 P_API_VERSION => L_API_VERSION, P_INIT_MSG_LIST => P_INIT_MSG_LIST, P_COMMIT => P_COMMIT, P_VALIDATION_LEVEL => P_VALIDATION_LEVEL,
146 X_RETURN_STATUS => X_RETURN_STATUS, X_MSG_COUNT => X_MSG_COUNT, X_MSG_DATA => X_MSG_DATA,
147 P_TXN_HDR_ID => P_TXN_HDR_ID, P_PROCESS_CODE => 'CNCL_NTFYPO'
148 );
149 END IF;
150
151 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
152 RAISE FND_API.G_EXC_ERROR;
153 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
154 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
155 END IF;
156
157 EXCEPTION
158 WHEN FND_API.G_EXC_ERROR THEN
159 ROLLBACK TO SEND_CANCEL_NOTIF;
160 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
161 -- Standard call to get message count and if count=1, get the message
162 FND_MSG_PUB.COUNT_AND_GET (P_ENCODED => FND_API.G_FALSE, P_COUNT => X_MSG_COUNT, P_DATA => X_MSG_DATA);
163 IF X_MSG_COUNT > 1 THEN
164 FOR I IN 1..X_MSG_COUNT LOOP
165 X_MSG_DATA := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
166 END LOOP;
167 END IF;
168 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
169 ROLLBACK TO SEND_CANCEL_NOTIF;
170 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
171 -- Standard call to get message count and if count=1, get the message
172 FND_MSG_PUB.COUNT_AND_GET (P_ENCODED => FND_API.G_FALSE, P_COUNT => X_MSG_COUNT, P_DATA => X_MSG_DATA);
173 IF X_MSG_COUNT > 1 THEN
174 FOR I IN 1..X_MSG_COUNT LOOP
178 WHEN OTHERS THEN
175 X_MSG_DATA := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
176 END LOOP;
177 END IF;
179 ROLLBACK TO SEND_CANCEL_NOTIF;
180 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
181
182 DPP_UTILITY_PVT.DEBUG_MESSAGE(FND_LOG.LEVEL_STATEMENT, l_module, L_API_NAME || ',FND_API.G_RET_STS_UNEXP_ERROR :: ' || SQLERRM);
183
184 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
185 FND_MESSAGE.SET_NAME('FND', 'SQL_PLSQL_ERROR');
186 FND_MESSAGE.SET_TOKEN('ROUTINE', L_FULL_NAME);
187 FND_MESSAGE.SET_TOKEN('ERRNO', SQLCODE);
188 FND_MESSAGE.SET_TOKEN('REASON', SQLERRM);
189 END IF;
190
191 IF FND_MSG_PUB.CHECK_MSG_LEVEL ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
192 THEN
193 FND_MSG_PUB.ADD_EXC_MSG(G_PKG_NAME, L_API_NAME);
194 END IF;
195 -- Standard call to get message count and if count=1, get the message
196 FND_MSG_PUB.COUNT_AND_GET (P_ENCODED => FND_API.G_FALSE, P_COUNT => X_MSG_COUNT, P_DATA => X_MSG_DATA);
197 IF X_MSG_COUNT > 1 THEN
198 FOR I IN 1..X_MSG_COUNT LOOP
199 X_MSG_DATA := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
200 END LOOP;
201 END IF;
202
203 END SEND_CANCEL_NOTIFICATIONS;
204
205
206 PROCEDURE RAISE_BUSINESS_EVT_FOR_PROCESS (
207 P_API_VERSION IN NUMBER,
208 P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
209 P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
210 P_VALIDATION_LEVEL IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
211
212 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
213 X_MSG_COUNT OUT NOCOPY NUMBER,
214 X_MSG_DATA OUT NOCOPY VARCHAR2,
215
216 P_TXN_HDR_ID IN NUMBER,
217 P_PROCESS_CODE IN VARCHAR2
218 )
219 IS
220
221 L_API_VERSION CONSTANT NUMBER := 1.0;
222 L_API_NAME CONSTANT VARCHAR2(35) := 'RAISE_BUSINESS_EVENT_FOR_PROCESS';
223 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| L_API_NAME;
224 l_module CONSTANT VARCHAR2(100) := 'dpp.plsql.DPP_BUSINESSEVENTS_PVT.RAISE_BUSINESS_EVT_FOR_PROCESS';
225
226 L_TXN_HDR_NUM VARCHAR2(255);
227
228 L_TXN_HDR_REC DPP_TXN_HDR_REC_TYPE;
229 L_TXN_LINE_ID DPP_TXN_LINE_TBL_TYPE;
230
231 BEGIN
232
233 SAVEPOINT RAISE_BUS_EVT_FOR_PROC;
234
235 -- Standard call to check for call compatibility.
236 IF NOT FND_API.COMPATIBLE_API_CALL (L_API_VERSION, P_API_VERSION, L_API_NAME, G_PKG_NAME)
237 THEN
238 DPP_UTILITY_PVT.DEBUG_MESSAGE(FND_LOG.LEVEL_STATEMENT, l_module, L_API_NAME || ' : Not a compatible API call.');
239 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
240 END IF;
241
242 DPP_UTILITY_PVT.DEBUG_MESSAGE(FND_LOG.LEVEL_PROCEDURE, l_module, L_API_NAME||': START');
243
244 --Initialize message list if p_init_msg_list is TRUE.
245 IF FND_API.TO_BOOLEAN (P_INIT_MSG_LIST) THEN
246 FND_MSG_PUB.INITIALIZE;
247 END IF;
248
249 -- Initialize API return status to sucess
250 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
251
252 SELECT TRANSACTION_NUMBER
253 INTO L_TXN_HDR_NUM
254 FROM DPP_TRANSACTION_HEADERS_ALL
255 WHERE TRANSACTION_HEADER_ID = P_TXN_HDR_ID;
256
257 L_TXN_HDR_REC := NULL;
258 L_TXN_LINE_ID.DELETE();
259 L_TXN_HDR_REC.TRANSACTION_HEADER_ID := P_TXN_HDR_ID;
260 L_TXN_HDR_REC.TRANSACTION_NUMBER := L_TXN_HDR_NUM;
261 L_TXN_HDR_REC.PROCESS_CODE := P_PROCESS_CODE;
262
263 RAISE_BUSINESS_EVENT( P_API_VERSION => L_API_VERSION,
264 P_INIT_MSG_LIST => P_INIT_MSG_LIST,
265 P_COMMIT => P_COMMIT,
266 P_VALIDATION_LEVEL => P_VALIDATION_LEVEL,
267 X_RETURN_STATUS => X_RETURN_STATUS,
268 X_MSG_COUNT => X_MSG_COUNT,
269 X_MSG_DATA => X_MSG_DATA,
270 P_TXN_HDR_REC => L_TXN_HDR_REC,
271 P_TXN_LINE_ID => L_TXN_LINE_ID
272 );
273
274 IF X_RETURN_STATUS = FND_API.G_RET_STS_SUCCESS THEN
275 FND_FILE.PUT_LINE(FND_FILE.LOG,'Business event raised for the process code : ' || P_PROCESS_CODE);
276 FND_FILE.NEW_LINE(FND_FILE.LOG);
277 ELSE
278 FND_FILE.PUT_LINE(FND_FILE.LOG,'Business event not raised for the process code : ' || P_PROCESS_CODE);
279 FND_FILE.NEW_LINE(FND_FILE.LOG);
280 FND_FILE.PUT_LINE(FND_FILE.LOG,substr(('Error Message : '||X_MSG_DATA),1,4000));
281 FND_FILE.NEW_LINE(FND_FILE.LOG);
282 --RETCODE := '2';
283 --ERRBUFF := 'ERROR';
284 END IF;
285
286 EXCEPTION
287 WHEN FND_API.G_EXC_ERROR THEN
288 ROLLBACK TO RAISE_BUS_EVT_FOR_PROC;
289 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
290 -- Standard call to get message count and if count=1, get the message
291 FND_MSG_PUB.COUNT_AND_GET (P_ENCODED => FND_API.G_FALSE, P_COUNT => X_MSG_COUNT, P_DATA => X_MSG_DATA);
292 IF X_MSG_COUNT > 1 THEN
293 FOR I IN 1..X_MSG_COUNT LOOP
294 X_MSG_DATA := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
295 END LOOP;
296 END IF;
297 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
298 ROLLBACK TO RAISE_BUS_EVT_FOR_PROC;
299 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
303 FOR I IN 1..X_MSG_COUNT LOOP
300 -- Standard call to get message count and if count=1, get the message
301 FND_MSG_PUB.COUNT_AND_GET (P_ENCODED => FND_API.G_FALSE, P_COUNT => X_MSG_COUNT, P_DATA => X_MSG_DATA);
302 IF X_MSG_COUNT > 1 THEN
304 X_MSG_DATA := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
305 END LOOP;
306 END IF;
307 WHEN OTHERS THEN
308 ROLLBACK TO RAISE_BUS_EVT_FOR_PROC;
309 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
310
311 DPP_UTILITY_PVT.DEBUG_MESSAGE(FND_LOG.LEVEL_STATEMENT, l_module, L_API_NAME || ',FND_API.G_RET_STS_UNEXP_ERROR :: ' || SQLERRM);
312
313 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
314 FND_MESSAGE.SET_NAME('FND', 'SQL_PLSQL_ERROR');
315 FND_MESSAGE.SET_TOKEN('ROUTINE', L_FULL_NAME);
316 FND_MESSAGE.SET_TOKEN('ERRNO', SQLCODE);
317 FND_MESSAGE.SET_TOKEN('REASON', SQLERRM);
318 END IF;
319
320 IF FND_MSG_PUB.CHECK_MSG_LEVEL ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
321 THEN
322 FND_MSG_PUB.ADD_EXC_MSG(G_PKG_NAME, L_API_NAME);
323 END IF;
324 -- Standard call to get message count and if count=1, get the message
325 FND_MSG_PUB.COUNT_AND_GET (P_ENCODED => FND_API.G_FALSE, P_COUNT => X_MSG_COUNT, P_DATA => X_MSG_DATA);
326 IF X_MSG_COUNT > 1 THEN
327 FOR I IN 1..X_MSG_COUNT LOOP
328 X_MSG_DATA := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
329 END LOOP;
330 END IF;
331
332 END RAISE_BUSINESS_EVT_FOR_PROCESS;
333
334
335
336
337
338 PROCEDURE RAISE_EFFECTIVE_DATE_EVENT(
339 P_API_VERSION IN NUMBER,
340 P_INIT_MSG_LIST IN VARCHAR2 DEFAULT FND_API.G_FALSE,
341 P_COMMIT IN VARCHAR2 DEFAULT FND_API.G_FALSE,
342 P_VALIDATION_LEVEL IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
343 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
344 X_MSG_DATA OUT NOCOPY VARCHAR2,
345 X_MSG_COUNT OUT NOCOPY NUMBER,
346 P_PROGRAM_ID IN NUMBER)
347
348 IS
349
350 L_API_NAME CONSTANT VARCHAR2(30) := 'RAISE_EFFECTIVE_DATE_EVENT';
351 L_API_VERSION CONSTANT NUMBER := 1.0;
352 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
353 l_module CONSTANT VARCHAR2(100) := 'dpp.plsql.DPP_BUSINESSEVENTS_PVT.RAISE_EFFECTIVE_DATE_EVENT';
354
355 L_EVENT_NAME VARCHAR2(60) := 'oracle.apps.dpp.notifyeffective';
356 L_ITEM_KEY VARCHAR2(30);
357 L_PARAMETER_LIST WF_PARAMETER_LIST_T;
358 L_USER_NAME VARCHAR2(255);
359 L_EVENT_TEST VARCHAR2(10);
360
361 BEGIN
362
363 SAVEPOINT START_WF_ON_EFF_DATE;
364
365 -- Standard call to check for call compatibility.
366 IF NOT FND_API.COMPATIBLE_API_CALL (L_API_VERSION, P_API_VERSION, L_API_NAME, G_PKG_NAME)
367 THEN
368 DPP_UTILITY_PVT.DEBUG_MESSAGE(FND_LOG.LEVEL_STATEMENT, l_module, L_API_NAME || ' : Not a compatible API call.');
369 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
370 END IF;
371
372 DPP_UTILITY_PVT.DEBUG_MESSAGE( FND_LOG.LEVEL_PROCEDURE, l_module, L_API_NAME||': START');
373 DPP_UTILITY_PVT.DEBUG_MESSAGE(FND_LOG.LEVEL_STATEMENT, l_module, L_API_NAME || ', Program ID : ' || P_PROGRAM_ID );
374
375 --Initialize message list if p_init_msg_list is TRUE.
376 IF FND_API.TO_BOOLEAN (P_INIT_MSG_LIST) THEN
377 FND_MSG_PUB.INITIALIZE;
378 END IF;
379
380 -- Initialize API return status to sucess
381 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
382
383 L_ITEM_KEY := DBMS_UTILITY.GET_TIME();
384
385 DPP_UTILITY_PVT.DEBUG_MESSAGE(FND_LOG.LEVEL_STATEMENT, l_module, L_API_NAME || ', Event Key : ' || L_ITEM_KEY);
386
387 L_EVENT_TEST := WF_EVENT.TEST(L_EVENT_NAME);
388
389 DPP_UTILITY_PVT.DEBUG_MESSAGE(FND_LOG.LEVEL_STATEMENT, l_module, L_API_NAME || ', Event Test : ' || L_EVENT_TEST);
390
391 IF L_EVENT_TEST = 'NONE' THEN
392 X_MSG_DATA := FND_MESSAGE.GET();
393 DPP_UTILITY_PVT.DEBUG_MESSAGE(FND_LOG.LEVEL_STATEMENT, l_module, 'No enabled local subscriptions reference the event, or the event does not exist.');
394 FND_FILE.PUT_LINE(FND_FILE.LOG, 'No enabled local subscriptions reference the event, or the event does not exist.');
395 FND_FILE.NEW_LINE(FND_FILE.LOG);
396 RAISE FND_API.G_EXC_ERROR;
397 END IF;
398
399 L_PARAMETER_LIST := WF_PARAMETER_LIST_T();
400 WF_EVENT.ADDPARAMETERTOLIST( P_NAME => 'PROGRAM_ID', P_VALUE => P_PROGRAM_ID, P_PARAMETERLIST => L_PARAMETER_LIST );
401 WF_EVENT.RAISE ( P_EVENT_NAME => L_EVENT_NAME,
402 P_EVENT_KEY => L_ITEM_KEY,
403 P_PARAMETERS => L_PARAMETER_LIST,
404 P_SEND_DATE => SYSDATE );
405
406 DPP_UTILITY_PVT.DEBUG_MESSAGE(FND_LOG.LEVEL_STATEMENT, l_module, L_API_NAME || ', Event raised : ' || L_ITEM_KEY );
407
408 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Event raised : ' || L_ITEM_KEY );
409 FND_FILE.NEW_LINE(FND_FILE.LOG);
410
411
412 FND_MSG_PUB.COUNT_AND_GET(P_COUNT => X_MSG_COUNT, P_DATA => X_MSG_DATA );
413
414 EXCEPTION
415
416 WHEN FND_API.G_EXC_ERROR THEN
417 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR ;
418 DPP_UTILITY_PVT.DEBUG_MESSAGE(FND_LOG.LEVEL_EXCEPTION, l_module, L_API_NAME || ', FND_API.G_EXC_ERROR : ' || SQLERRM);
419
420 FND_FILE.PUT_LINE(FND_FILE.LOG,'EXC_ERROR : '||SQLERRM);
421 FND_FILE.NEW_LINE(FND_FILE.LOG);
425 FOR I IN 1..X_MSG_COUNT LOOP
422 ROLLBACK TO START_WF_ON_EFF_DATE;
423 FND_MSG_PUB.COUNT_AND_GET (P_ENCODED => FND_API.G_FALSE, P_COUNT => X_MSG_COUNT, P_DATA => X_MSG_DATA);
424 IF X_MSG_COUNT > 1 THEN
426 X_MSG_DATA := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
427 END LOOP;
428 END IF;
429 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
430 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR ;
431 DPP_UTILITY_PVT.DEBUG_MESSAGE(FND_LOG.LEVEL_EXCEPTION, l_module, L_API_NAME || ', FND_API.G_RET_STS_UNEXP_ERROR : ' || SQLERRM);
432
433 FND_FILE.PUT_LINE(FND_FILE.LOG, 'UNEXP_ERROR : '||SQLERRM);
434 FND_FILE.NEW_LINE(FND_FILE.LOG);
435 ROLLBACK TO START_WF_ON_EFF_DATE;
436 FND_MSG_PUB.COUNT_AND_GET (P_ENCODED => FND_API.G_FALSE, P_COUNT => X_MSG_COUNT, P_DATA => X_MSG_DATA);
437 IF X_MSG_COUNT > 1 THEN
438 FOR I IN 1..X_MSG_COUNT LOOP
439 X_MSG_DATA := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
440 END LOOP;
441 END IF;
442 WHEN OTHERS THEN
443 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
444 DPP_UTILITY_PVT.DEBUG_MESSAGE(FND_LOG.LEVEL_EXCEPTION, l_module, L_API_NAME || ',FND_API.G_RET_STS_UNEXP_ERROR :: ' || SQLERRM);
445
446 FND_FILE.PUT_LINE(FND_FILE.LOG, 'OTHER_ERROR : '||SQLERRM);
447 FND_FILE.NEW_LINE(FND_FILE.LOG);
448 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
449 FND_MESSAGE.SET_NAME('FND', 'SQL_PLSQL_ERROR');
450 FND_MESSAGE.SET_TOKEN('ROUTINE', L_FULL_NAME);
451 FND_MESSAGE.SET_TOKEN('ERRNO', SQLCODE);
452 FND_MESSAGE.SET_TOKEN('REASON', SQLERRM);
453 END IF;
454
455 ROLLBACK TO START_WF_ON_EFF_DATE;
456 IF FND_MSG_PUB.CHECK_MSG_LEVEL (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
457 THEN
458 FND_MSG_PUB.ADD_EXC_MSG( G_PKG_NAME,L_API_NAME);
459 END IF;
460
461 FND_MSG_PUB.COUNT_AND_GET (P_ENCODED => FND_API.G_FALSE, P_COUNT => X_MSG_COUNT, P_DATA => X_MSG_DATA);
462 IF X_MSG_COUNT > 1 THEN
463 FOR I IN 1..X_MSG_COUNT LOOP
464 X_MSG_DATA := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
465 END LOOP;
466 END IF;
467
468 END RAISE_EFFECTIVE_DATE_EVENT;
469
470
471
472 PROCEDURE SEND_EFFECTIVE_DATE_NOTIF(
473 ITEMTYPE IN VARCHAR2,
474 ITEMKEY IN VARCHAR2,
475 ACTID IN NUMBER,
476 FUNCMODE IN VARCHAR2,
477 RESULT IN OUT NOCOPY VARCHAR2
478 )
479 IS
480
481 L_PROGRAM_ID NUMBER := 0;
482 l_module CONSTANT VARCHAR2(100) := 'dpp.plsql.DPP_BUSINESSEVENTS_PVT.SEND_EFFECTIVE_DATE_NOTIF';
483
484 CURSOR GET_USER_LIST (P_PROGRAM_ID VARCHAR2) IS
485 SELECT DISTINCT H.CREATED_BY, U.USER_NAME
486 FROM DPP_TRANSACTION_HEADERS_ALL H, FND_USER U
487 WHERE TRUNC(H.EFFECTIVE_START_DATE) < TRUNC(SYSDATE)
488 AND H.REQUEST_ID=P_PROGRAM_ID
489 AND H.TRANSACTION_STATUS = 'PENDING_ADJUSTMENT'
490 AND H.CREATED_BY=U.USER_ID;
491
492 L_USER_ID VARCHAR2(50);
493 L_USER_NAME VARCHAR2(50);
494 L_NOTIFY_ID NUMBER;
495 L_WF_ID VARCHAR2(10) := 'DPP_EDNF';
496
497 BEGIN
498
499 L_PROGRAM_ID := WF_ENGINE.GETITEMATTRNUMBER(ITEMTYPE => ITEMTYPE, ITEMKEY => ITEMKEY, ANAME =>'PROGRAM_ID');
500
501 DPP_UTILITY_PVT.DEBUG_MESSAGE(FND_LOG.LEVEL_STATEMENT, l_module, 'SEND_EFFECTIVE_DATE_NOTIF :: itemtype:' || itemtype);
502 DPP_UTILITY_PVT.DEBUG_MESSAGE(FND_LOG.LEVEL_STATEMENT, l_module, 'SEND_EFFECTIVE_DATE_NOTIF :: itemkey:' || itemkey);
503 DPP_UTILITY_PVT.DEBUG_MESSAGE(FND_LOG.LEVEL_STATEMENT, l_module, 'SEND_EFFECTIVE_DATE_NOTIF :: actid:' || actid);
504 DPP_UTILITY_PVT.DEBUG_MESSAGE(FND_LOG.LEVEL_STATEMENT, l_module, 'SEND_EFFECTIVE_DATE_NOTIF :: funcmode:' || funcmode);
505 DPP_UTILITY_PVT.DEBUG_MESSAGE(FND_LOG.LEVEL_STATEMENT, l_module, 'SEND_EFFECTIVE_DATE_NOTIF :: programid' || L_PROGRAM_ID);
506
507 OPEN GET_USER_LIST(L_PROGRAM_ID);
508 LOOP
509 FETCH GET_USER_LIST INTO L_USER_ID, L_USER_NAME;
510 EXIT WHEN GET_USER_LIST%NOTFOUND;
511
512 DPP_UTILITY_PVT.DEBUG_MESSAGE(FND_LOG.LEVEL_STATEMENT, l_module, 'SEND_EFFECTIVE_DATE_NOTIF :: ' || L_USER_ID);
513 DPP_UTILITY_PVT.DEBUG_MESSAGE(FND_LOG.LEVEL_STATEMENT, l_module, 'SEND_EFFECTIVE_DATE_NOTIF :: ' || L_USER_NAME);
514
515 WF_ENGINE.SETITEMATTRTEXT(ITEMTYPE => ITEMTYPE,
516 ITEMKEY => ITEMKEY,
517 ANAME => 'USER_ID',
518 AVALUE => L_USER_ID);
519
520 WF_ENGINE.SETITEMATTRTEXT(ITEMTYPE => ITEMTYPE,
521 ITEMKEY => ITEMKEY,
522 ANAME => 'EFFECTIVE_DATE',
523 AVALUE => TRUNC(SYSDATE));
524
525 -- L_PARAMETER_LIST := WF_PARAMETER_LIST_T();
526 --
527 -- WF_EVENT.ADDPARAMETERTOLIST ( P_NAME => 'CREATED_USER_ID',
528 -- P_VALUE => L_USER_NAME,
529 -- P_PARAMETERLIST => L_PARAMETER_LIST);
530
531 L_NOTIFY_ID := WF_NOTIFICATION.SEND(
532 ROLE => L_USER_NAME,
533 MSG_TYPE => L_WF_ID,
534 MSG_NAME => 'TXN_LIST_MSG',
535 DUE_DATE => NULL,
536 CALLBACK => 'WF_ENGINE.CB',
537 CONTEXT => L_WF_ID || ':' || ITEMKEY || ':',
541 DPP_UTILITY_PVT.DEBUG_MESSAGE(FND_LOG.LEVEL_STATEMENT, l_module, 'SEND_EFFECTIVE_DATE_NOTIF :: ' || L_NOTIFY_ID);
538 SEND_COMMENT => NULL,
539 PRIORITY => NULL );
540
542
543 END LOOP;
544 CLOSE GET_USER_LIST;
545
546 RESULT := 'COMPLETE:Y';
547
548 end SEND_EFFECTIVE_DATE_NOTIF;
549
550
551 ---------------------------------------------------------------------
552 -- PROCEDURE
553 -- Raise_Business_Event
554 --
555 -- PURPOSE
556 -- Raise Business Event.
557 --
558 -- PARAMETERS
559 --
560 -- NOTES
561 -- 1.
562 -- 2.
563 ----------------------------------------------------------------------
564
565 PROCEDURE Raise_Business_Event(
566 p_api_version IN NUMBER
567 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
568 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
569 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
570 ,x_return_status OUT NOCOPY VARCHAR2
571 ,x_msg_count OUT NOCOPY NUMBER
572 ,x_msg_data OUT NOCOPY VARCHAR2
573
574 ,p_txn_hdr_rec IN dpp_txn_hdr_rec_type
575 ,p_txn_line_id IN dpp_txn_line_tbl_type
576 )
577 IS
578 l_api_version CONSTANT NUMBER := 1.0;
579 l_api_name CONSTANT VARCHAR2(30) := 'Raise_Business_Event';
580 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
581 l_return_status VARCHAR2(30);
582 l_msg_count NUMBER;
583 l_msg_data VARCHAR2(4000);
584 l_module CONSTANT VARCHAR2(100) := 'dpp.plsql.DPP_BUSINESSEVENTS_PVT.RAISE_BUSINESS_EVENT';
585
586 l_input_xml CLOB;
587 l_queryCtx dbms_xmlquery.ctxType;
588 l_table_count NUMBER;
589 l_row_count NUMBER := 0;
590 l_exe_dtl_id NUMBER;
591 l_user_id NUMBER := FND_GLOBAL.USER_ID;
592 l_user_name VARCHAR2(150);
593 l_login_id NUMBER := FND_GLOBAL.LOGIN_ID;
594 l_claim_type_flag VARCHAR2(240);
595 l_status_code CONSTANT VARCHAR2(20) := '''PENDING_CLOSE''';
596 l_claim_hdr_amt NUMBER;
597 l_cost_adj_acct NUMBER;
598 dtl_price_change VARCHAR2(50);
599 sup_trd_prf_price_change VARCHAR2(50);
600 dtla_price_change VARCHAR2(50);
601 l_price_change_flag VARCHAR2(20);
602 l_trx_currency VARCHAR2(15);
603
604 L_CANCEL VARCHAR2(10) DEFAULT 'FALSE';
605
606 BEGIN
607 --------------------- initialize -----------------------
608 SAVEPOINT Raise_Business_Event;
609
610 IF NOT FND_API.Compatible_API_Call (
611 l_api_version,
612 p_api_version,
613 l_api_name,
614 G_PKG_NAME)
615 THEN
616 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
617 END IF;
618 -- Initialize message list if p_init_msg_list is set to TRUE.
619 IF FND_API.to_Boolean( p_init_msg_list )
620 THEN
621 FND_MSG_PUB.initialize;
622 END IF;
623
624 -- Debug Message
625 DPP_UTILITY_PVT.debug_message(FND_LOG.LEVEL_PROCEDURE, l_module, 'Public API: ' || l_api_name || 'start');
626
627 -- Initialize API return status to sucess
628 x_return_status := FND_API.g_ret_sts_success;
629
630 --Get a unique value for the execution detail id
631 BEGIN
632 SELECT DPP_EXECUTION_DETAIL_ID_SEQ.nextval
633 INTO l_exe_dtl_id
634 FROM dual;
635
636 EXCEPTION
637 WHEN NO_DATA_FOUND THEN
638 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
639 fnd_message.set_token('ROUTINE', 'DPP_BUSINESSEVENTS_PVT');
640 fnd_message.set_token('ERRNO', sqlcode);
641 fnd_message.set_token('REASON', 'EXECUTION DETAIL ID NOT FOUND');
642 FND_MSG_PUB.add;
643 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
644 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
645 END IF;
646 RAISE FND_API.g_exc_error;
647 WHEN OTHERS THEN
648 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
649 fnd_message.set_token('ROUTINE', 'DPP_BUSINESSEVENTS_PVT');
650 fnd_message.set_token('ERRNO', sqlcode);
651 fnd_message.set_token('REASON', sqlerrm);
652 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
653 FND_MESSAGE.set_name('DPP', 'DPP_BUSEVT_INVALID_EXE_DET_ID');
654 fnd_message.set_token('SEQ_NAME', 'DPP_EXECUTION_DETAIL_ID_SEQ');
655 FND_MSG_PUB.add;
656 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
657 END IF;
658 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
659 END;
660
661 DPP_UTILITY_PVT.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Execution Detail ID : ' || l_exe_dtl_id ||' For Transaction ID :'||p_txn_hdr_rec.Transaction_Header_ID);
662 DPP_UTILITY_PVT.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Process Code : ' || p_txn_hdr_rec.process_code);
663
664 --Get the user name corresponding to the user id
665 BEGIN
666 SELECT user_name
667 INTO l_user_name
668 FROM fnd_user
669 WHERE user_id = l_user_id ;
670
671 EXCEPTION
672 WHEN NO_DATA_FOUND THEN
673 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
674 fnd_message.set_token('ROUTINE', 'DPP_BUSINESSEVENTS_PVT');
675 fnd_message.set_token('ERRNO', sqlcode);
676 fnd_message.set_token('REASON', 'INVALID USER');
677 FND_MSG_PUB.add;
681 RAISE FND_API.g_exc_error;
678 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
679 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
680 END IF;
682 WHEN OTHERS THEN
683 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
684 fnd_message.set_token('ROUTINE', 'DPP_BUSINESSEVENTS_PVT');
685 fnd_message.set_token('ERRNO', sqlcode);
686 fnd_message.set_token('REASON', sqlerrm);
687 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
688 END;
689
690 l_user_name := ''''||l_user_name||'''';
691
692 IF p_txn_hdr_rec.process_code LIKE 'CNCL%'
693 THEN
694 L_CANCEL:='TRUE';
695 END IF;
696
697 IF p_txn_hdr_rec.process_code = 'DSTRINVCL'
698 OR p_txn_hdr_rec.process_code = 'CUSTINVCL'
699 OR p_txn_hdr_rec.process_code = 'CUSTCL' THEN
700
701 IF p_txn_hdr_rec.claim_type_flag IS NULL THEN
702 --No claim type flag has been sent
703 FND_MESSAGE.set_name('DPP', 'DPP_BUSEVT_INVALID_CLAIM_TYPE');
704 FND_MSG_PUB.add;
705 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
706 RAISE FND_API.g_exc_error;
707 ELSE
708 --Concatinate the claim flag with necessary quotes to generate xml data
709 l_claim_type_flag := ''''||p_txn_hdr_rec.claim_type_flag||'''';
710 DPP_UTILITY_PVT.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Claim Type Flag : ' || l_claim_type_flag);
711 END IF;
712 --Delete the existing rows from the DPP_TRANSACTION_LINES_GT table
713 DELETE FROM DPP_TRANSACTION_LINES_GT;
714 --Check if any line id has been passed and insert into the table DPP_TRANSACTION_LINES_GT
715 l_table_count := p_txn_line_id.COUNT;
716 --Changed the condition to check the claim source instead of the table count since array cannot be NULL from UI
717 IF p_txn_hdr_rec.claim_creation_source <> 'EXEDTLS' THEN
718 IF l_table_count > 0 THEN
719 FOR i IN p_txn_line_id.FIRST..p_txn_line_id.LAST LOOP
720 BEGIN
721 INSERT INTO DPP_TRANSACTION_LINES_GT(transaction_header_id,
722 transaction_line_id
723 )
724 VALUES(p_txn_hdr_rec.Transaction_Header_ID,
725 p_txn_line_id(i)
726 );
727 EXCEPTION
728 WHEN OTHERS THEN
729 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
730 fnd_message.set_token('ROUTINE', 'DPP_BUSINESSEVENTS_PVT');
731 fnd_message.set_token('ERRNO', sqlcode);
732 fnd_message.set_token('REASON', sqlerrm);
733 FND_MSG_PUB.add;
734 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
735 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
736 END IF;
737 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
738 END;
739 END LOOP;
740 ELSE
741 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
742 fnd_message.set_token('ROUTINE', 'DPP_BUSINESSEVENTS_PVT');
743 fnd_message.set_token('ERRNO', sqlcode);
744 fnd_message.set_token('REASON', 'Transaction Line Id is required for the API');
745 FND_MSG_PUB.add;
746 RAISE FND_API.G_EXC_ERROR;
747 END IF; --l_table_count
748 ELSE
749 IF p_txn_hdr_rec.claim_type_flag = 'SUPP_DSTR_CL' THEN
750 BEGIN
751 INSERT INTO DPP_TRANSACTION_LINES_GT(transaction_header_id,
752 transaction_line_id)
753 SELECT transaction_header_id,
754 transaction_line_id
755 FROM dpp_transaction_lines_all
756 WHERE transaction_header_id = p_txn_hdr_rec.Transaction_Header_ID;
757 EXCEPTION
758 WHEN OTHERS THEN
759 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
760 fnd_message.set_token('ROUTINE', 'DPP_BUSINESSEVENTS_PVT');
761 fnd_message.set_token('ERRNO', sqlcode);
762 fnd_message.set_token('REASON', sqlerrm);
763 FND_MSG_PUB.add;
764 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
765 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
766 END IF;
767 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
768 END;
769 ELSE
770 BEGIN
771 INSERT INTO DPP_TRANSACTION_LINES_GT(transaction_header_id,
772 transaction_line_id)
773 SELECT transaction_header_id,
774 customer_inv_line_id
775 FROM dpp_customer_claims_all
776 WHERE transaction_header_id = p_txn_hdr_rec.Transaction_Header_ID;
777 EXCEPTION
778 WHEN OTHERS THEN
779 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
780 fnd_message.set_token('ROUTINE', 'DPP_BUSINESSEVENTS_PVT');
781 fnd_message.set_token('ERRNO', sqlcode);
782 fnd_message.set_token('REASON', sqlerrm);
783 FND_MSG_PUB.add;
787 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
784 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
785 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
786 END IF;
788 END;
789 END IF;
790 END IF;
791
792 END IF;
793
794 IF p_txn_hdr_rec.process_code = 'UPDTLP' THEN
795 --Generate the Input Xml required for the Business Event -- UpdateListPrice
796 l_queryCtx := dbms_xmlquery.newContext('SELECT Transaction_header_id,
797 transaction_number,
798 org_id,
799 Vendor_id,'
800 ||l_user_name|| ' user_name, '
801 ||l_user_id||'user_id,'
802 ||l_exe_dtl_id||'Execution_detail_id,
803 CURSOR(select transaction_line_id,
804 inventory_item_id,
805 supplier_new_price new_price,
806 UOM,
807 headers.trx_currency currency
808 FROM dpp_transaction_lines_all lines
809 WHERE headers.Transaction_header_id = lines.Transaction_header_id
810 AND nvl(lines.UPDATE_ITEM_LIST_PRICE,''N'') = ''N'') LINES
811 FROM dpp_transaction_headers_all headers
812 WHERE headers.Transaction_header_id = ' ||p_txn_hdr_rec.Transaction_Header_ID||
813 'AND EXISTS (SELECT Transaction_header_id
814 FROM dpp_transaction_lines_all
815 WHERE Transaction_header_id = '||p_txn_hdr_rec.Transaction_Header_ID||'
816 AND nvl(UPDATE_ITEM_LIST_PRICE,''N'') = ''N'')'
817 );
818 dbms_xmlquery.setRowTag(l_queryCtx
819 , 'HEADER'
820 );
821 dbms_xmlquery.setRowSetTag(l_queryCtx
822 ,'TRANSACTION'
823 );
824 l_input_xml := dbms_xmlquery.getXml(l_queryCtx);
825 --Check if the query returns any rows
826 l_row_count := dbms_xmlquery.getNumRowsProcessed(l_queryCtx);
827 dbms_xmlquery.closeContext(l_queryCtx);
828
829 IF l_row_count >0 THEN
830 --Update the line status to PENDING in the DPP_TRANSACTION_LINES_ALL table
831 UPDATE DPP_TRANSACTION_LINES_ALL
832 SET update_item_list_price = 'P',
833 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER +1,
834 last_updated_by = l_user_id,
835 last_update_date = sysdate,
836 last_update_login = l_login_id
837 WHERE transaction_header_id = p_txn_hdr_rec.Transaction_Header_ID
838 AND nvl(update_item_list_price, 'N') = 'N';
839
840 IF SQL%ROWCOUNT = 0 THEN
841 l_return_status := FND_API.G_RET_STS_ERROR;
842 DPP_UTILITY_PVT.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Unable to Update the column update_item_list_price in DPP_TRANSACTION_LINES_ALL Table');
843 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
844 END IF;
845
846 END IF;
847 ELSIF p_txn_hdr_rec.process_code = 'OUTPL' OR p_txn_hdr_rec.process_code = 'CNCL_OUTPL' THEN
848 --Generate the Input Xml required for the Business-- Event Send Notification for Outbound Price lists
849 l_queryCtx := dbms_xmlquery.newContext('SELECT Transaction_header_id,
850 '''||L_CANCEL||''' CANCEL_TXN,
851 Transaction_number,
852 to_char(Effective_start_date,''YYYY-MM-DD'') Effectivity_date,
853 org_id,
854 Vendor_id,
855 Vendor_site_id,'
856 ||l_user_name|| ' user_name, '
857 ||l_user_id||'user_id,'
858 ||l_exe_dtl_id||'Execution_detail_id,
859 CURSOR(select transaction_line_id,
860 inventory_item_id,
861 supplier_new_price new_price,
862 headers.trx_currency currency
863 FROM dpp_transaction_lines_all lines
864 WHERE headers.Transaction_header_id = lines.Transaction_header_id
865 AND nvl(lines.NOTIFY_OUTBOUND_PRICELIST,''N'') <> ''D'') LINES
866 FROM dpp_transaction_headers_all headers
867 WHERE headers.Transaction_header_id = ' ||p_txn_hdr_rec.Transaction_Header_ID||
868 'AND EXISTS (SELECT Transaction_header_id
869 FROM dpp_transaction_lines_all
870 WHERE Transaction_header_id = '||p_txn_hdr_rec.Transaction_Header_ID||'
871 AND nvl(NOTIFY_OUTBOUND_PRICELIST,''N'') <> ''D'')'
872 );
873 dbms_xmlquery.setRowTag(l_queryCtx
874 , 'HEADER'
875 );
876 dbms_xmlquery.setRowSetTag(l_queryCtx
880 --Check if the query returns any rows
877 ,'TRANSACTION'
878 );
879 l_input_xml := dbms_xmlquery.getXml(l_queryCtx);
881 l_row_count := dbms_xmlquery.getNumRowsProcessed(l_queryCtx);
882 dbms_xmlquery.closeContext(l_queryCtx);
883
884 IF l_row_count >0 THEN
885 --Update the line status to PENDING in the DPP_TRANSACTION_LINES_ALL table
886 UPDATE DPP_TRANSACTION_LINES_ALL
887 SET NOTIFY_OUTBOUND_PRICELIST = 'P',
888 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER +1,
889 last_updated_by = l_user_id,
890 last_update_date = sysdate,
891 last_update_login = l_login_id
892 WHERE transaction_header_id = p_txn_hdr_rec.Transaction_Header_ID
893 AND nvl(NOTIFY_OUTBOUND_PRICELIST,'N') <> 'D';
894
895 IF SQL%ROWCOUNT = 0 THEN
896 l_return_status := FND_API.G_RET_STS_ERROR;
897 DPP_UTILITY_PVT.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Unable to Update the column NOTIFY_OUTBOUND_PRICELIST in DPP_TRANSACTION_LINES_ALL Table');
898 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
899 END IF;
900 END IF;
901
902 ELSIF p_txn_hdr_rec.process_code = 'UPDCLM' THEN
903 --Calculate the value of the claim header amount
904 BEGIN
905 SELECT trx_currency
906 INTO l_trx_currency
907 FROM dpp_transaction_headers_all
908 WHERE transaction_header_id = p_txn_hdr_rec.transaction_header_id;
909
910 SELECT SUM(DPP_UTILITY_PVT.CurrRound(nvl(claim_amount,0), l_trx_currency))
911 INTO l_claim_hdr_amt
912 FROM dpp_transaction_lines_all
913 WHERE transaction_header_id = p_txn_hdr_rec.Transaction_Header_ID
914 AND to_number(supp_dist_claim_id) = p_txn_hdr_rec.claim_id;
915 EXCEPTION
916 WHEN NO_DATA_FOUND THEN
917 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
918 fnd_message.set_token('ROUTINE', 'DPP_BUSINESSEVENTS_PVT');
919 fnd_message.set_token('ERRNO', sqlcode);
920 fnd_message.set_token('REASON', 'INVALID CLAIM HEADER AMOUNT');
921 FND_MSG_PUB.add;
922 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
923 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
924 END IF;
925 RAISE FND_API.G_EXC_ERROR;
926 WHEN OTHERS THEN
927 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
928 fnd_message.set_token('ROUTINE', 'DPP_BUSINESSEVENTS_PVT');
929 fnd_message.set_token('ERRNO', sqlcode);
930 fnd_message.set_token('REASON', sqlerrm);
931 FND_MSG_PUB.add;
932 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
933 FND_MESSAGE.set_name('DPP', 'DPP_BUSEVT_INVALID_CLAIM_AMT');
934 FND_MSG_PUB.add;
935 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
936 END IF;
937 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
938 END;
939 --Generate the Input Xml required for the Business Event --Update Claim
940 l_queryCtx := dbms_xmlquery.newContext('SELECT Transaction_header_id,
941 Transaction_number,
942 Vendor_id,
943 org_id,
944 Vendor_site_id,'
945 ||l_user_name|| ' user_name, '
946 ||l_user_id||'user_id,'
947 ||l_exe_dtl_id||'Execution_detail_id,'
948 ||l_status_code||'Status_code,
949 trx_currency,'
950 ||l_claim_hdr_amt||'Claim_amount,'
951 ||p_txn_hdr_rec.claim_id||'claim_id,
952 CURSOR(select transaction_line_id,
953 inventory_item_id,
954 claim_amount claim_amount,
955 approved_inventory claim_quantity,
956 UOM,'
957 ||p_txn_hdr_rec.claim_id||'claim_id,
958 headers.trx_currency currency
959 FROM dpp_transaction_lines_all lines
960 WHERE headers.Transaction_header_id = lines.Transaction_header_id
961 AND lines.supp_dist_claim_id = '||p_txn_hdr_rec.claim_id||') LINES
962 FROM dpp_transaction_headers_all headers
963 WHERE headers.Transaction_header_id = ' ||p_txn_hdr_rec.Transaction_Header_ID
964 );
965 dbms_xmlquery.setRowTag(l_queryCtx
966 , 'HEADER'
967 );
968 dbms_xmlquery.setRowSetTag(l_queryCtx
969 ,'TRANSACTION'
970 );
971 l_input_xml := dbms_xmlquery.getXml(l_queryCtx);
972 --Check if the query returns any rows
973 l_row_count := dbms_xmlquery.getNumRowsProcessed(l_queryCtx);
974 dbms_xmlquery.closeContext(l_queryCtx);
975
976 ELSIF p_txn_hdr_rec.process_code = 'INPL' OR p_txn_hdr_rec.process_code = 'CNCL_INPL' THEN
977 --Generate the Input Xml required for the Business Event -- Send Notification for Inbound Price lists
978 l_queryCtx := dbms_xmlquery.newContext('SELECT Transaction_header_id,
982 org_id,
979 '''||L_CANCEL||''' CANCEL_TXN,
980 Transaction_number,
981 to_char(Effective_start_date,''YYYY-MM-DD'') Effectivity_date,
983 Vendor_id,
984 Vendor_site_id,'
985 ||l_user_name|| ' user_name, '
986 ||l_user_id||'user_id,'
987 ||l_exe_dtl_id||'Execution_detail_id,
988 CURSOR(select transaction_line_id,
989 inventory_item_id,
990 supplier_new_price new_price,
991 headers.trx_currency currency
992 FROM dpp_transaction_lines_all lines
993 WHERE headers.Transaction_header_id = lines.Transaction_header_id
994 AND nvl(lines.NOTIFY_INBOUND_PRICELIST,''N'') <> ''D'') LINES
995 FROM dpp_transaction_headers_all headers
996 WHERE headers.Transaction_header_id = ' ||p_txn_hdr_rec.Transaction_Header_ID||
997 'AND EXISTS (SELECT Transaction_header_id
998 FROM dpp_transaction_lines_all
999 WHERE Transaction_header_id = '||p_txn_hdr_rec.Transaction_Header_ID||'
1000 AND nvl(NOTIFY_INBOUND_PRICELIST,''N'') <> ''D'')'
1001 );
1002 dbms_xmlquery.setRowTag(l_queryCtx
1003 , 'HEADER'
1004 );
1005 dbms_xmlquery.setRowSetTag(l_queryCtx
1006 ,'TRANSACTION'
1007 );
1008 l_input_xml := dbms_xmlquery.getXml(l_queryCtx);
1009 --Check if the query returns any rows
1010 l_row_count := dbms_xmlquery.getNumRowsProcessed(l_queryCtx);
1011 dbms_xmlquery.closeContext(l_queryCtx);
1012
1013 IF l_row_count >0 THEN
1014 --Update the line status to PENDING in the DPP_TRANSACTION_LINES_ALL table
1015 UPDATE DPP_TRANSACTION_LINES_ALL
1016 SET NOTIFY_INBOUND_PRICELIST = 'P',
1017 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER +1,
1018 last_updated_by = l_user_id,
1019 last_update_date = sysdate,
1020 last_update_login = l_login_id
1021 WHERE transaction_header_id = p_txn_hdr_rec.Transaction_Header_ID
1022 AND nvl(NOTIFY_INBOUND_PRICELIST,'N') <> 'D';
1023
1024 IF SQL%ROWCOUNT = 0 THEN
1025 l_return_status := FND_API.G_RET_STS_ERROR;
1026 DPP_UTILITY_PVT.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Unable to Update the column NOTIFY_INBOUND_PRICELIST in DPP_TRANSACTION_LINES_ALL Table');
1027 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1028 END IF;
1029 END IF;
1030
1031 ELSIF p_txn_hdr_rec.process_code = 'PROMO' OR p_txn_hdr_rec.process_code = 'CNCL_PROMO' THEN
1032 --Generate the Input Xml required for the Business Event -- Send Notification for Offers
1033 l_queryCtx := dbms_xmlquery.newContext('SELECT Transaction_header_id,
1034 '''||L_CANCEL||''' CANCEL_TXN,
1035 Transaction_number,
1036 to_char(Effective_start_date,''YYYY-MM-DD'') Effectivity_date,
1037 org_id,
1038 Vendor_id,
1039 Vendor_site_id,'
1040 ||l_user_name|| ' user_name, '
1041 ||l_user_id||'user_id,'
1042 ||l_exe_dtl_id||'Execution_detail_id,
1043 CURSOR(select transaction_line_id,
1044 inventory_item_id,
1045 supplier_new_price new_price,
1046 headers.trx_currency currency
1047 FROM dpp_transaction_lines_all lines
1048 WHERE headers.Transaction_header_id = lines.Transaction_header_id
1049 AND nvl(lines.NOTIFY_PROMOTIONS_PRICELIST,''N'') <> ''D'') LINES
1050 FROM dpp_transaction_headers_all headers
1051 WHERE headers.Transaction_header_id = ' ||p_txn_hdr_rec.Transaction_Header_ID||
1052 'AND EXISTS (SELECT Transaction_header_id
1053 FROM dpp_transaction_lines_all
1054 WHERE Transaction_header_id = '||p_txn_hdr_rec.Transaction_Header_ID||'
1055 AND nvl(NOTIFY_PROMOTIONS_PRICELIST,''N'') <> ''D'')'
1056 );
1057 dbms_xmlquery.setRowTag(l_queryCtx
1058 , 'HEADER'
1059 );
1060 dbms_xmlquery.setRowSetTag(l_queryCtx
1061 ,'TRANSACTION'
1062 );
1063 l_input_xml := dbms_xmlquery.getXml(l_queryCtx);
1064 --Check if the query returns any rows
1065 l_row_count := dbms_xmlquery.getNumRowsProcessed(l_queryCtx);
1066 dbms_xmlquery.closeContext(l_queryCtx);
1067
1068 IF l_row_count >0 THEN
1072 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER +1,
1069 --Update the line status to PENDING in the DPP_TRANSACTION_LINES_ALL table
1070 UPDATE DPP_TRANSACTION_LINES_ALL
1071 SET NOTIFY_PROMOTIONS_PRICELIST = 'P',
1073 last_updated_by = l_user_id,
1074 last_update_date = sysdate,
1075 last_update_login = l_login_id
1076 WHERE transaction_header_id = p_txn_hdr_rec.Transaction_Header_ID
1077 AND nvl(NOTIFY_PROMOTIONS_PRICELIST,'N') <> 'D';
1078
1079 IF SQL%ROWCOUNT = 0 THEN
1080 l_return_status := FND_API.G_RET_STS_ERROR;
1081 DPP_UTILITY_PVT.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Unable to Update the column NOTIFY_PROMOTIONS_PRICELIST in DPP_TRANSACTION_LINES_ALL Table');
1082 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1083 END IF;
1084 END IF;
1085 ELSIF p_txn_hdr_rec.process_code = 'INVC' THEN
1086 --Retrieve the gl_cost_adjustment_acct
1087 BEGIN
1088 SELECT ostp.gl_cost_adjustment_acct
1089 INTO l_cost_adj_acct
1090 FROM ozf_supp_trd_prfls_all ostp,
1091 dpp_transaction_headers_all dtha
1092 WHERE ostp.supplier_id = to_number(dtha.vendor_id)
1093 AND ostp.supplier_site_id = to_number(dtha.vendor_site_id)
1094 AND ostp.org_id = to_number(dtha.org_id)
1095 AND dtha.transaction_header_id = p_txn_hdr_rec.Transaction_Header_ID;
1096 EXCEPTION
1097 WHEN NO_DATA_FOUND THEN
1098 l_cost_adj_acct := null;
1099 WHEN OTHERS THEN
1100 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
1101 fnd_message.set_token('ROUTINE', 'DPP_BUSINESSEVENTS_PVT');
1102 fnd_message.set_token('ERRNO', sqlcode);
1103 fnd_message.set_token('REASON', sqlerrm);
1104 FND_MSG_PUB.add;
1105 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1106 FND_MESSAGE.set_name('DPP', 'DPP_BUSEVT_INVALID_COSTADJACC');
1107 FND_MSG_PUB.add;
1108 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
1109 END IF;
1110 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1111 END;
1112 IF l_cost_adj_acct IS NULL THEN
1113 BEGIN
1114 SELECT osp.gl_cost_adjustment_acct
1115 INTO l_cost_adj_acct
1116 FROM ozf_sys_parameters osp,
1117 dpp_transaction_headers_all dtha
1118 WHERE osp.org_id = to_number(dtha.org_id)
1119 AND dtha.transaction_header_id = p_txn_hdr_rec.Transaction_Header_ID;
1120 EXCEPTION
1121 WHEN NO_DATA_FOUND THEN
1122 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
1123 fnd_message.set_token('ROUTINE', 'DPP_BUSINESSEVENTS_PVT');
1124 fnd_message.set_token('ERRNO', sqlcode);
1125 fnd_message.set_token('REASON', 'COST ADJUSTMENT ACCOUNT NOT FOUND');
1126 FND_MSG_PUB.add;
1127 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1128 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
1129 END IF;
1130 RAISE FND_API.G_EXC_ERROR;
1131 WHEN OTHERS THEN
1132 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
1133 fnd_message.set_token('ROUTINE', 'DPP_BUSINESSEVENTS_PVT');
1134 fnd_message.set_token('ERRNO', sqlcode);
1135 fnd_message.set_token('REASON', sqlerrm);
1136 FND_MSG_PUB.add;
1137 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1138 FND_MESSAGE.set_name('DPP', 'DPP_BUSEVT_INVALID_COSTADJACC');
1139 FND_MSG_PUB.add;
1140 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
1141 END IF;
1142 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1143 END;
1144 END IF;
1145
1146 IF l_cost_adj_acct IS NOT NULL THEN
1147 DPP_UTILITY_PVT.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Cost Adjustment Account : ' ||l_cost_adj_acct);
1148
1149 --Generate the Input Xml required for the Business Event -- Update Inventory Costing
1150 l_queryCtx := dbms_xmlquery.newContext('SELECT Transaction_header_id,
1151 Transaction_number,
1152 org_id,'
1153 ||l_user_name|| ' user_name, '
1154 ||l_user_id||'user_id,'
1155 ||l_exe_dtl_id||'Execution_detail_id,'
1156 ||l_cost_adj_acct||'gl_cost_adjustment_acct,
1157 CURSOR(select transaction_line_id,
1158 inventory_item_id,
1159 supplier_new_price new_price,
1160 headers.trx_currency currency,
1161 UOM,
1162 price_change
1163 FROM dpp_transaction_lines_all lines
1164 WHERE headers.Transaction_header_id = lines.Transaction_header_id
1165 AND nvl(lines.UPDATE_INVENTORY_COSTING,''N'') = ''N'') LINES
1166 FROM dpp_transaction_headers_all headers
1167 WHERE headers.Transaction_header_id = ' ||p_txn_hdr_rec.Transaction_Header_ID||
1168 'AND EXISTS (SELECT Transaction_header_id
1169 FROM dpp_transaction_lines_all
1170 WHERE Transaction_header_id = '||p_txn_hdr_rec.Transaction_Header_ID||'
1171 AND nvl(UPDATE_INVENTORY_COSTING,''N'') = ''N'')'
1175 );
1172 );
1173 dbms_xmlquery.setRowTag(l_queryCtx
1174 , 'HEADER'
1176 dbms_xmlquery.setRowSetTag(l_queryCtx
1177 ,'TRANSACTION'
1178 );
1179 l_input_xml := dbms_xmlquery.getXml(l_queryCtx);
1180 --Check if the query returns any rows
1181 l_row_count := dbms_xmlquery.getNumRowsProcessed(l_queryCtx);
1182 dbms_xmlquery.closeContext(l_queryCtx);
1183
1184 IF l_row_count >0 THEN
1185 --Update the line status to PENDING in the DPP_TRANSACTION_LINES_ALL table
1186 UPDATE DPP_TRANSACTION_LINES_ALL
1187 SET UPDATE_INVENTORY_COSTING = 'P',
1188 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER +1,
1189 last_updated_by = l_user_id,
1190 last_update_date = sysdate,
1191 last_update_login = l_login_id
1192 WHERE transaction_header_id = p_txn_hdr_rec.Transaction_Header_ID
1193 AND nvl(UPDATE_INVENTORY_COSTING,'N') = 'N';
1194
1195 IF SQL%ROWCOUNT = 0 THEN
1196 l_return_status := FND_API.G_RET_STS_ERROR;
1197 DPP_UTILITY_PVT.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Unable to Update the column UPDATE_INVENTORY_COSTING in DPP_TRANSACTION_LINES_ALL Table');
1198 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1199 END IF;
1200 END IF;
1201 ELSE
1202 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
1203 fnd_message.set_token('ROUTINE', 'DPP_BUSINESSEVENTS_PVT');
1204 fnd_message.set_token('ERRNO', sqlcode);
1205 fnd_message.set_token('REASON', 'COST ADJUSTMENT ACCOUNT IS NULL');
1206 FND_MSG_PUB.add;
1207 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1208 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
1209 END IF;
1210 RAISE FND_API.G_EXC_ERROR;
1211 END IF;
1212
1213 ELSIF p_txn_hdr_rec.process_code = 'NTFYPO' OR p_txn_hdr_rec.process_code = 'CNCL_NTFYPO' THEN
1214 --Generate the Input Xml required for the Business-- Event Send Notifications for Purchase Orders
1215 l_queryCtx := dbms_xmlquery.newContext('SELECT Transaction_header_id,
1216 '''||L_CANCEL||''' CANCEL_TXN,
1217 Transaction_number,
1218 org_id,
1219 to_char(Effective_start_date,''YYYY-MM-DD'') Effectivity_date,
1220 vendor_site_id,
1221 Vendor_id,'
1222 ||l_user_name|| ' user_name, '
1223 ||l_user_id||'user_id,'
1224 ||l_exe_dtl_id||'Execution_detail_id,
1225 CURSOR(select transaction_line_id,
1226 inventory_item_id,
1227 supplier_new_price new_price,
1228 UOM,
1229 headers.trx_currency currency
1230 FROM dpp_transaction_lines_all lines
1231 WHERE headers.Transaction_header_id = lines.Transaction_header_id
1232 AND nvl(lines.NOTIFY_PURCHASING_DOCS,''N'') <> ''D'') LINES
1233 FROM dpp_transaction_headers_all headers
1234 WHERE headers.Transaction_header_id = ' ||p_txn_hdr_rec.Transaction_Header_ID||
1235 'AND EXISTS (SELECT Transaction_header_id
1236 FROM dpp_transaction_lines_all
1237 WHERE Transaction_header_id = '||p_txn_hdr_rec.Transaction_Header_ID||'
1238 AND nvl(NOTIFY_PURCHASING_DOCS,''N'') <> ''D'')'
1239 );
1240 dbms_xmlquery.setRowTag(l_queryCtx
1241 , 'HEADER'
1242 );
1243 dbms_xmlquery.setRowSetTag(l_queryCtx
1244 ,'TRANSACTION'
1245 );
1246 l_input_xml := dbms_xmlquery.getXml(l_queryCtx);
1247 --Check if the query returns any rows
1248 l_row_count := dbms_xmlquery.getNumRowsProcessed(l_queryCtx);
1249 dbms_xmlquery.closeContext(l_queryCtx);
1250
1251 IF l_row_count >0 THEN
1252 --Update the line status to PENDING in the DPP_TRANSACTION_LINES_ALL table
1253 UPDATE DPP_TRANSACTION_LINES_ALL
1254 SET notify_purchasing_docs = 'P',
1255 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER +1,
1256 last_updated_by = l_user_id,
1257 last_update_date = sysdate,
1258 last_update_login = l_login_id
1259 WHERE transaction_header_id = p_txn_hdr_rec.Transaction_Header_ID
1260 AND nvl(notify_purchasing_docs ,'N') <> 'D';
1261
1262 IF SQL%ROWCOUNT = 0 THEN
1263 l_return_status := FND_API.G_RET_STS_ERROR;
1264 DPP_UTILITY_PVT.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Unable to Update the column notify_purchasing_docs in DPP_TRANSACTION_LINES_ALL Table');
1265 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1266 END IF;
1267 END IF;
1268 ELSIF p_txn_hdr_rec.process_code = 'UPDTPO' THEN
1269 --Generate the Input Xml required for the Business Event -- Update Purchasing - Purchase Orders
1270 l_queryCtx := dbms_xmlquery.newContext('SELECT Transaction_header_id,
1271 Transaction_number,
1272 org_id,
1276 ||l_exe_dtl_id||'Execution_detail_id,
1273 Vendor_id,'
1274 ||l_user_name|| ' user_name, '
1275 ||l_user_id||'user_id,'
1277 CURSOR(select transaction_line_id,
1278 inventory_item_id,
1279 supplier_new_price new_price,
1280 UOM,
1281 headers.trx_currency currency
1282 FROM dpp_transaction_lines_all lines
1283 WHERE headers.Transaction_header_id = lines.Transaction_header_id
1284 AND nvl(lines.UPDATE_PURCHASING_DOCS,''N'') = ''N'') LINES
1285 FROM dpp_transaction_headers_all headers
1286 WHERE headers.Transaction_header_id = ' ||p_txn_hdr_rec.Transaction_Header_ID||
1287 'AND EXISTS (SELECT Transaction_header_id
1288 FROM dpp_transaction_lines_all
1289 WHERE Transaction_header_id = '||p_txn_hdr_rec.Transaction_Header_ID||'
1290 AND nvl(UPDATE_PURCHASING_DOCS,''N'') = ''N'')'
1291 );
1292 dbms_xmlquery.setRowTag(l_queryCtx
1293 , 'HEADER'
1294 );
1295 dbms_xmlquery.setRowSetTag(l_queryCtx
1296 ,'TRANSACTION'
1297 );
1298 l_input_xml := dbms_xmlquery.getXml(l_queryCtx);
1299 --Check if the query returns any rows
1300 l_row_count := dbms_xmlquery.getNumRowsProcessed(l_queryCtx);
1301 dbms_xmlquery.closeContext(l_queryCtx);
1302
1303 IF l_row_count >0 THEN
1304 --Update the line status to PENDING in the DPP_TRANSACTION_LINES_ALL table
1305 UPDATE DPP_TRANSACTION_LINES_ALL
1306 SET UPDATE_PURCHASING_DOCS = 'P',
1307 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER +1,
1308 last_updated_by = l_user_id,
1309 last_update_date = sysdate,
1310 last_update_login = l_login_id
1311 WHERE transaction_header_id = p_txn_hdr_rec.Transaction_Header_ID
1312 AND nvl(UPDATE_PURCHASING_DOCS ,'N')= 'N';
1313
1314 IF SQL%ROWCOUNT = 0 THEN
1315 l_return_status := FND_API.G_RET_STS_ERROR;
1316 DPP_UTILITY_PVT.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Unable to Update the column UPDATE_PURCHASING_DOCS in DPP_TRANSACTION_LINES_ALL Table');
1317 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1318 END IF;
1319 END IF;
1320 ELSIF p_txn_hdr_rec.process_code = 'DSTRINVCL' THEN
1321 --Added code for DPP Price Increase Enhancement
1322 BEGIN
1323 SELECT nvl(create_claim_price_increase,'N')
1324 INTO l_price_change_flag
1325 FROM ozf_supp_trd_prfls_all ostp,
1326 dpp_transaction_headers_all dtha
1327 WHERE ostp.supplier_id = to_number(dtha.vendor_id)
1328 AND ostp.supplier_site_id = to_number(dtha.vendor_site_id)
1329 AND ostp.org_id = to_number(dtha.org_id)
1330 AND dtha.transaction_header_id = p_txn_hdr_rec.Transaction_Header_ID;
1331 EXCEPTION
1332 WHEN NO_DATA_FOUND THEN
1333 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
1334 fnd_message.set_token('ROUTINE', 'DPP_BUSINESSEVENTS_PVT');
1335 fnd_message.set_token('ERRNO', sqlcode);
1336 fnd_message.set_token('REASON', 'SUPPLIER TRADE PROFILE IS NOT FOUND'); --To be modified
1337 FND_MSG_PUB.add;
1338 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1339 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
1340 END IF;
1341 RAISE FND_API.g_exc_error;
1342 WHEN OTHERS THEN
1343 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
1344 fnd_message.set_token('ROUTINE', 'DPP_BUSINESSEVENTS_PVT');
1345 fnd_message.set_token('ERRNO', sqlcode);
1346 fnd_message.set_token('REASON', sqlerrm);
1347 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1348 FND_MESSAGE.set_name('DPP', 'DPP_BUSEVT_INVALID_EXE_DET_ID'); --To be modified
1349 fnd_message.set_token('SEQ_NAME', 'DPP_EXECUTION_DETAIL_ID_SEQ'); --To be modified
1350 FND_MSG_PUB.add;
1351 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
1352 END IF;
1353 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1354 END;
1355
1356 IF (l_price_change_flag = 'N') THEN
1357 dtl_price_change := 'nvl(dtl.price_change,0) > 0';
1358 dtla_price_change := 'nvl(dtla.price_change,0) > 0';
1359 ELSE
1360 dtl_price_change := 'nvl(dtl.price_change,0) <> 0';
1361 dtla_price_change := 'nvl(dtla.price_change,0) <> 0';
1362 END IF;
1363
1364 --Generate the Input Xml required for the Business Event -- Create On-Hand Inventory claim
1365 l_queryCtx := dbms_xmlquery.newContext('SELECT headers.Transaction_header_id,
1366 headers.Transaction_number,
1367 headers.Vendor_id,
1368 headers.org_id,
1369 headers.Vendor_site_id,'
1370 ||l_user_name|| ' user_name, '
1371 ||l_user_id||'user_id,'
1372 ||l_exe_dtl_id||'Execution_detail_id,'
1376 dtl.inventory_item_id,
1373 ||l_claim_type_flag||'claim_type_flag,
1374 headers.trx_currency,
1375 CURSOR(select dtl.transaction_line_id,
1377 dtl.claim_amount claim_line_amount,
1378 dtl.approved_inventory CLAIM_QUANTITY,
1379 dtl.UOM,
1380 headers.trx_currency currency
1381 FROM dpp_transaction_lines_all dtl,
1382 DPP_TRANSACTION_LINES_GT dtlg
1383 WHERE headers.Transaction_header_id = dtl.Transaction_header_id
1384 AND dtl.transaction_line_id = dtlg.transaction_line_id
1385 AND '||dtl_price_change||'
1386 AND nvl(dtl.approved_inventory,0) > 0
1387 AND nvl(dtl.SUPP_DIST_CLAIM_STATUS,''N'') = ''N'') LINES
1388 FROM dpp_transaction_headers_all headers
1389 WHERE headers.Transaction_header_id = ' ||p_txn_hdr_rec.Transaction_Header_ID||
1390 'AND EXISTS (SELECT dtla.Transaction_header_id
1391 FROM dpp_transaction_lines_all dtla,
1392 DPP_TRANSACTION_LINES_GT dtg
1393 WHERE dtla.Transaction_header_id = '||p_txn_hdr_rec.Transaction_Header_ID||'
1394 AND dtla.transaction_line_id = dtg.transaction_line_id
1395 AND nvl(dtla.SUPP_DIST_CLAIM_STATUS,''N'') = ''N''
1396 AND '||dtla_price_change||'
1397 AND nvl(dtla.approved_inventory,0) > 0)'
1398 );
1399 dbms_xmlquery.setRowTag(l_queryCtx
1400 , 'HEADER'
1401 );
1402 dbms_xmlquery.setRowSetTag(l_queryCtx
1403 ,'TRANSACTION'
1404 );
1405 l_input_xml := dbms_xmlquery.getXml(l_queryCtx);
1406 --Check if the query returns any rows
1407 l_row_count := dbms_xmlquery.getNumRowsProcessed(l_queryCtx);
1408 dbms_xmlquery.closeContext(l_queryCtx);
1409
1410 IF l_row_count >0 THEN
1411 IF p_txn_hdr_rec.claim_creation_source = 'EXEDTLS' THEN
1412
1413 IF (l_price_change_flag = 'N') THEN -- Only Price Decrease Lines
1414 --Update the line status to PENDING in the DPP_TRANSACTION_LINES_ALL table
1415 UPDATE DPP_TRANSACTION_LINES_ALL
1416 SET SUPP_DIST_CLAIM_STATUS = 'P',
1417 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER +1,
1418 last_updated_by = l_user_id,
1419 last_update_date = sysdate,
1420 last_update_login = l_login_id
1421 WHERE transaction_header_id = p_txn_hdr_rec.Transaction_Header_ID
1422 AND nvl(SUPP_DIST_CLAIM_STATUS ,'N')= 'N'
1423 AND nvl(approved_inventory,0) > 0
1424 AND nvl(price_change,0) > 0;
1425
1426 IF SQL%ROWCOUNT = 0 THEN
1427 l_return_status := FND_API.G_RET_STS_ERROR;
1428 DPP_UTILITY_PVT.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Unable to Update the column SUPP_DIST_CLAIM_STATUS in DPP_TRANSACTION_LINES_ALL Table');
1429 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1430 END IF;
1431 --Update those lines to Y who have the approved inventory as 0
1432 UPDATE DPP_TRANSACTION_LINES_ALL
1433 SET SUPP_DIST_CLAIM_STATUS = 'Y',
1434 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER +1,
1435 last_updated_by = l_user_id,
1436 last_update_date = sysdate,
1437 last_update_login = l_login_id
1438 WHERE transaction_header_id = p_txn_hdr_rec.Transaction_Header_ID
1439 AND nvl(SUPP_DIST_CLAIM_STATUS ,'N')= 'N'
1440 AND (nvl(approved_inventory,0) = 0 OR nvl(price_change,0) <= 0);
1441 ELSE -- Both Price Increase and Price Decrease Lines
1442 --Update the line status to PENDING in the DPP_TRANSACTION_LINES_ALL table
1443 UPDATE DPP_TRANSACTION_LINES_ALL
1444 SET SUPP_DIST_CLAIM_STATUS = 'P',
1445 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER +1,
1446 last_updated_by = l_user_id,
1447 last_update_date = sysdate,
1448 last_update_login = l_login_id
1449 WHERE transaction_header_id = p_txn_hdr_rec.Transaction_Header_ID
1450 AND nvl(SUPP_DIST_CLAIM_STATUS ,'N')= 'N'
1451 AND nvl(approved_inventory,0) > 0
1452 AND nvl(price_change,0) <> 0;
1453
1454 IF SQL%ROWCOUNT = 0 THEN
1455 l_return_status := FND_API.G_RET_STS_ERROR;
1456 DPP_UTILITY_PVT.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Unable to Update the column SUPP_DIST_CLAIM_STATUS in DPP_TRANSACTION_LINES_ALL Table');
1457 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1458 END IF;
1459 --Update those lines to Y who have the approved inventory as 0
1460 UPDATE DPP_TRANSACTION_LINES_ALL
1461 SET SUPP_DIST_CLAIM_STATUS = 'Y',
1462 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER +1,
1463 last_updated_by = l_user_id,
1464 last_update_date = sysdate,
1465 last_update_login = l_login_id
1466 WHERE transaction_header_id = p_txn_hdr_rec.Transaction_Header_ID
1470 END IF; --p_txn_hdr_rec.claim_creation_source = 'EXEDTLS'
1467 AND nvl(SUPP_DIST_CLAIM_STATUS ,'N')= 'N'
1468 AND nvl(approved_inventory,0) = 0;
1469 END IF;
1471 END IF;
1472 ELSIF p_txn_hdr_rec.process_code = 'CUSTINVCL' THEN
1473 --Generate the Input Xml required for the Business Event -- Create Customer Inventory claim for distributor
1474 l_queryCtx := dbms_xmlquery.newContext('SELECT headers.Transaction_header_id,
1475 headers.Transaction_number,
1476 headers.org_id,
1477 headers.Vendor_id,
1478 headers.Vendor_site_id,'
1479 ||l_user_name|| ' user_name, '
1480 ||l_user_id||'user_id,'
1481 ||l_exe_dtl_id||'Execution_detail_id,'
1482 ||l_claim_type_flag||'claim_type_flag,
1483 headers.trx_currency,
1484 CURSOR(select dcc.customer_inv_line_id TRANSACTION_LINE_ID,
1485 dcc.inventory_item_id,
1486 dcc.cust_account_id CUSTOMER_ACCOUNT_ID,
1487 dcc.supp_claim_amt CLAIM_LINE_AMOUNT,
1488 dcc.reported_inventory claim_quantity,
1489 dcc.trx_currency currency,
1490 dcc.UOM
1491 FROM DPP_customer_claims_all dcc,
1492 DPP_TRANSACTION_LINES_GT dtg
1493 WHERE headers.Transaction_header_id = dcc.Transaction_header_id
1494 AND dcc.customer_inv_line_id = dtg.transaction_line_id
1495 AND nvl(dcc.reported_inventory,0) > 0
1496 AND nvl(dcc.supp_claim_amt,0) > 0
1497 AND nvl(dcc.supplier_claim_created,''N'') = ''N'') LINES
1498 FROM dpp_transaction_headers_all headers
1499 WHERE headers.Transaction_header_id = ' ||p_txn_hdr_rec.Transaction_Header_ID||
1500 'AND EXISTS (SELECT dcca.Transaction_header_id
1501 FROM DPP_customer_claims_all dcca,
1502 DPP_TRANSACTION_LINES_GT dtga
1503 WHERE dcca.Transaction_header_id = '||p_txn_hdr_rec.Transaction_Header_ID||'
1504 AND dcca.customer_inv_line_id = dtga.transaction_line_id
1505 AND nvl(dcca.supplier_claim_created,''N'') = ''N''
1506 AND nvl(dcca.reported_inventory,0) > 0
1507 AND nvl(dcca.supp_claim_amt,0) > 0)'
1508 );
1509 dbms_xmlquery.setRowTag(l_queryCtx
1510 , 'HEADER'
1511 );
1512 dbms_xmlquery.setRowSetTag(l_queryCtx
1513 ,'TRANSACTION'
1514 );
1515 l_input_xml := dbms_xmlquery.getXml(l_queryCtx);
1516 --Check if the query returns any rows
1517 l_row_count := dbms_xmlquery.getNumRowsProcessed(l_queryCtx);
1518 dbms_xmlquery.closeContext(l_queryCtx);
1519
1520 IF l_row_count >0 THEN
1521 IF p_txn_hdr_rec.claim_creation_source = 'EXEDTLS' THEN
1522 --Update the line status to PENDING in the DPP_customer_claims_all table
1523 UPDATE DPP_customer_claims_all
1524 SET supplier_claim_created = 'P',
1525 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER +1,
1526 last_updated_by = l_user_id,
1527 last_update_date = sysdate,
1528 last_update_login = l_login_id
1529 WHERE transaction_header_id = p_txn_hdr_rec.Transaction_Header_ID
1530 AND nvl(supplier_claim_created,'N') = 'N'
1531 AND nvl(reported_inventory,0) > 0
1532 AND nvl(supp_claim_amt,0) > 0;
1533
1534 IF SQL%ROWCOUNT = 0 THEN
1535 l_return_status := FND_API.G_RET_STS_ERROR;
1536 DPP_UTILITY_PVT.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Unable to Update the column supplier_claim_created in DPP_customer_claims_all Table');
1537 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1538 END IF;
1539 --Update those lines to Y which have reported inventory as 0
1540 UPDATE DPP_customer_claims_all
1541 SET supplier_claim_created = 'Y',
1542 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER +1,
1543 last_updated_by = l_user_id,
1544 last_update_date = sysdate,
1545 last_update_login = l_login_id
1546 WHERE transaction_header_id = p_txn_hdr_rec.Transaction_Header_ID
1547 AND nvl(supplier_claim_created,'N') = 'N'
1548 AND (nvl(reported_inventory,0) = 0 OR nvl(supp_claim_amt,0) <= 0);
1549
1550 END IF; --IF p_txn_hdr_rec.claim_creation_source = 'EXEDTLS' THEN
1551 END IF;
1552 ELSIF p_txn_hdr_rec.process_code = 'CUSTCL' THEN
1553 --Generate the Input Xml required for the Business Event -- Create Customer Inventory claim for customer
1554 l_queryCtx := dbms_xmlquery.newContext('SELECT headers.Transaction_header_id,
1555 headers.Transaction_number,
1556 headers.org_id,
1557 headers.Vendor_id,
1561 ||l_exe_dtl_id||'Execution_detail_id,'
1558 headers.Vendor_site_id,'
1559 ||l_user_name|| ' user_name, '
1560 ||l_user_id||'user_id,'
1562 ||l_claim_type_flag||'claim_type_flag,
1563 headers.trx_currency,
1564 CURSOR(select dcc.customer_inv_line_id transaction_line_id,
1565 dcc.inventory_item_id,
1566 dcc.cust_account_id customer_account_id,
1567 dcc.cust_claim_amt claim_line_amount,
1568 dcc.reported_inventory claim_quantity,
1569 dcc.trx_currency currency,
1570 dcc.UOM
1571 FROM DPP_customer_claims_all dcc,
1572 DPP_TRANSACTION_LINES_GT dtg
1573 WHERE headers.Transaction_header_id = dcc.Transaction_header_id
1574 AND dcc.customer_inv_line_id = dtg.transaction_line_id
1575 AND nvl(dcc.reported_inventory,0) > 0
1576 AND nvl(dcc.cust_claim_amt,0) > 0
1577 AND nvl(dcc.customer_claim_created,''N'') = ''N'') LINES
1578 FROM dpp_transaction_headers_all headers
1579 WHERE headers.Transaction_header_id = ' ||p_txn_hdr_rec.Transaction_Header_ID||
1580 'AND EXISTS (SELECT dcca.Transaction_header_id
1581 FROM DPP_customer_claims_all dcca,
1582 DPP_TRANSACTION_LINES_GT dtga
1583 WHERE dcca.Transaction_header_id = '||p_txn_hdr_rec.Transaction_Header_ID||'
1584 AND dcca.customer_inv_line_id = dtga.transaction_line_id
1585 AND nvl(dcca.customer_claim_created,''N'') = ''N''
1586 AND nvl(dcca.reported_inventory,0) > 0
1587 AND nvl(dcca.cust_claim_amt,0) > 0)'
1588 );
1589 dbms_xmlquery.setRowTag(l_queryCtx
1590 , 'HEADER'
1591 );
1592 dbms_xmlquery.setRowSetTag(l_queryCtx
1593 ,'TRANSACTION'
1594 );
1595 l_input_xml := dbms_xmlquery.getXml(l_queryCtx);
1596 --Check if the query returns any rows
1597 l_row_count := dbms_xmlquery.getNumRowsProcessed(l_queryCtx);
1598 dbms_xmlquery.closeContext(l_queryCtx);
1599
1600 ELSIF p_txn_hdr_rec.process_code = 'POPCUSTCLAIM' THEN
1601 --Generate the Input Xml required for the Business Event -- Populate Customer Claim lines details
1602 l_queryCtx := dbms_xmlquery.newContext('SELECT Transaction_header_id,
1603 Transaction_number,
1604 org_id,
1605 nvl(to_char(effective_start_date-days_covered,''YYYY-MM-DD''),''1900-01-01'' ) EFFECTIVE_START_DATE,
1606 to_char(Effective_start_date,''YYYY-MM-DD'') effective_end_date,
1607 trx_currency,'
1608 ||l_user_name|| ' user_name, '
1609 ||l_user_id||'user_id,'
1610 ||l_exe_dtl_id||'Execution_detail_id,
1611 CURSOR(select transaction_line_id,
1612 inventory_item_id,
1613 UOM
1614 FROM dpp_transaction_lines_all lines
1615 WHERE headers.Transaction_header_id = lines.Transaction_header_id
1616 AND lines.price_change > 0 ) LINES
1617 FROM dpp_transaction_headers_all headers
1618 WHERE headers.Transaction_header_id = ' ||p_txn_hdr_rec.Transaction_Header_ID||
1619 'AND EXISTS (SELECT Transaction_header_id
1620 FROM dpp_transaction_lines_all
1621 WHERE price_change > 0
1622 AND Transaction_header_id = '||p_txn_hdr_rec.Transaction_Header_ID||')'
1623 );
1624 dbms_xmlquery.setRowTag(l_queryCtx
1625 , 'HEADER'
1626 );
1627 dbms_xmlquery.setRowSetTag(l_queryCtx
1628 ,'TRANSACTION'
1629 );
1630 l_input_xml := dbms_xmlquery.getXml(l_queryCtx);
1631 --Check if the query returns any rows
1632 l_row_count := dbms_xmlquery.getNumRowsProcessed(l_queryCtx);
1633 dbms_xmlquery.closeContext(l_queryCtx);
1634
1635 ELSIF p_txn_hdr_rec.process_code = 'POPINVDTLS' THEN
1636 --Generate the Input Xml required for the Business Event --Populate Inventory Details
1637 l_queryCtx := dbms_xmlquery.newContext('SELECT Transaction_header_id,
1638 Transaction_number,
1639 org_id,
1643 ||l_user_id||'user_id,'
1640 nvl(to_char(effective_start_date-days_covered,''YYYY-MM-DD''),''1900-01-01'' ) EFFECTIVE_START_DATE,
1641 to_char(Effective_start_date,''YYYY-MM-DD'') effective_end_date,'
1642 ||l_user_name|| ' user_name, '
1644 ||l_exe_dtl_id||'Execution_detail_id,
1645 CURSOR(select transaction_line_id,
1646 inventory_item_id
1647 FROM dpp_transaction_lines_all lines
1648 WHERE headers.Transaction_header_id = lines.Transaction_header_id) LINES
1649 FROM dpp_transaction_headers_all headers
1650 WHERE headers.Transaction_header_id = ' ||p_txn_hdr_rec.Transaction_Header_ID||
1651 'AND EXISTS (SELECT Transaction_header_id
1652 FROM dpp_transaction_lines_all
1653 WHERE Transaction_header_id = '||p_txn_hdr_rec.Transaction_Header_ID||')'
1654 );
1655 dbms_xmlquery.setRowTag(l_queryCtx
1656 , 'HEADER'
1657 );
1658 dbms_xmlquery.setRowSetTag(l_queryCtx
1659 ,'TRANSACTION'
1660 );
1661 l_input_xml := dbms_xmlquery.getXml(l_queryCtx);
1662 --Check if the query returns any rows
1663 l_row_count := dbms_xmlquery.getNumRowsProcessed(l_queryCtx);
1664 dbms_xmlquery.closeContext(l_queryCtx);
1665 ELSE
1666 FND_MESSAGE.set_name('DPP', 'DPP_BUSEVT_INVALID_PRO_CODE');
1667 fnd_message.set_token('PROCESS_CODE', p_txn_hdr_rec.process_code);
1668 FND_MSG_PUB.add;
1669 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
1670 RAISE FND_API.g_exc_error;
1671 END IF;
1672
1673 --Raise the Workflow Event Procedure for the given process
1674 Raise_Workflow_Event(
1675 x_return_status => l_return_status
1676 ,x_msg_count => l_msg_count
1677 ,x_msg_data => l_msg_data
1678 ,p_txn_hdr_id => p_txn_hdr_rec.Transaction_Header_ID
1679 ,p_process_code => p_txn_hdr_rec.process_code
1680 ,p_input_xml => l_input_xml
1681 ,p_row_count => l_row_count
1682 ,p_exe_dtl_id => l_exe_dtl_id
1683 );
1684
1685 dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'return status for Raise_Workflow_Event =>'||l_return_status);
1686 --dpp_utility_pvt.debug_message (FND_LOG.LEVEL_STATEMENT, l_module, substr(('Message data =>'||l_msg_data),1,4000));
1687
1688 -- Check return status from the above procedure call
1689 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1690 RAISE FND_API.g_exc_error;
1691 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1692 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1693 END IF;
1694 x_return_status:= l_return_status;
1695
1696 -- Standard check for p_commit
1697 IF FND_API.to_Boolean( p_commit )
1698 THEN
1699 COMMIT;
1700 END IF;
1701 FND_MSG_PUB.count_and_get(
1702 p_encoded => FND_API.g_false,
1703 p_count => x_msg_count,
1704 p_data => x_msg_data
1705 );
1706
1707 --Exception Handling
1708 EXCEPTION
1709 WHEN FND_API.g_exc_error THEN
1710 ROLLBACK TO Raise_Business_Event;
1711 x_return_status := FND_API.g_ret_sts_error;
1712 FND_MSG_PUB.count_and_get (
1713 p_encoded => FND_API.g_false,
1714 p_count => x_msg_count,
1715 p_data => x_msg_data
1716 );
1717
1718 IF x_msg_count > 1 THEN
1719 FOR I IN 1..x_msg_count LOOP
1720 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
1721 END LOOP;
1722 END IF;
1723
1724 WHEN FND_API.g_exc_unexpected_error THEN
1725 ROLLBACK TO Raise_Business_Event;
1726 x_return_status := FND_API.g_ret_sts_unexp_error ;
1727 FND_MSG_PUB.count_and_get (
1728 p_encoded => FND_API.g_false,
1729 p_count => x_msg_count,
1730 p_data => x_msg_data
1731 );
1732
1733 IF x_msg_count > 1 THEN
1734 FOR I IN 1..x_msg_count LOOP
1735 x_msg_data := SUBSTR((x_msg_data||' '|| substr(FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F'),1,254)), 1, 4000);
1736 END LOOP;
1737 END IF;
1738
1739 WHEN OTHERS THEN
1740 ROLLBACK TO Raise_Business_Event;
1741 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
1742 fnd_message.set_token('ROUTINE', 'DPP_BUSINESSEVENTS_PVT');
1743 fnd_message.set_token('ERRNO', sqlcode);
1744 fnd_message.set_token('REASON', sqlerrm);
1745 x_return_status := FND_API.g_ret_sts_unexp_error ;
1746 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1747 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
1748 END IF;
1749 FND_MSG_PUB.count_and_get (
1750 p_encoded => FND_API.g_false,
1751 p_count => x_msg_count,
1752 p_data => x_msg_data
1753 );
1754
1755 IF x_msg_count > 1 THEN
1756 FOR I IN 1..x_msg_count LOOP
1757 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
1758 END LOOP;
1759 END IF;
1760
1761 END Raise_Business_Event;
1762
1763 ---------------------------------------------------------------------
1767 -- PURPOSE
1764 -- PROCEDURE
1765 -- Raise_Workflow_Event
1766 --
1768 -- Raise Workflow Event.
1769 --
1770 -- PARAMETERS
1771 --
1772 -- NOTES
1773 -- 1.
1774 -- 2.
1775 ----------------------------------------------------------------------
1776 PROCEDURE Raise_Workflow_Event(
1777 x_return_status OUT NOCOPY VARCHAR2
1778 ,x_msg_count OUT NOCOPY NUMBER
1779 ,x_msg_data OUT NOCOPY VARCHAR2
1780
1781 ,p_txn_hdr_id IN NUMBER
1782 ,p_process_code IN VARCHAR2
1783 ,p_input_xml IN CLOB
1784 ,p_row_count IN NUMBER
1785 ,p_exe_dtl_id IN NUMBER
1786 )
1787 IS
1788 l_api_name CONSTANT VARCHAR2(30) := 'Raise_Business_Event';
1789 l_event_name VARCHAR2(60):= 'oracle.apps.dpp.executions';
1790 l_new_item_key VARCHAR2(30);
1791 l_parameter_list WF_PARAMETER_LIST_T;
1792 l_target_system VARCHAR2(4) := 'EBIZ';
1793 l_user_id NUMBER := FND_GLOBAL.USER_ID;
1794 l_exe_status VARCHAR2(15) := 'SUBMITTED';
1795 l_event_test VARCHAR2(10);
1796 l_module CONSTANT VARCHAR2(100) := 'dpp.plsql.DPP_BUSINESSEVENTS_PVT.RAISE_WORKFLOW_EVENT';
1797
1798 BEGIN
1799
1800 x_return_status := FND_API.g_ret_sts_success;
1801 --------------------- initialize -----------------------
1802 SAVEPOINT Raise_Workflow_Event;
1803
1804 --Get a unique value for the l_new_item_key key
1805 l_new_item_key := TO_CHAR(SYSDATE,'DDMMRRRRHH24MISS');
1806 --Check if the event subscription can be raised or not before inserting into the DPP_EXECUTION_DETAILS table
1807 l_event_test := wf_event.test(l_event_name);
1808 IF l_event_test = 'NONE' THEN
1809 DPP_UTILITY_PVT.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'No enabled local subscriptions reference the event, or the event does not exist.');
1810 RAISE FND_API.g_exc_error;
1811 ELSE
1812 DPP_UTILITY_PVT.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Number of rows Processed : '||p_row_count);
1813
1814 --Check if the xml has any rows processed and raise the event
1815 IF p_row_count > 0 THEN
1816 --Insert a line in to the DPP_EXECUTION_DETAILS table corresponding to the process which has been started
1817 BEGIN
1818 INSERT INTO DPP_EXECUTION_DETAILS (EXECUTION_DETAIL_ID,
1819 object_version_number,
1820 TRANSACTION_HEADER_ID,
1821 PROCESS_CODE,
1822 INPUT_XML,
1823 EXECUTION_STATUS,
1824 EXECUTION_START_DATE,
1825 CREATION_DATE,
1826 CREATED_BY,
1827 LAST_UPDATE_DATE,
1828 LAST_UPDATED_BY,
1829 LAST_UPDATE_LOGIN)
1830 VALUES (p_exe_dtl_id,
1831 1,
1832 p_txn_hdr_id,
1833 p_process_code,
1834 XMLTYPE(p_input_xml),
1835 l_exe_status,
1836 sysdate,
1837 sysdate,
1838 l_user_id,
1839 sysdate,
1840 l_user_id,
1841 l_user_id);
1842
1843 EXCEPTION
1844 WHEN OTHERS THEN
1845 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
1846 fnd_message.set_token('ROUTINE', 'DPP_BUSINESSEVENTS_PVT');
1847 fnd_message.set_token('ERRNO', sqlcode);
1848 fnd_message.set_token('REASON', sqlerrm);
1849 FND_MSG_PUB.add;
1850 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1851 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
1852 END IF;
1853 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1854 END;
1855 --Define in WF Attributes l_parameter_list := WF_PARAMETER_LIST_T();
1856 WF_EVENT.AddParameterToList( p_name => 'TARGETSYSTEM'
1857 , p_value => l_target_system
1858 , p_parameterlist => l_parameter_list
1859 );
1860 WF_EVENT.AddParameterToList( p_name => 'FLOWNAME'
1861 , p_value => p_process_code
1862 , p_parameterlist => l_parameter_list
1863 );
1864 WF_EVENT.AddParameterToList( p_name => 'EXECUTIONDETAILID'
1865 , p_value => p_exe_dtl_id
1866 , p_parameterlist => l_parameter_list
1867 );
1868 WF_EVENT.Raise(p_event_name => l_event_name
1869 ,p_event_key => l_new_item_key
1870 ,p_event_data => p_input_xml
1871 ,p_parameters => l_parameter_list
1872 ,p_send_date => sysdate
1873 );
1874
1875 DPP_UTILITY_PVT.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Work Flow Event Raised');
1876
1877 ELSE
1878 DPP_UTILITY_PVT.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Work Flow Event is not Raised');
1879 FND_MESSAGE.set_name('DPP', 'DPP_CC_NO_ELIGIBLE_LINES_MSG');
1880 FND_MSG_PUB.add;
1881 RAISE FND_API.g_exc_error;
1882 END IF; --p_row_count > 0
1883 END IF;
1884
1885 EXCEPTION
1886 WHEN FND_API.g_exc_error THEN
1887 ROLLBACK TO Raise_Workflow_Event;
1888 x_return_status := FND_API.g_ret_sts_error;
1889 FND_MSG_PUB.count_and_get (
1890 p_encoded => FND_API.g_false,
1891 p_count => x_msg_count,
1892 p_data => x_msg_data
1893 );
1894 IF x_msg_count > 1 THEN
1895 FOR I IN 1..x_msg_count LOOP
1896 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
1897 END LOOP;
1898 END IF;
1899
1900 WHEN FND_API.g_exc_unexpected_error THEN
1901 ROLLBACK TO Raise_Workflow_Event;
1902 x_return_status := FND_API.g_ret_sts_unexp_error ;
1903 FND_MSG_PUB.count_and_get (
1904 p_encoded => FND_API.g_false,
1905 p_count => x_msg_count,
1906 p_data => x_msg_data
1907 );
1908 IF x_msg_count > 1 THEN
1909 FOR I IN 1..x_msg_count LOOP
1910 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
1911 END LOOP;
1912 END IF;
1913
1914 WHEN OTHERS THEN
1915 ROLLBACK TO Raise_Workflow_Event;
1916 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
1917 fnd_message.set_token('ROUTINE', 'DPP_BUSINESSEVENTS_PVT');
1918 fnd_message.set_token('ERRNO', sqlcode);
1919 fnd_message.set_token('REASON', sqlerrm);
1920 FND_MSG_PUB.add;
1921 x_return_status := FND_API.g_ret_sts_unexp_error ;
1922 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1923 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
1924 END IF;
1925 FND_MSG_PUB.count_and_get (
1926 p_encoded => FND_API.g_false,
1927 p_count => x_msg_count,
1928 p_data => x_msg_data
1929 );
1930 IF x_msg_count > 1 THEN
1931 FOR I IN 1..x_msg_count LOOP
1932 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
1933 END LOOP;
1934 END IF;
1935
1936 END Raise_Workflow_Event;
1937
1938 END DPP_BUSINESSEVENTS_PVT;
1939