VIEW: APPS.PAY_VOIDABLE_ASG_ACTIONS_V

Source

View Text - Preformatted

SELECT paf.organization_id, paf.person_id, ppf.full_name , paf.assignment_number, ppa.payroll_id, payrl. payroll_name, payrl.business_group_id, ppa.effective_date Payment_date, ppa.effective_date Process_date, ppa.action_status payroll_action_status, paa.action_status assignment_action_status, paa.assignment_id, paa.assignment_action_id, paa.payroll_action_id, paa.serial_number, paa.pre_payment_id, ppp.personal_payment_method_id, ppp.org_payment_method_id, OPMTL.ORG_PAYMENT_METHOD_NAME , OPM.CURRENCY_CODE, PPT.PAYMENT_TYPE_ID, PPTTL.PAYMENT_TYPE_NAME , ppp.value, PCS.CONSOLIDATION_SET_ID, PCS.CONSOLIDATION_SET_NAME, PAY_ASSIGNMENT_ACTIONS_PKG.GET_PAYMENT_STATUS( ppp.ASSIGNMENT_ACTION_ID, PPP.PRE_PAYMENT_ID) PAYMENT_STATUS from per_people_f ppf, per_assignments_f paf, pay_payroll_Actions ppa, pay_payrolls_f payrl, pay_assignment_actions paa, pay_pre_payments ppp, PAY_ORG_PAYMENT_METHODS_F_TL OPMTL, PAY_PAYMENT_TYPES_TL PPTTL, PAY_ORG_PAYMENT_METHODS_F OPM, PAY_PAYMENT_TYPES PPT, PAY_CONSOLIDATION_SETS PCS WHERE ppa.action_type = 'H' and PAA.action_status = 'C' and ppf.person_id = paf.person_id and paf.assignment_id = paa.assignment_id and paa.payroll_action_id = ppa.payroll_action_id and paa.pre_payment_id = ppp.pre_payment_id and ppp.org_payment_method_id = opm.org_payment_method_id and OPM.ORG_PAYMENT_METHOD_ID = OPMTL.ORG_PAYMENT_METHOD_ID AND OPMTL.LANGUAGE = userenv('LANG') AND PPT.PAYMENT_TYPE_ID = PPTTL.PAYMENT_TYPE_ID AND PPTTL.LANGUAGE = userenv('LANG') AND OPM.PAYMENT_TYPE_ID = PPT.PAYMENT_TYPE_ID AND Ppa.payroll_id = payrl.payroll_id AND Payrl.consolidation_set_id = pcs.consolidation_set_id AND ppa.effective_date between opm.effective_start_date and opm.effective_end_date and ppa.effective_Date between paf.effective_start_date and paf.effective_end_date and ppa.effective_date between ppf.effective_start_date and ppf.effective_end_date and ppa.effective_date between payrl.effective_start_date and payrl.effective_end_date AND NOT EXISTS (SELECT NULL FROM PAY_ACTION_INTERLOCKS PAI WHERE PAI.LOCKED_ACTION_ID = paa.ASSIGNMENT_ACTION_ID)

View Text - HTML Formatted

SELECT PAF.ORGANIZATION_ID
, PAF.PERSON_ID
, PPF.FULL_NAME
, PAF.ASSIGNMENT_NUMBER
, PPA.PAYROLL_ID
, PAYRL. PAYROLL_NAME
, PAYRL.BUSINESS_GROUP_ID
, PPA.EFFECTIVE_DATE PAYMENT_DATE
, PPA.EFFECTIVE_DATE PROCESS_DATE
, PPA.ACTION_STATUS PAYROLL_ACTION_STATUS
, PAA.ACTION_STATUS ASSIGNMENT_ACTION_STATUS
, PAA.ASSIGNMENT_ID
, PAA.ASSIGNMENT_ACTION_ID
, PAA.PAYROLL_ACTION_ID
, PAA.SERIAL_NUMBER
, PAA.PRE_PAYMENT_ID
, PPP.PERSONAL_PAYMENT_METHOD_ID
, PPP.ORG_PAYMENT_METHOD_ID
, OPMTL.ORG_PAYMENT_METHOD_NAME
, OPM.CURRENCY_CODE
, PPT.PAYMENT_TYPE_ID
, PPTTL.PAYMENT_TYPE_NAME
, PPP.VALUE
, PCS.CONSOLIDATION_SET_ID
, PCS.CONSOLIDATION_SET_NAME
, PAY_ASSIGNMENT_ACTIONS_PKG.GET_PAYMENT_STATUS( PPP.ASSIGNMENT_ACTION_ID
, PPP.PRE_PAYMENT_ID) PAYMENT_STATUS
FROM PER_PEOPLE_F PPF
, PER_ASSIGNMENTS_F PAF
, PAY_PAYROLL_ACTIONS PPA
, PAY_PAYROLLS_F PAYRL
, PAY_ASSIGNMENT_ACTIONS PAA
, PAY_PRE_PAYMENTS PPP
, PAY_ORG_PAYMENT_METHODS_F_TL OPMTL
, PAY_PAYMENT_TYPES_TL PPTTL
, PAY_ORG_PAYMENT_METHODS_F OPM
, PAY_PAYMENT_TYPES PPT
, PAY_CONSOLIDATION_SETS PCS
WHERE PPA.ACTION_TYPE = 'H'
AND PAA.ACTION_STATUS = 'C'
AND PPF.PERSON_ID = PAF.PERSON_ID
AND PAF.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
AND PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID
AND PAA.PRE_PAYMENT_ID = PPP.PRE_PAYMENT_ID
AND PPP.ORG_PAYMENT_METHOD_ID = OPM.ORG_PAYMENT_METHOD_ID
AND OPM.ORG_PAYMENT_METHOD_ID = OPMTL.ORG_PAYMENT_METHOD_ID
AND OPMTL.LANGUAGE = USERENV('LANG')
AND PPT.PAYMENT_TYPE_ID = PPTTL.PAYMENT_TYPE_ID
AND PPTTL.LANGUAGE = USERENV('LANG')
AND OPM.PAYMENT_TYPE_ID = PPT.PAYMENT_TYPE_ID
AND PPA.PAYROLL_ID = PAYRL.PAYROLL_ID
AND PAYRL.CONSOLIDATION_SET_ID = PCS.CONSOLIDATION_SET_ID
AND PPA.EFFECTIVE_DATE BETWEEN OPM.EFFECTIVE_START_DATE
AND OPM.EFFECTIVE_END_DATE
AND PPA.EFFECTIVE_DATE BETWEEN PAF.EFFECTIVE_START_DATE
AND PAF.EFFECTIVE_END_DATE
AND PPA.EFFECTIVE_DATE BETWEEN PPF.EFFECTIVE_START_DATE
AND PPF.EFFECTIVE_END_DATE
AND PPA.EFFECTIVE_DATE BETWEEN PAYRL.EFFECTIVE_START_DATE
AND PAYRL.EFFECTIVE_END_DATE
AND NOT EXISTS (SELECT NULL
FROM PAY_ACTION_INTERLOCKS PAI
WHERE PAI.LOCKED_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID)