update sequence

[chamilz]

line_id action_type po_number po_line_number color_code nrf_code pack_code
----------- ----------- ----------- -------------- ---------- -------- ---------
0 A 1000124 1 215 080 NULL
1 A 1000124 1 215 120 NULL
2 A 1000124 1 215 140 NULL
3 A 1000124 1 215 160 NULL
4 A 1000124 0 215 NULL 40097
5 A 1000124 0 215 NULL 40098
6 A 1000125 0 215 NULL 40097
7 A 1000126 1 407 090 NULL
8 A 1000126 2 508 090 NULL
9 A 1000126 3 605 090 NULL
10 A 1000126 0 407 NULL 40112
11 A 1000126 0 508 NULL 40114
12 A 1000126 0 605 NULL 40116
13 A 1000126 0 407 NULL 40120
14 A 1000126 0 508 NULL 40121
15 A 1000126 0 605 NULL 40122


I wanted to update the line numbers of ths above table following a sequence.

1. I have two different type of records in this table,
- records with nrf_code
- records with pack_code
2. NRF_CODE line numbers; For example, PO number 1000124 and Color_code 215 has different NRF codes,
In this case if the PO number = PO_number and Color_code = color_code then the line number is 1 for different NRF.

In PO 1000126, i got three colours (407/508/605), so line sequence for color 407= 1, 508=2, 605=3 (color
codes are sorted in ascending order)

I have impelemted this process and it works.

---------------------------------------------------
Now i need to update the Squence for PACK_CODE records:
PACK_REcord line number should start from next available line sequence number and each pack
should assign a new squence within the same PO_Number range:
Somthing like this


Next available SEquence Number
Where PO_Number = PO_Number
and Pack_code is NOT NULL



line_id action_type po_number po_line_number color_code nrf_code pack_code
----------- ----------- ----------- -------------- ---------- -------- ---------
0 A 1000124 1 215 080 NULL
1 A 1000124 1 215 120 NULL
2 A 1000124 1 215 140 NULL
3 A 1000124 1 215 160 NULL
4 A 1000124 2 215 NULL 40097
5 A 1000124 3 215 NULL 40098

6 A 1000125 1 215 NULL 40097

7 A 1000126 1 407 090 NULL
8 A 1000126 2 508 090 NULL
9 A 1000126 3 605 090 NULL
10 A 1000126 4 407 NULL 40112
11 A 1000126 5 508 NULL 40114
12 A 1000126 6 605 NULL 40116
13 A 1000126 7 407 NULL 40120
14 A 1000126 8 508 NULL 40121
15 A 1000126 9 605 NULL 40122


Appriciate your help and advice...

Thanks.

 

[jbenson001]

PACK_REcord line number should start from next available line sequence number

Are you refering to the line_id column?

Jim

 

[chamilz]

No..i am refering po_line_number column..

In my first table you could see po_line_numbers for records where NRF_CODE is not Null.

Now,I want to update po_line_numbers for records where PACK_CODE is not Null..
PACK records po_line_number should be start from next available po_line_number.


Second table shows my expected Result
/Thanks