Script to update Supplier pay_group_lookup_code in Oracle APPS R12

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

-- Initialize apps session

fnd_global.apps_initialize(0, 50554, 200);

mo_global.set_policy_context('S',204);

mo_global.init('SQLAP');

 

Ln_Vendor_Id                          := 1;

Lrec_Vendor_Rec.pay_group_lookup_code := 'Employee';

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

ROLLBACK;

END

Script to update Supplier Type(ap_suppliers.vendor_type_lookup_code) in Oracle APPS R12

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

-- Initialize apps session

fnd_global.apps_initialize(0, 50554, 200);

mo_global.set_policy_context('S',204);

mo_global.init('SQLAP');

 

Ln_Vendor_Id                          := 1;

Lrec_Vendor_Rec.vendor_type_lookup_code := 'LOCAL';

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

ROLLBACK;

END

Script to update Supplier Tax Payer ID(AP_SUPPLIERS.NUM_1099) in Oracle APPS R12

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

-- Initialize apps session

fnd_global.apps_initialize(0, 50554, 200);

mo_global.set_policy_context('S',204);

mo_global.init('SQLAP');

 

Ln_Vendor_Id                          := 1;

Lrec_Vendor_Rec.jgzz_fiscal_code := 'X123456789X';

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

ROLLBACK;

END

API to Create Online Accounting for AP Payment in Oracle APPS R12 (ap_drilldown_pub_pkg.payment_online_accounting)

    PROCEDURE xx_payment_accounting(errbuf OUT VARCHAR2

   ,retcode           OUT VARCHAR2

   ,p_invoice_id    IN  NUMBER

   )

IS

CURSOR c1 

IS

SELECT DISTINCT aca.check_id, aca.check_number        

              FROM ap_invoices_all xlpi

      ,ap_invoice_payments_all aipa

  ,ap_checks_all aca

             WHERE xlpi.invoice_id   = aipa.invoice_id

               AND xlpi.invoice_id   = pin_invoice_id

               AND aipa.check_id     = aca.check_id

               AND ap_checks_pkg.get_posting_status(aca.check_id) = 'N';

   -- This condition is to pick invoices which are paid and unaccounted

   ;  

ln_processed_cnt    NUMBER := 0;

ln_failed_cnt       NUMBER := 0;  

lv_error_buf        VARCHAR2(4000);

ln_retcode          NUMBER; 

BEGIN

FOR rec_create_pmt_accounting IN c1

LOOP

BEGIN

fnd_file.put_line(fnd_file.log,'Check ID: '||rec_create_pmt_accounting.check_id);

ln_retcode   := NULL;

lv_error_buf := NULL;

ap_drilldown_pub_pkg.payment_online_accounting(p_check_id           => rec_create_pmt_accounting.check_id

  ,p_accounting_mode    => 'P'      

  ,p_errbuf             => lv_error_buf

  ,p_retcode            => ln_retcode

  ,p_calling_sequence   => 'XX_PAYMENT_ACCOUNTING'

  );

IF ln_retcode = 0 

THEN

fnd_file.put_line(fnd_file.log,'Invoice Check ID: '

||rec_create_pmt_accounting.check_id 

||'. Payment ('

||rec_create_pmt_accounting.check_number

||') Accounted Sucessfully'

);

ln_processed_cnt := ln_processed_cnt+1;

ELSIF ln_retcode = 1 THEN

fnd_file.put_line(fnd_file.log,'Invoice Check ID: '

||rec_create_pmt_accounting.check_id

||'. Payment ('

||rec_create_pmt_accounting.check_number

||') Accounting ended in WARNING. Errbuf : '

||lv_error_buf

);

ln_processed_cnt := ln_processed_cnt+1;

ELSIF ln_retcode = 2 THEN

fnd_file.put_line(fnd_file.log,'Invoice Check ID: '

||rec_create_pmt_accounting.check_id 

||'. Payment ('

||rec_create_pmt_accounting.check_number

||') Accounting ended in ERROR. Errbuf : '

||lv_error_buf

);       

ln_failed_cnt := ln_failed_cnt +1;          

ELSE

fnd_file.put_line(fnd_file.log,'Invoice Check ID: '

||rec_create_pmt_accounting.check_id 

||'. Payment ('

||rec_create_pmt_accounting.check_number

||') Accounting ended in ERROR. Errbuf : '

||lv_error_buf

||' Retcode: '

||ln_retcode

);

ln_failed_cnt := ln_failed_cnt +1;

END IF; 

COMMIT;

EXCEPTION

WHEN OTHERS THEN

fnd_file.put_line(fnd_file.log,'Invoice Check ID: ' 

||rec_create_pmt_accounting.check_id

||'. Payment Accounting failed with Unhandled Exception. Error Message:' 

||SQLERRM);

ln_failed_cnt := ln_failed_cnt + 1;

END;

END LOOP;

fnd_file.put_line ( fnd_file.log,'Processed Record Count: '

||ln_processed_cnt

||' Failed Record Count: '

||ln_failed_cnt);

EXCEPTION

WHEN OTHERS THEN

fnd_file.put_line ( fnd_file.log,'Main Error in xx_payment_accounting. Error Message: '||SQLERRM);

END xx_payment_accounting;

AR Receipt Reversal Categories(REVERSAL_CATEGORY_TYPE) in Oracle APPS

SELECT lookup_code

      ,meaning

      ,description

  FROM fnd_lookup_values 

WHERE lookup_type = 'REVERSAL_CATEGORY_TYPE'

    AND LANGUAGE = USERENV('LANG')

    AND lookup_code = 'REV'

Script to Update Liability Account, Prepay Account, Invoice Withholding Tax Group and Tax Classification Code at Supplier Site Level in Oracle APPS

Table Script:

DROP TABLE XXSCHEMA.XX_VENDOR_MASTER_DATA CASCADE CONSTRAINTS;

CREATE TABLE XXSCHEMA.XX_VENDOR_MASTER_DATA

(

  VENDOR_NAME                    VARCHAR2(250 BYTE),

  VENDOR_ID                      NUMBER,

  VENDOR_SITE_CODE               VARCHAR2(15 BYTE),

  ORG_ID                         NUMBER,

  LIABILITY_ACCOUNT              VARCHAR2(207 BYTE),

  NEW_LIABILITY_ACCOUNT          VARCHAR2(207 BYTE),

  PRE_PAYMENT_ACCOUNT            VARCHAR2(207 BYTE),

  NEW_PRE_PAYMENT_ACCOUNT        VARCHAR2(207 BYTE),

  TAX_CLASSIFICATION_CODE        VARCHAR2(207 BYTE),

  NEW_TAX_CLASSIFICATION_CODE    VARCHAR2(30 BYTE),

  INVOICE_WITHHOLDING_TAX_GROUP  VARCHAR2(25 BYTE),

  NEW_INVOICE_WHT_TAX_GROUP      VARCHAR2(25 BYTE),

  SUPPLIER_TYPE                  VARCHAR2(80 BYTE),

  NEW_SUPPLIER_TYPE              VARCHAR2(80 BYTE),

  TAX_APPLICABILITY              VARCHAR2(80 BYTE),

  SUPP_STATUS_FLAG               VARCHAR2(1 BYTE),

  SUPP_STATUS_MSG                VARCHAR2(2000 BYTE),

  SUPP_SITE_STATUS_FLAG          VARCHAR2(1 BYTE),

  SUPP_SITE_STATUS_MSG           VARCHAR2(2000 BYTE)

);

DROP SYNONYM APPS.XX_VENDOR_MASTER_DATA;

CREATE OR REPLACE SYNONYM APPS.XX_VENDOR_MASTER_DATA FOR XXSCHEMA.XX_VENDOR_MASTER_DATA;

API Script:

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

DECLARE

    v_api_version              NUMBER;

    v_init_msg_list            VARCHAR2(200);

    v_commit                    VARCHAR2(200);

    v_validation_level          NUMBER;

    x_return_status            VARCHAR2(200);

    x_msg_count                NUMBER;

    v_vendor                    NUMBER;

    x_msg_data                  VARCHAR2(200);

    l_vendor_site_rec          apps.ap_vendor_pub_pkg.r_vendor_site_rec_type;

    l_existing_vendor_site_rec ap_supplier_sites_all%rowtype;

    l_vendor_site_id            NUMBER;

l_liability_ccid gl_code_combinations_kfv.code_combination_id%TYPE;

l_prepay_ccid gl_code_combinations_kfv.code_combination_id%TYPE;

l_ap_awt_group_id ap_awt_groups.group_id%TYPE;

l_allow_awt_flag ap_suppliers.allow_awt_flag%TYPE;

    v_calling_prog              VARCHAR2(200);

    

    l_msg                      VARCHAR2(200);

    l_status_flag         VARCHAR(1):='N';

    l_status_msg     VARCHAR2(2000) := NULL;

        

    CURSOR c 

IS

SELECT DISTINCT 

            vendor_id

               ,vendor_site_code

   ,new_tax_classification_code

                   ,new_invoice_wht_tax_group

                   ,new_liability_account

                   ,new_pre_payment_account

          FROM xx_vendor_master_data

         WHERE NVL(supp_status_flag, 'N') = 'S'

   AND NVL(supp_site_status_flag, 'N') IN ('N', 'E')

   --AND vendor_id = 7216

   --AND vendor_id IN (5041)--5059)

   ;

BEGIN

    FOR i IN c 

LOOP

l_status_flag     := 'N';

l_status_msg      := NULL;

l_liability_ccid  := NULL;

l_prepay_ccid   := NULL;

l_ap_awt_group_id := NULL;

l_allow_awt_flag  := 'N';

BEGIN

SELECT vendor_site_id 

  INTO l_vendor_site_id

  FROM ap_supplier_sites_all

WHERE vendor_id = i.vendor_id

   AND vendor_site_code = i.vendor_site_code

   AND org_id = 101

   ;

EXCEPTION

WHEN NO_DATA_FOUND THEN

l_vendor_site_id := null;

l_status_flag := 'E';

l_status_msg := 'Unable to find vendor_site_code. '; 

WHEN OTHERS THEN

l_vendor_site_id := NULL;

l_status_flag := 'E';

l_status_msg := 'Error while validating vendor_site_code. error message: ' || SQLERRM;  

END;

BEGIN

SELECT code_combination_id 

  INTO l_liability_ccid

  FROM gl_code_combinations_kfv

WHERE concatenated_segments = TRIM(i.new_liability_account)

   ;

EXCEPTION

WHEN NO_DATA_FOUND THEN

l_liability_ccid := NULL;

l_status_flag := 'E';

l_status_msg := l_status_msg||'-Unable to find new_liability_account. '; 

WHEN OTHERS THEN

l_liability_ccid := NULL;

l_status_flag := 'E';

l_status_msg := l_status_msg||'-Error while validating new_liability_account. error message: ' || SQLERRM;  

END;

BEGIN

SELECT code_combination_id 

  INTO l_prepay_ccid

  FROM gl_code_combinations_kfv

WHERE concatenated_segments = TRIM(i.new_pre_payment_account)

   ;

EXCEPTION

WHEN NO_DATA_FOUND THEN

l_prepay_ccid := NULL;

l_status_flag := 'E';

l_status_msg := l_status_msg||'-Unable to find new_pre_payment_account. '; 

WHEN OTHERS THEN

l_prepay_ccid := NULL;

l_status_flag := 'E';

l_status_msg := l_status_msg||'-Error while validating new_pre_payment_account. error message: ' || SQLERRM;  

END;

IF TRIM(i.new_invoice_wht_tax_group) IS NOT NULL THEN

BEGIN

SELECT group_id 

  INTO l_ap_awt_group_id

  FROM ap_awt_groups

WHERE name = TRIM(i.new_invoice_wht_tax_group)

   ;

l_allow_awt_flag := 'Y';

EXCEPTION

WHEN NO_DATA_FOUND THEN

l_ap_awt_group_id := NULL;

l_status_flag := 'E';

l_status_msg := l_status_msg||'-Unable to find new_invoice_wht_tax_group. '; 

WHEN OTHERS THEN

l_ap_awt_group_id := NULL;

l_status_flag := 'E';

l_status_msg := l_status_msg||'-Error while validating new_invoice_wht_tax_group. error message: ' || SQLERRM;  

END;

END IF;

dbms_output.put_line('l_status_flag = ' || l_status_flag);

dbms_output.put_line('l_status_msg = ' || l_status_msg);

        IF l_status_flag <> 'E' THEN

fnd_global.apps_initialize(0, 50240, 200);

mo_global.init('SQLAP');

mo_global.set_policy_context('S', 101);

v_api_version := 1.0;

v_init_msg_list := fnd_api.g_true;

v_commit := fnd_api.g_true;

v_validation_level := fnd_api.g_valid_level_full;

--l_vendor_site_id := j.vendor_site_id; -- to be end dated

v_calling_prog := 'Supplier Tax code update';

l_vendor_site_rec.vendor_site_id := l_existing_vendor_site_rec.vendor_site_id;

l_vendor_site_rec.last_update_date := sysdate;

--l_vendor_site_rec.last_updated_by := 6147023;

l_vendor_site_rec.vendor_id := l_existing_vendor_site_rec.vendor_id;

l_vendor_site_rec.org_id := l_existing_vendor_site_rec.org_id;

l_vendor_site_rec.accts_pay_code_combination_id := l_liability_ccid;

l_vendor_site_rec.prepay_code_combination_id := l_prepay_ccid;

l_vendor_site_rec.vat_code := i.new_tax_classification_code;

l_vendor_site_rec.awt_group_id := l_ap_awt_group_id;

l_vendor_site_rec.allow_awt_flag := l_allow_awt_flag;

--l_vendor_site_rec.auto_tax_calc_flag := 'y';

--l_vendor_site_rec.vat_registration_num := i.tax_registration_no;

ap_vendor_pub_pkg.update_vendor_site_public(p_api_version => v_api_version

                                           ,p_init_msg_list => v_init_msg_list

       ,p_commit => v_commit

                                           ,p_validation_level  => v_validation_level

       ,x_return_status => x_return_status

       ,x_msg_count => x_msg_count

       ,x_msg_data     => x_msg_data

       ,p_vendor_site_rec   => l_vendor_site_rec

       ,p_vendor_site_id    => l_vendor_site_id

       ,p_calling_prog      => v_calling_prog

       );

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_return_status <> fnd_api.g_ret_sts_success) 

THEN

FOR i1 IN 1 .. fnd_msg_pub.count_msg 

LOOP

l_msg := fnd_msg_pub.get(p_msg_index => i1,

  p_encoded   => fnd_api.g_false);

DBMS_OUTPUT.put_line('The API call failed with error ' || l_msg);

UPDATE xx_vendor_master_data 

   SET supp_site_status_flag = 'E'

      ,supp_site_status_msg = l_msg 

     WHERE vendor_id = i.vendor_id

   AND vendor_site_code = i.vendor_site_code;

END LOOP;

ELSE

   DBMS_OUTPUT.put_line('The API call ended with SUCESSS status');

   UPDATE xx_vendor_master_data 

  SET supp_site_status_flag = 'S'

,supp_site_status_msg = 'Update Successful' 

WHERE vendor_id = i.vendor_id

  AND vendor_site_code = i.vendor_site_code

  ;

END IF;

ELSE

UPDATE xx_vendor_master_data 

   SET supp_site_status_flag = 'E'

      ,supp_site_status_msg = l_status_msg 

WHERE vendor_id = i.vendor_id

   AND vendor_site_code = i.vendor_site_code

   ;

END IF;

COMMIT;

    END LOOP;

END;

Script to Update Supplier Vendor Type and Invoice Withholding Tax Group at Supplier Level in Oracle APPS

Table Script:

DROP TABLE XXSCHEMA.XX_VENDOR_MASTER_DATA CASCADE CONSTRAINTS;

CREATE TABLE XXSCHEMA.XX_VENDOR_MASTER_DATA

(

  VENDOR_NAME                    VARCHAR2(250 BYTE),

  VENDOR_ID                      NUMBER,

  VENDOR_SITE_CODE               VARCHAR2(15 BYTE),

  ORG_ID                         NUMBER,

  LIABILITY_ACCOUNT              VARCHAR2(207 BYTE),

  NEW_LIABILITY_ACCOUNT          VARCHAR2(207 BYTE),

  PRE_PAYMENT_ACCOUNT            VARCHAR2(207 BYTE),

  NEW_PRE_PAYMENT_ACCOUNT        VARCHAR2(207 BYTE),

  TAX_CLASSIFICATION_CODE        VARCHAR2(207 BYTE),

  NEW_TAX_CLASSIFICATION_CODE    VARCHAR2(30 BYTE),

  INVOICE_WITHHOLDING_TAX_GROUP  VARCHAR2(25 BYTE),

  NEW_INVOICE_WHT_TAX_GROUP      VARCHAR2(25 BYTE),

  SUPPLIER_TYPE                  VARCHAR2(80 BYTE),

  NEW_SUPPLIER_TYPE              VARCHAR2(80 BYTE),

  TAX_APPLICABILITY              VARCHAR2(80 BYTE),

  SUPP_STATUS_FLAG               VARCHAR2(1 BYTE),

  SUPP_STATUS_MSG                VARCHAR2(2000 BYTE),

  SUPP_SITE_STATUS_FLAG          VARCHAR2(1 BYTE),

  SUPP_SITE_STATUS_MSG           VARCHAR2(2000 BYTE)

);

DROP SYNONYM APPS.XX_VENDOR_MASTER_DATA;

CREATE OR REPLACE SYNONYM APPS.XX_VENDOR_MASTER_DATA FOR XXSCHEMA.XX_VENDOR_MASTER_DATA;

API Script:

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

--SET serveroutput on;

DECLARE

p_api_version          NUMBER;

p_init_msg_list        VARCHAR2(200);

p_commit                VARCHAR2(200);

p_validation_level      NUMBER;

x_return_status        VARCHAR2(200);

x_msg_count            NUMBER;

x_msg_data              VARCHAR2(200);

lr_vendor_rec          apps.ap_vendor_pub_pkg.r_vendor_rec_type;

lr_existing_vendor_rec ap_suppliers%ROWTYPE;

l_msg                  VARCHAR2(200);

p_vendor_id            NUMBER;

l_new_supplier_type fnd_lookup_values.lookup_code%type;

l_allow_awt_flag ap_suppliers.allow_awt_flag%TYPE;

l_ap_awt_group_id     ap_awt_groups.group_id%TYPE;

l_status_flag VARCHAR(1):='N';

l_status_msg VARCHAR2(2000) := NULL;

CURSOR c_vendor_type IS

SELECT DISTINCT vendor_id

               ,supplier_type

   ,new_supplier_type

                       ,new_invoice_wht_tax_group    

  FROM xx_vendor_master_data

WHERE NVL(supp_status_flag, 'N') IN ('N', 'E')

   --AND vendor_id = 7216

   AND vendor_id IN (5041)--5059)

   ;

    BEGIN

-- Initialize apps session

fnd_global.apps_initialize(0, 50240, 200); --user_id, 

mo_global.init('SQLAP');

fnd_client_info.set_org_context(101);

-- Assign Basic Values

p_api_version      := 1.0;

p_init_msg_list    := fnd_api.g_true;

p_commit           := fnd_api.g_true;

p_validation_level := fnd_api.g_valid_level_full;

     

FOR j in c_vendor_type 

        LOOP

            p_vendor_id        := j.vendor_id;

l_status_flag     := 'N';

l_status_msg      := NULL;

l_ap_awt_group_id := NULL;

l_allow_awt_flag  := 'N';

    

            -- gather vendor details

            BEGIN

                SELECT *

                  INTO lr_existing_vendor_rec

                  FROM ap_suppliers asa

                 WHERE asa.vendor_id = p_vendor_id;

            EXCEPTION

    WHEN OTHERS THEN

DBMS_OUTPUT.put_line('Unable to derive the supplier  information for vendor id:' ||

                                      p_vendor_id);

END;

            

IF j.supplier_type <> j.new_supplier_type THEN

BEGIN

SELECT lookup_code

  INTO l_new_supplier_type

  FROM fnd_lookup_values

WHERE meaning = j.new_supplier_type

   AND lookup_type = 'VENDOR TYPE'

   AND enabled_flag = 'Y'

   AND language = userenv('LANG');

EXCEPTION

WHEN NO_DATA_FOUND THEN

l_new_supplier_type := null;

l_status_flag := 'E';

l_status_msg := 'Supplier Type does not exists in VENDOR TYPE Lookup: ' || SQLERRM; 

WHEN OTHERS THEN

l_new_supplier_type := null;

l_status_flag := 'E';

l_status_msg := 'Error while validating new supplier type. error message: ' || SQLERRM;  

END;

ELSE

NULL;

END IF;

IF TRIM(j.new_invoice_wht_tax_group) IS NOT NULL THEN

BEGIN

SELECT group_id 

  INTO l_ap_awt_group_id

  FROM ap_awt_groups

WHERE name = TRIM(j.new_invoice_wht_tax_group)

   ;

l_allow_awt_flag := 'Y';

EXCEPTION

WHEN OTHERS THEN

l_ap_awt_group_id := NULL;

l_status_flag := 'E';

l_status_msg := l_status_msg||'-Error while validating new_invoice_wht_tax_group. error message: ' || SQLERRM;  

END;

END IF;

dbms_output.put_line('l_status_flag = ' || l_status_flag);

dbms_output.put_line('l_status_msg = ' || l_status_msg);

    

             lr_vendor_rec.vendor_id       := lr_existing_vendor_rec.vendor_id;

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

--Deactivate Vendor

             -- lr_vendor_rec.end_date_active := SYSDATE;

             -- lr_vendor_rec.enabled_flag    := 'N';

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

             lr_vendor_rec.vendor_type_lookup_code := l_new_supplier_type;

lr_vendor_rec.allow_awt_flag := l_allow_awt_flag;

lr_vendor_rec.awt_group_id := l_ap_awt_group_id;

            IF l_status_flag != 'E'

THEN

ap_vendor_pub_pkg.update_vendor(p_api_version      => p_api_version

   ,p_init_msg_list    => p_init_msg_list

   ,p_commit           => p_commit

   ,p_validation_level => p_validation_level

   ,x_return_status    => x_return_status

   ,x_msg_count        => x_msg_count

   ,x_msg_data         => x_msg_data

   ,p_vendor_rec       => lr_vendor_rec

   ,p_vendor_id        => p_vendor_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_return_status <> fnd_api.g_ret_sts_success) THEN

FOR i IN 1 .. fnd_msg_pub.count_msg 

LOOP

l_msg := fnd_msg_pub.get(p_msg_index => i

                        ,p_encoded   => fnd_api.g_false

);

DBMS_OUTPUT.put_line('The API call failed with error ' || l_msg);

UPDATE xx_vendor_master_data 

                           SET supp_status_flag = 'E'

      ,supp_status_msg = l_msg 

     WHERE vendor_id = j.vendor_id;

                    END LOOP;

                ELSE

DBMS_OUTPUT.put_line('The API call ended with SUCESSS status');

    UPDATE xx_vendor_master_data 

   SET supp_status_flag = 'S'

  ,supp_status_msg = 'Update Successful' 

WHERE vendor_id = j.vendor_id;

END IF;

ELSE

UPDATE xx_vendor_master_data 

   SET supp_status_flag = 'E'

      ,supp_status_msg = l_status_msg 

WHERE vendor_id = j.vendor_id;

            END IF;

        END LOOP;

    END;

/

AP_SUPPLIER_SITES_ALL.AUTO_TAX_CALC_FLAG in Oracle APPS(AP_TAX_CALCULATION_METHOD)

SELECT *

  FROM fnd_lookup_values

 WHERE lookup_type = 'AP_TAX_CALCULATION_METHOD'

 AND language = USERENV('LANG')

Here AP_SUPPLIER_SITES_ALL.AUTO_TAX_CALC_FLAG will be fnd_lookup_values.lookup_code

AP_CHECKS_ALL.PAYMENT_METHOD_LOOKUP_CODE and AP_CHECKS_ALL.PAYMENT_METHOD_CODE in Oracle APPS

Below is from Oracle Metalink ID 1919051.1

PAYMENT_METHOD_LOOKUP_CODE is obsolete in R12 hence it should not be referred in R12. If you want to refer payment method code from Invoice header then PAYMENT_METHOD_CODE should be used. During upgrade to R12 we populate PAYMENT_METNHOD_CODE with PAYMENT_METHOD_LOOKUP_CODE for migrated invoices.

SELECT payment_method_code, payment_method_name, description

  FROM iby_payment_methods_tl

 WHERE payment_method_code IN

          (SELECT DISTINCT PAYMENT_METHOD_CODE

              FROM AP_CHECKS_ALL

              )

AND language = USERENV('LANG')

Query to get Asset Categories with GL Codes in Oracle APPS

SELECT fcb.book_type_code

       ,fct.segment1||'.'||fct.segment2 category

       ,fct.enabled_flag enabled

       ,fct.description

       ,fct.capitalize_flag  capitalize

       ,flt.meaning category_type

       ,fct.inventorial physical_inventory

       ,flt1.meaning ownership 

       ,fct.property_type_code property_type

       ,gcck.concatenated_segments asset_cost

       ,gcck1.concatenated_segments asset_clearing

       ,gcck2.concatenated_segments depreciation_expense

       ,gcck3.concatenated_segments accumulated_depreciation

       ,gcck4.concatenated_segments bonus_expense

       ,gcck5.concatenated_segments bonus_reserve

       ,gcck6.concatenated_segments revaluation_reserve

       ,gcck7.concatenated_segments revaluation_amortization

       ,gcck8.concatenated_segments cip_cost

       ,gcck9.concatenated_segments cip_clearing

       ,gcck10.concatenated_segments impairment_expense

       ,gcck11.concatenated_segments accumulated_impairment

       ,gcck12.concatenated_segments unplanned_depreciatn_exp

       ,gcck13.concatenated_segments alternate_asset_cost

       ,gcck14.concatenated_segments write_off_expense

       ,fcbd.start_dpis placed_in_service_from

       ,fcbd.end_dpis placed_in_service_to

       ,fcbd.depreciate_flag depreciate

       ,fcbd.deprn_method 

       ,fcbd.life_in_months

       ,FLOOR(fcbd.life_in_months/12) life_in_years

       ,fcbd.bonus_rule

       ,fcbd.prorate_convention_code

       ,fcbd.retirement_prorate_convention

       ,fcbd.percent_salvage_value

       ,fcbd.ceiling_name

       ,fcbd.price_index_name

       ,fcbd.subcomponent_life_rule

       ,fcbd.minimum_life_in_months

       ,FLOOR(fcbd.minimum_life_in_months/12) minimum_life_in_years

       ,fcbd.use_stl_retirements_flag

       ,fcbd.stl_method_code 

       ,FLOOR(fcbd.stl_life_in_months/12) slr_life_years

       ,fcbd.use_deprn_limits_flag

       ,fcbd.allowed_deprn_limit

       ,fcbd.special_deprn_limit_amount

       ,FLOOR(fcbd.capital_gain_threshold/12) cgt_in_years

       ,MOD(fcbd.capital_gain_threshold,12) cgt_in_months

       ,fcbd.itc_eligible_flag       

       ,fcbd.use_itc_ceilings_flag

       ,fcbd.mass_property_flag

       ,fcbd.group_asset_id group_asset

       ,fcbd.recognize_gain_loss

       ,fcbd.terminal_gain_loss

       ,fcbd.recapture_reserve_flag 

       ,fcbd.limit_proceeds_flag

       ,fcbd.tracking_method

       ,fcbd.allocate_to_fully_rsv_flag

       ,fcbd.excess_allocation_option distribute_or_reduce_excess

   FROM apps.fa_categories_vl fct

       ,apps.fa_lookups_tl flt

       ,apps.fa_lookups_tl flt1

       ,apps.fa_category_books fcb

       ,apps.gl_code_combinations_kfv gcck

       ,apps.gl_code_combinations_kfv gcck1

       ,apps.gl_code_combinations_kfv gcck2

       ,apps.gl_code_combinations_kfv gcck3

       ,apps.gl_code_combinations_kfv gcck4

       ,apps.gl_code_combinations_kfv gcck5

       ,apps.gl_code_combinations_kfv gcck6

       ,apps.gl_code_combinations_kfv gcck7

       ,apps.gl_code_combinations_kfv gcck8

       ,apps.gl_code_combinations_kfv gcck9

       ,apps.gl_code_combinations_kfv gcck10

       ,apps.gl_code_combinations_kfv gcck11

       ,apps.gl_code_combinations_kfv gcck12

       ,apps.gl_code_combinations_kfv gcck13

       ,apps.gl_code_combinations_kfv gcck14

       ,apps.fa_category_book_defaults fcbd

  WHERE 1=1

    AND fct.category_type = flt.lookup_code(+)

    AND flt.lookup_type(+) = 'CATEGORY TYPE'

    AND flt1.lookup_code(+) = fct.owned_leased

    AND flt1.lookup_type(+) ='OWNLEASE'

    AND fct.category_id = fcb.category_id

    AND gcck.code_combination_id(+) = fcb.asset_cost_account_ccid

    AND gcck1.code_combination_id(+) = fcb.asset_clearing_account_ccid

    AND gcck2.code_combination_id(+) = fcb.deprn_expense_account_ccid

    AND gcck3.code_combination_id(+) = fcb.reserve_account_ccid

    AND gcck4.code_combination_id(+) = fcb.bonus_expense_account_ccid

    AND gcck5.code_combination_id(+) = fcb.bonus_reserve_acct_ccid

    AND gcck6.code_combination_id(+) = fcb.reval_reserve_account_ccid

    AND gcck7.code_combination_id(+) = fcb.reval_amort_account_ccid

    AND gcck8.code_combination_id(+) = fcb.wip_cost_account_ccid

    AND gcck9.code_combination_id(+) = fcb.wip_clearing_account_ccid

    AND gcck10.code_combination_id(+) = fcb.impair_expense_account_ccid

    AND gcck11.code_combination_id(+) = fcb.impair_reserve_account_ccid

    AND gcck12.code_combination_id(+) = fcb.unplan_expense_account_ccid

    AND gcck13.code_combination_id(+) = fcb.alt_cost_account_ccid

    AND gcck14.code_combination_id(+) = fcb.write_off_account_ccid

    AND fcb.category_id = fcbd.category_id

    AND fct.category_id = fcb.category_id

    AND fcb.book_type_code = fcbd.book_type_code

    AND fcb.book_type_code = :p_book_type_code

    

Query to get Date Difference in Seconds in Oracle

DECLARE

    l_start_time   date;

l_end_time     date;

        l_date_difference    NUMBER;

BEGIN

    SELECT SYSDATE 

  INTO l_start_time 

  FROM DUAL;

fnd_file.put_line(fnd_file.log,'Program Start Time  :'||  TO_CHAR(SYSDATE,'HH24:MI:SS'));

    dbms_output.put_line('Program Start Time  :'||  TO_CHAR(SYSDATE,'HH24:MI:SS'));

   

WHILE l_Start_time IS NOT NULL

LOOP

SELECT SYSDATE 

  INTO l_end_time 

  FROM DUAL;

                l_date_difference := l_end_time - l_start_time;

    EXIT WHEN (l_date_difference)*24*60*60 = 180;

END LOOP;

fnd_file.put_line(fnd_file.log,'Program End Time     :'||  TO_CHAR(SYSDATE,'HH24:MI:SS'));

dbms_output.put_line('Program End Time  :'||  TO_CHAR(SYSDATE,'HH24:MI:SS'));

END;

Here control will be released after 180 seconds.

Query to get GL Account Description in Oracle APPS using GL_FLEXFIELDS _PKG

SELECT gcc.code_combination_id

  ,gcc.segment1

  ,apps.gl_flexfields_pkg.get_description_sql( gcc.chart_of_accounts_id,1,gcc.segment1) SEGMENT1_DESC

  ,gcc.segment2

  ,apps.gl_flexfields_pkg.get_description_sql( gcc.chart_of_accounts_id,2,gcc.segment2) SEGMENT2_DESC

  ,gcc.segment3

  ,apps.gl_flexfields_pkg.get_description_sql( gcc.chart_of_accounts_id,3,gcc.segment3) SEGMENT3_DESC

  ,gcc.segment4

  ,apps.gl_flexfields_pkg.get_description_sql( gcc.chart_of_accounts_id,4,gcc.segment4) SEGMENT4_DESC

  ,gcc.segment5

  ,apps.gl_flexfields_pkg.get_description_sql( gcc.chart_of_accounts_id,5,gcc.segment5) SEGMENT5_DESC

  ,gcc.segment6

  ,apps.gl_flexfields_pkg.get_description_sql( gcc.chart_of_accounts_id,6,gcc.segment6) SEGMENT6_DESC

FROM gl_code_combinations gcc

WHERE gcc.code_combination_id = 100000

Here gl_flexfields_pkg.get_description_sql has below parameters:

1. Chart of Account ID
2. Number of the Segment
3. Segment value for which we need Description

API to End Date FND_USER in Oracle APPS

DECLARE

    l_user_name       VARCHAR2(100)  := 'SYSADMIN';

    l_user_end_date   DATE  := SYSDATE+1;

BEGIN

fnd_user_pkg.updateuser(x_user_name               => l_user_name

                           ,x_owner                   => NULL

                           ,x_unencrypted_password    => NULL

                           ,x_start_date              => NULL

                           ,x_end_date                => l_user_end_date

                           ,x_password_date           => NULL

                           ,x_password_lifespan_days  => NULL

                           ,x_employee_id             => NULL

                           ,x_email_address           => NULL

);

 COMMIT;

EXCEPTION

  WHEN OTHERS THEN

    ROLLBACK;

    DBMS_OUTPUT.PUT_LINE(SQLERRM);

END;

Query to get Supplier Site Details(Payment Method, Term) details in Oracle APPS

SELECT assa.vendor_id

      ,asa.vendor_name 

      ,assa.vendor_site_code

      ,asa.segment1 vendor_number

      ,assa.vendor_site_id

      ,assa.address_line1

      ,assa.address_line2

      ,assa.city

      ,assa.state

      ,assa.zip postal_code

      ,assa.country

      ,asa.vat_registration_num

      ,asa.vendor_type_lookup_code vendor_type

      ,decode(nvl(assa.hold_unmatched_invoices_flag, 'N'), 'Y', 'PO', 'NONPO') invoice_type

      ,asa.invoice_currency_code currency_code

      ,assa.org_id

      ,hou.name operating_unit

      ,assa.vendor_site_code 

      ,gcc.segment1 || '.' || gcc.segment2 || '.' || gcc.segment3 || '.' || gcc.segment4 || '.' || gcc.segment5 || '.' || gcc.segment6 liability_account

      ,apt.name term_name

      ,ipmb.payment_method_code

  FROM fnd_lookup_values       lv

      ,iby_payment_methods_b   ipmb

      ,iby_ext_party_pmt_mthds epm

      ,hr_organization_units   hou

      ,iby_external_payees_all iepa

      ,ap_terms                apt

      ,gl_code_combinations    gcc

      ,ap_supplier_sites_all   assa

      ,ap_suppliers            asa

 WHERE lv.attribute2(+) = 'Y'

   AND lv.lookup_code(+) = assa.pay_group_lookup_code

   AND ipmb.payment_method_code(+) = epm.payment_method_code

   AND NVL(epm.primary_flag, 'Y') = 'Y' -- this is the current/active version.  when you make a change, object_version_number changes and old record primary_flag will change to N

   AND iepa.supplier_site_id = assa.vendor_site_id

   AND epm.ext_pmt_party_id (+) = iepa.ext_payee_id

   AND iepa.payee_party_id = asa.party_id

   AND apt.term_id = assa.terms_id

   AND gcc.code_combination_id = assa.accts_pay_code_combination_id

   AND assa.vendor_id = asa.vendor_id

   AND assa.org_id = hou.organization_id

   -- Supplier and Site Validations

   AND nvl(assa.inactive_date, SYSDATE + 1) >= TRUNC(SYSDATE)  -- site must be active

   AND nvl(asa.end_date_active, SYSDATE + 1) >= TRUNC(SYSDATE) -- supplier must be active

   AND asa.vendor_type_lookup_code = 'VENDOR'

Query to get Element Advance Salary in Oracle APPS HRMS

SELECT papf.employee_number

      ,prrv.result_value

  FROM pay_run_results prr

  ,pay_element_types_f petf

  ,pay_run_result_values prrv

  ,pay_input_values_f piv

  ,pay_assignment_actions paa

  ,pay_payroll_actions ppa

  ,per_all_assignments_f paaf

  ,per_all_people_f papf

 WHERE prr.element_type_id = petf.element_type_id

   AND prr.run_result_id = prrv.run_result_id

   AND prrv.input_value_id = piv.input_value_id

   AND prr.assignment_action_id = paa.assignment_action_id

   AND paaf.assignment_id = paa.assignment_id

   AND paaf.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')

   and paaf.person_id= papf.person_id

   AND papf.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')

   AND upper(petf.element_name) = 'ADVANCE SALARY'

   AND piv.name = 'Pay Value' 

   AND SYSDATE BETWEEN piv.effective_start_date AND  piv.effective_end_date

   AND SYSDATE BETWEEN petf.effective_start_date AND  petf.effective_end_date

   AND SYSDATE BETWEEN paaf.effective_start_date AND  paaf.effective_end_date

   AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date

   AND ppa.payroll_action_id = paa.payroll_action_id                         

   AND ppa.action_type IN ('Q', 'R')

   AND ppa.effective_date = '31-JUL-2022'

   AND prrv.result_value > '0'

AP_INVOICE_LINES_ALL.LINE_SOURCE in Oracle APPS

LINE_SOURCE --> Source of the invoice line. Validated against AP_LOOKUP_CODES.LOOKUP_CODE for LOOKUP_TYPE as LINE SOURCE

 

SELECT *

  FROM ap_lookup_codes

 WHERE lookup_type = 'LINE SOURCE'

AP_INVOICE_LINES_ALL.LINE_TYPE_LOOKUP_CODE(INVOICE LINE TYPE) in Oracle APPS

LINE_TYPE_LOOKUP_CODE --> Type of invoice line. Possible values for this column are derived from FND_LOOKUP_VALUES for lookup_type 'INVOICE LINE TYPE'.

 

SELECT *

  FROM fnd_lookup_values

 WHERE lookup_type = 'INVOICE LINE TYPE'

    AND language = USERENV('LANG')

Balance Type (Year to Date, Project to Date, Period to Date and Quarter to Date)in Oracle General Ledger

SELECT lookup_code, meaning, description, REPLACE(description, '-', ' ') balance_type

  FROM fnd_lookup_values

 WHERE 1 = 1 

    AND lookup_type = 'PTD_YTD'

    AND language = USERENV('LANG')

AP_SUPPLIER_SITES_ALL.PAY_GROUP_LOOKUP_CODE or PO_VENDOR_SITES_ALL.PAY_GROUP_LOOKUP_CODE in Oracle APPS

SELECT *

  FROM apps.fnd_lookup_values flv

 WHERE     flv.lookup_type = 'PAY GROUP'

       AND flv.LANGUAGE = USERENV ('LANG')

       AND flv.enabled_flag = 'Y'

       AND SYSDATE BETWEEN NVL (flv.START_DATE_ACTIVE, SYSDATE)

                       AND NVL (flv.END_DATE_ACTIVE, SYSDATE)

LOOKUP_CODE will be AP_SUPPLIER_SITES_ALL.PAY_GROUP_LOOKUP_CODE or PO_VENDOR_SITES_ALL.PAY_GROUP_LOOKUP_CODE

Employee Category(PER_ALL_ASSIGNMENTS_F.EMPLOYMENT_CATEGORY) in Employee Assignment Form in Oracle APPS

PER_ALL_ASSIGNMENTS_F.EMPLOYMENT_CATEGORY values coming from HR_LOOKUPS.LOOKUP_TYPE = 'EMP_CAT'

SELECT * FROM HR_LOOKUPS WHERE LOOKUP_TYPE = 'EMP_CAT' AND enabled_flag = 'Y' AND SYSDATE BETWEEN NVL (start_date_active, SYSDATE) AND NVL (end_date_active, SYSDATE)