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
