Sunday, 11 January 2015

AP Pre Payments Query: AP XLA GL

SELECT glcc.segment1 COMPANY,
  glcc.segment2 LOCATION,
  glcc.segment3 COST_CENTER,
  glcc.segment4 ACCOUNT,
  glcc.segment5 PRODUCT_LINE,
  glcc.segment6 CHANNEL,
  glcc.segment7 PROJECT,
  (SELECT flex_value
    || ' '
    || fvt.description
  FROM apps.ap_invoices_all ap1,
    apps.gl_code_combinations glc,
    apps.fnd_flex_values fv,
    apps.fnd_flex_values_tl fvt
  WHERE aia.accts_pay_code_combination_id = glc.code_combination_id
  AND glc.segment4                        = fv.flex_value
  AND fv.flex_value_set_id                = 1002653
  AND ap1.invoice_id                      = aia.invoice_id
  AND fv.flex_value_id                    = fvt.flex_value_id
  ) CODE_COMBO_DESC,
  GJH.POSTED_DATE POSTED_ON_DT,
20141130 MONTH_DT_WID,
  gjh.JE_CATEGORY,
  gjh.je_header_id gl_journal_id,
  gjh.name DOCUMENT_NAME,
  gjh.external_reference acct_je_line_desc,
  XAL.DESCRIPTION JE_LINE_DESC,
  pv.vendor_name vendor_customer,
  PV.SEGMENT1 VENDOR_NUMBER,
  (SELECT PAP.EMPLOYEE_NUMBER
  FROM APPS.PER_ALL_PEOPLE_F PAP
  WHERE PAP.person_id = PV.EMPLOYEE_ID
  AND rownum          =1
  ) Employee_number,
  xal.accounting_class_code transaction_type,
  aia.invoice_num transaction_number,
  '' MOD_TRANSACTION_NUMBER,
  CASE
    WHEN XAL.ENTERED_DR IS NULL
    THEN GJL.ENTERED_DR
    ELSE XAL.ENTERED_DR
  END GLOBAL_DR,
  CASE
    WHEN XAL.ENTERED_cR IS NULL
    THEN gjl.ENTERED_cR
    ELSE XAL.ENTERED_cR
  END GLOBAL_CR,
  XAL.CURRENCY_CODE GLOBAL_CUR,
  CASE
    WHEN XAL.ACCOUNTED_CR IS NULL
    THEN GJL.ACCOUNTED_CR
    ELSE XAL.ACCOUNTED_CR
  END LOCAL_CR,
  CASE
    WHEN XAL.ACCOUNTED_DR IS NULL
    THEN GJL.ACCOUNTED_DR
    ELSE XAL.ACCOUNTED_DR
  END LOCAL_DR,
  APAD.amount TRANSACTION_AMOUNT,
  aia.invoice_currency_code TRANSACTION_CURR_CODE,
  GL.CURRENCY_CODE LOCAL_CUR,
  gjh.period_name fiscal_period,
  (GB.BEGIN_BALANCE_DR - GB.BEGIN_BALANCE_CR) begin_balance,
  ( GB.PERIOD_NET_DR   - GB.PERIOD_NET_CR + GB.PROJECT_TO_DATE_DR - GB.PROJECT_TO_DATE_CR ) END_BALANCE,
  GL.name Ledger_name
FROM APPS.AP_INVOICES_ALL AIA,
  APPS.AP_INVOICE_LINES_ALL AILA,
  APPS.AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
  APPS.AP_PREPAY_APP_DISTS APAD,
apps.xla_events xe,
  APPS.XLA_DISTRIBUTION_LINKS XDL,
  XLA.XLA_TRANSACTION_ENTITIES XTE ,
  XLA.XLA_AE_HEADERS XAH ,
  XLA.XLA_AE_LINES XAL ,
  APPS.GL_CODE_COMBINATIONS GLCC ,
  APPS.GL_IMPORT_REFERENCES GIR ,
  APPS.GL_JE_LINES GJL ,
  apps.gl_je_headers gjh ,
  APPS.AP_SUPPLIERS PV ,
  APPS.GL_LEDGERS GL,
  apps.gl_balances gb
WHERE 1                          =1
AND aia.vendor_id                = pv.vendor_id
AND XTE.ENTITY_ID                = XAH.ENTITY_ID
AND XTE.LEDGER_ID                = GL.LEDGER_ID
AND AIA.INVOICE_ID               = AILA.INVOICE_ID
AND AILA.INVOICE_ID              = AIDA.INVOICE_ID
AND AILA.LINE_NUMBER             = AIDA.INVOICE_LINE_NUMBER
AND aida.line_type_lookup_code   ='PREPAY'
AND aida.invoice_distribution_id = apad.prepay_app_distribution_id
AND NVL(XTE.SOURCE_ID_INT_1,-99) = AIA.INVOICE_ID
AND XTE.APPLICATION_ID           = 200
AND XTE.ENTITY_CODE = 'AP_INVOICES'
AND XAH.AE_HEADER_ID                 = XAL.AE_HEADER_ID
AND XAH.APPLICATION_ID               = XAL.APPLICATION_ID
AND XAL.LEDGER_ID                    = GJH.LEDGER_ID
AND XAL.APPLICATION_ID               = xte.APPLICATION_ID
AND GLCC.CODE_COMBINATION_ID         = GJL.CODE_COMBINATION_ID
AND XAL.GL_SL_LINK_ID                = GIR.GL_SL_LINK_ID
AND XAL.GL_SL_LINK_TABLE             = GIR.GL_SL_LINK_TABLE
AND gir.JE_HEADER_ID                 = gjl.JE_HEADER_ID
AND gir.je_line_num                  = gjl.je_line_num
AND gjh.je_header_id                 = gjl.je_header_id
AND GJH.LEDGER_ID                    = XAL.LEDGER_ID
AND GJH.LEDGER_ID                    = GL.LEDGER_ID
AND GB.CODE_COMBINATION_ID           = GLCC.CODE_COMBINATION_ID
AND GB.PERIOD_NAME                   = GJH.PERIOD_NAME
AND GB.CURRENCY_CODE                 = GL.CURRENCY_CODE
AND XAL.AE_HEADER_ID                 = xdl.AE_HEADER_ID
AND XAL.AE_LINE_NUM                  = xdl.AE_LINE_NUM
AND XDL.SOURCE_DISTRIBUTION_TYPE     = 'AP_PREPAY'
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 = APAD.PREPAY_APP_DIST_ID
AND APAD.AMOUNT                     <>0
AND XDL.EVENT_ID                     = XE.EVENT_ID
AND xe.application_id                = xte.application_id
AND GJH.PERIOD_NAME                  = 'NOV-14'
AND GLCC.SEGMENT4 = '20600'


No comments:

Post a Comment