i make a report since 3 month it's running smoothly without any issue previous week
it running for the first time it's take only one minute if run it again it will not run after 3 days to get the output
the quarry for hrms and payroll information it's name Ministry of labor report
Attached the select statement
/* Formatted on 2014/02/27 13:34 (Formatter Plus v4.8.8) */
--this is the last quarry
SELECT 'IFH'
|| ','
|| 'IFILE'
|| ','
|| 'CSV'
|| ','
|| 'ABC50839001'
|| ','
|| 'SASABBGSA042411447'
|| ','
|| 'AYTB SABB '
|| ''
|| TO_CHAR (SYSDATE, 'YYYYMM')
|| ' '
|| xxaytb_mol_batchref_seq
|| ','
|| MIN
((SELECT CASE
WHEN TRIM (TO_CHAR (SYSDATE, 'DAY')) =
'SATURDAY'
THEN TO_CHAR (SYSDATE + 1, 'YYYY/MM/DD')
WHEN TRIM (TO_CHAR (SYSDATE, 'DAY')) =
'FRIDAY'
THEN TO_CHAR (SYSDATE + 2, 'YYYY/MM/DD')
WHEN TRIM (TO_CHAR (SYSDATE, 'DAY')) =
'THURSDAY'
THEN TO_CHAR (SYSDATE + 3, 'YYYY/MM/DD')
ELSE TO_CHAR (SYSDATE, 'YYYY/MM/DD')
END sdate
FROM DUAL)
)
|| ','
|| TO_CHAR (SYSDATE, 'HH:MM:SS')
|| ','
|| 'P'
|| ','
|| '1.0'
|| ','
|| TO_NUMBER (COUNT (employee_number) + 2)
FROM (SELECT acc_no,
REPLACE (SUBSTR (full_name, 1, 20), '-', ' ') full_name,
employee_number, swift_code, pay_amount,
national_identifier_sa_iqama,
(CASE
WHEN SUM (basic_salary) < 0
THEN 0
ELSE SUM (basic_salary)
END
) basic_salary,
(CASE
WHEN SUM (housing_allowance) < 0
THEN 0
ELSE SUM (housing_allowance)
END
) housing_allowance,
--MODIFIED Added
(CASE
WHEN SUM (tot_earn) < 0
THEN 0
ELSE SUM (tot_earn)
END
) tot_earn,
(CASE
WHEN SUM (tot_earn) < 0
THEN ABS (SUM (tot_earn))
ELSE 0
END
) tot_earn_d,
--MODIFIED Added
SUM (NVL (tot_dud, 0)) tot_dud,
(CASE
WHEN SUM (basic_salary) < 0
THEN ABS (SUM (basic_salary))
ELSE 0
END
) basic_salary_d,
(CASE
WHEN SUM (housing_allowance) < 0
THEN ABS (SUM (housing_allowance))
ELSE 0
END
) housing_allowance_d,
REPLACE ( 'SALARY PAYMENT FOR '
|| SUBSTR (&p_period_name, 3, 5),
'-',
NULL
)
|| ' PERIOD' pay /*,
pay_type*/
FROM (SELECT REPLACE (papf.full_name, ',', '') full_name,
-- DECODE (XPVM.CLASSIFICATION_ID,'104',XPVM.RESULT_VALUE)TOT_EARN,
DECODE (xpvm.classification_id,
'100', (TO_NUMBER (xpvm.result_value)),
--Involuntary Deductions
'99', (TO_NUMBER (xpvm.result_value)),
--Voluntary Deductions
'103', (TO_NUMBER (xpvm.result_value))
-- Statutory Deductions
) tot_dud,
NVL (DECODE (xpvm.element_cat_no,
3, xpvm.result_value
),
0
) housing_allowance,
NVL (DECODE (xpvm.element_cat_no,
1, xpvm.result_value
),
0
) basic_salary,
(SELECT SUM (NVL (xpvm1.result_value, 0))
FROM xxaytb_pay_values_v_mol xpvm1
WHERE 1 = 1
AND xpvm1.classification_id = '104'
AND xpvm1.element_name NOT IN
('Housing Allowance',
'Housing Allowance Arrears',
'Housing Advance Payment',
'SS Housing Pay',
'Housing Advance Recovery')
AND xpvm1.assignment_id = xpvm.assignment_id
AND xpvm1.assignment_id = paav.assignment_id
AND xpvm1.effective_date = paav.effective_date
AND xpvm1.assignment_id = paaf.assignment_id
AND xpvm1.attribute2 = xpvm.attribute2
AND xpvm1.action_type = 'R') tot_earn,
papf.employee_number employee_number,
TO_CHAR (e_acc.segment4) acc_no,
(SELECT meaning
FROM fnd_lookup_values
WHERE lookup_type = 'SA_BANKS'
AND LANGUAGE = USERENV ('LANG')
AND lookup_code = e_acc.segment1) bank_name,
TO_NUMBER (pppv3.VALUE) pay_amount,
(SELECT DECODE
(flv.meaning,
'ALBILAD BANK', 'ALBISARI',
'SAUDI BRITISH BANK', 'SABBSARI',
'ARAB NATIONAL BANK', 'ARNBSARI',
'SAMBA FINANCIAL GROUP', 'SAMBSARI',
'AL RAJHI BANK', 'RJHISARI',
'NATIONAL COMMERCIAL BANK', 'NCBKSAJE',
'ALINMA BANK', 'INMASARI',
'RIYAD BANK', 'RIBLSARI',
'SAUDI HOLLANDI BANK', 'AAALSARI',
'BANQUE SAUDI FRANS', 'BSFRSARI'
)
FROM fnd_lookup_values flv
WHERE flv.lookup_type = 'SA_BANKS'
AND LANGUAGE = USERENV ('LANG')
AND flv.lookup_code = e_acc.segment1) swift_code,
REPLACE
(NVL (ppei.pei_information1,
papf.national_identifier
),
'-',
''
) national_identifier_sa_iqama
FROM pay_assignment_actions_v paav,
pay_pre_payments_v3 pppv3,
pay_external_accounts e_acc,
pay_pre_payments_v ppav,
xxaytb_pay_values_v_mol xpvm,
per_all_assignments_f paaf,
per_all_people_f papf,
(SELECT *
FROM per_people_extra_info ppeix
WHERE ppeix.information_type = 'SA_IQAMA') ppei
WHERE 1 = 1
AND papf.employee_number NOT IN
('25772',
'25065',
'26756',
'21316',
'26745',
'25951',
'23441',
'24975',
'22954',
'23744',
'25851',
'26266',
'27420',
'26134',
'27325',
'25023',
'27291'
)
AND paav.assignment_action_id = ppav.assignment_action_id
AND ppav.pre_payment_id = pppv3.pre_payment_id
AND pppv3.external_account_id = e_acc.external_account_id
AND paav.business_group_id =
NVL (fnd_profile.VALUE ('PER_BUSINESS_GROUP_ID'),
81
)
AND ppav.org_payment_method_name LIKE 'SABB%'
AND paav.period_name = &p_period_name
--AND pact.effective_date=substr(&p_period_name, 15,11)
AND pppv3.ppm_effective_end_date >= paav.effective_date
--modified
AND paav.effective_date
BETWEEN pppv3.ppm_effective_start_date
AND pppv3.ppm_effective_end_date
--modified
----ADDED
AND UPPER (xpvm.attribute2) =
UPPER (NVL (:p_attr2, xpvm.attribute2))
AND papf.person_id = ppei.person_id(+)
AND papf.person_id = paaf.person_id
--MODIFIED
AND paav.effective_date BETWEEN papf.effective_start_date
AND papf.effective_end_date
--MODIFIED
AND papf.business_group_id = paav.business_group_id
AND paaf.assignment_id = paav.assignment_id
AND paaf.assignment_id = xpvm.assignment_id
--MODIFIED
AND paav.effective_date BETWEEN paaf.effective_start_date
AND paaf.effective_end_date
--MODIFIED
AND paav.business_group_id = paaf.business_group_id
AND xpvm.effective_date = paav.effective_date
AND xpvm.assignment_id = paav.assignment_id
AND xpvm.action_type = 'R')
GROUP BY acc_no,
bank_name,
pay_amount,
employee_number,
swift_code,
full_name,
national_identifier_sa_iqama /*,
tot_earn*/
/*,
pay_type*/)
UNION ALL
--BATHDE
SELECT 'BATHDR'
|| ','
|| 'ACH-CR'
|| ','
|| COUNT (*)
|| ','
|| ','
|| ','
|| ','
|| NVL (UPPER (:p_attr2), 'S')
|| ','
|| REPLACE ('PAYROLL FOR ' || SUBSTR (&p_period_name, 3, 5), '-', NULL)
|| ' PERIOD'
|| ','
|| ','
|| '@1ST@'
|| ','
|| MAX ((SELECT CASE
WHEN TO_CHAR (SYSDATE, 'HH24') <= 14
AND TRIM (TO_CHAR (SYSDATE, 'DAY')) = ('WEDNESDAY')
THEN TO_CHAR (SYSDATE + 1, 'YYYYMMDD')
WHEN TO_CHAR (SYSDATE, 'HH24') > 14
AND TRIM (TO_CHAR (SYSDATE, 'DAY')) = ('WEDNESDAY')
THEN TO_CHAR (SYSDATE + 4, 'YYYYMMDD')
WHEN TO_CHAR (SYSDATE, 'HH24') <= 14
AND TRIM (TO_CHAR (SYSDATE, 'DAY')) = ('THURSDAY')
THEN TO_CHAR (SYSDATE + 4, 'YYYYMMDD')
WHEN TO_CHAR (SYSDATE, 'HH24') > 14
AND TRIM (TO_CHAR (SYSDATE, 'DAY')) = ('THURSDAY')
THEN TO_CHAR (SYSDATE + 4, 'YYYYMMDD')
WHEN TRIM (TO_CHAR (SYSDATE, 'DAY')) = 'FRIDAY'
THEN TO_CHAR (SYSDATE + 3, 'YYYYMMDD')
WHEN TRIM (TO_CHAR (SYSDATE, 'DAY')) = 'SATURDAY'
THEN TO_CHAR (SYSDATE + 2, 'YYYYMMDD')
ELSE TO_CHAR (SYSDATE + 1, 'YYYYMMDD')
END
FROM DUAL)
)
--TO_CHAR(CASE when to_char(sysdate,'HH24') <13 THEN SYSDATE else sysdate+1 end)
|| ','
|| '042411447001'
|| ','
|| 'SAR'
|| ','
|| SUM (pay_amount)
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| 'AYTB KSA'
|| ','
|| '6-2566' --this is for Jubail --yanbu--2103-14--
|| ','
|| '2055001244'
|| ','
|| ','
|| ','
|| ','
|| 'AYTB MOL '
|| SUBSTR (&p_period_name, 1, 2)
|| ''
|| SUBSTR (&p_period_name, 4, 3)
--BATCH REF
--'AYTB'||' '||XXAYTB_MOL_BATCH_REF.NEXTVAL
FROM (SELECT acc_no,
REPLACE (SUBSTR (full_name, 1, 20), '-', ' ') full_name,
employee_number, swift_code, pay_amount,
national_identifier_sa_iqama,
(CASE
WHEN SUM (basic_salary) < 0
THEN 0
ELSE SUM (basic_salary)
END
) basic_salary,
(CASE
WHEN SUM (housing_allowance) < 0
THEN 0
ELSE SUM (housing_allowance)
END
) housing_allowance,
--MODIFIED Added
(CASE
WHEN SUM (tot_earn) < 0
THEN 0
ELSE SUM (tot_earn)
END
) tot_earn,
(CASE
WHEN SUM (tot_earn) < 0
THEN ABS (SUM (tot_earn))
ELSE 0
END
) tot_earn_d,
--MODIFIED Added
SUM (NVL (tot_dud, 0)) tot_dud,
(CASE
WHEN SUM (basic_salary) < 0
THEN ABS (SUM (basic_salary))
ELSE 0
END
) basic_salary_d,
(CASE
WHEN SUM (housing_allowance) < 0
THEN ABS (SUM (housing_allowance))
ELSE 0
END
) housing_allowance_d,
REPLACE ( 'SALARY PAYMENT FOR '
|| SUBSTR (&p_period_name, 3, 5),
'-',
NULL
)
|| ' PERIOD' pay /*,
pay_type*/
FROM (SELECT REPLACE (papf.full_name, ',', '') full_name,
-- DECODE (XPVM.CLASSIFICATION_ID,'104',XPVM.RESULT_VALUE)TOT_EARN,
DECODE (xpvm.classification_id,
'100', (TO_NUMBER (xpvm.result_value)),
--Involuntary Deductions
'99', (TO_NUMBER (xpvm.result_value)),
--Voluntary Deductions
'103', (TO_NUMBER (xpvm.result_value))
-- Statutory Deductions
) tot_dud,
NVL (DECODE (xpvm.element_cat_no,
3, xpvm.result_value
),
0
) housing_allowance,
NVL (DECODE (xpvm.element_cat_no,
1, xpvm.result_value
),
0
) basic_salary,
(SELECT SUM (NVL (xpvm1.result_value, 0))
FROM xxaytb_pay_values_v_mol xpvm1
WHERE 1 = 1
AND xpvm1.classification_id = '104'
AND xpvm1.element_name NOT IN
('Housing Allowance',
'Housing Allowance Arrears',
'Housing Advance Payment',
'SS Housing Pay',
'Housing Advance Recovery')
AND xpvm1.assignment_id = xpvm.assignment_id
AND xpvm1.assignment_id = paav.assignment_id
AND xpvm1.effective_date = paav.effective_date
AND xpvm1.assignment_id = paaf.assignment_id
AND xpvm1.attribute2 = xpvm.attribute2
AND xpvm1.action_type = 'R') tot_earn,
papf.employee_number employee_number,
TO_CHAR (e_acc.segment4) acc_no,
(SELECT meaning
FROM fnd_lookup_values
WHERE lookup_type = 'SA_BANKS'
AND LANGUAGE = USERENV ('LANG')
AND lookup_code = e_acc.segment1) bank_name,
TO_NUMBER (pppv3.VALUE) pay_amount,
(SELECT DECODE
(flv.meaning,
'ALBILAD BANK', 'ALBISARI',
'SAUDI BRITISH BANK', 'SABBSARI',
'ARAB NATIONAL BANK', 'ARNBSARI',
'SAMBA FINANCIAL GROUP', 'SAMBSARI',
'AL RAJHI BANK', 'RJHISARI',
'NATIONAL COMMERCIAL BANK', 'NCBKSAJE',
'ALINMA BANK', 'INMASARI',
'RIYAD BANK', 'RIBLSARI',
'SAUDI HOLLANDI BANK', 'AAALSARI',
'BANQUE SAUDI FRANS', 'BSFRSARI'
)
FROM fnd_lookup_values flv
WHERE flv.lookup_type = 'SA_BANKS'
AND LANGUAGE = USERENV ('LANG')
AND flv.lookup_code = e_acc.segment1) swift_code,
REPLACE
(NVL (ppei.pei_information1,
papf.national_identifier
),
'-',
''
) national_identifier_sa_iqama
FROM pay_assignment_actions_v paav,
pay_pre_payments_v3 pppv3,
pay_external_accounts e_acc,
pay_pre_payments_v ppav,
xxaytb_pay_values_v_mol xpvm,
per_all_assignments_f paaf,
per_all_people_f papf,
(SELECT *
FROM per_people_extra_info ppeix
WHERE ppeix.information_type = 'SA_IQAMA') ppei
WHERE 1 = 1
AND papf.employee_number NOT IN
('25772',
'25065',
'26756',
'21316',
'26745',
'25951',
'23441',
'24975',
'22954',
'23744',
'25851',
'26266',
'27420',
'26134',
'27325',
'25023',
'27291'
)
AND paav.assignment_action_id = ppav.assignment_action_id
AND ppav.pre_payment_id = pppv3.pre_payment_id
AND pppv3.external_account_id = e_acc.external_account_id
AND paav.business_group_id =
NVL (fnd_profile.VALUE ('PER_BUSINESS_GROUP_ID'),
81
)
AND ppav.org_payment_method_name LIKE 'SABB%'
AND paav.period_name = &p_period_name
--AND pact.effective_date=substr(&p_period_name, 15,11)
AND pppv3.ppm_effective_end_date >= paav.effective_date
--modified
AND paav.effective_date
BETWEEN pppv3.ppm_effective_start_date
AND pppv3.ppm_effective_end_date
--modified
----ADDED
AND UPPER (xpvm.attribute2) =
UPPER (NVL (:p_attr2, xpvm.attribute2))
AND papf.person_id = ppei.person_id(+)
AND papf.person_id = paaf.person_id
--MODIFIED
AND paav.effective_date BETWEEN papf.effective_start_date
AND papf.effective_end_date
--MODIFIED
AND papf.business_group_id = paav.business_group_id
AND paaf.assignment_id = paav.assignment_id
AND paaf.assignment_id = xpvm.assignment_id
--MODIFIED
AND paav.effective_date BETWEEN paaf.effective_start_date
AND paaf.effective_end_date
--MODIFIED
AND paav.business_group_id = paaf.business_group_id
AND xpvm.effective_date = paav.effective_date
AND xpvm.assignment_id = paav.assignment_id
AND xpvm.action_type = 'R')
GROUP BY acc_no,
bank_name,
pay_amount,
employee_number,
swift_code,
full_name,
national_identifier_sa_iqama /*,
tot_earn*/
/*,
pay_type*/)
UNION ALL
SELECT 'SECPTY'
|| ','
|| acc_no
|| ','
|| REPLACE (full_name, q'[']', NULL)
|| ','
|| employee_number
|| ','
|| swift_code
|| ','
|| ','
|| ','
|| pay_amount
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| 'N'
|| ','
|| 'N'
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| '@SACH@'
|| ','
|| national_identifier_sa_iqama
|| ','
|| basic_salary
|| ','
|| housing_allowance
|| ','
|| tot_earn
|| ','
|| TO_NUMBER (tot_dud + housing_allowance_d + basic_salary_d
+ tot_earn_d
)
|| ','
|| pay
FROM (SELECT acc_no,
REPLACE (SUBSTR (full_name, 1, 20), '-', ' ') full_name,
employee_number, swift_code, pay_amount,
national_identifier_sa_iqama,
(CASE
WHEN SUM (basic_salary) < 0
THEN 0
ELSE SUM (basic_salary)
END
) basic_salary,
(CASE
WHEN SUM (housing_allowance) < 0
THEN 0
ELSE SUM (housing_allowance)
END
) housing_allowance,
--MODIFIED Added
(CASE
WHEN SUM (tot_earn) < 0
THEN 0
ELSE SUM (tot_earn)
END
) tot_earn,
(CASE
WHEN SUM (tot_earn) < 0
THEN ABS (SUM (tot_earn))
ELSE 0
END
) tot_earn_d,
--MODIFIED Added
(CASE
WHEN SUM (basic_salary) < 0
THEN ABS (SUM (basic_salary))
ELSE 0
END
) basic_salary_d,
(CASE
WHEN SUM (housing_allowance) < 0
THEN ABS (SUM (housing_allowance))
ELSE 0
END
) housing_allowance_d,
REPLACE ( 'SALARY PAYMENT FOR '
|| SUBSTR (&p_period_name, 3, 5),
'-',
NULL
)
|| ' PERIOD' pay,
SUM (NVL (tot_dud, 0)) tot_dud
-- ,
--
--
-- (CASE
-- WHEN SUM (tot_dud) < 0
-- THEN 0
-- ELSE SUM (tot_dud)
-- END
-- ) tot_dud
--
-- (CASE
-- WHEN SUM (tot_dud) < 0
-- THEN ABS (SUM (tot_dud))
-- ELSE 0
-- END
-- ) tot_dud_d
--
FROM (SELECT REPLACE (papf.full_name, ',', '') full_name,
-- DECODE (XPVM.CLASSIFICATION_ID,'104',XPVM.RESULT_VALUE)TOT_EARN,
DECODE (xpvm.classification_id,
'100', (TO_NUMBER (xpvm.result_value)),
--Involuntary Deductions
'99', (TO_NUMBER (xpvm.result_value)),
--Voluntary Deductions
'103', (TO_NUMBER (xpvm.result_value))
-- Statutory Deductions
) tot_dud,
NVL (DECODE (xpvm.element_cat_no,
3, xpvm.result_value
),
0
) housing_allowance,
NVL (DECODE (xpvm.element_cat_no,
1, xpvm.result_value
),
0
) basic_salary,
(SELECT SUM (NVL (xpvm1.result_value, 0))
FROM xxaytb_pay_values_v_mol xpvm1
WHERE 1 = 1
AND xpvm1.classification_id = '104'
AND xpvm1.element_name NOT IN
('Housing Allowance',
'Housing Allowance Arrears',
'Housing Advance Payment',
'SS Housing Pay',
'Housing Advance Recovery')
AND xpvm1.assignment_id = xpvm.assignment_id
AND xpvm1.assignment_id = paav.assignment_id
AND xpvm1.effective_date = paav.effective_date
AND xpvm1.assignment_id = paaf.assignment_id
AND xpvm1.attribute2 = xpvm.attribute2
AND xpvm1.action_type = 'R') tot_earn,
papf.employee_number employee_number,
TO_CHAR (e_acc.segment4) acc_no,
(SELECT meaning
FROM fnd_lookup_values
WHERE lookup_type = 'SA_BANKS'
AND LANGUAGE = USERENV ('LANG')
AND lookup_code = e_acc.segment1) bank_name,
TO_NUMBER (pppv3.VALUE) pay_amount,
(SELECT DECODE
(flv.meaning,
'ALBILAD BANK', 'ALBISARI',
'SAUDI BRITISH BANK', 'SABBSARI',
'ARAB NATIONAL BANK', 'ARNBSARI',
'SAMBA FINANCIAL GROUP', 'SAMBSARI',
'AL RAJHI BANK', 'RJHISARI',
'NATIONAL COMMERCIAL BANK', 'NCBKSAJE',
'ALINMA BANK', 'INMASARI',
'RIYAD BANK', 'RIBLSARI',
'SAUDI HOLLANDI BANK', 'AAALSARI',
'BANQUE SAUDI FRANS', 'BSFRSARI'
)
FROM fnd_lookup_values flv
WHERE flv.lookup_type = 'SA_BANKS'
AND LANGUAGE = USERENV ('LANG')
AND flv.lookup_code = e_acc.segment1) swift_code,
REPLACE
(NVL (ppei.pei_information1,
papf.national_identifier
),
'-',
''
) national_identifier_sa_iqama
FROM pay_assignment_actions_v paav,
pay_pre_payments_v3 pppv3,
pay_external_accounts e_acc,
pay_pre_payments_v ppav,
xxaytb_pay_values_v_mol xpvm,
per_all_assignments_f paaf,
per_all_people_f papf,
(SELECT *
FROM per_people_extra_info ppeix
WHERE ppeix.information_type = 'SA_IQAMA') ppei
WHERE 1 = 1
AND papf.employee_number NOT IN
('25772',
'25065',
'26756',
'21316',
'26745',
'25951',
'23441',
'24975',
'22954',
'23744',
'25851',
'26266',
'27420',
'26134',
'27325',
'25023',
'27291'
)
AND paav.assignment_action_id = ppav.assignment_action_id
AND ppav.pre_payment_id = pppv3.pre_payment_id
AND pppv3.external_account_id = e_acc.external_account_id
AND paav.business_group_id =
NVL (fnd_profile.VALUE ('PER_BUSINESS_GROUP_ID'),
81
)
AND ppav.org_payment_method_name LIKE 'SABB%'
AND paav.period_name = &p_period_name
--AND pact.effective_date=substr(&p_period_name, 15,11)
AND pppv3.ppm_effective_end_date >= paav.effective_date
--modified
AND paav.effective_date
BETWEEN pppv3.ppm_effective_start_date
AND pppv3.ppm_effective_end_date
--modified
----ADDED
AND UPPER (xpvm.attribute2) =
UPPER (NVL (:p_attr2, xpvm.attribute2))
AND papf.person_id = ppei.person_id(+)
AND papf.person_id = paaf.person_id
--MODIFIED
AND paav.effective_date BETWEEN papf.effective_start_date
AND papf.effective_end_date
--MODIFIED
AND papf.business_group_id = paav.business_group_id
AND paaf.assignment_id = paav.assignment_id
AND paaf.assignment_id = xpvm.assignment_id
--MODIFIED
AND paav.effective_date BETWEEN paaf.effective_start_date
AND paaf.effective_end_date
--MODIFIED
AND paav.business_group_id = paaf.business_group_id
AND xpvm.effective_date = paav.effective_date
AND xpvm.assignment_id = paav.assignment_id
AND xpvm.action_type = 'R')
GROUP BY acc_no,
bank_name,
pay_amount,
employee_number,
swift_code,
full_name,
national_identifier_sa_iqama /*,
tot_earn*/
/*,
pay_type*/)