Saturday, 21 February 2026

U.S. Retiree Payroll

U.S. Retiree Payroll - Year-End Checklist for Retirees: A Long Guide for U.S. Employers, HR & Payroll

Disclaimer: This guide is general educational information and not legal or tax advice. Requirements vary by plan documents, vendor setup, and state law. Confirm decisions with your payroll provider, third-party administrator (TPA), benefits broker, ERISA counsel, and/or CPA.


Who this guide is for

This is written for employers, HR, payroll, and benefits administrators who manage retiree-related payments and need clarity on what “year-end” obligations exist—especially in environments with pension distributions, retiree health coverage, and occasional post-retirement consulting work.

“Retiree” can mean different things in practice:

  • A former employee receiving pension/annuity benefits
  • A former employee taking distributions from a 401(k)/403(b)/457 plan
  • A former employee covered under retiree medical or COBRA continuation
  • A former employee who is truly separated with no ongoing benefits
  • A retiree who returns for project work as a consultant or rehired employee

1) First principles: Do you “have to pay retirees” in the U.S.?

There is no general rule that employers must keep paying people just because they retired. What matters is whether you have:

  • Wage obligations (final pay, PTO payouts depending on state law/policy)
  • A retirement plan that pays benefits (defined benefit pension or cash balance plan)
  • Post-employment benefits you promised (retiree medical subsidy, severance, union CBA terms, etc.)

Practical takeaway: Year-end tasks are driven by what you paid and what you provided during the calendar year—not by the label “retiree.”


2) What jobs commonly provide pensions (and why it matters)

In U.S. benefits language, a “pension” usually means a Defined Benefit (DB) plan (a promised benefit, often monthly, based on a formula). DB pensions are most common in:

  • Government (state/local public employees, teachers, police/fire)
  • Federal employment (often a pension component plus a 401(k)-style plan)
  • Military (career service pension-style benefits)
  • Union/collectively bargained roles (some trades and legacy industries)
  • Legacy private-sector employers (select utilities/transportation/older employers)

Why HR/payroll should care: Pension/plan payments are typically reported on Form 1099-R (often issued by a plan administrator/recordkeeper/TPA). Wages are reported on Form W-2. Consulting work may be reported on W-2 or 1099-NEC depending on worker classification.


3) The master “year-end” decision tree

Answer these questions to identify what you need to issue:

  1. Did we pay the retiree any wages this year? (final paycheck, PTO payout treated as wages, bonus, post-retirement rehiring)
  2. Did the retiree receive retirement distributions? (pension payments, 401(k) distributions, rollover distributions)
  3. Did we provide health coverage to retirees? (retiree medical plan, employer-subsidized coverage, COBRA)
  4. Do retirees remain plan participants/beneficiaries? (ERISA disclosures, annual fee disclosures, Summary Annual Report)
  5. Did any retirees provide services as consultants? (independent contractor vs employee)

From that tree, your outputs are typically:

  • W-2 (if wages)
  • 1099-R (if retirement distributions)
  • 1099-NEC (if independent contractor payments for services)
  • ACA health coverage statements (1095-C and/or 1095-B depending on employer/plan type)
  • Annual plan disclosures (SAR, fee disclosures, and other notices as applicable)

4) If you paid wages: W-2 (and related payroll hygiene)

When a retiree needs a W-2

A retiree receives a Form W-2 if they were paid taxable wages through payroll during the year, such as:

  • Final wages / last paycheck
  • Bonus or incentive paid after retirement
  • PTO/vacation payout treated as wages (depends on state law and written policy)
  • Post-retirement wages for part-time employment (rehire, seasonal, project employee)

Operational checklist

  • Confirm the person’s mailing address and electronic delivery consent (if used).
  • Confirm the correct work and resident state taxation setup (retirees often relocate).
  • Make sure separation/rehire actions are clean so benefits deductions stop/transition correctly.

5) If you paid retirement distributions: Form 1099-R

What 1099-R is

Form 1099-R reports distributions from retirement arrangements: pensions, annuities, retirement plans (401(k)/403(b)/457), IRAs, and similar vehicles. It is not used for wages or service/consulting payments.

Who actually issues the 1099-R?

In most organizations, HR/payroll does not manually generate 1099-R. Typically:

  • 401(k)/403(b)/457 plans: recordkeeper/TPA issues 1099-R
  • Defined benefit pensions: plan administrator/TPA issues 1099-R
  • Annuity contracts: insurance carrier issues 1099-R

Employer role: coordinate with the provider, validate addresses, confirm distribution categories (rollovers vs taxable), and ensure withholding data is correct.


6) How to read Form 1099-R: box-by-box explanation

Box 1 — Gross distribution

Total amount paid out during the year before taxes withheld. Includes taxable and nontaxable portions.

Box 2a — Taxable amount

The portion of Box 1 that is taxable income.

Box 2b — Checkboxes

  • Taxable amount not determined: payer did not calculate taxable amount; recipient must determine.
  • Total distribution: indicates the account/contract was fully distributed.

Box 4 — Federal income tax withheld

Federal withholding taken out of the distribution.

Box 5 — Employee contributions / insurance premiums (basis)

After-tax basis that is generally non-taxable and reduces the taxable amount.

Box 7 — Distribution code(s)

Identifies the distribution type. Common codes:

  • 7 = Normal distribution
  • 4 = Death (beneficiary/estate)
  • G = Direct rollover
  • 1 = Early distribution (no known exception)
  • 2 = Early distribution (exception applies)
  • 3 = Disability

10-second read: Box 1 = total paid → Box 2a = taxable → Box 4 = withholding → Box 7 = what kind of payout.


7) What are annuities (and how they show up in retiree administration)?

An annuity is a contract (often with an insurance company) designed to convert money into future income. Many pensions pay benefits in annuity form (monthly income, potentially for life).

Common annuity types

  • Immediate: pay in, income starts soon
  • Deferred: pay in, income starts later
  • Fixed: predictable payment/return
  • Variable: payment depends on investments; often higher complexity/fees
  • Indexed: linked to an index with caps/limits

Payout options you may see

  • Single life (lifetime)
  • Joint & survivor (continues for spouse/beneficiary)
  • Period certain (guaranteed number of years)

8) NEW: Retirees doing consulting after retirement — W-2, 1099-NEC, or 1099-R?

This is a very common situation in cities and other large employers: retirees return for project work, seasonal work, advisory roles, or “consulting.” The reporting depends on what the payment is for:

Key rule: 1099-R is only for retirement distributions

Never use 1099-R to report pay for consulting or services. A 1099-R is strictly for pension/annuity/retirement plan distributions.

A) If the retiree is treated as an employee: issue a W-2

Use Form W-2 if the retiree is working under an employment relationship (even if temporary). Practical indicators include:

  • The city controls how the work is done (hours, methods, supervision)
  • The worker uses city equipment/systems and is integrated like staff
  • The work looks like a staff role, not a deliverable-based engagement

Operationally: pay through payroll, apply normal withholding/tax processing, and issue a W-2.

B) If the retiree is a true independent contractor: issue a 1099-NEC

Use Form 1099-NEC if the retiree is a non-employee contractor (typically a deliverables-based engagement, more independence in how work is performed, uses their own tools, etc.).

Operationally:

  • Collect a Form W-9 before payment (legal name + TIN).
  • Pay via Accounts Payable (not payroll).
  • Issue 1099-NEC for reportable nonemployee compensation once thresholds and rules apply.

C) Very common: the same person receives both 1099-R and W-2 or 1099-NEC in the same year

A retiree can receive:

  • 1099-R for pension/retirement distributions, and
  • W-2 or 1099-NEC for consulting/work payments

These are different payment types and can coexist without issue when correctly classified.

Risk note (classification)

Don’t choose W-2 vs 1099-NEC based only on convenience. Worker classification depends on the facts (degree of control, financial arrangement, relationship). Misclassification can create tax and compliance risk.


9) Retiree health coverage & ACA reporting: what might be required

Health coverage reporting is where many year-end retiree questions live. The right forms depend on:

  • Whether the employer is an Applicable Large Employer (ALE) under the ACA
  • Whether retiree coverage is fully insured (carrier) or self-insured
  • Whether the retiree is on COBRA continuation

Operational tips:

  • Confirm who is responsible: employer vs carrier vs TPA.
  • Reconcile who was covered in which months (especially if someone retired mid-year).
  • Update addresses (retirees move often).

10) COBRA and retirement (not a “year-end” form, but frequently missed)

If retirement triggers loss of group health coverage, COBRA continuation may apply (depending on plan/employer circumstances). COBRA issues often surface months later, so cities include it in retiree offboarding checklists.


11) Annual ERISA plan disclosures that may still apply to retirees

If retirees remain plan participants/beneficiaries, annual disclosures may still be required depending on plan design and governance. Common examples:

  • Summary Annual Report (SAR)
  • Annual fee/investment disclosures for participant-directed plans
  • Other notices depending on the plan’s funding and structure

12) “Who does what?”: clean division of responsibilities

Payroll typically owns

  • W-2 delivery and wage/tax reconciliation
  • Final pay timing and state compliance for wages
  • Address validation and year-end mailing logistics

Benefits/HR typically owns

  • Retiree eligibility and enrollment (retiree medical, COBRA, dependent changes)
  • Coordination with broker/TPA/carrier for coverage and reporting
  • Offboarding/rehire processes (especially when retirees return to work)

TPA/Recordkeeper/Carrier typically owns

  • 1099-R issuance for retirement distributions
  • Plan-level reporting and participant notices (depending on contract)
  • Distribution processing and withholding elections administration

13) Sample retiree year-end checklist (copy/paste)

  • Data hygiene: validate retiree addresses, emails (if e-delivery), beneficiary contacts
  • W-2 audit: confirm any wages (rehire/bonus/PTO payout) and correct state taxation
  • Consulting classification audit: confirm who is W-2 vs 1099-NEC and ensure W-9s are collected for contractors
  • Retirement plan audit: confirm 1099-R production timeline with recordkeeper/TPA
  • Distribution categorization: rollovers vs taxable vs death benefits; confirm Box 7 coding logic
  • Withholding audit: federal and state withholding elections captured correctly for pensions
  • Health coverage reconciliation: coverage months by person; confirm who issues ACA statements
  • COBRA compliance: confirm election notices sent for qualifying events
  • Plan disclosures: confirm SAR/fee disclosures distributed where required
  • Support readiness: prepare an FAQ for retiree inquiries (“When do I get my forms?”, “Why is Box 2a blank?”, “What is Code 7?”)

14) FAQ for HR/payroll teams

Q1: If we don’t have a pension, do we still issue 1099-R?

Possibly. If retirees took distributions from your 401(k) or other plan, a 1099-R is still issued, typically by the recordkeeper/TPA.

Q2: Can we issue a W-2 and a 1099-R to the same person?

Yes. If they received wages and retirement distributions in the same year, they can receive both.

Q3: Our retiree is “consulting” — should we just use 1099-R?

No. 1099-R is for retirement distributions only. Consulting/services is reported as W-2 (employee) or 1099-NEC (independent contractor).

Q4: Why would Box 2a (taxable amount) be blank on 1099-R?

Sometimes the payer doesn’t determine the taxable amount and checks “Taxable amount not determined.” The recipient determines taxable income based on basis and other factors.

Q5: What’s the most common operational cause of retiree year-end problems?

Address changes and timing mismatches (retirement mid-year, coverage changes, distribution changes). The fix is disciplined reconciliation and clear division of responsibility with providers.


15) If you want to tailor this guide to your city’s Oracle EBS setup

If you want a version customized to your Oracle EBS implementation, gather:

  • Do you run a separate Pension Payroll (payroll name, period type, consolidation set)?
  • Who issues 1099-R (EBS, custom report, or tax vendor)?
  • Do retirees return as rehired employees or independent contractors (and who decides classification)?
  • Do you deduct retiree health premiums from pension checks?

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


Friday, 3 April 2015

Oracle Payroll Costing hierarchy

Oracle Payroll Costing Hierarchy


In oracle payroll costing can be set at 5 levels with below priority order.


- Payroll                    = Lowest
- Element Link
- Organization
- Assignment
- Element Entry        = Highest



Thursday, 14 August 2014

Query for attachments in oracle apps





You can get attached_document_id of approximate all applications transaction as shown below.

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

SELECT  fad.attached_document_id         attached_document_id
FROM    applsys.fnd_attached_documents   fad
WHERE   fad.entity_name                  = 'R_DOCUMENT_EXTRA_INFO'
AND     FAD.PK1_VALUE                    IN(SELECT  HDEI.DOCUMENT_EXTRA_INFO_ID  DOCUMENT_EXTRA_INFO_ID
                                            FROM    hr.hr_document_extra_info HDEI
                                           )
UNION
SELECT fad.attached_document_id          attached_document_id
FROM   applsys.fnd_attached_documents    fad
WHERE  fad.entity_name                   = 'PQH_SS_ATTACHMENT'
AND    fad.pk1_value                     IN(SELECT hat.transaction_id      transaction_id
                                            FROM   hr.hr_api_transactions  hat
                                            )
UNION
SELECT fad.attached_document_id          attached_document_id
FROM   applsys.fnd_attached_documents    fad
WHERE  fad.entity_name                   = 'AP_INVOICES'
AND    fad.pk1_value                     IN(SELECT aia.invoice_id      invoice_id
                                            FROM   ap.ap_invoices_all  aia
                                            )
UNION
SELECT fad.attached_document_id          attached_document_id
FROM   applsys.fnd_attached_documents    fad
WHERE  fad.entity_name                   IN ('PO_HEAD','PO_HEADERS')
AND    fad.pk1_value                     IN (SELECT pha.po_header_id   header_id
                                             FROM   po.po_headers_all  pha
                                             )
UNION
SELECT fad.attached_document_id           attached_document_id
FROM   applsys.fnd_attached_documents     fad
WHERE  fad.entity_name                    = 'OIE_HEADER_ATTACHMENTS'
AND    fad.pk1_value                      IN(SELECT  arha.report_header_id            report_header_id
                                             FROM    ap.ap_expense_report_headers_all arha
                                          )
UNION
SELECT fad.attached_document_id       attached_document_id
FROM   applsys.fnd_attached_documents fad
WHERE  fad.entity_name                = 'OKC_CONTRACT_DOCS'
AND    fad.pk2_value                  IN(SELECT ocd.business_document_id
                                         FROM   okc.okc_contract_docs ocd
                                          )
UNION
SELECT  fad.attached_document_id       attached_document_id
FROM    applsys.fnd_attached_documents fad
WHERE   fad.entity_name                = 'PO_LINES'
AND     fad.pk1_value                  IN(SELECT pla.po_line_id   po_line_id
                                          FROM   po.po_lines_all  pla            
                                          )
UNION
SELECT fad.attached_document_id        attached_document_id
FROM   applsys.fnd_attached_documents  fad
WHERE  fad.entity_name                 = 'OIE_LINE_ATTACHMENTS'
AND    fad.pk1_value                   IN(SELECT arla.report_header_id          report_header_id
                                          FROM   ap.ap_expense_report_lines_all arla
                                          )
UNION
SELECT fad.attached_document_id       attached_document_id
FROM   applsys.fnd_attached_documents fad
WHERE  fad.entity_name                = 'REQ_HEADERS'
AND    fad.pk1_value                  IN(SELECT prha.requisition_header_id    requisition_header_id
                                         FROM   po.po_requisition_headers_all prha
                                         )
UNION
SELECT fad.attached_document_id        attached_document_id
FROM   applsys.fnd_attached_documents  fad
WHERE  fad.entity_name                 ='REQ_LINES'
AND    fad.pk1_value                   IN(SELECT prla.requisition_line_id     requisition_line_id
                                          FROM   po.po_requisition_lines_all  prla
                                          )
UNION
SELECT fad.attached_document_id         attached_document_id
FROM   applsys.fnd_attached_documents   fad
WHERE  fad.entity_name                  ='PO_SHIPMENTS'
AND    fad.pk1_value                    IN(SELECT plla.line_location_id     line_location_id
                                           FROM   po.po_line_locations_all  plla
                                           )
UNION
SELECT fad.attached_document_id        attached_document_id
FROM   applsys.fnd_attached_documents  fad
WHERE  fad.entity_name                 = 'IBY_PAY_INSTRUCTIONS_ALL'
AND    fad.pk1_value                   IN(SELECT pia.payment_instruction_id     payment_insruction_id
                                          FROM   iby.iby_pay_instructions_all   pia
                                          )
UNION
SELECT fad.attached_document_id        attached_document_id
FROM   applsys.fnd_attached_documents  fad
WHERE  fad.entity_name                 = 'PER_ABSENCE_ATTENDANCES'
AND    fad.pk1_value                   IN(SELECT to_char(paa.absence_attendance_id) absence_attendance_id
                                          FROM   hr.per_absence_attendances         paa
                                          )
UNION
SELECT fad.attached_document_id        attached_document_id
FROM   applsys.fnd_attached_documents  fad
WHERE  fad.entity_name                 = 'XX_LOOKUP_DOCUMENT_ENTITY'
AND    fad.pk2_value                   LIKE '%BBW%%'
UNION
SELECT fad.attached_document_id        attached_document_id
FROM   applsys.fnd_attached_documents  fad
WHERE  fad.entity_name                 = 'PER_PEOPLE_F'
AND    fad.pk1_value                   IN(SELECT asg.person_id             person_id
                                          FROM   hr.per_all_assignments_f  asg
                                          )
UNION
SELECT fad.attached_document_id        attached_document_id
FROM   applsys.fnd_attached_documents  fad
WHERE  fad.entity_name                 = 'PAY_ELEMENT_ENTRIES_F'
AND    fad.pk1_value                   IN(SELECT peef.element_entry_id    element_entry_id
                                          FROM   hr.pay_element_entries_f peef
                                          )
UNION
SELECT fad.attached_document_id        attached_document_id
FROM   applsys.fnd_attached_documents  fad
WHERE  fad.entity_name                 = 'OTA_DELEGATE_BOOKINGS'
AND    fad.pk1_value                   IN(SELECT odb.booking_id             booking_id
                                          FROM   OTA.OTA_DELEGATE_BOOKINGS  ODB
                                          )


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

Once you have the attached_document_id

you can use below query


SELECT fd.* FROM FND_ATTACHED_DOCUMENTS FAD, FND_DOCUMENTS FD
WHERE FAD.DOCUMENT_ID=FD.DOCUMENT_ID;

based on datatype_id in fnd_documents

you can join with any of

,APPLSYS.FND_LOBS
,APPLSYS.FND_DOCUMENTS_SHORT_TEXT
,APPLSYS.FND_DOCUMENTS_LONG_TEXT

tables.



Wednesday, 23 July 2014

How to download attachment stored in fnd_lobs table.


How to download attachment stored in fnd_lobs table


-        -          Open employee’s record and click on attachment icon.


-      Attach any new document under category as show below.





Now you can see the attachment symbol.



Attachment will be stored in fnd_lobs table.

below are queries to identify the attachment record.

select * from fnd_attached_documents
where entity_name='PER_PEOPLE_F'
and pk1_value=33579
order by creation_date desc;


select * from fnd_documents
where document_id=2349427;

select * from fnd_lobs
where file_id=2990785;

Now if you download the attachment through pl/sql procedure.

prerequisite 

- server directory -  where you want to download the file - generally database tier
- read/write access on the that directory.
- proper privileges on UTL_file packages

you can below procedure to download above attachment.



CREATE OR REPLACE PROCEDURE APPS.xx_download_attachment
AS
   CURSOR cur_new_attmt
   IS
      SELECT fl.file_id, fl.file_name, fl.file_data,
             DBMS_LOB.getlength (fl.file_data) file_length
        FROM fnd_lobs fl
       WHERE fl.file_id =2990785;

   v_start           NUMBER             DEFAULT 1;
   v_bytelen         NUMBER             DEFAULT 32000;
   v_len_copy        NUMBER;
   v_raw_var         RAW (32000);
   v_output          UTL_FILE.file_type;
   v_inv_file_name   VARCHAR2 (100);
   v_position        NUMBER;
   v_first_rec       BOOLEAN            DEFAULT TRUE;
BEGIN
   v_position := 10;

   FOR rec_inv IN cur_new_attmt
   LOOP
      v_inv_file_name := NULL;
      v_inv_file_name := rec_inv.file_name;
      v_position := 20;
      -- define output directory AND OPEN THE file IN WRITE BYTE MODE
      v_output := UTL_FILE.fopen ('ODPDIR', v_inv_file_name, 'wb', 32760);
      v_position := 30;

      -- maximum size OF buffer parameter IS 32767 BEFORE
      -- which you have TO flush  your buffer
      IF rec_inv.file_length < 32760
      THEN
         UTL_FILE.put_raw (v_output, rec_inv.file_data);
         UTL_FILE.fflush (v_output);
      ELSE
         v_position := 40;
         v_start := 1;
         v_bytelen := 32000;
         v_len_copy := rec_inv.file_length;

         WHILE v_start < rec_inv.file_length AND v_bytelen > 0
         LOOP
            v_position := 50;
            DBMS_LOB.READ (rec_inv.file_data, v_bytelen, v_start, v_raw_var);
            v_position := 60;
            UTL_FILE.put_raw (v_output, v_raw_var);
            v_position := 70;
            UTL_FILE.fflush (v_output);
            v_start := v_start + v_bytelen;
            v_len_copy := v_len_copy - v_bytelen;

            IF v_len_copy < 32000
            THEN
               v_bytelen := v_len_copy;
            END IF;
         END LOOP;

         v_position := 80;
         UTL_FILE.fclose (v_output);
      END IF;

      v_position := 90;
      DBMS_OUTPUT.put_line (' File_name :' || rec_inv.file_name);
   END LOOP;
END xx_download_attachment;
/


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


execute the procedure to download the attachment.


execute xx_download_attachment;

Note : 

-  'ODPDIR' is the directory name.

refer dba_directories for more detail.

select * from dba_directories;





Tuesday, 22 July 2014

How to change current session language


How to change current session language in oracle

select * from per_business_groups;

output : no rows selected.

sometime some views will not return any data because of current session language.
Language install in your application is 'US', but what if your current session language is 'GB' or 'AR'.

sometimes your current session language depends on the tool that you are using or some default setup.

Generally it returns 'US'.
=====================================

select userenv('LANG') from dual;

output : US

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

If it's not then you can your current session language by using alter session command.

eg. For AR

alter session set NLS_LANGUAGE='ARABIC';

eg. For GB

alter session set NLS_LANGUAGE='ENGLISH';

eg. For US

alter session set NLS_LANGUAGE='AMERICAN';

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


Friday, 30 May 2014

Set application message and token and retrieve it / Oracle application messages / FND Messages



Create a new application message as shown below.





You can set the message ,it's tokens, retrieve the value and then you can use it user hooks,form personalizations and other scripts.


DECLARE

   lv_msgdata   VARCHAR2 (32000);

BEGIN

   apps.fnd_message.set_name ('PER', 'XX_MSG_1');
   apps.fnd_message.set_token ('MSG_NAME', 'XXXX', TRANSLATE => TRUE);
   apps.fnd_message.set_token ('MSG_START_DATE',
                               '01-Jan-2014',
                               TRANSLATE      => TRUE
                              );
   lv_msgdata := apps.fnd_message.get ();

END;



Wednesday, 28 May 2014

How to Hide Reassign and Request for information button in workflow notification in oracle apps



HIDE REASSIGN BUTTON

Create an attribute with below details and drag and drop under the message for which you want to hide reassign button.




HIDE REQUEST FOR INFORMATION BUTTON

Create an attribute with below details and drag and drop under the message for which you want to hide reassign button.



Sunday, 25 May 2014

Running the pre-processor after registering user-hook in oracle apps / Alternative of pre-processor


To run the pre-processor run one of the following commands:

cd $PER_TOP/admin/sql
Log into SQLPLUS as the APPS user
SQL> @hrahkall.sql

or

SQL> @hrahkone.sql


The first script will create all hook package bodies, whilst the second will
create hook package bodies for one API module only, and prompt for that
api_module_id.

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

Sometimes if you don't have server credentials then you can use below alternative to run the pre-processor through sqlplus

Log in to database through apps

SQL> @hrahkall.sql

instead of running above script you can execute below statement

exec hr_api_user_hooks_utility.create_hooks_all_modules;

SQL> @hrahkone.sql

instead of running above script you can execute below statement

exec hr_api_user_hooks_utility.create_hooks_one_module(p_module_id);