QueryGen Case: Create Tax Records From Invoices

This is based on IDing the original invoices by Session No. Several assumptions apply here - each case must be evaluated.

Case 1 - New System, Find all transactions missing an I record

This model was used at Farmers. Idealy this is run immediately on import. Many assumptions are made

Insert I records

Generate I records in arttaxtrx:

lq1:

select  loc.tax_locality,TAX.SEQ_NO,loc.tax_rate,
I.* from artinv I 
inner join arttxstr ts on ts.tax_authority=i.tax_authority
inner join arttxloc loc on loc.tax_locality=ts.tax_locality
left outer join arttaxtrx tax on  tax.ar_sesSion_no=i.session_no and tax.ar_trans_no=i.trans_no and TAX.trx_type='I'
WHERE TAX.SEQ_NO IS NULL AND I.INVOICE_TYPE IN ('B','I')

lq2:

select  ts.tax_authority, sum(tax_rate) auth_tax_rate from arttxloc loc
inner join arttxstr ts on loc.tax_locality=ts.tax_locality
group by tax_authority

primary:

select 
lq2.*,LQ1.TAX_LOCALITY,LQ1.INVOICE_DATE,
SALES_TAX_DUE,round(SALES_TAX_DUE*lq1.tax_rate/lq2.auth_tax_rate,2) TAX,tax_rate,
round(100*SALES_TAX_DUE/auth_tax_rate,2) TXBL,
DEBIT_AMOUNT- round(100*SALES_TAX_DUE/auth_tax_rate,2)  - SALES_TAX_DUE EXMT,DEBIT_AMOUNT,LQ1.SESSION_NO,LQ1.TRANS_NO from lq1
inner join lq2 on lq1.tax_authority=lq2.tax_authority
AND LQ1.TAX_RATE<>0

Model:

INSERT INTO ARTTAXTRX ( SEQ_NO, SESSION_NO, TAX_LOCALITY, TRX_DATE, TRX_TYPE, AR_SESSION_NO, AR_TRANS_NO, TAXABLE, TAX, EXEMPT_1 ) 
VALUES ( GEN_ID(GEN_ARTAXTRXKEY,1), {SESSION_NO}, '{TAX_LOCALITY}', '{INVOICE_DATE}', 'I', {SESSION_NO}, {TRANS_NO}, {TXBL}, {TAX}, {DEBIT_AMOUNT}-{TXBL}-{SALES_TAX_DUE} ); 

Validate

Query used by sales tax report to validate transactions:

SELECT * FROM fix_salestax_discrepancy(null, 'N')

Case 2 - Sample of system in field with known import session

This is a little more crude older case.

Insert I records

Select

select 
CAST(DEBIT_AMOUNT*.04 AS NUMERIC(15,2)) TAXNY,
I.* from artinv I
WHERE SESSION_NO =84 

Insert Model

INSERT INTO   ARTTAXTRX
( SEQ_NO, SESSION_NO, TAX_LOCALITY, TRX_DATE, TRX_TYPE, AR_SESSION_NO, AR_TRANS_NO, TAXABLE, TAX )  VALUES 
( GEN_ID(GEN_ARTAXTRXKEY,1), {SESSION_NO}, 'NY', '{INVOICE_DATE}', 'I', {SESSION_NO}, {TRANS_NO}, {DEBIT_AMOUNT}, {TAXNY}    );

INSERT INTO   ARTTAXTRX
( SEQ_NO, SESSION_NO, TAX_LOCALITY, TRX_DATE, TRX_TYPE, AR_SESSION_NO, AR_TRANS_NO, TAXABLE, TAX )  VALUES 
( GEN_ID(GEN_ARTAXTRXKEY,1), {SESSION_NO}, 'SU', '{INVOICE_DATE}', 'I', {SESSION_NO}, {TRANS_NO}, {DEBIT_AMOUNT}, {SALES_TAX_DUE}-{TAXNY}    );

insert C records

Select

select  CH.POST_DATE,
CAST(CD.CREDIT_AMOUNT*.04 AS NUMERIC(15,2)) TAXNY,
CAST(CD.CREDIT_AMOUNT*.0865 AS NUMERIC(15,2))-CAST(CD.CREDIT_AMOUNT*.04 AS NUMERIC(15,2)) TAXSU,
I.SESSION_NO,I.TRANS_NO,I.DEBIT_AMOUNT,I.SALES_TAX_DUE ,I.INVOICE_DATE, CD.SESSION_NO CASH_SESSION_NO,
CD.TRANS_NO CASH_TRANS_NO, CD.LINE_NO CASH_LINE_NO,CD.CREDIT_AMOUNT,CD.ALLOW_AMOUNT,CD.DISC_AMOUNT from artinv I
INNER JOIN ARTCSHDT CD ON CD.AR_SESSION_NO=I.SESSION_NO AND CD.AR_TRANS_NO=I.TRANS_NO
INNER JOIN ARTCSHHD CH ON CH.SESSION_NO=CD.SESSION_NO AND CH.TRANS_NO=CD.TRANS_NO
WHERE I.SESSION_NO =84
AND CD.CREDIT_AMOUNT-CD.DISC_AMOUNT<>0

Insert Model

/* {DEBIT_AMOUNT} {CREDIT_AMOUNT} {DISC_AMOUNT} */
DELETE FROM ARTTAXTRX WHERE TRX_TYPE='C' AND CASH_SESSION_NO= '{CASH_SESSION_NO}' AND CASH_TRANS_NO= '{CASH_TRANS_NO}' AND CASH_LINE_NO= '{CASH_LINE_NO}';

INSERT INTO   ARTTAXTRX
( SEQ_NO, SESSION_NO, TAX_LOCALITY, TRX_DATE, TRX_TYPE, AR_SESSION_NO, AR_TRANS_NO, TAXABLE, TAX, CASH_SESSION_NO,CASH_TRANS_NO,CASH_LINE_NO )  VALUES 
( GEN_ID(GEN_ARTAXTRXKEY,1), {CASH_SESSION_NO}, 'NY', '{POST_DATE}', 'C', {SESSION_NO}, {TRANS_NO}, -{CREDIT_AMOUNT}+{DISC_AMOUNT}, -{TAXNY}, '{CASH_SESSION_NO}','{CASH_TRANS_NO}','{CASH_LINE_NO}'  );

INSERT INTO   ARTTAXTRX
( SEQ_NO, SESSION_NO, TAX_LOCALITY, TRX_DATE, TRX_TYPE, AR_SESSION_NO, AR_TRANS_NO, TAXABLE, TAX,CASH_SESSION_NO,CASH_TRANS_NO,CASH_LINE_NO )  VALUES 
( GEN_ID(GEN_ARTAXTRXKEY,1), {CASH_SESSION_NO}, 'SU', '{POST_DATE}', 'C', {SESSION_NO}, {TRANS_NO}, -{CREDIT_AMOUNT}+{DISC_AMOUNT}, -{TAXSU},'{CASH_SESSION_NO}','{CASH_TRANS_NO}','{CASH_LINE_NO}'  );

Insert DISCOUNTS

Select

select  CH.POST_DATE,
CAST(CD.CREDIT_AMOUNT*.04 AS NUMERIC(15,2)) TAXNY,
CAST(CD.CREDIT_AMOUNT*.0865 AS NUMERIC(15,2))-CAST(CD.CREDIT_AMOUNT*.04 AS NUMERIC(15,2)) TAXSU,
I.SESSION_NO,I.TRANS_NO,I.DEBIT_AMOUNT,I.SALES_TAX_DUE ,I.INVOICE_DATE, CD.SESSION_NO CASH_SESSION_NO,
CD.TRANS_NO CASH_TRANS_NO, CD.LINE_NO CASH_LINE_NO,CD.CREDIT_AMOUNT,CD.ALLOW_AMOUNT,CD.DISC_AMOUNT from artinv I
INNER JOIN ARTCSHDT CD ON CD.AR_SESSION_NO=I.SESSION_NO AND CD.AR_TRANS_NO=I.TRANS_NO
INNER JOIN ARTCSHHD CH ON CH.SESSION_NO=CD.SESSION_NO AND CH.TRANS_NO=CD.TRANS_NO
WHERE I.SESSION_NO =84
AND CD.DISC_AMOUNT<>0

Insert Model

/* {DEBIT_AMOUNT} {CREDIT_AMOUNT} {DISC_AMOUNT} */
DELETE FROM ARTTAXTRX WHERE TRX_TYPE='D' AND CASH_SESSION_NO= '{CASH_SESSION_NO}' AND CASH_TRANS_NO= '{CASH_TRANS_NO}' AND CASH_LINE_NO= '{CASH_LINE_NO}';

INSERT INTO   ARTTAXTRX
( SEQ_NO, SESSION_NO, TAX_LOCALITY, TRX_DATE, TRX_TYPE, AR_SESSION_NO, AR_TRANS_NO, TAXABLE, TAX, CASH_SESSION_NO,CASH_TRANS_NO,CASH_LINE_NO,disc_taxable,disc_exempt,disc_tax )  VALUES 
( GEN_ID(GEN_ARTAXTRXKEY,1), {CASH_SESSION_NO}, 'NY', '{POST_DATE}', 'D', {SESSION_NO}, {TRANS_NO}, -{CREDIT_AMOUNT}+{DISC_AMOUNT}, -{TAXNY}, '{CASH_SESSION_NO}','{CASH_TRANS_NO}','{CASH_LINE_NO}'  ,-{CREDIT_AMOUNT},0,-{TAXSU}  );

INSERT INTO   ARTTAXTRX
( SEQ_NO, SESSION_NO, TAX_LOCALITY, TRX_DATE, TRX_TYPE, AR_SESSION_NO, AR_TRANS_NO, TAXABLE, TAX,CASH_SESSION_NO,CASH_TRANS_NO,CASH_LINE_NO,disc_taxable,disc_exempt,disc_tax )  VALUES 
( GEN_ID(GEN_ARTAXTRXKEY,1), {CASH_SESSION_NO}, 'SU', '{POST_DATE}', 'D', {SESSION_NO}, {TRANS_NO}, -{CREDIT_AMOUNT}+{DISC_AMOUNT}, -{TAXSU},'{CASH_SESSION_NO}','{CASH_TRANS_NO}','{CASH_LINE_NO}'
,-{CREDIT_AMOUNT},0,-{TAXSU}  );

++++NO ALLOWANCE IN THIS CASE ++++++

check for cash det <> sum credit amount (would require extra C record pre cutover)

SELECT (SELECT SUM(CREDIT_AMOUNT) FROM ARTCSHDT CD WHERE CD.AR_SESSION_NO=I.SESSION_NO AND CD.AR_TRANS_NO=I.TRANS_NO) SUMCD ,I.* FROM ARTINV I
WHERE (SELECT SUM(CREDIT_AMOUNT) FROM ARTCSHDT CD WHERE CD.AR_SESSION_NO=I.SESSION_NO AND CD.AR_TRANS_NO=I.TRANS_NO) <> CREDIT_AMOUNT