[Home] [Help]
PACKAGE: APPS.MSC_ATP_PVT
Source
1 PACKAGE MSC_ATP_PVT AUTHID CURRENT_USER AS
2 /* $Header: MSCGATPS.pls 120.12 2011/12/06 09:59:56 vjuluri ship $ */
3
4 INFINITE_NUMBER CONSTANT NUMBER := 1.0e+10;
5 -- single level stealing
6 SL_STEALING CONSTANT NUMBER := -12345;
7 -- order type
8 ATP CONSTANT INTEGER := 100;
9 SOURCE11i CONSTANT NUMBER := 3;
10
11 --3720018
12 -- This record structure will be used to store records at request level.
13 -- These will be used to call remove_invalid_sd_rec() and delete_row()
14 -- procedures at request level in case of ATP_Inquiry
15
16 TYPE REMOVE_REQUEST_LEVEL_REC IS RECORD (
17 REMOVE_PEGGING_ID_REQUEST MRP_ATP_PUB.NUMBER_ARR := MRP_ATP_PUB.number_arr(),
18 REMOVE_PLAN_ID_REQUEST MRP_ATP_PUB.NUMBER_ARR := MRP_ATP_PUB.number_arr(),
19 REMOVE_DC_ATP_FLAG_REQUEST MRP_ATP_PUB.NUMBER_ARR := MRP_ATP_PUB.number_arr()
20 );
21
22 --3720018, new record type for returning records from call_delete_row .
23 TYPE DELETE_ATP_REC IS RECORD (
24 time_phased_set VARCHAR2(1),
25 error_code MRP_ATP_PUB.number_arr := MRP_ATP_PUB.number_arr(),
26 attribute_07 MRP_ATP_PUB.char30_arr := MRP_ATP_PUB.char30_arr(),
27 old_plan_id MRP_ATP_PUB.number_arr := MRP_ATP_PUB.number_arr(),
28 del_demand_ids MRP_ATP_PUB.number_arr := MRP_ATP_PUB.number_arr(),
29 del_inv_item_ids MRP_ATP_PUB.number_arr := MRP_ATP_PUB.number_arr(),
30 del_plan_ids MRP_ATP_PUB.number_arr := MRP_ATP_PUB.number_arr(),
31 del_identifiers MRP_ATP_PUB.number_arr := MRP_ATP_PUB.number_arr(),
32 del_demand_source_type MRP_ATP_PUB.number_arr := MRP_ATP_PUB.number_arr(),
33 del_copy_demand_ids mrp_atp_pub.number_arr := MRP_ATP_PUB.number_arr(),
34 del_copy_demand_plan_ids mrp_atp_pub.number_arr := MRP_ATP_PUB.number_arr(),
35 del_atp_peg_items MRP_ATP_PUB.Number_Arr := MRP_ATP_PUB.number_arr(),
36 del_atp_peg_demands MRP_ATP_PUB.Number_Arr := MRP_ATP_PUB.number_arr(),
37 del_atp_peg_supplies MRP_ATP_PUB.Number_Arr := MRP_ATP_PUB.number_arr(),
38 del_atp_peg_res_reqs MRP_ATP_PUB.Number_Arr := MRP_ATP_PUB.number_arr(),
39 atp_peg_demands_plan_ids MRP_ATP_PUB.Number_Arr := MRP_ATP_PUB.number_arr(),
40 atp_peg_supplies_plan_ids MRP_ATP_PUB.Number_Arr := MRP_ATP_PUB.number_arr(),
41 atp_peg_res_reqs_plan_ids MRP_ATP_PUB.Number_Arr := MRP_ATP_PUB.number_arr(),
42 off_demand_instance_id MRP_ATP_PUB.Number_Arr := MRP_ATP_PUB.number_arr(),
43 off_supply_instance_id MRP_ATP_PUB.Number_Arr := MRP_ATP_PUB.number_arr(),
44 off_res_instance_id MRP_ATP_PUB.Number_Arr := MRP_ATP_PUB.number_arr(),
45 del_ods_demand_ids MRP_ATP_PUB.Number_Arr := MRP_ATP_PUB.number_arr(), --3720018, added for support of rescheduling in ODS
46 del_ods_inv_item_ids MRP_ATP_PUB.Number_Arr := MRP_ATP_PUB.number_arr(), --3720018, added for support of rescheduling in ODS
47 del_ods_demand_src_type MRP_ATP_PUB.Number_Arr := MRP_ATP_PUB.number_arr(), --3720018, added for support of rescheduling in ODS
48 del_ods_cto_demand_ids MRP_ATP_PUB.Number_Arr := MRP_ATP_PUB.number_arr(), --3720018, added for support of rescheduling in ODS
49 del_ods_cto_inv_item_ids MRP_ATP_PUB.Number_Arr := MRP_ATP_PUB.number_arr(), --3720018, added for support of rescheduling in ODS
50 del_ods_cto_dem_src_type MRP_ATP_PUB.Number_Arr := MRP_ATP_PUB.number_arr(), --3720018, added for support of rescheduling in ODS
51 del_ods_atp_refresh_no MRP_ATP_PUB.Number_Arr := MRP_ATP_PUB.number_arr(), --3720018, added for support of rescheduling in ODS
52 del_ods_cto_atp_refresh_no MRP_ATP_PUB.Number_Arr := MRP_ATP_PUB.number_arr() --3720018, added for support of rescheduling in ODS
53 );
54 ----3720018
55
56 TYPE item_attribute_rec IS RECORD (
57 instance_id NUMBER,
58 organization_id NUMBER,
59 sr_inv_item_id NUMBER,
60 atp_flag varchar2(1),
61 atp_comp_flag varchar2(1),
62 pre_pro_lt number,
63 post_pro_lt number,
64 fixed_lt number,
65 variable_lt number,
66 substitution_window number,
67 create_supply_flag number,
68 dest_inv_item_id number,
69 item_name varchar2(40), -- Rewind to 40 Bug 2408159
70 atp_rule_id number, -- To accomodate Bug 1510853
71 rounding_control_type number, -- For Forward Stealing
72 cto_source_org_id number, -- For a CTO line item, stores the source org
73 unit_volume number,
74 unit_weight number,
75 volume_uom varchar2(3),
76 weight_uom varchar2(3),
77 uom_code VARCHAR2(3), -- added by rajjain 12/10/2002
78 inventory_item_id NUMBER, -- added by rajjain 12/10/2002
79 processing_lt number, -- processing lead time, full_lead_time.
80 --s_cto_rearch
81 parent_repl_ord_flag varchar2(1),
82 parent_bom_item_type number,
83 parent_comp_flag varchar2(1),
84 parent_atp_flag varchar2(1),
85 parent_item_id number,
86 replenish_to_ord_flag varchar2(1),
87 bom_item_type number,
88 base_item_id number,
89 parent_pegging_id number,
90 --atf_date date, -- For time_phased_atp
91 atf_days number, -- For time_phased_atp
92 product_family_id number, -- For time_phased_atp
93 --bug 3917625: Store plan id as well
94 plan_id number,
95 lowest_level_src number -- For ATP4drp
96 );
97 -- For Supplier Capacity and Lead Time (SCLT) changes.
98 -- New fields introduced are processing_lt.
99 /* ship_rec_cal
100 TYPE Supplier_Info_rec IS RECORD(
101 base_item_id number,
102 bom_item_type number,
103 rep_ord_flag varchar2(1));*/
104
105 TYPE org_attribute_rec IS RECORD(
106 instance_id NUMBER,
107 organization_id NUMBER,
108 default_atp_rule_id NUMBER,
109 cal_code VARCHAR2(14),
110 cal_exception_set_id NUMBER,
111 default_demand_class VARCHAR2(34),
112 org_code VARCHAR2(7),
113 org_type NUMBER, --OPM fix bug 2865389 (ssurendr)
114 network_scheduling_method NUMBER, --bug3601223
115 use_phantom_routings NUMBER --4570421
116 );
117 -- New record plan_info_rec defined for bug 2392456
118 TYPE plan_info_rec IS RECORD(
119 plan_id NUMBER,
120 plan_name VARCHAR2(10),
121 assignment_set_id NUMBER,
122 plan_start_date DATE, -- for future use
123 plan_cutoff_date DATE, -- for future use
124 summary_flag number, -- 24x7 atp
125 subst_flag number, -- 24x7
126 copy_plan_id number, -- 24x7 atp
127 sr_instance_id NUMBER,
128 organization_id NUMBER, -- Owning organization for plan.
129 curr_cutoff_date DATE,
130 optimized_plan NUMBER,-- 1 for constrained plan, 2 else -- 2859130
131 schedule_by_date_type NUMBER,--for identifying request type
132 enforce_pur_lead_time NUMBER, -- Ship_Rec_Cal
133 enforce_sup_capacity NUMBER, -- Ship_Rec_Cal
134 plan_type NUMBER, -- ATP4drp
135 itf_horiz_days NUMBER -- ATP4drp
136 );
137 -- Additional Fields for Supplier Capacity and Lead Time (SCLT) Project.
138 -- Additional Fields are sr_instance_id and organization_id and curr_cutoff_date.
139 -- Existing fields now used are plan_start_date and plan_cutoff_date.
140
141 /* changes for ship_rec_cal begin */
142 TYPE ship_arrival_date_rec_typ IS RECORD(
143 scheduled_arrival_date DATE,
144 latest_acceptable_date DATE,
145 order_date_type NUMBER,
146 demand_id NUMBER,
147 instance_id NUMBER,
148 plan_id NUMBER,
149 ship_set_name VARCHAR2(30),
150 arrival_set_name VARCHAR2(30),
151 atp_override_flag VARCHAR2(1),
152 request_arrival_date DATE
153 );
154
155 G_SHIP_CAPACITY CONSTANT INTEGER := 1;
156 G_DOCK_CAPACITY CONSTANT INTEGER := 2;
157 G_USE_SHIP_REC_CAL VARCHAR2(1) := NVL(FND_PROFILE.value('MSC_USE_SHIP_REC_CAL'),'N'); --Bug 3593394
158 /* changes for ship_rec_cal end */
159
160 -- global variables
161 G_ITEM_INFO_REC item_attribute_rec;
162 G_ORG_INFO_REC org_attribute_rec;
163
164 --3720018
165 G_REMOVE_REQUEST_LEVEL_REC remove_request_level_rec;
166
167 -- global variable for plan introduced as a part of
168 -- Supplier Capacity Lead Time (SCLT) project.
169 G_PLAN_INFO_REC plan_info_rec;
170
171 G_FIND_FUTURE_DATE VARCHAR2(1) := 'N';
172 G_PEGGING_FOR_SET MRP_ATP_PUB.number_arr:=MRP_ATP_PUB.number_arr();
173 G_DEMAND_CLASS_ATP_FLAG MRP_ATP_PUB.number_arr:=MRP_ATP_PUB.number_arr();
174 G_REQ_ATP_DATE MRP_ATP_PUB.date_arr:=MRP_ATP_PUB.date_arr();
175 G_REQ_DATE_QTY MRP_ATP_PUB.number_arr:=MRP_ATP_PUB.number_arr();
176 G_INV_CTP NUMBER := FND_PROFILE.value('INV_CTP') ;
177 G_SUB_COMP VARCHAR2(1) := NVL(FND_PROFILE.value('MRP_ATP_SUB_COMP'), 'N');
178 G_ALLOCATED_ATP VARCHAR2(1) := NVL(FND_PROFILE.value('MSC_ALLOCATED_ATP'),'N');
179 G_INTERNAL_SALES_ORDER VARCHAR2(1) := NVL(FND_PROFILE.value('MSC_ISO_ALLOC_ATP'),'N'); --10425563
180 -- ATP4drp use original profile to restore value after DRP to non-DRP plan switch.
181 G_ORIG_ALLOC_ATP VARCHAR2(1) := G_ALLOCATED_ATP;
182 -- End ATP4drp
183 G_RES_CONSUME VARCHAR2(1) := 'Y';
184 G_ASSEMBLY_LINE_ID NUMBER;
185 G_HIERARCHY_PROFILE NUMBER := NVL(FND_PROFILE.VALUE('MSC_CLASS_HIERARCHY'), 2);
186 G_PARTNER_ID NUMBER;
187 G_PARTNER_SITE_ID NUMBER;
188 G_SR_PARTY_SITE_ID NUMBER; --2814895
189 G_SR_CUSTOMER_COUNTRY VARCHAR2(60); --2814895
190 G_OPTIMIZED_PLAN PLS_INTEGER := 2;
191 G_SESSION_ID NUMBER;
192 G_ORDER_LINE_ID NUMBER;
193 G_DEMAND_PEGGING_ID NUMBER;
194 G_COMP_LINE_ID NUMBER;
195 G_MOV_PAST_DUE_SYSDATE_PROF VARCHAR2(1) := NVL(FND_PROFILE.value('MSC_MOVE_PAST_DUE_TO_SYSDATE'), 'Y'); --6316476
196
197 -- dsting default to 3 (11i source) since we don't read msc_apps_instances for non-distributed case
198 G_APPS_VER NUMBER := SOURCE11i; -- 2300767
199 G_SUMMARY_FLAG VARCHAR2(1) := NVL(FND_PROFILE.VALUE('MSC_ENABLE_ATP_SUMMARY'), 'N');
200 G_SUMMARY_SQL VARCHAR2(1); -- For summary enhancement
201
202 --optional_fw
203 G_OPTIONAL_FW NUMBER := 1; --This global variable is Null for the first pass and not null for remain passes.
204 G_ATP_COMP_FLAG VARCHAR2(1) := 'Y';
205 --This variable @ first pass on top level.
206 G_REQUESTED_SHIP_DATE DATE; --User entered value
207 G_FW_PEGGING_ID NUMBER; --Points to the ATP supply line pegging added during subsequent passes.
208 G_FW_CTP_PEGGING_ID MRP_ATP_PUB.number_arr:=MRP_ATP_PUB.number_arr();
209 --Points to the PO supply line pegging added during subsequent passes.
210 G_FW_STEAL_PEGGING_ID MRP_ATP_PUB.number_arr:=MRP_ATP_PUB.number_arr();
211 --Points to the Stealing line in forward pass.
212 G_FORWARD_ATP VARCHAR2(1) := NVL(FND_PROFILE.value('MSC_ENHANCED_FORWARD_ATP'), 'N');
213 --Profile to control which f/w pass we choose.
214 G_DEMAND_ID NUMBER; --5158454
215 G_NUMBER_OF_ITERATIONS NUMBER; --5211558
216 G_LOOP_COUNT NUMBER; --5211558
217 -- Bug 2877340, 2746213
218 -- Create new global variable for Infinite Time Fence Pad
219 -- Workaround to match ASCP and ATP output.
220 -- Round the days in case user erroneously
221 -- sets fraction Number as profile calue. If user sets profile
222 -- value to be -ve,Consider it to be 0.
223 G_INF_SUP_TF_PAD NUMBER := GREATEST(CEIL(NVL(FND_PROFILE.value('MSC_ATP_INFINITE_TF_PAD'),0)),0);
224 ---diag_atp
225 G_DIAGNOSTIC_ATP NUMBER;
226 G_SUBSTITUTION_FLAG VARCHAR2(1);
227 G_PLAN_SUBST_FLAG NUMBER;
228 G_HAVE_MAKE_BUY_PARENT NUMBER := 0;
229 G_SUBST_GOOD_PEGGING NUMBER;
230
231 BACKWARD_SCHEDULING CONSTANT INTEGER := 1;
232 FORWARD_SCHEDULING CONSTANT INTEGER := 2;
233 /************ Bug 1510853 ATP Rule Check ************/
234 G_ATP_RULE_FLAG VARCHAR2(1); -- to capture the presence of a rule
235
236 -- savirine added global variables G_SR_PARTNER_SITE_ID on Aug 27, 2001
237 G_SR_PARTNER_SITE_ID NUMBER;
238
239 -- ngoel 9/24/2001, added to identify if current line is MATO line
240 G_CTO_LINE VARCHAR2(1);
241 -- For bug 2259824, G_END_OF_DAY represents number of seconds in a day - 1.
242 --G_END_OF_DAY NUMBER := 86399/86400; Bug 3343359 - changed to last minute of the day
243 G_END_OF_DAY NUMBER := 1439/1440;
244 --- Enhance CTO Phase 1 Req #17
245 -- New global to deal with forward stealing for CTO components.
246 -- Used to store/track the Demand Pegging ID.
247 G_CTO_FORWARD_DMD_PEG NUMBER;
248 -- For bug 2974324. Store the calling_module in global variable.
249 G_CALLING_MODULE NUMBER;
250
251 G_INSTANCE_ID NUMBER; -- this variable contains value of instance id
252 --with which call is made
253 --plan by request date
254 G_HP_DEMAND_BUCKETING_PREF NUMBER := NVL(FND_PROFILE.VALUE('MSC_HP_DMD_BKT_PRF'), 1);
255 -- Action code
256 G_ZERO_ALLOCATION_PERC VARCHAR2(1) := NVL(FND_PROFILE.value('MSC_ZERO_ALLOC_PERC'),'N');--6359986
257
258 ATPQUERY CONSTANT INTEGER := 100;
259 DEMANDADD CONSTANT INTEGER := 110;
260 DEMANDMODIFY CONSTANT INTEGER := 120;
261 RSVADD CONSTANT INTEGER := 130;
262 RSVMODIFY CONSTANT INTEGER := 140;
263 DMDRSVADD CONSTANT INTEGER := 150;
264 DMDRSVXFER CONSTANT INTEGER := 160;
265 FORCERSVADD CONSTANT INTEGER := 170;
266
267 -- Existing error code
268 ALLSUCCESS CONSTANT INTEGER := 0;
269 NOREQ_DATE CONSTANT INTEGER := 20;
270
271 DEM_NOT_FOUND CONSTANT INTEGER := 9;
272 GROUPEL_ERROR CONSTANT INTEGER := 19;
273 DUPLICATE_DMD CONSTANT INTEGER := 42;
274 ATP_NO_GROUP_DATE CONSTANT INTEGER := 50;
275 ATP_NO_REQUESTS CONSTANT INTEGER := 51;
276 ATP_REQ_QTY_FAIL CONSTANT INTEGER := 52;
277 ATP_ACCEPT_FAIL CONSTANT INTEGER := 53;
278 ATP_EXCEED_SIZE CONSTANT INTEGER := 54;
279 ATP_NO_CALENDAR CONSTANT INTEGER := 55;
280 ATP_MULTI_CALENDARS CONSTANT INTEGER := 56;
281 ATP_BAD_RULE CONSTANT INTEGER := 57;
282 ATP_REQ_QTY_FAIL_RES CONSTANT INTEGER := 58;
283 ATP_REQ_QTY_FAIL_BOTH CONSTANT INTEGER := 59;
284 BAD_EXPLOSION CONSTANT INTEGER := 60;
285 ATP_NOT_APPL CONSTANT INTEGER := 61;
286 ATP_SHORT_CAL CONSTANT INTEGER := 62;
287 ATP_LEAD_TIME_FAIL CONSTANT INTEGER := 63;
288
289 -- new error code we need to add
290 ATP_MULTI_REQ_DATES CONSTANT INTEGER := 70;
291 ATP_NO_SOURCES CONSTANT INTEGER := 80;
292 ATP_ITEM_NOT_COLLECTED CONSTANT INTEGER := 85;
293 ATP_NO_ASSIGN_SET CONSTANT INTEGER := 90;
294 ATP_REQ_DATE_FAIL CONSTANT INTEGER := 100;
295 ATP_PASS_INFINITE_DATE CONSTANT INTEGER := 110;
296 ATP_INVALID_DATE CONSTANT INTEGER := 99;
297 PLAN_NOT_FOUND CONSTANT INTEGER := 120;
298 TRY_ATP_LATER CONSTANT INTEGER := 130;
299 NO_ASSIGNMENT_SET CONSTANT INTEGER := 135;
300 NO_MATCHING_CAL_DATE CONSTANT INTEGER := 47;
301 SUMM_CONC_PROG_RUNNING CONSTANT INTEGER := 160;
302 -- 2152184
303 PF_MEMBER_ITEM_NOT_ATPABLE CONSTANT INTEGER := 180;
304 RUN_POST_PLAN_ALLOC CONSTANT INTEGER := 200;
305 -- 1873918
306 PDS_TO_ODS_SWITCH CONSTANT INTEGER := 150;
307
308 -- search
309
310 --Error Handling Changes krajan
311 ATP_INVALID_OBJECTS CONSTANT INTEGER := 220;
312 ATP_PROCESSING_ERROR CONSTANT INTEGER := 23;
313
314 ---diag_atp
315 DIAGNOSTIC_ATP_ENABLED CONSTANT INTEGER := 260;
316
317 -- 2368426 starts
318 INV_CTP_NOT_IN_SYNC CONSTANT INTEGER := 230;
319 USE_SHIP_REC_NOT_IN_SYNC CONSTANT INTEGER := 231; --bug3593394
320 ASSIGN_SET_NOT_IN_SYNC CONSTANT INTEGER := 240;
321 G_INV_CTP_SOURCE NUMBER ;
322 G_SR_ASSIGN_SET NUMBER ;
323 -- 2368426 ends
324
325 -- ATP Override rajjain begin
326 ATP_MULTI_OVERRIDE_DATES CONSTANT INTEGER := 250;
327 ATP_OVERRIDE_DATE_FAIL CONSTANT INTEGER := 251;
328 -- ATP Override rajjain end
329
330 -- rajjain 02/03/2003 Bug 2766713
331 INVALID_ITEM_ORG_COMBINATION CONSTANT INTEGER := 270;
332
333 -- rajjain 02/20/2003 Bug 2813095
334 INVALID_ALLOC_PROFILE_SETUP CONSTANT INTEGER := 280;
335 INVALID_INV_CTP_PROFILE_SETUP CONSTANT INTEGER := 281;
336 INVALID_ALLOC_ATP_OFF CONSTANT INTEGER := 282;
337
338 PLAN_DOWN_TIME CONSTANT INTEGER := 300;
339
340 MUTUALLY_EXCLUSIVE_OSS CONSTANT NUMBER := 350;
341 OSS_ERROR_AT_LOWER_LEVEL CONSTANT NUMBER := 360;
342 OSS_ERROR_AT_THIS_LEVEL CONSTANT NUMBER := 370;
343 OSS_SOURCING_ERROR CONSTANT NUMBER := 310;
344 ERROR_WHILE_MATCHING CONSTANT NUMBER := 320;
345 INVALID_OSS_WAREHOUSE CONSTANT NUMBER := 330;
346
347 CTO_OSS_ERROR CONSTANT VARCHAR2(10) := 'CTO_Err';
348
349 -- rajjain bug 2951786 05/13/2003
350 NO_SUPPLY_DEMAND CONSTANT INTEGER := 175;
351
352 DEPTH CONSTANT INTEGER := 1;
353 BREADTH CONSTANT INTEGER := 2;
354
355 -- source type
356 TRANSFER CONSTANT INTEGER := 1;
357 MAKE CONSTANT INTEGER := 2;
358 BUY CONSTANT INTEGER := 3;
359
360 -- action for pegging
361 UNDO CONSTANT INTEGER := 1;
362 INVALID CONSTANT INTEGER := 2;
363
364 --4570421
365 DISCRETE_ORG CONSTANT INTEGER := 1;
366 OPM_ORG CONSTANT INTEGER := 2;
367
368 -- 2400614: krajan:
369 -- Returned by ATP_Check.
370 -- Indicates a source mismatch
371 G_ATO_SRC_MISMATCH VARCHAR2(1) := 'G';
372 G_ATO_UNCOLL_ITEM VARCHAR2(1) := 'A'; -- krajan 2752705
373 G_NO_PLAN_FOUND VARCHAR2(1) := 'P'; -- dsting 2764213
374
375
376 --- bug 1905037. The following variable is added so
377 --- we can return the inventory_item_id which is not collected
378 --- to schedule procedure
379 G_SR_INVENTORY_ITEM_ID number;
380 G_PLAN_COPRODUCTS VARCHAR(1) := FND_Profile.value('MSC_PLAN_COPRODUCTS');
381 G_DATABASE_LINK VARCHAR2(128);
382 --bug 2178544
383 --G_PTF_FLAG NUMBER; -- This flag indicates that plan is constrained by PTF or not
384 G_MSO_LEAD_TIME_FACTOR NUMBER := NVL(FND_PROFILE.value('MSO_SCO_LEAD_TIME_FACTOR'),0);
385 G_FUTURE_ORDER_DATE DATE;
386 G_FUTURE_START_DATE DATE;
387 G_PTF_DATE DATE;
388 G_PTF_DATE_THIS_LEVEL DATE;
389 G_ALLOCATION_METHOD NUMBER := NVL(FND_PROFILE.VALUE('MSC_ALLOCATION_METHOD'), 2);
390 G_FUTURE_PEGGING_ID NUMBER;
391
392 G_REFRESH_NUMBER NUMBER;
393 --G_CREATE_TIME_FENCE VARCHAR(1) := NVL(FND_Profile.value('MRP_FIRM_ORDER_TF'),'N');
394
395 -- krajan: 2408902
396 -- Variable to store Demand Class passed in for ATO models.
397 G_ATP_DEMAND_CLASS VARCHAR2(34);
398
399 -- krajan: 2408696
400 -- Variable to store Phantom Profile option value
401 G_EXPLODE_PHANTOM VARCHAR(1) := NVL(FND_Profile.value('MSC_ENABLE_PHANT_COMP'), 'N');
402
403 -- 24x7 Support : Synchronization in progress flag
404 G_SYNC_ATP_CHECK VARCHAR2(1);
405 --Set in case msc_item_id_lid has no records 4091487
406 G_ITEM_ID_NULL VARCHAR2(1) := 'F';
407 --Set in case item is not collected 4091487
408 G_ITEM_NOT_COLL VARCHAR2(1) := 'C';
409
410 -- Set if we ever encounter downtime
411 G_DOWNTIME_HIT VARCHAR2(1);
412
413 ---- Exceptions
414 NO_MATCHING_DATE_IN_CAL Exception;
415 /************ Bug 1510853 ATP Rule Check ************/
416 EXC_NO_ATP_RULE Exception;
417
418 -- Bug 2400614: krajan
419 G_ATO_SOURCING_MISMATCH Exception;
420
421 -- dsting 2764213
422 EXC_NO_PLAN_FOUND Exception;
423
424 -- krajan : 2752705
425 G_EXC_UNCOLLECTED_ITEM Exception;
426
427 -- rajjain 02/20/2002 Bug 2813095
428 ALLOC_ATP_INVALID_PROFILE Exception;
429
430 --s_cto_rearch
431 INVALID_OSS_SOURCE Exception;
432
433 --diag_atp
434 G_ALLOCATION_RULE_NAME VARCHAR2(30);
435 ORG_DEMAND CONSTANT NUMBER := 1;
436 SUPPLIER_DEMAND CONSTANT NUMBER := 2;
437 ATP_SUPPLY CONSTANT NUMBER := 3;
438 MAKE_SUPPLY CONSTANT NUMBER := 4;
439 BUY_SUPPLY CONSTANT NUMBER := 5;
440 TRANSFER_SUPPLY CONSTANT NUMBER := 6;
441 ATP_SUPPLIER CONSTANT NUMBER := 7;
442 RESOURCE_DEMAND CONSTANT NUMBER := 8;
443 RESOURCE_SUPPLY CONSTANT NUMBER := 9;
444
445 -- For summary enhancement
446 G_COPY_DEMAND_ID MRP_ATP_PUB.number_arr := MRP_ATP_PUB.number_arr();
447 G_COPY_DEMAND_PLAN_ID MRP_ATP_PUB.number_arr := MRP_ATP_PUB.number_arr();
448 --Global Variable Defined at this level just to avoid hardcoding for
449 --enhancment for Plan by request Date
450 G_SCHEDULE_SHIP_DATE_LEGEND CONSTANT NUMBER :=1;
451 G_SCHEDULE_ARRIVAL_DATE_LEGEND CONSTANT NUMBER :=2;
452 G_REQUEST_SHIP_DATE_LEGEND CONSTANT NUMBER :=3;
453 G_REQUEST_ARRIVAL_DATE_LEGEND CONSTANT NUMBER :=4;
454 G_PROMISE_SHIP_DATE_LEGEND CONSTANT NUMBER :=5;
455 G_PROMISE_ARRIVAL_DATE_LEGEND CONSTANT NUMBER :=6;
456
457 -- For new allocation logic for time_phased_atp
458 G_MEM_RULE_WITHIN_ATF VARCHAR2(1);
459 G_PF_RULE_OUTSIDE_ATF VARCHAR2(1);
460
461 -- To support new logic for dependent demands allocation in time phased PF rule based AATP scenarios
462 G_TIME_PHASED_PF_ENABLED VARCHAR2(1);
463 -- CTO_PF_PRJ changes for CTO PF Cross Project Impacts
464 G_CTO_PF_ATP VARCHAR2(1);
465
466 --4279623 4333596
467 YES CONSTANT NUMBER := 1;
468 NO CONSTANT NUMBER := 2;
469 CHECK_ORG_IN_PLAN CONSTANT NUMBER := 3;
470 --4279623 4333596
471 G_ATP_CHECK_ISO NUMBER := NVL(FND_PROFILE.VALUE('MSC_ATP_CHECK_INT_SALES_ORDERS'),NO); --6485306
472
473 --bug 4358596
474 G_RETAIN_TIME_NON_ATP VARCHAR(1) := NVL(FND_Profile.value('MSC_RETAIN_TIME_NON_ATP_ITEM'), 'Y');
475 G_ATP_ITEM_PRESENT_IN_SET VARCHAR(1) :='N'; --4460369
476
477 G_WS_CALL VARCHAR2(1) := 'N'; -- GOP Web Service Call
478
479 --ALLOC ATP CHANGES -12973673
480 G_HYBRID_ALLOC_ATP VARCHAR(1) := LEAST(G_ORIG_ALLOC_ATP, Nvl(FND_Profile.value('MSC_HYBRID_ALLOC_ATP'),'N'));
481
482 PROCEDURE Schedule (
483 p_atp_table IN MRP_ATP_PUB.ATP_Rec_Typ,
484 p_instance_id IN NUMBER,
485 p_assign_set_id IN NUMBER,
486 p_refresh_number IN NUMBER,
487 x_atp_table OUT NoCopy MRP_ATP_PUB.ATP_Rec_Typ,
488 x_return_status OUT NoCopy VARCHAR2,
489 x_msg_data OUT NoCopy VARCHAR2,
490 x_msg_count OUT NoCopy NUMBER,
491 x_atp_supply_demand OUT NOCOPY MRP_ATP_PUB.ATP_Supply_Demand_Typ,
492 x_atp_period OUT NOCOPY MRP_ATP_PUB.ATP_Period_Typ,
493 x_atp_details OUT NOCOPY MRP_ATP_PUB.ATP_Details_Typ
494 );
495
496 PROCEDURE ATP_Check (p_atp_record IN OUT NoCopy MRP_ATP_PVT.AtpRec,
497 p_plan_id IN NUMBER,
498 p_level IN NUMBER,
499 p_scenario_id IN NUMBER,
500 p_search IN NUMBER,
501 p_refresh_number IN NUMBER,
502 p_parent_pegging_id IN NUMBER,
503 p_assign_set_id IN NUMBER,
504 x_atp_period OUT NOCOPY MRP_ATP_PUB.ATP_Period_Typ,
505 x_atp_supply_demand OUT NOCOPY MRP_ATP_PUB.ATP_Supply_Demand_Typ,
506 x_return_status OUT NoCopy VARCHAR2,
507 p_pre_processing_lead_time IN NUMBER :=0
508 );
509
510
511 PROCEDURE Call_Schedule (
512 p_session_id IN NUMBER,
513 p_atp_table IN MRP_ATP_PUB.ATP_Rec_Typ,
514 p_instance_id IN NUMBER,
515 p_assign_set_id IN NUMBER,
516 p_refresh_number IN NUMBER,
517 x_atp_table OUT NoCopy MRP_ATP_PUB.ATP_Rec_Typ,
518 x_return_status OUT NoCopy VARCHAR2,
519 x_msg_data OUT NoCopy VARCHAR2,
520 x_msg_count OUT NoCopy NUMBER,
521 x_atp_supply_demand OUT NOCOPY MRP_ATP_PUB.ATP_Supply_Demand_Typ,
522 x_atp_period OUT NOCOPY MRP_ATP_PUB.ATP_Period_Typ,
523 x_atp_details OUT NOCOPY MRP_ATP_PUB.ATP_Details_Typ
524 );
525
526 -- p_inv_ctp added in argument of call_schedule_remote
527 -- for bug 2368426 to match source and destination INV_CTP
528
529 --bug3940999 commenting the agruments for passing profiles
530 PROCEDURE Call_Schedule_Remote (
531 p_session_id IN NUMBER,
532 p_instance_id IN NUMBER,
533 p_assign_set_id IN NUMBER,
534 p_refresh_number IN NUMBER,
535 -- p_inv_ctp IN NUMBER := -1, --bug3940999
536 p_def_assign_set_id IN NUMBER := -1,
537 -- We are passing debug mode as parameter so that debug could be set as soon as we enter procedure.
538 p_atp_debug_flag IN VARCHAR2 := NULL,
539 --ATP Debug Workflow
540 x_session_loc_des OUT NOCOPY VARCHAR2,
541 x_spid_des OUT NOCOPY NUMBER,
542 x_trace_loc_des OUT NOCOPY VARCHAR2,
543 -- p_atp_workflow IN VARCHAR2 := NULL, --bug3940999
544 p_node_id IN NUMBER DEFAULT null --bug3520746
545 -- p_use_ship_rec IN VARCHAR2 DEFAULT 'N' --bug3593394 --bug3940999
546 );
547
548 PROCEDURE Process_Time_Stamp_Errors (l_atp_table IN OUT NOCOPY MRP_ATP_PUB.ATP_Rec_Typ,
549 i NUMBER);--4460369
550
551 --optional_fw
552 /*--GET_SEQ_NUM-------------------------------------------------
553 | o This procedure is called from Schedule to return the seq
554 | number for the passed date. (Used in binary search algo)
555 +---------------------------------------------------------------*/
556 FUNCTION GET_SEQ_NUM (p_calendar_date IN DATE,
557 p_calendar_code IN VARCHAR2,
558 p_instance_id IN number
559 ) RETURN NUMBER;
560
561 /*--GET_DATE_FROM_SEQNUM-----------------------------------------
562 | o This procedure is called from Schedule to return the date
563 | for the passed seq number. (Used in binary search algo)
564 +---------------------------------------------------------------*/
565
566 FUNCTION GET_DATE_FROM_SEQNUM (p_seq_num IN NUMBER,
567 p_calendar_code IN VARCHAR2,
568 p_instance_id IN number
569 ) RETURN DATE;
570 END MSC_ATP_PVT;