Need help for Program

[Namasiva]

Hi,

I am trying to extract the so details in to comma delimited csv file.

I am here with attaching the codes for your review.

i need the following things in the code.

1. the code should not duplicate the values if i run the program for several time in a day. for example i am creating 7 sales order and extract the data using the so extraction program after that i am creating another 5 sales order . if i run the so extraction program it should only give the out put of 5 (last created) sales order's detail.

2. if a sales order having 10 lines the report is giving the out put of only one line ie 1st line, its not giving out put of othere lines...

3. the porgram is taking long time to give the out put.


kindly help me to sort out this issue.

 

[sphipp]

Hi,

I am trying to extract the so details in to comma delimited csv file.

I am here with attaching the codes for your review.

i need the following things in the code.

1. the code should not duplicate the values if i run the program for several time in a day. for example i am creating 7 sales order and extract the data using the so extraction program after that i am creating another 5 sales order . if i run the so extraction program it should only give the out put of 5 (last created) sales order's detail.

You could mark so_mstr with something to say you have processed it. so_user1 and so_user2 are normally free, unless you are using it for something else. Then you could search for it in your for each making sure you have so_user1 = "" as part of the for each.

Alternatively, you could store the sales orders that you have already processed in a Generalised Codes record. Set up a Generalised Codes for SoEXTRACT or some other unique code, and the date and store the sales order numbers as a comma-delimited list. That way you can find the Generalised Codes record corresponding to SoEXTRACT and today's date, pull the list into a variable, check whether the Sales Orders you are For-Eaching are already in the list and add new ones to the list, writing it back to the Generalised Codes afterwards.

2. if a sales order having 10 lines the report is giving the out put of only one line ie 1st line, its not giving out put of othere lines...

You have commented out part of the find first t1_hist, so you are only checking to see if there is a record with that Sales Order Number. Uncomment those lines and you should get more records.

                                    find first t1_hist where t1_sonbr = sod_nbr
                     /*   and t1_part = sod_part
                                     and t1_site = sod_site       
                                     and t1_cust = so_cust                           
                                     and t1_date = sod_due_date   */ no-error.

should be

                                    find first t1_hist where t1_sonbr = sod_nbr
                                     and t1_part = sod_part
                                     and t1_site = sod_site       
                                     and t1_cust = so_cust                           
                                     and t1_date = sod_due_date   no-error.

3. the porgram is taking long time to give the out put.

That sounds like an index problem. I don't have the MFG/PRO schema with me so I can't check, but I don't think that so_ord_date is on a good index. The others look fine.

 

[zee]

2. if a sales order having 10 lines the report is giving the out put of only one line ie 1st line, its not giving out put of othere lines...

[B]first [/B]sod_det where sod_nbr = so_nbr and sod_site = so_site

should be

[B]each [/B]sod_det where sod_nbr = so_nbr and sod_site = so_site

You should also change your "find first tl_hist" to include a match on t1_soline = sod_line.

 

[maheshnaik85]

hi,
I couldn't able to access ur program...

To get the latest record of SO i.e if the extracting program has fetch the so and then u have created 5 SO....DO one thing while extracting take all the corresponding record in a temp-table . when u run a program after creating new SO , The program will check the record in TEMP-TABLE . as new so has been created , it will not find any record .Hence create record in temp-table by extracting it. ( don't delete the temp-table at the begining of the program what we use to do normally)

 

[Namasiva]

Thank a lot for all your comments. based on your comments i did the coding and its working fine but its taking too much time to create the report rest everything is fine..

once againg thanks for your kind help.

 

[maheshnaik85]

if ur program contains code with break by caluse.
then do following:

1) Remove the break by clause from the for each Statement.
2) collect the data in temp-table.
3) Then use break by for that temp-table.



in case of multiple joins or complex queries always fetch data in temp-table then apply break by clause for further processing. It reduces the load on database server and makes programs more efficient.All the best....