FORMATTED QUERY RETURNING MULTIPLE DUPLICATES 

SELECT SUM ( AL1.BAS_CNFRMD_QTY ), SUM (AL1.BAS_ORDR_ENTRY_QTY ), AL1.CRD, SUM ( AL1.BAS_RESERVED_QTY ),
	AL1.CHNL_CLASS, AL1.USAGE, AL1.DS_QLFR, 
	DECODE(to_char(AL1.ETA_DT,'YYYYMMDD'),'19000101',NULL,To_Char (AL1.ETA_DT,'YYYYMMDD')),
		      AL3.DS_ETA_DATE, 
       case when AL1.ITEM_CAT in ('ZJTS','ZJTP', 'ZJTD') then AL3.DS_ETA_DATE else 
	to_date((DECODE(to_char(AL1.ETA_DT,'YYYYMMDD'),'19000101',NULL,To_Char(AL1.ETA_DT,'YYYYMMDD'))),'yyyymmdd')
       end, 
	SUM ( AL1.BAS_RESERVED_IN_TRANSIT_QTY ), SUM(AL1.BAS_RESERVED_PO_QTY ), AL1.FIRM_COMMIT_FLAG 

FROM OPS_RPT.RPT_SLS_ORDR_FACT AL1, OPS_RPT.RPT_DS_ETA_DATE AL3 

WHERE [B]AL1.SO_HDR_NO = AL3.KEY_DRS_SO_NO (+) 
AND AL1.SO_LN_ITM_NO = AL3.KEY_DRS_SO_LN (+)
AND AL1.SO_HDR_NO='0810410391'
AND AL1.SO_LN_ITM_NO='000010' 

GROUP BY AL1.CRD, AL1.CHNL_CLASS, AL1.USAGE, AL1.DS_QLFR,DECODE(to_char(AL1.ETA_DT,'YYYYMMDD'),'19000101',NULL,To_Char(AL1.ETA_DT,'YYYYMMDD')),AL3.DS_ETA_DATE,
 case when AL1.ITEM_CAT in ('ZJTS','ZJTP', 'ZJTD') then AL3.DS_ETA_DATE 
else 
  to_date((DECODE(to_char(AL1.ETA_DT,'YYYYMMDD'),'19000101',NULL,To_Char(AL1.ETA_DT,'YYYYMMDD'))),'yyyymmdd') end, 
  AL1.FIRM_COMMIT_FLAG 



TABLE STRUCTURE FOR THE OPS_RPT.RPT_DS_ETA_DATE 


COLUMN NAME          DATATYPE

KEY_PO_HDR (IDX)  	(VARCHAR2 (10))
KEY_PO_LINE (IDX)  	(VARCHAR2 (5))
KEY_IB_DELIVERY_NO (IDX)(VARCHAR2 (10))
PO_TYPE  		(VARCHAR2 (4))
PO_CREATE_DATE  	(DATE)
PO_DOC_DATE  		(DATE)
PO_POST_DATE  		(DATE)
DIVISION (IDX)  	(VARCHAR2 (4000))
REGION  		(VARCHAR2 (4000))
KEY_PURCH_ORG (IDX)  	(VARCHAR2 (4))
KEY_PURCH_GRP (IDX)  	(VARCHAR2 (3))
PURCH_DOC_CAT  		(VARCHAR2 (1))
STAT_DELV_DATE  	(DATE)
PO_REASON_CD  		(VARCHAR2 (3))
PO_DOC_STATUS 		(VARCHAR2 (1))
ITEM_CAT (IDX)  	(VARCHAR2 (1))
ACCT_ASSGNMNT  		(VARCHAR2 (1))
KEY_DRS_SO_NO (IDX)  	(VARCHAR2 (10))
KEY_DRS_SO_LN (IDX)  	(VARCHAR2 (6))
PURCH_REQ_NO  		(VARCHAR2 (10))
PO_REQ_TYPE  		(VARCHAR2 (4))
FINAL_DELIVERY  	(VARCHAR2 (1))
DELETION_FLG  		(VARCHAR2 (1))
RPR  			(VARCHAR2 (10))
KEY_IB_ACCT_SLDT_DIM_ID (NUMBER)
KEY_MATERIAL_DIM_ID  	(NUMBER)
KEY_SLSMATRL_DIM_ID (IDX)(NUMBER)
KEY_SLS_ORG_DIM_ID  	(VARCHAR2 (4))
KEY_REGION_DIM_ID (IDX)  (VARCHAR2 (4000))
KEY_PLANT_DIM_ID  	(VARCHAR2 (4))
BUY_DATE  		(VARCHAR2 (8))
PLANT (IDX)  		(VARCHAR2 (4))
PLAN_DLVRY_DT  		(DATE)
ORIGINAL_GAC_DT  	(DATE)
GAC_DT  		(DATE)
PO_SCL_DATE  		(DATE)
VENDOR (IDX)  		(VARCHAR2 (10))
VENDOR_COUNTRY  	(VARCHAR2 (3))
MATERIAL_GRP  		(VARCHAR2 (9))
MATERIAL (IDX)  	(VARCHAR2 (18))
MATERIAL_DESC  		(VARCHAR2 (40))
GOAL_PO  		(VARCHAR2 (12))
FISCAL_VARIANT  	(VARCHAR2 (2))
COMPANY_CODE (IDX)  	(VARCHAR2 (4))
CREATED_BY  		(VARCHAR2 (12))
STORAGE_LOC  		(VARCHAR2 (4))
SUPPLY_PLANT  		(VARCHAR2 (4))
PARTNER_VENDOR  	(VARCHAR2 (10))
LIAISON_OFFICE  	(VARCHAR2 (10))
GOODS_SUPPLIER  	(VARCHAR2 (10))
SUPPLY_VENDOR  		(VARCHAR2 (10))
SUPPLYING_PLANT  	(VARCHAR2 (10))
GS_COUNTRY  		(VARCHAR2 (3))
IB_SLS_ORG  		(VARCHAR2 (4))
IB_RECEIVING_PT  	(VARCHAR2 (4))
IB_MATERIAL_TXT  	(VARCHAR2 (40))
IB_PLANNED_GID  	(DATE)
IB_ACTUAL_GID  		(DATE)
IB_DELIVERY_DT  	(DATE)
INCOMING_DATE (IDX)	(DATE)
IB_DELIVERY_TYPE  	(VARCHAR2 (4))
IB_GDS_MVMT_STATUS  	(VARCHAR2 (1))
DLVRY_MODE  		(VARCHAR2 (2))
DELV_DT_LINE  		(DATE)
VENDOR_MATERIAL  	(VARCHAR2 (35))
MSR_INDICATOR  		(VARCHAR2 (1))
NET_PRICE  		(NUMBER)
PRICE_UOM  		(NUMBER)
FOB_AMT  		(NUMBER)
DUTY_AMT  		(NUMBER)
COSTING_AMT  		(NUMBER)
OTHER_COST_AMT  	(NUMBER)
IC_STK_XFER  		(NUMBER)
LOCAL_CURR  		(VARCHAR2 (5))
PO_CURR  		(VARCHAR2 (5))
ORDER_UOM  		(VARCHAR2 (3))
BASE_UOM  		(VARCHAR2 (3))
SALES_UOM  		(VARCHAR2 (3))
GR_INDICATOR  		(VARCHAR2 (1))
IR_INDICATOR  		(VARCHAR2 (1))
EX_FCT_DT  		(DATE)
PO_LN_ADDR_CD  		(VARCHAR2 (10))
DLVRY_CMPLT_FLG  	(VARCHAR2 (1))
PO_DLVRY_CMPLT_FLG  	(VARCHAR2 (1))
DRS_SO_HDR_NO  		(VARCHAR2 (12))
DRS_SO_LN_NO  		(VARCHAR2 (6))
PO_DOC_CHNG_DT  	(VARCHAR2 (8))
VNDR_SUBCNTRCT_FLG  	(VARCHAR2 (8))
PURCH_REQ_ITEM_NO  	(VARCHAR2 (10))
DECONSOL_FLG  		(VARCHAR2 (20))
IB_DLVRY_ITEM_CAT  	(VARCHAR2 (4))
IB_DLVRY_TOT_WT_UOM  	(VARCHAR2 (3))
IB_DLVRY_TOT_VOL_UOM  	(VARCHAR2 (3))
AFS_STOCK_CAT  		(VARCHAR2 (16))
AFS_REQ_CAT  		(VARCHAR2 (16))
QUALITY  		(VARCHAR2 (2))
ISEG  			(VARCHAR2 (3))
BATCH_NO  		(VARCHAR2 (10))
IB_DLVRY_TOT_WT  	(NUMBER)
IB_DLVRY_NET_WT  	(NUMBER)
IB_DLVRY_TOT_VOL  	(NUMBER)
IB_DLVRY_QTY_BASE  	(NUMBER)
ORIGIN_RECEIPT_DT  	(DATE)
ORIGIN_RECEIPT_QTY  	(NUMBER)
IB_DELIVER_QTY  	(NUMBER)
IB_NO_OF_CARTONS  	(NUMBER)
INTRANSIT_QTY  		(NUMBER)
RECEIVED_QTY  		(NUMBER)
SHIPPING_NOTIFY_QTY  	(NUMBER)
BAL_AT_VENDOR_QTY  	(NUMBER)
BAL_AT_ORIGIN_QTY  	(NUMBER)
ORDERED_QTY  		(NUMBER)
QTY_REDUCED  		(NUMBER)
PO_QTY_SIZE  		(NUMBER)
BAL_TO_SHP_QTY  	(NUMBER)
INCOMING_QTY  		(NUMBER)
ORIGIN_PORT_VENDOR  	(VARCHAR2 (10))
GAC_RSN_CD  		(VARCHAR2 (10))
PLNG_SESN_CD  		(VARCHAR2 (2))
PLNG_SESN_YR  		(NUMBER)
VAS_FLAG  		(VARCHAR2 (1))
MSR_FLAG  		(VARCHAR2 (1))
PO_CNFRMTN_STATUS  	(VARCHAR2 (5))
KEY_GOAL_PO_TYP_DIM_ID  (VARCHAR2 (39))
UNSHIPPED_QTY  		(NUMBER)
BW_RTL_VAL_LOC_CUR  	(NUMBER)
FC_QTY  		(NUMBER)
PO_ACPT_DT  		(VARCHAR2 (8))
FC_DT  			(VARCHAR2 (8))
KEY_CUST_SOLD_TO_DIM_ID (IDX)  (NUMBER)
KEY_SOLD_TO_PRFL_DIM_ID (IDX)  (NUMBER)
KEY_CUST_SHIP_TO_DIM_ID (IDX)  (NUMBER)
KEY_SHIP_TO_PRFL_DIM_ID (IDX)  (NUMBER)
KEY_CUST_DEPT_DIM_ID  	(VARCHAR2 (25))
KEY_SLS_REP_DIM_ID  	(VARCHAR2 (25))
KEY_DIVISION_DIM_ID 	(IDX)  (VARCHAR2 (25))
KEY_CCD_BUCKET_DIM_ID 	(IDX)  (NUMBER)
KEY_CRD_BUCKET_DIM_ID 	(IDX)  (NUMBER)
KEY_VAS_SSO_LN_DIM_ID  	(NUMBER)
KEY_VAS_TL_LN_DIM_ID  	(NUMBER)
KEY_VAS_PK_LN_DIM_ID  	(NUMBER)
SOLD_TO_CUST  		(VARCHAR2 (25))
SHIP_TO_CUST  		(VARCHAR2 (25))
BILL_TO_CUST  		(VARCHAR2 (10))
CUST_DEPT  		(VARCHAR2 (25))
BUYING_GRP  		(VARCHAR2 (25))
SLS_REP  		(VARCHAR2 (25))
SO_HDR_NO (IDX)  	(VARCHAR2 (25))
SO_LN_ITM_NO (IDX)  	(VARCHAR2 (25))
SLS_ORG (IDX)  		(VARCHAR2 (25))
ORDER_TYPE (IDX)  	(VARCHAR2 (25))
SLS_ORDR_ITEM_CAT  	(VARCHAR2 (25))
SUPPLY_CHAIN_GRP  	(VARCHAR2 (25))
SLS_ORDR_REGION (IDX)   (VARCHAR2 (6))
BUSINESS_TYPE  		(VARCHAR2 (6))
ACCT_TYPE  		(VARCHAR2 (6))
CHNL_CLASS  		(VARCHAR2 (6))
REPORT_GRP  		(VARCHAR2 (6))
SALES_OFFICE  		(VARCHAR2 (6))
SLS_ORDR_DIVISION  	(VARCHAR2 (25))
SLS_ORDR_PLANT (IDX)  	(VARCHAR2 (25))
DISTRBTN_CHNL  		(VARCHAR2 (6))
RETURN_REASON  		(VARCHAR2 (25))
ORDER_SRC  		(VARCHAR2 (6))
DLVRY_GRP  		(VARCHAR2 (25))
CUST_PO  		(VARCHAR2 (60))
CUST_PO_DT  		(DATE)
MARK_FOR  		(VARCHAR2 (25))
SLS_ORDR_MATERIAL (IDX) (VARCHAR2 (25))
SLS_ORDR_MATERIAL_DESC  (VARCHAR2 (50))
LIFECYCLE  		(VARCHAR2 (25))
SLS_ORDR_BUS_ORG  	(VARCHAR2 (6))
SLS_ORDR_CAT_CD  	(VARCHAR2 (6))
SLS_ORDR_SUB_CAT  	(VARCHAR2 (6))
GENDER_AGE  		(VARCHAR2 (6))
SLS_ORDR_TYPE_GRP  	(VARCHAR2 (6))
SLS_ORDR_SEGMENT  	(VARCHAR2 (6))
SLS_ORDR_AFS_CAT  	(VARCHAR2 (25))
SLS_ORDR_QUALITY_CD  	(VARCHAR2 (2))
SLS_ORDR_ISEG_CD  	(VARCHAR2 (3))
IDP_FLAG  		(CHAR (1))
IDP_DT  		(DATE)
IDP_PLND_FLAG  		(CHAR (1))
PLND_SCHDLD_DT  	(DATE)
CNTRCT_HDR_NO (IDX)  	(VARCHAR2 (25))
CNTRCT_LN_ITM_NO (IDX)  (VARCHAR2 (25))
VAS_GRP  		(VARCHAR2 (6))
UOM_CNVRSN_FCTR  	(NUMBER)
SLS_UOM 		(VARCHAR2 (6))
BAS_UOM  		(VARCHAR2 (3))
LCL_CURRENCY  		(VARCHAR2 (6))
DOC_CRTN_DT  		(DATE)
DOC_DT  		(DATE)
SOH_CHNG_DT  		(DATE)
SO_LN_ITM_CRNT_DT  	(DATE)
CNTRCT_START_DT  	(DATE)
CNTRCT_END_DT  		(DATE)
CCD (IDX)  		(DATE)
CRD  			(DATE)
CANCEL_DT  		(DATE)
PLANNED_GID  		(DATE)
MAD  			(DATE)
LAUNCH_FLAG  		(CHAR (1))
PROMO_FLAG  		(CHAR (1))
BILL_BLCK_HDR  		(VARCHAR2 (2))
BILL_BLK_LN  		(VARCHAR2 (6))
DLVRY_BLCK_HDR  	(VARCHAR2 (25))
RJCT_CD_LN  		(VARCHAR2 (2))
RJCT_CD_SZ  		(VARCHAR2 (25))
DIRECT_SHIP_FLAG  	(VARCHAR2 (1))
ORDR_CMPLT_STATUS  	(CHAR (1))
DLVRY_CMPLT_STATUS  	(CHAR (1))
INVC_STATUS  		(CHAR (1))
CREDIT_STATUS  		(VARCHAR2 (6))
REJECT_STATUS  		(CHAR (1))
ALLOC_STATUS  		(VARCHAR2 (6))
INCOMPLETE_STATUS  	(VARCHAR2 (1))
ETA_TYPE  		(CHAR (13))
ETA_DT  		(DATE)
SLS_ORDR_NET_PRICE  	(NUMBER)
WHLSL_UNIT_PRICE  	(NUMBER)
SLS_ORDR_ENTRY_QTY  	(NUMBER)
BAS_ORDR_ENTRY_QTY  	(NUMBER)
NET_ORDR_ENTRY_AMT  	(NUMBER)
WHLSL_ORDR_ENTRY_AMT  	(NUMBER)
SLS_UNCNFRMED_QTY  	(NUMBER)
BAS_UNCNFRMD_QTY  	(NUMBER)
NET_UNCNFRMD_AMT  	(NUMBER)
WHLSL_UNCNFRMD_AMT  	(NUMBER)
SLS_REJECT_QTY  	(NUMBER)
BAS_REJECT_QTY  	(NUMBER)
NET_REJECT_AMT  	(NUMBER)
WHLSL_REJECT_AMT  	(NUMBER)
SLS_CNFRMD_QTY  	(NUMBER)
BAS_CNFRMD_QTY  	(NUMBER)
NET_CNFRMD_AMT  	(NUMBER)
WHLSL_CNFRMD_AMT  	(NUMBER)
AIR_FRGHT_PCT  		(NUMBER)
FAF_PRE_JULY05  	(NUMBER)
MAX_SHPPNG_NOTIFY_ETA_DT  (DATE)


------------------------------------------