AR Salce Invoice

Sales invoice

SELECT hou.NAME operating_unit_name,
 rct.trx_number sales_invoice_number,
       ooh.order_number,
 rc.customer_name,
 rsa.NAME sales_person_name,
       msi.segment1 item_number,
 mln.lot_number,
       msi.primary_unit_of_measure primary_uom,
       ool.ordered_quantity primary_quantity,
       msi.secondary_uom_code secondary_uom,
       ool.ordered_quantity2 secondary_quantity,
       msi.organization_id inventory_org_id,
 ool.org_id,
 rctl.extended_amount amount
  FROM oe_order_headers_all ooh,
       oe_order_lines_all ool,
       hr_operating_units hou,
       ra_customer_trx_all rct,
       ra_customer_trx_lines_all rctl,
       ra_customers rc,
       ra_salesreps_all rsa,
       mtl_system_items_b msi,
       mtl_lot_numbers mln
 WHERE ooh.header_id = ool.header_id
   AND hou.organization_id = ool.org_id
   AND ool.reference_customer_trx_line_id = rctl.customer_trx_line_id
   AND rct.sold_to_customer_id = rc.customer_id
   AND ooh.salesrep_id = rsa.salesrep_id
   AND mln.inventory_item_id = msi.inventory_item_id
   AND mln.organization_id = msi.organization_id



Receipt


SELECT ACR.RECEIPT_NUMBER
       ,ARM.NAME RECEIPT_METHOD
       ,ACR.RECEIPT_DATE
       ,ACRH.GL_DATE
       ,ABB.BANK_NAME
       ,ABA.BANK_ACCOUNT_NUM
       ,HP.PARTY_NAME CUSTOMER_NAME
FROM AR_RECEIPT_METHODS ARM
    ,AR_CASH_RECEIPTS_ALL ACR
    ,AR_CASH_RECEIPT_HISTORY_ALL ACRH
    ,AP_BANK_ACCOUNTS ABA
    ,AP_BANK_BRANCHES ABB
    ,HZ_PARTIES HP
    ,HZ_CUST_ACCOUNTS HCA
WHERE ARM.RECEIPT_METHOD_ID=ACR.RECEIPT_METHOD_ID
    AND ACR.CASH_RECEIPT_ID=ACRH.CASH_RECEIPT_ID
    AND ACR.REMITTANCE_BANK_ACCOUNT_ID=ABA.BANK_ACCOUNT_ID(+)
    AND ABA.BANK_ACCOUNT_ID=ABB.BANK_BRANCH_ID(+)
    AND ACR.PAY_FROM_CUSTOMER=HCA.CUST_ACCOUNT_ID
    AND HCA.PARTY_ID=HP.PARTY_ID


 
AR INVOICE

select rcta.trx_number invoice_num,
       --sales_order_reference,
       rct.gl_date,
       rctl.inventory_item_id,
       rct.amount,
       rcta.trx_date  invoice_date,
       rctl.taxable_amount tax_amount
      -- revenue_account_id,
      --receivables_account_id,
from  ra_customer_trx_lines_all rctl,
      ra_cust_trx_line_gl_dist_all rct,
      ra_customer_trx_all rcta
where rctcl.customer_trx_line_id=rct.customer_trx_line_id
and   rctl.customer_trx_id=rcta.customer_trx_id
and   rcta.customer_trx_id=rct.customer_trx_id