[Home] [Help]
PACKAGE BODY: APPS.JTY_TRANS_USG_PGM_DETAILS_PKG
Source
1 PACKAGE BODY JTY_TRANS_USG_PGM_DETAILS_PKG as
2 /* $Header: jtftupdb.pls 120.1 2005/11/18 11:37:23 achanda noship $ */
3
4 PROCEDURE Insert_Row(
5 X_ROW_ID IN OUT NOCOPY VARCHAR2,
6 X_TRANS_USG_PGM_DETAILS_ID IN OUT NOCOPY NUMBER,
7 P_SOURCE_ID IN NUMBER,
8 P_TRANS_TYPE_ID IN NUMBER,
9 P_PROGRAM_NAME IN VARCHAR2,
10 P_LAST_UPDATE_DATE IN DATE,
11 P_LAST_UPDATED_BY IN NUMBER,
12 P_CREATION_DATE IN DATE,
13 P_CREATED_BY IN NUMBER,
14 P_LAST_UPDATE_LOGIN IN NUMBER,
15 P_PARAM_PASSING_MECHANISM IN VARCHAR2,
16 P_REAL_TIME_ENABLE_FLAG IN VARCHAR2,
17 P_BATCH_ENABLE_FLAG IN VARCHAR2,
18 P_MULTI_LEVEL_WINNING_FLAG IN VARCHAR2,
19 P_REAL_TIME_TRANS_TABLE IN VARCHAR2,
20 P_BATCH_TRANS_TABLE IN VARCHAR2,
21 P_BATCH_NM_TRANS_TABLE IN VARCHAR2,
22 P_BATCH_DEA_TRANS_TABLE IN VARCHAR2,
23 P_BATCH_MATCH_TABLE IN VARCHAR2,
24 P_BATCH_UNIQUE_MATCH_TABLE IN VARCHAR2,
25 P_BATCH_WINNER_TABLE IN VARCHAR2,
26 P_BATCH_UNIQUE_WINNER_TABLE IN VARCHAR2,
27 P_BATCH_L1_WINNER_TABLE IN VARCHAR2,
28 P_BATCH_L2_WINNER_TABLE IN VARCHAR2,
29 P_BATCH_L3_WINNER_TABLE IN VARCHAR2,
30 P_BATCH_L4_WINNER_TABLE IN VARCHAR2,
31 P_BATCH_L5_WINNER_TABLE IN VARCHAR2,
32 P_BATCH_WT_WINNER_TABLE IN VARCHAR2,
33 P_BATCH_MP_WINNER_TABLE IN VARCHAR2,
34 P_BATCH_DMC_WINNER_TABLE IN VARCHAR2,
35 P_INDEX_EXTN IN VARCHAR2) IS
36
37 CURSOR C1 IS
38 SELECT rowid
39 FROM JTY_TRANS_USG_PGM_DETAILS
40 WHERE TRANS_USG_PGM_DETAILS_ID = X_TRANS_USG_PGM_DETAILS_ID;
41
42 CURSOR C2 IS SELECT jty_trans_usg_pgm_details_s.nextval FROM sys.dual;
43
44 BEGIN
45 If (X_TRANS_USG_PGM_DETAILS_ID IS NULL) then
46 OPEN C2;
47 FETCH C2 INTO X_TRANS_USG_PGM_DETAILS_ID;
48 CLOSE C2;
49 End If;
50
51 INSERT INTO JTY_TRANS_USG_PGM_DETAILS(
52 TRANS_USG_PGM_DETAILS_ID,
53 SOURCE_ID,
54 TRANS_TYPE_ID,
55 PROGRAM_NAME,
56 LAST_UPDATE_DATE,
57 LAST_UPDATED_BY,
58 CREATION_DATE,
59 CREATED_BY,
60 LAST_UPDATE_LOGIN,
61 PARAM_PASSING_MECHANISM,
62 REAL_TIME_ENABLE_FLAG,
63 BATCH_ENABLE_FLAG,
64 MULTI_LEVEL_WINNING_FLAG,
65 REAL_TIME_TRANS_TABLE_NAME,
66 BATCH_TRANS_TABLE_NAME,
67 BATCH_NM_TRANS_TABLE_NAME,
68 BATCH_DEA_TRANS_TABLE_NAME,
69 BATCH_MATCH_TABLE_NAME,
70 BATCH_UNIQUE_MATCH_TABLE_NAME,
71 BATCH_WINNER_TABLE_NAME,
72 BATCH_UNIQUE_WINNER_TABLE_NAME,
73 BATCH_L1_WINNER_TABLE_NAME,
74 BATCH_L2_WINNER_TABLE_NAME,
75 BATCH_L3_WINNER_TABLE_NAME,
76 BATCH_L4_WINNER_TABLE_NAME,
77 BATCH_L5_WINNER_TABLE_NAME,
78 BATCH_WT_WINNER_TABLE_NAME,
79 BATCH_MP_WINNER_TABLE_NAME,
80 BATCH_DMC_WINNER_TABLE_NAME,
81 INDEX_EXTN)
82 VALUES (X_TRANS_USG_PGM_DETAILS_ID,
83 decode( P_SOURCE_ID, FND_API.G_MISS_NUM, NULL, P_SOURCE_ID),
84 decode( P_TRANS_TYPE_ID, FND_API.G_MISS_NUM, NULL, P_TRANS_TYPE_ID),
85 decode( P_PROGRAM_NAME, FND_API.G_MISS_CHAR, NULL,P_PROGRAM_NAME),
86 decode( P_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),P_LAST_UPDATE_DATE),
87 decode( P_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL,P_LAST_UPDATED_BY),
88 decode( P_CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),P_CREATION_DATE),
89 decode( P_CREATED_BY, FND_API.G_MISS_NUM, NULL,P_CREATED_BY),
90 decode( P_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL,P_LAST_UPDATE_LOGIN),
91 decode( P_PARAM_PASSING_MECHANISM, FND_API.G_MISS_CHAR, NULL,P_PARAM_PASSING_MECHANISM),
92 decode( P_REAL_TIME_ENABLE_FLAG, FND_API.G_MISS_CHAR, NULL,P_REAL_TIME_ENABLE_FLAG),
93 decode( P_BATCH_ENABLE_FLAG, FND_API.G_MISS_CHAR, NULL,P_BATCH_ENABLE_FLAG),
94 decode( P_MULTI_LEVEL_WINNING_FLAG, FND_API.G_MISS_CHAR, NULL,P_MULTI_LEVEL_WINNING_FLAG),
95 decode( P_REAL_TIME_TRANS_TABLE, FND_API.G_MISS_CHAR, NULL,P_REAL_TIME_TRANS_TABLE),
96 decode( P_BATCH_TRANS_TABLE, FND_API.G_MISS_CHAR, NULL,P_BATCH_TRANS_TABLE),
97 decode( P_BATCH_NM_TRANS_TABLE, FND_API.G_MISS_CHAR, NULL,P_BATCH_NM_TRANS_TABLE),
98 decode( P_BATCH_DEA_TRANS_TABLE, FND_API.G_MISS_CHAR, NULL,P_BATCH_DEA_TRANS_TABLE),
99 decode( P_BATCH_MATCH_TABLE, FND_API.G_MISS_CHAR, NULL,P_BATCH_MATCH_TABLE),
100 decode( P_BATCH_UNIQUE_MATCH_TABLE, FND_API.G_MISS_CHAR, NULL,P_BATCH_UNIQUE_MATCH_TABLE),
101 decode( P_BATCH_WINNER_TABLE, FND_API.G_MISS_CHAR, NULL,P_BATCH_WINNER_TABLE),
102 decode( P_BATCH_UNIQUE_WINNER_TABLE, FND_API.G_MISS_CHAR, NULL,P_BATCH_UNIQUE_WINNER_TABLE),
103 decode( P_BATCH_L1_WINNER_TABLE, FND_API.G_MISS_CHAR, NULL,P_BATCH_L1_WINNER_TABLE),
104 decode( P_BATCH_L2_WINNER_TABLE, FND_API.G_MISS_CHAR, NULL,P_BATCH_L2_WINNER_TABLE),
105 decode( P_BATCH_L3_WINNER_TABLE, FND_API.G_MISS_CHAR, NULL,P_BATCH_L3_WINNER_TABLE),
106 decode( P_BATCH_L4_WINNER_TABLE, FND_API.G_MISS_CHAR, NULL,P_BATCH_L4_WINNER_TABLE),
107 decode( P_BATCH_L5_WINNER_TABLE, FND_API.G_MISS_CHAR, NULL,P_BATCH_L5_WINNER_TABLE),
108 decode( P_BATCH_WT_WINNER_TABLE, FND_API.G_MISS_CHAR, NULL,P_BATCH_WT_WINNER_TABLE),
109 decode( P_BATCH_MP_WINNER_TABLE, FND_API.G_MISS_CHAR, NULL,P_BATCH_MP_WINNER_TABLE),
110 decode( P_BATCH_DMC_WINNER_TABLE, FND_API.G_MISS_CHAR, NULL,P_BATCH_DMC_WINNER_TABLE),
111 decode( P_INDEX_EXTN, FND_API.G_MISS_CHAR, NULL,P_INDEX_EXTN));
112
113 OPEN C1;
114 FETCH C1 INTO X_Row_id;
115 If (C1%NOTFOUND) then
116 CLOSE C1;
117 RAISE NO_DATA_FOUND;
118 End If;
119 End Insert_Row;
120
121 PROCEDURE Update_Row(
122 P_ROW_ID IN VARCHAR2,
123 P_TRANS_USG_PGM_DETAILS_ID IN NUMBER,
124 P_SOURCE_ID IN NUMBER,
125 P_TRANS_TYPE_ID IN NUMBER,
126 P_PROGRAM_NAME IN VARCHAR2,
127 P_LAST_UPDATE_DATE IN DATE,
128 P_LAST_UPDATED_BY IN NUMBER,
129 P_CREATION_DATE IN DATE,
130 P_CREATED_BY IN NUMBER,
131 P_LAST_UPDATE_LOGIN IN NUMBER,
132 P_PARAM_PASSING_MECHANISM IN VARCHAR2,
133 P_REAL_TIME_ENABLE_FLAG IN VARCHAR2,
134 P_BATCH_ENABLE_FLAG IN VARCHAR2,
135 P_MULTI_LEVEL_WINNING_FLAG IN VARCHAR2,
136 P_REAL_TIME_TRANS_TABLE IN VARCHAR2,
137 P_BATCH_TRANS_TABLE IN VARCHAR2,
138 P_BATCH_NM_TRANS_TABLE IN VARCHAR2,
139 P_BATCH_DEA_TRANS_TABLE IN VARCHAR2,
140 P_BATCH_MATCH_TABLE IN VARCHAR2,
141 P_BATCH_UNIQUE_MATCH_TABLE IN VARCHAR2,
142 P_BATCH_WINNER_TABLE IN VARCHAR2,
143 P_BATCH_UNIQUE_WINNER_TABLE IN VARCHAR2,
144 P_BATCH_L1_WINNER_TABLE IN VARCHAR2,
145 P_BATCH_L2_WINNER_TABLE IN VARCHAR2,
146 P_BATCH_L3_WINNER_TABLE IN VARCHAR2,
147 P_BATCH_L4_WINNER_TABLE IN VARCHAR2,
148 P_BATCH_L5_WINNER_TABLE IN VARCHAR2,
149 P_BATCH_WT_WINNER_TABLE IN VARCHAR2,
150 P_BATCH_MP_WINNER_TABLE IN VARCHAR2,
151 P_BATCH_DMC_WINNER_TABLE IN VARCHAR2,
152 P_INDEX_EXTN IN VARCHAR2) IS
153 BEGIN
154 Update JTY_TRANS_USG_PGM_DETAILS
155 SET
156 SOURCE_ID = decode(P_SOURCE_ID, FND_API.G_MISS_NUM, SOURCE_ID, P_SOURCE_ID),
157 TRANS_TYPE_ID = decode(P_TRANS_TYPE_ID, FND_API.G_MISS_NUM, TRANS_TYPE_ID, P_TRANS_TYPE_ID),
158 PROGRAM_NAME = decode(P_PROGRAM_NAME, FND_API.G_MISS_CHAR, PROGRAM_NAME, P_PROGRAM_NAME),
159 LAST_UPDATE_DATE = decode(P_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, LAST_UPDATE_DATE, P_LAST_UPDATE_DATE),
160 LAST_UPDATED_BY = decode(P_LAST_UPDATED_BY, FND_API.G_MISS_NUM, LAST_UPDATED_BY, P_LAST_UPDATED_BY),
161 CREATION_DATE = decode(P_CREATION_DATE, FND_API.G_MISS_DATE, CREATION_DATE, P_CREATION_DATE),
162 CREATED_BY = decode(P_CREATED_BY, FND_API.G_MISS_NUM, CREATED_BY, P_CREATED_BY),
163 LAST_UPDATE_LOGIN = decode(P_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, LAST_UPDATE_LOGIN, P_LAST_UPDATE_LOGIN),
164 PARAM_PASSING_MECHANISM = decode(P_PARAM_PASSING_MECHANISM,FND_API.G_MISS_CHAR,
165 PARAM_PASSING_MECHANISM,P_PARAM_PASSING_MECHANISM),
166 REAL_TIME_ENABLE_FLAG = decode(P_REAL_TIME_ENABLE_FLAG, FND_API.G_MISS_CHAR, REAL_TIME_ENABLE_FLAG, P_REAL_TIME_ENABLE_FLAG),
167 BATCH_ENABLE_FLAG = decode(P_BATCH_ENABLE_FLAG, FND_API.G_MISS_CHAR, BATCH_ENABLE_FLAG, P_BATCH_ENABLE_FLAG),
168 MULTI_LEVEL_WINNING_FLAG = decode(P_MULTI_LEVEL_WINNING_FLAG,FND_API.G_MISS_CHAR,
169 MULTI_LEVEL_WINNING_FLAG,P_MULTI_LEVEL_WINNING_FLAG),
170 REAL_TIME_TRANS_TABLE_NAME = decode(P_REAL_TIME_TRANS_TABLE, FND_API.G_MISS_CHAR,
171 REAL_TIME_TRANS_TABLE_NAME, P_REAL_TIME_TRANS_TABLE),
172 BATCH_TRANS_TABLE_NAME = decode(P_BATCH_TRANS_TABLE, FND_API.G_MISS_CHAR, BATCH_TRANS_TABLE_NAME, P_BATCH_TRANS_TABLE),
173 BATCH_NM_TRANS_TABLE_NAME = decode(P_BATCH_NM_TRANS_TABLE, FND_API.G_MISS_CHAR,
174 BATCH_NM_TRANS_TABLE_NAME, P_BATCH_NM_TRANS_TABLE),
175 BATCH_DEA_TRANS_TABLE_NAME = decode(P_BATCH_DEA_TRANS_TABLE, FND_API.G_MISS_CHAR,
176 BATCH_DEA_TRANS_TABLE_NAME, P_BATCH_DEA_TRANS_TABLE),
177 BATCH_MATCH_TABLE_NAME = decode(P_BATCH_MATCH_TABLE, FND_API.G_MISS_CHAR, BATCH_MATCH_TABLE_NAME, P_BATCH_MATCH_TABLE),
178 BATCH_UNIQUE_MATCH_TABLE_NAME = decode(P_BATCH_UNIQUE_MATCH_TABLE,FND_API.G_MISS_CHAR,
179 BATCH_UNIQUE_MATCH_TABLE_NAME,P_BATCH_UNIQUE_MATCH_TABLE),
180 BATCH_WINNER_TABLE_NAME = decode(P_BATCH_WINNER_TABLE, FND_API.G_MISS_CHAR, BATCH_WINNER_TABLE_NAME, P_BATCH_WINNER_TABLE),
181 BATCH_UNIQUE_WINNER_TABLE_NAME = decode(P_BATCH_UNIQUE_WINNER_TABLE,FND_API.G_MISS_CHAR,
182 BATCH_UNIQUE_WINNER_TABLE_NAME,P_BATCH_UNIQUE_WINNER_TABLE),
183 BATCH_L1_WINNER_TABLE_NAME = decode(P_BATCH_L1_WINNER_TABLE, FND_API.G_MISS_CHAR,
184 BATCH_L1_WINNER_TABLE_NAME, P_BATCH_L1_WINNER_TABLE),
185 BATCH_L2_WINNER_TABLE_NAME = decode(P_BATCH_L2_WINNER_TABLE, FND_API.G_MISS_CHAR,
186 BATCH_L2_WINNER_TABLE_NAME, P_BATCH_L2_WINNER_TABLE),
187 BATCH_L3_WINNER_TABLE_NAME = decode(P_BATCH_L3_WINNER_TABLE, FND_API.G_MISS_CHAR,
188 BATCH_L3_WINNER_TABLE_NAME, P_BATCH_L3_WINNER_TABLE),
189 BATCH_L4_WINNER_TABLE_NAME = decode(P_BATCH_L4_WINNER_TABLE, FND_API.G_MISS_CHAR,
190 BATCH_L4_WINNER_TABLE_NAME, P_BATCH_L4_WINNER_TABLE),
191 BATCH_L5_WINNER_TABLE_NAME = decode(P_BATCH_L5_WINNER_TABLE, FND_API.G_MISS_CHAR,
192 BATCH_L5_WINNER_TABLE_NAME, P_BATCH_L5_WINNER_TABLE),
193 BATCH_WT_WINNER_TABLE_NAME = decode(P_BATCH_WT_WINNER_TABLE, FND_API.G_MISS_CHAR,
194 BATCH_WT_WINNER_TABLE_NAME, P_BATCH_WT_WINNER_TABLE),
195 BATCH_MP_WINNER_TABLE_NAME = decode(P_BATCH_MP_WINNER_TABLE, FND_API.G_MISS_CHAR,
196 BATCH_MP_WINNER_TABLE_NAME, P_BATCH_MP_WINNER_TABLE),
197 BATCH_DMC_WINNER_TABLE_NAME = decode(P_BATCH_DMC_WINNER_TABLE, FND_API.G_MISS_CHAR,
198 BATCH_DMC_WINNER_TABLE_NAME, P_BATCH_DMC_WINNER_TABLE),
199 INDEX_EXTN = decode(P_INDEX_EXTN, FND_API.G_MISS_CHAR,
200 INDEX_EXTN, P_INDEX_EXTN)
201 where TRANS_USG_PGM_DETAILS_ID = P_TRANS_USG_PGM_DETAILS_ID;
202
203 If (SQL%NOTFOUND) then
204 RAISE NO_DATA_FOUND;
205 End If;
206 END Update_Row;
207
208 PROCEDURE Delete_Row(P_TRANS_USG_PGM_DETAILS_ID IN NUMBER) IS
209 BEGIN
210 DELETE FROM JTY_TRANS_USG_PGM_DETAILS
211 WHERE TRANS_USG_PGM_DETAILS_ID = P_TRANS_USG_PGM_DETAILS_ID;
212 If (SQL%NOTFOUND) then
213 RAISE NO_DATA_FOUND;
214 End If;
215 END Delete_Row;
216
217 END JTY_TRANS_USG_PGM_DETAILS_PKG;