Blog Archives

Salary Management rollback the proposed Salary

To rollback the proposed salary which has been uploaded by webadi in Salary Management we have to use API to delete the uploaded Records.

So first query the records that has been uploaded in the backend by the below Query

select * from per_pay_proposals where change_date = :p_date;

Once you are sure to remove the records use the below

DECLARE
CURSOR fetch_det
IS
SELECT   *
FROM   per_pay_proposals
WHERE   TRUNC (change_date) = trunc(sysdate-73);

l_salary_warning   BOOLEAN;
l_err_msg          VARCHAR2 (500);
BEGIN
FOR i IN fetch_det
LOOP
BEGIN
hr_maintain_proposal_api.delete_salary_proposal (
p_pay_proposal_id         => i.pay_proposal_id,
p_business_group_id       => i.business_group_id,
p_object_version_number   => i.object_version_number,
p_validate                => FALSE,
p_salary_warning          => l_salary_warning
);
COMMIT;
DBMS_OUTPUT.put_line (
‘Proposal has been Deleted: ‘ || i.pay_proposal_id
);

COMMIT;
EXCEPTION
WHEN OTHERS
THEN
l_err_msg := SQLERRM;
DBMS_OUTPUT.put_line (‘Inner Exception: ‘ || l_err_msg);
END;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
l_err_msg := SQLERRM
DBMS_OUTPUT.put_line (‘Main Exception: ‘ || l_err_msg);
END;

Adding Invoice and registering as asset

To add the Invoice to the Fixed Asset first we have to put a Invoice and then make sure that Given Account is a Asset Account and then Validate the Invoice and make accounting once done run the Concurrent named Mass Additions Create with parameter as GL date which is Given in Invoice and select the Book that the Asset has to be registered.

Invoice Distribution Line

Invoice Distribution Line

Below is the Screenshot of the program

Mass Additions Create in AP

Mass Additions Create in AP

Once this is done you can check the same by running the Mass Additions Create Report and then you can verify the output whether the particular invoice has been send to FA and then you can go to Fixed Assets and check in Mass Additions the Invoice will be coming as new and then you can give the category and other fields and post as a Asset.

 

Submitting a Concurrent Request from BackEnd in Oracle apps

Sometimes there will be some validation that prevents us to submit Concurrent Request from frontend. So at that time we can submit the concurrent request from backend.

Below is the script to submit concurrent request from backend.

DECLARE
LN_REQUEST_ID   NUMBER := 0;
BEGIN
MO_GLOBAL.SET_POLICY_CONTEXT (‘S’, 567);
FND_GLOBAL.APPS_INITIALIZE (USER_ID           => 10835,
RESP_ID           => 51546,
RESP_APPL_ID      => 200
);
LN_REQUEST_ID :=
FND_REQUEST.SUBMIT_REQUEST
(APPLICATION      => ‘IBY’,
PROGRAM          => ‘IBY_FD_PAYMENT_FORMAT’,
DESCRIPTION      => ‘Format Payment Instructions’,
START_TIME       => SYSDATE,
SUB_REQUEST      => FALSE,
ARGUMENT1        => 46602,
ARGUMENT2        => ‘N’
);

IF LN_REQUEST_ID = 0
THEN
INSERT INTO OAF_DEBUG_TEMP
VALUES (SYSDATE, 1, ‘Concurrent Request Failed to Submit.’, NULL);
ELSE
INSERT INTO OAF_DEBUG_TEMP
VALUES (SYSDATE, 1, ‘Concurrent Request Successfully  Submitted’,
LN_REQUEST_ID);
END IF;

COMMIT;
EXCEPTION
WHEN OTHERS
THEN
INSERT INTO OAF_DEBUG_TEMP
VALUES (SYSDATE, 1, ‘Inside Exception’, NULL);
END;

Thank you.

 

Bulk Loading of Lookups in Oracle Apps

When you have some lookups you can insert it into oracle apps by going to lookups and insert ahead. But when you have data in a large quantity it is a duplication to type and insert. We have some fnd_lookup_values_pkg, fnd_lookup_types_pkg API’s to rescue

So now lets go Step by Step to insert

The Steps are

Step-1 :  To identify the UI(oracle forms) from where user can be able to visualize

Step – 2 Digging into query

Step – 3 Inserting data into oracle seeded table
 Step – 1  : To identify the UI(oracle forms) from where user can be able to visualise

The path for navigation is

Application Developer -> Application  – > Lookups -> Common and you will get a page as shown below

1

Now Just go to insert mode by pressing F11 and put % symbol to Query and press Ctrl+F11 to Query.

2.

Now you will have records populated. Just click a record and go to Help -> Record History

You will get the table/view name from where the data is populated.

3.

If suppose you are not able to view the record history then try this

Help -> Diagnostics -> Examine

Then you will get the below screen and set the LOV’s as shown

Block  : System

Field   : Last_query

4.

And in Value you can get the SQL query. With this we can start our hard way.

Step – 2 Digging into query

Now we have to dig down the Query View Scripts from where the data comes.

SELECT text

FROM all_views

WHERE view_name = ‘%FND_LOOKUP_TYPES_VL%’;

By the above query we can get the scripts for the view.

Now the scripts will give a query which has joins to tables by this we will get to a idea that where it fetches the data.

Now on analyzing we get data from fnd_lookup_types table and on analysing the values we get the data from  fnd_lookup_values table.

Step – 3 Inserting data into oracle seeded table

When the requirement is to insert your data into oracle seeded table you have two options recommended. One is by Interface and another by API.

Here we are going to see the values to be inserted by API for a particular table.

API -> fnd_lookup_values_pkg, fnd_lookup_types_pkg

Table -> fnd_lookup_values, fnd_lookup_types

To insert into fnd_lookup_values, fnd_lookup_types  table which is oracle seeded we can use the API  fnd_lookup_values_pkg, fnd_lookup_types_pkg

which is a package.

Note:

To view the contents of the package

1 If you use TOAD place the cursor in the pkg  and press F4

2 Use the Below Query to find the content

select * from user_source where name=upper(‘fnd_lookup_values_pkg’);

select * from user_source where name=upper(‘fnd_lookup_types_pkg’);

Below is the script for inserting data into table by using fnd_lookup_values _pkg

DECLARE

XROW     ROWID;  –You cant directly give Row Id to the x_rowid parameter,

ln_rowid1   ROWID;

cursor c is     select * from xx_dept; –My Custom Query to have fetch in my custom table

cursor c1 is

select distinct lookup_type from xx_dept;

BEGIN

for i in c1   — For loop for Inserting lookup types

loop

fnd_lookup_types_pkg.insert_row (x_rowid                    => XROW,

x_lookup_type              => i.lookup_type,  –cursor values

x_security_group_id        => 0,

x_view_application_id      => 3,

x_application_id           => 20187,

x_customization_level      => ‘U’,

x_meaning                  => i.lookup_type,

x_description              => i.lookup_type,

x_creation_date            => SYSDATE,

x_created_by               => 1318,

x_last_update_date         => SYSDATE,

x_last_updated_by          => 0,

x_last_update_login        => 5513095

);

DBMS_OUTPUT.put_line (XROW);

end loop;  –Loop ends here

commit;

for i in c — For loop for Inserting lookup values

loop

fnd_lookup_values_pkg.insert_row (x_rowid                    => ln_rowid1,

x_lookup_type              => I.LOOKUP_TYPE,  –Secomd cursor values

x_security_group_id        => 0,

x_view_application_id      => 3,

x_lookup_code              => I.LOOKUP_CODE,

x_tag                      => NULL,

x_attribute_category       => NULL,

x_attribute1               => NULL,

x_attribute2               => NULL,

x_attribute3               => NULL,

x_attribute4               => NULL,

x_enabled_flag             => ‘Y’,

x_start_date_active        => TO_DATE (’01-JAN-1950′,

‘DD-MON-YYYY’

),

x_end_date_active          => NULL,

x_territory_code           => NULL,

x_attribute5               => NULL,

x_attribute6               => NULL,

x_attribute7               => NULL,

x_attribute8               => NULL,

x_attribute9               => NULL,

x_attribute10              => NULL,

x_attribute11              => NULL,

x_attribute12              => NULL,

x_attribute13              => NULL,

x_attribute14              => NULL,

x_attribute15              => NULL,

x_meaning                  => I.LOOKUP_MEANING,

x_description              => NULL,

x_creation_date            => SYSDATE,

x_created_by               => 1318,

x_last_update_date         => SYSDATE,

x_last_updated_by          => 1318,

x_last_update_login        => 5513095

);

DBMS_OUTPUT.put_line (ln_rowid1);

end loop;

exception

when others then dbms_output.put_line(‘Exception Occured’);

commit;

END;