/* Formatted on 2010/11/26 09:06 (Formatter Plus v4.8.6) */
SELECT b.*
FROM (SELECT ROWNUM pagination_num, a.*
FROM (SELECT s.storehouse_id, i.storehouse_name, s.materiel_name,
s.materiel_num, s.unit_name, s.total_money,
s.material_sign,
(SELECT d.dictdata_name
FROM td_sm_dictdata d, td_sm_dicttype t
WHERE d.dicttype_id = t.dicttype_id
AND t.dicttype_name = 'materialSign'
AND d.dictdata_value = s.material_sign) CATEGORY,
s.unit_id, s.materiel_type,
(SELECT d.dictdata_name
FROM td_sm_dictdata d, td_sm_dicttype t
WHERE d.dicttype_id = t.dicttype_id
AND t.dicttype_name = 'MATERIEL_TYPE'
AND d.dictdata_value = s.materiel_type) model,
s.goodsrack_id, s.item_id, s.incept_state, s.city_id,
s.materiel_id,
NVL ((SELECT p.project_name
FROM td_newstorage_project p
WHERE p.project_id = s.item_id),
''
) project_name,
m.materiel_spec, m.materiel_model, g.goodsrack_name,
TO_CHAR (m.up_value) up_value,
TO_CHAR (m.down_value) down_value,
TO_CHAR
((SELECT SUM (materiel_num)
FROM td_newstorage_stockpile needsum
WHERE needsum.depositary_id = s.depositary_id
AND needsum.storehouse_id = s.storehouse_id
AND needsum.incept_state = s.incept_state
AND needsum.item_id = s.item_id
AND needsum.material_sign = s.material_sign
AND needsum.materiel_id = s.materiel_id
AND needsum.materiel_type = s.materiel_type
AND needsum.unit_id = s.unit_id)
) sum_materiel
FROM td_newstorage_stockpile s,
td_newstorage_materiel_info m,
td_newstorage_goodsrack g,
td_newstorage_info i
WHERE s.materiel_id = m.materiel_id
AND s.goodsrack_id = g.goodsrack_id
AND s.storehouse_id = i.storehouse_id
AND s.materiel_num <> 0
AND s.depositary_id IN (
SELECT i.depositary_id
FROM td_newstorage_situs_info i
WHERE i.storehouse_id =
'CKXX2007120716465498')
AND s.depositary_id IN (SELECT m.depositary_id
FROM td_newstorage_keeping_ref m
WHERE m.keeping_id = 'admin')
ORDER BY s.storehouse_id, s.item_id,s.goodsrack_id, s.incept_state) a
--WHERE ROWNUM <= 10
) b
WHERE pagination_num <= 10 and pagination_num >= 1
SELECT b.*
FROM (SELECT ROWNUM pagination_num, a.*
FROM (SELECT s.storehouse_id, i.storehouse_name, s.materiel_name,
s.materiel_num, s.unit_name, s.total_money,
s.material_sign,
(SELECT d.dictdata_name
FROM td_sm_dictdata d, td_sm_dicttype t
WHERE d.dicttype_id = t.dicttype_id
AND t.dicttype_name = 'materialSign'
AND d.dictdata_value = s.material_sign) CATEGORY,
s.unit_id, s.materiel_type,
(SELECT d.dictdata_name
FROM td_sm_dictdata d, td_sm_dicttype t
WHERE d.dicttype_id = t.dicttype_id
AND t.dicttype_name = 'MATERIEL_TYPE'
AND d.dictdata_value = s.materiel_type) model,
s.goodsrack_id, s.item_id, s.incept_state, s.city_id,
s.materiel_id,
NVL ((SELECT p.project_name
FROM td_newstorage_project p
WHERE p.project_id = s.item_id),
''
) project_name,
m.materiel_spec, m.materiel_model, g.goodsrack_name,
TO_CHAR (m.up_value) up_value,
TO_CHAR (m.down_value) down_value,
TO_CHAR
((SELECT SUM (materiel_num)
FROM td_newstorage_stockpile needsum
WHERE needsum.depositary_id = s.depositary_id
AND needsum.storehouse_id = s.storehouse_id
AND needsum.incept_state = s.incept_state
AND needsum.item_id = s.item_id
AND needsum.material_sign = s.material_sign
AND needsum.materiel_id = s.materiel_id
AND needsum.materiel_type = s.materiel_type
AND needsum.unit_id = s.unit_id)
) sum_materiel
FROM td_newstorage_stockpile s,
td_newstorage_materiel_info m,
td_newstorage_goodsrack g,
td_newstorage_info i
WHERE s.materiel_id = m.materiel_id
AND s.goodsrack_id = g.goodsrack_id
AND s.storehouse_id = i.storehouse_id
AND s.materiel_num <> 0
AND s.depositary_id IN (
SELECT i.depositary_id
FROM td_newstorage_situs_info i
WHERE i.storehouse_id =
'CKXX2007120716465498')
AND s.depositary_id IN (SELECT m.depositary_id
FROM td_newstorage_keeping_ref m
WHERE m.keeping_id = 'admin')
ORDER BY s.storehouse_id, s.item_id,s.goodsrack_id, s.incept_state) a
--WHERE ROWNUM <= 10
) b
WHERE pagination_num <= 10 and pagination_num >= 1