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)
|
|
|
|
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
|
|
|
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.
|
|
|
|
Re: Payment register report in 80 byte format [message #222425 is a reply to message #222406] |
Sun, 04 March 2007 10:21 |
|
Littlefoot
Messages: 21818 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 |
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
|
|
|
|
|
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.
|
|
|
|
|
|
|
|
|
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.
|
|
|
|
|