Error : timeout occurred while waiting to lock object

I am getting error timeout occurred while waiting to lock object when i Compile a package. I want to remove the lock.

SELECT * FROM v$access  where object='package_name'

Take that SID and pass it to the below query and take sid and serial#

SELECT s.inst_id,
       s.sid,
       s.serial#,
       p.spid,
       s.username,
       s.program
FROM   gv$session s
       JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE  s.type != 'BACKGROUND' and s.sid=?

Kill Session

Use the below  query to kill the corresponding session

ALTER SYSTEM KILL SESSION 'sid,serial#'

Get Financial Year Week no for given date

Financial Week start from 01-Apr each year. So for 01-Apr of that year i should get week number as 1.

select
to_char(SYSDATE) the_date,
to_char(add_months(SYSDATE,-3),'WW') fiscal_week
from dual

Change Password using API for e-Business Suite Login

You can change password and also disable the change password which will be asked when the password change limit days has crossed  as given below.


No need to pass the old password for the below.

begin
fnd_user_pkg.updateuser(
                x_user_name => 'fnd_user.User_NAME',
                x_unencrypted_password => 'password',
                x_password_date => (sysdate+31)
                );
commit;
end;

Bulk Collect Example

Declare
  Type emprec IS RECORD(
    empno Number,
    name  varchar2(40));
  TYPE emptab IS TABLE OF emprec;
  cursor empcur IS
    select * from emp1;
  emptable emptab;

BEGIN
  OPEN empcur;
  FETCH empcur BULK COLLECT
    INTO emptable;

  if (nvl(emptable.LAST 0) > 0) then
    FOR i in emptable.FIRST .. emptable.LAST LOOP
      BEGIN
           INSERT INTO EMP1(empno,name) values (emptable(i).empno,emptable(i).name);
      END;
    END LOOP;
  END IF;

  COMMIT;
 END;

Connect By Root Example

I have a Table person_map which will have 2 columns,
Person_id,Mapped_person_id

For Example
Person_ID Mapped_Person_id
1 2
2 3
3 4
5 6
6 7

I require a query which will give output like below
1 2
1 3
1 4
2 3
2 4
3 4
5 6
5 7
6 7

Scripts
Create table Person (person_id Number, Mapped_person_id Number);

INSERT INTO PERSON VALUES ( 1,2);
INSERT INTO PERSON VALUES ( 2,3);
INSERT INTO PERSON VALUES ( 3,4);
INSERT INTO PERSON VALUES ( 5,6);
INSERT INTO PERSON VALUES ( 6,7);

Solution:

SELECT level, person_id AS original_person_id, 

CONNECT_BY_ROOT person_id AS root_person_id, mapped_person_id
      FROM person
 CONNECT BY person_id = prior mapped_person_id;

Error - Another user has updated Opportunity header record in AS_OPPORTUNITY_PUB.Update_Opp_Header

Cause: 
While Updating opportunities using AS_OPPORTUNITY_PUB.Update_Opp_Header I am getting the below error

Another user has updated Opportunity header record. please re-query to see the changes.

The Code is 
headerRec.lead_id := leadId;
headerRec.owner_salesforce_id := resourceid;
headerRec.owner_sales_group_id :=salesGroupid;
headerRec.status :='Dropped';
headerRec.status_code :='Dropped';
headerRec.close_reason:='NOT_SPECIFIED';
headerRec.last_update_date := sysdate;
headerRec.last_updated_by := FND_GLOBAL.USER_ID;
-- dbms_output.put_line (headerRec.last_update_date );
AS_OPPORTUNITY_PUB.Update_Opp_Header(p_api_version_number => '2.0',
p_commit => fnd_api.G_FALSE,
p_header_rec => headerRec,
p_check_access_flag => 'Y',
p_admin_flag => 'N',
p_admin_group_id => null,
p_identity_salesforce_id => resourceid,
p_partner_cont_party_id => null,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_lead_id => x_lead_id);
DBMS_OUTPUT.put_line('--' || x_return_status);
IF (x_return_status != 'S') THEN
IF (fnd_msg_pub.count_msg > 0) THEN
FOR i IN 1 .. fnd_msg_pub.count_msg LOOP
fnd_msg_pub.get(p_msg_index => i,
p_encoded => 'F',
p_data => x_msg_data,
p_msg_index_out => x_msg_count);
DBMS_OUTPUT.put_line(x_msg_data);
END LOOP;

Solution:

  Get the Last_updated_date from the opportunity table for the selected opportunity and pass it to the headerRec.last_update_date

SELECT last_update_date
FROM as_leads_all
WHERE lead_id = opportunity_id;

Error - Close reason with value: does not exist. in Update Opportunities

Cause:
While Updating opportunities using AS_OPPORTUNITY_PUB.Update_Opp_Header I am getting the below error

Close reason with value: BUYING DEFERRED does not exist.

The code is


headerRec.lead_id := leadId;
headerRec.owner_salesforce_id := resourceid;
headerRec.owner_sales_group_id :=salesGroupid;
headerRec.status :='Dropped';
headerRec.status_code :='Dropped';
headerRec.close_reason:='BUYING DEFERRED';
headerRec.last_update_date := lastUpdatedDate;
headerRec.last_updated_by := FND_GLOBAL.USER_ID;
-- dbms_output.put_line (headerRec.last_update_date );
AS_OPPORTUNITY_PUB.Update_Opp_Header(p_api_version_number => '2.0',
p_commit => fnd_api.G_FALSE,
p_header_rec => headerRec,
p_check_access_flag => 'Y',
p_admin_flag => 'N',
p_admin_group_id => null,
p_identity_salesforce_id => resourceid,
p_partner_cont_party_id => null,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_lead_id => x_lead_id);
DBMS_OUTPUT.put_line('--' || x_return_status);
IF (x_return_status != 'S') THEN
IF (fnd_msg_pub.count_msg > 0) THEN
FOR i IN 1 .. fnd_msg_pub.count_msg LOOP
fnd_msg_pub.get(p_msg_index => i,
p_encoded => 'F',
p_data => x_msg_data,
p_msg_index_out => x_msg_count);
DBMS_OUTPUT.put_line(x_msg_data);

Solution:

You have to pass one of the values from column lookup_code from below query

select lookup_code from fnd_lookup_values  where lookup_type='CLOSE_REASON'

How to find Table Last Altered Date/Time in Oracle

select * from dba_objects
where owner='<Table Schema Owner>'
and object_name ='<Table Name>'