Running Select statement from diagnostics in Oracle Apps

You can see value of any profile using diagnostics in Oracle applications as shown in screenshot here

Delete An Assignment Change

Find what record you want to delete e.g. for following assignment I want to delete this record which is start from 01-Mar-2010

Change the effective date to one day before above record i.e. 28-mar-2010 by click on below shown icon in toolbar

After changing your effective date assignment form will show you previous record as shown here where your effective date to will be 28-Feb-2010

Click on delete icon in toolbar as shown in below screenshot

After click the delete icon it will show below message where click on OK button

Next screen will ask what delete you want to do. Depending on requirement you can

a) Next – It will delete next change only so if you have multiple future changes, it will delete only next one leaving other changes untouched

b) All – It will delete all the future changes done on assignment

c) Purge – It will completely delete this assignment

After selecting your option, press save icon in toolbar

Application Implementation Methodology

Project management methodology and implementation strategy.

AIM gives document templates which are useful for oracle application related project related projects and implementations. Here are some examples :-

AP010	Executive Project Strategy
AP010	Invitation Memorandum
AP010	Session Planning Checklist
AP020	Application Overview Customization
AP020	Invitation Memorandum
AP020	Project Team Orientation Plan
AP020	Session Planning Checklist
AP030	Project Team Learning Plan
AP040	Project Team Learning Environment
AP050	Project Team Learning Events Administration
AP060	Business Unit Managers Readiness Plan
AP060	High Level Systems Overview Customization
AP060	Invitation Memorandum
AP060	Session Planning Checklist
AP070	Invitation Memorandum
AP070	Project Readiness Roadmap
AP070	Session Planning Checklist
AP080	Communication Campaign
AP090	Application to Process Overview Customization
AP090	Invitation Memorandum
AP090	Managers Readiness Plan
AP090	Session Planning Checklist
AP100	Business Process Organizational Impact
AP110	Human Performance Support Systems
AP120	Information Technology Groups Alignment
AP130	User Learning Needs Analysis
AP140	Learning Plan
AP150	User Learningware
AP160	User Learning Environment
AP170	User Learning Events Administration
AP180	Effectiveness Assessment
AP180	Invitation Memorandum
AP180	Session Planning Checklist
BP010	Business and Process Strategy
BP020	Change Catalog
BP030	Data Gathering Requirements
BP040	Current Process Model
BP050	Leading Practice Review
BP060	High Level Process Vision
BP070	High Level Process Designs
BP080	Future Process Model
BP090	Business Procedure Documentation
BR010	High Level Gap Analysis
BR020	Configured Mapping Environment Architecture and Configuration
BR030	Business Requirements Mapping Form
BR050	Integration Fit Analysis
BR060	Information Model
BR080	Business Mapping Test Results
BR100Aap	Accounts Payable Application Setup
BR100Aar	Accounts Receivable Application Setup
BR100Aas	Sales and Marketing Application Setup
BR100Abom	Bill Of Materials Application Setup
BR100Aca	Common Applications Application Setup
BR100Acas	Common Applications System Admin Application Setup
BR100Ace	Cash Management Applications Setup
BR100Acf	Common Financials Application Setup
BR100Acm	Common Manufacturing Application Setup
BR100Acn	Sales Compensation Application Setup
BR100Acrp	Capacity Application Setup
BR100Acs	Service Application Setup
BR100Acst	Cost Management Application Setup
BR100Aeng	Engineering Application Setup
BR100Afa	Fixed Assets Application Setup
BR100Afi	OPM Financials Application Setup
BR100Agl	General Ledger Application Setup
BR100Ahr	Human Resources Application Setup
BR100Aic	OPM Inventory Application Setup
BR100Ainv	Inventory Application Setup
BR100Alo	OPM Logistics Application Setup
BR100Amrp	Master Scheduling MRP Application Setup
BR100Aoe	Order Entry Application Setup
BR100Apa	Project Accounting Application Setup
BR100Apd	OPM Product Development Application Setup
BR100Apo	Purchasing Application Setup
BR100App	OPM Process Planning Application Setup
BR100Aqa	Quality Application Setup
BR100Ascp	Supply Chain Planning Application Setup
BR100Ass	Supplier Scheduling Application Setup
BR100Asy	OPM Systems Application Setup
BR100Awip	Work in Process Application Setup Document
CM010	Configuration Management Procedures
CM020	Document Index
CM020	Document Update Notice
CM030	Configuration Item Index
CM030	Configuration Item Status Record
CM040	Release Note
CR010	Project Management Plan
CR010	Project Start Up Checklist
CR020	Control and Reporting Procedures
CR040	Risk and Issue Form
CR040	Risk and Issue Log
CR050	Problem Report Form
CR050	Problem Report Log
CR060	Change Request Form
CR060	Change Request Log
CR070	Meeting Minutes
CR070	Project Progress Report
CR080	Acceptance Certificate
CR080	Client Satisfaction Report
CR080	End Report
CV010	Data Conversion Requirements and Strategy
CV020	Conversion Standards
CV030	Conversion Environment
CV040	Conversion Data Mapping
CV050	Manual Conversion Procedures
CV060	Conversion Program Designs
CV070	Conversion Test Plans
CV120	Installed Conversion Programs
CV130	Converted and Verified Data
DO010	Documentation Requirements and Strategy
DO020	Documentation Standards and Procedures
DO030	Glossary
DO040	Document Environment
DO060	User Reference Manual
DO070	User Guide
DO080	Technical Reference Manual
DO090	System Management Guide
MD010	Application Extension Strategy
MD020	Application Extension Definition And Estimates
MD030	Design Standards
MD040	Build Standards
MD050	Application Extensions Functional Design
MD060	Database Extensions Design
MD070	Application Extensions Technical Design
MD090	Development Environment
MD120	Installation Instructions
PJM01	Engagement Summary
PJM02	Project Memo
PJM03	Meeting Agenda
PJM04	Rejection Note
PJM05	Supplier Assessment Record
PJM06	Project Document Generic
PJM07	Phase End Report
PJM08	Site Visit Report
PJM09	Project Management Checklist
PJM10	Quality Management Checklist
PM010	Transition Strategy
PM020	Production Support Infrastructure Design
PM030	Transition and Contingency Plan
PM040	Installation QA Checklist
PM090	System Performance Assessment
PM130	Business Direction Recommendations
PM140	Technical Direction Recommendations
PT010	Performance Testing Strategy
PT020	Performance Test Scenarios
PT030	Performance Test Transaction Models
PT040	Performance Test Scripts
PT050	Performance Test Transaction Program Designs
PT060	Performance Test Data Design
PT070	Performance Test Database Load Program Designs
PT110	Performance Test Environment
PT120	Performance Test Results
PT130	Performance Test Report
QM010	Quality Management Procedures
QM020	Review Comments List
QM020	Review Leader Form
QM030	Audit Action Form
QM030	Audit Report
QM040	Metrics Report
QM045	Project Healthcheck Checklist
QM050	Quality Report
RD010	Current Financial and Operating Structure
RD020	Current Business Baseline
RD030	Process and Mapping Summary
RD040	Business Volumes and Metrics
RD050	Business Requirements Scenarios
RD060	Audit and Control Requirements
RD070	Business Availability Requirements
RM010	Resource Management Procedures
RM020	Assignment Request
RM020	Staffing and Organization Plan
RM025	Project Orientation Guide
RM030	Staff Training Plan
RM040	Physical Resource Plan
RM040	Service Level Agreement
RM050	Incoming Item Record
RM050	Installation Plan and Record
RM060	Assignment Terms of Reference
RM070	Equipment Fault Record
TA010	Architecture Requirements and Strategy
TA020	Current Technical Architecture Baseline
TA030	Preliminary Conceptual Architecture
TA040	Application Architecture
TA050	System Availability Strategy
TA060	Reporting and Information Access
TA080	Application Security Architecture
TA090	Application and Database Server Architecture
TA100	Architecture Subsystems Proposal
TA110	System Capacity Plan
TA120	Platform and Network Architecture
TA130	Application Deployment Plan
TA140	Performance Risk Assessment
TA150	System Management Procedures
TE010	Testing Requirements and Strategy
TE020	Unit Test Script
TE030	Link Test Script
TE040	System Test Script
TE050	Systems Integration Test Script
TE060	Testing Environment Setup Log
TE110	Test Report for System Test
TE120	Test Report for Systems Integration Test
TE130	Test Report for Acceptance Test
WM010	Work Management Procedures

Element Set - Not showing in Payroll Process

This morning, I created a element set of type Run set in order to restrict
elements which are processed in payroll run. I included following element
classification rules
1) Earnings
2) Supplemental Earnings
3) Imputed Earnings
4) Non-Payroll Payments
5) Tax Deductions

Because only taxes should be deducted so no other deduction classifications
were included.

When I was submitting Payroll Run, element set name LOV wasn't showing
my newly created element set. So I tried to debug the situation and found
that LOV is running following query behind the scene.

SELECT element_set_name
FROM pay_element_sets e, per_business_groups_perf p
WHERE ( (e.business_group_id = p.business_group_id)
OR (e.legislation_code = p.legislation_code)
OR (e.business_group_id IS NULL AND e.legislation_code IS NULL)
)
AND element_set_type = 'R'
AND EXISTS (
SELECT NULL
FROM pay_ele_classification_rules ecr, pay_element_types_f pet
WHERE ecr.element_set_id = e.element_set_id -- element set ID
AND pet.classification_id = ecr.classification_id
AND pet.element_name = 'VERTEX'
AND NOT EXISTS (
SELECT NULL
FROM pay_element_type_rules etr
WHERE etr.element_set_id = e.element_set_id
AND etr.element_type_id = pet.element_type_id
AND etr.include_or_exclude = 'E')
UNION
SELECT NULL
FROM pay_element_type_rules etr, pay_element_types_f pet
WHERE etr.element_set_id = e.element_set_id
AND etr.element_type_id = pet.element_type_id
AND pet.element_name = 'VERTEX'
AND etr.include_or_exclude = 'I')
AND p.business_group_id = :$profiles$.per_business_group_id
ORDER BY e.element_set_name

So you have to include information element classification in order to include
your element set in payroll run process.

Exxor - Adjust amount for EFT

First let me give some introduction about Exxor tool. Exxor is a
third party tool which works on top of Oracle application. This
tool is used to create third party payment EFT file and then create
invoices in AP module. You can maintain your garnishment element
entries also with this tool.

Last payroll at my client place, was the third payroll in month (its a
Bi-weekly payroll). Employee should not have any garnishment or
child support or chapter 13 this pay period but fast formula get
broke and deductions were created in payroll. As these transactions
were direct deposit so money went to bank and can't be returned.

So we decided not to deduct amount next pay period and balance it.
In order to do that we have to adjust amount in Exxor as shown below














After making all the adjustments, you will run EFT creation process
which might error out because of employees with $0 amount. But EFT
file should be created with correct transactions.

Please let me know if someone wants more information about this problem.

SOE form not showing Check Number

The statement of earning form is showing NULL in Check Number
field even though payment details exists as shown below





















In that case check the payment methods detail for assignment,
there should be some payment method which has prenote date + 10
greater than period end date. As the SOE form is not checking
effective date so any payment method could cause this issue.

Over Limit Report

The Over Limit Report identifies employees who have taxes withheld
in excess of the legal limit. This report checks the following balances
for compliance:

• 401(k), 403(b), 457, and the associated catchups
• Roth 401(k) and Roth 403(b) withholding
• Social Security Taxable Balance (employee and employer)
• FUTA Taxable Balance
• SUI Taxable Balance (employee and employer)
• SDI Taxable Balance (employee and employer)

Note: The report does not check SDI balances for Hawaii and New
York.

Run the Over Limit Report as of a specific date either for a single
organization or location within a GRE or business group, or for all
of the organizations or locations in a GRE or business group. You
report on a single type of balance or include all of the balances in
the report.

Note: Once you identify the corrections needed, adjust the necessary
employee balances to comply with the legal limit.

Unacceptable Tax Balance Report

The Unacceptable Tax Balance Report identifies employees with
potential incorrect withheld amounts for the following taxes:

• Federal Tax:- FIT, Medicare, SS, FUTA
• State Tax:- SIT, SUI, SDI
• Local Income Tax (LIT)

This report identifies incorrect withheld on following conditions

Tax Type	Possible Warning Messages
FIT	FIT gross earnings <> FIT reduced subject whable
	FIT reduced subject whable = 0 and FIT withheld > 0
	FIT subject whable <= 0 and FIT withheld > 0
	FIT subject whable <> FIT reduced subject whable
	FIT gross earnings not = Medicare taxable + Section 125 pretax + Dependent care +401k pretax
FUTA	FIT gross earnings <> FUTA taxable
Medicare	FIT gross earnings <> Medicare EE taxable
	Medicare EE withheld not equal to Medicare ER liability
	FIT gross earnings not equal to Medicare taxable + Section 125 pretax + Dependent care +401k pretax
Social Security	FIT gross earnings <>Medicare EE taxable
	SS EE withheld not equal to Medicare ER liability
SIT	SIT subject whable <= 0 and SIT withheld > 0
	FIT subject whable <= 0 and SIT withheld > 0
	SIT subject whable <>SIT reduced subject whable
	FIT subject <> SIT Subject
	Ensure SIT Withheld = 0 when State has no SIT withheld rule
SUI	SUI EE/ER sbject whable <=0 and SUI EE/ER witheld > 0
	FIT gross earnings <> SUI ER taxable
	FIT subject whable <= 0 and SDI EE/ER withheld > 0
SDI	FIT gross earnings <> SDI EE taxable
	SDI EE subject whable = 0 and SDI EE withheld > 0
	FIT subject whable <= 0 and SDI EE/ER withheld > 0
LIT	LIT subject = 0 and LIT withheld > 0
	FIT subject whable <= 0 and LIT withheld > 0
	LIT subject whable <>LIT reduced sbject whable

SS Withheld does not = 6.2% of Taxable Balance
Medicare Withheld does not = 1.45% of Taxable Balance
FUTA Withheld does not = X% of ER FUTA Liability (X = value derived from JIT)
SUI Withheld does not = X% of SUI ER Liability (X = value derived from JIT)
SDI Withheld does not = X% of SDI Liability (X = value derived from JIT)

This report is really helpful at year end and should be run. All the error messages in report output should be resolved before running year end preprocess.

Year End Process

Various Forms required quarterly or at Year End
--------------------------------------------------

W-2
-----
Wage and Tax Statement
Employers must report to their employees the amounts paid and
withheld so the employees can complete their own income tax
returns and pay any amount owed to the Internal Revenue Service
and state and local governments.

Form 940
-----------
Employer's Annual Federal Unemployment (FUTA) Tax Return
Employers use Form 940 to determine your FUTA taxable wages for the calendar year
and the FUTA tax liability on those wages after accounting for applicable state
unemployment tax credits and FUTA tax deposits made during the year

Form 941
----------
Employer's Quarterly Federal Tax Return
Employer's total taxable wages paid and payroll tax liability, which is then reconciled
with the employer's record of tax deposits and wage and tax information provided to
employees on their W-2 forms.

Form 1099-R
--------------
Employers who make distributions of retirement income from qualified
plans are required to report those distributions and any amount withheld for
FIT/SIT/LIT on form 1099-R.
Form 1099-R requires distributions from any of the following be reported:
• Pensions
• Annuities
• Retirement or Profit sharing plans
• IRAs
• Insurance Contracts

State Quarterly Wage listing
----------------------------
At the state level, employer's electronically submit report of employee wages subject to
SUI to the states in which they have employees. Oracle payroll provide a report named state quarterly wage listing which produce this data in correct format as per state. You can compare this report with Federal/State remittance report to reconcile.

Usually year end processing has following process

1) Running pre-archival reports
2) Fixing the balance issues if there any
3) Running state quarterly wage listing process
4) Running year end pre-process.
5) Running post-archival reports.
6) Generating government mandated reports

Pre-Archival Reports
-------------------------
These reports are divided into three categories
a) Diagnostic Reports
b) Balancing Reports
c) Reconciliation Reports

Diagnostic Reports
-----------------------
Unacceptable Tax balance Report :- Identifes wrong withheld amount for various tax categories. e.g. FIT, SIT, LIT, SUI, SDI, FUTA, SS, Medicare etc.

Invalid Address Report:- list of employees who has wrong primary address

Over limit Report:- show employees with taxes withheld more than legal limit or wages in excess to wage limit or salary deferrals more than annual limit.

Balancing Reports
-------------------
GRE Total Report:- List total for all GREs or mentioned one. This report help to reconcile with W-2s

Tax Remittance Report :- Shows federal, state and local taxable wages and taxes summerized for date range

Reconciliation Reports
-----------------------
Employee Run Results Report :- shows problem with individual employees balances

Payroll Activity Report :- Shows employee and group-level payroll details for pay runs, quick pays, balance adjustments, balance initializations, voided payments, and reversals.

Federal/State Remittance Report

This report shows taxable wages and withheld for following categories
1) Federal
2) Medicare (Employee and Employer)
3) Social Security (Employee and Employer)
4) State
5) SUI (Employee and Employer)

RDF file behind this report is PAYUSFTR.rdf under $PAY_TOP/reports/US directory.

The report calculates wages and withheld based on whether Federal/State run balances are valid or not.

If balances are not valid then use package: pay_us_taxbal_view_pkg.us_gp_gre_jd_ctd otherwise uses view pay_us_state_tax_bal_gre_v which in turn calls pay_us_tax_bals_pkg.us_tax_balance.

This view use two tables
1) pay_balance_sets which provides the ability to group related balances for reporting purposes
2) PAY_US_ASG_REPORTING : denormalized table used to improve the performance of some US balance reports. This table is maintained through the Employee Tax Information form(PAYEETAX.fmb) and the assignment form. It's primary purpose is to speed access to balances by filtering the assignments based first on tax unit id and then on jurisdiction code.

The balances used in this report are group level balances with dimension GRE in JD Run. The following query you can use to check whether balances are correct or not.

SELECT prb.*
FROM pay_run_balances prb,
pay_defined_balances pdb,
pay_balance_dimensions pbd,
pay_balance_types pbt
WHERE prb.defined_balance_id = pdb.defined_balance_id
AND pdb.balance_type_id = pbt.balance_type_id
AND pdb.balance_dimension_id = pbd.balance_dimension_id
AND pbt.balance_name = 'SUI ER Taxable'
AND pbd.dimension_name = 'GRE in JD Run'
and prb.effective_date = '05-Mar-2009'

If balances are not correct then run the process Generate Run balances process with particular balance and dimension.