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
Can u pls share the script for xxksalestypes
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
Hi..In R12.1.3 the table hz_cust_profile_amts is empty hence whats the workaround?
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,
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]
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’
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]
Hi…I hope this single word sums up your above response.
******************************GENIUS**********************************
Hello there
I’ll be happier if you confirm the altered scripts did the job. Against your confirmation I can update the blog and it may help few others as well.
Regards