iBatis XML Sample

SELECT GAMEPLAN_ID,GAMEPLAN_STATUS,CREATE_USR_BTLR_ID,
START_DT,END_DT,DESCRIPTION_ID FROM PCIMS_GAMEPLAN
WHERE GAMEPLAN_STATUS !='H'
order by start_dt desc




SELECT GAMEPLAN_ID,GAMEPLAN_STATUS,CREATE_USR_BTLR_ID,
START_DT,END_DT,DESCRIPTION_ID FROM PCIMS_GAMEPLAN
WHERE GAMEPLAN_STATUS = 'A'
order by start_dt desc





SELECT GP.GAMEPLAN_ID
FROM PCIMS_GAMEPLAN GP
WHERE
GP.START_DT BETWEEN
(
SELECT P.START_DT FROM PCIMS_PERIOD P
WHERE
P.YEAR = #trimesterYear:NUMERIC#
AND P.PERIOD_NUMBER = #startPeriod:NUMERIC#
)
AND
(
SELECT P.END_DT FROM PCIMS_PERIOD P
WHERE
P.YEAR = #trimesterYear:NUMERIC#
AND P.PERIOD_NUMBER = #endPeriod:NUMERIC#
)






select pga.file_id,pf.file_name ,pf.file_type,pga.gameplan_id from pcims_file pf
,pcims_gameplan_attachment pga where pf.file_id=pga.file_id
and pga.gameplan_id=#gamePlanID:int# and pf.file_type = 'G'





SELECT GP.GAMEPLAN_ID,GP.GAMEPLAN_STATUS, GP.START_DT,GP.END_DT,
GP.CREATE_USR_BTLR_ID,GP.DESCRIPTION_ID
FROM PCIMS_GAMEPLAN GP
WHERE
GP.GAMEPLAN_ID=#gamePlanID:int#





SELECT SEQ_IMS_GAMEPLAN.NEXTVAL AS ID FROM DUAL

INSERT INTO PCIMS_GAMEPLAN(
GAMEPLAN_ID,
GAMEPLAN_STATUS,
CREATE_USR_BTLR_ID,
CREATE_DT,
UPDATE_USR_BTLR_ID,
UPDATE_DT,
START_DT,
END_DT,
DESCRIPTION_ID
)VALUES(
#gamePlanID:NUMERIC#,
#gamePlanStatus:VARCHAR#,
LOWER(#createUsrBtlrId:VARCHAR#),
#createDate:DATE#,
LOWER(#updateUsrBtlrId:VARCHAR#),
sysdate,
#startDate:DATE#,
#endDate:DATE#,
#descriptionId:NUMERIC#
)



UPDATE PCIMS_GAMEPLAN
SET
GAMEPLAN_STATUS = #gamePlanStatus:VARCHAR#,
END_DT = #endDate:DATE#,
DESCRIPTION_ID = #descriptionId:NUMERIC#,
START_DT = #startDate:DATE#,
UPDATE_DT = sysdate,
UPDATE_USR_BTLR_ID = LOWER(#updateUsrBtlrId:VARCHAR#)
WHERE gamePlan_id = #gamePlanID:NUMERIC#



UPDATE PCIMS_GAMEPLAN
SET
DESCRIPTION_ID = #descriptionId:NUMERIC#,
UPDATE_DT = sysdate,
UPDATE_USR_BTLR_ID = LOWER(#updateUsrBtlrId:VARCHAR#)
WHERE gamePlan_id = #gamePlanID:NUMERIC#



UPDATE PCIMS_GAMEPLAN
SET GAMEPLAN_STATUS = 'A'
WHERE (GAMEPLAN_ID = #gamePlanID:NUMERIC#)


SELECT DECODE (COUNT(GAMEPLAN_ID),0,'N','Y')
FROM (
SELECT GP.GAMEPLAN_ID
FROM PCIMS_GAMEPLAN GP
WHERE
GP.START_DT BETWEEN
(
SELECT P.START_DT FROM PCIMS_PERIOD P
WHERE
P.YEAR = #trimesterYear:NUMERIC#
AND P.PERIOD_NUMBER = #startPeriod:NUMERIC#
)
AND
(
SELECT P.END_DT FROM PCIMS_PERIOD P
WHERE
P.YEAR = #trimesterYear:NUMERIC#
AND P.PERIOD_NUMBER = #endPeriod:NUMERIC#
)
AND GAMEPLAN_ID <> #gamePlanID:NUMERIC#
)


INSERT INTO PCIMS_GAMEPLAN_ATTACHMENT(
GAMEPLAN_ID,
FILE_ID
)VALUES(
#gamePlanID:NUMERIC#,
#fileId:NUMERIC#
)



DELETE FROM PCIMS_GAMEPLAN_ATTACHMENT
WHERE FILE_ID = #fileId:VARCHAR#



UPDATE PCIMS_GAMEPLAN
SET GAMEPLAN_STATUS = 'H'
WHERE TRUNC(ADD_MONTHS(START_DT,12)) < SYSDATE
AND GAMEPLAN_STATUS <> 'H'