[Home] [Help]
PACKAGE BODY: APPS.AMS_ITEM_OWNERS_PKG
Source
1 PACKAGE BODY AMS_ITEM_OWNERS_PKG as
2 /* $Header: amstinvb.pls 115.8 2002/11/11 22:05:09 abhola ship $ */
3 -- Start of Comments
4 -- Package name : AMS_ITEM_OWNERS_PKG
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10
11 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AMS_ITEM_OWNERS_PKG';
12 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amstinvb.pls';
13
14 PROCEDURE Insert_Row(
15 px_ITEM_OWNER_ID IN OUT NOCOPY NUMBER,
16 px_OBJECT_VERSION_NUMBER IN OUT NOCOPY NUMBER,
17 p_INVENTORY_ITEM_ID NUMBER,
18 p_ORGANIZATION_ID NUMBER,
19 p_ITEM_NUMBER VARCHAR2,
20 p_OWNER_ID NUMBER,
21 p_STATUS_CODE VARCHAR2,
22 p_EFFECTIVE_DATE DATE,
23 p_IS_MASTER_ITEM VARCHAR2,
24 p_ITEM_SETUP_TYPE VARCHAR2,
25 p_custom_setup_id NUMBER)
26
27 IS
28 X_ROWID VARCHAR2(30);
29
30 CURSOR C IS SELECT rowid FROM AMS_ITEM_ATTRIBUTES
31 WHERE ITEM_OWNER_ID = px_ITEM_OWNER_ID;
32 CURSOR C2 IS SELECT AMS_ITEM_ATTRIBUTES_S.nextval FROM sys.dual;
33
34 BEGIN
35
36 IF (px_ITEM_OWNER_ID IS NULL) THEN
37 OPEN C2;
38 FETCH C2 INTO px_ITEM_OWNER_ID;
39 CLOSE C2;
40 END IF;
41
42 IF (px_OBJECT_VERSION_NUMBER IS NULL OR
43 px_OBJECT_VERSION_NUMBER = FND_API.G_MISS_NUM) THEN
44 px_OBJECT_VERSION_NUMBER := 1;
45 END IF;
46
47 INSERT INTO AMS_ITEM_ATTRIBUTES(
48 ITEM_OWNER_ID,
49 OBJECT_VERSION_NUMBER,
50 INVENTORY_ITEM_ID,
51 ORGANIZATION_ID,
52 ITEM_NUMBER,
53 OWNER_ID,
54 STATUS_CODE,
55 EFFECTIVE_DATE,
56 IS_MASTER_ITEM,
57 ITEM_SETUP_TYPE,
58 last_update_date,
59 last_updated_by,
60 creation_date,
61 created_by,
62 last_update_login,
63 custom_setup_id
64 ) VALUES (
65 decode( px_ITEM_OWNER_ID, FND_API.G_MISS_NUM, NULL, px_ITEM_OWNER_ID),
66 decode( px_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, px_OBJECT_VERSION_NUMBER),
67 decode( p_INVENTORY_ITEM_ID, FND_API.G_MISS_NUM, NULL, p_INVENTORY_ITEM_ID),
68 decode( p_ORGANIZATION_ID, FND_API.G_MISS_NUM, NULL, p_ORGANIZATION_ID),
69 decode( p_ITEM_NUMBER, FND_API.G_MISS_CHAR, NULL, p_ITEM_NUMBER),
70 decode( p_OWNER_ID, FND_API.G_MISS_NUM, NULL, p_OWNER_ID),
71 decode( p_STATUS_CODE, FND_API.G_MISS_CHAR, NULL, p_STATUS_CODE),
72 decode( p_EFFECTIVE_DATE, FND_API.G_MISS_DATE, NULL, p_EFFECTIVE_DATE),
73 p_IS_MASTER_ITEM,
74 p_ITEM_SETUP_TYPE,
75 SYSDATE,
76 FND_GLOBAL.user_id,
77 SYSDATE,
78 FND_GLOBAL.user_id,
79 FND_GLOBAL.conc_login_id,
80 decode(p_custom_setup_id,FND_API.G_MISS_NUM,1200,NULL,1200,p_custom_setup_id));
81 OPEN C;
82 FETCH C INTO x_rowid;
83 If (C%NOTFOUND) then
84 CLOSE C;
85 RAISE NO_DATA_FOUND;
86 End If;
87 End Insert_Row;
88
89 PROCEDURE Update_Row(
90 p_ITEM_OWNER_ID NUMBER,
91 p_OBJECT_VERSION_NUMBER NUMBER,
92 p_INVENTORY_ITEM_ID NUMBER,
93 p_ORGANIZATION_ID NUMBER,
94 p_ITEM_NUMBER VARCHAR2,
95 p_OWNER_ID NUMBER,
96 p_STATUS_CODE VARCHAR2,
97 p_EFFECTIVE_DATE DATE,
98 p_IS_MASTER_ITEM VARCHAR2,
99 p_ITEM_SETUP_TYPE VARCHAR2)
100
101 IS
102 BEGIN
103 Update AMS_ITEM_ATTRIBUTES
104 SET
105 ITEM_OWNER_ID = decode( p_ITEM_OWNER_ID, FND_API.G_MISS_NUM, ITEM_OWNER_ID, p_ITEM_OWNER_ID),
106 OBJECT_VERSION_NUMBER = decode( p_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, OBJECT_VERSION_NUMBER, p_OBJECT_VERSION_NUMBER),
107 INVENTORY_ITEM_ID = decode( p_INVENTORY_ITEM_ID, FND_API.G_MISS_NUM, INVENTORY_ITEM_ID, p_INVENTORY_ITEM_ID),
108 ORGANIZATION_ID = decode( p_ORGANIZATION_ID, FND_API.G_MISS_NUM, ORGANIZATION_ID, p_ORGANIZATION_ID),
109 ITEM_NUMBER = decode( p_ITEM_NUMBER, FND_API.G_MISS_CHAR, ITEM_NUMBER, p_ITEM_NUMBER),
110 OWNER_ID = decode( p_OWNER_ID, FND_API.G_MISS_NUM, OWNER_ID, p_OWNER_ID),
111 STATUS_CODE = decode( p_STATUS_CODE, FND_API.G_MISS_CHAR, STATUS_CODE, p_STATUS_CODE),
112 EFFECTIVE_DATE = decode( p_EFFECTIVE_DATE, FND_API.G_MISS_DATE, EFFECTIVE_DATE, p_EFFECTIVE_DATE),
113 IS_MASTER_ITEM = p_IS_MASTER_ITEM,
114 ITEM_SETUP_TYPE = p_ITEM_SETUP_TYPE,
115 last_update_date = SYSDATE,
116 last_updated_by = FND_GLOBAL.user_id,
117 last_update_login = FND_GLOBAL.conc_login_id
118 where ITEM_OWNER_ID = p_ITEM_OWNER_ID;
119
120 If (SQL%NOTFOUND) then
121 RAISE NO_DATA_FOUND;
122 End If;
123 END Update_Row;
124
125 PROCEDURE Delete_Row(
126 p_ITEM_OWNER_ID NUMBER)
127 IS
128 BEGIN
129 DELETE FROM AMS_ITEM_ATTRIBUTES
130 WHERE ITEM_OWNER_ID = p_ITEM_OWNER_ID;
131 If (SQL%NOTFOUND) then
132 RAISE NO_DATA_FOUND;
133 End If;
134 END Delete_Row;
135
136 PROCEDURE Lock_Row(
137 p_ITEM_OWNER_ID NUMBER,
138 p_OBJECT_VERSION_NUMBER NUMBER,
139 p_INVENTORY_ITEM_ID NUMBER,
140 p_ORGANIZATION_ID NUMBER,
141 p_ITEM_NUMBER VARCHAR2,
142 p_OWNER_ID NUMBER,
143 p_STATUS_CODE VARCHAR2,
144 p_EFFECTIVE_DATE DATE,
145 p_IS_MASTER_ITEM VARCHAR2,
146 p_ITEM_SETUP_TYPE VARCHAR2)
147
148 IS
149 CURSOR C IS
150 SELECT *
151 FROM AMS_ITEM_ATTRIBUTES
152 WHERE ITEM_OWNER_ID = p_ITEM_OWNER_ID
153 FOR UPDATE of ITEM_OWNER_ID NOWAIT;
154 Recinfo C%ROWTYPE;
155 BEGIN
156 OPEN C;
157 FETCH C INTO Recinfo;
158 If (C%NOTFOUND) then
159 CLOSE C;
160 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
161 APP_EXCEPTION.RAISE_EXCEPTION;
162 End If;
163 CLOSE C;
164 if (
165 ( Recinfo.ITEM_OWNER_ID = p_ITEM_OWNER_ID)
166 AND ( ( Recinfo.OBJECT_VERSION_NUMBER = p_OBJECT_VERSION_NUMBER)
167 OR ( ( Recinfo.OBJECT_VERSION_NUMBER IS NULL )
168 AND ( p_OBJECT_VERSION_NUMBER IS NULL )))
169 AND ( ( Recinfo.INVENTORY_ITEM_ID = p_INVENTORY_ITEM_ID)
170 OR ( ( Recinfo.INVENTORY_ITEM_ID IS NULL )
171 AND ( p_INVENTORY_ITEM_ID IS NULL )))
172 AND ( ( Recinfo.ORGANIZATION_ID = p_ORGANIZATION_ID)
173 OR ( ( Recinfo.ORGANIZATION_ID IS NULL )
174 AND ( p_ORGANIZATION_ID IS NULL )))
175 AND ( ( Recinfo.ITEM_NUMBER = p_ITEM_NUMBER)
176 OR ( ( Recinfo.ITEM_NUMBER IS NULL )
177 AND ( p_ITEM_NUMBER IS NULL )))
178 AND ( ( Recinfo.OWNER_ID = p_OWNER_ID)
179 OR ( ( Recinfo.OWNER_ID IS NULL )
180 AND ( p_OWNER_ID IS NULL )))
181 AND ( ( Recinfo.STATUS_CODE = p_STATUS_CODE)
182 OR ( ( Recinfo.STATUS_CODE IS NULL )
183 AND ( p_STATUS_CODE IS NULL )))
184 AND ( ( Recinfo.EFFECTIVE_DATE = p_EFFECTIVE_DATE)
185 OR ( ( Recinfo.EFFECTIVE_DATE IS NULL )
186 AND ( p_EFFECTIVE_DATE IS NULL )))
187 ) then
188 return;
189 else
190 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
191 APP_EXCEPTION.RAISE_EXCEPTION;
192 End If;
193 END Lock_Row;
194
195 End AMS_ITEM_OWNERS_PKG;