1 PACKAGE CSP_PARTS_REQUIREMENT AUTHID CURRENT_USER AS
2 /* $Header: cspvprqs.pls 120.4 2006/05/01 17:25:15 phegde noship $ */
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 );
58
59 TYPE Line_Rec_type IS RECORD
60 ( requisition_line_id NUMBER
61 ,order_line_id NUMBER
62 ,line_num NUMBER
63 ,inventory_item_id NUMBER
64 ,item_description VARCHAR2(240)
65 ,revision VARCHAR2(3)
66 ,quantity NUMBER
67 ,unit_of_measure VARCHAR2(3)
68 ,dest_subinventory VARCHAR2(30)
69 ,source_organization_id NUMBER
70 ,source_subinventory VARCHAR2(30)
71 ,ship_complete VARCHAR2(30)
72 ,shipping_method_code VARCHAR2(30)
73 ,likelihood NUMBER
74 ,ordered_quantity NUMBER
75 ,order_by_date DATE
76 ,arrival_date DATE
77 ,need_by_date DATE
78 ,reservation_id NUMBER
79 ,requirement_line_id NUMBER
80 ,change_reason VARCHAR2(30)
81 ,change_comments VARCHAR2(30)
82 ,booked_flag VARCHAR2(30) := 'N'
83 ,sourced_from VARCHAR2(30) := 'IO'
84 ,available_by_need_date VARCHAR2(1)
85 ,ATTRIBUTE_CATEGORY VARCHAR2(30) ,
86 ATTRIBUTE1 VARCHAR2(150),
87 ATTRIBUTE2 VARCHAR2(150),
88 ATTRIBUTE3 VARCHAR2(150),
89 ATTRIBUTE4 VARCHAR2(150),
90 ATTRIBUTE5 VARCHAR2(150),
91 ATTRIBUTE6 VARCHAR2(150),
92 ATTRIBUTE7 VARCHAR2(150),
93 ATTRIBUTE8 VARCHAR2(150),
94 ATTRIBUTE9 VARCHAR2(150),
95 ATTRIBUTE10 VARCHAR2(150),
96 ATTRIBUTE11 VARCHAR2(150),
97 ATTRIBUTE12 VARCHAR2(150),
98 ATTRIBUTE13 VARCHAR2(150),
99 ATTRIBUTE14 VARCHAR2(150),
100 ATTRIBUTE15 VARCHAR2(150)
101 );
102
103
104 TYPE Line_Tbl_Type IS TABLE OF Line_Rec_Type
105 INDEX BY BINARY_INTEGER;
106
107
108 TYPE Line_details_Rec_type IS RECORD
109 ( req_line_detail_id NUMBER
110 ,req_line_id NUMBER
111 ,source_type varchar2(10):= 'IO'
112 ,source_id NUMBER
113 );
114
115 TYPE Line_detail_Tbl_Type IS TABLE OF Line_details_Rec_type
116 INDEX BY BINARY_INTEGER;
117
118 TYPE Rqmt_Line_Rec_Type IS RECORD
119 ( Requirement_Line_Id NUMBER);
120
121 TYPE Rqmt_Line_Tbl_Type IS TABLE OF Rqmt_Line_Rec_Type
122 INDEX BY BINARY_INTEGER;
123
124 /* TYPE Item_Rec_Type IS RECORD
125 ( Inventory_Item_Id NUMBER);
126
127 TYPE Item_Tbl_Type IS TABLE OF Item_Rec_Type
128 INDEX BY BINARY_INTEGER;
129 */
130
131 TYPE Order_Rec_Type IS RECORD
132 ( SOURCE_TYPE VARCHAR2(10)
133 ,ORDER_NUMBER NUMBER);
134
135 TYPE Order_Tbl_Type IS TABLE OF Order_Rec_Type
136 INDEX BY BINARY_INTEGER;
137
138 -- Operations
139 G_OPR_CREATE CONSTANT VARCHAR2(30) := 'CREATE';
140 G_OPR_UPDATE CONSTANT VARCHAR2(30) := 'UPDATE';
141 G_OPR_DELETE CONSTANT VARCHAR2(30) := 'DELETE';
142 G_OPR_LOCK CONSTANT VARCHAR2(30) := 'LOCK';
143 G_OPR_CANCEL CONSTANT VARCHAR2(30) := 'CANCEL';
144 --G_OPR_NONE CONSTANT VARCHAR2(30) := FND_API.G_MISS_CHAR;
145
146 -- Address Types
147 G_ADDR_RESOURCE CONSTANT VARCHAR2(1) := 'R';
148 G_ADDR_CUSTOMER CONSTANT VARCHAR2(1) := 'C';
149 G_ADDR_SPECIAL CONSTANT VARCHAR2(1) := 'S';
150
151 PROCEDURE process_requirement
152 ( p_api_version IN NUMBER
153 ,p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE
154 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
155 ,px_header_rec IN OUT NOCOPY csp_parts_requirement.Header_rec_type
156 ,px_line_table IN OUT NOCOPY csp_parts_requirement.Line_Tbl_type
157 ,p_create_order_flag IN VARCHAR2
158 ,x_return_status OUT NOCOPY VARCHAR2
159 ,x_msg_count OUT NOCOPY NUMBER
160 ,x_msg_data OUT NOCOPY VARCHAR2
161 );
162
163 -- This procedure will be called from the parts requirement UI for creating orders
164 -- and updating the requirements table with the order details
165 PROCEDURE csptrreq_fm_order(
166 p_api_version IN NUMBER
167 ,p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE
168 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
169 ,px_header_rec IN OUT NOCOPY csp_parts_requirement.Header_rec_type
170 ,px_line_table IN OUT NOCOPY csp_parts_requirement.Line_Tbl_type
171 ,x_return_status OUT NOCOPY VARCHAR2
172 ,x_msg_count OUT NOCOPY NUMBER
173 ,x_msg_data OUT NOCOPY VARCHAR2
174 );
175
176 PROCEDURE csptrreq_order_res(
177 p_api_version IN NUMBER
178 ,p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE
179 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
180 ,px_header_rec IN OUT NOCOPY csp_parts_requirement.Header_rec_type
181 ,px_line_table IN OUT NOCOPY csp_parts_requirement.Line_Tbl_type
182 ,x_return_status OUT NOCOPY VARCHAR2
183 ,x_msg_count OUT NOCOPY NUMBER
184 ,x_msg_data OUT NOCOPY VARCHAR2
185 );
186 PROCEDURE Get_source_organization (
187 P_Inventory_Item_Id IN NUMBER,
188 P_Organization_Id IN NUMBER,
189 P_Secondary_Inventory IN VARCHAR2,
190 x_source_org_id OUT NOCOPY NUMBER
191 );
192
193 PROCEDURE delete_rqmt_header(
194 p_api_version IN NUMBER
195 ,p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE
196 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
197 ,p_header_id IN NUMBER
198 ,x_return_status OUT NOCOPY VARCHAR2
199 ,x_msg_count OUT NOCOPY NUMBER
200 ,x_msg_data OUT NOCOPY VARCHAR2
201 );
202
203 PROCEDURE save_rqmt_line(
204 p_api_version IN NUMBER
205 ,p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE
206 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
207 ,px_header_rec IN OUT NOCOPY csp_parts_requirement.Header_rec_type
208 ,px_line_tbl IN OUT NOCOPY csp_parts_requirement.Line_tbl_type
209 ,x_return_status OUT NOCOPY VARCHAR2
210 ,x_msg_count OUT NOCOPY NUMBER
211 ,x_msg_data OUT NOCOPY VARCHAR2
212 );
213
214 PROCEDURE delete_rqmt_line(
215 p_api_version IN NUMBER
216 ,p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE
217 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
218 ,p_line_tbl IN OUT NOCOPY csp_parts_requirement.Rqmt_Line_tbl_type
219 ,x_return_status OUT NOCOPY VARCHAR2
220 ,x_msg_count OUT NOCOPY NUMBER
221 ,x_msg_data OUT NOCOPY VARCHAR2
222 );
223
224 PROCEDURE check_Availability(
225 p_api_version IN NUMBER
226 ,p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE
227 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
228 ,p_header_id IN NUMBER
229 ,x_line_Tbl OUT NOCOPY csp_parts_requirement.Line_tbl_type
230 ,x_avail_flag OUT NOCOPY VARCHAR2
231 ,x_return_status OUT NOCOPY VARCHAR2
232 ,x_msg_count OUT NOCOPY NUMBER
233 ,x_msg_data OUT NOCOPY VARCHAR2
234 );
235
236 PROCEDURE create_order(
237 p_api_version IN NUMBER
238 ,p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE
239 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
240 ,p_header_id IN NUMBER
241 ,x_order_tbl OUT NOCOPY Order_Tbl_Type
242 ,x_return_status OUT NOCOPY VARCHAR2
243 ,x_msg_count OUT NOCOPY NUMBER
244 ,x_msg_data OUT NOCOPY VARCHAR2
245 );
246
247 PROCEDURE TASK_ASSIGNMENT_POST_UPDATE(x_return_status out nocopy varchar2);
248 PROCEDURE TASK_ASSIGNMENT_PRE_UPDATE( x_return_status OUT NOCOPY varchar2);
249 END; -- Package spec
[Home] [Help]