Credit memo and its Invoice

I had few difficulties in finding the corresponding invoice number for a credit memo, for one my issue in Oracle incentive compensation and googled for it, but have not found anything useful.

Finally i have drafted the below simple query with the help of my team mate.

select A.TRX_NUMBER CMNO,
A.TRX_DATE CMDATE,
O.TRX_NUMBER INVNO,
O.TRX_DATE INVDATE
FROM RA_CUSTOMER_TRX_ALL A,
RA_CUSTOMER_TRX_ALL O
where
O.CUSTOMER_TRX_ID = A.PREVIOUS_CUSTOMER_TRX_ID
and A.trx_number like &enter_the_invoice_number

the above gives the corressponding the invoice number for a credit memo with the creation date.

Shipping Dock

What does a dock mean ?

A dock means a place where loading and unloading of goods happens .

Learn WMS with me ....

Just thought i would scribble whatever i learn in the WMS ........

Starting again ...

Its almost six months ,i did not blog anything ,i thought i would start this again ...I hope i would write this very often ....Im now involved in a large enterprise transformation R12 implementation .Thinking of writing a lot .....

-Saravana prakash

How to display both Vertical and Horizontal Scroll Bars in Detail Block in Oracle Forms 6i?

Problem
A Detail Block in Oracle Forms 6i with many fields in tabular format needs not only a Vertical Scroll Bar, but also a Horizontal Scroll Bar so that we can see all the information we need for a Record.
But the problem is that a Data Block supports only one Scroll Bar at a time. We can choose to use or not a Scroll Bar, setting the Show Scroll Bar property to Yes or No at the Data Block level. If the Scroll Bar will be displayed, we can establish its orientation setting the Scroll Bar Orientation property to Horizontal or Vertical. So how to display both Vertical and Horizontal Scroll Bars in Detail Block in Oracle Forms 6i?

Solution
Create the Detail Data Block setting Show Scroll Bar property to Yes and Scroll Bar Orientation property to Vertical. Add some fields from the Detail Block on the Content Canvas.
Create a Stacked Canvas and put the other needed fields from the Detail Block on it. For the Stacked Canvas set Show Horizontal Scroll Bar property to Yes.

Solution description with an example
This example uses the HR schema. The Master Block contains the departments and the Detail Block contains all the employees from the department displayed in the Master Block. Suppose you already have created the Master Block based on departments table.

Step 1
Create the Detail Block based on employees table. Choose to display only a few items from this Data Block (for example, only first_name and last_name). Set the Number of Records Displayed property to 10 (or other value grater than 1). Set the Show Scroll Bar property to Yes and set the Scroll Bar Orientation property to Vertical. Then move the Scroll Bar object in the right side of the frame, like in the next image:

Step 2
Associated with the Content Canvas make a Stacked Canvas and place it on the Content Canvas in the space between Last Name Item Text and the Scroll Bar object (the green rectangle in the previous image). For Stacked Canvas set the Show Horizontal Scroll Bar to Yes.

Step 3
Place in the Stacked Canvas all the fields that you need from Detail Block, like in the next images:

Content Canvas

Stacked Canvas

The Text Items from the Detail Block placed in the Content Canvas will be synchronized with the Text Items placed in the Stacked Canvas.

How to alternate row colors using Conditional Formatting property?

It is useful to highlight some parts of a report to get the attention when some conditions are met: some sum is greater then a specific value, something is on minus etc.

Oracle Report Builder offers two ways of doing this in a report:

- With Conditional Formatting, attributes like font, text color, border, fill color can be modified when some condition is true without programming;

- Format Trigger property offers more flexibility because you write your own code to perform conditional formatting.

The next report is showing all the responsibilities from Oracle Applications that starts with ‘Oracle’. The report is based on this query:

select frt.responsibility_id, frt.responsibility_name,

fr.start_date responsability_start_date, fr.end_date responsability_end_date

from fnd_responsibility fr, fnd_responsibility_tl frt

where frt.responsibility_id = fr.responsibility_id

and frt.responsibility_name like 'Oracle%'

order by frt.responsibility_name;

The colors for the rows of the report must be white and light blue. For this, a Summary Column (in this case it has the name row_no) must be created for counting and keeping the row number (count the responsibility_id because it is never null):

We want odd rows to be light blue and the even rows to be white. A Formula Column named odd_no must be created to check for each row if row_no has an odd or an even value:

In Layout Model, for each field from the row in the repeating frame, create a new formatting rule with the Conditional Formatting property: if odd_no equals with 1 then Fill Color will be light blue.

The report will look like this:

How to load data from a text file into database table using a SQL*Loader Request in Oracle Applications?

---

Create the text file containing the data and the control file (with ‘.ctl’ extension).
Then create a request in Oracle Application. This request must use as Execution Method SQL*Loader and must have a parameter for the text file path.

---

Solution description with an example
Step 1
The table test which will be populated with the data from the text file has the following columns:
Column Name--Nullable?---Data Type
X---------------No----------NUMBER
Y---------------Yes---------VARCHAR2(20 BYTE)
Z---------------Yes---------DATE

The sequence X_S is defined for column X:
CREATE SEQUENCE X_S
MINVALUE 1
INCREMENT BY 1
START WITH 1
NOCACHE
NOORDER
NOCYCLE ;

Name the text file with data to insert test.txt.
Place this file in $APPL_TOP\app_name\version\txt\ folder.
Suppose text file contains something like this:
,"aaa",10-APR-2008
,"bbb",11-APR-2008
,"ccc",12-APR-2008
,"ddd",13-APR-2008
,"eee",14-APR-2008
,"fff",15-APR-2008
"aaa", "bbb" ,"ccc" ,"ddd" ,"eee" ,"fff" are for column Y and 10-APR-2008,
11-APR-2008, …, 15-APR-2008 are for column Z. There are no values specified for
column X because for this values it will be used the sequence X_S.

Step 2
Name the control file test.ctl.
Place it in $APPL_TOP\app_name\x.y.z\bin\ folder.
The control file looks like this:
LOAD DATA
APPEND
INTO TABLE eco.test
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(X "eco.x_s.nextval",
Y,
Z)

Step 3
Define the Request in Oracle Application:
The Concurrent Program Executable has:
- Execution Method = SQL*Loader
- Execution File Name = test

The Request must have a parameter for the full path of the text file
( for example: C:\\oracle\visappl\eco\11.x.x\txt\test.txt ).

Step 4
Assign the request to some Request Group.

Step 5
Run the request and check if the data was loaded successfully into the database
table.

---

Troubleshooting
If the log file shows the error SQL*Loader-524: partial record found at end of
datafile, the last line in the text file and the control file must end with an “Enter”.

---