Delete Reservations based on Item , Organization Code and Sales Order number

CREATE OR REPLACE
PROCEDURE XXT_RelieveReservation(p_segment1 IN VARCHAR2, p_organization_code IN VARCHAR2, p_order_number IN VARCHAR2)
AS
        -- Common Declarations
        l_api_version   NUMBER      := 1.0;
        l_init_msg_list   VARCHAR2(2) := FND_API.G_TRUE;
        x_return_status   VARCHAR2(2);
        x_msg_count   NUMBER      := 0;
        x_msg_data               VARCHAR2(255);
        x_error_code             NUMBER      := 0;
   
        -- WHO columns
 l_user_id  NUMBER := -1;
 l_resp_id  NUMBER := -1;
 l_application_id NUMBER := -1;
        l_row_cnt  NUMBER := 1;
        l_user_name  VARCHAR2(30) := 'XXXUSER_NAME';
        l_resp_name  VARCHAR2(30) := 'XXXXesponsibility_key';  
       
        -- API specific declarations
        l_rsv_rec                   INV_RESERVATION_GLOBAL.MTL_RESERVATION_REC_TYPE;
        l_serial_number             INV_RESERVATION_GLOBAL.SERIAL_NUMBER_TBL_TYPE;
        l_primary_relieved_qty      NUMBER := 0;
        l_validation_flag           VARCHAR2(2) := FND_API.G_TRUE; 
        x_mtl_reservation_tbl       INV_RESERVATION_GLOBAL.MTL_RESERVATION_TBL_TYPE;
        x_mtl_reservation_tbl_count NUMBER := 0;
        x_primary_relieved_qty      NUMBER := 0;
        x_primary_remain_qty        NUMBER := 0;
        -- Get the reservation to be relieved       
        CURSOR c_item_reservations IS
        SELECT msi.organization_id, msi.inventory_item_id, msi.segment1, msi.primary_uom_code, res.reservation_id
        FROM mtl_system_items_b msi, mtl_parameters mp, mtl_reservations res, mtl_Sales_orders mso
        WHERE msi.segment1 = p_segment1
        AND msi.organization_id = mp.organization_id
        AND mp.organization_code = p_organization_code
        AND res.organization_id = msi.organization_id
        AND res.inventory_item_id = msi.inventory_item_id
  AND res.demand_source_header_id=mso.sales_order_id
        AND mso.segment1              =p_order_number ;
       
       
BEGIN

 -- Get the user_id
 SELECT user_id
 INTO l_user_id
 FROM fnd_user
 WHERE user_name = l_user_name;
 -- Get the application_id and responsibility_id
 SELECT application_id, responsibility_id
 INTO l_application_id, l_resp_id
 FROM fnd_responsibility
 WHERE responsibility_key = l_resp_name;
 FND_GLOBAL.APPS_INITIALIZE(l_user_id, l_resp_id, l_application_id); 
 dbms_output.put_line('Initialized applications context: '|| l_user_id || ' '|| l_resp_id ||' '|| l_application_id );
       
        -- Get the first row
        FOR ir IN c_item_reservations LOOP
          l_rsv_rec.organization_id := ir.organization_id;
          l_rsv_rec.inventory_item_id := ir.inventory_item_id;
    l_rsv_rec.reservation_id := ir.reservation_id;
    DBMS_OUTPUT.PUT_LINE('c_item_reservations'||':  '||ir.reservation_id);
          --EXIT;
       
       
        -- Get all reservations that exist for this item
        -- call API to get all the reservations for this item
        DBMS_OUTPUT.PUT_LINE('=======================================================');
        DBMS_OUTPUT.PUT_LINE('Calling INV_RESERVATION_PUB.Query_Reservation');        
        INV_RESERVATION_PUB.QUERY_RESERVATION(
                  P_API_VERSION_NUMBER    => l_api_version
                , P_INIT_MSG_LST    => l_init_msg_list
                , X_RETURN_STATUS    => x_return_status
                , X_MSG_COUNT     => x_msg_count
                , X_MSG_DATA     => x_msg_data
                , P_QUERY_INPUT     => l_rsv_rec
                , P_LOCK_RECORDS    => FND_API.G_FALSE
                , P_SORT_BY_REQ_DATE    => INV_RESERVATION_GLOBAL.G_QUERY_NO_SORT
                , P_CANCEL_ORDER_MODE    => INV_RESERVATION_GLOBAL.G_CANCEL_ORDER_NO
                , X_MTL_RESERVATION_TBL    => x_mtl_reservation_tbl
                , X_MTL_RESERVATION_TBL_COUNT   => x_mtl_reservation_tbl_count
                , X_ERROR_CODE     => x_error_code 
         );
        
         DBMS_OUTPUT.PUT_LINE('=======================================================');
         DBMS_OUTPUT.PUT_LINE('Return Status: '||x_return_status);
 
         IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
            DBMS_OUTPUT.PUT_LINE('Error Message :'||x_msg_data);
         END IF;
        
         IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
            FOR i IN 1..x_mtl_reservation_tbl_count LOOP
                DBMS_OUTPUT.PUT_LINE('=======================================================');
                dbms_output.put_line('reservation_id              : '|| TO_CHAR(x_mtl_reservation_tbl(i).reservation_id));
                dbms_output.put_line('requirement_date            : '|| TO_CHAR(x_mtl_reservation_tbl(i).requirement_date, 'YYYY/MM/DD'));
                dbms_output.put_line('organization_id             : '|| TO_CHAR(x_mtl_reservation_tbl(i).organization_id));
                dbms_output.put_line('inventory_item_id           : '|| TO_CHAR(x_mtl_reservation_tbl(i).inventory_item_id));
                dbms_output.put_line('demand_source_type_id       : '|| TO_CHAR(x_mtl_reservation_tbl(i).demand_source_type_id));
                dbms_output.put_line('demand_source_name          : '|| x_mtl_reservation_tbl(i).demand_source_name);
                dbms_output.put_line('demand_source_header_id     : '|| TO_CHAR(x_mtl_reservation_tbl(i).demand_source_header_id));
                dbms_output.put_line('demand_source_line_id       : '|| TO_CHAR(x_mtl_reservation_tbl(i).demand_source_line_id));
                dbms_output.put_line('demand_source_line_detail   : '|| TO_CHAR(x_mtl_reservation_tbl(i).demand_source_line_detail));
                dbms_output.put_line('primary_uom_code            : '|| x_mtl_reservation_tbl(i).primary_uom_code);
                dbms_output.put_line('reservation_uom_code        : '|| x_mtl_reservation_tbl(i).reservation_uom_code);
                dbms_output.put_line('reservation_quantity        : '|| TO_CHAR(x_mtl_reservation_tbl(i).reservation_quantity));
                dbms_output.put_line('primary_reservation_quantity: '|| TO_CHAR(x_mtl_reservation_tbl(i).primary_reservation_quantity));
                dbms_output.put_line('detailed_quantity           : '|| TO_CHAR(x_mtl_reservation_tbl(i).detailed_quantity));
                dbms_output.put_line('supply_source_type_id       : '|| TO_CHAR(x_mtl_reservation_tbl(i).supply_source_type_id));
                dbms_output.put_line('supply_source_header_id     : '|| TO_CHAR(x_mtl_reservation_tbl(i).supply_source_header_id));
                dbms_output.put_line('supply_source_line_id       : '|| TO_CHAR(x_mtl_reservation_tbl(i).supply_source_line_id));
                dbms_output.put_line('supply_source_name          : '|| (x_mtl_reservation_tbl(i).supply_source_name));
                dbms_output.put_line('supply_source_line_detail   : '|| TO_CHAR(x_mtl_reservation_tbl(i).supply_source_line_detail));
                dbms_output.put_line('subinventory_code           : '|| x_mtl_reservation_tbl(i).subinventory_code);
                dbms_output.put_line('ship_ready_flag             : '|| TO_CHAR(x_mtl_reservation_tbl(i).ship_ready_flag));
                dbms_output.put_line('staged_flag                 : '|| x_mtl_reservation_tbl(i).staged_flag);
                DBMS_OUTPUT.PUT_LINE('=======================================================');
             END LOOP;  
           END IF;
        -- call API to relieve all the queried reservations for this item
        DBMS_OUTPUT.PUT_LINE('=======================================================');
        DBMS_OUTPUT.PUT_LINE('Calling INV_RESERVATION_PUB.Relieve_Reservation');         
       FOR i IN 1..x_mtl_reservation_tbl_count LOOP
       
                l_primary_relieved_qty := x_mtl_reservation_tbl(i).reservation_quantity; --- Passing whole quantity to relieve
             
              -- Call the API to relieve reservations for the provided serial numbers
              INV_RESERVATION_PUB.RELIEVE_RESERVATION(
                  P_API_VERSION_NUMBER   =>  l_api_version
                  , P_INIT_MSG_LST   =>  l_init_msg_list
                  , X_RETURN_STATUS   =>  x_return_status
                  ,  X_MSG_COUNT   =>  x_msg_count    
                  ,  X_MSG_DATA    =>  x_msg_data     
                  ,  P_RSV_REC    =>  x_mtl_reservation_tbl(i)      
                  ,  P_PRIMARY_RELIEVED_QUANTITY =>  l_primary_relieved_qty
                  ,  P_RELIEVE_ALL   =>  FND_API.G_true      ---- True for Quantity Relieves to Zero
                  ,  P_ORIGINAL_SERIAL_NUMBER  =>  l_serial_number
                  ,  P_VALIDATION_FLAG   =>  l_validation_flag
                  ,  X_PRIMARY_RELIEVED_QUANTITY =>  x_primary_relieved_qty
                  ,  X_PRIMARY_REMAIN_QUANTITY  =>  x_primary_remain_qty
              );
             
      
              DBMS_OUTPUT.PUT_LINE('=======================================================');
              DBMS_OUTPUT.PUT_LINE('Return Status: '||x_return_status);
       
              IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
                 DBMS_OUTPUT.PUT_LINE('Error Message :'||x_msg_data);
              END IF;
             
              IF (X_RETURN_STATUS = FND_API.G_RET_STS_SUCCESS) THEN
         DBMS_OUTPUT.PUT_LINE('Item reserved quantity relieve details');
     DBMS_OUTPUT.PUT_LINE('Reservation ID:'||x_mtl_reservation_tbl(i).reservation_id);
     DBMS_OUTPUT.PUT_LINE('Item ID:'||x_mtl_reservation_tbl(i).inventory_item_id);
     DBMS_OUTPUT.PUT_LINE('Organization ID:'||x_mtl_reservation_tbl(i).organization_id);
     DBMS_OUTPUT.PUT_LINE('Primary Relieved Quantity: '||X_PRIMARY_RELIEVED_QTY);
         DBMS_OUTPUT.PUT_LINE('Primary Remain Quantity: '||x_primary_remain_qty); 
              END IF;
              END LOOP;
   END LOOP;
EXCEPTION
        WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE('Exception Occured :');
          DBMS_OUTPUT.PUT_LINE(SQLCODE ||':'||SQLERRM);
          DBMS_OUTPUT.PUT_LINE('=======================================================');
END;
/
begin
XXT_RelieveReservation('<Item>','<Organization>','<Order>');-- Item , Organization Code, Sales Order number respectively
END;
/
SELECT msi.organization_id, msi.inventory_item_id, msi.segment1, msi.primary_uom_code,res.reservation_id,mso.segment1
        FROM mtl_system_items_b msi, mtl_parameters mp, mtl_reservations res, mtl_Sales_orders mso
        WHERE msi.segment1 = 'B6-M'
        AND msi.organization_id = mp.organization_id
        AND mp.organization_code = 'TB1'
        AND res.organization_id = msi.organization_id
        AND res.inventory_item_id = msi.inventory_item_id
      AND res.demand_source_header_id=mso.sales_order_id
        AND mso.segment1              ='413098' ;

/

This provides the Customer Address of an Order

Modules Involved : OM
Purpose    : This provides the Customer Address of an Order
Description      : We can get the BILL_TO and SHIP_TO addresses of a given order.
Needs two parmeters(ORDER_NUMBER,ORG_ID) and It provides Bill_To and Ship_To address in two different lines
****************************************************************** */
SELECT OOHA.ORDER_NUMBER
, HP.PARTY_NAME
, HCSUA.SITE_USE_CODE
, HCSUA.location
, hl.ADDRESS1||','||hl.ADDRESS2||' '||hl.ADDRESS3||' '||hl.ADDRESS4||' '||hl.CITY||','||hl.STATE||','
  ||hl.POSTAL_CODE||','||hl.country ADDRESS
FROM OE_ORDER_HEADERS_ALL OOHA
, HZ_PARTIES HP
, HZ_CUST_ACCOUNTS HCA
, HZ_PARTY_SITES HPS
, HZ_CUST_ACCT_SITES_ALL HCASA
, HZ_CUST_SITE_USES_ALL  HCSUA
, HZ_LOCATIONS HL
WHERE 1=1
and ORDER_NUMBER = :ORDER_NUMBER
and OOHA.ORG_ID = :ORG_ID
and (OOHA.INVOICE_TO_ORG_ID = HCSUA.SITE_USE_ID or OOHA.SHIP_TO_ORG_ID = HCSUA.SITE_USE_ID)
--AND OOHA.ORG_ID = HCSUA.ORG_ID
AND HP.PARTY_ID = HCA.PARTY_ID
AND HP.PARTY_ID = HPS.PARTY_ID
and HPS.PARTY_SITE_ID = HCASA.PARTY_SITE_ID
AND HCA.CUST_ACCOUNT_ID = HCASA.CUST_ACCOUNT_ID
and HCASA.CUST_ACCT_SITE_ID = HCSUA.CUST_ACCT_SITE_ID
and HCSUA.SITE_USE_CODE in ('BILL_TO', 'SHIP_TO')
--AND HCSUA.PRIMARY_FLAG = 'Y'
and HPS.LOCATION_ID  = HL.LOCATION_ID;

To get the Employee's Potential Life Event Details

Modules Involved : HRMS, OAB
Purpose    : To get the Employee's Potential Life Event Details
Description      : It gives the Life Event details for an employee, the Status,the date the life event was Started,
       the date the life event was closed (Processed), as well as Void or Back Out Dates.
Needs two parameters (Person_Id, Business Group ID)
****************************************************************** */
SELECT  PPF.PERSON_ID
       , PPF.FULL_NAME
       , bplp.lf_evt_ocrd_dt                  eventDate
       , BL.NAME || ' (' || BL.LER_ID  || ')' LIFEEVENT
       , BPLP.PTNL_LER_FOR_PER_STAT_CD  STATUS
       , bplp.NTFN_DT                         notified
       , bplp.DTCTD_DT                        detected
       , bplp.UNPROCD_DT                      unprocessed
       , bplp.PROCD_DT                        processed
       , BPLP.VOIDD_DT                        VOIDED
       , bplp.lf_evt_ocrd_dt                  Occured
      FROM
         ben_ptnl_ler_for_per bplp
       , BEN_LER_F            BL
       , PER_ALL_PEOPLE_F     PPF
    WHERE sysdate between ppf.effective_start_date and ppf.effective_end_date
        AND ppf.person_id = bplp.person_id
        AND sysdate between bl.effective_start_date and bl.effective_end_date
        AND bplp.ler_id = bl.ler_id
        and PPF.BUSINESS_GROUP_ID = :BUSINESS_GROUP_ID
        AND PPF.PERSON_ID = :Person_ID
      ORDER BY 1 desc;

To get the Employee's assignment Information

Modules Involved : Human Resource Management System (HRMS)
Purpose    : To get the Employee's assignment Information
Description      : We need to pass the employee full_name. It retrieves Employee's Business Group,Hire Date,Job,Dept
                   ,Division,Manager etc..
Note: Department and Division is vary from client to client depends on the company segments.
      Here Valueset Name for the Department is hard-coded.
****************************************************************** */
SELECT    PAPF.PERSON_ID
,         PAPF.FULL_NAME
,         PBG.NAME
,         PAPF.ORIGINAL_DATE_OF_HIRE HIRE_DATE
,         PPOS.DATE_START LATEST_START_DATE
,         PPOS.ADJUSTED_SVC_DATE
,         SUBSTR(PPG.GROUP_NAME,1,INSTR(PPG.GROUP_NAME,'.')-1) DIVISION
,         JOB.NAME "JOB TITLE"
,         GCC.SEGMENT2 "DEPT NUMBER"
,         FVT.DESCRIPTION  "DEPT NAME"
,         SUP.FULL_NAME MANAGER
,         PG.NAME GRADE
,         LOC.LOCATION_CODE  "WORK LOCATION"
FROM      PER_ALL_PEOPLE_F PAPF
,         PER_ALL_PEOPLE_F SUP
,         PER_BUSINESS_GROUPS PBG
,         PER_PERIODS_OF_SERVICE PPOS
,         PER_ALL_ASSIGNMENTS_F PAAF
,         PER_JOBS_TL JOB
,         PER_GRADES_TL PG
,         PAY_PEOPLE_GROUPS   PPG
,         HR_LOCATIONS_ALL_TL LOC
,         GL_CODE_COMBINATIONS    GCC
,         FND_FLEX_VALUE_SETS      FVS       
,         FND_FLEX_VALUES FV
,         FND_FLEX_VALUES_TL FVT
where     1=1
and       PAPF.FULL_NAME = :EMPLOYEE_FULL_NAME
and       PAPF.BUSINESS_GROUP_ID = PBG.BUSINESS_GROUP_ID
and       PAAF.PERSON_ID  =   PAPF.PERSON_ID
and       PAAF.ASSIGNMENT_TYPE = 'E'
AND       PAAF.PERIOD_OF_SERVICE_ID   =   PPOS.PERIOD_OF_SERVICE_ID
AND       TRUNC(SYSDATE) BETWEEN TRUNC(PAAF.EFFECTIVE_START_DATE) AND TRUNC(PAAF.EFFECTIVE_END_DATE)
AND       TRUNC(SYSDATE) BETWEEN TRUNC(PAPF.EFFECTIVE_START_DATE) AND TRUNC(PAPF.EFFECTIVE_END_DATE)
AND       TRUNC(SYSDATE) BETWEEN TRUNC(SUP.EFFECTIVE_START_DATE) AND TRUNC(SUP.EFFECTIVE_END_DATE)
AND       PAAF.JOB_ID = JOB.JOB_ID
AND       PAAF.SUPERVISOR_ID = SUP.PERSON_ID
AND       PAAF.GRADE_ID   =   PG.GRADE_ID(+)
AND       PAAF.PEOPLE_GROUP_ID   =   PPG.PEOPLE_GROUP_ID(+)
AND       PAAF.LOCATION_ID  =   LOC.LOCATION_ID(+)
AND       GCC.CODE_COMBINATION_ID = PAAF.DEFAULT_CODE_COMB_ID
AND       FVS.FLEX_VALUE_SET_NAME  =  'SSFT Cost Center'
AND       FVS.FLEX_VALUE_SET_ID  =  FV.FLEX_VALUE_SET_ID
AND       FV.FLEX_VALUE_ID  =  FVT.FLEX_VALUE_ID
and          FV.FLEX_VALUE = GCC.SEGMENT2

order by 3

To get the Suppliers Bank details

Modules Involved : Accounts Payables (AP)
Purpose    : To get the Suppliers Bank details
Description      : We can get the Supplier, Supplier site details, bank and branch details of the Supplier
Note: We need to pass the vendor_id as parameter.
****************************************************************** */
SELECT DISTINCT pv.vendor_id,
pv.vendor_name vendor_name,
pv.segment1 vendor_number,
pv.vendor_type_lookup_code vendor_type_lookup_code,
pvs.vendor_site_id vendor_site_id,
pvs.vendor_site_code vendor_site_Name,
pvs.address_line1 Site_Address_Line1,
pvs.address_line2 Site_Address_Line2,
PVS.CITY,
PVS.ZIP,
aba.org_id,
abau.bank_account_uses_id bank_account_uses_id,
abau.end_date end_date,
abau.external_bank_account_id external_bank_account_id,
abau.primary_flag primary_flag,
abau.start_date start_date,
aba.bank_account_name bank_account_name,
aba.bank_account_num bank_account_num,
aba.bank_account_type bank_account_type,
aba.account_type account_type,
aba.currency_code currency_code,
aba.description bank_account_description,
aba.check_digits check_digits,
aba.multi_currency_flag multi_currency_flag,
abb.bank_name bank_name,
abb.bank_name_alt bank_name_alt,
abb.bank_number bank_number,
abb.bank_branch_name bank_branch_name,
abb.bank_branch_name_alt bank_branch_name_alt,
abb.bank_num bank_num, abb.institution_type institution_type,
abb.bank_branch_type bank_branch_type,
abb.end_date branch_end_date,
aba.inactive_date acct_inactive_date
FROM ap_bank_account_uses_all abau,
ap_bank_accounts_all aba,
ap_bank_branches abb,
po_vendors pv,
PO_VENDOR_SITES_ALL PVS
where 1=1
and pv.vendor_id = :Vendor_Id
and abau.external_bank_account_id = aba.bank_account_id
AND aba.bank_branch_id = abb.bank_branch_id
AND abau.vendor_id = pv.vendor_id
AND abau.vendor_id = pvs.vendor_id(+)
and ABAU.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID(+)
;

If the request contains multiple sql statements, It gives the current sql statement that

Modules Involved : Application Object Library (AOL)
Purpose             : This SQL gives the current sql statement that the request is processing
Description        : If the request contains multiple sql statements, It gives the current sql statement that
                            the request is processing.
                            Needs one parmeter (Request_ID)
****************************************************************** */
SELECT r.request_id,
sq.sql_text,
ss.SID,
ss.SERIAL#
FROM fnd_concurrent_requests r,
v$process p,
v$session ss,
V$SQLAREA sq
WHERE r.request_id IN (:Request_ID)
AND p.spid = r.oracle_process_id
AND ss.paddr = p.addr
AND sq.ADDRESS = ss.SQL_ADDRESS;

Finding out the Scheduled concurrent program details from a perticular responsibility

Modules Involved : Application Object Library (AOL)
Purpose    : Finding out the Scheduled concurrent program details from a perticular responsibility
Description      : Needs one parmeter (responsibility_name)
****************************************************************** */
SELECT   fcr.REQUEST_ID
        ,NVL(fcr.DESCRIPTION,CPT.USER_CONCURRENT_PROGRAM_NAME) CONCURRENT_PROGRAM_NAME
        ,SUBSTR (fcr.ARGUMENT_TEXT, 1, 30) ARGUMENT_TEXT
        ,USR.USER_NAME REQUESTED_BY
        ,RESPT.RESPONSIBILITY_NAME
        ,fcrc.DATE1 START_DATE
        ,fcrc.DATE2 END_DATE
        ,DECODE(fcrc.class_type,
              'P', 'Periodic',
              'S', 'On Specific Days',
              'X', 'Advanced',
              fcrc.CLASS_TYPE
             ) SCHEDULE_TYPE
        ,CASE
         when fcrc.class_type = 'P' then
            'Repeat every ' ||
             substr(fcrc.class_info, 1, instr(fcrc.class_info, ':') - 1) ||
             DECODE(SUBSTR(fcrc.CLASS_INFO, INSTR(fcrc.CLASS_INFO, ':', 1, 1) + 1, 1),
                   'N', ' minutes',
                   'M', ' months',
                   'H', ' hours',
                   'D', ' days') ||
             decode(substr(fcrc.class_info, instr(fcrc.class_info, ':', 1, 2) + 1, 1),
                  'S', ' from the start of the prior run',
                  'C', ' from the completion of the prior run')
         WHEN fcrc.CLASS_TYPE = 'S' THEN 
              DECODE(SUBSTR(fcrc.CLASS_INFO, 32, 1), '1', 'Last day of month ') ||
              decode(sign(to_number(substr(fcrc.class_info, 33))),
                    '1',  'Days of week: ' ||
                    decode(substr(fcrc.class_info, 33, 1), '1', 'Su ') ||
                    decode(substr(fcrc.class_info, 34, 1), '1', 'Mo ') ||
                    decode(substr(fcrc.class_info, 35, 1), '1', 'Tu ') ||
                    decode(substr(fcrc.class_info, 36, 1), '1', 'We ') ||
                    decode(substr(fcrc.class_info, 37, 1), '1', 'Th ') ||
                    decode(substr(fcrc.class_info, 38, 1), '1', 'Fr ') ||
                    DECODE(SUBSTR(fcrc.CLASS_INFO, 39, 1), '1', 'Sa '))
        END SCHEDULE,
        fcrc.CLASS_INFO "Class Info"
    FROM fnd_concurrent_requests fcr,
         fnd_concurrent_programs_tl cpt,
         fnd_responsibility_tl RESPT,
         fnd_conc_release_classes fcrc,
         fnd_user usr
   WHERE fcr.concurrent_program_id = cpt.concurrent_program_id
     AND fcr.program_application_id = cpt.application_id
     AND fcr.responsibility_id = respt.responsibility_id
     AND fcr.requested_by = usr.user_id
     AND fcr.STATUS_CODE IN ('Q','I')-- 'P'
     AND RESPT.RESPONSIBILITY_NAME = :RESPONSIBILITY_NAME
     AND fcr.RELEASE_CLASS_APP_ID = fcrc.APPLICATION_ID
     AND fcr.RELEASE_CLASS_ID = fcrc.RELEASE_CLASS_ID
     AND fcrc.CLASS_TYPE IS NOT NULL
    --AND fcr.concurrent_program_id = 36888
    --AND TRUNC (actual_start_date) >= TRUNC (SYSDATE) - 1
ORDER BY 1 DESC

To retrieve the user concurrent programs, responsibilities, menus, request groups details

Modules Involved: Application object library(AOL)
Purpose         : To retrieve the user concurrent programs, responsibilities, menus, request groups details
Description     : This query needs one parameter(User Name).
****************************************************************** */
SELECT DISTINCT
  USR.USER_NAME ,
  EMP.FULL_NAME ,
  HRO.name ,
  FAT.APPLICATION_NAME ,
  FRV.RESPONSIBILITY_NAME ,
  FRT.DESCRIPTION RESPONSIBILITY_DESC ,
  FRG.REQUEST_GROUP_NAME ,
  FMT.USER_MENU_NAME ,
  FMT.DESCRIPTION MENU_DESCRIPTION
FROM
  FND_USER_RESP_GROUPS_ALL URGV ,
  FND_USER USR ,
  PER_ALL_PEOPLE_F EMP ,
  HR_ALL_ORGANIZATION_UNITS_TL HRO ,
  PER_ALL_ASSIGNMENTS_F HRA ,
  APPS.FND_REQUEST_GROUPS FRG ,
  APPS.FND_REQUEST_GROUP_UNITS FRGU ,
  APPS.FND_RESPONSIBILITY_VL FRV ,
  APPS.FND_RESPONSIBILITY_TL FRT ,
  APPS.FND_RESPONSIBILITY FRB ,
  APPS.fnd_menus_tl fmt ,
  APPS.FND_MENUS FMS ,
  APPS.FND_APPLICATION_TL FAT ,
  APPS.FND_APPLICATION FAL ,
  APPS.FND_CONCURRENT_PROGRAMS FCP ,
  APPS.FND_CONCURRENT_PROGRAMS_TL FCPT
WHERE  1                          = 1
AND URGV.RESPONSIBILITY_ID        = FRT.responsibility_id
AND URGV.USER_ID                  = USR.USER_ID
AND usr.employee_id               = emp.person_id
AND usr.end_date                 IS NULL
AND emp.person_id                 = hra.person_id
AND HRO.ORGANIZATION_ID           = HRA.ORGANIZATION_ID
AND HRA.ASSIGNMENT_STATUS_TYPE_ID = 1
AND FRV.REQUEST_GROUP_ID          = FRG.REQUEST_GROUP_ID
AND frgu.request_group_id         = frg.request_group_id
AND FRV.RESPONSIBILITY_ID(+)      = FRB.RESPONSIBILITY_ID
AND FRT.responsibility_id         = frb.responsibility_id
AND FRB.MENU_ID                   = FMT.MENU_ID
AND frb.menu_id                   = fms.menu_id
AND fat.application_id            = fal.application_id
AND fal.application_id            = frb.application_id
AND frgu.request_unit_id          = fcp.concurrent_program_id
AND FCP.CONCURRENT_PROGRAM_ID     = FCPT.CONCURRENT_PROGRAM_ID
AND USR.USER_NAME = :User_Name
;

Extract the trace Files for Concurrent program

Modules Involved: Application object library(AOL)
Purpose         : To extract the trace Files for Concurrent program
Description     : This query needs the parameter Request_Id.
****************************************************************** */
select 'Request id:'||REQUEST_ID as "Request_Id",
'Trace id: '||ORACLE_PROCESS_ID as "Trace Id",
'Trace Flag: '||REQ.ENABLE_TRACE as "Trace Flag",
'Trace Name: '||DEST.value||'/'||LOWER(DBNM.value)||'_ora_'||ORACLE_PROCESS_ID||'.trc' as "Trace Path",
'Prog. Name: '||PROG.USER_CONCURRENT_PROGRAM_NAME as "Concurrent Program Name",
'File Name: '||EXECNAME.EXECUTION_FILE_NAME|| EXECNAME.SUBROUTINE_NAME as "File name",
'Status : '||decode(phase_code,'R','Running') ||'-'||decode(status_code,'R','Normal') as "Status",
'SID Serial: '||ses.sid||','|| ses.serial#, 'Module : '||ses.module
from
fnd_concurrent_requests req,
v$session ses,
v$process proc,
v$parameter dest,
v$parameter dbnm,
fnd_concurrent_programs_vl prog,
fnd_executables execname
where req.request_id = :Request_Id
and req.oracle_process_id=proc.spid(+)
and proc.addr = ses.paddr(+)
and dest.name='user_dump_dest'
and dbnm.name='db_name'
and req.concurrent_program_id = prog.concurrent_program_id
and req.program_application_id = prog.application_id
and prog.application_id = execname.application_id
and PROG.EXECUTABLE_ID=EXECNAME.EXECUTABLE_ID
;

Profile Options at all levels provided Profile Option Name

Modules Involved: Application object library (AOL)
Purpose         : To get the Profile option information at all levels(Site,Application,Responsibility, User).                  
Description     : Need to pass the profile option name either system name (Ex:'ORG_ID') or
                  user defined name (Ex:'MO: Operating Unit')
 It provides
 1)The value assigned at site level
 2)The value assigned at Application level and Application Name If it is assigned to it.
 3)The value assigned at Responsibility level and Responsibility Name If it is assigned to any resp.
 4)The value assigned at User level and User Name If it is assigned to any user.
****************************************************************** */
select FPO.PROFILE_OPTION_ID, FPOT.PROFILE_OPTION_NAME PROFILE_SHORT_NAME
, fpot.user_profile_option_name profile_name
, DECODE(fpov.level_id,10001,'site',10002,'Appl',10003,'Resp',10004,'User') profile_level
, DECODE(fpov.level_id,10001,null, 10002,fa.application_short_name,10003,fr.responsibility_name,10004,fu.user_name) level_value
, FPOV.PROFILE_OPTION_VALUE PROFILE_VALUE
, fpov.*
FROM fnd_profile_option_values fpov
, fnd_profile_options fpo
, fnd_profile_options_tl fpot
, fnd_application fa
, FND_RESPONSIBILITY_TL FR
, FND_USER FU    
where 1=1 and (FPO.PROFILE_OPTION_NAME like NVL(:PROFILE_OPTION_NAME,FPO.PROFILE_OPTION_NAME)
and fpot.user_profile_option_name like nvl(:User_Profile_Option_Name,fpot.user_profile_option_name))
and fpo.profile_option_name=fpot.profile_option_name
and fpo.profile_option_id = fpov.profile_option_id
and fa.application_id(+)=fpov.level_value
AND FR.RESPONSIBILITY_ID(+)=FPOV.LEVEL_VALUE
and FU.USER_ID(+)=FPOV.LEVEL_VALUE
order by 5
;

Concurrent Program Name Vs Responsibility and Request Group

Modules Involved: Application object library (AOL)
Purpose         : To extract Concurrent Program, Responsibility and Request Group Info.
Description     : We can find the list of Responsibilities from which we can run the specified concurrent program.
This query need any one of the 2 parameters (Conc_Prog_Name, Responsibility_Name):
1)If you pass Conc_Prog_Name, You will get all the Responsibilities from which you can run that Program.
2)If you pass Responsibility_Name, You will get all the Concurrent Programs which you can run from that responsibility.
****************************************************************** */
SELECT fcpt.user_concurrent_program_name,
  frg.request_group_name,
  fcp.concurrent_program_name,
  FRT.RESPONSIBILITY_NAME,
  fat.application_name,
  fe.executable_name
FROM
  APPS.FND_REQUEST_GROUP_UNITS FRGU,
  APPS.FND_CONCURRENT_PROGRAMS FCP,
  APPS.FND_CONCURRENT_PROGRAMS_TL FCPT,
  APPS.FND_REQUEST_GROUPS FRG,
  APPS.FND_EXECUTABLES FE,
  APPS.FND_RESPONSIBILITY FR,
  APPS.FND_RESPONSIBILITY_TL FRT,
  APPS.FND_APPLICATION_TL FAT
WHERE 1                       = 1
AND fat.application_id        = frgu.application_id
AND frgu.request_unit_id      = fcp.concurrent_program_id
AND frgu.request_group_id     = frg.request_group_id
AND fe.executable_id          = fcp.executable_id
AND fcp.concurrent_program_id = fcpt.concurrent_program_id
AND frg.request_group_id      = fr.request_group_id
AND FR.RESPONSIBILITY_ID      = FRT.RESPONSIBILITY_ID
AND fcpt.user_concurrent_program_name = NVL(:Conc_Prog_Name, fcpt.user_concurrent_program_name)
and FRT.RESPONSIBILITY_NAME = nvl(:RESPONSIBILITY_NAME,FR.RESPONSIBILITY_NAME)
;

Responsibility Vs User_name

It needs any one of two parameters (User_Name, Responsibility_Name):
1)If you pass User_Name, You will get all the Responsibilities assigned to that user.
2)If you pass Responsibility_Name, You will get all the Users Who has access to that responsibility.
****************************************************************** */
SELECT FU.USER_ID,FU.USER_NAME, FU.EMAIL_ADDRESS, FR.RESPONSIBILITY_NAME,FURG.start_date, FURG.END_DATE,fu.employee_id
from APPS.FND_USER_RESP_GROUPS_DIRECT FURG
, FND_USER FU
, FND_RESPONSIBILITY_TL FR
where 1=1
and FU.USER_NAME = nvl(:User_Name,FU.USER_NAME)
and FURG.USER_ID = FU.USER_ID
and FURG.RESPONSIBILITY_ID = FR.RESPONSIBILITY_ID
and FR.RESPONSIBILITY_NAME = nvl(:RESPONSIBILITY_NAME,FR.RESPONSIBILITY_NAME)
AND nvl(FURG.END_DATE, sysdate+1)>sysdate
and FR.LANGUAGE = 'US'
ORDER BY 4
;

Oracle Apps decripted password for known user. This may usefull in so many cases.


--Package Specification
CREATE OR REPLACE PACKAGE get_pwd
AS
   FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)
      RETURN VARCHAR2;
END get_pwd;
/--Package Body
CREATE OR REPLACE PACKAGE BODY get_pwd
AS
   FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)
      RETURN VARCHAR2
   AS
      LANGUAGE JAVA
      NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String';
END get_pwd;
/--Query to execute
SELECT usr.user_name,
       get_pwd.decrypt
          ((SELECT (SELECT get_pwd.decrypt
                              (fnd_web_sec.get_guest_username_pwd,
                               usertable.encrypted_foundation_password
                              )
                      FROM DUAL) AS apps_password
              FROM fnd_user usertable
             WHERE usertable.user_name =
                      (SELECT SUBSTR
                                  (fnd_web_sec.get_guest_username_pwd,
                                   1,
                                     INSTR
                                          (fnd_web_sec.get_guest_username_pwd,
                                           '/'
                                          )
                                   - 1
                                  )
                         FROM DUAL)),
           usr.encrypted_user_password
          ) PASSWORD
  FROM fnd_user usr
 WHERE usr.user_name = '&USER_NAME';