[Home] [Help]
PACKAGE BODY: APPS.GR_PROCESS_ORDERS
Source
1 PACKAGE BODY GR_PROCESS_ORDERS AS
2 /*$Header: GRPORDRB.pls 120.1 2005/09/22 14:25:48 methomas noship $*/
3 /*
4 **
5 **
6 **
7 */
8 PROCEDURE Build_OPM_Selections
9 (errbuf OUT NOCOPY VARCHAR2,
10 retcode OUT NOCOPY VARCHAR2,
11 p_commit IN VARCHAR2,
12 p_init_msg_list IN VARCHAR2,
13 p_validation_level IN NUMBER,
14 p_api_version IN NUMBER,
15 p_batch_number IN NUMBER,
16 p_process_all_flag IN NUMBER,
17 p_printer IN VARCHAR2,
18 p_user_print_style IN VARCHAR2,
19 p_number_of_copies IN NUMBER,
20 p_return_status OUT NOCOPY VARCHAR2,
21 p_msg_count OUT NOCOPY NUMBER,
22 p_msg_data OUT NOCOPY VARCHAR2)
23 IS
24
25 /* Alpha Variables */
26 L_CODE_BLOCK VARCHAR2(2000);
27 L_RETURN_STATUS VARCHAR2(1);
28 L_MSG_DATA VARCHAR2(2000);
29
30 L_API_NAME CONSTANT VARCHAR2(30) := 'Build OPM Selections';
31
32 L_CURRENT_DATE DATE := SYSDATE;
33
34 L_OM_INTEGRATION VARCHAR2(1);
35
36 x_return_status VARCHAR2(100);
37 x_msg_count NUMBER;
38 X_msg_data VARCHAR2(2000);
39 pg_fp utl_file.file_type;
40
41 /* Numeric Variables */
42 L_ORACLE_ERROR NUMBER;
43 L_API_VERSION CONSTANT NUMBER := 1.0;
44
45 /* Exceptions */
46 INCOMPATIBLE_API_VERSION_ERROR EXCEPTION;
47 BATCH_NUMBER_NULL_ERROR EXCEPTION;
48 INVALID_BATCH_NUMBER_ERROR EXCEPTION;
49 INVALID_BATCH_STATUS_ERROR EXCEPTION;
50 SELECTION_INSERT_ERROR EXCEPTION;
51 PROCESS_SELECTIONS_ERROR EXCEPTION;
52
53 /*
54 ** Define the cursors
55 **
56 ** Shipment information by shipment number
57 */
58 /* GK Changes B2286375*/
59 CURSOR c_get_shipment_detail
60 IS
61 SELECT om.order_id,
62 om.line_no,
63 om.bol_id,
64 om.item_id,
65 om.shipcust_id,
66 om.holdreas_code,
67 mtl.segment1 item_no,
68 om.shipping_ind,
69 om.picking_ind,
70 om.order_no,
71 om.bol_no,
72 cust.cust_no
73 FROM op_cust_mst cust,
74 gr_order_info_v om,
75 mtl_system_items mtl
76 WHERE om.bol_id >= GlobalBatchHeader.shipment_from
77 AND om.bol_id <= GlobalBatchHeader.shipment_to
78 AND om.from_whse = TO_CHAR(g_default_orgid)
79 AND om.shipaddr_id = cust.of_ship_to_site_use_id
80 AND mtl.inventory_item_id = om.item_id
81 AND om.shipping_ind = 0
82 AND om.delete_mark = 0
83 UNION
84 SELECT om.order_id,
85 om.line_no,
86 om.bol_id,
87 om.item_id,
88 om.shipcust_id,
89 om.holdreas_code,
90 ic.item_no,
91 om.shipping_ind,
92 om.picking_ind,
93 om.order_no,
94 om.bol_no,
95 cust.cust_no
96 FROM op_cust_mst cust,
97 gr_order_info_v om,
98 ic_item_mst ic
99 WHERE om.bol_id >= GlobalBatchHeader.shipment_from
100 AND om.bol_id <= GlobalBatchHeader.shipment_to
101 AND om.from_whse = g_default_whse
102 AND om.shipcust_id = cust.cust_id
103 AND ic.item_id = om.item_id
104 AND om.shipping_ind = 0
105 AND om.delete_mark = 0;
106 LocalShipmentDetail c_get_shipment_detail%ROWTYPE;
107 /*
108 ** Shipment information by date
109 */
110 /* GK Changes B2286375*/
111 CURSOR c_get_shipment_date
112 IS
113 SELECT om.order_id,
114 om.line_no,
115 om.bol_id,
116 om.item_id,
117 om.shipcust_id,
118 om.holdreas_code,
119 mtl.segment1 item_no,
120 om.shipping_ind,
121 om.picking_ind,
122 om.order_no,
123 om.bol_no,
124 cust.cust_no
125 FROM op_cust_mst cust,
126 gr_order_info_v om,
127 mtl_system_items mtl
128 WHERE om.actual_shipdate >= GlobalBatchHeader.shipment_date_from
129 AND om.actual_shipdate <= GlobalBatchHeader.shipment_date_to
130 AND om.from_whse = TO_CHAR(g_default_orgid)
131 AND om.shipaddr_id = cust.of_ship_to_site_use_id
132 AND mtl.inventory_item_id = om.item_id
133 AND om.shipping_ind = 0
134 AND om.delete_mark = 0
135 UNION
136 SELECT om.order_id,
137 om.line_no,
138 om.bol_id,
139 om.item_id,
140 om.shipcust_id,
141 om.holdreas_code,
142 ic.item_no,
143 om.shipping_ind,
144 om.picking_ind,
145 om.order_no,
146 om.bol_no,
147 cust.cust_no
148 FROM op_cust_mst cust,
149 gr_order_info_v om,
150 ic_item_mst ic
151 WHERE om.actual_shipdate >= GlobalBatchHeader.shipment_date_from
152 AND om.actual_shipdate <= GlobalBatchHeader.shipment_date_to
153 AND om.from_whse = g_default_whse
154 AND om.shipcust_id = cust.cust_id
155 AND ic.item_id = om.item_id
156 AND om.shipping_ind = 0
157 AND om.delete_mark = 0;
158 LocalShipmentDate c_get_shipment_date%ROWTYPE;
159 /*
160 ** Order header information
161 */
162 /* GK Changes B2286375*/
163 CURSOR c_get_order_detail
164 IS
165 SELECT om.order_id,
166 om.line_no,
167 om.line_id,
168 om.bol_id,
169 om.item_id,
170 om.shipcust_id,
171 om.holdreas_code,
172 om.hold_code,
173 mtl.segment1 item_no,
174 om.shipping_ind,
175 om.picking_ind,
176 om.order_no,
177 om.bol_no,
178 cust.cust_no
179 FROM op_cust_mst cust,
180 gr_order_info_v om,
181 mtl_system_items mtl
182 WHERE om.order_id >= GlobalBatchHeader.order_from
183 AND om.order_id <= GlobalBatchHeader.order_to
184 AND om.from_whse = TO_CHAR(g_default_orgid)
185 AND om.shipaddr_id = cust.of_ship_to_site_use_id
186 AND mtl.inventory_item_id = om.item_id
187 UNION
188 SELECT DISTINCT om.order_id,
189 om.line_no,
190 om.line_id,
191 om.bol_id,
192 om.item_id,
193 om.shipcust_id,
194 om.holdreas_code,
195 om.hold_code,
196 ic.item_no,
197 om.shipping_ind,
198 om.picking_ind,
199 om.order_no,
200 om.bol_no,
201 cust.cust_no
202 FROM op_cust_mst cust,
203 gr_order_info_v om,
204 ic_item_mst ic
205 WHERE om.order_id >= GlobalBatchHeader.order_from
206 AND om.order_id <= GlobalBatchHeader.order_to
207 AND om.from_whse = g_default_whse
208 AND om.shipcust_id = cust.cust_id
209 AND ic.item_id = om.item_id;
210 LocalOrderDetail c_get_order_detail%ROWTYPE;
211
212 /* GK Changes B2286375: Get organization id */
213 CURSOR c_get_org_id
214 IS
215 SELECT organization_id
216 FROM mtl_parameters
217 WHERE organization_code = g_default_whse;
218 LocalOrgId c_get_org_id%ROWTYPE;
219
220 CURSOR c_get_hold
221 IS
222 SELECT ooh.order_hold_id
223 FROM oe_order_holds_all ooh
224 WHERE (LocalOrderDetail.order_id = ooh.header_id
225 OR LocalOrderDetail.line_id = ooh.line_id)
226 AND ooh.hold_release_id IS NULL;
227 LocalHoldRecord c_get_hold%ROWTYPE;
228
229 BEGIN
230 SAVEPOINT Build_OPM_Selections;
231 /*
232 ** Initialize the message list if true
233 */
234 IF FND_API.To_Boolean(p_init_msg_list) THEN
235 FND_MSG_PUB.Initialize;
236 END IF;
237 g_report_type := 2;
238 /* Check the API version passed in matches the
239 ** internal API version.
240 */
241 IF NOT FND_API.Compatible_API_Call
242 (l_api_version,
243 p_api_version,
244 l_api_name,
245 g_pkg_name) THEN
246 RAISE Incompatible_API_Version_Error;
247 END IF;
248 /*
249 ** Set return status to successful
250 */
251 x_return_status := FND_API.G_RET_STS_SUCCESS;
252
253 /*
254 ** B2286375 Check if using OM or OF
255 */
256 l_om_integration := FND_PROFILE.VALUE('GML_OM_INTEGRATION');
257
258 FND_FILE.PUT(FND_FILE.LOG, 'OM Integration' || l_om_integration);
259 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
260
261 /*
262 ** Check the passed in batch number is not null
263 ** and exists on the batch selection header and the
264 ** status is set to '1' indicating entered.
265 */
266 l_code_block := 'Validate the batch number';
267 g_batch_number := p_batch_number;
268
269
270 IF g_batch_number IS NULL THEN
271 RAISE Batch_Number_Null_Error;
272 ELSE
273 OPEN g_get_batch_status;
274 FETCH g_get_batch_status INTO GlobalBatchHeader;
275 IF g_get_batch_status%NOTFOUND THEN
276 CLOSE g_get_batch_status;
277 RAISE Invalid_Batch_Number_Error;
278 ELSIF GlobalBatchHeader.status <> 1 THEN
279 CLOSE g_get_batch_status;
280 RAISE Invalid_Batch_Status_Error;
281 END IF;
282 CLOSE g_get_batch_status;
283 END IF;
284 /*
285 ** Set the territory, organizaton and warehouse defaults
286 */
287 g_default_country := GlobalBatchHeader.territory_code;--utl_file.put_line(pg_fp, 'terr '||GlobalBatchHeader.territory_code);
288 g_default_orgn := GlobalBatchHeader.orgn_code;
289 g_default_whse := GlobalBatchHeader.whse_code;
290
291 /* Bug #2286375 GK Changes*/
292 OPEN c_get_org_id;
293 FETCH c_get_org_id INTO LocalOrgId;
294 g_default_orgid := LocalOrgId.organization_id;
295 CLOSE c_get_org_id;
296 /* End Changes*/
297 /*
298 ** Get the default country profile
299 */
300 OPEN g_get_country_profile;
301 FETCH g_get_country_profile INTO GlobalCountryRecord;
302 IF g_get_country_profile%NOTFOUND THEN
303 g_default_document := NULL;
304 ELSE
305 g_default_document := GlobalCountryRecord.document_code;--utl_file.put_line(pg_fp, 'doc '||GlobalCountryRecord.document_code);
306 END IF;
307 CLOSE g_get_country_profile;
308 /*
309 ** Clear any existing rows from the detail table
310 */
311 DELETE
312 FROM gr_selection sd
313 WHERE sd.batch_no = p_batch_number;
314 /*
315 ** Determine whether to process orders or shipments
316 */
317 IF GlobalBatchHeader.order_from IS NULL AND
318 GlobalBatchHeader.order_to IS NULL THEN
319 l_code_block := 'Process shipments';
320 IF GlobalBatchHeader.shipment_from IS NULL AND
321 GlobalBatchHeader.shipment_to IS NULL THEN
322 l_code_block := 'Process by shipment date';
323 FND_FILE.PUT(FND_FILE.LOG, l_code_block);
324 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
325
326 OPEN c_get_shipment_date;
327 FETCH c_get_shipment_date INTO LocalShipmentDate;
328 IF c_get_shipment_date%FOUND THEN
329 WHILE c_get_shipment_date%FOUND LOOP
330 g_item_code := LocalShipmentDate.item_no;
331 IF LocalShipmentDate.shipping_ind <> 0 THEN
332 FND_FILE.PUT(FND_FILE.LOG, ' Shipment : '||LocalShipmentDate.bol_no||' Order : '||LocalShipmentDate.order_no||' - '||TO_CHAR(LocalShipmentDate.line_no));
333 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
334
335 l_code_block := 'Hold reason does not allow order to be shipped';
336
337 FND_FILE.PUT(FND_FILE.LOG, l_code_block);
338 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
339
340 l_return_status := FND_API.G_RET_STS_SUCCESS;
341 Insert_Selection_Row
342 ('GR_ON_HOLD_NO_SHIP',
343 'CODE',
344 LocalShipmentDate.holdreas_code,
345 LocalShipmentDate.order_id,
346 LocalShipmentDate.line_no,
347 g_default_document,
348 'N',
349 LocalShipmentDate.cust_no,
350 LocalShipmentDate.bol_no,
351 l_return_status);
352
353 IF l_return_status <> 'S' THEN
354 RAISE Selection_Insert_Error;
355 END IF;
356 ELSE
357 g_order_no := LocalShipmentDate.order_no;
358 g_order_number := LocalShipmentDate.order_id;
359 g_order_line := LocalShipmentDate.line_no;
360 g_recipient_code := LocalShipmentDate.cust_no;
361 g_item_code := LocalShipmentDate.item_no;
362 g_shipment_number := LocalShipmentDate.bol_no;
363 l_return_status := FND_API.G_RET_STS_SUCCESS;
364
365 Check_Selected_Line
366 (l_return_status,
367 x_msg_count,
368 x_msg_data);
369
370 IF l_return_status <> 'S' THEN
371 RAISE Process_Selections_Error;
372 END IF;
373 END IF;
374 FETCH c_get_shipment_date INTO LocalShipmentDate;
375 END LOOP;
376 END IF;
377 CLOSE c_get_shipment_date;
378 ELSE
379 l_code_block := 'Process by shipment number';
380
381 FND_FILE.PUT(FND_FILE.LOG, l_code_block);
382 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
383
384 IF c_get_shipment_detail%ISOPEN THEN
385 CLOSE c_get_shipment_detail;
386 END IF;
387
388 OPEN c_get_shipment_detail;
389 FETCH c_get_shipment_detail INTO LocalShipmentDetail;
390 IF c_get_shipment_detail%FOUND THEN
391 WHILE c_get_shipment_detail%FOUND LOOP
392 FND_FILE.PUT(FND_FILE.LOG, ' Shipment : '||LocalShipmentDetail.bol_no||' Order : '||LocalShipmentDetail.order_no||' - '||TO_CHAR(LocalShipmentDetail.line_no));
393 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
394 g_item_code := LocalShipmentDetail.item_no;
395 IF LocalShipmentDetail.shipping_ind <> 0 THEN
396 l_code_block := 'Hold reason does not allow order to be shipped';
397
398 FND_FILE.PUT(FND_FILE.LOG, l_code_block);
399 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
400
401 l_return_status := FND_API.G_RET_STS_SUCCESS;
402 Insert_Selection_Row
403 ('GR_ON_HOLD_NO_SHIP',
404 'CODE',
405 LocalShipmentDetail.holdreas_code,
406 LocalShipmentDetail.order_id,
407 LocalShipmentDetail.line_no,
408 g_default_document,
409 'N',
410 LocalShipmentDetail.cust_no,
411 LocalShipmentDetail.bol_no,
412 l_return_status);
413 IF l_return_status <> 'S' THEN
414 RAISE Selection_Insert_Error;
415 END IF;
416 ELSE
417 g_order_no := LocalShipmentDetail.order_no;
418 g_order_number := LocalShipmentDetail.order_id;
419 g_order_line := LocalShipmentDetail.line_no;
420 g_recipient_code := LocalShipmentDetail.cust_no;
421 g_item_code := LocalShipmentDetail.item_no;
422 g_shipment_number := LocalShipmentDetail.bol_no;
423 l_return_status := FND_API.G_RET_STS_SUCCESS;
424
425 Check_Selected_Line
426 (l_return_status,
427 x_msg_count,
428 x_msg_data);
429
430 IF l_return_status <> 'S' THEN
431 RAISE Process_Selections_Error;
432 END IF;
433 END IF;
434 FETCH c_get_shipment_detail INTO LocalShipmentDetail;
435 END LOOP;
436 END IF;
437 CLOSE c_get_shipment_detail;
438 END IF;
439 ELSE
440 l_code_block := 'Process orders';
441 FND_FILE.PUT(FND_FILE.LOG, l_code_block);
442 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
443
444 OPEN c_get_order_detail;
445 FETCH c_get_order_detail INTO LocalOrderDetail;
446 IF c_get_order_detail%FOUND THEN
447
448 WHILE c_get_order_detail%FOUND LOOP
449 FND_FILE.PUT(FND_FILE.LOG, ' Order : '||LocalOrderDetail.order_no||' - '||TO_CHAR(LocalOrderDetail.line_no));
450 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
451
452 g_item_code := LocalOrderDetail.item_no;
453 IF l_om_integration = 'N' THEN /*Added for OM Integration B2286375*/
454 --utl_file.put_line(pg_fp, 'integrn ' ||l_om_integration);
455 IF LocalOrderDetail.holdreas_code <> 'NONE' OR LocalOrderDetail.hold_code <> 'NONE' THEN
456 -- utl_file.put_line(pg_fp, 'hold found' ||Localofhold.holdreas_code);
457 IF LocalOrderDetail.picking_ind <> 0 AND
458 LocalOrderDetail.picking_ind IS NOT NULL THEN
459 l_code_block := 'Hold reason does not allow order to be picked';
460
461 FND_FILE.PUT(FND_FILE.LOG, l_code_block);
462 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
463
464 l_return_status := FND_API.G_RET_STS_SUCCESS;
465 Insert_Selection_Row
466 ('GR_ON_HOLD_NO_PICK',
467 'CODE',
468 LocalOrderDetail.holdreas_code,
469 LocalOrderDetail.order_id,
470 LocalOrderDetail.line_no,
471 '',
472 'N',
473 LocalOrderDetail.cust_no,
474 '',
475 l_return_status);
476 IF l_return_status <> 'S' THEN
477 RAISE Selection_Insert_Error;
478 END IF;
479 /* Fix for B1449278 */
480 /* Added code to check for the hold for shipping_ind on the order. */
481 ELSIF LocalOrderDetail.shipping_ind <> 0 AND
482 LocalOrderDetail.shipping_ind IS NOT NULL THEN
483 l_code_block := 'Hold reason does not allow order to be shipped';
484 --utl_file.put_line(pg_fp, 'code '||l_code_block);
485 FND_FILE.PUT(FND_FILE.LOG, l_code_block);
486 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
487
488 l_return_status := FND_API.G_RET_STS_SUCCESS;
489 Insert_Selection_Row
490 ('GR_ON_HOLD_NO_SHIP',
491 'CODE',
492 LocalOrderDetail.holdreas_code,
493 LocalOrderDetail.order_id,
494 LocalOrderDetail.line_no,
495 '',
496 'N',
497 LocalOrderDetail.cust_no,
498 '',
499 l_return_status);
500 IF l_return_status <> 'S' THEN
501 RAISE Selection_Insert_Error;
502 END IF;
503
504 END IF;
505
506 ELSE
507 IF LocalOrderDetail.bol_id <> 0 AND
508 LocalOrderDetail.bol_id IS NOT NULL THEN
509 l_code_block := ' Line already shipped';
510
511 FND_FILE.PUT(FND_FILE.LOG, l_code_block);
512 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
513
514 l_return_status := FND_API.G_RET_STS_SUCCESS;
515 Insert_Selection_Row
516 ('GR_ORDER_ALREADY_SHIPPED',
517 '',
518 '',
519 LocalOrderDetail.order_id,
520 LocalOrderDetail.line_no,
521 '',
522 'N',
523 LocalOrderDetail.cust_no,
524 '',
525 l_return_status);
526 IF l_return_status <> 'S' THEN
527 RAISE Selection_Insert_Error;
528 END IF;
529 ELSE
530 -- utl_file.put_line(pg_fp, 'else bol_id');
531 g_order_no := LocalOrderDetail.order_no;
532 g_order_number := LocalOrderDetail.order_id;
533 g_order_line := LocalOrderDetail.line_no;
534 g_recipient_code := LocalOrderDetail.cust_no;
535 g_item_code := LocalOrderDetail.item_no;
536 g_shipment_number := NULL;
537
538 l_return_status := FND_API.G_RET_STS_SUCCESS;
539
540 Check_Selected_Line
541 (l_return_status,
542 x_msg_count,
543 x_msg_data);
544
545 IF l_return_status <> 'S' THEN
546 RAISE Process_Selections_Error;
547 END IF;
548
549 END IF; /* LocalOrderDetail.bol_id <> 0 */
550 END IF; /*holdreas_code*/
551 ELSIF l_om_integration = 'Y' THEN /*Added for OM Integration B2286375*/
552 --utl_file.put_line(pg_fp, 'integrnOM ' ||l_om_integration);
553 IF LocalOrderDetail.holdreas_code = 0 THEN
554 OPEN c_get_hold;
555 FETCH c_get_hold INTO LocalHoldRecord;
556 IF c_get_hold%FOUND THEN
557 l_code_block := 'Hold reason does not allow order to be picked';
558 --utl_file.put_line(pg_fp,'code blk ' ||l_code_block);
559 FND_FILE.PUT(FND_FILE.LOG, l_code_block);
560 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
561
562 l_return_status := FND_API.G_RET_STS_SUCCESS;
563 Insert_Selection_Row
564 ('GR_ON_HOLD_NO_PICK',
565 'CODE',
566 LocalHoldRecord.order_hold_id,
567 LocalOrderDetail.order_id,
568 LocalOrderDetail.line_no,
569 '',
570 'N',
571 LocalOrderDetail.cust_no,
572 '',
573 l_return_status);
574 IF l_return_status <> 'S' THEN
575 RAISE Selection_Insert_Error;
576 END IF;
577 ELSE
578 g_order_no := LocalOrderDetail.order_no;
579 g_order_number := LocalOrderDetail.order_id;
580 g_order_line := LocalOrderDetail.line_no;
581 g_recipient_code := LocalOrderDetail.cust_no;
582 g_item_code := LocalOrderDetail.item_no;
583 g_shipment_number := NULL;
584 --utl_file.put_line(pg_fp,'else ' ||g_order_number);
585 l_return_status := FND_API.G_RET_STS_SUCCESS;
586
587 Check_Selected_Line
588 (l_return_status,
589 x_msg_count,
590 x_msg_data);
591
592 IF l_return_status <> 'S' THEN
593 RAISE Process_Selections_Error;
594 END IF;
595
596 END IF;/*c_get_hold%found*/
597
598 CLOSE c_get_hold;
599
600 END IF;/* holdreas = 0*/
601 END IF;/* integration*/
602 FETCH c_get_order_detail INTO LocalOrderDetail;
603 END LOOP;
604 END IF; /* c_get_order_detail found*/
605 CLOSE c_get_order_detail;
606 END IF; /* order_from is NULL */
607 /*
608 ** Update the header status to Selected
609 */
610 UPDATE gr_selection_header
611 SET status = 2
612 WHERE batch_no = p_batch_number;
613
614 IF FND_API.To_Boolean(p_commit) THEN
615 COMMIT WORK;
616 END IF;
617 /*
618 ** Process all flag set to 1 means automatically chain
619 ** to process the selected lines.
620 */
621 IF p_process_all_flag = 1 THEN
622 Process_Selections
623 (errbuf,
624 retcode,
625 p_commit,
626 'F',
627 p_init_msg_list,
628 p_validation_level,
629 1.0,
630 p_batch_number,
631 p_process_all_flag,
632 p_printer,
633 p_user_print_style,
634 p_number_of_copies,
635 l_return_status,
636 x_msg_count,
637 x_msg_data);
638 IF l_return_status <> 'S' THEN
639 RAISE Process_Selections_Error;
640 END IF;
641 END IF;
642
643 EXCEPTION
644
645 WHEN Incompatible_API_Version_Error THEN
646 ROLLBACK TO SAVEPOINT Build_OPM_Selections;
647
648 Handle_Error_Messages
649 ('GR_API_VERSION_ERROR',
650 'VERSION',
651 p_api_version,
652 x_msg_count,
653 x_msg_data,
654 x_return_status);
655
656 WHEN Batch_Number_Null_Error THEN
657 ROLLBACK TO SAVEPOINT Build_OPM_Selections;
658
659 Handle_Error_Messages
660 ('GR_NULL_BATCH_NUMBER',
661 '',
662 '',
663 x_msg_count,
664 x_msg_data,
665 x_return_status);
666
667 WHEN Invalid_Batch_Number_Error THEN
668 ROLLBACK TO SAVEPOINT Build_OPM_Selections;
669
670 Handle_Error_Messages
671 ('GR_INVALID_BATCH_NUMBER',
672 'BATCH',
673 p_batch_number,
674 x_msg_count,
675 x_msg_data,
676 x_return_status);
677
678 WHEN Invalid_Batch_Status_Error THEN
679 ROLLBACK TO SAVEPOINT Build_OPM_Selections;
680
681 Handle_Error_Messages
682 ('GR_INVALID_BATCH_STATUS',
683 'STATUS',
684 GlobalBatchHeader.status,
685 x_msg_count,
686 x_msg_data,
687 x_return_status);
688
689 WHEN Selection_Insert_Error THEN
690
691 ROLLBACK TO SAVEPOINT Build_OPM_Selections;
692
693 Handle_Error_Messages
694 ('GR_NO_RECORD_INSERTED',
695 'CODE',
696 g_order_number || ' ' || g_order_line,
697 x_msg_count,
698 x_msg_data,
699 x_return_status);
700
701 WHEN Process_Selections_Error THEN
702
703 ROLLBACK TO SAVEPOINT Build_OPM_Selections;
704
705 Handle_Error_Messages
706 ('GR_UNEXPECTED_ERROR',
707 'TEXT',
708 l_msg_data,
709 x_msg_count,
710 x_msg_data,
711 x_return_status);
712
713 WHEN OTHERS THEN
714
715 ROLLBACK TO SAVEPOINT Build_OPM_Selections;
716
717 l_oracle_error := SQLCODE;
718 /*l_code_block := SUBSTR(SQLERRM, 1, 200);*/
719 l_code_block := l_code_block || ' ' || TO_CHAR(l_oracle_error);
720 FND_MESSAGE.SET_NAME('GR',
721 'GR_UNEXPECTED_ERROR');
722 FND_MESSAGE.SET_TOKEN('TEXT',
723 l_code_block||sqlerrm,
724 FALSE);
725 --utl_file.fflush(pg_fp);
726 --utl_file.fclose(pg_fp);
727 END Build_OPM_Selections;
728 /*
729 ** This procedure takes the selections for the batch that are
730 ** stored in the gr_selection table and generates the cover
731 ** letters and documents for the items in the selection.
732 */
733 PROCEDURE Process_Selections
734 (errbuf OUT NOCOPY VARCHAR2,
735 retcode OUT NOCOPY VARCHAR2,
736 p_commit IN VARCHAR2,
737 p_called_by_form IN VARCHAR2,
738 p_init_msg_list IN VARCHAR2,
739 p_validation_level IN NUMBER,
740 p_api_version IN NUMBER,
741 p_batch_number IN NUMBER,
742 p_process_all_flag IN NUMBER,
743 p_printer IN VARCHAR2,
744 p_user_print_style IN VARCHAR2,
745 p_number_of_copies IN NUMBER,
746 x_return_status OUT NOCOPY VARCHAR2,
747 x_msg_count OUT NOCOPY NUMBER,
748 x_msg_data OUT NOCOPY VARCHAR2)
749 IS
750
751 /* Alpha Variables */
752 L_CODE_BLOCK VARCHAR2(2000);
753 L_RETURN_STATUS VARCHAR2(1);
754 L_MSG_DATA VARCHAR2(2000);
755 L_NEW_RECIPIENT VARCHAR2(2);
756 L_PRINT_COUNT NUMBER(5) DEFAULT 0;
757 L_LANGUAGE_CODE FND_LANGUAGES.language_code%TYPE;
758 L_API_NAME CONSTANT VARCHAR2(30) := 'Process Selections';
759 pg_fp utl_file.file_type;
760 /* Numeric Variables */
761 L_HEADER_STATUS GR_SELECTION_HEADER.status%TYPE;
762 L_ORACLE_ERROR NUMBER;
763 L_USER_ID NUMBER;
764
765 L_API_VERSION CONSTANT NUMBER := 1.0;
766
767 /* Exceptions */
768 INCOMPATIBLE_API_VERSION_ERROR EXCEPTION;
769 INVALID_BATCH_NUMBER_ERROR EXCEPTION;
770 INVALID_BATCH_STATUS_ERROR EXCEPTION;
771 BATCH_NUMBER_NULL_ERROR EXCEPTION;
772 UPDATE_HISTORY_ERROR EXCEPTION;
773 OTHER_API_ERROR EXCEPTION;
774
775 /* Define the local cursors
776 **
777 ** Get the line details. Only select line details
778 ** marked for print that have been selected and have
779 ** not been updated.
780 */
781 CURSOR c_get_line_details
782 IS
783 SELECT sd.ROWID,
784 sd.document_code,
785 sd.item_code,
786 sd.recipient_code,
787 sd.line_status,
788 sd.order_no,
789 sd.order_line_number,
790 /* 22-Aug-2003 Mercy Thomas BUG 2932007 - Added the column shipment_no to the cursor */
791 sd.shipment_no
792 /* 22-Aug-2003 Mercy Thomas BUG 2932007 - End of the code change */
793 FROM gr_selection sd
794 WHERE sd.batch_no = g_batch_number
795 AND sd.print_flag = 'Y'
796 AND sd.line_status <> 0
797 AND sd.line_status <> 8;
798 LocalDetailRecord c_get_line_details%ROWTYPE;
799
800 /*
801 ** Get the line message if the row cannot be printed
802 */
803 CURSOR c_get_line_message
804 IS
805 SELECT SUBSTR(message, 1, 100) message, order_no, order_line_number
806 FROM gr_selection sd
807 WHERE sd.batch_no = g_batch_number;
808 LocalLineRecord c_get_line_message%ROWTYPE;
809
810 CURSOR c_get_order_dtl
811 IS
812 SELECT distinct order_no, line_no
813 FROM gr_order_info_v
814 WHERE order_id = g_order_number;
815 LocalOrdDtl c_get_order_dtl%ROWTYPE;
816
817 BEGIN
818 /*
819 ** Initialize the message list if true
820 */
821 IF FND_API.To_Boolean(p_init_msg_list) THEN
822 FND_MSG_PUB.Initialize;
823 END IF;
824 /*pg_fp := utl_file.fopen('/sqlcom/log/opm115m','order1.log','w');
825 utl_file.put_line(pg_fp, 'this is a test statement');*/
826 /*
827 ** Check the API version passed in matches the
828 ** internal API version.
829 */
830 IF NOT FND_API.Compatible_API_Call
831 (l_api_version,
832 p_api_version,
833 l_api_name,
834 g_pkg_name) THEN
835 RAISE Incompatible_API_Version_Error;
836 END IF;
837
838 /* 17-Jun-2003 Mercy Thomas BUG 2932007 - Added report type for Document Management */
839
840 FND_FILE.PUT(FND_FILE.LOG, ' Report Type ' || g_report_type);
841 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
842 IF g_report_type = 0 THEN
843 g_report_type := 4;
844 FND_FILE.PUT(FND_FILE.LOG, ' Report Type ' || g_report_type);
845 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
846 END IF;
847 /* 17-Jun-2003 Mercy Thomas BUG 2932007 - End of code changes */
848
849 /*
850 ** Set return status to successful and get
851 ** the required user profiles.
852 */
853 IF p_called_by_form = 'F' THEN
854 x_return_status := FND_API.G_RET_STS_SUCCESS;
855 END IF;
856 g_recipient_code := NULL;
857 /*
858 ** Check the passed in batch number is not null
859 ** and exists on the batch selection header and the
860 ** status is set to 2, 4, 5 or 6 indicating selected, a
861 ** a restart, a rerun or printed.
862 */
863 l_code_block := 'Validate the batch number';
864 g_batch_number := p_batch_number;
865
866 IF g_batch_number IS NULL THEN
867 RAISE Batch_Number_Null_Error;
868 ELSE
869 OPEN g_get_batch_status;
870 FETCH g_get_batch_status INTO GlobalBatchHeader;
871 IF g_get_batch_status%NOTFOUND THEN
872 CLOSE g_get_batch_status;
873 RAISE Invalid_Batch_Number_Error;
874 ELSIF GlobalBatchHeader.status <> 2 AND
875 GlobalBatchHeader.status <> 4 AND
876 GlobalBatchHeader.status <> 5 AND
877 GlobalBatchHeader.status <> 6 THEN
878 CLOSE g_get_batch_status;
879 RAISE Invalid_Batch_Status_Error;
880 END IF;
881 CLOSE g_get_batch_status;
882 END IF;
883 /*
884 ** Set the territory, organizaton and warehouse defaults
885 */
886 g_default_orgn := GlobalBatchHeader.orgn_code;
887 g_default_whse := GlobalBatchHeader.whse_code;
888 g_default_country := GlobalBatchHeader.territory_code;
889
890 /*
891 ** Get the default country profile
892 */
893 OPEN g_get_country_profile;
894 FETCH g_get_country_profile INTO GlobalCountryRecord;
895 IF g_get_country_profile%NOTFOUND THEN
896 g_default_document := NULL;
897 ELSE
898 g_default_document := GlobalCountryRecord.document_code;
899 END IF;
900 CLOSE g_get_country_profile;
901
902 /*
903 ** Get the session id and clear print work table
904 */
905 SELECT gr_work_build_docs_s.nextval INTO g_session_id
906 FROM dual;
907
908 l_return_status := 'S';
909 GR_PROCESS_DOCUMENTS.Clear_Worksheet_Session
910 ('F',
911 g_session_id,
912 l_return_status,
913 l_oracle_error,
914 l_msg_data);
915
916 IF l_return_status <> 'S' THEN
917 RAISE Other_API_Error;
918 END IF;
919 /*
920 ** Store the batch header status and set the
921 ** header status to in process - 3
922 */
923 l_header_status := GlobalBatchHeader.status;
924 /*utl_file.put_line(pg_fp, 'GlobalBatchHeader.status' ||GlobalBatchHeader.status);*/
925
926 UPDATE gr_selection_header
927 SET status = 3
928 WHERE batch_no = p_batch_number;
929 /*
930 ** Now process the detail lines
931 */
932 OPEN c_get_line_details;
933 FETCH c_get_line_details INTO LocalDetailRecord;
934 IF c_get_line_details%FOUND THEN
935 WHILE c_get_line_details%FOUND LOOP
936
937 /*
938 ** 17-Jun-2003 Mercy Thomas BUG 2932007 - If the Process Selections, is done separately, to populate the Global vaiables for Document
939 ** Management added the following code
940 **
941 */
942
943 IF g_report_type = 4 THEN
944 g_item_code := LocalDetailRecord.item_code;
945 g_order_number := LocalDetailRecord.order_no;
946 /* 22-Aug-2003 Mercy Thomas BUG 2932007 - Added the code to populate the global variable g_shipment_number */
947 g_shipment_number := LocalDetailRecord.shipment_no;
948 /* 22-Aug-2003 Mercy Thomas BUG 2932007 - End of the code changes*/
949 OPEN c_get_order_dtl;
950 FETCH c_get_order_dtl INTO LocalOrdDtl;
951 IF c_get_order_dtl%FOUND THEN
952 g_order_no := LocalOrdDtl.order_no;
953 /* 22-Aug-2003 Mercy Thomas BUG 2932007 - Modified the global variable from g_line_number to g_order_line */
954 g_order_line := LocalOrdDtl.line_no;
955 /* 22-Aug-2003 Mercy Thomas BUG 2932007 - End of the code changes */
956 END IF;
957 CLOSE c_get_order_dtl;
958 END IF;
959
960 /* 17-Jun-2003 Mercy Thomas BUG 2932007 - End of code changes */
961
962 IF l_header_status = 4 AND
963 (LocalDetailRecord.line_status <> 2 OR
964 LocalDetailRecord.line_status <> 3) THEN
965 l_code_block := 'Do not print this line';
966 FND_FILE.PUT(FND_FILE.LOG, l_code_block||' - '||LocalDetailRecord.order_no||' - '||TO_CHAR(LocalDetailRecord.order_line_number));
967 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
968 ELSE
969 SAVEPOINT Process_Document_Selection;
970 /*
971 ** Set the line status to printing in process
972 */
973 UPDATE gr_selection
974 SET line_status = 3
975 WHERE ROWID = LocalDetailRecord.ROWID;
976 /*
977 ** If a change of recipient code, get the new details or
978 ** build a new record if note there.
979 */
980 IF g_recipient_code IS NULL OR
981 g_recipient_code <> LocalDetailRecord.recipient_code THEN
982 g_recipient_code := LocalDetailRecord.recipient_code;
983 /*utl_file.put_line(pg_fp, 'LocalDetailRecord.recipient_code'||LocalDetailRecord.recipient_code);*/
984 OPEN g_get_recipient;
985 FETCH g_get_recipient INTO GlobalRecipient;
986 IF g_get_recipient%NOTFOUND THEN
987 l_code_block := 'Write new recipient record';
988 END IF;
989 CLOSE g_get_recipient;
990 l_language_code := GlobalRecipient.language;
991 l_new_recipient := 'YS';
992 ELSE
993 l_new_recipient := 'NO';
994 END IF;
995 /*
996 ** Print documents for invoice address
997 */
998 IF GlobalRecipient.invoice_address = 1 THEN
999 /*utl_file.put_line(pg_fp, 'GlobalRecipient.invoice_address'||GlobalRecipient.invoice_address);*/
1000 IF GlobalRecipient.region_code IS NULL THEN
1001 IF l_new_recipient = 'YS' THEN
1002 l_return_status := 'S';
1003 Read_And_Print_Cover_Letter
1004 (l_language_code,
1005 LocalDetailRecord.item_code,
1006 GlobalRecipient.recipient_code,
1007 'I',
1008 LocalDetailRecord.order_no,
1009 '',
1010 l_return_status);
1011 IF l_return_status <> 'S' THEN
1012 l_code_block := 'Error';
1013 FND_FILE.PUT(FND_FILE.LOG,'Cover Letter error - 1');
1014 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
1015 RAISE Other_API_Error;
1016 END IF;
1017 /*
1018 ** Print the letter and increment the session id
1019 */
1020 l_return_status := 'S';
1021 g_cover_letter := 'Y';
1022 Submit_Print_Request
1023 (p_printer,
1024 p_user_print_style,
1025 p_number_of_copies,
1026 g_default_document,
1027 l_language_code,
1028 l_return_status);
1029 IF l_return_status <> 'S' THEN
1030 FND_FILE.PUT(FND_FILE.LOG,'Submission error for session ' || TO_CHAR(g_session_id));
1031 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
1032 END IF;
1033 l_return_status := 'S';
1034 END IF;
1035 l_code_block := 'Print document in default language';
1036 l_return_status := 'S';
1037 Print_Document_Selection
1038 (LocalDetailRecord.document_code,
1039 LocalDetailRecord.item_code,
1040 l_language_code,
1041 GlobalRecipient.disclosure_code,
1042 l_return_status);
1043 IF l_return_status <> 'S' THEN
1044 l_code_block := 'Print Document Error';
1045 ELSE
1046 g_cover_letter := 'N';
1047 Submit_Print_Request
1048 (p_printer,
1049 p_user_print_style,
1050 p_number_of_copies,
1051 g_default_document,
1052 l_language_code,
1053 l_return_status);
1054 -- Bug #1902822 (JKB) Added above.
1055 l_print_count := l_print_count + 1;
1056 END IF;
1057 ELSE
1058 OPEN g_get_region_language;
1059 FETCH g_get_region_language INTO GlobalRgnLangRecord;
1060 IF g_get_region_language%NOTFOUND THEN
1061 IF l_new_recipient = 'YS' THEN
1062 l_return_status := 'S';
1063 Read_And_Print_Cover_Letter
1064 (l_language_code,
1065 LocalDetailRecord.item_code,
1066 GlobalRecipient.recipient_code,
1067 'I',
1068 LocalDetailRecord.order_no,
1069 '',
1070 l_return_status);
1071 IF l_return_status <> 'S' THEN
1072 l_code_block := 'Error';
1073 FND_FILE.PUT(FND_FILE.LOG,'Cover Letter error - 1');
1074 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
1075 RAISE Other_API_Error;
1076 END IF;
1077 /*
1078 ** Print the letter and increment the session id
1079 */
1080 l_return_status := 'S';
1081 g_cover_letter := 'Y';
1082 Submit_Print_Request
1083 (p_printer,
1084 p_user_print_style,
1085 p_number_of_copies,
1086 g_default_document,
1087 l_language_code,
1088 l_return_status);
1089 IF l_return_status <> 'S' THEN
1090 FND_FILE.PUT(FND_FILE.LOG,'Submission error for session ' || TO_CHAR(g_session_id));
1091 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
1092 END IF;
1093 l_return_status := 'S';
1094 END IF;
1095 l_code_block := 'Print document in default language';
1096 l_return_status := 'S';
1097 Print_Document_Selection
1098 (LocalDetailRecord.document_code,
1099 LocalDetailRecord.item_code,
1100 l_language_code,
1101 GlobalRecipient.disclosure_code,
1102 l_return_status);
1103 IF l_return_status <> 'S' THEN
1104 l_code_block := 'Print Document Error';
1105 ELSE
1106 g_cover_letter := 'N';
1107 Submit_Print_Request
1108 (p_printer,
1109 p_user_print_style,
1110 p_number_of_copies,
1111 g_default_document,
1112 l_language_code,
1113 l_return_status);
1114 -- Bug #1902822 (JKB) Added above.
1115 l_print_count := l_print_count + 1;
1116 END IF;
1117 ELSE
1118 WHILE g_get_region_language%FOUND LOOP
1119 l_language_code := GlobalRgnLangRecord.language;
1120 IF l_new_recipient = 'YS' THEN
1121 l_return_status := 'S';
1122 Read_And_Print_Cover_Letter
1123 (l_language_code,
1124 LocalDetailRecord.item_code,
1125 GlobalRecipient.recipient_code,
1126 'I',
1127 LocalDetailRecord.order_no,
1128 '',
1129 l_return_status);
1130 IF l_return_status <> 'S' THEN
1131 l_code_block := 'Error';
1132 FND_FILE.PUT(FND_FILE.LOG,'Cover Letter error - 1');
1133 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
1134 RAISE Other_API_Error;
1135 END IF;
1136 /*
1137 ** Print the letter and increment the session id
1138 */
1139 l_return_status := 'S';
1140 g_cover_letter := 'Y';
1141 Submit_Print_Request
1142 (p_printer,
1143 p_user_print_style,
1144 p_number_of_copies,
1145 g_default_document,
1146 l_language_code,
1147 l_return_status);
1148 IF l_return_status <> 'S' THEN
1149 FND_FILE.PUT(FND_FILE.LOG,'Submission error for session ' || TO_CHAR(g_session_id));
1150 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
1151 END IF;
1152 l_return_status := 'S';
1153 END IF;
1154 l_code_block := 'Print document in region language';
1155 l_return_status := 'S';
1156 Print_Document_Selection
1157 (LocalDetailRecord.document_code,
1158 LocalDetailRecord.item_code,
1159 l_language_code,
1160 GlobalRecipient.disclosure_code,
1161 l_return_status);
1162 IF l_return_status <> 'S' THEN
1163 l_code_block := 'Print Document Error';
1164 ELSE
1165 g_cover_letter := 'N';
1166 Submit_Print_Request
1167 (p_printer,
1168 p_user_print_style,
1169 p_number_of_copies,
1170 g_default_document,
1171 l_language_code,
1172 l_return_status);
1173 -- Bug #1902822 (JKB) Added above.
1174 l_print_count := l_print_count + 1;
1175 END IF;
1176 FETCH g_get_region_language INTO GlobalRgnLangRecord;
1177 END LOOP;
1178 END IF;
1179 CLOSE g_get_region_language;
1180 END IF;
1181 END IF;
1182 /*
1183 ** Print documents for shipping address
1184 */
1185 IF GlobalRecipient.shipping_address = 1 THEN
1186 IF GlobalRecipient.region_code IS NULL THEN
1187 IF l_new_recipient = 'YS' THEN
1188 /*utl_file.put_line(pg_fp, 'l_new_recipient'||l_new_recipient);*/
1189 l_return_status := 'S';
1190 Read_And_Print_Cover_Letter
1191 (l_language_code,
1192 LocalDetailRecord.item_code,
1193 GlobalRecipient.recipient_code,
1194 'S',
1195 LocalDetailRecord.order_no,
1196 '',
1197 l_return_status);
1198 IF l_return_status <> 'S' THEN
1199 l_code_block := 'Error';
1200 FND_FILE.PUT(FND_FILE.LOG,'Cover Letter error - 1');
1201 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
1202 RAISE Other_API_Error;
1203 END IF;
1204 /*
1205 ** Print the letter and increment the session id
1206 */
1207 l_return_status := 'S';
1208 g_cover_letter := 'Y';
1209 Submit_Print_Request
1210 (p_printer,
1211 p_user_print_style,
1212 p_number_of_copies,
1213 g_default_document,
1214 l_language_code,
1215 l_return_status);
1216 IF l_return_status <> 'S' THEN
1217 FND_FILE.PUT(FND_FILE.LOG,'Submission error for session ' || TO_CHAR(g_session_id));
1218 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
1219 END IF;
1220 l_return_status := 'S';
1221 END IF;
1222 l_code_block := 'Print document in default language';
1223 l_return_status := 'S';
1224 Print_Document_Selection
1225 (LocalDetailRecord.document_code,
1226 LocalDetailRecord.item_code,
1227 l_language_code,
1228 GlobalRecipient.disclosure_code,
1229 l_return_status);
1230 IF l_return_status <> 'S' THEN
1231 l_code_block := 'Print Document Error';
1232 ELSE
1233 g_cover_letter := 'N';
1234 Submit_Print_Request
1235 (p_printer,
1236 p_user_print_style,
1237 p_number_of_copies,
1238 g_default_document,
1239 l_language_code,
1240 l_return_status);
1241 -- Bug #1902822 (JKB) Added above.
1242 l_print_count := l_print_count + 1;
1243 END IF;
1244 ELSE
1245 OPEN g_get_region_language;
1246 FETCH g_get_region_language INTO GlobalRgnLangRecord;
1247 IF g_get_region_language%NOTFOUND THEN
1248 IF l_new_recipient = 'YS' THEN
1249 l_return_status := 'S';
1250 Read_And_Print_Cover_Letter
1251 (l_language_code,
1252 LocalDetailRecord.item_code,
1253 GlobalRecipient.recipient_code,
1254 'S',
1255 LocalDetailRecord.order_no,
1256 '',
1257 l_return_status);
1258 IF l_return_status <> 'S' THEN
1259 l_code_block := 'Error';
1260 FND_FILE.PUT(FND_FILE.LOG,'Cover Letter error - 1');
1261 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
1262 RAISE Other_API_Error;
1263 END IF;
1264 /*
1265 ** Print the letter and increment the session id
1266 */
1267 l_return_status := 'S';
1268 g_cover_letter := 'Y';
1269 Submit_Print_Request
1270 (p_printer,
1271 p_user_print_style,
1272 p_number_of_copies,
1273 g_default_document,
1274 l_language_code,
1275 l_return_status);
1276 IF l_return_status <> 'S' THEN
1277 FND_FILE.PUT(FND_FILE.LOG,'Submission error for session ' || TO_CHAR(g_session_id));
1278 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
1279 END IF;
1280 l_return_status := 'S';
1281 END IF;
1282 l_code_block := 'Print document in default language';
1283 l_return_status := 'S';
1284 Print_Document_Selection
1285 (LocalDetailRecord.document_code,
1286 LocalDetailRecord.item_code,
1287 l_language_code,
1288 GlobalRecipient.disclosure_code,
1289 l_return_status);
1290 IF l_return_status <> 'S' THEN
1291 l_code_block := 'Print Document Error';
1292 ELSE
1293 g_cover_letter := 'N';
1294 Submit_Print_Request
1295 (p_printer,
1296 p_user_print_style,
1297 p_number_of_copies,
1298 g_default_document,
1299 l_language_code,
1300 l_return_status);
1301 -- Bug #1902822 (JKB) Added above.
1302 l_print_count := l_print_count + 1;
1303 END IF;
1304 ELSE
1305 WHILE g_get_region_language%FOUND LOOP
1306 l_language_code := GlobalRgnLangRecord.language;
1307 IF l_new_recipient = 'YS' THEN
1308 l_return_status := 'S';
1309 Read_And_Print_Cover_Letter
1310 (l_language_code,
1311 LocalDetailRecord.item_code,
1312 GlobalRecipient.recipient_code,
1313 'S',
1314 LocalDetailRecord.order_no,
1315 '',
1316 l_return_status);
1317 IF l_return_status <> 'S' THEN
1318 l_code_block := 'Error';
1319 FND_FILE.PUT(FND_FILE.LOG,'Cover Letter error - 1');
1320 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
1321 RAISE Other_API_Error;
1322 END IF;
1323 /*
1324 ** Print the letter and increment the session id
1325 */
1326 l_return_status := 'S';
1327 g_cover_letter := 'Y';
1328 Submit_Print_Request
1329 (p_printer,
1330 p_user_print_style,
1331 p_number_of_copies,
1332 g_default_document,
1333 l_language_code,
1334 l_return_status);
1335 IF l_return_status <> 'S' THEN
1336 FND_FILE.PUT(FND_FILE.LOG,'Submission error for session ' || TO_CHAR(g_session_id));
1337 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
1338 END IF;
1339 l_return_status := 'S';
1340 END IF;
1341 l_code_block := 'Print document in region language';
1342 l_return_status := 'S';
1343 Print_Document_Selection
1344 (LocalDetailRecord.document_code,
1345 LocalDetailRecord.item_code,
1346 l_language_code,
1347 GlobalRecipient.disclosure_code,
1348 l_return_status);
1349 IF l_return_status <> 'S' THEN
1350 l_code_block := 'Print Document Error';
1351 ELSE
1352 g_cover_letter := 'N';
1353 Submit_Print_Request
1354 (p_printer,
1355 p_user_print_style,
1356 p_number_of_copies,
1357 g_default_document,
1358 l_language_code,
1359 l_return_status);
1360 -- Bug #1902822 (JKB) Added above.
1361 l_print_count := l_print_count + 1;
1362 END IF;
1363 FETCH g_get_region_language INTO GlobalRgnLangRecord;
1364 END LOOP;
1365 END IF;
1366 CLOSE g_get_region_language;
1367 END IF;
1368 END IF;
1369 /*
1370 ** Print documents for other addresses
1371 */
1372 IF GlobalRecipient.additional_address_flag = 1 THEN
1373 OPEN g_get_other_addresses;
1374 FETCH g_get_other_addresses INTO GlobalOtherAddrRecord;
1375 WHILE g_get_other_addresses%FOUND LOOP
1376 IF GlobalRecipient.region_code IS NULL THEN
1377 IF l_new_recipient = 'YS' THEN
1378 l_return_status := 'S';
1379 Read_And_Print_Cover_Letter
1380 (l_language_code,
1381 LocalDetailRecord.item_code,
1382 GlobalRecipient.recipient_code,
1383 'O',
1384 '',
1385 GlobalOtherAddrRecord.addr_id,
1386 l_return_status);
1387 IF l_return_status <> 'S' THEN
1388 l_code_block := 'Error';
1389 FND_FILE.PUT(FND_FILE.LOG,'Cover Letter error - 1');
1390 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
1391 RAISE Other_API_Error;
1392 END IF;
1393 /*
1394 ** Print the letter and increment the session id
1395 */
1396 l_return_status := 'S';
1397 g_cover_letter := 'Y';
1398 Submit_Print_Request
1399 (p_printer,
1400 p_user_print_style,
1401 p_number_of_copies,
1402 g_default_document,
1403 l_language_code,
1404 l_return_status);
1405
1406 IF l_return_status <> 'S' THEN
1407 FND_FILE.PUT(FND_FILE.LOG,'Submission error for session ' || TO_CHAR(g_session_id));
1408 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
1409 END IF;
1410 l_return_status := 'S';
1411 END IF;
1412 l_code_block := 'Print document in default language';
1413 l_return_status := 'S';
1414 Print_Document_Selection
1415 (LocalDetailRecord.document_code,
1416 LocalDetailRecord.item_code,
1417 l_language_code,
1418 GlobalRecipient.disclosure_code,
1419 l_return_status);
1420 IF l_return_status <> 'S' THEN
1421 l_code_block := 'Print Document Error';
1422 ELSE
1423 g_cover_letter := 'N';
1424 Submit_Print_Request
1425 (p_printer,
1426 p_user_print_style,
1427 p_number_of_copies,
1428 g_default_document,
1429 l_language_code,
1430 l_return_status);
1431 -- Bug #1902822 (JKB) Added above.
1432 l_print_count := l_print_count + 1;
1433 END IF;
1434 ELSE
1435 OPEN g_get_region_language;
1436 FETCH g_get_region_language INTO GlobalRgnLangRecord;
1437 IF g_get_region_language%NOTFOUND THEN
1438 IF l_new_recipient = 'YS' THEN
1439 l_return_status := 'S';
1440 Read_And_Print_Cover_Letter
1441 (l_language_code,
1442 LocalDetailRecord.item_code,
1443 GlobalRecipient.recipient_code,
1444 'O',
1445 '',
1446 GlobalOtherAddrRecord.addr_id,
1447 l_return_status);
1448 IF l_return_status <> 'S' THEN
1449 l_code_block := 'Error';
1450 FND_FILE.PUT(FND_FILE.LOG,'Cover Letter error - 1');
1451 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
1452 RAISE Other_API_Error;
1453 END IF;
1454 /*
1455 ** Print the letter and increment the session id
1456 */
1457 l_return_status := 'S';
1458 g_cover_letter := 'Y';
1459 Submit_Print_Request
1460 (p_printer,
1461 p_user_print_style,
1462 p_number_of_copies,
1463 g_default_document,
1464 l_language_code,
1465 l_return_status);
1466
1467 IF l_return_status <> 'S' THEN
1468 FND_FILE.PUT(FND_FILE.LOG,'Submission error for session ' || TO_CHAR(g_session_id));
1469 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
1470 END IF;
1471 l_return_status := 'S';
1472 END IF;
1473 l_code_block := 'Print document in default language';
1474 l_return_status := 'S';
1475 Print_Document_Selection
1476 (LocalDetailRecord.document_code,
1477 LocalDetailRecord.item_code,
1478 l_language_code,
1479 GlobalRecipient.disclosure_code,
1480 l_return_status);
1481 IF l_return_status <> 'S' THEN
1482 l_code_block := 'Print Document Error';
1483 ELSE
1484 g_cover_letter := 'N';
1485 Submit_Print_Request
1486 (p_printer,
1487 p_user_print_style,
1488 p_number_of_copies,
1489 g_default_document,
1490 l_language_code,
1491 l_return_status);
1492 -- Bug #1902822 (JKB) Added above.
1493 l_print_count := l_print_count + 1;
1494 END IF;
1495 ELSE
1496 WHILE g_get_region_language%FOUND LOOP
1497 l_language_code := GlobalRgnLangRecord.language;
1498 IF l_new_recipient = 'YS' THEN
1499 l_return_status := 'S';
1500 Read_And_Print_Cover_Letter
1501 (l_language_code,
1502 LocalDetailRecord.item_code,
1503 GlobalRecipient.recipient_code,
1504 'O',
1505 '',
1506 GlobalOtherAddrRecord.addr_id,
1507 l_return_status);
1508 IF l_return_status <> 'S' THEN
1509 l_code_block := 'Error';
1510 FND_FILE.PUT(FND_FILE.LOG,'Cover Letter error - 1');
1511 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
1512 RAISE Other_API_Error;
1513 END IF;
1514 /*
1515 ** Print the letter and increment the session id
1516 */
1517 l_return_status := 'S';
1518 g_cover_letter := 'Y';
1519 Submit_Print_Request
1520 (p_printer,
1521 p_user_print_style,
1522 p_number_of_copies,
1523 g_default_document,
1524 l_language_code,
1525 l_return_status);
1526
1527 IF l_return_status <> 'S' THEN
1528 FND_FILE.PUT(FND_FILE.LOG,'Submission error for session ' || TO_CHAR(g_session_id));
1529 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
1530 END IF;
1531 l_return_status := 'S';
1532 END IF;
1533 l_code_block := 'Print document in region language';
1534 l_return_status := 'S';
1535 Print_Document_Selection
1536 (LocalDetailRecord.document_code,
1537 LocalDetailRecord.item_code,
1538 l_language_code,
1539 GlobalRecipient.disclosure_code,
1540 l_return_status);
1541 IF l_return_status <> 'S' THEN
1542 l_code_block := 'Print Document Error';
1543 ELSE
1544 g_cover_letter := 'N';
1545 Submit_Print_Request
1546 (p_printer,
1547 p_user_print_style,
1548 p_number_of_copies,
1549 g_default_document,
1550 l_language_code,
1551 l_return_status);
1552 -- Bug #1902822 (JKB) Added above.
1553 l_print_count := l_print_count + 1;
1554 END IF;
1555 FETCH g_get_region_language INTO GlobalRgnLangRecord;
1556 END LOOP;
1557 END IF;
1558 CLOSE g_get_region_language;
1559 END IF;
1560 FETCH g_get_other_addresses INTO GlobalOtherAddrRecord;
1561 END LOOP;
1562 CLOSE g_get_other_addresses;
1563 END IF;
1564 END IF;
1565 /*
1566 ** Set the line status to printing completed
1567 */
1568 UPDATE gr_selection
1569 SET line_status = 6,
1570 document_code = NVL(document_code, g_default_document)
1571 WHERE ROWID = LocalDetailRecord.ROWID;
1572 /*
1573 ** Commit the work if the flag is set
1574 */
1575 IF FND_API.To_Boolean(p_commit) THEN
1576 COMMIT WORK;
1577 END IF;
1578 /*
1579 ** Now submit the print job.
1580 */
1581 -- IF l_print_count > 0 THEN
1582 -- IF p_number_of_copies > 0 THEN
1583 -- g_print_status := FND_REQUEST.SET_PRINT_OPTIONS
1584 -- (p_printer,
1585 -- p_user_print_style,
1586 -- p_number_of_copies, TRUE, 'N');
1587 -- END IF;
1588 -- g_concurrent_id := FND_REQUEST.SUBMIT_REQUEST
1589 -- ('GR', 'GRRPT030_DOC', '', '', FALSE, g_session_id,
1590 -- Bug #1673690 (JKB)
1591 -- g_default_document, l_language_code, CHR(0),
1592 -- '', '', '', '', '', '',
1593 -- '', '', '', '', '', '', '', '', '', '',
1594 -- '', '', '', '', '', '', '', '', '', '',
1595 -- '', '', '', '', '', '', '', '', '', '',
1596 -- '', '', '', '', '', '', '', '', '', '',
1597 -- '', '', '', '', '', '', '', '', '', '',
1598 -- '', '', '', '', '', '', '', '', '', '',
1599 -- '', '', '', '', '', '', '', '', '', '',
1600 -- '', '', '', '', '', '', '', '', '', '',
1601 -- '', '', '', '', '', '', '', '', '', '');
1602 -- END IF;
1603 -- Bug #1902822 (JKB) Commented above.
1604 /*
1605 ** Get the session id and clear print work table
1606 */
1607 SELECT gr_work_build_docs_s.nextval INTO g_session_id
1608 FROM dual;
1609 -- Bug #1902822 (JKB) Added above.
1610
1611 /*
1612 ** Now get the next row
1613 */
1614 FETCH c_get_line_details INTO LocalDetailRecord;
1615 END LOOP;
1616 ELSE
1617 OPEN c_get_line_message;
1618 LOOP
1619 FETCH c_get_line_message INTO LocalLineRecord;
1620 EXIT WHEN c_get_line_message%NOTFOUND;
1621 FND_FILE.PUT(FND_FILE.LOG, LocalLineRecord.message);
1622 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
1623 END LOOP;
1624 CLOSE c_get_line_message;
1625
1626 END IF;
1627 CLOSE c_get_line_details;
1628 /*
1629 ** Update the header status to Print Completed
1630 */
1631 UPDATE gr_selection_header
1632 SET status = 6
1633 WHERE batch_no = p_batch_number;
1634
1635 IF FND_API.To_Boolean(p_commit) THEN
1636 COMMIT WORK;
1637 END IF;
1638 /*
1639 ** Process all flag set to 1 means automatically chain
1640 ** to process the selected lines.
1641 */
1642 IF p_process_all_flag = 1 THEN
1643 /* l_return_status := FND_API.G_RET_STS_SUCCESS; */
1644 /*utl_file.put_line(pg_fp, 'process '|| p_process_all_flag);*/
1645 Update_Dispatch_History
1646 (errbuf,
1647 retcode,
1648 p_commit,
1649 p_init_msg_list,
1650 p_validation_level,
1651 p_api_version,
1652 p_batch_number,
1653 l_return_status,
1654 x_msg_count,
1655 x_msg_data);
1656 IF l_return_status <> 'S' THEN
1657 RAISE Update_History_Error;
1658 END IF;
1659 END IF;
1660
1661 EXCEPTION
1662
1663 WHEN Incompatible_API_Version_Error THEN
1664 Handle_Error_Messages
1665 ('GR_API_VERSION_ERROR',
1666 'VERSION',
1667 p_api_version,
1668 x_msg_count,
1669 x_msg_data,
1670 l_return_status);
1671 IF p_called_by_form = 'F' THEN
1672 X_return_status := l_return_status;
1673 ELSE
1674 APP_EXCEPTION.Raise_Exception;
1675 END IF;
1676 WHEN Batch_Number_Null_Error THEN
1677 Handle_Error_Messages
1678 ('GR_NULL_BATCH_NUMBER',
1679 '',
1680 '',
1681 x_msg_count,
1682 x_msg_data,
1683 l_return_status);
1684 IF p_called_by_form = 'F' THEN
1685 X_return_status := l_return_status;
1686 ELSE
1687 APP_EXCEPTION.Raise_Exception;
1688 END IF;
1689 WHEN Invalid_Batch_Number_Error THEN
1690 Handle_Error_Messages
1691 ('GR_INVALID_BATCH_NUMBER',
1692 'BATCH',
1693 p_batch_number,
1694 x_msg_count,
1695 x_msg_data,
1696 l_return_status);
1697 IF p_called_by_form = 'F' THEN
1698 X_return_status := l_return_status;
1699 ELSE
1700 APP_EXCEPTION.Raise_Exception;
1701 END IF;
1702 WHEN Invalid_Batch_Status_Error THEN
1703 Handle_Error_Messages
1704 ('GR_INVALID_BATCH_STATUS',
1705 'STATUS',
1706 GlobalBatchHeader.status,
1707 x_msg_count,
1708 x_msg_data,
1709 l_return_status);
1710 IF p_called_by_form = 'F' THEN
1711 X_return_status := l_return_status;
1712 ELSE
1713 APP_EXCEPTION.Raise_Exception;
1714 END IF;
1715 WHEN Update_History_Error THEN
1716 Handle_Error_Messages
1717 ('GR_UNEXPECTED_ERROR',
1718 'TEXT',
1719 l_msg_data,
1720 x_msg_count,
1721 x_msg_data,
1722 l_return_status);
1723 IF p_called_by_form = 'F' THEN
1724 X_return_status := l_return_status;
1725 ELSE
1726 APP_EXCEPTION.Raise_Exception;
1727 END IF;
1728 WHEN Other_API_Error THEN
1729 Handle_Error_Messages
1730 ('GR_UNEXPECTED_ERROR',
1731 'TEXT',
1732 l_msg_data,
1733 x_msg_count,
1734 x_msg_data,
1735 l_return_status);
1736 IF p_called_by_form = 'F' THEN
1737 X_return_status := l_return_status;
1738 ELSE
1739 APP_EXCEPTION.Raise_Exception;
1740 END IF;
1741 WHEN OTHERS THEN
1742 l_oracle_error := SQLCODE;
1743 /*l_code_block := SUBSTR(SQLERRM, 1, 200);*/
1744 l_code_block := l_code_block || ' ' || TO_CHAR(l_oracle_error);
1745 FND_MESSAGE.SET_NAME('GR',
1746 'GR_UNEXPECTED_ERROR');
1747 FND_MESSAGE.SET_TOKEN('TEXT',
1748 l_code_block||sqlerrm,
1749 FALSE);
1750 APP_EXCEPTION.Raise_Exception;
1751 /*utl_file.fflush(pg_fp);
1752 utl_file.fclose(pg_fp);*/
1753 END Process_Selections;
1754 /*
1755 ** This procedure updates the dispatch history tables for the
1756 ** batch. The selections and print should have been carried out
1757 ** before this procedure is run.
1758 */
1759 PROCEDURE Update_Dispatch_History
1760 (errbuf OUT NOCOPY VARCHAR2,
1761 retcode OUT NOCOPY VARCHAR2,
1762 p_commit IN VARCHAR2,
1763 p_init_msg_list IN VARCHAR2,
1764 p_validation_level IN NUMBER,
1765 p_api_version IN NUMBER,
1766 p_batch_number IN NUMBER,
1767 x_return_status OUT NOCOPY VARCHAR2,
1768 x_msg_count OUT NOCOPY NUMBER,
1769 x_msg_data OUT NOCOPY VARCHAR2)
1770 IS
1771
1772 /*
1773 ** Alpha Variables
1774 */
1775 L_CODE_BLOCK VARCHAR2(2000);
1776 L_COMMIT VARCHAR2(1);
1777 L_CALLED_BY_FORM VARCHAR2(1);
1778 L_ROWID VARCHAR2(18);
1779 L_RETURN_STATUS VARCHAR2(1);
1780 L_STATUS VARCHAR2(1);
1781 L_MSG_DATA VARCHAR2(1000);
1782 L_KEY_EXISTS VARCHAR2(1);
1783 L_BLANK_ATTRIBUTE gr_dispatch_histories.attribute1%TYPE;
1784 L_BLANK_CATEGORY gr_dispatch_histories.attribute_category%TYPE;
1785 L_MSDS_DATE gr_dispatch_histories.date_msds_sent%TYPE;
1786
1787 L_API_NAME CONSTANT VARCHAR2(30) := 'Update Dispatch History';
1788
1789 L_CURRENT_DATE CONSTANT DATE := sysdate;
1790 L_LANGUAGE_CODE gr_document_print.language%TYPE;
1791
1792 pg_fp utl_file.file_type;
1793 /*
1794 ** Numeric Variables
1795 */
1796 L_ORACLE_ERROR NUMBER;
1797 L_API_VERSION CONSTANT NUMBER := 1.0;
1798 L_USER_ID NUMBER;
1799
1800
1801 /*
1802 ** Exceptions
1803 */
1804 BATCH_NUMBER_NULL_ERROR EXCEPTION;
1805 INVALID_BATCH_STATUS_ERROR EXCEPTION;
1806 INCOMPATIBLE_API_VERSION_ERROR EXCEPTION;
1807 INVALID_BATCH_NUMBER_ERROR EXCEPTION;
1808 DISPATCH_HISTORY_INSERT_ERROR EXCEPTION;
1809
1810 /*
1811 ** Define the cursors
1812 **
1813 ** Get the batch details
1814 */
1815 CURSOR c_get_batch_details
1816 IS
1817 SELECT sd.batch_no,
1818 sd.order_no,
1819 sd.order_line_number,
1820 sd.document_code,
1821 sd.item_code,
1822 sd.print_flag,
1823 sd.recipient_code,
1824 sd.date_msds_sent,
1825 sd.user_id,
1826 sd.user_override
1827 FROM gr_selection sd
1828 WHERE sd.batch_no = p_batch_number;
1829 BatchDetails c_get_batch_details%ROWTYPE;
1830 /*
1831 ** Check the recipient exists
1832 */
1833 CURSOR c_get_recipient
1834 IS
1835 SELECT ri.recipient_code
1836 FROM gr_recipient_info ri
1837 WHERE ri.recipient_code = BatchDetails.recipient_code;
1838 LocalRecipientRecord c_get_recipient%ROWTYPE;
1839 /*
1840 ** Get the customer name from the OPM Customer table
1841 */
1842 CURSOR c_get_customer_name
1843 IS
1844 SELECT cm.cust_name
1845 FROM op_cust_mst cm
1846 WHERE cm.cust_no = BatchDetails.recipient_code;
1847 LocalCustRecord c_get_customer_name%ROWTYPE;
1848
1849 CURSOR c_get_language_code
1850 IS
1851 SELECT language
1852 FROM gr_document_print
1853 WHERE document_code = BatchDetails.document_code;
1854 LocalLangRecord c_get_language_code%ROWTYPE;
1855
1856 /*
1857 ** Bug 2342375 Mercy Thomas 08/15/2002 Added the following code columns Recipeint_code and Item_Code to the cursor c_get_status
1858 */
1859
1860 CURSOR c_get_status
1861 IS
1862 SELECT recipient_code,
1863 item_code,
1864 line_status
1865 FROM gr_selection
1866 WHERE batch_no = p_batch_number;
1867 LocalStatRecord c_get_status%ROWTYPE;
1868 /*
1869 ** Bug 2342375 Mercy Thomas 08/15/2002 End the code changes
1870 */
1871
1872 v_item_code GR_ITEM_GENERAL.ITEM_CODE%TYPE;
1873
1874 CURSOR c_get_item
1875 IS
1876 SELECT item_code
1877 FROM gr_item_general
1878 WHERE item_code = v_item_code;
1879 ItemRecord c_get_item%ROWTYPE;
1880
1881 /* Get the generic item information */
1882 CURSOR c_get_generic_item
1883 IS
1884 SELECT ig1.item_code,
1885 gi.item_no
1886 FROM gr_item_general ig1,
1887 gr_generic_items_b gi
1888 WHERE gi.item_no = v_item_code
1889 AND gi.item_code = ig1.item_code;
1890 GenericRecord c_get_generic_item%ROWTYPE;
1891
1892 l_item_code GR_ITEM_GENERAL.ITEM_CODE%TYPE;
1893 t_item_code GR_ITEM_GENERAL.ITEM_CODE%TYPE;
1894 t_recipient_code GR_RECIPIENT_INFO.RECIPIENT_CODE%TYPE;
1895
1896 /*
1897 ** Bug 2342375 Mercy Thomas 08/15/2002 End the code changes
1898 */
1899
1900 BEGIN
1901
1902 /* Standard API Start */
1903
1904 SAVEPOINT Update_Dispatch_History;
1905 l_code_block := 'Initialize';
1906 l_blank_attribute := NULL;
1907
1908 /* Initialize the message list if true */
1909
1910 IF FND_API.To_Boolean(p_init_msg_list) THEN
1911 FND_MSG_PUB.Initialize;
1912 END IF;
1913
1914 /* Check the API version passed in matches the
1915 ** internal API version.
1916 */
1917 IF NOT FND_API.Compatible_API_Call
1918 (l_api_version,
1919 p_api_version,
1920 l_api_name,
1921 g_pkg_name) THEN
1922 RAISE Incompatible_API_Version_Error;
1923 END IF;
1924
1925 /* Set return status to successful */
1926
1927 l_return_status := FND_API.G_RET_STS_SUCCESS;
1928
1929 /* Check the passed in batch number is not null
1930 ** and exists on the batch selection header.
1931 */
1932 l_code_block := 'Validate the batch number';
1933 g_batch_number := p_batch_number;
1934
1935 IF g_batch_number IS NULL THEN
1936 RAISE Batch_Number_Null_Error;
1937 ELSE
1938 OPEN g_get_batch_status;
1939 FETCH g_get_batch_status INTO GlobalBatchHeader;
1940 IF g_get_batch_status%NOTFOUND THEN
1941 CLOSE g_get_batch_status;
1942 RAISE Invalid_Batch_Number_Error;
1943 ELSE
1944 IF GlobalBatchHeader.status <> 6 THEN
1945 CLOSE g_get_batch_status;
1946 RAISE Invalid_Batch_Status_Error;
1947 ELSE
1948 /*
1949 ** Now update the batch header status to in process
1950 */
1951
1952 CLOSE g_get_batch_status;
1953 l_code_block := 'Update the batch header';
1954 UPDATE gr_selection_header
1955 SET status = 7
1956 WHERE batch_no = p_batch_number;
1957
1958 FND_FILE.PUT(FND_FILE.LOG, l_code_block);
1959 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
1960 /*
1961 ** Now process the details and update dispatch history
1962 */
1963 l_code_block := 'Process the batch details';
1964 FND_FILE.PUT(FND_FILE.LOG, l_code_block);
1965 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
1966 OPEN c_get_batch_details;
1967 FETCH c_get_batch_details INTO BatchDetails;
1968 WHILE c_get_batch_details%FOUND LOOP
1969 IF BatchDetails.print_flag = 'Y' OR
1970 (BatchDetails.print_flag = 'N' AND
1971 BatchDetails.user_override = 'Y') THEN
1972 l_code_block := 'Updating ' || BatchDetails.order_no;
1973 l_code_block := l_code_block || ' ' || BatchDetails.item_code;
1974 FND_FILE.PUT(FND_FILE.LOG, l_code_block);
1975 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
1976 l_commit := 'F';
1977 l_called_by_form := 'F';
1978 l_user_id := FND_GLOBAL.USER_ID;
1979
1980 OPEN c_get_recipient;
1981 FETCH c_get_recipient INTO LocalRecipientRecord;
1982 IF c_get_recipient%NOTFOUND THEN
1983 l_code_block := 'Write recipient record ';
1984
1985 OPEN c_get_customer_name;
1986 FETCH c_get_customer_name INTO LocalCustRecord;
1987 IF c_get_customer_name%NOTFOUND THEN
1988 CLOSE c_get_customer_name;
1989 l_code_block := 'No customer info';
1990 END IF; /* c_get_customer_name Not Found */
1991 CLOSE c_get_customer_name;
1992
1993 GR_RECIPIENT_INFO_PKG.Insert_Row
1994 (l_commit,
1995 l_called_by_form,
1996 BatchDetails.recipient_code,
1997 LocalCustRecord.cust_name,
1998 BatchDetails.document_code,
1999 GlobalBatchHeader.territory_code,
2000 '0', /* Do not print recipient product code */
2001 '0', /* Do not update address */
2002 '0', /* Do not disclose all ingredients */
2003 'O', /* Print organization address on document */
2004 'R', /* Print documents as required */
2005 '', /* No recipient specific disclosure code */
2006 '', /* No recipient region code */
2007 '', /* No special cover letter */
2008 '0', /* No additional documents */
2009 '0', /* No Other addresses to send to */
2010 '1', /* Send documents to ship address */
2011 '0', /* Do not send to invoice address */
2012 l_blank_category,
2013 l_blank_attribute,
2014 l_blank_attribute,
2015 l_blank_attribute,
2016 l_blank_attribute,
2017 l_blank_attribute,
2018 l_blank_attribute,
2019 l_blank_attribute,
2020 l_blank_attribute,
2021 l_blank_attribute,
2022 l_blank_attribute,
2023 l_blank_attribute,
2024 l_blank_attribute,
2025 l_blank_attribute,
2026 l_blank_attribute,
2027 l_blank_attribute,
2028 l_blank_attribute,
2029 l_blank_attribute,
2030 l_blank_attribute,
2031 l_blank_attribute,
2032 l_blank_attribute,
2033 l_blank_attribute,
2034 l_blank_attribute,
2035 l_blank_attribute,
2036 l_blank_attribute,
2037 l_blank_attribute,
2038 l_blank_attribute,
2039 l_blank_attribute,
2040 l_blank_attribute,
2041 l_blank_attribute,
2042 l_blank_attribute,
2043 l_user_id,
2044 l_current_date,
2045 l_user_id,
2046 l_current_date,
2047 l_user_id,
2048 --LocalRecipientRecord.time_period,
2049 l_rowid,
2050 l_return_status,
2051 l_oracle_error,
2052 l_msg_data);
2053
2054 IF l_return_status <> 'S' THEN
2055 RAISE Dispatch_History_Insert_Error;
2056 END IF;
2057 END IF; /* c_get_recipient Not Found */
2058 CLOSE c_get_recipient;
2059 /*
2060 ** Set the date to the system date if NULL
2061 */
2062 IF BatchDetails.date_msds_sent IS NULL THEN
2063 l_msds_date := l_current_date;
2064 ELSE
2065 l_msds_date := BatchDetails.date_msds_sent;
2066 END IF; /* BatchDetails.date_msds_sent IS NULL */
2067 IF BatchDetails.document_code IS NOT NULL THEN
2068 l_return_status := FND_API.G_RET_STS_SUCCESS;
2069 FND_FILE.PUT(FND_FILE.LOG, ' Check Primary Key for Dispatch Histories ' );
2070 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
2071 /*
2072 ** Bug 2342375 Mercy Thomas 08/15/2002 Added the following code to check for the validity of the item code before Inserting
2073 ** the Dispatch History Table.
2074 */
2075 v_item_code := BatchDetails.item_code;
2076 OPEN c_get_item;
2077 FETCH c_get_item INTO ItemRecord;
2078 IF c_get_item%NOTFOUND THEN
2079
2080 l_code_block := 'Non Regulatory Item ' || v_item_code;
2081 OPEN c_get_generic_item;
2082 FETCH c_get_generic_item INTO GenericRecord;
2083 IF c_get_generic_item%NOTFOUND THEN
2084
2085 l_code_block := 'Inventory Item ' || ItemRecord.item_code;
2086 l_item_code := NULL;
2087 ELSE
2088 l_item_code := GenericRecord.item_code;
2089 END IF;
2090 CLOSE c_get_generic_item;
2091 ELSE
2092 l_item_code := ItemRecord.item_code;
2093 END IF;
2094 CLOSE c_get_item;
2095 /*
2096 ** Bug 2342375 Mercy Thomas 08/15/2002 End of the Code Changes
2097 */
2098 /*
2099 ** Bug 2342375 Mercy Thomas 08/15/2002 Added the following IF condition to check if the Item Code exists or not
2100 */
2101
2102 IF l_item_code IS NOT NULL THEN
2103 /*
2104 ** Bug 2342375 Mercy Thomas 08/15/2002 End of the Code Changes
2105 */
2106
2107 GR_DISPATCH_HISTORIES_PKG.Check_Primary_Key
2108 (BatchDetails.document_code,
2109 1, /* Document text id */
2110 /*
2111 ** Bug 2342375 Mercy Thomas 08/15/2002 Changed the BatchDetails.item_code to l_item_code
2112 */
2113 l_item_code,
2114 /*
2115 ** Bug 2342375 Mercy Thomas 08/15/2002 End of the Code Changes
2116 */
2117 BatchDetails.recipient_code,
2118 l_msds_date,
2119 'F',
2120 l_rowid,
2121 l_key_exists);
2122 IF NOT FND_API.TO_BOOLEAN(l_key_exists) THEN
2123 GR_DISPATCH_HISTORIES_PKG.Insert_Row
2124 (l_commit,
2125 l_called_by_form,
2126 BatchDetails.document_code,
2127 1, /* Document text id */
2128 /*
2129 ** Bug 2342375 Mercy Thomas 08/15/2002 Changed the BatchDetails.item_code to l_item_code
2130 */
2131 l_item_code,
2132 /*
2133 ** Bug 2342375 Mercy Thomas 08/15/2002 End of the Code Changes
2134 */
2135 BatchDetails.recipient_code,
2136 l_msds_date,
2137 1, /* Dispatch method */
2138 1, /* Cover letter text id */
2139 l_blank_category,
2140 l_blank_attribute,
2141 l_blank_attribute,
2142 l_blank_attribute,
2143 l_blank_attribute,
2144 l_blank_attribute,
2145 l_blank_attribute,
2146 l_blank_attribute,
2147 l_blank_attribute,
2148 l_blank_attribute,
2149 l_blank_attribute,
2150 l_blank_attribute,
2151 l_blank_attribute,
2152 l_blank_attribute,
2153 l_blank_attribute,
2154 l_blank_attribute,
2155 l_blank_attribute,
2156 l_blank_attribute,
2157 l_blank_attribute,
2158 l_blank_attribute,
2159 l_blank_attribute,
2160 l_blank_attribute,
2161 l_blank_attribute,
2162 l_blank_attribute,
2163 l_blank_attribute,
2164 l_blank_attribute,
2165 l_blank_attribute,
2166 l_blank_attribute,
2167 l_blank_attribute,
2168 l_blank_attribute,
2169 l_blank_attribute,
2170 l_user_id,
2171 l_current_date,
2172 l_user_id,
2173 l_current_date,
2174 l_user_id,
2175 l_rowid,
2176 l_return_status,
2177 l_oracle_error,
2178 l_msg_data);
2179
2180 IF l_return_status <> 'S' THEN
2181 RAISE Dispatch_History_Insert_Error;
2182 END IF;
2183 END IF; /* NOT FND_API.TO_BOOLEAN(l_key_exists) */
2184 END IF; /* l_item_code IS NOT NULL */
2185 ELSE
2186 FND_MESSAGE.SET_NAME('GR', 'GR_NULL_BATCH_NUMBER');
2187 FND_FILE.PUT(FND_FILE.LOG, FND_MESSAGE.GET);
2188 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
2189 END IF; /* BatchDetails.document_code IS NOT NULL */
2190 /*
2191 ** Update the line status
2192 */
2193 UPDATE gr_selection sd
2194 SET sd.line_status = 8
2195 WHERE sd.batch_no = BatchDetails.batch_no
2196 AND sd.order_no = BatchDetails.order_no
2197 AND sd.order_line_number = BatchDetails.order_line_number;
2198 END IF; /* BatchDetails.print_flag = 'Y' OR (BatchDetails.print_flag = 'N' AND BatchDetails.user_override = 'Y') */
2199 /*
2200 ** Get the next row of data
2201 */
2202 FETCH c_get_batch_details INTO BatchDetails;
2203 END LOOP;
2204 /*
2205 ** Update the header status to Updated
2206 */
2207 UPDATE gr_selection_header
2208 SET status = 8
2209 WHERE batch_no = p_batch_number;
2210
2211 /*
2212 ** Getting the language code
2213 */
2214 OPEN c_get_language_code;
2215 FETCH c_get_language_code INTO LocalLangRecord;
2216 l_language_code := LocalLangRecord.language;
2217 CLOSE c_get_language_code;
2218
2219 /*
2220 ** Commit the work if the flag is set
2221 */
2222 IF FND_API.To_Boolean(p_commit) THEN
2223 COMMIT WORK;
2224 END IF;
2225 -- Bug #1902822 (JKB) Added above.
2226 /*
2227 ** Now submit the print job.
2228 */
2229 /* IF p_number_of_copies > 0 THEN
2230 g_print_status := FND_REQUEST.SET_PRINT_OPTIONS
2231 (p_printer,
2232 p_user_print_style,
2233 p_number_of_copies,
2234 TRUE,
2235 'N');
2236 END IF;
2237 */
2238
2239 /* Bug #2286375 Gk Changes*/
2240 OPEN c_get_status;
2241 FETCH c_get_status INTO LocalStatRecord;
2242 WHILE c_get_status%FOUND LOOP
2243 l_status := LocalStatRecord.line_status;
2244 /*
2245 ** Bug 2342375 Mercy Thomas 08/15/2002 Added the following code into the Loop. As the Dispatch History is being Printed
2246 ** only for the last Item Code or Recipient code fetched by the Cursor LocalRecipienRecord.
2247 */
2248 v_item_code := LocalStatRecord.item_code;
2249 OPEN c_get_item;
2250 FETCH c_get_item INTO ItemRecord;
2251 IF c_get_item%NOTFOUND THEN
2252
2253 l_code_block := 'Non Regulatory Item ' || v_item_code;
2254 OPEN c_get_generic_item;
2255 FETCH c_get_generic_item INTO GenericRecord;
2256 IF c_get_generic_item%NOTFOUND THEN
2257
2258 l_code_block := 'Inventory Item ' || ItemRecord.item_code;
2259 t_item_code := NULL;
2260 ELSE
2261 t_item_code := GenericRecord.item_code;
2262 END IF;
2263 CLOSE c_get_generic_item;
2264 ELSE
2265 t_item_code := ItemRecord.item_code;
2266 END IF;
2267 CLOSE c_get_item;
2268 IF (t_item_code IS NOT NULL or LocalStatRecord.recipient_code IS NOT NULL) THEN
2269 IF l_status = 8 THEN
2270 g_concurrent_id := FND_REQUEST.SUBMIT_REQUEST
2271 ('GR', 'GRRPT024', '', '', FALSE, 0,
2272 -- Bug #1673690 (JKB)
2273 LocalStatRecord.recipient_code,
2274 LocalStatRecord.recipient_code,
2275 t_item_code, t_item_code,
2276 CHR(0), g_session_id, '', '',
2277 '', '', '', '', '', '', '', '', '', '',
2278 '', '', '', '', '', '', '', '', '', '',
2279 '', '', '', '', '', '', '', '', '', '',
2280 '', '', '', '', '', '', '', '', '', '',
2281 '', '', '', '', '', '', '', '', '', '',
2282 '', '', '', '', '', '', '', '', '', '',
2283 '', '', '', '', '', '', '', '', '', '',
2284 '', '', '', '', '', '', '', '', '', '',
2285 '', '', '', '', '', '', '', '', '', '');
2286
2287
2288 IF FND_API.To_Boolean(p_commit) THEN
2289 COMMIT WORK;
2290 END IF;
2291 END IF;
2292 END IF;
2293 /*
2294 ** Bug 2342375 Mercy Thomas 08/15/2002 End of the code changes
2295 */
2296
2297 FETCH c_get_status INTO LocalStatRecord;
2298 END LOOP;
2299 CLOSE c_get_status;
2300 END IF;
2301 END IF;
2302 END IF;
2303
2304 EXCEPTION
2305
2306 WHEN Invalid_Batch_Status_Error THEN
2307 ROLLBACK TO SAVEPOINT Update_Dispatch_History;
2308 Handle_Error_Messages
2309 ('GR_INVALID_BATCH_STATUS',
2310 'STATUS',
2311 GlobalBatchHeader.status,
2312 x_msg_count,
2313 x_msg_data,
2314 x_return_status);
2315
2316 WHEN Batch_Number_Null_Error THEN
2317 ROLLBACK TO SAVEPOINT Update_Dispatch_History;
2318 Handle_Error_Messages
2319 ('GR_NULL_BATCH_NUMBER',
2320 '',
2321 '',
2322 x_msg_count,
2323 x_msg_data,
2324 x_return_status);
2325
2326 WHEN Incompatible_API_Version_Error THEN
2327 ROLLBACK TO SAVEPOINT Update_Dispatch_History;
2328 Handle_Error_Messages
2329 ('GR_API_VERSION_ERROR',
2330 'VERSION',
2331 p_api_version,
2332 x_msg_count,
2333 x_msg_data,
2334 x_return_status);
2335
2336 WHEN Invalid_Batch_Number_Error THEN
2337 ROLLBACK TO SAVEPOINT Update_Dispatch_History;
2338 Handle_Error_Messages
2339 ('GR_INVALID_BATCH_NUMBER',
2340 'BATCH',
2341 p_batch_number,
2342 x_msg_count,
2343 x_msg_data,
2344 x_return_status);
2345
2346 WHEN Dispatch_History_Insert_Error THEN
2347 ROLLBACK TO SAVEPOINT Update_Dispatch_History;
2348 Handle_Error_Messages
2349 ('GR_UNEXPECTED_ERROR',
2350 'TEXT',
2351 l_msg_data,
2352 x_msg_count,
2353 x_msg_data,
2354 x_return_status);
2355
2356 WHEN OTHERS THEN
2357 ROLLBACK TO SAVEPOINT Update_Dispatch_History;
2358 l_oracle_error := SQLCODE;
2359 l_code_block := l_code_block || ' ' || TO_CHAR(l_oracle_error);
2360 FND_MESSAGE.SET_NAME('GR',
2361 'GR_UNEXPECTED_ERROR');
2362 FND_MESSAGE.SET_TOKEN('TEXT',
2363 l_code_block||sqlerrm,
2364 FALSE);
2365 END Update_Dispatch_History;
2366 /*
2367 ** This procedure takes the input from form GRFRM037 and processes the
2368 ** recipient and item selections to print cover letters and documents.
2369 **
2370 ** p_items_to_print indicates if master items or inventriy items or both
2371 ** are to be printed.
2372 */
2373 PROCEDURE Print_Recipients
2374 (errbuf OUT NOCOPY VARCHAR2,
2375 retcode OUT NOCOPY VARCHAR2,
2376 p_recipient_from IN VARCHAR2,
2377 p_recipient_to IN VARCHAR2,
2378 p_item_code_from IN VARCHAR2,
2379 p_item_code_to IN VARCHAR2,
2380 p_changed_after IN VARCHAR2,
2381 p_printer IN VARCHAR2,
2382 p_user_print_style IN VARCHAR2,
2383 p_number_of_copies IN NUMBER,
2384 p_items_to_print IN VARCHAR2,
2385 x_return_status OUT NOCOPY VARCHAR2,
2386 x_msg_count OUT NOCOPY NUMBER,
2387 x_msg_data OUT NOCOPY VARCHAR2)
2388 IS
2389
2390 /*
2391 ** Alpha Variables
2392 */
2393 L_CODE_BLOCK VARCHAR2(2000);
2394 L_RETURN_STATUS VARCHAR2(1);
2395 L_MSG_DATA VARCHAR2(2000);
2396 L_PRINT_COUNT NUMBER(5) DEFAULT 0;
2397
2398 L_LANGUAGE_CODE FND_LANGUAGES.language_code%TYPE;
2399
2400 /*
2401 ** Number Variables
2402 */
2403 L_ORACLE_ERROR NUMBER;
2404
2405 /*
2406 ** Date Variables
2407 */
2408 L_CHANGED_AFTER DATE;
2409
2410 /*
2411 ** Exceptions
2412 */
2413 OTHER_API_ERROR EXCEPTION;
2414
2415 /*
2416 ** Cursors
2417 **
2418 ** Get the recipient range
2419 */
2420 CURSOR c_get_recipient_range
2421 IS
2422 SELECT ri.recipient_code,
2423 ri.recipient_name,
2424 ri.document_code,
2425 ri.document_print_frequency,
2426 ri.disclosure_code,
2427 ri.region_code,
2428 ri.territory_code,
2429 ri.shipping_address,
2430 ri.invoice_address,
2431 ri.additional_address_flag,
2432 cp.language,
2433 cp.document_code country_document
2434 FROM gr_recipient_info ri,
2435 gr_country_profiles cp
2436 WHERE ri.recipient_code >= p_recipient_from
2437 AND ri.recipient_code <= p_recipient_to
2438 AND ri.territory_code = cp.territory_code;
2439 LocalRecipient c_get_recipient_range%ROWTYPE;
2440
2441 /*
2442 ** Get the region language details
2443 */
2444 CURSOR c_get_region_language
2445 IS
2446 SELECT rl.language
2447 FROM gr_region_languages rl
2448 WHERE rl.region_code = LocalRecipient.region_code;
2449 LocalRgnLangRecord c_get_region_language%ROWTYPE;
2450
2451 /*
2452 ** Get the item code
2453 */
2454 CURSOR c_get_item
2455 IS
2456 SELECT ig1.item_code
2457 FROM gr_item_general ig1
2458 WHERE ig1.item_code >= p_item_code_from
2459 AND ig1.item_code <= p_item_code_to
2460 AND (ig1.ingredient_flag = 'N'
2461 OR (ig1.ingredient_flag = 'Y'
2462 AND p_item_code_from = p_item_code_to));
2463 LocalItemRec c_get_item%ROWTYPE;
2464
2465 /*
2466 ** Get the item code and the document.
2467 */
2468 CURSOR c_get_item_range
2469 IS
2470 SELECT ids.item_code,
2471 ids.last_doc_update_date
2472 FROM gr_item_doc_statuses ids
2473 WHERE ids.item_code = LocalItemRec.item_code
2474 AND ids.document_code = g_default_document;
2475 LocalItem c_get_item_range%ROWTYPE;
2476 /*
2477 ** Get the customer address id
2478 */
2479 CURSOR c_get_cust_address
2480 IS
2481 SELECT cu.addr_id
2482 FROM op_cust_mst cu
2483 WHERE cu.cust_no = LocalRecipient.recipient_code;
2484 LocalCustRecord c_get_cust_address%ROWTYPE;
2485 /*
2486 ** Get the inventory items for a specified master item
2487 */
2488 CURSOR c_get_master_list
2489 IS
2490 SELECT gib.item_code,
2491 gib.item_no
2492 FROM gr_generic_items_b gib
2493 WHERE gib.item_code = LocalItem.item_code;
2494 /*
2495 ** Get the master item for a specified inventory item
2496 */
2497 CURSOR c_get_invent_items
2498 IS
2499 SELECT gib.item_code,
2500 gib.item_no
2501 FROM gr_generic_items_b gib
2502 WHERE gib.item_no >= p_item_code_from
2503 AND gib.item_no <= p_item_code_to;
2504 LocalInventList c_get_invent_items%ROWTYPE;
2505
2506 BEGIN
2507 /*
2508 ** Initialize
2509 */
2510 l_code_block := 'Init';
2511 l_changed_after := FND_DATE.CANONICAL_TO_DATE(p_changed_after);
2512 g_report_type := 3;
2513 /*
2514 ** Get the session id and clear print work table
2515 */
2516 SELECT gr_work_build_docs_s.nextval INTO g_session_id
2517 FROM dual;
2518
2519 l_code_block := 'Session id: ' || TO_CHAR(g_session_id);
2520
2521 FND_FILE.PUT(FND_FILE.LOG,l_code_block);
2522 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
2523
2524 l_return_status := 'S';
2525 GR_PROCESS_DOCUMENTS.Clear_Worksheet_Session
2526 ('F',
2527 g_session_id,
2528 l_return_status,
2529 l_oracle_error,
2530 l_msg_data);
2531
2532 IF l_return_status <> 'S' THEN
2533 RAISE Other_API_Error;
2534 END IF;
2535 /*
2536 ** Process the recipient range
2537 */
2538 OPEN c_get_recipient_range;
2539 FETCH c_get_recipient_range INTO LocalRecipient;
2540 IF c_get_recipient_range%FOUND THEN
2541 WHILE c_get_recipient_range%FOUND LOOP
2542 g_recipient_code := LocalRecipient.recipient_code;
2543 IF LocalRecipient.document_code IS NULL THEN
2544 g_default_document := LocalRecipient.country_document;
2545 ELSE
2546 g_default_document := LocalRecipient.document_code;
2547 END IF;
2548
2549 IF p_items_to_print = 'M' OR
2550 p_items_to_print = 'S' OR
2551 p_items_to_print = 'A' THEN
2552 OPEN c_get_item;
2553 FETCH c_get_item INTO LocalItemRec;
2554 IF c_get_item%FOUND THEN
2555 WHILE c_get_item%FOUND LOOP
2556 FND_FILE.PUT(FND_FILE.LOG,' PROCESSING ITEM '||LocalItemRec.item_code);
2557 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
2558 g_doc_item_code := LocalItemRec.item_code;
2559 OPEN c_get_item_range;
2560 FETCH c_get_item_range INTO LocalItem;
2561 IF c_get_item_range%NOTFOUND THEN
2562 FND_FILE.PUT(FND_FILE.LOG,' Document ' || g_default_document || ' not defined for item '||LocalItemRec.item_code);
2563 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
2564 ELSE
2565 IF l_changed_after IS NOT NULL AND
2566 LocalItem.last_doc_update_date <= l_changed_after THEN
2567 l_code_block := 'Do not print';
2568 ELSE
2569 l_code_block := 'Print this recipient ' || LocalRecipient.recipient_code;
2570 FND_FILE.PUT(FND_FILE.LOG,l_code_block);
2571 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
2572 /*
2573 ** Print using the default language if the region code is null.
2574 */
2575 IF LocalRecipient.region_code IS NULL THEN
2576 l_language_code := LocalRecipient.language;
2577 IF LocalRecipient.shipping_address = '1' OR
2578 LocalRecipient.invoice_address = '1' THEN
2579
2580 l_code_block := 'Print the shipping or invoice address';
2581 FND_FILE.PUT(FND_FILE.LOG,l_code_block);
2582 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
2583
2584 OPEN c_get_cust_address;
2585 FETCH c_get_cust_address INTO LocalCustRecord;
2586 IF c_get_cust_address%FOUND THEN
2587
2588 /* Fix for B1270176 */
2589 l_return_status := 'S';
2590 GR_PROCESS_DOCUMENTS.Clear_Worksheet_Session
2591 ('F',
2592 g_session_id,
2593 l_return_status,
2594 l_oracle_error,
2595 l_msg_data);
2596
2597 IF l_return_status <> 'S' THEN
2598 RAISE Other_API_Error;
2599 END IF;
2600
2601 l_return_status := 'S';
2602
2603 Read_And_Print_Cover_Letter
2604 (LocalRecipient.language,
2605 LocalItem.item_code,
2606 LocalRecipient.recipient_code,
2607 'O',
2608 '',
2609 LocalCustRecord.addr_id,
2610 l_return_status);
2611
2612 IF l_return_status <> 'S' THEN
2613 FND_FILE.PUT(FND_FILE.LOG,'Cover Letter error - 1');
2614 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
2615
2616 RAISE Other_API_Error;
2617 END IF;
2618 /*
2619 ** Print the letter and increment the session id
2620 */
2621 l_return_status := 'S';
2622 g_cover_letter := 'Y';
2623 Submit_Print_Request
2624 (p_printer,
2625 p_user_print_style,
2626 p_number_of_copies,
2627 g_default_document,
2628 l_language_code,
2629 l_return_status);
2630
2631 IF l_return_status <> 'S' THEN
2632 FND_FILE.PUT(FND_FILE.LOG,'Submission error for session ' || TO_CHAR(g_session_id));
2633 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
2634 END IF;
2635
2636 /* Fix for B1270176 */
2637 l_return_status := 'S';
2638 GR_PROCESS_DOCUMENTS.Clear_Worksheet_Session
2639 ('F',
2640 g_session_id,
2641 l_return_status,
2642 l_oracle_error,
2643 l_msg_data);
2644
2645 IF l_return_status <> 'S' THEN
2646 RAISE Other_API_Error;
2647 END IF;
2648
2649 l_return_status := 'S';
2650 Print_Document_Selection
2651 (g_default_document,
2652 LocalItem.item_code,
2653 LocalRecipient.language,
2654 LocalRecipient.disclosure_code,
2655 l_return_status);
2656 IF l_return_status <> 'S' THEN
2657 FND_FILE.PUT(FND_FILE.LOG,'Document print error - A');
2658 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
2659
2660 RAISE Other_API_Error;
2661 ELSE
2662 l_print_count := l_print_count + 1;
2663 END IF;
2664 /*
2665 ** Print the document and increment the session id
2666 */
2667 l_return_status := 'S';
2668 g_cover_letter := 'N';
2669 Submit_Print_Request
2670 (p_printer,
2671 p_user_print_style,
2672 p_number_of_copies,
2673 g_default_document,
2674 l_language_code,
2675 l_return_status);
2676
2677 IF l_return_status <> 'S' THEN
2678 FND_FILE.PUT(FND_FILE.LOG,'Submission error for session ' || TO_CHAR(g_session_id));
2679 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
2680 END IF;
2681 /*
2682 ** Process inventory items if print all
2683 */
2684 IF p_items_to_print = 'A' THEN
2685 l_code_block := 'Process generics for ' || LocalItem.item_code;
2686 OPEN c_get_master_list;
2687 FETCH c_get_master_list INTO LocalInventList;
2688 IF c_get_master_list%FOUND THEN
2689 WHILE c_get_master_list%FOUND LOOP
2690 g_doc_item_code := LocalInventList.item_no;
2691 /* Fix for B1270176 */
2692 l_return_status := 'S';
2693 GR_PROCESS_DOCUMENTS.Clear_Worksheet_Session
2694 ('F',
2695 g_session_id,
2696 l_return_status,
2697 l_oracle_error,
2698 l_msg_data);
2699
2700 IF l_return_status <> 'S' THEN
2701 RAISE Other_API_Error;
2702 END IF;
2703
2704 l_return_status := 'S';
2705 Print_Document_Selection
2706 (g_default_document,
2707 LocalInventList.item_no,
2708 LocalRecipient.language,
2709 LocalRecipient.disclosure_code,
2710 l_return_status);
2711 IF l_return_status <> 'S' THEN
2712 FND_FILE.PUT(FND_FILE.LOG,'Document print error - B');
2713 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
2714
2715 RAISE Other_API_Error;
2716 ELSE
2717 l_print_count := l_print_count + 1;
2718 END IF;
2719 /*
2720 ** Print the document and increment the session id
2721 */
2722 l_return_status := 'S';
2723 g_cover_letter := 'N';
2724 Submit_Print_Request
2725 (p_printer,
2726 p_user_print_style,
2727 p_number_of_copies,
2728 g_default_document,
2729 l_language_code,
2730 l_return_status);
2731
2732 IF l_return_status <> 'S' THEN
2733 FND_FILE.PUT(FND_FILE.LOG,'Submission error for session ' || TO_CHAR(g_session_id));
2734 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
2735 END IF;
2736 FETCH c_get_master_list INTO LocalInventList;
2737 END LOOP;
2738 END IF;
2739 /* Fix for B1270176 */
2740 CLOSE c_get_master_list;
2741 END IF;
2742 END IF;
2743 CLOSE c_get_cust_address;
2744 END IF;
2745
2746 IF LocalRecipient.additional_address_flag = '1' THEN
2747 OPEN g_get_other_addresses;
2748 FETCH g_get_other_addresses INTO GlobalOtherAddrRecord;
2749 IF g_get_other_addresses%FOUND THEN
2750 WHILE g_get_other_addresses%FOUND LOOP
2751
2752 l_code_block := 'Other Addresses ';
2753 FND_FILE.PUT(FND_FILE.LOG,l_code_block);
2754 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
2755
2756 /* Fix for B1270176 */
2757 l_return_status := 'S';
2758 GR_PROCESS_DOCUMENTS.Clear_Worksheet_Session
2759 ('F',
2760 g_session_id,
2761 l_return_status,
2762 l_oracle_error,
2763 l_msg_data);
2764 IF l_return_status <> 'S' THEN
2765 RAISE Other_API_Error;
2766 END IF;
2767
2768 l_return_status := 'S';
2769 Read_And_Print_Cover_Letter
2770 (LocalRecipient.language,
2771 LocalItem.item_code,
2772 LocalRecipient.recipient_code,
2773 'O',
2774 '',
2775 GlobalOtherAddrRecord.addr_id,
2776 l_return_status);
2777 IF l_return_status <> 'S' THEN
2778 FND_FILE.PUT(FND_FILE.LOG,'Cover Letter error - 2');
2779 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
2780
2781 RAISE Other_API_Error;
2782 END IF;
2783 /*
2784 ** Print the letter and increment the session id
2785 */
2786 l_return_status := 'S';
2787 g_cover_letter := 'Y';
2788 Submit_Print_Request
2789 (p_printer,
2790 p_user_print_style,
2791 p_number_of_copies,
2792 g_default_document,
2793 l_language_code,
2794 l_return_status);
2795
2796 IF l_return_status <> 'S' THEN
2797 FND_FILE.PUT(FND_FILE.LOG,'Submission error for session ' || TO_CHAR(g_session_id));
2798 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
2799 END IF;
2800
2801 /* Fix for B1270176*/
2802 l_return_status := 'S';
2803 GR_PROCESS_DOCUMENTS.Clear_Worksheet_Session
2804 ('F',
2805 g_session_id,
2806 l_return_status,
2807 l_oracle_error,
2808 l_msg_data);
2809
2810 IF l_return_status <> 'S' THEN
2811 RAISE Other_API_Error;
2812 END IF;
2813
2814 l_return_status := 'S';
2815 Print_Document_Selection
2816 (g_default_document,
2817 LocalItem.item_code,
2818 LocalRecipient.language,
2819 LocalRecipient.disclosure_code,
2820 l_return_status);
2821 IF l_return_status <> 'S' THEN
2822 FND_FILE.PUT(FND_FILE.LOG,'Document print error - C');
2823 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
2824
2825 RAISE Other_API_Error;
2826 ELSE
2827 l_print_count := l_print_count + 1;
2828 END IF;
2829 /*
2830 ** Print the document and increment the session id
2831 */
2832 l_return_status := 'S';
2833 g_cover_letter := 'N';
2834 Submit_Print_Request
2835 (p_printer,
2836 p_user_print_style,
2837 p_number_of_copies,
2838 g_default_document,
2839 l_language_code,
2840 l_return_status);
2841
2842 IF l_return_status <> 'S' THEN
2843 FND_FILE.PUT(FND_FILE.LOG,'Submission error for session ' || TO_CHAR(g_session_id));
2844 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
2845 END IF;
2846 /*
2847 ** Process inventory items if print all
2848 */
2849 IF p_items_to_print = 'A' THEN
2850 l_code_block := 'Process generics for ' || LocalItem.item_code;
2851 OPEN c_get_master_list;
2852 FETCH c_get_master_list INTO LocalInventList;
2853 IF c_get_master_list%FOUND THEN
2854 WHILE c_get_master_list%FOUND LOOP
2855 g_doc_item_code := LocalInventList.item_no;
2856 /* Fix for B1270176 */
2857 l_return_status := 'S';
2858 GR_PROCESS_DOCUMENTS.Clear_Worksheet_Session
2859 ('F',
2860 g_session_id,
2861 l_return_status,
2862 l_oracle_error,
2863 l_msg_data);
2864
2865 IF l_return_status <> 'S' THEN
2866 RAISE Other_API_Error;
2867 END IF;
2868 l_return_status := 'S';
2869 Print_Document_Selection
2870 (g_default_document,
2871 LocalInventList.item_no,
2872 LocalRecipient.language,
2873 LocalRecipient.disclosure_code,
2874 l_return_status);
2875 IF l_return_status <> 'S' THEN
2876 FND_FILE.PUT(FND_FILE.LOG,'Document print error - D');
2877 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
2878
2879 RAISE Other_API_Error;
2880 ELSE
2881 l_print_count := l_print_count + 1;
2882 END IF;
2883 /*
2884 ** Print the document and increment the session id
2885 */
2886 l_return_status := 'S';
2887 g_cover_letter := 'N';
2888 Submit_Print_Request
2889 (p_printer,
2890 p_user_print_style,
2891 p_number_of_copies,
2892 g_default_document,
2893 l_language_code,
2894 l_return_status);
2895
2896 IF l_return_status <> 'S' THEN
2897 FND_FILE.PUT(FND_FILE.LOG,'Submission error for session ' || TO_CHAR(g_session_id));
2898 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
2899 END IF;
2900 FETCH c_get_master_list INTO LocalInventList;
2901 END LOOP;
2902 END IF;
2903 /* Fix for B1270176 */
2904 CLOSE c_get_master_list;
2905 END IF;
2906 FETCH g_get_other_addresses INTO GlobalOtherAddrRecord;
2907 END LOOP;
2908 END IF;
2909 CLOSE g_get_other_addresses;
2910 END IF;
2911 ELSE
2912 /*
2913 ** Print using the languages for the region
2914 */
2915 l_code_block := 'Print for regions';
2916 FND_FILE.PUT(FND_FILE.LOG,l_code_block);
2917 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
2918
2919 OPEN c_get_region_language;
2920 FETCH c_get_region_language INTO LocalRgnLangRecord;
2921 IF c_get_region_language%FOUND THEN
2922 WHILE c_get_region_language%FOUND LOOP
2923 l_language_code := LocalRgnLangRecord.language;
2924 IF LocalRecipient.shipping_address = '1' OR
2925 LocalRecipient.invoice_address = '1' THEN
2926 OPEN c_get_cust_address;
2927 FETCH c_get_cust_address INTO LocalCustRecord;
2928 IF c_get_cust_address%FOUND THEN
2929
2930 /* Fix for B1270176 */
2931 l_return_status := 'S';
2932 GR_PROCESS_DOCUMENTS.Clear_Worksheet_Session
2933 ('F',
2934 g_session_id,
2935 l_return_status,
2936 l_oracle_error,
2937 l_msg_data);
2938
2939 IF l_return_status <> 'S' THEN
2940 RAISE Other_API_Error;
2941 END IF;
2942
2943 l_return_status := 'S';
2944 Read_And_Print_Cover_Letter
2945 (l_language_code,
2946 LocalItem.item_code,
2947 LocalRecipient.recipient_code,
2948 'O',
2949 '',
2950 LocalCustRecord.addr_id,
2951 l_return_status);
2952 IF l_return_status <> 'S' THEN
2953 FND_FILE.PUT(FND_FILE.LOG,'Cover Letter error - 3');
2954 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
2955
2956 RAISE Other_API_Error;
2957 END IF;
2958 /*
2959 ** Print the letter and increment the session id
2960 */
2961 l_return_status := 'S';
2962 g_cover_letter := 'Y';
2963 Submit_Print_Request
2964 (p_printer,
2965 p_user_print_style,
2966 p_number_of_copies,
2967 g_default_document,
2968 l_language_code,
2969 l_return_status);
2970
2971 IF l_return_status <> 'S' THEN
2972 FND_FILE.PUT(FND_FILE.LOG,'Submission error for session ' || TO_CHAR(g_session_id));
2973 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
2974 END IF;
2975
2976 /* Fix for B1270176 */
2977 l_return_status := 'S';
2978 GR_PROCESS_DOCUMENTS.Clear_Worksheet_Session
2979 ('F',
2980 g_session_id,
2981 l_return_status,
2982 l_oracle_error,
2983 l_msg_data);
2984 IF l_return_status <> 'S' THEN
2985 RAISE Other_API_Error;
2986 END IF;
2987
2988 l_return_status := 'S';
2989 Print_Document_Selection
2990 (g_default_document,
2991 LocalItem.item_code,
2992 l_language_code,
2993 LocalRecipient.disclosure_code,
2994 l_return_status);
2995 IF l_return_status <> 'S' THEN
2996 FND_FILE.PUT(FND_FILE.LOG,'Document print error - E');
2997 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
2998
2999 RAISE Other_API_Error;
3000 ELSE
3001 l_print_count := l_print_count + 1;
3002 END IF;
3003 /*
3004 ** Print the document and increment the session id
3005 */
3006 l_return_status := 'S';
3007 g_cover_letter := 'N';
3008 Submit_Print_Request
3009 (p_printer,
3010 p_user_print_style,
3011 p_number_of_copies,
3012 g_default_document,
3013 l_language_code,
3014 l_return_status);
3015
3016 IF l_return_status <> 'S' THEN
3017 FND_FILE.PUT(FND_FILE.LOG,'Submission error for session ' || TO_CHAR(g_session_id));
3018 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
3019 END IF;
3020 /*
3021 ** Process inventory items if print all
3022 */
3023 IF p_items_to_print = 'A' THEN
3024 l_code_block := 'Process generics for ' || LocalItem.item_code;
3025 OPEN c_get_master_list;
3026 FETCH c_get_master_list INTO LocalInventList;
3027 IF c_get_master_list%FOUND THEN
3028 WHILE c_get_master_list%FOUND LOOP
3029 g_doc_item_code := LocalInventList.item_no;
3030 /* Fix for B1270176 */
3031 l_return_status := 'S';
3032 GR_PROCESS_DOCUMENTS.Clear_Worksheet_Session
3033 ('F',
3034 g_session_id,
3035 l_return_status,
3036 l_oracle_error,
3037 l_msg_data);
3038
3039 IF l_return_status <> 'S' THEN
3040 RAISE Other_API_Error;
3041 END IF;
3042
3043 l_return_status := 'S';
3044 Print_Document_Selection
3045 (g_default_document,
3046 LocalInventList.item_no,
3047 LocalRecipient.language,
3048 LocalRecipient.disclosure_code,
3049 l_return_status);
3050 IF l_return_status <> 'S' THEN
3051 FND_FILE.PUT(FND_FILE.LOG,'Document print error - F');
3052 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
3053
3054 RAISE Other_API_Error;
3055 ELSE
3056 l_print_count := l_print_count + 1;
3057 END IF;
3058 /*
3059 ** Print the document and increment the session id
3060 */
3061 l_return_status := 'S';
3062 g_cover_letter := 'N';
3063 Submit_Print_Request
3064 (p_printer,
3065 p_user_print_style,
3066 p_number_of_copies,
3067 g_default_document,
3068 l_language_code,
3069 l_return_status);
3070
3071 IF l_return_status <> 'S' THEN
3072 FND_FILE.PUT(FND_FILE.LOG,'Submission error for session ' || TO_CHAR(g_session_id));
3073 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
3074 END IF;
3075 FETCH c_get_master_list INTO LocalInventList;
3076 END LOOP;
3077 END IF;
3078 /* Fix for B1270176 */
3079 CLOSE c_get_master_list;
3080 END IF;
3081 END IF;
3082 CLOSE c_get_cust_address;
3083 END IF;
3084
3085 IF LocalRecipient.additional_address_flag = '1' THEN
3086 OPEN g_get_other_addresses;
3087 FETCH g_get_other_addresses INTO GlobalOtherAddrRecord;
3088 IF g_get_other_addresses%FOUND THEN
3089 WHILE g_get_other_addresses%FOUND LOOP
3090
3091 /* Fix for B1270176 */
3092 l_return_status := 'S';
3093 GR_PROCESS_DOCUMENTS.Clear_Worksheet_Session
3094 ('F',
3095 g_session_id,
3096 l_return_status,
3097 l_oracle_error,
3098 l_msg_data);
3099
3100 IF l_return_status <> 'S' THEN
3101 RAISE Other_API_Error;
3102 END IF;
3103
3104 l_return_status := 'S';
3105 Read_And_Print_Cover_Letter
3106 (l_language_code,
3107 LocalItem.item_code,
3108 LocalRecipient.recipient_code,
3109 'O',
3110 '',
3111 GlobalOtherAddrRecord.addr_id,
3112 l_return_status);
3113 IF l_return_status <> 'S' THEN
3114 FND_FILE.PUT(FND_FILE.LOG,'Cover Letter error - 4');
3115 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
3116
3117 RAISE Other_API_Error;
3118 END IF;
3119 /*
3120 ** Print the letter and increment the session id
3121 */
3122 l_return_status := 'S';
3123 g_cover_letter := 'Y';
3124 Submit_Print_Request
3125 (p_printer,
3126 p_user_print_style,
3127 p_number_of_copies,
3128 g_default_document,
3129 l_language_code,
3130 l_return_status);
3131
3132 IF l_return_status <> 'S' THEN
3133 FND_FILE.PUT(FND_FILE.LOG,'Submission error for session ' || TO_CHAR(g_session_id));
3134 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
3135 END IF;
3136
3137 /* Fix for B1270176 */
3138 l_return_status := 'S';
3139 GR_PROCESS_DOCUMENTS.Clear_Worksheet_Session
3140 ('F',
3141 g_session_id,
3142 l_return_status,
3143 l_oracle_error,
3144 l_msg_data);
3145
3146 IF l_return_status <> 'S' THEN
3147 RAISE Other_API_Error;
3148 END IF;
3149
3150 l_return_status := 'S';
3151 Print_Document_Selection
3152 (g_default_document,
3153 LocalItem.item_code,
3154 l_language_code,
3155 LocalRecipient.disclosure_code,
3156 l_return_status);
3157 IF l_return_status <> 'S' THEN
3158 FND_FILE.PUT(FND_FILE.LOG,'Document print error - G');
3159 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
3160
3161 RAISE Other_API_Error;
3162 ELSE
3163 l_print_count := l_print_count + 1;
3164 END IF;
3165 /*
3166 ** Print the document and increment the session id
3167 */
3168 l_return_status := 'S';
3169 g_cover_letter := 'N';
3170 Submit_Print_Request
3171 (p_printer,
3172 p_user_print_style,
3173 p_number_of_copies,
3174 g_default_document,
3175 l_language_code,
3176 l_return_status);
3177
3178 IF l_return_status <> 'S' THEN
3179 FND_FILE.PUT(FND_FILE.LOG,'Submission error for session ' || TO_CHAR(g_session_id));
3180 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
3181 END IF;
3182 /*
3183 ** Process inventory items if print all
3184 */
3185 IF p_items_to_print = 'A' THEN
3186 l_code_block := 'Process generics for ' || LocalItem.item_code;
3187 OPEN c_get_master_list;
3188 FETCH c_get_master_list INTO LocalInventList;
3189 IF c_get_master_list%FOUND THEN
3190 WHILE c_get_master_list%FOUND LOOP
3191 g_doc_item_code := LocalInventList.item_no;
3192 /* Fix for B1270176 */
3193 l_return_status := 'S';
3194 GR_PROCESS_DOCUMENTS.Clear_Worksheet_Session
3195 ('F',
3196 g_session_id,
3197 l_return_status,
3198 l_oracle_error,
3199 l_msg_data);
3200
3201 IF l_return_status <> 'S' THEN
3202 RAISE Other_API_Error;
3203 END IF;
3204
3205 l_return_status := 'S';
3206 Print_Document_Selection
3207 (g_default_document,
3208 LocalInventList.item_no,
3209 LocalRecipient.language,
3210 LocalRecipient.disclosure_code,
3211 l_return_status);
3212 IF l_return_status <> 'S' THEN
3213 FND_FILE.PUT(FND_FILE.LOG,'Document print error - H');
3214 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
3215
3216 RAISE Other_API_Error;
3217 ELSE
3218 l_print_count := l_print_count + 1;
3219 END IF;
3220 /*
3221 ** Print the document and increment the session id
3222 */
3223 l_return_status := 'S';
3224 g_cover_letter := 'N';
3225 Submit_Print_Request
3226 (p_printer,
3227 p_user_print_style,
3228 p_number_of_copies,
3229 g_default_document,
3230 l_language_code,
3231 l_return_status);
3232
3233 IF l_return_status <> 'S' THEN
3234 FND_FILE.PUT(FND_FILE.LOG,'Submission error for session ' || TO_CHAR(g_session_id));
3235 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
3236 END IF;
3237 FETCH c_get_master_list INTO LocalInventList;
3238 END LOOP;
3239 END IF;
3240 /* Fix for B1270176 */
3241 CLOSE c_get_master_list;
3242 END IF;
3243 FETCH g_get_other_addresses INTO GlobalOtherAddrRecord;
3244 END LOOP;
3245 END IF;
3246 CLOSE g_get_other_addresses;
3247 END IF;
3248 FETCH c_get_region_language INTO LocalRgnLangRecord;
3249 END LOOP;
3250 END IF;
3251 CLOSE c_get_region_language;
3252 END IF;
3253 END IF;
3254 END IF;
3255 CLOSE c_get_item_range;
3256 FETCH c_get_item INTO LocalItemRec;
3257 END LOOP;
3258 END IF;
3259 /* Fix for B1270176 */
3260 CLOSE c_get_item;
3261 END IF;
3262 /*
3263 ** Print only the inventory items linked to the master
3264 ** Also print if the option for a single item and there was
3265 ** no processing of a master item.
3266 */
3267 IF p_items_to_print = 'I' OR
3268 (p_items_to_print = 'S' AND
3269 l_print_count = 0) THEN
3270
3271 l_code_block := 'Process generic items';
3272 FND_FILE.PUT(FND_FILE.LOG,l_code_block);
3273 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
3274
3275 OPEN c_get_invent_items;
3276 FETCH c_get_invent_items INTO LocalInventList;
3277 IF c_get_invent_items%FOUND THEN
3278 WHILE c_get_invent_items%FOUND LOOP
3279 g_doc_item_code := LocalInventList.item_no;
3280 IF LocalRecipient.region_code IS NULL THEN
3281 l_language_code := LocalRecipient.language;
3282 IF LocalRecipient.shipping_address = '1' OR
3283 LocalRecipient.invoice_address = '1' THEN
3284 OPEN c_get_cust_address;
3285 FETCH c_get_cust_address INTO LocalCustRecord;
3286 IF c_get_cust_address%FOUND THEN
3287
3288 /* Fix for B1270176 */
3289 l_return_status := 'S';
3290 GR_PROCESS_DOCUMENTS.Clear_Worksheet_Session
3291 ('F',
3292 g_session_id,
3293 l_return_status,
3294 l_oracle_error,
3295 l_msg_data);
3296
3297 IF l_return_status <> 'S' THEN
3298 RAISE Other_API_Error;
3299 END IF;
3300
3301 l_return_status := 'S';
3302 Read_And_Print_Cover_Letter
3303 (l_language_code,
3304 LocalInventList.item_no,
3305 LocalRecipient.recipient_code,
3306 'O',
3307 '',
3308 LocalCustRecord.addr_id,
3309 l_return_status);
3310 IF l_return_status <> 'S' THEN
3311 FND_FILE.PUT(FND_FILE.LOG,'Cover Letter error');
3312 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
3313
3314 RAISE Other_API_Error;
3315 END IF;
3316 /*
3317 ** Print the letter and increment the session id
3318 */
3319 l_return_status := 'S';
3320 g_cover_letter := 'Y';
3321 Submit_Print_Request
3322 (p_printer,
3323 p_user_print_style,
3324 p_number_of_copies,
3325 g_default_document,
3326 l_language_code,
3327 l_return_status);
3328
3329 IF l_return_status <> 'S' THEN
3330 FND_FILE.PUT(FND_FILE.LOG,'Submission error for session ' || TO_CHAR(g_session_id));
3331 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
3332 END IF;
3333
3334 /* Fix for B1270176 */
3335 l_return_status := 'S';
3336 GR_PROCESS_DOCUMENTS.Clear_Worksheet_Session
3337 ('F',
3338 g_session_id,
3339 l_return_status,
3340 l_oracle_error,
3341 l_msg_data);
3342
3343 IF l_return_status <> 'S' THEN
3344 RAISE Other_API_Error;
3345 END IF;
3346
3347 l_return_status := 'S';
3348 Print_Document_Selection
3349 (g_default_document,
3350 LocalInventList.item_no,
3351 l_language_code,
3352 LocalRecipient.disclosure_code,
3353 l_return_status);
3354 IF l_return_status <> 'S' THEN
3355 FND_FILE.PUT(FND_FILE.LOG,'Document print error - I');
3356 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
3357
3358 RAISE Other_API_Error;
3359 END IF;
3360 /*
3361 ** Print the document and increment the session id
3362 */
3363 l_return_status := 'S';
3364 g_cover_letter := 'N';
3365 Submit_Print_Request
3366 (p_printer,
3367 p_user_print_style,
3368 p_number_of_copies,
3369 g_default_document,
3370 l_language_code,
3371 l_return_status);
3372
3373 IF l_return_status <> 'S' THEN
3374 FND_FILE.PUT(FND_FILE.LOG,'Submission error for session ' || TO_CHAR(g_session_id));
3375 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
3376 END IF;
3377 ELSE
3378 FND_FILE.PUT(FND_FILE.LOG,'No inv/ship address for ' || LocalRecipient.recipient_code);
3379 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
3380 END IF;
3381 CLOSE c_get_cust_address;
3382 END IF;
3383 /*
3384 ** Process any other addresses
3385 */
3386 IF LocalRecipient.additional_address_flag = '1' THEN
3387 OPEN g_get_other_addresses;
3388 FETCH g_get_other_addresses INTO GlobalOtherAddrRecord;
3389 IF g_get_other_addresses%FOUND THEN
3390 WHILE g_get_other_addresses%FOUND LOOP
3391
3392 /* Fix for B1270176 */
3393 l_return_status := 'S';
3394 GR_PROCESS_DOCUMENTS.Clear_Worksheet_Session
3395 ('F',
3396 g_session_id,
3397 l_return_status,
3398 l_oracle_error,
3399 l_msg_data);
3400
3401 IF l_return_status <> 'S' THEN
3402 RAISE Other_API_Error;
3403 END IF;
3404
3405 l_return_status := 'S';
3406 Read_And_Print_Cover_Letter
3407 (l_language_code,
3408 LocalInventList.item_no,
3409 LocalRecipient.recipient_code,
3410 'O',
3411 '',
3412 LocalCustRecord.addr_id,
3413 l_return_status);
3414 IF l_return_status <> 'S' THEN
3415 FND_FILE.PUT(FND_FILE.LOG,'Cover Letter error');
3416 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
3417
3418 RAISE Other_API_Error;
3419 END IF;
3420 /*
3421 ** Print the letter and increment the session id
3422 */
3423 l_return_status := 'S';
3424 g_cover_letter := 'Y';
3425 Submit_Print_Request
3426 (p_printer,
3427 p_user_print_style,
3428 p_number_of_copies,
3429 g_default_document,
3430 l_language_code,
3431 l_return_status);
3432
3433 IF l_return_status <> 'S' THEN
3434 FND_FILE.PUT(FND_FILE.LOG,'Submission error for session ' || TO_CHAR(g_session_id));
3435 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
3436 END IF;
3437
3438 /* Fix for B1270176 */
3439 l_return_status := 'S';
3440 GR_PROCESS_DOCUMENTS.Clear_Worksheet_Session
3441 ('F',
3442 g_session_id,
3443 l_return_status,
3444 l_oracle_error,
3445 l_msg_data);
3446
3447 IF l_return_status <> 'S' THEN
3448 RAISE Other_API_Error;
3449 END IF;
3450
3451 l_return_status := 'S';
3452 Print_Document_Selection
3453 (g_default_document,
3454 LocalInventList.item_no,
3455 l_language_code,
3456 LocalRecipient.disclosure_code,
3457 l_return_status);
3458 IF l_return_status <> 'S' THEN
3459 FND_FILE.PUT(FND_FILE.LOG,'Document print error - J');
3460 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
3461
3462 RAISE Other_API_Error;
3463 END IF;
3464 /*
3465 ** Print the document and increment the session id
3466 */
3467 l_return_status := 'S';
3468 g_cover_letter := 'N';
3469 Submit_Print_Request
3470 (p_printer,
3471 p_user_print_style,
3472 p_number_of_copies,
3473 g_default_document,
3474 l_language_code,
3475 l_return_status);
3476
3477 IF l_return_status <> 'S' THEN
3478 FND_FILE.PUT(FND_FILE.LOG,'Submission error for session ' || TO_CHAR(g_session_id));
3479 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
3480 END IF;
3481 FETCH g_get_other_addresses INTO GlobalOtherAddrRecord;
3482 END LOOP;
3483 END IF;
3484 CLOSE g_get_other_addresses;
3485 END IF;
3486 ELSE
3487 /*
3488 ** Print for regions
3489 */
3490 OPEN c_get_region_language;
3491 FETCH c_get_region_language INTO LocalRgnLangRecord;
3492 IF c_get_region_language%FOUND THEN
3493 WHILE c_get_region_language%FOUND LOOP
3494 l_language_code := LocalRgnLangRecord.language;
3495 IF LocalRecipient.shipping_address = '1' OR
3496 LocalRecipient.invoice_address = '1' THEN
3497 OPEN c_get_cust_address;
3498 FETCH c_get_cust_address INTO LocalCustRecord;
3499 IF c_get_cust_address%FOUND THEN
3500
3501 /* Fix for B1270176 */
3502 l_return_status := 'S';
3503 GR_PROCESS_DOCUMENTS.Clear_Worksheet_Session
3504 ('F',
3505 g_session_id,
3506 l_return_status,
3507 l_oracle_error,
3508 l_msg_data);
3509
3510 IF l_return_status <> 'S' THEN
3511 RAISE Other_API_Error;
3512 END IF;
3513
3514 l_return_status := 'S';
3515 Read_And_Print_Cover_Letter
3516 (l_language_code,
3517 LocalInventList.item_no,
3518 LocalRecipient.recipient_code,
3519 'O',
3520 '',
3521 LocalCustRecord.addr_id,
3522 l_return_status);
3523 IF l_return_status <> 'S' THEN
3524 FND_FILE.PUT(FND_FILE.LOG,'Cover Letter error');
3525 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
3526
3527 RAISE Other_API_Error;
3528 END IF;
3529 /*
3530 ** Print the letter and increment the session id
3531 */
3532 l_return_status := 'S';
3533 g_cover_letter := 'Y';
3534 Submit_Print_Request
3535 (p_printer,
3536 p_user_print_style,
3537 p_number_of_copies,
3538 g_default_document,
3539 l_language_code,
3540 l_return_status);
3541
3542 IF l_return_status <> 'S' THEN
3543 FND_FILE.PUT(FND_FILE.LOG,'Submission error for session ' || TO_CHAR(g_session_id));
3544 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
3545 END IF;
3546
3547 /* Fix for B1270176 */
3548 l_return_status := 'S';
3549 GR_PROCESS_DOCUMENTS.Clear_Worksheet_Session
3550 ('F',
3551 g_session_id,
3552 l_return_status,
3553 l_oracle_error,
3554 l_msg_data);
3555
3556 IF l_return_status <> 'S' THEN
3557 RAISE Other_API_Error;
3558 END IF;
3559
3560 l_return_status := 'S';
3561 Print_Document_Selection
3562 (g_default_document,
3563 LocalInventList.item_no,
3564 l_language_code,
3565 LocalRecipient.disclosure_code,
3566 l_return_status);
3567 IF l_return_status <> 'S' THEN
3568 RAISE Other_API_Error;
3569 END IF;
3570 /*
3571 ** Print the document and increment the session id
3572 */
3573 l_return_status := 'S';
3574 g_cover_letter := 'N';
3575 Submit_Print_Request
3576 (p_printer,
3577 p_user_print_style,
3578 p_number_of_copies,
3579 g_default_document,
3580 l_language_code,
3581 l_return_status);
3582
3583 IF l_return_status <> 'S' THEN
3584 FND_FILE.PUT(FND_FILE.LOG,'Submission error for session ' || TO_CHAR(g_session_id));
3585 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
3586 END IF;
3587 ELSE
3588 FND_FILE.PUT(FND_FILE.LOG,'No inv/ship address for ' || LocalRecipient.recipient_code);
3589 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
3590 END IF;
3591 CLOSE c_get_cust_address;
3592 END IF;
3593 /*
3594 ** Process any other addresses
3595 */
3596 IF LocalRecipient.additional_address_flag = '1' THEN
3597 OPEN g_get_other_addresses;
3598 FETCH g_get_other_addresses INTO GlobalOtherAddrRecord;
3599 IF g_get_other_addresses%FOUND THEN
3600 WHILE g_get_other_addresses%FOUND LOOP
3601
3602 /* Fix for B1270176 */
3603 l_return_status := 'S';
3604 GR_PROCESS_DOCUMENTS.Clear_Worksheet_Session
3605 ('F',
3606 g_session_id,
3607 l_return_status,
3608 l_oracle_error,
3609 l_msg_data);
3610
3611 IF l_return_status <> 'S' THEN
3612 RAISE Other_API_Error;
3613 END IF;
3614
3615 l_return_status := 'S';
3616 Read_And_Print_Cover_Letter
3617 (l_language_code,
3618 LocalInventList.item_no,
3619 LocalRecipient.recipient_code,
3620 'O',
3621 '',
3622 LocalCustRecord.addr_id,
3623 l_return_status);
3624 IF l_return_status <> 'S' THEN
3625 FND_FILE.PUT(FND_FILE.LOG,'Cover Letter error');
3626 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
3627
3628 RAISE Other_API_Error;
3629 END IF;
3630 /*
3631 ** Print the letter and increment the session id
3632 */
3633 l_return_status := 'S';
3634 g_cover_letter := 'Y';
3635 Submit_Print_Request
3636 (p_printer,
3637 p_user_print_style,
3638 p_number_of_copies,
3639 g_default_document,
3640 l_language_code,
3641 l_return_status);
3642
3643 IF l_return_status <> 'S' THEN
3644 FND_FILE.PUT(FND_FILE.LOG,'Submission error for session ' || TO_CHAR(g_session_id));
3645 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
3646 END IF;
3647
3648 /* Fix for B1270176 */
3649 l_return_status := 'S';
3650 GR_PROCESS_DOCUMENTS.Clear_Worksheet_Session
3651 ('F',
3652 g_session_id,
3653 l_return_status,
3654 l_oracle_error,
3655 l_msg_data);
3656
3657 IF l_return_status <> 'S' THEN
3658 RAISE Other_API_Error;
3659 END IF;
3660
3661 l_return_status := 'S';
3662 Print_Document_Selection
3663 (g_default_document,
3664 LocalInventList.item_no,
3665 l_language_code,
3666 LocalRecipient.disclosure_code,
3667 l_return_status);
3668 IF l_return_status <> 'S' THEN
3669 RAISE Other_API_Error;
3670 END IF;
3671 /*
3672 ** Print the document and increment the session id
3673 */
3674 l_return_status := 'S';
3675 g_cover_letter := 'N';
3676
3677 Submit_Print_Request
3678 (p_printer,
3679 p_user_print_style,
3680 p_number_of_copies,
3681 g_default_document,
3682 l_language_code,
3683 l_return_status);
3684
3685 IF l_return_status <> 'S' THEN
3686 FND_FILE.PUT(FND_FILE.LOG,'Submission error for session ' || TO_CHAR(g_session_id));
3687 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
3688 END IF;
3689 FETCH g_get_other_addresses INTO GlobalOtherAddrRecord;
3690 END LOOP;
3691 END IF;
3692 CLOSE g_get_other_addresses;
3693 END IF;
3694 FETCH c_get_region_language INTO LocalRgnLangRecord;
3695 END LOOP;
3696 END IF;
3697 CLOSE c_get_region_language;
3698 END IF;
3699 FETCH c_get_invent_items INTO LocalInventList;
3700 END LOOP;
3701 END IF;
3702 CLOSE c_get_invent_items;
3703 END IF;
3704 FETCH c_get_recipient_range INTO LocalRecipient;
3705 END LOOP;
3706 END IF;
3707 CLOSE c_get_recipient_range;
3708 /*
3709 ** Now submit the print job.
3710 *
3711 IF p_number_of_copies > 0 THEN
3712 g_print_status := FND_REQUEST.SET_PRINT_OPTIONS
3713 (p_printer,
3714 p_user_print_style,
3715 p_number_of_copies, TRUE, 'N');
3716 END IF;
3717
3718 g_concurrent_id := FND_REQUEST.SUBMIT_REQUEST
3719 ('GR', 'GRRPT030_DOC', '', '', FALSE, g_session_id,
3720 -- Bug #1673690 (JKB)
3721 g_default_document, l_language_code, CHR(0),
3722 '', '', '', '', '', '',
3723 '', '', '', '', '', '', '', '', '', '',
3724 '', '', '', '', '', '', '', '', '', '',
3725 '', '', '', '', '', '', '', '', '', '',
3726 '', '', '', '', '', '', '', '', '', '',
3727 '', '', '', '', '', '', '', '', '', '',
3728 '', '', '', '', '', '', '', '', '', '',
3729 '', '', '', '', '', '', '', '', '', '',
3730 '', '', '', '', '', '', '', '', '', '',
3731 '', '', '', '', '', '', '', '', '', '');
3732 */
3733 EXCEPTION
3734
3735 WHEN Other_API_Error THEN
3736 Handle_Error_Messages
3737 ('GR_UNEXPECTED_ERROR',
3738 'TEXT',
3739 l_msg_data,
3740 x_msg_count,
3741 x_msg_data,
3742 x_return_status);
3743
3744 WHEN OTHERS THEN
3745 FND_FILE.PUT(FND_FILE.LOG,'Unhandled Exception : ' || sqlerrm);
3746 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
3747 l_oracle_error := SQLCODE;
3748 /*l_code_block := SUBSTR(SQLERRM, 1, 200);*/
3749 l_code_block := l_code_block || ' ' || TO_CHAR(l_oracle_error);
3750 FND_MESSAGE.SET_NAME('GR',
3751 'GR_UNEXPECTED_ERROR');
3752 FND_MESSAGE.SET_TOKEN('TEXT',
3753 l_code_block||sqlerrm,
3754 FALSE);
3755
3756 END Print_Recipients;
3757 /*
3758 **
3759 **
3760 **
3761 */
3762 PROCEDURE Insert_Selection_Row
3763 (p_message_code IN VARCHAR2,
3764 p_token_name IN VARCHAR2,
3765 p_token_value IN VARCHAR2,
3766 p_order_id IN NUMBER,
3767 p_order_line_number IN NUMBER,
3768 p_document_code IN VARCHAR2,
3769 p_print_flag IN VARCHAR2,
3770 p_cust_no IN VARCHAR2,
3771 p_shipment_no IN VARCHAR2,
3772 x_return_status OUT NOCOPY VARCHAR2)
3773 IS
3774
3775
3776 /*
3777 **Alpha Variables
3778 */
3779 L_SELECTION_MESSAGE GR_SELECTION.message%TYPE;
3780
3781 /*
3782 ** Numeric Variables
3783 */
3784 L_LINE_STATUS GR_SELECTION.line_status%TYPE;
3785 L_USER_ID GR_SELECTION.user_id%TYPE;
3786 L_LINES_WRITTEN NUMBER;
3787 /*
3788 ** Cursors
3789 **
3790 ** Check to see if this row already written
3791 */
3792 CURSOR c_count_lines
3793 IS
3794 SELECT COUNT(*)
3795 FROM gr_selection sd
3796 WHERE sd.batch_no = g_batch_number
3797 AND sd.order_no = p_order_id
3798 AND sd.order_line_number = p_order_line_number;
3799
3800 BEGIN
3801
3802 x_return_status := 'S';
3803 /*
3804 ** Read to see if the job, order number and line number combination
3805 ** is already on file.
3806 */
3807 l_lines_written := 0;
3808 OPEN c_count_lines;
3809 FETCH c_count_lines INTO l_lines_written;
3810 CLOSE c_count_lines;
3811 /*
3812 ** l_count_lines = zero means no match so write the row
3813 ** otherwise put a message to the log file about a duplicate
3814 */
3815
3816 IF l_lines_written = 0 THEN
3817 /*
3818 ** Get the message string
3819 */
3820 IF p_message_code IS NOT NULL THEN
3821 FND_MESSAGE.SET_NAME('GR',
3822 p_message_code);
3823 IF p_token_name IS NOT NULL THEN
3824 FND_MESSAGE.SET_TOKEN(p_token_name,
3825 p_token_value,
3826 FALSE);
3827 END IF;
3828 l_selection_message := FND_MESSAGE.Get;
3829 END IF;
3830 /*
3831 ** Set the status based on the print flag.
3832 ** If the print flag is set to 'Y' the line status
3833 ** is selected, otherwise the line status is not
3834 ** selected.
3835 */
3836 IF p_print_flag = 'Y' THEN
3837 l_line_status := 2;
3838 ELSE
3839 l_line_status := 0;
3840 END IF;
3841 /*
3842 ** Get the user id.
3843 */
3844 l_user_id := FND_GLOBAL.USER_ID;
3845
3846 INSERT INTO gr_selection
3847 (batch_no,
3848 order_no,
3849 order_line_number,
3850 line_status,
3851 document_code,
3852 print_flag,
3853 user_id,
3854 item_code,
3855 recipient_code,
3856 shipment_no,
3857 message,
3858 user_override,
3859 date_msds_sent)
3860 VALUES
3861 (g_batch_number,
3862 p_order_id,
3863 p_order_line_number,
3864 l_line_status,
3865 p_document_code,
3866 p_print_flag,
3867 l_user_id,
3868 g_item_code,
3869 p_cust_no,
3870 p_shipment_no,
3871 l_selection_message,
3872 'N',
3873 '');
3874 ELSE
3875 FND_FILE.PUT(FND_FILE.LOG,' *** Duplicate order line record exists ***');
3876 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
3877 END IF;
3878
3879 EXCEPTION
3880
3881 WHEN OTHERS THEN
3882 FND_FILE.PUT(FND_FILE.LOG,' Selection Insert Error:'||sqlerrm);
3883 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
3884 x_return_status := FND_API.G_RET_STS_ERROR;
3885
3886 END Insert_Selection_Row;
3887 /*
3888 **
3889 **
3890 **
3891 **
3892 */
3893 PROCEDURE Check_Selected_Line
3894 (x_return_status OUT NOCOPY VARCHAR2,
3895 x_msg_count OUT NOCOPY NUMBER,
3896 x_msg_data OUT NOCOPY VARCHAR2)
3897 IS
3898
3899 /*
3900 ** Alphanumeric variables
3901 */
3902 L_CODE_BLOCK VARCHAR2(2000);
3903 L_PRINT_FLAG VARCHAR2(2);
3904 L_RETURN_STATUS VARCHAR2(1);
3905
3906 L_CURRENT_DATE DATE := SYSDATE;
3907 pg_fp utl_file.file_type;
3908 /*
3909 ** Numeric Variables
3910 */
3911 L_ORACLE_ERROR NUMBER;
3912 L_TIME_PERIOD NUMBER(3);
3913 L_DIFF NUMBER(3);
3914 /*
3915 ** Exceptions
3916 */
3917 SELECTION_INSERT_ERROR EXCEPTION;
3918
3919
3920 /*
3921 ** Define the cursors
3922 **
3923 ** Check order and line are not already selected on an
3924 ** open batch (status 8 is completed, 9 is cancelled)
3925 */
3926 CURSOR c_check_selections
3927 IS
3928 SELECT sd.batch_no
3929 FROM gr_selection_header sh,
3930 gr_selection sd
3931 WHERE ( (sd.order_no = g_order_number /* Same order and line number */
3932 AND sd.order_line_number = g_order_line)
3933 OR ( (NOT EXISTS (SELECT 1
3934 FROM gr_recipient_info
3935 WHERE recipient_code = g_recipient_code)
3936 -- AND document_print_frequency = 'A')
3937 OR sh.batch_no = g_batch_number
3938 )
3939 AND (sd.item_code = g_item_code /* Item and recipient selected */
3940 AND sd.recipient_code = g_recipient_code)
3941 )
3942 )
3943 AND sd.batch_no = sh.batch_no
3944 AND sh.status <> 8
3945 AND sh.status <> 9
3946 ORDER BY sd.BATCH_NO DESC;
3947 LocalSelection c_check_selections%ROWTYPE;
3948 /*
3949 ** Get the dispatch history
3950 */
3951 CURSOR c_get_last_dispatch
3952 IS
3953 SELECT ids.rebuild_item_doc_flag,
3954 ids.last_doc_update_date,
3955 dh.date_msds_sent
3956 FROM gr_dispatch_histories dh,
3957 gr_item_doc_statuses ids
3958 WHERE ids.item_code = g_item_code
3959 /* Fix for B1255401 */
3960 AND dh.dispatch_method_code <> 99
3961 AND ids.document_code = GlobalRecipient.document_code
3962 AND dh.date_msds_sent = (SELECT MAX(dh1.date_msds_sent)
3963 FROM gr_dispatch_histories dh1
3964 WHERE dh1.item_code = g_item_code
3965 AND dh1.document_code = GlobalRecipient.document_code
3966 AND dh1.recipient_code = g_recipient_code);
3967 LocalDispatchRcd c_get_last_dispatch%ROWTYPE;
3968
3969 BEGIN
3970 --pg_fp := utl_file.fopen('/sqlcom/log/opm115m','check.log','w');
3971 x_return_status := FND_API.G_RET_STS_SUCCESS;
3972 l_print_flag := 'YS';
3973 FND_FILE.PUT(FND_FILE.LOG,'Order:'||g_order_number||'Line:'||g_order_line||' Recip:'||g_recipient_code||'Batch:'||g_batch_number||'Item:'||g_item_code);
3974 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
3975 --utl_file.put_line(pg_fp, 'docco '||g_default_document);
3976 OPEN c_check_selections;
3977 FETCH c_check_selections INTO LocalSelection;
3978 IF c_check_selections%FOUND THEN
3979 CLOSE c_check_selections;
3980 l_code_block := ' Order line already selected in print job ';
3981 FND_FILE.PUT(FND_FILE.LOG,l_code_block);
3982 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
3983 --utl_file.put_line(pg_fp, 'cblock' ||l_code_block);
3984 l_return_status := FND_API.G_RET_STS_SUCCESS;
3985 Insert_Selection_Row
3986 ('GR_ORDER_ALREADY_SELECTED',
3987 'CODE',
3988 LocalSelection.batch_no,
3989 g_order_number,
3990 g_order_line,
3991 g_default_document,
3992 'N',
3993 g_recipient_code,
3994 g_shipment_number,
3995 l_return_status);
3996 l_print_flag := 'NO';
3997 IF l_return_status <> 'S' THEN
3998 RAISE Selection_Insert_Error;
3999 END IF;
4000 ELSE
4001 CLOSE c_check_selections;
4002 l_code_block := 'Now check the item code has safety info.';
4003 --utl_file.put_line(pg_fp, 'cblock1' ||l_code_block);
4004 OPEN g_get_item_safety;
4005 FETCH g_get_item_safety INTO GlobalSafetyRecord;
4006 IF g_get_item_safety%NOTFOUND THEN
4007 CLOSE g_get_item_safety;
4008
4009 OPEN g_get_generic_item;
4010 FETCH g_get_generic_item INTO GlobalGenericRecord;
4011 IF g_get_generic_item%NOTFOUND THEN
4012 CLOSE g_get_generic_item;
4013 l_code_block := ' No safety information for this item';
4014 FND_FILE.PUT(FND_FILE.LOG,l_code_block);
4015 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
4016 --utl_file.put_line(pg_fp, 'cblock2' ||l_code_block);
4017 l_return_status := FND_API.G_RET_STS_SUCCESS;
4018 l_print_flag := 'NO';
4019 Insert_Selection_Row
4020 ('GR_NO_SAFETY_INFO',
4021 'ITEM',
4022 g_item_code,
4023 g_order_number,
4024 g_order_line,
4025 g_default_document,
4026 'N',
4027 g_recipient_code,
4028 g_shipment_number,
4029 l_return_status);
4030 IF l_return_status <> 'S' THEN
4031 RAISE Selection_Insert_Error;
4032 END IF;
4033 ELSE
4034 CLOSE g_get_generic_item;
4035 END IF;
4036 ELSE
4037 CLOSE g_get_item_safety;
4038 END IF;/* c_get_item_safety%NOTFOUND */
4039 /*
4040 ** Only need to process further if item safety information
4041 ** is found.
4042 */
4043 IF l_print_flag = 'YS' THEN
4044 l_code_block := ' Check the dispatch history etc.';
4045 FND_FILE.PUT(FND_FILE.LOG,l_code_block);
4046 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
4047 --utl_file.put_line(pg_fp, 'cblock3' ||l_code_block);
4048 /*
4049 ** Get the recipient information. If not there then assume we
4050 ** have to generate a document and build the recipient info during
4051 ** the dispatch history update.
4052 */
4053 OPEN g_get_recipient;
4054 FETCH g_get_recipient INTO GlobalRecipient;
4055 IF g_get_recipient%NOTFOUND THEN
4056 CLOSE g_get_recipient;
4057 l_return_status := FND_API.G_RET_STS_SUCCESS;
4058 IF g_default_document IS NOT NULL THEN
4059 l_return_status := 'S';
4060 Insert_Selection_Row
4061 ('GR_NO_RECIPIENT_DO_PRINT',
4062 '',
4063 '',
4064 g_order_number,
4065 g_order_line,
4066 g_default_document,
4067 'Y',
4068 g_recipient_code,
4069 g_shipment_number,
4070 l_return_status);
4071 IF l_return_status <> 'S' THEN
4072 RAISE Selection_Insert_Error;
4073 END IF;
4074 ELSE
4075 l_return_status := FND_API.G_RET_STS_SUCCESS;
4076 Insert_Selection_Row
4077 ('GR_NO_DEFAULT_DOCUMENT',
4078 'CODE',
4079 g_default_country,
4080 g_order_number,
4081 g_order_Line,
4082 '',
4083 'N',
4084 g_recipient_code,
4085 g_shipment_number,
4086 l_return_status);
4087 IF l_return_status <> 'S' THEN
4088 RAISE Selection_Insert_Error;
4089 END IF;
4090 END IF; /*g_default_document IS NOT NULL*/
4091 ELSE
4092 CLOSE g_get_recipient;
4093 l_code_block := 'Check Recipient Print Frequency';--utl_file.put_line(pg_fp, 'cblock4' ||l_code_block);
4094 /*
4095 ** Check the print frequency on the recipient.
4096 ** 'A' - Always print.
4097 ** 'N' - Never print.
4098 ** 'R' - As required.
4099 ** 'Q' - At least quarterly
4100 ** 'S' - At least every six months
4101 ** 'Y' - At least once a year
4102 */
4103 IF GlobalRecipient.document_print_frequency = 'A' THEN
4104 l_return_status := FND_API.G_RET_STS_SUCCESS;
4105 --utl_file.put_line(pg_fp, 'Always');
4106 Insert_Selection_Row
4107 ('GR_ALWAYS_PRINT',
4108 '',
4109 '',
4110 g_order_number,
4111 g_order_Line,
4112 GlobalRecipient.document_code,
4113 'Y',
4114 g_recipient_code,
4115 g_shipment_number,
4116 l_return_status);
4117 IF l_return_status <> 'S' THEN
4118 RAISE Selection_Insert_Error;
4119 END IF;
4120 ELSIF GlobalRecipient.document_print_frequency = 'N' THEN
4121 l_return_status := FND_API.G_RET_STS_SUCCESS; --utl_file.put_line(pg_fp, 'never');
4122 Insert_Selection_Row
4123 ('GR_NEVER_PRINT',
4124 '',
4125 '',
4126 g_order_number,
4127 g_order_line,
4128 GlobalRecipient.document_code,
4129 'N',
4130 g_recipient_code,
4131 g_shipment_number,
4132 l_return_status);
4133 IF l_return_status <> 'S' THEN
4134 RAISE Selection_Insert_Error;
4135 END IF;
4136 ELSE
4137 OPEN c_get_last_dispatch;
4138 FETCH c_get_last_dispatch INTO LocalDispatchRcd;
4139 IF c_get_last_dispatch%NOTFOUND THEN
4140 --utl_file.put_line(pg_fp, 'not found');
4141 l_return_status := FND_API.G_RET_STS_SUCCESS;
4142 Insert_Selection_Row
4143 ('GR_FIRST_DISPATCH',
4144 '',
4145 '',
4146 g_order_number,
4147 g_order_line,
4148 GlobalRecipient.document_code,
4149 'Y',
4150 g_recipient_code,
4151 g_shipment_number,
4152 l_return_status);
4153 IF l_return_status <> 'S' THEN
4154 RAISE Selection_Insert_Error;
4155 END IF;
4156 ELSIF LocalDispatchRcd.last_doc_update_date > LocalDispatchRcd.date_msds_sent THEN
4157 --utl_file.put_line(pg_fp, 'doc_update > ');
4158 l_return_status := FND_API.G_RET_STS_SUCCESS;
4159 Insert_Selection_Row
4160 ('GR_DOCUMENT_CHANGED',
4161 'DATE',
4162 TO_CHAR(LocalDispatchRcd.date_msds_sent,'DD-MON-YYYY'),
4163 g_order_number,
4164 g_order_line,
4165 GlobalRecipient.document_code,
4166 'Y',
4167 g_recipient_code,
4168 g_shipment_number,
4169 l_return_status);
4170 IF l_return_status <> 'S' THEN
4171 RAISE Selection_Insert_Error;
4172 END IF;
4173 ELSIF GlobalRecipient.document_print_frequency = 'Q' THEN
4174 --utl_file.put_line(pg_fp, 'quarter');
4175 --utl_file.put_line(pg_fp, 'current_date '|| l_current_date);
4176 -- utl_file.put_line(pg_fp, 'msds sent '||LocalDispatchRcd.date_msds_sent);
4177 --utl_file.put_line(pg_fp, 'abs ' ||ABS(MONTHS_BETWEEN (l_current_date,
4178 -- LocalDispatchRcd.date_msds_sent)) );
4179 IF ABS(MONTHS_BETWEEN (l_current_date,
4180 LocalDispatchRcd.date_msds_sent)) >= 3 THEN
4181 l_return_status := FND_API.G_RET_STS_SUCCESS;
4182 --utl_file.put_line(pg_fp, 'in ');
4183 Insert_Selection_Row
4184 ('GR_DISPATCH_QUARTERLY',
4185 '',
4186 '',
4187 g_order_number,
4188 g_order_Line,
4189 GlobalRecipient.document_code,
4190 'Y',
4191 g_recipient_code,
4192 g_shipment_number,
4193 l_return_status);
4194 IF l_return_status <> 'S' THEN
4195 RAISE Selection_Insert_Error;
4196 END IF;
4197 ELSE /* added for 2286375 rework*/
4198 --utl_file.put_line(pg_fp, 'else ');
4199 FND_MESSAGE.SET_NAME('GR', 'GR_ORDER_ALREADY_SELECTED');
4200 FND_MESSAGE.SET_TOKEN('CODE', '');
4201 FND_FILE.PUT(FND_FILE.LOG, FND_MESSAGE.GET);
4202 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
4203 END IF;
4204 ELSIF GlobalRecipient.document_print_frequency = 'S' THEN
4205 --utl_file.put_line(pg_fp, 'S');
4206 IF ABS(MONTHS_BETWEEN (l_current_date,
4207 LocalDispatchRcd.date_msds_sent)) >= 6 THEN
4208 l_return_status := FND_API.G_RET_STS_SUCCESS;
4209 Insert_Selection_Row
4210 ('GR_DISPATCH_QUARTERLY',
4211 '',
4212 '',
4213 g_order_number,
4214 g_order_line,
4215 GlobalRecipient.document_code,
4216 'Y',
4217 g_recipient_code,
4218 g_shipment_number,
4219 l_return_status);
4220 IF l_return_status <> 'S' THEN
4221 RAISE Selection_Insert_Error;
4222 END IF;
4223 ELSE /* added for 2286375 rework*/
4224 FND_MESSAGE.SET_NAME('GR', 'GR_ORDER_ALREADY_SELECTED');
4225 FND_MESSAGE.SET_TOKEN('CODE', '');
4226 FND_FILE.PUT(FND_FILE.LOG, FND_MESSAGE.GET);
4227 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
4228 END IF;
4229 ELSIF GlobalRecipient.document_print_frequency = 'Y' THEN
4230 --utl_file.put_line(pg_fp, 'Y');
4231 IF ABS(MONTHS_BETWEEN (l_current_date,
4232 LocalDispatchRcd.date_msds_sent)) >= 12 THEN
4233 l_return_status := FND_API.G_RET_STS_SUCCESS;
4234 Insert_Selection_Row
4235 ('GR_DISPATCH_QUARTERLY',
4236 '',
4237 '',
4238 g_order_number,
4239 g_order_line,
4240 GlobalRecipient.document_code,
4241 'Y',
4242 g_recipient_code,
4243 g_shipment_number,
4244 l_return_status);
4245 IF l_return_status <> 'S' THEN
4246 RAISE Selection_Insert_Error;
4247 END IF;
4248 ELSE /* added for 2286375 rework*/
4249 FND_MESSAGE.SET_NAME('GR', 'GR_ORDER_ALREADY_SELECTED');
4250 FND_MESSAGE.SET_TOKEN('CODE', '');
4251 FND_FILE.PUT(FND_FILE.LOG, FND_MESSAGE.GET);
4252 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
4253 END IF;
4254 /* GK B1253943 - added in code for testing print freq for different time periods*/
4255 /*ELSIF GlobalRecipient.document_print_frequency = 'R' THEN
4256 l_time_period := GlobalRecipient.time_period;
4257 l_diff := ABS(l_current_date - LocalDispatchRcd.date_msds_sent);
4258 IF l_time_period = l_diff THEN
4259 l_return_status := FND_API.G_RET_STS_SUCCESS;
4260 Insert_Selection_Row
4261 ('GR_DISPATCH_AS_REQUIRED',
4262 '',
4263 '',
4264 g_order_number,
4265 g_order_line,
4266 GlobalRecipient.document_code,
4267 'Y',
4268 g_recipient_code,
4269 g_shipment_number,
4270 l_return_status);
4271 IF l_return_status <> 'S' THEN
4272 RAISE Selection_Insert_Error;
4273 END IF;
4274 END IF;*/
4275 ELSE
4276 FND_FILE.PUT(FND_FILE.LOG,' No document required');
4277 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
4278 --utl_file.put_line(pg_fp, 'no doc');
4279 l_return_status := FND_API.G_RET_STS_SUCCESS;
4280 Insert_Selection_Row
4281 ('GR_NO_DOCUMENT_REQUIRED',
4282 '',
4283 '',
4284 g_order_number,
4285 g_order_line,
4286 GlobalRecipient.document_code,
4287 'N',
4288 g_recipient_code,
4289 g_shipment_number,
4290 l_return_status);
4291 IF l_return_status <> 'S' THEN
4292 RAISE Selection_Insert_Error;
4293 END IF;
4294 END IF;/* c_get_last_dispatch*/
4295 END IF;/* c_document_print_frequency */
4296 END IF; /* g_get_recipient%NOTFOUND */
4297 END IF; /* print_flag='YS' */
4298 END IF; /* c_check_selections */
4299
4300 EXCEPTION
4301
4302 WHEN Selection_Insert_Error THEN
4303 Handle_Error_Messages
4304 ('GR_NO_RECORD_INSERTED',
4305 'CODE',
4306 g_order_number || ' ' || g_order_line,
4307 x_msg_count,
4308 x_msg_data,
4309 x_return_status);
4310
4311 WHEN OTHERS THEN
4312 l_oracle_error := SQLCODE;
4313 /*l_code_block := SUBSTR(SQLERRM, 1, 200); */
4314 l_code_block := l_code_block || ' ' || TO_CHAR(l_oracle_error);
4315 FND_MESSAGE.SET_NAME('GR',
4316 'GR_UNEXPECTED_ERROR');
4317 FND_MESSAGE.SET_TOKEN('TEXT',
4318 l_code_block||sqlerrm,
4319 FALSE);
4320 --utl_file.fflush(pg_fp);
4321 -- utl_file.fclose(pg_fp);
4322
4323 END Check_Selected_Line;
4324 /*
4325 ** This procedure is called to read the address and cover letter text
4326 ** and build the cover letter information into the print work file.
4327 */
4328 PROCEDURE Read_And_Print_Cover_Letter
4329 (p_language_code IN VARCHAR2,
4330 p_item_code IN VARCHAR2,
4331 p_recipient_code IN VARCHAR2,
4332 p_print_address IN VARCHAR2,
4333 p_order_no IN NUMBER,
4334 p_other_addr_id IN NUMBER,
4335 x_return_status OUT NOCOPY VARCHAR2)
4336 IS
4337
4338 /*
4339 ** Alphanumeric Variables
4340 */
4341 L_CODE_BLOCK VARCHAR2(2000);
4342 L_MSG_DATA VARCHAR2(2000);
4343 L_RETURN_STATUS VARCHAR2(1);
4344 L_LANGUAGE_CODE FND_LANGUAGES.language_code%TYPE;
4345 L_WORK_TEXT GR_COVER_LETTERS_TL.text%TYPE;
4346 L_TEXT_LINE GR_WORK_WORKSHEETS.text_line%TYPE;
4347
4348 X_MSG_DATA VARCHAR2(2000);
4349 pg_fp utl_file.file_type;
4350 L_INTEGRATION VARCHAR2(1); /* GK CHANGES*/
4351 /*
4352 ** Numeric Variables
4353 */
4354 L_CUSTOMER_ID OP_CUST_MST.cust_id%TYPE;
4355 L_ADDRESS_ID SY_ADDR_MST.addr_id%TYPE;
4356
4357 X_MSG_COUNT NUMBER;
4358 L_FIRST_SPACE NUMBER;
4359
4360 /*
4361 ** Exception
4362 */
4363 OTHER_API_ERROR EXCEPTION;
4364
4365 BEGIN
4366 l_return_status := 'S';
4367
4368 END Read_And_Print_Cover_Letter;
4369 /*
4370 ** This procedure is called to get the document information.
4371 ** If the information does not exist or needs to be rebuilt, the document
4372 ** is rebuilt.
4373 ** Data is read from the document tables into the print work table.
4374 */
4375 PROCEDURE Print_Document_Selection
4376 (p_document_code IN VARCHAR2,
4377 p_item_code IN VARCHAR2,
4378 p_language_code IN VARCHAR2,
4379 p_disclosure_code IN VARCHAR2,
4380 x_return_status OUT NOCOPY VARCHAR2)
4381 IS
4382 /*
4383 ** Alphanumeric Variables
4384 */
4385 L_CODE_BLOCK VARCHAR2(2000);
4386 L_COMMIT VARCHAR2(1) := 'F';
4387 L_INIT_MSG_LIST VARCHAR2(1) := 'F';
4388 L_RETURN_STATUS VARCHAR2(1);
4389 L_MSG_DATA VARCHAR2(2000);
4390 L_TELEPHONE_NUMBER VARCHAR2(70);
4391
4392 L_LABEL_CODE GR_LABELS_B.label_code%TYPE;
4393 L_ADDR_LINE SY_ADDR_MST.addr1%TYPE;
4394 L_DEFAULT_ORGN SY_ORGN_MST.orgn_code%TYPE;
4395
4396 X_MSG_DATA VARCHAR2(2000);
4397
4398 L_INTEGRATION VARCHAR2(1); /*B2286375 GK Changes*/
4399 /*
4400 ** Numeric Values
4401 */
4402 L_VALIDATION_LEVEL NUMBER := 99;
4403 L_API_VERSION CONSTANT NUMBER := 1.0;
4404 L_LINE_LEN NUMBER;
4405
4406 L_DOCUMENT_TEXT_ID GR_DOCUMENT_PRINT.document_text_id%TYPE;
4407 L_DISCLOSURE_CODE GR_DISCLOSURES.disclosure_code%TYPE;
4408 L_TEXT_LINE GR_DOCUMENT_DETAILS.text_line%TYPE;
4409
4410 L_LABEL_LEN NUMBER;
4411
4412
4413 X_MSG_COUNT NUMBER;
4414 /*
4415 ** Exceptions
4416 */
4417 BUILD_DOCUMENT_ERROR EXCEPTION;
4418
4419 /*
4420 ** Cursors
4421 **
4422 ** Get the document details
4423 */
4424 CURSOR c_get_document
4425 IS
4426 SELECT dd.print_font,
4427 dd.print_size,
4428 dd.text_line
4429 FROM gr_document_details dd
4430 WHERE dd.document_text_id = l_document_text_id
4431 ORDER BY dd.text_line_number;
4432 LocalDocRecord c_get_document%ROWTYPE;
4433 /*
4434 ** Get the organization name, address and contact information
4435 */
4436 CURSOR c_get_orgn_info
4437 IS
4438 SELECT om.orgn_name,
4439 oa.addr1,
4440 oa.addr2,
4441 oa.addr3,
4442 oa.addr4,
4443 oa.postal_code,
4444 oa.state_code,
4445 oa.country_code,
4446 oc.daytime_contact_name,
4447 oc.daytime_telephone,
4448 oc.daytime_extension,
4449 oc.daytime_area_code,
4450 oc.evening_contact_name,
4451 oc.evening_telephone,
4452 oc.evening_extension,
4453 oc.evening_area_code
4454 FROM gr_organization_contacts oc,
4455 sy_addr_mst_v oa,
4456 sy_orgn_mst om
4457 WHERE om.orgn_code = g_default_orgn
4458 AND om.addr_id = oa.addr_id
4459 AND oc.orgn_code = om.orgn_code;
4460 LocalOrgnRecord c_get_orgn_info%ROWTYPE;
4461
4462 /* GK B2286375
4463 ** Get the organization name, address and contact information for OM Integration
4464 */
4465 CURSOR c_get_orgn_info_v
4466 IS
4467 SELECT hou.name,
4468 oa.addr1,
4469 oa.addr2,
4470 oa.addr3,
4471 oa.addr4,
4472 oa.postal_code,
4473 oa.state_code,
4474 oa.country_code,
4475 oc.daytime_contact_name,
4476 oc.daytime_telephone,
4477 oc.daytime_extension,
4478 oc.daytime_area_code,
4479 oc.evening_contact_name,
4480 oc.evening_telephone,
4481 oc.evening_extension,
4482 oc.evening_area_code
4483 FROM gr_organization_contacts oc,
4484 sy_addr_mst_v oa,
4485 hr_operating_units hou,
4486 gl_plcy_mst gl,
4487 sy_orgn_mst om
4488 WHERE om.orgn_code = g_default_orgn
4489 AND oc.orgn_code = om.orgn_code
4490 AND om.addr_id = oa.addr_id
4491 AND om.co_code = gl.co_code
4492 AND gl.org_id = hou.organization_id;
4493 LocalOrgnOMRecord c_get_orgn_info_v%ROWTYPE;
4494 /*
4495 ** Get the label description and print information
4496 */
4497 CURSOR c_get_label_info
4498 IS
4499 SELECT lab.data_position_indicator,
4500 lat.label_description
4501 FROM gr_labels_tl lat,
4502 gr_labels_b lab
4503 WHERE lab.label_code = l_label_code
4504 AND lat.label_code = lab.label_code
4505 AND lat.language = p_language_code;
4506 LocalLabelRecord c_get_label_info%ROWTYPE;
4507
4508 /*
4509 ** Get the country description
4510 */
4511
4512 CURSOR c_get_country_info (V_country_code VARCHAR2)
4513 IS
4514 SELECT geog_desc
4515 FROM sy_geog_mst
4516 WHERE geog_type = 1
4517 AND geog_code = V_country_code;
4518 LocalCountryRecord c_get_country_info%ROWTYPE;
4519
4520 BEGIN
4521 /*
4522 ** Initialize the variables
4523 */
4524 l_return_status := 'S';
4525
4526 IF p_disclosure_code IS NULL THEN
4527 l_disclosure_code := FND_PROFILE.Value('GR_STD_DISCLOSURE');
4528 IF l_disclosure_code IS NULL THEN
4529 l_disclosure_code := 'STAND';
4530 END IF;
4531 ELSE
4532 l_disclosure_code := p_disclosure_code;
4533 END IF;
4534 /*
4535 ** Check and get the text id of the document.
4536 ** Build item document builds the document if required
4537 */
4538 FND_FILE.PUT(FND_FILE.LOG, 'Processing Build_Item_Document for: ' || p_item_code || ' ' || TO_CHAR(g_session_id));
4539 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
4540
4541 GR_PROCESS_DOCUMENTS.Build_Item_Document
4542 (l_commit,
4543 l_init_msg_list,
4544 l_validation_level,
4545 l_api_version,
4546 p_item_code,
4547 p_document_code,
4548 l_disclosure_code,
4549 p_language_code,
4550 g_session_id,
4551 l_document_text_id,
4552 l_return_status,
4553 x_msg_count,
4554 l_msg_data);
4555
4556 IF l_document_text_id IS NULL THEN
4557 l_code_block := 'No document id for ' || p_document_code;
4558 FND_FILE.PUT(FND_FILE.LOG,l_code_block);
4559 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
4560 END IF;
4561
4562 IF l_return_status <> 'S' THEN
4563 RAISE Build_Document_Error;
4564 END IF;
4565 -- Submit_Print_Request
4566 -- (p_printer,
4567 -- p_user_print_style,
4568 -- p_number_of_copies,
4569 -- g_default_document,
4570 -- l_language_code,
4571 -- l_return_status);
4572
4573 /*
4574 ** Get the organization address and contact info.
4575 */
4576 IF g_default_orgn IS NULL THEN
4577 g_default_orgn := FND_PROFILE.Value('GR_ORGN_DEFAULT');
4578 END IF;
4579
4580 /* B2286375 GK Changes*/
4581 IF l_integration = 'N' THEN
4582 OPEN c_get_orgn_info;
4583 FETCH c_get_orgn_info INTO LocalOrgnRecord;
4584 IF c_get_orgn_info%NOTFOUND THEN
4585 l_code_block := 'No organization info for ' || g_default_orgn;
4586 FND_FILE.PUT(FND_FILE.LOG,l_code_block);
4587 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
4588 END IF;
4589 CLOSE c_get_orgn_info;
4590 -- ELSE
4591 --OPEN c_get_orgn_info_v;
4592 --FETCH c_get_orgn_info_v INTO LocalOrgnOMRecord;
4593 --IF c_get_orgn_info_v%NOTFOUND THEN
4594 -- l_code_block := 'No organization info for ' || g_default_orgn;
4595 -- FND_FILE.PUT(FND_FILE.LOG,l_code_block);
4596 -- FND_FILE.NEW_LINE(FND_FILE.LOG,1);
4597 --END IF;
4598 --CLOSE c_get_orgn_info_v;
4599 END IF; /* End changes*/
4600 /*
4601 ** Read the document, based on the text id and copy
4602 ** it into the work file.
4603 */
4604 OPEN c_get_document;
4605 FETCH c_get_document INTO LocalDocRecord;
4606 IF c_get_document%FOUND THEN
4607 WHILE c_get_document%FOUND LOOP
4608 /*
4609 ** Label code for printing the name and address
4610 */
4611 IF LocalDocRecord.text_line = '01100' THEN
4612 l_label_code := '01100';
4613 OPEN c_get_label_info;
4614 FETCH c_get_label_info INTO LocalLabelRecord;
4615 /*
4616 ** If no label info print the code and ????
4617 */
4618 IF c_get_label_info%NOTFOUND THEN
4619 l_text_line := l_label_code || ' ???? ' || g_default_orgn;
4620 FND_FILE.PUT(FND_FILE.LOG,l_text_line);
4621 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
4622 l_return_status := 'S';
4623 Insert_Work_Row
4624 (p_item_code,
4625 LocalDocRecord.print_font,
4626 LocalDocRecord.print_size,
4627 l_text_line,
4628 'M',
4629 l_return_status);
4630
4631 IF l_return_status <> 'S' THEN
4632 RAISE Build_Document_Error;
4633 END IF;
4634 ELSE
4635 l_text_line := LocalLabelRecord.label_description;
4636 IF LocalLabelRecord.data_position_indicator = 'I' THEN
4637 l_text_line := l_text_line || ' '||LocalOrgnRecord.orgn_name;
4638 l_label_len := LENGTH(LocalLabelRecord.label_description) + 16;
4639 ELSIF LocalLabelRecord.data_position_indicator IN ('C', 'R') THEN
4640 l_text_line := RPAD(l_text_line,30)||' '||LocalOrgnRecord.orgn_name;
4641 l_label_len := 31;
4642 END IF;
4643 l_return_status := 'S';
4644 Insert_Work_Row
4645 (p_item_code,
4646 LocalDocRecord.print_font,
4647 LocalDocRecord.print_size,
4648 l_text_line,
4649 'M',
4650 l_return_status);
4651
4652 IF l_return_status <> 'S' THEN
4653 RAISE Build_Document_Error;
4654 END IF;
4655 IF LocalLabelRecord.data_position_indicator = 'N' THEN
4656 l_text_line := LocalOrgnRecord.orgn_name;
4657 l_label_len := 0;
4658 l_return_status := 'S';
4659 Insert_Work_Row
4660 (p_item_code,
4661 LocalDocRecord.print_font,
4662 LocalDocRecord.print_size,
4663 l_text_line,
4664 'M',
4665 l_return_status);
4666
4667 IF l_return_status <> 'S' THEN
4668 RAISE Build_Document_Error;
4669 END IF;
4670 END IF;
4671
4672 IF LocalOrgnRecord.addr1 IS NOT NULL THEN
4673 l_addr_line := LocalOrgnRecord.addr1;
4674 l_line_len := l_label_len + LENGTH(l_addr_line);
4675 l_text_line := LPAD(l_addr_line,l_line_len,' ');
4676
4677 l_return_status := 'S';
4678 Insert_Work_Row
4679 (p_item_code,
4680 LocalDocRecord.print_font,
4681 LocalDocRecord.print_size,
4682 l_text_line,
4683 'M',
4684 l_return_status);
4685
4686 IF l_return_status <> 'S' THEN
4687 RAISE Build_Document_Error;
4688 END IF;
4689 END IF;
4690
4691 IF LocalOrgnRecord.addr2 IS NOT NULL THEN
4692 l_addr_line := LocalOrgnRecord.addr2;
4693 l_line_len := l_label_len + LENGTH(l_addr_line);
4694 l_text_line := LPAD(l_addr_line,l_line_len,' ');
4695
4696 l_return_status := 'S';
4697 Insert_Work_Row
4698 (p_item_code,
4699 LocalDocRecord.print_font,
4700 LocalDocRecord.print_size,
4701 l_text_line,
4702 'M',
4703 l_return_status);
4704
4705 IF l_return_status <> 'S' THEN
4706 RAISE Build_Document_Error;
4707 END IF;
4708 END IF;
4709
4710 IF LocalOrgnRecord.addr3 IS NOT NULL THEN
4711 l_addr_line := LocalOrgnRecord.addr3;
4712 l_line_len := l_label_len + LENGTH(l_addr_line);
4713 l_text_line := LPAD(l_addr_line,l_line_len,' ');
4714
4715 l_return_status := 'S';
4716 Insert_Work_Row
4717 (p_item_code,
4718 LocalDocRecord.print_font,
4719 LocalDocRecord.print_size,
4720 l_text_line,
4721 'M',
4722 l_return_status);
4723
4724 IF l_return_status <> 'S' THEN
4725 RAISE Build_Document_Error;
4726 END IF;
4727 END IF;
4728
4729 IF (LocalOrgnRecord.addr4 IS NOT NULL) OR
4730 (LocalOrgnRecord.state_code IS NOT NULL) OR
4731 (LocalOrgnRecord.postal_code IS NOT NULL) THEN
4732 l_addr_line := NULL;
4733 IF LocalOrgnRecord.addr4 IS NOT NULL THEN
4734 l_addr_line := LocalOrgnRecord.addr4;
4735 END IF;
4736 IF LocalOrgnRecord.state_code IS NOT NULL THEN
4737 l_addr_line := l_addr_line ||' '||LocalOrgnRecord.state_code;
4738 END IF;
4739 IF LocalOrgnRecord.postal_code IS NOT NULL THEN
4740 l_addr_line := l_addr_line||' '||LocalOrgnRecord.postal_code;
4741 END IF;
4742 l_line_len := l_label_len + LENGTH(l_addr_line);
4743 l_text_line := LPAD(l_addr_line,l_line_len,' ');
4744
4745 l_return_status := 'S';
4746 Insert_Work_Row
4747 (p_item_code,
4748 LocalDocRecord.print_font,
4749 LocalDocRecord.print_size,
4750 l_text_line,
4751 'M',
4752 l_return_status);
4753
4754 IF l_return_status <> 'S' THEN
4755 RAISE Build_Document_Error;
4756 END IF;
4757 END IF;
4758
4759 IF LocalOrgnRecord.country_code IS NOT NULL THEN
4760 l_addr_line := LocalOrgnRecord.country_code;
4761 OPEN c_get_country_info(l_addr_line);
4762 FETCH c_get_country_info INTO LocalCountryRecord;
4763 IF c_get_country_info%FOUND THEN
4764 l_addr_line := LocalCountryRecord.geog_desc;
4765 END IF;
4766 CLOSE c_get_country_info;
4767 l_line_len := l_label_len + LENGTH(l_addr_line);
4768 l_text_line := LPAD(l_addr_line,l_line_len,' ');
4769
4770 l_return_status := 'S';
4771 Insert_Work_Row
4772 (p_item_code,
4773 LocalDocRecord.print_font,
4774 LocalDocRecord.print_size,
4775 l_text_line,
4776 'M',
4777 l_return_status);
4778
4779 IF l_return_status <> 'S' THEN
4780 RAISE Build_Document_Error;
4781 END IF;
4782 END IF;
4783 END IF;
4784 CLOSE c_get_label_info;
4785 /*
4786 ** Label code for printing the daytime contact name
4787 */
4788 ELSIF LocalDocRecord.text_line = '01101' THEN
4789 IF LocalOrgnRecord.daytime_contact_name IS NOT NULL THEN
4790 l_label_code := '01101';
4791 OPEN c_get_label_info;
4792 FETCH c_get_label_info INTO LocalLabelRecord;
4793 /*
4794 ** If no label info print the label code and ??????
4795 */
4796 IF c_get_label_info%NOTFOUND THEN
4797 l_text_line := l_label_code || '??????' || ' ';
4798 l_text_line := l_text_line || LocalOrgnRecord.daytime_contact_name;
4799 l_return_status := 'S';
4800 Insert_Work_Row
4801 (p_item_code,
4802 LocalDocRecord.print_font,
4803 LocalDocRecord.print_size,
4804 l_text_line,
4805 'M',
4806 l_return_status);
4807
4808 IF l_return_status <> 'S' THEN
4809 RAISE Build_Document_Error;
4810 END IF;
4811 ELSE
4812 l_text_line := RPAD(LocalLabelRecord.label_description,30,' ');
4813 /*
4814 ** Label info and print data on the same line
4815 */
4816 IF LocalLabelRecord.data_position_indicator = 'I' THEN
4817 l_text_line := l_text_line || LocalOrgnRecord.daytime_contact_name;
4818 l_return_status := 'S';
4819 Insert_Work_Row
4820 (p_item_code,
4821 LocalDocRecord.print_font,
4822 LocalDocRecord.print_size,
4823 l_text_line,
4824 'M',
4825 l_return_status);
4826 IF l_return_status <> 'S' THEN
4827 RAISE Build_Document_Error;
4828 END IF;
4829 ELSE
4830 /*
4831 ** Label info and print data on the next line
4832 */
4833 l_return_status := 'S';
4834 Insert_Work_Row
4835 (p_item_code,
4836 LocalDocRecord.print_font,
4837 LocalDocRecord.print_size,
4838 l_text_line,
4839 'M',
4840 l_return_status);
4841 IF l_return_status <> 'S' THEN
4842 RAISE Build_Document_Error;
4843 END IF;
4844 l_text_line := LocalOrgnRecord.daytime_contact_name;
4845 l_return_status := 'S';
4846 Insert_Work_Row
4847 (p_item_code,
4848 LocalDocRecord.print_font,
4849 LocalDocRecord.print_size,
4850 l_text_line,
4851 'M',
4852 l_return_status);
4853 IF l_return_status <> 'S' THEN
4854 RAISE Build_Document_Error;
4855 END IF;
4856 END IF;
4857 END IF;
4858 CLOSE c_get_label_info;
4859 END IF;
4860 /*
4861 ** Label code for printing the daytime contact number
4862 */
4863 ELSIF LocalDocRecord.text_line = '01102' THEN
4864 IF LocalOrgnRecord.daytime_telephone IS NOT NULL THEN
4865 l_label_code := '01102';
4866 l_telephone_number := LocalOrgnRecord.daytime_area_code;
4867 IF l_telephone_number IS NOT NULL THEN
4868 l_telephone_number := l_telephone_number || ' ' || LocalOrgnRecord.daytime_telephone;
4869 ELSE
4870 l_telephone_number := LocalOrgnRecord.daytime_telephone;
4871 END IF;
4872 l_telephone_number := l_telephone_number || ' ' || LocalOrgnRecord.daytime_extension;
4873 OPEN c_get_label_info;
4874 FETCH c_get_label_info INTO LocalLabelRecord;
4875 /*
4876 ** If no label info print the label code and ??????
4877 */
4878 IF c_get_label_info%NOTFOUND THEN
4879 l_text_line := l_label_code || '??????' || ' ' || l_telephone_number;
4880 l_return_status := 'S';
4881 Insert_Work_Row
4882 (p_item_code,
4883 LocalDocRecord.print_font,
4884 LocalDocRecord.print_size,
4885 l_text_line,
4886 'M',
4887 l_return_status);
4888
4889 IF l_return_status <> 'S' THEN
4890 RAISE Build_Document_Error;
4891 END IF;
4892 ELSE
4893 l_text_line := RPAD(LocalLabelRecord.label_description,30,' ');
4894 /*
4895 ** Label info and print data on the same line
4896 */
4897 IF LocalLabelRecord.data_position_indicator = 'I' THEN
4898 l_text_line := l_text_line || l_telephone_number;
4899 l_return_status := 'S';
4900 Insert_Work_Row
4901 (p_item_code,
4902 LocalDocRecord.print_font,
4903 LocalDocRecord.print_size,
4904 l_text_line,
4905 'M',
4906 l_return_status);
4907 IF l_return_status <> 'S' THEN
4908 RAISE Build_Document_Error;
4909 END IF;
4910 ELSE
4911 /*
4912 ** Label info and print data on the next line
4913 */
4914 l_return_status := 'S';
4915 Insert_Work_Row
4916 (p_item_code,
4917 LocalDocRecord.print_font,
4918 LocalDocRecord.print_size,
4919 l_text_line,
4920 'M',
4921 l_return_status);
4922 IF l_return_status <> 'S' THEN
4923 RAISE Build_Document_Error;
4924 END IF;
4925 l_text_line := l_telephone_number;
4926 l_return_status := 'S';
4927 Insert_Work_Row
4928 (p_item_code,
4929 LocalDocRecord.print_font,
4930 LocalDocRecord.print_size,
4931 l_text_line,
4932 'M',
4933 l_return_status);
4934 IF l_return_status <> 'S' THEN
4935 RAISE Build_Document_Error;
4936 END IF;
4937 END IF;
4938 END IF;
4939 CLOSE c_get_label_info;
4940 END IF;
4941 /*
4942 ** Label code for printing the evening contact name
4943 */
4944 ELSIF LocalDocRecord.text_line = '01103' THEN
4945 IF LocalOrgnRecord.evening_contact_name IS NOT NULL THEN
4946 l_label_code := '01103';
4947 OPEN c_get_label_info;
4948 FETCH c_get_label_info INTO LocalLabelRecord;
4949 /*
4950 ** If no label info print the label code and ??????
4951 */
4952 IF c_get_label_info%NOTFOUND THEN
4953 l_text_line := l_label_code || '??????' || ' ';
4954 l_text_line := l_text_line || LocalOrgnRecord.evening_contact_name;
4955 l_return_status := 'S';
4956 Insert_Work_Row
4957 (p_item_code,
4958 LocalDocRecord.print_font,
4959 LocalDocRecord.print_size,
4960 l_text_line,
4961 'M',
4962 l_return_status);
4963
4964 IF l_return_status <> 'S' THEN
4965 RAISE Build_Document_Error;
4966 END IF;
4967 ELSE
4968 l_text_line := RPAD(LocalLabelRecord.label_description,30,' ');
4969 /*
4970 ** Label info and print data on the same line
4971 */
4972 IF LocalLabelRecord.data_position_indicator = 'I' THEN
4973 l_text_line := l_text_line || LocalOrgnRecord.evening_contact_name;
4974 l_return_status := 'S';
4975 Insert_Work_Row
4976 (p_item_code,
4977 LocalDocRecord.print_font,
4978 LocalDocRecord.print_size,
4979 l_text_line,
4980 'M',
4981 l_return_status);
4982 IF l_return_status <> 'S' THEN
4983 RAISE Build_Document_Error;
4984 END IF;
4985 ELSE
4986 /*
4987 ** Label info and print data on the next line
4988 */
4989 l_return_status := 'S';
4990 Insert_Work_Row
4991 (p_item_code,
4992 LocalDocRecord.print_font,
4993 LocalDocRecord.print_size,
4994 l_text_line,
4995 'M',
4996 l_return_status);
4997 IF l_return_status <> 'S' THEN
4998 RAISE Build_Document_Error;
4999 END IF;
5000 l_text_line := LocalOrgnRecord.evening_contact_name;
5001 l_return_status := 'S';
5002 Insert_Work_Row
5003 (p_item_code,
5004 LocalDocRecord.print_font,
5005 LocalDocRecord.print_size,
5006 l_text_line,
5007 'M',
5008 l_return_status);
5009 IF l_return_status <> 'S' THEN
5010 RAISE Build_Document_Error;
5011 END IF;
5012 END IF;
5013 END IF;
5014 CLOSE c_get_label_info;
5015 END IF;
5016 /*
5017 ** Label code for printing the evening contact number
5018 */
5019 ELSIF LocalDocRecord.text_line = '01104' THEN
5020 IF LocalOrgnRecord.evening_telephone IS NOT NULL THEN
5021 l_label_code := '01104';
5022 l_telephone_number := LocalOrgnRecord.evening_area_code;
5023 IF l_telephone_number IS NOT NULL THEN
5024 l_telephone_number := l_telephone_number || ' ' || LocalOrgnRecord.evening_telephone;
5025 ELSE
5026 l_telephone_number := LocalOrgnRecord.evening_telephone;
5027 END IF;
5028 l_telephone_number := l_telephone_number || ' ' || LocalOrgnRecord.evening_extension;
5029 OPEN c_get_label_info;
5030 FETCH c_get_label_info INTO LocalLabelRecord;
5031 /*
5032 ** If no label info print the label code and ??????
5033 */
5034 IF c_get_label_info%NOTFOUND THEN
5035 l_text_line := l_label_code || '??????' || ' ' || l_telephone_number;
5036 l_return_status := 'S';
5037 Insert_Work_Row
5038 (p_item_code,
5039 LocalDocRecord.print_font,
5040 LocalDocRecord.print_size,
5041 l_text_line,
5042 'M',
5043 l_return_status);
5044
5045 IF l_return_status <> 'S' THEN
5046 RAISE Build_Document_Error;
5047 END IF;
5048 ELSE
5049 l_text_line := RPAD(LocalLabelRecord.label_description,30,' ');
5050 /*
5051 ** Label info and print data on the same line
5052 */
5053 IF LocalLabelRecord.data_position_indicator = 'I' THEN
5054 l_text_line := l_text_line || l_telephone_number;
5055 l_return_status := 'S';
5056 Insert_Work_Row
5057 (p_item_code,
5058 LocalDocRecord.print_font,
5059 LocalDocRecord.print_size,
5060 l_text_line,
5061 'M',
5062 l_return_status);
5063 IF l_return_status <> 'S' THEN
5064 RAISE Build_Document_Error;
5065 END IF;
5066 ELSE
5067 /*
5068 ** Label info and print data on the next line
5069 */
5070 l_return_status := 'S';
5071 Insert_Work_Row
5072 (p_item_code,
5073 LocalDocRecord.print_font,
5074 LocalDocRecord.print_size,
5075 l_text_line,
5076 'M',
5077 l_return_status);
5078 IF l_return_status <> 'S' THEN
5079 RAISE Build_Document_Error;
5080 END IF;
5081 l_text_line := l_telephone_number;
5082 l_return_status := 'S';
5083 Insert_Work_Row
5084 (p_item_code,
5085 LocalDocRecord.print_font,
5086 LocalDocRecord.print_size,
5087 l_text_line,
5088 'M',
5089 l_return_status);
5090 IF l_return_status <> 'S' THEN
5091 RAISE Build_Document_Error;
5092 END IF;
5093 END IF;
5094 END IF;
5095 CLOSE c_get_label_info;
5096 END IF;
5097 /*
5098 ** Print recipient name
5099 */
5100 ELSIF LocalDocRecord.text_line = '01006' THEN
5101 IF g_cust_name IS NOT NULL THEN
5102 l_label_code := '01006';
5103 OPEN c_get_label_info;
5104 FETCH c_get_label_info INTO LocalLabelRecord;
5105 /*
5106 ** If no label info print the label code and ??????
5107 */
5108 IF c_get_label_info%NOTFOUND THEN
5109 l_text_line := l_label_code || '??????' || ' ';
5110 l_text_line := l_text_line || g_cust_name;
5111 l_return_status := 'S';
5112 Insert_Work_Row
5113 (p_item_code,
5114 LocalDocRecord.print_font,
5115 LocalDocRecord.print_size,
5116 l_text_line,
5117 'M',
5118 l_return_status);
5119
5120 IF l_return_status <> 'S' THEN
5121 RAISE Build_Document_Error;
5122 END IF;
5123 ELSE
5124 l_text_line := RPAD(LocalLabelRecord.label_description,30,' ');
5125 /*
5126 ** Label info and print data on the same line
5127 */
5128 IF LocalLabelRecord.data_position_indicator = 'I' THEN
5129 l_text_line := l_text_line || g_cust_name;
5130 l_return_status := 'S';
5131 Insert_Work_Row
5132 (p_item_code,
5133 LocalDocRecord.print_font,
5134 LocalDocRecord.print_size,
5135 l_text_line,
5136 'M',
5137 l_return_status);
5138 IF l_return_status <> 'S' THEN
5139 RAISE Build_Document_Error;
5140 END IF;
5141 ELSE
5142 /*
5143 ** Label info and print data on the next line
5144 */
5145 l_return_status := 'S';
5146 Insert_Work_Row
5147 (p_item_code,
5148 LocalDocRecord.print_font,
5149 LocalDocRecord.print_size,
5150 l_text_line,
5151 'M',
5152 l_return_status);
5153 IF l_return_status <> 'S' THEN
5154 RAISE Build_Document_Error;
5155 END IF;
5156 l_text_line := g_cust_name;
5157 l_return_status := 'S';
5158 Insert_Work_Row
5159 (p_item_code,
5160 LocalDocRecord.print_font,
5161 LocalDocRecord.print_size,
5162 l_text_line,
5163 'M',
5164 l_return_status);
5165 IF l_return_status <> 'S' THEN
5166 RAISE Build_Document_Error;
5167 END IF;
5168 END IF;
5169 END IF;
5170 CLOSE c_get_label_info;
5171 END IF;
5172 ELSIF LocalDocRecord.text_line = '01007' THEN
5173 l_label_code := '01007';
5174 OPEN c_get_label_info;
5175 FETCH c_get_label_info INTO LocalLabelRecord;
5176
5177 IF g_addr1 IS NOT NULL THEN
5178 IF LocalLabelRecord.data_position_indicator = 'I' THEN
5179 l_addr_line := g_addr1;
5180 l_line_len := 30 + LENGTH(l_addr_line);
5181 l_text_line := LPAD(l_addr_line,l_line_len,' ');
5182 ELSE
5183 l_text_line := g_addr1;
5184 END IF;
5185 l_return_status := 'S';
5186 Insert_Work_Row
5187 (p_item_code,
5188 LocalDocRecord.print_font,
5189 LocalDocRecord.print_size,
5190 l_text_line,
5191 'M',
5192 l_return_status);
5193
5194 IF l_return_status <> 'S' THEN
5195 RAISE Build_Document_Error;
5196 END IF;
5197 END IF;
5198
5199 IF g_addr2 IS NOT NULL THEN
5200 IF LocalLabelRecord.data_position_indicator = 'I' THEN
5201 l_addr_line := g_addr2;
5202 l_line_len := 30 + LENGTH(l_addr_line);
5203 l_text_line := LPAD(l_addr_line,l_line_len,' ');
5204 ELSE
5205 l_text_line := g_addr2;
5206 END IF;
5207 l_return_status := 'S';
5208 Insert_Work_Row
5209 (p_item_code,
5210 LocalDocRecord.print_font,
5211 LocalDocRecord.print_size,
5212 l_text_line,
5213 'M',
5214 l_return_status);
5215
5216 IF l_return_status <> 'S' THEN
5217 RAISE Build_Document_Error;
5218 END IF;
5219 END IF;
5220
5221 IF g_addr3 IS NOT NULL THEN
5222 IF LocalLabelRecord.data_position_indicator = 'I' THEN
5223 l_addr_line := g_addr3;
5224 l_line_len := 30 + LENGTH(l_addr_line);
5225 l_text_line := LPAD(l_addr_line,l_line_len,' ');
5226 ELSE
5227 l_text_line := g_addr3;
5228 END IF;
5229 l_return_status := 'S';
5230 Insert_Work_Row
5231 (p_item_code,
5232 LocalDocRecord.print_font,
5233 LocalDocRecord.print_size,
5234 l_text_line,
5235 'M',
5236 l_return_status);
5237
5238 IF l_return_status <> 'S' THEN
5239 RAISE Build_Document_Error;
5240 END IF;
5241 END IF;
5242
5243 IF g_addr4 IS NOT NULL THEN
5244 IF LocalLabelRecord.data_position_indicator = 'I' THEN
5245 l_addr_line := g_addr4;
5246 l_line_len := 30 + LENGTH(l_addr_line);
5247 l_text_line := LPAD(l_addr_line,l_line_len,' ');
5248 ELSE
5249 l_text_line := g_addr4;
5250 END IF;
5251 l_return_status := 'S';
5252 Insert_Work_Row
5253 (p_item_code,
5254 LocalDocRecord.print_font,
5255 LocalDocRecord.print_size,
5256 l_text_line,
5257 'M',
5258 l_return_status);
5259
5260 IF l_return_status <> 'S' THEN
5261 RAISE Build_Document_Error;
5262 END IF;
5263 END IF;
5264
5265 IF g_postal_code IS NOT NULL THEN
5266 IF LocalLabelRecord.data_position_indicator = 'I' THEN
5267 l_addr_line := g_state_code || ' ' || g_postal_code;
5268 l_addr_line := l_addr_line || ' ' || g_country_code;
5269 l_line_len := 30 + LENGTH(l_addr_line);
5270 l_text_line := LPAD(l_addr_line,l_line_len,' ');
5271 ELSE
5272 l_text_line := g_postal_code;
5273 END IF;
5274 l_return_status := 'S';
5275 Insert_Work_Row
5276 (p_item_code,
5277 LocalDocRecord.print_font,
5278 LocalDocRecord.print_size,
5279 l_text_line,
5280 'M',
5281 l_return_status);
5282
5283 IF l_return_status <> 'S' THEN
5284 RAISE Build_Document_Error;
5285 END IF;
5286 END IF;
5287 CLOSE c_get_label_info;
5288 ELSIF LocalDocRecord.text_line = '01008' OR
5289 LocalDocRecord.text_line = '01009' THEN
5290 l_code_block := 'Recipient contact info.';
5291 ELSE
5292 l_return_status := 'S';
5293 Insert_Work_Row
5294 (p_item_code,
5295 LocalDocRecord.print_font,
5296 LocalDocRecord.print_size,
5297 LocalDocRecord.text_line,
5298 'M',
5299 l_return_status);
5300
5301 IF l_return_status <> 'S' THEN
5302 RAISE Build_Document_Error;
5303 END IF;
5304 END IF;
5305 FETCH c_get_document INTO LocalDocRecord;
5306 END LOOP;
5307 END IF;
5308
5309 EXCEPTION
5310
5311 WHEN Build_Document_Error THEN
5312 Handle_Error_Messages
5313 ('GR_UNEXPECTED_ERROR',
5314 'TEXT',
5315 l_msg_data,
5316 x_msg_count,
5317 x_msg_data,
5318 l_return_status);
5319
5320 END Print_Document_Selection;
5321 /*
5322 ** This procedure writes the worksheet row.
5323 */
5324 PROCEDURE Insert_Work_Row
5325 (p_item_code IN VARCHAR2,
5326 p_print_font IN VARCHAR2,
5327 p_print_size IN NUMBER,
5328 p_text_line IN VARCHAR2,
5329 p_line_type IN VARCHAR2,
5330 x_return_status OUT NOCOPY VARCHAR2)
5331 IS
5332
5333 BEGIN
5334
5335 IF g_line_number IS NULL THEN
5336 g_line_number := 0;
5337 END IF;
5338
5339 g_line_number := g_line_number + 1;
5340
5341 INSERT INTO gr_work_worksheets
5342 (session_id,
5343 text_line_number,
5344 item_code,
5345 print_font,
5346 text_line,
5347 line_type,
5348 print_size)
5349 VALUES
5350 (g_session_id,
5351 g_line_number,
5352 p_item_code,
5353 p_print_font,
5354 p_text_line,
5355 p_line_type,
5356 p_print_size);
5357
5358 EXCEPTION
5359
5360 WHEN OTHERS THEN
5361 FND_FILE.PUT(FND_FILE.LOG, TO_CHAR(g_session_id) || ' Error inserting work record');
5362 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
5363
5364 END Insert_Work_Row;
5365 /*
5366 ** This procedure handles the submission of print jobs to the
5367 ** concurrent manager.
5368 **
5369 */
5370 PROCEDURE Submit_Print_Request
5371 (p_printer IN VARCHAR2,
5372 p_user_print_style IN VARCHAR2,
5373 p_number_of_copies IN NUMBER,
5374 p_default_document IN VARCHAR2,
5375 p_language_code IN VARCHAR2,
5376 x_return_status OUT NOCOPY VARCHAR2)
5377 IS
5378
5379 /* 17-Jun-2003 Mercy Thomas BUG 2932007 - Added the following local variables for Document Management */
5380
5381 /************* Local Variables *************/
5382 l_document_management VARCHAR2(2);
5383 l_default_orgn VARCHAR2(4);
5384 l_category VARCHAR2(40);
5385 l_request_id NUMBER;
5386 l_java_concurrent_id NUMBER;
5387 l_attribute1 VARCHAR2(32) := NULL;
5388 l_attribute2 VARCHAR2(32) := NULL;
5389 l_attribute3 VARCHAR2(32) := NULL;
5390 l_attribute4 VARCHAR2(32) := NULL;
5391 l_attribute5 VARCHAR2(32) := NULL;
5392 l_attribute6 VARCHAR2(32) := NULL;
5393 l_attribute7 VARCHAR2(32) := NULL;
5394 l_attribute8 VARCHAR2(32) := NULL;
5395 l_attribute9 VARCHAR2(32) := NULL;
5396 l_attribute10 VARCHAR2(32) := NULL;
5397 l_rebuild_flag VARCHAR2(2) := 'N';
5398
5399 /* 17-Jun-2003 Mercy Thomas BUG 2932007 - End of the code changes */
5400
5401 BEGIN
5402
5403 x_return_status := 'S';
5404
5405 /* 17-Jun-2003 Mercy Thomas BUG 2932007 - Added following code for Document Management */
5406
5407 l_default_orgn := FND_PROFILE.Value('GR_ORGN_DEFAULT');
5408 l_document_management := FND_PROFILE.Value('GR_DOC_MANAGEMENT');
5409
5410 IF g_cover_letter = 'N' THEN
5411
5412 /* If document management is enabled */
5413 IF l_document_management <> 'N' THEN
5414 l_attribute2 := p_default_document;
5415 l_attribute3 := userenv('LANG');
5416 l_attribute4 := 'STAND';
5417 l_attribute5 := l_default_orgn;
5418
5419 /* Determine if this is for a Recipeint Document or a Sales Order */
5420 IF g_report_type = 3 THEN
5421 l_category := 'MSDS_RECIPIENT';
5422 l_attribute1 := g_doc_item_code;
5423 l_attribute6 := ' ';
5424 l_attribute7 := ' ';
5425 l_attribute8 := g_recipient_code;
5426 l_attribute9 := NULL;
5427 l_attribute10 := NULL;
5428 ELSE
5429 l_category := 'MSDS_SALES_ORDER';
5430 l_attribute1 := g_item_code;
5431 l_attribute6 := ' ';
5432 l_attribute7 := ' ';
5433 l_attribute8 := g_order_no;
5434 l_attribute9 := g_order_line;
5435 l_attribute10 := g_shipment_number;
5436 END IF;
5437 l_rebuild_flag := 'Y';
5438 ELSE
5439 l_category := NULL;
5440 l_attribute1 := NULL;
5441 l_attribute2 := NULL;
5442 l_attribute3 := NULL;
5443 l_attribute4 := NULL;
5444 l_attribute5 := NULL;
5445 l_attribute6 := NULL;
5446 l_attribute7 := NULL;
5447 l_attribute8 := NULL;
5448 l_attribute9 := NULL;
5449 l_attribute10 := NULL;
5450 l_rebuild_flag := 'N';
5451 END IF;
5452 ELSIF g_cover_letter = 'Y' THEN
5453 l_rebuild_flag := 'N';
5454 END IF;
5455
5456 IF l_rebuild_flag = 'Y' THEN
5457 FND_FILE.PUT(FND_FILE.LOG, ' Rebuild Flag is selected ');
5458 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
5459 ELSE
5460 FND_FILE.PUT(FND_FILE.LOG, ' Rebuild Flag is not selected ');
5461 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
5462 END IF;
5463
5464 /* 17-Jun-2003 Mercy Thomas BUG 2932007 - End of the code changes */
5465
5466 IF p_number_of_copies > 0 THEN
5467 g_print_status := FND_REQUEST.SET_PRINT_OPTIONS
5468 (p_printer,
5469 p_user_print_style,
5470 p_number_of_copies, TRUE, 'N');
5471 END IF;
5472
5473 /* 17-Jun-2003 Mercy Thomas BUG 2932007 - Added parameters for the concurrent report to incorporate Document Management */
5474
5475 g_concurrent_id := FND_REQUEST.SUBMIT_REQUEST
5476 ('GR', 'GRRPT030_DOC', '', '', FALSE, g_session_id,
5477 p_default_document, p_language_code, 1,
5478 l_category, 'PDF', l_attribute1, l_attribute2, l_attribute3, l_attribute4, l_attribute5,
5479 l_attribute6, l_attribute7, l_attribute8, l_attribute9, l_attribute10, l_rebuild_flag,'', '', '',
5480 '', '', '', '', '', '', '', '', '', '',
5481 '', '', '', '', '', '', '', '', '', '',
5482 '', '', '', '', '', '', '', '', '', '',
5483 '', '', '', '', '', '', '', '', '', '',
5484 '', '', '', '', '', '', '', '', '', '',
5485 '', '', '', '', '', '', '', '', '', '',
5486 '', '', '', '', '', '', '', '', '', '',
5487 '', '', '', '', '', '', '', '', '', '');
5488
5489 /* 17-Jun-2003 Mercy Thomas BUG 2932007 - End of the code changes */
5490
5491 -- g_session_id := g_session_id + 1;
5492 SELECT gr_work_build_docs_s.nextval INTO g_session_id
5493 FROM dual;
5494 -- Bug #1902822 (JKB)
5495
5496
5497 EXCEPTION
5498
5499 WHEN OTHERS THEN
5500 x_return_status := 'E';
5501
5502 END Submit_Print_Request;
5503 /*
5504 ** This procedure is called from the EXCEPTION handlers
5505 ** in other procedures. It is passed the message code,
5506 ** token name and token value.
5507 **
5508 ** The procedure will then process the error message into
5509 ** the message stack and then return to the calling routine.
5510 ** The procedure assumes all messages used are in the
5511 ** application id 'GR'.
5512 **
5513 */
5514 PROCEDURE Handle_Error_Messages
5515 (p_message_code IN VARCHAR2,
5516 p_token_name IN VARCHAR2,
5517 p_token_value IN VARCHAR2,
5518 x_msg_count IN OUT NOCOPY NUMBER,
5519 x_msg_data IN OUT NOCOPY VARCHAR2,
5520 x_return_status OUT NOCOPY VARCHAR2)
5521 IS
5522 /*
5523 ** Alphanumeric variables
5524 */
5525 L_MSG_DATA VARCHAR2(2000);
5526
5527
5528 /*
5529 ** Numeric variables
5530 */
5531 L_MSG_COUNT NUMBER;
5532
5533 BEGIN
5534
5535 x_return_status := FND_API.G_RET_STS_ERROR;
5536 FND_MESSAGE.SET_NAME('GR',
5537 p_message_code);
5538 IF p_token_name IS NOT NULL THEN
5539 FND_MESSAGE.SET_TOKEN(p_token_name,
5540 p_token_value,
5541 FALSE);
5542 END IF;
5543
5544 FND_MSG_PUB.Add;
5545 FND_MSG_PUB.Count_and_Get
5546 (p_count => l_msg_count,
5547 p_data => l_msg_data);
5548 FND_FILE.PUT(FND_FILE.LOG, p_message_code||' '||p_token_value);
5549 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
5550 END Handle_Error_Messages;
5551
5552 END GR_PROCESS_ORDERS;