VIEW: APPS.PAY_AC_EMP_DEDN_ACTION_INFO_V

Source

View Text - Preformatted

SELECT pai.action_context_id, pai.tax_unit_id, substr(pay_us_employee_payslip_web.get_jurisdiction_name(pai.jurisdiction_code),1,110) jurisdiction_name, pai.effective_date, pai.action_information1 element_classification, pai.action_information2 element_type_id, pai.action_information6 primary_balance, pai.action_information10 reporting_name, pai.action_information7 processing_priority, substr(decode(length(pai.jurisdiction_code), 11, decode(pai.jurisdiction_code, '00-000-0000', null, decode(pai.action_information10, 'Head Tax Withheld', null, pay_us_employee_payslip_web.get_jurisdiction_name( pai.jurisdiction_code) || ' ')), 8, pay_us_employee_payslip_web.get_jurisdiction_name( substr(pai.jurisdiction_code,1,2)||'-000-0000') || ' ') || decode(fl.description, '', null, nvl(fl.description, pai.action_information10)) || ' ' || decode(length(pai.jurisdiction_code), 8, decode(substr(pai.jurisdiction_code,1,2), '36', substr(pai.jurisdiction_code, 4), pay_us_employee_payslip_web.get_jurisdiction_name(pai.jurisdiction_code)), 11, decode(pai.action_information10, 'Head Tax Withheld', pay_us_employee_payslip_web.get_jurisdiction_name( pai.jurisdiction_code))),1,500) display_name, SUM(FND_NUMBER.CANONICAL_TO_NUMBER(PAI.ACTION_INFORMATION8)) CURRENT_AMOUNT, SUM(FND_NUMBER.CANONICAL_TO_NUMBER(PAI.ACTION_INFORMATION9)) YTD_AMOUNT FROM pay_action_information pai, fnd_common_lookups fl, per_assignments_f paf WHERE PAI.ACTION_CONTEXT_TYPE = 'AAP' AND PAI.ACTION_INFORMATION_CATEGORY = 'AC DEDUCTIONS' AND (PAI.ACTION_INFORMATION9 <> '0' OR PAI.ACTION_INFORMATION8 <> '0') AND UPPER(PAI.ACTION_INFORMATION10) = FL.LOOKUP_CODE(+) AND FL.LOOKUP_TYPE(+) = 'PAY_US_LABELS' AND PAF.ASSIGNMENT_ID = PAI.ASSIGNMENT_ID AND PAI.EFFECTIVE_DATE BETWEEN PAF.EFFECTIVE_START_DATE AND PAF.EFFECTIVE_END_DATE GROUP BY PAF.BUSINESS_GROUP_ID, PAI.ACTION_CONTEXT_ID, PAI.TAX_UNIT_ID, PAI.JURISDICTION_CODE, PAI.EFFECTIVE_DATE, PAI.ACTION_INFORMATION1, PAI.ACTION_INFORMATION2, PAI.ACTION_INFORMATION6, PAI.ACTION_INFORMATION10, PAI.ACTION_INFORMATION7, DECODE(LENGTH(PAI.JURISDICTION_CODE), 11, DECODE(PAI.JURISDICTION_CODE, '00-000-0000', NULL, DECODE(PAI.ACTION_INFORMATION10, 'Head Tax Withheld', NULL, PAY_US_EMPLOYEE_PAYSLIP_WEB.GET_JURISDICTION_NAME( PAI.JURISDICTION_CODE) || ' ')), 8, PAY_US_EMPLOYEE_PAYSLIP_WEB.GET_JURISDICTION_NAME( SUBSTR(PAI.JURISDICTION_CODE,1,2)||'-000-0000') || ' ') || DECODE(FL.DESCRIPTION, '', NULL, NVL(FL.DESCRIPTION, PAI.ACTION_INFORMATION10)) || ' ' || DECODE(LENGTH(PAI.JURISDICTION_CODE), 8, DECODE(SUBSTR(PAI.JURISDICTION_CODE,1,2), '36', SUBSTR(PAI.JURISDICTION_CODE, 4), PAY_US_EMPLOYEE_PAYSLIP_WEB.GET_JURISDICTION_NAME(PAI.JURISDICTION_CODE)), 11, DECODE(PAI.ACTION_INFORMATION10, 'Head Tax Withheld', PAY_US_EMPLOYEE_PAYSLIP_WEB.GET_JURISDICTION_NAME( PAI.JURISDICTION_CODE)))

View Text - HTML Formatted

SELECT PAI.ACTION_CONTEXT_ID
, PAI.TAX_UNIT_ID
, SUBSTR(PAY_US_EMPLOYEE_PAYSLIP_WEB.GET_JURISDICTION_NAME(PAI.JURISDICTION_CODE)
, 1
, 110) JURISDICTION_NAME
, PAI.EFFECTIVE_DATE
, PAI.ACTION_INFORMATION1 ELEMENT_CLASSIFICATION
, PAI.ACTION_INFORMATION2 ELEMENT_TYPE_ID
, PAI.ACTION_INFORMATION6 PRIMARY_BALANCE
, PAI.ACTION_INFORMATION10 REPORTING_NAME
, PAI.ACTION_INFORMATION7 PROCESSING_PRIORITY
, SUBSTR(DECODE(LENGTH(PAI.JURISDICTION_CODE)
, 11
, DECODE(PAI.JURISDICTION_CODE
, '00-000-0000'
, NULL
, DECODE(PAI.ACTION_INFORMATION10
, 'HEAD TAX WITHHELD'
, NULL
, PAY_US_EMPLOYEE_PAYSLIP_WEB.GET_JURISDICTION_NAME( PAI.JURISDICTION_CODE) || ' '))
, 8
, PAY_US_EMPLOYEE_PAYSLIP_WEB.GET_JURISDICTION_NAME( SUBSTR(PAI.JURISDICTION_CODE
, 1
, 2)||'-000-0000') || ' ') || DECODE(FL.DESCRIPTION
, ''
, NULL
, NVL(FL.DESCRIPTION
, PAI.ACTION_INFORMATION10)) || ' ' || DECODE(LENGTH(PAI.JURISDICTION_CODE)
, 8
, DECODE(SUBSTR(PAI.JURISDICTION_CODE
, 1
, 2)
, '36'
, SUBSTR(PAI.JURISDICTION_CODE
, 4)
, PAY_US_EMPLOYEE_PAYSLIP_WEB.GET_JURISDICTION_NAME(PAI.JURISDICTION_CODE))
, 11
, DECODE(PAI.ACTION_INFORMATION10
, 'HEAD TAX WITHHELD'
, PAY_US_EMPLOYEE_PAYSLIP_WEB.GET_JURISDICTION_NAME( PAI.JURISDICTION_CODE)))
, 1
, 500) DISPLAY_NAME
, SUM(FND_NUMBER.CANONICAL_TO_NUMBER(PAI.ACTION_INFORMATION8)) CURRENT_AMOUNT
, SUM(FND_NUMBER.CANONICAL_TO_NUMBER(PAI.ACTION_INFORMATION9)) YTD_AMOUNT
FROM PAY_ACTION_INFORMATION PAI
, FND_COMMON_LOOKUPS FL
, PER_ASSIGNMENTS_F PAF
WHERE PAI.ACTION_CONTEXT_TYPE = 'AAP'
AND PAI.ACTION_INFORMATION_CATEGORY = 'AC DEDUCTIONS'
AND (PAI.ACTION_INFORMATION9 <> '0' OR PAI.ACTION_INFORMATION8 <> '0')
AND UPPER(PAI.ACTION_INFORMATION10) = FL.LOOKUP_CODE(+)
AND FL.LOOKUP_TYPE(+) = 'PAY_US_LABELS'
AND PAF.ASSIGNMENT_ID = PAI.ASSIGNMENT_ID
AND PAI.EFFECTIVE_DATE BETWEEN PAF.EFFECTIVE_START_DATE
AND PAF.EFFECTIVE_END_DATE GROUP BY PAF.BUSINESS_GROUP_ID
, PAI.ACTION_CONTEXT_ID
, PAI.TAX_UNIT_ID
, PAI.JURISDICTION_CODE
, PAI.EFFECTIVE_DATE
, PAI.ACTION_INFORMATION1
, PAI.ACTION_INFORMATION2
, PAI.ACTION_INFORMATION6
, PAI.ACTION_INFORMATION10
, PAI.ACTION_INFORMATION7
, DECODE(LENGTH(PAI.JURISDICTION_CODE)
, 11
, DECODE(PAI.JURISDICTION_CODE
, '00-000-0000'
, NULL
, DECODE(PAI.ACTION_INFORMATION10
, 'HEAD TAX WITHHELD'
, NULL
, PAY_US_EMPLOYEE_PAYSLIP_WEB.GET_JURISDICTION_NAME( PAI.JURISDICTION_CODE) || ' '))
, 8
, PAY_US_EMPLOYEE_PAYSLIP_WEB.GET_JURISDICTION_NAME( SUBSTR(PAI.JURISDICTION_CODE
, 1
, 2)||'-000-0000') || ' ') || DECODE(FL.DESCRIPTION
, ''
, NULL
, NVL(FL.DESCRIPTION
, PAI.ACTION_INFORMATION10)) || ' ' || DECODE(LENGTH(PAI.JURISDICTION_CODE)
, 8
, DECODE(SUBSTR(PAI.JURISDICTION_CODE
, 1
, 2)
, '36'
, SUBSTR(PAI.JURISDICTION_CODE
, 4)
, PAY_US_EMPLOYEE_PAYSLIP_WEB.GET_JURISDICTION_NAME(PAI.JURISDICTION_CODE))
, 11
, DECODE(PAI.ACTION_INFORMATION10
, 'HEAD TAX WITHHELD'
, PAY_US_EMPLOYEE_PAYSLIP_WEB.GET_JURISDICTION_NAME( PAI.JURISDICTION_CODE)))