top of page

Tags

Archive

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 !

426 views0 comments

Recent Posts

See All

When transactions are entered, Dynamic Insertion in Oracle Fusion General Ledger (GL) allows an organisation to automatically insert extra segments or values in the GL chart of accounts. This feature

Oracle Fusion Segment Security The General Ledger (GL) feature enables an organisation to restrict access to data in the GL depending on certain parts of the GL chart of accounts. A segment is a subse

Oracle Fusion General Ledger (GL) Cross-Validation Rules are used to assure the integrity and accuracy of data entered into the GL module. They are used to ensure that data submitted in the GL is cons

Other Posts you may Interested