Query to retrieve Transaction & Receipt details.

Query to retrieve Transaction & Receipt details.

SELECT rcta.trx_number transaction_number ,
  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

Post a Comment