This 1099 Supplier Invoices Missing Income Tax Type query identifies distribution lines of invoices related to 1099 reportable suppliers where the income tax type (type_1099) is missing. It helps ensure compliance with tax reporting by highlighting invoices from suppliers marked as 1099-reportable but without the necessary tax classification on their distribution lines.
#sqlquery
SELECT
-- SQL4Fusion (An Orbit Analytics Project)
-- This query identifies distribution lines of invoices related to 1099 reportable suppliers where the income tax type (type_1099) is missing
PSV.VENDOR_NAME SUPPLIER_NAME,
PSV.SEGMENT1 SUPPLIER_NUMBER,
AIA.INVOICE_NUM INVOICE_NUMBER,
AIA.INVOICE_DATE,
NVL (AIA.INVOICE_AMOUNT, 0) INVOICE_AMOUNT,
AIDA.DISTRIBUTION_LINE_NUMBER,
NVL (AIDA.AMOUNT, 0) DISTRIBUTION_AMOUNT,
AIPA.ACCOUNTING_DATE,
AIDA.TYPE_1099,
AIA.INVOICE_ID,
ARELA.TAX_ENTITY_ID,
PSV.VENDOR_ID SUPPLIER_ID,
AIA.ORG_ID
FROM
AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
AP_INVOICES_ALL AIA,
POZ_SUPPLIERS_V PSV,
GL_CODE_COMBINATIONS GCC,
AP_REPORTING_ENTITY_LINES_ALL ARELA,
AP_INVOICE_PAYMENTS_ALL AIPA,
AP_CHECKS_ALL ACA,
CE_BANK_ACCOUNTS CBA,
CE_BANK_ACCT_USES_ALL CBAU,
HR_ALL_ORGANIZATION_UNITS HAOU
WHERE
1 = 1
AND AIDA.INVOICE_ID = AIA.INVOICE_ID
AND NVL (AIDA.REVERSAL_FLAG, 'N') <> 'Y'
AND AIA.VENDOR_ID = PSV.VENDOR_ID
AND PSV.FEDERAL_REPORTABLE_FLAG = 'Y'
AND AIDA.TYPE_1099 IS NULL
AND AIPA.CHECK_ID = ACA.CHECK_ID
AND ACA.CE_BANK_ACCT_USE_ID = CBAU.BANK_ACCT_USE_ID
AND CBAU.BANK_ACCOUNT_ID = CBA.BANK_ACCOUNT_ID
AND AIDA.DIST_CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID
AND AIA.ORG_ID=ARELA.ORG_ID(+)
AND AIPA.INVOICE_ID = AIA.INVOICE_ID
AND AIA.ORG_ID = HAOU.ORGANIZATION_ID
GROUP BY
PSV.VENDOR_NAME,
PSV.SEGMENT1,
AIA.INVOICE_NUM,
AIA.INVOICE_DATE,
AIA.INVOICE_AMOUNT,
AIDA.DISTRIBUTION_LINE_NUMBER,
AIDA.AMOUNT,
AIPA.ACCOUNTING_DATE,
AIDA.TYPE_1099,
AIA.INVOICE_ID,
ARELA.TAX_ENTITY_ID,
PSV.VENDOR_ID,
AIA.ORG_ID;