1: PACKAGE BODY JTY_TRANS_USG_PGM_SQL_PKG as
2: /* $Header: jtftupsb.pls 120.2 2005/11/21 13:34:02 achanda noship $ */
3: -- Start of Comments
4: -- ---------------------------------------------------
5: -- PACKAGE NAME: JTY_TRANS_USG_PGM_SQL_PKG
1: PACKAGE BODY JTY_TRANS_USG_PGM_SQL_PKG as
2: /* $Header: jtftupsb.pls 120.2 2005/11/21 13:34:02 achanda noship $ */
3: -- Start of Comments
4: -- ---------------------------------------------------
5: -- PACKAGE NAME: JTY_TRANS_USG_PGM_SQL_PKG
6: -- ---------------------------------------------------
7: -- PURPOSE
8: -- This package is used to create the transaction type SQLs
9: -- and the corresponding TRANS tables.
80: ORDER BY column_id;
81:
82: CURSOR CUR_REAL_TIME(cl_trans_usg_pgm_sql_id IN NUMBER) IS
83: SELECT REAL_TIME_SQL
84: FROM JTY_TRANS_USG_PGM_SQL
85: WHERE TRANS_USG_PGM_SQL_ID = cl_trans_usg_pgm_sql_id
86: FOR UPDATE OF REAL_TIME_SQL NOWAIT;
87:
88: CURSOR CUR_REAL_TIME_INSERT(cl_trans_usg_pgm_sql_id IN NUMBER) IS
86: FOR UPDATE OF REAL_TIME_SQL NOWAIT;
87:
88: CURSOR CUR_REAL_TIME_INSERT(cl_trans_usg_pgm_sql_id IN NUMBER) IS
89: SELECT REAL_TIME_INSERT
90: FROM JTY_TRANS_USG_PGM_SQL
91: WHERE TRANS_USG_PGM_SQL_ID = cl_trans_usg_pgm_sql_id
92: FOR UPDATE OF REAL_TIME_INSERT NOWAIT;
93:
94: CURSOR CUR_BATCH_TOTAL(cl_trans_usg_pgm_sql_id IN NUMBER) IS
92: FOR UPDATE OF REAL_TIME_INSERT NOWAIT;
93:
94: CURSOR CUR_BATCH_TOTAL(cl_trans_usg_pgm_sql_id IN NUMBER) IS
95: SELECT BATCH_TOTAL_SQL
96: FROM JTY_TRANS_USG_PGM_SQL
97: WHERE TRANS_USG_PGM_SQL_ID = cl_trans_usg_pgm_sql_id
98: FOR UPDATE OF BATCH_TOTAL_SQL NOWAIT;
99:
100: CURSOR CUR_BATCH_INCR(cl_trans_usg_pgm_sql_id IN NUMBER) IS
98: FOR UPDATE OF BATCH_TOTAL_SQL NOWAIT;
99:
100: CURSOR CUR_BATCH_INCR(cl_trans_usg_pgm_sql_id IN NUMBER) IS
101: SELECT BATCH_INCR_SQL
102: FROM JTY_TRANS_USG_PGM_SQL
103: WHERE TRANS_USG_PGM_SQL_ID = cl_trans_usg_pgm_sql_id
104: FOR UPDATE OF BATCH_INCR_SQL NOWAIT;
105:
106: CURSOR CUR_BATCH_DEA(cl_trans_usg_pgm_sql_id IN NUMBER) IS
104: FOR UPDATE OF BATCH_INCR_SQL NOWAIT;
105:
106: CURSOR CUR_BATCH_DEA(cl_trans_usg_pgm_sql_id IN NUMBER) IS
107: SELECT BATCH_DEA_SQL
108: FROM JTY_TRANS_USG_PGM_SQL
109: WHERE TRANS_USG_PGM_SQL_ID = cl_trans_usg_pgm_sql_id
110: FOR UPDATE OF BATCH_DEA_SQL NOWAIT;
111:
112: CURSOR CUR_INCR_REASSIGN(cl_trans_usg_pgm_sql_id IN NUMBER) IS
110: FOR UPDATE OF BATCH_DEA_SQL NOWAIT;
111:
112: CURSOR CUR_INCR_REASSIGN(cl_trans_usg_pgm_sql_id IN NUMBER) IS
113: SELECT INCR_REASSIGN_SQL
114: FROM JTY_TRANS_USG_PGM_SQL
115: WHERE TRANS_USG_PGM_SQL_ID = cl_trans_usg_pgm_sql_id
116: FOR UPDATE OF INCR_REASSIGN_SQL NOWAIT;
117:
118: BEGIN
172: END IF;
173:
174: /* If this version needs to be enabled, then all other versions need to be disabled */
175: IF (l_enabled_flag = 'Y') THEN
176: UPDATE jty_trans_usg_pgm_sql
177: SET enabled_flag = 'N'
178: WHERE SOURCE_ID = p_source_id
179: AND TRANS_TYPE_ID = p_trans_type_id
180: AND PROGRAM_NAME = p_program_name;
181: END IF;
182:
183: /* If present, delete the old entries from jty_trnas_usg_pgm_sql */
184: BEGIN
185: DELETE FROM JTY_TRANS_USG_PGM_SQL
186: WHERE SOURCE_ID = p_source_id
187: AND TRANS_TYPE_ID = p_trans_type_id
188: AND PROGRAM_NAME = p_program_name
189: AND VERSION_NAME = p_version_name;
192: NULL;
193: END;
194:
195: /* Get the unique id from sequence */
196: SELECT JTY_TRANS_USG_PGM_SQL_S.nextval
197: INTO l_trans_usg_pgm_sql_id
198: FROM DUAL;
199:
200: /* Insert a record with all the SQLs as NULL */
197: INTO l_trans_usg_pgm_sql_id
198: FROM DUAL;
199:
200: /* Insert a record with all the SQLs as NULL */
201: INSERT INTO JTY_TRANS_USG_PGM_SQL (
202: TRANS_USG_PGM_SQL_ID
203: ,SOURCE_ID
204: ,TRANS_TYPE_ID
205: ,PROGRAM_NAME
577: errbuf := 'SQLCODE : ' || SQLCODE || ' : SQLERRM : ' || SQLERRM;
578: end if;
579: End Insert_Row;
580:
581: END JTY_TRANS_USG_PGM_SQL_PKG;