Setting Org Context in R12 ( MOAC Context )

Oracle Apps tables ending with ALL holds transaction data for multiple org ( Operating Units).
In 11i we had views on these tables. It requires setting context in order to fetch data from these views.This  is for security concerns as these objects holds transaction details.

This is one of major difference in application architecture between 11i and R12.
In R12 oracle uses VPD (Virtual Private Database) to secure these transactional data.We shall discuss about VPD and MOAC concepts in other post.  

In order to retrieve data from transactional objects, set policy context first ( as below ) -

BEGIN
      MO_GLOBAL.set_policy_context ('MODE' char(1),Org_ID number);
END;

MODE - This is  either "S" - For Single Operating Unit
                                 "M" - For Multiple Operating Unit

Org_ID - Operating unit (Value from column ORG_ID in all transactional objects)
                This is mandatory for "S" mode.

Example -


BEGIN
      MO_GLOBAL.set_policy_context ('S' ,204);
END;

XML Bursting

Requirement -Split the generated report output into multiple reports based on no of suppliers.

For example - Report is having 3 invoices, 2 for one supplier and 1 for second. concurrent program will generate one file as output having  all 3 invoice detail.
Project need is to split output either on 3 files (one for each invoice) or 2 files ( for 2 supplier). 
  
XML bursting concept help us to cut down the generated output in multiple pieces based on requirement.
Also we can send output through email using this service. 

Assumption - SMTP configuration should be available

Steps for XML bursting -
1) Create and set temp folder  
   - Create one folder in unix box ( application server) and set the temp path in application
       - Go to XML administrator responsibility
       - Administrator Function
       - Give the folder path for TEMP directory

2)Create the bursting control file
    Create the control file. control file contains split logic and delivery method, which will be used by bursting engine. Sample file as below-

   -     

    <xapi:requestset xmlns:xapi="http://xmlns.oracle.com/oxp/xapi" type="bursting">

          <xapi:request select="/XXXXX/LIST_G_SUPPLIER_NO/G_SUPPLIER_NO">

  <xapi:delivery>

   <xapi:email id="123" server="smtp.host.com" port="25" from="${EMAILFROM}" reply-to="${EMAILFROM}">

   <xapi:message id="123" to="${SUPPLIERSITEEMAIL}" cc="${EMAILCC}" attachment="true" subject="XML Bursting">This program is for xml bursting check

</xapi:message> </xapi:email> </xapi:delivery>

   <xapi:document output="AttachmentName" output-type="pdf" delivery="123">

  <xapi:template type="rtf" location="xdo://APPSHORTNAME.TEMPLATECODE.en.00/?getSource=true" />

 </xapi:document>

</xapi:request></xapi:requestset>

*${XML_TAG_NAME}
Request – Describe group level, on which bursting is required. In above example bursting will be on G_SUPPLIER_NO level
Delivery – How to send the split data ( Email, Fax, Printer etc).
Document output – Subject and format of output    
Template Type – Related template information. Location can have temp directory path ( make sure your rtf file is placed at temp directory too) too  or use the above syntax only.

***en is the territory code.Either use the correct territory code or upload rtf file without territory name.

3)Place the control file –

 XML publisher responsibility >> Data Definition >> Search for your Data Definition >> Bursting Control file >> Add File

4)Invoke Bursting Concurrent Program – XDOBURSTREP is oracle provided standard concurrent program for bursting. Invoke this concurrent program in your program at the last.
   This will read the instructions given in control file and perform bursting.

l_request_id := FND_REQUEST.SUBMIT_REQUEST (
    'XDO',
    'XDOBURSTREP',
    'XML Publisher Report Bursting Program',
    '',
    FALSE,
    'N',
    fnd_global.conc_request_id, 'Y',
    chr(0), '', '', '', '', '', '',
    '', '', '', '', '', '', '', '', '', '',
    '', '', '', '', '', '', '', '', '', '',
    '', '', '', '', '', '', '', '', '', '',
    '', '', '', '', '', '', '', '', '', '',
    '', '', '', '', '', '', '', '', '', '',
    '', '', '', '', '', '', '', '', '', '',
    '', '', '', '', '', '', '', '', '', '',
    '', '', '', '', '', '', '', '', '', '',
    '', '', '', '', '', '', '', '', '', '');

Run Time Restriction on Concurrent Program Parameter Values

Requirement – Have one program with 2 parameters. 1) Email Options and 2) Invoice Status  

                     Email option can be Yes or No – No will be default value

                    Invoice Status can have Approved, Cancelled, Needs reapproval and Never approved options.

As per requirement  user should be able to pick any of 4 possible Invoice Status, if Email option is No but  in case if Email option is Yes

User should be restricted to select any value other than Approved.

Solution –  This can be easily achieved if we create a custom table having invoice status values along with one additional column to map Email options value.                       

-          Create one custom table using below script

CREATE TABLE O_APPROVAL_STATUS_TAB

(

  status_id                                NUMBER,

  inv_approval_status                             VARCHAR2(50),

  description                VARCHAR2(50),

  email_flag                 VARCHAR2(5)

)

/

GRANT ALL ON O_APPROVAL_STATUS_TAB TO APPS

/

insert into O_APPROVAL_STATUS_TAB values(10,'APPROVED','Validated','Y')

/

insert into O_APPROVAL_STATUS_TAB values(20,'APPROVED','Validated','N')

/

insert into O_APPROVAL_STATUS_TAB values(30,'CANCELLED','Calcelled','N')

/

insert into O_APPROVAL_STATUS_TAB values(40,'NEEDS REAPPROVAL','Needs Revalidation','N')

/

insert into O_APPROVAL_STATUS_TAB values(50,'NEVER APPROVED','Never Validated','N')

-          Create  custom value set O_APPROVAL_VS

    

   Go to Edit Information and select table name as O_APPROVAL_STATUS_TAB

-          Set Where condition as  email_flag=$:FLEX$.Yes_No

-          Create one concurrent Program and define 2 parameters

a)      Email Option              – Yes_No                                             ( Value Set )

b)      Invoice Status            – O_APPROVAL_VS                        ( Value Set )

-          Assign this program to any request group and validate the results

Output Validation – Run the program and change the Email option values Yes and No.

 

OPP Service log file and location on server

In previous post on OPP, we learnt about OPP services using application. Below mentioned SQL is to identify the log file name and location in unix server.

If concurrent program ends in warning due to any OPP service failure, run below sql by passing request id.

SELECT fcpp.concurrent_request_id req_id,
  fcp.node_name,
  fcp.logfile_name
FROM fnd_conc_pp_actions fcpp,
  fnd_concurrent_processes fcp
WHERE fcpp.processor_id        = fcp.concurrent_process_id
AND fcpp.action_type           = 6
AND fcpp.concurrent_request_id = &REQ -- 12345

Req_id      Node_name          LogFileName
12345  ebs002learn   /oracle/ebs/inst/apps/ebs_002learn/logs/appl/conc/log/FNDOPP383669.txt
 
Open putty
Login to server ebs002learn
open specified Logfile and search for your request id (12345) for detail log message

**** Above SQL is from my DBA Friend ( Jyothi )

Concurrent Program Parameter Validation

Requirement – Input should be in specific format for one parameter. Let’s say we have one free text parameter (email) which must be ended with “@OLearning.com” .

It should not accept if email is not having @OLearning.com at end

How to Achieve -

Create one value set of validation type = Special 

Press Edit Information and than Select event = Validate

Write validation logic in Function section

FND PLSQL "DECLARE
lv_str varchar2(100) := :!VALUE;
lv_com varchar2(20);
lv_standard varchar2(100) :='@OLearning.com';
BEGIN
IF (lv_str IS NOT NULL) THEN
 Select substr(lv_str,instr(lv_str,'@'

,1),length(lv_str)) INTO
lv_com from dual;

       IF lv_com<>lv_standard  THEN
                 fnd_message.set_name('XXXXX','@OLearning is Required');
           fnd_message.raise_error;
   END IF;
END IF;
END;"

                Go to concurrent program definition page

                Press parameter button

                Create parameter for email address and associate above created value set (OLearning_VS)

                Save the program

Validation –

Go to  respective responsibility

Run the concurrent program

Pass the email parameter = sandeep@gmail.com and tab out

               

Pass the email parameter = sandeep@OLearning.com   and tab out

               

Different Header and Footer for First Page - XML Publisher Report

Requirement  was to report invoice details in xml publisher in PDF format. If there is multiple pages for any invoice, it should show different header details.

In rtf it is very easily achievable using MS word features.

• 1)      Open the rtf file
• 2)      Go Header & Footer section
• 3)      Check the flag “Different First Page”
• 

• 4)      Write the Header/footer Details
• 
• 5)      Go to the second page (if rtf has only one page, put some line space to reach second page)
• 6)      Create second header/footer details
• 
• 7)      Remove the extra line space created in step 5
• 8)      Load the data source
• 9)      Preview the output 
• 

  
  

  
  

  
  

  
  

  
  

  
  

Pivot Function - Transform table rows to colums

PIVOT is very powerful option when data is available in table and requirement is to convert rows in column and display data for those column. One group function is mandatory to use this option.

Syntex -  
SELECT * FROM ( 

SELECT ColumnA,ColumnB,ColumnC,ColumnD 

FROM TABLE_NAME 

WHERE conditions)

PIVOT ( groupFunction(ColumnD) FOR ColumnC in (Values of ColumnC))

Example- assume we have employee expense details for each month. Now requirement is to generate a report which will display each employee’s monthly expenses in column.

Create Table & Insert Values>>

create table test_sandeep(name varchar2(10),dept varchar2(10),month number,amount number);

insert into test_sandeep values('Sandeep','ABC',1,300)

insert into test_sandeep values('Sandeep','ABC',1,120)

insert into test_sandeep values('Sandeep','ABC',2,400)

insert into test_sandeep values('Sandeep','ABC',3,200)

insert into test_sandeep values('Kumar','DEF',2,600)

insert into test_sandeep values('Kumar','DEF',3,700)

insert into test_sandeep values('Kumar','DEF',3,450)

To obtain monthly basis expense amount, we used to write below group by code -

SELECT name,

         dept,

         month,

         SUM (amount)

    FROM test_sandeep

GROUP BY name, dept, month

Using Pivot, we can obtain same data in below manner

SELECT *

  FROM (SELECT name,

               dept,

               month,

               amount

          FROM test_sandeep) PIVOT (SUM (amount) FOR month IN (1 "Jan", 2 "Feb", 3 "Mar"))

OPP Service Failure - Output Post Processing

Concurrent program for XML reports very often ends in warning with below message -

“Beginning post-processing of request $REQUEST_ID on node $APP_HOST at 13-OCT-2011 17:21:02.
 Post-processing of request $REQUEST_ID failed at 13-OCT-2011 17:21:04 with the error message:
 One or more post-processing actions failed. Consult the OPP service log for details.”

Cause - This issue can occur because of either opp service failure or custom xml template.

Fix 1- Check with DBA about opp service status and ask if it is not active.
Fix 2- If opp service is running, it can be issue with our custom xml template. Analyze the opp service log to catch the root cause.


To get opp service log - go to 
1) System administrator >>> Oracle Applications Manager >>concurrent Managers 

2) Select “output post processor” and click  “View Details”

3) Click on the output post processor to display all process running for OPP

4) Click on the log button  - to see the OPP request log


Go through the log file and get the error point. It have some keyword  which is causing issue.
Now check the properties of data field on template, Bad keyword will be available at some places.
correct it and save the template.

Run the program again..It shall complete normal and you can see your expected output file. 

Profiles 4 OAF pages

Below are the list of few profiles, you must know to work on OAF pages.

1) Show Diagnostics option on OAF pages -
    Set profile "FND:Diagnostics"= Yes


2) Display Personalization link on OAF pages -Set  any of profile options
          "Personalize Self-Service Defn"= YES
                          OR
          "Disable Self-Service Personal"=No


3) Display Personalization link for each region -
     Set profile "FND: Personalization Region Link Enabled" =YES

4) Show About this page on OAF Pages - enable any profile option mentioned above in 1 and 2.

Soft Link or Symbolic Link

S Link ( soft link or symbolic link) is required to read a shell script (.prog file) through a concurrent program.

We create a concurrent program of executable type as HOST. The host file extension is .prog. We must have to create a soft link to access this host file.

Host File Location – It is available at bin folder on custom top. ($XXXXX_TOP/bin)

Soft Link Creation –  ln -s  $FND_TOP/bin/fndcpesr  sourcefile

 ln -s  $FND_TOP/bin/fndcpesr  $v_custom_top/bin/XXXXX_TEST_SOFTLINK

The above command will create the soft link for XXXXX_TEST_SOFTLINK.prog file. The new link will look as below – 

Known Issues – Sometime concurrent program could not read the soft link, though it is available.  Below is the error message can be found in log file

FND-CP-ESP: Child: exec:: No such file or directory

/oracle/…/…/xxxxx/12.0.0/bin/XXXXX_TEST_SOFTLINK

Program exited with status 1

Root Cause – This issue appear if there is any control M character in prog file, which is the source for soft link.

Check for control M Char -$ ./XXXXX_TEST_SOFTLINK.prog

if output is file contents => No control M character available

if output has control M Char => 

Resolutions – Remove the control M character and re create the soft link.

1)      Delete the existing soft link   >>$  rm    XXXXX_TEST_SOFTLINK  

2)      Remove the control M character >>$ cat    XXXXX_TEST_SOFTLINK.prog | col -b >  XXXXX_TEST_SOFTLINK1.prog

3)   Delete the existing prog file  >> rm XXXXX_TEST_SOFTLINK.prog
4)   Rename the new prog file to old  >>$  mv     XXXXX_TEST_SOFTLINK1.prog  XXXXX_TEST_SOFTLINK.prog

4)      Re create the soft link for XXXXX_TEST_SOFTLINK.prog

**PS – Do not copy prog file in  binary mode or zipped mode to server. It may include control M character to prog file.