Thursday, 28 January 2021

SQL Query to Get Database Items and context

 SELECT DBI.USER_NAME,BASE_CONTEXT_NAME

FROM FF_DATABASE_ITEMS_VL DBI,

FF_USER_ENTITIES_VL USERENT,

FF_ROUTES_VL ROUTES,

FF_ROUTE_CONTEXT_USAGES RCU,

FF_CONTEXTS_VL CON

Where  DBI.USER_ENTITY_ID = USERENT.USER_ENTITY_ID

AND USERENT.ROUTE_ID = ROUTES.ROUTE_ID

AND DBI.base_USER_name = <DBI Name>

AND ROUTES.ROUTE_ID = RCU.ROUTE_ID

AND CON.CONTEXT_ID = RCU.CONTEXT_ID

==========================================================

select FF.FORMULA_NAME, BASE_CONTEXT_NAME

ff_contexts_vl CON,

ff_ftype_context_usages FCU,

FF_FORMULA_TYPES_VL FT

WHERE FF.formula_type_id = FT.formula_type_id

AND   FCU.FORMULA_TYPE_ID =  FT.formula_type_id

AND   CON.CONTEXT_ID = FCU.CONTEXT_ID

AND   BASE_FORMULA_NAME LIKE <Formula Bas Name>

Wednesday, 24 January 2018

Payroll Batch Loader Error Query



select ml.*
from pay_bl_message_lines_vl ml
,    pay_batch_lines bl
,    pay_batch_headers bh
where bh.batch_id = bl.batch_id
and   bl.batch_line_id = ml.source_id
and   bh.batch_name = 'BATCH NAME'
and   bl.batch_line_status = 'E';


Full Query for all PBL errors with batch name, task name, task action name, assignment number and error message


SELECT pbh.batch_name,
       pbh.legislative_data_group_id,pbt.display_task_name,pbta.display_task_action_name,
pivot_line_value_data.action_parameter_name1,pivot_line_value_data.action_parameter_value1,pivot_line_value_data.action_parameter_name2,pivot_line_value_data.action_parameter_value2,
       pbmlv.message_text
FROM
(SELECT batch_line_id,
     MAX(decode(rn,1,display_action_parameter_name))
action_parameter_name1,MAX(decode(rn,1,action_parameter_value))
action_parameter_value1,
     MAX(decode(rn,2,display_action_parameter_name))
action_parameter_name2,MAX(decode(rn,2,action_parameter_value))
action_parameter_value2,
     MAX(decode(rn,3,display_action_parameter_name))
action_parameter_name3,MAX(decode(rn,3,action_parameter_value))
action_parameter_value3,
    MAX(decode(rn,4,display_action_parameter_name))
action_parameter_name4,MAX(decode(rn,4,action_parameter_value))
action_parameter_value4
 FROM ( SELECT batch_line_id,
               action_parameter_name,
       action_parameter_value,
       display_action_parameter_name,
               row_number() over (partition by batch_line_id
                          order by batch_line_id,
  action_parameter_name ) rn
          FROM (SELECT pbavl.element_name action_parameter_name,
               pbavl.parameter_name display_action_parameter_name,
       pblv.action_parameter_value,
       pblv.batch_line_id
FROM pay_task_parameters_vl pbavl,  pay_batch_line_values pblv
WHERE pbavl.base_task_parameter_id = pblv.action_parameter_id
AND pbavl.display_flag <> 'N' ) 
          )
GROUP BY batch_line_id) pivot_line_value_data,
                        pay_batch_lines pbl ,
                  pay_batch_headers pbh,
                        pay_bl_message_lines_vl pbmlv,
PAY_BL_TASK_ACTIONS_VL pbta,PAY_BL_TASKS_VL pbt
WHERE pivot_line_value_data.batch_line_id = pbl.batch_line_id
AND pbl.batch_id = pbh.batch_id
AND pbl.batch_line_id = pbmlv.source_id
AND pbl.batch_line_status = 'E'
AND pbh.batch_name like '%Pay%%%'
AND pbt.task_id=pbta.task_id
and pbl.task_action_id=pbta.task_action_id

Friday, 21 July 2017

Oracle Cloud Benefits - Person Change Life Event Fast Formula

Person Change Life Event Fast Formula

- Recently i had requirement of triggering life event in below cases.
(1) Change of working hours >= 30
(2) Change of working hours < 30
(3) Employees' termination or Death
(4) Salary Change

Below are the fast formulas that i have used to achieve the requirement.

(1)

/*****************************************************************************
FORMULA NAME: XX_CHG_WORKING_HOURS_GT_30
FORMULA TYPE: Person Life Changes

BEN_ASG_IN_NORMAL_HOURS  = New Value
BEN_ASG_IO_NORMAL_HOURS  = Old Value
*******************************************************************************/
INPUTS ARE BEN_ASG_IN_NORMAL_HOURS(text),BEN_ASG_IO_NORMAL_HOURS(text)

Default for BEN_ASG_IO_NORMAL_HOURS is '999'
Default for BEN_ASG_IN_NORMAL_HOURS is '999'

l_return = 'N'

IF (to_number(BEN_ASG_IO_NORMAL_HOURS) <> to_number(BEN_ASG_IN_NORMAL_HOURS) AND to_number(BEN_ASG_IO_NORMAL_HOURS)>= 30 AND to_number(BEN_ASG_IN_NORMAL_HOURS)< 30)
then
(l_return = 'Y') 
else 
(l_return = 'N')

return l_return


(2)


/*****************************************************************************

FORMULA NAME: XX_CHG_WORKING_HOURS_LT_30
FORMULA TYPE: Person Life Changes

*******************************************************************************/
INPUTS ARE BEN_ASG_IN_NORMAL_HOURS(text),BEN_ASG_IO_NORMAL_HOURS(text)

Default for BEN_ASG_IO_NORMAL_HOURS is '999'
Default for BEN_ASG_IN_NORMAL_HOURS is '999'

l_return = 'N'

IF (to_number(BEN_ASG_IO_NORMAL_HOURS) <> to_number(BEN_ASG_IN_NORMAL_HOURS) AND to_number(BEN_ASG_IO_NORMAL_HOURS)< 30 AND to_number(BEN_ASG_IN_NORMAL_HOURS)>= 30)
then
(l_return = 'Y') 
else 
(l_return = 'N')

return l_return


(3)

/*****************************************************************************

FORMULA NAME: XX_TERMINATION_LE
FORMULA TYPE: Person Life Changes

*******************************************************************************/
DEFAULT FOR PER_PER_ENTERPRISE_TERMINATION_ACTION_CODE is 'X'

L_RETURN='N'
IF (PER_PER_ENTERPRISE_TERMINATION_ACTION_CODE = 'TERMINATION' OR PER_PER_ENTERPRISE_TERMINATION_ACTION_CODE = 'RESIGNATION' OR PER_PER_ENTERPRISE_TERMINATION_ACTION_CODE ='DEATH' ) THEN
L_RETURN='Y'
RETURN L_RETURN

(4)

/*****************************************************************************

FORMULA NAME: XX_SALARY_CHG_LE
FORMULA TYPE: Person Life Changes

*******************************************************************************/
inputs are BEN_SAL_IN_SALARY_AMOUNT(text), BEN_SAL_IO_SALARY_AMOUNT(text)
default for BEN_SAL_IN_SALARY_AMOUNT is '99999999'
default for BEN_SAL_IO_SALARY_AMOUNT is '99999999'
l_create_ptnl = 'N'
If (TO_NUMBER(BEN_SAL_IO_SALARY_AMOUNT) <> TO_NUMBER(BEN_SAL_IN_SALARY_AMOUNT))
THEN
( l_create_ptnl = 'Y' )
ELSE
( l_create_ptnl = 'N' )
return l_create_ptnl


Saturday, 15 July 2017

50 US States

50 US States


  1.  California
  2. Oregon
  3. Washington
  4. Nevada
  5. Idaho
  6. Arizona
  7. New Mexico
  8. Texas
  9. Kansas
  10. North Dakota
  11. South Dakota
  12. Minnesota
  13. Iowa
  14. Missouri 
  15. ArKansas
  16. Louisiana
  17. Kentucky
  18. Tennessee
  19. Colorado
  20. Mississippi 
  21. Georgia  
  22. Florida
  23. Virginia
  24. West Virginia
  25. North Caroliana
  26. South Caroliana
  27. New jersey
  28. New York
  29. Vermont
  30. Maine
  31. Pennsylvania
  32. Rhode Island
  33. Hawaii
  34. Alaska
  35. Wyoming
  36. Michigan
  37. Illinois
  38. Wisconsin
  39. Ohio
  40. Maryland
  41. Massachusetts 
  42. Montana
  43. Utah
  44. Oklahoma
  45. Nebraska
  46. Indiana
  47. Alabama
  48. Delaware
  49. Connecticut
  50. New Hampshire

Puerto Rico - Union Territory (Important One , there are total 5)
Washington D.C. - Federal District

Saturday, 21 January 2017

Fusion HCM New Hire Process - USA


Important Points -

(1) Address

All employees attached to a payroll must have a home address throughout their period of employment.Also, if you enter the ZIP Code first, the city, state, and county fields are automatically populated.

(2) Marital Status, Ethnicity, and Veteran fields in the Legislative Information section

Note: The Ethnicity and Veteran fields are required for EEO and VETS reporting.

(3)  On the Employment Information page, provide the necessary work relationship, payroll relationship, assignment, job, manager, payroll, and salary details.

Note: Use the Payroll Details section to associate a TRU and payroll with the employee. If you opt not to, this employee would not automatically receive an Employee Withholding Certificate, and you would have to create it manually. See Manual Tax Card Creation for more instructions.
.
Once a TRU is attached to an employee, the W-4 Federal Tax Card is generated. The association to the TRU is also generated. Additionally, the US taxation element is automatically added to the employee’s element entry once the association to the TRU is done. This tax card is not created for HR-only customers.

= = = = = =

Verifying Employee New Hire Status in Work Relationship Details

When hiring or rehiring employees, the New Hire Status field indicates whether they are to be included or excluded from new hire reporting. Find this field in the Work Relationship Details of the Employment Information page.

Field Name Description :
New Hire Status

- Identifies the employee’s employment status as pertains to the New Hire report:

Different Values

(1) Include in the New Hire report :Employee is to be included in the next run of the New Hire Report.
(2) Already reported: Employee has already been included in a previous run of the New Hire Report.
      The New Hire Report process automatically sets all included employees to this status upon                 completion in final mode.
(3) Excluded from the New Hire report : Employee is not included in the report.


= = = =

Adding a Second Assignment
To add an additional assignment to an employee’s employment information: 1. Follow steps 1 through 3 under Maintaining Employment Information above.
2. Select Edit > Update.
3. Enter an Effective Start Date (or accept the default).
4. Select Add Assignment.
5. Click OK.
6. Enter employment information.
7. Click Next.
8. Enter compensation details.
9. Click Next.
10. Add or delete roles as needed.
11. Click Next.
12. Review the information and click Submit.
13. Click Yes.

You can view and access the new assignment from the Employment Tree. The last assignment added is the one first displayed in the Manage Employment UI when it is initially accessed. The other assignments may be accessed using this tree hierarchy.

====

There are several factors that make up the payroll processing.
Taxation Within Fusion Payroll
Vertex provides all the statutory compliance for the Oracle Fusion Global Payroll engine, but it is important for you to understand how the payroll process handles US taxation.
Managing the Employee Withholding Certificate
The Employee Withholding Certificate is the default tax card. For most employees, it is created automatically during the New Hire process. The Employee Withholding Certificate provides information used in taxation. Items such as filing status, number of allowances, and exemptions from taxes are specified on the card. If no values are entered, during tax calculations, a default value of Single for filing status and zero allowances will be used.
Setting Up Automatic Tax Card Creation
To ensure that new workers get an Employee Withholding Certificate:
1. Set the PAYROLL_LICENSE process configuration parameter to either PAYROLL or PAYROLL_INTERFACE, as appropriate to your implementation.
2. Confirm that element eligibility has been created for the US Taxation element. This element is automatically added to employee’s element entry when the association to the Tax Reporting Unit is completed.
Manual Tax Card Creation
There are cases where an employee would not have their tax card automatically created, such as if they were loaded through the File Based Loader utility.
For these employees, to create an Employee Withholding Certificate:
1. Navigate to the Payroll Calculations work area.
2. Start the Manage Calculation Cards task.
3. Search for and select the person record.
4. Click Create.
5. Enter an appropriate Effective-As-of-Date, and select Employee Withholding Certificate for Name.
6. Enter employee information as appropriate at the Federal level.
7. Click Save.
8. Select the Regional link under the Component Groups tree.
9. Enter employee information as appropriate for the Regional level.
10. Click Save.
11. Select the Associations link under the Component Groups tree.
12. Under Associations, click Create.
13. Select the Tax Reporting Unit, and click OK.
14. Click Save. This creates the US Taxation Component and is displayed in the Calculation Component column after saving.
15. Under Association Details, click Create.
16. Select the Employment Terms or Assignment Number and the Calculation Component created in prior steps, and click OK.
17. Click Save. 
18. Upon tax card association creation, the following fields are autopopulated with default values on the federal-level employee withholding certificate and should be verified:
 State for Unemployment Calculation
 State for Disability Calculation
 Primary Work Address

Changing the TRU for an Assignment:

To change the TRU for a preexisting assignment on the Employee Withholding Certificate:

1. Navigate to the Payroll Calculations work area.
2. Select Manage Calculation Cards.
3. Search for and select the person record.
4. Click Employee Withholding Certificate.
5. Click Associations under the Component Groups tree.
6. Select the Tax Reporting Unit under Associations for which the assignment currently exists.
If the association for the TRU for the new assignment does not already exist, create it now.
7. Select the assignment number to change under Association Details.
8. Click Edit>Update.
9. Select the Calculation Component for the new TRU.
10. Click Save and Close.
This end dates the record for the assignment associated with the previous TRU and creates a new record for the new TRU.


Manage Tax Withholding in My Portrait

Employees can update their own withholding information in Portrait using the Manage Tax Withholding action: 

1. Select Manage Tax Withholding action in the left panel under Actions.
    This displays the Employee Withholding Certificate page.

2. Click Edit. This is available for both the federal and state level.

When the federal employee withholding certificate is accessed, the system displays the federal W-4 editable PDF form. For those states that do follow federal, the state name is stamped on the editable federal PDF form. For those states that do not follow federal, the specific state’s editable PDF form will be displayed. The employee can perform their updates on these forms for both federal and state withholding. When the form is submitted, the data is saved to the system. See Appendix C for information on accessing the PA Residency Certificate in My Portrait.

Tax Calculation:

Oracle Fusion Global Payroll automatically calculates your taxes when you perform a payroll run. The following describes the rules it uses when doing so.

Payroll Processing

When you perform a payroll run, the payroll process:

1. Determines the resident and work tax addresses based on the following hierarchy:

Address Type                                                 ---- Priority

Location address                                            ----   4
Location override address                              ----   3
Assignment-level location override               ----   3
Work at home flag = Yes                               ----   1 (overrides assignment, location override, and                                                                                           location)

Higher priorities override the lower ones.
The process derives the resident tax address from the home address, and the work tax address is derived from the work location or, if the work-at-home flag is enabled, it uses the home address.
2. Determines the related withholding status and any additional information from the tax calculation card.
3. Passes this information to Vertex for calculation.


Fair Labor Standards Concepts


Fair Labor Standards: Concepts


The Fair Labor Standards Act (FLSA), also known as the Federal Wage-Hour Law, is perhaps the most basic of all payroll and employment laws.

The FLSA:
  1. sets the minimum wage and overtime rates covered employees must receive for their work;
  2. requires record keeping by all covered employers;
  3. places restrictions on the types of work minors can do and the hours they can work; and
  4. mandates equal pay for equal work.
The FLSA does not:
  1. require employers to provide paid vacations, sick days, jury duty leave, holidays, lunch breaks, or coffee breaks, but some states require certain employers to provide paid sick leave to employees meeting specific requirements;
  2. regulate how often employees must be paid, or when they must be paid after employment termination (voluntary or involuntary); or
  3. restrict the hours that employees over 16 years of age may be required to work.
Generally, items not covered by the FLSA are regulated by state laws.
The FLSA does not require that wages be paid within a certain time after services are performed. However, federal courts have ruled that wages are "unpaid" unless they are paid on the employees' regular payday. Payment after that date violates the FLSA's minimum wage and overtime pay requirements.

Exempt Employees: No Overtime - Fix salary employees, computer professionals , white collar jobs

Non-Exempt Employees: minimum wage and overtime - hourly  employees , labor jobs 

State Wage and Hour laws may impose requirements beyond those found in the FLSA when defining an employee's exemption from minimum wage and overtime. For example, a state may have a salary requirement that is greater than the FLSA's weekly salary requirement.

Friday, 20 January 2017

Employee - Independent Contractor - Agency Contractor

As employer you hire a person as employee or independent contractor or agency employees 

Employee -

(1) Employer will be responsible for all withholding
(2) Employer would be responsible for W2
(3) Social security number is important 

Independent contractor - 

(1) No tax withholding
(2) If the paying more than $600 then report 1099-MISC
(3) W9 reporting - TIN(Tax Identification Number) is important - in case of failure or incorrect TIN - backup withholding  - 28% 
ex. $1000 earning then $280 withholding

Agency - 

(1) Nothing to pay to person/contractor - pay to agency 
(2) No reporting 
(3) no withholding 

= = = = = = = =

Social security number verification system - SSNVS

Taxpayer Identification Number (TIN) - verification system

= = = = = = = =

SS-8 - to hire as employee vs independent contractor 

Form 945 - 

Use this form to report withheld federal income tax from non payroll payments. Non payroll payments include:

- Pensions (including distributions from tax-favored retirement plans, for example, section 401(k), section 403(b), and governmental section 457(b) plans), and annuities;
- Military retirement;
- Gambling winnings;
- Indian gaming profits;
- Voluntary withholding on certain government payments;
- Backup withholding.






End-to-End Guide to Enterprise Shifts, Work Pattern Types and Work Pattern Template Rules in Oracle HCM

End-to-End Guide to Enterprise Shifts, Work Pattern Types and Work Pattern Template Rules in Oracle HCM Excerpt: This article demonstrate...