[Home] [Help]
PACKAGE BODY: APPS.CHV_SCHEDULE_ITEMS_PKG_S1
Source
1 PACKAGE BODY CHV_SCHEDULE_ITEMS_PKG_S1 as
2 /* $Header: CHVSITMB.pls 115.0 99/07/17 01:31:37 porting ship $ */
3
4 /*=============================================================================
5
6 PROCEDURE NAME: lock_row()
7
8 =============================================================================*/
9 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
10 X_Schedule_Id NUMBER,
11 X_Schedule_Item_Id NUMBER,
12 X_Organization_Id NUMBER,
13 X_Item_Id NUMBER,
14 X_Item_Planning_Method VARCHAR2,
15 X_Po_Header_Id NUMBER,
16 X_Po_Line_Id NUMBER,
17 X_Rebuild_Flag VARCHAR2,
18 X_Item_Confirm_Status VARCHAR2,
19 X_Starting_Cum_Quantity NUMBER,
20 X_Starting_Auth_Quantity NUMBER,
21 X_Starting_Cum_Qty_Primary NUMBER,
22 X_Starting_Auth_Qty_Primary NUMBER,
23 X_Last_Receipt_Transaction_Id NUMBER,
24 X_Purchasing_Unit_OF_Measure VARCHAR2,
25 X_Primary_Unit_Of_Measure VARCHAR2,
26 X_Attribute_Category VARCHAR2,
27 X_Attribute1 VARCHAR2,
28 X_Attribute2 VARCHAR2,
29 X_Attribute3 VARCHAR2,
30 X_Attribute4 VARCHAR2,
31 X_Attribute5 VARCHAR2,
32 X_Attribute6 VARCHAR2,
33 X_Attribute7 VARCHAR2,
34 X_Attribute8 VARCHAR2,
35 X_Attribute9 VARCHAR2,
36 X_Attribute10 VARCHAR2,
37 X_Attribute11 VARCHAR2,
38 X_Attribute12 VARCHAR2,
39 X_Attribute13 VARCHAR2,
40 X_Attribute14 VARCHAR2,
41 X_Attribute15 VARCHAR2
42 ) IS
43 CURSOR C IS
44 SELECT *
45 FROM CHV_SCHEDULE_ITEMS
46 WHERE rowid = X_Rowid
47 FOR UPDATE of Schedule_Id NOWAIT;
48 Itemrec C%ROWTYPE;
49
50 BEGIN
51 OPEN C;
52 FETCH C INTO Itemrec;
53 if (C%NOTFOUND) then
54 CLOSE C;
55 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
56 APP_EXCEPTION.Raise_Exception;
57 end if;
58 CLOSE C;
59 if (
60 (Itemrec.schedule_id = X_Schedule_Id)
61 AND (Itemrec.schedule_item_id = X_schedule_item_id)
62 AND (Itemrec.organization_id = X_organization_id)
63 AND (Itemrec.item_id = X_item_id)
64 AND (Itemrec.item_planning_method = X_item_planning_method)
65 AND ( (Itemrec.po_header_id = X_po_header_id)
66 OR ( (Itemrec.po_header_id IS NULL)
67 AND (X_po_header_id IS NULL)))
68 AND ( (Itemrec.po_line_id = X_po_line_id)
69 OR ( (Itemrec.po_line_id IS NULL)
70 AND (X_po_line_id IS NULL)))
71 AND ( (Itemrec.rebuild_flag = X_rebuild_flag)
72 OR ( (Itemrec.rebuild_flag IS NULL)
73 AND (X_rebuild_flag IS NULL)))
74 AND ( (Itemrec.item_confirm_status =
75 X_item_confirm_status)
76 OR ( (Itemrec.item_confirm_status IS NULL)
77 AND (X_item_confirm_status IS NULL)))
78 AND ( (Itemrec.starting_cum_quantity =
79 X_starting_cum_quantity)
80 OR ( (Itemrec.starting_cum_quantity IS NULL)
81 AND (X_starting_cum_quantity IS NULL)))
82 AND ( (Itemrec.starting_auth_quantity =
83 X_starting_auth_quantity)
84 OR ( (Itemrec.starting_auth_quantity IS NULL)
85 AND (X_starting_auth_quantity IS NULL)))
86 AND ( (Itemrec.starting_cum_qty_primary =
87 X_starting_cum_qty_primary)
88 OR ( (Itemrec.starting_cum_qty_primary IS NULL)
89 AND (X_starting_cum_qty_primary IS NULL)))
90 AND ( (Itemrec.starting_auth_qty_primary =
91 X_starting_auth_qty_primary)
92 OR ( (Itemrec.starting_auth_qty_primary IS NULL)
93 AND (X_starting_auth_qty_primary IS NULL)))
94 AND ( (Itemrec.last_receipt_transaction_id =
95 X_last_receipt_transaction_id)
96 OR ( (Itemrec.last_receipt_transaction_id IS NULL)
97 AND (X_last_receipt_transaction_id IS NULL)))
98 AND ( (Itemrec.purchasing_unit_of_measure =
99 X_purchasing_unit_of_measure)
100 OR ( (Itemrec.purchasing_unit_of_measure IS NULL)
101 AND (X_purchasing_unit_of_measure IS NULL)))
102 AND ( (Itemrec.primary_unit_of_measure =
103 X_primary_unit_of_measure)
104 OR ( (Itemrec.primary_unit_of_measure IS NULL)
105 AND (X_primary_unit_of_measure IS NULL)))
106 AND ( (Itemrec.attribute_category = X_Attribute_Category)
107 OR ( (Itemrec.attribute_category IS NULL)
108 AND (X_Attribute_Category IS NULL)))
109 AND ( (Itemrec.attribute1 = X_Attribute1)
110 OR ( (Itemrec.attribute1 IS NULL)
111 AND (X_Attribute1 IS NULL)))
112 AND ( (Itemrec.attribute2 = X_Attribute2)
113 OR ( (Itemrec.attribute2 IS NULL)
114 AND (X_Attribute2 IS NULL)))
115 AND ( (Itemrec.attribute3 = X_Attribute3)
116 OR ( (Itemrec.attribute3 IS NULL)
117 AND (X_Attribute3 IS NULL)))
118 AND ( (Itemrec.attribute4 = X_Attribute4)
119 OR ( (Itemrec.attribute4 IS NULL)
120 AND (X_Attribute4 IS NULL)))
121 AND ( (Itemrec.attribute5 = X_Attribute5)
122 OR ( (Itemrec.attribute5 IS NULL)
123 AND (X_Attribute5 IS NULL)))
124 AND ( (Itemrec.attribute6 = X_Attribute6)
125 OR ( (Itemrec.attribute6 IS NULL)
126 AND (X_Attribute6 IS NULL)))
127 AND ( (Itemrec.attribute7 = X_Attribute7)
128 OR ( (Itemrec.attribute7 IS NULL)
129 AND (X_Attribute7 IS NULL)))
130 AND ( (Itemrec.attribute8 = X_Attribute8)
131 OR ( (Itemrec.attribute8 IS NULL)
132 AND (X_Attribute8 IS NULL)))
133 AND ( (Itemrec.attribute9 = X_Attribute9)
134 OR ( (Itemrec.attribute9 IS NULL)
135 AND (X_Attribute9 IS NULL)))
136 AND ( (Itemrec.attribute10 = X_Attribute10)
137 OR ( (Itemrec.attribute10 IS NULL)
138 AND (X_Attribute10 IS NULL)))
139 AND ( (Itemrec.attribute11 = X_Attribute11)
140 OR ( (Itemrec.attribute11 IS NULL)
141 AND (X_Attribute11 IS NULL)))
142 AND ( (Itemrec.attribute12 = X_Attribute12)
143 OR ( (Itemrec.attribute12 IS NULL)
144 AND (X_Attribute12 IS NULL)))
145 AND ( (Itemrec.attribute13 = X_Attribute13)
146 OR ( (Itemrec.attribute13 IS NULL)
147 AND (X_Attribute13 IS NULL)))
148 AND ( (Itemrec.attribute14 = X_Attribute14)
149 OR ( (Itemrec.attribute14 IS NULL)
150 AND (X_Attribute14 IS NULL)))
151 AND ( (Itemrec.attribute15 = X_Attribute15)
152 OR ( (Itemrec.attribute15 IS NULL)
153 AND (X_Attribute15 IS NULL)))
154 ) then
155 return;
156 else
157 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
158 APP_EXCEPTION.RAISE_EXCEPTION;
159 end if;
160 END Lock_Row;
161
162 /*===========================================================================
163
164 PROCEDURE NAME: update_row()
165
166 =============================================================================*/
167 PROCEDURE Update_Row(X_Rowid VARCHAR2,
168 X_Item_Confirm_Status VARCHAR2,
169 X_Rebuild_Flag VARCHAR2,
170 X_Last_Update_Date DATE,
171 X_Last_Updated_By NUMBER,
172 X_Last_Update_Login NUMBER,
173 X_Attribute_Category VARCHAR2,
174 X_Attribute1 VARCHAR2,
175 X_Attribute2 VARCHAR2,
176 X_Attribute3 VARCHAR2,
177 X_Attribute4 VARCHAR2,
178 X_Attribute5 VARCHAR2,
179 X_Attribute6 VARCHAR2,
180 X_Attribute7 VARCHAR2,
181 X_Attribute8 VARCHAR2,
182 X_Attribute9 VARCHAR2,
183 X_Attribute10 VARCHAR2,
184 X_Attribute11 VARCHAR2,
185 X_Attribute12 VARCHAR2,
186 X_Attribute13 VARCHAR2,
187 X_Attribute14 VARCHAR2,
188 X_Attribute15 VARCHAR2
189 ) IS
190
191 BEGIN
192
193 UPDATE CHV_SCHEDULE_ITEMS
194 SET
195 item_confirm_status = X_Item_Confirm_Status,
196 rebuild_flag = X_Rebuild_Flag,
197 last_update_date = X_Last_Update_Date,
198 last_updated_by = X_Last_Updated_By,
199 last_update_login = X_Last_Update_Login,
200 attribute_category = X_Attribute_Category,
201 attribute1 = X_Attribute1,
202 attribute2 = X_Attribute2,
203 attribute3 = X_Attribute3,
204 attribute4 = X_Attribute4,
205 attribute5 = X_Attribute5,
206 attribute6 = X_Attribute6,
207 attribute7 = X_Attribute7,
208 attribute8 = X_Attribute8,
209 attribute9 = X_Attribute9,
210 attribute10 = X_Attribute10,
211 attribute11 = X_Attribute11,
212 attribute12 = X_Attribute12,
213 attribute13 = X_Attribute13,
214 attribute14 = X_Attribute14,
215 attribute15 = X_Attribute15
216 WHERE rowid = X_rowid ;
217
218 if (SQL%NOTFOUND) then
219 Raise NO_DATA_FOUND;
220 end if;
221
222 END Update_Row;
223
224 /*===========================================================================
225
226 PROCEDURE NAME: delete_row1()
227
228 =============================================================================*/
229 PROCEDURE delete_row1(X_RowId VARCHAR2,
230 X_Schedule_Item_Id NUMBER,
231 X_Schedule_Id NUMBER
232 ) IS
233
234 BEGIN
235
236
237 CHV_ITEM_ORDERS_PKG_S1.delete_row(X_Schedule_Id,
238 X_Schedule_Item_Id) ;
239
240 CHV_HORIZ_SCHEDULES_PKG_S1.delete_row(X_Schedule_Id,
241 X_Schedule_Item_Id) ;
242
243 CHV_AUTHORIZATIONS_PKG_S1.delete_row(X_Schedule_Item_Id) ;
244
245 /* After the data is deleted from the child tables
246 ** delete from the items table.
247 */
248
249 DELETE FROM chv_schedule_items
250 WHERE rowid = X_Rowid ;
251
252 if (SQL%NOTFOUND) then
253 Raise NO_DATA_FOUND ;
254 end if ;
255
256 END delete_row1 ;
257
258 /*===========================================================================
259
260 PROCEDURE NAME: delete_row2()
261
262 =============================================================================*/
263 PROCEDURE delete_row2(X_Schedule_Id NUMBER
264 ) IS
265
266 Cursor C1 is SELECT rowid, schedule_item_id
267 FROM CHV_SCHEDULE_ITEMS
268 WHERE schedule_id = X_Schedule_Id ;
269
270 BEGIN
271
272 for Itemrec in C1 loop
273
274 /* Execute procedure to actually delete the records */
275
276 CHV_SCHEDULE_ITEMS_PKG_S1.delete_row1(Itemrec.rowid,
277 Itemrec.Schedule_Item_Id,
278 X_Schedule_Id
279 ) ;
280 end loop ;
281
282 END delete_row2 ;
283
284 END CHV_SCHEDULE_ITEMS_PKG_S1;