[Home] [Help]
PACKAGE BODY: APPS.OKL_AM_SHIPPING_INSTR_PVT
Source
1 PACKAGE BODY OKL_AM_SHIPPING_INSTR_PVT AS
2 /* $Header: OKLRSHIB.pls 120.4 2006/09/26 09:58:21 zrehman noship $ */
3
4
5 -- Start of comments
6 --
7 -- Procedure Name : create_shipping_instr
8 -- Description : Creates the shipping instruction rec
9 -- Business Rules :
10 -- Parameters :
11 -- Version : 1.0
12 -- History : SECHAWLA - 19-DEC-2002 : Bug # 2667636
13 -- Added logic to convert Insurance amt from contract currency to functional currency
14 -- SECHAWLA 07-FEB-03 Bug # 2789656
15 -- Added x_return_status parameter to okl_accounting_util call. Removed DEFAULT hint from
16 -- procedure parameters
17 -- End of comments
18 PROCEDURE create_shipping_instr(
19 p_api_version IN NUMBER,
20 p_init_msg_list IN VARCHAR2,
21 x_return_status OUT NOCOPY VARCHAR2,
22 x_msg_count OUT NOCOPY NUMBER,
23 x_msg_data OUT NOCOPY VARCHAR2,
24 p_rasv_rec IN rasv_rec_type,
25 x_rasv_rec OUT NOCOPY rasv_rec_type) AS
26
27 -- Cursor to get the khr_id and kle_id for the asset being shipped
28 CURSOR okl_get_khr_kle_csr ( p_art_id IN NUMBER) IS
29 SELECT KLE.chr_id khr_id,
30 KLE.id kle_id,
31 ART.floor_price floor_price
32 FROM OKL_K_LINES_FULL_V KLE,
33 OKL_ASSET_RETURNS_V ART
34 WHERE ART.id = p_art_id
35 AND ART.kle_id = KLE.id;
36
37 -- Cursor to get the lessee info
38 -- Fix for bug 3562321 - Added dnz_chr_id in the where clause to avoid fts.
39 CURSOR okl_get_lessee_csr (p_khr_id IN NUMBER) IS
40 select CPLB.OBJECT1_ID1,
41 CPLB.JTOT_OBJECT1_CODE
42 from OKC_K_PARTY_ROLES_B CPLB
43 where CPLB.RLE_CODE = 'LESSEE'
44 and CPLB.DNZ_CHR_ID = CPLB.CHR_ID
45 and CPLB.DNZ_CHR_ID = p_khr_id;
46
47 lp_rasv_rec rasv_rec_type := p_rasv_rec;
48 lx_rasv_rec rasv_rec_type;
49 l_return_status VARCHAR2(200);
50 l_api_name CONSTANT VARCHAR2(30) := 'create_shipping_instr';
51 l_api_version CONSTANT NUMBER := 1;
52 l_formula_name CONSTANT VARCHAR2(30) := 'ASSET VALUE FOR INSURANCE';
53 l_khr_id NUMBER;
54 l_kle_id NUMBER;
55 l_insurance_amt NUMBER := 0;
56 l_floor_price NUMBER := 0;
57 l_contact_method_id NUMBER;
58 l_id_value VARCHAR2(200);
59 l_id_type VARCHAR2(200);
60 l_id_code VARCHAR2(3);
61 l_party_object_tbl OKL_AM_PARTIES_PVT.party_object_tbl_type;
62 i NUMBER;
63
64 --SECHAWLA Bug # 2667636 : new declarations
65 l_func_curr_code GL_LEDGERS_PUBLIC_V.CURRENCY_CODE%TYPE;
66 l_contract_curr_code okc_k_headers_b.currency_code%TYPE;
67 lx_contract_currency okl_k_headers_full_v.currency_code%TYPE;
68 lx_currency_conversion_type okl_k_headers_full_v.currency_conversion_type%TYPE;
69 lx_currency_conversion_rate okl_k_headers_full_v.currency_conversion_rate%TYPE;
70 lx_currency_conversion_date okl_k_headers_full_v.currency_conversion_date%TYPE;
71 lx_converted_amount NUMBER;
72 l_sysdate DATE;
73
74 BEGIN
75
76 --Check API version, initialize message list and create savepoint.
77 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
78 G_PKG_NAME,
79 p_init_msg_list,
80 l_api_version,
81 p_api_version,
82 '_PVT',
83 x_return_status);
84
85 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
86 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
87 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
88 RAISE OKL_API.G_EXCEPTION_ERROR;
89 END IF;
90
91
92 -- SECHAWLA Bug # 2667636 : using sysdate as transaction date for currency conversion routines
93 SELECT SYSDATE INTO l_sysdate FROM DUAL;
94
95
96 -- Get the khr_id and kle_id and floor price for the asset being shipped
97 OPEN okl_get_khr_kle_csr(lp_rasv_rec.art_id);
98 FETCH okl_get_khr_kle_csr INTO l_khr_id, l_kle_id, l_floor_price;
99 CLOSE okl_get_khr_kle_csr;
100
101
102 -- SECHAWLA Bug # 2667636 : get the functional and contract currency
103
104 -- get the functional currency
105 l_func_curr_code := okl_am_util_pvt.get_functional_currency;
106 -- get the contract currency
107 l_contract_curr_code := okl_am_util_pvt.get_chr_currency( p_chr_id => l_khr_id);
108
109 -- Populate Asset Value For Insurance from formula, if the formula
110 -- is not available then set the value to floor price of asset return
111 OKL_AM_UTIL_PVT.get_formula_value(
112 p_formula_name => l_formula_name,
113 p_chr_id => l_khr_id,
114 p_cle_id => l_kle_id,
115 x_formula_value => l_insurance_amt,
116 x_return_status => l_return_status);
117
118 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS OR l_insurance_amt <= 0 OR l_insurance_amt IS NULL THEN
119 l_insurance_amt := l_floor_price;
120 ELSE
121
122
123
124 -- SECHAWLA Bug # 2667636 : added the following logic to convert i9nsurance amt to functional currency
125 IF l_contract_curr_code <> l_func_curr_code THEN
126 -- convert amount to functional currency
127 --SECHAWLA 07-FEB-03 Bug # 2789656 : Added x_return_status parameter to the following procedure call
128 okl_accounting_util.convert_to_functional_currency(
129 p_khr_id => l_khr_id,
130 p_to_currency => l_func_curr_code,
131 p_transaction_date => l_sysdate ,
132 p_amount => l_insurance_amt,
133 x_return_status => x_return_status,
134 x_contract_currency => lx_contract_currency,
135 x_currency_conversion_type => lx_currency_conversion_type,
136 x_currency_conversion_rate => lx_currency_conversion_rate,
137 x_currency_conversion_date => lx_currency_conversion_date,
138 x_converted_amount => lx_converted_amount );
139
140 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
141 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
142 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
143 RAISE OKL_API.G_EXCEPTION_ERROR;
144 END IF;
145
146 l_insurance_amt := lx_converted_amount ;
147
148
149 END IF;
150
151 END IF;
152
153 -- Get Lessee for contract, get the contact method id for lessee and default
154 OPEN okl_get_lessee_csr(l_khr_id);
155 FETCH okl_get_lessee_csr INTO l_id_value, l_id_type;
156 CLOSE okl_get_lessee_csr;
157
158 -- Set the id_code
159 IF UPPER(l_id_type) = 'OKX_PARTY' THEN
160 l_id_code := 'P';
161 ELSIF UPPER(l_id_type) = 'OKX_PARTYSITE' THEN
162 l_id_code := 'PS';
163 ELSIF UPPER(l_id_type) = 'OKX_PCONTACT' THEN
164 l_id_code := 'PC';
165 ELSIF UPPER(l_id_type) = 'OKX_VENDOR' THEN
166 l_id_code := 'V';
167 ELSIF UPPER(l_id_type) = 'OKX_VENDORSITE' THEN
168 l_id_code := 'VS';
169 ELSIF UPPER(l_id_type) = 'OKX_VCONTACT' THEN
170 l_id_code := 'VC';
171 ELSE -- default is PARTY
172 l_id_code := 'P';
173 END IF;
174
175 -- Get the contact method id (refered to as Contact point id in parties api)
176 OKL_AM_PARTIES_PVT.get_party_details(
177 p_id_code => l_id_code,
178 p_id_value => l_id_value,
179 x_party_object_tbl => l_party_object_tbl,
180 x_return_status => l_return_status);
181
182 IF l_party_object_tbl.COUNT > 0 THEN
183 i := l_party_object_tbl.FIRST;
184 LOOP
185 IF l_party_object_tbl(i).pcp_id IS NOT NULL
186 AND l_party_object_tbl(i).pcp_id <> OKL_API.G_MISS_NUM THEN
187 l_contact_method_id := l_party_object_tbl(i).pcp_id;
188 EXIT;
189 END IF;
190 EXIT WHEN (i = l_party_object_tbl.LAST);
191 i := l_party_object_tbl.NEXT(i);
192 END LOOP;
193 END IF;
194
195 -- Check if NULL
196 IF l_insurance_amt IS NULL THEN
197 l_insurance_amt := 0;
198 END IF;
199
200 -- Set the rasv rec
201 lp_rasv_rec.insurance_amount := l_insurance_amt;
202 lp_rasv_rec.pac_id := l_contact_method_id;
203
204 -- Call TAPI to insert the row
205 OKL_RELOCATE_ASSETS_PUB.insert_relocate_assets(
206 p_api_version => p_api_version,
207 p_init_msg_list => OKL_API.G_FALSE,
208 x_return_status => l_return_status,
209 x_msg_count => x_msg_count,
210 x_msg_data => x_msg_data,
211 p_rasv_rec => lp_rasv_rec,
212 x_rasv_rec => lx_rasv_rec);
213
214 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
215 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
216 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
217 RAISE OKL_API.G_EXCEPTION_ERROR;
218 END IF;
219
220 -- set return variables
221 x_return_status := l_return_status;
222 x_rasv_rec := lx_rasv_rec;
223
224 -- end the transaction
225 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
226
227 EXCEPTION
228 WHEN OKL_API.G_EXCEPTION_ERROR THEN
229 IF okl_get_khr_kle_csr%ISOPEN THEN
230 CLOSE okl_get_khr_kle_csr;
231 END IF;
232 x_return_status := OKL_API.HANDLE_EXCEPTIONS
233 (
234 l_api_name,
235 G_PKG_NAME,
236 'OKL_API.G_RET_STS_ERROR',
237 x_msg_count,
238 x_msg_data,
239 '_PVT'
240 );
241 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
242 IF okl_get_khr_kle_csr%ISOPEN THEN
243 CLOSE okl_get_khr_kle_csr;
244 END IF;
245 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
246 (
247 l_api_name,
248 G_PKG_NAME,
249 'OKL_API.G_RET_STS_UNEXP_ERROR',
250 x_msg_count,
251 x_msg_data,
252 '_PVT'
253 );
254 WHEN OTHERS THEN
255 IF okl_get_khr_kle_csr%ISOPEN THEN
256 CLOSE okl_get_khr_kle_csr;
257 END IF;
258 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
259 (
260 l_api_name,
261 G_PKG_NAME,
262 'OTHERS',
263 x_msg_count,
264 x_msg_data,
265 '_PVT'
266 );
267 END create_shipping_instr;
268
269
270 -- Start of comments
271 --
272 -- Procedure Name : create_shipping_instr
273 -- Description : Creates the shipping instructions records
274 -- Business Rules :
275 -- Parameters :
276 -- Version : 1.0
277 -- History : SECHAWLA 07-FEB-03 Bug # 2789656
278 -- Removed DEFAULT hint from procedure parameters
279 -- End of comments
280 PROCEDURE create_shipping_instr(
281 p_api_version IN NUMBER,
282 p_init_msg_list IN VARCHAR2,
283 x_return_status OUT NOCOPY VARCHAR2,
284 x_msg_count OUT NOCOPY NUMBER,
285 x_msg_data OUT NOCOPY VARCHAR2,
286 p_rasv_tbl IN rasv_tbl_type,
287 x_rasv_tbl OUT NOCOPY rasv_tbl_type) AS
288
289 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
290 l_api_name CONSTANT VARCHAR2(30) := 'create_shipping_instr';
291 i NUMBER := 0;
292
293 BEGIN
294
295 OKL_API.init_msg_list(p_init_msg_list);
296 IF (p_rasv_tbl.COUNT > 0) THEN
297 i := p_rasv_tbl.FIRST;
298 LOOP
299
300 create_shipping_instr(
301 p_api_version => p_api_version,
302 p_init_msg_list => p_init_msg_list,
303 x_return_status => x_return_status,
304 x_msg_count => x_msg_count,
305 x_msg_data => x_msg_data,
306 p_rasv_rec => p_rasv_tbl(i),
307 x_rasv_rec => x_rasv_tbl(i));
308
309 IF x_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
310 IF l_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR THEN
311 l_return_status := x_return_status;
312 END IF;
313 END IF;
314 EXIT WHEN (i = p_rasv_tbl.LAST);
315 i := p_rasv_tbl.NEXT(i);
316 END LOOP;
317 x_return_status := l_return_status;
318 END IF;
319
320 -- end the transaction
321 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
322 EXCEPTION
323 WHEN OKL_API.G_EXCEPTION_ERROR THEN
324 x_return_status := OKL_API.HANDLE_EXCEPTIONS
325 (
326 l_api_name,
327 G_PKG_NAME,
328 'OKL_API.G_RET_STS_ERROR',
329 x_msg_count,
330 x_msg_data,
331 '_PVT'
332 );
333 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
334 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
335 (
336 l_api_name,
337 G_PKG_NAME,
338 'OKL_API.G_RET_STS_UNEXP_ERROR',
339 x_msg_count,
340 x_msg_data,
341 '_PVT'
342 );
343 WHEN OTHERS THEN
344 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
345 (
346 l_api_name,
347 G_PKG_NAME,
348 'OTHERS',
349 x_msg_count,
350 x_msg_data,
351 '_PVT'
352 );
353 END create_shipping_instr;
354
355
356 -- Start of comments
357 --
358 -- Procedure Name : update_shipping_instr
359 -- Description : Updates the shipping instructions rec
360 -- Business Rules :
361 -- Parameters :
362 -- Version : 1.0
363 -- History : SECHAWLA 07-FEB-03 Bug # 2789656
364 -- Removed DEFAULT hint from procedure parameters
365 -- End of comments
366 PROCEDURE update_shipping_instr(
367 p_api_version IN NUMBER,
368 p_init_msg_list IN VARCHAR2,
369 x_return_status OUT NOCOPY VARCHAR2,
370 x_msg_count OUT NOCOPY NUMBER,
371 x_msg_data OUT NOCOPY VARCHAR2,
372 p_rasv_rec IN rasv_rec_type,
373 x_rasv_rec OUT NOCOPY rasv_rec_type) AS
374
375 lp_rasv_rec rasv_rec_type := p_rasv_rec;
376 lx_rasv_rec rasv_rec_type;
377 l_return_status VARCHAR2(200);
378 l_api_name CONSTANT VARCHAR2(30) := 'create_shipping_instr';
379 l_api_version CONSTANT NUMBER := 1;
380
381 BEGIN
382
383 --Check API version, initialize message list and create savepoint.
384 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
385 G_PKG_NAME,
386 p_init_msg_list,
387 l_api_version,
388 p_api_version,
389 '_PVT',
390 x_return_status);
391
392 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
393 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
394 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
395 RAISE OKL_API.G_EXCEPTION_ERROR;
396 END IF;
397
398 -- If insurance amount is <=0 then error
399 IF lp_rasv_rec.insurance_amount <= 0
400 OR lp_rasv_rec.insurance_amount IS NULL
401 OR lp_rasv_rec.insurance_amount = OKL_API.G_MISS_NUM THEN
402 -- Message: You must enter a positive value for PROMPT.
403 OKL_API.set_message(p_app_name => 'OKL',
404 p_msg_name => 'OKL_AM_REQ_FIELD_POS_ERR',
405 p_token1 => 'PROMPT',
406 p_token1_value => OKL_AM_UTIL_PVT.get_ak_attribute('OKL_ASSET_VALUE_FOR_INSURANCE'));
407 RAISE OKL_API.G_EXCEPTION_ERROR;
408 END IF;
409
410 -- PAC_ID mandatory
411 IF lp_rasv_rec.pac_id IS NULL
412 OR lp_rasv_rec.pac_id = OKL_API.G_MISS_NUM THEN
413 -- Message: You must enter a value for PROMPT.
414 OKL_API.set_message(p_app_name => 'OKL',
415 p_msg_name => 'OKL_AM_REQ_FIELD_ERR',
416 p_token1 => 'PROMPT',
417 p_token1_value => OKL_AM_UTIL_PVT.get_ak_attribute('OKL_CONTACT_METHOD'));
418 RAISE OKL_API.G_EXCEPTION_ERROR;
419 END IF;
420
421 -- IST_ID mandatory
422 IF lp_rasv_rec.ist_id IS NULL
423 OR lp_rasv_rec.ist_id = OKL_API.G_MISS_NUM THEN
424 -- Message: You must enter a value for PROMPT.
425 OKL_API.set_message(p_app_name => 'OKL',
426 p_msg_name => 'OKL_AM_REQ_FIELD_ERR',
427 p_token1 => 'PROMPT',
428 p_token1_value => OKL_AM_UTIL_PVT.get_ak_attribute('OKL_SHIP_TO_PARTY'));
429 RAISE OKL_API.G_EXCEPTION_ERROR;
430 END IF;
431
432 -- Call TAPI to update the rec
433 OKL_RELOCATE_ASSETS_PUB.update_relocate_assets(
434 p_api_version => p_api_version,
435 p_init_msg_list => OKL_API.G_FALSE,
436 x_return_status => l_return_status,
437 x_msg_count => x_msg_count,
438 x_msg_data => x_msg_data,
439 p_rasv_rec => lp_rasv_rec,
440 x_rasv_rec => lx_rasv_rec);
441
442 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
443 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
444 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
445 RAISE OKL_API.G_EXCEPTION_ERROR;
446 END IF;
447
448 -- set return variables
449 x_return_status := l_return_status;
450 x_rasv_rec := lx_rasv_rec;
451
452 -- end the transaction
453 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
454
455 EXCEPTION
456 WHEN OKL_API.G_EXCEPTION_ERROR THEN
457 x_return_status := OKL_API.HANDLE_EXCEPTIONS
458 (
459 l_api_name,
460 G_PKG_NAME,
461 'OKL_API.G_RET_STS_ERROR',
462 x_msg_count,
463 x_msg_data,
464 '_PVT'
465 );
466 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
467 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
468 (
469 l_api_name,
470 G_PKG_NAME,
471 'OKL_API.G_RET_STS_UNEXP_ERROR',
472 x_msg_count,
473 x_msg_data,
474 '_PVT'
475 );
476 WHEN OTHERS THEN
477 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
478 (
479 l_api_name,
480 G_PKG_NAME,
481 'OTHERS',
482 x_msg_count,
483 x_msg_data,
484 '_PVT'
485 );
486 END update_shipping_instr;
487
488 -- Start of comments
489 --
490 -- Procedure Name : update_shipping_instr
491 -- Description : Updates the shipping instructions records
492 -- Business Rules :
493 -- Parameters :
494 -- Version : 1.0
495 -- History : SECHAWLA 07-FEB-03 Bug # 2789656
496 -- Removed DEFAULT hint from procedure parameters
497 -- End of comments
498 PROCEDURE update_shipping_instr(
499 p_api_version IN NUMBER,
500 p_init_msg_list IN VARCHAR2,
501 x_return_status OUT NOCOPY VARCHAR2,
502 x_msg_count OUT NOCOPY NUMBER,
503 x_msg_data OUT NOCOPY VARCHAR2,
504 p_rasv_tbl IN rasv_tbl_type,
505 x_rasv_tbl OUT NOCOPY rasv_tbl_type) AS
506
507 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
508 l_api_name CONSTANT VARCHAR2(30) := 'create_shipping_instr';
509 i NUMBER := 0;
510
511 BEGIN
512
513 OKL_API.init_msg_list(p_init_msg_list);
514 IF (p_rasv_tbl.COUNT > 0) THEN
515 i := p_rasv_tbl.FIRST;
516 LOOP
517
518 update_shipping_instr(
519 p_api_version => p_api_version,
520 p_init_msg_list => p_init_msg_list,
521 x_return_status => x_return_status,
522 x_msg_count => x_msg_count,
523 x_msg_data => x_msg_data,
524 p_rasv_rec => p_rasv_tbl(i),
525 x_rasv_rec => x_rasv_tbl(i));
526
527 IF x_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
528 IF l_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR THEN
529 l_return_status := x_return_status;
530 END IF;
531 END IF;
532 EXIT WHEN (i = p_rasv_tbl.LAST);
533 i := p_rasv_tbl.NEXT(i);
534 END LOOP;
535 x_return_status := l_return_status;
536 END IF;
537
538 -- end the transaction
539 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
540 EXCEPTION
541 WHEN OKL_API.G_EXCEPTION_ERROR THEN
542 x_return_status := OKL_API.HANDLE_EXCEPTIONS
543 (
544 l_api_name,
545 G_PKG_NAME,
546 'OKL_API.G_RET_STS_ERROR',
547 x_msg_count,
548 x_msg_data,
549 '_PVT'
550 );
551 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
552 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
553 (
554 l_api_name,
555 G_PKG_NAME,
556 'OKL_API.G_RET_STS_UNEXP_ERROR',
557 x_msg_count,
558 x_msg_data,
559 '_PVT'
560 );
561 WHEN OTHERS THEN
562 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
563 (
564 l_api_name,
565 G_PKG_NAME,
566 'OTHERS',
567 x_msg_count,
568 x_msg_data,
569 '_PVT'
570 );
571 END update_shipping_instr;
572
573 -- Start of comments
574 --
575 -- Procedure Name : send_shipping_instr
576 -- Description : Launches the shipping instructions WF
577 -- Business Rules :
578 -- Parameters :
579 -- Version : 1.0
580 -- History : SECHAWLA 07-FEB-03 Bug # 2789656
581 -- Removed DEFAULT hint from procedure parameters
582 -- End of comments
583 PROCEDURE send_shipping_instr(
584 p_api_version IN NUMBER,
585 p_init_msg_list IN VARCHAR2,
586 x_return_status OUT NOCOPY VARCHAR2,
587 x_msg_count OUT NOCOPY NUMBER,
588 x_msg_data OUT NOCOPY VARCHAR2,
589 p_rasv_rec IN rasv_rec_type,
590 x_rasv_rec OUT NOCOPY rasv_rec_type) AS
591
592 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
593 l_api_name CONSTANT VARCHAR2(30) := 'send_shipping_instr';
594 lp_rasv_rec rasv_rec_type := p_rasv_rec;
595 lx_rasv_rec rasv_rec_type;
596 l_api_version CONSTANT NUMBER := 1;
597 l_event_name VARCHAR2(200);
598 BEGIN
599
600 --Check API version, initialize message list and create savepoint.
601 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
602 G_PKG_NAME,
603 p_init_msg_list,
604 l_api_version,
605 p_api_version,
606 '_PVT',
607 x_return_status);
608
609 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
610 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
611 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
612 RAISE OKL_API.G_EXCEPTION_ERROR;
613 END IF;
614
615 -- Set the date_sent if not set
616 IF lp_rasv_rec.date_shipping_instructions_sen IS NULL
617 OR lp_rasv_rec.date_shipping_instructions_sen = OKL_API.G_MISS_DATE THEN
618
619 SELECT SYSDATE INTO lp_rasv_rec.date_shipping_instructions_sen FROM DUAL;
620
621 END IF;
622
623 -- Call the update
624 update_shipping_instr(
625 p_api_version => p_api_version,
626 p_init_msg_list => OKL_API.G_FALSE,
627 x_return_status => l_return_status,
628 x_msg_count => x_msg_count,
629 x_msg_data => x_msg_data,
630 p_rasv_rec => lp_rasv_rec,
631 x_rasv_rec => lx_rasv_rec);
632
633 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
634 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
635 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
636 RAISE OKL_API.G_EXCEPTION_ERROR;
637 END IF;
638
639 -- Based on trans option launch the WF
640 IF lx_rasv_rec.trans_option_accepted_yn = 'Y' THEN
641
642 -- Get the WF event name
643 l_event_name := OKL_AM_UTIL_PVT.get_wf_event_name(
644 p_wf_process_type => 'OKLAMNTD',
645 p_wf_process_name => 'NOTIFY_ITD_PROC',
646 x_return_status => l_return_status);
647
648 -- Raise exception when error
649 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
650 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
651 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
652 RAISE OKL_API.G_EXCEPTION_ERROR;
653 END IF;
654
655 -- Launch the Notify Internal Transport Department WF
656 OKL_AM_WF.raise_business_event (
657 p_transaction_id => lx_rasv_rec.art_id,
658 p_event_name => 'oracle.apps.okl.am.notifytransdept');
659 ELSE
660
661 -- Get the WF event name
662 l_event_name := OKL_AM_UTIL_PVT.get_wf_event_name(
663 p_wf_process_type => 'OKLAMNSI',
664 p_wf_process_name => 'SHIPPING_INSTRUCTION_PROC',
665 x_return_status => l_return_status);
666
667 -- Raise exception when error
668 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
669 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
670 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
671 RAISE OKL_API.G_EXCEPTION_ERROR;
672 END IF;
673
674 -- Launch the Shipping Instruction WF
675 OKL_AM_WF.raise_business_event (
676 p_transaction_id => lx_rasv_rec.art_id,
677 p_event_name => 'oracle.apps.okl.am.notifyshipinstr');
678 END IF;
679
680 -- Set message on stack
681 -- Workflow event EVENT_NAME has been requested.
682 OKL_API.set_message(p_app_name => 'OKL',
683 p_msg_name => 'OKL_AM_WF_EVENT_MSG',
684 p_token1 => 'EVENT_NAME',
685 p_token1_value => l_event_name);
686
687 x_return_status := l_return_status;
688 x_rasv_rec := lx_rasv_rec;
689
690 -- end the transaction
691 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
692 EXCEPTION
693 WHEN OKL_API.G_EXCEPTION_ERROR THEN
694 x_return_status := OKL_API.HANDLE_EXCEPTIONS
695 (
696 l_api_name,
697 G_PKG_NAME,
698 'OKL_API.G_RET_STS_ERROR',
699 x_msg_count,
700 x_msg_data,
701 '_PVT'
702 );
703 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
704 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
705 (
706 l_api_name,
707 G_PKG_NAME,
708 'OKL_API.G_RET_STS_UNEXP_ERROR',
709 x_msg_count,
710 x_msg_data,
711 '_PVT'
712 );
713 WHEN OTHERS THEN
714 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
715 (
716 l_api_name,
717 G_PKG_NAME,
718 'OTHERS',
719 x_msg_count,
720 x_msg_data,
721 '_PVT'
722 );
723 END send_shipping_instr;
724
725 -- Start of comments
726 --
727 -- Procedure Name : send_shipping_instr
728 -- Description : Launches the shipping instructions WF
729 -- Business Rules :
730 -- Parameters :
731 -- Version : 1.0
732 -- History : SECHAWLA 07-FEB-03 Bug # 2789656
733 -- Removed DEFAULT hint from procedure parameters
734 -- End of comments
735 PROCEDURE send_shipping_instr(
736 p_api_version IN NUMBER,
737 p_init_msg_list IN VARCHAR2,
738 x_return_status OUT NOCOPY VARCHAR2,
739 x_msg_count OUT NOCOPY NUMBER,
740 x_msg_data OUT NOCOPY VARCHAR2,
741 p_rasv_tbl IN rasv_tbl_type,
742 x_rasv_tbl OUT NOCOPY rasv_tbl_type) AS
743
744 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
745 l_api_name CONSTANT VARCHAR2(30) := 'send_shipping_instr';
746 i NUMBER := 0;
747
748 BEGIN
749
750 OKL_API.init_msg_list(p_init_msg_list);
751 IF (p_rasv_tbl.COUNT > 0) THEN
752 i := p_rasv_tbl.FIRST;
753 LOOP
754
755 send_shipping_instr(
756 p_api_version => p_api_version,
757 p_init_msg_list => p_init_msg_list,
758 x_return_status => x_return_status,
759 x_msg_count => x_msg_count,
760 x_msg_data => x_msg_data,
761 p_rasv_rec => p_rasv_tbl(i),
762 x_rasv_rec => x_rasv_tbl(i));
763
764 IF x_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
765 IF l_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR THEN
766 l_return_status := x_return_status;
767 END IF;
768 END IF;
769 EXIT WHEN (i = p_rasv_tbl.LAST);
770 i := p_rasv_tbl.NEXT(i);
771 END LOOP;
772 x_return_status := l_return_status;
773 END IF;
774
775 EXCEPTION
776 WHEN OKL_API.G_EXCEPTION_ERROR THEN
777 x_return_status := OKL_API.HANDLE_EXCEPTIONS
778 (
779 l_api_name,
780 G_PKG_NAME,
781 'OKL_API.G_RET_STS_ERROR',
782 x_msg_count,
783 x_msg_data,
784 '_PVT'
785 );
786 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
787 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
788 (
789 l_api_name,
790 G_PKG_NAME,
791 'OKL_API.G_RET_STS_UNEXP_ERROR',
792 x_msg_count,
793 x_msg_data,
794 '_PVT'
795 );
796 WHEN OTHERS THEN
797 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
798 (
799 l_api_name,
800 G_PKG_NAME,
801 'OTHERS',
802 x_msg_count,
803 x_msg_data,
804 '_PVT'
805 );
806 END send_shipping_instr;
807
808
809 END OKL_AM_SHIPPING_INSTR_PVT;