Query to retrieve Transaction & Receipt details.
haou1.name operating_unit ,
rctta.name transaction_type ,
rcta.trx_date transaction_date ,
hca.account_name bill_to_customer ,
hl.address1
||' ,'
||hl.city
||' ,'
||hl.state
||' ,'
||hl.postal_code
||' ,'
||hl.country bill_to_address,
gsob.name ledger ,
rcta.interface_header_attribute1 order_number ,
rcta.interface_header_attribute2 order_type ,
rcta.interface_header_context invoice_source ,
CASE rcta.complete_flag
WHEN 'Y'
THEN 'YES'
WHEN 'N'
THEN 'NO'
END transaction_complete ,
rtt.name payment_terms ,
haou.name ship_from_organization ,
rctla.line_number ,
rctla.line_type ,
rcta.ship_via ,
msib.segment1 item ,
rctla.description item_description ,
rctla.quantity_ordered ,
rctla.quantity_invoiced ,
rctla.quantity_credited ,
rctla.unit_standard_price item_standard_price ,
rctla.unit_selling_price item_selling_price ,
fct.name currency ,
(rctla.unit_selling_price * rctla.quantity_invoiced) line_total ,
(SELECT SUM(unit_selling_price * quantity_invoiced)
FROM ra_customer_trx_lines_all
WHERE rcta.customer_trx_id=customer_trx_id
) invoice_total,
NVL(acra.receipt_number,'No receipt created for this transaction') receipt_number,
(SELECT name
FROM ar_receipt_methods
WHERE acra.receipt_method_id=receipt_method_id
) receipt_method ,
(SELECT meaning
FROM fnd_lookup_values
WHERE acra.type =lookup_code
AND lookup_type ='CASH_RECEIPT_TYPE'
) receipt_type ,
acra.receipt_date ,
acra.amount received_amount ,
(SELECT meaning
FROM fnd_lookup_values
WHERE lookup_code=araa.status
AND lookup_type ='PAYMENT_TYPE'
) receipt_status ,
acra.comments ,
hp1.party_name bank_name ,
hp.party_name branch_name ,
cba.bank_account_name ,
cba.bank_account_num
FROM ra_customer_trx_all rcta ,
hz_cust_accounts_all hca ,
hz_cust_acct_sites_all hcasa ,
hz_party_sites hps ,
hz_locations hl ,
ra_terms_tl rtt ,
gl_sets_of_books gsob ,
ra_customer_trx_lines_all rctla ,
ra_cust_trx_types_all rctta ,
fnd_currencies_tl fct ,
hr_all_organization_units haou ,
hr_all_organization_units haou1 ,
mtl_system_items_b msib ,
ar_receivable_applications_all araa ,
ar_cash_receipts_all acra ,
hz_parties hp1,
hz_parties hp ,
ce_bank_accounts cba
WHERE rcta.term_id =rtt.term_id --to get payment terms
AND rcta.invoice_currency_code =fct.currency_code--to get currency
AND fct.language ='US'
AND rcta.org_id =haou1.organization_id--to get operating unit
AND rcta.sold_to_customer_id =hca.cust_account_id --TO GET CUSTOMER DETAIL
AND hca.cust_account_id =hcasa.cust_account_id--to get account site
AND rcta.org_id =hcasa.org_id
AND hcasa.party_site_id =hps.party_site_id --to get account site
AND hps.location_id =hl.location_id --to get site location
AND rctla.inventory_item_id =msib.inventory_item_id(+)--to get item
AND rctla.org_id =msib.organization_id(+)
AND rcta.set_of_books_id =gsob.set_of_books_id --for set of books
AND to_number(rcta.interface_header_attribute10)=haou.organization_id(+)--ship from organization(warehouse)
AND rcta.cust_trx_type_id =rctta.cust_trx_type_id --transaction type
AND rcta.org_id =rctta.org_id
AND rcta.customer_trx_id =rctla.customer_trx_id --for line details
AND rcta.customer_trx_id =araa.applied_customer_trx_id(+)--for receipt application
AND rcta.trx_number = :transaction_number --'10037542'
AND araa.cash_receipt_id =acra.cash_receipt_id(+) --to get receipt details
AND acra.remit_bank_acct_use_id =cba.bank_account_id(+) --for beneficiary bank account
AND cba.bank_branch_id =hp.party_id(+) --for branch name
AND cba.bank_id =hp1.party_id(+); --for bank name
No comments:
Post a Comment