AR-
Accounts Receivables
Table Name Description
RA_CUST_TRX_TYPES_ALL Transaction type for invoices, commitments and credit
memos
RA_CUSTOMER_TRX_ALL Header-level information about invoices, debit memos,
chargebacks, commitments and credit memos
RA_CUSTOMER_TRX_LINES_ALL Invoice, debit memo, chargeback, credit memo and
commitment lines
RA_CUST_TRX_LINE_GL_DIST_ALL Accounting records for revenue, unearned revenue
and unbilled receivables
RA_CUST_TRX_LINE_SALESREPS_ALL Sales credit assignments for transactions
AR_ADJUSTMENTS_ALL Pending and approved invoice adjustments
RA_BATCHES_ALL
AR_CASH_RECEIPTS_ALL Detailed receipt information
AR_CASH_RECEIPT_HISTORY_ALL History of actions and status changes in the life
cycle of a receipt
AR_PAYMENT_SCHEDULES_ALL All transactions except adjustments and miscellaneous
cash receipts
AR_RECEIVABLE_APPLICATIONS_ALL Accounting information for cash and credit memo
applications
AR_TRANSACTION_HISTORY_ALL Life cycle of a transaction
HZ_CUST_ACCOUNTS Stores information about customer accounts.
HZ_CUSTOMER_PROFILES Credit information for customer accounts and customer
account sites
HZ_CUST_ACCT_SITES_ALL Stores all customer account sites across all operating
units
HZ_CUST_ACCT_RELATE_ALL Relationships between customer accounts
HZ_CUST_CONTACT_POINTS This table is no longer used
HZ_CUST_PROF_CLASS_AMTS Customer profile class amount limits for each currency
HZ_CUST_SITE_USES_ALL Stores business purposes assigned to customer account
sites.
HZ_LOCATIONS Physical addresses
HZ_ORG_CONTACTS People as contacts for parties
HZ_ORG_CONTACT_ROLES Roles played by organization contacts
HZ_PARTIES Information about parties such as organizations, people, and groups
HZ_PARTY_SITES Links party to physical locations
HZ_PARTY_SITE_USES The way that a party uses a particular site or address
HZ_RELATIONSHIPS Relationships between entities
HZ_RELATIONSHIP_TYPES Relationship types
1.How to get Customer Address Details in Oracle apps?
The following Query will provide the customer address details in oracle applications.
SELECT hca.account_number customer_number,
hp.party_name customer_name,
hps.party_site_number site_number, hl.address1 address1,
hl.address2 address2, hl.address3 address3,
hl.address4 address4, hl.city city,
hl.postal_code postal_code, hl.state state,
ftt.territory_short_name country,
hcsua1.LOCATION bill_to_location,
hcsua2.LOCATION ship_to_location
FROM hz_parties hp,
hz_party_sites hps,
hz_cust_accounts hca,
hz_cust_acct_sites_all hcasa1,
hz_cust_site_uses_all hcsua1,
hz_locations hl,
fnd_territories_tl ftt,
hz_cust_acct_sites_all hcasa2,
hz_cust_site_uses_all hcsua2
WHERE hp.party_id = hps.party_id(+)
AND hp.party_id = hca.party_id(+)
AND hcasa1.party_site_id(+) = hps.party_site_id
AND hcasa2.party_site_id(+) = hps.party_site_id
AND hcsua1.cust_acct_site_id(+) = hcasa1.cust_acct_site_id
AND hcsua2.cust_acct_site_id(+) = hcasa2.cust_acct_site_id
AND hcsua1.site_use_code(+) = 'bill_to'
AND hcsua2.site_use_code(+) = 'ship_to'
AND hcasa1.org_id(+) = fnd_profile.VALUE ('org_id')
AND hcasa2.org_id(+) = fnd_profile.VALUE ('org_id')
AND hps.location_id = hl.location_id
AND hl.country = ftt.territory_code
AND ftt.LANGUAGE = USERENV ('lang')
ORDER BY customer_number;
2. Debtor Ledger Report Query
SELECT DISTINCT B.GL_DATE,
c.type type1,
--d.CUSTOMER_CATEGORY_CODE ,
-- d.customer_name,
party
.PARTY_NAME customer_name,
-- c.NAME Vch_Type,
decode(c.type,'INV','Invoice',
'CM','Credit Memo',
'DM','Debit Memo') Vch_Type,
a.trx_number doc_no,
a.trx_date doc_dt,
--c.DESCRIPTION particular,
d.description particular,
b.amount * nvl(a.EXCHANGE_RATE,1) amount,
--a.customer_trx_id,
-- a.INTERFACE_HEADER_ATTRIBUTE1 ref_no,
--d.customer_id,
cust.CUST_ACCOUNT_ID,
null,
null,
null,
null
FROM RA_CUSTOMER_TRX_ALL a,
RA_CUST_TRX_LINE_GL_DIST_ALL b,
RA_CUST_TRX_TYPES_ALL C ,
hz_cust_accounts cust,
hz_parties party,
hz_cust_acct_sites_all hcasa,
hz_cust_site_uses_all hcsua ,
(SELECT distinct gcc.CODE_COMBINATION_ID, gcc.segment4, ffvl.description,lgd.CUSTOMER_TRX_ID
FROM RA_CUSTOMER_TRX_ALL a,
ra_cust_trx_line_gl_dist_all lgd,
gl_code_combinations gcc,
fnd_flex_values_vl ffvl
WHERE a.CUSTOMER_TRX_ID=lgd.CUSTOMER_TRX_ID
and lgd.code_combination_id = gcc.code_combination_id
AND gcc.segment4 = ffvl.flex_value
AND lgd.account_class = 'REV'
-- and gcc.segment4=31111
--and a.trx_number=10130000002
) d
WHERE cust.party_id = party.party_id
and cust.CUST_ACCOUNT_ID=hcasa.cust_account_id
and a.CUSTOMER_TRX_ID=d.CUSTOMER_TRX_ID
and hcasa.cust_acct_site_id = hcsua.cust_acct_site_id
AND a.customer_trx_id=b.customer_trx_id
AND a.cust_trx_type_id = c.cust_trx_type_id
AND a.bill_to_customer_id =cust.CUST_ACCOUNT_ID
AND A.COMPLETE_FLAG = 'Y'
AND b.org_id=nvl(:P_ORG_ID,b.org_id)
AND b.latest_rec_flag ='Y'
AND c.type in ('INV','CM','DM')
AND B.GL_DATE is not null
AND trunc(b.gl_date) between nvl(:p_from_date,b.gl_date) AND nvl(:p_to_date,b.gl_date)
AND cust.CUST_ACCOUNT_ID =nvl(:P_FROM_CUSTOMER_ID,cust.CUST_ACCOUNT_ID)
union
SELECT DISTINCT (G.GL_DATE),
'REC' type1,
party.PARTY_NAME customer_name,
--e.TYPE vch_type,
'Receipt' vch_type,
E.RECEIPT_NUMBER doc_no,
e.RECEIPT_DATE doc_dt ,
ABCA.BANK_ACCOUNT_NAME particular,
e.AMOUNT AMOUNT ,
cust.CUST_ACCOUNT_ID,
-- e.cash_receipt_id,
null,
null,
null,
null
FROM ar_cash_receipts_all e,
ar_cash_receipt_history_all g,
ce_bank_accounts abca,
ce_bank_acct_uses_all abcau,
hz_cust_accounts cust,
HZ_PARTIES party,
AR_RECEIVABLE_APPLICATIONS_ALL f,
AR_PAYMENT_SCHEDULES_all d
WHERE e.cash_receipt_id = g.cash_receipt_id
AND abcau.bank_account_id = abca.bank_account_id
AND e.remit_bank_acct_use_id = abcau.bank_acct_use_id
AND e.pay_from_customer = cust.cust_account_id
and party.PARTY_ID=cust.PARTY_ID
--and hcasa.CUST_ACCT_SITE_ID=hcsua.CUST_ACCT_SITE_ID
--and cust.CUST_ACCOUNT_ID=hcasa.cust_account_id
-- and cust.CUST_ACCOUNT_ID=hcasa.cust_account_id
and e.CASH_RECEIPT_ID=f.RECEIVABLE_APPLICATION_ID
and d.PAYMENT_SCHEDULE_ID=f.PAYMENT_SCHEDULE_ID
-- AND g.status = 'CLEARED'
AND G.gl_date between nvl(:p_from_date,g.gl_date) AND nvl(:p_to_date,g.gl_date)
AND cust.CUST_ACCOUNT_ID =nvl(:P_FROM_CUSTOMER_ID,cust.CUST_ACCOUNT_ID)
AND G.ORG_ID=nvl(:P_ORG_ID,g.org_id)
union
Select DISTINCT (G.GL_DATE),
'REV' type1,
--rc.CUSTOMER_CATEGORY_CODE,
party.PARTY_NAME customer_name,
-- e.TYPE vch_type,
'Receipt' vch_type,
E.RECEIPT_NUMBER doc_no,
e.RECEIPT_DATE doc_dt ,
ABCA.BANK_ACCOUNT_NAME particular,
e.amount AMOUNT ,
-- null,
--e.COMMENTS,--a.TRX_NUMBER||' '||ABCA.BANK_ACCOUNT_NAME ref_no ,
-- hp.PARTY_NAME,
--e.cash_receipt_id,
--e.REVERSAL_COMMENTS
cust.CUST_ACCOUNT_ID,
null,
null,
null,
null
from AR_PAYMENT_SCHEDULES_all d ,
AR_CASH_RECEIPTS_all e ,
AR_RECEIVABLE_APPLICATIONS_ALL f ,
ar_cash_receipt_history_all G ,
--RA_CUSTOMER_TRX_ALL A ,
hz_cust_accounts cust,
hz_parties party,
hz_cust_acct_sites_all hcasa,
hz_cust_site_uses_all hcsua,
CE_BANK_ACCOUNTS ABCA
where d.PAYMENT_SCHEDULE_ID = f.PAYMENT_SCHEDULE_ID
and f.CASH_RECEIPT_ID = e.CASH_RECEIPT_ID(+)
-- AND f.AMOUNT_APPLIED IS NOT NULL
and f.CASH_RECEIPT_ID = G.CASH_RECEIPT_ID
--and d.CUSTOMER_ID = nvl(:P_FROM_CUSTOMER_ID,d.customer_id)
and d.CUSTOMER_ID in(select distinct cust_account_id from hz_cust_acct_sites_all
where cust_acct_site_id in (select distinct cust_acct_site_id from hz_cust_site_uses_all
--where GL_ID_REC=:P_CODE)
))
AND G.gl_date between nvl(:p_from_date,g.gl_date) AND nvl(:p_to_date,g.gl_date)
and g.FIRST_POSTED_RECORD_FLAG='N'
AND G.ORG_ID=nvl(:P_ORG_ID,g.org_id)
-- and rc.customer_id=d.CUSTOMER_ID
-- and rc.customer_id=d.CUSTOMER_ID
--and rc.CUSTOMER_ID=hcasa.CUST_ACCOUNT_ID
and cust.party_id = party.party_id
-- and D.customer_id = hcasa.cust_account_id
and cust.CUST_ACCOUNT_ID=hcasa.cust_account_id
and d.CUSTOMER_ID=hcasa.CUST_ACCOUNT_ID
and hcasa.CUST_ACCT_SITE_ID=hcsua.CUST_ACCT_SITE_ID
and e.receipt_method_id NOT IN (SELECT receipt_method_id FROM AR_RECEIPT_METHODS
WHERE UPPER(NAME) LIKE 'ADVANCE%')
AND E.REVERSAL_DATE IS not NULL
AND E.REMITTANCE_BANK_ACCOUNT_ID=ABCA.BANK_ACCOUNT_ID
--AND HCSUA.GL_ID_REC=:P_CODE
and e.STATUS in ('REV','NSF','STOP') --Added by JD 20/04/09
-- and e.STATUS='REV' --Removed by JD 20/04/09
AND E.CASH_RECEIPT_ID IN (SELECT CASH_RECEIPT_ID FROM AR_CASH_RECEIPT_HISTORY_ALL A
WHERE A.STATUS = 'REVERSED')
AND g.REVERSAL_GL_DATE is null --Added by JD 10/01/09
AND g.REVERSAL_CASH_RECEIPT_HIST_ID is null
AND cust.CUST_ACCOUNT_ID =nvl(:P_FROM_CUSTOMER_ID,cust.CUST_ACCOUNT_ID)
union
select distinct
h.GL_DATE,
'EXGL' type,
--rc.CUSTOMER_CATEGORY_CODE,
party.PARTY_NAME customer_name,
'Receipt' vch_type,
c.RECEIPT_NUMBER doc_no,--trx number
c.RECEIPT_DATE doc_dt,
ABCA.BANK_ACCOUNT_NAME particular,
--sum((c.EXCHANGE_RATE-t.EXCHANGE_RATE)*(AMOUNT_APPLIED))
sum((nvl(c.exchange_rate,r.TRANS_TO_RECEIPT_RATE)-t.EXCHANGE_RATE)*(r.AMOUNT_APPLIED)) EXGAINLOSS,
--(c.EXCHANGE_RATE-t.EXCHANGE_RATE)*(AMOUNT_APPLIED) AMOUNT,
--rc.CUSTOMER_ID ,
cust.CUST_ACCOUNT_ID,
null,
null,
null,
null
from RA_CUSTOMER_TRX_ALL t,
AR_RECEIVABLE_APPLICATIONS_ALL r,
ar_cash_receipts_all c,
ar_cash_receipt_history_all h,
CE_BANK_ACCOUNTS ABCA,
hz_cust_accounts cust,
hz_parties party
where r.APPLIED_CUSTOMER_TRX_ID = t.CUSTOMER_TRX_ID
and c.CASH_RECEIPT_ID = r.CASH_RECEIPT_ID
and cust.party_id = party.party_id
and c.CASH_RECEIPT_ID = h.CASH_RECEIPT_ID
and c.PAY_FROM_CUSTOMER = cust.CUST_ACCOUNT_ID
-- AND c.REMITTANCE_BANK_ACCOUNT_ID=ABCA.BANK_ACCOUNT_ID
and c.REMIT_BANK_ACCT_USE_ID=ABCA.BANK_ACCOUNT_ID
and cust.PARTY_ID=party.PARTY_ID
and c.PAY_FROM_CUSTOMER = cust.CUST_ACCOUNT_ID
--and r.CASH_RECEIPT_ID = h.CASH_RECEIPT_ID
--and c.PAY_FROM_CUSTOMER = 1040
and h.STATUS = 'CLEARED'
-- and h.STATUS = 'REMITTED'
--and h.GL_DATE >'31-MAR-2008'
and h.gl_date between nvl(:p_from_date,h.gl_date) AND nvl(:p_to_date,h.gl_date)
and h.ORG_ID=nvl(:P_ORG_ID,h.org_id)
AND cust.CUST_ACCOUNT_ID =nvl(:P_FROM_CUSTOMER_ID,cust.CUST_ACCOUNT_ID)
--and c.CURRENCY_CODE <> 'INR'
--AND RC.CUSTOMER_ID =:P_FROM_CUSTOMER_ID--new
--and (nvl(c.exchange_rate,r.TRANS_TO_RECEIPT_RATE)-t.EXCHANGE_RATE)*(r.AMOUNT_APPLIED)<>0 --new
GROUP BY h.GL_DATE,TYPE,c.RECEIPT_NUMBER,c.RECEIPT_DATE,null,
null,c.CASH_RECEIPT_ID,c.COMMENTS, party.PARTY_NAME,ABCA.BANK_ACCOUNT_NAME
,cust.CUST_ACCOUNT_ID
union all
SELECT DISTINCT
B.GL_DATE,
'ADJ' type1,
-- d.CUSTOMER_CATEGORY_CODE,
party.PARTY_NAME,
c.NAME Vch_Type,
a.trx_number,
a.trx_date ,
-- c.DESCRIPTION particular,
d.description particular,
b.amount * nvl(a.EXCHANGE_RATE,1) amount,
a.customer_trx_id,
a.INTERFACE_HEADER_ATTRIBUTE1 ref_no,
-- d.customer_id,
null,
null,
null
FROM RA_CUSTOMER_TRX_ALL a,
ar_adjustments_all b,
RA_CUST_TRX_TYPES_ALL C ,
--AR_CUSTOMERS d,
hz_cust_accounts cust,
hz_parties party,
hz_cust_acct_sites_all hcasa,
hz_cust_site_uses_all hcsua,
(SELECT distinct gcc.CODE_COMBINATION_ID, gcc.segment4, ffvl.description,lgd.CUSTOMER_TRX_ID
FROM RA_CUSTOMER_TRX_ALL a,
ra_cust_trx_line_gl_dist_all lgd,
gl_code_combinations gcc,
fnd_flex_values_vl ffvl
WHERE a.CUSTOMER_TRX_ID=lgd.CUSTOMER_TRX_ID
and lgd.code_combination_id = gcc.code_combination_id
AND gcc.segment4 = ffvl.flex_value
AND lgd.account_class = 'REV'
-- and gcc.segment4=31111
--and a.trx_number=10130000002
) d
WHERE a.customer_trx_id=b.customer_trx_id
AND a.cust_trx_type_id = c.cust_trx_type_id
and cust.party_id = party.party_id
AND a.bill_to_customer_id =cust.CUST_ACCOUNT_ID
and cust.CUST_ACCOUNT_ID=hcasa.cust_account_id
and
hcasa.cust_acct_site_id = hcsua.cust_acct_site_id
and a.CUSTOMER_TRX_ID=d.CUSTOMER_TRX_ID
--and a.customer_trx_id <> '69735'
--AND a.sold_to_customer_id =d.customer_id
AND b.org_id=nvl(:P_ORG_ID,b.org_id)
--AND c.type in ('INV','CM','DM')
AND B. GL_DATE is not null
--AND a.trx_number =:P_TRX
AND trunc(b.gl_date) between nvl(:p_from_date,b.gl_date) AND nvl(:p_to_date,b.gl_date)
AND cust.CUST_ACCOUNT_ID =nvl(:P_FROM_CUSTOMER_ID,cust.CUST_ACCOUNT_ID)
--AND HCSUA.GL_ID_REC=:P_CODE;
order by 1,3
SELECT hca.account_number customer_number,
hp.party_name customer_name,
hps.party_site_number site_number, hl.address1 address1,
hl.address2 address2, hl.address3 address3,
hl.address4 address4, hl.city city,
hl.postal_code postal_code, hl.state state,
ftt.territory_short_name country,
hcsua1.LOCATION bill_to_location,
hcsua2.LOCATION ship_to_location
FROM hz_parties hp,
hz_party_sites hps,
hz_cust_accounts hca,
hz_cust_acct_sites_all hcasa1,
hz_cust_site_uses_all hcsua1,
hz_locations hl,
fnd_territories_tl ftt,
hz_cust_acct_sites_all hcasa2,
hz_cust_site_uses_all hcsua2
WHERE hp.party_id = hps.party_id(+)
AND hp.party_id = hca.party_id(+)
AND hcasa1.party_site_id(+) = hps.party_site_id
AND hcasa2.party_site_id(+) = hps.party_site_id
AND hcsua1.cust_acct_site_id(+) = hcasa1.cust_acct_site_id
AND hcsua2.cust_acct_site_id(+) = hcasa2.cust_acct_site_id
AND hcsua1.site_use_code(+) = 'bill_to'
AND hcsua2.site_use_code(+) = 'ship_to'
AND hcasa1.org_id(+) = fnd_profile.VALUE ('org_id')
AND hcasa2.org_id(+) = fnd_profile.VALUE ('org_id')
AND hps.location_id = hl.location_id
AND hl.country = ftt.territory_code
AND ftt.LANGUAGE = USERENV ('lang')
ORDER BY customer_number;
2. Debtor Ledger Report Query
SELECT DISTINCT B.GL_DATE,
c.type type1,
--d.CUSTOMER_CATEGORY_CODE ,
-- d.customer_name,
party
.PARTY_NAME customer_name,
-- c.NAME Vch_Type,
decode(c.type,'INV','Invoice',
'CM','Credit Memo',
'DM','Debit Memo') Vch_Type,
a.trx_number doc_no,
a.trx_date doc_dt,
--c.DESCRIPTION particular,
d.description particular,
b.amount * nvl(a.EXCHANGE_RATE,1) amount,
--a.customer_trx_id,
-- a.INTERFACE_HEADER_ATTRIBUTE1 ref_no,
--d.customer_id,
cust.CUST_ACCOUNT_ID,
null,
null,
null,
null
FROM RA_CUSTOMER_TRX_ALL a,
RA_CUST_TRX_LINE_GL_DIST_ALL b,
RA_CUST_TRX_TYPES_ALL C ,
hz_cust_accounts cust,
hz_parties party,
hz_cust_acct_sites_all hcasa,
hz_cust_site_uses_all hcsua ,
(SELECT distinct gcc.CODE_COMBINATION_ID, gcc.segment4, ffvl.description,lgd.CUSTOMER_TRX_ID
FROM RA_CUSTOMER_TRX_ALL a,
ra_cust_trx_line_gl_dist_all lgd,
gl_code_combinations gcc,
fnd_flex_values_vl ffvl
WHERE a.CUSTOMER_TRX_ID=lgd.CUSTOMER_TRX_ID
and lgd.code_combination_id = gcc.code_combination_id
AND gcc.segment4 = ffvl.flex_value
AND lgd.account_class = 'REV'
-- and gcc.segment4=31111
--and a.trx_number=10130000002
) d
WHERE cust.party_id = party.party_id
and cust.CUST_ACCOUNT_ID=hcasa.cust_account_id
and a.CUSTOMER_TRX_ID=d.CUSTOMER_TRX_ID
and hcasa.cust_acct_site_id = hcsua.cust_acct_site_id
AND a.customer_trx_id=b.customer_trx_id
AND a.cust_trx_type_id = c.cust_trx_type_id
AND a.bill_to_customer_id =cust.CUST_ACCOUNT_ID
AND A.COMPLETE_FLAG = 'Y'
AND b.org_id=nvl(:P_ORG_ID,b.org_id)
AND b.latest_rec_flag ='Y'
AND c.type in ('INV','CM','DM')
AND B.GL_DATE is not null
AND trunc(b.gl_date) between nvl(:p_from_date,b.gl_date) AND nvl(:p_to_date,b.gl_date)
AND cust.CUST_ACCOUNT_ID =nvl(:P_FROM_CUSTOMER_ID,cust.CUST_ACCOUNT_ID)
union
SELECT DISTINCT (G.GL_DATE),
'REC' type1,
party.PARTY_NAME customer_name,
--e.TYPE vch_type,
'Receipt' vch_type,
E.RECEIPT_NUMBER doc_no,
e.RECEIPT_DATE doc_dt ,
ABCA.BANK_ACCOUNT_NAME particular,
e.AMOUNT AMOUNT ,
cust.CUST_ACCOUNT_ID,
-- e.cash_receipt_id,
null,
null,
null,
null
FROM ar_cash_receipts_all e,
ar_cash_receipt_history_all g,
ce_bank_accounts abca,
ce_bank_acct_uses_all abcau,
hz_cust_accounts cust,
HZ_PARTIES party,
AR_RECEIVABLE_APPLICATIONS_ALL f,
AR_PAYMENT_SCHEDULES_all d
WHERE e.cash_receipt_id = g.cash_receipt_id
AND abcau.bank_account_id = abca.bank_account_id
AND e.remit_bank_acct_use_id = abcau.bank_acct_use_id
AND e.pay_from_customer = cust.cust_account_id
and party.PARTY_ID=cust.PARTY_ID
--and hcasa.CUST_ACCT_SITE_ID=hcsua.CUST_ACCT_SITE_ID
--and cust.CUST_ACCOUNT_ID=hcasa.cust_account_id
-- and cust.CUST_ACCOUNT_ID=hcasa.cust_account_id
and e.CASH_RECEIPT_ID=f.RECEIVABLE_APPLICATION_ID
and d.PAYMENT_SCHEDULE_ID=f.PAYMENT_SCHEDULE_ID
-- AND g.status = 'CLEARED'
AND G.gl_date between nvl(:p_from_date,g.gl_date) AND nvl(:p_to_date,g.gl_date)
AND cust.CUST_ACCOUNT_ID =nvl(:P_FROM_CUSTOMER_ID,cust.CUST_ACCOUNT_ID)
AND G.ORG_ID=nvl(:P_ORG_ID,g.org_id)
union
Select DISTINCT (G.GL_DATE),
'REV' type1,
--rc.CUSTOMER_CATEGORY_CODE,
party.PARTY_NAME customer_name,
-- e.TYPE vch_type,
'Receipt' vch_type,
E.RECEIPT_NUMBER doc_no,
e.RECEIPT_DATE doc_dt ,
ABCA.BANK_ACCOUNT_NAME particular,
e.amount AMOUNT ,
-- null,
--e.COMMENTS,--a.TRX_NUMBER||' '||ABCA.BANK_ACCOUNT_NAME ref_no ,
-- hp.PARTY_NAME,
--e.cash_receipt_id,
--e.REVERSAL_COMMENTS
cust.CUST_ACCOUNT_ID,
null,
null,
null,
null
from AR_PAYMENT_SCHEDULES_all d ,
AR_CASH_RECEIPTS_all e ,
AR_RECEIVABLE_APPLICATIONS_ALL f ,
ar_cash_receipt_history_all G ,
--RA_CUSTOMER_TRX_ALL A ,
hz_cust_accounts cust,
hz_parties party,
hz_cust_acct_sites_all hcasa,
hz_cust_site_uses_all hcsua,
CE_BANK_ACCOUNTS ABCA
where d.PAYMENT_SCHEDULE_ID = f.PAYMENT_SCHEDULE_ID
and f.CASH_RECEIPT_ID = e.CASH_RECEIPT_ID(+)
-- AND f.AMOUNT_APPLIED IS NOT NULL
and f.CASH_RECEIPT_ID = G.CASH_RECEIPT_ID
--and d.CUSTOMER_ID = nvl(:P_FROM_CUSTOMER_ID,d.customer_id)
and d.CUSTOMER_ID in(select distinct cust_account_id from hz_cust_acct_sites_all
where cust_acct_site_id in (select distinct cust_acct_site_id from hz_cust_site_uses_all
--where GL_ID_REC=:P_CODE)
))
AND G.gl_date between nvl(:p_from_date,g.gl_date) AND nvl(:p_to_date,g.gl_date)
and g.FIRST_POSTED_RECORD_FLAG='N'
AND G.ORG_ID=nvl(:P_ORG_ID,g.org_id)
-- and rc.customer_id=d.CUSTOMER_ID
-- and rc.customer_id=d.CUSTOMER_ID
--and rc.CUSTOMER_ID=hcasa.CUST_ACCOUNT_ID
and cust.party_id = party.party_id
-- and D.customer_id = hcasa.cust_account_id
and cust.CUST_ACCOUNT_ID=hcasa.cust_account_id
and d.CUSTOMER_ID=hcasa.CUST_ACCOUNT_ID
and hcasa.CUST_ACCT_SITE_ID=hcsua.CUST_ACCT_SITE_ID
and e.receipt_method_id NOT IN (SELECT receipt_method_id FROM AR_RECEIPT_METHODS
WHERE UPPER(NAME) LIKE 'ADVANCE%')
AND E.REVERSAL_DATE IS not NULL
AND E.REMITTANCE_BANK_ACCOUNT_ID=ABCA.BANK_ACCOUNT_ID
--AND HCSUA.GL_ID_REC=:P_CODE
and e.STATUS in ('REV','NSF','STOP') --Added by JD 20/04/09
-- and e.STATUS='REV' --Removed by JD 20/04/09
AND E.CASH_RECEIPT_ID IN (SELECT CASH_RECEIPT_ID FROM AR_CASH_RECEIPT_HISTORY_ALL A
WHERE A.STATUS = 'REVERSED')
AND g.REVERSAL_GL_DATE is null --Added by JD 10/01/09
AND g.REVERSAL_CASH_RECEIPT_HIST_ID is null
AND cust.CUST_ACCOUNT_ID =nvl(:P_FROM_CUSTOMER_ID,cust.CUST_ACCOUNT_ID)
union
select distinct
h.GL_DATE,
'EXGL' type,
--rc.CUSTOMER_CATEGORY_CODE,
party.PARTY_NAME customer_name,
'Receipt' vch_type,
c.RECEIPT_NUMBER doc_no,--trx number
c.RECEIPT_DATE doc_dt,
ABCA.BANK_ACCOUNT_NAME particular,
--sum((c.EXCHANGE_RATE-t.EXCHANGE_RATE)*(AMOUNT_APPLIED))
sum((nvl(c.exchange_rate,r.TRANS_TO_RECEIPT_RATE)-t.EXCHANGE_RATE)*(r.AMOUNT_APPLIED)) EXGAINLOSS,
--(c.EXCHANGE_RATE-t.EXCHANGE_RATE)*(AMOUNT_APPLIED) AMOUNT,
--rc.CUSTOMER_ID ,
cust.CUST_ACCOUNT_ID,
null,
null,
null,
null
from RA_CUSTOMER_TRX_ALL t,
AR_RECEIVABLE_APPLICATIONS_ALL r,
ar_cash_receipts_all c,
ar_cash_receipt_history_all h,
CE_BANK_ACCOUNTS ABCA,
hz_cust_accounts cust,
hz_parties party
where r.APPLIED_CUSTOMER_TRX_ID = t.CUSTOMER_TRX_ID
and c.CASH_RECEIPT_ID = r.CASH_RECEIPT_ID
and cust.party_id = party.party_id
and c.CASH_RECEIPT_ID = h.CASH_RECEIPT_ID
and c.PAY_FROM_CUSTOMER = cust.CUST_ACCOUNT_ID
-- AND c.REMITTANCE_BANK_ACCOUNT_ID=ABCA.BANK_ACCOUNT_ID
and c.REMIT_BANK_ACCT_USE_ID=ABCA.BANK_ACCOUNT_ID
and cust.PARTY_ID=party.PARTY_ID
and c.PAY_FROM_CUSTOMER = cust.CUST_ACCOUNT_ID
--and r.CASH_RECEIPT_ID = h.CASH_RECEIPT_ID
--and c.PAY_FROM_CUSTOMER = 1040
and h.STATUS = 'CLEARED'
-- and h.STATUS = 'REMITTED'
--and h.GL_DATE >'31-MAR-2008'
and h.gl_date between nvl(:p_from_date,h.gl_date) AND nvl(:p_to_date,h.gl_date)
and h.ORG_ID=nvl(:P_ORG_ID,h.org_id)
AND cust.CUST_ACCOUNT_ID =nvl(:P_FROM_CUSTOMER_ID,cust.CUST_ACCOUNT_ID)
--and c.CURRENCY_CODE <> 'INR'
--AND RC.CUSTOMER_ID =:P_FROM_CUSTOMER_ID--new
--and (nvl(c.exchange_rate,r.TRANS_TO_RECEIPT_RATE)-t.EXCHANGE_RATE)*(r.AMOUNT_APPLIED)<>0 --new
GROUP BY h.GL_DATE,TYPE,c.RECEIPT_NUMBER,c.RECEIPT_DATE,null,
null,c.CASH_RECEIPT_ID,c.COMMENTS, party.PARTY_NAME,ABCA.BANK_ACCOUNT_NAME
,cust.CUST_ACCOUNT_ID
union all
SELECT DISTINCT
B.GL_DATE,
'ADJ' type1,
-- d.CUSTOMER_CATEGORY_CODE,
party.PARTY_NAME,
c.NAME Vch_Type,
a.trx_number,
a.trx_date ,
-- c.DESCRIPTION particular,
d.description particular,
b.amount * nvl(a.EXCHANGE_RATE,1) amount,
a.customer_trx_id,
a.INTERFACE_HEADER_ATTRIBUTE1 ref_no,
-- d.customer_id,
null,
null,
null
FROM RA_CUSTOMER_TRX_ALL a,
ar_adjustments_all b,
RA_CUST_TRX_TYPES_ALL C ,
--AR_CUSTOMERS d,
hz_cust_accounts cust,
hz_parties party,
hz_cust_acct_sites_all hcasa,
hz_cust_site_uses_all hcsua,
(SELECT distinct gcc.CODE_COMBINATION_ID, gcc.segment4, ffvl.description,lgd.CUSTOMER_TRX_ID
FROM RA_CUSTOMER_TRX_ALL a,
ra_cust_trx_line_gl_dist_all lgd,
gl_code_combinations gcc,
fnd_flex_values_vl ffvl
WHERE a.CUSTOMER_TRX_ID=lgd.CUSTOMER_TRX_ID
and lgd.code_combination_id = gcc.code_combination_id
AND gcc.segment4 = ffvl.flex_value
AND lgd.account_class = 'REV'
-- and gcc.segment4=31111
--and a.trx_number=10130000002
) d
WHERE a.customer_trx_id=b.customer_trx_id
AND a.cust_trx_type_id = c.cust_trx_type_id
and cust.party_id = party.party_id
AND a.bill_to_customer_id =cust.CUST_ACCOUNT_ID
and cust.CUST_ACCOUNT_ID=hcasa.cust_account_id
and
hcasa.cust_acct_site_id = hcsua.cust_acct_site_id
and a.CUSTOMER_TRX_ID=d.CUSTOMER_TRX_ID
--and a.customer_trx_id <> '69735'
--AND a.sold_to_customer_id =d.customer_id
AND b.org_id=nvl(:P_ORG_ID,b.org_id)
--AND c.type in ('INV','CM','DM')
AND B. GL_DATE is not null
--AND a.trx_number =:P_TRX
AND trunc(b.gl_date) between nvl(:p_from_date,b.gl_date) AND nvl(:p_to_date,b.gl_date)
AND cust.CUST_ACCOUNT_ID =nvl(:P_FROM_CUSTOMER_ID,cust.CUST_ACCOUNT_ID)
--AND HCSUA.GL_ID_REC=:P_CODE;
order by 1,3
No comments:
Post a Comment