Oracle – a simple function to format numbers for display

February 16, 2014

We have requirements to produce Payment Requests through Oracle E-Business suite custom interface, where the users are allowed to select different currencies based on the payment.

Different currencies means different precisions, US $ 2, Indian Rupees 2, while Kuwait, Bahrain have 3 precisions to maintain…

So the amounts displayed on reports needed to be formatted properly and we were doing some hardcoded formatting like

Now we have more currencies to deal with, hence came up with following simple function, which accepts the amount, precisions as inputs and returns a VARCHAR2 string as formatted number!

Function

CREATE OR REPLACE FUNCTION xx_format_number (P_AMOUNT IN NUMBER,
P_DEC_PREC IN NUMBER)
RETURN VARCHAR2
IS
frm_1 VARCHAR2 (30) := 'fm999999999990.';
new_num VARCHAR2 (30);
BEGIN
SELECT TO_CHAR (P_AMOUNT,
RPAD (frm_1, 15 + P_DEC_PREC, '90000000000000000'))
INTO new_num
FROM DUAL;

RETURN (new_num);

END;

You can check the function like following

SET SERVEROUTPUT ON;

DECLARE
l_new_num VARCHAR2 (30);
BEGIN

l_new_num := xx_format_number(.009,3);

DBMS_OUTPUT.PUT_LINE (l_new_num);
END;

Check it out, and let us know whether it worked for you!

for Windows7bugs

admin

Advertisements

Oracle ERP 12.0.6, Customer Aging SQL Query

March 3, 2013

 

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


Select count(*) or Select ‘X’ ??

January 15, 2012

One of the most confusing “stuff” I had with PLSQL coding! After spending these many years developing more than a dozen business applications, I still try to negotiate between Select count(*) and Select ‘X’ approaches while trying to find out whether a set of rows or particular information is available with a particular repository!

Select count(*)into some_local_variable from dual where1=2 would always return ‘0’, thus nullifying the scope of raising an exception unless further coding done from PL/SQL prospectives, as following example

if NOT(some_local_variable>0) then

message(‘Nothing found, raising error’);

raise form_trigger_failure;

end if;

While within a Begin Scope

Begin

Select ‘X’ into some_local_variable from dual where1=2;

Exception

when no_data_found then

message(‘Nothing found, raising error’);

raise form_trigger_failure;

End;

Would look more professional approach towards handling the situation. Finally the choice is entirely upon the developer, based on requirements, both may look most appropriate.

Hope this post was useful for few out there!

Regards,

Admin