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

API call failed with error ORA-01403: no data found in Package AP_VENDOR_PUB_PKG Procedure Validate_Vendor_Site

We had a requirement to update Tax Classification Code, Invoice Withholding Tax Group at supplier level. While using ap_vendor_pub_pkg.update_vendor_site API we got below error message:

The API call failed with error ORA-01403: no data found in Package AP_VENDOR_PUB_PKG Procedure Validate_Vendor_Site

But when used ap_vendor_pub_pkg.update_vendor_site_public the update was successful.

CE_PAYMENT_TRANSACTIONS.SETTLE_BY_SYSTEM_FLAG in Oracle Cash Management

CE_PAYMENT_TRANSACTIONS.SETTLE_BY_SYSTEM_FLAG Determines whether the settlement is required through oracle Payments.

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 Open FYI notifications in Oracle Workflow

SELECT wfn.recipient_role

  ,wfn.notification_id

  ,wfn.status

  ,wfn.mail_status

  ,wfn.subject

  FROM wf_item_activity_statuses wias

      ,wf_notifications wfn

 WHERE wias.notification_id=wfn.notification_id

   AND wfn.status='OPEN'

   AND wias.activity_status='COMPLETE'

   AND wfn.end_date is null

   AND wfn.message_type = 'REQAPPRV'

ORDER BY wfn.recipient_role;

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.

How to remove spaces in RTF Based Excel Output in Oracle APPS(XML Publisher Report)

Standard Trial Balance report output is Text. So client wanted to convert this report output to Excel. After converting standard Trial Balance report to excel we found issue like amount columns having leading and trailing spaces.

With this user unable to do SUM and other mathematical calculations. 

To remove spaces use below xdo function.

<?xdoxslt:trim(DISP_BEGIN_BALANCE)?>

This will remove leading and trailing spaces in Excel Output File.

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

Dependent Values in DFF Enabled Fields in Oracle APPS

 We can use :$FLEX$.Previous_Value_Set_Name to get dependent Values in DFF Enabled Fields in Oracle APPS.

Functional Setup Manager in Oracle Fusion

• Functional Setup Manager is a web application that enables you to manage and perform functional configuration tasks for offerings. 
• FSM is installed and deployed as part of OTBI Enterprise for HCM Cloud Service. 
• FSM generates a list of configuration tasks specific to the offering and functional areas that were selected. These tasks can be assigned to different functional developers and the status of the implementation project can be monitored in FSM.
• Functional Setup Manager user interfaces guide functional developers through the performance of each task. 

Configuration Manager in Oracle Fusion

• Configuration Manager is a web application for setting up and maintaining an OTBI Enterprise for HCM Cloud Service environment. 
• It also provides a quick review of setup values, and is the tool for monitoring and troubleshooting load plan executions. 
• Configuration Manager works in conjunction with Functional Setup Manager to provide guided tasks to configure offerings and functional areas.
• Using Configuration Manager, migrate configuration data across environments, using the import and export options.
• Use Configuration Manager to Monitor and manage setup data also monitor and manage load plans.

Reconciled & Unreconciled Transactions Reports in Oracle Fusion

• Reconciled Transactions Report provides details of clearing account journal lines that have been successfully reconciled by both automatic and manual reconciliation processes.

• Unreconciled Transactions Report lists all unreconciled clearing account journal lines, specifically the unreconciled lines that weren't subjected to reconciliation yet, those lines rejected by previous automatic or manual reconciliation processes, and those lines that were marked as reviewed.

Journals Day Book Report in Oracle Fusion

• This report provides posted journal entries and journal details chronologically by accounting date for a specified range of dates, journal source, and journal category.
• Report sorts journal entries for each accounting date by document number.
• It prints the accounting date, document number, journal entry name, journal source and category, subledger document name and number, currency, and conversion rate.
• Report prints for each journal line, the line number, account segment value and description, functional debit and credit amounts, description, and cost center segment value.

Journals Details Report in Oracle Fusion

• This report provides information about manually entered journals prior to posting, including field by field, all data entered into the applications or data imported from external sources.

Journals Batch Summary Report in Oracle Fusion

• This report lists posted journal batches for a particular ledger, balancing segment value, currency, and date range. 
• It provides information about actual balances for your journal batches, source, batch, and posting dates, total entered debits and credits. 
• Report sorts the information by journal batch within each journal entry category.
• This report includes totals for each journal category and a grand total for each ledger and balancing segment value combination.
• It doesn't report on budget or encumbrance balances.

General Journals Report in Oracle Fusion

• This report provides journal activity for a given period or range of periods, balancing segment value, currency, and range of account segment values.

Journals Report in Oracle Fusion

• This report provides Journal activity for a given period or range of periods, balancing segment value, currency, and range of account segment values.
• It also prints the accounting date, category, journal name, reference, journal batch name, entered debit or credit amounts, net balance, and account total for each journal.
• Report includes a total for each balancing segment and a grand total for all the activity. 

Average Balance Audit Account Analysis Report in Oracle Fusion

• This Report displays the detail account activity that created the aggregate balances and related average balances.
• It displays daily Average Balance information for the selected accounts for the specified range of dates.
• It contains parameters such as the As-Of reporting date, average balance type (period,quarter, or year average-to-date), and account ranges.

Account Analysis for Contra Account Report in Oracle Fusion

• This report Prints balances by account segment and a secondary segment.
• It lists the contra account for each journal entry and the subledger document number for transactions imported from subledgers.
• It prints by date range, accounting Flex Field range, contra account, and amount range.

General Ledger Account Details Report in Oracle Fusion

• Provides journal information to trace each transaction back to its original source.
• Prints a separate page for each balancing segment value.
• For each journal line it prints the account affected, the concatenated description, the journal line amount, and the beginning and ending account balance.
• It also prints Journal details including source, category, journal name, and effective date.
• Report lists accounts in ascending order by account segment value.
• It prints a CR next to credit amounts

Account Analysis Report in Oracle Fusion General Ledger

• Prints balances by account segment and a secondary segment for each journal entry.
• Lists the subledger document number for transactions imported from subledgers. 

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

API to get the Segment Delimiter for the specified (KFF)Key Flex Field Structure using FND_FLEX_EXT.GET_DELIMITER in Oracle APPS

DECLARE

  l_application_short_name VARCHAR2(50);--fnd_application.application_short_name

  l_key_flex_code          VARCHAR2(200);

  l_structure_number       NUMBER; -- gl_ledgers.chart_of_accounts_id

  l_segment_delimiter      VARCHAR2(10);

BEGIN

l_application_short_name := 'SQLGL';

l_key_flex_code          := 'GL#';

l_structure_number       := 51234;

l_segment_delimiter := fnd_flex_ext.get_delimiter

                    (

                      application_short_name => l_application_short_name

                     ,key_flex_code          => l_key_flex_code

                     ,structure_number       => l_structure_number

                    ); 

DBMS_OUTPUT.PUT_LINE('l_segment_delimiter = ' || l_segment_delimiter);

END;

Set Oracle Report(rdf) Concurrent Program ends with Warning Status

Create a user parameter as P_CONC_REQUEST_ID as shown in below screenshot.

In before report trigger initialize using user exists as shown in below screenshot.

Below is our requirement:

We have two optional parameters Account and Parent Account. User has to select either of the one parameter. If user selects both Parameters then the report output will be blank. So we are completing program with Warning status so that user can go review the log file.

As shown in above screenshot write srw.message to see the message in log file.

Query to get Legal Entity Name in Oracle Apps

SELECT hou.name operating_unit_name

      ,xep.name legal_entity_name

  FROM hr_operating_units hou

      ,xle_entity_profiles xep

 WHERE hou.default_legal_context_id = xep.legal_entity_id

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)

Revenue Analyst in Oracle Fusion

Revenue Analyst manages and implements revenue adjustments, revenue recognition, revenue accounting, creation and processing of revenue documents, and the inquiry of revenue documents and adjustments. 

It also manages the defining of open and closed periods and transferring entries to the GL(General Ledger).

Accounts Receivable Specialist in Oracle Fusion

Accounts Receivable Specialist manages and implements all customer related payment activities, including receiving customer payments and electronic remittance advice, performing cash handling activities, processing customer payments, and applying payments to customer accounts.

Accounts Receivable Manager in Oracle Fusion

Account Receivable Manager manages all accounts receivable activities. It also includes below activities:

• Defining policy and controls
• Establishing processes and procedures, resolving issues
• Monitoring and analyzing accounts receivable balances 
• Creating reports 
• Business Intelligence.

Query to get Next Month First Day for a given Date in Oracle

SELECT TRUNC (ADD_MONTHS (SYSDATE, 1), 'MON'), TO_CHAR (TRUNC (ADD_MONTHS (SYSDATE, 1), 'MON'), 'DD-MON-RRRR')  FROM DUAL

Assets by Cash Generating Unit Report in Oracle Fusion

Oracle Fusion Assets provides predefined reports that are used in the close process and to verify asset transactions. The Assets reports run from the Scheduled Processes work area found on the Navigator.

Assets by Cash Generating Unit Report:

• Lists all assets by cash generating unit. 
• The report extracts the details of all of the asset impairment transactions, such as asset number, cash generating unit, cost, net book value, accumulated impairment reserve, and year-to-date impairments from Oracle Fusion Assets.

Hypothetical Depreciation Analysis Report in Oracle Fusion

Oracle Fusion Assets provides predefined reports that are used in the close process and to verify asset transactions. The Assets reports run from the Scheduled Processes work area found on the Navigator.

Hypothetical Depreciation Analysis Report:

• Lists depreciation projections for hypothetical assets based on the specified depreciation parameters.

Property Tax Report in Oracle Fusion

Oracle Fusion Assets provides predefined reports that are used in the close process and to verify asset transactions. The Assets reports run from the Scheduled Processes work area found on the Navigator.

Property Tax Report:

• Lists the assets at a particular location as of the specified cutoff date. 
• This information is used for property tax forms. 
• The report is sorted by and groups totals for each balancing segment, location, asset account, and year acquired.

Revaluation Reserve Summary Report in Oracle Fusion

Oracle Fusion Assets provides predefined reports that are used in the close process and to verify asset transactions. The Assets reports run from the Scheduled Processes work area found on the Navigator.

Revaluation Reserve Summary Report; 

• Lists asset level revaluation reserve account balances for the specified periods. 
• The report is sorted by and groups totals for each balancing segment, reserve account, and cost center.

Revaluation Reserve Detail Report in Oracle Fusion

Oracle Fusion Assets provides predefined reports that are used in the close process and to verify asset transactions. The Assets reports run from the Scheduled Processes work area found on the Navigator.

Revaluation Reserve Detail Report:

• Lists asset revaluation reserve account balances for a specified period for an asset level. 
• The report is sorted by and groups totals for each balancing segment, asset cost account, and cost center.

Reserve Summary in Oracle Fusion

Oracle Fusion Assets provides predefined reports that are used in the close process and to verify asset transactions. The Assets reports run from the Scheduled Processes work area found on the Navigator.

Reserve Summary Report:

• Lists reserve account summary balances for the specified periods. 
• The report is sorted by and groups totals for each balancing segment and reserve account.

Reserve Detail Report in Oracle Fusion

Oracle Fusion Assets provides predefined reports that are used in the close process and to verify asset transactions. The Assets reports run from the Scheduled Processes work area found on the Navigator.

Reserve Detail Report:

• Lists asset level asset reserve account balances for the specified periods. 
• The report is sorted by and groups totals for each balancing segment, reserve account, and cost center.

CIP Summary Report in Oracle Fusion

Oracle Fusion Assets provides predefined reports that are used in the close process and to verify asset transactions. The Assets reports run from the Scheduled Processes work area found on the Navigator.

CIP Summary Report: 

• Lists CIP cost account summary balances for the specified periods. 
• The report is sorted by and groups totals for each balancing segment and CIP cost account.

CIP Detail Report in Oracle Fusion

Oracle Fusion Assets provides predefined reports that are used in the close process and to verify asset transactions. The Assets reports run from the Scheduled Processes work area found on the Navigator.

CIP Detail Report:

• Lists asset-level construction-in-process (CIP) cost account balances for the specified periods.
• The report is sorted by and groups totals for each balancing segment, CIP cost account, and cost center.

Journal Entry Reserve Ledger in Oracle Fusion

Oracle Fusion Assets provides predefined reports that are used in the close process and to verify asset transactions. The Assets reports run from the Scheduled Processes work area found on the Navigator.

Journal Entry Reserve Ledger:

• Lists asset depreciation amounts for the specified period. 
• The report is sorted by and groups totals for each balancing segment, expense account, reserve account, and cost center.