R12凭证编号客户化求教

mengjiancheng

Oracle系统中不提供满足中国财务制度要求的凭证编号功能，需要通过客户化来实现。

凭证号按月大流水编号产生，为避免出现重号断号现象，凭证将在过账后产生凭证流水号。

这样方案怎样才能实现，希望大家指教一下

b1uepanda

create or replace trigger CUX_CREATE_DOC_SEQ
before update of STATUS on GL.GL_JE_HEADERS
  for each row

when (old.status <>'P'
      and new.CURRENCY_CODE <>'STAT' AND NEW.ACTUAL_FLAG = 'A'
      and new.je_source not in ('Encumbrance','Budget Journal')
  )
DECLARE
  p_VSEQ     number;
  p_company    VARCHAR2(30);
  p_Sobid    number;
  p_end_seq VARCHAR2(80);
begin

SELECT gcc.segment1
  INTO p_company
  FROM gl_je_lines l,
       gl_code_combinations gcc
WHERE 1 = 1
   AND l.code_combination_id = gcc.code_combination_id
   AND l.je_header_id = :NEW.JE_header_ID
   AND rownum = 1;

  p_Sobid := :NEW.ledger_id;


  IF :new.STATUS = 'P' THEN
    SELECT NEXT_JE_SEQUENCE
      INTO p_VSEQ
      FROM CUX_GL_JE_SEQUENCE
     WHERE SET_OF_BOOKS_ID = :NEW.ledger_id
       AND company = p_company
       --AND je_source_name = :NEW.je_source
       AND PERIOD_NAME = :NEW.PERIOD_NAME
       /*for update of next_je_sequence*/;

    --:new.doc_sequence_value := p_VSEQ;
    IF p_Sobid=XXX THEN
    :new.external_reference := p_company||REPLACE(:new.period_name,'-')||lpad(p_vseq,5,'0');
    ELSE IF p_Sobid=XXX THEN
    :new.external_reference := p_company||'-'||REPLACE(:new.period_name,'-')||'-'||lpad(p_vseq,5,'0');
    ELSE IF  p_Sobid=XXX THEN
       :new.external_reference :=p_company||'-'||REPLACE(:new.period_name,'-')||'-'||lpad(p_vseq,5,'0');
       END IF;
    END IF;
    END IF;

   
    UPDATE CUX_GL_JE_SEQUENCE
       SET NEXT_JE_SEQUENCE  = NEXT_JE_SEQUENCE + 1,
           LAST_UPDATE_DATE  = SYSDATE,
           LAST_UPDATE_BY    = FND_GLOBAL.USER_ID,
           LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
     WHERE SET_OF_BOOKS_ID = :NEW.ledger_id
       AND company = p_company
       --AND JE_SOURCE_NAME =:NEW.JE_SOURCE
       AND PERIOD_NAME = :NEW.PERIOD_NAME;
  END IF;
exception
  when no_data_found then
    INSERT INTO CUX_GL_JE_SEQUENCE
      (SET_OF_BOOKS_ID,
       company,
       --JE_SOURCE_NAME,
       PERIOD_NAME,
       NEXT_JE_SEQUENCE,
       LAST_UPDATE_DATE,
       LAST_UPDATE_BY,
       CREATION_DATE,
       CREATED_BY,
       LAST_UPDATE_LOGIN)
    VALUES
      (:NEW.ledger_id,
       p_company,
       --:NEW.JE_SOURCE,
       :NEW.PERIOD_NAME,
       2,
       SYSDATE,
       fnd_global.USER_ID,
       SYSDATE,
       fnd_global.USER_ID,
       fnd_global.LOGIN_ID);

    --:new.doc_sequence_value := 1;
    IF p_Sobid=XXX THEN
    :new.external_reference := p_company||REPLACE(:new.period_name,'-')||lpad(1,5,'0');
    ELSE IF p_Sobid=XXX THEN
    :new.external_reference := p_company||'-'||REPLACE(:new.period_name,'-')||'-'||lpad(1,5,'0');
        ELSE IF p_Sobid=XXX THEN
    :new.external_reference := p_company||'-'||REPLACE(:new.period_name,'-')||'-'||lpad(1,5,'0');
    END IF;
    END IF;
    END IF;
   
end;

zspzjb

据了解，这个开发大部分公司都会有，开发也很简单，半个人工天吧