1 PACKAGE BODY MRP_ATP_PUB AS
2 /* $Header: MRPEATPB.pls 115.55 2002/12/02 22:57:00 dsting ship $ */
3 G_PKG_NAME CONSTANT VARCHAR2(30) := 'MRP_ATP_PUB';
4
5 -- This package contains 2 procedures : Call_ATP and Call_ATP_No_Commit.
6 -- Call_ATP and Call_ATP_No_Commit are almost the same except
7 -- Call ATP is a automonous transaction which will commit the data.
8 -- Call_ATP_No_Commit will be used by backlog scheduling and Call_ATP will be
9 -- used by OM and all the other caller. In order to maintain this package
10 -- easier, Call_ATP actually calls the Call_ATP_No_Commit and then do a commit
11
12 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('MSC_ATP_DEBUG'), 'N');
13
14 PROCEDURE Call_ATP (
15 p_session_id IN OUT NoCopy NUMBER,
16 p_atp_rec IN MRP_ATP_PUB.ATP_Rec_Typ,
17 x_atp_rec OUT NoCopy MRP_ATP_PUB.ATP_Rec_Typ,
18 x_atp_supply_demand OUT NoCopy MRP_ATP_PUB.ATP_Supply_Demand_Typ,
19 x_atp_period OUT NoCopy MRP_ATP_PUB.ATP_Period_Typ,
20 x_atp_details OUT NoCopy MRP_ATP_PUB.ATP_Details_Typ,
21 x_return_status OUT NoCopy VARCHAR2,
22 x_msg_data OUT NoCopy VARCHAR2,
23 x_msg_count OUT NoCopy NUMBER
24 ) IS
25 --PRAGMA AUTONOMOUS_TRANSACTION;
26 i PLS_INTEGER;
27 counter1 NUMBER;
28 p_line_arr MRP_OM_API_PK.line_id_tbl;
29 x_return_status1 VARCHAR2(1);
30
31 BEGIN
32
33 MSC_ATP_PUB.Call_ATP(
34 p_session_id,
35 p_atp_rec,
36 x_atp_rec,
37 x_atp_supply_demand,
38 x_atp_period,
39 x_atp_details,
40 x_return_status,
41 x_msg_data,
42 x_msg_count);
43
44 /*------------------------------------------------------+
45 | Bug # 1916037 |
46 | Changes for Planning Manager performance improvement.|
47 +------------------------------------------------------*/
48
49 i := x_atp_rec.Action.FIRST;
50 IF (NVL(x_atp_rec.Action(i), -1) <> 100) THEN
51
52 -- This is NOT an ATP request.
53 -- Insert the Line Ids into a temporary
54 -- table to help the MRP Planning Manager
55 -- perform better.
56 -- Do This only if the Calling Module is OM.
57 -- We Insert the line_id only if the
58 -- scheduling request was successful or
59 -- the item is not ATPable.
60
61 IF PG_DEBUG in ('Y', 'C') THEN
62 msc_sch_wb.atp_debug('Call_ATP: ' || 'this is NOT an atp request');
63 END IF;
64 IF (NVL(x_atp_rec.Calling_module(i), -1) = 660) THEN
65 counter1 := 0;
66 FOR j IN 1..x_atp_rec.Action.LAST LOOP
67
68 IF (((NVL(x_atp_rec.Action(j), -1) = 110) OR
69 (NVL(x_atp_rec.Action(j), -1) = 120)) AND
70 ((NVL(x_atp_rec.Error_Code(j), -1) = 0 )OR
71 (NVL(x_atp_rec.Error_Code(j), -1) = 61 ) OR
72 (NVL(x_atp_rec.Error_Code(j), -1) = -99 )) ) THEN
73
74 counter1 := counter1 + 1;
75 p_line_arr(counter1) := x_atp_rec.identifier(j);
76
77 END IF;
78 END LOOP;
79 IF PG_DEBUG in ('Y', 'C') THEN
80 msc_sch_wb.atp_debug('Call_ATP: ' || 'Counter1 : ' || to_char(counter1));
81 END IF;
82 IF (counter1 > 0) THEN
83 MRP_OM_API_PK.MRP_OM_Interface (p_line_arr, x_return_status1);
84 IF PG_DEBUG in ('Y', 'C') THEN
85 msc_sch_wb.atp_debug('Call_ATP: ' || 'Return status from MRP_OM_Interface: '
86 || x_return_status1);
87 END IF;
88 END IF;
89 END IF;
90 END IF;
91
92
93 EXCEPTION
94
95 -- Error Handling changes.
96 WHEN MSC_ATP_PUB.ATP_INVALID_OBJECTS_FOUND THEN
97 x_return_status := NVL(x_return_status, FND_API.G_RET_STS_ERROR);
98 IF PG_DEBUG in ('Y', 'C') THEN
99 msc_sch_wb.atp_debug('Call_ATP: Invalid Objects found');
100 END IF;
101
102 IF (x_atp_rec.Inventory_item_id.COUNT = 0) THEN
103 x_atp_rec := p_atp_rec;
104 END IF;
105 FOR i IN 1..x_atp_rec.Calling_Module.LAST LOOP
106 IF ((NVL(x_atp_rec.Error_Code(i), -1)) in (-1,0,61,150)) THEN
107 x_atp_rec.Error_Code(i) := MSC_ATP_PVT.ATP_INVALID_OBJECTS;
108 END IF;
109 END LOOP;
110
111 WHEN others THEN
112 -- something wrong so we want to rollback;
113 IF PG_DEBUG in ('Y', 'C') THEN
114 msc_sch_wb.atp_debug('something wrong in Call_ATP');
115 END IF;
116 x_return_status := NVL(x_return_status, FND_API.G_RET_STS_ERROR);
117 IF PG_DEBUG in ('Y', 'C') THEN
118 msc_sch_wb.atp_debug('Call_ATP: ' || 'Return Status in excpetion : '||x_return_status);
119 END IF;
120 -- Bug 2072612 : krajan : 04/03/02
121 -- Commented rollback call.
122 -- ROLLBACK;
123
124 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
125 END Call_ATP;
126
127
128 PROCEDURE Call_ATP_No_Commit (
129 p_session_id IN OUT NoCopy NUMBER,
130 p_atp_rec IN MRP_ATP_PUB.ATP_Rec_Typ,
131 x_atp_rec OUT NoCopy MRP_ATP_PUB.ATP_Rec_Typ,
132 x_atp_supply_demand OUT NoCopy MRP_ATP_PUB.ATP_Supply_Demand_Typ,
133 x_atp_period OUT NoCopy MRP_ATP_PUB.ATP_Period_Typ,
134 x_atp_details OUT NoCopy MRP_ATP_PUB.ATP_Details_Typ,
135 x_return_status OUT NoCopy VARCHAR2,
136 x_msg_data OUT NoCopy VARCHAR2,
137 x_msg_count OUT NoCopy NUMBER
138 ) IS
139 i PLS_INTEGER;
140 counter1 NUMBER;
141 p_line_arr MRP_OM_API_PK.line_id_tbl;
142 x_return_status1 VARCHAR2(1);
143
144 BEGIN
145 MSC_ATP_PUB.Call_ATP_No_Commit(
146 p_session_id,
147 p_atp_rec,
148 x_atp_rec,
149 x_atp_supply_demand,
150 x_atp_period,
151 x_atp_details,
152 x_return_status,
153 x_msg_data,
154 x_msg_count);
155
156 /*------------------------------------------------------+
157 | Bug # 1916037 |
158 | Changes for Planning Manager performance improvement.|
159 +------------------------------------------------------*/
160
161 i := x_atp_rec.Action.FIRST;
162 IF (NVL(x_atp_rec.Action(i), -1) <> 100) THEN
163
164 -- This is NOT an ATP request.
165 -- Insert the Line Ids into a temporary
166 -- table to help the MRP Planning Manager
167 -- perform better.
168 -- Do This only if the Calling Module is
169 -- Backlog Scheduling Workbench.
170 -- We Insert the line_id only if the
171 -- scheduling request was successful or
172 -- the item is not ATPable.
173
174 IF PG_DEBUG in ('Y', 'C') THEN
175 msc_sch_wb.atp_debug('Call_ATP: ' || 'this is NOT an atp request');
176 END IF;
177 IF (NVL(x_atp_rec.Calling_module(i), -99) = -1 ) THEN
178 counter1 := 0;
179 FOR j IN 1..x_atp_rec.Action.LAST LOOP
180
181 IF (((NVL(x_atp_rec.Action(j), -1) = 110) OR
182 (NVL(x_atp_rec.Action(j), -1) = 120)) AND
183 ((NVL(x_atp_rec.Error_Code(j), -1) = 0 )OR
184 (NVL(x_atp_rec.Error_Code(j), -1) = 61 ) OR
185 (NVL(x_atp_rec.Error_Code(j), -1) = -99 )) ) THEN
186
187 counter1 := counter1 + 1;
188 p_line_arr(counter1) := x_atp_rec.identifier(j);
189
190 END IF;
191 END LOOP;
192 IF PG_DEBUG in ('Y', 'C') THEN
193 msc_sch_wb.atp_debug('Call_ATP: ' || 'Counter1 : ' || to_char(counter1));
194 END IF;
195 IF (counter1 > 0) THEN
196 MRP_OM_API_PK.MRP_OM_Interface (p_line_arr, x_return_status1);
197 IF PG_DEBUG in ('Y', 'C') THEN
198 msc_sch_wb.atp_debug('Call_ATP: ' || 'Return status from MRP_OM_Interface: '
199 || x_return_status1);
200 END IF;
201 END IF;
202 END IF;
203 END IF;
204
205
206
207 EXCEPTION
208 WHEN others THEN
209 x_return_status := NVL(x_return_status, FND_API.G_RET_STS_ERROR);
210 IF PG_DEBUG in ('Y', 'C') THEN
211 msc_sch_wb.atp_debug('Error in Call_ATP_No_Commit :'||sqlcode);
212 msc_sch_wb.atp_debug('Call_ATP: ' || sqlerrm);
213 msc_sch_wb.atp_debug('Call_ATP: ' || 'shipset count ' ||p_atp_rec.error_code.count);
214 msc_sch_wb.atp_debug('Call_ATP: ' || 'Exception x_return_status : '||x_return_status);
215 END IF;
216 -- Bug 2072612 : krajan : 04/03/02
217 -- Commented rollback call.
218 -- ROLLBACK;
219
220 END Call_ATP_No_Commit;
221
222
223 END MRP_ATP_PUB;