Oracle Database and Oracle Applications

Model Profile Query in Fusion HCM

noreply@blogger.com (Sandip Jadhav) — Mon, 16 Aug 2021 12:13:00 +0000

select hapf.position_code

,hpb.profile_code

,hpt.description

,hpt.summary

,hpeiv.DESCRIPTION

,hpeiv.RESPONSIBILITIES

,hpeiv.QUALIFICATIONS

from HRT_PROFILE_ITEMS hpi

,HRT_PROFILES_B hpb

,HRT_PROFILES_TL hpt

,HRT_PROFILE_RELATIONS hpr

,HRT_PROFILE_EXTRA_INFO_VL hpeiv

,HR_ALL_POSITIONS_F hapf

where hpi.profile_id = hpb.profile_id

and hpb.profile_usage_code = 'M'

and hpi.profile_id = hpr.profile_id

and hpi.profile_id = hpt.profile_id

and hpi.profile_id = hpeiv.profile_id

and hapf.position_id = hpr.object_id

and trunc(sysdate) between hapf.effective_start_date and hapf.effective_end_Date

and hapf.position_code= '1099'

and hpt.language = 'US'

Session Variables in Fusion HCM for BI

noreply@blogger.com (Sandip Jadhav) — Wed, 19 Jun 2019 10:36:00 +0000

Session Variables in BI Publisher to get logged in user info

Session variables are used frequently in BI Publisher when the reports are being exposed to employees.

We need to write query to pull only the logged in user information and for this we will use the session variable.

Apart from this user, there are many other session variables and we are going to look at them now.

Logged in User Person ID - HRC_SESSION_UTIL.get_user_personid

Logged in User Username - :xdo_user_name

Logged in User Username - FND_GLOBAL.USER_NAME

Logged in User GUID - fnd_global.user_guid

Logged in User SessionID - userenv('sessionid')

Logged in User Language - USERENV ('LANG')

Logged in User Business Group - fnd_profile.value('PER_BUSINESS_GROUP_ID')

Sample SQLs:

select * from per_persons where person_id = HRC_SESSION_UTIL.get_user_personid
select * from per_organization_units WHERE business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')

You can add these to the SQL query as per your requirement and get appropriate results.

Hope this is useful.. stay tuned for more updates.

Report for pending transactions and who they are pending with in Fusion

noreply@blogger.com (Sandip Jadhav) — Wed, 28 Nov 2018 13:02:00 +0000

SELECT dp.person_number "Person Number",
n.display_name "Worker Name",
txnh.module_identifier "UI Process",
wft.creator "Requestor",
wft.assigneesdisplayname
||','
||wft.assignees "Current Assignee,User,Type",
wft.assigneddate "Assigned Date",
wft.approvers "Approved By",
wft.title "Notification Title",
wft.tasknumber "Task Number",
txnd.status "HCM Transaction Status",
wft.state "BPM Task State",
txnh.object
FROM fusion.per_all_people_f dp,
fusion.per_person_names_f_v n,
fusion.per_all_assignments_m asg,
fusion.hrc_txn_header txnh,
fusion.hrc_txn_data txnd,
hcm_fusion_soainfra.WFTASK wft
WHERE dp.person_id             =n.person_id
AND asg.person_id              =n.person_id
AND LENGTH(asg.assignment_type)=1
AND asg.assignment_id          =txnh.object_id
AND wft.identificationkey      =TO_CHAR(txnh.transaction_id)
AND txnh.object                ='PER_ALL_ASSIGNMENTS_M'
AND txnh.transaction_id        =txnd.transaction_id
AND sysdate BETWEEN asg.effective_start_date AND asg.effective_end_date
AND asg.effective_latest_change='Y'
AND sysdate BETWEEN dp.effective_start_date AND dp.effective_end_date
AND sysdate BETWEEN n.effective_start_date AND n.effective_end_date
AND txnd.status IN ('PENDING','ERROR')
UNION
SELECT dp.person_number "Person Number",
n.display_name "Worker Name",
txnh.module_identifier "UI Process",
wft.creator "Requestor",
wft.assigneesdisplayname
||','
||wft.assignees "Current Assignee,User,Type",
wft.assigneddate "Assigned Date",
wft.approvers "Approved By",
wft.title "Notification Title",
wft.tasknumber "Task Number",
txnd.status "HCM Transaction Status",
wft.state "BPM Task State",
txnh.object
FROM fusion.per_all_people_f dp,
fusion.per_person_names_f_v n,
fusion.per_all_assignments_m asg,
fusion.hrc_txn_header txnh,
fusion.hrc_txn_data txnd,
hcm_fusion_soainfra.WFTASK wft
WHERE dp.person_id             =n.person_id
AND asg.person_id              =n.person_id
AND LENGTH(asg.assignment_type)=1
AND asg.period_of_service_id   =txnh.object_id
AND wft.identificationkey      =TO_CHAR(txnh.transaction_id)
AND txnh.object                ='PER_PERIODS_OF_SERVICE'
AND txnh.transaction_id        =txnd.transaction_id
AND sysdate BETWEEN asg.effective_start_date AND asg.effective_end_date
AND asg.effective_latest_change='Y'
AND sysdate BETWEEN dp.effective_start_date AND dp.effective_end_date
AND sysdate BETWEEN n.effective_start_date AND n.effective_end_date
AND txnd.status IN ('PENDING','ERROR')
UNION
SELECT 'Create Worker' "Person Number",
'Create Worker' "Worker Name",
txnh.module_identifier "UI Process",
wft.creator "Requestor",
wft.assigneesdisplayname
||','
||wft.assignees "Current Assignee,User,Type",
wft.assigneddate "Assigned Date",
wft.approvers "Approved By",
wft.title "Notification Title",
wft.tasknumber "Task Number",
txnd.status "HCM Transaction Status",
wft.state "BPM Task State",
txnh.object
FROM fusion.hrc_txn_header txnh,
fusion.hrc_txn_data txnd,
hcm_fusion_soainfra.WFTASK wft
WHERE wft.identificationkey =TO_CHAR(txnh.transaction_id)
AND txnh.object             ='PER_ALL_PEOPLE_F'
AND txnh.transaction_id     =txnd.transaction_id
AND txnd.status            IN ('PENDING','ERROR');

Accrual Plan Entry Details

noreply@blogger.com (Sandip Jadhav) — Mon, 15 Oct 2018 10:01:00 +0000

Select * from ANC_PER_ACRL_ENTRY_DTLS

Where person_id = (select distinct person_id from per_all_people_f where person_number =’’)

Assigned User Roles Query in Fusion

noreply@blogger.com (Sandip Jadhav) — Tue, 02 Oct 2018 14:02:00 +0000

SELECT pu.person_id,

hz.party_id,

'Person' AS Type,

ppn.display_name,

pu.username,

DECODE(pu.suspended, 'Y', 'Yes', 'No') AS Suspended,

prdv.role_common_name,

prdv.role_name,

prdv.description,

prdv.abstract_role,

prdv.job_role,

prdv.data_role,
pur.last_updated_by,
pur.last_update_date

FROM fusion.per_users pu,

fusion.hz_parties hz,

fusion.per_user_roles pur,

fusion.per_roles_dn_vl prdv,

fusion.per_person_names_f ppn

WHERE pu.user_id = pur.user_id(+)

AND pu.user_guid = hz.user_guid

AND prdv.role_guid(+) = pur.role_guid

AND pu.person_id IS NOT NULL

AND ppn.name_type = 'GLOBAL'

AND ppn.person_id = pu.person_id

AND (sysdate BETWEEN ppn.effective_start_date AND ppn.effective_end_date)

and PU.ACTIVE_FLAG = 'Y'

and upper(pu.username) IN ('96040')

Departments not available in Organization Tree

noreply@blogger.com (Sandip Jadhav) — Mon, 24 Sep 2018 11:16:00 +0000

SELECT to_char(haou.organization_id) organization_id, haou.NAME Department_name, haou.attribute2 Department_Category, hla.location_name,htt.territory_short_name country
FROM hr_all_organization_units haou,
hr_org_unit_classifications_f haouc,
hr_locations_all hla,
fnd_territories_tl htt
WHERE haou.organization_id = haouc.organization_id
AND haouc.status <> 'I'
AND haou.location_id = hla.location_id(+)
and haouc.classification_code = 'DEPARTMENT'
AND TRUNC (SYSDATE) BETWEEN haou.effective_start_date
AND haou.effective_end_date
AND TRUNC (SYSDATE) BETWEEN haouc.effective_start_date
AND haouc.effective_end_date
AND hla.country = htt.territory_code
and htt.source_lang = 'US'
and htt.territory_short_name = nvl(:p_country,htt.territory_short_name)
AND haou.organization_id NOT IN (SELECT potn.pk1_start_value
FROM per_org_tree_node potn
WHERE 1=1
and potn.tree_code in( 'ORG_TREE_CODE')
)

Department List in Fusion

noreply@blogger.com (Sandip Jadhav) — Mon, 24 Sep 2018 11:15:00 +0000

Regeneration of Roles

noreply@blogger.com (Sandip Jadhav) — Thu, 20 Sep 2018 10:12:00 +0000

Post any cloud upgrade, it is recommended to regenerate all the roles to avoid security-related issues. I have enabled an option where one can regenerate all the roles in bulk.

Please navigate to My Workforce - ( on the left side panel, search for) - Manage Data Roles and Security Profiles.

On right side top corner, you will find Regenerate All Roles button. You can schedule a process to regenerate bulk roles with the following options:

Named Job Role	Regenerates any data roles that inherits the specified job role directly. Data roles that inherit the job role indirectly are not regenerated.
Named Data Role	Regenerates the specified data role only.
Named Abstract Role	Regenerates the specified abstract role only.
Process All Roles	Regenerates all roles to which security profiles are assigned. In this mode, secured access for all roles is recalculated and the secured access of all users is refreshed. The time taken to complete this process depends on the number of roles to be regenerated.

After each upgrade, you must choose the last option of Process All Roles. For Rel 13, we will do it for you but keep this email handy for your future upgrades.

Work Schedule Query

noreply@blogger.com (Sandip Jadhav) — Thu, 20 Sep 2018 07:35:00 +0000

select * from per_schedule_assignments where schedule_id = 300000058913254 and resource_type <> 'ASSIGN' and end_date >= sysdate

--select * from zmm_sr_schedules_tl where schedule_id = 300000058913254

Fusion HCM Personalization Expression Builder based on Roles attached to User

noreply@blogger.com (Sandip Jadhav) — Wed, 12 Sep 2018 08:22:00 +0000

#{securityContext.userInRole['XX_PER_EMPLOYEE_ABSTRACT_CUSTOM,ORA_ASM_APPLICATION_IMPLEMENTATION_CONSULTANT_JOB']}

Steps to create TCC Batch file

noreply@blogger.com (Sandip Jadhav) — Thu, 08 Feb 2018 13:50:00 +0000

Steps to create TCC Batch file

1. Open any configuration file in TCC

2. Go to General tab à Enable Schedule Monitoring à Click on “Click Here to Create Schedule” à Build Command Line à Copy the path e.g. "D:\Taleo Connect Client\TaleoConnectClient.bat" "D:\Taleo_Interfaces\Dev\Outbound\TCC_Scripts\DefaultHiringManagerRecruiter_cfg.xml"

3. Open Notepad

4. Type call and path from step 2. It will be like call "D:\Taleo Connect Client\TaleoConnectClient.bat" "D:\Taleo_Interfaces\Dev\Outbound\TCC_Scripts\DefaultHiringManagerRecruiter_cfg.xml"

5. Save the notepad file as “DefaultHiringManagerRecruiter.bat”

6. Go to Task Scheduler of Server where TCC is installed.

7. Click on Create Basic Task

8. Define the schedule and under Program/script browse file created in step 5.

9. If needed Task can be updated.

WF Comments in Fusion BIP Reports

noreply@blogger.com (Sandip Jadhav) — Tue, 06 Feb 2018 12:30:00 +0000

SELECT txnh.module_identifier ProcessName,
wft.creator Requestor,
wft.assignees CurrentAssignee,
wft.assigneddate AssignedDate,
wft.title NotificationTitle,
txnd.status TxnStatus,
txnh.object ObjectName, wfc.wfcomment
FROM fusion.per_all_people_f dp,
fusion.per_person_names_f_v n,
fusion.per_all_assignments_m asg,
fusion.hrc_txn_header txnh,
fusion.hrc_txn_data txnd,
hcm_fusion_soainfra.WFTASK wft,
HCM_FUSION_SOAINFRA.wfcomments wfc

WHERE dp.person_id =n.person_id

AND asg.person_id =n.person_id

AND LENGTH(asg.assignment_type)=1

AND asg.assignment_id =txnh.object_id

AND wft.identificationkey =TO_CHAR(txnh.transaction_id)
and wfc.taskid = wft.taskid

AND txnh.object ='PER_ALL_ASSIGNMENTS_M'

AND txnh.transaction_id =txnd.transaction_id

AND sysdate BETWEEN asg.effective_start_date AND asg.effective_end_date

AND asg.effective_latest_change='Y'

AND sysdate BETWEEN dp.effective_start_date AND dp.effective_end_date

AND sysdate BETWEEN n.effective_start_date AND n.effective_end_date

Supervisor Query

noreply@blogger.com (Sandip Jadhav) — Sat, 25 Nov 2017 18:18:00 +0000

SELECT    papf.person_number
         ,TO_CHAR (pps.date_start
                  ,'DD-Mon-YYYY'
                  ,'NLS_DATE_LANGUAGE=AMERICAN')
             person_hire_date
         ,TO_CHAR (pps.actual_termination_date
                  ,'DD-Mon-YYYY'
                  ,'NLS_DATE_LANGUAGE=AMERICAN')
             person_termination_date
         ,TO_CHAR (paaf.effective_start_date
                  ,'DD-Mon-YYYY'
                  ,'NLS_DATE_LANGUAGE=AMERICAN')
             assignment_start_date
         ,TO_CHAR (paaf.effective_end_date
                  ,'DD-Mon-YYYY'
                  ,'NLS_DATE_LANGUAGE=AMERICAN')
             assignment_end_date
         ,TO_CHAR (pasf.effective_start_date
                  ,'DD-Mon-YYYY'
                  ,'NLS_DATE_LANGUAGE=AMERICAN')
             manager_start_date
         ,TO_CHAR (pasf.effective_end_date
                  ,'DD-Mon-YYYY'
                  ,'NLS_DATE_LANGUAGE=AMERICAN')
             manager_end_date
         ,pasf.manager_type
         ,pasf.primary_flag
         ,paaf.assignment_number
         ,papfs.person_number manager_number
         ,TO_CHAR (ppss.date_start
                  ,'DD-Mon-YYYY'
                  ,'NLS_DATE_LANGUAGE=AMERICAN')
             manager_hire_date
         ,TO_CHAR (ppss.actual_termination_date
                  ,'DD-Mon-YYYY'
                  ,'NLS_DATE_LANGUAGE=AMERICAN')
             manager_termination_date
FROM      per_periods_of_service ppss
         ,per_all_people_f papfs
         ,per_assignment_supervisors_f pasf
         ,per_all_people_f papf
         ,per_all_assignments_m paaf
         ,per_periods_of_service pps
WHERE         ppss.person_id(+) = papfs.person_id
          AND pasf.effective_start_date BETWEEN papfs.effective_start_date(+) AND papfs.effective_end_date(+)
          AND papfs.person_id(+) = pasf.manager_id
          AND paaf.effective_start_date BETWEEN pasf.effective_start_date(+) AND pasf.effective_end_date(+)
          AND pasf.assignment_id(+) = paaf.assignment_id
          AND papf.person_number = NVL (:p_person_number, papf.person_number)
          AND pps.date_start BETWEEN papf.effective_start_date AND papf.effective_end_date
          AND papf.person_id = paaf.person_id
          AND paaf.effective_latest_change = 'Y'
          AND paaf.primary_assignment_flag = 'Y'
          AND paaf.primary_flag = 'Y'
          AND paaf.assignment_type = 'E'
          AND paaf.period_of_service_id = pps.period_of_service_id
ORDER BY papf.person_number
         ,paaf.effective_start_date

Element Links

noreply@blogger.com (Sandip Jadhav) — Sat, 25 Nov 2017 18:17:00 +0000

SELECT    pldgv.name legislative_data_group
         ,pect.classification_name
         ,pett.element_name
         ,pelf.element_link_name
         ,TO_CHAR (pelf.effective_start_date
                  ,'YYYY/MM/DD')
             effective_start_date
         ,TO_CHAR (pelf.effective_end_date
                  ,'YYYY/MM/DD')
             effective_end_date
         ,haou.name legal_entity
         ,ppg.segment1 people_group_segment1
         ,ppg.segment2 people_group_segment2
         ,ppg.segment3 people_group_segment3
         ,pcac.source_sub_type
         ,pcac.segment1 cost_segment1
         ,pcac.segment2 cost_segment2
         ,pcac.segment3 cost_segment3
         ,pcac.segment4 cost_segment4
         ,pcac.segment5 cost_segment5
         ,pcac.segment6 cost_segment6
         ,pcac.segment7 cost_segment7
         ,pcac.segment8 cost_segment8
         ,pcif.costable_type costing_type
         ,pcif.transfer_to_gl_flag
FROM      pay_cost_alloc_accounts pcac
         ,pay_cost_allocations_f pcaf
         ,pay_cost_info_f pcif
         ,per_people_groups ppg
         ,hr_all_organization_units haou
         ,pay_element_criteria pec
         ,pay_element_links_f pelf
         ,pay_ele_classifications_tl pect
         ,per_legislative_data_groups_vl pldgv
         ,pay_element_types_f petf
         ,pay_element_types_tl pett
WHERE         pcac.cost_allocation_record_id(+) = pcaf.cost_allocation_record_id
          AND pelf.effective_start_date BETWEEN pcaf.effective_start_date(+) AND pcaf.effective_end_date(+)
          AND pcaf.source_type(+) = 'EL'
          AND pcaf.source_id(+) = pelf.element_link_id
          AND pelf.effective_start_date BETWEEN pcif.effective_start_date(+) AND pcif.effective_end_date(+)
          AND pcif.source_type(+) = 'EL'
          AND pcif.source_id(+) = pelf.element_link_id
          AND ppg.people_group_id(+) = pec.people_group_id
          AND haou.organization_id(+) = pec.legal_employer_id
          AND pec.element_criteria_id(+) = pelf.element_criteria_id
          AND pelf.element_type_id(+) = pett.element_type_id
          AND pect.language = 'US'
          AND pect.classification_id = petf.classification_id
          AND pldgv.legislative_data_group_id = petf.legislative_data_group_id
          AND petf.effective_start_date = (SELECT MAX (petfi.effective_start_date)
                                           FROM    pay_element_types_f petfi
                                           WHERE   petfi.element_type_id = petf.element_type_id)
          AND petf.element_type_id = pett.element_type_id
          AND pett.language = 'US'
ORDER BY pect.classification_name
         ,pett.element_name
         ,pelf.element_link_name
         ,pelf.effective_start_date

SELECT    pect.classification_name link_classification_name
         ,pett.element_name link_element_name
         ,pelf.element_link_name link_element_link_name
         ,TO_CHAR (plivf.effective_start_date
                  ,'YYYY/MM/DD')
             link_effective_start_date
         ,TO_CHAR (plivf.effective_end_date
                  ,'YYYY/MM/DD')
             link_effective_end_date
         ,pivt.name link_input_name
         ,pcif.costable_type link_costable_type
         ,pcif.costed_flag link_costed_flag
         ,pcif.transfer_to_gl_flag link_transfer_to_gl_flag
FROM      pay_ele_classifications_tl pect
         ,pay_element_types_f petf
         ,pay_element_types_tl pett
         ,pay_element_links_f pelf
         ,pay_input_values_tl pivt
         ,pay_link_input_values_f plivf
         ,pay_cost_info_f pcif
WHERE         pect.language = 'US'
          AND pect.classification_id = petf.classification_id
          AND pcif.effective_start_date BETWEEN petf.effective_start_date AND petf.effective_end_date
          AND petf.element_type_id = pett.element_type_id
          AND pett.language = 'US'
          AND pett.element_type_id = pelf.element_type_id
          AND pcif.effective_start_date BETWEEN pelf.effective_start_date AND pelf.effective_end_date
          AND pelf.element_link_id = plivf.element_link_id
          AND pivt.language = 'US'
          AND pivt.input_value_id = plivf.input_value_id
          AND pcif.effective_start_date BETWEEN plivf.effective_start_date AND plivf.effective_end_date
          AND plivf.link_input_value_id = pcif.source_id
          AND pcif.source_type = 'LIV'
ORDER BY pect.classification_name
         ,pett.element_name
         ,pelf.element_link_name
         ,pelf.effective_start_date

SELECT    pldg.name ele_leg_data_group_name
         ,pect.classification_name ele_classification_name
         ,pett.element_name ele_element_name
         ,pivf.display_sequence ele_display_sequence
         ,pivt.name ele_input_name
         ,pivf.user_enterable_flag ele_user_enterable_flag
         ,pivf.mandatory_flag ele_mandatory_flag
         ,pivf.hot_default_flag ele_hot_default_flag
         ,hr_general.decode_lookup ('UOM'
                                   ,pivf.uom)
             ele_uom
         ,pivf.user_display_flag ele_user_display_flag
         ,pivf.DEFAULT_VALUE ele_default_value
         ,pivf.lookup_type ele_lookup_type
         ,pivf.min_value ele_min_value
         ,pivf.max_value ele_max_value
         ,pivf.value_set_code ele_value_set_code
FROM      pay_input_values_tl pivt
         ,pay_input_values_f pivf
         ,per_legislative_data_groups_vl pldg
         ,pay_ele_classifications_tl pect
         ,pay_element_types_tl pett
         ,pay_element_types_f petf
WHERE         pivt.language = 'US'
          AND pivt.input_value_id = pivf.input_value_id
          AND petf.effective_start_date BETWEEN pivf.effective_start_date AND pivf.effective_end_date
          AND pldg.legislative_data_group_id = petf.legislative_data_group_id
          AND pect.language = 'US'
          AND pect.classification_id = petf.classification_id
          AND pett.language = 'US'
          AND pett.element_type_id = petf.element_type_id
and 1=2
ORDER BY pect.classification_name
         ,pett.element_name
         ,petf.effective_start_date
         ,pivf.display_sequence

Bank Details Query

noreply@blogger.com (Sandip Jadhav) — Sat, 25 Nov 2017 18:15:00 +0000

SELECT   papf.person_number
,       ppnf.full_name
,           pp.attribute1 initials
,           bank_dtls.bank_account_name
,           bank_dtls.bank_name
,           bank_dtls.bank_branch_name
,          bank_dtls.bank_account_num
,           bank_dtls.iban_number
,           bank_dtls.currency_code
,           bank_dtls.base_org_pay_method_name
FROM   per_all_people_f papf
,    per_person_names_f ppnf
,    per_all_assignments_f paaf
,    pay_rel_groups_dn payrel
,           per_persons pp
,    (SELECT pba.bank_account_name
       ,       pba.bank_name
       ,       pba.bank_branch_name
       ,     pba.bank_account_num
       ,       pba.iban_number
       ,       popf.currency_code
       ,       popf.base_org_pay_method_name
     ,       pppmf.payroll_relationship_id
     FROM  pay_bank_accounts pba
     ,   pay_person_pay_methods_f pppmf
     ,   pay_org_pay_methods_f popf
     WHERE pppmf.bank_account_id = pba.bank_account_id
     AND     pppmf.org_payment_method_id = popf.org_payment_method_id
     AND TRUNC(SYSDATE) BETWEEN pppmf.effective_start_date AND pppmf.effective_end_date
     AND TRUNC(SYSDATE) BETWEEN popf.effective_start_date AND popf.effective_end_date
     AND TRUNC(SYSDATE) BETWEEN pba.start_date AND pba.end_date) bank_dtls
WHERE papf.person_id = ppnf.person_id
AND ppnf.name_type ='GLOBAL'
AND payrel.payroll_relationship_id = bank_dtls.payroll_relationship_id(+)
AND payrel.assignment_id = paaf.assignment_id
AND papf.person_id = paaf.person_id
AND   paaf.assignment_status_type = 'ACTIVE'
--AND papf.person_number = '44'
-- AND paaf.primary_flag = 'Y'
AND   papf.person_id = pp.person_id
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
--AN D TRUNC(SYSDATE) BETWEen epbad.account_start_date and epbad.account_end_date
AND TRUNC(SYSDATE) BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND TRUNC(SYSDATE) BETWEEN payrel.start_date AND payrel.end_date

Assigned Payrolls

noreply@blogger.com (Sandip Jadhav) — Sat, 25 Nov 2017 18:15:00 +0000

SELECT    papf.person_number
         ,paam.assignment_number
         ,TO_CHAR (pps.date_start
                  ,'YYYY/MM/DD')
             hire_date
         ,TO_CHAR (pps.actual_termination_date
                  ,'YYYY/MM/DD')
             actual_termination_date
         ,papfp.payroll_name
         ,TO_CHAR (papd.start_date
                  ,'YYYY/MM/DD')
             start_date
         ,TO_CHAR (papd.lsed
                  ,'YYYY/MM/DD')
             close_date
         ,TO_CHAR (papd.lspd
                  ,'YYYY/MM/DD')
             last_standard_process_date
         ,TO_CHAR (papd.fsed
                  ,'YYYY/MM/DD')
             final_process_date
FROM      pay_all_payrolls_f papfp
         ,pay_assigned_payrolls_dn papd
         ,pay_payroll_assignments ppay
         ,per_all_assignments_m paam
         ,per_all_people_f papf
         ,per_periods_of_service pps
WHERE         1 = 1
          AND papd.start_date BETWEEN papfp.effective_start_date(+) AND papfp.effective_end_date(+)
          AND papfp.payroll_id(+) = papd.payroll_id
          AND papd.payroll_term_id(+) = ppay.payroll_term_id
          AND ppay.hr_assignment_id(+) = paam.assignment_id
          AND LEAST (NVL (pps.actual_termination_date
                         ,TO_DATE ('31-12-4712'
                                  ,'DD-MM-YYYY'))
                    ,TO_DATE ('31-12-4712'
                             ,'DD-MM-YYYY')) BETWEEN paam.effective_start_date
                                                 AND paam.effective_end_date
          AND paam.effective_latest_change = 'Y'
          AND paam.assignment_type = 'E'
          AND paam.primary_assignment_flag = 'Y'
          AND paam.primary_flag = 'Y'
          AND paam.period_of_service_id = pps.period_of_service_id
          AND pps.date_start BETWEEN papf.effective_start_date AND papf.effective_end_date
          AND papf.person_id = pps.person_id
ORDER BY papf.person_number
         ,papd.start_date

Area of Responsibility AOR Query

noreply@blogger.com (Sandip Jadhav) — Sat, 25 Nov 2017 18:13:00 +0000

SELECT (SELECT DISTINCT PERSON_NUMBER FROM PER_ALL_PEOPLE_F PER WHERE PER.PERSON_ID = AOR.PERSON_ID) PERSON_NUMBER,
    (SELECT DISTINCT FULL_NAME FROM PER_PERSON_NAMES_F PER WHERE PER.PERSON_ID = AOR.PERSON_ID AND NAME_TYPE = 'GLOBAL') PERSON_NAME,
    AOR.RESPONSIBILITY_NAME,
    TO_CHAR(AOR.START_DATE,'DD-MON-YYYY','NLS_DATE_LANGUAGE = AMERICAN') START_DATE,
    TO_CHAR(AOR.END_DATE,'DD-MON-YYYY','NLS_DATE_LANGUAGE = AMERICAN') END_DATE,
    AOR.RESPONSIBILITY_TYPE,
    AOR.STATUS,
    (SELECT NAME FROM HR_ALL_ORGANIZATION_UNITS HOU WHERE HOU.ORGANIZATION_ID = AOR.BUSINESS_UNIT_ID) BUSINESS_UNIT,
    (SELECT NAME FROM HR_ALL_ORGANIZATION_UNITS HOU WHERE HOU.ORGANIZATION_ID = AOR.LEGAL_ENTITY_ID) LEGAL_ENTITY,
    (SELECT NAME FROM HR_ALL_ORGANIZATION_UNITS HOU WHERE HOU.ORGANIZATION_ID = AOR.ORGANIZATION_ID) DEPARTMENT,
    (SELECT LOCATION_NAME FROM HR_LOCATIONS HL WHERE HL.LOCATION_ID = AOR.LOCATION_ID) LOCATION,
    (SELECT DISTINCT NAME FROM HR_ALL_POSITIONS_F_VL PP WHERE PP.POSITION_ID = AOR.POSITION_ID) POSITION,
    (SELECT DISTINCT NAME FROM PER_JOBS_F_TL PJ WHERE PJ.JOB_ID = AOR.JOB_ID) JOB,
    (SELECT DISTINCT NAME FROM PER_GRADES_F_TL PG WHERE PG.GRADE_ID = AOR.GRADE_ID) GRADE,
    AOR.ASSIGNMENT_CATEGORY,
    (SELECT PAY.PAYROLL_NAME
          FROM PAY_ALL_PAYROLLS_F    PAY
        WHERE PAY.PAYROLL_ID = AOR.PAYROLL_ID
             AND TRUNC(SYSDATE) BETWEEN PAY.EFFECTIVE_START_DATE AND PAY.EFFECTIVE_END_DATE ) PAYROLL,
   (SELECT NAME
     FROM PER_LEGISLATIVE_DATA_GROUPS_VL PLD
      WHERE PLD.LEGISLATIVE_DATA_GROUP_ID = AOR.LEGISLATIVE_DATA_GROUP_ID) LEGISLATIVE_DATA_GROUP
FROM PER_ASG_RESPONSIBILITIES AOR

Absences Leave Entries

noreply@blogger.com (Sandip Jadhav) — Sat, 25 Nov 2017 18:12:00 +0000

select per.person_number,
            abs.name absence_type,
            to_char(apae.start_date,'DD-MON-YYYY','NLS_DATE_LANGUAGE = AMERICAN') start_date,
            to_char(apae.end_date,'DD-MON-YYYY','NLS_DATE_LANGUAGE = AMERICAN') end_date,
            duration
from ANC_PER_ABS_ENTRIES apae,
         PER_PERIODS_OF_SERVICE pps,
         PER_ALL_PEOPLE_F per,
   ANC_ABSENCE_TYPES_VL abs
where apae.period_of_service_id = pps.period_of_Service_id
    and apae.absence_type_id = abs.absence_type_id
    and pps.person_id = per.person_id
and trunc(sysdate) between abs.effective_start_date and abs.effective_end_Date
and trunc(sysdate) between per.effective_start_date and per.effective_end_Date
order by per.person_number

Work Schedules Query

noreply@blogger.com (Sandip Jadhav) — Sat, 25 Nov 2017 18:11:00 +0000

SELECT to_char(psa.start_date,'YYYY/MM/DD') START_DATE,to_char(psa.end_date,'YYYY/MM/DD') END_DATE,papf.person_number,zsst.schedule_name,zsst.SCHEDULE_ID,psa.SCHEDULE_ASSIGNMENT_ID
FROM   zmm_sr_schedules_tl zsst
      ,per_schedule_assignments psa
      ,per_all_assignments_m paam1
      ,per_all_people_f papf
WHERE      1 = 1
       AND zsst.language = 'US'
       AND zsst.schedule_id = psa.schedule_id
       AND TRUNC (SYSDATE) BETWEEN psa.start_date AND psa.end_date
       AND psa.resource_id = paam1.assignment_id
       AND psa.resource_type = 'ASSIGN'
       AND TRUNC (SYSDATE) BETWEEN paam1.effective_start_date
                               AND paam1.effective_end_date
       AND paam1.primary_flag = 'Y'
         AND paam1.effective_latest_change = 'Y'
       AND paam1.assignment_type = 'E'
       AND papf.person_id = paam1.person_id
       AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date
                               AND papf.effective_end_date
--and person_number = '17'
ORDER BY LPAD(PAPF.PERSON_NUMBER,20,'0')

User Roles Query

noreply@blogger.com (Sandip Jadhav) — Sat, 25 Nov 2017 18:10:00 +0000

select papf.person_number,
--pu.username,
prd.role_common_name
from
per_users pu,
per_user_roles pr,
per_roles_dn prd ,
per_all_people_f papf
where 1=1
and papf.person_id = pu.person_id
and pu.user_id = pr.user_id
and pr.role_id = prd.role_id
--and papf.person_number = '119802'
--and prd.role_common_name ='PAYROLL_SPECIALIST_DATA'

Salary Query

noreply@blogger.com (Sandip Jadhav) — Sat, 25 Nov 2017 18:09:00 +0000

SELECT   papf.person_number
        ,TO_CHAR(cs.date_to,'YYYY/MM/DD') date_to
        ,to_char(cs.date_from,'YYYY/MM/DD') date_from
        ,cs.salary_amount
        ,csb.name
FROM     cmp_salary cs
        ,cmp_salary_bases csb
        ,per_all_assignments_m paam
        ,per_all_people_f papf
WHERE        cs.salary_basis_id = csb.salary_basis_id
         AND paam.assignment_id = cs.assignment_id
         AND paam.effective_latest_change = 'Y'
         AND paam.primary_assignment_flag = 'Y'
         AND TRUNC (SYSDATE) BETWEEN paam.effective_start_date
                                 AND paam.effective_end_date
         AND papf.person_id = paam.person_id
         AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date
                                 AND papf.effective_end_date
ORDER BY LPAD (papf.person_number
              ,20
              ,'0')

Position Query

noreply@blogger.com (Sandip Jadhav) — Sat, 25 Nov 2017 18:08:00 +0000

SELECT    esd
         ,position_code
         ,department
         ,business_unit
         ,position_name
         ,full_part_time
         ,permanent_temporary_flag
         ,position_type
         ,fte
         ,head_count
         ,probation_period
         ,probation_period_unit_cd
         ,hiring_status
         ,job_code
         ,health_card
         ,MIN (valid_grade_code1) valid_grade_code1
         ,MIN (valid_grade_code2) valid_grade_code2
         ,MIN (valid_grade_code3) valid_grade_code3
FROM      (SELECT TO_CHAR (hap.effective_start_date
                           ,'dd-mm-yyyy')
                      esd
                  ,hap.position_code position_code
                  ,haou.name department
                  ,haoub.name business_unit
                  ,hapt.name position_name
                  ,hap.full_part_time
                  ,hap.permanent_temporary_flag
                  ,hap.position_type position_type
                  ,hap.fte fte
                  ,hap.max_persons head_count
                  ,hap.probation_period probation_period
                  ,hap.probation_period_unit_cd probation_period_unit_cd
                  ,hap.hiring_status hiring_status
                  ,pj.job_code job_code
                  ,hap.attribute1 health_card
                  ,CASE
                      WHEN RANK ()
                              OVER (PARTITION BY hap.position_id
                                    ORDER BY LPAD (pg.grade_code
                                                  ,10
                                                  ,'0')) = 1
                      THEN
                         pg.grade_code
                      ELSE
                         ''
                   END
                      valid_grade_code1
                  ,CASE
                      WHEN RANK ()
                              OVER (PARTITION BY hap.position_id
                                    ORDER BY LPAD (pg.grade_code
                                                  ,10
                                                  ,'0')) = 2
                      THEN
                         pg.grade_code
                      ELSE
                         ''
                   END
                      valid_grade_code2
                  ,CASE
                      WHEN RANK ()
                              OVER (PARTITION BY hap.position_id
                                    ORDER BY LPAD (pg.grade_code
                                                  ,10
                                                  ,'0')) = 3
                      THEN
                         pg.grade_code
                      ELSE
                         ''
                   END
                      valid_grade_code3
           FROM    hr_all_positions_f hap
                  ,hr_all_positions_f_tl hapt
                  ,hr_all_organization_units haoub
                  ,hr_all_organization_units haou
                  ,per_jobs_f pj
                  ,per_valid_grades_f pvg
                  ,per_grades_f pg
                  ,per_grades_f_tl pgft
           WHERE       1 = 1
                   AND TRUNC (SYSDATE) BETWEEN hap.effective_start_date AND hap.effective_end_date
                   AND hapt.position_id = hap.position_id
                   AND hapt.language = 'US'
                   AND TRUNC (SYSDATE) BETWEEN hapt.effective_start_date AND hapt.effective_end_date
                   AND haoub.organization_id = hap.business_unit_id
                   AND haou.organization_id = hap.organization_id
                   AND pj.job_id = hap.job_id
                   AND TRUNC (SYSDATE) BETWEEN pj.effective_start_date AND pj.effective_end_date
                   AND pvg.position_id = hap.position_id
                   AND TRUNC (SYSDATE) BETWEEN pvg.effective_start_date AND pvg.effective_end_date
                   AND pg.grade_id = pvg.grade_id
                   AND TRUNC (SYSDATE) BETWEEN pg.effective_start_date AND pg.effective_end_date
                   AND pgft.grade_id = pg.grade_id
                   AND pgft.language = 'US'
                   AND TRUNC (SYSDATE) BETWEEN pgft.effective_start_date AND pgft.effective_end_date)
GROUP BY esd
         ,position_code
         ,department
         ,business_unit
         ,position_name
         ,full_part_time
         ,permanent_temporary_flag
         ,position_type
         ,fte
         ,head_count
         ,probation_period
         ,probation_period_unit_cd
         ,hiring_status
         ,job_code
         ,health_card

Job Family

noreply@blogger.com (Sandip Jadhav) — Sat, 25 Nov 2017 18:07:00 +0000

SELECT pjf.job_family_name
      ,pj.job_family_code CODE
      ,pj.active_status
      ,TO_CHAR (pj.effective_start_date
               ,'DD-Mon-YYYY'
               ,'NLS_DATE_LANGUAGE=AMERICAN')
          effective_start_date
FROM   per_job_family_f_tl pjf
      ,per_job_family_f pj
WHERE pjf.job_family_id = pj.job_family_id
       AND TRUNC (SYSDATE) BETWEEN pj.effective_start_date
                               AND pj.effective_end_date
       AND TRUNC (SYSDATE) BETWEEN pjf.effective_start_date
                               AND pjf.effective_end_date

Leave Initial Balance

noreply@blogger.com (Sandip Jadhav) — Sat, 25 Nov 2017 18:06:00 +0000

select PER.PERSON_NUMBER,
            PPN.FULL_NAME,
            TO_CHAR(ACC.PROCD_DATE,'DD-MON-YYYY','NLS_DATE_LANGUAGE = AMERICAN') PROCD_DATE,
            (select NAME
   from ANC_ABSENCE_PLANS_VL
   WHERE ABSENCE_PLAN_ID = ACC.PL_ID) PLAN_NAME,
            ACC.VALUE BALANCE
   from ANC_PER_ACRL_ENTRY_DTLS ACC,
            PER_ALL_PEOPLE_F PER,
            PER_PERSON_NAMES_F PPN
WHERE ACC.PERSON_ID = PER.PERSON_ID
AND PER.PERSON_ID = PPN.PERSON_ID
AND TRUNC(SYSDATE) BETWEEN PER.EFFECTIVE_START_DATE AND PER.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN PPN.EFFECTIVE_START_DATE AND PPN.EFFECTIVE_END_DATE
AND PPN.NAME_TYPE = 'GLOBAL'
AND ACC.TYPE = 'INIT'
ORDER BY PER.PERSON_NUMBER

Organization Department Query

noreply@blogger.com (Sandip Jadhav) — Sat, 25 Nov 2017 18:05:00 +0000

SELECT haou.name
      ,fnd.set_name
      ,haou.attribute1 classification
      ,haou.attribute2 business_function
      ,haou.attribute3 operating_unit
      ,TO_CHAR (haou.effective_start_date
               ,'dd-mm-yyyy')
          Effective_Start_Date
      ,hl.iNTERNAL_LOCATION_CODE LOCATION_CODE
FROM   hr_org_unit_classifications_f houc
      ,hr_all_organization_units haou
      ,fnd_setid_sets fnd
      ,hr_locations hl
WHERE 1 = 1
AND    hl.location_id(+) = haou.location_id
AND    haou.organization_id = houc.organization_id
AND    houc.classification_code = 'DEPARTMENT'
AND    fnd.set_id = houc.set_id