OraFAQ Forum: Reports & Discoverer » Payment register report in 80-byte format (merged)

Home » Developer & Programmer » Reports & Discoverer » Payment register report in 80-byte format (merged)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Payment register report in 80-byte format (merged) [message #222392]

Sat, 03 March 2007 18:47

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)

Report message to a moderator

Re: format register report-80byte [message #222396 is a reply to message #222392]

Sat, 03 March 2007 21:34

BlackSwan
Messages: 26766
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.

Report message to a moderator

Re: format register report-80byte [message #222397 is a reply to message #222396]

Sat, 03 March 2007 22:01

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

Report message to a moderator

Re: format register report-80byte [message #222398 is a reply to message #222392]

Sat, 03 March 2007 22:19

BlackSwan
Messages: 26766
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

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.

Report message to a moderator

Re: Payment register report in 80 byte format [message #222406 is a reply to message #222401]

Sun, 04 March 2007 00:29

BlackSwan
Messages: 26766
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.

Report message to a moderator

Re: Payment register report in 80 byte format [message #222425 is a reply to message #222406]

Sun, 04 March 2007 10:21

Littlefoot
Messages: 21813
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;

Report message to a moderator

Re: Payment register report in 80 byte format [message #222427 is a reply to message #222425]

Sun, 04 March 2007 10:36

skooman
Messages: 913
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

Report message to a moderator

Re: Payment register report in 80 byte format [message #222433 is a reply to message #222427]

Sun, 04 March 2007 13:51

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.

Report message to a moderator

Re: Payment register report in 80 byte format [message #222435 is a reply to message #222433]

Sun, 04 March 2007 14:35

Littlefoot
Messages: 21813
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.

Report message to a moderator

Re: Payment register report in 80 byte format [message #222476 is a reply to message #222435]

Mon, 05 March 2007 01:04

skooman
Messages: 913
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.

Report message to a moderator

Re: Payment register report in 80 byte format [message #222612 is a reply to message #222476]

Mon, 05 March 2007 12:01

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.

Report message to a moderator

Re: Payment register report in 80 byte format [message #222630 is a reply to message #222612]

Mon, 05 March 2007 12:48

skooman
Messages: 913
Registered: March 2005
Location: Netherlands

Senior Member

Okay, here we go... Wink

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?

Report message to a moderator

Re: Payment register report in 80 byte format [message #222644 is a reply to message #222612]

Mon, 05 March 2007 14:47

Littlefoot
Messages: 21813
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.

Report message to a moderator

Re: Payment register report in 80 byte format [message #222652 is a reply to message #222644]

Mon, 05 March 2007 15:44

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.

Report message to a moderator

Re: Payment register report in 80 byte format [message #222661 is a reply to message #222630]

Mon, 05 March 2007 19:05

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

Report message to a moderator

Re: Payment register report in 80 byte format [message #223370 is a reply to message #222661]

Thu, 08 March 2007 12:47

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

Report message to a moderator

Re: Payment register report in 80 byte format [message #224337 is a reply to message #223370]

Tue, 13 March 2007 23:24

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.

Report message to a moderator

Re: Payment register report in 80 byte format [message #224371 is a reply to message #224337]

Wed, 14 March 2007 02:05

Littlefoot
Messages: 21813
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.

Report message to a moderator

Re: Payment register report in 80 byte format [message #224604 is a reply to message #224371]

Wed, 14 March 2007 13:58

skooman
Messages: 913
Registered: March 2005
Location: Netherlands

Senior Member

Maybe Pbills is just reporting progress? That's nice for a change!

Report message to a moderator

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Jul 03 05:11:47 CDT 2024