1 PACKAGE BODY MTL_RELATED_ITEMS_PLN_INFO_PKG AS
2 /* $Header: INVTRIPB.pls 120.0 2011/03/08 20:17:51 mshirkol noship $ */
3
4 PROCEDURE INSERT_ROW (
5 P_INVENTORY_ITEM_ID IN NUMBER
6 ,P_ORGANIZATION_ID IN NUMBER
7 ,P_RELATED_ITEM_ID IN NUMBER
8 ,P_RELATIONSHIP_TYPE_ID IN NUMBER
9 ,P_SUBSTITUTION_SET IN VARCHAR2
10 ,P_PARTIAL_FULFILLMENT_FLAG IN VARCHAR2
11 ,P_START_DATE IN DATE
12 ,P_END_DATE IN DATE
13 ,P_ALL_CUSTOMERS_FLAG IN VARCHAR2
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
19 ,X_PLN_INFO_ID OUT NOCOPY NUMBER) IS
20
21 CURSOR C_CHECK_INSERT IS
22 SELECT 'Y'
23 FROM MTL_RELATED_ITEMS_PLN_INFO
24 WHERE PLN_INFO_ID = X_PLN_INFO_ID
25 AND INVENTORY_ITEM_ID = P_INVENTORY_ITEM_ID
26 AND ORGANIZATION_ID = P_ORGANIZATION_ID
27 AND RELATED_ITEM_ID = P_RELATED_ITEM_ID
28 AND RELATIONSHIP_TYPE_ID = P_RELATIONSHIP_TYPE_ID;
29
30 l_exists VARCHAR2(1);
31
32 BEGIN
33 INSERT INTO MTL_RELATED_ITEMS_PLN_INFO(
34 PLN_INFO_ID
35 ,INVENTORY_ITEM_ID
36 ,ORGANIZATION_ID
37 ,RELATED_ITEM_ID
38 ,RELATIONSHIP_TYPE_ID
39 ,SUBSTITUTION_SET
40 ,PARTIAL_FULFILLMENT_FLAG
41 ,START_DATE
42 ,END_DATE
43 ,ALL_CUSTOMERS_FLAG
44 ,LAST_UPDATE_DATE
45 ,LAST_UPDATED_BY
46 ,CREATION_DATE
47 ,CREATED_BY
48 ,LAST_UPDATE_LOGIN)
49 VALUES(
50 MTL_REL_ITEM_PLN_INFO_S.NEXTVAL
51 ,P_INVENTORY_ITEM_ID
52 ,P_ORGANIZATION_ID
53 ,P_RELATED_ITEM_ID
54 ,P_RELATIONSHIP_TYPE_ID
55 ,P_SUBSTITUTION_SET
56 ,P_PARTIAL_FULFILLMENT_FLAG
57 ,P_START_DATE
58 ,P_END_DATE
59 ,P_ALL_CUSTOMERS_FLAG
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 RETURNING PLN_INFO_ID INTO X_PLN_INFO_ID;
67
68 OPEN C_CHECK_INSERT;
69 FETCH C_CHECK_INSERT INTO l_exists;
70 IF (C_CHECK_INSERT%NOTFOUND) THEN
71 CLOSE C_CHECK_INSERT;
72 RAISE No_Data_Found;
73 END IF;
74 CLOSE C_CHECK_INSERT;
75
76 END INSERT_ROW;
77
78 PROCEDURE LOCK_ROW (
79 P_PLN_INFO_ID IN NUMBER
80 ,P_INVENTORY_ITEM_ID IN NUMBER
81 ,P_ORGANIZATION_ID IN NUMBER
82 ,P_RELATED_ITEM_ID IN NUMBER
83 ,P_RELATIONSHIP_TYPE_ID IN NUMBER
84 ,P_SUBSTITUTION_SET IN VARCHAR2
85 ,P_PARTIAL_FULFILLMENT_FLAG IN VARCHAR2
86 ,P_START_DATE IN DATE
87 ,P_END_DATE IN DATE
88 ,P_ALL_CUSTOMERS_FLAG IN VARCHAR2) IS
89
90 CURSOR C_PLN_INFO IS
91 SELECT
92 INVENTORY_ITEM_ID
93 ,ORGANIZATION_ID
94 ,RELATED_ITEM_ID
95 ,RELATIONSHIP_TYPE_ID
96 ,SUBSTITUTION_SET
97 ,PARTIAL_FULFILLMENT_FLAG
98 ,START_DATE
99 ,END_DATE
100 ,ALL_CUSTOMERS_FLAG
101 FROM MTL_RELATED_ITEMS_PLN_INFO
102 WHERE PLN_INFO_ID = P_PLN_INFO_ID
103 AND INVENTORY_ITEM_ID = P_INVENTORY_ITEM_ID
104 AND ORGANIZATION_ID = P_ORGANIZATION_ID
105 AND RELATED_ITEM_ID = P_RELATED_ITEM_ID
106 AND RELATIONSHIP_TYPE_ID = P_RELATIONSHIP_TYPE_ID
107 FOR UPDATE OF PLN_INFO_ID,
108 INVENTORY_ITEM_ID,
109 ORGANIZATION_ID,
110 RELATED_ITEM_ID,
111 RELATIONSHIP_TYPE_ID NOWAIT;
112
113 l_recinfo C_PLN_INFO%ROWTYPE;
114
115 BEGIN
116
117 OPEN C_PLN_INFO;
118 FETCH C_PLN_INFO INTO l_recinfo;
119 IF (C_PLN_INFO%NOTFOUND) THEN
120 CLOSE C_PLN_INFO;
121 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
122 APP_EXCEPTION.RAISE_EXCEPTION;
123 END IF;
124 CLOSE C_PLN_INFO;
125
126 IF ((l_recinfo.INVENTORY_ITEM_ID = P_INVENTORY_ITEM_ID)
127 AND (l_recinfo.ORGANIZATION_ID = P_ORGANIZATION_ID)
128 AND (l_recinfo.RELATED_ITEM_ID = P_RELATED_ITEM_ID)
129 AND (l_recinfo.RELATIONSHIP_TYPE_ID = P_RELATIONSHIP_TYPE_ID)
130 AND ((l_recinfo.SUBSTITUTION_SET = P_SUBSTITUTION_SET)
131 OR ((l_recinfo.SUBSTITUTION_SET IS NULL) AND (P_SUBSTITUTION_SET IS NULL)))
132 AND (l_recinfo.PARTIAL_FULFILLMENT_FLAG = P_PARTIAL_FULFILLMENT_FLAG)
133 AND ((l_recinfo.START_DATE = P_START_DATE)
134 OR ((l_recinfo.START_DATE IS NULL) AND (P_START_DATE IS NULL)))
135 AND ((l_recinfo.END_DATE = P_END_DATE)
136 OR ((l_recinfo.END_DATE IS NULL) AND (P_END_DATE IS NULL)))
137 AND (l_recinfo.ALL_CUSTOMERS_FLAG = P_ALL_CUSTOMERS_FLAG))
138 THEN
139 NULL;
140 ELSE
141 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
142 APP_EXCEPTION.RAISE_EXCEPTION;
143 END IF;
144 END LOCK_ROW;
145
146 PROCEDURE UPDATE_ROW(
147 P_PLN_INFO_ID IN NUMBER
148 ,P_INVENTORY_ITEM_ID IN NUMBER
149 ,P_ORGANIZATION_ID IN NUMBER
150 ,P_RELATED_ITEM_ID IN NUMBER
151 ,P_RELATIONSHIP_TYPE_ID IN NUMBER
152 ,P_SUBSTITUTION_SET IN VARCHAR2
153 ,P_PARTIAL_FULFILLMENT_FLAG IN VARCHAR2
154 ,P_START_DATE IN DATE
155 ,P_END_DATE IN DATE
156 ,P_ALL_CUSTOMERS_FLAG IN VARCHAR2
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_PLN_INFO
162 SET
163 INVENTORY_ITEM_ID = P_INVENTORY_ITEM_ID
164 ,ORGANIZATION_ID = P_ORGANIZATION_ID
165 ,RELATED_ITEM_ID = P_RELATED_ITEM_ID
166 ,RELATIONSHIP_TYPE_ID = P_RELATIONSHIP_TYPE_ID
167 ,SUBSTITUTION_SET = P_SUBSTITUTION_SET
168 ,PARTIAL_FULFILLMENT_FLAG = P_PARTIAL_FULFILLMENT_FLAG
169 ,START_DATE = P_START_DATE
170 ,END_DATE = P_END_DATE
171 ,ALL_CUSTOMERS_FLAG = P_ALL_CUSTOMERS_FLAG
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
181 IF (SQL%NOTFOUND) THEN
182 RAISE No_Data_Found;
183 END IF;
184 END UPDATE_ROW;
185
186 PROCEDURE DELETE_ROW(
187 P_PLN_INFO_ID IN NUMBER
188 ,P_INVENTORY_ITEM_ID IN NUMBER
189 ,P_ORGANIZATION_ID IN NUMBER
190 ,P_RELATED_ITEM_ID IN NUMBER
191 ,P_RELATIONSHIP_TYPE_ID IN NUMBER) IS
192
193 BEGIN
194 DELETE FROM MTL_RELATED_ITEMS_PLN_INFO
195 WHERE PLN_INFO_ID = P_PLN_INFO_ID
196 AND INVENTORY_ITEM_ID = P_INVENTORY_ITEM_ID
197 AND ORGANIZATION_ID = P_ORGANIZATION_ID
198 AND RELATED_ITEM_ID = P_RELATED_ITEM_ID
199 AND RELATIONSHIP_TYPE_ID = P_RELATIONSHIP_TYPE_ID;
200
201 IF (SQL%NOTFOUND) THEN
202 RAISE No_Data_Found;
203 END IF;
204 END DELETE_ROW;
205
206 END MTL_RELATED_ITEMS_PLN_INFO_PKG;
[Home] [Help]