Testing Wireshark HTTP request and response

Testing Wireshark HTTP request and response

Call PLSQL package.function() from shell

OUTPUT_RESPONSE=`sqlplus -s $FCP_LOGIN << EOSQL
!
set term off
set head off
set pages 0
set feedback off
set serveroutput on

declare
retcode NUMBER:=0;
begin
retcode := XXYH_XML_PARSER.PARSE('$SOURCE_CODE', '$l_get_response', $FCP_REQID, $PARENT_REQUEST_ID, '$PROD', '$FEED', '$ACTION','','','','','','','');
dbms_output.put_line(retcode);
end;
/
exit;
!

EOSQL
`

Get Responsibility Name and Resp Application Short Name in Shell

# -----------------------------------------------------
# Get Resp Name
# -----------------------------------------------------

RESP_NAME=`sqlplus -s $FCP_LOGIN << !
set term off
set head off
set pages 0
set feedback off
SELECT RESP.responsibility_name
  FROM fnd_responsibility_vl RESP
      ,fnd_concurrent_requests REQ
 WHERE RESP.responsibility_id = REQ.responsibility_id
   AND REQ.request_id         = $FCP_REQID
   AND rownum = 1
/
!
`

RESP_APPL_SHORT_NAME=`sqlplus -s $FCP_LOGIN << !
set term off
set head off
set pages 0
set feedback off
SELECT FAPP.application_short_name
  FROM fnd_application FAPP
      ,fnd_concurrent_requests REQ
 WHERE FAPP.application_id = REQ.responsibility_application_id
   AND REQ.request_id      = $FCP_REQID
   AND rownum = 1
/
!
`

Trace file details for Concurrent Request

SELECT request_id  request_id, 
      oracle_Process_id Trace_id, 
      req.enable_trace trace_flag, 
      decode(oracle_process_id, null, 'No trace file generated',
      dest.value||'/'||lower(dbnm.value)||'_ora_'||oracle_process_id||'.trc') trace_file_name, 
      prog.user_concurrent_program_name concurrent_program_name, 
      execname.execution_file_name|| execname.subroutine_name executable_name, 
      decode(phase_code,'R','Running') 
      ||'-'||decode(status_code,'R','Normal') concurrent_program_status, 
      ses.sid||','|| ses.serial# SID_serial, 
      ses.module 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 = 26784178 
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.executable_application_id = execname.application_id
and prog.executable_id=execname.executable_id; 

Steps to Enable Audit Trail in Oracle Applications

Steps to Enable Audit Trail in Oracle Applications by Sachin Dixit

Sample BULK collect example

set serveroutput on;

declare
cursor get is
select * from all_objects
where rownum <= 100
and object_type = 'TABLE';

TYPE t_get IS TABLE OF get%ROWTYPE;
l_get t_get;

j number:=0;
begin

open get;
 loop

  FETCH get BULK COLLECT INTO l_get LIMIT 20 ;
  EXIT WHEN l_get.count = 0;
 
    for i in 1 .. l_get.count loop
     j := j+1;
     dbms_output.put_line ('table_name('||j||')    '|| l_get(i).object_name);
    end loop;
   
 end loop;
close get;

exception
  when others then
dbms_output.put_line ('error : '||sqlerrm);
end;

Inventory Item Status Update API

API to update the item status.

/**** STEP 1) Insert data into pending interface table ****/

insert into mtl_pending_item_status
          (inventory_item_id,
          organization_id,
          status_code,
          effective_date,
          implemented_date,
          pending_flag,
          last_update_date,
          last_updated_by,
          creation_date,
          created_by)
     values(
          3093,   -- Item Segment
          7250,   -- Organization Id 
          'Inactive',  -- status
          sysdate,
          sysdate,
          'Y',
          sysdate,
          68382,
          sysdate,
          68382);

/**** STEP 2) Call below API code to do the actual change ****/

declare 
 x_return_status VARCHAR2(100);
 x_msg_count  NUMBER;
 x_msg_data  VARCHAR2(1000);

begin
        
 inv_item_status_pub.Update_Pending_Status (
                     p_api_version  => 1.0
                    ,p_Org_Id   => 7250  -- Organization Id
                    ,p_Item_Id   => 3093  -- Item Segment
                    ,p_init_msg_list  => FND_API.G_FALSE
                    ,p_commit   => FND_API.G_FALSE
                    ,x_return_status  => x_return_status
                    ,x_msg_count  => x_msg_count
                    ,x_msg_data  => x_msg_data );

commit;
end;