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'

AP Distribution 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,
       CAST (TO_CHAR (TO_DATE (gjh.posted_date, 'DD-MM-YY'), 'YYYYMMDD') AS NUMBER
            ) AS 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,
       aida.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.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 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_INV_DIST'
   AND xdl.source_distribution_id_num_1 = aida.invoice_distribution_id
   AND xdl.event_id = xe.event_id
   AND xe.application_id = xte.application_id
   AND gjh.period_name = 'OCT-14'
   AND glcc.segment4 = '20600'

AR Adjustments Query: AR 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.gl_code_combinations glc,
               apps.fnd_flex_values fv,
               apps.fnd_flex_values_tl fvt
         WHERE glc.code_combination_id = gjl.code_combination_id
           AND glc.segment4 = fv.flex_value
           AND fv.flex_value_set_id = 1002653
           AND fv.flex_value_id = fvt.flex_value_id) code_combo_desc,
       gjh.posted_date posted_on_dt, gjh.je_source, gjh.je_category,
       gjb.NAME je_batch_name, gjh.NAME document_name, '' je_seq_name,
       '' je_seq_num, gjl.je_line_num, gjl.description je_line_desc,
       xal.entered_cr global_cr, xal.entered_dr global_dr,
       xal.currency_code global_cur, ac.customer_name vendor_customer,
       rcta.trx_number transaction_number, rcta.trx_date transaction_date,
       xal.accounting_class_code transaction_type, xal.accounted_cr local_cr,
       xal.accounted_dr local_dr, gl.currency_code local_cur,
       (NVL (xal.accounted_dr, 0) - NVL (xal.accounted_cr, 0)
       ) transaction_amount,
       gl.currency_code transaction_curr_code, 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.gl_je_headers gjh,
       apps.gl_je_lines gjl,
       apps.gl_import_references gir,
       xla.xla_ae_lines xal,
       xla.xla_ae_headers xah,
       apps.gl_code_combinations glcc,
       xla.xla_transaction_entities xte,
       apps.ar_adjustments_all ada,
       apps.ra_customer_trx_all rcta,
       apps.gl_ledgers gl,
       apps.gl_balances gb,
       apps.ar_customers ac,
       apps.gl_je_batches gjb
 WHERE 1 = 1
   AND gjh.je_header_id = gjl.je_header_id
   AND gjl.je_header_id = gir.je_header_id
   AND gjl.je_line_num = gir.je_line_num
   AND gir.gl_sl_link_id = xal.gl_sl_link_id
   AND gir.gl_sl_link_table = xal.gl_sl_link_table
   AND xal.ae_header_id = xah.ae_header_id
   AND xal.application_id = xah.application_id
   AND xal.code_combination_id = glcc.code_combination_id
   AND xte.entity_id = xah.entity_id
   AND xte.entity_code = 'ADJUSTMENTS'
   AND xte.ledger_id = gl.ledger_id
   AND xte.application_id = 222
   AND NVL (xte.source_id_int_1, -99) = ada.adjustment_id
   AND ada.customer_trx_id = rcta.customer_trx_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 gb.ledger_id = gl.ledger_id
   AND gjh.je_batch_id = gjb.je_batch_id
   AND rcta.bill_to_customer_id = ac.customer_id(+)
   AND gjh.period_name IN ('NOV-14')
   AND glcc.segment4 = '20331'
   AND gjh.je_source = 'Receivables'

AR Transactions Query: AR XLA GL

SELECT glcc.segment1 company, glcc.segment2 LOCATION,
       glcc.segment3 cost_center, glcc.segment4 ACCOUNT,
       glcc.segment5 product, glcc.segment6 channel, glcc.segment7 project,
       (SELECT flex_value || ' ' || fvt.description
          FROM apps.gl_code_combinations glc,
               apps.fnd_flex_values fv,
               apps.fnd_flex_values_tl fvt
         WHERE glc.code_combination_id = gjl.code_combination_id
           AND glc.segment4 = fv.flex_value
           AND fv.flex_value_set_id = 1002653
           AND fv.flex_value_id = fvt.flex_value_id) code_combo_desc,
       gjh.posted_date posted_on_dt, gjh.je_source, gjh.je_category,
       gjb.NAME je_batch_name, gjh.NAME journal_name, '' je_seq_name,
       '' je_seq_num, gjl.je_line_num je_line, gjl.description je_line_descr,
       xal.entered_cr global_cr, xal.entered_dr global_dr,
       xal.currency_code global_cur, ac.customer_name vendor_customer,
       rcta.trx_number transaction_num, rcta.trx_date transaction_date,
       xal.accounting_class_code transaction_type, xal.accounted_cr local_cr,
       xal.accounted_dr local_dr, gl.currency_code local_cur,
       (NVL (xal.accounted_dr, 0) - NVL (xal.accounted_cr, 0)
       ) transaction_amount,
       gl.currency_code transaction_curr_code, 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.gl_je_headers gjh,
       apps.gl_je_lines gjl,
       apps.gl_import_references gir,
       xla.xla_ae_lines xal,
       xla.xla_ae_headers xah,
       apps.gl_code_combinations glcc,
       xla.xla_transaction_entities xte,
       apps.ra_customer_trx_all rcta,
       apps.gl_ledgers gl,
       apps.gl_balances gb,
       apps.ar_customers ac,
       apps.gl_je_batches gjb
 WHERE 1 = 1
   AND gjh.je_header_id = gjl.je_header_id
   AND gjl.je_header_id = gir.je_header_id
   AND gjl.je_line_num = gir.je_line_num
   AND gir.gl_sl_link_id = xal.gl_sl_link_id
   AND gir.gl_sl_link_table = xal.gl_sl_link_table
   AND xal.ae_header_id = xah.ae_header_id
   AND xal.application_id = xah.application_id
   AND xal.code_combination_id = glcc.code_combination_id
   AND xte.entity_id = xah.entity_id
   AND xte.entity_code = 'TRANSACTIONS'
   AND xte.ledger_id = gl.ledger_id
   AND xte.application_id = xal.application_id
   AND NVL (xte.source_id_int_1, -99) = rcta.customer_trx_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 gjh.je_batch_id = gjb.je_batch_id
   AND rcta.bill_to_customer_id = ac.customer_id(+)
   AND gjh.period_name = 'NOV-14'
   AND glcc.segment4 = '20330'
   AND gjh.je_source = 'Receivables'
--and GJH.name = 'NOV-14 Credit Memos GBP'

AR Receipt Query: AR XLA GL

SELECT glcc.segment1 company, glcc.segment2 LOCATION,
       glcc.segment3 cost_center, glcc.segment4 ACCOUNT,
       glcc.segment5 product, glcc.segment6 channel, glcc.segment7 project,
       (SELECT flex_value || ' ' || fvt.description
          FROM apps.gl_code_combinations glc,
               apps.fnd_flex_values fv,
               apps.fnd_flex_values_tl fvt
         WHERE glc.code_combination_id = gjl.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, gjh.je_source, gjh.je_category,
       gjb.NAME je_batch_name, gjh.NAME journal_name, '' je_seq_name,
       '' je_seq_num, gjl.je_line_num je_line, gjl.description je_line_descr,
       xal.entered_cr global_cr, xal.entered_dr global_dr,
       xal.currency_code global_cur, ac.customer_name vendor_customer,
       acra.receipt_number transaction_num,
       acra.receipt_date transaction_date,
       xal.accounting_class_code transaction_type, xal.accounted_cr local_cr,
       xal.accounted_dr local_dr, gl.currency_code local_cur,
       (NVL (xal.accounted_dr, 0) - NVL (xal.accounted_cr, 0)
       ) transaction_amount,
       gl.currency_code transaction_curr_code, 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.ar_cash_receipts_all acra,
       xla.xla_transaction_entities xte,
       xla.xla_ae_lines xal,
       xla.xla_ae_headers xah,
       apps.gl_code_combinations glcc,
       apps.gl_import_references gir,
       apps.gl_je_lines gjl,
       apps.gl_je_headers gjh,
       apps.gl_ledgers gl,
       apps.gl_balances gb,
       apps.ar_customers ac,
       apps.gl_je_batches gjb
 WHERE 1 = 1
   AND acra.customer_site_use_id = ac.customer_id(+)
   AND xte.ledger_id = gl.ledger_id
   AND xte.entity_code = 'RECEIPTS'
   AND NVL (xte.source_id_int_1, -99) = acra.cash_receipt_id
   AND xte.application_id = xal.application_id
   AND xte.entity_id = xah.entity_id
   AND xal.ae_header_id = xah.ae_header_id
   AND xal.application_id = xah.application_id
   AND xal.code_combination_id = glcc.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 gjl.je_header_id = gjh.je_header_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 gjh.je_batch_id = gjb.je_batch_id
   AND glcc.segment4 = '20330'
   AND gjh.period_name = 'NOV-14'
   AND gjh.je_source = 'Receivables'
--and ACRA.CASH_RECEIPT_ID = 273042246

AR Receipt Distribution Query: AR XLA GL

SELECT glcc.segment1 co, glcc.segment2 loc, glcc.segment3 cc,
       glcc.segment4 acct, glcc.segment5 prod, glcc.segment6 chan,
       glcc.segment7 proj, '' "CODECOMBO desc", xal.accounting_date,
       gjh.je_source, gjh.je_category CATEGORY, gjb.NAME batch,
       gjh.NAME "JOURNAL name", '' "JE SEQ name", '' "JE SEQ NUM",
       gjl.je_line_num "JE LINE", gjl.description "JE LINE DESCR",
       gjl.accounted_cr gl_cr, gjl.accounted_dr gl_dr, '' "VENDOR/CUSTOMER",
       acra.receipt_number "TRANSACTION NUM",
       xal.accounting_class_code "TRANSACTION TYPE", xal.accounted_cr xla_cr,
       xal.accounted_dr xla_dr, gjh.period_name 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.gl_je_headers gjh,
       apps.gl_je_batches gjb,
       apps.gl_balances gb,
       apps.gl_ledgers gl,
       apps.gl_je_lines gjl,
       apps.gl_import_references gir,
       apps.gl_code_combinations glcc,
       apps.xla_ae_lines xal,
       apps.xla_ae_headers xah,
       apps.xla_distribution_links xdl,
       apps.ar_distributions_all ada,
       apps.ar_cash_receipt_history_all acrha,
       apps.ar_cash_receipts_all acra
 WHERE 1 = 1
   AND gjh.ledger_id = gl.ledger_id
   AND gjh.je_batch_id = gjb.je_batch_id
   AND gjl.je_header_id = gjh.je_header_id
   AND gjl.je_header_id = gir.je_header_id
   AND gjl.je_line_num = gir.je_line_num
   AND gjl.code_combination_id = glcc.code_combination_id
   AND gir.gl_sl_link_id = xal.gl_sl_link_id
   AND gir.gl_sl_link_table = xal.gl_sl_link_table
   AND xal.ae_header_id = xah.ae_header_id
   AND xal.ae_header_id = xdl.ae_header_id
   AND xal.application_id = xah.application_id
   AND xal.ae_line_num = xdl.ae_line_num
   AND xal.application_id = xdl.application_id
   AND xdl.source_distribution_type(+) = 'AR_DISTRIBUTIONS_ALL'
   AND xdl.source_distribution_id_num_1(+) = ada.line_id
   AND ada.source_id = acrha.cash_receipt_history_id
   AND acrha.cash_receipt_id = acra.cash_receipt_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 glcc.segment4 = '13010'
   AND gjh.period_name = 'OCT-14'
   AND gl.NAME = 'Amazon.com, Inc.'
   AND gjh.je_source = 'Receivables'
--and gjh.je_category = 'Receipts'
--and GJH.name = 'OCT-14 Receipts USD'

Report Builder Components

Report Builder Components

1. Data Model
2. Layout Model
3. Object Navigator
4. Report Triggers
5. Parameter Form
6. Program Units
7. Attached Libraries

Data Model
The Report Editor's Data Model view enables you to define and modify the data model objects for a report.

Data Model Comprises tool Palette which Comes with several Options They are:

1. Select
2. Magnify
3. SQL Query
4. Ref Cursor Query
5. Express Query
6. Summary Column
7. Formula Column
8. Placeholder Column
9. Cross Product
10. Data Link

The tool palette is a set of tools you can use to create and manipulate objects. Click a tool to activate it for a single operation, or double-click a tool to "lock" it for multiple operations. The tools in the tool palette vary depending on the Report Editor view.

Select
The Selector toolbar provides tools to help you select or arrange dimension values that meet your criteria. The Selector toolbar is displayed at the top of the Selector dialog box.

Magnify
To magnify a hard-to-see portion of your report or reduce the image to get a sense of your report's overall layout.

SQL Query
The SQL Query Statement property is a SQL SELECT statement that retrieves data from the database for your report. Enter a valid SELECT statement not to exceed 64K. The upper limit may vary between operating systems All features of the SELECT statement are supported, except the INTO and FOR UPDATE clauses.

In building your SELECT statement, you can do the following:

1. Use the Tables and Columns dialog box
2. Insert comments
3. Insert bind and lexical references
4. Review SQL errors

Required/Optional : Required

Ref Cursor Query
A ref cursor query uses PL/SQL to fetch data. Each ref cursor query is associated with a PL/SQL function that returns a strongly typed ref cursor. The function must ensure that the ref cursor is opened and associated with a SELECT statement that has a SELECT list that matches the type of the ref cursor.

You base a query on a ref cursor when you want to:

1. more easily administer SQL
2. avoid the use of lexical parameters in your reports
3. share data sources with other applications, such as Form Builder
4. encapsulate logic within a subprogram

Summary Column
A summary column performs a computation on another column's data.
Using the Report Wizard or Data Wizard, you can create the following summaries:
sum, average, count, minimum, maximum, % total.

You can also create a summary column manually in the Data Model view, and use the Property Palette to create the following additional summaries: first, last, standard deviation, variance.

Formula Column
A formula column performs a user-defined computation on another column(s) data, including placeholder columns.

Placeholder Column
A placeholder is a column for which you set the data type and value in PL/SQL that you define.

You can set the value of a placeholder column in the following places:
1. The Before Report Trigger, if the placeholder is a report-level column
2. A report-level formula column, if the placeholder is a report-level column
3. A formula in the placeholder's group or a group below it

Cross Product
The Cross Product Group property is the group that contains the source groups of the Horizontal and Vertical Repeating Frames. The cross product group correlates values between one or more groups to produce the data in the matrix.

Values Enter a valid cross product group name.
Applies to matrices
Required/Optional required
Default blank

Data Link
Data links relate the results of multiple queries. A data link (or parent-child relationship) causes the child query to be executed once for each instance of its parent group.
When you create a data link in the Data Model view of your report, Report Builder constructs a clause (as specified in the link's Property Palette) that will be added to the child query's SELECT statement at runtime.

Layout Model
The Report Editor's Layout Model view enables you to define and modify the layout model objects for a report. In this view, objects and their property settings are represented symbolically to highlight their types and relationships.

This is the view where you can design your Report Layout.

Object Navigator
The Object Navigator provides a hierarchical display of all major objects in a report or template, including attached libraries and external queries. Using this view, you can take such actions as select an object, bring up the Property Palette for an object, edit an object's PL/SQL, drag and drop PL/SQL program units, and search for an object by name.

Report Triggers
Report triggers execute PL/SQL functions at specific times during the execution and formatting of your report. Using the conditional processing capabilities of PL/SQL for these triggers, you can do things such as customize the formatting of your report, perform initialization tasks, and access the database.

To create or modify a report trigger, use Report Triggers in the Object Navigator. Report triggers must explicitly return TRUE or FALSE. Report Builder has five global report triggers which are fired in the following sequence.

1. Before Parameter Form trigger
2. After Parameter Form trigger
3. Before Report trigger
4. Between Pages trigger
5. After Report trigger

1. Before Parameter Form trigger
The Before Parameter Form trigger fires before the Runtime Parameter Form is displayed. From this trigger, you can access and change the values of parameters, PL/SQL global variables, and report-level columns. If the Runtime Parameter Form is suppressed, this trigger still fires. Consequently, you can use this trigger for validation of command line parameters.
Definition Level Report
On Failure:
Displays an error message and then returns to the place from which you ran the report.

2. After Parameter Form trigger
The After Parameter Form trigger fires after the Runtime Parameter Form is displayed. From this trigger, you can access parameters and check their values. This trigger can also be used to change parameter values or, if an error occurs, return to the Runtime Parameter Form. Columns from the data model are not accessible from this trigger. If the Runtime Parameter Form is suppressed, the After Parameter Form trigger still fires. Consequently, you can use this trigger for validation of command line parameters or other data.

Definition Level Report
On Failure:
Returns to the Runtime Parameter Form. If the Form is suppressed, then returns to place from which you ran the report.

3. Before Report trigger
The Before Report trigger fires before the report is executed but after queries is parsed and data is fetched.
Definition Level Report
On Failure:
Displays an error message and then returns to the place from which you ran the report.

4. Between Pages trigger
Between Pages trigger fires before each page of the report is formatted, except the very first page. This trigger can be used for customized page formatting. In the Runtime Previewer or Live Previewer, this trigger only fires the first time that you go to a page. If you subsequently return to the page, the trigger does not fire again.
Definition Level report
On Failure:
Displays an error message when you try to go to the page for which the trigger returned FALSE. The Between Pages trigger does not fire before the first page.

5. After Report trigger
The After Report trigger fires after you exit the Runtime Previewer, or after report output is sent to a specified destination, such as a file, a printer, or a mailid. This trigger can be used to clean up any initial processing that was done, such as deleting tables.
Note, however, that this trigger always fires, whether or not your report completed successfully.
Definition Level Report
On Failure:
Does not affect formatting because the report is done. You can raise a message, though, to indicate that the report did not run correctly

Note: The After-Report trigger does not fire when you are in the Live Previewer.

Layout Model Properties

In this Layout model, we have several Options

Frames:
Frames are used to surround other objects and protect them from being overwritten or pushed by other objects.
For example, a frame might be used to surround all objects owned by a group, to surround column headings, or to surround summaries.
When you default the layout for a report, Report Builder creates frames around report objects as needed; you can also create a frame manually in the Layout Model view.

Repeating Frames
Repeating frames surround all of the fields that are created for a group's columns. The repeating frame prints (is fired) once for each record of the group.
When you default the layout for a report, Report Builder creates repeating frames around fields as needed; you can also create a repeating frame manually in the Layout Model view.

Text
This allows to embed the text in the layout view.

Field
A field is the layout container for each column in the layout. A field is owned by the object surrounding it, typically a repeating frame, unless the field is a summary (in which case it is owned by a frame).

Anchor
Anchors are used to determine the vertical and horizontal positioning of a child object relative to its parent. The end of the anchor with a symbol on it is attached to the parent object...

Parameter Form
The Report Editor's Parameter Form view enables you to create a Runtime Parameter Form for your report. You can select pre-defined system parameters for your form using the Parameter Form Builder, or you can create your own.

Program Units
Program units are packages, functions, or procedures that you can reference from any PL/SQL within the current report.

Stored program units (also known as stored subprograms) can be compiled separately and stored permanently in an Oracle database, ready to be executed. Once compiled and stored in the data dictionary, they are schema objects, which can be referenced by any number of applications connected to that database.
Stored program units offer higher productivity, better performance, memory savings, application integrity, and tighter security. For example, by designing applications around a library of stored procedures and functions, you can avoid redundant coding and increase your productivity.

Stored program units are stored in parsed, compiled form. So, when called, they are loaded and passed to the PL/SQL engine immediately. Also, they take advantage of shared memory. So, only one copy of a program unit need be loaded into memory for execution by multiple users.

Attached Libraries
Attached libraries are external PL/SQL libraries that you have associated with a report or another external library. When an external library is attached, you can reference its packages, functions, and procedures from within your report. For example, if you attached an external library name MYLIB to your report and it contained a function named ADDXY, then you could reference ADDXY from any PL/SQL in the report.

Report Customizations
You can customize business intelligence reports by both content and layout. You also have the option to add columns, filter the data and sort the resulting information. For example, you can add another column for a running total, or insert grand totals and subtotals as you need them. In layout, you can change column headings, report titles and the order of columns. You can also display the data in graphical form, such as bar graphs or pie charts.

Customization is enhancement of new features to the Existing Report Content and Layout depending on the new requirement.

AIM Document Specifications

AIM Document Specifications:

1. Business Process Architecture (BP)	2. Business Requirements Definition (RD)	3. Business Requirements Mapping	4. Application and Technical Architecture (TA)
BP.010 Define Business and Process Strategy BP.020 Catalog and Analyze Potential Changes BP.030 Determine Data Gathering Requirements BP.040 Develop Current Process Model BP.050 Review Leading Practices BP.060 Develop High-Level Process Vision BP.070 Develop High-Level Process Design BP.080 Develop Future Process Model BP.090 Document Business Procedure	RD.010 Identify Current Financial and Operating Structure RD.020 Conduct Current Business Baseline RD.030 Establish Process and Mapping Summary RD.040 Gather Business Volumes and Metrics RD.050 Gather Business Requirements RD.060 Determine Audit and Control Requirements RD.070 Identify Business Availability Requirements RD.080 Identify Reporting and Information Access Requirements	BR.010 Analyze High-Level Gaps BR.020 Prepare mapping environment BR.030 Map Business requirements BR.040 Map Business Data BR.050 Conduct Integration Fit Analysis BR.060 Create Information Model BR.070 Create Reporting Fit Analysis BR.080 Test Business Solutions BR.090 Confirm Integrated Business Solutions BR.100 Define Applications Setup BR.110 Define security Profiles	TA.010 Define Architecture Requirements and Strategy TA.020 Identify Current Technical Architecture TA.030 Develop Preliminary Conceptual Architecture TA.040 Define Application Architecture TA.050 Define System Availability Strategy TA.060 Define Reporting and Information Access Strategy TA.070 Revise Conceptual Architecture TA.080 Define Application Security Architecture TA.090 Define Application and Database Server Archtecture TA.100 Define and Propose Architecture Subsystems TA.110 Define System Capacity Plan TA.120 Define Platform and Network Architecture TA.130 Define Application Deployment Plan TA.140 Assess Performance Risks TA.150 Define System Management Procedures
5. Module Design and Build (MD)	6. Data Conversion (CV)	7. Documentation (DO)	8. Business System Testing (TE)
MD.010 Define Application Extension Strategy MD.020 Define and estimate application extensions MD.030 Define design standards MD.040 Define Build Standards MD.050 Create Application extensions functional design MD.060 Design Database extensions MD.070 Create Application extensions technical design MD.080 Review functional and Technical designs MD.090 Prepare Development environment MD.100 Create Database extensions MD.110 Create Application extension modules MD.120 Create Installation routines	CV.010 Define data conversion requirements and strategy CV.020 Define Conversion standards CV.030 Prepare conversion environment CV.040 Perform conversion data mapping CV.050 Define manual conversion procedures CV.060 Design conversion programs CV.070 Prepare conversion test plans CV.080 Develop conversion programs CV.090 Perform conversion unit tests CV.100 Perform conversion business objects CV.110 Perform conversion validation tests CV.120 Install conversion programs CV.130 Convert and verify data	DO.010 Define documentation requirements and strategy DO.020 Define Documentation standards and procedures DO.030 Prepare glossary DO.040 Prepare documentation environment DO.050 Produce documentation prototypes and templates DO.060 Publish user reference manual DO.070 Publish user guide DO.080 Publish technical reference manual DO.090 Publish system management guide	TE.010 Define testing requirements and strategy TE.020 Develop unit test script TE.030 Develop link test script TE.040 Develop system test script TE.050 Develop systems integration test script TE.060 Prepare testing environments TE.070 Perform unit test TE.080 Perform link test TE.090 perform installation test TE.100 Prepare key users for testing TE.110 Perform system test TE.120 Perform systems integration test TE.130 Perform Acceptance test