API To Update Supplier Name in Oracle APPS R12

/*PLSQL Script using API to update vendor name in ap_suppliers*/

Initialization steps BEFORE MODIFY supplier TABLES: 

a) Make sure "HZ: Change Party Name" profile option is not set to No for this user.
SELECT user_id
FROM   fnd_user
WHERE  user_name=<'Username which is used to run script'>;

b) Get the resp id AS
SELECT responsibility_id,
       application_id
FROM   fnd_responsibility_tl
WHERE  responsibility_name = '<resp name>';

c) Now, SET the applications context FROM sqlplus using, 

EXEC fnd_global.apps_initialize(<user_id>,<resp_id>,200);

API:

DECLARE
    lc_return_status  VARCHAR2(2000);
    ln_msg_count      NUMBER;
    ll_msg_data       LONG;
    ln_vendor_id      NUMBER;
    ln_vendor_site_id NUMBER;
    ln_message_int    NUMBER;
    ln_party_id       NUMBER;
    lrec_vendor_rec   ap_vendor_pub_pkg.r_vendor_rec_type;
BEGIN
    fnd_global.Apps_initialize(<USER_ID>, <RESP_ID>, 201);
    ln_vendor_id := <VENDOR_ID>;
    lrec_vendor_rec.vendor_name := '<New_Vendor_Name>';

    ap_vendor_pub_pkg.Update_vendor_public

    (p_api_version => 1,
     x_return_status => lc_return_status, 

     x_msg_count => ln_msg_count,
     x_msg_data => ll_msg_data, 

     p_vendor_rec => lrec_vendor_rec,
     p_vendor_id => ln_vendor_id);

    IF ( lc_return_status <> 'S' ) THEN
      IF ln_msg_count >= 1 THEN
        FOR v_index IN 1..ln_msg_count LOOP
            fnd_msg_pub.Get (p_msg_index => v_index, p_encoded => 'F',
            p_data => ll_msg_data
            , p_msg_index_out => ln_message_int);

            ll_msg_data := 'UPDATE_VENDOR '
                           ||Substr(ll_msg_data, 1, 3900);

            dbms_output.Put_line('Ll_Msg_Data - '
                                 ||ll_msg_data);
        END LOOP;
      END IF;
    END IF;

    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
      dbms_output.Put_line('SQLERRM - '
                           ||SQLERRM);
END; 

If the Supplier is an Employee type supplier you also need to add the following or you will get the error:

Ll_Msg_Data - UPDATE_VENDOR There is no active profile for party ID xxxx of content source type SST.  If an active profile exists, the content source type passed must be the same as the content source type of the active profile.

Ll_Msg_Data - UPDATE_VENDOR Call to TCA API has errored out.

This needs to be done before the call to ap_vendor_pub_pkg.update_vendor_public()...

lrec_vendor_rec.employee_id := <employee_id>;

Reference: R12: AP: New Supplier Update API's in Oracle Payables (Doc ID 1618099.1)

List of all SUNDAYS in a year by query in Oracle

 ***List of all SUNDAYS in a year by query***

(1).

with dts AS
       (
              SELECT DATE'2023-01-01'+ROWNUM-1 dt
              FROM   dual
                     CONNECT BY LEVEL <= 366 )
SELECT *
FROM   dts
WHERE  to_char(dt, 'fmday', 'NLS_DATE_LANGUAGE=AMERICAN') = 'sunday';

(2).

SELECT dates, day
FROM   (
              SELECT ROWNUM,
                     Trunc(SYSDATE,'YEAR')+ROWNUM-1                dates,
                     To_char(Trunc(SYSDATE,'YEAR')+ROWNUM-1,'DAY') day
              FROM   user_objects or_any_table_that_will_exceed_365_rows
              WHERE  ROWNUM-1 < 365 )
WHERE  day LIKE 'SUNDAY%';

How To Enable/Disable The Forms Personalization Menu

 By default the “Personalize” menu is visible to all the users.  This can be controlled with help of profile options. By setting up the below profile options, the access to the Personalize menu can be limited for the authorized users. This will prevent unwanted users from changing the look and behavior of the forms.

The profile options are:

Utilities:Diagnostics = Yes/No

This profile option controls the availability of the Diagnostics utility.

So you can set it to Yes on site level or on user level.

Hide Diagnostics menu entry = Yes/No

This profile Option determines whether a user can automatically use the Diagnostics features.

If Utilities:Diagnostics is set to No, then users must enter the password for the APPS schema

to use the Diagnostics features.

Query to find distinct comma separated values in Oracle

 

Oracle Query to find distinct comma separated values from given String : 

SELECT Listagg(word, ',')
         within GROUP (ORDER BY NULL) output_string
FROM   (SELECT DISTINCT Regexp_substr(val, '[^,]+', 1, column_value) word
        FROM   (SELECT :p_comma_seperated_str val
                FROM   dual),
               TABLE(Cast(MULTISET(SELECT LEVEL
                             FROM   dual
                             CONNECT BY LEVEL <= Regexp_count(val, ',') + 1) AS
                          sys.ODCINUMBERLIST)));