1 PACKAGE BODY GR_REG_PRINT_DOC AS
2 /* $Header: GROMPRTB.pls 120.5 2006/01/16 15:27:24 methomas noship $ */
3
4 /*===========================================================================
5 -- PROCEDURE:
6 -- print_shipping_doc
7 --
8 -- DESCRIPTION:
9 -- This procedure is used to print the documents attached to an object - Regulatory Item,
10 -- Linked Inventory Item, Sales Order, etc. It is meant to be called from the
11 -- Order Management Ship Confirm.
12 --
13 -- PARAMETERS:
14 -- p_delivery_id IN NUMBER - Delivery ID key to the workflow record
15 --
16 -- RETURNS:
17 -- errbuf OUT VARCHAR2 - Returns error message only when this procedure is submitted from a concurrent program.
18 -- retcode OUT VARCHAR2 - Returns error code only when this procedure is submitted from a concurrent program.
19 --
20 -- SYNOPSIS:
21 -- GR_REG_DOC_PRINT.printing_shipping_doc(p_delivery_id);
22 --
23 -- HISTORY
24 -- M. Grosser 13-Jun-2005 Added cursor to retrieve the recipient id
25 -- from the shipment based upon the ship_to_location_id.
26 --=========================================================================== */
27 PROCEDURE print_shipping_doc (errbuf OUT NOCOPY VARCHAR2,
28 retcode OUT NOCOPY VARCHAR2,
29 p_delivery_id IN NUMBER) IS
30
31 /* Bug 4912043 created new bind variables */
32 l_bind_var_msds VARCHAR2(2000);
33 l_bind_var_hazard VARCHAR2(2000);
34 l_bind_var_safety VARCHAR2(2000);
35 l_bind_var_msds_rejected VARCHAR2(2000);
36
37 /* ------------------ CURSORS ---------------------- */
38
39 /* M.Grosser 12-Apr-2005 Added retrieval of inventory_item_id, organization_id and ship_to_location_id
40 for 3rd Party Integration project.
41 */
42 /* Used to get the Delivery Information */
43 CURSOR c_get_delivery_details IS
44 SELECT delivery_detail_id,
45 source_header_id,
46 source_line_id,
47 source_header_number,
48 source_line_number,
49 inventory_item_id,
50 organization_id,
51 ship_to_location_id
52 FROM WSH_DLVY_DELIVERABLES_V
53 WHERE delivery_id = p_delivery_id
54 Order by delivery_detail_id;
55 LocalDeliverydetail c_get_delivery_details%ROWTYPE;
56
57 -- M. Grosser 13-Jun-2005 Added cursor to retrieve the recipient id
58 -- from the shipment based upon the ship_to_location_id.
59 /* Used to retrieve the party id that will actually recieve the document */
60 CURSOR c_get_recipient_id (p_ship_to_location_id NUMBER) IS
61 SELECT party_id
62 FROM hz_party_sites
63 WHERE party_site_id = p_ship_to_location_id;
64
65 /* M.Grosser 12-Apr-2005 Added retrieval of document_id for 3rd Party Integration project.
66 */
67 /* Used to get the attached document information for a Delivery Detail Line */
68 CURSOR c_get_attch_doc_details IS
69 SELECT DISTINCT CATEGORY_DESCRIPTION,
70 FILE_NAME,
71 USER_ENTITY_NAME,
72 MEDIA_ID,
73 DOCUMENT_ID
74 FROM FND_ATTACHED_DOCS_FORM_VL
75 WHERE (security_type=4 OR publish_flag='Y')
76 AND ((entity_name= 'OE_ORDER_LINES'
77 AND pk1_value = LocalDeliverydetail.source_line_id )
78 OR (entity_name= 'WSH_DELIVERY_DETAILS'
79 AND pk1_value = LocalDeliverydetail.delivery_detail_id ))
80 AND CATEGORY_ID IN (SELECT category_id
81 FROM FND_DOCUMENT_CATEGORIES_VL
82 WHERE UPPER(user_name) like l_bind_var_msds
83 OR UPPER(user_name) like l_bind_var_safety
84 OR UPPER(user_name) like l_bind_var_hazard)
85 /*
86 ** 28-May-2004 Mercy Thomas 3211481 removed the column seq_num from the order by clause
87 */
88 order by user_entity_name;
89
90 LocalAttachDocDetail c_get_attch_doc_details%ROWTYPE;
91
92 --, seq_num;
93 /*
94 ** 28-May-2004 Mercy Thomas 3211481 End of the changes.
95 */
96
97 /* M.Grosser 12-Apr-2005 Added for 3rd Party Integration project */
98
99 /* Used to retrieve Item Code */
100 CURSOR c_get_item_code(p_organization_id NUMBER, p_item_id NUMBER)IS
101 Select segment1
102 from mtl_system_items
103 where inventory_item_id = p_item_id
104 and organization_id = p_organization_id;
105
106 /* M.Grosser 12-Apr-2005 End of changes */
107
108
109 /* ------------- LOCAL VARIABLES ------------------- */
110 L_CONCURRENT_ID NUMBER(15);
111 L_CODE_BLOCK VARCHAR2(2000);
112 L_MSG_DATA VARCHAR2(2000);
113 L_ORACLE_ERROR NUMBER;
114 l_item_code VARCHAR2(32);
115 l_recipient_id NUMBER;
116 l_return_status VARCHAR2(3);
117 l_msg_count NUMBER;
118 l_user_id NUMBER;
119 l_error_msg VARCHAR2(2000);
120
121 /* ------------------ EXCEPTIONS ---------------------- */
122 No_Delivery_Details EXCEPTION;
123 Concurrent_Request_Error EXCEPTION;
124
125 BEGIN
126
127 /* Bug 4912043 Populating the bind variables with the message text instead of hardcoding the text in the Query */
128 FND_MESSAGE.SET_NAME('GR','GR_BIND_VAR_MSDS');
129 l_bind_var_msds := FND_MESSAGE.GET;
130
131 FND_MESSAGE.SET_NAME('GR','GR_BIND_VAR_SAFETY');
132 l_bind_var_safety := FND_MESSAGE.GET;
133
134 FND_MESSAGE.SET_NAME('GR','GR_BIND_VAR_HAZARD');
135 l_bind_var_hazard := FND_MESSAGE.GET;
136
137 /* Get the Delivery Details for the delivery Id */
138 OPEN c_get_delivery_details;
139 FETCH c_get_delivery_details into LocalDeliverydetail;
140
141 /* If Delivery Details exists or not */
142 IF c_get_delivery_details%NOTFOUND THEN
143 /* Raise a Error, if there are no delivery details */
144 RAISE No_Delivery_Details;
145 Close c_get_delivery_details;
146 ELSE
147 /* M.Grosser 12-Apr-2005 Added for 3rd Party Integration project */
148 ATTACH_SHIPPING_DOCUMENT(
149 p_delivery_id => p_delivery_id,
150 x_return_status => l_return_status,
151 x_msg_data => l_msg_data );
152
153 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
154 FND_MESSAGE.SET_NAME('GR','GR_SHIPPING_ATTACHMENT_ERROR');
155 FND_MESSAGE.SET_TOKEN('DELIVERY_DETAIL_ID', LocalDeliverydetail.delivery_detail_id, FALSE);
156 l_msg_data := FND_MESSAGE.GET;
157 FND_FILE.PUT(FND_FILE.LOG, l_msg_data);
158 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
159 END IF;
160
161 l_user_id := NVL(FND_PROFILE.VALUE('USER_ID'),0);
162 /* M.Grosser 12-Apr-2005 End of changes */
163
164 /* Delivery Details exists */
165 WHILE c_get_delivery_details%FOUND LOOP
166 /* Post the log with the Delivery Id for which the Shipping Report is executed */
167 FND_MESSAGE.SET_NAME('GR','GR_SHIP_CONFIRM_TEXT');
168 FND_MESSAGE.SET_TOKEN('DELIVERY_ID', p_delivery_id, FALSE);
169 l_msg_data := FND_MESSAGE.GET;
170 FND_FILE.PUT(FND_FILE.LOG, l_msg_data);
171 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
172 /* Get the attached documents for a delivery detail line */
173 OPEN c_get_attch_doc_details;
174 FETCH c_get_attch_doc_details into LocalAttachDocDetail;
175 /* If attached documents exists or not */
176 IF c_get_attch_doc_details%NOTFOUND THEN
177 /* No attached documents exists for the delivery detail line, post the log with the information */
178 Close c_get_attch_doc_details;
179 FND_MESSAGE.SET_NAME('GR','GR_NO_ATTACHED_DOC_TEXT');
180 FND_MESSAGE.SET_TOKEN('DELIVERY_DETAIL_ID', LocalDeliverydetail.delivery_detail_id, FALSE);
181 l_msg_data := FND_MESSAGE.GET;
182 FND_FILE.PUT(FND_FILE.LOG, l_msg_data);
183 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
184 ELSE
185 /* Attached documents exists for the delivery detail line */
186 WHILE c_get_attch_doc_details%FOUND LOOP
187 /* Submit the java concurrent program, to print the attached document to an output file */
188 l_concurrent_id := FND_REQUEST.SUBMIT_REQUEST
189 ('GR', 'GR_PRINT_SHIP_DOC', '', '', FALSE,
190 LocalAttachDocDetail.media_id,
191 '', '', '', '', '', '', '', '', '',
192 '', '', '', '', '', '', '', '', '', '',
193 '', '', '', '', '', '', '', '', '', '',
194 '', '', '', '', '', '', '', '', '', '',
195 '', '', '', '', '', '', '', '', '', '',
196 '', '', '', '', '', '', '', '', '', '',
197 '', '', '', '', '', '', '', '', '', '',
198 '', '', '', '', '', '', '', '', '', '',
199 '', '', '', '', '', '', '', '', '', '',
200 '', '', '', '', '', '', '', '', '', '');
201
202 IF l_concurrent_id = 0 THEN
203 /* Java concurrent program failed, to print the attached document to an output file */
204 FND_MESSAGE.SET_NAME('GR','GR_CONC_REQ_PRINT_SHIP');
205 FND_MESSAGE.SET_TOKEN('FILE_NAME', LocalAttachDocDetail.file_name, FALSE);
206 l_msg_data := FND_MESSAGE.GET;
207 FND_FILE.PUT(FND_FILE.LOG, l_msg_data);
208 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
209 ELSE
210
211 /* Java concurrent program was succesfull, therefore, post the information with regard to the delivery details and the attached documents to the log */
212 FND_MESSAGE.SET_NAME('GR','GR_REG_DOC_PRINT_TEXT');
213 l_msg_data := FND_MESSAGE.GET;
214 FND_FILE.PUT(FND_FILE.LOG, l_msg_data);
215 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
216 /* Post the Delivery Detail ID to the log for which the document was printed */
217 FND_MESSAGE.SET_NAME('GR','GR_DELIVERY_DETAIL_ID_TEXT');
218 FND_MESSAGE.SET_TOKEN('DELIVERY_DETAIL_ID', LocalDeliverydetail.delivery_detail_id, FALSE);
219 l_msg_data := FND_MESSAGE.GET;
220 FND_FILE.PUT(FND_FILE.LOG, l_msg_data);
221 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
222
223 /* Post the Delivery Detail ID to the log for which the document was printed */
224 FND_MESSAGE.SET_NAME('GR','GR_ORDER_ID_TEXT');
225 FND_MESSAGE.SET_TOKEN('ORDER_ID', LocalDeliverydetail.Source_Header_id, FALSE);
226 l_msg_data := FND_MESSAGE.GET;
227 FND_FILE.PUT(FND_FILE.LOG, l_msg_data);
228 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
229
230 /* Post the Sales Order Line ID to the log for which the document was printed */
231 FND_MESSAGE.SET_NAME('GR','GR_ORDER_LINE_ID_TEXT');
232 FND_MESSAGE.SET_TOKEN('ORDER_LINE_ID', LocalDeliverydetail.Source_Line_id, FALSE);
233 l_msg_data := FND_MESSAGE.GET;
234 FND_FILE.PUT(FND_FILE.LOG, l_msg_data);
235 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
236
237 /* Post the Sales Order Number to the log for which the document was printed */
238 FND_MESSAGE.SET_NAME('GR','GR_ORD_NUMBER_TEXT');
239 FND_MESSAGE.SET_TOKEN('ORDER_NUMBER', LocalDeliverydetail.Source_Header_Number, FALSE);
240 l_msg_data := FND_MESSAGE.GET;
241 FND_FILE.PUT(FND_FILE.LOG, l_msg_data);
242 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
243
244 /* Post the Sales Order Line Number to the log for which the document was printed */
245 FND_MESSAGE.SET_NAME('GR','GR_ORD_LINE_NO_TEXT');
246 FND_MESSAGE.SET_TOKEN('ORDER_LINE_NO', LocalDeliverydetail.Source_Line_Number, FALSE);
247 l_msg_data := FND_MESSAGE.GET;
248 FND_FILE.PUT(FND_FILE.LOG, l_msg_data);
249 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
250
251 /* Post the Category Description to the log for which the document was printed */
252 FND_MESSAGE.SET_NAME('GR','GR_CATEGORY_DESC_TEXT');
253 FND_MESSAGE.SET_TOKEN('CATEGORY_DESC', LocalAttachDocDetail.category_description, FALSE);
254 l_msg_data := FND_MESSAGE.GET;
255 FND_FILE.PUT(FND_FILE.LOG, l_msg_data);
256 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
257
258 /* Post the File Name to the log for which the document was printed */
259 FND_MESSAGE.SET_NAME('GR','GR_FILE_NAME_TEXT');
260 FND_MESSAGE.SET_TOKEN('FILE_NAME', LocalAttachDocDetail.file_name, FALSE);
261 l_msg_data := FND_MESSAGE.GET;
262 FND_FILE.PUT(FND_FILE.LOG, l_msg_data);
263 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
264
265 /* Post the Concurrent request id for the java concurrent program to the log for which the document was printed */
266 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
267 FND_MESSAGE.SET_NAME('GR','GR_DOC_CONC_REQ_ID_TEXT');
268 FND_MESSAGE.SET_TOKEN('DOC_CONC_REQ_ID', l_concurrent_id, FALSE);
269 l_msg_data := FND_MESSAGE.GET;
270 FND_FILE.PUT(FND_FILE.LOG, l_msg_data);
271 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
272
273 /* Post the Entity Name to the log for which the document was printed */
274 FND_MESSAGE.SET_NAME('GR','GR_ENTITY_NAME_TEXT');
275 FND_MESSAGE.SET_TOKEN('ENTITY_NAME', LocalAttachDocDetail.user_entity_name, FALSE);
276 l_msg_data := FND_MESSAGE.GET;
277 FND_FILE.PUT(FND_FILE.LOG, l_msg_data);
278 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
279
280 /* M.Grosser 12-Apr-2005 Added for 3rd Party Integration project */
281 OPEN c_get_item_code(LocalDeliverydetail.organization_id,LocalDeliverydetail.inventory_item_id);
282 FETCH c_get_item_code into l_item_code;
283 IF c_get_item_code%NOTFOUND then
284 FND_MESSAGE.SET_NAME('GR','GR_INVALID_ITEM_ORG_ID');
285 FND_MESSAGE.SET_TOKEN('ITEM_ID', LocalDeliverydetail.INVENTORY_ITEM_ID, FALSE);
286 FND_MESSAGE.SET_TOKEN('ORGN_ID', LocalDeliverydetail.ORGANIZATION_ID, FALSE);
287 l_msg_data := FND_MESSAGE.GET;
288 FND_FILE.PUT(FND_FILE.LOG, l_msg_data);
289 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
290 RETURN;
291 END IF;
292 CLOSE c_get_item_code;
293
294 -- M. Grosser 13-Jun-2005 Added cursor to retrieve the recipient id
295 -- from the shipment based upon the ship_to_location_id.
296 --
297 OPEN c_get_recipient_id(LocalDeliverydetail.ship_to_location_id);
298 FETCH c_get_recipient_id into l_recipient_id;
299 IF c_get_recipient_id%NOTFOUND then
300 FND_MESSAGE.SET_NAME('GR','GR_INVALID_RECIPIENT_SITE');
301 FND_MESSAGE.SET_TOKEN('RECIPIENT_SITE_ID', LocalDeliverydetail.SHIP_TO_LOCATION_ID, FALSE);
302 l_msg_data := FND_MESSAGE.GET;
303 FND_FILE.PUT(FND_FILE.LOG, l_msg_data);
304 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
305 RETURN;
306 END IF;
307 CLOSE c_get_recipient_id;
308
309
310 FND_MESSAGE.SET_NAME('GR', 'GR_CREATING_DISPATCH_HISTORY');
311 FND_MESSAGE.SET_TOKEN('FILE_NAME',localAttachDocDetail.File_name, FALSE);
312 l_msg_data := FND_MESSAGE.GET;
313 FND_FILE.PUT(FND_FILE.LOG, l_msg_data);
314 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
315 /* Call Update Dispatch History API with creation_source = 1 (Internal)*/
316 GR_DISPATCH_HISTORY_PUB.create_dispatch_history (
317 p_api_version => 1.0,
318 p_init_msg_list => FND_API.G_FALSE,
319 p_commit => FND_API.G_TRUE,
320 p_item => l_item_code,
321 p_organization_id => LocalDeliverydetail.organization_id,
322 p_inventory_item_id => LocalDeliverydetail.inventory_item_id,
323 p_recipient_id => l_recipient_id,
324 p_recipient_site_id => LocalDeliverydetail.ship_to_location_id,
325 p_date_sent => SYSDATE,
326 p_dispatch_method_code => 3,
327 p_document_id => localAttachDocDetail.document_id,
328 p_user_id => l_user_id,
329 p_creation_source => 1,
330 p_cas_number => NULL,
331 p_document_location => NULL,
332 p_document_name => NULL,
333 p_document_version => NULL,
334 p_document_category => NULL,
335 p_file_format => NULL,
336 p_file_description => NULL,
337 p_document_code => NULL,
338 p_disclosure_code => NULL,
339 p_language => NULL,
340 p_organization_code => NULL,
341 x_return_status => l_return_status,
342 x_msg_count => l_msg_count,
343 x_msg_data => l_error_msg );
344
345 -- M. Grosser 13-Jun-2005 End of changes
346
347 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
348 FND_MESSAGE.SET_NAME('GR', 'GR_DISPATCH_HISTORY_FAILED');
349 FND_MESSAGE.SET_TOKEN('FILE_NAME',localAttachDocDetail.File_name, FALSE);
350 l_msg_data := FND_MESSAGE.GET;
351 FND_FILE.PUT(FND_FILE.LOG, l_msg_data);
352 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
353 FND_FILE.PUT(FND_FILE.LOG, l_error_msg);
354 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
355 END IF;
356 /* M.Grosser 12-Apr-2005 End of changes */
357
358 END IF; /* g_concurrent_id */
359 FETCH c_get_attch_doc_details INTO LocalAttachDocDetail;
360 END LOOP;
361 Close c_get_attch_doc_details;
362 END IF; /* c_get_attch_doc_details%NOTFOUND */
363 FETCH c_get_delivery_details INTO LocalDeliverydetail;
364 END LOOP;
365 Close c_get_delivery_details;
366 END IF; /* c_get_delivery_details%NOTFOUND */
367
368 EXCEPTION
369 WHEN No_Delivery_Details THEN
370 FND_MESSAGE.SET_NAME('GR','GR_NO_DELIVERY_DETAILS');
371 FND_MESSAGE.SET_TOKEN('DELIVERY_ID', p_delivery_id, FALSE);
372 WHEN OTHERS THEN
373 l_code_block :='Print Shipping Doc ' || ' ' ||TO_CHAR(l_oracle_error);
374 FND_MESSAGE.SET_NAME('GR','GR_UNEXPECTED_ERROR');
375 FND_MESSAGE.SET_TOKEN('TEXT',l_code_block||sqlerrm, FALSE);
376
377 END print_shipping_doc;
378
379
380
381 /*===========================================================================
382 -- PROCEDURE:
383 -- attach_shipping_document
384 --
385 -- DESCRIPTION:
386 -- This procedure is used to attach a Regulatory document to a Shipment line
387 -- if no other Regulatory documents have been attached to that line.
388 --
389 -- PARAMETERS:
390 -- p_delivery_id IN NUMBER - Delivery ID key of Shipment
391 -- x_return_status OUT NOCOPY VARCHAR2 - Status of procedure execution
392 -- x_msg_data OUT NOCOPY VARCHAR2 - Error message, if error has occurred
393 --
394 -- SYNOPSIS:
395 -- GR_REG_DOC_PRINT.attach_shipping_document(p_delivery_id, l_return_status, l_msg_data);
396 --
397 -- BUG#4431025 : Attachment Convergence Coding
398 Removed cursor c_regulatory_item , Modified cursor c_get_last_dispatch_date
399 replacing item_code by inventory_item_id.
400 -- HISTORY
401 --=========================================================================== */
402 PROCEDURE ATTACH_SHIPPING_DOCUMENT(
403 p_delivery_id IN NUMBER,
404 x_return_status OUT NOCOPY VARCHAR2,
405 x_msg_data OUT NOCOPY VARCHAR2) IS
406
407 /* ------------- LOCAL VARIABLES ------------------- */
408 l_item_code VARCHAR2(32);
409 l_code_block VARCHAR2(2000);
410 l_msg_data VARCHAR2(2000);
411 l_return_status VARCHAR2(2);
412 l_create_attachment VARCHAR2(2);
413 l_rowid VARCHAR2(200);
414 l_msg_count NUMBER;
415 l_user_id NUMBER(15);
416 --removed l_regulatory_item_exists NUMBER(5);
417 l_attached_document_id NUMBER(15);
418 l_seq NUMBER;
419 l_last_dispatch_date DATE;
420
421 /* Bug 4912043 created new bind variables */
422 l_bind_var_msds VARCHAR2(2000);
423 l_bind_var_hazard VARCHAR2(2000);
424 l_bind_var_safety VARCHAR2(2000);
425 l_bind_var_msds_rejected VARCHAR2(2000);
426
427 /* ------------------ CURSORS ---------------------- */
428 /*Used to get the Shipment Line Details */
429 CURSOR c_get_delivery_details IS
430 SELECT delivery_detail_id,
431 source_line_id,
432 inventory_item_id ,
433 organization_id,
434 ship_to_location_id,
435 customer_id
436 FROM WSH_DLVY_DELIVERABLES_V
437 WHERE delivery_id = p_delivery_id
438 Order by delivery_detail_id;
439 LocalDeliverydetail c_get_delivery_details%ROWTYPE;
440
441 /* Used to get the attached document information for a Delivery Detail Line*/
442 CURSOR c_get_attch_doc_details IS
443 SELECT
444 a.entity_name
445 FROM fnd_attached_documents a,
446 fnd_documents d
447 WHERE
448 d.category_id IN
449 (SELECT category_id
450 FROM FND_DOCUMENT_CATEGORIES
451 WHERE ( UPPER(name) like l_bind_var_msds or
452 UPPER(name) like l_bind_var_hazard or
453 UPPER(name) like l_bind_var_safety ) AND
454 UPPER(name) NOT like l_bind_var_msds_rejected
455 )
456 AND d.document_id = a.document_id
457 AND (( a.entity_name = 'OE_ORDER_LINES' AND a.pk1_value = LocalDeliverydetail.source_line_id )
458 OR (a.entity_name = 'WSH_DELIVERY_DETAILS' AND a.pk1_value = LocalDeliverydetail.delivery_detail_id ));
459 LocalAttachDocDetail c_get_attch_doc_details%ROWTYPE;
460
461 /* Used to retrieve Item Code */
462 CURSOR c_get_item_code(p_organization_id NUMBER, p_item_id NUMBER)IS
463 Select segment1
464 from mtl_system_items
465 where inventory_item_id = p_item_id
466 and organization_id = p_organization_id;
467
468 /* Used to Check the Item Regulatory Item or not
469 CURSOR c_regulatory_item IS
470 SELECT 1
471 FROM dual
472 WHERE EXISTS (SELECT 1 FROM gr_item_general where item_code = l_item_code)
473 OR EXISTS (SELECT 1 FROM gr_generic_items_b where item_no = l_item_code); */
474
475 /* Used to get the Territory Details */
476 CURSOR c_territory_details IS
477 select p.document_code, p.language, p.disclosure_code
478 from GR_COUNTRY_PROFILES p,
479 HZ_LOCATIONS l
480 where p.territory_code = l.country and
481 l.location_id = LocalDeliverydetail.ship_to_location_id;
482 l_territory_details c_territory_details%ROWTYPE;
483
484 /* Used to get the Country code if there is no territory profile set up */
485 CURSOR c_get_country IS
486 SELECT country
487 FROM HZ_LOCATIONS
488 WHERE location_id = LocalDeliverydetail.ship_to_location_id;
489 CountryRec c_get_country%ROWTYPE;
490
491 /* Used to get the Latest Details of a Regulatory Item Document */
492 CURSOR c_latest_document IS
493 SELECT *
494 FROM fnd_documents_vl
495 WHERE DOC_ATTRIBUTE1 = l_item_code
496 and DOC_ATTRIBUTE2 = l_territory_details.DOCUMENT_CODE
497 and DOC_ATTRIBUTE3 = l_territory_details.LANGUAGE
498 and DOC_ATTRIBUTE4 = l_territory_details.DISCLOSURE_CODE
499 and ( UPPER(DOC_ATTRIBUTE_CATEGORY) like l_bind_var_msds or
500 UPPER(DOC_ATTRIBUTE_CATEGORY) like l_bind_var_hazard or
501 UPPER(DOC_ATTRIBUTE_CATEGORY) like l_bind_var_safety )
502 AND UPPER(DOC_ATTRIBUTE_CATEGORY) NOT like l_bind_var_msds_rejected
503 And publish_flag = 'Y'
504 ORDER BY CREATION_DATE DESC;
505 LocalLatestDocument c_latest_document%ROWTYPE;
506
507 /* Used to get the Latest Dispatch details of the Item Document */
508 Cursor c_get_last_dispatch_date IS
509 Select max(date_sent)
510 From GR_DISPATCH_HISTORY_V
511 Where INVENTORY_ITEM_ID = LocalDeliverydetail.inventory_item_id -- BUG#4431025
512 and ORGANIZATION_ID = LocalDeliverydetail.organization_id -- BUG#4431025
513 and DOCUMENT_CODE = l_territory_details.DOCUMENT_CODE
514 and DOCUMENT_LANGUAGE = l_territory_details.LANGUAGE
515 and DISCLOSURE_CODE = l_territory_details.DISCLOSURE_CODE
516 and Recipient_id = LocalDeliveryDetail.customer_id;
517
518 /* Get Sequence number for Next New attachment */
519 CURSOR c_get_seq IS
520 SELECT NVL(max(seq_num),0) + 10
521 FROM fnd_attached_documents
522 WHERE entity_name= 'WSH_DELIVERY_DETAILS'
523 AND pk1_value = LocalDeliverydetail.delivery_detail_id;
524
525 /*Used to get the next attached_document_id for a new attachment*/
526 CURSOR c_get_id IS
527 SELECT fnd_attached_documents_s.nextval
528 FROM dual;
529
530
531 BEGIN
532
533 /* Bug 4912043 Populating the bind variables with the message text instead of hardcoding the text in the Query */
534 FND_MESSAGE.SET_NAME('GR','GR_BIND_VAR_MSDS');
535 l_bind_var_msds := FND_MESSAGE.GET;
536
537 FND_MESSAGE.SET_NAME('GR','GR_BIND_VAR_SAFETY');
538 l_bind_var_safety := FND_MESSAGE.GET;
539
540 FND_MESSAGE.SET_NAME('GR','GR_BIND_VAR_HAZARD');
541 l_bind_var_hazard := FND_MESSAGE.GET;
542
543 FND_MESSAGE.SET_NAME('GR','GR_BIND_VAR_MSDS_REJECTED');
544 l_bind_var_msds_rejected := FND_MESSAGE.GET;
545
546 l_user_id := NVL(FND_PROFILE.VALUE('USER_ID'),0);
547
548 OPEN c_get_delivery_details;
549 FETCH c_get_delivery_details into LocalDeliverydetail;
550
551 /* For each line on the given Shipment */
552 IF c_get_delivery_details%FOUND THEN
553
554 WHILE c_get_delivery_details%FOUND LOOP
555
556 /* Get item code */
557 OPEN c_get_item_code(LocalDeliverydetail.organization_id, LocalDeliverydetail.inventory_item_id);
558 FETCH c_get_item_code into l_item_code;
559 CLOSE c_get_item_code;
560
561 /* Check to see if the item on this Shipment line is a Regulatory Item
562 OPEN c_regulatory_item;
563 FETCH c_regulatory_item INTO l_regulatory_item_exists;
564
565 /* If this is a Regulatory Item
566 IF c_regulatory_item%FOUND THEN */
567
568 /* Check Regulatory document has already been attached to the Shipment or Sales Order line */
569 OPEN c_get_attch_doc_details;
570 FETCH c_get_attch_doc_details into LocalAttachDocDetail;
571
572 /* If a document has NOT already been attached*/
573 IF c_get_attch_doc_details%NOTFOUND THEN
574
575 /* Retrieve document type, language, disclosure code for territory */
576 OPEN c_territory_details;
577 FETCH c_territory_details INTO l_territory_details;
578
579 /* If a territory profile for that territory is NOT found */
580 IF c_territory_details%NOTFOUND THEN
581
582 OPEN c_get_country;
583 FETCH c_get_country into CountryRec;
584 CLOSE c_get_country;
585
586 FND_MESSAGE.SET_NAME('GR', 'GR_NO_TERRITORY_PROFILE');
587 FND_MESSAGE.SET_TOKEN('COUNTRY', CountryRec.country, FALSE);
588 l_msg_data := FND_MESSAGE.GET;
589 FND_FILE.PUT(FND_FILE.LOG, l_msg_data);
590 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
591
592 ELSE
593 /* Retrieve the latest version of the document that matches the item, document_type, language and
594 disclosure code from the territory profile */
595 OPEN c_latest_document;
596 FETCH c_latest_document into LocalLatestDocument;
597
598 /*If no document meets that criteria */
599 IF c_latest_document%NOTFOUND THEN
600 /* Write no document found message to error log */
601 FND_MESSAGE.SET_NAME('GR', 'GR_NO_ATTACH_DOCUMENT');
602 FND_MESSAGE.SET_TOKEN('ITEM', l_item_code, FALSE);
603 FND_MESSAGE.SET_TOKEN('DOC_CODE', l_territory_details.document_code, FALSE);
604 FND_MESSAGE.SET_TOKEN('LANG', l_territory_details.language, FALSE);
605 FND_MESSAGE.SET_TOKEN('DISCLOSURE', l_territory_details.disclosure_code, FALSE);
606 l_msg_data := FND_MESSAGE.GET;
607 FND_FILE.PUT(FND_FILE.LOG, l_msg_data);
608 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
609
610 ELSE
611
612 /* Check the dispatch history to see the last time this document was sent to the recipient */
613 OPEN c_get_last_dispatch_date;
614 FETCH c_get_last_dispatch_date into l_last_dispatch_date;
615
616 /* If a Dispatch History record is NOT found, attach the document to the shipment line */
617 IF l_last_dispatch_date IS NULL THEN
618 l_create_attachment := 'Y';
619 ELSE
620 /* If the retrieved document is newer than the last one that was sent */
621 IF l_last_dispatch_date < LocalLatestDocument.creation_date THEN
622 l_create_attachment := 'Y';
623 ELSE
624 l_create_attachment := 'N';
625 END IF;
626 END IF;
627 CLOSE c_get_last_dispatch_date;
628
629 IF l_create_attachment = 'Y' THEN
630
631 OPEN c_get_id;
632 FETCH c_get_id INTO l_attached_document_id;
633 CLOSE c_get_id;
634
635 /* Get the next sequence number */
636 OPEN c_get_seq;
637 FETCH c_get_seq INTO l_seq;
638 IF c_get_seq%NOTFOUND THEN
639 l_seq := 10;
640 END IF;
641 CLOSE c_get_seq;
642
643 FND_ATTACHED_DOCUMENTS_PKG.Insert_Row(
644 X_Rowid => l_rowid,
645 X_attached_document_id => l_attached_document_id,
646 X_document_id => LocalLatestDocument.document_id,
647 X_creation_date => SYSDATE,
648 X_created_by => l_user_id,
649 X_last_update_date => SYSDATE,
650 X_last_updated_by => l_user_id,
651 X_last_update_login => NULL,
652 X_seq_num => l_seq,
653 X_entity_name => 'WSH_DELIVERY_DETAILS',
654 X_column1 => NULL,
655 X_pk1_value => LocalDeliverydetail.delivery_detail_id,
656 X_pk2_value => NULL,
657 X_pk3_value => NULL,
658 X_pk4_value => NULL,
659 X_pk5_value => NULL,
660 X_automatically_added_flag => 'Y',
661 X_datatype_id => LocalLatestDocument.datatype_id,
662 X_category_id => LocalLatestDocument.category_id,
663 X_security_type => LocalLatestDocument.security_type,
664 X_security_id => LocalLatestDocument.security_id,
665 X_publish_flag => LocalLatestDocument.publish_flag,
666 X_storage_type => LocalLatestDocument.storage_type,
667 X_usage_type => LocalLatestDocument.usage_type,
668 X_language => LocalLatestDocument.doc_attribute3,
669 X_description => LocalLatestDocument.description,
670 X_file_name => LocalLatestDocument.file_name,
671 X_media_id => LocalLatestDocument.media_id,
672 X_attribute_category => LocalLatestDocument.doc_attribute_category,
673 X_attribute1 => LocalLatestDocument.doc_attribute1,
674 X_attribute2 => LocalLatestDocument.doc_attribute2,
675 X_attribute3 => LocalLatestDocument.doc_attribute3,
676 X_attribute4 => LocalLatestDocument.doc_attribute4,
677 X_attribute5 => LocalLatestDocument.doc_attribute5,
678 X_attribute6 => LocalLatestDocument.doc_attribute6,
679 X_attribute7 => LocalLatestDocument.doc_attribute7,
680 X_attribute8 => LocalLatestDocument.doc_attribute8,
681 X_attribute9 => LocalLatestDocument.doc_attribute9,
682 X_attribute10 => LocalLatestDocument.doc_attribute10,
683 X_attribute11 => LocalLatestDocument.doc_attribute11,
684 X_attribute12 => LocalLatestDocument.doc_attribute12,
685 X_attribute13 => LocalLatestDocument.doc_attribute13,
686 X_attribute14 => LocalLatestDocument.doc_attribute14,
687 X_attribute15 => LocalLatestDocument.doc_attribute15,
688 X_create_doc => 'N');
689
690 END IF; /* Create attachment */
691
692 END IF; /*latest document found */
693 CLOSE c_latest_document;
694
695 END IF; /* territory Details */
696 CLOSE c_territory_details;
697
698 END IF; /* attachments check */
699 CLOSE c_get_attch_doc_details;
700
701
702 FETCH c_get_delivery_details into LocalDeliverydetail;
703
704 END LOOP;
705 END IF;/*DELIVERY DETAILS*/
706
707 l_return_status := FND_API.G_RET_STS_SUCCESS;
708
709 EXCEPTION
710 WHEN OTHERS THEN
711 l_code_block :='Shipping Attachment :' ;
712 FND_MESSAGE.SET_NAME('GR','GR_UNEXPECTED_ERROR');
713 FND_MESSAGE.SET_TOKEN('TEXT',l_code_block||sqlerrm, FALSE);
714 x_msg_data := FND_MESSAGE.GET;
715 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
716 END;
717
718
719 /*===========================================================================
720 -- PROCEDURE:
721 -- print_reg_docs
722 --
723 -- DESCRIPTION:
724 -- This procedure is used to print the current version of approved documents
725 -- that fall within the specified ranges.
726 --
727 -- PARAMETERS:
728 -- errbuf OUT NOCOPY VARCHAR2 - Error message, when submitted from concurrent program
729 -- retcode OUT NOCOPY VARCHAR2 - Error code, when submitted from concurrent program
730 -- p_orgn_id IN NUMBER - Organization_id to search items in.
731 -- p_from_item IN VARCHAR2 - First item in range
732 -- p_to_item IN VARCHAR2 - Last item in range
733 - p_from_language IN VARCHAR2 - First language in range
734 -- p_to_language IN VARCHAR2 - Last language in range
735 -- p_document_category IN VARCHAR2 - Document category to retrict documents to
736 -- p_update_dispatch_history IN VARCHAR2 - Update Dispatch History - 'Y'es or 'N'o
737 -- p_recipent_site IN NUMBER - ID of site receiving the dispatch
738 --
739 -- SYNOPSIS:
740 -- GR_REG_DOC_PRINT.print_reg_item_docs(errbuf,retcode,p_from_item,p_to_item,p_from_lang,
741 -- p_to_lang,p_doc_category,p_upd_disp_hist,p_recipient_site);
742 --
743 -- HISTORY
744 --=========================================================================== */
745
746
747 PROCEDURE PRINT_REG_DOCS(
748 errbuf OUT NOCOPY VARCHAR2
749 ,retcode OUT NOCOPY VARCHAR2
750 ,p_orgn_id IN NUMBER
751 ,p_from_item IN VARCHAR2
752 ,p_to_item IN VARCHAR2
753 ,p_from_language IN VARCHAR2
754 ,p_to_language IN VARCHAR2
755 ,p_document_category IN VARCHAR2
756 ,p_update_dispatch_history IN VARCHAR2
757 ,p_recipient_site IN VARCHAR2
758 ) IS
759
760 l_orgn_code varchar2(3);
761
762 /* Bug 4912043 created new bind variables */
763 l_bind_var_msds VARCHAR2(2000);
764 l_bind_var_hazard VARCHAR2(2000);
765 l_bind_var_safety VARCHAR2(2000);
766 l_bind_var_msds_rejected VARCHAR2(2000);
767
768 /* ------------------ CURSORS ---------------------- */
769 /* Used to get the document information for all Regulatory document categories */
770 CURSOR c_get_doc_info IS
771 SELECT *
772 FROM fnd_documents_vl a
773 WHERE a.creation_date = (SELECT max(b.creation_date) FROM fnd_documents_vl b
774 WHERE a.doc_attribute1 = b.doc_attribute1
775 AND a.doc_attribute3 = b.doc_attribute3
776 AND a.doc_attribute5 = l_orgn_code
777 AND (UPPER(b.DOC_ATTRIBUTE_CATEGORY) like l_bind_var_msds
778 OR UPPER(b.DOC_ATTRIBUTE_CATEGORY) like l_bind_var_safety
779 OR UPPER(b.DOC_ATTRIBUTE_CATEGORY) like l_bind_var_hazard)
780 AND UPPER(b.DOC_ATTRIBUTE_CATEGORY) not like l_bind_var_msds_rejected
781 AND ( (a.DOC_ATTRIBUTE_CATEGORY = p_document_category))
782 AND b.publish_flag = 'Y')
783 AND a.DOC_ATTRIBUTE1 >= nvl(p_from_item,' ')
784 AND a.DOC_ATTRIBUTE1 <= nvl(p_to_item,'ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ')
785 AND a.DOC_ATTRIBUTE3 >= nvl(p_from_language,' ')
786 AND a.DOC_ATTRIBUTE3 <= nvl(p_to_language,'ZZZZ')
787 AND (UPPER(a.DOC_ATTRIBUTE_CATEGORY) like l_bind_var_msds
788 OR UPPER(a.DOC_ATTRIBUTE_CATEGORY) like l_bind_var_safety
789 OR UPPER(a.DOC_ATTRIBUTE_CATEGORY) like l_bind_var_hazard)
790 AND UPPER(a.DOC_ATTRIBUTE_CATEGORY) not like l_bind_var_msds_rejected
791 AND ((a.DOC_ATTRIBUTE_CATEGORY = p_document_category))
792 AND a.publish_flag = 'Y';
793 DocumentRec c_get_doc_info%ROWTYPE;
794
795
796
797 CURSOR c_get_recipient_details IS
798 SELECT party_id, party_site_id
799 FROM hz_party_sites
800 WHERE party_site_number = p_recipient_site;
801
802 Cursor C_get_organization_code(l_orgn_id number) IS
803 SELECT ORGANIZATION_CODE from mtl_parameters
804 where ORGANIZATION_ID = l_orgn_id;
805
806 Cursor C_get_item_id(l_orgn_id number, l_item_code varchar2) IS
807 SELECT inventory_item_id from mtl_system_items
808 where ORGANIZATION_ID = l_orgn_id
809 and SEGMENT1 = l_item_code;
810
811
812 /* ------------- LOCAL VARIABLES ------------------- */
813
814 l_item_id NUMBER;
815 l_concurrent_id NUMBER(15);
816 l_user_id NUMBER(15);
817 l_recipient_site_id NUMBER(15);
818 l_recipient_id NUMBER(15);
819 l_msg_data VARCHAR2(2000);
820 l_return_status VARCHAR2(3);
821 l_code_block VARCHAR2(200);
822 l_msg_count NUMBER;
823 l_error_msg VARCHAR2(2000);
824
825 /* ------------------ EXCEPTIONS ---------------------- */
826 No_Document_To_Print EXCEPTION;
827 No_Recipient_Exists EXCEPTION;
828
829
830 BEGIN
831
832 /* Bug 4912043 Populating the bind variables with the message text instead of hardcoding the text in the Query */
833 FND_MESSAGE.SET_NAME('GR','GR_BIND_VAR_MSDS');
834 l_bind_var_msds := FND_MESSAGE.GET;
835
836 FND_MESSAGE.SET_NAME('GR','GR_BIND_VAR_SAFETY');
837 l_bind_var_safety := FND_MESSAGE.GET;
838
839 FND_MESSAGE.SET_NAME('GR','GR_BIND_VAR_HAZARD');
840 l_bind_var_hazard := FND_MESSAGE.GET;
841
842 FND_MESSAGE.SET_NAME('GR','GR_BIND_VAR_MSDS_REJECTED');
843 l_bind_var_msds_rejected := FND_MESSAGE.GET;
844
845 l_user_id := NVL(FND_PROFILE.VALUE('USER_ID'),0);
846
847 open C_get_organization_code(p_orgn_id);
848 fetch C_get_organization_code into l_orgn_code;
849 IF C_get_organization_code %NOTFOUND THEN
850 l_orgn_code :=' ';
851 END IF;
852 CLOSE C_get_organization_code;
853
854
855
856
857 /* Select documents */
858 OPEN c_get_doc_info;
859 FETCH c_get_doc_info into DocumentRec;
860 IF c_get_doc_info %NOTFOUND THEN
861 Close c_get_doc_info;
862 RAISE No_Document_To_Print;
863 ELSE
864 OPEN C_get_item_id(p_orgn_id, DocumentRec.doc_attribute1);
865 FETCH C_get_item_id into l_item_id;
866 IF C_get_item_id%NOTFOUND THEN
867 l_item_id := 0;
868 END IF;
869 Close C_get_item_id;
870 /* If updating dispatch history, get recipient info */
871 IF p_update_dispatch_history = 'Y' THEN
872
873 OPEN c_get_recipient_details ;
874 FETCH c_get_recipient_details
875 INTO l_recipient_id,l_recipient_site_id;
876 IF c_get_recipient_details%NOTFOUND THEN
877 RAISE No_Recipient_Exists;
878 CLOSE c_get_recipient_details ;
879 ELSE
880 CLOSE c_get_recipient_details ;
881 END IF; /* If recipient is found */
882 END IF; /* Updating dispatch history */
883 WHILE c_get_doc_info %FOUND LOOP
884 /* Post the File Name printed to the file */
885 FND_MESSAGE.SET_NAME('GR','GR_ITEM_LANG_CATEGORY');
886 FND_MESSAGE.SET_TOKEN('ITEM', DocumentRec.doc_attribute1,FALSE);
887 FND_MESSAGE.SET_TOKEN('LANG', DocumentRec.doc_attribute3,FALSE);
888 FND_MESSAGE.SET_TOKEN('CATEGORY',DocumentRec.doc_attribute_category,FALSE);
889 l_msg_data := FND_MESSAGE.GET;
890 FND_FILE.PUT(FND_FILE.LOG, l_msg_data);
891 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
892 /* Post the File Name printed to the file */
893 FND_MESSAGE.SET_NAME('GR','GR_FILE_NAME_TEXT');
894 FND_MESSAGE.SET_TOKEN('FILE_NAME', DocumentRec.file_name,FALSE);
895 l_msg_data := FND_MESSAGE.GET;
896 FND_FILE.PUT(FND_FILE.LOG, l_msg_data);
897 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
898 l_concurrent_id :=
899 FND_REQUEST.SUBMIT_REQUEST
900 ( 'GR', 'GR_PRINT_SHIP_DOC', '', '', FALSE,
901 DocumentRec.media_id,
902 '', '', '', '', '', '', '', '', '',
903 '', '', '', '', '', '', '', '', '', '',
904 '', '', '', '', '', '', '', '', '', '',
905 '', '', '', '', '', '', '', '', '', '',
906 '', '', '', '', '', '', '', '', '', '',
907 '', '', '', '', '', '', '', '', '', '',
908 '', '', '', '', '', '', '', '', '', '',
909 '', '', '', '', '', '', '', '', '', '',
910 '', '', '', '', '', '', '', '', '', '',
911 '', '', '', '', '', '', '', '', '', '');
912 IF l_concurrent_id = 0 THEN
913 FND_MESSAGE.SET_NAME('GR','GR_CONC_REQ_PRINT_DOC');
914 FND_MESSAGE.SET_TOKEN('FILE_NAME', DocumentRec.file_name,FALSE);
915 l_msg_data := FND_MESSAGE.GET;
916 FND_FILE.PUT(FND_FILE.LOG, l_msg_data);
917 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
918 ELSE
919 IF p_update_dispatch_history = 'Y' THEN
920
921 FND_MESSAGE.SET_NAME('GR', 'GR_CREATING_DISPATCH_HISTORY');
922 FND_MESSAGE.SET_TOKEN('FILE_NAME', DocumentRec.file_name,FALSE);
923 l_msg_data := FND_MESSAGE.GET;
924 FND_FILE.PUT(FND_FILE.LOG, l_msg_data);
925 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
926 GR_DISPATCH_HISTORY_PUB.create_dispatch_history (
927 p_api_version => 1.0,
928 p_init_msg_list => FND_API.G_FALSE,
929 p_commit => FND_API.G_TRUE,
930 p_item => DocumentRec.doc_attribute1,
931 p_organization_id => p_orgn_id,
932 p_inventory_item_id => l_item_id,
933 p_recipient_id => l_recipient_id,
934 p_recipient_site_id => l_recipient_site_id,
935 p_date_sent => SYSDATE,
936 p_dispatch_method_code => 3,
937 p_document_id => DocumentRec.document_id,
938 p_user_id => l_user_id,
939 p_creation_source => 1,
940 p_cas_number => NULL,
941 p_document_location => NULL,
942 p_document_name => NULL,
943 p_document_version => NULL,
944 p_document_category => NULL,
945 p_file_format => NULL,
946 p_file_description => NULL,
947 p_document_code => NULL,
948 p_disclosure_code => NULL,
949 p_language => NULL,
950 p_organization_code => NULL,
951 x_return_status => l_return_status,
952 x_msg_count => l_msg_count,
953 x_msg_data => l_error_msg );
954 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
955 FND_MESSAGE.SET_NAME('GR', 'GR_DISPATCH_HISTORY_FAILED');
956 FND_MESSAGE.SET_TOKEN('FILE_NAME',DocumentRec.File_name, FALSE);
957 l_msg_data := FND_MESSAGE.GET;
958 FND_FILE.PUT(FND_FILE.LOG, l_msg_data);
959 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
960 FND_FILE.PUT(FND_FILE.LOG, l_error_msg);
961 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
962 END IF;
963
964 END IF; /* update dispatch history */
965 END IF; /* g_concurrent_id */
966 FND_FILE.PUT(FND_FILE.LOG, ' ');
967 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
968
969 FETCH c_get_doc_info INTO DocumentRec;
970 END LOOP;
971 CLOSE c_get_doc_info;
972 END IF; /* If documents found */
973
974
975 EXCEPTION
976 WHEN No_Document_To_Print THEN
977 FND_MESSAGE.SET_NAME('GR','GR_NO_DOCUMENT_TO_PRINT');
978 l_msg_data := FND_MESSAGE.GET;
979 FND_FILE.PUT(FND_FILE.LOG, l_msg_data);
980 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
981
982 WHEN No_Recipient_Exists THEN
983 FND_MESSAGE.SET_NAME('GR','GR_INVALID_RECIPIENT_SITE_NUM');
984 FND_MESSAGE.SET_TOKEN('SITE_NUM', P_Recipient_site , FALSE);
985 l_msg_data := FND_MESSAGE.GET;
986 FND_FILE.PUT(FND_FILE.LOG, l_msg_data);
987 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
988
989 WHEN OTHERS THEN
990 l_code_block :='Print Regulatory Doc ' ;
991 FND_MESSAGE.SET_NAME('GR','GR_UNEXPECTED_ERROR');
992 FND_MESSAGE.SET_TOKEN('TEXT',l_code_block||sqlerrm, FALSE);
993 l_msg_data := FND_MESSAGE.GET;
994 FND_FILE.PUT(FND_FILE.LOG, l_msg_data);
995 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
996
997
998 END PRINT_REG_DOCS;
999
1000
1001
1002 END GR_REG_PRINT_DOC;
1003
[Home] [Help]