1 PACKAGE GMD_RECIPE_HEADER AUTHID CURRENT_USER AS
2 /* $Header: GMDPRCHS.pls 120.7.12020000.2 2012/09/29 09:31:44 yanpewan ship $ */
3 /*#
4 * This interface is used to create, update and delete Recipe Headers.
5 * This package defines and implements the procedures and datatypes
6 * required to create, update and delete recipe header information.
7 * @rep:scope public
8 * @rep:product GMD
9 * @rep:lifecycle active
10 * @rep:displayname Recipe Header API
11 * @rep:compatibility S
12 * @rep:category BUSINESS_ENTITY GMD_RECIPE
13 */
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 UPDATE_FLEX IS RECORD (
50 ATTRIBUTE_CATEGORY VARCHAR2(30) ,
51 ATTRIBUTE1 VARCHAR2(240) ,
52 ATTRIBUTE2 VARCHAR2(240) ,
53 ATTRIBUTE3 VARCHAR2(240) ,
54 ATTRIBUTE4 VARCHAR2(240) ,
55 ATTRIBUTE5 VARCHAR2(240) ,
56 ATTRIBUTE6 VARCHAR2(240) ,
57 ATTRIBUTE7 VARCHAR2(240) ,
58 ATTRIBUTE8 VARCHAR2(240) ,
59 ATTRIBUTE9 VARCHAR2(240) ,
60 ATTRIBUTE10 VARCHAR2(240) ,
61 ATTRIBUTE11 VARCHAR2(240) ,
62 ATTRIBUTE12 VARCHAR2(240) ,
63 ATTRIBUTE13 VARCHAR2(240) ,
64 ATTRIBUTE14 VARCHAR2(240) ,
65 ATTRIBUTE15 VARCHAR2(240) ,
66 ATTRIBUTE16 VARCHAR2(240) ,
67 ATTRIBUTE17 VARCHAR2(240) ,
68 ATTRIBUTE18 VARCHAR2(240) ,
69 ATTRIBUTE19 VARCHAR2(240) ,
70 ATTRIBUTE20 VARCHAR2(240) ,
71 ATTRIBUTE21 VARCHAR2(240) ,
72 ATTRIBUTE22 VARCHAR2(240) ,
73 ATTRIBUTE23 VARCHAR2(240) ,
74 ATTRIBUTE24 VARCHAR2(240) ,
75 ATTRIBUTE25 VARCHAR2(240) ,
76 ATTRIBUTE26 VARCHAR2(240) ,
77 ATTRIBUTE27 VARCHAR2(240) ,
78 ATTRIBUTE28 VARCHAR2(240) ,
79 ATTRIBUTE29 VARCHAR2(240) ,
80 ATTRIBUTE30 VARCHAR2(240)
81 );
82
83 TYPE RECIPE_HDR IS RECORD (
84 RECIPE_ID NUMBER(15)
85 ,RECIPE_DESCRIPTION GMD_RECIPES_TL.RECIPE_DESCRIPTION%TYPE -- Bug# 14480509
86 ,RECIPE_NO VARCHAR2(32)
87 ,RECIPE_VERSION NUMBER(5)
88 ,USER_ID FND_USER.user_id%TYPE
89 ,USER_NAME FND_USER.user_name%TYPE
90 ,OWNER_ORGN_CODE VARCHAR2(4)
91 ,CREATION_ORGN_CODE VARCHAR2(4)
92 ,OWNER_ORGANIZATION_ID NUMBER
93 ,CREATION_ORGANIZATION_ID NUMBER
94 ,FORMULA_ID FM_FORM_MST.formula_id%TYPE
95 ,FORMULA_NO FM_FORM_MST.formula_no%TYPE
96 ,FORMULA_VERS FM_FORM_MST.formula_vers%TYPE
97 ,ROUTING_ID NUMBER
98 ,ROUTING_NO FM_ROUT_HDR.routing_no%TYPE
99 ,ROUTING_VERS FM_ROUT_HDR.routing_vers%TYPE
100 ,PROJECT_ID NUMBER(15)
101 ,RECIPE_STATUS VARCHAR2(30) := '100'
102 ,PLANNED_PROCESS_LOSS NUMBER := 0
103 ,TEXT_CODE NUMBER(10)
104 ,DELETE_MARK NUMBER(5) := 0
105 ,CONTIGUOUS_IND NUMBER
106 ,ENHANCED_PI_IND VARCHAR2(1)
107 ,RECIPE_TYPE NUMBER
108 ,CREATION_DATE DATE
109 ,CREATED_BY NUMBER(15)
110 ,LAST_UPDATED_BY NUMBER(15)
111 ,LAST_UPDATE_DATE DATE
112 ,LAST_UPDATE_LOGIN NUMBER(15)
113 ,OWNER_ID NUMBER(15)
114 ,OWNER_LAB_TYPE VARCHAR2(4)
115 ,CALCULATE_STEP_QUANTITY NUMBER(5)
116 ,FIXED_PROCESS_LOSS NUMBER /* B6811759 */
117 ,FIXED_PROCESS_LOSS_UOM VARCHAR2(3) /* B6811759 */
118 );
119
120 /* define this record for calculating charges */
121 TYPE CHARGE_REC IS RECORD (
122 RoutingStep_id NUMBER,
123 Max_Capacity NUMBER,
124 charge INTEGER
125 );
126
127 TYPE PROCESS_LOSS_REC IS RECORD (
128 qty NUMBER := 0 ,
129 Recipe_id NUMBER ,
130 Formula_id NUMBER ,
131 Routing_id NUMBER
132 );
133
134 /* All table definitions */
135 TYPE recipe_tbl IS TABLE OF RECIPE_HDR
136 INDEX BY BINARY_INTEGER;
137
138 TYPE recipe_flex IS TABLE OF FLEX
139 INDEX BY BINARY_INTEGER;
140
141 TYPE recipe_update_flex IS TABLE OF UPDATE_FLEX
142 INDEX BY BINARY_INTEGER;
143
144 /* Define a table type for charges */
145 TYPE charge_tbl IS TABLE OF CHARGE_REC
146 INDEX BY BINARY_INTEGER;
147
148 /*#
149 * Inserts Recipe Header
150 * This is a PL/SQL procedure to create a recipe header.
151 * Call is made to create_recipe_header API of GMD_RECIPE_HEADER_PVT package.
152 * @param p_api_version API version field
153 * @param p_init_msg_list Flag to check if message list intialized
154 * @param p_commit Flag to check for commit
155 * @param p_called_from_forms Flag to check if API is called from a form
156 * @param x_return_status 'S'-Success, 'E'-Error, 'U'-Unexpected Error
157 * @param x_msg_count Number of msg's on message stack
158 * @param x_msg_data Actual message data on message stack
159 * @param p_recipe_header_tbl Table structure of recipe header
160 * @param p_recipe_header_flex Table structure of flex fields
161 * @rep:scope public
162 * @rep:lifecycle active
163 * @rep:displayname Create Recipe Header procedure
164 * @rep:compatibility S
165 */
166 PROCEDURE CREATE_RECIPE_HEADER
167 (p_api_version IN NUMBER
168 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
169 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
170 ,p_called_from_forms IN VARCHAR2 := 'NO'
171 ,x_return_status OUT NOCOPY VARCHAR2
172 ,x_msg_count OUT NOCOPY NUMBER
173 ,x_msg_data OUT NOCOPY VARCHAR2
174 ,p_recipe_header_tbl IN recipe_tbl
175 ,p_recipe_header_flex IN recipe_flex
176 );
177
178 /*#
179 * Updates Recipe Header
180 * This is a PL/SQL procedure to update a recipe header.
181 * Call is made to update_recipe_header of GMD_RECIPE_HEADER_PVT package.
182 * @param p_api_version API version field
183 * @param p_init_msg_list Flag to check if message list intialized
184 * @param p_commit Flag to check for commit
185 * @param p_called_from_forms Flag to check if API is called from a form
186 * @param x_return_status 'S'-Success, 'E'-Error, 'U'-Unexpected Error
187 * @param x_msg_count Number of msg's on message stack
188 * @param x_msg_data Actual message data on message stack
189 * @param p_recipe_header_tbl Table structure of recipe header
190 * @param p_recipe_update_flex Table structure of flex fields
191 * @rep:scope public
192 * @rep:lifecycle active
193 * @rep:displayname Update Recipe Header procedure
194 * @rep:compatibility S
195 */
196 PROCEDURE UPDATE_RECIPE_HEADER
197 ( p_api_version IN NUMBER
198 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
199 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
200 ,p_called_from_forms IN VARCHAR2 := 'NO'
201 ,x_return_status OUT NOCOPY VARCHAR2
202 ,x_msg_count OUT NOCOPY NUMBER
203 ,x_msg_data OUT NOCOPY VARCHAR2
204 ,p_recipe_header_tbl IN recipe_tbl
205 ,p_recipe_update_flex IN recipe_update_flex
206 );
207
208 /*#
209 * Deletes Recipe Header
210 * This is a PL/SQL procedure to delete a recipe header
211 * Delete in OPM world is not a physical delete. Its a logical delete
212 * (i.e) its an update with the delete_mark set to 1. Therefore prior to
213 * calling this procedure the delete_mark needs to be set to 1.
214 * @param p_api_version API version field
215 * @param p_init_msg_list Flag to check if message list intialized
216 * @param p_commit Flag to check for commit
217 * @param p_called_from_forms Flag to check if API is called from a form
218 * @param x_return_status 'S'-Success, 'E'-Error, 'U'-Unexpected Error
219 * @param x_msg_count Number of msg's on message stack
220 * @param x_msg_data Actual message data on message stack
221 * @param p_recipe_header_tbl Table structure of recipe header
222 * @param p_recipe_update_flex Table structure of flex fields
223 * @rep:scope public
224 * @rep:lifecycle active
225 * @rep:displayname Delete Recipe Header procedure
226 * @rep:compatibility S
227 */
228 PROCEDURE DELETE_RECIPE_HEADER
229 ( p_api_version IN NUMBER
230 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
231 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
232 ,p_called_from_forms IN VARCHAR2 := 'NO'
233 ,x_return_status OUT NOCOPY VARCHAR2
234 ,x_msg_count OUT NOCOPY NUMBER
235 ,x_msg_data OUT NOCOPY VARCHAR2
236 ,p_recipe_header_tbl IN recipe_tbl
237 ,p_recipe_update_flex IN recipe_update_flex
238 );
239
240 END;