To reference a parent level element in a child level table — BI Publisher

To reference a parent level element in a child level table, you can use the “../” notation.

For example , if you have a block Department and a child block as Employee

<?xml version=”1.0″ encoding=”UTF-8″?>
<!– Generated by Oracle Reports version 10.1.2.3.0 –>
<MPLDEPRICIATION>
<LIST_G_DEPT>
<G_DEPT>
<DEPT_NAME>Oracle</DEPT_NAME>
<LIST_G_EMP>
<G_EMP>
<EMP_NAME>Arun</EMP_NAME>
<ENO>1234</ENO>
</G_EMP>

</LIST_G_EMP>

</G_DEPT>

</LIST_G_DEPT>

</MPLDEPRICIATION>

Now to reference the value of DEPT_NAME in G_EMP group , we  can use the following

<?../DEPT_NAME?>

You can always use the absolute path to reference any extract element anywhere in the

template. The absolute path starts with a backslash “/”.

This notation goes up to any level for the parent elements.

For example the absolute path for the above example would be  /G_DEPT/DEPT_NAME

The absolute path syntax provides better performance.

If we have 3 levels of hierarchy then we can use <?../../DEPT_NAME?>

Create New User from Beckend

Sometimes when a Development Instance is Cloned from Production Instance, and if some Users is not available in Production Instance will not be available in Development instance after cloning. User can be generated through

 

API :fnd_user_pkg.createuser

DECLARE

   l_user_name         VARCHAR2 (100) := 'USER_NAME'; -- 'USER_NAME';

   l_pwd               VARCHAR2 (100) := 'password123';

BEGIN

   fnd_user_pkg.createuser (x_user_name         => l_user_name

                           ,x_owner             => 'FND'

                           ,x_unencrypted_password => l_pwd

                           );

   --commit;

   fnd_user_pkg.addresp (username            => l_user_name

                        ,resp_app            => 'SYSADMIN'

                        ,resp_key            => 'SYSTEM_ADMINISTRATOR'

                        ,security_group      => 'STANDARD'

                        ,description         => NULL

                        ,start_date          => SYSDATE

                        ,end_date            => NULL

                        );

   COMMIT;

END;

Process to Enable DFF for a Lookup Value set

Create a DFF

Login to System Administrator
Application -> Flexfield -> Descriptive -> Segments
Search for Application = Application Object Library
Title = Common Lookups

You can see referenced field as "LOOKUP_TYPE"

Now if you want to enable your lookup, you need to uncheck "Freeze Flexfield Definitions" checkbox on the top left the DFF Segments screen.
this enables you to enter new DFF entries.

Now, enter create a line under Context Field Values 

Under "Code" enter your lookup ex: XXSYK_DEDUCT_ELEMENTS

Click on Segments

Enter the number, name, prompt, column and value set(if you have one) 

save and compile the DFF.

Now, switch the responsibility to Application Developer to see the DFFs enabled in lookup

Open the common lookups screen and query for the lookup name you have given under "Code" of DFF setup.

and thus your DFF is enabled.

Difference between CHR(10) Line Feed (\n) and CHR(13) Carriage Return (\r)

 CHR(13) is carriage return. CHR(10) is line feed.

They are different. Most of the editors will treat them as new line, but not all

The new line is actually based on your OS - If I am not wrong for Unix it is CHR(10).
For MAc it is CHR(13). For Windows it is combination of both.

So it all depends on, what editor, what OS - how they will interpret these characters..

This difference is viewable during you do file transfer between different OS.

Example :---

SQL> select
       replace('a,b,c,d,e',',',CHR(10)) x1,
       replace('a,b,c,d,e',',',CHR(13)) x2,
       replace('a,b,c,d,e',',',CHR(13)||CHR(10)) x3,
       replace('a,b,c,d,e',',',CHR(10)||CHR(13)) x4
   from
   dual;

X1        X2        X3            X4                                                                                                        
--------- --------- ------------- -------------                                                                                             
a         a b
c
d
e a             a                                                                                                         
b                   b             
b                                                                                                        
c                   c             
c                                                                                                        
d                   d             
d                                                                                                        
e                   e              e       

Use of CHR(0) String

Chr(0) is one of the ascii characters Where as NULL is undefined or unknown value.

SELECT ascii(chr(0))  Value FROM DUAL;

Value
-------------
0



Chr(0) is different from NULL.

SELECT DECODE(CHR(0), NULL, 'NULL', 'NOT NULL') Value from dual;

Value
--------
NOT NULL




SELECT length(chr(0)) FROM dual;

LENGTH(CHR(0))
--------------
1

SELECT length(NULL) FROM dual;

LENGTH(NULL)


You can use chr(0) to diplay a blank line with DBMS_OUTPUT.PUT_LINE.

Example

SQL> begin
dbms_output.put_line('This is first line');
dbms_output.put_line( Chr(0));
dbms_output.put_line('This is second line');
end;
/
This is first line

This is second line

PL/SQL procedure successfully completed.

Calculate Sales Order Tax and Lines / Order Total

Standard Package to show Sales order Line Basic/ Tax Value and Total Value (Basic+Tax)

Sometime we Need to show Line wise or Complete Order Taxes / Tax+Basic in few reports, for this , oracle has provided a standard Package to Calculate these values, based on parameter passed.



Package Name: oe_totals_grp.get_order_total

Below are the Illustration of this package with example.

For Example: 1 Order have 3 lines, and each one have taxes attached on it.


  Header ID: 23096

  Line ID: 36492----Line Value=1,045.00----Tax on this Line ----87.52----TOTAL VALUE---1132.52
  Line ID: 36494----Line Value=505.00-----Tax on This Line ----42.3-----T0TAL VALUE---547.3
  Line ID: 36495----Line Value=1,750.00---Tax on this Line ----146.56---T0TAL VALUE---1896.56



  Calculate Line wise Tax

    select nvl(oe_totals_grp.get_order_total (HEADER_ID, LINE_ID, 'TAXES'),0) from dual;
 
 For Ex:
 
    select nvl(oe_totals_grp.get_order_total (23096, 36492, 'TAXES'),0) LINE_Tax from dual;
   
    Output : LINE_Tax=87.52
 
 
  Calculate Order Taxes (All Lines)


    select nvl(oe_totals_grp.get_order_total (HEADER_ID, NULL, 'TAXES'),0) from dual;
 
 
   Ex:
 
   select nvl(oe_totals_grp.get_order_total (23096, null, 'TAXES'),0) Order_Tax from dual;
 
 
   Output : Order_Tax: 276.38
 
 
 
 
 
    Calculate Line wise Value (Without Tax)

    select nvl(oe_totals_grp.get_order_total (HEADER_ID, LINE_ID, 'LINES'),0) from dual;
 
 
    Ex:
 
      select nvl(oe_totals_grp.get_order_total (23096, 36492, 'LINES'),0)  LINE_BASIC from dual;
   
   
      Output : LINE_BASIC=1045
   
 
  Calculate All Lines Total (Without Tax)

    select nvl(oe_totals_grp.get_order_total (HEADER_ID, NULL, 'LINES'),0) from dual;
 
 
     Ex:
 
   select nvl(oe_totals_grp.get_order_total (23096, null, 'LINES'),0) Order_Basic from dual;
 
 
   Output : Order_Basic: 3300
 
 
 
 
   Calculate Line wise Value ( With Tax)

    select nvl(oe_totals_grp.get_order_total (HEADER_ID, LINE_ID, 'ALL'),0) from dual;
 
 
     Ex:
 
      select nvl(oe_totals_grp.get_order_total (23096, 36492, 'ALL'),0)  LINE_TOTAL from dual;
   
   
      Output : LINE_TOTAL=1132.52
 


    
  Calculate Order Total Value (With Tax)

    select nvl(oe_totals_grp.get_order_total (HEADER_ID, NULL, 'ALL'),0) from dual;
 
     Ex:
 
   select nvl(oe_totals_grp.get_order_total (23096, null, 'ALL'),0) Order_Total from dual;
 
 
   Output : Order_Total: 3576.38

Printing Address in standard Format in Formatted reports ,

Printing Address in standard Format , specified in  Oracle Package.


Create a Formula in Report, and pass the below values from Query.



function CF_SHIP_ADDRESSFormula return Char is
lv_address varchar2(2000);
begin

lv_address:= TRIM(arp_addr_pkg.format_address(NULL
                                   ,:ACCOUNT_NAME
                                   ,:SHIP_TO_ADDRESS1
                                   ,:SHIP_TO_ADDRESS2
                                    ,:SHIP_TO_ADDRESS3
                                  ,:SHIP_LOC_CITY||'|'||:SHIP_LOC_STATE||'|'||:SHIP_LOC_POSTAL_CODE
                                       ,NULL
                                       ,null
                                       ,NULL
                                       ,null
                                       ,null
                                       )
           );
return REPLACE(REPLACE(lv_address,', ',','||chr(10)),'|',',');
exception
when others then
return null;

end;