VIEW: APPS.PAY_US_GTN_EARNINGS_V

Source

View Text - Preformatted

SELECT paa.tax_unit_id, paa.assignment_id, paa.assignment_action_id, paa.action_status, paa.payroll_action_id, ppa.action_type, ppa.effective_date, ernv.element_name, ernv.element_type_id, ernv.classification_name, pet.processing_priority, pet.effective_start_date, pet.effective_end_date, pet.business_group_id, pet.legislation_code, pet.reporting_name, nvl(pet.reporting_name, pet.element_name) reporting_name_alt, pbt.balance_name, pbt.balance_type_id, pbt.balance_uom, pay_us_balance_view_pkg.get_view_mode view_mode, pay_us_balance_view_pkg.get_calc_all_timetypes_flag calc_all_timetypes, pay_us_taxbal_view_pkg.us_named_balance_vm( upper(pbt.balance_name), 'ASG_GRE_RUN', paa.assignment_action_id, paa.assignment_id, ppa.date_earned, paa.tax_unit_id, pet.business_group_id, null) cash_value, pay_us_taxbal_view_pkg.us_named_balance_vm( upper(pbt2.balance_name), 'ASG_GRE_RUN', paa.assignment_action_id, paa.assignment_id, ppa.date_earned, paa.tax_unit_id, pet.business_group_id, null) hours_value from pay_assignment_actions paa, pay_payroll_actions ppa, pay_element_types_f pet, pay_balance_types pbt, pay_balance_types pbt2, pay_us_earnings_types_v ernv WHERE ppa.payroll_action_id = paa.payroll_action_id and ppa.action_type in('Q', 'R', 'V', 'B', 'I') /* Bug 12637772 : 'B', 'I' have been included in the check. */ and pet.element_type_id = ernv.element_type_id and pet.element_information10 is not null and pet.element_information10 = pbt.balance_type_id and pet.element_information12 = pbt2.balance_type_id (+) and nvl(ppa.date_earned, ppa.effective_date) between pet.effective_start_date and pet.effective_end_date and nvl(ppa.date_earned, ppa.effective_date) between ernv.effective_start_date and ernv.effective_end_date and exists( select null from pay_run_results prr where prr.assignment_action_id = paa.assignment_action_id and prr.element_type_id = ernv.element_type_id)

View Text - HTML Formatted

SELECT PAA.TAX_UNIT_ID
, PAA.ASSIGNMENT_ID
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ACTION_STATUS
, PAA.PAYROLL_ACTION_ID
, PPA.ACTION_TYPE
, PPA.EFFECTIVE_DATE
, ERNV.ELEMENT_NAME
, ERNV.ELEMENT_TYPE_ID
, ERNV.CLASSIFICATION_NAME
, PET.PROCESSING_PRIORITY
, PET.EFFECTIVE_START_DATE
, PET.EFFECTIVE_END_DATE
, PET.BUSINESS_GROUP_ID
, PET.LEGISLATION_CODE
, PET.REPORTING_NAME
, NVL(PET.REPORTING_NAME
, PET.ELEMENT_NAME) REPORTING_NAME_ALT
, PBT.BALANCE_NAME
, PBT.BALANCE_TYPE_ID
, PBT.BALANCE_UOM
, PAY_US_BALANCE_VIEW_PKG.GET_VIEW_MODE VIEW_MODE
, PAY_US_BALANCE_VIEW_PKG.GET_CALC_ALL_TIMETYPES_FLAG CALC_ALL_TIMETYPES
, PAY_US_TAXBAL_VIEW_PKG.US_NAMED_BALANCE_VM( UPPER(PBT.BALANCE_NAME)
, 'ASG_GRE_RUN'
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, PPA.DATE_EARNED
, PAA.TAX_UNIT_ID
, PET.BUSINESS_GROUP_ID
, NULL) CASH_VALUE
, PAY_US_TAXBAL_VIEW_PKG.US_NAMED_BALANCE_VM( UPPER(PBT2.BALANCE_NAME)
, 'ASG_GRE_RUN'
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, PPA.DATE_EARNED
, PAA.TAX_UNIT_ID
, PET.BUSINESS_GROUP_ID
, NULL) HOURS_VALUE
FROM PAY_ASSIGNMENT_ACTIONS PAA
, PAY_PAYROLL_ACTIONS PPA
, PAY_ELEMENT_TYPES_F PET
, PAY_BALANCE_TYPES PBT
, PAY_BALANCE_TYPES PBT2
, PAY_US_EARNINGS_TYPES_V ERNV
WHERE PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND PPA.ACTION_TYPE IN('Q'
, 'R'
, 'V'
, 'B'
, 'I') /* BUG 12637772 : 'B'
, 'I' HAVE BEEN INCLUDED IN THE CHECK. */
AND PET.ELEMENT_TYPE_ID = ERNV.ELEMENT_TYPE_ID
AND PET.ELEMENT_INFORMATION10 IS NOT NULL
AND PET.ELEMENT_INFORMATION10 = PBT.BALANCE_TYPE_ID
AND PET.ELEMENT_INFORMATION12 = PBT2.BALANCE_TYPE_ID (+)
AND NVL(PPA.DATE_EARNED
, PPA.EFFECTIVE_DATE) BETWEEN PET.EFFECTIVE_START_DATE
AND PET.EFFECTIVE_END_DATE
AND NVL(PPA.DATE_EARNED
, PPA.EFFECTIVE_DATE) BETWEEN ERNV.EFFECTIVE_START_DATE
AND ERNV.EFFECTIVE_END_DATE
AND EXISTS( SELECT NULL
FROM PAY_RUN_RESULTS PRR
WHERE PRR.ASSIGNMENT_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID
AND PRR.ELEMENT_TYPE_ID = ERNV.ELEMENT_TYPE_ID)