VIEW: APPS.PAY_CA_EMP_ALL_PROVTAX_INFO_V

Source

View Text - Preformatted

SELECT paf.assignment_id, paf.effective_start_date, paf.effective_end_date, pcp.province_code, pcp.province_abbrev, pcp.province_name, pcp.province_abbrev, pay_ca_emp_tax_inf.get_prov_tax_detail_char(paf.assignment_id, paf.effective_start_date, paf.effective_end_date, fnd.effective_date, pcp.province_abbrev, 'NOTPROV') , pay_ca_emp_tax_inf.get_prov_tax_detail_char(paf.assignment_id, paf.effective_start_date, paf.effective_end_date, fnd.effective_date, pcp.province_abbrev, 'MARRIED') , pay_ca_emp_tax_inf.get_prov_tax_detail_char(paf.assignment_id, paf.effective_start_date, paf.effective_end_date, fnd.effective_date, pcp.province_abbrev, 'NONRES') , pay_ca_emp_tax_inf.get_prov_tax_detail_char(paf.assignment_id, paf.effective_start_date, paf.effective_end_date, fnd.effective_date, pcp.province_abbrev, 'DISABLE'), pay_ca_emp_tax_inf.get_prov_tax_detail_num(paf.assignment_id, paf.effective_start_date, paf.effective_end_date, fnd.effective_date, pcp.province_abbrev, 'NUMDEP') , pay_ca_emp_tax_inf.get_prov_tax_detail_num(paf.assignment_id, paf.effective_start_date, paf.effective_end_date, fnd.effective_date, pcp.province_abbrev, 'INFDEP') , pay_ca_emp_tax_inf.get_prov_tax_detail_num(paf.assignment_id, paf.effective_start_date, paf.effective_end_date, fnd.effective_date, pcp.province_abbrev, 'TCA') , pay_ca_emp_tax_inf.get_prov_tax_detail_num(paf.assignment_id, paf.effective_start_date, paf.effective_end_date, fnd.effective_date, pcp.province_abbrev, 'ADDTAX') , pay_ca_emp_tax_inf.get_prov_tax_detail_char(paf.assignment_id, paf.effective_start_date, paf.effective_end_date, fnd.effective_date, pcp.province_abbrev, 'PROVEXEMPT') , pay_ca_emp_tax_inf.get_prov_tax_detail_char(paf.assignment_id, paf.effective_start_date, paf.effective_end_date, fnd.effective_date, pcp.province_abbrev, 'PMEDEXEMPT') , pay_ca_emp_tax_inf.get_prov_tax_detail_char(paf.assignment_id, paf.effective_start_date, paf.effective_end_date, fnd.effective_date, pcp.province_abbrev, 'WCBEXEMPT') , pay_ca_emp_tax_inf.get_prov_tax_detail_char(paf.assignment_id, paf.effective_start_date, paf.effective_end_date, fnd.effective_date, pcp.province_abbrev, 'QPPEXEMPT') , pay_ca_emp_tax_inf.get_prov_tax_detail_num(paf.assignment_id, paf.effective_start_date, paf.effective_end_date, fnd.effective_date, pcp.province_abbrev, 'ANNDED') , pay_ca_emp_tax_inf.get_prov_tax_detail_num(paf.assignment_id, paf.effective_start_date, paf.effective_end_date, fnd.effective_date, pcp.province_abbrev, 'REMCOMM') , pay_ca_emp_tax_inf.get_prov_tax_detail_num(paf.assignment_id, paf.effective_start_date, paf.effective_end_date, fnd.effective_date, pcp.province_abbrev, 'EXPCOMM') , pay_ca_emp_tax_inf.get_prov_tax_detail_num(paf.assignment_id, paf.effective_start_date, paf.effective_end_date, fnd.effective_date, pcp.province_abbrev, 'PZDN') , pay_ca_emp_tax_inf.get_prov_tax_detail_num(paf.assignment_id, paf.effective_start_date, paf.effective_end_date, fnd.effective_date, pcp.province_abbrev, 'OVERRIDERATE') , pay_ca_emp_tax_inf.get_prov_tax_detail_num(paf.assignment_id, paf.effective_start_date, paf.effective_end_date, fnd.effective_date, pcp.province_abbrev, 'OVERRIDEAMNT') , pay_ca_emp_tax_inf.get_prov_tax_detail_char(paf.assignment_id, paf.effective_start_date, paf.effective_end_date, fnd.effective_date, pcp.province_abbrev, 'CALCMETHOD'), pay_ca_emp_tax_inf.get_prov_tax_detail_dfs(paf.assignment_id, paf.effective_start_date, paf.effective_end_date, fnd.effective_date, pcp.province_abbrev, 'STATINDIAN') , pay_ca_emp_tax_inf.get_prov_tax_detail_dfs(paf.assignment_id, paf.effective_start_date, paf.effective_end_date, fnd.effective_date, pcp.province_abbrev, 'OVER65') , pay_ca_emp_tax_inf.get_prov_tax_detail_num(paf.assignment_id, paf.effective_start_date, paf.effective_end_date, fnd.effective_date, pcp.province_abbrev, 'LSP'), pay_ca_emp_tax_inf.get_prov_tax_detail_char(paf.assignment_id, paf.effective_start_date, paf.effective_end_date, fnd.effective_date, pcp.province_abbrev, 'PPIPEXEMPT') from fnd_sessions fnd, pay_ca_provinces_v pcp, per_assignments_f paf WHERE fnd.session_id = userenv('sessionid') and fnd.effective_date between paf.effective_start_date and paf.effective_end_date

View Text - HTML Formatted

SELECT PAF.ASSIGNMENT_ID
, PAF.EFFECTIVE_START_DATE
, PAF.EFFECTIVE_END_DATE
, PCP.PROVINCE_CODE
, PCP.PROVINCE_ABBREV
, PCP.PROVINCE_NAME
, PCP.PROVINCE_ABBREV
, PAY_CA_EMP_TAX_INF.GET_PROV_TAX_DETAIL_CHAR(PAF.ASSIGNMENT_ID
, PAF.EFFECTIVE_START_DATE
, PAF.EFFECTIVE_END_DATE
, FND.EFFECTIVE_DATE
, PCP.PROVINCE_ABBREV
, 'NOTPROV')
, PAY_CA_EMP_TAX_INF.GET_PROV_TAX_DETAIL_CHAR(PAF.ASSIGNMENT_ID
, PAF.EFFECTIVE_START_DATE
, PAF.EFFECTIVE_END_DATE
, FND.EFFECTIVE_DATE
, PCP.PROVINCE_ABBREV
, 'MARRIED')
, PAY_CA_EMP_TAX_INF.GET_PROV_TAX_DETAIL_CHAR(PAF.ASSIGNMENT_ID
, PAF.EFFECTIVE_START_DATE
, PAF.EFFECTIVE_END_DATE
, FND.EFFECTIVE_DATE
, PCP.PROVINCE_ABBREV
, 'NONRES')
, PAY_CA_EMP_TAX_INF.GET_PROV_TAX_DETAIL_CHAR(PAF.ASSIGNMENT_ID
, PAF.EFFECTIVE_START_DATE
, PAF.EFFECTIVE_END_DATE
, FND.EFFECTIVE_DATE
, PCP.PROVINCE_ABBREV
, 'DISABLE')
, PAY_CA_EMP_TAX_INF.GET_PROV_TAX_DETAIL_NUM(PAF.ASSIGNMENT_ID
, PAF.EFFECTIVE_START_DATE
, PAF.EFFECTIVE_END_DATE
, FND.EFFECTIVE_DATE
, PCP.PROVINCE_ABBREV
, 'NUMDEP')
, PAY_CA_EMP_TAX_INF.GET_PROV_TAX_DETAIL_NUM(PAF.ASSIGNMENT_ID
, PAF.EFFECTIVE_START_DATE
, PAF.EFFECTIVE_END_DATE
, FND.EFFECTIVE_DATE
, PCP.PROVINCE_ABBREV
, 'INFDEP')
, PAY_CA_EMP_TAX_INF.GET_PROV_TAX_DETAIL_NUM(PAF.ASSIGNMENT_ID
, PAF.EFFECTIVE_START_DATE
, PAF.EFFECTIVE_END_DATE
, FND.EFFECTIVE_DATE
, PCP.PROVINCE_ABBREV
, 'TCA')
, PAY_CA_EMP_TAX_INF.GET_PROV_TAX_DETAIL_NUM(PAF.ASSIGNMENT_ID
, PAF.EFFECTIVE_START_DATE
, PAF.EFFECTIVE_END_DATE
, FND.EFFECTIVE_DATE
, PCP.PROVINCE_ABBREV
, 'ADDTAX')
, PAY_CA_EMP_TAX_INF.GET_PROV_TAX_DETAIL_CHAR(PAF.ASSIGNMENT_ID
, PAF.EFFECTIVE_START_DATE
, PAF.EFFECTIVE_END_DATE
, FND.EFFECTIVE_DATE
, PCP.PROVINCE_ABBREV
, 'PROVEXEMPT')
, PAY_CA_EMP_TAX_INF.GET_PROV_TAX_DETAIL_CHAR(PAF.ASSIGNMENT_ID
, PAF.EFFECTIVE_START_DATE
, PAF.EFFECTIVE_END_DATE
, FND.EFFECTIVE_DATE
, PCP.PROVINCE_ABBREV
, 'PMEDEXEMPT')
, PAY_CA_EMP_TAX_INF.GET_PROV_TAX_DETAIL_CHAR(PAF.ASSIGNMENT_ID
, PAF.EFFECTIVE_START_DATE
, PAF.EFFECTIVE_END_DATE
, FND.EFFECTIVE_DATE
, PCP.PROVINCE_ABBREV
, 'WCBEXEMPT')
, PAY_CA_EMP_TAX_INF.GET_PROV_TAX_DETAIL_CHAR(PAF.ASSIGNMENT_ID
, PAF.EFFECTIVE_START_DATE
, PAF.EFFECTIVE_END_DATE
, FND.EFFECTIVE_DATE
, PCP.PROVINCE_ABBREV
, 'QPPEXEMPT')
, PAY_CA_EMP_TAX_INF.GET_PROV_TAX_DETAIL_NUM(PAF.ASSIGNMENT_ID
, PAF.EFFECTIVE_START_DATE
, PAF.EFFECTIVE_END_DATE
, FND.EFFECTIVE_DATE
, PCP.PROVINCE_ABBREV
, 'ANNDED')
, PAY_CA_EMP_TAX_INF.GET_PROV_TAX_DETAIL_NUM(PAF.ASSIGNMENT_ID
, PAF.EFFECTIVE_START_DATE
, PAF.EFFECTIVE_END_DATE
, FND.EFFECTIVE_DATE
, PCP.PROVINCE_ABBREV
, 'REMCOMM')
, PAY_CA_EMP_TAX_INF.GET_PROV_TAX_DETAIL_NUM(PAF.ASSIGNMENT_ID
, PAF.EFFECTIVE_START_DATE
, PAF.EFFECTIVE_END_DATE
, FND.EFFECTIVE_DATE
, PCP.PROVINCE_ABBREV
, 'EXPCOMM')
, PAY_CA_EMP_TAX_INF.GET_PROV_TAX_DETAIL_NUM(PAF.ASSIGNMENT_ID
, PAF.EFFECTIVE_START_DATE
, PAF.EFFECTIVE_END_DATE
, FND.EFFECTIVE_DATE
, PCP.PROVINCE_ABBREV
, 'PZDN')
, PAY_CA_EMP_TAX_INF.GET_PROV_TAX_DETAIL_NUM(PAF.ASSIGNMENT_ID
, PAF.EFFECTIVE_START_DATE
, PAF.EFFECTIVE_END_DATE
, FND.EFFECTIVE_DATE
, PCP.PROVINCE_ABBREV
, 'OVERRIDERATE')
, PAY_CA_EMP_TAX_INF.GET_PROV_TAX_DETAIL_NUM(PAF.ASSIGNMENT_ID
, PAF.EFFECTIVE_START_DATE
, PAF.EFFECTIVE_END_DATE
, FND.EFFECTIVE_DATE
, PCP.PROVINCE_ABBREV
, 'OVERRIDEAMNT')
, PAY_CA_EMP_TAX_INF.GET_PROV_TAX_DETAIL_CHAR(PAF.ASSIGNMENT_ID
, PAF.EFFECTIVE_START_DATE
, PAF.EFFECTIVE_END_DATE
, FND.EFFECTIVE_DATE
, PCP.PROVINCE_ABBREV
, 'CALCMETHOD')
, PAY_CA_EMP_TAX_INF.GET_PROV_TAX_DETAIL_DFS(PAF.ASSIGNMENT_ID
, PAF.EFFECTIVE_START_DATE
, PAF.EFFECTIVE_END_DATE
, FND.EFFECTIVE_DATE
, PCP.PROVINCE_ABBREV
, 'STATINDIAN')
, PAY_CA_EMP_TAX_INF.GET_PROV_TAX_DETAIL_DFS(PAF.ASSIGNMENT_ID
, PAF.EFFECTIVE_START_DATE
, PAF.EFFECTIVE_END_DATE
, FND.EFFECTIVE_DATE
, PCP.PROVINCE_ABBREV
, 'OVER65')
, PAY_CA_EMP_TAX_INF.GET_PROV_TAX_DETAIL_NUM(PAF.ASSIGNMENT_ID
, PAF.EFFECTIVE_START_DATE
, PAF.EFFECTIVE_END_DATE
, FND.EFFECTIVE_DATE
, PCP.PROVINCE_ABBREV
, 'LSP')
, PAY_CA_EMP_TAX_INF.GET_PROV_TAX_DETAIL_CHAR(PAF.ASSIGNMENT_ID
, PAF.EFFECTIVE_START_DATE
, PAF.EFFECTIVE_END_DATE
, FND.EFFECTIVE_DATE
, PCP.PROVINCE_ABBREV
, 'PPIPEXEMPT')
FROM FND_SESSIONS FND
, PAY_CA_PROVINCES_V PCP
, PER_ASSIGNMENTS_F PAF
WHERE FND.SESSION_ID = USERENV('SESSIONID')
AND FND.EFFECTIVE_DATE BETWEEN PAF.EFFECTIVE_START_DATE
AND PAF.EFFECTIVE_END_DATE