Migrating Web ADI template from one Instance to another

Following are the components that are associated to the Web ADI that can be migrated from one instance to another:

• Integrators
• Layouts
• Mappings
• Contents

Integrators:

Identify the Integrator to migrate as below,

select integrator_code, application_short_name
  from apps.bne_integrators_vl  bnv
      ,apps.fnd_application_vl  fav
 where bnv.user_name = &integrator_name
   and fav.application_id = bnv.application_id;

For the Integrators, you can

Download the Integrator:
FNDLOAD apps/$APPS_PASSWORD 0 Y DOWNLOAD $BNE_TOP/patch/115/import/bneintegrator.lct INTEGRATOR_LDTFILE.ldt BNE_INTEGRATORS INTEGRATOR_ASN="appl_short_name" INTEGRATOR_CODE="integrator_name"

Upload the Integrator:
FNDLOAD apps/$APPS_PASSWORD 0 Y UPLOAD $BNE_TOP/patch/115/import/bneintegrator.lct INTEGRATOR_LDTFILE.ldt

Layouts:

Identify the Layout to migrate as below,

select layout_code
  from apps.bne_layouts_vl
 where user_name = &integrator_name;

For the Above Layouts, you can

Download the Layout:
FNDLOAD apps/$APPS_PASSWORD 0 Y DOWNLOAD $BNE_TOP/patch/115/import/bnelay.lct LAYOUT_LDT.ldt BNE_LAYOUTS LAYOUT_ASN="appl_short_name" LAYOUT_CODE="Layout_code"

Upload the Layout:
FNDLOAD apps/$APPS_PASSWORD 0 Y UPLOAD $BNE_TOP/admin/import/bnelay.lct LAYOUT_LDT.ldt

Mappings:

Identify the Mapping to migrate as below,


select mapping_code
  from apps.bne_mappings_vl
 where integrator_code = &integrator_code

For the Above Mappings, you can,

Download the Mapping:
FNDLOAD apps/$APPS_PASSWORD 0 Y DOWNLOAD $BNE_TOP/patch/115/import/bnemap.lct MAPPING_LDT.ldt BNE_MAPPINGS MAPPING_ASN="appl_short_name" MAPPING_CODE="Mapping_code"

Upload the Mapping:
FNDLOAD apps/$APPS_PASSWORD 0 Y UPLOAD $BNE_TOP/admin/import/bnemap.lct MAPPING_LDT.ldt

Contents:

Identify the Contents to migrate as below,

select content_code
   from apps.bne_contents_vl
  where integrator_code = &integrator_code

For the Above Contents, you can

Download the Contents:
FNDLOAD apps/$APPS_PASSWORD 0 Y DOWNLOAD $BNE_TOP/patch/115/import/bnecont.lct CONTENT_LDT.ldt BNE_CONTENTS CONTENT_ASN="appl_short_name" CONTENT_CODE="Content_code"

Upload the Contents:
FNDLOAD apps/$APPS_PASSWORD 0 Y UPLOAD $BNE_TOP/admin/import/bnecont.lct CONTENT_LDT.ldt

Print This Post

Web ADI: Custom Integrators Update Icon

The Update Icon for Custom Web ADI's is greyed out, reason for this is that the Integrator may be associated to the ORACLE Standard User.

For the Custom Web ADI's to be updateable, the Integrators Source should be set to 'C'(Custom) rather than 'O'(Oracle).

If the Custom integrators source is Set to 'O' - Oracle in the table BNE_INTEGRATORS_B , please reset it to 'C' - Custom to make it updateable as below,

update BNE_INTEGRATORS_B
   set SOURCE = 'C'
 where APPLICATION_ID = &application_id
   and INTEGRATOR_CODE = &integrator_code;

Print This Post

Unable to access the 'Web ADI - Update Interface Column Prompts' integrator

The Integrator 'Web ADI - Update Interface Column Prompts' is designed for HR and if HR is not installed/implemented then there is a chance that you will not be able to find it in the list.
To access the integrator, you have to add the Function ‘HR_ADI_CAG_INTERFACE_COLS’ to the Menu ‘Desktop Integration menu’

  Print This Post

Oracle Web ADI: Custom Integrator Prompts Change

If you are creating a custom Integrator, and most of the times you would not like the Default prompts it keeps from the API parameters, here is the process to change the Prompt names.

Login to Application as Web ADI Responsibility

Desktop Integrator --> Create Document

Select the Integrator ‘Web ADI – Update Interface Column Prompts’ and click on the Next Button as below,

 

Select the specified Excel version and Click on next Button,

 

Select any specific Integrator, for which you want to change the prompts, Here I have selected,

Application: PAY
Integrator: PAY RI – Create US Federal Taxability Rules For Earnings
Language: US

 

Click Continue and Create a Document

 

This Opens the Document with the Prompt Details Above, Left etc. Here you Can update the prompts and upload the changes.

The Changes would then be reflected in the respective Documents.


 

Print This Post

Oracle Web ADI: Creating a Custom Integrator

Just to give you a quick overview of Oracle Web ADI(Oracle Web Application Desktop Integrator), it brings E-business suite functionality to desktop where familiar Microsoft, Excel, Word and Project applications that can be used to complete your Oracle E-Business Suite tasks.

The Oracle Web Applications Desktop Integrator integration with Microsoft Excel enables you to bring your Oracle E-Business Suite data to a spreadsheet where familiar data entry and modeling techniques can be used to complete Oracle E-Business Suite tasks. You can create formatted spreadsheets on your desktop that allow you to download, view, edit, and create Oracle E-Business Suite data. Use data entry shortcuts (such as copying and pasting or dragging and dropping ranges of cells) or Excel formulas to calculate amounts to save time.

Here in this topic I will give some quick steps to create a Custom Integrator:

Firstly, with Web ADI you get the following 2 Standard responsibilities:

1. Desktop Integration Manager

2. Desktop Integrator

 

Desktop Integration manager involves creating/Managing Integrators and parameters

Desktop Integrator lets you create Document, layout, Mapping, Setup options etc.,

Now to create a Custom integrator you need to,

a. Create an integrator
b. Create a Layout

Desktop Integration Manager - > Create Integrator

Define the Integrator as below, and make sure to check the checkbox across ‘Display in Create Documents Page’ and click on the Next Button

 

 And now define the Interface Name, here I am using the Interface Type of Function, and you can return the Error Information from the Function. The Package and Function names are provided. The Function I have used is a Sample insert into a Table function as shown below,

create table test_adi(sno number, sname varchar2(2000));

create or replace package test_adi_pkg as
function test_adi_func(p_sno    in number, p_sname in varchar2) return varchar2;
end test_adi_pkg;
/

create or replace package body test_adi_pkg as
function test_adi_func(p_sno    in number, p_sname in varchar2) return varchar2
is
begin
insert into test_adi values(p_sno, p_sname);
return 'Y';
end;
end test_adi_pkg;
/

Click on the Apply button and the Next button after until you reach the Last Page and Submit the Definition as shown below,

    

 Now you have defined an Integrator and the next step is to Define a Layout to insert data in to the Integrator

Desktop Integrator --> Create a Layout




As shown below select the Test ADI Integrator and click on Go button as there are no Layouts defined it does not display any Layouts.

Now click on create Button, and the next Page asks you the Layout Name and the Number of Headers required for the Spreadsheet,
 

Select the number of Headers and click on next Button and you can now select which fields to appear at Header Level and which fields to appear at Line level. And there are different Types you can use. 

 Now in the Layout Screen you can do all necessary Layout changes, such as you can make a field Read Only, order them, Select the Prompt name etc.,

And once you click on the Apply button it saves the Layout and the spreadsheet is now ready to be entered data for.

And now we will go to the Final Step of creating a document,

Desktop Integrator -> create Document

Select the Integrator and the Layout defined and click on the create Document button to create an Excel Spreadsheet.

 Enter data for SNO and SNAME fields defined and upload them to Oracle Data Table.

An Excel document would be created with name Wen ADI(n).xls

Once the data is entered you can initiate the Upload Macro by clicking Oracle -> Upload

And the successful upload would like as shown below with the number of rows uploaded

 The data is now inserted to the underlying Table as shown below.

 Custom Integrator not only loads the data, you can do much more things to process the data by initiating a concurrent program from the API etc.,

Print This Post

FND: Personalization Seeding Mode

In Oracle Applications Framework, if the Profile option FND: Personalization Seeding Mode is set to NULL/No,it would not allow Standard Personalizations to be updated.

Suppose for this case, it does not allow you to update the Personalization at Site Level for the Page /oracle/apps/icx/por/req/webui/CheckoutUpdateSinglePG as shown below,

Now after setting the Profile option FND: Personalization Seeding Mode to Yes, the Personalization can be updated as below,

Print This Post

RG_FLEX_ASSIGNMENTS Error: Structure Id's for both Axis Sets are Null

Failing to define neither of the Row and Column Assignments, will error the report with the above error.

The issue is caused due to following setup:
There are no account assignments in either Row Set or Column Set which is being used in the FSG Report.

Since FSG reports are used to query balances of accounts, atleast one account assignment is mandatory for it.



Print This Post

 

Oracle Financial Statement Generator Report Basics

Financial Statement Generator (FSG) is a powerful report building tool for Oracle General Ledger.

1. Generate financial reports, such as income statements and balance sheets, based upon data in your general ledger.
2. Define segment value security rules to restrict financial information contained in FSG report output generated by specific users and responsibilities.

FSG Report uses the fundamental row/column concept to build the financial reports,a dn follow the below guidelines while defining a new report:
1. Decide which rows and columns will make up your report.
2. Define the rows and columns, and then tell FSG what attributes those rows and columns have.
3. Build a report using those rows and columns.

More than one row can be grouped to a Rowset and similarly more than once column can be grouped to a Column Set, and can be re-used in another reports.

Row sets and column sets are the two primary building blocks of FSG reports.

Example to create an FSG Report:

Create a Sample Rowset as below,

GL -> Reports -> Define -> Row Set

Click on Define Rows

Click on Account Assignments and define the ccounts for which you want to generate the Row details for,

 

 Activity can contain Net Dr, Cr values

Create a Column set as below,

GL -> Reports -> Define -> Column Set

Click on Define Columns

You can as well define Account Assignments at the Column level as well to control the data and values

Define Column Set with Column Headings as below,

Define a Financial Report as below,

GL -> Reports -> Define -> Report

Run the Report(Run Report from Report Definition screen), and it executes the following Query:

SELECT nvl(bal.PERIOD_TYPE, ''),
    nvl(bal.PERIOD_YEAR, -1),
    bal.PERIOD_NAME,
    nvl(bal.PERIOD_NUM, -1),
    nvl(bal.PERIOD_NUM, -1),
    bal.ACTUAL_FLAG,
    decode(cc.TEMPLATE_ID, NULL, 'N', 'Y'),
    -1,
    nvl(bal.BUDGET_VERSION_ID, -1),
    -1,
    nvl(bal.ENCUMBRANCE_TYPE_ID, -1),
    bal.CURRENCY_CODE,
    bal.SET_OF_BOOKS_ID,
    nvl(bal.TRANSLATED_FLAG, ''),
    nvl(bal.PERIOD_NET_DR, 0) - nvl(bal.PERIOD_NET_CR, 0),
    nvl(bal.PERIOD_NET_DR, 0),
    nvl(bal.PERIOD_NET_CR, 0),
    nvl(bal.QUARTER_TO_DATE_DR,0)-nvl(bal.QUARTER_TO_DATE_CR, 0),
    nvl(bal.QUARTER_TO_DATE_DR, 0),
    nvl(bal.QUARTER_TO_DATE_CR, 0),
    nvl(bal.BEGIN_BALANCE_DR, 0) - nvl(bal.BEGIN_BALANCE_CR, 0),
    nvl(bal.BEGIN_BALANCE_DR, 0),
    nvl(bal.BEGIN_BALANCE_CR, 0),
    nvl(bal.PROJECT_TO_DATE_DR, 0) - nvl(bal.PROJECT_TO_DATE_CR, 0),
    nvl(bal.PROJECT_TO_DATE_DR, 0),
    nvl(bal.PROJECT_TO_DATE_CR, 0),
    nvl(SEGMENT1,''),
    nvl(SEGMENT2,''),
    nvl(SEGMENT3,''),
    nvl(SEGMENT4,''),
    nvl(SEGMENT5,''),
    nvl(SEGMENT6,'')
FROM GL_BALANCES bal,
    GL_CODE_COMBINATIONS cc
WHERE bal.CODE_COMBINATION_ID = cc.CODE_COMBINATION_ID
AND   cc.CHART_OF_ACCOUNTS_ID = 50286
AND   bal.SET_OF_BOOKS_ID  = 1
AND   nvl(bal.TRANSLATED_FLAG, 'x')in ('Y','N','x')
AND   cc.TEMPLATE_ID is NULL
--This is due to the Account assignments defined at row level
AND ( (nvl(SEGMENT2,'') >= '23000' AND  nvl(SEGMENT2,'') <= '23001')
)
--This is due to PTD and YTD Columns defined for the Column Set level
AND    (
(bal.period_name in ('Adj-14','Nov-13')
AND     (    (nvl(bal.period_name,'') = 'Adj-14'
AND    (    (bal.CURRENCY_CODE = 'GBP'
        AND    bal.ACTUAL_FLAG = 'B'
        AND    bal.BUDGET_VERSION_ID = 0
)
 OR
 (    (bal.CURRENCY_CODE = 'GBP'
        AND    bal.ACTUAL_FLAG = 'B'
        AND    bal.BUDGET_VERSION_ID = 0
        AND    ((nvl(bal.PERIOD_NET_DR,0) != 0) or (nvl(bal.PERIOD_NET_CR,0) != 0))) ))) OR        (nvl(bal.period_name,'') = 'Nov-13'
AND    (    (bal.CURRENCY_CODE = 'GBP'
        AND    bal.ACTUAL_FLAG = 'B'
        AND    bal.BUDGET_VERSION_ID = 0
        AND    ((nvl(bal.PERIOD_NET_DR,0) != 0) or (nvl(bal.PERIOD_NET_CR,0) != 0))))))
) )

Print This Post

 

Journals/Journal Lines created with ‘Journal Import Created’

By Default Oracle creates Journal/Journal Line Reference fields with ‘Journal Import created’ text and Oracle advises to use Reference fields Reference6(for Journal) and Reference10(for Journal Line) in GL_INTERFACE to be populated with the desired details to appear in the Journal/Journal line Reference fields.



Print This Post

 

Email a File from the Oracle Application Server

Below is the most useful code snippet, that can be used to read a file from the application server or any concurrent program output file from the desired location

*********************************************************

declare

l_conn           utl_smtp.connection;
l_file_handle    UTL_FILE.FILE_TYPE;

l_dirpath        VARCHAR2 (150) := '/usr/tmp/';
l_filename       VARCHAR2 (50) :=  'Test.txt';
l_sender         VARCHAR2 (50) := 'abc@gmail.com';
l_recpnt         VARCHAR2 (255):= 'xyz@gmail.com';
l_msg            VARCHAR2 (32767);
l_line           VARCHAR2 (1000);
lc_message       VARCHAR2 (1000);
crlf             VARCHAR2 (2)  := CHR (13) || CHR (10);

l_reply          UTL_SMTP.REPLY;
l_clob           CLOB := '';
l_subject        VARCHAR2(2000) := 'This is a sample test email';
l_body           VARCHAR2(2000) := 'Body email';
l_boundary       VARCHAR2(50) := '----=*#abc1234321cba#*=';
ln_amount        NUMBER := 0;
ln_temp          NUMBER := 0;
l_filedata       VARCHAR(32767);

begin

l_conn := utl_smtp.Open_Connection('127.0.0.1', 25);

utl_smtp.Helo(l_conn, '127.0.0.1');
utl_smtp.Mail(l_conn, l_sender);
utl_smtp.rcpt(l_conn, l_recpnt);
utl_smtp.open_data(l_conn);
l_filedata := l_filedata || 'Date:' || TO_CHAR(SYSDATE, 'DD-MON-RRRR HH24:MI:SS') || crlf;
l_filedata := l_filedata || 'To: ' || l_recpnt || crlf;
l_filedata := l_filedata || 'From: ' || l_sender || crlf;
l_filedata := l_filedata || 'Subject: ' || l_subject || crlf;
l_filedata := l_filedata || 'MIME-Version: 1.0' ||  crlf; 
l_filedata := l_filedata || 'Content-Type: multipart/mixed; boundary=' ||chr(34)||l_boundary|| chr(34)||crlf;
l_filedata := l_filedata || '--'||l_boundary|| crlf;
l_filedata := l_filedata || 'Content-Type: text/plain;'|| crlf ||
                                           'Content-Transfer_Encoding: 7bit'|| crlf ||
                                           crlf || l_body || crlf || crlf ||
                                           '--'||l_boundary|| crlf ||
                                           'Content-Type: text/plain;'|| crlf ||
                                           ' name="'||l_filename||'"'|| crlf ||
                                           'Content-Transfer_Encoding: 8bit'|| crlf ||
                                          'Content-Disposition: attachment;'|| crlf ||
                                          ' filename="'||l_filename||'"'|| crlf || crlf;

dbms_lob.createtemporary(l_clob, false, 10);
dbms_lob.write(l_clob, LENGTH(l_filedata), 1, l_filedata);

l_file_handle := UTL_FILE.FOPEN (l_dirpath, l_filename, 'R');

ln_temp := dbms_lob.getlength(l_clob) + 1;

LOOP

  BEGIN
    utl_file.get_line(l_file_handle, l_msg);
    ln_temp := dbms_lob.getlength(l_clob) + 1;
    IF DBMS_LOB.GETLENGTH(l_msg) > 0 THEN
       dbms_lob.write(l_clob, LENGTH(l_msg)+1, ln_temp, l_msg||CHR(10));
    END IF;   
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
    EXIT;
  END;

END LOOP;

utl_file.fclose(l_file_handle);

ln_temp := 1;
ln_amount := 1900;

WHILE ln_temp < dbms_lob.getlength(l_clob) LOOP
   utl_smtp.write_data(l_conn, dbms_lob.substr(l_clob, ln_amount, ln_temp));
   ln_temp := ln_temp + ln_amount;
   ln_amount := least(1900, dbms_lob.getlength(l_clob) - ln_amount);
END LOOP;

utl_smtp.write_data(l_conn, crlf||crlf);
utl_smtp.close_data(l_conn);
utl_smtp.quit(l_conn);

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error:'||SQLERRM);

end;

*********************************************************

Print This Post

 

Useful Scripts

1. Diabling all the users:

DECLARE

CURSOR c_user is
SELECT user_name
  FROM fnd_user
 WHERE TRUNC(NVL(end_date,SYSDATE)) >= TRUNC(sysdate);

type t_user_name is table of fnd_user.user_name%TYPE;
lt_user_name t_user_name;

begin

open c_user;
fetch c_user bulk collect into lt_user_name;
close c_user;

for i in 1..lt_user_name.COUNT LOOP
  fnd_user_pkg.updateuser(x_user_name => lt_user_name(i)
                         ,x_owner     => null
                         ,x_end_date  => TRUNC(sysdate-1) );
                        
  dbms_output.put_line('User Disabled:'||lt_user_name(i));
END LOOP;

COMMIT;

dbms_output.put_line('commit Complete');

EXCEPTION

WHEN OTHERS THEN
dbms_output.put_line('Exception');

END;
/ 


Print This Post
 

Standing Charges: Setup and Process Overview

Standing Charges:

Standing Charges are used to Enter Standing Charge Agreements which are used as a source to control periodic invoices. The Standing Charges functionality enables the user to charge customers periodically and automatically create invoices without the need to enter duplicate information.

Standing Charges have a start date and items specified, which can be amended and update the price items.

Standing Charges Process:

The following diagram shows the Standing Charges process flow, as described in the accompanying text.

Standing Charges Setup:

Specify the Combined Basis Accounting Standing Charges details in the Extended System Options Window(Standing Charges -> Receivables Setup -> Extended System Options)
Receivables uses the transaction flexfields to uniquely identify each transaction and transaction line created by standing charges. Transactions and transaction lines are imported through AutoInvoice.

Billing and Charging Periods:

Billing periods determine the frequency of standing charge invoice creation. Charge periods determine how often items are charged to the customers.
Set up billing and charging periods before entering standing charge items and standing charge agreements.

There are four types of period component: Day, Week, Month and Year. If using a combination of components for a standing charge, the relationship must be set up in advance.

Enter Charge Items: ( Standing Charges -> Maintain Items)

Charge Items are entered in this form with the Price, Period, Tax Code, Account details and the effective datee, it also has the item price history. Each item should be made active to be included in the Standing Charge.

Enter Standing Charges: (Standing Charges -> Maintain Standing Charges):

When there is an agreement with the customer that a charge is invoiced periodically the details of the agreement can be entered in the system.

Enter Customer Name, Period, Frequency, Next Due Date(This is updated when the Standing Charge programs are run), Next GL Date.

The Synchronize Standing Charges process updates the Next GL Date and the First Date at charge line level if invoicing rules are used. The only difference is that the Next GL Date and the First Date for the accounting rule can be updated and the updated value is used for calculating the GL dates.

and then following Programs are run in an order to generate the Standing Charges periodically,

Standing Charges: Preliminary Invoice Register Report
Standing Charges: Generate Interface Data Program
AutoInvoice
Standing Charges: Synchronize Standing Charges Program



Print This Post

APP-AR-11017: Recreation of Payment schedules failed because the transaction has activity against it

The main reason that the transaction, adjustment, receipt fails validation is due to the difference in the Payment Term(term_id), exchange rate or gl_date between RA_CUSTOMER_TRX_ALL and AR_PAYMENT_SCHEDULES_ALL for a given record.

The below update statement should resolve the issue:

update AR_PAYMENT_SCHEDULES_ALL arps
   set arps.TERM_ID = (select rct.TERM_ID
                         from RA_CUSTOMER_TRX_ALL rct
                        where arps.CUSTOMER_TRX_ID = rct.CUSTOMER_TRX_ID )
where arps.CUSTOMER_TRX_ID = <trans_id> ;

to check if there are other transactions affected you can run the following SQL:

SELECT arps.payment_schedule_id,arps.term_id ps_term_id,rct.term_id ct_term_id
  FROM ra_customer_trx_all rct,
       ar_payment_schedules_all arps
 WHERE rct.customer_trx_id = arps.customer_trx_id
   AND rct.term_id <> nvl(arps.term_id,-1); 



Print This Post

Terminating an HR Employee

I have seen a few organizations sometimes end date the user rather than the employee especially when the organizations tend not to use Employee Payroll, which clearly makes sense. But the Inactive Employees have to be made Ex-Employees at some point of time for the integrity of the system and Reporting purpose.

Hereby is the script to modify Inactive Employees(personalized to the Employees without a user-login, but can also be personalized to your need) to Ex-Employees:


DECLARE

ld_last_std_process_date_out date := TRUNC(TO_DATE('23-JUL-2002', 'DD-MON-RRRR'));
l_period_of_service_id  PER_PERIODS_OF_SERVICE.period_of_service_id%TYPE;

lb_supervisor_warning               boolean;
lb_event_warning                    boolean;
lb_interview_warning                boolean;
lb_review_warning                   boolean;
lb_recruiter_warning                boolean;
lb_pay_proposal_warning             boolean;
lb_dod_warning                      boolean;

--lc_object_version_number number(15) := 1;

ld_final_process_date    date := SYSDATE-14;
lb_org_now_no_manager_warning boolean;
lb_asg_future_changes_warning boolean;
lb_entries_changed_warning    varchar2(240);
ln_error number := 0;

TYPE t_person_id IS TABLE OF PER_ALL_PEOPLE_F.person_id%TYPE;
TYPE t_full_name IS TABLE OF PER_ALL_PEOPLE_F.full_name%TYPE;
TYPE t_eff_start_date IS TABLE OF PER_ALL_PEOPLE_F.effective_start_date%TYPE;
TYPE t_eff_end_date IS TABLE OF PER_ALL_PEOPLE_F.effective_end_date%TYPE;
TYPE t_ass_eff_start_date IS TABLE OF PER_ALL_PEOPLE_F.effective_start_date%TYPE;
TYPE t_ass_eff_end_date IS TABLE OF PER_ALL_PEOPLE_F.effective_end_date%TYPE;
TYPE t_last_update_date IS TABLE OF PER_ALL_PEOPLE_F.last_update_date%TYPE;
TYPE t_ass_last_update_date IS TABLE OF PER_ALL_PEOPLE_F.last_update_date%TYPE;
TYPE t_object_version_number IS TABLE OF PER_PERIODS_OF_SERVICE.object_version_number%TYPE;
TYPE t_period_of_service_id IS TABLE OF PER_PERIODS_OF_SERVICE.period_of_service_id%TYPE;
TYPE t_end_date IS TABLE OF fnd_user.end_date%TYPE;

lt_person_id             t_person_id;
lt_full_name             t_full_name;
lt_eff_start_date        t_eff_start_date;
lt_eff_end_date          t_eff_end_date;
lt_ass_eff_start_date    t_ass_eff_start_date;
lt_ass_eff_end_date      t_ass_eff_end_date;
lt_last_update_date      t_last_update_date;
lt_ass_last_update_date  t_ass_last_update_date;
lt_object_version_number t_object_version_number;
lt_period_of_service_id  t_period_of_service_id;
lt_end_date              t_end_date;

CURSOR c_inactive_emp
IS
select distinct papf.person_id, papf.full_name, papf.effective_start_date, papf.effective_end_date,
       pasf.effective_start_date, pasf.effective_end_date, papf.last_update_date, pasf.last_update_date, pps.object_version_number
      ,pps.period_of_service_id, fu.end_date
  from per_all_people_f papf
      ,per_all_assignments_f pasf
      ,per_periods_of_service pps
      ,fnd_user fu
 where trunc(sysdate) between trunc(papf.effective_start_date) and trunc(NVL(papf.effective_end_date, sysdate))
   and trunc(sysdate) between trunc(pasf.effective_start_date) and trunc(NVL(pasf.effective_end_date, sysdate))
   and papf.person_id = pasf.person_id
   and papf.person_id = pps.person_id
   and papf.person_id = fu.employee_id
   and EXISTS (select 1
                from fnd_user fu
               where fu.employee_id = papf.person_id
                 and trunc(sysdate) not between trunc(fu.start_date) and trunc(fu.end_date)
              )
   and NVL(current_employee_flag, 'N') = 'Y'
 order by papf.person_id;
   
/*
CURSOR c_inactive_emp
IS
SELECT DISTINCT papf.person_id, papf.full_name, papf.effective_start_date, papf.effective_end_date,
       pasf.effective_start_date, pasf.effective_end_date, papf.last_update_date, pasf.last_update_date, pps.object_version_number
      ,pps.period_of_service_id
  FROM per_all_people_f papf
      ,per_all_assignments_f pasf
      ,per_periods_of_service pps
 WHERE TRUNC(SYSDATE) BETWEEN TRUNC(papf.effective_start_date) and trunc(NVL(papf.effective_end_date, sysdate))
   and trunc(sysdate) between trunc(pasf.effective_start_date) and trunc(NVL(pasf.effective_end_date, sysdate))
   and papf.person_id = pasf.person_id
   and papf.person_id = pps.person_id
   and EXISTS (select 1
                from fnd_user fu
               where fu.employee_id = papf.person_id
                 and trunc(sysdate) not between trunc(fu.start_date) and trunc(fu.end_date)
              )
   and NVL(current_employee_flag, 'N') = 'Y'

 ORDER BY papf.person_id;
*/

begin

OPEN c_inactive_emp;
FETCH c_inactive_emp BULK COLLECT INTO lt_person_id, lt_full_name, lt_eff_start_date, lt_eff_end_date, lt_ass_eff_start_date
                                      ,lt_ass_eff_end_date, lt_last_update_date, lt_ass_last_update_date, lt_object_version_number
                                      ,lt_period_of_service_id, lt_end_date;
CLOSE c_inactive_emp;


FOR i in 1..lt_person_id.COUNT LOOP

ln_error := 0;

dbms_output.put_line('Full Name:'||lt_full_name(i));
dbms_output.put_line('Effective Start Date:'||lt_eff_start_date(i));
dbms_output.put_line('Effective End Date:'||lt_eff_end_date(i));
dbms_output.put_line('Assignment Effective Start Date:'||lt_ass_eff_start_date(i));
dbms_output.put_line('Assignment Effective End Date:'||lt_ass_eff_end_date(i));
dbms_output.put_line('Period of Service:'||lt_period_of_service_id(i));
dbms_output.put_line('Object Version:'||lt_object_version_number(i));
dbms_output.put_line('End Date:'||lt_end_date(i));

BEGIN

hr_ex_employee_api.actual_termination_emp(
 p_effective_date => lt_end_date(i)
,p_period_of_service_id => lt_period_of_service_id(i)
,p_object_version_number => lt_object_version_number(i)
,p_actual_termination_date => lt_end_date(i)
,p_person_type_id => 69 -- Person Type Id is EX_EMP

,p_last_std_process_date_out => ld_last_std_process_date_out
,p_supervisor_warning        => lb_supervisor_warning
,p_event_warning             => lb_event_warning
,p_interview_warning         => lb_interview_warning
,p_review_warning              => lb_review_warning
,p_recruiter_warning           => lb_recruiter_warning
,p_asg_future_changes_warning   => lb_asg_future_changes_warning
,p_entries_changed_warning      => lb_entries_changed_warning
,p_pay_proposal_warning         => lb_pay_proposal_warning
,p_dod_warning                  => lb_dod_warning
);
dbms_output.put_line('actual_termination_emp completed successfully for Employee:'||lt_full_name(i));
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Exception for Employee:'||SQLCODE||SQLERRM);
ln_error := 1;
END;

IF (ln_error = 0) THEN

 BEGIN

 hr_ex_employee_api.update_term_details_emp
   (p_effective_date                => lt_end_date(i)
   ,p_period_of_service_id          => lt_period_of_service_id(i)
   ,p_object_version_number         => lt_object_version_number(i)
   ,p_accepted_termination_date     => lt_end_date(i)
   );
 dbms_output.put_line('update_term_details_emp completed successfully for Employee:'||lt_full_name(i));
 
 EXCEPTION
 WHEN OTHERS THEN
 DBMS_OUTPUT.PUT_LINE('Exception for Employee:'||SQLCODE||SQLERRM);
 ln_error := 1;
 END;

 END IF;

dbms_output.put_line('Employee: '||lt_full_name(i)||' update completed');
 
  /* Not Required if Payroll is not used
  hr_ex_employee_api.final_process_emp
    (p_period_of_service_id         => l_period_of_service_id
    ,p_object_version_number        => lc_object_version_number
    ,p_final_process_date           => ld_final_process_date
    ,p_org_now_no_manager_warning   => lb_org_now_no_manager_warning
    ,p_asg_future_changes_warning   => lb_asg_future_changes_warning
    ,p_entries_changed_warning      => lb_entries_changed_warning
    );
  */

END LOOP;

exception
when others then
dbms_output.put_line('Exception'||SQLERRM||SQLCODE);

end;


hr_ex_employee_api1.final_process_emp is not required to be called following the other 2 API's if Oracle Payroll is not implemented.


Print This Post