A/P Balancing Queries - Revision history

Chanson: /* Irregular Use of A/P account in A/P system */

2025-12-29T19:16:34Z

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

← Older revision		Revision as of 19:16, 29 December 2025
Line 15:		Line 15:

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

			===Non-AP Tranasctions from AP Account===
	<PRE>		<PRE>
	~~===Non-AP Tranasctions from AP Account===~~
	select session_no,trans_no,gl_account,trx_date,trx_amount,last_change_datetime,description_1,		select session_no,trans_no,gl_account,trx_date,trx_amount,last_change_datetime,description_1,
	source_app,source_table,source_TRANS_TYPE from glttrx		source_app,source_table,source_TRANS_TYPE from glttrx

Chanson: /* Irregular Use of A/P account in A/P system */

2025-12-29T18:52:24Z

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

← Older revision		Revision as of 18:52, 29 December 2025
Line 16:		Line 16:
	==Irregular Use of A/P account in A/P system==		==Irregular Use of A/P account in A/P system==
	<PRE>		<PRE>
			===Non-AP Tranasctions from AP Account===
	select session_no,trans_no,gl_account,trx_date,trx_amount,last_change_datetime,description_1,		select session_no,trans_no,gl_account,trx_date,trx_amount,last_change_datetime,description_1,
	source_app,source_table,source_TRANS_TYPE from glttrx		source_app,source_table,source_TRANS_TYPE from glttrx
Line 21:		Line 22:
	and gl_account=:ap_account		and gl_account=:ap_account
	</PRE>		</PRE>
			Updated 12/29/2025 to add HX and HY to exclude list.

	~~Updated~~ 12/29/2025 ~~to add HX and HY to exclude list.~~		===AP Tranasctions from Non-AP Account===
			<PRE>
			select session_no,trans_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 in ('IP','CP','HP','VP','HX','HY')
			and gl_account<>:ap_account
			</PRE>
			12/29/2025

	==A/P Crossover Query==		==A/P Crossover Query==

Chanson: /* Irregular Use of A/P account in A/P system */

2025-12-29T17:17:34Z

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

← Older revision		Revision as of 17:17, 29 December 2025
Line 16:		Line 16:
	==Irregular Use of A/P account in A/P system==		==Irregular Use of A/P account in A/P system==
	<PRE>		<PRE>
	select session_no,gl_account,trx_date,trx_amount,last_change_datetime,description_1,		select session_no,trans_no,gl_account,trx_date,trx_amount,last_change_datetime,description_1,
	source_app,source_table,source_TRANS_TYPE from glttrx		source_app,source_table,source_TRANS_TYPE from glttrx
	where source_app='P' and source_trans_type not in ('IP','CP','HP','VP')		where source_app='P' and source_trans_type not in ('IP','CP','HP','VP','HX','HY')
	and gl_account=:ap_account		and gl_account=:ap_account
	</PRE>		</PRE>

			Updated 12/29/2025 to add HX and HY to exclude list.

	==A/P Crossover Query==		==A/P Crossover Query==

WikiAdmin: 1 revision imported

2023-07-14T12:58:47Z

1 revision imported

← Older revision	Revision as of 12:58, 14 July 2023
(No difference)

Intra>User: 1 revision imported

2017-01-10T00:01:22Z

1 revision imported

New page

==A/P Raw Balance==
<PRE>
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
</PRE>

==A/P Transactions not from A/P system==
<PRE>
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
</PRE>

==Irregular Use of A/P account in A/P system==
<PRE>
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
</PRE>

==A/P Crossover Query==
<PRE>
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
</PRE>

==Invoices Paid before Invoice Date ==
<PRE>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)
</PRE>

==Invoice Gross Not Equal G/L Detail "IP" transactions==
<PRE>
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
</PRE>

==Generic G/L Payments = Invoice Paid (W.I.P. Work needed)==
<PRE>
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
</pre>