VIEW: APPS.PAY_US_SRS_RETRY_ROLLBACK_V

Source

View Text - Preformatted

SELECT ppa.payroll_action_id, ppa.action_sequence, ppa.effective_date, ppa.start_date, ppa.action_type, hr_general.DECODE_FND_COMM_LOOKUP('ACTION_TYPE',ppa.action_type) || decode(ppa.action_type, 'X', decode(ppa.report_type, 'YREND','-', decode(ppa.report_category, NULL, NULL, '-' || hr_general.DECODE_FND_COMM_LOOKUP( ppa.report_qualifier||'_'|| ppa.report_type||'_MEDIA_TYPE', ppa.report_category))), NULL), decode(ppa.report_type, 'XFR_INTERFACE', hr_general.decode_payroll(to_number( pay_mag_utils.get_parameter( 'TRANSFER_PAYROLL_ID', 'TRANSFER_CONSOLIDATION_SET_ID', ppa.legislative_parameters))), 'FLS', hr_general.decode_payroll(to_number( decode(ltrim(rtrim(pay_mag_utils.get_parameter( 'TRANSFER_PAYROLL_ID', 'TRANSFER_CONSOLIDATION_SET_ID', ppa.legislative_parameters))), null, '-1', pay_mag_utils.get_parameter( 'TRANSFER_PAYROLL_ID', 'TRANSFER_CONSOLIDATION_SET_ID', ppa.legislative_parameters)))), hr_general.DECODE_PAYROLL(ppa.payroll_id)) "Payroll" , decode(report_type, 'YREND', hr_general.DECODE_ORGANIZATION( to_number( pay_mag_utils.get_parameter( 'TRANSFER_GRE', null,ppa.legislative_parameters))), 'SQWL' ,hr_general.decode_organization (to_number (pay_mag_utils.get_parameter ('TRANSFER_GRE' ,'TRANSFER_PARENT_PAYROLL_ACTION' ,ppa.legislative_parameters))), 'W2C_PRE_PROCESS', hr_general.DECODE_ORGANIZATION(to_number( ltrim(rtrim(substr(ppa.legislative_parameters, instr(ppa.legislative_parameters,'TRANSFER_GRE=')+length('TRANSFER_GRE='), instr(substr(ppa.legislative_parameters, instr(ppa.legislative_parameters,'TRANSFER_GRE=')),' ') - length('TRANSFER_GRE=')))))), 'W-2C PAPER', hr_general.DECODE_ORGANIZATION(to_number( ltrim(rtrim(substr(ppa.legislative_parameters, instr(ppa.legislative_parameters,'TRANSFER_GRE=')+length('TRANSFER_GRE='), instr(substr(ppa.legislative_parameters, instr(ppa.legislative_parameters,'TRANSFER_GRE=')), ' ') - length('TRANSFER_GRE=')))))), '1099R', hr_general.DECODE_ORGANIZATION( to_number( pay_mag_utils.get_parameter( 'TRANSFER_TRANS_LEGAL_CO_ID', 'TRANSFER_ALL_PAYERS', ppa.legislative_parameters))), 'XFR_INTERFACE', hr_us_reports.get_consolidation_set(to_number( pay_mag_utils.get_parameter( 'TRANSFER_CONSOLIDATION_SET_ID', '', ppa.legislative_parameters))), 'FLS', decode(hr_us_reports.get_consolidation_set(to_number(decode(ltrim(rtrim( pay_mag_utils.get_parameter( 'TRANSFER_CONSOLIDATION_SET_ID', 'TRANSFER_TAX_UNIT_ID', ppa.legislative_parameters))), null,'-1', pay_mag_utils.get_parameter( 'TRANSFER_CONSOLIDATION_SET_ID', 'TRANSFER_TAX_UNIT_ID', ppa.legislative_parameters)))), null, hr_general.DECODE_ORGANIZATION(to_number( decode(ltrim(rtrim( pay_mag_utils.get_parameter( 'TRANSFER_TAX_UNIT_ID','', ppa.legislative_parameters))), null,'-1', pay_mag_utils.get_parameter( 'TRANSFER_TAX_UNIT_ID','', ppa.legislative_parameters)))), decode(hr_general.DECODE_ORGANIZATION(to_number( decode(ltrim(rtrim( pay_mag_utils.get_parameter( 'TRANSFER_TAX_UNIT_ID','', ppa.legislative_parameters))), null,'-1', pay_mag_utils.get_parameter ('TRANSFER_TAX_UNIT_ID', '', ppa.legislative_parameters)))), null, hr_us_reports.get_consolidation_set(to_number( pay_mag_utils.get_parameter( 'TRANSFER_CONSOLIDATION_SET_ID', 'TRANSFER_TAX_UNIT_ID', ppa.legislative_parameters))), hr_us_reports.get_consolidation_set(to_number( decode(ltrim(rtrim(pay_mag_utils.get_parameter( 'TRANSFER_CONSOLIDATION_SET_ID', 'TRANSFER_TAX_UNIT_ID', ppa.legislative_parameters))) , null,-1, pay_mag_utils.get_parameter( 'TRANSFER_CONSOLIDATION_SET_ID', 'TRANSFER_TAX_UNIT_ID', ppa.legislative_parameters)))) ||'/'|| hr_general.DECODE_ORGANIZATION( to_number( decode(ltrim(rtrim( pay_mag_utils.get_parameter( 'TRANSFER_TAX_UNIT_ID','', ppa.legislative_parameters))), null,'-1', pay_mag_utils.get_parameter( 'TRANSFER_TAX_UNIT_ID', '', ppa.legislative_parameters)))))), hr_us_reports.get_consolidation_set(ppa.consolidation_set_id)), ppa.report_type, ppa.report_category, ppa.report_type||'-'|| ppa.report_qualifier || decode(report_category,'RL',' - Local ' , '') , ppa.action_status, hr_general.DECODE_FND_COMM_LOOKUP('ACTION_STATUS',ppa.action_status), ppa.request_id, ppa.business_group_id, ppa.current_task, ppa.purge_phase, ppa.payroll_id, ppa.consolidation_set_id, decode(report_type, 'YREND', to_number( pay_mag_utils.get_parameter( 'TRANSFER_GRE', null, ppa.legislative_parameters)), 'W2C_PRE_PROCESS', to_number( ltrim(rtrim(substr(ppa.legislative_parameters, instr(ppa.legislative_parameters,'TRANSFER_GRE=')+length('TRANSFER_GRE='), instr(substr(ppa.legislative_parameters, instr(ppa.legislative_parameters,'TRANSFER_GRE=')),' ') - length('TRANSFER_GRE='))))), 'W-2C PAPER', to_number( ltrim(rtrim(substr(ppa.legislative_parameters, instr(ppa.legislative_parameters,'TRANSFER_GRE=')+length('TRANSFER_GRE='), instr(substr(ppa.legislative_parameters, instr(ppa.legislative_parameters,'TRANSFER_GRE=')), ' ') - length('TRANSFER_GRE='))))), '1099R', to_number( pay_mag_utils.get_parameter( 'TRANSFER_TRANS_LEGAL_CO_ID', 'TRANSFER_ALL_PAYERS', ppa.legislative_parameters)), 'FLS', to_number( pay_mag_utils.get_parameter( 'TRANSFER_TAX_UNIT_ID','', ppa.legislative_parameters)), 'W2', to_number( pay_mag_utils.get_parameter( 'TRANSFER_TRANS_LEGAL_CO_ID', 'TRANSFER_DATE', ppa.legislative_parameters)), 'SQWL', to_number( pay_mag_utils.get_parameter( 'TRANSFER_TRANS_LEGAL_CO_ID', 'TRANSFER_DATE', ppa.legislative_parameters)) ) organization_id from pay_payroll_actions ppa WHERE ppa.action_type <> 'I' and ppa.current_task is null and decode(ppa.action_type, 'X', ppa.report_category, 'Y' ) is not null

View Text - HTML Formatted

SELECT PPA.PAYROLL_ACTION_ID
, PPA.ACTION_SEQUENCE
, PPA.EFFECTIVE_DATE
, PPA.START_DATE
, PPA.ACTION_TYPE
, HR_GENERAL.DECODE_FND_COMM_LOOKUP('ACTION_TYPE'
, PPA.ACTION_TYPE) || DECODE(PPA.ACTION_TYPE
, 'X'
, DECODE(PPA.REPORT_TYPE
, 'YREND'
, '-'
, DECODE(PPA.REPORT_CATEGORY
, NULL
, NULL
, '-' || HR_GENERAL.DECODE_FND_COMM_LOOKUP( PPA.REPORT_QUALIFIER||'_'|| PPA.REPORT_TYPE||'_MEDIA_TYPE'
, PPA.REPORT_CATEGORY)))
, NULL)
, DECODE(PPA.REPORT_TYPE
, 'XFR_INTERFACE'
, HR_GENERAL.DECODE_PAYROLL(TO_NUMBER( PAY_MAG_UTILS.GET_PARAMETER( 'TRANSFER_PAYROLL_ID'
, 'TRANSFER_CONSOLIDATION_SET_ID'
, PPA.LEGISLATIVE_PARAMETERS)))
, 'FLS'
, HR_GENERAL.DECODE_PAYROLL(TO_NUMBER( DECODE(LTRIM(RTRIM(PAY_MAG_UTILS.GET_PARAMETER( 'TRANSFER_PAYROLL_ID'
, 'TRANSFER_CONSOLIDATION_SET_ID'
, PPA.LEGISLATIVE_PARAMETERS)))
, NULL
, '-1'
, PAY_MAG_UTILS.GET_PARAMETER( 'TRANSFER_PAYROLL_ID'
, 'TRANSFER_CONSOLIDATION_SET_ID'
, PPA.LEGISLATIVE_PARAMETERS))))
, HR_GENERAL.DECODE_PAYROLL(PPA.PAYROLL_ID)) "PAYROLL"
, DECODE(REPORT_TYPE
, 'YREND'
, HR_GENERAL.DECODE_ORGANIZATION( TO_NUMBER( PAY_MAG_UTILS.GET_PARAMETER( 'TRANSFER_GRE'
, NULL
, PPA.LEGISLATIVE_PARAMETERS)))
, 'SQWL'
, HR_GENERAL.DECODE_ORGANIZATION (TO_NUMBER (PAY_MAG_UTILS.GET_PARAMETER ('TRANSFER_GRE'
, 'TRANSFER_PARENT_PAYROLL_ACTION'
, PPA.LEGISLATIVE_PARAMETERS)))
, 'W2C_PRE_PROCESS'
, HR_GENERAL.DECODE_ORGANIZATION(TO_NUMBER( LTRIM(RTRIM(SUBSTR(PPA.LEGISLATIVE_PARAMETERS
, INSTR(PPA.LEGISLATIVE_PARAMETERS
, 'TRANSFER_GRE=')+LENGTH('TRANSFER_GRE=')
, INSTR(SUBSTR(PPA.LEGISLATIVE_PARAMETERS
, INSTR(PPA.LEGISLATIVE_PARAMETERS
, 'TRANSFER_GRE='))
, ' ') - LENGTH('TRANSFER_GRE='))))))
, 'W-2C PAPER'
, HR_GENERAL.DECODE_ORGANIZATION(TO_NUMBER( LTRIM(RTRIM(SUBSTR(PPA.LEGISLATIVE_PARAMETERS
, INSTR(PPA.LEGISLATIVE_PARAMETERS
, 'TRANSFER_GRE=')+LENGTH('TRANSFER_GRE=')
, INSTR(SUBSTR(PPA.LEGISLATIVE_PARAMETERS
, INSTR(PPA.LEGISLATIVE_PARAMETERS
, 'TRANSFER_GRE='))
, ' ') - LENGTH('TRANSFER_GRE='))))))
, '1099R'
, HR_GENERAL.DECODE_ORGANIZATION( TO_NUMBER( PAY_MAG_UTILS.GET_PARAMETER( 'TRANSFER_TRANS_LEGAL_CO_ID'
, 'TRANSFER_ALL_PAYERS'
, PPA.LEGISLATIVE_PARAMETERS)))
, 'XFR_INTERFACE'
, HR_US_REPORTS.GET_CONSOLIDATION_SET(TO_NUMBER( PAY_MAG_UTILS.GET_PARAMETER( 'TRANSFER_CONSOLIDATION_SET_ID'
, ''
, PPA.LEGISLATIVE_PARAMETERS)))
, 'FLS'
, DECODE(HR_US_REPORTS.GET_CONSOLIDATION_SET(TO_NUMBER(DECODE(LTRIM(RTRIM( PAY_MAG_UTILS.GET_PARAMETER( 'TRANSFER_CONSOLIDATION_SET_ID'
, 'TRANSFER_TAX_UNIT_ID'
, PPA.LEGISLATIVE_PARAMETERS)))
, NULL
, '-1'
, PAY_MAG_UTILS.GET_PARAMETER( 'TRANSFER_CONSOLIDATION_SET_ID'
, 'TRANSFER_TAX_UNIT_ID'
, PPA.LEGISLATIVE_PARAMETERS))))
, NULL
, HR_GENERAL.DECODE_ORGANIZATION(TO_NUMBER( DECODE(LTRIM(RTRIM( PAY_MAG_UTILS.GET_PARAMETER( 'TRANSFER_TAX_UNIT_ID'
, ''
, PPA.LEGISLATIVE_PARAMETERS)))
, NULL
, '-1'
, PAY_MAG_UTILS.GET_PARAMETER( 'TRANSFER_TAX_UNIT_ID'
, ''
, PPA.LEGISLATIVE_PARAMETERS))))
, DECODE(HR_GENERAL.DECODE_ORGANIZATION(TO_NUMBER( DECODE(LTRIM(RTRIM( PAY_MAG_UTILS.GET_PARAMETER( 'TRANSFER_TAX_UNIT_ID'
, ''
, PPA.LEGISLATIVE_PARAMETERS)))
, NULL
, '-1'
, PAY_MAG_UTILS.GET_PARAMETER ('TRANSFER_TAX_UNIT_ID'
, ''
, PPA.LEGISLATIVE_PARAMETERS))))
, NULL
, HR_US_REPORTS.GET_CONSOLIDATION_SET(TO_NUMBER( PAY_MAG_UTILS.GET_PARAMETER( 'TRANSFER_CONSOLIDATION_SET_ID'
, 'TRANSFER_TAX_UNIT_ID'
, PPA.LEGISLATIVE_PARAMETERS)))
, HR_US_REPORTS.GET_CONSOLIDATION_SET(TO_NUMBER( DECODE(LTRIM(RTRIM(PAY_MAG_UTILS.GET_PARAMETER( 'TRANSFER_CONSOLIDATION_SET_ID'
, 'TRANSFER_TAX_UNIT_ID'
, PPA.LEGISLATIVE_PARAMETERS)))
, NULL
, -1
, PAY_MAG_UTILS.GET_PARAMETER( 'TRANSFER_CONSOLIDATION_SET_ID'
, 'TRANSFER_TAX_UNIT_ID'
, PPA.LEGISLATIVE_PARAMETERS)))) ||'/'|| HR_GENERAL.DECODE_ORGANIZATION( TO_NUMBER( DECODE(LTRIM(RTRIM( PAY_MAG_UTILS.GET_PARAMETER( 'TRANSFER_TAX_UNIT_ID'
, ''
, PPA.LEGISLATIVE_PARAMETERS)))
, NULL
, '-1'
, PAY_MAG_UTILS.GET_PARAMETER( 'TRANSFER_TAX_UNIT_ID'
, ''
, PPA.LEGISLATIVE_PARAMETERS))))))
, HR_US_REPORTS.GET_CONSOLIDATION_SET(PPA.CONSOLIDATION_SET_ID))
, PPA.REPORT_TYPE
, PPA.REPORT_CATEGORY
, PPA.REPORT_TYPE||'-'|| PPA.REPORT_QUALIFIER || DECODE(REPORT_CATEGORY
, 'RL'
, ' - LOCAL '
, '')
, PPA.ACTION_STATUS
, HR_GENERAL.DECODE_FND_COMM_LOOKUP('ACTION_STATUS'
, PPA.ACTION_STATUS)
, PPA.REQUEST_ID
, PPA.BUSINESS_GROUP_ID
, PPA.CURRENT_TASK
, PPA.PURGE_PHASE
, PPA.PAYROLL_ID
, PPA.CONSOLIDATION_SET_ID
, DECODE(REPORT_TYPE
, 'YREND'
, TO_NUMBER( PAY_MAG_UTILS.GET_PARAMETER( 'TRANSFER_GRE'
, NULL
, PPA.LEGISLATIVE_PARAMETERS))
, 'W2C_PRE_PROCESS'
, TO_NUMBER( LTRIM(RTRIM(SUBSTR(PPA.LEGISLATIVE_PARAMETERS
, INSTR(PPA.LEGISLATIVE_PARAMETERS
, 'TRANSFER_GRE=')+LENGTH('TRANSFER_GRE=')
, INSTR(SUBSTR(PPA.LEGISLATIVE_PARAMETERS
, INSTR(PPA.LEGISLATIVE_PARAMETERS
, 'TRANSFER_GRE='))
, ' ') - LENGTH('TRANSFER_GRE=')))))
, 'W-2C PAPER'
, TO_NUMBER( LTRIM(RTRIM(SUBSTR(PPA.LEGISLATIVE_PARAMETERS
, INSTR(PPA.LEGISLATIVE_PARAMETERS
, 'TRANSFER_GRE=')+LENGTH('TRANSFER_GRE=')
, INSTR(SUBSTR(PPA.LEGISLATIVE_PARAMETERS
, INSTR(PPA.LEGISLATIVE_PARAMETERS
, 'TRANSFER_GRE='))
, ' ') - LENGTH('TRANSFER_GRE=')))))
, '1099R'
, TO_NUMBER( PAY_MAG_UTILS.GET_PARAMETER( 'TRANSFER_TRANS_LEGAL_CO_ID'
, 'TRANSFER_ALL_PAYERS'
, PPA.LEGISLATIVE_PARAMETERS))
, 'FLS'
, TO_NUMBER( PAY_MAG_UTILS.GET_PARAMETER( 'TRANSFER_TAX_UNIT_ID'
, ''
, PPA.LEGISLATIVE_PARAMETERS))
, 'W2'
, TO_NUMBER( PAY_MAG_UTILS.GET_PARAMETER( 'TRANSFER_TRANS_LEGAL_CO_ID'
, 'TRANSFER_DATE'
, PPA.LEGISLATIVE_PARAMETERS))
, 'SQWL'
, TO_NUMBER( PAY_MAG_UTILS.GET_PARAMETER( 'TRANSFER_TRANS_LEGAL_CO_ID'
, 'TRANSFER_DATE'
, PPA.LEGISLATIVE_PARAMETERS)) ) ORGANIZATION_ID
FROM PAY_PAYROLL_ACTIONS PPA
WHERE PPA.ACTION_TYPE <> 'I'
AND PPA.CURRENT_TASK IS NULL
AND DECODE(PPA.ACTION_TYPE
, 'X'
, PPA.REPORT_CATEGORY
, 'Y' ) IS NOT NULL