iProcurement Bulk Inactivation/Deletion of Catalog Items

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

To delete items from iProcurement Catalog/Category tables we can use the following APIs

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

--API to delete items from Catalog

    ICX_POR_DELETE_CATALOG.delete_items_in_catalog (p_catalog_name        IN VARCHAR2);

   

    --API to delete items from Category

    ICX_POR_DELETE_CATALOG.delete_items_in_category (

                                    errbuf            OUT NOCOPY VARCHAR2,

                                    retcode           OUT NOCOPY VARCHAR2,

                                    p_rt_category_id   IN NUMBER,

                                    p_category_key     IN VARCHAR2);

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

To delete Item-Category assignment use -  INV_ITEM_CATEGORY_PUB.Delete_Category_Assignment standard API. Following is the sample code:-

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

DECLARE

   l_return_status       VARCHAR2 (80);

   l_error_code          NUMBER;

   l_msg_count           NUMBER;

   l_msg_data            VARCHAR2 (80);

   l_category_id         NUMBER;

   l_category_set_id     NUMBER;

   l_inventory_item_id   NUMBER;

   l_organization_id     NUMBER;

BEGIN

   SELECT mcs_tl.category_set_id

     INTO l_category_set_id

     FROM mtl_category_sets_tl mcs_tl

    WHERE mcs_tl.category_set_name = 'INV_COLORS_SET';

   SELECT mcb.category_id

     INTO l_category_id

     FROM mtl_categories_b mcb

    WHERE mcb.segment1 = 'RED'

      AND mcb.structure_id =

             (SELECT mcs_b.structure_id

                FROM mtl_category_sets_b mcs_b

               WHERE mcs_b.category_set_id =

                                 (SELECT mcs_tl.category_set_id

                                    FROM mtl_category_sets_tl mcs_tl

                                   WHERE category_set_name = 'INV_COLORS_SET'));

   SELECT DISTINCT (inventory_item_id)

              INTO l_inventory_item_id

              FROM mtl_system_items_kfv msik

             WHERE msik.concatenated_segments = 'Category_Item';

   SELECT organization_id

     INTO l_organization_id

     FROM mtl_parameters

    WHERE organization_code = &P_ORG_CODE;

   inv_item_category_pub.delete_category_assignment

                                  (p_api_version            => 1.0,

                                   p_init_msg_list          => fnd_api.g_false,

                                   p_commit                 => fnd_api.g_true,

                                   x_return_status          => l_return_status,

                                   x_errorcode              => l_error_code,

                                   x_msg_count              => l_msg_count,

                                   x_msg_data               => l_msg_data,

                                   p_category_id            => l_category_id,

                                   p_category_set_id        => l_category_set_id,

                                   p_inventory_item_id      => l_inventory_item_id,

                                   p_organization_id        => l_organization_id

                                  );

END;

How to create a Quote using ASO_QUOTE_PUB

DECLARE
   -- pragma autonomous_transaction;
   p_api_version_number        NUMBER;
   p_init_msg_list                    VARCHAR2 (32767);
   p_commit                             VARCHAR2 (32767);
   p_control_rec                       aso_quote_pub.control_rec_type;
   p_qte_header_rec                 aso_quote_pub.qte_header_rec_type;
   p_hd_price_attributes_tbl    aso_quote_pub.price_attributes_tbl_type;
   p_hd_payment_tbl               aso_quote_pub.payment_tbl_type;
   p_hd_shipment_rec             aso_quote_pub.shipment_rec_type;
   p_hd_freight_charge_tbl     aso_quote_pub.freight_charge_tbl_type;
   p_hd_tax_detail_tbl            aso_quote_pub.tax_detail_tbl_type;
   p_qte_line_tbl                     aso_quote_pub.qte_line_tbl_type;
   p_qte_line_dtl_tbl               aso_quote_pub.qte_line_dtl_tbl_type;
   p_line_attr_ext_tbl              aso_quote_pub.line_attribs_ext_tbl_type;
   p_line_rltship_tbl                aso_quote_pub.line_rltship_tbl_type;
   p_price_adjustment_tbl       aso_quote_pub.price_adj_tbl_type;
   p_price_adj_attr_tbl            aso_quote_pub.price_adj_attr_tbl_type;
   p_price_adj_rltship_tbl       aso_quote_pub.price_adj_rltship_tbl_type;
   p_ln_price_attributes_tbl    aso_quote_pub.price_attributes_tbl_type;
   p_ln_payment_tbl               aso_quote_pub.payment_tbl_type;
   p_ln_shipment_tbl              aso_quote_pub.shipment_tbl_type;
   p_ln_freight_charge_tbl     aso_quote_pub.freight_charge_tbl_type;
   p_ln_tax_detail_tbl            aso_quote_pub.tax_detail_tbl_type;
   x_qte_header_rec              aso_quote_pub.qte_header_rec_type;
   x_qte_line_tbl                   aso_quote_pub.qte_line_tbl_type;
   x_qte_line_dtl_tbl             aso_quote_pub.qte_line_dtl_tbl_type;
   x_hd_price_attributes_tbl aso_quote_pub.price_attributes_tbl_type;
   x_hd_payment_tbl            aso_quote_pub.payment_tbl_type;
   x_hd_shipment_rec           aso_quote_pub.shipment_rec_type;
   x_hd_freight_charge_tbl   aso_quote_pub.freight_charge_tbl_type;
   x_hd_tax_detail_tbl          aso_quote_pub.tax_detail_tbl_type;
   x_line_attr_ext_tbl           aso_quote_pub.line_attribs_ext_tbl_type;
   x_line_rltship_tbl             aso_quote_pub.line_rltship_tbl_type;
   x_price_adjustment_tbl    aso_quote_pub.price_adj_tbl_type;
   x_price_adj_attr_tbl         aso_quote_pub.price_adj_attr_tbl_type;
   x_price_adj_rltship_tbl    aso_quote_pub.price_adj_rltship_tbl_type;
   x_ln_price_attributes_tbl aso_quote_pub.price_attributes_tbl_type;
   x_ln_payment_tbl            aso_quote_pub.payment_tbl_type;
   x_ln_shipment_tbl           aso_quote_pub.shipment_tbl_type;
   x_ln_freight_charge_tbl  aso_quote_pub.freight_charge_tbl_type;
   x_ln_tax_detail_tbl         aso_quote_pub.tax_detail_tbl_type;
   v_party_id                      NUMBER;
   v_account_id                  NUMBER;
   v_user_id                        NUMBER;
   v_org_id                         NUMBER;
   v_party_site_id              NUMBER;
   v_order_type_id            NUMBER;
   v_quote_name               VARCHAR2 (200);
   v_currency                    VARCHAR2 (10);
   x_return_status             VARCHAR2 (1);
   x_msg_data                  VARCHAR2 (4000);
   x_msg_count                NUMBER;
   x_cost_quote_header_id      VARCHAR2 (1000);
   p_quote_header_id               NUMBER := &p_quote_hdr_id;
   v_pricelist_id                        NUMBER;
   v_item_num                          VARCHAR2 (30) := &p_item_num;
   v_inv_org_code                    VARCHAR2 (50) := &p_invorg_code;
   v_item_id                             NUMBER;
   v_inv_org_id                        NUMBER;
   v_resp_id                              NUMBER := fnd_profile.value('RESP_ID');
   v_appl_id                              NUMBER := fnd_profile.value('APPL_ID');
BEGIN
   fnd_global.APPS_INITIALIZE (v_user_id, v_resp_id, v_appl_id);
   p_api_version_number := 1.0;
   p_init_msg_list := fnd_api.g_true;
   p_commit := fnd_api.g_false;

   -- P_CONTROL_REC := NULL;  Modify the code to initialize this parameter

   SELECT cust_party_id,
          order_type_id,
          SUBSTR (quote_name, 1, 200),
          org_id,
          created_by,
          currency_code,
          quote_source_code,
          resource_id,
          resource_grp_id
     INTO v_party_id,
          v_order_type_id,
          v_quote_name,
          v_org_id,
          v_user_id,
          v_currency,
          p_qte_header_rec.quote_source_code,
          p_qte_header_rec.resource_id,
          p_qte_header_rec.resource_grp_id
     FROM aso_quote_headers_all
    WHERE quote_header_id = p_quote_header_id;

   DBMS_OUTPUT.put_line ('after party id' || v_party_id);

   -- Find the cust account primary bill site's party site id
   BEGIN
      SELECT hca.cust_account_id, cas.party_site_id
        INTO v_account_id, v_party_site_id
        FROM hz_parties party,
             hz_cust_accounts hca,
             hz_cust_acct_sites_all cas,
             hz_cust_site_uses_all csu
       WHERE     party.party_id = v_party_id
             AND hca.party_id = party.party_id
             AND hca.cust_account_id = v_account_id
             AND cas.cust_account_id = hca.cust_account_id
             AND cas.status = 'A'
             AND cas.cust_acct_site_id = csu.cust_acct_site_id
             AND csu.site_use_code = 'BILL_TO'
             AND csu.primary_flag = 'Y'
             AND csu.status = 'A'
             AND csu.org_id = v_org_id;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         BEGIN
            SELECT ps.party_site_id
              INTO v_party_site_id
              FROM hz_parties party,
                   hz_party_sites ps,
                   hz_party_site_uses psu
             WHERE     party.party_id = v_party_id
                   AND party.party_id = ps.party_id
                   AND ps.party_site_id = psu.party_site_id
                   AND ps.status = 'A'
                   AND psu.site_use_type = 'BILL_TO'
                   AND psu.primary_per_type = 'Y'
                   AND psu.status = 'A';
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               SELECT ps.party_site_id
                 INTO v_party_site_id
                 FROM hz_parties party, hz_party_sites ps
                WHERE     party.party_id = v_party_id
                      AND party.party_id = ps.party_id
                      AND ps.status = 'A'
                      AND ROWNUM < 2;
         END;
   END;

   -- p_qte_header_rec := NULL;  Modify the code to initialize this parameter
   -----------------------------------------------------
   -- build the header and shipment rec to pass to Oracle's API
   -----------------------------------------------------
   v_user_id := fnd_global.user_id;
   v_org_id := fnd_global.org_id;
   p_qte_header_rec.created_by := v_user_id;
   p_qte_header_rec.last_updated_by := v_user_id;
   p_qte_header_rec.org_id := v_org_id;
   p_qte_header_rec.quote_source_code := 'Order Capture Quotes';

   p_qte_header_rec.cust_party_id := v_party_id;
   p_qte_header_rec.cust_account_id := v_account_id;
   p_qte_header_rec.invoice_to_cust_account_id := v_account_id;
   p_qte_header_rec.invoice_to_party_site_id := v_party_site_id;
   p_qte_header_rec.invoice_to_cust_party_id := v_party_id;
   p_qte_header_rec.quote_name := SUBSTR ('Test Quote', 1, 80);
   p_qte_header_rec.currency_code := v_currency;
   p_qte_header_rec.order_type_id := v_order_type_id;


   --p_qte_header_rec.quote_status_id;
   --COST ESTIMATION
   SELECT quote_status_id
     INTO p_qte_header_rec.quote_status_id
     FROM aso_quote_statuses_vl
    WHERE status_code = 'DRAFT';

   --p_qte_header_rec.price_list_id ;
   --cost price list
   SELECT list_header_id
     INTO p_qte_header_rec.price_list_id
     FROM qp_list_headers_vl
    WHERE list_header_id = v_pricelist_id;

   --AND list_type_code = 'PRL';

   p_hd_shipment_rec.operation_code := 'CREATE';
   p_hd_shipment_rec.ship_to_party_site_id := v_party_site_id;


   SELECT inventory_item_id, organization_id
     INTO v_item_id, v_inv_org_id
     FROM mtl_system_items_b msi, mtl_parameters mp
    WHERE     msi.segment1 = v_item_num
          AND msi.organization_id = mp.organization_id
          AND mp.organization_code = v_inv_org_code;

   ----------------- LINES ---------------------------------
   P_Qte_Line_Tbl (1).organization_id := v_inv_org_id;
   P_Qte_Line_Tbl (1).operation_code := 'CREATE';
   P_Qte_Line_Tbl (1).inventory_item_id := v_item_id;
   P_Qte_Line_Tbl (1).quantity := 1;
   P_Qte_Line_Tbl (1).uom_code := 'EA';
   P_Qte_Line_Tbl (1).line_category_code := 'ORDER';

   x_return_status := NULL;
   x_msg_count := NULL;
   x_msg_data := NULL;

   DBMS_OUTPUT.put_line ('before creating quote');

   apps.aso_quote_pub.create_quote (p_api_version_number,
                                    p_init_msg_list,
                                    p_commit,
                                    p_control_rec,
                                    p_qte_header_rec,
                                    p_hd_price_attributes_tbl,
                                    p_hd_payment_tbl,
                                    p_hd_shipment_rec,
                                    p_hd_freight_charge_tbl,
                                    p_hd_tax_detail_tbl,
                                    p_qte_line_tbl,
                                    p_qte_line_dtl_tbl,
                                    p_line_attr_ext_tbl,
                                    p_line_rltship_tbl,
                                    p_price_adjustment_tbl,
                                    p_price_adj_attr_tbl,
                                    p_price_adj_rltship_tbl,
                                    p_ln_price_attributes_tbl,
                                    p_ln_payment_tbl,
                                    p_ln_shipment_tbl,
                                    p_ln_freight_charge_tbl,
                                    p_ln_tax_detail_tbl,
                                    x_qte_header_rec,
                                    x_qte_line_tbl,
                                    x_qte_line_dtl_tbl,
                                    x_hd_price_attributes_tbl,
                                    x_hd_payment_tbl,
                                    x_hd_shipment_rec,
                                    x_hd_freight_charge_tbl,
                                    x_hd_tax_detail_tbl,
                                    x_line_attr_ext_tbl,
                                    x_line_rltship_tbl,
                                    x_price_adjustment_tbl,
                                    x_price_adj_attr_tbl,
                                    x_price_adj_rltship_tbl,
                                    x_ln_price_attributes_tbl,
                                    x_ln_payment_tbl,
                                    x_ln_shipment_tbl,
                                    x_ln_freight_charge_tbl,
                                    x_ln_tax_detail_tbl,
                                    x_return_status,
                                    x_msg_count,
                                    x_msg_data);
   DBMS_OUTPUT.put_line ('x_qte_header_rec.quote_header_id = '
                                                || x_qte_header_rec.quote_header_id);
   DBMS_OUTPUT.put_line ('x_return_status = ' || x_return_status);
   DBMS_OUTPUT.put_line ('x_msg_count = ' || x_msg_count);
   DBMS_OUTPUT.put_line ('x_msg_data = ' || x_msg_data);

   IF x_qte_header_rec.quote_header_id IS NOT NULL
   THEN
      x_cost_quote_header_id := x_qte_header_rec.quote_header_id;
   END IF;

   FOR i IN 1 .. x_msg_count
   LOOP
      DBMS_OUTPUT.put_line ('Error ' || fnd_msg_pub.get (i, 'F'));
   END LOOP;

   DBMS_OUTPUT.put_line ('end cost api');
END;
/

How to join Projects and Customers, sites using select stmt

SELECT proj.segment1 project#, proj.NAME proj_name,
                hc_bill.account_number bill_to_customer#,
                hp_bill.party_name bill_to_customer,
                hc_ship.account_number ship_to_customer#,
                hp_ship.party_name ship_to_customer,
                hps_bill.party_site_number bill_to_site#,
                hl_bill.address1 bill_to_addr1, hl_bill.city bill_to_city,
                hl_bill.county bill_to_county, hl_bill.state bill_to_state,
                hl_bill.postal_code bill_to_postal_code,
                hps_ship.party_site_number ship_to_site#,
                hl_ship.address1 ship_to_addr1, hl_ship.city ship_to_city,
                hl_ship.county ship_to_county, hl_ship.state ship_to_state,
                hl_ship.postal_code ship_to_postal_code
  FROM pa_projects_all proj,
              pa_project_customers pc,
              hz_cust_accounts hc_bill,
              hz_parties hp_bill,
              hz_cust_accounts hc_ship,
              hz_parties hp_ship,
              hz_cust_acct_sites_all hcs_bill,
              hz_party_sites hps_bill,
              hz_locations hl_bill,
              hz_cust_acct_sites_all hcs_ship,
              hz_party_sites hps_ship,
              hz_locations hl_ship
WHERE TRUNC (SYSDATE) <= NVL (completion_date, TRUNC (SYSDATE))
   AND proj.project_status_code <> 'CLOSED'
   AND proj.project_id = pc.project_id
   AND pc.bill_to_customer_id = hc_bill.cust_account_id
   AND hc_bill.party_id = hp_bill.party_id
   AND pc.ship_to_customer_id = hc_ship.cust_account_id
   AND hc_ship.party_id = hp_ship.party_id
   AND pc.bill_to_address_id = hcs_bill.cust_acct_site_id
   AND hcs_bill.party_site_id = hps_bill.party_site_id
   AND hps_bill.location_id = hl_bill.location_id
   AND pc.ship_to_address_id = hcs_ship.cust_acct_site_id
   AND hcs_ship.party_site_id = hps_ship.party_site_id
   AND hps_ship.location_id = hl_ship.location_id;

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;

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

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;

How to link HZ_PARTY_SITE_USES and HZ_CUST_SITE_USES_ALL

Following SQL statement gives the link between HZ_PARTY_SITE_USES and HZ_CUST_SITE_USES_ALL table:-

SELECT hp.party_id,
       hp.party_name,
       hp.party_number,
       hps.party_site_id,
       hcas.org_id,
       hpsu.site_use_type,
       hps.party_site_number,
       hp.status hz_parties,
       hps.status hz_party_sites,
       hpsu.status hz_party_site_uses,
       hcsu.site_use_code,
       hca.status hz_cust_accounts,
       hcas.status hz_cust_acct_sites_all,
       hcsu.status hz_cust_site_uses_all
FROM hz_parties hp,
     hz_party_sites hps,
     hz_party_site_uses hpsu,
     hz_cust_accounts hca,
     hz_cust_acct_sites_all hcas,
     hz_cust_site_uses_all hcsu
WHERE hp.party_id = hps.party_id
AND   hp.party_id = hca.party_id
AND   hps.party_site_id = hcas.party_site_id
AND   hcsu.cust_acct_site_id = hcas.cust_acct_site_id
AND   hps.party_site_id = hpsu.party_site_id
AND   hcsu.site_use_code = hpsu.site_use_type
AND   hp.party_id = &P_PARTY_ID;

Oracle DBMS_AQ.dequeue sample code

--------------------------------------------------------------------------------------------------------------------
-- Following is a sample code on how to dequeue and insert into a custom table from AQ table:-
--------------------------------------------------------------------------------------------------------------------
DECLARE

   r_dequeue_options       DBMS_AQ.dequeue_options_t;
   r_message_properties   DBMS_AQ.message_properties_t;
   v_message_handle        RAW (16);
   o_payload                     xx_custom_payload_obj;
   lqname                          VARCHAR2 (30 BYTE);
   l_userdata                      XMLTYPE;
   l_usertype                     ANYDATA;
   l_msgid                         RAW (16);
   l_clob                            CLOB;
   l_corrid                         VARCHAR2 (128 BYTE);
   l_recid                           NUMBER := &p_recid;
  
BEGIN
   SELECT q_name,user_prop, msgid, corrid
     INTO lqname,l_usertype, l_msgid, l_corrid
     FROM xx_custom_tbl
    WHERE msgid = &p_msgid;

   r_dequeue_options.dequeue_mode := DBMS_AQ.remove;
   r_dequeue_options.msgid := l_msgid;
   r_dequeue_options.navigation := DBMS_AQ.next_message;
   r_dequeue_options.deq_condition := l_corrid;

   DBMS_AQ.dequeue (queue_name              => lqname,
                                       dequeue_options       => r_dequeue_options,
                                       message_properties   => r_message_properties,
                                        payload                     => l_userdata,
                                        msgid                        => v_message_handle
                                      );

   SELECT XMLTYPE.getclobval (l_userdata)
        INTO l_clob
      FROM DUAL;

   INSERT INTO xx_custom_xml
               (rec_id, file_name, file_struct, xml_data, msgid
               )
        VALUES (l_recid, 'DQTESTFILE.XML', l_clob, l_userdata, l_msgid
               );

   COMMIT;
  DBMS_OUTPUT.PUT_LINE('*** Browsed message is [' || o_payload.message || '] ***');

EXCEPTION
   WHEN OTHERS THEN
      dbms_output.put_line(SQLERRM);
      ROLLBACK;
END;
--------------------------------------------------------------------------------------------------------------------