http://www.erp100.com/forum.php?mod=viewthread&tid=80&extra=&authorid=8&page=1
查看关键用户
select distinct login_name
from (select c.user_name as login_name,
d.full_name as employee_name,
f.name as department_name,
a.user_id as user_id,
a.responsibility_id as responsibility_id,
b.RESPONSIBILITY_NAME as RESPONSIBILITY_NAME
from FND_USER_RESP_GROUPS a,
FND_RESPONSIBILITY_VL b,
fnd_user c,
hr_employees d,
per_assignments_f e,
hr_all_organization_units_tl f
where a.user_id = c.user_id
and c.employee_id = d.employee_id
and c.employee_id = e.PERSON_ID
and e.ORGANIZATION_ID = f.organization_id
and a.responsibility_id = b.RESPONSIBILITY_ID
and sysdate > e.EFFECTIVE_START_DATE
and sysdate < e.EFFECTIVE_END_DATE
and b.RESPONSIBILITY_NAME not in
('员工自助 (1)')
order by c.description, c.user_name, a.responsibility_id) a
;
得到员工的部门和成本中心
select a.full_name as employee_name,
c.name as hr_depart,
cux_hr11_report.getDepartmentNameByName(a.full_name) as account_name
from (select *
from apps.PER_PEOPLE_f a
where a.PERSON_TYPE_ID = 6
and a.LAST_NAME not like '虚拟%'
and sysdate > a.EFFECTIVE_START_DATE
and sysdate < a.EFFECTIVE_END_DATE) a,
per_assignments_f b,
hr_all_organization_units_tl c
where a.PERSON_ID = b.ASSIGNMENT_ID
and b.ORGANIZATION_ID = c.organization_id
and c.language = 'ZHS'
and sysdate > b.EFFECTIVE_START_DATE
and sysdate < b.EFFECTIVE_END_DATE
order by c.name
导出AR退款的SQL
begin
fnd_client_info.set_org_context(85);
end;select tt.customer_id,
tt.customer_name,
tt.customer_number,
tt.gl_date as shoukuan_date,
t.GL_DATE as tuikuan_date,
t.AMOUNT_APPLIED,
t.TRX_NUMBER,
tt.receipt_number
from AR_RECEIVABLE_APPLICATIONS_V t, AR_CASH_RECEIPTS_V tt
where t.TRX_NUMBER = 'Receipt Write-off'
and t.CASH_RECEIPT_ID = tt.cash_receipt_id
and t.GL_DATE > to_date('20050101', 'YYYYMMDD')
and t.GL_DATE < to_date('20050331', 'YYYYMMDD');
基于gl_balances 的部门费用SQL
这是一个统计某个会计科目在某个会计期内的费用合计,在SQL中有
and b.segment2 = p_department_id
and b.segment3 = p_account_id
这即是假设你的segment2 为部门段,segment3为会计科目
budget_version_id为预算的ID,可以用select * from gl.gl_budgets t确认相应的预算id
set_of_books_id 的值可以用 select * from gl_sets_of_books确认
该SQL实际证明是完全可靠和可信赖的,我们的很多取值都用这个SQL。
select sum(a.period_net_dr - a.period_net_cr)
from apps.gl_balances a, apps.gl_code_combinations b
where b.enabled_flag = 'Y'
and a.set_of_books_id = 1
and a.code_combination_id = b.code_combination_id
and nvl(a.budget_version_id, 1022) = 1022
AND (upper(a.ACTUAL_FLAG) = upper(p_money_type))
AND trim(nvl(a.PERIOD_NAME, 'XXX')) =
trim(to_char(to_date(2005|| '-' || 1, 'YYYY-MM'),
'MON-YY',
'NLS_DATE_LANGUAGE=American'))
and b.segment2 = p_department_id
and b.segment3 = p_account_id
有查询User具体权限的SQL吗?
这就是
select c.user_name as login_name,
d.full_name as employee_name,
f.name as department_name,
a.user_id as user_id,
a.responsibility_id as responsibility_id,
b.RESPONSIBILITY_NAME as RESPONSIBILITY_NAME
from FND_USER_RESP_GROUPS a,
FND_RESPONSIBILITY_VL b,
fnd_user c,
hr_employees d,
per_assignments_f e,
hr_all_organization_units_tl f
where a.user_id = c.user_id
and c.employee_id = d.employee_id
and c.employee_id = e.PERSON_ID
and e.ORGANIZATION_ID = f.organization_id
and a.responsibility_id = b.RESPONSIBILITY_ID
and sysdate > e.EFFECTIVE_START_DATE
and sysdate < e.EFFECTIVE_END_DATE
order by c.description, c.user_name, a.responsibility_id
;
关联一下fnd_application 表就可以了select c.user_name as login_name,
d.full_name as employee_name,
f.name as department_name,
a.user_id as user_id,
a.responsibility_id as responsibility_id,
b.RESPONSIBILITY_NAME as RESPONSIBILITY_NAME,
g.application_short_name
from FND_USER_RESP_GROUPS a,
FND_RESPONSIBILITY_VL b,
fnd_user c,
hr_employees d,
per_assignments_f e,
hr_all_organization_units_tl f,
fnd_application g
where a.user_id = c.user_id
and c.employee_id = d.employee_id
and c.employee_id = e.PERSON_ID
and e.ORGANIZATION_ID = f.organization_id
and a.responsibility_id = b.RESPONSIBILITY_ID
and sysdate > e.EFFECTIVE_START_DATE
and sysdate < e.EFFECTIVE_END_DATE
and b.APPLICATION_ID = g.application_id
order by c.description, c.user_name, a.responsibility_id;
按照天,列出发票总额
select a.trx_date,
sum(b.unit_selling_price *
NVL(b.QUANTITY_CREDITED, b.QUANTITY_INVOICED)) as amount
from ra_customer_trx_all a, ra_customer_trx_lines_all b
where a.customer_trx_id = b.customer_trx_id
and a.trx_date >= to_date('20050101', 'YYYYMMDD')
and a.trx_date < to_date('20050701', 'YYYYMMDD')
and a.org_id = 85
group by a.trx_date
/*120*240,创建于2011-6-8*/ var cpro_id = 'u501098';-员工姓名及其部门信息表:select a.LAST_NAME,
a.PERSON_ID,
a.SEX,
a.EMAIL_ADDRESS,
b.ORGANIZATION_ID,
c.name
from per_people_f a, PER_ASSIGNMENTS_F b, HR_ALL_ORGANIZATION_UNITS c
where a.PERSON_ID = b.person_id
and sysdate between b.EFFECTIVE_START_DATE and b.EFFECTIVE_END_DATE
and a.PERSON_TYPE_ID = 6
and b.ORGANIZATION_ID = c.organization_id
按照部门名称得到报销单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;
得到公司组织架构的SQL:
create or replace view cux_org_level1 as
select a_pa.organization_id as org_id1, a_pa.name as org_name1, a_ch.organization_id as org_id2, a_ch.name as org_name2
from per_org_structure_elements t,
hr_organization_units a_pa,
hr_organization_units a_ch
where t.organization_id_parent = 0
and a_pa.organization_id = t.organization_id_parent
and a_ch.organization_id = t.organization_id_child;
create view cux_org_level2 as
select a_pa.organization_id as org_id1, a_pa.name as org_name1, a_ch.organization_id as org_id2, a_ch.name as org_name2
from per_org_structure_elements t,
hr_organization_units a_pa,
hr_organization_units a_ch
where t.organization_id_parent in
(select t.organization_id_child
from per_org_structure_elements t
where t.organization_id_parent = 0)
and a_pa.organization_id = t.organization_id_parent
and a_ch.organization_id = t.organization_id_child;
create view cux_org_level3 as
select a_pa.organization_id as org_id1,
a_pa.name as org_name1,
a_ch.organization_id as org_id2,
a_ch.name as org_name2
from per_org_structure_elements t,
hr_organization_units a_pa,
hr_organization_units a_ch
where t.organization_id_parent in
(select t.organization_id_child
from per_org_structure_elements t
where t.organization_id_parent in
(select t.organization_id_child
from per_org_structure_elements t
where t.organization_id_parent = 0))
and a_pa.organization_id = t.organization_id_parent
and a_ch.organization_id = t.organization_id_child;
create view cux_org_level4 as
select a_pa.organization_id as org_id1,
a_pa.name as org_name1,
a_ch.organization_id as org_id2,
a_ch.name as org_name2
from per_org_structure_elements t,
hr_organization_units a_pa,
hr_organization_units a_ch
where t.organization_id_parent in
(select t.organization_id_child
from per_org_structure_elements t
where t.organization_id_parent in
(select t.organization_id_child
from per_org_structure_elements t
where t.organization_id_parent in
(select t.organization_id_child
from per_org_structure_elements t
where t.organization_id_parent = 0)))
and a_pa.organization_id = t.organization_id_parent
and a_ch.organization_id = t.organization_id_child;
select t.organization_id_parent, t.organization_id_child
from per_org_structure_elements t
where t.organization_id_parent in
(select t.organization_id_child
from per_org_structure_elements t
where t.organization_id_parent = 0);
select t1.org_name2,
t2.org_name2,
t3.org_name2,
t4.org_name2
from cux_org_level1 t1,
cux_org_level2 t2,
cux_org_level3 t3,
cux_org_level4 t4
where t1.org_id2 = t2.org_id1(+)
and t2.org_id2 = t3.org_id1(+)
and t3.org_id2 = t4.org_id1(+)
;
事务处理登记
select to_char(d.gl_date, 'YYYY-MON') as yuefen,
a.trx_number,
e.name as trx_type,
a.doc_sequence_value,
c.customer_name,
c.customer_number,
a.trx_date,
d.gl_date,
b.unit_selling_price * NVL(b.QUANTITY_CREDITED, b.QUANTITY_INVOICED) as amount,
a.attribute3
from ra_customer_trx_all a,
ra_customer_trx_lines_all b,
ra_customers c,
RA_CUST_TRX_LINE_GL_DIST_ALL d,
RA_CUST_TRX_TYPES_ALL e
where a.org_id = 90
and a.customer_trx_id = b.customer_trx_id
and a.cust_trx_type_id=e.cust_trx_type_id
and a.bill_to_customer_id = c.customer_id
and d.customer_trx_line_id = b.customer_trx_line_id
--and d.gl_date >= to_date('20050101', 'YYYYMMDD')
--and d.gl_date < to_date('20050801', 'YYYYMMDD')
and d.gl_date >= to_date('20050701', 'YYYYMMDD')
and d.gl_date < to_date('20050801', 'YYYYMMDD')
order by to_char(d.gl_date, 'YYYY-MON');
销售人员信息表
select JRS.SALESREP_ID, JRS.NAME, hla.description from jtf_rs_srp_vl jrs, per_all_assignments_f pasf, HR_LOCATIONS_ALL hla
where jrs.PERSON_ID = pasf.person_id and SYSDATE between pasf.effective_start_date and pasf.effective_end_date and pasf.location_id = hla.location_id
参看系统请求的SQL
select t.CONCURRENT_PROGRAM_NAME, t.EXECUTABLE_ID, tt.EXECUTABLE_NAME, tt.EXECUTION_FILE_NAME from FND_CONCURRENT_PROGRAMS_VL t, FND_EXECUTABLES_FORM_V tt where upper(t.CONCURRENT_PROGRAM_NAME) like '%CUX%' and t.EXECUTABLE_ID = tt.EXECUTABLE_ID order by tt.EXECUTION_FILE_NAME;
员工成本,采购订单,部门表(注一个员工如果他的成本有多条的话,那么就有多条记录)
select d.ASSIGNMENT_ID, a.person_id as person_id, a.last_name as last_name, --cux_hr_common.getDepartmentNameByPersonID(a.person_id) as hr_department, cux_hr_common.getDepartmentIDByPersonID(a.person_id) as hr_department, e.segment1 || '.' || e.segment2 || '.' || e.segment3 || '.' || e.segment4 || '.' || e.segment5 || '.' || e.segment6 || '.' || e.segment7 || '.' || e.segment8 "²É¹º¶©µ¥ÐÅÏ¢", f.PROPORTION, f.CONCATENATED_SEGMENTS from PER_PEOPLE_f a, PER_ASSIGNMENTS_F d, gl_code_combinations e, (SELECT PAY.ROWID ROW_ID, PAY.COST_ALLOCATION_ID, PAY.EFFECTIVE_START_DATE, PAY.EFFECTIVE_END_DATE, PAY.BUSINESS_GROUP_ID, PAY.COST_ALLOCATION_KEYFLEX_ID, PAY.ASSIGNMENT_ID, PAY.PROPORTION, PAY.REQUEST_ID, PAY.PROGRAM_APPLICATION_ID, PAY.PROGRAM_ID, PAY.PROGRAM_UPDATE_DATE, PAY.LAST_UPDATE_DATE, PAY.LAST_UPDATED_BY, PAY.LAST_UPDATE_LOGIN, PAY.CREATED_BY, PAY.CREATION_DATE, PCAF.CONCATENATED_SEGMENTS FROM PAY_COST_ALLOCATION_KEYFLEX PCAF, PAY_COST_ALLOCATIONS_F PAY WHERE PCAF.COST_ALLOCATION_KEYFLEX_ID(+) = PAY.COST_ALLOCATION_KEYFLEX_ID) f where a.PERSON_ID = d.PERSON_ID and a.person_type_id = 6 and d.EFFECTIVE_START_DATE >= (select max(EFFECTIVE_START_DATE) from PER_ASSIGNMENTS_F where person_id = d.PERSON_ID) and sysdate between d.EFFECTIVE_START_DATE and d.EFFECTIVE_END_DATE and sysdate between a.EFFECTIVE_START_DATE and a.EFFECTIVE_END_DATE and sysdate between f.EFFECTIVE_START_DATE and f.EFFECTIVE_END_DATE and e.code_combination_id = d.DEFAULT_CODE_COMB_ID and d.ASSIGNMENT_ID = f.ASSIGNMENT_ID-- and d.ASSIGNMENT_ID = 67
--以下部分为没有导入成发票的报销单和导入成报销单又没有导入gl的报销单 select 1 as set_of_books_id, b.code_combination_id, 'CNY' currency_code, to_char(a.creation_date, 'MON-YY'), 0 a_1, 0 a_2, 0 b_1, 0 b_2, b.amount unpost_1, 0 unpost_2, 0, 0 from apps.ap_expense_report_headers_all a, apps.ap_expense_report_lines_all b, apps.per_assignments_f c where a.report_header_id = b.report_header_id and a.employee_id = c.PERSON_ID and a.creation_date between c.EFFECTIVE_START_DATE and c.EFFECTIVE_END_DATE and a.expense_current_approver_id <> c.SUPERVISOR_ID and a.employee_id = 285 and (select count(*) from ap_invoices_all t where t.invoice_num = a.invoice_num) = 0 union all select 1 as set_of_books_id, a.dist_code_combination_id, 'CNY' currency_code, a.period_name, 0 a_1, 0 a_2, 0 b_1, 0 b_2, a.amount unpost_1, 0 unpost_2, 0, 0 from apps.AP_INVOICE_DISTRIBUTIONS_all a where a.posted_flag = 'N'
1.begin
2.fnd_client_info.set_org_context(90);
3.end;
4.
5.select a.customer_id,
6. d.address_id,
7. a.customer_number 客户编号,
8. a.customer_name 客户名称,
9. a.attribute1 与公司关系,
10. a.attribute2 渠道,
11. a.attribute3 行业,
12. b.description 一级行业分类,
13. c.description 二级行业分类,
14. d.status 状态,
15. d.country 国家,
16. d.address1 地址,
17. d.address2 地址2,
18. d.address3 地址3,
19. d.address4 地址4,
20. e.last_name 联系人,
21. f.phone_type 联系方式,
22. f.area_code 地区代码,
23. f.phone_number 电话号码
24. from apps.ra_customers a,
25. (select t.FLEX_VALUE, t.DESCRIPTION
26. from apps.FND_FLEX_VALUES_VL t
27. where t.FLEX_VALUE_SET_ID = 1009676) b,
28. (select t.FLEX_VALUE, t.DESCRIPTION
29. from apps.FND_FLEX_VALUES_VL t
30. where t.FLEX_VALUE_SET_ID = 1009735) c,
31. apps.AR_ADDRESSES_V d,
32. apps.ar_contacts_v e,
33. apps.ra_PHONES f
34.where a.customer_number like 'AD_%'
35. and b.flex_value(+) = a.attribute4
36. and c.flex_value(+) = a.attribute5
37. and d.customer_id = a.customer_id
38. and e.address_id = d.address_id
39. and e.contact_id = f.contact_id
查看AP发票的撤销人
select t.invoice_num, t.cancelled_by, t.cancelled_by_display
from ap_invoices_v t
where t.gl_date between to_date('20060701', 'YYYYMMDD') and
to_date('20060930', 'YYYYMMDD')
and t.cancelled_by is not null;
begin
fnd_client_info.set_org_context(560);
end;
更改AR发票会计科目
/*120*240,创建于2011-6-8*/ var cpro_id = 'u501098';-update ra_cust_trx_line_gl_dist_all t
set t.code_combination_id = 374510
where t.customer_trx_id in
(select customer_trx_id
from RA_CUSTOMER_TRX_ALL t
where t.trx_number >= 'SFQC20061001001174'
and t.trx_number <= 'SFQC20061001012968')
and t.customer_trx_line_id is not null;
请求正在执行的sql
SELECT to_char(s.sid) || ',' || to_char(s.serial#), sql_text
FROM applsys.fnd_concurrent_requests r,
v$process p,
v$session s,
v$sqltext_with_newlines sqlt
WHERE r.oracle_process_id = p.spid
AND p.addr = s.paddr(+)
AND s.sql_address = sqlt.address(+)
AND s.sql_hash_value = sqlt.hash_value(+)
AND r.request_id = 2641173
ORDER BY piece;
select a.LAST_NAME 员工姓名,
a.PERSON_ID 员工ID,
a.SEX 性别,
a.EMAIL_ADDRESS 电子邮件,
b.ORGANIZATION_ID,
aa.LAST_NAME 主管姓名,
aa.EMAIL_ADDRESS 主管email
from per_people_f a, PER_ASSIGNMENTS_F b, per_people_f aa
where a.PERSON_ID = b.person_id
and sysdate between b.EFFECTIVE_START_DATE and b.EFFECTIVE_END_DATE
and sysdate between a.EFFECTIVE_START_DATE and a.EFFECTIVE_END_DATE
and sysdate between aa.EFFECTIVE_START_DATE and aa.EFFECTIVE_END_DATE
and a.PERSON_TYPE_ID <> 9
and b.SUPERVISOR_ID = aa.PERSON_ID
select a.USER_PROFILE_OPTION_NAME 配置文件名,
c.user_name 用户名,
b.profile_option_value 配置文件值,
a.PROFILE_OPTION_ID ,
c.user_id,
c.rowid,
b.rowid
from FND_PROFILE_OPTIONS_VL a, fnd_profile_option_values b, fnd_user c
where a.USER_PROFILE_OPTION_NAME = 'FND:诊断'
and a.PROFILE_OPTION_ID = b.profile_option_id
and c.user_id = b.level_value
and b.level_id = 10004 --10004为用户层
select t.message_type,
t.begin_date,
t.responder,
t.from_user,
t.to_user,
t.subject
from wf_notifications t
where t.responder = 'SYSADMIN'
and t.recipient_role <> t.responder
and t.begin_date between to_date('20070101', 'YYYYMMDD') and
to_date('20070331', 'YYYYMMDD')