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'

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'

OTL - Query with PROJECTS, TASKS

   SELECT DISTINCT PP.SEGMENT1              "Project Number",
                  PP.NAME                  PROJECT_NAME,
                  PT.TASK_NUMBER,
                  PT.TASK_NAME,
                  HTA.ATTRIBUTE3,
                  submission_date,
                  DAY.START_TIME,
                  DET.MEASURE,
                  hts.APPROVAL_STATUS,
                  DET.COMMENT_TEXT,
                  papf.employee_number     employee_number,
                  papf.first_name,
                  papf.last_name,
                  papf.middle_names,
                  papf.full_name,
                  sup.first_name           sup_first_name,
                  sup.last_name            sup_last_name,
                  sup.middle_names         sup_middle_name,
                  sup.full_name            sup_full_name
    FROM (SELECT TIME_BUILDING_BLOCK_ID,
                 PARENT_BUILDING_BLOCK_ID,
                 PARENT_BUILDING_BLOCK_OVN,
                 OBJECT_VERSION_NUMBER,
                 MEASURE,
                 RESOURCE_ID,
                 APPROVAL_STATUS,
                 COMMENT_TEXT,
                 TRANSLATION_DISPLAY_KEY
            FROM HXC_TIME_BUILDING_BLOCKS
           WHERE SCOPE = 'DETAIL') DET,
         (SELECT TIME_BUILDING_BLOCK_ID,
                 PARENT_BUILDING_BLOCK_ID,
                 PARENT_BUILDING_BLOCK_OVN,
                 OBJECT_VERSION_NUMBER,
                 START_TIME,
                 APPROVAL_STATUS,
                 RESOURCE_ID
            FROM HXC_TIME_BUILDING_BLOCKS
           WHERE SCOPE = 'DAY') DAY,
         (SELECT TIME_BUILDING_BLOCK_ID,
                 START_TIME,
                 APPROVAL_STATUS,
                 OBJECT_VERSION_NUMBER,
                 COMMENT_TEXT,
                 RESOURCE_ID
            FROM HXC_TIME_BUILDING_BLOCKS
           WHERE SCOPE = 'TIMECARD') TC,
         HXC_TIME_ATTRIBUTE_USAGES HTAU,
         HXC_TIME_ATTRIBUTES      HTA,
         PA_PROJECTS_ALL          PP,
         PA_TASKS                 PT,
         hxc_timecard_summary     hts,
         per_all_people_f         papf,
         per_all_assignments_f    paaf,
         per_all_people_f         sup
   WHERE     DAY.PARENT_BUILDING_BLOCK_ID = TC.TIME_BUILDING_BLOCK_ID
         AND DAY.PARENT_BUILDING_BLOCK_OVN = TC.OBJECT_VERSION_NUMBER
         AND DET.PARENT_BUILDING_BLOCK_ID = DAY.TIME_BUILDING_BLOCK_ID
         AND DET.PARENT_BUILDING_BLOCK_OVN = DAY.OBJECT_VERSION_NUMBER
         AND TRUNC (DAY.START_TIME) BETWEEN '1-MAY-2024' AND '31-MAY-2024'
         AND DAY.RESOURCE_ID = (SELECT EMPLOYEE_ID from FND_USER WHERE USER_NAME='XXX')
         AND TC.OBJECT_VERSION_NUMBER =
             (SELECT MAX (OBJECT_VERSION_NUMBER)
                FROM HXC_TIME_BUILDING_BLOCKS
               WHERE     SCOPE = 'TIMECARD'
                     AND TIME_BUILDING_BLOCK_ID = TC.TIME_BUILDING_BLOCK_ID)
         AND DET.OBJECT_VERSION_NUMBER =
             (SELECT MAX (OBJECT_VERSION_NUMBER)
                FROM HXC_TIME_BUILDING_BLOCKS
               WHERE     SCOPE = 'DETAIL'
                     AND TIME_BUILDING_BLOCK_ID = DET.TIME_BUILDING_BLOCK_ID)
         AND DET.TIME_BUILDING_BLOCK_ID = HTAU.TIME_BUILDING_BLOCK_ID
         AND DET.OBJECT_VERSION_NUMBER = HTAU.TIME_BUILDING_BLOCK_OVN
         AND HTAU.TIME_ATTRIBUTE_ID = HTA.TIME_ATTRIBUTE_ID
         AND HTA.ATTRIBUTE1 = TO_CHAR (PP.PROJECT_ID)
         AND HTA.ATTRIBUTE2 = TO_CHAR (PT.TASK_ID)
         AND HTA.ATTRIBUTE5 = 'ST'
         AND HTS.timecard_id = TC.TIME_BUILDING_BLOCK_ID
         -- papf, paaf, sup
         AND TC.resource_id = papf.person_id
         AND SYSDATE BETWEEN papf.effective_start_date
                         AND papf.effective_end_date
         AND papf.person_id = paaf.person_id
         AND SYSDATE BETWEEN paaf.effective_start_date
                         AND paaf.effective_end_date
         AND paaf.supervisor_id = sup.person_id
         AND SYSDATE BETWEEN sup.effective_start_date
                         AND sup.effective_end_date
-- tms
ORDER BY 7;

EBS - Form currently using user details for submission

 WITH vs AS (
     SELECT
         ROWNUM rnum,
         inst_id,
         sid,
         serial#,
         status,
         username,
         last_call_et,
         command,
         machine,
         osuser,
         module,
         action,
         resource_consumer_group,
         client_info,
         client_identifier,
         type,
         terminal,
         sql_id,
         sql_child_number
     FROM
         gv$session
)
SELECT
     vs.inst_id,
     vs.sid,
     serial#       serial,
     vs.sql_id,
     vs.sql_child_number,
     vs.username   "Username",
     CASE
         WHEN vs.status = 'ACTIVE' THEN last_call_et
         ELSE NULL
     END "Seconds in Wait",
     (
         SELECT
             command_name
         FROM
             v$sqlcommand
         WHERE
             command_type = vs.command
     ) "Command",
     vs.machine    "Machine",
     vs.osuser     "OS User",
     lower(vs.status) "Status",
     vs.module     "Module",
     vs.action     "Action",
     vs.resource_consumer_group,
     vs.client_info,
     vs.client_identifier
FROM
     vs
WHERE
     vs.username IS NOT NULL
     AND nvl(vs.osuser,'x') <> 'SYSTEM'
     AND vs.type <> 'BACKGROUND'
     AND module = 'e:ONT:frm:OEXOEORD'
ORDER BY
     1,
     2,
     3;

EBS - Order Type associated with Workflow details query

select * from hr_operating_units;

exec MO_GLOBAL.SET_POLICY_CONTEXT('S',12762);

SELECT OWA.order_type_id,
       ol.name,
       oe.meaning item_type_code,
       wf.display_name line_flow
  FROM wf_activities_vl wf,
       oe_workflow_assignments OWA,
       oe_line_types_v ol,
       oe_lookups oe
 WHERE     OWA.line_type_id = ol.line_type_id
       AND oe.lookup_type(+) = 'WF_ASSIGN_ITEM_TYPES'
       AND oe.lookup_code(+) = OWA.item_type_code
       AND wf.name = OWA.process_name
       AND wf.item_type = 'OEOL'
       AND wf.version =
              (SELECT MAX (version)
                 FROM wf_activities_vl wf1
                WHERE wf1.name = wf.name AND wf1.item_type = 'OEOL');

EBS Order Holds details and release responsibility

  SELECT ooh.order_number, 
                ooh.ordered_date,
                ooh.flow_status_code,
                ooh.credit_card_approval_code,
                ooh.order_category_code,
                ohd.name              "Hold Name",
                ohs.released_flag,
                ohr.release_reason_code,
                ohr.creation_date     "Hold Release Date",
                ooh.payment_type_code
           FROM apps.oe_order_headers_all ooh,
                apps.oe_order_lines_all   ool,
                apps.oe_order_holds_all  ohld,
                apps.oe_hold_sources_all ohs,
                apps.oe_hold_definitions ohd,
                apps.oe_hold_releases    ohr
          WHERE  1=1--   TRUNC (ooh.ordered_date) BETWEEN '10-NOV-15' AND '14-NOV-15'
              --  AND ooh.order_category_code = 'RETURN'
                AND ohld.header_id(+) = ooh.header_id
                AND ohs.hold_source_id(+) = ohld.hold_source_id
                AND ohd.hold_id(+) = ohs.hold_id
                AND ohr.hold_release_id(+) = ohs.hold_release_id
                AND ool.header_id=ooh.header_id
                AND ohld.line_id(+) = ool.line_id
                and ohs.released_flag='N'
                AND ooh.order_number in ('12345')
               -- and ohld.line_id=111338124
       ORDER BY ooh.order_number

SELECT frt.responsibility_name,

       authorized_action_code,

       oha.start_date_active,

       oha.end_date_active

  FROM apps.oe_hold_authorizations oha,

       apps.fnd_responsibility_tl frt,

       apps.oe_hold_definitions ohd

 WHERE 1 = 1

   AND oha.hold_id = ohd.hold_id                           

   AND ohd.NAME ='Deferred Invoice Hold'

   AND oha.responsibility_id = frt.responsibility_id

   AND oha.application_id = frt.application_id

   AND LANGUAGE = 'US'

   AND authorized_action_code = 'REMOVE'

EBS Order details along with workflow

 SELECT hr.name ou,h.order_number, 
       h.flow_status_code header_Status,
       l.flow_status_code line_status,
       CUST_ACCT.ACCOUNT_NUMBER,ship_su.location SHIP_TO_LOCATION, bill_su.location  INVOICE_TO_LOCATION,
       ship_ps.party_site_number ship_to_site_number,
       bill_ps.party_site_number bill_to_site_number,
       ship_from_org.organization_code
       SHIP_FROM,
       L.LINE_ID,
       L.ORG_ID,
       L.HEADER_ID,
       s.NAME source_name,
       L.LINE_NUMBER,
       L.ORDERED_ITEM,
       L.ORDERED_QUANTITY,
       l.cancelled_quantity,
       l.unit_selling_price,
       l.unit_list_price,
       l.tax_value,
       l.tax_line_value,
       L.INVENTORY_ITEM_ID,
       (select user_name from fnd_user where user_id=l.created_by) creater,
       (select user_name from fnd_user where user_id=l.last_updated_by) updater,
       L.CREATION_DATE LINE_CREATION,
       L.LAST_UPDATE_DATE LINE_UPDATE,
       (select sh.order_number||'->'||sl.line_number||'->'||sl.ordered_item||'->'||sl.ordered_quantity from oe_order_headers_all sh, oe_order_lines_all sl where sl.header_id=sh.header_id and sl.line_id=l.reference_line_id)
        original_order,
       L.REFERENCE_LINE_ID,
       L.REFERENCE_HEADER_ID,
       L.LINE_CATEGORY_CODE,
       lt.name  LINE_TYPE,
       PARTY.PARTY_NAME  SOLD_TO,
       L.SUBINVENTORY
           SUBINVENTORY,
           ship_loc.location_id,
       ship_loc.address1
           SHIP_TO_ADDRESS1,
       DECODE (ship_loc.city, NULL, NULL, ship_loc.city || ', ')
       || DECODE (ship_loc.state,
                  NULL, ship_loc.province || ', ',
                  ship_loc.state || ', ')
       || DECODE (ship_loc.postal_code,
                  NULL, NULL,
                  ship_loc.postal_code || ', ')
       || DECODE (ship_loc.country, NULL, NULL, ship_loc.country)
           SHIP_TO_ADDRESS5,
       bill_loc.address1
           INVOICE_TO_ADDRESS1,
          DECODE (bill_loc.city, NULL, NULL, bill_loc.city || ', ')
       || DECODE (bill_loc.state,
                  NULL, bill_loc.province || ', ',
                  bill_loc.state || ', ')
       || DECODE (bill_loc.postal_code,
                  NULL, NULL,
                  bill_loc.postal_code || ', ')
       || DECODE (bill_loc.country, NULL, NULL, bill_loc.country)
           INVOICE_TO_ADDRESS5,
       H.order_type_id,
       H.ordered_date,
       L.return_reason_code,
       L.ordered_item_id,
       L.item_identifier_type,
       L.booked_flag,
       L.cancelled_flag,
       L.open_flag,
       l.sold_from_org_id,
       l.shipping_instructions,
       l.flow_status_code,
       l.SHIPPABLE_FLAG,L.LINE_TYPE_ID,
       L.SOLD_TO_ORG_ID,
       L.SHIP_FROM_ORG_ID,
       L.SHIP_TO_ORG_ID,
       L.INVOICE_TO_ORG_ID,
        L.ORIG_SYS_DOCUMENT_REF,
       L.ORIG_SYS_LINE_REF,
       l.ATTRIBUTE16  "Return entered by" ,
       l.ATTRIBUTE19  "Return to Customer Order Ref" ,
       l.ATTRIBUTE12  "Return Id" ,
       l.ATTRIBUTE4  "Return Line ID" ,
       l.ATTRIBUTE1  "Return Receipt Date" ,
       l.ATTRIBUTE3  "Return Credit Denial Reason1" ,
       l.ATTRIBUTE4  "Return Credit Denial Reason2" ,
       l.RETURN_ATTRIBUTE4  "Certiport Return Line ID",
       l.ATTRIBUTE18  PO_NUMBER,
       l.attribute10,
       l.context,
       cursor(SELECT wias.item_key,
         wpa.activity_name,
         wias.activity_status,
         wias.activity_result_code,
         wias.assigned_user,
         wias.begin_date,
         wias.end_date
    FROM WF_ITEM_ACTIVITY_STATUSES wias, WF_PROCESS_ACTIVITIES wpa
   WHERE     wias.process_activity = wpa.instance_id(+)
         AND wpa.process_item_type = wias.item_type
         AND wias.end_date IS NULL
         AND wias.item_type='OEOL'
         AND wias.item_key =to_char(l.line_id)
ORDER BY begin_date DESC) workflow_line_status,
 cursor(SELECT wias.item_key,
         wpa.activity_name,
         wias.activity_status,
         wias.activity_result_code,
         wias.assigned_user,
         wias.begin_date,
         wias.end_date
    FROM WF_ITEM_ACTIVITY_STATUSES wias, WF_PROCESS_ACTIVITIES wpa
   WHERE     wias.process_activity = wpa.instance_id(+)
         AND wpa.process_item_type = wias.item_type
         AND wias.end_date IS NULL
         AND wias.item_type='OEOH'
         AND wias.item_key =to_char(l.header_id)
ORDER BY begin_date DESC) workflow_header_status
  FROM mtl_parameters           ship_from_org,
       hz_cust_site_uses_all    ship_su,
       hz_party_sites           ship_ps,
       hz_locations             ship_loc,
       hz_cust_acct_sites_all   ship_cas,
       hz_cust_site_uses_all    bill_su,
       hz_party_sites           bill_ps,
       hz_locations             bill_loc,
       hz_cust_acct_sites_all   bill_cas,
       hz_parties               party,
       hz_cust_accounts         cust_acct,
       oe_order_headers_all         H,
       oe_order_lines_all       L,
       oe_order_sources s,
       OE_TRANSACTION_TYPES_TL  lt,
       hr_operating_units hr
 WHERE     L.line_type_id = LT.transaction_type_id
       AND LT.language = USERENV ('LANG')
       AND L.sold_to_org_id = cust_acct.cust_account_id(+)
       AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID(+)
       AND L.ship_from_org_id = ship_from_org.organization_id(+)
       AND l.ship_to_org_id = ship_su.site_use_id(+)
       AND ship_su.cust_acct_site_id = ship_cas.cust_acct_site_id(+)
       AND ship_cas.party_site_id = ship_ps.party_site_id(+)
       AND ship_loc.location_id(+) = ship_ps.location_id
       AND l.invoice_to_org_id = bill_su.site_use_id(+)
       AND bill_su.cust_acct_site_id = bill_cas.cust_acct_site_id(+)
       AND bill_cas.party_site_id = bill_ps.party_site_id(+)
       AND bill_loc.location_id(+) = bill_ps.location_id
       AND L.header_id = H.header_id
       AND h.order_source_id=s.order_source_id
       AND hr.organization_id=L.ORG_ID
     --  AND  L.REFERENCE_LINE_ID =98618576
      -- AND L.REFERENCE_HEADER_ID                                                     
   --    and L.LINE_ID=99176446       
    -- and L.ORDERED_ITEM in('9780132156554')
  --     and h.creation_date>sysdate-5
      -- and l.header_id=31494876
      -- and l.line_id=99176446       
       and h.order_number ='12727162'--
       order by line_id
       
          select ooh.order_number
       ,ool.ordered_item
       ,ool.ordered_quantity
       ,ooh.flow_status_code header_status                   
       ,ool.flow_status_code line_status
       ,prha.segment1 requisition
       ,poh.segment1 po_number
       ,poh.closed_code po_status
       ,pll.quantity
       ,pll.quantity_received
       ,pll.closed_code po_shipment_status
from apps.oe_order_headers_all                   ooh
    ,apps.oe_order_lines_all                     ool
    ,apps.oe_drop_ship_sources                   odss
    ,apps.po_requisition_headers_all             prha
    ,apps.po_headers_all                         poh
    ,apps.po_lines_all                           pol
    ,apps.po_line_locations_all                  pll              
where ool.header_id = ooh.header_id
and   odss.header_id = ooh.header_id
and   odss.line_id = ool.line_id
and   prha.requisition_header_id = odss.requisition_header_id
and   poh.po_header_id = odss.po_header_id
and   pol.po_line_id = odss.po_line_id
and   pol.po_header_id = poh.po_header_id
and   pll.po_line_id = pol.po_line_id
and   ooh.order_number = '12727162'--'12727161';

SELECT ooh.order_number

              ,ool.ordered_item

              ,ool.line_id

              ,ool.ordered_quantity

              ,ool.shipped_quantity

              ,ool.invoiced_quantity

              ,wdd.delivery_detail_id

              ,wnd.delivery_id

              ,rctl.interface_line_attribute1 order_number

              ,rctl.interface_line_attribute3

              ,rctl.interface_line_attribute6 order_line_id

              ,rct.org_id

              ,rct.creation_date invoice_creation_date

              ,rct.last_update_date invoice_update_date

              ,ooh.creation_date order_creation_date

              ,ooh.last_update_date order_update_date          

             ,trx_number

              ,rctl.quantity_ordered

              ,rct.interface_header_context

  FROM oe_order_headers_all ooh

             ,oe_order_lines_all ool

             ,wsh_delivery_details wdd

             ,wsh_new_deliveries wnd

             ,wsh_delivery_assignments wda

             ,ra_customer_trx_all rct

             ,ra_customer_trx_lines_all rctl

 WHERE ooh.header_Id=ool.header_id

      AND wdd.source_header_id=ooh.header_id

      AND wdd.delivery_detail_Id=wda.delivery_detail_id

      AND wda.delivery_id=wnd.delivery_id

      AND rctl.interface_line_attribute1=to_char(ooh.order_number)

      AND rctl.interface_line_attribute6=to_char(ool.line_id)

      AND rctl.interface_line_attribute3=to_char(wnd.delivery_id)

     AND rctl.customer_trx_id=rct.customer_trx_id

      AND rct.interface_header_context='ORDER ENTRY'

      AND ooh.order_number in ('12727161');

SELECT TRX.TRX_NUMBER         INVOICE_NUMBER,

       TRX.TRX_DATE           INVOICE_DATE,

       HZA.ACCOUNT_NUMBER     CUSTOMER_NUMBER,

       RAT.NAME               TERM_NAME,

       ARPS.DUE_DATE          INVOICE_DUE_DATE,

       trx.creation_date invoice_creation_date

       ,trx.last_update_date invoice_update_date

       ,ooh.creation_date order_creation_date

       ,ooh.last_update_date order_update_date  

  FROM RA_CUSTOMER_TRX_ALL       TRX,

       oe_order_headers_all      OOH,

       HZ_CUST_ACCOUNTS          HZA,

       RA_TERMS                  RAT,

       AR_PAYMENT_SCHEDULES_ALL  ARPS

 WHERE     to_char(TRX.ct_reference) = to_char(OOH.order_number)

       AND OOH.SOLD_TO_ORG_ID = HZA.CUST_ACCOUNT_ID

       AND RAT.TERM_ID = TRX.TERM_ID

       AND ARPS.CUSTOMER_TRX_ID(+) = TRX.CUSTOMER_TRX_ID

       AND ooh.order_number = '12727161';

   SELECT ooh.order_number

              ,ool.ordered_item

              ,ool.line_id

              ,ool.ordered_quantity

              ,ool.shipped_quantity

              ,ool.invoiced_quantity

              ,wdd.delivery_detail_id

              ,wnd.delivery_id

              ,ooh.creation_date order_creation_date

              ,ooh.last_update_date order_update_date          

  FROM oe_order_headers_all ooh

             ,oe_order_lines_all ool

             ,wsh_delivery_details wdd

             ,wsh_new_deliveries wnd

             ,wsh_delivery_assignments wda

 WHERE ooh.header_Id=ool.header_id

      AND wdd.source_header_id=ooh.header_id

      AND wdd.delivery_detail_Id=wda.delivery_detail_id

      AND wda.delivery_id=wnd.delivery_id

      and wnd.name='526398067'

select request_id,interface_status ,interface_line_attribute6 line_id,a.* from ra_interface_lines_all a   where sales_order='8044839' --req id 124077809

                     and interface_line_attribute6 in (133090970,133090946)

select * from ra_interface_errors_all where MESSAGE_TEXT='A Party Tax Profile does not exist for this Party.'

and org_id=16916 --INTERFACE_LINE_ID in (select a.INTERFACE_LINE_ID from ra_interface_lines_all a where sales_order='8044839')

Random number generation

     select round(dbms_random.value(5,10)) ||'   +   '||round(dbms_random.value(1,10))||'  = '        
        output
       from   dual
       connect by level <= 26

GL - Segment descriptions

Select GCC.CONCATENATED_SEGMENTS ACCOUNT,

       GL_FLEXFIELDS_PKG.GET_CONCAT_DESCRIPTION(GCC.CHART_OF_ACCOUNTS_ID,

                                                GCC.CODE_COMBINATION_ID) DESCRIPTION

  from GL_CODE_COMBINATIONS_KFV GCC

 where segment1 IN ('9000', '9560')

   and segment2 IN ('8002', '9519')

SELECT *
  FROM fnd_flex_key_seg_vset_v
 WHERE application_name = 'General Ledger'
   AND id_flex_name = 'Accounting Flexfield'

select * from fnd_application_vl where application_id = 101

select * from fnd_id_flex_segments WHERE application_id = 101

SELECT LEDGER_ID,
       LEDGER_NAME,
       LEDGER_SHORT_NAME,
       LEGAL_ENTITY_NAME,
       gllv.LOCATION_CODE          "LOCATION",
       LOCATION_DESCRIPTION,
       LEDGER_CATEGORY_CODE,
       CURRENCY_CODE,
       CHART_OF_ACCOUNTS_ID,
       PERIOD_SET_NAME,
       ACCOUNTED_PERIOD_TYPE,
       hr_loc.country              "Country Code",
      -- hr_loc.location_code        "Location Code",
       glev.flex_segment_value     "Company Code"
  FROM gl_ledger_le_v          gllv,
       hr_locations_all        hr_loc,
       gl_legal_entities_bsvs  glev
WHERE     hr_loc.location_id = gllv.location_id
       AND LEDGER_NAME LIKE 'PL%'
       AND glev.LEGAL_ENTITY_ID = gllv.LEGAL_ENTITY_ID

Range of Transaction number

SELECT COUNTROW, MIN(Lvl) starting_val, MAX(Lvl) end_value

  FROM (SELECT LEVEL Lvl,

               ceil(ROW_NUMBER() OVER(order by 1 desc) / 5) countrow

          FROM DUAL

        CONNECT BY LEVEL < 21)

 GROUP BY COUNTROW

 order by 1

select min(trx_number) lb,max(trx_number) ub

from (select trx_number,trx_number- row_number() over(order by trx_number) rn
from ra_customer_trx_all a , ra_cust_trx_types_all b,fnd_user c
where printing_option = 'PRI'
and a.cust_trx_type_id = B.CUST_TRX_TYPE_ID
and a.org_id = b.org_id
and b.type = 'INV'
and c.user_id = a.last_updated_by
and A.PRINTING_PENDING = 'N'
and a.org_id = 3842
--       and printing_count >= 1
and trx_date between '01-JAN-2024' and '30-JAN-2024')
group by rn
order by 1;    

Concurrent Jobs completed through Scheduled

  SELECT b.user_name,

         a.USER_CONCURRENT_PROGRAM_NAME,

         a.Program,

         ROUND (

             (  (a.actual_completion_date - a.actual_start_date)

              * 24

              * 60

              * 60

              / 60),

             2)

             AS Process_time,

         a.request_id,

         TO_CHAR (a.request_date, 'DD-MON-YY HH24:MI:SS')

             AS Request_Date,

         TO_CHAR (a.actual_start_date, 'DD-MON-YY HH24:MI:SS')

             AS Actual_Start_Date,

         TO_CHAR (a.actual_completion_date, 'DD-MON-YY HH24:MI:SS')

             AS Actual_Completion_Date,

         ROUND ((a.actual_completion_date - a.request_date) * 24 * 60 * 60, 2)

             AS end_to_end,

         a.phase_code,

         a.status_code,

         a.argument_text,

         a.completion_text

    FROM apps.fnd_conc_req_summary_v a, apps.fnd_user b

   WHERE     a.requested_by = b.user_id

         AND (a.ACTUAL_COMPLETION_DATE) BETWEEN '<start date>' AND '<end date>'

         AND a.status_code = 'C'

         AND (   (    is_sub_request = 'Y'

                  AND request_type = 'P'

                  AND request_id <> priority_request_id

                  AND EXISTS

                          (SELECT 1

                             FROM fnd_concurrent_requests fc

                            WHERE     fc.request_id = a.priority_request_id

                                  AND fc.parent_request_id != -1))

              OR (    is_sub_request = 'N'

                  AND request_id = priority_request_id

                  AND parent_request_id != -1))

         AND b.user_name NOT LIKE 'XX__%' ESCAPE '_'

ORDER BY request_id DESC;

Oracle PL/SQL - PL SQL Cursor CURSOR Expressions

A CURSOR expression returns a nested cursor.

 It has this syntax:

 CURSOR ( subquery )

You can use a CURSOR expression in a SELECT statement or pass it to a function.

 You cannot use a cursor expression with an implicit cursor.

 

The following code declares and defines an explicit cursor for a query that includes a cursor expression.

/* Formatted on 3/16/2023 1:41:03 PM (QP5 v5.388) */
CREATE TABLE emp
(
    empid             NUMBER (6),
    first_name        VARCHAR2 (20),
    last_name         VARCHAR2 (25),
    email             VARCHAR2 (25),
    phone_number      VARCHAR2 (20),
    hire_date         DATE,
    job_id            VARCHAR2 (10),
    salary            NUMBER (8, 2),
    commission_pct    NUMBER (2, 2),
    manager_id        NUMBER (6),
    department_id     NUMBER (4)
);
/* Formatted on 3/16/2023 1:41:11 PM (QP5 v5.388) */
INSERT INTO emp
     VALUES (100,
             'Steven',
             'King',
             'SKING',
             '123.123.4567',
             TO_DATE ('17-JUN-1987', 'dd-MON-yyyy'),
             'CODER',
             24000,
             NULL,
             NULL,
             10);
INSERT INTO emp
     VALUES (200,
             'Joe',
             'Lee',
             'abc',
             '123.123.9999',
             TO_DATE ('17-JUN-1980', 'dd-MON-yyyy'),
             'TESTER',
             25000,
             NULL,
             NULL,
             20);

/* Formatted on 3/16/2023 1:41:17 PM (QP5 v5.388) */
CREATE TABLE departments
(
    department_id      NUMBER (4),
    department_name    VARCHAR2 (30) CONSTRAINT dept_name_nn NOT NULL,
    manager_id         NUMBER (6),
    location_id        NUMBER (4)
);
/* Formatted on 3/16/2023 1:41:20 PM (QP5 v5.388) */
INSERT INTO departments
     VALUES (10,
             'Administration',
             200,
             1700);
INSERT INTO departments
     VALUES (20,
             'Marketing',
             201,
             1000);
INSERT INTO departments
     VALUES (30,
             'Purchasing',
             114,
             1700);

INSERT INTO departments

     VALUES (40,
             'Human Resources',
             203,
             1000);

INSERT INTO departments

     VALUES (50,
             'Shipping',
             121,
             1700);

 

 DECLARE
   TYPE emp_cur_typ IS REF CURSOR;
     emp_cur    emp_cur_typ;
     dept_name  departments.department_name%TYPE;
     emp_name   emp.last_name%TYPE;
     CURSOR c1 IS
       SELECT department_name,
         CURSOR ( SELECT e.last_name
                 FROM emp e
                 WHERE e.department_id = d.department_id
                 ORDER BY e.last_name
                 ) emp
       FROM departments d
       ORDER BY department_name;
 BEGIN
     OPEN c1;
     LOOP 
       FETCH c1 INTO dept_name, emp_cur;
       EXIT WHEN c1%NOTFOUND;
       DBMS_OUTPUT.PUT_LINE('Department: ' || dept_name);
       LOOP 
         FETCH emp_cur INTO emp_name;
         EXIT WHEN emp_cur%NOTFOUND;
         DBMS_OUTPUT.PUT_LINE('-- Employee: ' || emp_name);
       END LOOP;
     END LOOP;
     CLOSE c1;
 END;