1 PACKAGE MSC_ATP_DB_UTILS AS
2 /* $Header: MSCDATPS.pls 120.2.12010000.2 2008/09/22 09:59:17 sbnaik ship $ */
3
4
5 SYS_YES CONSTANT NUMBER := 1;
6 SYS_NO CONSTANT NUMBER := 2;
7 REQUEST_MODE CONSTANT NUMBER := 1;
8 RESULTS_MODE CONSTANT NUMBER := 2;
9
10
11 --bug 3766179
12 TYPE Supply_Rec_Typ is RECORD (
13 instance_id NUMBER,
14 plan_id NUMBER,
15 inventory_item_id NUMBER,
16 organization_id NUMBER,
17 schedule_date DATE,
18 order_quantity NUMBER,
19 supplier_id NUMBER,
20 supplier_site_id NUMBER,
21 demand_class VARCHAR2(80),
22 source_organization_id NUMBER,
23 source_sr_instance_id NUMBER,
24 process_seq_id NUMBER,
25 refresh_number VARCHAR2(80),
26 shipping_cal_code VARCHAR2(20), -- For ship_rec_cal
27 receiving_cal_code VARCHAR2(20), -- For ship_rec_cal
28 intransit_cal_code VARCHAR2(20), -- For ship_rec_cal
29 new_ship_date DATE, -- For ship_rec_cal
30 new_dock_date DATE, -- For ship_rec_cal
31 start_date DATE, -- Bug 3241766
32 order_date DATE, -- Bug 3241766
33 ship_method VARCHAR2(30), -- For ship_rec_cal
34 transaction_id NUMBER,
35 return_status VARCHAR2(10),
36 request_item_id NUMBER,
37 atf_date DATE,
38 Supply_type Number,
39 disposition_status_type Number,
40 firm_planned_type Number,
41 record_source Number,
42 disposition_id Number,
43 intransit_lead_time NUMBER --4127630
44
45 );
46
47
48 PROCEDURE Add_Mat_Demand(
49 p_atp_rec IN MRP_ATP_PVT.AtpRec,
50 p_plan_id IN NUMBER,
51 p_dc_flag IN NUMBER,
52 x_demand_id OUT NoCopy NUMBER
53 );
54
55
56 PROCEDURE Add_Pegging(
57 p_pegging_rec IN mrp_atp_details_temp%ROWTYPE,
58 x_pegging_id OUT NoCopy number
59 );
60
61
62 PROCEDURE Add_Planned_Order(
63 p_instance_id IN NUMBER,
64 p_plan_id IN NUMBER,
65 p_inventory_item_id IN NUMBER,
66 p_organization_id IN NUMBER,
67 p_schedule_date IN DATE,
68 p_order_quantity IN NUMBER,
69 p_supplier_id IN NUMBER,
70 p_supplier_site_id IN NUMBER,
71 p_demand_class IN VARCHAR2,
72 -- rajjain 02/19/2003 Bug 2788302 Begin
73 p_source_organization_id IN NUMBER,
74 p_source_sr_instance_id IN NUMBER,
75 p_process_seq_id IN NUMBER,
76 -- rajjain 02/19/2003 Bug 2788302 End
77 p_refresh_number IN VARCHAR2, -- for summary enhancement
78 p_shipping_cal_code IN VARCHAR2, -- For ship_rec_cal
79 p_receiving_cal_code IN VARCHAR2, -- For ship_rec_cal
80 p_intransit_cal_code IN VARCHAR2, -- For ship_rec_cal
81 p_new_ship_date IN DATE, -- For ship_rec_cal
82 p_new_dock_date IN DATE, -- For ship_rec_cal
83 p_start_date IN DATE, -- Bug 3241766
84 p_order_date IN DATE, -- Bug 3241766
85 p_ship_method IN VARCHAR2, -- For ship_rec_cal
86 x_transaction_id OUT NoCopy NUMBER,
87 x_return_status OUT NoCopy VARCHAR2,
88 p_intransit_lead_time IN NUMBER, --4127630
89 p_request_item_id IN NUMBER := NULL, -- for time_phased_atp
90 p_atf_date IN DATE := NULL -- for time_phased_atp
91
92 );
93
94
95 PROCEDURE Add_Resource_Demand(
96 p_instance_id IN NUMBER,
97 p_plan_id IN NUMBER,
98 p_supply_id IN NUMBER,
99 p_organization_id IN NUMBER,
100 p_resource_id IN NUMBER,
101 p_department_id IN NUMBER,
102 -- Bug 3348095
103 -- Now both start and end dates will be stored for
104 -- ATP created resource requirements.
105 p_start_date IN DATE,
106 p_end_date IN DATE,
107 -- End Bug 3348095
108 p_resource_hours IN NUMBER,
109 p_unadj_resource_hours IN NUMBER, --5093604
110 p_touch_time IN NUMBER, --5093604
111 p_std_op_code IN VARCHAR2,
112 p_resource_cap_hrs IN NUMBER,
113 p_item_id IN NUMBER, -- Need to store assembly_item_id CTO ODR
114 p_basis_type IN NUMBER, -- Need to store basis_type CTO ODR
115 p_op_seq_num IN NUMBER, -- Need to store op_seq_num CTO ODR
116 p_refresh_number IN VARCHAR2, -- for summary enhancement
117 x_transaction_id OUT NoCopy NUMBER,
118 x_return_status OUT NoCopy VARCHAR2
119 );
120
121
122 PROCEDURE Delete_Pegging(
123 p_pegging_id IN number
124 );
125
126
127 PROCEDURE Delete_Row(
128 p_identifier IN NUMBER,
129 p_config_line_id IN NUMBER,
130 p_plan_id IN NUMBER,
131 p_instance_id IN NUMBER,
132 p_refresh_number IN NUMBER,
133 -- Bug 2831298 Ensure that the refresh_number is updated
134 p_order_number IN NUMBER, -- Bug 2840734 : krajan
135 p_time_phased_atp IN VARCHAR2, -- For time_phased_atp
136 p_ato_model_line_id IN number,
137 p_demand_source_type IN Number, --cmro
138 p_source_organization_Id IN NUMBER, --Bug 7118988
139 x_demand_id OUT NoCopy MRP_ATP_PUB.Number_Arr,
140 x_inv_item_id OUT NoCopy MRP_ATP_PUB.Number_Arr,
141 x_copy_demand_id OUT NoCopy MRP_ATP_PUB.Number_Arr, -- For summary enhancement
142 x_atp_peg_items OUT NoCopy MRP_ATP_PUB.Number_Arr,
143 x_atp_peg_demands OUT NoCopy MRP_ATP_PUB.Number_Arr,
144 x_atp_peg_supplies OUT NoCopy MRP_ATP_PUB.Number_Arr,
145 x_atp_peg_res_reqs OUT NoCopy MRP_ATP_PUB.Number_Arr,
146 x_demand_instance_id OUT NoCopy MRP_ATP_PUB.Number_Arr, --Bug 3629191
147 x_supply_instance_id OUT NoCopy MRP_ATP_PUB.Number_Arr, --Bug 3629191
148 x_res_instance_id OUT NoCopy MRP_ATP_PUB.Number_Arr, --Bug 3629191
149 x_ods_cto_demand_ids OUT NoCopy MRP_ATP_PUB.Number_Arr, --3720018, added for support of rescheduling in ODS
150 x_ods_cto_inv_item_ids OUT NoCopy MRP_ATP_PUB.Number_Arr, --3720018, added for support of rescheduling in ODS
151 x_ods_atp_refresh_no OUT NoCopy MRP_ATP_PUB.Number_Arr,
152 x_ods_cto_atp_refresh_no OUT NoCopy MRP_ATP_PUB.Number_Arr
153 -- End CTO ODR and Simplified Pegging
154
155 );
156
157
158 PROCEDURE Remove_Invalid_SD_Rec(
159 p_identifier IN NUMBER,
160 p_instance_id IN NUMBER,
161 p_plan_id IN NUMBER,
162 p_mode IN NUMBER,
163 p_dc_flag IN NUMBER,
164 x_return_status OUT NoCopy VARCHAR2
165 );
166
167
168 PROCEDURE Update_Pegging(
169 p_pegging_id IN NUMBER,
170 p_date IN DATE,
171 p_quantity IN NUMBER
172 );
173
174
175 PROCEDURE Update_Planned_Order(
176 p_pegging_id IN NUMBER,
177 p_plan_id IN NUMBER,
178 p_date IN DATE,
179 p_quantity IN NUMBER,
180 p_supplier_id IN NUMBER,
181 p_supplier_site_id IN NUMBER,
182 p_dock_date IN Date,
183 p_ship_date IN DATE, -- Bug 3241766
184 p_start_date IN DATE, -- Bug 3241766
185 p_order_date IN DATE, -- Bug 3241766
186 p_mem_item_id IN NUMBER, -- Bug 3293163
187 p_pf_item_id IN NUMBER,
188 p_mode IN NUMBER := MSC_ATP_PVT.UNDO,
189 p_uom_conv_rate IN NUMBER := NULL
190 );
191
192
193 PROCEDURE Update_SD_Date(
194 p_identifier IN NUMBER,
195 p_instance_id IN NUMBER,
196 p_supply_demand_date IN DATE,
197 p_plan_id IN NUMBER,
198 p_supply_demand_qty IN NUMBER,
199 p_dc_flag IN NUMBER,
200 p_old_demand_date IN DATE,
201 p_old_demand_qty IN NUMBER,
202 p_dmd_satisfied_date IN DATE, -- bug 2795053-reopen
203 p_sd_date_quantity IN NUMBER, -- For time_phased_atp
204 p_atf_date IN DATE, -- For time_phased_atp
205 p_atf_date_quantity IN NUMBER, -- For time_phased_atp
206 p_sch_arrival_date IN DATE, -- For ship_rec_cal
207 p_order_date_type IN NUMBER, -- For ship_rec_cal
208 p_lat_date IN DATE, -- For ship_rec_cal
209 p_ship_set_name IN VARCHAR2, --plan by request date
210 p_arrival_set_name IN VARCHAR2, --plan by request date
211 p_override_flag IN VARCHAR2, --plan by request date
212 p_request_arrival_date IN DATE, --plan by request date
213 p_bkwd_pass_atf_date_qty IN NUMBER, -- For time_phased_atp bug3397904
214 p_atp_rec IN MRP_ATP_PVT.AtpRec := NULL -- For bug 3226083
215 );
216
217
218
219 -- NGOEL 7/26/2001, Bug 1661545, if scheduling was unsuccessful, make sure that old demand record is
220 -- preserved back, as it was updated to 0 in the begining in case of reschedule in PDS.
221
222 -- RAJJAIN 11/01/2002, Now schedule procedure passes reference to del_demand_ids array to this
223 -- procedure
224
225 PROCEDURE Undo_Delete_Row(
226 p_identifiers IN MRP_ATP_PUB.Number_Arr,
227 p_plan_ids IN MRP_ATP_PUB.Number_Arr,
228 p_instance_id IN NUMBER,
229 p_del_demand_ids IN MRP_ATP_PUB.Number_Arr,
230 p_inv_item_ids IN MRP_ATP_PUB.Number_Arr,
231 p_copy_demand_ids IN MRP_ATP_PUB.Number_Arr, -- For summary enhancement
232 p_copy_plan_ids IN MRP_ATP_PUB.Number_Arr, -- For summary enhancement
233 p_time_phased_set IN VARCHAR2, -- For time_phased_atp
234 -- CTO ODR and Simplified Pegging
235 p_del_atp_peg_items IN MRP_ATP_PUB.Number_Arr,
236 p_del_atp_peg_demands IN MRP_ATP_PUB.Number_Arr,
237 p_del_atp_peg_supplies IN MRP_ATP_PUB.Number_Arr,
238 p_del_atp_peg_res_reqs IN MRP_ATP_PUB.Number_Arr,
239 p_demand_source_type IN MRP_ATP_PUB.Number_Arr, --cmro
240 p_atp_peg_demands_plan_ids IN MRP_ATP_PUB.Number_Arr, --Bug 3629191
241 p_atp_peg_supplies_plan_ids IN MRP_ATP_PUB.Number_Arr, --Bug 3629191
242 p_atp_peg_res_reqs_plan_ids IN MRP_ATP_PUB.Number_Arr, --Bug 3629191
243 p_del_ods_demand_ids IN MRP_ATP_PUB.Number_Arr, --3720018, added for support of rescheduling in ODS
244 p_del_ods_inv_item_ids IN MRP_ATP_PUB.Number_Arr, --3720018, added for support of rescheduling in ODS
245 p_del_ods_demand_src_type IN MRP_ATP_PUB.Number_Arr, --3720018, added for support of rescheduling in ODS
246 p_del_ods_cto_demand_ids IN MRP_ATP_PUB.Number_Arr, --3720018, added for support of rescheduling in ODS
247 p_del_ods_cto_inv_item_ids IN MRP_ATP_PUB.Number_Arr, --3720018, added for support of rescheduling in ODS
248 p_del_ods_cto_dem_src_type IN MRP_ATP_PUB.Number_Arr, --3720018, added for support of rescheduling in ODS
249 p_del_ods_atp_refresh_no IN MRP_ATP_PUB.Number_Arr, --3720018, added for support of rescheduling in ODS
250 p_del_ods_cto_atp_refresh_no IN MRP_ATP_PUB.Number_Arr --3720018, added for support of rescheduling in ODS
251 -- End CTO ODR and Simplified Pegging
252 );
253
254 PROCEDURE DELETE_SUMMARY_ROW (p_identifier IN NUMBER,
255 p_plan_id IN NUMBER,
256 p_instance_id IN NUMBER,
257 p_demand_source_type IN Number --cmro
258 );
259
260 -- RAJJAIN 11/05/2002 Now we pass the reference to p_identifiers and p_plan_ids array
261 -- and do bulk update in this procedure
262
263
264
265 /* Bug 2738280. Change the specification of these procedures.
266 PROCEDURE UPDATE_PLAN_SUMMARY_ROW (p_identifier IN NUMBER,
267 p_plan_id IN NUMBER,
268 p_instance_id IN NUMBER);
269
270 PROCEDURE UNDO_PLAN_SUMMARY_ROW (p_identifiers IN MRP_ATP_PUB.Number_Arr,
271 p_plan_ids IN MRP_ATP_PUB.Number_Arr,
272 p_instance_id IN NUMBER);
273 */
274 PROCEDURE UPDATE_PLAN_SUMMARY_ROW (p_inventory_item_id IN MRP_ATP_PUB.Number_Arr,
275 p_old_demand_date IN MRP_ATP_PUB.Date_Arr,
276 p_old_demand_quantity IN MRP_ATP_PUB.Number_Arr,
277 p_organization_id IN MRP_ATP_PUB.Number_Arr,
278 p_plan_id IN NUMBER,
279 p_instance_id IN NUMBER);
280
281 PROCEDURE UNDO_PLAN_SUMMARY_ROW (p_inventory_item_id IN MRP_ATP_PUB.Number_Arr,
282 p_using_assembly_demand_date IN MRP_ATP_PUB.Date_Arr,
283 p_using_requirement_quantity IN MRP_ATP_PUB.Number_Arr,
284 p_organization_id IN MRP_ATP_PUB.Number_Arr,
285 p_plan_id IN MRP_ATP_PUB.Number_Arr,
286 p_instance_id IN NUMBER);
287
288
289
290 PROCEDURE INSERT_SUMMARY_SD_ROW( p_plan_id IN NUMBER,
291 p_instance_id IN NUMBER,
292 p_organization_id IN NUMBER,
293 p_inventory_item_id IN NUMBER,
294 p_date IN DATE,
295 p_quantity IN NUMBER,
296 p_demand_class IN VARCHAR2);
297
298 /* New procedure for Allocated ATP Based on Planning Details for Agilent */
299
300 PROCEDURE Add_Stealing_Supply_Details (
301 p_plan_id IN NUMBER,
302 p_identifier IN NUMBER,
303 p_inventory_item_id IN NUMBER,
304 p_organization_id IN NUMBER,
305 p_sr_instance_id IN NUMBER,
306 p_stealing_quantity IN NUMBER,
307 p_stealing_demand_class IN VARCHAR2,
308 p_stolen_demand_class IN VARCHAR2,
309 p_ship_date IN DATE,
310 p_transaction_id OUT NoCopy NUMBER,
311 p_refresh_number IN NUMBER,
312 p_ato_model_line_id IN NUMBER,
313 p_demand_source_type IN Number, --cmro
314 --bug3684383
315 p_order_number IN Number
316 ); -- For summary enhancement
317
318
319 PROCEDURE Remove_Invalid_Future_SD(
320 p_future_pegging_tab IN MRP_ATP_PUB.Number_Arr
321 );
322
323 -- supply/demand perf enh
324 PROCEDURE move_SD_temp_into_mrp_details(
325 p_pegging_id IN NUMBER,
326 p_end_pegging_id IN NUMBER);
327
328 PROCEDURE Clear_SD_Details_Temp;
329
330 -- for summary enhancement
331 PROCEDURE Delete_Copy_Demand (
332 p_copy_demand_ids IN MRP_ATP_PUB.Number_Arr,
333 p_copy_plan_ids IN MRP_ATP_PUB.Number_Arr,
334 p_time_phased_set IN VARCHAR2,
335 x_return_status OUT NOCOPY VARCHAR2
336 );
337
338 -- New procedure added for ship_rec_cal project
339 PROCEDURE Flush_Data_In_Pds(
340 p_ship_arrival_date_rec IN MSC_ATP_PVT.ship_arrival_date_rec_typ,
341 x_return_status OUT NOCOPY VARCHAR2
342 );
343 PROCEDURE Flush_Data_In_Ods(
344 p_ship_arrival_date_rec IN MSC_ATP_PVT.ship_arrival_date_rec_typ,
345 x_return_status OUT NOCOPY VARCHAR2
346 );
347
348 --bug 3766179
349 PROCEDURE Add_Supplies ( p_supply_rec_type IN OUT NOCOPY MSC_ATP_DB_UTILS.supply_rec_typ);
350
351 --3720018, new procedure to call delete_row for set or request level.
352 Procedure call_delete_row (
353 p_instance_id IN NUMBER,
354 p_atp_table IN MRP_ATP_PUB.ATP_Rec_Typ,
355 p_refresh_number IN NUMBER,
356 x_delete_atp_rec OUT NoCopy MSC_ATP_PVT.DELETE_ATP_REC,
357 x_return_status OUT NoCopy VARCHAR2
358 );
359
360 END MSC_ATP_DB_UTILS;
[Home] [Help]