[Home] [Help]
PACKAGE BODY: APPS.MRP_ATP_UTILS
Source
1 PACKAGE BODY MRP_ATP_UTILS AS
2 /* $Header: MRPUATPB.pls 115.39 2004/03/16 00:43:33 vghiya ship $ */
3
4 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('MSC_ATP_DEBUG'), 'N');
5
6 PROCEDURE put_into_temp_table(
7 x_dblink IN VARCHAR2,
8 x_session_id IN NUMBER,
9 x_atp_rec IN MRP_ATP_PUB.atp_rec_typ,
10 x_atp_supply_demand IN MRP_ATP_PUB.ATP_Supply_Demand_Typ,
11 x_atp_period IN MRP_ATP_PUB.ATP_Period_Typ,
12 x_atp_details IN MRP_ATP_PUB.ATP_Details_Typ,
13 x_mode IN NUMBER,
14 x_return_status OUT NoCopy VARCHAR2,
15 x_msg_data OUT NoCopy VARCHAR2,
16 x_msg_count OUT NoCopy NUMBER
17 ) IS
18 -- PRAGMA AUTONOMOUS_TRANSACTION;
19 BEGIN
20 MSC_ATP_UTILS.put_into_temp_table(
21 x_dblink,
22 x_session_id,
23 x_atp_rec,
24 x_atp_supply_demand,
25 x_atp_period,
26 x_atp_details,
27 x_mode,
28 x_return_status,
29 x_msg_data,
30 x_msg_count);
31 EXCEPTION
32 WHEN OTHERS THEN
33 IF PG_DEBUG in ('Y', 'C') THEN
34 msc_sch_wb.atp_debug('put_into_temp_table: ' || ' Error in MRPUATPB.pls '||substr(sqlerrm,1,100));
35 END IF;
36 x_msg_data := substr(sqlerrm,1,100);
37 x_return_status := FND_API.G_RET_STS_ERROR;
38 END put_into_temp_table;
39
40
41 PROCEDURE get_from_temp_table(
42 x_dblink IN VARCHAR2,
43 x_session_id IN NUMBER,
44 x_atp_rec OUT NoCopy MRP_ATP_PUB.atp_rec_typ,
45 x_atp_supply_demand OUT NoCopy MRP_ATP_PUB.ATP_Supply_Demand_Typ,
46 x_atp_period OUT NoCopy MRP_ATP_PUB.ATP_Period_Typ,
47 x_atp_details OUT NoCopy MRP_ATP_PUB.ATP_Details_Typ,
48 x_mode IN NUMBER,
49 x_return_status OUT NoCopy VARCHAR2,
50 x_msg_data OUT NoCopy VARCHAR2,
51 x_msg_count OUT NoCopy NUMBER
52 ) IS
53 BEGIN
54 MSC_ATP_UTILS.get_from_temp_table(
55 x_dblink,
56 x_session_id,
57 x_atp_rec,
58 x_atp_supply_demand,
59 x_atp_period,
60 x_atp_details,
61 x_mode,
62 x_return_status,
63 x_msg_data,
64 x_msg_count,
65 1);
66 EXCEPTION
67 WHEN OTHERS THEN
68 x_return_status := FND_API.G_RET_STS_ERROR;
69 END get_from_temp_table;
70
71 FUNCTION Call_ATP_11(
72 p_group_id NUMBER,
73 p_session_id NUMBER,
74 p_insert_flag NUMBER,
75 p_partial_flag NUMBER,
76 p_err_message IN OUT NoCopy VARCHAR2)
77 RETURN NUMBER is
78
79 v_dummy NUMBER := 0;
80 x_atp_rec MRP_ATP_PUB.atp_rec_typ;
81 x_atp_rec_out MRP_ATP_PUB.atp_rec_typ;
82 x_atp_supply_demand MRP_ATP_PUB.ATP_Supply_Demand_Typ;
83 x_atp_period MRP_ATP_PUB.ATP_Period_Typ;
84 x_atp_details MRP_ATP_PUB.ATP_Details_Typ;
85 x_return_status VARCHAR2(1);
86 x_msg_data VARCHAR2(200);
87 x_msg_count NUMBER;
88 x_session_id NUMBER;
89
90 ato_exists VARCHAR2(1) := 'N';
91 j NUMBER;
92
93 BEGIN
94 --bug 3346564: null out the function as its not used any more.
95 return null;
96 EXCEPTION
97 WHEN OTHERS THEN
98 p_err_message := substr(sqlerrm,1,100);
99 return(INV_EXTATP_GRP.G_RETURN_ERROR);
100 End Call_ATP_11;
101
102 PROCEDURE extend_mast( mast_rec IN OUT NoCopy mrp_atp_schedule_temp_typ,
103 x_ret_code OUT NoCopy varchar2,
104 x_ret_status OUT NoCopy varchar2) IS
105 BEGIN
106 mast_rec.rowid_char.extend(1);
107 mast_rec.sequence_number.extend(1);
108 mast_rec.firm_flag.extend(1);
109 mast_rec.order_line_number.extend(1);
110 mast_rec.option_number.extend(1);
111 mast_rec.shipment_number.extend(1);
112 mast_rec.item_desc.extend(1);
113 mast_rec.customer_name.extend(1);
114 mast_rec.customer_location.extend(1);
115 mast_rec.ship_set_name.extend(1);
116 mast_rec.arrival_set_name.extend(1);
117 mast_rec.requested_ship_date.extend(1);
118 mast_rec.requested_arrival_date.extend(1);
119 mast_rec.old_line_schedule_date.extend(1);
120 mast_rec.old_source_organization_code.extend(1);
121 mast_rec.firm_source_org_id.extend(1);
122 mast_rec.firm_source_org_code.extend(1);
123 mast_rec.firm_ship_date.extend(1);
124 mast_rec.firm_arrival_date.extend(1);
125 mast_rec.ship_method_text.extend(1);
126 mast_rec.ship_set_id.extend(1);
127 mast_rec.arrival_set_id.extend(1);
128 mast_rec.project_id.extend(1);
129 mast_rec.task_id.extend(1);
130 mast_rec.project_number.extend(1);
131 mast_rec.task_number.extend(1);
132 EXCEPTION
133 WHEN OTHERS THEN
134 IF PG_DEBUG in ('Y', 'C') THEN
135 msc_sch_wb.atp_debug('Excp in extend_mast : '||Substr(Sqlerrm,1,100));
136 END IF;
137 END extend_mast;
138
139
140 PROCEDURE trim_mast( mast_rec IN OUT NoCopy mrp_atp_schedule_temp_typ,
141 x_ret_code OUT NoCopy varchar2,
142 x_ret_status OUT NoCopy varchar2) IS
143 BEGIN
144 mast_rec.rowid_char.trim(1);
145 mast_rec.sequence_number.trim(1);
146 mast_rec.firm_flag.trim(1);
147 mast_rec.order_line_number.trim(1);
148 mast_rec.option_number.trim(1);
149 mast_rec.shipment_number.trim(1);
150 mast_rec.item_desc.trim(1);
151 mast_rec.customer_name.trim(1);
152 mast_rec.customer_location.trim(1);
153 mast_rec.ship_set_name.trim(1);
154 mast_rec.arrival_set_name.trim(1);
155 mast_rec.requested_ship_date.trim(1);
156 mast_rec.requested_arrival_date.trim(1);
157 mast_rec.old_line_schedule_date.trim(1);
158 mast_rec.old_source_organization_code.trim(1);
159 mast_rec.firm_source_org_id.trim(1);
160 mast_rec.firm_source_org_code.trim(1);
161 mast_rec.firm_ship_date.trim(1);
162 mast_rec.firm_arrival_date.trim(1);
163 mast_rec.ship_method_text.trim(1);
164 mast_rec.ship_set_id.trim(1);
165 mast_rec.arrival_set_id.trim(1);
166 mast_rec.project_id.trim(1);
167 mast_rec.task_id.trim(1);
168 mast_rec.project_number.trim(1);
169 mast_rec.task_number.trim(1);
170 EXCEPTION
171 WHEN OTHERS THEN
172 IF PG_DEBUG in ('Y', 'C') THEN
173 msc_sch_wb.atp_debug('Excp in trim_mast : '||Substr(Sqlerrm,1,100));
174 END IF;
175 END trim_mast;
176
177 PROCEDURE test(x_session_id NUMBER) IS
178 j NUMBER := 1;
179 l_dynstring VARCHAR2(128) := NULL;
180 sql_stmt VARCHAR2(10000);
181 mast_rec mrp_atp_schedule_temp_typ;
182 mast_rec_insert mrp_atp_schedule_temp_typ;
183 TYPE mastcurtyp IS REF CURSOR;
184 mast_cursor mastcurtyp;
185 x_ret_code VARCHAR2(1);
186 x_ret_status VARCHAR2(100);
187
188 BEGIN
189
190 sql_stmt :=
191 ' SELECT '||
192 ' rowidtochar(rowid),'||
193 ' sequence_number,'||
194 ' firm_flag,'||
195 ' order_line_number,'||
196 ' option_number,'||
197 ' shipment_number,'||
198 ' item_desc,'||
199 ' customer_name,'||
200 ' customer_location,'||
201 ' ship_set_name,'||
202 ' arrival_set_name,'||
203 ' old_line_schedule_date,'||
204 ' old_source_organization_code,'||
205 ' project_id,'||
206 ' task_id,'||
207 ' project_number,'||
208 ' task_number '||
209 ' FROM '||
210 ' mrp_atp_schedule_temp'||l_dynstring||
211 ' WHERE session_id = :x_session_id '||
212 ' AND status_flag = 1';
213
214 OPEN mast_cursor FOR sql_stmt using x_session_id;
215
216 LOOP
217 IF PG_DEBUG in ('Y', 'C') THEN
218 msc_sch_wb.atp_debug('test: ' || ' Retrieved record from mast ');
219 END IF;
220 extend_mast(mast_rec, x_ret_code, x_ret_status);
221 IF PG_DEBUG in ('Y', 'C') THEN
222 msc_sch_wb.atp_debug('test: ' || ' error code '||x_ret_status||' '||x_ret_code);
223 END IF;
224 FETCH mast_cursor INTO
225 mast_rec.rowid_char(j),
226 mast_rec.sequence_number(j),
227 mast_rec.firm_flag(j),
228 mast_rec.order_line_number(j),
229 mast_rec.option_number(j),
230 mast_rec.shipment_number(j),
231 mast_rec.item_desc(j),
232 mast_rec.customer_name(j),
233 mast_rec.customer_location(j),
234 mast_rec.ship_set_name(j),
235 mast_rec.arrival_set_name(j),
236 mast_rec.old_line_schedule_date(j),
237 mast_rec.old_source_organization_code(j),
238 mast_rec.project_id(j),
239 mast_rec.task_id(j),
240 mast_rec.project_number(j),
241 mast_rec.task_number(j);
242 EXIT WHEN mast_cursor%notfound;
243 END LOOP;
244 trim_mast(mast_rec,x_ret_code, x_ret_status);
245 IF PG_DEBUG in ('Y', 'C') THEN
246 msc_sch_wb.atp_debug('test: ' || ' Count '||mast_rec.rowid_char.COUNT);
247 END IF;
248
249 END test;
250
251
252 END MRP_ATP_UTILS;