Oracle Order Entry data

Hi all,

I know that most questions here are about the Oracle database but I'v not found a newsgroup for questions of my type so will try this forum.

We are running Oracle7.1.6.2.0 on Sun Solaris. With version 10.4.2.1 of Oracle Order Entry.

I have been tasked with creating a view that our customers will access that shows the status of their orders/lines that are currently scheduled but unshipped or have shipped in the past 28 days. I have learned a lot over the past month or so but am still running into a final roadblock relating to backorders. Was hoping someone could help me with a question.

When part of a line is backordered (line was partially picked) I see a new so_picking_lines record added with a picking_header_id = -1 and a requested quantity of the unpicked portion of the line. Scheduling is able to change the schedule_date on this new picking_lines record (I need to show this new schedule date to the customer). At some point another record is created in so_picking_lines with the same requested quantity, same (new) scheduled_date and a 'real' picking_header_id. After this additional so_picking_lines records is created with a real picking_header_id the one with '-1' picking_header_id still exists.

What I need is a fool proof way to know whether a picking_line with a real picking_header exists for the one with '-1' picking_header_id. I need to know this so that my SQL can pick up the '-1' picking_header_id record (with its possibly changed schedule_date) if one with a real picking_header_id does not yet exist but pick up the one with a 'real' picking_header_id instead, when it exists (with possible shipped quantity).

Does this make sense to anyone? Can anyone help me understand this portion of Oracle Order Entry functionality.

Thanks, Dan

-- 


   ******************

   Opinions expressed are my own and do not
   necessarily reflect those of my employer


   ******************