srw的总结

１．在报表触发器BeforeReport中调用用户出口函数 FND SRWINIT 来初始化环境：取得并发程序相关信息和预制文件信息

Parameter:
------------
P_SET_OF_BOOKS_ID='1001'
P_CHART_OF_ACCOUNTS_ID='50268'
P_REPORT_TYPE='B'
P_KIND='L'
P_CURRENCY_CODE='HKD'
P_ACTUAL_FLAG='A'
P_BUD_ENC_TYPE_ID='-100'
P_START_PERIOD='Adj-12'
P_END_PERIOD='Adj-12'
P_MIN_FLEX='22-133208-00-00-0000-000'
P_MAX_FLEX='22-133211-00-00-0000-000'
P_ORDER_TYPE='S'
P_PAGE_SIZE='180'
------------

 

 

function BeforeReport return boolean is
begin
SRW.USER_EXIT('FND SRWINIT');

--P_COA_ID =  50268
srw.reference( :P_COA_ID );------------帐套，必须在report和ebs里都定义这个参数不然会报错SRW.REFERENCE 将引用的对象添加到构造依赖列表中，让Report在触发初始化构造的时候对值进行测定
srw.user_exit('FND FLEXSQL --------------------在报表中调用 FND FLEXIDVAL来动态设置显示的弹性域数据
                      CODE = "GL#" ------‘GL#’是帐户关键性弹性域的代码;code:弹性域编码
                      NUM=":P_COA_ID" ---------包括弹性域结构ID的词义变量
                      APPL_SHORT_NAME = "SQLGL"-----弹性域对应的应用简称
                      OUTPUT=":ACC_SELECT_ALL" -------------output:用来存贮SQL语句的词义变量(Placeholder)
                      TABLEALIAS="CC")--------如果使用表别名，制定弹性域对应表的别名
                      MODE="SELECT" "----SELECT 按照内部的格式取出所有段值
                      DISPLAY="ALL"');---指定有多少段是需要显示的，默认值为 ALL；如果只希望显示其中的一段，可以通过指定段名或者段号码来实现


-- GL_CODE_COMBINATIONS  --get values
SELECT 
         APPLICATION_COLUMN_NAME,
         SEGMENT_NUM
    FROM FND_ID_FLEX_SEGMENTS_VL
   WHERE     (ID_FLEX_NUM = 50268) -- P_COA_ID = 50268
         AND (ID_FLEX_CODE = 'GL#') -- CODE = "GL#"  
         AND (APPLICATION_ID = 101) -- APPL_SHORT_NAME = SQLGL
ORDER BY application_id,
         id_flex_code,
         id_flex_num,
         DECODE (enabled_flag,  'Y', 1,  'N', 2),
         segment_num

MSG-00100: 

CC.SEGMENT1||'
'||CC.SEGMENT2||'
'||CC.SEGMENT3||'
'||CC.SEGMENT4||'
'||CC.SEGMENT5||'
'||CC.SEGMENT6

--****************************************

srw.reference(:P_COA_ID);
srw.user_exit('FND FLEXSQL 
                      CODE="GL#" 
                      NUM=":P_COA_ID"
                      APPL_SHORT_NAME="SQLGL"
                      OUTPUT=":WHERE_FLEX" 
                      TABLEALIAS="CC"
                      MODE="WHERE" 
                      DISPLAY="ALL"
                      OPERATOR="BETWEEN" 
                      OPERAND1=":P_MIN_FLEX"
                      OPERAND2=":P_MAX_FLEX"');     


MSG-00200: CC.SEGMENT1 = '22' AND CC.SEGMENT2 BETWEEN '133208' AND '133211' AND CC.SEGMENT3 = '00' AND CC.SEGMENT4 = '00' AND CC.SEGMENT5 = '0000' AND CC.SEGMENT6 = '000'

--****************************************

srw.reference(:P_COA_ID);
srw.user_exit('FND FLEXSQL 
                     CODE="GL#" 
                     NUM=":P_COA_ID"
                     APPL_SHORT_NAME="SQLGL"
                     OUTPUT=":ORDERBY_ACCT" 
                     TABLEALIAS="CC"
                     MODE="ORDER BY" 
                     DISPLAY="GL_ACCOUNT"  -- ACCOUNT:  SEGMENT2
                    SHOWDEPSEG="N"'); 

MSG-00300: CC.SEGMENT2

 --****************************************

srw.reference(:P_COA_ID);
srw.user_exit('FND FLEXSQL 
                     CODE="GL#" 
                     NUM=":P_COA_ID"
                     APPL_SHORT_NAME="SQLGL"
                     OUTPUT=":ORDERBY_BAL" 
                     TABLEALIAS="CC"
                     MODE="ORDER BY" 
                     DISPLAY="GL_BALANCING"  -- BALANCING:  SEGMENT1
                     SHOWDEPSEG="N"');

 MSG-00500: CC.SEGMENT1

return (TRUE);
end;

２．在报表触发器AfterReport中调用用户出口函数 FND SRWEXIT 来释放用户出口函数中使用的内存,USER_EXIT是一个用户出口函数，用于取得帐户段描

function AfterReport return boolean is
begin
     SRW.USER_EXIT('FND SRWEXIT');
     return (TRUE);
end;

 

3.在字段内

设置

function ACC_flex_dataFormula return VARCHAR2 is
begin
   srw.reference(:ACC_flex);
   srw.reference( :P_COA_ID );
   srw.user_exit('FND FLEXIDVAL
                  CODE="GL#"
                  NUM=":P_COA_ID"
                  APPL_SHORT_NAME="SQLGL"
                  DATA=":ACC_FLEX"
                  VALUE=":ACC_FLEX_DATA"
                  DISPLAY="ALL"');
  return(:ACC_FLEX_DATA);

  --22-133211-00-00-0000-000
end;


function ACC_FLEX_DESCFormula return Char is
begin
   srw.reference(:ACC_flex);
   srw.reference( :P_COA_ID );
   srw.user_exit('FND FLEXIDVAL
                  CODE="GL#"
                  NUM=":P_COA_ID"
                  APPL_SHORT_NAME="SQLGL"
                  DATA=":ACC_FLEX"
                  FULL_DESCRIPTION=":ACC_FLEX_DESC"
                  DISPLAY="ALL"');

   return(REPLACE(REPLACE(REPLACE(:ACC_flex_desc,'南京本部'),'缺省'),'无'));
end;

４．做一个cp acc_select_all

value is null 为

(c.segment1||'n'||c.segment2||'n'||

c.segment3||'n'||c.segment4||'n'||c.segment5||'n'||

c.segment6||'n'||c.segment7)

具体按要选择的段落分类

５.在报表的SqL里写

select
&acc_select_all acc_flex,


srw.reference(:FLEXDATA);
srw.reference(:STRUCT_NUM);
srw.user_exit('FND FLEXIDVAL CODE="GL#"
               NUM=":STRUCT_NUM"
               APPL_SHORT_NAME="SQLGL"
               DATA=":FLEXDATA"
               VALUE=":FLEXFIELD"
               PADDED_VALUE=":FLEXFIELD_PAD"
               DESCRIPTION=":FLEXDESC"
               DISPLAY="ALL"
               IDISPLAY="ALL"');

RETURN(:FLEXFIELD);

增加placeholder (占位符) SELECT_ALL

(CC.SEGMENT1 || 'n' || SEGMENT2 || 'n' || SEGMENT3 || 'n' || SEGMENT4 || 'n' || SEGMENT5 || 'n' || SEGMENT6 || 'n' || SEGMENT7 || 'n' || SEGMENT8 || 'n' || SEGMENT9 || 'n' || SEGMENT10 || 'n' || SEGMENT11 || 'n' || SEGMENT12 || 'n' || SEGMENT13 || 'n' || SEGMENT14 || 'n' || SEGMENT15 || 'n' || SEGMENT16 || 'n' || SEGMENT17 || 'n' || SEGMENT18 || 'n' || SEGMENT19 || 'n' || SEGMENT20 || 'n' || SEGMENT21 || 'n' || SEGMENT22 || 'n' || SEGMENT23 || 'n' || SEGMENT24 || 'n' || SEGMENT25 || 'n' || SEGMENT26 || 'n' || SEGMENT27 || 'n' || SEGMENT28 || 'n' || SEGMENT29 || 'n' || SEGMENT30)

其中 FLEXDATA 是查询的各段的组合,就是SELECT_ALL

STRUCT_NUM 是COAID

FLEXDESC 是科目的描述

FLEXFIELD_PAD ???