Oracle Cloud ERP – AR to GL Process Flow Techno Functional

Process Flow :

Sales Order –> AR Invoice –> SubLedger –> General Ledger (Journal)

Tables

ra_customer_trx_all
ra_customer_trx_lines_all
ra_cust_trx_line_salesreps_all
ra_cust_trx_line_gl_dist_all

--Header Query 
SELECT
    rcta.trx_number,
    rcta.customer_trx_id,
    (
        SELECT
            party.party_name
        FROM
            jtf_rs_salesreps  sales,
            hz_parties        party
        WHERE
                sales.resource_salesrep_id = rcta.primary_resource_salesrep_id
            AND sales.resource_id = party.party_id
    ) primary_salesrep_name,
    (
        SELECT
            rt.name
        FROM
            ra_terms_vl rt
        WHERE
            rcta.term_id = rt.term_id
    ) payment_terms,
    (
        SELECT
            xle.name
        FROM
            xle_firstparty_information_v xle
        WHERE
            xle.legal_entity_id = rcta.legal_entity_id
    ) AS legal_entity_name,
    batch.name    batch_source_name,
    types.name    cust_trx_type_name,
    hou.name      "BU_NAME"
FROM
    ra_customer_trx_all    rcta,
    hr_operating_units     hou,
    hz_cust_accounts       hcabt /* -- Bill to Site */,
    hz_cust_accounts       hcast /* -- Ship to Site */,
    hz_cust_accounts       hcasoldto /* -- Sold to  */,
    ar_receipt_methods     arm,
    ra_cust_trx_types_all  types,
    ra_batch_sources_all   batch,
    xle_entity_profiles    xep
WHERE
        1 = 1
    AND hcabt.cust_account_id = rcta.bill_to_customer_id
    AND hcast.cust_account_id (+) = rcta.ship_to_customer_id
    AND hcasoldto.cust_account_id (+) = rcta.ship_to_customer_id
    AND arm.receipt_method_id (+) = rcta.receipt_method_id
    AND rcta.cust_trx_type_seq_id = types.cust_trx_type_seq_id
    AND rcta.batch_source_seq_id = batch.batch_source_seq_id
    AND xep.legal_entity_id = rcta.legal_entity_id
    AND xep.legal_entity_id = hou.default_legal_context_id (+)
    AND rcta.trx_number = '50765'

--Lines
SELECT
    rcta.trx_number,
    rctl.customer_trx_line_id                                                                                 customer_trx_line_id,
    rctl.link_to_cust_trx_line_id,
    rctl.line_number,
    rctl.line_type                                                                                            line_type,
    rctl.uom_code,
    rctl.unit_selling_price,
    decode(rctl.line_type, 'TAX', NULL, nvl(rctl.quantity_invoiced, rctl.quantity_credited))                  quantity,
    (
        SELECT
            egsiv.item_number
        FROM
            egp_system_items_vl egsiv
        WHERE
                egsiv.inventory_item_id = rctl.inventory_item_id
            AND ROWNUM = 1
    ) item_number,
    (
        SELECT
            egsiv.description
        FROM
            egp_system_items_vl egsiv
        WHERE
                egsiv.inventory_item_id = rctl.inventory_item_id
            AND ROWNUM = 1
    ) item_name,
    rctl.interface_line_attribute1,
    rctl.interface_line_attribute2,
    rctl.interface_line_attribute3,
    rctl.interface_line_attribute4,
    rctl.tax_rate
FROM
    ra_customer_trx_all        rcta,
    ra_customer_trx_lines_all  rctl
WHERE
        1 = 1
    AND rctl.customer_trx_id = rcta.customer_trx_id
    AND rcta.trx_number = '50765'

--Distribution
SELECT RCTD.*
FROM
    ra_customer_trx_all RCTA
   ,ra_cust_trx_line_gl_dist_all RCTD
   ,ra_customer_trx_lines_all RCTL

WHERE 1=1
  AND rcta.customer_trx_id = RCTL.customer_trx_id
   AND RCTD.customer_trx_id = RCTL.customer_trx_id  (+)
   AND RCTD.customer_trx_line_id   =    RCTL.customer_trx_line_id(+)
and rcta.trx_number='50765'

--AR to SLA to GL
SELECT
    hou.name    "BU_NAME",
    gl.name     ledger_name,
    rcta.trx_number,
    rcta.customer_trx_id,
    gjh.je_source,
    gjh.je_category,
    gjh.name,
    gjh.description,
    gjh.status,
    gjh.date_created,
    gjh.period_name,
    gll.je_line_num,
    gll.entered_dr,
    gll.entered_cr,
    gll.accounted_dr,
    gll.accounted_cr
FROM
    gl_import_references      gir,
    gl_je_headers             gjh,
    gl_je_lines               gll,
    xla_ae_lines              ael,
    xla_ae_headers            aeh,
    xla_events                aea,
    xla_transaction_entities  xte,
    ra_customer_trx_all       rcta,
    gl_ledgers                gl,
    hr_operating_units        hou,
    xle_entity_profiles       xep
WHERE
        1 = 1
    AND gll.je_header_id = gir.je_header_id
    AND gir.je_line_num = gll.je_line_num
    AND ael.gl_sl_link_id = gir.gl_sl_link_id
    AND ael.gl_sl_link_table = gir.gl_sl_link_table
    AND aea.application_id = 222
    AND aeh.application_id = 222
    AND ael.application_id = 222
    AND aea.event_id = aeh.event_id
    AND aeh.ae_header_id = ael.ae_header_id
    AND xte.application_id = 222
    AND aea.application_id = xte.application_id
    AND aea.entity_id = xte.entity_id
    AND xte.source_id_int_1 = rcta.customer_trx_id
    AND rcta.trx_number IN ( '50767' )
    AND gll.je_header_id = gjh.je_header_id
    AND gjh.ledger_id = gl.ledger_id
    AND gl.name = 'US Primary Ledger'
    AND xep.legal_entity_id = rcta.legal_entity_id
    AND xep.legal_entity_id = hou.default_legal_context_id (+)