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: Thu Nov 21 13:28:00 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
