[Home] [Help]
PACKAGE BODY: APPS.GMD_EBI_RECIPE_PUB
Source
1 PACKAGE BODY GMD_EBI_RECIPE_PUB AS
2 /* $Header: GMDEIPRB.pls 120.2.12020000.2 2012/07/26 09:44:22 rborpatl noship $ */
3
4 /* ======================================================================== */
5
6 /* FUNCTION: */
7
8 /* item_exists_in_org */
9
10 /* */
11
12 /* DESCRIPTION: */
13
14 /* This function checks if item is present in an organization */
15
16 /* ======================================================================== */
17
18
19
20 FUNCTION item_exists_in_org(p_item_id IN NUMBER,p_org_id IN NUMBER)
21 RETURN VARCHAR2
22 IS
23 CURSOR check_item_exists IS
24 SELECT inventory_item_id FROM mtl_system_items_b WHERE
25 inventory_item_id= p_item_id AND organization_id= p_org_id;
26 l_item_id NUMBER;
27 BEGIN
28 IF (check_item_exists%ISOPEN) THEN
29 CLOSE check_item_exists;
30 END IF;
31 OPEN check_item_exists;
32 FETCH check_item_exists INTO l_item_id;
33 IF ( check_item_exists%NOTFOUND) THEN
34 RETURN FND_API.G_FALSE;
35 ELSE
36 RETURN FND_API.G_TRUE;
37 END IF;
38 CLOSE check_item_exists;
39 EXCEPTION
40 WHEN OTHERS THEN
41 IF (check_item_exists%ISOPEN) THEN
42 CLOSE check_item_exists;
43 END IF;
44 RETURN FND_API.G_FALSE;
45 END;
46
47 /* ======================================================================== */
48
49 /* PROCEDURE: */
50
51 /* assign_items_to_org */
52
53 /* */
54
55 /* DESCRIPTION: */
56
57 /* This procedure assigns formula material items to child organization */
58
59 /* ======================================================================== */
60
61 PROCEDURE assign_items_to_org(
62 p_formula_obj IN gmd_ebi_formula_obj
63 ,x_return_status OUT NOCOPY VARCHAR2
64 ,x_msg_data OUT NOCOPY VARCHAR2
65 )
66 IS
67 l_org_id_tbl inv_ebi_org_tbl;
68 l_org_id_obj inv_ebi_org_obj;
69 l_out inv_ebi_item_output_obj;
70 l_init_msg_list VARCHAR2(1);
71 l_temp VARCHAR(1);
72 BEGIN
73 IF p_formula_obj IS NOT NULL THEN
74 FOR j in 1..p_formula_obj.formula_details.COUNT LOOP
75 IF( inv_ebi_UTIL.is_master_org (
76 p_formula_obj.formula_details(j).owner_organization_id) <> FND_API.g_true) THEN
77 IF(item_exists_in_org( p_formula_obj.formula_details(j).inventory_item_id,p_formula_obj.formula_details(j).owner_organization_id) = FND_API.g_false) THEN
78 l_org_id_obj := inv_ebi_org_obj(p_formula_obj.formula_details(j).owner_organization_id,
79 p_formula_obj.formula_details(j).owner_organization_code);
80 l_org_id_tbl := inv_ebi_org_tbl();
81 l_org_id_tbl.extend();
82 l_org_id_tbl(1) := l_org_id_obj;
83 inv_ebi_ITEM_HELPER.process_org_id_assignments(
84 p_init_msg_list => FND_API.G_FALSE
85 ,p_commit => FND_API.G_FALSE
86 ,p_inventory_item_id => p_formula_obj.formula_details(j).inventory_item_id
87 ,p_item_number => p_formula_obj.formula_details(j).item_no
88 ,p_org_id_tbl=>l_org_id_tbl
89 ,x_out=>l_out
90 );
91 SELECT recipe_enabled_flag INTO l_temp FROM mtl_system_items_b WHERE inventory_item_id=p_formula_obj.formula_details(j).inventory_item_id
92 AND organization_id=p_formula_obj.formula_details(j).owner_organization_id;
93
94 IF (l_out.output_status.return_status NOT IN(FND_API.G_RET_STS_SUCCESS,'Q')) THEN
95 x_msg_data := l_out.output_status.msg_data || ' FOR FORMULA' || p_formula_obj.formula_header.FORMULA_NO || p_formula_obj.formula_details(j).LINE_NO || p_formula_obj.formula_details(j).LINE_TYPE;
96 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
97 END IF;
98 END IF;
99 END IF;
100 END LOOP;
101 END IF;
102 x_return_status := FND_API.G_RET_STS_SUCCESS;
103 EXCEPTION
104 WHEN OTHERS THEN
105 x_return_status := FND_API.G_RET_STS_ERROR;
106 END;
107
108 /* ======================================================================== */
109
110 /* PROCEDURE: */
111
112 /* update_org_ids */
113
114 /* */
115
116 /* DESCRIPTION: */
117
118 /* This procedure converts organization code to organization ids. */
119
120 /* ======================================================================== */
121
122 PROCEDURE update_org_ids(
123 p_formula_obj IN gmd_ebi_formula_obj
124 ,x_formula_obj OUT NOCOPY gmd_ebi_formula_obj
125 ,x_msg_count OUT NOCOPY NUMBER
126 ,x_return_status OUT NOCOPY VARCHAR2
127 ,x_msg_data OUT NOCOPY VARCHAR2
128 )
129 IS
130 l_org_id NUMBER;
131 l_formula_obj gmd_ebi_formula_obj;
132 BEGIN
133 x_return_status := FND_API.G_RET_STS_SUCCESS;
134 l_formula_obj := p_formula_obj;
135 IF l_formula_obj IS NOT NULL THEN
136 SELECT organization_id INTO l_org_id FROM mtl_parameters
137 WHERE organization_code = l_formula_obj.formula_header.OWNER_ORGANIZATION_CODE;
138 l_formula_obj.formula_header.OWNER_ORGANIZATION_ID := l_org_id;
139 FOR j in 1..l_formula_obj.formula_details.COUNT LOOP
140 l_formula_obj.formula_details(j).OWNER_ORGANIZATION_ID := l_org_id;
141 END LOOP;
142 END IF;
143 x_formula_obj := l_formula_obj;
144 EXCEPTION
145 WHEN OTHERS THEN
146 x_return_status := FND_API.G_RET_STS_ERROR;
147 FND_MESSAGE.set_name('INV','INV_EBI_ORG_CODE_INVALID');
148 FND_MESSAGE.set_token('COL_VALUE', p_formula_obj.formula_header.OWNER_ORGANIZATION_CODE);
149 FND_MSG_PUB.add;
150 FND_MSG_PUB.count_and_get(
151 p_encoded => FND_API.g_false
152 ,p_count => x_msg_count
153 ,p_data => x_msg_data
154 );
155 END;
156
157 PROCEDURE update_item_ids(
158 p_formula_obj IN gmd_ebi_formula_obj
159 ,x_formula_obj OUT NOCOPY gmd_ebi_formula_obj
160 ,x_return_status OUT NOCOPY VARCHAR2
161 ,x_msg_data OUT NOCOPY VARCHAR2
162 )
163 IS
164 l_item_id NUMBER;
165 l_formula_obj gmd_ebi_formula_obj;
166
167 BEGIN
168 x_return_status := FND_API.G_RET_STS_SUCCESS;
169 l_formula_obj := p_formula_obj;
170 IF l_formula_obj IS NOT NULL THEN
171 FOR j in 1..l_formula_obj.formula_details.COUNT LOOP
172 IF (l_formula_obj.formula_details(j).inventory_item_id IS NULL) THEN
173 SELECT inventory_item_id INTO l_item_id FROM mtl_system_items_kfv
174 WHERE concatenated_segments = l_formula_obj.formula_details(j).item_no
175 AND organization_id = l_formula_obj.formula_details(j).owner_organization_id;
176 l_formula_obj.formula_details(j).inventory_item_id := l_item_id;
177 END IF;
178 END LOOP;
179 END IF;
180 x_formula_obj := l_formula_obj;
181 EXCEPTION
182 WHEN OTHERS THEN
183 x_return_status := FND_API.G_RET_STS_ERROR;
184 x_msg_data := SQLERRM || 'AT GET ORG IDS FROM ORG CODE';
185 END;
186
187
188 /* ======================================================================== */
189
190 /* PROCEDURE: */
191
192 /* update_org_ids */
193
194 /* */
195
196 /* DESCRIPTION: */
197
198 /* This procedure populates the output object with the entity ids and */
199
200 /* corresponding business component ids. */
201
202 /* ======================================================================== */
203
204 PROCEDURE populate_output(
205 p_input IN gmd_ebi_formula_obj
206 ,p_line_delete_tbl IN gmd_ebi_formula_line_del_tbl
207 ,status_flag IN VARCHAR2
208 ,x_output OUT NOCOPY gmd_ebi_formula_output_obj
209 ,x_return_status OUT NOCOPY VARCHAR2
210 ,x_msg_count OUT NOCOPY NUMBER
211 ,x_msg_data OUT NOCOPY VARCHAR2
212 )
213 IS
214 l_out_obj gmd_ebi_formula_output_obj;
215 l_out_line_sync_obj gmd_ebi_formula_line_sync_obj;
216 l_out_line_sync_tbl gmd_ebi_formula_line_sync_tbl := gmd_ebi_formula_line_sync_tbl();
217 l_form_id NUMBER;
218 l_form_line_id NUMBER;
219 line_ctr NUMBER := 1;
220 BEGIN
221 IF ( status_flag IN ( FND_API.G_RET_STS_SUCCESS,'Q')) THEN
222 IF ( p_input.formula_header.FORMULA_ID IS NULL ) THEN
223 SELECT Max(formula_id) INTO l_form_id FROM fm_form_mst_b WHERE formula_no = p_input.formula_header.formula_no;
224 ELSE
225 l_form_id := p_input.formula_header.FORMULA_ID;
226 END IF;
227 FOR j IN 1..p_input.formula_details.Count LOOP
228 SELECT formulaline_id INTO l_form_line_id FROM fm_matl_dtl WHERE formula_id = l_form_id
229 AND line_type=p_input.formula_details(j).line_type AND line_no = p_input.formula_details(j).line_no;
230 l_out_line_sync_obj := gmd_ebi_formula_line_sync_obj(l_form_line_id,p_input.formula_details(j).business_component_id);
231 l_out_line_sync_tbl.extend(1);
232 l_out_line_sync_tbl(line_ctr) := l_out_line_sync_obj;
233 line_ctr := line_ctr + 1;
234 END LOOP;
235 l_out_obj := gmd_ebi_formula_output_obj(p_input.formula_header.business_component_id,l_form_id,l_out_line_sync_tbl,p_line_delete_tbl);
236 x_output := l_out_obj;
237 x_return_status := FND_API.G_RET_STS_SUCCESS;
238 x_msg_data := NULL;
239 ELSE
240 FOR j IN 1..p_input.formula_details.Count LOOP
241 l_out_line_sync_obj := gmd_ebi_formula_line_sync_obj(NULL,p_input.formula_details(j).business_component_id);
242 l_out_line_sync_tbl.extend(1);
243 l_out_line_sync_tbl(line_ctr) := l_out_line_sync_obj;
244 line_ctr := line_ctr + 1;
245 END LOOP;
246 l_out_obj := gmd_ebi_formula_output_obj(p_input.formula_header.business_component_id,NULL,l_out_line_sync_tbl,NULL);
247 x_output := l_out_obj;
248 x_return_status := FND_API.G_RET_STS_SUCCESS;
249 x_msg_data := NULL;
250 END IF;
251 EXCEPTION
252 WHEN OTHERS THEN
253 x_return_status := FND_API.G_RET_STS_ERROR;
254 x_msg_data := SQLERRM;
255 END;
256
257 FUNCTION get_org_id(p_org_code IN VARCHAR2)
258 RETURN NUMBER
259 IS
260 l_org_id NUMBER;
261 BEGIN
262 SELECT organization_id INTO l_org_id
263 FROM MTL_PARAMETERS
264 WHERE organization_code=p_org_code;
265 RETURN l_org_id;
266 EXCEPTION
267 WHEN OTHERS THEN
268 RETURN NULL;
269 END;
270
271 /* ======================================================================== */
272
273 /* PROCEDURE: */
274
275 /* sync_routings */
276
277 /* */
278
279 /* DESCRIPTION: */
280
281 /* The procedure creates/updates routings and routing step dependencies */
282
283 /* ======================================================================== */
284
285 PROCEDURE sync_routings(
286 p_routing_obj IN gmd_ebi_mfg_routing_obj
287 ,p_commit IN VARCHAR2
288 ,x_return_status OUT NOCOPY VARCHAR2
289 ,x_msg_count OUT NOCOPY NUMBER
290 ,x_msg_data OUT NOCOPY VARCHAR2
291 )
292 IS
293 l_routings_insert gmd_routings%ROWTYPE;
294 l_routings_step_rec fm_rout_dtl%ROWTYPE;
295 l_routings_step_dep_rec fm_rout_dep%ROWTYPE;
296 l_routings_step_tbl_insert GMD_ROUTINGS_PUB.gmd_routings_step_tab;
297 l_routings_step_dep_tbl_insert GMD_ROUTINGS_PUB.gmd_routings_step_dep_tab;
298 l_routings_step_insert_ctr NUMBER :=1;
299 l_routings_step_dep_insert_ctr NUMBER :=1;
300 l_msg_count NUMBER;
301 l_msg VARCHAR2(1000);
302 l_ret_status VARCHAR2(3);
303 l_rout_tbl GMD_ROUTINGS_PUB.update_tbl_type;
304 l_rout_rec GMD_ROUTINGS_PUB.update_table_rec_type;
305 BEGIN
306 IF p_routing_obj.ROUTING_ID IS NULL THEN
307 l_routings_insert.ROUTING_STATUS := p_routing_obj.ROUTING_STATUS;
308 l_routings_insert.ENFORCE_STEP_DEPENDENCY := p_routing_obj.ENFORCE_STEP_DEPENDENCY;
309 l_routings_insert.ROUTING_DESC := p_routing_obj.ROUTING_DESC;
310 l_routings_insert.FIXED_PROCESS_LOSS := p_routing_obj.FIXED_PROCESS_LOSS ;
311 l_routings_insert.FIXED_PROCESS_LOSS_UOM := p_routing_obj.FIXED_PROCESS_LOSS_UOM;
312 l_routings_insert.OWNER_ORGANIZATION_ID := get_org_id(p_routing_obj.OWNER_ORGANIZATION_CODE);
313 l_routings_insert.ROUTING_ID := p_routing_obj.ROUTING_ID;
314 l_routings_insert.ROUTING_NO := p_routing_obj.ROUTING_NO;
315 l_routings_insert.ROUTING_VERS := p_routing_obj.ROUTING_VERS;
316 l_routings_insert.ROUTING_CLASS := p_routing_obj.ROUTING_CLASS ;
317 l_routings_insert.ROUTING_QTY := p_routing_obj.ROUTING_QTY;
318 l_routings_insert.ITEM_UM := p_routing_obj.ITEM_UM ;
319 l_routings_insert.ROUTING_UOM := p_routing_obj.ROUTING_UOM ;
320 l_routings_insert.CONTIGUOUS_IND := p_routing_obj.CONTIGUOUS_IND;
321 l_routings_insert.DELETE_MARK := p_routing_obj.DELETE_MARK;
322 l_routings_insert.TEXT_CODE := p_routing_obj.TEXT_CODE;
323 l_routings_insert.INACTIVE_IND := p_routing_obj.INACTIVE_IND ;
324 l_routings_insert.IN_USE := p_routing_obj.IN_USE;
325 l_routings_insert.CREATION_DATE := Nvl(p_routing_obj.CREATION_DATE,SYSDATE);
326 l_routings_insert.CREATED_BY := Nvl(p_routing_obj.CREATED_BY,FND_GLOBAL.USER_ID);
327 l_routings_insert.LAST_UPDATE_LOGIN := Nvl(p_routing_obj.LAST_UPDATE_LOGIN ,FND_GLOBAL.USER_ID);
328 l_routings_insert.LAST_UPDATE_DATE := Nvl(p_routing_obj.LAST_UPDATE_DATE,SYSDATE);
329 l_routings_insert.LAST_UPDATED_BY := Nvl(p_routing_obj.LAST_UPDATED_BY,FND_GLOBAL.USER_ID);
330 l_routings_insert.ATTRIBUTE_CATEGORY :=p_routing_obj.flex_obj.ATTRIBUTE_CATEGORY;
331 l_routings_insert.ATTRIBUTE_CATEGORY:=p_routing_obj.flex_obj.ATTRIBUTE_CATEGORY;
332 l_routings_insert.ATTRIBUTE1:=p_routing_obj.flex_obj.ATTRIBUTE1;
333 l_routings_insert.ATTRIBUTE2:=p_routing_obj.flex_obj.ATTRIBUTE2;
334 l_routings_insert.ATTRIBUTE3:=p_routing_obj.flex_obj.ATTRIBUTE3;
335 l_routings_insert.ATTRIBUTE4:=p_routing_obj.flex_obj.ATTRIBUTE4;
336 l_routings_insert.ATTRIBUTE5:=p_routing_obj.flex_obj.ATTRIBUTE5;
337 l_routings_insert.ATTRIBUTE6:=p_routing_obj.flex_obj.ATTRIBUTE6;
338 l_routings_insert.ATTRIBUTE7:=p_routing_obj.flex_obj.ATTRIBUTE7;
339 l_routings_insert.ATTRIBUTE8:=p_routing_obj.flex_obj.ATTRIBUTE8;
340 l_routings_insert.ATTRIBUTE9:=p_routing_obj.flex_obj.ATTRIBUTE9;
341 l_routings_insert.ATTRIBUTE10:=p_routing_obj.flex_obj.ATTRIBUTE10;
342 l_routings_insert.ATTRIBUTE11:=p_routing_obj.flex_obj.ATTRIBUTE11;
343 l_routings_insert.ATTRIBUTE12:=p_routing_obj.flex_obj.ATTRIBUTE12;
344 l_routings_insert.ATTRIBUTE13:=p_routing_obj.flex_obj.ATTRIBUTE13;
345 l_routings_insert.ATTRIBUTE14:=p_routing_obj.flex_obj.ATTRIBUTE14;
346 l_routings_insert.ATTRIBUTE15:=p_routing_obj.flex_obj.ATTRIBUTE15;
347 l_routings_insert.ATTRIBUTE16:=p_routing_obj.flex_obj.ATTRIBUTE16;
348 l_routings_insert.ATTRIBUTE17:=p_routing_obj.flex_obj.ATTRIBUTE17;
349 l_routings_insert.ATTRIBUTE18:=p_routing_obj.flex_obj.ATTRIBUTE18;
350 l_routings_insert.ATTRIBUTE19:=p_routing_obj.flex_obj.ATTRIBUTE19;
351 l_routings_insert.ATTRIBUTE20:=p_routing_obj.flex_obj.ATTRIBUTE20;
352 l_routings_insert.ATTRIBUTE21:=p_routing_obj.flex_obj.ATTRIBUTE21;
353 l_routings_insert.ATTRIBUTE22:=p_routing_obj.flex_obj.ATTRIBUTE22;
354 l_routings_insert.ATTRIBUTE23:=p_routing_obj.flex_obj.ATTRIBUTE23;
355 l_routings_insert.ATTRIBUTE24:=p_routing_obj.flex_obj.ATTRIBUTE24;
356 l_routings_insert.ATTRIBUTE25:=p_routing_obj.flex_obj.ATTRIBUTE25;
357 l_routings_insert.ATTRIBUTE26:=p_routing_obj.flex_obj.ATTRIBUTE26;
358 l_routings_insert.ATTRIBUTE27:=p_routing_obj.flex_obj.ATTRIBUTE27;
359 l_routings_insert.ATTRIBUTE28:=p_routing_obj.flex_obj.ATTRIBUTE28;
360 l_routings_insert.ATTRIBUTE29:=p_routing_obj.flex_obj.ATTRIBUTE29;
361 l_routings_insert.ATTRIBUTE30:=p_routing_obj.flex_obj.ATTRIBUTE30;
362 l_routings_insert.EFFECTIVE_START_DATE := p_routing_obj.EFFECTIVE_START_DATE;
363 l_routings_insert.EFFECTIVE_END_DATE := p_routing_obj.EFFECTIVE_END_DATE;
364 l_routings_insert.OWNER_ID := Nvl(p_routing_obj.OWNER_ID,FND_GLOBAL.USER_ID);
365 l_routings_insert.PROJECT_ID := p_routing_obj.PROJECT_ID ;
366 l_routings_insert.PROCESS_LOSS := p_routing_obj.PROCESS_LOSS ;
367 FOR j IN 1..p_routing_obj.gmd_ebi_routing_steps.Count LOOP
368 l_routings_step_rec.X_COORDINATE:=p_routing_obj.gmd_ebi_routing_steps(j).X_COORDINATE;
369 l_routings_step_rec.Y_COORDINATE:=p_routing_obj.gmd_ebi_routing_steps(j).Y_COORDINATE;
370 l_routings_step_rec.MINIMUM_TRANSFER_QTY:=p_routing_obj.gmd_ebi_routing_steps(j).MINIMUM_TRANSFER_QTY;
371 l_routings_step_rec.ROUTING_ID:=p_routing_obj.gmd_ebi_routing_steps(j).ROUTING_ID;
372 l_routings_step_rec.ROUTINGSTEP_NO:=p_routing_obj.gmd_ebi_routing_steps(j).ROUTINGSTEP_NO;
373 l_routings_step_rec.ROUTINGSTEP_ID:=p_routing_obj.gmd_ebi_routing_steps(j).ROUTINGSTEP_ID;
374 l_routings_step_rec.OPRN_ID:=p_routing_obj.gmd_ebi_routing_steps(j).OPRN_ID;
375 l_routings_step_rec.STEP_QTY:=p_routing_obj.gmd_ebi_routing_steps(j).STEP_QTY;
376 l_routings_step_rec.STEPRELEASE_TYPE:=p_routing_obj.gmd_ebi_routing_steps(j).STEPRELEASE_TYPE;
377 l_routings_step_rec.TEXT_CODE:=p_routing_obj.gmd_ebi_routing_steps(j).TEXT_CODE;
378 l_routings_step_rec.LAST_UPDATED_BY:=Nvl(p_routing_obj.gmd_ebi_routing_steps(j).LAST_UPDATED_BY,FND_GLOBAL.USER_ID);
379 l_routings_step_rec.CREATED_BY:=Nvl(p_routing_obj.gmd_ebi_routing_steps(j).CREATED_BY,FND_GLOBAL.USER_ID);
380 l_routings_step_rec.LAST_UPDATE_DATE:=Nvl(p_routing_obj.gmd_ebi_routing_steps(j).LAST_UPDATE_DATE,SYSDATE);
381 l_routings_step_rec.CREATION_DATE:=Nvl(p_routing_obj.gmd_ebi_routing_steps(j).CREATION_DATE,SYSDATE);
382 l_routings_step_rec.LAST_UPDATE_LOGIN:=Nvl(p_routing_obj.gmd_ebi_routing_steps(j).LAST_UPDATE_LOGIN,FND_GLOBAL.USER_ID);
383 l_routings_step_rec.ATTRIBUTE1:=p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.ATTRIBUTE1;
384 l_routings_step_rec.ATTRIBUTE2:=p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.ATTRIBUTE2;
385 l_routings_step_rec.ATTRIBUTE3:=p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.ATTRIBUTE3;
386 l_routings_step_rec.ATTRIBUTE4:=p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.ATTRIBUTE4;
387 l_routings_step_rec.ATTRIBUTE5:=p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.ATTRIBUTE5;
388 l_routings_step_rec.ATTRIBUTE6:=p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.ATTRIBUTE6;
389 l_routings_step_rec.ATTRIBUTE7:=p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.ATTRIBUTE7;
390 l_routings_step_rec.ATTRIBUTE8:=p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.ATTRIBUTE8;
391 l_routings_step_rec.ATTRIBUTE9:=p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.ATTRIBUTE9;
392 l_routings_step_rec.ATTRIBUTE10:=p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.ATTRIBUTE10;
393 l_routings_step_rec.ATTRIBUTE11:=p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.ATTRIBUTE11;
394 l_routings_step_rec.ATTRIBUTE12:=p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.ATTRIBUTE12;
395 l_routings_step_rec.ATTRIBUTE13:=p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.ATTRIBUTE13;
396 l_routings_step_rec.ATTRIBUTE14:=p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.ATTRIBUTE14;
397 l_routings_step_rec.ATTRIBUTE15:=p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.ATTRIBUTE15;
398 l_routings_step_rec.ATTRIBUTE16:=p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.ATTRIBUTE16;
399 l_routings_step_rec.ATTRIBUTE17:=p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.ATTRIBUTE17;
400 l_routings_step_rec.ATTRIBUTE18:=p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.ATTRIBUTE18;
401 l_routings_step_rec.ATTRIBUTE19:=p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.ATTRIBUTE19;
402 l_routings_step_rec.ATTRIBUTE20:=p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.ATTRIBUTE20;
403 l_routings_step_rec.ATTRIBUTE21:=p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.ATTRIBUTE21;
404 l_routings_step_rec.ATTRIBUTE22:=p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.ATTRIBUTE22;
405 l_routings_step_rec.ATTRIBUTE23:=p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.ATTRIBUTE23;
406 l_routings_step_rec.ATTRIBUTE24:=p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.ATTRIBUTE24;
407 l_routings_step_rec.ATTRIBUTE25:=p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.ATTRIBUTE25;
408 l_routings_step_rec.ATTRIBUTE26:=p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.ATTRIBUTE26;
409 l_routings_step_rec.ATTRIBUTE27:=p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.ATTRIBUTE27;
410 l_routings_step_rec.ATTRIBUTE28:=p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.ATTRIBUTE28;
411 l_routings_step_rec.ATTRIBUTE29:=p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.ATTRIBUTE29;
412 l_routings_step_rec.ATTRIBUTE30:=p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.ATTRIBUTE30;
413 l_routings_step_tbl_insert(l_routings_step_insert_ctr) := l_routings_step_rec;
414 l_routings_step_insert_ctr := l_routings_step_insert_ctr + 1;
415 END LOOP;
416 FOR i IN 1..p_routing_obj.gmd_ebi_rtg_step_dependency.Count LOOP
417 l_routings_step_dep_rec.ROUTINGSTEP_NO_UOM := p_routing_obj.gmd_ebi_rtg_step_dependency(i).ROUTINGSTEP_NO_UOM;
418 l_routings_step_dep_rec.CHARGEABLE_IND := p_routing_obj.gmd_ebi_rtg_step_dependency(i).CHARGEABLE_IND;
419 l_routings_step_dep_rec.ROUTINGSTEP_NO := p_routing_obj.gmd_ebi_rtg_step_dependency(i).ROUTINGSTEP_NO;
420 l_routings_step_dep_rec.DEP_ROUTINGSTEP_NO := p_routing_obj.gmd_ebi_rtg_step_dependency(i).DEP_ROUTINGSTEP_NO;
421 l_routings_step_dep_rec.ROUTING_ID := p_routing_obj.gmd_ebi_rtg_step_dependency(i).ROUTING_ID;
422 l_routings_step_dep_rec.DEP_TYPE := p_routing_obj.gmd_ebi_rtg_step_dependency(i).DEP_TYPE;
423 l_routings_step_dep_rec.REWORK_CODE := p_routing_obj.gmd_ebi_rtg_step_dependency(i).REWORK_CODE;
424 l_routings_step_dep_rec.STANDARD_DELAY := p_routing_obj.gmd_ebi_rtg_step_dependency(i).STANDARD_DELAY;
425 l_routings_step_dep_rec.MINIMUM_DELAY := p_routing_obj.gmd_ebi_rtg_step_dependency(i).MINIMUM_DELAY;
426 l_routings_step_dep_rec.MAX_DELAY := p_routing_obj.gmd_ebi_rtg_step_dependency(i).MAX_DELAY;
427 l_routings_step_dep_rec.TRANSFER_QTY := p_routing_obj.gmd_ebi_rtg_step_dependency(i).TRANSFER_QTY;
428 l_routings_step_dep_rec.ITEM_UM := p_routing_obj.gmd_ebi_rtg_step_dependency(i).ITEM_UM;
429 l_routings_step_dep_rec.TEXT_CODE := p_routing_obj.gmd_ebi_rtg_step_dependency(i).TEXT_CODE;
430 l_routings_step_dep_rec.LAST_UPDATED_BY := Nvl(p_routing_obj.gmd_ebi_rtg_step_dependency(i).LAST_UPDATED_BY,FND_GLOBAL.USER_ID);
431 l_routings_step_dep_rec.CREATED_BY := Nvl(p_routing_obj.gmd_ebi_rtg_step_dependency(i).CREATED_BY,FND_GLOBAL.USER_ID);
432 l_routings_step_dep_rec.LAST_UPDATE_DATE := Nvl(p_routing_obj.gmd_ebi_rtg_step_dependency(i).LAST_UPDATE_DATE,SYSDATE);
433 l_routings_step_dep_rec.CREATION_DATE := Nvl(p_routing_obj.gmd_ebi_rtg_step_dependency(i).CREATION_DATE,SYSDATE);
434 l_routings_step_dep_rec.LAST_UPDATE_LOGIN := Nvl(p_routing_obj.gmd_ebi_rtg_step_dependency(i).LAST_UPDATE_LOGIN,FND_GLOBAL.USER_ID);
435 l_routings_step_dep_rec.TRANSFER_PCT := p_routing_obj.gmd_ebi_rtg_step_dependency(i).TRANSFER_PCT;
436 l_routings_step_dep_tbl_insert(l_routings_step_dep_insert_ctr) := l_routings_step_dep_rec;
437 l_routings_step_dep_insert_ctr := l_routings_step_dep_insert_ctr + 1;
438 END LOOP;
439
440 GMD_ROUTINGS_PUB.insert_routing(
441 p_api_version => 1
442 , p_init_msg_list => TRUE
443 , p_commit => FALSE
444 , p_routings => l_routings_insert
445 , p_routings_step_tbl => l_routings_step_tbl_insert
446 , p_routings_step_dep_tbl => l_routings_step_dep_tbl_insert
447 , x_message_count => l_msg_count
448 , x_message_list => l_msg
449 , x_return_status => l_ret_status
450 );
451 IF (l_ret_status <> FND_API.G_RET_STS_SUCCESS) THEN
452 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
453 END IF;
454
455 ELSE
456 l_rout_rec.p_col_to_update := 'routing_status';
457 l_rout_rec.p_value := p_routing_obj.routing_status;
458 l_rout_tbl(1) := l_rout_rec;
459 l_rout_rec.p_col_to_update := 'enforce_step_dependency';
460 l_rout_rec.p_value := p_routing_obj.enforce_step_dependency;
461 l_rout_tbl(2) := l_rout_rec;
462 l_rout_rec.p_col_to_update := 'routing_desc';
463 l_rout_rec.p_value := p_routing_obj.routing_desc;
464 l_rout_tbl(3) := l_rout_rec;
465 l_rout_rec.p_col_to_update := 'fixed_process_loss';
466 l_rout_rec.p_value := p_routing_obj.fixed_process_loss;
467 l_rout_tbl(4) := l_rout_rec;
468 l_rout_rec.p_col_to_update := 'fixed_process_loss_uom';
469 l_rout_rec.p_value := p_routing_obj.fixed_process_loss_uom;
470 l_rout_tbl(5) := l_rout_rec;
471 l_rout_rec.p_col_to_update := 'owner_organization_id';
472 l_rout_rec.p_value := get_org_id(p_routing_obj.owner_organization_code);
473 l_rout_tbl(6) := l_rout_rec;
474 l_rout_rec.p_col_to_update := 'routing_id';
475 l_rout_rec.p_value := p_routing_obj.routing_id;
476 l_rout_tbl(7) := l_rout_rec;
477 l_rout_rec.p_col_to_update := 'routing_no';
478 l_rout_rec.p_value := p_routing_obj.routing_no;
479 l_rout_tbl(8) := l_rout_rec;
480 l_rout_rec.p_col_to_update := 'routing_vers';
481 l_rout_rec.p_value := p_routing_obj.routing_vers;
482 l_rout_tbl(9) := l_rout_rec;
483 l_rout_rec.p_col_to_update := 'routing_class';
484 l_rout_rec.p_value := p_routing_obj.routing_class;
485 l_rout_tbl(10) := l_rout_rec;
486 l_rout_rec.p_col_to_update := 'routing_qty';
487 l_rout_rec.p_value := p_routing_obj.routing_qty;
488 l_rout_tbl(11) := l_rout_rec;
489 l_rout_rec.p_col_to_update := 'item_um';
490 l_rout_rec.p_value := p_routing_obj.item_um;
491 l_rout_tbl(12) := l_rout_rec;
492 l_rout_rec.p_col_to_update := 'routing_uom';
493 l_rout_rec.p_value := p_routing_obj.routing_uom;
494 l_rout_tbl(13) := l_rout_rec;
495 l_rout_rec.p_col_to_update := 'contiguous_ind';
496 l_rout_rec.p_value := p_routing_obj.contiguous_ind;
497 l_rout_tbl(14) := l_rout_rec;
498 l_rout_rec.p_col_to_update := 'delete_mark';
499 l_rout_rec.p_value := p_routing_obj.delete_mark;
500 l_rout_tbl(15) := l_rout_rec;
501 l_rout_rec.p_col_to_update := 'text_code';
502 l_rout_rec.p_value := p_routing_obj.text_code;
503 l_rout_tbl(16) := l_rout_rec;
504 l_rout_rec.p_col_to_update := 'inactive_ind';
505 l_rout_rec.p_value := p_routing_obj.inactive_ind;
506 l_rout_tbl(17) := l_rout_rec;
507 l_rout_rec.p_col_to_update := 'in_use';
508 l_rout_rec.p_value := p_routing_obj.in_use;
509 l_rout_tbl(18) := l_rout_rec;
510 l_rout_rec.p_col_to_update := 'creation_date';
511 l_rout_rec.p_value := p_routing_obj.creation_date;
512 l_rout_tbl(19) := l_rout_rec;
513 l_rout_rec.p_col_to_update := 'created_by';
514 l_rout_rec.p_value := p_routing_obj.created_by;
515 l_rout_tbl(20) := l_rout_rec;
516 l_rout_rec.p_col_to_update := 'last_update_login';
517 l_rout_rec.p_value := p_routing_obj.last_update_login;
518 l_rout_tbl(21) := l_rout_rec;
519 l_rout_rec.p_col_to_update := 'last_update_date';
520 l_rout_rec.p_value := p_routing_obj.last_update_date;
521 l_rout_tbl(22) := l_rout_rec;
522 l_rout_rec.p_col_to_update := 'last_updated_by';
523 l_rout_rec.p_value := p_routing_obj.last_updated_by;
524 l_rout_tbl(23) := l_rout_rec;
525 l_rout_rec.p_col_to_update := 'effective_start_date';
526 l_rout_rec.p_value := p_routing_obj.effective_start_date;
527 l_rout_tbl(24) := l_rout_rec;
528 l_rout_rec.p_col_to_update := 'effective_end_date';
529 l_rout_rec.p_value := p_routing_obj.effective_end_date;
530 l_rout_tbl(25) := l_rout_rec;
531 l_rout_rec.p_col_to_update := 'owner_id';
532 l_rout_rec.p_value := Nvl(p_routing_obj.owner_id,FND_GLOBAL.USER_ID);
533 l_rout_tbl(26) := l_rout_rec;
534 l_rout_rec.p_col_to_update := 'project_id';
535 l_rout_rec.p_value := p_routing_obj.project_id;
536 l_rout_tbl(27) := l_rout_rec;
537 l_rout_rec.p_col_to_update := 'process_loss';
538 l_rout_rec.p_value := p_routing_obj.process_loss;
539 l_rout_tbl(28) := l_rout_rec;
540 l_rout_rec.p_col_to_update := 'attribute_category';
541 l_rout_rec.p_value := p_routing_obj.flex_obj.attribute_category;
542 l_rout_tbl(29) := l_rout_rec;
543 l_rout_rec.p_col_to_update := 'attribute1';
544 l_rout_rec.p_value := p_routing_obj.flex_obj.attribute1;
545 l_rout_tbl(30) := l_rout_rec;
546 l_rout_rec.p_col_to_update := 'attribute2';
547 l_rout_rec.p_value := p_routing_obj.flex_obj.attribute2;
548 l_rout_tbl(31) := l_rout_rec;
549 l_rout_rec.p_col_to_update := 'attribute3';
550 l_rout_rec.p_value := p_routing_obj.flex_obj.attribute3;
551 l_rout_tbl(32) := l_rout_rec;
552 l_rout_rec.p_col_to_update := 'attribute4';
553 l_rout_rec.p_value := p_routing_obj.flex_obj.attribute4;
554 l_rout_tbl(33) := l_rout_rec;
555 l_rout_rec.p_col_to_update := 'attribute5';
556 l_rout_rec.p_value := p_routing_obj.flex_obj.attribute5;
557 l_rout_tbl(34) := l_rout_rec;
558 l_rout_rec.p_col_to_update := 'attribute6';
559 l_rout_rec.p_value := p_routing_obj.flex_obj.attribute6;
560 l_rout_tbl(35) := l_rout_rec;
561 l_rout_rec.p_col_to_update := 'attribute7';
562 l_rout_rec.p_value := p_routing_obj.flex_obj.attribute7;
563 l_rout_tbl(36) := l_rout_rec;
564 l_rout_rec.p_col_to_update := 'attribute8';
565 l_rout_rec.p_value := p_routing_obj.flex_obj.attribute8;
566 l_rout_tbl(37) := l_rout_rec;
567 l_rout_rec.p_col_to_update := 'attribute9';
568 l_rout_rec.p_value := p_routing_obj.flex_obj.attribute9;
569 l_rout_tbl(38) := l_rout_rec;
570 l_rout_rec.p_col_to_update := 'attribute10';
571 l_rout_rec.p_value := p_routing_obj.flex_obj.attribute10;
572 l_rout_tbl(39) := l_rout_rec;
573 l_rout_rec.p_col_to_update := 'attribute11';
574 l_rout_rec.p_value := p_routing_obj.flex_obj.attribute11;
575 l_rout_tbl(40) := l_rout_rec;
576 l_rout_rec.p_col_to_update := 'attribute12';
577 l_rout_rec.p_value := p_routing_obj.flex_obj.attribute12;
578 l_rout_tbl(41) := l_rout_rec;
579 l_rout_rec.p_col_to_update := 'attribute13';
580 l_rout_rec.p_value := p_routing_obj.flex_obj.attribute13;
581 l_rout_tbl(42) := l_rout_rec;
582 l_rout_rec.p_col_to_update := 'attribute14';
583 l_rout_rec.p_value := p_routing_obj.flex_obj.attribute14;
584 l_rout_tbl(43) := l_rout_rec;
585 l_rout_rec.p_col_to_update := 'attribute15';
586 l_rout_rec.p_value := p_routing_obj.flex_obj.attribute15;
587 l_rout_tbl(44) := l_rout_rec;
588 l_rout_rec.p_col_to_update := 'attribute16';
589 l_rout_rec.p_value := p_routing_obj.flex_obj.attribute16;
590 l_rout_tbl(45) := l_rout_rec;
591 l_rout_rec.p_col_to_update := 'attribute17';
592 l_rout_rec.p_value := p_routing_obj.flex_obj.attribute17;
593 l_rout_tbl(46) := l_rout_rec;
594 l_rout_rec.p_col_to_update := 'attribute18';
595 l_rout_rec.p_value := p_routing_obj.flex_obj.attribute18;
596 l_rout_tbl(47) := l_rout_rec;
597 l_rout_rec.p_col_to_update := 'attribute19';
598 l_rout_rec.p_value := p_routing_obj.flex_obj.attribute19;
599 l_rout_tbl(48) := l_rout_rec;
600 l_rout_rec.p_col_to_update := 'attribute20';
601 l_rout_rec.p_value := p_routing_obj.flex_obj.attribute20;
602 l_rout_tbl(49) := l_rout_rec;
603 l_rout_rec.p_col_to_update := 'attribute21';
604 l_rout_rec.p_value := p_routing_obj.flex_obj.attribute21;
605 l_rout_tbl(50) := l_rout_rec;
606 l_rout_rec.p_col_to_update := 'attribute22';
607 l_rout_rec.p_value := p_routing_obj.flex_obj.attribute22;
608 l_rout_tbl(51) := l_rout_rec;
609 l_rout_rec.p_col_to_update := 'attribute23';
610 l_rout_rec.p_value := p_routing_obj.flex_obj.attribute23;
611 l_rout_tbl(52) := l_rout_rec;
612 l_rout_rec.p_col_to_update := 'attribute24';
613 l_rout_rec.p_value := p_routing_obj.flex_obj.attribute24;
614 l_rout_tbl(53) := l_rout_rec;
615 l_rout_rec.p_col_to_update := 'attribute25';
616 l_rout_rec.p_value := p_routing_obj.flex_obj.attribute25;
617 l_rout_tbl(54) := l_rout_rec;
618 l_rout_rec.p_col_to_update := 'attribute26';
619 l_rout_rec.p_value := p_routing_obj.flex_obj.attribute26;
620 l_rout_tbl(55) := l_rout_rec;
621 l_rout_rec.p_col_to_update := 'attribute27';
622 l_rout_rec.p_value := p_routing_obj.flex_obj.attribute27;
623 l_rout_tbl(56) := l_rout_rec;
624 l_rout_rec.p_col_to_update := 'attribute28';
625 l_rout_rec.p_value := p_routing_obj.flex_obj.attribute28;
626 l_rout_tbl(57) := l_rout_rec;
627 l_rout_rec.p_col_to_update := 'attribute29';
628 l_rout_rec.p_value := p_routing_obj.flex_obj.attribute29;
629 l_rout_tbl(58) := l_rout_rec;
630 l_rout_rec.p_col_to_update := 'attribute30';
631 l_rout_rec.p_value := p_routing_obj.flex_obj.attribute30;
632 l_rout_tbl(59) := l_rout_rec;
633
634 GMD_ROUTINGS_PUB.update_routing
635 ( p_api_version => 1
636 , p_init_msg_list => TRUE
637 , p_commit => FALSE
638 , p_routing_id => p_routing_obj.ROUTING_ID
639 , p_routing_no => p_routing_obj.ROUTING_NO
640 , p_routing_vers => p_routing_obj.ROUTING_VERS
641 , p_update_table => l_rout_tbl
642 , x_message_count => l_msg_count
643 , x_message_list => l_msg
644 , x_return_status => l_ret_status
645 );
646 IF (l_ret_status <> FND_API.G_RET_STS_SUCCESS) THEN
647 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
648 END IF;
649
650 FOR j IN 1..p_routing_obj.gmd_ebi_routing_steps.Count LOOP
651 IF ( GMDRTVAL_PUB.check_routingstep_no(proutingstep_no => p_routing_obj.gmd_ebi_routing_steps(j).ROUTINGSTEP_NO
652 ,prouting_id => p_routing_obj.ROUTING_ID) = 0 ) THEN
653 l_routings_step_rec.X_COORDINATE:=p_routing_obj.gmd_ebi_routing_steps(j).X_COORDINATE;
654 l_routings_step_rec.Y_COORDINATE:=p_routing_obj.gmd_ebi_routing_steps(j).Y_COORDINATE;
655 l_routings_step_rec.MINIMUM_TRANSFER_QTY:=p_routing_obj.gmd_ebi_routing_steps(j).MINIMUM_TRANSFER_QTY;
656 l_routings_step_rec.ROUTING_ID:=p_routing_obj.gmd_ebi_routing_steps(j).ROUTING_ID;
657 l_routings_step_rec.ROUTINGSTEP_NO:=p_routing_obj.gmd_ebi_routing_steps(j).ROUTINGSTEP_NO;
658 l_routings_step_rec.ROUTINGSTEP_ID:=p_routing_obj.gmd_ebi_routing_steps(j).ROUTINGSTEP_ID;
659 l_routings_step_rec.OPRN_ID:=p_routing_obj.gmd_ebi_routing_steps(j).OPRN_ID;
660 l_routings_step_rec.STEP_QTY:=p_routing_obj.gmd_ebi_routing_steps(j).STEP_QTY;
661 l_routings_step_rec.STEPRELEASE_TYPE:=p_routing_obj.gmd_ebi_routing_steps(j).STEPRELEASE_TYPE;
662 l_routings_step_rec.TEXT_CODE:=p_routing_obj.gmd_ebi_routing_steps(j).TEXT_CODE;
663 l_routings_step_rec.LAST_UPDATED_BY:=Nvl(p_routing_obj.gmd_ebi_routing_steps(j).LAST_UPDATED_BY,FND_GLOBAL.USER_ID);
664 l_routings_step_rec.CREATED_BY:=Nvl(p_routing_obj.gmd_ebi_routing_steps(j).CREATED_BY,FND_GLOBAL.USER_ID);
665 l_routings_step_rec.LAST_UPDATE_DATE:=NVL(p_routing_obj.gmd_ebi_routing_steps(j).LAST_UPDATE_DATE,SYSDATE);
666 l_routings_step_rec.CREATION_DATE:=Nvl(p_routing_obj.gmd_ebi_routing_steps(j).CREATION_DATE,SYSDATE);
667 l_routings_step_rec.LAST_UPDATE_LOGIN:=Nvl(p_routing_obj.gmd_ebi_routing_steps(j).LAST_UPDATE_LOGIN,FND_GLOBAL.USER_ID);
668 l_routings_step_rec.ATTRIBUTE1:=p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.ATTRIBUTE1;
669 l_routings_step_rec.ATTRIBUTE2:=p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.ATTRIBUTE2;
670 l_routings_step_rec.ATTRIBUTE3:=p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.ATTRIBUTE3;
671 l_routings_step_rec.ATTRIBUTE4:=p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.ATTRIBUTE4;
672 l_routings_step_rec.ATTRIBUTE5:=p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.ATTRIBUTE5;
673 l_routings_step_rec.ATTRIBUTE6:=p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.ATTRIBUTE6;
674 l_routings_step_rec.ATTRIBUTE7:=p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.ATTRIBUTE7;
675 l_routings_step_rec.ATTRIBUTE8:=p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.ATTRIBUTE8;
676 l_routings_step_rec.ATTRIBUTE9:=p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.ATTRIBUTE9;
677 l_routings_step_rec.ATTRIBUTE10:=p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.ATTRIBUTE10;
678 l_routings_step_rec.ATTRIBUTE11:=p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.ATTRIBUTE11;
679 l_routings_step_rec.ATTRIBUTE12:=p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.ATTRIBUTE12;
680 l_routings_step_rec.ATTRIBUTE13:=p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.ATTRIBUTE13;
681 l_routings_step_rec.ATTRIBUTE14:=p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.ATTRIBUTE14;
682 l_routings_step_rec.ATTRIBUTE15:=p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.ATTRIBUTE15;
683 l_routings_step_rec.ATTRIBUTE16:=p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.ATTRIBUTE16;
684 l_routings_step_rec.ATTRIBUTE17:=p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.ATTRIBUTE17;
685 l_routings_step_rec.ATTRIBUTE18:=p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.ATTRIBUTE18;
686 l_routings_step_rec.ATTRIBUTE19:=p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.ATTRIBUTE19;
687 l_routings_step_rec.ATTRIBUTE20:=p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.ATTRIBUTE20;
688 l_routings_step_rec.ATTRIBUTE21:=p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.ATTRIBUTE21;
689 l_routings_step_rec.ATTRIBUTE22:=p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.ATTRIBUTE22;
690 l_routings_step_rec.ATTRIBUTE23:=p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.ATTRIBUTE23;
691 l_routings_step_rec.ATTRIBUTE24:=p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.ATTRIBUTE24;
692 l_routings_step_rec.ATTRIBUTE25:=p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.ATTRIBUTE25;
693 l_routings_step_rec.ATTRIBUTE26:=p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.ATTRIBUTE26;
694 l_routings_step_rec.ATTRIBUTE27:=p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.ATTRIBUTE27;
695 l_routings_step_rec.ATTRIBUTE28:=p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.ATTRIBUTE28;
696 l_routings_step_rec.ATTRIBUTE29:=p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.ATTRIBUTE29;
697 l_routings_step_rec.ATTRIBUTE30:=p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.ATTRIBUTE30;
698 IF l_routings_step_dep_tbl_insert.Count > 1 THEN
699 l_routings_step_dep_tbl_insert.DELETE;
700 END IF;
701 l_routings_step_dep_insert_ctr := 1;
702
703 FOR i IN 1..p_routing_obj.gmd_ebi_rtg_step_dependency.Count LOOP
704
705 IF (p_routing_obj.gmd_ebi_rtg_step_dependency(i).ROUTINGSTEP_NO = p_routing_obj.gmd_ebi_routing_steps(j).ROUTINGSTEP_NO) THEN
706 l_routings_step_dep_rec.ROUTINGSTEP_NO_UOM := p_routing_obj.gmd_ebi_rtg_step_dependency(i).ROUTINGSTEP_NO_UOM;
707 l_routings_step_dep_rec.CHARGEABLE_IND := p_routing_obj.gmd_ebi_rtg_step_dependency(i).CHARGEABLE_IND;
708 l_routings_step_dep_rec.ROUTINGSTEP_NO := p_routing_obj.gmd_ebi_rtg_step_dependency(i).ROUTINGSTEP_NO;
709 l_routings_step_dep_rec.DEP_ROUTINGSTEP_NO := p_routing_obj.gmd_ebi_rtg_step_dependency(i).DEP_ROUTINGSTEP_NO;
710 l_routings_step_dep_rec.ROUTING_ID := p_routing_obj.gmd_ebi_rtg_step_dependency(i).ROUTING_ID;
711 l_routings_step_dep_rec.DEP_TYPE := p_routing_obj.gmd_ebi_rtg_step_dependency(i).DEP_TYPE;
712 l_routings_step_dep_rec.REWORK_CODE := p_routing_obj.gmd_ebi_rtg_step_dependency(i).REWORK_CODE;
713 l_routings_step_dep_rec.STANDARD_DELAY := p_routing_obj.gmd_ebi_rtg_step_dependency(i).STANDARD_DELAY;
714 l_routings_step_dep_rec.MINIMUM_DELAY := p_routing_obj.gmd_ebi_rtg_step_dependency(i).MINIMUM_DELAY;
715 l_routings_step_dep_rec.MAX_DELAY := p_routing_obj.gmd_ebi_rtg_step_dependency(i).MAX_DELAY;
716 l_routings_step_dep_rec.TRANSFER_QTY := p_routing_obj.gmd_ebi_rtg_step_dependency(i).TRANSFER_QTY;
717 l_routings_step_dep_rec.ITEM_UM := p_routing_obj.gmd_ebi_rtg_step_dependency(i).ITEM_UM;
718 l_routings_step_dep_rec.TEXT_CODE := p_routing_obj.gmd_ebi_rtg_step_dependency(i).TEXT_CODE;
719 l_routings_step_dep_rec.LAST_UPDATED_BY := Nvl(p_routing_obj.gmd_ebi_rtg_step_dependency(i).LAST_UPDATED_BY,FND_GLOBAL.USER_ID);
720 l_routings_step_dep_rec.CREATED_BY := Nvl(p_routing_obj.gmd_ebi_rtg_step_dependency(i).CREATED_BY,FND_GLOBAL.USER_ID);
721 l_routings_step_dep_rec.LAST_UPDATE_DATE := Nvl(p_routing_obj.gmd_ebi_rtg_step_dependency(i).LAST_UPDATE_DATE,SYSDATE);
722 l_routings_step_dep_rec.CREATION_DATE := Nvl(p_routing_obj.gmd_ebi_rtg_step_dependency(i).CREATION_DATE,SYSDATE);
723 l_routings_step_dep_rec.LAST_UPDATE_LOGIN := Nvl(p_routing_obj.gmd_ebi_rtg_step_dependency(i).LAST_UPDATE_LOGIN,FND_GLOBAL.USER_ID);
724 l_routings_step_dep_rec.TRANSFER_PCT := p_routing_obj.gmd_ebi_rtg_step_dependency(i).TRANSFER_PCT;
725 l_routings_step_dep_tbl_insert(l_routings_step_dep_insert_ctr) := l_routings_step_dep_rec;
726 l_routings_step_dep_insert_ctr := l_routings_step_dep_insert_ctr + 1;
727 END IF;
728 END LOOP;
729 GMD_ROUTING_STEPS_PUB.insert_routing_steps
730 (
731 p_api_version => 1
732 , p_init_msg_list => FALSE
733 , p_commit => FALSE
734 , p_routing_id => p_routing_obj.ROUTING_ID
735 , p_routing_no => p_routing_obj.ROUTING_NO
736 , p_routing_vers => p_routing_obj.ROUTING_VERS
737 , p_routing_step_rec => l_routings_step_rec
738 , p_routings_step_dep_tbl => l_routings_step_dep_tbl_insert
739 , x_message_count => l_msg_count
740 , x_message_list => l_msg
741 , x_return_status => l_ret_status
742 );
743 IF (l_ret_status <> FND_API.G_RET_STS_SUCCESS) THEN
744 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
745 END IF;
746 ELSE
747 IF l_rout_tbl.Count > 1 THEN
748 l_rout_tbl.DELETE;
749 END IF;
750 l_rout_rec.p_col_to_update := 'x_coordinate';
751 l_rout_rec.p_value := p_routing_obj.gmd_ebi_routing_steps(j).x_coordinate;
752 l_rout_tbl(1) := l_rout_rec;
753 l_rout_rec.p_col_to_update := 'y_coordinate';
754 l_rout_rec.p_value := p_routing_obj.gmd_ebi_routing_steps(j).y_coordinate;
755 l_rout_tbl(2) := l_rout_rec;
756 l_rout_rec.p_col_to_update := 'minimum_transfer_qty';
757 l_rout_rec.p_value := p_routing_obj.gmd_ebi_routing_steps(j).minimum_transfer_qty;
758 l_rout_tbl(3) := l_rout_rec;
759 l_rout_rec.p_col_to_update := 'routing_id';
760 l_rout_rec.p_value := p_routing_obj.gmd_ebi_routing_steps(j).routing_id;
761 l_rout_tbl(4) := l_rout_rec;
762 l_rout_rec.p_col_to_update := 'routingstep_no';
763 l_rout_rec.p_value := p_routing_obj.gmd_ebi_routing_steps(j).routingstep_no;
764 l_rout_tbl(5) := l_rout_rec;
765 l_rout_rec.p_col_to_update := 'routingstep_id';
766 l_rout_rec.p_value := p_routing_obj.gmd_ebi_routing_steps(j).routingstep_id;
767 l_rout_tbl(6) := l_rout_rec;
768 l_rout_rec.p_col_to_update := 'oprn_id';
769 l_rout_rec.p_value := p_routing_obj.gmd_ebi_routing_steps(j).oprn_id;
770 l_rout_tbl(7) := l_rout_rec;
771 l_rout_rec.p_col_to_update := 'step_qty';
772 l_rout_rec.p_value := p_routing_obj.gmd_ebi_routing_steps(j).step_qty;
773 l_rout_tbl(8) := l_rout_rec;
774 l_rout_rec.p_col_to_update := 'steprelease_type';
775 l_rout_rec.p_value := p_routing_obj.gmd_ebi_routing_steps(j).steprelease_type;
776 l_rout_tbl(9) := l_rout_rec;
777 l_rout_rec.p_col_to_update := 'text_code';
778 l_rout_rec.p_value := p_routing_obj.gmd_ebi_routing_steps(j).text_code;
779 l_rout_tbl(10) := l_rout_rec;
780 l_rout_rec.p_col_to_update := 'last_updated_by';
781 l_rout_rec.p_value := p_routing_obj.gmd_ebi_routing_steps(j).last_updated_by;
782 l_rout_tbl(11) := l_rout_rec;
783 l_rout_rec.p_col_to_update := 'created_by';
784 l_rout_rec.p_value := p_routing_obj.gmd_ebi_routing_steps(j).created_by;
785 l_rout_tbl(12) := l_rout_rec;
786 l_rout_rec.p_col_to_update := 'last_update_date';
787 l_rout_rec.p_value := p_routing_obj.gmd_ebi_routing_steps(j).last_update_date;
788 l_rout_tbl(13) := l_rout_rec;
789 l_rout_rec.p_col_to_update := 'creation_date';
790 l_rout_rec.p_value := p_routing_obj.gmd_ebi_routing_steps(j).creation_date;
791 l_rout_tbl(14) := l_rout_rec;
792 l_rout_rec.p_col_to_update := 'last_update_login';
793 l_rout_rec.p_value := p_routing_obj.gmd_ebi_routing_steps(j).last_update_login;
794 l_rout_tbl(15) := l_rout_rec;
795 l_rout_rec.p_col_to_update := 'attribute_category';
796 l_rout_rec.p_value := p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.attribute_category;
797 l_rout_tbl(16) := l_rout_rec;
798 l_rout_rec.p_col_to_update := 'attribute1';
799 l_rout_rec.p_value := p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.attribute1;
800 l_rout_tbl(17) := l_rout_rec;
801 l_rout_rec.p_col_to_update := 'attribute2';
802 l_rout_rec.p_value := p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.attribute2;
803 l_rout_tbl(18) := l_rout_rec;
804 l_rout_rec.p_col_to_update := 'attribute3';
805 l_rout_rec.p_value := p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.attribute3;
806 l_rout_tbl(19) := l_rout_rec;
807 l_rout_rec.p_col_to_update := 'attribute4';
808 l_rout_rec.p_value := p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.attribute4;
809 l_rout_tbl(20) := l_rout_rec;
810 l_rout_rec.p_col_to_update := 'attribute5';
811 l_rout_rec.p_value := p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.attribute5;
812 l_rout_tbl(21) := l_rout_rec;
813 l_rout_rec.p_col_to_update := 'attribute6';
814 l_rout_rec.p_value := p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.attribute6;
815 l_rout_tbl(22) := l_rout_rec;
816 l_rout_rec.p_col_to_update := 'attribute7';
817 l_rout_rec.p_value := p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.attribute7;
818 l_rout_tbl(23) := l_rout_rec;
819 l_rout_rec.p_col_to_update := 'attribute8';
820 l_rout_rec.p_value := p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.attribute8;
821 l_rout_tbl(24) := l_rout_rec;
822 l_rout_rec.p_col_to_update := 'attribute9';
823 l_rout_rec.p_value := p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.attribute9;
824 l_rout_tbl(25) := l_rout_rec;
825 l_rout_rec.p_col_to_update := 'attribute10';
826 l_rout_rec.p_value := p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.attribute10;
827 l_rout_tbl(26) := l_rout_rec;
828 l_rout_rec.p_col_to_update := 'attribute11';
829 l_rout_rec.p_value := p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.attribute11;
830 l_rout_tbl(27) := l_rout_rec;
831 l_rout_rec.p_col_to_update := 'attribute12';
832 l_rout_rec.p_value := p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.attribute12;
833 l_rout_tbl(28) := l_rout_rec;
834 l_rout_rec.p_col_to_update := 'attribute13';
835 l_rout_rec.p_value := p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.attribute13;
836 l_rout_tbl(29) := l_rout_rec;
837 l_rout_rec.p_col_to_update := 'attribute14';
838 l_rout_rec.p_value := p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.attribute14;
839 l_rout_tbl(30) := l_rout_rec;
840 l_rout_rec.p_col_to_update := 'attribute15';
841 l_rout_rec.p_value := p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.attribute15;
842 l_rout_tbl(31) := l_rout_rec;
843 l_rout_rec.p_col_to_update := 'attribute16';
844 l_rout_rec.p_value := p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.attribute16;
845 l_rout_tbl(32) := l_rout_rec;
846 l_rout_rec.p_col_to_update := 'attribute17';
847 l_rout_rec.p_value := p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.attribute17;
848 l_rout_tbl(33) := l_rout_rec;
849 l_rout_rec.p_col_to_update := 'attribute18';
850 l_rout_rec.p_value := p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.attribute18;
851 l_rout_tbl(34) := l_rout_rec;
852 l_rout_rec.p_col_to_update := 'attribute19';
853 l_rout_rec.p_value := p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.attribute19;
854 l_rout_tbl(35) := l_rout_rec;
855 l_rout_rec.p_col_to_update := 'attribute20';
856 l_rout_rec.p_value := p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.attribute20;
857 l_rout_tbl(36) := l_rout_rec;
858 l_rout_rec.p_col_to_update := 'attribute21';
859 l_rout_rec.p_value := p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.attribute21;
860 l_rout_tbl(37) := l_rout_rec;
861 l_rout_rec.p_col_to_update := 'attribute22';
862 l_rout_rec.p_value := p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.attribute22;
863 l_rout_tbl(38) := l_rout_rec;
864 l_rout_rec.p_col_to_update := 'attribute23';
865 l_rout_rec.p_value := p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.attribute23;
866 l_rout_tbl(39) := l_rout_rec;
867 l_rout_rec.p_col_to_update := 'attribute24';
868 l_rout_rec.p_value := p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.attribute24;
869 l_rout_tbl(40) := l_rout_rec;
870 l_rout_rec.p_col_to_update := 'attribute25';
871 l_rout_rec.p_value := p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.attribute25;
872 l_rout_tbl(41) := l_rout_rec;
873 l_rout_rec.p_col_to_update := 'attribute26';
874 l_rout_rec.p_value := p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.attribute26;
875 l_rout_tbl(42) := l_rout_rec;
876 l_rout_rec.p_col_to_update := 'attribute27';
877 l_rout_rec.p_value := p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.attribute27;
878 l_rout_tbl(43) := l_rout_rec;
879 l_rout_rec.p_col_to_update := 'attribute28';
880 l_rout_rec.p_value := p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.attribute28;
881 l_rout_tbl(44) := l_rout_rec;
882 l_rout_rec.p_col_to_update := 'attribute29';
883 l_rout_rec.p_value := p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.attribute29;
884 l_rout_tbl(45) := l_rout_rec;
885 l_rout_rec.p_col_to_update := 'attribute30';
886 l_rout_rec.p_value := p_routing_obj.gmd_ebi_routing_steps(j).flex_obj.attribute30;
887 l_rout_tbl(46) := l_rout_rec;
888 GMD_ROUTING_STEPS_PUB.update_routing_steps
889 ( p_api_version => 1
890 , p_init_msg_list => FALSE
891 , p_commit => FALSE
892 , p_routingstep_id => p_routing_obj.gmd_ebi_routing_steps(j).routingstep_id
893 , p_routingstep_no => p_routing_obj.gmd_ebi_routing_steps(j).routingstep_no
894 , p_routing_id => p_routing_obj.gmd_ebi_routing_steps(j).routing_id
895 , p_routing_no => p_routing_obj.ROUTING_NO
896 , p_routing_vers => p_routing_obj.ROUTING_vers
897 , p_update_table => l_rout_tbl
898 , x_message_count => l_msg_count
899 , x_message_list => l_msg
900 , x_return_status => l_ret_status
901 );
902 IF (l_ret_status <> FND_API.G_RET_STS_SUCCESS) THEN
903 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
904 END IF;
905 IF l_routings_step_dep_tbl_insert.Count > 1 THEN
906 l_routings_step_dep_tbl_insert.DELETE;
907 END IF;
908 l_routings_step_dep_insert_ctr := 1;
909 FOR i IN 1..p_routing_obj.gmd_ebi_rtg_step_dependency.Count LOOP
910 GMDRTVAL_PUB.check_deprouting(prouting_id=> p_routing_obj.gmd_ebi_rtg_step_dependency(i).ROUTING_ID
911 ,proutingStep_no => p_routing_obj.gmd_ebi_rtg_step_dependency(i).ROUTINGSTEP_NO
912 ,pdeproutingStep_no => p_routing_obj.gmd_ebi_rtg_step_dependency(i).DEP_ROUTINGSTEP_NO
913 ,x_return_status => l_ret_status
914 );
915
916 IF ( p_routing_obj.gmd_ebi_rtg_step_dependency(i).ROUTINGSTEP_NO = p_routing_obj.gmd_ebi_routing_steps(j).routingstep_no AND
917 l_ret_status = FND_API.G_RET_STS_SUCCESS ) THEN
918 l_routings_step_dep_rec.ROUTINGSTEP_NO_UOM := p_routing_obj.gmd_ebi_rtg_step_dependency(i).ROUTINGSTEP_NO_UOM;
919 l_routings_step_dep_rec.CHARGEABLE_IND := p_routing_obj.gmd_ebi_rtg_step_dependency(i).CHARGEABLE_IND;
920 l_routings_step_dep_rec.ROUTINGSTEP_NO := p_routing_obj.gmd_ebi_rtg_step_dependency(i).ROUTINGSTEP_NO;
921 l_routings_step_dep_rec.DEP_ROUTINGSTEP_NO := p_routing_obj.gmd_ebi_rtg_step_dependency(i).DEP_ROUTINGSTEP_NO;
922 l_routings_step_dep_rec.ROUTING_ID := p_routing_obj.gmd_ebi_rtg_step_dependency(i).ROUTING_ID;
923 l_routings_step_dep_rec.DEP_TYPE := p_routing_obj.gmd_ebi_rtg_step_dependency(i).DEP_TYPE;
924 l_routings_step_dep_rec.REWORK_CODE := p_routing_obj.gmd_ebi_rtg_step_dependency(i).REWORK_CODE;
925 l_routings_step_dep_rec.STANDARD_DELAY := p_routing_obj.gmd_ebi_rtg_step_dependency(i).STANDARD_DELAY;
926 l_routings_step_dep_rec.MINIMUM_DELAY := p_routing_obj.gmd_ebi_rtg_step_dependency(i).MINIMUM_DELAY;
927 l_routings_step_dep_rec.MAX_DELAY := p_routing_obj.gmd_ebi_rtg_step_dependency(i).MAX_DELAY;
928 l_routings_step_dep_rec.TRANSFER_QTY := p_routing_obj.gmd_ebi_rtg_step_dependency(i).TRANSFER_QTY;
929 l_routings_step_dep_rec.ITEM_UM := p_routing_obj.gmd_ebi_rtg_step_dependency(i).ITEM_UM;
930 l_routings_step_dep_rec.TEXT_CODE := p_routing_obj.gmd_ebi_rtg_step_dependency(i).TEXT_CODE;
931 l_routings_step_dep_rec.LAST_UPDATED_BY :=Nvl( p_routing_obj.gmd_ebi_rtg_step_dependency(i).LAST_UPDATED_BY,FND_GLOBAL.USER_ID);
932 l_routings_step_dep_rec.CREATED_BY := Nvl(p_routing_obj.gmd_ebi_rtg_step_dependency(i).CREATED_BY,FND_GLOBAL.USER_ID);
933 l_routings_step_dep_rec.LAST_UPDATE_DATE := Nvl(p_routing_obj.gmd_ebi_rtg_step_dependency(i).LAST_UPDATE_DATE,SYSDATE);
934 l_routings_step_dep_rec.CREATION_DATE := Nvl(p_routing_obj.gmd_ebi_rtg_step_dependency(i).CREATION_DATE,SYSDATE);
935 l_routings_step_dep_rec.LAST_UPDATE_LOGIN := Nvl(p_routing_obj.gmd_ebi_rtg_step_dependency(i).LAST_UPDATE_LOGIN,FND_GLOBAL.USER_ID);
936 l_routings_step_dep_rec.TRANSFER_PCT := p_routing_obj.gmd_ebi_rtg_step_dependency(i).TRANSFER_PCT;
937 l_routings_step_dep_tbl_insert(l_routings_step_dep_insert_ctr) := l_routings_step_dep_rec;
938 l_routings_step_dep_insert_ctr := l_routings_step_dep_insert_ctr + 1;
939 ELSE
940 IF l_rout_tbl.Count > 1 THEN
941 l_rout_tbl.DELETE;
942 END IF;
943 l_rout_rec.p_col_to_update := 'ROUTINGSTEP_NO_UOM';
944 l_rout_rec.p_value := p_routing_obj.gmd_ebi_rtg_step_dependency(i).ROUTINGSTEP_NO_UOM;
945 l_rout_tbl(1) := l_rout_rec;
946 l_rout_rec.p_col_to_update := 'CHARGEABLE_IND';
947 l_rout_rec.p_value := p_routing_obj.gmd_ebi_rtg_step_dependency(i).CHARGEABLE_IND;
948 l_rout_tbl(2) := l_rout_rec;
949 l_rout_rec.p_col_to_update := 'ROUTINGSTEP_NO';
950 l_rout_rec.p_value := p_routing_obj.gmd_ebi_rtg_step_dependency(i).ROUTINGSTEP_NO;
951 l_rout_tbl(3) := l_rout_rec;
952 l_rout_rec.p_col_to_update := 'DEP_ROUTINGSTEP_NO';
953 l_rout_rec.p_value := p_routing_obj.gmd_ebi_rtg_step_dependency(i).DEP_ROUTINGSTEP_NO;
954 l_rout_tbl(4) := l_rout_rec;
955 l_rout_rec.p_col_to_update := 'ROUTING_ID';
956 l_rout_rec.p_value := p_routing_obj.gmd_ebi_rtg_step_dependency(i).ROUTING_ID;
957 l_rout_tbl(5) := l_rout_rec;
958 l_rout_rec.p_col_to_update := 'DEP_TYPE';
959 l_rout_rec.p_value := p_routing_obj.gmd_ebi_rtg_step_dependency(i).DEP_TYPE;
960 l_rout_tbl(6) := l_rout_rec;
961 l_rout_rec.p_col_to_update := 'REWORK_CODE';
962 l_rout_rec.p_value := p_routing_obj.gmd_ebi_rtg_step_dependency(i).REWORK_CODE;
963 l_rout_tbl(7) := l_rout_rec;
964 l_rout_rec.p_col_to_update := 'STANDARD_DELAY';
965 l_rout_rec.p_value := p_routing_obj.gmd_ebi_rtg_step_dependency(i).STANDARD_DELAY;
966 l_rout_tbl(8) := l_rout_rec;
967 l_rout_rec.p_col_to_update := 'MINIMUM_DELAY';
968 l_rout_rec.p_value := p_routing_obj.gmd_ebi_rtg_step_dependency(i).MINIMUM_DELAY;
969 l_rout_tbl(9) := l_rout_rec;
970 l_rout_rec.p_col_to_update := 'MAX_DELAY';
971 l_rout_rec.p_value := p_routing_obj.gmd_ebi_rtg_step_dependency(i).MAX_DELAY;
972 l_rout_tbl(10) := l_rout_rec;
973 l_rout_rec.p_col_to_update := 'TRANSFER_QTY';
974 l_rout_rec.p_value := p_routing_obj.gmd_ebi_rtg_step_dependency(i).TRANSFER_QTY;
975 l_rout_tbl(11) := l_rout_rec;
976 l_rout_rec.p_col_to_update := 'ITEM_UM';
977 l_rout_rec.p_value := p_routing_obj.gmd_ebi_rtg_step_dependency(i).ITEM_UM;
978 l_rout_tbl(12) := l_rout_rec;
979 l_rout_rec.p_col_to_update := 'TEXT_CODE';
980 l_rout_rec.p_value := p_routing_obj.gmd_ebi_rtg_step_dependency(i).TEXT_CODE;
981 l_rout_tbl(13) := l_rout_rec;
982 l_rout_rec.p_col_to_update := 'LAST_UPDATED_BY';
983 l_rout_rec.p_value := p_routing_obj.gmd_ebi_rtg_step_dependency(i).LAST_UPDATED_BY;
984 l_rout_tbl(14) := l_rout_rec;
985 l_rout_rec.p_col_to_update := 'CREATED_BY';
986 l_rout_rec.p_value := p_routing_obj.gmd_ebi_rtg_step_dependency(i).CREATED_BY;
987 l_rout_tbl(15) := l_rout_rec;
988 l_rout_rec.p_col_to_update := 'LAST_UPDATE_DATE';
989 l_rout_rec.p_value := p_routing_obj.gmd_ebi_rtg_step_dependency(i).LAST_UPDATE_DATE;
990 l_rout_tbl(16) := l_rout_rec;
991 l_rout_rec.p_col_to_update := 'CREATION_DATE';
992 l_rout_rec.p_value := p_routing_obj.gmd_ebi_rtg_step_dependency(i).CREATION_DATE;
993 l_rout_tbl(17) := l_rout_rec;
994 l_rout_rec.p_col_to_update := 'LAST_UPDATE_LOGIN';
995 l_rout_rec.p_value := p_routing_obj.gmd_ebi_rtg_step_dependency(i).LAST_UPDATE_LOGIN;
996 l_rout_tbl(18) := l_rout_rec;
997 l_rout_rec.p_col_to_update := 'TRANSFER_PCT';
998 l_rout_rec.p_value := p_routing_obj.gmd_ebi_rtg_step_dependency(i).TRANSFER_PCT;
999 l_rout_tbl(19) := l_rout_rec;
1000
1001 GMD_ROUTING_STEPS_PUB.update_step_dependencies
1002 ( p_api_version =>1
1003 , p_init_msg_list =>FALSE
1004 , p_commit => FALSE
1005 , p_dep_routingstep_no => p_routing_obj.gmd_ebi_rtg_step_dependency(i).DEP_ROUTINGSTEP_NO
1006 , p_routingstep_id => p_routing_obj.gmd_ebi_routing_steps(j).routingstep_id
1007 , p_routingstep_no => p_routing_obj.gmd_ebi_routing_steps(j).routingstep_no
1008 , p_routing_id => p_routing_obj.gmd_ebi_routing_steps(j).routing_id
1009 , p_routing_no => p_routing_obj.ROUTING_NO
1010 , p_routing_vers => p_routing_obj.ROUTING_vers
1011 , p_update_table =>l_rout_tbl
1012 , x_message_count =>l_msg_count
1013 , x_message_list => l_msg
1014 , x_return_status => l_ret_status
1015 );
1016 IF (l_ret_status <> FND_API.G_RET_STS_SUCCESS) THEN
1017 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1018 END IF;
1019 END IF;
1020 END LOOP;
1021 IF l_routings_step_dep_tbl_insert.Count > 1 THEN
1022 GMD_ROUTING_STEPS_PUB.insert_step_dependencies
1023 ( p_api_version => 1
1024 , p_init_msg_list => FALSE
1025 , p_commit => FALSE
1026 , p_routing_id => p_routing_obj.gmd_ebi_routing_steps(j).routing_id
1027 , p_routing_no => p_routing_obj.ROUTING_NO
1028 , p_routing_vers => p_routing_obj.ROUTING_vers
1029 , p_routingstep_id => p_routing_obj.gmd_ebi_routing_steps(j).routingstep_id
1030 , p_routingstep_no => p_routing_obj.gmd_ebi_routing_steps(j).routingstep_no
1031 , p_routings_step_dep_tbl => l_routings_step_dep_tbl_insert
1032 , x_message_count => l_msg_count
1033 , x_message_list => l_msg
1034 , x_return_status => l_ret_status
1035 );
1036 IF (l_ret_status <> FND_API.G_RET_STS_SUCCESS) THEN
1037 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1038 END IF;
1039 END IF;
1040 END IF;
1041 END LOOP;
1042 END IF;
1043 x_return_status := FND_API.G_RET_STS_SUCCESS;
1044 EXCEPTION
1045 WHEN OTHERS THEN
1046 x_return_status := l_ret_status;
1047 IF l_msg IS NOT NULL THEN
1048 x_msg_data := l_msg;
1049 ELSE
1050 x_msg_data := SQLERRM || 'AT SYNC ROUTINGS';
1051 END IF;
1052 END;
1053
1054
1055 /* ======================================================================== */
1056
1057 /* PROCEDURE: */
1058
1059 /* add_attachment */
1060
1061 /* */
1062
1063 /* DESCRIPTION: */
1064
1065 /* The procedure creates/updates the attachment that denotes routing */
1066
1067 /* information. */
1068
1069 /* ======================================================================== */
1070
1071 PROCEDURE add_attachment(
1072 p_recipe_obj IN gmd_ebi_recipe_obj
1073 ,x_return_status OUT NOCOPY VARCHAR2
1074 ,x_msg_count OUT NOCOPY NUMBER
1075 ,x_msg_data OUT NOCOPY VARCHAR2
1076 )
1077 IS
1078 l_text LONG;
1079 l_text_ing LONG;
1080 l_text_products LONG;
1081 l_text_by_products LONG;
1082 l_new_line varchar2(10) := fnd_global.newline;
1083 l_space varchar2(10) := fnd_const.local_chr(32);
1084 l_bus_comp_id VARCHAR2(100);
1085 l_line_id NUMBER;
1086 l_attchmt_exits VARCHAR2(1);
1087 l_formula_id NUMBER;
1088 l_datatype_id number := 2;
1089 l_category_id NUMBER ;
1090 l_security_tp number := 4;
1091 l_publish_flag varchar2(1) := 'Y';
1092 l_usage_type varchar2(1) := 'S';
1093 l_description varchar2(100) := 'ROUTING DETAILS';
1094 l_entity_name varchar2(100) := 'FM_FORM_MST_B';
1095 l_aut_add_flag varchar2(1) := 'N';
1096 l_row_id1 varchar2(255);
1097 l_row_id2 varchar2(255);
1098 l_document_id number;
1099 l_att_doc_id number;
1100 l_media_id NUMBER;
1101 l_attached_doc fnd_attached_documents%ROWTYPE;
1102 l_doc fnd_documents%ROWTYPE;
1103 l_item_no VARCHAR2(100);
1104 l_item_desc VARCHAR2(1000);
1105 l_org_id NUMBER;
1106
1107 BEGIN
1108 x_return_status := FND_API.G_RET_STS_SUCCESS;
1109 x_msg_data := NULL;
1110 SELECT category_id INTO l_category_id FROM FND_DOCUMENT_CATEGORIES_tl WHERE user_name='Formula Header Info';
1111 l_text := FND_MESSAGE.GET_STRING('GMD','GMD_FORMULA') ||':'|| p_recipe_obj.formula_obj.formula_header.FORMULA_NO;
1112 l_text := l_text || l_new_line || FND_MESSAGE.GET_STRING('GMD','GMD_EBI_ATTACHMENT_DESCRIPTION') ||':'|| p_recipe_obj.formula_obj.formula_header.FORMULA_DESC1;
1113 l_text := l_text || l_new_line || FND_MESSAGE.GET_STRING('GMD','GMD_EBI_ATTACHMENT_STATUS') ||':'|| p_recipe_obj.formula_obj.formula_header.FORMULA_STATUS;
1114 l_text := l_text || l_new_line || FND_MESSAGE.GET_STRING('GMD','GMD_EBI_ATTACHMENT_FACILITY')|| ':';
1115 /*Code modification for Bug No.9750340 started here */
1116 /*Code modification for Bug No.9852048 started here */
1117 IF p_recipe_obj.manufacturing_plant IS NOT NULL THEN
1118 FOR i IN 1..p_recipe_obj.manufacturing_plant.Count LOOP
1119 IF( i=1) THEN
1120 l_text := l_text || p_recipe_obj.manufacturing_plant(i).name;
1121 ELSE
1122 l_text := l_text ||','||' '|| p_recipe_obj.manufacturing_plant(i).name;
1123 END IF;
1124 END LOOP;
1125 END IF;
1126 /*Code modification for Bug No.9852048 ends here*/
1127 /*Code modification for Bug No.9750340 ends here*/
1128 l_text := l_text || l_new_line;
1129 IF p_recipe_obj.routing_obj IS NOT NULL THEN
1130 FOR i IN 1..p_recipe_obj.routing_obj.gmd_ebi_routing_steps.Count LOOP
1131 l_text := l_text || l_new_line || FND_MESSAGE.GET_STRING('GMD','GMD_EBI_ATTACHMENT_STEPNO') ||':'||p_recipe_obj.routing_obj.gmd_ebi_routing_steps(i).ROUTINGSTEP_NO;
1132 l_text := l_text || l_new_line || FND_MESSAGE.GET_STRING('GMD','GMD_EBI_ATTACHMENT_INSTRUCTION') ||':'||p_recipe_obj.routing_obj.gmd_ebi_routing_steps(i).instructions;
1133 IF p_recipe_obj.routing_obj.gmd_ebi_routing_steps(i).gmd_ebi_formula_line_ref IS NOT NULL THEN
1134 l_text_ing := l_new_line || LPad(FND_MESSAGE.GET_STRING('GMD','GMD_INGREDIENTS'),17,l_space);
1135 l_text_ing := l_text_ing || l_new_line ||LPad('-------------------',25,l_space);
1136 l_text_products := l_new_line || LPad(FND_MESSAGE.GET_STRING('GMD','GMD_EBI_ATTACHMENT_COPRODUCTS'),17,l_space);
1137 l_text_products := l_text_products || l_new_line ||LPad('-------------------',25,l_space);
1138 l_text_by_products := l_new_line || LPad(FND_MESSAGE.GET_STRING('GMD','GMD_EBI_ATTACHMENT_BYPRODUCTS'),17,l_space);
1139 l_text_by_products := l_text_by_products || l_new_line ||LPad('-------------------',25,l_space);
1140 FOR j IN 1..p_recipe_obj.routing_obj.gmd_ebi_routing_steps(i).gmd_ebi_formula_line_ref.Count LOOP
1141 l_bus_comp_id := p_recipe_obj.routing_obj.gmd_ebi_routing_steps(i).gmd_ebi_formula_line_ref(j).business_component_id;
1142 l_line_id := p_recipe_obj.routing_obj.gmd_ebi_routing_steps(i).gmd_ebi_formula_line_ref(j).formula_line_id;
1143 FOR k IN 1..p_recipe_obj.formula_obj.formula_details.Count LOOP
1144 IF ( l_bus_comp_id = p_recipe_obj.formula_obj.formula_details(k).BUSINESS_COMPONENT_ID
1145 OR l_line_id = p_recipe_obj.formula_obj.formula_details(k).FORMULALINE_ID) THEN
1146 l_org_id := get_org_id(p_recipe_obj.formula_obj.formula_header.OWNER_ORGANIZATION_CODE);
1147 IF(p_recipe_obj.formula_obj.formula_details(k).LINE_TYPE = -1) THEN
1148 l_text_ing := l_text_ing || l_new_line || LPad(FND_MESSAGE.GET_STRING('GMD','GMD_EBI_LINE_NUMBER'),17,l_space)||':'||p_recipe_obj.formula_obj.formula_details(k).LINE_NO ;
1149 SELECT DISTINCT concatenated_segments,description INTO l_item_no,l_item_desc FROM mtl_system_items_kfv WHERE inventory_item_id = p_recipe_obj.formula_obj.formula_details(k).INVENTORY_ITEM_ID AND organization_id = l_org_id;
1150 l_text_ing := l_text_ing || l_new_line || LPad(FND_MESSAGE.GET_STRING('GMD','GMD_EBI_ATTACHMENT_ITEMNO'),16,l_space) ||':'||l_item_no;
1151 l_text_ing := l_text_ing || l_new_line || LPad(FND_MESSAGE.GET_STRING('GMD','GMD_EBI_ATTACHMENT_ITMDESC'),23,l_space) ||':'||l_item_desc;
1152 l_text_ing := l_text_ing || l_new_line || LPad(FND_MESSAGE.GET_STRING('GMD','GMD_QUANTITY'),14,l_space) ||':'||p_recipe_obj.formula_obj.formula_details(k).QTY;
1153 l_text_ing := l_text_ing || l_new_line || LPad(FND_MESSAGE.GET_STRING('GMD','GMD_EBI_ATTACHMENT_UOM'),10,l_space) ||':'||p_recipe_obj.formula_obj.formula_details(k).DETAIL_UOM;
1154 l_text_ing := l_text_ing || l_new_line;
1155 ELSIF(p_recipe_obj.formula_obj.formula_details(k).LINE_TYPE = 1) THEN
1156 l_text_products := l_text_products || l_new_line || LPad(FND_MESSAGE.GET_STRING('GMD','GMD_EBI_LINE_NUMBER'),17,l_space) ||':'||p_recipe_obj.formula_obj.formula_details(k).LINE_NO ;
1157 SELECT DISTINCT concatenated_segments,description INTO l_item_no,l_item_desc FROM mtl_system_items_kfv WHERE inventory_item_id = p_recipe_obj.formula_obj.formula_details(k).INVENTORY_ITEM_ID AND organization_id = l_org_id;
1158 l_text_products := l_text_products || l_new_line || LPad(FND_MESSAGE.GET_STRING('GMD','GMD_EBI_ATTACHMENT_ITEMNO'),16,l_space) ||':'||l_item_no;
1159 l_text_products := l_text_products || l_new_line || LPad(FND_MESSAGE.GET_STRING('GMD','GMD_EBI_ATTACHMENT_ITMDESC'),23,l_space)||':'||l_item_desc;
1160 l_text_products := l_text_products || l_new_line || LPad(FND_MESSAGE.GET_STRING('GMD','GMD_QUANTITY'),14,l_space)||':'||p_recipe_obj.formula_obj.formula_details(k).QTY;
1161 l_text_products := l_text_products || l_new_line || LPad(FND_MESSAGE.GET_STRING('GMD','GMD_EBI_ATTACHMENT_UOM'),10,l_space)||':'||p_recipe_obj.formula_obj.formula_details(k).DETAIL_UOM;
1162 l_text_products := l_text_products || l_new_line;
1163 ELSIF(p_recipe_obj.formula_obj.formula_details(k).LINE_TYPE = 2) THEN
1164 l_text_by_products := l_text_by_products || l_new_line ||LPad(FND_MESSAGE.GET_STRING('GMD','GMD_EBI_LINE_NUMBER'),17,l_space) ||':'||p_recipe_obj.formula_obj.formula_details(k).LINE_NO ;
1165 SELECT DISTINCT concatenated_segments,description INTO l_item_no,l_item_desc FROM mtl_system_items_kfv WHERE inventory_item_id = p_recipe_obj.formula_obj.formula_details(k).INVENTORY_ITEM_ID AND organization_id = l_org_id;
1166 l_text_by_products := l_text_by_products || l_new_line || LPad(FND_MESSAGE.GET_STRING('GMD','GMD_EBI_ATTACHMENT_ITEMNO'),16,l_space) ||':'||l_item_no;
1167 l_text_by_products := l_text_by_products || l_new_line || LPad(FND_MESSAGE.GET_STRING('GMD','GMD_EBI_ATTACHMENT_ITMDESC'),23,l_space)||':'||l_item_desc;
1168 l_text_by_products := l_text_by_products || l_new_line || LPad(FND_MESSAGE.GET_STRING('GMD','GMD_QUANTITY'),14,l_space)||':'||p_recipe_obj.formula_obj.formula_details(k).QTY;
1169 l_text_by_products := l_text_by_products || l_new_line || LPad(FND_MESSAGE.GET_STRING('GMD','GMD_EBI_ATTACHMENT_UOM'),10,l_space)||':'||p_recipe_obj.formula_obj.formula_details(k).DETAIL_UOM;
1170 l_text_by_products := l_text_by_products || l_new_line;
1171 END IF;
1172 END IF;
1173 END LOOP;
1174 END LOOP;
1175 l_text := l_text || l_new_line || l_text_ing;
1176 l_text := l_text || l_new_line || l_text_products;
1177 l_text := l_text || l_new_line || l_text_by_products;
1178 END IF;
1179 END LOOP;
1180
1181 IF ( p_recipe_obj.formula_obj.formula_header.FORMULA_ID IS NULL ) THEN
1182 SELECT Max(formula_id) INTO l_formula_id FROM fm_form_mst_b WHERE formula_no=p_recipe_obj.formula_obj.formula_header.FORMULA_no;
1183 ELSE
1184 l_formula_id := p_recipe_obj.formula_obj.formula_header.FORMULA_ID;
1185 END IF;
1186 l_attchmt_exits := fnd_attachment_util_pkg.get_atchmt_exists(l_entity_name => 'FM_FORM_MST_B'
1187 ,l_pkey1 => l_formula_id
1188 ,l_pkey3 => null
1189 ,l_pkey4 => null
1190 ,l_pkey5 => null
1191 ,l_function_name => 'GMDFRMED_F'
1192 ,l_function_type => 'F');
1193 IF(l_attchmt_exits <> 'Y') THEN
1194 FND_Documents_PKG.Insert_Row(X_Rowid => l_row_id1,
1195 X_document_id => l_document_id,
1196 X_creation_date => sysdate,
1197 X_created_by => FND_GLOBAL.USER_ID,
1198 X_last_update_date => sysdate,
1199 X_last_updated_by => FND_GLOBAL.USER_ID,
1200 X_datatype_id => l_datatype_id,
1201 X_category_id => l_category_id,
1202 X_security_type => l_security_tp,
1203 X_publish_flag => l_publish_flag,
1204 X_usage_type => l_usage_type,
1205 X_language => userenv('LANG'),
1206 X_description => l_description,
1207 X_file_name => null,
1208 X_media_id => l_media_id);
1209 INSERT INTO fnd_documents_long_text(Media_id,long_text) VALUES ( l_media_id,l_text);
1210 SELECT fnd_attached_documents_s.NEXTVAL
1211 INTO l_att_doc_id
1212 FROM dual;
1213 FND_Attached_Documents_PKG.Insert_Row(X_Rowid => l_row_id2,
1214 X_attached_document_id => l_att_doc_id,
1215 X_document_id => l_document_id,
1216 X_creation_date => sysdate,
1217 X_created_by => FND_GLOBAL.USER_ID,
1218 X_last_update_date => sysdate,
1219 X_last_updated_by => FND_GLOBAL.USER_ID,
1220 X_seq_num => '10',
1221 X_entity_name => l_entity_name,
1222 X_column1 => NULL,
1223 X_pk1_value => l_formula_id,
1224 X_pk2_value => NULL,
1225 X_pk3_value => NULL,
1226 X_pk4_value => NULL,
1227 X_pk5_value => NULL,
1228 X_automatically_added_flag => l_aut_add_flag,
1229 X_datatype_id => l_datatype_id,
1230 X_category_id => l_category_id,
1231 X_security_type => l_security_tp,
1232 X_publish_flag => l_publish_flag,
1233 X_usage_type => l_usage_type,
1234 X_language => UserEnv('LANG'),
1235 X_description => l_description,
1236 X_file_name => null,
1237 X_media_id => l_media_id,
1238 X_doc_attribute_Category => NULL,
1239 X_doc_attribute1 => NULL,
1240 X_doc_attribute2 => NULL,
1241 X_doc_attribute3 => NULL,
1242 X_doc_attribute4 => NULL,
1243 X_doc_attribute5 => NULL,
1244 X_doc_attribute6 => NULL,
1245 X_doc_attribute7 => NULL,
1246 X_doc_attribute8 => NULL,
1247 X_doc_attribute9 => NULL,
1248 X_doc_attribute10 => NULL,
1249 X_doc_attribute11 => NULL,
1250 X_doc_attribute12 => NULL,
1251 X_doc_attribute13 => NULL,
1252 X_doc_attribute14 => NULL,
1253 X_doc_attribute15 => NULL);
1254 ELSE
1255
1256 SELECT * INTO l_attached_doc
1257 FROM FND_ATTACHED_DOCUMENTS
1258 WHERE ENTITY_NAME ='FM_FORM_MST_B' AND PK1_VALUE = l_formula_id;
1259
1260 SELECT * INTO l_doc
1261 FROM fnd_documents
1262 WHERE document_id = l_attached_doc.document_id;
1263
1264 SELECT DISTINCT row_id INTO l_row_id1 FROM FND_ATTACHED_DOCS_FORM_VL
1265 WHERE attached_document_id = l_attached_doc.attached_document_id;
1266
1267
1268 FND_Attached_Documents_PKG.Update_Row(X_Rowid => l_row_id1
1269 ,X_attached_document_id => l_attached_doc.attached_document_id
1270 ,X_document_id =>l_attached_doc.document_id
1271 ,X_last_update_date => sysdate
1272 ,X_last_updated_by => FND_GLOBAL.USER_ID
1273 ,X_last_update_login => FND_GLOBAL.USER_ID
1274 ,X_seq_num => l_attached_doc.seq_num
1275 ,X_entity_name => l_attached_doc.entity_name
1276 ,X_column1 => l_attached_doc.column1
1277 ,X_pk1_value => l_attached_doc.pk1_value
1278 ,X_pk2_value => NULL
1279 ,X_pk3_value => NULL
1280 ,X_pk4_value => NULL
1281 ,X_pk5_value => NULL
1282 ,X_automatically_added_flag => l_attached_doc.automatically_added_flag
1283 ,X_request_id => l_attached_doc.request_id
1284 ,X_program_application_id => l_attached_doc.program_application_id
1285 ,X_program_id => l_attached_doc.program_id
1286 ,X_program_update_date => sysdate
1287 ,X_doc_attribute_Category => NULL
1288 ,X_doc_attribute1 => NULL
1289 ,X_doc_attribute2 => NULL
1290 ,X_doc_attribute3 => NULL
1291 ,X_doc_attribute4 => NULL
1292 ,X_doc_attribute5 => NULL
1293 ,X_doc_attribute6 => NULL
1294 ,X_doc_attribute7 => NULL
1295 ,X_doc_attribute8 => NULL
1296 ,X_doc_attribute9 => NULL
1297 ,X_doc_attribute10 => NULL
1298 ,X_doc_attribute11 => NULL
1299 ,X_doc_attribute12 => NULL
1300 ,X_doc_attribute13 => NULL
1301 ,X_doc_attribute14 => NULL
1302 ,X_doc_attribute15 => NULL
1303 ,X_datatype_id => l_doc.datatype_id
1304 ,X_category_id => l_doc.category_id
1305 ,X_security_type => l_doc.security_type
1306 ,X_publish_flag => l_doc.publish_flag
1307 ,X_usage_type => l_doc.usage_type
1308 ,X_language => userenv('LANG')
1309 ,X_image_type =>null
1310 ,X_storage_type => null
1311 ,X_start_date_active => sysdate
1312 ,X_end_date_active =>null
1313 ,X_description => l_description
1314 ,X_file_name => null
1315 ,X_media_id => l_doc.media_id
1316 ,X_url => NULL
1317 ,X_title => 'ROUTING DETAILS'
1318 ,x_attribute_Category => NULL
1319 ,x_attribute1 => NULL
1320 ,x_attribute2 => NULL
1321 ,x_attribute3 => NULL
1322 ,x_attribute4 => NULL
1323 ,x_attribute5 => NULL
1324 ,x_attribute6 => NULL
1325 ,x_attribute7 => NULL
1326 ,x_attribute8 => NULL
1327 ,x_attribute9 => NULL
1328 ,x_attribute10 => NULL
1329 ,x_attribute11 => NULL
1330 ,x_attribute12 => NULL
1331 ,x_attribute13 => NULL
1332 ,x_attribute14 => NULL
1333 ,x_attribute15 => NULL
1334 );
1335 UPDATE fnd_documents_long_text
1336 SET long_text = l_text
1337 WHERE media_id = l_doc.media_id;
1338 END IF;
1339 END IF;
1340 EXCEPTION
1341
1342 WHEN OTHERS THEN
1343 x_return_status := FND_API.G_RET_STS_ERROR;
1344 x_msg_data := SQLERRM;
1345 END;
1346
1347 FUNCTION get_config_value (p_config_tbl inv_ebi_name_value_tbl,p_config_prop_name VARCHAR2 )
1348 RETURN VARCHAR2
1349 IS
1350 BEGIN
1351 FOR k in 1..p_config_tbl.COUNT LOOP
1352 IF (p_config_tbl(k).param_name = p_config_prop_name) THEN
1353 RETURN p_config_tbl(k).param_value;
1354 END IF;
1355 END LOOP;
1356 RETURN NULL;
1357 END;
1358
1359 /* ======================================================================== */
1360
1361 /* PROCEDURE: */
1362
1363 /* Delete_FormulaDetail */
1364
1365 /* */
1366
1367 /* DESCRIPTION: */
1368
1369 /* The procedure deletes the formula details that have been removed */
1370
1371 /* from the formula. */
1372
1373 /* ======================================================================== */
1374
1375 PROCEDURE Delete_FormulaDetail(
1376 p_formula_detail_tbl IN GMD_FORMULA_DETAIL_PUB.formula_update_dtl_tbl_type
1377 ,x_line_delete_tbl OUT NOCOPY gmd_ebi_formula_line_del_tbl
1378 ,x_return_status OUT NOCOPY VARCHAR2
1379 ,x_msg_count OUT NOCOPY NUMBER
1380 ,x_msg_data OUT NOCOPY VARCHAR2
1381 )
1382 IS
1383
1384 l_line_delete_tbl gmd_ebi_formula_line_del_tbl := gmd_ebi_formula_line_del_tbl();
1385 l_line_delete_obj gmd_ebi_formula_line_del_obj;
1386 l_delete_ctr NUMBER :=1;
1387 l_msg_count NUMBER;
1388 l_msg_data VARCHAR2(32000);
1389 l_ret_status VARCHAR2(10);
1390 l_formula_delete_detail_rec GMD_FORMULA_COMMON_PUB.formula_update_rec_type;
1391 l_formula_delete_detail_tbl GMD_FORMULA_DETAIL_PUB.formula_update_dtl_tbl_type;
1392 l_line_found NUMBER;
1393
1394
1395 CURSOR get_formula_line_id
1396 IS
1397 SELECT formula_id,formulaline_id FROM fm_matl_dtl WHERE formula_id = p_formula_detail_tbl(1).formula_id;
1398
1399 BEGIN
1400 FOR formula_line IN get_formula_line_id LOOP
1401 l_line_found:=0;
1402 FOR i IN 1..p_formula_detail_tbl.Count LOOP
1403 IF formula_line.formulaline_id = p_formula_detail_tbl(i).formulaline_id THEN
1404 l_line_found:=1;
1405 END IF;
1406 END LOOP;
1407 IF l_line_found = 0 THEN
1408 l_formula_delete_detail_rec.formula_id := formula_line.formula_id;
1409 l_formula_delete_detail_rec.formulaline_id := formula_line.formulaline_id;
1410 l_formula_delete_detail_rec.record_type := 'D';
1411 l_formula_delete_detail_tbl(l_delete_ctr) := l_formula_delete_detail_rec;
1412 l_line_delete_obj := gmd_ebi_formula_line_del_obj(formula_line.formulaline_id);
1413 l_line_delete_tbl.extend();
1414 l_line_delete_tbl(l_delete_ctr):= l_line_delete_obj;
1415 l_delete_ctr := l_delete_ctr + 1;
1416 END IF;
1417 END LOOP;
1418 IF l_formula_delete_detail_tbl.Count > 0 THEN
1419 GMD_FORMULA_DETAIL_PUB.Delete_FormulaDetail(p_api_version => 1.1
1420 ,p_init_msg_list => FND_API.G_FALSE
1421 ,p_commit => FND_API.G_FALSE
1422 ,p_called_from_forms => 'NO'
1423 ,x_return_status => l_ret_status
1424 ,x_msg_count => l_msg_count
1425 ,x_msg_data => l_msg_data
1426 ,p_formula_detail_tbl => l_formula_delete_detail_tbl);
1427
1428 IF (l_ret_status NOT IN(FND_API.G_RET_STS_SUCCESS,'Q')) THEN
1429 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1430 END IF;
1431 END IF;
1432 x_line_delete_tbl := l_line_delete_tbl;
1433 x_return_status := l_ret_status;
1434 EXCEPTION
1435 WHEN OTHERS THEN
1436 x_return_status := FND_API.G_RET_STS_ERROR;
1437 IF l_msg_data IS NOT NULL THEN
1438 x_msg_data := l_msg_data;
1439 ELSE
1440 x_msg_data := SQLERRM||' AT DELETE FORMULA LINES';
1441 END IF;
1442 END;
1443
1444 /* ======================================================================== */
1445
1446 /* PROCEDURE: */
1447
1448 /* is_updateable */
1449
1450 /* */
1451
1452 /* DESCRIPTION: */
1453
1454 /* This function returns 'Y' or 'N' based on whether formula is */
1455
1456 /* version controlled or not. */
1457
1458 /* ======================================================================== */
1459
1460 FUNCTION is_updateable(p_orgn_id IN NUMBER)
1461 RETURN VARCHAR2
1462 IS
1463 l_vers_cntrl VARCHAR2(1);
1464 l_return_stat VARCHAR2(1);
1465 BEGIN
1466 GMD_API_GRP.FETCH_PARM_VALUES(P_orgn_id => p_orgn_id,
1467 P_parm_name => 'GMD_FORMULA_VERSION_CONTROL',
1468 P_parm_value => l_vers_cntrl,
1469 x_return_status => l_return_stat);
1470 IF(l_vers_cntrl = 'Y') THEN
1471 RETURN FND_API.G_FALSE;
1472 ELSE
1473 RETURN FND_API.G_TRUE;
1474 END IF;
1475 END;
1476
1477 /* ======================================================================== */
1478
1479 /* PROCEDURE: */
1480
1481 /* sync_formula */
1482
1483 /* */
1484
1485 /* DESCRIPTION: */
1486
1487 /* This function does a sync of formula header and formula details */
1488
1489 /* ======================================================================== */
1490
1491 PROCEDURE sync_formula(
1492 p_formula_obj IN gmd_ebi_formula_obj
1493 ,x_line_delete_tbl OUT NOCOPY gmd_ebi_formula_line_del_tbl
1494 ,x_return_status OUT NOCOPY VARCHAR2
1495 ,x_msg_count OUT NOCOPY NUMBER
1496 ,x_msg_data OUT NOCOPY VARCHAR2
1497 )
1498 IS
1499 l_formula_insert_header_rec GMD_FORMULA_COMMON_PUB.formula_insert_rec_type;
1500 l_formula_insert_header_tbl GMD_FORMULA_PUB.formula_insert_hdr_tbl_type;
1501 l_formula_update_header_rec GMD_FORMULA_COMMON_PUB.formula_update_rec_type;
1502 l_formula_update_header_tbl GMD_FORMULA_PUB.formula_update_hdr_tbl_type;
1503 l_formula_insert_detail_rec GMD_FORMULA_COMMON_PUB.formula_insert_rec_type;
1504 l_formula_insert_detail_tbl GMD_FORMULA_DETAIL_PUB.formula_insert_dtl_tbl_type;
1505 l_formula_update_detail_rec GMD_FORMULA_COMMON_PUB.formula_update_rec_type;
1506 l_formula_update_detail_tbl GMD_FORMULA_DETAIL_PUB.formula_update_dtl_tbl_type;
1507 l_insert_detailctr NUMBER := 1;
1508 l_insert_headerctr NUMBER := 1;
1509 l_update_detailctr NUMBER := 1;
1510 l_msg_count NUMBER;
1511 l_msg_data VARCHAR2(32000);
1512 l_ret_status VARCHAR2(10);
1513 l_zero_ing_qty VARCHAR2(100);
1514 l_formula_owner_name VARCHAR2(100);
1515 l_formula_owner_id NUMBER;
1516 l_update_header_flag NUMBER;
1517 l_config_tbl inv_ebi_name_value_tbl;
1518 l_form_version NUMBER;
1519 l_line_delete_tbl gmd_ebi_formula_line_del_tbl;
1520 l_form_type NUMBER;
1521
1522 BEGIN
1523 x_return_status := FND_API.G_RET_STS_SUCCESS;
1524 l_config_tbl := p_formula_obj.p_config_tbl;
1525 IF l_config_tbl IS NOT NULL AND l_config_tbl.Count>0 THEN
1526 l_zero_ing_qty := get_config_value(l_config_tbl,'ZERO_ING_ALLOWED');
1527 l_formula_owner_name := get_config_value(l_config_tbl,'FORMULA_OWNER_NAME');
1528 SELECT user_id INTO l_formula_owner_id FROM fnd_user WHERE user_name = l_formula_owner_name;
1529 END IF;
1530 IF (p_formula_obj.formula_header.formula_type NOT IN (0,1)) THEN
1531 l_form_type := 0;
1532 ELSE
1533 l_form_type := p_formula_obj.formula_header.formula_type;
1534 END IF;
1535 FOR j in 1..p_formula_obj.formula_details.COUNT LOOP
1536 IF ( p_formula_obj.formula_header.formula_id is NULL ) THEN
1537 SELECT Max(formula_vers)+1 INTO l_form_version FROM fm_form_mst_b WHERE formula_no = p_formula_obj.formula_header.formula_no;
1538 IF l_form_version IS NULL THEN
1539 l_form_version:=1;
1540 END IF;
1541 l_formula_insert_header_rec.formula_id := p_formula_obj.formula_header.formula_id;
1542 l_formula_insert_header_rec.formula_no := p_formula_obj.formula_header.formula_no;
1543 l_formula_insert_header_rec.formula_vers := l_form_version;
1544 l_formula_insert_header_rec.formula_type := l_form_type;
1545 l_formula_insert_header_rec.master_formula_id := p_formula_obj.formula_header.master_formula_id;
1546 l_formula_insert_header_rec.scale_type_hdr := p_formula_obj.formula_header.scale_type_hdr;
1547 l_formula_insert_header_rec.formula_desc1 := p_formula_obj.formula_header.formula_desc1;
1548 l_formula_insert_header_rec.formula_desc2 := p_formula_obj.formula_header.formula_desc2;
1549 l_formula_insert_header_rec.formula_class := p_formula_obj.formula_header.formula_class;
1550 l_formula_insert_header_rec.fmcontrol_class := p_formula_obj.formula_header.fmcontrol_class;
1551 l_formula_insert_header_rec.inactive_ind := Nvl(p_formula_obj.formula_header.inactive_ind,0);
1552 l_formula_insert_header_rec.owner_organization_id := p_formula_obj.formula_header.owner_organization_id;
1553 l_formula_insert_header_rec.total_input_qty := p_formula_obj.formula_header.total_input_qty ;
1554 l_formula_insert_header_rec.total_output_qty := p_formula_obj.formula_header.total_output_qty ;
1555 l_formula_insert_header_rec.yield_uom := p_formula_obj.formula_header.yield_uom;
1556 l_formula_insert_header_rec.formula_status := p_formula_obj.formula_header.formula_status ;
1557 l_formula_insert_header_rec.owner_id := l_formula_owner_id;
1558 l_formula_insert_header_rec.attribute1 := p_formula_obj.formula_header.attribute1;
1559 l_formula_insert_header_rec.attribute2 := p_formula_obj.formula_header.attribute2;
1560 l_formula_insert_header_rec.attribute3 := p_formula_obj.formula_header.attribute3;
1561 l_formula_insert_header_rec.attribute4 := p_formula_obj.formula_header.attribute4;
1562 l_formula_insert_header_rec.attribute5 := p_formula_obj.formula_header.attribute5;
1563 l_formula_insert_header_rec.attribute6 := p_formula_obj.formula_header.attribute6;
1564 l_formula_insert_header_rec.attribute7 := p_formula_obj.formula_header.attribute7;
1565 l_formula_insert_header_rec.attribute8 := p_formula_obj.formula_header.attribute8;
1566 l_formula_insert_header_rec.attribute9 := p_formula_obj.formula_header.attribute9;
1567 l_formula_insert_header_rec.attribute10 := p_formula_obj.formula_header.attribute10;
1568 l_formula_insert_header_rec.attribute11 := p_formula_obj.formula_header.attribute11;
1569 l_formula_insert_header_rec.attribute12 := p_formula_obj.formula_header.attribute12;
1570 l_formula_insert_header_rec.attribute13 := p_formula_obj.formula_header.attribute13;
1571 l_formula_insert_header_rec.attribute14 := p_formula_obj.formula_header.attribute14;
1572 l_formula_insert_header_rec.attribute15 := p_formula_obj.formula_header.attribute15;
1573 l_formula_insert_header_rec.attribute16 := p_formula_obj.formula_header.attribute16;
1574 l_formula_insert_header_rec.attribute17 := p_formula_obj.formula_header.attribute17;
1575 l_formula_insert_header_rec.attribute18 := p_formula_obj.formula_header.attribute18;
1576 l_formula_insert_header_rec.attribute19 := p_formula_obj.formula_header.attribute19;
1577 l_formula_insert_header_rec.attribute20 := p_formula_obj.formula_header.attribute20;
1578 l_formula_insert_header_rec.attribute21 := p_formula_obj.formula_header.attribute21;
1579 l_formula_insert_header_rec.attribute22 := p_formula_obj.formula_header.attribute22;
1580 l_formula_insert_header_rec.attribute23 := p_formula_obj.formula_header.attribute23;
1581 l_formula_insert_header_rec.attribute24 := p_formula_obj.formula_header.attribute24;
1582 l_formula_insert_header_rec.attribute25 := p_formula_obj.formula_header.attribute25;
1583 l_formula_insert_header_rec.attribute26 := p_formula_obj.formula_header.attribute26;
1584 l_formula_insert_header_rec.attribute27 := p_formula_obj.formula_header.attribute27;
1585 l_formula_insert_header_rec.attribute28 := p_formula_obj.formula_header.attribute28;
1586 l_formula_insert_header_rec.attribute29 := p_formula_obj.formula_header.attribute29;
1587 l_formula_insert_header_rec.attribute30 := p_formula_obj.formula_header.attribute30;
1588 l_formula_insert_header_rec.attribute_category := p_formula_obj.formula_header.attribute_category;
1589 l_formula_insert_header_rec.text_code_hdr := p_formula_obj.formula_header.text_code_hdr;
1590 l_formula_insert_header_rec.delete_mark := NVL(p_formula_obj.formula_header.delete_mark,0);
1591 l_formula_insert_header_rec.created_by := FND_GLOBAL.USER_ID;
1592 l_formula_insert_header_rec.creation_date := NVL(p_formula_obj.formula_header.creation_date, SYSDATE);
1593 l_formula_insert_header_rec.last_update_date := NVL(p_formula_obj.formula_header.last_update_date, SYSDATE);
1594 l_formula_insert_header_rec.last_update_login := NVL(p_formula_obj.formula_header.last_update_login, FND_GLOBAL.USER_ID);
1595 l_formula_insert_header_rec.last_updated_by := FND_GLOBAL.USER_ID;
1596 l_formula_insert_header_rec.formulaline_id := p_formula_obj.formula_details(j).formulaline_id;
1597 l_formula_insert_header_rec.line_type := p_formula_obj.formula_details(j).line_type;
1598 l_formula_insert_header_rec.line_no := p_formula_obj.formula_details(j).line_no;
1599 l_formula_insert_header_rec.inventory_item_id := p_formula_obj.formula_details(j).inventory_item_id;
1600 l_formula_insert_header_rec.revision := p_formula_obj.formula_details(j).revision;
1601 l_formula_insert_header_rec.qty := p_formula_obj.formula_details(j).qty;
1602 l_formula_insert_header_rec.detail_uom := p_formula_obj.formula_details(j).detail_uom;
1603 l_formula_insert_header_rec.release_type := p_formula_obj.formula_details(j).release_type;
1604 l_formula_insert_header_rec.scrap_factor := p_formula_obj.formula_details(j).scrap_factor;
1605 l_formula_insert_header_rec.scale_type_dtl := p_formula_obj.formula_details(j).scale_type_dtl;
1606 l_formula_insert_header_rec.cost_alloc := p_formula_obj.formula_details(j).cost_alloc;
1607 l_formula_insert_header_rec.phantom_type := p_formula_obj.formula_details(j).phantom_type;
1608 l_formula_insert_header_rec.buffer_ind := p_formula_obj.formula_details(j).buffer_ind;
1609 l_formula_insert_header_rec.tpformula_id := p_formula_obj.formula_details(j).tpformula_id;
1610 l_formula_insert_header_rec.iaformula_id := p_formula_obj.formula_details(j).iaformula_id;
1611 l_formula_insert_header_rec.scale_multiple := p_formula_obj.formula_details(j).scale_multiple;
1612 l_formula_insert_header_rec.contribute_yield_ind := p_formula_obj.formula_details(j).contribute_yield_ind;
1613 l_formula_insert_header_rec.scale_uom := p_formula_obj.formula_details(j).scale_uom;
1614 l_formula_insert_header_rec.contribute_step_qty_ind := p_formula_obj.formula_details(j).contribute_step_qty_ind;
1615 l_formula_insert_header_rec.scale_rounding_variance := p_formula_obj.formula_details(j).scale_rounding_variance;
1616 l_formula_insert_header_rec.rounding_direction := p_formula_obj.formula_details(j).rounding_direction;
1617 l_formula_insert_header_rec.by_product_type := p_formula_obj.formula_details(j).by_product_type;
1618 l_formula_insert_header_rec.ingredient_end_date := p_formula_obj.formula_details(j).ingredient_end_date;
1619 l_formula_insert_header_rec.text_code_dtl := p_formula_obj.formula_details(j).text_code_dtl;
1620 l_formula_insert_header_rec.dtl_attribute_category := p_formula_obj.formula_details(j).dtl_attribute_category;
1621 l_formula_insert_header_rec.dtl_attribute1 := p_formula_obj.formula_details(j).dtl_attribute1;
1622 l_formula_insert_header_rec.dtl_attribute2 := p_formula_obj.formula_details(j).dtl_attribute2;
1623 l_formula_insert_header_rec.dtl_attribute3 := p_formula_obj.formula_details(j).dtl_attribute3;
1624 l_formula_insert_header_rec.dtl_attribute4 := p_formula_obj.formula_details(j).dtl_attribute4;
1625 l_formula_insert_header_rec.dtl_attribute5 := p_formula_obj.formula_details(j).dtl_attribute5;
1626 l_formula_insert_header_rec.dtl_attribute6 := p_formula_obj.formula_details(j).dtl_attribute6;
1627 l_formula_insert_header_rec.dtl_attribute7 := p_formula_obj.formula_details(j).dtl_attribute7;
1628 l_formula_insert_header_rec.dtl_attribute8 := p_formula_obj.formula_details(j).dtl_attribute8;
1629 l_formula_insert_header_rec.dtl_attribute9 := p_formula_obj.formula_details(j).dtl_attribute9;
1630 l_formula_insert_header_rec.dtl_attribute10 := p_formula_obj.formula_details(j).dtl_attribute10;
1631 l_formula_insert_header_rec.dtl_attribute11 := p_formula_obj.formula_details(j).dtl_attribute11;
1632 l_formula_insert_header_rec.dtl_attribute12 := p_formula_obj.formula_details(j).dtl_attribute12;
1633 l_formula_insert_header_rec.dtl_attribute13 := p_formula_obj.formula_details(j).dtl_attribute13;
1634 l_formula_insert_header_rec.dtl_attribute14 := p_formula_obj.formula_details(j).dtl_attribute14;
1635 l_formula_insert_header_rec.dtl_attribute15 := p_formula_obj.formula_details(j).dtl_attribute15;
1636 l_formula_insert_header_rec.dtl_attribute16 := p_formula_obj.formula_details(j).dtl_attribute16;
1637 l_formula_insert_header_rec.dtl_attribute17 := p_formula_obj.formula_details(j).dtl_attribute17;
1638 l_formula_insert_header_rec.dtl_attribute18 := p_formula_obj.formula_details(j).dtl_attribute18;
1639 l_formula_insert_header_rec.dtl_attribute19 := p_formula_obj.formula_details(j).dtl_attribute19;
1640 l_formula_insert_header_rec.dtl_attribute20 := p_formula_obj.formula_details(j).dtl_attribute20;
1641 l_formula_insert_header_rec.dtl_attribute21 := p_formula_obj.formula_details(j).dtl_attribute21;
1642 l_formula_insert_header_rec.dtl_attribute22 := p_formula_obj.formula_details(j).dtl_attribute22;
1643 l_formula_insert_header_rec.dtl_attribute23 := p_formula_obj.formula_details(j).dtl_attribute23;
1644 l_formula_insert_header_rec.dtl_attribute24 := p_formula_obj.formula_details(j).dtl_attribute24;
1645 l_formula_insert_header_rec.dtl_attribute25 := p_formula_obj.formula_details(j).dtl_attribute25;
1646 l_formula_insert_header_rec.dtl_attribute26 := p_formula_obj.formula_details(j).dtl_attribute26;
1647 l_formula_insert_header_rec.dtl_attribute27 := p_formula_obj.formula_details(j).dtl_attribute27;
1648 l_formula_insert_header_rec.dtl_attribute28 := p_formula_obj.formula_details(j).dtl_attribute28;
1649 l_formula_insert_header_rec.dtl_attribute29 := p_formula_obj.formula_details(j).dtl_attribute29;
1650 l_formula_insert_header_rec.dtl_attribute30 := p_formula_obj.formula_details(j).dtl_attribute30;
1651 l_formula_insert_header_tbl(l_insert_headerctr) := l_formula_insert_header_rec;
1652 l_insert_headerctr := l_insert_headerctr + 1;
1653 ELSE
1654 IF l_formula_update_header_tbl.Count = 0 THEN
1655 IF(is_updateable( p_formula_obj.formula_header.owner_organization_id)=FND_API.G_FALSE)THEN
1656 FND_MESSAGE.set_name('GMD','GMD_EBI_VERSION_ERROR');
1657 FND_MESSAGE.set_token('FORM', p_formula_obj.formula_header.formula_no);
1658 FND_MSG_PUB.add;
1659 RAISE FND_API.g_exc_error;
1660 END IF;
1661 l_formula_update_header_rec.formula_id := p_formula_obj.formula_header.formula_id;
1662 l_formula_update_header_rec.formula_no := p_formula_obj.formula_header.formula_no;
1663 l_formula_update_header_rec.formula_type := l_form_type;
1664 l_formula_update_header_rec.scale_type_hdr := p_formula_obj.formula_header.scale_type_hdr;
1665 l_formula_update_header_rec.formula_desc1 := p_formula_obj.formula_header.formula_desc1;
1666 l_formula_update_header_rec.formula_desc2 := p_formula_obj.formula_header.formula_desc2;
1667 l_formula_update_header_rec.formula_class := p_formula_obj.formula_header.formula_class;
1668 l_formula_update_header_rec.fmcontrol_class := p_formula_obj.formula_header.fmcontrol_class;
1669 l_formula_update_header_rec.inactive_ind := Nvl(p_formula_obj.formula_header.inactive_ind,0);
1670 l_formula_update_header_rec.owner_organization_id := p_formula_obj.formula_header.owner_organization_id;
1671 l_formula_update_header_rec.total_input_qty := p_formula_obj.formula_header.total_input_qty ;
1672 l_formula_update_header_rec.total_output_qty := p_formula_obj.formula_header.total_output_qty ;
1673 l_formula_update_header_rec.yield_uom := p_formula_obj.formula_header.yield_uom;
1674 IF p_formula_obj.formula_header.formula_status IS NOT NULL THEN
1675 l_formula_update_header_rec.formula_status := p_formula_obj.formula_header.formula_status;
1676 ELSE
1677 l_formula_update_header_rec.formula_status := NULL;
1678 END IF;
1679 l_formula_update_header_rec.owner_id := l_formula_owner_id;
1680 l_formula_update_header_rec.attribute1 := p_formula_obj.formula_header.attribute1;
1681 l_formula_update_header_rec.attribute2 := p_formula_obj.formula_header.attribute2;
1682 l_formula_update_header_rec.attribute3 := p_formula_obj.formula_header.attribute3;
1683 l_formula_update_header_rec.attribute4 := p_formula_obj.formula_header.attribute4;
1684 l_formula_update_header_rec.attribute5 := p_formula_obj.formula_header.attribute5;
1685 l_formula_update_header_rec.attribute6 := p_formula_obj.formula_header.attribute6;
1686 l_formula_update_header_rec.attribute7 := p_formula_obj.formula_header.attribute7;
1687 l_formula_update_header_rec.attribute8 := p_formula_obj.formula_header.attribute8;
1688 l_formula_update_header_rec.attribute9 := p_formula_obj.formula_header.attribute9;
1689 l_formula_update_header_rec.attribute10 := p_formula_obj.formula_header.attribute10;
1690 l_formula_update_header_rec.attribute11 := p_formula_obj.formula_header.attribute11;
1691 l_formula_update_header_rec.attribute12 := p_formula_obj.formula_header.attribute12;
1692 l_formula_update_header_rec.attribute13 := p_formula_obj.formula_header.attribute13;
1693 l_formula_update_header_rec.attribute14 := p_formula_obj.formula_header.attribute14;
1694 l_formula_update_header_rec.attribute15 := p_formula_obj.formula_header.attribute15;
1695 l_formula_update_header_rec.attribute16 := p_formula_obj.formula_header.attribute16;
1696 l_formula_update_header_rec.attribute17 := p_formula_obj.formula_header.attribute17;
1697 l_formula_update_header_rec.attribute18 := p_formula_obj.formula_header.attribute18;
1698 l_formula_update_header_rec.attribute19 := p_formula_obj.formula_header.attribute19;
1699 l_formula_update_header_rec.attribute20 := p_formula_obj.formula_header.attribute20;
1700 l_formula_update_header_rec.attribute21 := p_formula_obj.formula_header.attribute21;
1701 l_formula_update_header_rec.attribute22 := p_formula_obj.formula_header.attribute22;
1702 l_formula_update_header_rec.attribute23 := p_formula_obj.formula_header.attribute23;
1703 l_formula_update_header_rec.attribute24 := p_formula_obj.formula_header.attribute24;
1704 l_formula_update_header_rec.attribute25 := p_formula_obj.formula_header.attribute25;
1705 l_formula_update_header_rec.attribute26 := p_formula_obj.formula_header.attribute26;
1706 l_formula_update_header_rec.attribute27 := p_formula_obj.formula_header.attribute27;
1707 l_formula_update_header_rec.attribute28 := p_formula_obj.formula_header.attribute28;
1708 l_formula_update_header_rec.attribute29 := p_formula_obj.formula_header.attribute29;
1709 l_formula_update_header_rec.attribute30 := p_formula_obj.formula_header.attribute30;
1710 l_formula_update_header_rec.attribute_category:= p_formula_obj.formula_header.attribute_category;
1711 l_formula_update_header_rec.text_code_hdr := p_formula_obj.formula_header.text_code_hdr;
1712 l_formula_update_header_rec.delete_mark := NVL(p_formula_obj.formula_header.delete_mark,0);
1713 l_formula_update_header_rec.created_by := FND_GLOBAL.USER_ID;
1714 l_formula_update_header_rec.creation_date := NVL(p_formula_obj.formula_header.creation_date, SYSDATE);
1715 l_formula_update_header_rec.last_update_date := NVL(p_formula_obj.formula_header.last_update_date, SYSDATE);
1716 l_formula_update_header_rec.last_update_login := NVL(p_formula_obj.formula_header.last_update_login, FND_GLOBAL.USER_ID);
1717 l_formula_update_header_rec.last_updated_by := FND_GLOBAL.USER_ID;
1718 l_formula_update_header_tbl(1) := l_formula_update_header_rec;
1719 END IF;
1720 IF (p_formula_obj.formula_details(j).formulaline_id IS NULL) THEN
1721 l_formula_insert_detail_rec.formula_id := p_formula_obj.formula_details(j).formula_id;
1722 l_formula_insert_detail_rec.formulaline_id := p_formula_obj.formula_details(j).formulaline_id;
1723 l_formula_insert_detail_rec.line_type := p_formula_obj.formula_details(j).line_type;
1724 l_formula_insert_detail_rec.line_no := p_formula_obj.formula_details(j).line_no;
1725 l_formula_insert_detail_rec.inventory_item_id := p_formula_obj.formula_details(j).inventory_item_id;
1726 l_formula_insert_detail_rec.revision := p_formula_obj.formula_details(j).revision;
1727 l_formula_insert_detail_rec.qty := p_formula_obj.formula_details(j).qty;
1728 l_formula_insert_detail_rec.detail_uom := p_formula_obj.formula_details(j).detail_uom;
1729 l_formula_insert_detail_rec.release_type := p_formula_obj.formula_details(j).release_type;
1730 l_formula_insert_detail_rec.scrap_factor := p_formula_obj.formula_details(j).scrap_factor;
1731 l_formula_insert_detail_rec.scale_type_dtl := p_formula_obj.formula_details(j).scale_type_dtl;
1732 l_formula_insert_detail_rec.cost_alloc := p_formula_obj.formula_details(j).cost_alloc;
1733 l_formula_insert_detail_rec.phantom_type := p_formula_obj.formula_details(j).phantom_type;
1734 l_formula_insert_detail_rec.buffer_ind := p_formula_obj.formula_details(j).buffer_ind;
1735 l_formula_insert_detail_rec.tpformula_id := p_formula_obj.formula_details(j).tpformula_id;
1736 l_formula_insert_detail_rec.iaformula_id := p_formula_obj.formula_details(j).iaformula_id;
1737 l_formula_insert_detail_rec.scale_multiple := p_formula_obj.formula_details(j).scale_multiple;
1738 l_formula_insert_detail_rec.contribute_yield_ind := p_formula_obj.formula_details(j).contribute_yield_ind;
1739 l_formula_insert_detail_rec.scale_uom := p_formula_obj.formula_details(j).scale_uom;
1740 l_formula_insert_detail_rec.contribute_step_qty_ind := p_formula_obj.formula_details(j).contribute_step_qty_ind;
1741 l_formula_insert_detail_rec.scale_rounding_variance := p_formula_obj.formula_details(j).scale_rounding_variance;
1742 l_formula_insert_detail_rec.rounding_direction := p_formula_obj.formula_details(j).rounding_direction;
1743 l_formula_insert_detail_rec.by_product_type := p_formula_obj.formula_details(j).by_product_type;
1744 l_formula_insert_detail_rec.ingredient_end_date := p_formula_obj.formula_details(j).ingredient_end_date;
1745 l_formula_insert_detail_rec.text_code_dtl := p_formula_obj.formula_details(j).text_code_dtl;
1746 l_formula_insert_detail_rec.dtl_attribute_category := p_formula_obj.formula_details(j).dtl_attribute_category;
1747 l_formula_insert_detail_rec.dtl_attribute1 := p_formula_obj.formula_details(j).dtl_attribute1;
1748 l_formula_insert_detail_rec.dtl_attribute2 := p_formula_obj.formula_details(j).dtl_attribute2;
1749 l_formula_insert_detail_rec.dtl_attribute3 := p_formula_obj.formula_details(j).dtl_attribute3;
1750 l_formula_insert_detail_rec.dtl_attribute4 := p_formula_obj.formula_details(j).dtl_attribute4;
1751 l_formula_insert_detail_rec.dtl_attribute5 := p_formula_obj.formula_details(j).dtl_attribute5;
1752 l_formula_insert_detail_rec.dtl_attribute6 := p_formula_obj.formula_details(j).dtl_attribute6;
1753 l_formula_insert_detail_rec.dtl_attribute7 := p_formula_obj.formula_details(j).dtl_attribute7;
1754 l_formula_insert_detail_rec.dtl_attribute8 := p_formula_obj.formula_details(j).dtl_attribute8;
1755 l_formula_insert_detail_rec.dtl_attribute9 := p_formula_obj.formula_details(j).dtl_attribute9;
1756 l_formula_insert_detail_rec.dtl_attribute10 := p_formula_obj.formula_details(j).dtl_attribute10;
1757 l_formula_insert_detail_rec.dtl_attribute11 := p_formula_obj.formula_details(j).dtl_attribute11;
1758 l_formula_insert_detail_rec.dtl_attribute12 := p_formula_obj.formula_details(j).dtl_attribute12;
1759 l_formula_insert_detail_rec.dtl_attribute13 := p_formula_obj.formula_details(j).dtl_attribute13;
1760 l_formula_insert_detail_rec.dtl_attribute14 := p_formula_obj.formula_details(j).dtl_attribute14;
1761 l_formula_insert_detail_rec.dtl_attribute15 := p_formula_obj.formula_details(j).dtl_attribute15;
1762 l_formula_insert_detail_rec.dtl_attribute16 := p_formula_obj.formula_details(j).dtl_attribute16;
1763 l_formula_insert_detail_rec.dtl_attribute17 := p_formula_obj.formula_details(j).dtl_attribute17;
1764 l_formula_insert_detail_rec.dtl_attribute18 := p_formula_obj.formula_details(j).dtl_attribute18;
1765 l_formula_insert_detail_rec.dtl_attribute19 := p_formula_obj.formula_details(j).dtl_attribute19;
1766 l_formula_insert_detail_rec.dtl_attribute20 := p_formula_obj.formula_details(j).dtl_attribute20;
1767 l_formula_insert_detail_rec.dtl_attribute21 := p_formula_obj.formula_details(j).dtl_attribute21;
1768 l_formula_insert_detail_rec.dtl_attribute22 := p_formula_obj.formula_details(j).dtl_attribute22;
1769 l_formula_insert_detail_rec.dtl_attribute23 := p_formula_obj.formula_details(j).dtl_attribute23;
1770 l_formula_insert_detail_rec.dtl_attribute24 := p_formula_obj.formula_details(j).dtl_attribute24;
1771 l_formula_insert_detail_rec.dtl_attribute25 := p_formula_obj.formula_details(j).dtl_attribute25;
1772 l_formula_insert_detail_rec.dtl_attribute26 := p_formula_obj.formula_details(j).dtl_attribute26;
1773 l_formula_insert_detail_rec.dtl_attribute27 := p_formula_obj.formula_details(j).dtl_attribute27;
1774 l_formula_insert_detail_rec.dtl_attribute28 := p_formula_obj.formula_details(j).dtl_attribute28;
1775 l_formula_insert_detail_rec.dtl_attribute29 := p_formula_obj.formula_details(j).dtl_attribute29;
1776 l_formula_insert_detail_rec.dtl_attribute30 := p_formula_obj.formula_details(j).dtl_attribute30;
1777
1778 l_formula_insert_detail_tbl(l_insert_detailctr) := l_formula_insert_detail_rec;
1779 l_insert_detailctr := l_insert_detailctr + 1;
1780 ELSE
1781 l_formula_update_detail_rec.formula_id := p_formula_obj.formula_details(j).formula_id;
1782 l_formula_update_detail_rec.formulaline_id := p_formula_obj.formula_details(j).formulaline_id;
1783 l_formula_update_detail_rec.owner_organization_id := p_formula_obj.formula_details(j).owner_organization_id;
1784 l_formula_update_detail_rec.line_type := p_formula_obj.formula_details(j).line_type;
1785 l_formula_update_detail_rec.line_no := NULL;
1786 l_formula_update_detail_rec.inventory_item_id := p_formula_obj.formula_details(j).inventory_item_id;
1787 l_formula_update_detail_rec.revision := p_formula_obj.formula_details(j).revision;
1788 l_formula_update_detail_rec.qty := p_formula_obj.formula_details(j).qty;
1789 l_formula_update_detail_rec.detail_uom := p_formula_obj.formula_details(j).detail_uom;
1790 l_formula_update_detail_rec.release_type := p_formula_obj.formula_details(j).release_type;
1791 l_formula_update_detail_rec.scrap_factor := p_formula_obj.formula_details(j).scrap_factor;
1792 l_formula_update_detail_rec.scale_type_dtl := p_formula_obj.formula_details(j).scale_type_dtl;
1793 l_formula_update_detail_rec.cost_alloc := p_formula_obj.formula_details(j).cost_alloc;
1794 l_formula_update_detail_rec.phantom_type := p_formula_obj.formula_details(j).phantom_type;
1795 l_formula_update_detail_rec.buffer_ind := p_formula_obj.formula_details(j).buffer_ind;
1796 l_formula_update_detail_rec.tpformula_id := p_formula_obj.formula_details(j).tpformula_id;
1797 l_formula_update_detail_rec.iaformula_id := p_formula_obj.formula_details(j).iaformula_id;
1798 l_formula_update_detail_rec.scale_multiple := p_formula_obj.formula_details(j).scale_multiple;
1799 l_formula_update_detail_rec.contribute_yield_ind := p_formula_obj.formula_details(j).contribute_yield_ind;
1800 l_formula_update_detail_rec.scale_uom := p_formula_obj.formula_details(j).scale_uom;
1801 l_formula_update_detail_rec.contribute_step_qty_ind := p_formula_obj.formula_details(j).contribute_step_qty_ind;
1802 l_formula_update_detail_rec.scale_rounding_variance := p_formula_obj.formula_details(j).scale_rounding_variance;
1803 l_formula_update_detail_rec.rounding_direction := p_formula_obj.formula_details(j).rounding_direction;
1804 l_formula_update_detail_rec.by_product_type := p_formula_obj.formula_details(j).by_product_type;
1805 l_formula_update_detail_rec.ingredient_end_date := p_formula_obj.formula_details(j).ingredient_end_date;
1806 l_formula_update_detail_rec.text_code_dtl := p_formula_obj.formula_details(j).text_code_dtl;
1807 l_formula_update_detail_rec.attribute_category := p_formula_obj.formula_details(j).dtl_attribute_category;
1808 l_formula_update_detail_rec.attribute1 := p_formula_obj.formula_details(j).dtl_attribute1;
1809 l_formula_update_detail_rec.attribute2 := p_formula_obj.formula_details(j).dtl_attribute2;
1810 l_formula_update_detail_rec.attribute3 := p_formula_obj.formula_details(j).dtl_attribute3;
1811 l_formula_update_detail_rec.attribute4 := p_formula_obj.formula_details(j).dtl_attribute4;
1812 l_formula_update_detail_rec.attribute5 := p_formula_obj.formula_details(j).dtl_attribute5;
1813 l_formula_update_detail_rec.attribute6 := p_formula_obj.formula_details(j).dtl_attribute6;
1814 l_formula_update_detail_rec.attribute7 := p_formula_obj.formula_details(j).dtl_attribute7;
1815 l_formula_update_detail_rec.attribute8 := p_formula_obj.formula_details(j).dtl_attribute8;
1816 l_formula_update_detail_rec.attribute9 := p_formula_obj.formula_details(j).dtl_attribute9;
1817 l_formula_update_detail_rec.attribute10 := p_formula_obj.formula_details(j).dtl_attribute10;
1818 l_formula_update_detail_rec.attribute11 := p_formula_obj.formula_details(j).dtl_attribute11;
1819 l_formula_update_detail_rec.attribute12 := p_formula_obj.formula_details(j).dtl_attribute12;
1820 l_formula_update_detail_rec.attribute13 := p_formula_obj.formula_details(j).dtl_attribute13;
1821 l_formula_update_detail_rec.attribute14 := p_formula_obj.formula_details(j).dtl_attribute14;
1822 l_formula_update_detail_rec.attribute15 := p_formula_obj.formula_details(j).dtl_attribute15;
1823 l_formula_update_detail_rec.attribute16 := p_formula_obj.formula_details(j).dtl_attribute16;
1824 l_formula_update_detail_rec.attribute17 := p_formula_obj.formula_details(j).dtl_attribute17;
1825 l_formula_update_detail_rec.attribute18 := p_formula_obj.formula_details(j).dtl_attribute18;
1826 l_formula_update_detail_rec.attribute19 := p_formula_obj.formula_details(j).dtl_attribute19;
1827 l_formula_update_detail_rec.attribute20 := p_formula_obj.formula_details(j).dtl_attribute20;
1828 l_formula_update_detail_rec.attribute21 := p_formula_obj.formula_details(j).dtl_attribute21;
1829 l_formula_update_detail_rec.attribute22 := p_formula_obj.formula_details(j).dtl_attribute22;
1830 l_formula_update_detail_rec.attribute23 := p_formula_obj.formula_details(j).dtl_attribute23;
1831 l_formula_update_detail_rec.attribute24 := p_formula_obj.formula_details(j).dtl_attribute24;
1832 l_formula_update_detail_rec.attribute25 := p_formula_obj.formula_details(j).dtl_attribute25;
1833 l_formula_update_detail_rec.attribute26 := p_formula_obj.formula_details(j).dtl_attribute26;
1834 l_formula_update_detail_rec.attribute27 := p_formula_obj.formula_details(j).dtl_attribute27;
1835 l_formula_update_detail_rec.attribute28 := p_formula_obj.formula_details(j).dtl_attribute28;
1836 l_formula_update_detail_rec.attribute29 := p_formula_obj.formula_details(j).dtl_attribute29;
1837 l_formula_update_detail_rec.attribute30 := p_formula_obj.formula_details(j).dtl_attribute30;
1838 l_formula_update_detail_rec.created_by := FND_GLOBAL.USER_ID;
1839 l_formula_update_detail_rec.creation_date := NVL(p_formula_obj.formula_details(j).creation_date, SYSDATE);
1840 l_formula_update_detail_rec.last_update_date := NVL(p_formula_obj.formula_details(j).last_update_date, SYSDATE);
1841 l_formula_update_detail_rec.last_update_login := NVL(p_formula_obj.formula_details(j).last_update_login, FND_GLOBAL.USER_ID);
1842 l_formula_update_detail_rec.last_updated_by := FND_GLOBAL.USER_ID;
1843 l_formula_update_detail_tbl(l_update_detailctr) := l_formula_update_detail_rec;
1844 l_update_detailctr := l_update_detailctr + 1;
1845 END IF;
1846 END IF;
1847 END LOOP;
1848 IF ( l_formula_insert_header_tbl.COUNT > 0) THEN
1849 GMD_FORMULA_PUB.Insert_Formula
1850 ( p_api_version => 1.0
1851 ,p_init_msg_list => FND_API.G_FALSE
1852 ,p_commit => FND_API.G_FALSE
1853 ,p_called_from_forms => 'NO'
1854 ,x_return_status => l_ret_status
1855 ,x_msg_count => l_msg_count
1856 ,x_msg_data => l_msg_data
1857 ,p_formula_header_tbl => l_formula_insert_header_tbl
1858 ,p_allow_zero_ing_qty => l_zero_ing_qty
1859 );
1860 IF (l_ret_status NOT IN(FND_API.G_RET_STS_SUCCESS,'Q')) THEN
1861 x_msg_data := l_msg_data;
1862 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1863 END If;
1864
1865 END IF;
1866
1867 IF ( l_formula_update_header_tbl.COUNT > 0) THEN
1868 GMD_FORMULA_PUB.Update_FormulaHeader
1869 ( p_api_version => 2.0
1870 ,p_init_msg_list => FND_API.G_FALSE
1871 ,p_commit => FND_API.G_FALSE
1872 ,p_called_from_forms => 'NO'
1873 ,x_return_status => l_ret_status
1874 ,x_msg_count => l_msg_count
1875 ,x_msg_data => l_msg_data
1876 ,p_formula_header_tbl => l_formula_update_header_tbl
1877 );
1878
1879 IF (l_ret_status NOT IN(FND_API.G_RET_STS_SUCCESS,'Q')) THEN
1880 x_msg_data := l_msg_data;
1881 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1882 END If;
1883 END IF;
1884
1885
1886 IF ( l_formula_update_detail_tbl.COUNT > 0) THEN
1887 Delete_FormulaDetail(
1888 p_formula_detail_tbl => l_formula_update_detail_tbl
1889 ,x_line_delete_tbl => l_line_delete_tbl
1890 ,x_return_status => l_ret_status
1891 ,x_msg_count => l_msg_count
1892 ,x_msg_data => l_msg_data
1893 );
1894 IF (l_ret_status NOT IN(FND_API.G_RET_STS_SUCCESS,'Q')) THEN
1895 x_msg_data := l_msg_data;
1896 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1897 END If;
1898 x_line_delete_tbl := l_line_delete_tbl;
1899 GMD_FORMULA_DETAIL_PUB.Update_FormulaDetail
1900 ( p_api_version => 2.0
1901 ,p_init_msg_list => FND_API.G_FALSE
1902 ,p_commit => FND_API.G_FALSE
1903 ,p_called_from_forms => 'NO'
1904 ,x_return_status => l_ret_status
1905 ,x_msg_count => l_msg_count
1906 ,x_msg_data => l_msg_data
1907 ,p_formula_detail_tbl => l_formula_update_detail_tbl
1908 );
1909 IF (l_ret_status NOT IN(FND_API.G_RET_STS_SUCCESS,'Q')) THEN
1910 x_msg_data := l_msg_data;
1911 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1912 END If;
1913
1914 END IF;
1915 IF ( l_formula_insert_detail_tbl.COUNT > 0) THEN
1916
1917 GMD_FORMULA_DETAIL_PUB.Insert_FormulaDetail
1918 ( p_api_version => 1.0
1919 ,p_init_msg_list => FND_API.G_FALSE
1920 ,p_commit => FND_API.G_FALSE
1921 ,p_called_from_forms => 'NO'
1922 ,x_return_status => l_ret_status
1923 ,x_msg_count => l_msg_count
1924 ,x_msg_data => l_msg_data
1925 ,p_formula_detail_tbl =>l_formula_insert_detail_tbl
1926 );
1927 SELECT Count(*) INTO l_msg_count FROM fm_matl_dtl WHERE formula_id=470;
1928 IF (l_ret_status NOT IN(FND_API.G_RET_STS_SUCCESS,'Q')) THEN
1929 x_msg_data := l_msg_data;
1930 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1931 END If;
1932 END IF;
1933
1934 EXCEPTION
1935 WHEN FND_API.g_exc_error THEN
1936 x_return_status := FND_API.G_RET_STS_ERROR;
1937 fnd_msg_pub.count_and_get(
1938 p_encoded => fnd_api.g_false
1939 ,p_count => x_msg_count
1940 ,p_data => x_msg_data
1941 );
1942 WHEN OTHERS THEN
1943 x_return_status := FND_API.G_RET_STS_ERROR;
1944 IF x_msg_data IS NULL THEN
1945 x_msg_data := SQLERRM;
1946 END IF;
1947 END;
1948
1949
1950 /* ======================================================================== */
1951
1952 /* PROCEDURE: */
1953
1954 /* sync_recipe */
1955
1956 /* */
1957
1958 /* DESCRIPTION: */
1959
1960 /* This function controls the entire sync flow and does a sync of formula */
1961
1962 /* ,creates attachments and populates the required output format */
1963
1964 /* ======================================================================== */
1965
1966
1967 PROCEDURE sync_recipe(
1968 p_api_version IN VARCHAR2
1969 ,p_commit IN VARCHAR2
1970 ,p_recipe_tbl IN gmd_ebi_recipe_tbl
1971 ,x_recipe_tbl OUT NOCOPY gmd_ebi_recipe_output_tbl
1972 ,x_return_status OUT NOCOPY VARCHAR2
1973 ,x_msg_count OUT NOCOPY NUMBER
1974 ,x_msg_data OUT NOCOPY VARCHAR2
1975 )
1976 IS
1977 l_ret_status VARCHAR2(10);
1978 l_status VARCHAR2(10);
1979 l_ret_msg VARCHAR2(32000);
1980 l_error VARCHAR2(32000);
1981 l_msg_count NUMBER;
1982 l_formula_obj gmd_ebi_formula_obj;
1983 l_formula_obj_updated gmd_ebi_formula_obj;
1984 l_output gmd_ebi_formula_output_obj;
1985 l_formula_obj_inp gmd_ebi_formula_obj;
1986 l_recipe_out_tbl gmd_ebi_recipe_output_tbl := gmd_ebi_recipe_output_tbl();
1987 l_recipe_out_obj gmd_ebi_recipe_output_obj;
1988 l_recipe_out_Ctr NUMBER := 1;
1989 l_create_attachment VARCHAR2(1);
1990 l_line_delete_tbl gmd_ebi_formula_line_del_tbl;
1991
1992 BEGIN
1993 x_return_status := FND_API.G_RET_STS_SUCCESS;
1994 FND_MSG_PUB.initialize();
1995 IF (p_recipe_tbl IS NOT NULL AND p_recipe_tbl.count>0) THEN
1996 FOR i IN 1..p_recipe_tbl.COUNT LOOP
1997 l_formula_obj_inp := p_recipe_tbl(i).formula_obj;
1998 l_create_attachment := get_config_value(l_formula_obj_inp.p_config_tbl,'CREATE_ATTACHMENT');
1999 update_org_ids(
2000 p_formula_obj => l_formula_obj_inp
2001 ,x_formula_obj => l_formula_obj
2002 ,x_msg_count => l_msg_count
2003 ,x_return_status => l_ret_status
2004 ,x_msg_data => l_ret_msg
2005 );
2006 IF ( l_ret_status = FND_API.G_RET_STS_SUCCESS) THEN
2007 update_item_ids(
2008 p_formula_obj => l_formula_obj
2009 ,x_formula_obj => l_formula_obj_updated
2010 ,x_return_status => l_ret_status
2011 ,x_msg_data => l_ret_msg
2012 );
2013 ELSE
2014 RAISE FND_API.g_exc_error;
2015 END IF;
2016 IF ( l_ret_status = FND_API.G_RET_STS_SUCCESS) THEN
2017 assign_items_to_org(
2018 p_formula_obj => l_formula_obj_updated
2019 ,x_return_status => l_ret_status
2020 ,x_msg_data => l_ret_msg
2021 );
2022 ELSE
2023 RAISE FND_API.g_exc_error;
2024 END IF;
2025 IF ( l_ret_status = FND_API.G_RET_STS_SUCCESS ) THEN
2026 IF l_formula_obj_updated.formula_details.Count < 2 THEN
2027 FND_MESSAGE.SET_NAME('GMD', 'FM_SAVE_FORMULA_ERR');
2028 FND_MESSAGE.SET_TOKEN('FORMULA_NO', l_formula_obj_updated.formula_header.formula_no);
2029 FND_MESSAGE.SET_TOKEN('FORMULA_VERS', l_formula_obj_updated.formula_header.formula_vers);
2030 FND_MSG_PUB.Add;
2031 RAISE FND_API.G_EXC_ERROR;
2032 END IF;
2033 sync_formula(
2034 p_formula_obj => l_formula_obj_updated
2035 ,x_line_delete_tbl => l_line_delete_tbl
2036 ,x_return_status => l_ret_status
2037 ,x_msg_count => l_msg_count
2038 ,x_msg_data => l_ret_msg
2039 );
2040 ELSE
2041 RAISE FND_API.g_exc_error;
2042 END IF;
2043 IF (l_ret_status IN(FND_API.G_RET_STS_SUCCESS,'Q') ) THEN
2044 IF(p_recipe_tbl(i).routing_obj IS NOT NULL AND ( p_recipe_tbl(i).routing_obj.gmd_ebi_routing_steps.Count > 0 )) THEN
2045 IF (l_create_attachment = 'Y') THEN
2046 add_attachment(
2047 p_recipe_obj => p_recipe_tbl(i)
2048 ,x_return_status => l_ret_status
2049 ,x_msg_count => l_msg_count
2050 ,x_msg_data => l_ret_msg
2051 );
2052 ELSE
2053 sync_routings(
2054 p_routing_obj => p_recipe_tbl(i).routing_obj
2055 ,p_commit => FND_API.G_FALSE
2056 ,x_return_status => l_ret_status
2057 ,x_msg_count => l_msg_count
2058 ,x_msg_data => l_ret_msg
2059 );
2060 END IF;
2061 END IF;
2062 END IF;
2063 l_status := l_ret_status;
2064 l_error := l_ret_msg;
2065 populate_output(
2066 p_input => l_formula_obj_updated
2067 ,p_line_delete_tbl => l_line_delete_tbl
2068 ,status_flag => l_status
2069 ,x_output => l_output
2070 ,x_return_status => l_ret_status
2071 ,x_msg_count => l_msg_count
2072 ,x_msg_data => l_ret_msg
2073 );
2074 IF (l_ret_status = FND_API.G_RET_STS_SUCCESS AND (l_status IN(FND_API.G_RET_STS_SUCCESS,'Q'))) THEN
2075 l_recipe_out_obj := gmd_ebi_recipe_output_obj(NULL,NULL,FND_API.G_RET_STS_SUCCESS,NULL,l_output,NULL);
2076 l_recipe_out_tbl.extend(1);
2077 l_recipe_out_tbl(l_recipe_out_Ctr):= l_recipe_out_obj;
2078 l_recipe_out_Ctr := l_recipe_out_Ctr + 1;
2079 ELSIF (l_ret_status = FND_API.G_RET_STS_SUCCESS AND (l_status NOT IN(FND_API.G_RET_STS_SUCCESS,'Q'))) THEN
2080 l_recipe_out_obj := gmd_ebi_recipe_output_obj(NULL,NULL,FND_API.G_RET_STS_ERROR,l_error,l_output,NULL);
2081 l_recipe_out_tbl.extend(1);
2082 l_recipe_out_tbl(l_recipe_out_Ctr):= l_recipe_out_obj;
2083 l_recipe_out_Ctr := l_recipe_out_Ctr + 1;
2084 ELSE
2085 l_recipe_out_obj := gmd_ebi_recipe_output_obj(NULL,NULL,FND_API.G_RET_STS_ERROR,l_ret_msg,l_output,NULL);
2086 l_recipe_out_tbl.extend(1);
2087 l_recipe_out_tbl(l_recipe_out_Ctr):= l_recipe_out_obj;
2088 l_recipe_out_Ctr := l_recipe_out_Ctr + 1;
2089 END IF;
2090 IF l_status NOT IN (FND_API.G_RET_STS_SUCCESS,'Q') THEN
2091 x_return_status := FND_API.G_RET_STS_ERROR;
2092 x_msg_data := x_msg_data || l_recipe_out_obj.error_msg || fnd_global.newline;
2093 END IF;
2094 IF p_commit = FND_API.G_TRUE THEN
2095 COMMIT;
2096 END IF;
2097 END LOOP;
2098 ELSE
2099 x_msg_data := FND_MESSAGE.GET_STRING('GMD','GMD_EBI_NO_RECORD');
2100 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2101 END IF;
2102 x_recipe_tbl := l_recipe_out_tbl;
2103 EXCEPTION
2104 WHEN FND_API.G_EXC_ERROR THEN
2105 IF l_ret_msg IS NULL THEN
2106 x_return_status := FND_API.G_RET_STS_ERROR;
2107 FND_MSG_PUB.count_and_get(
2108 p_encoded => FND_API.g_false
2109 ,p_count => x_msg_count
2110 ,p_data => x_msg_data
2111 );
2112 ELSE
2113 x_return_status := FND_API.G_RET_STS_ERROR;
2114 x_msg_data := l_ret_msg;
2115 END IF;
2116 WHEN OTHERS THEN
2117 IF x_msg_data IS NULL THEN
2118 x_return_status := FND_API.G_RET_STS_ERROR;
2119 x_msg_data := SQLERRM || 'AT SYNC RECIPE API';
2120 END IF;
2121 END;
2122
2123 END GMD_EBI_RECIPE_PUB;