Step 1: click Instance url
https://<Instance url>/FndManageImportExportFilesService?wsdl
Step 2:
You'll see certificate data in this tag : dsig:X509Certificate
Step 3:
Your certificate should be in this format :
-----BEGIN CERTIFICATE-----
Copy data from Step 2
-----END CERTIFICATE-----
open a notepad and copy those header and footer and insert data in between
Step 4 :
Save the file as <Filename.cer>
SELECT
ldg.name ldg_name,
pect1.classification_name primary_classification,
(SELECT pect2.classification_name
FROM pay_ele_classifications_tl pect2
WHERE pect2.classification_id = pet.secondary_classification_id
AND pect2.LANGUAGE = USERENV('LANG')
) secondary_classification,
pldf.attribute1 region,
ftl.territory_short_name country_name,
ppslv.person_number person_number,
ppnf.full_name employee_name,
pldft.location_name location,
houft.name department,
(SELECT ffvv.description
FROM gl_code_combinations gcc,
fnd_flex_values_vl ffvv,
fnd_id_flex_segments fifs
WHERE gcc.code_combination_id = paam.default_code_comb_id
AND gcc.segment5 = ffvv.flex_value
AND ffvv.flex_value_set_id = fifs.flex_value_set_id
AND fifs.SEGMENT_NAME = 'Team'
AND fifs.id_flex_code = 'GL#'
) team,
pet.reporting_name reporting_name,
to_char(pee.creation_date, 'MM-DD-YYYY') entry_date,
to_char(pee.effective_start_date, 'MM-DD-YYYY') effective_start_date,
to_char(pee.effective_end_date, 'MM-DD-YYYY') effective_end_date,
pet.element_name element_name,
pet.base_element_name element_code,
pet.description element_description,
DECODE(pet.processing_type, 'R', 'Yes', 'N', 'No', 'No') recurring_entry,
ldg.default_currency_code currency,
peevf.screen_entry_value amount,
( CASE
WHEN ldg.default_currency_code = 'USD'
THEN to_number(peevf.screen_entry_value)
ELSE
ROUND((to_number(peevf.screen_entry_value)) * gdr.conversion_rate,2)
END
) amount_usd,
pgft.name job_grade,
pjf.job_code job_code,
pelf.attribute1 bonus_eligible,
pelf.attribute2 merit_eligible,
pelf.attribute3 pension_eligible
FROM pay_legislative_data_groups ldg,
pay_element_types_vl pet,
(select gdr1.conversion_rate, gdr1.from_currency
FROM gl_daily_rates gdr1
WHERE gdr1.to_currency = 'USD'
AND gdr1.conversion_type = 'Corporate'
AND gdr1.conversion_date = (SELECT MAX(gdr2.conversion_date)
FROM gl_daily_rates gdr2
WHERE gdr2.to_currency = 'USD'
AND gdr2.conversion_type = 'Corporate'
AND gdr2.from_currency=gdr1.from_currency
AND gdr2.conversion_date <= sysdate)
) gdr,
pay_element_links_f pelf,
pay_ele_classifications_tl pect1,
pay_element_entries_f pee,
pay_element_entry_values_f peevf,
pay_input_values_f piv,
per_person_names_f ppnf,
per_person_secured_list_v ppslv,
per_all_assignments_m paam,
hr_organization_units_f_tl houft,
per_location_details_f pldf,
per_location_details_f_tl pldft,
per_grades_f_tl pgft,
per_jobs_f pjf,
per_locations pl,
fnd_territories_tl ftl
WHERE (( coalesce(NULL, :p_ldg_name) IS NULL )
OR ( ldg.name IN (:p_ldg_name) ) )
AND ( ( coalesce(NULL, :p_reporting_name) IS NULL )
OR ( pet.reporting_name IN ( :p_reporting_name ) ) )
AND ( ( coalesce(NULL, :p_element_name) IS NULL )
OR ( pet.element_type_id IN ( :p_element_name ) ) )
AND ( ( coalesce(NULL, :p_processing_type) IS NULL )
OR ( pet.processing_type IN ( :p_processing_type ) ) )
AND ldg.legislative_data_group_id = pet.legislative_data_group_id
AND ldg.default_currency_code = gdr.from_currency(+)
AND pet.element_type_id = pee.element_type_id
AND pet.element_type_id = pelf.element_type_id
AND pet.classification_id = pect1.classification_id
AND UPPER(pet.element_name) NOT LIKE '%SALARY%'
AND pect1.LANGUAGE = USERENV('LANG')
AND TRUNC(SYSDATE) BETWEEN pet.effective_start_date AND pet.effective_end_date
AND TRUNC(SYSDATE) BETWEEN pelf.effective_start_date AND pelf.effective_end_date
AND pee.person_id = ppnf.person_id
AND TRUNC(SYSDATE) BETWEEN ppnf.effective_start_date AND ppnf.effective_end_date
AND pee.element_entry_id = peevf.element_entry_id
AND pee.last_update_date = peevf.last_update_date
AND pee.effective_start_date = peevf.effective_start_date
AND peevf.input_value_id = piv.input_value_id
AND piv.element_type_id = pet.element_type_id
AND piv.base_name = 'Amount'
AND TRUNC(SYSDATE) BETWEEN piv.effective_start_date AND piv.effective_end_date
AND ppnf.person_id = ppslv.person_id
AND TRUNC(SYSDATE) BETWEEN ppslv.effective_start_date AND ppslv.effective_end_date
AND ppnf.person_id = paam.person_id
AND ppnf.name_type = 'GLOBAL'
AND paam.organization_id = houft.organization_id
AND houft.LANGUAGE = USERENV('LANG')
AND paam.effective_latest_change = 'Y'
AND paam.assignment_type = 'E'
AND paam.assignment_status_type = 'ACTIVE'
AND paam.primary_assignment_flag = 'Y'
AND TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND paam.effective_end_date
AND paam.location_id = pldf.location_id
AND pldf.location_details_id = pldft.location_details_id
AND pldft.LANGUAGE = USERENV('LANG')
AND paam.grade_id = pgft.grade_id
AND pgft.LANGUAGE = USERENV('LANG')
AND TRUNC(SYSDATE) BETWEEN pgft.effective_start_date AND pgft.effective_end_date
AND paam.job_id = pjf.job_id
AND TRUNC(SYSDATE) BETWEEN pjf.effective_start_date AND pjf.effective_end_date
AND paam.location_id = pl.location_id
AND pl.country = ftl.TERRITORY_CODE
AND ftl.LANGUAGE = USERENV('LANG')
AND (( coalesce(NULL, :p_full_name) IS NULL )
OR ( ppnf.full_name IN (:p_full_name) ) )
AND pee.effective_start_date <= :p_end_date
AND pee.effective_end_date >= :p_start_date
--AND :p_start_date <= :P_end_date
ORDER BY ldg.name, ppnf.full_name, pet.element_name,pee.effective_start_date DESC
WITH pay_retro AS
(
SELECT rule_value, pet1.element_name
FROM pay_tmplt_rule_values ptrv,
pay_tmplt_rules_f ptrf ,
pay_element_types_vl pet1,
pay_templates pt
WHERE ptrv.template_id = pt.template_id
AND pt.base_name = pet1.element_name
AND ptrf.rule_id = ptrv.rule_id
AND ptrf.rule_name = 'Retro'
),
pay_proration AS
(
SELECT rule_value, pet2.element_name
FROM pay_tmplt_rule_values ptrv,
pay_tmplt_rules_f ptrf ,
pay_element_types_vl pet2,
pay_templates pt
WHERE ptrv.template_id = pt.template_id
AND pt.base_name = pet2.element_name
AND ptrf.rule_id = ptrv.rule_id
AND ptrf.rule_name = 'Proration'
)
SELECT ldg.name legislative_data_group
,pec.classification_name primary_classification
,pec1.classification_name secondary_classification
,NVL ((SELECT territory_short_name
FROM fnd_territories_vl
WHERE (territory_code = SUBSTR(pet.element_name,4,2))
),
(SELECT territory_short_name
FROM fnd_territories_vl
WHERE (territory_code = ldg.legislation_code))) country_name
,pet.element_name name
,pet.reporting_name
,pet.description
,TO_CHAR(pef.effective_start_date,'MM/DD/YYYY') effective_date
,TO_CHAR(pef.effective_end_date,'MM/DD/YYYY') effective_end_date
,pef.input_currency_code input_currency
,ptd1.definition_name earliest_entry_date
,ptd2.definition_name latest_entry_date
,DECODE(pet.use_at_asg_level, 'Y', 'Assignment level',
DECODE(pet.use_at_term_level, 'Y', 'Term level', 'Pay relationship level')
) employment_level
,DECODE(hrl_petproc.meaning, 'Recurring', 'Yes', 'Nonrecurring', 'No') recurring
,pef.multiple_entries_allowed_flag multiple_entries_allowed
,pp.rule_value proration
,pr.rule_value retroactive
,pelf.element_link_name element_eligibility_name
,pelf.standard_link_flag automatic_entry
, CASE WHEN :p_effective_start_date >= pef.effective_start_date AND :p_effective_start_date < pef.effective_end_date
THEN 'ACTIVE'
ELSE 'INACTIVE'
END AS status
,pelf.attribute2 merit_eligible
,pelf.attribute1 bonus_eligible
,pelf.attribute3 pension_eligible
FROM pay_element_types_vl pet
,pay_ele_classifications_vl pec
,pay_ele_classifications_vl pec1
,pay_element_links_f pelf
,pay_element_types_f pef
,per_legislative_data_groups_vl ldg
,hr_lookups hrl_petproc
,pay_time_definitions ptd1
,pay_time_definitions ptd2
,pay_retro pr
,pay_proration pp
WHERE 1=1
AND pet.classification_id = pec.classification_id
AND pet.secondary_classification_id = pec1.classification_id(+)
AND pet.element_type_id = pelf.element_type_id(+)
AND pet.element_type_id = pef.element_type_id
AND ldg.legislative_data_group_id = pet.legislative_data_group_id
AND UPPER(pet.reporting_name) not like '%SALARY%'
AND pet.processing_type = hrl_petproc.lookup_code
AND hrl_petproc.lookup_type LIKE 'PAY_PROCESSING_TYPE'
AND TRUNC(SYSDATE) BETWEEN hrl_petproc.start_date_active AND hrl_petproc.end_date_active
AND pet.starting_time_def_id = ptd1.time_definition_id
AND pet.ending_time_def_id = ptd2.time_definition_id
AND pet.element_name = pp.element_name(+)
AND pet.element_name = pr.element_name(+)
AND ((COALESCE(NULL, :param_ldg_name) IS NULL)
OR (ldg.NAME IN (:param_ldg_name)))
AND ((COALESCE(NULL, :param_element_name) IS NULL)
OR (pet.element_type_id IN (:param_element_name)))
AND ((COALESCE(NULL, :param_rep_name) IS NULL)
OR (pet.reporting_name IN (:param_rep_name)))
AND (CASE WHEN :param_status = 'All'
THEN 'All'
WHEN :p_effective_start_date >= pef.effective_start_date AND :p_effective_start_date < pef.effective_end_date
THEN 'ACTIVE'
ELSE 'INACTIVE'
END) = :param_status
ORDER BY ldg.legislation_code, pet.element_name
