[Home] [Help]
PACKAGE BODY: APPS.DPP_EXECUTIONPROCESS_PVT
Source
1 PACKAGE BODY DPP_EXECUTIONPROCESS_PVT AS
2 /* $Header: dppexppb.pls 120.20 2010/09/27 04:48:06 anbbalas noship $ */
3
4 -- Package name : DPP_EXECUTIONPROCESS_PVT
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10 G_PKG_NAME CONSTANT VARCHAR2(30) := 'DPP_EXECUTIONPROCESS_PVT';
11 G_DEBUG BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_high);
12 G_FILE_NAME CONSTANT VARCHAR2(14) := 'dppexppb.pls';
13
14 G_NO_REC_PROC_MSG CONSTANT VARCHAR2(50) := 'This process is not enabled (process code) : ';
15 G_NO_EVT_MSG CONSTANT VARCHAR2(52) := 'Business event not raised for the process code : ';
16 G_NO_DAYS_MSG CONSTANT VARCHAR2(60) := 'Number of days is not set up in the System parameter for ';
17 G_NO_TXN_MSG CONSTANT VARCHAR2(90) := 'No transactions effective after the number of days specified in the System parameter : ';
18 G_AUTO_PROC_MSG CONSTANT VARCHAR2(50) := 'This process is not automatic (process code) : ';
19 G_AUTO_EVT_SUCCESS_MSG CONSTANT VARCHAR2(50) := 'Auto notification events raised successfully.';
20 G_PRCSING_TXN_MSG CONSTANT VARCHAR2(30) := 'Processing transaction : ';
21 G_ERR_AUTO_NTF_MSG CONSTANT VARCHAR2(50) := 'Error while executing auto notification process.';
22 G_NO_PROFILE_MSG CONSTANT VARCHAR2(45) := 'Supplier trade profile setup not available.';
23 G_NO_EXEC_SETUP_MSG CONSTANT VARCHAR2(65) := 'No Execution processes setup available for this transaction : ';
24 G_NO_SETUP_MSG CONSTANT VARCHAR2(60) := 'Supplier Trade Profile or Process Setup not available';
25
26 PROCEDURE EXECUTE_PROCESS(
27 P_API_VERSION IN NUMBER,
28 P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
29 P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
30 P_VALIDATION_LEVEL IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
31 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
32 X_MSG_COUNT OUT NOCOPY NUMBER,
33 X_MSG_DATA OUT NOCOPY VARCHAR2,
34 P_TRANSACTION_HEADER_ID IN VARCHAR2,
35 P_TRANSACTION_NUMBER IN VARCHAR2,
36 P_PROCESS_CODE IN VARCHAR2);
37
38 PROCEDURE EXECUTE_NOTIF_PROCESSES(
39 P_API_VERSION IN NUMBER,
40 P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
41 P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
42 P_VALIDATION_LEVEL IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
43 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
44 X_MSG_COUNT OUT NOCOPY NUMBER,
45 X_MSG_DATA OUT NOCOPY VARCHAR2,
46 P_SUPP_TRADE_PROFILE_ID IN VARCHAR2,
47 P_IN_ORG_ID IN NUMBER,
48 P_TRANSACTION_HEADER_ID IN VARCHAR2,
49 P_TRANSACTION_NUMBER IN VARCHAR2);
50
51 FUNCTION GET_SUPPLIER_TRADE_PROFILE_ID ( P_VENDOR_ID IN VARCHAR2, P_VENDOR_SITE_ID IN VARCHAR2, P_ORG_ID IN VARCHAR2 ) RETURN NUMBER;
52
53 FUNCTION IS_PROCESS_SETUP ( P_SUPP_TRADE_PROFILE_ID IN NUMBER, P_ORG_ID IN NUMBER ) RETURN BOOLEAN;
54
55 FUNCTION GET_PROCESS_SETUP_ID ( P_VENDOR_ID IN VARCHAR2, P_VENDOR_SITE_ID IN VARCHAR2, P_ORG_ID IN VARCHAR2 ) RETURN NUMBER;
56
57 ---------------------------------------------------------------------
58 -- PROCEDURE
59 -- INITIATE_EXECUTIONPROCESS
60 --
61 -- PURPOSE
62 -- Initiates the Execution Process for an org/transaction.
63 --
64 -- PARAMETERS
65 -- org id, transaction number.
66 ----------------------------------------------------------------------
67 PROCEDURE INITIATE_EXECUTIONPROCESS(ERRBUF OUT NOCOPY VARCHAR2,
68 RETCODE OUT NOCOPY VARCHAR2,
69 P_IN_ORG_ID IN NUMBER,
70 P_IN_TXN_NUMBER IN VARCHAR2)
71 IS
72 L_API_VERSION CONSTANT NUMBER := 1.0;
73 L_API_NAME CONSTANT VARCHAR2(30) := 'INITIATE_EXECUTIONPROCESS';
74 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| L_API_NAME;
75
76 L_RETURN_STATUS VARCHAR2(10);
77 L_MSG_DATA VARCHAR2(4000);
78 L_MSG_COUNT NUMBER;
79
80 L_TXN_HDR_REC DPP_BUSINESSEVENTS_PVT.DPP_TXN_HDR_REC_TYPE;
81 L_TXN_LINE_ID DPP_BUSINESSEVENTS_PVT.DPP_TXN_LINE_TBL_TYPE;
82 L_FLAG BOOLEAN := FALSE;
83 L_SUPP_TRADE_PROFILE_ID NUMBER;
84 L_SETUP_FLAG BOOLEAN := FALSE;
85
86 CURSOR get_auto_flag_csr(p_supp_trd_prf_id NUMBER, p_process_code VARCHAR2, p_txn_hdr_id VARCHAR2) IS
87 SELECT NVL(AUTOMATIC_FLAG,'N') AUTOMATIC_FLAG
88 FROM OZF_PROCESS_SETUP_ALL OPSA, DPP_EXECUTION_PROCESSES DEP
89 WHERE NVL(SUPP_TRADE_PROFILE_ID,0) = NVL(P_SUPP_TRD_PRF_ID,0)
90 AND OPSA.PROCESS_CODE = P_PROCESS_CODE
91 AND OPSA.PROCESS_CODE = DEP.PROCESS_CODE
92 AND OPSA.ORG_ID = P_IN_ORG_ID
93 AND DEP.TRANSACTION_HEADER_ID=P_TXN_HDR_ID;
94
95 --Cursor to check if the customer claims tab is already populated
96 CURSOR get_customer_claim_csr(p_txn_header_id IN NUMBER) IS
97 SELECT dpp.transaction_header_id
98 FROM dpp_transaction_headers_all dpp
99 WHERE dpp.transaction_header_id = p_txn_header_id
100 AND NOT EXISTS (SELECT DISTINCT dcc.transaction_header_id
101 FROM dpp_customer_claims_all dcc
102 WHERE dcc.transaction_header_id = dpp.transaction_header_id);
103
104 --Cursor for Update PO
105 CURSOR get_lines_for_updatepo_csr(p_txn_hdr_id IN NUMBER) IS
106 SELECT dpp.transaction_header_id
107 FROM dpp_transaction_headers_all dpp
108 WHERE dpp.transaction_header_id = p_txn_hdr_id
109 AND EXISTS (SELECT update_purchasing_docs
110 FROM dpp_transaction_lines_all dtl
111 WHERE nvl(update_purchasing_docs,'N') = 'N'
112 AND dtl.transaction_header_id = p_txn_hdr_id);
113
114 --Cursor for Update inv costing
115 CURSOR get_lines_for_updateinv_csr(p_txn_hdr_id IN NUMBER) IS
116 SELECT dtl.transaction_line_id
117 FROM dpp_transaction_lines_all dtl
118 WHERE dtl.transaction_header_id = p_txn_hdr_id
119 AND EXISTS (SELECT UPDATE_INVENTORY_COSTING
120 FROM dpp_transaction_lines_all
121 WHERE nvl(UPDATE_INVENTORY_COSTING,'N') = 'N'
122 AND transaction_header_id = p_txn_hdr_id)
123 AND rownum = 1;
124
125 --Cursor for Update item List Price
126 CURSOR get_lines_for_updlistprice_csr(p_txn_hdr_id IN NUMBER) IS
127 SELECT dtl.transaction_line_id
128 FROM dpp_transaction_lines_all dtl
129 WHERE dtl.transaction_header_id = p_txn_hdr_id
130 AND EXISTS (SELECT UPDATE_ITEM_LIST_PRICE
131 FROM dpp_transaction_lines_all
132 WHERE nvl(UPDATE_ITEM_LIST_PRICE,'N') = 'N'
133 AND transaction_header_id = p_txn_hdr_id)
134 AND rownum = 1;
135
136 CURSOR get_approved_txn_csr (p_txn_number VARCHAR2) IS
137 SELECT DPP.TRANSACTION_HEADER_ID,
138 DPP.TRANSACTION_NUMBER,
139 DPP.VENDOR_ID,
140 DPP.VENDOR_SITE_ID
141 FROM DPP_TRANSACTION_HEADERS_ALL DPP
142 WHERE DPP.TRANSACTION_STATUS = 'APPROVED'
143 AND DPP.EFFECTIVE_START_DATE <= SYSDATE
144 AND TO_NUMBER(DPP.ORG_ID) = P_IN_ORG_ID
145 AND DPP.TRANSACTION_NUMBER = NVL(P_TXN_NUMBER,DPP.TRANSACTION_NUMBER);
146
147 BEGIN
148
149 -- Initialize return status to sucess
150 ERRBUF := 'SUCCESS';
151 RETCODE := 0;
152
153 SAVEPOINT INITIATE_EXEC;
154
155 DPP_UTILITY_PVT.DEBUG_MESSAGE( FND_LOG.LEVEL_PROCEDURE, 'dpp.plsql.' || L_FULL_NAME, 'Public API: ' || L_API_NAME || ' started at: ' || to_char(sysdate,'dd-mon-yyyy hh24:mi:ss'));
156
157 --Initialize message list
158 FND_MSG_PUB.INITIALIZE;
159
160 -- Initialize API return status to sucess
161 L_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
162
163 --Get all the active transactions
164 FOR get_approved_txn_rec IN get_approved_txn_csr(p_in_txn_number) LOOP
165
166 FND_FILE.PUT_LINE(FND_FILE.LOG,'Concurrent program executed for the transaction header id : ' ||get_approved_txn_rec.transaction_header_id);
167 FND_FILE.NEW_LINE(FND_FILE.LOG);
168
169 DPP_UTILITY_PVT.DEBUG_MESSAGE( FND_LOG.LEVEL_STATEMENT, 'dpp.plsql.' || L_FULL_NAME, '*Concurrent program executed for the transaction header id : '
170 ||get_approved_txn_rec.transaction_header_id);
171
172
173 l_supp_trade_profile_id := GET_PROCESS_SETUP_ID (get_approved_txn_rec.vendor_id, get_approved_txn_rec.vendor_site_id, P_IN_ORG_ID);
174
175 FOR get_auto_flag_rec IN get_auto_flag_csr(l_supp_trade_profile_id, 'UPDTPO', get_approved_txn_rec.transaction_header_id) LOOP
176 l_flag := TRUE;
177 l_setup_flag := TRUE;
178 IF get_auto_flag_rec.automatic_flag = 'Y' THEN
179 FOR get_lines_for_updatepo_rec IN get_lines_for_updatepo_csr(get_approved_txn_rec.transaction_header_id) LOOP
180 EXECUTE_PROCESS(L_API_VERSION, FND_API.G_FALSE, FND_API.G_FALSE, FND_API.G_VALID_LEVEL_FULL, L_RETURN_STATUS, L_MSG_COUNT,
181 L_MSG_DATA, GET_APPROVED_TXN_REC.TRANSACTION_HEADER_ID, GET_APPROVED_TXN_REC.TRANSACTION_NUMBER, 'UPDTPO');
182 IF L_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
183 RAISE FND_API.G_EXC_ERROR;
184 ELSIF L_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
185 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
186 END IF;
187 END LOOP;
188 ELSE
189 FND_FILE.PUT_LINE(FND_FILE.LOG, G_AUTO_PROC_MSG || 'UPDTPO');
190 FND_FILE.NEW_LINE(FND_FILE.LOG);
191 DPP_UTILITY_PVT.DEBUG_MESSAGE( FND_LOG.LEVEL_STATEMENT, 'dpp.plsql.' || L_FULL_NAME, '*' || G_AUTO_PROC_MSG || 'UPDTPO');
192 END IF;
193 END LOOP;
194 IF NOT l_flag THEN
195 FND_FILE.PUT_LINE(FND_FILE.LOG, G_NO_REC_PROC_MSG || 'UPDTPO');
196 FND_FILE.NEW_LINE(FND_FILE.LOG);
197 DPP_UTILITY_PVT.DEBUG_MESSAGE( FND_LOG.LEVEL_STATEMENT, 'dpp.plsql.' || L_FULL_NAME, '*' || G_NO_REC_PROC_MSG || 'UPDTPO');
198 END IF;
199
200 FOR get_auto_flag_rec IN get_auto_flag_csr(l_supp_trade_profile_id, 'INVC', get_approved_txn_rec.transaction_header_id) LOOP
201 l_flag := TRUE;
202 l_setup_flag := TRUE;
203 IF get_auto_flag_rec.automatic_flag = 'Y' THEN
204 FOR get_lines_for_updateinv_rec IN get_lines_for_updateinv_csr(get_approved_txn_rec.transaction_header_id) LOOP
205 EXECUTE_PROCESS(L_API_VERSION, FND_API.G_FALSE, FND_API.G_FALSE, FND_API.G_VALID_LEVEL_FULL, L_RETURN_STATUS, L_MSG_COUNT,
206 L_MSG_DATA, GET_APPROVED_TXN_REC.TRANSACTION_HEADER_ID, GET_APPROVED_TXN_REC.TRANSACTION_NUMBER, 'INVC');
207 IF L_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
208 RAISE FND_API.G_EXC_ERROR;
209 ELSIF L_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
210 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
211 END IF;
212 END LOOP;
213 ELSE
214 FND_FILE.PUT_LINE(FND_FILE.LOG, G_AUTO_PROC_MSG || 'INVC');
215 FND_FILE.NEW_LINE(FND_FILE.LOG);
216 DPP_UTILITY_PVT.DEBUG_MESSAGE( FND_LOG.LEVEL_STATEMENT, 'dpp.plsql.' || L_FULL_NAME, '*' || G_AUTO_PROC_MSG || 'INVC');
217 END IF;
218 END LOOP;
219 IF NOT l_flag THEN
220 FND_FILE.PUT_LINE(FND_FILE.LOG, G_NO_REC_PROC_MSG || 'INVC');
221 FND_FILE.NEW_LINE(FND_FILE.LOG);
222 DPP_UTILITY_PVT.DEBUG_MESSAGE( FND_LOG.LEVEL_STATEMENT, 'dpp.plsql.' || L_FULL_NAME, '*' || G_NO_REC_PROC_MSG || 'INVC');
223 END IF;
224
225 FOR get_auto_flag_rec IN get_auto_flag_csr(l_supp_trade_profile_id, 'UPDTLP', get_approved_txn_rec.transaction_header_id) LOOP
226 l_flag := TRUE;
227 l_setup_flag := TRUE;
228 IF get_auto_flag_rec.automatic_flag = 'Y' THEN
229 FOR get_lines_for_updlistprice_rec IN get_lines_for_updlistprice_csr(get_approved_txn_rec.transaction_header_id) LOOP
230 EXECUTE_PROCESS(L_API_VERSION, FND_API.G_FALSE, FND_API.G_FALSE, FND_API.G_VALID_LEVEL_FULL, L_RETURN_STATUS, L_MSG_COUNT,
231 L_MSG_DATA, GET_APPROVED_TXN_REC.TRANSACTION_HEADER_ID, GET_APPROVED_TXN_REC.TRANSACTION_NUMBER, 'UPDTLP');
232 IF L_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
233 RAISE FND_API.G_EXC_ERROR;
234 ELSIF L_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
235 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
236 END IF;
237 END LOOP;
238 ELSE
239 FND_FILE.PUT_LINE(FND_FILE.LOG, G_AUTO_PROC_MSG || 'UPDTLP');
240 FND_FILE.NEW_LINE(FND_FILE.LOG);
241 DPP_UTILITY_PVT.DEBUG_MESSAGE( FND_LOG.LEVEL_STATEMENT, 'dpp.plsql.' || L_FULL_NAME, '*' || G_AUTO_PROC_MSG || 'UPDTLP');
242 END IF;
243 END LOOP;
244 IF NOT l_flag THEN
245 FND_FILE.PUT_LINE(FND_FILE.LOG, G_NO_REC_PROC_MSG || 'UPDTLP');
246 FND_FILE.NEW_LINE(FND_FILE.LOG);
247 DPP_UTILITY_PVT.DEBUG_MESSAGE( FND_LOG.LEVEL_STATEMENT, 'dpp.plsql.' || L_FULL_NAME, '*' || G_NO_REC_PROC_MSG || 'UPDTLP');
248 END IF;
249
250 EXECUTE_NOTIF_PROCESSES(L_API_VERSION, FND_API.G_FALSE, FND_API.G_FALSE, FND_API.G_VALID_LEVEL_FULL, L_RETURN_STATUS, L_MSG_COUNT,
251 L_MSG_DATA, L_SUPP_TRADE_PROFILE_ID,P_IN_ORG_ID, GET_APPROVED_TXN_REC.TRANSACTION_HEADER_ID,GET_APPROVED_TXN_REC.TRANSACTION_NUMBER);
252 IF L_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
253 RAISE FND_API.G_EXC_ERROR;
254 ELSIF L_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
255 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
256 END IF;
257
258 --Check if the customer claims tab is populated
259 FOR get_customer_claim_rec IN get_customer_claim_csr(get_approved_txn_rec.transaction_header_id) LOOP
260 EXECUTE_PROCESS(L_API_VERSION, FND_API.G_FALSE, FND_API.G_FALSE, FND_API.G_VALID_LEVEL_FULL, L_RETURN_STATUS, L_MSG_COUNT,
261 L_MSG_DATA, GET_APPROVED_TXN_REC.TRANSACTION_HEADER_ID, GET_APPROVED_TXN_REC.TRANSACTION_NUMBER, 'POPCUSTCLAIM');
262 IF L_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
263 RAISE FND_API.G_EXC_ERROR;
264 ELSIF L_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
265 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
266 END IF;
267 END LOOP;
268
269 END LOOP;
270
271 --Commit thechanges
272 COMMIT;
273
274 EXCEPTION
275 WHEN FND_API.G_EXC_ERROR THEN
276 ROLLBACK TO INITIATE_EXEC;
277 retcode := 2;
278 errbuf := 'Error';
279 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE, p_count => l_msg_count, p_data => l_msg_data );
280 IF l_msg_count > 1 THEN
281 FOR I IN 1..l_msg_count LOOP
282 l_msg_data := SUBSTR((l_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
283 END LOOP;
284 END IF;
285
286 FND_FILE.PUT_LINE(FND_FILE.LOG,substr(('Error Message : ' || l_msg_data),1,4000));
287 FND_FILE.NEW_LINE(FND_FILE.LOG);
288 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
289 ROLLBACK TO INITIATE_EXEC;
290 retcode := 2;
291 errbuf := 'Error';
292 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE, p_count => l_msg_count, p_data => l_msg_data );
293 IF l_msg_count > 1 THEN
294 FOR I IN 1..l_msg_count LOOP
295 l_msg_data := SUBSTR((l_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
296 END LOOP;
297 END IF;
298
299 FND_FILE.PUT_LINE(FND_FILE.LOG,substr(('Error Message : ' || l_msg_data),1,4000));
300 FND_FILE.NEW_LINE(FND_FILE.LOG);
301 WHEN OTHERS THEN
302 ROLLBACK TO INITIATE_EXEC;
303 retcode := 2;
304 errbuf := 'Error';
305 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
306 fnd_message.set_token('ROUTINE', l_full_name);
307 fnd_message.set_token('ERRNO', sqlcode);
308 fnd_message.set_token('REASON', sqlerrm);
309 FND_MSG_PUB.add;
310
311 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE, p_count => l_msg_count, p_data => l_msg_data );
312 IF l_msg_count > 1 THEN
313 FOR I IN 1..l_msg_count LOOP
314 l_msg_data := SUBSTR((l_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
315 END LOOP;
316 END IF;
317
318 FND_FILE.PUT_LINE(FND_FILE.LOG,substr(('Error Message : ' || l_msg_data),1,4000));
319 FND_FILE.NEW_LINE(FND_FILE.LOG);
320
321 END INITIATE_EXECUTIONPROCESS;
322
323 ---------------------------------------------------------------------
324 -- PROCEDURE
325 -- EXECUTE_NOTIF_PROCESSES
326 --
327 -- PURPOSE
328 -- Executes the Notification Process
329 --
330 -- PARAMETERS
331 -- supplier trade profile id(if null, the setup is taken from the system parameters)
332 -- org-id, transaction header id, transaction number.
333 ----------------------------------------------------------------------
334 PROCEDURE EXECUTE_NOTIF_PROCESSES(
335 P_API_VERSION IN NUMBER,
336 P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
337 P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
338 P_VALIDATION_LEVEL IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
339 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
340 X_MSG_COUNT OUT NOCOPY NUMBER,
341 X_MSG_DATA OUT NOCOPY VARCHAR2,
342 P_SUPP_TRADE_PROFILE_ID IN VARCHAR2,
343 P_IN_ORG_ID IN NUMBER,
344 P_TRANSACTION_HEADER_ID IN VARCHAR2,
345 P_TRANSACTION_NUMBER IN VARCHAR2)
346 IS
347
348 L_API_VERSION CONSTANT NUMBER := 1.0;
349 L_API_NAME CONSTANT VARCHAR2(30) := 'EXECUTE_NOTIF_PROCESSES';
350 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| L_API_NAME;
351
352 l_flag boolean := false;
353
354 CURSOR get_lines_for_notif_csr(p_txn_hdr_id IN NUMBER)
355 IS
356 SELECT dpp.transaction_header_id
357 FROM dpp_transaction_headers_all dpp
358 WHERE dpp.transaction_header_id = p_txn_hdr_id
359 AND ROWNUM=1;
360
361 CURSOR get_auto_flag_csr(p_supp_trd_prf_id NUMBER, p_process_code VARCHAR2, p_txn_hdr_id VARCHAR2) IS
362 SELECT NVL(AUTOMATIC_FLAG,'N') AUTOMATIC_FLAG
363 FROM OZF_PROCESS_SETUP_ALL OPSA, DPP_EXECUTION_PROCESSES DEP
364 WHERE NVL(SUPP_TRADE_PROFILE_ID,0) = NVL(P_SUPP_TRD_PRF_ID,0)
365 AND OPSA.PROCESS_CODE = P_PROCESS_CODE
366 AND OPSA.PROCESS_CODE = DEP.PROCESS_CODE
367 AND OPSA.ORG_ID = P_IN_ORG_ID
368 AND DEP.TRANSACTION_HEADER_ID=P_TXN_HDR_ID;
369
370 BEGIN
371
372 SAVEPOINT EXECUTE_NOTIF;
373
374 -- Standard call to check for call compatibility.
375 IF NOT FND_API.COMPATIBLE_API_CALL (L_API_VERSION, P_API_VERSION, L_API_NAME, G_PKG_NAME)
376 THEN
377 DPP_UTILITY_PVT.DEBUG_MESSAGE( FND_LOG.LEVEL_ERROR, 'dpp.plsql.' || L_FULL_NAME, L_API_NAME || ' : Not a compatible API call.');
378 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
379 END IF;
380
381 DPP_UTILITY_PVT.DEBUG_MESSAGE( FND_LOG.LEVEL_PROCEDURE, 'dpp.plsql.' || L_FULL_NAME, L_API_NAME||': START');
382
383 --Initialize message list if p_init_msg_list is TRUE.
384 IF FND_API.TO_BOOLEAN (P_INIT_MSG_LIST) THEN
385 FND_MSG_PUB.INITIALIZE;
386 END IF;
387
388 -- Initialize API return status to sucess
389 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
390
391 FOR get_lines_for_notif_rec IN get_lines_for_notif_csr(p_transaction_header_id) LOOP
392 l_flag := FALSE;
393 FOR get_auto_flag_rec IN get_auto_flag_csr(p_supp_trade_profile_id, 'NTFYPO', p_transaction_header_id) LOOP
394 l_flag := TRUE;
395 IF get_auto_flag_rec.automatic_flag = 'Y' THEN
396 EXECUTE_PROCESS(L_API_VERSION, FND_API.G_FALSE, FND_API.G_FALSE, FND_API.G_VALID_LEVEL_FULL, X_RETURN_STATUS, X_MSG_COUNT, X_MSG_DATA,
397 P_TRANSACTION_HEADER_ID,P_TRANSACTION_NUMBER, 'NTFYPO');
398 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
399 RAISE FND_API.G_EXC_ERROR;
400 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
401 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
402 END IF;
403 ELSE
404 FND_FILE.PUT_LINE(FND_FILE.LOG, G_AUTO_PROC_MSG || 'NTFYPO');
405 FND_FILE.NEW_LINE(FND_FILE.LOG);
406 DPP_UTILITY_PVT.DEBUG_MESSAGE( FND_LOG.LEVEL_STATEMENT, 'dpp.plsql.' || L_FULL_NAME, '*' || G_AUTO_PROC_MSG || 'NTFYPO');
407 END IF;
408 END LOOP;
409 IF NOT l_flag THEN
410 FND_FILE.PUT_LINE(FND_FILE.LOG, G_NO_REC_PROC_MSG || 'NTFYPO');
411 FND_FILE.NEW_LINE(FND_FILE.LOG);
412 DPP_UTILITY_PVT.DEBUG_MESSAGE( FND_LOG.LEVEL_STATEMENT, 'dpp.plsql.' || L_FULL_NAME, '*' || G_NO_REC_PROC_MSG || 'NYFYPO');
413 END IF;
414
415 l_flag := FALSE;
416 FOR get_auto_flag_rec IN get_auto_flag_csr(p_supp_trade_profile_id, 'INPL', p_transaction_header_id) LOOP
417 l_flag := TRUE;
418 IF get_auto_flag_rec.automatic_flag = 'Y' THEN
419 EXECUTE_PROCESS(L_API_VERSION, FND_API.G_FALSE, FND_API.G_FALSE, FND_API.G_VALID_LEVEL_FULL, X_RETURN_STATUS, X_MSG_COUNT, X_MSG_DATA,
420 P_TRANSACTION_HEADER_ID,P_TRANSACTION_NUMBER, 'INPL');
421 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
422 RAISE FND_API.G_EXC_ERROR;
423 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
424 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
425 END IF;
426 ELSE
427 FND_FILE.PUT_LINE(FND_FILE.LOG, G_AUTO_PROC_MSG || 'INPL');
428 FND_FILE.NEW_LINE(FND_FILE.LOG);
429 DPP_UTILITY_PVT.DEBUG_MESSAGE( FND_LOG.LEVEL_STATEMENT, 'dpp.plsql.' || L_FULL_NAME, '*' || G_AUTO_PROC_MSG || 'INPL');
430 END IF;
431 END LOOP;
432 IF NOT l_flag THEN
433 FND_FILE.PUT_LINE(FND_FILE.LOG, G_NO_REC_PROC_MSG || 'INPL');
434 FND_FILE.NEW_LINE(FND_FILE.LOG);
435 DPP_UTILITY_PVT.DEBUG_MESSAGE( FND_LOG.LEVEL_STATEMENT, 'dpp.plsql.' || L_FULL_NAME, '*' || G_NO_REC_PROC_MSG || 'INPL');
436 END IF;
437
438 l_flag := FALSE;
439 FOR get_auto_flag_rec IN get_auto_flag_csr(p_supp_trade_profile_id, 'OUTPL', p_transaction_header_id) LOOP
440 l_flag := TRUE;
441 IF get_auto_flag_rec.automatic_flag = 'Y' THEN
442 EXECUTE_PROCESS(L_API_VERSION, FND_API.G_FALSE, FND_API.G_FALSE, FND_API.G_VALID_LEVEL_FULL, X_RETURN_STATUS, X_MSG_COUNT, X_MSG_DATA,
443 P_TRANSACTION_HEADER_ID,P_TRANSACTION_NUMBER, 'OUTPL');
444 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
445 RAISE FND_API.G_EXC_ERROR;
446 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
447 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
448 END IF;
449 ELSE
450 FND_FILE.PUT_LINE(FND_FILE.LOG, G_AUTO_PROC_MSG || 'OUTPL');
451 FND_FILE.NEW_LINE(FND_FILE.LOG);
452 DPP_UTILITY_PVT.DEBUG_MESSAGE( FND_LOG.LEVEL_STATEMENT, 'dpp.plsql.' || L_FULL_NAME, '*' || G_AUTO_PROC_MSG || 'OUTPL');
453 END IF;
454 END LOOP;
455 IF NOT l_flag THEN
456 FND_FILE.PUT_LINE(FND_FILE.LOG, G_NO_REC_PROC_MSG || 'OUTPL');
457 FND_FILE.NEW_LINE(FND_FILE.LOG);
458 DPP_UTILITY_PVT.DEBUG_MESSAGE( FND_LOG.LEVEL_STATEMENT, 'dpp.plsql.' || L_FULL_NAME, '*' || G_NO_REC_PROC_MSG || 'OUTPL');
459 END IF;
460
461 l_flag := FALSE;
462 FOR get_auto_flag_rec IN get_auto_flag_csr(p_supp_trade_profile_id, 'PROMO', p_transaction_header_id) LOOP
463 l_flag := TRUE;
464 IF get_auto_flag_rec.automatic_flag = 'Y' THEN
465 EXECUTE_PROCESS(L_API_VERSION, FND_API.G_FALSE, FND_API.G_FALSE, FND_API.G_VALID_LEVEL_FULL, X_RETURN_STATUS, X_MSG_COUNT, X_MSG_DATA,
466 P_TRANSACTION_HEADER_ID,P_TRANSACTION_NUMBER, 'PROMO');
467 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
468 RAISE FND_API.G_EXC_ERROR;
469 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
470 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
471 END IF;
472 ELSE
473 FND_FILE.PUT_LINE(FND_FILE.LOG, G_AUTO_PROC_MSG || 'PROMO');
474 FND_FILE.NEW_LINE(FND_FILE.LOG);
475 DPP_UTILITY_PVT.DEBUG_MESSAGE( FND_LOG.LEVEL_STATEMENT, 'dpp.plsql.' || L_FULL_NAME, '*' || G_AUTO_PROC_MSG || 'PROMO');
476 END IF;
477 END LOOP;
478 IF NOT l_flag THEN
479 FND_FILE.PUT_LINE(FND_FILE.LOG, G_NO_REC_PROC_MSG || 'PROMO');
480 FND_FILE.NEW_LINE(FND_FILE.LOG);
481 DPP_UTILITY_PVT.DEBUG_MESSAGE( FND_LOG.LEVEL_STATEMENT, 'dpp.plsql.' || L_FULL_NAME, '*' || G_NO_REC_PROC_MSG || 'PROMO');
482 END IF;
483
484 END LOOP;
485
486 EXCEPTION
487 WHEN FND_API.G_EXC_ERROR THEN
488 ROLLBACK TO EXECUTE_NOTIF;
489 FND_MSG_PUB.COUNT_AND_GET (P_ENCODED => FND_API.G_FALSE, P_COUNT => X_MSG_COUNT, P_DATA => X_MSG_DATA );
490 IF x_msg_count > 1 THEN
491 FOR I IN 1..x_msg_count LOOP
492 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
493 END LOOP;
494 END IF;
495 FND_FILE.PUT_LINE(FND_FILE.LOG,substr(('Error Message : ' || x_msg_data),1,4000));
496 FND_FILE.NEW_LINE(FND_FILE.LOG);
497
498 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
499 ROLLBACK TO EXECUTE_NOTIF;
500 FND_MSG_PUB.COUNT_AND_GET (P_ENCODED => FND_API.G_FALSE, P_COUNT => X_MSG_COUNT, P_DATA => X_MSG_DATA );
501 IF x_msg_count > 1 THEN
502 FOR I IN 1..x_msg_count LOOP
503 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
504 END LOOP;
505 END IF;
506 FND_FILE.PUT_LINE(FND_FILE.LOG,substr(('Error Message : ' || x_msg_data),1,4000));
507 FND_FILE.NEW_LINE(FND_FILE.LOG);
508
509 WHEN OTHERS THEN
510 ROLLBACK TO EXECUTE_NOTIF;
511 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
512 fnd_message.set_token('ROUTINE', l_full_name);
513 fnd_message.set_token('ERRNO', sqlcode);
514 fnd_message.set_token('REASON', sqlerrm);
515 FND_MSG_PUB.add;
516
517 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE, p_count => x_msg_count, p_data => x_msg_data );
518 IF x_msg_count > 1 THEN
519 FOR I IN 1..x_msg_count LOOP
520 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
521 END LOOP;
522 END IF;
523 FND_FILE.PUT_LINE(FND_FILE.LOG,substr(('Error Message : ' || x_msg_data),1,4000));
524 FND_FILE.NEW_LINE(FND_FILE.LOG);
525
526
527 END EXECUTE_NOTIF_PROCESSES;
528
529
530 ---------------------------------------------------------------------
531 -- PROCEDURE
532 -- EXECUTE_PROCESS
533 --
534 -- PURPOSE
535 -- Executes a process depending on the process code.
536 --
537 -- PARAMETERS
538 -- transaction header id, transaction number, process code.
539 ----------------------------------------------------------------------
540 PROCEDURE EXECUTE_PROCESS(P_API_VERSION IN NUMBER,
541 P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
542 P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
543 P_VALIDATION_LEVEL IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
544 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
545 X_MSG_COUNT OUT NOCOPY NUMBER,
546 X_MSG_DATA OUT NOCOPY VARCHAR2,
547 P_TRANSACTION_HEADER_ID IN VARCHAR2,
548 P_TRANSACTION_NUMBER IN VARCHAR2,
549 P_PROCESS_CODE IN VARCHAR2)
550 IS
551
552 L_API_VERSION CONSTANT NUMBER := 1.0;
553 L_API_NAME CONSTANT VARCHAR2(30) := 'EXECUTE_PROCESS';
554 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| L_API_NAME;
555
556 L_TXN_HDR_REC DPP_BUSINESSEVENTS_PVT.DPP_TXN_HDR_REC_TYPE;
557 L_TXN_LINE_ID DPP_BUSINESSEVENTS_PVT.DPP_TXN_LINE_TBL_TYPE;
558
559 L_FLAG BOOLEAN := FALSE;
560
561
562 BEGIN
563
564 SAVEPOINT EXEC_PROCESS;
565
566 -- Standard call to check for call compatibility.
567 IF NOT FND_API.COMPATIBLE_API_CALL (L_API_VERSION, P_API_VERSION, L_API_NAME, G_PKG_NAME)
568 THEN
569 DPP_UTILITY_PVT.DEBUG_MESSAGE( FND_LOG.LEVEL_ERROR, 'dpp.plsql.' || L_FULL_NAME, L_API_NAME || ' : Not a compatible API call.');
570 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
571 END IF;
572
573 DPP_UTILITY_PVT.DEBUG_MESSAGE( FND_LOG.LEVEL_PROCEDURE, 'dpp.plsql.' || L_FULL_NAME, L_API_NAME||': START');
574
575 --Initialize message list if p_init_msg_list is TRUE.
576 IF FND_API.TO_BOOLEAN (P_INIT_MSG_LIST) THEN
577 FND_MSG_PUB.INITIALIZE;
578 END IF;
579
580 -- Initialize API return status to sucess
581 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
582
583 L_TXN_HDR_REC := NULL;
584 L_TXN_LINE_ID.DELETE();
585 L_TXN_HDR_REC.TRANSACTION_HEADER_ID := P_TRANSACTION_HEADER_ID;
586 L_TXN_HDR_REC.TRANSACTION_NUMBER := P_TRANSACTION_NUMBER;
587 L_TXN_HDR_REC.PROCESS_CODE := P_PROCESS_CODE;
588
589 --Raise business event for the process
590 DPP_BUSINESSEVENTS_PVT.RAISE_BUSINESS_EVENT( P_API_VERSION => 1.0
591 ,P_INIT_MSG_LIST => FND_API.G_FALSE
592 ,P_COMMIT => FND_API.G_FALSE
593 ,P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL
594 ,X_RETURN_STATUS => X_RETURN_STATUS
595 ,X_MSG_COUNT => X_MSG_COUNT
596 ,X_MSG_DATA => X_MSG_DATA
597 ,P_TXN_HDR_REC => L_TXN_HDR_REC
598 ,P_TXN_LINE_ID => L_TXN_LINE_ID
599 );
600
601 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
602 RAISE FND_API.G_EXC_ERROR;
603 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
604 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
605 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_SUCCESS THEN
606 FND_FILE.PUT_LINE(FND_FILE.LOG,'Business event raised for the process code : ' || P_PROCESS_CODE);
607 FND_FILE.NEW_LINE(FND_FILE.LOG);
608 DPP_UTILITY_PVT.DEBUG_MESSAGE( FND_LOG.LEVEL_STATEMENT, 'dpp.plsql.' || L_FULL_NAME, '*Business event raised for the process code : ' || P_PROCESS_CODE);
609 END IF;
610
611 EXCEPTION
612 WHEN FND_API.G_EXC_ERROR THEN
613 ROLLBACK TO EXEC_PROCESS;
614 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE, p_count => x_msg_count, p_data => x_msg_data );
615 IF x_msg_count > 1 THEN
616 FOR I IN 1..x_msg_count LOOP
617 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
618 END LOOP;
619 END IF;
620 FND_FILE.PUT_LINE(FND_FILE.LOG, G_NO_EVT_MSG || P_PROCESS_CODE);
621 FND_FILE.NEW_LINE(FND_FILE.LOG);
622 FND_FILE.PUT_LINE(FND_FILE.LOG,substr(('Error Message : ' || x_msg_data),1,4000));
623 FND_FILE.NEW_LINE(FND_FILE.LOG);
624
625 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
626 ROLLBACK TO EXEC_PROCESS;
627 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE, p_count => x_msg_count, p_data => x_msg_data );
628 IF x_msg_count > 1 THEN
629 FOR I IN 1..x_msg_count LOOP
630 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
631 END LOOP;
632 END IF;
633 FND_FILE.PUT_LINE(FND_FILE.LOG, G_NO_EVT_MSG || P_PROCESS_CODE);
634 FND_FILE.NEW_LINE(FND_FILE.LOG);
635 FND_FILE.PUT_LINE(FND_FILE.LOG,substr(('Error Message : ' || x_msg_data),1,4000));
636 FND_FILE.NEW_LINE(FND_FILE.LOG);
637
638 WHEN OTHERS THEN
639 ROLLBACK TO EXEC_PROCESS;
640 FND_MESSAGE.SET_NAME('FND', 'SQL_PLSQL_ERROR');
641 FND_MESSAGE.SET_TOKEN('ROUTINE', L_FULL_NAME);
642 FND_MESSAGE.SET_TOKEN('ERRNO', SQLCODE);
643 FND_MESSAGE.SET_TOKEN('REASON', SQLERRM);
644 FND_MSG_PUB.ADD;
645 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE, p_count => x_msg_count, p_data => x_msg_data );
646 IF x_msg_count > 1 THEN
647 FOR I IN 1..x_msg_count LOOP
648 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
649 END LOOP;
650 END IF;
651 FND_FILE.PUT_LINE(FND_FILE.LOG, G_NO_EVT_MSG || P_PROCESS_CODE);
652 FND_FILE.NEW_LINE(FND_FILE.LOG);
653 FND_FILE.PUT_LINE(FND_FILE.LOG,substr(('Error Message : ' || x_msg_data),1,4000));
654 FND_FILE.NEW_LINE(FND_FILE.LOG);
655
656
657 END EXECUTE_PROCESS;
658
659
660 ---------------------------------------------------------------------
661 -- PROCEDURE
662 -- GET_SUPPLIER_TRADE_PROFILE_ID
663 --
664 -- PURPOSE
665 -- Returns the supplier trade profile id.
666 --
667 -- PARAMETERS
668 -- vendor id, vendor site id, org id.
669 ----------------------------------------------------------------------
670 FUNCTION GET_SUPPLIER_TRADE_PROFILE_ID ( P_VENDOR_ID IN VARCHAR2, P_VENDOR_SITE_ID IN VARCHAR2, P_ORG_ID IN VARCHAR2) RETURN NUMBER IS
671 L_RET_ID NUMBER;
672 L_API_NAME CONSTANT VARCHAR2(30) := 'GET_SUPPLIER_TRADE_PROFILE_ID';
673 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| L_API_NAME;
674 BEGIN
675 SELECT SUPP_TRADE_PROFILE_ID
676 INTO L_RET_ID
677 FROM OZF_SUPP_TRD_PRFLS_ALL
678 WHERE SUPPLIER_ID = P_VENDOR_ID
679 AND SUPPLIER_SITE_ID = P_VENDOR_SITE_ID
680 AND ORG_ID = P_ORG_ID;
681
682 RETURN (L_RET_ID);
683
684 EXCEPTION
685 WHEN NO_DATA_FOUND THEN
686 L_RET_ID := null;
687 fnd_message.set_name( 'DPP','DPP_SUPP_TRDPRFLS_MISSING_ERR');
688 fnd_msg_pub.add;
689 DPP_UTILITY_PVT.DEBUG_MESSAGE( FND_LOG.LEVEL_EXCEPTION, 'dpp.plsql.' || L_FULL_NAME, 'Supplier trade profile setup not available');
690 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Supplier trade profile setup not available');
691 FND_FILE.NEW_LINE(FND_FILE.LOG);
692 RAISE FND_API.G_EXC_ERROR;
693 WHEN OTHERS THEN
694 L_RET_ID := null;
695 DPP_UTILITY_PVT.DEBUG_MESSAGE( FND_LOG.LEVEL_EXCEPTION, 'dpp.plsql.' || L_FULL_NAME, 'Exception while fetching supp_trade_profile_id: ' || SQLERRM);
696 FND_FILE.PUT_LINE(FND_FILE.LOG,'Exception while fetching supp_trade_profile_id: ' || SQLERRM);
697 FND_FILE.NEW_LINE(FND_FILE.LOG);
698 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
699
700 END GET_SUPPLIER_TRADE_PROFILE_ID;
701
702
703 ---------------------------------------------------------------------
704 -- PROCEDURE
705 -- IS_PROCESS_SETUP
706 --
707 -- PURPOSE
708 -- Checks if the price protection setup is done for a supplier with the specified id.
709 -- If the id is null, the system parameter is checked.
710 --
711 -- PARAMETERS
712 -- Supplier trade profile id, org id.
713 ----------------------------------------------------------------------
714 FUNCTION IS_PROCESS_SETUP ( P_SUPP_TRADE_PROFILE_ID IN NUMBER, P_ORG_ID IN NUMBER ) RETURN BOOLEAN IS
715
716 L_COUNT NUMBER := 0;
717
718 L_API_NAME CONSTANT VARCHAR2(30) := 'IS_PROCESS_SETUP';
719 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| L_API_NAME;
720
721 CURSOR GET_PROCESS_SETUP_CNT_CSR (P_SUPP_TRADE_PROFILE_ID NUMBER, P_ORG_ID NUMBER)
722 IS
723 SELECT COUNT(1)
724 FROM OZF_PROCESS_SETUP_ALL
725 WHERE NVL(SUPP_TRADE_PROFILE_ID,0) = NVL(P_SUPP_TRADE_PROFILE_ID,0)
726 AND ENABLED_FLAG = 'Y'
727 AND ORG_ID = P_ORG_ID;
728
729 BEGIN
730
731 OPEN GET_PROCESS_SETUP_CNT_CSR(P_SUPP_TRADE_PROFILE_ID, P_ORG_ID);
732 FETCH GET_PROCESS_SETUP_CNT_CSR INTO L_COUNT;
733 CLOSE GET_PROCESS_SETUP_CNT_CSR;
734
735 IF L_COUNT = 0 THEN --Process setup does not exist for the supplier.
736 RETURN FALSE;
737 ELSE
738 RETURN TRUE;
739 END IF;
740
741 EXCEPTION
742 WHEN OTHERS THEN
743 DPP_UTILITY_PVT.DEBUG_MESSAGE( FND_LOG.LEVEL_EXCEPTION, 'dpp.plsql.' || L_FULL_NAME, 'Exception while fetching from OZF_PROCESS_SETUP_ALL: ' || SQLERRM);
744 FND_FILE.PUT_LINE(FND_FILE.LOG,'Exception while fetching from OZF_PROCESS_SETUP_ALL: ' || SQLERRM);
745 FND_FILE.NEW_LINE(FND_FILE.LOG);
746 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
747
748 END IS_PROCESS_SETUP;
749
750
751 ---------------------------------------------------------------------
752 -- PROCEDURE
753 -- GET_PROCESS_SETUP_ID
754 --
755 -- PURPOSE
756 -- Returns the supplier trade profile id if the setup is available, else returns null.
757 --
758 -- PARAMETERS
759 -- vendor id, vendor site id, org id.
760 ----------------------------------------------------------------------
761 FUNCTION GET_PROCESS_SETUP_ID ( P_VENDOR_ID IN VARCHAR2, P_VENDOR_SITE_ID IN VARCHAR2, P_ORG_ID IN VARCHAR2 ) RETURN NUMBER IS
762
763 L_SUPP_TRADE_PROFILE_ID NUMBER;
764
765 L_API_NAME CONSTANT VARCHAR2(30) := 'GET_PROCESS_SETUP_ID';
766 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| L_API_NAME;
767
768 BEGIN
769
770 L_SUPP_TRADE_PROFILE_ID := GET_SUPPLIER_TRADE_PROFILE_ID(P_VENDOR_ID, P_VENDOR_SITE_ID, P_ORG_ID);
771
772 --Check if the Process Setup is done for the Supplier, Supplier site and Operating Unit
773 IF NOT IS_PROCESS_SETUP(L_SUPP_TRADE_PROFILE_ID, P_ORG_ID) THEN
774 L_SUPP_TRADE_PROFILE_ID := NULL;
775 DPP_UTILITY_PVT.DEBUG_MESSAGE( FND_LOG.LEVEL_STATEMENT, 'dpp.plsql.' || L_FULL_NAME, 'No setup at supplier trade profile for vendor id : '
776 || P_VENDOR_ID || ' at site id ' || P_VENDOR_SITE_ID || ', org id ' || P_ORG_ID);
777 --Check if setup is available at system parameter level
778 IF NOT IS_PROCESS_SETUP(NULL, P_ORG_ID) THEN
779 --Process Setup does not exist
780 fnd_message.set_name( 'DPP','DPP_PROCESS_SETUP_MISSING_ERR');
781 fnd_msg_pub.add;
782 FND_FILE.PUT_LINE(FND_FILE.LOG, 'No price protection setup available for vendor id : '
783 || P_VENDOR_ID || ' at site id ' || P_VENDOR_SITE_ID || ', org id ' || P_ORG_ID);
784 FND_FILE.NEW_LINE(FND_FILE.LOG);
785 DPP_UTILITY_PVT.DEBUG_MESSAGE( FND_LOG.LEVEL_STATEMENT, 'dpp.plsql.' || L_FULL_NAME, 'No price protection setup available for vendor id : '
786 || P_VENDOR_ID || ' at site id ' || P_VENDOR_SITE_ID || ', org id ' || P_ORG_ID );
787 RAISE FND_API.G_EXC_ERROR;
788 END IF;
789 END IF;
790
791 RETURN L_SUPP_TRADE_PROFILE_ID;
792
793 END GET_PROCESS_SETUP_ID;
794
795
796 ---------------------------------------------------------------------
797 -- PROCEDURE
798 -- INITIATE_NOTIFICATION_PROCESS
799 --
800 -- PURPOSE
801 -- Initiates the notification Execution Process for transactions under an org.
802 --
803 -- PARAMETERS
804 -- org id.
805 ----------------------------------------------------------------------
806 PROCEDURE INITIATE_NOTIFICATION_PROCESS(ERRBUF OUT NOCOPY VARCHAR2,
807 RETCODE OUT NOCOPY VARCHAR2,
808 P_IN_ORG_ID IN NUMBER
809 )
810 IS
811 L_API_VERSION CONSTANT NUMBER := 1.0;
812 L_API_NAME CONSTANT VARCHAR2(30) := 'INITIATE_NOTIFICATION_PROCESS';
813 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| L_API_NAME;
814
815 L_RETURN_STATUS VARCHAR2(10);
816 L_MSG_DATA VARCHAR2(4000);
817 L_MSG_COUNT NUMBER;
818
819 L_TXN_HDR_REC DPP_BUSINESSEVENTS_PVT.DPP_TXN_HDR_REC_TYPE;
820 L_TXN_LINE_ID DPP_BUSINESSEVENTS_PVT.DPP_TXN_LINE_TBL_TYPE;
821
822 L_SUPP_TRADE_PROFILE_ID NUMBER;
823
824 L_DAYS NUMBER;
825 NO_TXN_FLAG BOOLEAN := TRUE;
826
827 CURSOR get_txn_to_notify_csr (p_days in number)
828 IS
829 SELECT dpp.transaction_header_id,
830 dpp.transaction_number,
831 dpp.vendor_id,
832 dpp.vendor_site_id
833 FROM dpp_transaction_headers_all dpp
834 WHERE dpp.transaction_status = 'ACTIVE'
835 AND trunc(dpp.effective_start_date) = trunc(sysdate)+p_days
836 AND to_number(dpp.org_id) = p_in_org_id;
837
838 BEGIN
839
840 ERRBUF := 'SUCCESS';
841 RETCODE := 0;
842
843 SAVEPOINT INITIATE_NOTIF;
844
845 DPP_UTILITY_PVT.DEBUG_MESSAGE( FND_LOG.LEVEL_STATEMENT, 'dpp.plsql.' || L_FULL_NAME, 'Public API: ' || l_api_name || ' started at: ' || to_char(sysdate,'dd-mon-yyyy hh24:mi:ss'));
846
847 --Initialize message list
848 FND_MSG_PUB.INITIALIZE;
849
850 -- Initialize API return status to sucess
851 L_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
852
853 BEGIN
854 SELECT AUTOMATE_NOTIFICATION_DAYS INTO L_DAYS
855 FROM OZF_SYS_PARAMETERS_ALL
856 WHERE ORG_ID = P_IN_ORG_ID;
857 EXCEPTION
858 WHEN NO_DATA_FOUND THEN
859 retcode := 2;
860 errbuf := 'Error';
861 FND_FILE.PUT_LINE(FND_FILE.LOG, G_NO_DAYS_MSG || P_IN_ORG_ID);
862 FND_FILE.NEW_LINE(FND_FILE.LOG);
863 DPP_UTILITY_PVT.DEBUG_MESSAGE( FND_LOG.LEVEL_EXCEPTION, 'dpp.plsql.' || L_FULL_NAME, '*' || G_NO_DAYS_MSG || P_IN_ORG_ID);
864
865 END;
866
867 DPP_UTILITY_PVT.DEBUG_MESSAGE( FND_LOG.LEVEL_STATEMENT, 'dpp.plsql.' || L_FULL_NAME, L_API_NAME || ' Number of days for auto notif:' || L_DAYS);
868
869 IF L_DAYS >= 0
870 THEN
871 FOR get_txn_to_notify_rec IN get_txn_to_notify_csr(l_days) LOOP
872 FND_FILE.PUT_LINE(FND_FILE.LOG, G_PRCSING_TXN_MSG || get_txn_to_notify_rec.transaction_number);
873 FND_FILE.NEW_LINE(FND_FILE.LOG);
874 DPP_UTILITY_PVT.DEBUG_MESSAGE( FND_LOG.LEVEL_STATEMENT, 'dpp.plsql.' || L_FULL_NAME, '*' || G_PRCSING_TXN_MSG || get_txn_to_notify_rec.transaction_number);
875
876 NO_TXN_FLAG := FALSE;
877
878 L_SUPP_TRADE_PROFILE_ID := GET_PROCESS_SETUP_ID(GET_TXN_TO_NOTIFY_REC.VENDOR_ID, GET_TXN_TO_NOTIFY_REC.VENDOR_SITE_ID, P_IN_ORG_ID);
879 EXECUTE_NOTIF_PROCESSES(L_API_VERSION, FND_API.G_FALSE,FND_API.G_FALSE, FND_API.G_VALID_LEVEL_FULL, L_RETURN_STATUS, L_MSG_COUNT, L_MSG_DATA,
880 l_supp_trade_profile_id,p_in_org_id, get_txn_to_notify_rec.transaction_header_id,get_txn_to_notify_rec.transaction_number);
881
882 IF L_RETURN_STATUS = FND_API.G_RET_STS_SUCCESS THEN
883 FND_FILE.PUT_LINE(FND_FILE.LOG, G_AUTO_EVT_SUCCESS_MSG);
884 FND_FILE.NEW_LINE(FND_FILE.LOG);
885 DPP_UTILITY_PVT.DEBUG_MESSAGE( FND_LOG.LEVEL_STATEMENT, 'dpp.plsql.' || L_FULL_NAME, '*' || G_AUTO_EVT_SUCCESS_MSG );
886 ELSE
887 retcode := 1;
888 errbuf := 'Warning';
889 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE, p_count => l_msg_count, p_data => l_msg_data );
890 IF l_msg_count > 1 THEN
891 FOR I IN 1..l_msg_count LOOP
892 l_msg_data := SUBSTR((l_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
893 END LOOP;
894 END IF;
895 FND_FILE.PUT_LINE(FND_FILE.LOG,G_ERR_AUTO_NTF_MSG);
896 FND_FILE.NEW_LINE(FND_FILE.LOG);
897 FND_FILE.PUT_LINE(FND_FILE.LOG,substr(('Error Message : ' || l_msg_data),1,4000));
898 FND_FILE.NEW_LINE(FND_FILE.LOG);
899 END IF;
900
901 END LOOP;
902
903 IF NO_TXN_FLAG THEN
904 FND_FILE.PUT_LINE(FND_FILE.LOG, G_NO_TXN_MSG || L_DAYS);
905 FND_FILE.NEW_LINE(FND_FILE.LOG);
906 DPP_UTILITY_PVT.DEBUG_MESSAGE( FND_LOG.LEVEL_STATEMENT, 'dpp.plsql.' || L_FULL_NAME, '*' || G_NO_TXN_MSG || L_DAYS);
907 END IF;
908
909 END IF;
910
911 --Commit the changes
912 COMMIT;
913
914 EXCEPTION
915 WHEN FND_API.G_EXC_ERROR THEN
916 ROLLBACK TO INITIATE_NOTIF;
917 retcode := 2;
918 errbuf := 'Error';
919 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE, p_count => l_msg_count, p_data => l_msg_data );
920 IF l_msg_count > 1 THEN
921 FOR I IN 1..l_msg_count LOOP
922 l_msg_data := SUBSTR((l_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
923 END LOOP;
924 END IF;
925 FND_FILE.PUT_LINE(FND_FILE.LOG,G_ERR_AUTO_NTF_MSG);
926 FND_FILE.NEW_LINE(FND_FILE.LOG);
927 FND_FILE.PUT_LINE(FND_FILE.LOG,substr(('Error Message : ' || l_msg_data),1,4000));
928 FND_FILE.NEW_LINE(FND_FILE.LOG);
929
930 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
931 ROLLBACK TO INITIATE_NOTIF;
932 retcode := 2;
933 errbuf := 'Error';
934 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE, p_count => l_msg_count, p_data => l_msg_data );
935 IF l_msg_count > 1 THEN
936 FOR I IN 1..l_msg_count LOOP
937 l_msg_data := SUBSTR((l_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
938 END LOOP;
939 END IF;
940 FND_FILE.PUT_LINE(FND_FILE.LOG, G_ERR_AUTO_NTF_MSG);
941 FND_FILE.NEW_LINE(FND_FILE.LOG);
942 FND_FILE.PUT_LINE(FND_FILE.LOG,substr(('Error Message : ' || l_msg_data),1,4000));
943 FND_FILE.NEW_LINE(FND_FILE.LOG);
944 WHEN OTHERS THEN
945 ROLLBACK TO INITIATE_NOTIF;
946 retcode := 2;
947 errbuf := 'Error';
948 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
949 fnd_message.set_token('ROUTINE', l_full_name);
950 fnd_message.set_token('ERRNO', sqlcode);
951 fnd_message.set_token('REASON', sqlerrm);
952 FND_MSG_PUB.add;
953
954 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE, p_count => l_msg_count, p_data => l_msg_data );
955 IF l_msg_count > 1 THEN
956 FOR I IN 1..l_msg_count LOOP
957 l_msg_data := SUBSTR((l_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
958 END LOOP;
959 END IF;
960 FND_FILE.PUT_LINE(FND_FILE.LOG, G_ERR_AUTO_NTF_MSG);
961 FND_FILE.NEW_LINE(FND_FILE.LOG);
962 FND_FILE.PUT_LINE(FND_FILE.LOG,substr(('Error Message : ' || l_msg_data),1,4000));
963 FND_FILE.NEW_LINE(FND_FILE.LOG);
964
965 END Initiate_Notification_Process;
966
967 ---------------------------------------------------------------------
968 -- PROCEDURE
969 -- Populate_ExecutionProcess
970 --
971 -- PURPOSE
972 -- Populate Execution Process as soon as the transaction is created
973 --
974 -- PARAMETERS
975 --
976 -- NOTES
977 -- 1.
978 -- 2.
979 ----------------------------------------------------------------------
980 PROCEDURE populate_ExecutionProcess(
981 p_api_version_number IN NUMBER,
982 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
983 p_commit IN VARCHAR2 := FND_API.G_FALSE,
984 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
985 x_return_status OUT NOCOPY VARCHAR2,
986 x_msg_count OUT NOCOPY NUMBER,
987 x_msg_data OUT NOCOPY VARCHAR2,
988 p_org_id IN NUMBER,
989 p_txn_hdr_id IN NUMBER,
990 p_txn_number IN VARCHAR2,
991 p_vendor_id IN NUMBER,
992 p_vendor_site_id IN NUMBER
993 )
994 IS
995 l_api_name CONSTANT VARCHAR2(30) := 'populate_ExecutionProcess';
996 l_api_version_number CONSTANT NUMBER := 1.0;
997
998 l_supp_trade_profile_id NUMBER;
999 l_exe_process_cnt NUMBER := 0;
1000
1001 BEGIN
1002 -- Standard Start of API savepoint
1003 SAVEPOINT POPULATE_EXECUTIONPROCESS;
1004
1005 -- Standard call to check for call compatibility.
1006 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1007 p_api_version_number,
1008 l_api_name,
1009 G_PKG_NAME)
1010 THEN
1011 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1012 END IF;
1013
1014 -- Initialize message list if p_init_msg_list is set to TRUE.
1015 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1016 FND_MSG_PUB.initialize;
1017 END IF;
1018
1019 -- Initialize API return status to SUCCESS
1020 x_return_status := FND_API.G_RET_STS_SUCCESS;
1021
1022 L_SUPP_TRADE_PROFILE_ID := GET_PROCESS_SETUP_ID(P_VENDOR_ID, P_VENDOR_SITE_ID, P_ORG_ID);
1023
1024 --Process Setup exist
1025 --Insert records into DPP_EXECUTION_PROCESSES based on the Execution Process setup either at STP or System Parameters
1026 InsertExecProcesses(
1027 p_txn_hdr_id => p_txn_hdr_id,
1028 p_org_id => p_org_id,
1029 p_supp_trd_prfl_id => l_supp_trade_profile_id,
1030 x_msg_count => x_msg_count,
1031 x_msg_data => x_msg_data,
1032 x_return_status => x_return_status );
1033
1034 EXCEPTION
1035 WHEN FND_API.G_EXC_ERROR THEN
1036 ROLLBACK TO POPULATE_EXECUTIONPROCESS;
1037 x_return_status := FND_API.G_RET_STS_ERROR;
1038 FND_FILE.PUT_LINE(FND_FILE.LOG,G_NO_SETUP_MSG);
1039 FND_FILE.NEW_LINE(FND_FILE.LOG);
1040 -- Standard call to get message count and if count=1, get the message
1041 FND_MSG_PUB.Count_And_Get (
1042 p_encoded => FND_API.G_FALSE,
1043 p_count => x_msg_count,
1044 p_data => x_msg_data
1045 );
1046 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1047 ROLLBACK TO POPULATE_EXECUTIONPROCESS;
1048 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1049 FND_FILE.PUT_LINE(FND_FILE.LOG,'Unexpected error occured: ' || SQLERRM);
1050 FND_FILE.NEW_LINE(FND_FILE.LOG);
1051 -- Standard call to get message count and if count=1, get the message
1052 FND_MSG_PUB.Count_And_Get (
1053 p_encoded => FND_API.G_FALSE,
1054 p_count => x_msg_count,
1055 p_data => x_msg_data
1056 );
1057 WHEN OTHERS THEN
1058 ROLLBACK TO POPULATE_EXECUTIONPROCESS;
1059 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1060 FND_FILE.PUT_LINE(FND_FILE.LOG,'Unexpected error occured: ' || SQLERRM);
1061 FND_FILE.NEW_LINE(FND_FILE.LOG);
1062 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1063 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1064 END IF;
1065 -- Standard call to get message count and if count=1, get the message
1066 FND_MSG_PUB.Count_And_Get (
1067 p_encoded => FND_API.G_FALSE,
1068 p_count => x_msg_count,
1069 p_data => x_msg_data
1070 );
1071
1072 END Populate_ExecutionProcess;
1073
1074 ---------------------------------------------------------------------
1075 -- PROCEDURE
1076 -- InsertExecProcesses
1077 --
1078 -- PURPOSE
1079 -- Insert Execution Process as soon as the transaction is created
1080 --
1081 -- PARAMETERS
1082 --
1083 -- NOTES
1084 -- 1.
1085 -- 2.
1086 ----------------------------------------------------------------------
1087 PROCEDURE InsertExecProcesses(
1088 p_txn_hdr_id IN NUMBER,
1089 p_org_id IN NUMBER,
1090 p_supp_trd_prfl_id IN NUMBER,
1091 x_msg_count OUT NOCOPY NUMBER,
1092 x_msg_data OUT NOCOPY VARCHAR2,
1093 x_return_status OUT NOCOPY VARCHAR2
1094 )
1095 IS
1096 l_api_name CONSTANT VARCHAR2(30) := 'InsertExecProcesses';
1097 l_api_version_number CONSTANT NUMBER := 1.0;
1098
1099 l_user_id NUMBER := FND_GLOBAL.USER_ID;
1100 l_login_id NUMBER := FND_GLOBAL.LOGIN_ID;
1101
1102 l_exe_process_cnt NUMBER := 0;
1103
1104 --Cursor to get the count of execution processes for the transaction
1105 CURSOR get_exec_processes_cnt(p_txn_hdr_id NUMBER)
1106 IS
1107 SELECT COUNT(1)
1108 FROM DPP_EXECUTION_PROCESSES
1109 WHERE transaction_header_id = p_txn_hdr_id;
1110
1111 --Cursor to retrieve the process codes from look up
1112 CURSOR get_process_codes_csr (p_supp_trd_prf_id NUMBER)
1113 IS
1114 SELECT dppl.lookup_code
1115 FROM dpp_lookups dppl,
1116 OZF_PROCESS_SETUP_ALL opsa
1117 WHERE dppl.lookup_type = 'DPP_EXECUTION_PROCESSES'
1118 AND dppl.tag is not null
1119 AND nvl(opsa.supp_trade_profile_id,0) = nvl(p_supp_trd_prf_id,0)
1120 AND opsa.enabled_flag = 'Y'
1121 AND opsa.org_id = p_org_id
1122 AND dppl.lookup_code = opsa.process_code;
1123
1124 BEGIN
1125 SAVEPOINT InsertExecProcesses;
1126
1127 -- Initialize API return status to SUCCESS
1128 x_return_status := FND_API.G_RET_STS_SUCCESS;
1129
1130 BEGIN --Check if the transaction exists in DPP_EXECUTION_PROCESSES table
1131 OPEN get_exec_processes_cnt(p_txn_hdr_id);
1132 FETCH get_exec_processes_cnt INTO l_exe_process_cnt;
1133 CLOSE get_exec_processes_cnt;
1134 EXCEPTION
1135 WHEN OTHERS THEN
1136 FND_FILE.PUT_LINE(FND_FILE.LOG,'Exception while checking if the transaction exists in DPP_EXECUTION_PROCESSES table: '||SQLERRM);
1137 FND_FILE.NEW_LINE(FND_FILE.LOG);
1138 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1139 END;
1140
1141 IF l_exe_process_cnt > 0 THEN
1142 BEGIN
1143 DELETE FROM DPP_EXECUTION_PROCESSES
1144 WHERE transaction_header_id = p_txn_hdr_id;
1145 EXCEPTION
1146 WHEN OTHERS THEN
1147 FND_FILE.PUT_LINE(FND_FILE.LOG,'Exception while deleting the records from DPP_EXECUTION_PROCESSES table: ' || SQLERRM);
1148 FND_FILE.NEW_LINE(FND_FILE.LOG);
1149 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1150 END;
1151 END IF;
1152
1153 FOR get_process_codes_rec IN get_process_codes_csr(p_supp_trd_prfl_id)
1154 LOOP
1155 BEGIN
1156 --Insert the Process codes into the DPP_EXECUTION_PROCESSES table
1157 INSERT INTO DPP_EXECUTION_PROCESSES (process_code,
1158 transaction_header_id,
1159 created_by,
1160 creation_date,
1161 last_updated_by,
1162 last_update_date,
1163 last_update_login
1164 )
1165 VALUES (get_process_codes_rec.lookup_code,
1166 p_txn_hdr_id,
1167 l_user_id,
1168 sysdate,
1169 l_user_id,
1170 sysdate,
1171 l_login_id
1172 );
1173 EXCEPTION
1174 WHEN OTHERS THEN
1175 FND_FILE.PUT_LINE(FND_FILE.LOG,'Exception while inserting into DPP_EXECUTION_PROCESSES: ' || SQLERRM);
1176 FND_FILE.NEW_LINE(FND_FILE.LOG);
1177 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1178 END;
1179 END LOOP;
1180
1181 EXCEPTION
1182 WHEN FND_API.G_EXC_ERROR THEN
1183 ROLLBACK TO InsertExecProcesses;
1184 x_return_status := FND_API.G_RET_STS_ERROR;
1185 FND_FILE.PUT_LINE(FND_FILE.LOG,G_NO_SETUP_MSG);
1186 FND_FILE.NEW_LINE(FND_FILE.LOG);
1187 -- Standard call to get message count and if count=1, get the message
1188 FND_MSG_PUB.Count_And_Get (
1189 p_encoded => FND_API.G_FALSE,
1190 p_count => x_msg_count,
1191 p_data => x_msg_data
1192 );
1193 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1194 ROLLBACK TO InsertExecProcesses;
1195 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1196 FND_FILE.PUT_LINE(FND_FILE.LOG,'Unexpected error occured: ' || SQLERRM);
1197 FND_FILE.NEW_LINE(FND_FILE.LOG);
1198 -- Standard call to get message count and if count=1, get the message
1199 FND_MSG_PUB.Count_And_Get (
1200 p_encoded => FND_API.G_FALSE,
1201 p_count => x_msg_count,
1202 p_data => x_msg_data
1203 );
1204 WHEN OTHERS THEN
1205 ROLLBACK TO InsertExecProcesses;
1206 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1207 FND_FILE.PUT_LINE(FND_FILE.LOG,'Unexpected error occured: ' || SQLERRM);
1208 FND_FILE.NEW_LINE(FND_FILE.LOG);
1209 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1210 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1211 END IF;
1212 -- Standard call to get message count and if count=1, get the message
1213 FND_MSG_PUB.Count_And_Get (
1214 p_encoded => FND_API.G_FALSE,
1215 p_count => x_msg_count,
1216 p_data => x_msg_data
1217 );
1218
1219 END InsertExecProcesses;
1220
1221
1222 ---------------------------------------------------------------------
1223 -- PROCEDURE
1224 -- Change_Status
1225 --
1226 -- PURPOSE
1227 -- Future dated Transactions will be moved from Active to Pending Adjustment
1228 -- status on the effective date. It raised business events for populating the
1229 -- inventory details for all those transactions. Further, Work Flow notification
1230 -- will be sent to the creators of the transactions.
1231 --
1232 -- PARAMETERS
1233 -- p_in_org_id - operating unit
1234 --
1235 -- NOTES
1236 ---------------------------------------------------------------------
1237 PROCEDURE Change_Status (
1238 errbuf OUT NOCOPY VARCHAR2
1239 ,retcode OUT NOCOPY VARCHAR2
1240 ,p_in_org_id IN NUMBER )
1241 IS
1242 --Declare the variables
1243 l_api_name CONSTANT VARCHAR2(30) := 'Change_Status';
1244 l_api_version CONSTANT NUMBER := 1.0;
1245 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
1246
1247 l_user_id NUMBER := FND_GLOBAL.USER_ID;
1248 l_login_id NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
1249 l_request_id NUMBER := FND_GLOBAL.CONC_REQUEST_ID;
1250 l_program_application_id NUMBER := FND_GLOBAL.PROG_APPL_ID;
1251 l_program_id NUMBER := FND_GLOBAL.CONC_PROGRAM_ID;
1252
1253 l_return_status VARCHAR2(10);
1254 l_msg_data VARCHAR2(4000);
1255 l_msg_count NUMBER;
1256
1257 l_org_id NUMBER := p_in_org_id;
1258 l_txn_hdr_rec DPP_BUSINESSEVENTS_PVT.dpp_txn_hdr_rec_type;
1259 l_txn_line_id DPP_BUSINESSEVENTS_PVT.dpp_txn_line_tbl_type;
1260
1261 l_approval_rec DPP_APPROVAL_PVT.approval_rec_type;
1262 l_approversOut DPP_APPROVAL_PVT.approversTable;
1263 l_final_approval_flag VARCHAR2(1) := 'N';
1264
1265 l_transaction_header_id NUMBER;
1266 l_transaction_number VARCHAR2(40);
1267 l_ref_document_number VARCHAR2(40);
1268 l_skip_adjustment_flag VARCHAR2(1) := 'N';
1269 l_skip_approval_flag VARCHAR2(1) := 'N';
1270
1271 l_eligible_txn_exist VARCHAR2(1) := 'N';
1272
1273 CURSOR eligible_txn_cur (p_org_id IN NUMBER)
1274 IS
1275 SELECT transaction_header_id,
1276 transaction_number,
1277 ref_document_number,
1278 skip_adjustment_flag,
1279 skip_approval_flag
1280 FROM dpp_transaction_headers_all dtha, ozf_supp_trd_prfls_all ostpa
1281 WHERE dtha.vendor_id = ostpa.supplier_id
1282 AND dtha.vendor_site_id = ostpa.supplier_site_id
1283 AND dtha.org_id = ostpa.org_id
1284 AND dtha.transaction_status = 'ACTIVE'
1285 AND trunc(dtha.effective_start_date) <= trunc(sysdate)
1286 AND dtha.org_id = p_org_id;
1287
1288 BEGIN
1289 SAVEPOINT Change_Status;
1290
1291 -- Initialize message list
1292 FND_MSG_PUB.initialize;
1293
1294
1295 DPP_UTILITY_PVT.DEBUG_MESSAGE( FND_LOG.LEVEL_STATEMENT, 'dpp.plsql.' || L_FULL_NAME, 'Public API: ' || l_api_name || ' started at: ' || to_char(sysdate,'dd-mon-yyyy hh24:mi:ss'));
1296
1297 -- Initialize API return status to sucess
1298 errbuf := 'Success';
1299 retcode := 0;
1300
1301 FOR eligible_txn_rec IN eligible_txn_cur(l_org_id) LOOP
1302
1303 l_transaction_header_id := eligible_txn_rec.transaction_header_id;
1304 l_transaction_number := eligible_txn_rec.transaction_number;
1305 l_ref_document_number := eligible_txn_rec.ref_document_number;
1306 l_skip_adjustment_flag := eligible_txn_rec.skip_adjustment_flag;
1307 l_skip_approval_flag := eligible_txn_rec.skip_approval_flag;
1308
1309 l_approval_rec.object_type := 'PRICE PROTECTION';
1310 l_approval_rec.object_id := l_transaction_header_id;
1311 l_approval_rec.action_code := 'SUBMIT';
1312 l_approval_rec.status_code := 'SUBMITTED_FOR_APPROVAL';
1313
1314 IF l_skip_adjustment_flag = 'Y' AND l_skip_approval_flag = 'N' THEN
1315 BEGIN
1316 DPP_APPROVAL_PVT.Get_AllApprovers(
1317 p_api_version => 1.0
1318 , p_init_msg_list => FND_API.G_FALSE
1319 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
1320 , x_return_status => l_return_status
1321 , x_msg_data => l_msg_data
1322 , x_msg_count => l_msg_count
1323 , p_approval_rec => l_approval_rec
1324 , p_approversOut => l_approversOut
1325 );
1326
1327 IF l_approversOut.COUNT = 0 THEN
1328 RAISE FND_API.G_EXC_ERROR;
1329 END IF;
1330
1331 UPDATE dpp_transaction_headers_all
1332 SET transaction_status = 'PENDING_APPROVAL',
1333 object_version_number = object_version_number +1,
1334 last_updated_by = l_user_id,
1335 last_update_date = sysdate,
1336 last_update_login = l_login_id,
1337 request_id = l_request_id,
1338 program_application_id = l_program_application_id,
1339 program_id = l_program_id,
1340 program_update_date = sysdate
1341 WHERE transaction_header_id = l_transaction_header_id;
1342
1343 DPP_APPROVAL_PVT.Process_User_Action (
1344 p_api_version => 1.0
1345 , p_init_msg_list => FND_API.G_FALSE
1346 , p_commit => FND_API.G_FALSE
1347 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
1348 , x_return_status => l_return_status
1349 , x_msg_data => l_msg_data
1350 , x_msg_count => l_msg_count
1351 , p_approval_rec => l_approval_rec
1352 , p_approver_id => null
1353 , x_final_approval_flag => l_final_approval_flag
1354 );
1355
1356 IF l_return_status = Fnd_Api.g_ret_sts_error OR l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
1357 fnd_file.put_line(fnd_file.log, l_msg_data);
1358 fnd_file.new_line(fnd_file.log);
1359 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1360 END IF;
1361
1362 EXCEPTION
1363 WHEN FND_API.G_EXC_ERROR THEN
1364 l_msg_data := fnd_message.get_string('DPP','DPP_AME_NO_APP');
1365 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Transaction Number : ' || l_transaction_number || ' Ref. Document Number : ' || l_ref_document_number);
1366 FND_FILE.NEW_LINE(FND_FILE.LOG);
1367 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error Message : ' || l_msg_data);
1368 FND_FILE.NEW_LINE(FND_FILE.LOG);
1369 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1370 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Transaction Number : ' || l_transaction_number || ' Ref. Document Number : ' || l_ref_document_number);
1371 FND_FILE.NEW_LINE(FND_FILE.LOG);
1372 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error Message : ' || l_msg_data);
1373 FND_FILE.NEW_LINE(FND_FILE.LOG);
1374 WHEN OTHERS THEN
1375 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
1376 fnd_message.set_token('ROUTINE', l_full_name);
1377 fnd_message.set_token('ERRNO', sqlcode);
1378 fnd_message.set_token('REASON', sqlerrm);
1379 FND_MSG_PUB.add;
1380 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1381 END;
1382
1383 ELSIF l_skip_adjustment_flag = 'Y' AND l_skip_approval_flag = 'Y' THEN
1384 BEGIN
1385 UPDATE dpp_transaction_headers_all
1386 SET transaction_status = 'APPROVED',
1387 object_version_number = object_version_number +1,
1388 last_updated_by = l_user_id,
1389 last_update_date = sysdate,
1390 last_update_login = l_login_id,
1391 request_id = l_request_id,
1392 program_application_id = l_program_application_id,
1393 program_id = l_program_id,
1394 program_update_date = sysdate
1395 WHERE transaction_header_id = l_transaction_header_id;
1396
1397 --Call the Initiate execution process
1398 Initiate_ExecutionProcess(errbuf,
1399 retcode,
1400 l_org_id,
1401 l_transaction_number
1402 );
1403
1404 IF G_DEBUG THEN
1405 fnd_file.put_line(fnd_file.log, ' Initiate_ExecutionProcess. retcode: ' || retcode || ' errbuf: ' || errbuf);
1406 END IF;
1407
1408 IF retcode <> 0 THEN
1409 FND_FILE.PUT_LINE(fnd_file.log, 'Initiate_ExecutionProcess did not complete successfully for' );
1410 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Transaction Number : ' || l_transaction_number || ' Ref. Document Number : ' || l_ref_document_number);
1411 FND_FILE.NEW_LINE(FND_FILE.LOG);
1412 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error Message : ' || errbuf);
1413 FND_FILE.NEW_LINE(FND_FILE.LOG);
1414 END IF;
1415
1416 EXCEPTION
1417 WHEN OTHERS THEN
1418 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
1419 fnd_message.set_token('ROUTINE', l_full_name);
1420 fnd_message.set_token('ERRNO', sqlcode);
1421 fnd_message.set_token('REASON', sqlerrm);
1422 FND_MSG_PUB.add;
1423 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1424 END;
1425
1426 ELSE
1427
1428 IF l_eligible_txn_exist = 'N' THEN
1429 l_eligible_txn_exist := 'Y';
1430 END IF;
1431
1432 BEGIN
1433 UPDATE dpp_transaction_headers_all
1434 SET transaction_status = 'PENDING_ADJUSTMENT',
1435 object_version_number = object_version_number +1,
1436 last_updated_by = l_user_id,
1437 last_update_date = sysdate,
1438 last_update_login = l_login_id,
1439 request_id = l_request_id,
1440 program_application_id = l_program_application_id,
1441 program_id = l_program_id,
1442 program_update_date = sysdate
1443 WHERE transaction_header_id = l_transaction_header_id;
1444 EXCEPTION
1445 WHEN OTHERS THEN
1446 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
1447 fnd_message.set_token('ROUTINE', l_full_name);
1448 fnd_message.set_token('ERRNO', sqlcode);
1449 fnd_message.set_token('REASON', sqlerrm);
1450 FND_MSG_PUB.add;
1451 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1452 END;
1453
1454 END IF;
1455
1456 Update_HeaderLog(
1457 p_api_version_number => 1.0
1458 , p_init_msg_list => FND_API.G_FALSE
1459 , p_commit => FND_API.G_FALSE
1460 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
1461 , x_return_status => l_return_status
1462 , x_msg_count => l_msg_count
1463 , x_msg_data => l_msg_data
1464 , p_transaction_header_id => l_transaction_header_id
1465 ) ;
1466
1467 IF G_DEBUG THEN
1468 fnd_file.put_line(fnd_file.log, ' Update_HeaderLog. Return Status: ' || l_return_status || ' Error Msg: ' || l_msg_data);
1469 END IF;
1470
1471 IF l_return_status = Fnd_Api.g_ret_sts_error THEN
1472 RAISE Fnd_Api.g_exc_error;
1473 ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
1474 RAISE Fnd_Api.g_exc_unexpected_error;
1475 END IF;
1476
1477 EXECUTE_PROCESS(L_API_VERSION, FND_API.G_FALSE, FND_API.G_FALSE, FND_API.G_VALID_LEVEL_FULL, L_RETURN_STATUS, L_MSG_COUNT, L_MSG_DATA,
1478 l_transaction_header_id, l_transaction_number, 'POPINVDTLS');
1479
1480 END LOOP;
1481
1482 IF l_eligible_txn_exist = 'Y' THEN
1483
1484 --Invoke the API to raise the business event
1485 DPP_BUSINESSEVENTS_PVT.RAISE_EFFECTIVE_DATE_EVENT( p_api_version => 1.0
1486 ,p_init_msg_list => FND_API.G_FALSE
1487 ,p_commit => FND_API.G_FALSE
1488 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
1489 ,x_return_status => l_return_status
1490 ,x_msg_data => l_msg_data
1491 ,x_msg_count => l_msg_count
1492 ,p_program_id => l_request_id
1493 );
1494
1495 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
1496 FND_FILE.PUT_LINE(FND_FILE.LOG,'Invoked API to send WF Notifications to the '
1497 || 'Creator on the effective date of the Price Protection Transaction');
1498 FND_FILE.NEW_LINE(FND_FILE.LOG);
1499 ELSE
1500 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error while invoking API that sends WF Notifications to the '
1501 ||'Creator on the effective date of the Price Protection Transaction');
1502 FND_FILE.NEW_LINE(FND_FILE.LOG);
1503 FND_FILE.PUT_LINE(FND_FILE.LOG,substr(('Error Message : '||l_msg_data),1,4000));
1504 FND_FILE.NEW_LINE(FND_FILE.LOG);
1505 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1506 END IF;
1507
1508 ELSE
1509 FND_FILE.PUT_LINE(FND_FILE.LOG, 'No Price protection transactions effective today.');
1510 FND_FILE.NEW_LINE(FND_FILE.LOG);
1511 DPP_UTILITY_PVT.DEBUG_MESSAGE( FND_LOG.LEVEL_STATEMENT, 'dpp.plsql.' || L_FULL_NAME, 'No Price protection transactions effective today.');
1512 END IF;
1513
1514 --Commit the changes
1515 COMMIT;
1516
1517 EXCEPTION
1518 WHEN FND_API.G_EXC_ERROR THEN
1519 ROLLBACK TO Change_Status;
1520 retcode := 2;
1521 errbuf := 'Error';
1522 -- Standard call to get message count and if count=1, get the message
1523 FND_MSG_PUB.Count_And_Get (
1524 p_encoded => FND_API.G_FALSE,
1525 p_count => l_msg_count,
1526 p_data => l_msg_data
1527 );
1528 IF l_msg_count > 1 THEN
1529 FOR I IN 1..l_msg_count LOOP
1530 l_msg_data := SUBSTR((l_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
1531 END LOOP;
1532 END IF;
1533
1534 FND_FILE.PUT_LINE(FND_FILE.LOG,substr(('Error Message : ' || l_msg_data),1,4000));
1535 FND_FILE.NEW_LINE(FND_FILE.LOG);
1536 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1537 ROLLBACK TO Change_Status;
1538 retcode := 2;
1539 errbuf := 'Error';
1540 -- Standard call to get message count and if count=1, get the message
1541 FND_MSG_PUB.Count_And_Get (
1542 p_encoded => FND_API.G_FALSE,
1543 p_count => l_msg_count,
1544 p_data => l_msg_data
1545 );
1546 IF l_msg_count > 1 THEN
1547 FOR I IN 1..l_msg_count LOOP
1548 l_msg_data := SUBSTR((l_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
1549 END LOOP;
1550 END IF;
1551
1552 FND_FILE.PUT_LINE(FND_FILE.LOG,substr(('Error Message : ' || l_msg_data),1,4000));
1553 FND_FILE.NEW_LINE(FND_FILE.LOG);
1554 WHEN OTHERS THEN
1555 ROLLBACK TO Change_Status;
1556 retcode := 2;
1557 errbuf := 'Error';
1558 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
1559 fnd_message.set_token('ROUTINE', l_full_name);
1560 fnd_message.set_token('ERRNO', sqlcode);
1561 fnd_message.set_token('REASON', sqlerrm);
1562 FND_MSG_PUB.add;
1563
1564 -- Standard call to get message count and if count=1, get the message
1565 FND_MSG_PUB.Count_And_Get (
1566 p_encoded => FND_API.G_FALSE,
1567 p_count => l_msg_count,
1568 p_data => l_msg_data
1569 );
1570 IF l_msg_count > 1 THEN
1571 FOR I IN 1..l_msg_count LOOP
1572 l_msg_data := SUBSTR((l_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
1573 END LOOP;
1574 END IF;
1575
1576 FND_FILE.PUT_LINE(FND_FILE.LOG,substr(('Error Message : ' || l_msg_data),1,4000));
1577 FND_FILE.NEW_LINE(FND_FILE.LOG);
1578
1579 END Change_Status;
1580
1581 ---------------------------------------------------------------------
1582 -- PROCEDURE
1583 -- approve_transaction
1584 --
1585 -- PURPOSE
1586 -- This procedure will directly update the transaction status to
1587 -- APPROVED without going through the AME approval and initiate the
1588 -- automated execution processes
1589 --
1590 -- PARAMETERS
1591 --
1592 -- NOTES
1593 -- 1.
1594 -- 2.
1595 ----------------------------------------------------------------------
1596 PROCEDURE approve_transaction(
1597 p_api_version_number IN NUMBER,
1598 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1599 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1600 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
1601 x_return_status OUT NOCOPY VARCHAR2,
1602 x_msg_count OUT NOCOPY NUMBER,
1603 x_msg_data OUT NOCOPY VARCHAR2,
1604 p_txn_hdr_id IN NUMBER
1605 )
1606 IS
1607 --Declare the variables
1608 l_api_name CONSTANT VARCHAR2(30) := 'approve_transaction';
1609 l_api_version_number CONSTANT NUMBER := 1.0;
1610 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
1611
1612 l_user_id NUMBER := FND_GLOBAL.USER_ID;
1613 l_login_id NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
1614 l_request_id NUMBER := FND_GLOBAL.CONC_REQUEST_ID;
1615 l_program_application_id NUMBER := FND_GLOBAL.PROG_APPL_ID;
1616 l_program_id NUMBER := FND_GLOBAL.CONC_PROGRAM_ID;
1617
1618 l_init_msg_list VARCHAR2(10) := p_init_msg_list;
1619 l_commit VARCHAR2(10) := p_commit;
1620 l_validation_level NUMBER := p_validation_level;
1621
1622 l_return_status VARCHAR2(10);
1623 l_msg_data VARCHAR2(4000);
1624 l_msg_count NUMBER;
1625
1626 errbuff VARCHAR2(4000);
1627 retcode VARCHAR2(10);
1628
1629 l_txn_hdr_id NUMBER := p_txn_hdr_id;
1630 l_org_id NUMBER;
1631 l_txn_number VARCHAR2(40);
1632
1633 CURSOR txn_dtls_cur (p_txn_hdr_id IN NUMBER)
1634 IS
1635 SELECT transaction_number, org_id
1636 FROM dpp_transaction_headers_all
1637 WHERE transaction_header_id = p_txn_hdr_id;
1638
1639 BEGIN
1640 -- Standard Start of API savepoint
1641 SAVEPOINT APPROVE_TRANSACTION;
1642
1643 -- Standard call to check for call compatibility.
1644 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1645 p_api_version_number,
1646 l_api_name,
1647 G_PKG_NAME)
1648 THEN
1649 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1650 END IF;
1651
1652 -- Initialize message list if p_init_msg_list is set to TRUE.
1653 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1654 FND_MSG_PUB.initialize;
1655 END IF;
1656
1657 -- Initialize API return status to SUCCESS
1658 x_return_status := FND_API.G_RET_STS_SUCCESS;
1659
1660 update_status(
1661 p_api_version_number => l_api_version_number
1662 , p_init_msg_list => l_init_msg_list
1663 , p_commit => l_commit
1664 , p_validation_level => l_validation_level
1665 , x_return_status => l_return_status
1666 , x_msg_count => l_msg_count
1667 , x_msg_data => l_msg_data
1668 , p_txn_hdr_id => l_txn_hdr_id
1669 , p_to_status => 'APPROVED'
1670 ) ;
1671
1672 IF G_DEBUG THEN
1673 fnd_file.put_line(fnd_file.log, ' Update_Status. Return Status: ' || l_return_status || ' Error Msg: ' || l_msg_data);
1674 END IF;
1675
1676 IF l_return_status = Fnd_Api.g_ret_sts_error THEN
1677 RAISE Fnd_Api.g_exc_error;
1678 ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
1679 RAISE Fnd_Api.g_exc_unexpected_error;
1680 END IF;
1681
1682 BEGIN
1683 OPEN txn_dtls_cur(l_txn_hdr_id);
1684 FETCH txn_dtls_cur INTO l_txn_number, l_org_id;
1685 CLOSE txn_dtls_cur;
1686 EXCEPTION
1687 WHEN OTHERS THEN
1688 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1689 END;
1690
1691 --Call the Initiate execution process
1692 Initiate_ExecutionProcess(errbuff,
1693 retcode,
1694 l_org_id,
1695 l_txn_number
1696 );
1697
1698 IF G_DEBUG THEN
1699 fnd_file.put_line(fnd_file.log, ' Initiate_ExecutionProcess. retcode: ' || retcode || ' errbuff: ' || errbuff);
1700 END IF;
1701
1702 IF retcode <> 0 THEN
1703 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1704 END IF;
1705
1706 EXCEPTION
1707 WHEN FND_API.G_EXC_ERROR THEN
1708 ROLLBACK TO APPROVE_TRANSACTION;
1709 x_return_status := FND_API.G_RET_STS_ERROR;
1710 --FND_FILE.PUT_LINE(FND_FILE.LOG,'Supplier Trade Profile or Process Setup not available');
1711 --FND_FILE.NEW_LINE(FND_FILE.LOG);
1712 -- Standard call to get message count and if count=1, get the message
1713 FND_MSG_PUB.Count_And_Get (
1714 p_encoded => FND_API.G_FALSE,
1715 p_count => x_msg_count,
1716 p_data => x_msg_data
1717 );
1718 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1719 ROLLBACK TO APPROVE_TRANSACTION;
1720 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1721 FND_FILE.PUT_LINE(FND_FILE.LOG,'Unexpected error occured: ' || SQLERRM);
1722 FND_FILE.NEW_LINE(FND_FILE.LOG);
1723 -- Standard call to get message count and if count=1, get the message
1724 FND_MSG_PUB.Count_And_Get (
1725 p_encoded => FND_API.G_FALSE,
1726 p_count => x_msg_count,
1727 p_data => x_msg_data
1728 );
1729 WHEN OTHERS THEN
1730 ROLLBACK TO APPROVE_TRANSACTION;
1731 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1732 FND_FILE.PUT_LINE(FND_FILE.LOG,'Unexpected error occured: ' || SQLERRM);
1733 FND_FILE.NEW_LINE(FND_FILE.LOG);
1734 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1735 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1736 END IF;
1737 -- Standard call to get message count and if count=1, get the message
1738 FND_MSG_PUB.Count_And_Get (
1739 p_encoded => FND_API.G_FALSE,
1740 p_count => x_msg_count,
1741 p_data => x_msg_data
1742 );
1743
1744 END approve_transaction;
1745
1746 ---------------------------------------------------------------------
1747 -- PROCEDURE
1748 -- update_status
1749 --
1750 -- PURPOSE
1751 -- This procedure will update the transaction status of a particular
1752 -- Price Protection transaction.
1753 --
1754 -- PARAMETERS
1755 --
1756 -- NOTES
1757 -- 1.
1758 -- 2.
1759 ----------------------------------------------------------------------
1760 PROCEDURE update_status(
1761 p_api_version_number IN NUMBER,
1762 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1763 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1764 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
1765 x_return_status OUT NOCOPY VARCHAR2,
1766 x_msg_count OUT NOCOPY NUMBER,
1767 x_msg_data OUT NOCOPY VARCHAR2,
1768 p_txn_hdr_id IN NUMBER,
1769 p_to_status IN VARCHAR2
1770 )
1771 IS
1772 --Declare the variables
1773 l_api_name CONSTANT VARCHAR2(30) := 'update_status';
1774 l_api_version_number CONSTANT NUMBER := 1.0;
1775 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
1776
1777 l_user_id NUMBER := FND_GLOBAL.USER_ID;
1778 l_login_id NUMBER := FND_GLOBAL.LOGIN_ID;
1779
1780 l_return_status VARCHAR2(10);
1781 l_msg_data VARCHAR2(4000);
1782 l_msg_count NUMBER;
1783
1784 BEGIN
1785 -- Standard Start of API savepoint
1786 SAVEPOINT UPDATE_STATUS;
1787
1788 -- Standard call to check for call compatibility.
1789 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1790 p_api_version_number,
1791 l_api_name,
1792 G_PKG_NAME)
1793 THEN
1794 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1795 END IF;
1796
1797 -- Initialize message list if p_init_msg_list is set to TRUE.
1798 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1799 FND_MSG_PUB.initialize;
1800 END IF;
1801
1802 -- Initialize API return status to SUCCESS
1803 x_return_status := FND_API.G_RET_STS_SUCCESS;
1804
1805 UPDATE dpp_transaction_headers_all
1806 SET transaction_status = p_to_status,
1807 object_version_number = object_version_number +1,
1808 last_updated_by = l_user_id,
1809 last_update_date = sysdate,
1810 last_update_login = l_login_id
1811 WHERE transaction_header_id = p_txn_hdr_id;
1812
1813 Update_HeaderLog(
1814 p_api_version_number => l_api_version_number
1815 , p_init_msg_list => p_init_msg_list
1816 , p_commit => p_commit
1817 , p_validation_level => p_validation_level
1818 , x_return_status => l_return_status
1819 , x_msg_count => l_msg_count
1820 , x_msg_data => l_msg_data
1821 , p_transaction_header_id => p_txn_hdr_id
1822 ) ;
1823
1824 IF G_DEBUG THEN
1825 fnd_file.put_line(fnd_file.log, ' Update_HeaderLog. Return Status: ' || l_return_status || ' Error Msg: ' || l_msg_data);
1826 END IF;
1827
1828 IF l_return_status = Fnd_Api.g_ret_sts_error THEN
1829 RAISE Fnd_Api.g_exc_error;
1830 ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
1831 RAISE Fnd_Api.g_exc_unexpected_error;
1832 END IF;
1833
1834 EXCEPTION
1835 WHEN FND_API.G_EXC_ERROR THEN
1836 ROLLBACK TO UPDATE_STATUS;
1837 x_return_status := FND_API.G_RET_STS_ERROR;
1838 --FND_FILE.PUT_LINE(FND_FILE.LOG,'Supplier Trade Profile or Process Setup not available');
1839 --FND_FILE.NEW_LINE(FND_FILE.LOG);
1840 -- Standard call to get message count and if count=1, get the message
1841 FND_MSG_PUB.Count_And_Get (
1842 p_encoded => FND_API.G_FALSE,
1843 p_count => x_msg_count,
1844 p_data => x_msg_data
1845 );
1846 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1847 ROLLBACK TO UPDATE_STATUS;
1848 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1849 FND_FILE.PUT_LINE(FND_FILE.LOG,'Unexpected error occured: ' || SQLERRM);
1850 FND_FILE.NEW_LINE(FND_FILE.LOG);
1851 -- Standard call to get message count and if count=1, get the message
1852 FND_MSG_PUB.Count_And_Get (
1853 p_encoded => FND_API.G_FALSE,
1854 p_count => x_msg_count,
1855 p_data => x_msg_data
1856 );
1857 WHEN OTHERS THEN
1858 ROLLBACK TO UPDATE_STATUS;
1859 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1860 FND_FILE.PUT_LINE(FND_FILE.LOG,'Unexpected error occured: ' || SQLERRM);
1861 FND_FILE.NEW_LINE(FND_FILE.LOG);
1862 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1863 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1864 END IF;
1865 -- Standard call to get message count and if count=1, get the message
1866 FND_MSG_PUB.Count_And_Get (
1867 p_encoded => FND_API.G_FALSE,
1868 p_count => x_msg_count,
1869 p_data => x_msg_data
1870 );
1871
1872 END update_status;
1873
1874 ---------------------------------------------------------------------
1875 -- PROCEDURE
1876 -- Update_HeaderLog
1877 --
1878 -- PURPOSE
1879 -- This procedure will update the transaction header log of a particular
1880 -- Price Protection transaction.
1881 --
1882 -- PARAMETERS
1883 --
1884 -- NOTES
1885 -- 1.
1886 -- 2.
1887 ----------------------------------------------------------------------
1888
1889 PROCEDURE Update_HeaderLog(
1890 p_api_version_number IN NUMBER,
1891 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1892 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1893 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
1894 x_return_status OUT NOCOPY VARCHAR2,
1895 x_msg_count OUT NOCOPY NUMBER,
1896 x_msg_data OUT NOCOPY VARCHAR2,
1897 p_transaction_header_id IN NUMBER
1898 )
1899 IS
1900 --Declare the variables
1901 l_api_name CONSTANT VARCHAR2(30) := 'Update_HeaderLog';
1902 l_api_version_number CONSTANT NUMBER := 1.0;
1903 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
1904
1905 l_user_id NUMBER := FND_GLOBAL.USER_ID;
1906 l_login_id NUMBER := FND_GLOBAL.LOGIN_ID;
1907
1908 l_return_status VARCHAR2(10);
1909 l_msg_data VARCHAR2(4000);
1910 l_msg_count NUMBER;
1911
1912 l_log_enabled VARCHAR2(20);
1913
1914 l_txn_hdr_hist_rec dpp_log_pvt.dpp_cst_hdr_rec_type;
1915 l_txn_hdr_rec dpp_log_pvt.dpp_cst_hdr_rec_type;
1916
1917 --Cursor to fetch header information
1918 CURSOR fetch_header_cur IS
1919 SELECT *
1920 FROM dpp_transaction_headers_all dtha
1921 WHERE dtha.transaction_header_id = p_transaction_header_id;
1922
1923 BEGIN
1924 -- Standard Start of API savepoint
1925 SAVEPOINT UPDATE_HEADERLOG;
1926
1927 -- Standard call to check for call compatibility.
1928 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1929 p_api_version_number,
1930 l_api_name,
1931 G_PKG_NAME)
1932 THEN
1933 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1934 END IF;
1935
1936 -- Initialize message list if p_init_msg_list is set to TRUE.
1937 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1938 FND_MSG_PUB.initialize;
1939 END IF;
1940
1941 -- Initialize API return status to SUCCESS
1942 x_return_status := FND_API.G_RET_STS_SUCCESS;
1943
1944 IF G_DEBUG THEN
1945 FND_FILE.PUT_LINE(FND_FILE.LOG,' Begin Update HeaderLog');
1946 END IF;
1947
1948 SELECT fnd_profile.VALUE('DPP_AUDIT_ENABLED')
1949 INTO l_log_enabled
1950 FROM dual;
1951
1952 IF G_DEBUG THEN
1953 fnd_file.put_line(fnd_file.log, 'Audit Enabled ' || l_log_enabled );
1954 END IF;
1955
1956 --Update Header history log
1957 IF l_log_enabled = 'Y' THEN
1958 FOR fetch_header_rec IN fetch_header_cur
1959 LOOP
1960 --Form header record type to populate header log table
1961 l_txn_hdr_hist_rec.log_mode := 'U';
1962 l_txn_hdr_hist_rec.transaction_header_id := fetch_header_rec.transaction_header_id;
1963 l_txn_hdr_hist_rec.ref_document_number := fetch_header_rec.ref_document_number;
1964 l_txn_hdr_hist_rec.vendor_id := fetch_header_rec.vendor_id;
1965 l_txn_hdr_hist_rec.vendor_site_id := fetch_header_rec.vendor_site_id;
1966 l_txn_hdr_hist_rec.vendor_contact_id := fetch_header_rec.vendor_contact_id;
1967 l_txn_hdr_hist_rec.contact_email_address := fetch_header_rec.contact_email_address;
1968 l_txn_hdr_hist_rec.contact_phone := fetch_header_rec.contact_phone;
1969 l_txn_hdr_hist_rec.effective_start_date := TRUNC(fetch_header_rec.effective_start_date);
1970 l_txn_hdr_hist_rec.days_covered := fetch_header_rec.days_covered;
1971 l_txn_hdr_hist_rec.trx_currency := fetch_header_rec.trx_currency;
1972 l_txn_hdr_hist_rec.transaction_status := fetch_header_rec.transaction_status;
1973 l_txn_hdr_hist_rec.org_id := fetch_header_rec.org_id;
1974 l_txn_hdr_hist_rec.creation_date := fetch_header_rec.creation_date;
1975 l_txn_hdr_hist_rec.created_by := fetch_header_rec.created_by;
1976 l_txn_hdr_hist_rec.last_update_date := fetch_header_rec.last_update_date;
1977 l_txn_hdr_hist_rec.last_updated_by := fetch_header_rec.last_updated_by;
1978 l_txn_hdr_hist_rec.last_update_login := fetch_header_rec.last_update_login;
1979 l_txn_hdr_hist_rec.last_refreshed_by := fetch_header_rec.last_refreshed_by;
1980 l_txn_hdr_hist_rec.last_refreshed_date := fetch_header_rec.last_refreshed_date;
1981 l_txn_hdr_hist_rec.attribute_category := fetch_header_rec.attribute_category;
1982 l_txn_hdr_hist_rec.attribute1 := fetch_header_rec.attribute1;
1983 l_txn_hdr_hist_rec.attribute2 := fetch_header_rec.attribute2;
1984 l_txn_hdr_hist_rec.attribute3 := fetch_header_rec.attribute3;
1985 l_txn_hdr_hist_rec.attribute4 := fetch_header_rec.attribute4;
1986 l_txn_hdr_hist_rec.attribute5 := fetch_header_rec.attribute5;
1987 l_txn_hdr_hist_rec.attribute6 := fetch_header_rec.attribute6;
1988 l_txn_hdr_hist_rec.attribute7 := fetch_header_rec.attribute7;
1989 l_txn_hdr_hist_rec.attribute8 := fetch_header_rec.attribute8;
1990 l_txn_hdr_hist_rec.attribute9 := fetch_header_rec.attribute9;
1991 l_txn_hdr_hist_rec.attribute10 := fetch_header_rec.attribute10;
1992 l_txn_hdr_hist_rec.attribute11 := fetch_header_rec.attribute11;
1993 l_txn_hdr_hist_rec.attribute12 := fetch_header_rec.attribute12;
1994 l_txn_hdr_hist_rec.attribute13 := fetch_header_rec.attribute13;
1995 l_txn_hdr_hist_rec.attribute14 := fetch_header_rec.attribute14;
1996 l_txn_hdr_hist_rec.attribute15 := fetch_header_rec.attribute15;
1997 l_txn_hdr_hist_rec.attribute16 := fetch_header_rec.attribute16;
1998 l_txn_hdr_hist_rec.attribute17 := fetch_header_rec.attribute17;
1999 l_txn_hdr_hist_rec.attribute18 := fetch_header_rec.attribute18;
2000 l_txn_hdr_hist_rec.attribute19 := fetch_header_rec.attribute19;
2001 l_txn_hdr_hist_rec.attribute20 := fetch_header_rec.attribute20;
2002 l_txn_hdr_hist_rec.attribute21 := fetch_header_rec.attribute21;
2003 l_txn_hdr_hist_rec.attribute22 := fetch_header_rec.attribute22;
2004 l_txn_hdr_hist_rec.attribute23 := fetch_header_rec.attribute23;
2005 l_txn_hdr_hist_rec.attribute24 := fetch_header_rec.attribute24;
2006 l_txn_hdr_hist_rec.attribute25 := fetch_header_rec.attribute25;
2007 l_txn_hdr_hist_rec.attribute26 := fetch_header_rec.attribute26;
2008 l_txn_hdr_hist_rec.attribute27 := fetch_header_rec.attribute27;
2009 l_txn_hdr_hist_rec.attribute28 := fetch_header_rec.attribute28;
2010 l_txn_hdr_hist_rec.attribute29 := fetch_header_rec.attribute29;
2011 l_txn_hdr_hist_rec.attribute30 := fetch_header_rec.attribute30;
2012
2013 dpp_log_pvt.insert_headerlog(
2014 p_api_version => l_api_version_number
2015 ,p_init_msg_list => p_init_msg_list
2016 ,p_commit => p_commit
2017 ,p_validation_level => p_validation_level
2018 ,x_return_status => l_return_status
2019 ,x_msg_count => x_msg_count
2020 ,x_msg_data => x_msg_data
2021 ,p_txn_hdr_rec => l_txn_hdr_hist_rec
2022 );
2023
2024 IF G_DEBUG THEN
2025 FND_FILE.PUT_LINE(FND_FILE.LOG,' End Update HeaderLog');
2026 END IF;
2027
2028 IF l_return_status = Fnd_Api.g_ret_sts_error THEN
2029 RAISE Fnd_Api.g_exc_error;
2030 ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
2031 RAISE Fnd_Api.g_exc_unexpected_error;
2032 END IF;
2033 END LOOP;
2034 END IF;
2035 EXCEPTION
2036 WHEN Fnd_Api.G_EXC_ERROR THEN
2037 x_return_status := Fnd_Api.g_ret_sts_error ;
2038 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
2039 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
2040 WHEN OTHERS THEN
2041 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
2042 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2043 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
2044 fnd_message.set_token('ROUTINE', l_full_name);
2045 fnd_message.set_token('ERRNO', SQLCODE);
2046 fnd_message.set_token('REASON', sqlerrm);
2047 END IF;
2048 x_msg_data := fnd_message.get();
2049 FND_FILE.PUT_LINE(FND_FILE.LOG,' ' || x_msg_data);
2050 END Update_HeaderLog;
2051
2052 END DPP_EXECUTIONPROCESS_PVT;