FAM系统总帐部分数据字典

1、FAM系统部分数据字典一、 测试环境：本次测试在FAM系统的TEST环境进行测试。TEST环境配置如下：TEST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 9)(PORT = 1522) ) (CONNECT_DATA = (SERVICE_NAME = test2) )二、 总帐模块：1、 维度：帐套、组织、期间、币种 + 会计弹性域段值会计弹性域段值：电厂10段，物资公司 7段SELECT se.id_flex_num, s.flex_value_set_name, se.

2、flex_value_set_id, s.description, se.application_column_name, se.segment_name, s.*, se.*FROM fnd_id_flex_segments se, fnd_flex_value_sets sWHERE s.flex_value_set_id = se.flex_value_set_id and se.id_flex_num in( 50260,50259) order by se.id_flex_num,se.segment_num-公司段 SEGMENT1SELECT v.flex_value,v.des

3、criptionFROM fnd_flex_value_sets s,fnd_flex_values_vl vWHERE s.flex_value_set_name = HDPI_COMPANYAND s.flex_value_set_id = v.flex_value_set_idAND v.summary_flag = N ORDER BY v.flex_value-青岛发电厂成本中心 SEGMENT2SELECT v.flex_value,v.descriptionFROM fnd_flex_value_sets s,fnd_flex_values_vl vWHERE s.flex_va

4、lue_set_name = HDPI_QD_DC_COST_CENTERAND s.flex_value_set_id = v.flex_value_set_idAND v.summary_flag = N ORDER BY v.flex_value-会计科目 SEGMENT3SELECT v.flex_value,v.descriptionFROM fnd_flex_value_sets s,fnd_flex_values_vl vWHERE s.flex_value_set_name = HDPI_ACCOUNTINGAND s.flex_value_set_id = v.flex_va

5、lue_set_idAND v.summary_flag = N ORDER BY v.flex_value-青岛发电厂子目段 4SELECT v.flex_value,v.descriptionFROM fnd_flex_value_sets s,fnd_flex_values_vl vWHERE s.flex_value_set_name = HDPI_QD_DC_SUBACCOUNTINGAND s.flex_value_set_id = v.flex_value_set_idAND v.summary_flag = N ORDER BY v.flex_value-投资期值 SEGMEN

6、T6SELECT v.flex_value,v.descriptionFROM fnd_flex_value_sets s,fnd_flex_values_vl vWHERE s.flex_value_set_name = HDPI_PERIODAND s.flex_value_set_id = v.flex_value_set_idAND v.summary_flag = N ORDER BY v.flex_value-机组 SEGMENT7SELECT v.flex_value,v.descriptionFROM fnd_flex_value_sets s,fnd_flex_values_

7、vl vWHERE s.flex_value_set_name = HDPI_SETAND s.flex_value_set_id = v.flex_value_set_idAND v.summary_flag = N ORDER BY v.flex_value-产品 SEGMENT8SELECT v.flex_value,v.descriptionFROM fnd_flex_value_sets s,fnd_flex_values_vl vWHERE s.flex_value_set_name = HDPI_PRODUCTAND s.flex_value_set_id = v.flex_va

8、lue_set_idAND v.summary_flag = N ORDER BY v.flex_value-青岛发电厂项目值集 SEGMENT9SELECT v.flex_value,v.descriptionFROM fnd_flex_value_sets s,fnd_flex_values_vl vWHERE s.flex_value_set_name = HDPI_QD_DC_PROJECTAND s.flex_value_set_id = v.flex_value_set_idAND v.summary_flag = N ORDER BY v.flex_value-华电国际备用值集

9、SEGMENT10SELECT v.flex_value,v.descriptionFROM fnd_flex_value_sets s,fnd_flex_values_vl vWHERE s.flex_value_set_name = HDPI_RESERVEAND s.flex_value_set_id = v.flex_value_set_idAND v.summary_flag = N ORDER BY v.flex_value2、 计算值：年初余额、期末余额、本期发生数、本月发生数、上年同期数3、 主要关键表： n GL_SETS_OF_BOOKS 帐套:字段名称中文名称类型说明及举

10、例SET_OF_BOOKS_ID 帐套IDNUMBER(15)NAME帐套名称VARCHAR2(30)SHORT_NAME帐套简称VARCHAR2(20)DESCRIPTION描述VARCHAR2(240)CHART_OF_ACCOUNTS_ID会计弹性域IDNUMBER(15)CURRENCY_CODE币种VARCHAR2(15)select sob.chart_of_accounts_id, - 青岛发电厂会计弹性域 id sob.set_of_books_id, , sob.description, sob.*from gl_sets_of_books sobwhere

11、sob.set_of_books_id = 9 - 青岛发电厂会计帐套 n GL_PERIOD_STATUSES 会计期间:字段名称中文名称类型说明及举例APPLICATION_ID 应用IDNUMBER(15)SET_OF_BOOKS_ID帐套IDNUMBER(15)PERIOD_NAME会计期间VARCHAR2(15)CLOSING_STATUS状态VARCHAR2(1)START_DATE开始时间DATEEND_DATE结束时间DATEPERIOD_YEAR年NUMBER(15)PERIOD_NUM月NUMBER(15)ADJUSTMENT_PERIOD_FLAG是否调整期间VARCHA

12、R2(1)select gp.period_name ,gp.start_date ,gp.end_date ,gp.*from gl_period_statuses gpwhere gp.set_of_books_id = 9 - 青岛发电厂会计帐套 and gp.application_id = 101 - 总帐order by gp.period_namen fnd_id_flex_segments se, fnd_flex_value_sets s 会计弹性域:字段名称中文名称类型说明及举例se.flex_value_set_id值集IDs.flex_value_set_name值集名

13、称s.description值集描述se.application_column_name应用段se.segment_name段值名称SELECT se.flex_value_set_id, s.flex_value_set_name, s.description, se.application_column_name, se.segment_name, se.*FROM fnd_id_flex_segments se, fnd_flex_value_sets sWHERE s.flex_value_set_id = se.flex_value_set_id and se.id_flex_num

14、 = 50259 - 青岛发电厂会计弹性域IDorder by se.segment_numn gl_balances 余额表n gl_code_combinations 帐户表n gl_je_headers 日记帐头n gl_je_lines 日记帐行select gb.period_name 期间, SUM(nvl(gb.begin_balance_dr,0) - nvl(gb.begin_balance_cr,0) 期初余额, SUM(nvl(gb.period_net_dr,0) - nvl(gb.period_net_cr,0) 本期(发生)数PTD, SUM(nvl(gb.peri

15、od_net_dr,0) - nvl(gb.period_net_cr,0) +nvl(gb.begin_balance_dr,0) - nvl(gb.begin_balance_cr,0) 期末余额YTDfrom gl_balances gb, gl_code_combinations gcwhere gb.set_of_books_id = 1-and gb.period_name BETWEEN 2005-01 AND 2005-08-注意 -and gb.period_year = 2005-and gb.period_num = 8and gb.actual_flag = Aand

16、gb.currency_code = CNYand gb.template_id is nulland gb.code_combination_id = gc.code_combination_idand gc.segment3 = 113101GROUP BY gb.period_nameORDER BY gb.period_name三、 项目模块1、 维度：组织、日期、支出类型2、 计算值：年初余额、期末余额、本期发生数、本月发生数、上年同期数select nvl(sum(pe.burden_cost),0) from PA_EXPEND_ITEMS_ADJUST2_V pe, PA_COST_DISTRIBUTION_LINES_ALL pcdl where pe.expenditure_item_id=pcdl.expenditure_item_id