[Home] [Help]
PACKAGE BODY: APPS.HZ_PRIMARY_TRANSFORM_PKG
Source
1 PACKAGE BODY HZ_PRIMARY_TRANSFORM_PKG AS
2 /*$Header: ARHDQPTB.pls 120.4 2005/10/30 04:19:19 appldev noship $ */
3
4
5 PROCEDURE Insert_Row(
6 px_PRIMARY_TRANSFORM_ID IN OUT NOCOPY NUMBER,
7 p_PRIMARY_ATTRIBUTE_ID NUMBER,
8 p_FUNCTION_ID NUMBER,
9 p_ACTIVE_FLAG VARCHAR2,
10 p_CREATED_BY NUMBER,
11 p_CREATION_DATE DATE,
12 p_LAST_UPDATE_LOGIN NUMBER,
13 p_LAST_UPDATE_DATE DATE,
14 p_LAST_UPDATED_BY NUMBER,
15 p_OBJECT_VERSION_NUMBER NUMBER)
16 IS
17
18 CURSOR C2 IS SELECT HZ_PRIMARY_TRANS_s.nextval FROM sys.dual;
19 l_success VARCHAR2(1) := 'N';
20 BEGIN
21 WHILE l_success = 'N' LOOP
22 BEGIN
23 IF ( px_PRIMARY_TRANSFORM_ID IS NULL) OR (px_PRIMARY_TRANSFORM_ID = FND_API.G_MISS_NUM) THEN
24 OPEN C2;
25 FETCH C2 INTO px_PRIMARY_TRANSFORM_ID;
26 CLOSE C2;
27 END IF;
28 INSERT INTO HZ_PRIMARY_TRANS(
29 PRIMARY_TRANSFORM_ID,
30 PRIMARY_ATTRIBUTE_ID,
31 FUNCTION_ID,
32 ACTIVE_FLAG,
33 CREATED_BY,
34 CREATION_DATE,
35 LAST_UPDATE_LOGIN,
36 LAST_UPDATE_DATE,
37 LAST_UPDATED_BY,
38 OBJECT_VERSION_NUMBER
39 )
40 VALUES (
41 px_PRIMARY_TRANSFORM_ID,
42 decode(p_PRIMARY_ATTRIBUTE_ID, FND_API.G_MISS_NUM, NULL, p_PRIMARY_ATTRIBUTE_ID),
43 decode(p_FUNCTION_ID, FND_API.G_MISS_NUM, NULL, p_FUNCTION_ID),
44 decode(p_ACTIVE_FLAG, FND_API.G_MISS_CHAR, NULL, p_ACTIVE_FLAG),
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 1
51 );
52
53 l_success := 'Y';
54 EXCEPTION WHEN DUP_VAL_ON_INDEX THEN
55 IF INSTRB( SQLERRM, 'HZ_PRIMARY_TRANS_U1' ) <> 0 THEN
56 DECLARE
57 l_count NUMBER;
58 l_dummy VARCHAR2(1);
59 BEGIN
60 l_count := 1;
61 WHILE l_count > 0 LOOP
62 SELECT HZ_PRIMARY_TRANS_s.nextval
63 into px_PRIMARY_TRANSFORM_ID FROM sys.dual;
64 BEGIN
65 SELECT 'Y' INTO l_dummy
66 FROM HZ_PRIMARY_TRANS
67 WHERE PRIMARY_TRANSFORM_ID = px_PRIMARY_TRANSFORM_ID ;
68 l_count := 1;
69 EXCEPTION WHEN NO_DATA_FOUND THEN
70 l_count := 0;
71 END;
72 END LOOP;
73 END;
74 END IF;
75 END;
76 END LOOP;
77 End Insert_Row;
78
79 PROCEDURE Update_Row(
80 p_PRIMARY_TRANSFORM_ID NUMBER,
81 p_PRIMARY_ATTRIBUTE_ID NUMBER,
82 p_FUNCTION_ID NUMBER,
83 p_ACTIVE_FLAG VARCHAR2,
84 p_CREATED_BY NUMBER,
85 p_CREATION_DATE DATE,
86 p_LAST_UPDATE_LOGIN NUMBER,
87 p_LAST_UPDATE_DATE DATE,
88 p_LAST_UPDATED_BY NUMBER,
89 p_OBJECT_VERSION_NUMBER in out NOCOPY NUMBER
90 ) IS
91 l_object_version_number number;
92
93 BEGIN
94
95 l_object_version_number := NVL(p_object_version_number, 1) + 1;
96
97 UPDATE hz_primary_trans
98 SET
99 PRIMARY_ATTRIBUTE_ID= decode(p_PRIMARY_ATTRIBUTE_ID, FND_API.G_MISS_NUM, PRIMARY_ATTRIBUTE_ID,
100 p_PRIMARY_ATTRIBUTE_ID),
101 FUNCTION_ID = decode(p_FUNCTION_ID, FND_API.G_MISS_NUM, FUNCTION_ID , p_FUNCTION_ID),
102 ACTIVE_FLAG = decode(p_ACTIVE_FLAG, FND_API.G_MISS_CHAR, ACTIVE_FLAG , p_ACTIVE_FLAG),
103 -- Bug 3032780
104 /*
105 CREATED_BY = decode(p_created_by, FND_API.G_MISS_NUM, CREATED_BY , p_created_by),
106 CREATION_DATE = decode(p_CREATION_DATE, FND_API.G_MISS_DATE, CREATION_DATE ,p_CREATION_DATE),
107 */
108 LAST_UPDATE_LOGIN = decode(p_LAST_UPDATE_LOGIN,FND_API.G_MISS_NUM, LAST_UPDATE_LOGIN ,p_LAST_UPDATE_LOGIN),
109 LAST_UPDATE_DATE = decode(p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, LAST_UPDATE_DATE ,p_LAST_UPDATE_DATE),
110 LAST_UPDATED_BY = decode(p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, LAST_UPDATED_BY ,p_LAST_UPDATED_BY),
111 OBJECT_VERSION_NUMBER = decode(l_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, OBJECT_VERSION_NUMBER,l_object_version_number)
112 WHERE PRIMARY_TRANSFORM_ID = p_PRIMARY_TRANSFORM_ID;
113
114 p_OBJECT_VERSION_NUMBER := l_OBJECT_VERSION_NUMBER;
115
116 If (SQL%NOTFOUND) then
117 RAISE NO_DATA_FOUND;
118 End If;
119 END Update_Row;
120
121 PROCEDURE Delete_Row(p_PRIMARY_TRANSFORM_ID NUMBER) IS
122 BEGIN
123 DELETE FROM HZ_PRIMARY_TRANS
124 WHERE primary_transform_id = p_primary_transform_id;
125
126 If (SQL%NOTFOUND) then
127 RAISE NO_DATA_FOUND;
128 End If;
129 END Delete_Row;
130
131 PROCEDURE Lock_Row(
132 p_PRIMARY_TRANSFORM_ID IN OUT NOCOPY NUMBER,
133 p_OBJECT_VERSION_NUMBER IN NUMBER
134 ) IS
135 CURSOR C IS
136
137 SELECT OBJECT_VERSION_NUMBER
138 FROM hz_primary_trans
139 WHERE PRIMARY_TRANSFORM_ID = p_PRIMARY_TRANSFORM_ID
140 FOR UPDATE OF PRIMARY_TRANSFORM_ID NOWAIT;
141 Recinfo C%ROWTYPE;
142 BEGIN
143 OPEN C;
144 FETCH C INTO Recinfo;
145 If (C%NOTFOUND) then
146 CLOSE C;
147 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
148 APP_EXCEPTION.RAISE_EXCEPTION;
149 End If;
150 CLOSE C;
151
152 if (
153 ( Recinfo.OBJECT_VERSION_NUMBER IS NOT NULL AND p_OBJECT_VERSION_NUMBER IS NOT NULL
154 AND Recinfo.OBJECT_VERSION_NUMBER = p_OBJECT_VERSION_NUMBER )
155 OR ((Recinfo.OBJECT_VERSION_NUMBER IS NULL)AND (p_OBJECT_VERSION_NUMBER IS NULL ))
156 ) then
157 return;
158 else
159 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
160 APP_EXCEPTION.RAISE_EXCEPTION;
161 End If;
162 END Lock_Row;
163
164 END HZ_PRIMARY_TRANSFORM_PKG;