Query to fetch IR/ISO details

Query to fetch IR  (Internal Requisition) and ISO (Internal Sales Order) details:

SELECT OOH.*, PRH.*

  FROM APPS.OE_ORDER_HEADERS_ALL        OOH,

       APPS.OE_ORDER_LINES_ALL          OOL,

       APPS.OE_ORDER_SOURCES            OOS,

       APPS.PO_REQUISITION_LINES_ALL    PRL,

       APPS.PO_REQUISITION_HEADERS_ALL  PRH

 WHERE     1 = 1

       -- Order conditions

       AND OOH.ORG_ID = OOL.ORG_ID

       AND OOH.HEADER_ID = OOL.HEADER_ID

       AND OOL.CANCELLED_FLAG = 'N'

       AND OOL.ORDER_SOURCE_ID = OOS.ORDER_SOURCE_ID

       AND UPPER (OOS.NAME) = 'INTERNAL'

       -- AND OOH.ORDER_NUMBER = :P_ORDER_NUMBER -- Input your ISO number

       -- Link between Internal Sales Order and Internal Requisition

       AND OOL.SOURCE_DOCUMENT_LINE_ID = PRL.REQUISITION_LINE_ID

       AND OOL.SOURCE_DOCUMENT_ID = PRH.REQUISITION_HEADER_ID

       AND OOH.ORIG_SYS_DOCUMENT_REF = PRH.SEGMENT1 -- You can input Requisition number

       -- Requisition conditions

       AND PRH.REQUISITION_HEADER_ID = PRL.REQUISITION_HEADER_ID

       AND PRH.TYPE_LOOKUP_CODE = 'INTERNAL'

Junk characters in FND Output fnd_file.put_line

Junk characters in Output file when working with non English languages (example: Korean, Japan, Chinese etc.) :

We use fnd_file.put_line(fnd_output,..) to print the data in the output file. We see that data in the DB looks alright  but when the user opens the output file he sees junk characters in the output.  One reason could be with the user's session language. To resolve the issue,  from user preferences set the user's session language according to the characters that you are expecting in the output. Log in and Log out and open the output file from the request window. Now everything should be fine.

Purging Workflow data

The concurrent program "Purge Obsolete Workflow Runtime Data" can be used to purge EBS workflow data. The program internally uses WF_PURGE API.


FAQ on Purging Oracle Workflow Data (Doc ID 277124.1)

STATUS_CODE IN WF_DEFERRED

If a WF subscription's phase is less than 100 then the subscription will run synchronously. A value > 100 means it is asynchronous and it will be run by the WF_DEFERRED agent listener.

WF_DEFERRED.State values and meaning
                   0 = Ready
                   1 = Delayed
                   2 = Retained/Completed.
                   3 = Exception

CODE_COMBINATION_ID in the AP_EXPENSE_REPORT_LINES_ALL

CODE_COMBINATION_ID in the AP_EXPENSE_REPORT_LINES_ALL table. 

As confirmed in Doc ID 452438.1, this is an obsolete column. Code combinations are now stored in the CODE_COMBINATION_ID column of the AP_EXP_REPORT_DISTS_ALL table.

Recursive update of all records in a table with huge data

Method 1:

Begin
 loop
   update xx_test_table
   set  xx_flag='Y'
 where xx_flag='N'
  and rownum <= 5000;
exit when sql%notfound;
commit;
end loop;
commit;
end;

Method 2:

declare
type rowid_array_tbl_type is table of rowid;
ids_tbl rowid_array_tbl_type;

cursor cur is
select rowid as id
from xx_test_table
where xx_flag='N';

begin
      open cur;
      loop
         fetch cur bulk collect into ids_tbl limit 1000;
         exit when ids_tbl.count = 0;
forall i in ids_tbl.first .. ids_tbl.last
            update xx_test_table set  xx_flag='Y' where rowid=ids_tbl(i);
commit;   
      end loop;

    end;

Query to get data from fnd_log_messages for a given concurrent request id

SELECT l.module , l.message_text message, l.*
FROM apps.fnd_log_messages l,
            apps.fnd_log_transaction_context r
WHERE r.transaction_id = <:p_request_id > -- Input request id here
AND r.transaction_type = 'REQUEST'
AND r.transaction_context_id = l.transaction_context_id
ORDER BY l.log_sequence;

Query to get Oracle iExpense Line attachments

Query to get Oracle iExpense Line attachments

select report_line_id from apps.ap_expense_report_lines_all l where report_header_id=:P_expense_report_number; -- expense report number

SELECT fl.*
  FROM apps.fnd_documents_tl        fdtl,
       apps.fnd_documents           fd,
       apps.fnd_attached_documents  fad,
       apps.fnd_lobs                fl
 WHERE     fdtl.document_id = fd.document_id
       AND fd.document_id = fad.document_id
       AND fad.entity_name = 'OIE_LINE_ATTACHMENTS'
       AND fad.pk1_value = ':p_report_line_id'  -- line_id from expense line, use above query
       AND fl.file_id = fd.media_id
       and fdtl.language='US';

Query to get DFF segments for a given table name

Query to get DFF segments for a given table name

SELECT dlfx_col.application_id,
       dlfx_col.descriptive_flexfield_name,
       dlfx_col.descriptive_flex_context_code,
       dlfx_col.application_column_name,
       dlfx_col.end_user_column_name,
       dlfx_col.form_left_prompt,
       dlfx_col.description,
       dlfx_col.column_seq_num,
       dflx.APPLICATION_TABLE_NAME,
       dlfx_col.required_flag,
       dlfx_col.enabled_flag,
       dlfx_col.display_flag,
       dlfx_col.display_size,
       dlfx_col.flex_value_set_id
  FROM apps.FND_DESCR_FLEX_COL_USAGE_VL  dlfx_col,
       apps.FND_DESCRIPTIVE_FLEXS_vl     dflx
 WHERE     1 = 1
       AND dflx.application_id = dlfx_col.application_id
       AND dflx.application_table_name = UPPER (':P_TABLE_NAME') -- Input table name
       AND dlfx_col.descriptive_flex_context_code = :P_DFF_CONTEXT -- Input Context code, For global category attributes you can comment
       AND dlfx_col.DESCRIPTIVE_FLEXFIELD_NAME =
           dflx.DESCRIPTIVE_FLEXFIELD_NAME;

Query to find current running SQL for a given concurrent request id

Query to find current running SQL for a given concurrent request id

SELECT FCR.REQUEST_ID,
       D.SID,
       D.SERIAL#,
       D.OSUSER,
       D.PROCESS,
       C.SPID,
       E.SQL_TEXT
  FROM APPS.FND_CONCURRENT_REQUESTS   FCR,
       APPS.FND_CONCURRENT_PROCESSES  FCP,
       V$PROCESS                      C, 
       V$SESSION                      D,
       V$SQL                          E
 WHERE     FCR.CONTROLLING_MANAGER = FCP.CONCURRENT_PROCESS_ID
       AND C.PID = FCP.ORACLE_PROCESS_ID
       AND FCP.SESSION_ID = D.AUDSID
       AND D.SQL_ADDRESS = E.ADDRESS
       AND FCR.REQUEST_ID = :P_REQUEST_ID;

(In multi node -- use GV$)

Long Running Concurrent Requests

  SELECT ROUND (
               (NVL (ACTUAL_COMPLETION_DATE, SYSDATE) - actual_start_date)
             * 24
             * 60,
             2)
             run_time_mins,
         a.*
    FROM apps.fnd_amp_requests_v a
   WHERE     1 = 1
         AND actual_start_date > TRUNC (SYSDATE) - 1
         AND phase_code = 'R'
         AND ROUND (
                   (NVL (ACTUAL_COMPLETION_DATE, SYSDATE) - actual_start_date)
                 * 24
                 * 60,
                 2) >
             60

ORDER BY 1 DESC;