XXSDL No Sgwi Payroll Int PKG

CREATE OR REPLACE PACKAGE xxsdl_no_sgwi_payroll_int_pkg AS
--
+==================================================================================
==================================+
-- | HP ENTERPRISE SERVICES INDIA
|
-- |
===================================================================================
================================|
-- | DESCRIPTION : This sql file creates the Package Spec for the providing
handling the Mass Dispute Functionality for Norway |
-- |
|
-- | Version Date Author Brief Description of
Modification |
-- | ======= ====== ========
================================== |
-- | 1.00 01-APR-2020 Nandini Chakrabarti Initial Version
--
+==================================================================================
==================================+
g_request_id NUMBER;
g_user_id NUMBER;
FUNCTION print_nid_dup_det (
per_id NUMBER
) RETURN NUMBER;
FUNCTION validate_dups (
payroll_name VARCHAR2
) RETURN NUMBER;
FUNCTION print_bank_dup_det (
per_id NUMBER
) RETURN NUMBER;
FUNCTION validate_bank_dups (
) RETURN NUMBER;
FUNCTION check_residential_address (
l_payroll_name VARCHAR2
) RETURN NUMBER;
PROCEDURE xxsdl_no_sgwi_pay_masterdata_p (
p_errbuff OUT NOCOPY VARCHAR2,
p_retcode OUT NOCOPY VARCHAR2,
p_flag IN VARCHAR2,
p_payroll_group IN VARCHAR2,
p_payroll_name IN VARCHAR2,
p_effective_date IN VARCHAR2
-- Removing Paramter p_person_location_type for Global Payroll Project
Commented by Srikkanth M
-- p_person_location_type IN VARCHAR2
);
PROCEDURE xxsdl_no_sgwi_pay_element_p (
p_flag IN VARCHAR2,
);
END xxsdl_no_sgwi_payroll_int_pkg;
/
CREATE OR REPLACE PACKAGE BODY xxsdl_no_sgwi_payroll_int_pkg AS

--
+==================================================================================
==================================+
-- | HP ENTERPRISE SERVICES INDIA
|
-- |
===================================================================================
================================|
-- | DESCRIPTION : This sql file creates the Package Spec for the providing
handling the Mass Dispute Functionality |
-- |
|
-- | 1.00 12-Sep-2019 Nandini Chakrabarti Iniial version for Norway

payroll with columns NO_LE_ST_DT, NO_ASS_EFFDT, NO_REG, NO_INTRINN for Norwegian
payroll and offshore/onshore parameter|
-- | 2.00 30-Apr-2021 Sreyashi Saha Added logic for removing
hardcoded server and restructed file path|
-- | 3.00 14-JUL-2021 Suvendu Chowdhury Change logic on db
check and Source Directory structure |
-- | 4.00 21-Sep-2021 Naveedoddin Mohammad IBAN column change as per
RITM0198449
-- | 5.00 18-May-2023 Srikkanth M Added a new column
Collective Bargaining date
-- | 6.00 15-Jun-2023 Srikkanth M Modified the date format
for Collective Bargaining date
-- | 7.00 22-Sep-2023 Srikkanth M Removing Paramter
p_person_location_type for Global Payroll Project
--
+==================================================================================
==================================+
PROCEDURE transfer_file (
p_from VARCHAR2,
p_file VARCHAR2,
p_to VARCHAR2
);
PROCEDURE log (
p_text VARCHAR2
);
PROCEDURE xxsdl_no_sgwi_pay_masterdata_p (
p_flag IN VARCHAR2,
) IS
l_count NUMBER := 1;
l_file_path VARCHAR2(1000);
l_file_name VARCHAR2(200);
l_file VARCHAR2(100);
l_file_handle utl_file.file_type;
l_file_handle1 utl_file.file_type;
l_instance v$instance.instance_name%TYPE;
l_output VARCHAR2(30000);
l_output1 VARCHAR2(30000);
p_from_date DATE;
p_to_date DATE;
l_from_date DATE;
l_to_date DATE;
q_from_date DATE;
q_to_date DATE;
l_effective_date DATE;
l_payroll_cnt NUMBER := 0;
l_db_name VARCHAR2(20);
l_pay_group VARCHAR2(20);
l_nid_val NUMBER;
l_bank_val NUMBER;
l_val_pay_name VARCHAR2(100);
l_emp_val NUMBER;
CURSOR c1 IS
SELECT DISTINCT
*
FROM
xxsdl_no_sgwi_pay_master_stg
ORDER BY
employee_number,
payroll_leave_date DESC;
CURSOR c_sgwi_payroll (
p_from_date DATE,
p_to_date DATE,
l_from_date DATE,
l_to_date DATE,
q_from_date DATE,
q_to_date DATE,
l_payroll_name VARCHAR2,
l_business_group_id NUMBER,
l_secondary_payroll VARCHAR2,
l_effective_date DATE,
l_payroll_id NUMBER
) IS
SELECT
papf.employee_number,
(
CASE
WHEN (
SELECT
effective_start_date
FROM
per_all_people_f
WHERE
person_id = papf.person_id
AND effective_start_date = (
SELECT
MIN(effective_start_date)
FROM
per_all_people_f
WHERE
AND business_group_id = papf.business_group_id
AND employee_number IS NOT NULL
)
AND ( ( nvl(
original_date_of_hire, effective_start_date
) <= q_to_date
AND creation_date BETWEEN l_from_date AND l_to_date
)
OR ( creation_date <= q_to_date
AND nvl(
) BETWEEN q_from_date AND q_to_date ) )
) IS NOT NULL THEN
'Starter'
/* Starter logic added to identify rehire employees */
/*Added by Rajitha */
WHEN (
SELECT
COUNT(*)
FROM
per_periods_of_service
WHERE
) > 1
AND (
SELECT
MAX(date_start)
FROM
WHERE
) BETWEEN l_from_date AND l_to_date THEN
'Starter'
/*Leaver logic as per RFCHCM2016033*/
WHEN (
SELECT
actual_termination_date
FROM
WHERE
1 = 1
AND last_update_date BETWEEN l_from_date AND l_to_date
AND actual_termination_date <= l_to_date
AND person_id = papf.person_id
) IS NOT NULL THEN
'Leaver'
WHEN ( ( ppf.payroll_id = (
SELECT
payroll_id
FROM
per_all_assignments_f
WHERE
AND primary_flag = 'Y'
SELECT
MAX(effective_start_date)
FROM
WHERE
)
AND ( ( effective_start_date <= q_to_date
AND last_update_date BETWEEN l_from_date AND
l_to_date )
OR ( last_update_date <= q_to_date
AND effective_start_date BETWEEN l_from_date AND
l_to_date ) )
) )
AND ( ppf.attribute1 <> (
SELECT
attribute1
FROM
pay_payrolls_f
WHERE
payroll_id IN (
SELECT
payroll_id
FROM
WHERE
AND effective_end_date = (
SELECT
MAX(effective_start_date) - 1
FROM
WHERE
)
AND ( ( effective_end_date <= q_to_date
AND last_update_date BETWEEN l_from_date
AND l_to_date )
AND effective_end_date BETWEEN
l_from_date AND l_to_date ) )
)
AND trunc(sysdate) BETWEEN effective_start_date AND
effective_end_date
) ) ) THEN
'IN'
ELSE
NULL
END
) starter_or_leaver,
papf.person_id,
papf.business_group_id,
replace(
papf.last_name, ',', NULL
) sur_name,
replace(
papf.middle_names, ',', NULL
) middle_name,
replace(
papf.first_name, ',', NULL
) fornames,
papf.title title,
to_char(
nvl(
papf.original_date_of_hire, papf.start_date
), 'YYYY-MM-DD'
) start_date,
to_char(
papf.date_of_birth, 'YYYY-MM-DD'
) dob,
/*Below Logic as per RFCHCM2016033*/

(
SELECT
to_char(
actual_termination_date, 'YYYY-MM-DD'
)
FROM
WHERE
1 = 1
) company_end_date,
NULL tel_number,
decode(
papf.sex, 'M', 'Male', 'F', 'Female', NULL
) gender,
(
CASE
WHEN instr(
papf.email_address, ';'
) > 0 THEN
NULL
WHEN instr(
papf.email_address, '/'
) > 0 THEN
NULL
ELSE
(
SELECT
papf2.email_address
FROM
per_all_people_f papf2
WHERE
REGEXP_LIKE ( papf2.email_address,
'[a-zA-Z0-9._%-]+@[a-zA-Z0-9._%-]+\.[a-zA-Z]
{2,4}' )
AND trunc(
nvl(
to_date(l_effective_date), sysdate
)
) BETWEEN papf2.effective_start_date AND
papf2.effective_end_date
AND papf2.person_id = papf.person_id
)
END
) email_address,
--null Email_Address,--commented by Rajitha

'Residential Address' address_type,
address.address_line1 address1,
nvl(
address.town_or_city, NULL
) city,
address.state state,
address.postal_code zip_code,
nvl(
(
SELECT
territory_short_name
FROM
fnd_territories_tl
WHERE
territory_code = address.country
AND language = 'US'
), address.country
) country,
(
CASE
WHEN ( p_payroll_group IN ( 'NO' ) ) THEN
( ppf.payroll_name )
ELSE
( l_payroll_name )
END
) payroll_to_join,
(
SELECT
to_char(
MIN(effective_start_date), 'YYYY-MM-DD'
)
FROM
WHERE
AND payroll_id = paaf.payroll_id
) payroll_start_date,
NULL payroll_join_date, -- Made null for
RFCHCM2016033
NULL payroll_end_date, -- Made null for

RFCHCM2016033
(
SELECT
to_char(
final_process_date, 'YYYY-MM-DD'
)
FROM
WHERE
) final_process_date,
/*Below logic changes as per RFCHCM2016033*/

(
CASE
WHEN (
SELECT
FROM
WHERE
1 = 1
) IS NOT NULL THEN
(
SELECT
to_char(
)
FROM
WHERE
1 = 1
)
ELSE
NULL
END
) payroll_leave_date,
(
SELECT DISTINCT
bank_name
FROM
cefv_bank_branches
WHERE
bank_branch_id = ppinf.pei_information2
) bank_name,
(
SELECT DISTINCT
bank_branch_number
FROM
cefv_bank_branches
WHERE
) bank_number,
ppinf.pei_information16 ach_routing_number,
-- ppinf.pei_information4 bank_account_number,
nvl(
ppinf.pei_information6, ppinf.pei_information4
) bank_account_number, -- added as per
RITM0198449
ppinf.pei_information12 bic_swift,
ppinf.pei_information6 iban,
ppinf.pei_information3 payee_name,
(
SELECT
meaning
FROM
hr_lookups hl
WHERE
lookup_type = 'MAR_STATUS'
AND hl.lookup_code = papf.marital_status
) marital_status,
/*(SELECT TO_CHAR (change_date,
'YYYY-MM-DD'
)
FROM per_pay_proposals
WHERE assignment_id = paaf.assignment_id
AND TRUNC (NVL (l_effective_date, SYSDATE))
BETWEEN change_date
AND NVL (date_to, '31-DEC-4712')
AND approved = 'Y') salary_effective_date,*/ -- commented by
Subhashree to add leavers salary for the current month
---- added by Subhashree to include leavers salary for the

current month
(
SELECT
to_char(
change_date, 'YYYY-MM-DD'
)
FROM
per_pay_proposals
WHERE
assignment_id = paaf.assignment_id
AND ( ( trunc(
nvl(
l_effective_date, sysdate
)
) BETWEEN change_date AND nvl(
date_to, '31-DEC-4712'
) )
OR ( ( nvl(
) BETWEEN l_from_date AND l_to_date )
AND date_to = (
SELECT
MAX(date_to)
FROM
per_pay_proposals
WHERE
)
AND ( pps.actual_termination_date BETWEEN
l_from_date AND l_to_date ) ) )
AND approved = 'Y'
) salary_effective_date,
(
CASE
WHEN p_payroll_group IN ( 'AN' ) THEN
( nvl(
(
SELECT
to_number(
e.screen_entry_value
)
FROM
apps.pay_element_entries_f b,
apps.pay_element_links_f c, apps.pay_element_entry_values_f e, apps.
pay_element_types_f_tl f, apps.pay_input_values_f
g
WHERE
1 = 1
AND f.element_name = 'Local Salary Payment'
AND b.assignment_id = paaf.assignment_id
AND b.element_link_id = c.element_link_id
AND c.element_type_id = f.element_type_id
AND g.element_type_id = f.element_type_id
AND f.language = 'US'
AND b.element_entry_id = e.element_entry_id
AND e.screen_entry_value IS NOT NULL
AND trunc(sysdate) BETWEEN b.effective_start_date AND
b.effective_end_date
AND trunc(sysdate) BETWEEN e.effective_start_date AND
e.effective_end_date
AND g.name = 'Amount'
AND g.input_value_id = e.input_value_id
),(
SELECT
proposed_salary_n
FROM
per_pay_proposals
WHERE
AND trunc(
nvl(
)
)
AND approved = 'Y'
)
) )
/*ELSE (SELECT proposed_salary_n
BETWEEN change_date
AND approved = 'Y')
END
) annual_salary,*/ -- commented by Subhashree to add leavers
salary for the current month
-- added by Subhashree to include leavers salary for the current

month
ELSE
(
SELECT
proposed_salary_n
FROM
per_pay_proposals
WHERE
AND ( ( trunc(
nvl(
)
) )
OR ( ( nvl(
AND date_to = (
SELECT
MAX(date_to)
FROM
per_pay_proposals
WHERE
)
AND approved = 'Y'
)
END
) annual_salary,
/*(SELECT proposed_salary_n
FROM per_pay_Proposals
WHERE assignment_id=paaf.assignment_id
AND TRUNC(nvl(to_date(:l_effective_date), sysdate)) BETWEEN
change_date AND NVL(date_to,'31-DEC-4712')
AND approved='Y'
) Annual_Salary,*/
(
CASE
( nvl(
(
SELECT
to_char(
)
FROM
g
WHERE
1 = 1
AND g.name = 'Currency'
),(substr(
pb.name, - 3
))
) )
ELSE
substr(
pb.name, - 3
)
END
) salary_currency,
--substr(pb.name,-3) Salary_Currency,
nvl(
ppd.segment1, ''
) upn, -- added by Naveed on 05-feb-2019
40 hours_per_week,
replace(
pj.name, '..', NULL
) job_title,
(
SELECT DISTINCT
full_name
FROM
per_all_people_f
WHERE
person_id = paaf.supervisor_id
AND trunc(
nvl(
)
) BETWEEN effective_start_date AND effective_end_date
) line_manager,
paaf.ass_attribute17 fte,
(
SELECT
meaning
FROM
hr_lookups
WHERE
lookup_type = 'EMP_CAT'
AND lookup_code = paaf.employment_category
) parttime_or_fulltime,
(
SELECT
t.description
FROM
fnd_flex_values_tl t,
fnd_flex_values b,
fnd_flex_value_sets s
WHERE
b.flex_value_id = t.flex_value_id
AND b.flex_value_set_id = s.flex_value_set_id
AND t.language = userenv(
'LANG'
)
AND s.flex_value_set_name = 'SDRL_GL_HQ_LEGAL_ENTITY'
AND flex_value = hsc.segment1
) legal,
hsc.segment1 legal_entity_code,
--Replaced hsc.segment1 with
ASS_Attribute under RFC
--
DECODE(ppg.segment2, 'ON', 'Onshore', 'OFF', 'Offshore', NULL) Onshore,
/**** Norway person type OF/ON*** Added by Nandini for Norwagian
Payroll****RITM0174520********/
decode(
ppg.segment2, 'ON', 'Onshore', 'OFF', 'Offshore', NULL
) onshore,/*person_location_type,*/
/****OF/ON effective date added by Nandini*****/

(
CASE
WHEN
-- Removing Paramter p_person_location_type for Global Payroll
Project Commented by Srikkanth M
-- p_person_location_type
decode(
ppg.segment2, 'ON', 'ONSHORE', 'OFF', 'OFFSHORE', NULL
) IN ( 'OFFSHORE' )
AND (
SELECT
COUNT(DISTINCT ppg2.segment2)
FROM
pay_people_groups ppg2,
per_all_assignments_f paaf2
WHERE
paaf2.people_group_id = ppg2.people_group_id
AND paaf2.person_id = paaf.person_id
) > 1 THEN
(
SELECT
to_char(
MIN(paaf1.effective_start_date), 'YYYY-MM-DD'
)
FROM
per_all_assignments_f paaf1,
pay_people_groups ppg1
WHERE
AND ppg1.segment2 = 'OFF'
AND EXISTS (
SELECT
1
FROM
WHERE
AND ppg2.segment2 = 'ON'
AND paaf2.person_id = paaf1.person_id
AND paaf2.effective_start_date <
paaf1.effective_start_date
)
)
WHEN
decode(
) IN ( 'OFFSHORE' )
AND (
SELECT
FROM
WHERE
) = 1 THEN
(
SELECT
to_char(
)
FROM
WHERE
paaf1.person_id = paaf.person_id
AND paaf1.payroll_id = 66
)
----offshore logic ends Onshore logic starts------
WHEN
decode(
) IN ( 'ONSHORE' )
AND (
SELECT
FROM
WHERE
) > 1 THEN
(
SELECT
to_char(
MIN(paaf1.effective_end_date), 'YYYY-MM-DD'
)
FROM
WHERE
AND EXISTS (
SELECT
1
FROM
WHERE
)
)
WHEN
decode(
) IN ( 'ONSHORE' )
AND (
SELECT
FROM
WHERE
) = 1 THEN
(
SELECT
to_char(
)
FROM
WHERE
)
ELSE
NULL
END
) off_on_effdt,
--to_char(paaf.effective_start_date, 'YYYY-MM-DD')
off_on_effdt,
haou.name organization,
pcak.segment1 profit_centre,
ppd.segment5 region,
haou.attribute20 rig_name,
ppd.segment7 rig_type,
(
SELECT
location_code
FROM
hr_locations_all
WHERE
location_id = paaf.location_id
) rig_location,
(
SELECT
meaning
FROM
hr_lookups
WHERE
lookup_type = 'EMPLOYEE_CATG'
AND lookup_code = paaf.employee_category
) employee_category,
pg.name grade,
(
SELECT
user_status
FROM
per_assignment_status_types
WHERE
assignment_status_type_id = paaf.assignment_status_type_id
) assignment_status,
hapf.attribute2 job_category,
(
SELECT
ppei1.pei_information1
FROM
per_people_extra_info ppei1
WHERE
ppei1.information_type = 'SG_PASSPORT_INFO'
AND ppei1.person_id = papf.person_id
AND ROWNUM < 2
) passport,
NULL entitlement_daily_rate,
NULL entitlement_hourly_rate,
NULL employment_status,
(
SELECT
meaning
FROM
hr_lookups hl
WHERE
lookup_type = 'NATIONALITY'
AND hl.lookup_code = papf.nationality
) nationality,
papf.national_identifier social_security_number,
nvl(
paaf.ass_attribute26, NULL
) paying_legal_entity,
/**** Norway Legal Entity start date*** Added by Nandini for
Norwagian Payroll****SOW025********/
(
CASE
WHEN ( p_payroll_group IN ( 'NO' ) )
AND hsc.segment1 IN ( '841', '816' ) THEN
(
CASE
WHEN trunc(
nvl(
papf.original_date_of_hire, papf.effective_start_date
)
) >= TO_DATE('01-01-2019', 'mm-dd-yyyy') THEN
to_char(
nvl(
), 'YYYY-MM-DD'
)
WHEN trunc(
nvl(
)
) < TO_DATE('01-01-2019', 'mm-dd-yyyy') THEN
(
SELECT
to_char(
)
FROM
WHERE
1 = 1--paaf4.payroll_id in (142, 66)
AND paaf4.soft_coding_keyflex_id IN ( 40089, 64, 68,
69, 55089, 325095 )
AND trunc(
) >= TO_DATE('04-01-2011', 'mm-dd-yyyy')
)
ELSE
NULL
END
)
ELSE
NULL
END
) no_le_st_dt,
/**** Norway Assignment effective date*** Added by Nandini for

(
CASE
WHEN p_payroll_group IN ( 'NO' )
AND hsc.segment1 IN ( '841', '816' )
AND trunc(
paaf.effective_start_date
to_char(
paaf.effective_start_date, 'YYYY-MM-DD'
)
ELSE
NULL
END
) no_ass_effdt,
/**** Norway Regulativ*** Added by Nandini for Norwagian

Payroll****SOW025********/
(
CASE
paaf.ass_attribute14
ELSE
NULL
END
) no_reg,
/**** Norway Intrinn*** Added by Nandini for Norwagian

Payroll****SOW025********/
(
CASE
ELSE
NULL
END
) no_intrinn,
--** Added New column Collective Bargaining date for

Norwagian Payroll**--
(
SELECT
to_char(
fnd_date.canonical_to_date(
ppei.pei_information10
), 'YYYY-MM-DD'
)
FROM
per_people_extra_info ppei
WHERE
ppei.information_type = 'XXSD_NO_COLL_BARG'
AND ppei.person_id = papf.person_id
AND ROWNUM = 1
) collective_bargaining_date,
--**Below columns are not included in the extract**--
ppg.segment4 employee_cat_code,
(
CASE
WHEN ( p_payroll_group IN ( 'BR' ) ) THEN
( decode(
hsc.segment1, 702, '01', 904, '02'
)
|| '.'
|| xxsdl_pay_safegaurd_pkg.get_profit_center(
papf.person_id, nvl(
l_effective_date, trunc(sysdate)
)
)
|| '.'
|| xxsdl_pay_safegaurd_pkg.get_sub_account(
)
)
|| '.'
|| xxsdl_pay_safegaurd_pkg.get_life_cycle(
)
) )
ELSE
NULL
END
) br_codigo --
Added By Anil
-- Added By Subhashree 21st Mar-2018*/

,
paaf.ass_attribute20 AS life_cycle
FROM
per_all_people_f papf,
per_periods_of_service pps,
per_all_assignments_f paaf,
per_pay_bases pb,
per_grades pg,
per_jobs pj,
hr_all_organization_units haou,
per_position_definitions ppd,
per_all_positions pap,
hr_all_positions_f hapf,
hr_soft_coding_keyflex hsc,
pay_people_groups ppg,
pay_all_payrolls_f ppf,
pay_cost_allocation_keyflex pcak,
(
SELECT
person_id,
address_line1,
address_line2,
address_line3,
town_or_city,
region_2 state,
postal_code,
country
FROM
per_addresses
WHERE
primary_flag = 'Y'
AND address_type LIKE '%RA%'
AND trunc(
nvl(
)
) BETWEEN date_from AND nvl(
)
) address,
(
SELECT
pei_information17,
pei_information16,
pei_information4,
pei_information11,
pei_information12,
pei_information6,
pei_information3,
pei_information2,
person_id
FROM
per_people_extra_info a
WHERE
information_type = 'SO_BANK_DETAILS'
AND pei_information5 = 'Salary'
AND nvl(
to_date(
pei_information10, 'YYYY-MM-DD HH24:MI:SS'
), nvl(
)
) >= trunc(
nvl(
)
)
) ppinf
WHERE
papf.person_id = paaf.person_id
AND paaf.pay_basis_id = pb.pay_basis_id
AND paaf.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
AND address.person_id (+) = papf.person_id
AND paaf.effective_end_date = to_date(
'31-DEC-4712'
)
AND trunc(
nvl(
)
) BETWEEN paaf.effective_start_date AND paaf.effective_end_date
--to be removed
AND trunc(
nvl(
)
) BETWEEN papf.effective_start_date AND papf.effective_end_date
--to be removed
AND trunc(
nvl(
)
) BETWEEN hapf.effective_start_date AND hapf.effective_end_date
--to be removed
AND trunc(
nvl(
)
) BETWEEN ppf.effective_start_date AND ppf.effective_end_date
--to be removed
AND (
CASE
WHEN ( pps.actual_termination_date IS NOT NULL )
AND pps.actual_termination_date BETWEEN l_from_date AND
l_to_date THEN
'INVALID'
WHEN papf.original_date_of_hire > q_to_date THEN
'INVALID'
ELSE
'VALID'
END
) = 'VALID'
AND pg.grade_id (+) = paaf.grade_id
AND pj.job_id (+) = paaf.job_id
AND haou.organization_id = paaf.organization_id
AND pap.position_definition_id = ppd.position_definition_id
AND haou.name NOT LIKE '%SKD%'
AND paaf.position_id = pap.position_id
AND hapf.position_id = paaf.position_id
AND paaf.people_group_id = ppg.people_group_id
AND paaf.payroll_id = ppf.payroll_id
AND pcak.cost_allocation_keyflex_id (+) =
haou.cost_allocation_keyflex_id
AND ppf.payroll_name IN ( l_payroll_name, l_secondary_payroll )
AND ppf.business_group_id = l_business_group_id
AND pps.person_id = papf.person_id (+)
AND ppinf.person_id (+) = papf.person_id
AND papf.person_id = paaf.person_id
AND papf.current_employee_flag = 'Y'
AND paaf.assignment_type = 'E'
/*Norway OF/ON condition added by Nandini*/
/*AND ( ( p_person_location_type = 'OFFSHORE'
AND ppg.segment2 = 'OFF' )
OR ( p_person_location_type = 'ONSHORE'
AND ppg.segment2 = 'ON' ) ) */
UNION
SELECT DISTINCT
(
CASE
WHEN (
SELECT
effective_start_date
FROM
per_all_people_f
WHERE
SELECT
MIN(effective_start_date)
FROM
per_all_people_f
WHERE
AND business_group_id = papf.business_group_id
AND employee_number IS NOT NULL
)
AND ( ( nvl(
) <= q_to_date
AND creation_date BETWEEN l_from_date AND l_to_date
)
OR ( creation_date <= q_to_date
AND nvl(
) BETWEEN q_from_date AND q_to_date ) )
) IS NOT NULL THEN
'Starter'
/* Starter logic added to identify rehire employees */
/*Added by Rajitha */
WHEN (
SELECT
COUNT(*)
FROM
WHERE
) > 1
AND (
SELECT
MAX(date_start)
FROM
WHERE
) BETWEEN l_from_date AND l_to_date THEN
'Starter'
/*Leaver logic as per RFCHCM2016033*/
WHEN (
SELECT
FROM
WHERE
1 = 1
) IS NOT NULL THEN
'Leaver'
WHEN ( ( ppf.payroll_id = (
SELECT
payroll_id
FROM
WHERE
SELECT
FROM
WHERE
)
AND ( ( effective_start_date <= q_to_date
AND last_update_date BETWEEN l_from_date AND
l_to_date )
AND effective_start_date BETWEEN l_from_date AND
l_to_date ) )
) )
AND ( ppf.attribute1 <> (
SELECT
attribute1
FROM
pay_payrolls_f
WHERE
payroll_id IN (
SELECT
payroll_id
FROM
WHERE
AND effective_end_date = (
SELECT
MAX(effective_start_date) - 1
FROM
WHERE
)
AND ( ( effective_end_date <= q_to_date
AND last_update_date BETWEEN l_from_date
AND l_to_date )
AND effective_end_date BETWEEN
l_from_date AND l_to_date ) )
)
effective_end_date
) ) ) THEN
'IN'
WHEN
--ppf.payroll_id <> (
( ppf.attribute1 <> (
SELECT
attribute1
FROM
pay_payrolls_f
WHERE
payroll_id IN (
SELECT
payroll_id
FROM
WHERE
SELECT
FROM
WHERE
)
AND effective_start_date BETWEEN q_from_date AND
l_to_date
)
effective_end_date
) ) THEN
'OUT'
ELSE
NULL
END
) starter_or_leaver,
papf.person_id,
replace(
) sur_name,
replace(
) middle_name,
replace(
) fornames,
papf.title title,
to_char(
nvl(
), 'YYYY-MM-DD'
) start_date,
to_char(
) dob,
/*Below Logic as per RFCHCM2016033*/

(
SELECT
to_char(
)
FROM
WHERE
1 = 1
) company_end_date,
NULL tel_number,
decode(
) gender,
(
CASE
WHEN instr(
) > 0 THEN
NULL
WHEN instr(
) > 0 THEN
NULL
ELSE
(
SELECT
papf2.email_address
FROM
WHERE
'[a-zA-Z0-9._%-]+@[a-zA-Z0-9._%-]+\.[a-zA-Z]
{2,4}' )
AND trunc(
nvl(
)
)
END
) email_address,
--null Email_Address, commented by Rajitha

nvl(
) city,
nvl(
(
SELECT
FROM
fnd_territories_tl
WHERE
AND language = 'US'
), address.country
) country,
(
CASE
ELSE
( l_payroll_name )
END
) payroll_to_join,
(
SELECT
to_char(
)
FROM
WHERE
NULL payroll_join_date,
-- Made null for RFCHCM2016033

RFCHCM2016033
(
SELECT
to_char(
)
FROM
WHERE
AND actual_termination_date >= trunc(
nvl(
)
)

(
CASE
WHEN (
SELECT
FROM
WHERE
1 = 1
) IS NOT NULL THEN
(
SELECT
to_char(
)
FROM
WHERE
1 = 1
)
ELSE
/*(select to_char(max(effective_end_date), 'YYYY-
MM-DD')
from per_all_assignments_f
where person_id=papf.person_id
and primary_flag='Y'
and payroll_id<>(select payroll_id
and primary_flag='Y'
and effective_start_date=(select
max(effective_start_date)
and primary_flag='Y'))
) */
decode(
(
SELECT
to_char(
MAX(effective_end_date), 'YYYY-MM-DD'
)
FROM
WHERE
AND payroll_id = ppf.payroll_id
), to_char(
'4712-12-31'
), NULL,(
SELECT
to_char(
MAX(effective_end_date), 'YYYY-MM-DD'
)
FROM
WHERE
AND payroll_id = ppf.payroll_id
)
)
END
(
SELECT DISTINCT
bank_name
FROM
cefv_bank_branches
WHERE
) bank_name,
(
SELECT DISTINCT
bank_branch_number
FROM
cefv_bank_branches
WHERE
) bank_number,
-- Added By Anil
--ppinf.pei_information4 bank_account_number,
nvl(
RITM0198449
(
SELECT
meaning
FROM
hr_lookups hl
WHERE
) marital_status,
'YYYY-MM-DD'
)
BETWEEN change_date

current month
(
SELECT
to_char(
)
FROM
per_pay_proposals
WHERE
AND ( ( trunc(
nvl(
)
) )
OR ( ( nvl(
AND date_to = (
SELECT
MAX(date_to)
FROM
per_pay_proposals
WHERE
)
AND approved = 'Y'
(
CASE
( nvl(
(
SELECT
to_number(
)
FROM
g
WHERE
1 = 1
),(
SELECT
proposed_salary_n
FROM
per_pay_proposals
WHERE
AND trunc(
nvl(
)
)
AND approved = 'Y'
)
) )
BETWEEN change_date
AND approved = 'Y')
END

month
ELSE
(
SELECT
proposed_salary_n
FROM
per_pay_proposals
WHERE
AND ( ( trunc(
nvl(
)
) )
OR ( ( nvl(
AND date_to = (
SELECT
MAX(date_to)
FROM
per_pay_proposals
WHERE
)
AND approved = 'Y'
)
END
) annual_salary,
AND TRUNC(nvl(to_date(:l_effective_date), sysdate))
BETWEEN change_date AND NVL(date_to,'31-DEC-4712')
AND approved='Y'
) Annual_Salary,*/
(
CASE
( nvl(
(
SELECT
to_char(
)
FROM
g
WHERE
1 = 1
),(substr(
pb.name, - 3
))
) )
ELSE
substr(
pb.name, - 3
)
END
) salary_currency,
nvl(
ppd.segment1, ''
40 hours_per_week,
replace(
pj.name, '..', NULL
) job_title,
(
SELECT DISTINCT
full_name
FROM
per_all_people_f
WHERE
AND trunc(
nvl(
)
) line_manager,
(
SELECT
meaning
FROM
hr_lookups
WHERE
(
SELECT DISTINCT
t.description
FROM
fnd_flex_values b,
fnd_flex_value_sets s,
hr_soft_coding_keyflex hsc1,
WHERE
'LANG'
)
AND paaf1.soft_coding_keyflex_id = hsc1.soft_coding_keyflex_id
AND trunc(
nvl(
)
) BETWEEN paaf1.effective_start_date AND
paaf1.effective_end_date
-- Added By Subhashree
AND paaf1.person_id = papf.person_id
AND flex_value = hsc1.segment1
AND paaf1.primary_flag = 'Y'
) legal, --Added by pradeep
/*hsc.segment1 Legal_Entity_Code,--Replaced hsc.segment1

with ASS_Attribute under RFC --Commented by Subhashree */
(
SELECT
hsc1.segment1
FROM
hr_soft_coding_keyflex hsc1,
per_all_assignments_f paaf
WHERE
paaf.soft_coding_keyflex_id = hsc1.soft_coding_keyflex_id
AND trunc(
nvl(
)
AND paaf.person_id = papf.person_id
AND paaf.primary_flag = 'Y'
) legal_entity_code, --Added by pradeep
/**** Norway person type OF/ON*** Added by Nandini for

Norwagian Payroll****RITM0174520********/
decode(
) onshore,
(
CASE
WHEN
decode(
) IN ( 'OFFSHORE' )
AND (
SELECT
FROM
WHERE
) > 1 THEN
(
SELECT
to_char(
)
FROM
WHERE
AND EXISTS (
SELECT
1
FROM
WHERE
)
)
WHEN
decode(
) IN ( 'OFFSHORE' )
AND (
SELECT
FROM
WHERE
) = 1 THEN
(
SELECT
to_char(
)
FROM
WHERE
)
WHEN
decode(
) IN ( 'ONSHORE' )
AND (
SELECT
FROM
WHERE
) > 1 THEN
(
SELECT
to_char(
)
FROM
WHERE
AND EXISTS (
SELECT
1
FROM
WHERE
)
)
WHEN
decode(
) IN ( 'ONSHORE' )
AND (
SELECT
FROM
WHERE
) = 1 THEN
(
SELECT
to_char(
)
FROM
WHERE
)
ELSE
NULL
END
) off_on_effdt,
off_on_effdt,
(
SELECT
location_code
FROM
hr_locations_all
WHERE
) rig_location,
(
SELECT
meaning
FROM
hr_lookups
WHERE
pg.name grade,
(
SELECT
user_status
FROM
WHERE
(
SELECT
FROM
WHERE
AND ROWNUM < 2
) passport,
(
SELECT
meaning
FROM
hr_lookups hl
WHERE
) nationality,
nvl(

(
CASE
(
CASE
WHEN trunc(
nvl(
)
to_char(
nvl(
), 'YYYY-MM-DD'
)
WHEN trunc(
nvl(
)
(
SELECT
to_char(
)
FROM
WHERE
69, 55089, 325095 )
AND trunc(
) >= TO_DATE('04-01-2011', 'mm-dd-yyyy')
)
ELSE
NULL
END
)
ELSE
NULL
END
) no_le_st_dt,

(
CASE
AND trunc(
to_char(
)
ELSE
NULL
END
) no_ass_effdt,

Payroll****SOW025********/
(
CASE
ELSE
NULL
END
) no_reg,

Payroll****SOW025********/
(
CASE
ELSE
NULL
END
) no_intrinn,
(
SELECT
to_char(
), 'YYYY-MM-DD'
)
FROM
WHERE
AND ROWNUM = 1

(
CASE
( decode(
hsc.segment1, 702, '01', 904, '02'
)
|| '.'
)
)
|| '.'
)
)
|| '.'
)
) )
ELSE
NULL
END
) br_codigo -- Added By
Anil
-- Added By
Subhashree 21st Mar-2018*/
,
FROM
per_pay_bases pb,
per_grades pg,
per_jobs pj,
(
SELECT
person_id,
address_line1,
address_line2,
address_line3,
town_or_city,
region_2 state,
postal_code,
country
FROM
per_addresses
WHERE
primary_flag = 'Y'
AND trunc(
nvl(
)
)
) address,
(
SELECT
pei_information17,
pei_information16,
pei_information4,
pei_information11,
pei_information12,
pei_information6,
pei_information3,
pei_information2,
person_id
FROM
WHERE
AND nvl(
to_date(
), nvl(
)
) >= trunc(
nvl(
)
)
) ppinf
WHERE
AND (
CASE
--WHEN (ppf.payroll_id <> -- commented by Naveed
on 08-Nov-2019 for INC0718027
WHEN ( ppf.payroll_id NOT IN -- added by Naveed on 08-Nov-
2019 for INC0718027
(
SELECT
payroll_id
FROM
WHERE
SELECT
FROM
WHERE
)
) )
AND paaf.effective_end_date BETWEEN trunc(
nvl(
to_date(q_from_date), sysdate
)
) AND trunc(
nvl(
to_date(l_to_date), sysdate
)
)
OR ( trunc(
nvl(
)
AND paaf.effective_end_date <> to_date(
'31-DEC-4712'
) )
/*added to fetch future payroll changes of more
than 2 months */
/*Added by Rajitha */ THEN
'VALID'
--WHEN (ppf.payroll_id = -- commented by Naveed on
08-Nov-2019 for INC0718027
WHEN ( ppf.payroll_id IN -- added by Naveed on 08-Nov-2019 for
INC0718027
(
SELECT
payroll_id
FROM
WHERE
SELECT
FROM
WHERE
)
) )
AND paaf.effective_start_date BETWEEN trunc(
nvl(
to_date(l_from_date), sysdate
)
) AND trunc(
nvl(
)
)
AND paaf.effective_end_date = to_date(
'31-DEC-4712'
) THEN
'VALID'
--Added to resolve the issue, and pick employees with
future dated assignment changes APART FROM PAYROLL changes
--WHEN (ppf.payroll_id = -- Commented by Naveed on 08-
Nov-2019 for INC0718027
WHEN ( ppf.payroll_id IN -- added by Naveed on 08-Nov-2019 for
INC0718027
(
SELECT
payroll_id
FROM
WHERE
SELECT
FROM
WHERE
)
) )
AND trunc(
nvl(
)
AND paaf.effective_end_date <> to_date(
'31-DEC-4712'
) THEN
'VALID'
ELSE
'INVALID'
END
) = 'VALID'
--AND TRUNC(nvl(to_date(:l_effective_date), sysdate)) BETWEEN
paaf.effective_start_date AND paaf.effective_end_date--to be removed
AND trunc(
nvl(
)
AND trunc(
nvl(
)
AND trunc(
nvl(
)
) BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND (
CASE
WHEN ( pps.actual_termination_date IS NOT NULL )
AND pps.actual_termination_date BETWEEN l_from_date AND
l_to_date THEN
'INVALID'
WHEN papf.original_date_of_hire > q_to_date THEN
'INVALID'
ELSE
'VALID'
END
) = 'VALID'
UNION
SELECT
'Leaver' starter_or_leaver,
papf.person_id,
replace(
) sur_name,
replace(
) middle_name,
replace(
) fornames,
papf.title title,
to_char(
nvl(
), 'YYYY-MM-DD'
) start_date,
to_char(
) dob,
(
SELECT
to_char(
)
FROM
WHERE
1 = 1
) company_end_date,
NULL tel_number,
decode(
) gender,
(
CASE
WHEN instr(
) > 0 THEN
NULL
WHEN instr(
) > 0 THEN
NULL
ELSE
(
SELECT
papf2.email_address
FROM
WHERE
'[a-zA-Z0-9._%-]+@[a-zA-Z0-9._%-]+\.[a-zA-Z]
{2,4}' )
AND trunc(
nvl(
)
)
END
) email_address,
--null Email_Address, commented by Rajitha

nvl(
) city,
nvl(
(
SELECT
FROM
fnd_territories_tl
WHERE
AND language = 'US'
), address.country
) country,
(
CASE
ELSE
( l_payroll_name )
END
) payroll_to_join,
(
SELECT
to_char(
)
FROM
WHERE
RFCHCM2016033
RFCHCM2016033
(
SELECT
to_char(
)
FROM
WHERE

(
SELECT
to_char(
)
FROM
WHERE
1 = 1
(
SELECT DISTINCT
bank_name
FROM
cefv_bank_branches
WHERE
) bank_name,
(
SELECT DISTINCT
bank_branch_number
FROM
cefv_bank_branches
WHERE
) bank_number,
-- Added By Anil
nvl(
RITM0198449
(
SELECT
meaning
FROM
hr_lookups hl
WHERE
) marital_status,
'YYYY-MM-DD'
)
BETWEEN change_date

current month
(
SELECT
to_char(
)
FROM
per_pay_proposals
WHERE
AND ( ( trunc(
nvl(
)
) )
OR ( ( nvl(
AND date_to = (
SELECT
MAX(date_to)
FROM
per_pay_proposals
WHERE
)
AND approved = 'Y'
(
CASE
( nvl(
(
SELECT
to_number(
)
FROM
g
WHERE
1 = 1
),(
SELECT
proposed_salary_n
FROM
per_pay_proposals
WHERE
AND trunc(
nvl(
)
)
AND approved = 'Y'
)
) )
BETWEEN change_date
AND approved = 'Y')
END

month
ELSE
(
SELECT
proposed_salary_n
FROM
per_pay_proposals
WHERE
AND ( ( trunc(
nvl(
)
) )
OR ( ( nvl(
AND date_to = (
SELECT
MAX(date_to)
FROM
per_pay_proposals
WHERE
)
AND approved = 'Y'
)
END
) annual_salary,
AND approved='Y'
) Annual_Salary,*/
(
CASE
( nvl(
(
SELECT
to_char(
)
FROM
g
WHERE
1 = 1
),(substr(
pb.name, - 3
))
) )
ELSE
substr(
pb.name, - 3
)
END
) salary_currency,
nvl(
ppd.segment1, ''
40 hours_per_week,
replace(
pj.name, '..', NULL
) job_title,
(
SELECT DISTINCT
full_name
FROM
per_all_people_f
WHERE
AND trunc(
nvl(
)
) line_manager,
(
SELECT
meaning
FROM
hr_lookups
WHERE
(
SELECT
t.description
FROM
fnd_flex_values b,
WHERE
'LANG'
)
) legal,
hsc.segment1,
--DECODE(ppg.segment2, 'ON', 'Onshore', 'OFF',
'Offshore', NULL) Onshore,

Payroll****RITM0174520********/
decode(

(
CASE
WHEN
decode(
) IN ( 'OFFSHORE' )
AND (
SELECT
FROM
WHERE
) > 1 THEN
(
SELECT
to_char(
)
FROM
WHERE
AND EXISTS (
SELECT
1
FROM
WHERE
)
)
WHEN
decode(
) IN ( 'OFFSHORE' )
AND (
SELECT
FROM
WHERE
) = 1 THEN
(
SELECT
to_char(
)
FROM
WHERE
)
WHEN
decode(
) IN ( 'ONSHORE' )
AND (
SELECT
FROM
WHERE
) > 1 THEN
(
SELECT
to_char(
)
FROM
WHERE
AND EXISTS (
SELECT
1
FROM
WHERE
)
)
WHEN
decode(
) IN ( 'ONSHORE' )
AND (
SELECT
FROM
WHERE
) = 1 THEN
(
SELECT
to_char(
)
FROM
WHERE
)
ELSE
NULL
END
) off_on_effdt,
off_on_effdt,
(
SELECT
location_code
FROM
hr_locations_all
WHERE
) rig_location,
(
SELECT
meaning
FROM
hr_lookups
WHERE
pg.name grade,
(
SELECT
user_status
FROM
WHERE
(
SELECT
FROM
WHERE
AND ROWNUM < 2
) passport,
(
SELECT
meaning
FROM
hr_lookups hl
WHERE
) nationality,
nvl(

(
CASE
(
CASE
WHEN trunc(
nvl(
)
to_char(
nvl(
), 'YYYY-MM-DD'
)
WHEN trunc(
nvl(
)
(
SELECT
to_char(
)
FROM
WHERE
69, 55089, 325095 )
AND trunc(
) >= TO_DATE('04-01-2011', 'mm-dd-yyyy')
)
ELSE
NULL
END
)
ELSE
NULL
END
) no_le_st_dt,

(
CASE
AND trunc(
to_char(
)
ELSE
NULL
END
) no_ass_effdt,

Payroll****SOW025********/
(
CASE
ELSE
NULL
END
) no_reg,

Payroll****SOW025********/
(
CASE
ELSE
NULL
END
) no_intrinn,
(
SELECT
to_char(
), 'YYYY-MM-DD'
)
FROM
WHERE
AND ROWNUM = 1

(
CASE
( decode(
hsc.segment1, 702, '01', 904, '02'
)
|| '.'
)
)
|| '.'
)
)
|| '.'
)
) )
ELSE
NULL
END
) br_codigo --
Added By Anil
-- Added By
,
FROM
per_pay_bases pb,
per_grades pg,
per_jobs pj,
(
SELECT
person_id,
address_line1,
address_line2,
address_line3,
town_or_city,
region_2 state,
postal_code,
country
FROM
per_addresses
WHERE
primary_flag = 'Y'
AND trunc(
nvl(
)
)
) address,
(
SELECT
pei_information17,
pei_information16,
pei_information4,
pei_information11,
pei_information12,
pei_information6,
pei_information3,
pei_information2,
person_id
FROM
WHERE
AND nvl(
to_date(
), nvl(
)
) >= trunc(
nvl(
)
)
) ppinf
WHERE
AND papf.person_id = pps.person_id
AND trunc(
pps.actual_termination_date
AND trunc(
AND trunc(
AND pps.last_update_date BETWEEN l_from_date AND l_to_date
AND pps.actual_termination_date <= l_to_date
UNION
SELECT
'Leaver' starter_or_leaver,
papf.person_id,
replace(
) sur_name,
replace(
) middle_name,
replace(
) fornames,
papf.title title,
to_char(
nvl(
), 'YYYY-MM-DD'
) start_date,
to_char(
) dob,
(
SELECT
to_char(
)
FROM
WHERE
1 = 1
AND actual_termination_date BETWEEN l_from_date AND l_to_date
AND last_update_date <= l_to_date
) company_end_date,
NULL tel_number,
decode(
) gender,
(
CASE
WHEN instr(
) > 0 THEN
NULL
WHEN instr(
) > 0 THEN
NULL
ELSE
(
SELECT
papf2.email_address
FROM
WHERE
'[a-zA-Z0-9._%-]+@[a-zA-Z0-9._%-]+\.[a-zA-Z]
{2,4}' )
AND trunc(
nvl(
)
)
END
) email_address,
--null Email_Address, Commented by Rajitha

nvl(
) city,
nvl(
(
SELECT
FROM
fnd_territories_tl
WHERE
AND language = 'US'
), address.country
) country,
(
CASE
ELSE
( l_payroll_name )
END
) payroll_to_join,
(
SELECT
to_char(
)
FROM
WHERE
RFCHCM2016033

RFCHCM2016033
(
SELECT
to_char(
)
FROM
WHERE

(
SELECT
to_char(
)
FROM
WHERE
1 = 1
(
SELECT DISTINCT
bank_name
FROM
cefv_bank_branches
WHERE
) bank_name,
(
SELECT DISTINCT
bank_branch_number
FROM
cefv_bank_branches
WHERE
) bank_number,
-- Added By Anil
nvl(
RITM0198449
(
SELECT
meaning
FROM
hr_lookups hl
WHERE
) marital_status,
'YYYY-MM-DD'
)
BETWEEN change_date

current month
(
SELECT
to_char(
)
FROM
per_pay_proposals
WHERE
AND ( ( trunc(
nvl(
)
) )
OR ( ( nvl(
AND date_to = (
SELECT
MAX(date_to)
FROM
per_pay_proposals
WHERE
)
AND approved = 'Y'
(
CASE
( nvl(
(
SELECT
to_number(
)
FROM
g
WHERE
1 = 1
),(
SELECT
proposed_salary_n
FROM
per_pay_proposals
WHERE
AND trunc(
nvl(
)
)
AND approved = 'Y'
)
) )
BETWEEN change_date
AND approved = 'Y')
END

month
ELSE
(
SELECT
proposed_salary_n
FROM
per_pay_proposals
WHERE
AND ( ( trunc(
nvl(
)
) )
OR ( ( nvl(
AND date_to = (
SELECT
MAX(date_to)
FROM
per_pay_proposals
WHERE
)
AND approved = 'Y'
)
END
) annual_salary,
AND approved='Y'
) Annual_Salary,*/
(
CASE
( nvl(
(
SELECT
to_char(
)
FROM
g
WHERE
1 = 1
),(substr(
pb.name, - 3
))
) )
ELSE
substr(
pb.name, - 3
)
END
) salary_currency,
nvl(
ppd.segment1, ''
40 hours_per_week,
replace(
pj.name, '..', NULL
) job_title,
(
SELECT DISTINCT
full_name
FROM
per_all_people_f
WHERE
AND trunc(
nvl(
)
) line_manager,
(
SELECT
meaning
FROM
hr_lookups
WHERE
(
SELECT
t.description
FROM
fnd_flex_values b,
WHERE
'LANG'
)
) legal,
--DECODE(ppg.segment2, 'ON',
'Onshore', 'OFF', 'Offshore', NULL) Onshore,
Payroll****RITM0174520********/
decode(

(
CASE
WHEN
decode(
) IN ( 'OFFSHORE' )
AND (
SELECT
FROM
WHERE
) > 1 THEN
(
SELECT
to_char(
)
FROM
WHERE
AND EXISTS (
SELECT
1
FROM
WHERE
)
)
WHEN
decode(
) IN ( 'OFFSHORE' )
AND (
SELECT
FROM
WHERE
) = 1 THEN
(
SELECT
to_char(
)
FROM
WHERE
)
WHEN
decode(
) IN ( 'ONSHORE' )
AND (
SELECT
FROM
WHERE
) > 1 THEN
(
SELECT
to_char(
)
FROM
WHERE
AND EXISTS (
SELECT
1
FROM
WHERE
)
)
WHEN
decode(
) IN ( 'ONSHORE' )
AND (
SELECT
FROM
WHERE
) = 1 THEN
(
SELECT
to_char(
)
FROM
WHERE
)
ELSE
NULL
END
) off_on_effdt,
off_on_effdt,
(
SELECT
location_code
FROM
hr_locations_all
WHERE
) rig_location,
(
SELECT
meaning
FROM
hr_lookups
WHERE
pg.name grade,
(
SELECT
user_status
FROM
WHERE
(
SELECT
FROM
WHERE
AND ROWNUM < 2
) passport,
(
SELECT
meaning
FROM
hr_lookups hl
WHERE
) nationality,
nvl(

(
CASE
(
CASE
WHEN trunc(
nvl(
)
to_char(
nvl(
), 'YYYY-MM-DD'
)
WHEN trunc(
nvl(
)
(
SELECT
to_char(
)
FROM
WHERE
69, 55089, 325095 )
AND trunc(
) >= TO_DATE('04-01-2011', 'mm-dd-yyyy')
)
ELSE
NULL
END
)
ELSE
NULL
END
) no_le_st_dt,

(
CASE
AND trunc(
to_char(
)
ELSE
NULL
END
) no_ass_effdt,

Payroll****SOW025********/
(
CASE
ELSE
NULL
END
) no_reg,

Payroll****SOW025********/
(
CASE
ELSE
NULL
END
) no_intrinn,
(
SELECT
to_char(
), 'YYYY-MM-DD'
)
FROM
WHERE
AND ROWNUM = 1

(
CASE
( decode(
hsc.segment1, 702, '01', 904, '02'
)
|| '.'
)
)
|| '.'
)
)
|| '.'
)
) )
ELSE
NULL
END
) br_codigo --
Added By Anil
-- Added By
,
FROM
per_pay_bases pb,
per_grades pg,
per_jobs pj,
(
SELECT
person_id,
address_line1,
address_line2,
address_line3,
town_or_city,
region_2 state,
postal_code,
country
FROM
per_addresses
WHERE
primary_flag = 'Y'
AND trunc(
nvl(
)
)
) address,
(
SELECT
pei_information17,
pei_information16,
pei_information4,
pei_information11,
pei_information12,
pei_information6,
pei_information3,
pei_information2,
person_id
FROM
WHERE
AND nvl(
to_date(
), nvl(
)
) >= trunc(
nvl(
)
)
) ppinf
WHERE
AND papf.person_id = pps.person_id
AND trunc(
AND trunc(
AND trunc(
AND pps.actual_termination_date BETWEEN l_from_date AND l_to_date
AND pps.last_update_date < trunc(
nvl(
)
)
ORDER BY
employee_number,
payroll_leave_date DESC;
CURSOR g_payroll_names IS
SELECT
payroll_name
FROM
pay_all_payrolls_f
WHERE
1 = 1
AND attribute1 = p_payroll_group
AND nvl(
) BETWEEN effective_start_date AND effective_end_date;
/* CURSOR l_pay_group IS
select attribute1 from
(
select attribute1,count(payroll_name)count_payroll
from pay_payrolls_f
where trunc(sysdate) between effective_start_date and effective_end_date and
attribute1 is not null group by attribute1
)
where count_payroll > 1; */
BEGIN
l_effective_date := fnd_date.canonical_to_date(p_effective_date);
SELECT
16
|| '-'
|| ( to_char(
add_months(
nvl(
), - 1
), 'MON-YYYY'
) )
INTO p_from_date
FROM
dual;
SELECT
15
|| '-'
|| ( to_char(
nvl(
), 'MON-YYYY'
) )
INTO p_to_date
FROM
dual;
SELECT
1
|| '-'
|| ( to_char(
add_months(
nvl(
), - 1
), 'MON-YYYY'
) )
INTO q_from_date
FROM
dual;
SELECT
20
|| '-'
|| ( to_char(
nvl(
), 'MON-YYYY'
) )
INTO q_to_date
FROM
dual;
SELECT
'01'
|| '-'
|| to_char(
nvl(
), 'MON-YYYY'
)
INTO l_from_date
FROM
dual;
SELECT
last_day(
nvl(
)
)
INTO l_to_date
FROM
dual;
--SELECT substr(value,0,instr(value,',')-1) into l_file_path FROM

V$PARAMETER WHERE NAME LIKE '%utl_file%';
/*SELECT directory_path
INTO l_file_path
FROM dba_directories
WHERE directory_name = 'ODPDIR'; */
--- Commented out by
TCS on 1st Jul'21
SELECT
directory_path
INTO l_file_path
FROM
dba_directories
WHERE
directory_name = 'XXSDL_SAFEGUARD'; --- Added by TCS on 14th
Jul'21
fnd_file.put_line(
fnd_file.log,
'XXSDL_SAFEGUARD Directory Path - ' || l_file_path
); --- Added by TCS on 14th Jul'21
SELECT
name
INTO l_db_name
FROM
v$pdbs; --- Modified by TCS on 06-JUL-2021
SELECT
*
INTO l_pay_group
FROM
(
SELECT
(
CASE
WHEN (
SELECT
attribute1
FROM
(
SELECT
attribute1,
COUNT(payroll_name) count_payroll
FROM
pay_payrolls_f
WHERE
trunc(sysdate) BETWEEN effective_start_date
AND effective_end_date
AND attribute1 IS NOT NULL
GROUP BY
attribute1
)
WHERE
count_payroll > 1
AND attribute1 = p_payroll_group
) IS NULL THEN
'XX'
ELSE
p_payroll_group
END
)
FROM
dual
);
--validation for NID dups and bank dups before fetch---

/*SELECT payroll_name
INTO l_val_pay_name
FROM pay_all_payrolls_f
WHERE attribute1=p_payroll_group
AND nvl(l_effective_date, sysdate) BETWEEN effective_start_date AND
effective_end_date;*/
--FND_FILE.PUT_LINE(FND_FILE.LOG, 'Entering: THIS IS A TEST');

IF ( p_payroll_name IS NULL ) THEN
FOR l_pay_name IN g_payroll_names LOOP
fnd_file.put_line(
fnd_file.log,
'+*****************************' ||
'**********************************************+'
);
fnd_file.put_line(
fnd_file.log,
' ' || 'REPORT'
);
fnd_file.put_line(
fnd_file.log,
'+*****************************' ||
'**********************************************+'
);
l_emp_val := check_residential_address(l_pay_name.payroll_name);
-- if (l_emp_val= 1) then
-- --dbms_output.put_line('Test');
-- FND_FILE.PUT_LINE(FND_FILE.LOG, 'Within loop: Employees
with Non Residential Address Type Records Found');
-- end if;
IF ( l_emp_val = 1 ) THEN
--dbms_output.put_line('Test');
fnd_file.put_line(
fnd_file.log,
' '
);
fnd_file.put_line(
fnd_file.log,
'+*****************************' ||
'**********************************************+'
);
fnd_file.put_line(
fnd_file.log,
' ' || 'END OF
REPORT'
);
fnd_file.put_line(
fnd_file.log,
'+*****************************' ||
'**********************************************+'
);
END IF;
l_nid_val := validate_dups(l_pay_name.payroll_name);
l_bank_val := validate_bank_dups(l_pay_name.payroll_name);
fnd_file.put_line(
fnd_file.log,
'+*****************************' ||
'**********************************************+'
);
IF ( l_nid_val <> 0 ) THEN
p_retcode := 2;
p_errbuff := 'The program has completed with error, please
check the log file for further details';
RETURN;
ELSIF ( l_nid_val = -1 ) THEN
p_retcode := 1;
p_errbuff := 'The program has completed with warning, please
END IF;
END LOOP;
/* ELSE
--FND_FILE.PUT_LINE(FND_FILE.LOG, 'Entering Check Residential Address '||

p_payroll_name );
l_nid_val := validate_dups (p_payroll_name);

l_bank_val :=
validate_bank_dups (p_payroll_name,q_from_date, l_to_date,
l_effective_date);
IF (l_nid_val <> 0)
THEN
p_retcode := 2;
p_errbuff :=
'The program has completed with error, please check the log file for
further details';
RETURN;
ELSIF (l_nid_val = -1)
THEN
p_retcode := 1;
p_errbuff :=
'The program has completed with warning, please check the log file
for further details';
END IF; */
END IF;
IF ( p_payroll_name IS NOT NULL ) THEN

FOR l_pay_name IN g_payroll_names LOOP
fnd_file.put_line(
fnd_file.log,
'+*****************************' ||
'**********************************************+'
);
fnd_file.put_line(
fnd_file.log,
' ' || 'REPORT'
);
fnd_file.put_line(
fnd_file.log,
'+*****************************' ||
'**********************************************+'
);
l_emp_val := check_residential_address(l_pay_name.payroll_name);
-- FND_FILE.PUT_LINE(FND_FILE.LOG, 'Within loop: Employees
with Non Residential Address Type Records Found');
-- end if;
IF ( l_emp_val = 1 ) THEN
--dbms_output.put_line('Test');
fnd_file.put_line(
fnd_file.log,
' '
);
fnd_file.put_line(
fnd_file.log,
'+*****************************' ||
'**********************************************+'
);
fnd_file.put_line(
fnd_file.log,
' ' || 'END OF
REPORT'
);
fnd_file.put_line(
fnd_file.log,
'+*****************************' ||
'**********************************************+'
);
END IF;
-- l_emp_val := check_residential_address (l_pay_name.payroll_name);
-- FND_FILE.PUT_LINE(FND_FILE.LOG, 'Within loop: THIS IS A
TEST');
--
-- end if;
l_nid_val := validate_dups(l_pay_name.payroll_name);
l_bank_val := validate_bank_dups(l_pay_name.payroll_name);
fnd_file.put_line(
fnd_file.log,
'+*****************************' ||
'**********************************************+'
);
IF ( l_nid_val <> 0 ) THEN
p_retcode := 2;
p_errbuff := 'The program has completed with error, please
RETURN;
ELSIF ( l_nid_val = -1 ) THEN
p_retcode := 1;
p_errbuff := 'The program has completed with warning, please
END IF;
END LOOP;
/* ELSE
--FND_FILE.PUT_LINE(FND_FILE.LOG, 'Entering Check Residential Address '||

p_payroll_name );

l_bank_val :=
validate_bank_dups (p_payroll_name,q_from_date, l_to_date,
l_effective_date);
IF (l_nid_val <> 0)
THEN
p_retcode := 2;
p_errbuff :=
'The program has completed with error, please check the log file for
further details';
RETURN;
ELSIF (l_nid_val = -1)
THEN
p_retcode := 1;
p_errbuff :=
'The program has completed with warning, please check the log file
for further details';
END IF; */
END IF;
/*IF (p_payroll_name IS NULL)

THEN
FOR l_pay_name IN g_payroll_names
LOOP
l_nid_val := validate_dups (l_pay_name.payroll_name);
l_bank_val :=
validate_bank_dups (l_pay_name.payroll_name,
q_from_date,
l_to_date
);
IF (l_nid_val <> 0 OR l_bank_val <> 0)

THEN
p_retcode := 0;
p_errbuff :=
'Please check the log file for further details';
RETURN;
ELSIF (l_nid_val = -1 OR l_bank_val = -1)
THEN
p_retcode := 0;
p_errbuff :=
END IF;
END LOOP;
ELSE
l_bank_val :=
validate_bank_dups (p_payroll_name, q_from_date, l_to_date);

THEN
p_retcode := 0;
p_errbuff :=
RETURN;
ELSIF (l_nid_val = -1 OR l_bank_val = -1)
THEN
p_retcode := 0;
p_errbuff :=
END IF;
IF (p_payroll_name IS NULL)
THEN
FOR l_pay_name IN g_payroll_names
LOOP
l_nid_val := validate_dups (l_pay_name.payroll_name);
l_bank_val :=
validate_bank_dups (l_pay_name.payroll_name,
q_from_date,
l_to_date
);

THEN
p_retcode := 0;
p_errbuff :=
RETURN;
begin
FND_FILE.PUT_LINE(FND_FILE.LOG, 'THIS IS A TEST');
end;
END IF;
END LOOP;
END IF;*/
------------------------------------------**Logic for payrolls starts

here**------------------------------------
IF ( p_payroll_group = 'NO' ) THEN
IF p_flag = 'Y' THEN
l_file := nvl(
p_payroll_name,
p_payroll_group
)
|| '_Payroll_Master_D'
|| '_'
|| to_char(
nvl(
l_effective_date,
sysdate
),
'yyyy-MM-dd'
)
|| '.csv';
l_file_handle := utl_file.fopen(
l_file_path,
l_file,
'W'
);
utl_file.put_line(
l_file_handle,
utl_raw.cast_to_varchar2(utl_raw.convert(
utl_raw.cast_to_raw(nvl(
p_payroll_name,
p_payroll_group
|| ' Master Data'),
'AMERICAN_AMERICA.JA16SJISTILDE',
'AMERICAN_AMERICA.AL32UTF8'
))
);
utl_file.put_line(
l_file_handle,
utl_raw.cast_to_raw('--------------------------------------------------------------
------------'),
))
);
utl_file.put_line(
l_file_handle,
utl_raw.cast_to_raw('employee_number'
|| ','
|| 'starter_or_leaver'
|| ','
|| 'Fornames'
|| ','
|| 'Middle_Name'
|| ','
|| 'Sur_Name'
|| ','
|| 'Title'
|| ','
|| 'Start_Date'
|| ','
|| 'DOB'
|| ','
|| 'Company_End_DATE'
|| ','
|| 'Tel_number'
|| ','
|| 'Gender'
|| ','
|| 'Email_Address'
|| ','
|| 'Address Type'
|| ','
|| 'Address1'
|| ','
|| 'Address2'
|| ','
|| 'Address3'
|| ','
|| 'City'
|| ','
|| 'State'
|| ','
|| 'Zip_Code'
|| ','
|| 'Country'
|| ','
|| 'Payroll_to_Join'
|| ','
|| 'Payroll_start_Date'
|| ','
|| 'Payroll_join_date'
|| ','
|| 'Payroll_End_date'
|| ','
|| 'Payroll_leave_date'
|| ','
|| 'Bank_Name'
|| ','
|| 'Bank_Number'
|| ','
|| 'ACH_Routing_Number'
|| ','
|| 'Bank_Account_Number'
|| ','
|| 'BIC_Swift'
|| ','
|| 'IBAN'
|| ','
|| 'Payee_Name'
|| ','
|| 'Marital_status'
|| ','
|| 'Salary_Effective_Date'
|| ','
|| 'Annual_Salary'
|| ','
|| 'Salary_Currency'
|| ','
|| 'Hours_Per_Week'
|| ','
|| 'Job_Title'
|| ','
|| 'Line_Manager'
|| ','
|| 'FTE'
|| ','
|| 'Parttime_or_fulltime'
|| ','
|| 'Entitlement_Daily_Rate'
|| ','
|| 'Entitlement_Hourly_Rate'
|| ','
|| 'Legal'
|| ','
|| 'Legal_Entity_Code'
|| ','
|| 'Onshore'
|| ','
|| 'Off_On_Effdt' --added by Nandini
|| ',' --added by Nandini
|| 'Organization'
|| ','
|| 'Profit_Centre'
|| ','
|| 'Region'
|| ','
|| 'Rig_Name'
|| ','
|| 'Rig_Type'
|| ','
|| 'Rig_Location'
|| ','
|| 'Employee_Category'
|| ','
|| 'Grade'
|| ','
|| 'Employment_Status'
|| ','
|| 'Passport'
|| ','
|| 'Nationality'
|| ','
|| 'BR_Codigo'
|| ','
|| 'NO_UPN' -- added by Naveed on 05-feb-2019
|| ','
|| 'NO_LE_ST_DT' ---- added by Nandini
|| ','
|| 'NO_ASS_EFFDT' ---- added by Nandini
|| ','
|| 'NO_REG' ---- added by Nandini
|| ','
|| 'NO_INTRINN' ---- added by Nandini
|| ',' ---- added by Nandini
|| 'COLLECTIVE_BARGAINING_DATE' ---- added by Srikkanth M
|| ','
|| 'Life_Cycle'
|| ','
|| 'Paying_Legal_Entity_Code'),
))
);
END IF;
/*-- If payroll group is given and payroll name is null--*/

IF p_payroll_name IS NULL THEN
FOR i IN (
SELECT
payroll_name,
business_group_id,
attribute2,
payroll_id
FROM
pay_all_payrolls_f
WHERE
attribute1 = p_payroll_group
AND nvl(
) LOOP
fnd_file.put_line(
fnd_file.output,
i.payroll_name || ' Master Data'
);
fnd_file.put_line(
fnd_file.output,
'--------------------------------------------------------------------------'
);
fnd_file.put_line(
fnd_file.output,
'employee_number'
|| ','
|| ','
|| 'Fornames'
|| ','
|| 'Middle_Name'
|| ','
|| 'Sur_Name'
|| ','
|| 'Title'
|| ','
|| 'Start_Date'
|| ','
|| 'DOB'
|| ','
|| ','
|| 'Tel_number'
|| ','
|| 'Gender'
|| ','
|| 'Email_Address'
|| ','
|| 'Address Type'
|| ','
|| 'Address1'
|| ','
|| 'Address2'
|| ','
|| 'Address3'
|| ','
|| 'City'
|| ','
|| 'State'
|| ','
|| 'Zip_Code'
|| ','
|| 'Country'
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| 'Bank_Name'
|| ','
|| 'Bank_Number'
|| ','
|| ','
|| ','
|| 'BIC_Swift'
|| ','
|| 'IBAN'
|| ','
|| 'Payee_Name'
|| ','
|| 'Marital_status'
|| ','
|| ','
|| 'Annual_Salary'
|| ','
|| ','
|| 'Hours_Per_Week'
|| ','
|| 'Job_Title'
|| ','
|| 'Line_Manager'
|| ','
|| 'FTE'
|| ','
|| ','
|| ','
|| ','
|| 'Legal'
|| ','
|| ','
|| 'Onshore'
|| ','
|| 'Organization'
|| ','
|| 'Profit_Centre'
|| ','
|| 'Region'
|| ','
|| 'Rig_Name'
|| ','
|| 'Rig_Type'
|| ','
|| 'Rig_Location'
|| ','
|| ','
|| 'Grade'
|| ','
|| ','
|| 'Passport'
|| ','
|| 'Nationality'
|| ','
|| 'Social_Security_Number'
|| ','
|| 'BR_Codigo'
|| ','
|| ','
|| ','
|| ','
|| ','
|| 'COLLECTIVE_BARGAINING_DATE' ---- added
by Srikkanth M
|| ',' ---- added by
Srikkanth M
|| 'Life_Cycle'
|| ','
|| 'Paying_Legal_Entity_Code'
);
FOR l_sgwi_payroll IN c_sgwi_payroll(
p_from_date,
p_to_date,
l_from_date,
l_to_date,
q_from_date,
q_to_date,
i.payroll_name,
i.business_group_id,
i.attribute2,
l_effective_date,
i.payroll_id
) LOOP
IF
p_flag = 'Y'
AND p_payroll_group != l_pay_group
THEN
l_output := l_sgwi_payroll.employee_number
|| ','
|| l_sgwi_payroll.starter_or_leaver
|| ','
|| l_sgwi_payroll.fornames
|| ','
|| l_sgwi_payroll.middle_name
|| ','
|| l_sgwi_payroll.sur_name
|| ','
|| l_sgwi_payroll.title
|| ','
|| l_sgwi_payroll.start_date
|| ','
|| l_sgwi_payroll.dob
|| ','
|| l_sgwi_payroll.company_end_date
|| ','
|| l_sgwi_payroll.tel_number
|| ','
|| l_sgwi_payroll.gender
|| ','
|| replace(
replace(
l_sgwi_payroll.email_address,
chr(10),
''
),
chr(13),
''
)
|| ','
|| l_sgwi_payroll.address_type
|| ','
|| replace(
l_sgwi_payroll.address1,
',',
''
)
|| ','
|| replace(
',',
''
)
|| ','
|| replace(
',',
''
)
|| ','
|| replace(
l_sgwi_payroll.city,
',',
''
)
|| ','
|| replace(
l_sgwi_payroll.state,
',',
''
)
|| ','
|| replace(
l_sgwi_payroll.zip_code,
',',
''
)
|| ','
|| replace(
l_sgwi_payroll.country,
',',
''
)
|| ','
|| replace(
l_sgwi_payroll.payroll_to_join,
' ',
'-'
)
|| ','
|| l_sgwi_payroll.payroll_start_date
|| ','
|| l_sgwi_payroll.payroll_join_date
|| ','
|| l_sgwi_payroll.payroll_end_date
|| ','
|| l_sgwi_payroll.payroll_leave_date
|| ','
|| ' '
|| ','
|| ' '
|| ','
|| ' '
|| ','
|| ' '
|| ','
--|| ' ' -- commented by
naveed on 11-feb-2019 for UPN changes
|| l_sgwi_payroll.bic_swift -- added by
|| ','
|| l_sgwi_payroll.iban --
added by naveed on 11-feb-2019 for UPN changes
|| ','
|| ' '
|| ','
|| l_sgwi_payroll.marital_status
|| ','
|| l_sgwi_payroll.salary_effective_date
|| ','
|| l_sgwi_payroll.annual_salary
|| ','
|| l_sgwi_payroll.salary_currency
|| ','
|| l_sgwi_payroll.hours_per_week
|| ','
|| replace(
l_sgwi_payroll.job_title,
',',
''
)
|| ','
|| replace(
l_sgwi_payroll.line_manager,
',',
''
)
|| ','
|| l_sgwi_payroll.fte
|| ','
|| l_sgwi_payroll.parttime_or_fulltime
|| ','
|| l_sgwi_payroll.entitlement_daily_rate
|| ','
|| l_sgwi_payroll.entitlement_hourly_rate
|| ','
|| replace(
l_sgwi_payroll.legal,
',',
''
)
|| ','
|| l_sgwi_payroll.legal_entity_code
|| ','
|| l_sgwi_payroll.onshore
|| ','
|| l_sgwi_payroll.off_on_effdt -- added by
Nandini
|| replace(
l_sgwi_payroll.organization,
',',
''
)
|| ','
|| l_sgwi_payroll.profit_centre
|| ','
|| replace(
l_sgwi_payroll.region,
',',
''
)
|| ','
|| replace(
l_sgwi_payroll.rig_name,
',',
''
)
|| ','
|| replace(
l_sgwi_payroll.rig_type,
',',
''
)
|| ','
|| replace(
l_sgwi_payroll.rig_location,
',',
''
)
|| ','
|| l_sgwi_payroll.employee_category
|| ','
|| l_sgwi_payroll.grade
|| ','
|| l_sgwi_payroll.assignment_status
|| ','
|| l_sgwi_payroll.passport
|| ','
|| l_sgwi_payroll.nationality
|| ','
|| l_sgwi_payroll.social_security_number
|| ','
|| l_sgwi_payroll.br_codigo
|| ','
|| l_sgwi_payroll.upn -- added by Naveed
on 05-feb-2019
|| ','
|| l_sgwi_payroll.no_le_st_dt ---- added
by Nandini for Norwegian payroll
|| ',' ---- added by Nandini for
Norwegian payroll
|| l_sgwi_payroll.no_ass_effdt ---- added
Norwegian payroll
|| l_sgwi_payroll.no_reg ---- added
Norwegian payroll
|| l_sgwi_payroll.no_intrinn ---- added
Norwegian payroll
||
l_sgwi_payroll.collective_bargaining_date ---- added by Srikkanth M for Norwegian
payroll
|| ',' ---- added by Srikkanth M
for Norwegian payroll
|| l_sgwi_payroll.life_cycle
|| ' '
|| chr(13);
utl_file.put_line(
l_file_handle,
utl_raw.cast_to_raw(l_output),
))
);
END IF;
l_output1 := l_sgwi_payroll.employee_number
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| replace(
replace(
chr(10),
''
),
chr(13),
''
)
|| ','
|| ','
|| replace(
',',
''
)
|| ','
|| replace(
',',
''
)
|| ','
|| replace(
',',
''
)
|| ','
|| replace(
',',
''
)
|| ','
|| replace(
',',
''
)
|| ','
|| replace(
',',
''
)
|| ','
|| replace(
',',
''
)
|| ','
|| replace(
' ',
'-'
)
|| ','
|| ','
|| ','
|| ','
|| ','
|| replace(
l_sgwi_payroll.bank_name,
',',
''
)
|| ','
|| l_sgwi_payroll.bank_number
|| ','
|| ' '
|| ','
|| l_sgwi_payroll.bank_account_number
|| ','
--|| ' ' -- commented by naveed
on 11-feb-2019 for UPN changes
|| ','
--|| ' ' -- commented by naveed
|| l_sgwi_payroll.iban -- added by naveed
|| ','
|| ' '
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| replace(
',',
''
)
|| ','
|| replace(
',',
''
)
|| ','
|| ','
|| ','
|| ','
|| ','
|| replace(
',',
''
)
|| ','
|| ','
|| ','
Nandini
|| replace(
',',
''
)
|| ','
|| ','
|| replace(
',',
''
)
|| ','
|| replace(
',',
''
)
|| ','
|| replace(
',',
''
)
|| ','
|| replace(
',',
''
)
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| l_sgwi_payroll.upn -- added by Naveed on
05-feb-2019
|| ','
|| l_sgwi_payroll.no_le_st_dt ---- added by
Nandini for Norwegian payroll
Norwegian payroll
|| l_sgwi_payroll.no_ass_effdt ---- added by
Norwegian payroll
|| l_sgwi_payroll.no_reg ---- added by
Norwegian payroll
|| l_sgwi_payroll.no_intrinn ---- added by
Norwegian payroll
|| l_sgwi_payroll.collective_bargaining_date
---- added by Srikkanth M for Norwegian payroll
|| ',' ---- added by Srikkanth M for
Norwegian payroll
|| ','
|| l_sgwi_payroll.paying_legal_entity
|| chr(13);
--Writing the File
fnd_file.put_line(
fnd_file.output,
l_output1
);
END LOOP;
fnd_file.put_line(
fnd_file.output,
'--------------------------------------------------------------------------'
|| chr(13)
|| chr(13)
);
END LOOP;
/*--If payroll name is null and pay group has multiple payrolls --*/
/*--Included to reorder the output file sent to Safeguard such that
employees leaving the payroll will appear in the end of the report--*/
IF
p_payroll_group = l_pay_group
AND p_flag = 'Y'
THEN
EXECUTE IMMEDIATE 'TRUNCATE TABLE
XXSDL.XXSDL_NO_SGWI_PAY_MASTER_STG';
FOR i_payroll IN (
SELECT
payroll_name,
business_group_id,
attribute2,
payroll_id
FROM
pay_all_payrolls_f
WHERE
AND nvl(
) BETWEEN effective_start_date AND
effective_end_date
) LOOP
p_from_date,
p_to_date,
l_from_date,
l_to_date,
q_from_date,
q_to_date,
i_payroll.payroll_name,
i_payroll.business_group_id,
i_payroll.attribute2,
l_effective_date,
i_payroll.payroll_id
) LOOP
INSERT INTO xxsdl_no_sgwi_pay_master_stg (
employee_number,
starter_or_leaver,
fornames,
middle_name,
sur_name,
title,
start_date,
dob,
company_end_date,
tel_number,
gender,
email_address,
address_type,
address1,
address2,
address3,
city,
state,
zip_code,
country,
payroll_to_join,
payroll_start_date,
payroll_join_date,
payroll_end_date,
payroll_leave_date,
bank_name,
bank_number,
ach_routing_number,
bank_account_number,
bic_swift,
iban,
payee_name,
marital_status,
salary_effective_date,
annual_salary,
salary_currency,
hours_per_week,
job_title,
line_manager,
fte,
parttime_or_fulltime,
entitlement_daily_rate,
entitlement_hourly_rate,
legal,
legal_entity_code,
onshore,
off_on_effdt, -- added by Nandini
organization,
profit_centre,
region,
rig_name,
rig_type,
rig_location,
employee_category,
grade,
employment_status,
passport,
nationality,
social_security_number,
br_codigo,
upn, -- added by Naveed on 05-feb-2019
no_le_st_dt, --- added by Nandini
no_ass_effdt,--- added by Nandini
no_reg, --- added by Nandini
no_intrinn, --- added by Nandini
collective_bargaining_date,
life_cycle
) VALUES (
l_sgwi_payroll.employee_number,
l_sgwi_payroll.starter_or_leaver,
l_sgwi_payroll.fornames,
l_sgwi_payroll.middle_name,
l_sgwi_payroll.sur_name,
l_sgwi_payroll.title,
l_sgwi_payroll.start_date,
l_sgwi_payroll.dob,
l_sgwi_payroll.company_end_date,
l_sgwi_payroll.tel_number,
l_sgwi_payroll.gender,
l_sgwi_payroll.address_type,
l_sgwi_payroll.payroll_start_date,
l_sgwi_payroll.payroll_join_date,
l_sgwi_payroll.payroll_end_date,
l_sgwi_payroll.payroll_leave_date,
l_sgwi_payroll.bank_number,
l_sgwi_payroll.ach_routing_number,
l_sgwi_payroll.bank_account_number,
l_sgwi_payroll.bic_swift,
l_sgwi_payroll.iban,
l_sgwi_payroll.payee_name,
l_sgwi_payroll.marital_status,
l_sgwi_payroll.salary_effective_date,
l_sgwi_payroll.annual_salary,
l_sgwi_payroll.salary_currency,
l_sgwi_payroll.hours_per_week,
l_sgwi_payroll.fte,
l_sgwi_payroll.parttime_or_fulltime,
l_sgwi_payroll.entitlement_daily_rate,
l_sgwi_payroll.entitlement_hourly_rate,
l_sgwi_payroll.legal_entity_code,
l_sgwi_payroll.onshore,
l_sgwi_payroll.off_on_effdt, -- added by
Nandini
l_sgwi_payroll.profit_centre,
l_sgwi_payroll.employee_category,
l_sgwi_payroll.grade,
l_sgwi_payroll.assignment_status,
l_sgwi_payroll.passport,
l_sgwi_payroll.nationality,
l_sgwi_payroll.social_security_number,
l_sgwi_payroll.br_codigo, -- Added By Anil
l_sgwi_payroll.upn, -- added by Naveed on 05-
feb-2019
l_sgwi_payroll.no_le_st_dt, ---- added by
l_sgwi_payroll.no_ass_effdt, ---- added by
l_sgwi_payroll.no_reg, ---- added by
l_sgwi_payroll.no_intrinn, ---- added by
l_sgwi_payroll.collective_bargaining_date,
l_sgwi_payroll.life_cycle
);
END LOOP;
COMMIT;
END LOOP;
FOR l_sgwi IN (
SELECT DISTINCT
*
FROM
xxsdl_no_sgwi_pay_master_stg
ORDER BY
employee_number,
payroll_leave_date
) LOOP
IF l_sgwi.br_codigo IS NOT NULL THEN
l_output := l_sgwi.employee_number
|| ','
|| l_sgwi.starter_or_leaver
|| ','
|| l_sgwi.fornames
|| ','
|| l_sgwi.middle_name
|| ','
|| l_sgwi.sur_name
|| ','
|| l_sgwi.title
|| ','
|| l_sgwi.start_date
|| ','
|| l_sgwi.dob
|| ','
|| l_sgwi.company_end_date
|| ','
|| l_sgwi.tel_number
|| ','
|| l_sgwi.gender
|| ','
|| replace(
l_sgwi.email_address,
chr(10),
''
)
|| ','
|| l_sgwi.address_type
|| ','
|| replace(
l_sgwi.address1,
',',
''
)
|| ','
|| replace(
l_sgwi.address2,
',',
''
)
|| ','
|| replace(
l_sgwi.address3,
',',
''
)
|| ','
|| replace(
l_sgwi.city,
',',
''
)
|| ','
|| replace(
l_sgwi.zip_code,
',',
''
)
|| ','
|| replace(
l_sgwi.country,
',',
''
)
|| ','
|| replace(
l_sgwi.payroll_to_join,
' ',
'-'
)
|| ','
|| l_sgwi.payroll_start_date
|| ','
|| l_sgwi.payroll_join_date
|| ','
|| l_sgwi.payroll_end_date
|| ','
|| l_sgwi.payroll_leave_date
|| ','
|| l_sgwi.bank_name
|| ','
|| l_sgwi.bank_number
|| ','
|| l_sgwi.ach_routing_number
|| ','
|| l_sgwi.bank_account_number
|| ','
|| l_sgwi.bic_swift
|| ','
|| l_sgwi.iban
|| ','
|| l_sgwi.payee_name
|| ','
|| l_sgwi.marital_status
|| ','
|| l_sgwi.salary_effective_date
|| ','
|| l_sgwi.annual_salary
|| ','
|| l_sgwi.salary_currency
|| ','
|| l_sgwi.hours_per_week
|| ','
|| replace(
l_sgwi.job_title,
',',
''
)
|| ','
|| replace(
l_sgwi.line_manager,
',',
''
)
|| ','
|| l_sgwi.fte
|| ','
|| l_sgwi.parttime_or_fulltime
|| ','
|| l_sgwi.entitlement_daily_rate
|| ','
|| l_sgwi.entitlement_hourly_rate
|| ','
|| replace(
l_sgwi.legal,
',',
''
)
|| ','
|| l_sgwi.legal_entity_code
|| ','
|| l_sgwi.onshore
|| ','
|| l_sgwi.off_on_effdt -- added by
Nandini
|| ',' -- added by Nandini
|| replace(
l_sgwi.organization,
',',
''
)
|| ','
|| l_sgwi.profit_centre
|| ','
|| replace(
l_sgwi.region,
',',
''
)
|| ','
|| replace(
l_sgwi.rig_name,
',',
''
)
|| ','
|| replace(
l_sgwi.rig_type,
',',
''
)
|| ','
|| replace(
l_sgwi.rig_location,
',',
''
)
|| ','
|| l_sgwi.employee_category
|| ','
|| l_sgwi.grade
|| ','
|| l_sgwi.employment_status
|| ','
|| l_sgwi.passport
|| ','
|| l_sgwi.nationality
|| ','
|| l_sgwi.social_security_number
|| ','
|| l_sgwi.br_codigo
|| ','
|| l_sgwi.upn -- added by Naveed on
05-feb-2019
|| ','
|| l_sgwi.no_le_st_dt ---- added by
|| l_sgwi.no_ass_effdt ---- added by
|| l_sgwi.no_reg ---- added by
|| l_sgwi.no_intrinn ---- added by
|| l_sgwi.collective_bargaining_date
|| ',' ---- added by
Srikkanth M for Norwegian payroll
|| l_sgwi.life_cycle
|| ','
|| ' '
|| chr(13);
ELSE
l_output := l_sgwi.employee_number
|| ','
|| l_sgwi.starter_or_leaver
|| ','
|| l_sgwi.fornames
|| ','
|| l_sgwi.middle_name
|| ','
|| l_sgwi.sur_name
|| ','
|| l_sgwi.title
|| ','
|| l_sgwi.start_date
|| ','
|| l_sgwi.dob
|| ','
|| l_sgwi.company_end_date
|| ','
|| l_sgwi.tel_number
|| ','
|| l_sgwi.gender
|| ','
|| replace(
l_sgwi.email_address,
chr(10),
''
)
|| ','
|| l_sgwi.address_type
|| ','
|| replace(
l_sgwi.address1,
',',
''
)
|| ','
|| replace(
l_sgwi.address2,
',',
''
)
|| ','
|| replace(
l_sgwi.address3,
',',
''
)
|| ','
|| replace(
l_sgwi.city,
',',
''
)
|| ','
|| replace(
l_sgwi.state,
',',
''
)
|| ','
|| replace(
l_sgwi.zip_code,
',',
''
)
|| ','
|| replace(
l_sgwi.country,
',',
''
)
|| ','
|| replace(
l_sgwi.payroll_to_join,
' ',
'-'
)
|| ','
|| l_sgwi.payroll_start_date
|| ','
|| l_sgwi.payroll_join_date
|| ','
|| l_sgwi.payroll_end_date
|| ','
|| l_sgwi.payroll_leave_date
|| ','
|| ' '
|| ','
|| ' '
|| ','
|| ' '
|| ','
|| ' '
|| ','
-- || ' ' --
commented by naveed on 11-feb-2019 for UPN changes
|| l_sgwi.bic_swift --
|| ','
--|| ' ' --
commented by naveed on 11-feb-2019 for UPN changes
|| l_sgwi.iban --
|| ','
|| ' '
|| ','
|| l_sgwi.marital_status
|| ','
|| l_sgwi.salary_effective_date
|| ','
|| l_sgwi.annual_salary
|| ','
|| l_sgwi.salary_currency
|| ','
|| l_sgwi.hours_per_week
|| ','
|| replace(
l_sgwi.job_title,
',',
''
)
|| ','
|| replace(
l_sgwi.line_manager,
',',
''
)
|| ','
|| l_sgwi.fte
|| ','
|| l_sgwi.parttime_or_fulltime
|| ','
|| l_sgwi.entitlement_daily_rate
|| ','
|| l_sgwi.entitlement_hourly_rate
|| ','
|| replace(
l_sgwi.legal,
',',
''
)
|| ','
|| l_sgwi.legal_entity_code
|| ','
|| l_sgwi.onshore
|| ','
|| l_sgwi.off_on_effdt -- added by
Nandini
|| ',' -- added by Nandini
|| replace(
l_sgwi.organization,
',',
''
)
|| ','
|| l_sgwi.profit_centre
|| ','
|| replace(
l_sgwi.region,
',',
''
)
|| ','
|| replace(
l_sgwi.rig_name,
',',
''
)
|| ','
|| replace(
l_sgwi.rig_type,
',',
''
)
|| ','
|| replace(
l_sgwi.rig_location,
',',
''
)
|| ','
|| l_sgwi.employee_category
|| ','
|| l_sgwi.grade
|| ','
|| l_sgwi.employment_status
|| ','
|| l_sgwi.passport
|| ','
|| l_sgwi.nationality
|| ','
|| l_sgwi.social_security_number
|| ','
|| l_sgwi.br_codigo
|| ','
|| l_sgwi.upn -- added by Naveed on
05-feb-2019
|| ','
|| l_sgwi.no_le_st_dt ---- added by
|| l_sgwi.no_ass_effdt ---- added by
|| l_sgwi.no_reg ---- added by
|| l_sgwi.no_intrinn ---- added by
|| l_sgwi.collective_bargaining_date
|| ',' ---- added by
Srikkanth M for Norwegian payroll
|| l_sgwi.life_cycle
|| ','
|| ' '
|| chr(13);
END IF;
utl_file.put_line(
l_file_handle,
))
);
END LOOP;
END IF;
END IF;
/*--If payroll group and payroll name both are provided--*/
ELSE
fnd_file.put_line(
fnd_file.output,
p_payroll_name || ' Master Data'
);
fnd_file.put_line(
fnd_file.output,
'--------------------------------------------------------------------------'
);
fnd_file.put_line(
fnd_file.output,
'employee_number'
|| ','
|| ','
|| 'Fornames'
|| ','
|| 'Middle_Name'
|| ','
|| 'Sur_Name'
|| ','
|| 'Title'
|| ','
|| 'Start_Date'
|| ','
|| 'DOB'
|| ','
|| ','
|| 'Tel_number'
|| ','
|| 'Gender'
|| ','
|| 'Email_Address'
|| ','
|| 'Address Type'
|| ','
|| 'Address1'
|| ','
|| 'Address2'
|| ','
|| 'Address3'
|| ','
|| 'City'
|| ','
|| 'State'
|| ','
|| 'Zip_Code'
|| ','
|| 'Country'
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| 'Bank_Name'
|| ','
|| 'Bank_Number'
|| ','
|| ','
|| ','
|| 'BIC_Swift'
|| ','
|| 'IBAN'
|| ','
|| 'Payee_Name'
|| ','
|| 'Marital_status'
|| ','
|| ','
|| 'Annual_Salary'
|| ','
|| ','
|| 'Hours_Per_Week'
|| ','
|| 'Job_Title'
|| ','
|| 'Line_Manager'
|| ','
|| 'FTE'
|| ','
|| ','
|| ','
|| ','
|| 'Legal'
|| ','
|| ','
|| 'Onshore'
|| ','
|| 'Organization'
|| ','
|| 'Profit_Centre'
|| ','
|| 'Region'
|| ','
|| 'Rig_Name'
|| ','
|| 'Rig_Type'
|| ','
|| 'Rig_Location'
|| ','
|| ','
|| 'Grade'
|| ','
|| ','
|| 'Passport'
|| ','
|| 'Nationality'
|| ','
|| 'Social_Security_Number'
|| ','
|| 'BR_Codigo'
|| ','
|| ','
|| ','
|| ','
|| ','
|| 'COLLECTIVE_BARGAINING_DATE' ---- added by
Srikkanth M
|| 'Life_Cycle'
|| ','
|| 'Paying_Legal_Entity_Code'
);
FOR i IN (
SELECT
business_group_id,
attribute2,
payroll_id
FROM
pay_all_payrolls_f
WHERE
payroll_name = p_payroll_name
AND nvl(
) LOOP
p_from_date,
p_to_date,
l_from_date,
l_to_date,
q_from_date,
q_to_date,
p_payroll_name,
i.attribute2,
l_effective_date,
i.payroll_id
) LOOP
IF
p_flag = 'Y'
AND p_payroll_group = 'BR'
THEN
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| replace(
replace(
chr(10),
''
),
chr(13),
''
)
|| ','
|| ','
|| replace(
',',
''
)
|| ','
|| replace(
',',
''
)
|| ','
|| replace(
',',
''
)
|| ','
|| replace(
',',
''
)
|| ','
|| replace(
',',
''
)
|| ','
|| replace(
',',
''
)
|| ','
|| replace(
',',
''
)
|| ','
|| replace(
' ',
'-'
)
|| ','
|| ','
|| ','
|| ','
|| ','
|| l_sgwi_payroll.bank_name
|| ','
|| l_sgwi_payroll.bank_number
|| ','
|| l_sgwi_payroll.ach_routing_number
|| ','
|| l_sgwi_payroll.bank_account_number
|| ','
|| l_sgwi_payroll.bic_swift
|| ','
|| l_sgwi_payroll.iban
|| ','
|| l_sgwi_payroll.payee_name
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| replace(
',',
''
)
|| ','
|| replace(
',',
''
)
|| ','
|| ','
|| ','
|| ','
|| ','
|| replace(
',',
''
)
|| ','
|| ','
|| ','
Nandini
|| replace(
',',
''
)
|| ','
|| ','
|| replace(
',',
''
)
|| ','
|| replace(
',',
''
)
|| ','
|| replace(
',',
''
)
|| ','
|| replace(
',',
''
)
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
on 05-feb-2019
|| ','
Norwegian payroll
Norwegian payroll
Norwegian payroll
Norwegian payroll
||
payroll
|| ' '
|| chr(13);
utl_file.put_line(
l_file_handle,
))
);
ELSIF
p_flag = 'Y'
AND p_payroll_group <> 'BR'
THEN
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| replace(
replace(
chr(10),
''
),
chr(13),
''
)
|| ','
|| ','
|| replace(
',',
''
)
|| ','
|| replace(
',',
''
)
|| ','
|| replace(
',',
''
)
|| ','
|| replace(
',',
''
)
|| ','
|| replace(
',',
''
)
|| ','
|| replace(
',',
''
)
|| ','
|| replace(
',',
''
)
|| ','
|| replace(
' ',
'-'
)
|| ','
|| ','
|| ','
|| ','
|| ','
|| ' '
|| ','
|| ' '
|| ','
|| ' '
|| ','
|| ' '
|| ','
|| ','
|| l_sgwi_payroll.iban -- added by
|| ','
|| ' '
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| replace(
',',
''
)
|| ','
|| replace(
',',
''
)
|| ','
|| ','
|| ','
|| ','
|| ','
|| replace(
',',
''
)
|| ','
|| ','
|| ','
Nandini
|| replace(
',',
''
)
|| ','
|| ','
|| replace(
',',
''
)
|| ','
|| replace(
',',
''
)
|| ','
|| replace(
',',
''
)
|| ','
|| replace(
',',
''
)
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
on 05-feb-2019
|| ','
Norwegian payroll
Norwegian payroll
Norwegian payroll
Norwegian payroll
||
payroll
|| ' '
|| chr(13);
utl_file.put_line(
l_file_handle,
))
);
ELSE
GOTO print_output;
END IF;
-- modified by pradeep on 30th Nov 17 due to duplication issue
<< print_output >> l_output1 :=
l_sgwi_payroll.employee_number
|| ','
||
l_sgwi_payroll.starter_or_leaver
|| ','
|| ','
||
l_sgwi_payroll.middle_name
|| ','
|| ','
|| ','
||
l_sgwi_payroll.start_date
|| ','
|| ','
||
l_sgwi_payroll.company_end_date
|| ','
||
l_sgwi_payroll.tel_number
|| ','
|| ','
|| replace(
replace(
chr(10),
''
),
chr(13),
''
)
|| ','
||
l_sgwi_payroll.address_type
|| ','
|| replace(
',',
''
)
|| ','
|| replace(
',',
''
)
|| ','
|| replace(
',',
''
)
|| ','
|| replace(
',',
''
)
|| ','
|| replace(
',',
''
)
|| ','
|| replace(
',',
''
)
|| ','
|| replace(
',',
''
)
|| ','
|| replace(
' ',
'-'
)
|| ','
||
l_sgwi_payroll.payroll_start_date
|| ','
||
l_sgwi_payroll.payroll_join_date
|| ','
||
l_sgwi_payroll.payroll_end_date
|| ','
||
l_sgwi_payroll.payroll_leave_date
|| ','
|| replace(
',',
''
)
|| ','
||
l_sgwi_payroll.bank_number
|| ','
|| ' '
|| ','
||
l_sgwi_payroll.bank_account_number
|| ','
|| l_sgwi_payroll.bic_swift
-- added by naveed on 11-feb-2019 for UPN changes
|| ','
|| l_sgwi_payroll.iban
-- added by naveed on 11-feb-2019 for UPN changes
|| ','
|| ' '
|| ','
||
l_sgwi_payroll.marital_status
|| ','
||
l_sgwi_payroll.salary_effective_date
|| ','
||
l_sgwi_payroll.annual_salary
|| ','
||
l_sgwi_payroll.salary_currency
|| ','
||
l_sgwi_payroll.hours_per_week
|| ','
|| replace(
',',
''
)
|| ','
|| replace(
',',
''
)
|| ','
|| ','
||
l_sgwi_payroll.parttime_or_fulltime
|| ','
||
l_sgwi_payroll.entitlement_daily_rate
|| ','
||
l_sgwi_payroll.entitlement_hourly_rate
|| ','
|| replace(
',',
''
)
|| ','
||
l_sgwi_payroll.legal_entity_code
|| ','
|| ','
||
l_sgwi_payroll.off_on_effdt -- added by Nandini
|| replace(
',',
''
)
|| ','
||
l_sgwi_payroll.profit_centre
|| ','
|| replace(
',',
''
)
|| ','
|| replace(
',',
''
)
|| ','
|| replace(
',',
''
)
|| ','
|| replace(
',',
''
)
|| ','
||
l_sgwi_payroll.employee_category
|| ','
|| ','
||
l_sgwi_payroll.assignment_status
|| ','
|| ','
||
l_sgwi_payroll.nationality
|| ','
||
l_sgwi_payroll.social_security_number
|| ','
|| ','
|| l_sgwi_payroll.upn --
added by Naveed on 05-feb-2019
|| ','
||
l_sgwi_payroll.no_le_st_dt ---- added by Nandini for Norwegian payroll
|| ',' ---- added
||
l_sgwi_payroll.no_ass_effdt ---- added by Nandini for Norwegian payroll
|| ',' ---- added
|| l_sgwi_payroll.no_reg
---- added by Nandini for Norwegian payroll
|| ',' ---- added
||
l_sgwi_payroll.no_intrinn ---- added by Nandini for Norwegian payroll
|| ',' ---- added
||
payroll
|| ',' ---- added
by Srikkanth M for Norwegian payroll
||
l_sgwi_payroll.life_cycle
|| ','
||
l_sgwi_payroll.paying_legal_entity
|| chr(13);
--Writing the File
fnd_file.put_line(
fnd_file.output,
l_output1
);
--Writing the File -- to dsiplay the output in case of file
transfer
END LOOP;
END LOOP;
END IF;
utl_file.fclose(l_file_handle);
IF ( p_flag = 'Y' ) THEN
transfer_file(
'XXSDL_SAFEGUARD',
l_file,
'/PAYROLL/Safeguard/Outbound/'
|| l_db_name
|| '/'
|| l_file
); --Modified by TCS on 14th Jul,2021
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
log('The error is un-expected, please contact the technical support
representative to assist on this');
log('Error Code: ' || sqlcode);
log('Error Msg: ' || sqlerrm);
p_retcode := 2;
p_errbuff := 'Un-expected error occurred while fetching the Employee
Master Data, Please check the log file for details';
END xxsdl_no_sgwi_pay_masterdata_p;
---Elements Procedure start

PROCEDURE xxsdl_no_sgwi_pay_element_p (
p_flag IN VARCHAR2,
) IS
l_count NUMBER := 1;
l_file_path1 VARCHAR2(1000);
l_file_name VARCHAR2(200);
l_file1 VARCHAR2(100);
l_file_handle utl_file.file_type;
l_file_handle1 utl_file.file_type;
l_instance v$instance.instance_name%TYPE;
l_output VARCHAR2(30000);
l_output1 VARCHAR2(30000);
p_from_date DATE;
p_to_date DATE;
l_from_date DATE;
l_to_date DATE;
q_from_date DATE;
q_to_date DATE;
l_db_name VARCHAR2(20);
CURSOR c_sgwi_pay_ele (
p_from_date DATE,
p_to_date DATE,
q_from_date DATE,
q_to_date DATE,
l_payroll_name VARCHAR2,
l_business_group_id NUMBER,
l_secondary_payroll VARCHAR2,
l_effective_date DATE
) IS
SELECT
*
FROM
(
SELECT DISTINCT
*
FROM
( (
SELECT
replace(
) fornames,
replace(
) sur_name,
pet.element_name,
to_char(
pee.effective_start_date, 'YYYY-MM-DD'
) effective_start_date,
--TO_CHAR (pee.effective_end_date,
'YYYY-MM-DD' ) effective_end_date,
replace(
to_char(
pee.effective_end_date, 'YYYY-MM-DD'
), '4712-12-31', NULL
) effective_end_date,
--
MAX(DECODE(piv.name,xedsv.input_name1,piv.name)) input_name1,
xedsv.input_name1,
MAX(decode(
piv.name, xedsv.input_name1, pev.screen_entry_value
)) input_value1,
--
xedsv.input_name2,
MAX(decode(
)) input_value2,
--
xedsv.input_name3,
MAX(decode(
)) input_value3,
--
xedsv.input_name4,
MAX(decode(
)) input_value4,
--
xedsv.input_name5,
MAX(decode(
)) input_value5,
--
xedsv.input_name6,
(
CASE
WHEN ppd.segment7 IN ( 'Tender Barges', 'Semi-
tender' )
AND pet.element_name = 'Offshore Premium New'
THEN
NULL
ELSE
MAX(decode(
piv.name, xedsv.input_name6,
pev.screen_entry_value
))
END
) input_value6,
--
xedsv.input_name7,
MAX(decode(
)) input_value7,
--
MAX(DECODE(piv.name,xedsv.input_name8,piv.name)) input_name8 ,
xedsv.input_name8,
(
CASE
WHEN ppd.segment7 NOT IN ( 'Tender Barges', 'Semi-
tender' )
AND pet.element_name = 'Offshore Premium New'
THEN
NULL
ELSE
MAX(decode(
))
END
) input_value8,
--
MAX(DECODE(piv.name,xedsv.input_name9,piv.name)) input_name9 ,
xedsv.input_name9,
MAX(decode(
)) input_value9,
--
xedsv.input_name10,
MAX(decode(
)) input_value10,
--
xedsv.input_name11,
MAX(decode(
)) input_value11,
-- Added By Subhashree from

input_name12 to input_name20
xedsv.input_name12,
MAX(decode(
)) input_value12,
xedsv.input_name13,
MAX(decode(
)) input_value13,
xedsv.input_name14,
MAX(decode(
)) input_value14,
xedsv.input_name15,
MAX(decode(
)) input_value15,
xedsv.input_name16,
MAX(decode(
)) input_value16,
xedsv.input_name17,
MAX(decode(
)) input_value17,
xedsv.input_name18,
MAX(decode(
)) input_value18,
xedsv.input_name19,
MAX(decode(
)) input_value19,
xedsv.input_name20,
MAX(decode(
)) input_value20,
xedsv.input_name21,
MAX(decode(
)) input_value21,
xedsv.input_name22,
MAX(decode(
)) input_value22,
--**Columns not included in the

extract**--
ppd.segment3 onshore,
(
CASE
ELSE
( l_payroll_name )
END
) payroll_to_join,
(
SELECT
meaning
FROM
hr_lookups
WHERE
) employee_category
FROM
pay_element_entry_values_f pev,
pay_element_entries_f pee,
pay_element_types_f pet,
pay_input_values_f piv,
per_all_positions pap1,
hr_all_positions_f pap,
xxsdl_ele_det_sgwi_v xedsv,
pay_people_groups ppg
WHERE
pev.element_entry_id = pee.element_entry_id
AND pee.assignment_id = paaf.assignment_id
AND pev.input_value_id = piv.input_value_id
AND pee.element_type_id = pet.element_type_id
AND piv.element_type_id = pet.element_type_id
AND ppd.position_definition_id =
pap.position_definition_id
AND pap.position_id = paaf.position_id
AND nvl(
papf.current_employee_flag, 'N'
) = 'Y'
AND ppf.payroll_id = paaf.payroll_id
AND ppf.payroll_name = l_payroll_name
AND paaf.soft_coding_keyflex_id =
hsc.soft_coding_keyflex_id
AND pap1.position_definition_id =
ppd.position_definition_id
AND paaf.position_id = pap1.position_id
--AND piv.NAME
IN ('Amount', 'Currency', 'Car or Transport allowance', 'Fuel Allowance', 'Housing
Allowance', 'Premium Percentage', 'Geo Factor Percentage', 'Premium Amount',
'Allowance Amount', 'Cash pension Percentage of base Actual Value', 'Cash pension
Percentage of base', 'Cash pension Amount', 'Bonus Amount', 'Bonus Percentage')
--AND pet.element_name
IN ('Local Salary Deduction', 'Currency', 'Annual Salary', 'Annual Salary USD',
'Tender Bonus New', 'Car New', 'Housing New', 'Foreign Service Premium New', 'GEO
Factor New', 'Offshore Premium New', 'Travel Allowance New', 'Pension New')
AND pet.element_name = xedsv.element_name
--and
pet.business_group_id=:l_business_group_id
/*
AND TRUNC
(NVL
(TO_DATE (l_effective_date),
SYSDATE
)
)
BETWEEN papf.effective_start_date
AND papf.effective_end_date
*/
/*commented by subhashree*/
AND ( ( trunc(
nvl(
)
) BETWEEN papf.effective_start_date AND
papf.effective_end_date )
/*Added by Subhashree to include the
leavers record for the current running month*/
OR ( ( to_date(
papf.effective_end_date
AND ( ( trunc(
nvl(
)
) BETWEEN paaf.effective_start_date AND
paaf.effective_end_date )
/*Added by Subhashree to include the
leavers record for the current running month*/
OR ( ( to_date(
paaf.effective_end_date
) BETWEEN l_from_date AND l_to_date ) ) )
AND ( ( trunc(
nvl(
)
) BETWEEN pev.effective_start_date AND
pev.effective_end_date )
/*Added by Subhashree to
include the leavers record for the current running month*/
OR ( ( to_date(
pev.effective_end_date
AND ( ( trunc(
nvl(
)
) BETWEEN pee.effective_start_date AND
pee.effective_end_date )
/*Added by Subhashree to
include the leavers record for the current running month*/
OR ( ( to_date(
pee.effective_end_date
AND trunc(
nvl(
)
) BETWEEN pet.effective_start_date AND
pet.effective_end_date
AND trunc(
nvl(
)
) BETWEEN piv.effective_start_date AND
piv.effective_end_date
AND trunc(
nvl(
)
) BETWEEN ppf.effective_start_date AND
ppf.effective_end_date
/*****OF/ON logic added by Nandini******/

-- Removing Paramter p_person_location_type for Global
Payroll Project Commented by Srikkanth M
/*****OF/ON logic ends*****/

--*any new column added in the select,needs to
be put in the group by clause*--
GROUP BY
papf.first_name,
papf.last_name,
pet.element_name,
ppd.segment7,
to_char(
pee.effective_start_date, 'YYYY-MM-DD'
),
to_char(
pee.effective_end_date, 'YYYY-MM-DD'
),
input_name1,
input_name2,
input_name3,
input_name4,
input_name5,
input_name6,
input_name7,
input_name8,
input_name9,
input_name10,
input_name11,
input_name12,
input_name13,
input_name14,
input_name15,
input_name16,
input_name17,
input_name18,
input_name19,
input_name20,
input_name21,
input_name22,
hsc.segment1,
ppd.segment3,
ppg.segment4,
paaf.employee_category,
ppf.payroll_name
) )
ORDER BY
1,
4
);
BEGIN
l_effective_date := fnd_date.canonical_to_date(p_effective_date);
SELECT
16
|| '-'
|| ( to_char(
add_months(
nvl(
), - 1
), 'MON-YYYY'
) )
INTO p_from_date
FROM
dual;
SELECT
15
|| '-'
|| ( to_char(
nvl(
), 'MON-YYYY'
) )
INTO p_to_date
FROM
dual;
SELECT
10
|| '-'
|| ( to_char(
add_months(
nvl(
), - 1
), 'MON-YYYY'
) )
INTO q_from_date
FROM
dual;
SELECT
10
|| '-'
|| ( to_char(
nvl(
), 'MON-YYYY'
) )
INTO q_to_date
FROM
dual;
SELECT
'01'
|| '-'
|| to_char(
nvl(
), 'MON-YYYY'
)
INTO l_from_date
FROM
dual;
SELECT
last_day(
nvl(
)
)
INTO l_to_date
FROM
dual;
--SELECT substr(value,0,instr(value,',')-1) into l_file_path1 FROM

V$PARAMETER WHERE NAME LIKE '%utl_file%';
/* SELECT directory_path
INTO l_file_path1
FROM dba_directories
WHERE directory_name = 'ODPDIR'; */ --- Commented out by TCS on
1st Jul'21
SELECT
directory_path
INTO l_file_path1
FROM
dba_directories
WHERE
directory_name = 'XXSDL_SAFEGUARD'; --- Added by TCS on 14th Jul'21
fnd_file.put_line(
fnd_file.log,
'XXSDL_SAFEGUARD Directory Path - ' || l_file_path1
); --- Added by TCS on 14th Jul'21
SELECT
name
INTO l_db_name
FROM
------------------------------------------**Logic for payrolls starts

here**------------------------------------
IF ( p_payroll_group IN ( 'NO' ) ) THEN
l_file1 := nvl(
p_payroll_name,
p_payroll_group
)
|| '_SGWI_Payroll_Permanent_E_'
|| to_char(
nvl(
l_effective_date,
sysdate
),
'yyyy-MM-dd'
)
|| '.csv';
l_file_handle1 := utl_file.fopen(
l_file_path1,
l_file1,
'W'
);
utl_file.put_line(
l_file_handle1,
utl_raw.cast_to_raw(nvl(
p_payroll_name,
p_payroll_group
|| ' SGWI Payroll Permanent Entitlements'),
))
);
utl_file.put_line(
l_file_handle1,
utl_raw.cast_to_raw('--------------------------------------------------------------
------------'),
))
);
utl_file.put_line(
l_file_handle1,
utl_raw.cast_to_raw('employee_number'
|| ','
|| 'Fornames'
|| ','
|| 'Sur_Name'
|| ','
|| 'element_name'
|| ','
|| 'effective_start_date'
|| ','
|| 'effective_end_date'
|| ','
|| 'input_name1'
|| ','
|| 'input_value1'
|| ','
|| 'input_name2'
|| ','
|| 'input_value2'
|| ','
|| 'input_name3'
|| ','
|| 'input_value3'
|| ','
|| 'input_name4'
|| ','
|| 'input_value4'
|| ','
|| 'input_name5'
|| ','
|| 'input_value5'
|| ','
|| 'input_name6'
|| ','
|| 'input_value6'
|| ','
|| 'input_name7'
|| ','
|| 'input_value7'
|| ','
|| 'input_name8'
|| ','
|| 'input_value8'
|| ','
|| 'input_name9'
|| ','
|| 'input_value9'
|| ','
|| 'input_name10'
|| ','
|| 'input_value10'
|| ','
|| 'input_name11'
|| ','
|| 'input_value11'
|| ','
|| 'input_name12'
|| ','
|| 'input_value12'
|| ','
|| 'input_name13'
|| ','
|| 'input_value13'
|| ','
|| 'input_name14'
|| ','
|| 'input_value14'
|| ','
|| 'input_name15'
|| ','
|| 'input_value15'
|| ','
|| 'input_name16'
|| ','
|| 'input_value16'
|| ','
|| 'input_name17'
|| ','
|| 'input_value17'
|| ','
|| 'input_name18'
|| ','
|| 'input_value18'
|| ','
|| 'input_name19'
|| ','
|| 'input_value19'
|| ','
|| 'input_name20'
|| ','
|| 'input_value20'
|| ','
|| 'input_name21'
|| ','
|| 'input_value21'
|| ','
|| 'input_name22'
|| ','
|| 'input_value22'),
))
);
END IF;
/*-- If payroll group is given and payroll name is null--*/
FOR i IN (
SELECT
payroll_name,
business_group_id,
attribute2
FROM
pay_all_payrolls_f
WHERE
AND nvl(
) LOOP
fnd_file.put_line(
fnd_file.output,
i.payroll_name || ' Permanent Entitlements'
);
fnd_file.put_line(
fnd_file.output,
'--------------------------------------------------------------------------'
);
fnd_file.put_line(
fnd_file.output,
'employee_number'
|| ','
|| 'Fornames'
|| ','
|| 'Sur_Name'
|| ','
|| 'element_name'
|| ','
|| ','
|| ','
|| 'input_name1'
|| ','
|| 'input_value1'
|| ','
|| 'input_name2'
|| ','
|| 'input_value2'
|| ','
|| 'input_name3'
|| ','
|| 'input_value3'
|| ','
|| 'input_name4'
|| ','
|| 'input_value4'
|| ','
|| 'input_name5'
|| ','
|| 'input_value5'
|| ','
|| 'input_name6'
|| ','
|| 'input_value6'
|| ','
|| 'input_name7'
|| ','
|| 'input_value7'
|| ','
|| 'input_name8'
|| ','
|| 'input_value8'
|| ','
|| 'input_name9'
|| ','
|| 'input_value9'
|| ','
|| 'input_name10'
|| ','
|| 'input_value10'
|| ','
|| 'input_name11'
|| ','
|| 'input_value11'
|| ','
|| 'input_name12'
|| ','
|| 'input_value12'
|| ','
|| 'input_name13'
|| ','
|| 'input_value13'
|| ','
|| 'input_name14'
|| ','
|| 'input_value14'
|| ','
|| 'input_name15'
|| ','
|| 'input_value15'
|| ','
|| 'input_name16'
|| ','
|| 'input_value16'
|| ','
|| 'input_name17'
|| ','
|| 'input_value17'
|| ','
|| 'input_name18'
|| ','
|| 'input_value18'
|| ','
|| 'input_name19'
|| ','
|| 'input_value19'
|| ','
|| 'input_name20'
|| ','
|| 'input_value20'
|| ','
|| 'input_name21'
|| ','
|| 'input_value21'
|| ','
|| 'input_name22'
|| ','
|| 'input_value22'
);
FOR l_sgwi_pay_ele IN c_sgwi_pay_ele(

p_from_date,
p_to_date,
q_from_date,
q_to_date,
i.payroll_name,
i.attribute2,
l_effective_date
) LOOP
l_output := l_sgwi_pay_ele.employee_number
|| ','
|| l_sgwi_pay_ele.fornames
|| ','
|| l_sgwi_pay_ele.sur_name
|| ','
|| l_sgwi_pay_ele.element_name
|| ','
|| l_sgwi_pay_ele.effective_start_date
|| ','
|| l_sgwi_pay_ele.effective_end_date
|| ','
|| l_sgwi_pay_ele.input_name1
|| ','
|| l_sgwi_pay_ele.input_value1
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| chr(13);
utl_file.put_line(
l_file_handle1,
))
);
END IF;
l_output1 := l_sgwi_pay_ele.employee_number
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| chr(13);
fnd_file.put_line(
fnd_file.output,
l_output1
);
END LOOP;
fnd_file.put_line(
fnd_file.output,
'--------------------------------------------------------------------------'
|| chr(13)
|| chr(13)
);
END LOOP;
/*--If payroll group and payroll name both are provided--*/
ELSE
fnd_file.put_line(
fnd_file.output,
p_payroll_name || ' Permanent Entitlements'
);
fnd_file.put_line(
fnd_file.output,
'--------------------------------------------------------------------------'
);
fnd_file.put_line(
fnd_file.output,
'employee_number'
|| ','
|| 'Fornames'
|| ','
|| 'Sur_Name'
|| ','
|| 'element_name'
|| ','
|| ','
|| ','
|| 'input_name1'
|| ','
|| 'input_value1'
|| ','
|| 'input_name2'
|| ','
|| 'input_value2'
|| ','
|| 'input_name3'
|| ','
|| 'input_value3'
|| ','
|| 'input_name4'
|| ','
|| 'input_value4'
|| ','
|| 'input_name5'
|| ','
|| 'input_value5'
|| ','
|| 'input_name6'
|| ','
|| 'input_value6'
|| ','
|| 'input_name7'
|| ','
|| 'input_value7'
|| ','
|| 'input_name8'
|| ','
|| 'input_value8'
|| ','
|| 'input_name9'
|| ','
|| 'input_value9'
|| ','
|| 'input_name10'
|| ','
|| 'input_value10'
|| ','
|| 'input_name11'
|| ','
|| 'input_value11'
|| ','
|| 'input_name12'
|| ','
|| 'input_value12'
|| ','
|| 'input_name13'
|| ','
|| 'input_value13'
|| ','
|| 'input_name14'
|| ','
|| 'input_value14'
|| ','
|| 'input_name15'
|| ','
|| 'input_value15'
|| ','
|| 'input_name16'
|| ','
|| 'input_value16'
|| ','
|| 'input_name17'
|| ','
|| 'input_value17'
|| ','
|| 'input_name18'
|| ','
|| 'input_value18'
|| ','
|| 'input_name19'
|| ','
|| 'input_value19'
|| ','
|| 'input_name20'
|| ','
|| 'input_value20'
|| ','
|| 'input_name21'
|| ','
|| 'input_value21'
|| ','
|| 'input_name22'
|| ','
|| 'input_value22'
);
FOR i IN (
SELECT
business_group_id,
attribute2
FROM
pay_all_payrolls_f
WHERE
payroll_name = p_payroll_name
AND nvl(
) LOOP
FOR l_sgwi_pay_ele IN c_sgwi_pay_ele(
p_from_date,
p_to_date,
q_from_date,
q_to_date,
p_payroll_name,
i.attribute2,
l_effective_date
) LOOP
l_output := l_sgwi_pay_ele.employee_number
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| chr(13);
utl_file.put_line(
l_file_handle1,
))
);
END IF;
l_output1 := l_sgwi_pay_ele.employee_number
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| ','
|| chr(13);
fnd_file.put_line(
fnd_file.output,
l_output1
);
END LOOP;
END LOOP;
utl_file.fclose(l_file_handle1);
END IF;
SELECT
name
INTO l_db_name
FROM
IF ( p_flag = 'Y' ) THEN

transfer_file(
'XXSDL_SAFEGUARD',
l_file1,
'/PAYROLL/Safeguard/Outbound/'
|| l_db_name
|| '/'
|| l_file1
); --Modified by TCS on 14th Jul,2021
END IF;
END IF;
EXCEPTION
WHEN utl_file.invalid_path THEN
fnd_file.put_line(
fnd_file.log,
'ERROR: INVALID_PATH IN SUB-PROC ' || sqlerrm
);
utl_file.fclose_all;
p_retcode := 2;
p_errbuff := 'Un-expected error occurred while fetching the Permanent
Entitlements data, Please check the log file for details';
WHEN utl_file.invalid_mode THEN
fnd_file.put_line(
fnd_file.log,
'ERROR: INVALID_MODE IN SUB-PROC ' || sqlerrm
);
p_retcode := 2;
WHEN utl_file.invalid_filehandle THEN
fnd_file.put_line(
fnd_file.log,
'ERROR: INVALID_FILEHANDLE IN SUB-PROC ' || sqlerrm
);
p_retcode := 2;
WHEN utl_file.invalid_operation THEN
fnd_file.put_line(
fnd_file.log,
'ERROR: INVALID_OPERATION IN SUB-PROC ' || sqlerrm
);
p_retcode := 2;
WHEN utl_file.read_error THEN
fnd_file.put_line(
fnd_file.log,
'ERROR: READ_ERROR IN SUB-PROC ' || sqlerrm
);
p_retcode := 2;
WHEN utl_file.write_error THEN
fnd_file.put_line(
fnd_file.log,
'ERROR: WRITE_ERROR IN SUB-PROC ' || sqlerrm
);
p_retcode := 2;
WHEN utl_file.internal_error THEN
fnd_file.put_line(
fnd_file.log,
'ERROR: INTERNAL_ERROR IN SUB-PROC ' || sqlerrm
);
p_retcode := 2;
WHEN utl_file.invalid_maxlinesize THEN
fnd_file.put_line(
fnd_file.log,
'ERROR: INVALID_MAXLINESIZE IN SUB-PROC ' || sqlerrm
);
p_retcode := 2;
WHEN no_data_found THEN
fnd_file.put_line(
fnd_file.log,
'ERROR: NO_DATA_FOUND IN SUB-PROC ' || sqlerrm
);
p_retcode := 2;
WHEN value_error THEN
fnd_file.put_line(
fnd_file.log,
'ERROR: VALUE_ERROR IN SUB-PROC' || sqlerrm
);
p_retcode := 2;
WHEN OTHERS THEN
fnd_file.put_line(
fnd_file.log,
'ERROR: OTHERS IN SUB-PROC ' || sqlerrm
);
p_retcode := 2;
END xxsdl_no_sgwi_pay_element_p;
PROCEDURE transfer_file (
p_from VARCHAR2,
p_file VARCHAR2,
p_to VARCHAR2
) IS
l_conn utl_tcp.connection;
l_db VARCHAR2(250); --- added by TCS on 30th April 2021
l_tag VARCHAR2(100); --- added by TCS on 30th April 2021
l_host VARCHAR2(250); --- added by TCS on 30th April 2021
l_port NUMBER(20); --- added by TCS on 30th April 2021
l_user VARCHAR2(250); --- added by TCS on 30th April 2021
l_pass VARCHAR2(250); --- added by TCS on 30th April 2021
BEGIN
fnd_file.put_line(
fnd_file.log,
'Transfer:'
|| p_from
|| ':'
|| p_file
|| ':'
|| p_to
|| ':'
);
---- Below Part is added as part of DC migration Project by TCS on 30th

April 2021
BEGIN
SELECT
name
INTO l_db
FROM
EXCEPTION
fnd_file.put_line(
fnd_file.log,
'ERROR: NO_DATA_FOUND IN FETCHING INSTANCE DETAILS
' || sqlerrm
);
WHEN OTHERS THEN
fnd_file.put_line(
fnd_file.log,
'ERROR: UNEXPECTED ERROR IN FETCHING INSTANCE
DETAILS ' || sqlerrm
);
END;
IF l_db = 'EBSPROD' THEN

l_tag := 'EBSPROD';
ELSE
l_tag := 'NONPROD';
END IF;
BEGIN
SELECT
meaning,
attribute1,
attribute2,
attribute3
INTO
l_host,
l_user,
l_pass,
l_port
FROM
fnd_lookup_values
WHERE
lookup_type = 'XXSDL_FTP_SERVER'
AND language = 'US'
AND enabled_flag = 'Y'
AND end_date_active IS NULL
AND tag = l_tag;
EXCEPTION
fnd_file.put_line(
fnd_file.log,
'ERROR: NO_DATA_FOUND IN FTP LOOKUP ' || sqlerrm
);
WHEN OTHERS THEN
fnd_file.put_line(
fnd_file.log,
'ERROR: UNEXPECTED ERROR IN FETCHING FTP DETAILS '
|| sqlerrm
);
END;
fnd_file.put_line(
fnd_file.log,
'For Environment '
|| l_db
|| ', FTP Server fetched - '
|| l_host
);
BEGIN
l_conn := xxsdl_ftp.login(
p_host => l_host,
p_port => l_port,
p_user => l_user,
p_pass => l_pass
);
EXCEPTION
WHEN OTHERS THEN
l_conn := xxsdl_ftp.login(
p_host => l_host,
p_port => l_port,
p_user => l_user,
p_pass => l_pass
);
END;
/*l_conn :=
xxsdl_ftp.login ('sdrlhpprftp01p.corp.local', --- commented out by TCS
on 30th April 2021
'21',
'regnhk',
'ftp2nt'
); */
xxsdl_ftp.binary(p_conn => l_conn);
xxsdl_ftp.put(
p_conn => l_conn,
p_from_dir => p_from,
p_from_file => p_file,
p_to_file => p_to
);
xxsdl_ftp.logout(l_conn);
END;
PROCEDURE log (
p_text VARCHAR2
) IS
BEGIN
fnd_file.put_line(
fnd_file.log,
p_text
);
END;
/*Function to validate if duplicate Additional NIDs exist for any of the employees
belonging to the payroll*/
FUNCTION validate_dups (
) RETURN NUMBER IS
l_validate NUMBER;
CURSOR c_nid_dups (
) IS
SELECT
ppei.person_id,
COUNT(ppei.person_id),
ppei.pei_attribute_category
FROM
WHERE
1 = 1
AND ppei.information_type = 'SG_NATIONAL_IDENTIFIER'
AND ppei.pei_information2 IS NULL
AND ppei.person_id IN (
SELECT DISTINCT
papf.person_id
FROM
per_all_people_f papf, per_all_assignments_f paaf,
pay_all_payrolls_f ppf
WHERE
1 = 1
AND sysdate BETWEEN papf.effective_start_date AND
AND sysdate BETWEEN paaf.effective_start_date AND
)
GROUP BY
ppei.person_id,
HAVING
COUNT(ppei.person_id) > 1;
TYPE tt_c_nid_dups IS
TABLE OF c_nid_dups%rowtype INDEX BY PLS_INTEGER;
l_tt_c_nid_dups tt_c_nid_dups;
BEGIN
l_validate := 0;
OPEN c_nid_dups(payroll_name);
--LOOP
FETCH c_nid_dups
BULK COLLECT INTO l_tt_c_nid_dups;
--print nid details and employee number

IF ( ( l_tt_c_nid_dups.count ) > 0 ) THEN
l_validate := 1;
FOR i IN 1..( l_tt_c_nid_dups.count ) LOOP
IF ( print_nid_dup_det(l_tt_c_nid_dups(i).person_id) <> 1 ) THEN
l_validate := -1;
END IF;
END LOOP;
ELSE
l_validate := 0;
END IF;
--EXIT WHEN c_nid_dups%NOTFOUND;

--END LOOP;
CLOSE c_nid_dups;
RETURN l_validate;
EXCEPTION
WHEN OTHERS THEN
log(' Error occurred while validation for employees with suplicate
additional NID records, please contact HPE representative');
log(' Error Code: ' || sqlcode);
log(' Error Msg: ' || sqlerrm);
l_validate := -1;
RETURN l_validate;
END;
/*Function to fetch employee details having diplicate Additional NIDs and print*/
FUNCTION print_nid_dup_det (
per_id NUMBER
) RETURN NUMBER IS
l_success NUMBER;
l_emp_num per_all_people_f.employee_number%TYPE;
l_attr_categ per_people_extra_info.pei_attribute_category%TYPE;
BEGIN
--log(' printing per Id: '||per_id);
SELECT DISTINCT
INTO
l_emp_num,
l_attr_categ
FROM
WHERE
1 = 1
AND papf.person_id = ppei.person_id
AND ppei.information_type = 'SG_NATIONAL_IDENTIFIER'
AND papf.person_id = per_id;
log(' Employee with employee number '

|| l_emp_num
|| ' has more than one Addtional national identifier for '
|| l_attr_categ);
l_success := 1;
RETURN l_success;
EXCEPTION
WHEN too_many_rows THEN
|| l_emp_num
|| ' has more than one national identifier for different
countries');
l_success := 1;
RETURN l_success;
l_success := 1;
RETURN l_success;
WHEN OTHERS THEN
log(' Error occurred while printing details of employees having more
than one additional national identifier, please contact HPE representative');
l_success := 0;
RETURN l_success;
END;
FUNCTION validate_bank_dups (
-- q_from_date DATE,
-- l_to_date DATE,
) RETURN NUMBER IS
l_validate NUMBER;
CURSOR c_bank_dups (
) IS
SELECT
ppei.person_id,
COUNT(ppei.person_id)
FROM
WHERE
1 = 1
AND ppei.information_type = 'SO_BANK_DETAILS'
AND ppei.pei_information5 = 'Salary'
AND nvl(
to_date(
ppei.pei_information10, 'YYYY-MM-DD HH24:MI:SS'
), sysdate
) >= trunc(sysdate)
AND ppei.person_id IN (
SELECT DISTINCT
papf.person_id
FROM
per_all_people_f papf, per_all_assignments_f paaf,
WHERE
1 = 1
AND sysdate BETWEEN paaf.effective_start_date AND
-- AND ( paaf.effective_start_date
-- BETWEEN q_from_date
-- AND l_to_date
-- OR paaf.last_update_date
-- BETWEEN q_from_date
-- AND l_to_date
-- )
/*Added by Rajitha*/
)
GROUP BY
ppei.person_id
HAVING
COUNT(ppei.person_id) > 1;
TYPE tt_c_bank_dups IS
TABLE OF c_bank_dups%rowtype INDEX BY PLS_INTEGER;
l_tt_c_bank_dups tt_c_bank_dups;
BEGIN
l_validate := 0;
OPEN c_bank_dups(payroll_name);
--LOOP
FETCH c_bank_dups
BULK COLLECT INTO l_tt_c_bank_dups;
--print nid details and employee number

IF ( ( l_tt_c_bank_dups.count ) > 0 ) THEN
l_validate := 1;
FOR i IN 1..( l_tt_c_bank_dups.count ) LOOP
IF ( print_bank_dup_det(l_tt_c_bank_dups(i).person_id) <> 1 ) THEN
l_validate := -1;
END IF;
END LOOP;
ELSE
l_validate := 0;
END IF;
--EXIT WHEN c_nid_dups%NOTFOUND;

--END LOOP;
CLOSE c_bank_dups;
RETURN l_validate;
EXCEPTION
WHEN OTHERS THEN
log(' Error occurred while validation for employees with
two/duplicate salary bank accounts, please contact HPE representative');
l_validate := -1;
RETURN l_validate;
END;
FUNCTION print_bank_dup_det (
per_id NUMBER
) RETURN NUMBER IS
l_success NUMBER;
BEGIN
SELECT DISTINCT
papf.employee_number
INTO l_emp_num
FROM
WHERE
1 = 1
AND papf.person_id = ppei.person_id
AND ppei.information_type = 'SO_BANK_DETAILS'
--AND papf.current_employee_flag='Y' --Commented by Rajitha
AND papf.person_id = per_id;

|| l_emp_num
|| ' has more than one Salary bank account');
l_success := 1;
RETURN l_success;
EXCEPTION
WHEN OTHERS THEN
log(' Error occurred while printing details of employees having more
than one Salary Bank accounts, please contact HPE representative');
l_success := 0;
RETURN l_success;
END;
FUNCTION check_residential_address (
) RETURN NUMBER IS
l_success NUMBER;
l_to_date DATE;
q_from_date DATE;
q_to_date DATE;
CURSOR cur_empnonresaddress IS
SELECT DISTINCT
papf.employee_number
INTO l_emp_num
FROM
per_addresses pa,
WHERE
1 = 1
AND papf.person_id = pa.person_id
AND trunc(sysdate) BETWEEN papf.effective_start_date AND
AND pa.primary_flag = 'Y'
AND pa.address_type NOT LIKE '%RA%'
AND trunc(sysdate) BETWEEN papf.effective_start_date AND
AND trunc(sysdate) BETWEEN paaf.effective_start_date AND
AND trunc(
nvl(
to_date(
l_effective_date, 'DD-MON-YYYY'
), sysdate
)
pa.date_to, '31-DEC-4712'
);
BEGIN
FOR i IN cur_empnonresaddress LOOP
|| ' '
|| i.employee_number
|| ' '
|| ' has no Active primary Residential Address Type ');
l_success := 1;
END LOOP;
RETURN l_success;
EXCEPTION
WHEN too_many_rows THEN
|| l_emp_num
|| ' has more than one residential address type ');
l_success := 1;
RETURN l_success;
l_success := 1;
RETURN l_success;
WHEN OTHERS THEN
log(' Error occurred while printing details of employees residential
address Type, please contact HPE representative');
l_success := 0;
RETURN l_success;
END;
END xxsdl_no_sgwi_payroll_int_pkg;

XXSDL No Sgwi Payroll Int PKG

Uploaded by

Copyright:

Available Formats

XXSDL No Sgwi Payroll Int PKG

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

XXSDL No Sgwi Payroll Int PKG

Uploaded by

Copyright:

Available Formats

CREATE OR REPLACE PACKAGE xxsdl_no_sgwi_payroll_int_pkg AS

CREATE OR REPLACE PACKAGE BODY xxsdl_no_sgwi_payroll_int_pkg AS

-- | 1.00 12-Sep-2019 Nandini Chakrabarti Iniial version for Norway

/*Below Logic as per RFCHCM2016033*/

--null Email_Address,--commented by Rajitha

NULL payroll_end_date, -- Made null for

/*Below logic changes as per RFCHCM2016033*/

---- added by Subhashree to include leavers salary for the

-- added by Subhashree to include leavers salary for the current

/****OF/ON effective date added by Nandini*****/

----offshore logic ends Onshore logic starts------

/**** Norway Assignment effective date*** Added by Nandini for

/**** Norway Regulativ*** Added by Nandini for Norwagian

/**** Norway Intrinn*** Added by Nandini for Norwagian

--** Added New column Collective Bargaining date for

-- Added By Subhashree 21st Mar-2018*/

/*Below Logic as per RFCHCM2016033*/

--null Email_Address, commented by Rajitha

NULL payroll_end_date, -- Made null for

/*Below logic changes as per RFCHCM2016033*/

---- added by Subhashree to include leavers salary for the

-- added by Subhashree to include leavers salary for the current

/*hsc.segment1 Legal_Entity_Code,--Replaced hsc.segment1

/**** Norway person type OF/ON*** Added by Nandini for

----offshore logic ends Onshore logic starts------

/**** Norway Legal Entity start date*** Added by Nandini for

/**** Norway Assignment effective date*** Added by Nandini for

/**** Norway Regulativ*** Added by Nandini for Norwagian

/**** Norway Intrinn*** Added by Nandini for Norwagian

--**Below columns are not included in the extract**--

--null Email_Address, commented by Rajitha

/*Below logic changes as per RFCHCM2016033*/

---- added by Subhashree to include leavers salary for the

-- added by Subhashree to include leavers salary for the current

/**** Norway person type OF/ON*** Added by Nandini for Norwagian

/****OF/ON effective date added by Nandini*****/

----offshore logic ends Onshore logic starts------

/**** Norway Legal Entity start date*** Added by Nandini for

/**** Norway Assignment effective date*** Added by Nandini for

/**** Norway Regulativ*** Added by Nandini for Norwagian

/**** Norway Intrinn*** Added by Nandini for Norwagian

--**Below columns are not included in the extract**--

--null Email_Address, Commented by Rajitha

NULL payroll_end_date, -- Made null for

/*Below logic changes as per RFCHCM2016033*/

---- added by Subhashree to include leavers salary for the

-- added by Subhashree to include leavers salary for the current

/****OF/ON effective date added by Nandini*****/

----offshore logic ends Onshore logic starts------

/**** Norway Legal Entity start date*** Added by Nandini for

/**** Norway Assignment effective date*** Added by Nandini for

/**** Norway Regulativ*** Added by Nandini for Norwagian

/**** Norway Intrinn*** Added by Nandini for Norwagian

--**Below columns are not included in the extract**--

--SELECT substr(value,0,instr(value,',')-1) into l_file_path FROM

--validation for NID dups and bank dups before fetch---

--FND_FILE.PUT_LINE(FND_FILE.LOG, 'Entering: THIS IS A TEST');

--FND_FILE.PUT_LINE(FND_FILE.LOG, 'Entering Check Residential Address '||

l_nid_val := validate_dups (p_payroll_name);

/Below Logic as per RFCHCM2016033/

/Below logic changes as per RFCHCM2016033/

/OF/ON effective date added by Nandini*/

/** Norway Assignment effective date* Added by Nandini for

/** Norway Regulativ* Added by Nandini for Norwagian

/** Norway Intrinn* Added by Nandini for Norwagian

/Below Logic as per RFCHCM2016033/

/Below logic changes as per RFCHCM2016033/

/** Norway person type OF/ON* Added by Nandini for

/** Norway Legal Entity start date* Added by Nandini for

/** Norway Assignment effective date* Added by Nandini for

/** Norway Regulativ* Added by Nandini for Norwagian

/** Norway Intrinn* Added by Nandini for Norwagian

--Below columns are not included in the extract--

/Below logic changes as per RFCHCM2016033/

/** Norway person type OF/ON* Added by Nandini for Norwagian

/OF/ON effective date added by Nandini*/

/** Norway Legal Entity start date* Added by Nandini for

/** Norway Assignment effective date* Added by Nandini for

/** Norway Regulativ* Added by Nandini for Norwagian

/** Norway Intrinn* Added by Nandini for Norwagian

--Below columns are not included in the extract--

/Below logic changes as per RFCHCM2016033/

/OF/ON effective date added by Nandini*/

/** Norway Legal Entity start date* Added by Nandini for

/** Norway Assignment effective date* Added by Nandini for

/** Norway Regulativ* Added by Nandini for Norwagian

/** Norway Intrinn* Added by Nandini for Norwagian

--Below columns are not included in the extract--

/-- If payroll group is given and payroll name is null--/

/*OF/ON logic added by Nandini**/

/OF/ON logic ends/