1 PACKAGE BODY mtl_related_items_cust_ref_pkg AS
2 /* $Header: INVTRICB.pls 120.0 2011/03/08 20:13:26 mshirkol noship $ */
3
4 PROCEDURE INSERT_ROW (
5 P_PLN_INFO_ID IN NUMBER
6 ,P_INVENTORY_ITEM_ID IN NUMBER
7 ,P_ORGANIZATION_ID IN NUMBER
8 ,P_RELATED_ITEM_ID IN NUMBER
9 ,P_RELATIONSHIP_TYPE_ID IN NUMBER
10 ,P_CUSTOMER_ID IN NUMBER
11 ,P_SITE_USE_ID IN NUMBER
12 ,P_START_DATE IN DATE
13 ,P_END_DATE IN DATE
14 ,P_LAST_UPDATE_DATE IN DATE
15 ,P_LAST_UPDATED_BY IN NUMBER
16 ,P_CREATION_DATE IN DATE
17 ,P_CREATED_BY IN NUMBER
18 ,P_LAST_UPDATE_LOGIN IN NUMBER) IS
19
20 CURSOR C_CHECK_INSERT IS
21 SELECT 'Y'
22 FROM MTL_RELATED_ITEMS_CUST_REF
23 WHERE PLN_INFO_ID = P_PLN_INFO_ID
24 AND INVENTORY_ITEM_ID = P_INVENTORY_ITEM_ID
25 AND ORGANIZATION_ID = P_ORGANIZATION_ID
26 AND RELATED_ITEM_ID = P_RELATED_ITEM_ID
27 AND RELATIONSHIP_TYPE_ID = P_RELATIONSHIP_TYPE_ID
28 AND CUSTOMER_ID = P_CUSTOMER_ID
29 AND SITE_USE_ID = P_SITE_USE_ID;
30
31 l_exists VARCHAR2(1);
32
33 BEGIN
34
35 INSERT INTO MTL_RELATED_ITEMS_CUST_REF(
36 PLN_INFO_ID
37 ,INVENTORY_ITEM_ID
38 ,ORGANIZATION_ID
39 ,RELATED_ITEM_ID
40 ,RELATIONSHIP_TYPE_ID
41 ,CUSTOMER_ID
42 ,SITE_USE_ID
43 ,START_DATE
44 ,END_DATE
45 ,LAST_UPDATE_DATE
46 ,LAST_UPDATED_BY
47 ,CREATION_DATE
48 ,CREATED_BY
49 ,LAST_UPDATE_LOGIN)
50 VALUES(
51 P_PLN_INFO_ID
52 ,P_INVENTORY_ITEM_ID
53 ,P_ORGANIZATION_ID
54 ,P_RELATED_ITEM_ID
55 ,P_RELATIONSHIP_TYPE_ID
56 ,P_CUSTOMER_ID
57 ,P_SITE_USE_ID
58 ,P_START_DATE
59 ,P_END_DATE
60 ,Nvl(P_LAST_UPDATE_DATE, SYSDATE)
61 ,Nvl(P_LAST_UPDATED_BY, FND_GLOBAL.USER_ID)
62 ,Nvl(P_CREATION_DATE, SYSDATE)
63 ,Nvl(P_CREATED_BY, FND_GLOBAL.USER_ID)
64 ,Nvl(P_LAST_UPDATE_LOGIN, FND_GLOBAL.LOGIN_ID));
65
66 OPEN C_CHECK_INSERT;
67 FETCH C_CHECK_INSERT INTO l_exists;
68 IF (C_CHECK_INSERT%NOTFOUND) THEN
69 CLOSE C_CHECK_INSERT;
70 RAISE No_Data_Found;
71 END IF;
72 CLOSE C_CHECK_INSERT;
73
74 END INSERT_ROW;
75
76 PROCEDURE LOCK_ROW(
77 P_PLN_INFO_ID IN NUMBER
78 ,P_INVENTORY_ITEM_ID IN NUMBER
79 ,P_ORGANIZATION_ID IN NUMBER
80 ,P_RELATED_ITEM_ID IN NUMBER
81 ,P_RELATIONSHIP_TYPE_ID IN NUMBER
82 ,P_CUSTOMER_ID IN NUMBER
83 ,P_SITE_USE_ID IN NUMBER
84 ,P_START_DATE IN DATE
85 ,P_END_DATE IN DATE) IS
86
87 CURSOR C_CUST_REF IS
88 SELECT
89 INVENTORY_ITEM_ID
90 ,ORGANIZATION_ID
91 ,RELATED_ITEM_ID
92 ,RELATIONSHIP_TYPE_ID
93 ,CUSTOMER_ID
94 ,SITE_USE_ID
95 ,START_DATE
96 ,END_DATE
97 FROM MTL_RELATED_ITEMS_CUST_REF
98 WHERE PLN_INFO_ID = P_PLN_INFO_ID
99 AND INVENTORY_ITEM_ID = P_INVENTORY_ITEM_ID
100 AND ORGANIZATION_ID = P_ORGANIZATION_ID
101 AND RELATED_ITEM_ID = P_RELATED_ITEM_ID
102 AND RELATIONSHIP_TYPE_ID = P_RELATIONSHIP_TYPE_ID
103 AND CUSTOMER_ID = P_CUSTOMER_ID
104 AND SITE_USE_ID = P_SITE_USE_ID
105 FOR UPDATE OF PLN_INFO_ID,
106 INVENTORY_ITEM_ID,
107 ORGANIZATION_ID,
108 RELATED_ITEM_ID,
109 RELATIONSHIP_TYPE_ID,
110 CUSTOMER_ID,
111 SITE_USE_ID,
112 START_DATE,
113 END_DATE NOWAIT;
114
115 l_recinfo C_CUST_REF%ROWTYPE;
116
117 BEGIN
118
119 OPEN C_CUST_REF;
120 FETCH C_CUST_REF INTO l_recinfo;
121 IF (C_CUST_REF%NOTFOUND) THEN
122 CLOSE C_CUST_REF;
123 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
124 APP_EXCEPTION.RAISE_EXCEPTION;
125 END IF;
126 CLOSE C_CUST_REF;
127
128 IF (l_recinfo.INVENTORY_ITEM_ID = P_INVENTORY_ITEM_ID)
129 AND (l_recinfo.ORGANIZATION_ID = P_ORGANIZATION_ID)
130 AND (l_recinfo.RELATED_ITEM_ID = P_RELATED_ITEM_ID)
131 AND (l_recinfo.RELATIONSHIP_TYPE_ID = P_RELATIONSHIP_TYPE_ID)
132 AND (l_recinfo.CUSTOMER_ID = P_CUSTOMER_ID)
133 AND ((l_recinfo.SITE_USE_ID = P_SITE_USE_ID)
134 OR ((l_recinfo.SITE_USE_ID IS NULL) AND (P_SITE_USE_ID IS NULL)))
135 AND ((l_recinfo.START_DATE = P_START_DATE)
136 OR ((l_recinfo.START_DATE IS NULL) AND (P_START_DATE IS NULL)))
137 AND ((l_recinfo.END_DATE = P_END_DATE)
138 OR ((l_recinfo.END_DATE IS NULL) AND (P_END_DATE IS NULL)))
139 THEN
140 NULL;
141 ELSE
142 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
143 APP_EXCEPTION.RAISE_EXCEPTION;
144 END IF;
145 END LOCK_ROW;
146
147 PROCEDURE UPDATE_ROW(
148 P_PLN_INFO_ID IN NUMBER
149 ,P_INVENTORY_ITEM_ID IN NUMBER
150 ,P_ORGANIZATION_ID IN NUMBER
151 ,P_RELATED_ITEM_ID IN NUMBER
152 ,P_RELATIONSHIP_TYPE_ID IN NUMBER
153 ,P_CUSTOMER_ID IN NUMBER
154 ,P_SITE_USE_ID IN NUMBER
155 ,P_START_DATE IN DATE
156 ,P_END_DATE IN DATE
157 ,P_LAST_UPDATE_DATE IN DATE
158 ,P_LAST_UPDATED_BY IN NUMBER
159 ,P_LAST_UPDATE_LOGIN IN NUMBER) IS
160 BEGIN
161 UPDATE MTL_RELATED_ITEMS_CUST_REF
162 SET
163 PLN_INFO_ID = P_PLN_INFO_ID
164 ,INVENTORY_ITEM_ID = P_INVENTORY_ITEM_ID
165 ,ORGANIZATION_ID = P_ORGANIZATION_ID
166 ,RELATED_ITEM_ID = P_RELATED_ITEM_ID
167 ,RELATIONSHIP_TYPE_ID = P_RELATIONSHIP_TYPE_ID
168 ,CUSTOMER_ID = P_CUSTOMER_ID
169 ,SITE_USE_ID = P_SITE_USE_ID
170 ,START_DATE = P_START_DATE
171 ,END_DATE = P_END_DATE
172 ,LAST_UPDATE_DATE = Nvl(P_LAST_UPDATE_DATE,SYSDATE)
173 ,LAST_UPDATED_BY = Nvl(P_LAST_UPDATED_BY,FND_GLOBAL.USER_ID)
174 ,LAST_UPDATE_LOGIN = Nvl(P_LAST_UPDATE_LOGIN,FND_GLOBAL.LOGIN_ID)
175 WHERE PLN_INFO_ID = P_PLN_INFO_ID
176 AND INVENTORY_ITEM_ID = P_INVENTORY_ITEM_ID
177 AND ORGANIZATION_ID = P_ORGANIZATION_ID
178 AND RELATED_ITEM_ID = P_RELATED_ITEM_ID
179 AND RELATIONSHIP_TYPE_ID = P_RELATIONSHIP_TYPE_ID
180 AND CUSTOMER_ID = P_CUSTOMER_ID
181 AND SITE_USE_ID = P_SITE_USE_ID;
182
183 IF (SQL%NOTFOUND) THEN
184 RAISE No_Data_Found;
185 END IF;
186
187 END UPDATE_ROW;
188
189 PROCEDURE DELETE_ROW(
190 P_PLN_INFO_ID IN NUMBER
191 ,P_INVENTORY_ITEM_ID IN NUMBER
192 ,P_ORGANIZATION_ID IN NUMBER
193 ,P_RELATED_ITEM_ID IN NUMBER
194 ,P_RELATIONSHIP_TYPE_ID IN NUMBER
195 ,P_CUSTOMER_ID IN NUMBER
196 ,P_SITE_USE_ID IN NUMBER) IS
197
198 BEGIN
199 DELETE FROM MTL_RELATED_ITEMS_CUST_REF
200 WHERE PLN_INFO_ID = P_PLN_INFO_ID
201 AND INVENTORY_ITEM_ID = P_INVENTORY_ITEM_ID
202 AND ORGANIZATION_ID = P_ORGANIZATION_ID
203 AND RELATED_ITEM_ID = P_RELATED_ITEM_ID
204 AND RELATIONSHIP_TYPE_ID = P_RELATIONSHIP_TYPE_ID
205 AND CUSTOMER_ID = P_CUSTOMER_ID
206 AND SITE_USE_ID = P_SITE_USE_ID;
207
208 IF (SQL%NOTFOUND) THEN
209 RAISE No_Data_Found;
210 END IF;
211 END DELETE_ROW;
212
213 END MTL_RELATED_ITEMS_CUST_REF_PKG;
[Home] [Help]