Query to find Patches and bugs for R12

 

Query to find Patches and bugs for R12

Two tables to check if the patch is applied or not:

This table includes the defined bugs on the system:
SELECT   bug_number
FROM     apps.ad_bugs
WHERE   bug_number LIKE '%' ||15880118|| '%';

This table includes patches applied on the system:
SELECT patch_name
FROM   apps.ad_applied_patches
WHERE patch_name LIKE '%' ||15880118|| '%'

Query to Get AVG, MAX , MIN Time of Concurren program.

select
b.user_name username,
a.USER_CONCURRENT_PROGRAM_NAME  as PNAME,
avg(((nvl(ACTUAL_COMPLETION_DATE,sysdate)-a.REQUESTED_START_DATE)*24)*60) avg_Hrs_running, 
max(((nvl(ACTUAL_COMPLETION_DATE,sysdate)-a.REQUESTED_START_DATE)*24)*60) Max_Hrs_running,
min((nvl(ACTUAL_COMPLETION_DATE,sysdate)-a.REQUESTED_START_DATE)*24) Min_Hrs_running
from
apps.fnd_conc_req_summary_v a,
apps.fnd_user  b
where
phase_code = 'C' and status_code = 'C' and
a.REQUESTED_START_DATE > sysdate-30 and
upper(a.USER_CONCURRENT_PROGRAM_NAME) like upper('concurrent program name%') and
a.REQUESTED_BY=b.user_id
and a.description is null
group by b.user_name,a.USER_CONCURRENT_PROGRAM_NAME;

Query to find request set and its Request group and Responsibility

SELECT FA.application_name,

       fr.responsibility_name program_attached_to,

       frg.request_group_name,

       fcp.request_set_name,

       fcp.user_request_set_name

  FROM apps.fnd_responsibility_vl   fr,

       apps.fnd_request_groups      frg,

       apps.fnd_request_group_units frgu,

       apps.fnd_request_Sets_vl     fcp,

       apps.fnd_application_vl      FA

 WHERE frg.request_group_id = fr.request_group_id

   AND frgu.request_group_id = frg.request_group_id

   AND fcp.request_set_id = frgu.request_unit_id

   AND fcp.application_id = FA.application_id

   AND upper(fcp.user_request_set_name) LIKE  UPPER('');

How to Kill Long Running Concurrent Job in R12

Step1 : Find SID and Serial Number for Concurrent Job

SELECT a.request_id, d.sid, d.serial# ,d.osuser,

d.process , c.SPID ,d.inst_id

FROM apps.fnd_concurrent_requests a,

apps.fnd_concurrent_processes b,

gv$process c,

gv$session d

WHERE a.controlling_manager = b.concurrent_process_id

AND c.pid = b.oracle_process_id

AND b.session_id=d.audsid

AND a.request_id =:REQUESTID

AND a.phase_code = 'R';

Step2: Kill the long running session 

--ALTER SYSTEM KILL SESSION 'SID, serial#' IMMEDIATE;

ALTER SYSTEM KILL SESSION '1201, 40153' IMMEDIATE;

Step3 : Terminate the Concurrent Request and commit the transaction

 update fnd_concurrent_requests

   set status_code='X', phase_code='C'

   where request_id=:REQUESTID;

  

  

commit;  

Query to get Order Types and its process flow

SELECT HAOU.NAME "Operating Unit",
         OTTT_H.NAME "Transaction Type",
         OTTT_H.DESCRIPTION "Description",
         DECODE (OTVL.SALES_DOCUMENT_TYPE_CODE,
                 'O', 'Sales Order',
                 OTVL.SALES_DOCUMENT_TYPE_CODE)
            "Sales Document Type",
         FLV_CAT.MEANING "Order Category",
         OTVL.TRANSACTION_TYPE_CODE "Transaction Type Code",
         WRPV_H.DISPLAY_NAME "Fulfillment Flow",
         NULL "Negotiation Flow",
            TO_CHAR (OTVL.START_DATE_ACTIVE, 'DD-MON-YYYY')
         || ' - '
         || TO_CHAR (OTVL.END_DATE_ACTIVE, 'DD-MON-YYYY')
            "Effective Dates",
         FLV_PHA.MEANING "Default Transaction Phase",
         LAYOUT_TEMPLATE_ID "Layout Template",
         CONTRACT_TEMPLATE_ID "Contract Template",
         OTVL.QUOTE_NUM_AS_ORD_NUM_FLAG "Retain Document Number",
         OTVL.AGREEMENT_TYPE_CODE "Agreement Type",
         OTVL.AGREEMENT_REQUIRED_FLAG "Agreement Required",
         OTVL.PO_REQUIRED_FLAG "Purchase Order Required",
         DECODE (OTVL.DEFAULT_INBOUND_LINE_TYPE_ID, NULL, NULL, OTTT_DEF.NAME)
            "Default Return Line Type",
         DECODE (OTVL.DEFAULT_OUTBOUND_LINE_TYPE_ID, NULL, NULL, OTTT_DEF.NAME)
            "Default Order Line Type",
         OTVL.ENFORCE_LINE_PRICES_FLAG "Enfore List Price",
         (SELECT NAME
            FROM APPS.QP_LIST_HEADERS_TL Q
           WHERE     LIST_HEADER_ID = OTVL.PRICE_LIST_ID
                 AND Q.LANGUAGE = OTTT_H.LANGUAGE)
            "Price List",
         OTVL.MIN_MARGIN_PERCENT "Min. Margin Percent",
         OTVL.ENTRY_CREDIT_CHECK_RULE_ID "Ordering Credit Check",
         OTVL.PICKING_CREDIT_CHECK_RULE_ID "Picking Credit Check",
         OTVL.PACKING_CREDIT_CHECK_RULE_ID "Packing Credit Check",
         OTVL.SHIPPING_CREDIT_CHECK_RULE_ID "Shipping Credit Check",
         (SELECT ORGANIZATION_CODE
            FROM APPS.MTL_PARAMETERS
           WHERE ORGANIZATION_ID = OTVL.WAREHOUSE_ID)
            "Warehouse",
         OTVL.SHIPPING_METHOD_CODE "Shipping Method",
         OTVL.SHIPMENT_PRIORITY_CODE "Shipment Priority",
         OTVL.FREIGHT_TERMS_CODE "Freight Terms",
         OTVL.FOB_POINT_CODE "FOB",
         OTVL.SHIP_SOURCE_TYPE_CODE "Shipping Source Type",
         OTVL.DEMAND_CLASS_CODE "Demand Class",
         OTVL.SCHEDULING_LEVEL_CODE "Scheduling Level",
         OTVL.INSPECTION_REQUIRED_FLAG "Inspection Required",
         OTVL.AUTO_SCHEDULING_FLAG "Auto Schedule",
         OTVL.DEFAULT_LINE_SET_CODE "Line Set",
         OTVL.DEFAULT_FULFILLMENT_SET "Fulfillment Set",
         OTVL.INVOICING_RULE_ID "Invoicing Rule",
         OTVL.ACCOUNTING_RULE_ID "Accouting Rule",
         (SELECT NAME
            FROM APPS.RA_BATCH_SOURCES_ALL
           WHERE     BATCH_SOURCE_ID = OTVL.INVOICE_SOURCE_ID
                 AND ORG_ID = OTVL.ORG_ID)
            "Invoice Source",
         (SELECT NAME
            FROM APPS.RA_BATCH_SOURCES_ALL
           WHERE     BATCH_SOURCE_ID = OTVL.NON_DELIVERY_INVOICE_SOURCE_ID
                 AND ORG_ID = OTVL.ORG_ID)
            "Non Delivery Invoice Source",
         OTVL.INVOICING_CREDIT_METHOD_CODE "Invoices With Rules",
         OTVL.ACCOUNTING_CREDIT_METHOD_CODE "Split Term Invoices",
         (SELECT NAME
            FROM APPS.RA_CUST_TRX_TYPES_ALL
           WHERE     CUST_TRX_TYPE_ID = OTVL.CUST_TRX_TYPE_ID
                 AND ORG_ID = OTVL.ORG_ID)
            "Recv. Trans. Type",
         OTVL.TAX_CALCULATION_EVENT_CODE "Tax Event",
         OTVL.COST_OF_GOODS_SOLD_ACCOUNT "COGS Account",
         OTVL.CURRENCY_CODE "Currency",
         (SELECT UsER_CONVERSION_TYPE
            FROM apps.GL_DAILY_CONVERSION_TYPES
           WHERE CONVERSION_TYPE = OTVL.CONVERSION_TYPE_CODE)
            "Conversion Type",
         OTTT_L.NAME "Line Type",
         OWA_L.ITEM_TYPE_CODE "Item Type",
         WRPV_L.DISPLAY_NAME "Process",
         TO_CHAR (OWA_L.START_DATE_ACTIVE, 'DD-MON-YYYY') "Start Date",
         TO_CHAR (OWA_L.END_DATE_ACTIVE, 'DD-MON-YYYY') "End Date"
    FROM APPS.OE_TRANSACTION_TYPES_ALL OTVL,
         APPS.HR_ALL_ORGANIZATION_UNITS HAOU,
         APPS.OE_TRANSACTION_TYPES_TL OTTT_H,
         APPS.FND_LOOKUP_VALUES FLV_CAT,
         APPS.OE_WORKFLOW_ASSIGNMENTS OWA_H,
         APPS.WF_RUNNABLE_PROCESSES_V WRPV_H,
         APPS.FND_LOOKUP_VALUES FLV_PHA,
         APPS.OE_TRANSACTION_TYPES_TL OTTT_DEF,
         APPS.OE_WORKFLOW_ASSIGNMENTS OWA_L,
         APPS.OE_TRANSACTION_TYPES_TL OTTT_L,
         APPS.WF_RUNNABLE_PROCESSES_V WRPV_L,
         DUAL
   WHERE     1 = 1
         AND OTVL.ORG_ID = HAOU.ORGANIZATION_ID
         AND OTVL.TRANSACTION_TYPE_CODE = 'ORDER'
         AND OTVL.TRANSACTION_TYPE_ID = OTTT_H.TRANSACTION_TYPE_ID
         AND (    OTVL.ORDER_CATEGORY_CODE = FLV_CAT.LOOKUP_CODE
              AND FLV_CAT.LOOKUP_TYPE IN ('ORDER_CATEGORY'))
         AND OTVL.TRANSACTION_TYPE_ID = OWA_H.ORDER_TYPE_ID
         AND (    OWA_H.PROCESS_NAME = WRPV_H.PROCESS_NAME
              AND OWA_H.WF_ITEM_TYPE = WRPV_H.ITEM_TYPE)
         AND (    OTVL.DEF_TRANSACTION_PHASE_CODE = FLV_PHA.LOOKUP_CODE(+)
              AND FLV_PHA.LOOKUP_TYPE(+) IN ('TRANSACTION_PHASE'))
         AND DECODE (
                OTVL.ORDER_CATEGORY_CODE,
                'ORDER', NVL (OTVL.DEFAULT_OUTBOUND_LINE_TYPE_ID,
                              OTVL.TRANSACTION_TYPE_ID),
                'RETURN', NVL (OTVL.DEFAULT_INBOUND_LINE_TYPE_ID,
                               OTVL.TRANSACTION_TYPE_ID)) =
                OTTT_DEF.TRANSACTION_TYPE_ID
         AND OTVL.TRANSACTION_TYPE_ID = OWA_L.ORDER_TYPE_ID
         AND OTTT_L.TRANSACTION_TYPE_ID = OWA_L.LINE_TYPE_ID
         AND (    OWA_L.PROCESS_NAME = WRPV_L.PROCESS_NAME
              AND WRPV_L.ITEM_TYPE = 'OEOL')
         AND OTTT_H.LANGUAGE = 'US'
         AND OTTT_H.LANGUAGE = FLV_CAT.LANGUAGE
         AND OTTT_H.LANGUAGE = OTTT_DEF.LANGUAGE
         AND OTTT_H.LANGUAGE = OTTT_L.LANGUAGE
         AND HAOU.NAME IN (SELECT DISTINCT NVL (PARAM_LIST, NAME)
                             FROM (    SELECT TRIM (REGEXP_SUBSTR (PARAMETER,
                                                                   '[^,]+',
                                                                   1,
                                                                   LEVEL))
                                                 PARAM_LIST,
                                              NULL
                                         FROM (SELECT TRIM (
                                                         DECODE ('ALL',
                                                                 'ALL', NULL,
                                                                 'ALL'))
                                                         PARAMETER
                                                 FROM DUAL) T
                                   CONNECT BY REGEXP_SUBSTR (PARAMETER,
                                                             '[^,]+',
                                                             1,
                                                             LEVEL)
                                                 IS NOT NULL) PARAM_TBL,
                                  (SELECT NULL, NAME
                                     FROM APPS.HR_ALL_ORGANIZATION_UNITS)
                                  BASE_TBL)
ORDER BY OTTT_H.NAME,
         OTTT_L.NAME,
         OWA_L.START_DATE_ACTIVE,
         OWA_L.ITEM_TYPE_CODE

Query to find Responsibilit and its Request group

SELECT DISTINCT frt.responsibility_name, frg.request_group_name,fr.application_id
    FROM fnd_Responsibility fr, fnd_responsibility_tl frt,
    fnd_request_groups frg
    WHERE frt.responsibility_id = fr.responsibility_id
    AND frg.request_group_id = fr.request_group_id
    AND frt.LANGUAGE = USERENV('LANG');

Query to Get Employee Timecard Details

SELECT nvl(ppf.employee_number,ppf.npw_number) "Person Number",
 ppf.full_name,
 htbb1.start_time week_start_date,
 htbb1.stop_time week_end_date,
 hts.submission_date submitted_date,
 day.start_time day,
 hta1.attribute_category,
 detail.measure hours,
 detail.creation_date,
 detail.last_update_date,
 ppa.project_number,
 pt.task_number,
 hta.attribute3 "Type",hta.attribute2,
 hts.approval_status,pt.task_name
 FROM apps.per_all_people_f ppf,
 apps.hxc_time_building_blocks htbb1,
 apps.hxc_time_building_blocks DAY,
 apps.hxc_time_building_blocks detail,
 apps.hxc_base_pa_online_projects_v ppa,
 apps.hxc_base_pa_online_tasks_v pt,
 apps.hxc_time_attribute_usages htau,
 apps.hxc_time_attribute_usages htau1,
 apps.hxc_time_attributes hta,
 apps.hxc_bld_blk_info_types hbbit,
 apps.hxc_time_attributes hta1,
 apps.hxc_timecard_summary hts
 WHERE 1 = 1
 AND htbb1.resource_id = ppf.person_id
 AND htbb1.SCOPE = 'TIMECARD'
 AND TRUNC (SYSDATE) BETWEEN ppf.effective_start_date
 AND NVL (ppf.effective_end_date,
 TRUNC (SYSDATE))
 AND DAY.parent_building_block_id = htbb1.time_building_block_id
 AND DAY.parent_building_block_ovn = htbb1.object_version_number
 AND DAY.SCOPE = 'DAY'
 AND DAY.date_to = TO_DATE ('31/12/4712', 'DD/MM/YYYY')
 AND detail.date_to = TO_DATE ('31/12/4712', 'DD/MM/YYYY')
 AND detail.SCOPE = 'DETAIL'
 AND detail.parent_building_block_id = DAY.time_building_block_id
 AND detail.parent_building_block_ovn = DAY.object_version_number
 AND hta.attribute1 = ppa.project_id
 AND hta.attribute2 = pt.task_id
 AND ppa.project_id = pt.project_id
 AND htau.time_building_block_id = detail.time_building_block_id
 AND htau.time_building_block_ovn = detail.object_version_number
 AND htau1.time_building_block_id = detail.time_building_block_id
 AND htau1.time_building_block_ovn = detail.object_version_number
 AND htau.time_attribute_id = hta.time_attribute_id
 AND hta.bld_blk_info_type_id = hbbit.bld_blk_info_type_id
 AND hbbit.bld_blk_info_type = 'PROJECTS'
 AND htau1.time_attribute_id = hta1.time_attribute_id
 AND hta1.attribute_category like 'ELEMENT%'--'SECURITY'
 AND hts.timecard_id = htbb1.time_building_block_id
 AND NVL (hts.timecard_ovn, htbb1.object_version_number) = htbb1.object_version_number
 and nvl(ppf.employee_number, ppf.npw_number) = ':empno' -- Change your employee number here of comment this line for all resources
 and project_number='Aftermarket'
 and  hts.approval_status ='WORKING'
ORDER BY day.start_time asc;