[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_context => 'UPDATE_LAB_IND',
p_app_short_name => 'GMA');
update_batch_header(p_migration_run_id => p_migration_run_id,
x_exception_count => x_failure_count);
update_wip_entities(p_migration_run_id => p_migration_run_id,
x_exception_count => x_failure_count);
update_from_doc_no(p_migration_run_id);
update_reason_id(p_migration_run_id);
SELECT fpov.profile_option_value
FROM fnd_profile_options fpo,
fnd_profile_option_values fpov
WHERE fpo.application_id = c_appl_id
AND fpo.profile_option_name = c_profile_name
AND fpo.profile_option_id = fpov.profile_option_id
AND fpo.application_id = fpov.application_id
AND fpov.level_id = 10001
AND fpov.level_value = 0;
SELECT sy.organization_id, sy.orgn_code plant_code
FROM sy_orgn_mst sy
WHERE NOT EXISTS (SELECT 1
FROM gme_parameters
WHERE organization_id = sy.organization_id)
AND sy.organization_id IS NOT NULL;
SELECT assignment_type, last_assigned
FROM sy_docs_seq
WHERE orgn_code = v_plant_code AND doc_type = v_doc_type;
INSERT INTO gme_parameters
(organization_id, auto_consume_supply_sub_only,
supply_subinventory, supply_locator_id,
yield_subinventory, yield_locator_id,
delete_material_ind,
validate_plan_dates_ind,
display_unconsumed_material,
step_controls_batch_sts_ind,
backflush_rsrc_usg_ind,
def_actual_rsrc_usg_ind,
calc_interim_rsrc_usg_ind,
allow_qty_below_min_ind,
display_non_work_days_ind,
check_shortages_ind,
copy_formula_text_ind,
copy_routing_text_ind,
ib_factor_ind,
create_high_level_resv_ind, create_move_orders_ind,
reservation_timefence, move_order_timefence,
batch_doc_numbering,
batch_no_last_assigned, fpo_doc_numbering,
fpo_no_last_assigned, created_by, creation_date,
last_updated_by, last_update_login, last_update_date
)
VALUES (rec.organization_id,
0, -- AUTO_CONSUME_SUPPLY_SUB_ONLY,
NULL, -- SUPPLY_SUBINVENTORY
NULL, -- SUPPLY_LOCATOR_ID
NULL, -- YIELD_SUBINVETORY
NULL, --YIELD_LOCATOR_ID
NVL(get_profile_value ('GME_ALLOW_MATERIAL_DELETION', 553),1),
NVL(get_profile_value ('GME_VALIDATE_PLAN_DATES', 553),1), --VALIDATE_PLAN_DATES_IND
1, --DISPLAY_UNCONSUMED_MATERIAL
NVL(DECODE (get_profile_value ('GME_STEP_CONTROL', 553), 'N', 0, 'Y', 1, 0),0),--STEP_CONTROLS_BATCH_STS_IND
NVL(get_profile_value ('GME_BACKFLUSH_USAGE', 553),0), --BACKFLUSH_RSRC_USG_IND
NVL(get_profile_value ('PM$DEFAULT_ACTUAL_RESOURCE_USAGE', 550),1), --DEF_ACTUAL_RSRC_USG_IND
NVL(get_profile_value ('GME_CALC_INT_RSRC_USAGE', 553),0), --CALC_INTERIM_RSRC_USG_IND
NVL(get_profile_value ('GME_ALLOW_QTY_BELOW_CAP', 553),1), --ALLOW_QTY_BELOW_MIN_IND
NVL(get_profile_value ('GME_DISP_NON_WORKING_DAYS_IN_GANTT', 553),1), --DISPLAY_NON_WORK_DAYS_IND
NVL(get_profile_value ('PM$CHECK_INV_SAVE', 550),0), --CHECK_SHORTAGES_IND
NVL(get_profile_value ('PM_COPY_FM_TEXT', 550),1), --COPY_FORMULA_TEXT_IND
NVL(get_profile_value ('GME_COPY_ROUTING_TEXT', 553),1), --COPY_ROUTING_TEXT_IND
NVL(get_profile_value ('GME_IB_FACTOR',553),0), --IB_FACTOR_IND
0, --CREATE_HIGH_LEVEL_RESV_IND
0, --CREATE_MOVE_ORDERS_IND
NULL, --RESERVATION_TIMEFENCE
NULL, --MOVE_ORDER_TIMEFENCE
l_batch_assignment, --BATCH_DOC_NUMBERING
l_batch_number, --BATCH_NO_LAST_ASSIGNED
l_fpo_assignment, --FPO_DOC_NUMBERING
l_fpo_number, --FPO_NO_LAST_ASSIGNED
-1, --created_by
SYSDATE, --creation_date
-1, --last_updated_by
NULL, --last_update_login
SYSDATE --last_updated_date
);
PROCEDURE update_batch_header(p_migration_run_id IN NUMBER,
x_exception_count OUT NOCOPY NUMBER) IS
BEGIN
UPDATE gme_batch_header h
SET laboratory_ind = (SELECT DECODE (org.plant_ind, 1, 0, 2, 1)
FROM sy_orgn_mst org
WHERE org.orgn_code = h.plant_code),
migrated_batch_ind = 'Y'
WHERE laboratory_ind IS NULL;
p_context => 'UPDATE_LAB_IND',
p_param1 => SQL%ROWCOUNT,
p_app_short_name => 'GMA');
p_context => 'UPDATE_LAB_IND',
p_db_error => SQLERRM,
p_app_short_name => 'GMA');
p_context => 'UPDATE_LAB_IND',
p_app_short_name => 'GMA');
END update_batch_header;
PROCEDURE update_wip_entities(p_migration_run_id IN NUMBER,
x_exception_count OUT NOCOPY NUMBER) IS
l_wip_entity_id NUMBER;
SELECT batch_no, b.organization_id, batch_type, v.inventory_item_id
FROM gme_batch_header b, gmd_recipe_validity_rules v
WHERE b.recipe_validity_rule_id = v.recipe_validity_rule_id(+)
AND b.organization_id IS NOT NULL
AND DECODE(batch_type, 0, l_batch_prefix, l_fpo_prefix)||batch_no
NOT IN (SELECT wip_entity_name
FROM wip_entities
WHERE organization_id = b.organization_id
AND ((b.batch_type = 0 AND entity_type = 10)
OR (b.batch_type = 10 AND entity_type = 9)));
SELECT NVL(max(wip_entity_id), 0)
FROM wip_entities;
SELECT NVL(min(wip_entity_id), 0)
FROM gme_batch_header b, wip_entities w
WHERE b.organization_id IS NOT NULL
AND DECODE(batch_type, 0, l_batch_prefix, l_fpo_prefix)||batch_no = w.wip_entity_name
and w.organization_id = b.organization_id
AND ((b.batch_type = 0 AND entity_type = 10)
OR (b.batch_type = 10 AND entity_type = 9));
SELECT batch_no, b.organization_id, batch_type, v.inventory_item_id
FROM gme_batch_header b, gmd_recipe_validity_rules v
WHERE b.recipe_validity_rule_id = v.recipe_validity_rule_id(+)
AND b.organization_id IS NOT NULL
MINUS
SELECT batch_no, b.organization_id, batch_type, v.inventory_item_id
FROM gme_batch_header b, gmd_recipe_validity_rules v,
(SELECT wip_entity_name, organization_id, entity_type
FROM wip_entities
WHERE wip_entity_id > l_min_wip_entity_id
AND entity_type IN (9, 10)) w
WHERE b.recipe_validity_rule_id = v.recipe_validity_rule_id(+)
AND b.organization_id IS NOT NULL
AND w.wip_entity_name = DECODE(batch_type, 0, l_batch_prefix, l_fpo_prefix)||batch_no
AND w.organization_id = b.organization_id
AND ((b.batch_type = 0 AND w.entity_type = 10)
OR (b.batch_type = 10 AND w.entity_type = 9));
INSERT INTO wip_entities
(wip_entity_id, organization_id,
last_update_date, last_updated_by, creation_date,
created_by, last_update_login, request_id,
program_application_id, program_id,
program_update_date, wip_entity_name,
entity_type, description,
primary_item_id, gen_object_id
)
VALUES (wip_entities_s.NEXTVAL,
rec.organization_id, --ORGANIZATION_ID
SYSDATE, --LAST_UPDATE_DATE
1, --LAST_UPDATED_BY,
SYSDATE, --CREATION_DATE,
1, --CREATED_BY,
1, ---LAST_UPDATE_LOGIN,
NULL, --REQUEST_ID,
NULL, --PROGRAM_APPLICATION_ID,
NULL, --PROGRAM_ID,
NULL, --PROGRAM_UPDATE_DATE,
DECODE (rec.batch_type, 0, l_batch_prefix, l_fpo_prefix)||rec.batch_no, --WIP_ENTITY_NAME,
DECODE (rec.batch_type, 0, 10, 10, 9), --ENTITY_TYPE,
NULL, --DESCRIPTION,
rec.inventory_item_id, --PRIMARY_ITEM_ID,
mtl_gen_object_id_s.NEXTVAL); --GEN_OBJECT_ID
SELECT MAX (wip_entity_id)
INTO l_wip_entity_id
FROM wip_entities;
SELECT MAX (batch_id)
INTO l_batch_id
FROM gme_batch_header;
SELECT wip_entities_s.NEXTVAL
INTO l_wip_entity_id
FROM DUAL;
END update_wip_entities;
PROCEDURE update_from_doc_no(p_migration_run_id NUMBER) IS
BEGIN
UPDATE gme_gantt_document_filter
SET from_doc_no = document_no;
END update_from_doc_no;
PROCEDURE update_reason_id(p_migration_run_id NUMBER) IS
BEGIN
UPDATE gme_resource_txns t
SET reason_id = (SELECT reason_id FROM sy_reas_cds_b WHERE reason_code = t.reason_code);
UPDATE gme_resource_txns_mig t
SET reason_id = (SELECT reason_id FROM sy_reas_cds_b WHERE reason_code = t.reason_code);
END update_reason_id;