1 PACKAGE CSP_SCH_INT_PVT AUTHID CURRENT_USER AS
2 /* $Header: cspgscis.pls 115.26 2004/02/17 21:16:37 jjalla 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
23 TYPE csp_sch_interval_rec_typ IS RECORD(earliest_time DATE,
24 latest_time DATE);
25
26 TYPE CSP_PARTS_REC_TYPE IS RECORD(item_id NUMBER
27 ,item_uom VARCHAR2(3)
28 ,revision VARCHAR2(3)
29 ,quantity NUMBER
30 ,ship_set_name VARCHAR2(30)
31 ,line_id NUMBER);
32
33 TYPE CSP_UNAVAILABILITY_REC_TYPE IS RECORD(resource_id NUMBER
34 ,resource_type VARCHAR2(30)
35 ,organization_id NUMBER
36 ,item_id NUMBER
37 ,revision VARCHAR2(3)
38 ,item_uom VARCHAR2(3)
39 ,item_type NUMBER
40 ,line_id NUMBER
41 ,quantity NUMBER
42 ,ship_set_name VARCHAR2(30));
43
44 TYPE CSP_AVAILABILITY_REC_TYPE IS RECORD(resource_id NUMBER
45 ,resource_type VARCHAR2(30)
46 ,organization_id NUMBER
47 ,destination_location_id NUMBER
48 ,line_id NUMBER
49 ,item_id NUMBER
50 ,item_uom VARCHAR2(3)
51 ,item_type NUMBER
52 ,revision VARCHAR2(3)
53 ,quantity NUMBER
54 ,available_quantity NUMBER
55 ,source_org NUMBER
56 ,sub_inventory VARCHAR2(10)
57 ,available_date DATE
58 ,shipping_methode VARCHAR2(30)
59 ,intransit_time NUMBER
60 );
61
62 TYPE AVAILABLE_PARTS_REC_TYP IS RECORD(item_id NUMBER
63 ,item_uom varchar2(10)
64 ,required_quantity NUMBER
65 ,source_org_id NUMBER
66 ,sub_inventory_code VARCHAR2(10)
67 ,reserved_quantity NUMBER
68 ,ordered_quantity NUMBER
69 ,available_quantity NUMBER
70 ,shipping_methode VARCHAR2(30)
71 ,arraival_date DATE
72 ,order_by_date DATE
73 ,source_type VARCHAR2(50)
74 ,line_id NUMBER
75 ,item_type NUMBER
76 ,recommended_option VARCHAR2(1)
77 ,revision varchar2(3));
78 TYPE RESERVATION_REC_TYP IS RECORD(need_by_date DATE
79 ,organization_id NUMBER
80 ,item_id NUMBER
81 ,item_uom_code VARCHAR2(3)
82 ,quantity_needed NUMBER
83 ,sub_inventory_code VARCHAR2(10)
84 ,line_id NUMBER
85 ,revision varchar2(3));
86 TYPE ws_AVAILABLE_PARTS_REC_TYP IS RECORD(resource_id NUMBER
87 ,resource_type varchar2(30)
88 ,distance NUMBER
89 ,unit varchar(10)
90 ,phone_number varchar2(30)
91 ,name varchar2(240)
92 ,item_id NUMBER
93 ,item_number varchar2(240)
94 ,item_uom varchar2(10)
95 ,item_type varchar2(30)
96 ,source_org_id NUMBER
97 ,location_id NUMBER
98 ,sub_inventory_code VARCHAR2(10)
99 ,available_quantity NUMBER
100 ,on_hand_quantity NUMBER
101 ,shipping_method_code varchar2(30)
102 ,shipping_methode VARCHAR2(80)
103 ,arraival_date DATE
104 ,order_by_date DATE
105 ,source_type VARCHAR2(50)
106 ,revision varchar2(3)
107 );
108 TYPE csp_ws_resource_rec_type IS record (resource_type varchar2(30)
109 ,resource_id NUMBER
110 ,distance NUMBER
111 ,unit varchar2(10)
112 ,phone_number varchar2(30)
113 ,name varchar2(240));
114
115 TYPE alternate_item_rec_type IS RECORD(item NUMBER
116 ,revision varchar2(3)
117 ,item_uom varchar2(10)
118 ,item_quantity NUMBER
119 ,alternate_item NUMBER
120 ,alternate_item_uom varchar2(10)
121 ,alternate_item_quantity NUMBER
122 ,relation_type NUMBER);
123
124
125
126 TYPE csp_sch_resource_tbl_typ IS TABLE OF csp_sch_resources_rec_typ;
127
128 TYPE csp_sch_options_tbl_typ IS TABLE OF csp_sch_options_rec_typ ;
129
130 TYPE CSP_UNAVAILABILITY_TBL_TYPE IS TABLE OF CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_REC_TYPE ;
131
132 TYPE CSP_AVAILABILITY_TBL_TYPE IS TABLE OF CSP_SCH_INT_PVT.CSP_AVAILABILITY_REC_TYPE ;
133
134 TYPE CSP_PARTS_TBL_TYPE IS TABLE OF CSP_SCH_INT_PVT.CSP_PARTS_REC_TYPE;
135
136 TYPE AVAILABLE_PARTS_TBL_TYP IS TABLE OF CSP_SCH_INT_PVT.AVAILABLE_PARTS_REC_TYP;
137 TYPE AVAILABLE_PARTS_TBL_TYP1 IS TABLE OF CSP_SCH_INT_PVT.AVAILABLE_PARTS_REC_TYP INDEX BY BINARY_INTEGER;
138 TYPE CSP_PARTS_TBL_TYP1 IS TABLE OF CSP_SCH_INT_PVT.CSP_PARTS_REC_TYPE INDEX BY BINARY_INTEGER;
139 TYPE ws_AVAILABLE_PARTS_tbl_TYP IS table of ws_AVAILABLE_PARTS_REC_TYP;
140 TYPE csp_ws_resource_table_type IS table of csp_ws_resource_rec_type;
141 TYPE alternate_items_table_type IS TABLE OF alternate_item_rec_type;
142
143 PROCEDURE GET_AVAILABILITY_OPTIONS(p_api_version_number IN NUMBER
144 ,p_task_id IN NUMBER
145 ,p_resources IN CSP_SCH_INT_PVT.csp_sch_resource_tbl_typ
146 ,p_interval IN CSP_SCH_INT_PVT.csp_sch_interval_rec_typ
147 ,p_likelihood IN NUMBER
148 ,p_subinv_only IN BOOLEAN DEFAULT FALSE
149 ,x_options OUT NOCOPY CSP_SCH_INT_PVT.csp_sch_options_tbl_typ
150 ,x_return_status OUT NOCOPY VARCHAR2
151 ,x_msg_data OUT NOCOPY VARCHAR2
152 ,x_msg_count OUT NOCOPY NUMBER);
153
154 PROCEDURE CHOOSE_OPTION(p_api_version_number IN NUMBER
155 ,p_task_id IN NUMBER
156 ,p_task_assignment_id IN NUMBER
157 ,p_likelihood IN NUMBER
158 ,p_options IN CSP_SCH_INT_PVT.csp_sch_options_rec_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 CLEAN_MATERIAL_TRANSACTION(p_api_version_number IN NUMBER
164 ,p_task_assignment_id IN NUMBER
165 ,x_return_status OUT NOCOPY VARCHAR2
166 ,x_msg_data OUT NOCOPY VARCHAR2
167 ,x_msg_count OUT NOCOPY NUMBER);
168 PROCEDURE CREATE_ORDERS(p_api_version_number IN NUMBER
169 ,p_task_assignment_id IN NUMBER
170 ,x_return_status OUT NOCOPY VARCHAR2
171 ,x_msg_data OUT NOCOPY VARCHAR2
172 ,x_msg_count OUT NOCOPY NUMBER);
173 PROCEDURE CHECK_PARTS_AVAILABILITY(p_resource IN CSP_SCH_INT_PVT.csp_sch_resources_rec_typ
174 ,p_organization_id IN NUMBER
175 ,P_subinv_code IN VARCHAR2
176 ,p_need_by_date IN DATE
177 ,p_parts_list IN CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYP1
178 ,p_timezone_id IN NUMBER
179 ,x_availability OUT NOCOPY CSP_SCH_INT_PVT.AVAILABLE_PARTS_TBL_TYP1
180 ,x_return_status OUT NOCOPY VARCHAR2
181 ,x_msg_data OUT NOCOPY VARCHAR2
182 ,x_msg_count OUT NOCOPY NUMBER
183 ,p_called_from IN varchar2 DEFAULT 'SPARES'
184 ,p_location_id IN NUMBER DEFAULT NULL
185 );
186 PROCEDURE CHECK_LOCAL_INVENTORY( p_org_id IN NUMBER
187 ,p_revision IN varchar2
188 ,p_subinv_code IN VARCHAR2
189 ,p_item_id IN NUMBER
190 ,x_att OUT NOCOPY NUMBER
191 ,x_onhand OUT NOCOPY NUMBER
192 ,x_return_status OUT NOCOPY VARCHAR2
193 ,x_msg_data OUT NOCOPY VARCHAR2
194 ,x_msg_count OUT NOCOPY NUMBER);
195 FUNCTION CREATE_RESERVATION(p_reservation_parts IN CSP_SCH_INT_PVT.RESERVATION_REC_TYP
196 ,x_return_status OUT NOCOPY VARCHAR2
197 ,x_msg_data OUT NOCOPY VARCHAR2) RETURN NUMBER;
198 PROCEDURE TASKS_POST_INSERT( x_return_status out NOCOPY varchar2);
199
200 PROCEDURE ws_Check_other_eng_subinv(p_resource_list IN CSP_SCH_INT_PVT.csp_ws_resource_table_type
201 ,p_parts_list IN CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYP1
202 ,p_include_alternate IN varchar2 DEFAULT 'N'
203 ,x_available_list OUT NOCOPY CSP_SCH_INT_PVT.ws_AVAILABLE_PARTS_tbl_TYP
204 ,x_return_status OUT NOCOPY varchar2
205 ,x_msg_data OUT NOCOPY varchar2
206 ,x_msg_count OUT NOCOPY NUMBER);
207 PROCEDURE ws_Check_engineers_subinv(p_resource_type IN varchar2
208 ,p_resource_id IN NUMBER
209 ,p_parts_list IN CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYP1
210 ,p_include_alternate IN varchar2 DEFAULT 'N'
211 ,x_available_list OUT NOCOPY CSP_SCH_INT_PVT.ws_AVAILABLE_PARTS_tbl_TYP
212 ,x_return_status OUT NOCOPY varchar2
213 ,x_msg_data OUT NOCOPY varchar2
214 ,x_msg_count OUT NOCOPY NUMBER);
215 PROCEDURE ws_Check_organizations(p_resource_type IN varchar2
216 ,p_resource_id IN NUMBER
217 ,p_parts_list IN csp_sch_int_pvt.CSP_PARTS_TBL_TYP1
218 ,p_include_alternate IN varchar2 DEFAULT 'N'
219 ,x_available_list OUT NOCOPY csp_sch_int_pvt.ws_AVAILABLE_PARTS_tbl_TYP
220 ,x_return_status OUT NOCOPY varchar2
221 ,x_msg_data OUT NOCOPY varchar2
222 ,x_msg_count OUT NOCOPY NUMBER);
223 PROCEDURE get_alternates(p_parts_rec IN CSP_SCH_INT_PVT.CSP_PARTS_REC_TYPE
224 ,p_org_id IN NUMBER
225 ,px_alternate_items IN OUT NOCOPY csp_sch_int_pvt.alternate_items_table_type
226 ,x_return_status OUT NOCOPY varchar2
227 ,x_msg_data OUT NOCOPY varchar2
228 ,x_msg_count OUT NOCOPY NUMBER);
229
230
231 PROCEDURE TASK_POST_CANCEL(x_return_status out nocopy varchar2);
232 PROCEDURE CREATE_RES_FOR_RCV_TRANXS(p_transaction_id IN NUMBER
233 ,x_return_Status OUT NOCOPY varchar2
234 ,x_msg_data OUT NOCOPY varchar2);
235
236 PROCEDURE GET_DELIVERY_DATE(p_relation_ship_id IN NUMBER,
237 x_delivery_date OUT NOCOPY DATE,
238 x_shipping_option OUT NOCOPY BOOLEAN,
239 x_return_status OUT NOCOPY VARCHAR2
240 ,x_msg_data OUT NOCOPY VARCHAR2
241 ,x_msg_count OUT NOCOPY NUMBER);
242
243 FUNCTION get_arrival_date(p_ship_date IN DATE,
244 p_lead_time IN NUMBER,
245 p_org_id IN NUMBER) return DATE;
246
247
248 PROCEDURE CANCEL_RESERVATION(p_reserv_id IN NUMBER
249 ,x_return_status OUT NOCOPY VARCHAR2
250 ,x_msg_data OUT NOCOPY VARCHAR2
251 ,x_msg_count OUT NOCOPY NUMBER);
252 PROCEDURE cancel_order_line(
253 p_order_line_id IN NUMBER,
254 p_cancel_reason IN Varchar2,
255 x_return_status OUT NOCOPY VARCHAR2,
256 x_msg_count OUT NOCOPY NUMBER,
257 x_msg_data OUT NOCOPY VARCHAR2);
258
259 END CSP_SCH_INT_PVT;
260
[Home] [Help]