1 PACKAGE CSP_SCH_INT_PVT AUTHID CURRENT_USER AS
2 /* $Header: cspgscis.pls 120.7.12020000.3 2012/10/09 11:43:54 shadas ship $ */
3
4 -- Start of Comments
5 -- Package name : CSP_SCH_INT_PVT
6 -- Purpose :
7 -- History :
8 -- NOTE :
9 -- End of Comments
10
11
12 G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSP_SCH_INT_PVT';
13 G_FILE_NAME CONSTANT VARCHAR2(12) := 'cspgscis.pls';
14
15 TYPE csp_sch_resources_rec_typ IS RECORD(resource_id NUMBER
16 ,resource_type VARCHAR2(30));
17
18 TYPE csp_sch_options_rec_typ IS RECORD(resource_id NUMBER
19 ,resource_type VARCHAR2(30)
20 ,start_time DATE
21 ,transfer_cost NUMBER
22 ,missing_parts NUMBER
23 ,available_parts NUMBER
24 ,src_warehouse VARCHAR2(4000) -- org code (3 char) + '.' (1) char + 10 subinv
25 ,ship_method VARCHAR2(4000)
26 ,distance_str VARCHAR2(4000));
27
28 TYPE csp_sch_interval_rec_typ IS RECORD(earliest_time DATE,
29 latest_time DATE);
30
31 TYPE CSP_PARTS_REC_TYPE IS RECORD(item_id NUMBER
32 ,item_uom VARCHAR2(3)
33 ,revision VARCHAR2(3)
34 ,quantity NUMBER
35 ,ship_set_name VARCHAR2(30)
36 ,line_id NUMBER);
37
38 TYPE CSP_UNAVAILABILITY_REC_TYPE IS RECORD(resource_id NUMBER
39 ,resource_type VARCHAR2(30)
40 ,organization_id NUMBER
41 ,item_id NUMBER
42 ,revision VARCHAR2(3)
43 ,item_uom VARCHAR2(3)
44 ,item_type NUMBER
45 ,line_id NUMBER
46 ,quantity NUMBER
47 ,ship_set_name VARCHAR2(30));
48
49 TYPE CSP_AVAILABILITY_REC_TYPE IS RECORD(resource_id NUMBER
50 ,resource_type VARCHAR2(30)
51 ,organization_id NUMBER
52 ,destination_location_id NUMBER
53 ,line_id NUMBER
54 ,item_id NUMBER
55 ,item_uom VARCHAR2(3)
56 ,item_type NUMBER
57 ,revision VARCHAR2(3)
58 ,quantity NUMBER
59 ,available_quantity NUMBER
60 ,source_org NUMBER
61 ,sub_inventory VARCHAR2(10)
62 ,available_date DATE
63 ,shipping_methode VARCHAR2(30)
64 ,intransit_time NUMBER
65 ,replenishment_source varchar2(3)
66 );
67
68 TYPE AVAILABLE_PARTS_REC_TYP IS RECORD(item_id NUMBER
69 ,item_uom varchar2(10)
70 ,required_quantity NUMBER
71 ,source_org_id NUMBER
72 ,sub_inventory_code VARCHAR2(10)
73 ,reserved_quantity NUMBER
74 ,ordered_quantity NUMBER
75 ,available_quantity NUMBER
76 ,shipping_methode VARCHAR2(30)
77 ,arraival_date DATE
78 ,order_by_date DATE
79 ,source_type VARCHAR2(50)
80 ,line_id NUMBER
81 ,item_type NUMBER
82 ,recommended_option VARCHAR2(1)
83 ,revision varchar2(3));
84 TYPE RESERVATION_REC_TYP IS RECORD(need_by_date DATE
85 ,organization_id NUMBER
86 ,item_id NUMBER
87 ,item_uom_code VARCHAR2(3)
88 ,quantity_needed NUMBER
89 ,sub_inventory_code VARCHAR2(10)
90 ,line_id NUMBER
91 ,revision varchar2(3));
92 TYPE ws_AVAILABLE_PARTS_REC_TYP IS RECORD(resource_id NUMBER
93 ,resource_type varchar2(30)
94 ,distance NUMBER
95 ,unit varchar(10)
96 ,phone_number varchar2(30)
97 ,name varchar2(240)
98 ,item_id NUMBER
99 ,item_number varchar2(240)
100 ,item_uom varchar2(10)
101 ,item_type varchar2(30)
102 ,source_org_id NUMBER
103 ,location_id NUMBER
104 ,sub_inventory_code VARCHAR2(10)
105 ,available_quantity NUMBER
106 ,on_hand_quantity NUMBER
107 ,shipping_method_code varchar2(30)
108 ,shipping_methode VARCHAR2(80)
109 ,arraival_date DATE
110 ,order_by_date DATE
111 ,source_type VARCHAR2(50)
112 ,revision varchar2(3)
113 );
114 TYPE csp_ws_resource_rec_type IS record (resource_type varchar2(30)
115 ,resource_id NUMBER
116 ,distance NUMBER
117 ,unit varchar2(10)
118 ,phone_number varchar2(30)
119 ,name varchar2(240));
120
121 TYPE alternate_item_rec_type IS RECORD(item NUMBER
122 ,revision varchar2(3)
123 ,item_uom varchar2(10)
124 ,item_quantity NUMBER
125 ,alternate_item NUMBER
126 ,alternate_item_uom varchar2(10)
127 ,alternate_item_quantity NUMBER
128 ,relation_type NUMBER);
129
130
131
132 TYPE csp_sch_resource_tbl_typ IS TABLE OF csp_sch_resources_rec_typ;
133
134 TYPE csp_sch_options_tbl_typ IS TABLE OF csp_sch_options_rec_typ ;
135
136 TYPE CSP_UNAVAILABILITY_TBL_TYPE IS TABLE OF CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_REC_TYPE ;
137
138 TYPE CSP_AVAILABILITY_TBL_TYPE IS TABLE OF CSP_SCH_INT_PVT.CSP_AVAILABILITY_REC_TYPE ;
139
140 TYPE CSP_PARTS_TBL_TYPE IS TABLE OF CSP_SCH_INT_PVT.CSP_PARTS_REC_TYPE;
141
142 TYPE AVAILABLE_PARTS_TBL_TYP IS TABLE OF CSP_SCH_INT_PVT.AVAILABLE_PARTS_REC_TYP;
143 TYPE AVAILABLE_PARTS_TBL_TYP1 IS TABLE OF CSP_SCH_INT_PVT.AVAILABLE_PARTS_REC_TYP INDEX BY BINARY_INTEGER;
144 TYPE CSP_PARTS_TBL_TYP1 IS TABLE OF CSP_SCH_INT_PVT.CSP_PARTS_REC_TYPE INDEX BY BINARY_INTEGER;
145 TYPE ws_AVAILABLE_PARTS_tbl_TYP IS table of ws_AVAILABLE_PARTS_REC_TYP;
146 TYPE csp_ws_resource_table_type IS table of csp_ws_resource_rec_type;
147 TYPE alternate_items_table_type IS TABLE OF alternate_item_rec_type;
148
149 PROCEDURE GET_AVAILABILITY_OPTIONS(p_api_version_number IN NUMBER
150 ,p_task_id IN NUMBER
151 ,p_resources IN CSP_SCH_INT_PVT.csp_sch_resource_tbl_typ
152 ,p_interval IN CSP_SCH_INT_PVT.csp_sch_interval_rec_typ
153 ,p_likelihood IN NUMBER
154 ,p_subinv_only IN BOOLEAN DEFAULT FALSE
155 ,p_mandatory IN BOOLEAN DEFAULT TRUE
156 ,p_trunk IN BOOLEAN DEFAULT TRUE
157 ,p_warehouse IN BOOLEAN DEFAULT TRUE
158 ,x_options OUT NOCOPY CSP_SCH_INT_PVT.csp_sch_options_tbl_typ
159 ,x_return_status OUT NOCOPY VARCHAR2
160 ,x_msg_data OUT NOCOPY VARCHAR2
161 ,x_msg_count OUT NOCOPY NUMBER);
162
163 PROCEDURE CHOOSE_OPTION(p_api_version_number IN NUMBER
164 ,p_task_id IN NUMBER
165 ,p_task_assignment_id IN NUMBER
166 ,p_likelihood IN NUMBER
167 ,p_mandatory IN BOOLEAN DEFAULT TRUE
168 ,p_trunk IN BOOLEAN DEFAULT TRUE
169 ,p_warehouse IN BOOLEAN DEFAULT TRUE
170 ,p_options IN CSP_SCH_INT_PVT.csp_sch_options_rec_typ
171 ,x_return_status OUT NOCOPY VARCHAR2
172 ,x_msg_data OUT NOCOPY VARCHAR2
173 ,x_msg_count OUT NOCOPY NUMBER);
174
175 PROCEDURE CLEAN_MATERIAL_TRANSACTION(p_api_version_number IN NUMBER
176 ,p_task_assignment_id IN NUMBER
177 ,x_return_status OUT NOCOPY VARCHAR2
178 ,x_msg_data OUT NOCOPY VARCHAR2
179 ,x_msg_count OUT NOCOPY NUMBER);
180
181 PROCEDURE CLEAN_REQUIREMENT(p_api_version_number IN NUMBER
182 ,p_task_assignment_id IN NUMBER
183 ,x_return_status OUT NOCOPY VARCHAR2
184 ,x_msg_data OUT NOCOPY VARCHAR2
185 ,x_msg_count OUT NOCOPY NUMBER);
186
187 PROCEDURE CREATE_ORDERS(p_api_version_number IN NUMBER
188 ,p_task_assignment_id IN NUMBER
189 ,x_return_status OUT NOCOPY VARCHAR2
190 ,x_msg_data OUT NOCOPY VARCHAR2
191 ,x_msg_count OUT NOCOPY NUMBER);
192 PROCEDURE CHECK_PARTS_AVAILABILITY(p_resource IN CSP_SCH_INT_PVT.csp_sch_resources_rec_typ
193 ,p_organization_id IN NUMBER
194 ,P_subinv_code IN VARCHAR2
195 ,p_need_by_date IN DATE
196 ,p_parts_list IN CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYP1
197 ,p_timezone_id IN NUMBER
201 ,x_msg_count OUT NOCOPY NUMBER
198 ,x_availability OUT NOCOPY CSP_SCH_INT_PVT.AVAILABLE_PARTS_TBL_TYP1
199 ,x_return_status OUT NOCOPY VARCHAR2
200 ,x_msg_data OUT NOCOPY VARCHAR2
202 ,p_called_from IN varchar2 DEFAULT 'SPARES'
203 ,p_location_id IN NUMBER DEFAULT NULL
204 ,p_include_alternates IN BOOLEAN DEFAULT NULL
205 );
206 PROCEDURE CHECK_LOCAL_INVENTORY( p_org_id IN NUMBER
207 ,p_revision IN varchar2
208 ,p_subinv_code IN VARCHAR2
209 ,p_item_id IN NUMBER
210 ,x_att OUT NOCOPY NUMBER
211 ,x_onhand OUT NOCOPY NUMBER
212 ,x_return_status OUT NOCOPY VARCHAR2
213 ,x_msg_data OUT NOCOPY VARCHAR2
214 ,x_msg_count OUT NOCOPY NUMBER);
215 FUNCTION CREATE_RESERVATION(p_reservation_parts IN
216 CSP_SCH_INT_PVT.RESERVATION_REC_TYP
217 ,x_return_status OUT NOCOPY VARCHAR2
218 ,x_msg_data OUT NOCOPY VARCHAR2) RETURN NUMBER;
219 PROCEDURE TASKS_POST_INSERT( x_return_status out NOCOPY varchar2);
220
221 PROCEDURE ws_Check_other_eng_subinv(p_resource_list IN CSP_SCH_INT_PVT.csp_ws_resource_table_type
222 ,p_parts_list IN CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYP1
223 ,p_include_alternate IN varchar2 DEFAULT 'N'
224 ,x_available_list OUT NOCOPY CSP_SCH_INT_PVT.ws_AVAILABLE_PARTS_tbl_TYP
225 ,x_return_status OUT NOCOPY varchar2
226 ,x_msg_data OUT NOCOPY varchar2
227 ,x_msg_count OUT NOCOPY NUMBER);
228 PROCEDURE ws_Check_engineers_subinv(p_resource_type IN varchar2
229 ,p_resource_id IN NUMBER
230 ,p_parts_list IN CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYP1
231 ,p_include_alternate IN varchar2 DEFAULT 'N'
232 ,x_available_list OUT NOCOPY CSP_SCH_INT_PVT.ws_AVAILABLE_PARTS_tbl_TYP
233 ,x_return_status OUT NOCOPY varchar2
234 ,x_msg_data OUT NOCOPY varchar2
235 ,x_msg_count OUT NOCOPY NUMBER);
236 PROCEDURE ws_Check_organizations(p_resource_type IN varchar2
237 ,p_resource_id IN NUMBER
238 ,p_parts_list IN csp_sch_int_pvt.CSP_PARTS_TBL_TYP1
239 ,p_include_alternate IN varchar2 DEFAULT 'N'
240 ,x_available_list OUT NOCOPY csp_sch_int_pvt.ws_AVAILABLE_PARTS_tbl_TYP
241 ,x_return_status OUT NOCOPY varchar2
242 ,x_msg_data OUT NOCOPY varchar2
243 ,x_msg_count OUT NOCOPY NUMBER);
244 PROCEDURE get_alternates(p_parts_rec IN CSP_SCH_INT_PVT.CSP_PARTS_REC_TYPE
245 ,p_org_id IN NUMBER
246 ,px_alternate_items IN OUT NOCOPY csp_sch_int_pvt.alternate_items_table_type
247 ,x_return_status OUT NOCOPY varchar2
248 ,x_msg_data OUT NOCOPY varchar2
249 ,x_msg_count OUT NOCOPY NUMBER);
250
251
252 PROCEDURE TASK_POST_CANCEL(x_return_status out nocopy varchar2);
253 PROCEDURE CREATE_RES_FOR_RCV_TRANXS(p_transaction_id IN NUMBER
254 ,x_return_Status OUT NOCOPY varchar2
255 ,x_msg_data OUT NOCOPY varchar2);
256
257 PROCEDURE GET_DELIVERY_DATE(p_relation_ship_id IN NUMBER,
258 x_delivery_date OUT NOCOPY DATE,
259 x_shipping_option OUT NOCOPY BOOLEAN,
260 x_return_status OUT NOCOPY VARCHAR2
261 ,x_msg_data OUT NOCOPY VARCHAR2
262 ,x_msg_count OUT NOCOPY NUMBER);
263
264 FUNCTION get_arrival_date(p_ship_date IN DATE,
265 p_lead_time IN NUMBER,
266 p_org_id IN NUMBER) return DATE;
267
268
269 PROCEDURE CANCEL_RESERVATION(p_reserv_id IN NUMBER
270 ,x_return_status OUT NOCOPY VARCHAR2
271 ,x_msg_data OUT NOCOPY VARCHAR2
272 ,x_msg_count OUT NOCOPY NUMBER);
273 PROCEDURE cancel_order_line(
274 p_order_line_id IN NUMBER,
275 p_cancel_reason IN Varchar2,
276 x_return_status OUT NOCOPY VARCHAR2,
277 x_msg_count OUT NOCOPY NUMBER,
278 x_msg_data OUT NOCOPY VARCHAR2);
279
280 PROCEDURE SPARES_CHECK2(
281 p_resources IN CSP_SCH_INT_PVT.csp_sch_resource_tbl_typ,
282 p_task_id in number,
283 p_need_by_date in date,
284 p_trunk IN BOOLEAN,
285 p_warehouse IN BOOLEAN,
286 p_mandatory IN BOOLEAN,
287 x_options OUT NOCOPY CSP_SCH_INT_PVT.csp_sch_options_tbl_typ,
288 x_return_status OUT NOCOPY VARCHAR2,
289 x_msg_data OUT NOCOPY VARCHAR2,
293 p_task_id in number,
290 x_msg_count OUT NOCOPY NUMBER);
291
292 procedure move_parts_on_reassign(
294 p_task_asgn_id in number,
295 p_new_task_asgn_id in number,
296 p_new_need_by_date in date,
297 p_new_resource_id in number,
298 p_new_resource_type in varchar2,
299 x_return_status OUT NOCOPY VARCHAR2,
300 x_msg_count OUT NOCOPY NUMBER,
301 x_msg_data OUT NOCOPY VARCHAR2
302 );
303
304 END CSP_SCH_INT_PVT;
305
[Home] [Help]