Friday, April 12, 2013

E-business Tax: Query to list TAX_RATE_CODE based on Operating Unit in R12













In R12, we have E-Business Tax module to maintain the Tax Details. Every TAX_RATE_CODE is attached to a TAX_REGIME and in-turn a TAX_REGIME is tagged to one or more Operating Units. Below query is used to list down the TAX_RATE_CODES attached to a Operating Unit.

SELECT hou.organization_id,
       hou.set_of_books_id,
       hou.default_legal_context_id,
       hou.short_code,
       hou.NAME,
       zru.first_pty_org_id party_tax_profile_id,
       zru.tax_regime_id,
       zru.tax_regime_code,
       zxr.tax,
       zxr.tax_status_code,
       zxr.tax_rate_code,
       zxr.tax_jurisdiction_code,
       zxr.rate_type_code,
       zxr.recovery_type_code,
       zxr.percentage_rate,
       zxr.tax_rate_id,
       zxr.effective_from,
       zxr.effective_to,
       zxr.active_flag,
       zxr.attribute3,
       zxr.offset_tax,
       zxr.offset_status_code,
       zxr.offset_tax_rate_code
  FROM zx_party_tax_profile      ptp,
       zx_subscription_details   zsd,
       hr_operating_units        hou,    
       zx_regimes_usages         zru,
       zx_rates_vl               zxr
 WHERE zxr.tax_regime_code         = zru.tax_regime_code  
   AND ptp.party_type_code         = 'OU'  
   AND ptp.party_id                = hou.organization_id
   AND zru.first_pty_org_id        = ptp.party_tax_profile_id
   AND zru.first_pty_org_id        = zsd.first_pty_org_id
   AND zsd.tax_regime_code         = ZRU.TAX_REGIME_CODE
   AND zsd.parent_first_pty_org_id = -99
   AND SYSDATE BETWEEN zsd.effective_from AND NVL(zsd.effective_to,SYSDATE);
Below query is to list down the tax account based on Operating Unit,
SELECT hou.organization_id,
       hou.NAME,
       tax_account_ccid,
       zxr.tax,
       zxr.tax_status_code,
       zxr.tax_regime_code,
       zxr.tax_rate_code,
       zxr.tax_jurisdiction_code,
       zxr.rate_type_code,
       zxr.percentage_rate,
       zxr.tax_rate_id,
       zxr.effective_from,
       zxr.effective_to,
       zxr.active_flag
  FROM zx_rates_vl        zxr,
       zx_accounts        b,
       hr_operating_units hou
 WHERE b.internal_organization_id = hou.organization_id
   AND b.tax_account_entity_code = 'RATES'
   AND b.tax_account_entity_id = zxr.tax_rate_id
   AND zxr.active_flag = 'Y'
   AND SYSDATE BETWEEN zxr.effective_from AND nvl(zxr.effective_to, SYSDATE);

Friday, April 12, 2013 by Team search · 4

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('Sucessfully 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;
/

I hope it helps you!!! If so, please provide your valuable comments and share it. Thank You!!

by Team search · 6

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.