Build logic [message #409639] |
Tue, 23 June 2009 06:48  |
mukundsahare
Messages: 21 Registered: February 2007
|
Junior Member |
|
|
I have below set of data in one the file
Inv Num Invoice AmT Type
20057636 0 2000
20057636 1787.28 2000
20057637 0 2000
20057637 2154.65 2000
20057640 0 2000
20057640 1635 2000
55042162 0 2000
55042162 1349.5 2000
Qty Price Part num PO num Type
88 2031 RE245831 5500197609 2500
65 1597 RE217574 5500176427 2500
60 1861 LVU19159 5500216675 2500
100 1635 LVU18508 5500216676 2500
50 2699 RE210038 5500177879 2500
I want to print the o/p as
20057636 88 2031 RE245831 5500197609
20057637 65 1597 RE217574 5500176427
20057637 60 1861 LVU19159 5500216675
20057640 100 1635 LVU18508 5500216676
55042162 50 2699 RE210038 5500177879
There are four invoices but the above line types '2500' are five lines. To link the details to particular invoice use the below logic.
Quantity * Price = Invoice Amount
In the above data
88 * 20.31 = 1787.28 (This invoice amount is for Invoice #20057636)
One Invoice can have multiple PO lines
65 * 15.97 = 1038.05
60 * 18.61 = 1116.60
-----------
2154.65 (This invoice amount is for Invoice #20057637)
100 * 16.35 = 1635 (This invoice amount is for Invoice #20057640)
50 * 26.99 = 1349.5(This invoice amount is for Invoice #55042162)
How to write the code for the same.Can anyone help.
Regards,
Mukund
|
|
|
|
Re: Build logic [message #409642 is a reply to message #409639] |
Tue, 23 June 2009 06:54   |
yoursrajju
Messages: 5 Registered: September 2006 Location: Bangalore
|
Junior Member |
|
|
I think you need to revisit the linking logic as there is no common field that can combine these two details (Invoice and PO). Please post the full details.
|
|
|
Re: Build logic [message #409646 is a reply to message #409640] |
Tue, 23 June 2009 07:09   |
mukundsahare
Messages: 21 Registered: February 2007
|
Junior Member |
|
|
I tried format the data .Hope this will help
Inv Num Invoice AmT Type
20057636 0 2000
20057636 1787.28 2000
20057637 0 2000
20057637 2154.65 2000
20057640 0 2000
20057640 1635 2000
55042162 0 2000
55042162 1349.5 2000
Qty Price Part num PO num Type
88 2031 RE245831 5500197609 2500
65 1597 RE217574 5500176427 2500
60 1861 LVU19159 5500216675 2500
100 1635 LVU18508 5500216676 2500
50 2699 RE210038 5500177879 2500
O/p should be like .
20057636 - 88 -2031- RE245831 - 5500197609
20057637 - 65 - 1597 - RE217574- 5500176427
20057637 - 60 - 1861- LVU19159- 5500216675
20057640 - 100 - 1635 - LVU18508 - 5500216676
55042162 - 50 - 2699 - RE210038 - 5500177879.
There are four invoices but the above line types '2500' are five lines. To link the details to particular invoice use the below logic.
Quantity * Price = Invoice Amount
In the above data
88 * 20.31 = 1787.28 (This inv amt is for inv. #20057636)
One Invoice can have multiple PO lines
65 * 15.97 = 1038.05
60 * 18.61 = 1116.60
-----------
2154.65 (This inv. amt is for Invoice #20057637)
100 * 16.35 = 1635 (This inv. amount is for Invoice #20057640)
50 * 26.99 = 1349.5(This inv. amount is for Invoice #55042162)
Please help to write the code for same
Regards
[mod-edit]Applied code tags.
[Updated on: Tue, 23 June 2009 07:35] by Moderator Report message to a moderator
|
|
|
|
Re: Build logic [message #409671 is a reply to message #409660] |
Tue, 23 June 2009 07:53   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
The bigf problem here isn't the test case, it's the fact that there is no way to work out which Invoice number goes with which Part Number/Po Number.
Without that information, you've got an problem.
|
|
|
Re: Build logic [message #409676 is a reply to message #409660] |
Tue, 23 June 2009 07:59   |
mukundsahare
Messages: 21 Registered: February 2007
|
Junior Member |
|
|
Hi,
I have to read the data from the file and write into the file .I am using UTL_FILE to read and write the data.
I need help to develop the code based on the logic I mentioned.
Thanks & Regards,
Mukund
|
|
|
|
Re: Build logic [message #409775 is a reply to message #409688] |
Tue, 23 June 2009 23:45   |
yoursrajju
Messages: 5 Registered: September 2006 Location: Bangalore
|
Junior Member |
|
|
You need to find any other logic to combine these two tables. Beacuase currently you cant really match which invoice number relates to which PO. IT can work for the given entries. But what if more than one invoice have same amount?
|
|
|
Re: Build logic [message #409940 is a reply to message #409775] |
Wed, 24 June 2009 09:30   |
mukundsahare
Messages: 21 Registered: February 2007
|
Junior Member |
|
|
Hi,
Thanks for the reply.
There are no tables ..I will be reading from file and writing into file.
Can you please help me build the code for the given entries.
Regards,
|
|
|
Re: Build logic [message #409944 is a reply to message #409639] |
Wed, 24 June 2009 09:33  |
 |
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
>There are no tables ..I will be reading from file and writing into file.
Then why is Oracle involved at all?
|
|
|