Edi Yanto (何 萬 新)


Basically we can generate the data source from Oracle Report Builder or using XML Data Template, but in this time I show you another option (using PL/SQL) to generate data source when we just have a single group for our report generally.

Steps to create XML/BI Publisher report using PL/SQL package.

1. Write a PL/SQL package procedure to generate XML data based on your business requirement (simple sample package EY_GENERATE_SOURCE_PKG attached. Note: remove the extension .odt and extract the zip file)
–> Script to Generate XML Data

     — create a new context with the sql query
      l_ctx := DBMS_XMLGEN.newcontext (p_ref_cur);

      — add tag names for rows and row sets
      DBMS_XMLGEN.setrowsettag (l_ctx, p_row_tag);
      DBMS_XMLGEN.setrowtag (l_ctx, p_row_set_tag);

      — generate xml data
      x_xml_data := DBMS_XMLGEN.getxml (l_ctx);
      DBMS_XMLGEN.closecontext (l_ctx);

2. Register package procedure to Oracle Apps (Executable and Define Program) and add to Request Group

BIPPLSQL1

BIPPLSQL2

BIPPLSQL3

Read the rest of this entry »


When user OPERATIONS submit a request at System Administrator responsibility, this user can see the log and output files of this request. But the other user (ex: SYSADMIN) cannot do that even has the same responsibility. If we use the View All Concurrent Requests (System Administrator Mode) form, we only can see the request but not the output file of this request.

So, if we want to access the request output of the same responsibility from another user’s requests, then we need to follow the below setup steps:

1. Login as SYSADMIN with Functional Developer responsibility and update the object Concurrent Requests

1.a. Search Concurrent Requests object and click the link to update it.

CR_1

1.b. Create new Instance Set

CR_2

 

1.c. Enter Name, Code, Description and Predicate for the new instance Set.
Predicate:
&TABLE_ALIAS.request_id in (select cr.request_id from fnd_concurrent_requests cr where cr.responsibility_id = fnd_global.resp_id and cr.responsibility_application_id = fnd_global.resp_appl_id)
.

Read the rest of this entry »


We can apply Credit Memo (CM) to an Invoice as the invoice to be applied to is already there in AR by using API or interface table.

  • API
    • ARP_PROCESS_APPLICATION.CM_APPLICATION
    • AR_CM_API_PUB.APPLY_ON_ACCOUNT

  • Interface Table
    • RA_INTERFACE_LINES_ALL (REFERENCE_LINE_ID column)

If there is no Credit Memo in AR, you can using the AR_CREDIT_MEMO_API_PUB.CREATE_REQUEST API to create the Credit Memo directly and auto apply to an invoice.

Below is the query of corresponding invoice number for a credit memo with the creation date:

SELECT cm.trx_number cm_no,
cm.trx_date cm_date,
inv.trx_number inv_no,
inv.trx_date inv_date
FROM ra_customer_trx_all cm,
ra_customer_trx_all inv
WHERE inv.customer_trx_id = cm.previous_customer_trx_id


When you open the Report Builder application and it suddenly stopped working like the screen below. It doesn’t have the main menu of Report Builder.

RB_ERR

You can solve this problem by checking the CAUPREFS.ORA file under Oracle Developer Home directory and search if there are lines containing:

Reports.root_x = xxxxx
Reports.root_y = yyyyy

Read the rest of this entry »


We can achieve the Multi Org functionality for custom table with follow the below steps:
1. Create a custom table in custom schema, grant to user APPS, create synonyms for this table and insert some data.

CREATE TABLE xxey.ey_inv_all (
    inv_num     VARCHAR2(30),
    inv_date    DATE,
    org_id      NUMBER);

GRANT ALL ON xxey.ey_inv_all TO apps;
CREATE OR REPLACE SYNONYM apps.ey_inv_all FOR xxey.ey_inv_all;
CREATE OR REPLACE SYNONYM apps.ey_inv FOR xxey.ey_inv_all;

2. Apply RLS (Row Level Security) to EY_INV synonym using standard Virtual Private Database (VPD) policy function MO_GLOBAL.ORG_SECURITY and DBMS_RLS.ADD_POLICY API

BEGIN
dbms_rls.add_policy (
    ‘apps’, — Schema Name
    ‘EY_INV’, — synonym name
    ‘ORG_SEC’,– use policy_name ‘ORG_SEC’ standard policy
    ‘apps’, –function_schema
    ‘MO_GLOBAL.ORG_SECURITY’,–Standard MO VPD policy function
    ‘SELECT, INSERT, UPDATE, DELETE’, — statement_type
    TRUE, — update_check
    TRUE — Policy enable
);
END;

Read the rest of this entry »


We can find the blocking and blocked sessions with the below scripts.

SELECT vs.blocking_session, vs.SID, vs.serial#, vs.seconds_in_wait
FROM v$session vs
WHERE vs.blocking_session IS NOT NULL

OR

SELECT vs.username locking_usename, vs.osuser locking_osuser, vh.SID locking_sid, vs.status status, vs.module module, vs.program program_holding,
vsw.username waiter_username, vsw.osuser waiter_osuser, vw.SID waiter_sid, vsw.program program_waiting,
‘alter system kill session ‘ || ”” || vh.SID || ‘,’ || vs.serial# || ”';’ “Kill_Command_4_Blocking_Locks”,
‘alter system kill session ‘ || ”” || vsw.SID || ‘,’ || vsw.serial# || ”';’ “Kill_Command_4_Blocked_Locks”
FROM v$lock vh, v$lock vw, v$session vs, v$session vsw
WHERE (vh.id1, vh.id2) IN (SELECT id1, id2
FROM v$lock
WHERE request = 0
INTERSECT
SELECT id1, id2
FROM v$lock
WHERE lmode = 0)
AND vh.id1 = vw.id1
AND vh.id2 = vw.id2
AND vh.request = 0
AND vw.lmode = 0
AND vh.SID = vs.SID
AND vw.SID = vsw.SID

Once the locking sessions have been identified, we can kill such sessions using kill command scripts below for blocking or blocked sessions.

blocked_session


Do you ever think to find the environment variables path and ORACLE_HOME path from database table? Below are the scripts to achieve them:

  • Environment Variables

    select variable_name, value
    from   fnd_env_context
    where  variable_name like ‘%\_TOP’ escape ‘\’
    and    concurrent_process_id = (select max(concurrent_process_id) from fnd_env_context)
    order by 1;

    Result:
    VARIABLE_NAME   VALUE
    ————— ————————————————–
    AD_TOP          /oracle/VIS/apps/apps_st/appl/ad/12.0.0
    AF_JRE_TOP      /oracle/VIS/apps/tech_st/10.1.3/appsutil/jdk/jre
    AHL_TOP         /oracle/VIS/apps/apps_st/appl/ahl/12.0.0
    AK_TOP          /oracle/VIS/apps/apps_st/appl/ak/12.0.0
    ALR_TOP         /oracle/VIS/apps/apps_st/appl/alr/12.0.0
    AME_TOP         /oracle/VIS/apps/apps_st/appl/ame/12.0.0
    AMS_TOP         /oracle/VIS/apps/apps_st/appl/ams/12.0.0
    AMV_TOP         /oracle/VIS/apps/apps_st/appl/amv/12.0.0
    AMW_TOP         /oracle/VIS/apps/apps_st/appl/amw/12.0.0
    APPL_TOP        /oracle/VIS/apps/apps_st/appl
    AP_TOP          /oracle/VIS/apps/apps_st/appl/ap/12.0.0
    ……………….
    ……………….

Read the rest of this entry »

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 23 other followers


Edi Yanto
Oracle Applications Technical Consultant
View Edi Yanto's profile on LinkedIn

My Certifications




Follow me on Twitter

Archives

October 2014
M T W T F S S
« Sep    
 12345
6789101112
13141516171819
20212223242526
2728293031  

  • Edi Yanto: Hi Hiba, can you sent me the source file and the final layout that you want to edi.y4nto@gmail.com? Thanks, Edi Yanto
  • info20111: I would like to have a repeatable header even if one page split into 2 i worked it as you said but it dosn't work
  • Edi Yanto: Hi Venn, Yup, we can do it in Win XP, Vista or 7, but in Win 8/8.1 not has this Maximize function. Thanks, Edi Yanto

Top Rated

Visitors

Categories

Blog Stats

  • 95,108 hits

Users Online

web counter

Pages

Follow

Get every new post delivered to your Inbox.