Showing posts with label oracle. Show all posts
Showing posts with label oracle. Show all posts

Monday, 11 February 2013

Oracle First day of given month or given year.

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

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.

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

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';

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

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.

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.

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

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

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;

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

:)

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))"

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.

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...