Home » Applications » Oracle Fusion Apps & E-Business Suite » Query for all open items including billed location, contact and amount outstanding at line level (Receivables R12)
 Query for all open items including billed location, contact and amount outstanding at line level [message #554716] |
Wed, 16 May 2012 16:52 |
 |
EBSreader
Messages: 1
Registered: May 2012
Location: UK
|
Junior Member |
|
|
Guys is anyone able to help
I need a query that can give me a listing of all open items, the site that the open item is for and will be paid by, the contact who may pay it from the site , the amount outstanding at each line, the discounted amount available. I've googled and looked on metalink and can't find anything that is relevant, and a query like the below gives me multiple records. I've looked on the ETRMs and ERDs to try and find relationships etc Does anyone have a query that works and can give me this information, the one below I think is getting there but returns too many rows so happy to start again if I have to or replace it with something that is tried and true?
Many thanks
My query:
select distinct
aps.customer_id customer
, aps.customer_trx_id TransactionReference
, aps.TRX_DATE TransactionDate
, 1 ContactName -- where do I find contact?
, 1 TelephoneNumber -- where do i find the telephone number?
, 1 CostCentre
, decode(aps.amount_in_dispute,NULL,'O',0,'O','D') disputed_or_not
, NVL(TRL.AMOUNT_DUE_REMAINING, TRL.EXTENDED_AMOUNT) AmountOutstanding -- amount outstanding at line leve is this correct?l
, DECODE(APS.CLASS, 'INV', 'INV ', 'DM', 'DBM ', 'CM', 'CRN ', 'PMT', 'PMT ','GUAR','GUR', APS.CLASS) StatementTransactionType
, HZ_PARTIES.PARTY_NAME CustomerName
, HZ_LOCATIONS.ADDRESS1 AddressLine1
, HZ_LOCATIONS.ADDRESS2 AddressLine2
, HZ_LOCATIONS.CITY AddressLine3
, HZ_LOCATIONS.COUNTY AddressLine4
, HZ_LOCATIONS.POSTAL_CODE Postcode
,HZ_CUST_ACCOUNTS.ATTRIBUTE5 CustomerAccountCategory
--TRANSACTION INFO
, aps.DUE_DATE InvoiceDueDate
, NVL(TRL.AMOUNT_DUE_REMAINING, TRL.EXTENDED_AMOUNT) DiscountedAmountAvailable --- AR calculates discount on payment not up front i think but is it held somewhere?
, tra.purchase_order DocumentNumber
, 1 MaxRevenueDifferenceValue -- are these tolerances held anywhere?
, 1 MaxExpenseDifferenceValue -- are these tolerances held anywhere?
, 1 MaxRevenueDifferencePercentage -- are these tolerances held anywhere?
, 1 MaxExpenseDifferencePercentage -- are these tolerances held anywhere?
, 1 MaxGainFromDiscount -- are these tolerances held anywhere?
, 1 MaxLossFromDiscount -- are these tolerances held anywhere?
FROM
ar_payment_schedules_all aps
,ra_customer_trx_all Tra
,ra_customer_trx_lines_all trl
,HZ_CUST_ACCT_SITES_ALL
,HZ_CUST_SITE_USES_ALL
,HZ_PARTY_SITES
,HZ_LOCATIONS
,HZ_PARTIES
,HZ_CUST_ACCOUNTS
WHERE 1 = 1
--finding transaction details
AND aps.customer_trx_id = tra.customer_trx_id(+)
AND aps.customer_trx_id =trl.customer_trx_id
AND aps.amount_due_remaining <> 0 -- amount due is not zero
AND aps.status = 'OP' -- open item
AND tra.org_id = aps.org_id
AND tra.complete_flag = 'Y'
AND trl.line_type IN ('FREIGHT', 'LINE') -- include freight and line transactions
--linking transactioan to an address
AND tra.bill_to_customer_id = HZ_CUST_ACCT_SITES_ALL.cust_account_id
AND tra.bill_to_SITE_USE_id = HZ_CUST_SITE_USES_ALL.bill_to_SITE_USE_id
-- finding addresses for transactions
AND (HZ_PARTY_SITES.PARTY_SITE_ID = HZ_CUST_ACCT_SITES_ALL.CUST_ACCT_SITE_ID)
AND (HZ_PARTY_SITES.LOCATION_ID = HZ_LOCATIONS.LOCATION_ID)
AND (HZ_PARTIES.PARTY_ID = HZ_PARTY_SITES.PARTY_ID)
AND (HZ_CUST_ACCT_SITES_ALL.CUST_ACCOUNT_ID = HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID)
AND (HZ_PARTIES.PARTY_ID = HZ_CUST_ACCOUNTS.PARTY_ID)
AND HZ_CUST_ACCT_SITES_ALL.STATUS = 'A'
--AND TRA.TRX_NUMBER = '10084'
|
|
|
|
Goto Forum:
Current Time: Wed May 21 08:19:33 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
