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