EBS惯用开发整理

EBS常用开发整理

1、通过code编号查询到值集的name名称
–取flex_value描述
FUNCTION get_flex_value_desc(p_flex_value_set_name IN VARCHAR2,–值集代码
p_flex_value IN VARCHAR2)
RETURN VARCHAR2 IS
v_flex_value_desc fnd_flex_values_vl.description%TYPE;
BEGIN
SELECT v.description
INTO v_flex_value_desc
FROM fnd_flex_values_vl v, fnd_flex_value_sets s
WHERE v.flex_value_set_id = s.flex_value_set_id
AND s.flex_value_set_name = p_flex_value_set_name
AND v.flex_value = p_flex_value;

RETURN v_flex_value_desc;

EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END get_flex_value_desc;

2、资产所属责任中心、机构等常用关联表
From
gl_code_combinations gcc,
fa_distribution_history fdh
where
AND gcc.code_combination_id=fdh.code_combination_id
AND (v_respon_from IS NULL OR TO_NUMBER(gcc.segment2) >= v_respon_from)
AND (v_respon_to IS NULL OR TO_NUMBER(gcc.segment2) <= v_respon_to)

3、格式化日期
v_date_from DATE := TO_DATE(p_date_from, ‘YYYY/MM/DD’);

4、涉及到资产的变动、报废、新增等操作的关联表
fa_transaction_headers
5、取累计折旧
function GET_DEPRN_RESERVE(P_ASSET_ID number, P_BOOK_TYPE_CODE varchar2)
return number is
DUMMY_NUM number;
DUMMY_CHAR varchar2(10);
DUMMY_BOOL boolean;
V_REVAL_RESERVE number;
V_YTD_DEPRN number;
V_YTD_PRODUCTION number;
V_LTD_PRODUCTION number;
V_BONUS_DEPRN_RESERVE number;
V_BONUS_YTD_DEPRN number;
V_DEPRN_RESERVE number;
V_BOOK_TYPE_CODE varchar2(30);
L_IMPAIRMENT_RSV number; –Bug#7293626
L_SET_OF_BOOKS_ID number := FND_PROFILE.VALUE(‘GL_SET_OF_BKS_ID’);
L_log_level_rec FA_API_TYPES.log_level_rec_type;
begin

--取资产当前的累计折旧
if (P_BOOK_TYPE_CODE is null) then
  select BOOK_TYPE_CODE
    into V_BOOK_TYPE_CODE
    from FA_BOOKS
   where ASSET_ID = P_ASSET_ID
     and DATE_INEFFECTIVE is null;
else
  V_BOOK_TYPE_CODE := P_BOOK_TYPE_CODE;
end if;

FA_QUERY_BALANCES_PKG.QUERY_BALANCES(
  X_ASSET_ID                => P_ASSET_ID,
  X_BOOK                    => V_BOOK_TYPE_CODE,
  X_PERIOD_CTR              => 0,
  X_DIST_ID                 => 0,
  X_RUN_MODE                => 'STANDARD',
  X_COST                    => DUMMY_NUM,
  X_DEPRN_RSV               => V_DEPRN_RESERVE,
  X_REVAL_RSV               => V_REVAL_RESERVE,
  X_YTD_DEPRN               => V_YTD_DEPRN,
  X_YTD_REVAL_EXP           => DUMMY_NUM,
  X_REVAL_DEPRN_EXP         => DUMMY_NUM,
  X_DEPRN_EXP               => DUMMY_NUM,
        X_REVAL_AMO                             => DUMMY_NUM,
        X_PROD                                      => DUMMY_NUM,
        X_YTD_PROD                              => V_YTD_PRODUCTION,
        X_LTD_PROD                              => V_LTD_PRODUCTION,
        X_ADJ_COST                              => DUMMY_NUM,
        X_REVAL_AMO_BASIS               => DUMMY_NUM,
        X_BONUS_RATE                            => DUMMY_NUM,
        X_DEPRN_SOURCE_CODE             => DUMMY_CHAR,
        X_ADJUSTED_FLAG                     => DUMMY_BOOL,
        X_TRANSACTION_HEADER_ID     => -1,
        X_BONUS_DEPRN_RSV               => V_BONUS_DEPRN_RESERVE,
        X_BONUS_YTD_DEPRN               => V_BONUS_YTD_DEPRN,
        X_BONUS_DEPRN_AMOUNT            => DUMMY_NUM,
        X_IMPAIRMENT_RSV                    => DUMMY_NUM,
        X_YTD_IMPAIRMENT                    => DUMMY_NUM,
        X_IMPAIRMENT_AMOUNT             => DUMMY_NUM,
        X_CAPITAL_ADJUSTMENT            => DUMMY_NUM,
        X_GENERAL_FUND                      => DUMMY_NUM,
        X_MRC_SOB_TYPE_CODE             => null,
        X_SET_OF_BOOKS_ID               => L_SET_OF_BOOKS_ID,
        p_log_level_rec                     => L_log_level_rec);

    return (V_DEPRN_RESERVE);
exception
    when others then
        return (null);
end GET_DEPRN_RESERVE;

6、获取资产小类名称，与资产大类关联
FUNCTION cux_get_flex_desc2(v_flex_set VARCHAR2,
v_flex_value VARCHAR2,
v_flex_value2 VARCHAR2) RETURN VARCHAR2 IS
v_description VARCHAR2(240);
BEGIN
SELECT ffvv.description
INTO v_description
FROM fnd_flex_value_sets ffvs, fnd_flex_values_vl ffvv
WHERE ffvs.flex_value_set_name = v_flex_set
AND ffvv.flex_value = v_flex_value
AND ffvv.flex_value_set_id = ffvs.flex_value_set_id
– AND FFVV.PARENT_FLEX_VALUE_LOW = v_flex_value2
AND FFVV.ENABLED_FLAG = ‘Y’;
RETURN v_description;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END cux_get_flex_desc2;

–调用 cux_get_flex_desc2(‘FA_MIN’, fc.segment2, fc.segment1) cate_small_desc

6、设置有上下文关联关系的值集
where (ASSET_CATEGORY_SEGMENT1>=: $FLEX$ .CUX_FA_CATEGORY_BIG_FROM)

7、生成随机自动编号

SELECT cux_order_headers_s.NEXTVAL INTO x_header_id FROM dual;

8、报表的参数设置
CONCURRENT_PROGRAM_NAME=”CUXFACATSUM” PROGRAM_APPL_SHORT_NAME=”CUX” SUBMIT_ONCE=”Y”

9、用于无提示框提交
PROCEDURE DO_COMMIT IS
OLD_LEVEL VARCHAR2(2);
BEGIN
OLD_LEVEL:=NAME_IN(‘SYSTEM.MESSAGE_LEVEL’);
COPY(‘5’,’SYSTEM.MESSAGE_LEVEL’);
COMMIT;
COPY(OLD_LEVEL,’SYSTEM.MESSAGE_LEVEL’);
END DO_COMMIT;

10、 Forms做完增删改后，需要加入commit;才会执行到数据库中。
11、 Form-LOV 相互之间有引用相互关系的
SELECT Fvv.Flex_Value VALUE_CODE,fvv.DESCRIPTION
FROM Fnd_Flex_Value_Sets Fvs,
Fnd_Flex_Values_Vl Fvv,
cux_fa_bill_lookup_t cfb
WHERE Fvs.Flex_Value_Set_Name = cfb.lookup_code
AND Fvs.Flex_Value_Set_Id = Fvv.Flex_Value_Set_Id
and cfb.lookup_type = ‘CATEGORY_FLEXFIELD’
and cfb.lookup_name = ‘SEGMENT1’
and cfb.is_enable = ‘Y’
AND Fvv.Enabled_Flag = ‘Y’
AND FVV.FLEX_VALUE like nvl(substr(:QUERY_FIND.ASSET_CATEGORY_SEGMENT2,1,3), ‘%’)

12、 Pl/sql中输出异常语句
exception
when others then
insert into testinfo values(dbms_utility.format_error_backtrace);
commit;

或者
dbms_output.put_line(l_sql); –测试的时候可以输出

13、 PL/SQL测试的时候如果需要初始化参数
在PL、SQL的程序入口处begin敲入如下代码：
FND_GLOBAL.APPS_INITIALIZE(user_id =>1130 ,resp_id =>50724 ,resp_appl_id =>20003);
里面参数具体值，可以在form界面的诊断里查询出。
14、根据职责配置功能权限控制
（1）新建职责，在应用产品——功能，新建职责功能
（2）form中编写代码验证职责，编写更删改控制语句
IF FND_FUNCTION.TEST(‘CUX_FA_QUERY’) THEN
SET_BLOCK_PROPERTY(‘BILL_HEAD’,INSERT_ALLOWED,PROPERTY_TRUE);

15、 FORM 之间的调用
APP_NAVIGATE.EXECUTE 或FND_FUNCTION.EXECUTE
例：APP_NAVIGATE.EXECUTE(‘HEK_DISCOUNT_PERIOD_NEW’, ‘Y’, ‘Y’, null);
其中APP_NAVIGATE.EXECUTE 与FND_FUNCTION.EXECUTE 的区别：
APP_NAVIGATE.EXECUTE 只打一个FORM，而FND_FUNCTION.EXECUTE 调用多少次，就打开多少
个。

16、遍历数据块
go_block(‘BLOCKNAME’);
first_record;
IF :SYSTEM.block_status <> ‘NEW ’ THEN
Loop
if :system.last_record = ‘TRUE’ then
exit;
else
next_record;
end if;
end loop;
end if;

17、
Fnd_Profile 的常用方法
begin
fnd_message.debug(‘user_id= ’ || fnd_profile.value(‘user_id’)); –取当前登录EBS 用户ID
fnd_message.debug(‘user_name= ‘||fnd_profile.value(‘USERNAME’)); –取当前登录EBS 用户名
fnd_message.debug(‘FND_Global.User_Name=’|| FND_Global.User_Name); –取当前登录EBS 用户名
fnd_message.set_string(‘GL_SET_OF_BKS_ID=’||fnd_profile.value(‘GL_SET_OF_BKS_ID’));
fnd_message.show; –取当前帐套
end;
18、

EBS惯用开发整理

相关推荐