Buenas practicas
de programacion!
Va a ser cortito y al pie
Basicamente
- Naming convention
- Tabulaciones
- Comentarios
Copiar
sin
leer
y
entender
Programar
sin definir
el algoritmo
previamente
Mucha explicacion
Explicar lo obvio
Nombres descriptivos
Que indiquen el contexto
No abreviar si no es necesario
Para variables i, j, k, m, n
Package
edu.cmu.cs.bovik.cheese
Metodo
envArchFormXml
PL/SQL
Variables
- p - para parametros
- l - variables locales
- g - variables globales
spool XX_INV_PROPICKALLITEMS_PK.pks.log
REM +==========================================================================+
REM | Copyright (c) 2014 Watea!, Buenos Aires, Argentina |
REM | All rights reserved. |
REM +==========================================================================+
REM | FILENAME |
REM | XX_INV_PROPICKALLITEMS_PK.sql |
REM | |
REM | |
REM | LANGUAGE |
REM | PL/SQL |
REM | |
REM | PRODUCT |
REM | Oracle Financials |
REM | |
REM | SOURCE CONTROL |
REM | Version: $Revision: 2.0 $ |
REM | Fecha : $Date: 2010-06-16 16:45:34 -0300 (mié, 16 jun 2010) $ |
REM | |
REM | HISTORY |
REM | 09-MAY-2011 El gran Juani Creado |
REM +==========================================================================+
PROMPT CREATE OR REPLACE PACKAGE XX_INV_PROPICKALLITEMS_PK
CREATE OR REPLACE PACKAGE XX_INV_PROPICKALLITEMS_PK IS
PROCEDURE MAIN (errbuf OUT VARCHAR2
,retcode OUT NUMBER);
PROCEDURE PICK_CONFIRM (p_transferencia_id IN NUMBER
,p_move_order_header_id IN NUMBER
,p_move_order_line_id IN NUMBER
,p_subinventory_code IN VARCHAR2
,p_transfer_subinventory IN VARCHAR2
,p_transaction_temp_id IN NUMBER
,p_wip_entity_name IN VARCHAR2
,p_wip_entity_id IN NUMBER
,p_cantidad IN NUMBER
,p_quantity_delivered IN NUMBER
,p_quantity_detailed IN NUMBER
,p_status OUT VARCHAR2
,p_error OUT VARCHAR2);
PROCEDURE PICK_PARCIAL (p_transferencia_id IN NUMBER
,p_transaction_temp_id IN NUMBER
,p_cantidad IN NUMBER
,p_wip_entity_name IN VARCHAR2
,p_wip_entity_id IN NUMBER
,p_move_order_header_id IN NUMBER
,p_move_order_line_id IN NUMBER
,p_subinventory_code IN VARCHAR2
,p_transfer_subinventory IN VARCHAR2
,p_transaction_quantity IN NUMBER
,p_quantity_delivered IN NUMBER
,p_quantity_detailed IN NUMBER
,p_status OUT VARCHAR2
,p_error OUT VARCHAR2);
PROCEDURE EXCEDENTES (p_wip_entity_name IN VARCHAR2
,p_wip_entity_id IN NUMBER
,p_organization_id IN NUMBER
,p_inventory_item_id IN NUMBER
,p_subinventario_ori IN VARCHAR2
,p_locator_ori IN NUMBER
,p_cantidad IN NUMBER
,p_transaction_uom IN VARCHAR2
,p_status OUT VARCHAR2
,p_error OUT VARCHAR2);
END XX_INV_PROPICKALLITEMS_PK
/
SHOW ERR
SPOOL OFF
EXIT
Package Specs
Package Body
spool XX_INV_PROPICKALLITEMS_PK.pkb.log
REM +==========================================================================+
REM | Copyright (c) 2014 Watea!, Buenos Aires, Argentina |
REM | All rights reserved. |
REM +==========================================================================+
REM | FILENAME |
REM | XX_INV_PROPICKALLITEMS_PK.sql |
REM | |
REM | |
REM | LANGUAGE |
REM | PL/SQL |
REM | |
REM | PRODUCT |
REM | Oracle Financials |
REM | |
REM | SOURCE CONTROL |
REM | Version: $Revision: 2.0 $ |
REM | Fecha : $Date: 2010-06-16 16:45:34 -0300 (mié, 16 jun 2010) $ |
REM | |
REM | HISTORY |
REM | 09-MAY-2011 San Juani Creado |
REM +==========================================================================+
PROMPT CREATE OR REPLACE PACKAGE XX_INV_PROPICKALLITEMS_PK
CREATE OR REPLACE PACKAGE BODY APPS.XX_INV_PROPICKALLITEMS_PK IS
PROCEDURE MAIN (errbuf OUT VARCHAR2
,retcode OUT NUMBER) IS
CURSOR c_principal_no_temp IS
SELECT t."IdMoveOrderLine"
,t."Cantidad"
,t."IdMaterial"
,t."Id"
,t."IdOrdenProduccion"
,(select wip_entity_id
from wip_entities
where organization_id = msib.organization_id
and wip_entity_name = t."IdOrdenProduccion") wip_entity_id
,mp_ori.organization_id
,msib.inventory_item_id
,msib.primary_uom_code
,msi_ori.secondary_inventory_name subinv_ori
,msi_des.secondary_inventory_name subinv_des
,(select mil.inventory_location_id
from mtl_item_locations mil
where mil.organization_id = msi_ori.organization_id
and mil.subinventory_code = msi_ori.secondary_inventory_name) locator_id
,mtrl.uom_code
FROM xx_transferencias_tmp t
,mtl_parameters mp_ori
,mtl_secondary_inventories msi_ori
,mtl_parameters mp_des
,mtl_secondary_inventories msi_des
,mtl_system_items_b msib
,mtl_txn_request_lines mtrl
WHERE t."IdOrganizacionOrigen" = mp_ori.organization_code
AND t."IdAlmacenOrigen" = msi_ori.secondary_inventory_name
AND mp_ori.organization_id = msi_ori.organization_id
AND t."IdOrganizacionDestino" = mp_des.organization_code
AND t."IdAlmacenDestino" = msi_des.secondary_inventory_name
AND mp_des.organization_id = msi_des.organization_id
AND t."IdMaterial" = msib.segment1
AND mp_ori.organization_id = msib.organization_id
AND mtrl.line_id = t."IdMoveOrderLine"
AND t."DocumentoOrigenTipo" = 'OP'
AND t."IdEstado" = 0
AND t."IdMoveOrder" IS NOT NULL
AND NOT EXISTS ( SELECT 1
FROM mtl_material_transactions_temp
WHERE move_order_line_id = t."IdMoveOrderLine" );
CURSOR c_principal IS
SELECT t."IdMoveOrderLine"
,t."Cantidad"
,t."IdMaterial"
,t."Id"
,t."IdOrdenProduccion"
,mp_ori.organization_id
,msib.inventory_item_id
,msib.primary_uom_code
,msi_ori.secondary_inventory_name subinv_ori
,msi_des.secondary_inventory_name subinv_des
,t."IdMoveOrderLine" moli_transf
,mmtt.move_order_line_id moli_temp
,wdj.date_released
,we.wip_entity_id
,we.wip_entity_name
,mmtt.transaction_quantity
,mmtt.primary_quantity
,mmtt.transaction_uom
,mmtt.locator_id
,mmtt.transaction_temp_id
,mmtt.subinventory_code
,mmtt.transfer_subinventory
,nvl(mtrl.quantity_delivered,0) quantity_delivered
,nvl(mtrl.quantity_detailed,0) quantity_detailed
,mtrl.header_id
FROM xx_transferencias_tmp t
,mtl_parameters mp_ori
,mtl_secondary_inventories msi_ori
,mtl_parameters mp_des
,mtl_secondary_inventories msi_des
,mtl_system_items_b msib
,mtl_material_transactions_temp mmtt
,wip_entities we
,wip_discrete_jobs wdj
,mtl_txn_request_lines mtrl
WHERE t."IdOrganizacionOrigen" = mp_ori.organization_code
AND t."IdAlmacenOrigen" = msi_ori.secondary_inventory_name
AND mp_ori.organization_id = msi_ori.organization_id
AND t."IdOrganizacionDestino" = mp_des.organization_code
AND t."IdAlmacenDestino" = msi_des.secondary_inventory_name
AND mp_des.organization_id = msi_des.organization_id
AND t."IdMaterial" = msib.segment1
AND mp_ori.organization_id = msib.organization_id
AND t."DocumentoOrigenTipo" = 'OP'
AND t."IdEstado" = 0
AND t."IdMoveOrder" IS NOT NULL
AND t."IdMoveOrderLine" = mmtt.move_order_line_id
AND mmtt.organization_id = msib.organization_id
AND we.wip_entity_name = t."IdOrdenProduccion"
AND we.organization_id = msi_ori.organization_id
AND we.wip_entity_id = wdj.wip_entity_id
AND mmtt.subinventory_code = msi_ori.secondary_inventory_name
AND mmtt.transfer_subinventory = msi_des.secondary_inventory_name
AND mmtt.transaction_header_id IS NOT NULL
AND mmtt.move_order_header_id IS NOT NULL
-- No levantar reservas de lineas de MO cerradas
AND mmtt.move_order_line_id NOT IN (SELECT line_id FROM mtl_txn_request_lines WHERE line_status = 5)
AND mtrl.line_id = t."IdMoveOrderLine";
CURSOR c_otras_mo (p_inventory_item_id NUMBER
,p_transferencia_id NUMBER) IS
SELECT t."IdMoveOrderLine"
,t."Cantidad"
,t."IdMaterial"
,t."Id"
,t."IdOrdenProduccion"
,mp_ori.organization_id
,msib.inventory_item_id
,msib.primary_uom_code
,msi_ori.secondary_inventory_name subinv_ori
,msi_des.secondary_inventory_name subinv_des
,t."IdMoveOrderLine" moli_transf
,mmtt.move_order_line_id moli_temp
,mmtt.move_order_header_id mohe_temp
,wdj.date_released
,we.wip_entity_id
,we.wip_entity_name
,we_otra.wip_entity_name wip_entity_name_otra
,we_otra.wip_entity_id wip_entity_id_otra
,mmtt.transaction_quantity
,mmtt.primary_quantity
,mmtt.transaction_uom
,mmtt.locator_id
,mmtt.transaction_temp_id
,mmtt.subinventory_code
,mmtt.transfer_subinventory
,nvl(mtrl.quantity_delivered,0) quantity_delivered
,nvl(mtrl.quantity_detailed,0) quantity_detailed
,mtrl.header_id
FROM xx_transferencias_tmp t
,mtl_parameters mp_ori
,mtl_secondary_inventories msi_ori
,mtl_parameters mp_des
,mtl_secondary_inventories msi_des
,mtl_system_items_b msib
,mtl_material_transactions_temp mmtt
,wip_entities we
,wip_discrete_jobs wdj
,mtl_txn_request_lines mtrl
,wip_entities we_otra
,mtl_txn_request_lines mtrl_otra
WHERE t."IdOrganizacionOrigen" = mp_ori.organization_code
AND t."IdAlmacenOrigen" = msi_ori.secondary_inventory_name
AND mp_ori.organization_id = msi_ori.organization_id
AND t."IdOrganizacionDestino" = mp_des.organization_code
AND t."IdAlmacenDestino" = msi_des.secondary_inventory_name
AND mp_des.organization_id = msi_des.organization_id
AND t."IdMaterial" = msib.segment1
AND mp_ori.organization_id = msib.organization_id
AND t."DocumentoOrigenTipo" = 'OP'
AND t."IdEstado" = 0
AND t."IdMoveOrder" IS NOT NULL
AND mmtt.inventory_item_id = msib.inventory_item_id
AND mmtt.organization_id = msib.organization_id
AND t."IdMoveOrderLine" != mmtt.move_order_line_id
AND we.wip_entity_name = t."IdOrdenProduccion"
AND we.organization_id = msi_ori.organization_id
AND we.wip_entity_id = wdj.wip_entity_id
AND mmtt.subinventory_code = msi_ori.secondary_inventory_name
AND mmtt.transfer_subinventory = msi_des.secondary_inventory_name
AND mmtt.transaction_header_id IS NOT NULL
AND mmtt.move_order_header_id IS NOT NULL
AND mtrl.line_id = t."IdMoveOrderLine"
AND we_otra.wip_entity_id = mtrl_otra.txn_source_id
AND mtrl_otra.line_id = mmtt.move_order_line_id
AND msib.inventory_item_id = p_inventory_item_id
AND t."Id" = p_transferencia_id
ORDER BY wdj.date_released;
-- Variables para seteo de entorno
l_user_id NUMBER;
l_resp_id NUMBER;
l_resp_appl_id NUMBER;
-- Bandera y Excedente
l_picking_parcial VARCHAR2(5);
l_picking_parcial_otras VARCHAR2(5);
l_cant_excedente NUMBER;
l_cant_excedente_otras NUMBER;
-- Excepciones y Errores
e_errores1 EXCEPTION;
e_errores2 EXCEPTION;
e_errores3 EXCEPTION;
l_errores VARCHAR2(10000);
l_status_pick VARCHAR2(1);
l_error_pick VARCHAR2(1000);
l_status_parcial VARCHAR2(1);
l_error_parcial VARCHAR2(1000);
l_status_exc VARCHAR2(1);
l_error_exc VARCHAR2(1000);
l_status_up_tra VARCHAR2(1);
l_error_up_tra VARCHAR2(1000);
l_status_valida_pick VARCHAR2(1);
l_error_valida_pick VARCHAR2(1000);
l_mensaje_error VARCHAR2(1000);
l_transaction_quantity NUMBER;
l_quantity_delivered NUMBER;
l_quantity_detailed NUMBER;
l_transaction_quantity_otras NUMBER;
l_quantity_delivered_otras NUMBER;
l_quantity_detailed_otras NUMBER;
BEGIN
-- Seteo de variables, lenguaje, usuario, aplicacion y responsabilidad --
l_user_id := fnd_profile.value('USER_ID');
l_resp_id := fnd_profile.value('RESP_ID');
l_resp_appl_id := fnd_profile.value('RESP_APPL_ID');
EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA = "APPS"';
fnd_global.apps_initialize(l_user_id, l_resp_id, l_resp_appl_id);
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_LANGUAGE = ''LATIN AMERICAN SPANISH''';
DELETE
FROM mtl_material_transactions_temp
WHERE transaction_type_id = 51
AND move_order_line_id IN (SELECT line_id
FROM mtl_txn_request_lines
WHERE line_status = 5);
COMMIT;
-- Inserta en tablas temporales locales
BEGIN
INSERT INTO xx_transferencias_tmp ( SELECT *
FROM transferencias
WHERE "IdEstado" = 0
AND "DocumentoOrigenTipo" = 'OP'
AND "IdOrdenProduccion" IS NOT NULL
AND "IdMoveOrder" IS NOT NULL
AND "IdMoveOrderLine" IS NOT NULL );
EXCEPTION WHEN OTHERS THEN
l_errores := '1 - Codigo: '||SQLCODE||', Error: '||SQLERRM;
RAISE e_errores1;
END;
-- Cursor principal
FOR r_principal IN c_principal LOOP
l_picking_parcial := 'FALSE';
l_cant_excedente := 0;
l_errores := '';
l_transaction_quantity := 0;
l_quantity_delivered := 0;
l_quantity_detailed := 0;
--Main
BEGIN
-- Recupera nuevamente las cantidades
BEGIN
SELECT nvl(mmtt.transaction_quantity,0) transaction_quantity
,nvl(mtrl.quantity_delivered,0) quantity_delivered
,nvl(mtrl.quantity_detailed,0) quantity_detailed
INTO l_transaction_quantity
,l_quantity_delivered
,l_quantity_detailed
FROM mtl_material_transactions_temp mmtt
,mtl_txn_request_lines mtrl
WHERE mmtt.move_order_line_id = mtrl.line_id
AND mmtt.move_order_header_id = r_principal.header_id
AND mmtt.move_order_line_id = r_principal."IdMoveOrderLine"
AND mmtt.subinventory_code = r_principal.subinv_ori
AND mmtt.transfer_subinventory = r_principal.subinv_des
AND mmtt.transaction_header_id IS NOT NULL
AND mmtt.transaction_quantity > 0;
EXCEPTION WHEN OTHERS THEN
l_errores := '1.5 - Codigo: '||SQLCODE||', Error: '||SQLERRM;
l_transaction_quantity := 0;
END;
-- If Principal. Existe Temp para la MO line principal?
IF (l_transaction_quantity > 0) THEN
-- Actualizo bandera de picking parcial y calculo excedente si hay
IF (r_principal."Cantidad" < l_transaction_quantity) THEN
l_picking_parcial := 'TRUE';
ELSE
l_picking_parcial := 'FALSE';
l_cant_excedente := r_principal."Cantidad" - l_transaction_quantity;
END IF;
-- Si consume toda la MO original y tiene remanente
IF l_picking_parcial = 'FALSE' AND l_cant_excedente > 0 THEN
-- Realiza el pick confirm por la cantidad total de la linea de la MO original
PICK_CONFIRM (r_principal."Id"
,r_principal.header_id
,r_principal.moli_transf
,r_principal.subinv_ori
,r_principal.subinv_des
,r_principal.transaction_temp_id
,r_principal.wip_entity_name
,r_principal.wip_entity_id
,l_transaction_quantity
,l_quantity_delivered
,l_quantity_detailed
,l_status_pick
,l_error_pick);
-- Verifica el estado
IF l_status_pick != 'S' THEN
l_errores := l_error_pick;
RAISE e_errores2;
END IF;
l_cant_excedente_otras := l_cant_excedente;
-- Busca reservas del item en otras MO y los consume
FOR r_otras_mo IN c_otras_mo (r_principal.inventory_item_id
,r_principal."Id") LOOP
l_picking_parcial_otras := 'FALSE';
l_errores := '';
l_transaction_quantity_otras := 0;
l_quantity_delivered_otras := 0;
l_quantity_detailed_otras := 0;
-- Existe registro de la MMTT que se tiene que confirmar?
BEGIN
SELECT mmtt.transaction_quantity
,nvl(mtrl.quantity_delivered,0) quantity_delivered
,nvl(mtrl.quantity_detailed,0) quantity_detailed
INTO l_transaction_quantity_otras
,l_quantity_delivered_otras
,l_quantity_detailed_otras
FROM mtl_material_transactions_temp mmtt
,mtl_txn_request_lines mtrl
WHERE mmtt.move_order_line_id = mtrl.line_id
AND mmtt.move_order_header_id = r_otras_mo.mohe_temp
AND mmtt.move_order_line_id = r_otras_mo.moli_temp
AND mmtt.subinventory_code = r_otras_mo.subinv_ori
AND mmtt.transfer_subinventory = r_otras_mo.subinv_des
AND mmtt.transaction_header_id IS NOT NULL
AND mmtt.transaction_quantity > 0;
EXCEPTION WHEN OTHERS THEN
l_errores := '1.5.1 - Codigo: '||SQLCODE||', Error: '||SQLERRM;
l_transaction_quantity_otras := 0;
END;
-- If Principal. Existe Temp para la MO line secundaria?
IF l_transaction_quantity_otras > 0 THEN
-- Actualizo bandera de picking parcial y calculo excedente si hay
IF (l_cant_excedente_otras < l_transaction_quantity_otras) THEN
l_picking_parcial_otras := 'TRUE';
ELSE
l_picking_parcial_otras := 'FALSE';
-- Va actualizando la cantidad excedente
l_cant_excedente_otras := l_cant_excedente_otras - l_transaction_quantity_otras;
END IF;
-- Si consume toda la cantidad de la linea de la MO secundaria y tiene remanente
IF l_picking_parcial_otras = 'FALSE' AND l_cant_excedente_otras > 0 THEN
-- Realiza el pick confirm por la cantidad total de la linea de la MO secundaria
PICK_CONFIRM (r_otras_mo."Id"
,r_otras_mo.mohe_temp
,r_otras_mo.moli_temp
,r_otras_mo.subinv_ori
,r_otras_mo.subinv_des
,r_otras_mo.transaction_temp_id
,r_otras_mo.wip_entity_name_otra
,r_otras_mo.wip_entity_id_otra
,l_transaction_quantity_otras
,l_quantity_delivered_otras
,l_quantity_detailed_otras
,l_status_pick
,l_error_pick);
-- Verifica el estado
IF l_status_pick != 'S' THEN
l_errores := l_error_pick;
RAISE e_errores2;
END IF;
-- Si consume toda la cantidad de la linea de la MO secundaria y NO tiene remanente
ELSIF l_picking_parcial_otras = 'FALSE' THEN
-- Realiza el pick confirm por la cantidad total de la linea de la MO secundaria
PICK_CONFIRM (r_otras_mo."Id"
,r_otras_mo.mohe_temp
,r_otras_mo.moli_temp
,r_otras_mo.subinv_ori
,r_otras_mo.subinv_des
,r_otras_mo.transaction_temp_id
,r_otras_mo.wip_entity_name_otra
,r_otras_mo.wip_entity_id_otra
,r_otras_mo."Cantidad"
,l_quantity_delivered_otras
,l_quantity_detailed_otras
,l_status_pick
,l_error_pick);
-- Verifica el estado
IF l_status_pick != 'S' THEN
l_errores := l_error_pick;
RAISE e_errores2;
END IF;
-- No hay mas excendente
l_cant_excedente_otras := 0;
--sale del LOOP r_otras_mo, ya que no hace falta que siga buscando mas reservas
exit;
-- Si no llega a consumir toda la cantidad de la MO secundaria
ELSIF l_picking_parcial_otras = 'TRUE' THEN
-- Realiza el pick parcial de la MO secundaria
PICK_PARCIAL (r_otras_mo."Id"
,r_otras_mo.transaction_temp_id
,l_cant_excedente_otras
,r_otras_mo.wip_entity_name_otra
,r_otras_mo.wip_entity_id_otra
,r_otras_mo.mohe_temp
,r_otras_mo.moli_temp
,r_otras_mo.subinv_ori
,r_otras_mo.subinv_des
,l_transaction_quantity_otras
,l_quantity_delivered_otras
,l_quantity_detailed_otras
,l_status_parcial
,l_error_parcial);
-- Verifica el estado
IF l_status_parcial != 'S' THEN
l_errores := l_error_parcial;
RAISE e_errores2;
END IF;
-- No hay mas excendente
l_cant_excedente_otras := 0;
--sale del LOOP r_otras_mo, ya que no hace falta que siga buscando mas reservas
exit;
END IF;
END IF;
-- Realiza la validacion de los datos
VALIDA_PICK_CONFIRM (r_otras_mo.mohe_temp
,l_status_valida_pick
,l_error_valida_pick);
-- Verifica el estado
IF l_status_valida_pick != 'S' THEN
l_errores := l_error_valida_pick;
RAISE e_errores2;
END IF;
END LOOP;
-- Si queda excedente y no tiene mas reservas en MOs transfiere el remanente a EXCEDENTESx
IF l_cant_excedente_otras > 0 THEN
-- Realiza el subinventory transfer
EXCEDENTES(r_principal.wip_entity_name
,r_principal.wip_entity_id
,r_principal.organization_id
,r_principal.inventory_item_id
,r_principal.subinv_ori
,r_principal.locator_id
,l_cant_excedente_otras
,r_principal.transaction_uom
,l_status_exc
,l_error_exc);
-- Verifica el estado
IF l_status_exc != 'S' THEN
l_errores := l_error_exc;
RAISE e_errores2;
END IF;
END IF;
-- Si consume toda la MO original y NO tiene remanente
ELSIF l_picking_parcial = 'FALSE' THEN
-- Realiza el pick confirm por la cantidad total de la linea de la MO primaria
PICK_CONFIRM (r_principal."Id"
,r_principal.header_id
,r_principal.moli_transf
,r_principal.subinv_ori
,r_principal.subinv_des
,r_principal.transaction_temp_id
,r_principal.wip_entity_name
,r_principal.wip_entity_id
,r_principal."Cantidad"
,l_quantity_delivered
,l_quantity_detailed
,l_status_pick
,l_error_pick);
-- Verifica el estado
IF l_status_pick != 'S' THEN
l_errores := l_error_pick;
RAISE e_errores2;
END IF;
-- Si no llega a consumir toda la cantidad de la MO original
ELSIF l_picking_parcial = 'TRUE' THEN
-- Realiza el pick parcial de la MO original
PICK_PARCIAL (r_principal."Id"
,r_principal.transaction_temp_id
,r_principal."Cantidad"
,r_principal.wip_entity_name
,r_principal.wip_entity_id
,r_principal.header_id
,r_principal.moli_transf
,r_principal.subinv_ori
,r_principal.subinv_des
,l_transaction_quantity
,l_quantity_delivered
,l_quantity_detailed
,l_status_parcial
,l_error_parcial);
-- Verifica el estado
IF l_status_parcial != 'S' THEN
l_errores := l_error_parcial;
RAISE e_errores2;
END IF;
END IF; --l_picking_parcial
-- Actualiza el estado de la tabla de interfaz a Cygnus a procesado
UPDATE_TRANSFERENCIA (r_principal."Id"
,l_status_up_tra
,l_error_up_tra);
-- Verifica el estado
IF l_status_up_tra != 'S' THEN
l_errores := l_error_up_tra;
RAISE e_errores2;
END IF;
-- Realiza la validacion de los datos
VALIDA_PICK_CONFIRM (r_principal.header_id
,l_status_valida_pick
,l_error_valida_pick);
-- Verifica el estado
IF l_status_valida_pick != 'S' THEN
l_errores := l_error_valida_pick;
RAISE e_errores2;
END IF;
END IF;
EXCEPTION WHEN e_errores2 THEN
ROLLBACK;
fnd_file.put_line(fnd_file.output,'Ha surgido un error al procesar el Move Order Line ID: '||r_principal."IdMoveOrderLine");
fnd_file.put_line(fnd_file.Log,'Error en proceso cursor para el Move Order Line ID: '||r_principal."IdMoveOrderLine"||', Error: '||l_errores);
retcode := 1;
errbuf := l_errores;
END;
END LOOP;
-- Subinventory transfers para transferencias que no poseen temporal
FOR r_principal_no_temp IN c_principal_no_temp LOOP
BEGIN
l_cant_excedente_otras := r_principal_no_temp."Cantidad";
-- Busca reservas del item en otras MO y los consume
FOR r_otras_mo IN c_otras_mo (r_principal_no_temp.inventory_item_id
,r_principal_no_temp."Id") LOOP
l_picking_parcial_otras := 'FALSE';
l_errores := '';
l_transaction_quantity_otras := 0;
l_quantity_delivered_otras := 0;
l_quantity_detailed_otras := 0;
-- Existe registro de la MMTT que se tiene que confirmar?
BEGIN
SELECT mmtt.transaction_quantity
,nvl(mtrl.quantity_delivered,0) quantity_delivered
,nvl(mtrl.quantity_detailed,0) quantity_detailed
INTO l_transaction_quantity_otras
,l_quantity_delivered_otras
,l_quantity_detailed_otras
FROM mtl_material_transactions_temp mmtt
,mtl_txn_request_lines mtrl
WHERE mmtt.move_order_line_id = mtrl.line_id
AND mmtt.move_order_header_id = r_otras_mo.mohe_temp
AND mmtt.move_order_line_id = r_otras_mo.moli_temp
AND mmtt.subinventory_code = r_otras_mo.subinv_ori
AND mmtt.transfer_subinventory = r_otras_mo.subinv_des
AND mmtt.transaction_header_id IS NOT NULL
AND mmtt.transaction_quantity > 0;
EXCEPTION WHEN OTHERS THEN
l_errores := '1.5.1 - Codigo: '||SQLCODE||', Error: '||SQLERRM;
l_transaction_quantity_otras := 0;
END;
-- If Principal. Existe Temp para la MO line secundaria?
IF l_transaction_quantity_otras > 0 THEN
-- Actualizo bandera de picking parcial y calculo excedente si hay
IF (l_cant_excedente_otras < l_transaction_quantity_otras) THEN
l_picking_parcial_otras := 'TRUE';
ELSE
l_picking_parcial_otras := 'FALSE';
-- Va actualizando la cantidad excedente
l_cant_excedente_otras := l_cant_excedente_otras - l_transaction_quantity_otras;
END IF;
-- Si consume toda la cantidad de la linea de la MO secundaria y tiene remanente
IF l_picking_parcial_otras = 'FALSE' AND l_cant_excedente_otras > 0 THEN
-- Realiza el pick confirm por la cantidad total de la linea de la MO secundaria
PICK_CONFIRM (r_otras_mo."Id"
,r_otras_mo.mohe_temp
,r_otras_mo.moli_temp
,r_otras_mo.subinv_ori
,r_otras_mo.subinv_des
,r_otras_mo.transaction_temp_id
,r_otras_mo.wip_entity_name_otra
,r_otras_mo.wip_entity_id_otra
,l_transaction_quantity_otras
,l_quantity_delivered_otras
,l_quantity_detailed_otras
,l_status_pick
,l_error_pick);
-- Verifica el estado
IF l_status_pick != 'S' THEN
l_errores := l_error_pick;
RAISE e_errores2;
END IF;
-- Si consume toda la cantidad de la linea de la MO secundaria y NO tiene remanente
ELSIF l_picking_parcial_otras = 'FALSE' THEN
-- Realiza el pick confirm por la cantidad total de la linea de la MO secundaria
PICK_CONFIRM (r_otras_mo."Id"
,r_otras_mo.mohe_temp
,r_otras_mo.moli_temp
,r_otras_mo.subinv_ori
,r_otras_mo.subinv_des
,r_otras_mo.transaction_temp_id
,r_otras_mo.wip_entity_name_otra
,r_otras_mo.wip_entity_id_otra
,r_otras_mo."Cantidad"
,l_quantity_delivered_otras
,l_quantity_detailed_otras
,l_status_pick
,l_error_pick);
-- Verifica el estado
IF l_status_pick != 'S' THEN
l_errores := l_error_pick;
RAISE e_errores2;
END IF;
-- No hay mas excendente
l_cant_excedente_otras := 0;
--sale del LOOP r_otras_mo, ya que no hace falta que siga buscando mas reservas
exit;
-- Si no llega a consumir toda la cantidad de la MO secundaria
ELSIF l_picking_parcial_otras = 'TRUE' THEN
-- Realiza el pick parcial de la MO secundaria
PICK_PARCIAL (r_otras_mo."Id"
,r_otras_mo.transaction_temp_id
,l_cant_excedente_otras
,r_otras_mo.wip_entity_name_otra
,r_otras_mo.wip_entity_id_otra
,r_otras_mo.mohe_temp
,r_otras_mo.moli_temp
,r_otras_mo.subinv_ori
,r_otras_mo.subinv_des
,l_transaction_quantity_otras
,l_quantity_delivered_otras
,l_quantity_detailed_otras
,l_status_parcial
,l_error_parcial);
-- Verifica el estado
IF l_status_parcial != 'S' THEN
l_errores := l_error_parcial;
RAISE e_errores2;
END IF;
-- No hay mas excendente
l_cant_excedente_otras := 0;
--sale del LOOP r_otras_mo, ya que no hace falta que siga buscando mas reservas
exit;
END IF;
END IF;
-- Realiza la validacion de los datos
VALIDA_PICK_CONFIRM (r_otras_mo.mohe_temp
,l_status_valida_pick
,l_error_valida_pick);
-- Verifica el estado
IF l_status_valida_pick != 'S' THEN
l_errores := l_error_valida_pick;
RAISE e_errores3;
END IF;
END LOOP;
-- Si queda excedente y no tiene mas reservas en MOs transfiere el remanente a EXCEDENTESx
IF l_cant_excedente_otras > 0 THEN
-- Realiza el subinventory transfer
EXCEDENTES(r_principal_no_temp."IdOrdenProduccion"
,r_principal_no_temp.wip_entity_id
,r_principal_no_temp.organization_id
,r_principal_no_temp.inventory_item_id
,r_principal_no_temp.subinv_ori
,r_principal_no_temp.locator_id
,l_cant_excedente_otras
,r_principal_no_temp.uom_code
,l_status_exc
,l_error_exc);
-- Verifica el estado
IF l_status_exc != 'S' THEN
l_errores := l_error_exc;
RAISE e_errores3;
END IF;
END IF;
-- Actualiza el estado de la tabla de interfaz a Cygnus a procesado
UPDATE_TRANSFERENCIA (r_principal_no_temp."Id"
,l_status_up_tra
,l_error_up_tra);
-- Verifica el estado
IF l_status_up_tra != 'S' THEN
l_errores := l_error_up_tra;
RAISE e_errores3;
END IF;
EXCEPTION WHEN e_errores3 THEN
ROLLBACK;
fnd_file.put_line(fnd_file.output,'Ha surgido un error al procesar el Move Order Line ID: '||r_principal_no_temp."IdMoveOrderLine");
fnd_file.put_line(fnd_file.Log,'Error en proceso cursor para el Move Order Line ID: '||r_principal_no_temp."IdMoveOrderLine"||', Error: '||l_errores);
retcode := 1;
errbuf := l_errores;
END;
END LOOP;
EXCEPTION WHEN e_errores1 THEN
ROLLBACK;
fnd_file.put_line(fnd_file.Log,'Error en proceso principal: '||l_errores);
retcode := 2;
errbuf := l_errores;
-- Elimino reservas de lineas de MO cerradas.
DELETE
FROM mtl_material_transactions_temp
WHERE transaction_type_id = 51
AND move_order_line_id IN (SELECT line_id
FROM mtl_txn_request_lines
WHERE line_status = 5);
COMMIT;
END MAIN;
----------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------
PROCEDURE PICK_CONFIRM (p_transferencia_id IN NUMBER
,p_move_order_header_id IN NUMBER
,p_move_order_line_id IN NUMBER
,p_subinventory_code IN VARCHAR2
,p_transfer_subinventory IN VARCHAR2
,p_transaction_temp_id IN NUMBER
,p_wip_entity_name IN VARCHAR2
,p_wip_entity_id IN NUMBER
,p_cantidad IN NUMBER
,p_quantity_delivered IN NUMBER
,p_quantity_detailed IN NUMBER
,p_status OUT VARCHAR2
,p_error OUT VARCHAR2) IS
l_actualiza_registro3 INV_MO_LINE_DETAIL_UTIL.G_MMTT_TBL_TYPE;
l_actualiza_registro2 INV_MO_LINE_DETAIL_UTIL.G_MMTT_TBL_TYPE;
l_trolin_tbl INV_MOVE_ORDER_PUB.TROLIN_TBL_TYPE;
l_mold_tbl INV_MO_LINE_DETAIL_UTIL.G_MMTT_TBL_TYPE;
x_mold_tbl INV_MO_LINE_DETAIL_UTIL.G_MMTT_TBL_TYPE;
x_trolin_tbl INV_MOVE_ORDER_PUB.TROLIN_TBL_TYPE;
l_trohdr_rec INV_MOVE_ORDER_PUB.TROHDR_REC_TYPE;
-- Common Declarations
l_api_version NUMBER := 1.0;
x_msg_count NUMBER := 0;
l_init_msg_list VARCHAR2(2) := FND_API.G_TRUE;
l_return_values VARCHAR2(2) := FND_API.G_FALSE;
l_commit VARCHAR2(2) := FND_API.G_FALSE;
x_return_status VARCHAR2(2);
x_msg_data VARCHAR2(255);
l_status_up_tra VARCHAR2(10000);
l_error_up_tra VARCHAR2(1000);
v_error_pick VARCHAR2(1);
e_errores4 EXCEPTION;
BEGIN
p_status := 'S';
p_error := NULL;
l_actualiza_registro3.DELETE(1);
l_actualiza_registro3.DELETE(2);
l_actualiza_registro2.DELETE(1);
l_actualiza_registro2.DELETE(2);
-- Este update al attribute13 y transaction_reference fue pedido por PTILKIN --
BEGIN
-- Actualizo registro original
l_actualiza_registro3(1) := inv_mo_line_detail_util.query_row (p_transaction_temp_id);
l_actualiza_registro3(1).transaction_reference := p_wip_entity_name;
inv_mo_line_detail_util.update_row (x_return_status => x_return_status
,p_mo_line_detail_rec => l_actualiza_registro3(1));
EXCEPTION WHEN OTHERS THEN
p_status := 'E';
p_error := '11 - Codigo: '||SQLCODE||', Error: '||SQLERRM;
RAISE e_errores4;
END;
BEGIN
UPDATE mtl_material_transactions_temp
SET attribute13 = p_wip_entity_id,
attribute_category = 'Ushuaia'
WHERE move_order_line_id = p_move_order_line_id
AND subinventory_code = p_subinventory_code
AND transfer_subinventory = p_transfer_subinventory
AND process_flag = 'Y';
EXCEPTION WHEN OTHERS THEN
p_status := 'E';
p_error := '12 - Codigo: '||SQLCODE||', Error: '||SQLERRM;
RAISE e_errores4;
END;
-- Actualizo linea de MO --
BEGIN
UPDATE mtl_txn_request_lines
SET quantity_delivered = 0
WHERE line_id = p_move_order_line_id;
EXCEPTION WHEN OTHERS THEN
p_status := 'E';
p_error := '12.1 - Codigo: '||SQLCODE||', Error: '||SQLERRM;
RAISE e_errores4;
END;
-- Lockeo de registros que no se confirman
l_actualiza_registro2 := inv_mo_line_detail_util.query_rows(p_move_order_line_id);
FOR j IN 1 .. l_actualiza_registro2.Count LOOP
IF l_actualiza_registro2(j).transaction_temp_id != p_transaction_temp_id THEN
l_actualiza_registro2(j).process_flag := 'N';
inv_mo_line_detail_util.update_row (x_return_status => x_return_status
,p_mo_line_detail_rec => l_actualiza_registro2(j));
IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
p_status := 'E';
p_error := '10 - Error al actualizar el process_flag a N del registro original luego de haber hecho el split';
RAISE e_errores4;
END IF;
END IF;
END LOOP;
-- Pick Confirm
l_trolin_tbl(1).line_id := p_move_order_line_id;
inv_pick_wave_pick_confirm_pub.pick_confirm ( p_api_version_number => l_api_version
, p_init_msg_list => l_init_msg_list
, p_commit => l_commit
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_move_order_type => 5
, p_transaction_mode => 1
, p_trolin_tbl => l_trolin_tbl
, p_mold_tbl => l_mold_tbl
, x_mmtt_tbl => x_mold_tbl
, x_trolin_tbl => x_trolin_tbl
, p_transaction_date => SYSDATE );
IF x_return_status <> fnd_api.g_ret_sts_success THEN
p_status := 'E';
p_error := '13 - Codigo: '||x_msg_count||', Error: '||x_msg_data;
RAISE e_errores4;
END IF;
-- Actualizo cantidad allocated de la move order line
BEGIN
UPDATE mtl_txn_request_lines
SET quantity_delivered = (p_quantity_delivered + p_cantidad)
,quantity = (p_quantity_delivered + p_quantity_detailed)
WHERE line_id = p_move_order_line_id;
EXCEPTION WHEN OTHERS THEN
p_status := 'E';
p_error := '16 - Codigo: '||SQLCODE||', Error: '||SQLERRM;
RAISE e_errores4;
END;
-- Procesa la Move Order
l_trohdr_rec.operation := INV_GLOBALS.G_OPR_UPDATE;
l_trolin_tbl(1).header_id := p_move_order_header_id;
l_trolin_tbl(1).line_id := p_move_order_line_id;
IF p_cantidad > p_quantity_detailed THEN
l_trolin_tbl(1).quantity_detailed := 0;
ELSE
l_trolin_tbl(1).quantity_detailed := p_quantity_detailed - p_cantidad;
END IF;
l_trolin_tbl(1).operation := INV_GLOBALS.G_OPR_UPDATE;
INV_MOVE_ORDER_PUB.Process_Move_Order_Line ( p_api_version_number => 1.0
, p_init_msg_list => l_init_msg_list
, p_return_values => l_return_values
, p_commit => l_commit
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_trolin_tbl => l_trolin_tbl
, p_trolin_old_tbl => l_trolin_tbl
, x_trolin_tbl => x_trolin_tbl );
IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
p_status := 'E';
p_error := '17 - Codigo: '||x_msg_count||', Error: '||x_msg_data;
RAISE e_errores4;
END IF;
-- Actualizo registros lockeados
FOR r IN 1 .. l_actualiza_registro2.Count LOOP
BEGIN
IF l_actualiza_registro2(r).transaction_temp_id != p_transaction_temp_id THEN
l_actualiza_registro2(r).process_flag := 'Y';
l_actualiza_registro2(r).transaction_status := 2;
IF l_actualiza_registro2(r).transaction_quantity < 0 THEN
l_actualiza_registro2(r).transaction_quantity := abs(l_actualiza_registro2(r).transaction_quantity);
l_actualiza_registro2(r).primary_quantity := abs(l_actualiza_registro2(r).primary_quantity);
END IF;
inv_mo_line_detail_util.update_row ( x_return_status => x_return_status
,p_mo_line_detail_rec => l_actualiza_registro2(r));
IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
p_status := 'E';
p_error := '14 - Error al actualizar las cantidades Transaction/Primary del registro confirmado luego de haber hecho el pick confirm';
RAISE e_errores4;
END IF;
END IF;
EXCEPTION WHEN OTHERS THEN
p_status := 'E';
p_error := '15 - Codigo: '||SQLCODE||', Error: '||SQLERRM;
RAISE e_errores4;
END;
END LOOP;
EXCEPTION WHEN e_errores4 THEN
fnd_file.put_line(fnd_file.Log,'Error en proceso Pick Confirm: ');
END PICK_CONFIRM;
----------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------
PROCEDURE PICK_PARCIAL (p_transferencia_id IN NUMBER
,p_transaction_temp_id IN NUMBER
,p_cantidad IN NUMBER
,p_wip_entity_name IN VARCHAR2
,p_wip_entity_id IN NUMBER
,p_move_order_header_id IN NUMBER
,p_move_order_line_id IN NUMBER
,p_subinventory_code IN VARCHAR2
,p_transfer_subinventory IN VARCHAR2
,p_transaction_quantity IN NUMBER
,p_quantity_delivered IN NUMBER
,p_quantity_detailed IN NUMBER
,p_status OUT VARCHAR2
,p_error OUT VARCHAR2) IS
l_mmtt_confirma MTL_MATERIAL_TRANSACTIONS_TEMP%ROWTYPE;
l_actualiza_registro1 INV_MO_LINE_DETAIL_UTIL.G_MMTT_TBL_TYPE;
-- Common Declarations
l_api_version NUMBER := 1.0;
x_msg_count NUMBER := 0;
l_init_msg_list VARCHAR2(2) := FND_API.G_TRUE;
l_return_values VARCHAR2(2) := FND_API.G_FALSE;
l_commit VARCHAR2(2) := FND_API.G_FALSE;
x_return_status VARCHAR2(2);
x_msg_data VARCHAR2(255);
l_status_pick VARCHAR2(1);
l_error_pick VARCHAR2(1000);
e_errores5 EXCEPTION;
BEGIN
p_status := 'S';
p_error := NULL;
l_actualiza_registro1.DELETE(1);
l_actualiza_registro1.DELETE(2);
-- Realiza el split de la linea original de MO
inv_replenish_detail_pub.split_line_details (p_transaction_temp_id => p_transaction_temp_id
,p_missing_quantity => 0
,p_detailed_quantity => p_cantidad
,p_transaction_quantity => 0
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
p_status := 'E';
p_error := '7 - Codigo: '||x_msg_count||', Error: '||x_msg_data;
RAISE e_errores5;
END IF;
-- Obtengo nuevo registro generado
BEGIN
SELECT *
INTO l_mmtt_confirma
FROM mtl_material_transactions_temp
WHERE move_order_line_id = p_move_order_line_id
AND subinventory_code = p_subinventory_code
AND transfer_subinventory = p_transfer_subinventory
AND transaction_header_id IS NULL
AND move_order_header_id IS NULL;
-- Actualizo registro original
l_actualiza_registro1(1) := inv_mo_line_detail_util.query_row(p_transaction_temp_id);
l_actualiza_registro1(1).transaction_quantity := (l_actualiza_registro1(1).transaction_quantity - l_mmtt_confirma.transaction_quantity);
l_actualiza_registro1(1).primary_quantity := (l_actualiza_registro1(1).primary_quantity - l_mmtt_confirma.primary_quantity);
EXCEPTION WHEN OTHERS THEN
p_status := 'E';
p_error := '8 - Codigo: '||SQLCODE||', Error: '||SQLERRM;
RAISE e_errores5;
END;
inv_mo_line_detail_util.update_row (x_return_status => x_return_status
,p_mo_line_detail_rec => l_actualiza_registro1(1));
IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
p_status := 'E';
p_error := '9 - Error al actualizar las cantidades Transaction/Primary del registro original luego de haber hecho el split';
RAISE e_errores5;
END IF;
--Realiza el pick confirm
PICK_CONFIRM (p_transferencia_id
,p_move_order_header_id
,p_move_order_line_id
,p_subinventory_code
,p_transfer_subinventory
,l_mmtt_confirma.transaction_temp_id
,p_wip_entity_name
,p_wip_entity_id
,p_cantidad
,p_quantity_delivered
,p_quantity_detailed
,l_status_pick
,l_error_pick);
IF l_status_pick != 'S' THEN
p_status := 'E';
p_error := l_error_pick;
RAISE e_errores5;
END IF;
EXCEPTION WHEN e_errores5 THEN
fnd_file.put_line(fnd_file.Log,'Error en proceso Pick Parcial: ');
END PICK_PARCIAL;
----------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------
PROCEDURE EXCEDENTES (p_wip_entity_name IN VARCHAR2
,p_wip_entity_id IN NUMBER
,p_organization_id IN NUMBER
,p_inventory_item_id IN NUMBER
,p_subinventario_ori IN VARCHAR2
,p_locator_ori IN NUMBER
,p_cantidad IN NUMBER
,p_transaction_uom IN VARCHAR2
,p_status OUT VARCHAR2
,p_error OUT VARCHAR2) IS
l_loc_excedente MTL_ITEM_LOCATIONS.INVENTORY_LOCATION_ID%TYPE;
l_sub_excedente MTL_ITEM_LOCATIONS.SUBINVENTORY_CODE%TYPE;
l_transaction_type_id_sub NUMBER;
l_transaction_action_id_sub NUMBER;
l_transact_source_type_id_sub NUMBER;
e_errores6 EXCEPTION;
BEGIN
p_status := 'S';
p_error := NULL;
-- Obtengo el subinventario y localizador Excedente de la organizacion
BEGIN
SELECT msi.secondary_inventory_name
,mil.inventory_location_id
INTO l_sub_excedente
,l_loc_excedente
FROM mtl_secondary_inventories msi
,mtl_secondary_inventories_dfv msi_dfv
,mtl_item_locations mil
WHERE msi.organization_id = p_organization_id
AND msi.organization_id = mil.organization_id
AND msi.secondary_inventory_name = mil.subinventory_code
AND msi.rowid = msi_dfv.row_id
AND msi_dfv.sub_excedente = 'Y';
EXCEPTION WHEN OTHERS THEN
p_status := 'E';
p_error := '4 - Codigo: '||SQLCODE||', Error: '||SQLERRM;
RAISE e_errores6;
END;
-- Recupera el tipo de transaccion para el Subinventory Transfer
BEGIN
SELECT transaction_type_id
,transaction_action_id
,transaction_source_type_id
INTO l_transaction_type_id_sub
,l_transaction_action_id_sub
,l_transact_source_type_id_sub
FROM mtl_transaction_types
WHERE transaction_type_name = 'Subinventory Transfer';
EXCEPTION WHEN OTHERS THEN
p_status := 'E';
p_error := '5 - Codigo: '||SQLCODE||', Error: '||SQLERRM;
RAISE e_errores6;
END;
-- Insert en Interfaz para Transferencia a Subinventario de Excedentes
BEGIN
INSERT INTO mtl_transactions_interface (source_code
,source_line_id
,source_header_id
,process_flag
,transaction_mode
,organization_id
,inventory_item_id
,transaction_quantity
,transaction_uom
,transaction_date
,transaction_type_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,subinventory_code
,locator_id
,transaction_source_type_id
,transaction_action_id
,transfer_subinventory
,transfer_locator
,transaction_reference
,attribute_category
,attribute13)
VALUES ('Inventory'
,1
,1
,1
,3
,p_organization_id
,p_inventory_item_id
,p_cantidad
,p_transaction_uom
,SYSDATE
,l_transaction_type_id_sub
,SYSDATE
,FND_GLOBAL.USER_ID
,SYSDATE
,FND_GLOBAL.USER_ID
,p_subinventario_ori
,p_locator_ori
,l_transact_source_type_id_sub
,l_transaction_action_id_sub
,l_sub_excedente
,l_loc_excedente
,p_wip_entity_name || ' - Excedente con MMTT'
,'Ushuaia'
,p_wip_entity_id );
EXCEPTION WHEN OTHERS THEN
p_status := 'E';
p_error := '6 - Codigo: '||SQLCODE||', Error: '||SQLERRM;
RAISE e_errores6;
END;
EXCEPTION WHEN e_errores6 THEN
fnd_file.put_line(fnd_file.Log,'Error en proceso Excedentes: ');
END EXCEDENTES;
----------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------
PROCEDURE UPDATE_TRANSFERENCIA (p_transferencia_id IN NUMBER
,p_status OUT VARCHAR2
,p_error OUT VARCHAR2) IS
l_mensaje_error VARCHAR2(1000);
e_errores7 EXCEPTION;
BEGIN
p_status := 'S';
p_error := NULL;
BEGIN
COMMIT;
-- Actualiza el estado de la tabla de interfaz a Cygnus a procesado
UPDATE transferencias
SET "IdEstado" = 1
WHERE "Id" = p_transferencia_id;
COMMIT;
DELETE
FROM xx_transferencias_tmp
WHERE "Id" = p_transferencia_id;
COMMIT;
EXCEPTION WHEN OTHERS THEN
-- En caso de error actualiza el estado de la tabla de interfaz a Cygnus a error
BEGIN
l_mensaje_error := 'Error actualizando Transferencias: '||p_transferencia_id;
UPDATE transferencias
SET "IdEstado" = 2
, "ObservacionesEstado" = l_mensaje_error
WHERE "Id" = p_transferencia_id;
COMMIT;
EXCEPTION WHEN OTHERS THEN
p_status := 'E';
p_error := '18 - Codigo: '||SQLCODE||', Error: '||SQLERRM;
RAISE e_errores7;
END;
END;
EXCEPTION WHEN e_errores7 THEN
fnd_file.put_line(fnd_file.Log,'Error en proceso update_transferencia: ');
END UPDATE_TRANSFERENCIA;
----------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------
PROCEDURE VALIDA_PICK_CONFIRM (p_move_order_header_id IN NUMBER
,p_status OUT VARCHAR2
,p_error OUT VARCHAR2) IS
CURSOR c_temp IS
SELECT *
FROM mtl_material_transactions_temp
WHERE transaction_type_id = 51
AND move_order_header_id = p_move_order_header_id
AND transaction_quantity < 0;
l_quantity NUMBER := 0;
l_quantity_split NUMBER := 0;
v_id NUMBER;
l_existe_temp VARCHAR2(5);
l_move_order_header_id VARCHAR2(20);
l_move_order_line_id VARCHAR2(20);
BEGIN
p_status := 'S';
p_error := NULL;
FOR r_temp IN c_temp LOOP
l_quantity := 0;
l_quantity_split := 0;
-- Existe registro spliteado?
BEGIN
SELECT 'TRUE'
INTO l_existe_temp
FROM mtl_material_transactions_temp
WHERE move_order_line_id = r_temp.move_order_line_id
AND move_order_header_id IS NULL;
EXCEPTION WHEN no_data_found THEN
l_existe_temp := 'FALSE';
END;
IF (l_existe_temp = 'TRUE') THEN
-- Obtengo Cantidad Original
BEGIN
SELECT abs(Sum(transaction_quantity))
INTO l_quantity
FROM mtl_material_transactions_temp
WHERE transaction_type_id = 51
AND move_order_line_id = r_temp.move_order_line_id;
EXCEPTION WHEN others THEN
p_status := 'E';
p_error := '19 - Codigo: '||SQLCODE||', Error: '||SQLERRM;
exit;
END;
-- Obtengo Cantidad del split
BEGIN
SELECT abs(Sum(transaction_quantity))
INTO l_quantity_split
FROM mtl_material_transactions_temp
WHERE transaction_type_id = 51
AND transaction_quantity < 0
AND move_order_line_id = r_temp.move_order_line_id
AND move_order_header_id IS NULL;
EXCEPTION WHEN others THEN
p_status := 'E';
p_error := '19.1 - Codigo: '||SQLCODE||', Error: '||SQLERRM;
exit;
END;
BEGIN
-- Actualizo registro temp original
UPDATE mtl_material_transactions_temp
SET transaction_quantity = l_quantity,
primary_quantity = l_quantity,
transaction_status = 2,
process_flag = 'Y'
WHERE transaction_temp_id = r_temp.transaction_temp_id;
EXCEPTION WHEN others THEN
p_status := 'E';
p_error := '19.2 - Codigo: '||SQLCODE||', Error: '||SQLERRM;
exit;
END;
BEGIN
-- Elimino registro spliteado
DELETE
FROM mtl_material_transactions_temp
WHERE transaction_type_id = 51
AND transaction_quantity < 0
AND move_order_line_id = r_temp.move_order_line_id
AND move_order_header_id IS NULL;
EXCEPTION WHEN others THEN
p_status := 'E';
p_error := '19.3 - Codigo: '||SQLCODE||', Error: '||SQLERRM;
exit;
END;
l_move_order_header_id := to_char(r_temp.move_order_header_id);
l_move_order_line_id := to_char(r_temp.move_order_line_id);
BEGIN
-- Actualizo transferencias
UPDATE transferencias
SET "IdEstado" = 0
WHERE "DocumentoOrigenTipo" = 'OP'
AND "IdMoveOrder" = l_move_order_header_id
AND "IdMoveOrderLine" = l_move_order_line_id
AND "Cantidad" = l_quantity_split
AND ROWNUM = 1;
EXCEPTION WHEN others THEN
p_status := 'E';
p_error := '19.4 - Codigo: '||SQLCODE||', Error: '||SQLERRM;
exit;
END;
END IF;
END LOOP;
IF p_status = 'S' THEN
COMMIT;
ELSE
ROLLBACK;
END IF;
END VALIDA_PICK_CONFIRM;
END XX_INV_PROPICKALLITEMS_PK;
/
SHOW ERR
SPOOL OFF
EXIT
SQL - TABLAS
spool XX_CLASIFICACION_MATERIALES.log
REM +=======================================================================+
REM | |
REM | FILENAME |
REM | XX_CLASIFICACION_MATERIALES |
REM | |
REM | DESCRIPTION |
REM | Script para creacion de tabla |
REM | |
REM | SOURCE CONTROL |
REM | Version : $Revision:$ |
REM | Date : $Date:$ |
REM | |
REM | HISTORY |
REM | 24-OCT-2011 Sir Juan de Los Montes Created |
REM +=======================================================================+
SET VERIFY OFF
CREATE TABLE XX_CLASIFICACION_MATERIALES
(nro_clasificacion NUMBER
,modelo VARCHAR2(40)
,lote VARCHAR2(10)
,orden_produccion VARCHAR2(240)
,cantidad_original NUMBER
,total NUMBER
,item NUMBER
,codigo VARCHAR2(240)
,descripcion VARCHAR2(240)
,unidad_medida VARCHAR2(3)
,subinventario VARCHAR2(10)
,sub_origen VARCHAR2(10)
,linea_prod VARCHAR2(120)
,linea_prod_id NUMBER
,component_seq_id NUMBER
,item_id NUMBER
,parent_item_id NUMBER
,plan_level NUMBER
,wip_entity_id NUMBER
,inventory_item_id NUMBER
,organization_id NUMBER)
/
SHOW ERR
spool off
EXIT
JAVA
JAVA Class
package ar.com.watea.einvoice;
import java.util.Date;
import java.util.logging.FileHandler;
import java.util.logging.Formatter;
import java.util.logging.Handler;
import java.util.logging.Level;
import java.util.logging.LogManager;
import java.util.logging.LogRecord;
import java.util.logging.Logger;
import javax.annotation.Resource;
import javax.ejb.MessageDriven;
import javax.ejb.MessageDrivenContext;
import javax.jms.JMSException;
import javax.jms.Message;
import javax.jms.MessageListener;
import javax.jms.TextMessage;
import ar.com.watea.einvoice.ws.WebServiceRequest;
import ar.com.watea.einvoice.ws.WebServiceRequestFactory;
/**
* Message-Driven Bean implementation class for: ClientMDB
*/
@MessageDriven
public class ClientMDB implements MessageListener {
/*
* Function: Define the logger and all their configuration
*
* @author Emiliano Di Pierro <emiliano.dipierro@watea.com.ar>
* @version 1.0
*
*/
private static Logger logger = Logger.getLogger( "ClientMDB" );
static {
try {
logger.setLevel(Level.FINEST);
Formatter formatter = new Formatter() {
//Define the structure of the logging
@Override
public String format(LogRecord arg0) {
StringBuilder b = new StringBuilder();
b.append(new Date() + " ");
b.append(arg0.getSourceClassName() + " ");
b.append(arg0.getSourceMethodName() + " ");
b.append(arg0.getLevel() + " ");
if (arg0.getThrown() == null){
b.append(arg0.getMessage() + " ");
}else{
b.append(arg0.getThrown().getMessage() + " ");
arg0.getThrown().printStackTrace();
}
b.append(System.getProperty("line.separator"));
return b.toString();
}
};
Handler fh = new FileHandler("eInvoiceClient.log",1000000000,1,true);
//Define the filehander with 1GB limit, with one cycle and true append
fh.setFormatter(formatter);
logger.addHandler(fh);
logger.info("eInvoice Client Test - Logger Started");
LogManager lm = LogManager.getLogManager();
lm.addLogger(logger);
}
catch (Throwable e) {
System.out.println("Error initializing the Logger");
e.printStackTrace();
}
}
@Resource
private MessageDrivenContext mdc;
/*
* Function: Read a message from the queue and throws the request
*
* @author Emiliano Di Pierro <emiliano.dipierro@watea.com.ar>
* @version 1.0
* @param message Message from the queue
* @return void
*
*/
public void onMessage(Message message) {
//Finds the logger
Logger logger = LogManager.getLogManager().getLogger("ClientMDB");
TextMessage msg = null;
//Process the menssage
try {
//Gives the ack to the message
message.acknowledge();
if (message instanceof TextMessage) {
msg = (TextMessage) message;
logger.info("Message received: " + msg.getText() );
//Create a serviceRequest with the webservice and
//the customerTrxId parsed to BigDecimal
WebServiceRequest wsRequest =
WebServiceRequestFactory.buildWebServiceRequest( message );
//Process this request
wsRequest.process();
wsRequest.closeEntityManager();
}
} catch (JMSException e) {
logger.throwing(this.getClass().getName(),"JMS Error",e);
} catch (Throwable tw) {
logger.throwing(this.getClass().getName(), "Error on queue", tw);
}
}
}
Buenas practicas
By Emiliano Di Pierro
