Thursday, July 26, 2012

How to get Party/Customer & child entities in XML payload using Business Object and create AQ


--BPEL process calls EBS TCA (Trading Community Architecture) APIs via Apps Adapter to create and update
--Organizations (Parties) and Customers (Accounts) accordingly. TCA provides hz_org_cust_bo_pub API uses
--object types as parameters and is called Business Object(BO) API.

--Following is an example of how to create and update TCA party, customer, customer site etc
--Declare HZ_ORG_CUST_BO business object type variable to pass the customer payload:-

DECLARE
   l_hz_org_cust_bo       hz_org_cust_bo;
   x_payload              XMLTYPE;
   l_organization_id      NUMBER;
   l_orig_system          VARCHAR2 (20);
   l_orig_system_ref      VARCHAR2 (100);
   l_return_status        VARCHAR2 (4000);
   l_msg_count            NUMBER;
   l_org_id               NUMBER;
   l_msg_data             VARCHAR2 (4000);
   v_enqueue_options      DBMS_AQ.ENQUEUE_OPTIONS_T;
   v_Message_Properties   DBMS_AQ.MESSAGE_PROPERTIES_T;
   v_message_handle       RAW (16);
BEGIN
   -- Initialize to set the context
   --fnd_global.apps_initialize(<user_id>, <resp_id>, <resp_appl_id>);

   -- This select statement to derive party and child entity details in HZ_ORG_CUST_BO xmltype payload

   SELECT hp.party_id, hor.orig_system, hor.orig_system_references
     INTO l_organization_id, l_orig_system, l_orig_system_reference
     FROM hz_orig_sys_references hor, hz_parties hp
    WHERE     hor.owner_table_name = 'HZ_PARTIES'
          AND hp.party_id = hor.owner_table_id;



   -- Call API to Create/Update Party/Customer details using Business Object as parameter

   hz_org_cust_bo_pub.get_org_cust_bo (
      p_init_msg_list      => fnd_api.g_true,
      p_organization_id    => l_organization_id,
      p_organization_os    => l_orig_system,
      p_organization_osr   => l_orig_system_reference,
      x_org_cust_obj       => l_hz_org_cust_bo,
      x_return_status      => l_return_status,
      x_msg_count          => l_msg_count,
      x_msg_data           => l_msg_data);

   COMMIT;
   DBMS_OUTPUT.put_line ('x' || l_return_status);
   DBMS_OUTPUT.put_line ('x' || l_msg_data || l_msg_count);
   DBMS_OUTPUT.put_line ('l_organization_id: ' || l_organization_id);

     x_payload:=l_hz_org_cust_bo;

   --Print any error/s
   IF l_msg_count > 1
   THEN
      FOR i IN 1 .. l_msg_count
      LOOP
         DBMS_OUTPUT.put_line (
               i
            || '. '
            || SUBSTR (fnd_msg_pub.get (p_encoded => fnd_api.g_false), 1, 255));
      END LOOP;
   ELSE
      --Create AQ using DBMS_AQ.ENQUEUE standard API
      v_message_properties.correlation := G_MESSAGE_NUMBER;
      v_message_properties.EXPIRATION := DBMS_AQ.NEVER;
      DBMS_AQ.ENQUEUE (Queue_Name           => 'XX_ORG_CUST_ACC_Q',
                       Enqueue_Options      => V_Enqueue_Options,
                       Message_Properties   => V_Message_Properties,
                       Payload              => X_Payload,
                       Msgid                => V_Message_Handle);
      COMMIT;
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (SQLERRM);
END;

Friday, July 20, 2012

How to extend OE_Dependencies_Extn - OEXUDEPB.pls API hook


Some attributes on the quote/sales order are dependent upon the value of other attributes on the same record. If an attribute is changed, either by the user or by the system, any other attribute that is dependent on it will be cleared and then re-defaulted.

The dependencies package - OE_Dependencies (file: $ONT_TOP/patch/115/sql/OEXUDEPB.pls) provides a list of all the dependent attributes on the Ship To Address. 

In order to disable the existing dependencies that we do not need, we need to add code in a simple API hook - package OE_Dependencies_Extn (the file name is $ONT_TOP/patch/115/sql/OEXEDEPB.pls).

Following is a sample code to extend OE_Dependencies_Extn custom API hook:-
--------------------------------------------------------------------
CREATE OR REPLACE PACKAGE BODY OE_Dependencies_Extn
AS
   /* $Header: OEXEDEPB.pls 120.1 RRRR/MM/DD 11:33:12 $ */

   --  Global constant holding the package name

   G_PKG_NAME   CONSTANT VARCHAR2 (30) := 'OE_Dependencies_Extn';

   PROCEDURE Load_Entity_Attributes (
      p_entity_code    IN            VARCHAR2,
      x_extn_dep_tbl      OUT NOCOPY Dep_Tbl_Type)
   IS
      l_index                  NUMBER := 0;
      --
      l_debug_level   CONSTANT NUMBER := oe_debug_pub.g_debug_level;
      --
   BEGIN
      IF p_entity_code = OE_GLOBALS.G_ENTITY_HEADER
      THEN
         --Code for Disabling dependency of Invoice To on Ship To
         x_extn_dep_tbl (l_index).source_attribute :=
            OE_HEADER_UTIL.G_ORDER_TYPE;
         x_extn_dep_tbl (l_index).dependent_attribute :=
            OE_HEADER_UTIL.G_SHIP_FROM_ORG;
         x_extn_dep_tbl (l_index).enabled_flag := 'N';
         l_index := l_index + 1;
         x_extn_dep_tbl (l_index).source_attribute :=
            OE_HEADER_UTIL.G_ORDER_TYPE;
         x_extn_dep_tbl (l_index).dependent_attribute :=
            OE_HEADER_UTIL.G_FOB_POINT;
         x_extn_dep_tbl (l_index).enabled_flag := 'N';
         l_index := l_index + 1;


      ELSIF p_entity_code = OE_GLOBALS.G_ENTITY_LINE
      THEN
         --Code for Disabling dependency of Invoice To on Ship To
         x_extn_dep_tbl (l_index).source_attribute := OE_LINE_UTIL.G_LINE_TYPE;
         x_extn_dep_tbl (l_index).dependent_attribute :=
            OE_LINE_UTIL.G_SHIP_FROM_ORG;
         x_extn_dep_tbl (l_index).enabled_flag := 'N';
         l_index := l_index + 1;
         x_extn_dep_tbl (l_index).source_attribute := OE_LINE_UTIL.G_LINE_TYPE;
         x_extn_dep_tbl (l_index).dependent_attribute :=
            OE_LINE_UTIL.G_FOB_POINT;
         x_extn_dep_tbl (l_index).enabled_flag := 'N';
         l_index := l_index + 1;
         x_extn_dep_tbl (l_index).source_attribute := OE_LINE_UTIL.G_LINE_TYPE;
         x_extn_dep_tbl (l_index).dependent_attribute :=
            OE_LINE_UTIL.G_SUBINVENTORY;
         x_extn_dep_tbl (l_index).enabled_flag := 'N';
         l_index := l_index + 1;
      END IF;

      oe_debug_pub.add ('Exit OE_Dependencies_Extn.LOAD_ENTITY_ATTRIBUTES',
                        1);
   EXCEPTION
      WHEN OTHERS
      THEN
         IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
         THEN
            OE_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'Load_Entity_Attributes');
         END IF;

         RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
   END Load_Entity_Attributes;
END OE_Dependencies_Extn; 

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

Tuesday, July 10, 2012

Create user using FND_USER_PKG.CreateUser

DECLARE
   v_user_name   VARCHAR2 (30) :=  UPPER ('SVISHNUB');
   v_password     VARCHAR2 (30) :=  'ebspro';
   v_session_id    NUMBER            :=  USERENV ('sessionid');
   v_email           VARCHAR2 (30) :=  UPPER ('oracle.ebspro@blogspot.com');
BEGIN
   fnd_user_pkg.createuser (x_user_name                         => v_user_name,
                                             x_owner                                    => NULL,
                                            x_unencrypted_password      => v_password,
                                            x_session_number                 => v_session_id,
                                            x_start_date                           => SYSDATE,
                                            x_end_date                            => NULL,
                                            x_email_address                   => v_email
                                             );
   COMMIT;
   DBMS_OUTPUT.put_line ('Success');
EXCEPTION
   WHEN OTHERS THEN
        DBMS_OUTPUT.put_line ('Failed'|| SUBSTR (SQLERRM, 1, 100));
        ROLLBACK;
END;