India TDS Control Sheet Query

SELECT DISTINCT INV_DTL.VENDOR_NAME, INV_DTL.INVOICE_ID,
                INV_DTL.INVOICE_AMOUNT, INV_DTL.INVOICE_NUM,
                INV_DTL.ACCOUNTING_DATE, INV_DTL.INVOICE_DATE,
                INV_DTL.ATTRIBUTE1, (INV_DTL.AMOUNT), INV_DTL.DED_AMT,
                INV_DTL.TAX_NAME, INV_DTL.SECTION_CODE, INV_DTL.TAX_RATE,
                INV_DTL.TAX_ID, INV_DTL.ORG_ID, INV_DTL.VENDOR_NUM,
                INV_DTL.TDS_VENDOR_TYPE_LOOKUP_CODE, INV_DTL.PAN_NO,
                INV_DTL.NAME
           FROM (SELECT   D.VENDOR_NAME, B.INVOICE_ID, B.INVOICE_AMOUNT,
                          B.INVOICE_NUM, C.ACCOUNTING_DATE, B.INVOICE_DATE,
                          C.ATTRIBUTE1, SUM (C.AMOUNT) AMOUNT,
                          ROUND ((JATTT.TAX_RATE * SUM (C.AMOUNT)) / 100
                                ) DED_AMT,
                          (SELECT DISTINCT TAX_NAME
                                      FROM JAI_CMN_TAXES_ALL
                                     WHERE TAX_ID = JATTT.TAX_ID) TAX_NAME,
                          JATTH.SECTION_CODE, JATTT.TAX_RATE,
                          JATTT.TAX_ID TAX_ID, B.ORG_ID,
                          D.SEGMENT1 VENDOR_NUM,
                          JATTH.VENDOR_TYPE_LOOKUP_CODE
                                                  TDS_VENDOR_TYPE_LOOKUP_CODE,
                          JATTG.VENDOR_PAN_NUM PAN_NO, G.NAME
                     FROM AP_INVOICES_ALL B,
                          JAI_AP_TDS_THHOLD_TRXS JATTT,
                          JAI_AP_TDS_THHOLD_HDRS JATTH,
                          JAI_AP_TDS_THHOLD_GRPS JATTG,
                          AP_INVOICE_DISTRIBUTIONS_ALL C,
                          AP_SUPPLIERS D,
                          HR_OPERATING_UNITS G
                    WHERE B.INVOICE_ID = C.INVOICE_ID
                      AND JATTT.VENDOR_ID = D.VENDOR_ID
                      AND JATTT.THRESHOLD_HDR_ID = JATTH.THRESHOLD_HDR_ID
                      AND JATTT.INVOICE_ID = B.INVOICE_ID
                      AND JATTT.THRESHOLD_GRP_ID = JATTG.THRESHOLD_GRP_ID
                      AND G.NAME = :NAME
                      AND G.ORGANIZATION_ID = C.ORG_ID
                      AND JATTH.SECTION_CODE LIKE
                              TO_CHAR (NVL (:SECTIONCODE, JATTH.SECTION_CODE))
                      AND JATTH.VENDOR_TYPE_LOOKUP_CODE LIKE
                             TO_CHAR (NVL (:VENDOR_TYPE,
                                           JATTH.VENDOR_TYPE_LOOKUP_CODE
                                          )
                                     )
                      AND C.LINE_TYPE_LOOKUP_CODE <> 'PREPAY'
                 GROUP BY D.VENDOR_NAME,
                          B.INVOICE_ID,
                          B.ATTRIBUTE1,
                          B.INVOICE_AMOUNT,
                          B.INVOICE_NUM,
                          C.ACCOUNTING_DATE,
                          B.INVOICE_DATE,
                          C.ATTRIBUTE1,
                          JATTH.THRESHOLD_SETUP_NAME,
                          JATTH.SECTION_CODE,
                          JATTT.TAX_RATE,
                          JATTT.TAX_ID,
                          B.ORG_ID,
                          D.SEGMENT1,
                          JATTH.VENDOR_TYPE_LOOKUP_CODE,
                          JATTG.VENDOR_PAN_NUM,
                          G.NAME) INV_DTL
          WHERE TO_DATE (INV_DTL.ACCOUNTING_DATE, 'DD-MON-RRRR')
                   BETWEEN TO_CHAR (TO_DATE (:FROM_DATE, 'DD-MON-RRRR'))
                       AND TO_CHAR (TO_DATE (:TO_DATE, 'DD-MON-RRRR'))
UNION
SELECT DISTINCT INV_DTL.VENDOR_NAME, INV_DTL.INVOICE_ID,
                INV_DTL.INVOICE_AMOUNT, INV_DTL.INVOICE_NUM,
                INV_DTL.ACCOUNTING_DATE, INV_DTL.INVOICE_DATE,
                INV_DTL.ATTRIBUTE1, (INV_DTL.AMOUNT), INV_DTL.DED_AMT,
                INV_DTL.TAX_NAME, INV_DTL.SECTION_CODE, INV_DTL.TAX_RATE,
                INV_DTL.TAX_ID, INV_DTL.ORG_ID, INV_DTL.VENDOR_NUM,
                INV_DTL.TDS_VENDOR_TYPE_LOOKUP_CODE, INV_DTL.PAN_NO,
                INV_DTL.NAME
           FROM (SELECT   D.VENDOR_NAME, B.INVOICE_ID, C.ATTRIBUTE_CATEGORY,
                          B.INVOICE_AMOUNT, B.INVOICE_NUM, C.ACCOUNTING_DATE,
                          B.INVOICE_DATE, C.ATTRIBUTE1, SUM (C.AMOUNT) AMOUNT,
                          ROUND ((JATTT.TAX_RATE * SUM (C.AMOUNT)) / 100
                                ) DED_AMT,
                          (SELECT DISTINCT TAX_NAME
                                      FROM JAI_CMN_TAXES_ALL
                                     WHERE TAX_ID = JATTT.TAX_ID) TAX_NAME,
                          JATTH.SECTION_CODE, JATTT.TAX_RATE,
                          JATTT.TAX_ID TAX_ID, B.ORG_ID,
                          D.SEGMENT1 VENDOR_NUM,
                          JATTH.VENDOR_TYPE_LOOKUP_CODE
                                                  TDS_VENDOR_TYPE_LOOKUP_CODE,
                          JATTG.VENDOR_PAN_NUM PAN_NO, G.NAME
                     FROM AP_INVOICES_ALL B,
                          JAI_AP_TDS_THHOLD_TRXS JATTT,
                          JAI_AP_TDS_THHOLD_HDRS JATTH,
                          JAI_AP_TDS_THHOLD_GRPS JATTG,
                          AP_INVOICE_DISTRIBUTIONS_ALL C,
                          AP_SUPPLIERS D,
                          HR_OPERATING_UNITS G
                    WHERE B.INVOICE_ID = C.INVOICE_ID
                      AND JATTT.VENDOR_ID = D.VENDOR_ID
                      AND JATTT.THRESHOLD_HDR_ID = JATTH.THRESHOLD_HDR_ID
                      AND JATTT.INVOICE_ID = B.INVOICE_ID
                      AND JATTT.THRESHOLD_GRP_ID = JATTG.THRESHOLD_GRP_ID
                      AND G.NAME = :NAME
                      AND G.ORGANIZATION_ID = C.ORG_ID
                      AND JATTH.SECTION_CODE LIKE
                              TO_CHAR (NVL (:SECTIONCODE, JATTH.SECTION_CODE))
                      AND JATTH.VENDOR_TYPE_LOOKUP_CODE LIKE
                             TO_CHAR (NVL (:VENDOR_TYPE,
                                           JATTH.VENDOR_TYPE_LOOKUP_CODE
                                          )
                                     )
                      AND C.LINE_TYPE_LOOKUP_CODE = 'PREPAY'
                      AND C.GLOBAL_ATTRIBUTE_CATEGORY =
                                                'JA.IN.APXINWKB.DISTRIBUTIONS'
                 GROUP BY D.VENDOR_NAME,
                          B.INVOICE_ID,
                          B.ATTRIBUTE1,
                          B.INVOICE_AMOUNT,
                          B.INVOICE_NUM,
                          C.ATTRIBUTE_CATEGORY,
                          C.ACCOUNTING_DATE,
                          B.INVOICE_DATE,
                          C.ATTRIBUTE1,
                          JATTH.THRESHOLD_SETUP_NAME,
                          JATTH.SECTION_CODE,
                          JATTT.TAX_RATE,
                          JATTT.TAX_ID,
                          B.ORG_ID,
                          D.SEGMENT1,
                          JATTH.VENDOR_TYPE_LOOKUP_CODE,
                          JATTG.VENDOR_PAN_NUM,
                          G.NAME) INV_DTL
          WHERE TO_DATE (INV_DTL.ACCOUNTING_DATE, 'DD-MON-RRRR')
                   BETWEEN TO_CHAR (TO_DATE (:FROM_DATE, 'DD-MON-RRRR'))
                       AND TO_CHAR (TO_DATE (:TO_DATE, 'DD-MON-RRRR'))

AP ACCRUAL QUERY FOR OPM

SELECT ORG_ID,TRX_HDR_ID,SHIPMENT_HEADER_ID,NULL SHIPMENT_LINE_ID, SEGMENT4,INVENTORY_ORGANIZATION_CODE,RCV_RECEIPT_NUM,PO_ORDER_NUMBER
,PO_LINE_NUM,
ACCOUNTED_CR,ACCOUNTED_DR,ACCOUNTING_DATE,TRX_TYPE_NAME,ENTERED_CR,ENTERED_DR,CURRENCY_CODE,
THIRD_PARTY_NAME,TRX_DATE,INVENTORY_ITEM_ID,TRX_QUANTITY,ITEM_DESCRIPTION,TAX_AMT_CR,
TAX_AMT_DR,PO_DATE,CHALLAN_NUM
FROM (
SELECT ORG_ID, RCV_TRX_ID TRX_HDR_ID , SHIPMENT_HEADER_ID, SHIPMENT_LINE_ID,
        SEGMENT4, INVENTORY_ORGANIZATION_CODE,
       RCV_RECEIPT_NUM, PO_ORDER_NUMBER, PO_LINE_NUM,
       NVL (DECODE (SIGN (DD.BASE_AMOUNT), -1, ABS (DD.BASE_AMOUNT), 0, 0, ''),
            0
           ) ACCOUNTED_CR,
       NVL (DECODE (SIGN (DD.BASE_AMOUNT), 1, (DD.BASE_AMOUNT), 0, 0, ''),
            0
           ) ACCOUNTED_DR,ACCOUNTING_DATE,INITCAP(TRANSACTION_TYPE) TRX_TYPE_NAME,NULL ENTERED_CR, NULL ENTERED_DR, CURRENCY_CODE,
        THIRD_PARTY_NAME, TRX_DATE, INVENTORY_ITEM_ID,
       TRX_QUANTITY, ITEM_DESCRIPTION, (SELECT SUM (ENTERED_CR)
          FROM JAI_RCV_JOURNAL_ENTRIES J,
               GL_CODE_COMBINATIONS GCC,
               RCV_TRANSACTIONS RT
         WHERE RT.TRANSACTION_ID = DD.RCV_TRX_ID            --4201763--4201801
           AND GCC.CODE_COMBINATION_ID = J.CODE_COMBINATION_ID
           AND RT.SHIPMENT_LINE_ID = J.SHIPMENT_LINE_ID
           AND RT.TRANSACTION_ID = J.TRANSACTION_ID
           AND GCC.SEGMENT4 IN
                  ('212801', '212802', '212804', '212805', '212807', '212808',
                   '212809', '212810', '212811')) TAX_AMT_CR,
       (SELECT SUM (ENTERED_DR)
          FROM JAI_RCV_JOURNAL_ENTRIES J,
               GL_CODE_COMBINATIONS GCC,
               RCV_TRANSACTIONS RT
         WHERE RT.TRANSACTION_ID = DD.RCV_TRX_ID            --4201763--4201801
           AND GCC.CODE_COMBINATION_ID = J.CODE_COMBINATION_ID
           AND RT.SHIPMENT_LINE_ID = J.SHIPMENT_LINE_ID
           AND RT.TRANSACTION_ID = J.TRANSACTION_ID
           AND GCC.SEGMENT4 IN
                  ('212801', '212802', '212804', '212805', '212807', '212808',
                   '212809', '212810', '212811')) TAX_AMT_DR, PO_DATE, CHALLAN_NUM
  FROM (SELECT GXEH.OPERATING_UNIT ORG_ID,
                        RT.TRANSACTION_ID RCV_TRX_ID, RCV.SHIPMENT_HEADER_ID,
                        RT.SHIPMENT_LINE_ID, RT.TRANSACTION_TYPE,
                        GCC.SEGMENT4,
                        MP.ORGANIZATION_CODE INVENTORY_ORGANIZATION_CODE,
                        RCV.RECEIPT_NUM RCV_RECEIPT_NUM,
                        POA.SEGMENT1 PO_ORDER_NUMBER,
                        POL.LINE_NUM PO_LINE_NUM,
                        NVL (GXEL.BASE_AMOUNT, 0) BASE_AMOUNT,
                        GXEH.TRANSACTION_DATE ACCOUNTING_DATE,
                        GXEH.BASE_CURRENCY CURRENCY_CODE,
                        PV.VENDOR_NAME THIRD_PARTY_NAME,
                        GXEH.TRANSACTION_DATE TRX_DATE, MSI.INVENTORY_ITEM_ID,
                        GXEH.TRANSACTION_QUANTITY TRX_QUANTITY,
                        MSI.DESCRIPTION ITEM_DESCRIPTION,
                        TRUNC (POA.CREATION_DATE) PO_DATE,
                        RCV.SHIPMENT_NUM CHALLAN_NUM
                   FROM APPS.XLA_AE_LINES D,
                        APPS.XLA_DISTRIBUTION_LINKS E,
                        APPS.GL_CODE_COMBINATIONS_KFV GCC,
                        APPS.GMF_XLA_EXTRACT_LINES GXEL,
                        APPS.GMF_XLA_EXTRACT_HEADERS GXEH,
                        APPS.RCV_SHIPMENT_HEADERS RCV,
                        APPS.RCV_TRANSACTIONS RT,
                        APPS.PO_HEADERS_ALL POA,
                        APPS.PO_LINES_ALL POL,
                        APPS.PO_DISTRIBUTIONS_ALL POD,
                        APPS.PO_VENDORS PV,
                        APPS.MTL_SYSTEM_ITEMS MSI,
                        APPS.MTL_PARAMETERS MP
                  WHERE GCC.SEGMENT4 IN
                           ('212801', '212802', '212804', '212805', '212807',
                            '212808', '212809', '212810', '212811')
                    AND D.AE_HEADER_ID = E.AE_HEADER_ID
                    AND D.AE_LINE_NUM = E.AE_LINE_NUM
                    AND E.SOURCE_DISTRIBUTION_TYPE = 'PURCHASING'
                    AND E.SOURCE_DISTRIBUTION_ID_NUM_1 = GXEL.LINE_ID
                    AND D.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
                    AND GXEL.HEADER_ID = GXEH.HEADER_ID
                    AND E.SOURCE_DISTRIBUTION_TYPE = GXEH.ENTITY_CODE
                    AND GXEH.SOURCE_DOCUMENT_ID = RCV.SHIPMENT_HEADER_ID
                    AND GXEH.SOURCE_DOCUMENT_ID = RT.SHIPMENT_HEADER_ID
                    AND GXEH.SOURCE_LINE_ID = RT.TRANSACTION_ID
                    AND RCV.VENDOR_ID = PV.VENDOR_ID(+)
                    AND GXEH.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
                    AND GXEH.ORGANIZATION_ID = MSI.ORGANIZATION_ID
                    AND RT.SHIPMENT_HEADER_ID = RCV.SHIPMENT_HEADER_ID
                    AND RT.PO_HEADER_ID = POA.PO_HEADER_ID
                    AND POA.PO_HEADER_ID = POL.PO_HEADER_ID
                    AND POL.PO_LINE_ID = POD.PO_LINE_ID
                    AND MSI.ORGANIZATION_ID = MP.ORGANIZATION_ID
                    AND RT.PO_LINE_LOCATION_ID(+) = POD.LINE_LOCATION_ID
                    AND TRUNC (GXEH.TRANSACTION_DATE) >=
                           NVL (TRUNC (TO_DATE (:P_FROM_ACCT_DATE)),
                                TRUNC (GXEH.TRANSACTION_DATE)
                               )
                    AND TRUNC (GXEH.TRANSACTION_DATE) <
                           NVL (TRUNC (TO_DATE (:P_TO_ACCT_DATE)) + 1,
                                TRUNC (GXEH.TRANSACTION_DATE)
                               )
                    AND GXEL.JOURNAL_LINE_TYPE = 'AAP'
                    AND RT.TRANSACTION_ID NOT IN (
                           SELECT DISTINCT ADA.RCV_TRANSACTION_ID
                                      FROM AP_INVOICE_DISTRIBUTIONS_ALL ADA,
                                           AP_INVOICES_ALL AIA
                                     WHERE ADA.RCV_TRANSACTION_ID =
                                                             RT.TRANSACTION_ID
                                       AND ADA.INVOICE_ID = AIA.INVOICE_ID
                                       AND AIA.INVOICE_AMOUNT > 0
                                       AND ADA.ACCOUNTING_DATE
                                              BETWEEN '01-FEB-2008'
                                                  AND :P_TO_ACCT_DATE
                                       AND ADA.ORG_ID =
                                                   NVL (:P_ORG_ID, ADA.ORG_ID))
                    AND GXEH.OPERATING_UNIT =
                                          NVL (:P_ORG_ID, GXEH.OPERATING_UNIT)
                    AND GXEH.TRANSACTION_QUANTITY > 0      
                    AND RT.TRANSACTION_TYPE = 'RECEIVE') DD
UNION
SELECT ORG_ID, RCV_TRX_ID TRX_HDR_ID , SHIPMENT_HEADER_ID, SHIPMENT_LINE_ID,
        SEGMENT4, INVENTORY_ORGANIZATION_CODE,
       RCV_RECEIPT_NUM, PO_ORDER_NUMBER, PO_LINE_NUM,
       NVL (DECODE (SIGN (DD.BASE_AMOUNT), -1, ABS (DD.BASE_AMOUNT), 0, 0, ''),
            0
           ) ACCOUNTED_CR,
       NVL (DECODE (SIGN (DD.BASE_AMOUNT), 1, (DD.BASE_AMOUNT), 0, 0, ''),
            0
           ) ACCOUNTED_DR,ACCOUNTING_DATE,INITCAP(TRANSACTION_TYPE) TRX_TYPE_NAME,NULL ENTERED_CR, NULL ENTERED_DR, CURRENCY_CODE,
        THIRD_PARTY_NAME, TRX_DATE, INVENTORY_ITEM_ID,
       TRX_QUANTITY, ITEM_DESCRIPTION, (SELECT SUM (ENTERED_CR)
          FROM JAI_RCV_JOURNAL_ENTRIES J,
               GL_CODE_COMBINATIONS GCC,
               RCV_TRANSACTIONS RT
         WHERE RT.TRANSACTION_ID = DD.RCV_TRX_ID            --4201763--4201801
           AND GCC.CODE_COMBINATION_ID = J.CODE_COMBINATION_ID
           AND RT.SHIPMENT_LINE_ID = J.SHIPMENT_LINE_ID
           AND RT.TRANSACTION_ID = J.TRANSACTION_ID
           AND GCC.SEGMENT4 IN
                  ('212801', '212802', '212804', '212805', '212807', '212808',
                   '212809', '212810', '212811')) TAX_AMT_CR,
       (SELECT SUM (ENTERED_DR)
          FROM JAI_RCV_JOURNAL_ENTRIES J,
               GL_CODE_COMBINATIONS GCC,
               RCV_TRANSACTIONS RT
         WHERE RT.TRANSACTION_ID = DD.RCV_TRX_ID            --4201763--4201801
           AND GCC.CODE_COMBINATION_ID = J.CODE_COMBINATION_ID
           AND RT.SHIPMENT_LINE_ID = J.SHIPMENT_LINE_ID
           AND RT.TRANSACTION_ID = J.TRANSACTION_ID
           AND GCC.SEGMENT4 IN
                  ('212801', '212802', '212804', '212805', '212807', '212808',
                   '212809', '212810', '212811')) TAX_AMT_DR, PO_DATE, CHALLAN_NUM
  FROM (SELECT  GXEH.OPERATING_UNIT ORG_ID,
                        RT.TRANSACTION_ID RCV_TRX_ID,
                                                   --RT.PARENT_TRANSACTION_ID,
                                                     RCV.SHIPMENT_HEADER_ID,
                        RT.SHIPMENT_LINE_ID, RT.TRANSACTION_TYPE,
                        GCC.SEGMENT4,
                        MP.ORGANIZATION_CODE INVENTORY_ORGANIZATION_CODE,
                        RCV.RECEIPT_NUM RCV_RECEIPT_NUM,
                        POA.SEGMENT1 PO_ORDER_NUMBER,
                        POL.LINE_NUM PO_LINE_NUM,
                        NVL (GXEL.BASE_AMOUNT, 0) BASE_AMOUNT,
                        GXEH.TRANSACTION_DATE ACCOUNTING_DATE,
                        GXEH.BASE_CURRENCY CURRENCY_CODE,
                        PV.VENDOR_NAME THIRD_PARTY_NAME,
                        GXEH.TRANSACTION_DATE TRX_DATE, MSI.INVENTORY_ITEM_ID,
                        GXEH.TRANSACTION_QUANTITY TRX_QUANTITY,
                        MSI.DESCRIPTION ITEM_DESCRIPTION,
                        TRUNC (POA.CREATION_DATE) PO_DATE,
                        RCV.SHIPMENT_NUM CHALLAN_NUM
                   FROM APPS.XLA_AE_LINES D,
                        APPS.XLA_DISTRIBUTION_LINKS E,
                        APPS.GL_CODE_COMBINATIONS_KFV GCC,
                        APPS.GMF_XLA_EXTRACT_LINES GXEL,
                        APPS.GMF_XLA_EXTRACT_HEADERS GXEH,
                        APPS.RCV_SHIPMENT_HEADERS RCV,
                        APPS.RCV_TRANSACTIONS RT,
                        APPS.PO_HEADERS_ALL POA,
                        APPS.PO_LINES_ALL POL,
                        APPS.PO_VENDORS PV,
                        APPS.MTL_SYSTEM_ITEMS MSI,
                        APPS.MTL_PARAMETERS MP
                  WHERE GCC.SEGMENT4 IN
                           ('212801', '212802', '212804', '212805', '212807',
                            '212808', '212809', '212810', '212811')
                    AND D.AE_HEADER_ID = E.AE_HEADER_ID
                    AND D.AE_LINE_NUM = E.AE_LINE_NUM
                    AND E.SOURCE_DISTRIBUTION_TYPE = 'PURCHASING'
                    AND E.SOURCE_DISTRIBUTION_ID_NUM_1 = GXEL.LINE_ID
                    AND D.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
                    AND GXEL.HEADER_ID = GXEH.HEADER_ID
                    AND E.SOURCE_DISTRIBUTION_TYPE = GXEH.ENTITY_CODE
--                    AND GXEH.SOURCE_DOCUMENT_ID = RCV.SHIPMENT_HEADER_ID
                    AND GXEH.SOURCE_LINE_ID = RT.TRANSACTION_ID
                    AND RCV.VENDOR_ID = PV.VENDOR_ID(+)
                    AND GXEH.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
                    AND GXEH.ORGANIZATION_ID = MSI.ORGANIZATION_ID
                    AND RT.SHIPMENT_HEADER_ID = RCV.SHIPMENT_HEADER_ID
                    AND RT.PO_HEADER_ID = POA.PO_HEADER_ID
                    AND POA.PO_HEADER_ID = POL.PO_HEADER_ID
                    AND MSI.ORGANIZATION_ID = MP.ORGANIZATION_ID
                    AND TRUNC (GXEH.TRANSACTION_DATE) >=
                           NVL (TRUNC (TO_DATE (:P_FROM_ACCT_DATE)),
                                TRUNC (GXEH.TRANSACTION_DATE)
                               )
                    AND TRUNC (GXEH.TRANSACTION_DATE) <
                           NVL (TRUNC (TO_DATE (:P_TO_ACCT_DATE)) + 1,
                                TRUNC (GXEH.TRANSACTION_DATE)
                               )
                    AND GXEL.JOURNAL_LINE_TYPE = 'AAP'
                    AND GXEH.TRANSACTION_QUANTITY < 0
                    AND GXEH.OPERATING_UNIT =
                                          NVL (:P_ORG_ID, GXEH.OPERATING_UNIT)
                    AND RT.TRANSACTION_TYPE IN ('CORRECT')
                    AND RT.PARENT_TRANSACTION_ID NOT IN (
                           SELECT DISTINCT ADA.RCV_TRANSACTION_ID
                                      FROM AP_INVOICE_DISTRIBUTIONS_ALL ADA,
                                           AP_INVOICES_ALL AIA
                                     WHERE ADA.RCV_TRANSACTION_ID =
                                                      RT.PARENT_TRANSACTION_ID
                                       AND ADA.INVOICE_ID = AIA.INVOICE_ID
                                       AND AIA.INVOICE_AMOUNT > 0
                                       AND ADA.ACCOUNTING_DATE
                                              BETWEEN '01-FEB-2008'
                                                  AND :P_TO_ACCT_DATE
                                       AND ADA.ORG_ID =
                                                   NVL (:P_ORG_ID, ADA.ORG_ID))) DD
UNION
SELECT ORG_ID, RCV_TRX_ID TRX_HDR_ID , SHIPMENT_HEADER_ID, SHIPMENT_LINE_ID,
        SEGMENT4, INVENTORY_ORGANIZATION_CODE,
       RCV_RECEIPT_NUM, PO_ORDER_NUMBER, PO_LINE_NUM,
       NVL (DECODE (SIGN (DD.BASE_AMOUNT), -1, ABS (DD.BASE_AMOUNT), 0, 0, ''),
            0
           ) ACCOUNTED_CR,
       NVL (DECODE (SIGN (DD.BASE_AMOUNT), 1, (DD.BASE_AMOUNT), 0, 0, ''),
            0
           ) ACCOUNTED_DR,ACCOUNTING_DATE,INITCAP(TRANSACTION_TYPE) TRX_TYPE_NAME,NULL ENTERED_CR, NULL ENTERED_DR, CURRENCY_CODE,
        THIRD_PARTY_NAME, TRX_DATE, INVENTORY_ITEM_ID,
       TRX_QUANTITY, ITEM_DESCRIPTION, (SELECT SUM (ENTERED_CR)
          FROM JAI_RCV_JOURNAL_ENTRIES J,
               GL_CODE_COMBINATIONS GCC,
               RCV_TRANSACTIONS RT
         WHERE RT.TRANSACTION_ID = DD.RCV_TRX_ID            --4201763--4201801
           AND GCC.CODE_COMBINATION_ID = J.CODE_COMBINATION_ID
           AND RT.SHIPMENT_LINE_ID = J.SHIPMENT_LINE_ID
           AND RT.TRANSACTION_ID = J.TRANSACTION_ID
           AND GCC.SEGMENT4 IN
                  ('212801', '212802', '212804', '212805', '212807', '212808',
                   '212809', '212810', '212811')) TAX_AMT_CR,
       (SELECT SUM (ENTERED_DR)
          FROM JAI_RCV_JOURNAL_ENTRIES J,
               GL_CODE_COMBINATIONS GCC,
               RCV_TRANSACTIONS RT
         WHERE RT.TRANSACTION_ID = DD.RCV_TRX_ID            --4201763--4201801
           AND GCC.CODE_COMBINATION_ID = J.CODE_COMBINATION_ID
           AND RT.SHIPMENT_LINE_ID = J.SHIPMENT_LINE_ID
           AND RT.TRANSACTION_ID = J.TRANSACTION_ID
           AND GCC.SEGMENT4 IN
                  ('212801', '212802', '212804', '212805', '212807', '212808',
                   '212809', '212810', '212811')) TAX_AMT_DR, PO_DATE, CHALLAN_NUM
  FROM (SELECT  GXEH.OPERATING_UNIT ORG_ID,
                        RT.TRANSACTION_ID RCV_TRX_ID, RCV.SHIPMENT_HEADER_ID,
                        RT.SHIPMENT_LINE_ID, RT.TRANSACTION_TYPE,
                        GCC.SEGMENT4,
                        MP.ORGANIZATION_CODE INVENTORY_ORGANIZATION_CODE,
                        RCV.RECEIPT_NUM RCV_RECEIPT_NUM,
                        POA.SEGMENT1 PO_ORDER_NUMBER,
                        POL.LINE_NUM PO_LINE_NUM,
                        NVL (GXEL.BASE_AMOUNT, 0) BASE_AMOUNT,
                        GXEH.TRANSACTION_DATE ACCOUNTING_DATE,
                        GXEH.BASE_CURRENCY CURRENCY_CODE,
                        PV.VENDOR_NAME THIRD_PARTY_NAME,
                        RT.TRANSACTION_DATE TRX_DATE, MSI.INVENTORY_ITEM_ID,
                        GXEH.TRANSACTION_QUANTITY TRX_QUANTITY,
                        MSI.DESCRIPTION ITEM_DESCRIPTION,
                        TRUNC (POA.CREATION_DATE) PO_DATE,
                        RCV.SHIPMENT_NUM CHALLAN_NUM
                   FROM APPS.XLA_AE_LINES D,
                        APPS.XLA_DISTRIBUTION_LINKS E,
                        APPS.GL_CODE_COMBINATIONS_KFV GCC,
                        APPS.GMF_XLA_EXTRACT_LINES GXEL,
                        APPS.GMF_XLA_EXTRACT_HEADERS GXEH,
                        APPS.RCV_SHIPMENT_HEADERS RCV,
                        APPS.RCV_TRANSACTIONS RT,
                        APPS.PO_HEADERS_ALL POA,
                        APPS.PO_LINES_ALL POL,
                        APPS.PO_VENDORS PV,
                        APPS.MTL_SYSTEM_ITEMS MSI,
                        APPS.MTL_PARAMETERS MP
                  WHERE GCC.SEGMENT4 IN
                           ('212801', '212802', '212804', '212805', '212807',
                            '212808', '212809', '212810', '212811')
                    AND D.AE_HEADER_ID = E.AE_HEADER_ID
                    AND D.AE_LINE_NUM = E.AE_LINE_NUM
                    AND E.SOURCE_DISTRIBUTION_TYPE = 'PURCHASING'
                    AND E.SOURCE_DISTRIBUTION_ID_NUM_1 = GXEL.LINE_ID
                    AND D.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
                    AND GXEL.HEADER_ID = GXEH.HEADER_ID
                    AND E.SOURCE_DISTRIBUTION_TYPE = GXEH.ENTITY_CODE
                    AND GXEH.SOURCE_DOCUMENT_ID = RCV.SHIPMENT_HEADER_ID
                    AND GXEH.SOURCE_DOCUMENT_ID = RT.SHIPMENT_HEADER_ID
                    AND GXEH.SOURCE_LINE_ID = RT.TRANSACTION_ID
                    AND RCV.VENDOR_ID = PV.VENDOR_ID(+)
                    AND GXEH.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
                    AND GXEH.ORGANIZATION_ID = MSI.ORGANIZATION_ID
                    AND RT.SHIPMENT_HEADER_ID = RCV.SHIPMENT_HEADER_ID
                    AND RT.PO_HEADER_ID = POA.PO_HEADER_ID
                    AND POA.PO_HEADER_ID = POL.PO_HEADER_ID
                    AND MSI.ORGANIZATION_ID = MP.ORGANIZATION_ID
                    AND TRUNC (RT.TRANSACTION_DATE) >=
                           NVL (TRUNC (TO_DATE (:P_FROM_ACCT_DATE)),
                                TRUNC (RT.TRANSACTION_DATE)
                               )
                    AND TRUNC (RT.TRANSACTION_DATE) <
                           NVL (TRUNC (TO_DATE (:P_TO_ACCT_DATE)) + 1,
                                TRUNC (RT.TRANSACTION_DATE)
                               )
                    AND GXEL.JOURNAL_LINE_TYPE = 'AAP'
                    AND GXEH.OPERATING_UNIT =
                                          NVL (:P_ORG_ID, GXEH.OPERATING_UNIT)
                    AND RT.TRANSACTION_TYPE IN ('RETURN TO VENDOR')
                    AND RT.PARENT_TRANSACTION_ID NOT IN (
                           SELECT DISTINCT ADA.RCV_TRANSACTION_ID
                                      FROM AP_INVOICE_DISTRIBUTIONS_ALL ADA,
                                           AP_INVOICES_ALL AIA
                                     WHERE ADA.RCV_TRANSACTION_ID =
                                                      RT.PARENT_TRANSACTION_ID
                                       AND ADA.INVOICE_ID = AIA.INVOICE_ID
                                       AND AIA.INVOICE_AMOUNT > 0
                                       AND ADA.ACCOUNTING_DATE
                                              BETWEEN '01-FEB-2008'
                                                  AND :P_TO_ACCT_DATE
                                       AND ADA.ORG_ID =
                                                   NVL (:P_ORG_ID, ADA.ORG_ID))) DD
                                                   )

XML Publisher System Date

Display system date / report generated date as below

<?xdoxslt:sysdate('DD-MON-YYYY HH24:MI‘)?>
<?xdoxslt:sysdate('DD-MM-YYYY HH24:MI‘)?>
<?xdoxslt:sysdate('DD-MON-YYYY‘)?>

Insert Serial Number Directly to RTF Design (XML Publisher)

One blizzard problem comes during report development is with the serial number .

When you give two or more grouping in your query , serial number make hell lot of problem in EXCEL output. To resolve this problem a simple xml code has to be inserted in the RTF design.

1. Insert your design and then copy paste any of your field to the front.

2. Then double click your first field and then change the code as
       <xsl:value-of select="position()"/>

SNO	Date	Trans Type	Credit
F SNO	ACCOUNTED_DATE	CATEGORY	CREDIT E

3. Then you are done, here i will share my output format

SNO	Date	Trans Type	Credit
1	12-Jun-12	Misc Receipts	40
2	13-Jun-12	Receiving	20
3	13-Jun-12	Receiving India
4	18-Jun-12	Misc Receipts

CRM Module - AMC Expiry Query

SELECT
rownum SR_NO, OPERATING_UNIT, CONTRACT_NUMBER, CUSTOMER_NAME,LOCATION,
         BILL_TO,SHIP_TO,SITE_INCHARGE_NAME, CONTACT_DETAILS,
         MACHINE_MODEL, SERIAL_NUMBER, START_DATE, END_DATE,
         PAYMENT_TERMS, SALES_PERSON_NAME,AMOUNT, CLE_ID
FROM (SELECT  XX.SR_NO, XX.OPERATING_UNIT, XX.CONTRACT_NUMBER, XX.CUSTOMER_NAME,XX.CITY LOCATION,
         XX.BILL_TO, XX.SHIP_TO, XX.SITE_INCHARGE_NAME, XX.CONTACT_DETAILS,
         XX.MACHINE_MODEL, XX.SERIAL_NUMBER, XX.START_DATE, XX.END_DATE,
         XX.PAYMENT_TERMS, XX.SALES_PERSON_NAME,XX.AMOUNT, XX.CLE_ID
    FROM (SELECT DISTINCT rownum SR_NO, X.OPERATING_UNIT, X.CONTRACT_NUMBER, X.CUSTOMER_NAME,X.CITY, X.PAYMENT_TERM_ID,
                 (SELECT DISTINCT    HL.ADDRESS1
                                  || HL.ADDRESS2
                                  || HL.ADDRESS3
                                  || HL.CITY
                                  || HL.STATE
                             FROM OKC_K_HEADERS_ALL_B OKHAB,
                                  HZ_CUST_SITE_USES_ALL HCSUA,
                                  HZ_CUST_ACCT_SITES_ALL HCASA,
                                  HZ_PARTY_SITES HPS,
                                  HZ_LOCATIONS HL
                            WHERE OKHAB.CONTRACT_NUMBER = X.CONTRACT_NUMBER
                              AND HCSUA.SITE_USE_ID =
                                                     OKHAB.SHIP_TO_SITE_USE_ID
                              AND HCASA.CUST_ACCT_SITE_ID =
                                                       HCSUA.CUST_ACCT_SITE_ID
                              AND HPS.PARTY_SITE_ID = HCASA.PARTY_SITE_ID
                              AND HL.LOCATION_ID = HPS.LOCATION_ID) BILL_TO,
                 X.SHIP_TO, X.SITE_INCHARGE_NAME, X.CONTACT_DETAILS,
                 X.MACHINE_MODEL, X.SERIAL_NUMBER, X.START_DATE, X.END_DATE,
                 (SELECT RT.NAME FROM RA_TERMS RT ,okc_k_headers_all_b a
                    WHERE A.PAYMENT_TERM_ID=RT.TERM_ID
                    AND a.ID = X.ID) PAYMENT_TERMS,
                 (select LAST_NAME ||' '|| FIRST_NAME from PER_ALL_PEOPLE_F where person_id =
                   ( select PERSON_ID from jtf_rs_salesreps where salesrep_id = NVL(X.OBJECT1_ID1,salesrep_id)))
                                                            SALES_PERSON_NAME,
                 X.AMOUNT, X.CLE_ID
            FROM (SELECT DISTINCT HR.NAME AS OPERATING_UNIT,
                                  OKHAB.CONTRACT_NUMBER AS CONTRACT_NUMBER,I.CLE_ID,
                                  OKHAB.PAYMENT_TERM_ID,OK.OBJECT1_ID1,
                                  OLDV.CUSTOMER_ACCOUNT_NAME CUSTOMER_NAME,CA.CITY,
                                  OKHAB.ID,
                                  (   CA.ADDRESS1
                                   || CA.ADDRESS2
                                   || CA.ADDRESS3
                                   || CA.CITY
                                  ) SHIP_TO,
                                  OKLB.ATTRIBUTE1 SITE_INCHARGE_NAME,
                                  OKLB.ATTRIBUTE2 CONTACT_DETAILS,
                                  MTL.DESCRIPTION MACHINE_MODEL,
                                  CII.SERIAL_NUMBER SERIAL_NUMBER,
--                                  OAL.START_DATE, OAL.END_DATE,
                                 (TO_CHAR (OAL.START_DATE,'DD-MON-RRRR ')) START_DATE,
                                  OAL.END_DATE END_DATE,
                                  ROUND(OKLB.PRICE_NEGOTIATED) AS AMOUNT
                  FROM            OKC_K_HEADERS_ALL_B OKHAB,
                                  OKC_K_LINES_B OKLB,
                                  HR_OPERATING_UNITS HR,
                                  OKS_LINE_DETAILS_V OLDV,
                                  OKS_AUTH_LINES_V OAL,
                                  OKC_K_ITEMS I,
                                  CSI_ITEM_INSTANCES CII,
                                  MTL_SYSTEM_ITEMS_B MTL,
                                  CSI_HZPTY_ADDRESSES_V CA,
                                  OKC_CONTACTS OK,
                                  HZ_PARTY_SITE_USES HZP
                            WHERE HR.ORGANIZATION_ID = OKHAB.AUTHORING_ORG_ID
                              AND OKLB.DNZ_CHR_ID = OKHAB.ID
                              AND OLDV.CONTRACT_ID = OKHAB.ID
                              AND OAL.CLE_ID = OKLB.CLE_ID
                              AND I.CLE_ID = OKLB.ID
                              AND CII.INSTANCE_ID = I.OBJECT1_ID1
                              AND CII.INVENTORY_ITEM_ID =
                                                         MTL.INVENTORY_ITEM_ID
                              AND OKHAB.INV_ORGANIZATION_ID =
                                                           MTL.ORGANIZATION_ID
                              AND CII.LOCATION_ID = CA.LOCATION
                              AND CA.LOCATION = HZP.PARTY_SITE_ID(+)
                              AND SITE_USE_TYPE(+) = 'INSTALL_AT'
                              AND HZP.STATUS(+) = 'A'
                              AND CA.STATUS = 'A'
and ok.DNZ_CHR_ID = NVL(OKHAB.ID , OK.dnz_chr_id)
AND ok.SALES_GROUP_ID IS NOT NULL
                              AND OKHAB.CONTRACT_NUMBER =
                                     NVL (:CONTRACT_NUMBER,
                                          OKHAB.CONTRACT_NUMBER
                                         )
                              AND OKHAB.ORG_ID = NVL (:P_ORG_ID, OKHAB.ORG_ID)
                              AND TRUNC (OAL.END_DATE)
                                     BETWEEN NVL (TO_DATE (:P_START_DATE),
                                                  TRUNC (OAL.END_DATE)
                                                 )
                                         AND NVL (TO_DATE (:P_END_DATE),
                                                  TRUNC (SYSDATE)
                                                 )
                                                 ) X
                                                 WHERE X.CUSTOMER_NAME IS NOT NULL
                                                 ORDER BY X.CLE_ID ASC
                                                 ) XX     WHERE XX.SALES_PERSON_NAME IS NOT NULL
UNION ALL
 SELECT  XX.SR_NO, XX.OPERATING_UNIT, XX.CONTRACT_NUMBER, XX.CUSTOMER_NAME, XX.CITY LOCATION,
         XX.BILL_TO, XX.SHIP_TO, XX.SITE_INCHARGE_NAME, XX.CONTACT_DETAILS,
         XX.MACHINE_MODEL, NULL SERIAL_NUMBER, XX.START_DATE, XX.END_DATE,
         XX.PAYMENT_TERMS, XX.SALES_PERSON_NAME,XX.AMOUNT, XX.CLE_ID FROM (
 SELECT DISTINCT rownum SR_NO ,X.OPERATING_UNIT, X.CONTRACT_NUMBER, X.CUSTOMER_NAME,/*X.CITY ,*/ X.PAYMENT_TERM_ID,
                 (SELECT DISTINCT    HL.ADDRESS1
                                  || HL.ADDRESS2
                                  || HL.ADDRESS3
                                  || HL.CITY
                                  || HL.STATE
                             FROM OKC_K_HEADERS_ALL_B OKHAB,
                                  HZ_CUST_SITE_USES_ALL HCSUA,
                                  HZ_CUST_ACCT_SITES_ALL HCASA,
                                  HZ_PARTY_SITES HPS,
                                  HZ_LOCATIONS HL
                            WHERE OKHAB.CONTRACT_NUMBER = X.CONTRACT_NUMBER
                              AND HCSUA.SITE_USE_ID =
                                                     OKHAB.SHIP_TO_SITE_USE_ID
                              AND HCASA.CUST_ACCT_SITE_ID =
                                                       HCSUA.CUST_ACCT_SITE_ID
                              AND HPS.PARTY_SITE_ID = HCASA.PARTY_SITE_ID
                              AND HL.LOCATION_ID = HPS.LOCATION_ID) BILL_TO,
                              ( SELECT DISTINCT  HLA.CITY
                              FROM OKC_K_HEADERS_ALL_B OKHAB,
                                  HZ_CUST_SITE_USES_ALL HCSUA,
                                  HZ_CUST_ACCT_SITES_ALL HCASA,
                                  HZ_PARTY_SITES HPS,
                                  HZ_LOCATIONS HLA
                            WHERE OKHAB.CONTRACT_NUMBER =X.CONTRACT_NUMBER
                               and hcsua.SITE_USE_ID=okhab.BILL_TO_SITE_USE_ID
and hcasa.CUST_ACCT_SITE_ID = hcsua.CUST_ACCT_SITE_ID
and hps.PARTY_SITE_ID = hcasa.PARTY_SITE_ID
and HLA.LOCATION_ID = hps.LOCATION_ID) CITY,
                              ( SELECT DISTINCT    HLA.ADDRESS1
                                  || HLA.ADDRESS2
                                  || HLA.ADDRESS3
                                  || HLA.CITY
                                  || HLA.STATE
                             FROM OKC_K_HEADERS_ALL_B OKHAB,
                                  HZ_CUST_SITE_USES_ALL HCSUA,
                                  HZ_CUST_ACCT_SITES_ALL HCASA,
                                  HZ_PARTY_SITES HPS,
                                  HZ_LOCATIONS HLA
                            WHERE OKHAB.CONTRACT_NUMBER =X.CONTRACT_NUMBER
                               and hcsua.SITE_USE_ID=okhab.BILL_TO_SITE_USE_ID
and hcasa.CUST_ACCT_SITE_ID = hcsua.CUST_ACCT_SITE_ID
and hps.PARTY_SITE_ID = hcasa.PARTY_SITE_ID
and HLA.LOCATION_ID = hps.LOCATION_ID) SHIP_TO, X.SITE_INCHARGE_NAME, X.CONTACT_DETAILS,
                 X.MACHINE_MODEL, X.START_DATE, X.END_DATE,
                 (SELECT RT.NAME FROM RA_TERMS RT ,okc_k_headers_all_b a
                    WHERE A.PAYMENT_TERM_ID=RT.TERM_ID
                    AND a.ID = X.ID) PAYMENT_TERMS,
                 (select LAST_NAME ||' '|| FIRST_NAME from PER_ALL_PEOPLE_F where person_id =
                   ( select PERSON_ID from jtf_rs_salesreps where salesrep_id = NVL(X.OBJECT1_ID1,salesrep_id)))
                                                            SALES_PERSON_NAME,
                 X.AMOUNT, X.CLE_ID FROM (
SELECT DISTINCT HR.NAME AS OPERATING_UNIT,
                                  OKHAB.CONTRACT_NUMBER AS CONTRACT_NUMBER,I.CLE_ID,
                                  OKHAB.PAYMENT_TERM_ID,OK.OBJECT1_ID1,
                                  OLDV.CUSTOMER_ACCOUNT_NAME CUSTOMER_NAME,
                                  OKHAB.ID,
                                  OKLB.ATTRIBUTE1 SITE_INCHARGE_NAME,
                                  OKLB.ATTRIBUTE2 CONTACT_DETAILS,
                                  MTL.DESCRIPTION MACHINE_MODEL,
                                  NULL SERIAL_NUMBER,
                                  (TO_CHAR (OAL.START_DATE,'DD-MON-RRRR ') )START_DATE,
                                    OAL.END_DATE END_DATE,
                                  OKLB.PRICE_NEGOTIATED AS AMOUNT
                  FROM            OKC_K_HEADERS_ALL_B OKHAB,
                                  OKC_K_LINES_B OKLB,
                                  HR_OPERATING_UNITS HR,
                                  OKS_LINE_DETAILS_V OLDV,
                                  OKS_AUTH_LINES_V OAL,
                                  OKC_K_ITEMS I,
--                                  CSI_ITEM_INSTANCES CII,
                                  MTL_SYSTEM_ITEMS_B MTL,
--                                  CSI_HZPTY_ADDRESSES_V CA,
                                  OKC_CONTACTS OK,
                                  HZ_PARTY_SITE_USES HZP
                            WHERE HR.ORGANIZATION_ID = OKHAB.AUTHORING_ORG_ID
                              AND OKLB.DNZ_CHR_ID = OKHAB.ID
                              AND OLDV.CONTRACT_ID = OKHAB.ID
                              AND OAL.CLE_ID = OKLB.CLE_ID
                              AND I.CLE_ID = OKLB.ID
                              AND I.OBJECT1_ID2 = TO_CHAR(MTL.ORGANIZATION_ID)
                              AND I.OBJECT1_ID1 = TO_CHAR(MTL.INVENTORY_ITEM_ID)
                              AND OKHAB.INV_ORGANIZATION_ID =
                                                           MTL.ORGANIZATION_ID
                              AND MTL.ITEM_TYPE IS NOT NULL
and ok.DNZ_CHR_ID = NVL(OKHAB.ID , OK.dnz_chr_id)
AND ok.SALES_GROUP_ID IS NOT NULL
                              AND OKHAB.CONTRACT_NUMBER =
                                     NVL (:CONTRACT_NUMBER,
                                          OKHAB.CONTRACT_NUMBER
                                         )
                              AND OKHAB.ORG_ID = NVL (:P_ORG_ID, OKHAB.ORG_ID)
                              AND TRUNC (OAL.END_DATE)
                                     BETWEEN NVL (TO_DATE (:P_START_DATE),
                                                  TRUNC (OAL.END_DATE)
                                                 )
                                         AND NVL (TO_DATE (:P_END_DATE),
                                                  TRUNC (SYSDATE)
                                                 )
                                                 )X
                                                  WHERE X.CUSTOMER_NAME IS NOT NULL
                                                  ORDER BY X.CLE_ID ASC
                                                 )XX  
                                                )
                                                ORDER BY SR_NO