Oracle ERP 12.0.6, Customer Aging SQL Query

 

Update:07th March 2013

A much simpler script

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

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

 

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

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

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;
/

and finally the script for view “OMS_GL_ORG_HOOKUP_V”

 

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;

 

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

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;

Enjoy another quality solution from us!

regards,

admin

9 Responses to Oracle ERP 12.0.6, Customer Aging SQL Query

  1. Prasanta says:

    Can u pls share the script for xxksalestypes

    • windows7bugs says:

      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

  2. Rajab says:

    Hi..In R12.1.3 the table hz_cust_profile_amts is empty hence whats the workaround?

    • windows7bugs says:

      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,

    • windows7bugs says:

      You may try the following, I have changed the code and removed the reference to profile amounts

      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
      
      • Rajab says:

        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’

      • windows7bugs says:

        Hi Rajab
        Please try the below 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'
        
  3. Rajab says:

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

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

%d bloggers like this: