[Home] [Help]
PACKAGE BODY: APPS.GME_TRANSFORM_BATCH
Source
1 PACKAGE BODY gme_transform_batch AS
2 /* $Header: GMEVTRFB.pls 120.4 2006/09/20 18:38:31 creddy noship $ */
3
4 /***********************************************************/
5 -- Oracle Process Manufacturing Process Execution APIs
6 --
7 -- File Name: GMEVTRFB.pls
8 -- Contents: Package body for GME data transformation
9 -- Description:
10 -- This package transforms GME data from 11.5.10 to
11 -- 12.
12
13 /**********************************************************/
14
15 PROCEDURE gme_migration (p_migration_run_id IN NUMBER,
16 p_commit IN VARCHAR2,
17 x_failure_count OUT NOCOPY NUMBER) IS
18 BEGIN
19 gma_common_logging.gma_migration_central_log
20 (p_run_id => p_migration_run_id,
21 p_log_level => fnd_log.level_procedure,
22 p_message_token => 'GMA_MIGRATION_TABLE_STARTED',
23 p_table_name => 'GME_PARAMETERS',
24 p_context => 'PROFILES',
25 p_app_short_name => 'GMA');
26 create_gme_parameters(p_migration_run_id => p_migration_run_id,
27 x_exception_count => x_failure_count);
28
29 gma_common_logging.gma_migration_central_log
30 (p_run_id => p_migration_run_id,
31 p_log_level => fnd_log.level_procedure,
32 p_message_token => 'GMA_MIGRATION_TABLE_STARTED',
33 p_table_name => 'GME_BATCH_HEADER',
34 p_context => 'UPDATE_LAB_IND',
35 p_app_short_name => 'GMA');
36 update_batch_header(p_migration_run_id => p_migration_run_id,
37 x_exception_count => x_failure_count);
38
39 gma_common_logging.gma_migration_central_log
40 (p_run_id => p_migration_run_id,
41 p_log_level => fnd_log.level_procedure,
42 p_message_token => 'GMA_MIGRATION_TABLE_STARTED',
43 p_table_name => 'WIP_ENTITIES',
44 p_context => 'CREATE_WIP_ENTITY',
45 p_app_short_name => 'GMA');
46 update_wip_entities(p_migration_run_id => p_migration_run_id,
47 x_exception_count => x_failure_count);
48
49
50 gma_common_logging.gma_migration_central_log
51 (p_run_id => p_migration_run_id,
52 p_log_level => fnd_log.level_procedure,
53 p_message_token => 'GMA_MIGRATION_TABLE_STARTED',
54 p_table_name => 'GME_GANTT_DOCUMENT_FILTER',
55 p_context => 'GANTT FILTERS',
56 p_app_short_name => 'GMA');
57 update_from_doc_no(p_migration_run_id);
58
59 gma_common_logging.gma_migration_central_log
60 (p_run_id => p_migration_run_id,
61 p_log_level => fnd_log.level_procedure,
62 p_message_token => 'GMA_MIGRATION_TABLE_STARTED',
63 p_table_name => 'GME_RESOURCE_TXNS',
64 p_context => 'REASON_ID',
65 p_app_short_name => 'GMA');
66 update_reason_id(p_migration_run_id);
67
68 IF p_commit = 'Y' THEN
69 COMMIT;
70 END IF;
71 END gme_migration;
72
73 FUNCTION get_profile_value (v_profile_name IN VARCHAR2, v_appl_id IN NUMBER)
74 RETURN VARCHAR2 IS
75 BEGIN
76 RETURN fnd_profile.VALUE (v_profile_name);
77 END get_profile_value;
78
79 PROCEDURE create_gme_parameters(p_migration_run_id IN NUMBER,
80 x_exception_count OUT NOCOPY NUMBER) IS
81 CURSOR get_plant_and_labs IS
82 SELECT sy.organization_id, sy.orgn_code plant_code
83 FROM sy_orgn_mst sy
84 WHERE NOT EXISTS (SELECT 1
85 FROM gme_parameters
86 WHERE organization_id = sy.organization_id)
87 AND sy.organization_id IS NOT NULL;
88
89 CURSOR get_doc_numbering (v_doc_type IN VARCHAR2, v_plant_code IN VARCHAR2) IS
90 SELECT assignment_type, last_assigned
91 FROM sy_docs_seq
92 WHERE orgn_code = v_plant_code AND doc_type = v_doc_type;
93
94 l_fpo_assignment NUMBER;
95 l_batch_assignment NUMBER;
96 l_fpo_number NUMBER;
97 l_batch_number NUMBER;
98 l_count NUMBER := 0;
99 BEGIN
100 FOR rec IN get_plant_and_labs LOOP
101 BEGIN
102 OPEN get_doc_numbering ('FPO', rec.plant_code);
103 FETCH get_doc_numbering INTO l_fpo_assignment, l_fpo_number;
104 CLOSE get_doc_numbering;
105 OPEN get_doc_numbering ('PROD', rec.plant_code);
106 FETCH get_doc_numbering INTO l_batch_assignment, l_batch_number;
107 CLOSE get_doc_numbering;
108
109 INSERT INTO gme_parameters
110 (organization_id, auto_consume_supply_sub_only,
111 supply_subinventory, supply_locator_id,
112 yield_subinventory, yield_locator_id,
113 delete_material_ind,
114 validate_plan_dates_ind,
115 display_unconsumed_material,
116 step_controls_batch_sts_ind,
117 backflush_rsrc_usg_ind,
118 def_actual_rsrc_usg_ind,
119 calc_interim_rsrc_usg_ind,
120 allow_qty_below_min_ind,
121 display_non_work_days_ind,
122 check_shortages_ind,
123 copy_formula_text_ind,
124 copy_routing_text_ind,
125 create_high_level_resv_ind, create_move_orders_ind,
126 reservation_timefence, move_order_timefence,
127 batch_doc_numbering,
128 batch_no_last_assigned, fpo_doc_numbering,
129 fpo_no_last_assigned, created_by, creation_date,
130 last_updated_by, last_update_login, last_update_date
131 )
132 VALUES (rec.organization_id,
133 0, -- AUTO_CONSUME_SUPPLY_SUB_ONLY,
134 NULL, -- SUPPLY_SUBINVENTORY
135 NULL, -- SUPPLY_LOCATOR_ID
136 NULL, -- YIELD_SUBINVETORY
137 NULL, --YIELD_LOCATOR_ID
138 NVL(get_profile_value ('GME_ALLOW_MATERIAL_DELETION', 553),1),
139 NVL(get_profile_value ('GME_VALIDATE_PLAN_DATES', 553),1), --VALIDATE_PLAN_DATES_IND
140 1, --DISPLAY_UNCONSUMED_MATERIAL
141 NVL(DECODE (get_profile_value ('GME_STEP_CONTROL', 553), 'N', 0, 'Y', 1, 0),0),--STEP_CONTROLS_BATCH_STS_IND
142 NVL(get_profile_value ('GME_BACKFLUSH_USAGE', 553),0), --BACKFLUSH_RSRC_USG_IND
143 NVL(get_profile_value ('PM$DEFAULT_ACTUAL_RESOURCE_USAGE', 550),1), --DEF_ACTUAL_RSRC_USG_IND
144 NVL(get_profile_value ('GME_CALC_INT_RSRC_USAGE', 553),0), --CALC_INTERIM_RSRC_USG_IND
145 NVL(get_profile_value ('GME_ALLOW_QTY_BELOW_CAP', 553),1), --ALLOW_QTY_BELOW_MIN_IND
146 NVL(get_profile_value ('GME_DISP_NON_WORKING_DAYS_IN_GANTT', 553),1), --DISPLAY_NON_WORK_DAYS_IND
147 NVL(get_profile_value ('PM$CHECK_INV_SAVE', 550),0), --CHECK_SHORTAGES_IND
148 NVL(get_profile_value ('PM_COPY_FM_TEXT', 550),1), --COPY_FORMULA_TEXT_IND
149 NVL(get_profile_value ('GME_COPY_ROUTING_TEXT', 553),1), --COPY_ROUTING_TEXT_IND
150 0, --CREATE_HIGH_LEVEL_RESV_IND
151 0, --CREATE_MOVE_ORDERS_IND
152 NULL, --RESERVATION_TIMEFENCE
153 NULL, --MOVE_ORDER_TIMEFENCE
154 l_batch_assignment, --BATCH_DOC_NUMBERING
155 l_batch_number, --BATCH_NO_LAST_ASSIGNED
156 l_fpo_assignment, --FPO_DOC_NUMBERING
157 l_fpo_number, --FPO_NO_LAST_ASSIGNED
158 -1, --created_by
159 SYSDATE, --creation_date
160 -1, --last_updated_by
161 NULL, --last_update_login
162 SYSDATE --last_updated_date
163 );
164 l_count := l_count + 1;
165 gma_common_logging.gma_migration_central_log(p_run_id => p_migration_run_id,
166 p_log_level => fnd_log.level_procedure,
167 p_message_token => 'GME_CREATE_PARAMS_SUCCESS',
168 p_table_name => 'GME_PARAMETERS',
169 p_context => 'PROFILES',
170 p_token1 => 'ORG_CODE',
171 p_param1 => rec.plant_code,
172 p_app_short_name => 'GME');
173 EXCEPTION
174 WHEN OTHERS THEN
175 x_exception_count := x_exception_count + 1;
176 gma_common_logging.gma_migration_central_log
177 (p_run_id => p_migration_run_id,
178 p_log_level => fnd_log.level_unexpected,
179 p_message_token => 'GMA_MIGRATION_DB_ERROR',
180 p_table_name => 'GME_PARAMETERS',
181 p_context => 'PROFILES',
182 p_db_error => SQLERRM,
183 p_app_short_name => 'GMA');
184 gma_common_logging.gma_migration_central_log
185 (p_run_id => p_migration_run_id,
186 p_log_level => fnd_log.level_unexpected,
187 p_message_token => 'GMA_MIGRATION_TABLE_FAIL',
188 p_table_name => 'GME_PARAMETERS',
189 p_context => 'PROFILES',
190 p_app_short_name => 'GMA');
191 END;
192 END LOOP;
193 gma_common_logging.gma_migration_central_log
194 (p_run_id => p_migration_run_id,
195 p_log_level => fnd_log.level_procedure,
196 p_message_token => 'GMA_MIGRATION_TABLE_SUCCESS',
197 p_table_name => 'GME_PARAMETERS',
198 p_context => 'PROFILES',
199 p_param1 => l_count,
200 p_app_short_name => 'GMA');
201 EXCEPTION
202 WHEN OTHERS THEN
203 x_exception_count := x_exception_count + 1;
204 gma_common_logging.gma_migration_central_log
205 (p_run_id => p_migration_run_id,
206 p_log_level => fnd_log.level_unexpected,
207 p_message_token => 'GMA_MIGRATION_DB_ERROR',
208 p_table_name => 'GME_PARAMETERS',
209 p_context => 'PROFILES',
210 p_db_error => SQLERRM,
211 p_app_short_name => 'GMA');
212 gma_common_logging.gma_migration_central_log
213 (p_run_id => p_migration_run_id,
214 p_log_level => fnd_log.level_unexpected,
215 p_message_token => 'GMA_MIGRATION_TABLE_FAIL',
216 p_table_name => 'GME_PARAMETERS',
217 p_context => 'PROFILES',
218 p_app_short_name => 'GMA');
219 END create_gme_parameters;
220
221 PROCEDURE update_batch_header(p_migration_run_id IN NUMBER,
222 x_exception_count OUT NOCOPY NUMBER) IS
223 BEGIN
224 UPDATE gme_batch_header h
225 SET laboratory_ind = (SELECT DECODE (org.plant_ind, 1, 0, 2, 1)
226 FROM sy_orgn_mst org
227 WHERE org.orgn_code = h.plant_code),
228 migrated_batch_ind = 'Y'
229 WHERE laboratory_ind IS NULL;
230 gma_common_logging.gma_migration_central_log
231 (p_run_id => p_migration_run_id,
232 p_log_level => fnd_log.level_procedure,
233 p_message_token => 'GMA_MIGRATION_TABLE_SUCCESS',
234 p_table_name => 'GME_BATCH_HEADER',
235 p_context => 'UPDATE_LAB_IND',
236 p_param1 => SQL%ROWCOUNT,
237 p_app_short_name => 'GMA');
238 EXCEPTION
239 WHEN OTHERS THEN
240 x_exception_count := x_exception_count + 1;
241 gma_common_logging.gma_migration_central_log
242 (p_run_id => p_migration_run_id,
243 p_log_level => fnd_log.level_unexpected,
244 p_message_token => 'GMA_MIGRATION_DB_ERROR',
245 p_table_name => 'GME_BATCH_HEADER',
246 p_context => 'UPDATE_LAB_IND',
247 p_db_error => SQLERRM,
248 p_app_short_name => 'GMA');
249 gma_common_logging.gma_migration_central_log
250 (p_run_id => p_migration_run_id,
251 p_log_level => fnd_log.level_unexpected,
252 p_message_token => 'GMA_MIGRATION_TABLE_FAIL',
253 p_table_name => 'GME_BATCH_HEADER',
254 p_context => 'UPDATE_LAB_IND',
255 p_app_short_name => 'GMA');
256 END update_batch_header;
257
258 PROCEDURE update_wip_entities(p_migration_run_id IN NUMBER,
259 x_exception_count OUT NOCOPY NUMBER) IS
260 l_wip_entity_id NUMBER;
261 l_batch_id NUMBER;
262 l_count NUMBER;
263 l_batch_prefix VARCHAR2(80);
264 l_fpo_prefix VARCHAR2(80);
265 CURSOR get_batches IS
266 SELECT batch_no, b.organization_id, batch_type, v.inventory_item_id
267 FROM gme_batch_header b, gmd_recipe_validity_rules v
268 WHERE b.recipe_validity_rule_id = v.recipe_validity_rule_id(+)
269 AND b.organization_id IS NOT NULL
270 AND DECODE(batch_type, 0, l_batch_prefix, l_fpo_prefix)||batch_no
271 NOT IN (SELECT wip_entity_name
272 FROM wip_entities
273 WHERE organization_id = b.organization_id
274 AND ((b.batch_type = 0 AND entity_type = 10)
275 OR (b.batch_type = 10 AND entity_type = 9)));
276
277 BEGIN
278 l_count := 0;
279 l_batch_prefix := NVL(get_profile_value ('GME_BATCH_PREFIX', 553),'BATCH');
280 l_fpo_prefix := NVL(get_profile_value ('GME_FPO_PREFIX', 553),'FPO');
281 FOR rec IN get_batches LOOP
282 BEGIN
283 INSERT INTO wip_entities
284 (wip_entity_id, organization_id,
285 last_update_date, last_updated_by, creation_date,
286 created_by, last_update_login, request_id,
287 program_application_id, program_id,
288 program_update_date, wip_entity_name,
289 entity_type, description,
290 primary_item_id, gen_object_id
291 )
292 VALUES (wip_entities_s.NEXTVAL,
293 rec.organization_id, --ORGANIZATION_ID
294 SYSDATE, --LAST_UPDATE_DATE
295 1, --LAST_UPDATED_BY,
296 SYSDATE, --CREATION_DATE,
297 1, --CREATED_BY,
298 1, ---LAST_UPDATE_LOGIN,
299 NULL, --REQUEST_ID,
300 NULL, --PROGRAM_APPLICATION_ID,
301 NULL, --PROGRAM_ID,
302 NULL, --PROGRAM_UPDATE_DATE,
303 DECODE (rec.batch_type, 0, l_batch_prefix, l_fpo_prefix)||rec.batch_no, --WIP_ENTITY_NAME,
304 DECODE (rec.batch_type, 0, 10, 10, 9), --ENTITY_TYPE,
305 NULL, --DESCRIPTION,
306 rec.inventory_item_id, --PRIMARY_ITEM_ID,
307 mtl_gen_object_id_s.NEXTVAL); --GEN_OBJECT_ID
308 l_count := l_count + 1;
309 EXCEPTION
310 WHEN OTHERS THEN
311 x_exception_count := x_exception_count + 1;
312 gma_common_logging.gma_migration_central_log
313 (p_run_id => p_migration_run_id,
314 p_log_level => fnd_log.level_unexpected,
315 p_message_token => 'GMA_MIGRATION_DB_ERROR',
316 p_table_name => 'WIP_ENTITIES',
317 p_context => 'CREATE_WIP_ENTITY',
318 p_db_error => SQLERRM,
319 p_app_short_name => 'GMA');
320 gma_common_logging.gma_migration_central_log
321 (p_run_id => p_migration_run_id,
322 p_log_level => fnd_log.level_unexpected,
323 p_message_token => 'GMA_MIGRATION_TABLE_FAIL',
324 p_table_name => 'WIP_ENTITIES',
325 p_context => 'CREATE_WIP_ENTITY',
326 p_app_short_name => 'GMA');
327 END;
328 END LOOP;
329 SELECT MAX (wip_entity_id)
330 INTO l_wip_entity_id
331 FROM wip_entities;
332
333 SELECT MAX (batch_id)
334 INTO l_batch_id
335 FROM gme_batch_header;
336
337 WHILE l_wip_entity_id < l_batch_id LOOP
338 SELECT wip_entities_s.NEXTVAL
339 INTO l_wip_entity_id
340 FROM DUAL;
341 END LOOP;
342 gma_common_logging.gma_migration_central_log
343 (p_run_id => p_migration_run_id,
344 p_log_level => fnd_log.level_procedure,
345 p_message_token => 'GMA_MIGRATION_TABLE_SUCCESS',
346 p_table_name => 'WIP_ENTITIES',
347 p_context => 'CREATE_WIP_ENTITY',
348 p_token1 => 'PARAM1',
349 p_param1 => l_count,
350 p_app_short_name => 'GMA');
351 EXCEPTION
352 WHEN OTHERS THEN
353 x_exception_count := x_exception_count + 1;
354 gma_common_logging.gma_migration_central_log
355 (p_run_id => p_migration_run_id,
356 p_log_level => fnd_log.level_unexpected,
357 p_message_token => 'GMA_MIGRATION_DB_ERROR',
358 p_table_name => 'WIP_ENTITIES',
359 p_context => 'CREATE_WIP_ENTITY',
360 p_db_error => SQLERRM,
361 p_app_short_name => 'GMA');
362 gma_common_logging.gma_migration_central_log
363 (p_run_id => p_migration_run_id,
364 p_log_level => fnd_log.level_unexpected,
365 p_message_token => 'GMA_MIGRATION_TABLE_FAIL',
366 p_table_name => 'WIP_ENTITIES',
367 p_context => 'CREATE_WIP_ENTITY',
368 p_app_short_name => 'GMA',
369 p_db_error => SQLERRM);
370 END update_wip_entities;
371
372 PROCEDURE update_from_doc_no(p_migration_run_id NUMBER) IS
373 BEGIN
374 UPDATE gme_gantt_document_filter
375 SET from_doc_no = document_no;
376 gma_common_logging.gma_migration_central_log
377 (p_run_id => p_migration_run_id,
378 p_log_level => fnd_log.level_procedure,
379 p_message_token => 'GMA_MIGRATION_TABLE_SUCCESS',
380 p_table_name => 'GME_GANTT_DOCUMENT_FILTER',
381 p_context => 'GANTT FILTERS',
382 p_param1 => SQL%ROWCOUNT,
383 p_app_short_name => 'GMA');
384 END update_from_doc_no;
385
386 PROCEDURE update_reason_id(p_migration_run_id NUMBER) IS
387 BEGIN
388 UPDATE gme_resource_txns t
389 SET reason_id = (SELECT reason_id FROM sy_reas_cds_b WHERE reason_code = t.reason_code);
390 gma_common_logging.gma_migration_central_log
391 (p_run_id => p_migration_run_id,
392 p_log_level => fnd_log.level_procedure,
393 p_message_token => 'GMA_MIGRATION_TABLE_SUCCESS',
394 p_table_name => 'GME_RESOURCE_TXNS',
395 p_context => 'REASON_ID',
396 p_param1 => SQL%ROWCOUNT,
397 p_app_short_name => 'GMA');
398 UPDATE gme_resource_txns_mig t
399 SET reason_id = (SELECT reason_id FROM sy_reas_cds_b WHERE reason_code = t.reason_code);
400 gma_common_logging.gma_migration_central_log
401 (p_run_id => p_migration_run_id,
402 p_log_level => fnd_log.level_procedure,
403 p_message_token => 'GMA_MIGRATION_TABLE_SUCCESS',
404 p_table_name => 'GME_RESOURCE_TXNS_MIG',
405 p_context => 'REASON_ID',
406 p_param1 => SQL%ROWCOUNT,
407 p_app_short_name => 'GMA');
408 END update_reason_id;
409 END gme_transform_batch;