VIEW: APPS.PAY_JP_SOE_ADV_RETRO_ENTRIES_V

Source

View Text - Preformatted

SELECT v.creator_action_id, v.assignment_action_id, v.element_type_id, v.input_currency_code, v.element_entry_id, v.effective_start_date, v.effective_end_date, v.reporting_name, v.processing_priority, v.subpriority, peev.screen_entry_value, substrb(to_char(to_number(peev.screen_entry_value), fnd_currency.get_format_mask(v.input_currency_code, 60)), 1, 80), substrb(pay_jp_soe_pkg.entry_processed_flag(v.element_entry_id,v.effective_start_date,v.effective_end_date), 1, 1) from pay_element_entry_values_f peev, ( select /*+ RULE */ paa.assignment_action_id CREATOR_ACTION_ID, pee.source_asg_action_id ASSIGNMENT_ACTION_ID, piv.element_type_id, pet.input_currency_code, pee.element_entry_id, pee.effective_start_date, pee.effective_end_date, piv.input_value_id, nvl(pettl.reporting_name,pettl.element_name) REPORTING_NAME, pet.processing_priority, pee.subpriority from pay_input_values_f piv, pay_element_types_f_tl pettl, pay_element_types_f pet, pay_element_links_f pel, pay_element_entries_f pee, /* Retro entries */ pay_payroll_actions ppa, /* Retro pact */ pay_assignment_actions paa /* Retro assact */ where ppa.payroll_action_id = paa.payroll_action_id and ppa.action_type = 'L' and pee.assignment_id = paa.assignment_id and pee.entry_type = 'E' and ppa.effective_date between pee.effective_start_date and pee.effective_end_date and pee.creator_id = paa.assignment_action_id and (pee.creator_type = 'RR' or pee.creator_type = 'EE') and pel.element_link_id = pee.element_link_id and ppa.effective_date between pel.effective_start_date and pel.effective_end_date and pet.element_type_id = pel.element_type_id and ppa.effective_date between pet.effective_start_date and pet.effective_end_date and pettl.element_type_id = pet.element_type_id and pettl.language = userenv('LANG') and piv.element_type_id(+) = pet.element_type_id and piv.name(+) = 'Pay Value' and pet.effective_start_date between piv.effective_start_date(+) and piv.effective_end_date(+) ) v where peev.element_entry_id(+) = v.element_entry_id and peev.effective_start_date(+) = v.effective_start_date and peev.effective_end_date(+) = v.effective_end_date and peev.input_value_id(+) = v.input_value_id

View Text - HTML Formatted

SELECT V.CREATOR_ACTION_ID
, V.ASSIGNMENT_ACTION_ID
, V.ELEMENT_TYPE_ID
, V.INPUT_CURRENCY_CODE
, V.ELEMENT_ENTRY_ID
, V.EFFECTIVE_START_DATE
, V.EFFECTIVE_END_DATE
, V.REPORTING_NAME
, V.PROCESSING_PRIORITY
, V.SUBPRIORITY
, PEEV.SCREEN_ENTRY_VALUE
, SUBSTRB(TO_CHAR(TO_NUMBER(PEEV.SCREEN_ENTRY_VALUE)
, FND_CURRENCY.GET_FORMAT_MASK(V.INPUT_CURRENCY_CODE
, 60))
, 1
, 80)
, SUBSTRB(PAY_JP_SOE_PKG.ENTRY_PROCESSED_FLAG(V.ELEMENT_ENTRY_ID
, V.EFFECTIVE_START_DATE
, V.EFFECTIVE_END_DATE)
, 1
, 1)
FROM PAY_ELEMENT_ENTRY_VALUES_F PEEV
, ( SELECT /*+ RULE */ PAA.ASSIGNMENT_ACTION_ID CREATOR_ACTION_ID
, PEE.SOURCE_ASG_ACTION_ID ASSIGNMENT_ACTION_ID
, PIV.ELEMENT_TYPE_ID
, PET.INPUT_CURRENCY_CODE
, PEE.ELEMENT_ENTRY_ID
, PEE.EFFECTIVE_START_DATE
, PEE.EFFECTIVE_END_DATE
, PIV.INPUT_VALUE_ID
, NVL(PETTL.REPORTING_NAME
, PETTL.ELEMENT_NAME) REPORTING_NAME
, PET.PROCESSING_PRIORITY
, PEE.SUBPRIORITY
FROM PAY_INPUT_VALUES_F PIV
, PAY_ELEMENT_TYPES_F_TL PETTL
, PAY_ELEMENT_TYPES_F PET
, PAY_ELEMENT_LINKS_F PEL
, PAY_ELEMENT_ENTRIES_F PEE
, /* RETRO ENTRIES */ PAY_PAYROLL_ACTIONS PPA
, /* RETRO PACT */ PAY_ASSIGNMENT_ACTIONS PAA /* RETRO ASSACT */
WHERE PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND PPA.ACTION_TYPE = 'L'
AND PEE.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
AND PEE.ENTRY_TYPE = 'E'
AND PPA.EFFECTIVE_DATE BETWEEN PEE.EFFECTIVE_START_DATE
AND PEE.EFFECTIVE_END_DATE
AND PEE.CREATOR_ID = PAA.ASSIGNMENT_ACTION_ID
AND (PEE.CREATOR_TYPE = 'RR' OR PEE.CREATOR_TYPE = 'EE')
AND PEL.ELEMENT_LINK_ID = PEE.ELEMENT_LINK_ID
AND PPA.EFFECTIVE_DATE BETWEEN PEL.EFFECTIVE_START_DATE
AND PEL.EFFECTIVE_END_DATE
AND PET.ELEMENT_TYPE_ID = PEL.ELEMENT_TYPE_ID
AND PPA.EFFECTIVE_DATE BETWEEN PET.EFFECTIVE_START_DATE
AND PET.EFFECTIVE_END_DATE
AND PETTL.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID
AND PETTL.LANGUAGE = USERENV('LANG')
AND PIV.ELEMENT_TYPE_ID(+) = PET.ELEMENT_TYPE_ID
AND PIV.NAME(+) = 'PAY VALUE'
AND PET.EFFECTIVE_START_DATE BETWEEN PIV.EFFECTIVE_START_DATE(+)
AND PIV.EFFECTIVE_END_DATE(+) ) V
WHERE PEEV.ELEMENT_ENTRY_ID(+) = V.ELEMENT_ENTRY_ID
AND PEEV.EFFECTIVE_START_DATE(+) = V.EFFECTIVE_START_DATE
AND PEEV.EFFECTIVE_END_DATE(+) = V.EFFECTIVE_END_DATE
AND PEEV.INPUT_VALUE_ID(+) = V.INPUT_VALUE_ID