1 PACKAGE GMD_MBR_UTIL_PKG AUTHID CURRENT_USER AS
2 /* $Header: GMDMBRUS.pls 120.2 2005/08/21 20:04 srpuri noship $ */
3 /***********************************************************************************
4 ** If you are planning to use routine form this package in other places
5 ** then make sure it satisfies your requirement. If you are changing any parameters
6 ** data types make sure gmo MBR map in sync with this package code.
7 ************************************************************************************/
8
9 /**********************************************************************************
10 ** This Procedure is to retrieve Organization specific Activity overrides
11 ** for a recipe for MBR XML Gateway Map.
12 ** IN Parameters:
13 ** p_RECIPE_ID number -- Recipe ID
14 ** p_ROUTINGSTEP_ID number
15 ** p_OPRN_LINE_ID number
16 ** p_DOCUMENT_id VARCHAR2 -- Document id is one of following
17 ** combination
18 ** RecipeID-OraginizationID-ValidityRuleID
19 ** RecipeID-OraginizationID
20 ** p_Default_factor number -- Acitvity Factor
21 ** OUT Parameter
22 ** x_org_specific_factor OUT NUMBER -- This Org Specific Activity Factor
23 ** if no org specific Activity Factor
24 ** is defined then p_Default_factor value
25 ** will be passed as Activity Factor
26 ***********************************************************************************/
27
28 PROCEDURE GET_ACTIVITY_FACTOR(p_RECIPE_ID number,
29 p_ROUTINGSTEP_ID number,
30 p_OPRN_LINE_ID number,
31 p_DOCUMENT_id VARCHAR2,
32 p_Default_factor number,
33 x_org_specific_factor OUT NOCOPY NUMBER);
34
35 /**********************************************************************************
36 ** This Procedure is to retrieve Organization specific Resource Overrides for MBR
37 ** XML Gateway Map. If No Org Specific Overrides are defined then this procedure
38 ** returns values passed in as out values
39 ** IN Parameters:
40 ** P_RECIPE_ID number
41 ** P_ROUTINGSTEP_ID number
42 ** P_OPRN_LINE_ID number
43 ** P_RESOURCES VARCHAR2
44 ** P_PROCESS_UOM VARCHAR2
45 ** P_USAGE_UM VARCHAR2
46 ** P_RESOURCE_USAGE number
47 ** P_PROCESS_QTY number
48 ** P_DOCUMENT_ID VARCHAR2-- Document id is one of following
49 ** combination
50 ** RecipeID-OraginizationID-ValidityRuleID
51 ** RecipeID-OraginizationID
52 ** P_MIN_CAPACITY number
53 ** P_MAX_CAPACITY number
54 ** OUT Parameters
55 ** x_PROCESS_UOM OUT VARCHAR2
56 ** x_USAGE_UM OUT VARCHAR2
57 ** x_RESOURCE_USAGE OUT VARCHAR2
58 ** x_PROCESS_QTY OUT number
59 ** x_MIN_CAPACITY OUT number
60 ** x_MAX_CAPACITY OUT number)
61 ***********************************************************************************/
62
63 PROCEDURE GET_RECIPE_RSRC_ORGN_OVERRIDES(
64 P_RECIPE_ID number
65 ,P_ROUTINGSTEP_ID number
66 ,P_OPRN_LINE_ID number
67 ,P_RESOURCES VARCHAR2
68 ,P_PROCESS_UOM VARCHAR2
69 ,P_USAGE_UM VARCHAR2
70 ,P_RESOURCE_USAGE number
71 ,P_PROCESS_QTY number
72 ,P_DOCUMENT_ID VARCHAR2
73 ,P_MIN_CAPACITY number
74 ,P_MAX_CAPACITY number
75 ,P_CAPACITY_UM VARCHAR2
76 ,x_PROCESS_UOM OUT NOCOPY VARCHAR2
77 ,x_USAGE_UM OUT NOCOPY VARCHAR2
78 ,x_RESOURCE_USAGE OUT NOCOPY VARCHAR2
79 ,x_PROCESS_QTY OUT NOCOPY number
80 ,x_MIN_CAPACITY OUT NOCOPY number
81 ,x_MAX_CAPACITY OUT NOCOPY number
82 ,x_CAPACITY_UM OUT NOCOPY VARCHAR2);
83
84 /**********************************************************************************
85 ** This Function is a Returns Sampling Plan ID for the given input parameters
86 ** IN Parameters
87 ** P_INVENTORY_ITEM_ID IN NUMBER
88 ** P_GRADE_CODE IN VARCHAR2
89 ** P_ORGANIZATION_ID IN VARCHAR2
90 ** P_BATCH_ID IN VARCHAR2
91 ** P_RECIPE_ID IN VARCHAR2
92 ** P_RECIPE_NO IN VARCHAR2
93 ** P_RECIPE_VERSION IN VARCHAR2
94 ** P_FORMULA_ID IN VARCHAR2
95 ** P_FORMULALINE_ID IN VARCHAR2
96 ** P_FORMULA_NO IN VARCHAR2
97 ** P_FORMULA_VERS IN VARCHAR2
98 ** P_ROUTING_ID IN VARCHAR2
99 ** P_ROUTING_NO IN VARCHAR2
100 ** P_ROUTING_VERS IN VARCHAR2
101 ** P_STEP_ID IN VARCHAR2
102 ** P_STEP_NO IN VARCHAR2
103 ** P_OPRN_ID IN VARCHAR2
104 ** P_OPRN_NO IN VARCHAR2
105 ** P_OPRN_VERS IN VARCHAR2
106 ** P_CHARGE IN VARCHAR2
107 ** P_DATE_EFFECTIVE IN DATE
108 ** P_EXACT_MATCH IN VARCHAR2
109 ** P_LOT_NUMBER IN VARCHAR2
110 ** P_FIND_SPEC_WITH_STEP IN VARCHAR2)
111 ** OUT Parameter
112 ** This functional call returns sampling plan Id
113 ***********************************************************************************/
114
115 FUNCTION GET_SAMPLING_PLAN(P_INVENTORY_ITEM_ID IN NUMBER
116 ,P_GRADE_CODE IN VARCHAR2
117 ,P_ORGANIZATION_ID IN VARCHAR2
118 ,P_BATCH_ID IN VARCHAR2
119 ,P_RECIPE_ID IN VARCHAR2
120 ,P_RECIPE_NO IN VARCHAR2
121 ,P_RECIPE_VERSION IN VARCHAR2
122 ,P_FORMULA_ID IN VARCHAR2
123 ,P_FORMULALINE_ID IN VARCHAR2
124 ,P_FORMULA_NO IN VARCHAR2
125 ,P_FORMULA_VERS IN VARCHAR2
126 ,P_ROUTING_ID IN VARCHAR2
127 ,P_ROUTING_NO IN VARCHAR2
128 ,P_ROUTING_VERS IN VARCHAR2
129 ,P_STEP_ID IN VARCHAR2
130 ,P_STEP_NO IN VARCHAR2
131 ,P_OPRN_ID IN VARCHAR2
132 ,P_OPRN_NO IN VARCHAR2
133 ,P_OPRN_VERS IN VARCHAR2
134 ,P_CHARGE IN VARCHAR2
135 ,P_DATE_EFFECTIVE IN DATE
136 ,P_EXACT_MATCH IN VARCHAR2
137 ,P_LOT_NUMBER IN VARCHAR2
138 ,P_FIND_SPEC_WITH_STEP IN VARCHAR2) RETURN NUMBER;
139
140 /**********************************************************************************
141 ** This Procedure is to retrieve org specific Process Parameter Overrides for
142 ** MBR XML Gateway Map. If No Org Specific Overrides are defined then this procedure
143 ** returns values passed in as out values
144 ** IN Parameters
145 ** p_RECIPE_ID number,
146 ** p_ROUTINGSTEP_ID number,
147 ** p_OPRN_LINE_ID number,
148 ** p_resource VARCHAR2,
149 ** p_DOCUMENT_id VARCHAR2 -- Document id is one of following
150 ** combination
151 ** RecipeID-OraginizationID-ValidityRuleID
152 ** RecipeID-OraginizationID
153 ** P_PARAMETER_ID Number,
154 ** P_TARGET_VALUE VARCHAR2,
155 ** P_MINIMUM_VALUE Number,
156 ** P_MAXIMUM_VALUE Number,
157 **
158 ** OUT Parameters
159 **
160 ** X_TARGET_VALUE OUT VARCHAR2,
161 ** X_MINIMUM_VALUE OUT Number,
162 ** X_MAXIMUM_VALUE OUT Number
163 **
164 **
165 ***********************************************************************************/
166
167 PROCEDURE Get_ORGN_Process_parameters(p_RECIPE_ID number,
168 p_ROUTINGSTEP_ID number,
169 p_OPRN_LINE_ID number,
170 p_resource VARCHAR2,
171 p_DOCUMENT_id VARCHAR2,
172 P_PARAMETER_ID Number,
173 P_TARGET_VALUE VARCHAR2,
174 P_MINIMUM_VALUE Number,
175 P_MAXIMUM_VALUE Number,
176 X_TARGET_VALUE OUT NOCOPY VARCHAR2,
177 X_MINIMUM_VALUE OUT NOCOPY Number,
178 X_MAXIMUM_VALUE OUT NOCOPY Number,
179 X_PARAMETER_NAME OUT NOCOPY VARCHAR2);
180
181 /**********************************************************************************
182 ** This function returns calculated step qty if ASQC flag is on for the recipe.
183 ** Otherwise it returns passed in step Qty
184 ** IN Parameters
185 ** p_RECIPE_ID number
186 ** p_ROUTINGSTEP_ID number
187 ** p_step_qty number
188 **
189 ** OUT Parameters
190 ** This function returns calculated step qty if ASQC is on
191 **
192 ***********************************************************************************/
193
194 Function Get_Step_qty (p_RECIPE_ID number,
195 p_ROUTINGSTEP_ID number,
196 p_step_qty number) return number;
197
198 END GMD_MBR_UTIL_PKG;