if not order by , use this function it will be order 'order by cast ( age as float) asc '
select itemcode,description, ved,
user_item_type, itemstatus, qty, uom, unitprice, total, raised, dep,
recc, iss, fin,age,
dat
from
(SELECT itemcode,
REGEXP_REPLACE (description, '( *[[:punct:]])', '') description, ved,
user_item_type, itemstatus, qty, uom, unitprice, total, raised, dep,
recc, iss, fin,
mf_month (MONTHS_BETWEEN (LAST_DAY (SYSDATE), LAST_DAY (fin)))
AS age,
dat
FROM (SELECT itemcode, description, ved, user_item_type, itemstatus,
qty, uom, unitprice, total, raised, dep, recc, iss,
CASE
WHEN recc >= iss
THEN recc
WHEN iss > recc
THEN iss
WHEN iss IS NULL AND recc IS NOT NULL
THEN recc
WHEN recc IS NULL AND iss IS NOT NULL
THEN iss
WHEN iss IS NULL AND recc IS NULL
THEN NULL
END AS fin,
dat
FROM (SELECT DISTINCT segment1 AS itemcode, description, ved,
user_item_type, itemstatus, qty, uom,
unit AS unitprice, UP AS total,
crdate AS dat,
(CASE
WHEN did IS NOT NULL
THEN (SELECT DISTINCT papf.full_name
FROM apps.per_all_people_f papf,
-- apps.po_req_distributions_all prda,
apps.po_requisition_lines_all prla,
apps.per_all_assignments_f paaf,
apps.hr_all_organization_units haou
WHERE prla.requisition_line_id =
did
AND ROWNUM = 1
AND papf.person_id =
prla.to_person_id
AND papf.person_id =
paaf.person_id
AND SYSDATE
BETWEEN paaf.effective_start_date
AND paaf.effective_end_date
AND paaf.organization_id =
haou.organization_id
AND haou.TYPE =
'SEC')
ELSE NULL
END
) AS raised,
(CASE
WHEN did IS NOT NULL
THEN (SELECT DISTINCT SUBSTR
(haou.NAME,
5,
3
)
FROM apps.per_all_people_f papf,
-- apps.po_req_distributions_all prda,
apps.po_requisition_lines_all prla,
apps.per_all_assignments_f paaf,
apps.hr_all_organization_units haou
WHERE prla.requisition_line_id =
did
AND papf.person_id =
prla.to_person_id
AND papf.person_id =
paaf.person_id
AND SYSDATE
BETWEEN paaf.effective_start_date
AND paaf.effective_end_date
AND paaf.organization_id =
haou.organization_id
AND haou.TYPE =
'SEC')
ELSE NULL
END
) AS dep,
receipt recc,
CASE
WHEN pid >= schedule
THEN pid
WHEN schedule > pid
THEN schedule
WHEN schedule IS NULL
AND pid IS NOT NULL
THEN pid
WHEN pid IS NULL
AND schedule IS NOT NULL
THEN schedule
WHEN schedule IS NULL AND pid IS NULL
THEN NULL
END AS iss,
prd, pid, schedule, receipt, crdate
FROM (SELECT segment1, qty, ids, iid,
schedule, description, crdate,
(SELECT MAX
(prla.requisition_line_id
)
FROM apps.mtl_system_items_b msib,
apps.po_requisition_lines_all prla
WHERE prla.item_id =
msib.inventory_item_id
AND msib.organization_id =
prla.destination_organization_id
AND item_id = iid) AS did,
ved, user_item_type, itemstatus,
uom, SUM (unit) unit,
ROUND (SUM (unit) * qty,
2) AS UP,
(SELECT MAX
(req_distribution_id
)
FROM apps.rcv_transactions
WHERE shipment_header_id = ids)
distid,
(SELECT MAX
(creation_date
)
FROM apps.rcv_shipment_headers
WHERE shipment_header_id = ids)
receipt,
prd, pid
FROM (SELECT msib.segment1,
msib.description
AS description,
msib.attribute4
AS ved,
flv.meaning
AS user_item_type,
msib.inventory_item_status_code
AS itemstatus,
msib.primary_unit_of_measure
AS uom,
cis.item_cost unit,
NULL AS prd,
mmt.transaction_date
pid,
(SELECT SUM
(primary_transaction_quantity
)
AS stock
FROM apps.mtl_onhand_quantities_detail
WHERE inventory_item_id =
msib.inventory_item_id
AND organization_id =
moqd.organization_id
GROUP BY inventory_item_id)
qty,
(SELECT MAX
(rshh.shipment_header_id
)
FROM apps.rcv_shipment_lines rsll,
apps.rcv_shipment_headers rshh
WHERE rsll.shipment_header_id =
rshh.shipment_header_id
AND rsll.to_organization_id =
104
AND rsll.item_id =
msib.inventory_item_id)
AS ids,
(SELECT MAX
(transaction_date
)
FROM apps.mtl_material_transactions
WHERE inventory_item_id =
msib.inventory_item_id
AND organization_id =
msib.organization_id)
schedule,
(SELECT MAX
(creation_date
)
FROM apps.mtl_system_items_b
WHERE inventory_item_id =
msib.inventory_item_id
AND organization_id =
msib.organization_id)
AS crdate,
msib.inventory_item_id
AS iid
FROM apps.mtl_system_items_b msib,
apps.mtl_onhand_quantities_detail moqd,
apps.rcv_shipment_lines rsl,
apps.rcv_shipment_headers rsh,
apps.rcv_transactions rt,
apps.cst_item_costs cis,
apps.cst_cost_types cct,
apps.mtl_material_transactions mmt,
apps.fnd_lookup_values flv
WHERE msib.inventory_item_id =
moqd.inventory_item_id
AND msib.organization_id =
moqd.organization_id
AND mmt.inventory_item_id =
msib.inventory_item_id
AND mmt.organization_id =
msib.organization_id
AND mmt.transaction_date >=
'01-APR-2012'
AND msib.item_type =
flv.lookup_code
AND flv.lookup_type =
'ITEM_TYPE'
AND msib.inventory_item_id =
rsl.item_id
AND rsl.shipment_header_id =
rsh.shipment_header_id
AND rt.shipment_line_id =
rsl.shipment_line_id
AND rt.shipment_header_id =
rsh.shipment_header_id
AND rt.transaction_id =
mmt.rcv_transaction_id
AND flv.meaning =
NVL (:p_item_type,
flv.meaning
)
AND msib.inventory_item_status_code =
NVL
(:p_itmstatus,
msib.inventory_item_status_code
)
AND :p_base IN
('Age', 'Item')
AND cis.organization_id =
msib.organization_id
AND cis.inventory_item_id =
msib.inventory_item_id
AND cis.cost_type_id =
cct.cost_type_id
AND moqd.organization_id =
NVL
(:org_id,
moqd.organization_id
)
GROUP BY msib.segment1,
msib.description,
msib.attribute4,
flv.meaning,
msib.inventory_item_status_code,
msib.primary_unit_of_measure,
msib.inventory_item_id,
moqd.organization_id,
moqd.inventory_item_id,
msib.organization_id,
cis.item_cost,
mmt.transaction_date)
GROUP BY segment1,
qty,
ids,
iid,
schedule,
description,
ved,
user_item_type,
itemstatus,
uom,
crdate,
prd,
pid)
GROUP BY segment1,
qty,
ids,
iid,
schedule,
UP,
distid,
description,
ved,
user_item_type,
itemstatus,
uom,
receipt,
crdate,
prd,
pid,
did,
unit)
GROUP BY itemcode,
description,
ved,
user_item_type,
itemstatus,
qty,
uom,
unitprice,
total,
raised,
dep,
recc,
iss,
dat)
WHERE fin BETWEEN fm_month (:ptodate) AND fm_month (:pfromdate)
AND total BETWEEN NVL (:p_frm_value, total) AND NVL (:p_to_value, total)
GROUP BY itemcode,
description,
ved,
user_item_type,
itemstatus,
qty,
uom,
unitprice,
total,
raised,
dep,
recc,
iss,
fin,
dat
union
SELECT itemcode,
REGEXP_REPLACE (description, '( *[[:punct:]])', '') description, ved,
user_item_type, itemstatus, qty, uom, unitprice, total, raised, dep,
recc, iss, fin,
mf_month (MONTHS_BETWEEN (LAST_DAY (SYSDATE), LAST_DAY (fin)))
AS age,
dat
FROM (SELECT itemcode, description, ved, user_item_type, itemstatus,
qty, uom, unitprice, total, raised, dep, recc, iss,
CASE
WHEN recc >= iss
THEN recc
WHEN iss > recc
THEN iss
WHEN iss IS NULL AND recc IS NOT NULL
THEN recc
WHEN recc IS NULL AND iss IS NOT NULL
THEN iss
WHEN iss IS NULL AND recc IS NULL
THEN NULL
END AS fin,
dat
FROM (SELECT DISTINCT segment1 AS itemcode, description, ved,
user_item_type, itemstatus, qty, uom,
UP AS unitprice, (qty * UP) AS total,
crdate AS dat,
(CASE
WHEN did IS NOT NULL
THEN (SELECT DISTINCT papf.full_name
FROM apps.per_all_people_f papf,
-- apps.po_req_distributions_all prda,
apps.po_requisition_lines_all prla,
apps.per_all_assignments_f paaf,
apps.hr_all_organization_units haou
WHERE prla.requisition_line_id =
did
AND ROWNUM = 1
AND papf.person_id =
prla.to_person_id
AND papf.person_id =
paaf.person_id
AND SYSDATE
BETWEEN paaf.effective_start_date
AND paaf.effective_end_date
AND paaf.organization_id =
haou.organization_id
AND haou.TYPE =
'SEC')
ELSE NULL
END
) AS raised,
(CASE
WHEN did IS NOT NULL
THEN (SELECT DISTINCT SUBSTR
(haou.NAME,
5,
3
)
FROM apps.per_all_people_f papf,
-- apps.po_req_distributions_all prda,
apps.po_requisition_lines_all prla,
apps.per_all_assignments_f paaf,
apps.hr_all_organization_units haou
WHERE prla.requisition_line_id =
did
AND papf.person_id =
prla.to_person_id
AND papf.person_id =
paaf.person_id
AND SYSDATE
BETWEEN paaf.effective_start_date
AND paaf.effective_end_date
AND paaf.organization_id =
haou.organization_id
AND haou.TYPE =
'SEC')
ELSE NULL
END
) AS dep,
prd recc, pid iss, prd, pid, schedule,
crdate
FROM (SELECT segment1, qty,
--ids, -- iid,
schedule,
description, crdate,
(SELECT MAX
(prla.requisition_line_id
)
FROM apps.mtl_system_items_b msib,
apps.po_requisition_lines_all prla
WHERE prla.item_id =
msib.inventory_item_id
AND msib.organization_id =
prla.destination_organization_id
AND item_id = iid) AS did,
ved, user_item_type, itemstatus,
uom, UP, prd, pid
FROM (SELECT DISTINCT msib.segment1,
msib.description
AS description,
msib.attribute4
AS ved,
flv.meaning
AS user_item_type,
msib.inventory_item_status_code
AS itemstatus,
msib.primary_unit_of_measure
AS uom,
tionmm.soh_value
unit,
tionmm.stk_on_hand
qty,
( tionmm.soh_value
/ tionmm.stk_on_hand
) AS UP,
tionmm.prev_rcpt_date
prd,
tionmm.prev_iss_date
pid,
tionmm.trxn_date
schedule,
(SELECT MAX
(creation_date
)
FROM apps.mtl_system_items_b
WHERE inventory_item_id =
msib.inventory_item_id
AND organization_id =
msib.organization_id)
AS crdate,
msib.inventory_item_id
AS iid
FROM apps.mtl_system_items_b msib,
apps.mtl_onhand_quantities_detail moqd,
apps.fnd_lookup_values flv,
apps.tnpl_inv_oiis_nmv_mtl_txn_mst tionmm
WHERE tionmm.inventory_item_id =
msib.inventory_item_id
and tionmm.org_id = msib.organization_id
AND tionmm.close_flag =
'N'
AND msib.inventory_item_id =
moqd.inventory_item_id
AND msib.organization_id =
moqd.organization_id
AND tionmm.trxn_date <=
'31-MAR-2012'
AND msib.item_type =
flv.lookup_code
AND flv.lookup_type =
'ITEM_TYPE'
AND flv.meaning =
NVL
(:p_item_type,
flv.meaning
)
AND msib.inventory_item_status_code =
NVL
(:p_itmstatus,
msib.inventory_item_status_code
)
AND :p_base IN
('Age',
'Item')
AND moqd.organization_id =
NVL
(:org_id,
moqd.organization_id
)
GROUP BY msib.segment1,
msib.description,
msib.attribute4,
flv.meaning,
msib.inventory_item_status_code,
msib.primary_unit_of_measure,
msib.inventory_item_id,
moqd.organization_id,
moqd.inventory_item_id,
tionmm.prev_rcpt_date,
tionmm.prev_iss_date,
msib.organization_id,
tionmm.trxn_date,
tionmm.soh_value,
tionmm.stk_on_hand)
GROUP BY segment1,
qty,
-- ids,
iid,
schedule,
description,
ved,
user_item_type,
itemstatus,
uom,
crdate,
prd,
pid,
UP)
GROUP BY segment1,
qty,
-- ids,
-- iid,
schedule,
UP,
-- distid,
description,
ved,
user_item_type,
itemstatus,
uom,
-- receipt,
crdate,
prd,
pid,
did)
GROUP BY itemcode,
description,
ved,
user_item_type,
itemstatus,
qty,
uom,
unitprice,
total,
raised,
dep,
recc,
iss,
dat)
WHERE fin BETWEEN fm_month (:ptodate) AND fm_month (:pfromdate)
AND total BETWEEN NVL (:p_frm_value, total) AND NVL (:p_to_value, total)
GROUP BY itemcode,
description,
ved,
user_item_type,
itemstatus,
qty,
uom,
unitprice,
total,
raised,
dep,
recc,
iss,
fin,
dat
)group by itemcode,description, ved,
user_item_type, itemstatus, qty, uom, unitprice, total, raised, dep,
recc, iss, fin,age,
dat
&p_order
No comments:
Post a Comment