我想创建一个物化视图（快速刷新）。它包含一些复杂的计算，涉及大约25+个表。它给了我在plsql函数的错误，写在where子句中
mv不支持plsql函数
下面是我要创作的mv。请修改语法，如果我缺少一些东西，以便它可以允许联合和plsql函数的东西与快速刷新。
注意我不允许我的管理层更改查询，因此请建议我以mv的形式使用相同的查询。
create materialized view SAMPLE
ENABLE QUERY REWRITE
refresh fast
as
select ms.employee_id,
ms.sal_code code,
'SALARY' Account_type,
UPPER(sb.salary_breakup) Account_title,
sb.account_code ACCOUNT_CODE,
('ADDITION') AMOUNT_TYPE,
sb.pay_type PAY_TYPE,
p.display_title PAY_TITLE,
ms.salary AMOUNT,
p.order_in_payroll,
to_number((select order_by from payroll_account_title_org_wise
where org_id=p.org_id and account_id=1) || substr(sb.sal_code,3,3)) Code_order,
ms.month,
sb.tax_exemption,
sb.taxtation_type,
'Y' INCLUDE_IN_MONTHLYTAX_INCOME,
p.org_id
from PAY_MONTHLY_SALARY ms,pay_salary_breakups sb,PAYROL_TYPE p
where ms.sal_code=sb.sal_code
and p.pay_type=sb.pay_type
--order by sb.sal_code
union
-----ALLOWANCE PART
select mad.employee_id,
mad.ad_code code,
ad.type Account_type,
UPPER(ad.ad_name) Account_title,
ad.account_code ACCOUNT_CODE,
'ADDITION' AMOUNT_TYPE,
ad.pay_type PAY_TYPE,
p.display_title PAY_TITLE,
mad.amount AMOUNT,
p.order_in_payroll,
to_number((select order_by from payroll_account_title_org_wise
where org_id=p.org_id and account_id=2) || substr(mad.ad_code,3,3)) Code_order,
mad.month,
ad.tax_exemption,
ad.taxtation_type,
'Y' INCLUDE_IN_MONTHLYTAX_INCOME,
p.org_id
from Pay_Month_Allow_Ded mad,pay_allowance_deduction_setup ad,PAYROL_TYPE p
where mad.ad_code=ad.ad_code
and p.pay_type=ad.pay_type
and ad.type ='ALLOWANCE'
--order by ad.ad_code
union
-----DEDUCTION PART
select mad.employee_id,
mad.ad_code code,
ad.type Account_type,
UPPER(ad.ad_name) Account_title,
ad.account_code ACCOUNT_CODE,
'DEDUCTION' AMOUNT_TYPE,
ad.pay_type PAY_TYPE,
p.display_title PAY_TITLE,
mad.amount AMOUNT,
p.order_in_payroll,
to_number((select order_by from payroll_account_title_org_wise
where org_id=p.org_id and account_id=3) || substr(mad.ad_code,3,3)) Code_order,
mad.month,
ad.tax_exemption,
ad.taxtation_type,
'Y' INCLUDE_IN_MONTHLYTAX_INCOME,
p.org_id
from Pay_Month_Allow_Ded mad,pay_allowance_deduction_setup ad,PAYROL_TYPE p
where mad.ad_code=ad.ad_code
and p.pay_type=ad.pay_type
and ad.type='DEDUCTION'
--order by ad.ad_code
union
---- FUNDS AND CONTRIBUTION PART
select e.employee_id
,fcs.fc_code
,'FUNDS' Account_type
,UPPER(fct.fc_type_name) ACCOUNT_TITLE
,(case when fct.type='EMPLOYEE' then fcs.account_code_credit
when fct.type='EMPLOYER' then fcs.account_code
end) ACCOUNT_CODE
,(case when fct.type='EMPLOYEE' then 'DEDUCTION'
when fct.type='EMPLOYER' then 'OTHERTAX'
end) AMOUNT_TYPE
,pt.pay_type
,pt.display_title PAY_TITLE
,r.amount AMOUNT
,pt.order_in_payroll
,to_number((select order_by from payroll_account_title_org_wise
where org_id=pt.org_id and account_id=4) || substr(fcs.fc_code,3,3)) Code_order
,r.month
,(case when fcs.TAX_EXEMPTION='Y' then fcs.tax_exemption
when fcs.TAX_EXEMPTION='N' then fct.tax_exemption
end) TAX_EXEMPTION,
fcs.taxtation_type,
'Y' INCLUDE_IN_MONTHLYTAX_INCOME,
pt.org_id
from
hr_employee_info e
,fund_contribution_records r
,fund_contributions_type fct
,fund_contributions_setup fcs
,payrol_type pt
where e.employee_id=r.employee_id
and r.contribution_type_id=fct.contribution_type_id
and r.fc_code=fcs.fc_code
and fct.fc_code=fcs.fc_code
and fct.payroll_type=pt.pay_type
union
----- ATTENDACE DATA PART
select
pmt.employee_id
,att.att_code
,'ATTENDANCE' Account_type
,UPPER(att.attendace_type) ACCOUNT_TITLE
,NULL ACCOUNT_CODE
,'ZERO' AMOUNT_TYPE
,pt.pay_type
,pt.display_title PAY_TITLE
,pmt.attendance_days AMOUNT
,pt.order_in_payroll
,to_number((select order_by from payroll_account_title_org_wise
where org_id=pt.org_id and account_id=5) || substr(att.att_code,3,3)) Code_order
,pmt.month
,'Y' TAX_EXEMPTION
,'NONE' taxtation_type
,'N' INCLUDE_IN_MONTHLYTAX_INCOME
, pt.org_id
from att_attendance_seup att
,pay_monthly_attendance pmt
,payrol_type pt
where att.att_code=pmt.att_code
and pt.pay_type=att.pay_type
and pt.org_id=att.org_id
/*union
---- TARGET DATA PART
select
pmt.employee_id
,pts.ta_code CODE
,UPPER(pts.target_name) ACCOUNT_TITLE
,'ZERO' AMOUNT_TYPE
,pt.pay_type
,pt.display_title PAY_TITLE
,pmt.value AMOUNT
,pt.order_in_payroll
,to_number(5 || substr(pts.ta_code,4,1)) Code_order
,pmt.month
,'Y' TAX_EXEMPTION,
pt.org_id
from
pay_monthly_targets pmt
,pay_target_setup pts
,payrol_type pt
where pts.ta_code=pmt.ta_code
and pt.pay_type=pts.pay_type*/
union
----LOAN DISBURSEMENT PART
select
pmld.employee_id
,plns.ln_code code
,'LOAN DISBURSEMENT' Account_type
,UPPER(plns.loan_name) ACCOUNT_TITLE
,plns.account_code ACCOUNT_CODE
,'ADDITION' AMOUNT_TYPE
,pt.pay_type
,pt.display_title PAY_TITLE
,pmld.amount
,pt.order_in_payroll
,to_number((select order_by from payroll_account_title_org_wise
where org_id=pt.org_id and account_id=6) || substr(plns.ln_code,3,3)) Code_order
,pmld.month
,'Y' tax_exemption
,plns.taxtation_type
,'N' INCLUDE_IN_MONTHLYTAX_INCOME
, pt.org_id
from
PAY_LOAN_SETUP plns
,pay_loan_monthly_disbursement pmld
,payrol_type pt
where plns.ln_code=pmld.ln_code
and plns.pay_type_disburse=pt.pay_type
union
------- LOAN INSTALLMENTS PART
select
peld.employee_id
,plns.ln_code code
,'LOAN INSTALLMENT' Account_type
,UPPER(plns.loan_name || '(I)') ACCOUNT_TITLE
,plns.account_code_installment ACCOUNT_CODE
,'DEDUCTION' AMOUNT_TYPE
,pt.pay_type
,pt.display_title PAY_TITLE
,plei.amount amount
,pt.order_in_payroll
,to_number((select order_by from payroll_account_title_org_wise
where org_id=pt.org_id and account_id=7) || substr(plns.ln_code,3,3)) Code_order
,plei.month
,'Y' tax_exemption
,plns.taxtation_type
,'N' INCLUDE_IN_MONTHLYTAX_INCOME
, pt.org_id
from
PAY_LOAN_SETUP plns
,PAY_EMP_LOAN_DISBURSEMENT peld
,PAY_EMP_LOAN_INSTALMENTS plei
,payrol_type pt
where plns.ln_code=peld.ln_code
and peld.loan_id=plei.loan_id
and plns.pay_type=pt.pay_type
and pt.org_id=plns.org_id
-----LOAN MARKUP
union
select
peld.employee_id
,plns.ln_code code
,'LOAN MARKUP' Account_type
,UPPER(plns.loan_name || '(M)') ACCOUNT_TITLE
,plns.account_code_markup ACCOUNT_CODE
,'DEDUCTION' AMOUNT_TYPE
,pt.pay_type
,pt.display_title PAY_TITLE
,plei.benchmark_taken amount
,pt.order_in_payroll
,to_number((select order_by from payroll_account_title_org_wise
where org_id=pt.org_id and account_id=8) || substr(plns.ln_code,3,3)) Code_order
,plei.month
,'Y' tax_exemption
,plns.taxtation_type
,'N' INCLUDE_IN_MONTHLYTAX_INCOME
, pt.org_id
from
PAY_LOAN_SETUP plns
,PAY_EMP_LOAN_DISBURSEMENT peld
,PAY_EMP_LOAN_INSTALMENTS plei
,payrol_type pt
where plns.ln_code=peld.ln_code
and peld.loan_id=plei.loan_id
and plns.pay_type=pt.pay_type
--------REIMBURSEMENT PART
union
select
hrc.employee_id
,hrs.re_code CODE
,'REIMBURSEMENT' Account_type
,UPPER(hrs.reimbursement_name) ACCOUNT_TITLE
,hrs.account_code ACCOUNT_CODE
,'ADDITION' AMOUNT_TYPE
,pt.pay_type
,pt.display_title PAY_TITLE
,hrc.amount
,pt.order_in_payroll
,to_number((select order_by from payroll_account_title_org_wise
where org_id=pt.org_id and account_id=9) || substr(hrs.re_code,3,3)) Code_order
,hrc.month
,hrs.tax_exemption
,hrs.taxtation_type
,'Y' INCLUDE_IN_MONTHLYTAX_INCOME
, pt.org_id
from
hr_reimbursement_setup hrs
,pay_monthly_reimbursement hrc
,payrol_type pt
,hr_employee_info e
where hrs.re_code=hrc.re_code
and hrc.employee_id=e.employee_id
and hrs.pay_type=pt.pay_type
--------OTHER INCOME TAX PART
union
select d.employee_id
,to_char('OT'||lpad(d.itax_other_id,3,000)) CODE
,'OTHER TAXABLE' Account_type
,UPPER(s.name || '(OT)') ACCOUNT_TITLE
,NULL ACCOUNT_CODE
,'OTHERTAX' AMOUNT_TYPE
,pt.pay_type
,pt.display_title PAY_TITLE
,(case when s.type='ADDITION' then i.amount
when s.type='EXEMPTION' then i.amount*-1
end) Amount
,pt.order_in_payroll
,to_number((select order_by from payroll_account_title_org_wise
where org_id=pt.org_id and account_id=10) || lpad(d.itax_other_id,3,000)) CODE_ORDER
,i.month
,'N' TAX_EXEMPTION
,s.taxtation_type
,s.include_in_mothly_income INCLUDE_IN_MONTHLYTAX_INCOME
,i.org_id
from
PAY_MONTHLY_OTHER_TAX_INCOME i
,ITAX_DECLARE_OTHER_EMP_INCOME d
,ITAX_OTHER_INCOME_SETUP s
,payrol_type pt
where i.org_id=s.org_id
and d.org_id=s.org_id
and i.itax_declare_id=d.itax_declare_id
and s.itax_other_id=d.itax_other_id
and s.pay_type=pt.pay_type
and s.org_id=pt.org_id
-----TAXABLE INCOME(just to show)
union
select
i.employee_id
,t.tx_code CODE
,'TAXABLE INCOME' Account_type
,'TAXABLE INCOME' ACCOUNT_TITLE
,NULL ACCOUNT_CODE
,'ZERO' AMOUNT_TYPE
,pt.pay_type
,pt.display_title PAY_TITLE
, sum(i.amount) amount
,pt.order_in_payroll
,to_number((select order_by from payroll_account_title_org_wise
where org_id=i.org_id and account_id=11) || substr(t.tx_code,3,3)) Code_order
,i.month
,'Y' TAX_EXEMPTION
,'NONE' taxtation_type
,'N' INCLUDE_IN_MONTHLYTAX_INCOME
,i.org_id
from
TAX_CODE_REGIONS t
,PAY_EMP_MONTHLY_TAXABLE_INCOME i
,payrol_type pt
where t.tx_code=i.tx_code
and i.INCLUDE_IN_MOTHLY_INCOME='Y'
--and t.pay_type=pt.pay_type
and pt.pay_type=Default_Value.GET_SYSTEM_DEFAULT('TXPAYTYPE',i.org_id)
group by i.employee_id,t.tx_code,pt.pay_type,pt.display_title,pt.order_in_payroll,i.month,i.org_id
--------Income Tax Part
union
select
i.employee_id
,t.tx_code CODE
,'INCOME TAX' Account_type
,'INCOME TAX' ACCOUNT_TITLE
,DEFAULT_VALUE.GET_SYSTEM_DEFAULT('ITAC',i.org_id) ACCOUNT_CODE
,'DEDUCTION' AMOUNT_TYPE
,pt.pay_type
,pt.display_title PAY_TITLE
,i.amount amount
,pt.order_in_payroll
,to_number((select order_by from payroll_account_title_org_wise
where org_id=pt.org_id and account_id=12) || substr(t.tx_code,3,3)) Code_order
,i.month
,'Y' TAX_EXEMPTION
,'NONE' taxtation_type
,'N' INCLUDE_IN_MONTHLYTAX_INCOME
,i.org_id
from
TAX_CODE_REGIONS t
,pay_monthly_itax i
,payrol_type pt
where t.tx_code=i.tx_code
--and t.pay_type=pt.pay_type
and pt.pay_type=Default_Value.GET_SYSTEM_DEFAULT('TXPAYTYPE',i.org_id)
-----GRATUITY PART(only for report purpose)
union
select
p.employee_id
,to_char('GP'||lpad(p.gratuity_id,3,000)) CODE
,'GRATUITY' Account_type
,UPPER(gs.name) ACCOUNT_TITLE
,gs.account_code ACCOUNT_CODE
,'ZERO' AMOUNT_TYPE
,gs.pay_type
,pt.display_title PAY_TITLE
,p.amount
,pt.order_in_payroll
,to_number((select order_by from payroll_account_title_org_wise
where org_id=pt.org_id and account_id=13) || lpad(p.gratuity_id,3,000)) CODE_ORDER
,p.month
,'Y' TAX_EXEMPTION
,'NONE' taxtation_type
,'N' INCLUDE_IN_MONTHLYTAX_INCOME
,pt.org_id
from
HR_GRATUITY_MONTHLY_ACCRUALS p,
HR_GRATUITY_SETUP gs,
payrol_type pt
where p.gratuity_id=gs.gratuity_id
and p.org_id=gs.org_id
and gs.pay_type=pt.pay_type
and p.org_id=pt.org_id
---------LEAVE ENCASHMENT PART
union
select
LED.Employee_Id
,'LE'||lpad(lt.leave_type_id,3,0) CODE
,'ALLOWANCE' ACCOUNT_TYPE
,UPPER(lt.leave_name) ACCOUNT_TITLE
,null ACCOUNT_CODE
,'ADDITION' AMOUNT_TYPE
,pt.pay_type
,pt.display_title PAY_TITLE
,LED.Amount
,pt.order_in_payroll
,to_number((select order_by from payroll_account_title_org_wise
where org_id=pt.org_id and account_id=2)||lpad(lt.leave_type_id,3,0)) CODE_ORDER
,LED.Month
,'N' TAX_EXEMPTION
,'VARIABLE' TAXTATION_TYPE
,'Y' INCLUDE_IN_MONTHLYTAX_INCOME
,LED.Org_Id
from
HR_LEAVE_ENCASHMENT_DISBRSE LED
,Hr_Leave_Type lt
,(select max(pt.order_in_payroll) order_in_payroll,pt.org_id from Payrol_Type pt group by pt.org_id) pt_a
,Payrol_Type pt
where LED.Leave_Type_Id=lt.leave_type_id
and LED.Org_Id=lt.org_id
and LED.Org_Id=pt_a.org_id
and pt_a.org_id=pt.org_id
and pt_a.order_in_payroll=pt.order_in_payroll
------Generate Fake Lines For every Employee against pay_type
union
select
e.employee_id
,'FAKE' CODE
,pt.display_title Account_type
,'FAKE' ACCOUNT_TITLE
,pt.account_code ACCOUNT_CODE
,'ZERO' AMOUNT_TYPE
,pt.pay_type
,pt.display_title PAY_TITLE
,0 AMOUNT
,pt.order_in_payroll
,99991 CODE_ORDER
,pi.month
,'Y' TAX_EXEMPTION
,'NONE' taxtation_type
,'N' INCLUDE_IN_MONTHLYTAX_INCOME
,pi.org_id
from
PAY_MONTHLY_PROCESS_INFO pi
,hr_employee_info e
,payrol_type pt
where pi.org_id=e.org_id
and pi.org_id=pt.org_id
and pi.month>=e.join_date;
暂无答案！
目前还没有任何答案，快来回答吧！