VIEW: APPS.PAY_PAYROLL_ACTIONS_V6

Source

View Text - Preformatted

SELECT PAC1.ROWID ROW_ID, PAC1.PAYROLL_ID , PAC1.PAYROLL_ACTION_ID , PAC1.EFFECTIVE_DATE , HR_PAYROLLS.DISPLAY_PERIOD_NAME(PAC1.PAYROLL_ACTION_ID) PERIOD_NAME, decode( PAC1.ACTION_TYPE, 'H', nvl(pay_payroll_actions_pkg.decode_cheque_type(pac1.business_group_id), HR_GENERAL.DECODE_LOOKUP('ACTION_TYPE', PAC1.ACTION_TYPE)), 'T', decode(instrb(PAC1.LEGISLATIVE_PARAMETERS,'SLA_MODE=Y'), 0, HR_GENERAL.DECODE_LOOKUP('TRANSFER_TO_LEDGER', 'GL'), null, HR_GENERAL.DECODE_LOOKUP('TRANSFER_TO_LEDGER', 'GL'), HR_GENERAL.DECODE_LOOKUP('TRANSFER_TO_LEDGER', 'SLA')) , HR_GENERAL.DECODE_LOOKUP('ACTION_TYPE', PAC1.ACTION_TYPE)) MEANING, PAY_PAYROLL_ACTIONS_PKG.V_NAME(PAC1.PAYROLL_ACTION_ID, PAC1.ACTION_TYPE, PAC1.CONSOLIDATION_SET_ID, PAC1.DISPLAY_RUN_NUMBER, PAC1.ELEMENT_SET_ID, PAC1.ASSIGNMENT_SET_ID, PAC1.EFFECTIVE_DATE) NAME, PAC1.ACTION_TYPE , PRO.PAYROLL_NAME , PAC1.ACTION_STATUS STATUS_CODE, PAY_PAYROLL_ACTIONS_PKG.V_ACTION_STATUS(PAC1.PAYROLL_ACTION_ID , PAC1.ACTION_STATUS , PAC1.REQUEST_ID ) ACTION_STATUS, PAC1.ACTION_SEQUENCE , PAY_PAYROLL_ACTIONS_PKG.V_MESSAGES_EXIST(PAC1.PAYROLL_ACTION_ID) MESSAGES_EXIST, PAC1.REQUEST_ID FROM pay_payrolls_f pro, pay_payroll_actions pac1 WHERE pac1.payroll_id = pro.payroll_id (+) and pac1.effective_date between pro.effective_start_date (+) and pro.effective_end_date (+) and pac1.action_status <> 'U' and pac1.action_type = nvl(pay_payroll_actions_pkg.get_char_bindvar('ACTION_TYPE'),pac1.action_type) and pac1.business_group_id + 0 = pay_payroll_actions_pkg.get_num_bindvar('BUSINESS_GROUP_ID') and (pac1.payroll_id is null or pay_payroll_actions_pkg.get_num_bindvar('PAYROLL_ID') is null or pac1.payroll_id = pay_payroll_actions_pkg.get_num_bindvar('PAYROLL_ID')) and (exists ( select 'x' from pay_assignment_actions aa, per_assignments_f2 paf, pay_payrolls_f pro1 where pac1.action_type <> 'BEE' and paf.payroll_id = pro1.payroll_id and pro1.payroll_id = pay_payroll_actions_pkg.get_num_bindvar('PAYROLL_ID') and pac1.effective_date between pro1.effective_start_date and pro1.effective_end_date and aa.payroll_action_id = pac1.payroll_action_id and pac1.effective_date between paf.effective_start_date and paf.effective_end_date and aa.assignment_id = paf.assignment_id) or exists ( select 'x' from pay_assignment_actions aa, per_assignments_f2 paf, pay_payrolls_f pro1 where pac1.action_type = 'BEE' and paf.payroll_id = pro1.payroll_id and pro1.payroll_id = pay_payroll_actions_pkg.get_num_bindvar('PAYROLL_ID') and aa.payroll_action_id = pac1.payroll_action_id and aa.assignment_id = paf.assignment_id) or not exists ( select 'x' from pay_assignment_actions aa where aa.payroll_action_id = pac1.payroll_action_id) or (pay_payroll_actions_pkg.get_num_bindvar('PAYROLL_ID') is null )) and nvl(pac1.date_earned, pac1.effective_date) >= pay_payroll_actions_pkg.get_date_bindvar('PERIOD_DATE_FROM') and nvl(pac1.date_earned, pac1.effective_date) <= pay_payroll_actions_pkg.get_date_bindvar('PERIOD_DATE_TO') and (pay_payroll_actions_pkg.get_char_bindvar('SERVER_VALIDATE') = 'Y' or (pay_payroll_actions_pkg.get_char_bindvar('SERVER_VALIDATE') = 'N' and pay_payroll_actions_pkg.get_num_bindvar('PAYROLL_ID') is not null)) union all SELECT PAC1.ROWID ROW_ID, PAC1.PAYROLL_ID , PAC1.PAYROLL_ACTION_ID , PAC1.EFFECTIVE_DATE , HR_PAYROLLS.DISPLAY_PERIOD_NAME(PAC1.PAYROLL_ACTION_ID) PERIOD_NAME, decode( PAC1.ACTION_TYPE, 'H', nvl(pay_payroll_actions_pkg.decode_cheque_type(pac1.business_group_id), HR_GENERAL.DECODE_LOOKUP('ACTION_TYPE', PAC1.ACTION_TYPE)), 'T', decode(instrb(PAC1.LEGISLATIVE_PARAMETERS,'SLA_MODE=Y'), 0, HR_GENERAL.DECODE_LOOKUP('TRANSFER_TO_LEDGER', 'GL'), null, HR_GENERAL.DECODE_LOOKUP('TRANSFER_TO_LEDGER', 'GL'), HR_GENERAL.DECODE_LOOKUP('TRANSFER_TO_LEDGER', 'SLA')) , HR_GENERAL.DECODE_LOOKUP('ACTION_TYPE', PAC1.ACTION_TYPE)) MEANING, PAY_PAYROLL_ACTIONS_PKG.V_NAME(PAC1.PAYROLL_ACTION_ID, PAC1.ACTION_TYPE, PAC1.CONSOLIDATION_SET_ID, PAC1.DISPLAY_RUN_NUMBER, PAC1.ELEMENT_SET_ID, PAC1.ASSIGNMENT_SET_ID, PAC1.EFFECTIVE_DATE) NAME, PAC1.ACTION_TYPE , PRO.PAYROLL_NAME , PAC1.ACTION_STATUS STATUS_CODE, PAY_PAYROLL_ACTIONS_PKG.V_ACTION_STATUS(PAC1.PAYROLL_ACTION_ID , PAC1.ACTION_STATUS , PAC1.REQUEST_ID ) ACTION_STATUS, PAC1.ACTION_SEQUENCE , PAY_PAYROLL_ACTIONS_PKG.V_MESSAGES_EXIST(PAC1.PAYROLL_ACTION_ID) MESSAGES_EXIST, PAC1.REQUEST_ID FROM pay_payrolls_f pro, pay_payroll_actions pac1 WHERE pac1.payroll_id = pro.payroll_id (+) and pac1.effective_date between pro.effective_start_date (+) and pro.effective_end_date (+) and pac1.action_status <> 'U' and pac1.action_type = nvl(pay_payroll_actions_pkg.get_char_bindvar('ACTION_TYPE'),pac1.action_type) and pac1.business_group_id + 0 = pay_payroll_actions_pkg.get_num_bindvar('BUSINESS_GROUP_ID') and (pac1.payroll_id is null or pay_payroll_actions_pkg.get_num_bindvar('PAYROLL_ID') is null or pac1.payroll_id = pay_payroll_actions_pkg.get_num_bindvar('PAYROLL_ID')) and nvl(pac1.date_earned, pac1.effective_date) >= pay_payroll_actions_pkg.get_date_bindvar('PERIOD_DATE_FROM') and nvl(pac1.date_earned, pac1.effective_date) <= pay_payroll_actions_pkg.get_date_bindvar('PERIOD_DATE_TO') and pay_payroll_actions_pkg.get_char_bindvar('SERVER_VALIDATE') = 'N' and pay_payroll_actions_pkg.get_num_bindvar('PAYROLL_ID') is null

View Text - HTML Formatted

SELECT PAC1.ROWID ROW_ID
, PAC1.PAYROLL_ID
, PAC1.PAYROLL_ACTION_ID
, PAC1.EFFECTIVE_DATE
, HR_PAYROLLS.DISPLAY_PERIOD_NAME(PAC1.PAYROLL_ACTION_ID) PERIOD_NAME
, DECODE( PAC1.ACTION_TYPE
, 'H'
, NVL(PAY_PAYROLL_ACTIONS_PKG.DECODE_CHEQUE_TYPE(PAC1.BUSINESS_GROUP_ID)
, HR_GENERAL.DECODE_LOOKUP('ACTION_TYPE'
, PAC1.ACTION_TYPE))
, 'T'
, DECODE(INSTRB(PAC1.LEGISLATIVE_PARAMETERS
, 'SLA_MODE=Y')
, 0
, HR_GENERAL.DECODE_LOOKUP('TRANSFER_TO_LEDGER'
, 'GL')
, NULL
, HR_GENERAL.DECODE_LOOKUP('TRANSFER_TO_LEDGER'
, 'GL')
, HR_GENERAL.DECODE_LOOKUP('TRANSFER_TO_LEDGER'
, 'SLA'))
, HR_GENERAL.DECODE_LOOKUP('ACTION_TYPE'
, PAC1.ACTION_TYPE)) MEANING
, PAY_PAYROLL_ACTIONS_PKG.V_NAME(PAC1.PAYROLL_ACTION_ID
, PAC1.ACTION_TYPE
, PAC1.CONSOLIDATION_SET_ID
, PAC1.DISPLAY_RUN_NUMBER
, PAC1.ELEMENT_SET_ID
, PAC1.ASSIGNMENT_SET_ID
, PAC1.EFFECTIVE_DATE) NAME
, PAC1.ACTION_TYPE
, PRO.PAYROLL_NAME
, PAC1.ACTION_STATUS STATUS_CODE
, PAY_PAYROLL_ACTIONS_PKG.V_ACTION_STATUS(PAC1.PAYROLL_ACTION_ID
, PAC1.ACTION_STATUS
, PAC1.REQUEST_ID ) ACTION_STATUS
, PAC1.ACTION_SEQUENCE
, PAY_PAYROLL_ACTIONS_PKG.V_MESSAGES_EXIST(PAC1.PAYROLL_ACTION_ID) MESSAGES_EXIST
, PAC1.REQUEST_ID
FROM PAY_PAYROLLS_F PRO
, PAY_PAYROLL_ACTIONS PAC1
WHERE PAC1.PAYROLL_ID = PRO.PAYROLL_ID (+)
AND PAC1.EFFECTIVE_DATE BETWEEN PRO.EFFECTIVE_START_DATE (+)
AND PRO.EFFECTIVE_END_DATE (+)
AND PAC1.ACTION_STATUS <> 'U'
AND PAC1.ACTION_TYPE = NVL(PAY_PAYROLL_ACTIONS_PKG.GET_CHAR_BINDVAR('ACTION_TYPE')
, PAC1.ACTION_TYPE)
AND PAC1.BUSINESS_GROUP_ID + 0 = PAY_PAYROLL_ACTIONS_PKG.GET_NUM_BINDVAR('BUSINESS_GROUP_ID')
AND (PAC1.PAYROLL_ID IS NULL OR PAY_PAYROLL_ACTIONS_PKG.GET_NUM_BINDVAR('PAYROLL_ID') IS NULL OR PAC1.PAYROLL_ID = PAY_PAYROLL_ACTIONS_PKG.GET_NUM_BINDVAR('PAYROLL_ID'))
AND (EXISTS ( SELECT 'X'
FROM PAY_ASSIGNMENT_ACTIONS AA
, PER_ASSIGNMENTS_F2 PAF
, PAY_PAYROLLS_F PRO1
WHERE PAC1.ACTION_TYPE <> 'BEE'
AND PAF.PAYROLL_ID = PRO1.PAYROLL_ID
AND PRO1.PAYROLL_ID = PAY_PAYROLL_ACTIONS_PKG.GET_NUM_BINDVAR('PAYROLL_ID')
AND PAC1.EFFECTIVE_DATE BETWEEN PRO1.EFFECTIVE_START_DATE
AND PRO1.EFFECTIVE_END_DATE
AND AA.PAYROLL_ACTION_ID = PAC1.PAYROLL_ACTION_ID
AND PAC1.EFFECTIVE_DATE BETWEEN PAF.EFFECTIVE_START_DATE
AND PAF.EFFECTIVE_END_DATE
AND AA.ASSIGNMENT_ID = PAF.ASSIGNMENT_ID) OR EXISTS ( SELECT 'X'
FROM PAY_ASSIGNMENT_ACTIONS AA
, PER_ASSIGNMENTS_F2 PAF
, PAY_PAYROLLS_F PRO1
WHERE PAC1.ACTION_TYPE = 'BEE'
AND PAF.PAYROLL_ID = PRO1.PAYROLL_ID
AND PRO1.PAYROLL_ID = PAY_PAYROLL_ACTIONS_PKG.GET_NUM_BINDVAR('PAYROLL_ID')
AND AA.PAYROLL_ACTION_ID = PAC1.PAYROLL_ACTION_ID
AND AA.ASSIGNMENT_ID = PAF.ASSIGNMENT_ID) OR NOT EXISTS ( SELECT 'X'
FROM PAY_ASSIGNMENT_ACTIONS AA
WHERE AA.PAYROLL_ACTION_ID = PAC1.PAYROLL_ACTION_ID) OR (PAY_PAYROLL_ACTIONS_PKG.GET_NUM_BINDVAR('PAYROLL_ID') IS NULL ))
AND NVL(PAC1.DATE_EARNED
, PAC1.EFFECTIVE_DATE) >= PAY_PAYROLL_ACTIONS_PKG.GET_DATE_BINDVAR('PERIOD_DATE_FROM')
AND NVL(PAC1.DATE_EARNED
, PAC1.EFFECTIVE_DATE) <= PAY_PAYROLL_ACTIONS_PKG.GET_DATE_BINDVAR('PERIOD_DATE_TO')
AND (PAY_PAYROLL_ACTIONS_PKG.GET_CHAR_BINDVAR('SERVER_VALIDATE') = 'Y' OR (PAY_PAYROLL_ACTIONS_PKG.GET_CHAR_BINDVAR('SERVER_VALIDATE') = 'N'
AND PAY_PAYROLL_ACTIONS_PKG.GET_NUM_BINDVAR('PAYROLL_ID') IS NOT NULL)) UNION ALL SELECT PAC1.ROWID ROW_ID
, PAC1.PAYROLL_ID
, PAC1.PAYROLL_ACTION_ID
, PAC1.EFFECTIVE_DATE
, HR_PAYROLLS.DISPLAY_PERIOD_NAME(PAC1.PAYROLL_ACTION_ID) PERIOD_NAME
, DECODE( PAC1.ACTION_TYPE
, 'H'
, NVL(PAY_PAYROLL_ACTIONS_PKG.DECODE_CHEQUE_TYPE(PAC1.BUSINESS_GROUP_ID)
, HR_GENERAL.DECODE_LOOKUP('ACTION_TYPE'
, PAC1.ACTION_TYPE))
, 'T'
, DECODE(INSTRB(PAC1.LEGISLATIVE_PARAMETERS
, 'SLA_MODE=Y')
, 0
, HR_GENERAL.DECODE_LOOKUP('TRANSFER_TO_LEDGER'
, 'GL')
, NULL
, HR_GENERAL.DECODE_LOOKUP('TRANSFER_TO_LEDGER'
, 'GL')
, HR_GENERAL.DECODE_LOOKUP('TRANSFER_TO_LEDGER'
, 'SLA'))
, HR_GENERAL.DECODE_LOOKUP('ACTION_TYPE'
, PAC1.ACTION_TYPE)) MEANING
, PAY_PAYROLL_ACTIONS_PKG.V_NAME(PAC1.PAYROLL_ACTION_ID
, PAC1.ACTION_TYPE
, PAC1.CONSOLIDATION_SET_ID
, PAC1.DISPLAY_RUN_NUMBER
, PAC1.ELEMENT_SET_ID
, PAC1.ASSIGNMENT_SET_ID
, PAC1.EFFECTIVE_DATE) NAME
, PAC1.ACTION_TYPE
, PRO.PAYROLL_NAME
, PAC1.ACTION_STATUS STATUS_CODE
, PAY_PAYROLL_ACTIONS_PKG.V_ACTION_STATUS(PAC1.PAYROLL_ACTION_ID
, PAC1.ACTION_STATUS
, PAC1.REQUEST_ID ) ACTION_STATUS
, PAC1.ACTION_SEQUENCE
, PAY_PAYROLL_ACTIONS_PKG.V_MESSAGES_EXIST(PAC1.PAYROLL_ACTION_ID) MESSAGES_EXIST
, PAC1.REQUEST_ID
FROM PAY_PAYROLLS_F PRO
, PAY_PAYROLL_ACTIONS PAC1
WHERE PAC1.PAYROLL_ID = PRO.PAYROLL_ID (+)
AND PAC1.EFFECTIVE_DATE BETWEEN PRO.EFFECTIVE_START_DATE (+)
AND PRO.EFFECTIVE_END_DATE (+)
AND PAC1.ACTION_STATUS <> 'U'
AND PAC1.ACTION_TYPE = NVL(PAY_PAYROLL_ACTIONS_PKG.GET_CHAR_BINDVAR('ACTION_TYPE')
, PAC1.ACTION_TYPE)
AND PAC1.BUSINESS_GROUP_ID + 0 = PAY_PAYROLL_ACTIONS_PKG.GET_NUM_BINDVAR('BUSINESS_GROUP_ID')
AND (PAC1.PAYROLL_ID IS NULL OR PAY_PAYROLL_ACTIONS_PKG.GET_NUM_BINDVAR('PAYROLL_ID') IS NULL OR PAC1.PAYROLL_ID = PAY_PAYROLL_ACTIONS_PKG.GET_NUM_BINDVAR('PAYROLL_ID'))
AND NVL(PAC1.DATE_EARNED
, PAC1.EFFECTIVE_DATE) >= PAY_PAYROLL_ACTIONS_PKG.GET_DATE_BINDVAR('PERIOD_DATE_FROM')
AND NVL(PAC1.DATE_EARNED
, PAC1.EFFECTIVE_DATE) <= PAY_PAYROLL_ACTIONS_PKG.GET_DATE_BINDVAR('PERIOD_DATE_TO')
AND PAY_PAYROLL_ACTIONS_PKG.GET_CHAR_BINDVAR('SERVER_VALIDATE') = 'N'
AND PAY_PAYROLL_ACTIONS_PKG.GET_NUM_BINDVAR('PAYROLL_ID') IS NULL