IRS Audit T-SQL

Remote log into 'Hagerco-sql-19' server and open SQL server management studio and paste the below T-SQL code and click on execute. Copy the results with Headers and paste it in the excel and send it to the requested users.

Use GloviaProd

Go

SELECT DISTINCT     

    a.dt_ AS INVOICE_DATE,

     A.AR_DOC AS INVOICE_NO,

     a.AR_DOC_TYPE,

    f.SO AS SALES_ORDER_NO,    

    H.NAME AS CUSTOMER_NAME,

    B.PARTS_AMT+B.ADDL_CHG_AMT-B.DISC_AMT AS INVOICE_AMOUNT,

    B.PARTS_AMT-B.DISC_AMT AS PARTS_AMOUNT ,

    B.ADDL_CHG_AMT,

    a.CM_APPLIED_AMT AS CREDIT_ISSUED

    ---A.AMt-A.DISC_AMT-A.WRITE_OFF_AMT-A.PAID_AMT-A.CM_APPLIED_AMT AS AMOUNT_DUE

FROM     

    [GLOVIA_PROD].AR_DOC a     

LEFT JOIN     

    [GLOVIA_PROD].[BILL_HDR] b ON a.AR_CCN = b.AR_CCN     

    AND A.AR_DOC_TYPE = b.AR_DOC_TYPE     

    AND A.AR_DOC = b.AR_DOC    

LEFT JOIN     

    [GLOVIA_PROD].so_ship f ON A.AR_CCN = f.SALES_CCN     

    AND A.AR_DOC = f.AR_DOC     

    AND a.AR_DOC_TYPE = f.AR_DOC_TYPE    

/*INNER JOIN     

    [GLOVIA_PROD].so_hdr c ON a.AR_CCN = c.SALES_CCN     

    AND a.CUSTOMER = c.CUSTOMER     

    AND a.CUS_AR_LOC = c.CUS_AR_LOC     

    AND c.SO = f.SO */    

left JOIN[GLOVIA_PROD].[CUS_LOC] h ON A.CUSTOMER = H.CUSTOMER AND a.CUS_AR_LOC = H.CUS_LOC    

WHERE   a.AR_ccn = 'HG0101' and format(a.dt_,'yyyy') = '2022'

order by a.AR_DOC

Update the query with the requested dates and send the data via a Power BI report or an Excel file, depending on the size of the dataset. You can organize it by month or year as needed. 

(Server     Hagerco-sql-19)

BY YEAR 

SELECT 

    CONCAT(c.CUSTOMER, ' ', c.cus_loc) AS CUSTOMER_NO,

    c.name AS CUSTOMER_NAME,

    a.*

FROM 

    glovia_prod.FIM_BL a

LEFT JOIN 

    glovia_prod.ar_doc b ON a.fim_ccn = b.AR_CCN 

                         AND a.ar_doc = b.ar_doc 

                         AND a.AR_DOC_TYPE = b.AR_DOC_TYPE

LEFT JOIN 

    glovia_prod.cus_loc c ON b.CUSTOMER = c.customer 

                         AND b.cus_ar_loc = c.cus_loc

WHERE 

    a.FIM_CCN = 'HG0101'

    AND a.BL_FIN_ASOF_DATE BETWEEN '2022-01-01' AND '2022-12-31';

BY MONTH

SELECT CONCAT(c.CUSTOMER, ' ', c.cus_loc) AS CUSTOMER_NO,

       c.name AS CUSTOMER_NAME,

       a.*

FROM glovia_prod.FIM_BL a

LEFT JOIN glovia_prod.ar_doc b ON a.fim_ccn = b.AR_CCN

                               AND a.ar_doc = b.ar_doc

                               AND a.AR_DOC_TYPE = b.AR_DOC_TYPE

LEFT JOIN glovia_prod.cus_loc c ON b.CUSTOMER = c.customer

                               AND b.cus_ar_loc = c.cus_loc

WHERE a.FIM_CCN = 'HG0101'

  AND a.BL_FIN_ASOF_DATE BETWEEN '2022-02-01' AND '2022-02-28';