Oracle EBS R12: Payables – Query to calculate Projected payment withholding of an AP Invoice

This post will provide you with the query to calculate “Projected” payment withholding of a single or multiple AP Invoices.

You all will be well aware that when you assign withholding tax codes to an AP invoice(at payment time), Oracle provides you a projected withholding form as a standard functionality on the invoice. But when, you apply an advance to an invoice, the form disappears. The result from this query wont.

You can also pass multiple Invoice IDs to this query. These multiple invoices may contain any type of Invoice (i.e. Standard, Prepayment, Debit Memo etc.). I have incorporated the SQL into a view so you can simply pass your relevant INVOICE_ID(s) to the view and get your desired output.

The query will provide you with the following Data.

  1. invoice_id
  2. tax_name
  3. taxable_amount
  4. rate
  5. wht_amt
  6. tax_exempt
  7. exempt_certificate
  8. comments
  9. expiry_date

As you can see, it will also fetch any tax exemption data defined in the system against the Tax Code.

Note: If an exemption certificate or exception exists against a Tax Code or invoice respectively, the query will return a Projected Withholding Amount according to the certificate rate or exception rate defined. The query is designed to first check an exception defined against an invoice, then an exemption certificate and finally the standard withholding code rate.

CREATE OR REPLACE FORCE VIEW apps.ap_projected_wht_tax (invoice_id,
                                                        tax_name,
                                                        taxable_amount,
                                                        rate,
                                                        wht_amt,
                                                        tax_exempt,
                                                        exempt_certificate,
                                                        comments,
                                                        expiry_date
                                                       )
AS
   SELECT   aida.invoice_id, atc.NAME tax_name,
            SUM (aida.amount) taxable_amount,
               (  apps.ap_projected_taxrate (aag.tax_name,
                                             atc.org_id,
                                             aia.invoice_date,
                                             aia.vendor_id,
                                             aia.vendor_site_id,
                                             aia.invoice_num
                                            )
                * 100
               )
            || '%' rate,
           SUM (  apps.ap_projected_taxrate (aag.tax_name,
                                             atc.org_id,
                                             aia.invoice_date,
                                             aia.vendor_id,
                                             aia.vendor_site_id,
                                             aia.invoice_num
                                                        )
                            * aida.amount
                           )  wht_amt,
            NVL (apps.ap_wht_exemption (aia.vendor_id,
                                        aia.vendor_site_id,
                                        aia.invoice_date,
                                        atc.NAME,
                                        'TAX_EXEMPT'
                                       ),
                 'No'
                ) tax_exempt,
            apps.ap_wht_exemption (aia.vendor_id,
                                   aia.vendor_site_id,
                                   aia.invoice_date,
                                   atc.NAME,
                                   'CERTIFICATE_NUMBER'
                                  ) exempt_certificate,
            apps.ap_wht_exemption (aia.vendor_id,
                                   aia.vendor_site_id,
                                   aia.invoice_date,
                                   atc.NAME,
                                   'COMMENTS'
                                  ) comments,
            apps.ap_wht_exemption (aia.vendor_id,
                                   aia.vendor_site_id,
                                   aia.invoice_date,
                                   atc.NAME,
                                   'END_DATE'
                                  ) expiry_date
       FROM ap_invoices_all aia,
            ap_invoice_distributions_all aida,
            ap_awt_groups apg,
            ap_tax_codes_all atc,
            ap_awt_group_taxes_all aag
      WHERE aia.invoice_id = aida.invoice_id
--     AND AIA.INVOICE_ID = P_INVOICE_ID
        AND apg.GROUP_ID = aida.pay_awt_group_id
        AND apg.GROUP_ID = aag.GROUP_ID
        AND atc.NAME = aag.tax_name
        AND atc.org_id = aag.org_id
        AND atc.org_id = aia.org_id
   GROUP BY aida.invoice_id,
            atc.attribute1,
            atc.NAME,
               (  apps.ap_projected_taxrate (aag.tax_name,
                                             atc.org_id,
                                             aia.invoice_date,
                                             aia.vendor_id,
                                             aia.vendor_site_id,
                                             aia.invoice_num
                                            )
                * 100
               )
            || '%',
            NVL (apps.ap_wht_exemption (aia.vendor_id,
                                        aia.vendor_site_id,
                                        aia.invoice_date,
                                        atc.NAME,
                                        'TAX_EXEMPT'
                                       ),
                 'No'
                ),
            apps.ap_wht_exemption (aia.vendor_id,
                                   aia.vendor_site_id,
                                   aia.invoice_date,
                                   atc.NAME,
                                   'CERTIFICATE_NUMBER'
                                  ),
            apps.ap_wht_exemption (aia.vendor_id,
                                   aia.vendor_site_id,
                                   aia.invoice_date,
                                   atc.NAME,
                                   'COMMENTS'
                                  ),
            apps.ap_wht_exemption (aia.vendor_id,
                                   aia.vendor_site_id,
                                   aia.invoice_date,
                                   atc.NAME,
                                   'END_DATE'
                                  );

This view contains two functions:

  1. AP_WHT_EXEMPTION
CREATE OR REPLACE function APPS.AP_WHT_EXEMPTION   (
                                                          P_VENDOR_ID       NUMBER, 
                                                          P_VENDOR_SITE_ID  NUMBER,
                                                          P_INVOICE_DATE    DATE,
                                                          P_TAX_NAME        VARCHAR,
                                                          P_RETURN          VARCHAR
                                                          )
RETURN VARCHAR2
IS
TAX_EXEMPT VARCHAR2(100);
CERTIFICATE_NUMBER VARCHAR2(100); 
COMMENTS VARCHAR2(100);
END_DATE DATE;

 BEGIN

         SELECT DISTINCT 'Yes', AWTR.CERTIFICATE_NUMBER, AWTR.COMMENTS, AWTR.END_DATE 
           INTO TAX_EXEMPT, CERTIFICATE_NUMBER, COMMENTS, END_DATE
           FROM AP_AWT_TAX_RATES_ALL AWTR,
                AP_TAX_CODES_ALL ATC
          WHERE AWTR.VENDOR_ID = P_VENDOR_ID
            AND AWTR.VENDOR_SITE_ID = P_VENDOR_SITE_ID
            AND AWTR.TAX_NAME = P_TAX_NAME
            AND P_INVOICE_DATE BETWEEN AWTR.START_DATE AND AWTR.END_DATE
            AND ATC.NAME = AWTR.TAX_NAME
            AND ATC.ORG_ID = AWTR.ORG_ID
            AND AWTR.RATE_TYPE = 'CERTIFICATE'
            ;
  
 IF P_RETURN = 'TAX_EXEMPT' THEN
  
  RETURN TAX_EXEMPT;
 
 ELSIF P_RETURN = 'CERTIFICATE_NUMBER' THEN
  
  RETURN CERTIFICATE_NUMBER;
  
 ELSIF P_RETURN = 'COMMENTS' THEN
  
  RETURN COMMENTS;
  
 ELSIF P_RETURN = 'END_DATE' THEN
  
  RETURN END_DATE; 
  
 END IF;
 
  
 END;
/

2. AP_PROJECTED_TAXRATE

CREATE OR REPLACE function APPS.AP_PROJECTED_TAXRATE (
                                                          P_TAXNAME VARCHAR2, 
                                                          P_ORG_ID NUMBER,
                                                          P_INVOICE_DATE DATE,
                                                          P_VENDOR_ID NUMBER,
                                                          P_VENDOR_SITE_ID NUMBER,
                                                          P_INVOICE_NUM VARCHAR2
                                                          )
RETURN NUMBER
AS
AMOUNT NUMBER;
AWT_EXCEPTION VARCHAR2(200);
AWT_EXCEPTION_RATE NUMBER;
AWT_EXEMPTION VARCHAR2(200);
AWT_EXEMPTION_RATE NUMBER;
AWT_STANDARD  VARCHAR2(200);
AWT_STANDARD_RATE NUMBER;
 
 BEGIN
 
 
     BEGIN
            ---------------Evaluate Standard Rate-------------
          
            SELECT awt.rate_type,
                   (awt.tax_rate / 100)
              INTO awt_standard, 
                   awt_standard_rate
              FROM ap_awt_tax_rates_all awt
             WHERE awt.rate_type = 'STANDARD'
               AND awt.tax_name = p_taxname
               AND awt.org_id = p_org_id
               AND p_invoice_date BETWEEN awt.start_date AND awt.end_date
                  ;
                  
            EXCEPTION WHEN OTHERS THEN
                awt_standard := 'NONE';
                awt_standard_rate := 0;      
       
     END;
      
      BEGIN
            -------EVALUATE EXCEPTION----- 
        
            SELECT awt.rate_type,
                   (tax_rate/100)
              INTO awt_exception,
                   awt_exception_rate
              FROM ap_awt_tax_rates_all awt
             WHERE 1 = 1
               AND awt.rate_type = 'EXCEPTION'
               AND awt.invoice_num = p_invoice_num
               AND awt.vendor_id = p_vendor_id
               AND awt.vendor_site_id = p_vendor_site_id
               and awt.tax_name = p_taxname
               and awt.org_id = p_org_id
                    ;
          EXCEPTION WHEN OTHERS THEN
                awt_exception := 'NONE';
                awt_exception_rate := 0;
                    
      END;             
      
      BEGIN
            -----------Evaluate Tax Certificate---------
            SELECT awt.rate_type,
                   (tax_rate/100)
              INTO awt_exemption,
                   awt_exemption_rate
              FROM ap_awt_tax_rates_all awt
             WHERE 1 = 1
               AND awt.rate_type = 'CERTIFICATE'
               and p_invoice_date between awt.start_date and awt.end_date
               AND awt.vendor_id = p_vendor_id
               AND awt.vendor_site_id = p_vendor_site_id
               and awt.tax_name = p_taxname
               and awt.org_id = p_org_id
                    ;
          EXCEPTION WHEN OTHERS THEN
                awt_exemption := 'NONE';
                awt_exemption_rate := 0;          
     END;
     
     
     
 
     IF  NVL(AWT_EXCEPTION,'NONE') = 'EXCEPTION' 
     
     THEN AMOUNT:= awt_exception_rate;
     
     ELSIF NVL(AWT_EXEMPTION,'NONE') = 'CERTIFICATE' AND NVL(AWT_EXCEPTION,'NONE') = 'NONE'
     
     THEN AMOUNT:= AWT_EXEMPTION_RATE;
     
     ELSIF AWT_STANDARD = 'STANDARD' and NVL(AWT_EXCEPTION,'NONE')= 'NONE' AND NVL(AWT_EXEMPTION,'NONE') = 'NONE'
     
     THEN AMOUNT:= AWT_STANDARD_RATE;
    
    
    
     END IF;
 
 
          
 RETURN AMOUNT;
 
  
 END;
/

There you go. This query is pretty much plug and play!

Added Bonus: How I utilized this view and provided this data to the user on the Invoices form:

I personalized the Invoices form to show a popup containing the data returned from the view.

This popup shows up when the user clicks on the “withholding” field on the General tab of the invoices form. Additionally this popup only shows if the invoice is in a “Validated” status. This ensures highest quality in the projected data.

The personalization is as follows:

1.jpg

Trigger Event: WHEN-NEW-ITEM-INSTANCE

Trigger Object: INV_SUM_FOLDER.AWT_TOTAL_DISP

Condition:

:INV_SUM_FOLDER.APPROVAL_STATUS_DISPLAY in ( ‘Validated’, ‘Fully Applied’, ‘Unpaid’)

2.jpg

The function called in the message above contains the view that i have shown in this post. The syntax of the function is as follows:

CREATE OR REPLACE function APPS.AP_INVOICE_PROJECTED_TAX(P_INVOICE_ID number)
  return varchar2 is
  Result  varchar2(500);
  linertn VARCHAR(2) := CHR(13) || CHR(10);
BEGIN

--------------------------------------------------------------
-------------------------WHT Popup----------------------------
--------------------------------------------------------------
select 
        LISTAGG(
                'Tax Code: ' || TAX_NAME || ' - ' || linertn ||
                'Taxable Amount: ' || TRIM(TO_CHAR(TAXABLE_AMOUNT,'9,999,999.00'))||linertn ||
                'WHT Amount: ' || TRIM(TO_CHAR(WHT_AMT,'9,999,999.00')) ||linertn ||
                'Tax Ememption: ' ||TAX_EXEMPT|| linertn||linertn,''
                ) WITHIN GROUP(ORDER BY TAX_NAME) 
INTO RESULT
from (  
select  INVOICE_ID,
        TAX_NAME,
        TAXABLE_AMOUNT,
        RATE,
        WHT_AMT,
        TAX_EXEMPT 
  from  AP_PROJECTED_WHT_TAX tapw
 where tapw.INVOICE_ID = P_INVOICE_ID
 )
;
 return(Result);
EXCEPTION
  WHEN OTHERS THEN
    RETURN 0;
end;
/

The end result of this personalization is as follows:

3

There you have it, one of the many implications of the view.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s