PAY Payroll Report (Final Notice of Fixed Labor Ins Fee)

select /*+ leading(PAY_JP_LABOR_INS_V.ppa) use_nl(PAY_JP_LABOR_INS_V.ppa PAY_JP_LABOR_INS_V.paa) */
    salary_category,
	target_month,
	count(decode(wai_organization_id,:P_ORGANIZATION_ID,decode(wai_category,'EE',decode(sign(wai_sal_amt),1,'X',NULL),NULL),NULL))										WAI_EE_COUNT,
	nvl(sum(decode(wai_organization_id,:P_ORGANIZATION_ID,decode(wai_category,'EE',decode(sign(wai_sal_amt),1,wai_sal_amt,NULL),NULL),NULL)),0)								WAI_EE_SAL_AMT,
	count(decode(wai_organization_id,:P_ORGANIZATION_ID,decode(wai_category,'EX',decode(sign(wai_sal_amt),1,'X',NULL),NULL),NULL))										WAI_EX_COUNT,
	nvl(sum(decode(wai_organization_id,:P_ORGANIZATION_ID,decode(wai_category,'EX',decode(sign(wai_sal_amt),1,wai_sal_amt,NULL),NULL),NULL)),0)								WAI_EX_SAL_AMT,
	count(decode(wai_organization_id,:P_ORGANIZATION_ID,decode(wai_category,'TW',decode(sign(wai_sal_amt),1,'X',NULL),NULL),NULL))										WAI_TW_COUNT,
	nvl(sum(decode(wai_organization_id,:P_ORGANIZATION_ID,decode(wai_category,'TW',decode(sign(wai_sal_amt),1,wai_sal_amt,NULL),NULL),NULL)),0)								WAI_TW_SAL_AMT,
	count(decode(ui_organization_id,:P_ORGANIZATION_ID,decode(ui_category,'EE',decode(sign(ui_sal_amt),1,'X',NULL),'EE_AGED',decode(sign(ui_sal_amt),1,'X',NULL),NULL),NULL))				UI_EE_COUNT,
	nvl(sum(decode(ui_organization_id,:P_ORGANIZATION_ID,decode(ui_category,'EE',decode(sign(ui_sal_amt),1,ui_sal_amt,NULL),'EE_AGED',decode(sign(ui_sal_amt),1,ui_sal_amt,NULL),NULL),NULL)),0)		UI_EE_SAL_AMT,
	count(decode(ui_organization_id,:P_ORGANIZATION_ID,decode(ui_category,'EX',decode(sign(ui_sal_amt),1,'X',NULL),'EX_AGED',decode(sign(ui_sal_amt),1,'X',NULL),NULL),NULL))				UI_EX_COUNT,
	nvl(sum(decode(ui_organization_id,:P_ORGANIZATION_ID,decode(ui_category,'EX',decode(sign(ui_sal_amt),1,ui_sal_amt,NULL),'EX_AGED',decode(sign(ui_sal_amt),1,ui_sal_amt,NULL),NULL),NULL)),0)		UI_EX_SAL_AMT,
	count(decode(ui_organization_id,:P_ORGANIZATION_ID,decode(ui_category,'EE_AGED',decode(sign(ui_sal_amt),1,'X',NULL),'EX_AGED',decode(sign(ui_sal_amt),1,'X',NULL),NULL),NULL))				UI_AGED_COUNT,
	nvl(sum(decode(ui_organization_id,:P_ORGANIZATION_ID,decode(ui_category,'EE_AGED',decode(sign(ui_sal_amt),1,ui_sal_amt,NULL),'EX_AGED',decode(sign(ui_sal_amt),1,ui_sal_amt,NULL),NULL),NULL)),0)	UI_AGED_SAL_AMT,
	PAY_PAYJPFLI_XMLP_PKG.cf_li_dummyformula(salary_category,target_month,count(decode(wai_organization_id,:P_ORGANIZATION_ID,decode(wai_category,'EE',decode(sign(wai_sal_amt),1,'X',NULL),NULL),NULL)),count(decode(wai_organization_id,:P_ORGANIZATION_ID,decode(wai_category,'EX',decode(sign(wai_sal_amt),1,'X',NULL),NULL),NULL)),count(decode(wai_organization_id,:P_ORGANIZATION_ID,decode(wai_category,'TW',decode(sign(wai_sal_amt),1,'X',NULL),NULL),NULL)),nvl(sum(decode(wai_organization_id,:P_ORGANIZATION_ID,decode(wai_category,'EE',decode(sign(wai_sal_amt),1,wai_sal_amt,NULL),NULL),NULL)),0),nvl(sum(decode(wai_organization_id,:P_ORGANIZATION_ID,decode(wai_category,'EX',decode(sign(wai_sal_amt),1,wai_sal_amt,NULL),NULL),NULL)),0),nvl(sum(decode(wai_organization_id,:P_ORGANIZATION_ID,decode(wai_category,'TW',decode(sign(wai_sal_amt),1,wai_sal_amt,NULL),NULL),NULL)),0),count(decode(ui_organization_id,:P_ORGANIZATION_ID,decode(ui_category,'EE',decode(sign(ui_sal_amt),1,'X',NULL),'EE_AGED',decode(sign(ui_sal_amt),1,'X',NULL),NULL),NULL)),count(decode(ui_organization_id,:P_ORGANIZATION_ID,decode(ui_category,'EX',decode(sign(ui_sal_amt),1,'X',NULL),'EX_AGED',decode(sign(ui_sal_amt),1,'X',NULL),NULL),NULL)),nvl(sum(decode(ui_organization_id,:P_ORGANIZATION_ID,decode(ui_category,'EE',decode(sign(ui_sal_amt),1,ui_sal_amt,NULL),'EE_AGED',decode(sign(ui_sal_amt),1,ui_sal_amt,NULL),NULL),NULL)),0),nvl(sum(decode(ui_organization_id,:P_ORGANIZATION_ID,decode(ui_category,'EX',decode(sign(ui_sal_amt),1,ui_sal_amt,NULL),'EX_AGED',decode(sign(ui_sal_amt),1,ui_sal_amt,NULL),NULL),NULL)),0),count(decode(ui_organization_id,:P_ORGANIZATION_ID,decode(ui_category,'EE_AGED',decode(sign(ui_sal_amt),1,'X',NULL),'EX_AGED',decode(sign(ui_sal_amt),1,'X',NULL),NULL),NULL))),
	PAY_PAYJPFLI_XMLP_PKG.CP_SALARY_CATEGORY_p CP_SALARY_CATEGORY,
PAY_PAYJPFLI_XMLP_PKG.CP_TARGET_YEAR_p CP_TARGET_YEAR,
PAY_PAYJPFLI_XMLP_PKG.CP_TARGET_MONTH_p CP_TARGET_MONTH,
PAY_PAYJPFLI_XMLP_PKG.CP_WAI_COUNT_p CP_WAI_COUNT,
PAY_PAYJPFLI_XMLP_PKG.CP_WAI_SAL_AMT_p CP_WAI_SAL_AMT,
PAY_PAYJPFLI_XMLP_PKG.CP_UI_AGED_SAL_AMT_SUM_p CP_UI_AGED_SAL_AMT_SUM,
PAY_PAYJPFLI_XMLP_PKG.CP_UI_COUNT_p CP_UI_COUNT,
PAY_PAYJPFLI_XMLP_PKG.CP_UI_SAL_AMT_p CP_UI_SAL_AMT,
'X'  dummy
from	PAY_JP_LABOR_INS_V
where	business_group_id=:P_BUSINESS_GROUP_ID
and	to_number(target_month) between to_number(to_char(:P_FISCAL_YEAR) || '04') and to_number(to_char(:P_FISCAL_YEAR + 1) || '03')
and	(wai_organization_id=:P_ORGANIZATION_ID or ui_organization_id=:P_ORGANIZATION_ID)
and	(wai_sal_amt > 0 or ui_sal_amt > 0)
group by
	salary_category,
	target_month
order by decode(salary_category,'SALARY',1,'BONUS',2,'SP_BONUS',3),2