
INSERT INTO C#STUDENTMASTER (INSTITUTECODE, COUNSELLINGID, PROGRAMTYPE, RANK, ROLLNO, NAME, SEX, DATEOFBIRTH, FATHERSHUSBANDNAME, 
   LOCALGUARDIAN, SUBJECTCODE, LOCATIONCODE,DEACTIVE, STUDENTID, FEESPAID, QUOTA, APPLICATIONNO, APPLICATIONID,REFNO)
SELECT 'JPWG',a.COUNSELLINGID, 'UG', AieeeRANK, AIEEEROLLNUMBER, ltrim(rtrim(rtrim(ltrim(rtrim(FIRSTNAME))||' '||ltrim(rtrim(MIDDLENAME)))||' '||ltrim(rtrim(LASTNAME)))),
SEX, DATEOFBIRTH,FATHERNAME,NULL,NULL,NULL,'N',NULL,NULL,'GENERAL',APPLICATIONNO,a.APPLICATIONID,REFNO 
from C#APPLICATIONMASTER a   , C#APPLICATIONMASTERDETAIL b
where nvl(a.REJECTED,'N') ='N'
and  a.CATEGORYCODE in ('GEN','OBC')
and a.aieeerank is not null
and a.aieeerank <> '------'
and a.COUNSELLINGID =b.COUNSELLINGID
and a.APPLICATIONID =b.APPLICATIONID
and b.APPLIEDINSTITUTECODE IN ('JIIT')
AND AIEEERANK IN ('149661','231446')
GROUP BY a.COUNSELLINGID, Aieeerank, AIEEEROLLNUMBER, ltrim(rtrim(rtrim(ltrim(rtrim(FIRSTNAME))||' '||ltrim(rtrim(MIDDLENAME)))||' '||ltrim(rtrim(LASTNAME)))),
SEX, DATEOFBIRTH,FATHERNAME,APPLICATIONNO,a.APPLICATIONID,REFNO
ORDER BY TO_NUMBER(AieeeRANK)

INSERT INTO C#CATEGORYAPPLIEDFOR 
(INSTITUTECODE, COUNSELLINGID, PROGRAMTYPE,RANK, CHOICENO, CATEGORY, DEACTIVE, ELIGIBLE) 
SELECT 'JPWG',A.COUNSELLINGID, 'UG', A.AieeeRANK, 1, A.CATEGORYCODE,'N','Y'
from C#APPLICATIONMASTER a   , C#APPLICATIONMASTERDETAIL b
where nvl(a.REJECTED,'N') ='N'
and a.aieeerank is not null
and a.aieeerank <> '------'
and a.COUNSELLINGID =b.COUNSELLINGID
and a.APPLICATIONID =b.APPLICATIONID
and b.APPLIEDINSTITUTECODE IN ('JIIT')
AND AIEEERANK IN ('149661','231446')
GROUP BY A.COUNSELLINGID, A.AieeeRANK, A.CATEGORYCODE
ORDER BY TO_NUMBER(AieeeRANK)


INSERT INTO C#STUDENTPROGRAMCHOICE (
   COUNSELLINGID, PROGRAMTYPE, PROGRAMCODE,RANK, ENTRYBY, ENTRYDATE,DEACTIVE) 
SELECT A.COUNSELLINGID, 'UG','BTech', A.AIEEERANK,USER,SYSDATE,'N'
from C#APPLICATIONMASTER a   , C#APPLICATIONMASTERDETAIL b
where nvl(a.REJECTED,'N') ='N'
and a.aieeerank is not null
and a.aieeerank <> '------'
and a.COUNSELLINGID =b.COUNSELLINGID
and a.APPLICATIONID =b.APPLICATIONID
and b.APPLIEDINSTITUTECODE IN ('JIIT')
AND AIEEERANK IN ('149661','231446')
GROUP BY A.COUNSELLINGID,  A.AIEEERANK
ORDER BY TO_NUMBER(AieeeRANK)

INSERT INTO C#STUDENTPROGRAMCHOICE (
   COUNSELLINGID, PROGRAMTYPE, PROGRAMCODE,RANK, ENTRYBY, ENTRYDATE,DEACTIVE) 
SELECT A.COUNSELLINGID, 'UG','DUAL', A.AIEEERANK,USER,SYSDATE,'N'
from C#APPLICATIONMASTER a   , C#APPLICATIONMASTERDETAIL b
where nvl(a.REJECTED,'N') ='N'
and a.aieeerank is not null
and a.aieeerank <> '------'
and a.COUNSELLINGID =b.COUNSELLINGID
and a.APPLICATIONID =b.APPLICATIONID
and b.APPLIEDINSTITUTECODE IN ('JIIT')
AND AIEEERANK IN ('149661','231446')
GROUP BY A.COUNSELLINGID,  A.AIEEERANK
ORDER BY TO_NUMBER(AieeeRANK)

INSERT INTO C#STUDENTiNSTITUTECHOICE (
   COUNSELLINGID, iNSTITUTECODE,RANK, ENTRYBY, ENTRYDATE,DEACTIVE) 
SELECT A.COUNSELLINGID, 'JUET', A.AIEEERANK,USER,SYSDATE,'N'
from C#APPLICATIONMASTER a   , C#APPLICATIONMASTERDETAIL b
where nvl(a.REJECTED,'N') ='N'
and a.aieeerank is not null
and a.aieeerank <> '------'
and a.COUNSELLINGID =b.COUNSELLINGID
and a.APPLICATIONID =b.APPLICATIONID
and b.APPLIEDINSTITUTECODE IN ('JIIT')
AND AIEEERANK IN ('149661','231446')
GROUP BY A.COUNSELLINGID,  A.AIEEERANK
ORDER BY TO_NUMBER(AieeeRANK)

update  C#CATEGORYAPPLIEDFOR A set A.CATEGORY = (SELECT B.CATEGORYCODE FROM C#APPLICATIONMASTER B where B.AIEEERANK=A.RANK) 
WHERE A.RANK IN ('149661','231446')

INSERT INTO C#STUDENTMASTER ( INSTITUTECODE, COUNSELLINGID, PROGRAMTYPE, RANK, ROLLNO, NAME, SEX,
DATEOFBIRTH, FATHERSHUSBANDNAME, LOCALGUARDIAN, SUBJECTCODE, LOCATIONCODE, DEACTIVE, STUDENTID,
FEESPAID, QUOTA, APPLICATIONNO, APPLICATIONSLNO, HOSTELPERMISSION, APPFEEUNPAID, APPLICATIONID,
REFNO, FLAG, APPLIEDCATEGORY ) VALUES ( 
'JPWG', '2014', 'UG', '99338', '28302017', 'ARSHPREET SINGH', 'M',  TO_Date( '11/21/1995 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 'JAGJIT SINGH', NULL, NULL, NULL, 'N', NULL, 'N', 'GENERAL', 'B60836', NULL, NULL
, NULL, 'JIACOL00807', NULL, NULL, 'GEN'); 
COMMIT;

UPDATE C#STUDENTINSTITUTECHOICE
SET RANK='99338' WHERE RANK = '120906'

UPDATE C#STUDENTPROGRAMCHOICE
SET RANK='99338' WHERE RANK = '120906'
 
UPDATE C#CATEGORYAPPLIEDFOR
SET RANK='99338' WHERE RANK = '120906'

DELETE C#STUDENTMASTER WHERE RANK = '120906'


INSERT INTO C#STUDENTMASTER ( INSTITUTECODE, COUNSELLINGID, PROGRAMTYPE, RANK, ROLLNO, NAME, SEX,
DATEOFBIRTH, FATHERSHUSBANDNAME, LOCALGUARDIAN, SUBJECTCODE, LOCATIONCODE, DEACTIVE, STUDENTID,
FEESPAID, QUOTA, APPLICATIONNO, APPLICATIONSLNO, HOSTELPERMISSION, APPFEEUNPAID, APPLICATIONID,
REFNO, FLAG, APPLIEDCATEGORY ) VALUES ( 
'JPWG', '2014', 'UG', '76089', '28302216', 'DEEKSHA NARANG', 'F',  TO_Date( '09/21/1995 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 'RAM CHANDER NARANG', NULL, NULL, NULL, 'N', NULL, 'N', 'GENERAL', '17223', NULL
, NULL, NULL, 'JIACOM09140', '001653', NULL, 'GEN'); 
COMMIT;


UPDATE C#STUDENTINSTITUTECHOICE
SET RANK='76089' WHERE RANK = '88965'

UPDATE C#STUDENTPROGRAMCHOICE
SET RANK='76089' WHERE RANK = '88965'
 
UPDATE C#CATEGORYAPPLIEDFOR
SET RANK='76089' WHERE RANK = '88965'

DELETE C#STUDENTMASTER WHERE RANK = '88965'

COMMIT;


