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