FSQL 12

SQL QUERY FOR GST IN ORACLE CLOUD FUSION R13

SELECT C.INVOICE_ID,

       DENSE_RANK()

         OVER(

           ORDER BY C.INVOICE_ID)                              AS S_NO,

       D.SEGMENT1                                              VENDOR_NUMBER,

       HP.PARTY_NAME                                           VENDOR_NAME,

       PSS.PARTY_SITE_NAME                                     SITE_CODE,

       ZR.REGISTRATION_NUMBER

       GST_REGISTRATION_NUMBER,

       PSS.ADDRESS_LINE1

       || ','

       || PSS.ADDRESS_LINE2

       || ','

       || PSS.ADDRESS_LINE3

       || ','

       || PSS.CITY

       || ','

       || PSS.STATE

       || ','

       || PSS.ZIP                                              ADDRESS,

       PSS.STATE                                               STATE_NAME,

       TO_CHAR(C.GL_DATE, 'DD-MON-YYYY')                       GL_DATE,

       TO_CHAR(C.INVOICE_DATE, 'DD-MON-YYYY')                  INVOICE_DATE,

       TO_CHAR(C.CREATION_DATE, 'DD-MON-YYYY')                 CREATION_DATE,

       C.DOC_SEQUENCE_VALUE,

       C.INVOICE_NUM                                           INVOICE_NUMBER,

       C.DESCRIPTION,

       C.INVOICE_AMOUNT,

       (SELECT SUM(A.LINE_AMT)

        FROM   ZX_LINES_V A

        WHERE  A.TRX_ID = B.INVOICE_ID

               AND TAX_LINE_NUMBER = '1')

       AMOUNT_SUBJECT_TO_GST,

       (SELECT SUM(A.TAX_AMT)

        FROM   ZX_LINES_V A

        WHERE  A.TRX_ID = C.INVOICE_ID

               AND A.TAX_RATE > '0')                           GST_AMOUNT,

       (SELECT SUM(A.NREC_TAX_AMT)

        FROM   ZX_LINES_V A

        WHERE  A.TRX_ID = C.INVOICE_ID

               AND A.TAX_RATE > '0')

       GST_AMOUNT_NOT_REC,

       B.LINE_NUMBER,

       B.AMOUNT,

       (SELECT SEGMENT2

        FROM   GL_CODE_COMBINATIONS

        WHERE  CODE_COMBINATION_ID = B.DEFAULT_DIST_CCID)      GL_ACCOUNT,

       (SELECT GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (CHART_OF_ACCOUNTS_ID, 2,

               SEGMENT2

               )

        FROM   GL_CODE_COMBINATIONS GCCL

        WHERE  GCCL.CODE_COMBINATION_ID = B.DEFAULT_DIST_CCID) GL_ACCOUNT_DESC,

       B.DESCRIPTION                                           AS

       LINE_DESCRIPTION,

       (SELECT LOCATION_NAME

        FROM   HR_LOCATIONS

        WHERE  LOCATION_ID = B.SHIP_TO_LOCATION_ID)            SHIP_TO_LOCATION,

       B.USER_DEFINED_FISC_CLASS                               AS HSN_SAC_CODE,

       B.PRODUCT_CATEGORY                                      AS TAX_RATE_CAT,

       (SELECT SUM(A.TAX_RATE)

        FROM   ZX_LINES_V A

        WHERE  A.TRX_ID = B.INVOICE_ID

               AND A.TRX_LINE_NUMBER = B.LINE_NUMBER

               AND A.TAX_RATE > 0)                             TAX_RATE,

       (SELECT SUM(A.REC_TAX_AMT)

        FROM   ZX_LINES_V A

        WHERE  A.TRX_ID = B.INVOICE_ID

               AND A.TRX_LINE_NUMBER = B.LINE_NUMBER

               AND A.TAX = 'IN_SGST'

               AND A.TAX_RATE_CODE NOT LIKE '%RCM%')           IN_SGST,

       (SELECT SUM(A.REC_TAX_AMT)

        FROM   ZX_LINES_V A

        WHERE  A.TRX_ID = B.INVOICE_ID

               AND A.TRX_LINE_NUMBER = B.LINE_NUMBER

               AND A.TAX = 'IN_CGST'

               AND A.TAX_RATE_CODE NOT LIKE '%RCM%')           IN_CGST,

       (SELECT SUM(A.REC_TAX_AMT)

        FROM   ZX_LINES_V A

        WHERE  A.TRX_ID = B.INVOICE_ID

               AND A.TRX_LINE_NUMBER = B.LINE_NUMBER

               AND A.TAX = 'IN_IGST'

               AND A.TAX_RATE_CODE NOT LIKE '%RCM%')           IN_IGST,

       (SELECT SUM(A.REC_TAX_AMT)

        FROM   ZX_LINES_V A

        WHERE  A.TRX_ID = B.INVOICE_ID

               AND A.TRX_LINE_NUMBER = B.LINE_NUMBER

               AND A.TAX = 'IN_IGST_OFFSET')                   RCM_IGST_LIAB,

       (SELECT SUM(A.REC_TAX_AMT)

        FROM   ZX_LINES_V A

        WHERE  A.TRX_ID = B.INVOICE_ID

               AND A.TRX_LINE_NUMBER = B.LINE_NUMBER

               AND A.TAX = 'IN_CGST_OFFSET')                   RCM_CGST_LIAB,

       (SELECT SUM(A.REC_TAX_AMT)

        FROM   ZX_LINES_V A

        WHERE  A.TRX_ID = B.INVOICE_ID

               AND A.TRX_LINE_NUMBER = B.LINE_NUMBER

               AND A.TAX = 'IN_SGST_OFFSET')                   RCM_SGST_LIAB,

       (SELECT SUM(A.REC_TAX_AMT)

        FROM   ZX_LINES_V A

        WHERE  A.TRX_ID = B.INVOICE_ID

               AND A.TRX_LINE_NUMBER = B.LINE_NUMBER

               AND A.TAX_RATE_CODE LIKE 'IN_IGST_RCM%')        RCM_IGST_REC,

       (SELECT SUM(A.REC_TAX_AMT)

        FROM   ZX_LINES_V A

        WHERE  A.TRX_ID = B.INVOICE_ID

               AND A.TRX_LINE_NUMBER = B.LINE_NUMBER

               AND A.TAX_RATE_CODE LIKE 'IN_CGST_RCM%')        RCM_CGST_REC,

       (SELECT SUM(A.REC_TAX_AMT)

        FROM   ZX_LINES_V A

        WHERE  A.TRX_ID = B.INVOICE_ID

               AND A.TRX_LINE_NUMBER = B.LINE_NUMBER

               AND A.TAX_RATE_CODE LIKE 'IN_SGST_RCM%')        RCM_SGST_REC,

       TO_CHAR(B.ACCOUNTING_DATE, 'DD-MON-YYYY') LINE_ACCOUNTING_DATE

FROM   AP_INVOICE_LINES_ALL B,

       AP_INVOICES_ALL C,

       POZ_SUPPLIERS D,

       HZ_PARTIES HP,

       POZ_SUPPLIER_SITES_V PSS,

       ZX_REGISTRATIONS ZR,

       ZX_PARTY_TAX_PROFILE ZPTP

WHERE  LINE_TYPE_LOOKUP_CODE = 'ITEM'

       AND C.INVOICE_ID = B.INVOICE_ID

       AND C.APPROVAL_STATUS != 'CANCELLED'

       AND C.VENDOR_ID = D.VENDOR_ID

       AND D.PARTY_ID = HP.PARTY_ID

       AND PSS.VENDOR_SITE_ID = C.VENDOR_SITE_ID

       AND ZPTP.PARTY_TAX_PROFILE_ID = ZR.PARTY_TAX_PROFILE_ID(+)

       AND PSS.PARTY_SITE_ID = ZPTP.PARTY_ID(+)

       AND ZPTP.PARTY_TYPE_CODE (+) = 'THIRD_PARTY_SITE'

       AND NVL(ZR.EFFECTIVE_TO (+), SYSDATE) >= sysdate

       AND EXISTS (SELECT 1

                   FROM   ZX_LINES_SUMMARY_V ZX

                   WHERE  ZX.TRX_ID = B.INVOICE_ID

                          AND ZX.TAX_RATE != '0')

      AND C.ORG_ID IN (SELECT DISTINCT ORGANIZATION_ID

                                       FROM HR_OPERATING_UNITS

                                       WHERE NAME IN (:p_bu))

      AND HP.PARTY_NAME IN NVL(:p_supplier, hp.party_name)

      AND C.INVOICE_ID IN (SELECT AIDA.INVOICE_ID

                                              FROM AP_INVOICE_DISTRIBUTIONS_ALL AIDA

                                              WHERE TO_CHAR(AIDA.ACCOUNTING_DATE, 'DD-MON-YYYY')

                                                BETWEEN NVL(to_char(:p_fromdate, 'DD-MON-YYYY'),

                                                to_char(sysdate, 'DD-MON-YYYY'))

                    and NVL(to_char(:p_todate, 'DD-MON-YYYY'), to_char(sysdate, 'DD-MON-YYYY')))

ORDER  BY C.INVOICE_ID