EBS Query to get segment structure description of an Chart of Account Code

SELECT

ST.ID_FLEX_STRUCTURE_CODE  "Chart of Account Code"

,SG.ID_FLEX_NUM            "Chart of Account Num"

,SG.SEGMENT_NAME               "Segment Name"

,SG.APPLICATION_COLUMN_NAME    "Column Name"

,SG.FLEX_VALUE_SET_ID          "Value Set Id"

,VS.FLEX_VALUE_SET_NAME

FROM

FND_ID_FLEX_STRUCTURES ST

INNER JOIN FND_ID_FLEX_SEGMENTS SG ON ST.APPLICATION_ID = SG.APPLICATION_ID AND ST.ID_FLEX_CODE = SG.ID_FLEX_CODE AND ST.ID_FLEX_NUM = SG.ID_FLEX_NUM

INNER JOIN FND_FLEX_VALUE_SETS VS ON SG.FLEX_VALUE_SET_ID = VS.FLEX_VALUE_SET_ID

LEFT OUTER JOIN FND_ID_FLEX_SEGMENTS SG1 ON VS.PARENT_FLEX_VALUE_SET_ID = SG1.FLEX_VALUE_SET_ID AND SG.ID_FLEX_NUM = SG1.ID_FLEX_NUM AND SG.APPLICATION_ID = SG1.APPLICATION_ID AND SG.ID_FLEX_CODE = SG1.ID_FLEX_CODE

WHERE

ST.APPLICATION_ID = 101

AND ST.ID_FLEX_CODE = 'GL#'

AND ST.ENABLED_FLAG = 'Y'

and SG.ID_FLEX_NUM =101

ORDER BY 1,2,3;

GL source_id_int_1 Mappings

The source_id_int_1 on the xla_transaction_entities table can be used to join from SLA to related transactions in the sub-ledgers.
There is a very useful table called xla_entity_id_mappings which contains the mapping information.
This covers the mappings used for some common transaction types:

APPLICATION_ID		APPLICATION_NAME	ENTITY_CODE	SOURCE_ID_COL_NAME_1	TRANSACTION_ID_COL_NAME_1	SOURCE_ID_COL_NAME_2	TRANSACTION_ID_COL_NAME_2
200		Payables	AP_INVOICES	SOURCE_ID_INT_1	INVOICE_ID
200		Payables	AP_PAYMENTS	SOURCE_ID_INT_1	CHECK_ID
275		Projects	BUDGETS	SOURCE_ID_INT_1	BUDGET_VERSION_ID
275		Projects	EXPENDITURES	SOURCE_ID_INT_1	EXPENDITURE_ITEM_ID
275		Projects	REVENUE	SOURCE_ID_INT_1	PROJECT_ID	SOURCE_ID_INT_2	DRAFT_REVENUE_NUM
201		Purchasing	PURCHASE_ORDER	SOURCE_ID_INT_1	PO_HEADER_ID
201		Purchasing	REQUISITION	SOURCE_ID_INT_1	REQ_ID
222		Receivables	RECEIPTS	SOURCE_ID_INT_1	CASH_RECEIPT_ID
222		Receivables	TRANSACTIONS	SOURCE_ID_INT_1	CUSTOMER_TRX_ID

For example, for a Revenue in Projects, you could see that accounting info that flows from Projects through to GL would have and source_id_int_1 value related to the PROJECT_ID and a source_id_int_2 value related to the DRAFT_REVENUE_NUM.

Having access to the xla_entity_id_mappings helps take the guess work out of linking source transactions to the sub ledger tables.

-- ##############################################################################

--       SOURCE_ID_INT_1 MAPPINGS

-- ############################################################################*/

     

-- source_id_int_1 mappings

-- for a given application, find out what entity (e.g. AP_INVOICES) and column_name (e.g. INVOICE_ID)

-- found via Application Diagnostics > Subledger Accounting > Setup Diagnostics > Xla Setup Diagnostics Diagnostics Script

 

    SELECT fa.application_id

         , fa.application_short_name app

         , fat.application_name

         , DECODE(fpi.status,'I','Licensed','S','Shared','N','Not Licensed') status

         , fpi.product_version

         , fpi.patch_level

         , xeim.entity_code

         , xeim.source_id_col_name_1

         , xeim.transaction_id_col_name_1

         , xeim.source_id_col_name_2

         , xeim.transaction_id_col_name_2

         , xeim.source_id_col_name_3

         , xeim.transaction_id_col_name_3

         , xeim.source_id_col_name_4

         , xeim.transaction_id_col_name_4

      FROM xla.xla_entity_id_mappings xeim

      JOIN applsys.fnd_application fa ON xeim.application_id = fa.application_id

      JOIN applsys.fnd_application_tl fat ON fa.application_id = fat.application_id

 LEFT JOIN applsys.fnd_product_installations fpi ON fpi.application_id = fa.application_id

     WHERE 1 = 1

       AND xeim.source_id_col_name_1 IS NOT NULL

--       AND fpi.status <> 'N'

  ORDER BY fat.application_name

         , xeim.entity_code;

Query to get GL Account and Account Segment Description

1.Query to get Account Code Combination Description

Code (SQL):

SELECT gl_flexfields_pkg.get_concat_description
                                          (chart_of_accounts_id,
                                           code_combination_id
                                          )
            FROM gl_code_combinations
 

2. Query to get Account Segment Description

Code (SQL):

SELECT gl_flexfields_pkg.get_description_sql
                                     (chart_of_accounts_id,--- chart of account id
                                      1,----- Position of segment
                                      segment1 ---- Segment value
                                     )
FROM gl_code_combinations
 

SQL query to find Open/Close Periods in Oracle Apps R12

Query to find the Gl Set of Books.

To find SET_OF_BOOKS_ID:

SELECT * FROM gl_sets_of_books

Inventory

SELECT DISTINCT opu.name AS operating_unit
                , per.organization_id AS inv_org_id
                , par.organization_code AS inv_org_code
                , org1.name AS Organization_name
                , per.period_name
                , per.period_year
                , flv.meaning AS status
    FROM org_acct_periods per
       , fnd_lookup_values flv
       , mtl_parameters par
       , hr_all_organization_units org1
       , hr_all_organization_units_tl otl
       , hr_organization_information org2
       , hr_organization_information org3
       , hr_operating_units opu
   WHERE 1 = 1
     AND flv.lookup_type(+) = 'MTL_ACCT_PERIOD_STATUS'
     AND flv.enabled_flag(+) = 'Y'
     AND per.organization_id = par.organization_id
     AND flv.lookup_code(+) =
            DECODE (
               NVL (per.period_close_date, SYSDATE)
             , per.period_close_date, DECODE (
                                         per.open_flag
                                       , 'N', DECODE (summarized_flag
                                                    , 'N', 65
                                                    , 66)
                                       , 'Y', 4
                                       , 'P', 2
                                       , 4)
             , 3)
     AND flv.language = 'US'
     AND UPPER (flv.meaning) != 'CLOSED'
     AND per.organization_id = org1.organization_id
     AND org1.organization_id = otl.organization_id
     AND org1.organization_id = org2.organization_id
     AND org1.organization_id = org3.organization_id
     AND org2.org_information_context = 'Accounting Information'
     AND org3.org_information_context = 'CLASS'
     AND org3.org_information1 = 'INV'
     AND org3.org_information2 = 'Y'
     AND org2.org_information3 = opu.organization_id
     AND PER.PERIOD_NAME = '&Period_Name'
     and opu.set_of_books_id = '&SOB'
ORDER BY opu.name
       , per.organization_id;

To Check whether Periods of AP/AR/GL/FA/PO is closed?

  SELECT DISTINCT (SELECT sob.NAME
                     FROM gl_sets_of_books sob
                    WHERE sob.set_of_books_id = a.set_of_books_id)
                     "SOB_Name"
                , a.period_name "Period_Name"
                , a.period_num "Period_Num"
                , a.gl_status "GL_Status"
                , b.po_status "PO_Status"
                , c.ap_status "AP_Status"
                , d.ar_status "AR_Status"
                , e.fa_status "FA_Status"
    FROM (SELECT period_name
               , period_num
               , DECODE (closing_status
                       , 'O', 'Open'
                       , 'C', 'Closed'
                       , 'F', 'Future'
                       , 'N', 'Never'
                       , closing_status)
                    gl_status
               , set_of_books_id
            FROM gl_period_statuses
           WHERE application_id = 101
             AND UPPER (period_name) = UPPER ('&period_name')
             AND set_of_books_id = '&sob') a
       , (SELECT period_name
               , DECODE (closing_status
                       , 'O', 'Open'
                       , 'C', 'Closed'
                       , 'F', 'Future'
                       , 'N', 'Never'
                       , closing_status)
                    po_status
               , set_of_books_id
            FROM gl_period_statuses
           WHERE application_id = 201
             AND UPPER (period_name) = UPPER ('&period_name')
             AND set_of_books_id = '&sob') b
       , (SELECT period_name
               , DECODE (closing_status
                       , 'O', 'Open'
                       , 'C', 'Closed'
                       , 'F', 'Future'
                       , 'N', 'Never'
                       , closing_status)
                    ap_status
               , set_of_books_id
            FROM gl_period_statuses
           WHERE application_id = 200
             AND UPPER (period_name) = UPPER ('&period_name')
             AND set_of_books_id = '&sob') c
       , (SELECT period_name
               , DECODE (closing_status
                       , 'O', 'Open'
                       , 'C', 'Closed'
                       , 'F', 'Future'
                       , 'N', 'Never'
                       , closing_status)
                    ar_status
               , set_of_books_id
            FROM gl_period_statuses
           WHERE application_id = 222
             AND UPPER (period_name) = UPPER ('&period_name')
             AND set_of_books_id = '&sob') d
       , (SELECT fdp.period_name
               , DECODE (fdp.period_close_date, NULL, 'Open', 'Closed')
                    fa_status
               , fbc.set_of_books_id
            FROM fa_book_controls fbc, fa_deprn_periods fdp
           WHERE fbc.set_of_books_id = '&sob'
             AND fbc.book_type_code = fdp.book_type_code
             AND UPPER (fdp.period_name) = UPPER ('&period_name')) e
   WHERE a.period_name = b.period_name(+)
     AND a.period_name = c.period_name(+)
     AND a.period_name = d.period_name(+)
     AND a.period_name = e.period_name(+)
     AND a.set_of_books_id = b.set_of_books_id(+)
     AND a.set_of_books_id = c.set_of_books_id(+)
     AND a.set_of_books_id = d.set_of_books_id(+)
     AND a.set_of_books_id = e.set_of_books_id(+)
ORDER BY 1;

QUERY FOR AR - XLA - GL RELATION IN EBS R12 FOR ADJUSTMENTS GL CATEGORY

  SELECT GL_JE_LINES.JE_HEADER_ID,
  GL_JE_LINES.JE_LINE_NUM,
  AR_ADJUSTMENTS_ALL.SET_OF_BOOKS_ID,
  GL_JE_LINES.PERIOD_NAME,
  GL_SETS_OF_BOOKS.PERIOD_SET_NAME,
  AR_DISTRIBUTIONS_ALL.ORG_ID,
  AR_DISTRIBUTIONS_ALL.SOURCE_ID,
  GL_JE_HEADERS.POSTED_DATE,
  DECODE(AR_ADJUSTMENTS_ALL.TYPE,'CHARGES','Charges Adjustments' ,'FREIGHT','Freight Adjustments' ,'INVOICE','Invoice Adjustments' ,'LINE','Line Adjustments' ,'TAX', 'Tax Adjustments'),
  AR_ADJUSTMENTS_ALL.RECEIVABLES_TRX_ID,
  AR_RECEIVABLES_TRX_ALL.NAME,
  AR_ADJUSTMENTS_ALL.ADJUSTMENT_NUMBER,
  RA_CUSTOMER_TRX_ALL.TRX_DATE,
  AR_DISTRIBUTIONS_ALL.SOURCE_TYPE,
  SUM(AR_DISTRIBUTIONS_ALL.AMOUNT_DR),
  SUM(AR_DISTRIBUTIONS_ALL.AMOUNT_CR),
  SUM(AR_DISTRIBUTIONS_ALL.ACCTD_AMOUNT_DR),
  SUM(AR_DISTRIBUTIONS_ALL.ACCTD_AMOUNT_CR),
  AR_DISTRIBUTIONS_ALL.CODE_COMBINATION_ID,
  AR_DISTRIBUTIONS_ALL.CURRENCY_CODE,
  AR_DISTRIBUTIONS_ALL.THIRD_PARTY_ID,
  HZ_CUST_ACCOUNTS.ACCOUNT_NUMBER,
  SUBSTR(HZ_PARTIES.PARTY_NAME,1,50),
  AR_ADJUSTMENTS_ALL.GL_DATE,
  RA_CUST_TRX_TYPES_ALL.DESCRIPTION,
  RA_CUSTOMER_TRX_ALL1.TRX_NUMBER,
  RA_CUSTOMER_TRX_ALL.CUSTOMER_TRX_ID,
  RA_CUSTOMER_TRX_ALL.TRX_NUMBER,
  RA_CUSTOMER_TRX_ALL.TRX_DATE,
  GL_JE_HEADERS.JE_CATEGORY,
  GL_JE_HEADERS.JE_SOURCE,
  GL_JE_HEADERS.NAME,
  GL_JE_LINES.REFERENCE_1,
  GL_JE_LINES.REFERENCE_3,
  GL_JE_LINES.STATUS,
  GL_JE_LINES.REFERENCE_5
FROM GL.GL_JE_LINES GL_JE_LINES,
  GL.GL_JE_HEADERS GL_JE_HEADERS,
  gl_import_references,
  XLA_AE_LINES,
  xla_ae_headers,
  GL_SETS_OF_BOOKS GL_SETS_OF_BOOKS,
  GL_JE_CATEGORIES_VL,
  XLA_DISTRIBUTION_LINKS,
  ar_distributions_all,
  AR.AR_ADJUSTMENTS_ALL AR_ADJUSTMENTS_ALL,
  AR.RA_CUSTOMER_TRX_ALL RA_CUSTOMER_TRX_ALL,
  AR.RA_CUST_TRX_TYPES_ALL RA_CUST_TRX_TYPES_ALL,
  AR.HZ_CUST_ACCOUNTS HZ_CUST_ACCOUNTS,
  AR.HZ_PARTIES HZ_PARTIES,
  AR.RA_CUSTOMER_TRX_ALL RA_CUSTOMER_TRX_ALL1,
  AR.AR_RECEIVABLES_TRX_ALL AR_RECEIVABLES_TRX_ALL
WHERE (1                      =1)
AND (GL_JE_HEADERS.JE_CATEGORY='Adjustment')
AND GL_JE_HEADERS.je_source   = 'Receivables'
AND (TRUNC(GL_JE_LINES.LAST_UPDATE_DATE) BETWEEN TO_DATE('01-OCT-2013','DD-MON-YYYY') AND NVL(null,TRUNC(SYSDATE+1)) )
AND (GL_JE_HEADERS.JE_HEADER_ID                         =GL_JE_LINES.JE_HEADER_ID)
AND gl_import_references.je_header_id                   = gl_je_lines.je_header_id
AND gl_import_references.je_line_num                    = gl_je_lines.je_line_num
AND (XLA_AE_LINES.GL_SL_LINK_ID                         =gl_import_references.GL_SL_LINK_ID)
AND xla_ae_lines.ae_header_id                           = xla_ae_headers.ae_header_id
AND xla_ae_headers.je_category_name                     = GL_JE_CATEGORIES_VL.je_category_name
AND (GL_JE_LINES.LEDGER_ID                              = GL_SETS_OF_BOOKS.SET_OF_BOOKS_ID)
AND XLA_DISTRIBUTION_LINKS.EVENT_ID                     = xla_ae_headers.EVENT_ID
AND XLA_DISTRIBUTION_LINKS.AE_HEADER_ID                 = xla_ae_headers.AE_HEADER_ID
AND XLA_DISTRIBUTION_LINKS.AE_LINE_NUM                  = xla_ae_lines.ae_line_num
AND XLA_DISTRIBUTION_LINKS.source_distribution_id_num_1 = ar_distributions_all.line_id
AND XLA_DISTRIBUTION_LINKS.source_distribution_type     = 'AR_DISTRIBUTIONS_ALL'
AND ((AR_RECEIVABLES_TRX_ALL.RECEIVABLES_TRX_ID(+)      =AR_ADJUSTMENTS_ALL.RECEIVABLES_TRX_ID)
AND NVL(AR_ADJUSTMENTS_ALL.ORG_ID,-99)                  =NVL(AR_RECEIVABLES_TRX_ALL.ORG_ID(+),-99))
AND (HZ_CUST_ACCOUNTS.PARTY_ID                          =HZ_PARTIES.PARTY_ID)
AND (GL_JE_HEADERS.JE_HEADER_ID                         =GL_JE_LINES.JE_HEADER_ID)
AND ((RA_CUSTOMER_TRX_ALL1.CUSTOMER_TRX_ID(+)           =AR_ADJUSTMENTS_ALL.CHARGEBACK_CUSTOMER_TRX_ID)
AND NVL(RA_CUSTOMER_TRX_ALL1.ORG_ID(+),-99)             =NVL(AR_ADJUSTMENTS_ALL.ORG_ID,-99))
AND (HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID                   =AR_DISTRIBUTIONS_ALL.THIRD_PARTY_ID)
AND ((AR_DISTRIBUTIONS_ALL.SOURCE_ID                    =AR_ADJUSTMENTS_ALL.ADJUSTMENT_ID)
AND NVL(AR_DISTRIBUTIONS_ALL.ORG_ID,                                                    -99)               =NVL(AR_ADJUSTMENTS_ALL.ORG_ID,-99))
AND ((NVL(RA_CUST_TRX_TYPES_ALL.ORG_ID,99)              =NVL(RA_CUSTOMER_TRX_ALL.ORG_ID,-99))
AND RA_CUST_TRX_TYPES_ALL.CUST_TRX_TYPE_ID              =RA_CUSTOMER_TRX_ALL.CUST_TRX_TYPE_ID)
AND ((RA_CUSTOMER_TRX_ALL.CUSTOMER_TRX_ID               =AR_ADJUSTMENTS_ALL.CUSTOMER_TRX_ID)
AND NVL(RA_CUSTOMER_TRX_ALL.ORG_ID,-99)                 =NVL(AR_ADJUSTMENTS_ALL.ORG_ID,-99))
GROUP BY GL_JE_LINES.JE_HEADER_ID,
  GL_JE_LINES.JE_LINE_NUM,
  AR_ADJUSTMENTS_ALL.SET_OF_BOOKS_ID,
  GL_JE_LINES.PERIOD_NAME,
  GL_SETS_OF_BOOKS.PERIOD_SET_NAME,
  AR_DISTRIBUTIONS_ALL.ORG_ID,
  AR_DISTRIBUTIONS_ALL.SOURCE_ID,
  GL_JE_HEADERS.POSTED_DATE,
  DECODE(AR_ADJUSTMENTS_ALL.TYPE,'CHARGES','Charges Adjustments' ,'FREIGHT','Freight Adjustments' ,'INVOICE','Invoice Adjustments' ,'LINE','Line Adjustments' ,'TAX', 'Tax Adjustments'),
  AR_ADJUSTMENTS_ALL.RECEIVABLES_TRX_ID,
  AR_RECEIVABLES_TRX_ALL.NAME,
  AR_ADJUSTMENTS_ALL.ADJUSTMENT_NUMBER,
  RA_CUSTOMER_TRX_ALL.TRX_DATE,
  AR_DISTRIBUTIONS_ALL.SOURCE_TYPE,
  AR_DISTRIBUTIONS_ALL.CODE_COMBINATION_ID,
  AR_DISTRIBUTIONS_ALL.CURRENCY_CODE,
  AR_DISTRIBUTIONS_ALL.THIRD_PARTY_ID,
  HZ_CUST_ACCOUNTS.ACCOUNT_NUMBER,
  SUBSTR(HZ_PARTIES.PARTY_NAME,1,50),
  AR_ADJUSTMENTS_ALL.GL_DATE,
  RA_CUST_TRX_TYPES_ALL.DESCRIPTION,
  RA_CUSTOMER_TRX_ALL1.TRX_NUMBER,
  RA_CUSTOMER_TRX_ALL.CUSTOMER_TRX_ID,
  RA_CUSTOMER_TRX_ALL.TRX_NUMBER,
  RA_CUSTOMER_TRX_ALL.TRX_DATE,
  GL_JE_HEADERS.JE_CATEGORY,
  GL_JE_HEADERS.JE_SOURCE,
  GL_JE_HEADERS.NAME,
  GL_JE_LINES.REFERENCE_1,
  GL_JE_LINES.REFERENCE_3,
  GL_JE_LINES.STATUS,
  GL_JE_LINES.REFERENCE_5;

QUERY FOR AP - XLA - GL RELATION IN EBS R12

SELECT GL_JE_HEADERS.JE_HEADER_ID,

  GL_JE_LINES.JE_LINE_NUM,

  xla_ae_headers.ledger_id,

  GL_JE_LINES.PERIOD_NAME,

  GL_SETS_OF_BOOKS.PERIOD_SET_NAME,

  AP_INVOICE_DISTRIBUTIONS_ALL.ORG_ID,

  xla_ae_lines.SOURCE_ID,

  GL_JE_HEADERS.POSTED_DATE,

  AP_INVOICES_ALL.INVOICE_TYPE_LOOKUP_CODE,

  AP_LOOKUP_CODES.DISPLAYED_FIELD,

  AP_INVOICES_ALL.INVOICE_NUM,

  AP_INVOICES_ALL.INVOICE_DATE,

  SUM(xla_ae_lines.ENTERED_DR),

  SUM(xla_ae_lines.ENTERED_CR),

  SUM(xla_ae_lines.ACCOUNTED_DR),

  SUM(xla_ae_lines.ACCOUNTED_CR),

  GL_JE_LINES.CODE_COMBINATION_ID,

  GL_JE_HEADERS.CURRENCY_CODE,

  PO_VENDORS.SEGMENT1,

  PO_VENDORS.VENDOR_NAME,

  xla_ae_headers.ACCOUNTING_DATE,

  AP_INVOICE_DISTRIBUTIONS_ALL.DISTRIBUTION_LINE_NUMBER,

  AP_INVOICE_DISTRIBUTIONS_ALL.LINE_TYPE_LOOKUP_CODE,

  PO_HEADERS_ALL.SEGMENT1,

  AP_INVOICES_ALL.INVOICE_NUM,

  PO_RELEASES_ALL.RELEASE_NUM,

  RCV_SHIPMENT_HEADERS.RECEIPT_SOURCE_CODE,

  GL_JE_HEADERS.JE_CATEGORY,

  GL_JE_HEADERS.JE_SOURCE,

  GL_JE_HEADERS.NAME,

  GL_JE_LINES.REFERENCE_1,

  GL_JE_LINES.REFERENCE_3,

  GL_JE_LINES.STATUS,

  GL_JE_LINES.REFERENCE_5,

  GL_JE_LINES.LAST_UPDATE_DATE

FROM xla_ae_headers,

  xla_ae_lines,

  AP_ACCOUNTING_EVENTS_ALL,

  AP_INVOICES_ALL,

  AP_LOOKUP_CODES,

  AP_INVOICE_DISTRIBUTIONS_ALL,

  PO_DISTRIBUTIONS_ALL,

  RCV_TRANSACTIONS RCV_TRANSACTIONS1,

  PO_RELEASES_ALL,

  PO_HEADERS_ALL,

  RCV_SHIPMENT_HEADERS,

  GL_JE_CATEGORIES_VL,

  gl_import_references,

  gl_je_lines,

  GL_JE_HEADERS,

  GL_SETS_OF_BOOKS,

  PO_VENDORS

WHERE 1=1

  --AND xla_ae_headers.je_category_name IN ('Payments','Purchase Invoices','Reconciled Payments'

AND GL_JE_HEADERS.JE_HEADER_ID=173521

AND TRUNC(gl_je_lines.last_update_date) BETWEEN TO_DATE('01-MAY-2014','DD-MON-YYYY')

AND TO_DATE('31-MAY-2014','DD-MON-YYYY')

AND xla_ae_lines.ae_header_id                                                                = xla_ae_headers.ae_header_id

AND (AP_ACCOUNTING_EVENTS_ALL.SOURCE_TABLE                                                   = 'AP_INVOICES')

AND (AP_ACCOUNTING_EVENTS_ALL.ACCOUNTING_EVENT_ID                                            =xla_ae_headers.EVENT_ID)

AND (AP_ACCOUNTING_EVENTS_ALL.SOURCE_ID                                                      = AP_INVOICES_ALL.INVOICE_ID)

AND (AP_LOOKUP_CODES.LOOKUP_CODE                                                             =AP_INVOICES_ALL.INVOICE_TYPE_LOOKUP_CODE)

AND (AP_LOOKUP_CODES.LOOKUP_TYPE                                                             ='INVOICE TYPE')

AND (DECODE(XLA_AE_LINES.SOURCE_TABLE,'AP_INVOICE_DISTRIBUTIONS',XLA_AE_LINES.SOURCE_ID,NULL)=AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_DISTRIBUTION_ID(+))

AND (AP_INVOICE_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID                                         =PO_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID(+))

AND (AP_INVOICE_DISTRIBUTIONS_ALL.RCV_TRANSACTION_ID                                         =RCV_TRANSACTIONS1.TRANSACTION_ID(+))

AND (PO_DISTRIBUTIONS_ALL.PO_RELEASE_ID                                                      =PO_RELEASES_ALL.PO_RELEASE_ID(+))

AND (PO_DISTRIBUTIONS_ALL.PO_HEADER_ID                                                       =PO_HEADERS_ALL.PO_HEADER_ID(+))

AND (RCV_TRANSACTIONS1.SHIPMENT_HEADER_ID                                                    =RCV_SHIPMENT_HEADERS.SHIPMENT_HEADER_ID(+))

AND (XLA_AE_HEADERS.JE_CATEGORY_NAME                                                         = GL_JE_CATEGORIES_VL.JE_CATEGORY_NAME)

AND (XLA_AE_LINES.GL_SL_LINK_ID                                                              =gl_import_references.GL_SL_LINK_ID)

AND gl_import_references.je_header_id                                                        = gl_je_lines.je_header_id

AND gl_import_references.je_line_num                                                         = gl_je_lines.je_line_num

AND (GL_JE_HEADERS.JE_HEADER_ID                                                              = GL_JE_LINES.JE_HEADER_ID)

AND (GL_JE_LINES.LEDGER_ID                                                                   =GL_SETS_OF_BOOKS.SET_OF_BOOKS_ID)

AND (XLA_AE_LINES.PARTY_ID                                                                   = PO_VENDORS.VENDOR_ID)

GROUP BY GL_JE_HEADERS.JE_HEADER_ID,

  GL_JE_LINES.JE_LINE_NUM,

  xla_ae_headers.ledger_id,

  GL_JE_LINES.PERIOD_NAME,

  GL_SETS_OF_BOOKS.PERIOD_SET_NAME,

  AP_INVOICE_DISTRIBUTIONS_ALL.ORG_ID,

  xla_ae_lines.SOURCE_ID,

  GL_JE_HEADERS.POSTED_DATE,

  AP_INVOICES_ALL.INVOICE_TYPE_LOOKUP_CODE,

  AP_LOOKUP_CODES.DISPLAYED_FIELD,

  AP_INVOICES_ALL.INVOICE_NUM,

  AP_INVOICES_ALL.INVOICE_DATE,

  GL_JE_LINES.CODE_COMBINATION_ID,

  GL_JE_HEADERS.CURRENCY_CODE,

  PO_VENDORS.SEGMENT1,

  PO_VENDORS.VENDOR_NAME,

  xla_ae_headers.ACCOUNTING_DATE,

  AP_INVOICE_DISTRIBUTIONS_ALL.DISTRIBUTION_LINE_NUMBER,

  AP_INVOICE_DISTRIBUTIONS_ALL.LINE_TYPE_LOOKUP_CODE,

  PO_HEADERS_ALL.SEGMENT1,

  AP_INVOICES_ALL.INVOICE_NUM,

  PO_RELEASES_ALL.RELEASE_NUM,

  RCV_SHIPMENT_HEADERS.RECEIPT_SOURCE_CODE,

  GL_JE_HEADERS.JE_CATEGORY,

  GL_JE_HEADERS.JE_SOURCE,

  GL_JE_HEADERS.NAME,

  GL_JE_LINES.REFERENCE_1,

  GL_JE_LINES.REFERENCE_3,

  GL_JE_LINES.STATUS,

  GL_JE_LINES.REFERENCE_5,

  GL_JE_LINES.LAST_UPDATE_DATE ;