SELECT distinct 

    aia.invoice_id,

    aia.invoice_num,

    aia.invoice_date,

    aia.invoice_amount,

    aia.invoice_currency_code,

    aia.payment_currency_code,

    aia.gl_date,

    xah.period_name,

    aia.payment_method_code,

    xah.je_category_name,

    xev.event_id,

    xte.ENTITY_CODE

    ,xte.APPLICATION_ID

    ,xte.legal_entity_id

    ,source_id_int_1

    ,source_application_id

    ,source_id_int_2

    ,source_id_char_1

    ,gjh.status

FROM

    ap.ap_invoices_all             aia,

    xla.xla_transaction_entities   xte,

    xla.xla_events                 xev,

    xla.xla_ae_headers             xah,

    xla.xla_ae_lines               xal,

    gl_import_references           gir,

    gl_je_headers                  gjh,

    gl_je_lines                    gjl,

    gl_code_combinations           gcc

WHERE

    aia.invoice_id = xte.source_id_int_1

    AND xev.entity_id = xte.entity_id

    AND xah.entity_id = xte.entity_id

    AND xah.event_id = xev.event_id

    AND xah.ae_header_id = xal.ae_header_id

    AND xah.je_category_name = 'XX_CATEGORY_NAME'--Purchase Invoices

    AND xah.gl_transfer_status_code = 'Y'

    AND xal.gl_sl_link_id = gir.gl_sl_link_id

    AND gir.gl_sl_link_table = xal.gl_sl_link_table

    AND gjl.je_header_id = gjh.je_header_id

    AND gjh.je_header_id = gir.je_header_id

    AND gjl.je_header_id = gir.je_header_id

    AND gir.je_line_num = gjl.je_line_num

    and gjh.name='XX_JOURNAL_NAME'

    and aia.invoice_num='XX_INVOICE_NUM';

DECLARE

p_event_source_info xla_events_pub_pkg.t_event_source_info;

lv_valuation_method VARCHAR2(100) :=null;

lv_security_context xla_events_pub_pkg.t_security;

BEGIN

p_event_source_info.source_application_id := 200; --101 GL -- 602 XLE

p_event_source_info.application_id := 200; -- AR

p_event_source_info.legal_entity_id := 23273;

p_event_source_info.ledger_id := 2021;

p_event_source_info.entity_type_code := 'AP_INVOICES';

p_event_source_info.transaction_number := NULL;

p_event_source_info.source_id_int_1 := 138348; -- Customer Transaction ID;

p_event_source_info.source_id_int_2 := NULL;

p_event_source_info.source_id_int_3 := NULL;

p_event_source_info.source_id_int_4 := NULL;

p_event_source_info.source_id_char_1 := NULL;

p_event_source_info.source_id_char_2 := NULL;

p_event_source_info.source_id_char_3 := NULL;

p_event_source_info.source_id_char_4 := NULL;

xla_events_pub_pkg.delete_event

(p_event_source_info => p_event_source_info,

p_event_id => 154087, -- xla_events

p_valuation_method =>lv_valuation_method,

p_security_context => lv_security_context

);

DBMS_OUTPUT.put_line('Process completed');

commit;

exception when others then

DBMS_OUTPUT.put_line('Error:'||SQLERRM);

END;

/

** To Find Parameters used in API navigate to below page :

XLA Tables Details And Query For SLA API's

Below query can be used to add (any)responsibility from backend database:

BEGIN

FND_USER_PKG.ADDRESP (

'XX_USERNAME', — Username

'SYSADMIN', –application short name

'SYSTEM_ADMINISTRATOR', — Responsibility Key

'STANDARD', — Security Group Key

'Sysadmin Responsibility added due to work', –Description

sysdate, –Start date

null –End date

);

COMMIT;

DBMS_OUTPUT.put_line ('Responsibility Added Successfully');

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line (

' Responsibility is not added due to '

|| SQLCODE

|| SUBSTR (SQLERRM, 1, 100)

);

ROLLBACK;

END;

                   API - pay_element_entry_api.create_element_entry

set serveroutput on;

DECLARE

l_date date := '01-APR-2020'; /*Start date of element*/

x NUMBER := 0;

input_value_id2 number;

input_value_id3 number;

LV_ERROR_MSG VARCHAR2(240);

LD_EFFECTIVE_START_DATE DATE;

LD_EFFECTIVE_END_DATE DATE;

LN_ELEMENT_ENTRY_ID NUMBER;

LN_OBJECT_VERSION_NUMBER NUMBER;

LB_CREATE_WARNING BOOLEAN;

lb_delete_warning BOOLEAN;

Cursor CREATE_ELEMENT_CUR Is

    SELECT

    emd.emp_name,

    emd.employee_number,

    paaf.assignment_id,

    paaf.object_version_number,

    emd.person_id,

    emd.start_date,

    ead.end_date,

    emd.total_amt   march_da,

    ead.total_amt   april_da,

    emd.FIRST_NAME,

    emd.TIME_PERIOD_ID,

    emd.EMPLOYMENT_CATEGORY,

    ( ead.total_amt - emd.total_amt) difference_in_da,

    (select pelf.element_link_id

                  from  PAY_ELEMENT_LINKS_F PELF,pay_element_types_f petf2

                  where petf2.element_type_id        =  pelf.element_type_id

                  and  petf2.element_name           = 'Dearness Allowance Adjustment') element_link_id  /*Name of Element */

                   ,(select pivf2.input_value_id input_value_id1

                  from pay_input_values_f pivf2, pay_element_types_f petf2

                  where petf2.element_name = 'Dearness Allowance Adjustment'

                  AND pivf2.element_type_id = petf2.element_type_id

                  AND pivf2.NAME = 'Pay Value') input_value_id1 

FROM

    emp_mar_da   emd,

    emp_apr_da   ead,

    per_All_assignments_f paaf

WHERE

    emd.person_id = ead.person_id

    and emd.person_id=paaf.person_id

   AND sysdate BETWEEN TRUNC(paaf.effective_start_date) AND TRUNC(paaf.effective_end_date)

;

BEGIN

FOR i in CREATE_ELEMENT_CUR 

         LOOP

/*Create custom table to get errors or success message and status if any as per requirements*/

         insert into XXEMP_UPD values(

         i.emp_name,

         i.PERSON_ID,

         i.EMPLOYMENT_CATEGORY,

         i.FIRST_NAME,

         i.EMPLOYEE_NUMBER,

         i.difference_in_da,

         i.START_DATE,

         i.END_DATE,

         null,

         null,

         i.march_da,

         i.april_da);

         input_value_id1 := i.input_value_id1;

            BEGIN

                  

                    pay_element_entry_api.create_element_entry 

                        ( -- Input data elements

                        -- -----------------------------

                        p_effective_date => l_date ,

                        P_BUSINESS_GROUP_ID => 83, -- Put BUSINESS_GROUP_ID as per client

                        P_ASSIGNMENT_ID => i.assignment_id, 

                        P_ELEMENT_LINK_ID => i.element_link_id,                                 --Link_id of User Declared Income Tax

                        p_entry_type => 'E', 

                        P_INPUT_VALUE_ID1 => i.input_value_id1,                                  --Input_Value_Id for entry_value1

                        P_ENTRY_VALUE1 => i.difference_in_da,

                        -- Output data elements

                        -----------------------------------

                        p_effective_start_date => ld_effective_start_date, 

                        p_effective_end_date => ld_effective_end_date, 

                        p_element_entry_id => ln_element_entry_id, 

                        p_object_version_number => ln_object_version_number, 

                        p_create_warning => lb_create_warning, 

                        P_OVERRIDE_USER_ENT_CHK => 'Y' );

                        x := x + 1;

                        update  XXEMP_UPD EMAU set EMAU.ELEMENT_STATUS = 'S' , EMAU.ERROR_MSG ='Success'

                        where i.person_id = EMAU.person_id;

            EXCEPTION WHEN OTHERS THEN

                LV_ERROR_MSG:=SUBSTR(SQLERRM,1,230);

                update XXEMP_UPD EMAU set EMAU.ELEMENT_STATUS = 'E' , EMAU.ERROR_MSG = LV_ERROR_MSG

                where i.person_id = EMAU.person_id;

                dbms_output.put_line(SQLERRM);

            END;

        END LOOP;

        COMMIT;

        dbms_output.put_line('Employees Processed: '||x);

EXCEPTION WHEN OTHERS THEN

    dbms_output.put_line(SQLERRM);

    COMMIT;

END;

/**Comment below for any clarification**/

we have to use frmcmp(Form Compiler) for compiling forms and libraries in Oracle Applications Version R12, But in 11i we need to use f60gen to compile forms and libraries which is deprecated in R12.

1) Login to application server.

2) Go to the directory $AU_TOP/forms/US

3) Place “.fmb” file in binary mode

4) Execute the below command to generate “.fmx”.

frmcmp_batch userid=apps/<apps_paswd> module=<Form_Name>.fmb output_file=<Form_Name>.fmx module_type=form batch=no compile_all=special

EXAMPLE:

frmcmp_batch userid=apps/apps module=XXFORM.fmb output_file=$AU_TOP/forms/US/XXFORM.fmx module_type=form batch=no compile_all=special

 SOLUTION for FRM-18108: Failed to load the following objects.
ERROR
FRM-18108: Failed to load the following objects.
Source Module: APPSTAND Source Object: STANDARD_PC_AND_VA
Source Module: APPSTAND Source Object: STANDARD_TOOLBAR
Source Module: APPSTAND Source Object: STANDARD_CALENDAR
SOLUTION:
- Copy the Form file(.fmb) APPSTAND.fmb from $AU_TOP(appl_top/au/forms/US)
- And Paste into your Local forms folder(mentioned inFORMS_PATH)
- Open the Form Builder, First open that APPSTAND.fmb Form , Now You try to Open the form.
- FRM-18108: Error will not come.

Adding new user in Oracle Apps (EBS) from back end

Application users in Oracle Apps (EBS) can be created from the front end as well as back end. The back end method for user creation can be used when you don’t have sysadmin access to the application or also it can be used to speed up the process when there are many userids to be created.

You will require apps access to connect to database  to run the package fnd_user_pkg.


We can create user, disable/enable user, add/delete responsibility to the user through back end using this seeded oracle API.


fnd_user_pkg is the seeded API provided by Oracle.

In this post we will see how to use below two functionality of this API
Step 1: createuser : the procedure to create the user
Step 2: addresp : the procedure to add responsibility to a user

Step 1: createuser : - fnd_user_pkg.createuser

DECLARE
v_user_name VARCHAR2 (100) := upper('&Enter_User_Name');
 v_description VARCHAR2 (100) := 'NEW Test User';
BEGIN
 fnd_user_pkg.createuser
(x_user_name => v_user_name,
 x_owner => NULL,
 x_unencrypted_password => '&input_password',
 x_session_number => 0,
 x_start_date => SYSDATE,
 x_end_date => NULL,
 x_last_logon_date => NULL,
 x_description => v_description,
 x_password_date => NULL,
 x_password_accesses_left => NULL,
 x_password_lifespan_accesses => NULL,
 x_password_lifespan_days => NULL,
 x_employee_id => NULL,
 x_email_address => NULL,
 x_fax => NULL,
 x_customer_id => NULL,
 x_supplier_id => NULL,
 x_user_guid => NULL,
 x_change_source => NULL
 );
 COMMIT;
END;

Step 2: Simple anonymous block to add responsibility(Sysadmin) to the user :

BEGIN
fnd_user_pkg.addresp ('&Enter_User_Name','SYSADMIN',
'SYSTEM_ADMINISTRATOR','STANDARD',
'Add Responsibility to USER using pl/sql',SYSDATE,SYSDATE + 100);
commit;
dbms_output.put_line('Responsibility Added Successfully');
exception
        WHEN others THEN
                dbms_output.put_line(' Responsibility is not added due to ' || SQLCODE || substr(SQLERRM, 1, 100));
                ROLLBACK;
END;