[Home] [Help]
PACKAGE BODY: APPS.GMD_SPREAD_FETCH_PKG
Source
1 PACKAGE BODY GMD_SPREAD_FETCH_PKG AS
2 /* $Header: GMDSPDFB.pls 120.21.12020000.3 2013/03/07 02:51:10 mtou ship $ */
3
4 /*##############################################################
5 # NAME
6 # load_details
7 # SYNOPSIS
8 # proc load_details
9 # DESCRIPTION
10 # This procedure loads the material and lot lines based on
11 # the id passed.
12 # HISTORY
13 # 15-SEP-06 Kapil M Bug# 5513268
14 # Added the parameter spec_id.
15 ###############################################################*/
16
17 PROCEDURE load_details (V_entity_id IN NUMBER,V_sprd_id IN NUMBER,
18 V_batch_id IN NUMBER,V_formula_id IN NUMBER,V_spec_id IN NUMBER DEFAULT NULL,
19 V_orgn_id IN NUMBER,V_update_inv_ind IN VARCHAR2,
20 V_plant_id IN NUMBER) IS
21 BEGIN
22 DELETE FROM gmd_material_details_gtmp;
23 DELETE FROM gmd_technical_data_gtmp;
24 IF (V_sprd_id IS NOT NULL) THEN
25 gmd_spread_fetch_pkg.load_spread_details(V_entity_id,V_sprd_id,V_orgn_id);
26 ELSIF (V_batch_id IS NOT NULL AND V_spec_id IS NULL) THEN
27 gmd_spread_fetch_pkg.load_batch_details(V_entity_id,V_batch_id,V_orgn_id,V_update_inv_ind,V_plant_id);
28 ELSIF (V_formula_id IS NOT NULL AND V_spec_id IS NULL) THEN
29 gmd_spread_fetch_pkg.load_formula_details(V_entity_id,V_formula_id,V_orgn_id,V_plant_id);
30 ELSE
31 gmd_spread_fetch_pkg.load_lcf_details(V_entity_id,V_orgn_id,V_plant_id);
32 END IF;
33 END load_details;
34
35 /*##############################################################
36 # NAME
37 # load_spread_details
38 # SYNOPSIS
39 # proc load_spread_details
40 # DESCRIPTION
41 # This procedure inserts the data into temp tables and will
42 # be fetched in the form.
43 ###############################################################*/
44
45 PROCEDURE load_spread_details (V_entity_id IN NUMBER, V_sprd_id IN NUMBER,V_orgn_id IN NUMBER) IS
46 CURSOR Cur_get_spread IS
47 SELECT a.*,b.description,b.lot_control_code,b.secondary_default_ind,
48 b.grade_control_flag,b.location_control_code,b.tracking_quantity_ind,c.expiration_date expiry_date,
49 b.primary_uom_code PRIMARY, c.lot_number lot, d.batchstep_no
50 FROM lm_sprd_dtl a, mtl_system_items_b b, mtl_lot_numbers c,
51 gme_batch_steps d, gme_batch_step_items e
52 WHERE a.inventory_item_id = b.inventory_item_id
53 AND a.organization_id = b.organization_id
54 AND a.inventory_item_id = c.inventory_item_id (+)
55 AND a.organization_id = c.organization_id (+)
56 AND a.lot_number = c.lot_number (+)
57 AND a.sprd_id = V_sprd_id
58 AND a.material_detail_id = e.material_detail_id (+)
59 AND d.batchstep_id(+) = e.batchstep_id
60 AND (a.line_type <> 1 OR a.line_no = 1)
61 ORDER BY a.line_type,a.line_no;
62
63 CURSOR Cur_get_lines IS
64 SELECT parent_line_id
65 FROM gmd_material_details_gtmp
66 WHERE entity_id = V_entity_id
67 AND line_type <> 3 ;
68
69 CURSOR Cur_get_batch_text(V_matldetlid IN NUMBER) IS
70 SELECT text_code
71 FROM gme_material_details
72 WHERE material_detail_id = V_matldetlid;
73
74 CURSOR Cur_get_formula_text(V_formlineid IN NUMBER) IS
75 SELECT text_code
76 FROM fm_matl_dtl
77 WHERE formulaline_id = V_formlineid;
78
79 l_text_code NUMBER(10);
80 l_line_id NUMBER DEFAULT 0;
81 l_parent_line_id NUMBER;
82 l_secondary_qty NUMBER;
83 l_spread_rec Cur_get_spread%ROWTYPE;
84 BEGIN
85 /* Inserting the item and lot data from spread tables to temp tables*/
86 IF (V_sprd_id IS NOT NULL) THEN
87 OPEN Cur_get_spread;
88 LOOP
89 l_line_id := l_line_id + 1;
90 FETCH Cur_get_spread INTO l_spread_rec;
91 EXIT WHEN Cur_get_spread%NOTFOUND;
92 IF (l_spread_rec.material_detail_id IS NOT NULL) THEN
93 OPEN Cur_get_batch_text(l_spread_rec.material_detail_id);
94 FETCH Cur_get_batch_text INTO l_text_code;
95 CLOSE Cur_get_batch_text;
96 l_parent_line_id := l_spread_rec.material_detail_id;
97 ELSE
98 OPEN Cur_get_formula_text(l_spread_rec.formulaline_id);
99 FETCH Cur_get_formula_text INTO l_text_code;
100 CLOSE Cur_get_formula_text;
101 l_parent_line_id := l_spread_rec.formulaline_id;
102 END IF;
103 --Bug12346394 is fixed by adding the code to insert the detial uom instead of items primary uom (l_spread_rec.detail_uom).
104 INSERT INTO GMD_MATERIAL_DETAILS_GTMP
105 (ENTITY_ID,LINE_ID,LINE_TYPE,LINE_NO,ROLLUP_IND,TRACKING_QUANTITY_IND,LOCATION_CONTROL_CODE,
106 INVENTORY_ITEM_ID,DESCRIPTION,QTY,SECONDARY_QTY,DETAIL_UOM,ORGANIZATION_ID,
107 GRADE_CODE,PRIMARY_UOM,SECONDARY_UOM,LOT_CONTROL_CODE,REVISION,
108 GRADE_CONTROL_FLAG,LOT_NUMBER,TEXT_CODE,ORGINAL_TEXT_CODE,SPRD_LINE_ID,ACTION_CODE,
109 FORMULALINE_ID,EXPAND_IND,EXPIRATION_DATE,MATERIAL_DETAIL_ID,PARENT_LINE_ID,TPFORMULA_ID,
110 SUBINVENTORY_CODE,LOCATION,TRANSACTION_ID,RESERVATION_ID,BATCHSTEP_NO,BUFFER_IND, PLANT_ORGANIZATION_ID,
111 CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,REVISION_QTY_CONTROL_CODE,
112 MOVE_ORDER_LINE_ID,SECONDARY_DEFAULT_IND,LOCATOR_ID,PROD_PERCENT)
113 VALUES
114 (l_spread_rec.sprd_id,l_line_id,l_spread_rec.line_type,l_spread_rec.line_no,
115 l_spread_rec.rollup_ind,l_spread_rec.tracking_quantity_ind,l_spread_rec.location_control_code,
116 l_spread_rec.inventory_item_id,l_spread_rec.description,
117 l_spread_rec.qty,l_spread_rec.secondary_qty,l_spread_rec.detail_uom,l_spread_rec.organization_id,l_spread_rec.grade_code,
118 l_spread_rec.primary,l_spread_rec.secondary_uom,l_spread_rec.lot_control_code,
119 l_spread_rec.revision,l_spread_rec.grade_control_flag,l_spread_rec.lot_number,
120 l_spread_rec.text_code,l_text_code,l_spread_rec.line_id,'NONE',l_spread_rec.formulaline_id,
121 DECODE(l_spread_rec.lot_number,NULL,1,0),l_spread_rec.expiry_date,l_spread_rec.material_detail_id,
122 l_parent_line_id,l_spread_rec.tpformula_id,l_spread_rec.subinventory_code,l_spread_rec.location,
123 l_spread_rec.transaction_id,l_spread_rec.reservation_id,l_spread_rec.batchstep_no,l_spread_rec.buffer_ind,l_spread_rec.plant_organization_id,
124 l_spread_rec.created_by,l_spread_rec.creation_date,l_spread_rec.last_updated_by,
125 l_spread_rec.last_update_date,l_spread_rec.revision_qty_control_code,
126 l_spread_rec.move_order_line_id,l_spread_rec.secondary_default_ind,l_spread_rec.locator_id,l_spread_rec.prod_percent);
127 END LOOP;
128 CLOSE Cur_get_spread;
129 FOR l_sprd_rec IN Cur_get_lines LOOP
130 gmd_spread_fetch_pkg.load_spread_values(V_entity_id,V_sprd_id,V_orgn_id,l_sprd_rec.parent_line_id);
131 END LOOP;
132 END IF;
133 EXCEPTION
134 WHEN OTHERS THEN
135 fnd_msg_pub.add_exc_msg ('GMD_SPREAD_FETCH_PKG', 'Load_Spread_Details');
136 END load_spread_details;
137
138 /*##############################################################
139 # NAME
140 # load_batch_details
141 # SYNOPSIS
142 # proc load_batch_details
143 # DESCRIPTION
144 # This procedure inserts the data into temp tables and will
145 # be fetched in the form.
146 ###############################################################*/
147
148 PROCEDURE load_batch_details (V_entity_id IN NUMBER, V_batch_id IN NUMBER,
149 V_orgn_id IN NUMBER,V_update_inv_ind IN VARCHAR2,
150 V_plant_id IN NUMBER) IS
151 CURSOR Cur_get_batch IS
152 SELECT a.*,b.description,b.lot_control_code,
153 b.grade_control_flag,b.tracking_quantity_ind,b.location_control_code,
154 b.default_grade,b.primary_uom_code primary,b.secondary_uom_code secondary,
155 c.batchstep_no,e.batch_status, b.revision_qty_control_code,b.secondary_default_ind
156 FROM gme_material_details a, mtl_system_items_b b, gme_batch_steps c,
157 gme_batch_step_items d, gme_batch_header e
158 WHERE a.inventory_item_id = b.inventory_item_id
159 AND b.organization_id = a.organization_id
160 AND a.batch_id = V_entity_id
161 AND e.batch_id = V_batch_id
162 AND a.material_detail_id = d.material_detail_id (+)
163 AND c.batchstep_id(+) = d.batchstep_id
164 AND (a.line_type <> 1 OR a.line_no = 1)
165 ORDER BY a.line_type, a.line_no;
166
167 CURSOR Cur_get_lab_lots (V_matl_detl_id NUMBER) IS
168 SELECT a.*, c.expiration_date, b.inventory_item_id,
169 b.detail_uom, b.primary_uom, b.secondary_uom,
170 b.tracking_quantity_ind,b.lot_control_code,b.secondary_default_ind,
171 b.grade_control_flag,b.location_control_code, b.organization_id,b.locator_id
172 FROM gme_pending_product_lots a, gmd_material_details_gtmp b,
173 mtl_lot_numbers c
174 WHERE a.material_detail_id = b.material_detail_id
175 AND a.material_detail_id = V_matl_detl_id
176 AND a.batch_id = V_batch_id
177 AND b.organization_id = c.organization_id
178 AND b.inventory_item_id = c.inventory_item_id
179 AND a.lot_number = c.lot_number
180 AND b.lot_control_code = 2
181 AND b.line_type NOT IN (1,3);
182
183 CURSOR Cur_get_controls (V_inventory_item_id NUMBER,V_lot_number VARCHAR2,V_organization_id NUMBER) IS
184 SELECT b.tracking_quantity_ind,b.lot_control_code,
185 b.grade_control_flag,b.location_control_code,
186 b.default_grade,b.secondary_default_ind,c.expiration_date,c.organization_id
187 FROM mtl_system_items b,mtl_lot_numbers c
188 WHERE b.inventory_item_id = c.inventory_item_id
189 AND b.organization_id = c.organization_id
190 AND b.inventory_item_id = V_inventory_item_id
191 AND b.organization_id = V_organization_id
192 AND c.lot_number = V_lot_number;
193
194 CURSOR Cur_get_lines IS
195 SELECT material_detail_id
196 FROM gmd_material_details_gtmp
197 WHERE entity_id = V_entity_id
198 AND line_type <> 3 ;
199
200 l_line_id NUMBER DEFAULT 0;
201 l_status VARCHAR2(100);
202 l_material_detail_id NUMBER DEFAULT 0;
203 l_line_no NUMBER;
204 l_qty NUMBER;
205 l_secondary_qty NUMBER;
206 l_primary_qty NUMBER;
207 l_rsv_qty NUMBER;
208 l_mat_count NUMBER;
209 l_rsc_count NUMBER;
210 l_user_id NUMBER;
211
212 l_batch_row GME_BATCH_HEADER%ROWTYPE;
213 l_batch_rec Cur_get_batch%ROWTYPE;
214 l_control Cur_get_controls%ROWTYPE;
215 l_labrec Cur_get_lab_lots%ROWTYPE;
216
217 CURSOR Cur_get_formulaid (V_validity_rule_id NUMBER) IS
218 SELECT formula_id
219 FROM gmd_recipes r, gmd_recipe_validity_rules v
220 WHERE recipe_validity_rule_id = V_validity_rule_id
221 AND r.recipe_id = v.recipe_id;
222
223 l_return_status VARCHAR2(1);
224 x_return_status VARCHAR2(1);
225 l_msg_data VARCHAR2(2000);
226 l_msg_count NUMBER(10);
227 l_return_code NUMBER(10);
228 l_rec_count NUMBER(10);
229 l_tpformula_id NUMBER(10);
230
231 l_recipe_validity_out GMD_VALIDITY_RULES.recipe_validity_tbl;
232 l_reservations_tbl gme_common_pvt.reservations_tab;
233 l_mmt_tbl gme_common_pvt.mtl_mat_tran_tbl;
234 l_mmln_tbl gme_common_pvt.mtl_trans_lots_num_tbl;
235 BEGIN
236 gmd_debug.log_initialize ('simul');
237 /* Inserting the item and lot data from batch material tables to temp tables*/
238 l_user_id := TO_NUMBER (fnd_profile.VALUE ('USER_ID'));
239 IF (V_batch_id IS NOT NULL) THEN
240 OPEN Cur_get_batch;
241 LOOP
242 l_line_id := l_line_id + 1;
243 FETCH Cur_get_batch INTO l_batch_rec;
244 EXIT WHEN Cur_get_batch%NOTFOUND;
245 IF (l_batch_rec.batch_status = 2) THEN
246 l_qty := l_batch_rec.wip_plan_qty;
247 ELSE
248 l_qty := l_batch_rec.plan_qty;
249 END IF;
250 l_secondary_qty := null;
251 IF (l_qty > 0 AND l_batch_rec.tracking_quantity_ind ='PS') THEN
252 l_secondary_qty := gmd_labuom_calculate_pkg.uom_conversion (pitem_id => l_batch_rec.inventory_item_id,
253 pformula_id => NULL,
254 plot_number => NULL,
255 pcur_qty => l_qty,
256 pcur_uom => l_batch_rec.dtl_um,
257 pnew_uom => l_batch_rec.secondary,
258 patomic => 0,
259 plab_id => l_batch_rec.organization_id);
260 IF (l_secondary_qty < 0) THEN
261 l_secondary_qty := 0;
262 END IF;
263 END IF;
264 GMD_VAL_DATA_PUB.get_val_data (p_api_version => 1.0
265 ,p_init_msg_list => 'T'
266 ,p_object_type => 'L'
267 ,p_item_id => l_batch_rec.inventory_item_id
268 ,p_product_qty => l_qty
269 ,p_uom => l_batch_rec.dtl_um
270 ,p_organization_id => V_orgn_id
271 ,x_return_status => l_return_status
272 ,x_msg_count => l_msg_count
273 ,x_msg_data => l_msg_data
274 ,x_return_code => l_return_code
275 ,x_recipe_validity_out => l_recipe_validity_out);
276 IF l_return_status = 'S' THEN
277 l_rec_count := l_recipe_validity_out.COUNT;
278 IF l_rec_count > 0 THEN
279 OPEN Cur_get_formulaid (l_recipe_validity_out (l_rec_count).recipe_validity_rule_id);
280 FETCH Cur_get_formulaid INTO l_tpformula_id;
281 CLOSE Cur_get_formulaid;
282 END IF;
283 END IF;
284 INSERT INTO GMD_MATERIAL_DETAILS_GTMP
285 (ENTITY_ID,LINE_ID,LINE_TYPE,LINE_NO,ROLLUP_IND,TRACKING_QUANTITY_IND,LOCATION_CONTROL_CODE,
286 INVENTORY_ITEM_ID,DESCRIPTION,EXPAND_IND,QTY,SECONDARY_QTY,DETAIL_UOM,
287 GRADE_CODE,PRIMARY_UOM,SECONDARY_UOM,LOT_CONTROL_CODE,ORGANIZATION_ID,
288 GRADE_CONTROL_FLAG,REVISION,TEXT_CODE,ORGINAL_TEXT_CODE,MATERIAL_DETAIL_ID,FORMULALINE_ID,
289 PARENT_LINE_ID,ACTION_CODE,TPFORMULA_ID,BATCHSTEP_NO,MOVE_ORDER_LINE_ID,
290 CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,REVISION_QTY_CONTROL_CODE,
291 SECONDARY_DEFAULT_IND)
292 VALUES
293 (l_batch_rec.batch_id,l_line_id,l_batch_rec.line_type,l_batch_rec.line_no,1,
294 l_batch_rec.tracking_quantity_ind,l_batch_rec.location_control_code,
295 l_batch_rec.inventory_item_id,l_batch_rec.description,
296 1,l_qty,l_secondary_qty,l_batch_rec.dtl_um,l_batch_rec.default_grade,l_batch_rec.primary,l_batch_rec.secondary,
297 l_batch_rec.lot_control_code,l_batch_rec.organization_id,l_batch_rec.grade_control_flag,
298 l_batch_rec.revision,l_batch_rec.text_code,l_batch_rec.text_code,l_batch_rec.material_detail_id,
299 l_batch_rec.formulaline_id,l_batch_rec.material_detail_id,'NONE',l_tpformula_id,
300 l_batch_rec.batchstep_no,l_batch_rec.move_order_line_id,l_batch_rec.created_by,l_batch_rec.creation_date,
301 l_batch_rec.last_updated_by,l_batch_rec.last_update_date,
302 l_batch_rec.revision_qty_control_code, l_batch_rec.secondary_default_ind);
303
304 /*Load the lot transactions into temp table*/
305 IF (l_batch_rec.lot_control_code = 2) THEN
306 gme_transactions_pvt.get_mat_trans (p_mat_det_id => l_batch_rec.material_detail_id
307 ,p_batch_id => V_batch_id
308 ,x_mmt_tbl => l_mmt_tbl
309 ,x_return_status => l_status);
310 IF (l_status = FND_API.G_RET_STS_SUCCESS) THEN
311 FOR i in 1..l_mmt_tbl.COUNT LOOP
312 gme_transactions_pvt.get_lot_trans (p_transaction_id => l_mmt_tbl(i).transaction_id
313 ,x_mmln_tbl => l_mmln_tbl
314 ,x_return_status => x_return_status);
315 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
316 FOR j IN 1..l_mmln_tbl.COUNT LOOP
317 IF (l_mmln_tbl(j).lot_number IS NOT NULL) THEN
318 l_line_id := l_line_id + 1;
319 IF l_material_detail_id <> l_mmt_tbl(i).transaction_source_id THEN
320 l_line_no := 1;
321 l_material_detail_id := l_mmt_tbl(i).transaction_source_id;
322 END IF;
323 OPEN Cur_get_controls(l_mmln_tbl(j).inventory_item_id,
324 l_mmln_tbl(j).lot_number,
325 l_batch_rec.organization_id);
326 FETCH Cur_get_controls INTO l_control;
327 CLOSE Cur_get_controls;
328 INSERT INTO GMD_MATERIAL_DETAILS_GTMP
329 (ENTITY_ID,LINE_ID,LINE_TYPE,LINE_NO,ROLLUP_IND,INVENTORY_ITEM_ID,EXPAND_IND,
330 EXPIRATION_DATE,LOT_NUMBER,QTY,PRIMARY_QTY,PRIMARY_UOM,SECONDARY_QTY,SECONDARY_UOM,
331 DETAIL_UOM,GRADE_CODE,TRACKING_QUANTITY_IND,LOCATION_CONTROL_CODE,LOT_CONTROL_CODE,
332 GRADE_CONTROL_FLAG,SUBINVENTORY_CODE,MATERIAL_DETAIL_ID,TRANSACTION_ID,
333 PARENT_LINE_ID,ACTION_CODE,CREATED_BY,CREATION_DATE,LOCATOR_ID,
334 LAST_UPDATED_BY,LAST_UPDATE_DATE,SECONDARY_DEFAULT_IND,ORGANIZATION_ID)
335 VALUES
336 (V_batch_id,l_line_id,3,l_line_no,0,l_mmln_tbl(j).inventory_item_id,0,
337 l_control.expiration_date,l_mmln_tbl(j).lot_number,
338 ABS(l_mmln_tbl(j).transaction_quantity),ABS(l_mmln_tbl(j).primary_quantity),
339 l_mmt_tbl(i).transaction_uom,ABS(l_mmln_tbl(j).secondary_transaction_quantity),
340 l_mmt_tbl(i).secondary_uom_code,l_batch_rec.dtl_um,l_mmln_tbl(j).grade_code,
341 l_control.tracking_quantity_ind,l_control.location_control_code,
342 l_control.lot_control_code,l_control.grade_control_flag,
343 l_mmt_tbl(i).subinventory_code,l_batch_rec.material_detail_id,
344 l_mmt_tbl(i).transaction_id,l_batch_rec.material_detail_id,
345 'NONE',l_user_id,sysdate,l_mmt_tbl(i).locator_id,
346 l_user_id,sysdate,l_control.secondary_default_ind,l_mmln_tbl(j).organization_id);
347 l_line_no := l_line_no + 1;
348 END IF;
349 END LOOP;
350 END IF;
351 END LOOP;
352 END IF;
353 END IF;
354 /* based on update_inventory_ind data will be loaded either from reservations table
355 or pending lots table*/
356
357 IF (V_update_inv_ind = 'Y') THEN
358 --Load Reservations
359 gme_reservations_pvt.get_material_reservations (p_organization_id => l_batch_rec.organization_id
360 ,p_batch_id => V_batch_id
361 ,p_material_detail_id => l_batch_rec.material_detail_id
362 ,x_return_status => l_status
363 ,x_reservations_tbl => l_reservations_tbl);
364 IF (l_status = FND_API.G_RET_STS_SUCCESS) THEN
365 FOR i IN 1..l_reservations_tbl.COUNT LOOP
366 IF (l_reservations_tbl(i).lot_number IS NOT NULL) THEN
367 l_line_id := l_line_id + 1;
368 IF l_material_detail_id <> l_reservations_tbl(i).demand_source_line_id THEN
369 l_line_no := 1;
370 l_material_detail_id := l_reservations_tbl(i).demand_source_line_id;
371 END IF;
372 OPEN Cur_get_controls(l_reservations_tbl(i).inventory_item_id,
373 l_reservations_tbl(i).lot_number,
374 l_batch_rec.organization_id);
375 FETCH Cur_get_controls INTO l_control;
376 CLOSE Cur_get_controls;
377
378 l_rsv_qty := NULL;
379 IF (l_reservations_tbl(i).reservation_uom_code = l_batch_rec.dtl_um) THEN
380 l_rsv_qty := l_reservations_tbl(i).reservation_quantity;
381 ELSIF (l_reservations_tbl(i).primary_uom_code = l_batch_rec.dtl_um) THEN
382 l_rsv_qty := l_reservations_tbl(i).primary_reservation_quantity;
383 ELSIF (l_reservations_tbl(i).secondary_uom_code = l_batch_rec.dtl_um) THEN
384 l_rsv_qty := l_reservations_tbl(i).secondary_reservation_quantity;
385 ELSE
386 l_rsv_qty := gmd_labuom_calculate_pkg.uom_conversion (pitem_id => l_labrec.inventory_item_id,
387 pformula_id => NULL,
388 plot_number => l_reservations_tbl(i).lot_number,
389 pcur_qty => l_reservations_tbl(i).primary_reservation_quantity,
390 pcur_uom => l_reservations_tbl(i).primary_uom_code,
391 pnew_uom => l_batch_rec.dtl_um,
392 patomic => 0,
393 plab_id => l_batch_rec.organization_id);
394
395 END IF;
396
397 INSERT INTO GMD_MATERIAL_DETAILS_GTMP
398 (ENTITY_ID,LINE_ID,LINE_TYPE,LINE_NO,ROLLUP_IND,INVENTORY_ITEM_ID,EXPAND_IND,
399 EXPIRATION_DATE,LOT_NUMBER,QTY,PRIMARY_QTY,PRIMARY_UOM,SECONDARY_QTY,SECONDARY_UOM,
400 DETAIL_UOM,GRADE_CODE,TRACKING_QUANTITY_IND,LOCATION_CONTROL_CODE,LOT_CONTROL_CODE,
401 GRADE_CONTROL_FLAG,SUBINVENTORY_CODE,MATERIAL_DETAIL_ID,RESERVATION_ID,
402 PARENT_LINE_ID,ACTION_CODE,CREATED_BY,CREATION_DATE,LOCATOR_ID,
403 LAST_UPDATED_BY,LAST_UPDATE_DATE,SECONDARY_DEFAULT_IND,ORGANIZATION_ID)
404 VALUES
405 (V_batch_id,l_line_id,3,l_line_no,0,l_reservations_tbl(i).inventory_item_id,0,
406 l_control.expiration_date,l_reservations_tbl(i).lot_number,
407 l_rsv_qty,l_reservations_tbl(i).primary_reservation_quantity,
408 l_reservations_tbl(i).primary_uom_code,l_reservations_tbl(i).secondary_reservation_quantity,
409 l_reservations_tbl(i).secondary_uom_code,l_batch_rec.dtl_um,l_control.default_grade,
410 l_control.tracking_quantity_ind,l_control.location_control_code,
411 l_control.lot_control_code,l_control.grade_control_flag,
412 l_reservations_tbl(i).subinventory_code,l_batch_rec.material_detail_id,
413 l_reservations_tbl(i).reservation_id,l_batch_rec.material_detail_id,
414 'NONE',l_user_id,sysdate,l_reservations_tbl(i).locator_id,l_user_id,sysdate,l_control.secondary_default_ind,l_control.organization_id);
415 l_line_no := l_line_no + 1;
416 END IF;
417 END LOOP;
418 END IF;
419 ELSE
420 OPEN Cur_get_lab_lots (l_batch_rec.material_detail_id);
421 LOOP
422 l_line_id := l_line_id + 1;
423 FETCH Cur_get_lab_lots INTO l_labrec;
424 EXIT WHEN Cur_get_lab_lots%NOTFOUND;
425 IF l_material_detail_id <> l_labrec.material_detail_id THEN
426 l_line_no := 1;
427 l_material_detail_id := l_labrec.material_detail_id;
428 END IF;
429
430 l_primary_qty := null;
431 IF (l_labrec.quantity > 0) THEN
432 l_primary_qty := gmd_labuom_calculate_pkg.uom_conversion (pitem_id => l_labrec.inventory_item_id,
433 pformula_id => NULL,
434 plot_number => l_labrec.lot_number,
435 pcur_qty => l_labrec.quantity,
436 pcur_uom => l_labrec.primary_uom,
437 pnew_uom => l_labrec.secondary_uom,
438 patomic => 0,
439 plab_id => l_batch_rec.organization_id);
440 IF (l_primary_qty < 0) THEN
441 l_primary_qty := NULL;
442 END IF;
443 END IF;
444 INSERT INTO GMD_MATERIAL_DETAILS_GTMP
445 (ENTITY_ID,LINE_ID,LINE_TYPE,LINE_NO,ROLLUP_IND,INVENTORY_ITEM_ID,EXPAND_IND,
446 EXPIRATION_DATE,SECONDARY_UOM,LOT_NUMBER,QTY,PRIMARY_QTY,PRIMARY_UOM,SECONDARY_QTY,
447 DETAIL_UOM,TRACKING_QUANTITY_IND,LOCATION_CONTROL_CODE,LOT_CONTROL_CODE,
448 GRADE_CONTROL_FLAG,MATERIAL_DETAIL_ID,PARENT_LINE_ID,ACTION_CODE,CREATED_BY,
449 CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,SECONDARY_DEFAULT_IND,
450 ORGANIZATION_ID,LOCATOR_ID,TRANS_ID)
451 VALUES
452 (V_batch_id,l_line_id,3,l_line_no,0,l_labrec.inventory_item_id,0,l_labrec.expiration_date,
453 l_labrec.secondary_uom,l_labrec.lot_number,l_labrec.quantity,l_primary_qty,l_labrec.primary_uom,
454 l_labrec.secondary_quantity,l_labrec.detail_uom,l_labrec.tracking_quantity_ind,
455 l_labrec.location_control_code,l_labrec.lot_control_code,l_labrec.grade_control_flag,
456 l_labrec.material_detail_id,l_labrec.material_detail_id,'NONE',
457 l_user_id,sysdate,l_user_id,sysdate,l_labrec.secondary_default_ind,
458 l_labrec.organization_id,l_labrec.locator_id,l_labrec.pending_product_lot_id);
459 l_line_no := l_line_no + 1;
460 END LOOP;
461 CLOSE Cur_get_lab_lots;
462 END IF;
463 END LOOP;
464 CLOSE Cur_get_batch;
465 FOR l_mat_rec IN Cur_get_lines LOOP
466 gmd_spread_fetch_pkg.load_batch_values(V_entity_id => V_entity_id,
467 V_batch_id => V_batch_id,
468 V_orgn_id => V_orgn_id,
469 V_matl_detl_id => l_mat_rec.material_detail_id,
470 V_plant_id => V_plant_id);
471 END LOOP;
472 END IF; --IF (V_batch_id IS NOT NULL) THEN
473
474 EXCEPTION
475 WHEN OTHERS THEN
476 gmd_debug.put_line('error '||SQLERRM);
477 fnd_msg_pub.add_exc_msg ('GMD_SPREAD_FETCH_PKG', 'Load_Batch_Details');
478 END load_batch_details;
479
480 /*##############################################################
481 # NAME
482 # load_formula_details
483 # SYNOPSIS
484 # proc load_formula_details
485 # DESCRIPTION
486 # This procedure inserts the data into temp tables and will
487 # be fetched in the form.
488 # HISTORY
489 # Kapil M 12-FEB-2007 Bug# 5716318 : Auto-Prod Calcualtion ME
490 # Added the newly added column - prod_percent to insert into temp tables
491 ###############################################################*/
492
493 PROCEDURE load_formula_details (V_entity_id IN NUMBER, V_formula_id IN NUMBER,
494 V_orgn_id IN NUMBER, V_plant_id IN NUMBER) IS
495 CURSOR Cur_get_formula IS
496 SELECT a.*,b.description,b.default_grade,
497 b.primary_uom_code primary,b.secondary_uom_code secondary,
498 b.lot_control_code,b.revision_qty_control_code,b.secondary_default_ind,
499 b.grade_control_flag,b.tracking_quantity_ind,b.location_control_code
500 FROM fm_matl_dtl a, mtl_system_items_b b
501 WHERE a.inventory_item_id = b.inventory_item_id
502 AND b.organization_id = a.organization_id
503 AND a.formula_id = V_entity_id
504 AND (a.line_type <> 1 OR a.line_no = 1)
505 ORDER BY a.line_type, a.line_no;
506
507 CURSOR Cur_get_materials IS
508 SELECT a.*, b.lot_number lot,b.expiration_date expire, b.inventory_item_id itemid
509 FROM gmd_material_details_gtmp a, mtl_lot_numbers b, (select inventory_item_id,lot_number, lot_organization_id
510 from gmd_technical_data_vl group by inventory_item_id,lot_number,lot_organization_id) c
511 WHERE a.entity_id = V_formula_id
512 AND a.inventory_item_id = b.inventory_item_id
513 AND a.inventory_item_id = c.inventory_item_id
514 AND b.organization_id = c.lot_organization_id
515 AND b.lot_number = c.lot_number --Added this condition for Bug10633233
516 AND a.lot_control_code = 2
517 AND (a.line_type <> 1)
518 ORDER BY a.formulaline_id,b.lot_number;
519
520 CURSOR Cur_get_lines IS
521 SELECT formulaline_id
522 FROM gmd_material_details_gtmp
523 WHERE entity_id = V_entity_id
524 AND line_type <> 3 ;
525
526 l_line_id NUMBER DEFAULT 0;
527 l_matl_rec Cur_get_materials%ROWTYPE;
528 l_formula_rec Cur_get_formula%ROWTYPE;
529 l_line_no NUMBER;
530 l_secondary_qty NUMBER;
531 l_formulaline_id NUMBER DEFAULT 0;
532
533 CURSOR Cur_get_formulaid (V_validity_rule_id NUMBER) IS
534 SELECT formula_id
535 FROM gmd_recipes r, gmd_recipe_validity_rules v
536 WHERE recipe_validity_rule_id = V_validity_rule_id
537 AND r.recipe_id = v.recipe_id;
538
539 l_return_status VARCHAR2(1);
540 l_msg_data VARCHAR2(2000);
541 l_msg_count NUMBER(10);
542 l_return_code NUMBER(10);
543 l_recipe_validity_out GMD_VALIDITY_RULES.recipe_validity_tbl;
544 l_rec_count NUMBER(10);
545 l_tpformula_id NUMBER(10);
546
547 BEGIN
548 /* Inserting the item and lot data from formula detail tables to temp tables*/
549 IF (V_formula_id IS NOT NULL) THEN
550 OPEN Cur_get_formula;
551 LOOP
552 l_line_id := l_line_id + 1;
553 FETCH Cur_get_formula INTO l_formula_rec;
554 EXIT WHEN Cur_get_formula%NOTFOUND;
555 /* Getting the secondary qty*/
556 l_secondary_qty := null;
557 IF (l_formula_rec.qty > 0 AND l_formula_rec.tracking_quantity_ind = 'PS') THEN
558 l_secondary_qty := gmd_labuom_calculate_pkg.uom_conversion (pitem_id => l_formula_rec.inventory_item_id,
559 pformula_id => NULL,
560 plot_number => NULL,
561 pcur_qty => l_formula_rec.qty,
562 pcur_uom => l_formula_rec.detail_uom,
563 pnew_uom => l_formula_rec.secondary,
564 patomic => 0,
565 plab_id => l_formula_rec.organization_id);
566 IF (l_secondary_qty < 0) THEN
567 l_secondary_qty := NULL;
568 END IF;
569 END IF;
570
571 l_tpformula_id := NULL; /* Added in Bug No.7462584 */
572 IF l_formula_rec.line_type = -1 THEN /* Added in Bug No.7462584 */
573 IF l_formula_rec.tpformula_id IS NULL THEN
574 /*GMD_VAL_DATA_PUB.get_val_data (p_api_version => 1.0
575 ,p_init_msg_list => 'T'
576 ,p_object_type => 'L'
577 ,p_item_id => l_formula_rec.inventory_item_id
578 ,p_product_qty => l_formula_rec.qty
579 ,p_uom => l_formula_rec.detail_uom
580 ,p_organization_id => V_orgn_id
581 ,x_return_status => l_return_status
582 ,x_msg_count => l_msg_count
583 ,x_msg_data => l_msg_data
584 ,x_return_code => l_return_code
585 ,x_recipe_validity_out => l_recipe_validity_out);
586 IF l_return_status = 'S' THEN
587 l_rec_count := l_recipe_validity_out.COUNT;
588 IF l_rec_count > 0 THEN
589 OPEN Cur_get_formulaid (l_recipe_validity_out (l_rec_count).recipe_validity_rule_id);
590 FETCH Cur_get_formulaid INTO l_tpformula_id;
591 CLOSE Cur_get_formulaid;
592 END IF;
593 END IF;
594 ELSE
595 l_tpformula_id := l_formula_rec.tpformula_id;
596 END IF; */
597 BEGIN
598 SELECT a.formula_id INTO l_tpformula_id
599 FROM
600 (SELECT a.formula_id
601 FROM Fm_form_mst a, fm_matl_dtl b, gmd_technical_data_hdr g
602 WHERE b.item_id = l_formula_rec.item_id
603 AND a.formula_id = b.formula_id
604 AND g.item_id = b.item_id
605 AND g.formula_id = a.formula_id
606 AND g.organization_id = V_orgn_id
607 AND a.formula_id <>0
608 AND b.line_type = 1
609 AND a.delete_mark =0
610 AND g.delete_mark =0
611 ORDER BY a.formula_id) a
612 WHERE rownum < 2;
613 EXCEPTION
614 WHEN OTHERS THEN
615 l_tpformula_id := l_formula_rec.tpformula_id;
616 END;
617 ELSE
618 l_tpformula_id := l_formula_rec.tpformula_id;
619 END IF;
620 ELSIF l_formula_rec.line_type = 1 THEN
621 l_tpformula_id := V_formula_id ;
622 END IF; /*Added in Bug No.7462584 */
623
624 INSERT INTO GMD_MATERIAL_DETAILS_GTMP
625 (ENTITY_ID,LINE_ID,LINE_TYPE,LINE_NO,ROLLUP_IND,EXPAND_IND,TRACKING_QUANTITY_IND,
626 LOCATION_CONTROL_CODE,INVENTORY_ITEM_ID,DESCRIPTION,TPFORMULA_ID,
627 QTY,SECONDARY_QTY,DETAIL_UOM,GRADE_CODE,PRIMARY_UOM,SECONDARY_UOM,LOT_CONTROL_CODE,
628 GRADE_CONTROL_FLAG,REVISION,TEXT_CODE,ORGINAL_TEXT_CODE,FORMULALINE_ID,PARENT_LINE_ID,
629 ACTION_CODE,BUFFER_IND,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,
630 REVISION_QTY_CONTROL_CODE,ORGANIZATION_ID,SECONDARY_DEFAULT_IND,PROD_PERCENT)
631 VALUES
632 (l_formula_rec.formula_id,l_line_id,l_formula_rec.line_type,l_formula_rec.line_no,1,1,
633 l_formula_rec.tracking_quantity_ind,l_formula_rec.location_control_code,
634 l_formula_rec.inventory_item_id,l_formula_rec.description,
635 l_tpformula_id,l_formula_rec.qty,l_secondary_qty,l_formula_rec.detail_uom,
636 l_formula_rec.default_grade,l_formula_rec.primary,l_formula_rec.secondary,
637 l_formula_rec.lot_control_code,l_formula_rec.grade_control_flag,
638 l_formula_rec.revision,l_formula_rec.text_code,l_formula_rec.text_code,l_formula_rec.formulaline_id,
639 l_formula_rec.formulaline_id,'NONE',l_formula_rec.buffer_ind,l_formula_rec.created_by,
640 l_formula_rec.creation_date,l_formula_rec.last_updated_by,
641 l_formula_rec.last_update_date,l_formula_rec.revision_qty_control_code,
642 l_formula_rec.organization_id, l_formula_rec.secondary_default_ind,l_formula_rec.prod_percent);
643 END LOOP;
644 CLOSE Cur_get_formula;
645
646 OPEN Cur_get_materials;
647 LOOP
648 l_line_id := l_line_id + 1;
649 FETCH Cur_get_materials INTO l_matl_rec;
650 EXIT WHEN Cur_get_materials%NOTFOUND;
651 IF l_formulaline_id <> l_matl_rec.formulaline_id THEN
652 l_line_no := 1;
653 l_formulaline_id := l_matl_rec.formulaline_id;
654 END IF;
655 IF (l_matl_rec.tracking_quantity_ind = 'PS') THEN
656 l_secondary_qty := 0;
657 ELSE
658 l_secondary_qty := NULL;
659 END IF;
660
661 INSERT INTO GMD_MATERIAL_DETAILS_GTMP
662 (ENTITY_ID,LINE_ID,LINE_TYPE,LINE_NO,ROLLUP_IND,TRACKING_QUANTITY_IND,LOCATION_CONTROL_CODE,
663 INVENTORY_ITEM_ID,EXPAND_IND,EXPIRATION_DATE,LOT_NUMBER,QTY,SECONDARY_QTY,
664 DETAIL_UOM,GRADE_CODE,FORMULALINE_ID,PARENT_LINE_ID,LOT_CONTROL_CODE,
665 GRADE_CONTROL_FLAG,ACTION_CODE,SECONDARY_UOM,SECONDARY_DEFAULT_IND,
666 CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,ORGANIZATION_ID,PROD_PERCENT)
667 VALUES
668 (l_matl_rec.entity_id,l_line_id,3,l_line_no,0,l_matl_rec.tracking_quantity_ind,
669 l_matl_rec.location_control_code,l_matl_rec.inventory_item_id,0,l_matl_rec.expiration_date,
670 l_matl_rec.lot,0,l_secondary_qty,l_matl_rec.detail_uom,l_matl_rec.grade_code,
671 l_matl_rec.formulaline_id,l_matl_rec.formulaline_id,l_matl_rec.lot_control_code,
672 l_matl_rec.grade_control_flag,'NONE',l_matl_rec.secondary_uom,l_matl_rec.secondary_default_ind,
673 l_matl_rec.created_by,l_matl_rec.creation_date,
674 l_matl_rec.last_updated_by,l_matl_rec.last_update_date,l_matl_rec.organization_id,l_formula_rec.prod_percent);
675 l_line_no := l_line_no + 1;
676 END LOOP;
677 CLOSE Cur_get_materials;
678 FOR l_form_rec IN Cur_get_lines LOOP
679 gmd_spread_fetch_pkg.load_formula_values(V_entity_id => V_entity_id,
680 V_formula_id => V_formula_id,
681 V_orgn_id => V_orgn_id,
682 V_formulaline_id => l_form_rec.formulaline_id,
683 V_plant_id => V_plant_id);
684 END LOOP;
685 END IF;
686 EXCEPTION
687 WHEN OTHERS THEN
688 fnd_msg_pub.add_exc_msg ('GMD_SPREAD_FETCH_PKG', 'Load_Formula_Details');
689 END load_formula_details;
690
691 /*##############################################################
692 # NAME
693 # load_tech_params
694 # SYNOPSIS
695 # proc load_tech_params
696 # DESCRIPTION
697 # This procedure inserts the data into temp tables for tech
698 # parameters .
699 ###############################################################*/
700
701 PROCEDURE load_tech_params (V_entity_id IN NUMBER,V_sprd_id IN NUMBER,V_batch_id IN NUMBER,
702 V_orgn_id IN NUMBER,V_folder_name IN VARCHAR2,
703 V_inv_item_id IN NUMBER,V_formula_id IN NUMBER) IS
704 CURSOR Cur_get_prod IS
705 SELECT inventory_item_id
706 FROM fm_matl_dtl
707 WHERE formula_id = V_entity_id
708 AND line_type = 1
709 AND line_no = 1;
710
711 CURSOR Cur_get_material_prod IS
712 SELECT inventory_item_id
713 FROM gme_material_details
714 WHERE batch_id = V_entity_id
715 AND line_type = 1
716 AND line_no = 1;
717
718 CURSOR Cur_get_lcf_prod IS
719 SELECT inventory_item_id
720 FROM gmd_lcf_details_gtmp
721 WHERE entity_id = V_entity_id
722 AND line_type = 1
723 AND line_no = 1;
724
725 l_item_id NUMBER;
726 l_category_set_id NUMBER;
727
728 CURSOR Cur_get_category IS
729 SELECT category_id
730 FROM mtl_item_categories
731 WHERE category_set_id = l_category_set_id
732 AND inventory_item_id = l_item_id;
733 l_category_id NUMBER;
734
735 CURSOR Cur_get_item_count IS
736 SELECT count(*)
737 FROM gmd_technical_sequence_vl
738 WHERE organization_id = V_orgn_id
739 AND inventory_item_id = l_item_id
740 AND delete_mark = 0;
741
742 CURSOR Cur_get_category_count IS
743 SELECT count(*)
744 FROM gmd_technical_sequence_vl
745 WHERE category_id = l_category_id
746 AND organization_id = V_orgn_id
747 AND delete_mark = 0;
748 l_count NUMBER;
749
750 /*CURSOR Cur_get_folder_cols IS
751 SELECT c.*, b.sequence
752 FROM fnd_folders a, fnd_folder_columns b, lm_tech_hdr c
753 WHERE a.folder_id = b.folder_id
754 AND b.item_prompt = c.tech_parm_name
755 AND a.name = V_folder_name
756 AND a.OBJECT = 'SPREAD_DTL_SUB'
757 AND c.organization_id = V_orgn_id;*/
758
759 -- Bug: 7006219 Vpedarla Modified the cursor Cur_get_folder_cols to get the default organization tech. parameters sequence
760 -- if item parameters sequence is not defined.
761 CURSOR Cur_get_folder_cols IS
762 SELECT c.*, b.sequence
763 FROM fnd_folders a,
764 fnd_folder_columns b, lm_tech_hdr c, gmd_technical_sequence_vl s
765 WHERE a.folder_id(+) = b.folder_id
766 AND c.tech_parm_name = b.item_prompt(+)
767 AND a.name(+) = V_folder_name --- Bug 4254315 ....
768 AND a.OBJECT(+) = 'SPREAD_DTL_SUB'
769 AND c.organization_id = V_orgn_id
770 AND c.tech_parm_id = s.tech_parm_id
771 AND c.organization_id = s.organization_id
772 AND NVL(l_item_id, 1) = NVL(s.inventory_item_id,1)
773 AND NVL(s.category_id, 1) = NVL(l_category_id ,1)
774 UNION
775 SELECT c.*, b.sequence
776 FROM fnd_folders a,
777 fnd_folder_columns b, lm_tech_hdr c, gmd_technical_sequence_vl s
778 WHERE a.folder_id(+) = b.folder_id
779 AND c.tech_parm_name = b.item_prompt(+)
780 AND a.name(+) = V_folder_name --- Bug 4254315 ....
781 AND a.OBJECT(+) = 'SPREAD_DTL_SUB'
782 AND c.organization_id = V_orgn_id
783 AND c.tech_parm_id = s.tech_parm_id
784 AND c.organization_id = s.organization_id
785 AND s.inventory_item_id is NULL
786 AND NVL(s.category_id, 1) = NVL(l_category_id ,1)
787 ORDER BY sequence ;
788
789 l_rec Cur_get_folder_cols%ROWTYPE;
790 BEGIN
791 DELETE FROM gmd_technical_parameter_gtmp;
792 l_category_set_id := TO_NUMBER (FND_PROFILE.VALUE ('GMD_TECH_CATG_SET'));
793 IF (V_folder_name IS NOT NULL) THEN
794 OPEN Cur_get_folder_cols;
795 LOOP
796 FETCH Cur_get_folder_cols INTO l_rec;
797 EXIT WHEN Cur_get_folder_cols%NOTFOUND;
798 INSERT INTO GMD_TECHNICAL_PARAMETER_GTMP
799 (ENTITY_ID,TECH_PARM_NAME,TECH_PARM_ID,PARM_DESCRIPTION,SORT_SEQ,QCASSY_TYP_ID,
800 DATA_TYPE,EXPRESSION_CHAR,LM_UNIT_CODE,SIGNIF_FIGURES,LOWERBOUND_NUM,
801 UPPERBOUND_NUM,LOWERBOUND_CHAR,UPPERBOUND_CHAR,MAX_LENGTH)
802 VALUES (V_entity_id,l_rec.tech_parm_name,l_rec.tech_parm_id,l_rec.parm_description,l_rec.sequence,l_rec.qcassy_typ_id,
803 l_rec.data_type,l_rec.expression_char,l_rec.lm_unit_code,
804 DECODE(l_rec.data_type, 4, NVL(l_rec.signif_figures, 0 ), 11, NVL(l_rec.signif_figures, 0 ),l_rec.signif_figures ),
805 l_rec.lowerbound_num,l_rec.upperbound_num,l_rec.lowerbound_char,l_rec.upperbound_char,l_rec.max_length);
806 END LOOP;
807 CLOSE Cur_get_folder_cols;
808
809 ELSIF (V_sprd_id IS NOT NULL) THEN
810 INSERT INTO GMD_TECHNICAL_PARAMETER_GTMP
811 (ENTITY_ID,TECH_PARM_NAME,TECH_PARM_ID,PARM_DESCRIPTION,SORT_SEQ,QCASSY_TYP_ID,DATA_TYPE,EXPRESSION_CHAR,
812 LM_UNIT_CODE,SIGNIF_FIGURES,LOWERBOUND_NUM,UPPERBOUND_NUM,OPTIMIZE_TYPE,LOWERBOUND_CHAR,UPPERBOUND_CHAR,MAX_LENGTH)
813 SELECT a.sprd_id,a.tech_parm_name,b.tech_parm_id,b.parm_description,a.sort_seq,b.qcassy_typ_id,
814 b.data_type,b.expression_char,b.lm_unit_code,
815 DECODE(b.data_type, 4, NVL(b.signif_figures, 0 ), 11, NVL(b.signif_figures, 0 ),b.signif_figures ),
816 a.min_value,a.max_value,a.optimize_type,
817 b.lowerbound_char,b.upperbound_char,b.max_length
818 FROM lm_sprd_prm a, lm_tech_hdr b
819 WHERE a.tech_parm_id = b.tech_parm_id
820 AND a.sprd_id = V_entity_id
821 AND a.organization_id = V_orgn_id;
822
823 ELSE
824 IF (V_inv_item_id IS NOT NULL) THEN
825 l_item_id := V_inv_item_id;
826 ELSIF (V_batch_id IS NOT NULL) THEN
827 OPEN Cur_get_material_prod;
828 FETCH Cur_get_material_prod INTO l_item_id;
829 CLOSE Cur_get_material_prod;
830 ELSIF (V_formula_id IS NOT NULL) THEN
831 OPEN Cur_get_prod;
832 FETCH Cur_get_prod INTO l_item_id;
833 CLOSE Cur_get_prod;
834 ELSE
835 OPEN Cur_get_lcf_prod;
836 FETCH Cur_get_lcf_prod INTO l_item_id;
837 CLOSE Cur_get_lcf_prod;
838 END IF;
839
840 --Fetching the category id
841 OPEN Cur_get_category;
842 FETCH Cur_get_category INTO l_category_id;
843 CLOSE Cur_get_category;
844
845 --If item has the parameters then insert
846 OPEN Cur_get_item_count;
847 FETCH Cur_get_item_count INTO l_count;
848 CLOSE Cur_get_item_count;
849 IF (l_count > 0) THEN
850 INSERT INTO GMD_TECHNICAL_PARAMETER_GTMP
851 (ENTITY_ID,TECH_PARM_NAME,TECH_PARM_ID,PARM_DESCRIPTION,SORT_SEQ,QCASSY_TYP_ID,DATA_TYPE,EXPRESSION_CHAR,
852 LM_UNIT_CODE,SIGNIF_FIGURES,LOWERBOUND_NUM,UPPERBOUND_NUM,LOWERBOUND_CHAR,UPPERBOUND_CHAR,MAX_LENGTH)
853 SELECT V_entity_id,a.tech_parm_name,b.tech_parm_id,b.parm_description,a.sort_seq,
854 b.qcassy_typ_id,b.data_type,b.expression_char,b.lm_unit_code,
855 DECODE(b.data_type, 4, NVL(b.signif_figures, 0 ), 11, NVL(b.signif_figures, 0 ),b.signif_figures ),
856 b.lowerbound_num,b.upperbound_num,
857 b.lowerbound_char,b.upperbound_char,b.max_length
858 FROM gmd_technical_sequence_vl a, lm_tech_hdr b
859 WHERE a.tech_parm_id = b.tech_parm_id
860 AND a.inventory_item_id = l_item_id
861 AND a.organization_id = V_orgn_id;
862 ELSE
863 --If item category has the parameters then insert
864 OPEN Cur_get_category_count;
865 FETCH Cur_get_category_count INTO l_count;
866 CLOSE Cur_get_category_count;
867 IF (l_count > 0) THEN
868 INSERT INTO GMD_TECHNICAL_PARAMETER_GTMP
869 (ENTITY_ID,TECH_PARM_NAME,TECH_PARM_ID,PARM_DESCRIPTION,SORT_SEQ,QCASSY_TYP_ID,DATA_TYPE,EXPRESSION_CHAR,
870 LM_UNIT_CODE,SIGNIF_FIGURES,LOWERBOUND_NUM,UPPERBOUND_NUM,LOWERBOUND_CHAR,UPPERBOUND_CHAR,MAX_LENGTH)
871 SELECT V_entity_id,a.tech_parm_name,b.tech_parm_id,b.parm_description,a.sort_seq,
872 b.qcassy_typ_id,b.data_type,b.expression_char,b.lm_unit_code,
873 DECODE(b.data_type, 4, NVL(b.signif_figures, 0 ), 11, NVL(b.signif_figures, 0 ),b.signif_figures ),
874 b.lowerbound_num,b.upperbound_num,
875 b.lowerbound_char,b.upperbound_char,b.max_length
876 FROM gmd_technical_sequence_vl a, lm_tech_hdr b
877 WHERE a.tech_parm_id = b.tech_parm_id
878 AND a.category_id = l_category_id
879 AND a.organization_id = V_orgn_id;
880 ELSE
881 --If organization has the parameters then insert
882 INSERT INTO GMD_TECHNICAL_PARAMETER_GTMP
883 (ENTITY_ID,TECH_PARM_NAME,TECH_PARM_ID,PARM_DESCRIPTION,SORT_SEQ,QCASSY_TYP_ID,DATA_TYPE,EXPRESSION_CHAR,
884 LM_UNIT_CODE,SIGNIF_FIGURES,LOWERBOUND_NUM,UPPERBOUND_NUM,LOWERBOUND_CHAR,UPPERBOUND_CHAR,MAX_LENGTH)
885 SELECT V_entity_id,a.tech_parm_name,b.tech_parm_id,b.parm_description,a.sort_seq,
886 b.qcassy_typ_id,b.data_type,b.expression_char,b.lm_unit_code,
887 DECODE(b.data_type, 4, NVL(b.signif_figures, 0 ), 11, NVL(b.signif_figures, 0 ),b.signif_figures ),
888 b.lowerbound_num,b.upperbound_num,
889 b.lowerbound_char,b.upperbound_char,b.max_length
890 FROM gmd_technical_sequence_vl a, lm_tech_hdr b
891 WHERE a.tech_parm_id = b.tech_parm_id
892 AND a.organization_id = V_orgn_id
893 AND a.inventory_item_id IS NULL
894 AND a.category_id IS NULL;
895 END IF;
896 END IF;
897 END IF;
898 EXCEPTION
899 WHEN OTHERS THEN
900 fnd_msg_pub.add_exc_msg ('GMD_SPREAD_FETCH_PKG', 'Load_Tech_Params');
901 END load_tech_params;
902
903 /*##############################################################
904 # NAME
905 # add_new_line
906 # SYNOPSIS
907 # proc add_new_line
908 # DESCRIPTION
909 # This procedure inserts the data into temp tables and will
910 # be fetched in the form.
911 ###############################################################*/
912
913 PROCEDURE add_new_line (V_entity_id IN NUMBER, V_inv_item_id IN NUMBER, V_line_type IN NUMBER,
914 V_line_no IN NUMBER, V_source_ind IN NUMBER,V_formula_id IN NUMBER,V_move_order_header_id IN NUMBER,
915 V_orgn_id IN NUMBER, X_line_id OUT NOCOPY NUMBER,
916 X_parent_line_id OUT NOCOPY NUMBER,X_move_order_line_id OUT NOCOPY NUMBER,
917 V_plant_id IN NUMBER) IS
918 CURSOR Cur_get_item IS
919 SELECT b.description,b.default_grade,b.secondary_default_ind,
920 b.primary_uom_code,b.secondary_uom_code,b.tracking_quantity_ind,
921 b.lot_control_code,b.grade_control_flag,b.location_control_code,
922 b.revision_qty_control_code,b.mtl_transactions_enabled_flag
923 FROM mtl_system_items_b b
924 WHERE b.inventory_item_id = V_inv_item_id
925 AND b.organization_id = V_orgn_id;
926
927 CURSOR Cur_line_id IS
928 SELECT MAX(line_id)
929 FROM gmd_material_details_gtmp
930 WHERE entity_id = V_entity_id;
931
932 CURSOR Cur_formulaline_id IS
933 SELECT gem5_formulaline_id_s.NEXTVAL
934 FROM dual;
935
936 CURSOR Cur_spreadline_id IS
937 SELECT gem5_sprd_line_id_s.nextval
938 FROM dual;
939
940 CURSOR Cur_get_materials (Pline_id NUMBER) IS
941 SELECT *
942 FROM gmd_material_details_gtmp
943 WHERE line_id = Pline_id;
944
945 CURSOR Cur_get_batch IS
946 SELECT *
947 FROM gme_batch_header
948 WHERE batch_id = V_entity_id;
949
950 l_line_id NUMBER;
951 l_item_rec Cur_get_item%ROWTYPE;
952 l_line_no NUMBER DEFAULT 0;
953 l_parentline_id NUMBER;
954 l_user_id NUMBER;
955 l_return_status VARCHAR2(1);
956 l_msg_count NUMBER;
957 l_msg_data VARCHAR2(2000);
958 X_return_status VARCHAR2(1);
959 l_rec Cur_get_materials%ROWTYPE;
960 l_batch Cur_get_batch%ROWTYPE;
961 l_materials gme_common_pvt.material_details_tab;
962 l_materials_out gme_common_pvt.material_details_tab;
963 l_trolin inv_move_order_pub.trolin_tbl_type;
964
965 create_mo_line_err EXCEPTION;
966 setup_failure EXCEPTION;
967 BEGIN
968 l_user_id := TO_NUMBER (fnd_profile.VALUE ('USER_ID'));
969 IF NOT (gme_common_pvt.setup(P_org_id => V_orgn_id)) THEN
970 RAISE setup_failure;
971 END IF;
972 /* Inserting the item and lot data for the item entered in the form*/
973 OPEN Cur_line_id;
974 FETCH Cur_line_id INTO l_line_id;
975 CLOSE Cur_line_id;
976 IF (V_source_ind = 0) THEN
977 OPEN Cur_formulaline_id;
978 FETCH Cur_formulaline_id INTO l_parentline_id;
979 CLOSE Cur_formulaline_id;
980 ELSE
981 OPEN Cur_spreadline_id;
982 FETCH Cur_spreadline_id INTO l_parentline_id;
983 CLOSE Cur_spreadline_id;
984 END IF;
985 OPEN Cur_get_item;
986 FETCH Cur_get_item INTO l_item_rec;
987 CLOSE Cur_get_item;
988 l_line_id := l_line_id + 1;
989
990 INSERT INTO GMD_MATERIAL_DETAILS_GTMP
991 (ENTITY_ID,LINE_ID,LINE_TYPE,LINE_NO,ROLLUP_IND,INVENTORY_ITEM_ID,
992 DESCRIPTION,EXPAND_IND,QTY,DETAIL_UOM,GRADE_CODE,PRIMARY_UOM,SECONDARY_UOM,TRACKING_QUANTITY_IND,SECONDARY_DEFAULT_IND,
993 LOT_CONTROL_CODE,GRADE_CONTROL_FLAG,LOCATION_CONTROL_CODE,TEXT_CODE,ORGINAL_TEXT_CODE,FORMULALINE_ID,
994 MATERIAL_DETAIL_ID,PARENT_LINE_ID,ACTION_CODE,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,
995 LAST_UPDATE_DATE,REVISION_QTY_CONTROL_CODE,ORGANIZATION_ID)
996 VALUES
997 (V_entity_id,l_line_id,V_line_type,V_line_no,1,V_inv_item_id,
998 l_item_rec.description,1,0,l_item_rec.primary_uom_code,l_item_rec.default_grade,l_item_rec.primary_uom_code,
999 l_item_rec.secondary_uom_code,l_item_rec.tracking_quantity_ind,l_item_rec.secondary_default_ind,
1000 l_item_rec.lot_control_code,l_item_rec.grade_control_flag,l_item_rec.location_control_code,
1001 NULL,NULL,l_parentline_id,l_parentline_id,l_parentline_id,'NONE',l_user_id,SYSDATE,
1002 l_user_id,SYSDATE,l_item_rec.revision_qty_control_code,V_orgn_id);
1003
1004 X_line_id := l_line_id;
1005 X_parent_line_id := l_parentline_id;
1006
1007 --Create a move order line for SAI form to be called before updating the batch.
1008 OPEN Cur_get_batch;
1009 FETCH Cur_get_batch INTO l_batch;
1010 CLOSE Cur_get_batch;
1011 IF (V_line_type = -1 AND l_batch.update_inventory_ind = 'Y'
1012 AND l_item_rec.mtl_transactions_enabled_flag = 'Y') THEN
1013 IF (V_source_ind = 1) THEN
1014 OPEN Cur_get_materials(l_line_id);
1015 FETCH Cur_get_materials INTO l_rec;
1016 CLOSE Cur_get_materials;
1017
1018 IF (l_rec.line_type = -1) THEN
1019 l_materials(1).material_requirement_date := l_batch.plan_start_date;
1020 ELSE
1021 l_materials(1).material_requirement_date := l_batch.plan_cmplt_date;
1022 END IF;
1023
1024 l_materials(1).inventory_item_id := V_inv_item_id;
1025 l_materials(1).material_detail_id := l_parentline_id;
1026 l_materials(1).organization_id := V_orgn_id;
1027 l_materials(1).plan_qty := 0;
1028 l_materials(1).dtl_um := l_rec.detail_uom;
1029 l_materials(1).line_type := V_line_type;
1030 l_materials(1).batch_id := V_entity_id;
1031 l_materials(1).creation_date := gme_common_pvt.g_timestamp;
1032 l_materials(1).created_by := gme_common_pvt.g_user_ident;
1033 l_materials(1).last_update_date := gme_common_pvt.g_timestamp;
1034 l_materials(1).last_updated_by := gme_common_pvt.g_user_ident;
1035
1036 gme_move_orders_pvt.create_move_order_lines (p_move_order_header_id => v_move_order_header_id
1037 ,p_move_order_type => gme_common_pvt.g_invis_move_order_type
1038 ,p_material_details_tbl => l_materials
1039 ,x_material_details_tbl => l_materials_out
1040 ,x_trolin_tbl => l_trolin
1041 ,x_return_status => l_return_status);
1042 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1043 RAISE create_mo_line_err;
1044 ELSE
1045 X_move_order_line_id := l_materials_out(1).move_order_line_id;
1046 END IF;
1047 END IF;
1048 END IF;
1049 --Fixed for ADS demo this code was in the if condition before.
1050 IF (V_line_type = -1) THEN
1051 gmd_spread_fetch_pkg.load_formula_values(V_entity_id => V_entity_id,
1052 V_formula_id => V_formula_id,
1053 V_orgn_id => V_orgn_id,
1054 V_formulaline_id => l_parentline_id,
1055 V_plant_id => V_plant_id);
1056 END IF;
1057 EXCEPTION
1058 WHEN create_mo_line_err THEN
1059 FOR i IN 1 .. l_msg_count LOOP
1060 l_msg_data := fnd_msg_pub.get (p_msg_index => i
1061 ,p_encoded => 'T');
1062 END LOOP;
1063 WHEN setup_failure THEN
1064 x_return_status := fnd_api.g_ret_sts_error;
1065 WHEN OTHERS THEN
1066 fnd_msg_pub.add_exc_msg ('GMD_SPREAD_FETCH_PKG', 'Add_New_line');
1067 END add_new_line;
1068
1069 /*##############################################################
1070 # NAME
1071 # load_spread_values
1072 # SYNOPSIS
1073 # proc load_spread_values
1074 # DESCRIPTION
1075 # This procedure inserts the data into temp tables and will
1076 # be fetched in the form.
1077 ###############################################################*/
1078
1079 PROCEDURE load_spread_values (V_entity_id IN NUMBER,V_sprd_id IN NUMBER,
1080 V_orgn_id IN NUMBER,V_parent_line_id IN NUMBER) IS
1081 CURSOR Cur_get_line IS
1082 SELECT line_id
1083 FROM gmd_material_details_gtmp
1084 WHERE parent_line_id = V_parent_line_id
1085 ORDER BY line_type;
1086 BEGIN
1087 /* Inserting the technical parameter data of item and lot to temp tables*/
1088 IF (V_sprd_id IS NOT NULL) THEN
1089 INSERT INTO GMD_TECHNICAL_DATA_GTMP
1090 (ENTITY_ID,LINE_ID,TECH_PARM_NAME,TECH_PARM_ID,
1091 VALUE,SORT_SEQ,NUM_DATA,TEXT_DATA,BOOLEAN_DATA)
1092 SELECT a.sprd_id,c.line_id,a.tech_parm_name,a.tech_parm_id,
1093 DECODE(B.DATA_TYPE,0,TEXT_DATA,2,TEXT_DATA,3,BOOLEAN_DATA,NUM_DATA) VALUE,
1094 b.sort_seq,a.num_data,a.text_data,a.boolean_data
1095 FROM lm_sprd_tec a, gmd_technical_parameter_gtmp b, gmd_material_details_gtmp c
1096 WHERE a.tech_parm_id = b.tech_parm_id
1097 AND a.sprd_id = V_entity_id
1098 AND a.line_id = c.sprd_line_id
1099 AND a.organization_id = V_orgn_id
1100 AND c.parent_line_id = V_parent_line_id;
1101 END IF;
1102 EXCEPTION
1103 WHEN OTHERS THEN
1104 fnd_msg_pub.add_exc_msg ('GMD_SPREAD_FETCH_PKG', 'Load_Spread_Values');
1105 END load_spread_values;
1106
1107 /*##############################################################
1108 # NAME
1109 # load_batch_values
1110 # SYNOPSIS
1111 # proc load_batch_values
1112 # DESCRIPTION
1113 # This procedure inserts the data into temp tables and will
1114 # be fetched in the form.
1115 ###############################################################*/
1116
1117 PROCEDURE load_batch_values (V_entity_id IN NUMBER,V_batch_id IN NUMBER,
1118 V_orgn_id IN NUMBER,V_matl_detl_id IN NUMBER,
1119 V_line_id IN NUMBER,V_plant_id IN NUMBER) IS
1120 CURSOR Cur_get_line IS
1121 SELECT line_id
1122 FROM gmd_material_details_gtmp
1123 WHERE parent_line_id = V_matl_detl_id
1124 ORDER BY line_type;
1125 BEGIN
1126 /* Inserting the technical parameter data of item and lot to temp tables*/
1127 IF (V_batch_id IS NOT NULL) THEN
1128 INSERT INTO GMD_TECHNICAL_DATA_GTMP
1129 (ENTITY_ID,LINE_ID,TECH_PARM_NAME,TECH_PARM_ID,
1130 VALUE,SORT_SEQ,NUM_DATA,TEXT_DATA,BOOLEAN_DATA)
1131 SELECT V_entity_id,c.line_id,a.tech_parm_name, a.tech_parm_id,
1132 DECODE(B.DATA_TYPE,0,TEXT_DATA,2,TEXT_DATA,3,BOOLEAN_DATA,NUM_DATA) VALUE,
1133 b.sort_seq,a.num_data,a.text_data,a.boolean_data
1134 FROM gmd_technical_data_vl a, gmd_technical_parameter_gtmp b, gmd_material_details_gtmp c
1135 WHERE a.tech_parm_id = b.tech_parm_id
1136 AND a.organization_id = V_orgn_id
1137 AND a.inventory_item_id = c.inventory_item_id
1138 AND (V_matl_detl_id IS NULL OR c.parent_line_id = V_matl_detl_id)
1139 AND c.entity_id = V_entity_id
1140 AND (V_line_id IS NULL OR c.line_id = V_line_id)
1141 AND (a.batch_id = V_entity_id OR
1142 (a.batch_id IS NULL AND NOT EXISTS ( SELECT 1
1143 FROM GMD_TECHNICAL_DATA_VL e
1144 WHERE e.inventory_item_id = c.inventory_item_id
1145 AND nvl(e.lot_number, '-1') = nvl(c.lot_number, '-1')
1146 AND nvl(e.lot_organization_id, c.organization_id) = c.organization_id
1147 AND e.formula_id IS NULL
1148 AND e.batch_id = V_entity_id
1149 AND e.organization_id = V_orgn_id)))
1150 AND a.formula_id IS NULL
1151 AND a.delete_mark = 0
1152 AND NVL(c.organization_id, -1) = NVL(a.lot_organization_id, c.organization_id)
1153 AND NVL(c.lot_number, '-1') = NVL(a.lot_number, '-1');
1154
1155 gmd_spread_fetch_pkg.get_lot_density (P_orgn_id => V_orgn_id,
1156 P_parent_detl_id => V_matl_detl_id,
1157 P_entity_id => V_entity_id);
1158
1159
1160 END IF;
1161 FOR l_quality_rec IN Cur_get_line LOOP
1162 load_derived_cost (V_entity_id,V_orgn_id,l_quality_rec.line_id);
1163 load_quality_data (l_quality_rec.line_id,V_orgn_id,V_plant_id);
1164 END LOOP;
1165 EXCEPTION
1166 WHEN OTHERS THEN
1167 fnd_msg_pub.add_exc_msg ('GMD_SPREAD_FETCH_PKG', 'Load_Batch_Values');
1168 END load_batch_values;
1169
1170 /*##############################################################
1171 # NAME
1172 # load_formula_values
1173 # SYNOPSIS
1174 # proc load_formula_values
1175 # DESCRIPTION
1176 # This procedure inserts the data into temp tables and will
1177 # be fetched in the form.
1178 ###############################################################*/
1179
1180 PROCEDURE load_formula_values (V_entity_id IN NUMBER,V_formula_id IN NUMBER,
1181 V_orgn_id IN NUMBER,V_formulaline_id IN NUMBER,
1182 V_line_id IN NUMBER,V_plant_id IN NUMBER) IS
1183 CURSOR Cur_get_line IS
1184 SELECT line_id
1185 FROM gmd_material_details_gtmp
1186 WHERE (V_formulaline_id IS NULL OR parent_line_id = V_formulaline_id)
1187 ORDER BY line_type;
1188 BEGIN
1189 /* Inserting the technical parameter data of item and lot to temp tables*/
1190 IF (V_formula_id IS NOT NULL) THEN
1191 INSERT INTO GMD_TECHNICAL_DATA_GTMP
1192 (ENTITY_ID,LINE_ID,TECH_PARM_NAME,TECH_PARM_ID,
1193 VALUE,SORT_SEQ,NUM_DATA,TEXT_DATA,BOOLEAN_DATA)
1194 SELECT V_entity_id,c.line_id,a.tech_parm_name,a.tech_parm_id,
1195 DECODE(B.DATA_TYPE,0,TEXT_DATA,2,TEXT_DATA,3,BOOLEAN_DATA,NUM_DATA) VALUE,
1196 b.sort_seq,a.num_data,a.text_data,a.boolean_data
1197 FROM gmd_technical_data_vl a, gmd_technical_parameter_gtmp b, gmd_material_details_gtmp c
1198 WHERE a.tech_parm_id = b.tech_parm_id
1199 AND a.organization_id = V_orgn_id
1200 AND a.inventory_item_id = c.inventory_item_id
1201 AND (V_formulaline_id IS NULL OR c.parent_line_id = V_formulaline_id)
1202 AND c.entity_id = V_entity_id
1203 AND (V_line_id IS NULL OR c.line_id = V_line_id)
1204 AND (a.formula_id = c.tpformula_id OR
1205 (a.formula_id IS NULL AND NOT EXISTS (SELECT 1
1206 FROM GMD_TECHNICAL_DATA_VL e
1207 WHERE e.inventory_item_id = c.inventory_item_id
1208 AND NVL(e.lot_number, '-1') = NVL(c.lot_number, '-1')
1209 AND NVL(e.lot_organization_id, c.organization_id) = c.organization_id
1210 AND e.batch_id IS NULL
1211 AND e.formula_id = c.tpformula_id
1212 AND e.organization_id = V_orgn_id)))
1213 AND a.batch_id IS NULL
1214 AND a.delete_mark = 0
1215 AND NVL(c.organization_id, -1) = NVL(a.lot_organization_id, c.organization_id)
1216 AND NVL(c.lot_number, '-1') = NVL(a.lot_number, '-1');
1217
1218 gmd_spread_fetch_pkg.get_lot_density (P_orgn_id => V_orgn_id,
1219 P_parent_detl_id => V_formulaline_id,
1220 P_entity_id => V_entity_id);
1221
1222 END IF;
1223
1224 FOR l_quality_rec IN Cur_get_line LOOP
1225 load_derived_cost (V_entity_id,V_orgn_id,l_quality_rec.line_id);
1226 load_quality_data (l_quality_rec.line_id,V_orgn_id,V_plant_id);
1227 END LOOP;
1228 EXCEPTION
1229 WHEN OTHERS THEN
1230 fnd_msg_pub.add_exc_msg ('GMD_SPREAD_FETCH_PKG', 'Load_Formula_Values');
1231 END load_formula_values;
1232
1233 /*##############################################################
1234 # NAME
1235 # save_spreadsheet
1236 # SYNOPSIS
1237 # proc save_spreadsheet
1238 # DESCRIPTION
1239 # This procedure inserts the data into spreadsheet tables.
1240 ###############################################################*/
1241
1242 PROCEDURE save_spreadsheet (V_entity_id IN NUMBER,V_sprd_id IN NUMBER,
1243 V_formula_id IN NUMBER,V_batch_id IN NUMBER,
1244 V_orgn_id IN NUMBER,V_spread_name IN VARCHAR2,
1245 V_maintain_type IN NUMBER,V_text_code IN NUMBER,
1246 V_last_update_date IN DATE,V_move_order_header_id IN NUMBER) IS
1247 CURSOR Cur_sprd_id IS
1248 SELECT gem5_sprd_id_s.nextval
1249 FROM fnd_dual;
1250
1251 CURSOR Cur_line_id IS
1252 SELECT gem5_sprd_line_id_s.nextval
1253 FROM fnd_dual;
1254
1255 CURSOR Cur_sprd_insert IS
1256 SELECT line_id,V_orgn_id,move_order_line_id,line_type,formulaline_id,
1257 material_detail_id,line_no,rollup_ind,inventory_item_id,qty,detail_uom,
1258 text_code,subinventory_code,location,locator_id,lot_number,expiration_date,grade_code,
1259 transaction_id,reservation_id,secondary_qty,secondary_uom,buffer_ind,revision,
1260 revision_qty_control_code,plant_organization_id,organization_id,
1261 tpformula_id,last_updated_by,last_update_date,created_by,creation_date,prod_percent--added tpformula_id for Bug#7254259
1262 FROM gmd_material_details_gtmp
1263 WHERE entity_id = V_entity_id;
1264 X_sprd_id NUMBER(10);
1265 X_line_id NUMBER(10);
1266 l_user_id NUMBER;
1267 l_text_code NUMBER;
1268 type text_table is table of NUMBER(10) index by binary_integer;
1269 x_text_tab text_table;
1270 x_cnt NUMBER default 0;
1271 BEGIN
1272 /* saving the data to spreadsheet tables from temp tables*/
1273 l_user_id := TO_NUMBER (fnd_profile.VALUE ('USER_ID'));
1274 IF (V_sprd_id IS NOT NULL) THEN
1275 X_sprd_id := V_sprd_id;
1276 UPDATE lm_sprd_fls
1277 SET lab_organization_id = V_orgn_id,
1278 formula_id = V_formula_id,
1279 batch_id = V_batch_id,
1280 move_order_header_id = V_move_order_header_id,
1281 maintain_type = V_maintain_type,
1282 last_update_date = V_last_update_date,
1283 last_updated_by = l_user_id,
1284 text_code = V_text_code
1285 WHERE sprd_name = V_spread_name;
1286 DELETE FROM lm_sprd_dtl
1287 WHERE sprd_id = V_sprd_id;
1288 DELETE FROM lm_sprd_tec
1289 WHERE sprd_id = V_sprd_id;
1290 DELETE FROM lm_sprd_prm
1291 WHERE sprd_id = V_sprd_id;
1292 ELSE
1293 OPEN Cur_sprd_id;
1294 FETCH Cur_sprd_id INTO X_sprd_id;
1295 CLOSE Cur_sprd_id;
1296 INSERT INTO lm_sprd_fls (sprd_id, sprd_name, formula_id,batch_id, lab_organization_id, maintain_type,
1297 active_ind, delete_mark, creation_date,
1298 last_update_date, created_by,
1299 last_updated_by, text_code, in_use,move_order_header_id)
1300 VALUES (X_sprd_id, V_spread_name,
1301 V_formula_id,V_batch_id,
1302 V_orgn_id,V_maintain_type,1,0,V_last_update_date,
1303 V_last_update_date,l_user_id,
1304 l_user_id, V_text_code, 0, V_move_order_header_id);
1305 END IF;
1306
1307 INSERT INTO lm_sprd_prm (sprd_id,organization_id,tech_parm_name,tech_parm_id,sort_seq,data_type,
1308 expression_char,min_value,max_value,precision,optimize_type,
1309 last_updated_by,last_update_date,created_by,creation_date)
1310 SELECT X_sprd_id, V_orgn_id,tech_parm_name,tech_parm_id,sort_seq,data_type,
1311 expression_char,lowerbound_num,upperbound_num,signif_figures,optimize_type,
1312 l_user_id,sysdate,l_user_id,sysdate
1313 FROM gmd_technical_parameter_gtmp
1314 WHERE entity_id = V_entity_id;
1315
1316 FOR l_rec IN Cur_sprd_insert LOOP
1317 OPEN Cur_line_id;
1318 FETCH Cur_line_id INTO X_line_id;
1319 CLOSE Cur_line_id;
1320 INSERT INTO lm_sprd_dtl (line_id,sprd_id,move_order_line_id,line_type,formulaline_id,material_detail_id,line_no,
1321 rollup_ind,inventory_item_id,qty,detail_uom,revision,revision_qty_control_code,text_code,subinventory_code,
1322 location,lot_number,expiration_date,grade_code,transaction_id,reservation_id,secondary_qty,secondary_uom,
1323 buffer_ind, plant_organization_id,organization_id,tpformula_id, last_updated_by,last_update_date,--added tpformula_id for Bug#7254259
1324 created_by,creation_date,locator_id,prod_percent)
1325 VALUES
1326 (X_line_id,x_sprd_id,l_rec.move_order_line_id,l_rec.line_type,l_rec.formulaline_id,
1327 l_rec.material_detail_id,l_rec.line_no,l_rec.rollup_ind,l_rec.inventory_item_id,
1328 l_rec.qty,l_rec.detail_uom,l_rec.revision,l_rec.revision_qty_control_code,l_rec.text_code,
1329 l_rec.subinventory_code,l_rec.location,l_rec.lot_number,l_rec.expiration_date,
1330 l_rec.grade_code,l_rec.transaction_id,l_rec.reservation_id,l_rec.secondary_qty,
1331 l_rec.secondary_uom,l_rec.buffer_ind,l_rec.plant_organization_id,
1332 l_rec.organization_id,l_rec.tpformula_id,l_rec.last_updated_by,l_rec.last_update_date,--added l_rec.tpformula_id for Bug#7254259
1333 l_rec.created_by,l_rec.creation_date,l_rec.locator_id,l_rec.prod_percent);
1334
1335 INSERT INTO lm_sprd_tec (line_id,organization_id,tech_parm_name,tech_parm_id,sprd_id,sort_seq,num_data,text_data,
1336 boolean_data,last_updated_by,last_update_date,created_by,creation_date)
1337 SELECT X_line_id, V_orgn_id, tech_parm_name,tech_parm_id,
1338 X_sprd_id,sort_seq,num_data,text_data,boolean_data,
1339 l_user_id,sysdate,l_user_id,sysdate
1340 FROM gmd_technical_data_gtmp
1341 WHERE entity_id = V_entity_id
1342 AND line_id = l_rec.line_id;
1343 END LOOP;
1344 EXCEPTION
1345 WHEN OTHERS THEN
1346 fnd_msg_pub.add_exc_msg ('GMD_SPREAD_FETCH_PKG', 'Save_Spreadsheet');
1347 END save_spreadsheet;
1348
1349 /*##############################################################
1350 # NAME
1351 # get_density_value
1352 # SYNOPSIS
1353 # proc get_density_value
1354 # DESCRIPTION
1355 # This procedure gets the density value for uom conversion.
1356 ###############################################################*/
1357
1358 FUNCTION get_density_value (V_line_id IN NUMBER,
1359 V_density_parameter IN VARCHAR2) RETURN NUMBER IS
1360 CURSOR Cur_density IS
1361 SELECT value
1362 FROM gmd_technical_data_gtmp
1363 WHERE line_id = V_line_id
1364 AND tech_parm_name = V_density_parameter;
1365 l_value NUMBER;
1366 BEGIN
1367 OPEN Cur_density;
1368 FETCH Cur_density INTO l_value;
1369 CLOSE Cur_density;
1370 RETURN (l_value);
1371 END get_density_value;
1372
1373 /*##############################################################
1374 # NAME
1375 # update_line_mass_vol_qty
1376 # SYNOPSIS
1377 # proc update_line_mass_vol_qty
1378 # DESCRIPTION
1379 # This procedure calculates the qtys to mass and volume.
1380 ###############################################################*/
1381
1382 PROCEDURE update_line_mass_vol_qty (V_orgn_id IN NUMBER,
1383 V_line_id IN NUMBER,
1384 V_density_parameter IN VARCHAR2,
1385 V_mass_uom IN VARCHAR2,
1386 V_vol_uom IN VARCHAR2,
1387 X_return_status OUT NOCOPY VARCHAR2) IS
1388
1389 CURSOR Cur_line_qty IS
1390 SELECT inventory_item_id, lot_number, qty,
1391 detail_uom,primary_uom,secondary_uom
1392 FROM gmd_material_details_gtmp
1393 WHERE line_id = V_line_id;
1394
1395 CURSOR Cur_line_item_number (V_inventory_item_id NUMBER)IS
1396 SELECT concatenated_segments
1397 FROM mtl_system_items_kfv
1398 WHERE inventory_item_id = V_inventory_item_id;
1399
1400 l_conv_factor NUMBER;
1401 l_mass_qty NUMBER;
1402 l_primary_qty NUMBER;
1403 l_vol_qty NUMBER;
1404 l_item_no VARCHAR2(1000);
1405 l_error NUMBER(5) := 0;
1406 l_rec Cur_line_qty%ROWTYPE;
1407 LINE_NOT_FOUND EXCEPTION;
1408 BEGIN
1409 X_return_status := FND_API.g_ret_sts_success;
1410
1411 l_conv_factor := get_density_value (V_line_id => V_line_id,
1412 V_density_parameter => V_density_parameter);
1413 OPEN Cur_line_qty;
1414 FETCH Cur_line_qty INTO l_rec;
1415 IF Cur_line_qty%NOTFOUND THEN
1416 CLOSE Cur_line_qty;
1417 RAISE LINE_NOT_FOUND;
1418 END IF;
1419 CLOSE Cur_line_qty;
1420
1421 OPEN Cur_line_item_number(l_rec.inventory_item_id);
1422 FETCH Cur_line_item_number INTO l_item_no;
1423 CLOSE Cur_line_item_number;
1424
1425
1426 IF l_rec.detail_uom <> V_mass_uom THEN
1427 l_mass_qty := gmd_labuom_calculate_pkg.uom_conversion (pitem_id => l_rec.inventory_item_id,
1428 pformula_id => 0,
1429 plot_number => l_rec.lot_number,
1430 pcur_qty => l_rec.qty,
1431 pcur_uom => l_rec.detail_uom,
1432 pnew_uom => V_mass_uom,
1433 patomic => 0,
1434 plab_id => V_orgn_id,
1435 pcnv_factor => l_conv_factor);
1436 IF l_mass_qty < 0 THEN
1437 l_error := 1;
1438 l_mass_qty := NULL;
1439 --ADD in bug15886166
1440 gmd_api_grp.log_message('BAD_UOMCV', 'ITEM_NO',l_item_no, 'UOM',V_mass_uom);
1441 END IF;
1442 ELSE
1443 l_mass_qty := l_rec.qty;
1444 END IF;
1445
1446 IF l_rec.detail_uom <> V_vol_uom THEN
1447 l_vol_qty := gmd_labuom_calculate_pkg.uom_conversion (pitem_id => l_rec.inventory_item_id,
1448 pformula_id => 0,
1449 plot_number => l_rec.lot_number,
1450 pcur_qty => l_rec.qty,
1451 pcur_uom => l_rec.detail_uom,
1452 pnew_uom => V_vol_uom,
1453 patomic => 0,
1454 plab_id => V_orgn_id,
1455 pcnv_factor => l_conv_factor);
1456 IF l_vol_qty < 0 THEN
1457 l_error := 1;
1458 l_vol_qty := NULL;
1459 --ADD in bug15886166
1460 gmd_api_grp.log_message('BAD_UOMCV', 'ITEM_NO',l_item_no, 'UOM',V_vol_uom);
1461 END IF;
1462 ELSE
1463 l_vol_qty := l_rec.qty;
1464 END IF;
1465
1466 IF l_rec.detail_uom <> l_rec.primary_uom THEN
1467 l_primary_qty := gmd_labuom_calculate_pkg.uom_conversion (pitem_id => l_rec.inventory_item_id,
1468 pformula_id => 0,
1469 plot_number => l_rec.lot_number,
1470 pcur_qty => l_rec.qty,
1471 pcur_uom => l_rec.detail_uom,
1472 pnew_uom => l_rec.primary_uom,
1473 patomic => 0,
1474 plab_id => V_orgn_id);
1475 IF l_primary_qty < 0 THEN
1476 l_error := 1;
1477 l_primary_qty := NULL;
1478 --ADD in bug15886166
1479 gmd_api_grp.log_message('BAD_UOMCV', 'ITEM_NO',l_item_no, 'UOM',l_rec.primary_uom);
1480 END IF;
1481 ELSE
1482 l_primary_qty := l_rec.qty;
1483 END IF;
1484
1485 UPDATE gmd_material_details_gtmp
1486 SET qty_mass = l_mass_qty,
1487 mass_uom = V_mass_uom,
1488 qty_vol = l_vol_qty,
1489 vol_uom = V_vol_uom,
1490 primary_qty = l_primary_qty,
1491 primary_uom = l_rec.primary_uom
1492 WHERE line_id = V_line_id;
1493
1494
1495
1496 IF l_error = 1 THEN
1497 X_return_status := FND_API.g_ret_sts_error;
1498 -- comment in bug15886166
1499 --gmd_api_grp.log_message('LM_BAD_UOMCV', 'ITEM_NO',l_item_no, 'DENSITY',V_density_parameter);
1500 END IF;
1501
1502 EXCEPTION
1503 WHEN line_not_found THEN
1504 X_return_status := FND_API.g_ret_sts_error;
1505 WHEN OTHERS THEN
1506 fnd_msg_pub.add_exc_msg ('GMD_SPREAD_FETCH_PKG', 'Update_Line_Mass_Vol_Qty');
1507 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1508 END update_line_mass_vol_qty;
1509
1510 /*##############################################################
1511 # NAME
1512 # update_line_mass_qty
1513 # SYNOPSIS
1514 # proc update_line_mass_qty
1515 # DESCRIPTION
1516 # This procedure calculates the qtys to mass and volume.
1517 ###############################################################*/
1518
1519 PROCEDURE update_mass_vol_qty (V_orgn_id IN NUMBER,
1520 V_entity_id IN NUMBER,
1521 V_density_parameter IN VARCHAR2,
1522 V_mass_uom IN VARCHAR2,
1523 V_vol_uom IN VARCHAR2,
1524 X_return_status OUT NOCOPY VARCHAR2) IS
1525 CURSOR Cur_get_lines IS
1526 SELECT line_id
1527 FROM gmd_material_details_gtmp
1528 WHERE rollup_ind = 1
1529 AND line_type <> 1
1530 AND entity_id = V_entity_id;
1531
1532 l_return_status VARCHAR2(1);
1533 BEGIN
1534 X_return_status := FND_API.g_ret_sts_success;
1535
1536 FOR l_rec IN Cur_get_lines LOOP
1537 l_return_status := FND_API.g_ret_sts_success;
1538 update_line_mass_vol_qty (V_orgn_id => V_orgn_id,
1539 V_line_id => l_rec.line_id,
1540 V_density_parameter => V_density_parameter,
1541 V_mass_uom => V_mass_uom,
1542 V_vol_uom => V_vol_uom,
1543 X_return_status => l_return_status);
1544 IF l_return_status <> FND_API.g_ret_sts_success THEN
1545 X_return_status := l_return_status;
1546 END IF;
1547 END LOOP;
1548 END update_mass_vol_qty;
1549
1550 /*##############################################################
1551 # NAME
1552 # load_quality_data
1553 # SYNOPSIS
1554 # proc load_quality_data
1555 # DESCRIPTION
1556 # This procedure inserts the data into temp tables from quality
1557 # tables.
1558 ###############################################################*/
1559
1560 PROCEDURE load_quality_data (V_line_id IN NUMBER, V_orgn_id IN NUMBER,V_plant_id IN NUMBER) IS
1561
1562 CURSOR Cur_get_qmdata IS
1563 SELECT *
1564 FROM gmd_technical_parameter_gtmp
1565 WHERE qcassy_typ_id IS NOT NULL;
1566
1567 CURSOR Cur_get_data IS
1568 SELECT *
1569 FROM gmd_material_details_gtmp
1570 WHERE line_id = V_line_id;
1571
1572 l_rec Cur_get_data%ROWTYPE;
1573 l_return_status VARCHAR2(1);
1574 l_value VARCHAR2(80);
1575 l_inv_inp_rec_type GMD_QUALITY_GRP.inv_inp_rec_type;
1576 l_inv_val_out_rec_type GMD_QUALITY_GRP.inv_val_out_rec_type;
1577
1578 CURSOR Cur_get_value(Pline_id NUMBER,Pparm_id NUMBER) IS
1579 SELECT value
1580 FROM gmd_technical_data_gtmp
1581 WHERE line_id = Pline_id
1582 AND tech_parm_id = Pparm_id;
1583
1584 l_temp VARCHAR2(80);
1585 l_num_value NUMBER;
1586 l_char_value VARCHAR2(240);
1587 X_return_status VARCHAR2(20);
1588 BEGIN
1589 OPEN Cur_get_data;
1590 FETCH Cur_get_data INTO l_rec;
1591 CLOSE Cur_get_data;
1592 l_inv_inp_rec_type.organization_id := V_orgn_id;
1593 l_inv_inp_rec_type.inventory_item_id := l_rec.inventory_item_id;
1594 l_inv_inp_rec_type.grade_code := l_rec.grade_code;
1595 l_inv_inp_rec_type.lot_number := l_rec.lot_number;
1596 l_inv_inp_rec_type.subinventory := l_rec.subinventory_code;
1597 l_inv_inp_rec_type.locator_id := l_rec.locator_id;
1598 l_inv_inp_rec_type.plant_id := V_plant_id;
1599 FOR l_qmrec IN Cur_get_qmdata LOOP
1600 l_inv_inp_rec_type.test_id := l_qmrec.qcassy_typ_id;
1601 gmd_quality_grp.get_inv_test_value (P_inv_test_inp_rec => l_inv_inp_rec_type,
1602 x_inv_test_out_rec => l_inv_val_out_rec_type,
1603 x_return_status => l_return_status);
1604 l_value := l_inv_val_out_rec_type.entity_value;
1605 IF (l_rec.line_type = 3) THEN
1606 IF (l_inv_val_out_rec_type.level BETWEEN 11 AND 20) OR (l_inv_val_out_rec_type.level > 40) THEN
1607 OPEN Cur_get_value(V_line_id,l_qmrec.tech_parm_id);
1608 FETCH Cur_get_value INTO l_temp;
1609 IF (Cur_get_value%FOUND) THEN
1610 l_value := NULL;
1611 END IF;
1612 CLOSE Cur_get_value;
1613 END IF;
1614 END IF;
1615 IF (l_value IS NOT NULL) THEN
1616 IF l_qmrec.data_type = 1 THEN
1617 l_num_value := l_value;
1618 l_char_value := NULL;
1619 ELSE
1620 l_char_value := l_value;
1621 l_num_value := NULL;
1622 END IF;
1623 UPDATE gmd_technical_data_gtmp
1624 SET value = l_inv_val_out_rec_type.entity_value,
1625 num_data = l_num_value,
1626 qm_entity_id = l_inv_val_out_rec_type.entity_id,
1627 qm_level = l_inv_val_out_rec_type.level,
1628 text_data = l_char_value
1629 WHERE line_id = V_line_id
1630 AND tech_parm_id = l_qmrec.tech_parm_id;
1631 IF SQL%NOTFOUND THEN
1632 INSERT INTO GMD_TECHNICAL_DATA_GTMP
1633 (ENTITY_ID,LINE_ID,TECH_PARM_NAME,TECH_PARM_ID,VALUE,SORT_SEQ,NUM_DATA,
1634 TEXT_DATA,QM_ENTITY_ID,QM_LEVEL,COMP_IND,MIN_VALUE,MAX_VALUE,SPEC_ID)
1635 VALUES (l_rec.entity_id,V_line_id,l_qmrec.tech_parm_name,l_qmrec.tech_parm_id,l_inv_val_out_rec_type.entity_value,
1636 l_qmrec.sort_seq,l_num_value,l_char_value,
1637 l_inv_val_out_rec_type.entity_id,l_inv_val_out_rec_type.level,l_inv_val_out_rec_type.composite_ind,
1638 l_inv_val_out_rec_type.entity_min_value,l_inv_val_out_rec_type.entity_max_value,l_inv_val_out_rec_type.spec_id);
1639 END IF;
1640 ELSE
1641 UPDATE gmd_technical_data_gtmp
1642 SET value = l_inv_val_out_rec_type.entity_value,
1643 num_data = l_num_value,
1644 qm_entity_id = l_inv_val_out_rec_type.entity_id,
1645 qm_level = l_inv_val_out_rec_type.level,
1646 text_data = l_char_value
1647 WHERE line_id = V_line_id
1648 AND tech_parm_id = l_qmrec.tech_parm_id;
1649 END IF;
1650 END LOOP;
1651 EXCEPTION
1652 WHEN OTHERS THEN
1653 fnd_msg_pub.add_exc_msg ('GMD_SPREAD_FETCH_PKG', 'load_quality_data');
1654 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1655 NULL;
1656 END load_quality_data;
1657
1658 /**#############################################################
1659 # NAME
1660 # get_lot_density
1661 # SYNOPSIS
1662 # proc get_lot_density
1663 # DESCRIPTION
1664 #
1665 # HISTORY
1666 # Sriram.S 30Aug04 Created
1667 # CAF Enhancement
1668 ##############################################################**/
1669
1670 PROCEDURE get_lot_density (P_orgn_id NUMBER,
1671 P_parent_detl_id NUMBER,
1672 P_entity_id NUMBER) IS
1673
1674 CURSOR cur_get_param (v_density VARCHAR2) IS
1675 SELECT tech_parm_id
1676 FROM gmd_tech_parameters_b
1677 WHERE tech_parm_name = v_density
1678 AND organization_id = P_orgn_id;
1679
1680 CURSOR cur_get_value (v_tech_parm_id NUMBER) IS
1681 SELECT a.value
1682 FROM gmd_technical_data_gtmp a, gmd_material_details_gtmp c
1683 WHERE a.tech_parm_id = v_tech_parm_id
1684 AND c.parent_line_id = P_parent_detl_id
1685 AND a.line_id = c.line_id
1686 AND c.line_type <> 3;
1687
1688 CURSOR cur_get_data (v_tech_parm_id NUMBER) IS
1689 SELECT line_id
1690 FROM gmd_material_details_gtmp p
1691 WHERE parent_line_id = P_parent_detl_id
1692 AND line_type = 3
1693 AND NOT EXISTS ( SELECT 1
1694 FROM gmd_technical_data_gtmp g
1695 WHERE p.line_id = g.line_id
1696 AND g.tech_parm_id = v_tech_parm_id);
1697
1698 /* Local variables */
1699 l_density VARCHAR2 (240);
1700 l_value NUMBER;
1701 l_tech_parm_id NUMBER;
1702
1703 BEGIN
1704 l_density := fnd_profile.VALUE ('LM$DENSITY');
1705
1706 OPEN cur_get_param (l_density);
1707 FETCH cur_get_param INTO l_tech_parm_id;
1708 CLOSE cur_get_param;
1709
1710 OPEN cur_get_value (l_tech_parm_id);
1711 FETCH cur_get_value INTO l_value;
1712 CLOSE cur_get_value;
1713
1714 IF (l_value IS NOT NULL) THEN
1715 FOR l_rec IN cur_get_data (l_tech_parm_id) LOOP
1716 INSERT INTO gmd_technical_data_gtmp
1717 (entity_id, line_id, tech_parm_name, tech_parm_id,
1718 Value, sort_seq, num_data, TEXT_DATA, BOOLEAN_DATA)
1719 VALUES
1720 (P_entity_id, l_rec.line_id, l_density, l_tech_parm_id,
1721 l_value, 1, l_value, NULL, NULL);
1722 END LOOP;
1723 END IF;
1724 END get_lot_density;
1725
1726 /*##############################################################
1727 # NAME
1728 # load_lcf_details
1729 # SYNOPSIS
1730 # proc load_lcf_details
1731 # DESCRIPTION
1732 # This procedure inserts the data into temp tables and will
1733 # be fetched in the form.
1734 ###############################################################*/
1735
1736 PROCEDURE load_lcf_details (V_entity_id IN NUMBER,
1737 V_orgn_id IN NUMBER,
1738 V_plant_id IN NUMBER) IS
1739 CURSOR Cur_get_lcf IS
1740 SELECT a.*,b.description descrip, b.default_grade,
1741 b.primary_uom_code primary,b.secondary_uom_code secondary,
1742 b.lot_control_code,b.secondary_default_ind,
1743 b.grade_control_flag,b.tracking_quantity_ind,b.location_control_code
1744 FROM gmd_lcf_details_gtmp a, mtl_system_items_b b
1745 WHERE a.inventory_item_id = b.inventory_item_id
1746 AND b.organization_id = V_orgn_id
1747 ORDER BY a.line_type, a.line_no;
1748 l_formula_rec Cur_get_lcf%ROWTYPE;
1749 l_secondary_qty NUMBER;
1750 CURSOR Cur_get_lines IS
1751 SELECT formulaline_id
1752 FROM gmd_material_details_gtmp
1753 WHERE entity_id = V_entity_id;
1754 BEGIN
1755 /* Inserting the item and lot data from formula detail tables to temp tables*/
1756 IF (V_orgn_id IS NOT NULL) THEN
1757 OPEN Cur_get_lcf;
1758 LOOP
1759 FETCH Cur_get_lcf INTO l_formula_rec;
1760 EXIT WHEN Cur_get_lcf%NOTFOUND;
1761 /* Getting the secondary qty*/
1762 l_secondary_qty := null;
1763 IF (l_formula_rec.qty > 0 AND l_formula_rec.tracking_quantity_ind = 'PS') THEN
1764 l_secondary_qty := gmd_labuom_calculate_pkg.uom_conversion (pitem_id => l_formula_rec.inventory_item_id,
1765 pformula_id => NULL,
1766 plot_number => NULL,
1767 pcur_qty => l_formula_rec.qty,
1768 pcur_uom => l_formula_rec.detail_uom,
1769 pnew_uom => l_formula_rec.secondary,
1770 patomic => 0,
1771 plab_id => V_orgn_id);
1772 IF (l_secondary_qty < 0) THEN
1773 l_secondary_qty := NULL;
1774 END IF;
1775 END IF;
1776
1777 INSERT INTO GMD_MATERIAL_DETAILS_GTMP
1778 (ENTITY_ID,LINE_ID,LINE_TYPE,LINE_NO,ROLLUP_IND,EXPAND_IND,TRACKING_QUANTITY_IND,
1779 LOCATION_CONTROL_CODE,INVENTORY_ITEM_ID,DESCRIPTION,
1780 QTY,SECONDARY_QTY,DETAIL_UOM,GRADE_CODE,PRIMARY_UOM,SECONDARY_UOM,LOT_CONTROL_CODE,
1781 GRADE_CONTROL_FLAG,FORMULALINE_ID,PARENT_LINE_ID,
1782 CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,
1783 ORGANIZATION_ID,SECONDARY_DEFAULT_IND)
1784 VALUES
1785 (V_entity_id,l_formula_rec.line_id,l_formula_rec.line_type,l_formula_rec.line_no,1,1,
1786 l_formula_rec.tracking_quantity_ind,l_formula_rec.location_control_code,
1787 l_formula_rec.inventory_item_id,l_formula_rec.descrip,
1788 l_formula_rec.qty,l_secondary_qty,l_formula_rec.detail_uom,
1789 l_formula_rec.default_grade,l_formula_rec.primary,l_formula_rec.secondary,
1790 l_formula_rec.lot_control_code,l_formula_rec.grade_control_flag,
1791 l_formula_rec.formulaline_id,l_formula_rec.line_id,l_formula_rec.created_by,
1792 l_formula_rec.creation_date,l_formula_rec.last_updated_by,
1793 l_formula_rec.last_update_date,V_orgn_id,l_formula_rec.secondary_default_ind);
1794
1795 /* Changed l_formula_rec.line_id to l_formula_rec.formulaline_id in the Bug No.8439868 */
1796
1797 END LOOP;
1798 CLOSE Cur_get_lcf;
1799
1800 FOR l_form_rec IN Cur_get_lines LOOP
1801 gmd_spread_fetch_pkg.load_lcf_values(V_entity_id => V_entity_id,
1802 V_orgn_id => V_orgn_id,
1803 V_formulaline_id => l_form_rec.formulaline_id,
1804 V_plant_id => V_plant_id);
1805 END LOOP;
1806 END IF;
1807 EXCEPTION
1808 WHEN OTHERS THEN
1809 fnd_msg_pub.add_exc_msg ('GMD_SPREAD_FETCH_PKG', 'Load_Lcf_Details');
1810 END load_lcf_details;
1811
1812 /*##############################################################
1813 # NAME
1814 # load_lcf_values
1815 # SYNOPSIS
1816 # proc load_lcf_values
1817 # DESCRIPTION
1818 # This procedure inserts the data into temp tables and will
1819 # be fetched in the form.
1820 ###############################################################*/
1821
1822 PROCEDURE load_lcf_values (V_entity_id IN NUMBER,V_orgn_id IN NUMBER,
1823 V_formulaline_id IN NUMBER,V_plant_id IN NUMBER,
1824 V_line_id IN NUMBER) IS
1825 CURSOR Cur_get_line IS
1826 SELECT line_id
1827 FROM gmd_material_details_gtmp
1828 WHERE (V_formulaline_id IS NULL OR parent_line_id = V_formulaline_id)
1829 ORDER BY line_type;
1830 BEGIN
1831 /* Inserting the technical parameter data of item and lot to temp tables*/
1832 IF (V_entity_id IS NOT NULL) THEN
1833 INSERT INTO GMD_TECHNICAL_DATA_GTMP
1834 (ENTITY_ID,LINE_ID,TECH_PARM_NAME,TECH_PARM_ID,
1835 VALUE,SORT_SEQ,NUM_DATA,TEXT_DATA,BOOLEAN_DATA)
1836 SELECT V_entity_id,c.line_id,b.tech_parm_name,a.tech_parm_id,
1837 DECODE(B.DATA_TYPE,0,TEXT_DATA,2,TEXT_DATA,3,BOOLEAN_DATA,NUM_DATA) VALUE,
1838 b.sort_seq,a.num_data,a.text_data,a.boolean_data
1839 FROM gmd_technical_data_vl a, gmd_technical_parameter_gtmp b, gmd_material_details_gtmp c
1840 WHERE a.tech_parm_id = b.tech_parm_id
1841 AND a.organization_id = V_orgn_id
1842 AND a.inventory_item_id = c.inventory_item_id
1843 AND (V_formulaline_id IS NULL OR c.parent_line_id = V_formulaline_id)
1844 AND c.entity_id = V_entity_id
1845 AND (V_line_id IS NULL OR c.line_id = V_line_id)
1846 AND NVL(c.organization_id, -1) = NVL(a.lot_organization_id, c.organization_id)
1847 AND NVL(c.lot_number, '-1') = NVL(a.lot_number, '-1');
1848 END IF;
1849 FOR l_quality_rec IN Cur_get_line LOOP
1850 load_derived_cost (V_entity_id,V_orgn_id,l_quality_rec.line_id);
1851 load_quality_data (l_quality_rec.line_id,V_orgn_id,V_plant_id);
1852 END LOOP;
1853 EXCEPTION
1854 WHEN OTHERS THEN
1855 fnd_msg_pub.add_exc_msg ('GMD_SPREAD_FETCH_PKG', 'Load_Lcf_Values');
1856 END load_lcf_values;
1857
1858 /*##############################################################
1859 # NAME
1860 # load_derived_cost
1861 # SYNOPSIS
1862 # proc load_derived_cost
1863 # DESCRIPTION
1864 # This procedure inserts the data into temp tables and will
1865 # be fetched in the form.
1866 ###############################################################*/
1867
1868 PROCEDURE load_derived_cost (V_entity_id IN NUMBER,V_orgn_id IN NUMBER,V_line_id IN NUMBER) IS
1869 CURSOR Cur_get_type IS
1870 SELECT a.*, c.line_id line, b.tech_parm_id tech, b.tech_parm_name name,b.sort_seq,
1871 c.inventory_item_id,c.detail_uom
1872 FROM gmd_tech_parameters_b a, gmd_technical_parameter_gtmp b, gmd_material_details_gtmp c
1873 WHERE a.tech_parm_id = b.tech_parm_id
1874 AND c.line_id = V_line_id;
1875 CURSOR Cur_get_item (V_inv_item_id NUMBER) IS
1876 SELECT primary_uom_code
1877 FROM mtl_system_items_b
1878 WHERE inventory_item_id = V_inv_item_id
1879 AND organization_id = V_orgn_id;
1880 l_value NUMBER;
1881 l_parm_value VARCHAR2(240);
1882 l_return_status VARCHAR2(1);
1883 l_uom VARCHAR2(3);
1884 l_qty NUMBER;
1885 BEGIN
1886 gmd_api_grp.fetch_parm_values (P_orgn_id => V_orgn_id
1887 ,P_parm_name => 'GMD_COST_SOURCE_ORGN'
1888 ,P_parm_value => l_parm_value
1889 ,X_return_status => l_return_status);
1890 FOR l_rec IN Cur_get_type LOOP
1891 IF l_rec.data_type = 12 THEN
1892 l_value := 0;
1893 l_qty := 0;
1894 gmd_lcf_fetch_pkg.load_cost_values (V_orgn_id => V_orgn_id,
1895 V_inv_item_id => l_rec.inventory_item_id,
1896 V_cost_type => l_rec.cost_type,
1897 V_date => SYSDATE,
1898 V_cost_orgn => l_parm_value,
1899 V_source => l_rec.cost_source,
1900 X_value => l_value);
1901 OPEN Cur_get_item(l_rec.inventory_item_id);
1902 FETCH Cur_get_item INTO l_uom;
1903 CLOSE Cur_get_item;
1904 l_qty := gmd_labuom_calculate_pkg.uom_conversion
1905 (pitem_id => l_rec.inventory_item_id,
1906 pformula_id => V_entity_id,
1907 plot_number => NULL,
1908 pcur_qty => 1,
1909 pcur_uom => l_rec.detail_uom,
1910 pnew_uom => l_uom,
1911 patomic => 0,
1912 plab_id => V_orgn_id);
1913 IF (l_qty > 0 AND l_value > 0) THEN
1914 l_value := (l_value * l_qty);
1915 END IF;
1916 INSERT INTO GMD_TECHNICAL_DATA_GTMP (ENTITY_ID,LINE_ID,TECH_PARM_NAME,
1917 TECH_PARM_ID,VALUE,NUM_DATA,SORT_SEQ)
1918 VALUES (V_entity_id,l_rec.line,l_rec.name,l_rec.tech,l_value,l_value,l_rec.sort_seq);
1919 END IF;
1920 END LOOP;
1921 END load_derived_cost;
1922
1923
1924 /* Bug#7254259 (add procedure ,update_formula_details ,Update_Formula_Values ) begin */
1925 PROCEDURE update_formula_details (V_entity_id IN NUMBER, V_line_id IN NUMBER, V_formula_id IN NUMBER,
1926 V_line_type IN NUMBER, V_line_no IN NUMBER,V_orgn_id IN number,V_plant_id IN number) IS
1927 /* CURSOR Cur_get_formula IS
1928 SELECT a.*,b.item_no,b.item_desc1,b.qc_grade,b.item_um primary,b.item_um2 secondary,
1929 b.lot_ctl,b.sublot_ctl,b.grade_ctl,b.loct_ctl,b.dualum_ind
1930 FROM fm_matl_dtl a, ic_item_mst b
1931 WHERE a.item_id = b.item_id
1932 AND a.formula_id = V_formula_id
1933 AND a.line_type = 1
1934 AND a.line_no = V_line_no; */
1935
1936 CURSOR Cur_get_formula IS
1937 SELECT a.*,b.segment1 original_item_id, b.primary_uom_code primary,b.secondary_uom_code secondary,b.dual_uom_control dualum_ind
1938 FROM fm_matl_dtl a, mtl_system_items_b b
1939 WHERE a.inventory_item_id = b.inventory_item_id
1940 and b.organization_id = V_orgn_id
1941 AND a.formula_id = V_formula_id
1942 AND a.line_type = 1
1943 AND a.line_no = V_line_no;
1944
1945
1946 /* CURSOR Cur_get_materials IS
1947 SELECT a.*,b.lot_no lot, b.sublot_no sublot, b.lot_id item_lotid, b.expire_date expire, b.item_id itemid
1948 FROM gmd_material_details_gtmp a, ic_lots_mst b, (select item_id,lot_id from gmd_technical_data_vl group by item_id,lot_id) c
1949 WHERE a.entity_id = V_formula_id
1950 AND a.item_id = b.item_id
1951 AND a.item_id = c.item_id
1952 AND b.lot_id = c.lot_id
1953 AND a.lot_ctl = 1
1954 AND b.lot_id <> 0
1955 AND (a.line_type <> 1)
1956 ORDER BY a.formulaline_id,b.lot_no; */
1957
1958
1959 CURSOR Cur_get_materials IS
1960 SELECT a.*,b.PARENT_LOT_NUMBER lot, b.LOT_NUMBER sublot, NULL item_lotid, b.EXPIRATION_DATE expire, b.inventory_item_id itemid
1961 FROM gmd_material_details_gtmp a, MTL_LOT_NUMBERS b,
1962 (select inventory_item_id,lot_NUMBER from gmd_technical_data_vl group by inventory_item_id,lot_NUMBER) c
1963 WHERE a.entity_id = V_formula_id
1964 AND a.inventory_item_id = b.inventory_item_id
1965 AND a.inventory_item_id = c.inventory_item_id
1966 and b.organization_id = V_orgn_id
1967 AND b.PARENT_LOT_NUMBER = c.LOT_NUMBER
1968 AND a.lot_ctl = 1
1969 AND (a.line_type <> 1)
1970 ORDER BY a.formulaline_id,b.PARENT_LOT_NUMBER;
1971
1972
1973 CURSOR Cur_get_lines IS
1974 SELECT formulaline_id
1975 FROM gmd_material_details_gtmp
1976 WHERE entity_id = V_entity_id
1977 AND line_type <> 3 ;
1978
1979 l_line_id NUMBER DEFAULT 0;
1980 l_matl_rec Cur_get_materials%ROWTYPE;
1981 l_formula_rec Cur_get_formula%ROWTYPE;
1982 l_line_no NUMBER;
1983 l_secondary_qty NUMBER;
1984 l_formulaline_id NUMBER DEFAULT 0;
1985
1986 CURSOR Cur_get_formulaid (V_validity_rule_id NUMBER) IS
1987 SELECT formula_id
1988 FROM gmd_recipes r, gmd_recipe_validity_rules v
1989 WHERE recipe_validity_rule_id = V_validity_rule_id
1990 AND r.recipe_id = v.recipe_id;
1991
1992 l_return_status VARCHAR2(1);
1993 l_msg_data VARCHAR2(2000);
1994 l_msg_count NUMBER(10);
1995 l_return_code NUMBER(10);
1996 l_recipe_validity_out GMD_VALIDITY_RULES.recipe_validity_tbl;
1997 l_rec_count NUMBER(10);
1998 l_tpformula_id NUMBER(10);
1999 fmid number;
2000
2001 BEGIN
2002 /* Updating the item and lot data from formula detail tables to temp tables*/
2003
2004 IF (V_formula_id IS NOT NULL) THEN
2005 OPEN Cur_get_formula;
2006 LOOP
2007 l_line_id := V_line_id;
2008 FETCH Cur_get_formula INTO l_formula_rec;
2009 EXIT WHEN Cur_get_formula%NOTFOUND;
2010
2011 /* Getting the secondary qty*/
2012 l_secondary_qty := null;
2013 IF (l_formula_rec.qty > 0 AND l_formula_rec.dualum_ind <> NULL) THEN
2014 l_secondary_qty := GMICUOM.uom_conversion (pitem_id => l_formula_rec.original_item_id,
2015 plot_id => 0,
2016 pcur_qty => l_formula_rec.qty,
2017 pcur_uom => l_formula_rec.item_um,
2018 pnew_uom => l_formula_rec.secondary,
2019 patomic => 0);
2020 IF (l_secondary_qty < 0) THEN
2021 l_secondary_qty := NULL;
2022 END IF;
2023 END IF;
2024
2025 l_tpformula_id := V_formula_id;
2026
2027 UPDATE GMD_MATERIAL_DETAILS_GTMP
2028 SET TPFORMULA_ID = l_tpformula_id,
2029 FORMULALINE_ID = l_formula_rec.formulaline_id,
2030 PARENT_LINE_ID = l_formula_rec.formulaline_id
2031 WHERE ENTITY_ID = V_entity_id
2032 AND LINE_ID = V_line_id;
2033
2034 fmid := l_formula_rec.formulaline_id;
2035 END LOOP;
2036 CLOSE Cur_get_formula;
2037
2038 OPEN Cur_get_materials;
2039 LOOP
2040 l_line_id :=V_line_id;
2041 FETCH Cur_get_materials INTO l_matl_rec;
2042 EXIT WHEN Cur_get_materials%NOTFOUND;
2043 IF l_formulaline_id <> l_matl_rec.formulaline_id THEN
2044 l_line_no := 1;
2045 l_formulaline_id := l_matl_rec.formulaline_id;
2046 END IF;
2047 IF (l_matl_rec.SECONDARY_DEFAULT_IND <> NULL) THEN
2048 l_secondary_qty := 0;
2049 ELSE
2050 l_secondary_qty := NULL;
2051 END IF;
2052
2053 DELETE FROM GMD_MATERIAL_DETAILS_GTMP
2054 WHERE ENTITY_ID = V_entity_id
2055 AND LINE_ID = V_line_id;
2056
2057 INSERT INTO GMD_MATERIAL_DETAILS_GTMP
2058 (ENTITY_ID,LINE_ID,LINE_TYPE,LINE_NO,ROLLUP_IND,DUALUM_IND,LOCT_CTL,ITEM_ID,EXPAND_IND,EXPIRE_DATE,
2059 LOT_ID,LOT_NO,SUBLOT_NO,QTY,SECONDARY_QTY,ITEM_UM,QC_GRADE,FORMULALINE_ID,PARENT_LINE_ID,
2060 LOT_CTL,SUBLOT_CTL,GRADE_CTL,ACTION_CODE,SECONDARY_UM,
2061 CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,
2062 SECONDARY_DEFAULT_IND,TRACKING_QUANTITY_IND)
2063 VALUES(l_matl_rec.entity_id,l_line_id,3,l_line_no,0,l_matl_rec.dualum_ind,l_matl_rec.loct_ctl,
2064 l_matl_rec.item_id,0,l_matl_rec.expire,l_matl_rec.item_lotid,l_matl_rec.lot,l_matl_rec.sublot,0,
2065 l_secondary_qty,l_matl_rec.item_um,l_matl_rec.qc_grade,l_matl_rec.formulaline_id,l_matl_rec.formulaline_id,
2066 l_matl_rec.lot_ctl,l_matl_rec.sublot_ctl,l_matl_rec.grade_ctl,'NONE',l_matl_rec.secondary_um,
2067 l_matl_rec.created_by,l_matl_rec.creation_date,l_matl_rec.last_updated_by,l_matl_rec.last_update_date,
2068 l_matl_rec.SECONDARY_DEFAULT_IND,l_matl_rec.TRACKING_QUANTITY_IND);
2069 l_line_no := l_line_no + 1;
2070 END LOOP;
2071 CLOSE Cur_get_materials;
2072
2073 gmd_spread_fetch_pkg.Update_formula_values(V_entity_id => V_entity_id,
2074 V_formula_id => V_formula_id,
2075 V_orgn_id => V_orgn_id,
2076 V_formulaline_id => fmid,
2077 V_line_id => V_line_id,
2078 V_item_id => null,
2079 V_plant_id => V_plant_id);
2080
2081 END IF;
2082
2083 EXCEPTION
2084 WHEN OTHERS THEN
2085 fnd_msg_pub.add_exc_msg ('GMD_SPREAD_FETCH_PKG', 'Update_Formula_Details');
2086 END update_formula_details;
2087
2088
2089 PROCEDURE Update_Formula_Values (V_entity_id IN NUMBER,V_formula_id IN NUMBER,
2090 V_orgn_id IN number,V_formulaline_id IN NUMBER,
2091 V_line_id IN NUMBER,V_item_id IN NUMBER,V_plant_id IN number) IS
2092 CURSOR Cur_get_line IS
2093 SELECT line_id
2094 FROM gmd_material_details_gtmp
2095 WHERE (V_formulaline_id IS NULL OR parent_line_id = V_formulaline_id)
2096 ORDER BY line_type;
2097
2098
2099 BEGIN
2100 DELETE FROM GMD_TECHNICAL_DATA_GTMP
2101 WHERE ENTITY_ID = V_entity_id
2102 AND LINE_ID = V_line_id;
2103
2104 /* Updating the technical parameter data of item and lot to temp tables*/
2105 IF (V_formula_id IS NOT NULL) THEN
2106
2107 /* INSERT INTO GMD_TECHNICAL_DATA_GTMP
2108 (ENTITY_ID,LINE_ID,TECH_PARM_NAME,TECH_PARM_ID,
2109 VALUE,SORT_SEQ,NUM_DATA,TEXT_DATA,BOOLEAN_DATA)
2110 SELECT V_entity_id,c.line_id,a.tech_parm_name,a.tech_parm_id,
2111 DECODE(B.DATA_TYPE,0,TEXT_DATA,2,TEXT_DATA,3,BOOLEAN_DATA,NUM_DATA) VALUE,
2112 b.sort_seq,a.num_data,a.text_data,a.boolean_data
2113 FROM gmd_technical_data_vl a, gmd_technical_parameter_gtmp b, gmd_material_details_gtmp c
2114 WHERE a.tech_parm_id = b.tech_parm_id
2115 AND a.organization_id = V_orgn_id
2116 AND a.item_id = c.item_id
2117 AND (V_formulaline_id IS NULL OR c.parent_line_id = V_formulaline_id)
2118 AND c.entity_id = V_entity_id
2119 AND (V_line_id IS NULL OR c.line_id = V_line_id)
2120 AND (a.formula_id = c.tpformula_id OR
2121 (a.formula_id IS NULL AND NOT EXISTS ( SELECT 1
2122 FROM GMD_TECHNICAL_DATA_VL e
2123 WHERE e.item_id = c.item_id
2124 AND nvl(e.lot_id, -1) = nvl(c.lot_id, -1)
2125 AND e.batch_id IS NULL
2126 AND e.formula_id = c.tpformula_id
2127 AND e.organization_id = V_orgn_id)))
2128
2129 AND a.batch_id IS NULL
2130 AND NVL(c.lot_id,-1) = NVL(a.lot_id,-1); */
2131 INSERT INTO GMD_TECHNICAL_DATA_GTMP
2132 (ENTITY_ID,LINE_ID,TECH_PARM_NAME,TECH_PARM_ID,
2133 VALUE,SORT_SEQ,NUM_DATA,TEXT_DATA,BOOLEAN_DATA)
2134 SELECT V_entity_id,c.line_id,a.tech_parm_name,a.tech_parm_id,
2135 DECODE(B.DATA_TYPE,0,TEXT_DATA,2,TEXT_DATA,3,BOOLEAN_DATA,NUM_DATA) VALUE,
2136 b.sort_seq,a.num_data,a.text_data,a.boolean_data
2137 FROM gmd_technical_data_vl a, gmd_technical_parameter_gtmp b, gmd_material_details_gtmp c
2138 WHERE a.tech_parm_id = b.tech_parm_id
2139 AND a.organization_id = V_orgn_id
2140 AND a.inventory_item_id = c.inventory_item_id
2141 and c.organization_id = V_orgn_id
2142 AND (V_formulaline_id IS NULL OR c.parent_line_id = V_formulaline_id)
2143 AND c.entity_id = V_entity_id
2144 AND (V_line_id IS NULL OR c.line_id = V_line_id)
2145 AND (a.formula_id = c.tpformula_id OR
2146 (a.formula_id IS NULL AND NOT EXISTS ( SELECT 1
2147 FROM GMD_TECHNICAL_DATA_VL e
2148 WHERE e.inventory_item_id = c.inventory_item_id
2149 AND nvl(e.lot_id, -1) = nvl(c.lot_id, -1)
2150 AND e.batch_id IS NULL
2151 AND e.formula_id = c.tpformula_id
2152 AND e.organization_id = V_orgn_id)))
2153
2154 AND a.batch_id IS NULL
2155 AND NVL(c.lot_id,-1) = NVL(a.lot_id,-1);
2156
2157 gmd_spread_fetch_pkg.get_lot_density (P_orgn_id => V_orgn_id,
2158 P_parent_detl_id => V_formulaline_id,
2159 P_entity_id => V_entity_id);
2160
2161 ELSE
2162 INSERT INTO GMD_TECHNICAL_DATA_GTMP
2163 (ENTITY_ID,LINE_ID,TECH_PARM_NAME,TECH_PARM_ID,
2164 VALUE,SORT_SEQ,NUM_DATA,TEXT_DATA,BOOLEAN_DATA)
2165 SELECT V_entity_id,V_line_id,a.tech_parm_name,a.tech_parm_id,
2166 DECODE(B.DATA_TYPE,0,TEXT_DATA,2,TEXT_DATA,3,BOOLEAN_DATA,NUM_DATA) VALUE,
2167 b.sort_seq,a.num_data,a.text_data,a.boolean_data
2168 FROM gmd_technical_data_vl a, gmd_technical_parameter_gtmp b--, gmd_material_details_gtmp c
2169 WHERE a.tech_parm_id = b.tech_parm_id
2170 AND a.organization_id = V_orgn_id ---1
2171 AND a.inventory_item_id = V_item_id
2172 AND a.formula_id is null
2173 AND a.batch_id IS NULL;
2174 END IF;
2175 FOR l_quality_rec IN Cur_get_line LOOP
2176 load_quality_data (l_quality_rec.line_id,V_orgn_id,V_plant_id);
2177 END LOOP;
2178
2179 EXCEPTION
2180 WHEN OTHERS THEN
2181 fnd_msg_pub.add_exc_msg ('GMD_SPREAD_FETCH_PKG', 'Update_Formula_Values');
2182 END Update_Formula_Values;
2183 /* Bug#7254259 (add procedure ,update_formula_details ,Update_Formula_Values ) End */
2184
2185
2186 END GMD_SPREAD_FETCH_PKG;