SQL & PL/SQL

Hi ,

Please help me in solving this query

I have rcv_transaction table quantity column which is always a +ve Number but for transaction_type 'Return To Vendor' it is to be displayed as -Ve
.

below is my query-

select 'I' ,
Decode(MAX(Prl.Attribute1),NULL,MAX(Hl.Attribute1),hr.attribute2) ,
Decode(MAX(Prl.Attribute1),NULL,'S','D'),
Ph.Segment1,
PL.LINE_NUM,
pov.segment1||'-'||povs.vendor_site_code,
Substr(MAX(Prl.Manufacturer_Part_Number),1,14),
Pl.Item_Description,
Uom.Uom_Code,
PH.CREATION_DATE,
NULL ,
PL.QUANTITY,
DECODE( RT.TRANSACTION_TYPE,'RETURN TO VENDOR',-RT.QUANTITY,RT.QUANTITY),
NULL,
Pl.Unit_Price,
NVL(PL.QUANTITY,0) * NVL(PL.UNIT_PRICE,0),
DECODE( RT.TRANSACTION_TYPE,'RETURN TO VENDOR',NVL(RT.PO_UNIT_PRICE,0)* (SUM(RT.QUANTITY) OVER (PARTITION BY RT.SHIPMENT_HEADER_ID ORDER BY RT.TRANSACTION_DATE
Rows Between Unbounded Preceding And Current Row))-NVL(RT.PO_UNIT_PRICE,0)*rt.quantity,NVL(RT.PO_UNIT_PRICE,0)* (SUM(RT.QUANTITY) OVER (PARTITION BY RT.SHIPMENT_HEADER_ID ORDER BY RT.TRANSACTION_DATE --RT.SHIPMENT_HEADER_ID
Rows Between Unbounded Preceding And Current Row))) ,
Rsh.Receipt_Num,
Null,Null,Null,Prl.Attribute3,
decode(rt.transaction_type ,'RECEIVE','RCC','RCA'),
Cc.Segment1,
cc.segment2,
Cc.Segment3,
Cc.Segment4,
Nvl(Ph.Approved_Flag,'N'),
RSH.CREATION_DATE
from po_headers_all ph,
Po_Lines Pl,
po_requisition_headers prh,
Po_Distributions Po,
Po_Req_Distributions Pr,
Po_Requisition_Lines Prl,
Rcv_Shipment_Lines Rsl,
rcv_shipment_headers rsh,
Hr_Locations Hl ,
po_line_locations_all pll,
rcv_transactions rt,
PO_VENDORS POV,
Po_Vendor_Sites_All Povs,
Gl_Code_Combinations Cc,
MTL_UNITS_OF_MEASURE UOM,
hr_organization_units hr
Where Ph.Po_Header_Id = Pl.Po_Header_Id
And Po.Po_Line_Id = Pl.Po_Line_Id
And Po.Po_Header_Id = Ph.Po_Header_Id
And Pr.Requisition_Line_Id = Prl.Requisition_Line_Id
And Po.Req_Distribution_Id (+) = Pr.Distribution_Id
And Hl.Location_Id = Pll.Ship_To_Location_Id
and ph.po_header_id = rsl.po_header_id
And Pl.Po_Line_Id = Rsl.Po_Line_Id
And Rsh.Shipment_Header_Id = RSL.SHIPMENT_HEADER_ID
And Po.Req_Distribution_Id = Pr.Distribution_Id
And Pr.Code_Combination_Id = Cc.Code_Combination_Id
And Pl.Unit_Meas_Lookup_Code = Uom.Unit_Of_Measure
AND POV.VENDOR_ID(+) = PH.VENDOR_ID
and Povs.Vendor_Site_Id(+) = Ph.Vendor_Site_Id
And Pl.Po_Line_Id = Pll.Po_Line_Id
And Ph.Po_Header_Id = Pll.Po_Header_Id
and prl.requisition_header_id = prh.requisition_header_id
AND PRL.DELIVER_TO_LOCATION_ID = HL.LOCATION_ID
And prh.Attribute15 = Hr.Organization_Id
and upper(hr.name) = 'HT BUILDING SERVICES'
And (Prl.Attribute3 Is Not Null Or (Upper(Prl.Manufacturer_Name)=Upper('Servitor') And Prl.Manufacturer_Part_Number Is Not Null And hl.attribute1 is not null))
And Rt.Po_Header_Id = Ph.Po_Header_Id
And Rt.Shipment_Line_Id = Rsl.Shipment_Line_Id
AND RT.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID
and rt.transaction_type in ('RETURN TO VENDOR', 'RECEIVE','CORRECT')
AND trunc(rsh.last_update_date) >=DECODE(TO_DATE(SUBSTR('&1', 1, 10), 'YYYY/MM/DD'),'',
TRUNC(ph.creation_date),
To_Date(Substr('&1', 1, 10), 'YYYY/MM/DD'))
AND trunc(rsh.last_update_date) <= DECODE(TO_DATE(SUBSTR('&2', 1, 10), 'YYYY/MM/DD'),'',
TRUNC(ph.creation_date),
TO_DATE(SUBSTR('&2', 1, 10), 'YYYY/MM/DD'))
Group By Ph.Segment1,Pl.Line_Num ,Pov.Segment1,
Pl.Item_Description,Prl.Manufacturer_Part_Number,Uom.Uom_Code,
Ph.Revised_Date,Ph.Creation_Date, Pl.Quantity, Pll.Quantity_Received,Pl.Unit_Price,Cc.Segment1,Cc.Segment2,
CC.SEGMENT3,CC.SEGMENT4,PH.APPROVED_FLAG,PH.REVISION_NUM,
RSH.CREATION_DATE,
PRL.ATTRIBUTE3,
RT.TRANSACTION_TYPE,RT.QUANTITY,
RT.PO_UNIT_PRICE,RSH.RECEIPT_NUM
,HL.ATTRIBUTE1,HR.ATTRIBUTE2,POVS.VENDOR_SITE_CODE,RT.SHIPMENT_HEADER_ID,
RT.TRANSACTION_DATE


This gives me below result-

SEGMENT1 CREATION_DATE QUANTITY_ORDERED QUANTITY_RECEIVED UNIT_PRICE ITEM_VALUE RECEIPT_VALUE RECORD_TYPE
3097102 16-Aug-10 4 3 5.29 21.16 15.87 RCC
3097102 16-Aug-10 4 1 5.29 21.16 21.16 RCA
3097102 16-Aug-10 4 -2 5.29 21.16 21.16 RCA
3097102 16-Aug-10 4 1 5.29 21.16 5.29 RCC
3097102 16-Aug-10 4 -1 5.29 21.16 5.29 RCA

But this should be as below

Segment1 CREATION_DATE QUANTITY_ORDERED QUANTITY_RECEIVED UNIT_PRICE ITEM_VALUE RECEIPT_VALUE RECORD_TYPE
3097102 16-Aug-10 4 3 5.29 21.16 15.87 RCC
3097102 16-Aug-10 4 1 5.29 21.16 21.16 RCA
3097102 16-Aug-10 4 -2 5.29 21.16 10.58 RCA
3097102 16-Aug-10 4 -1 5.29 21.16 0 RCA
3097102 16-Aug-10 4 1 5.29 21.16 5.29 RCC


Now since Quantity column of RCV_TRANSACTIOns is positive quantity_received is getting summed up.
How can I achieve total quantityy received as (3+1+2 -(3+1) =2.
I Hope you understand what I am trying to say.

Please help.
Manisha