按照部门名称得到报销单（费用）

纵横四海 · 发表于 2006/5/15 07:59:04

马上注册，结交更多好友，享用更多功能，让你轻松玩转社区。如果您注册时有任何问题请联系客服QQ： 83569622 　。

您需要登录才可以下载或查看，没有帐号？注册

x

按照部门名称得到报销单

select e.name as department_name,
   c.last_name as employee_name,
   --d.PERSON_ID,
   --a.employee_id,
   a.invoice_num as expense_number,
   b.ITEM_DESCRIPTION as expense_type,
   b.amount,
   b.ATTRIBUTE1,
   b.ATTRIBUTE2,
   b.ATTRIBUTE3,
   b.ATTRIBUTE4,
   b.JUSTIFICATION
  from ap_expense_report_headers_all a,
   ap_expense_report_lines_all b,
   hr_employees                c,
   per_assignments_f          d,
   hr_organization_units       e
where a.REPORT_HEADER_ID = b.REPORT_HEADER_ID
and c.employee_id = d.PERSON_ID
and a.employee_id = c.employee_id
and d.ORGANIZATION_ID = e.organization_id
and e.name = '部门名称
and a.CREATION_DATE > to_date('20050715', 'YYYYMMDD')
and (sysdate between d.EFFECTIVE_START_DATE and d.EFFECTIVE_END_DATE)
order by c.last_name, a.invoice_num;

select B.ROWID,
   e.name as department_name,
   c.last_name as employee_name,
   --d.PERSON_ID,
   --a.employee_id,
   to_char(b.creation_date, 'YYYYMMDD'),
   a.invoice_num as expense_number,
   b.ITEM_DESCRIPTION as expense_type,
   b.amount,
   b.ATTRIBUTE1,
   b.ATTRIBUTE2,
   b.ATTRIBUTE3,
   b.ATTRIBUTE4,
   b.JUSTIFICATION
  from ap_expense_report_headers_all a,
   ap_expense_report_lines_all b,
   hr_employees                c,
   per_assignments_f          d,
   hr_organization_units       e
where a.REPORT_HEADER_ID = b.REPORT_HEADER_ID
and c.employee_id = d.PERSON_ID
and a.employee_id = c.employee_id
and d.ORGANIZATION_ID = e.organization_id
and e.name in ('电子商务及搜索应用部', '平台工程组')
and a.CREATION_DATE >= to_date('20060101', 'YYYYMMDD')
and (sysdate between d.EFFECTIVE_START_DATE and d.EFFECTIVE_END_DATE)
--and 'W44057' = a.invoice_num
order by e.name, c.last_name, a.invoice_num;

纵横四海 · 发表于 2007/5/24 12:16:57

select B.ROWID,
e.name as department_name,
c.last_name as employee_name,
--d.PERSON_ID,
--a.employee_id,
to_char(b.creation_date, 'YYYYMMDD'),
a.invoice_num as expense_number,
b.ITEM_DESCRIPTION as expense_type,
b.amount,
b.ATTRIBUTE1,
b.ATTRIBUTE2,
b.ATTRIBUTE3,
b.ATTRIBUTE4,
a.description,
b.JUSTIFICATION
from ap_expense_report_headers_all a,
ap_expense_report_lines_all b,
hr_employees c,
per_assignments_f d,
hr_organization_units e
where a.REPORT_HEADER_ID = b.REPORT_HEADER_ID
and c.employee_id = d.PERSON_ID
and a.employee_id = c.employee_id
and d.ORGANIZATION_ID = e.organization_id
--and e.name in ('µç×ÓÉÌÎñ¼°ËÑË÷Ó¦ÓÃ²¿', 'Æ½Ì¨¹¤³Ì×é')
and (a.description like '%%' or
b.JUSTIFICATION like '%%')
--and a.CREATION_DATE >= to_date('20060101', 'YYYYMMDD')
--and (sysdate between d.EFFECTIVE_START_DATE and d.EFFECTIVE_END_DATE)
--and 'W44057' = a.invoice_num
order by e.name, c.last_name, a.invoice_num;

复制代码

纵横四海 · 发表于 2007/7/15 18:59:13

2007年7月15日更新
在此前的SQl基础上增加了对总账日期的判断。

select e.name as 部门名称,
c.last_name as 员工,
--d.PERSON_ID,
--a.employee_id,
f.gl_date　总账日期,
a.invoice_num as 报销单号,
b.ITEM_DESCRIPTION as 费用类型,
b.code_combination_id,
g.segment3 科目段,
b.amount　行金额,
b.ATTRIBUTE1,
b.ATTRIBUTE2,
b.ATTRIBUTE3,
b.ATTRIBUTE4,
b.JUSTIFICATION
from ap_expense_report_headers_all a,
ap_expense_report_lines_all b,
hr_employees c,
per_assignments_f d,
hr_organization_units e,
ap_invoices_all f,
gl_code_combinations g
where a.REPORT_HEADER_ID = b.REPORT_HEADER_ID
and c.employee_id = d.PERSON_ID
and a.employee_id = c.employee_id
and d.ORGANIZATION_ID = e.organization_id
and e.name = '－－'
and a.invoice_num = f.invoice_num
and f.gl_date between to_date('20070401', 'YYYYMMDD') and
to_date('20070701', 'YYYYMMDD')
and g.code_combination_id = b.code_combination_id
--and a.CREATION_DATE > to_date('20070401', 'YYYYMMDD')
--and a.CREATION_DATE > to_date('20070401', 'YYYYMMDD')
and (sysdate between d.EFFECTIVE_START_DATE and d.EFFECTIVE_END_DATE)
order by c.last_name, a.invoice_num;

复制代码

帐号		自动登录	找回密码
密码			注册

社区人物壹佰网重点话题	分享知识原创连载活动正式启动已有673人参加查看全部会员靓照旅行分享文学连载	壹佰读书会开始招收成员了
招聘求职壹佰网信息化选型平台	倾听用户的心声纵横四海谈IT如何提升企业管理管理小说连载《苏州桥》	如何获取努力值
原创连载－我来管管看 ERP分享+	推荐连载：[已结帖][分享知识原创连载]财务供应链项目新手实施手记：记录自己正	入门学习-致所有初学者

按照部门名称得到报销单（费用）

马上注册，结交更多好友，享用更多功能，让你轻松玩转社区。如果您注册时有任何问题请联系客服QQ： 83569622 。

浏览过的版块

马上注册，结交更多好友，享用更多功能，让你轻松玩转社区。如果您注册时有任何问题请联系客服QQ： 83569622 　。