Oracle Technofunc 101: August 2018

Hello. I have never imagined that my first concrete post would be about SLA things. I will share about custom SLA for revenue account for Invoice, Credit Memo, and Debit Memo in Receivable module.

I get a requirement to create custom SLA for one of our segments for combination accounts.

Here are the segments of combination accounts: A.BBBBBBB.CCCCC.DDD.EEE.FFF.GGG

Segment A : Company
Segment B : Natural Account
Segment C : Department
Segment D : Product
Segment E : Channel
Segment F : Region
Segment G : Future

I am going to create a custom SLA for segment D. It is because there is no standard source aligned with what I need. The custom Itself is derived from the Item Categories sold/returned in Invoice/Credit Memo/Debit Memo.

E.g. Based on the invoice, item 1501% should map to 101 and item 1505% should map to 103.

Here is the journal before I create the custom SLA.

The first thing I need is a function to fill the PL/SQL Function Name field.

Responsibility: AR Superuser
Navigation: Setup> Accounting> Accounting Methods Builder> Sources> Custom Source

Since I need a function to map Item Categories to Product, here is the function created by the technical:

CREATE OR REPLACE FUNCTION APPS.xgss_ar_item_source_fnc (p_trx_line_id NUMBER)

   RETURN NUMBER
IS
   v_category_id   NUMBER;
BEGIN
BEGIN
   SELECT DISTINCT MAX (mc.segment2)
     INTO v_category_id
     FROM ra_customer_trx_all rcta,
          ra_customer_trx_lines_all rctla,
          mtl_item_categories mic,
          org_organization_definitions ood,
          mtl_categories mc
    WHERE rcta.customer_trx_id = rctla.customer_trx_id
      AND mic.organization_id = ood.organization_id
      AND rcta.set_of_books_id = ood.set_of_books_id
      AND rctla.inventory_item_id = mic.inventory_item_id
      AND rctla.inventory_item_id IS NOT NULL
      AND rctla.customer_trx_line_id = p_trx_line_id
      AND mc.category_id = mic.category_id
      AND mic.category_set_id = 1100000041
      AND mc.segment1 = '15'
      AND rctla.line_type = 'LINE';
   EXCEPTION
   WHEN OTHERS
   THEN v_category_id:=NULL;
   END;

   RETURN v_category_id;
END;

The next thing is to fill the Custom Source form.

The parameter have to set as Transaction Line Identifier because the Item Categories appear in every line. I used to set the parameter to Transaction Identifier, but It turned out the custom SLA is getting the same Item Categories for the different ones.

After that, I set the Custom Source to the Account Derivation Rule (ADR).

Responsibility: AR Superuser
Navigation: Setup> Accounting> Accounting Methods Builder> Methods and Definitions> Accounting Application Definitions

I copy the Application Accounting Definitions (AAD) to be able set the custom source.