Concatenate fields and populate into single column in table using SQLLDR Control file

Recently we got a requirement where in we get a chart of accounts mapping file from legacy to EBS. Due to some legacy issues, we ended up using SQLLDR and Control file approach to load into EBS tables. The data file has  4 legacy segments and corresponding 8 EBS segments (Our client uses 8 segments) something like below

LS -- Legacy Segments
EBS -- EBS Segments

LS1, LS2, LS3, LS4 -->  EBS1.. EBS8


Table structure:
LS_CodeCombination  | EBS_CodeCombination
LS1.LS2.LS3.LS4      | EBS1.EBS2...EBS8

We used BOUNDFILLER to define filler columns in Control file.

OPTIONS (SKIP =1)
LOAD DATA
APPEND
INTO TABLE XX_LS_CC_EBS_CC_STG
Fields terminated by "," Optionally enclosed by '"'
TRAILING NULLCOLS
(
LS1 BOUNDFILLER,
LS2 BOUNDFILLER,
LS3 BOUNDFILLER,
LS4 BOUNDFILLER,
EBS1 BOUNDFILLER,
EBS2 BOUNDFILLER,
EBS3 BOUNDFILLER,
EBS4 BOUNDFILLER,
EBS5 BOUNDFILLER,
EBS6 BOUNDFILLER,
EBS7 BOUNDFILLER,
        EBS8 BOUNDFILLER,
LEGACY_CC "'Account Combination: '||:LS1 ||'.'||:LS2 ||'.'||:LS3 ||'.'||:LS4",
ORACLE_CC "TRIM(:EBS1||'.'||:EBS2||'.'||:EBS3||'.'||:EBS14|'.'||:EBS5)" );

API for role assignment to a user

Indirect Responsibility assignment.

In some organizations, role based responsibilities will be given instead of direct responsibility assignment. To assign an indirect responsibility you can use below API.

Begin

Wf_local_synch.PropagateUserRole(

p_user_name => 'XXUSER',

p_role_name =>  <Role>,

p_start_date=>'03-APR-18',

p_expiration_date=>null);

commit;

End;

To get list of active role assignments for a particular role:

select distinct assigning_role from wf_user_role_assignments

 where user_name='XXUSER'

 and end_date is  null;

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;

WSH_DELIVERY_DETAILS - INV_INTERFACED_FLAG & RELEASED_STATUS

WSH_DELIVERY_DETAILS.INV_INTERFACED_FLAG

N --> When delivery detail created/ New

Y --> Interfaced to MTI and processed successfully by Trx mgr

P --> Pending Interfaced

WSH_DELIVERY_DETAILS.RELEASED_STATUS

B: Backordered- Line failed to be allocated in Inventory

C: Shipped -Line has been shipped

D: Cancelled -Line is Cancelled

N: Not Ready for Release -Line is not ready to be released

R: Ready to Release: Line is ready to be released

S: Released to Warehouse: Line has been released to Inventory for processing

X: Not Applicable- Line is not applicable for Pick Release

Y: Staged- Line has been picked and staged by Inventory

Points to be noted:

Select delivery_detail_id, oe_interfaced_flag, inv_interfaced_flag from wsh_delivery_details where delivery_detail_id = &Delivery_detail_id;

If the above output does not show ‘Y’ for both oe_interfaced_flag and inv_interfaced_flag the same means that the Interface Trip Stop has not been successful (completed in error) and the same needs to be processed.

If the above output shows ‘P’ for inv_interfaced_flag the same means that the Interface Trip Stop has run into warning & the same needs to be processed

FND_CONC_PP_ACTIONS Action Types

The table FND_CONC_PP_ACTIONS stores about post processing actions.  The action_type column shows type of action according to below values:
1 indicates printing
2 indicates Notification
4 PLSQL procedure
6 Output post processor log.

Wait for concurrent program (WAITFORCONCURRENTPROGRAM) node details

The Workflow activity "Wait For Concurrent Program" waits for the concurrent program to be complete before proceeding with the other actions, Internally the flow is like below:

The activity uses PLSQL pakcage fnd_wf_standard.Seed_CB to verify if the program is complete and waits for average program time and then checks if the program is complete. If complete it returns a value to continue with the workflow. If the program is not complete and not running then it seeds concurrent request post processor actions (FND_CONC_PP_ACTIONS) to notify the workflow back when the program is complete.  The concurrent post processor complete post processor action i.e  FND_WF_STANDARD.callback to notify the workflow and to continue further.