这是一个统计某个会计科目在某个会计期内的费用合计,在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'
- begin
- fnd_client_info.set_org_context(90);
- end;
- select a.customer_id,
- d.address_id,
- a.customer_number 客户编号,
- a.customer_name 客户名称,
- a.attribute1 与公司关系,
- a.attribute2 渠道,
- a.attribute3 行业,
- b.description 一级行业分类,
- c.description 二级行业分类,
- d.status 状态,
- d.country 国家,
- d.address1 地址,
- d.address2 地址2,
- d.address3 地址3,
- d.address4 地址4,
- e.last_name 联系人,
- f.phone_type 联系方式,
- f.area_code 地区代码,
- f.phone_number 电话号码
- from apps.ra_customers a,
- (select t.FLEX_VALUE, t.DESCRIPTION
- from apps.FND_FLEX_VALUES_VL t
- where t.FLEX_VALUE_SET_ID = 1009676) b,
- (select t.FLEX_VALUE, t.DESCRIPTION
- from apps.FND_FLEX_VALUES_VL t
- where t.FLEX_VALUE_SET_ID = 1009735) c,
- apps.AR_ADDRESSES_V d,
- apps.ar_contacts_v e,
- apps.ra_PHONES f
- where a.customer_number like 'AD_%'
- and b.flex_value(+) = a.attribute4
- and c.flex_value(+) = a.attribute5
- and d.customer_id = a.customer_id
- and e.address_id = d.address_id
- 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')
|
|
|
|
|