Delete bank accounts HDL – SQL
To delete the bank accounts, we
need to delete :
1. Personal
Payment Methods
2. External
Bank Accounts
Personal
Payment Methods delete HDL – SQL:
Select HDL from (
Select
('METADATA|PersonalPaymentMethod|PersonalPaymentMethodId|EffectiveStartDate|EffectiveEndDate|LegislativeDataGroupId|OrganizationPaymentMethodId')
HDL, 1 as no
from dual
Union
(
SELECT
('DELETE'||'|'||
'PersonalPaymentMethod'||'|'||
PAY.PERSONAL_PAYMENT_METHOD_ID||'|'||
TO_CHAR(PAY.EFFECTIVE_START_DATE,'YYYY/MM/DD')||'|'||
TO_CHAR(PAY.EFFECTIVE_END_DATE,'YYYY/MM/DD')||'|'||
PAY.LEGISLATIVE_DATA_GROUP_ID||'|'||
PAY.ORG_PAYMENT_METHOD_ID)
HDL,
2 as no
from
PER_ALL_PEOPLE_F P,
PER_ALL_ASSIGNMENTS_F
A,
PAY_PERSON_PAY_METHODS_F
PAY,
PAY_PAY_RELATIONSHIPS_DN
P2,
PAY_PAYROLL_ASSIGNMENTS
P3
where 1=1
AND P.PERSON_ID =
A.PERSON_ID
AND
A.ASSIGNMENT_STATUS_TYPE IN ('ACTIVE')
AND
A.ASSIGNMENT_TYPE IN ('E','C')
AND
P2.PAYROLL_RELATIONSHIP_ID = P3.PAYROLL_RELATIONSHIP_ID
AND
P3.HR_ASSIGNMENT_ID = A.ASSIGNMENT_ID
AND
PAY.PAYROLL_RELATIONSHIP_ID = P2.PAYROLL_RELATIONSHIP_ID
AND SYSDATE BETWEEN
A.EFFECTIVE_START_DATE AND A.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN
P.EFFECTIVE_START_DATE AND P.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN
P2.START_DATE AND P2.END_DATE
AND SYSDATE BETWEEN
P3.START_DATE AND P3.END_DATE
AND SYSDATE BETWEEN
PAY.EFFECTIVE_START_DATE AND PAY.EFFECTIVE_END_DATE
AND P.PERSON_NUMBER
IN('121')
)
)
order by no
External
Bank Accounts delete HDL – SQL:
Select HDL from (
Select
('METADATA|ExternalBankAccount|ExternalBankAccountId')
HDL, 1 as no
from dual
Union
(
SELECT
('DELETE'||'|'||
'ExternalBankAccount'||'|'||
BA.BANK_ACCOUNT_ID) HDL,
2 as no
from
PER_ALL_PEOPLE_F P,
PER_ALL_ASSIGNMENTS_F A,
PAY_PERSON_PAY_METHODS_F PAY,
PAY_PAY_RELATIONSHIPS_DN P2,
PAY_PAYROLL_ASSIGNMENTS P3,
PAY_BANK_ACCOUNTS BA
where 1=1
AND P.PERSON_ID = A.PERSON_ID
AND A.ASSIGNMENT_STATUS_TYPE IN ('ACTIVE')
AND A.ASSIGNMENT_TYPE IN ('E','C')
AND P2.PAYROLL_RELATIONSHIP_ID = P3.PAYROLL_RELATIONSHIP_ID
AND P3.HR_ASSIGNMENT_ID = A.ASSIGNMENT_ID
AND PAY.PAYROLL_RELATIONSHIP_ID = P2.PAYROLL_RELATIONSHIP_ID
AND SYSDATE BETWEEN A.EFFECTIVE_START_DATE AND A.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN P.EFFECTIVE_START_DATE AND P.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN P2.START_DATE AND P2.END_DATE
AND SYSDATE BETWEEN P3.START_DATE AND P3.END_DATE
AND SYSDATE BETWEEN PAY.EFFECTIVE_START_DATE AND
PAY.EFFECTIVE_END_DATE
AND PAY.BANK_ACCOUNT_ID = BA.BANK_ACCOUNT_ID
AND P.PERSON_NUMBER IN('121')
)
)
order by no
