Query for CHART OF ACCOUNTS in Oracle EBS R12

 

Query for CHART OF ACCOUNTS in Oracle EBS R12

SELECT GCC.CODE_COMBINATION_ID,
       GCC.SEGMENT1||'.'||
       GCC.SEGMENT2||'.'||
       GCC.SEGMENT3||'.'||
       GCC.SEGMENT4||'.'||
       GCC.SEGMENT5||'.'||
       GCC.SEGMENT6||'.'||
       GCC.SEGMENT7||'.'||
       GCC.SEGMENT8,
       SUBSTR (
          APPS.GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
             GCC.CHART_OF_ACCOUNTS_ID,
             1,
             GCC.SEGMENT1),
          1,
          40)
          SEGMENT1_DESC
,
       SUBSTR (
          APPS.GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
             GCC.CHART_OF_ACCOUNTS_ID,
             2,
             GCC.SEGMENT2),
          1,
          40)
          SEGMENT2_DESC,
       DECODE (
          GCC.SEGMENT3,
          NULL, NULL,
          SUBSTR (
             APPS.GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
                GCC.CHART_OF_ACCOUNTS_ID,
                3,
                GCC.SEGMENT3),
             1,
             40))
          SEGMENT3_DESC,
       DECODE (
          GCC.SEGMENT4,
          NULL, NULL,
          SUBSTR (
             APPS.GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
                GCC.CHART_OF_ACCOUNTS_ID,
                4,
                GCC.SEGMENT4),
             1,
             40))
          SEGMENT4_DESC,
       DECODE (
          GCC.SEGMENT5,
          NULL, NULL,
          SUBSTR (
             APPS.GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
                GCC.CHART_OF_ACCOUNTS_ID,
                5,
                GCC.SEGMENT5),
             1,
             40))
          SEGMENT5_DESC,
       DECODE (
          GCC.SEGMENT6,
          NULL, NULL,
          SUBSTR (
             APPS.GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
                GCC.CHART_OF_ACCOUNTS_ID,
                6,
                GCC.SEGMENT6),
             1,
             40))
          SEGMENT6_DESC,
       DECODE (
          GCC.SEGMENT7,
          NULL, NULL,
          SUBSTR (
             APPS.GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
                GCC.CHART_OF_ACCOUNTS_ID,
                7,
                GCC.SEGMENT7),
             1,
             40))
          SEGMENT7_DESC,
       DECODE (
          GCC.SEGMENT9,
          NULL, NULL,
          SUBSTR (
             APPS.GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
                GCC.CHART_OF_ACCOUNTS_ID,
                8,
                GCC.SEGMENT8),
             1,
             40))
          SEGMENT8_DESC,
       GCC.CHART_OF_ACCOUNTS_ID CHART_OF_ACCOUNTS_ID,
       GCC.ACCOUNT_TYPE
  FROM GL_CODE_COMBINATIONS GCC
  WHERE GCC.CODE_COMBINATION_ID = 2291

Query: Cancelled invoices

 

Query: Cancelled invoices

SELECT
distinct AIA.INVOICE_ID, aia.invoice_num, AIA.INVOICE_AMOUNT,
 AIA.INVOICE_TYPE_LOOKUP_CODE,
DECODE(AP_INVOICES_PKG.GET_APPROVAL_STATUS(AIA.INVOICE_ID,
              AIA.INVOICE_AMOUNT,
     AIA.PAYMENT_STATUS_FLAG,              
    AIA.INVOICE_TYPE_LOOKUP_CODE),
                       'NEVER APPROVED', 'Never Validated',
                       'NEEDS REAPPROVAL', 'Needs Revalidation',
                       'APPROVED',  'Validated',
                       'CANCELLED',      'Cancelled',
                       'AVAILABLE PREPAYMENT', 'Available Prepayment',
                       'AVAILABLE', 'Available') INVOICE_STATUS
FROM AP_INVOICES_ALL AIA
WHERE AIA.INVOICE_TYPE_LOOKUP_CODE = 'AWT'
 AND   AP_INVOICES_PKG.GET_APPROVAL_STATUS(AIA.INVOICE_ID,
                AIA.INVOICE_AMOUNT,
                AIA.PAYMENT_STATUS_FLAG,
                AIA.INVOICE_TYPE_LOOKUP_CODE) = 'NEVER APPROVED'