Apps2CloudFusion: HCM Report Queries From Rajesh/Swetha

----------------------------Absence Report--------------------------

Parameters name

-------------------------------------

1.p_absence_status

2.p_legal_entity

3.p_department

4.p_leave_type

5.p_effective_start_date(mandatory)

6.p_effective_end_date(mandatory)

list of values

----------------------------------------

------------L_Leave_type lov

SELECT distinct name, absence_type_id

from ANC_ABSENCE_TYPES_VL

-----------L_Department lov

select distinct name,ORGANIZATION_ID from PER_DEPARTMENTS

-------------L_legal_entity lov

select distinct

haou1.name

,haou1.ORGANIZATION_ID

FROM HR_ORGANIZATION_UNITS_F_TL haou1

,PER_ALL_ASSIGNMENTS_F paam

WHERE 1=1

and trunc(sysdate) between trunc(paam.EFFECTIVE_START_DATE) and trunc(paam.EFFECTIVE_END_DATE)

and paam.ASSIGNMENT_TYPE in ('E','C')

and paam.PRIMARY_FLAG='Y'

and paam.SYSTEM_PERSON_TYPE='EMP'

and paam.EFFECTIVE_LATEST_CHANGE='Y'

and paam.LEGAL_ENTITY_ID=haou1.ORGANIZATION_ID

and trunc(sysdate) between trunc(haou1.EFFECTIVE_START_DATE) and trunc(haou1.EFFECTIVE_END_DATE)

and haou1.LANGUAGE='US'

--and rownum<=1

--------------------------------------------------------------------

------------------main query

With absence_track as (select

ppos.worker_number,

papf.person_number,

papf.person_id p_id,

ppnf.display_name Person_Name,

pd.name Department,

haou1.name LE,

apae.start_date start_date,

apae.end_date end_date,

-- apae.absence_status_cd absence_status,

DECODE(apae.absence_status_cd,'ORA_WITHDRAWN','Withdrawn','SUBMITTED','Submitted',null) absence_status_cd,

apae.approval_status_cd approval_status,

abs.name absence_name,

DECODE(abs.name,'Marriage Leave','G','Short Leave','-','Sick Leave','S','Leave Without Pay','U','Annual Leave','A','Paternity Leave','P','Hajj leave','J','Exam Leave','ST','Maternity Leave','M',null) absence_type,

apae.duration,

ROWNUM

from

per_all_people_f papf,

per_all_assignments_f paam,

per_person_names_f ppnf,

HR_ORGANIZATION_UNITS_F_TL haou1,

per_periods_of_service ppos,

per_departments pd,

ANC_PER_ABS_ENTRIES apae,

ANC_ABSENCE_TYPES_VL abs

where 1=1

and trunc(sysdate) between trunc(papf.effective_start_date) and trunc(papf.effective_end_date)

and papf.person_id=paam.person_id

and trunc(sysdate) between trunc(paam.effective_start_date) and trunc(paam.effective_end_date)

and paam.primary_flag='Y'

and paam.effective_latest_change='Y'

and paam.ASSIGNMENT_STATUS_TYPE = 'ACTIVE'

and paam.person_id=ppnf.person_id

and trunc(sysdate) between trunc(ppnf.effective_start_date) and trunc(ppnf.effective_end_date)

and ppnf.name_type='GLOBAL'

and paam.LEGAL_ENTITY_ID=haou1.ORGANIZATION_ID

and trunc(sysdate) between trunc(haou1.EFFECTIVE_START_DATE) and trunc(haou1.EFFECTIVE_END_DATE)

and haou1.LANGUAGE='US'

and paam.period_of_service_id = ppos.period_of_service_id

and papf.person_id=ppos.person_id

and paam.organization_id = pd.organization_id(+)

and trunc(sysdate) between trunc(pd.effective_start_date(+)) and trunc(pd.effective_end_date(+))

and papf.person_id = apae.person_id

and apae.absence_type_id = abs.absence_type_id

and ABS.LANGUAGE='US'

and trunc(sysdate) between trunc(abs.EFFECTIVE_START_DATE) and trunc(abs.EFFECTIVE_END_DATE)

-- and apae.absence_status_cd != 'ORA_WITHDRAWN'

and apae.approval_status_cd = 'APPROVED'

and haou1.ORGANIZATION_ID = NVL(:p_legal_entity,haou1.ORGANIZATION_ID)

and ((paam.ORGANIZATION_ID) IN (:p_department) OR 'All' IN (:p_department|| 'All'))

and abs.absence_type_id = nvl(:P_LEAVE_TYPE,abs.absence_type_id)

and apae.absence_status_cd = NVL(:p_absence_status,apae.absence_status_cd)

)

select

fcd.REPORT_DATE ABS_DAYS_H,

fcd.REPORT_DATE,

at.*

FROM fnd_cal_day fcd,

absence_track at

where 1=1

and trunc(fcd.REPORT_DATE) between trunc(start_date(+)) and trunc(end_date(+))

and trunc(fcd.report_date) between trunc(:p_effective_start_date) and trunc(:p_effective_end_date)

order by fcd.REPORT_DATE

-------Absence report parameters query dataset--2

SELECT

NVL((SELECT NAME FROM hr_organization_units_f_tl

WHERE 1=1 and language='US'

and rownum <=1

and organization_id = :P_LEGAL_ENTITY),'ALL') PE_LEGAL_ENTITY_ID,

NVL((SELECT distinct NAME FROM PER_DEPARTMENTS WHERE organization_id = :P_DEPARTMENT),'ALL') PE_DEPARTMENT,

NVL((SELECT distinct name FROM ANC_ABSENCE_TYPES_VL WHERE absence_type_id = :P_LEAVE_TYPE),'ALL') PE_LEAVE_TYPE,

TO_CHAR(:P_EFFECTIVE_START_DATE, 'DD-MM-YYYY', 'NLS_DATE_LANGUAGE = AMERICAN') PE_EFFECTIVE_FROM_DATE,

TO_CHAR(:P_EFFECTIVE_END_DATE, 'DD-MM-YYYY', 'NLS_DATE_LANGUAGE = AMERICAN') PE_EFFECTIVE_TO_DATE

FROM

DUAL

---------------------------------------------------------------------------

-----------------------------Bank Export query

--------------parameters

1.p_pay_date(mandatory)

2.p_legal_entity

-------------------------------

--------------list of values for legal entity

select distinct

haou1.name

FROM HR_ORGANIZATION_UNITS_F_TL haou1

,PER_ALL_ASSIGNMENTS_F paam

WHERE 1=1

and trunc(sysdate) between trunc(paam.EFFECTIVE_START_DATE) and trunc(paam.EFFECTIVE_END_DATE)

and paam.ASSIGNMENT_TYPE in ('E','C')

and paam.PRIMARY_FLAG='Y'

and paam.SYSTEM_PERSON_TYPE='EMP'

and paam.EFFECTIVE_LATEST_CHANGE='Y'

and paam.LEGAL_ENTITY_ID=haou1.ORGANIZATION_ID

and trunc(sysdate) between trunc(haou1.EFFECTIVE_START_DATE) and trunc(haou1.EFFECTIVE_END_DATE)

and haou1.LANGUAGE='US'

--and rownum<=1

-----------------------------main query for bank export

with dedec as(SELECT distinct PRRV.result_value,PRR.SOURCE_RESULT_ID,PAAPF.person_id,haou1.name Legal_Entity,ptp.end_date

FROM fusion.pay_element_types_vl ELE,

fusion.pay_run_results PRR,

fusion.pay_run_result_values PRRV,

fusion.pay_input_values_vl PIV,

fusion.pay_payroll_rel_actions PPRA,

fusion.pay_pay_relationships_dn PREL,

fusion.pay_payroll_actions PPA,

fusion.pay_all_payrolls_f PAP,

fusion.per_all_assignments_f PAA,

HR_ORGANIZATION_UNITS_F_TL haou1,

fusion.pay_ele_classifications_vl PEC,

fusion.per_person_names_f PPN,

fusion.per_all_people_f PAAPF,

per_person_types_vl PER_TYPES,

per_periods_of_service ppos,

pay_payroll_assignments ppas,

pay_balance_feeds_f pbff,

pay_balance_types_tl pbtl,

pay_time_periods ptp

--per_all_people_f papf

WHERE PPA.payroll_action_id = PPRA.payroll_action_id

AND PPA.payroll_id = PAP.payroll_id

AND PAA.person_type_id = PER_TYPES.person_type_id

AND PPA.action_type IN ( 'R', 'Q' )

AND PRR.payroll_rel_action_id = PPRA.payroll_rel_action_id

AND PPRA.run_type_id IS NOT NULL

AND ppra.source_action_id IS NOT NULL --Newly added

AND PRR.element_type_id = ELE.element_type_id

AND PRRV.run_result_id = PRR.run_result_id

AND PIV.input_value_id = PRRV.input_value_id

AND PREL.payroll_relationship_id = PPRA.payroll_relationship_id

AND PREL.person_id = PAA.person_id

AND ppos.person_id = PAAPF.person_id

AND ppos.period_of_service_id = paa.period_of_service_id

and paa.LEGAL_ENTITY_ID=haou1.ORGANIZATION_ID

and trunc(sysdate) between trunc(haou1.EFFECTIVE_START_DATE) and trunc(haou1.EFFECTIVE_END_DATE)

and haou1.LANGUAGE='US'

AND paa.effective_latest_change = 'Y'

AND paa.primary_flag = 'Y'

AND PREL.person_id = PPN.person_id

AND PPN.name_type = 'GLOBAL'

AND PREL.person_id = PAAPF.person_id

AND PREL.payroll_relationship_id = ppas.payroll_relationship_id

AND ppas.hr_assignment_id = paa.assignment_id

AND Trunc(SYSDATE) BETWEEN PPN.effective_start_date AND

PPN.effective_end_date

AND Trunc(SYSDATE) BETWEEN PAA.effective_start_date AND

PAA.effective_end_date

AND Trunc(SYSDATE) BETWEEN PAAPF.effective_start_date AND

PAAPF.effective_end_date

AND Trunc(SYSDATE) BETWEEN ELE.effective_start_date AND

ELE.effective_end_date

AND Trunc(SYSDATE) BETWEEN piv.effective_start_date AND

piv.effective_end_date

AND Trunc(SYSDATE) BETWEEN PAP.effective_start_date AND

PAP.effective_end_date

--AND flv.lookup_type(+) ='EMP_CAT'

--AND paa.employment_category = flv.lookup_code(+)

--AND PPA.EFFECTIVE_DATE between TRUNC(:Effective_Date_Parameter) and trunc(:Effective_end_Date_Parameter)

AND PAA.primary_assignment_flag = 'Y'

AND paa.assignment_type IN ( 'E', 'N' )

AND PAA.assignment_status_type IN( 'ACTIVE' )

AND ELE.classification_id = PEC.classification_id

AND Trunc(ptp.end_date) BETWEEN PEC.date_from AND

Nvl(PEC.date_to, PPA.effective_date)

AND retro_component_id IS NULL

--AND papf.person_id =:PER_ID

AND PIV.element_type_id = pbff.element_type_id

AND PIV.input_value_id = pbff.input_value_id

AND pbff.balance_type_id = pbtl.balance_type_id

AND pbtl.LANGUAGE = 'US'

AND PTP.period_category = 'E'

AND pap.payroll_id = ptp.payroll_id

AND ppa.earn_time_period_id = ptp.time_period_id

AND pbtl.balance_name = 'Net Pay'

AND PEC.classification_name IN( 'Social Insurance Deductions',

'Other' )

AND PIV.base_name = ( 'Pay Value' )

--AND PAAPF.person_id = papf.person_id

--AND papf.person_number = '16'

order by PRR.SOURCE_RESULT_ID desc)

select distinct round(nvl((ban.Allowance + ban.basic - ban.Gosi_Ded_Other_deducion),ban.Allowance + ban.basic))Net_pay,ban.*

from

(SELECT /*(SELECT SUM(To_number(peevf1.screen_entry_value))

FROM pay_element_entry_values_f peevf1,

pay_input_values_tl pivt1,

pay_input_values_f pivf1,

pay_element_types_tl petf1,

pay_element_entries_f peef1

WHERE peevf1.screen_entry_value IS NOT NULL

AND SYSDATE BETWEEN peevf1.effective_start_date AND

peevf1.effective_end_date

AND peevf1.input_value_id = pivf1.input_value_id

AND peevf1.element_entry_id = peef1.element_entry_id

AND pivt1.name = 'Amount'

AND pivt1.input_value_id = pivf1.input_value_id

AND SYSDATE BETWEEN pivf1.effective_start_date AND

pivf1.effective_end_date

AND pivf1.element_type_id = petf1.element_type_id

AND petf1.LANGUAGE = 'US'

AND pivt1.LANGUAGE = 'US'

AND petf1.element_name IN ( 'Basic Salary' )

AND petf1.element_type_id = peef1.element_type_id

AND SYSDATE BETWEEN peef1.effective_start_date AND

peef1.effective_end_date

AND peef1.person_id = papf.person_id)

Total_salary,*/

ppnf.full_name,

(SELECT pad.iban_number

FROM pay_person_pay_methods_f ppm,

pay_bank_accnt_details_vl pad,

pay_pay_relationships_dn ppr

WHERE ppm.bank_account_id IS NOT NULL

AND ppm.bank_account_id = pad.bank_account_id

AND ppr.payroll_relationship_id = ppm.payroll_relationship_id

AND SYSDATE BETWEEN ppm.effective_start_date AND

ppm.effective_end_date

AND ppr.person_id = papf.person_id

AND ppm.payroll_relationship_id =

(SELECT Max(ppr1.payroll_relationship_id)

FROM pay_pay_relationships_dn ppr1

WHERE ppr1.person_id = ppr.person_id)

AND ppm.personal_payment_method_id =

(SELECT Max(ppm1.personal_payment_method_id)

FROM

fusion.pay_person_pay_methods_f ppm1

WHERE

ppm1.payroll_relationship_id =

ppm.payroll_relationship_id

AND SYSDATE BETWEEN ppm1.effective_start_date AND

ppm1.effective_end_date)) IBAN_NUMBER

(SELECT pbae.eft_swift_code

FROM pay_bank_accounts PBAE,

per_all_people_f PPAF,

per_all_assignments_m PAAM,

pay_payroll_assignments PA,

pay_payroll_rel_actions PPRA,

pay_pre_payments PPP

WHERE 1 = 1

AND PBAE.bank_account_id = PPP.payee_bank_account_id

AND PPAF.person_id = PAAM.person_id

AND SYSDATE BETWEEN paam.effective_start_date AND

paam.effective_end_date

AND SYSDATE BETWEEN ppaf.effective_start_date AND

ppaf.effective_end_date

AND paam.effective_latest_change = 'Y'

AND paam.assignment_id = pa.hr_assignment_id

AND PA.payroll_relationship_id = ppra.payroll_relationship_id

AND PPP.payroll_rel_action_id = ppra.payroll_rel_action_id

AND PPAF.person_id = papf.person_id) SWIFT_CODE,

decode((SELECT cs.salary_basis_code

FROM cmp_salary cs

WHERE papf.person_id = cs.person_id

AND ROWNUM = 1),'MONTHLY','SALARY') TYPE,

(SELECT cs.salary_amount

FROM cmp_salary cs

WHERE papf.person_id = cs.person_id

AND ROWNUM = 1) Basic,

(SELECT pni.national_identifier_number

FROM per_national_identifiers pni

WHERE pni.person_id = papf.person_id

AND ROWNUM = 1

AND pni.last_update_date = (SELECT Max(pni1.last_update_date)

FROM per_national_identifiers pni1

WHERE pni.national_identifier_id =

pni1.national_identifier_id

AND pni1.person_id =

pni.person_id))

"ID (National / Iqama)",

(SELECT SUM(To_number(peevf1.screen_entry_value))

FROM pay_element_entry_values_f peevf1,

pay_input_values_tl pivt1,

pay_input_values_f pivf1,

pay_element_types_tl petf1,

pay_element_entries_f peef1

WHERE SYSDATE BETWEEN peevf1.effective_start_date AND

peevf1.effective_end_date

AND peevf1.input_value_id = pivf1.input_value_id

AND peevf1.element_entry_id = peef1.element_entry_id

AND pivt1.name = 'Pay Value'

AND pivt1.input_value_id = pivf1.input_value_id

AND SYSDATE BETWEEN pivf1.effective_start_date AND

pivf1.effective_end_date

AND pivf1.element_type_id = petf1.element_type_id

AND petf1.LANGUAGE = 'US'

AND pivt1.LANGUAGE = 'US'

AND petf1.element_name IN ( 'Housing Allowance' )

AND petf1.element_type_id = peef1.element_type_id

AND SYSDATE BETWEEN peef1.effective_start_date AND

peef1.effective_end_date

AND peef1.person_id = papf.person_id)

"Housing Allowance",

(SELECT SUM(To_number(peevf1.screen_entry_value))

FROM pay_element_entry_values_f peevf1,

pay_input_values_tl pivt1,

pay_input_values_f pivf1,

pay_element_types_tl petf1,

pay_element_entries_f peef1

WHERE SYSDATE BETWEEN peevf1.effective_start_date AND

peevf1.effective_end_date

AND peevf1.input_value_id = pivf1.input_value_id

AND peevf1.element_entry_id = peef1.element_entry_id

AND pivt1.name = 'Pay Value'

AND pivt1.input_value_id = pivf1.input_value_id

AND SYSDATE BETWEEN pivf1.effective_start_date AND

pivf1.effective_end_date

AND pivf1.element_type_id = petf1.element_type_id

AND petf1.LANGUAGE = 'US'

AND pivt1.LANGUAGE = 'US'

AND petf1.element_name NOT IN(

'Basic Salary', 'Housing Allowance' )

AND petf1.element_type_id = peef1.element_type_id

AND SYSDATE BETWEEN peef1.effective_start_date AND

peef1.effective_end_date

AND peef1.person_id = papf.person_id)

Transport_Other_Allowance,

(SELECT SUM(To_number(peevf1.screen_entry_value))

FROM pay_element_entry_values_f peevf1,

pay_input_values_tl pivt1,

pay_input_values_f pivf1,

pay_element_types_tl petf1,

pay_element_entries_f peef1

WHERE SYSDATE BETWEEN peevf1.effective_start_date AND

peevf1.effective_end_date

AND peevf1.input_value_id = pivf1.input_value_id

AND peevf1.element_entry_id = peef1.element_entry_id

AND pivt1.name = 'Pay Value'

AND pivt1.input_value_id = pivf1.input_value_id

AND SYSDATE BETWEEN pivf1.effective_start_date AND

pivf1.effective_end_date

AND pivf1.element_type_id = petf1.element_type_id

AND petf1.LANGUAGE = 'US'

AND pivt1.LANGUAGE = 'US'

-- AND petf1.element_name NOT IN(

-- 'Basic Salary', 'Housing Allowance' )

AND petf1.element_type_id = peef1.element_type_id

AND SYSDATE BETWEEN peef1.effective_start_date AND

peef1.effective_end_date

AND peef1.person_id = papf.person_id)

Allowance,

(SELECT SUM(To_number(peevf1.screen_entry_value))

FROM pay_element_entry_values_f peevf1,

pay_input_values_tl pivt1,

pay_input_values_f pivf1,

pay_element_types_tl petf1,

pay_element_entries_f peef1

WHERE SYSDATE BETWEEN peevf1.effective_start_date AND

peevf1.effective_end_date

AND peevf1.input_value_id = pivf1.input_value_id

AND peevf1.element_entry_id = peef1.element_entry_id

--AND pivt1.NAME = 'Pay Value'

AND pivt1.input_value_id = pivf1.input_value_id

AND SYSDATE BETWEEN pivf1.effective_start_date AND

pivf1.effective_end_date

AND pivf1.element_type_id = petf1.element_type_id

AND petf1.LANGUAGE = 'US'

AND pivt1.LANGUAGE = 'US'

AND petf1.element_name IN ( 'Employee GOSI Annuities',

'Employee GOSI Saned' )

AND petf1.element_type_id = peef1.element_type_id

AND SYSDATE BETWEEN peef1.effective_start_date AND

peef1.effective_end_date

AND peef1.person_id = papf.person_id) "Gosi Ded",

(select sum(dedec.result_value) from dedec

where 1=1

AND dedec.person_id = papf.person_id

AND ROWNUM <=2

)

Gosi_Ded_Other_deducion,

/*(SELECT DISTINCT

prb.balance_value

FROM

per_periods_of_service ppos,

per_all_assignments_f paaf,

pay_payroll_assignments ppasg,

pay_pay_relationships_dn pprd,

pay_payroll_rel_actions ppra,

pay_run_results prr,

pay_run_result_values prrv,

pay_input_values_f pivf,

pay_balance_feeds_f pbf,

pay_balance_types_vl pbtv,

pay_payroll_actions ppa,

pay_all_payrolls_f prl,

pay_run_balances prb,

pay_defined_balances pdb,

pay_balance_dimensions pbd

WHERE 1=1

AND ppos.person_id = papf.person_id

AND paaf.person_id = ppos.person_id

AND paaf.period_of_service_id = ppos.period_of_service_id

AND trunc(SYSDATE) BETWEEN paaf.effective_start_date AND paaf.effective_end_date

AND paaf.assignment_id = ppasg.hr_assignment_id

AND paaf.work_terms_assignment_id = ppasg.hr_term_id

AND trunc(SYSDATE) BETWEEN ppasg.start_date AND ppasg.end_date

AND ppasg.payroll_relationship_id = pprd.payroll_relationship_id

AND trunc(SYSDATE) BETWEEN pprd.start_date AND pprd.end_date

AND ppra.payroll_relationship_id = pprd.payroll_relationship_id

AND ppra.payroll_relationship_id = ppasg.payroll_relationship_id

AND ppra.payroll_rel_action_id = prr.payroll_rel_action_id

AND prrv.run_result_id = prr.run_result_id

AND pivf.input_value_id = prrv.input_value_id

AND pbf.input_value_id = pivf.input_value_id

AND pbtv.balance_type_id = pbf.balance_type_id

AND pbf.input_value_id = pivf.input_value_id

AND ppra.payroll_action_id = ppa.payroll_action_id

AND prl.payroll_id = ppa.payroll_id

AND prb.payroll_relationship_id = ppra.payroll_relationship_id

AND prb.payroll_rel_action_id = ppra.payroll_rel_action_id

AND trunc(prb.effective_date) = trunc(ppa.effective_date)

AND pdb.defined_balance_id = prb.defined_balance_id

AND pdb.balance_type_id = pbtv.balance_type_id

AND pdb.balance_dimension_id = pbd.balance_dimension_id

--AND pbd.base_dimension_name = 'Core Relationship Tax Unit Run'

AND BASE_DB_ITEM_SUFFIX = '_ASG_RUN'

AND pbtv.balance_name = 'Net Pay'

AND ROWNUM <= 1

--AND trunc(PPA.effective_date) = to_date('04/30/2021','MM/DD/YYYY')

) Basicnet, */

papf.person_number,

dedec.Legal_Entity

FROM per_all_people_f papf,

per_person_names_f ppnf,

dedec

WHERE 1 = 1

--AND papf.person_number = '32'

AND dedec.person_id = papf.person_id

AND papf.person_id = ppnf.person_id

AND ppnf.name_type = 'GLOBAL'

AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date

AND SYSDATE BETWEEN ppnf.effective_start_date AND ppnf.effective_end_date

--AND dedec.Legal_Entity = NVL(:p_legal_entity,dedec.Legal_Entity)

--AND ((dedec.Legal_Entity) IN (:p_legal_entity) OR 'All' IN (:p_legal_entity|| 'All'))

AND (COALESCE(NULL, :p_legal_entity) IS NULL OR dedec.Legal_Entity IN (:p_legal_entity))

AND To_char(dedec.end_date,'MM-DD-YYYY') <=:P_pay_date

) ban

------------------------------------------------------------------------------

-------------------------Document of record

-----parameters

1.p_employee

2.p_legal_entity

3.p_document_type

4.as_of_date

-------------list of values

----employee name lov

select DISTINCT display_name

from PER_PERSON_NAMES_F

where 1=1

AND SYSDATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE

AND NAME_TYPE = 'GLOBAL'

--------document type lov

Select DISTINCT DOCUMENT_TYPE from HR_DOCUMENT_TYPES_TL where 1=1

--------legal employeer lov

select distinct name from PER_LEGAL_EMPLOYERS

------------------main query

SELECT DISTINCT

'Document Of Record' Header

,A.DOCUMENT_CODE

,B.LEGISLATION_CODE

,b.CATEGORY_CODE

,C.PERSON_NUMBER

--,A.ISSUING_COUNTRY AS Country

,D.DOCUMENT_TYPE

,A.DOCUMENT_NAME

--,A.DOCUMENT_NUMBER

,A.LAST_UPDATE_DATE

,TO_CHAR(A.DATE_FROM,'MM/DD/YYYY') AS DATE_FROM

,TO_CHAR(A.DATE_TO,'MM/DD/YYYY') AS DATE_TO

,A.ISSUING_AUTHORITY

,TO_CHAR(A.ISSUED_DATE,'MM/DD/YYYY') AS ISSUES_DATE

,A.ISSUING_COUNTRY AS ISSUING_COUNTRY_NAME

,A.ISSUING_LOCATION

,A.COMMENTS

--,A.PUBLISH

--,TO_CHAR(A.PUBLISH_DATE,'YYYY-MM-DD') AS PUBLISH_DATE

--,A.RELATED_OBJECT_NAME

--,A.STATUS

--,A.DEI_ATTRIBUTE_CATEGORY AS ATTRIBUTE_CATEGORY

--,hl.MEANING "CATEGORY"

,ppnf.display_name

,hauft.NAME "Legal Entity"

,ppnf.person_id

,D.DOCUMENT_TYPE_ID

,( SELECT hauft.NAME

FROM HR_ORG_UNIT_CLASSIFICATIONS_F houcf,

HR_ALL_ORGANIZATION_UNITS_F haouf,

HR_ORGANIZATION_UNITS_F_TL hauft,

per_all_assignments_m paam

WHERE haouf.ORGANIZATION_ID = houcf.ORGANIZATION_ID

AND haouf.ORGANIZATION_ID = hauft.ORGANIZATION_ID

AND haouf.EFFECTIVE_START_DATE BETWEEN houcf.EFFECTIVE_START_DATE AND houcf.EFFECTIVE_END_DATE

AND hauft.LANGUAGE = 'US'

AND hauft.EFFECTIVE_START_DATE = haouf.EFFECTIVE_START_DATE

AND hauft.EFFECTIVE_END_DATE = haouf.EFFECTIVE_END_DATE

AND houcf.CLASSIFICATION_CODE = 'DEPARTMENT'

AND SYSDATE BETWEEN hauft.effective_start_date AND hauft.effective_end_date

AND hauft.organization_id = paam.organization_id

and paam.person_id = c.person_id

and paam.primary_assignment_flag = 'Y'

and paam.assignment_type = 'E'

and paam.effective_latest_change = 'Y'

and sysdate between paam.effective_start_date and paam.effective_end_date ) Department

,( SELECT hapft.name

FROM

per_all_assignments_m paam,

hr_all_positions_f hapf,

hr_all_positions_f_tl hapft

WHERE c.person_id = paam.person_id

AND paam.primary_assignment_flag = 'Y'

AND paam.assignment_type = 'E'

AND paam.effective_latest_change = 'Y'

AND TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND paam.effective_end_date

AND paam.position_id = hapf.position_id

AND TRUNC(SYSDATE) BETWEEN hapf.effective_start_date AND hapf.effective_end_date

AND hapf.position_id = hapft.position_id

AND hapft.language = 'US'

AND TRUNC(SYSDATE) BETWEEN hapft.effective_start_date AND hapft.effective_end_date ) "Position Name"

FROM

HR_DOCUMENTS_OF_RECORD A,

HR_DOCUMENT_TYPES_B B,

PER_ALL_PEOPLE_F C,

HR_DOCUMENT_TYPES_TL D,

--hr_lookups HL,

PER_PERSON_NAMES_F ppnf,

HR_ORG_UNIT_CLASSIFICATIONS_F houcf,

HR_ALL_ORGANIZATION_UNITS_F haouf,

HR_ORGANIZATION_UNITS_F_TL hauft,

per_all_assignments_m paam1

WHERE A.DOCUMENT_TYPE_ID = B.DOCUMENT_TYPE_ID

AND D.DOCUMENT_TYPE_ID = B.DOCUMENT_TYPE_ID

--AND D.DOCUMENT_TYPE = 'Experience Letter'

AND A.PERSON_ID = C.PERSON_ID

AND A.DOCUMENT_TYPE_ID = D.DOCUMENT_TYPE_ID

AND D.LANGUAGE = SYS_CONTEXT('USERENV', 'LANG')

AND SYSDATE BETWEEN C.EFFECTIVE_START_DATE AND C.EFFECTIVE_END_DATE

AND SYSDATE BETWEEN ppnf.EFFECTIVE_START_DATE AND ppnf.EFFECTIVE_END_DATE

--AND B.CATEGORY_CODE = HL.LOOKUP_CODE

--AND d.DOCUMENT_TYPE = HL.LOOKUP_TYPE

AND ppnf.NAME_TYPE = 'GLOBAL'

AND c.person_id = ppnf.person_id

--AND D.DOCUMENT_TYPE = NVL(:Document,D.DOCUMENT_TYPE)

--AND D.DOCUMENT_TYPE_ID = NVL(:P_DOCUMENT,D.DOCUMENT_TYPE_ID)

--AND (COALESCE(NULL, :p_Document_Type) IS NULL OR D.DOCUMENT_TYPE IN (:p_Document_Type))

AND ((D.DOCUMENT_TYPE) IN (:p_Document_Type) OR 'All' IN (:p_Document_Type|| 'All'))

--AND ppnf.FULL_NAME = NVL(:EmployeeName,ppnf.FULL_NAME)

--AND (COALESCE(NULL, :p_employee) IS NULL OR ppnf.display_name IN (:p_employee))

AND ((ppnf.display_name) IN (:p_employee) OR 'All' IN (:p_employee|| 'All'))

--AND ppnf.person_id = NVL(:P_EMPLOYEE,ppnf.person_id)

------------------------------------legal---------------------------------------

AND haouf.ORGANIZATION_ID = houcf.ORGANIZATION_ID

AND haouf.ORGANIZATION_ID = hauft.ORGANIZATION_ID

AND haouf.EFFECTIVE_START_DATE BETWEEN houcf.EFFECTIVE_START_DATE AND houcf.EFFECTIVE_END_DATE

AND hauft.LANGUAGE = 'US'

AND hauft.EFFECTIVE_START_DATE = haouf.EFFECTIVE_START_DATE

AND hauft.EFFECTIVE_END_DATE = haouf.EFFECTIVE_END_DATE

AND houcf.CLASSIFICATION_CODE = 'HCM_LEMP'

AND SYSDATE BETWEEN hauft.effective_start_date AND hauft.effective_end_date

AND hauft.organization_id = paam1.legal_entity_id

and paam1.person_id = c.person_id

and paam1.primary_assignment_flag = 'Y'

and paam1.assignment_type = 'E'

and paam1.effective_latest_change = 'Y'

and sysdate between paam1.effective_start_date and paam1.effective_end_date

--AND hauft.NAME = NVL(:Legal,hauft.NAME)

--AND hauft.ORGANIZATION_ID = NVL(:P_LEGAL_ENTITY,hauft.ORGANIZATION_ID)

--AND (COALESCE(NULL, :p_legal_entity) IS NULL OR hauft.NAME IN (:p_legal_entity))

AND ((hauft.NAME) IN (:p_legal_entity) OR 'All' IN (:p_legal_entity|| 'All'))

order by c.PERSON_NUMBER asc

----------------parameter query

select

NVL((SELECT distinct ppnf.display_name FROM PER_PERSON_NAMES_F ppnf WHERE 1=1 and ppnf.NAME_TYPE='GLOBAL' and rownum<=1 and PPNF.display_name = :p_employee),'ALL') PE_EMPLOYEE,

NVL((SELECT NAME FROM hr_organization_units_f_tl WHERE 1=1 and language='US' and NAME = :p_legal_entity and rownum = 1),'ALL') PE_LEGAL_ENTITY_ID,

NVL((Select DISTINCT DOCUMENT_TYPE from HR_DOCUMENT_TYPES_TL where 1=1 and rownum=1 and DOCUMENT_TYPE = :p_Document_Type),'ALL')PE_Document_Type

FROM

dual

---------------------------------------------------------------

-----------------employee absence plan balance-----------------------

----------parameters

1.p_legal_entity

2.p_department

3.p_grade

4.p_employee

5.p_plan_name

6.p_effective_start_date

7.p_effective_end_date

----------------list of values

------employee name lov

select distinct ppnf.display_name

from PER_PERSON_NAMES_F ppnf

where trunc(sysdate) between trunc(ppnf.EFFECTIVE_START_DATE) and trunc(ppnf.EFFECTIVE_END_DATE)

and ppnf.NAME_TYPE='GLOBAL'

------plan name lov

SELECT DISTINCT name

FROM (SELECT name

FROM anc_absence_plans_vl

WHERE 1 = 1

AND plan_status = 'A'

UNION

SELECT name

FROM anc_absence_types_vl

WHERE 1 = 1

AND status = 'A')

-------grade lov

select distinct NAME from PER_GRADES_F_VL

--------department name lov

select distinct name from PER_DEPARTMENTS

-------legal entity lov

select distinct

haou1.name

FROM HR_ORGANIZATION_UNITS_F_TL haou1

,PER_ALL_ASSIGNMENTS_F paam

WHERE 1=1

and trunc(sysdate) between trunc(paam.EFFECTIVE_START_DATE) and trunc(paam.EFFECTIVE_END_DATE)

and paam.ASSIGNMENT_TYPE in ('E','C')

and paam.PRIMARY_FLAG='Y'

and paam.SYSTEM_PERSON_TYPE='EMP'

and paam.EFFECTIVE_LATEST_CHANGE='Y'

and paam.LEGAL_ENTITY_ID=haou1.ORGANIZATION_ID

and trunc(sysdate) between trunc(haou1.EFFECTIVE_START_DATE) and trunc(haou1.EFFECTIVE_END_DATE)

and haou1.LANGUAGE='US'

--and rownum<=1

--------------------------main query

select * from((SELECT papf.person_number,

PAPF.person_id,

ppnf.display_name person_name,

houft.name legalemployer,

ppos.worker_number,

houft.organization_id,

aapft.name AS PLAN_NAME,

aapft.absence_plan_id,

To_char(aapft.effective_start_date, 'MM/DD/YYYY')PLAN_START_DATE,

To_char(aapft.effective_end_date, 'MM/DD/YYYY') PLAN_END_DATE,

To_char(appe.enrt_st_dt, 'MM/DD/YYYY')

Enrollment_Start_Date,

To_char(appe.last_accrual_run, 'MM/DD/YYYY')

Balance_Calculation_Date

appe.last_accrual_run,

Nvl(apae.end_bal, 0)

Absence_Plan_Balance,

Decode (aap.plan_uom, 'D', 'Days',

'H', 'Hours') plan_balance_dh,

(SELECT To_char(Trunc(SYSDATE), 'MM/DD/YYYY')

FROM dual) SYS,

(SELECT user_status

FROM per_assignment_status_types_vl

WHERE assignment_status_type_id =

paam.assignment_status_type_id

AND ROWNUM <= 1)

assignment_status,

(SELECT name

FROM per_departments

WHERE organization_id = paam.organization_id

AND Trunc(Trunc(SYSDATE)) BETWEEN

Trunc(effective_start_date) AND

Trunc(

effective_end_date)

AND ROWNUM <= 1) department,

(SELECT organization_id

FROM per_departments

WHERE organization_id = paam.organization_id

AND Trunc(Trunc(SYSDATE)) BETWEEN

Trunc(effective_start_date) AND

Trunc(

effective_end_date)

AND ROWNUM <= 1) department_ID,

(SELECT name

FROM per_grades_f_vl

WHERE grade_id = paam.grade_id

AND Trunc(Trunc(SYSDATE)) BETWEEN

Trunc(effective_start_date) AND

Trunc(

effective_end_date)

AND ROWNUM <= 1) grade,

(SELECT grade_id

FROM per_grades_f_vl

WHERE grade_id = paam.grade_id

AND Trunc(Trunc(SYSDATE)) BETWEEN

Trunc(effective_start_date) AND

Trunc(

effective_end_date)

AND ROWNUM <= 1) grade_ID,

(SELECT start_date

FROM anc_per_abs_entries f

WHERE person_id = paam.person_id

AND period_of_service_id = ppos.period_of_service_id

AND ROWNUM <= 1)

absence_start_date,

(SELECT end_date

FROM anc_per_abs_entries f

WHERE person_id = paam.person_id

AND period_of_service_id = ppos.period_of_service_id

AND ROWNUM <= 1) absence_end_date

FROM per_all_people_f papf,

per_person_names_f ppnf,

per_all_assignments_m paam,

per_periods_of_service ppos,

hr_organization_units_f_tl houft,

anc_per_plan_enrollment appe,

anc_absence_plans_f_tl aapft,

anc_per_accrual_entries apae,

anc_absence_plans_vl aap

WHERE 1 = 1

AND ppnf.person_id = papf.person_id

AND ppnf.name_type (+) = 'GLOBAL'

AND papf.person_id = paam.person_id

AND paam.primary_flag = 'Y'

AND paam.assignment_type = 'E'

AND paam.effective_latest_change = 'Y'

AND ppos.period_of_service_id = paam.period_of_service_id

AND ppos.date_start = (SELECT Max(date_start)

FROM per_periods_of_service ppos1

WHERE 1 = 1

AND ppos1.person_id =

papf.person_id

AND period_type = 'E')

AND houft.organization_id = paam.legal_entity_id

AND houft.LANGUAGE = 'US'

AND Trunc(SYSDATE) BETWEEN papf.effective_start_date AND

papf.effective_end_date

AND Trunc(SYSDATE) BETWEEN ppnf.effective_start_date AND

ppnf.effective_end_date

AND Trunc(SYSDATE) BETWEEN paam.effective_start_date AND

paam.effective_end_date

AND Trunc(SYSDATE) BETWEEN houft.effective_start_date AND

houft.effective_end_date

AND appe.person_id(+) = papf.person_id

AND Trunc(SYSDATE) BETWEEN appe.enrt_st_dt AND appe.enrt_end_dt

AND aapft.absence_plan_id = appe.plan_id

AND aapft.LANGUAGE = 'US'

AND Trunc(SYSDATE) BETWEEN aapft.effective_start_date AND

aapft.effective_end_date

AND apae.per_plan_enrt_id = appe.per_plan_enrt_id

AND apae.accrual_period = appe.last_accrual_run

AND aapft.absence_plan_id = aap.absence_plan_id

AND aap.LANGUAGE = 'US'

AND Trunc(SYSDATE) BETWEEN aap.effective_start_date AND

aap.effective_end_date

AND aap.plan_status = 'A'

--and apae.end_bal is not null

--AND papf.person_number = '485'

)

UNION

(SELECT papf.person_number,

PAPF.person_id,

ppnf.display_name person_name,

houft.name legalemployer,

ppos.worker_number,

houft.organization_id,

aatl.name AS PLAN_NAME,

aatl.absence_type_id,

To_char(apaes.start_date, 'MM/DD/YYYY') PLAN_START_DATE,

To_char(apaes.end_date, 'MM/DD/YYYY') PLAN_END_DATE,

NULL Enrollment_Start_Date,

--to_char(appe.enrt_st_dt,'DD/MM/YYYY') Enrollment_Start_Date,

To_char(apaes.start_date, 'MM/DD/YYYY') Balance_Calculation_Date,

apaes.start_date last_accrual_run,

Nvl(CASE

WHEN aatl.name = 'Annual Leave' THEN 30

WHEN aatl.name = 'Marriage Leave' THEN 5

ELSE aatl.max_duration

END - apaes.duration, 0) Absence_Plan_Balance,

Decode (apaes.uom, 'D', 'Days',

'H', 'Hours') plan_balance_dh,

(SELECT To_char(Trunc(SYSDATE), 'MM/DD/YYYY')

FROM dual) SYS,

(SELECT user_status

FROM per_assignment_status_types_vl

WHERE assignment_status_type_id =

paam.assignment_status_type_id

AND ROWNUM <= 1) assignment_status,

(SELECT name

FROM per_departments

WHERE organization_id = paam.organization_id

AND Trunc(Trunc(SYSDATE)) BETWEEN

Trunc(effective_start_date) AND

Trunc(

effective_end_date)

AND ROWNUM <= 1) department,

(SELECT organization_id

FROM per_departments

WHERE organization_id = paam.organization_id

AND Trunc(Trunc(SYSDATE)) BETWEEN

Trunc(effective_start_date) AND

Trunc(

effective_end_date)

AND ROWNUM <= 1) department_ID,

(SELECT name

FROM per_grades_f_vl

WHERE grade_id = paam.grade_id

AND Trunc(Trunc(SYSDATE)) BETWEEN

Trunc(effective_start_date) AND

Trunc(

effective_end_date)

AND ROWNUM <= 1) grade,

(SELECT grade_id

FROM per_grades_f_vl

WHERE grade_id = paam.grade_id

AND Trunc(Trunc(SYSDATE)) BETWEEN

Trunc(effective_start_date) AND

Trunc(

effective_end_date)

AND ROWNUM <= 1) grade_ID,

(SELECT start_date

FROM anc_per_abs_entries f

WHERE person_id = paam.person_id

AND period_of_service_id = ppos.period_of_service_id

AND ROWNUM <= 1) absence_start_date,

(SELECT end_date

FROM anc_per_abs_entries f

WHERE person_id = paam.person_id

AND period_of_service_id = ppos.period_of_service_id

AND ROWNUM <= 1) absence_end_date

FROM per_all_people_f papf,

per_person_names_f ppnf,

per_all_assignments_m paam,

per_periods_of_service ppos,

hr_organization_units_f_tl houft,

anc_per_abs_entries apaes,

anc_absence_types_vl aatl

WHERE 1 = 1

AND aatl.name not in('Annual Leave','Short Leave')

AND apaes.period_of_service_id = ppos.period_of_service_id

AND apaes.absence_type_id = aatl.absence_type_id

AND ppnf.person_id = papf.person_id

AND ppnf.name_type (+) = 'GLOBAL'

AND papf.person_id = paam.person_id

and apaes.APPROVAL_STATUS_CD='APPROVED'

AND apaes.ABSENCE_STATUS_CD!='ORA_WITHDRAWN'

AND paam.primary_flag = 'Y'

AND paam.assignment_type = 'E'

AND paam.effective_latest_change = 'Y'

AND ppos.period_of_service_id = paam.period_of_service_id

AND ppos.date_start = (SELECT Max(date_start)

FROM per_periods_of_service ppos1

WHERE 1 = 1

AND ppos1.person_id =

papf.person_id

AND period_type = 'E')

AND houft.organization_id = paam.legal_entity_id

AND houft.LANGUAGE = 'US'

AND aatl.LANGUAGE = 'US'

AND Trunc(SYSDATE) BETWEEN papf.effective_start_date AND

papf.effective_end_date

AND Trunc(SYSDATE) BETWEEN ppnf.effective_start_date AND

ppnf.effective_end_date

AND Trunc(SYSDATE) BETWEEN paam.effective_start_date AND

paam.effective_end_date

AND Trunc(SYSDATE) BETWEEN houft.effective_start_date AND

houft.effective_end_date

AND aatl.status = 'A'

--AND papf.person_number = '485'

)

) a

WHERE 1 = 1

AND (a.legalemployer IN (:p_legal_entity) OR COALESCE(:p_legal_entity,NULL)IS NULL)

AND (a.department IN (:p_department) OR COALESCE(:p_department,NULL)IS NULL)

AND (A.grade IN (:p_grade) OR COALESCE(:p_grade,NULL)IS NULL)

AND (a.plan_name IN (:p_PLAN_NAME) OR COALESCE(:p_PLAN_NAME,NULL)IS NULL)

AND (a.person_name IN (:p_employee) OR COALESCE(:p_employee,NULL)IS NULL)

AND Trunc (a.last_accrual_run) BETWEEN

nvl(:P_EFFECTIVE_START_DATE,a.last_accrual_run) AND nvl(:P_EFFECTIVE_END_DATE,a.last_accrual_run)

order by a.worker_number asc

---------------------------parameters

select

Nvl((SELECT name

FROM hr_organization_units_f_tl

WHERE 1 = 1

AND LANGUAGE = 'US'

AND ROWNUM <= 1

AND name = :P_LEGAL_ENTITY), 'ALL') PE_LEGAL_ENTITY_ID,

Nvl((SELECT DISTINCT name

FROM per_departments

WHERE name = :P_DEPARTMENT), 'ALL') PE_DEPARTMENT,

Nvl((SELECT DISTINCT name

FROM per_grades_f_vl

WHERE name = :P_GRADE), 'ALL') PE_GRADE,

To_char(:P_EFFECTIVE_START_DATE, 'MM/DD/YYYY', 'NLS_DATE_LANGUAGE = AMERICAN')

PE_EFFECTIVE_START_DATE,

To_char(:P_EFFECTIVE_END_DATE, 'MM/DD/YYYY', 'NLS_DATE_LANGUAGE = AMERICAN')

PE_EFFECTIVE_END_DATE,

Nvl((SELECT DISTINCT ppnf.display_name

FROM per_person_names_f ppnf

WHERE 1 = 1

AND ppnf.name_type = 'GLOBAL'

AND ROWNUM <= 1

AND PPNF.display_name = :P_EMPLOYEE), 'ALL') PE_EMPLOYEE,

Nvl((SELECT DISTINCT name

FROM anc_absence_plans_vl

WHERE 1 = 1

AND plan_status = 'A'

AND name = :P_PLAN_NAME

UNION

SELECT DISTINCT name

FROM anc_absence_types_vl

WHERE 1 = 1

AND status = 'A'

AND name = :P_PLAN_NAME), 'ALL') PE_PLAN_NAME

from

dual

--------------------------test1

select a.* from

(select

papf.person_number,

PAPF.PERSON_ID,

ppnf.display_name person_name,

houft.name legalemployer,

ppos.WORKER_NUMBER,

houft.organization_id,

aapft.name as PLAN_NAME,

aapft.absence_plan_id ,

to_char(aapft.EFFECTIVE_START_DATE,'DD/MM/YYYY')PLAN_START_DATE,

to_char(aapft.EFFECTIVE_END_DATE,'DD/MM/YYYY')PLAN_END_DATE,

to_char(appe.enrt_st_dt,'DD/MM/YYYY') Enrollment_Start_Date,

to_char(appe.last_accrual_run,'DD/MM/YYYY') Balance_Calculation_Date,

appe.last_accrual_run,

nvl(apae.end_bal,0) Absence_Plan_Balance,

decode (aap.PLAN_UOM,'D','Days','H','Hours') plan_balance_dh,

(select to_char(trunc(SYSDATE),'DD/MM/YYYY') FROM DUAL) SYS,

(select USER_STATUS from PER_ASSIGNMENT_STATUS_TYPES_VL where ASSIGNMENT_STATUS_TYPE_ID=paam.ASSIGNMENT_STATUS_TYPE_ID and rownum <=1) assignment_status,

(select name from PER_DEPARTMENTS where ORGANIZATION_ID=paam.ORGANIZATION_ID

and trunc(trunc(SYSDATE)) between trunc(EFFECTIVE_START_DATE) and trunc(EFFECTIVE_END_DATE)and rownum <=1) department,

(select ORGANIZATION_ID from PER_DEPARTMENTS where ORGANIZATION_ID=paam.ORGANIZATION_ID

and trunc(trunc(SYSDATE)) between trunc(EFFECTIVE_START_DATE) and trunc(EFFECTIVE_END_DATE)and rownum <=1) department_ID,

(select name from PER_GRADES_F_VL where Grade_id=paam.grade_id

and trunc(trunc(SYSDATE)) between trunc(EFFECTIVE_START_DATE) and trunc(EFFECTIVE_END_DATE)and rownum <=1) grade,

(select Grade_id from PER_GRADES_F_VL where Grade_id=paam.grade_id

and trunc(trunc(SYSDATE)) between trunc(EFFECTIVE_START_DATE) and trunc(EFFECTIVE_END_DATE)and rownum <=1) grade_ID,

(select start_date from ANC_PER_ABS_ENTRIES f where person_id=paam.person_id

and period_of_service_id = ppos.period_of_Service_id and rownum <=1) absence_start_date,

(select END_DATE from ANC_PER_ABS_ENTRIES f where person_id=paam.person_id

and period_of_service_id = ppos.period_of_Service_id and rownum <=1) absence_end_date

From per_all_people_f papf,

per_person_names_f ppnf,

per_all_assignments_m paam,

per_periods_of_service ppos,

hr_organization_units_f_tl houft,

anc_per_plan_enrollment appe,

anc_absence_plans_f_tl aapft,

anc_per_accrual_entries apae,

ANC_ABSENCE_PLANS_VL aap

Where 1=1

and ppnf.person_id = papf.person_id

and ppnf.name_type (+)= 'GLOBAL'

and papf.person_id = paam.person_id

and paam.primary_flag = 'Y'

and paam.assignment_type = 'E'

and paam.effective_latest_change ='Y'

and ppos.period_of_service_id = paam.period_of_service_id

AND ppos.date_start = (select max(date_start)

from per_periods_of_service ppos1

where 1=1

and ppos1.person_id = papf.person_id

and period_type = 'E')

and houft.organization_id = paam.legal_entity_id

and houft.LANGUAGE = 'US'

and trunc(SYSDATE) between papf.effective_start_date and papf.effective_end_date

and trunc(SYSDATE) between ppnf.effective_start_date and ppnf.effective_end_date

and trunc(SYSDATE) between paam.effective_start_date and paam.effective_end_date

and trunc(SYSDATE) BETWEEN houft.effective_start_date AND houft.effective_end_date

and appe.Person_id(+) = papf.Person_id

and trunc(SYSDATE) between appe.enrt_st_dt and appe.enrt_end_dt

and aapft.absence_plan_id = appe.plan_id

and aapft.LANGUAGE = 'US'

and trunc(SYSDATE) between aapft.effective_start_date and aapft.effective_end_date

and apae.per_plan_enrt_id = appe.per_plan_enrt_id

and apae.accrual_period = appe.last_accrual_run

and aapft.absence_plan_id = aap.absence_plan_id

and aap.LANGUAGE = 'US'

and trunc(SYSDATE) between aap.effective_start_date and aap.effective_end_date

and aap.PLAN_STATUS='A'

--and apae.end_bal is not null

--and papf.person_number = '20002'

) a

where 1=1

AND (a.legalemployer IN (:p_legal_entity) OR COALESCE(:p_legal_entity,NULL)IS NULL)

AND (a.department IN (:p_department) OR COALESCE(:p_department,NULL)IS NULL)

AND (A.grade IN (:p_grade) OR COALESCE(:p_grade,NULL)IS NULL)

AND (a.plan_name IN (:p_PLAN_NAME) OR COALESCE(:p_PLAN_NAME,NULL)IS NULL)

AND (a.person_name IN (:p_employee) OR COALESCE(:p_employee,NULL)IS NULL)

AND Trunc (a.last_accrual_run) BETWEEN

nvl(:P_EFFECTIVE_START_DATE,a.last_accrual_run) AND nvl(:P_EFFECTIVE_END_DATE,a.last_accrual_run)

--------------test11

SELECT papf.person_number,

PAPF.person_id,

ppnf.display_name person_name,

houft.name legalemployer,

ppos.worker_number,

houft.organization_id,

aatl.name AS PLAN_NAME,

aatl.absence_type_id,

To_char(apaes.start_date, 'MM/DD/YYYY') PLAN_START_DATE,

To_char(apaes.end_date, 'MM/DD/YYYY') PLAN_END_DATE,

NULL Enrollment_Start_Date,

--to_char(appe.enrt_st_dt,'DD/MM/YYYY') Enrollment_Start_Date,

To_char(apaes.start_date, 'MM/DD/YYYY') Balance_Calculation_Date,

apaes.start_date last_accrual_run,

Nvl(CASE

WHEN aatl.name = 'Annual Leave' THEN 30

WHEN aatl.name = 'Marriage Leave' THEN 5

ELSE aatl.max_duration

END - apaes.duration, 0) Absence_Plan_Balance,

Decode (apaes.uom, 'D', 'Days',

'H', 'Hours') plan_balance_dh,

(SELECT To_char(Trunc(SYSDATE), 'MM/DD/YYYY')

FROM dual) SYS,

(SELECT user_status

FROM per_assignment_status_types_vl

WHERE assignment_status_type_id =

paam.assignment_status_type_id

AND ROWNUM <= 1) assignment_status,

(SELECT name

FROM per_departments

WHERE organization_id = paam.organization_id

AND Trunc(Trunc(SYSDATE)) BETWEEN

Trunc(effective_start_date) AND

Trunc(

effective_end_date)

AND ROWNUM <= 1) department,

(SELECT organization_id

FROM per_departments

WHERE organization_id = paam.organization_id

AND Trunc(Trunc(SYSDATE)) BETWEEN

Trunc(effective_start_date) AND

Trunc(

effective_end_date)

AND ROWNUM <= 1) department_ID,

(SELECT name

FROM per_grades_f_vl

WHERE grade_id = paam.grade_id

AND Trunc(Trunc(SYSDATE)) BETWEEN

Trunc(effective_start_date) AND

Trunc(

effective_end_date)

AND ROWNUM <= 1) grade,

(SELECT grade_id

FROM per_grades_f_vl

WHERE grade_id = paam.grade_id

AND Trunc(Trunc(SYSDATE)) BETWEEN

Trunc(effective_start_date) AND

Trunc(

effective_end_date)

AND ROWNUM <= 1) grade_ID,

(SELECT start_date

FROM anc_per_abs_entries f

WHERE person_id = paam.person_id

AND period_of_service_id = ppos.period_of_service_id

AND ROWNUM <= 1) absence_start_date,

(SELECT end_date

FROM anc_per_abs_entries f

WHERE person_id = paam.person_id

AND period_of_service_id = ppos.period_of_service_id

AND ROWNUM <= 1) absence_end_date

FROM per_all_people_f papf,

per_person_names_f ppnf,

per_all_assignments_m paam,

per_periods_of_service ppos,

hr_organization_units_f_tl houft,

anc_per_abs_entries apaes,

anc_absence_types_vl aatl

WHERE 1 = 1

--AND aatl.name not in('Annual Leave','Sick Leave','Short Leave')

AND apaes.period_of_service_id = ppos.period_of_service_id

AND apaes.absence_type_id = aatl.absence_type_id

AND ppnf.person_id = papf.person_id

AND ppnf.name_type (+) = 'GLOBAL'

AND papf.person_id = paam.person_id

and apaes.APPROVAL_STATUS_CD='APPROVED'

AND paam.primary_flag = 'Y'

AND paam.assignment_type = 'E'

AND paam.effective_latest_change = 'Y'

AND ppos.period_of_service_id = paam.period_of_service_id

AND ppos.date_start = (SELECT Max(date_start)

FROM per_periods_of_service ppos1

WHERE 1 = 1

AND ppos1.person_id =

papf.person_id

AND period_type = 'E')

AND houft.organization_id = paam.legal_entity_id

AND houft.LANGUAGE = 'US'

AND aatl.LANGUAGE = 'US'

AND Trunc(SYSDATE) BETWEEN papf.effective_start_date AND

papf.effective_end_date

AND Trunc(SYSDATE) BETWEEN ppnf.effective_start_date AND

ppnf.effective_end_date

AND Trunc(SYSDATE) BETWEEN paam.effective_start_date AND

paam.effective_end_date

AND Trunc(SYSDATE) BETWEEN houft.effective_start_date AND

houft.effective_end_date

AND aatl.status = 'A'

AND papf.person_number = '9'

-----------------------------------------------------------------------

----------------employee dependent report----------------------------

----------parameters

1.p_legal_entity

2.p_location

3.p_department

4.p_Grade

5.p_position

6.p_employee

7.p_employee_number

8.p_dependant_number

9.p_contact_type

10.P_EFFECTIVE_START_DATE

11.P_EFFECTIVE_END_DATE

--------------list of values

------L_Dependant_number lov

select distinct papf.person_number, pcr.CONTACT_PERSON_ID

from per_contact_relationships pcr,

per_all_people_f papf

where

papf.person_id= pcr.contact_person_id

and trunc(sysdate) between trunc(pcr.EFFECTIVE_START_DATE) and trunc(pcr.EFFECTIVE_END_DATE)

-------L_Emp_Number lov

select

distinct papf.person_number ,papf.person_id

from per_all_people_f papf

where trunc(sysdate) between trunc(papf.EFFECTIVE_START_DATE) and trunc(papf.EFFECTIVE_END_DATE)

order by

papf.person_number

------L_Employee lov

select

distinct ppnf.display_name ,ppnf.person_id

from PER_PERSON_NAMES_F ppnf

where trunc(sysdate) between trunc(ppnf.EFFECTIVE_START_DATE) and trunc(ppnf.EFFECTIVE_END_DATE)

and ppnf.NAME_TYPE='GLOBAL'

order by

ppnf.person_id asc

-------L_Position lov

select distinct name, POSITION_ID from HR_ALL_POSITIONS_F_VL

------L_Grade lov

select distinct NAME,GRADE_ID from PER_GRADES_F_VL

where ACTIVE_STATUS = 'A'

ORDER BY NAME

-------L_Department lov

select distinct name,ORGANIZATION_ID from PER_DEPARTMENTS

-------L_Location lov

select distinct LOCATION_NAME,LOCATION_ID from HR_LOCATIONS

-------L_Legal_Entity lov

select distinct

haou1.name

,haou1.ORGANIZATION_ID

FROM HR_ORGANIZATION_UNITS_F_TL haou1

,PER_ALL_ASSIGNMENTS_F paam

WHERE 1=1

and trunc(sysdate) between trunc(paam.EFFECTIVE_START_DATE) and trunc(paam.EFFECTIVE_END_DATE)

and paam.ASSIGNMENT_TYPE in ('E','C')

and paam.PRIMARY_FLAG='Y'

and paam.SYSTEM_PERSON_TYPE='EMP'

and paam.EFFECTIVE_LATEST_CHANGE='Y'

and paam.LEGAL_ENTITY_ID=haou1.ORGANIZATION_ID

and trunc(sysdate) between trunc(haou1.EFFECTIVE_START_DATE) and trunc(haou1.EFFECTIVE_END_DATE)

and haou1.LANGUAGE='US'

--and rownum<=1

-------------------------main query

select

A.*

from

(select

-------------------------------------------------employee-details----------------------------------------------------

-- papf.person_number,

-- papf.person_id p_id,

paam.assignment_number,

ppnf.display_name Person_Name,

pas.user_status assignment_status,

TO_NUMBER(ppos.worker_number) worker_number,

to_char(ppos.date_start,'mm/dd/yyyy') hire_date,

(select

decode(sex, 'M', 'Male', 'F', 'Female', null)

from

PER_PEOPLE_LEGISLATIVE_F

where

PERSON_ID=pcr.CONTACT_PERSON_ID

and trunc(sysdate) between trunc(EFFECTIVE_START_DATE) and trunc(EFFECTIVE_END_DATE)) gender,

pd.name Department,

hl.location_name,

hap.name Position,

pj.name Job,

pg.name Grade,

haou1.name LE,

--------------------------------------------------dependent-details--------------------------------------------------

decode(pcr.contact_type,

'IN_S', 'Son',

'IN_D', 'Daughter',

'S', 'Spouse',

'EMRG', 'Emergency',

'M', 'Contact',

'F','Friend',

'IN_FR', 'Father',

'IN_MR', 'Mother') Contact_type,

decode(pcr.EMERGENCY_CONTACT_FLAG,

'Y','Yes',

'N','No') Emergency_contact,

decode( pcr.CONT_INFORMATION4,

'Y', 'Yes',

'N', 'No') STATUTORY_DEPENDENT,

(select

person_number

from

PER_ALL_PEOPLE_F

where

person_id=pcr.CONTACT_PERSON_ID

and trunc(sysdate) between trunc(EFFECTIVE_START_DATE) and trunc(EFFECTIVE_END_DATE)) Dependent_Person_Number,

(select

display_name

from

PER_PERSON_NAMES_F

where

person_id=pcr.CONTACT_PERSON_ID

and trunc(sysdate) between trunc(EFFECTIVE_START_DATE) and trunc(EFFECTIVE_END_DATE)

and name_type = 'GLOBAL') Dependent_Name,

(select

to_char(pp.DATE_OF_BIRTH,'mm/dd/yyyy')

from

PER_PERSONS pp

where

pp.person_id = pcr.CONTACT_PERSON_ID) DATE_OF_BIRTH,

(SELECT

CASE

WHEN

age is not null

THEN

age || ' ' || Y_M

ELSE null

END

FROM

(SELECT

pp.DATE_OF_BIRTH,

CASE

WHEN

TRUNC(MONTHS_BETWEEN(SYSDATE, pp.DATE_OF_BIRTH) / 12) < 1

THEN

TRUNC(MONTHS_BETWEEN(SYSDATE, pp.DATE_OF_BIRTH))

ELSE

TRUNC(MONTHS_BETWEEN(SYSDATE, pp.DATE_OF_BIRTH) / 12)

END AS age,

CASE

WHEN

TRUNC(MONTHS_BETWEEN(SYSDATE, pp.DATE_OF_BIRTH) / 12) < 1

THEN

'M'

ELSE

'Y'

END AS Y_M

FROM

PER_PERSONS pp

WHERE

pp.person_id = pcr.CONTACT_PERSON_ID)) AGE,

(select

decode(MARITAL_STATUS,'M', 'Married', 'S', 'Single', NULL)

from

PER_PEOPLE_LEGISLATIVE_F

where

PERSON_ID=pcr.CONTACT_PERSON_ID

and trunc(sysdate) between trunc(EFFECTIVE_START_DATE) and trunc(EFFECTIVE_END_DATE)) MARITAL_STATUS,

(select

ppv.PHONE_NUMBER

from

PER_PERSON_SECURED_LIST_V ppsl,

PER_PHONES_V ppv

where

ppsl.primary_phone_id = ppv.phone_id

and ppv.person_id = pcr.CONTACT_PERSON_ID

and rownum = 1) Dependent_Phone,

(select

EMAIL_ADDRESS

from

PER_PERSON_SECURED_LIST_V ppsl,

PER_EMAIL_ADDRESSES pea

where

ppsl.primary_email_id = pea.EMAIL_ADDRESS_ID

and pea.person_id = pcr.CONTACT_PERSON_ID

and rownum = 1) Dependant_email,

(select

pa.ADDRESS_LINE_1||','||pa.ADDRESS_LINE_2||','||pa.ADDRESS_LINE_3||','||pa.TOWN_OR_CITY ||','||ftv1.TERRITORY_SHORT_NAME address

from

PER_PERSON_ADDR_USAGES_F ppa,

PER_ADDRESSES_F pa,

FND_TERRITORIES_VL ftv1

where

ppa.ADDRESS_ID=pa.ADDRESS_ID

and ppa.person_id=pcr.CONTACT_PERSON_ID

and trunc(sysdate) between trunc(ppa.EFFECTIVE_START_DATE) and trunc(ppa.EFFECTIVE_END_DATE)

and trunc(sysdate) between trunc(pa.EFFECTIVE_START_DATE) and trunc(pa.EFFECTIVE_END_DATE)

and ftv1.TERRITORY_CODE=pa.COUNTRY

and ROWNUM=1) dependent_address,

(select

PNI.NATIONAL_IDENTIFIER_TYPE

from

PER_NATIONAL_IDENTIFIERS pni

where

pni.PERSON_ID(+)=pcr.CONTACT_PERSON_ID) Nat_ID_TYPE,

(select

PNI.NATIONAL_IDENTIFIER_NUMBER

from

PER_NATIONAL_IDENTIFIERS pni

where

pni.PERSON_ID(+)=pcr.CONTACT_PERSON_ID) Nat_ID_NUM,

(SELECT

ft.TERRITORY_SHORT_NAME

FROM

FND_TERRITORIES_VL ft,

PER_NATIONAL_IDENTIFIERS pni

WHERE

pni.LEGISLATION_CODE = ft.TERRITORY_CODE

and pni.PERSON_ID = pcr.CONTACT_PERSON_ID) Nationality

from

per_all_people_f papf,

per_all_assignments_f paam,

per_person_names_f ppnf,

HR_ORGANIZATION_UNITS_F_TL haou1,

per_assignment_status_types_tl pas,

per_periods_of_service ppos,

per_people_legislative_f pplf,

per_departments pd,

hr_locations hl,

PER_JOBS pj,

hr_all_positions_f_vl hap,

PER_GRADES_F_VL pg,

per_contact_relationships pcr

where 1=1

and trunc(sysdate) between trunc(papf.effective_start_date) and trunc(papf.effective_end_date)

and papf.person_id=paam.person_id

and trunc(sysdate) between trunc(paam.effective_start_date) and trunc(paam.effective_end_date)

and paam.primary_flag='Y'

and paam.effective_latest_change='Y'

and paam.ASSIGNMENT_STATUS_TYPE = 'ACTIVE'

and paam.person_id=ppnf.person_id

and trunc(sysdate) between trunc(ppnf.effective_start_date) and trunc(ppnf.effective_end_date)

and ppnf.name_type='GLOBAL'

and paam.LEGAL_ENTITY_ID=haou1.ORGANIZATION_ID

and trunc(sysdate) between trunc(haou1.EFFECTIVE_START_DATE) and trunc(haou1.EFFECTIVE_END_DATE)

and haou1.LANGUAGE='US'

and paam.assignment_status_type_id=pas.assignment_status_type_id(+)

and pas.language = 'US'

and paam.period_of_service_id = ppos.period_of_service_id

and papf.person_id=ppos.person_id

and paam.legislation_code=pplf.legislation_code

and papf.person_id=pplf.person_id

and trunc(sysdate) between trunc(pplf.effective_start_date) and trunc(pplf.effective_end_date)

and paam.organization_id = pd.organization_id(+)

and trunc(sysdate) between trunc(pd.effective_start_date(+)) and trunc(pd.effective_end_date(+))

and paam.location_id = hl.location_id(+)

and paam.job_id=pj.JOB_ID(+)

and trunc(sysdate) between trunc(pj.effective_start_date(+)) and trunc(pj.effective_end_date(+))

and paam.position_id = hap.position_id(+)

and trunc(sysdate) between trunc(hap.effective_start_date(+)) and trunc(hap.effective_end_date(+))

and paam.GRADE_ID = pg.GRADE_ID(+)

and trunc(sysdate) between trunc(pg.effective_start_date(+)) and trunc(pg.effective_end_date(+))

and papf.person_id= pcr.person_id(+)

and trunc(sysdate) between trunc(pcr.EFFECTIVE_START_DATE(+)) and trunc(pcr.EFFECTIVE_END_DATE(+))

-- and papf.person_number = '532'

--------------------------------------------------parameters--------------------------------------------------------

and ppnf.person_id = NVL(:p_employee,ppnf.person_id)

and ppnf.person_id = NVL(:p_employee_number,ppnf.person_id)

and pcr.CONTACT_PERSON_ID = NVL(:p_dependant_number,pcr.CONTACT_PERSON_ID)

and pcr.contact_type = NVL(:p_contact_type,pcr.contact_type)

and haou1.ORGANIZATION_ID = NVL(:p_legal_entity,haou1.ORGANIZATION_ID)

AND ((paam.LOCATION_ID) IN (:p_location) OR 'All' IN (:p_location|| 'All'))

AND ((paam.ORGANIZATION_ID) IN (:p_department) OR 'All' IN (:p_department|| 'All'))

AND ((paam.GRADE_ID) IN (:p_Grade) OR 'All' IN (:p_Grade|| 'All'))

AND trunc(pcr.EFFECTIVE_START_DATE) between :P_EFFECTIVE_START_DATE and :P_EFFECTIVE_END_DATE

AND ((paam.POSITION_ID) IN (:p_position) OR 'All' IN (:p_position|| 'All'))) A

ORDER BY

TO_NUMBER(A.worker_number), A.Dependent_Person_Number

------------parameters query

SELECT

NVL((SELECT NAME FROM hr_organization_units_f_tl WHERE 1=1 and language='US' and ORGANIZATION_ID = :p_legal_entity and rownum = 1),'ALL') PE_LEGAL_ENTITY_ID

,NVL((SELECT distinct LOCATION_NAME FROM HR_LOCATIONS WHERE LOCATION_ID = :p_location),'ALL') PE_LOCATION

,NVL((SELECT distinct NAME FROM PER_DEPARTMENTS WHERE ORGANIZATION_ID = :p_department),'ALL') PE_DEPARTMENT

,NVL((SELECT distinct NAME FROM PER_GRADES_F_VL WHERE GRADE_ID = :p_Grade),'ALL') PE_GRADE

,NVL((SELECT distinct ppnf.display_name FROM PER_PERSON_NAMES_F ppnf WHERE 1=1 and ppnf.NAME_TYPE='GLOBAL' and PPNF.PERSON_ID = :p_employee),'ALL') PE_EMPLOYEE

,NVL((SELECT distinct papf.person_number from PER_ALL_PEOPLE_F papf where trunc(sysdate) between trunc(papf.EFFECTIVE_START_DATE) and trunc(papf.EFFECTIVE_END_DATE) and papf.PERSON_ID = :p_employee_number),'ALL') PE_EMP_NUMBER

,NVL((SELECT distinct papf.person_number from per_contact_relationships pcr, PER_ALL_PEOPLE_F papf where papf.person_id = pcr.contact_person_id and trunc(sysdate) between trunc(pcr.EFFECTIVE_START_DATE) and trunc(pcr.EFFECTIVE_END_DATE) and pcr.CONTACT_PERSON_ID = :p_dependant_number),'ALL') PE_DEP_NUMBER

,NVL((select distinct hap.name from HR_ALL_POSITIONS_F_VL hap where hap.POSITION_ID = :p_position),'ALL') PE_POSITION

,NVL((decode(:p_contact_type,

'IN_S', 'Son',

'IN_D', 'Daughter',

'S', 'Spouse',

'EMRG', 'Emergency',

'M', 'Contact',

'F','Friend',

'IN_FR', 'Father',

'IN_MR', 'Mother')),'ALL') PE_CONTACT_TYPE

, TO_CHAR(:P_EFFECTIVE_START_DATE, 'MM/DD/YYYY', 'NLS_DATE_LANGUAGE = AMERICAN') PE_EFFECTIVE_START_DATE

, TO_CHAR(:P_EFFECTIVE_END_DATE, 'MM/DD/YYYY', 'NLS_DATE_LANGUAGE = AMERICAN') PE_EFFECTIVE_END_DATE

FROM

DUAL

--------------------------------------------------------------------------------------------------

-----------------experience letterdm old report----------------------------

---------parameters

1.pTransId

-----------main query

select papf.person_number

,ppnf.DISPLAY_NAME||' '||ppnf.NAM_INFORMATION1 employee_name

,ft.TERRITORY_SHORT_NAME Nationality

,pni.NATIONAL_IDENTIFIER_NUMBER

,to_char(ppos.DATE_START,'DD/MM/YYYY') hire_date

,to_char(paam.TERMINATION_DATE,'Dy' ,'nls_date_language=American') TERMINATION_DATE

,hapf.ATTRIBUTE1 arabic_pos

,hapf.name position_name

,(select ft1.TERRITORY_SHORT_NAME from FND_TERRITORIES_TL ft1 where ft1.TERRITORY_CODE=pni.LEGISLATION_CODE and ft1.language='AR') Nationality_arabic

,(SELECT (PPNF1.FIRST_NAME)

FROM PER_PERSON_NAMES_F PPNF1

WHERE 1=1

AND PPNF1.PERSON_ID = PAPF.PERSON_ID

AND SYSDATE BETWEEN PPNF1.EFFECTIVE_START_DATE

AND PPNF1.EFFECTIVE_END_DATE

-- AND PPNF1.NAME_TYPE= 'AR'

and ppnf1.name_type = 'SA'

)"FULL_ARABIC_NAME"

,(select to_char(sysdate,'YYYY/MM/DD' ,'nls_date_language=American') SYS FROM DUAL) sys

,to_char(papf.person_id,0) PersonId

from per_all_people_f papf

,per_all_assignments_m paam

,per_person_names_f ppnf

,per_periods_of_service ppos

,PER_NATIONAL_IDENTIFIERS PNI

,hr_lookups hl

,FND_TERRITORIES_TL ft

,HR_ALL_POSITIONS_F_VL hapf

where 1=1

and trunc(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date

and papf.PERSON_ID=paam.PERSON_ID

and trunc(SYSDATE) BETWEEN paam.effective_start_date AND paam.effective_end_date

and paam.primary_flag='Y'

and paam.assignment_type in ('E','C')

and paam.ASSIGNMENT_STATUS_TYPE='ACTIVE'

and paam.effective_latest_change= 'Y'

AND ppnf.person_id = papf.person_id

and Trunc(sysdate) BETWEEN ppnf.effective_start_date AND ppnf.effective_end_date

AND ppnf.name_type = 'GLOBAL'

and paam.period_of_service_id=ppos.period_of_service_id

and papf.PERSON_ID = PNI.PERSON_ID

and pni.LEGISLATION_CODE=hl.LOOKUP_CODE

and pni.LEGISLATION_CODE=ft.TERRITORY_CODE

and ft.language ='US'

and paam.POSITION_ID=hapf.POSITION_ID

and Trunc(sysdate) BETWEEN hapf.effective_start_date AND hapf.effective_end_date

and hl.LOOKUP_TYPE='NATIONALITY'

--and papf.person_number='20003'

-----------document query

SELECT

txnheader.transaction_id as "transactionId",

extractValue(value(DocumentDetails), '//PersonId/DATA') as "PersonId" ,

extractValue(value(DocumentDetails), '//DocumentsOfRecordId/DATA') as "DocumentsOfRecordId" ,

extractValue(value(DocumentDetails), '//DocumentName/DATA') as "DocumentName" ,

extractValue(value(DocumentDetails), '//DocumentNumber/DATA') as "DocumentNumber" ,

extractValue(value(DocumentDetails), '//DocumentCode/DATA') as "DocumentCode" ,

TO_DATE(extractValue(value(DocumentDetails), '//DateFrom/DATA'),'YYYY-MM-DD') as "DateFrom",

TO_DATE(extractValue(value(DocumentDetails), '//DateTo/DATA'),'YYYY-MM-DD') as "DateTo",

extractValue(value(DocumentDetails), '//IssuingCountry/DATA') as "IssuingCountry",

extractValue(value(DocumentDetails), '//IssuingLocation/DATA') as "IssuingLocation",

TO_DATE(extractValue(value(DocumentDetails), '//IssuedDate/DATA'),'YYYY-MM-DD') as "IssuedOn",

extractValue(value(DocumentDetails), '//IssuingAuthority/DATA') as "IssuingAuthority",

extractValue(value(DocumentDetails), '//Comments/DATA') as "Comments"

FROM hrc_txn_header txnheader, hrc_txn_data txndata,hr_document_types_vl doc_type,fnd_lookup_values_vl category_table,

TABLE(per_bipntf_utility.extractXMLSequenceForEO(:pTransId,'DocumentsOfRecordEO',0)) DocumentDetails

where :pTransId is not null and txndata.transaction_id=:pTransId AND doc_type.document_type_id = extractValue(value(DocumentDetails), '//DocumentTypeId/DATA')

and txnheader.object_id= extractValue(value(DocumentDetails), '//DocumentsOfRecordId/DATA')

and txnheader.object = 'HR_DOCUMENTS_OF_RECORD'

and txnheader.transaction_id = txndata.transaction_id

and lookup_code = doc_type.CATEGORY_CODE and lookup_type ='DOCUMENT_CATEGORY'

and txnheader.object_id = extractValue(value(DocumentDetails), '//PersonId/DATA')

---------------------------------------------------------------------------

--------------experience certificate dm---------------

SELECT

papf.person_id,

papf.person_number,

(SELECT (PPNF1.FIRST_NAME)

FROM PER_PERSON_NAMES_F PPNF1

WHERE 1=1

AND PPNF1.PERSON_ID = PAPF.PERSON_ID

AND SYSDATE BETWEEN PPNF1.EFFECTIVE_START_DATE

AND PPNF1.EFFECTIVE_END_DATE

and ppnf1.name_type = 'SA')"EMPLOYEE_NAME",

to_char(ppos.DATE_START,'MM/DD/YYYY') hire_date,

to_char(paam.TERMINATION_DATE,'Dy' ,'nls_date_language=American') TERMINATION_DATE,

hapf.ATTRIBUTE1 arabic_pos,

hapf.name position_name,

(select to_char(sysdate,'MM/DD/YYYY' ,'nls_date_language=American') SYS FROM DUAL) sys,

(SELECT (PPNF1.FIRST_NAME)

FROM PER_PERSON_NAMES_F PPNF1

WHERE 1=1

AND PPNF1.PERSON_ID = PAPF.PERSON_ID

AND SYSDATE BETWEEN PPNF1.EFFECTIVE_START_DATE

AND PPNF1.EFFECTIVE_END_DATE

and ppnf1.name_type = 'SA')"FULL_ARABIC_NAME",

(select ft1.TERRITORY_SHORT_NAME

from FND_TERRITORIES_TL ft1, per_national_identifiers pni3

where ft1.TERRITORY_CODE = pni3.LEGISLATION_CODE

and ft1.language='AR'

AND pni3.person_id = papf.person_id) Nationality_arabic,

(SELECT pni.national_identifier_number

FROM per_national_identifiers pni

WHERE pni.person_id = papf.person_id

AND ROWNUM = 1

AND pni.last_update_date = (SELECT Max(pni1.last_update_date)

FROM per_national_identifiers pni1

WHERE pni.national_identifier_id =

pni1.national_identifier_id

AND pni1.person_id =

pni.person_id)) National_Identifier_Number,

(SELECT ft.TERRITORY_SHORT_NAME

FROM per_national_identifiers pni2,

FND_TERRITORIES_TL ft

WHERE pni2.person_id = papf.person_id

and pni2.LEGISLATION_CODE=ft.TERRITORY_CODE

and ft.language ='US'

AND ROWNUM = 1

AND pni2.last_update_date = (SELECT Max(pni3.last_update_date)

FROM per_national_identifiers pni3

WHERE pni2.national_identifier_id =

pni3.national_identifier_id

AND pni3.person_id =

pni2.person_id)) Nationality,

(SELECT

hauft.NAME LegalEmployer

FROM

HR_ORG_UNIT_CLASSIFICATIONS_F houcf,

HR_ALL_ORGANIZATION_UNITS_F haouf,

HR_ORGANIZATION_UNITS_F_TL hauft,

per_all_assignments_m paam1

WHERE haouf.ORGANIZATION_ID = houcf.ORGANIZATION_ID

AND haouf.ORGANIZATION_ID = hauft.ORGANIZATION_ID

AND haouf.EFFECTIVE_START_DATE BETWEEN houcf.EFFECTIVE_START_DATE AND houcf.EFFECTIVE_END_DATE

AND hauft.LANGUAGE = 'US'

AND hauft.EFFECTIVE_START_DATE = haouf.EFFECTIVE_START_DATE

AND hauft.EFFECTIVE_END_DATE = haouf.EFFECTIVE_END_DATE

AND houcf.CLASSIFICATION_CODE = 'HCM_LEMP'

AND SYSDATE BETWEEN hauft.effective_start_date AND hauft.effective_end_date

AND hauft.organization_id = paam1.legal_entity_id

and paam1.person_id = papf.person_id

and paam1.primary_assignment_flag = 'Y'

and paam1.assignment_type = 'E'

and paam1.effective_latest_change = 'Y'

and sysdate between paam1.effective_start_date and paam1.effective_end_date) LegalEmployer,