1 PACKAGE BODY PO_ASL_ATTRIBUTES_THS2 as
2 /* $Header: POXA7LSB.pls 115.8 2003/08/21 09:42:49 tmanda ship $ */
3
4 /*=============================================================================
5
6 PROCEDURE NAME: lock_row()
7
8 ===============================================================================*/
9 procedure lock_row(
10 x_row_id VARCHAR2,
11 x_asl_id NUMBER,
12 x_using_organization_id NUMBER,
13 x_document_sourcing_method VARCHAR2,
14 x_release_generation_method VARCHAR2,
15 x_purchasing_unit_of_measure VARCHAR2,
16 x_enable_plan_schedule_flag VARCHAR2,
17 x_enable_ship_schedule_flag VARCHAR2,
18 x_plan_schedule_type VARCHAR2,
19 x_ship_schedule_type VARCHAR2,
20 x_plan_bucket_pattern_id NUMBER,
21 x_ship_bucket_pattern_id NUMBER,
22 x_enable_autoschedule_flag VARCHAR2,
23 x_scheduler_id NUMBER,
24 x_enable_authorizations_flag VARCHAR2,
25 x_vendor_id NUMBER,
26 x_vendor_site_id NUMBER,
27 x_item_id NUMBER,
28 x_category_id NUMBER,
29 x_attribute_category VARCHAR2,
30 x_attribute1 VARCHAR2,
31 x_attribute2 VARCHAR2,
32 x_attribute3 VARCHAR2,
33 x_attribute4 VARCHAR2,
34 x_attribute5 VARCHAR2,
35 x_attribute6 VARCHAR2,
36 x_attribute7 VARCHAR2,
37 x_attribute8 VARCHAR2,
38 x_attribute9 VARCHAR2,
39 x_attribute10 VARCHAR2,
40 x_attribute11 VARCHAR2,
41 x_attribute12 VARCHAR2,
42 x_attribute13 VARCHAR2,
43 x_attribute14 VARCHAR2,
44 x_attribute15 VARCHAR2,
45 x_price_update_tolerance NUMBER,
46 x_processing_lead_time NUMBER,
47 x_delivery_calendar VARCHAR2,
48 x_min_order_qty NUMBER,
49 x_fixed_lot_multiple NUMBER,
50 x_country_of_origin_code VARCHAR2,
51 /* VMI FPH START */
52 x_enable_vmi_flag VARCHAR2,
53 x_vmi_min_qty NUMBER,
54 x_vmi_max_qty NUMBER,
55 x_enable_vmi_auto_repl_flag VARCHAR2,
56 x_vmi_replenishment_approval VARCHAR2,
57 /* VMI FPH END */
58 /* CONSSUP FPI START */
59 x_consigned_from_supplier_flag VARCHAR2,
60 x_consigned_billing_cycle NUMBER ,
61 x_last_billing_date DATE,
62 /* CONSSUP FPI END */
63 /*FPJ START*/
64 x_replenishment_method NUMBER,
65 x_vmi_min_days NUMBER,
66 x_vmi_max_days NUMBER,
67 x_fixed_order_quantity NUMBER,
68 x_forecast_horizon NUMBER,
69 x_consume_on_aging_flag VARCHAR2,
70 x_aging_period NUMBER
71 /*FPJ END*/
72 ) is
73
74
75 cursor asl_row is SELECT *
76 FROM po_asl_attributes
77 WHERE rowid = x_row_id
78 FOR UPDATE of asl_id NOWAIT;
79
80 recinfo asl_row%rowtype;
81
82 begin
83
84 OPEN asl_row;
85 FETCH asl_row INTO recinfo;
86 if (asl_row%notfound) then
87 CLOSE asl_row;
88 fnd_message.set_name('FND','FORM_RECORD_DELETED');
89 app_exception.raise_exception;
90 end if;
91 CLOSE asl_row;
92
93 if (
94 (recinfo.asl_id = x_asl_id)
95 AND (recinfo.using_organization_id = x_using_organization_id)
96 AND ((recinfo.document_sourcing_method = x_document_sourcing_method) OR
97 ((recinfo.document_sourcing_method is null) AND
98 (x_document_sourcing_method is null)))
99 AND ((recinfo.release_generation_method = x_release_generation_method) OR
100 ((recinfo.release_generation_method is null) AND
101 (x_release_generation_method is null)))
102 AND ((recinfo.purchasing_unit_of_measure = x_purchasing_unit_of_measure) OR
103 ((recinfo.purchasing_unit_of_measure is null) AND
104 (x_purchasing_unit_of_measure is null)))
105 AND ((recinfo.enable_plan_schedule_flag = x_enable_plan_schedule_flag) OR
106 ((recinfo.enable_plan_schedule_flag is null) AND
107 (x_enable_plan_schedule_flag is null)))
108 AND ((recinfo.enable_ship_schedule_flag = x_enable_ship_schedule_flag) OR
109 ((recinfo.enable_ship_schedule_flag is null) AND
110 (x_enable_ship_schedule_flag is null)))
111 AND ((recinfo.plan_schedule_type = x_plan_schedule_type) OR
112 ((recinfo.plan_schedule_type is null) AND
113 (x_plan_schedule_type is null)))
114 AND ((recinfo.ship_schedule_type = x_ship_schedule_type) OR
115 ((recinfo.ship_schedule_type is null) AND
116 (x_ship_schedule_type is null)))
117 AND ((recinfo.plan_bucket_pattern_id = x_plan_bucket_pattern_id) OR
118 ((recinfo.plan_bucket_pattern_id is null) AND
119 (x_plan_bucket_pattern_id is null)))
120 AND ((recinfo.ship_bucket_pattern_id = x_ship_bucket_pattern_id) OR
121 ((recinfo.ship_bucket_pattern_id is null) AND
122 (x_ship_bucket_pattern_id is null)))
123 AND ((recinfo.enable_autoschedule_flag = x_enable_autoschedule_flag) OR
124 ((recinfo.enable_autoschedule_flag is null) AND
125 (x_enable_autoschedule_flag is null)))
126 AND ((recinfo.scheduler_id = x_scheduler_id) OR
127 ((recinfo.scheduler_id is null) AND
128 (x_scheduler_id is null)))
129 AND ((recinfo.enable_authorizations_flag = x_enable_authorizations_flag) OR
130 ((recinfo.enable_authorizations_flag is null) AND
131 (x_enable_authorizations_flag is null)))
132 AND ((recinfo.vendor_id = x_vendor_id) OR
133 ((recinfo.vendor_id is null) AND
134 (x_vendor_id is null)))
135 AND ((recinfo.vendor_site_id = x_vendor_site_id) OR
136 ((recinfo.vendor_site_id is null) AND
137 (x_vendor_site_id is null)))
138 AND ((recinfo.item_id = x_item_id) OR
139 ((recinfo.item_id is null) AND
140 (x_item_id is null)))
141 AND ((recinfo.category_id = x_category_id) OR
142 ((recinfo.category_id is null) AND
143 (x_category_id is null)))
144 AND ((recinfo.attribute_category = x_attribute_category) OR
145 ((recinfo.attribute_category is null) AND
146 (x_attribute_category is null)))
147 AND ((recinfo.attribute1 = x_attribute1) OR
148 ((recinfo.attribute1 is null) AND
149 (x_attribute1 is null)))
150 AND ((recinfo.attribute2 = x_attribute2) OR
151 ((recinfo.attribute2 is null) AND
152 (x_attribute2 is null)))
153 AND ((recinfo.attribute3 = x_attribute3) OR
154 ((recinfo.attribute3 is null) AND
155 (x_attribute3 is null)))
156 AND ((recinfo.attribute4 = x_attribute4) OR
157 ((recinfo.attribute4 is null) AND
158 (x_attribute4 is null)))
159 AND ((recinfo.attribute5 = x_attribute5) OR
160 ((recinfo.attribute5 is null) AND
161 (x_attribute5 is null)))
162 AND ((recinfo.attribute6 = x_attribute6) OR
163 ((recinfo.attribute6 is null) AND
164 (x_attribute6 is null)))
165 AND ((recinfo.attribute7 = x_attribute7) OR
166 ((recinfo.attribute7 is null) AND
167 (x_attribute7 is null)))
168 AND ((recinfo.attribute8 = x_attribute8) OR
169 ((recinfo.attribute8 is null) AND
170 (x_attribute8 is null)))
171 AND ((recinfo.attribute9 = x_attribute9) OR
172 ((recinfo.attribute9 is null) AND
173 (x_attribute9 is null)))
174 AND ((recinfo.attribute10 = x_attribute10) OR
175 ((recinfo.attribute10 is null) AND
176 (x_attribute10 is null)))
177 AND ((recinfo.attribute11 = x_attribute11) OR
178 ((recinfo.attribute11 is null) AND
179 (x_attribute11 is null)))
180 AND ((recinfo.attribute12 = x_attribute12) OR
181 ((recinfo.attribute12 is null) AND
182 (x_attribute12 is null)))
183 AND ((recinfo.attribute13 = x_attribute13) OR
184 ((recinfo.attribute13 is null) AND
185 (x_attribute13 is null)))
186 AND ((recinfo.attribute14 = x_attribute14) OR
187 ((recinfo.attribute14 is null) AND
188 (x_attribute14 is null)))
189 AND ((recinfo.attribute15 = x_attribute15) OR
190 ((recinfo.attribute15 is null) AND
191 (x_attribute15 is null)))
192 AND ((recinfo.price_update_tolerance = x_price_update_tolerance) OR
193 ((recinfo.price_update_tolerance is null) AND
194 (x_price_update_tolerance is null)))
195 AND ((recinfo.processing_lead_time = x_processing_lead_time) OR
196 ((recinfo.processing_lead_time is null) AND
197 (x_processing_lead_time is null)))
198 AND ((recinfo.delivery_calendar = x_delivery_calendar) OR
199 ((recinfo.delivery_calendar is null) AND
200 (x_delivery_calendar is null)))
201 AND ((recinfo.min_order_qty = x_min_order_qty) OR
202 ((recinfo.min_order_qty is null) AND
203 (x_min_order_qty is null)))
204 AND ((recinfo.fixed_lot_multiple = x_fixed_lot_multiple) OR
205 ((recinfo.fixed_lot_multiple is null) AND
206 (x_fixed_lot_multiple is null)))
207 AND ((recinfo.country_of_origin_code = x_country_of_origin_code) OR
208 ((recinfo.country_of_origin_code is null) AND
209 (x_country_of_origin_code is null)))
210 /* VMI FPH START*/
211 AND ((recinfo.enable_vmi_flag = x_enable_vmi_flag) OR
212 ((recinfo.enable_vmi_flag is null) AND
213 (x_enable_vmi_flag is null)))
214 AND ((recinfo.vmi_min_qty = x_vmi_min_qty) OR
215 ((recinfo.vmi_min_qty is null) AND
216 (x_vmi_min_qty is null)))
217 AND ((recinfo.vmi_max_qty = x_vmi_max_qty) OR
218 ((recinfo.vmi_max_qty is null) AND
219 (x_vmi_max_qty is null)))
220 AND ((recinfo.enable_vmi_auto_replenish_flag = x_enable_vmi_auto_repl_flag) OR
221 ((recinfo.enable_vmi_auto_replenish_flag is null) AND
222 (x_enable_vmi_auto_repl_flag is null)))
223 AND ((recinfo.vmi_replenishment_approval = x_vmi_replenishment_approval) OR
224 ((recinfo.vmi_replenishment_approval is null) AND
225 (x_vmi_replenishment_approval is null)))
226 /* VMI FPH END*/
227 /* CONSSUP FPI START */
228
229 AND ((recinfo.consigned_from_supplier_flag = x_consigned_from_supplier_flag) OR
230 ((recinfo.consigned_from_supplier_flag is null) AND
231 (x_consigned_from_supplier_flag is null)))
232 AND ((recinfo.consigned_billing_cycle = x_consigned_billing_cycle ) OR
233 ((recinfo.consigned_billing_cycle is null) AND
234 (x_consigned_billing_cycle is null)))
235 AND ((TRUNC(recinfo.last_billing_date) = TRUNC(x_last_billing_date)) OR
236 ((recinfo.last_billing_date is null) AND
237 (x_last_billing_date is null)))
238 /* CONSSUP FPI START */
239 /*FPJ START */
240 AND ((recinfo.replenishment_method = x_replenishment_method) OR
241 ((recinfo.replenishment_method is null) AND
242 (x_replenishment_method is null)))
243 AND ((recinfo.vmi_min_days = x_vmi_min_days) OR
244 ((recinfo.vmi_min_days is null) AND
245 (x_vmi_min_days is null)))
246 AND ((recinfo.vmi_max_days = x_vmi_max_days) OR
247 ((recinfo.vmi_max_days is null) AND
248 (x_vmi_max_days is null)))
249 AND ((recinfo.fixed_order_quantity = x_fixed_order_quantity) OR
250 ((recinfo.fixed_order_quantity is null) AND
251 (x_fixed_order_quantity is null)))
252 AND ((recinfo.forecast_horizon = x_forecast_horizon) OR
253 ((recinfo.forecast_horizon is null) AND
254 (x_forecast_horizon is null)))
255 AND ((recinfo.consume_on_aging_flag = x_consume_on_aging_flag) OR
256 ((recinfo.consume_on_aging_flag is null) AND
257 (x_consume_on_aging_flag is null)))
258 AND ((recinfo.aging_period = x_aging_period) OR
259 ((recinfo.aging_period is null) AND
260 (x_aging_period is null)))
261
262 ) then
263
264
265 return;
266 else
267 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
268 app_exception.raise_exception;
269 end if;
270
271 end lock_row;
272
273 END PO_ASL_ATTRIBUTES_THS2;