1 PACKAGE BODY WIP_JOBS_LOCK_ROW as
2 /* $Header: wipdjlrb.pls 115.24 2002/12/12 16:57:59 rmahidha ship $ */
3
4 PROCEDURE Lock_Row
5 (X_Row_Id VARCHAR2,
6 X_wip_entity_id NUMBER,
7 X_organization_id NUMBER,
8 X_last_update_date DATE,
9 X_last_updated_by NUMBER,
10 X_creation_date DATE,
11 X_created_by NUMBER,
12 X_last_update_login NUMBER,
13 X_description VARCHAR2,
14 X_status_type NUMBER,
15 X_primary_item_id NUMBER,
16 X_firm_planned_flag NUMBER,
17 X_job_type NUMBER,
18 X_wip_supply_type NUMBER,
19 X_class_code VARCHAR2,
20 X_material_account NUMBER,
21 X_material_overhead_account NUMBER,
22 X_resource_account NUMBER,
23 X_outside_processing_account NUMBER,
24 X_material_variance_account NUMBER,
25 X_resource_variance_account NUMBER,
26 X_outside_proc_var_account NUMBER,
27 X_std_cost_adjustment_account NUMBER,
28 X_overhead_account NUMBER,
29 X_overhead_variance_account NUMBER,
30 X_scheduled_start_date DATE,
31 X_date_released DATE,
32 X_scheduled_completion_date DATE,
33 X_date_completed DATE,
34 X_date_closed DATE,
35 X_start_quantity NUMBER,
36 X_overcompletion_toleran_type NUMBER,
37 X_overcompletion_toleran_value NUMBER,
38 X_quantity_completed NUMBER,
39 X_quantity_scrapped NUMBER,
40 X_net_quantity NUMBER,
41 X_bom_reference_id NUMBER,
42 X_routing_reference_id NUMBER,
43 X_common_bom_sequence_id NUMBER,
44 X_common_routing_sequence_id NUMBER,
45 X_bom_revision VARCHAR2,
46 X_routing_revision VARCHAR2,
47 X_bom_revision_date DATE,
48 X_routing_revision_date DATE,
49 X_lot_number VARCHAR2,
50 X_alternate_bom_designator VARCHAR2,
51 X_alternate_routing_designator VARCHAR2,
52 X_completion_subinventory VARCHAR2,
53 X_completion_locator_id NUMBER,
54 X_demand_class VARCHAR2,
55 X_attribute_category VARCHAR2,
56 X_attribute1 VARCHAR2,
57 X_attribute2 VARCHAR2,
58 X_attribute3 VARCHAR2,
59 X_attribute4 VARCHAR2,
60 X_attribute5 VARCHAR2,
61 X_attribute6 VARCHAR2,
62 X_attribute7 VARCHAR2,
63 X_attribute8 VARCHAR2,
64 X_attribute9 VARCHAR2,
65 X_attribute10 VARCHAR2,
66 X_attribute11 VARCHAR2,
67 X_attribute12 VARCHAR2,
68 X_attribute13 VARCHAR2,
69 X_attribute14 VARCHAR2,
70 X_attribute15 VARCHAR2,
71 X_end_item_unit_number VARCHAR2,
72 X_Schedule_Group_Id NUMBER,
73 X_Build_Sequence NUMBER,
74 X_Line_Id NUMBER,
75 X_Project_Id NUMBER,
76 X_Task_Id NUMBER,
77 X_priority NUMBER,
78 X_due_date DATE,
79 X_due_date_penalty NUMBER,
80 X_due_date_tolerance NUMBER,
81 X_requested_start_date DATE,
82 x_serialization_start_op NUMBER := null)
83 /*X_Project_Costed NUMBER)*/
84 IS
85 CURSOR C IS
86 SELECT wip_entity_id, organization_id, description,
87 status_type, primary_item_id, firm_planned_flag, job_type,
88 wip_supply_type, class_code, material_account, material_overhead_account
89 ,resource_account ,outside_processing_account ,material_variance_account
90 ,resource_variance_account ,outside_proc_variance_account ,std_cost_adjustment_account
91 ,overhead_account ,overhead_variance_account ,scheduled_start_date
92 ,date_released ,scheduled_completion_date ,date_completed
93 ,date_closed ,start_quantity ,overcompletion_tolerance_type
94 ,overcompletion_tolerance_value,quantity_completed
95 ,quantity_scrapped ,net_quantity , bom_reference_id
96 ,routing_reference_id ,common_bom_sequence_id ,common_routing_sequence_id
97 ,bom_revision ,routing_revision ,bom_revision_date
98 ,routing_revision_date ,lot_number ,alternate_bom_designator
99 ,alternate_routing_designator ,completion_subinventory ,completion_locator_id
100 ,demand_class ,attribute_category ,attribute1 ,attribute2 ,attribute3
101 ,attribute4 ,attribute5 ,attribute6 ,attribute7 ,attribute8
102 ,attribute9 ,attribute10 ,attribute11 ,attribute12 ,attribute13
103 ,attribute14 ,attribute15, end_item_unit_number, schedule_group_id, build_sequence, line_id
104 ,project_id, task_id, priority, due_date
105 ,due_date_penalty, due_date_tolerance, requested_start_date, serialization_start_op /*project_costed*/
106 FROM WIP_DISCRETE_JOBS
107 WHERE rowid = X_Row_id
108 FOR UPDATE of Wip_Entity_Id NOWAIT;
109 Recinfo C%ROWTYPE;
110
111 BEGIN
112 OPEN C;
113 FETCH C INTO Recinfo;
114 if (C%NOTFOUND) then
115 CLOSE C;
116 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
117 APP_EXCEPTION.Raise_Exception;
118 end if;
119 CLOSE C;
120 if (
121 (Recinfo.wip_entity_id = X_Wip_Entity_Id)
122 AND (Recinfo.organization_id = X_Organization_Id)
123 AND ( (Recinfo.description = X_Description)
124 OR ( (Recinfo.description IS NULL)
125 AND (X_Description IS NULL)))
126 AND (Recinfo.status_type = X_Status_Type)
127 AND ( (Recinfo.primary_item_id = X_Primary_Item_Id)
128 OR ( (Recinfo.primary_item_id IS NULL)
129 AND (X_Primary_Item_Id IS NULL)))
130 AND (Recinfo.firm_planned_flag = X_Firm_Planned_Flag)
131 AND (Recinfo.job_type = X_Job_Type)
132 AND (Recinfo.wip_supply_type = X_Wip_Supply_Type)
133 AND (Recinfo.class_code = X_Class_Code)
134 AND ( (Recinfo.material_account = X_Material_Account)
135 OR ( (Recinfo.material_account IS NULL)
136 AND (X_Material_Account IS NULL)))
137 AND ( (Recinfo.material_overhead_account = X_Material_Overhead_Account)
138 OR ( (Recinfo.material_overhead_account IS NULL)
139 AND (X_Material_Overhead_Account IS NULL)))
140 AND ( (Recinfo.resource_account = X_Resource_Account)
141 OR ( (Recinfo.resource_account IS NULL)
142 AND (X_Resource_Account IS NULL)))
143 AND ( (Recinfo.outside_processing_account = X_Outside_Processing_Account)
144 OR ( (Recinfo.outside_processing_account IS NULL)
145 AND (X_Outside_Processing_Account IS NULL)))
146 AND ( (Recinfo.material_variance_account = X_Material_Variance_Account)
147 OR ( (Recinfo.material_variance_account IS NULL)
148 AND (X_Material_Variance_Account IS NULL)))
149 AND ( (Recinfo.resource_variance_account = X_Resource_Variance_Account)
150 OR ( (Recinfo.resource_variance_account IS NULL)
151 AND (X_Resource_Variance_Account IS NULL)))
152 AND ( (Recinfo.outside_proc_variance_account = X_Outside_Proc_Var_Account)
153 OR ( (Recinfo.outside_proc_variance_account IS NULL)
154 AND (X_Outside_Proc_Var_Account IS NULL)))
155 AND ( (Recinfo.std_cost_adjustment_account = X_Std_Cost_Adjustment_Account)
156 OR ( (Recinfo.std_cost_adjustment_account IS NULL)
157 AND (X_Std_Cost_Adjustment_Account IS NULL)))
158 AND ( (Recinfo.overhead_account = X_Overhead_Account)
159 OR ( (Recinfo.overhead_account IS NULL)
160 AND (X_Overhead_Account IS NULL)))
161 AND ( (Recinfo.overhead_variance_account = X_Overhead_Variance_Account)
162 OR ( (Recinfo.overhead_variance_account IS NULL)
163 AND (X_Overhead_Variance_Account IS NULL)))
164 AND (Recinfo.scheduled_start_date = X_Scheduled_Start_Date)
165 AND ( (to_char(Recinfo.date_released, 'DD-MON-YYYY') = to_char(X_Date_Released, 'DD-MON-YYYY'))
166 OR ( (Recinfo.date_released IS NULL)
167 AND (X_Date_Released IS NULL)))
168 AND (Recinfo.scheduled_completion_date = X_Scheduled_Completion_Date)
169 AND ( (to_char(Recinfo.date_completed, 'DD-MON-YYYY') = to_char(X_Date_Completed, 'DD-MON-YYYY'))
170 OR ( (Recinfo.date_completed IS NULL)
171 AND (X_Date_Completed IS NULL)))
172 AND ( (to_char(Recinfo.date_closed, 'DD-MON-YYYY') = to_char(X_Date_Closed, 'DD-MON-YYYY'))
173 OR ( (Recinfo.date_closed IS NULL)
174 AND (X_Date_Closed IS NULL)))
175 AND (Recinfo.start_quantity = X_Start_Quantity)
176 AND ( (Recinfo.overcompletion_tolerance_type = X_Overcompletion_Toleran_Type)
177 OR ( (Recinfo.overcompletion_tolerance_type IS NULL)
178 AND (X_Overcompletion_Toleran_Type IS NULL)))
179 AND ( (Recinfo.overcompletion_tolerance_value = X_Overcompletion_Toleran_Value)
180 OR ( (Recinfo.overcompletion_tolerance_value IS NULL)
181 AND (X_Overcompletion_Toleran_Value IS NULL)))
182 AND (Recinfo.quantity_completed = NVL(X_Quantity_Completed,0))
183 AND (Recinfo.quantity_scrapped = NVL(X_Quantity_Scrapped,0))
184 AND (Recinfo.net_quantity = X_Net_Quantity)
185 ) then if (
186 ( (Recinfo.bom_reference_id = X_Bom_Reference_Id)
187 OR ( (Recinfo.bom_reference_id IS NULL)
188 AND (X_Bom_Reference_Id IS NULL)))
189 AND ( (Recinfo.routing_reference_id = X_Routing_Reference_Id)
190 OR ( (Recinfo.routing_reference_id IS NULL)
191 AND (X_Routing_Reference_Id IS NULL)))
192 AND ( (Recinfo.common_bom_sequence_id = X_Common_Bom_Sequence_Id)
193 OR ( (Recinfo.common_bom_sequence_id IS NULL)
194 AND (X_Common_Bom_Sequence_Id IS NULL)))
195 AND ( (Recinfo.common_routing_sequence_id = X_Common_Routing_Sequence_Id)
196 OR ( (Recinfo.common_routing_sequence_id IS NULL)
197 AND (X_Common_Routing_Sequence_Id IS NULL)))
198 AND ( (Recinfo.bom_revision = X_Bom_Revision)
199 OR ( (Recinfo.bom_revision IS NULL)
200 AND (X_Bom_Revision IS NULL)))
201 AND ( (Recinfo.routing_revision = X_Routing_Revision)
202 OR ( (Recinfo.routing_revision IS NULL)
203 AND (X_Routing_Revision IS NULL)))
204 AND ( (Recinfo.bom_revision_date = X_Bom_Revision_Date)
205 OR ( (Recinfo.bom_revision_date IS NULL)
206 AND (X_Bom_Revision_Date IS NULL)))
207 AND ( (Recinfo.routing_revision_date = X_Routing_Revision_Date)
208 OR ( (Recinfo.routing_revision_date IS NULL)
209 AND (X_Routing_Revision_Date IS NULL)))
210 AND ( (Recinfo.lot_number = X_Lot_Number)
211 OR ( (Recinfo.lot_number IS NULL)
212 AND (X_Lot_Number IS NULL)))
213 AND ( (Recinfo.alternate_bom_designator = X_Alternate_Bom_Designator)
214 OR ( (Recinfo.alternate_bom_designator IS NULL)
215 AND (X_Alternate_Bom_Designator IS NULL)))
216 AND ( (Recinfo.alternate_routing_designator = X_Alternate_Routing_Designator)
217 OR ( (Recinfo.alternate_routing_designator IS NULL)
218 AND (X_Alternate_Routing_Designator IS NULL)))
219 AND ( (Recinfo.completion_subinventory = X_Completion_Subinventory)
220 OR ( (Recinfo.completion_subinventory IS NULL)
221 AND (X_Completion_Subinventory IS NULL)))
222 AND ( (Recinfo.completion_locator_id = X_Completion_Locator_Id)
223 OR ( (Recinfo.completion_locator_id IS NULL)
224 AND (X_Completion_Locator_Id IS NULL)))
225 AND ( (Recinfo.demand_class = X_Demand_Class)
226 OR ( (Recinfo.demand_class IS NULL)
227 AND (X_Demand_Class IS NULL)))
228 AND ( (Recinfo.attribute_category = X_Attribute_Category)
229 OR ( (Recinfo.attribute_category IS NULL)
230 AND (X_Attribute_Category IS NULL)))
231 AND ( (Recinfo.attribute1 = X_Attribute1)
232 OR ( (Recinfo.attribute1 IS NULL)
233 AND (X_Attribute1 IS NULL)))
234 AND ( (Recinfo.attribute2 = X_Attribute2)
235 OR ( (Recinfo.attribute2 IS NULL)
236 AND (X_Attribute2 IS NULL)))
237 AND ( (Recinfo.attribute3 = X_Attribute3)
238 OR ( (Recinfo.attribute3 IS NULL)
239 AND (X_Attribute3 IS NULL)))
240 AND ( (Recinfo.attribute4 = X_Attribute4)
241 OR ( (Recinfo.attribute4 IS NULL)
242 AND (X_Attribute4 IS NULL)))
243 AND ( (Recinfo.attribute5 = X_Attribute5)
244 OR ( (Recinfo.attribute5 IS NULL)
245 AND (X_Attribute5 IS NULL)))
246 AND ( (Recinfo.attribute6 = X_Attribute6)
247 OR ( (Recinfo.attribute6 IS NULL)
248 AND (X_Attribute6 IS NULL)))
249 AND ( (Recinfo.attribute7 = X_Attribute7)
250 OR ( (Recinfo.attribute7 IS NULL)
254 AND (X_Attribute8 IS NULL)))
251 AND (X_Attribute7 IS NULL)))
252 AND ( (Recinfo.attribute8 = X_Attribute8)
253 OR ( (Recinfo.attribute8 IS NULL)
255 AND ( (Recinfo.attribute9 = X_Attribute9)
256 OR ( (Recinfo.attribute9 IS NULL)
257 AND (X_Attribute9 IS NULL)))
258 AND ( (Recinfo.attribute10 = X_Attribute10)
259 OR ( (Recinfo.attribute10 IS NULL)
260 AND (X_Attribute10 IS NULL)))
261 AND ( (Recinfo.attribute11 = X_Attribute11)
262 OR ( (Recinfo.attribute11 IS NULL)
263 AND (X_Attribute11 IS NULL)))
264 AND ( (Recinfo.attribute12 = X_Attribute12)
265 OR ( (Recinfo.attribute12 IS NULL)
266 AND (X_Attribute12 IS NULL)))
267 AND ( (Recinfo.attribute13 = X_Attribute13)
268 OR ( (Recinfo.attribute13 IS NULL)
269 AND (X_Attribute13 IS NULL)))
270 AND ( (Recinfo.attribute14 = X_Attribute14)
271 OR ( (Recinfo.attribute14 IS NULL)
272 AND (X_Attribute14 IS NULL)))
273 AND ( (Recinfo.attribute15 = X_Attribute15)
274 OR ( (Recinfo.attribute15 IS NULL)
275 AND (X_Attribute15 IS NULL)))
276 AND ( (Recinfo.end_item_unit_number = X_end_item_unit_number)
277 OR ( (Recinfo.end_item_unit_number IS NULL)
278 AND (X_end_item_unit_number IS NULL)))
279 AND ( (Recinfo.schedule_group_id = X_Schedule_Group_Id)
280 OR ( (Recinfo.schedule_group_id IS NULL)
281 AND (X_schedule_group_id IS NULL)))
282 AND ( (Recinfo.build_sequence = X_build_sequence)
283 OR ( (Recinfo.build_sequence IS NULL)
284 AND (X_Build_Sequence IS NULL)))
285 AND ( (Recinfo.line_id = X_line_id)
286 OR ( (Recinfo.line_id IS NULL)
287 AND (X_line_id IS NULL)))
288 AND ( (Recinfo.Project_Id = X_Project_Id)
289 OR ( (Recinfo.Project_id IS NULL)
290 AND (X_project_id IS NULL)))
291 AND ( (Recinfo.Task_Id = X_task_Id)
292 OR ( (Recinfo.Task_id IS NULL)
293 AND (X_task_id IS NULL)))
294 AND ( (Recinfo.priority = X_priority)
295 OR ( (Recinfo.priority IS NULL)
296 AND (X_priority IS NULL)))
297 AND ( (Recinfo.due_date = X_due_date)
298 OR ( (Recinfo.due_date IS NULL)
299 AND (X_due_date IS NULL)))
300 AND ( (Recinfo.due_date_penalty = X_due_date_penalty)
301 OR ( (Recinfo.due_date_penalty IS NULL)
302 AND (X_due_date_penalty IS NULL)))
303 AND ( (Recinfo.due_date_tolerance = X_due_date_tolerance)
304 OR ( (Recinfo.due_date_tolerance IS NULL)
305 AND (X_due_date_tolerance IS NULL)))
306 AND ( (Recinfo.requested_start_date = X_requested_start_date)
307 OR ( (Recinfo.requested_start_date IS NULL)
308 AND (X_requested_start_date IS NULL)))
309 AND ( (Recinfo.serialization_start_op = X_serialization_start_op)
310 OR ( (Recinfo.serialization_start_op IS NULL)
311 AND (X_serialization_start_op IS NULL)))
312 /* AND ( (Recinfo.Project_costed = X_Project_Costed)
313 OR ( (Recinfo.Project_costed IS NULL)
314 AND (X_project_costed IS NULL)))*/
315 ) then
316 return;
317 else
318 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
319 APP_EXCEPTION.Raise_Exception;
320 end if;
321 else
322 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
323 APP_EXCEPTION.Raise_Exception;
324 end if;
325 END Lock_Row;
326
327 END WIP_JOBS_LOCK_ROW;
[Home] [Help]