[Home] [Help]
PACKAGE BODY: APPS.CSP_STOCK_LISTS_PVT
Source
1 PACKAGE BODY CSP_STOCK_LISTS_PVT AS
2 /* $Header: cspvstlb.pls 120.0.12020000.5 2013/03/24 12:46:33 sunarasi noship $ */
3 -- Start of Comments
4 -- Package name :CSP_STOCK_LISTS_PVT
5 -- Purpose :
6 -- History :
7 -- 01-Mar-13, sunarasi
8 --
9 -- NOTE :
10 -- End of Comments
11 PROCEDURE PROCESS_ITEMS (p_ORGANIZATION_ID NUMBER
12 ,p_INVENTORY_ITEM_ID IN NUMBER
13 ,p_SUBINVENTORY_CODE VARCHAR2
14 ,p_MANUAL_AUTO VARCHAR2
15 ,p_REASON_CODE VARCHAR2
16 ,p_CREATED_BY NUMBER
17 ,p_CREATION_DATE DATE
18 ,p_LAST_UPDATED_BY NUMBER
19 ,p_LAST_UPDATE_DATE DATE
20 ,p_LAST_UPDATE_LOGIN NUMBER
21 ,x_return_status out NOCOPY VARCHAR2) AS
22 CURSOR get_items IS
23 SELECT *
24 FROM csp_stock_lists
25 where INVENTORY_ITEM_ID = p_inventory_item_id
26 and ORGANIZATION_ID = p_organization_id
27 and nvl(SUBINVENTORY_CODE,'X') = nvl(p_subinventory_code,'X');
28
29 l_rec CSP_STOCK_LISTS%ROWTYPE;
30 BEGIN
31 OPEN get_items;
32 FETCH get_items INTO l_rec;
33 IF get_items%FOUND THEN
34 CSP_STOCK_LISTS_PKG.update_Row(p_ORGANIZATION_ID
35 ,p_INVENTORY_ITEM_ID
36 ,p_SUBINVENTORY_CODE
37 ,p_MANUAL_AUTO
38 ,p_REASON_CODE
39 ,p_CREATED_BY
40 ,p_CREATION_DATE
41 ,p_LAST_UPDATED_BY
42 ,p_LAST_UPDATE_DATE
43 ,p_LAST_UPDATE_LOGIN);
44 ELSE
45 CSP_STOCK_LISTS_PKG.Insert_Row( p_ORGANIZATION_ID
46 ,p_INVENTORY_ITEM_ID
47 ,p_SUBINVENTORY_CODE
48 ,p_MANUAL_AUTO
49 ,p_REASON_CODE
50 ,p_CREATED_BY
51 ,p_CREATION_DATE
52 ,p_LAST_UPDATED_BY
53 ,p_LAST_UPDATE_DATE
54 ,p_LAST_UPDATE_LOGIN);
55 END IF;
56 CLOSE get_items;
57 EXCEPTION
58 WHEN OTHERS THEN
59 x_return_status := FND_API.G_RET_STS_ERROR;
60 END PROCESS_ITEMS;
61 PROCEDURE PROCESS_SUBINV_ITEMS(
62 p_ROWID VARCHAR2,
63 p_INVENTORY_ITEM_ID NUMBER,
64 p_ORGANIZATION_ID NUMBER,
65 p_SECONDARY_INVENTORY VARCHAR2,
66 p_LAST_UPDATE_DATE DATE,
67 p_LAST_UPDATED_BY NUMBER,
68 p_CREATION_DATE DATE,
69 p_CREATED_BY NUMBER,
70 p_LAST_UPDATE_LOGIN NUMBER,
71 p_PRIMARY_SUBINVENTORY_FLAG NUMBER,
72 p_PICKING_ORDER NUMBER,
73 p_MIN_MINMAX_QUANTITY NUMBER,
74 p_MAX_MINMAX_QUANTITY NUMBER,
75 p_INVENTORY_PLANNING_CODE NUMBER,
76 p_FIXED_LOT_MULTIPLE NUMBER,
77 p_MINIMUM_ORDER_QUANTITY NUMBER,
78 p_MAXIMUM_ORDER_QUANTITY NUMBER,
79 p_SOURCE_TYPE NUMBER,
80 p_SOURCE_ORGANIZATION_ID NUMBER,
81 p_SOURCE_SUBINVENTORY VARCHAR2,
82 p_ATTRIBUTE_CATEGORY VARCHAR2,
83 p_ATTRIBUTE1 VARCHAR2,
84 p_ATTRIBUTE2 VARCHAR2,
85 p_ATTRIBUTE3 VARCHAR2,
86 p_ATTRIBUTE4 VARCHAR2,
87 p_ATTRIBUTE5 VARCHAR2,
88 p_ATTRIBUTE6 VARCHAR2,
89 p_ATTRIBUTE7 VARCHAR2,
90 p_ATTRIBUTE8 VARCHAR2,
91 p_ATTRIBUTE9 VARCHAR2,
92 p_ATTRIBUTE10 VARCHAR2,
93 p_ATTRIBUTE11 VARCHAR2,
94 p_ATTRIBUTE12 VARCHAR2,
95 p_ATTRIBUTE13 VARCHAR2,
96 p_ATTRIBUTE14 VARCHAR2,
97 p_ATTRIBUTE15 VARCHAR2,
98 p_ENCUMBRANCE_ACCOUNT NUMBER,
99 p_PREPROCESSING_LEAD_TIME NUMBER,
100 p_PROCESSING_LEAD_TIME NUMBER,
101 p_POSTPROCESSING_LEAD_TIME NUMBER
102 ,x_return_status out NOCOPY VARCHAR2) AS
103
104 CURSOR get_subinv_items IS
105 SELECT *
106 FROM MTL_ITEM_SUB_INVENTORIES
107 where INVENTORY_ITEM_ID = p_inventory_item_id
108 and ORGANIZATION_ID = p_organization_id
109 and SECONDARY_INVENTORY = p_secondary_inventory;
110
111 l_rec MTL_ITEM_SUB_INVENTORIES%ROWTYPE;
112 l_item_id NUMBER;
113 BEGIN
114 l_item_id := p_inventory_item_id;
115 OPEN get_subinv_items;
116 FETCH get_subinv_items INTO l_rec;
117 IF get_subinv_items%FOUND THEN
118 CSP_ITEM_SUB_INVENTORIES_PKG.Update_Row(
119 p_ROWID ,
120 l_item_id ,
121 p_ORGANIZATION_ID ,
122 p_SECONDARY_INVENTORY ,
123 p_LAST_UPDATE_DATE ,
124 p_LAST_UPDATED_BY ,
125 p_CREATION_DATE ,
126 p_CREATED_BY ,
127 p_LAST_UPDATE_LOGIN ,
128 p_PRIMARY_SUBINVENTORY_FLAG ,
129 p_PICKING_ORDER ,
130 p_MIN_MINMAX_QUANTITY ,
131 p_MAX_MINMAX_QUANTITY ,
132 p_INVENTORY_PLANNING_CODE ,
133 p_FIXED_LOT_MULTIPLE ,
134 p_MINIMUM_ORDER_QUANTITY ,
135 p_MAXIMUM_ORDER_QUANTITY ,
136 p_SOURCE_TYPE ,
137 p_SOURCE_ORGANIZATION_ID ,
138 p_SOURCE_SUBINVENTORY ,
139 p_ATTRIBUTE_CATEGORY ,
140 p_ATTRIBUTE1 ,
141 p_ATTRIBUTE2 ,
142 p_ATTRIBUTE3 ,
143 p_ATTRIBUTE4 ,
144 p_ATTRIBUTE5 ,
145 p_ATTRIBUTE6 ,
146 p_ATTRIBUTE7 ,
147 p_ATTRIBUTE8 ,
148 p_ATTRIBUTE9 ,
149 p_ATTRIBUTE10 ,
150 p_ATTRIBUTE11 ,
151 p_ATTRIBUTE12 ,
152 p_ATTRIBUTE13 ,
153 p_ATTRIBUTE14 ,
154 p_ATTRIBUTE15 ,
155 p_ENCUMBRANCE_ACCOUNT ,
156 p_PREPROCESSING_LEAD_TIME ,
157 p_PROCESSING_LEAD_TIME ,
158 p_POSTPROCESSING_LEAD_TIME );
159 ELSE
160 CSP_ITEM_SUB_INVENTORIES_PKG.Insert_Row(
161 l_item_id ,
162 p_ORGANIZATION_ID ,
163 p_SECONDARY_INVENTORY ,
164 p_LAST_UPDATE_DATE ,
165 p_LAST_UPDATED_BY ,
166 p_CREATION_DATE ,
167 p_CREATED_BY ,
168 p_LAST_UPDATE_LOGIN ,
169 p_PRIMARY_SUBINVENTORY_FLAG ,
170 p_PICKING_ORDER ,
171 p_MIN_MINMAX_QUANTITY ,
172 p_MAX_MINMAX_QUANTITY ,
173 p_INVENTORY_PLANNING_CODE ,
174 p_FIXED_LOT_MULTIPLE ,
175 p_MINIMUM_ORDER_QUANTITY ,
176 p_MAXIMUM_ORDER_QUANTITY ,
177 p_SOURCE_TYPE ,
178 p_SOURCE_ORGANIZATION_ID ,
179 p_SOURCE_SUBINVENTORY ,
180 p_ATTRIBUTE_CATEGORY ,
181 p_ATTRIBUTE1 ,
182 p_ATTRIBUTE2 ,
183 p_ATTRIBUTE3 ,
184 p_ATTRIBUTE4 ,
185 p_ATTRIBUTE5 ,
186 p_ATTRIBUTE6 ,
187 p_ATTRIBUTE7 ,
188 p_ATTRIBUTE8 ,
189 p_ATTRIBUTE9 ,
190 p_ATTRIBUTE10 ,
191 p_ATTRIBUTE11 ,
192 p_ATTRIBUTE12 ,
193 p_ATTRIBUTE13 ,
194 p_ATTRIBUTE14 ,
195 p_ATTRIBUTE15 ,
196 p_ENCUMBRANCE_ACCOUNT ,
197 p_PREPROCESSING_LEAD_TIME ,
198 p_PROCESSING_LEAD_TIME ,
199 p_POSTPROCESSING_LEAD_TIME);
200 END IF;
201 END PROCESS_SUBINV_ITEMS;
202 PROCEDURE DELETE_STOCK_LIST(
203 P_INVENTORY_ITEM_ID NUMBER
204 ,p_ORGANIZATION_ID NUMBER
205 ,P_SUBINVENTORY_CODE VARCHAR2
206 ,X_RETURN_STATUS OUT NOCOPY VARCHAR2) IS
207 ln_count NUMBER :=0;
208 BEGIN
209 SELECT count(*) into ln_count
210 FROM CSP_STOCK_LISTS
211 WHERE INVENTORY_ITEM_ID = p_INVENTORY_ITEM_ID
212 AND ORGANIZATION_ID = P_ORGANIZATION_ID
213 AND nvl(SUBINVENTORY_CODE,'X') = nvl(P_SUBINVENTORY_CODE,'X');
214 IF ln_count > 0 THEN
215 CSP_STOCK_LISTS_PKG.DELETE_ROW(p_INVENTORY_ITEM_ID,p_ORGANIZATION_ID,P_SUBINVENTORY_CODE );
216 END IF;
217 END;
218 END CSP_STOCK_LISTS_PVT;