Formatting Date in Oracle XML(BI) Publisher

We can format dates in BI Publishes in the following three different ways,

1. Formatting Date using Microsoft Word’s native date format mask.
2. Formatting Date using Oracle’s format-date function.
3. Formatting Date using Oracle’s abstract date format masks.
4. Format with Calendar.

Lets me now explain little details about the above three ways, and below is the XML I am going to use in the below examples,

1. Formatting Date using Microsoft Word’s native date format mask?
In the BI Publisher Properties window, select type as Date and select the format from the drop down box.

Here is the output sample of all the three types of date’s we had in the XML. Format I selected is
DD-MM-YY.

2. Formatting Date using Oracle’s format-date function.
format-date() is an inbuilt function in BI Publisher.
Syntax:

In the above syntax MASK is an optional parameter. We will see more details about MASK later in this post.

To use this format-date function, under the BI Publisher Properties window -> keep type as Regular Text -> click on Advanced tab –> call format-date function with the tag name. (For time being we will not pass the MASK parameter.)

Here is the output sample of all the three types of date’s we had in the XML.

3. Formatting Date using Oracle’s abstract date format masks.
In this section we will seeing various MASK parameters that can be passed to MASK’

?> function. - See more at: http://flexfields.blogspot.ae/2011/01/bi-publisher-formatting-date.html#sthash.LrUuM9zp.dpuf3. Formatting Date using Oracle’s abstract date format masks.
In this section we will seeing various MASK parameters that can be passed to

Function.

Out of box we have the following MASK available for various format of dates,
Mask Constant                                               Output
SHORT                                                          1/26/11
MEDIUM                                                      Jan 26, 2011
LONG                                                           Wednesday, January 1, 2011
SHORT_TIME                                              1/26/11 8.10PM
MEDIUM_TIME                                          Jan 26, 2011 8.10PM
LONG_TIME                                                Wednesday, January 1, 2011 8.10PM
SHORT_TIME_TZ                                       1/26/11 8.10PM GMT
MEDIUM_TIME_TZ                                   Jan 26, 2011 8.10PM GMT
LONG_TIME_TZ                                        Wednesday, January 1, 2011 8.10PM GMT


The default MASK constant is MEDIUM.

In order to user these abstract’s you need to have your XML date data in canonical format as,
YYYY-MM-DDThh:mm:ss(+/-)HH:MM
(For example check out DATE_UTC tag value in the above XML.)

Here,
YYYY is the year
MM is the month
DD is the date
T is the separator between the date and time component
hh is the hour in 24-hour format
mm is the minutes
ss is the seconds
(+/-)HH:MM is the time zone offset from Universal Time (UTC) or Greenwich Mean Time

Here is the output for our XML data using the MEDIUM_TIME mask,

Now if you notice the output that we got earlier while using format-date with the default parameter, column for date_utc alone is showing Jan 27 were as the date we have in the XML is 26.

This is because of the time zone offset –08:00.

Where this timezone offset is nothing but, the time zone difference of the date that we passed in the XML with respect to GMT. So BI Publisher will automatically understand the timezone of the XML data, and print/convert it as per the BI Publisher Local Server time.

How to get Time zone offset of the data?
Ok now we know its important to pass the time zone offset in the XML data to use the MASK. Lets see how to get them in the XML data,

First Option:
If your data source is a data definition file, then no need to worry, the standard BI Publisher data definition engine by default will give the data in the canonical format with time zone offset. So you are good. No need of any extra coding.

If you do not want the template to change the date as per the time zone then, user the TO_CHAR function to print without the time zone offset. So that template engine will not modify the date as per the server time zone.

Second Option:
Lets say your data source is an RDF file. In that case use TO_CHAR function in your SQL query and bring the data in required canonical format.

Third Option:
Lets say, you do not have control over the data source. You have only access to the template and know what will be the time zone of the data that is going in come in the XML.

4. Format with Calendar:
Now that we had seen different types of formatting dates, let finally see how to use the above formats with various calendars.

Here is the supported calendar types,
- GREGORIAN
- ARABIC_HIJRAH
- ENGLISH_HIJRAH
- JAPANESE_IMPERIAL
- THAI_BUDDHA
- ROC_OFFICIAL (Taiwan)


I think, I had covered all the date formatting techniques in BI Publisher. If you feel I had missed anything or would like to explain some specific example, feel free to put on the comments.



Ur's
AmarAlam

Hide a Field in Oracle XML Publisher

1). If You Want to Hide a Field:

2). If you want to Hide The Data Based on One Field :

3). To Hide a Column We Can Use IF And END IF.


Ur's
AmarAlam

RICE components

Mostly an oracle apps technical consultant will be working on RICE components.
R--Reports
I--Interfaces
C--Conversions
E--Extensions(Forms personalization)

Reports:
For suppose I'm running a business named 'ALAM' which is wide spread across the globe.Now,I want to know how my business is running across the globe.I will ask a tech guy(oracle apps technical consultant) to develop a report.In that report I ask him to simply print the details of profit or loss,of every branch of my business across the world.He(tech guy) will come up with the report to me then I will come to know whether my company is running fine or not.Thereby, I can take necessary decisions to run my business fine.
Clearly we have seen that report will be developed by technical person,who must know the report building.
We build a report using sql,pl/sql.So,if your are strong in sql,pl/sql then its not a big deal to learn the report building.Its enough to learn the report developer tool.

Conversion:
As I said earlier I'm running a 'ALAM' business(which is factious).Suppose I'm using excel sheets to store the data.Now I want to install oracle apps(E-Business Suite) in my company. So,What ever data that is present in my excel sheets must also be present in my oracle apps tables(A table is a collection of rows and columns).Now I ask a tech guy to write a code such that my excel sheet data will get into oracle apps base tables.Then that tech guy will use 'conversion' to get the data from the legacy system(in our case it is excel sheet) into oracle apps base tables.

Interfaces:
Interfaces are similar to conversions.Conversion is a one-time process where as Interface is on-going process every now and then.We run interfaces daily or periodically.
Interfaces are of two types
1)Inbound Interface
2)Outbound Interface
Inbound Interface:Transferring the data from the legacy system(in our case it is excel sheet) into the Oracle apps base tables.
Outbound Interface:Transferring the data from the Oracle apps base tables into the legacy system(It might be any of these SAP,People soft etc).

Extensions:
Extensions are nothing but personalizing the forms.In oracle e-business suite we have some where around 5000 to 6000 forms.In my 'ALAM' business i want to customise the po(purchase order) form of the oracle apps then i ask the tech guy to do that.He will use form builder tool to do that.


Ur's
AmarAlam

Oracle Apps Technical Consultant

Oracle Apps is an ERP(Enterprise Resource Planning),nothing but effective planning of the resources in an Enterprise.An Enterprise is  'A business organization' like Ford,Reliance...etc.
An insight into the organization for the higher level authorities(in that organization) always make them to take decisions for the development of the organization.ERP makes the details of the organization available to the managers at the click of a mouse.Thereby,Managers and the employees in the organization can collaborate for the success of the company by taking effective decisions.
Without ERP It would be possible to run the business effectively but its a tedious process coz there are many departments involved in an organization.Gathering all that data and summing up it for the result is a nightmare.
ERP comes with the facility that it puts all the data of an organization at one place,and also we can get the result of its functioning at the click of a mouse.
So,many organizations are using ERP for their business.Some of the ERPs in the market are SAP, JDedwards, Siebel, Peoplesoft.
As we are now learning the basics of Oracle apps for a technical consultant,We will only discuss about the ERP,Oracle apps.
In oracle apps we have three roles
1)Apps technical consultant.
2)Apps functional consultant.
3)Apps DBA(Database Adminstrator).
In order to start your career as oracle apps technical consultant,you must have a good command on sql,pl/sql. and a little bit of knowledge on UNIX commands.
For an organization to use this ERP,it has to first install the Oracle apps later on according to the company's requirement it asks the tech guys(We the oracle apps technical consultants) to customize the application.This is how we(Oracle apps technical consultants) get the work to do.


Ur's
AmarAlam

How To Enable About This Page in Oracle Apps

Navigate to System Administrator --> Profile --> System

Profile: FND: Diagnostics
Set the site value to Yes

Logout and log back in.


Ur's
AmarAlam

Concurrent Program Phase codes and Status Codes

Concurrent Program Phase Codes:

SELECT lookup_code, meaning

  FROM fnd_lookup_values

 WHERE lookup_type = 'CP_PHASE_CODE' AND LANGUAGE = 'US'

   AND enabled_flag = 'Y';

LOOKUP_CODE	MEANING
C	Completed
I	Inactive
P	Pending
R	Running

Concurrent Program Status Codes:

SELECT lookup_code, meaning

  FROM fnd_lookup_values

 WHERE lookup_type = 'CP_STATUS_CODE' AND LANGUAGE = 'US'

   AND enabled_flag = 'Y';

LOOKUP_CODE	MEANING
R	Normal
I	Normal
Z	Waiting
D	Cancelled
U	Disabled
E	Error
M	No Manager
C	Normal
H	On Hold
W	Paused
B	Resuming
P	Scheduled
Q	Standby
S	Suspended
X	Terminated
T	Terminating
A	Waiting
G	Warning

Ur's
AmarAlam

Deleting a Template From Back End

DECLARE  
   l_templateCode    varchar2 (100) := 'XX_AR_CUST_AUDIT_REPORT'; -- Template Code  
BEGIN
   FOR r IN (SELECT t1.application_short_name template_app_name,
                    t1.data_source_code,
                    t1.application_short_name def_app_name
               FROM xdo_templates_b t1
              WHERE t1.template_code = l_templateCode)
   LOOP
    
      xdo_templates_pkg.delete_row (r.template_app_name, l_templateCode);

      DELETE FROM xdo_lobs
            WHERE lob_code = l_templateCode
                  AND application_short_name = r.template_app_name
                  AND lob_type IN ('TEMPLATE_SOURCE', 'TEMPLATE');

      DELETE FROM xdo_config_values
            WHERE application_short_name = r.template_app_name
                  AND template_code = l_templateCode
                  AND data_source_code = r.data_source_code
                  AND config_level = 50;

  END LOOP;
END;


Ur's
AmarAlam

Value Sets in Oracle Applications

What is a value set and where is it used?
Value set is primarily the List of Values(LOV) to restrict and mantain consistencies in entering or selecting the values. It is also the place holders to allow user enter a value. Oracle Application Object Library uses value sets as important components of key flexfields, descriptive flexfields, and Concurrent Request Submission.

What are the Format Types the value set have?
* Character
* Number
* Time
* Standard Date, Standard Date Time
* Date, Date Time
Note that Date and Date Time value set formats are obsolete and are provided for backward compatibility only. For new value sets, use the the format types Standard Date and Standard Date Time.

What are the validation types?

    None:

* There is no validation done for this type of value set, hence allows user to enter any value.

    Independent:

* It provides a list of pre-defined values. The predefined values are entered seperately.

    Dependent:

* Same like Independent Value Set, except the List of Values shown to you will depends on which the Independent value you have selected in the Prior Segment.
* Must define your independent value set before you define the dependent value set that depends on it.
* Must create at least one dependent value for each independent value.

    Table:

* The list of value is created based on database tables
* Allows to write simple queries, joins, order by etc
* The value, meaning and ID can be used to display a value, description to the value but return ID to the calling program or screen.
* Additional columns can also be displayed. The syntax is column "column title(size)",.... e.g. order_type "SO Order Type(40)"
* Can also create dependent values to filter LOV data based on parameter value selected earlier. This can be done using :$FLEX:.value_set_name in the where clause.

    Special & Pair:

Pair validation value set allows to select a range of concatenated Flex field segments as parameters to the report. The special value set is used to perform special validation. This is used to enter the entire key flexfield segment in the single parameter of the report/calling entity.

    Translatable Independent & Translatable Dependent:


* This is similar to Independent and Dependent value set except that translated values can be displayed to the user.

APIs to create value set:
The FND_FLEX_VAL_API package can be used to create different types of value sets.
* VALUESET_EXISTS - To check if value set exists
* DELETE_VALUESET - To delete value set. The value set can only be deleted if it is not being referenced by any program or entity.
* CREATE_VALUESET_NONE
* CREATE_VALUESET_INDEPENDENT
* CREATE_VALUESET_DEPENDENT
* CREATE_VALUESET_TABLE
* CREATE_VALUESET_SPECIAL
* CREATE_VALUESET_PAIR

What are the oracle tables that store value set information?
* FND_FLEX_VALUE_SETS
* FND_ID_FLEX_SEGMENTS
* FND_FLEX_VALUE
* FND_FLEX_VALIDATION_EVENTS
* FND_FLEX_VALUE_RULE_LINES
* FND_FLEX_VALUE_RULE
* FND_FLEX_VALUE_RULE_USAGE
* FND_FLEX_VALIDATION_TABLES


Ur's
AmarAlam

Handling NULL Values Using $FLEX$ in Value Sets

To Handle The Null Values In Value Set Please find the Below One, Hope it is Helpful for All Techies.

SELECT SHIPMENT_NUM
FROM RCV_SHIPMENT_HEADERS

WHERE CLAUSE: SHIPMENT_HEADER_ID in
(select shipment_header_id from rcv_shipment_lines
where shipment_line_status_code NOT IN('FULLY RECEIVED','CANCELLED')
and from_organization_id=NVL(:$FLEX$.IFF_WMS_ORGANIZATION1:NULL,from_organization_id)
and to_organization_id=:$FLEX$.IFF_WMS_ORGANIZATION)
and receipt_source_code not in ('VENDOR')


Ur's
AmarAlam