Function to get reporting currency conversion rate for AP invoice

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135

CREATE OR REPLACE FUNCTION APPS.XXCB_WD_GET_AP_CONV_RATE(l_ap_trx_id varchar2, P_REPORTING_CURRENCY varchar2)
  RETURN number
AS
  --
    l_trx_exchange_date      DATE;
    l_trx_exchange_rate      NUMBER;
    l_trx_currency           VARCHAR2 (20);
    l_trx_exchange_rate_type VARCHAR2 (20);
--
--    l_INVOICE_TYPE_INV       VARCHAR2(240):='Invoice';
    l_USD_ledger_id              NUMBER;
    l_GBP_ledger_id              NUMBER;
    l_EUR_ledger_id              NUMBER;        
    l_fx_rate_USD_RCUR       NUMBER;
    l_conv_rate              NUMBER;       
  --
BEGIN
--9/18/2017 this function is not tested for GBP and EUR reportin
--10/04/2017 Converting this function to handle 
--Note 

    l_trx_exchange_date             :=NULL;
    l_trx_exchange_rate             :=NULL;
    l_trx_currency                  :=NULL;
    l_trx_exchange_rate_type        :=NULL;
--
    l_USD_ledger_id                 := NULL;
    l_GBP_ledger_id                 := NULL;
    l_EUR_ledger_id                 := NULL;        
--    l_ledger_id                     := NULL;
    l_fx_rate_USD_RCUR              := NULL;
    l_conv_rate                     :=1;
--
    IF(P_REPORTING_CURRENCY = 'GBP') THEN
      SELECT ledger_id INTO l_GBP_ledger_id FROM gl_ledgers WHERE name = 'GBP_ACCRUAL';
    elsif (P_REPORTING_CURRENCY = 'EUR') THEN
      SELECT ledger_id
      INTO l_EUR_ledger_id
      FROM gl_ledgers
      WHERE name = 'EUR_ACCRUAL';
    elsif (P_REPORTING_CURRENCY = 'USD') THEN
      SELECT ledger_id
      INTO l_USD_ledger_id
      FROM gl_ledgers
      WHERE name = 'USD_ACCRUAL';      
    END IF;
--  
if  P_REPORTING_CURRENCY = 'USD'
then
          BEGIN
          --
            SELECT nvl(xal.CURRENCY_CONVERSION_RATE,1)
            INTO l_fx_rate_USD_RCUR
            FROM XLA_AE_LINES xal,
              XLA_AE_HEADERS xah
            WHERE xal.application_id      = xah.application_id
            AND xal.ae_header_id          = xah.ae_header_id
            AND xah.application_id        = 200
            AND xah.ledger_id             = l_USD_ledger_id
            AND xal.ACCOUNTING_CLASS_CODE = 'LIABILITY'
              -- AND xal.BUSINESS_CLASS_CODE   = 'RECEIVABLE'
            AND EXISTS
              (SELECT '1'
              FROM XLA.XLA_TRANSACTION_ENTITIES xte
              WHERE xte.application_id = 200
              AND xte.entity_code      = 'AP_INVOICES'
              AND xte.source_id_int_1  = l_ap_trx_id
              AND xte.entity_id        = xah.entity_id
              )
            AND rownum   = 1;
            --
            l_conv_rate := l_fx_rate_USD_RCUR;
            --
          EXCEPTION
          WHEN OTHERS THEN
            l_conv_rate := 0;
          END;
          --
          elsif P_REPORTING_CURRENCY = 'GBP'
          then
--
            SELECT nvl(xal.CURRENCY_CONVERSION_RATE,1)
            INTO l_fx_rate_USD_RCUR
            FROM XLA_AE_LINES xal,
              XLA_AE_HEADERS xah
            WHERE xal.application_id      = xah.application_id
            AND xal.ae_header_id          = xah.ae_header_id
            AND xah.application_id        = 200
            AND xah.ledger_id             = l_GBP_ledger_id
            AND xal.ACCOUNTING_CLASS_CODE = 'LIABILITY'
              -- AND xal.BUSINESS_CLASS_CODE   = 'RECEIVABLE'
            AND EXISTS
              (SELECT '1'
              FROM XLA.XLA_TRANSACTION_ENTITIES xte
              WHERE xte.application_id = 200
              AND xte.entity_code      = 'AP_INVOICES'
              AND xte.source_id_int_1  = l_ap_trx_id
              AND xte.entity_id        = xah.entity_id
              )
            AND rownum   = 1;
--
            l_conv_rate := l_fx_rate_USD_RCUR;            
--          
          elsif P_REPORTING_CURRENCY = 'EUR'
          then
--
            SELECT nvl(xal.CURRENCY_CONVERSION_RATE,1)
            INTO l_fx_rate_USD_RCUR
            FROM XLA_AE_LINES xal,
              XLA_AE_HEADERS xah
            WHERE xal.application_id      = xah.application_id
            AND xal.ae_header_id          = xah.ae_header_id
            AND xah.application_id        = 200
            AND xah.ledger_id             = l_EUR_ledger_id
            AND xal.ACCOUNTING_CLASS_CODE = 'LIABILITY'
              -- AND xal.BUSINESS_CLASS_CODE   = 'RECEIVABLE'
            AND EXISTS
              (SELECT '1'
              FROM XLA.XLA_TRANSACTION_ENTITIES xte
              WHERE xte.application_id = 200
              AND xte.entity_code      = 'AP_INVOICES'
              AND xte.source_id_int_1  = l_ap_trx_id
              AND xte.entity_id        = xah.entity_id
              )
            AND rownum   = 1;
-- 
           l_conv_rate := l_fx_rate_USD_RCUR;
--           
         end if;
          
--
RETURN l_conv_rate;
--
END;
/