1 PACKAGE BODY MSC_WS_ATP AS
2 /* $Header: MSCWATPB.pls 120.6 2008/03/13 15:05:47 bnaghi noship $ */
3
4
5
6 -- =============================================================
7 -- Desc: Please see package spec file for description
8 -- =============================================================
9 PROCEDURE GetPromiseDate(
10 status OUT nocopy VARCHAR2,
11
12 InstanceId IN MscNumberArr ,
13 InventoryItemId IN MscNumberArr,
14 InventoryItemName IN MscChar40Arr,
15 SourceOrganizationId IN MscNumberArr ,
16 SourceOrganizationCode IN MscChar7Arr ,
17 OrganizationId IN MscNumberArr ,
18 Identifier IN MscNumberArr ,
19 DemandSourceHeaderId IN MscNumberArr ,
20 DemandSourceDelivery IN MscChar30Arr ,
21 DemandSourceType IN MscNumberArr ,
22 CallingModule IN MscNumberArr ,
23 CustomerId IN MscNumberArr ,
24 CustomerSiteId IN MscNumberArr ,
25 QuantityOrdered IN MscNumberArr ,
26 QuantityUOM IN MscChar3Arr ,
27 RequestedShipDate IN MscDateArr,
28 RequestedArrivalDate IN MscDateArr ,
29 LatestAcceptableDate IN MscDateArr ,
30 DeliveryLeadTime IN MscNumberArr ,
31 ShipMethod IN MscChar30Arr ,
32 DemandClass IN MscChar30Arr ,
33 ShipSetName IN MscChar30Arr ,
34 ArrivalSetName IN MscChar30Arr ,
35 OverrideFlag IN MscChar1Arr ,
36 Action IN MscNumberArr ,
37 InsertFlag IN MscNumberArr ,
38 OEFlag IN MscChar1Arr ,
39 OrderNumber IN MscNumberArr ,
40 OldSourceOrganizationId IN MscNumberArr ,
41 OldDemandClass IN MscChar30Arr ,
42 Attribute01 IN MscNumberArr ,
43 Attribute02 IN MscNumberArr ,
44 CustomerCountry IN MscChar60Arr ,
45 CustomerState IN MscChar60Arr ,
46 CustomerCity IN MscChar60Arr ,
47 CustomerPostalCode IN MscChar60Arr ,
48 SubstitutionTypeCode IN MscNumberArr ,
49 ReqItemDetailFlag IN MscNumberArr ,
50 SalesRep IN MscChar255Arr ,
51 CustomerContact IN MscChar255Arr ,
52 TopModelLineId IN MscNumberArr ,
53 ATOParentModelLineId IN MscNumberArr ,
54 ATOModelLineId IN MscNumberArr ,
55 ParentLineId IN MscNumberArr ,
56 MatchItemId IN MscNumberArr ,
57 ConfigItemLineId IN MscNumberArr ,
58 ValidationOrg IN MscNumberArr ,
59 ComponentSequenceID IN MscNumberArr ,
60 ComponentCode IN MscChar255Arr ,
61 LineNumber IN MscChar80Arr ,
62 IncludedItemFlag IN MscNumberArr ,
63 PickComponentsFlag IN MscChar1Arr ,
64 CascadeModelInfoToComp IN MscNumberArr ,
65 SequenceNumber IN MscNumberArr ,
66 InternalOrgId IN MscNumberArr ,
67 PartySiteId IN MscNumberArr ,
68 PartOfSet IN MscChar1Arr ,
69
70 InstanceIdOut OUT nocopy MscNumberArr,
71 InventoryItemIdOut OUT nocopy MscNumberArr,
72 InventoryItemNameOut OUT nocopy MscChar40Arr,
73 SourceOrganizationIdOut OUT nocopy MscNumberArr,
74 SourceOrganizationCodeOut OUT nocopy MscChar7Arr,
75 OrganizationIdOut OUT nocopy MscNumberArr,
76 IdentifierOut OUT nocopy MscNumberArr,
77 DemandSourceHeaderIdOut OUT nocopy MscNumberArr,
78 DemandSourceDeliveryOut OUT nocopy MscChar30Arr,
79 DemandSourceTypeOut OUT nocopy MscNumberArr,
80 CustomerIdOut OUT nocopy MscNumberArr,
81 CustomerSiteIdOut OUT nocopy MscNumberArr,
82 QuanitytOrderedOut OUT nocopy MscNumberArr,
83 QuantityUOMOut OUT nocopy MscChar3Arr,
84 RequestedShipDateOut OUT nocopy MscDateArr,
85 RequestedArrivalDateOut OUT nocopy MscDateArr,
86 LatestAcceptableDateOut OUT nocopy MscDateArr,
87 DeliveryLeadTimeOut OUT nocopy MscNumberArr,
88 ShipMethodOut OUT nocopy MscChar30Arr,
89 DemandClassOut OUT nocopy MscChar30Arr,
90 ShipSetNameOut OUT nocopy MscChar30Arr,
91 ArrivalSetNameOut OUT nocopy MscChar30Arr,
92 OverrideFlagOut OUT nocopy MscChar1Arr,
93 ShipDateOut OUT nocopy MscDateArr,
94 ArrivalDateOut OUT nocopy MscDateArr,
95 AvailableQuantityOut OUT nocopy MscNumberArr,
96 RequestedDateQuantityOut OUT nocopy MscNumberArr,
97 GroupShipDateOut OUT nocopy MscDateArr,
98 GroupArrivalDateOut OUT nocopy MscDateArr,
99 AtpLeadTimeOut OUT nocopy MscNumberArr,
100 ErrorCodeOut OUT nocopy MscNumberArr,
101 EndPeggingIdOut OUT nocopy MscNumberArr,
102 OldSourceOrganizationIdOut OUT nocopy MscNumberArr,
103 OldDemandClassOut OUT nocopy MscChar30Arr,
104 RequestItemIdOut OUT nocopy MscNumberArr,
105 ReqItemReqDateQtyOut OUT nocopy MscNumberArr,
106 ReqItemAvailableDateOut OUT nocopy MscDateArr,
107 ReqItemAvailableDateQtyOut OUT nocopy MscNumberArr,
108 RequestItemNameOut OUT nocopy MscChar40Arr,
109 OldInventoryItemIdOut OUT nocopy MscNumberArr,
110 SubstFlagOut OUT nocopy MscNumberArr,
111 BaseModelIdOut OUT nocopy MscNumberArr,
112 OssErrorCodeOut OUT nocopy MscNumberArr,
113 MatchedItemNameOut OUT nocopy MscChar255Arr,
114 CascadeModelInfoToCompOut OUT nocopy MscNumberArr,
115 PlanIdOut OUT nocopy MscNumberArr
116 ) is
117
118 sessionId NUMBER :=0;
119 p_atp_table MRP_ATP_PUB.ATP_Rec_Typ;
120 ln NUMBER;
121 i NUMBER :=0;
122 x_atp_table MRP_ATP_PUB.ATP_Rec_Typ;
123 x_atp_supply_demand MRP_ATP_PUB.ATP_Supply_Demand_Typ;
124 x_atp_period MRP_ATP_PUB.ATP_Period_Typ;
125 x_atp_details MRP_ATP_PUB.ATP_Details_Typ;
126 x_return_status VARCHAR2(1);
127 x_msg_data VARCHAR2(200);
128 x_msg_count NUMBER;
129
130 temp_status Varchar2(40);
131 atpUnknownError EXCEPTION;
132 atpError EXCEPTION;
133 atpParameterSizeError EXCEPTION;
134 atpProfilesError EXCEPTION;
135 parameterName VARCHAR2(40);
136
137 userId NUMBER :=0;
138 respId NUMBER :=0;
139 appId NUMBER :=0;
140 error_tracking_number NUMBER :=0;
141
142
143 BEGIN
144
145 error_tracking_number := 100;
146 InstanceIdOut := MscNumberArr();
147 InventoryItemIdOut := MscNumberArr();
148 InventoryItemNameOut := MscChar40Arr();
149 SourceOrganizationIdOut := MscNumberArr();
150 SourceOrganizationCodeOut := MscChar7Arr();
151 OrganizationIdOut := MscNumberArr();
152 IdentifierOut := MscNumberArr();
153 DemandSourceHeaderIdOut := MscNumberArr();
154 DemandSourceDeliveryOut := MscChar30Arr();
155 DemandSourceTypeOut := MscNumberArr();
156 CustomerIdOut := MscNumberArr();
157 CustomerSiteIdOut := MscNumberArr();
158 QuanitytOrderedOut := MscNumberArr();
159 QuantityUOMOut := MscChar3Arr();
160 RequestedShipDateOut := MscDateArr();
161 RequestedArrivalDateOut := MscDateArr();
162 LatestAcceptableDateOut := MscDateArr();
163 DeliveryLeadTimeOut := MscNumberArr();
164 ShipMethodOut := MscChar30Arr();
165 DemandClassOut := MscChar30Arr();
166 ShipSetNameOut := MscChar30Arr();
167 ArrivalSetNameOut := MscChar30Arr();
168 OverrideFlagOut := MscChar1Arr();
169 ShipDateOut := MscDateArr();
170 ArrivalDateOut := MscDateArr();
171 AvailableQuantityOut := MscNumberArr();
172 RequestedDateQuantityOut := MscNumberArr();
173 GroupShipDateOut := MscDateArr();
174 GroupArrivalDateOut := MscDateArr();
175 AtpLeadTimeOut := MscNumberArr();
176 ErrorCodeOut := MscNumberArr();
177 EndPeggingIdOut := MscNumberArr();
178 OldSourceOrganizationIdOut := MscNumberArr();
179 OldDemandClassOut := MscChar30Arr();
180 RequestItemIdOut := MscNumberArr();
181 ReqItemReqDateQtyOut := MscNumberArr();
182 ReqItemAvailableDateOut := MscDateArr();
183 ReqItemAvailableDateQtyOut := MscNumberArr();
184 RequestItemNameOut := MscChar40Arr();
185 OldInventoryItemIdOut := MscNumberArr();
186 SubstFlagOut := MscNumberArr();
187 BaseModelIdOut := MscNumberArr();
188 OssErrorCodeOut := MscNumberArr();
189 MatchedItemNameOut := MscChar255Arr();
190 CascadeModelInfoToCompOut := MscNumberArr();
191 PlanIdOut := MscNumberArr();
192
193
194 --dbms_output.put_line('ln= ' || ln );
195
196 ------------ STEP 1 verify first that all mandatory params are set!
197 IF ( InstanceId is NULL or InstanceId.COUNT =0) THEN
198 parameterName := 'InstanceId';
199 RAISE atpParameterSizeError;
200 END IF;
201 IF ( InventoryItemId is NULL or InventoryItemId.COUNT =0) THEN
202 parameterName := 'InventoryItemId';
203 RAISE atpParameterSizeError;
204 END IF;
205
206 IF ( Identifier is NULL or Identifier.COUNT =0) THEN
207 parameterName := 'Identifier';
208 RAISE atpParameterSizeError;
209 END IF;
210
211 IF ( CallingModule is NULL or CallingModule.COUNT =0) THEN
212 parameterName := 'Calling Module';
213 RAISE atpParameterSizeError;
214 END IF;
215
216 IF ( QuantityOrdered is NULL or QuantityOrdered.COUNT =0) THEN
217 parameterName := 'QuantityOrdered';
218 RAISE atpParameterSizeError;
219 END IF;
220
221
222 IF ( QuantityUOM is NULL or QuantityUOM.COUNT =0) THEN
223 parameterName := 'QuantityUOM';
224 RAISE atpParameterSizeError;
225 END IF;
226
227 IF (RequestedShipDate.COUNT=0 AND RequestedArrivalDate.COUNT =0 ) THEN
228 parameterName := 'Required Ship Date';
229 RAISE atpParameterSizeError;
230 END IF;
231
232 IF ( Action is NULL or Action.COUNT =0) THEN
233 parameterName := 'Action';
234 RAISE atpParameterSizeError;
235 END IF;
236
237 ----------- STEP 2 verify that the length of all arrays is the SAME !!.
238 ln := InventoryItemId.COUNT ;
239
240
241 error_tracking_number := 182;
242
243 IF ( InventoryItemName is not NULL and InventoryItemName.Count <>0 AND InventoryItemName.COUNT <> ln) THEN
244 parameterName := 'InventoryItemName';
245 RAISE atpParameterSizeError;
246 END IF;
247
248 IF ( SourceOrganizationId is not NULL and SourceOrganizationId.Count <>0 AND SourceOrganizationId.COUNT <> ln) THEN
249 parameterName := 'SourceOrganizationId';
250 RAISE atpParameterSizeError;
251 END IF;
252
253 IF ( SourceOrganizationCode is not NULL and SourceOrganizationCode.Count <>0 AND SourceOrganizationCode.Count <> ln) THEN
254 parameterName := 'SourceOrganizationCode';
255 RAISE atpParameterSizeError;
256 END IF;
257
258 error_tracking_number := 200;
259
260 IF (OrganizationId is not NULL and OrganizationId.Count <>0 AND OrganizationId.Count <> ln) THEN
261 parameterName := 'OrganizationId';
262 RAISE atpParameterSizeError;
263 END IF;
264
265 IF (Identifier is not NULL and Identifier.Count <>0 AND Identifier.Count <> ln) THEN
266 parameterName := 'Identifier';
267 RAISE atpParameterSizeError;
268 END IF;
269
270 IF (DemandSourceHeaderId is not NULL and DemandSourceHeaderId.Count <>0 AND DemandSourceHeaderId.Count <> ln) THEN
271 parameterName := 'DemandSourceHeaderId';
272 RAISE atpParameterSizeError;
273 END IF;
274
275 IF (DemandSourceDelivery is not NULL and DemandSourceDelivery.Count <>0 AND DemandSourceDelivery.Count <> ln) THEN
276 parameterName := 'DemandSourceDelivery';
277 RAISE atpParameterSizeError;
278 END IF;
279
280
281 IF (DemandSourceType is not NULL and DemandSourceType.Count <>0 AND DemandSourceType.Count <> ln) THEN
282 parameterName := 'DemandSourceType';
283 RAISE atpParameterSizeError;
284 END IF;
285
286 IF ( CallingModule.Count <>0 AND CallingModule.Count <> ln) THEN
287 parameterName := 'CallingModule';
288 RAISE atpParameterSizeError;
289 END IF;
290
291 IF (CustomerId is not NULL and CustomerId.Count <>0 AND CustomerId.Count <> ln) THEN
292 parameterName := 'CustomerId';
293 RAISE atpParameterSizeError;
294 END IF;
295
296 IF (CustomerSiteId is not NULL and CustomerSiteId.Count <>0 AND CustomerSiteId.Count <> ln) THEN
297 parameterName := 'CustomerSiteId';
298 RAISE atpParameterSizeError;
299 END IF;
300
301 error_tracking_number := 220;
302
303 IF (QuantityOrdered.Count <>0 AND QuantityOrdered.Count <> ln) THEN
304 parameterName := 'QuantityOrdered';
305 RAISE atpParameterSizeError;
306 END IF;
307
308 IF ( QuantityUOM.Count <>0 AND QuantityUOM.Count <> ln) THEN
309 parameterName := 'QuantityUOM';
310 RAISE atpParameterSizeError;
311 END IF;
312
313 IF ( RequestedShipDate is not NULL and RequestedShipDate.Count <>0 AND RequestedShipDate.Count <> ln) THEN
314 parameterName := 'RequestedShipDate';
315 RAISE atpParameterSizeError;
316 END IF;
317
318 IF (RequestedArrivalDate is not NULL and RequestedArrivalDate.Count <>0 AND RequestedArrivalDate.Count <> ln) THEN
319 parameterName := 'RequestedArrivalDate';
320 RAISE atpParameterSizeError;
321 END IF;
322
323 IF (LatestAcceptableDate is not NULL and LatestAcceptableDate.Count <>0 AND LatestAcceptableDate.Count <> ln) THEN
324 parameterName := 'LatestAcceptableDate';
325 RAISE atpParameterSizeError;
326 END IF;
327
328 IF (DeliveryLeadTime is not NULL and DeliveryLeadTime.Count <>0 AND DeliveryLeadTime.Count <> ln) THEN
329 parameterName := 'DeliveryLeadTime';
330 RAISE atpParameterSizeError;
331 END IF;
332
333 IF (ShipMethod is not NULL and ShipMethod.Count <>0 AND ShipMethod.Count <> ln) THEN
334 parameterName := 'ShipMethod';
335 RAISE atpParameterSizeError;
336 END IF;
337
338
339 error_tracking_number := 250;
340
341 IF (DemandClass is not NULL and DemandClass.Count <>0 AND DemandClass.Count <> ln) THEN
342 parameterName := 'DemandClass';
343 RAISE atpParameterSizeError;
344 END IF;
345
346 IF (ShipSetName is not NULL and ShipSetName.Count <>0 AND ShipSetName.Count <> ln) THEN
347 parameterName := 'ShipSetName';
348 RAISE atpParameterSizeError;
349 END IF;
350
351 IF (ArrivalSetName is not NULL and ArrivalSetName.Count <>0 AND ArrivalSetName.Count <> ln) THEN
352 parameterName := 'ArrivalSetName';
353 RAISE atpParameterSizeError;
354 END IF;
355
356 IF (OverrideFlag is not NULL and OverrideFlag.Count <>0 AND OverrideFlag.Count <> ln) THEN
357 parameterName := 'OverrideFlag';
358 RAISE atpParameterSizeError;
359 END IF;
360
361 IF( Action.Count <>0 AND Action.Count <> ln) THEN
362 parameterName := 'Action';
363 RAISE atpParameterSizeError;
364 END IF;
365
366 IF (InsertFlag is not NULL and InsertFlag.Count <>0 AND InsertFlag.Count <> ln) THEN
367 parameterName := 'InsertFlag';
368 RAISE atpParameterSizeError;
369 END IF;
370
371 IF (OEFlag is not NULL and OEFlag.Count <>0 AND OEFlag.Count <> ln) THEN
372 parameterName := 'OEFlag';
373 RAISE atpParameterSizeError;
374 END IF;
375
376 IF (OrderNumber is not NULL and OrderNumber.Count <>0 AND OrderNumber.Count <> ln) THEN
377 parameterName := 'OrderNumber';
378 RAISE atpParameterSizeError;
379 END IF;
380
381 IF (OldSourceOrganizationId is not NULL and OldSourceOrganizationId.Count <>0 AND OldSourceOrganizationId.Count <> ln) THEN
382 parameterName := 'OldSourceOrganizationId';
383 RAISE atpParameterSizeError;
384 END IF;
385
386 IF (OldDemandClass is not NULL and OldDemandClass.Count <>0 AND OldDemandClass.Count <> ln) THEN
387 parameterName := 'OldDemandClass';
388 RAISE atpParameterSizeError;
389 END IF;
390
391 IF (Attribute01 is not NULL and Attribute01.Count <>0 AND Attribute01.Count <> ln) THEN
392 parameterName := 'Attribute01';
393 RAISE atpParameterSizeError;
394 END IF;
395
396 IF (Attribute02 is not NULL and Attribute02.Count <>0 AND Attribute02.Count <> ln) THEN
397 parameterName := 'Attribute02';
398 RAISE atpParameterSizeError;
399 END IF;
400
401 IF (CustomerCountry is not NULL and CustomerCountry.Count <>0 AND CustomerCountry.Count <> ln) THEN
402 parameterName := 'CustomerCountry';
403 RAISE atpParameterSizeError;
404 END IF;
405
406 IF (CustomerState is not NULL and CustomerState.Count <>0 AND CustomerState.Count <> ln) THEN
407 parameterName := 'CustomerState';
408 RAISE atpParameterSizeError;
409 END IF;
410
411 IF (CustomerCity is not NULL and CustomerCity.Count <>0 AND CustomerCity.Count <> ln) THEN
412 parameterName := 'CustomerCity';
413 RAISE atpParameterSizeError;
414 END IF;
415
416 IF (CustomerPostalCode is not NULL and CustomerPostalCode.Count <>0 AND CustomerPostalCode.Count <> ln) THEN
417 parameterName := 'CustomerPostalCode';
418 RAISE atpParameterSizeError;
419 END IF;
420
421 IF (SubstitutionTypeCode is not NULL and SubstitutionTypeCode.Count <>0 AND SubstitutionTypeCode.Count <> ln) THEN
422 parameterName := 'SubstitutionTypeCode';
423 RAISE atpParameterSizeError;
424 END IF;
425
426 IF (ReqItemDetailFlag is not NULL and ReqItemDetailFlag.Count <>0 AND ReqItemDetailFlag.Count <> ln) THEN
427 parameterName := 'ReqItemDetailFlag';
428 RAISE atpParameterSizeError;
429 END IF;
430
431 IF (SalesRep is not NULL and SalesRep.Count <>0 AND SalesRep.Count <> ln) THEN
432 parameterName := 'SalesRep';
433 RAISE atpParameterSizeError;
434 END IF;
435
436 IF (CustomerContact is not NULL and CustomerContact.Count <>0 AND CustomerContact.Count <> ln) THEN
437 parameterName := 'CustomerContact';
438 RAISE atpParameterSizeError;
439 END IF;
440
441 error_tracking_number := 300;
442
443 IF (TopModelLineId is not NULL and TopModelLineId.Count <>0 AND TopModelLineId.Count <> ln) THEN
444 parameterName := 'TopModelLineId';
445 RAISE atpParameterSizeError;
446 END IF;
447
448 IF (ATOParentModelLineId is not NULL and ATOParentModelLineId.Count <>0 AND ATOParentModelLineId.Count <> ln) THEN
449 parameterName := 'ATOParentModelLineId';
450 RAISE atpParameterSizeError;
451 END IF;
452
453 IF (ATOModelLineId is not NULL and ATOModelLineId.Count <>0 AND ATOModelLineId.Count <> ln) THEN
454 parameterName := 'ATOModelLineId';
455 RAISE atpParameterSizeError;
456 END IF;
457
458 IF (ParentLineId is not NULL and ParentLineId.Count <>0 AND ParentLineId.Count <> ln) THEN
459 parameterName := 'ParentLineId';
460 RAISE atpParameterSizeError;
461 END IF;
462
463 IF (MatchItemId is not NULL and MatchItemId.Count <>0 AND MatchItemId.Count <> ln) THEN
464 parameterName := 'MatchItemId';
465 RAISE atpParameterSizeError;
466 END IF;
467
468 IF (ConfigItemLineId is not NULL and ConfigItemLineId.Count <>0 AND ConfigItemLineId.Count <> ln) THEN
469 parameterName := 'ConfigItemLineId';
470 RAISE atpParameterSizeError;
471 END IF;
472
473 IF (ValidationOrg is not NULL and ValidationOrg.Count <>0 AND ValidationOrg.Count <> ln) THEN
474 parameterName := 'ValidationOrg';
475 RAISE atpParameterSizeError;
476 END IF;
477
478 IF (ComponentSequenceID is not NULL and ComponentSequenceID.Count <>0 AND ComponentSequenceID.Count <> ln) THEN
479 parameterName := 'ComponentSequenceID';
480 RAISE atpParameterSizeError;
481 END IF;
482
483 IF (ComponentCode is not NULL and ComponentCode.Count <>0 AND ComponentCode.Count <> ln) THEN
484 parameterName := 'ComponentCode';
485 RAISE atpParameterSizeError;
486 END IF;
487
488 IF (LineNumber is not NULL and LineNumber.Count <>0 AND LineNumber.Count <> ln) THEN
489 parameterName := 'LineNumber';
490 RAISE atpParameterSizeError;
491 END IF;
492
493 IF (IncludedItemFlag is not NULL and IncludedItemFlag.Count <>0 AND IncludedItemFlag.Count <> ln) THEN
494 parameterName := 'IncludedItemFlag';
495 RAISE atpParameterSizeError;
496 END IF;
497
498 IF (PickComponentsFlag is not NULL and PickComponentsFlag.Count <>0 AND PickComponentsFlag.Count <> ln ) THEN
499 parameterName := 'PickComponentsFlag';
500 RAISE atpParameterSizeError;
501 END IF;
502
503 IF (CascadeModelInfoToComp is not NULL and CascadeModelInfoToComp.Count <>0 AND CascadeModelInfoToComp.Count <> ln) THEN
504 parameterName := 'CascadeModelInfoToComp';
505 RAISE atpParameterSizeError;
506 END IF;
507
508 IF (SequenceNumber is not NULL and SequenceNumber.Count <>0 AND SequenceNumber.Count <> ln) THEN
509 parameterName := 'SequenceNumber';
510 RAISE atpParameterSizeError;
511 END IF;
512
513 IF (InternalOrgId is not NULL and InternalOrgId.Count <>0 AND InternalOrgId.Count <> ln) THEN
514 parameterName := 'InternalOrgId';
515 RAISE atpParameterSizeError;
516 END IF;
517
518 IF (PartySiteId is not NULL and PartySiteId.Count <>0 AND PartySiteId.Count <> ln) THEN
519 parameterName := 'PartySiteId';
520 RAISE atpParameterSizeError;
521 END IF;
522
523 IF (PartOfSet is not NULL and PartOfSet.Count <>0 AND PartOfSet.Count <> ln) THEN
524 parameterName := 'PartOfSet';
525 RAISE atpParameterSizeError;
526 END IF;
527
528
529
530
531 ----------STEP 3 -- apps initialize for system_administrator , just so we can get profiles values
532 SELECT responsibility_id, application_id INTO respId, appId FROM fnd_responsibility WHERE responsibility_key = 'SYSTEM_ADMINISTRATOR';
533 error_tracking_number:= 110;
534
535 SELECT user_id INTO userId FROM fnd_user WHERE user_name = 'SYSADMIN';
536
537 error_tracking_number:= 120;
538 fnd_global.apps_initialize(userId, respId, appId);
539
540 -- now we get profile values, and properly init apps_initialize();
541 error_tracking_number:= 130;
542 userId := fnd_profile.value('MSC_WS_ATP_FNDUSER');
543 error_tracking_number:= 140;
544 respId := fnd_profile.value('MSC_WS_ATP_FNDRESP');
545
546 error_tracking_number:= 150;
547 begin
548 SELECT application_id INTO appId FROM fnd_responsibility WHERE responsibility_id = respId;
549 EXCEPTION
550 WHEN others THEN
551 RAISE atpProfilesError;
552 end;
553
554
555 error_tracking_number:= 160;
556 fnd_global.apps_initialize(userId, respId, appId);
557
558 error_tracking_number:= 170;
559
560 -----------GET SESSION ID ------------------------------
561 msc_atp_global.get_atp_session_id(sessionId, temp_status);
562
563
564 ----------- STEP 4 -- form input parameters
565 MSC_SATP_FUNC.Extend_Atp (p_atp_table, x_return_status, ln);
566 error_tracking_number:= 1180;
567
568 IF InstanceId is not NULL and InstanceId.Count <>0 THEN
569 FOR i IN 1 .. ln LOOP p_atp_table.Instance_Id(i) := InstanceId(i); END LOOP ;
570 END IF;
571
572
573 IF InventoryItemId is not NULL and InventoryItemId.Count <>0 THEN
574 FOR i IN 1 .. ln
575 LOOP
576 p_atp_table.Inventory_Item_Id (i) := InventoryItemId (i);
577 END LOOP ;
578 END IF;
579
580 IF InventoryItemName is not NULL and InventoryItemName.Count <>0 THEN
581 FOR i IN 1 .. ln LOOP p_atp_table.Inventory_Item_Name (i) := InventoryItemName (i); END LOOP ;
582 END IF;
583 IF SourceOrganizationId is not NULL and SourceOrganizationId.Count <>0 THEN
584 FOR i IN 1 .. ln LOOP p_atp_table.Source_Organization_Id (i) := SourceOrganizationId (i); END LOOP ;
585 END IF;
586 IF SourceOrganizationCode is not NULL and SourceOrganizationCode.Count <>0 THEN
587 FOR i IN 1 .. ln LOOP p_atp_table.Source_Organization_Code (i) := SourceOrganizationCode (i); END LOOP ;
588 END IF;
589 IF OrganizationId is not NULL and OrganizationId.Count <>0 THEN
590 FOR i IN 1 .. ln LOOP p_atp_table.Organization_Id (i) := OrganizationId (i); END LOOP ;
591 END IF;
592 IF Identifier is not NULL and Identifier.Count <>0 THEN
593 FOR i IN 1 .. ln LOOP p_atp_table.Identifier (i) := Identifier (i); END LOOP ;
594 END IF;
595 IF DemandSourceHeaderId is not NULL and DemandSourceHeaderId.Count <>0 THEN
596 FOR i IN 1 .. ln LOOP p_atp_table.Demand_Source_Header_Id (i) := DemandSourceHeaderId (i); END LOOP ;
597 END IF;
598 IF DemandSourceDelivery is not NULL and DemandSourceDelivery.Count <>0 THEN
599 FOR i IN 1 .. ln LOOP p_atp_table.Demand_Source_Delivery (i) := DemandSourceDelivery (i); END LOOP ;
600 END IF;
601 IF DemandSourceType is not NULL and DemandSourceType.Count <>0 THEN
602 FOR i IN 1 .. ln LOOP p_atp_table.Demand_Source_Type (i) := DemandSourceType(i); END LOOP ;
603 END IF;
604 -----------------------------------------------------------------------------------------------------------------
605
606 IF CallingModule is not NULL and CallingModule.Count <>0 THEN
607 FOR i IN 1 .. ln LOOP p_atp_table.Calling_Module (i) := CallingModule(i); END LOOP ;
608 END IF;
609 IF CustomerId is not NULL and CustomerId.Count <>0 THEN
610 FOR i IN 1 .. ln LOOP p_atp_table.Customer_Id (i) := CustomerId(i); END LOOP ;
611 END IF;
612 IF CustomerSiteId is not NULL and CustomerSiteId.Count <>0 THEN
613 FOR i IN 1 .. ln LOOP p_atp_table.Customer_Site_Id (i) := CustomerSiteId(i); END LOOP ;
614 END IF;
615 IF QuantityOrdered is not NULL and QuantityOrdered.Count <>0 THEN
616 FOR i IN 1 .. ln LOOP p_atp_table.Quantity_Ordered (i) := QuantityOrdered(i); END LOOP ;
617 END IF;
618 IF QuantityUOM is not NULL and QuantityUOM.Count <>0 THEN
619 FOR i IN 1 .. ln LOOP p_atp_table.Quantity_UOM (i) := QuantityUOM(i); END LOOP ;
620 END IF;
621 IF RequestedShipDate is not NULL and RequestedShipDate.Count <>0 THEN
622 FOR i IN 1 .. ln LOOP p_atp_table.Requested_Ship_Date (i) := RequestedShipDate(i); END LOOP ;
623 END IF;
624 IF RequestedArrivalDate is not NULL and RequestedArrivalDate.Count <>0 THEN
625 FOR i IN 1 .. ln LOOP p_atp_table.Requested_Arrival_Date (i) := RequestedArrivalDate(i); END LOOP ;
626 END IF;
627 IF LatestAcceptableDate is not NULL and LatestAcceptableDate.Count <>0 THEN
628 FOR i IN 1 .. ln LOOP p_atp_table.Latest_Acceptable_Date (i) := LatestAcceptableDate(i); END LOOP ;
629 END IF;
630 IF DeliveryLeadTime is not NULL and DeliveryLeadTime.Count <>0 THEN
631 FOR i IN 1 .. ln LOOP p_atp_table.Delivery_Lead_Time (i) := DeliveryLeadTime(i); END LOOP ;
632 END IF;
633 IF ShipMethod is not NULL and ShipMethod.Count <>0 THEN
634 FOR i IN 1 .. ln LOOP p_atp_table.Ship_Method (i) := ShipMethod(i); END LOOP ;
635 END IF;
636 -----------------------------------------------------------------------------------------------------------------
637
638 IF DemandClass is not NULL and DemandClass.Count <>0 THEN
639 FOR i IN 1 .. ln LOOP p_atp_table.Demand_Class (i) := DemandClass(i); END LOOP ;
640 END IF;
641 IF ShipSetName is not NULL and ShipSetName.Count <>0 THEN
642 FOR i IN 1 .. ln LOOP p_atp_table.Ship_Set_Name (i) := ShipSetName(i); END LOOP ;
643 END IF;
644 IF ArrivalSetName is not NULL and ArrivalSetName.Count <>0 THEN
645 FOR i IN 1 .. ln LOOP p_atp_table.Arrival_Set_Name (i) := ArrivalSetName(i); END LOOP ;
646 END IF;
647 IF OverrideFlag is not NULL and OverrideFlag.Count <>0 THEN
648 FOR i IN 1 .. ln LOOP p_atp_table.Override_Flag (i) := OverrideFlag(i); END LOOP ;
649 END IF;
650 IF Action is not NULL and Action.Count <>0 THEN
651 FOR i IN 1 .. ln LOOP p_atp_table.Action(i) := Action(i); END LOOP ;
652 END IF;
653 IF InsertFlag is not NULL and InsertFlag.Count <>0 THEN
654 FOR i IN 1 .. ln LOOP p_atp_table.Insert_Flag (i) := InsertFlag(i); END LOOP ;
655 END IF;
656 IF OEFlag is not NULL and OEFlag.Count <>0 THEN
657 FOR i IN 1 .. ln LOOP p_atp_table.OE_Flag (i) := OEFlag(i); END LOOP ;
658 END IF;
659 IF OrderNumber is not NULL and OrderNumber.Count <>0 THEN
660 FOR i IN 1 .. ln LOOP p_atp_table.Order_Number (i) := OrderNumber(i); END LOOP ;
661 END IF;
662 IF OldSourceOrganizationId is not NULL and OldSourceOrganizationId.Count <>0 THEN
663 FOR i IN 1 .. ln LOOP p_atp_table.Old_Source_Organization_Id (i) := OldSourceOrganizationId(i); END LOOP ;
664 END IF;
665 IF OldDemandClass is not NULL and OldDemandClass.Count <>0 THEN
666 FOR i IN 1 .. ln LOOP p_atp_table.Old_Demand_Class (i) := OldDemandClass(i); END LOOP ;
667 END IF;
668 IF Attribute01 is not NULL and Attribute01.Count <>0 THEN
669 FOR i IN 1 .. ln LOOP p_atp_table.Attribute_01 (i) := Attribute01(i); END LOOP ;
670 END IF;
671 -----------------------------------------------------------------------------------------------------------------
672
673 IF Attribute02 is not NULL and Attribute02.Count <>0 THEN
674 FOR i IN 1 .. ln LOOP p_atp_table.Attribute_02 (i) := Attribute02(i); END LOOP ;
675 END IF;
676 IF CustomerCountry is not NULL and CustomerCountry.Count <>0 THEN
677 FOR i IN 1 .. ln LOOP p_atp_table.Customer_Country (i) := CustomerCountry(i); END LOOP ;
678 END IF;
679 IF CustomerState is not NULL and CustomerState.Count <>0 THEN
680 FOR i IN 1 .. ln LOOP p_atp_table.Customer_State (i) := CustomerState(i); END LOOP ;
681 END IF;
682 IF CustomerCity is not NULL and CustomerCity.Count <>0 THEN
683 FOR i IN 1 .. ln LOOP p_atp_table.Customer_City (i) := CustomerCity(i); END LOOP ;
684 END IF;
685 IF CustomerPostalCode is not NULL and CustomerPostalCode.Count <>0 THEN
686 FOR i IN 1 .. ln LOOP p_atp_table.Customer_Postal_Code (i) := CustomerPostalCode(i); END LOOP ;
687 END IF;
688 IF SubstitutionTypeCode is not NULL and SubstitutionTypeCode.Count <>0 THEN
689 FOR i IN 1 .. ln LOOP p_atp_table.Substitution_Typ_Code (i) := SubstitutionTypeCode(i); END LOOP ;
690 END IF;
691 IF ReqItemDetailFlag is not NULL and ReqItemDetailFlag.Count <>0 THEN
692 FOR i IN 1 .. ln LOOP p_atp_table.Req_Item_Detail_Flag (i) := ReqItemDetailFlag(i); END LOOP ;
693 END IF;
694 IF SalesRep is not NULL and SalesRep.Count <>0 THEN
695 FOR i IN 1 .. ln LOOP p_atp_table.Sales_Rep (i) := SalesRep(i); END LOOP ;
696 END IF;
697 IF CustomerContact is not NULL and CustomerContact.Count <>0 THEN
698 FOR i IN 1 .. ln LOOP p_atp_table.Customer_Contact (i) := CustomerContact(i); END LOOP ;
699 END IF;
700 IF TopModelLineId is not NULL and TopModelLineId.Count <>0 THEN
701 FOR i IN 1 .. ln LOOP p_atp_table.Top_Model_Line_Id (i) := TopModelLineId(i); END LOOP ;
702 END IF;
703 IF ATOParentModelLineId is not NULL and ATOParentModelLineId.Count <>0 THEN
704 FOR i IN 1 .. ln LOOP p_atp_table.ATO_Parent_Model_Line_Id (i) := ATOParentModelLineId(i); END LOOP ;
705 END IF;
706 IF ATOModelLineId is not NULL and ATOModelLineId.Count <>0 THEN
707 FOR i IN 1 .. ln LOOP p_atp_table.ATO_Model_Line_Id (i) := ATOModelLineId(i); END LOOP ;
708 END IF;
709 IF ParentLineId is not NULL and ParentLineId.Count <>0 THEN
710 FOR i IN 1 .. ln LOOP p_atp_table.Parent_Line_Id (i) := ParentLineId(i); END LOOP ;
711 END IF;
712 IF MatchItemId is not NULL and MatchItemId.Count <>0 THEN
713 FOR i IN 1 .. ln LOOP p_atp_table.Match_Item_Id (i) := MatchItemId(i); END LOOP ;
714 END IF;
715 IF ConfigItemLineId is not NULL and ConfigItemLineId.Count <>0 THEN
716 FOR i IN 1 .. ln LOOP p_atp_table.Config_Item_Line_Id (i) := ConfigItemLineId(i); END LOOP ;
717 END IF;
718 IF ValidationOrg is not NULL and ValidationOrg.Count <>0 THEN
719 FOR i IN 1 .. ln LOOP p_atp_table.Validation_Org (i) := ValidationOrg(i); END LOOP ;
720 END IF;
721 IF ComponentSequenceID is not NULL and ComponentSequenceID.Count <>0 THEN
722 FOR i IN 1 .. ln LOOP p_atp_table.Component_Sequence_ID (i) := ComponentSequenceID(i); END LOOP ;
723 END IF;
724 IF ComponentCode is not NULL and ComponentCode.Count <>0 THEN
725 FOR i IN 1 .. ln LOOP p_atp_table.Component_Code (i) := ComponentCode(i); END LOOP ;
726 END IF;
727 IF LineNumber is not NULL and LineNumber.Count <>0 THEN
728 FOR i IN 1 .. ln LOOP p_atp_table.Line_Number (i) := LineNumber(i); END LOOP ;
729 END IF;
730 IF IncludedItemFlag is not NULL and IncludedItemFlag.Count <>0 THEN
731 FOR i IN 1 .. ln LOOP p_atp_table.Included_Item_Flag (i) := IncludedItemFlag(i); END LOOP ;
732 END IF;
733 -----------------------------------------------------------------------------------------------------------------
734
735 IF PickComponentsFlag is not NULL and PickComponentsFlag.Count <>0 THEN
736 FOR i IN 1 .. ln LOOP p_atp_table.Pick_Components_Flag (i) := PickComponentsFlag(i); END LOOP ;
737 END IF;
738 IF CascadeModelInfoToComp is not NULL and CascadeModelInfoToComp.Count <>0 THEN
739 FOR i IN 1 .. ln LOOP p_atp_table.Cascade_Model_Info_To_Comp (i) := CascadeModelInfoToComp(i); END LOOP ;
740 END IF;
741 IF SequenceNumber is not NULL and SequenceNumber.Count <>0 THEN
742 FOR i IN 1 .. ln LOOP p_atp_table.Sequence_Number (i) := SequenceNumber(i); END LOOP ;
743 END IF;
744 IF InternalOrgId is not NULL and InternalOrgId.Count <>0 THEN
745 FOR i IN 1 .. ln LOOP p_atp_table.Internal_Org_Id (i) := InternalOrgId(i); END LOOP ;
746 END IF;
747 IF PartySiteId is not NULL and PartySiteId.Count <>0 THEN
748 FOR i IN 1 .. ln LOOP p_atp_table.Party_Site_Id (i) := PartySiteId(i); END LOOP ;
749 END IF;
750 IF PartOfSet is not NULL and PartOfSet.Count <>0 THEN
751 FOR i IN 1 .. ln LOOP p_atp_table.Part_Of_Set (i) := PartOfSet(i); END LOOP ;
752 END IF;
753
754 error_tracking_number:= 1190;
755
756 /* for i in 1..ln loop
757 dbms_output.put_line('Inventory_Item_Id = ' || p_atp_table.Inventory_Item_Id(i) );
758 dbms_output.put_line('Identifier = ' || p_atp_table.Identifier(i) );
759 dbms_output.put_line('Calling Module = ' || p_atp_table.Calling_Module(i) );
760 dbms_output.put_line('Q Ordered = ' || p_atp_table.Quantity_Ordered(i) );
761 dbms_output.put_line('Q UOM = ' || p_atp_table.Quantity_UOM(i) );
762 dbms_output.put_line('Req Ship Date = ' || p_atp_table.Requested_Ship_Date(i) );
763 dbms_output.put_line('Req Arrival Date = ' || p_atp_table.Requested_Arrival_Date(i) );
764 dbms_output.put_line('Action = ' || p_atp_table.Action(i)) ;
765 dbms_output.put_line('InstanceId = ' || p_atp_table.Instance_id(i)) ;
766 dbms_output.put_line('ShipMethod = ' || p_atp_table.Ship_Method(i)) ;
767 dbms_output.put_line('SrOrgId = ' || p_atp_table.Source_organization_id(i)) ;
768 dbms_output.put_line('orderNumber = ' || p_atp_table.Order_Number(i)) ;
769 dbms_output.put_line('OverrideFlag = ' || p_atp_table.Override_Flag(i)) ;
770 dbms_output.put_line('InsertFlag = ' || p_atp_table.Insert_Flag(i)) ;
771 end loop;
772 dbms_output.put_line('status = ' || status );
773 dbms_output.put_line('sessionId = ' || sessionId );*/
774
775 ------- STEP 5 -- Call pl/sqlp ATP
776 MRP_ATP_PUB.Call_ATP(
777 sessionId,
778 p_atp_table,
779 x_atp_table,
780 x_atp_supply_demand,
781 x_atp_period,
782 x_atp_details,
783 x_return_status,
784 x_msg_data,
785 x_msg_count);
786
787 error_tracking_number:= 200;
788
789 IF x_return_status = 'E' THEN
790 RAISE atpError;
791 END IF;
792
793 IF x_return_status = 'U' THEN
794 RAISE atpUnknownError;
795 END IF;
796
797
798 -- otherwise x_return_status = 'S' for success
799 ln := x_atp_table.Ship_Date.Count;
800 if ln = 0 then
801 ln := x_atp_table.Arrival_Date.Count;
802 end if;
803
804 error_tracking_number:= 2100;
805
806
807 IF ln > 0 THEN
808 InstanceIdOut.extend(ln);
809 FOR i IN 1 .. ln LOOP InstanceIdOut (i) := x_atp_table.Instance_Id(i); END LOOP ;
810 InventoryItemIdOut.extend(ln);
811 FOR i IN 1 .. ln LOOP InventoryItemIdOut (i) := x_atp_table.Inventory_Item_Id(i); END LOOP ;
812
813 InventoryItemNameOut.extend(ln);
814 FOR i IN 1 .. ln LOOP InventoryItemNameOut (i) := x_atp_table.Inventory_Item_Name(i); END LOOP ;
815 SourceOrganizationIdOut.extend(ln);
816 FOR i IN 1 .. ln LOOP SourceOrganizationIdOut (i) := x_atp_table.Source_Organization_Id(i); END LOOP ;
817 SourceOrganizationCodeOut.extend(ln);
818 FOR i IN 1 .. ln LOOP SourceOrganizationCodeOut (i) := x_atp_table.Source_Organization_Code(i); END LOOP ;
819
820 error_tracking_number:= 2103;
821
822 OrganizationIdOut.extend(ln);
823 FOR i IN 1 .. ln LOOP OrganizationIdOut (i) := x_atp_table.Organization_Id(i); END LOOP ;
824 IdentifierOut.extend(ln);
825 FOR i IN 1 .. ln LOOP IdentifierOut (i) := x_atp_table.Identifier(i); END LOOP ;
826 DemandSourceHeaderIdOut.extend(ln);
827 FOR i IN 1 .. ln LOOP DemandSourceHeaderIdOut (i) := x_atp_table.Demand_Source_Header_Id(i); END LOOP ;
828 DemandSourceDeliveryOut.extend(ln);
829 FOR i IN 1 .. ln LOOP DemandSourceDeliveryOut (i) := x_atp_table.Demand_Source_Delivery(i); END LOOP ;
830 DemandSourceTypeOut.extend(ln);
831 FOR i IN 1 .. ln LOOP DemandSourceTypeOut (i) := x_atp_table.Demand_Source_Type(i); END LOOP ;
832
833 error_tracking_number:= 2104;
834
835 CustomerIdOut.extend(ln);
836 FOR i IN 1 .. ln LOOP CustomerIdOut (i) := x_atp_table.Customer_Id(i); END LOOP ;
837 CustomerSiteIdOut.extend(ln);
838 FOR i IN 1 .. ln LOOP CustomerSiteIdOut (i) := x_atp_table.Customer_Site_Id(i); END LOOP ;
839 QuanitytOrderedOut.extend(ln);
840 FOR i IN 1 .. ln LOOP QuanitytOrderedOut (i) := x_atp_table.Quantity_Ordered(i); END LOOP ;
841 QuantityUOMOut.extend(ln);
842 FOR i IN 1 .. ln LOOP QuantityUOMOut (i) := x_atp_table.Quantity_UOM(i); END LOOP ;
843 RequestedShipDateOut.extend(ln);
844 FOR i IN 1 .. ln LOOP RequestedShipDateOut (i) := x_atp_table.Requested_Ship_Date(i); END LOOP ;
845 RequestedArrivalDateOut.extend(ln);
846 FOR i IN 1 .. ln LOOP RequestedArrivalDateOut (i) := x_atp_table.Requested_Arrival_Date(i); END LOOP ;
847 LatestAcceptableDateOut.extend(ln);
848 FOR i IN 1 .. ln LOOP LatestAcceptableDateOut (i) := x_atp_table.Latest_Acceptable_Date(i); END LOOP ;
849 DeliveryLeadTimeOut.extend(ln);
850 FOR i IN 1 .. ln LOOP DeliveryLeadTimeOut (i) := x_atp_table.Delivery_Lead_Time(i); END LOOP ;
851 ShipMethodOut.extend(ln);
852 FOR i IN 1 .. ln LOOP ShipMethodOut (i) := x_atp_table.Ship_Method(i); END LOOP ;
853 DemandClassOut.extend(ln);
854 FOR i IN 1 .. ln LOOP DemandClassOut (i) := x_atp_table.Demand_Class(i); END LOOP ;
855
856 error_tracking_number:= 2105;
857
858 ShipSetNameOut.extend(ln);
859 FOR i IN 1 .. ln LOOP ShipSetNameOut (i) := x_atp_table.Ship_Set_Name(i); END LOOP ;
860 ArrivalSetNameOut.extend(ln);
861 FOR i IN 1 .. ln LOOP ArrivalSetNameOut (i) :=x_atp_table. Arrival_Set_Name(i); END LOOP ;
862 OverrideFlagOut.extend(ln);
863 FOR i IN 1 .. ln LOOP OverrideFlagOut (i) := x_atp_table.Override_Flag(i); END LOOP ;
864 ShipDateOut.extend(ln);
865 FOR i IN 1 .. ln LOOP ShipDateOut (i) := x_atp_table.Ship_Date(i); END LOOP ;
866 ArrivalDateOut.extend(ln);
867 FOR i IN 1 .. ln LOOP ArrivalDateOut (i) := x_atp_table.Arrival_Date(i); END LOOP ;
868 AvailableQuantityOut.extend(ln);
869 FOR i IN 1 .. ln LOOP AvailableQuantityOut (i) := x_atp_table.Available_Quantity(i); END LOOP ;
870
871 AvailableQuantityOut.extend(ln);
872 FOR i IN 1 .. ln LOOP AvailableQuantityOut (i) := x_atp_table.Available_Quantity(i); END LOOP ;
873 RequestedDateQuantityOut.extend(ln);
874 FOR i IN 1 .. ln LOOP RequestedDateQuantityOut (i) := x_atp_table.Requested_Date_Quantity(i); END LOOP ;
875 GroupShipDateOut.extend(ln);
876 FOR i IN 1 .. ln LOOP GroupShipDateOut (i) := x_atp_table.Group_Ship_Date(i); END LOOP ;
877 GroupArrivalDateOut.extend(ln);
878 FOR i IN 1 .. ln LOOP GroupArrivalDateOut (i) := x_atp_table.Group_Arrival_Date(i); END LOOP ;
879
880 error_tracking_number:= 2106;
881
882 AtpLeadTimeOut.extend(ln);
883 FOR i IN 1 .. ln LOOP AtpLeadTimeOut (i) := x_atp_table.Atp_Lead_Time(i); END LOOP ;
884 ErrorCodeOut.extend(ln);
885 FOR i IN 1 .. ln LOOP ErrorCodeOut (i) := x_atp_table.Error_Code(i); END LOOP ;
886 EndPeggingIdOut.extend(ln);
887 FOR i IN 1 .. ln LOOP EndPeggingIdOut (i) :=x_atp_table.End_Pegging_Id(i); END LOOP ;
888 OldSourceOrganizationIdOut.extend(ln);
889 FOR i IN 1 .. ln LOOP OldSourceOrganizationIdOut (i) := x_atp_table.Old_Source_Organization_Id(i); END LOOP ;
890 OldDemandClassOut.extend(ln);
891 FOR i IN 1 .. ln LOOP OldDemandClassOut (i) := x_atp_table.Old_Demand_Class(i); END LOOP ;
892 RequestItemIdOut.extend(ln);
893 FOR i IN 1 .. ln LOOP RequestItemIdOut (i) := x_atp_table.Request_Item_Id(i); END LOOP ;
894 ReqItemReqDateQtyOut.extend(ln);
895 FOR i IN 1 .. ln LOOP ReqItemReqDateQtyOut (i) := x_atp_table.Req_Item_Req_Date_Qty(i); END LOOP ;
896 ReqItemAvailableDateQtyOut.extend(ln);
897 FOR i IN 1 .. ln LOOP ReqItemAvailableDateQtyOut (i) := x_atp_table.Req_Item_Available_Date_Qty(i); END LOOP ;
898 RequestItemNameOut.extend(ln);
899 FOR i IN 1 .. ln LOOP RequestItemNameOut (i) := x_atp_table.Request_Item_Name(i); END LOOP ;
900 OldInventoryItemIdOut.extend(ln);
901 FOR i IN 1 .. ln LOOP OldInventoryItemIdOut (i) := x_atp_table.Old_Inventory_Item_Id(i); END LOOP ;
902
903 error_tracking_number:= 2110;
904
905 SubstFlagOut.extend(ln);
906 FOR i IN 1 .. ln LOOP SubstFlagOut (i) := x_atp_table.Subst_Flag(i); END LOOP ;
907 BaseModelIdOut.extend(ln);
908 FOR i IN 1 .. ln LOOP BaseModelIdOut (i) := x_atp_table.Base_Model_Id(i); END LOOP ;
909 OssErrorCodeOut.extend(ln);
910 FOR i IN 1 .. ln LOOP OssErrorCodeOut (i) := x_atp_table.Oss_Error_Code(i); END LOOP ;
911 MatchedItemNameOut.extend(ln);
912 FOR i IN 1 .. ln LOOP MatchedItemNameOut (i) := x_atp_table.Matched_Item_Name(i); END LOOP ;
913 CascadeModelInfoToCompOut.extend(ln);
914 FOR i IN 1 .. ln LOOP CascadeModelInfoToCompOut (i) := x_atp_table.Cascade_Model_Info_To_Comp(i); END LOOP ;
915 PlanIdOut.extend(ln);
916 FOR i IN 1 .. ln LOOP PlanIdOut (i) := x_atp_table.Plan_Id(i); END LOOP ;
917
918 END IF;
919
920 error_tracking_number:= 2200;
921 status := 'SUCCESS';
922
923
924 EXCEPTION
925 WHEN atpParameterSizeError THEN
926 status := 'ERROR_WRONG ARRAY_SIZE IN ' || parameterName;
927 RETURN;
928 WHEN atpError THEN
929 status := 'ERROR_ATP';
930 RETURN;
931 WHEN atpProfilesError THEN
932 status := 'ERROR_PROFILES_FOR_USER_OR_RESPID_NOT_SET';
933 RETURN;
934 WHEN others THEN
935 status := 'ERROR_UNEXPECTED_'||error_tracking_number;
936 RETURN;
937
938 END GetPromiseDate;
939
940
941
942 PROCEDURE GetPromiseDate_Public(
943 status OUT nocopy VARCHAR2,
944 UserName IN VARCHAR2,
945 RespName IN VARCHAR2,
946 RespApplName IN VARCHAR2,
947 SecurityGroupName IN VARCHAR2,
948 Language IN VARCHAR2,
949 InstanceId IN MscNumberArr ,
950 InventoryItemId IN MscNumberArr,
951 InventoryItemName IN MscChar40Arr,
952 SourceOrganizationId IN MscNumberArr ,
953 SourceOrganizationCode IN MscChar7Arr ,
954 OrganizationId IN MscNumberArr ,
955 Identifier IN MscNumberArr ,
956 DemandSourceHeaderId IN MscNumberArr ,
957 DemandSourceDelivery IN MscChar30Arr ,
958 DemandSourceType IN MscNumberArr ,
959 CallingModule IN MscNumberArr ,
960 CustomerId IN MscNumberArr ,
961 CustomerSiteId IN MscNumberArr ,
962 QuantityOrdered IN MscNumberArr ,
963 QuantityUOM IN MscChar3Arr ,
964 RequestedShipDate IN MscDateArr,
965 RequestedArrivalDate IN MscDateArr ,
966 LatestAcceptableDate IN MscDateArr ,
967 DeliveryLeadTime IN MscNumberArr ,
968 ShipMethod IN MscChar30Arr ,
969 DemandClass IN MscChar30Arr ,
970 ShipSetName IN MscChar30Arr ,
971 ArrivalSetName IN MscChar30Arr ,
972 OverrideFlag IN MscChar1Arr ,
973 Action IN MscNumberArr ,
974 InsertFlag IN MscNumberArr ,
975 OEFlag IN MscChar1Arr ,
976 OrderNumber IN MscNumberArr ,
977 OldSourceOrganizationId IN MscNumberArr ,
978 OldDemandClass IN MscChar30Arr ,
979 Attribute01 IN MscNumberArr ,
980 Attribute02 IN MscNumberArr ,
981 CustomerCountry IN MscChar60Arr ,
982 CustomerState IN MscChar60Arr ,
983 CustomerCity IN MscChar60Arr ,
984 CustomerPostalCode IN MscChar60Arr ,
985 SubstitutionTypeCode IN MscNumberArr ,
986 ReqItemDetailFlag IN MscNumberArr ,
987 SalesRep IN MscChar255Arr ,
988 CustomerContact IN MscChar255Arr ,
989 TopModelLineId IN MscNumberArr ,
990 ATOParentModelLineId IN MscNumberArr ,
991 ATOModelLineId IN MscNumberArr ,
992 ParentLineId IN MscNumberArr ,
993 MatchItemId IN MscNumberArr ,
994 ConfigItemLineId IN MscNumberArr ,
995 ValidationOrg IN MscNumberArr ,
996 ComponentSequenceID IN MscNumberArr ,
997 ComponentCode IN MscChar255Arr ,
998 LineNumber IN MscChar80Arr ,
999 IncludedItemFlag IN MscNumberArr ,
1000 PickComponentsFlag IN MscChar1Arr ,
1001 CascadeModelInfoToComp IN MscNumberArr ,
1002 SequenceNumber IN MscNumberArr ,
1003 InternalOrgId IN MscNumberArr ,
1004 PartySiteId IN MscNumberArr ,
1005 PartOfSet IN MscChar1Arr ,
1006
1007 InstanceIdOut OUT nocopy MscNumberArr,
1008 InventoryItemIdOut OUT nocopy MscNumberArr,
1009 InventoryItemNameOut OUT nocopy MscChar40Arr,
1010 SourceOrganizationIdOut OUT nocopy MscNumberArr,
1011 SourceOrganizationCodeOut OUT nocopy MscChar7Arr,
1012 OrganizationIdOut OUT nocopy MscNumberArr,
1013 IdentifierOut OUT nocopy MscNumberArr,
1014 DemandSourceHeaderIdOut OUT nocopy MscNumberArr,
1015 DemandSourceDeliveryOut OUT nocopy MscChar30Arr,
1016 DemandSourceTypeOut OUT nocopy MscNumberArr,
1017 CustomerIdOut OUT nocopy MscNumberArr,
1018 CustomerSiteIdOut OUT nocopy MscNumberArr,
1019 QuanitytOrderedOut OUT nocopy MscNumberArr,
1020 QuantityUOMOut OUT nocopy MscChar3Arr,
1021 RequestedShipDateOut OUT nocopy MscDateArr,
1022 RequestedArrivalDateOut OUT nocopy MscDateArr,
1023 LatestAcceptableDateOut OUT nocopy MscDateArr,
1024 DeliveryLeadTimeOut OUT nocopy MscNumberArr,
1025 ShipMethodOut OUT nocopy MscChar30Arr,
1026 DemandClassOut OUT nocopy MscChar30Arr,
1027 ShipSetNameOut OUT nocopy MscChar30Arr,
1028 ArrivalSetNameOut OUT nocopy MscChar30Arr,
1029 OverrideFlagOut OUT nocopy MscChar1Arr,
1030 ShipDateOut OUT nocopy MscDateArr,
1031 ArrivalDateOut OUT nocopy MscDateArr,
1032 AvailableQuantityOut OUT nocopy MscNumberArr,
1033 RequestedDateQuantityOut OUT nocopy MscNumberArr,
1034 GroupShipDateOut OUT nocopy MscDateArr,
1035 GroupArrivalDateOut OUT nocopy MscDateArr,
1036 AtpLeadTimeOut OUT nocopy MscNumberArr,
1037 ErrorCodeOut OUT nocopy MscNumberArr,
1038 EndPeggingIdOut OUT nocopy MscNumberArr,
1039 OldSourceOrganizationIdOut OUT nocopy MscNumberArr,
1040 OldDemandClassOut OUT nocopy MscChar30Arr,
1041 RequestItemIdOut OUT nocopy MscNumberArr,
1042 ReqItemReqDateQtyOut OUT nocopy MscNumberArr,
1043 ReqItemAvailableDateOut OUT nocopy MscDateArr,
1044 ReqItemAvailableDateQtyOut OUT nocopy MscNumberArr,
1045 RequestItemNameOut OUT nocopy MscChar40Arr,
1046 OldInventoryItemIdOut OUT nocopy MscNumberArr,
1047 SubstFlagOut OUT nocopy MscNumberArr,
1048 BaseModelIdOut OUT nocopy MscNumberArr,
1049 OssErrorCodeOut OUT nocopy MscNumberArr,
1050 MatchedItemNameOut OUT nocopy MscChar255Arr,
1051 CascadeModelInfoToCompOut OUT nocopy MscNumberArr,
1052 PlanIdOut OUT nocopy MscNumberArr
1053 )AS
1054 userid number;
1055 respid number;
1056 l_String VARCHAR2(30);
1057 error_tracking_num number;
1058 l_SecutirtGroupId NUMBER;
1059 BEGIN
1060 InstanceIdOut := MscNumberArr();
1061 InventoryItemIdOut := MscNumberArr();
1062 InventoryItemNameOut := MscChar40Arr();
1063 SourceOrganizationIdOut := MscNumberArr();
1064 SourceOrganizationCodeOut := MscChar7Arr();
1065 OrganizationIdOut := MscNumberArr();
1066 IdentifierOut := MscNumberArr();
1067 DemandSourceHeaderIdOut := MscNumberArr();
1068 DemandSourceDeliveryOut := MscChar30Arr();
1069 DemandSourceTypeOut := MscNumberArr();
1070 CustomerIdOut := MscNumberArr();
1071 CustomerSiteIdOut := MscNumberArr();
1072 QuanitytOrderedOut := MscNumberArr();
1073 QuantityUOMOut := MscChar3Arr();
1074 RequestedShipDateOut := MscDateArr();
1075 RequestedArrivalDateOut := MscDateArr();
1076 LatestAcceptableDateOut := MscDateArr();
1077 DeliveryLeadTimeOut := MscNumberArr();
1078 ShipMethodOut := MscChar30Arr();
1079 DemandClassOut := MscChar30Arr();
1080 ShipSetNameOut := MscChar30Arr();
1081 ArrivalSetNameOut := MscChar30Arr();
1082 OverrideFlagOut := MscChar1Arr();
1083 ShipDateOut := MscDateArr();
1084 ArrivalDateOut := MscDateArr();
1085 AvailableQuantityOut := MscNumberArr();
1086 RequestedDateQuantityOut := MscNumberArr();
1087 GroupShipDateOut := MscDateArr();
1088 GroupArrivalDateOut := MscDateArr();
1089 AtpLeadTimeOut := MscNumberArr();
1090 ErrorCodeOut := MscNumberArr();
1091 EndPeggingIdOut := MscNumberArr();
1092 OldSourceOrganizationIdOut := MscNumberArr();
1093 OldDemandClassOut := MscChar30Arr();
1094 RequestItemIdOut := MscNumberArr();
1095 ReqItemReqDateQtyOut := MscNumberArr();
1096 ReqItemAvailableDateOut := MscDateArr();
1097 ReqItemAvailableDateQtyOut := MscNumberArr();
1098 RequestItemNameOut := MscChar40Arr();
1099 OldInventoryItemIdOut := MscNumberArr();
1100 SubstFlagOut := MscNumberArr();
1101 BaseModelIdOut := MscNumberArr();
1102 OssErrorCodeOut := MscNumberArr();
1103 MatchedItemNameOut := MscChar255Arr();
1104 CascadeModelInfoToCompOut := MscNumberArr();
1105 PlanIdOut := MscNumberArr();
1106 error_tracking_num :=2010;
1107 MSC_WS_COMMON.GET_PERMISSION_IDS(l_String, userid, respid, l_SecutirtGroupId, UserName, RespName, RespApplName, SecurityGroupName, Language);
1108 IF (l_String <> 'OK') THEN
1109 Status := l_String;
1110 RETURN;
1111 END IF;
1112
1113 error_tracking_num :=2030;
1114 MSC_WS_COMMON.VALIDATE_USER_RESP_FUNC(l_String, userid, respid, 'MSC_MSCOSCWB',l_SecutirtGroupId);
1115 IF (l_String <> 'OK') THEN
1116 Status := l_String;
1117 RETURN;
1118 END IF;
1119 error_tracking_num :=2040;
1120
1121 GetPromiseDate( Status,
1122 InstanceId ,
1123 InventoryItemId,
1124 InventoryItemName ,
1125 SourceOrganizationId ,
1126 SourceOrganizationCode ,
1127 OrganizationId ,
1128 Identifier ,
1129 DemandSourceHeaderId ,
1130 DemandSourceDelivery ,
1131 DemandSourceType ,
1132 CallingModule ,
1133 CustomerId ,
1134 CustomerSiteId,
1135 QuantityOrdered ,
1136 QuantityUOM ,
1137 RequestedShipDate,
1138 RequestedArrivalDate,
1139 LatestAcceptableDate ,
1140 DeliveryLeadTime,
1141 ShipMethod,
1142 DemandClass ,
1143 ShipSetName ,
1144 ArrivalSetName ,
1145 OverrideFlag ,
1146 Action ,
1147 InsertFlag ,
1148 OEFlag ,
1149 OrderNumber ,
1150 OldSourceOrganizationId,
1151 OldDemandClass ,
1152 Attribute01 ,
1153 Attribute02,
1154 CustomerCountry ,
1155 CustomerState ,
1156 CustomerCity ,
1157 CustomerPostalCode ,
1158 SubstitutionTypeCode ,
1159 ReqItemDetailFlag ,
1160 SalesRep ,
1161 CustomerContact ,
1162 TopModelLineId ,
1163 ATOParentModelLineId ,
1164 ATOModelLineId ,
1165 ParentLineId ,
1166 MatchItemId ,
1167 ConfigItemLineId ,
1168 ValidationOrg ,
1169 ComponentSequenceID ,
1170 ComponentCode ,
1171 LineNumber ,
1172 IncludedItemFlag ,
1173 PickComponentsFlag ,
1174 CascadeModelInfoToComp ,
1175 SequenceNumber,
1176 InternalOrgId ,
1177 PartySiteId ,
1178 PartOfSet ,
1179
1180 InstanceIdOut,
1181 InventoryItemIdOut,
1182 InventoryItemNameOut,
1183 SourceOrganizationIdOut ,
1184 SourceOrganizationCodeOut,
1185 OrganizationIdOut ,
1186 IdentifierOut,
1187 DemandSourceHeaderIdOut ,
1188 DemandSourceDeliveryOut,
1189 DemandSourceTypeOut,
1190 CustomerIdOut ,
1191 CustomerSiteIdOut,
1192 QuanitytOrderedOut,
1193 QuantityUOMOut ,
1194 RequestedShipDateOut ,
1195 RequestedArrivalDateOut ,
1196 LatestAcceptableDateOut,
1197 DeliveryLeadTimeOut ,
1198 ShipMethodOut ,
1199 DemandClassOut,
1200 ShipSetNameOut ,
1201 ArrivalSetNameOut,
1202 OverrideFlagOut,
1203 ShipDateOut ,
1204 ArrivalDateOut ,
1205 AvailableQuantityOut ,
1206 RequestedDateQuantityOut,
1207 GroupShipDateOut,
1208 GroupArrivalDateOut,
1209 AtpLeadTimeOut ,
1210 ErrorCodeOut ,
1211 EndPeggingIdOut ,
1212 OldSourceOrganizationIdOut,
1213 OldDemandClassOut,
1214 RequestItemIdOut,
1215 ReqItemReqDateQtyOut ,
1216 ReqItemAvailableDateOut,
1217 ReqItemAvailableDateQtyOut,
1218 RequestItemNameOut,
1219 OldInventoryItemIdOut ,
1220 SubstFlagOut ,
1221 BaseModelIdOut,
1222 OssErrorCodeOut,
1223 MatchedItemNameOut ,
1224 CascadeModelInfoToCompOut,
1225 PlanIdOut );
1226
1227
1228
1229 EXCEPTION
1230 WHEN others THEN
1231 status := 'ERROR_UNEXPECTED_'||error_tracking_num;
1232
1233 return;
1234 END GetPromiseDate_Public;
1235
1236
1237
1238
1239
1240
1241
1242
1243 END MSC_WS_ATP;
[Home] [Help]