Una de las tarea repetitiva que se tienen que efectuar en algunos desarrollos para implementacion y o mentenimiento siempre es actualizar tablas en funcion a informacion de otras.
Yo suelo crear un cursor de la tabla a actualizar , hacer todas las busquedas ( select ) a otras tablas y finalmente actulizar la tabla.
Buscando una menera mas efectiva y sobre todo mas simple pude hacer lo que queria con un solo comando SQL.
Aqui la forma 01
CURSOR C_ca IS
select *
from CATEGORIA_ORGANIZACION
Where CATEGORIA_ORGANIZACION .attribute13 is null ;
BEGIN
for reg in C_ca Loop
Update attribute13 = reg.attribute13,
CATEGORY_SET_ID = reg.CATEGORY_SET_ID,
SUBINVENTORY_CODE = reg.SUBINVENTORY_CODE,
MATERIAL_ACCOUNT = reg.MATERIAL_ACCOUNT,
MATERIAL_OVERHEAD_ACCOUNT = reg.MATERIAL_OVERHEAD_ACCOUNT,
RESOURCE_ACCOUNT = reg.RESOURCE_ACCOUNT,
OUTSIDE_PROCESSING_ACCOUNT = reg.OUTSIDE_PROCESSING_ACCOUNT,
OVERHEAD_ACCOUNT = reg.OVERHEAD_ACCOUNT ,
EXPENSE_ACCOUNT = reg.EXPENSE_ACCOUNT,
ENCUMBRANCE_ACCOUNT = reg.ENCUMBRANCE_ACCOUNT,
BRIDGING_ACCOUNT = reg.BRIDGING_ACCOUNT,
REQUEST_ID = reg.REQUEST_ID ,
PROGRAM_APPLICATION_ID = reg.PROGRAM_APPLICATION_ID,
PROGRAM_ID = reg.PROGRAM_ID,
PROGRAM_UPDATE_DATE = reg.PROGRAM_UPDATE_DATE,
COST_GROUP_ID = reg.COST_GROUP_ID ,
ANALYTICAL_IPV = reg.ANALYTICAL_IPV,
ANALYTICAL_PO_MIRROR = reg.ANALYTICAL_PO_MIRROR,
NON_INVOICED_SO = reg.NON_INVOICED_SO,
NON_INVOICED_REVENUE = reg.NON_INVOICED_REVENUE,
ANALYTICAL_REVENUE_MIRROR = reg.ANALYTICAL_REVENUE_MIRROR,
ANALYTICAL_MOGS = reg.ANALYTICAL_MOGS,
AVERAGE_COST_VAR_ACCOUNT = reg.AVERAGE_COST_VAR_ACCOUNT
where not ok.attribute13 is null
and not ok.material_account is null
and ok.category_id = reg.category_id
and ok.location_id = reg.location_id
commit;
end loop;
Aqui la forma 02
update CATEGORIA_ORGANIZACION
set ( attribute13,
CATEGORY_SET_ID,
SUBINVENTORY_CODE,
MATERIAL_ACCOUNT,
MATERIAL_OVERHEAD_ACCOUNT,
RESOURCE_ACCOUNT,
OUTSIDE_PROCESSING_ACCOUNT,
OVERHEAD_ACCOUNT,
EXPENSE_ACCOUNT,
ENCUMBRANCE_ACCOUNT,
BRIDGING_ACCOUNT,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
COST_GROUP_ID,
ANALYTICAL_IPV,
ANALYTICAL_PO_MIRROR,
NON_INVOICED_SO,
NON_INVOICED_REVENUE,
ANALYTICAL_REVENUE_MIRROR,
ANALYTICAL_MOGS,
AVERAGE_COST_VAR_ACCOUNT)
= ( select attribute13,
CATEGORY_SET_ID,
SUBINVENTORY_CODE,
MATERIAL_ACCOUNT,
MATERIAL_OVERHEAD_ACCOUNT,
RESOURCE_ACCOUNT,
OUTSIDE_PROCESSING_ACCOUNT,
OVERHEAD_ACCOUNT,
EXPENSE_ACCOUNT,
ENCUMBRANCE_ACCOUNT,
BRIDGING_ACCOUNT,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
COST_GROUP_ID,
ANALYTICAL_IPV,
ANALYTICAL_PO_MIRROR,
NON_INVOICED_SO,
NON_INVOICED_REVENUE,
ANALYTICAL_REVENUE_MIRROR,
ANALYTICAL_MOGS,
AVERAGE_COST_VAR_ACCOUNT
from CATEGORIA_ORGANIZACION ok
where not ok.attribute13 is null
and not ok.material_account is null
and ok.category_id = CATEGORIA_ORGANIZACION.category_id
and ok.location_id = CATEGORIA_ORGANIZACION.location_id
)
where CATEGORIA_ORGANIZACION .attribute13 is null ;
Commit;
Debo añadir que se debe tener especial consideracion en la segunda opcion ya que si estamos en un ambiente transacional , este tipo de update Masivo bloqueara la informacion que se esta actualizando hasta el final mientras que la primera ejecuta commit registro por registro.
Si a alguien le parecen conocidos estos campos es porque pertenecen a eBusiness Suite (gl_code_combinations).
Ciao
