CREATE TABLE SAP_RECON_SDB_RET_PRICE_DTL2 
( 
  SDB_SAP_KOTABNR    VARCHAR2(1 BYTE), 
  SDB_SAP_KSCHL      VARCHAR2(4 BYTE), 
  SDB_SAP_VKORG      VARCHAR2(4 BYTE), 
  SDB_SAP_VTWEG      VARCHAR2(2 BYTE), 
  SDB_SAP_WERKS      VARCHAR2(4 BYTE), 
  SDB_SAP_PLTYP      VARCHAR2(2 BYTE), 
  SDB_SAP_MATNR      VARCHAR2(18 BYTE), 
  SDB_SAP_VRKME      VARCHAR2(3 BYTE), 
  SDB_KBETR          NUMBER, 
  SDB_KONWA          VARCHAR2(5 BYTE), 
  SDB_KPEIN          NUMBER(5), 
  SDB_SAP_KMEIN      VARCHAR2(3 BYTE), 
  SDB_DATAB          VARCHAR2(8 BYTE), 
  SDB_DATBI          VARCHAR2(8 BYTE), 
  FOM_SAP_KOTABNR    VARCHAR2(1 BYTE), 
  FOM_SAP_KSCHL      VARCHAR2(4 BYTE), 
  FOM_SAP_VKORG      VARCHAR2(4 BYTE), 
  FOM_SAP_VTWEG      VARCHAR2(2 BYTE), 
  FOM_SAP_WERKS      VARCHAR2(4 BYTE), 
  FOM_SAP_PLTYP      VARCHAR2(2 BYTE), 
  FOM_COUNTRY_IDEN   VARCHAR2(30 BYTE), 
  FOM_SAP_MATNR      VARCHAR2(18 BYTE), 
  FOM_SAP_VRKME      VARCHAR2(3 BYTE), 
  FOM_KBETR          VARCHAR2(22 BYTE), 
  FOM_KONWA          VARCHAR2(5 BYTE), 
  FOM_KPEIN          NUMBER(22), 
  FOM_SAP_KMEIN      VARCHAR2(3 BYTE), 
  FOM_DATAB          VARCHAR2(8 BYTE), 
  FOM_DATBI          VARCHAR2(8 BYTE), 
  RESULT_TYPE        VARCHAR2(2000 BYTE), 
  D_RECON_DATE       DATE, 
  V_REMARKS          VARCHAR2(2000 BYTE), 
  UPLOADED_VALID_TO  VARCHAR2(11 BYTE), 
  ROWNUMBER          NUMBER 
)

Insert into SAP_RECON_SDB_RET_PRICE_DTL2 
   (sdb_sap_kotabnr, sdb_sap_kschl, sdb_sap_vkorg, sdb_sap_vtweg, sdb_sap_matnr, sdb_sap_vrkme, sdb_kbetr, sdb_konwa, sdb_kpein, sdb_sap_kmein, sdb_datab, sdb_datbi, fom_sap_kotabnr, fom_sap_kschl, fom_sap_vkorg, fom_sap_vtweg, fom_sap_matnr, fom_sap_vrkme, fom_kbetr, fom_konwa, fom_kpein, fom_sap_kmein, fom_datab, fom_datbi, result_type, d_recon_date, rownumber) 
 Values 
   ('1', 'VKP0', 'B100', '01', '000000000002043171', 'KG', 1.69, 'BND', 1, 'KG', '20161221', '99991231', '1', 'VKP0', 'B100', '01', '000000000002043171', 'KG', '1.69', 'BND', 1, 'KG', '20161221', '20170328', 'VALID_TO_MISMATCH', TO_DATE('04/05/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1)

Insert into SAP_RECON_SDB_RET_PRICE_DTL2 
   (sdb_sap_kotabnr, sdb_sap_kschl, sdb_sap_vkorg, sdb_sap_vtweg, sdb_sap_matnr, sdb_sap_vrkme, sdb_kbetr, sdb_konwa, sdb_kpein, sdb_sap_kmein, sdb_datab, sdb_datbi, fom_sap_kotabnr, fom_sap_kschl, fom_sap_vkorg, fom_sap_vtweg, fom_sap_matnr, fom_sap_vrkme, fom_kbetr, fom_konwa, fom_kpein, fom_sap_kmein, fom_datab, fom_datbi, result_type, d_recon_date, rownumber) 
 Values 
   ('1', 'VKP0', 'B100', '01', '000000000002043171', 'KG', 1.99, 'BND', 1, 'KG', '20170329', '99991231', '1', 'VKP0', 'B100', '01', '000000000002043171', 'KG', '1.99', 'BND', 1, 'KG', '20170329', '20170403', 'VALID_TO_MISMATCH', TO_DATE('04/05/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 2)

Insert into SAP_RECON_SDB_RET_PRICE_DTL2 
   (sdb_sap_kotabnr, sdb_sap_kschl, sdb_sap_vkorg, sdb_sap_vtweg, sdb_sap_matnr, sdb_sap_vrkme, sdb_kbetr, sdb_konwa, sdb_kpein, sdb_sap_kmein, sdb_datab, sdb_datbi, fom_sap_kotabnr, fom_sap_kschl, fom_sap_vkorg, fom_sap_vtweg, fom_sap_matnr, fom_sap_vrkme, fom_kbetr, fom_konwa, fom_kpein, fom_sap_kmein, fom_datab, fom_datbi, result_type, d_recon_date, rownumber) 
 Values 
   ('1', 'VKP0', 'M100', '01', '000000000002043171', 'KG', 4.99, 'MYR', 1, 'KG', '20161224', '99991231', '1', 'VKP0', 'M100', '01', '000000000002043171', 'KG', '4.99', 'MYR', 1, 'KG', '20161224', '20170220', 'VALID_TO_MISMATCH', TO_DATE('04/05/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1)

Insert into SAP_RECON_SDB_RET_PRICE_DTL2 
   (sdb_sap_kotabnr, sdb_sap_kschl, sdb_sap_vkorg, sdb_sap_vtweg, sdb_sap_pltyp, sdb_sap_matnr, sdb_sap_vrkme, sdb_kbetr, sdb_konwa, sdb_kpein, sdb_sap_kmein, sdb_datab, sdb_datbi, fom_sap_kotabnr, fom_sap_kschl, fom_sap_vkorg, fom_sap_vtweg, fom_sap_pltyp, fom_sap_matnr, fom_sap_vrkme, fom_kbetr, fom_konwa, fom_kpein, fom_sap_kmein, fom_datab, fom_datbi, result_type, d_recon_date, rownumber) 
 Values 
   ('2', 'VKP0', 'M100', '01', 'P1', '000000000002043171', 'KG', 3.59, 'MYR', 1, 'KG', '20161201', '99991231', '2', 'VKP0', 'M100', '01', 'P1', '000000000002043171', 'KG', '3.59', 'MYR', 1, 'KG', '20161201', '20170331', 'VALID_TO_MISMATCH', TO_DATE('04/05/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1)

Insert into SAP_RECON_SDB_RET_PRICE_DTL2 
   (sdb_sap_kotabnr, sdb_sap_kschl, sdb_sap_vkorg, sdb_sap_vtweg, sdb_sap_pltyp, sdb_sap_matnr, sdb_sap_vrkme, sdb_kbetr, sdb_konwa, sdb_kpein, sdb_sap_kmein, sdb_datab, sdb_datbi, fom_sap_kotabnr, fom_sap_kschl, fom_sap_vkorg, fom_sap_vtweg, fom_sap_pltyp, fom_sap_matnr, fom_sap_vrkme, fom_kbetr, fom_konwa, fom_kpein, fom_sap_kmein, fom_datab, fom_datbi, result_type, d_recon_date, rownumber) 
 Values 
   ('2', 'VKP0', 'M100', '01', 'P2', '000000000002043171', 'KG', 3.59, 'MYR', 1, 'KG', '20161201', '99991231', '2', 'VKP0', 'M100', '01', 'P2', '000000000002043171', 'KG', '3.59', 'MYR', 1, 'KG', '20161201', '20170331', 'VALID_TO_MISMATCH', TO_DATE('04/05/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1)

Insert into SAP_RECON_SDB_RET_PRICE_DTL2 
   (sdb_sap_kotabnr, sdb_sap_kschl, sdb_sap_vkorg, sdb_sap_vtweg, sdb_sap_pltyp, sdb_sap_matnr, sdb_sap_vrkme, sdb_kbetr, sdb_konwa, sdb_kpein, sdb_sap_kmein, sdb_datab, sdb_datbi, fom_sap_kotabnr, fom_sap_kschl, fom_sap_vkorg, fom_sap_vtweg, fom_sap_pltyp, fom_sap_matnr, fom_sap_vrkme, fom_kbetr, fom_konwa, fom_kpein, fom_sap_kmein, fom_datab, fom_datbi, result_type, d_recon_date, rownumber) 
 Values 
   ('2', 'VKP0', 'M100', '01', 'P3', '000000000002043171', 'KG', 4.99, 'MYR', 1, 'KG', '20161224', '99991231', '2', 'VKP0', 'M100', '01', 'P3', '000000000002043171', 'KG', '4.99', 'MYR', 1, 'KG', '20161224', '20170220', 'VALID_TO_MISMATCH', TO_DATE('04/05/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1)

Insert into SAP_RECON_SDB_RET_PRICE_DTL2 
   (sdb_sap_kotabnr, sdb_sap_kschl, sdb_sap_vkorg, sdb_sap_vtweg, sdb_sap_pltyp, sdb_sap_matnr, sdb_sap_vrkme, sdb_kbetr, sdb_konwa, sdb_kpein, sdb_sap_kmein, sdb_datab, sdb_datbi, fom_sap_kotabnr, fom_sap_kschl, fom_sap_vkorg, fom_sap_vtweg, fom_sap_pltyp, fom_sap_matnr, fom_sap_vrkme, fom_kbetr, fom_konwa, fom_kpein, fom_sap_kmein, fom_datab, fom_datbi, result_type, d_recon_date, rownumber) 
 Values 
   ('2', 'VKP0', 'M100', '01', 'P5', '000000000002043171', 'KG', 6.99, 'MYR', 1, 'KG', '20161011', '99991231', '2', 'VKP0', 'M100', '01', 'P5', '000000000002043171', 'KG', '6.99', 'MYR', 1, 'KG', '20161011', '20170313', 'VALID_TO_MISMATCH', TO_DATE('04/05/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1)

Insert into SAP_RECON_SDB_RET_PRICE_DTL2 
   (sdb_sap_kotabnr, sdb_sap_kschl, sdb_sap_vkorg, sdb_sap_vtweg, sdb_sap_pltyp, sdb_sap_matnr, sdb_sap_vrkme, sdb_kbetr, sdb_konwa, sdb_kpein, sdb_sap_kmein, sdb_datab, sdb_datbi, fom_sap_kotabnr, fom_sap_kschl, fom_sap_vkorg, fom_sap_vtweg, fom_sap_pltyp, fom_sap_matnr, fom_sap_vrkme, fom_kbetr, fom_konwa, fom_kpein, fom_sap_kmein, fom_datab, fom_datbi, result_type, d_recon_date, rownumber) 
 Values 
   ('2', 'VKP0', 'M100', '01', 'P6', '000000000002043171', 'KG', 6.99, 'MYR', 1, 'KG', '20161011', '99991231', '2', 'VKP0', 'M100', '01', 'P6', '000000000002043171', 'KG', '6.99', 'MYR', 1, 'KG', '20161011', '20170313', 'VALID_TO_MISMATCH', TO_DATE('04/05/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1)

Insert into SAP_RECON_SDB_RET_PRICE_DTL2 
   (sdb_sap_kotabnr, sdb_sap_kschl, sdb_sap_vkorg, sdb_sap_vtweg, sdb_sap_pltyp, sdb_sap_matnr, sdb_sap_vrkme, sdb_kbetr, sdb_konwa, sdb_kpein, sdb_sap_kmein, sdb_datab, sdb_datbi, fom_sap_kotabnr, fom_sap_kschl, fom_sap_vkorg, fom_sap_vtweg, fom_sap_pltyp, fom_sap_matnr, fom_sap_vrkme, fom_kbetr, fom_konwa, fom_kpein, fom_sap_kmein, fom_datab, fom_datbi, result_type, d_recon_date, rownumber) 
 Values 
   ('2', 'VKP0', 'M101', '01', 'P4', '000000000002043171', 'KG', 7.49, 'MYR', 1, 'KG', '20161011', '99991231', '2', 'VKP0', 'M101', '01', 'P4', '000000000002043171', 'KG', '7.49', 'MYR', 1, 'KG', '20161011', '20170313', 'VALID_TO_MISMATCH', TO_DATE('04/05/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1)

COMMIT

select * From SAP_RECON_SDB_RET_PRICE_DTL2

UPDATE sap_recon_sdb_ret_price_dtl2 x 
   SET SDB_DATBI = (SELECT to_char(max(to_date(sdb_datab,'YYYYMMDD')) -1,'YYYYMMDD') 
   					 FROM sap_recon_sdb_ret_price_dtl2 y 
   					WHERE --y.sdb_sap_matnr			=	'000000000002043171' AND 
	     			  y.sdb_sap_kotabnr			=	x.sdb_sap_kotabnr 
	     			  AND y.sdb_sap_kschl			=	x.sdb_sap_kschl 
	     			  AND y.sdb_sap_vtweg			=	x.sdb_sap_vtweg 
	     			  AND y.sdb_sap_vkorg			=	x.sdb_sap_vkorg 
	     			  AND NVL(y.sdb_sap_pltyp,'$')	=	NVL(x.sdb_sap_pltyp,'$') 
	     			  AND y.sdb_sap_matnr			=	x.sdb_sap_matnr 
	     			  AND y.sdb_sap_vrkme			=	x.sdb_sap_vrkme 
	     			  and y.ROWNUMBER				=	x.rownumber + 1 
	     		group by y.sdb_sap_matnr,y.sdb_sap_kotabnr,y.sdb_sap_kschl,y.sdb_sap_vtweg,y.sdb_sap_vkorg,NVL(y.sdb_sap_pltyp,'$'),y.sdb_sap_vrkme) 
 WHERE x.result_type				=	'VALID_TO_MISMATCH' 
   and exists (SELECT 'X' 
 				 FROM sap_recon_sdb_ret_price_dtl2 y 
  				WHERE 
  				--y.sdb_sap_matnr			=	'000000000002043171' AND 
     			  y.sdb_sap_kotabnr			=	x.sdb_sap_kotabnr 
     			  AND y.sdb_sap_kschl			=	x.sdb_sap_kschl 
     			  AND y.sdb_sap_vtweg			=	x.sdb_sap_vtweg 
     			  AND y.sdb_sap_vkorg			=	x.sdb_sap_vkorg 
     			  AND NVL(y.sdb_sap_pltyp,'$')	=	NVL(x.sdb_sap_pltyp,'$') 
     			  AND y.sdb_sap_matnr			=	x.sdb_sap_matnr 
     			  AND y.sdb_sap_vrkme			=	x.sdb_sap_vrkme 
     			  and y.rownumber				=	x.rownumber + 1) 

select * from sap_recon_sdb_ret_price_dtl2 where result_type				=	'VALID_TO_MISMATCH'