[Home] [Help]
PACKAGE BODY: APPS.GMD_SPREAD_FETCH_PKG
Source
1 PACKAGE BODY GMD_SPREAD_FETCH_PKG AS
2 /* $Header: GMDSPDFB.pls 120.13.12010000.2 2008/10/23 07:50:13 kannavar 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
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)
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.primary,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);
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 a.lot_control_code = 2
516 AND (a.line_type <> 1)
517 ORDER BY a.formulaline_id,b.lot_number;
518
519 CURSOR Cur_get_lines IS
520 SELECT formulaline_id
521 FROM gmd_material_details_gtmp
522 WHERE entity_id = V_entity_id
523 AND line_type <> 3 ;
524
525 l_line_id NUMBER DEFAULT 0;
526 l_matl_rec Cur_get_materials%ROWTYPE;
527 l_formula_rec Cur_get_formula%ROWTYPE;
528 l_line_no NUMBER;
529 l_secondary_qty NUMBER;
530 l_formulaline_id NUMBER DEFAULT 0;
531
532 CURSOR Cur_get_formulaid (V_validity_rule_id NUMBER) IS
533 SELECT formula_id
534 FROM gmd_recipes r, gmd_recipe_validity_rules v
535 WHERE recipe_validity_rule_id = V_validity_rule_id
536 AND r.recipe_id = v.recipe_id;
537
538 l_return_status VARCHAR2(1);
539 l_msg_data VARCHAR2(2000);
540 l_msg_count NUMBER(10);
541 l_return_code NUMBER(10);
542 l_recipe_validity_out GMD_VALIDITY_RULES.recipe_validity_tbl;
543 l_rec_count NUMBER(10);
544 l_tpformula_id NUMBER(10);
545
546 BEGIN
547 /* Inserting the item and lot data from formula detail tables to temp tables*/
548 IF (V_formula_id IS NOT NULL) THEN
549 OPEN Cur_get_formula;
550 LOOP
551 l_line_id := l_line_id + 1;
552 FETCH Cur_get_formula INTO l_formula_rec;
553 EXIT WHEN Cur_get_formula%NOTFOUND;
554 /* Getting the secondary qty*/
555 l_secondary_qty := null;
556 IF (l_formula_rec.qty > 0 AND l_formula_rec.tracking_quantity_ind = 'PS') THEN
557 l_secondary_qty := gmd_labuom_calculate_pkg.uom_conversion (pitem_id => l_formula_rec.inventory_item_id,
558 pformula_id => NULL,
559 plot_number => NULL,
560 pcur_qty => l_formula_rec.qty,
561 pcur_uom => l_formula_rec.detail_uom,
562 pnew_uom => l_formula_rec.secondary,
563 patomic => 0,
564 plab_id => l_formula_rec.organization_id);
565 IF (l_secondary_qty < 0) THEN
566 l_secondary_qty := NULL;
567 END IF;
568 END IF;
569
570 l_tpformula_id := NULL; /* Added in Bug No.7462584 */
571 IF l_formula_rec.line_type = -1 THEN /* Added in Bug No.7462584 */
572 IF l_formula_rec.tpformula_id IS NULL THEN
573 /*GMD_VAL_DATA_PUB.get_val_data (p_api_version => 1.0
574 ,p_init_msg_list => 'T'
575 ,p_object_type => 'L'
576 ,p_item_id => l_formula_rec.inventory_item_id
577 ,p_product_qty => l_formula_rec.qty
578 ,p_uom => l_formula_rec.detail_uom
579 ,p_organization_id => V_orgn_id
580 ,x_return_status => l_return_status
581 ,x_msg_count => l_msg_count
582 ,x_msg_data => l_msg_data
583 ,x_return_code => l_return_code
584 ,x_recipe_validity_out => l_recipe_validity_out);
585 IF l_return_status = 'S' THEN
586 l_rec_count := l_recipe_validity_out.COUNT;
587 IF l_rec_count > 0 THEN
588 OPEN Cur_get_formulaid (l_recipe_validity_out (l_rec_count).recipe_validity_rule_id);
589 FETCH Cur_get_formulaid INTO l_tpformula_id;
590 CLOSE Cur_get_formulaid;
591 END IF;
592 END IF;
593 ELSE
594 l_tpformula_id := l_formula_rec.tpformula_id;
595 END IF; */
596 BEGIN
597 SELECT a.formula_id INTO l_tpformula_id
598 FROM
599 (SELECT a.formula_id
600 FROM Fm_form_mst a, fm_matl_dtl b, gmd_technical_data_hdr g
601 WHERE b.item_id = l_formula_rec.item_id
602 AND a.formula_id = b.formula_id
603 AND g.item_id = b.item_id
604 AND g.formula_id = a.formula_id
605 AND g.organization_id = V_orgn_id
606 AND a.formula_id <>0
607 AND b.line_type = 1
608 AND a.delete_mark =0
609 AND g.delete_mark =0
610 ORDER BY a.formula_id) a
611 WHERE rownum < 2;
612 EXCEPTION
613 WHEN OTHERS THEN
614 l_tpformula_id := l_formula_rec.tpformula_id;
615 END;
616 ELSE
617 l_tpformula_id := l_formula_rec.tpformula_id;
618 END IF;
619 ELSIF l_formula_rec.line_type = 1 THEN
620 l_tpformula_id := V_formula_id ;
621 END IF; /*Added in Bug No.7462584 */
622
623 INSERT INTO GMD_MATERIAL_DETAILS_GTMP
624 (ENTITY_ID,LINE_ID,LINE_TYPE,LINE_NO,ROLLUP_IND,EXPAND_IND,TRACKING_QUANTITY_IND,
625 LOCATION_CONTROL_CODE,INVENTORY_ITEM_ID,DESCRIPTION,TPFORMULA_ID,
626 QTY,SECONDARY_QTY,DETAIL_UOM,GRADE_CODE,PRIMARY_UOM,SECONDARY_UOM,LOT_CONTROL_CODE,
627 GRADE_CONTROL_FLAG,REVISION,TEXT_CODE,ORGINAL_TEXT_CODE,FORMULALINE_ID,PARENT_LINE_ID,
628 ACTION_CODE,BUFFER_IND,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,
629 REVISION_QTY_CONTROL_CODE,ORGANIZATION_ID,SECONDARY_DEFAULT_IND,PROD_PERCENT)
630 VALUES
631 (l_formula_rec.formula_id,l_line_id,l_formula_rec.line_type,l_formula_rec.line_no,1,1,
632 l_formula_rec.tracking_quantity_ind,l_formula_rec.location_control_code,
633 l_formula_rec.inventory_item_id,l_formula_rec.description,
634 l_tpformula_id,l_formula_rec.qty,l_secondary_qty,l_formula_rec.detail_uom,
635 l_formula_rec.default_grade,l_formula_rec.primary,l_formula_rec.secondary,
636 l_formula_rec.lot_control_code,l_formula_rec.grade_control_flag,
637 l_formula_rec.revision,l_formula_rec.text_code,l_formula_rec.text_code,l_formula_rec.formulaline_id,
638 l_formula_rec.formulaline_id,'NONE',l_formula_rec.buffer_ind,l_formula_rec.created_by,
639 l_formula_rec.creation_date,l_formula_rec.last_updated_by,
640 l_formula_rec.last_update_date,l_formula_rec.revision_qty_control_code,
641 l_formula_rec.organization_id, l_formula_rec.secondary_default_ind,l_formula_rec.prod_percent);
642 END LOOP;
643 CLOSE Cur_get_formula;
644
645 OPEN Cur_get_materials;
646 LOOP
647 l_line_id := l_line_id + 1;
648 FETCH Cur_get_materials INTO l_matl_rec;
649 EXIT WHEN Cur_get_materials%NOTFOUND;
650 IF l_formulaline_id <> l_matl_rec.formulaline_id THEN
651 l_line_no := 1;
652 l_formulaline_id := l_matl_rec.formulaline_id;
653 END IF;
654 IF (l_matl_rec.tracking_quantity_ind = 'PS') THEN
655 l_secondary_qty := 0;
656 ELSE
657 l_secondary_qty := NULL;
658 END IF;
659
660 INSERT INTO GMD_MATERIAL_DETAILS_GTMP
661 (ENTITY_ID,LINE_ID,LINE_TYPE,LINE_NO,ROLLUP_IND,TRACKING_QUANTITY_IND,LOCATION_CONTROL_CODE,
662 INVENTORY_ITEM_ID,EXPAND_IND,EXPIRATION_DATE,LOT_NUMBER,QTY,SECONDARY_QTY,
663 DETAIL_UOM,GRADE_CODE,FORMULALINE_ID,PARENT_LINE_ID,LOT_CONTROL_CODE,
664 GRADE_CONTROL_FLAG,ACTION_CODE,SECONDARY_UOM,SECONDARY_DEFAULT_IND,
665 CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,ORGANIZATION_ID,PROD_PERCENT)
666 VALUES
667 (l_matl_rec.entity_id,l_line_id,3,l_line_no,0,l_matl_rec.tracking_quantity_ind,
668 l_matl_rec.location_control_code,l_matl_rec.inventory_item_id,0,l_matl_rec.expiration_date,
669 l_matl_rec.lot,0,l_secondary_qty,l_matl_rec.detail_uom,l_matl_rec.grade_code,
670 l_matl_rec.formulaline_id,l_matl_rec.formulaline_id,l_matl_rec.lot_control_code,
671 l_matl_rec.grade_control_flag,'NONE',l_matl_rec.secondary_uom,l_matl_rec.secondary_default_ind,
672 l_matl_rec.created_by,l_matl_rec.creation_date,
673 l_matl_rec.last_updated_by,l_matl_rec.last_update_date,l_matl_rec.organization_id,l_formula_rec.prod_percent);
674 l_line_no := l_line_no + 1;
675 END LOOP;
676 CLOSE Cur_get_materials;
677 FOR l_form_rec IN Cur_get_lines LOOP
678 gmd_spread_fetch_pkg.load_formula_values(V_entity_id => V_entity_id,
679 V_formula_id => V_formula_id,
680 V_orgn_id => V_orgn_id,
681 V_formulaline_id => l_form_rec.formulaline_id,
682 V_plant_id => V_plant_id);
683 END LOOP;
684 END IF;
685 EXCEPTION
686 WHEN OTHERS THEN
687 fnd_msg_pub.add_exc_msg ('GMD_SPREAD_FETCH_PKG', 'Load_Formula_Details');
688 END load_formula_details;
689
690 /*##############################################################
691 # NAME
692 # load_tech_params
693 # SYNOPSIS
694 # proc load_tech_params
695 # DESCRIPTION
696 # This procedure inserts the data into temp tables for tech
697 # parameters .
698 ###############################################################*/
699
700 PROCEDURE load_tech_params (V_entity_id IN NUMBER,V_sprd_id IN NUMBER,V_batch_id IN NUMBER,
701 V_orgn_id IN NUMBER,V_folder_name IN VARCHAR2,
702 V_inv_item_id IN NUMBER,V_formula_id IN NUMBER) IS
703 CURSOR Cur_get_prod IS
704 SELECT inventory_item_id
705 FROM fm_matl_dtl
706 WHERE formula_id = V_entity_id
707 AND line_type = 1
708 AND line_no = 1;
709
710 CURSOR Cur_get_material_prod IS
711 SELECT inventory_item_id
712 FROM gme_material_details
713 WHERE batch_id = V_entity_id
714 AND line_type = 1
715 AND line_no = 1;
716
717 CURSOR Cur_get_lcf_prod IS
718 SELECT inventory_item_id
719 FROM gmd_lcf_details_gtmp
720 WHERE entity_id = V_entity_id
721 AND line_type = 1
722 AND line_no = 1;
723
724 l_item_id NUMBER;
725 l_category_set_id NUMBER;
726
727 CURSOR Cur_get_category IS
728 SELECT category_id
729 FROM mtl_item_categories
730 WHERE category_set_id = l_category_set_id
731 AND inventory_item_id = l_item_id;
732 l_category_id NUMBER;
733
734 CURSOR Cur_get_item_count IS
735 SELECT count(*)
736 FROM gmd_technical_sequence_vl
737 WHERE organization_id = V_orgn_id
738 AND inventory_item_id = l_item_id
739 AND delete_mark = 0;
740
741 CURSOR Cur_get_category_count IS
742 SELECT count(*)
743 FROM gmd_technical_sequence_vl
744 WHERE category_id = l_category_id
745 AND organization_id = V_orgn_id
746 AND delete_mark = 0;
747 l_count NUMBER;
748
749 CURSOR Cur_get_folder_cols IS
750 SELECT c.*, b.sequence
751 FROM fnd_folders a, fnd_folder_columns b, lm_tech_hdr c
752 WHERE a.folder_id = b.folder_id
753 AND b.item_prompt = c.tech_parm_name
754 AND a.name = V_folder_name
755 AND a.OBJECT = 'SPREAD_DTL_SUB'
756 AND c.organization_id = V_orgn_id;
757
758 l_rec Cur_get_folder_cols%ROWTYPE;
759 l_count11 number;
760 BEGIN
761 DELETE FROM gmd_technical_parameter_gtmp;
762 l_category_set_id := TO_NUMBER (FND_PROFILE.VALUE ('GMD_TECH_CATG_SET'));
763 IF (V_folder_name IS NOT NULL) THEN
764 OPEN Cur_get_folder_cols;
765 LOOP
766 FETCH Cur_get_folder_cols INTO l_rec;
767 EXIT WHEN Cur_get_folder_cols%NOTFOUND;
768 INSERT INTO GMD_TECHNICAL_PARAMETER_GTMP
769 (ENTITY_ID,TECH_PARM_NAME,TECH_PARM_ID,PARM_DESCRIPTION,SORT_SEQ,QCASSY_TYP_ID,
770 DATA_TYPE,EXPRESSION_CHAR,LM_UNIT_CODE,SIGNIF_FIGURES,LOWERBOUND_NUM,
771 UPPERBOUND_NUM,LOWERBOUND_CHAR,UPPERBOUND_CHAR,MAX_LENGTH)
772 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,
773 l_rec.data_type,l_rec.expression_char,l_rec.lm_unit_code,
774 DECODE(l_rec.data_type, 4, NVL(l_rec.signif_figures, 0 ), 11, NVL(l_rec.signif_figures, 0 ),l_rec.signif_figures ),
775 l_rec.lowerbound_num,l_rec.upperbound_num,l_rec.lowerbound_char,l_rec.upperbound_char,l_rec.max_length);
776 END LOOP;
777 CLOSE Cur_get_folder_cols;
778
779 ELSIF (V_sprd_id IS NOT NULL) THEN
780 INSERT INTO GMD_TECHNICAL_PARAMETER_GTMP
781 (ENTITY_ID,TECH_PARM_NAME,TECH_PARM_ID,PARM_DESCRIPTION,SORT_SEQ,QCASSY_TYP_ID,DATA_TYPE,EXPRESSION_CHAR,
782 LM_UNIT_CODE,SIGNIF_FIGURES,LOWERBOUND_NUM,UPPERBOUND_NUM,OPTIMIZE_TYPE,LOWERBOUND_CHAR,UPPERBOUND_CHAR,MAX_LENGTH)
783 SELECT a.sprd_id,a.tech_parm_name,b.tech_parm_id,b.parm_description,a.sort_seq,b.qcassy_typ_id,
784 b.data_type,b.expression_char,b.lm_unit_code,
785 DECODE(b.data_type, 4, NVL(b.signif_figures, 0 ), 11, NVL(b.signif_figures, 0 ),b.signif_figures ),
786 a.min_value,a.max_value,a.optimize_type,
787 b.lowerbound_char,b.upperbound_char,b.max_length
788 FROM lm_sprd_prm a, lm_tech_hdr b
789 WHERE a.tech_parm_id = b.tech_parm_id
790 AND a.sprd_id = V_entity_id
791 AND a.organization_id = V_orgn_id;
792
793 ELSE
794 IF (V_inv_item_id IS NOT NULL) THEN
795 l_item_id := V_inv_item_id;
796 ELSIF (V_batch_id IS NOT NULL) THEN
797 OPEN Cur_get_material_prod;
798 FETCH Cur_get_material_prod INTO l_item_id;
799 CLOSE Cur_get_material_prod;
800 ELSIF (V_formula_id IS NOT NULL) THEN
801 OPEN Cur_get_prod;
802 FETCH Cur_get_prod INTO l_item_id;
803 CLOSE Cur_get_prod;
804 ELSE
805 OPEN Cur_get_lcf_prod;
806 FETCH Cur_get_lcf_prod INTO l_item_id;
807 CLOSE Cur_get_lcf_prod;
808 END IF;
809
810 --Fetching the category id
811 OPEN Cur_get_category;
812 FETCH Cur_get_category INTO l_category_id;
813 CLOSE Cur_get_category;
814
815 --If item has the parameters then insert
816 OPEN Cur_get_item_count;
817 FETCH Cur_get_item_count INTO l_count;
818 CLOSE Cur_get_item_count;
819 IF (l_count > 0) THEN
820 INSERT INTO GMD_TECHNICAL_PARAMETER_GTMP
821 (ENTITY_ID,TECH_PARM_NAME,TECH_PARM_ID,PARM_DESCRIPTION,SORT_SEQ,QCASSY_TYP_ID,DATA_TYPE,EXPRESSION_CHAR,
822 LM_UNIT_CODE,SIGNIF_FIGURES,LOWERBOUND_NUM,UPPERBOUND_NUM,LOWERBOUND_CHAR,UPPERBOUND_CHAR,MAX_LENGTH)
823 SELECT V_entity_id,a.tech_parm_name,b.tech_parm_id,b.parm_description,a.sort_seq,
824 b.qcassy_typ_id,b.data_type,b.expression_char,b.lm_unit_code,
825 DECODE(b.data_type, 4, NVL(b.signif_figures, 0 ), 11, NVL(b.signif_figures, 0 ),b.signif_figures ),
826 b.lowerbound_num,b.upperbound_num,
827 b.lowerbound_char,b.upperbound_char,b.max_length
828 FROM gmd_technical_sequence_vl a, lm_tech_hdr b
829 WHERE a.tech_parm_id = b.tech_parm_id
830 AND a.inventory_item_id = l_item_id
831 AND a.organization_id = V_orgn_id;
832 ELSE
833 --If item category has the parameters then insert
834 OPEN Cur_get_category_count;
835 FETCH Cur_get_category_count INTO l_count;
836 CLOSE Cur_get_category_count;
837 IF (l_count > 0) THEN
838 INSERT INTO GMD_TECHNICAL_PARAMETER_GTMP
839 (ENTITY_ID,TECH_PARM_NAME,TECH_PARM_ID,PARM_DESCRIPTION,SORT_SEQ,QCASSY_TYP_ID,DATA_TYPE,EXPRESSION_CHAR,
840 LM_UNIT_CODE,SIGNIF_FIGURES,LOWERBOUND_NUM,UPPERBOUND_NUM,LOWERBOUND_CHAR,UPPERBOUND_CHAR,MAX_LENGTH)
841 SELECT V_entity_id,a.tech_parm_name,b.tech_parm_id,b.parm_description,a.sort_seq,
842 b.qcassy_typ_id,b.data_type,b.expression_char,b.lm_unit_code,
843 DECODE(b.data_type, 4, NVL(b.signif_figures, 0 ), 11, NVL(b.signif_figures, 0 ),b.signif_figures ),
844 b.lowerbound_num,b.upperbound_num,
845 b.lowerbound_char,b.upperbound_char,b.max_length
846 FROM gmd_technical_sequence_vl a, lm_tech_hdr b
847 WHERE a.tech_parm_id = b.tech_parm_id
848 AND a.category_id = l_category_id
849 AND a.organization_id = V_orgn_id;
850 ELSE
851 --If organization has the parameters then insert
852 INSERT INTO GMD_TECHNICAL_PARAMETER_GTMP
853 (ENTITY_ID,TECH_PARM_NAME,TECH_PARM_ID,PARM_DESCRIPTION,SORT_SEQ,QCASSY_TYP_ID,DATA_TYPE,EXPRESSION_CHAR,
854 LM_UNIT_CODE,SIGNIF_FIGURES,LOWERBOUND_NUM,UPPERBOUND_NUM,LOWERBOUND_CHAR,UPPERBOUND_CHAR,MAX_LENGTH)
855 SELECT V_entity_id,a.tech_parm_name,b.tech_parm_id,b.parm_description,a.sort_seq,
856 b.qcassy_typ_id,b.data_type,b.expression_char,b.lm_unit_code,
857 DECODE(b.data_type, 4, NVL(b.signif_figures, 0 ), 11, NVL(b.signif_figures, 0 ),b.signif_figures ),
858 b.lowerbound_num,b.upperbound_num,
859 b.lowerbound_char,b.upperbound_char,b.max_length
860 FROM gmd_technical_sequence_vl a, lm_tech_hdr b
861 WHERE a.tech_parm_id = b.tech_parm_id
862 AND a.organization_id = V_orgn_id
863 AND a.inventory_item_id IS NULL
864 AND a.category_id IS NULL;
865 END IF;
866 END IF;
867 END IF;
868 EXCEPTION
869 WHEN OTHERS THEN
870 fnd_msg_pub.add_exc_msg ('GMD_SPREAD_FETCH_PKG', 'Load_Tech_Params');
871 END load_tech_params;
872
873 /*##############################################################
874 # NAME
875 # add_new_line
876 # SYNOPSIS
877 # proc add_new_line
878 # DESCRIPTION
879 # This procedure inserts the data into temp tables and will
880 # be fetched in the form.
881 ###############################################################*/
882
883 PROCEDURE add_new_line (V_entity_id IN NUMBER, V_inv_item_id IN NUMBER, V_line_type IN NUMBER,
884 V_line_no IN NUMBER, V_source_ind IN NUMBER,V_formula_id IN NUMBER,V_move_order_header_id IN NUMBER,
885 V_orgn_id IN NUMBER, X_line_id OUT NOCOPY NUMBER,
886 X_parent_line_id OUT NOCOPY NUMBER,X_move_order_line_id OUT NOCOPY NUMBER,
887 V_plant_id IN NUMBER) IS
888 CURSOR Cur_get_item IS
889 SELECT b.description,b.default_grade,b.secondary_default_ind,
890 b.primary_uom_code,b.secondary_uom_code,b.tracking_quantity_ind,
891 b.lot_control_code,b.grade_control_flag,b.location_control_code,
892 b.revision_qty_control_code,b.mtl_transactions_enabled_flag
893 FROM mtl_system_items_b b
894 WHERE b.inventory_item_id = V_inv_item_id
895 AND b.organization_id = V_orgn_id;
896
897 CURSOR Cur_line_id IS
898 SELECT MAX(line_id)
899 FROM gmd_material_details_gtmp
900 WHERE entity_id = V_entity_id;
901
902 CURSOR Cur_formulaline_id IS
903 SELECT gem5_formulaline_id_s.NEXTVAL
904 FROM dual;
905
906 CURSOR Cur_spreadline_id IS
907 SELECT gem5_sprd_line_id_s.nextval
908 FROM dual;
909
910 CURSOR Cur_get_materials (Pline_id NUMBER) IS
911 SELECT *
912 FROM gmd_material_details_gtmp
913 WHERE line_id = Pline_id;
914
915 CURSOR Cur_get_batch IS
916 SELECT *
917 FROM gme_batch_header
918 WHERE batch_id = V_entity_id;
919
920 l_line_id NUMBER;
921 l_item_rec Cur_get_item%ROWTYPE;
922 l_line_no NUMBER DEFAULT 0;
923 l_parentline_id NUMBER;
924 l_user_id NUMBER;
925 l_return_status VARCHAR2(1);
926 l_msg_count NUMBER;
927 l_msg_data VARCHAR2(2000);
928 X_return_status VARCHAR2(1);
929 l_rec Cur_get_materials%ROWTYPE;
930 l_batch Cur_get_batch%ROWTYPE;
931 l_materials gme_common_pvt.material_details_tab;
932 l_materials_out gme_common_pvt.material_details_tab;
933 l_trolin inv_move_order_pub.trolin_tbl_type;
934
935 create_mo_line_err EXCEPTION;
936 setup_failure EXCEPTION;
937 BEGIN
938 l_user_id := TO_NUMBER (fnd_profile.VALUE ('USER_ID'));
939 IF NOT (gme_common_pvt.setup(P_org_id => V_orgn_id)) THEN
940 RAISE setup_failure;
941 END IF;
942 /* Inserting the item and lot data for the item entered in the form*/
943 OPEN Cur_line_id;
944 FETCH Cur_line_id INTO l_line_id;
945 CLOSE Cur_line_id;
946 IF (V_source_ind = 0) THEN
947 OPEN Cur_formulaline_id;
948 FETCH Cur_formulaline_id INTO l_parentline_id;
949 CLOSE Cur_formulaline_id;
950 ELSE
951 OPEN Cur_spreadline_id;
952 FETCH Cur_spreadline_id INTO l_parentline_id;
953 CLOSE Cur_spreadline_id;
954 END IF;
955 OPEN Cur_get_item;
956 FETCH Cur_get_item INTO l_item_rec;
957 CLOSE Cur_get_item;
958 l_line_id := l_line_id + 1;
959
960 INSERT INTO GMD_MATERIAL_DETAILS_GTMP
961 (ENTITY_ID,LINE_ID,LINE_TYPE,LINE_NO,ROLLUP_IND,INVENTORY_ITEM_ID,
962 DESCRIPTION,EXPAND_IND,QTY,DETAIL_UOM,GRADE_CODE,PRIMARY_UOM,SECONDARY_UOM,TRACKING_QUANTITY_IND,SECONDARY_DEFAULT_IND,
963 LOT_CONTROL_CODE,GRADE_CONTROL_FLAG,LOCATION_CONTROL_CODE,TEXT_CODE,ORGINAL_TEXT_CODE,FORMULALINE_ID,
964 MATERIAL_DETAIL_ID,PARENT_LINE_ID,ACTION_CODE,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,
965 LAST_UPDATE_DATE,REVISION_QTY_CONTROL_CODE,ORGANIZATION_ID)
966 VALUES
967 (V_entity_id,l_line_id,V_line_type,V_line_no,1,V_inv_item_id,
968 l_item_rec.description,1,0,l_item_rec.primary_uom_code,l_item_rec.default_grade,l_item_rec.primary_uom_code,
969 l_item_rec.secondary_uom_code,l_item_rec.tracking_quantity_ind,l_item_rec.secondary_default_ind,
970 l_item_rec.lot_control_code,l_item_rec.grade_control_flag,l_item_rec.location_control_code,
971 NULL,NULL,l_parentline_id,l_parentline_id,l_parentline_id,'NONE',l_user_id,SYSDATE,
972 l_user_id,SYSDATE,l_item_rec.revision_qty_control_code,V_orgn_id);
973
974 X_line_id := l_line_id;
975 X_parent_line_id := l_parentline_id;
976
977 --Create a move order line for SAI form to be called before updating the batch.
978 OPEN Cur_get_batch;
979 FETCH Cur_get_batch INTO l_batch;
980 CLOSE Cur_get_batch;
981 IF (V_line_type = -1 AND l_batch.update_inventory_ind = 'Y'
982 AND l_item_rec.mtl_transactions_enabled_flag = 'Y') THEN
983 IF (V_source_ind = 1) THEN
984 OPEN Cur_get_materials(l_line_id);
985 FETCH Cur_get_materials INTO l_rec;
986 CLOSE Cur_get_materials;
987
988 IF (l_rec.line_type = -1) THEN
989 l_materials(1).material_requirement_date := l_batch.plan_start_date;
990 ELSE
991 l_materials(1).material_requirement_date := l_batch.plan_cmplt_date;
992 END IF;
993
994 l_materials(1).inventory_item_id := V_inv_item_id;
995 l_materials(1).material_detail_id := l_parentline_id;
996 l_materials(1).organization_id := V_orgn_id;
997 l_materials(1).plan_qty := 0;
998 l_materials(1).dtl_um := l_rec.detail_uom;
999 l_materials(1).line_type := V_line_type;
1000 l_materials(1).batch_id := V_entity_id;
1001 l_materials(1).creation_date := gme_common_pvt.g_timestamp;
1002 l_materials(1).created_by := gme_common_pvt.g_user_ident;
1003 l_materials(1).last_update_date := gme_common_pvt.g_timestamp;
1004 l_materials(1).last_updated_by := gme_common_pvt.g_user_ident;
1005
1006 gme_move_orders_pvt.create_move_order_lines (p_move_order_header_id => v_move_order_header_id
1007 ,p_move_order_type => gme_common_pvt.g_invis_move_order_type
1008 ,p_material_details_tbl => l_materials
1009 ,x_material_details_tbl => l_materials_out
1010 ,x_trolin_tbl => l_trolin
1011 ,x_return_status => l_return_status);
1012 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1013 RAISE create_mo_line_err;
1014 ELSE
1015 X_move_order_line_id := l_materials_out(1).move_order_line_id;
1016 END IF;
1017 gmd_spread_fetch_pkg.load_formula_values(V_entity_id => V_entity_id,
1018 V_formula_id => V_formula_id,
1019 V_orgn_id => V_orgn_id,
1020 V_formulaline_id => l_parentline_id,
1021 V_plant_id => V_plant_id);
1022 END IF;
1023 END IF;
1024 EXCEPTION
1025 WHEN create_mo_line_err THEN
1026 FOR i IN 1 .. l_msg_count LOOP
1027 l_msg_data := fnd_msg_pub.get (p_msg_index => i
1028 ,p_encoded => 'T');
1029 END LOOP;
1030 WHEN setup_failure THEN
1031 x_return_status := fnd_api.g_ret_sts_error;
1032 WHEN OTHERS THEN
1033 fnd_msg_pub.add_exc_msg ('GMD_SPREAD_FETCH_PKG', 'Add_New_line');
1034 END add_new_line;
1035
1036 /*##############################################################
1037 # NAME
1038 # load_spread_values
1039 # SYNOPSIS
1040 # proc load_spread_values
1041 # DESCRIPTION
1042 # This procedure inserts the data into temp tables and will
1043 # be fetched in the form.
1044 ###############################################################*/
1045
1046 PROCEDURE load_spread_values (V_entity_id IN NUMBER,V_sprd_id IN NUMBER,
1047 V_orgn_id IN NUMBER,V_parent_line_id IN NUMBER) IS
1048 CURSOR Cur_get_line IS
1049 SELECT line_id
1050 FROM gmd_material_details_gtmp
1051 WHERE parent_line_id = V_parent_line_id
1052 ORDER BY line_type;
1053 BEGIN
1054 /* Inserting the technical parameter data of item and lot to temp tables*/
1055 IF (V_sprd_id IS NOT NULL) THEN
1056 INSERT INTO GMD_TECHNICAL_DATA_GTMP
1057 (ENTITY_ID,LINE_ID,TECH_PARM_NAME,TECH_PARM_ID,
1058 VALUE,SORT_SEQ,NUM_DATA,TEXT_DATA,BOOLEAN_DATA)
1059 SELECT a.sprd_id,c.line_id,a.tech_parm_name,a.tech_parm_id,
1060 DECODE(B.DATA_TYPE,0,TEXT_DATA,2,TEXT_DATA,3,BOOLEAN_DATA,NUM_DATA) VALUE,
1061 b.sort_seq,a.num_data,a.text_data,a.boolean_data
1062 FROM lm_sprd_tec a, gmd_technical_parameter_gtmp b, gmd_material_details_gtmp c
1063 WHERE a.tech_parm_id = b.tech_parm_id
1064 AND a.sprd_id = V_entity_id
1065 AND a.line_id = c.sprd_line_id
1066 AND a.organization_id = V_orgn_id
1067 AND c.parent_line_id = V_parent_line_id;
1068 END IF;
1069 EXCEPTION
1070 WHEN OTHERS THEN
1071 fnd_msg_pub.add_exc_msg ('GMD_SPREAD_FETCH_PKG', 'Load_Spread_Values');
1072 END load_spread_values;
1073
1074 /*##############################################################
1075 # NAME
1076 # load_batch_values
1077 # SYNOPSIS
1078 # proc load_batch_values
1079 # DESCRIPTION
1080 # This procedure inserts the data into temp tables and will
1081 # be fetched in the form.
1082 ###############################################################*/
1083
1084 PROCEDURE load_batch_values (V_entity_id IN NUMBER,V_batch_id IN NUMBER,
1085 V_orgn_id IN NUMBER,V_matl_detl_id IN NUMBER,
1086 V_line_id IN NUMBER,V_plant_id IN NUMBER) IS
1087 CURSOR Cur_get_line IS
1088 SELECT line_id
1089 FROM gmd_material_details_gtmp
1090 WHERE parent_line_id = V_matl_detl_id
1091 ORDER BY line_type;
1092 BEGIN
1093 /* Inserting the technical parameter data of item and lot to temp tables*/
1094 IF (V_batch_id IS NOT NULL) THEN
1095 INSERT INTO GMD_TECHNICAL_DATA_GTMP
1096 (ENTITY_ID,LINE_ID,TECH_PARM_NAME,TECH_PARM_ID,
1097 VALUE,SORT_SEQ,NUM_DATA,TEXT_DATA,BOOLEAN_DATA)
1098 SELECT V_entity_id,c.line_id,a.tech_parm_name, a.tech_parm_id,
1099 DECODE(B.DATA_TYPE,0,TEXT_DATA,2,TEXT_DATA,3,BOOLEAN_DATA,NUM_DATA) VALUE,
1100 b.sort_seq,a.num_data,a.text_data,a.boolean_data
1101 FROM gmd_technical_data_vl a, gmd_technical_parameter_gtmp b, gmd_material_details_gtmp c
1102 WHERE a.tech_parm_id = b.tech_parm_id
1103 AND a.organization_id = V_orgn_id
1104 AND a.inventory_item_id = c.inventory_item_id
1105 AND c.parent_line_id = V_matl_detl_id
1106 AND c.entity_id = V_entity_id
1107 AND (V_line_id IS NULL OR c.line_id = V_line_id)
1108 AND (a.batch_id = V_entity_id OR
1109 (a.batch_id IS NULL AND NOT EXISTS ( SELECT 1
1110 FROM GMD_TECHNICAL_DATA_VL e
1111 WHERE e.inventory_item_id = c.inventory_item_id
1112 AND nvl(e.lot_number, '-1') = nvl(c.lot_number, '-1')
1113 AND nvl(e.lot_organization_id, c.organization_id) = c.organization_id
1114 AND e.formula_id IS NULL
1115 AND e.batch_id = V_entity_id
1116 AND e.organization_id = V_orgn_id)))
1117 AND a.formula_id IS NULL
1118 AND a.delete_mark = 0
1119 AND NVL(c.organization_id, -1) = NVL(a.lot_organization_id, c.organization_id)
1120 AND NVL(c.lot_number, '-1') = NVL(a.lot_number, '-1');
1121
1122 gmd_spread_fetch_pkg.get_lot_density (P_orgn_id => V_orgn_id,
1123 P_parent_detl_id => V_matl_detl_id,
1124 P_entity_id => V_entity_id);
1125
1126
1127 END IF;
1128 FOR l_quality_rec IN Cur_get_line LOOP
1129 load_derived_cost (V_entity_id,V_orgn_id,l_quality_rec.line_id);
1130 load_quality_data (l_quality_rec.line_id,V_orgn_id,V_plant_id);
1131 END LOOP;
1132 EXCEPTION
1133 WHEN OTHERS THEN
1134 fnd_msg_pub.add_exc_msg ('GMD_SPREAD_FETCH_PKG', 'Load_Batch_Values');
1135 END load_batch_values;
1136
1137 /*##############################################################
1138 # NAME
1139 # load_formula_values
1140 # SYNOPSIS
1141 # proc load_formula_values
1142 # DESCRIPTION
1143 # This procedure inserts the data into temp tables and will
1144 # be fetched in the form.
1145 ###############################################################*/
1146
1147 PROCEDURE load_formula_values (V_entity_id IN NUMBER,V_formula_id IN NUMBER,
1148 V_orgn_id IN NUMBER,V_formulaline_id IN NUMBER,
1149 V_line_id IN NUMBER,V_plant_id IN NUMBER) IS
1150 CURSOR Cur_get_line IS
1151 SELECT line_id
1152 FROM gmd_material_details_gtmp
1153 WHERE (V_formulaline_id IS NULL OR parent_line_id = V_formulaline_id)
1154 ORDER BY line_type;
1155 BEGIN
1156 /* Inserting the technical parameter data of item and lot to temp tables*/
1157 IF (V_formula_id IS NOT NULL) THEN
1158 INSERT INTO GMD_TECHNICAL_DATA_GTMP
1159 (ENTITY_ID,LINE_ID,TECH_PARM_NAME,TECH_PARM_ID,
1160 VALUE,SORT_SEQ,NUM_DATA,TEXT_DATA,BOOLEAN_DATA)
1161 SELECT V_entity_id,c.line_id,a.tech_parm_name,a.tech_parm_id,
1162 DECODE(B.DATA_TYPE,0,TEXT_DATA,2,TEXT_DATA,3,BOOLEAN_DATA,NUM_DATA) VALUE,
1163 b.sort_seq,a.num_data,a.text_data,a.boolean_data
1164 FROM gmd_technical_data_vl a, gmd_technical_parameter_gtmp b, gmd_material_details_gtmp c
1165 WHERE a.tech_parm_id = b.tech_parm_id
1166 AND a.organization_id = V_orgn_id
1167 AND a.inventory_item_id = c.inventory_item_id
1168 AND (V_formulaline_id IS NULL OR c.parent_line_id = V_formulaline_id)
1169 AND c.entity_id = V_entity_id
1170 AND (V_line_id IS NULL OR c.line_id = V_line_id)
1171 AND (a.formula_id = c.tpformula_id OR
1172 (a.formula_id IS NULL AND NOT EXISTS (SELECT 1
1173 FROM GMD_TECHNICAL_DATA_VL e
1174 WHERE e.inventory_item_id = c.inventory_item_id
1175 AND NVL(e.lot_number, '-1') = NVL(c.lot_number, '-1')
1176 AND NVL(e.lot_organization_id, c.organization_id) = c.organization_id
1177 AND e.batch_id IS NULL
1178 AND e.formula_id = c.tpformula_id
1179 AND e.organization_id = V_orgn_id)))
1180 AND a.batch_id IS NULL
1181 AND NVL(c.organization_id, -1) = NVL(a.lot_organization_id, c.organization_id)
1182 AND NVL(c.lot_number, '-1') = NVL(a.lot_number, '-1');
1183
1184 gmd_spread_fetch_pkg.get_lot_density (P_orgn_id => V_orgn_id,
1185 P_parent_detl_id => V_formulaline_id,
1186 P_entity_id => V_entity_id);
1187
1188 END IF;
1189
1190 FOR l_quality_rec IN Cur_get_line LOOP
1191 load_derived_cost (V_entity_id,V_orgn_id,l_quality_rec.line_id);
1192 load_quality_data (l_quality_rec.line_id,V_orgn_id,V_plant_id);
1193 END LOOP;
1194 EXCEPTION
1195 WHEN OTHERS THEN
1196 fnd_msg_pub.add_exc_msg ('GMD_SPREAD_FETCH_PKG', 'Load_Formula_Values');
1197 END load_formula_values;
1198
1199 /*##############################################################
1200 # NAME
1201 # save_spreadsheet
1202 # SYNOPSIS
1203 # proc save_spreadsheet
1204 # DESCRIPTION
1205 # This procedure inserts the data into spreadsheet tables.
1206 ###############################################################*/
1207
1208 PROCEDURE save_spreadsheet (V_entity_id IN NUMBER,V_sprd_id IN NUMBER,
1209 V_formula_id IN NUMBER,V_batch_id IN NUMBER,
1210 V_orgn_id IN NUMBER,V_spread_name IN VARCHAR2,
1211 V_maintain_type IN NUMBER,V_text_code IN NUMBER,
1212 V_last_update_date IN DATE,V_move_order_header_id IN NUMBER) IS
1213 CURSOR Cur_sprd_id IS
1214 SELECT gem5_sprd_id_s.nextval
1215 FROM fnd_dual;
1216
1217 CURSOR Cur_line_id IS
1218 SELECT gem5_sprd_line_id_s.nextval
1219 FROM fnd_dual;
1220
1221 CURSOR Cur_sprd_insert IS
1222 SELECT line_id,V_orgn_id,move_order_line_id,line_type,formulaline_id,
1223 material_detail_id,line_no,rollup_ind,inventory_item_id,qty,detail_uom,
1224 text_code,subinventory_code,location,locator_id,lot_number,expiration_date,grade_code,
1225 transaction_id,reservation_id,secondary_qty,secondary_uom,buffer_ind,revision,
1226 revision_qty_control_code,plant_organization_id,organization_id,
1227 last_updated_by,last_update_date,created_by,creation_date
1228 FROM gmd_material_details_gtmp
1229 WHERE entity_id = V_entity_id;
1230 X_sprd_id NUMBER(10);
1231 X_line_id NUMBER(10);
1232 l_user_id NUMBER;
1233 l_text_code NUMBER;
1234 type text_table is table of NUMBER(10) index by binary_integer;
1235 x_text_tab text_table;
1236 x_cnt NUMBER default 0;
1237 BEGIN
1238 /* saving the data to spreadsheet tables from temp tables*/
1239 l_user_id := TO_NUMBER (fnd_profile.VALUE ('USER_ID'));
1240 IF (V_sprd_id IS NOT NULL) THEN
1241 X_sprd_id := V_sprd_id;
1242 UPDATE lm_sprd_fls
1243 SET lab_organization_id = V_orgn_id,
1244 formula_id = V_formula_id,
1245 batch_id = V_batch_id,
1246 move_order_header_id = V_move_order_header_id,
1247 maintain_type = V_maintain_type,
1248 last_update_date = V_last_update_date,
1249 last_updated_by = l_user_id,
1250 text_code = V_text_code
1251 WHERE sprd_name = V_spread_name;
1252 DELETE FROM lm_sprd_dtl
1253 WHERE sprd_id = V_sprd_id;
1254 DELETE FROM lm_sprd_tec
1255 WHERE sprd_id = V_sprd_id;
1256 DELETE FROM lm_sprd_prm
1257 WHERE sprd_id = V_sprd_id;
1258 ELSE
1259 OPEN Cur_sprd_id;
1260 FETCH Cur_sprd_id INTO X_sprd_id;
1261 CLOSE Cur_sprd_id;
1262 INSERT INTO lm_sprd_fls (sprd_id, sprd_name, formula_id,batch_id, lab_organization_id, maintain_type,
1263 active_ind, delete_mark, creation_date,
1264 last_update_date, created_by,
1265 last_updated_by, text_code, in_use,move_order_header_id)
1266 VALUES (X_sprd_id, V_spread_name,
1267 V_formula_id,V_batch_id,
1268 V_orgn_id,V_maintain_type,1,0,V_last_update_date,
1269 V_last_update_date,l_user_id,
1270 l_user_id, V_text_code, 0, V_move_order_header_id);
1271 END IF;
1272
1273 INSERT INTO lm_sprd_prm (sprd_id,organization_id,tech_parm_name,tech_parm_id,sort_seq,data_type,
1274 expression_char,min_value,max_value,precision,optimize_type,
1275 last_updated_by,last_update_date,created_by,creation_date)
1276 SELECT X_sprd_id, V_orgn_id,tech_parm_name,tech_parm_id,sort_seq,data_type,
1277 expression_char,lowerbound_num,upperbound_num,signif_figures,optimize_type,
1278 l_user_id,sysdate,l_user_id,sysdate
1279 FROM gmd_technical_parameter_gtmp
1280 WHERE entity_id = V_entity_id;
1281
1282 FOR l_rec IN Cur_sprd_insert LOOP
1283 OPEN Cur_line_id;
1284 FETCH Cur_line_id INTO X_line_id;
1285 CLOSE Cur_line_id;
1286 INSERT INTO lm_sprd_dtl (line_id,sprd_id,move_order_line_id,line_type,formulaline_id,material_detail_id,line_no,
1287 rollup_ind,inventory_item_id,qty,detail_uom,revision,revision_qty_control_code,text_code,subinventory_code,
1288 location,lot_number,expiration_date,grade_code,transaction_id,reservation_id,secondary_qty,secondary_uom,
1289 buffer_ind, plant_organization_id,organization_id,last_updated_by,last_update_date,
1290 created_by,creation_date,locator_id)
1291 VALUES
1292 (X_line_id,x_sprd_id,l_rec.move_order_line_id,l_rec.line_type,l_rec.formulaline_id,
1293 l_rec.material_detail_id,l_rec.line_no,l_rec.rollup_ind,l_rec.inventory_item_id,
1294 l_rec.qty,l_rec.detail_uom,l_rec.revision,l_rec.revision_qty_control_code,l_rec.text_code,
1295 l_rec.subinventory_code,l_rec.location,l_rec.lot_number,l_rec.expiration_date,
1296 l_rec.grade_code,l_rec.transaction_id,l_rec.reservation_id,l_rec.secondary_qty,
1297 l_rec.secondary_uom,l_rec.buffer_ind,l_rec.plant_organization_id,
1298 l_rec.organization_id,l_rec.last_updated_by,l_rec.last_update_date,
1299 l_rec.created_by,l_rec.creation_date,l_rec.locator_id);
1300
1301 INSERT INTO lm_sprd_tec (line_id,organization_id,tech_parm_name,tech_parm_id,sprd_id,sort_seq,num_data,text_data,
1302 boolean_data,last_updated_by,last_update_date,created_by,creation_date)
1303 SELECT X_line_id, V_orgn_id, tech_parm_name,tech_parm_id,
1304 X_sprd_id,sort_seq,num_data,text_data,boolean_data,
1305 l_user_id,sysdate,l_user_id,sysdate
1306 FROM gmd_technical_data_gtmp
1307 WHERE entity_id = V_entity_id
1308 AND line_id = l_rec.line_id;
1309 END LOOP;
1310 EXCEPTION
1311 WHEN OTHERS THEN
1312 fnd_msg_pub.add_exc_msg ('GMD_SPREAD_FETCH_PKG', 'Save_Spreadsheet');
1313 END save_spreadsheet;
1314
1315 /*##############################################################
1316 # NAME
1317 # get_density_value
1318 # SYNOPSIS
1319 # proc get_density_value
1320 # DESCRIPTION
1321 # This procedure gets the density value for uom conversion.
1322 ###############################################################*/
1323
1324 FUNCTION get_density_value (V_line_id IN NUMBER,
1325 V_density_parameter IN VARCHAR2) RETURN NUMBER IS
1326 CURSOR Cur_density IS
1327 SELECT value
1328 FROM gmd_technical_data_gtmp
1329 WHERE line_id = V_line_id
1330 AND tech_parm_name = V_density_parameter;
1331 l_value NUMBER;
1332 BEGIN
1333 OPEN Cur_density;
1334 FETCH Cur_density INTO l_value;
1335 CLOSE Cur_density;
1336 RETURN (l_value);
1337 END get_density_value;
1338
1339 /*##############################################################
1340 # NAME
1341 # update_line_mass_vol_qty
1342 # SYNOPSIS
1343 # proc update_line_mass_vol_qty
1344 # DESCRIPTION
1345 # This procedure calculates the qtys to mass and volume.
1346 ###############################################################*/
1347
1348 PROCEDURE update_line_mass_vol_qty (V_orgn_id IN NUMBER,
1349 V_line_id IN NUMBER,
1350 V_density_parameter IN VARCHAR2,
1351 V_mass_uom IN VARCHAR2,
1352 V_vol_uom IN VARCHAR2,
1353 X_return_status OUT NOCOPY VARCHAR2) IS
1354
1355 CURSOR Cur_line_qty IS
1356 SELECT inventory_item_id, lot_number, qty,
1357 detail_uom,primary_uom,secondary_uom
1358 FROM gmd_material_details_gtmp
1359 WHERE line_id = V_line_id;
1360
1361 CURSOR Cur_line_item_number (V_inventory_item_id NUMBER)IS
1362 SELECT concatenated_segments
1363 FROM mtl_system_items_kfv
1364 WHERE inventory_item_id = V_inventory_item_id;
1365
1366 l_conv_factor NUMBER;
1367 l_mass_qty NUMBER;
1368 l_primary_qty NUMBER;
1369 l_vol_qty NUMBER;
1370 l_item_no VARCHAR2(1000);
1371 l_error NUMBER(5) := 0;
1372 l_rec Cur_line_qty%ROWTYPE;
1373 LINE_NOT_FOUND EXCEPTION;
1374 BEGIN
1375 X_return_status := FND_API.g_ret_sts_success;
1376
1377 l_conv_factor := get_density_value (V_line_id => V_line_id,
1378 V_density_parameter => V_density_parameter);
1379 OPEN Cur_line_qty;
1380 FETCH Cur_line_qty INTO l_rec;
1381 IF Cur_line_qty%NOTFOUND THEN
1382 CLOSE Cur_line_qty;
1383 RAISE LINE_NOT_FOUND;
1384 END IF;
1385 CLOSE Cur_line_qty;
1386
1387 IF l_rec.detail_uom <> V_mass_uom THEN
1388 l_mass_qty := gmd_labuom_calculate_pkg.uom_conversion (pitem_id => l_rec.inventory_item_id,
1389 pformula_id => 0,
1390 plot_number => l_rec.lot_number,
1391 pcur_qty => l_rec.qty,
1392 pcur_uom => l_rec.detail_uom,
1393 pnew_uom => V_mass_uom,
1394 patomic => 0,
1395 plab_id => V_orgn_id,
1396 pcnv_factor => l_conv_factor);
1397 IF l_mass_qty < 0 THEN
1398 l_error := 1;
1399 l_mass_qty := NULL;
1400 END IF;
1401 ELSE
1402 l_mass_qty := l_rec.qty;
1403 END IF;
1404
1405 IF l_rec.detail_uom <> V_vol_uom THEN
1406 l_vol_qty := gmd_labuom_calculate_pkg.uom_conversion (pitem_id => l_rec.inventory_item_id,
1407 pformula_id => 0,
1408 plot_number => l_rec.lot_number,
1409 pcur_qty => l_rec.qty,
1410 pcur_uom => l_rec.detail_uom,
1411 pnew_uom => V_vol_uom,
1412 patomic => 0,
1413 plab_id => V_orgn_id,
1414 pcnv_factor => l_conv_factor);
1415 IF l_vol_qty < 0 THEN
1416 l_error := 1;
1417 l_vol_qty := NULL;
1418 END IF;
1419 ELSE
1420 l_vol_qty := l_rec.qty;
1421 END IF;
1422
1423 IF l_rec.detail_uom <> l_rec.primary_uom THEN
1424 l_primary_qty := gmd_labuom_calculate_pkg.uom_conversion (pitem_id => l_rec.inventory_item_id,
1425 pformula_id => 0,
1426 plot_number => l_rec.lot_number,
1427 pcur_qty => l_rec.qty,
1428 pcur_uom => l_rec.detail_uom,
1429 pnew_uom => l_rec.primary_uom,
1430 patomic => 0,
1431 plab_id => V_orgn_id);
1432 IF l_primary_qty < 0 THEN
1433 l_error := 1;
1434 l_primary_qty := NULL;
1435 END IF;
1436 ELSE
1437 l_primary_qty := l_rec.qty;
1438 END IF;
1439
1440 UPDATE gmd_material_details_gtmp
1441 SET qty_mass = l_mass_qty,
1442 mass_uom = V_mass_uom,
1443 qty_vol = l_vol_qty,
1444 vol_uom = V_vol_uom,
1445 primary_qty = l_primary_qty,
1446 primary_uom = l_rec.primary_uom
1447 WHERE line_id = V_line_id;
1448
1449 OPEN Cur_line_item_number(l_rec.inventory_item_id);
1450 FETCH Cur_line_item_number INTO l_item_no;
1451 CLOSE Cur_line_item_number;
1452
1453 IF l_error = 1 THEN
1454 X_return_status := FND_API.g_ret_sts_error;
1455 gmd_api_grp.log_message('LM_BAD_UOMCV', 'ITEM_NO',l_item_no, 'DENSITY',V_density_parameter);
1456 END IF;
1457
1458 EXCEPTION
1459 WHEN line_not_found THEN
1460 X_return_status := FND_API.g_ret_sts_error;
1461 WHEN OTHERS THEN
1462 fnd_msg_pub.add_exc_msg ('GMD_SPREAD_FETCH_PKG', 'Update_Line_Mass_Vol_Qty');
1463 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1464 END update_line_mass_vol_qty;
1465
1466 /*##############################################################
1467 # NAME
1468 # update_line_mass_qty
1469 # SYNOPSIS
1470 # proc update_line_mass_qty
1471 # DESCRIPTION
1472 # This procedure calculates the qtys to mass and volume.
1473 ###############################################################*/
1474
1475 PROCEDURE update_mass_vol_qty (V_orgn_id IN NUMBER,
1476 V_entity_id IN NUMBER,
1477 V_density_parameter IN VARCHAR2,
1478 V_mass_uom IN VARCHAR2,
1479 V_vol_uom IN VARCHAR2,
1480 X_return_status OUT NOCOPY VARCHAR2) IS
1481 CURSOR Cur_get_lines IS
1482 SELECT line_id
1483 FROM gmd_material_details_gtmp
1484 WHERE rollup_ind = 1
1485 AND line_type <> 1
1486 AND entity_id = V_entity_id;
1487
1488 l_return_status VARCHAR2(1);
1489 BEGIN
1490 X_return_status := FND_API.g_ret_sts_success;
1491
1492 FOR l_rec IN Cur_get_lines LOOP
1493 l_return_status := FND_API.g_ret_sts_success;
1494 update_line_mass_vol_qty (V_orgn_id => V_orgn_id,
1495 V_line_id => l_rec.line_id,
1496 V_density_parameter => V_density_parameter,
1497 V_mass_uom => V_mass_uom,
1498 V_vol_uom => V_vol_uom,
1499 X_return_status => l_return_status);
1500 IF l_return_status <> FND_API.g_ret_sts_success THEN
1501 X_return_status := l_return_status;
1502 END IF;
1503 END LOOP;
1504 END update_mass_vol_qty;
1505
1506 /*##############################################################
1507 # NAME
1508 # load_quality_data
1509 # SYNOPSIS
1510 # proc load_quality_data
1511 # DESCRIPTION
1512 # This procedure inserts the data into temp tables from quality
1513 # tables.
1514 ###############################################################*/
1515
1516 PROCEDURE load_quality_data (V_line_id IN NUMBER, V_orgn_id IN NUMBER,V_plant_id IN NUMBER) IS
1517
1518 CURSOR Cur_get_qmdata IS
1519 SELECT *
1520 FROM gmd_technical_parameter_gtmp
1521 WHERE qcassy_typ_id IS NOT NULL;
1522
1523 CURSOR Cur_get_data IS
1524 SELECT *
1525 FROM gmd_material_details_gtmp
1526 WHERE line_id = V_line_id;
1527
1528 l_rec Cur_get_data%ROWTYPE;
1529 l_return_status VARCHAR2(1);
1530 l_value VARCHAR2(80);
1531 l_inv_inp_rec_type GMD_QUALITY_GRP.inv_inp_rec_type;
1532 l_inv_val_out_rec_type GMD_QUALITY_GRP.inv_val_out_rec_type;
1533
1534 CURSOR Cur_get_value(Pline_id NUMBER,Pparm_id NUMBER) IS
1535 SELECT value
1536 FROM gmd_technical_data_gtmp
1537 WHERE line_id = Pline_id
1538 AND tech_parm_id = Pparm_id;
1539
1540 l_temp VARCHAR2(80);
1541 l_num_value NUMBER;
1542 l_char_value VARCHAR2(240);
1543 X_return_status VARCHAR2(20);
1544 BEGIN
1545 OPEN Cur_get_data;
1546 FETCH Cur_get_data INTO l_rec;
1547 CLOSE Cur_get_data;
1548 l_inv_inp_rec_type.organization_id := V_orgn_id;
1549 l_inv_inp_rec_type.inventory_item_id := l_rec.inventory_item_id;
1550 l_inv_inp_rec_type.grade_code := l_rec.grade_code;
1551 l_inv_inp_rec_type.lot_number := l_rec.lot_number;
1552 l_inv_inp_rec_type.subinventory := l_rec.subinventory_code;
1553 l_inv_inp_rec_type.locator_id := l_rec.locator_id;
1554 l_inv_inp_rec_type.plant_id := V_plant_id;
1555 FOR l_qmrec IN Cur_get_qmdata LOOP
1556 l_inv_inp_rec_type.test_id := l_qmrec.qcassy_typ_id;
1557 gmd_quality_grp.get_inv_test_value (P_inv_test_inp_rec => l_inv_inp_rec_type,
1558 x_inv_test_out_rec => l_inv_val_out_rec_type,
1559 x_return_status => l_return_status);
1560 l_value := l_inv_val_out_rec_type.entity_value;
1561 IF (l_rec.line_type = 3) THEN
1562 IF (l_inv_val_out_rec_type.level BETWEEN 11 AND 20) OR (l_inv_val_out_rec_type.level > 40) THEN
1563 OPEN Cur_get_value(V_line_id,l_qmrec.tech_parm_id);
1564 FETCH Cur_get_value INTO l_temp;
1565 IF (Cur_get_value%FOUND) THEN
1566 l_value := NULL;
1567 END IF;
1568 CLOSE Cur_get_value;
1569 END IF;
1570 END IF;
1571 IF (l_value IS NOT NULL) THEN
1572 IF l_qmrec.data_type = 1 THEN
1573 l_num_value := l_value;
1574 l_char_value := NULL;
1575 ELSE
1576 l_char_value := l_value;
1577 l_num_value := NULL;
1578 END IF;
1579 UPDATE gmd_technical_data_gtmp
1580 SET value = l_inv_val_out_rec_type.entity_value,
1581 num_data = l_num_value,
1582 qm_entity_id = l_inv_val_out_rec_type.entity_id,
1583 qm_level = l_inv_val_out_rec_type.level,
1584 text_data = l_char_value
1585 WHERE line_id = V_line_id
1586 AND tech_parm_id = l_qmrec.tech_parm_id;
1587 IF SQL%NOTFOUND THEN
1588 INSERT INTO GMD_TECHNICAL_DATA_GTMP
1589 (ENTITY_ID,LINE_ID,TECH_PARM_NAME,TECH_PARM_ID,VALUE,SORT_SEQ,NUM_DATA,
1590 TEXT_DATA,QM_ENTITY_ID,QM_LEVEL,COMP_IND,MIN_VALUE,MAX_VALUE,SPEC_ID)
1591 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,
1592 l_qmrec.sort_seq,l_num_value,l_char_value,
1593 l_inv_val_out_rec_type.entity_id,l_inv_val_out_rec_type.level,l_inv_val_out_rec_type.composite_ind,
1594 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);
1595 END IF;
1596 ELSE
1597 UPDATE gmd_technical_data_gtmp
1598 SET value = l_inv_val_out_rec_type.entity_value,
1599 num_data = l_num_value,
1600 qm_entity_id = l_inv_val_out_rec_type.entity_id,
1601 qm_level = l_inv_val_out_rec_type.level,
1602 text_data = l_char_value
1603 WHERE line_id = V_line_id
1604 AND tech_parm_id = l_qmrec.tech_parm_id;
1605 END IF;
1606 END LOOP;
1607 EXCEPTION
1608 WHEN OTHERS THEN
1609 fnd_msg_pub.add_exc_msg ('GMD_SPREAD_FETCH_PKG', 'load_quality_data');
1610 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1611 NULL;
1612 END load_quality_data;
1613
1614 /**#############################################################
1615 # NAME
1616 # get_lot_density
1617 # SYNOPSIS
1618 # proc get_lot_density
1619 # DESCRIPTION
1620 #
1621 # HISTORY
1622 # Sriram.S 30Aug04 Created
1623 # CAF Enhancement
1624 ##############################################################**/
1625
1626 PROCEDURE get_lot_density (P_orgn_id NUMBER,
1627 P_parent_detl_id NUMBER,
1628 P_entity_id NUMBER) IS
1629
1630 CURSOR cur_get_param (v_density VARCHAR2) IS
1631 SELECT tech_parm_id
1632 FROM gmd_tech_parameters_b
1633 WHERE tech_parm_name = v_density
1634 AND organization_id = P_orgn_id;
1635
1636 CURSOR cur_get_value (v_tech_parm_id NUMBER) IS
1637 SELECT a.value
1638 FROM gmd_technical_data_gtmp a, gmd_material_details_gtmp c
1639 WHERE a.tech_parm_id = v_tech_parm_id
1640 AND c.parent_line_id = P_parent_detl_id
1641 AND a.line_id = c.line_id
1642 AND c.line_type <> 3;
1643
1644 CURSOR cur_get_data (v_tech_parm_id NUMBER) IS
1645 SELECT line_id
1646 FROM gmd_material_details_gtmp p
1647 WHERE parent_line_id = P_parent_detl_id
1648 AND line_type = 3
1649 AND NOT EXISTS ( SELECT 1
1650 FROM gmd_technical_data_gtmp g
1651 WHERE p.line_id = g.line_id
1652 AND g.tech_parm_id = v_tech_parm_id);
1653
1654 /* Local variables */
1655 l_density VARCHAR2 (240);
1656 l_value NUMBER;
1657 l_tech_parm_id NUMBER;
1658
1659 BEGIN
1660 l_density := fnd_profile.VALUE ('LM$DENSITY');
1661
1662 OPEN cur_get_param (l_density);
1663 FETCH cur_get_param INTO l_tech_parm_id;
1664 CLOSE cur_get_param;
1665
1666 OPEN cur_get_value (l_tech_parm_id);
1667 FETCH cur_get_value INTO l_value;
1668 CLOSE cur_get_value;
1669
1670 IF (l_value IS NOT NULL) THEN
1671 FOR l_rec IN cur_get_data (l_tech_parm_id) LOOP
1672 INSERT INTO gmd_technical_data_gtmp
1673 (entity_id, line_id, tech_parm_name, tech_parm_id,
1674 Value, sort_seq, num_data, TEXT_DATA, BOOLEAN_DATA)
1675 VALUES
1676 (P_entity_id, l_rec.line_id, l_density, l_tech_parm_id,
1677 l_value, 1, l_value, NULL, NULL);
1678 END LOOP;
1679 END IF;
1680 END get_lot_density;
1681
1682 /*##############################################################
1683 # NAME
1684 # load_lcf_details
1685 # SYNOPSIS
1686 # proc load_lcf_details
1687 # DESCRIPTION
1688 # This procedure inserts the data into temp tables and will
1689 # be fetched in the form.
1690 ###############################################################*/
1691
1692 PROCEDURE load_lcf_details (V_entity_id IN NUMBER,
1693 V_orgn_id IN NUMBER,
1694 V_plant_id IN NUMBER) IS
1695 CURSOR Cur_get_lcf IS
1696 SELECT a.*,b.description descrip, b.default_grade,
1697 b.primary_uom_code primary,b.secondary_uom_code secondary,
1698 b.lot_control_code,b.secondary_default_ind,
1699 b.grade_control_flag,b.tracking_quantity_ind,b.location_control_code
1700 FROM gmd_lcf_details_gtmp a, mtl_system_items_b b
1701 WHERE a.inventory_item_id = b.inventory_item_id
1702 AND b.organization_id = V_orgn_id
1703 ORDER BY a.line_type, a.line_no;
1704 l_formula_rec Cur_get_lcf%ROWTYPE;
1705 l_secondary_qty NUMBER;
1706 CURSOR Cur_get_lines IS
1707 SELECT formulaline_id
1708 FROM gmd_material_details_gtmp
1709 WHERE entity_id = V_entity_id;
1710 BEGIN
1711 /* Inserting the item and lot data from formula detail tables to temp tables*/
1712 IF (V_orgn_id IS NOT NULL) THEN
1713 OPEN Cur_get_lcf;
1714 LOOP
1715 FETCH Cur_get_lcf INTO l_formula_rec;
1716 EXIT WHEN Cur_get_lcf%NOTFOUND;
1717 /* Getting the secondary qty*/
1718 l_secondary_qty := null;
1719 IF (l_formula_rec.qty > 0 AND l_formula_rec.tracking_quantity_ind = 'PS') THEN
1720 l_secondary_qty := gmd_labuom_calculate_pkg.uom_conversion (pitem_id => l_formula_rec.inventory_item_id,
1721 pformula_id => NULL,
1722 plot_number => NULL,
1723 pcur_qty => l_formula_rec.qty,
1724 pcur_uom => l_formula_rec.detail_uom,
1725 pnew_uom => l_formula_rec.secondary,
1726 patomic => 0,
1727 plab_id => V_orgn_id);
1728 IF (l_secondary_qty < 0) THEN
1729 l_secondary_qty := NULL;
1730 END IF;
1731 END IF;
1732
1733 INSERT INTO GMD_MATERIAL_DETAILS_GTMP
1734 (ENTITY_ID,LINE_ID,LINE_TYPE,LINE_NO,ROLLUP_IND,EXPAND_IND,TRACKING_QUANTITY_IND,
1735 LOCATION_CONTROL_CODE,INVENTORY_ITEM_ID,DESCRIPTION,
1736 QTY,SECONDARY_QTY,DETAIL_UOM,GRADE_CODE,PRIMARY_UOM,SECONDARY_UOM,LOT_CONTROL_CODE,
1737 GRADE_CONTROL_FLAG,FORMULALINE_ID,PARENT_LINE_ID,
1738 CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,
1739 ORGANIZATION_ID,SECONDARY_DEFAULT_IND)
1740 VALUES
1741 (V_entity_id,l_formula_rec.line_id,l_formula_rec.line_type,l_formula_rec.line_no,1,1,
1742 l_formula_rec.tracking_quantity_ind,l_formula_rec.location_control_code,
1743 l_formula_rec.inventory_item_id,l_formula_rec.descrip,
1744 l_formula_rec.qty,l_secondary_qty,l_formula_rec.detail_uom,
1745 l_formula_rec.default_grade,l_formula_rec.primary,l_formula_rec.secondary,
1746 l_formula_rec.lot_control_code,l_formula_rec.grade_control_flag,
1747 l_formula_rec.line_id,l_formula_rec.line_id,l_formula_rec.created_by,
1748 l_formula_rec.creation_date,l_formula_rec.last_updated_by,
1749 l_formula_rec.last_update_date,V_orgn_id,l_formula_rec.secondary_default_ind);
1750 END LOOP;
1751 CLOSE Cur_get_lcf;
1752
1753 FOR l_form_rec IN Cur_get_lines LOOP
1754 gmd_spread_fetch_pkg.load_lcf_values(V_entity_id => V_entity_id,
1755 V_orgn_id => V_orgn_id,
1756 V_formulaline_id => l_form_rec.formulaline_id,
1757 V_plant_id => V_plant_id);
1758 END LOOP;
1759 END IF;
1760 EXCEPTION
1761 WHEN OTHERS THEN
1762 fnd_msg_pub.add_exc_msg ('GMD_SPREAD_FETCH_PKG', 'Load_Lcf_Details');
1763 END load_lcf_details;
1764
1765 /*##############################################################
1766 # NAME
1767 # load_lcf_values
1768 # SYNOPSIS
1769 # proc load_lcf_values
1770 # DESCRIPTION
1771 # This procedure inserts the data into temp tables and will
1772 # be fetched in the form.
1773 ###############################################################*/
1774
1775 PROCEDURE load_lcf_values (V_entity_id IN NUMBER,V_orgn_id IN NUMBER,
1776 V_formulaline_id IN NUMBER,V_plant_id IN NUMBER,
1777 V_line_id IN NUMBER) IS
1778 CURSOR Cur_get_line IS
1779 SELECT line_id
1780 FROM gmd_material_details_gtmp
1781 WHERE (V_formulaline_id IS NULL OR parent_line_id = V_formulaline_id)
1782 ORDER BY line_type;
1783 BEGIN
1784 /* Inserting the technical parameter data of item and lot to temp tables*/
1785 IF (V_entity_id IS NOT NULL) THEN
1786 INSERT INTO GMD_TECHNICAL_DATA_GTMP
1787 (ENTITY_ID,LINE_ID,TECH_PARM_NAME,TECH_PARM_ID,
1788 VALUE,SORT_SEQ,NUM_DATA,TEXT_DATA,BOOLEAN_DATA)
1789 SELECT V_entity_id,c.line_id,b.tech_parm_name,a.tech_parm_id,
1790 DECODE(B.DATA_TYPE,0,TEXT_DATA,2,TEXT_DATA,3,BOOLEAN_DATA,NUM_DATA) VALUE,
1791 b.sort_seq,a.num_data,a.text_data,a.boolean_data
1792 FROM gmd_technical_data_vl a, gmd_technical_parameter_gtmp b, gmd_material_details_gtmp c
1793 WHERE a.tech_parm_id = b.tech_parm_id
1794 AND a.organization_id = V_orgn_id
1795 AND a.inventory_item_id = c.inventory_item_id
1796 AND (V_formulaline_id IS NULL OR c.parent_line_id = V_formulaline_id)
1797 AND c.entity_id = V_entity_id
1798 AND (V_line_id IS NULL OR c.line_id = V_line_id)
1799 AND NVL(c.organization_id, -1) = NVL(a.lot_organization_id, c.organization_id)
1800 AND NVL(c.lot_number, '-1') = NVL(a.lot_number, '-1');
1801 END IF;
1802 FOR l_quality_rec IN Cur_get_line LOOP
1803 load_derived_cost (V_entity_id,V_orgn_id,l_quality_rec.line_id);
1804 load_quality_data (l_quality_rec.line_id,V_orgn_id,V_plant_id);
1805 END LOOP;
1806 EXCEPTION
1807 WHEN OTHERS THEN
1808 fnd_msg_pub.add_exc_msg ('GMD_SPREAD_FETCH_PKG', 'Load_Lcf_Values');
1809 END load_lcf_values;
1810
1811 /*##############################################################
1812 # NAME
1813 # load_derived_cost
1814 # SYNOPSIS
1815 # proc load_derived_cost
1816 # DESCRIPTION
1817 # This procedure inserts the data into temp tables and will
1818 # be fetched in the form.
1819 ###############################################################*/
1820
1821 PROCEDURE load_derived_cost (V_entity_id IN NUMBER,V_orgn_id IN NUMBER,V_line_id IN NUMBER) IS
1822 CURSOR Cur_get_type IS
1823 SELECT a.*, c.line_id line, b.tech_parm_id tech, b.tech_parm_name name,b.sort_seq, c.inventory_item_id
1824 FROM gmd_tech_parameters_b a, gmd_technical_parameter_gtmp b, gmd_material_details_gtmp c
1825 WHERE a.tech_parm_id = b.tech_parm_id
1826 AND c.line_id = V_line_id;
1827 l_value NUMBER;
1828 l_parm_value VARCHAR2(240);
1829 l_return_status VARCHAR2(1);
1830 BEGIN
1831 gmd_api_grp.fetch_parm_values (P_orgn_id => V_orgn_id
1832 ,P_parm_name => 'GMD_COST_SOURCE_ORGN'
1833 ,P_parm_value => l_parm_value
1834 ,X_return_status => l_return_status);
1835 FOR l_rec IN Cur_get_type LOOP
1836 IF l_rec.data_type = 12 THEN
1837 gmd_lcf_fetch_pkg.load_cost_values (V_orgn_id => V_orgn_id,
1838 V_inv_item_id => l_rec.inventory_item_id,
1839 V_cost_type => l_rec.cost_type,
1840 V_date => SYSDATE,
1841 V_cost_orgn => l_parm_value,
1842 V_source => l_rec.cost_source,
1843 X_value => l_value);
1844 INSERT INTO GMD_TECHNICAL_DATA_GTMP (ENTITY_ID,LINE_ID,TECH_PARM_NAME,
1845 TECH_PARM_ID,VALUE,NUM_DATA,SORT_SEQ)
1846 VALUES (V_entity_id,l_rec.line,l_rec.name,l_rec.tech,l_value,l_value,l_rec.sort_seq);
1847 END IF;
1848 END LOOP;
1849 END load_derived_cost;
1850
1851
1852 END GMD_SPREAD_FETCH_PKG;
1853