Element Query

 SELECT petl.element_name an

      ,pelf.element_link_name ga

      ,pciel.effective_start_date gb

      ,pciel.effective_end_date gc

      ,pldgtl.name ao

      ,pivtl.name ap

      ,pciel.costable_type aq

      ,pogtl.object_group_name ar

      ,pciel.transfer_to_gl_flag aaa

      ,pci.costed_flag at

      ,pci.source_type au

      ,pcac.source_sub_type av

      ,CASE

          WHEN use_at_rel_level = 'Y' THEN 'Relationship level'

          WHEN use_at_term_level = 'Y' THEN 'Term Level'

          WHEN use_at_asg_level = 'Y' THEN 'Assignment Level'

       END

          elev

      ,pcac.segment1 aw

      ,pcac.segment2 ax

      ,pcac.segment3 ay

      ,pcac.segment4 az

      ,pcac.segment5 ba

      ,pcac.segment6 bb

      ,pcac.segment7 bc

      ,pcacb.segment1 bd

      ,pcacb.segment2 be

      ,pcacb.segment3 bf

      ,pcacb.segment4 bg

      ,pcacb.segment5 bh

      ,pcacb.segment6 bi

      ,pcacb.segment7 bj

      ,pectl.classification_name pcla

      ,pectl1.classification_name scla

      ,petf.legislation_code lcode

FROM   pay_object_groups_tl pogtl

      ,pay_object_groups pog

      ,per_legislative_data_groups_tl pldgtl

      ,per_legislative_data_groups pldg

      ,pay_cost_alloc_accounts pcacb

      ,pay_cost_alloc_accounts pcac

      ,pay_cost_allocations_f pcaf

      ,pay_cost_info_f pciel

      ,pay_cost_info_f pci

      ,pay_input_values_tl pivtl

      ,pay_input_values_f pivf

      ,pay_link_input_values_f plivf

      ,pay_element_links_f pelf

      ,pay_element_types_tl petl

      ,pay_element_types_f petf

      ,pay_ele_classifications pec

      ,pay_ele_classifications_tl pectl

      ,pay_ele_classifications pec1

      ,pay_ele_classifications_tl pectl1

WHERE  1 = 1

AND    (pectl1.language = 'US'

OR       pectl1.language IS NULL)

AND    pectl1.classification_id(+) = pec1.classification_id

AND    (TRUNC (SYSDATE) BETWEEN pec1.date_from AND pec1.date_to

OR       pec1.date_from IS NULL)

AND    pec1.classification_id(+) = petf.secondary_classification_id

AND    pectl.language = 'US'

AND    pectl.classification_id = pec.classification_id

AND    TRUNC (SYSDATE) BETWEEN pec.date_from AND pec.date_to

AND    pec.classification_id = petf.classification_id

AND    (pogtl.language = 'US'

OR       pogtl.language IS NULL)

AND    pogtl.object_group_id(+) = pog.object_group_id

AND    pog.object_group_id(+) = pciel.distribution_set_id

AND    (pldgtl.language = 'US'

OR       pldgtl.language IS NULL)

AND    pldgtl.legislative_data_group_id(+) = pldg.legislative_data_group_id

AND    pldg.legislative_data_group_id(+) = petf.legislative_data_group_id

AND    pcacb.source_sub_type = 'BAL'

AND    pcacb.cost_allocation_record_id = pcaf.cost_allocation_record_id

AND    pcac.source_sub_type = 'COST'

AND    pcac.cost_allocation_record_id = pcaf.cost_allocation_record_id

AND    pcaf.source_type = 'EL'

AND    pcaf.source_id = pelf.element_link_id

AND    pciel.source_type = 'EL'

AND    pciel.source_id = pelf.element_link_id

AND    pci.source_id = plivf.link_input_value_id

AND    pivtl.language = 'US'

AND    pivtl.input_value_id = pivf.input_value_id

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

AND    pivf.input_value_id = plivf.input_value_id

AND    plivf.element_link_id = pelf.element_link_id

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

AND    pelf.element_type_id = petf.element_type_id

AND    petl.language = 'US'

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

AND    petl.element_type_id = petf.element_type_id

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

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

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

ACCOUNTING BASICS TUTORIALS

1) ACCOUNTING OVERVIEW: Chart of Accounts, Double-Entry Bookkeeping, General Ledger, and Account Types

2) Accounting: Making Sense of Debits and Credits

FNDLOAD for user-defined tables..

pyusertable.lct is the lct for User Defined tables.

It handles all the four tables related to user Defined table

PAY_USER_TABLES
PAY_USER_ROWS_F
PAY_USER_COLUMNS
PAY_USER_COLUMN_INSTANCES_F

Cheers
Ganesh

SYSDATE or TRUNC(SYSDATE)?

One of the common mistakes non-HRMS developers make when they start with HRMS is to use sysdate instead of trunc(sysdate).

While the procurement, financials and the other modules use the time component heavily, HRMS does not capture the time component.

Take an employee record with the following data..

Effective Start Date: 01-Jan-2009 and Effective End Date: 07-Aug-2009.

On the 7th of August, If we run a query like this, we will expect this employee to be present.

select *
from per_all_people_f
where sysdate between effective_start_date and effective_end_date

But this employee wont be in the results.

Because, as sysdate(lets say '07-Aug-2009 12:34:56') will be compared against '07-Aug-2009 00:00:00' and the employee will be left.

Cheers,
Ganesh

New metalink note for GSP(Grade Step Progression)

There is a new note about GSP published in metalink yesterday.

Note Id: 794442.1 - Grade Step Progression (GSP) Focus Area

Cheers
Ganesh

Workflow Status Monitor error..

After upgrade to 11.5.10.2 we had issues with the Workflow Status Monitor Diagram.

We need to set the following profile values at the site level to fix this..

1. Server Timezone
2. Client Timezone

To be on the safer side, give them the same value.

Cheers,
Ganesh

Incorrect Tax Code in P45 Output..

What happened?
After applying the HR Family Pack K-RUP3 , the P45 is showing incorrect tax code.

How do we fix it?

Oracle has released couple of patches for this issue. One for 11i and a forward port for R12.

7410767-P45 SHOWING WRONG TAX CODE AFTER APPLYING SEPTEMBER 08 PATCH 7246613
7420661-HRMSR12: FWD PORT OF 7410767

Cheers,
Ganesh