[Home] [Help]
PACKAGE: APPS.GR_EURO_CLASSIFICATION
Source
1 PACKAGE GR_EURO_CLASSIFICATION AUTHID CURRENT_USER AS
2 /*$Header: GRPEUROS.pls 115.2 2002/10/30 20:20:10 mgrosser noship $*/
3
4 /* Global alpha variable definitions*/
5
6 G_PKG_NAME CONSTANT VARCHAR2(255) := 'GR_EURO_CLASSIFICATION';
7
8 G_CURRENT_ITEM GR_ITEM_GENERAL.item_code%TYPE;
9 G_INGREDIENT_ITEM GR_ITEM_CONCENTRATIONS.ingredient_item_code%TYPE;
10 G_LABEL_CODE GR_LABELS_B.label_code%TYPE;
11 G_PROPERTY_ID GR_ITEM_PROPERTIES.property_id%TYPE;
12 G_CURRENT_DATE DATE := sysdate;
13 G_PRODUCT_CLASS GR_PRODUCT_CLASSES.product_class%TYPE;
14
15 /* Global Numeric Variables */
16
17 G_SESSION_ID NUMBER;
18 G_USER_ID NUMBER;
19 G_ITEM_PRINT_COUNT NUMBER(5) DEFAULT 0;
20 G_LOGIN NUMBER;
21
22 /* Concurrent Request return values */
23
24 G_PRINT_STATUS BOOLEAN;
25 G_CONCURRENT_ID NUMBER;
26
27
28 /* PL/SQL table defined for storing the consolidated safety phrases */
29 TYPE cons_safety_phrases IS RECORD
30 (combination_group NUMBER(5),
31 safety_string VARCHAR2(200));
32
33 TYPE l_cons_safety_phrases IS TABLE OF cons_safety_phrases
34 INDEX BY BINARY_INTEGER;
35 t_cons_safety_phrases GR_EURO_CLASSIFICATION.l_cons_safety_phrases;
36
37 /* PL/SQL table defined for storing the consolidated risk phrases */
38 TYPE cons_risk_phrases IS RECORD
39 (combination_group NUMBER(5),
40 risk_string VARCHAR2(200));
41
42 TYPE l_cons_risk_phrases IS TABLE OF cons_risk_phrases
43 INDEX BY BINARY_INTEGER;
44 t_cons_risk_phrases GR_EURO_CLASSIFICATION.l_cons_risk_phrases;
45
46 /* PL/SQL table for storing calculations */
47 TYPE gr_temp_table IS RECORD
48 (run_total NUMBER(10),
49 percent NUMBER(10));
50
51 TYPE l_gr_temp IS TABLE OF gr_temp_table
52 INDEX BY BINARY_INTEGER;
53 t_gr_temp_table GR_EURO_CLASSIFICATION.l_gr_temp;
54
55 /* Declare Cursors */
56
57 /*
58 ** Get the item property information
59 */
60
61 CURSOR g_get_item_properties IS
62 SELECT ip.alpha_value,
63 ip.number_value,
64 ip.date_value,
65 ip.property_id
66 FROM gr_item_properties ip
67 WHERE ip.item_code = g_current_item
68 AND ip.label_code = g_label_code;
69 GlobalPropertyRecord g_get_item_properties%ROWTYPE;
70
71 /*
72 ** Get the risk phrase information
73 */
74
75 CURSOR g_get_risk_phrases IS
76 SELECT ir.risk_phrase_code,
77 rc.calculation_name_id,
78 rc.linked_risks_flag
79 FROM gr_item_risk_phrases ir,
80 gr_risk_phrases_b rp,
81 gr_risk_calculations rc,
82 gr_item_concentrations ic
83 WHERE ir.item_code = ic.ingredient_item_code
84 AND ir.item_code = g_ingredient_item
85 AND ir.risk_phrase_code = rp.risk_phrase_code
86 AND SUBSTR(ir.risk_phrase_code, 1,3) = rc.risk_phrase_code;
87 GlobalRiskrecord g_get_risk_phrases%ROWTYPE;
88 /*CURSOR g_get_risk_phrases IS
89 SELECT ws.risk_phrase_code,
90 rc.calculation_name_id,
91 rc.linked_risks_flag
92 FROM gr_item_risk_phrases ir,
96 gr_work_string ws
93 gr_risk_phrases_b rp,
94 gr_risk_calculations rc,
95 gr_item_concentrations ic,
97 WHERE ir.item_code = ic.ingredient_item_code
98 AND ir.item_code = g_ingredient_item
99 AND ws.session_id = g_session_id
100 AND ws.risk_phrase_code = rp.risk_phrase_code
101 AND ws.risk_phrase_code = rc.risk_phrase_code;
102
103 GlobalRiskrecord g_get_risk_phrases%ROWTYPE;*/
104 /*
105 ** Get the work classification information - to check if current or ingredient item
106 */
107 CURSOR g_get_classification_work IS
108 SELECT wc.hazard_classification_code,
109 wc.hazard_group_code,
110 wc.calculation_hierarchy
111 FROM gr_work_classns wc
112 WHERE wc.session_id = g_session_id
113 AND wc.item_code = g_current_item
114 ORDER BY wc.hazard_classification_code,
115 wc.hazard_group_code;
116 GlobalClassWorkRecord g_get_classification_work%ROWTYPE;
117
118 /*
119 ** Get the safety work information
120 */
121 CURSOR g_get_safety_work IS
122 SELECT safety_phrase_code,
123 safety_rule_group,
124 safety_category_code,
125 category_value
126 FROM gr_work_safety_phrases wsc
127 WHERE wsc.session_id = g_session_id
128 AND wsc.item_code = g_current_item
129 AND wsc.assign_flag = 'Y'
130 ORDER BY wsc.safety_phrase_code,
131 wsc.safety_rule_group;
132 GlobalSPWorkRecord g_get_safety_work%ROWTYPE;
133
134 /*
135 ** Get the item classifications
136 */
137 CURSOR g_get_item_classn IS
138 SELECT ihc.hazard_classification_code,
139 ehb.calculation_hierarchy,
140 ehb.hazard_group_code
141 FROM gr_item_classns ihc,
142 gr_eurohazards_b ehb
143 WHERE ihc.item_code = g_ingredient_item
144 AND ihc.hazard_classification_code = ehb.hazard_classification_code;
145 GlobalClassnRecord g_get_item_classn%ROWTYPE;
146
147 /* Check wether the safety phrase exists in the work table */
148 CURSOR g_exists_work_phrase (V_item_code VARCHAR2, V_safety_phrase VARCHAR2) IS
149 SELECT 1
150 FROM dual
151 WHERE EXISTS (SELECT safety_phrase_code
152 FROM gr_work_safety_phrases
153 WHERE item_code = V_item_code
154 AND session_id = g_session_id
155 AND safety_phrase_code = V_safety_phrase
156 AND assign_flag = 'Y');
157
158 /* Procedure Information */
159
160 PROCEDURE Classify_Hazard
161 (errbuf OUT NOCOPY VARCHAR2,
162 retcode OUT NOCOPY VARCHAR2,
163 p_api_version IN NUMBER,
164 p_init_msg_list IN VARCHAR2,
165 p_commit IN VARCHAR2,
166 p_validation_level IN NUMBER,
167 p_item_to_classify_from IN VARCHAR2,
168 p_item_to_classify_to IN VARCHAR2,
169 p_print_calculations IN VARCHAR2,
170 x_session_id OUT NOCOPY NUMBER,
171 x_return_status OUT NOCOPY VARCHAR2,
172 x_msg_count OUT NOCOPY NUMBER,
173 x_msg_data OUT NOCOPY VARCHAR2);
174
175 PROCEDURE Assign_Risk_Phrases
176 (errbuf OUT NOCOPY VARCHAR2,
177 retcode OUT NOCOPY VARCHAR2,
178 p_api_version IN NUMBER,
179 p_session_id IN NUMBER,
180 p_item_to_classify IN VARCHAR2,
181 p_print_calculations IN VARCHAR2,
182 x_return_status OUT NOCOPY VARCHAR2,
183 x_msg_count OUT NOCOPY NUMBER,
184 x_msg_data OUT NOCOPY VARCHAR2);
185
186 PROCEDURE Assign_Safety_Phrases
187 (errbuf OUT NOCOPY VARCHAR2,
188 retcode OUT NOCOPY VARCHAR2,
189 p_api_version IN NUMBER,
190 p_session_id IN NUMBER,
191 p_item_to_classify IN VARCHAR2,
192 p_print_calculations IN VARCHAR2,
193 x_return_status OUT NOCOPY VARCHAR2,
194 x_msg_count OUT NOCOPY NUMBER,
195 x_msg_data OUT NOCOPY VARCHAR2);
196
197 PROCEDURE Update_Hazard_Classifications
198 (errbuf OUT NOCOPY VARCHAR2,
199 retcode OUT NOCOPY VARCHAR2,
200 p_api_version IN NUMBER,
201 p_init_msg_list IN VARCHAR2,
202 p_commit IN VARCHAR2,
203 p_validation_level IN NUMBER,
204 p_session_id IN NUMBER,
205 p_item_to_update IN VARCHAR2,
206 p_update_documents IN VARCHAR2,
207 p_delete_work IN VARCHAR2,
208 x_return_status OUT NOCOPY VARCHAR2,
209 x_msg_count OUT NOCOPY NUMBER,
210 x_msg_data OUT NOCOPY VARCHAR2);
211
212 PROCEDURE Delete_Work_Data
213 (errbuf OUT NOCOPY VARCHAR2,
214 retcode OUT NOCOPY VARCHAR2,
215 p_api_version IN NUMBER,
216 p_init_msg_list IN VARCHAR2,
217 p_commit IN VARCHAR2,
218 p_validation_level IN NUMBER,
219 p_session_id IN NUMBER,
220 p_item_code IN VARCHAR2,
221 x_return_status OUT NOCOPY VARCHAR2,
222 x_msg_count OUT NOCOPY NUMBER,
223 x_msg_data OUT NOCOPY VARCHAR2);
224
225 FUNCTION ITEM_MATCHES_CATEGORY_VALUE(p_item_to_compare IN VARCHAR2,
226 p_safety_category_code IN VARCHAR2,
227 p_category_value IN VARCHAR2) RETURN BOOLEAN;
228
229 END GR_EURO_CLASSIFICATION;