I noticed trunc has a skillful way of working with dates.
First day of the month :
select trunc(sysdate,'MM') from dual;
Gives me 01-FEB-13
First day of the year:
select trunc(sysdate,'YY') from dual;
Gives me 01-JAN-13
Showing posts with label oracle. Show all posts
Showing posts with label oracle. Show all posts
Monday, 11 February 2013
Wednesday, 31 October 2012
Oracle Change/Modify PO Print Logo
Changing the Logo on PO Print is a little tricky. I say tricky purely because even though the PO Print program is a XML Publisher based report the layout part of the report is XSL-FO type. So all the details are in XML.
--------------------------------------------------------
Note: For doing this we need to have XML Publisher template builder installed. Pls follow the below instructions
Download the patch from Metalink. Latest version as of today is Patch 12395372: UPDATE FOR BI PUBLISHER DESKTOP 10.1.3.2.1 (5.6.3) size = 95.9 MB Product=BI Publisher (formerly XML Publisher)
Unzip this patch and then run BIPublisherDesktop.exe to install in a directory
--------------------------------------------------------
So lets get to the task.
Step 1: Create an RTF Document and embed the image into the document. I create a MSWord document and 'Save As' RTF document. And in Word (Menu)Insert -> Picture -> From File -> Select the image.
Step 2: Update the size of image to approx the size it appears in the document.
Step 3: Once you have template builder installed, the plug-in adds new menu options in Word. Tools -> Export -> XSL-FO Style Sheet. Screenshot attached
Step 4: An XML file is generated, Open the file and search for tag 'fo:instream-foreign-object' tag which is of our interest.
Step 5: Copy the tag contents from the beginning to the end of the tag 'fo:instream-foreign-object'. So this is our image xdofo:uid
Step 6: Open XML Publisher, Search for Template 'Standard Purchase Order Stylesheet', Duplicate the template entering required details. Download PO_STANDARD_XSLFO.xsl and rename to XXC_PO_STANDARD_XSLFO.xsl
Step 7: Open XXC_PO_STANDARD_XSLFO.xsl and search for 'fo:inline' which contains the Logo info. Uncomment the inline tags and replace the 'fo:external-graphic content' with the tags contents we copied in step 5.
The file should look like this.
Step 8: Save XXC_PO_STANDARD_XSLFO.xsl and attach the file to the template we created in Step 6. Also update the xsl:style sheet definition(at the beginning of the document) to refer to xdo namespace as follows.
'xsl:stylesheet version="1.0" xmlns:fo="http://www.w3.org/1999/XSL/Format" xmlns:ora="http://www.oracle.com/XSL/Transform/java/" xmlns:xdofo="http://xmlns.oracle.com/oxp/fo/extensions" xmlns:xdoxliff="urn:oasis:names:tc:xliff:document:1.1" xmlns:xdoxslt="http://www.oracle.com/XSL/Transform/java/oracle.apps.xdo.template.rtf.XSLTFunctions" xmlns:xlink="http://www.w3.org/1999/xlink" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"'
Step 9: Attach the new template to PO Document Types. (Document Type Layout), so the new template will be picked for the PO document creation
Step 10: For this article purpose lets simply the process and see if we can get the PO Document with the new Logo, by using the preview button in XML Publisher(for the new template we created in Step 6).Here we have it.
Leave me a comment if you have any questions.
--------------------------------------------------------
Note: For doing this we need to have XML Publisher template builder installed. Pls follow the below instructions
Download the patch from Metalink. Latest version as of today is Patch 12395372: UPDATE FOR BI PUBLISHER DESKTOP 10.1.3.2.1 (5.6.3) size = 95.9 MB Product=BI Publisher (formerly XML Publisher)
Unzip this patch and then run BIPublisherDesktop.exe to install in a directory
--------------------------------------------------------
So lets get to the task.
Step 1: Create an RTF Document and embed the image into the document. I create a MSWord document and 'Save As' RTF document. And in Word (Menu)Insert -> Picture -> From File -> Select the image.
Step 2: Update the size of image to approx the size it appears in the document.
Step 3: Once you have template builder installed, the plug-in adds new menu options in Word. Tools -> Export -> XSL-FO Style Sheet. Screenshot attached
Step 4: An XML file is generated, Open the file and search for tag 'fo:instream-foreign-object' tag which is of our interest.
Step 5: Copy the tag contents from the beginning to the end of the tag 'fo:instream-foreign-object'. So this is our image xdofo:uid
Step 6: Open XML Publisher, Search for Template 'Standard Purchase Order Stylesheet', Duplicate the template entering required details. Download PO_STANDARD_XSLFO.xsl and rename to XXC_PO_STANDARD_XSLFO.xsl
Step 7: Open XXC_PO_STANDARD_XSLFO.xsl and search for 'fo:inline' which contains the Logo info. Uncomment the inline tags and replace the 'fo:external-graphic content' with the tags contents we copied in step 5.
The file should look like this.
Step 8: Save XXC_PO_STANDARD_XSLFO.xsl and attach the file to the template we created in Step 6. Also update the xsl:style sheet definition(at the beginning of the document) to refer to xdo namespace as follows.
'xsl:stylesheet version="1.0" xmlns:fo="http://www.w3.org/1999/XSL/Format" xmlns:ora="http://www.oracle.com/XSL/Transform/java/" xmlns:xdofo="http://xmlns.oracle.com/oxp/fo/extensions" xmlns:xdoxliff="urn:oasis:names:tc:xliff:document:1.1" xmlns:xdoxslt="http://www.oracle.com/XSL/Transform/java/oracle.apps.xdo.template.rtf.XSLTFunctions" xmlns:xlink="http://www.w3.org/1999/xlink" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"'
Step 9: Attach the new template to PO Document Types. (Document Type Layout), so the new template will be picked for the PO document creation
Step 10: For this article purpose lets simply the process and see if we can get the PO Document with the new Logo, by using the preview button in XML Publisher(for the new template we created in Step 6).Here we have it.
Leave me a comment if you have any questions.
Unix Commands.
Some commands which I came across during my experience.
To Search for some piece of code or message text in Oracle_TOP or any directory in UNIX
find . | xargs grep -s 'XXC_MESSAGE'
Unix Command History: /bin/bash
Zip/Unzip in UNIX : zipinfo datazip.zip
Using TAR
---------
To ZIP : tar czf itemdata1.tar.gz datafiles
to Unzip : tar xvzf itemdata.tar.gz
Filesize in MB: ls -lh (in MB)
Count files/entries in a directory: ls -1 | wc -l
To Search for some piece of code or message text in Oracle_TOP or any directory in UNIX
find . | xargs grep -s 'XXC_MESSAGE'
Unix Command History: /bin/bash
Zip/Unzip in UNIX : zipinfo datazip.zip
Using TAR
---------
To ZIP : tar czf itemdata1.tar.gz datafiles
to Unzip : tar xvzf itemdata.tar.gz
Filesize in MB: ls -lh (in MB)
Count files/entries in a directory: ls -1 | wc -l
Oracle Set application Context
Below are tiny SQL's which can be used to set the Apps context.
BEGIN
dbms_application_info.set_client_info('103');
END;
BEGIN
FND_GLOBAL.Apps_Initialize (user_id => 5992 ,resp_id => 51498 ,resp_appl_id => 660 );
END;
In SQL developer sometimes the views don't fetch any data. Set the NLS Lang using the following SQL.
alter session set nls_language = 'AMERICAN';
BEGIN
dbms_application_info.set_client_info('103');
END;
BEGIN
FND_GLOBAL.Apps_Initialize (user_id => 5992 ,resp_id => 51498 ,resp_appl_id => 660 );
END;
In SQL developer sometimes the views don't fetch any data. Set the NLS Lang using the following SQL.
alter session set nls_language = 'AMERICAN';
Oracle SQL - GREATEST and LEAST
To select max from 2 columns
select greatest(4,2) from dual
To select min from 2 columns
select least(1,3) from dual
select greatest(4,2) from dual
To select min from 2 columns
select least(1,3) from dual
Search for Quote character in SQL
Ever wanted to find strings which contain Quote character. Using CHR(39) is the way.
Ex:
select table_name||CHR(39) from all_tables where table_name like '%'||CHR(39)||'%' and rownum < 5;
I am here trying to find table names which contain a quote char.
Ex:
select table_name||CHR(39) from all_tables where table_name like '%'||CHR(39)||'%' and rownum < 5;
I am here trying to find table names which contain a quote char.
Thursday, 15 September 2011
Organization selection in Oracle Applications.
Today I had to add Organization access to one of the forms. I found that is actually very easy to do.
Step 1: Add 'FND_ORG.CHOOSE_ORG; ' in the WHEN-NEW-FORM-INSTANCE trigger
Step 2: Define 4 parameters in the form; ORG_ID,ORG_CODE,ORG_NAME,CHART_OF_ACCOUNTS_ID
And you are all set, the list of Orgs appearing will be from the Organization Access defined for the current responsibility.
Step 1: Add 'FND_ORG.CHOOSE_ORG; ' in the WHEN-NEW-FORM-INSTANCE trigger
Step 2: Define 4 parameters in the form; ORG_ID,ORG_CODE,ORG_NAME,CHART_OF_ACCOUNTS_ID
And you are all set, the list of Orgs appearing will be from the Organization Access defined for the current responsibility.
Friday, 19 August 2011
Oracle Shipping Exceptions.
There might be scenarios when Pick Release is called you are not sure where to look into. WSH_EXCEPTIONS does the job of tracking the exceptions.
select * from WSH_EXCEPTIONS
select * from WSH_EXCEPTIONS
Thursday, 18 August 2011
Some More Date functions.
What day is today in SQL?
select to_char(sysdate,'DAY') from dual;
Extending the above to other useful stuff:
First Day of the Month:
select to_char(trunc(sysdate,'MM'),'DAY' ) from dual;
Ex: WEDNESDAY
Last Day of the Month:
select to_char(last_day(sysdate),'DAY') from dual;
Ex: MONDAY
Last Date of the month :
select last_day(sysdate) from dual
Ex:31-Aug-2011
Add time to a given date:
select to_char(trunc(last_day(sysdate))+13/12,'DD-MON-RRRR HH24:MI:SS') from dual
select to_char(sysdate,'DAY') from dual;
Extending the above to other useful stuff:
First Day of the Month:
select to_char(trunc(sysdate,'MM'),'DAY' ) from dual;
Ex: WEDNESDAY
Last Day of the Month:
select to_char(last_day(sysdate),'DAY') from dual;
Ex: MONDAY
Last Date of the month :
select last_day(sysdate) from dual
Ex:31-Aug-2011
Add time to a given date:
select to_char(trunc(last_day(sysdate))+13/12,'DD-MON-RRRR HH24:MI:SS') from dual
Thursday, 2 December 2010
Difference between two dates in hourss/minutes in Oracle.
We might need to find the difference between two dates columns in hours/minutes in many cases. Like trying to find how long some program ran. One of the easiest way I found is
SELECT to_number( to_char(to_date('1','J') +
(date1 - date2), 'J') - 1) days,
to_char(to_date('00:00:00','HH24:MI:SS') +
(date1 - date2), 'HH24:MI:SS') time
FROM mytable;
SELECT to_number( to_char(to_date('1','J') +
(date1 - date2), 'J') - 1) days,
to_char(to_date('00:00:00','HH24:MI:SS') +
(date1 - date2), 'HH24:MI:SS') time
FROM mytable;
Wednesday, 13 January 2010
Get Timestamp value for Date column in SQL Developer.
Most of the times when we look at Oracle tables from SQL Developer we find that the date column not showing the timestamp. A simple way to get the timestamp is using CAST function.
select cast(last_update_date as timestamp) from table_name.
Also there is a mroe easier way to see the timestamp in the settings of SQL Developer.
Tools -> Preferences -> Database -> NLS Parameters
Change Date Format to DD-MON-RRRR HH24:MI:SS
:)
select cast(last_update_date as timestamp) from table_name.
Also there is a mroe easier way to see the timestamp in the settings of SQL Developer.
Tools -> Preferences -> Database -> NLS Parameters
Change Date Format to DD-MON-RRRR HH24:MI:SS
:)
Thursday, 7 January 2010
SQL Loader Failing to load more than 255 characters.
This was one of my observations.
We had a sample dataload file defined as :
,mesg_type CHAR "ltrim(rtrim(:mesg_type))"
,mesg_priority CHAR "ltrim(rtrim(:mesg_priority))"
,mesg_description CHAR "ltrim(rtrim(:mesg_description))"
The program was failing to load the data when the length of the field is more than 254 characters.
Issue:
NOTE: The default data type in SQL*Loader is CHAR(255). To load character fields longer than 255 characters, code the type and length in your control file. By doing this, Oracle will allocate a big enough buffer to hold the entire column, thus eliminating potential "Field in data file exceeds maximum length" errors. Example:
Fix:
,mesg_description CHAR(4000) "ltrim(rtrim(:mesg_description))"
We had a sample dataload file defined as :
,mesg_type CHAR "ltrim(rtrim(:mesg_type))"
,mesg_priority CHAR "ltrim(rtrim(:mesg_priority))"
,mesg_description CHAR "ltrim(rtrim(:mesg_description))"
The program was failing to load the data when the length of the field is more than 254 characters.
Issue:
NOTE: The default data type in SQL*Loader is CHAR(255). To load character fields longer than 255 characters, code the type and length in your control file. By doing this, Oracle will allocate a big enough buffer to hold the entire column, thus eliminating potential "Field in data file exceeds maximum length" errors. Example:
Fix:
,mesg_description CHAR(4000) "ltrim(rtrim(:mesg_description))"
Friday, 27 March 2009
O2C - Order to Cash Flow.
Order to Cash flow starts with putting in an order in Order Management.
1. Enter an Sales order. A customer puts in a order for an Item. So put the customer details and the Item details/Quantity etc in the Line level. The order is in Entered Status.
Tables : oe_order_headers_all, oe_order_lines_all
2. Book the sales order, the flow status code column will be updated to reflect that the order is booked.
3. A entry is created in wsh_delivery_details in status 'Ready for Release' and the demand interface(Demand Interface) updated the demand in Inventory(MTL_DEMAND).
4. Reservations needs to be created for the Order placed. Reservations program (Schedule Orders) will create the reservations in Inventory.(MTL_RESERVATIONS)
5. Pick Release the Sales Order using 'Release Sales Orders' form, this steps picks the onhand inventory for the order. A delivery is created in this step. (wsh_new_deliveries). A move order is generated to move the order quantity(MTL_TXN_REQUEST_HEADERS/LINES)
6. Complete/Release Move order, which ensures the Pick Confirm for the delivery.
7. Ship Confirm the delivery, this will remove the demand and reservations and closes the order. Also interfaces the order line details into AR Interface (ra_interface_lines_all).
There are so many little details which I will try to add in a while.
1. Enter an Sales order. A customer puts in a order for an Item. So put the customer details and the Item details/Quantity etc in the Line level. The order is in Entered Status.
Tables : oe_order_headers_all, oe_order_lines_all
2. Book the sales order, the flow status code column will be updated to reflect that the order is booked.
3. A entry is created in wsh_delivery_details in status 'Ready for Release' and the demand interface(Demand Interface) updated the demand in Inventory(MTL_DEMAND).
4. Reservations needs to be created for the Order placed. Reservations program (Schedule Orders) will create the reservations in Inventory.(MTL_RESERVATIONS)
5. Pick Release the Sales Order using 'Release Sales Orders' form, this steps picks the onhand inventory for the order. A delivery is created in this step. (wsh_new_deliveries). A move order is generated to move the order quantity(MTL_TXN_REQUEST_HEADERS/LINES)
6. Complete/Release Move order, which ensures the Pick Confirm for the delivery.
7. Ship Confirm the delivery, this will remove the demand and reservations and closes the order. Also interfaces the order line details into AR Interface (ra_interface_lines_all).
There are so many little details which I will try to add in a while.
Subscribe to:
Posts (Atom)
Integrations Lead - Lessons learnt
Integrations have been my passion for a while but like anything tech there is no credit given when things go right but always heaps of pres...
-
For most outbound interfaces bursting to content server and then picking the file from UCM is the best approach for large extracts. Default...
-
While developing Cloud integrations I could not find a single place for all details like InterfaceID or jobdefinitionname or job name etc. ...
-
Order to Cash flow starts with putting in an order in Order Management. 1. Enter an Sales order. A customer puts in a order for an Item. S...