Table Lock - Query in Oracle APPS

 SELECT client_identifier,

       module,

       action,

       s.*

  FROM v$session s

 WHERE sid IN (SELECT session_id

                 FROM dba_dml_locks

                WHERE owner = 'AR' AND name = 'AR_ADJUSTMENTS_ALL')

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'