Qury to find Related Items for an existing Item

declare
a varchar2(1000):= null;
b varchar2(1000):= null;
CURSOR c_cur IS
 select
ito.segment1 item,
--ito.description,
ITR.SEGMENT1 RELATED_ITEM,
--itr.description,
ML.MEANING RELATION
--ri.reciprocal_flag
from
mfg_lookups ml,
mtl_system_items itr,
mtl_system_items ito,
mtl_related_items ri
where ri.inventory_item_id=ito.inventory_item_id
and ri.organization_id=ito.organization_id
and ri.related_item_id=itr.inventory_item_id
and ri.organization_id=itr.organization_id
and ri.relationship_type_id=ml.lookup_code
AND ML.LOOKUP_TYPE(+)='MTL_RELATIONSHIP_TYPES'
AND ITO.SEGMENT1  ='XXXX';
begin
a:=  'Related Items exists for the Item '  ||'. Please enter Related Item from below list'|| CHR(10);
a := a||rpad('Related Item',19,' ')||rpad('Relation',20,' ')|| CHR(10)||rpad('*************',22,' ')||rpad('******************',23,' ')|| CHR(10);
FOR r_line IN c_cur
    LOOP
     b:=b||rPAD(R_LINE.RELATED_ITEM,25,' ')||rPAD(R_LINE.RELATION,20,' ')||CHR(10);
    end LOOP;
   dbms_output.put_line( a||b);
    EXCEPTION
when OTHERS then
dbms_output.put_line( 'No data found');
end;

Query to find User who is locking the table

SELECT objects.owner,
       objects.object_name,
       objects.object_type,
       user1.user_name         locking_fnd_user_name,
       login.start_time        locking_fnd_user_login_time,
       vs.module,
       vs.machine,
       vs.osuser,
       vlocked.oracle_username,
       vs.sid,
       vp.pid,
       vp.spid                 os_process,
       vs.serial#,
       vs.status,
       vs.saddr,
       vs.audsid,
       vs.process
  FROM fnd_logins      login,
       fnd_user        user1,
       v$locked_object vlocked,
       v$process       vp,
       v$session       vs,
       dba_objects     objects
 WHERE vs.sid = vlocked.session_id
   AND vlocked.object_id = objects.object_id
   AND vs.paddr = vp.addr
   AND vp.spid = login.process_spid(+)
   AND vp.pid = login.pid(+)
   AND login.user_id = user1.user_id(+)
--change the table name below

   AND objects.object_name LIKE '%' || upper('PO_HEADERS_ALL') || '%'
   AND nvl(vs.status,
           'XX') != 'KILLED';

Discoverer Universal Query

SELECT DISTINCT d.ba_name business_area
               ,c.obj_name folder_name
               ,decode(c.obj_type, 'SOBJ','From Database', 'CUO', 'Custom Query', 'COBJ', 'Complex Folder') obj_type_desc
               ,c.sobj_ext_table view_name
               ,disco_docs.doc_name "Discoverer Workbook",
                TRUNC (disco_docs.doc_created_date) "Workbook Create Date",
                CASE
                   WHEN INSTR
                          (disco_docs.doc_created_by,
                           '#'
                          ) = 0
                      THEN disco_docs.doc_created_by
                   WHEN INSTR (disco_docs.doc_created_by, '#') > 0
                   AND INSTR (disco_docs.doc_created_by, '#', 2) = 0
                      THEN (SELECT fu.user_name
                              FROM fnd_user fu
                             WHERE fu.user_id =
                                      SUBSTR (disco_docs.doc_created_by, 2, 5))
                   ELSE NULL
                END "Workbook Owner/Creator",
                disco_users.eu_username,
                CASE
                   WHEN INSTR
                          (disco_users.eu_username,
                           '#'
                          ) = 0
                      THEN disco_users.eu_username
                   WHEN INSTR (disco_users.eu_username, '#') > 0
                   AND INSTR (disco_users.eu_username, '#', 2) = 0
                      THEN (SELECT fu.user_name
                              FROM fnd_user fu
                             WHERE fu.user_id =
                                        SUBSTR (disco_users.eu_username, 2, 5))
                   ELSE (SELECT resp.responsibility_name
                           FROM fnd_responsibility_tl resp
                          WHERE resp.responsibility_id =
                                        SUBSTR (disco_users.eu_username, 2, 5))
                END AS "Shared Name / Responsibility"
              
              
              
           FROM biseul_us.eul5_documents disco_docs,
                biseul_us.eul5_access_privs disco_shares,
                biseul_us.eul5_eul_users disco_users,
                --
                biseul_us.eul5_qpp_stats  a,
                biseul_us.eul5_bas d,
                biseul_us.eul5_objs c,
                biseul_us.eul5_ba_obj_links f
               
                
          WHERE 1=1
            AND a.qs_doc_name= disco_docs.doc_name
            AND c.obj_id = SUBSTR (a.qs_object_use_key, 1, 6)
            AND c.obj_id = f.bol_obj_id
            AND d.ba_id = f.bol_ba_id
            AND disco_docs.doc_id = disco_shares.gd_doc_id
            AND disco_users.eu_username(+) NOT IN ('EUL5', 'PUBLIC')
            AND DISCO_USERS.EU_ID(+) = DISCO_SHARES.AP_EU_ID
            AND upper(disco_docs.doc_name) LIKE upper('% :Report sheet name here%');

Adding Concurrent Program to Request Group(Backend)

begin
FND_PROGRAM.ADD_TO_GROUP(PROGRAM_SHORT_NAME=>'HOLXORGNTNS',
program_application=>'Inventory',
request_group=>'All Inclusive GUI',
group_application=>'Inventory');
Commit;
Exception
when OTHERS then
DBMS_OUTPUT.PUT_LINE('Exception while adding'||SQLERRM);
End;
/

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;