Home » Developer & Programmer » Reports & Discoverer » Payment register report in 80-byte format (merged)
icon9.gif  Payment register report in 80-byte format (merged) [message #222392] Sat, 03 March 2007 18:47 Go to next message
pbills
Messages: 40
Registered: February 2007
Member
Hello frnds

I was told that they want format register o/p in 80 byte format so that they can submit to the bank.I don't have any clue from where to start.Can some one pls help me with.. i have to submit this pretty soon.

Avi.
80 byte details
(1-13 acct number--numeric
14-23 chech serial num--numeric
24-33 check amount--numeric
34-41 issue date--numeric
42 void indicator--alphanumeric
43-72 additinal data(SSN,payee name)--alphanumeric
73-80 filler checkone zero blank--alphanumeric)
Re: format register report-80byte [message #222396 is a reply to message #222392] Sat, 03 March 2007 21:34 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>i have to submit this pretty soon.
I am unclear what your post has to do with Oracle.
It appears you desire a fixed column output; but I have no idea what is the input or why you are challenged to produce requested output.
Re: format register report-80byte [message #222397 is a reply to message #222396] Sat, 03 March 2007 22:01 Go to previous messageGo to next message
pbills
Messages: 40
Registered: February 2007
Member
It is a payment regieter report. Client has to submit the out of this report to bank when ever they want.And bank asked that in a particular format call 80byte format as I explained previously. What I have do for this

Avi
Re: format register report-80byte [message #222398 is a reply to message #222392] Sat, 03 March 2007 22:19 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
In neither of your posts do I see anything that mentions Oracle of any version, any table description(s), or any SQL.

What does your question have to do with Oracle??????????????

Based upon all of the above, I do not understand what any of this has to do with PL/SQL; let alone being an EXPERT question.

Open Excel & make sure the columns are in the correct order & correct size.

[Updated on: Sat, 03 March 2007 22:41] by Moderator

Report message to a moderator

Payment register report in 80 byte format [message #222401 is a reply to message #222392] Sat, 03 March 2007 23:04 Go to previous messageGo to next message
pbills
Messages: 40
Registered: February 2007
Member
Hello frnds

Payment Register is a seeded report.The o/p is check number customer address... etc with all details.
This details client has to send to bank but bank people want the o/p in 80byte format..which is follows

80 byte details
(1-13 acct number--numeric
14-23 chech serial num--numeric
24-33 check amount--numeric
34-41 issue date--numeric
42 void indicator--alphanumeric
43-72 additinal data(SSN,payee name)--alphanumeric
73-80 filler checkone zero blank--alphanumeric)

Avi.
Re: Payment register report in 80 byte format [message #222406 is a reply to message #222401] Sun, 04 March 2007 00:29 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
In your post do I see anything that mentions Oracle of any version, any table description(s), or any SQL.

What does your question have to do with Oracle??????????????

Open Excel & make sure the columns are in the correct order & correct size.
Re: Payment register report in 80 byte format [message #222425 is a reply to message #222406] Sun, 04 March 2007 10:21 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
As the line format must be fixed, you'll have to use proper functions with every datatype; numerics would, probably, have to be formatted using the TO_CHAR function, as well as date values. Characters might need to be (left?) padded in order to keep required space. For example, if you line was NUMBER (10) + DATE (8) + CHARACTER(10), it might look like this:
SELECT TO_CHAR(123.45, '999,990.00') 
    || TO_CHAR(SYSDATE, 'ddmmyyyy') 
    || LPAD('The Who', 10, ' '))
FROM dual;
Re: Payment register report in 80 byte format [message #222427 is a reply to message #222425] Sun, 04 March 2007 10:36 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Avi,

By seeded report, do you perhaps mean seeded in Oracle Apps (eBusiness Suite)?

If so, it's probably a Reports report. And it sounds as if the bank needs a plain ascii file. So, get the SQL statement from the report, alter it like Littlefoot described, spool the result and you have you required ascii file.

Regards,
Sabine
Re: Payment register report in 80 byte format [message #222433 is a reply to message #222427] Sun, 04 March 2007 13:51 Go to previous messageGo to next message
pbills
Messages: 40
Registered: February 2007
Member
If so, it's probably a Reports report.
s it is a reports report..

So do i need to write Packacge for this.. If i pull SQl and write how an user will run the report.


Avi.
Re: Payment register report in 80 byte format [message #222435 is a reply to message #222433] Sun, 04 March 2007 14:35 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
A package? As far as I can tell, no. Why would you need it? Unless there's something hidden, it seems to be quite a simple assignment. Package - to me - looks as unnecessary overhead. A simple .SQL script would do the job:
SET headings etc. OFF

SPOOL output_file.txt

SELECT this_and_that
FROM some_table
WHERE bla_bla;

SPOOL OFF;
How to run it? Anyhow you want - even with a double-click on MS Windows Desktop icon.
Re: Payment register report in 80 byte format [message #222476 is a reply to message #222435] Mon, 05 March 2007 01:04 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
However, to implement it in EBS (so to enable all users to run it), requires some additional EBS settings. I don't remember how to run plain SQL and spool the result from EBS, sorry.
Maybe if you ask this on the EBS thread, someone will be able to help you.

My suggestion: rephrase your question like: "I have this sql statement [your_statement] and I want EBS users to be able to run it and capture the result (ascii file) in order to mail it" or something like it.
Re: Payment register report in 80 byte format [message #222612 is a reply to message #222476] Mon, 05 March 2007 12:01 Go to previous messageGo to next message
pbills
Messages: 40
Registered: February 2007
Member
oops..

now it is becoming complicated..

I don't understand wt u are talking abt and wt to do abt my report..file...

can some pls explain me..


Thanks
Avi.
Re: Payment register report in 80 byte format [message #222630 is a reply to message #222612] Mon, 05 March 2007 12:48 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Okay, here we go... Wink

You have a report in Apps, right? So, you can get the SQL statement from that. Now you need to run that SQL statement and spool the results to file (then it will be a plain ascii file, based on your description I gather that's what the bank needs).

You can do this using SQL*PLus. However, since you want the users to be able to run this, you should incorporate it somehow in Apps (in the end it will be something like "run concurrent request XYZ and the output will be in directory ABC").

We can help you to make up the SQL statement and the spool file, but we don't know how to implement this in EBS. Therefor, I suggested you ask this on the EBS part of Orafaq: http://www.orafaq.com/forum/f/26/68527/

To give you a start: running a SQL statement in EBS is described in the Developer's Guide of EBS:
http://download-uk.oracle.com/docs/cd/B25516_15/current/acrobat/115devg.pdf

Does this help?


Re: Payment register report in 80 byte format [message #222644 is a reply to message #222612] Mon, 05 March 2007 14:47 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
@pbills: it is impolite to use IM speech on OraFAQ forum. This is unreadable as well as ugly.
pbills
wt u are talking abt and wt to do abt
Use it in an instant messenger communication; please, write full sentences while talking to people over here.
Re: Payment register report in 80 byte format [message #222652 is a reply to message #222644] Mon, 05 March 2007 15:44 Go to previous messageGo to next message
pbills
Messages: 40
Registered: February 2007
Member
Hello,

I am sorry for using IM format.

Thanks a lot for letting me know about this, as I am new I don't know that.

Thank you,
Avi.
Re: Payment register report in 80 byte format [message #222661 is a reply to message #222630] Mon, 05 March 2007 19:05 Go to previous messageGo to next message
pbills
Messages: 40
Registered: February 2007
Member
Hello Skooman,

Now I fell like I have some idea.

Let me pull the SQL statement from APPs report and do as you mentioned before.

Thank you,
Avi

Re: Payment register report in 80 byte format [message #223370 is a reply to message #222661] Thu, 08 March 2007 12:47 Go to previous messageGo to next message
pbills
Messages: 40
Registered: February 2007
Member
hello,

SQL> spool on
SQL> SET heading OFF
SQL> SPOOL C:\output_file.txt
SQL> SELECT sysdate
2 FROM dual
3 /
4 spool off;


when I write this, in o/p file i seel all code.

Avi
Re: Payment register report in 80 byte format [message #224337 is a reply to message #223370] Tue, 13 March 2007 23:24 Go to previous messageGo to next message
pbills
Messages: 40
Registered: February 2007
Member
SELECT to_number((TO_CHAR(ch.check_date, 'YYYYMMDD')))
|| lpad(round(ch.amount),10,0)
||substr('V',1)
|| LPAD ('0', 30, '0')
|| lpad('ZERO',8,0) detail
FROM DUAL
where ch.check_date between
(to_date(:p_start_date,'MM/DD/YYYY')) and
(to_date(:p_end_date,'MM/DD/YYYY'))


I am trying to SPOOL above query and concurrent the program so that user can run when ever they want.
Re: Payment register report in 80 byte format [message #224371 is a reply to message #224337] Wed, 14 March 2007 02:05 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Is it a question? Smells like it, but doesn't look like one. Could you explain it, please? I don't understand what is the problem here.
Re: Payment register report in 80 byte format [message #224604 is a reply to message #224371] Wed, 14 March 2007 13:58 Go to previous message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Maybe Pbills is just reporting progress? That's nice for a change!
Previous Topic: unable to display pdf drill down report in new browser window
Next Topic: reports 6i - assign page num to bind variable
Goto Forum:
  


Current Time: Fri Dec 09 23:31:36 CST 2016

Total time taken to generate the page: 0.13230 seconds