1 PACKAGE CSP_PARTS_REQUIREMENT AUTHID CURRENT_USER AS
2 /* $Header: cspvprqs.pls 120.9.12020000.3 2012/12/19 11:49:53 shadas ship $ */
3
4
5 -- Purpose: To create/update/cancel parts requirements for spares
6 --
7 -- MODIFICATION HISTORY
8 -- Person Date Comments
9 -- --------- ------ ------------------------------------------
10 -- phegde 05/17/01 Created Package
11
12
13 TYPE Header_Rec_Type IS RECORD
14 ( requisition_header_id NUMBER
15 ,requisition_number VARCHAR2(20)
16 ,description VARCHAR2(240)
17 ,order_header_id NUMBER
18 ,order_type_id NUMBER
19 ,ship_to_location_id NUMBER
20 ,shipping_method_code VARCHAR2(30)
21 ,task_id NUMBER
22 ,task_assignment_id NUMBER
23 ,need_by_date DATE
24 ,dest_organization_id NUMBER
25 ,dest_subinventory VARCHAR2(30)
26 ,operation VARCHAR2(30)
27 ,requirement_header_id NUMBER
28 ,change_Reason VARCHAR2(30)
29 ,change_comments VARCHAR2(30)
30 ,resource_type VARCHAR2(30)
31 ,resource_id NUMBER
32 ,incident_id NUMBER
33 ,address_type varchar2(1)
34 ,JUSTIFICATION VARCHAR2(480)
35 ,NOTE_TO_BUYER VARCHAR2(480)
36 ,NOTE1_ID NUMBER
37 ,NOTE1_TITLE VARCHAR2(80)
38 ,CALLED_FROM VARCHAR2(240)
39 ,suggested_vendor_id NUMBER
40 ,suggested_vendor_name VARCHAR2(240)
41 ,ATTRIBUTE_CATEGORY VARCHAR2(30) ,
42 ATTRIBUTE1 VARCHAR2(150),
43 ATTRIBUTE2 VARCHAR2(150),
44 ATTRIBUTE3 VARCHAR2(150),
45 ATTRIBUTE4 VARCHAR2(150),
46 ATTRIBUTE5 VARCHAR2(150),
47 ATTRIBUTE6 VARCHAR2(150),
48 ATTRIBUTE7 VARCHAR2(150),
49 ATTRIBUTE8 VARCHAR2(150),
50 ATTRIBUTE9 VARCHAR2(150),
51 ATTRIBUTE10 VARCHAR2(150),
52 ATTRIBUTE11 VARCHAR2(150),
53 ATTRIBUTE12 VARCHAR2(150),
54 ATTRIBUTE13 VARCHAR2(150),
55 ATTRIBUTE14 VARCHAR2(150),
56 ATTRIBUTE15 VARCHAR2(150),
57 SHIP_TO_CONTACT_ID NUMBER
58 );
59
60 TYPE Line_Rec_type IS RECORD
61 ( requisition_line_id NUMBER
62 ,order_line_id NUMBER
63 ,line_num NUMBER
64 ,inventory_item_id NUMBER
65 ,item_description VARCHAR2(240)
66 ,revision VARCHAR2(3)
67 ,quantity NUMBER
68 ,unit_of_measure VARCHAR2(3)
69 ,dest_subinventory VARCHAR2(30)
70 ,source_organization_id NUMBER
71 ,source_subinventory VARCHAR2(30)
72 ,ship_complete VARCHAR2(30)
73 ,shipping_method_code VARCHAR2(30)
74 ,likelihood NUMBER
75 ,ordered_quantity NUMBER
76 ,order_by_date DATE
77 ,arrival_date DATE
78 ,need_by_date DATE
79 ,reservation_id NUMBER
80 ,requirement_line_id NUMBER
81 ,change_reason VARCHAR2(30)
82 ,change_comments VARCHAR2(30)
83 ,booked_flag VARCHAR2(30) := 'N'
84 ,sourced_from VARCHAR2(30) := 'IO'
85 ,available_by_need_date VARCHAR2(1)
86 ,ATTRIBUTE_CATEGORY VARCHAR2(30) ,
87 ATTRIBUTE1 VARCHAR2(150),
88 ATTRIBUTE2 VARCHAR2(150),
89 ATTRIBUTE3 VARCHAR2(150),
90 ATTRIBUTE4 VARCHAR2(150),
91 ATTRIBUTE5 VARCHAR2(150),
92 ATTRIBUTE6 VARCHAR2(150),
93 ATTRIBUTE7 VARCHAR2(150),
94 ATTRIBUTE8 VARCHAR2(150),
95 ATTRIBUTE9 VARCHAR2(150),
96 ATTRIBUTE10 VARCHAR2(150),
97 ATTRIBUTE11 VARCHAR2(150),
98 ATTRIBUTE12 VARCHAR2(150),
99 ATTRIBUTE13 VARCHAR2(150),
100 ATTRIBUTE14 VARCHAR2(150),
101 ATTRIBUTE15 VARCHAR2(150)
102 );
103
104
105 TYPE Line_Tbl_Type IS TABLE OF Line_Rec_Type
106 INDEX BY BINARY_INTEGER;
107
108
109 TYPE Line_details_Rec_type IS RECORD
110 ( req_line_detail_id NUMBER
111 ,req_line_id NUMBER
112 ,source_type varchar2(10):= 'IO'
113 ,source_id NUMBER
114 );
115
116 TYPE Line_detail_Tbl_Type IS TABLE OF Line_details_Rec_type
117 INDEX BY BINARY_INTEGER;
118
119 TYPE Rqmt_Line_Rec_Type IS RECORD
120 ( Requirement_Line_Id NUMBER);
121
122 TYPE Rqmt_Line_Tbl_Type IS TABLE OF Rqmt_Line_Rec_Type
123 INDEX BY BINARY_INTEGER;
124
125 /* TYPE Item_Rec_Type IS RECORD
126 ( Inventory_Item_Id NUMBER);
127
128 TYPE Item_Tbl_Type IS TABLE OF Item_Rec_Type
129 INDEX BY BINARY_INTEGER;
130 */
131
132 TYPE Order_Rec_Type IS RECORD
133 ( SOURCE_TYPE VARCHAR2(10)
134 ,ORDER_NUMBER NUMBER);
135
136 TYPE Order_Tbl_Type IS TABLE OF Order_Rec_Type
137 INDEX BY BINARY_INTEGER;
138
139 -- Operations
140 G_OPR_CREATE CONSTANT VARCHAR2(30) := 'CREATE';
141 G_OPR_UPDATE CONSTANT VARCHAR2(30) := 'UPDATE';
142 G_OPR_DELETE CONSTANT VARCHAR2(30) := 'DELETE';
143 G_OPR_LOCK CONSTANT VARCHAR2(30) := 'LOCK';
144 G_OPR_CANCEL CONSTANT VARCHAR2(30) := 'CANCEL';
145 --G_OPR_NONE CONSTANT VARCHAR2(30) := FND_API.G_MISS_CHAR;
146
147 -- Address Types
148 G_ADDR_RESOURCE CONSTANT VARCHAR2(1) := 'R';
149 G_ADDR_CUSTOMER CONSTANT VARCHAR2(1) := 'C';
150 G_ADDR_SPECIAL CONSTANT VARCHAR2(1) := 'S';
151
152 PROCEDURE process_requirement
153 ( p_api_version IN NUMBER
154 ,p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE
155 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
156 ,px_header_rec IN OUT NOCOPY csp_parts_requirement.Header_rec_type
157 ,px_line_table IN OUT NOCOPY csp_parts_requirement.Line_Tbl_type
158 ,p_create_order_flag IN VARCHAR2
159 ,x_return_status OUT NOCOPY VARCHAR2
160 ,x_msg_count OUT NOCOPY NUMBER
161 ,x_msg_data OUT NOCOPY VARCHAR2
162 );
163
164 -- This procedure will be called from the parts requirement UI for creating orders
165 -- and updating the requirements table with the order details
166 PROCEDURE csptrreq_fm_order(
167 p_api_version IN NUMBER
168 ,p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE
169 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
170 ,px_header_rec IN OUT NOCOPY csp_parts_requirement.Header_rec_type
171 ,px_line_table IN OUT NOCOPY csp_parts_requirement.Line_Tbl_type
172 ,x_return_status OUT NOCOPY VARCHAR2
173 ,x_msg_count OUT NOCOPY NUMBER
174 ,x_msg_data OUT NOCOPY VARCHAR2
175 );
176
177 PROCEDURE csptrreq_order_res(
178 p_api_version IN NUMBER
179 ,p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE
180 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
181 ,px_header_rec IN OUT NOCOPY csp_parts_requirement.Header_rec_type
182 ,px_line_table IN OUT NOCOPY csp_parts_requirement.Line_Tbl_type
183 ,x_return_status OUT NOCOPY VARCHAR2
184 ,x_msg_count OUT NOCOPY NUMBER
185 ,x_msg_data OUT NOCOPY VARCHAR2
186 );
187 PROCEDURE Get_source_organization (
188 P_Inventory_Item_Id IN NUMBER,
189 P_Organization_Id IN NUMBER,
190 P_Secondary_Inventory IN VARCHAR2,
191 x_source_org_id OUT NOCOPY NUMBER,
192 x_source_subinv OUT NOCOPY VARCHAR2
193 );
194
195 PROCEDURE delete_rqmt_header(
196 p_api_version IN NUMBER
197 ,p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE
198 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
199 ,p_header_id IN NUMBER
200 ,x_return_status OUT NOCOPY VARCHAR2
201 ,x_msg_count OUT NOCOPY NUMBER
202 ,x_msg_data OUT NOCOPY VARCHAR2
203 );
204
205 PROCEDURE save_rqmt_line(
206 p_api_version IN NUMBER
207 ,p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE
208 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
209 ,px_header_rec IN OUT NOCOPY csp_parts_requirement.Header_rec_type
210 ,px_line_tbl IN OUT NOCOPY csp_parts_requirement.Line_tbl_type
211 ,x_return_status OUT NOCOPY VARCHAR2
212 ,x_msg_count OUT NOCOPY NUMBER
213 ,x_msg_data OUT NOCOPY VARCHAR2
214 );
215
216 PROCEDURE delete_rqmt_line(
217 p_api_version IN NUMBER
218 ,p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE
219 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
220 ,p_line_tbl IN OUT NOCOPY csp_parts_requirement.Rqmt_Line_tbl_type
221 ,x_return_status OUT NOCOPY VARCHAR2
222 ,x_msg_count OUT NOCOPY NUMBER
223 ,x_msg_data OUT NOCOPY VARCHAR2
224 );
225
226 PROCEDURE check_Availability(
227 p_api_version IN NUMBER
228 ,p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE
229 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
230 ,p_header_id IN NUMBER
231 ,x_line_Tbl OUT NOCOPY csp_parts_requirement.Line_tbl_type
232 ,x_avail_flag OUT NOCOPY VARCHAR2
233 ,x_return_status OUT NOCOPY VARCHAR2
234 ,x_msg_count OUT NOCOPY NUMBER
235 ,x_msg_data OUT NOCOPY VARCHAR2
236 );
237
238 PROCEDURE create_order(
239 p_api_version IN NUMBER
240 ,p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE
241 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
242 ,p_header_id IN NUMBER
243 ,x_order_tbl OUT NOCOPY Order_Tbl_Type
244 ,x_return_status OUT NOCOPY VARCHAR2
245 ,x_msg_count OUT NOCOPY NUMBER
246 ,x_msg_data OUT NOCOPY VARCHAR2
247 );
248
249 PROCEDURE TASK_ASSIGNMENT_POST_UPDATE(x_return_status out nocopy varchar2);
250 PROCEDURE TASK_ASSIGNMENT_PRE_UPDATE( x_return_status OUT NOCOPY varchar2);
251 PROCEDURE TASK_ASSIGNMENT_POST_INSERT(x_return_status out nocopy varchar2);
252 PROCEDURE TASK_ASSIGNMENT_PRE_DELETE(x_return_status out nocopy varchar2);
253
254 procedure get_resource_shift_end(
255 p_resource_id in number
256 ,p_resource_type in varchar2
257 ,x_shift_end_datetime out nocopy date
258 ,x_return_status out nocopy varchar2
259 ,x_msg_count out nocopy number
260 ,x_msg_data out nocopy varchar2
261 );
262
263 END; -- Package spec
[Home] [Help]