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