[Home] [Help]
PACKAGE: APPS.GR_PROCESS_DOCUMENTS
Source
1 PACKAGE GR_PROCESS_DOCUMENTS AUTHID CURRENT_USER AS
2 /*$Header: GRPDOCUS.pls 115.67 2003/08/20 20:43:54 methomas ship $*/
3
4 /* Global definitions used when defining the print sequence for
5 ** ingredient, toxic and exposure information.
6 */
7 TYPE g_toxic_line IS TABLE OF VARCHAR2(5)
8 INDEX BY BINARY_INTEGER;
9
10 TYPE g_ingred_line IS TABLE OF VARCHAR2(5)
11 INDEX BY BINARY_INTEGER;
12
13 TYPE g_exposure_line IS TABLE OF VARCHAR2(5)
14 INDEX BY BINARY_INTEGER;
15
16 TYPE g_ingred_size IS TABLE OF NUMBER(5)
17 INDEX BY BINARY_INTEGER;
18
19
20 T_INGRED_LINE GR_PROCESS_DOCUMENTS.G_Ingred_Line;
21 T_TOXIC_LINE GR_PROCESS_DOCUMENTS.G_Toxic_Line;
22 T_EXPOSURE_LINE GR_PROCESS_DOCUMENTS.G_Exposure_Line;
23 T_INGRED_SIZE GR_PROCESS_DOCUMENTS.G_ingred_size;
24
25 /*
26 ** PL/SQL table defined for global use with the package procedures
27 ** to record the print ingredient section data to consolidate based
28 ** on the item group.
29 */
30
31 TYPE print_ingredient IS RECORD
32 (item_group_code gr_item_general.item_group_code%TYPE,
33 line_text VARCHAR2(2000),
34 concentration NUMBER,
35 print_size NUMBER);
36
37 TYPE t_print_ingredient IS TABLE OF print_ingredient
38 INDEX BY BINARY_INTEGER;
39
40 /*
41 ** Datastructures
42 */
43 L_PRINT_INGREDIENT GR_PROCESS_DOCUMENTS.t_print_ingredient;
44 L_TEMP_INGREDIENT GR_PROCESS_DOCUMENTS.t_print_ingredient;
45
46 /*
47 ** Global alpha variable definitions
48 */
49 G_PKG_NAME CONSTANT VARCHAR2(255) := 'GR_PROCESS_DOCUMENTS';
50
51 G_DOCUMENT_CODE GR_DOCUMENT_CODES.document_code%TYPE;
52 G_DISCLOSURE_CODE GR_DISCLOSURES.disclosure_code%TYPE;
53 G_ING_DISCLOSURE GR_DISCLOSURES.disclosure_code%TYPE;
54
55 G_START_ITEM GR_ITEM_GENERAL.item_code%TYPE;
56 G_END_ITEM GR_ITEM_GENERAL.item_code%TYPE;
57
58 G_CURRENT_PHRASE GR_PHRASES_B.phrase_code%TYPE;
59 G_CURRENT_ITEM GR_ITEM_GENERAL.item_code%TYPE;
60 G_ML_ITEM GR_ITEM_GENERAL.item_code%TYPE;
61 G_CURRENT_LABEL GR_LABELS_B.label_code%TYPE;
62 G_OPM_VERSION FND_PROFILE_OPTION_VALUES.profile_option_value%TYPE;
63 G_MAIN_HEADING GR_MAIN_HEADINGS_B.main_heading_code%TYPE;
64 G_SUB_HEADING GR_SUB_HEADINGS_B.sub_heading_code%TYPE;
65 G_LABEL_CODE GR_LABELS_B.label_code%TYPE;
66 G_LANGUAGE_CODE FND_LANGUAGES.language_code%TYPE;
67 G_RECIP_PRINTED VARCHAR2(2) := 'NO';
68 G_VENDOR_PRINTED VARCHAR2(2) := 'NO';
69 G_GENERIC_STATUS VARCHAR2(7) := 'UNKNOWN';
70 G_GENERIC_ITEM GR_ITEM_GENERAL.item_code%TYPE;
71 G_ITEMS_TO_PRINT VARCHAR2(1);
72 G_MASTER_ITEM GR_ITEM_GENERAL.item_code%TYPE;
73 G_DOCUMENT_ITEM GR_ITEM_GENERAL.item_code%TYPE;
74 G_START_GENERIC GR_ITEM_GENERAL.item_code%TYPE;
75 G_END_GENERIC GR_ITEM_GENERAL.item_code%TYPE;
76
77 G_CURRENT_DATE DATE := sysdate;
78 G_SUPERCEDE_DATE DATE := sysdate;
79
80
81 /* 06-May-2002 M. Grosser BUG 9999999 - Added for XML/HTML Implementation */
82 G_OUTPUT_TYPE VARCHAR2(8) := 'PDF';
83 /* 06-Dec-2002 M. Thomas BUG 2349126 - Added to check if the Process Documents Procedures are called from Process Orders */
84 G_CALLED_FROM NUMBER := 0;
85 G_ORDERS_FLAG VARCHAR2(2) := 'N';
86
87 /* 27-Jan-2003 Mercy Thomas BUG 2685842 - Added the following Global Variable to check for the rebuild indicator flag forthe Linked inventory Item */
88 G_DOC_REBUILD_FLAG VARCHAR2(2) := 'N';
89 /* 27-Jan-2003 Mercy Thomas BUG 2685842 - End of the Changes */
90
91 /* 17-Jun-2003 Mercy Thomas BUG 2932007 - Added the following Global Variable to populate the Formula and Recipe information */
92 G_FORMULA_NO VARCHAR2(32);
93 G_FORMULA_VERS NUMBER;
94 G_RECIPE_NO VARCHAR2(32);
95 G_RECIPE_VERS NUMBER;
96 G_DOC_INV_REBUILD_FLAG VARCHAR2(2) := 'N';
97 /* 17-Jun-2003 Mercy Thomas BUG 2932007 - End of the Changes */
98
99 /* 07-Jun-2001 Melanie Grosser BUG 1772096 - Modified code to print column headings for Ingredient,
100 Exposure and Toxic sections. Added new procedure
101 GET_COLUMN_HEADINGS.
102 */
103 /*
104 ** Global Column Headings
105 */
106 G_INGCASNUM_HDG GR_SUB_HEADINGS_TL.sub_heading_desc%TYPE;
107 G_INGMSDS_HDG GR_SUB_HEADINGS_TL.sub_heading_desc%TYPE;
108 G_INGCONC_HDG GR_SUB_HEADINGS_TL.sub_heading_desc%TYPE;
109 G_INGUSER_HDG GR_SUB_HEADINGS_TL.sub_heading_desc%TYPE;
110 G_INGEEC_HDG GR_SUB_HEADINGS_TL.sub_heading_desc%TYPE;
111 G_INGHAZARD_HDG GR_SUB_HEADINGS_TL.sub_heading_desc%TYPE;
112 G_INGNFPA_HDG GR_SUB_HEADINGS_TL.sub_heading_desc%TYPE;
113 G_INGHMIS_HDG GR_SUB_HEADINGS_TL.sub_heading_desc%TYPE;
114 G_INGRISK_HDG GR_SUB_HEADINGS_TL.sub_heading_desc%TYPE;
115 G_INGSAFETY_HDG GR_SUB_HEADINGS_TL.sub_heading_desc%TYPE;
116 G_EXPCASNUM_HDG GR_SUB_HEADINGS_TL.sub_heading_desc%TYPE;
117 G_EXPMSDS_HDG GR_SUB_HEADINGS_TL.sub_heading_desc%TYPE;
118 G_EXPAUTH_HDG GR_SUB_HEADINGS_TL.sub_heading_desc%TYPE;
119 G_EXPTYPE_HDG GR_SUB_HEADINGS_TL.sub_heading_desc%TYPE;
120 G_EXPDOSE_HDG GR_SUB_HEADINGS_TL.sub_heading_desc%TYPE;
121 G_EXPNOTE_HDG GR_SUB_HEADINGS_TL.sub_heading_desc%TYPE;
122 G_TOXCASNUM_HDG GR_SUB_HEADINGS_TL.sub_heading_desc%TYPE;
123 G_TOXMSDS_HDG GR_SUB_HEADINGS_TL.sub_heading_desc%TYPE;
124 G_TOXSPECIES_HDG GR_SUB_HEADINGS_TL.sub_heading_desc%TYPE;
125 G_TOXEXPOSE_HDG GR_SUB_HEADINGS_TL.sub_heading_desc%TYPE;
126 G_TOXROUTE_HDG GR_SUB_HEADINGS_TL.sub_heading_desc%TYPE;
127 G_TOXDOSE_HDG GR_SUB_HEADINGS_TL.sub_heading_desc%TYPE;
128 G_TOXNOTE_HDG GR_SUB_HEADINGS_TL.sub_heading_desc%TYPE;
129 G_PRINT_COL_HDG VARCHAR2(1);
130
131 /*
132 ** Global numeric variable definitions
133 */
134 G_RECORD_COUNT NUMBER;
135 G_LINE_NUMBER NUMBER;
136 G_SESSION_ID NUMBER;
137 G_MAXIMUM_RECORDS NUMBER;
138 G_DOCUMENT_TEXT_ID NUMBER;
139 G_USER_ID NUMBER;
140 G_ITEM_PRINT_COUNT NUMBER(5) DEFAULT 0;
141 G_LOW_PRECISION NUMBER;
142 G_HIGH_PRECISION NUMBER;
143 G_ROW NUMBER(5) DEFAULT 0;
144
145 /*
146 ** Concurrent request return values
147 */
148 G_PRINT_STATUS BOOLEAN;
149 G_CONCURRENT_ID NUMBER;
150
151
152 /*
153 ** Constant Declarations
154 */
155 GR_HIGH_ROLLUP CONSTANT INTEGER := 1;
156 GR_LOW_ROLLUP CONSTANT INTEGER := 2;
157 GR_AVERAGE_ROLLUP CONSTANT INTEGER := 3;
158 GR_SUM_ROLLUP CONSTANT INTEGER := 4;
159 GR_COUNT_ROLLUP CONSTANT INTEGER := 5;
160 GR_COUNTRY_ROLLUP CONSTANT INTEGER := 6;
161 /*
162 ** M Thomas 05-Feb-2002 Bug 1323951 Added the new rollup types
163 */
164 GR_LC50_IHL_ROLLUP CONSTANT INTEGER := 7;
165 GR_LD50_ORAL_ROLLUP CONSTANT INTEGER := 8;
166 GR_LD50_SKIN_ROLLUP CONSTANT INTEGER := 9;
167
168 /*
169 ** M Thomas 05-Feb-2002 Bug 1323951 End of the changes
170 */
171
172
173 /*
174 ** Global cursor definitions
175 **
176 ** Document Codes
177 */
178 CURSOR g_get_document_code
179 IS
180 SELECT dc.document_code,
181 dc.document_description,
182 dc.document_date_format,
183 dc.print_ingredients_flag,
184 dc.ingredient_header_label,
185 dc.ingredient_conc_ind,
186 dc.print_toxic_info_flag,
187 dc.toxic_header_label,
188 dc.print_exposure_flag,
189 dc.exposure_header_label,
190 dc.prop_65_conc_ind,
191 dc.sara_312_conc_ind,
192 dc.print_rtk_on_document,
193 dc.rtk_header,
194 dc.print_all_state_indicator
195 FROM gr_document_codes dc
196 WHERE dc.document_code = g_document_code;
197 GlobalDocumentMaster g_get_document_code%ROWTYPE;
198 /*
199 ** Disclosure Codes
200 */
201 CURSOR g_get_disclosure_code
202 IS
203 SELECT di.disclosure_code
204 FROM gr_disclosures di
205 WHERE di.disclosure_code = g_disclosure_code;
206 GlobalDisclosureRecord g_get_disclosure_code%ROWTYPE;
207 /*
208 ** Language Codes
209 */
210 CURSOR g_get_language_code
211 IS
212 SELECT lng.language_code
213 FROM fnd_languages lng
214 WHERE lng.language_code = g_language_code;
215 GlobalLanguageRecord g_get_language_code%ROWTYPE;
216 /*
217 ** Count the number of item explosion records
218 */
219 CURSOR g_get_explosion_count
220 IS
221 SELECT COUNT(*) count
222 FROM gr_item_concentrations ic
223 WHERE ic.item_code = g_current_item;
224 /*
225 ** Get the document work structure
226 */
227 CURSOR g_get_work_structure
228 IS
229 SELECT wbd.main_heading_code,
230 wbd.sub_heading_code,
231 wbd.record_type,
232 wbd.label_or_phrase_code,
233 wbd.concentration_percent,
234 wbd.label_class,
235 wbd.phrase_type,
236 wbd.phrase_hierarchy,
237 wbd.print_flag,
238 wbd.source_itemcode
239 FROM gr_work_build_docs wbd
240 WHERE wbd.session_id = g_session_id
241 AND wbd.document_code = g_document_code
242 ORDER BY wbd.sequence_number;
243 GlobalStructureRecord g_get_work_structure%ROWTYPE;
244 /*
245 ** Get the main heading description
246 */
247 CURSOR g_get_main_heading
248 IS
249 SELECT mhb.main_heading_print_font,
250 mhb.main_heading_print_size,
251 mht.main_heading_description
252 FROM gr_main_headings_tl mht,
253 gr_main_headings_b mhb
254 WHERE mhb.main_heading_code = g_main_heading
255 AND mht.main_heading_code = g_main_heading
256 AND mht.language = g_language_code;
257 GlobalMainHeading g_get_main_heading%ROWTYPE;
258 /*
259 ** Get the sub heading description
260 */
261 CURSOR g_get_sub_heading
262 IS
263 SELECT shb.sub_heading_print_font,
264 shb.sub_heading_print_size,
265 sht.sub_heading_desc
266 FROM gr_sub_headings_tl sht,
267 gr_sub_headings_b shb
268 WHERE shb.sub_heading_code = g_sub_heading
269 AND sht.sub_heading_code = g_sub_heading
270 AND sht.language = g_language_code;
271 GlobalSubHeading g_get_sub_heading%ROWTYPE;
272 /*
273 ** Get the item codes in the selection range
274 */
275 CURSOR g_get_item_range
276 IS
277 SELECT ig1.item_code,
278 ig1.primary_cas_number,
279 ig1.ingredient_flag,
280 ig1.formula_source_indicator,
281 ig1.print_ingredient_phrases_flag,
282 ig1.explode_ingredient_flag,
283 ig1.product_class,
284 ig1.internal_reference_number,
285 ig1.version_code,
286 em.european_index_number,
287 em.eec_number,
288 em.consolidated_risk_phrase,
289 em.consolidated_safety_phrase,
290 fnu.user_name,
291 pc.product_class_desc
292 FROM gr_emea em,
293 gr_product_classes pc,
294 fnd_user fnu,
295 gr_item_general ig1
296 WHERE ig1.item_code >= g_start_item
297 AND ig1.item_code <= g_end_item
298 AND ig1.user_id = fnu.user_id
299 AND ig1.product_class = pc.product_class (+)
300 AND ig1.item_code = em.item_code (+)
301 ORDER BY ig1.item_code;
302 GlobalItemRecord g_get_item_range%ROWTYPE;
303 /*
304 ** Item ingredient concentrations
305 */
306 CURSOR g_get_ingredients
307 IS
308 SELECT ic.ingredient_item_code,
309 ic.concentration_percentage,
310 ig1.print_ingredient_phrases_flag,
311 ig1.item_group_code,
312 em.eec_number,
313 em.consolidated_risk_phrase,
314 em.consolidated_safety_phrase
315 FROM gr_emea em,
316 gr_item_general ig1,
317 gr_item_concentrations ic
318 WHERE ic.item_code = GlobalItemRecord.item_code
319 AND ic.ingredient_item_code = em.item_code (+)
320 AND ic.ingredient_item_code = ig1.item_code
321 ORDER BY 2 desc;
322 GlobalIngredientRecord g_get_ingredients%ROWTYPE;
323 /*
324 ** Item multilingual name
325 */
326 CURSOR g_get_ml_name
327 IS
328 SELECT mln.name_description
329 FROM gr_multilingual_name_tl mln
330 WHERE mln.item_code = g_ml_item
331 AND mln.language = g_language_code
332 AND mln.label_code = g_label_code;
333 GlobalMLName g_get_ml_name%ROWTYPE;
334 /*
335 ** Phrase description
336 */
337 CURSOR g_get_phrase_desc
338 IS
339 SELECT pd.phrase_text,
340 pd.print_font,
341 pd.print_size,
342 pd.image_pathname,
343 pd.image_print_location
344 FROM gr_phrases_tl pd
345 WHERE pd.phrase_code = g_current_phrase
346 AND pd.language = g_language_code;
347 GlobalPhraseRecord g_get_phrase_desc%ROWTYPE;
348 /*
349 ** Get the product classes associated with a label code
350 */
351 CURSOR g_get_label_prod_cls
352 IS
353 SELECT COUNT(*)
354 FROM gr_label_prod_classes lpc
355 WHERE lpc.label_code = g_current_label
356 AND lpc.product_class = GlobalItemRecord.product_class;
357 /*
358 ** Get the label code information
359 */
360 CURSOR g_get_label_info
361 IS
362 SELECT lc.data_position_indicator,
363 lc.print_font,
364 lc.print_size,
365 lc.label_class_code,
366 lc.label_properties_flag,
367 lc.ingredient_value_flag,
368 lc.print_ingredient_indicator,
369 lc.ingredient_label_code,
370 ld.label_description,
371 cl.rollup_type,
372 cl.rollup_label,
373 cl.rollup_property
374 FROM gr_labels_tl ld,
375 gr_labels_b lc, gr_label_classes_b cl
376 WHERE lc.label_code = g_label_code
377 AND ld.label_code = lc.label_code
378 AND ld.language = g_language_code
379 AND cl.label_class_code = lc.label_class_code;
380 GlobalLabelRecord g_get_label_info%ROWTYPE;
381
382 /*
383 ** Get the precision for the property value
384 */
385 CURSOR g_get_property_precision (V_property VARCHAR2)
386 IS
387 SELECT precision
388 FROM gr_properties_b
389 WHERE property_id = V_property;
390 GlobalPropertyRecord g_get_property_precision%ROWTYPE;
391
392 /*
393 ** Get the item disclosure information
394 */
395 CURSOR g_get_ing_disclosure
396 IS
397 SELECT id.print_on_document_flag,
398 id.minimum_reporting_level,
399 id.exposure_reporting_level,
400 id.toxicity_reporting_level
401 FROM gr_item_disclosures id
402 WHERE id.item_code = GlobalIngredientRecord.ingredient_item_code
403 AND id.disclosure_code = g_ing_disclosure;
404 GlobalDiscRecord g_get_ing_disclosure%ROWTYPE;
405 /*
406 ** Get the inventory items for a specified master item
407 */
408 CURSOR g_get_master_list
409 IS
410 SELECT gib.ROWID,
411 gib.item_code,
412 gib.item_no,
413 gib.default_document_name_flag,
414 gib.document_rebuild_indicator,
415 gib.attribute_category,
416 gib.attribute1,
417 gib.attribute2,
418 gib.attribute3,
419 gib.attribute4,
420 gib.attribute5,
421 gib.attribute6,
422 gib.attribute7,
423 gib.attribute8,
424 gib.attribute9,
425 gib.attribute10,
426 gib.attribute11,
427 gib.attribute12,
428 gib.attribute13,
429 gib.attribute14,
430 gib.attribute15,
431 gib.attribute16,
432 gib.attribute17,
433 gib.attribute18,
434 gib.attribute19,
435 gib.attribute20,
436 gib.attribute21,
437 gib.attribute22,
438 gib.attribute23,
439 gib.attribute24,
440 gib.attribute25,
441 gib.attribute26,
442 gib.attribute27,
443 gib.attribute28,
444 gib.attribute29,
445 gib.attribute30,
446 gib.created_by,
447 gib.creation_date,
448 gib.last_updated_by,
449 gib.last_update_date,
450 gib.last_update_login,
451 ids.rebuild_item_doc_flag
452 FROM gr_generic_items_b gib,
453 gr_item_doc_statuses ids
454 WHERE gib.item_code = g_master_item
455 AND ids.item_code = g_master_item
456 AND ids.document_code = g_document_code
457 ORDER BY gib.item_no;
458 /* MGROSSER */
459 /*
460 ** Get the master item for a specified inventory item
461 */
462 CURSOR g_get_invent_items
463 IS
464 SELECT gib.ROWID,
465 gib.item_code,
466 gib.item_no,
467 gib.default_document_name_flag,
468 gib.document_rebuild_indicator,
469 gib.attribute_category,
470 gib.attribute1,
471 gib.attribute2,
472 gib.attribute3,
473 gib.attribute4,
474 gib.attribute5,
475 gib.attribute6,
476 gib.attribute7,
477 gib.attribute8,
478 gib.attribute9,
479 gib.attribute10,
480 gib.attribute11,
481 gib.attribute12,
482 gib.attribute13,
483 gib.attribute14,
484 gib.attribute15,
485 gib.attribute16,
486 gib.attribute17,
487 gib.attribute18,
488 gib.attribute19,
489 gib.attribute20,
490 gib.attribute21,
491 gib.attribute22,
492 gib.attribute23,
493 gib.attribute24,
494 gib.attribute25,
495 gib.attribute26,
496 gib.attribute27,
497 gib.attribute28,
498 gib.attribute29,
499 gib.attribute30,
500 gib.created_by,
501 gib.creation_date,
502 gib.last_updated_by,
503 gib.last_update_date,
504 gib.last_update_login,
505 ids.rebuild_item_doc_flag
506 FROM gr_generic_items_b gib,
507 gr_item_doc_statuses ids
508 WHERE gib.item_no >= g_start_generic
509 AND gib.item_no <= g_end_generic
510 AND ids.item_code = gib.item_code
511 AND ids.document_code = g_document_code;
512 GlobalInventList g_get_invent_items%ROWTYPE;
513 /*
514 ** Get the toxic and exposure uom descriptions
515 */
516 CURSOR g_get_uom_desc (V_code VARCHAR2) IS
517 SELECT DECODE (V_code, '%', 'GR_PERCENT',
518 'P', 'GR_PPM',
519 'B', 'GR_PPB',
520 'T', 'GR_PPT',
521 'M', 'GR_MG_M3',
522 'D', 'GR_MP_F3',
523 'N', 'GR_NG_M3',
524 'I', 'GR_PG_M3',
525 'K', 'GR_MG_KG',
526 'L', 'GR_MG_L',
527 'X', 'GR_NOT_ESTABLISHED',
528 'O', 'GR_OTHER')
529 FROM dual;
530
531 PROCEDURE Build_Document_Details
532 (p_commit IN VARCHAR2,
533 p_init_msg_list IN VARCHAR2,
534 p_validation_level IN NUMBER,
535 p_api_version IN NUMBER,
536 p_item_code IN VARCHAR2,
537 p_document_code IN VARCHAR2,
538 p_session_id IN NUMBER,
539 x_return_status OUT NOCOPY VARCHAR2,
540 x_msg_count OUT NOCOPY NUMBER,
541 x_msg_data OUT NOCOPY VARCHAR2);
542 PROCEDURE Resolve_Phrase_Conflicts
543 (p_commit IN VARCHAR2,
544 p_init_msg_list IN VARCHAR2,
545 p_validation_level IN NUMBER,
546 p_api_version IN NUMBER,
547 p_session_id IN NUMBER,
548 x_return_status OUT NOCOPY VARCHAR2,
549 x_msg_count OUT NOCOPY NUMBER,
550 x_msg_data OUT NOCOPY VARCHAR2);
551 PROCEDURE Build_Item_Document
552 (p_commit IN VARCHAR2,
553 p_init_msg_list IN VARCHAR2,
554 p_validation_level IN NUMBER,
555 p_api_version IN NUMBER,
556 p_build_item IN VARCHAR2,
557 p_document_code IN VARCHAR2,
558 p_disclosure_code IN VARCHAR2,
559 p_language_code IN VARCHAR2,
560 p_session_id IN NUMBER,
561 x_document_text_id OUT NOCOPY NUMBER,
562 x_return_status OUT NOCOPY VARCHAR2,
563 x_msg_count OUT NOCOPY NUMBER,
564 x_msg_data OUT NOCOPY VARCHAR2);
565
566 PROCEDURE Print_Document
567 (errbuf OUT NOCOPY VARCHAR2,
568 retcode OUT NOCOPY VARCHAR2,
569 p_commit IN VARCHAR2,
570 p_init_msg_list IN VARCHAR2,
571 p_validation_level IN NUMBER,
572 p_api_version IN NUMBER,
573 p_start_item IN VARCHAR2,
574 p_end_item IN VARCHAR2,
575 p_document_code IN VARCHAR2,
576 p_disclosure_code IN VARCHAR2,
577 p_language_code IN VARCHAR2,
578 p_session_id IN NUMBER,
579 p_printer IN VARCHAR2,
580 p_user_print_style IN VARCHAR2,
581 p_number_of_copies IN NUMBER,
582 p_items_to_print IN VARCHAR2,
583 p_return_status OUT NOCOPY VARCHAR2,
584 p_msg_count OUT NOCOPY NUMBER,
585 p_msg_data OUT NOCOPY VARCHAR2
586 -- /* 09-Jan-2003 Mercy Thomas B2741212 - Commented out for SICO
587 ,p_output_type IN VARCHAR2
588 -- 09-Jan-2003 Mercy Thomas B2741212 - Commented out for SICO */
589 );
590
591 PROCEDURE Print_Worksheet
592 (errbuf OUT NOCOPY VARCHAR2,
593 retcode OUT NOCOPY VARCHAR2,
594 p_commit IN VARCHAR2,
595 p_init_msg_list IN VARCHAR2,
596 p_validation_level IN NUMBER,
597 p_api_version IN NUMBER,
598 p_start_item IN VARCHAR2,
599 p_end_item IN VARCHAR2,
600 p_document_code IN VARCHAR2,
601 p_disclosure_code IN VARCHAR2,
602 p_language_code IN VARCHAR2,
603 p_session_id IN NUMBER,
604 p_printer IN VARCHAR2,
605 p_user_print_style IN VARCHAR2,
606 p_number_of_copies IN NUMBER,
607 p_items_to_print IN VARCHAR2,
608 p_return_status OUT NOCOPY VARCHAR2,
609 p_msg_count OUT NOCOPY NUMBER,
610 p_msg_data OUT NOCOPY VARCHAR2
611 -- /* 09-Jan-2003 Mercy Thomas B2741212 - Commented out for SICO
612 ,p_output_type IN VARCHAR2
613 -- 09-Jan-2003 Mercy Thomas B2741212 - Commented out for SICO */
614 );
615
616 PROCEDURE Process_Document_Structure
617 (p_commit IN VARCHAR2,
618 p_session_id IN NUMBER,
619 p_source_action IN VARCHAR2,
620 x_return_status OUT NOCOPY VARCHAR2,
621 x_msg_count OUT NOCOPY NUMBER,
622 x_msg_data OUT NOCOPY VARCHAR2);
623 PROCEDURE Get_Phrase_Type
624 (p_phrase_code IN VARCHAR2,
625 p_conc_value IN NUMBER,
626 x_phrase_type OUT NOCOPY VARCHAR2,
627 x_hierarchy OUT NOCOPY NUMBER);
628 PROCEDURE Third_Phrase_Insert_Row
629 (p_session_id IN NUMBER,
630 p_sequence_number IN NUMBER,
631 p_source_itemcode IN VARCHAR2,
632 p_document_code IN VARCHAR2,
633 p_main_heading_code IN VARCHAR2,
634 p_main_display_order IN NUMBER,
635 p_sub_heading_code IN VARCHAR2,
636 p_sub_display_order IN NUMBER,
637 p_structure_display_order IN NUMBER,
638 p_third_phrase IN VARCHAR2,
639 x_return_status OUT NOCOPY VARCHAR2);
640 PROCEDURE Sort_Line_Data
641 (p_document_code IN VARCHAR2,
642 x_ingred_line IN OUT NOCOPY g_ingred_line,
643 x_toxic_line IN OUT NOCOPY g_toxic_line,
644 x_exposure_line IN OUT NOCOPY g_exposure_line,
645 x_msg_data OUT NOCOPY VARCHAR2,
646 x_msg_count OUT NOCOPY NUMBER,
647 x_return_status OUT NOCOPY VARCHAR2);
648 PROCEDURE Print_Ingredient_Data
649 (p_item_code IN VARCHAR2,
650 p_language_code IN VARCHAR2,
651 p_label_code IN VARCHAR2,
652 p_source_procedure IN VARCHAR2,
653 p_source_action IN VARCHAR2,
654 x_return_status OUT NOCOPY VARCHAR2);
655 PROCEDURE Insert_Worksheet_Message
656 (p_message_name IN VARCHAR2,
657 p_token_name IN VARCHAR2,
658 p_token_value IN VARCHAR2,
659 p_item_code IN VARCHAR2,
660 x_return_status OUT NOCOPY VARCHAR2,
661 p_token_name2 IN VARCHAR2,
662 p_token_value2 IN VARCHAR2);
663 PROCEDURE Print_Toxic_Data
664 (p_item_code IN VARCHAR2,
665 p_language_code IN VARCHAR2,
666 p_label_code IN VARCHAR2,
667 p_source_action IN VARCHAR2,
668 p_consolidate IN VARCHAR2,
669 x_return_status OUT NOCOPY VARCHAR2);
670 PROCEDURE Print_Exposure_Data
671 (p_item_code IN VARCHAR2,
672 p_language_code IN VARCHAR2,
673 p_label_code IN VARCHAR2,
674 p_source_action IN VARCHAR2,
675 p_consolidate IN VARCHAR2,
676 x_return_status OUT NOCOPY VARCHAR2);
677 PROCEDURE Print_Phrase_lines
678 (p_phrase_text IN VARCHAR2,
679 p_source_action IN VARCHAR2,
680 x_return_status OUT NOCOPY VARCHAR2);
681 PROCEDURE Print_Label_Lines
682 (p_label_code IN VARCHAR2,
683 p_source_action IN VARCHAR2,
684 x_return_status OUT NOCOPY VARCHAR2);
685 PROCEDURE Clear_Worksheet_Session
686 (p_commit IN VARCHAR2,
687 p_session_id IN NUMBER,
688 x_return_status OUT NOCOPY VARCHAR2,
689 x_oracle_error OUT NOCOPY NUMBER,
690 x_msg_data OUT NOCOPY VARCHAR2);
691 PROCEDURE Update_Phrase_Groups
692 (p_commit IN VARCHAR2,
693 p_api_version IN NUMBER,
694 p_init_msg_list IN VARCHAR2,
695 p_validation_level IN NUMBER,
696 p_called_by_form IN VARCHAR2,
697 p_update_option IN VARCHAR2,
698 p_rebuild_document_flag IN VARCHAR2,
699 p_phrase_group_code IN VARCHAR2,
700 p_phrase_code IN VARCHAR2,
701 x_return_status OUT NOCOPY VARCHAR2,
702 x_msg_count OUT NOCOPY NUMBER,
703 x_msg_data OUT NOCOPY VARCHAR2);
704 FUNCTION Conflict_Action
705 (p_work_phrase IN VARCHAR2,
706 p_phrase_code IN VARCHAR2)
707 RETURN VARCHAR2;
708 FUNCTION Product_Toxic_Data
709 (p_item_code IN VARCHAR2)
710 RETURN NUMBER;
711 FUNCTION Product_Exposure_Data
712 (p_item_code IN VARCHAR2)
713 RETURN NUMBER;
714 FUNCTION Read_Multilingual_Name
715 (p_label_code IN VARCHAR2,
716 p_language_code IN VARCHAR2)
717 RETURN VARCHAR2;
718 FUNCTION Print_Concentration
719 (p_concentration_ind IN VARCHAR2,
720 p_concentration IN NUMBER)
721 RETURN VARCHAR2;
722 FUNCTION Get_Hazard_Info
723 (p_item_code IN VARCHAR2,
724 p_hazard_code IN VARCHAR2,
725 p_source_procedure IN VARCHAR2)
726 RETURN VARCHAR2;
727 FUNCTION Calc_OSHA_Flam
728 (p_source_action IN VARCHAR2,
729 p_item_code IN VARCHAR2)
730 RETURN VARCHAR2;
731 PROCEDURE Handle_Error_Messages
732 (p_message_code IN VARCHAR2,
733 p_token_name IN VARCHAR2,
734 p_token_value IN VARCHAR2,
735 x_msg_count IN OUT NOCOPY NUMBER,
736 x_msg_data IN OUT NOCOPY VARCHAR2,
737 x_return_status OUT NOCOPY VARCHAR2);
738
739 FUNCTION Convert_Temperature_Values
740 (p_low_val IN NUMBER,
741 p_high_val IN NUMBER,
742 p_scale IN VARCHAR2)
743 RETURN VARCHAR2;
744
745 FUNCTION Convert_To_Scale
746 (p_value IN NUMBER,
747 p_from_scale IN VARCHAR2,
748 p_to_scale IN VARCHAR2,
749 p_precision IN NUMBER)
750 RETURN NUMBER;
751
752 PROCEDURE consolidate_ingredient_data (p_count IN OUT NOCOPY NUMBER);
753
754 PROCEDURE print_array_data (p_source_action IN VARCHAR2,
755 p_source_procedure IN VARCHAR2,
756 p_row IN NUMBER,
757 p_msds_count IN NUMBER,
758 p_conc_count IN NUMBER,
759 x_return_status IN OUT NOCOPY VARCHAR2);
760
761 FUNCTION GET_INGREDIENT_ROLLUP_VALUE (p_item_code IN VARCHAR2, p_label_code IN VARCHAR2,
762 p_property_id IN VARCHAR2, p_scale IN OUT NOCOPY VARCHAR2, x_return_status IN OUT NOCOPY VARCHAR2) RETURN VARCHAR2;
763
764 /* 24-Sep-2001 Melanie Grosser BUG 1323963 - Implementation of field name masking
765 Added new procedure PRINT_MASKED_VALUE
766 */
767 PROCEDURE print_masked_value (p_item_code IN VARCHAR2,
768 p_label_code IN VARCHAR2,
769 p_value IN VARCHAR2,
770 p_source_action IN VARCHAR2,
771 p_mask OUT NOCOPY VARCHAR2,
772 x_return_status OUT NOCOPY VARCHAR2);
773
774
775 /* 22-Oct-2001 Melanie Grosser BUG 1985685,1839807 - Print only one line worth of characters at a time
776 to avoid problems due to word wrapping in report.
777 Also added subheadings of F H R to HMIS, NFPA and
778 User code columns.
779 Added new procedure CHECK_HEADING_LENGTH.
780 */
781 PROCEDURE check_heading_length (p_text_line IN OUT NOCOPY VARCHAR2,
782 p_text_line2 IN OUT NOCOPY VARCHAR2,
783 p_column_length IN NUMBER,
784 p_source IN VARCHAR2,
785 x_return_status OUT NOCOPY VARCHAR2);
786
787 -- Jeff Baird 05-Nov-2001 Bug #2062036 Set all languages to rebuild if
788 -- one is rebuilt.
789
790 PROCEDURE invalidate_languages
791 (p_document_code IN VARCHAR2,
792 p_language_code IN VARCHAR2,
793 p_disclosure_code IN VARCHAR2,
794 p_commit IN VARCHAR2,
795 p_called_by_form IN VARCHAR2,
796 p_return_status OUT NOCOPY VARCHAR2);
797
798
799 /* 20-Jun-2002 Melanie Grosser BUG 2381697 - Modified code to allow printing of documents in XML and HTML
800 Created new procedure Submit_Print
801 */
802 PROCEDURE Submit_Print
803 (p_document_code IN VARCHAR2,
804 p_language_code IN VARCHAR2,
805 p_source IN VARCHAR2,
806 x_return_status OUT NOCOPY VARCHAR2);
807
808 /* 18-Feb-2003 Melanie Grosser BUG 2800429 - Patchset version of Sico Bug 2789546
809 Added new function Get_Print_ID to retrieve a value from the new
810 sequence GR_PRINT_ID_S
811 */
812 FUNCTION Get_Print_ID
813 RETURN NUMBER;
814
815 /* 26-Feb-2003 Melanie Grosser BUG 2718956 - Fixed data alignment issues
816 Added new function Get_Field_Name_Phrase
817 */
818 FUNCTION Get_Field_Name_Phrase (p_phrase_code IN VARCHAR2,
819 x_return_status OUT NOCOPY VARCHAR2) RETURN VARCHAR2;
820
821
822 /* 26-Feb-2003 Melanie Grosser BUG 2718956 - Fixed data alignment issues
823 Added new procedure Print_Field_Name_Data
824 */
825 PROCEDURE Print_Field_Name_Data (p_line_length IN NUMBER,
826 p_text_line_1 IN OUT NOCOPY VARCHAR2,
827 p_text_line_2 IN OUT NOCOPY VARCHAR2,
828 p_source_action IN VARCHAR2,
829 x_return_status OUT NOCOPY VARCHAR2);
830 /* 17-Jun-2003 Mercy Thomas BUG 2932007 - Added the new Procedure Upload_Doc_File.
831 */
832
833 PROCEDURE Upload_Doc_File
834 (errbuf OUT NOCOPY VARCHAR2,
835 retcode OUT NOCOPY VARCHAR2,
836 p_document_management IN VARCHAR2,
837 p_source_lang IN VARCHAR2,
838 p_category IN VARCHAR2,
839 p_request_id IN NUMBER,
840 p_output_type IN VARCHAR2,
841 p_attribute1 IN VARCHAR2,
842 p_attribute2 IN VARCHAR2,
843 p_attribute3 IN VARCHAR2,
844 p_attribute4 IN VARCHAR2,
845 p_attribute5 IN VARCHAR2,
846 p_attribute6 IN VARCHAR2,
847 p_attribute7 IN VARCHAR2,
848 p_attribute8 IN VARCHAR2,
849 p_attribute9 IN VARCHAR2,
850 p_attribute10 IN VARCHAR2,
851 p_created_by IN NUMBER,
852 p_creation_date IN DATE,
853 p_last_updated_by IN NUMBER,
854 p_last_update_login IN NUMBER,
855 p_last_update_date IN DATE,
856 x_return_status OUT NOCOPY VARCHAR2,
857 x_msg_data OUT NOCOPY VARCHAR2);
858
859 END GR_PROCESS_DOCUMENTS;