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>'