pragma autonomous_transaction real time example

Autonomous Transactions  with example




PROCEDURE p_update_logcounts(p_file_id      IN NUMBER
                                 ,p_line_count   IN NUMBER
                                 ,p_sucess_count IN NUMBER
                                 ,p_error_count  IN NUMBER)
    AS
    pragma autonomous_transaction;
    BEGIN
       UPDATE pfoa_in_out_files piof
          SET piof.number_of_lines= NVL(piof.number_of_lines,0) +p_line_count
             ,piof.number_of_lines_accepted=NVL(piof.number_of_lines_accepted,0) + p_sucess_count
             ,piof.number_of_lines_rejected= NVL(piof.number_of_lines_rejected,0) + p_error_count
        WHERE piof.file_id= p_file_id;

    COMMIT;
    END p_update_logcounts;



Autonomous Transactions

Autonomous transactions allow you to leave the context of the calling transaction, perform an independant transaction, and return to the calling transaction without affecting it's state. The autonomous transaction has no link to the calling transaction, so only commited data can be shared by both transactions.
The following types of PL/SQL blocks can be defined as autonomous transactions:
  • Stored procedures and functions.
  • Local procedures and functions defined in a PL/SQL declaration block.
  • Packaged procedures and functions.
  • Type methods.
  • Top-level anonymous blocks.
The easiest way to understand autonomous transactions is to see them in action. To do this, we create a test table and populate it with two rows. Notice that the data is not commited.
CREATE TABLE at_test (
  id           NUMBER       NOT NULL,
  description  VARCHAR2(50) NOT NULL
);

INSERT INTO at_test (id, description) VALUES (1, 'Description for 1');
INSERT INTO at_test (id, description) VALUES (2, 'Description for 2');

SELECT * FROM at_test;

        ID DESCRIPTION
---------- --------------------------------------------------
         1 Description for 1
         2 Description for 2

2 rows selected.

SQL>
Next, we insert another 8 rows using an anonymous block declared as an autonomous transaction, which contains a commit statement.
DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  FOR i IN 3 .. 10 LOOP
    INSERT INTO at_test (id, description)
    VALUES (i, 'Description for ' || i);
  END LOOP;
  COMMIT;
END;
/

PL/SQL procedure successfully completed.

SELECT * FROM at_test;

        ID DESCRIPTION
---------- --------------------------------------------------
         1 Description for 1
         2 Description for 2
         3 Description for 3
         4 Description for 4
         5 Description for 5
         6 Description for 6
         7 Description for 7
         8 Description for 8
         9 Description for 9
        10 Description for 10

10 rows selected.

SQL>
As expected, we now have 10 rows in the table. If we now issue a rollback statement we get the following result.
ROLLBACK;
SELECT * FROM at_test;

        ID DESCRIPTION
---------- --------------------------------------------------
         3 Description for 3
         4 Description for 4
         5 Description for 5
         6 Description for 6
         7 Description for 7
         8 Description for 8
         9 Description for 9
        10 Description for 10

8 rows selected.

SQL>
The 2 rows inserted by our current session (transaction) have been rolled back, while the rows inserted by the autonomous transactions remain. The presence of the PRAGMA AUTONOMOUS_TRANSACTION compiler directive made the anonymous block run in its own transaction, so the internal commit statement did not affect the calling session. As a result rollback was still able to affect the DML issued by the current statement.
Autonomous transactions are commonly used by error logging routines, where the error messages must be preserved, regardless of the the commit/rollback status of the transaction. For example, the following table holds basic error messages.
CREATE TABLE error_logs (
  id             NUMBER(10)     NOT NULL,
  log_timestamp  TIMESTAMP      NOT NULL,
  error_message  VARCHAR2(4000),
  CONSTRAINT error_logs_pk PRIMARY KEY (id)
);

CREATE SEQUENCE error_logs_seq;
We define a procedure to log error messages as an autonomous transaction.
CREATE OR REPLACE PROCEDURE log_errors (p_error_message  IN  VARCHAR2) AS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO error_logs (id, log_timestamp, error_message)
  VALUES (error_logs_seq.NEXTVAL, SYSTIMESTAMP, p_error_message);
  COMMIT;
END;
/
The following code forces an error, which is trapped and logged.
BEGIN
  INSERT INTO at_test (id, description)
  VALUES (998, 'Description for 998');

  -- Force invalid insert.
  INSERT INTO at_test (id, description)
  VALUES (999, NULL);
EXCEPTION
  WHEN OTHERS THEN
    log_errors (p_error_message => SQLERRM);
    ROLLBACK;
END;
/

PL/SQL procedure successfully completed.

SELECT * FROM at_test WHERE id >= 998;

no rows selected

SELECT * FROM error_logs;

        ID LOG_TIMESTAMP
---------- ---------------------------------------------------------------------------
ERROR_MESSAGE
----------------------------------------------------------------------------------------------------
         1 28-FEB-2006 11:10:10.107625
ORA-01400: cannot insert NULL into ("TIM_HALL"."AT_TEST"."DESCRIPTION")


1 row selected.

SQL>
From this we can see that the LOG_ERRORS transaction was separate to the anonymous block. If it weren't, we would expect the first insert in the anonymous block to be preserved by the commit statement in the LOG_ERRORS procedure.
Be careful how you use autonomous transactions. If they are used indiscriminately they can lead to deadlocks, and cause confusion when analyzing session trace. To hammer this point home, here's a quote from Tom Kyte.
"... in 999 times out of 1000, if you find yourself "forced" to use an autonomous transaction - it likely means you have a serious data integrity issue you haven't thought about.
Where do people try to use them?
  • in that trigger that calls a procedure that commits (not an error logging routine). Ouch, that has to hurt when you rollback.
  • in that trigger that is getting the mutating table constraint. Ouch, that hurts *even more*
Error logging - OK.
Almost everything else - not OK."

Customer Import using API's

Following API's are used for creating customers

1) The first step is to create Party. hz_party_v2pub.create_organization is used to create a party.
2) Once party is created then the customer accounts should be created. hz_cust_account_v2pub.create_cust_account API is used to create Customer Accounts. The p_organization_rec should have party information. The orig_system_reference for p_cust_account_Rec should be same as parties orig_system_reference.
3) Now that party and accounts are created, customer account sites and its uses should be created. But before that location and party_sites should be created and attached to party.
4) hz_location_v2pub.create_location API is used to create location. This is a simple API that takes address table type as input and returns location_id as a OUT parameter.
5) API hz_party_site_v2pub.create_party_site is used to create party_site. The party_id created in step 1 and location_id created in step 4 is passed in the party_site_rec parameter. This will return party_site_id as a OUT parameter.
6) Now that we have created the party_sites, its time to create customer site using APIhz_cust_account_site_v2pub.create_cust_acct_site. The cust_account_id created in step 2 and party_site_id created in step 5 is inputted in the cust_acct_site_rec record type. This returns cust_acct_site_id as a OUT parameter.
7) The site use(SHIP_TO, BILL_TO etc.) can be created using API hz_cust_account_site_v2pub.create_cust_site_use. The cust_acct_site_id created in step 6
8) For BILL_TO the customer profiles can be created using API hz_customer_profile_v2pub.create_customer_profile

z_party_contact_v2pub.create_org_contact - API to create a Contact person for an organization in Oracle TCA

API : hz_party_contact_v2pub.create_org_contact
TEST INSTANCE: R12.1.3
DETAILS OF THE ROUTINE:
This API is used to create a Contact person for an organization or person. The API creates a record in the HZ_ORG_CONTACTS table. It additionally creates a relationship record in the HZ_RELATIONSHIPS table using the contact person as the subject, the organization or person as object and relationship type and code passed by the caller. A reverse relationship record is also created at the same time. There is a de-normalized party record of type PARTY_RELATIONSHIP created for the relationship depending on relationship type set up for the relationship that is being used for the org contact.
NOTES:
·       Value for department_code must be a value defined in lookup type DEPARTMENT_TYPE
·       Subject_id. Please enter person id value from HZ_PARTIES
·       Object_id. Please enter organization id value from HZ_PARTIES
·       Check the HZ_RELATIONSHIP_TYPES Table for details related to relationship_type,relationship_code


SCRIPT:


SET SERVEROUTPUT ON;
DECLARE
p_org_contact_rec HZ_PARTY_CONTACT_V2PUB.ORG_CONTACT_REC_TYPE;
x_org_contact_id  NUMBER;
x_party_rel_id    NUMBER;
x_party_id        NUMBER;
x_party_number    VARCHAR2(2000);
x_return_status   VARCHAR2(2000);
x_msg_count       NUMBER;
x_msg_data        VARCHAR2(2000);

BEGIN
-- Setting the Context --
mo_global.init('AR');
fnd_global.apps_initialize ( user_id      => 1318
                            ,resp_id      => 50559
                            ,resp_appl_id => 222);
mo_global.set_policy_context('S',204);
fnd_global.set_nls_context('AMERICAN');

-- Initializing the Mandatory API parameters
p_org_contact_rec.department_code                  := 'OPERATIONS';
p_org_contact_rec.job_title                        := 'Chief Financial';
p_org_contact_rec.decision_maker_flag              := 'Y';
p_org_contact_rec.job_title_code                   := 'CFO';
p_org_contact_rec.created_by_module                := 'BO_API';
p_org_contact_rec.party_rel_rec.subject_id         :=  530684;
p_org_contact_rec.party_rel_rec.subject_type       := 'PERSON';
p_org_contact_rec.party_rel_rec.subject_table_name := 'HZ_PARTIES';
p_org_contact_rec.party_rel_rec.object_id          :=  530682;
p_org_contact_rec.party_rel_rec.object_type        := 'ORGANIZATION';
p_org_contact_rec.party_rel_rec.object_table_name  := 'HZ_PARTIES';
p_org_contact_rec.party_rel_rec.relationship_code  := 'CONTACT_OF';
p_org_contact_rec.party_rel_rec.relationship_type  := 'CONTACT';
p_org_contact_rec.party_rel_rec.start_date         :=  SYSDATE;

DBMS_OUTPUT.PUT_LINE('Calling the API hz_party_contact_v2pub.create_org_contact');

hz_party_contact_v2pub.create_org_contact
             (
              p_init_msg_list    => FND_API.G_TRUE,
              p_org_contact_rec  => p_org_contact_rec,
              x_org_contact_id   => x_org_contact_id,
              x_party_rel_id     => x_party_rel_id,
              x_party_id         => x_party_id,
              x_party_number     => x_party_number,
              x_return_status    => x_return_status,
              x_msg_count        => x_msg_count,
              x_msg_data         => x_msg_data
                   );



IF x_return_status = fnd_api.g_ret_sts_success THEN
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('Creation of Org contact is Successful ');
    DBMS_OUTPUT.PUT_LINE('Output information ....');
    DBMS_OUTPUT.PUT_LINE('x_party_rel_id   = '||x_party_rel_id);
    DBMS_OUTPUT.PUT_LINE('x_org_contact_id = '||x_org_contact_id);
    DBMS_OUTPUT.PUT_LINE('x_party_id       = '||x_party_id);
    DBMS_OUTPUT.PUT_LINE('x_party_number   = '||x_party_number);
     
ELSE
    DBMS_OUTPUT.PUT_LINE ('Creation of Org Contact failed:'||x_msg_data);
    ROLLBACK;
    FOR i IN 1 .. x_msg_count
    LOOP
      x_msg_data := FND_msg_pub.get( p_msg_index => i, p_encoded => 'F');
      dbms_output.put_line( i|| ') '|| x_msg_data);
    END LOOP;
END IF;

DBMS_OUTPUT.PUT_LINE('Completion of API hz_party_contact_v2pub.create_org_contact');
END;