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

Description
Categories: BI Publisher
Columns: Salary Category, Target Month, Wai Ee Count, Wai Ee Sal Amt, Wai Ex Count, Wai Ex Sal Amt, Wai Tw Count, Wai Tw Sal Amt, Ui Ee Count, Ui Ee Sal Amt ...
Application: Payroll
Source: Payroll Report (Final Notice of Fixed Labor Ins Fee) (XML)
Short Name: PAYJPFLI_XML
DB package: PAY_PAYJPFLI_XMLP_PKG
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
Parameter Name SQL text Validation
Fiscal Year
 
Number
Employer
 
LOV Oracle
Business Group
 
LOV Oracle