EBS_FORM_开发:关于FORM-record的复制

1.先写PKG-FUNCTION:

--复制个人记分卡--
  PROCEDURE people_card_copy(p_old_header_id IN NUMBER,
                             p_new_header_id IN NUMBER,
                             x_return_code   OUT VARCHAR2,
                             x_return_msg    OUT VARCHAR2) IS
    l_header_rec CUX_HRPE_HEADERS_T%ROWTYPE;
    l_line_rec   CUX_HRPE_LINES_T%ROWTYPE;
    l_mid_rec    CUX_HRPE_MID_T%ROWTYPE;
    l_mend_rec   CUX_HRPE_MEND_T%ROWTYPE;

    l_error_msg VARCHAR2(2000);

    l_line_count NUMBER := 0;
    l_MID_count  NUMBER := 0;
    l_mend_count NUMBER := 0;

    CURSOR cur_header IS
      SELECT * FROM CUX_HRPE_HEADERS_T t WHERE t.header_id = p_old_header_id;

    CURSOR cur_line IS
      SELECT * FROM CUX_HRPE_LINES_T t WHERE t.header_id = p_old_header_id;
    
    CURSOR cur_MID(p_line_id NUMBER) IS
      SELECT * FROM CUX_HRPE_MID_T t WHERE t.line_id = p_line_id;
    
    CURSOR cur_mend(p_MID_id NUMBER) IS
      SELECT * FROM CUX_HRPE_MEND_T t WHERE t.MID_id = p_MID_id;
      
  BEGIN
    x_return_code := fnd_api.g_ret_sts_success;
    x_return_msg  := NULL;

    Open  cur_header;
    FETCH cur_header
     INTO l_header_rec;
    CLOSE cur_header;

    IF l_header_rec.header_id IS NULL THEN
      l_error_msg := '没找到头数据源,HEADER_ID ' || p_old_header_id;
      RAISE fnd_api.g_exc_error;
    END IF;

    IF p_new_header_id IS NULL THEN
      l_error_msg := '请先保存头数据,再进行复制';
      RAISE fnd_api.g_exc_error;
    END IF;

    
    --复制头--
    l_header_rec.created_by        := fnd_global.user_id;
    l_header_rec.last_updated_by   := fnd_global.user_id;
    l_header_rec.last_update_login := fnd_global.user_id;
    l_header_rec.creation_date     := SYSDATE;
    l_header_rec.last_update_date  := SYSDATE;
    l_header_rec.status_type       := 'NS';
/*
    SELECT CUX_HRSC_HEADERS_T_S.nextval INTO l_header_rec.header_id FROM dual;
    INSERT INTO CUX_HRSC_HEADERS_T VALUES l_header_rec;
    p_new_header_id := l_header_rec.header_id; 
*/

  
    --复制行--
  FOR c_line IN cur_line LOOP
      l_line_count := l_line_count + 1;
      l_line_rec   := NULL;

      l_line_rec.created_by          := fnd_global.user_id;
      l_line_rec.last_updated_by     := fnd_global.user_id;
      l_line_rec.last_update_login   := fnd_global.user_id;
      l_line_rec.creation_date       := SYSDATE;
      l_line_rec.last_update_date    := SYSDATE;
      
      l_line_rec.header_id           := p_new_header_id;
      SELECT CUX_HRPE_LINES_T_S.nextval INTO l_line_rec.line_id FROM dual;
      
      l_line_rec.LINE_NUMBER         := c_line.LINE_NUMBER;
      l_line_rec.MAX_LINE_NUMBER     := c_line.MAX_LINE_NUMBER;
      l_line_rec.OBJECT_VERSION_NUMBER       := c_line.OBJECT_VERSION_NUMBER;
      
      l_line_rec.WPM_WEIGHTING      := c_line.WPM_WEIGHTING;
      l_line_rec.WPM_KPI_BIG        := c_line.WPM_KPI_BIG;
      l_line_rec.BIG_EXPLAIN        := c_line.BIG_EXPLAIN;
      
      INSERT INTO CUX_HRPE_LINES_T VALUES l_line_rec;
      --复制MID
         FOR c_MID IN cur_MID(c_line.line_id) LOOP
            --l_mend_rec   := c_mend;
            l_MID_rec := NULL;

            l_MID_rec.created_by          := fnd_global.user_id;
            l_MID_rec.last_updated_by     := fnd_global.user_id;
            l_MID_rec.last_update_login   := fnd_global.user_id;
            l_MID_rec.creation_date       := SYSDATE;
            l_MID_rec.last_update_date    := SYSDATE;
            
            l_MID_rec.header_id           := p_new_header_id;
            l_MID_rec.line_id             := l_line_rec.line_id;
            SELECT CUX_HRPE_MID_T_S.nextval INTO l_MID_rec.MID_id FROM dual;
            
            l_MID_rec.LINE_NUM_L              := c_mid.LINE_NUM_L;
            l_MID_rec.LINE_NUMBER             := c_mid.LINE_NUMBER;
            l_MID_rec.OBJECT_VERSION_NUMBER   := c_mid.OBJECT_VERSION_NUMBER;
            l_MID_rec.MAX_LINE_NUMBER         := c_mid.MAX_LINE_NUMBER;
            l_MID_rec.WPM_KPI_SMALL           := c_mid.WPM_KPI_SMALL;
            l_MID_rec.WPM_MEASURE_FORMULA     := c_mid.WPM_MEASURE_FORMULA;
            l_MID_rec.STD_SCORE               := c_mid.STD_SCORE;
            l_MID_rec.HARD_RATIO              := c_mid.HARD_RATIO;
            
            INSERT INTO CUX_HRPE_mid_T VALUES l_MID_rec;
            --复制mend
             FOR c_mend IN cur_mend(c_mid.mid_id) LOOP
                --l_mend_rec   := c_mend;
                l_mend_rec := NULL;

                l_mend_rec.created_by          := fnd_global.user_id;
                l_mend_rec.last_updated_by     := fnd_global.user_id;
                l_mend_rec.last_update_login   := fnd_global.user_id;
                l_mend_rec.creation_date       := SYSDATE;
                l_mend_rec.last_update_date    := SYSDATE;
                
                l_mend_rec.header_id           := p_new_header_id;
                l_mend_rec.line_id             := l_line_rec.line_id;
                l_mend_rec.MID_id              := l_MID_rec.MID_id;
                SELECT CUX_HRPE_MEND_t_S.nextval INTO l_mend_rec.mend_id FROM dual;
                
                l_mend_rec.MEND_ORGANIZATION_ID        := c_mend.MEND_ORGANIZATION_ID;
                l_mend_rec.MEND_PERSON_ID         := c_mend.MEND_PERSON_ID;
                l_mend_rec.PEOPLE_COUNT   := c_mend.PEOPLE_COUNT;
                l_mend_rec.SCD_PERCENT := c_mend.SCD_PERCENT;
               INSERT INTO CUX_HRPE_MEND_T VALUES l_mend_rec;
         END LOOP; 
      END LOOP; 
 END LOOP;
    
    IF l_line_count = 0 THEN
      l_error_msg := '没找到评定行数据源,HEADER_ID ' || p_old_header_id;
      RAISE fnd_api.g_exc_error;
    END IF;
    
  EXCEPTION
    WHEN fnd_api.g_exc_error THEN
      x_return_code := fnd_api.g_ret_sts_error;
      x_return_msg  := l_error_msg;
    WHEN OTHERS THEN
      x_return_code := fnd_api.g_ret_sts_unexp_error;
      x_return_msg  := SQLERRM || dbms_utility.format_error_backtrace;

  END people_card_copy;

2.在form中的复制按钮调用

DECLARE
  l_return_code     VARCHAR2(1);
  l_return_msg      VARCHAR2(2000);
  l_new_header_id   NUMBER;
  l_question_result NUMBER;
  l_status     boolean;
  L_DEFAULT_WHERE VARCHAR2(2000);
  l_cur_item varchar2(200):=:system.cursor_item;
BEGIN

  fnd_message.set_string('确认复制？');
  l_question_result := fnd_message.question('是', '否', NULL, 1, 2, NULL);
  IF l_question_result = 1 THEN
    npfm_score_card_pkg.people_card_copy(:control.copy_from,
                                  :headers.header_id,
                                  l_return_code,
                                  l_return_msg);
  
    IF l_return_code = 'S' THEN
      fnd_message.set_string('复制成功!');
      fnd_message.show;
    ELSE
      fnd_message.set_string(l_return_msg);
      fnd_message.show;
      RAISE form_trigger_failure;
    END IF;
   
    
    :CONTROL.COPY_FROM:=NULL;
    APP_FORM_PKG.commit_form;
  END IF;
   
  go_block('HEADERS');
  --set_block_property('HEADERS', default_where, 'HEADER_ID=' || l_new_header_id);
  execute_query;
  GO_RECORD(:PARAMETER.G_CURRENT_CARD);

EXCEPTION
  WHEN OTHERS THEN
    RAISE form_trigger_failure;
END;

3.获取要复制的header_id
做个copy_form的item

做一个LOV

SELECT T.HEADER_ID,
       T.CARD_YEAR,
       T.CARD_PERIOD_MEANING,
       T.CARD_PERIOD,
       T.CARD_TYPE_MEANING,
       T.CARD_TYPE,
       T.COMP_NAME,
       T.ORGANIZATION_ID,
       T.DEP_NAME,
       T.DEPARTMENT_ID,
       T.STATUS_TYPE_MEANING,
       T.STATUS_TYPE
  FROM CUX_HRPE_HEADERS_T_V T
 WHERE T.STATUS_TYPE_MEANING = '已提交'
 

将 header_id返回到 CONTROL.COPY_FROM

OK 完毕