ORACLE TECH CENTER

Thursday, 19 December 2024

Query for Intercompany Imbalance Report

SELECT A.*, A.US_Ledger_amount + A.CA_Ledger_amount Imbalance

FROM (

SELECT --LEDGER ,

Comp_Inter_acct ,

SUM(US_Ledger_amount) US_Ledger_amount ,

SUM(CA_Ledger_amount) CA_Ledger_amount ,

Accounting_Year ,

Accounting_Period

FROM

( SELECT

GL.NAME LEDGER,

CASE WHEN GL.NAME = 'XXXXXXXXXXXXXX'

THEN GCC.SEGMENT1||'-'||GCC.SEGMENT7||'-'|| GCC.SEGMENT4

WHEN GL.NAME = 'XXXXXXXXX'

THEN GCC.SEGMENT7||'-'||GCC.SEGMENT1||'-'|| GCC.SEGMENT4

END Comp_Inter_acct,

CASE WHEN GL.NAME = 'XXXXXXXXXXX'

THEN SUM( NVL(GB.PERIOD_NET_DR,0) - NVL(GB.PERIOD_NET_CR ,0 ))

ELSE 0

END US_Ledger_amount,

CASE WHEN GL.NAME = 'XXXXXXXX'

-- AND ((GB.translated_flag is null) or (GB.translated_flag = 'Y'))

AND ((GB.translated_flag = 'N') or (GB.translated_flag = 'Y'))

THEN SUM( NVL(GB.PERIOD_NET_DR,0) - NVL(GB.PERIOD_NET_CR ,0 ))

ELSE 0

END CA_Ledger_amount,

GB.PERIOD_YEAR Accounting_Year,

GB.PERIOD_NUM Accounting_Period

FROM GL_CODE_COMBINATIONS GCC,

GL_BALANCES GB,

GL_LEDGERS GL

-- GL_JE_HEADERS GJH,

-- GL_JE_LINES GJL

WHERE 1=1

AND GCC.SEGMENT4 IN ('370500') --, '370000')

AND NVL(GB.PERIOD_NET_DR,0) - NVL(GB.PERIOD_NET_CR ,0 ) != 0

-- AND GCC.SEGMENT1||'-'||GCC.SEGMENT7||'-'|| GCC.SEGMENT4 IN ( 'Z01378-C01031-370500')

-- AND GB.PERIOD_NAME = 'May-24'

AND GCC.CODE_COMBINATION_ID = GB.CODE_COMBINATION_ID

-- AND GB.CURRENCY_CODE = GL.CURRENCY_CODE

AND GB.LEDGER_ID = GL.LEDGER_ID

AND GB.CURRENCY_CODE = 'USD'

AND GL.NAME IN ( XXXXXXXXXX )

AND GL.LEDGER_ID IN (SELECT gl.ledger_id

FROM fun_user_role_data_asgnmnts f,

per_roles_dn_vl p,

per_users pu,

gl_ledgers gl

WHERE p.role_common_name = f.role_name

AND pu.user_guid = f.user_guid

AND gl.ledger_id = f.ledger_id

AND f.active_flag = 'Y'

and upper(pu.username) = upper(:xdo_user_name))

AND GB.PERIOD_YEAR = :P_YEAR

AND GB.PERIOD_NUM = :P_NUM

-- AND GCC.SEGMENT4 = NVL(:P_ACCOUNT, GCC.SEGMENT4)

-- AND GJH.JE_HEADER_ID = GJL.JE_HEADER_ID

-- AND GJH.ledger_id = GJL.ledger_id

-- AND GJH.LEDGER_ID = GL.LEDGER_ID

-- AND GCC.CODE_COMBINATION_ID = GJL.CODE_COMBINATION_ID

-- AND GJH.status = 'P'

-- AND GJL.PERIOD_NAME = 'May-24'

-- AND GB.PERIOD_NAME = GJL.PERIOD_NAME

-- AND GB.LEDGER_ID = GJL.LEDGER_ID

-- AND GB.CODE_COMBINATION_ID = GJL.CODE_COMBINATION_ID

GROUP BY GL.NAME, GCC.SEGMENT1, GCC.SEGMENT7, GCC.SEGMENT4,

GB.TRANSLATED_FLAG, GB.PERIOD_YEAR, GB.PERIOD_NUM

)

WHERE 1=1

AND (US_Ledger_amount + CA_Ledger_amount) != 0

GROUP BY --LEDGER ,

Comp_Inter_acct ,

Accounting_Year ,

Accounting_Period

WHERE 1=1

AND (A.US_Ledger_amount + A.CA_Ledger_amount) != 0

Monday, 9 December 2024

Query for Absence and Overlapping employees of Absence

WITH Main_Q as

(SELECT distinct * FROM(

SELECT papf.person_id,

papf.person_number,

ppnf.display_name

employee_name,

pasf_Sup.manager_name

manager_name,

pjft.name

job_name,

pgft.name

grade_name,

--abt.absence_type_id,

abt.name

absence_type,

paam.ASS_ATTRIBUTE1 sector,

paam.ASS_ATTRIBUTE2 unit,

(SELECT DISTINCT( pd.name )

FROM per_departments pd

WHERE pd.organization_id = paam.organization_id

AND pd.effective_start_date =

(SELECT Max(pd1.effective_start_date)

FROM per_departments pd1

WHERE

pd1.organization_id = pd.organization_id

))

department_name,

--To_char(apae.start_date, 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = AMERICAN')

apae.start_date start_date,

-- To_char(apae.end_date, 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = AMERICAN')

apae.end_date end_date,

apae.duration,

apae.start_date order_by_date,

apae.attribute1 Delegated_Person

FROM anc_per_abs_entries apae,

per_periods_of_service pps,

anc_absence_types_vl abt,

per_all_people_f papf,

per_person_names_f ppnf,

(SELECT ppnf2.full_name manager_name,

pasf.person_id

FROM per_assignment_supervisors_f pasf,

per_person_names_f ppnf2

WHERE pasf.manager_id = ppnf2.person_id

AND pasf.manager_type = 'LINE_MANAGER'

AND Trunc(SYSDATE) BETWEEN pasf.effective_start_date AND

pasf.effective_end_date

AND Trunc(SYSDATE) BETWEEN ppnf2.effective_start_date AND

ppnf2.effective_end_date

AND ppnf2.name_type = 'GLOBAL') pasf_Sup,

per_all_assignments_m paam,

per_jobs_f_vl pjft,

per_grades_f_vl pgft,

hr_organization_units bu,

hr_organization_units le,

per_asg_responsibilities resp

--hr_organization_units dept

WHERE apae.period_of_service_id = pps.period_of_service_id

AND apae.absence_type_id = abt.absence_type_id

AND apae.ABSENCE_STATUS_CD <> 'ORA_WITHDRAWN'

AND apae.APPROVAL_STATUS_CD = 'APPROVED'

AND abt.name = 'Tentative Leave Plan'

AND pps.person_id = papf.person_id

AND papf.person_id = ppnf.person_id

AND papf.person_id = pasf_Sup.person_id(+)

AND papf.person_id = paam.person_id(+)

AND paam.primary_assignment_flag(+) = 'Y'

--AND paam.assignment_type = 'E'

--AND paam.effective_latest_change = 'Y'

AND paam.job_id = pjft.job_id (+)

AND paam.grade_id = pgft.grade_id (+)

AND paam.business_unit_id = bu.organization_id

AND paam.legal_entity_id = le.organization_id

AND resp.person_id = HRC_SESSION_UTIL.get_user_personid

AND resp.responsibility_type ='HR_REP'

AND resp.status = 'Active'

AND resp.legal_entity_id = paam.legal_entity_id

--AND resp.BUSINESS_UNIT_ID=paam.BUSINESS_UNIT_ID

--AND resp.organization_id =paam.organization_id

AND TRUNC(sysdate) BETWEEN resp.start_date AND NVL(resp.end_date,TRUNC(sysdate))

AND Trunc(SYSDATE) BETWEEN pjft.effective_start_date(+) AND

pjft.effective_end_date(+)

AND Trunc(SYSDATE) BETWEEN pgft.effective_start_date(+) AND

pgft.effective_end_date(+)

AND Trunc(SYSDATE) BETWEEN paam.effective_start_date AND

paam.effective_end_date

AND Trunc(SYSDATE) BETWEEN abt.effective_start_date AND

abt.effective_end_date

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 ppnf.name_type = 'GLOBAL'

--AND papf.person_number IN ( '0015', '0033' )

AND to_number(Extract(year FROM apae.start_date)) = to_number(:p_date)

UNION

SELECT papf.person_id,

papf.person_number,

ppnf.display_name

employee_name,

pasf_Sup.manager_name

manager_name,

pjft.name

job_name,

pgft.name

grade_name,

--abt.absence_type_id,

abt.name

absence_type,

paam.ass_attribute2 sector,

paam.ass_attribute1 unit,

(SELECT DISTINCT( pd.name )

FROM per_departments pd

WHERE pd.organization_id = paam.organization_id

AND pd.effective_start_date =

(SELECT Max(pd1.effective_start_date)

FROM per_departments pd1

WHERE

pd1.organization_id = pd.organization_id))

department_name,

-- To_char(apae.start_date, 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = AMERICAN')

apae.start_date start_date,

-- To_char(apae.end_date, 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = AMERICAN')

apae.end_date end_date,

apae.duration,

apae.start_date order_by_date,

apae.attribute1 Delegated_Person

FROM anc_per_abs_entries apae,

per_periods_of_service pps,

anc_absence_types_vl abt,

per_all_people_f papf,

per_person_names_f ppnf,

(SELECT ppnf2.full_name manager_name,

pasf.person_id

FROM per_assignment_supervisors_f pasf,

per_person_names_f ppnf2

WHERE pasf.manager_id = ppnf2.person_id

AND pasf.manager_type = 'LINE_MANAGER'

AND Trunc(SYSDATE) BETWEEN pasf.effective_start_date AND

pasf.effective_end_date

AND Trunc(SYSDATE) BETWEEN ppnf2.effective_start_date AND

ppnf2.effective_end_date

AND ppnf2.name_type = 'GLOBAL') pasf_Sup,

per_all_assignments_m paam,

per_jobs_f_vl pjft,

per_grades_f_vl pgft,

hr_organization_units bu,

hr_organization_units le,

per_asg_responsibilities resp

--hr_organization_units dept

WHERE apae.period_of_service_id = pps.period_of_service_id

AND apae.absence_type_id = abt.absence_type_id

AND apae.ABSENCE_STATUS_CD <> 'ORA_WITHDRAWN'

AND apae.APPROVAL_STATUS_CD = 'APPROVED'

AND abt.name = 'Tentative Leave Plan'

AND pps.person_id = papf.person_id

AND papf.person_id = ppnf.person_id

AND papf.person_id = pasf_Sup.person_id(+)

AND papf.person_id = paam.person_id(+)

AND paam.primary_assignment_flag(+) = 'Y'

--AND paam.assignment_type = 'E'

--AND paam.effective_latest_change = 'Y'

AND paam.job_id = pjft.job_id (+)

AND paam.grade_id = pgft.grade_id (+)

AND paam.business_unit_id = bu.organization_id

AND paam.legal_entity_id = le.organization_id

AND resp.person_id = HRC_SESSION_UTIL.get_user_personid

AND resp.responsibility_type IN ('Sector Head', 'Department Head')

AND resp.status = 'Active'

--AND resp.legal_entity_id = paam.legal_entity_id

--AND resp.BUSINESS_UNIT_ID=paam.BUSINESS_UNIT_ID

AND resp.organization_id =paam.organization_id

AND TRUNC(sysdate) BETWEEN resp.start_date AND NVL(resp.end_date,TRUNC(sysdate))

AND Trunc(SYSDATE) BETWEEN pjft.effective_start_date(+) AND

pjft.effective_end_date(+)

AND Trunc(SYSDATE) BETWEEN pgft.effective_start_date(+) AND

pgft.effective_end_date(+)

AND Trunc(SYSDATE) BETWEEN paam.effective_start_date AND

paam.effective_end_date

AND Trunc(SYSDATE) BETWEEN abt.effective_start_date AND

abt.effective_end_date

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 ppnf.name_type = 'GLOBAL'

--AND papf.person_number IN ( '0015', '0033' )

AND to_number(Extract(year FROM apae.start_date)) = to_number(:p_date)

)

WHERE 1=1

AND (LEAST(:p_sector) IS NULL

OR trim(sector) IN trim( :p_sector ))

--AND ( (coalesce(null, :p_sector) is null) or (trim(sector) in trim(:p_sector)))

AND (LEAST(:p_dept) IS NULL

OR department_name IN ( :p_dept ))

--ORDER BY department_name,order_by_date

)

SELECT mq.person_id,

mq.person_number,

mq.employee_name,

mq.manager_name,

mq.job_name,

mq.grade_name,

--abt.absence_type_id,

mq.absence_type,

mq.sector,

mq.unit,

mq.department_name,

To_char(mq.start_date, 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = AMERICAN')

start_date,

To_char(mq.end_date, 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = AMERICAN')

end_date,

mq.duration,

mq.order_by_date,

mq.Delegated_Person,

(SELECT LISTAGG(mq1.person_number, ', ') WITHIN GROUP (ORDER BY mq1.person_number)

FROM Main_Q mq1

WHERE mq.person_id!=mq1.person_id

AND (TRUNC(mq.start_date) BETWEEN TRUNC(mq1.start_date) AND TRUNC(mq1.end_date)

OR TRUNC(mq1.start_date) BETWEEN TRUNC(mq.start_date) AND TRUNC(mq.end_date)

)

) overlap_emp

from Main_Q mq

Monday, 2 December 2024

Generate Empty File in BI Publisher Bursting sql returns no data

Currently there is no supported method for that. The workaround is to make sure that your sql will always return at least one record.

You could accomplish this by adding a union to your sql that will always return one row if the main sql returns no data.

We have provided a sample select that you may use as guidance:

We have modified the Data Model main sql in such a way that it will always return a record when the main sql returns no data. This will prevent the report job from failing and, at the same time, the bursting will not send out the email out as the party_site_id is equals to -999, which does not meet the bursting sql criteria.

SELECT s.vendor_id,

s.vendor_name,

ps.party_site_name ,

ps.party_site_id

FROM fusion.poz_suppliers_v s,

fusion.hz_party_sites ps

WHERE s.party_id = ps.party_id

UNION

SELECT 1,

'2' ,

'3' ,

-99999

FROM dual

WHERE NOT EXISTS

(SELECT 1

FROM fusion.poz_suppliers_v s,

fusion.hz_party_sites ps

WHERE s.party_id = ps.party_id

);

Reference link : Doc ID 1925999.1

Sunday, 24 November 2024

Query For Payroll Run Balances In Oracle Fusion

SELECT /*+ MATERIALIZE */ main.person_id

,main.Employer_Contribution

,main.Member_contribution

,main.org_information6 as Nest_Employer_Reference_Number

,main.NATIONAL_IDENTIFIER_NUMBER as unique_record_identifier

,'Norfolk County Council' as Payment_source

,'Monthly' Frequency

,'0.00' as Pensionable_Earnings

, to_char(last_day(add_months(sysdate,-1)),'MM-DD-YYYY') as Earnings_period_end_date

FROM(

SELECT /*+ inline */

Bal_val.person_id,

SUM (CASE WHEN Balances.balance_name IN (xxxxxxxxx) Employer_Contribution,

SUM(CASE WHEN Balances.balance_name IN (xxxxxxxxxx) Member_contribution,

TRU.org_information6,

-- Bal_val.period_name,

-- Legal_Emp.name,

-- Bal_val.payroll_name,

-- flow.flow_name,

Bal_val.NATIONAL_IDENTIFIER_NUMBER

FROM

(

SELECT /*+ MATERIALIZE */

prb.balance_value,

prb.defined_balance_id,

prb.tax_unit_id,

ppa.effective_date,

ppa.earn_time_period_id,

Nvl(prg.assignment_id, ppaf.assignment_id) assignment_id,

pldg.legislation_code,

ppa.PAY_REQUEST_ID,

ppaf.legal_entity_id,

ppaf.person_id,

ptp.period_name,

papf.payroll_name,

pni.NATIONAL_IDENTIFIER_NUMBER

FROM

pay_run_balances prb,

pay_pay_relationships_dn pprd,

pay_payroll_rel_actions ppra,

pay_payroll_actions ppa,

per_all_assignments_f ppaf,

pay_rel_groups_dn PRG,

per_legislative_data_groups_vl PLDG,

pay_all_payrolls_f papf,

pay_time_periods ptp,

per_national_identifiers pni

WHERE

prb.payroll_rel_action_id = ppra.payroll_rel_action_id

AND ppra.payroll_action_id = ppa.payroll_action_id

AND prb.payroll_relationship_id = pprd.payroll_relationship_id (+)

AND pprd.person_id = ppaf.person_id

AND prb.effective_date = ppa.effective_date

AND ppa.action_type IN ('R', 'B', 'V', 'Q', 'I')

AND ppa.effective_date BETWEEN ppaf.effective_start_date

AND ppaf.effective_end_date

AND ppaf.primary_flag = 'Y'

AND pprd.legislative_data_group_id = pldg.legislative_data_group_id

AND ppaf.work_terms_assignment_id IS NOT NULL

AND ppaf.effective_latest_change = 'Y'

AND ppaf.assignment_type in ('E','C','N','P')

AND prg.relationship_group_id (+)= prb.payroll_assignment_id

AND ppra.retro_component_id IS NULL

AND ppa.payroll_id = papf.payroll_id(+)

--AND ppaf.person_id=300000004491016 ---200193

--AND ppaf.person_id=100000001628019 -----1915

AND prb.effective_date BETWEEN papf.effective_start_date(+) AND papf.effective_end_date(+)

AND ppa.earn_time_period_id = ptp.time_period_id

AND prb.effective_date BETWEEN ppaf.effective_start_date AND ppaf.effective_end_date

AND ppaf.person_id = pni.person_id

AND ((COALESCE(NULL, :p_payroll_name) IS NULL) OR (papf.payroll_name IN (:p_payroll_name)))

AND ptp.period_name = nvl(:p_period_name,ptp.period_name)

) Bal_val,

(

SELECT /*+ MATERIALIZE */

pduv.dimension_usage_id,

pduv.dimension_name,

pduv.legislation_code,

pdb.defined_balance_id

FROM

pay_dimension_usages_vl pduv,

pay_defined_balances pdb

WHERE

pduv.balance_dimension_id = pdb.balance_dimension_id

and pduv.dimension_name = 'Payroll Relationship, Run'

)Dimension,

(

SELECT /*+ MATERIALIZE */

houcf.org_unit_classification_id,

houcf.effective_start_date,

houcf.effective_end_date,

haouf.organization_id AS unit_eo_organization_id,

haouf.effective_start_date AS unit_eo_effective_start_date,

haouf.effective_end_date AS unit_eo_effective_end_date,

houft.organization_id AS unit_tleo_organization_id,

houft.effective_start_date AS unit_tleo_effective_start_date,

houft.effective_end_date AS unit_tleo_effective_end_date,

houft.language,

houft.NAME,

HOIF.ORG_INFORMATION6--nest ids

FROM

hr_all_organization_units_f haouf,

hr_org_unit_classifications_f houcf,

hr_organization_units_f_tl houft,

hr_organization_information_f HOIF

WHERE haouf.organization_id = houcf.organization_id(+)

AND haouf.organization_id = houft.organization_id(+)

AND haouf.effective_start_date = houft.effective_start_date(+)

AND haouf.effective_end_date = houft.effective_end_date(+)

AND userenv('LANG')= houft.language(+)

AND sysdate BETWEEN haouf.effective_start_date AND haouf.effective_end_date

AND sysdate BETWEEN houcf.effective_start_date(+)AND houcf.effective_end_date(+)

AND sysdate BETWEEN houft.effective_start_date(+) AND houft.effective_end_date(+)

AND houcf.classification_code = 'HCM_TRU'

AND hoif.org_information_context LIKE 'LRU Additional Details'

AND hoif.organization_id = houft.organization_id

) TRU,

(

SELECT /*+ MATERIALIZE */

houcf.org_unit_classification_id,

houcf.effective_start_date,

houcf.effective_end_date,

haouf.organization_id AS unit_eo_organization_id,

haouf.effective_start_date AS unit_eo_effective_start_date,

haouf.effective_end_date AS unit_eo_effective_end_date,

houft.organization_id AS unit_tleo_organization_id,

houft.effective_start_date AS unit_tleo_effective_start_date,

houft.effective_end_date AS unit_tleo_effective_end_date,

houft.language,

houft.NAME

FROM

hr_all_organization_units_f haouf,

hr_org_unit_classifications_f houcf,

hr_organization_units_f_tl houft

WHERE

haouf.organization_id = houcf.organization_id(+)

AND haouf.organization_id = houft.organization_id(+)

AND haouf.effective_start_date = houft.effective_start_date(+)

AND haouf.effective_end_date = houft.effective_end_date(+)

AND userenv('LANG') = houft.language(+)

AND sysdate BETWEEN haouf.effective_start_date AND haouf.effective_end_date

AND sysdate BETWEEN houcf.effective_start_date(+)

AND houcf.effective_end_date(+)

AND sysdate BETWEEN houft.effective_start_date(+) AND houft.effective_end_date(+)

AND houcf.classification_code = 'HCM_LEMP'

AND ((COALESCE(NULL, :P_Legal_Entity) IS NULL) OR (houft.NAME IN (:P_Legal_Entity)))

) Legal_Emp,

(

SELECT /*+ MATERIALIZE */

pdb.balance_type_id,

pdb.defined_balance_id,

pbtv.balance_name

FROM

pay_defined_balances pdb,

pay_balance_types_vl pbtv

WHERE pbtv.balance_name in (xxxxxxx)

AND pdb.balance_type_id = pbtv.balance_type_id

) Balances,

(

SELECT /*+ MATERIALIZE */

pr.flow_task_instance_id,

pr.pay_request_id,

pfti.flow_instance_id,

pfti.flow_task_instance_id,

pft.flow_name

FROM

pay_requests pr,

pay_flow_task_instances pfti,

pay_flow_instances pfi,

pay_flows pf,

pay_flows_tl pft

WHERE pf.base_flow_id = pfi.base_flow_id (+)

AND pf.legislative_data_group_id = pfi.legislative_data_group_id

AND pft.flow_id = pf.flow_id

AND pft.language = userenv('LANG')

AND pfti.flow_instance_id = pfi.flow_instance_id(+)

AND pr.flow_task_instance_id = pfti.flow_task_instance_id(+)

AND ((COALESCE(NULL, :p_flow) IS NULL) OR (pfi.INSTANCE_NAME IN (:p_flow)))

) Flow

WHERE Bal_val.defined_balance_id = Dimension.defined_balance_id

AND Bal_val.legislation_code = Dimension.legislation_code

AND Bal_val.tax_unit_id = TRU.unit_eo_organization_id(+)

AND Bal_val.legal_entity_id = Legal_Emp.unit_eo_organization_id(+)

AND Bal_val.defined_balance_id = Balances.defined_balance_id

AND Bal_val.pay_request_id = Flow.pay_request_id(+)

AND Bal_val.effective_date BETWEEN TRU.effective_start_date(+) AND TRU.effective_end_date(+)

GROUP BY Bal_val.person_id,

TRU.ORG_INFORMATION6,

Bal_val.NATIONAL_IDENTIFIER_NUMBER

-- Bal_val.period_name,

-- Legal_Emp.name,

-- Bal_val.payroll_name,

-- flow.flow_name

) main

Friday, 8 November 2024

Query for Oracle Fusion Payroll Balances

WITH Employer_con AS

(

SELECT /*+ MATERIALIZE */

SUM(bal.balance_value) as amount,

pprd.person_id,

ppa.date_earned,

ptp.end_date,

ppa.payroll_action_id

FROM

per_legislative_data_groups_vl ldg,

pay_pay_relationships_dn pprd,

pay_payroll_rel_actions pra,

pay_payroll_actions ppa,

pay_balance_types_vl pbt,

TABLE ( pay_balance_view_pkg.get_balance_dimensions(p_balance_type_id => pbt.balance_type_id,p_payroll_rel_action_id => pra.payroll_rel_action_id

,p_payroll_term_id => NULL,p_payroll_assignment_id => NULL) ) bal,

pay_dimension_usages_vl pdu,

pay_time_periods ptp,

pay_requests req,

pay_flow_instances flow,

pay_all_payrolls_f paypf

WHERE

pprd.legislative_data_group_id = ldg.legislative_data_group_id

AND pra.payroll_relationship_id = pprd.payroll_relationship_id

AND EXISTS (

SELECT

FROM

pay_run_results prr

WHERE

prr.payroll_rel_action_id = pra.payroll_rel_action_id

)

AND ppa.payroll_action_id = pra.payroll_action_id

AND nvl(pbt.legislation_code,ldg.legislation_code) = ldg.legislation_code

AND nvl(pbt.legislative_data_group_id,ldg.legislative_data_group_id) = ldg.legislative_data_group_id

AND pdu.balance_dimension_id = bal.balance_dimension_id

AND pbt.balance_name IN ('xxxxxxxxxx')

AND pdu.dimension_name IN ('Payroll Relationship, Run')

AND nvl(pdu.legislation_code,ldg.legislation_code) = ldg.legislation_code

AND nvl(pdu.legislative_data_group_id,ldg.legislative_data_group_id) = ldg.legislative_data_group_id

AND trunc(ppa.date_earned) BETWEEN trunc(nvl(pprd.start_date(+),ppa.date_earned) ) AND trunc(nvl(pprd.end_date(+),ppa.date_earned) )

AND ptp.period_category = 'E'

AND ppa.earn_time_period_id=ptp.time_period_id

AND req.pay_request_id = ppa.pay_request_id

AND flow.flow_instance_id = req.flow_instance_id

and paypf.payroll_id = ptp.payroll_id

AND bal.balance_value <> 0

AND trunc(sysdate) BETWEEN paypf.effective_start_date AND paypf.effective_end_date

--AND ppa.payroll_id = :p_payroll_name

AND ((COALESCE(NULL, :p_payroll_name) IS NULL) OR (paypf.payroll_name IN (:p_payroll_name)))

AND ptp.period_name = nvl(:p_period_name,ptp.period_name)

--AND ppa.payroll_action_id in (:p_flow)

AND ((COALESCE(NULL, :p_flow) IS NULL) OR (flow.INSTANCE_NAME IN (:p_flow)))

GROUP BY

pprd.person_id,ppa.date_earned, ptp.end_date,ppa.payroll_action_id

MEMBER_CON AS

(

SELECT /*+ MATERIALIZE */

SUM(bal.balance_value) as amount,

pprd.person_id,

ppa.date_earned,

ptp.end_date,

ppa.payroll_action_id

FROM

per_legislative_data_groups_vl ldg,

pay_pay_relationships_dn pprd,

pay_payroll_rel_actions pra,

pay_payroll_actions ppa,

pay_balance_types_vl pbt,

TABLE ( pay_balance_view_pkg.get_balance_dimensions(p_balance_type_id => pbt.balance_type_id,p_payroll_rel_action_id => pra.payroll_rel_action_id

,p_payroll_term_id => NULL,p_payroll_assignment_id => NULL) ) bal,

pay_dimension_usages_vl pdu,

pay_time_periods ptp,

pay_requests req,

pay_flow_instances flow,

pay_all_payrolls_f paypf

WHERE

pprd.legislative_data_group_id = ldg.legislative_data_group_id

AND pra.payroll_relationship_id = pprd.payroll_relationship_id

AND EXISTS (

SELECT

FROM

pay_run_results prr

WHERE

prr.payroll_rel_action_id = pra.payroll_rel_action_id

)

AND ppa.payroll_action_id = pra.payroll_action_id

AND nvl(pbt.legislation_code,ldg.legislation_code) = ldg.legislation_code

AND nvl(pbt.legislative_data_group_id,ldg.legislative_data_group_id) = ldg.legislative_data_group_id

AND pdu.balance_dimension_id = bal.balance_dimension_id

AND pbt.balance_name IN ('xxxxxxxxxx')

AND pdu.dimension_name IN ('xxxxxxxxxxxxxx')

AND nvl(pdu.legislation_code,ldg.legislation_code) = ldg.legislation_code

AND nvl(pdu.legislative_data_group_id,ldg.legislative_data_group_id) = ldg.legislative_data_group_id

AND trunc(ppa.date_earned) BETWEEN trunc(nvl(pprd.start_date(+),ppa.date_earned) ) AND trunc(nvl(pprd.end_date(+),ppa.date_earned) )

AND ptp.period_category = 'E'

AND ppa.earn_time_period_id=ptp.time_period_id

AND req.pay_request_id = ppa.pay_request_id

AND flow.flow_instance_id = req.flow_instance_id

and paypf.payroll_id = ptp.payroll_id

AND bal.balance_value <> 0

AND trunc(sysdate) BETWEEN paypf.effective_start_date AND paypf.effective_end_date

--AND ppa.payroll_id = :p_payroll_name

AND ((COALESCE(NULL, :p_payroll_name) IS NULL) OR (paypf.payroll_name IN (:p_payroll_name)))

AND ptp.period_name = nvl(:p_period_name,ptp.period_name)

--AND ppa.payroll_action_id in (:p_flow)

AND ((COALESCE(NULL, :p_flow) IS NULL) OR (flow.INSTANCE_NAME IN (:p_flow)))

-- and paypf.payroll_name = 'DPSS PFS Payroll Monthly'

-- and ptp.period_name = '12 2023 Calendar Month'

-- and flow.INSTANCE_NAME = 'Calculate Payroll - 8th March 2024 - PFS'

--and pprd.person_id = 100000001631372

GROUP BY

pprd.person_id,ppa.date_earned, ptp.end_date,ppa.payroll_action_id

TRU_DETAILS as

(

SELECT /*+ MATERIALIZE */ DISTINCT

houft.NAME

,HOIF.ORG_INFORMATION_CONTEXT

,HOIF.ORG_INFORMATION1

,HOIF.ORG_INFORMATION2

,HOIF.ORG_INFORMATION3

,HOIF.ORG_INFORMATION_NUMBER1

,HOIF.ORG_INFORMATION4

,HOIF.ORG_INFORMATION6--nest ids

,HOIF.ORG_INFORMATION5

,pprd.person_id

FROM pay_run_balances prb,

pay_pay_relationships_dn pprd,

pay_payroll_rel_actions ppra,

pay_payroll_actions ppa,

pay_rel_groups_dn PRG,

per_legislative_data_groups_vl PLDG,

hr_all_organization_units_f haouf,

hr_org_unit_classifications_f houcf,

hr_organization_units_f_tl houft,

hr_organization_information_f HOIF

WHERE prb.payroll_rel_action_id=ppra.payroll_rel_action_id

AND ppra.payroll_action_id=ppa.payroll_action_id

AND prb.payroll_relationship_id= pprd.payroll_relationship_id (+)

AND prb.effective_date = ppa.effective_date

AND ppa.action_type IN ('R', 'B', 'V', 'Q', 'I')

AND pprd.legislative_data_group_id=pldg.legislative_data_group_id

AND prg.relationship_group_id (+)=prb.payroll_assignment_id

AND ppra.retro_component_id IS NULL

AND userenv('LANG') = houft.language(+)

AND haouf.organization_id = houcf.organization_id(+)

AND haouf.organization_id = houft.organization_id(+)

AND haouf.effective_start_date = houft.effective_start_date(+)

AND haouf.effective_end_date = houft.effective_end_date(+)

AND sysdate BETWEEN haouf.effective_start_date(+) AND haouf.effective_end_date(+)

AND sysdate BETWEEN houcf.effective_start_date(+)

AND houcf.effective_end_date(+)

AND sysdate BETWEEN houft.effective_start_date(+)

AND houft.effective_end_date(+)

AND houcf.classification_code(+) = 'HCM_TRU'

AND prb.tax_unit_id = haouf.organization_id (+)

AND ppa.effective_date >= houcf.effective_start_date(+)

AND ppa.effective_date <= houcf.effective_end_date(+)

AND hoif.org_information_context LIKE 'LRU Additional Details'

AND hoif.organization_id(+) = houft.organization_id

and HOIF.ORG_INFORMATION6 is not null

)

SELECT /*+ MATERIALIZE */

papf.person_number

,TD.ORG_INFORMATION6 as Nest_Employer_Reference_Number

,'Monthly' Frequency

, to_char(last_day(add_months(sysdate,-1)),'MM-DD-YYYY') as Earnings_period_end_date

,pni.NATIONAL_IDENTIFIER_NUMBER as unique_record_identifier

,0.00 as Pensionable_Earnings

,ec.amount Employer_Contribution

,mc.amount Member_contribution

FROM

per_all_people_f papf,

per_all_assignments_m paaf,

employer_con ec,

member_con mc,

tru_details td,

per_national_identifiers pni,

per_legal_employers ple

WHERE paaf.person_id = papf.person_id

AND papf.person_id = ec.person_id

AND papf.person_id = mc.person_id

AND paaf.primary_flag = 'Y'

AND paaf.effective_latest_change = 'Y'

AND PAAf.assignment_type IN('E','C')

AND td.person_id = paaf.person_id

AND papf.person_id = pni.person_id

AND ple.organization_id = paaf.legal_entity_id

AND sysdate between ple.EFFECTIVE_START_DATE and ple.EFFECTIVE_end_DATE

AND ((COALESCE(NULL, :P_Legal_Entity) IS NULL) OR (ple.name IN (:P_Legal_Entity)))

AND trunc(nvl(ec.date_earned,SYSDATE)) BETWEEN trunc(nvl(papf.effective_start_date,SYSDATE) ) AND trunc(nvl(papf.effective_end_date,SYSDATE) )

AND trunc(nvl(ec.date_earned,SYSDATE)) BETWEEN trunc(nvl(paaf.effective_start_date,SYSDATE) ) AND trunc(nvl(paaf.effective_end_date,SYSDATE) )

AND trunc(nvl(mc.date_earned,SYSDATE)) BETWEEN trunc(nvl(papf.effective_start_date,SYSDATE) ) AND trunc(nvl(papf.effective_end_date,SYSDATE) )

AND trunc(nvl(mc.date_earned,SYSDATE)) BETWEEN trunc(nvl(paaf.effective_start_date,SYSDATE) ) AND trunc(nvl(paaf.effective_end_date,SYSDATE) )

Rest Api Testing for Oracle Time and Labor (OTL)

Oracle Time and Labor Rest Api:

Rest Api: hcmRestApi/resources/11.13.18.05/timeRecordEventRequests

Method: Post

Payload:

{

"processMode":"TIME_SAVE",

"processInline":"N",

"timeRecordEvent":

[

{

"timeRecordId":"",

"timeRecordVersion":"",

"startTime":"2017-10-13T10:00:00.000-08:00",

"stopTime":"2017-10-13T14:00:00.000-08:00",

"reporterIdType":"PERSON",

"reporterId":"xxxxx", ----Insert Person Number

"assignmentNumber":"",

"comment":"Time Entry Update Measure",

"operationType":"ADD",

"changeReason":"ORA_CA_REASON_MTE_PROC",

"measure":"",

"timeRecordEventAttribute":

[

{

"attributeName":"PayrollTimeType",

"changeReason":"ORA_CA_REASON_MTE_PROC",

"attributeValue":"ZOTL_Regular"

}

]

}

]

}

Postman URL:

https://<<Instance URL>>/hcmRestApi/resources/11.13.18.05/timeRecordEventRequests

Response from Postman:

{

"timeRecordEventRequestId": 300000035161120,

"processMode": "TIME_SAVE",

"processInline": "N",

"timeRecordEvent": [

{

"comment": "Time Entry Update Measure",

"crudStatusValue": 0,

"personId": "XXXXXX", --Person_id

"referenceDate": null,

"reporterId": "XXXXX", ---Person_number

"reporterIdType": "PERSON",

"startTime": "2017-10-13T10:00:00.000-08:00",

"stopTime": "2017-10-13T14:00:00.000-08:00",

"subresourceId": null,

"timeRecordEventId": 300000035161121,

"timeRecordEventRequestId": 300000035161120,

"timeRecordId": null,

"timeRecordVersion": null,

"operationType": "ADD",

"assignmentNumber": "",

"eventStatusValue": 0,

"eventStatus": "NEW",

"measure": null,

"changeReason": "ORA_CA_REASON_MTE_PROC",

"timeRecordEventAttribute": [

{

"timeAttributeFieldId": 300000000427517,

"timeRecordEventAttributeId": 300000035161122,

"timeRecordEventId": 300000035161121,

"attributeValue": "ZOTL_Regular",

"changeReason": "ORA_CA_REASON_MTE_PROC",

"attributeName": "PayrollTimeType",

"links": [

{

"rel": "self",

"href": "https:<<Instance URL>>:443/hcmRestApi/resources/11.13.18.05/timeRecordEventRequests/300000035161120/child/timeRecordEvent/300000035161121/child/timeRecordEventAttribute/300000035161122",

"name": "timeRecordEventAttribute",

"kind": "item",

"properties": {

"changeIndicator": "ACED0005737200136A6176612E7574696C2E41727261794C6973747881D21D99C7619D03000149000473697A65787000000001770400000001737200116A6176612E6C616E672E496E746567657212E2A0A4F781873802000149000576616C7565787200106A6176612E6C616E672E4E756D62657286AC951D0B94E08B02000078700000000178"

}

{

"rel": "canonical",

"href": "<<Instance URL >>":443/hcmRestApi/resources/11.13.18.05/timeRecordEventRequests/300000035161120/child/timeRecordEvent/300000035161121/child/timeRecordEventAttribute/300000035161122",

"name": "timeRecordEventAttribute",

"kind": "item"

{

"rel": "parent",

"href": "<<Instance URL>>":443/hcmRestApi/resources/11.13.18.05/timeRecordEventRequests/300000035161120/child/timeRecordEvent/300000035161121",

"name": "timeRecordEvent",

"kind": "item"

{

"rel": "lov",

"href": "<<Instance URL>>:443/hcmRestApi/resources/11.13.18.05/timeRecordEventRequests/300000035161120/child/timeRecordEvent/300000035161121/child/timeRecordEventAttribute/300000035161122/lov/HcmLookupPVO1",

"name": "HcmLookupPVO1",

"kind": "collection"

}

]

}

"links": [

{

"rel": "self",

"href": "<<Instance URL>>:443/hcmRestApi/resources/11.13.18.05/timeRecordEventRequests/300000035161120/child/timeRecordEvent/300000035161121",

"name": "timeRecordEvent",

"kind": "item",

"properties": {

}

{

"rel": "canonical",

"href": "<<Instance URL>>:443/hcmRestApi/resources/11.13.18.05/timeRecordEventRequests/300000035161120/child/timeRecordEvent/300000035161121",

"name": "timeRecordEvent",

"kind": "item"

{

"rel": "parent",

"href": "<<Instance URL>>:443/hcmRestApi/resources/11.13.18.05/timeRecordEventRequests/300000035161120",

"name": "timeRecordEventRequests",

"kind": "item"

{

"rel": "lov",

"href": "<<Instance URL>>:443/hcmRestApi/resources/11.13.18.05/timeRecordEventRequests/300000035161120/child/timeRecordEvent/300000035161121/lov/HcmLookupPVO1",

"name": "HcmLookupPVO1",

"kind": "collection"

{

"rel": "child",

"href": "<<Instance URL>>:443/hcmRestApi/resources/11.13.18.05/timeRecordEventRequests/300000035161120/child/timeRecordEvent/300000035161121/child/timeRecordEventAttribute",

"name": "timeRecordEventAttribute",

"kind": "collection"

{

"rel": "child",

"href": "<<Instance URL>>:443/hcmRestApi/resources/11.13.18.05/timeRecordEventRequests/300000035161120/child/timeRecordEvent/300000035161121/child/timeRecordEventMessage",

"name": "timeRecordEventMessage",

"kind": "collection"

}

]

}

"links": [

{

"rel": "self",

"href": "<<Instance URL>>:443/hcmRestApi/resources/11.13.18.05/timeRecordEventRequests/300000035161120",

"name": "timeRecordEventRequests",

"kind": "item",

"properties": {

}

{

"rel": "canonical",

"href": "<<Instance URL>>:443/hcmRestApi/resources/11.13.18.05/timeRecordEventRequests/300000035161120",

"name": "timeRecordEventRequests",

"kind": "item"

{

"rel": "child",

"href": "<<Instance URL>>:443/hcmRestApi/resources/11.13.18.05/timeRecordEventRequests/300000035161120/child/timeRecordEvent",

"name": "timeRecordEvent",

"kind": "collection"

}

]

}

Time Record Entry created In fusion:

Screenshot from Oracle fusion:

Multiple Records 1 :

{

"processMode":"TIME_SAVE",

"processInline":"N",

"timeRecordEvent":

[

{

"timeRecordId":"",

"timeRecordVersion":"",

"startTime":"2018-10-13T10:00:00.000-08:00",

"stopTime":"2018-10-13T14:00:00.000-08:00",

"reporterIdType":"PERSON",

"reporterId":"xxxxx",---person_number

"assignmentNumber":"",

"comment":"Time Entry Update Measure",

"operationType":"ADD",

"changeReason":"ORA_CA_REASON_MTE_PROC",

"measure":"",

"timeRecordEventAttribute":

[

{

"attributeName":"PayrollTimeType",

"changeReason":"ORA_CA_REASON_MTE_PROC",

"attributeValue":"ZOTL_Regular"

}

]

}

]

}

2^nd Record:

{

"processMode":"TIME_SAVE",

"processInline":"N",

"timeRecordEvent":

[

{

"timeRecordId":"",

"timeRecordVersion":"",

"startTime":"2018-10-13T16:00:00.000-08:00",

"stopTime":"2018-10-13T17:00:00.000-08:00",

"reporterIdType":"PERSON",

"reporterId":"xxxxxx",---person_number

"assignmentNumber":"",

"comment":"Time Entry Update Measure",

"operationType":"ADD",

"changeReason":"ORA_CA_REASON_MTE_PROC",

"measure":"",

"timeRecordEventAttribute":

[

{

"attributeName":"PayrollTimeType",

"changeReason":"ORA_CA_REASON_MTE_PROC",

"attributeValue":"ZOTL_Regular"

}

]

}

]

}

3^rd Record:

{

"processMode":"TIME_SAVE",

"processInline":"N",

"timeRecordEvent":

[

{

"timeRecordId":"",

"timeRecordVersion":"",

"startTime":"2018-10-13T17:30:00.000-08:00",

"stopTime":"2018-10-13T17:45:00.000-08:00",

"reporterIdType":"PERSON",

"reporterId":"person_number",

"assignmentNumber":"",

"comment":"Time Entry Update Measure",

"operationType":"ADD",

"changeReason":"ORA_CA_REASON_MTE_PROC",

"measure":"",

"timeRecordEventAttribute":

[

{

"attributeName":"PayrollTimeType",

"changeReason":"ORA_CA_REASON_MTE_PROC",

"attributeValue":"ZOTL_Regular"

}

]

}

]

}

4^th Record :

{

"processMode":"TIME_SAVE",

"processInline":"N",

"timeRecordEvent":

[

{

"timeRecordId":"",

"timeRecordVersion":"",

"startTime":"2018-10-13T17:46:00.000-08:00",

"stopTime":"2018-10-13T17:59:00.000-08:00",

"reporterIdType":"PERSON",

"reporterId":"xxxxx", ---person_number

"assignmentNumber":"",

"comment":"Time Entry Update Measure",

"operationType":"ADD",

"changeReason":"ORA_CA_REASON_MTE_PROC",

"measure":"",

"timeRecordEventAttribute":

[

{

"attributeName":"PayrollTimeType",

"changeReason":"ORA_CA_REASON_MTE_PROC",

"attributeValue":"ZOTL_Regular"

}

]

}

]

}

Database Records:

Table : HWM_TM_REC_EVENTS

ESS : Process Imported Time Entries

Team Time Entries: