177 lines
7.0 KiB
SQL
177 lines
7.0 KiB
SQL
with months as (
|
|
select '202501' as payment_period
|
|
union all select '202502'
|
|
union all select '202503'
|
|
union all select '202504'
|
|
union all select '202505'
|
|
union all select '202506'
|
|
union all select '202507'
|
|
union all select '202508'
|
|
union all select '202509'
|
|
union all select '202510'
|
|
union all select '202511'
|
|
union all select '202512'
|
|
),
|
|
tax_calculation_basal as (
|
|
select erp."TaxCalculationType"
|
|
from _onx4pzkwkeortehfjthgyfkb7c."EmployeeReportPeriodics" erp
|
|
inner join _onx4pzkwkeortehfjthgyfkb7c."Employees" e on erp."EmployeeId" = e."Id"
|
|
where erp."PaymentPeriod" between '202501' and '202512'
|
|
and e."NIK" = 'PS2MA001'
|
|
and erp."BasicSalary" > 0
|
|
order by erp."PaymentPeriod" desc
|
|
limit 1
|
|
),
|
|
matched_records as (
|
|
select e."NIK" as employee_number,
|
|
e."Name" as employee_name,
|
|
e."NPWP" as employee_npwp,
|
|
(case
|
|
when erp."TaxStatus"=1 then 'TK0'
|
|
when erp."TaxStatus"=2 then 'TK1'
|
|
when erp."TaxStatus"=3 then 'TK2'
|
|
when erp."TaxStatus"=4 then 'TK3'
|
|
when erp."TaxStatus"=5 then 'K0'
|
|
when erp."TaxStatus"=6 then 'K1'
|
|
when erp."TaxStatus"=7 then 'K2'
|
|
when erp."TaxStatus"=8 then 'K3'
|
|
end) as tax_marital_status,
|
|
ets."EmployeeTaxStatusCode" as employee_tax_status,
|
|
m.payment_period,
|
|
erp."NPWPPemotong" as npwp_pemotong,
|
|
(case erp."TaxCalculationType" when 2 then 'PaidByEmployee' when 3 then 'PaidAsAllowance' else 'PaidByCompany' end) as tax_type,
|
|
erp."BasicSalary" as basic_salary,
|
|
erp."Overtime" as overtime,
|
|
erp."AllowanceRegular" as allowance,
|
|
erp."Deductions" as deduction,
|
|
erp."Natura" as benefit_in_kind,
|
|
erp."Severance" as severance,
|
|
erp."JKKCompany" as jkk,
|
|
erp."JKMCompany" as jkm,
|
|
erp."BPJSKesCompany" + erp."OtherInsuranceCompany" as bpjs_kesehatan_company,
|
|
erp."JHTEmployee" as jht_emp,
|
|
erp."JPEmployee" as jp_emp,
|
|
0 as pension_emp,
|
|
erp."EmployeeCondition" as employee_condition,
|
|
erp."ResignType" as resign_type,
|
|
erp."AllowanceTaxRegular" as allowance_tax_regular,
|
|
erp."TaxRegular" as tax_regular,
|
|
erp."AllowanceIrregular" as allowance_irregular,
|
|
0 as benefit_in_kind_irregular,
|
|
erp."AllowanceTaxIrregular" as allowance_tax_irregular,
|
|
erp."TaxIrregular" as tax_irregular,
|
|
0 as severance_tax,
|
|
0 as allowance_severance_tax,
|
|
erp."BegSalaryNetto" as beginning_salary_netto,
|
|
erp."BegPPh21" as beginning_pph21
|
|
from months m
|
|
left join _onx4pzkwkeortehfjthgyfkb7c."EmployeeReportPeriodics" erp
|
|
on m.payment_period = erp."PaymentPeriod"
|
|
left join _onx4pzkwkeortehfjthgyfkb7c."Employees" e
|
|
on erp."EmployeeId" = e."Id"
|
|
and e."NIK" = 'PS2MA001'
|
|
left join _onx4pzkwkeortehfjthgyfkb7c."EmployeeTaxStatuses" ets
|
|
on e."EmployeeTaxStatusRefId" = ets."Id"
|
|
where erp."TaxCalculationType" = (select "TaxCalculationType" from tax_calculation_basal)
|
|
),
|
|
unmatched_records as (
|
|
select e."NIK" as employee_number,
|
|
e."Name" as employee_name,
|
|
e."NPWP" as employee_npwp,
|
|
(case
|
|
when erp."TaxStatus"=1 then 'TK0'
|
|
when erp."TaxStatus"=2 then 'TK1'
|
|
when erp."TaxStatus"=3 then 'TK2'
|
|
when erp."TaxStatus"=4 then 'TK3'
|
|
when erp."TaxStatus"=5 then 'K0'
|
|
when erp."TaxStatus"=6 then 'K1'
|
|
when erp."TaxStatus"=7 then 'K2'
|
|
when erp."TaxStatus"=8 then 'K3'
|
|
end) as tax_marital_status,
|
|
ets."EmployeeTaxStatusCode" as employee_tax_status,
|
|
m.payment_period,
|
|
erp."NPWPPemotong" as npwp_pemotong,
|
|
(case erp."TaxCalculationType" when 2 then 'PaidByEmployee' when 3 then 'PaidAsAllowance' else 'PaidByCompany' end) as tax_type,
|
|
erp."BasicSalary" as basic_salary,
|
|
erp."Overtime" as overtime,
|
|
erp."AllowanceRegular" as allowance,
|
|
erp."Deductions" as deduction,
|
|
erp."Natura" as benefit_in_kind,
|
|
erp."Severance" as severance,
|
|
erp."JKKCompany" as jkk,
|
|
erp."JKMCompany" as jkm,
|
|
erp."BPJSKesCompany" + erp."OtherInsuranceCompany" as bpjs_kesehatan_company,
|
|
erp."JHTEmployee" as jht_emp,
|
|
erp."JPEmployee" as jp_emp,
|
|
0 as pension_emp,
|
|
erp."EmployeeCondition" as employee_condition,
|
|
erp."ResignType" as resign_type,
|
|
erp."AllowanceTaxRegular" as allowance_tax_regular,
|
|
erp."TaxRegular" as tax_regular,
|
|
erp."AllowanceIrregular" as allowance_irregular,
|
|
0 as benefit_in_kind_irregular,
|
|
erp."AllowanceTaxIrregular" as allowance_tax_irregular,
|
|
erp."TaxIrregular" as tax_irregular,
|
|
0 as severance_tax,
|
|
0 as allowance_severance_tax,
|
|
erp."BegSalaryNetto" as beginning_salary_netto,
|
|
erp."BegPPh21" as beginning_pph21
|
|
from months m
|
|
left join _onx4pzkwkeortehfjthgyfkb7c."EmployeeReportPeriodics" erp
|
|
on m.payment_period = erp."PaymentPeriod"
|
|
left join _onx4pzkwkeortehfjthgyfkb7c."Employees" e
|
|
on erp."EmployeeId" = e."Id"
|
|
and e."NIK" = 'PS2MA001'
|
|
left join _onx4pzkwkeortehfjthgyfkb7c."EmployeeTaxStatuses" ets
|
|
on e."EmployeeTaxStatusRefId" = ets."Id"
|
|
where erp."TaxCalculationType" <> (select "TaxCalculationType" from tax_calculation_basal)
|
|
and erp."TaxCalculationType" = 0
|
|
),
|
|
empty_records as (
|
|
select null::text as employee_number,
|
|
''::text as employee_name,
|
|
''::text as employee_npwp,
|
|
null::text as tax_marital_status,
|
|
null::integer as employee_tax_status,
|
|
m.payment_period,
|
|
''::text as npwp_pemotong,
|
|
''::text as tax_type,
|
|
0::numeric as basic_salary,
|
|
0::numeric as overtime,
|
|
0::numeric as allowance,
|
|
0::numeric as deduction,
|
|
0::numeric as benefit_in_kind,
|
|
0::numeric as severance,
|
|
0::numeric as jkk,
|
|
0::numeric as jkm,
|
|
0::numeric as bpjs_kesehatan_company,
|
|
0::numeric as jht_emp,
|
|
0::numeric as jp_emp,
|
|
0::numeric as pension_emp,
|
|
null::integer as employee_condition,
|
|
null::integer as resign_type,
|
|
0::numeric as allowance_tax_regular,
|
|
0::numeric as tax_regular,
|
|
0::numeric as allowance_irregular,
|
|
0::numeric as benefit_in_kind_irregular,
|
|
0::numeric as allowance_tax_irregular,
|
|
0::numeric as tax_irregular,
|
|
0::numeric as severance_tax,
|
|
0::numeric as allowance_severance_tax,
|
|
0::numeric as beginning_salary_netto,
|
|
0::numeric as beginning_pph21
|
|
from months m
|
|
where not exists (
|
|
select 1 from matched_records mr where mr.payment_period = m.payment_period
|
|
)
|
|
and not exists (
|
|
select 1 from unmatched_records ur where ur.payment_period = m.payment_period
|
|
)
|
|
)
|
|
select * from matched_records
|
|
union all
|
|
select * from unmatched_records
|
|
union all
|
|
select * from empty_records
|
|
order by payment_period
|