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