[Home] [Help]
PACKAGE BODY: APPS.HZ_MERGE_BATCH_PKG
Source
1 PACKAGE BODY HZ_MERGE_BATCH_PKG as
2 /* $Header: ARHMBTBB.pls 120.2 2005/06/16 21:12:21 jhuang noship $ */
3 -- Start of Comments
4 -- Package name : HZ_MERGE_BATCH_PKG
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10
11 G_PKG_NAME CONSTANT VARCHAR2(30):= 'HZ_MERGE_BATCH_PKG';
12 G_FILE_NAME CONSTANT VARCHAR2(12) := 'arhtbtbb.pls';
13
14 PROCEDURE Insert_Row(
15 px_BATCH_ID IN OUT NOCOPY NUMBER,
16 p_RULE_SET_NAME VARCHAR2,
17 p_BATCH_NAME VARCHAR2,
18 p_REQUEST_ID NUMBER,
19 p_BATCH_STATUS VARCHAR2,
20 p_BATCH_COMMIT VARCHAR2,
21 p_BATCH_DELETE VARCHAR2,
22 p_MERGE_REASON_CODE VARCHAR2,
23 p_CREATED_BY NUMBER,
24 p_CREATION_DATE DATE,
25 p_LAST_UPDATE_LOGIN NUMBER,
26 p_LAST_UPDATE_DATE DATE,
27 p_LAST_UPDATED_BY NUMBER)
28
29 IS
30 CURSOR C2 IS SELECT HZ_MERGE_BATCH_S.nextval FROM sys.dual;
31 BEGIN
32 If (px_BATCH_ID IS NULL) OR (px_BATCH_ID = FND_API.G_MISS_NUM) then
33 OPEN C2;
34 FETCH C2 INTO px_BATCH_ID;
35 CLOSE C2;
36 End If;
37 INSERT INTO HZ_MERGE_BATCH(
38 BATCH_ID,
39 RULE_SET_NAME,
40 BATCH_NAME,
41 REQUEST_ID,
42 BATCH_STATUS,
43 BATCH_COMMIT,
44 BATCH_DELETE,
45 MERGE_REASON_CODE,
46 CREATED_BY,
47 CREATION_DATE,
48 LAST_UPDATE_LOGIN,
49 LAST_UPDATE_DATE,
50 LAST_UPDATED_BY
51 ) VALUES (
52 px_BATCH_ID,
53 decode( p_RULE_SET_NAME, FND_API.G_MISS_CHAR, NULL, p_RULE_SET_NAME),
54 decode( p_BATCH_NAME, FND_API.G_MISS_CHAR, NULL, p_BATCH_NAME),
55 decode( p_REQUEST_ID, FND_API.G_MISS_NUM, NULL, p_REQUEST_ID),
56 decode( p_BATCH_STATUS, FND_API.G_MISS_CHAR, NULL, p_BATCH_STATUS),
57 decode( p_BATCH_COMMIT, FND_API.G_MISS_CHAR, NULL, p_BATCH_COMMIT),
58 decode( p_BATCH_DELETE, FND_API.G_MISS_CHAR, NULL, p_BATCH_DELETE),
59 decode( p_MERGE_REASON_CODE, FND_API.G_MISS_CHAR, NULL, p_MERGE_REASON_CODE),
60 decode( p_CREATED_BY, FND_API.G_MISS_NUM, NULL, p_CREATED_BY),
61 decode( p_CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_CREATION_DATE),
62 decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATE_LOGIN),
63 decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_LAST_UPDATE_DATE),
64 decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATED_BY));
65 End Insert_Row;
66
67 PROCEDURE Update_Row(
68 p_BATCH_ID NUMBER,
69 p_RULE_SET_NAME VARCHAR2,
70 p_BATCH_NAME VARCHAR2,
71 p_REQUEST_ID NUMBER,
72 p_BATCH_STATUS VARCHAR2,
73 p_BATCH_COMMIT VARCHAR2,
74 p_BATCH_DELETE VARCHAR2,
75 p_MERGE_REASON_CODE VARCHAR2,
76 p_CREATED_BY NUMBER,
77 p_CREATION_DATE DATE,
78 p_LAST_UPDATE_LOGIN NUMBER,
79 p_LAST_UPDATE_DATE DATE,
80 p_LAST_UPDATED_BY NUMBER)
81
82 IS
83 BEGIN
84 Update HZ_MERGE_BATCH
85 SET
86 RULE_SET_NAME = decode( p_RULE_SET_NAME, FND_API.G_MISS_CHAR, RULE_SET_NAME, p_RULE_SET_NAME),
87 BATCH_NAME = decode( p_BATCH_NAME, FND_API.G_MISS_CHAR, BATCH_NAME, p_BATCH_NAME),
88 REQUEST_ID = decode( p_REQUEST_ID, FND_API.G_MISS_NUM, REQUEST_ID, p_REQUEST_ID),
89 BATCH_STATUS = decode( p_BATCH_STATUS, FND_API.G_MISS_CHAR, BATCH_STATUS, p_BATCH_STATUS),
90 BATCH_COMMIT = decode( p_BATCH_COMMIT, FND_API.G_MISS_CHAR, BATCH_COMMIT, p_BATCH_COMMIT),
91 BATCH_DELETE = decode( p_BATCH_DELETE, FND_API.G_MISS_CHAR, BATCH_DELETE, p_BATCH_DELETE),
92 MERGE_REASON_CODE = decode( p_MERGE_REASON_CODE, FND_API.G_MISS_CHAR, MERGE_REASON_CODE, p_MERGE_REASON_CODE),
93 -- Bug 3032780
94 /*
95 CREATED_BY = decode( p_CREATED_BY, FND_API.G_MISS_NUM, CREATED_BY, p_CREATED_BY),
96 CREATION_DATE = decode( p_CREATION_DATE, FND_API.G_MISS_DATE, CREATION_DATE, p_CREATION_DATE),
97 */
98 LAST_UPDATE_LOGIN = decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, LAST_UPDATE_LOGIN, p_LAST_UPDATE_LOGIN),
99 LAST_UPDATE_DATE = decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, LAST_UPDATE_DATE, p_LAST_UPDATE_DATE),
100 LAST_UPDATED_BY = decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, LAST_UPDATED_BY, p_LAST_UPDATED_BY)
101 where BATCH_ID = p_BATCH_ID;
102
103 If (SQL%NOTFOUND) then
104 RAISE NO_DATA_FOUND;
105 End If;
106 END Update_Row;
107
108 PROCEDURE Delete_Row(
109 p_BATCH_ID NUMBER)
110 IS
111 BEGIN
112 DELETE FROM HZ_MERGE_BATCH
113 WHERE BATCH_ID = p_BATCH_ID;
114 If (SQL%NOTFOUND) then
115 RAISE NO_DATA_FOUND;
116 End If;
117 END Delete_Row;
118
119 PROCEDURE Lock_Row(
120 p_BATCH_ID NUMBER,
121 p_RULE_SET_NAME VARCHAR2,
122 p_BATCH_NAME VARCHAR2,
123 p_REQUEST_ID NUMBER,
124 p_BATCH_STATUS VARCHAR2,
125 p_BATCH_COMMIT VARCHAR2,
126 p_BATCH_DELETE VARCHAR2,
127 p_MERGE_REASON_CODE VARCHAR2,
128 p_CREATED_BY NUMBER,
129 p_CREATION_DATE DATE,
130 p_LAST_UPDATE_LOGIN NUMBER,
131 p_LAST_UPDATE_DATE DATE,
132 p_LAST_UPDATED_BY NUMBER)
133
134 IS
135 CURSOR C IS
136 SELECT *
137 FROM HZ_MERGE_BATCH
138 WHERE BATCH_ID = p_BATCH_ID
139 FOR UPDATE of BATCH_ID NOWAIT;
140 Recinfo C%ROWTYPE;
141 BEGIN
142 OPEN C;
143 FETCH C INTO Recinfo;
144 If (C%NOTFOUND) then
145 CLOSE C;
146 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
147 APP_EXCEPTION.RAISE_EXCEPTION;
148 End If;
149 CLOSE C;
150 if (
151 ( Recinfo.BATCH_ID = p_BATCH_ID)
152 AND ( ( Recinfo.RULE_SET_NAME = p_RULE_SET_NAME)
153 OR ( ( Recinfo.RULE_SET_NAME IS NULL )
154 AND ( p_RULE_SET_NAME IS NULL )))
155 AND ( ( Recinfo.BATCH_NAME = p_BATCH_NAME)
156 OR ( ( Recinfo.BATCH_NAME IS NULL )
157 AND ( p_BATCH_NAME IS NULL )))
158 AND ( ( Recinfo.REQUEST_ID = p_REQUEST_ID)
159 OR ( ( Recinfo.REQUEST_ID IS NULL )
160 AND ( p_REQUEST_ID IS NULL )))
161 AND ( ( Recinfo.BATCH_STATUS = p_BATCH_STATUS)
162 OR ( ( Recinfo.BATCH_STATUS IS NULL )
163 AND ( p_BATCH_STATUS IS NULL )))
164 AND ( ( Recinfo.BATCH_COMMIT = p_BATCH_COMMIT)
165 OR ( ( Recinfo.BATCH_COMMIT IS NULL )
166 AND ( p_BATCH_COMMIT IS NULL )))
167 AND ( ( Recinfo.BATCH_DELETE = p_BATCH_DELETE)
168 OR ( ( Recinfo.BATCH_DELETE IS NULL )
169 AND ( p_BATCH_DELETE IS NULL )))
170 AND ( ( Recinfo.MERGE_REASON_CODE = p_MERGE_REASON_CODE)
171 OR ( ( Recinfo.MERGE_REASON_CODE IS NULL )
172 AND ( p_MERGE_REASON_CODE IS NULL )))
173 AND ( ( Recinfo.CREATED_BY = p_CREATED_BY)
174 OR ( ( Recinfo.CREATED_BY IS NULL )
175 AND ( p_CREATED_BY IS NULL )))
176 AND ( ( Recinfo.CREATION_DATE = p_CREATION_DATE)
177 OR ( ( Recinfo.CREATION_DATE IS NULL )
178 AND ( p_CREATION_DATE IS NULL )))
179 AND ( ( Recinfo.LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN)
180 OR ( ( Recinfo.LAST_UPDATE_LOGIN IS NULL )
181 AND ( p_LAST_UPDATE_LOGIN IS NULL )))
182 AND ( ( Recinfo.LAST_UPDATE_DATE = p_LAST_UPDATE_DATE)
183 OR ( ( Recinfo.LAST_UPDATE_DATE IS NULL )
184 AND ( p_LAST_UPDATE_DATE IS NULL )))
185 AND ( ( Recinfo.LAST_UPDATED_BY = p_LAST_UPDATED_BY)
186 OR ( ( Recinfo.LAST_UPDATED_BY IS NULL )
187 AND ( p_LAST_UPDATED_BY IS NULL )))
188 ) then
189 return;
190 else
191 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
192 APP_EXCEPTION.RAISE_EXCEPTION;
193 End If;
194 END Lock_Row;
195
196 End HZ_MERGE_BATCH_PKG;