HDL to delete Personal payment Method and Bank Accounts

To delete the bank accounts:  we have to delete the PPM and then Bank accounts.

HDL Script to delete Personal Payment Methods

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('1234')

)

)

order by no

 

HDL to Delete External Bank Accounts 

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('1234')

)

)

order by no