A/P Balancing Queries

A/P Raw Balance

select sum(gross_amount-paid_amount-disc_taken) ap_apsys,
-(select sum(trx_amount) from glttrx  where gl_account=:ap_account) ap_glsys,
sum(gross_amount-paid_amount-disc_taken)+(select sum(trx_amount) from glttrx  where gl_account=:ap_account) difference
 from aptinvhd

A/P Transactions not from A/P system

select session_no, gl_account, trx_date, trx_amount, last_change_datetime, description_1, source_app, source_table from glttrx 
where gl_account=:ap_account and source_app<>'P' 
  and imported_flag='N'  and trx_date>=:begdate order by trx_date desc

Irregular Use of A/P account in A/P system

select session_no,gl_account,trx_date,trx_amount,last_change_datetime,description_1,
source_app,source_table,source_TRANS_TYPE  from glttrx
where source_app='P' and source_trans_type not in ('IP','CP','HP','VP')
and gl_account=:ap_account

A/P Crossover Query

Select 'A',session_no,g.trans_no gl_trans,i.trans_no ap_inv_trans,vend_id,invoice_no,invoice_date,g.trx_date gldate,i.gross_amount,
(select user_id from cctsessn cs where cs.session_no=i.session_no) chg_user,
g.last_change_datetime enter_date
 from aptinvhd i
inner join  glttrx g on
 (g.source_session_no=i.session_no and g.source_trans_no=i.trans_no
  and g.gl_account=:ap_account and g.trx_date<=:crossdate)
where i.invoice_date>:crossdate
union
Select 'B',session_no,g.trans_no gl_trans,i.trans_no ap_inv_trans,vend_id,invoice_no,invoice_date,g.trx_date gldate,i.gross_amount,
(select user_id from cctsessn cs where cs.session_no=i.session_no) chg_user,
g.last_change_datetime enter_date
 from aptinvhd i
inner join  glttrx g on
 (g.source_session_no=i.session_no and g.source_trans_no=i.trans_no
  and g.gl_account=:ap_account and g.trx_date>:crossdate)
where i.invoice_date<=:crossdate

Invoices Paid before Invoice Date

select c.trx_date,AI.INVOICE_DATE AP_HD_INV_DATE,ac.* from aptchkdt ac
inner join cmttrx c on c.session_no=ac.session_no and c.trans_no=ac.trans_no
INNER JOIN APTINVHD AI ON AC.AP_SESSION_NO=AI.SESSION_NO AND AC.AP_TRANS_NO=AI.TRANS_NO
where (ac.invoice_date>:Crossover_Date) and (c.trx_date<=:Crossover_Date)

Invoice Gross Not Equal G/L Detail "IP" transactions

select  (select sum (trx_amount) from glttrx tx where tx.source_session_no=ih.session_no and tx.source_trans_no=ih.trans_no and source_trans_type='IP') "GL Amount",
IH.* from aptinvhd ih
where invoice_date > :"Begin Date"
and gross_amount+
(select sum (trx_amount) from glttrx tx where tx.source_session_no=ih.session_no and tx.source_trans_no=ih.trans_no and source_trans_type='IP') <>0

Generic G/L Payments = Invoice Paid (W.I.P. Work needed)

select 
TX.GL_ACCOUNT,TX.TRX_AMOUNT,
cd.*,ih.paid_amount,ih.*
 from aptinvhd ih
inner join aptchkdt cd on cd.ap_session_no=ih.session_no and cd.ap_trans_no=ih.trans_no
inner join glttrx tx on tx.source_session_no=cd.session_no and tx.source_trans_no=cd.trans_no and tx.source_line_no=cd.line_no and source_trans_type='CP'
where ih.invoice_date > :"Begin Date"
AND PAID_THIS_CHECK<>PAID_AMOUNT