1099 Custom Report Query -Oracle R12 AP
The below Query is extremely helpful for the 1099 reporting. The out of box report has been customized to bring additional columns . Hope this helps.
/*Created by Sandeep Satyavolu */
SELECT SUPPLIER_NUMBER,
--VENDOR_ID,
SUPPLIER_TAX_ID,
VENDOR_NAME,
INCOME_TAX_TYPE,
PAYMENT_TOTAL,
(SELECT b.vendor_site_code
FROM ap_supplier_sites_all b
WHERE b.vendor_id = t.vendor_id
AND b.TAX_REPORTING_SITE_FLAG = 'Y'
AND ROWNUM = 1)
TAX_PAYMENT_SITE,
(SELECT b.address_line1
FROM ap_supplier_sites_all b
WHERE b.vendor_id = t.vendor_id
AND b.TAX_REPORTING_SITE_FLAG = 'Y'
AND ROWNUM = 1)
ADDRESS_LINE1,
(SELECT b.address_line2
FROM ap_supplier_sites_all b
WHERE b.vendor_id = t.vendor_id
AND b.TAX_REPORTING_SITE_FLAG = 'Y'
AND ROWNUM = 1)
ADDRESS_LINE2,
(SELECT b.city
FROM ap_supplier_sites_all b
WHERE b.vendor_id = t.vendor_id
AND b.TAX_REPORTING_SITE_FLAG = 'Y'
AND ROWNUM = 1)
CITY,
(SELECT b.zip
FROM ap_supplier_sites_all b
WHERE b.vendor_id = t.vendor_id
AND b.TAX_REPORTING_SITE_FLAG = 'Y'
AND ROWNUM = 1)
ZIP,
(SELECT b.state
FROM ap_supplier_sites_all b
WHERE b.vendor_id = t.vendor_id
AND b.TAX_REPORTING_SITE_FLAG = 'Y'
AND ROWNUM = 1)
STATE,
:P_FLAG NON_EMP_EXP_FLAG
FROM ( SELECT SUPPLIER_NUMBER,
VENDOR_ID,
SUPPLIER_TAX_ID,
VENDOR_NAME,
INCOME_TAX_TYPE,
SUM (PAYMENT_TOTAL) PAYMENT_TOTAL
FROM ( SELECT ABC.SUPPLIER_NUMBER,
ABC.SUPPLIER_TAX_ID,
ABC.VENDOR_NAME,
ABC.VENDOR_ID,
ABC.INCOME_TAX_TYPE,
ABC.VENDOR_SITE_CODE,
ABC.ADDRESS_LINE1,
ABC.ADDRESS_LINE2,
ABC.CITY,
ABC.STATE,
ABC.ZIP,
ABC.NON_EMP_EXP_FLAG,
ABC.VENDOR_SITE_ID,
ROUND (
SUM (
DECODE (
ABC.AIA_CANCELLED_AMOUNT,
NULL, ABC.ID_AMOUNT,
DECODE (
GREATEST (ABC.ID_ACCOUNTING_DATE,
( :P_END_DATE) + 1),
ABC.ID_ACCOUNTING_DATE, 0,
DECODE (
LEAST (ABC.ID_ACCOUNTING_DATE,
( :P_START_DATE) - 1),
ABC.ID_ACCOUNTING_DATE, 0,
ABC.ID_AMOUNT)))
/ DECODE (
ABC.INVOICE_AMOUNT,
0, DECODE (ABC.AIA_CANCELLED_AMOUNT,
NULL, 1,
0, 1,
ABC.AIA_CANCELLED_AMOUNT),
DECODE (
AP_UTILITIES_PKG.NET_INVOICE_AMOUNT (
ABC.AIA_INVOICE_ID),
0, 1,
AP_UTILITIES_PKG.NET_INVOICE_AMOUNT (
ABC.AIA_INVOICE_ID)))
* DECODE (ABC.IP_AMOUNT,
0, DECODE (ABC.IP_COUNT, NULL, 1, 0),
ABC.IP_AMOUNT)),
2)
PAYMENT_TOTAL
FROM (SELECT /*+ LEADING(ip aia) USE_NL(ip) */
SEGMENT1 SUPPLIER_NUMBER,
DECODE (
ASU.NUM_1099,
NULL, DECODE (
ASU.TCA_SYNC_NUM_1099,
NULL, DECODE (ASU.INDIVIDUAL_1099,
NULL, NULL,
ASU.INDIVIDUAL_1099),
ASU.TCA_SYNC_NUM_1099),
ASU.NUM_1099)
SUPPLIER_TAX_ID,
asu.vendor_id,
ASU.VENDOR_NAME,
ASU.TYPE_1099 INCOME_TAX_TYPE,
ASSA.VENDOR_SITE_CODE,
ASSA.ADDRESS_LINE1,
ASSA.ADDRESS_LINE2,
ASSA.CITY,
ASSA.STATE,
ASSA.ZIP,
AIA.ATTRIBUTE5 NON_EMP_EXP_FLAG,
ASSA.VENDOR_SITE_ID,
ID.AMOUNT ID_AMOUNT,
ID.ACCOUNTING_DATE ID_ACCOUNTING_DATE,
AIA.INVOICE_AMOUNT,
AIA.CANCELLED_AMOUNT AIA_CANCELLED_AMOUNT,
IP2.IP_COUNT,
IP.AMOUNT IP_AMOUNT,
AIA.INVOICE_ID AIA_INVOICE_ID
FROM AP_INVOICES_ALL AIA,
AP_SUPPLIERS ASU,
AP_SUPPLIER_SITES_ALL ASSA,
AP_INVOICE_DISTRIBUTIONS_ALL ID,
AP_INVOICE_PAYMENTS_ALL IP,
AP_CHECKS_ALL ACA,
( SELECT INVOICE_ID, COUNT (*) IP_COUNT
FROM AP_INVOICE_PAYMENTS_ALL AIP
WHERE AIP.AMOUNT <> 0
AND AIP.ACCOUNTING_DATE BETWEEN ( :P_START_DATE)
AND ( :P_END_DATE)
GROUP BY INVOICE_ID) IP2
WHERE 1 = 1
AND AIA.VENDOR_ID = ASU.VENDOR_ID
AND AIA.VENDOR_SITE_ID = ASSA.VENDOR_SITE_ID
AND ASU.VENDOR_ID = ASSA.VENDOR_ID
AND IP.INVOICE_ID = AIA.INVOICE_ID
AND IP.INVOICE_ID = IP2.INVOICE_ID
AND NVL (AIA.ATTRIBUTE5, 'N') = :P_FLAG
AND ASU.VENDOR_TYPE_LOOKUP_CODE NOT IN ('EMPLOYEE')
AND AIA.INVOICE_TYPE_LOOKUP_CODE NOT IN ('EXPENSE REPORT')
AND AIA.SOURCE NOT IN ('CONCUR')
AND ID.INVOICE_ID = AIA.INVOICE_ID
AND IP.ACCOUNTING_DATE BETWEEN ( :P_START_DATE)
AND ( :P_END_DATE)
AND AIA.ORG_ID = :P_ORG
AND AIA.ORG_ID = ID.ORG_ID
AND AIA.ORG_ID = ASSA.ORG_ID
AND AIA.ORG_ID = IP.ORG_ID
AND ID.type_1099 <> 'MISC4'
AND ID.type_1099 IS NOT NULL
AND ( ACA.void_date IS NULL
OR ACA.void_date NOT BETWEEN :p_start_date
AND :p_end_date)
AND ACA.check_id = IP.check_id
AND ACA.ORG_ID = IP.ORG_ID
AND ACA.ORG_ID = AIA.ORG_ID) ABC
GROUP BY ABC.SUPPLIER_NUMBER,
ABC.SUPPLIER_TAX_ID,
ABC.VENDOR_NAME,
ABC.INCOME_TAX_TYPE,
ABC.VENDOR_SITE_CODE,
ABC.ADDRESS_LINE1,
ABC.ADDRESS_LINE2,
ABC.CITY,
ABC.STATE,
ABC.ZIP,
ABC.NON_EMP_EXP_FLAG,
ABC.VENDOR_SITE_ID,
ABC.VENDOR_ID)
GROUP BY SUPPLIER_NUMBER,
SUPPLIER_TAX_ID,
VENDOR_NAME,
VENDOR_ID,
INCOME_TAX_TYPE) T
Happy Learning !
