SELECT DISTINCT QRY.*,
( CASE
WHEN QRY.total_deduc < 0 THEN Nvl (QRY.total_earning, 0)
+ Nvl (QRY.total_deduc, 0)
ELSE Nvl (QRY.total_earning, 0) - Nvl (QRY.total_deduc, 0)
END ) NET,
( QRY.p_end_date - p_strt_date + 1 ) --MONTH_DAYS
- (SELECT Nvl(SUM (Least (p_end_date, VAC.end_date) -
Greatest (p_strt_date,
VAC.start_date)), 0) VAC_DYS
FROM anc_per_abs_entries VAC
WHERE VAC.person_id = QRY.p_person_id
AND VAC.approval_status_cd NOT IN ( 'DENIED' )
AND VAC.absence_status_cd = 'SUBMITTED'
AND ( To_char (QRY.p_strt_date, 'MM') BETWEEN
To_char (VAC. start_date, 'MM') AND
To_char (VAC.end_date, 'MM') )) WRK_DAYS
FROM (SELECT PAPF.person_number AS PERSON_NUMBER,
PAPF.person_id P_PERSON_ID,
Substr (PTP.period_name, 1, 7) AS PERIOD_NAME,
PAYRO.payroll_name AS PAYROLL_NAME,
LOOKUPN.meaning AS NATIONALITY,
Initcap (PPN.list_name) AS PERSON_FULL_NAME,
PPN_AR.display_name PER_NAME_AR,
-- LOOKUPN.MEANING AS NATIONALITY,
POS.name AS POSITION,
-- TO_CHAR (PAPF.START_DATE, 'DD/MM/YYYY') AS JOINING_DATE,
HAOUFVD.name AS DEPT_CODE,
PAAM.ass_attribute1 DIVISION,
PTP.end_date P_END_DATE,
PTP.start_date P_STRT_DATE,
--------------------
--BANK DETAILS--
----------------
BANK_DTLS.pay_method AS PAY_METHOD,
BANK_DTLS.bank_name AS BANK_NAME,
------------------------------
grade.name grade_name,
-- grade_code.grade_code,
loc.location_name,
( CASE
WHEN PET.reporting_name = 'SA_EE_GOSI_UNEMPLOYMENT_INSURANCE'
OR PET.reporting_name = 'Employee GOSI Annuities'
OR PET.reporting_name = 'Employee GOSI Saned' THEN
'GOSI Deduction'
/*'Employee GOSI Saned' changed by Hamdy*/
WHEN PET.reporting_name = 'Sick Leave Entitlement Result'
THEN
'Sick Leave Deduction'
ELSE PET.reporting_name
END ) AS EARN_NAME,
SUM (PRRV.result_value) EARN_VAL,
( CASE
WHEN PRIM.classification_name = ( 'Standard Earnings' ) THEN
(
CASE
WHEN PET.reporting_name = 'Basic Salary' THEN 1
WHEN PET.reporting_name = 'Housing Allowance' THEN 2
WHEN PET.reporting_name = 'Transportation Allowance' THEN 3
WHEN PET.reporting_name = 'Mobile Allowance' THEN 4
WHEN PET.reporting_name = 'Nature Allowance' THEN 5
ELSE 20
END )
WHEN PRIM.classification_name IN (
'Voluntary Deductions', 'Social Insurance Deductions',
'Absences' )
THEN
( CASE
WHEN PET.reporting_name IN
( 'GOSI', 'SA_EE_GOSI_UNEMPLOYMENT_INSURANCE',
'Employee GOSI Annuities' ) THEN 22
ELSE 23
END )
END ) ORDERING,
(SELECT SUM (Q.earn_val)
FROM (SELECT PAPF.person_number AS PERSON_NUMBER,
Substr (PTP.period_name, 1, 7) AS PERIOD_NAME,
PAYRO.payroll_name AS PAYROLL_NAME,
LOOKUPN.meaning AS NATIONALITY,
Initcap (PPN.list_name) AS
PERSON_FULL_NAME,
PPN_AR.display_name PER_NAME_AR,
-- LOOKUPN.MEANING AS NATIONALITY,
POS.name AS POSITION,
-- TO_CHAR (PAPF.START_DATE, 'DD/MM/YYYY') AS JOINING_DATE,
HAOUFVD.name AS DEPT_CODE,
PAAM.ass_attribute1 DIVISION,
-- (PTP.END_DATE - PTP.START_DATE) + 1 DY_IN_MTH,
--------------------
--BANK DETAILS--
----------------
BANK_DTLS.pay_method AS PAY_METHOD,
BANK_DTLS.bank_name AS BANK_NAME,
------------------------------
grade.name grade_name,
-- grade_code.grade_code,
loc.location_name,
( CASE
WHEN PET.reporting_name =
'SA_EE_GOSI_UNEMPLOYMENT_INSURANCE'
OR PET.reporting_name =
'Employee GOSI Annuities'
OR PET.reporting_name =
'Employee GOSI Saned'
THEN
'GOSI Deduction'
/*'Employee GOSI Saned' changed by Hamdy*/
WHEN PET.reporting_name =
'Sick Leave Entitlement Result'
THEN
'Sick Leave Deduction'
ELSE PET.reporting_name
END ) AS EARN_NAME,
SUM (PRRV.result_value) EARN_VAL,
( CASE
WHEN PRIM.classification_name = (
'Standard Earnings' ) THEN
(
CASE
WHEN PET.reporting_name = 'Basic Salary'
THEN 1
WHEN PET.reporting_name =
'Housing Allowance' THEN
2
WHEN PET.reporting_name =
'Transportation Allowance' THEN
3
WHEN PET.reporting_name =
'Mobile Allowance' THEN
4
WHEN PET.reporting_name =
'Nature Allowance' THEN
5
ELSE 20
END )
WHEN PRIM.classification_name IN (
'Voluntary Deductions',
'Social Insurance Deductions',
'Absences' )
THEN
( CASE
WHEN PET.reporting_name IN
( 'GOSI', 'SA_EE_GOSI_UNEMPLOYMENT_INSURANCE',
'Employee GOSI Annuities' ) THEN 22
ELSE 23
END )
END ) ORDERING,
--------------------------------------------------
---------------------
--PARAMETERS--
---------------------
Nvl(:P_NUM_P, 'All') P_NUM_P,
-- :P_PAY_NAME,
:P_PERIOD_NAME,
Nvl(:P_DEPT, 'All') P_DEPT,
Nvl(:P_LOC, 'All') P_LOC,
Nvl(:P_GRADE, 'All') P_GRADE,
Nvl(:P_NATION, 'All') P_NATION,
Nvl(:P_SPONSER, 'All') P_SPONSER,
Nvl(:ORG_PAY_MTHD, 'All') ORG_PAY_MTHD,
Nvl(:P_DIVISION, 'All') P_DIVISION
FROM per_all_people_f PAPF,
per_citizenships PC,
hr_lookups LOOKUPN,
per_all_assignments_m PAAM,
per_periods_of_service PPOS,
per_person_names_f PPN,
per_person_names_f PPN_AR,
pay_all_payrolls_f PAYRO,
pay_payroll_actions PPA,
pay_payroll_rel_actions PPRA,
pay_pay_relationships_dn PPRD,
pay_relationship_types PRT,
pay_run_results PRR,
pay_element_types_vl PET,
pay_run_result_values PRRV,
pay_input_values_f PIVF,
pay_time_periods PTP,
pay_flow_instances PFI,
pay_requests REQ,
pay_rel_groups_dn ASG,
-- PER_LOCATION_DETAILS_F_VL PLDFV,
hr_org_unit_classifications_f HOUCFD,
hr_all_organization_units_f_vl HAOUFVD,
cmp_salary SAL,
per_people_groups PPG,
-- PAY_ELE_CLASSIFICATIONS_VL CLASSIF,
pay_ele_classifications_vl PRIM,
hr_all_positions POS,
per_grades_f_tl grade,
(SELECT lo.location_id,
l.location_name,
lo.internal_location_code LOCATION_CODE
FROM hr_locations_all lo,
hr_locations_all_tl l
WHERE l.location_details_id =
lo.location_details_id
AND l.LANGUAGE = 'US') loc,
(SELECT PBA.bank_account_name,
PBA.bank_name,
PBA.bank_branch_name,
PBA.bank_account_num,
PBA.iban_number,
Nvl (PBA.bank_code, PBA.bank_number)
BANK_CODE,
POPF.currency_code,
POPF.base_org_pay_method_name,
PPPMF.payroll_relationship_id,
-- PPTT.PAYMENT_TYPE_NAME,
-- PPTT.PAYMENT_TYPE_ID,
Nvl (PPPMF.effective_start_date, SYSDATE)
EFFECTIVE_START_DATE,
Nvl (PPPMF.effective_end_date, SYSDATE)
EFFECTIVE_END_DATE,
PPPMF.name
PAY_METHOD
FROM pay_bank_accounts PBA,
pay_person_pay_methods_f PPPMF,
pay_org_pay_methods_f POPF
WHERE PPPMF.bank_account_id =
PBA.bank_account_id(+)
AND PPPMF.org_payment_method_id(+) =
POPF.org_payment_method_id
-- AND POPF.language(+) = 'US'
-- AND POPF.PAYMENT_TYPE_ID = PPTT.PAYMENT_TYPE_ID
-- AND PPTT.LANGUAGE = USERENV ('LANG')
AND Trunc (SYSDATE) BETWEEN PPPMF.
effective_start_date
AND PPPMF.
effective_end_date
AND Trunc (SYSDATE) BETWEEN POPF.
effective_start_date
AND POPF.
effective_end_date
AND Trunc (SYSDATE) BETWEEN Nvl (
Trunc (PBA. start_date),
Trunc (
SYSDATE)
) AND
Nvl (
Trunc (PBA. end_date), Trunc
(
SYSDATE))
)
BANK_DTLS
WHERE PAPF.person_id = PPN.person_id
AND PAPF.person_id = PPN_AR.person_id
AND PET.classification_id =
PRIM.base_classification_id
AND LOOKUPN.lookup_code(+) = PC.legislation_code
AND PC.person_id(+) = PAPF.person_id
AND PAAM.person_id = PAPF.person_id
AND PPOS.person_id = PAPF.person_id
AND PPOS.period_of_service_id =
PAAM.period_of_service_id
AND PPRD.person_id = PAPF.person_id
AND PPRD.payroll_relationship_id =
PPRA.payroll_relationship_id
AND PRT.relationship_type_id =
PPRD.relationship_type_id
AND PRR.payroll_rel_action_id =
PPRA.payroll_rel_action_id
AND PPRA.payroll_action_id =
PPA.payroll_action_id
AND PAYRO.payroll_id = PPA.payroll_id
AND PTP.payroll_id = PPA.payroll_id
AND PRR.element_type_id = PET.element_type_id
AND PRRV.run_result_id = PRR.run_result_id
AND PRRV.input_value_id = PIVF.input_value_id
AND PIVF.element_type_id = PET.element_type_id
AND PPA.earn_time_period_id = PTP.time_period_id
AND PFI.flow_instance_id = REQ.flow_instance_id
AND REQ.pay_request_id = PPA.pay_request_id(+)
AND PAAM.assignment_id = ASG.assignment_id(+)
--AND PAAM.LOCATION_ID = PLDFV.LOCATION_ID(+)
AND HAOUFVD.organization_id(+) =
PAAM.organization_id
AND HAOUFVD.organization_id =
HOUCFD.organization_id(+)
AND SAL.assignment_id(+) = PAAM.assignment_id
AND PAPF.person_id = SAL.person_id(+)
AND PAAM.people_group_id = PPG.people_group_id(+)
AND PPRD.payroll_relationship_id =
BANK_DTLS.payroll_relationship_id(+)
AND POS.position_id(+) = PAAM.position_id
------------------------------------------------
AND PPN.name_type = 'GLOBAL'
AND PPN_AR.name_type(+) = 'SA'
AND PAAM.assignment_type = 'E'
AND PAAM.primary_flag = 'Y'
AND PIVF.base_name IN ( 'Pay Value', 'Net Pay' )
AND PPA.action_type IN ( 'Q', 'R' )
AND PAAM.effective_latest_change = 'Y'
AND LOOKUPN.lookup_type(+) = 'NATIONALITY'
-------------------------------------------
AND paam.location_id = loc.location_id(+)
AND paam.grade_id = grade.grade_id(+)
AND grade.LANGUAGE(+) = 'US'
------------------------------------------------------------
AND ( PRIM.classification_name IN (
'Standard Earnings', 'Voluntary Deductions'
,
'Social Insurance Deductions',
'Absences'
) )
AND PRIM.legislation_code = 'SA'
-- AND PET.REPORTING_NAME NOT IN ('Basic Salary')
-------------------------------------------------
AND ( PET.reporting_name NOT IN
(
'Salary Advance Payment',
'Dependant fees loan Payment',
'EOS Loan Payment',
'Car Loan Payment'
)
)
AND Trunc (SYSDATE) BETWEEN PPN.
effective_start_date AND
PPN.
effective_end_date
AND Trunc (SYSDATE) BETWEEN PPN_AR.
effective_start_date
(+) AND
PPN_AR.
effective_end_date(+)
AND Trunc (SYSDATE) BETWEEN PAPF.
effective_start_date
AND PAPF.
effective_end_date
AND Trunc (SYSDATE) BETWEEN POS.
effective_start_date(+)
AND
POS.
effective_end_date(+)
AND PPA.effective_date BETWEEN
PTP.start_date AND PTP.end_date
AND PTP.start_date BETWEEN PET.
effective_start_date AND
PET.
effective_end_date
AND PTP.start_date BETWEEN PIVF.
effective_start_date AND
PIVF.
effective_end_date
AND Trunc (SYSDATE) BETWEEN PET.
effective_start_date AND
PET.
effective_end_date
-- AND TRUNC(SYSDATE) BETWEEN SAL.DATE_FROM AND SAL.DATE_TO
-- AND NVL(PPOS.ACTUAL_TERMINATION_DATE,PTP.END_DATE) BETWEEN trunc(SAL.DATE_FROM) - (to_number(to_char(SAL.DATE_FROM,'DD')) - 1) AND LAST_DAY(SAL.DATE_TO)
AND ( Nvl (PPOS.actual_termination_date,
PTP.end_date) >
Trunc (SAL.date_from) - ( To_number (
To_char (SAL.date_from,
'DD'))
- 1
)
AND Nvl (PPOS.actual_termination_date,
PTP.end_date) <
Last_day (SAL.date_to +
1) )
AND Trunc (SYSDATE) BETWEEN Nvl (
Trunc (HOUCFD. effective_start_date),
Trunc (SYSDATE)) AND
Nvl (
Trunc (HOUCFD. effective_end_date),
Trunc (
SYSDATE))
AND Trunc (SYSDATE) BETWEEN Nvl (
Trunc (HAOUFVD. effective_start_date),
Trunc (
SYSDATE)) AND
Nvl (
Trunc (HAOUFVD. effective_end_date),
Trunc (
SYSDATE))
AND ( PTP.end_date BETWEEN Nvl (Trunc (
BANK_DTLS. effective_start_date),
Trunc (SYSDATE)) AND
Nvl (Trunc (
BANK_DTLS. effective_end_date),
Trunc (
SYSDATE))
OR Trunc (SYSDATE) BETWEEN Nvl (
Trunc (
BANK_DTLS. effective_start_date
)
,
Trunc
(
SYSDATE)) AND
Nvl (Trunc (
BANK_DTLS. effective_end_date),
Trunc
(
SYSDATE)) )
AND Trunc (SYSDATE) BETWEEN grade.
effective_start_date
(+) AND
grade.
effective_end_date(+)
AND SYSDATE BETWEEN PAAM.effective_start_date AND
PAAM.effective_end_date
--------------------
--PARAMETERS--
--------------
AND ( PAPF.person_number = :P_NUM_P
OR :P_NUM_P IS NULL )
AND ( Coalesce (NULL, :P_PERIOD_NAME) IS NULL
OR ( period_name IN ( :P_PERIOD_NAME ) ) )
AND ( PAAM.ass_attribute2 = :P_SPONSER
OR :P_SPONSER IS NULL )
AND ( LOOKUPN.meaning = :P_NATION
OR :P_NATION IS NULL )
AND ( HAOUFVD.name = :P_DEPT
OR :P_DEPT IS NULL )
AND ( grade.name = :P_GRADE
OR :P_GRADE IS NULL )
AND ( LOC.location_name = :P_LOC
OR :P_LOC IS NULL )
AND ( PAAM.ass_attribute1 = :P_DIVISION
OR :P_DIVISION IS NULL )
AND ( BANK_DTLS.base_org_pay_method_name =
:ORG_PAY_MTHD
OR :ORG_PAY_MTHD IS NULL )
AND ( PAYRO.payroll_name = :P_PAYROLL_NAME
OR :P_PAYROLL_NAME IS NULL )
GROUP BY PAPF.person_number,
Substr (PTP.period_name, 1, 7),
PAYRO.payroll_name,
LOOKUPN.meaning,
Initcap (PPN.list_name),
PPN_AR.display_name,
-- LOOKUPN.MEANING AS NATIONALITY,
POS.name,
-- TO_CHAR (PAPF.START_DATE, 'DD/MM/YYYY') AS JOINING_DATE,
HAOUFVD.name,
PAAM.ass_attribute1,
--------------------
--BANK DETAILS--
----------------
BANK_DTLS.pay_method,
BANK_DTLS.bank_name,
------------------------------
grade.name,
-- grade_code.grade_code,
loc.location_name,
( CASE
WHEN PET.reporting_name =
'SA_EE_GOSI_UNEMPLOYMENT_INSURANCE'
OR PET.reporting_name =
'Employee GOSI Annuities'
OR PET.reporting_name =
'Employee GOSI Saned' THEN
'GOSI Deduction'
/*'Employee GOSI Saned' changed by Hamdy*/
WHEN PET.reporting_name =
'Sick Leave Entitlement Result'
THEN
'Sick Leave Deduction'
ELSE PET.reporting_name
END ),
( CASE
WHEN PRIM.classification_name = (
'Standard Earnings' )
THEN (
CASE
WHEN PET.reporting_name = 'Basic Salary'
THEN 1
WHEN PET.reporting_name =
'Housing Allowance'
THEN 2
WHEN PET.reporting_name =
'Transportation Allowance'
THEN 3
WHEN PET.reporting_name =
'Mobile Allowance'
THEN 4
WHEN PET.reporting_name =
'Nature Allowance'
THEN 5
ELSE 20
END )
WHEN PRIM.classification_name IN (
'Voluntary Deductions',
'Social Insurance Deductions',
'Absences' )
THEN
( CASE
WHEN PET.reporting_name IN
( 'GOSI', 'SA_EE_GOSI_UNEMPLOYMENT_INSURANCE',
'Employee GOSI Annuities' ) THEN 22
ELSE 23
END )
END )
ORDER BY ordering) Q
WHERE PAPF.person_number = Q.person_number
AND Q.ordering <= 20) TOTAL_EARNING,
--------------------------------------------------
(SELECT SUM (Q2.earn_val)
FROM (SELECT PAPF.person_number AS PERSON_NUMBER,
Substr (PTP.period_name, 1, 7) AS PERIOD_NAME,
PAYRO.payroll_name AS PAYROLL_NAME,
LOOKUPN.meaning AS NATIONALITY,
Initcap (PPN.list_name) AS
PERSON_FULL_NAME,
PPN_AR.display_name PER_NAME_AR,
-- LOOKUPN.MEANING AS NATIONALITY,
POS.name AS POSITION,
-- TO_CHAR (PAPF.START_DATE, 'DD/MM/YYYY') AS JOINING_DATE,
HAOUFVD.name AS DEPT_CODE,
PAAM.ass_attribute1 DIVISION,
-- (PTP.END_DATE - PTP.START_DATE) + 1 DY_IN_MTH,
--------------------
--BANK DETAILS--
----------------
BANK_DTLS.pay_method AS PAY_METHOD,
BANK_DTLS.bank_name AS BANK_NAME,
------------------------------
grade.name grade_name,
-- grade_code.grade_code,
loc.location_name,
( CASE
WHEN PET.reporting_name =
'SA_EE_GOSI_UNEMPLOYMENT_INSURANCE'
OR PET.reporting_name =
'Employee GOSI Annuities'
OR PET.reporting_name =
'Employee GOSI Saned'
THEN
'GOSI Deduction'
/*'Employee GOSI Saned' changed by Hamdy*/
WHEN PET.reporting_name =
'Sick Leave Entitlement Result'
THEN
'Sick Leave Deduction'
ELSE PET.reporting_name
END ) AS EARN_NAME,
SUM (PRRV.result_value) EARN_VAL,
( CASE
WHEN PRIM.classification_name = (
'Standard Earnings' ) THEN
(
CASE
WHEN PET.reporting_name = 'Basic Salary'
THEN 1
WHEN PET.reporting_name =
'Housing Allowance' THEN
2
WHEN PET.reporting_name =
'Transportation Allowance' THEN
3
WHEN PET.reporting_name =
'Mobile Allowance' THEN
4
WHEN PET.reporting_name =
'Nature Allowance' THEN
5
ELSE 20
END )
WHEN PRIM.classification_name IN (
'Voluntary Deductions',
'Social Insurance Deductions',
'Absences' )
THEN
( CASE
WHEN PET.reporting_name IN
( 'GOSI', 'SA_EE_GOSI_UNEMPLOYMENT_INSURANCE',
'Employee GOSI Annuities' ) THEN 22
ELSE 23
END )
END ) ORDERING,
--------------------------------------------------
---------------------
--PARAMETERS--
---------------------
Nvl(:P_NUM_P, 'All') P_NUM_P,
-- :P_PAY_NAME,
:P_PERIOD_NAME,
Nvl(:P_DEPT, 'All') P_DEPT,
Nvl(:P_LOC, 'All') P_LOC,
Nvl(:P_GRADE, 'All') P_GRADE,
Nvl(:P_NATION, 'All') P_NATION,
Nvl(:P_SPONSER, 'All') P_SPONSER,
Nvl(:ORG_PAY_MTHD, 'All') ORG_PAY_MTHD,
Nvl(:P_DIVISION, 'All') P_DIVISION
FROM per_all_people_f PAPF,
per_citizenships PC,
hr_lookups LOOKUPN,
per_all_assignments_m PAAM,
per_periods_of_service PPOS,
per_person_names_f PPN,
per_person_names_f PPN_AR,
pay_all_payrolls_f PAYRO,
pay_payroll_actions PPA,
pay_payroll_rel_actions PPRA,
pay_pay_relationships_dn PPRD,
pay_relationship_types PRT,
pay_run_results PRR,
pay_element_types_vl PET,
pay_run_result_values PRRV,
pay_input_values_f PIVF,
pay_time_periods PTP,
pay_flow_instances PFI,
pay_requests REQ,
pay_rel_groups_dn ASG,
-- PER_LOCATION_DETAILS_F_VL PLDFV,
hr_org_unit_classifications_f HOUCFD,
hr_all_organization_units_f_vl HAOUFVD,
cmp_salary SAL,
per_people_groups PPG,
-- PAY_ELE_CLASSIFICATIONS_VL CLASSIF,
pay_ele_classifications_vl PRIM,
hr_all_positions POS,
per_grades_f_tl grade,
(SELECT lo.location_id,
l.location_name,
lo.internal_location_code LOCATION_CODE
FROM hr_locations_all lo,
hr_locations_all_tl l
WHERE l.location_details_id =
lo.location_details_id
AND l.LANGUAGE = 'US') loc,
(SELECT PBA.bank_account_name,
PBA.bank_name,
PBA.bank_branch_name,
PBA.bank_account_num,
PBA.iban_number,
Nvl (PBA.bank_code, PBA.bank_number)
BANK_CODE,
POPF.currency_code,
POPF.base_org_pay_method_name,
PPPMF.payroll_relationship_id,
-- PPTT.PAYMENT_TYPE_NAME,
-- PPTT.PAYMENT_TYPE_ID,
Nvl (PPPMF.effective_start_date, SYSDATE)
EFFECTIVE_START_DATE,
Nvl (PPPMF.effective_end_date, SYSDATE)
EFFECTIVE_END_DATE,
PPPMF.name
PAY_METHOD
FROM pay_bank_accounts PBA,
pay_person_pay_methods_f PPPMF,
pay_org_pay_methods_f POPF
WHERE PPPMF.bank_account_id =
PBA.bank_account_id(+)
AND PPPMF.org_payment_method_id(+) =
POPF.org_payment_method_id
-- AND POPF.language(+) = 'US'
-- AND POPF.PAYMENT_TYPE_ID = PPTT.PAYMENT_TYPE_ID
-- AND PPTT.LANGUAGE = USERENV ('LANG')
AND Trunc (SYSDATE) BETWEEN PPPMF.
effective_start_date
AND PPPMF.
effective_end_date
AND Trunc (SYSDATE) BETWEEN POPF.
effective_start_date
AND POPF.
effective_end_date
AND Trunc (SYSDATE) BETWEEN Nvl (
Trunc (PBA. start_date),
Trunc (
SYSDATE)
) AND
Nvl (
Trunc (PBA. end_date), Trunc
(
SYSDATE))
)
BANK_DTLS
WHERE PAPF.person_id = PPN.person_id
AND PAPF.person_id = PPN_AR.person_id
AND PET.classification_id =
PRIM.base_classification_id
AND LOOKUPN.lookup_code(+) = PC.legislation_code
AND PC.person_id(+) = PAPF.person_id
AND PAAM.person_id = PAPF.person_id
AND PPOS.person_id = PAPF.person_id
AND PPOS.period_of_service_id =
PAAM.period_of_service_id
AND PPRD.person_id = PAPF.person_id
AND PPRD.payroll_relationship_id =
PPRA.payroll_relationship_id
AND PRT.relationship_type_id =
PPRD.relationship_type_id
AND PRR.payroll_rel_action_id =
PPRA.payroll_rel_action_id
AND PPRA.payroll_action_id =
PPA.payroll_action_id
AND PAYRO.payroll_id = PPA.payroll_id
AND PTP.payroll_id = PPA.payroll_id
AND PRR.element_type_id = PET.element_type_id
AND PRRV.run_result_id = PRR.run_result_id
AND PRRV.input_value_id = PIVF.input_value_id
AND PIVF.element_type_id = PET.element_type_id
AND PPA.earn_time_period_id = PTP.time_period_id
AND PFI.flow_instance_id = REQ.flow_instance_id
AND REQ.pay_request_id = PPA.pay_request_id(+)
AND PAAM.assignment_id = ASG.assignment_id(+)
--AND PAAM.LOCATION_ID = PLDFV.LOCATION_ID(+)
AND HAOUFVD.organization_id(+) =
PAAM.organization_id
AND HAOUFVD.organization_id =
HOUCFD.organization_id(+)
AND SAL.assignment_id(+) = PAAM.assignment_id
AND PAPF.person_id = SAL.person_id(+)
AND PAAM.people_group_id = PPG.people_group_id(+)
AND PPRD.payroll_relationship_id =
BANK_DTLS.payroll_relationship_id(+)
AND POS.position_id(+) = PAAM.position_id
------------------------------------------------
AND PPN.name_type = 'GLOBAL'
AND PPN_AR.name_type(+) = 'SA'
AND PAAM.assignment_type = 'E'
AND PAAM.primary_flag = 'Y'
AND PIVF.base_name IN ( 'Pay Value', 'Net Pay' )
AND PPA.action_type IN ( 'Q', 'R' )
AND PAAM.effective_latest_change = 'Y'
AND LOOKUPN.lookup_type(+) = 'NATIONALITY'
-------------------------------------------
AND paam.location_id = loc.location_id(+)
AND paam.grade_id = grade.grade_id(+)
AND grade.LANGUAGE(+) = 'US'
------------------------------------------------------------
AND ( PRIM.classification_name IN (
'Standard Earnings', 'Voluntary Deductions'
,
'Social Insurance Deductions',
'Absences'
) )
AND PRIM.legislation_code = 'SA'
-- AND PET.REPORTING_NAME NOT IN ('Basic Salary')
-------------------------------------------------
AND Trunc (SYSDATE) BETWEEN PPN.
effective_start_date AND
PPN.
effective_end_date
AND Trunc (SYSDATE) BETWEEN PPN_AR.
effective_start_date
(+) AND
PPN_AR.
effective_end_date(+)
AND Trunc (SYSDATE) BETWEEN PAPF.
effective_start_date
AND PAPF.
effective_end_date
AND Trunc (SYSDATE) BETWEEN POS.
effective_start_date(+)
AND
POS.
effective_end_date(+)
AND PPA.effective_date BETWEEN
PTP.start_date AND PTP.end_date
AND PTP.start_date BETWEEN PET.
effective_start_date AND
PET.
effective_end_date
AND PTP.start_date BETWEEN PIVF.
effective_start_date AND
PIVF.
effective_end_date
AND Trunc (SYSDATE) BETWEEN PET.
effective_start_date AND
PET.
effective_end_date
-- AND TRUNC(SYSDATE) BETWEEN SAL.DATE_FROM AND SAL.DATE_TO
-- AND NVL(PPOS.ACTUAL_TERMINATION_DATE,PTP.END_DATE) BETWEEN trunc(SAL.DATE_FROM) - (to_number(to_char(SAL.DATE_FROM,'DD')) - 1) AND LAST_DAY(SAL.DATE_TO)
AND ( Nvl (PPOS.actual_termination_date,
PTP.end_date) >
Trunc (SAL.date_from) - ( To_number (
To_char (SAL.date_from,
'DD'))
- 1
)
AND Nvl (PPOS.actual_termination_date,
PTP.end_date) <
Last_day (SAL.date_to +
1) )
AND Trunc (SYSDATE) BETWEEN Nvl (
Trunc (HOUCFD. effective_start_date),
Trunc (SYSDATE)) AND
Nvl (
Trunc (HOUCFD. effective_end_date),
Trunc (
SYSDATE))
AND Trunc (SYSDATE) BETWEEN Nvl (
Trunc (HAOUFVD. effective_start_date),
Trunc (
SYSDATE)) AND
Nvl (
Trunc (HAOUFVD. effective_end_date),
Trunc (
SYSDATE))
AND ( PTP.end_date BETWEEN Nvl (Trunc (
BANK_DTLS. effective_start_date),
Trunc (SYSDATE)) AND
Nvl (Trunc (
BANK_DTLS. effective_end_date),
Trunc (
SYSDATE))
OR Trunc (SYSDATE) BETWEEN Nvl (
Trunc (
BANK_DTLS. effective_start_date
)
,
Trunc
(
SYSDATE)) AND
Nvl (Trunc (
BANK_DTLS. effective_end_date),
Trunc
(
SYSDATE)) )
AND Trunc (SYSDATE) BETWEEN grade.
effective_start_date
(+) AND
grade.
effective_end_date(+)
AND SYSDATE BETWEEN PAAM.effective_start_date AND
PAAM.effective_end_date
--------------------
--PARAMETERS--
--------------
AND PPA.element_set_id = 300000003834184
/* Added by Hamdy to filter for the regular payroll element object group, a parameter will be needed to make it dynamic*/
AND ( PAPF.person_number = :P_NUM_P
OR :P_NUM_P IS NULL )
AND ( Coalesce (NULL, :P_PERIOD_NAME) IS NULL
OR ( period_name IN ( :P_PERIOD_NAME ) ) )
AND ( PAAM.ass_attribute2 = :P_SPONSER
OR :P_SPONSER IS NULL )
AND ( LOOKUPN.meaning = :P_NATION
OR :P_NATION IS NULL )
AND ( HAOUFVD.name = :P_DEPT
OR :P_DEPT IS NULL )
AND ( grade.name = :P_GRADE
OR :P_GRADE IS NULL )
AND ( LOC.location_name = :P_LOC
OR :P_LOC IS NULL )
AND ( PAAM.ass_attribute1 = :P_DIVISION
OR :P_DIVISION IS NULL )
AND ( BANK_DTLS.base_org_pay_method_name =
:ORG_PAY_MTHD
OR :ORG_PAY_MTHD IS NULL )
AND ( PAYRO.payroll_name = :P_PAYROLL_NAME
OR :P_PAYROLL_NAME IS NULL )
GROUP BY PAPF.person_number,
Substr (PTP.period_name, 1, 7),
PAYRO.payroll_name,
LOOKUPN.meaning,
Initcap (PPN.list_name),
PPN_AR.display_name,
-- LOOKUPN.MEANING AS NATIONALITY,
POS.name,
-- TO_CHAR (PAPF.START_DATE, 'DD/MM/YYYY') AS JOINING_DATE,
HAOUFVD.name,
PAAM.ass_attribute1,
--------------------
--BANK DETAILS--
----------------
BANK_DTLS.pay_method,
BANK_DTLS.bank_name,
------------------------------
grade.name,
-- grade_code.grade_code,
loc.location_name,
( CASE
WHEN PET.reporting_name =
'SA_EE_GOSI_UNEMPLOYMENT_INSURANCE'
OR PET.reporting_name =
'Employee GOSI Annuities'
OR PET.reporting_name =
'Employee GOSI Saned' THEN
'GOSI Deduction'
/*'Employee GOSI Saned' changed by Hamdy*/
WHEN PET.reporting_name =
'Sick Leave Entitlement Result'
THEN
'Sick Leave Deduction'
ELSE PET.reporting_name
END ),
( CASE
WHEN PRIM.classification_name = (
'Standard Earnings' )
THEN (
CASE
WHEN PET.reporting_name = 'Basic Salary'
THEN 1
WHEN PET.reporting_name =
'Housing Allowance'
THEN 2
WHEN PET.reporting_name =
'Transportation Allowance'
THEN 3
WHEN PET.reporting_name =
'Mobile Allowance'
THEN 4
WHEN PET.reporting_name =
'Nature Allowance'
THEN 5
ELSE 20
END )
WHEN PRIM.classification_name IN (
'Voluntary Deductions',
'Social Insurance Deductions',
'Absences' )
THEN
( CASE
WHEN PET.reporting_name IN
( 'GOSI', 'SA_EE_GOSI_UNEMPLOYMENT_INSURANCE',
'Employee GOSI Annuities' ) THEN 22
ELSE 23
END )
END )
ORDER BY ordering) Q2
WHERE PAPF.person_number = Q2.person_number
AND Q2.ordering > 20) TOTAL_DEDUC,
--------------------------------------------------
---------------------
--PARAMETERS--
---------------------
Nvl(:P_NUM_P, 'All') P_NUM_P,
-- :P_PAY_NAME,
:P_PERIOD_NAME,
Nvl(:P_DEPT, 'All') P_DEPT,
Nvl(:P_LOC, 'All') P_LOC,
Nvl(:P_GRADE, 'All') P_GRADE,
Nvl(:P_NATION, 'All') P_NATION,
Nvl(:P_SPONSER, 'All') P_SPONSER,
Nvl(:ORG_PAY_MTHD, 'All') ORG_PAY_MTHD,
Nvl(:P_DIVISION, 'All') P_DIVISION
FROM per_all_people_f PAPF,
per_citizenships PC,
hr_lookups LOOKUPN,
per_all_assignments_m PAAM,
per_periods_of_service PPOS,
per_person_names_f PPN,
per_person_names_f PPN_AR,
pay_all_payrolls_f PAYRO,
pay_payroll_actions PPA,
pay_payroll_rel_actions PPRA,
pay_pay_relationships_dn PPRD,
pay_relationship_types PRT,
pay_run_results PRR,
pay_element_types_vl PET,
pay_run_result_values PRRV,
pay_input_values_f PIVF,
pay_time_periods PTP,
pay_flow_instances PFI,
pay_requests REQ,
pay_rel_groups_dn ASG,
-- PER_LOCATION_DETAILS_F_VL PLDFV,
hr_org_unit_classifications_f HOUCFD,
hr_all_organization_units_f_vl HAOUFVD,
cmp_salary SAL,
per_people_groups PPG,
-- PAY_ELE_CLASSIFICATIONS_VL CLASSIF,
pay_ele_classifications_vl PRIM,
hr_all_positions POS,
per_grades_f_tl grade,
--ANC_PER_ABS_ENTRIES VAC,
(SELECT lo.location_id,
l.location_name,
lo.internal_location_code LOCATION_CODE
FROM hr_locations_all lo,
hr_locations_all_tl l
WHERE l.location_details_id = lo.location_details_id
AND l.LANGUAGE = 'US') loc,
(SELECT PBA.bank_account_name,
PBA.bank_name,
PBA.bank_branch_name,
PBA.bank_account_num,
PBA.iban_number,
Nvl (PBA.bank_code, PBA.bank_number) BANK_CODE,
POPF.currency_code,
POPF.base_org_pay_method_name,
PPPMF.payroll_relationship_id,
-- PPTT.PAYMENT_TYPE_NAME,
-- PPTT.PAYMENT_TYPE_ID,
Nvl (PPPMF.effective_start_date, SYSDATE)
EFFECTIVE_START_DATE,
Nvl (PPPMF.effective_end_date, SYSDATE)
EFFECTIVE_END_DATE,
PPPMF.name PAY_METHOD
FROM pay_bank_accounts PBA,
pay_person_pay_methods_f PPPMF,
pay_org_pay_methods_f POPF
WHERE PPPMF.bank_account_id = PBA.bank_account_id(+)
AND PPPMF.org_payment_method_id(+) =
POPF.org_payment_method_id
-- AND POPF.language(+) = 'US'
-- AND POPF.PAYMENT_TYPE_ID = PPTT.PAYMENT_TYPE_ID
-- AND PPTT.LANGUAGE = USERENV ('LANG')
AND Trunc (SYSDATE) BETWEEN PPPMF.effective_start_date
AND
PPPMF.effective_end_date
AND Trunc (SYSDATE) BETWEEN POPF.effective_start_date AND
POPF.effective_end_date
AND Trunc (SYSDATE) BETWEEN Nvl (Trunc (PBA.start_date),
Trunc (
SYSDATE)
) AND
Nvl (Trunc (PBA.end_date), Trunc
(
SYSDATE))) BANK_DTLS
WHERE PAPF.person_id = PPN.person_id
AND PAPF.person_id = PPN_AR.person_id
-- AND PAPF.PERSON_ID = VAC.PERSON_ID
AND PET.classification_id = PRIM.base_classification_id
AND LOOKUPN.lookup_code(+) = PC.legislation_code
AND PC.person_id(+) = PAPF.person_id
AND PAAM.person_id = PAPF.person_id
AND PPOS.person_id = PAPF.person_id
AND PPOS.period_of_service_id = PAAM.period_of_service_id
AND PPRD.person_id = PAPF.person_id
AND PPRD.payroll_relationship_id = PPRA.payroll_relationship_id
AND PRT.relationship_type_id = PPRD.relationship_type_id
AND PRR.payroll_rel_action_id = PPRA.payroll_rel_action_id
AND PPRA.payroll_action_id = PPA.payroll_action_id
AND PAYRO.payroll_id = PPA.payroll_id
AND PTP.payroll_id = PPA.payroll_id
AND PRR.element_type_id = PET.element_type_id
AND PRRV.run_result_id = PRR.run_result_id
AND PRRV.input_value_id = PIVF.input_value_id
AND PIVF.element_type_id = PET.element_type_id
AND PPA.earn_time_period_id = PTP.time_period_id
AND PFI.flow_instance_id = REQ.flow_instance_id
AND REQ.pay_request_id = PPA.pay_request_id(+)
AND PAAM.assignment_id = ASG.assignment_id(+)
--AND PAAM.LOCATION_ID = PLDFV.LOCATION_ID(+)
AND HAOUFVD.organization_id(+) = PAAM.organization_id
AND HAOUFVD.organization_id = HOUCFD.organization_id(+)
AND SAL.assignment_id(+) = PAAM.assignment_id
AND PAPF.person_id = SAL.person_id(+)
AND PAAM.people_group_id = PPG.people_group_id(+)
AND PPRD.payroll_relationship_id =
BANK_DTLS.payroll_relationship_id(+)
AND POS.position_id(+) = PAAM.position_id
------------------------------------------------
AND PPN.name_type = 'GLOBAL'
AND PPN_AR.name_type(+) = 'SA'
AND PAAM.assignment_type = 'E'
AND PAAM.primary_flag = 'Y'
AND PIVF.base_name IN ( 'Pay Value', 'Net Pay' )
AND PPA.action_type IN ( 'Q', 'R' )
AND PAAM.effective_latest_change = 'Y'
AND LOOKUPN.lookup_type(+) = 'NATIONALITY'
-------------------------------------------
AND paam.location_id = loc.location_id(+)
AND paam.grade_id = grade.grade_id(+)
AND grade.LANGUAGE(+) = 'US'
------------------------------------------------------------
AND ( PRIM.classification_name IN (
'Standard Earnings', 'Voluntary Deductions',
'Social Insurance Deductions',
'Absences'
) )
AND PRIM.legislation_code = 'SA'
-- AND VAC.ABSENCE_STATUS_CD = 'SUBMITTED'
-- AND PET.REPORTING_NAME NOT IN ('Basic Salary')
-------------------------------------------------
AND Trunc (SYSDATE) BETWEEN PPN.effective_start_date AND
PPN.effective_end_date
AND Trunc (SYSDATE) BETWEEN PPN_AR.effective_start_date(+) AND
PPN_AR.effective_end_date(+)
AND Trunc (SYSDATE) BETWEEN PAPF.effective_start_date AND
PAPF.effective_end_date
AND Trunc (SYSDATE) BETWEEN POS.effective_start_date(+) AND
POS.effective_end_date(+)
AND PPA.effective_date BETWEEN PTP.start_date AND PTP.end_date
AND PTP.start_date BETWEEN PET.effective_start_date AND
PET.effective_end_date
AND PTP.start_date BETWEEN PIVF.effective_start_date AND
PIVF.effective_end_date
AND Trunc (SYSDATE) BETWEEN PET.effective_start_date AND
PET.effective_end_date
-- AND TRUNC(SYSDATE) BETWEEN SAL.DATE_FROM AND SAL.DATE_TO
-- AND NVL(PPOS.ACTUAL_TERMINATION_DATE,PTP.END_DATE) BETWEEN trunc(SAL.DATE_FROM) - (to_number(to_char(SAL.DATE_FROM,'DD')) - 1) AND LAST_DAY(SAL.DATE_TO)
AND ( Nvl (PPOS.actual_termination_date, PTP.end_date) >
Trunc (SAL.date_from) - ( To_number (
To_char (SAL.date_from,
'DD'))
- 1
)
AND Nvl (PPOS.actual_termination_date, PTP.end_date) <
Last_day (SAL.date_to +
1) )
AND Trunc (SYSDATE) BETWEEN Nvl (
Trunc (HOUCFD.effective_start_date),
Trunc (SYSDATE)) AND
Nvl (
Trunc (HOUCFD.effective_end_date),
Trunc (
SYSDATE))
AND Trunc (SYSDATE) BETWEEN Nvl (
Trunc (HAOUFVD.effective_start_date),
Trunc (
SYSDATE)) AND
Nvl (
Trunc (HAOUFVD.effective_end_date),
Trunc (
SYSDATE))
AND ( PTP.end_date BETWEEN Nvl (Trunc (
BANK_DTLS.effective_start_date),
Trunc (SYSDATE)) AND
Nvl (Trunc (
BANK_DTLS.effective_end_date),
Trunc (
SYSDATE))
OR Trunc (SYSDATE) BETWEEN Nvl (Trunc (
BANK_DTLS. effective_start_date)
,
Trunc
(
SYSDATE)) AND
Nvl (Trunc (
BANK_DTLS. effective_end_date),
Trunc
(
SYSDATE)) )
AND Trunc (SYSDATE) BETWEEN grade.effective_start_date(+) AND
grade.effective_end_date(+)
AND SYSDATE BETWEEN PAAM.effective_start_date AND
PAAM.effective_end_date
--------------------
--PARAMETERS--
--------------
AND ( PAPF.person_number = :P_NUM_P
OR :P_NUM_P IS NULL )
AND ( Coalesce (NULL, :P_PERIOD_NAME) IS NULL
OR ( period_name IN ( :P_PERIOD_NAME ) ) )
AND ( PAAM.ass_attribute2 = :P_SPONSER
OR :P_SPONSER IS NULL )
AND ( LOOKUPN.meaning = :P_NATION
OR :P_NATION IS NULL )
AND ( HAOUFVD.name = :P_DEPT
OR :P_DEPT IS NULL )
AND ( grade.name = :P_GRADE
OR :P_GRADE IS NULL )
AND ( LOC.location_name = :P_LOC
OR :P_LOC IS NULL )
AND ( PAAM.ass_attribute1 = :P_DIVISION
OR :P_DIVISION IS NULL )
AND ( BANK_DTLS.base_org_pay_method_name = :ORG_PAY_MTHD
OR :ORG_PAY_MTHD IS NULL )
AND ( PAYRO.payroll_name = :P_PAYROLL_NAME
OR :P_PAYROLL_NAME IS NULL )
AND ( PET.reporting_name NOT IN (
'Salary Advance Payment', 'Dependant fees loan Payment'
,
'EOS Loan Payment',
'Car Loan Payment' ) )
GROUP BY PAPF.person_number,
Substr (PTP.period_name, 1, 7),
PAYRO.payroll_name,
LOOKUPN.meaning,
Initcap (PPN.list_name),
PPN_AR.display_name,
-- LOOKUPN.MEANING AS NATIONALITY,
POS.name,
-- TO_CHAR (PAPF.START_DATE, 'DD/MM/YYYY') AS JOINING_DATE,
HAOUFVD.name,
PAAM.ass_attribute1,
--------------------
--BANK DETAILS--
----------------
BANK_DTLS.pay_method,
BANK_DTLS.bank_name,
------------------------------
grade.name,
-- grade_code.grade_code,
loc.location_name,
( CASE
WHEN PET.reporting_name =
'SA_EE_GOSI_UNEMPLOYMENT_INSURANCE'
OR PET.reporting_name = 'Employee GOSI Annuities'
OR PET.reporting_name = 'Employee GOSI Saned' THEN
'GOSI Deduction'
/*'Employee GOSI Saned' changed by Hamdy*/
WHEN PET.reporting_name = 'Sick Leave Entitlement Result'
THEN
'Sick Leave Deduction'
ELSE PET.reporting_name
END ),
( CASE
WHEN PRIM.classification_name = ( 'Standard Earnings' )
THEN (
CASE
WHEN PET.reporting_name = 'Basic Salary' THEN 1
WHEN PET.reporting_name = 'Housing Allowance' THEN 2
WHEN PET.reporting_name = 'Transportation Allowance'
THEN 3
WHEN PET.reporting_name = 'Mobile Allowance' THEN 4
WHEN PET.reporting_name = 'Nature Allowance' THEN 5
ELSE 20
END )
WHEN PRIM.classification_name IN (
'Voluntary Deductions', 'Social Insurance Deductions'
,
'Absences' )
THEN
( CASE
WHEN PET.reporting_name IN
( 'GOSI', 'SA_EE_GOSI_UNEMPLOYMENT_INSURANCE',
'Employee GOSI Annuities' ) THEN 22
ELSE 23
END )
END ),
PTP.end_date,
PTP.start_date,
PAPF.person_id) QRY
ORDER BY QRY.ordering
No comments:
Post a Comment