VIEW: APPS.PAY_JP_MAGTAPE_PAYMENTS_V

Source

View Text - Preformatted

SELECT v.payroll_action_id, pea.segment1, v.external_account_id, v.person_id, decode(grouping(v.person_id),0,min(v.min_effective_date),NULL), count(*), sum(v.payment) from pay_external_accounts pea, ( select ppa.payroll_action_id, ppm.external_account_id, pa.person_id, min(ppa2.effective_date) MIN_EFFECTIVE_DATE, sum(ppp.value) PAYMENT from per_all_assignments_f pa, pay_personal_payment_methods_f ppm, pay_payroll_actions ppa2, pay_assignment_actions paa2, pay_pre_payments ppp, pay_assignment_actions paa, pay_payroll_actions ppa where paa.payroll_action_id = ppa.payroll_action_id and ppp.pre_payment_id = paa.pre_payment_id and paa2.assignment_action_id = ppp.assignment_action_id and ppa2.payroll_action_id = paa2.payroll_action_id and ppm.personal_payment_method_id = ppp.personal_payment_method_id and ppa2.effective_date between ppm.effective_start_date and ppm.effective_end_date and pa.assignment_id = paa.assignment_id and ppa.effective_date between pa.effective_start_date and pa.effective_end_date group by ppa.payroll_action_id, pa.person_id, ppm.external_account_id having sum(ppp.value) > 0 ) v where pea.external_account_id = v.external_account_id group by v.payroll_action_id,rollup(pea.segment1,v.external_account_id,v.person_id) having not (grouping(v.external_account_id) + grouping(v.person_id) = 1)

View Text - HTML Formatted

SELECT V.PAYROLL_ACTION_ID
, PEA.SEGMENT1
, V.EXTERNAL_ACCOUNT_ID
, V.PERSON_ID
, DECODE(GROUPING(V.PERSON_ID)
, 0
, MIN(V.MIN_EFFECTIVE_DATE)
, NULL)
, COUNT(*)
, SUM(V.PAYMENT)
FROM PAY_EXTERNAL_ACCOUNTS PEA
, ( SELECT PPA.PAYROLL_ACTION_ID
, PPM.EXTERNAL_ACCOUNT_ID
, PA.PERSON_ID
, MIN(PPA2.EFFECTIVE_DATE) MIN_EFFECTIVE_DATE
, SUM(PPP.VALUE) PAYMENT
FROM PER_ALL_ASSIGNMENTS_F PA
, PAY_PERSONAL_PAYMENT_METHODS_F PPM
, PAY_PAYROLL_ACTIONS PPA2
, PAY_ASSIGNMENT_ACTIONS PAA2
, PAY_PRE_PAYMENTS PPP
, PAY_ASSIGNMENT_ACTIONS PAA
, PAY_PAYROLL_ACTIONS PPA
WHERE PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID
AND PPP.PRE_PAYMENT_ID = PAA.PRE_PAYMENT_ID
AND PAA2.ASSIGNMENT_ACTION_ID = PPP.ASSIGNMENT_ACTION_ID
AND PPA2.PAYROLL_ACTION_ID = PAA2.PAYROLL_ACTION_ID
AND PPM.PERSONAL_PAYMENT_METHOD_ID = PPP.PERSONAL_PAYMENT_METHOD_ID
AND PPA2.EFFECTIVE_DATE BETWEEN PPM.EFFECTIVE_START_DATE
AND PPM.EFFECTIVE_END_DATE
AND PA.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
AND PPA.EFFECTIVE_DATE BETWEEN PA.EFFECTIVE_START_DATE
AND PA.EFFECTIVE_END_DATE GROUP BY PPA.PAYROLL_ACTION_ID
, PA.PERSON_ID
, PPM.EXTERNAL_ACCOUNT_ID HAVING SUM(PPP.VALUE) > 0 ) V
WHERE PEA.EXTERNAL_ACCOUNT_ID = V.EXTERNAL_ACCOUNT_ID GROUP BY V.PAYROLL_ACTION_ID
, ROLLUP(PEA.SEGMENT1
, V.EXTERNAL_ACCOUNT_ID
, V.PERSON_ID) HAVING NOT (GROUPING(V.EXTERNAL_ACCOUNT_ID) + GROUPING(V.PERSON_ID) = 1)