1 PACKAGE gmd_api_grp AUTHID CURRENT_USER AS
2 /* $Header: GMDGAPIS.pls 120.10.12010000.1 2008/07/24 09:53:50 appldev ship $ */
3
4 pkg_application_short_name VARCHAR2(10) DEFAULT '*';
5 pkg_application_Id NUMBER;
6 pkg_flex_field_name VARCHAR2(200) DEFAULT '*';
7 pkg_flex_enabled VARCHAR2(1);
8 pkg_context_column_name VARCHAR2(200);
9 pkg_context_required VARCHAR2(1);
10 setup_done BOOLEAN := FALSE;
11 user_id NUMBER;
12 resp_id NUMBER;
13 login_id NUMBER;
14
15 TYPE FLEX IS RECORD (
16 ATTRIBUTE_CATEGORY VARCHAR2(30),
17 ATTRIBUTE1 VARCHAR2(240),
18 ATTRIBUTE2 VARCHAR2(240),
19 ATTRIBUTE3 VARCHAR2(240),
20 ATTRIBUTE4 VARCHAR2(240),
21 ATTRIBUTE5 VARCHAR2(240),
22 ATTRIBUTE6 VARCHAR2(240),
23 ATTRIBUTE7 VARCHAR2(240),
24 ATTRIBUTE8 VARCHAR2(240),
25 ATTRIBUTE9 VARCHAR2(240),
26 ATTRIBUTE10 VARCHAR2(240),
27 ATTRIBUTE11 VARCHAR2(240),
28 ATTRIBUTE12 VARCHAR2(240),
29 ATTRIBUTE13 VARCHAR2(240),
30 ATTRIBUTE14 VARCHAR2(240),
31 ATTRIBUTE15 VARCHAR2(240),
32 ATTRIBUTE16 VARCHAR2(240),
33 ATTRIBUTE17 VARCHAR2(240),
34 ATTRIBUTE18 VARCHAR2(240),
35 ATTRIBUTE19 VARCHAR2(240),
36 ATTRIBUTE20 VARCHAR2(240),
37 ATTRIBUTE21 VARCHAR2(240),
38 ATTRIBUTE22 VARCHAR2(240),
39 ATTRIBUTE23 VARCHAR2(240),
40 ATTRIBUTE24 VARCHAR2(240),
41 ATTRIBUTE25 VARCHAR2(240),
42 ATTRIBUTE26 VARCHAR2(240),
43 ATTRIBUTE27 VARCHAR2(240),
44 ATTRIBUTE28 VARCHAR2(240),
45 ATTRIBUTE29 VARCHAR2(240),
46 ATTRIBUTE30 VARCHAR2(240)
47 );
48
49 /* TYPE status_rec_type added as part of Default Status Build
50 Sriram.S Default Status Build 20Jan2004 Bug# 3408799 */
51 TYPE status_rec_type IS RECORD(
52 ENTITY_STATUS gmd_status.status_code%TYPE,
53 DESCRIPTION gmd_status.description%TYPE,
54 STATUS_TYPE gmd_status.status_type%TYPE
55 );
56
57 /* Sriram.S NPD Convergence. Added the below record type.
58 This type record used as one parameter in fetch procedure */
59 TYPE parameter_rec_type IS RECORD
60 (
61 plant_ind gmd_parameters_hdr.plant_ind%TYPE,
62 lab_ind gmd_parameters_hdr.lab_ind%TYPE,
63 gmd_formula_version_control gmd_parameters_dtl.parameter_value%TYPE,
64 gmd_byproduct_active gmd_parameters_dtl.parameter_value%TYPE,
65 gmd_zero_ingredient_qty gmd_parameters_dtl.parameter_value%TYPE,
66 gmd_mass_um_type gmd_parameters_dtl.parameter_value%TYPE,
67 gmd_volume_um_type gmd_parameters_dtl.parameter_value%TYPE,
68 fm_yield_type gmd_parameters_dtl.parameter_value%TYPE,
69 gmd_default_form_status gmd_parameters_dtl.parameter_value%TYPE,
70 gmi_lotgene_enable_fmsec gmd_parameters_dtl.parameter_value%TYPE,
71 gmd_default_release_type gmd_parameters_dtl.parameter_value%TYPE,
72 gmd_operation_version_control gmd_parameters_dtl.parameter_value%TYPE,
73 gmd_default_oprn_status gmd_parameters_dtl.parameter_value%TYPE,
74 gmd_routing_version_control gmd_parameters_dtl.parameter_value%TYPE,
75 gmd_default_rout_status gmd_parameters_dtl.parameter_value%TYPE,
76 steprelease_type gmd_parameters_dtl.parameter_value%TYPE,
77 gmd_enforce_step_dependency gmd_parameters_dtl.parameter_value%TYPE,
78 gmd_recipe_version_control gmd_parameters_dtl.parameter_value%TYPE,
79 gmd_proc_instr_paragraph gmd_parameters_dtl.parameter_value%TYPE,
80 gmd_default_recp_status gmd_parameters_dtl.parameter_value%TYPE,
81 gmd_default_valr_status gmd_parameters_dtl.parameter_value%TYPE,
82 gmd_default_spec_status gmd_parameters_dtl.parameter_value%TYPE,
83 gmd_cost_source_orgn gmd_parameters_dtl.parameter_value%TYPE,
84 gmd_default_sub_status gmd_parameters_dtl.parameter_value%TYPE,
85 gmd_sub_version_control gmd_parameters_dtl.parameter_value%TYPE,
86 gmd_recipe_type gmd_parameters_dtl.parameter_value%TYPE,
87 fm$default_release_type gmd_parameters_dtl.parameter_value%TYPE );
88
89 /*======================================================================
90 -- PROCEDURE :
91 -- FETCH_PARM_VALUES
92 --
93 -- DESCRIPTION:
94 -- This procedure is used to fetch the parameter values for a
95 -- particular orgn_id. If orgn_id is NULL return the Global orgn. parameters
96 --
97 -- HISTORY
98 -- Sriram.S 14-DEC-2004 Created
99 --===================================================================== */
100
101 PROCEDURE FETCH_PARM_VALUES (P_orgn_id IN NUMBER,
102 X_out_rec OUT NOCOPY GMD_PARAMETERS_DTL_PKG.parameter_rec_type,
103 X_return_status OUT NOCOPY VARCHAR2);
104
105 /*======================================================================
106 -- PROCEDURE :
107 -- FETCH_PARM_VALUES
108 --
109 -- DESCRIPTION:
110 -- This procedure is used to fetch the parameter value of the profile name passed for a
111 -- particular orgn_id. If orgn_id is NULL return the parameter value for Global orgn.
112 --
113 -- HISTORY
114 -- Sriram.S 14-DEC-2004 Created
115 --===================================================================== */
116 PROCEDURE FETCH_PARM_VALUES (P_orgn_id IN NUMBER,
117 P_parm_name IN VARCHAR2,
118 P_parm_value OUT NOCOPY VARCHAR2,
119 X_return_status OUT NOCOPY VARCHAR2);
120
121
122 /*================================================================================
123 Procedure
124 log_message
125 Description
126 This procedure is used accross all the procedures to log a message to the
127 message stack.
128 Parameters
129 p_mesage_code (R) The message which is being put onto the stack.
130 p_token1_name (R) The name of the token1 in the message if any.
131 p_token1_value (R) The value of the token1 in the message if it exists.
132 p_token2_name (R) The name of the token2 in the message if any.
133 p_token2_value (R) The value of the token2 in the message if it exists.
134 p_token3_name (R) The name of the token3 in the message if any.
135 p_token3_value (R) The value of the token3 in the message if it exists.
136 ================================================================================*/
137
138 PROCEDURE log_message (
139 p_message_code IN VARCHAR2
140 ,p_token1_name IN VARCHAR2 := NULL
141 ,p_token1_value IN VARCHAR2 := NULL
142 ,p_token2_name IN VARCHAR2 := NULL
143 ,p_token2_value IN VARCHAR2 := NULL
144 ,p_token3_name IN VARCHAR2 := NULL
145 ,p_token3_value IN VARCHAR2 := NULL);
146
147 /*================================================================================
148 FUNCTION
149 setup
150 Description
151 This function is used accross all the procedures to setup the profile values
152 and constants. This function returns FALSE if any of the constants or profiles
153 are not set properly.
154 Return Values
155 TRUE If the setup is done successfully.
156 FALSE If their are any errors in the setup.
157 ================================================================================*/
158 FUNCTION setup
159 RETURN BOOLEAN;
160
161 /*================================================================================
162 FUNCTION
163 validate_flex_field
164 Description
165 This function is used to validate the attribute value passed in based on the
166 flexfield setup.
167 ================================================================================*/
168 PROCEDURE validate_flex (
169 p_table_name IN VARCHAR2 ,
170 p_flex_record IN gmd_api_grp.flex,
171 x_flex_record IN OUT NOCOPY gmd_api_grp.flex,
172 x_return_status OUT NOCOPY VARCHAR2
173 );
174
175 /* *********************************************************************** *
176 * Function *
177 * Check_orgn_access *
178 * Parameter : Entity_id Number, Entity_name VARCHAR2 *
179 * Description *
180 * Checks if the user has access to the entity organization *
181 * *********************************************************************** */
182 FUNCTION Check_orgn_access(Entity VARCHAR2
183 ,Entity_id NUMBER) RETURN BOOLEAN;
184
185 /* *********************************************************************** *
186 * Function *
187 * OrgnAccessible *
188 * Parameter : powner_orgn_id IN NUMBER
189 * Description *
190 * Checks if the user has access to the entity organization *
191 * *********************************************************************** */
192 FUNCTION OrgnAccessible(powner_orgn_id IN NUMBER) RETURN BOOLEAN;
193
194
195 /* ************************************************************************
196 * Function *
197 * get_object_status_type *
198 * Parameter : Entity_id Number, Entity_name VARCHAR2 *
199 * Description *
200 * Checks if the user has access to the entity organization *
201 * *********************************************************************** */
202 FUNCTION get_object_status_type
203 ( pObject_Name IN VARCHAR2
204 , pObject_Id IN NUMBER)
205 RETURN GMD_STATUS_B.status_type%TYPE;
206
207
208 /***********************************************************************
209 NAME
210 Validate_with_dep_entities
211 SYNOPSIS
212 Proc Validate_with_dep_entities
213 DESCRIPTION
214 ***********************************************************************/
215 PROCEDURE Validate_with_dep_entities(V_type VARCHAR2,
216 V_entity_id NUMBER,
217 X_parent_check OUT NOCOPY BOOLEAN);
218
219 /***********************************************************************
220 NAME
221 get_object_name_version
222 SYNOPSIS
223 Proc get_object_name_version
224 DESCRIPTION
225 ***********************************************************************/
226 FUNCTION get_object_name_version(vEntity IN VARCHAR2
227 ,vEntity_id IN NUMBER
228 ,vtype IN VARCHAR2 DEFAULT 'NAME-VERSION')
229 RETURN VARCHAR2;
230
231 /********************************************************************************
232 * Name : get_formula_acces_type
233 *
234 * Description: Function returns the acces type level of the user for a given formula.
235 * Returns 'U', means user has updatable acces.
236 * Returns 'V', means user has view acces.
237 * Returns 'N', means no security record setup.
238 **********************************************************************************/
239 FUNCTION get_formula_access_type(p_formula_id IN PLS_INTEGER,
240 p_owner_organization_id IN PLS_INTEGER)
241 RETURN VARCHAR2;
242 /* Functions and Procedure added as part of Default Status Build */
243 ------------------------------------------------------------------
244 --Created by : Sriram.S
245 --Date created: 20-JAN-2004
246 --
247 --Purpose: Created as a part of Default Status TD
248 --kkillams 01-DEC-2004 Replaced V_orgn_code with V_orgn_id w.r.t. 4004501
249 -------------------------------------------------------------------
250 FUNCTION get_status_desc (V_entity_status IN VARCHAR2 )
251 RETURN VARCHAR2;
252 PROCEDURE get_status_details (V_entity_type IN VARCHAR2,
253 V_orgn_id IN NUMBER,
254 X_entity_status OUT NOCOPY GMD_API_GRP.status_rec_type);
255 FUNCTION check_dependent_status (V_entity_type IN VARCHAR2,
256 V_entity_id IN NUMBER,
257 V_entity_status IN VARCHAR2)
258 RETURN BOOLEAN;
259
260 /*================================================================================
261 Procedure
262 set_activity_sequence_num
263 Description
264 This procedure is used to renumber the activities.
265 Parameters
266 p_oprn_id (R) Operation for which the activities are being renumbered.
267 p_user_id (R) Parameter for updating the last updated by.
268 p_login_id (R) Parameter for updating the login id column.
269 ================================================================================*/
270 PROCEDURE set_activity_sequence_num(
271 P_oprn_id IN NUMBER,
272 P_user_id IN NUMBER,
273 P_login_id IN NUMBER
274 );
275
276
277 /* Functions and Procedure added as part of Recipe Generation Build */
278 ------------------------------------------------------------------
279 --Created by : G.Kelly
280 --Date created: 7-MAY-2004
281 --
282 --Purpose: Created as a part of Recipe Generation TD
283 --History:
284 -- Kapil M 03-Jan-2007 LCF-GMO ME : Bug#5458666. Added routing_id
285 --Parameters: p_formula_id Formula indicator
286 -------------------------------------------------------------------
287 PROCEDURE retrieve_recipe(p_formula_id IN NUMBER,
288 p_routing_id IN NUMBER DEFAULT NULL,
289 l_recipe_tbl OUT NOCOPY GMD_RECIPE_HEADER.recipe_hdr,
290 l_recipe_flex OUT NOCOPY GMD_RECIPE_HEADER.flex,
291 x_return_status OUT NOCOPY VARCHAR2);
292
293 PROCEDURE retrieve_vr(p_formula_id IN NUMBER,
294 l_recipe_vr_tbl OUT NOCOPY GMD_RECIPE_DETAIL.recipe_vr,
295 l_vr_flex OUT NOCOPY GMD_RECIPE_DETAIL.flex,
296 x_return_status OUT NOCOPY VARCHAR2,
297 p_recipe_use IN NUMBER DEFAULT NULL);
298
299 FUNCTION check_orgn_status (V_organization_id IN NUMBER) RETURN BOOLEAN;
300
301
302 PROCEDURE check_item_exists (p_formula_id IN NUMBER,
303 x_return_status OUT NOCOPY VARCHAR2,
304 p_organization_id IN NUMBER DEFAULT NULL,
305 p_orgn_code IN VARCHAR2 DEFAULT NULL,
306 p_production_check IN BOOLEAN DEFAULT FALSE,
307 p_costing_check IN BOOLEAN DEFAULT FALSE);
308
309 /************************************************************************ *
310 * Function *
311 * Validate_um *
312 * Parameter : item_uom_code IN varchar2 *
313 * Description *
314 * Checks if the uom_code passed is valid - Return True if it exists *
315 * *********************************************************************** */
316 FUNCTION Validate_um(pItem_uom_code IN VARCHAR2) RETURN BOOLEAN;
317
318 -- Bug number 4252212
319 FUNCTION derive_ingredent_end (p_substitution_id IN NUMBER DEFAULT NULL,
320 p_item_id IN NUMBER DEFAULT NULL,
321 p_exclude_context IN VARCHAR2 DEFAULT 'N' ) RETURN DATE;
322 PROCEDURE update_end_date (p_substitution_id IN NUMBER);
323
324 FUNCTION get_message RETURN Varchar2;
325
326 FUNCTION get_recipe_type (p_organization_id IN NUMBER) RETURN NUMBER;
327 /*================================================================================
328 FUNCTION
329 get_def_status_code
330 KSHUKLA bug 5199586
331 Description
332 THis function takes the input parameter as entity type and organziation code
333
334 Return Values
335 NUMBER - Default status code as defined by the user.
336 ================================================================================*/
337 FUNCTION get_def_status_code(p_entity_type varchar2,
338 p_orgn_id NUMBER)
339 RETURN NUMBER;
340
341
342 /*+========================================================================+
343 ** Name : validity_revision_check
344 ** Notes : This procedure checks if the passed in item has revision
345 ** associated with it in the given formula. It returns "Y" if
346 ** the item is defined as a product with revision in the formula.
347 ** Also, it returns the revision value, if there is a single
348 ** revision for the item in the formula.
349 +========================================================================+*/
350 PROCEDURE validity_revision_check (p_formula_id IN NUMBER,
351 p_organization_id IN NUMBER,
352 p_inventory_item_id IN NUMBER,
353 x_enable_revision OUT NOCOPY VARCHAR2,
354 x_revision OUT NOCOPY VARCHAR2);
355
356 END gmd_api_grp;