ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle GL Document Sequence Copy From Old Year

Oracle GL Document Sequence Copy From Old Year

原创 Linux操作系统 作者:xing_lang 时间:2012-03-28 10:38:03 0 删除 编辑
CREATE OR REPLACE PACKAGE GLGENSEQ_BG_1 AS
/**********************************************************/
/** This package is used for Copy GL document sequence to New Fin year
  P_COPY_FR_YEAR and P_COPY_TO_YEAR are FIN period year
  Exp:Copy from 2011 -->2012
  2011 year :01-APR-2011 to 31-MAR-2012  ,
  2012 year :01-APR-2012 to 31-MAR-2013  ,
**/
/**********************************************************/
/**                                                      **/
/**********************************************************/
    V_COPY_FR_YEAR VARCHAR2(10); --2012
    V_COPY_TO_YEAR VARCHAR2(10); --2013
    v_count_defin  number := 0;
    v_count_assig  number := 0;
    v_count        number;
    v_year         number;
    v_use_id  number:=fnd_profile.value('USER_ID');
PROCEDURE MAIN(ERRBUF         OUT varchar2,
                                          RETCODE        OUT number,
                                          P_COPY_FR_YEAR in VARCHAR2,
                                          P_COPY_TO_YEAR in VARCHAR2);
PROCEDURE COPY_NORMAL_SEQ(
                                          P_COPY_FR_YEAR in VARCHAR2,
                                          P_COPY_TO_YEAR in VARCHAR2);
PROCEDURE COPY_SPEC_SEQ(
                                          P_COPY_FR_YEAR in VARCHAR2,
                                          P_COPY_TO_YEAR in VARCHAR2);
End GLGENSEQ_BG_1;
 
CREATE OR REPLACE PACKAGE BODY GLGENSEQ_BG_1 AS
  PROCEDURE MAIN(ERRBUF         OUT varchar2,
                 RETCODE        OUT number,
                 P_COPY_FR_YEAR in VARCHAR2,
                 P_COPY_TO_YEAR in VARCHAR2) is
  begin
    V_COPY_FR_YEAR := P_COPY_FR_YEAR;
    V_COPY_TO_YEAR := P_COPY_TO_YEAR;
    v_year         := to_number(P_COPY_TO_YEAR) - to_number(P_COPY_FR_YEAR);
    --check data--
    SELECT count(*)
      into v_count
      FROM FND_DOCUMENT_SEQUENCES
     WHERE START_DATE = (select p.start_date
                           from GL_LEDGERS sob, GL_PERIODS_V p
                          where sob.ledger_id = 302
                            and p.period_set_name = sob.period_set_name
                            and p.period_year = P_COPY_TO_YEAR
                            and p.period_num = 1)
     order by name;
    if v_count = 0 then
      --check data--
      
      COPY_SPEC_SEQ(V_COPY_FR_YEAR, V_COPY_TO_YEAR);
      dbms_output.put_line('The year ' || P_COPY_TO_YEAR || '''' ||
                           's Document Sequence was Finished, It copy from ' ||
                           P_COPY_FR_YEAR || '!');
      dbms_output.put_line('Created Document Sequence: ' || v_count_defin ||
                           ' rows!');
      dbms_output.put_line('Assigned Document Sequence: ' || v_count_assig ||
                           ' rows!');
      fnd_file.put_line(fnd_file.output,
                        'The year ' || P_COPY_TO_YEAR || '''' ||
                        's Document Sequence was Finished, It copy from ' ||
                        P_COPY_FR_YEAR || '!');
      fnd_file.put_line(fnd_file.output,
                        'Created Document Sequence: ' || v_count_defin ||
                        ' rows!');
      fnd_file.put_line(fnd_file.output,
                        'Assigned Document Sequence: ' || v_count_assig ||
                        ' rows!');
    else
      dbms_output.put_line('The year ' || P_COPY_TO_YEAR || '''' ||
                           's Document Sequence already exists, Pls enter other year!');
      fnd_file.put_line(fnd_file.output,
                        'The year ' || P_COPY_TO_YEAR || '''' ||
                        's Document Sequence already exists, Pls enter other year!');
    end if;
    commit;
  EXCEPTION
    WHEN others THEN
      fnd_file.put_line(fnd_file.output,
                        'The year ' || P_COPY_TO_YEAR || '''' ||
                        's Document Sequence Creating with error!');
      dbms_output.put_line('The year ' || P_COPY_TO_YEAR || '''' ||
                           's Document Sequence Creating with error!');
      ROLLBACK;
  end;
  PROCEDURE COPY_NORMAL_SEQ(P_COPY_FR_YEAR in VARCHAR2,
                            P_COPY_TO_YEAR in VARCHAR2) is
    /**This procedure used for get whole year records */
    --declare  --
    CURSOR C_GL_DOC_SEQ(x_year varchar2) is
      select F.NAME,
             REGEXP_REPLACE(f.NAME, SUBSTR(f.NAME, -4, 4), null, 1, 1) seq_prefix,
             F.START_DATE,
             F.END_DATE,
             F.TYPE,
             F.MESSAGE_FLAG,
             F.INITIAL_VALUE,
             F.AUDIT_TABLE_NAME,
             F.DB_SEQUENCE_NAME,
             F.TABLE_NAME,
             F.DOC_SEQUENCE_ID,
             F.DOC_SEQUENCE_ID OLD_DOC_SEQUENCE_ID,
             F.LAST_UPDATE_DATE,
             F.LAST_UPDATED_BY,
             F.CREATION_DATE,
             F.CREATED_BY,
             F.LAST_UPDATE_LOGIN,
             F.APPLICATION_ID,
             (select p.period_num
                from GL_LEDGERS sob, GL_PERIODS_V p
               where sob.ledger_id = 302
                 and p.period_set_name = sob.period_set_name
                 and p.period_year = x_year
                 and p.START_DATE = F.START_DATE) period_num
        from FND_DOCUMENT_SEQUENCES f
       where F.DOC_SEQUENCE_ID in
             (SELECT distinct A.DOC_SEQUENCE_ID
                FROM FND_DOC_SEQUENCE_ASSIGNMENTS A
               WHERE A.START_DATE between
                     (select p.start_date
                        from GL_LEDGERS sob, GL_PERIODS_V p
                       where sob.ledger_id = 302
                         and p.period_set_name = sob.period_set_name
                         and p.period_year = x_year
                         and p.period_num = 1)
                 and (select p.START_DATE
                        from GL_LEDGERS sob, GL_PERIODS_V p
                       where sob.ledger_id = 302
                         and p.period_set_name = sob.period_set_name
                         and p.period_year = x_year
                         and p.period_num = 12)
                 and a.end_DATE = last_day(a.START_DATE)
                 and a.end_DATE <> a.START_DATE)
         and REGEXP_REPLACE(f.NAME, SUBSTR(f.NAME, -4, 4), null, 1, 1) in
             (select seq_prefix
                from (SELECT NAME,
                             REGEXP_REPLACE(NAME,
                                            SUBSTR(NAME, -4, 4),
                                            null,
                                            1,
                                            1) seq_prefix,
                             START_DATE,
                             END_DATE,
                             TYPE,
                             MESSAGE_FLAG,
                             INITIAL_VALUE,
                             AUDIT_TABLE_NAME,
                             DB_SEQUENCE_NAME,
                             TABLE_NAME,
                             DOC_SEQUENCE_ID OLD_DOC_SEQUENCE_ID,
                             LAST_UPDATE_DATE,
                             LAST_UPDATED_BY,
                             CREATION_DATE,
                             CREATED_BY,
                             LAST_UPDATE_LOGIN,
                             APPLICATION_ID
                        FROM FND_DOCUMENT_SEQUENCES
                       WHERE DOC_SEQUENCE_ID in
                             (SELECT distinct A.DOC_SEQUENCE_ID
                                FROM FND_DOC_SEQUENCE_ASSIGNMENTS A
                               WHERE A.START_DATE between
                                     (select p.start_date
                                        from GL_LEDGERS sob, GL_PERIODS_V p
                                       where sob.ledger_id = 302
                                         and p.period_set_name =
                                             sob.period_set_name
                                         and p.period_year = x_year
                                         and p.period_num = 1)
                                 and (select p.START_DATE
                                        from GL_LEDGERS sob, GL_PERIODS_V p
                                       where sob.ledger_id = 302
                                         and p.period_set_name =
                                             sob.period_set_name
                                         and p.period_year = x_year
                                         and p.period_num = 12)
                                 and a.end_DATE = last_day(a.START_DATE)
                                 and a.end_DATE <> a.START_DATE --add_months(a.START_DATE, 1) - 1
                              )
                      -- and NAME like 'BTCLR AR CHECK%'
                       order by name) seq
               where 1 = 1
               group by seq.seq_prefix
              having count(seq.seq_prefix) = 12)
       order by f.name;
    /*
    CURSOR C_GL_DOC_ASSIG(x_year varchar2, X_OLD_DOC_SEQUENCE_ID number) is
          SELECT A.ROWID,
                 A.METHOD_CODE,
                 A.START_DATE,
                 A.END_DATE,
                 A.SET_OF_BOOKS_ID,
                 A.CREATION_DATE,
                 A.CATEGORY_CODE,
                 A.CREATED_BY,
                 A.LAST_UPDATE_DATE,
                 A.LAST_UPDATED_BY,
                 A.LAST_UPDATE_LOGIN,
                 -- DOC_SEQUENCE_ID,
                 A.APPLICATION_ID,
                 A.DOC_SEQUENCE_ASSIGNMENT_ID
            FROM FND_DOC_SEQUENCE_ASSIGNMENTS A
           WHERE A.Doc_Sequence_Id = X_OLD_DOC_SEQUENCE_ID
             and A.START_DATE between
                 (select p.start_date
                    from GL_LEDGERS sob, GL_PERIODS_V p
                   where sob.ledger_id = 302
                     and p.period_set_name = sob.period_set_name
                     and p.period_year = x_year
                     and p.period_num = 1)
             and (select p.START_DATE
                    from GL_LEDGERS sob, GL_PERIODS_V p
                   where sob.ledger_id = 302
                     and p.period_set_name = sob.period_set_name
                     and p.period_year = x_year
                     and p.period_num = 12)
             and a.END_DATE = last_day(a.START_DATE)
             and a.END_DATE <> a.START_DATE
          -- AND SET_OF_BOOKS_ID=502
           order by APPLICATION_ID,
                    SET_OF_BOOKS_ID,
                    CATEGORY_CODE,
                    METHOD_CODE,
                    START_DATE;
      */
    ----
    CURSOR c_3(x_year varchar2, x_seq_prefix varchar2) is
      select seq_prefix,
             METHOD_CODE,
             SET_OF_BOOKS_ID,
             CATEGORY_CODE,
             APPLICATION_ID
        from (SELECT A.METHOD_CODE,
                     A.START_DATE,
                     A.END_DATE,
                     A.SET_OF_BOOKS_ID,
                     A.CREATION_DATE,
                     A.CATEGORY_CODE,
                     A.CREATED_BY,
                     A.LAST_UPDATE_DATE,
                     A.LAST_UPDATED_BY,
                     A.LAST_UPDATE_LOGIN,
                     -- DOC_SEQUENCE_ID,
                     F.NAME,
                     REGEXP_REPLACE(F.NAME,
                                    SUBSTR(F.NAME, -4, 4),
                                    null,
                                    1,
                                    1) seq_prefix,
                     A.APPLICATION_ID,
                     A.DOC_SEQUENCE_ASSIGNMENT_ID
                FROM FND_DOC_SEQUENCE_ASSIGNMENTS A,
                     FND_DOCUMENT_SEQUENCES       F
               WHERE A.Doc_Sequence_Id = F.DOC_SEQUENCE_ID
                 and REGEXP_REPLACE(F.NAME,
                                    SUBSTR(F.NAME, -4, 4),
                                    null,
                                    1,
                                    1) = x_seq_prefix --'BITLR AR RMA BEWX '
                 and A.START_DATE between
                     (select p.start_date
                        from GL_LEDGERS sob, GL_PERIODS_V p
                       where sob.ledger_id = 302
                         and p.period_set_name = sob.period_set_name
                         and p.period_year = x_year
                         and p.period_num = 1)
                 and (select p.START_DATE
                        from GL_LEDGERS sob, GL_PERIODS_V p
                       where sob.ledger_id = 302
                         and p.period_set_name = sob.period_set_name
                         and p.period_year = x_year
                         and p.period_num = 12)
                 and a.END_DATE = last_day(a.START_DATE)
                 and a.END_DATE <> a.START_DATE
               order by F.name) Seq
       where 1 = 1
       group by seq_prefix,
                METHOD_CODE,
                SET_OF_BOOKS_ID,
                CATEGORY_CODE,
                APPLICATION_ID
      /*having count(seq.seq_prefix) = 12*/
      ;
    --Assgin templete with 12 records --
    CURSOR C_GL_DOC_ASSIG(x_year            varchar2,
                          x_seq_prefix      varchar2,
                          x_METHOD_CODE     varchar2,
                          x_SET_OF_BOOKS_ID number,
                          x_CATEGORY_CODE   varchar2,
                          x_APPLICATION_ID  number,
                          x_START_DATE      date,
                          x_END_DATE        date) is
      select nvl(Seq.METHOD_CODE, x_METHOD_CODE) METHOD_CODE,
             nvl(seq.START_DATE, period.START_DATE) START_DATE,
             nvl(seq.END_DATE, period.END_DATE) END_DATE,
             nvl(Seq.SET_OF_BOOKS_ID, x_SET_OF_BOOKS_ID) SET_OF_BOOKS_ID,
             nvl(Seq.CATEGORY_CODE, x_CATEGORY_CODE) CATEGORY_CODE,
             nvl(seq.LAST_UPDATE_DATE, sysdate),
             nvl(seq.LAST_UPDATED_BY, 1),
             nvl(seq.CREATION_DATE, sysdate),
             nvl(seq.CREATED_BY, 1),
             nvl(seq.LAST_UPDATE_LOGIN, 1),
             nvl(seq.APPLICATION_ID, x_APPLICATION_ID) APPLICATION_ID,
             Seq.DOC_SEQUENCE_ASSIGNMENT_ID
        from (select p.period_num, p.START_DATE, p.end_DATE
                from GL_LEDGERS sob, GL_PERIODS_V p
               where sob.ledger_id = 302
                 and p.period_set_name = sob.period_set_name
                 and p.period_year = x_year
                 and p.period_num <> 13) period,
             (SELECT A.ROWID,
                     A.METHOD_CODE,
                     A.START_DATE,
                     A.END_DATE,
                     A.SET_OF_BOOKS_ID,
                     A.CREATION_DATE,
                     A.CATEGORY_CODE,
                     A.CREATED_BY,
                     A.LAST_UPDATE_DATE,
                     A.LAST_UPDATED_BY,
                     A.LAST_UPDATE_LOGIN,
                     -- DOC_SEQUENCE_ID,
                     F.NAME,
                     REGEXP_REPLACE(F.NAME,
                                    SUBSTR(F.NAME, -4, 4),
                                    null,
                                    1,
                                    1) seq_prefix,
                     A.APPLICATION_ID,
                     A.DOC_SEQUENCE_ASSIGNMENT_ID
                FROM FND_DOC_SEQUENCE_ASSIGNMENTS A,
                     FND_DOCUMENT_SEQUENCES       F
               WHERE A.Doc_Sequence_Id = F.DOC_SEQUENCE_ID
                 and A.START_DATE between
                     (select p.start_date
                        from GL_LEDGERS sob, GL_PERIODS_V p
                       where sob.ledger_id = 302
                         and p.period_set_name = sob.period_set_name
                         and p.period_year = x_year
                         and p.period_num = 1)
                 and (select p.START_DATE
                        from GL_LEDGERS sob, GL_PERIODS_V p
                       where sob.ledger_id = 302
                         and p.period_set_name = sob.period_set_name
                         and p.period_year = x_year
                         and p.period_num = 12)
                 and a.END_DATE = last_day(a.START_DATE)
                 and a.END_DATE <> a.START_DATE
                 and REGEXP_REPLACE(F.NAME,
                                    SUBSTR(F.NAME, -4, 4),
                                    null,
                                    1,
                                    1) = x_seq_prefix
                 and A.SET_OF_BOOKS_ID =
                     nvl(X_SET_OF_BOOKS_ID, A.SET_OF_BOOKS_ID)
                 and A.APPLICATION_ID =
                     nvl(x_APPLICATION_ID, A.APPLICATION_ID)
                 and A.METHOD_CODE = nvl(x_METHOD_CODE, A.METHOD_CODE)
                 and A.CATEGORY_CODE = nvl(x_CATEGORY_CODE, A.CATEGORY_CODE)) Seq
       where seq.start_date(+) = period.START_DATE
         and period.START_DATE = x_START_DATE
         and period.END_DATE = x_END_DATE
       order by period.period_num;
    --Assgin templete with 12 records --
    V_DOC_SEQUENCE_ID  FND_DOCUMENT_SEQUENCES.DOC_SEQUENCE_ID%type;
    V_SEQUENCE_NAME    FND_DOCUMENT_SEQUENCES.NAME%type;
    V_DB_SEQUENCE_NAME FND_DOCUMENT_SEQUENCES.DB_SEQUENCE_NAME%type;
    v_Start_Date    FND_DOCUMENT_SEQUENCES.START_DATE%type;
    v_end_Date      FND_DOCUMENT_SEQUENCES.END_DATE%type;
    v_INITIAL_VALUE FND_DOCUMENT_SEQUENCES.Initial_Value%type;
    V_DOC_SEQUENCE_ASSIGNMENT_ID FND_DOC_SEQUENCE_ASSIGNMENTS.Doc_Sequence_Assignment_Id%type;
    V_CATEGORY_CODE FND_DOC_SEQUENCE_ASSIGNMENTS.CATEGORY_CODE%type;
    V_SOB_ID        FND_DOC_SEQUENCE_ASSIGNMENTS.SET_OF_BOOKS_ID%type;
    GL_DOC_SEQ   C_GL_DOC_SEQ %rowtype;
    cur_3        C_3 %rowtype;
    GL_DOC_ASSIG C_GL_DOC_ASSIG %rowtype;
  begin
    dbms_output.put_line('Part1:Processing that with whole year define records document!');
    fnd_file.put_line(fnd_file.output,
                      'Part1:Processing that with whole year define records document!');
    for GL_DOC_SEQ in C_GL_DOC_SEQ(V_COPY_FR_YEAR) loop
      EXIT WHEN C_GL_DOC_SEQ%NOTFOUND;
      v_count_defin := v_count_defin + 1;
      /*  dbms_output.put_line('Part1:Processing that with whole year records document,Seq prefix:'||GL_DOC_SEQ.seq_prefix);
         fnd_file.put_line(fnd_file.output,
                           'Part1:Processing that with whole year records document,Seq prefix:'||GL_DOC_SEQ.seq_prefix);
      */
      select FND_DOCUMENT_SEQUENCES_S.nextval,
             REGEXP_REPLACE(GL_DOC_SEQ.NAME,
                            SUBSTR(GL_DOC_SEQ.NAME, -4, 2),
                            to_char(add_months(GL_DOC_SEQ.START_DATE,
                                               v_year * 12),
                                    'YY'),
                            1,
                            1),
             add_months(GL_DOC_SEQ.START_DATE, v_year * 12),
             add_months(GL_DOC_SEQ.END_DATE, v_year * 12),
             SUBSTR(GL_DOC_SEQ.INITIAL_VALUE, 1, 1) ||
             to_char(add_months(GL_DOC_SEQ.START_DATE, v_year * 12), 'YY') ||
             to_char(add_months(GL_DOC_SEQ.START_DATE, v_year * 12), 'MM') ||
             SUBSTR(GL_DOC_SEQ.INITIAL_VALUE, 6)
        into V_DOC_SEQUENCE_ID,
             V_SEQUENCE_NAME,
             V_START_DATE,
             V_END_DATE,
             v_INITIAL_VALUE
        from SYS.DUAL;
      V_DB_SEQUENCE_NAME := 'FND_DOC_SEQ_' ||
                           /*app_number.number_to_canonical*/
                            (V_DOC_SEQUENCE_ID) || '_S';
      -- Calls FND_SEQNUM.create_db_seq routine to create DB sequences
        -- new automatic Doc_Seq numbers.
        FND_SEQNUM.create_db_seq( V_DB_SEQUENCE_NAME, v_INITIAL_VALUE );
      --Bug--
      INSERT INTO FND_DOCUMENT_SEQUENCES
        (NAME,
         START_DATE,
         END_DATE,
         TYPE,
         MESSAGE_FLAG,
         INITIAL_VALUE,
         AUDIT_TABLE_NAME,
         DB_SEQUENCE_NAME,
         TABLE_NAME,
         DOC_SEQUENCE_ID,
         LAST_UPDATE_DATE,
         LAST_UPDATED_BY,
         CREATION_DATE,
         CREATED_BY,
         LAST_UPDATE_LOGIN,
         APPLICATION_ID)
      VALUES
        (V_SEQUENCE_NAME,
         V_START_DATE,
         V_END_DATE,
         GL_DOC_SEQ.TYPE,
         GL_DOC_SEQ.MESSAGE_FLAG,
         V_INITIAL_VALUE,
         GL_DOC_SEQ.AUDIT_TABLE_NAME,
         V_DB_SEQUENCE_NAME,
         GL_DOC_SEQ.TABLE_NAME,
         V_DOC_SEQUENCE_ID,
         SYSDATE,
         v_use_id,
         SYSDATE,
         v_use_id,
         v_use_id,
         GL_DOC_SEQ.APPLICATION_ID);
      /*dbms_output.put_line('OLD ID:' || GL_DOC_SEQ.OLD_DOC_SEQUENCE_ID);
      dbms_output.put_line('NEW ID:' || v_DOC_SEQUENCE_ID);*/
      /*---2. Assign Doc--
      for GL_DOC_ASSIG in C_GL_DOC_ASSIG(V_COPY_FR_YEAR,
                                         GL_DOC_SEQ.OLD_DOC_SEQUENCE_ID) loop
        EXIT WHEN C_GL_DOC_ASSIG%NOTFOUND;
        v_count_assig := v_count_assig + 1;
        select FND_DOC_SEQUENCE_ASSIGNMENTS_S.NEXTVAL,
               add_months(GL_DOC_ASSIG.START_DATE, v_year * 12),
               add_months(GL_DOC_ASSIG.END_DATE, v_year * 12)
          into V_DOC_SEQUENCE_ASSIGNMENT_ID, V_START_DATE, V_END_DATE
          from SYS.DUAL;*/
      ---2. Assign Doc--
      for cur_3 in C_3(V_COPY_FR_YEAR, GL_DOC_SEQ.seq_prefix) loop
        EXIT WHEN C_3%NOTFOUND;
        for GL_DOC_ASSIG in C_GL_DOC_ASSIG(V_COPY_FR_YEAR,
                                           cur_3.seq_prefix,
                                           cur_3.METHOD_CODE,
                                           cur_3.SET_OF_BOOKS_ID,
                                           cur_3.CATEGORY_CODE,
                                           cur_3.application_id,
                                           GL_DOC_SEQ.START_DATE,
                                           GL_DOC_SEQ.END_DATE) loop
          EXIT WHEN C_GL_DOC_ASSIG%NOTFOUND;
          v_count_assig := v_count_assig + 1;
          select FND_DOC_SEQUENCE_ASSIGNMENTS_S.NEXTVAL,
                 add_months(GL_DOC_ASSIG.START_DATE, v_year * 12),
                 add_months(GL_DOC_ASSIG.END_DATE, v_year * 12)
            into V_DOC_SEQUENCE_ASSIGNMENT_ID, V_START_DATE, V_END_DATE
            from SYS.DUAL;
          --  dbms_output.put_line('Assign Category Code:'||GL_DOC_ASSIG.CATEGORY_CODE);
          insert into FND_DOC_SEQUENCE_ASSIGNMENTS
            (METHOD_CODE,
             START_DATE,
             END_DATE,
             SET_OF_BOOKS_ID,
             CREATION_DATE,
             CATEGORY_CODE,
             CREATED_BY,
             LAST_UPDATE_DATE,
             LAST_UPDATED_BY,
             LAST_UPDATE_LOGIN,
             DOC_SEQUENCE_ID,
             APPLICATION_ID,
             DOC_SEQUENCE_ASSIGNMENT_ID)
          VALUES
            (null,
             V_START_DATE,
             V_END_DATE,
             GL_DOC_ASSIG.SET_OF_BOOKS_ID,
             sysdate,
             GL_DOC_ASSIG.CATEGORY_CODE,
             v_use_id,
             sysdate,
             v_use_id,
             v_use_id,
             v_DOC_SEQUENCE_ID,
             GL_DOC_ASSIG.APPLICATION_ID,
             V_DOC_SEQUENCE_ASSIGNMENT_ID);
        END LOOP;
      END LOOP;
    END LOOP;
  end;
  ---
  PROCEDURE COPY_SPEC_SEQ(P_COPY_FR_YEAR in VARCHAR2,
                          P_COPY_TO_YEAR in VARCHAR2) is
    /**This procedure used for get without whole year records
    Some companies not created whole year records when
    copy to new year we need creat whole year 's document records,
    */
    CURSOR c_1(x_year varchar2) is
    select seq_prefix,
       TYPE,
       value_type,
       AUDIT_TABLE_NAME,
       MESSAGE_FLAG,
       APPLICATION_ID
  from (SELECT NAME,
               REGEXP_REPLACE(NAME, SUBSTR(NAME, -4, 4), null, 1, 1) seq_prefix,
               START_DATE,
               END_DATE,
               TYPE,
               MESSAGE_FLAG,
               INITIAL_VALUE,
               substr(INITIAL_VALUE, 1, 1) value_type,
               AUDIT_TABLE_NAME,
               DB_SEQUENCE_NAME,
               TABLE_NAME,
               DOC_SEQUENCE_ID OLD_DOC_SEQUENCE_ID,
               LAST_UPDATE_DATE,
               LAST_UPDATED_BY,
               CREATION_DATE,
               CREATED_BY,
               LAST_UPDATE_LOGIN,
               APPLICATION_ID
          FROM FND_DOCUMENT_SEQUENCES
         WHERE DOC_SEQUENCE_ID in
               (SELECT distinct A.DOC_SEQUENCE_ID
                  FROM FND_DOC_SEQUENCE_ASSIGNMENTS A
                 WHERE A.START_DATE =
                       (select p.START_DATE
                          from GL_LEDGERS sob, GL_PERIODS_V p
                         where sob.ledger_id = 302
                           and p.period_set_name = sob.period_set_name
                           and p.period_year = x_year
                           and p.period_num = 12)
                   and a.end_DATE = last_day(a.START_DATE)
                   and a.end_DATE <> a.START_DATE
                )--Get Last month 's Sequence ID
         order by name) seq
 where 1 = 1
 group by seq_prefix,
          TYPE,
          value_type,
          AUDIT_TABLE_NAME,
          --TABLE_NAME,
          MESSAGE_FLAG,
          APPLICATION_ID;
      
---Define Seq
    CURSOR C_2(x_year             varchar2,
               x_seq_prefix       varchar2,
               x_AUDIT_TABLE_NAME varchar2,
               -- x_TABLE_NAME       varchar2,
               x_type           varchar2,
               x_value_type     varchar2,
               x_MESSAGE_FLAG   varchar2,
               x_application_id number) is
      select period.period_num,
             nvl(seq.NAME,
                 x_seq_prefix ||
                 to_char(nvl(seq.START_DATE, period.START_DATE), 'YY') ||
                 to_char(nvl(seq.START_DATE, period.START_DATE), 'MM')) name,
             nvl(seq.seq_prefix, x_seq_prefix) seq_prefix,
             nvl(seq.START_DATE, period.START_DATE) START_DATE,
             nvl(seq.END_DATE, period.END_DATE) END_DATE,
             nvl(seq.TYPE, x_type) TYPE,
             nvl(seq.MESSAGE_FLAG, x_MESSAGE_FLAG) MESSAGE_FLAG,
             nvl(seq.INITIAL_VALUE,
                 x_value_type ||
                 to_char(nvl(seq.START_DATE, period.START_DATE), 'YY') ||
                 to_char(nvl(seq.START_DATE, period.START_DATE), 'MM') ||
                 '0001') INITIAL_VALUE,
             --
             nvl(seq.AUDIT_TABLE_NAME, x_AUDIT_TABLE_NAME) AUDIT_TABLE_NAME,
             seq.DB_SEQUENCE_NAME,
             seq.TABLE_NAME,
             seq.OLD_DOC_SEQUENCE_ID,
             --
             nvl(seq.LAST_UPDATE_DATE, sysdate),
             nvl(seq.LAST_UPDATED_BY, 1),
             nvl(seq.CREATION_DATE, sysdate),
             nvl(seq.CREATED_BY, 1),
             nvl(seq.LAST_UPDATE_LOGIN, 1),
             nvl(seq.APPLICATION_ID, x_APPLICATION_ID) APPLICATION_ID
        from (select p.period_num, p.START_DATE, p.end_DATE
                from GL_LEDGERS sob, GL_PERIODS_V p
               where sob.ledger_id = 302
                 and p.period_set_name = sob.period_set_name
                 and p.period_year = x_year
                 and p.period_num <> 13) period,
             (SELECT F.NAME,
                     REGEXP_REPLACE(F.NAME,
                                    SUBSTR(F.NAME, -4, 4),
                                    null,
                                    1,
                                    1) seq_prefix,
                     F.START_DATE,
                     F.END_DATE,
                     F.TYPE,
                     F.MESSAGE_FLAG,
                     F.INITIAL_VALUE,
                     F.AUDIT_TABLE_NAME,
                     F.DB_SEQUENCE_NAME,
                     F.TABLE_NAME,
                     F.DOC_SEQUENCE_ID OLD_DOC_SEQUENCE_ID,
                     F.LAST_UPDATE_DATE,
                     F.LAST_UPDATED_BY,
                     F.CREATION_DATE,
                     F.CREATED_BY,
                     F.LAST_UPDATE_LOGIN,
                     F.APPLICATION_ID
                FROM FND_DOCUMENT_SEQUENCES F
               WHERE F.DOC_SEQUENCE_ID in
                     (SELECT distinct A.DOC_SEQUENCE_ID
                        FROM FND_DOC_SEQUENCE_ASSIGNMENTS A
                       WHERE A.START_DATE between
                             (select p.start_date
                                from GL_LEDGERS sob, GL_PERIODS_V p
                               where sob.ledger_id = 302
                                 and p.period_set_name = sob.period_set_name
                                 and p.period_year = x_year
                                 and p.period_num = 1)
                         and (select p.START_DATE
                                from GL_LEDGERS sob, GL_PERIODS_V p
                               where sob.ledger_id = 302
                                 and p.period_set_name = sob.period_set_name
                                 and p.period_year = x_year
                                 and p.period_num = 12)
                         and a.end_DATE = last_day(a.START_DATE)
                         and a.end_DATE <> a.START_DATE
                      )
                 and REGEXP_REPLACE(F.NAME,
                                    SUBSTR(F.NAME, -4, 4),
                                    null,
                                    1,
                                    1) = x_seq_prefix /*'BITLR AR OE INV BEWX '*/
                 and F.APPLICATION_ID =
                     nvl(x_APPLICATION_ID, F.APPLICATION_ID)
                 and F.AUDIT_TABLE_NAME =
                     nvl(x_AUDIT_TABLE_NAME, F.AUDIT_TABLE_NAME)
                 and F.type = nvl(x_type, F.type)
                 and F.MESSAGE_FLAG = nvl(x_MESSAGE_FLAG, F.MESSAGE_FLAG)) Seq
       where seq.start_date(+) = period.START_DATE
       order by period.period_num;
   
    ---Assign Sequence--
    CURSOR c_3(x_year varchar2, x_seq_prefix varchar2) is
      select seq_prefix,
             METHOD_CODE,
             SET_OF_BOOKS_ID,
             CATEGORY_CODE,
             APPLICATION_ID
        from (SELECT A.METHOD_CODE,
                     A.START_DATE,
                     A.END_DATE,
                     A.SET_OF_BOOKS_ID,
                     A.CREATION_DATE,
                     A.CATEGORY_CODE,
                     A.CREATED_BY,
                     A.LAST_UPDATE_DATE,
                     A.LAST_UPDATED_BY,
                     A.LAST_UPDATE_LOGIN,
                     -- DOC_SEQUENCE_ID,
                     F.NAME,
                     REGEXP_REPLACE(F.NAME,
                                    SUBSTR(F.NAME, -4, 4),
                                    null,
                                    1,
                                    1) seq_prefix,
                     A.APPLICATION_ID,
                     A.DOC_SEQUENCE_ASSIGNMENT_ID
                FROM FND_DOC_SEQUENCE_ASSIGNMENTS A,
                     FND_DOCUMENT_SEQUENCES       F
               WHERE A.Doc_Sequence_Id = F.DOC_SEQUENCE_ID
                 and REGEXP_REPLACE(F.NAME,
                                    SUBSTR(F.NAME, -4, 4),
                                    null,
                                    1,
                                    1) = x_seq_prefix --'BITLR AR RMA BEWX '
               /*  and A.START_DATE between
                     (select p.start_date
                        from GL_LEDGERS sob, GL_PERIODS_V p
                       where sob.ledger_id = 302
                         and p.period_set_name = sob.period_set_name
                         and p.period_year = x_year
                         and p.period_num = 1)
                 and (select p.START_DATE
                        from GL_LEDGERS sob, GL_PERIODS_V p
                       where sob.ledger_id = 302
                         and p.period_set_name = sob.period_set_name
                         and p.period_year = x_year
                         and p.period_num = 12)*/
                  ---
                  and  A.START_DATE =
                       (select p.START_DATE
                          from GL_LEDGERS sob, GL_PERIODS_V p
                         where sob.ledger_id = 302
                           and p.period_set_name = sob.period_set_name
                           and p.period_year = x_year
                           and p.period_num = 12)
                  ----
                 and a.END_DATE = last_day(a.START_DATE)
                 and a.END_DATE <> a.START_DATE
               order by F.name) Seq
       where 1 = 1
       group by seq_prefix,
                METHOD_CODE,
                SET_OF_BOOKS_ID,
                CATEGORY_CODE,
                APPLICATION_ID;
               
    --Assgin templete with 12 records --
    CURSOR C_GL_DOC_ASSIG(x_year            varchar2,
                          x_seq_prefix      varchar2,
                          x_METHOD_CODE     varchar2,
                          x_SET_OF_BOOKS_ID number,
                          x_CATEGORY_CODE   varchar2,
                          x_APPLICATION_ID  number,
                          x_START_DATE      date,
                          x_END_DATE        date) is
      select nvl(Seq.METHOD_CODE, x_METHOD_CODE) METHOD_CODE,
             nvl(seq.START_DATE, period.START_DATE) START_DATE,
             nvl(seq.END_DATE, period.END_DATE) END_DATE,
             nvl(Seq.SET_OF_BOOKS_ID, x_SET_OF_BOOKS_ID) SET_OF_BOOKS_ID,
             nvl(Seq.CATEGORY_CODE, x_CATEGORY_CODE) CATEGORY_CODE,
             nvl(seq.LAST_UPDATE_DATE, sysdate),
             nvl(seq.LAST_UPDATED_BY, 1),
             nvl(seq.CREATION_DATE, sysdate),
             nvl(seq.CREATED_BY, 1),
             nvl(seq.LAST_UPDATE_LOGIN, 1),
             nvl(seq.APPLICATION_ID, x_APPLICATION_ID) APPLICATION_ID,
             Seq.DOC_SEQUENCE_ASSIGNMENT_ID
        from (select p.period_num, p.START_DATE, p.end_DATE
                from GL_LEDGERS sob, GL_PERIODS_V p
               where sob.ledger_id = 302
                 and p.period_set_name = sob.period_set_name
                 and p.period_year = x_year
                 and p.period_num <> 13) period,
             (SELECT A.ROWID,
                     A.METHOD_CODE,
                     A.START_DATE,
                     A.END_DATE,
                     A.SET_OF_BOOKS_ID,
                     A.CREATION_DATE,
                     A.CATEGORY_CODE,
                     A.CREATED_BY,
                     A.LAST_UPDATE_DATE,
                     A.LAST_UPDATED_BY,
                     A.LAST_UPDATE_LOGIN,
                     -- DOC_SEQUENCE_ID,
                     F.NAME,
                     REGEXP_REPLACE(F.NAME,
                                    SUBSTR(F.NAME, -4, 4),
                                    null,
                                    1,
                                    1) seq_prefix,
                     A.APPLICATION_ID,
                     A.DOC_SEQUENCE_ASSIGNMENT_ID
                FROM FND_DOC_SEQUENCE_ASSIGNMENTS A,
                     FND_DOCUMENT_SEQUENCES       F
               WHERE A.Doc_Sequence_Id = F.DOC_SEQUENCE_ID
                 and A.START_DATE between
                     (select p.start_date
                        from GL_LEDGERS sob, GL_PERIODS_V p
                       where sob.ledger_id = 302
                         and p.period_set_name = sob.period_set_name
                         and p.period_year = x_year
                         and p.period_num = 1)
                 and (select p.START_DATE
                        from GL_LEDGERS sob, GL_PERIODS_V p
                       where sob.ledger_id = 302
                         and p.period_set_name = sob.period_set_name
                         and p.period_year = x_year
                         and p.period_num = 12)
                 and a.END_DATE = last_day(a.START_DATE)
                 and a.END_DATE <> a.START_DATE
                 and REGEXP_REPLACE(F.NAME,
                                    SUBSTR(F.NAME, -4, 4),
                                    null,
                                    1,
                                    1) = x_seq_prefix
                 and A.SET_OF_BOOKS_ID =
                     nvl(X_SET_OF_BOOKS_ID, A.SET_OF_BOOKS_ID)
                 and A.APPLICATION_ID =
                     nvl(x_APPLICATION_ID, A.APPLICATION_ID)
                 and A.METHOD_CODE = nvl(x_METHOD_CODE, A.METHOD_CODE)
                 and A.CATEGORY_CODE = nvl(x_CATEGORY_CODE, A.CATEGORY_CODE)) Seq
       where seq.start_date(+) = period.START_DATE
         and period.START_DATE = x_START_DATE
         and period.END_DATE = x_END_DATE
       order by period.period_num;
    --Assgin templete with 12 records --
    V_DOC_SEQUENCE_ID  FND_DOCUMENT_SEQUENCES.DOC_SEQUENCE_ID%type;
    V_SEQUENCE_NAME    FND_DOCUMENT_SEQUENCES.NAME%type;
    V_DB_SEQUENCE_NAME FND_DOCUMENT_SEQUENCES.DB_SEQUENCE_NAME%type;
    v_Start_Date    FND_DOCUMENT_SEQUENCES.START_DATE%type;
    v_end_Date      FND_DOCUMENT_SEQUENCES.END_DATE%type;
    v_INITIAL_VALUE FND_DOCUMENT_SEQUENCES.Initial_Value%type;
    V_DOC_SEQUENCE_ASSIGNMENT_ID FND_DOC_SEQUENCE_ASSIGNMENTS.Doc_Sequence_Assignment_Id%type;
    V_CATEGORY_CODE FND_DOC_SEQUENCE_ASSIGNMENTS.CATEGORY_CODE%type;
    V_SOB_ID        FND_DOC_SEQUENCE_ASSIGNMENTS.SET_OF_BOOKS_ID%type;
    cur_1      C_1 %rowtype;
    GL_DOC_SEQ C_2 %rowtype;
    cur_3        C_3 %rowtype;
    GL_DOC_ASSIG C_GL_DOC_ASSIG %rowtype;
  begin
    dbms_output.put_line('Processing GL Document Sequence!');
    fnd_file.put_line(fnd_file.output,'Processing GL Document Sequence!');
    for cur_1 in C_1(V_COPY_FR_YEAR) loop
      EXIT WHEN C_1%NOTFOUND;
    --  dbms_output.put_line('Seq prefix:' || cur_1.seq_prefix);
      fnd_file.put_line(fnd_file.output, 'Seq prefix:' || cur_1.seq_prefix);
      for GL_DOC_SEQ in C_2(V_COPY_FR_YEAR,
                            cur_1.seq_prefix,
                            cur_1.AUDIT_TABLE_NAME,
                            cur_1.TYPE,
                            cur_1.value_type,
                            cur_1.message_flag,
                            cur_1.application_id) loop
        EXIT WHEN C_2%NOTFOUND;
        v_count_defin := v_count_defin + 1;
        select FND_DOCUMENT_SEQUENCES_S.nextval,
               REGEXP_REPLACE(GL_DOC_SEQ.NAME,
                              SUBSTR(GL_DOC_SEQ.NAME, -4, 2),
                              to_char(add_months(GL_DOC_SEQ.START_DATE,
                                                 v_year * 12),
                                      'YY'),
                              1,
                              1),
               add_months(GL_DOC_SEQ.START_DATE, v_year * 12),
               add_months(GL_DOC_SEQ.END_DATE, v_year * 12),
               SUBSTR(GL_DOC_SEQ.INITIAL_VALUE, 1, 1) ||
               to_char(add_months(GL_DOC_SEQ.START_DATE, v_year * 12), 'YY') ||
               to_char(add_months(GL_DOC_SEQ.START_DATE, v_year * 12), 'MM') ||
               SUBSTR(GL_DOC_SEQ.INITIAL_VALUE, 6)
          into V_DOC_SEQUENCE_ID,
               V_SEQUENCE_NAME,
               V_START_DATE,
               V_END_DATE,
               v_INITIAL_VALUE
          from SYS.DUAL;
        V_DB_SEQUENCE_NAME := 'FND_DOC_SEQ_' ||
                             /*app_number.number_to_canonical*/
                              (V_DOC_SEQUENCE_ID) || '_S';
        -- Calls FND_SEQNUM.create_db_seq routine to create DB sequences
        -- new automatic Doc_Seq numbers.
        FND_SEQNUM.create_db_seq( V_DB_SEQUENCE_NAME, v_INITIAL_VALUE );
        --Bug--

        INSERT INTO FND_DOCUMENT_SEQUENCES
          (NAME,
           START_DATE,
           END_DATE,
           TYPE,
           MESSAGE_FLAG,
           INITIAL_VALUE,
           AUDIT_TABLE_NAME,
           DB_SEQUENCE_NAME,
           TABLE_NAME,
           DOC_SEQUENCE_ID,
           LAST_UPDATE_DATE,
           LAST_UPDATED_BY,
           CREATION_DATE,
           CREATED_BY,
           LAST_UPDATE_LOGIN,
           APPLICATION_ID)
        VALUES
          (V_SEQUENCE_NAME,
           V_START_DATE,
           V_END_DATE,
           GL_DOC_SEQ.TYPE,
           GL_DOC_SEQ.MESSAGE_FLAG,
           V_INITIAL_VALUE,
           GL_DOC_SEQ.AUDIT_TABLE_NAME,
           V_DB_SEQUENCE_NAME,
           GL_DOC_SEQ.TABLE_NAME,
           V_DOC_SEQUENCE_ID,
           SYSDATE,
           v_use_id,
           SYSDATE,
           v_use_id,
           v_use_id,
           GL_DOC_SEQ.APPLICATION_ID);
       -- dbms_output.put_line('Assign Doc:' || V_SEQUENCE_NAME);
        fnd_file.put_line(fnd_file.output,
                          'Assign Doc:' || V_SEQUENCE_NAME);
        ---2. Assign Doc--
        for cur_3 in C_3(V_COPY_FR_YEAR, cur_1.seq_prefix) loop
          EXIT WHEN C_3%NOTFOUND;
          for GL_DOC_ASSIG in C_GL_DOC_ASSIG(V_COPY_FR_YEAR,
                                             cur_3.seq_prefix,
                                             cur_3.METHOD_CODE,
                                             cur_3.SET_OF_BOOKS_ID,
                                             cur_3.CATEGORY_CODE,
                                             cur_3.application_id,
                                             GL_DOC_SEQ.START_DATE,
                                             GL_DOC_SEQ.END_DATE) loop
            EXIT WHEN C_GL_DOC_ASSIG%NOTFOUND;
            v_count_assig := v_count_assig + 1;
            select FND_DOC_SEQUENCE_ASSIGNMENTS_S.NEXTVAL,
                   add_months(GL_DOC_ASSIG.START_DATE, v_year * 12),
                   add_months(GL_DOC_ASSIG.END_DATE, v_year * 12)
              into V_DOC_SEQUENCE_ASSIGNMENT_ID, V_START_DATE, V_END_DATE
              from SYS.DUAL;
           /* dbms_output.put_line('Assign Category Code:' ||
                                 GL_DOC_ASSIG.CATEGORY_CODE);*/
            insert into FND_DOC_SEQUENCE_ASSIGNMENTS
              (METHOD_CODE,
               START_DATE,
               END_DATE,
               SET_OF_BOOKS_ID,
               CREATION_DATE,
               CATEGORY_CODE,
               CREATED_BY,
               LAST_UPDATE_DATE,
               LAST_UPDATED_BY,
               LAST_UPDATE_LOGIN,
               DOC_SEQUENCE_ID,
               APPLICATION_ID,
               DOC_SEQUENCE_ASSIGNMENT_ID)
            VALUES
              (null,
               V_START_DATE,
               V_END_DATE,
               GL_DOC_ASSIG.SET_OF_BOOKS_ID,
               sysdate,
               GL_DOC_ASSIG.CATEGORY_CODE,
               v_use_id,
               sysdate,
               v_use_id,
               v_use_id,
               v_DOC_SEQUENCE_ID,
               GL_DOC_ASSIG.APPLICATION_ID,
               V_DOC_SEQUENCE_ASSIGNMENT_ID);
          END LOOP;
        end loop;
      end loop;
    end loop;
  end;
end GLGENSEQ_BG_1;

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9182041/viewspace-719698/,如需转载,请注明出处,否则将追究法律责任。

下一篇: Oracle ERP Maintenance
请登录后发表评论 登录
全部评论

注册时间:2008-05-15

  • 博文量
    39
  • 访问量
    115631