[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]

[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]

[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]

[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]

[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]