1 PACKAGE MRP_ATP_UTILS AUTHID CURRENT_USER AS
2 /* $Header: MRPUATPS.pls 120.0 2005/05/25 03:44:18 appldev noship $ */
3
4 TYPE number_arr IS TABLE OF number;
5 TYPE char3_arr IS TABLE OF varchar2(3);
6 TYPE char7_arr IS TABLE OF varchar2(7);
7 TYPE char10_arr IS TABLE of varchar2(10);
8 TYPE char18_arr IS TABLE of varchar2(18);
9 TYPE char25_arr IS TABLE of varchar2(25);
10 TYPE char30_arr IS TABLE of varchar2(30);
11 TYPE char40_arr IS TABLE of varchar2(40);
12 TYPE char50_arr IS TABLE of varchar2(50);
13 TYPE char240_arr IS TABLE of varchar2(240);
14 TYPE char255_arr IS TABLE of varchar2(255);--3991728
15 TYPE date_arr IS TABLE OF date;
16 TYPE SchedCurTyp IS REF CURSOR;
17
18 -- cchen: add the following two types to replace mrp_atp_schedule_temp%ROWTYPE
19 -- and mrp_atp_details_temp%ROWTYPE since they don't work for distributed atp
20 -- if the table definitions are not the same.
21
22 TYPE Details_Temp is RECORD
23 (SESSION_ID NUMBER
24 ,ORDER_LINE_ID NUMBER
25 ,PEGGING_ID NUMBER
26 ,PARENT_PEGGING_ID NUMBER
27 ,ATP_LEVEL NUMBER
28 ,REQUEST_ITEM_ID NUMBER
29 ,INVENTORY_ITEM_ID NUMBER
30 ,INVENTORY_ITEM_NAME VARCHAR2(40)
31 ,ORGANIZATION_ID NUMBER
32 ,ORGANIZATION_CODE VARCHAR2(7)
33 ,DEPARTMENT_ID NUMBER
34 ,DEPARTMENT_CODE VARCHAR2(10)
35 ,RESOURCE_ID NUMBER
36 ,RESOURCE_CODE VARCHAR2(10)
37 ,SUPPLIER_ID NUMBER
38 ,SUPPLIER_NAME VARCHAR2(80)
39 ,SUPPLIER_SITE_ID NUMBER
40 ,SUPPLIER_SITE_NAME VARCHAR2(15)
41 ,FROM_ORGANIZATION_ID NUMBER
42 ,FROM_ORGANIZATION_CODE VARCHAR2(3)
43 ,FROM_LOCATION_ID NUMBER
44 ,FROM_LOCATION_CODE VARCHAR2(20)
45 ,TO_ORGANIZATION_ID NUMBER
46 ,TO_ORGANIZATION_CODE VARCHAR2(3)
47 ,TO_LOCATION_ID NUMBER
48 ,TO_LOCATION_CODE VARCHAR2(20)
49 ,SHIP_METHOD VARCHAR2(30)
50 ,UOM_CODE VARCHAR2(3)
51 ,IDENTIFIER1 NUMBER
52 ,IDENTIFIER2 NUMBER
53 ,IDENTIFIER3 NUMBER
54 ,IDENTIFIER4 NUMBER
55 ,SUPPLY_DEMAND_TYPE NUMBER
56 ,SUPPLY_DEMAND_DATE DATE
57 ,SUPPLY_DEMAND_QUANTITY NUMBER
58 ,SUPPLY_DEMAND_SOURCE_TYPE NUMBER
59 ,ALLOCATED_QUANTITY NUMBER
60 ,SOURCE_TYPE NUMBER
61 ,RECORD_TYPE NUMBER
62 ,TOTAL_SUPPLY_QUANTITY NUMBER
63 ,TOTAL_DEMAND_QUANTITY NUMBER
64 ,PERIOD_START_DATE DATE
65 ,PERIOD_QUANTITY NUMBER
66 ,CUMULATIVE_QUANTITY NUMBER
67 ,WEIGHT_CAPACITY NUMBER
68 ,VOLUME_CAPACITY NUMBER
69 ,WEIGHT_UOM VARCHAR2(3)
70 ,VOLUME_UOM VARCHAR2(3)
71 ,PERIOD_END_DATE DATE
72 ,SCENARIO_ID NUMBER
73 ,DISPOSITION_TYPE NUMBER
74 ,DISPOSITION_NAME VARCHAR2(80)
75 ,REQUEST_ITEM_NAME VARCHAR2(40)
76 ,SUPPLY_DEMAND_SOURCE_TYPE_NAME VARCHAR2(80)
77 ,END_PEGGING_ID NUMBER
78 );
79
80 TYPE Schedule_Temp is RECORD
81 (ACTION NUMBER
82 ,CALLING_MODULE NUMBER
83 ,SESSION_ID NUMBER
84 ,ORDER_HEADER_ID NUMBER
85 ,ORDER_LINE_ID NUMBER
86 ,INVENTORY_ITEM_ID NUMBER
87 ,ORGANIZATION_ID NUMBER
88 ,SR_INSTANCE_ID NUMBER
89 ,ORGANIZATION_CODE VARCHAR2(7)
90 ,ORDER_NUMBER NUMBER
91 ,SOURCE_ORGANIZATION_ID NUMBER
92 ,CUSTOMER_ID NUMBER
93 ,CUSTOMER_SITE_ID NUMBER
94 ,DESTINATION_TIME_ZONE VARCHAR2(30)
95 ,QUANTITY_ORDERED NUMBER
96 ,UOM_CODE VARCHAR2(3)
97 ,REQUESTED_SHIP_DATE DATE
98 ,REQUESTED_ARRIVAL_DATE DATE
99 ,LATEST_ACCEPTABLE_DATE DATE
100 ,DELIVERY_LEAD_TIME NUMBER
101 ,FREIGHT_CARRIER VARCHAR2(30)
102 ,SHIP_METHOD VARCHAR2(30)
103 ,DEMAND_CLASS VARCHAR2(30)
104 ,SHIP_SET_NAME VARCHAR2(30)
105 ,SHIP_SET_ID NUMBER
106 ,ARRIVAL_SET_NAME VARCHAR2(30)
107 ,ARRIVAL_SET_ID NUMBER
108 ,OVERRIDE_FLAG VARCHAR2(1)
109 ,SCHEDULED_SHIP_DATE DATE
110 ,SCHEDULED_ARRIVAL_DATE DATE
111 ,AVAILABLE_QUANTITY NUMBER
112 ,REQUESTED_DATE_QUANTITY NUMBER
113 ,GROUP_SHIP_DATE DATE
114 ,GROUP_ARRIVAL_DATE DATE
115 ,VENDOR_ID NUMBER
116 ,VENDOR_SITE_ID NUMBER
117 ,INSERT_FLAG NUMBER
118 ,ERROR_CODE VARCHAR2(240)
119 ,ERROR_MESSAGE VARCHAR2(240)
120 ,SEQUENCE_NUMBER NUMBER
121 ,FIRM_FLAG NUMBER
122 ,INVENTORY_ITEM_NAME VARCHAR2(40)
123 ,SOURCE_ORGANIZATION_CODE VARCHAR2(7)
124 ,INSTANCE_ID1 NUMBER
125 ,ORDER_LINE_NUMBER NUMBER
126 ,SHIPMENT_NUMBER NUMBER
127 ,OPTION_NUMBER NUMBER
128 ,PROMISE_DATE DATE
129 --,CUSTOMER_NAME VARCHAR2(50)
130 ,CUSTOMER_NAME VARCHAR2(255) --3991728
131 ,CUSTOMER_LOCATION VARCHAR2(40)
132 ,OLD_LINE_SCHEDULE_DATE DATE
133 ,OLD_SOURCE_ORGANIZATION_CODE VARCHAR2(7)
134 ,SCENARIO_ID NUMBER
135 ,VENDOR_NAME VARCHAR2(80)
136 ,VENDOR_SITE_NAME VARCHAR2(240)
137 ,STATUS_FLAG NUMBER
138 ,MDI_ROWID VARCHAR2(30)
139 ,DEMAND_SOURCE_TYPE NUMBER
140 ,DEMAND_SOURCE_DELIVERY VARCHAR2(30)
141 ,ATP_LEAD_TIME NUMBER
142 ,OE_FLAG VARCHAR2(1)
143 ,ITEM_DESC VARCHAR2(240)
144 ,INTRANSIT_LEAD_TIME NUMBER
145 ,SHIP_METHOD_TEXT VARCHAR2(240)
146 ,END_PEGGING_ID NUMBER
147 ,PROJECT_ID NUMBER
148 ,TASK_ID NUMBER
149 ,PROJECT_NUMBER VARCHAR2(25)
150 ,TASK_NUMBER VARCHAR2(25)
151 ,OLD_SOURCE_ORGANIZATION_ID NUMBER
152 ,OLD_DEMAND_CLASS VARCHAR2(30)
153 ,EXCEPTION1 NUMBER
154 ,EXCEPTION2 NUMBER
155 ,EXCEPTION3 NUMBER
156 ,EXCEPTION4 NUMBER
157 ,EXCEPTION5 NUMBER
158 ,EXCEPTION6 NUMBER
159 ,EXCEPTION7 NUMBER
160 ,EXCEPTION8 NUMBER
161 ,EXCEPTION9 NUMBER
162 ,EXCEPTION10 NUMBER
163 ,EXCEPTION11 NUMBER
164 ,EXCEPTION12 NUMBER
165 ,EXCEPTION13 NUMBER
166 ,EXCEPTION14 NUMBER
167 ,EXCEPTION15 NUMBER
168 ,ATTRIBUTE_06 VARCHAR2(1)
169 ,SUBSTITUTION_TYP_CODE NUMBER
170 ,REQ_ITEM_DETAIL_FLAG NUMBER
171 ,OLD_INVENTORY_ITEM_ID NUMBER
172 ,REQUEST_ITEM_ID NUMBER
173 ,REQUEST_ITEM_NAME VARCHAR2(40)
174 ,REQ_ITEM_AVAILABLE_DATE DATE
175 ,REQ_ITEM_AVAILABLE_DATE_QTY NUMBER
176 ,REQ_ITEM_REQ_DATE_QTY NUMBER
177 ,SALES_REP VARCHAR2(255)
178 ,CUSTOMER_CONTACT VARCHAR2(255)
179 ,SUBST_FLAG NUMBER
180 --diag-atp
181 ,diagnostic_atp_flag NUMBER
182 ,internal_org_id NUMBER --3409286
183 );
184
185 SYS_YES CONSTANT NUMBER := 1;
186 SYS_NO CONSTANT NUMBER := 2;
187 REQUEST_MODE CONSTANT NUMBER := 1;
188 RESULTS_MODE CONSTANT NUMBER := 2;
189
190 TYPE ATP_Period_String_typ is Record
191 (
192 Total_Supply_Quantity number_arr := number_arr(),
193 Total_Demand_Quantity number_arr := number_arr(),
194 Period_Start_Date date_arr := date_arr(),
195 Period_End_Date date_arr := date_arr(),
196 Period_Quantity number_arr := number_arr(),
197 Cumulative_Quantity number_arr := number_arr()
198 );
199
200 TYPE mrp_atp_schedule_temp_typ IS RECORD
201 (
202 rowid_char char18_arr := char18_arr(),
203 sequence_number number_arr := number_arr(),
204 firm_flag number_arr := number_arr(),
205 order_line_number number_arr := number_arr(),
206 option_number number_arr := number_arr(),
207 shipment_number number_arr := number_arr(),
208 item_desc char240_arr := char240_arr(),
209 --customer_name char50_arr := char50_arr(),
210 customer_name char255_arr := char255_arr(), --3991728
211 customer_location char40_arr := char40_arr(),
212 ship_set_name char30_arr := char30_arr(),
213 arrival_set_name char30_arr := char30_arr(),
214 requested_ship_date date_arr := date_arr(),
215 -- when firming, we put the firm date here for the api
216 requested_arrival_date date_arr := date_arr(),
217 -- we need to write the correct one back from the table.
218 old_line_schedule_date date_arr := date_arr(),
219 old_source_organization_code char7_arr := char7_arr(),
220 firm_source_org_id number_arr := number_arr(),
221 firm_source_org_code char7_arr := char7_arr(),
222 firm_ship_date date_arr := date_arr(),
223 firm_arrival_date date_arr := date_arr(),
224 ship_method_text char240_arr := char240_arr(),
225 ship_set_id number_arr := number_arr(),
226 arrival_set_id number_arr := number_arr(),
227 PROJECT_ID number_arr := number_arr(),
228 TASK_ID number_arr := number_arr(),
229 PROJECT_NUMBER char25_arr := char25_arr(),
230 TASK_NUMBER char25_arr := char25_arr()
231 );
232
233
234 PROCEDURE put_into_temp_table
235 (
236 x_dblink IN VARCHAR2,
237 x_session_id IN NUMBER,
238 x_atp_rec IN MRP_ATP_PUB.atp_rec_typ,
239 x_atp_supply_demand IN MRP_ATP_PUB.ATP_Supply_Demand_Typ,
240 x_atp_period IN MRP_ATP_PUB.ATP_Period_Typ,
241 x_atp_details IN MRP_ATP_PUB.ATP_Details_Typ,
242 x_mode IN NUMBER,
243 x_return_status OUT NoCopy VARCHAR2,
244 x_msg_data OUT NoCopy VARCHAR2,
245 x_msg_count OUT NoCopy NUMBER
246 );
247
248 PROCEDURE get_from_temp_table
249 (
250 x_dblink IN VARCHAR2,
251 x_session_id IN NUMBER,
252 x_atp_rec OUT NoCopy MRP_ATP_PUB.atp_rec_typ,
253 x_atp_supply_demand OUT NoCopy MRP_ATP_PUB.ATP_Supply_Demand_Typ,
254 x_atp_period OUT NoCopy MRP_ATP_PUB.ATP_Period_Typ,
255 x_atp_details OUT NoCopy MRP_ATP_PUB.ATP_Details_Typ,
256 x_mode IN NUMBER,
257 x_return_status OUT NoCopy VARCHAR2,
258 x_msg_data OUT NoCopy VARCHAR2,
259 x_msg_count OUT NoCopy NUMBER
260 );
261
262 FUNCTION Call_ATP_11(
263 p_group_id NUMBER,
264 p_session_id NUMBER,
265 p_insert_flag NUMBER,
266 p_partial_flag NUMBER,
267 p_err_message IN OUT NoCopy VARCHAR2) RETURN NUMBER;
268
269
270 PROCEDURE extend_mast
271 (
272 mast_rec IN OUT NoCopy mrp_atp_schedule_temp_typ,
273 x_ret_code OUT NoCopy varchar2,
274 x_ret_status OUT NoCopy varchar2);
275
276 PROCEDURE trim_mast( mast_rec IN OUT NoCopy mrp_atp_schedule_temp_typ,
277 x_ret_code OUT NoCopy varchar2,
278 x_ret_status OUT NoCopy varchar2);
279 PROCEDURE test(x_session_id NUMBER);
280
281
282 END MRP_ATP_UTILS;
[Home] [Help]