procedure usf_cancel_po corrigida

CREATE OR REPLACE FUNCTION     USF_CANCEL_PO (in_vchwhid     VARCHAR2
                           ,in_vchponumber VARCHAR2
                           ,in_vchuserid   VARCHAR2
                           ,in_vchobs      VARCHAR2)
RETURN VARCHAR2 IS
PRAGMA AUTONOMOUS_TRANSACTION;

  -- Error handling and logging variables.
  v_nlogerrornum INT; -- The # that uniquely tags the error message.
  v_vcherrormsg  VARCHAR2(500 CHAR);
  e_genprocerror EXCEPTION;
  v_nCount             INT;
  v_vchOutMsg          VARCHAR2(500 CHAR);
  v_vchDisplayPONumber VARCHAR2(30 CHAR);
  v_vchHostGroupID     VARCHAR2(500 CHAR);
  V_RECEIPT_ID         NUMBER(10);

  CURSOR C_PO_DETAIL(P_PONUMBER IN VARCHAR2, P_WHID IN VARCHAR2) IS
    SELECT MAS.WH_ID,
           MAS.PO_NUMBER,
           'P-'||MAS.DISPLAY_PO_NUMBER AS SCAC_CODE,
           DET.ITEM_NUMBER,
           DET.LINE_NUMBER,
           DET.QTY
      FROM T_PO_DETAIL DET, T_PO_MASTER MAS
     WHERE MAS.PO_NUMBER = P_PONUMBER
       AND MAS.PO_NUMBER = DET.PO_NUMBER
       AND MAS.WH_ID = DET.WH_ID
       AND MAS.WH_ID = P_WHID;

BEGIN
  -- Verifica se algum item foi recebido
  SELECT COUNT(*)
    INTO v_nCount
    FROM t_receipt
   WHERE wh_id = in_vchwhid
     AND po_number = in_vchponumber;

  IF v_nCount <> 0 THEN
    v_vchOutMsg := 'OE nao foi cancelada pois ha itens ja recebidos na Ordem de Entrada';
    GOTO retorno;
  END IF;

  --Captura infromações para LOG
  SELECT display_po_number
    INTO v_vchDisplayPONumber
    FROM t_po_master
   WHERE wh_id = in_vchwhid
     AND po_number = in_vchponumber;

  --Verifica se PO tem mais de uma ordem em aberto
  SELECT COUNT(*)
    INTO v_nCount
    FROM t_po_master
   WHERE wh_id = in_vchwhid
     AND display_po_number = v_vchDisplayPONumber
     AND status = 'O';

  IF v_nCount > 1 THEN
    v_vchOutMsg := 'OE nao foi cancelada pois ha mais uma de Ordem de Entrada em aberto para a PO';
    GOTO retorno;
  END IF;

  -- Verifica se algum item já foi separado
  SELECT COUNT(*)
    INTO v_nCount
    FROM t_pick_detail
   WHERE (pick_id) IN (SELECT pkd.pick_id
                         FROM t_pick_detail pkd, t_order orm
                        WHERE pkd.order_number = orm.order_number
                          AND pkd.wh_id = orm.wh_id
                          AND orm.wh_id = in_vchwhid
                          AND orm.cust_po_number = v_vchDisplayPONumber
                          AND pkd.picked_quantity > 0);

  IF v_nCount <> 0 THEN
    v_vchOutMsg := 'OE nao foi cancelada pois ha itens ja separados dessa Ordem de Entrada';
    GOTO retorno;
  END IF;

  -- Verifica se existem tarefas de separação associada
  SELECT COUNT(*)
    INTO v_nCount
    FROM t_work_q wkq
   WHERE (wkq.wh_id, wkq.work_q_id) IN
         (SELECT pkd.wh_id, pkd.work_q_id
            FROM t_pick_detail pkd, t_order orm
           WHERE pkd.order_number = orm.order_number
             AND pkd.wh_id = orm.wh_id
             AND orm.wh_id = in_vchwhid
             AND orm.cust_po_number = v_vchDisplayPONumber)
     AND wkq.work_status IN ('A', 'C');

  IF v_nCount <> 0 THEN
    v_vchOutMsg := 'OE não foi cancelada pois há tarefas de separação atribuídas aos usuários';
    GOTO retorno;
  END IF;

  -- Problema 400-892 - Adicionado a validação para verificar se contém ASN para a PO Mencionada.
  SELECT COUNT(*)
    INTO v_nCount
  FROM t_hu_master hum
  WHERE hum.wh_id = in_vchwhid
    AND hum.hu_id IN (SELECT asn.hu_id
                      FROM t_al_host_transfer_asn asn
                      WHERE asn.wh_id = hum.wh_id
                        AND asn.hu_id = hum.hu_id
                        AND asn.display_po_number = v_vchDisplayPONumber);

  IF v_nCount <> 0 THEN
    v_vchOutMsg := 'OE nao foi cancelada, Efetuar primeiramente o cancelamento de ASN para essa PO';
    GOTO retorno;
  END IF;
  -- Fim Problema 400-892

  /* Problem 400-1807 - Begin */
  -- Clear aux variable v_nCount
  v_nCount := 0;

  -- Check if t_al_host_transfer_asn.display_po_number is null
  SELECT COUNT(1)
  INTO v_nCount
  FROM t_po_master
  WHERE wh_id = in_vchwhid
  AND po_number IN (SELECT po_number
                    FROM t_al_host_transfer_asn
                    WHERE display_po_number IS NULL
                    AND po_number = in_vchponumber
                    AND wh_id = in_vchwhid);

  IF (v_nCount <> 0) THEN
    -- Delete t_stored_item
    DELETE t_stored_item
    WHERE wh_id = in_vchwhid
    AND hu_id IN (SELECT hu_id
                  FROM t_al_host_transfer_asn
                  WHERE display_po_number IS NULL
                  AND po_number = in_vchponumber
                  AND wh_id = in_vchwhid);

    -- Delete t_hu_master
    DELETE t_hu_master
    WHERE wh_id = in_vchwhid
    AND hu_id IN (SELECT hu_id
                  FROM t_al_host_transfer_asn
                  WHERE display_po_number IS NULL
                  AND po_number = in_vchponumber
                  AND wh_id = in_vchwhid);

    -- Delete t_al_host_transfer_asn
    DELETE t_al_host_transfer_asn
    WHERE display_po_number IS NULL
    AND po_number = in_vchponumber
    AND wh_id = in_vchwhid;
  END IF;
  /* Problem 400-1807 - End */


  --- Implementa validacao tipo de PO de importados.
  SELECT COUNT(*)
    INTO v_nCount
    FROM t_po_master mas, v_type tpe
   WHERE mas.po_number = in_vchponumber
     AND mas.wh_id = in_vchwhid
     AND mas.TYPE_ID = tpe.TYPE_ID
     AND tpe.TYPE = 'IP';

  IF v_nCount <> 0 THEN
    v_vchOutMsg := 'Nao e possivel cancelar PO de importados.';
    GOTO retorno;
  END IF;

  /* ***** Bloco abaixo foi retirado conforme definicoes do PJ00088 - SIM ********* */
  /*
  DELETE t_po_detail
  WHERE wh_id = in_vchwhid
         AND po_number = in_vchponumber;

  DELETE t_po_detail_dest
  WHERE wh_id = in_vchwhid
         AND po_number = in_vchponumber;

  DELETE t_po_master
  WHERE wh_id = in_vchwhid
         AND po_number = in_vchponumber;
  */

  DELETE t_hu_audit
  WHERE wh_id = in_vchwhid
    AND po_number = in_vchponumber;

  DELETE t_work_q_assignment
  WHERE (wh_id, work_q_id) IN (SELECT pkd.wh_id
                                    , pkd.work_q_id
                               FROM t_pick_detail pkd,
                                    t_order orm
                               WHERE pkd.order_number = orm.order_number
                                 AND pkd.wh_id = orm.wh_id
                                 AND orm.wh_id = in_vchwhid
                                 AND orm.cust_po_number = v_vchDisplayPONumber
                              );

  DELETE t_work_q
  WHERE (wh_id, work_q_id) IN (SELECT pkd.wh_id
                                    , pkd.work_q_id
                               FROM t_pick_detail pkd,
                                    t_order orm
                               WHERE pkd.order_number = orm.order_number
                                 AND pkd.wh_id = orm.wh_id
                                 AND orm.wh_id = in_vchwhid
                                 AND orm.cust_po_number = v_vchDisplayPONumber
                              );

  DELETE t_pick_detail
  WHERE (pick_id) IN (SELECT pkd.pick_id
                      FROM t_pick_detail pkd,
                             t_order orm
                      WHERE pkd.order_number = orm.order_number
                        AND pkd.wh_id = orm.wh_id
                        AND orm.wh_id = in_vchwhid
                        AND orm.cust_po_number = v_vchDisplayPONumber
                      );

  DELETE t_order_detail
  WHERE (wh_id, order_number) IN (SELECT wh_id
                                       , order_number
                                  FROM   t_order
                                  WHERE wh_id = in_vchwhid
                                    AND cust_po_number = v_vchDisplayPONumber
                                  );

  DELETE t_order
  WHERE wh_id = in_vchwhid
    AND cust_po_number = v_vchDisplayPONumber;

  --- iNSERE DADOS DA PO NA T_RECEIPT
  SELECT next_value
    INTO V_RECEIPT_ID
    FROM T_CONTROL
   WHERE control_type = 'RECEIPT_ID';

  V_RECEIPT_ID := V_RECEIPT_ID + 1;

  UPDATE T_CONTROL
     SET next_value = V_RECEIPT_ID
   WHERE control_type = 'RECEIPT_ID';

  FOR R_PO_DETAIL IN C_PO_DETAIL(in_vchponumber, in_vchwhid) LOOP

    INSERT INTO T_RECEIPT
      (RECEIPT_ID,
       PO_NUMBER,
       RECEIPT_DATE,
       ITEM_NUMBER,
       LINE_NUMBER,
       SCHEDULE_NUMBER,
       QTY_RECEIVED,
       QTY_DAMAGED,
       HU_ID,
       FORK_ID,
       TRAN_STATUS,
       WH_ID,
       SCAC_CODE)
    VALUES
      (V_RECEIPT_ID,
       R_PO_DETAIL.PO_NUMBER,
       TRUNC(SYSDATE),
       R_PO_DETAIL.ITEM_NUMBER,
       R_PO_DETAIL.LINE_NUMBER,
       NULL,
       0,  --R_PO_DETAIL.QTY,
       NULL,
       NULL,
       NULL,
       'O',
       R_PO_DETAIL.WH_ID,
       R_PO_DETAIL.SCAC_CODE);

  END LOOP;

  -- Gerando transação 159
  INSERT INTO t_tran_log_holding
    (TRAN_TYPE,
     DESCRIPTION,
     START_TRAN_DATE,
     START_TRAN_TIME,
     END_TRAN_DATE,
     END_TRAN_TIME,
     EMPLOYEE_ID,
     CONTROL_NUMBER,
     LINE_NUMBER,
     CONTROL_NUMBER_2,
     OUTSIDE_ID,
     WH_ID,
     LOCATION_ID,
     HU_ID,
     NUM_ITEMS,
     ITEM_NUMBER,
     LOT_NUMBER,
     UOM,
     TRAN_QTY,
     WH_ID_2,
     LOCATION_ID_2,
     VERIFY_STATUS,
     EMPLOYEE_ID_2,
     ROUTING_CODE,
     HU_ID_2,
     RETURN_DISPOSITION,
     ELAPSED_TIME,
     SOURCE_STORAGE_TYPE,
     DESTINATION_STORAGE_TYPE,
     GENERIC_ATTRIBUTE_1,
     GENERIC_ATTRIBUTE_2,
     GENERIC_ATTRIBUTE_3,
     GENERIC_ATTRIBUTE_4,
     GENERIC_ATTRIBUTE_5,
     GENERIC_ATTRIBUTE_6,
     GENERIC_ATTRIBUTE_7,
     GENERIC_ATTRIBUTE_8,
     GENERIC_ATTRIBUTE_9,
     GENERIC_ATTRIBUTE_10,
     GENERIC_ATTRIBUTE_11)
  VALUES (
    '159'
    , NVL((SELECT description FROM t_transaction WHERE tran_type = '159'), 'Cancel Web PO')
    , TRUNC(SYSDATE)
    , TO_DATE(TO_CHAR(TRUNC(SYSDATE, 'MM'), 'DD/MM/YYYY')||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'), 'DD/MM/YYYY HH24:MI:SS') --START_TRAN_TIME
    , TRUNC(SYSDATE) --TO_DATE('01/01/1900','MM/DD/YYYY')END_TRAN_DATE
    , TO_DATE(TO_CHAR(TRUNC(SYSDATE, 'MM'), 'DD/MM/YYYY')||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'), 'DD/MM/YYYY HH24:MI:SS') --END_TRAN_TIME
    , in_vchuserid --EMPLOYEE_ID
    , in_vchponumber --CONTROL_NUMBER
    , NULL --LINE_NUMBER
    , v_vchDisplayPONumber --CONTROL_NUMBER_2
    , NULL --OUTSIDE_ID
    , in_vchwhid --WH_ID
    , NULL --LOCATION_ID
    , NULL --HU_ID
    , NULL --NUM_ITEMS
    , NULL --ITEM_NUMBER
    , NULL --LOT_NUMBER
    , NULL --UOM
    , NULL --TRAN_QTY
    , NULL --WH_ID_2
    , NULL --LOCATION_ID_2
    , NULL --VERIFY_STATUS
    , NULL --EMPLOYEE_ID_2
    , NULL --ROUTING_CODE
    , NULL --HU_ID_2
    , NULL --RETURN_DISPOSITION
    , NULL --ELAPSED_TIME
    , NULL --SOURCE_STORAGE_TYPE
    , NULL --DESTINATION_STORAGE_TYPE
    , SUBSTR(in_vchobs, 1, 250) --GENERIC_ATTRIBUTE_1
    , NULL --GENERIC_ATTRIBUTE_2
    , NULL --GENERIC_ATTRIBUTE_3
    , NULL --GENERIC_ATTRIBUTE_4
    , NULL --GENERIC_ATTRIBUTE_5
    , NULL --GENERIC_ATTRIBUTE_6
    , NULL --GENERIC_ATTRIBUTE_7
    , NULL --GENERIC_ATTRIBUTE_8
    , NULL --GENERIC_ATTRIBUTE_9
    , NULL --GENERIC_ATTRIBUTE_10
    , NULL --GENERIC_ATTRIBUTE_11
    );

  -- Atualiza o Status da PO
  UPDATE t_po_master
     SET status = 'X'
   WHERE wh_id = in_vchwhid
     AND po_number = in_vchponumber;

  <<retorno>>
  COMMIT;
  RETURN v_vchoutmsg;
EXCEPTION
  WHEN e_genprocerror THEN
    v_nlogerrornum := -20001;
    v_vchOutMsg    := 'Ocorreu um erro ao tentar cancelar a Ordem';
    ROLLBACK;
    raise_application_error(v_nlogerrornum, v_vcherrormsg);
  WHEN OTHERS THEN
    v_nlogerrornum := SQLCODE; -- Unknown Error Occured Executing Procedure
    v_vcherrormsg  := 'Unknown Stored Procedure Error - SQLERRM: '||SQLERRM;
    v_vchOutMsg    := 'Ocorreu um erro ao tentar cancelar a Ordem';
    ROLLBACK;
    raise_application_error(v_nlogerrornum, SQLERRM);
END USF_CANCEL_PO;