[Home] [Help]
PACKAGE BODY: APPS.GR_DISPATCH_HISTORY_PVT
Source
1 PACKAGE BODY GR_DISPATCH_HISTORY_PVT AS
2 /* $Header: GRVDSPHB.pls 120.5 2005/10/19 12:22:14 pbamb noship $ */
3
4 PROCEDURE log_msg(p_msg_text IN VARCHAR2);
5
6 /* Global variables */
7 G_tmp BOOLEAN := FND_MSG_PUB.Check_Msg_Level(0) ; -- temp call to initialize the
8 -- msg level threshhold gobal
9 -- variable.
10 G_debug_level NUMBER := FND_MSG_PUB.G_Msg_Level_Threshold; -- Use this variable everywhere
11 -- to decide to log a debug msg.
12 G_PKG_NAME CONSTANT varchar2(30) := 'GR_DISPATCH_HISTORY_PVT';
13
14 g_log_head CONSTANT VARCHAR2(50) := 'gr.plsql.'|| G_PKG_NAME || '.';
15
16 --------------------------------------------------------------------------------
17 --Start of Comments
18 --Name: create_dispatch_history
19 --Pre-reqs:
20 -- None.
21 --Modifies:
22 -- None.
23 --Locks:
24 -- None.
25 --Function:
26 -- This procedure inserts records in the dispatch history table after validations.
27 --Parameters:
28 --IN:
29 -- Version of API to validate compatibility
30 --p_api_version IN NUMBER,
31 -- Initialize message stack (TRUE or FALSE)
32 --p_init_msg_list IN VARCHAR2,
33 -- Issue database commit after update (TRUE or FALSE)
34 --p_commit IN VARCHAR2,
35 -- iInventory_item_id of item/product that document is related to
36 --p_inventory_tem_id IN NUMBER,
37 -- Item/product that document is related to
38 --p_organization_id IN NUMBER,
39 -- Organization_id that document is generated for
40 --p_item IN VARCHAR2,
41 -- CAS # of item/product that document is related to
42 --p_cas_number IN VARCHAR2,
43 -- Document recipient ID
44 --p_recipient_id IN NUMBER,
45 -- Document recipient site ID
46 --p_recipient_site_id IN NUMBER,
47 -- Date document was sent to recipient
48 --p_date_sent IN DATE,
49 -- Method used to send the document to recipient
50 --p_dispatch_method_code IN NUMBER,
51 -- ID of saved document (document is already in the system)
52 --p_document_id IN NUMBER,
53 -- Physical Location of the document
54 --p_document_location IN VARCHAR2,
55 -- Actual name of File
56 --p_document_name IN VARCHAR2,
57 -- Version of document
58 --p_document_version IN VARCHAR2,
59 -- Category to assign document to
60 --p_document_category IN VARCHAR2,
61 -- Format of file - e.g. XML, pdf, etc
62 --p_file_format IN VARCHAR2,
63 -- Description of document
64 --p_file_description IN VARCHAR2,
65 -- Type of regulatory document - e.g. US16, CA16, etc.
66 --p_document_code IN VARCHAR2,
67 -- Disclosure code used to generate the document
68 --p_disclosure_code IN VARCHAR2,
69 -- Language that document was generated in
70 --p_language IN VARCHAR2,
71 -- Organization document was created for
72 --p_organization_code IN VARCHAR2,
73 -- User id to use for who columns
74 --p_user_id IN NUMBER,
75 -- Specifies the application calling this API (0 - External application ,1- Internal application, 2 - Form)
76 --p_creation_source IN NUMBER,
77
78 --OUT:
79 --x_return_status
80 -- FND_API.G_RET_STS_SUCCESS if API succeeds
81 -- FND_API.G_RET_STS_ERROR if API fails
82 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
83 --x_msg_count Number of error message in the error message
84 -- list
85 --
86 --x_msg_data If the number of error message in the error
87 -- message list is one, the error message
88 -- is in this output parameter
89 --Testing:
90 --
91 -- History:
92 -- M. Grosser 23-May-2005 Modified code for Inventory Convergence.
93 -- Added validation of organization_id and modified validations of
94 -- inventory_item_id and cas_number.
95 -- M. Grosser 29-Jun-2005 Modified code to use organization code from
96 -- organization_id validation for files_upload.
97 --
98 --End of Comments
99 -------------------------------------------------------------------------------
100 PROCEDURE CREATE_DISPATCH_HISTORY_REC(
101 p_item IN VARCHAR2,
102 p_organization_id IN NUMBER,
103 p_inventory_item_id IN NUMBER,
104 p_cas_number IN VARCHAR2,
105 p_recipient_id IN NUMBER,
106 p_recipient_site_id IN NUMBER,
107 p_date_sent IN DATE,
108 p_dispatch_method_code IN NUMBER,
109 p_document_id IN NUMBER,
110 p_document_location IN VARCHAR2,
111 p_document_name IN VARCHAR2,
112 p_document_version IN VARCHAR2,
113 p_document_category IN VARCHAR2,
114 p_file_format IN VARCHAR2,
115 p_file_description IN VARCHAR2,
116 p_document_code IN VARCHAR2,
117 p_disclosure_code IN VARCHAR2,
118 p_language IN VARCHAR2,
119 p_organization_code IN VARCHAR2,
120 p_user_id IN NUMBER,
121 p_creation_source IN NUMBER,
122 x_return_status OUT NOCOPY VARCHAR2 ,
123 x_msg_count OUT NOCOPY NUMBER ,
124 x_msg_data OUT NOCOPY VARCHAR2
125 )
126 IS
127
128 l_progress VARCHAR2(3) := '000';
129
130 l_msg_count NUMBER;
131 l_msg_data VARCHAR2(2000);
132 l_return_status VARCHAR2(1);
133
134
135 --Cursor used to validate document id
136 CURSOR c_val_document_id IS
137 SELECT 1
138 FROM fnd_documents
139 WHERE document_id = p_document_id;
140
141 --Cursor used to see if document,version is already in the system
142 CURSOR c_check_document_edr IS
143 SELECT fnd_document_id , version_label
144 FROM edr_files_b
145 WHERE original_file_name = p_document_name
146 AND version_label = nvl(p_document_version, version_label)
147 order by version_label desc;
148
149 CURSOR c_check_document_fnd IS
150 SELECT document_id
151 FROM fnd_documents_vl
152 WHERE file_name = p_document_name;
153
154 --Cursor used to see if document is already in the system
155 CURSOR c_get_doc_id IS
156 SELECT fnd_document_id
157 FROM edr_files_b
158 WHERE original_file_name = p_document_name;
159
160 --Cursor used to validate document category
161 CURSOR c_val_document_category IS
162 SELECT category_id
163 FROM fnd_document_categories
164 WHERE name = p_document_category;
165
166 --Cursor used to validate document language
167 CURSOR c_val_language IS
168 SELECT 1
169 FROM fnd_languages
170 WHERE language_code = p_language;
171
172 --Cursor used to validate user id
173 CURSOR c_val_user_id IS
174 SELECT 1
175 FROM fnd_user
176 WHERE user_id = p_user_id;
177
178 --Cursor used to retrieve actual file from temp table
179 CURSOR c_get_file_data IS
180 SELECT file_data
181 FROM gr_upload_file_tmp
182 WHERE request_id = -12345;
183
184 --Cursor used to retrieve the next dispatch history id
185 CURSOR c_get_dispatch_history_id IS
186 SELECT gr_dispatch_history_s.nextval
187 FROM DUAL;
188
189 INVALID_VALUE EXCEPTION;
190 INVALID_USER_ID EXCEPTION;
191 INVALID_DOCUMENT_ID EXCEPTION;
192 INVALID_DOCUMENT_CATEGORY EXCEPTION;
193 INVALID_DISPATCH_METHOD_CODE EXCEPTION;
194 INVALID_DISCLOSURE_CODE EXCEPTION;
195 INVALID_DOCUMENT_LANGUAGE EXCEPTION;
196 INVALID_DATE_SENT EXCEPTION;
197 INVALID_CREATION_SOURCE EXCEPTION;
198 INVALID_RECIPIENT_ID EXCEPTION;
199 INVALID_RECIPIENT_SITE_ID EXCEPTION;
200 INVALID_DOCUMENT_VERSION EXCEPTION;
201 FILE_ERROR EXCEPTION;
202
203
204 /**************** Local Variables****************/
205 l_api_name CONSTANT VARCHAR2(30) := 'Creat Dispatch History';
206 l_api_version CONSTANT NUMBER := 1.0;
207 l_temp NUMBER;
208 l_item VARCHAR2(240);
209 l_file_data BLOB;
210 l_category_id NUMBER;
211 l_document_id NUMBER;
212 l_document_version VARCHAR2(15);
213 l_doc_found BOOLEAN := FALSE;
214 l_document_category_id NUMBER;
215 l_dispatch_history_id NUMBER;
216 l_concurrent_id NUMBER;
217 l_document_managment VARCHAR2(50);
218 l_file_exists_action VARCHAR2(50);
219 l_content_type VARCHAR2(100);
220 l_submit_for_approval VARCHAR2(50);
221 l_rowid VARCHAR2(80);
222 l_inventory_item_id NUMBER;
223 l_document_location VARCHAR2(240);
224 l_document_name VARCHAR2(240);
225 l_org INV_VALIDATE.org;
226 dummy BOOLEAN;
227
228
229
230 l_phase VARCHAR2(30) ;
231 l_status VARCHAR2(30) ;
232 l_dev_phase VARCHAR2(30) ;
233 l_dev_status VARCHAR2(30) ;
234 l_line VARCHAR2(80) ;
235 l_message VARCHAR2(240);
236 l_interval NUMBER := 5;
237 l_maxwait NUMBER := 6000;
238 l_ret_status BOOLEAN ;
239 l_commit_file VARCHAR2(1) := 'F';
240
241
242 BEGIN
243
244 -- M. Grosser 23-May-2005 Added code for Inventory Convergence.
245 --
246 l_org.organization_id := p_organization_id;
247
248 -- Validate organization
249 l_temp := INV_VALIDATE.organization(l_org);
250 IF (l_temp = INV_VALIDATE.F) THEN
251 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
252 log_msg('Organization id provided - organization validation failed.');
253 END IF;
254 RAISE INVALID_VALUE;
255 END IF; -- If organization id is valid
256 -- M. Grosser 23-May-2005 End of changes
257
258 IF p_item IS NOT NULL THEN
259 --Validate the item
260
261 -- M. Grosser 23-May-2005 Modified code for Inventory Convergence.
262 IF NOT (GR_VALIDATE.validate_item(p_organization_id, p_item, l_inventory_item_id)) THEN
263 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
264 log_msg('Item provided - item validation failed.');
265 END IF;
266 FND_MESSAGE.SET_NAME('GR','GR_INVALID_ITEM');
267 FND_MESSAGE.SET_TOKEN('ITEM',p_item);
268 FND_MSG_PUB.Add;
269 RAISE INVALID_VALUE;
270 END IF;
271
272 l_item := p_item;
273
274 ELSE
275 --Validate the CAS #, return item
276 -- M. Grosser 23-May-2005 Modified code for Inventory Convergence.
277 IF NOT (GR_VALIDATE.validate_cas_number(p_organization_id, p_cas_number, l_item, l_inventory_item_id)) THEN
278 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
279 log_msg('CAS Number provided - CAS Number validation failed.');
280 END IF;
281 FND_MESSAGE.SET_NAME('GR','GR_INVALID_CAS_NUMBER');
282 FND_MESSAGE.SET_TOKEN('CAS_NUMBER',p_cas_number);
283 FND_MSG_PUB.Add;
284 RAISE INVALID_VALUE;
285 END IF;
286
287 END IF;
288
289 -- M. Grosser 23-May-2005 Added code for Inventory Convergence.
290 --
291 -- Make sure that inventory item matches item if it is not NULL
292 IF ( (p_inventory_item_id is NOT NULL) AND
293 (p_inventory_item_id <> -1) AND
294 (p_inventory_item_id <> l_inventory_item_id)) THEN
295 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
296 log_msg('Inventory Item ID provided - No match to item provided.');
297 END IF;
298 FND_MESSAGE.SET_NAME('GR','GR_NO_ITEM_ID_MATCH');
299 FND_MESSAGE.SET_TOKEN('ITEM_ID',p_inventory_item_id);
300 FND_MESSAGE.SET_TOKEN('ITEM',l_item);
301 FND_MSG_PUB.Add;
302 RAISE INVALID_VALUE;
303
304 END IF; -- ID matches item
305 -- M. Grosser 23-May-2005 End of changes
306
307 IF p_document_id is NOT NULL and p_document_id <> -1 THEN
308
309 --Validate the document_id
310 OPEN c_val_document_id;
311 FETCH c_val_document_id INTO l_temp;
312
313 --If document id not found
314 IF c_val_document_id%NOTFOUND THEN
315 CLOSE c_val_document_id;
316 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
317 log_msg('Document ID provided - Document ID validation failed.');
318 END IF;
319 RAISE INVALID_DOCUMENT_ID;
320 END IF;
321
322 l_document_id := p_document_id ;
323 CLOSE c_val_document_id ;
324
325 ELSE
326
327 -- See if document is already in the sytem
328 OPEN c_check_document_edr;
329 FETCH c_check_document_edr INTO l_document_id, l_document_version;
330 --If document exists check its version. If the version is different then we need
331 --to load a new version of this document.
332 IF c_check_document_edr%FOUND THEN
333 IF p_document_version IS NOT NULL and p_document_version <> l_document_version THEN
334 l_doc_found := FALSE;
335 ELSE
336 l_doc_found := TRUE;
337 END IF;
338 ELSIF c_check_document_edr%NOTFOUND THEN
339 --If document does not exist in fnd documents then we need to upload it.
340 OPEN c_check_document_fnd;
341 FETCH c_check_document_fnd INTO l_document_id;
342
343 IF c_check_document_fnd%NOTFOUND THEN
344 l_doc_found := FALSE;
345 ELSE
346 l_doc_found := TRUE;
347 END IF;
348 CLOSE c_check_document_fnd;
349 END IF;
350 CLOSE c_check_document_edr;
351
352 --If it's not in the system, we will upload it
353 IF NOT l_doc_found THEN
354
355 --Validate the document category
356 OPEN c_val_document_category;
357 FETCH c_val_document_category INTO l_document_category_id;
358
359 --If document category not found
360 IF c_val_document_category%NOTFOUND THEN
361 CLOSE c_val_document_category;
362 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
363 log_msg('Document Category provided - Document Category validation failed.');
364 END IF;
365 RAISE INVALID_DOCUMENT_CATEGORY;
366 END IF;
367
368 CLOSE c_val_document_category;
369
370 --Validate the dispatch_method
371 IF NOT(GR_VALIDATE.validate_dispatch_method_code(p_dispatch_method_code)) THEN
372 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
373 log_msg('Dispatch Method provided - Dispatch Method validation failed.');
374 END IF;
375 RAISE INVALID_DISPATCH_METHOD_CODE;
376 END IF;
377
378
379 --Validate the disclosure code
380 IF NOT(GR_VALIDATE.validate_disclosure_code(p_disclosure_code)) THEN
381 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
382 log_msg('Disclosure Code provided - Disclosure Code validation failed.');
383 END IF;
384 RAISE INVALID_DISCLOSURE_CODE;
385 END IF;
386
387
388 --Validate the document language
389 OPEN c_val_language;
390 FETCH c_val_language INTO l_temp;
391
392 --If document language is not found
393 IF c_val_language %NOTFOUND THEN
394 CLOSE c_val_language;
395 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
396 log_msg('Document Language provided - Document Language validation failed.');
397 END IF;
398 RAISE INVALID_DOCUMENT_LANGUAGE;
399 END IF;
400
401 CLOSE c_val_language;
402
403 IF p_document_version IS NULL THEN
404 RAISE INVALID_DOCUMENT_VERSION;
405 END IF;
406
407
408 l_document_location := substr(p_document_location,1,instr(p_document_location,':')-1);
409 l_document_name := substr(p_document_location,instr(p_document_location,':')+1)||'/'||p_document_name;
410
411 /* Submit the java concurrent program, to upload file to temp table. */
412 l_concurrent_id := FND_REQUEST.SUBMIT_REQUEST
413 ('GR', 'GR_FILE_UPLOAD', '', '', FALSE,
414 l_document_location,l_document_name,
415 '', '', '', '', '', '', '', '',
416 '', '', '', '', '', '', '', '', '', '',
417 '', '', '', '', '', '', '', '', '', '',
418 '', '', '', '', '', '', '', '', '', '',
419 '', '', '', '', '', '', '', '', '', '',
420 '', '', '', '', '', '', '', '', '', '',
421 '', '', '', '', '', '', '', '', '', '',
422 '', '', '', '', '', '', '', '', '', '',
423 '', '', '', '', '', '', '', '', '', '',
424 '', '', '', '', '', '', '', '', '', '');
425 IF l_concurrent_id = 0 THEN
426 /* Java concurrent program failed, to print the attached document to an output file */
427 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
428 log_msg('Document upload to temp table failed.');
429 END IF;
430 FND_MESSAGE.SET_NAME('GR','GR_CONC_REQ_FILE_UPLOAD');
431 FND_MESSAGE.SET_TOKEN('FILE_NAME', p_document_name, FALSE);
432 FND_MSG_PUB.Add;
433 RAISE FILE_ERROR;
434 ELSE
435 COMMIT;
436 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
437 log_msg('Document upload concurrent program fired - concurrent_id:'||to_char(l_concurrent_id));
438 END IF;
439 l_ret_status := FND_CONCURRENT.WAIT_FOR_REQUEST(l_concurrent_id,
440 l_interval,
441 l_maxwait,
442 l_phase,
443 l_status,
444 l_dev_phase,
445 l_dev_status,
446 l_message);
447 IF (NOT l_ret_status) OR ((l_dev_phase = 'COMPLETE') and (l_dev_status = 'ERROR')) THEN
448 RAISE FILE_ERROR;
449 END IF;
450 END IF;
451
452 --Get the handle to the File
453 OPEN c_get_file_data;
454 FETCH c_get_file_data INTO l_file_data;
455 IF c_get_file_data%NOTFOUND THEN
456 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
457 log_msg('Document cannot be read from temp table.');
458 END IF;
459 FND_MESSAGE.SET_NAME('GR','GR_CONC_REQ_FILE_UPLOAD');
460 FND_MESSAGE.SET_TOKEN('FILE_NAME', p_document_name, FALSE);
461 FND_MSG_PUB.Add;
462 RAISE FILE_ERROR;
463 END IF;
464 CLOSE c_get_file_data;
465
466 Delete from gr_upload_file_tmp where request_id = -12345;
467
468 --Get the profile value to use to determine if file should be sent for approvals
469 l_document_managment := FND_PROFILE.Value('GR_DOC_MANAGEMENT');
470
471 /* Set the submit for approval variable */
472 IF l_document_managment = 'A' THEN
473 l_submit_for_approval := 'Y';
474 l_commit_file := 'T';
475 ELSE
476 l_submit_for_approval := 'N';
477 END IF;
478
479 --Get the profile value to use to determine what should be done if the file alredy exists
480 l_file_exists_action := FND_PROFILE.Value('GR_DOC_MGMT_FILE_EXISTS_ACT');
481
482 -- M. Grosser 29-Jun-2005: Changed code to use organization_code returned from org validation
483 --Call the Upload file API to upload the file into the iSignatures system
484 EDR_FILES_PUB.UPLOAD_FILE
485 (
486 P_API_VERSION => 1.0,
487 P_COMMIT => l_commit_file,
488 P_CALLED_FROM_FORMS => 'F',
489 P_FILE_NAME => p_document_name,
490 P_CATEGORY => p_document_category,
491 P_CONTENT_TYPE => p_file_format,
492 P_VERSION_LABEL => p_document_version,
493 P_FILE_DATA => l_file_data,
494 P_FILE_FORMAT => p_file_format,
495 P_SOURCE_LANG => p_language,
496 P_DESCRIPTION => p_file_description,
497 P_FILE_EXISTS_ACTION => l_file_exists_action,
498 P_SUBMIT_FOR_APPROVAL => l_submit_for_approval,
499 P_ATTRIBUTE1 => l_item,
500 P_ATTRIBUTE2 => p_document_code,
501 P_ATTRIBUTE3 => p_language,
502 P_ATTRIBUTE4 => p_disclosure_code,
503 P_ATTRIBUTE5 => l_org.organization_code,
504 P_ATTRIBUTE6 => NULL,
505 P_ATTRIBUTE7 => NULL,
506 P_ATTRIBUTE8 => NULL,
507 P_ATTRIBUTE9 => NULL,
508 P_ATTRIBUTE10 => NULL,
509 P_CREATED_BY => p_user_id,
510 P_CREATION_DATE => SYSDATE,
511 P_LAST_UPDATED_BY => p_user_id,
512 P_LAST_UPDATE_LOGIN => NULL,
513 P_LAST_UPDATE_DATE => SYSDATE,
514 X_RETURN_STATUS => x_return_status,
515 X_MSG_DATA => x_msg_data);
516
517 IF (x_return_status = FND_API.G_RET_STS_ERROR)
518 THEN
519 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
520 log_msg('EDR Upload failed with expected error.');
521 END IF;
522 RAISE FND_API.G_EXC_ERROR;
523 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR)
524 THEN
525 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
526 log_msg('EDR Upload failed with unexpected error.');
527 END IF;
528 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
529 END IF;
530 IF (c_check_document_edr%ISOPEN) THEN
531 CLOSE c_check_document_edr;
532 END IF;
533 IF (c_check_document_fnd%ISOPEN) THEN
534 CLOSE c_check_document_fnd;
535 END IF;
536 -- Since document is uploaded correctly get document_id
537 OPEN c_get_doc_id;
538 FETCH c_get_doc_id INTO l_document_id;
539
540 --If it's not in the system, error out.
541 IF c_get_doc_id%NOTFOUND THEN
542 CLOSE c_get_doc_id;
543 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
544 log_msg('EDR Upload successful but doc id could not be fetched.');
545 END IF;
546 RAISE FILE_ERROR;
547 END IF;
548 CLOSE c_get_doc_id;
549
550 END IF; /* c_check_document%NOTFOUND THEN */
551 END IF; /*p_document_id is NOT NULL */
552
553 --Check the date sent
554 IF p_date_sent IS NULL OR p_date_sent > SYSDATE THEN
555 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
556 log_msg('Date Sent provided : Date Sent validation failed.');
557 END IF;
558 RAISE INVALID_DATE_SENT;
559 END IF;
560
561 --Check the creation source
562 IF p_creation_source NOT IN (0,1,2) THEN
563 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
564 log_msg('Creation Source provided : Creation Source validation failed.');
565 END IF;
566 RAISE INVALID_CREATION_SOURCE;
567 END IF;
568
569 --Validate the recipient_id
570 IF NOT (GR_VALIDATE.validate_recipient_id(p_recipient_id)) THEN
571 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
572 log_msg('Recipient ID provided - Recipient ID validation failed.');
573 END IF;
574 RAISE INVALID_RECIPIENT_ID;
575 END IF;
576
577 --Validate the recipient_site_id
578 IF NOT (GR_VALIDATE.validate_recipient_site_id(p_recipient_id,p_recipient_site_id)) THEN
579 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
580 log_msg('Recipient Site ID provided - Recipient Site ID validation failed.');
581 END IF;
582 RAISE INVALID_RECIPIENT_SITE_ID;
583 END IF;
584
585 --Validate the dispatch_method
586 IF NOT (GR_VALIDATE.validate_dispatch_method_code(p_dispatch_method_code)) THEN
587 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
588 log_msg('Dispatch Method provided - Dispatch Method validation failed.');
589 END IF;
590 RAISE INVALID_DISPATCH_METHOD_CODE;
591 END IF;
592
593 --Validate the user id
594 OPEN c_val_user_id;
595 FETCH c_val_user_id INTO l_temp;
596
597 --If user id not found
598 IF c_val_user_id%NOTFOUND THEN
599 CLOSE c_val_user_id;
600 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
601 log_msg('User ID provided - User ID validation failed.');
602 END IF;
603 RAISE INVALID_USER_ID;
604 END IF;
605
606 CLOSE c_val_user_id;
607
608 --Retrieve new dispatch history id
609 OPEN c_get_dispatch_history_id;
610 FETCH c_get_dispatch_history_id INTO l_dispatch_history_id;
611 CLOSE c_get_dispatch_history_id;
612
613
614 INSERT INTO GR_DISPATCH_HISTORY (
615 dispatch_history_id ,
616 document_id ,
617 item ,
618 organization_id ,
619 inventory_item_id ,
620 recipient_id ,
621 recipient_site_id ,
622 date_sent ,
623 dispatch_method_code ,
624 creation_source ,
625 attribute_category ,
626 attribute1 ,
627 attribute2 ,
628 attribute3 ,
629 attribute4 ,
630 attribute5 ,
631 attribute6 ,
632 attribute7 ,
633 attribute8 ,
634 attribute9 ,
635 attribute10 ,
636 attribute11 ,
637 attribute12 ,
638 attribute13 ,
639 attribute14 ,
640 attribute15 ,
641 attribute16 ,
642 attribute17 ,
643 attribute18 ,
644 attribute19 ,
645 attribute20 ,
646 attribute21 ,
647 attribute22 ,
648 attribute23 ,
649 attribute24 ,
650 attribute25 ,
651 attribute26 ,
652 attribute27 ,
653 attribute28 ,
654 attribute29 ,
655 attribute30 ,
656 created_by ,
657 creation_date ,
658 last_updated_by ,
659 last_update_date ,
660 last_update_login
661 ) VALUES (
662 l_dispatch_history_id ,
663 l_document_id ,
664 l_item ,
665 p_organization_id ,
666 l_inventory_item_id ,
667 p_recipient_id ,
668 p_recipient_site_id ,
669 p_date_sent ,
670 p_dispatch_method_code ,
671 p_creation_source ,
672 NULL ,
673 NULL ,
674 NULL ,
675 NULL ,
676 NULL ,
677 NULL ,
678 NULL ,
679 NULL ,
680 NULL ,
681 NULL ,
682 NULL ,
683 NULL ,
684 NULL ,
685 NULL ,
686 NULL ,
687 NULL ,
688 NULL ,
689 NULL ,
690 NULL ,
691 NULL ,
692 NULL ,
693 NULL ,
694 NULL ,
695 NULL ,
696 NULL ,
697 NULL ,
698 NULL ,
699 NULL ,
700 NULL ,
701 NULL ,
702 NULL ,
703 p_user_id ,
704 sysdate ,
705 p_user_id,
706 sysdate,
707 -1);
708
709 x_return_status := FND_API.G_RET_STS_SUCCESS;
710
711 EXCEPTION
712
713 WHEN INVALID_VALUE THEN
714 x_return_status := FND_API.G_RET_STS_ERROR;
715 FND_MSG_PUB.Count_And_Get (
716 p_count => x_msg_count,
717 p_data => x_msg_data );
718
719 WHEN INVALID_USER_ID THEN
720 FND_MESSAGE.SET_NAME('GR', 'GR_INVALID_USER_ID');
721 FND_MESSAGE.SET_TOKEN('USER_ID', p_user_id);
722 FND_MSG_PUB.Add;
723 x_return_status := FND_API.G_RET_STS_ERROR;
724 FND_MSG_PUB.Count_And_Get (
725 p_count => x_msg_count,
726 p_data => x_msg_data );
727
728 WHEN INVALID_DOCUMENT_ID THEN
729 FND_MESSAGE.SET_NAME('GR', 'GR_INVALID_DOCUMENT_ID');
730 FND_MESSAGE.SET_TOKEN('DOCUMENT_ID', p_document_id);
731 FND_MSG_PUB.Add;
732 x_return_status := FND_API.G_RET_STS_ERROR;
733 FND_MSG_PUB.Count_And_Get (
734 p_count => x_msg_count,
735 p_data => x_msg_data );
736
737 WHEN INVALID_DOCUMENT_CATEGORY THEN
738 FND_MESSAGE.SET_NAME('GR', 'GR_INVALID_DOCUMENT_CATEGORY');
739 FND_MESSAGE.SET_TOKEN('DOC_CATEGORY', p_document_category);
740 FND_MSG_PUB.Add;
741 x_return_status := FND_API.G_RET_STS_ERROR;
742 FND_MSG_PUB.Count_And_Get (
743 p_count => x_msg_count,
744 p_data => x_msg_data );
745
746 WHEN INVALID_DISPATCH_METHOD_CODE THEN
747 FND_MESSAGE.SET_NAME('GR', 'GR_INVALID_DISPATCH_METHOD');
748 FND_MESSAGE.SET_TOKEN('DISPATCH_METHOD_CODE', p_dispatch_method_code);
749 FND_MSG_PUB.Add;
750 x_return_status := FND_API.G_RET_STS_ERROR;
751 FND_MSG_PUB.Count_And_Get (
752 p_count => x_msg_count,
753 p_data => x_msg_data );
754
755 WHEN INVALID_DISCLOSURE_CODE THEN
756 FND_MESSAGE.SET_NAME('GR', 'GR_INVALID_DISCLOSURE_CODE');
757 FND_MESSAGE.SET_TOKEN('CODE', p_disclosure_code);
758 FND_MSG_PUB.Add;
759 x_return_status := FND_API.G_RET_STS_ERROR;
760 FND_MSG_PUB.Count_And_Get (
761 p_count => x_msg_count,
762 p_data => x_msg_data );
763
764 WHEN INVALID_DOCUMENT_LANGUAGE THEN
765 FND_MESSAGE.SET_NAME('GR', 'GR_INVALID_LANGUAGE');
766 FND_MESSAGE.SET_TOKEN('LANGUAGE', p_language);
767 FND_MSG_PUB.Add;
768 x_return_status := FND_API.G_RET_STS_ERROR;
769 FND_MSG_PUB.Count_And_Get (
770 p_count => x_msg_count,
771 p_data => x_msg_data );
772
773 WHEN INVALID_DOCUMENT_VERSION THEN
774 FND_MESSAGE.SET_NAME('GR', 'GR_INVALID_DOC_VERSION');
775 FND_MSG_PUB.Add;
776 x_return_status := FND_API.G_RET_STS_ERROR;
777 FND_MSG_PUB.Count_And_Get (
778 p_count => x_msg_count,
779 p_data => x_msg_data );
780
781 WHEN INVALID_DATE_SENT THEN
782 FND_MESSAGE.SET_NAME('GR', 'GR_INVALID_DATE_SENT');
783 FND_MESSAGE.SET_TOKEN('DATE_SENT', p_date_sent);
784 FND_MSG_PUB.Add;
785 x_return_status := FND_API.G_RET_STS_ERROR;
786 FND_MSG_PUB.Count_And_Get (
787 p_count => x_msg_count,
788 p_data => x_msg_data );
789
790 WHEN INVALID_CREATION_SOURCE THEN
791 FND_MESSAGE.SET_NAME('GR', 'GR_INVALID_CREATION_SOURCE');
792 FND_MESSAGE.SET_TOKEN('CREATION_SOURCE', p_creation_source);
793 FND_MSG_PUB.Add;
794 x_return_status := FND_API.G_RET_STS_ERROR;
795 FND_MSG_PUB.Count_And_Get (
796 p_count => x_msg_count,
797 p_data => x_msg_data );
798
799 WHEN INVALID_RECIPIENT_ID THEN
800 FND_MESSAGE.SET_NAME('GR', 'GR_INVALID_RECIPIENT_ID');
801 FND_MESSAGE.SET_TOKEN('RECIPIENT_ID', p_recipient_id);
802 FND_MSG_PUB.Add;
803 x_return_status := FND_API.G_RET_STS_ERROR;
804 FND_MSG_PUB.Count_And_Get (
805 p_count => x_msg_count,
806 p_data => x_msg_data );
807
808 WHEN INVALID_RECIPIENT_SITE_ID THEN
809 FND_MESSAGE.SET_NAME('GR', 'GR_INVALID_RECIPIENT_SITE_ID');
810 FND_MESSAGE.SET_TOKEN('RECIPIENT_SITE_ID', p_recipient_site_id);
811 FND_MSG_PUB.Add;
812 x_return_status := FND_API.G_RET_STS_ERROR;
813 FND_MSG_PUB.Count_And_Get (
814 p_count => x_msg_count,
815 p_data => x_msg_data );
816 WHEN FILE_ERROR THEN
817 FND_MESSAGE.SET_NAME('GR', 'GR_FILE_ERROR');
818 FND_MESSAGE.SET_TOKEN('FILE', p_document_location||'/'||p_document_name);
819 FND_MSG_PUB.Add;
820 x_return_status := FND_API.G_RET_STS_ERROR;
821 FND_MSG_PUB.Count_And_Get (
822 p_count => x_msg_count,
823 p_data => x_msg_data );
824
825 WHEN FND_API.G_EXC_ERROR THEN
826 x_return_status := FND_API.G_RET_STS_ERROR;
827 FND_MSG_PUB.Count_AND_GET ( p_count => x_msg_count,
828 p_data => x_msg_data);
829
830 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
831 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
832 FND_MSG_PUB.Count_AND_GET ( p_count => x_msg_count,
833 p_data => x_msg_data);
834
835 WHEN OTHERS THEN
836 fnd_msg_pub.add_exc_msg (G_PKG_NAME, l_api_name, SQLERRM);
837 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
838 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
839 p_data => x_msg_data );
840
841 END CREATE_DISPATCH_HISTORY_REC;
842
843 --------------------------------------------------------------------------------
844 --Start of Comments
845 --Name: GET_CAS_NO
846 --Pre-reqs:
847 -- None.
848 --Modifies:
849 -- None.
850 --Locks:
851 -- None.
852 --Function:
853 -- This procedure returns the CAS number for a given item.
854 --Parameters:
855 --IN:
856 -- Version of API to validate compatibility
857 --p_item IN ITEM NUMBER,
858 -- Initialize message stack (TRUE or FALSE)
859
860 --OUT:
861 --p_cas_no CAS Number of the item
862
863 --Testing:
864 --
865 --End of Comments
866 -------------------------------------------------------------------------------
867
868 PROCEDURE GET_CAS_NO(p_item IN varchar2,
869 p_organization_id IN NUMBER,
870 p_cas_no OUT NOCOPY varchar2)
871 IS
872 BEGIN
873 select NVL(CAS_Number,' ')
874 into P_CAS_NO
875 from mtl_system_items_kfv
876 WHERE concatenated_segments = p_item
877 AND organization_id = p_organization_id ;
878
879 EXCEPTION
880 WHEN OTHERS THEN
881 p_cas_no := NULL;
882
883 end;
884
885 --------------------------------------------------------------------------------
886 --Start of Comments
887 --Name: log_msg
888 --Pre-reqs:
889 -- None.
890 --Modifies:
891 -- None.
892 --Locks:
893 -- None.
894 --Function:
895 -- This procedure is used for common logging.
896 --Parameters:
897 --IN:
898 -- Version of API to validate compatibility
899 --p_msg_text IN VARCHAR2
900 -- Initialize message stack (TRUE or FALSE)
901
902 --OUT:
903 --None
904
905 --Testing:
906 --
907 --End of Comments
908 -------------------------------------------------------------------------------
909 PROCEDURE log_msg(p_msg_text IN VARCHAR2) IS
910 BEGIN
911
912 FND_MESSAGE.SET_NAME('GR','GR_DEBUG_API');
913 FND_MESSAGE.SET_TOKEN('MSG',p_msg_text);
914 FND_MSG_PUB.Add;
915
916 END log_msg ;
917
918
919 --------------------------------------------------------------------------------
920 --Start of Comments
921 --Name: ger_organization_code
922 --Pre-reqs:
923 -- None.
924 --Modifies:
925 -- None.
926 --Locks:
927 -- None.
928 --Function:
929 -- This procedure is used to get organization Code
930 --Parameters:
931 --IN:
932 -- Orgn_id - Organization Id
933
934 --OUT:
935 -- Orgn_code - Organization Code
936
937 --Testing:
938 --
939 --End of Comments
940 -------------------------------------------------------------------------------
941 PROCEDURE GET_ORGANIZATION_CODE( p_orgn_id IN NUMBER,
942 p_orgn_code OUT NOCOPY VARCHAR2)
943 IS
944 BEGIN
945 select NVL(ORGANIZATION_CODE,' ') into p_orgn_code
946 from MTL_PARAMETERS
947 where ORGANIZATION_ID = p_orgn_id;
948 EXCEPTION
949 WHEN OTHERS THEN
950 p_orgn_code := NULL;
951 END;
952
953
954 --------------------------------------------------------------------------------
955 --Start of Comments
956 --Name: ger_item_desc
957 --Pre-reqs:
958 -- None.
959 --Modifies:
960 -- None.
961 --Locks:
962 -- None.
963 --Function:
964 -- This procedure is used to get organization Code
965 --Parameters:
966 --IN:
967 -- Orgn_id - Organization Id
968 -- Item_id - Item Id
969
970 --OUT:
971 -- Orgn_code - Organization Code
972
973 --Testing:
974 --
975 --End of Comments
976 -------------------------------------------------------------------------------
977 PROCEDURE GET_ITEM_DESC( P_item_id IN NUMBER,
978 p_orgn_id IN NUMBER,
979 p_item_desc OUT NOCOPY VARCHAR2)
980 IS
981 BEGIN
982 select NVL(DESCRIPTION,' ') into p_item_desc
983 from MTL_SYSTEM_ITEMS
984 where INVENTORY_ITEM_ID = p_item_id
985 and ORGANIZATION_ID = p_orgn_id;
986 EXCEPTION
987 WHEN OTHERS THEN
988 p_item_desc := NULL;
989 END;
990
991 END GR_DISPATCH_HISTORY_PVT; -- Package body