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.

Price List Query for Item

 SELECT qph.list_header_id,

       qph.name,
       qph.description,
       qphh.start_date_active,
       qphh.currency_code,
       qphh.source_system_code,
       qphh.active_flag,
       qphh.orig_system_header_ref,
       qphh.orig_org_id,
       qphh.global_flag,
       qpl.list_line_id,
       qpl.start_date_active,
       qpl.end_date_active,
       qpl.arithmetic_operator,
       qpl.operand,
       qpl.orig_sys_line_ref,
       qpp.pricing_attribute_id,
       qpp.product_attribute_context,
       qpp.product_attribute,
       qpp.product_attr_value,
       qpp.product_uom_code,
       qpp.comparison_operator_code,
       qpp.orig_sys_pricing_attr_ref,
       mtl.inventory_item_id,
       mtl.segment1,
       mtlc.cross_reference_type,
       mtlc.cross_reference
  FROM apps.qp_list_headers_b       qphh,
       apps.qp_list_headers_tl      qph,
       apps.qp_list_lines           qpl,
       apps.qp_pricing_attributes   qpp,
       apps.mtl_system_items_b      mtl,
       apps.mtl_cross_references_b  mtlc
WHERE     qph.list_header_id = qphh.list_header_id
       AND qph.list_header_id = qpl.list_header_id
       AND qph.list_header_id = qpp.list_header_id
       AND qpl.list_line_id = qpp.list_line_id
       AND to_char(mtl.inventory_item_id) = (qpp.product_attr_value)
      -- AND   mtl.organization_id   = 344 
--       (SELECT UNIQUE master_organization_id FROM   mtl_parameters)
       AND mtl.inventory_item_id = mtlc.inventory_item_id
       AND SYSDATE BETWEEN qpl.start_date_active
                       AND NVL (qpl.end_date_active, SYSDATE)
       AND SYSDATE BETWEEN qphh.start_date_active
                       AND NVL (qphh.end_date_active, SYSDATE)
                       and  qph.name like '%SG%'
                       AND qpl.END_DATE_ACTIVE IS NULL
       AND mtl.segment1 = '9780749172008';

       select * from mtl_system_items_b where segment1='A101708534498'