AR INVOICE QUERY

select 
'Opening Balance' "BATCH SOURCE NAME"
,rtl.invoice_currency_code "CURRENCY CODE"
,(select name from ra_cust_trx_types_all where cust_trx_type_id=rtl.cust_trx_type_id and rownum=1)customer_trx_type_name
,ac.customer_name customer_name
,ac.customer_number customer_number
,rtl.trx_number  trx_number
,rtl.trx_date   transaction_date
,rgd.Gl_date gl_date
,rgd.percent dist_percent
,(SELECT  hcs.location 
        FROM    hz_parties hp
     ,hz_cust_accounts hca
     ,hz_cust_acct_sites_all hcas
     ,hz_party_sites hps
     ,HZ_CUST_SITE_USES_ALL hcs          
   WHERE   hca.cust_account_id=hcas.cust_account_id
   AND     hcas.party_site_id=hps.party_site_id
   AND     hcs.cust_acct_site_id=hcas.cust_acct_site_id
   AND     hps.party_id = hp.party_id
   AND     hcs.site_use_code='BILL_TO'
   AND     hca.cust_account_id=rtl.bill_to_customer_id
   AND     HCS.ORG_ID  =  rtl.org_id    
   AND     HCS.STATUS='A'
   AND     hcas.status='A'
   AND     hca.status = 'A'
   AND     upper(hp.party_name) = trim(upper(ac.CUSTOMER_NAME))
      and rownum=1)location_number
,rtl.comments "INV_DESC"
,rtl.interface_header_attribute1
,(select name from ra_terms where term_id=rtl.term_id and rownum=1) "TERM_NAME"
,rct.line_number "LINE_NUMBER"
,rct.quantity_invoiced "LINE_QUANTITY"
,rct.unit_selling_price "UNIT_SELLING_PRICE"
,(rct.revenue_amount) line_amount
,(rct.extended_amount) dist_amount
,(select concatenated_segments from gl_code_combinations_kfv where code_combination_id=rgd.code_combination_id)acccount
,(select name from hr_operating_units where organization_id=rtl.org_id)"OPERATING_UNIT"
from ra_customer_trx_all rtl,ar_customers ac,RA_CUSTOMER_TRX_LINES_ALL rct,RA_CUST_TRX_LINE_GL_DIST_ALL rgd
where ac.customer_id=rtl.bill_to_customer_id
and rct.customer_trx_id = rtl.customer_trx_id
and rct.customer_trx_line_id=rgd.customer_trx_line_id
and rtl.customer_trx_id=rgd.customer_trx_id
order by rtl.org_id

AP INVOICE QUERY

select a.vendor_name "SUPPLIER NAME"
,d.vendor_site_code "SUPPLIER SITE"
,b.invoice_num "INVOICE NUMBER"
,b.invoice_date "INVOICE DATE"
,b.invoice_currency_code "CURRENCY CODE"
,b.gl_date,substr(b.description,1,50) DESCRIPTION
,b.invoice_type_lookup_code "TRANSACTION TYPE"
,(c.amount) "AMOUNT"
,(select concatenated_segments from gl_code_combinations_kfv 
where code_combination_id=c.dist_code_combination_id)ACCOUNT
,(select name from hr_operating_units where organization_id=b.org_id)OPERATING_UNIT_NAME
from ap_suppliers a,ap_supplier_sites_all d,ap_invoices_all b,ap_invoice_distributions_all c
where a.vendor_id=b.vendor_id
and c.invoice_id=b.invoice_id
and  a.vendor_id=d.vendor_id
order by b.invoice_type_lookup_code
AR RECEIPT QUERY

select 
a.name "RECEIPT_METHOD_NAME"
,acr.receipt_number
,ac.customer_name
,ac.customer_number
,acr.receipt_date
,acr.type
,(SELECT  hcs.location
      FROM    hz_parties hp
     ,hz_cust_accounts hca
     ,hz_cust_acct_sites_all hcas
     ,hz_party_sites hps
     ,HZ_CUST_SITE_USES_ALL hcs          
   WHERE   hca.cust_account_id=hcas.cust_account_id
   AND     hcas.party_site_id=hps.party_site_id
   AND     hcs.cust_acct_site_id=hcas.cust_acct_site_id
   AND     hps.party_id = hp.party_id
   AND    hcs.site_use_code='BILL_TO'
   AND    hcs.site_use_id=acr.customer_site_use_id
   AND     HCS.ORG_ID  =  acr.org_id    
   AND     HCS.STATUS='A'
   AND     hcas.status='A'
   AND     hca.status = 'A'
   AND     upper(hp.party_name) = trim(upper(ac.CUSTOMER_NAME))
      and rownum=1)location_number
,acr.amount
,(select name from hr_operating_units where organization_id=acr.org_id)"operating_unit"
,acr.currency_code "CURRENCY CODE"
,'Opening Balance' "REMITANCE_BANK_NAME" 
,cba.bank_account_num "REMITANCE_BANK_ACC_NUM"
,acr.status "RECEIPT TYPE"
from
AR_RECEIPT_METHODS a ,
AR_CASH_RECEIPTS_ALL acr,ar_customers ac,CE_BANK_ACCT_USES_all REMIT_BANK,CE_BANK_ACCOUNTS cba
where acr.pay_from_customer = ac.customer_id(+)
AND a.receipt_method_id=acr.receipt_method_id
AND REMIT_BANK.BANK_ACCT_USE_ID (+)  = acr.REMIT_BANK_ACCT_USE_ID
AND remit_bank.bank_account_id       = CBA.bank_account_id (+)
order by acr.org_id;

ON-HAND Quantities Query





























  
Receipt Query

SELECT DISTINCT rcta.trx_number invoice_no,rcta.EXCHANGE_RATE_TYPE,rcta.EXCHANGE_RATE,rcta.exchange_date,
rcta.bill_to_customer_id cust_id,rcta.customer_trx_id l_trx_id,
rcta.trx_date receipt_date, apsa.due_date,
apsa.amount_due_remaining receipt_amount
FROM ra_customer_trx_all rcta,
ar_payment_schedules_all apsa,
ra_batch_sources_all rba
WHERE rcta.batch_source_id = rba.batch_source_id
AND rcta.customer_trx_id = apsa.customer_trx_id
---AND rcta.orig_system_batch_name = 'TABS'
AND apsa.amount_due_remaining > 0
AND UPPER (apsa.status) = 'OP'
AND rcta.INVOICE_CURRENCY_CODE = 'MA'
--AND UPPER (apsa.CLASS) = 'INV'
--AND rcta.org_id = 101         
ORDER BY rcta.bill_to_customer_id,
rcta.bill_to_customer_id,
rcta.trx_date,
apsa.due_date,
rcta.trx_number;

PO Matched Invoice Query

SELECT POV.VENDOR_NAME
,      AIA.INVOICE_TYPE_LOOKUP_CODE AS INVOICE_TYPE
,      AIA.INVOICE_NUM
,      AIA.INVOICE_DATE
,      AID.ACCOUNTING_DATE AS GL_DATE
,      AIA.INVOICE_CURRENCY_CODE
,      PHA.SEGMENT1  AS PO_NUMBER
,      RSH.RECEIPT_NUM
,      NVL(AIA.EXCHANGE_RATE,1) AS EXCHANGE_RATE
,      GCC.CONCATENATED_SEGMENTS
,       AID.AMOUNT          AS ENTERED_AMOUNT
,       AID.BASE_AMOUNT     AS ACCOUNTED_AMOUNT
FROM AP_INVOICE_DISTRIBUTIONS_ALL   AID
,    AP_INVOICES_ALL                AIA
,    PO_VENDORS                     POV
,    GL_CODE_COMBINATIONS_KFV       GCC
,    PO_DISTRIBUTIONS_ALL           PDA
,    PO_HEADERS_ALL                 PHA
,    (SELECT TRANSACTION_ID, SHIPMENT_HEADER_ID
      FROM RCV_TRANSACTIONS WHERE TRANSACTION_TYPE = 'RECEIVE') RCV
,    RCV_SHIPMENT_HEADERS          RSH
WHERE
AID.PO_DISTRIBUTION_ID IS NOT NULL
AND   AID.BASE_AMOUNT IS NOT NULL
AND AID.INVOICE_ID = AIA.INVOICE_ID
AND   AIA.VENDOR_ID = POV.VENDOR_ID
AND   AID.DIST_CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND   TRUNC(AID.ACCOUNTING_DATE) BETWEEN '01-APR-2011' AND '31-MAR-2013'
AND   AID.PO_DISTRIBUTION_ID = PDA.PO_DISTRIBUTION_ID
AND   PDA.PO_HEADER_ID  = PHA.PO_HEADER_ID
AND   AID.RCV_TRANSACTION_ID = RCV.TRANSACTION_ID (+)
AND   RCV.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID (+)
UNION ALL
SELECT POV.VENDOR_NAME
,      AIA.INVOICE_TYPE_LOOKUP_CODE AS INVOICE_TYPE
,      AIA.INVOICE_NUM
,      AIA.INVOICE_DATE
,      AID.ACCOUNTING_DATE AS GL_DATE
,      AIA.INVOICE_CURRENCY_CODE
,      PHA.SEGMENT1  AS PO_NUMBER
,      RSH.RECEIPT_NUM
,      NVL(AIA.EXCHANGE_RATE,1) AS EXCHANGE_RATE
,      GCC.CONCATENATED_SEGMENTS
,       AID.AMOUNT          AS ENTERED_AMOUNT
,       AID.AMOUNT          AS ACCOUNTED_AMOUNT
FROM AP_INVOICE_DISTRIBUTIONS_ALL   AID
,    AP_INVOICES_ALL                AIA
,    PO_VENDORS                     POV
,    GL_CODE_COMBINATIONS_KFV       GCC
,    PO_DISTRIBUTIONS_ALL           PDA
,    PO_HEADERS_ALL                 PHA
,    (SELECT TRANSACTION_ID, SHIPMENT_HEADER_ID
      FROM RCV_TRANSACTIONS WHERE TRANSACTION_TYPE = 'RECEIVE') RCV
,    RCV_SHIPMENT_HEADERS           RSH
WHERE
AID.PO_DISTRIBUTION_ID IS NOT NULL
AND   AID.BASE_AMOUNT IS NULL
AND   AID.INVOICE_ID = AIA.INVOICE_ID
AND   AIA.VENDOR_ID = POV.VENDOR_ID
AND   AID.DIST_CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND   TRUNC(AID.ACCOUNTING_DATE) BETWEEN '01-APR-2011' AND '31-MAR-2013'
AND   AID.PO_DISTRIBUTION_ID = PDA.PO_DISTRIBUTION_ID
AND   PDA.PO_HEADER_ID  = PHA.PO_HEADER_ID
AND   AID.RCV_TRANSACTION_ID = RCV.TRANSACTION_ID (+)
AND   RCV.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID (+)
ORDER BY INVOICE_NUM, GL_DATE