Wednesday, 11 February 2015

Group Shipments functionality of Autocreate in R12.2



This document provides information on Group Shipments functionality for Auto created Purchase Documents.


New feature in  R12.2 + related to Group Shipments of Autocreated purchasing documents.


Purchase orders generated through ASCP, WIP, OM, Inventory, and EAM requisitions can have multiple distributions per one shipment line. Currently, if one distribution line related to the shipment is cancelled, it results in the cancellation of all the distribution lines of that shipment. For example, one shipment is attached to multiple distributions. When one WIP job is cancelled, then the single shipment along with all the distributions gets cancelled.
With this release, Oracle Purchasing provides flexibility to buyers to decide whether they want to group shipments or not. Buyers can now use the new ‘Group Shipments’ check box available in the Buyer Work Center Demand Workbench Document Builder region, Purchasing Options page, and AutoCreate Documents window. If buyers select this check box, then shipments are grouped. Otherwise, Oracle Purchasing creates a separate shipment for each requisition line. Distributions are not grouped.


The Group Shipments check impacts only the requisitions from Oracle Purchasing and Oracle iProcurement. Buyers can decide whether to group shipments or not for requisitions from Purchasing and iProcurement. Shipments are not grouped even if the Group Shipments check box is selected for ASCP, WIP, OM, Inventory, and EAM requisitions.

   For manual requisitions, shipment grouping is done based on requisition lines and not on requisition distributions.
   The PO line level grouping is based on the values defined for the following profiles:
        PO: Use Need-by Date for Default Autocreate Grouping
        PO: Use Ship-to for Default Autocreate grouping.

Here in the below exercises we will look into 3 different scenarios for Group Shipments functionality for Auto created Purchase Documents.
Scenario 1: Requisitions from WIP – with Group Shipment checked
Create 3 Discrete Jobs. (Ex: OSP JOB1, OSP JOB2, OSP JOB3)
Navigation: WIP Responsibility > Discrete > Discrete Jobs.
Release all 3 Jobs.


Requisition import is triggered for all the 3 Jobs.
Check the requisitions created from the Discrete Jobs.
Purchasing Responsibility > Autocreate
Query by Item as below.
Find the Requisition lines created from Discrete Jobs. Check the “Group Shipments” Checkbox.
Click the Automatic Button. Enter the Supplier details.
Create the PO from the 3 requisition lines.
Check the line #1. The shipments are not grouped.

Scenario 2: Requisitions from Purchasing – with Group Shipment checked
Create 2 requisitions from Requisition Form for same item details. Approve the same.
Purchasing Responsibility > Requisitions > Requisitions.
Query these requisition lines in Autocreate Form. Check the ‘Group Shipments’ checkbox.
Purchasing responsibility > Autocreate.
Enter the Supplier Details & Click on Create.
PO is created from the 2 requisition lines.

Check the shipments are grouped into one shipment and 2 distributionss

Scenario 3: Requisitions from Purchasing – with Group Shipment Unchecked
Query the 2 similar requisition lines in Autocreate Form. Uncheck the ‘Group Shipments’ check box.
Purchasing Responsibility > Autocreate.
Enter the supplier details and click on create button.
PO is created from the 2 requisition lines.


Check the shipments are NOT grouped. 2 PO shipments corresponding to 2 requisition lines are created.






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'


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'