Oracle ERP 12.0.6, Customer Aging SQL Query

 

Update:07th March 2013

A much simpler script

[sourcecode language="sql" padlinenumbers="true"]
Select customer_id, customer_number,customer_name,
overall_credit_limit, currency_code,
customer_type,
 Balance,
cr_balance,
 "<=30",
 "31-60",
 "61-90",
 "91-180",
 "181-270",
"271-360",
">360"
from(
Select customer_id, customer_number,customer_name,
xx_customer_credit_limit_f(:P_ORG_ID,a.customer_id) overall_credit_limit, NULL currency_code,
decode(a.CUSTOMER_TYPE,'R','Ex','I','In') customer_type,
nvl(omscustbalance_f (a.customer_id, :P_ORG_ID ,0,0,0),0) Balance,
0 cr_balance,
omscustbalance_f (a.customer_id, :P_ORG_ID,1,0,0) "<=30",
omscustbalance_f (a.customer_id, :P_ORG_ID,2,31,60) "31-60",
omscustbalance_f (a.customer_id, :P_ORG_ID,3,61,90) "61-90",
omscustbalance_f (a.customer_id, :P_ORG_ID,4,91,180) "91-180",
omscustbalance_f (a.customer_id, :P_ORG_ID,5,181,270) "181-270",
omscustbalance_f (a.customer_id, :P_ORG_ID,6,271,360) "271-360",
omscustbalance_f (a.customer_id, :P_ORG_ID,7,0,0) ">360"
from ar_customers a
where 
1=1
and a.customer_id = NVL(:P_CUSTOMER_ID, a.customer_id)
and decode(a.CUSTOMER_TYPE,'R','Ex','I','In') = NVL(:P_CUST_TYPE, decode(a.CUSTOMER_TYPE,'R','Ex','I','In') )
and customer_id IN (Select customer_id from ar_payment_schedules_v)
and a.status = 'A'
)
where balance <> 0
ORDER BY 3
[/sourcecode]

Oracle says they have the best business practices :P, however our “Accountant” geeks believe the opposite.

They are not happy with the aging buckets, provided as standard and recently IT was challenged with a requirement to produce some output which will a total of 7 buckets!!!

Hence here it is, the script

 

[sourcecode language="sql" padlinenumbers="true"]
SELECT  su.org_id organization_id, acct.cust_account_id customer_id, acct.account_number customer_number,  PARTY.PARTY_NAME customer_name, overall_credit_limit,cl.currency_code,
decode(acct.CUSTOMER_TYPE,'R','Ex','I','In') customer_type,
omscustbalance_f (acct.cust_account_id, su.org_id,0,0,0) Balance,
(nvl(overall_credit_limit,0)-nvl(omscustbalance_f (acct.cust_account_id, su.org_id,0,0,0),0)) cr_balance,
omscustbalance_f (acct.cust_account_id, su.org_id,1,0,0) "<=30",
omscustbalance_f (acct.cust_account_id, su.org_id,2,31,60) "31-60",
omscustbalance_f (acct.cust_account_id, su.org_id,3,61,90) "61-90",
omscustbalance_f (acct.cust_account_id, su.org_id,4,91,180) "91-180",
omscustbalance_f (acct.cust_account_id, su.org_id,5,181,270) "181-270",
omscustbalance_f (acct.cust_account_id, su.org_id,6,271,360) "271-360",
omscustbalance_f (acct.cust_account_id, su.org_id,7,0,0) ">360"
       FROM   hz_cust_profile_amts cl,
            hz_cust_site_uses_all su,
            hz_cust_accounts acct,
            HZ_PARTIES PARTY,
            OMS_GL_ORG_HOOKUP_V ood
    WHERE       su.org_id = :P_ORG_ID
    and ood.organization_id = su.org_id
            AND acct.cust_account_id = NVL(:P_CUSTOMER_ID, acct.cust_account_id)
            AND party.party_id = acct.party_id
            AND cl.cust_account_id = acct.cust_account_id
            AND cl.site_use_id = su.site_use_id
            AND cl.currency_code = ood.currency_code --'KWD'
            AND su.status = 'A'
order by su.org_id, PARTY.PARTY_NAME
[/sourcecode]

With this query we are referring couple of custom objects like

omscustbalance_f – function calculating the outstanding balance

&

a custom view OMS_GL_ORG_HOOKUP_V, which links both ORG_ORGANIZATION_DEFINITIONS table and GL ledger information

Code for omscustbalance_f

[sourcecode language="sql"]
CREATE OR REPLACE FUNCTION APPS.omscustbalance_f (cust_id         IN NUMBER,
                                                  p_org_id        IN NUMBER,
                                                  bucket_number   IN NUMBER,
                                                  AGE1            IN NUMBER,
                                                  AGE2            IN NUMBER)
   RETURN NUMBER
AS
   total_due    NUMBER := 0;
   att_string   VARCHAR2 (2000);
BEGIN
   --1 <= 30
   --2 31-60
   --3 61-90
   --4 91-180
   --5 181-270
   --6 271-360
   --7 >360
   --0 balance

   IF bucket_number = 0
   THEN
      SELECT SUM (b.amount_due_remaining)
        INTO total_due
        FROM ar_payment_schedules_v b
       WHERE     b.customer_id = cust_id
             AND b.AL_STATUS_MEANING = 'Open'
             AND b.cust_trx_type_id IN (SELECT cust_trx_type_id trx_type
                                          FROM xxksalestypes
                                         WHERE org_id = p_org_id
                                        UNION ALL
                                        SELECT credit_memo_type_id trx_type
                                          FROM xxksalestypes
                                         WHERE org_id = p_org_id)
             AND b.org_id = p_org_id;
   ELSIF bucket_number = 1
   THEN
      SELECT SUM (b.amount_due_remaining)
        INTO total_due
        FROM ar_payment_schedules_v b
       WHERE     b.customer_id = cust_id
             AND b.AL_STATUS_MEANING = 'Open'
             AND b.cust_trx_type_id IN (SELECT cust_trx_type_id trx_type
                                          FROM xxksalestypes
                                         WHERE org_id = p_org_id
                                        UNION ALL
                                        SELECT credit_memo_type_id trx_type
                                          FROM xxksalestypes
                                         WHERE org_id = p_org_id)
             AND b.org_id = p_org_id
             AND b.days_past_due <= 30;
   ELSIF bucket_number = 7
   THEN
      SELECT SUM (b.amount_due_remaining)
        INTO total_due
        FROM ar_payment_schedules_v b
       WHERE     b.customer_id = cust_id
             AND b.AL_STATUS_MEANING = 'Open'
             AND b.cust_trx_type_id IN (SELECT cust_trx_type_id trx_type
                                          FROM xxksalestypes
                                         WHERE org_id = p_org_id
                                        UNION ALL
                                        SELECT credit_memo_type_id trx_type
                                          FROM xxksalestypes
                                         WHERE org_id = p_org_id)
             AND b.org_id = p_org_id
             AND b.days_past_due > 360;
   ELSE
      SELECT SUM (b.amount_due_remaining)
        INTO total_due
        FROM ar_payment_schedules_v b
       WHERE     b.customer_id = cust_id
             AND b.AL_STATUS_MEANING = 'Open'
             AND 
--you may avoid the following lines---custom requirement---
b.cust_trx_type_id IN (SELECT cust_trx_type_id trx_type
                                          FROM xxksalestypes
                                         WHERE org_id = p_org_id
                                        UNION ALL
                                        SELECT credit_memo_type_id trx_type
                                          FROM xxksalestypes
                                         WHERE org_id = p_org_id)
--you may avoid the code until here---custom requirement---
             AND b.org_id = p_org_id
             AND b.days_past_due BETWEEN AGE1 AND AGE2;
   END IF;


   RETURN TOTAL_DUE;
END omscustbalance_f;
/
[/sourcecode]

and finally the script for view “OMS_GL_ORG_HOOKUP_V”

 

[sourcecode language="sql"]
DROP VIEW APPS.OMS_GL_ORG_HOOKUP_V;

/* Formatted on 3/3/2013 8:55:56 AM (QP5 v5.163.1008.3004) */
CREATE OR REPLACE FORCE VIEW APPS.OMS_GL_ORG_HOOKUP_V
(
   LEDGER_ID,
   NAME,
   CURRENCY_CODE,
   ORGANIZATION_NAME,
   ORGANIZATION_ID,
   ORGANIZATION_CODE,
   OPERATING_UNIT
)
AS
     SELECT gll.ledger_id,
            gll.name,
            gll.currency_code,
            ood.organization_name,
            ood.organization_id,
            ood.organization_code,
            ood.operating_unit
       FROM GL_LEDGERS gll, org_organization_definitions ood
      WHERE ood.set_of_books_id = gll.ledger_id
   ORDER BY 1;
[/sourcecode]

 

Are you done yet? Not really. If you have to run the query from a PL/SQL node, you have to initialize the security using the following

[sourcecode language="sql"]
Begin
begin
MO_GLOBAL.SET_POLICY_CONTEXT('S',:P_ORG_ID);
end ;

begin
--fnd_global.apps_initialize(:P_USER_ID,:P_RESP_ID,:P_RESP_APPL_ID);
fnd_global.apps_initialize(1353, 50595, 222);
end;


begin
MO_GLOBAL.INIT('AR'); -- Receivables
end;

return (TRUE);
end;
[/sourcecode]

Enjoy another quality solution from us!

regards,

admin

9 thoughts on “Oracle ERP 12.0.6, Customer Aging SQL Query

    1. It’s actually a table, into which we keep adding transaction types

      CREATE TABLE APPS.XXKSALESTYPES
      (
      ORG_ID NUMBER(15),
      CUST_TRX_TYPE_ID NUMBER(15),
      NAME VARCHAR2(20 BYTE),
      CREDIT_MEMO_TYPE_ID NUMBER(15),
      CREDIT_MEMO_NAME VARCHAR2(20 BYTE)
      )

      HTH

    1. Hello Rajab
      We are not familiar with 12.1.3, however, we believe if you are setting up Credit limits for customers while registering them, the profile amounts must reside in this table. Are you sure your AR team is setting up the profile amounts?
      If they are not, we don’t have any workaround solutions against your query.

      Regards,

    2. You may try the following, I have changed the code and removed the reference to profile amounts
      [code]
      SELECT su.org_id organization_id, acct.cust_account_id customer_id, acct.account_number customer_number,
      PARTY.PARTY_NAME customer_name, overall_credit_limit,cl.currency_code,
      decode(acct.CUSTOMER_TYPE,’R’,’Ex’,’I’,’In’) customer_type,
      omscustbalance_f (acct.cust_account_id, su.org_id,0,0,0) Balance,
      omscustbalance_f (acct.cust_account_id, su.org_id,1,0,0) ‘<=30′,
      omscustbalance_f (acct.cust_account_id, su.org_id,2,31,60) ’31-60′,
      omscustbalance_f (acct.cust_account_id, su.org_id,3,61,90) ’61-90′,
      omscustbalance_f (acct.cust_account_id, su.org_id,4,91,180) ’91-180’,
      omscustbalance_f (acct.cust_account_id, su.org_id,5,181,270) ‘181-270’,
      omscustbalance_f (acct.cust_account_id, su.org_id,6,271,360) ‘271-360’,
      omscustbalance_f (acct.cust_account_id, su.org_id,7,0,0) ‘>360’
      FROM hz_cust_profile_amts cl,
      hz_cust_site_uses_all su,
      hz_cust_accounts acct,
      HZ_PARTIES PARTY,
      OMS_GL_ORG_HOOKUP_V ood
      WHERE su.org_id = :P_ORG_ID
      and ood.organization_id = su.org_id
      AND acct.cust_account_id = NVL(:P_CUSTOMER_ID, acct.cust_account_id)
      AND party.party_id = acct.party_id
      AND cl.cust_account_id = acct.cust_account_id
      AND cl.site_use_id = su.site_use_id
      AND cl.currency_code = ood.currency_code –‘KWD’
      AND su.status = ‘A’
      order by su.org_id, PARTY.PARTY_NAME
      [/code]

      1. Rajab

        Thanks alot…but the reference to the HZ_CUST_PROFILE_AMTS is still there.

        AND cl.cust_account_id = acct.cust_account_id
        AND cl.site_use_id = su.site_use_id
        AND cl.currency_code = ood.currency_code –‘KWD’

      2. Hi Rajab
        Please try the below code
        [code]
        SELECT su.org_id organization_id, acct.cust_account_id customer_id, acct.account_number customer_number,
        PARTY.PARTY_NAME customer_name,
        decode(acct.CUSTOMER_TYPE,’R’,’Ex’,’I’,’In’) customer_type,
        omscustbalance_f (acct.cust_account_id, su.org_id,0,0,0) Balance,
        omscustbalance_f (acct.cust_account_id, su.org_id,1,0,0) "<30",
        omscustbalance_f (acct.cust_account_id, su.org_id,2,31,60) "31-60",
        omscustbalance_f (acct.cust_account_id, su.org_id,3,61,90) "61-90",
        omscustbalance_f (acct.cust_account_id, su.org_id,4,91,180) "91-180",
        omscustbalance_f (acct.cust_account_id, su.org_id,5,181,270) "181-270",
        omscustbalance_f (acct.cust_account_id, su.org_id,6,271,360) "271-360",
        omscustbalance_f (acct.cust_account_id, su.org_id,7,0,0) ">360"
        FROM
        hz_cust_accounts acct,
        HZ_PARTIES PARTY,
        hz_cust_acct_sites_all su
        WHERE
        su.org_id = :P_ORG_ID
        AND acct.cust_account_id = NVL(:P_CUSTOMER_ID, acct.cust_account_id)
        AND su.cust_account_id = acct.cust_account_id
        AND party.party_id = acct.party_id
        AND su.status = ‘A’
        [/code]

  1. Rajab

    Hi…I hope this single word sums up your above response.
    ******************************GENIUS**********************************

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.