select
MAX(CASE WHEN seq =1 THEN parent_lot_number END) parentlot1,
MAX(CASE WHEN seq =2 THEN parent_lot_number END) parentlot2,
MAX(CASE WHEN seq =3 THEN parent_lot_number END) parentlot3,
MAX(CASE WHEN seq =4 THEN parent_lot_number END) parentlot4,
MAX(CASE WHEN seq =1 THEN lot_number END) sublot1,
MAX(CASE WHEN seq =2 THEN lot_number END) sublot2,
MAX(CASE WHEN seq =3 THEN lot_number END) sublot3,
MAX(CASE WHEN seq =4 THEN lot_number END) sublot4,
MAX(CASE WHEN seq =1 THEN requested_quantity END) requested_quantity1,
MAX(CASE WHEN seq =2 THEN requested_quantity END) requested_quantity2,
MAX(CASE WHEN seq =3 THEN requested_quantity END) requested_quantity3,
MAX(CASE WHEN seq =4 THEN requested_quantity END) requested_quantity4,
MAX(CASE WHEN seq =1 THEN Expiration_date END) Expiration_date1,
MAX(CASE WHEN seq =2 THEN Expiration_date END) Expiration_date2,
MAX(CASE WHEN seq =3 THEN Expiration_date END) Expiration_date3,
MAX(CASE WHEN seq =4 THEN Expiration_date END) Expiration_date4,
pagenumber,
sales_order_number
from (
select DECODE(regexp_replace(rownum/4, ‘[^.]+\.(.*)$’, ‘0.\1′),.25,1,0.25,1,.5,2,.75,3,4) seq ,
lot_number,parent_lot_number,unit_weight,requested_quantity,Production_date,Expiration_date,SALES_ORDER_NUMBER,ROUND(rownum/4+.25) pagenumber
from (select wdd.lot_number,
iln.parent_lot_number,
wdd.SALES_ORDER_NUMBER,
wdd.unit_weight,
wdd.requested_quantity,
to_char(iln.ORIGINATION_DATE,’ DD/MM/YYYY’) Production_date,
to_char(iln.expiration_date,’ DD/MM/YYYY’) Expiration_date
from wsh_delivery_details wdd,
inv_lot_numbers iln
where 1=1
and wdd.lot_number=iln.lot_number
and wdd.organization_id=iln.organization_id
and WDD.SALES_ORDER_NUMBER=:P_SALES_ORDERNO
order by sales_order_number)
)
group by pagenumber,sales_order_number
order by pagenumber
Recent Posts
