VIEW: APPS.PAY_SG_ASG_NET_PAYMENTS_V

Source

View Text - Preformatted

SELECT pac.rowid row_id, pac.assignment_action_id prepay_ssignment_action_id, case when ppt.payment_type_name like '%Direct Deposit%' then pea.segment1 else pop.org_payment_method_name end as account_number, hl.meaning account_type, pea.segment5 bank_name, ppm.percentage, ppp.value net_pay, pop.currency_code from hr_organization_units hou, hr_organization_information hoi, pay_external_accounts pea, hr_lookups hl, pay_personal_payment_methods_f ppm, pay_pre_payments ppp, pay_assignment_actions pac, pay_payroll_actions ppa, pay_payment_types ppt, pay_org_payment_methods_f pop WHERE hoi.org_information_context = 'Business Group Information' and hoi.org_information9 = 'SG' and hou.organization_id = hoi.organization_id and hou.business_group_id = pop.business_group_id and ppa.payroll_action_id = pac.payroll_action_id and ppa.action_type in ('P','U') and ppa.action_status = 'C' and ppt.payment_type_id = pop.payment_type_id and (ppt.territory_code = 'SG' or ppt.territory_code is null) and pac.assignment_action_id = ppp.assignment_action_id and pop.org_payment_method_id = ppp.org_payment_method_id and ppm.personal_payment_method_id(+) = ppp.personal_payment_method_id and ppa.effective_date between nvl(ppm.effective_start_date,to_date('01-01-0001','DD-MM-YYYY')) and nvl(ppm.effective_end_date,to_date('31-12-4712','DD-MM-YYYY')) and pea.segment3 = hl.lookup_code (+) and hl.lookup_type(+) = 'SG_ACCOUNT_TYPE' and pea.external_account_id(+) = ppm.external_account_id and ppa.effective_date between pop.effective_start_date and pop.effective_end_date

View Text - HTML Formatted

SELECT PAC.ROWID ROW_ID
, PAC.ASSIGNMENT_ACTION_ID PREPAY_SSIGNMENT_ACTION_ID
, CASE WHEN PPT.PAYMENT_TYPE_NAME LIKE '%DIRECT DEPOSIT%' THEN PEA.SEGMENT1 ELSE POP.ORG_PAYMENT_METHOD_NAME END AS ACCOUNT_NUMBER
, HL.MEANING ACCOUNT_TYPE
, PEA.SEGMENT5 BANK_NAME
, PPM.PERCENTAGE
, PPP.VALUE NET_PAY
, POP.CURRENCY_CODE
FROM HR_ORGANIZATION_UNITS HOU
, HR_ORGANIZATION_INFORMATION HOI
, PAY_EXTERNAL_ACCOUNTS PEA
, HR_LOOKUPS HL
, PAY_PERSONAL_PAYMENT_METHODS_F PPM
, PAY_PRE_PAYMENTS PPP
, PAY_ASSIGNMENT_ACTIONS PAC
, PAY_PAYROLL_ACTIONS PPA
, PAY_PAYMENT_TYPES PPT
, PAY_ORG_PAYMENT_METHODS_F POP
WHERE HOI.ORG_INFORMATION_CONTEXT = 'BUSINESS GROUP INFORMATION'
AND HOI.ORG_INFORMATION9 = 'SG'
AND HOU.ORGANIZATION_ID = HOI.ORGANIZATION_ID
AND HOU.BUSINESS_GROUP_ID = POP.BUSINESS_GROUP_ID
AND PPA.PAYROLL_ACTION_ID = PAC.PAYROLL_ACTION_ID
AND PPA.ACTION_TYPE IN ('P'
, 'U')
AND PPA.ACTION_STATUS = 'C'
AND PPT.PAYMENT_TYPE_ID = POP.PAYMENT_TYPE_ID
AND (PPT.TERRITORY_CODE = 'SG' OR PPT.TERRITORY_CODE IS NULL)
AND PAC.ASSIGNMENT_ACTION_ID = PPP.ASSIGNMENT_ACTION_ID
AND POP.ORG_PAYMENT_METHOD_ID = PPP.ORG_PAYMENT_METHOD_ID
AND PPM.PERSONAL_PAYMENT_METHOD_ID(+) = PPP.PERSONAL_PAYMENT_METHOD_ID
AND PPA.EFFECTIVE_DATE BETWEEN NVL(PPM.EFFECTIVE_START_DATE
, TO_DATE('01-01-0001'
, 'DD-MM-YYYY'))
AND NVL(PPM.EFFECTIVE_END_DATE
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY'))
AND PEA.SEGMENT3 = HL.LOOKUP_CODE (+)
AND HL.LOOKUP_TYPE(+) = 'SG_ACCOUNT_TYPE'
AND PEA.EXTERNAL_ACCOUNT_ID(+) = PPM.EXTERNAL_ACCOUNT_ID
AND PPA.EFFECTIVE_DATE BETWEEN POP.EFFECTIVE_START_DATE
AND POP.EFFECTIVE_END_DATE