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 完毕