Oracle EBS R12 – Query to View Consolidated Invoice Details with Individual Transactions

SELECT

    hp.party_name                  AS customer_name,

    aca.account_number             AS customer_account,

    hci.cons_billing_number        AS consolidated_bill_number,

    hci.issue_date                 AS bill_issue_date,

    rct.trx_number                 AS invoice_number,

    cit.transaction_type,

    cit.amount_original,

    cit.tax_original

FROM

    ar_cons_inv_all        hci,

    ar_cons_inv_trx_all    cit,

    hz_cust_accounts       aca,

    hz_parties             hp,

    ra_customer_trx_all    rct

WHERE

    hci.cons_inv_id          = cit.cons_inv_id

    AND hci.customer_id      = aca.cust_account_id

    AND aca.party_id         = hp.party_id

    AND rct.customer_trx_id  = cit.customer_trx_id

    -- AND hci.status        = 'ACCEPTED'        -- Uncomment if needed

    AND rct.trx_number       = '31380885'       -- Change as required

    AND hci.issue_date      >= TRUNC(SYSDATE) - 30

ORDER BY

    hci.issue_date DESC;

Oracle EBS: Query to Link OM Order Source with AR Grouping Rules and Attributes

SELECT 

    hou.name,

    rtsa.name "Order Source/Transaction Source",

    rtsa.description "Description",

    gr1.name grouping_rule_name,

    gr1.description grouping_rule_desc,

    gr1.start_date,

    gr1.end_date,

    al.meaning type,

    from_column_name,

    end_user_column_name

FROM 

    ra_grouping_rules gr1,

    ra_line_ordering_rules ord,

    ra_grouping_trx_types gt,

    ra_group_bys gb,

    ra_group_by_columns gc,

    ar_lookups al,

    fnd_descr_flex_col_usage_vl co,

    ra_batch_sources_all rtsa,

    hr_operating_units hou

WHERE 

    gr1.ordering_rule_id = ord.ordering_rule_id(+)

    AND gr1.grouping_rule_id = gt.grouping_rule_id(+)

    AND gt.grouping_trx_type_id = gb.grouping_trx_type_id(+)

    AND gb.column_id = gc.column_id

    AND al.lookup_type = 'GROUPING_TRX_TYPE'

    AND al.lookup_code = gt.class

    AND co.descriptive_flex_context_code = 'ORDER ENTRY'

    AND 'L.' || co.application_column_name = from_column_name

    AND rtsa.grouping_rule_id = gr1.grouping_rule_id(+)

    AND rtsa.name = 'Order Management'

    AND hou.organization_id = rtsa.org_id

ORDER BY 

    1;

Customer Credit Card Query

 select hca.cust_account_id,
       hca.party_id,
       hcas.party_site_id,
       site_use_id,
       hcsu.org_id,
       hca.account_number,
       hca.account_name,
       hps.party_site_number,
       location bill_location,
       creditcardeo.card_issuer_code      "CARD TYPE",
       creditcardeo.ccnumber              "CARD NUMBER",
       chname,
       trunc(ipiu.start_date)             "VALID FROM",
       trunc(creditcardeo.expirydate)            "EXP DATE",
       expired_flag,
       active_flag,
       hp.party_name                      "OWNER NAME",
       hcsu.location,
       hl.address1,
       hl.address2,
       hl.city,
       hl.state,
       hl.postal_code,
       creditcardeo.description           "CARDHOLDER NAME",
       creditcardeo.invalidation_reason,       
       (select iss.segment_cipher_text
          from apps.iby_fndcpt_tx_extensions extn,
               iby.iby_security_segments     iss
         where extn.instr_assignment_id = ipiu.instrument_payment_use_id
           and extn.instr_code_sec_segment_id = iss.sec_segment_id
           and rownum = 1)                 cvv
from   apps.hz_cust_accounts_all        hca,
       apps.hz_cust_acct_sites_all      hcas,
       apps.hz_cust_site_uses_all       hcsu,
       apps.hz_party_site_uses          hpsu,
       apps.iby_creditcard              creditcardeo,
       apps.iby_pmt_instr_uses_all      ipiu,
       apps.hz_parties                  hp,
       hz_locations hl,            
       hz_party_sites                   hps
where  hca.party_id = hp.party_id
and    hps.party_site_id = hcas.party_site_id
and    hps.location_id= hl.location_id
and    hca.cust_account_id = hcas.cust_account_id
and    hcas.cust_acct_site_id = hcsu.cust_acct_site_id
and    hcas.party_site_id = hpsu.party_site_id
and    hpsu.site_use_type = 'CC_BILLING'
and    creditcardeo.instrid = ipiu.instrument_id
and    ipiu.instrument_type = 'CREDITCARD'
and    creditcardeo.card_owner_id = hp.party_id
and    hcsu.site_use_code = 'BILL_TO'
and    hcsu.location = '36732870'--'36732870';
and    creditcardeo.ccnumber like '%5615'
--and    active_flag='Y'

;

SELECT ipiu.instrument_payment_use_id,

       ipiu.payment_flow,

       ipiu.ext_pmt_party_id,

       ipiu.instrument_type,

       ipiu.instrument_id,

       ic.ccnumber,

       ic.card_issuer_code,

       ipiu.payment_function,

       DECODE (ipiu.order_of_preference,

               1,

               'Yes',

               'NO'

              ) primary_flag,

       ipiu.start_date,

       ipiu.end_date,

       ic.expirydate,

       ipiu.debit_auth_flag,

       ipiu.debit_auth_method,

       ipiu.debit_auth_reference,

       ipiu.debit_auth_begin,

       ipiu.debit_auth_end

  FROM iby_pmt_instr_uses_all ipiu,

       iby_creditcard ic,

       iby_external_payers_all iep

 WHERE ipiu.instrument_id    = ic.instrid

   AND ipiu.ext_pmt_party_id = iep.ext_payer_id

   AND iep.cust_account_id   = 71950349

   AND iep.acct_site_use_id  = 36732870

;

Oracle Standard Package for Order Totals

 Purpose:

Sometimes we need to show line-wise or complete order totals (Basic / Tax / Tax+Basic) in reports.
Oracle provides a standard package to calculate these values based on the parameters passed.

Package Name

oe_totals_grp.get_order_total

Illustration with Example

Suppose 1 order has 3 lines, and each line has taxes attached:

• Header ID: 23096

Line ID	Line Value (Basic)	Tax Value	Total Value (Basic + Tax)
36492	1,045.00	87.52	1,132.52
36494	505.00	42.30	547.30
36495	1,750.00	146.56	1,896.56

✅ With oe_totals_grp.get_order_total, you can:

• Get line-level totals (basic, tax, total).

• Get order-level totals (sum across all lines).

  Calculate Line wise Tax

    select nvl(oe_totals_grp.get_order_total (HEADER_ID, LINE_ID, 'TAXES'),0) from dual;
 
 For Ex:
 
    select nvl(oe_totals_grp.get_order_total (23096, 36492, 'TAXES'),0) LINE_Tax from dual;
   
    Output : LINE_Tax=87.52
 
 
  Calculate Order Taxes (All Lines)


    select nvl(oe_totals_grp.get_order_total (HEADER_ID, NULL, 'TAXES'),0) from dual;
 
 
   Ex:
 
   select nvl(oe_totals_grp.get_order_total (23096, null, 'TAXES'),0) Order_Tax from dual;
 
 
   Output : Order_Tax: 276.38
 
 
 
 
 
    Calculate Line wise Value (Without Tax)

    select nvl(oe_totals_grp.get_order_total (HEADER_ID, LINE_ID, 'LINES'),0) from dual;
 
 
    Ex:
 
      select nvl(oe_totals_grp.get_order_total (23096, 36492, 'LINES'),0)  LINE_BASIC from dual;
   
   
      Output : LINE_BASIC=1045
   
 
  Calculate All Lines Total (Without Tax)

    select nvl(oe_totals_grp.get_order_total (HEADER_ID, NULL, 'LINES'),0) from dual;
 
 
     Ex:
 
   select nvl(oe_totals_grp.get_order_total (23096, null, 'LINES'),0) Order_Basic from dual;
 
 
   Output : Order_Basic: 3300
 
 
 
 
   Calculate Line wise Value ( With Tax)

    select nvl(oe_totals_grp.get_order_total (HEADER_ID, LINE_ID, 'ALL'),0) from dual;
 
 
     Ex:
 
      select nvl(oe_totals_grp.get_order_total (23096, 36492, 'ALL'),0)  LINE_TOTAL from dual;
   
   
      Output : LINE_TOTAL=1132.52
 


    
  Calculate Order Total Value (With Tax)

    select nvl(oe_totals_grp.get_order_total (HEADER_ID, NULL, 'ALL'),0) from dual;
 
     Ex:
 
   select nvl(oe_totals_grp.get_order_total (23096, null, 'ALL'),0) Order_Total from dual;

Customer Tax Registration - EBS Query

             SELECT DISTINCT 
hp.party_type,
hp.party_name,
hca.account_name,
hp.party_number,
hps.party_site_number,
hca.account_number,
hcsu.location,
hcsu.site_use_code,
zr.registration_id,
                        zr.registration_number,
                        zr.registration_status_code,
                        zr.tax_regime_code,
                        zr.tax,
                        zr.attribute1           country_cod,
                        zr.party_tax_profile_id,
hca.cust_account_id,
hp.party_id,
hps.party_site_id,
hcsu.site_use_id,
hps.location_id,
hcsa.cust_acct_site_id,
hl.address1,
hl.city,
hl.state,
hl.country,
hl.postal_code,
hcsa.bill_to_flag,
hcsu.org_id,
hcsu.status site_use_status,
hcsa.status acct_site_status,
hps.status party_site_status,
hca.creation_date    account_creation,
hcsa.creation_date    acct_site_creation,
hcsu.creation_date    site_use_creation
FROM hz_parties             hp,
hz_party_sites         hps,
hz_locations           hl,
hz_cust_accounts_all   hca,
hz_cust_acct_sites_all hcsa,
hz_cust_site_uses_all  hcsu,
zx.zx_party_tax_profile tax_prof,
zx_registrations     zr
WHERE hp.party_id = hps.party_id
AND hps.location_id = hl.location_id
AND hp.party_id = hca.party_id
AND hcsa.party_site_id = hps.party_site_id
AND tax_prof.party_id = hps.party_site_id
AND hcsu.cust_acct_site_id = hcsa.cust_acct_site_id
AND hca.cust_account_id = hcsa.cust_account_id
AND tax_prof.party_type_code = 'THIRD_PARTY_SITE'
AND zr.party_tax_profile_id = tax_prof.party_tax_profile_id
AND hp.status = 'A'
AND hca.status = 'A'
--AND hcsa.status = 'A'
--AND hcsu.status = 'A'
          AND location IN('47840945','47840946','142842','142843')--'52042893','50354303','50343674','51957873')
--and hp.party_id=9860;
--and party_number='391490'; 
-- AND hca.account_number='435080'--'24830389';  
ORDER BY 3, 8 DESC;

SELECT zr.creation_date,
      -- zr.created_by,
       hp.party_number,
       hp.party_name,
       hca.account_number,
       hca.account_name,
       hps.party_site_number,
    --   zr.registration_id,
       zr.registration_number,
       zr.registration_status_code,
       zr.tax_regime_code,
       zr.tax,
       (select distinct location FROM 
            hz_cust_acct_sites_all hcsa,
hz_cust_site_uses_all  hcsu
WHERE hcsu.cust_acct_site_id = hcsa.cust_acct_site_id
AND hcsa.party_site_id = hps.party_site_id
AND hcsu.site_use_code='SHIP_TO') ship_to_location ,
       (select distinct location FROM 
            hz_cust_acct_sites_all hcsa,
hz_cust_site_uses_all  hcsu
WHERE hcsu.cust_acct_site_id = hcsa.cust_acct_site_id
AND hcsa.party_site_id = hps.party_site_id
AND hcsu.site_use_code='BILL_TO') bill_to_location,
   zr.attribute1     country_cod,
       zr.party_tax_profile_id,
       zptp.party_id          
  FROM zx_party_tax_profile  zptp,
       zx_registrations      zr,
       hz_party_sites        hps,
       hz_parties            hp,
       hz_cust_accounts      hca
 WHERE     zr.party_tax_profile_id = zptp.party_tax_profile_id
       AND hp.party_id = hps.party_id
       AND hp.party_id = hca.party_id
       AND hps.party_site_id = zptp.party_id
       AND zptp.party_type_code = 'THIRD_PARTY_SITE'
       AND hca.account_number = '50354303';
   

Item - Category Query

      SELECT 
    msi.segment1 AS Item_Code,  
    msi.DESCRIPTION AS Item_Desc,  
    mcs.CATEGORY_SET_NAME,  
    mck.CONCATENATED_SEGMENTS,  
    mck.SEGMENT1, mck.SEGMENT2, mck.SEGMENT3, mck.SEGMENT4, mck.SEGMENT5,  
    mck.SEGMENT6, mck.SEGMENT7, mck.SEGMENT8, mck.SEGMENT9, mck.SEGMENT10,  
    mck.SEGMENT11, mck.SEGMENT12, mck.SEGMENT13, mck.SEGMENT14, mck.SEGMENT15,  
    mck.SEGMENT16, mck.SEGMENT17, mck.SEGMENT18, mck.SEGMENT19, mck.SEGMENT20  
FROM 
    mtl_system_items_b msi,  
    mtl_item_categories mic,  
    MTL_CATEGORIES_KFV mck,  
    MTL_CATEGORY_SETS_TL mcs  
WHERE 
    msi.INVENTORY_ITEM_ID = mic.INVENTORY_ITEM_ID  
    AND msi.ORGANIZATION_ID = mic.ORGANIZATION_ID  
    AND mic.CATEGORY_ID = mck.CATEGORY_ID  
    AND mcs.CATEGORY_SET_ID = mic.CATEGORY_SET_ID  
    AND mcs.LANGUAGE = 'US' 
    AND msi.ORGANIZATION_ID = :P_Org_id
    AND mcs.CATEGORY_SET_NAME='Tax Classification'
    AND msi.segment1='23542345234'

Oracle Order Management - Tax Calculation Stages in Sales Orders

 

Tax calculation in Oracle Order Management can be controlled at the order header level during any of the following events:

1. Order Entry
2. Order Booking
3. Invoicing

This is configured when Order Types are created in the Order Management Transaction Types window. The specific event to calculate tax for an entire order is specified during setup.

Navigation Steps to Configure:

1. Navigate to:
   India Local Order Management → Oracle Order Management → Setup → Transaction Types → Define → Finance Tab

2. In the Finance Tab, specify the event at which tax should be calculated for the entire order.

Order Entry: Tax Calculation at Entry

• When tax calculation is set to Order Entry, tax is calculated as each order line is entered.
• This is commonly used in scenarios where businesses require the user performing order entry to view the total order amount (including tax) to provide a quote to the customer.
• To include tax in the commitment applied amount, ensure that the tax event is set to Entry.

Order Booking: Tax Calculation at Booking

• When tax calculation is set to Order Booking, tax is calculated on each of the booked order lines.
• This option is suitable for businesses that require tax visibility for booked orders, but wish to improve order entry efficiency by avoiding tax calculation at entry.

Invoicing: Tax Calculation at Invoicing

• When tax calculation is set to Invoicing, no tax calculations occur within Order Management.
• Instead, tax calculation is performed in Oracle Receivables at the time the order or order line is invoiced.