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

How to find Oracle Applications Login URL from database

Execute either of the following query logging into Oracle Applications database from "APPS" user:-

1.
SELECT home_url
  FROM icx_parameters;


2.

SELECT profile_option_value 

  FROM fnd_profile_option_values 

 WHERE profile_option_id= 

   (SELECT profile_option_id                             

      FROM fnd_profile_options WHERE profile_option_name = 'APPS_FRAMEWORK_AGENT')                              

   AND level_value = 0;

How to select profile option values for a profile option name with setup level details

Following query provides details of each profile option value at Site, Application, Responsibility, User, Organization, Server levels. The output gives the profile option name, profile option value,level at which each profile value is set(site, application, responsibility, user etc.).

Note:- Standard Package "FND_PROFILE_OPTION_VALUES_PKG" can be used to
            insert/update/delete profile option values at various levels.

Application Name,Responsibility Name, User Name if a profile is setup at each of the respective level:

Parameters:

• User_Profile_Option_Name(can give partial value)

• P_level_id(Values should be as mentioned from the below level_ids)

•     10001  -  Site           
•     10002  -  Application
•     10003  -  Responsibility
•     10004  -  User
•     10005  -  Server
•     10007  -  Organization

SELECT fpot.user_profile_option_name AS "USER_PROFILE_OPTION_NAME",

       DECODE (level_id,

               '10001', 'Site',

               '10002', 'Application',

               '10003', 'Responsibility',

               '10004', 'User',

               '10005', 'Server',

               '10007', 'Organization'

              ) AS "LEVEL",

       fpov.profile_option_value AS "Value", fpo.profile_option_name,

       SUBSTR (fat.application_name, 1, 20) AS "Application",

       (SELECT responsibility_name

          FROM fnd_responsibility_tl frt

         WHERE frt.responsibility_id = fpov.level_value

           AND fpov.level_id = 10003) "Responsibility",

       (SELECT user_name

          FROM fnd_user fu

         WHERE user_id = fpov.level_value

           AND fpov.level_id = 10004) "User"

  FROM fnd_profile_options fpo,

       fnd_profile_options_tl fpot,

       fnd_application_tl fat,

       fnd_profile_option_values fpov

 WHERE fpo.application_id = fat.application_id

   AND fpov.application_id = fat.application_id

   AND fpov.profile_option_id = fpo.profile_option_id

   AND fpo.profile_option_name = fpot.profile_option_name

   AND fpov.level_id = NVL (&p_level_id, fpov.level_id)

   AND UPPER (fpot.user_profile_option_name) LIKE

          NVL (UPPER ('%&User_Profile_Option_Name%'),

               UPPER (fpot.user_profile_option_name)

              );