SQL Queries for checking Profile Option Values

SQL Queries for checking Profile Option Values

The following queries are useful to get the profile option values of a profile option at site, application, responsibility and user level

1) Obtain Profile Option values for Profile Option name like ‘%Ledger%’ and  Responsibility name like ‘%General%Ledger%’

/* Formatted on 10/15/2015 12:35:37 PM (QP5 v5.240.12305.39446) */
  SELECT SUBSTR (pro1.user_profile_option_name, 1, 35) Profile,
         DECODE (pov.level_id,
                 10001, 'Site',
                 10002, 'Application',
                 10003, 'Resp',
                 10004, 'User')
            Option_Level,
         DECODE (pov.level_id,
                 10001, 'Site',
                 10002, appl.application_short_name,
                 10003, resp.responsibility_name,
                 10004, u.user_name)
            Level_Value,
         NVL (pov.profile_option_value, 'Is Null') Profile_option_Value
    FROM fnd_profile_option_values pov,
         fnd_responsibility_tl resp,
         fnd_application appl,
         fnd_user u,
         fnd_profile_options pro,
         fnd_profile_options_tl pro1
   WHERE     pro1.user_profile_option_name LIKE ('%Ledger%')
         AND pro.profile_option_name = pro1.profile_option_name
         AND pro.profile_option_id = pov.profile_option_id
         AND resp.responsibility_name LIKE '%General%Ledger%' /* comment this line  if you need to check profiles for all responsibilities */
         AND pov.level_value = resp.responsibility_id(+)
         AND pov.level_value = appl.application_id(+)
         AND pov.level_value = u.user_id(+)
ORDER BY 1, 2;

2) ,Obtain all Profile Option values setup for a particular responsibility. Replace the responsibility name as per your requirement.

/* Formatted on 10/15/2015 12:36:49 PM (QP5 v5.240.12305.39446) */
  SELECT SUBSTR (pro1.user_profile_option_name, 1, 35) Profile,
         DECODE (pov.level_id,
                 10001, 'Site',
                 10002, 'Application',
                 10003, 'Resp',
                 10004, 'User')
            Option_Level,
         DECODE (pov.level_id,
                 10001, 'Site',
                 10002, appl.application_short_name,
                 10003, resp.responsibility_name,
                 10004, u.user_name)
            Level_Value,
         NVL (pov.profile_option_value, 'Is Null') Profile_option_Value
    FROM fnd_profile_option_values pov,
         fnd_responsibility_tl resp,
         fnd_application appl,
         fnd_user u,
         fnd_profile_options pro,
         fnd_profile_options_tl pro1
   WHERE     pro.profile_option_name = pro1.profile_option_name
         AND pro.profile_option_id = pov.profile_option_id
         AND resp.responsibility_name LIKE '%General%Ledger%'
         AND pov.level_value = resp.responsibility_id(+)
         AND pov.level_value = appl.application_id(+)
         AND pov.level_value = u.user_id(+)
ORDER BY 1, 2;

CUSTOMER CONVERSION IN ORACLE APPS R12 via API's - OVERVIEW

In this post, we have tried to describe the process flow of customer conversion in graphical manner and Oracle Standard API’s to load the data into R12 Target System. The methodology described here is one of the proven strategies which we used in our conversion project. This will give you an approach for your customer requirements.

Functional Setups need to be considered/verified

Below are the important entities need to be setup before running the customer conversion loadingprogram in the target system

1. External Banks
2. Orig System
3. Customer profile classes
4. Collectors
5. Statement cycles
6. Grouping rules
7. Payment terms   
8. Dunning Letter Sets
9. Various lookups used in party/customer account related entities

Loading Party Data - R12

In order to reduce the size of the post, we have published it in another link,

http://www.shareoracleapps.com/2012/09/customer-conversion-loading-party-data.html

Loading Customer Account Data - R12

In order to reduce the size of the post, we have published it in another link,

http://www.shareoracleapps.com/2012/09/customer-conversion-loading-customer.html

Tables/views involved Brief Overview:

Table/View name	Purpose
AR_CUST_RECEIPT_METHODS_V	Stores the payment method information of  the customer
RA_TERMS	Contains the payment term details
AR_DUNNING_LETTER_SETS	Stores dunning letter master details
AR_STATEMENT_CYCLES	Statement cycle details
HZ_CUST_PROFILE_CLASSES	Master table for customer account profile classes
AR_COLLECTORS	Master table for collector information
AR_AUTOCASH_HIERARCHIES	Master table for hierarches details related to Lockbox program
GL_CODE_COMBINATIONS_KFV	Stores the account code combinations
HZ_PARTIES	Stores the party information
HZ_PARTY_SITES	Master table for party site
HZ_LOCATIONS	Master table for addresses
HZ_PARTY_SITES	Master table for party sites
HZ_CUST_ACCOUNTS	Master table for customer account details
HZ_CUST_ACCT_SITES_ALL	Master table for customer account sites
HZ_CUST_SITE_USES_ALL	Master table for customer account site uses
HZ_CUSTOMER_PROFILES	Master table for customer account/site profiles
HZ_CUST_PROFILE_AMTS	Master table for customer account/site profile amounts
HZ_CUST_ACCOUNT_ROLES	Master table for customer account/site contacts
HZ_ROLE_RESPONSIBILITIES	Master table for customer account/site contact responsibilities
HZ_RELATIONSHIPS	Master table for storing party relationship details
HZ_CODE_ASSIGNMENTS	Master table for party organization classifications
HZ_ORGANIZATION_PROFILES	Master table for storing the organization type party profiles
HZ_PERSON_PROFILES	Master table for storing the person type party profiles
HZ_ORG_CONTACTS	Master table for storing the organization contact details
HZ_ORG_CONTACT_ROLES	Master table for storing the organization contact roles responsibilities
HZ_CONTACT_POINTS	Master table for storing the party/party site contact point details

Create Party of type Organization in Oracle TCA using API hz_party_v2pub.create_organization

                                                                                                         
Purpose of the API          : CREATE ORGANIZATION API
API Package Used            : HZ_PARTY_V2PUB
PL/SQL Procedure used : CREATE_ORGANIZATION
Base Tables Affected      : HZ_PARTIES , HZ_ORGANIZATION_PROFILES
Notes:
1.       Ensure that the Profile Option HZ : Generate Party Number is Set to ‘Yes’ at Site Level.
Value for created_by_module must be a value defined in lookup type HZ_CREATED_BY_MODULES in the table FND_LOOKUP_VALUES
SCRIPT:

SET SERVEROUTPUT ON;
DECLARE

p_organization_rec HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE;
x_return_status    VARCHAR2(2000);
x_msg_count        NUMBER;
x_msg_data         VARCHAR2(2000);
x_party_id         NUMBER;
x_party_number     VARCHAR2(2000);
x_profile_id       NUMBER;

BEGIN
p_organization_rec.organization_name := 'New TCA Organization';
p_organization_rec.created_by_module := 'BO_API';

DBMS_OUTPUT.PUT_LINE('Calling the API hz_party_v2pub.create_organization');

hz_party_v2pub.create_organization
             (
              p_init_msg_list    => FND_API.G_TRUE,
              p_organization_rec => p_organization_rec,
              x_return_status    => x_return_status,
              x_msg_count        => x_msg_count,
              x_msg_data         => x_msg_data,
              x_party_id         => x_party_id,
              x_party_number     => x_party_number,
              x_profile_id       => x_profile_id
                   );

IF x_return_status = fnd_api.g_ret_sts_success THEN
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('Creation of Organization is Successful ');
    DBMS_OUTPUT.PUT_LINE('Output information ....');
    DBMS_OUTPUT.PUT_LINE('Party Id         ='|| x_party_id);
    DBMS_OUTPUT.PUT_LINE('Party_Number     ='|| x_party_number);
    DBMS_OUTPUT.PUT_LINE('Profile Id       ='|| x_profile_id);
ELSE
    DBMS_OUTPUT.put_line ('Creation of Organization failed:'||x_msg_data);
    ROLLBACK;
    FOR i IN 1 .. x_msg_count
    LOOP
      x_msg_data := oe_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');
END;
/

Create a Customer Account Relationship API HZ_CUST_ACCOUNT_V2PUB.CREATE_CUST_ACCT_RELATE

Create a Customer Account Relationship API HZ_CUST_ACCOUNT_V2PUB.CREATE_CUST_ACCT_RELATE


 

                                                                                                                       

API : HZ_CUST_ACCOUNT_V2PUB.CREATE_CUST_ACCT_RELATE
TEST INSTANCE: R12.1.3
DETAILS OF THE ROUTINE:
This routine is used to create a Customer Account Relationship. The API creates a record in the HZ_CUST_ACCT_RELATE table. You can relate two different customer accounts in this process. This is different from Relationship API that has been discussed earlier.

SCRIPT:

SET SERVEROUTPUT ON;
DECLARE
  P_INIT_MSG_LIST VARCHAR2(200);
  P_CUST_ACCT_RELATE_REC APPS.HZ_CUST_ACCOUNT_V2PUB.CUST_ACCT_RELATE_REC_TYPE;
  X_RETURN_STATUS VARCHAR2(200);
  X_MSG_COUNT NUMBER;
  X_MSG_DATA VARCHAR2(200);

  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_CUST_ACCT_RELATE_REC.cust_account_id         := 150734;
  P_CUST_ACCT_RELATE_REC.related_cust_account_id := 151734;
  P_CUST_ACCT_RELATE_REC.relationship_type       := 'ALL';
  P_CUST_ACCT_RELATE_REC.created_by_module       := 'BO_API';

  DBMS_OUTPUT.PUT_LINE('Calling the API HZ_CUST_ACCOUNT_V2PUB.CREATE_CUST_ACCT_RELATE');

  HZ_CUST_ACCOUNT_V2PUB.CREATE_CUST_ACCT_RELATE
                (
                 p_init_msg_list        => FND_API.G_TRUE,
                 P_CUST_ACCT_RELATE_REC => P_CUST_ACCT_RELATE_REC,
                 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 Customer Relationship is Successful ');  
  ELSE
    DBMS_OUTPUT.put_line ('Creation of Customer Relationship got 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');
  END;
/
 

Assign Receipt method to Customer via API in Oracle Apps R12 (hz_payment_method_pub.create_payment_method)

In this post, we have given a sample script to assign a payment method to Customer account in R12.

Test instance: R12

Script: 

SET SERVEROUTPUT ON;

DECLARE

  vl_payment_method_name          VARCHAR2(50) := 'AP/AR Netting';

  vl_cust_orig_sys_reference      VARCHAR2(50) := '17312';

  vl_site_use_orig_sys_reference  VARCHAR2(50) := '34914';

  vl_pay_method_rec         hz_payment_method_pub.payment_method_rec_type;

 

  vl_method_id_num          NUMBER;

  vl_acc_id_num             NUMBER;

  vl_party_id_num           NUMBER;

  vl_site_id_num            NUMBER;

  vg_status_txt             VARCHAR2 (1);

  vg_msg_cnt_num            NUMBER;

  vg_msg_data_txt           VARCHAR2 (2000);

  vl_cust_receipt_method_id NUMBER;

  lv_cust_rec_met_id        NUMBER;

BEGIN

 -- Apps initialize

  fnd_global.APPS_INITIALIZE (1119, 50937, 222);

  mo_global.init('AR');

  BEGIN

    SELECT receipt_method_id

      INTO vl_method_id_num

      FROM ar_receipt_methods

     WHERE NAME = vl_payment_method_name

       AND SYSDATE BETWEEN (nvl(start_date,SYSDATE - 1))

                       AND (nvl(end_date,SYSDATE + 1));

  EXCEPTION

    WHEN OTHERS THEN

      dbms_output.put_line('Error deriving Receipt Method ID - ' || SQLERRM);

  END;

  BEGIN

    SELECT hca.cust_account_id,

           hca.party_id

      INTO vl_acc_id_num,

           vl_party_id_num

      FROM hz_cust_accounts hca

     WHERE hca.orig_system_reference = TRIM(vl_cust_orig_sys_reference);

  EXCEPTION

    WHEN OTHERS THEN

      dbms_output.put_line('Error deriving Cust Account ID - ' || SQLERRM);

  END;

  BEGIN

    SELECT hcsua.site_use_id

      INTO vl_site_id_num

      FROM hz_cust_site_uses_all hcsua

     WHERE hcsua.orig_system_reference = vl_site_use_orig_sys_reference

       AND hcsua.site_use_code IN ('BILL_TO', 'SHIP_TO');

       dbms_output.put_line('vl_site_id_num :'||vl_site_id_num);

  EXCEPTION

    WHEN OTHERS THEN

      dbms_output.put_line('Error deriving Site Use ID - ' || SQLERRM);

  END;

  vl_pay_method_rec.cust_account_id   := vl_acc_id_num;

  vl_pay_method_rec.receipt_method_id := vl_method_id_num;

  vl_pay_method_rec.primary_flag      := 'Y';

  vl_pay_method_rec.site_use_id       := vl_site_id_num;

  vl_pay_method_rec.start_date        := SYSDATE;

  vl_pay_method_rec.end_date          := NULL;

 

  hz_payment_method_pub.create_payment_method

               (p_init_msg_list          => fnd_api.g_false,

                p_payment_method_rec     => vl_pay_method_rec,

                x_cust_receipt_method_id => vl_cust_receipt_method_id,

                x_return_status          => vg_status_txt,

                x_msg_count              => vg_msg_cnt_num,

                x_msg_data               => vg_msg_data_txt);

                                             

  dbms_output.put_line('PAYMENT METHOD ');

  dbms_output.put_line('return_status=' || substr(vg_status_txt,1,255));

  dbms_output.put_line('count=' || to_char(vg_msg_cnt_num));

  dbms_output.put_line('Msg_data = ' || substr(vg_msg_data_txt,1,255));

  IF (nvl(vg_status_txt,'X') != fnd_api.g_ret_sts_success)

  THEN

    IF vg_msg_cnt_num >= 1

    THEN

      dbms_output.put_line('Create Payment Methods : ' || substr(fnd_msg_pub.get(p_encoded => fnd_api.g_false),1,255));

    END IF;

  ELSE

    BEGIN

    SELECT cust_receipt_method_id

      INTO lv_cust_rec_met_id

      FROM ra_cust_receipt_methods

     WHERE customer_id       = vl_acc_id_num

       AND site_use_id       = vl_site_id_num

       AND receipt_method_id = vl_method_id_num;

      

    dbms_output.put_line('Successfully Created the Payment Methods (cust_receipt_method_id) :'||lv_cust_rec_met_id);

    END;

   

    COMMIT;

  END IF;

EXCEPTION

  WHEN OTHERS THEN

    dbms_output.put_line('Unknown error during call to Create Payment Methods : ' ||SQLERRM);

END;

/

API to Update Customer Address in Oracle TCA R12 (HZ_LOCATION_V2PUB.UPDATE_LOCATION)

                                                                                                                   

With the help of the below script, we can update the existing customer address stored in the table “HZ_LOCATIONS”.
API:  HZ_LOCATION_V2PUB.UPDATE_LOCATION
Test Instance:  R12.1.3

Script:

SET SERVEROUTPUT ON;
DECLARE
p_location_rec          HZ_LOCATION_V2PUB.LOCATION_REC_TYPE;
p_object_version_number NUMBER;
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_location_rec.location_id := 28195;  -- HZ_LOCATIONS.LOCATION_ID
p_location_rec.address2    := FND_API.G_MISS_CHAR;
p_location_rec.address1    := 'test Oracle Apps 1';
p_object_version_number    := 1;

DBMS_OUTPUT.PUT_LINE('Calling the API hz_location_v2pub.update_location');

hz_location_v2pub.update_location
            (
             p_init_msg_list           => FND_API.G_TRUE,
             p_location_rec            => p_location_rec,
             p_object_version_number   => p_object_version_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 Location is Successful ');
    DBMS_OUTPUT.PUT_LINE('Output information ....');
    DBMS_OUTPUT.PUT_LINE('New Address1: '||p_location_rec.address1);
ELSE
    DBMS_OUTPUT.put_line ('Creation of Location failed:'||x_msg_data);
    ROLLBACK;
    FOR i IN 1 .. x_msg_count
    LOOP
      x_msg_data := oe_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');
END;
/